金蝶K3 SQL报表系列-委外核销检查表

1、创建存储过程：sp_ICStockBill_Check

create procedure [dbo].[sp_ICStockBill_Check]asset nocount ondeclare @currYear intdeclare @currPeriod intdeclare @begindate datetimedeclare @enddate datetime--查询期间--select * from  t_systemprofile where Fkey='CurrentPeriod' and Fcategory='GL'--select @currYear=Fvalue from  t_systemprofile where Fkey='CurrentYear' and Fcategory='IC'--select @currPeriod=Fvalue from  t_systemprofile where Fkey='CurrentPeriod' and Fcategory='IC' --2.取出当前年份  SELECT @currPeriod=FValue FROM t_Systemprofile WHERE FKey='CurrentPeriod' And FCategory='IC'  SELECT @currYear=FValue FROM t_Systemprofile WHERE FKey='CurrentYear' And FCategory='IC'   --3.取出当前期间的起始日期  EXECUTE GetPeriodStartEnd 0, @currPeriod, @begindate OUTPUT, @enddate OUTPUT  --委外核销检查表create table #StockBill(FInterid int,FEntryid int,FOrderInterid int,ForderEntryid int,FQty decimal(21,10))--委外核销材料明细表create table #icstockbillEntry(FStockInterid int,FStockEntryid int,FOrderInterid int,FOrderEntryid int,FItemid int,FStarandQty decimal(21,10),FScrap decimal(21,10),FQtyMust decimal(21,10),FQty decimal(21,10))--导入委外入库单--通过此控制委外入库单范围insert into #StockBill(FInterid,FEntryid,FQty,FOrderInterid,ForderEntryid)selectu1.Finterid,u1.Fentryid,u1.Fqty,u1.FOrderInterID,u1.FOrderEntryIDfrom ICStockBillEntry u1inner join ICStockBill t1 on u1.FInterID=t1.FInterIDwhere t1.FTranType=5 and ISNULL(FCheckerid,0)<>0--过滤条件and t1.Fdate>=@begindate and t1.Fdate<=@enddate ----生产委外应核销材料明细表insert into #icstockbillEntry(FStockInterid,FStockEntryid,FOrderInterid,FOrderEntryid,FItemid,FStarandQty,FScrap,FQtyMust)selectu1.FInterid,u1.FEntryid,u1.FOrderInterid,u1.ForderEntryid,t2.FItemID,t2.FQtyScrap,t2.FScrap,u1.FQty*t2.FQtyScrap*(1+t2.FScrap/100)from #StockBill u1inner join PPBOM t1 on u1.FOrderInterid=t1.FICMOInterID and u1.ForderEntryid=t1.FOrderEntryIDinner join PPBOMEntry t2 on t2.FInterID=t1.FInterID--委外实际核销未出下在投料单物料insert into #icstockbillEntry(FStockInterid,FStockEntryid,FOrderInterid,FOrderEntryid,FItemid,FStarandQty,FScrap,FQtyMust)selectu1.FDInterID,u1.FDEntryID,t2.FOrderInterID,t2.FOrderEntryID,t2.FItemID,0,0,0from ICClientVer u1left join #icstockbillEntry t1 on u1.FDInterID=t1.FStockInterid and u1.FDEntryID=t1.FStockEntryidinner join ICStockBillEntry t2 on t2.FInterID=u1.FSInterID and t2.FEntryID=u1.FSEntryID and t2.FItemID=t1.FItemidwhere t1.FStockInterid is nullgroup by u1.FDInterID,u1.FDEntryID,t2.FOrderInterID,t2.FOrderEntryID,t2.FItemID--跟新委外核销数量update u1set u1.FQty=t1.Fqtyfrom #icstockbillEntry u1inner join (select k1.FDInterID,k1.FDEntryID,k3.Fitemid,SUM(k1.Fqty) as FQty from ICClientVer k1inner join ICStockBillEntry k3 on k3.FInterID=k1.FSInterID and k3.FEntryID=k1.FSEntryIDgroup by k1.FDInterID,k1.FDEntryID,k3.Fitemid)t1 on t1.FDInterID=u1.FStockInterid and t1.FDEntryID=u1.FStockEntryid and u1.FItemid=t1.FItemID--生成报表selectt4.FBillNo 委外入库单号,v1.FEntryid 入库单行号,t4.FDate 入库日期,t3.FBillNo 委外订单号,t2.FEntryID 订单行号,t5.FNumber 产品代码,t5.FName 产品名称,t5.FModel 产品规格,t2.FQty 订单数量,t2.FStockQty 订单入库数量,t1.FQty 入库数量,t6.FNumber 材料代码,t6.FName 材料名称,t6.FModel 材料规格,u1.FStarandQty 标准用量,u1.FScrap [损耗(%)],u1.FQtyMust 应核销数量,isnull(u1.FQty,0) 实际核销数量,u1.FQtyMust-isnull(u1.FQty,0) 差异数量 from  #StockBill v1 left join #icstockbillEntry u1 on u1.FStockInterid=v1.FInterid and u1.FStockEntryid=v1.FEntryidleft join ICStockBillEntry t1 on v1.FInterid=t1.FInterID and v1.FEntryid=t1.FEntryIDleft join ICStockBill t4 on t4.FInterID=t1.FInterIDleft join ICSubContractEntry t2 on t2.FInterID=u1.FOrderInterid and t2.FEntryID=u1.FOrderEntryidleft join ICSubContract t3 on t3.FInterID=t2.FInterIDleft join t_ICItem t5 on t5.FItemID=t1.FItemIDleft join t_ICItem t6 on t6.FItemID=u1.FItemidorder by u1.FStockInterid,u1.FStockEntryiddrop table #icstockbillEntrydrop table #StockBill

2、K3查询分析工具调用：

exec sp_icstockbill_check

开发完毕。