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

195 阅读 0 评论 129 点赞

我是靠谱客的博主生动老师，这篇文章主要介绍金蝶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