金蝶K3 SQL报表系列-委外核销检查表
转自：https://blog.csdn.net/mamengna/article/details/111798954
1、创建存储过程：sp_ICStockBill_Check
create procedure [dbo].[sp_ICStockBill_Check] as set nocount on declare @currYear int declare @currPeriod int declare @begindate datetime declare @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) select u1.Finterid,u1.Fentryid,u1.Fqty,u1.FOrderInterID,u1.FOrderEntryID from ICStockBillEntry u1 inner join ICStockBill t1 on u1.FInterID=t1.FInterID where 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) select u1.FInterid,u1.FEntryid,u1.FOrderInterid,u1.ForderEntryid,t2.FItemID,t2.FQtyScrap,t2.FScrap,u1.FQty*t2.FQtyScrap*(1+t2.FScrap/100) from #StockBill u1 inner join PPBOM t1 on u1.FOrderInterid=t1.FICMOInterID and u1.ForderEntryid=t1.FOrderEntryID inner join PPBOMEntry t2 on t2.FInterID=t1.FInterID --委外实际核销未出下在投料单物料 insert into #icstockbillEntry (FStockInterid,FStockEntryid,FOrderInterid,FOrderEntryid,FItemid,FStarandQty,FScrap,FQtyMust) select u1.FDInterID,u1.FDEntryID,t2.FOrderInterID,t2.FOrderEntryID,t2.FItemID,0,0,0 from ICClientVer u1 left join #icstockbillEntry t1 on u1.FDInterID=t1.FStockInterid and u1.FDEntryID=t1.FStockEntryid inner join ICStockBillEntry t2 on t2.FInterID=u1.FSInterID and t2.FEntryID=u1.FSEntryID and t2.FItemID=t1.FItemid where t1.FStockInterid is null group by u1.FDInterID,u1.FDEntryID,t2.FOrderInterID,t2.FOrderEntryID,t2.FItemID --跟新委外核销数量 update u1 set u1.FQty=t1.Fqty from #icstockbillEntry u1 inner join ( select k1.FDInterID,k1.FDEntryID,k3.Fitemid,SUM(k1.Fqty) as FQty from ICClientVer k1 inner join ICStockBillEntry k3 on k3.FInterID=k1.FSInterID and k3.FEntryID=k1.FSEntryID group by k1.FDInterID,k1.FDEntryID,k3.Fitemid )t1 on t1.FDInterID=u1.FStockInterid and t1.FDEntryID=u1.FStockEntryid and u1.FItemid=t1.FItemID --生成报表 select t4.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.FEntryid left join ICStockBillEntry t1 on v1.FInterid=t1.FInterID and v1.FEntryid=t1.FEntryID left join ICStockBill t4 on t4.FInterID=t1.FInterID left join ICSubContractEntry t2 on t2.FInterID=u1.FOrderInterid and t2.FEntryID=u1.FOrderEntryid left join ICSubContract t3 on t3.FInterID=t2.FInterID left join t_ICItem t5 on t5.FItemID=t1.FItemID left join t_ICItem t6 on t6.FItemID=u1.FItemid order by u1.FStockInterid,u1.FStockEntryid drop table #icstockbillEntry drop table #StockBill
2、K3查询分析工具调用：
exec sp_icstockbill_check
