K3 增加SQL报表，统计入库物料未出库列表（存储过程）

仓库为统计吊滞情况，要求做一张报表，统计某月入库的物料，在未来一段时期内未出过库的列表。

1、写存储过程，入库物料就统计起始月份当月入库物料

create procedure [dbo].[sp_jlkj_CHR_入库物料未出库列表]
@StartDate datetime,
@EndDate datetime


as
BEGIN

SET NOCOUNT ON;



--declare @StartDate datetime,
--        @EndDate datetime ;

--set @StartDate='2023-06-01';
--set @EndDate='2023-11-30';


----起始日期至起始日当月月末的入库数量
select * into ##rk from  (Select v1.FBillNo ,v1.FDate,convert(char(20),v1.FDate,111) rq,  u1.FItemID fitemid ,
case 
when v1.FTranType=1 then '外购入库'
when v1.FTranType=2 then '产品入库'
when v1.FTranType=10 then '其他入库'
when v1.FTranType=21 then '销售出库'
when v1.FTranType=24 then '生产领料'
when v1.FTranType=29 then '其他入库'
end type,u1.FQty ,u1.FAmount 
from ICStockBill v1 
inner  JOIN ICStockBillEntry u1 ON  v1.FInterID = u1.FInterID  
where 1=1 AND ( (v1.Fstatus=1 AND  v1.FCancellation = 0))
and (v1.FDate>=@StartDate and  v1.FDate<=DATEADD(MONTH, DATEDIFF(MONTH, -1, @StartDate), -1) )
and v1.FTranType in (1,2,10))rk;


----起始日至截止日的出库数
select * into ##ck from  (
Select v1.FBillNo ,V1.FUse ,
convert(char(20),v1.FDate,111) rq,   
u1.FItemID ,
case 
when v1.FTranType=1 then '外购入库'
when v1.FTranType=2 then '产品入库'
when v1.FTranType=10 then '其他入库'
when v1.FTranType=21 then '销售出库'
when v1.FTranType=24 then '生产领料'
when v1.FTranType=29 then '其他入库'
end  type,
u1.FQty ,u1.FAmount 
from ICStockBill v1 
inner  JOIN ICStockBillEntry u1 ON     v1.FInterID = u1.FInterID  
 where 1=1 AND ( (  v1.FCancellation = 0))
and (v1.FDate>=@StartDate and v1.FDate<= @EndDate)
and v1.FTranType in (21,24,29) ) ck;



----查询起始日当月入库的物料，在未来至截止日未出过库的信息
select cc.fitemid,cc.fnumber,cc.fname,cc.fmodel,aa.sl from  (
select fitemid itemid,sum(fqty) sl  from  ##rk group by fitemid)aa
left join (select distinct(fitemid) itemid from ##ck) bb on aa.itemid=bb.itemid 
inner join t_icitem cc on aa.itemid=cc.fitemid
where 1=1 and  bb.itemid is null order by cc.FNumber;

drop table ##ck;
drop table ##rk;

end

2、在BOS中新增直接SQL报表

3、发布到EPR