K3 Cloud成本计算单

CREATE TABLE #TM_CB_COSTCALBILLRPT (FPRODUCTDIMEID INT not null DEFAULT 0, FMATERIALID INT not null DEFAULT 0, FEXPTYPE CHAR (1) not null DEFAULT '0', FID INT not null DEFAULT 0, FCOSTITEMID INT not null DEFAULT 0, FEXPENSESITEMID INT not null DEFAULT 0, FORDERBILLNO NVARCHAR (255) not null DEFAULT ' ', FYEARPERIOD NVARCHAR (160) not null DEFAULT ' ', FYEARPERIODSEQ INT not null DEFAULT 0, FBILLNOSEQ NVARCHAR (200) not null DEFAULT ' ', FORDERBILLSEQ NVARCHAR (160) not null DEFAULT ' ', FPRODUCTEDTYPE NVARCHAR (80) not null DEFAULT ' ', FPRODUCTNO NVARCHAR (255) not null DEFAULT ' ', FBILLSEQ NVARCHAR (160) not null DEFAULT ' ', FPRODUCTID NVARCHAR (255) not null DEFAULT ' ', FPRODUCTID_FNUMBER VARCHAR (255) not null DEFAULT ' ', FPRODUCTID_FNAME NVARCHAR (255) not null DEFAULT ' ', FCOSTCENTERID NVARCHAR (255) not null DEFAULT ' ', FCOSTITEMID_FNUMBER VARCHAR (255) not null DEFAULT ' ', FCOSTITEMID_FNAME NVARCHAR (255) not null DEFAULT ' ', FSPECIFICATION NVARCHAR (255) not null DEFAULT ' ', FCOSTCENTERNUMBER VARCHAR (255) not null DEFAULT ' ', FCOSTCENTERNAME NVARCHAR (255) not null DEFAULT ' ', FBOMNUMBERFIELD_FNUMBER VARCHAR (255) not null DEFAULT ' ', FBATCHFIELD_FNAME NVARCHAR (255) not null DEFAULT ' ', FMTONO NVARCHAR (255) not null DEFAULT ' ', FAUXPROPID INT not null DEFAULT 0, FAIDPROPERTYFIELD_FNAME NVARCHAR (255) not null DEFAULT ' ', FBASICUNITFIELD_FNAME NVARCHAR (255) not null DEFAULT ' ', FPLANQTY DECIMAL (23, 10) not null DEFAULT 0, FMATERIALFIELD_FNUMBER VARCHAR (255) not null DEFAULT ' ', FMATERIALFIELD_FNAME NVARCHAR (255) not null DEFAULT ' ', FEXPENSEITEMFIELD_FNUMBER VARCHAR (255) not null DEFAULT ' ', FEXPENSEITEMFIELD_FNAME NVARCHAR (255) not null DEFAULT ' ', FQTYDIGITS INT not null DEFAULT 0, FAMOUNTDIGITS INT not null DEFAULT 0, FBEGINQTY DECIMAL (23, 10) not null DEFAULT 0, FBEGINAMOUNT DECIMAL (23, 10) not null DEFAULT 0, FCURRINPUTQTY DECIMAL (23, 10) not null DEFAULT 0, FCURRINPUTAMOUNT DECIMAL (23, 10) not null DEFAULT 0, FADJCURRINPUTQTY DECIMAL (23, 10) not null DEFAULT 0, FADJCURRINPUTAMOUNT DECIMAL (23, 10) not null DEFAULT 0, FSUMCURRINPUTQTY DECIMAL (23, 10) not null DEFAULT 0, FSUMCURRINPUTAMOUNT DECIMAL (23, 10) not null DEFAULT 0, FSUMINPUTQTY DECIMAL (23, 10) not null DEFAULT 0, FSUMINPUTAMOUNT DECIMAL (23, 10) not null DEFAULT 0, FENDQTY DECIMAL (23, 10) not null DEFAULT 0, FENDAMOUNT DECIMAL (23, 10) not null DEFAULT 0, FCOMPLETEQTY DECIMAL (23, 10) not null DEFAULT 0, FPRODUCTUSEQTY DECIMAL (23, 10) not null DEFAULT 0, FPRODUCTUSECOST DECIMAL (23, 10) not null DEFAULT 0, FCOMPLETEAMOUNT DECIMAL (23, 10) not null DEFAULT 0, FSUMQUALIFIEDINQTY DECIMAL (23, 10) not null DEFAULT 0, FSUMQUALIFIEDINAMOUNT DECIMAL (23, 10) not null DEFAULT 0, FSUMDYSINQTY DECIMAL (23, 10) not null DEFAULT 0, FSUMDYSINAMOUNT DECIMAL (23, 10) not null DEFAULT 0, FSUMWASTEINQTY DECIMAL (23, 10) not null DEFAULT 0, FSUMWASTEINAMOUNT DECIMAL (23, 10) not null DEFAULT 0, FSUMCOMPLETEQTY DECIMAL (23, 10) not null DEFAULT 0, FSUMPRODUCTUSECOST DECIMAL (23, 10) not null DEFAULT 0, FSUMPRODUCTUSEQTY DECIMAL (23, 10) not null DEFAULT 0, FSUMCOMPLETEAMOUNT DECIMAL (23, 10) not null DEFAULT 0)

CREATE INDEX I332455749067420baaea308fa4033 ON #TM_CB_COSTCALBILLRPT (FPRODUCTDIMEID)

CREATE INDEX If54ae97a97364b9cbf887512eebad ON #TM_CB_COSTCALBILLRPT (FCOSTITEMID, FID, FBILLNOSEQ)

drop table #TM_CB_COSTCALBILLRPT

exec sp_executesql N'INSERT INTO #TM_CB_COSTCALBILLRPT (FID, FYearPeriod, FYearPeriodSeq, FOrderBillNo, FOrderBillSeq, 
FPRODUCTEDTYPE, FCOSTCENTERID, FPRODUCTID, FProductDimeId, FPRODUCTNO, FBillNoSeq, FBILLSeq, FPRODUCTID_FNUMBER, FPRODUCTID_FNAME, FSPECIFICATION, 
FCOSTCENTERNUMBER, FCOSTCENTERNAME, FBATCHFIELD_FNAME, FMTONO, FBOMNUMBERFIELD_FNUMBER, FAuxPropId, FBASICUNITFIELD_FNAME, FQTYDIGITS, FAMOUNTDIGITS, 
FPlanQty, FBeginQty, FBeginAmount, FCurrInputQty, FCurrInputAmount, FAdjCurrInputQty, FAdjCurrInputAmount, FSumCurrInputQty, FSumCurrInputAmount, 
FSumInputQty, FSumInputAmount, FEndQty, FEndAmount, FCompleteQty, FProductUseCost, FCompleteAmount, FSumQualifiedInQty, FSumQualifiedInAmount, FSumDysInQty, 
FSumDysInAmount, FSumWasteInQty, FSumWasteInAmount, FSumCompleteQty, FSumProductUseCost, FSumCompleteAmount) 
SELECT product.FID, ((CONVERT(VARCHAR(8000), acctg.FYear) + N''年'') + CONVERT(VARCHAR(8000), acctg.FPeriod)) + N''期'', (acctg.FYear * 100) + acctg.FPeriod, 
product.FORDERBILLNO, CASE  WHEN product.FOrderBillSeq = 0 THEN '' '' ELSE CONVERT(VARCHAR(8000), product.FOrderBillSeq) END, 
CASE  WHEN productDime.FPROORDERTYPE = ''PO'' THEN N''普通生产'' WHEN productDime.FPROORDERTYPE = ''SPO'' THEN N''简单生产'' 
WHEN productDime.FPROORDERTYPE = ''REM'' THEN N''重复生产'' ELSE N''委外生产'' END, CONVERT(VARCHAR(8000), productDime.FCOSTCENTERID), 
CONVERT(VARCHAR(8000), productDime.FPRODUCTID), product.FPRODUCTDIMEID, productDime.FPRODUCTNO, '' '', CASE  WHEN productDime.FBILLSeq = 0 
THEN '' '' ELSE CONVERT(VARCHAR(8000), productDime.FBILLSeq) END, product_M.FNUMBER, ISNULL(material_L.FName, '' ''), ISNULL(material_L.FSpecification, '' ''), 
ISNULL(center.FNumber, '' ''), ISNULL(center_L.FName, '' ''), ISNULL(lot.FNumber, '' ''), ISNULL(productDime.FMTONO, N'' ''), 
ISNULL(bom.FNumber, '' ''), product.FAuxPropId, ISNULL(unit_L.FName, '' ''), unit.FPrecision, @FAMOUNTDIGITS, product.FPlanQty, 
masResult.FBeginQty, masResult.FBeginAmount, masResult.FCurrInputQty, masResult.FCurrInputAmount, masResult.FAdjCurrInputQty, 
masResult.FAdjCurrInputAmount, masResult.FCurrInputQty + masResult.FAdjCurrInputQty, masResult.FCurrInputAmount + masResult.FAdjCurrInputAmount, 
masResult.FSumCurrInputQty, masResult.FSumCurrInputAmount, masResult.FEndQty, masResult.FEndAmount, 
masResult.FQualifiedInQty + masResult.FDysInQty + masResult.FWasteInQty, CASE  WHEN (masResult.FQualifiedInQty + masResult.FDysInQty + masResult.FWasteInQty) = 0 
THEN 0 ELSE ROUND(((masResult.FQualifiedInAmount + masResult.FDysInAmount + masResult.FWasteInAmount) / (masResult.FQualifiedInQty + masResult.FDysInQty + masResult.FWasteInQty)), 
10) END, masResult.FQualifiedInAmount + masResult.FDysInAmount + masResult.FWasteInAmount, masResult.FSumQualifiedInQty, masResult.FSumQualifiedInAmount, 
masResult.FSumDysInQty, masResult.FSumDysInAmount, masResult.FSumWasteInQty, masResult.FSumWasteInAmount, 
masResult.FSumQualifiedInQty + masResult.FSumDysInQty + masResult.FSumWasteInQty, 
CASE  WHEN (masResult.FSumQualifiedInQty + masResult.FSumDysInQty + masResult.FSumWasteInQty) = 0 THEN 0 ELSE 
ROUND(((masResult.FSumQualifiedInAmount + masResult.FSumDysInAmount + masResult.FSumWasteInAmount) / (masResult.FSumQualifiedInQty + masResult.FSumDysInQty + masResult.FSumWasteInQty)), 10) END, 
masResult.FSumQualifiedInAmount + masResult.FSumDysInAmount + masResult.FSumWasteInAmount FROM T_CB_PROORDERINFO_H product 
INNER JOIN T_CB_CostCalExpense_H masResult ON product.FID = masResult.FID INNER JOIN T_CB_PROORDERDIME productDime ON product.FProductDimeId = productDime.FProductDimeId 
INNER JOIN T_HS_OUTACCTG acctg ON product.FACCTGID = acctg.FId INNER JOIN t_bd_Material product_M ON productDime.FPRODUCTID = product_M.FMATERIALID 
LEFT OUTER JOIN t_bd_Material_l material_L ON (material_L.FMaterialId = product_M.FMATERIALID AND material_L.FLOCALEID = 2052) 
INNER JOIN T_BD_MATERIALBASE baseunit ON baseunit.FMaterialId = product_M.FMATERIALID INNER JOIN T_BD_UNIT unit ON baseunit.FBaseUnitId = unit.FUNITID 
LEFT OUTER JOIN T_BD_UNIT_L unit_L ON (unit.FUNITID = unit_L.FUNITID AND unit_L.FLOCALEID = 2052) 
LEFT OUTER JOIN T_CB_COSTCENTER center ON productDime.FCOSTCENTERID = center.FCOSTCENTERID 
LEFT OUTER JOIN T_CB_COSTCENTER_L center_L ON (center_L.FCOSTCENTERID = center.FCOSTCENTERID AND center_L.FLOCALEID = 2052) 
LEFT OUTER JOIN t_eng_bom bom ON product.FBOMId = bom.FID LEFT OUTER JOIN T_BD_LOTMASTER lot ON product.FLOT = lot.FLOTID 
WHERE (((((product.FENDINITKEY = ''1'' AND acctg.FDIMENSIONID = @FDIMEID) AND (((acctg.FYEAR * 12) + acctg.FPERIOD) >= @FSTARTACCTGID)) AND 
(((acctg.FYEAR * 12) + acctg.FPERIOD) <= @FENDACCTGID)) AND (product_M.FNumber >= @FStartProductNumber)) AND (product_M.FNumber <= @FEndProductNumber))',
N'@FAMOUNTDIGITS int,@FDIMEID bigint,@FSTARTACCTGID bigint,@FENDACCTGID bigint,@FStartProductNumber nvarchar(15),@FEndProductNumber nvarchar(15)',
@FAMOUNTDIGITS=2,@FDIMEID=12,@FSTARTACCTGID=24228,@FENDACCTGID=24228,@FStartProductNumber=N'301020626-00004',@FEndProductNumber=N'301020626-00004'

exec sp_executesql N'INSERT INTO #TM_CB_COSTCALBILLRPT (FID, FYearPeriodSeq, FProductDimeId, FBillNoSeq, FAMOUNTDIGITS, FCOSTCENTERID, 
FPRODUCTID, FCostItemId, FCOSTITEMID_FNUMBER, FCOSTITEMID_FNAME, FProductUseCost, FSumProductUseCost, FBeginAmount, FCurrInputAmount, 
FAdjCurrInputAmount, FSumCurrInputAmount, FSumInputAmount, FEndAmount, FCompleteAmount, FSumQualifiedInAmount, FSumDysInAmount,FSumWasteInAmount, FSumCompleteAmount) 
SELECT product.FID, (acctg.FYear * 100) + acctg.FPeriod, product.FPRODUCTDIMEID, ''1'', @FAMOUNTDIGITS, CONVERT(VARCHAR(8000), productDime.FCOSTCENTERID), CONVERT(VARCHAR(8000), productDime.FPRODUCTID), 
costitem.FCOSTITEMID, costitem.FNUMBER, ISNULL(costitem_L.FNAME, '' ''), CASE  WHEN max(masResult.FQualifiedInQty + masResult.FDysInQty + masResult.FWasteInQty) = 0 
THEN 0 ELSE ROUND((sum(masDetail.FQualifiedInAmount + masDetail.FDysInAmount + masDetail.FWasteInAmount) / max(masResult.FQualifiedInQty + masResult.FDysInQty + masResult.FWasteInQty)), 10) END, 
CASE  WHEN max(masResult.FSumQualifiedInQty + masResult.FSumDysInQty + masResult.FSumWasteInQty) = 0 
THEN 0 ELSE ROUND((sum(masDetail.FSumQualifiedInAmount + masDetail.FSumDysInAmount + masDetail.FSumWasteInAmount) / max(masResult.FSumQualifiedInQty + masResult.FSumDysInQty + masResult.FSumWasteInQty)), 10) END, 
sum(masDetail.FBEGINAMOUNT), sum(masDetail.FCurrInputAmount), sum(masDetail.FAdjCurrInputAmount), sum(masDetail.FCurrInputAmount + masDetail.FAdjCurrInputAmount), 
sum(masDetail.FSumCurrInputAmount), sum(masDetail.FEndAmount), sum(masDetail.FQualifiedInAmount + masDetail.FDysInAmount + masDetail.FWasteInAmount), sum(masDetail.FSumQualifiedInAmount),
sum(masDetail.FSumDysInAmount), sum(masDetail.FSumWasteInAmount), sum(masDetail.FSumQualifiedInAmount + masDetail.FSumDysInAmount + masDetail.FSumWasteInAmount) FROM T_CB_PROORDERINFO_H product 
INNER JOIN T_CB_PROORDERDIME productDime ON product.FProductDimeId = productDime.FProductDimeId 
INNER JOIN T_HS_OUTACCTG acctg ON product.FACCTGID = acctg.FId 
INNER JOIN T_CB_CostCalExpense_H masResult ON product.FID = masResult.FID 
INNER JOIN T_CB_CostCalExpenseDetail_H masDetail ON masResult.FENTRYID = masDetail.FENTRYID 
INNER JOIN T_HS_COSTITEM costitem ON masDetail.FCOSTITEMID = costitem.FCOSTITEMID 
LEFT OUTER JOIN T_HS_COSTITEM_L costitem_L ON (costitem_L.FCOSTITEMID = costitem.FCOSTITEMID AND costitem_L.FLOCALEID = 2052) 
WHERE EXISTS (SELECT 1 FROM #TM_CB_COSTCALBILLRPT "temp" WHERE (product.FID = "TEMP".FID AND "TEMP".FBillNoSeq = '' '')) 
GROUP BY product.FID, acctg.FYear, acctg.FPeriod, product.FPRODUCTDIMEID, productDime.FCOSTCENTERID, productDime.FPRODUCTID, costitem.FCOSTITEMID, costitem.FNUMBER, 
costitem_L.FNAME',N'@FAMOUNTDIGITS int',@FAMOUNTDIGITS=2

exec sp_executesql N'UPDATE T1 SET t1.FBeginAmount = t3.FBeginAmount, t1.FCurrInputAmount = t3.FCurrInputAmount, t1.FAdjCurrInputAmount = t3.FAdjCurrInputAmount, 
t1.FSumCurrInputAmount = t3.FSumCurrInputAmount, t1.FSumInputAmount = t3.FSumInputAmount, t1.FEndAmount = t3.FEndAmount, t1.FProductUseCost = t3.FProductUseCost, 
t1.FSumProductUseCost = t3.FSumProductUseCost, t1.FCompleteAmount = t3.FCompleteAmount, t1.FSumQualifiedInAmount = t3.FSumQualifiedInAmount, 
t1.FSumDysInAmount = t3.FSumDysInAmount, t1.FSumWasteInAmount = t3.FSumWasteInAmount, t1.FSumCompleteAmount = t3.FSumCompleteAmount FROM #TM_CB_COSTCALBILLRPT T1, 
(SELECT t2.FID, sum(t2.FBeginAmount) fbeginamount, sum(t2.FCurrInputAmount) fcurrinputamount, sum(t2.FAdjCurrInputAmount) fadjcurrinputamount, 
sum(t2.FSumCurrInputAmount) fsumcurrinputamount, sum(t2.FSumInputAmount) fsuminputamount, sum(t2.FEndAmount) fendamount, sum(t2.FSumDysInAmount) fsumdysinamount, 
sum(t2.FProductUseCost) fproductusecost, sum(t2.FSumProductUseCost) fsumproductusecost, sum(t2.FCompleteAmount) fcompleteamount, 
sum(t2.FSumQualifiedInAmount) fsumqualifiedinamount, sum(t2.FSumWasteInAmount) fsumwasteinamount, sum(t2.FSumCompleteAmount) fsumcompleteamount 
FROM #TM_CB_COSTCALBILLRPT t2 WHERE t2.FBillNoSeq = ''1'' GROUP BY t2.FID) t3 WHERE (t1.FID = t3.FID AND t1.FBillNoSeq = '' '')',N'@FAMOUNTDIGITS int',@FAMOUNTDIGITS=2

exec sp_executesql N'DELETE FROM #TM_CB_COSTCALBILLRPT WHERE (FEXPTYPE = @FEXPTYPE AND FBillNoSeq = ''11'')',N'@FEXPTYPE nvarchar(1)',@FEXPTYPE=N'1'

SELECT max(FQTYDIGITS) fqtydigits, max(FAMOUNTDIGITS) famountdigits, MAX(FBASICUNITFIELD_FNAME) fbasicunitfield_fname, 
FYearPeriod, FYearPeriodSeq, (FPRODUCTID + 'Z') fproductid, FPRODUCTID_FNUMBER, FPRODUCTID_FNAME, FSPECIFICATION, 
FCostItemId, FCOSTITEMID_FNUMBER, FCOSTITEMID_FNAME, FExpensesItemId, FEXPENSEITEMFIELD_FNUMBER, FEXPENSEITEMFIELD_FNAME, 
FExpType, FMaterialId, FMATERIALFIELD_FNUMBER, FMATERIALFIELD_FNAME, FBillNoSeq, sum(FPlanQty) fplanqty, sum(FBeginQty) fbeginqty, 
sum(FBeginAmount) fbeginamount, sum(FCurrInputQty) fcurrinputqty, sum(FCurrInputAmount) fcurrinputamount, sum(FAdjCurrInputQty) fadjcurrinputqty, 
sum(FAdjCurrInputAmount) fadjcurrinputamount, sum(FSumCurrInputQty) fsumcurrinputqty, sum(FSumCurrInputAmount) fsumcurrinputamount, 
sum(FSumInputQty) fsuminputqty, sum(FSumInputAmount) fsuminputamount, sum(FEndQty) fendqty, sum(FEndAmount) fendamount, 
sum(FCompleteQty) fcompleteqty, sum(FCompleteAmount) fcompleteamount, sum(FSumQualifiedInQty) fsumqualifiedinqty, 
sum(FSumQualifiedInAmount) fsumqualifiedinamount, sum(FSumDysInQty) fsumdysinqty, sum(FSumDysInAmount) fsumdysinamount, 
sum(FSumWasteInQty) fsumwasteinqty, sum(FProductUseQty) fproductuseqty, CASE  WHEN sum(FCompleteQty) = 0 THEN 0 ELSE 
ROUND((sum(FCompleteAmount) / sum(FCompleteQty)), 10) END fproductusecost, sum(FSumWasteInAmount) fsumwasteinamount, 
sum(FSumCompleteQty) fsumcompleteqty, sum(FSumProductUseQty) fsumproductuseqty, CASE  WHEN sum(FSumCompleteQty) = 0 THEN 0 ELSE 
ROUND((sum(FSumCompleteAmount) / sum(FSumCompleteQty)), 10) END fsumproductusecost, sum(FSumCompleteAmount) fsumcompleteamount, 
ROW_NUMBER() OVER( ORDER BY FYearPeriodSeq ASC, FPRODUCTID ASC, FCostItemId ASC, FExpType ASC, FMaterialId ASC, FExpensesItemId ASC, 
FBillNoSeq ASC) fidentityid FROM #TM_CB_COSTCALBILLRPT GROUP BY FYearPeriod, FYearPeriodSeq, FPRODUCTID, FPRODUCTID_FNUMBER, 
FPRODUCTID_FNAME, FSPECIFICATION, FCostItemId, FCOSTITEMID_FNUMBER, FCOSTITEMID_FNAME, FExpensesItemId, FEXPENSEITEMFIELD_FNUMBER, 
FEXPENSEITEMFIELD_FNAME, FExpType, FMaterialId, FMATERIALFIELD_FNUMBER, FMATERIALFIELD_FNAME, FBillNoSeq

select * from TMP9C279F81528711E9811AE04F430

select * from #TM_CB_COSTCALBILLRPT