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
K3生成领料汇总表分析
最新推荐文章于 2022-09-23 15:24:36 发布