```
CREATE
procedure MATALLOCFINALRESULTRPT
--产品成本明细
@fbeginperiod int null,--开始期间，如：202301
@fendperiod int null,--结束期间
@matnumber NVARCHAR (255),--产品编码
@orgid int null--组织内码
as
begin
CREATE TABLE #T__MATALLOCPRORESULTRPT (freccostcenterid int null, FDIMEENTRYID int null, FBASEUNITID int null,fqty DECIMAL (28, 10) NULL,fsendcostcenterid int null,FSRCBIILLTYPEID VARCHAR (255) NULL,FBILLNO NVARCHAR (255) NULL,FBillSeq VARCHAR (30) NULL,fbilldate datetime null,fproordertype VARCHAR (255) NULL,fproorderno NVARCHAR (255) NULL,fproorderseq NVARCHAR (30) NULL,fproductid int null,
fproordertypename NVARCHAR (255) NULL, FCostItemId int null, FEXPENSEITEMID int null,famount DECIMAL (28, 10) NULL );
--if not exists (select count(*) from [sys].[schemas] S JOIN [sys].[tables] T ON S.schema_id = T.schema_id where S.name='#T__MATALLOCFINALRESULTRPT')
CREATE TABLE #T__MATALLOCFINALRESULTRPT (FRowType SMALLINT NULL, FTotalType SMALLINT NULL, FGroupBySumType SMALLINT NULL, FRecCostCenterID INT NULL, FAmount DECIMAL (28, 10) NULL, FRecCostCenterNumber NVARCHAR (255) NULL, FRecCostCenterName NVARCHAR (255) NULL, FMATERIALID INT NULL, FMaterialNumber NVARCHAR (255) NOT NULL DEFAULT ' ', FMaterialName NVARCHAR (255) NULL, FMaterialSPECIFICATION NVARCHAR (510) NULL, FLotNum NVARCHAR (510) NULL, FBOMNum NVARCHAR (510) NULL, FAUXPROPID INT NULL, FAuxPropertyName NVARCHAR (1024) NULL, FSTOCKLOCID INT NULL, FStockLocName NVARCHAR (1024) NULL, FQty DECIMAL (28, 10) NULL, FPRICE DECIMAL (28, 10) NULL, FUnitName NVARCHAR (255) NULL, FPRICEDIGITS SMALLINT NULL, FQTYDIGITS SMALLINT NULL, FPRODUCTID INT NULL, FProductNumber NVARCHAR (255) NULL, FProductName NVARCHAR (255) NULL, FProductSPECIFICATION NVARCHAR (510) NULL, FSRCBIILLTYPEID VARCHAR (255) NULL, FBillTypeName NVARCHAR (255) NULL, FBILLNO NVARCHAR (255) NULL, FBillSeq VARCHAR (30) NULL, FBillDate DATETIME NULL, FStockName NVARCHAR (255) NULL, FSendOrgName NVARCHAR (255) NULL, FRecOrgName NVARCHAR (255) NULL, FPROORDERTYPE VARCHAR (255) NULL, FPROORDERTYPEName NVARCHAR (255) NULL, FPROORDERNO NVARCHAR (255) NULL, FPROORDERSEQ VARCHAR (30) NULL, FCostItemId INT NULL, FCOSTITEMID_FNUMBER VARCHAR (80) NULL, FCOSTITEMID_FNAME NVARCHAR (255) NULL, FEXPENSEITEMID INT NULL, FExpNumber NVARCHAR (80) NULL, FExpName NVARCHAR (255) NULL, FCategoryID NVARCHAR (255) NULL, FSourceBillTypeName NVARCHAR (255) NULL);
INSERT INTO #T__MATALLOCPRORESULTRPT with(tablock)
SELECT ISNULL(t2_DIM.FCOSTCENTERID, 0) freccostcenterid, t1.FDIMEENTRYID, t1.FBASEUNITID, Sum(t2.FQty) fqty, t1.FCOSTCENTERID fsendcostcenterid, t1.FSRCBIILLTYPEID, t1.FBILLNO, t1.FBillSeq, MAX(SEQ.FBILLDATE) fbilldate, ISNULL(t2_Dim.FPROORDERTYPE, ' ') fproordertype, ISNULL(t2_Dim.FPRODUCTNO, ' ') fproorderno, CONVERT(VARCHAR(8000), t2_Dim.FBILLSeq) fproorderseq, ISNULL(t2_Dim.FPRODUCTID, 0) fproductid, CASE WHEN t2_Dim.FPROORDERTYPE = 'PO' THEN N'普通生产' WHEN t2_Dim.FPROORDERTYPE = 'SPO' THEN N'简单生产' WHEN t2_Dim.FPROORDERTYPE = 'OUTSRC' THEN N'委外生产' WHEN t2_Dim.FPROORDERTYPE = 'REM' THEN N'重复生产' WHEN t2_Dim.FPROORDERTYPE = 'TSO' THEN N'费用追踪至出库单' WHEN t2_Dim.FPROORDERTYPE = 'TSR' THEN N'费用追踪至退货单' WHEN t2_Dim.FPROORDERTYPE = 'TP' THEN N'费用追踪至产品' ELSE N' ' END fproordertypename, t2_Exp.FCostItemId, t2_Exp.FEXPENSEITEMID, SUM(t2_Exp.FAMOUNT) famount
--INTO #T__MATALLOCPRORESULTRPT
FROM T_CB_COSTALLORESULTSEND_H t1 INNER JOIN T_CB_COSTALLORESULTREC_H t2 ON t2.FSENDID = t1.FSENDID LEFT OUTER JOIN T_CB_PROORDERDIME t2_Dim ON t2_Dim.FProductDimeId = t2.FProductDimeId INNER JOIN T_CB_COSTALLORESULTRECEXP_H t2_Exp ON t2_Exp.FRECID = t2.FRECID INNER JOIN T_HS_OUTACCTG ACCTG ON ACCTG.FID = t1.FOUTACCTGID INNER JOIN T_HS_OUTINSTOCKSEQ_H SEQ ON SEQ.FENTRYID = T1.FOUTINSTOCKID INNER JOIN T_HS_CALDIMENSIONS DIM ON DIM.FDIMENSIONID = ACCTG.FDIMENSIONID LEFT OUTER JOIN T_CB_COSTCENTER cc01 ON cc01.FCOSTCENTERID = t1.FCOSTCENTERID LEFT OUTER JOIN T_CB_COSTCENTER cc02 ON cc02.FCOSTCENTERID = t2_Dim.FCOSTCENTERID LEFT OUTER JOIN T_HS_COSTITEM cost ON cost.FCostItemId = t2_Exp.FCostItemId LEFT OUTER JOIN T_BD_EXPENSE ex ON ex.FEXPID = t2_Exp.FEXPENSEITEMID LEFT OUTER JOIN T_BD_MATERIAL mat ON mat.FMATERIALID = t2_Dim.FPRODUCTID WHERE ((((((((1 = 1 AND DIM.FACCTSYSTEMID = 1) AND DIM.FFINORGID = 1) AND DIM.FACCTPOLICYID = 1) AND (((ACCTG.FYEAR * 100) + ACCTG.FPERIOD) >= @fbeginperiod)) AND (((ACCTG.FYEAR * 100) + ACCTG.FPERIOD) <= @fendperiod)) AND (mat.FNUMBER >=@matnumber)) AND (mat.FNUMBER<=@matnumber) and (mat.FUSEORGID=@orgid))) GROUP BY t2_DIM.FCOSTCENTERID, t1.FBASEUNITID, t1.FDIMEENTRYID, t1.FCOSTCENTERID, t1.FSRCBIILLTYPEID, t1.FBILLNO, t1.FBillSeq, t2_Dim.FPROORDERTYPE, t2_Dim.FPRODUCTNO, t2_Dim.FBILLSeq, t2_Dim.FPRODUCTID, t2_Exp.FCostItemId, t2_Exp.FEXPENSEITEMID;
INSERT INTO #T__MATALLOCFINALRESULTRPT with(tablock) SELECT 0 frowtype, 0 ftotaltype, 0 fgroupbysumtype, t1.FRecCostCenterID, t1.FAmount, cc02.FNUMBER freccostcenternumber, cc02l.FNAME freccostcentername, stockDim.FMATERIALID, mat02.FNUMBER fmaterialnumber, mat02l.FNAME fmaterialname, mat02l.FSPECIFICATION fmaterialspecification, lot.FNUMBER flotnum, bom.FNUMBER fbomnum, stockDim.FAUXPROPID, CONVERT(VARCHAR(255), ' ') fauxpropertyname, stockDim.FSTOCKLOCID, CONVERT(VARCHAR(255), ' ') fstocklocname, t1.FQty, CASE WHEN t1.FQty = 0 THEN 0 ELSE ROUND((t1.FAmount / t1.FQty), 10) END fprice, unitL.FName funitname, 6 fpricedigits, unit.FPRECISION fqtydigits, t1.FPRODUCTID, mat.FNUMBER fproductnumber, matl.FNAME fproductname, matl.FSPECIFICATION fproductspecification, t1.FSRCBIILLTYPEID, billType_L.FName fbilltypename, t1.FBILLNO, t1.FBillSeq, t1.FBillDate, stockL.FName fstockname, org01l.FNAME fsendorgname, org02l.FNAME frecorgname, t1.FPROORDERTYPE, t1.FPROORDERTYPEName, t1.FPROORDERNO, t1.FPROORDERSEQ, t1.FCostItemId, cost.FNUMBER fcostitemid_fnumber, costl.FNAME fcostitemid_fname, t1.FEXPENSEITEMID, ex.FNUMBER fexpnumber, exl.FNAME fexpname, gory_L.FNAME fcategoryid, ' ' fsourcebilltypename
FROM #T__MATALLOCPRORESULTRPT t1
LEFT OUTER JOIN T_CB_COSTCENTER cc02 ON cc02.FCOSTCENTERID = t1.FRecCostCenterID
INNER JOIN T_HS_InivStockDimension stockDim ON stockDim.FEntryID = t1.FDIMEENTRYID
LEFT OUTER JOIN T_BD_MATERIAL mat02 ON mat02.FMATERIALID = stockDim.FMATERIALID
INNER JOIN T_BD_MATERIALBASE mat02Base ON (mat02Base.FMATERIALID = mat02.FMATERIALID AND mat02.FUSEORGID = mat02Base.FUSEORGID)
LEFT OUTER JOIN T_BD_MATERIAL_L mat02l ON (mat02l.FMATERIALID = mat02.FMATERIALID AND mat02l.FLOCALEID = 2052)
LEFT OUTER JOIN T_BD_LOTMASTER lot ON lot.FLotID = stockDim.FLOT
LEFT OUTER JOIN t_eng_bom bom ON bom.FID = stockDim.FBOMID
LEFT OUTER JOIN T_BD_STOCK stock ON stock.FSTOCKID = stockDim.FSTOCKID
LEFT OUTER JOIN T_BD_STOCK_L stockL ON (stockL.FSTOCKID = stock.FSTOCKID AND stockL.FLOCALEID = 2052)
LEFT OUTER JOIN T_BD_UNIT unit ON unit.FUnitID = t1.FBASEUNITID
LEFT OUTER JOIN T_BD_UNIT_L unitL ON (unitL.FUnitID = unit.FUnitID AND unitL.FLOCALEID = 2052)
LEFT OUTER JOIN T_BD_MATERIALCATEGORY_L gory_L ON (mat02Base.FCATEGORYID = gory_L.FCATEGORYID AND gory_L.FLOCALEID = 2052)
LEFT OUTER JOIN T_CB_COSTCENTER cc01 ON cc01.FCOSTCENTERID = t1.FSendCostCenterID
LEFT OUTER JOIN T_ORG_ORGANIZATIONS org01 ON org01.FORGID = cc01.FUSEORGID
LEFT OUTER JOIN T_ORG_ORGANIZATIONS_L org01l ON (org01l.FORGID = org01.FORGID AND org01l.FLOCALEID = 2052)
LEFT OUTER JOIN T_ORG_ORGANIZATIONS org02 ON org02.FORGID = cc02.FUSEORGID
LEFT OUTER JOIN T_ORG_ORGANIZATIONS_L org02l ON (org02l.FORGID = org02.FORGID AND org02l.FLOCALEID = 2052)
LEFT OUTER JOIN T_BD_MATERIAL mat ON mat.FMATERIALID = t1.FPRODUCTID
LEFT OUTER JOIN T_BD_MATERIAL_L matl ON (matl.FMATERIALID = mat.FMATERIALID AND matl.FLOCALEID = 2052)
LEFT OUTER JOIN T_BAS_BILLTYPE_L billType_L ON (billType_L.FBillTypeID = t1.FSRCBIILLTYPEID AND billType_L.FLOCALEID = 2052)
LEFT OUTER JOIN T_CB_COSTCENTER_L cc02l ON (cc02l.FCOSTCENTERID = cc02.FCOSTCENTERID AND cc02l.FLOCALEID = 2052)
LEFT OUTER JOIN T_HS_COSTITEM cost ON cost.FCostItemId = t1.FCostItemId
LEFT OUTER JOIN T_HS_COSTITEM_L costl ON (costl.FCostItemId = cost.FCostItemId AND costl.FLOCALEID = 2052)
LEFT OUTER JOIN T_BD_EXPENSE ex ON EX.FEXPID = t1.FEXPENSEITEMID
LEFT OUTER JOIN T_BD_EXPENSE_L exl ON (exl.FEXPID = ex.FEXPID AND exl.FLOCALEID = 2052) WHERE 1 = 1;
MERGE INTO #T__MATALLOCFINALRESULTRPT t0 using(SELECT T.FID, CASE WHEN ISNULL(T.FShowName, N' ') = N' ' THEN CONVERT(VARCHAR(8000), N'') ELSE LEFT(T.FShowName, (LEN(T.FShowName) - 1)) END fshowname
FROM (SELECT t0.FID fid, ((((CASE WHEN ISNULL(st01_L.FNAME, N' ') = ' ' THEN CONVERT(VARCHAR(8000), N'') ELSE (CONVERT(NVARCHAR(255), st01_L.FNAME) + CONVERT(VARCHAR(8000), ';')) END + CASE WHEN ISNULL(st02_L.FNAME, N' ') = ' ' THEN CONVERT(VARCHAR(8000), N'') ELSE (CONVERT(NVARCHAR(255), st02_L.FNAME) + CONVERT(VARCHAR(8000), ';')) END) + CASE WHEN ISNULL(st03_L.FNAME, N' ') = ' ' THEN CONVERT(VARCHAR(8000), N'') ELSE (CONVERT(NVARCHAR(255), st03_L.FNAME) + CONVERT(VARCHAR(8000), ';')) END) + CASE WHEN ISNULL(t0.FOPCODE, N' ') = ' ' THEN CONVERT(VARCHAR(8000), N'') ELSE (CONVERT(NVARCHAR(255), t0.FOPCODE) + CONVERT(VARCHAR(8000), ';')) END) + CONVERT(VARCHAR(8000), N'')) fshowname FROM T_BAS_FLEXVALUESDETAIL t0
LEFT OUTER JOIN T_BAS_FLEXVALUESENTRY_L st01_L ON (t0.FF100006 = st01_L.FEntryID AND st01_L.FLocaleId = 2052)
LEFT OUTER JOIN T_BAS_FLEXVALUESENTRY_L st02_L ON (t0.FF100007 = st02_L.FEntryID AND st02_L.FLocaleId = 2052)
LEFT OUTER JOIN T_BAS_FLEXVALUESENTRY_L st03_L ON (t0.FF100009 = st03_L.FEntryID AND st03_L.FLocaleId = 2052)) t) t1 ON (t1.FID = t0.FSTOCKLOCID)
WHEN MATCHED THEN UPDATE SET t0.FStockLocName = CASE WHEN (LEN(t1.FShowName) >= 255) THEN (LEFT(t1.FShowName, 252) + '...') ELSE t1.FShowName END;
select * from #T__MATALLOCFINALRESULTRPT;
drop table #T__MATALLOCPRORESULTRPT;
drop table #T__MATALLOCFINALRESULTRPT;
end;
```
