K3生成领料汇总表分析

Set NoCount On  Set Ansi_Warnings Off 
Select v2.*,v2.FQty/t2.FCoefficient As FCUUnitQty,
(Case When t1.FTrack<>81 Then v2.FPrice When t1.FTrack=81 Then v2.FPlanPrice End)*t2.FCoefficient 
As FCUUintPrice Into #ICStockBillEntry  
From ICStockBillEntry v2 Inner Join ICStockBill v1 On v1.FInterID=v2.FInterID And v1.FTranType=24 
Inner Join t_ICItem t1 On v2.FItemID=t1.FItemID
left Join t_stock ts On v2.FSCStockID=ts.FItemID
Inner Join t_MeasureUnit t2 On t1.FStoreUnitID=t2.FMeasureUnitID
 WHERE v1.FDate >='2017-04-26' And v1.FDate <='2017-12-30' And v1.FStatus>0 And v1.FCancelLation=0 
 And ts.FIncludeAccounting=1Select * Into #ICStockBill From ICStockBill v1 Where v1.FTranType=24 and  v1.FDate >='2017-04-26' And v1.FDate <='2017-12-30' And v1.FStatus>0 And v1.FCancelLation=0 
CREATE TABLE #ItemLevel( 
 FNumber1 Varchar(355),
 FName1 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, 
 '',
 FItemID,FNumber FROM t_Item
 WHERE FItemClassID=4
 AND FDetail=1  And  FItemID In (SELECT DISTINCT  v2.FItemID  FROM ICStockBill v1,ICStockBillEntry v2 
 WHERE v1.FInterID=v2.FInterID 
 AND  v1.FTranType =24 
 And v1.FDate >='2017-04-26'
 And v1.FDate <='2017-12-30')

 UPDATE t0 SET t0.FName1=t1.FName
  FROM #ItemLevel t0 left join t_Item t1 On t0.FNumber1=t1.FNumber  AND t1.FItemClassID=4 AND t1.FDetail=0 

CREATE TABLE #DATA(
FDptName Varchar(355) Null,
FName1 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,
     FQtyDecimal smallint null, 
     FPriceDecimal smallint null, 
     FQty     decimal(28,10)  null,
     FPrice   decimal(28,10)  null,
     FCUUnitQty     decimal(28,10)  null,
     FCUUnitPrice   decimal(28,10)  null,
     FAmount  decimal(28,10)  null,
     FSumSort smallint not null Default(0),
Flevel0 Decimal(10,3), 
Flevel1 Decimal(10,3), 
Flevel2 Decimal(10,3), 
Flevel3 Decimal(10,3), 
Flevel4 Decimal(10,3), 
Flevel5 Decimal(10,3), 
Flevel6 Decimal(10,3), 

      FID  int not null identity)INSERT INTO #DATA 
SELECT  t3.FName,tt1.FName1,t1.FNumber,'','','','','',6,4, Sum (v2.FQty),
Max(Case When t1.FTrack=81 Then v2.FPlanPrice Else v2.FPrice End),
Sum (v2.FCUUnitQty),Max(v2.FCUUintPrice),
Sum(Case When t1.FTrack=81 Then v2.FPlanAmount Else v2.FAmount End),
CASE   WHEN   Grouping(t3.FName)=1 THEN 101   WHEN   Grouping(tt1.FName1)=1 THEN 106
  WHEN   Grouping(t1.FNumber)=1 THEN 107  ELSE   0 END 
,0 ,0 ,0 ,0 ,0 ,0 ,0  FROM #ICStockBill v1 inner join #ICStockBillEntry v2 on v1.FInterID=v2.FInterID 
inner join t_ICItem t1 on v2.FItemID=t1.FItemID 
inner join t_Stock t2 on v2.FSCStockID=t2.FItemID
inner join t_Department t3 on v1.FDeptID=t3.FItemID
LEFT join cbCostObj t5 on v2.FCostObjID=t5.FItemID
left join ICMO t4   on v2.FSourceTranType=85 and v2.FSourceInterID=t4.FInterID
left join ICMO t41  on v2.FSourceTranType = 24 and v2.FICMOInterID = t41.FInterID
left join t_StockPlace t11 on v2.FDCSPID=t11.FSPID
,#ItemLevel tt1
 WHERE 1=1 

 AND t1.FItemID=tt1.FItemID AND t3.FNumber>='CSXM.004' AND t3.FNumber<='CSXM.004'
 And v1.FStatus>0 And v1.FCancelLation=0 
 Group By  t3.FName,tt1.FName1,t1.FNumber WITH ROLLUP
 Update t1 Set t1.FPrice=0  From #DATA t1 Where t1.FQty=0 
 Update t1 Set t1.FCUUnitPrice=0  From #DATA t1 Where t1.FCUUnitQty=0 
 Update t1 Set t1.FPrice=FAmount/FQty  From #DATA t1 Where t1.FQty<>0 
 Update t1 Set t1.FCUUnitPrice=FAmount/FCUUnitQty  From #DATA t1 Where t1.FCUUnitQty<>0 
 Update t1 Set t1.FPrice=t2.FPlanPrice From #Data t1 Inner Join t_ICItem t2  On t1.FNumber=t2.FNumber 
 Inner Join t_MeasureUnit t3 On t2.FUnitGroupID=t3.FUnitGroupID Where t2.FTrack=81 And FQty>0 
 Update t1 Set t1.FCUUnitPrice=t2.FPlanPrice*t3.FCoefficient From #Data t1 Inner Join t_ICItem t2  On t1.FNumber=t2.FNumber 
 Inner Join t_MeasureUnit t3 On t2.FUnitGroupID=t3.FUnitGroupID Where t2.FTrack=81 And FCUUnitQty>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 t3.FStandard=1
 And t2.FUnitID=t3.FMeasureUnitID 
 And t2.FStoreUnitID=t4.FMeasureUnitID 


SELECT IDENTITY(INT,1,1) AS FLevel0,FDPTNAME 
 INTO #LEVEL0 FROM #DATA
Where FSumSort=106


UPDATE a3 SET a3.FLevel0=lv.FLevel0
FROM #DATA a3
INNER JOIN #LEVEL0 lv ON ISNULL(lv.FDPTNAME,0)=ISNULL(a3.FDPTNAME,0)

Where  Not( FSumSort=101)
DROP TABLE #LEVEL0 
 Update  #DATA set FLevel0=(Select max(ISNULL(FLevel0,0))+1 From #DATA) Where 1=1 AND FSumSort=101

SELECT IDENTITY(INT,1,1) AS FLevel1,FDPTNAME , FNAME1
 INTO #LEVEL1 FROM #DATA
Where FSumSort=107


ORDER BY  FLEVEL0 
UPDATE a3 SET a3.FLevel1=lv.FLevel1
FROM #DATA a3
INNER JOIN #LEVEL1 lv ON ISNULL(lv.FDPTNAME,0)=ISNULL(a3.FDPTNAME,0) AND ISNULL(lv.FNAME1,0)=ISNULL(a3.FNAME1,0)

Where  Not( FSumSort=101)
DROP TABLE #LEVEL1 

 Update #DATA Set FLevel0=FLevel0+0.9   Where FSumSort=106
 Update #DATA Set FLevel1=FLevel1+0.9   Where FSumSort=107
Update #Data Set  FName1=FName1+'(小计)'  WHERE FSumSort=107
Update #Data Set FDptName=FDptName+'(小计)' WHERE FSumSort=106
Update #Data Set FDptName='合计' WHERE FSumSort=101
SELECT * FROM #DATA WHERE (FQty <>0  OR FAmount<>0)  
 Order By  FLevel0,  FLevel1

 DROP TABLE #DATA  
 DROP TABLE #ItemLevel

Drop Table #ICStockBill
Drop Table #ICStockBillEntry