金蝶K3 SQL报表系列-生产在线材料查询

1、创建存储过程，代码如下：

create procedure [dbo].[rk_sp_wip_status]
@enddate datetime
as


--declare @enddate datetime

--set @enddate='2012-06-30'

select 
u1.FBillNo  任务单号,
u1.FCheckDate 下单日期,
case when u1.FStatus=0 then '计划' when u1.FStatus=3 then '结案' when u1.FStatus in (1,2) then '下达' else '' end as 状态,
u11.FNumber 产品代码,
u11.FName 产品名称,
u11.FModel 产品规格,
u1.FQty 生产数量,
t3.FBegStockInQty 截止入库数量,
t3.FEndStockInQty 期后入库数量,
t21.FNumber 材料代码,
t21.FName 材料名称,
t21.FModel 材料规格,
t22.Fname 领料类型,
t23.Fname 发料仓库,
t2.FqtyScrap 标准用量,
case when t2.FMaterielType=376 then -t2.FQtyMust else t2.FQtyMust end 需发料数量,
t2.FDiscardQty 报废数量,
t4.FBegStockOutQty  截止领料数量,
t4.FEndStockOutQty 期后领料数量,
t5.FBegScrapQty 报废数量,
t5.FEndScrapQty 期末报废数量,
isnull(t4.FBegStockOutQty,0)-((case when t2.FMaterielType=376 then -t2.FQtyMust else t2.FQtyMust end)/u1.FQty)*isnull(t3.FBegStockInQty,0)-isnull(t5.FBegScrapQty,0) 在线数量
from icmo u1
inner join t_icitem u11 on u11.FItemID=u1.FItemID
inner join PPBOM t1 on u1.FInterID=t1.FICMOInterID
inner join PPBOMEntry t2 on t1.FInterID=t2.FInterID
inner join t_ICItem t21 on t21.FItemID=t2.FItemID
left join t_submessage t22 on t22.Finterid=t2.FMaterielType
left join t_stock t23 on t23.Fitemid=t2.Fstockid
left join 
(
	select
	t1.FICMOInterID,
	sum(case when u1.FDate<@enddate then t1.FQty else 0 end) as FBegStockInQty,
	sum(case when u1.FDate>=@enddate then t1.FQty else 0 end) as FEndStockInQty
	 from ICStockBill u1
	inner join ICStockBillEntry t1 on u1.FInterID=t1.FInterID
	where u1.FCancellation=0 and u1.FTranType=2
	group by FICMOInterID
) t3 on t3.FICMOInterID=u1.FInterID
left join 
(
	select
	t1.FICMOInterID,
	t1.FPPBomEntryID,
	sum(case when u1.FDate<@enddate then t1.FQty else 0 end) as FBegStockOutQty,
	sum(case when u1.FDate>=@enddate then t1.FQty else 0 end) as FEndStockOutQty
	 from ICStockBill u1
	inner join ICStockBillEntry t1 on u1.FInterID=t1.FInterID
	where u1.FCancellation=0 and u1.FTranType=24
	group by FICMOInterID,t1.FPPBomEntryID
) t4 on t4.FICMOInterID=t2.FICMOInterID and t4.FPPBomEntryID=t2.FEntryID
left join 
(
	select 
	t4_1.Fsourceinterid,
	sum(case when t4_2.FDate<@enddate then t4_1.FQty else 0 end) as FBegScrapQty,
	sum(case when t4_2.FDate>=@enddate then t4_1.FQty else 0 end) as FEndScrapQty,
	t4_1.FSourceEntryid,
	t4_1.FSourceTranType
	from 
	ICItemScrapEntry t4_1
	inner join ICItemScrap t4_2 on t4_1.finterid=t4_2.finterid
	where t4_2.Fdate<@enddate and t4_2.Fstatus=1 
	group by t4_1.Fsourceinterid,t4_1.FSourceEntryid,t4_1.FSourceTranType



) t5  on t5.FSourceInterid=t1.FICMOInterid and t5.FSourceEntryid=t2.FEntryID 
where 
(
(
(
u1.FCloseDate>=@enddate
or u1.FInterID in (
select FICMOInterID from ICStockBillEntry u1 
inner join ICStockBill t1 on u1.FInterID=t1.FInterID 
where t1.FCancellation=0 
and t1.FDate>=@enddate)
)
and u1.FStatus=3
)
 or u1.FStatus in (1,2)
 ) and u1.FCheckDate<@enddate
order by u1.FBillNo

2、K3调用存储视图：

exec rk_sp_wip_status '########'

开发完毕