金蝶 K3 库存账龄分析表 数据不一致

2021年9月5日 4点热度 0条评论 来源: DAHOUZI.CN

用户反馈某个物料在库存账龄分析表内的统计后存在超过1080天的数量,但实际该料只是在一年前导入,故不可能存在超过1080天的数据。

原因分析:用户没勾选包含调拨单的数据,报表统计时将调拨单排除在外。造成即时库存数据大于流水单据(不含调拨单)数据,多出来的部分系统将其归类到最后一项天数。

Set NoCount On SET ANSI_WARNINGS OFF Create Table #Happen2( FItemID int Null, FStockID int Null, FBatchNo NVARCHAR(200), FQty decimal(28,10) Null, FCUUnitQty decimal(28,10) Null, FQty1 Decimal(28,10), FCUUnitQty1 Decimal(28,10), FAmount1 Decimal(28,10), FQty2 Decimal(28,10), FCUUnitQty2 Decimal(28,10), FAmount2 Decimal(28,10), FQty3 Decimal(28,10), FCUUnitQty3 Decimal(28,10), FAmount3 Decimal(28,10), FTemp bit ,FPrice Decimal(28,10),FCUPrice Decimal(28,10),FAmount Decimal(28,10)) Create Table #Happen( FItemID int Null, FStockID int Null, FBatchNo NVARCHAR(200), FQty decimal(28,10) Null, FCUUnitQty decimal(28,10) Null, FQty1 Decimal(28,10), FCUUnitQty1 Decimal(28,10), FAmount1 Decimal(28,10), FQty2 Decimal(28,10), FCUUnitQty2 Decimal(28,10), FAmount2 Decimal(28,10), FQty3 Decimal(28,10), FCUUnitQty3 Decimal(28,10), FAmount3 Decimal(28,10), FTemp bit ,FPrice Decimal(28,10),FCUPrice Decimal(28,10),FAmount Decimal(28,10)) Create Table #Happen1( FItemID int Null, FStockID int Null, FBatchNo NVARCHAR(200), FQty decimal(28,10) Null, FCUUnitQty decimal(28,10) Null, FQty1 Decimal(28,10), FCUUnitQty1 Decimal(28,10), FQty2 Decimal(28,10), FCUUnitQty2 Decimal(28,10), FQty3 Decimal(28,10), FCUUnitQty3 Decimal(28,10), FTemp bit ,FPrice Decimal(28,10),FCUPrice Decimal(28,10),FAmount Decimal(28,10) ) --ICBal存货余额,先分仓库统计流水 Insert Into #Happen1 Select t5.FTranType,t5.FBillNo,t5.FDate,t1.FItemID,t2.FItemID As FStockID,t6.FBatchNo,0,0, (Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-359,'2018-07-16'),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,'2018-07-16'),101)) Then t5.FRob*t6.FQty Else 0 End) As FQty1, ((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-359,'2018-07-16'),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,'2018-07-16'),101)) Then t5.FRob*t6.FQty Else 0 End))/t7.FCoefficient As FCUUnitQty1, (Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-1079,'2018-07-16'),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,'2018-07-16'),101)) Then t5.FRob*t6.FQty Else 0 End) As FQty2, ((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-1079,'2018-07-16'),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,'2018-07-16'),101)) Then t5.FRob*t6.FQty Else 0 End))/t7.FCoefficient As FCUUnitQty2, (Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-1080,'2018-07-16'),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-1080,'2018-07-16'),101)) Then t5.FRob*t6.FQty Else 0 End) As FQty3, ((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-1080,'2018-07-16'),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-1080,'2018-07-16'),101)) Then t5.FRob*t6.FQty Else 0 End))/t7.FCoefficient As FCUUnitQty3, 1 ,ISNULL(((SELECT t12.FBegBal/t12.FBegQty FROM t_ICItem t11 INNER JOIN (SELECT FItemID,SUM(FBegBal) AS FBegBal,SUM(FBegQty) AS FBegQty FROM ICBal t13 Where t13.FItemID = t6.FItemID And t13.FYear = 2018 And t13.FPeriod = 7 GROUP BY t13.FItemID) t12 ON t11.FItemID=t12.FItemID AND t12.FBegBal>0 AND t12.FBegQty>0) ),0),0,0 From t_ICItem t1 Join ICStockBill t5 On (t5.FStatus > 0 Or (t5.FUpStockWhenSave > 0 And t5.FCancellation <1 )) Join ICStockBillEntry t6 On t5.FInterID=t6.FInterID Left Join t_MeasureUnit t7 On t1.FStoreUnitID=t7.FMeasureUnitID Left Join t_Stock t2 On t2.FItemID = (case when t5.ftrantype=24 then t6.FSCStockID else t6.FDCStockID end) Where t1.FItemID = t6.FItemID And ((t5.FTrantype In (1,2,5,10,40) And t5.FRob =1) Or (t5.FTrantype In(21,24,28,29) And t5.FRob=-1)) And t1.FNumber>='05.22.01.00049' And t1. FNumber<='05.22.01.00049' --期初余额ICInvInitial,再统计期初余额,不懂为什么这一步 Insert Into #Happen1 Select t1.FItemID,t2.FItemID As FStockID,t6.FBatchNo,0,0, (Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-359,'2018-07-16'),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,'2018-07-16'),101)) Then t6.FBegQty Else 0 End) As FQty1, ((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-359,'2018-07-16'),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,'2018-07-16'),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient As FCUUnitQty1, (Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-1079,'2018-07-16'),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,'2018-07-16'),101)) Then t6.FBegQty Else 0 End) As FQty2, ((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-1079,'2018-07-16'),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,'2018-07-16'),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient As FCUUnitQty2, (Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-1080,'2018-07-16'),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-1080,'2018-07-16'),101)) Then t6.FBegQty Else 0 End) As FQty3, ((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-1080,'2018-07-16'),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-1080,'2018-07-16'),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient As FCUUnitQty3, 1 ,ISNULL(((SELECT t12.FBegBal/t12.FBegQty FROM t_ICItem t11 INNER JOIN (SELECT FItemID,SUM(FBegBal) AS FBegBal,SUM(FBegQty) AS FBegQty FROM ICBal t13 Where t13.FItemID = t6.FItemID And t13.FYear = 2018 And t13.FPeriod = 7 GROUP BY t13.FItemID) t12 ON t11.FItemID=t12.FItemID AND t12.FBegBal>0 AND t12.FBegQty>0) ),0),0,0 From t_ICItem t1 Join ICInvInitial t6 On t1.FItemID = t6.FItemID Left Join t_MeasureUnit t7 On t1.FStoreUnitID=t7.FMeasureUnitID Left Join t_Stock t2 On t2.FItemID = t6.FStockID Where 1=1 And t1.FNumber>='05.22.01.00049' And t1. FNumber<='05.22.01.00049' --统计即时库存的数据,最终以此为准 CREATE TABLE #InventoryHanppen (FItemID INT NOT NULL, FStockID INT NULL, FBatchNo Varchar(255) NULL, FQty DECIMAL(28,10) NOT NULL, FAmount DECIMAL(28,10) NOT NULL ) SELECT * FROM #InventoryHanppen --ICInvBal存货余额 INSERT INTO #InventoryHanppen(FItemID,FStockID,FBatchNo,FQty,FAmount) SELECT u1.FItemID,u1.FStockID,u1.FBatchNo,u1.FBegQty,u1.FBegBal--, t2.FName FROM t_ICItem t1 INNER JOIN ICInvBal u1 ON t1.FItemID=u1.FItemID LEFT JOIN t_Stock t2 ON t2.FItemID = u1.FStockID WHERE u1.FYear=2018 AND u1.FPeriod=7 And t1.FNumber>='05.22.01.00049' And t1. FNumber<='05.22.01.00049' INSERT INTO #InventoryHanppen(FItemID,FStockID,FBatchNo,FQty,FAmount) SELECT u1.FItemID,t2.FItemID,u1.FBatchNo, CASE WHEN v1.FTranType IN (1,2,5,10,40,41,101,102) OR (v1.FTranType=100 AND v1.FBillTypeID=12542) THEN u1.FQty ELSE -1 * u1.FQty END, CASE WHEN v1.FTranType IN (1,2,5,10,40,101,102) OR (v1.FTranType=100 AND v1.FBillTypeID=12542) THEN u1.FAmount WHEN v1.FTranType=41 THEN u1.FAmtRef ELSE -1 * u1.FAmount END--,t2.FName FROM t_ICItem t1 INNER JOIN ICStockBillEntry u1 ON t1.FItemID=u1.FItemID INNER JOIN ICStockBill v1 ON u1.FInterID=v1.FInterID LEFT JOIN t_Stock t2 ON ((v1.FTrantype=24 AND u1.FSCStockID=t2.FItemID) OR (v1.FTranType IN (1,2,5,10,21,41,28,29,43,40,100,101,102) AND u1.FDCStockID=t2.FItemID )) WHERE v1.FDate>='2018/07/01' AND v1.FDate<='2018/07/16' And t1.FNumber>='05.22.01.00049' And t1. FNumber<='05.22.01.00049' AND v1.Ftrantype In (1,2,5,10,21,24,41,28,29,43,40,100,101,102) AND (v1.FStatus > 0 Or (v1.FUpStockWhenSave > 0 And v1.FCancellation <1 )) INSERT INTO #InventoryHanppen(FItemID,FStockID,FBatchNo,FQty,FAmount) SELECT u1.FItemID,t2.FItemID,u1.FBatchNo,-1 * u1.FQty ,-1 * u1.FAmount FROM t_ICItem t1 INNER JOIN ICStockBillEntry u1 ON t1.FItemID=u1.FItemID INNER JOIN ICStockBill v1 ON u1.FInterID=v1.FInterID LEFT JOIN t_Stock t2 ON u1.FSCStockID=t2.FItemID WHERE v1.FDate>='2018/07/01' AND v1.FDate<='2018/07/16' And t1.FNumber>='05.22.01.00049' And t1. FNumber<='05.22.01.00049' AND v1.Ftrantype=41 AND (v1.FStatus > 0 Or (v1.FUpStockWhenSave > 0 And v1.FCancellation <1 )) --TRUNCATE TABLE #InventoryHanppen SELECT a.FItemID,FStockID,FBatchNo,SUM(FQty) AS FQty,SUM(FAmount) AS FAmount,dbo.t_Stock.FName FROM #InventoryHanppen a LEFT JOIN t_Stock ON t_Stock.FItemID = a.FStockID GROUP BY a.FItemID,FStockID,FBatchNo,FName SELECT FItemID,FStockID,FBatchNo,SUM(FQty) AS FQty,SUM(FAmount) AS FAmount INTO #INVENTORY FROM #InventoryHanppen GROUP BY FItemID,FStockID,FBatchNo SELECT * FROM #INVENTORY DROP TABLE #InventoryHanppen --DROP TABLE #INVENTORY Insert Into #Happen1 Select t1.FItemID,t2.FItemID As FStockID,t3.FBatchNo,(t3.FQTY) As FQTY,(t3.FQTY)/t7.FCoefficient As FCUUnitQty,0,0,0,0,0,0,1 ,ISNULL(((SELECT t12.FBegBal/t12.FBegQty FROM t_ICItem t11 INNER JOIN (SELECT FItemID,SUM(FBegBal) AS FBegBal,SUM(FBegQty) AS FBegQty FROM ICBal t13 Where t13.FItemID = t3.FItemID And t13.FYear = 2018 And t13.FPeriod = 7 GROUP BY t13.FItemID) t12 ON t11.FItemID=t12.FItemID AND t12.FBegBal>0 AND t12.FBegQty>0) ),0),0,0 From t_ICItem t1 Join #INVENTORY t3 On t1.FItemID = t3.FItemID Left Join t_MeasureUnit t7 On t1.FStoreUnitID=t7.FMeasureUnitID Left Join t_Stock t2 On t2.FItemID = t3.FStockID Where 1=1 And t1.FNumber>='05.22.01.00049' And t1. FNumber<='05.22.01.00049' Insert Into #Happen2 Select t1.FITEMID,0,t1.FBatchNo,Sum(FQTY)As FQTY,Sum(FCUUnitQTY)As FCUUnitQTY, Sum(fqty1) As FQty1 ,Sum(fCUUnitqty1) As FCUUnitQty1 ,0,Sum(fqty2) As FQty2 ,Sum(fCUUnitqty2) As FCUUnitQty2 ,0,Sum(fqty3) As FQty3 ,Sum(fCUUnitqty3) As FCUUnitQty3 ,0,1,Min(FPrice),case Sum(FCUUnitQTY) when 0 then 0 else (Min(FPrice)*Sum(FQTY))/Sum(FCUUnitQTY) end,Min(FPrice)*Sum(FQTY) From #Happen1 t1 INNER JOIN t_ICItem t2 ON t1.FItemID=t2.FItemID GROUP By t1.FITEMID,t1.FStockID,t1.FBatchNo --如果统计的360天内的账龄数量大于库存,那么可以肯定多出来那部分已经出库,下方同理 Delete From #Happen2 Where FQty<=0 Update #Happen2 Set FQty1= FQty,FQty2=0 ,FQty3=0 Where FQty-FQty1<0 Update #Happen2 Set FQty2= FQty-FQty1,FQty3=0 Where FQty-FQty1-FQty2<0 Update #Happen2 Set FQty3= FQty-FQty1-FQty2 Update #Happen2 Set FAmount1=FQty1* FPrice,FAmount2=FQty2* FPrice,FAmount3=FQty3* FPrice Update #Happen2 Set FCUUnitQty1= FCUUnitQty,FCUUnitQty2=0 ,FCUUnitQty3=0 Where FCUUnitQty-FCUUnitQty1<0 Update #Happen2 Set FCUUnitQty2= FCUUnitQty-FCUUnitQty1 ,FCUUnitQty3=0 Where FCUUnitQty-FCUUnitQty1-FCUUnitQty2<0 Update #Happen2 Set FCUUnitQty3= FCUUnitQty-FCUUnitQty1 -FCUUnitQty2 SELECT * FROM #Happen Insert Into #HAPPEN Select t1.FITEMID,0,t1.FBatchNo,Sum(FQTY)As FQTY,Sum(FCUUnitQTY)As FCUUnitQTY, Sum(fqty1) As FQty1 ,Sum(fCUUnitqty1) As FCUUnitQty1 ,Sum(FAmount1) As FAmount1,Sum(fqty2) As FQty2 ,Sum(fCUUnitqty2) As FCUUnitQty2 ,Sum(FAmount2) As FAmount2,Sum(fqty3) As FQty3 ,Sum(fCUUnitqty3) As FCUUnitQty3 ,Sum(FAmount3) As FAmount3,1,Min(FPrice),case Sum(FCUUnitQTY) when 0 then 0 else (Min(FPrice)*Sum(FQTY))/Sum(FCUUnitQTY) end,Min(FPrice)*Sum(FQTY) From #Happen2 t1 INNER JOIN t_ICItem t2 ON t1.FItemID=t2.FItemID GROUP By t1.FITEMID,t1.FBatchNo HAVING (SUM(FQTY)>0) SET NOCOUNT ON CREATE TABLE #ItemLevel( FNumber1 Varchar(355), FName1 Varchar(355), FNumber2 Varchar(355), FName2 Varchar(355), FNumber3 Varchar(355), FName3 Varchar(355), FItemID int, FNumber Varchar(355)) INSERT INTO #ItemLevel SELECT CASE WHEN CHARINDEX('.',FFullNumber)-1= -1 or FLevel<2 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber)-1) END, '', CASE WHEN CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)-1= -1 or FLevel<3 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)-1) END, '', CASE WHEN CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)+1)-1= -1 or FLevel<4 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)+1)-1) END, '', FItemID,FNumber FROM t_Item WHERE FItemClassID=4 AND FDetail=1 AND FNumber>='05.22.01.00049' AND FNumber<='05.22.01.00049' AND FItemID In (Select Distinct FItemID from #Happen ) UPDATE t0 SET t0.FName1='[' + t1.FNumber + ']'+ t1.FName,t0.FName2='[' + t2.FNumber + ']'+ t2.FName,t0.FName3='[' + t3.FNumber + ']'+ t3.FName FROM #ItemLevel t0 left join t_Item t1 On t0.FNumber1=t1.FNumber AND t1.FItemClassID=4 AND t1.FDetail=0 left join t_Item t2 On t0.FNumber2=t2.FNumber AND t2.FItemClassID=4 AND t2.FDetail=0 left join t_Item t3 On t0.FNumber3=t3.FNumber AND t3.FItemClassID=4 AND t3.FDetail=0 --最后汇总 CREATE TABLE #DATA( FName1 Varchar(355) Null, FName2 Varchar(355) Null, FName3 Varchar(355) Null, FNumber Varchar(355) null, FShortNumber Varchar(355) null, FName Varchar(355) null, FModel Varchar(355) null, FUnitName Varchar(355) null, FCUUnitName Varchar(355) null, FBatchNo NVarchar(200) null, FQtyDecimal smallint null, FPriceDecimal smallint null, FQty Decimal(28,10) Null, FCUUnitQty Decimal(28,10) Null, FPrice Decimal(28,10) NULL, FCUPrice Decimal(28,10) Null, FAmount Decimal(28,10) Null, FQty1 Decimal(28,10), FCUUnitQty1 Decimal(28,10), FAmount1 Decimal(28,10), FQty2 Decimal(28,10), FCUUnitQty2 Decimal(28,10), FAmount2 Decimal(28,10), FQty3 Decimal(28,10), FCUUnitQty3 Decimal(28,10), FAmount3 Decimal(28,10), FSumSort smallint not null Default(0), FID int IDENTITY) INSERT INTO #DATA SELECT tt1.FName1,tt1.FName2,tt1.FName3,t1.FNumber, '','','','','','',6,4,Sum(FQty),Sum(FCUUnitQty), case Sum(FQty) when 0 then 0 else Sum(FAmount)/Sum(FQty) end,(CASE Sum(FCUUnitQty) WHEN 0 THEN 0 ELSE Sum(FAmount)/Sum(FCUUnitQty) END), sum(FAmount), SUM(FQty1), SUM(FCUUnitQty1), SUM(FAmount1), SUM(FQty2), SUM(FCUUnitQty2), SUM(FAmount2), SUM(FQty3), SUM(FCUUnitQty3), SUM(FAmount3), CASE WHEN Grouping(tt1.FName1)=1 THEN 106 WHEN Grouping(tt1.FName2)=1 THEN 107 WHEN Grouping(tt1.FName3)=1 THEN 108 WHEN Grouping(t1.FNumber)=1 THEN 109 ELSE 0 END FROM #Happen v2 Inner Join t_ICItem t1 On v2.FItemID=t1.FItemID Left Join t_Stock t2 On v2.FStockID=t2.FItemID Inner Join #ItemLevel tt1 On t1.FItemID=tt1.FItemID Where 1=1 Group By tt1.FName1,tt1.FName2,tt1.FName3,t1.FNumber WITH ROLLUP Having Sum(FQty)>0 Update t1 Set t1.FName=t2.FName,t1.FShortNumber=t2.FShortNumber,t1.FModel=t2.FModel, t1.FUnitName=t3.FName,t1.FCUUnitName=t4.FName ,t1.FQtyDecimal=t2.FQtyDecimal,t1.FPriceDecimal=t2.FPriceDecimal From #DATA t1,t_ICItem t2,t_MeasureUnit t3,t_MeasureUnit t4 Where t1.FNumber=t2.FNumber And t2.FUnitGroupID=t3.FUnitGroupID And t2.FStoreUnitID=t4.FMeasureUnitID And t3.FStandard=1 Update #Data Set FName1=isnull(FName1,'')+'(小计)' WHERE FSumSort=107 Update #Data Set FName2=isnull(FName2,'')+'(小计)' WHERE FSumSort=108 Update #Data Set FName3=isnull(FName3,'')+'(小计)' WHERE FSumSort=109 Update #Data Set FName1='合计' WHERE FSumSort=106 Update #Data Set FSumSort=101 WHERE FSumSort=106 DELETE #DATA WHERE FSumSort = 1000 UPDATE #DATA SET FBatchNo = '' WHERE FSumSort <> 0 DELETE #DATA WHERE FSumSort = 0 SELECT * FROM #DATA WHERE FSumSort>100 Order by FID DROP TABLE #DATA DROP TABLE #ItemLevel Drop Table #Happen Drop Table #Happen1 Drop Table #Happen2 DROP TABLE #INVENTORY 
    原文作者:DAHOUZI.CN
    原文地址: https://blog.csdn.net/lglglgl/article/details/81082783
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系管理员进行删除。