```CREATE
procedure BOMdetailMATALLOCFINALRESULTRPTfinal
@matnumber NVARCHAR (255),
@orgid int,
@fbeginperiod int,
@fendperiod int
as
begin
create table #bomfinalresult (
bomlevel int, --BOM层级
sonmatnumber NVARCHAR (255) NULL,--子项物料代码
sonmatname NVARCHAR (255) NULL,--子项物料名称
rowseq smallint,--分录行号
FReplace smallint,--项次
FMaterialType NVARCHAR (255),--子项类型
FNumerator int,--分子
FDenominator int,--分母
FFixScrapQty int,--固定损耗
FScrapqty int,--变动损耗
fuserorgid int,--使用组织
FReplaceGroup NVARCHAR (255), --项次组合
FBomEntryId NVARCHAR (255),--BOM内码和项次组合
FSONBOMId int,--子项BOM版本内码
FParentBOMId NVARCHAR(255), --所属BOM
FAncestorId int,--最顶级BOM内码
FStardQty decimal(23,10),--标准用量
FActualQty decimal(23,10),--实际用量
FPARENTMAT nvarchar(255)--父物料
);--定义BOM展开结果的存储表
CREATE TABLE #T__MATALLOCFINALRESULTRPTDD (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,FPARENTMAT NVARCHAR (255) NULL);
--定义成本分配结果表
--CREATE TABLE #T__MATALLOCFINALRESULTRPTDDP (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,FPARENTMAT NVARCHAR (255) NULL);
--过程表用于记录数据，并更新父级字段
insert into #bomfinalresult exec bomdetailquery @matnumber,@orgid;--执行计算结果，获取BOM信息
--select * from #bomfinalresult;
select sonmatnumber,sum(FStardQty) as 标准用量,sum(FActualQty) as 实际用量,FPARENTMAT,tbm.FMATERIALID as fparentmatid into #totalbom from #bomfinalresult p left join T_BD_MATERIAL tbm on p.FPARENTMAT=tbm.FNUMBER where tbm.FUSEORGID = @orgid group by sonmatnumber,FPARENTMAT,FMATERIALID;
declare @FPARENTMAT nvarchar(255),@bomlevel int;--定义变量，用于记载游标数据
declare mat cursor for select distinct (fparentmat) from #bomfinalresult;--定义游标
open mat;--打开游标
fetch next from mat into @FPARENTMAT;--获取第一行游标，开始遍历
while @@fetch_status=0
begin
CREATE TABLE #T__MATALLOCFINALRESULTRPTDDP (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__MATALLOCFINALRESULTRPTDDP exec MATALLOCFINALRESULTRPT @fbeginperiod,@fendperiod,@FPARENTMAT,@orgid;
alter table #T__MATALLOCFINALRESULTRPTDDP add FPARENTMAT nvarchar(255) null;
update #T__MATALLOCFINALRESULTRPTDDP set FPARENTMAT =@FPARENTMAT;
insert into #T__MATALLOCFINALRESULTRPTDD select * from #T__MATALLOCFINALRESULTRPTDDP;
drop table #T__MATALLOCFINALRESULTRPTDDP;
fetch next from mat into @FPARENTMAT;
end
close mat;
Deallocate mat;
```
BOM和材料费用分配明细的SQL，可以在前期文章查询
