产品版本号:金蝶kis13.0
数据库：sql server2005
操作系统:windows2008
问题描述：根据销售订单或者生产任务单关联生成采购建议的时候，系统计算的已出库数量包含了销售出库单的数量，导致了最后的生成的采购建议表中，未领料数量数据不对.
比如：有一张销售订单A的内码是170，共有8行，然后下推了销售出库单，未审核，销售订单未关闭(写入icstockbill,icstockbillentry);
有一张生产任务单B的内码刚好也是170，共有N行，部分领料,涉及到投料计划的前8行(写入icstockbill,icstockbillentry)，生产任务单正常状态，未关闭.例子中的生产任务单B和销售订单A没有关联关系。
现在我们关联这张生产任务单B所对应的销售订单B0生成采购建议，这时会发现系统计算为领料数量的时候，没有考虑到icstockbill的单据类型，把生产领料和销售出库混到一起了，计算过程跟踪代码如下，注意蓝色部分，可以发现取关联源单数据的时候没有考虑出库单的类型，而且又用了left join 关联：
IF object_id('tempdb..#Data') is not null drop table #Data ;set nocount on
Create Table #Data
(FItemID int,FBOMItemID int default 0 ,FInterID int,FEntryID int ,FBomInterID int,FBomEntryID int ,
FTranType int,
FItemPropID int,
FItemProp varchar(355),
FItemNumber varchar(355),
FItemName varchar(355),
FItemModel varchar(355),
FAuxPropID int, FAuxProp varchar(100),
FUnitID int,
FUnitName varchar(355),
FDestroyRate decimal(23, 10),FQtyDecimal int,
FNeedQty Decimal(23,10) default 0 , FSaveQty decimal(23, 10) default 0,
FCurrentQty decimal(23, 10) default 0,
FMovingQty decimal(23, 10) default 0,
FUnStockQty decimal(23, 10) default 0,FProducingQty decimal(23, 10) default 0,
FProducQty decimal(23, 10) default 0,
FMidStockQty decimal(23, 10) default 0,
FSuggestQty decimal(23, 10) default 0,
FQty decimal(23, 10) default 0,
FSourceNO varchar(355),
FSupplyNumber varchar(355),
FSupplyName varchar(355),
FMemo VarChar(355),FFinal int,FDate datetime null )
Insert Into #Data (FTranType,FItemID,FInterID,FEntryID,FSourceNO,FSuggestQty,FFinal,FDate) Values
('81','3337','1479','1','SC210716024',60,0 ,'2016-08-10' );
Insert Into #Data (FTranType,FItemID,FInterID,FEntryID,FSourceNO,FSuggestQty,FFinal,FDate) Values
('81','3409','1479','2','SC210716024',100,0 ,'2016-08-10' );
Insert Into #Data (FTranType,FItemID,FInterID,FEntryID,FSourceNO,FSuggestQty,FFinal,FDate) Values
('81','3353','1479','3','SC210716024',600,0 ,'2016-08-10' );
Insert Into #Data (FTranType,FItemID,FInterID,FEntryID,FSourceNO,FSuggestQty,FFinal,FDate) Values
('81','3233','1479','4','SC210716024',40,0 ,'2016-08-10' );
go
Insert Into #Data (FTranType,FItemID,FBOMItemID,FInterID,FEntryID,FSourceNO,FBomInterID,FBomEntryID,FSuggestQty,FAuxPropID,FDestroyRate,FFinal,fdate,FMemo)
Select v2.FTranType,v1.FItemID,u1.FItemID,v2.FInterID,v2.FEntryID,v2.FSourceNO,u1.FInterID,u1.FEntryID,(v2.FSuggestQty*u1.FQty*100)/(v1.FQty*(100-u1.FScrap)),u1.FAuxPropID,u1.FScrap,0,v2.fdate,u1.FNote From ICBOM v1
Inner Join ICBOMChild u1 On v1.FInterID=u1.FInterID Inner Join #Data v2 On v2.FItemID=v1.FItemID where v1.FUseStatus=1072 and v2.FBOMItemID=0 and v2.FTranType=81;
Insert Into #Data (FTranType,FItemID,FBOMItemID,FInterID,FEntryID,FSourceNO,FBomInterID,FBomEntryID,FSuggestQty,FAuxPropID,FDestroyRate,FFinal,fdate,FMemo)
select v2.FTranType,v1.FItemID,u1.FItemID,v2.FInterID,v2.FEntryID,v2.FSourceNO,u1.FInterID,u1.FEntryID,case when v2.FSuggestQty=V1.FQty then u1.FPlanQty else Cast((u1.FPlanQty/V1.FQty) as Decimal(23,10))*v2.FSuggestQty end,u1.FAuxPropID,u1.FScrapRate,0,v2.fdate,u1.FNote
from ICMO v1
inner join ICMOEntry u1 on v1.FInterID=u1.FInterID
inner Join #Data v2 On v2.FInterID=v1.FInterID where v2.FTranType=85;
delete from #Data where FBOMItemID=0;Update #Data set FNeedQty=FSuggestQty ;
IF object_id('tempdb..#T_Produc') is not null drop table #T_Produc;
Create table #T_Produc(FItemID int,FProducQty decimal(23, 10) default 0,FAuxPropID int)
Insert Into #T_Produc select u1.FItemID,sum(IsNull(s.FQty,0)) as FProducQty,u1.FAuxPropID
From (select DISTINCT FInterID from #Data where FTranType=85) D
inner join ICMO v1 on D.FInterID=v1.FInterID
inner join ICMOEntry u1 on v1.FInterID=u1.FInterID
left join icstockbillentry S on s.FSourceinterid = u1.finterid and s.fsourceentryid = u1.fentryid
left join icstockbill S1 on s.finterid = s1.finterid where s1.FCheckerID > 0
group by u1.FItemID,u1.FAuxPropID ;
update #Data set FAuxProp =t1.FName From t_AuxItem t1 where #Data.FAuxPropID=t1.FItemID and #Data.FBomItemID>0 declare @FBOMItemID int
declare @FAuxProp varchar(100)
declare @FAuxPropID int
declare @Data_cursor cursor
set @Data_cursor = cursor for
select FBOMItemID,FAuxProp,FAuxPropID from #Data
open @Data_cursor
fetch @Data_cursor into @FBOMItemID,@FAuxProp,@FAuxPropID
while @@fetch_status = 0
begin
Update #Data Set FMemo = (select top 1 FMemo from #Data where FBOMItemID = @FBOMItemID
and FAuxProp = @FAuxProp and FAuxPropID = @FAuxPropID)
where FBOMItemID = @FBOMItemID
and FAuxProp = @FAuxProp and FAuxPropID = @FAuxPropID
fetch next from @Data_cursor into @FBOMItemID,@FAuxProp,@FAuxPropID
End
close @Data_cursor
deallocate @Data_cursor
Insert Into #Data (FBOMItemID,FSuggestQty,FNeedQty,FAuxProp,FAuxPropID,FFinal,fdate,FMemo)
select FBOMItemID,sum(FSuggestQty),sum(FNeedQty),FAuxProp,FAuxPropID,1,min(fdate),FMemo From #Data group by FBOMItemID,FAuxProp,FAuxPropID,FMemo;Delete from #Data where FFinal = 0;
Update #Data set FItemPropID=t1.FErpClsID,FItemNumber=t1.FNumber, FItemName=t1.FName,FItemModel=t1.FModel,FUnitID=t1.FUnitID,FUnitName = t2.FName, FSaveQty=t1.FSecInv,FQtyDecimal=t1.FQtyDecimal
From T_ICItem t1,T_MeasureUnit t2 where #Data.FBomItemID=t1.FItemID and t1.FUnitID=t2.FMeasureUnitID and #Data.FBomItemID>0 Update #Data set FItemProp = (case FItemPropID when 1 then '外购' when 2 then '组装' when 3 then '自制' else '其他' end ) ;
go
Create table #T_StockQty(FItemID int,FAuxPropID int,FQty Decimal(23,10))
insert into #T_StockQty Select i.FItemID,i.FAuxPropID,Sum(i.FQty) From ICInventory i Inner Join t_Stock s On i.FStockID=s.FItemID and i.FStockID in (117,118,119,120,121,123,122) Group by i.FItemID,i.FAuxPropID
Update #Data set FCurrentQty = #T_StockQty.FQty from #T_StockQty Where #T_StockQty.FItemID =#Data.FBomItemID and #T_StockQty.FAuxPropID=#Data.FAuxPropID ; drop table #T_StockQty
go
Create table #T_MovingQty(FItemID int,FAuxPropID int,FQty Decimal(23,10),FFinal int default 0)
Insert Into #T_MovingQty select u1.FItemID,u1.FAuxPropID,sum(u1.FQty-u1.FCommitQty),0 from POOrderEntry u1
inner join POOrder v1 on u1.FInterID=v1.FInterID Where v1.FStatus IN (1,2) And u1.FQty>u1.FCommitQty and u1.FItemID in(Select FBomItemID From #Data) Group by u1.FItemID,u1.FAuxPropID
Insert Into #T_MovingQty (FItemID,FAuxPropID,FQty,FFinal) Select FItemID,FAuxPropID,sum(FQty),1 From #T_MovingQty group by FItemID,FAuxPropID
Delete From #T_MovingQty Where FFinal<>1
Update #Data set FMovingQty = #T_MovingQty.FQty From #T_MovingQty Where #Data.FBomItemID= #T_MovingQty.FItemID and #Data.FAuxPropID=#T_MovingQty.FAuxPropID and #Data.FBomItemID>0 ;drop table #T_MovingQty
go
Create table #T_UnStock(FItemID int,FAuxPropID int,FQty Decimal(23,10),FFinal int default 0)
Insert Into #T_UnStock select u1.FItemID,u1.FAuxPropID,sum(u1.FQty-u1.FCommitQty),0 from SEOrderEntry u1
inner join SEOrder v1 on u1.FInterID=v1.FInterID Where v1.FStatus IN (1,2) And u1.FQty>u1.FCommitQty and u1.FItemID in(Select FBomItemID From #Data) Group by u1.FItemID,u1.FAuxPropID
Insert Into #T_UnStock (FItemID,FAuxPropID,FQty,FFinal) Select FItemID,FAuxPropID,sum(FQty),1 From #T_UnStock group by FItemID,FAuxPropID
Delete From #T_UnStock Where FFinal<>1
Update #Data set FUnStockQty= #T_UnStock.FQty from #T_UnStock Where #Data.FBomItemID= #T_UnStock.FItemID and #Data.FBomItemID>0 and #Data.FAuxPropID=#T_UnStock.FAuxPropID;drop table #T_UnStock
go
CREATE TABLE #T_Producing(FItemID INT,FAuxPropID INT,FQty DECIMAL(23,10),FFinal INT DEFAULT 0)
INSERT INTO #T_Producing
SELECT FItemID, FAuxPropID, SUM(FPlanQty - FIssuedQty), 0 ---导致这里的数据中未领料的数据不正确
FROM (
SELECT u1.FInterID, u1.FEntryID, u1.FItemID AS FItemID, u1.FAuxPropID AS FAuxPropID, MIN(u1.FPlanQty) AS FPlanQty, SUM(ISNULL(s.FQty,0)) AS FIssuedQty
FROM ICMOEntry u1
INNER JOIN ICMO v1 ON u1.FInterID = v1.FInterID LEFT JOIN ICStockBillEntry s on s.FSourceInterID = u1.FInterID AND s.FSourceEntryID = u1.FEntryID
LEFT JOIN ICStockBill s1 on s.FInterID = s1.FInterID and s1.FCheckerID > 0
----此处没有加单据类型的限制，把销售出库和生产领料混到一起了
WHERE v1.FStatus IN (1,2) AND u1.FItemID IN(SELECT FBomItemID FROM #Data) AND v1.FBillNo NOT IN ('SC210716024','SC210716024','SC210716024','SC210716024')
GROUP BY u1.FInterID, u1.FEntryID, u1.FItemID, u1.FAuxPropID
) T2 WHERE FPlanQty - FIssuedQty > 0
GROUP BY FItemID, FAuxPropID
INSERT INTO #T_Producing (FItemID, FAuxPropID, FQty, FFinal)
SELECT FItemID, FAuxPropID, sum(FQty), 1
FROM #T_Producing
GROUP BY FItemID,FAuxPropID
DELETE FROM #T_Producing Where FFinal <> 1
UPDATE #Data SET FProducingQty= #T_Producing.FQty
FROM #T_Producing
WHERE #Data.FBomItemID = #T_Producing.FItemID AND #Data.FBomItemID > 0 AND #T_Producing.FAuxPropID = #Data.FAuxPropID
DROP TABLE #T_Producing
go
Update #Data set FSuggestQty = FSuggestQty - FCurrentQty where FBomItemID >0 Update #Data set FSuggestQty = FSuggestQty + FSaveQty where FBomItemID >0 Update #Data set FSuggestQty = FSuggestQty - FMovingQty where FBomItemID >0 Update #Data set FSuggestQty = FSuggestQty + FUnStockQty where FBomItemID >0 Update #Data set FSuggestQty = FSuggestQty + FProducingQty where FBomItemID >0 Update #Data set FProducQty=T.FProducQty
From #Data D
inner join #T_Produc T on D.FBOMItemID=T.FItemID and D.FAuxPropID =T.FAuxPropID
Update #Data set FSuggestQty = FSuggestQty - FProducQty where FBomItemID >0
Update #Data set FQty = FSuggestQty Where FBomItemID>0
Update #Data set FQty = 0,FSuggestQty=0 Where FSuggestQty<0 And FBomItemID>0
go
select * from #Data order by FItemNumber
drop table #Data
go
IF @@TRANCOUNT > 0 COMMIT TRAN
go
推荐阅读