金蝶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