U8BOM展开存储过程

不忘初心0217

CREATE  procedure [dbo].UJ_bomzk
(
  @cInvCode nvarchar (60)='',
  @Version VARCHAR(4)=''
)
as
declare @i int;
declare @cnt int;
declare @cnt2 int;
DECLARE @sql VARCHAR(3000)
if exists(select * from sys.tables where name='UJ_JRBOM')
drop table UJ_JRBOM
create table UJ_JRBOM  (
iLevel int,
bomid int,
partId nvarchar(60),
cInvCodeHead nvarchar(60),
cInvNameHead nvarchar(255),
cInvStdHead nvarchar(255),
cInvAddCodeHead nvarchar(255),
cEngineerFigNoHead nvarchar(60),
Version int,
VersionEffDate datetime,
cCusAdd nvarchar(60),
cCusInvCode nvarchar(40),
CreateUser nvarchar(20),
CreateDate datetime,
ModifyUser nvarchar(20),
RELSDate datetime,
cInvDefine7Head nvarchar(120),  --物料清单产品规格书
SortSeq nvarchar(60),
cInvCode nvarchar(60),
cInvName nvarchar(255),
cInvStd nvarchar(255),
cInvAddCode nvarchar(255),
BaseQtyN float,
cComUnitName nvarchar(20),
cEngineerFigNo nvarchar(60),
cInvDefine1 nvarchar(60),
free7 nvarchar(60),
cInvDefine7 nvarchar(120),   --存货档案产品规格书
Remark nvarchar(500),
bPurchase bit,
bProxyForeign bit,   
bSelf BIT,
bom varchar(1000)
)
set @i=1

IF ISNULL(@Version,'')<>'' and ISNULL(@cInvCode,'')<>''
BEGIN
insert into UJ_JRBOM(iLevel,bomid,PartId
,cInvCodeHead,cInvNameHead,cInvStdHead,cInvAddCodeHead,cEngineerFigNoHead
,Version,VersionEffDate,cCusAdd,cCusInvCode,CreateUser,ModifyUser,BaseQtyN,CreateDate,RELSDate,cInvDefine7head,bPurchase,bProxyForeign,bSelf)
select 1,b.bomid,b.ParentId
,d.cInvCode ,d.cInvName,d.cInvStd,d.cInvAddCode,d.cEngineerFigNo
,a.Version,a.VersionEffDate,cus.cCusAbbName,e.cCusInvCode
,a.CreateUser,a.RELSUSER as ModifyUser,1,a.CreateDate,a.RELSDate,d.cInvDefine7,
d.bPurchase,d.bProxyForeign,d.bSelf   
from  bom_bom a
inner join bom_parent b on a.bomId=b.bomId
inner join bas_part bas on b.ParentId=bas.PartId
INNER JOIN inventory D on bas.invcode=D.cinvcode  
left join customer cus on right(d.cInvCode,3)=cus.cCusCode
left join CusInvContrapose e on d.cInvCode=e.cInvCode
where 1=1 AND a.Version=@Version AND bas.InvCode=@cInvCode
end

IF ISNULL(@Version,'')<>'' and ISNULL(@cInvCode,'')=''
BEGIN
insert into UJ_JRBOM(iLevel,bomid,PartId
,cInvCodeHead,cInvNameHead,cInvStdHead,cInvAddCodeHead,cEngineerFigNoHead
,Version,VersionEffDate,cCusAdd,cCusInvCode,CreateUser,ModifyUser,BaseQtyN,CreateDate,RELSDate,cInvDefine7head,bPurchase,bProxyForeign,bSelf)
select 1,b.bomid,b.ParentId
,d.cInvCode ,d.cInvName,d.cInvStd,d.cInvAddCode,d.cEngineerFigNo
,a.Version,a.VersionEffDate,cus.cCusAbbName,e.cCusInvCode
,a.CreateUser,a.RELSUSER as ModifyUser,1,a.CreateDate,a.RELSDate,d.cInvDefine7,
d.bPurchase,d.bProxyForeign,d.bSelf   
from  bom_bom a
inner join bom_parent b on a.bomId=b.bomId
inner join bas_part bas on b.ParentId=bas.PartId
INNER JOIN inventory D on bas.invcode=D.cinvcode  
left join customer cus on right(d.cInvCode,3)=cus.cCusCode
left join CusInvContrapose e on d.cInvCode=e.cInvCode
where 1=1 AND a.Version=@Version
end

IF ISNULL(@Version,'')='' and ISNULL(@cInvCode,'')<>''
BEGIN
insert into UJ_JRBOM(iLevel,bomid,PartId
,cInvCodeHead,cInvNameHead,cInvStdHead,cInvAddCodeHead,cEngineerFigNoHead
,Version,VersionEffDate,cCusAdd,cCusInvCode,CreateUser,ModifyUser,BaseQtyN,CreateDate,RELSDate,cInvDefine7head,bPurchase,bProxyForeign,bSelf)
select 1,b.bomid,b.ParentId
,d.cInvCode ,d.cInvName,d.cInvStd,d.cInvAddCode,d.cEngineerFigNo
,a.Version,a.VersionEffDate,cus.cCusAbbName,e.cCusInvCode
,a.CreateUser,a.RELSUSER as ModifyUser,1,a.CreateDate,a.RELSDate,d.cInvDefine7,
d.bPurchase,d.bProxyForeign,d.bSelf   
from  bom_bom a
inner join bom_parent b on a.bomId=b.bomId
inner join bas_part bas on b.ParentId=bas.PartId
INNER JOIN inventory D on bas.invcode=D.cinvcode  
left join customer cus on right(d.cInvCode,3)=cus.cCusCode
left join CusInvContrapose e on d.cInvCode=e.cInvCode
where 1=1 AND bas.InvCode=@cInvCode
end


IF ISNULL(@Version,'')='' and ISNULL(@cInvCode,'')=''
BEGIN
insert into UJ_JRBOM(iLevel,bomid,PartId
,cInvCodeHead,cInvNameHead,cInvStdHead,cInvAddCodeHead,cEngineerFigNoHead
,Version,VersionEffDate,cCusAdd,cCusInvCode,CreateUser,ModifyUser,BaseQtyN,CreateDate,RELSDate,cInvDefine7head,bPurchase,bProxyForeign,bSelf)
select 1,b.bomid,b.ParentId
,d.cInvCode ,d.cInvName,d.cInvStd,d.cInvAddCode,d.cEngineerFigNo
,a.Version,a.VersionEffDate,cus.cCusAbbName,e.cCusInvCode
,a.CreateUser,a.RELSUSER as ModifyUser,1,a.CreateDate,a.RELSDate,d.cInvDefine7,
d.bPurchase,d.bProxyForeign,d.bSelf   
from  bom_bom a
inner join bom_parent b on a.bomId=b.bomId
inner join bas_part bas on b.ParentId=bas.PartId
INNER JOIN inventory D on bas.invcode=D.cinvcode  
left join customer cus on right(d.cInvCode,3)=cus.cCusCode
left join CusInvContrapose e on d.cInvCode=e.cInvCode
where 1=1
end


while 1=1
begin
set @i=@i+1
select @cnt=count(*) from UJ_JRBOM
INSERT into UJ_JRBOM(SortSeq,iLevel,bomid,PartId
,cInvCode,cInvName,cInvStd,cInvAddCode,cEngineerFigNo,BaseQtyN,Remark,cComUnitName,cInvDefine1,free7,
cInvDefine7,bPurchase,bProxyForeign,bSelf,bom)
select isnull(c.SortSeq,'') + '_'+right('000'+ltrim(str(b.SortSeq)),3) ,@i ,a.bomid,b.ComponentId
,inv.cInvCode,inv.cInvName,inv.cInvStd,inv.cInvAddCode,inv.cEngineerFigNo,
c.BaseQtyN*(b.BaseQtyN/b.BaseQtyD) as BaseQtyN,b.Remark,com.cComUnitName,
b.Define22,bas.free7,inv.cInvDefine7,inv.bPurchase,inv.bProxyForeign,inv.bSelf,b.BomId
from bom_opcomponent b
left join bom_parent a on a.ParentId=b.ComponentId
left join UJ_JRBOM c on b.bomid=c.bomid
INNER join bas_part bas on b.ComponentId=bas.partid
INNER join inventory inv on bas.invcode=inv.cinvcode
INNER join ComputationUnit com on inv.cComunitCode=com.cComunitCode
where c.iLevel=@i-1



update a set
a.cInvCodeHead=b.cInvCodeHead
,a.cInvNameHead=b.cInvNameHead
,a.cInvStdHead=b.cInvStdHead
,a.cInvAddCodeHead=b.cInvAddCodeHead
,a.cEngineerFigNoHead=b.cEngineerFigNoHead
,a.Version=b.Version
,a.VersionEffDate=b.VersionEffDate
,a.cCusAdd=b.cCusAdd
,a.cCusInvCode=b.cCusInvCode
,a.CreateUser=b.CreateUser
,a.ModifyUser=b.ModifyUser
,a.CreateDate=b.CreateDate
,a.RelsDate=b.RelsDate
,a.cInvDefine7Head=b.cInvDefine7Head
from UJ_JRBOM a LEFT JOIN
(select a.cInvCodeHead,a.cInvNameHead,a.cInvStdHead,a.cInvAddCodeHead,a.cEngineerFigNoHead
,a.Version,a.VersionEffDate,a.cCusAdd,a.cCusInvCode,
b.cUser_name as CreateUser,
c.cUser_name as ModifyUser,
a.CreateDate,a.RelsDate,a.cInvDefine7Head,bom,bomid
from UJ_JRBOM a
left join UA_User b on a.CreateUser=b.cUser_id
left join UA_User c on a.ModifyUser=c.cUser_id
) b ON a.bom=b.bomid
WHERE  a.cInvCodeHead IS NULL
select @cnt2=count(*) from UJ_JRBOM
if (@cnt=@cnt2) or @i>10
break
end   



delete from UJ_JRBOM where iLevel=1
update UJ_JRBOM  set SortSeq=substring(SortSeq,2,len(SortSeq)-1)


select SortSeq as 序号,cInvCodeHead as 母件编码,cInvNameHead as 母件名称,cInvStdHead as 母件规格型号,cInvAddCodeHead as 母件存货代码,
MAX(Version) as 版本,CreateUser as 制表人,
CreateDate as 制单时间,ModifyUser as 审核人,RELSDate as 审核时间
,cInvCode as 材料编码,cInvName as 材料名称,cInvStd as 规格,cInvAddCode as 代码,BaseQtyN as 使用数量,
cComUnitName as 单位,Remark as 备注,
case when bPurchase=1 and bProxyForeign=0 and bSelf=0 then '外购'  when bPurchase=0 and bProxyForeign=1 and bSelf=0  then '委外' when bPurchase=1 and bProxyForeign=0 and bSelf=1 then '自制' when bPurchase=1 and bProxyForeign=1 and bSelf=0
THEN '外购、委外' when bPurchase=1 and bProxyForeign=0 and bSelf=1 then '外购、自制' when bPurchase=0 and bProxyForeign=1 and bSelf=1 then '委外、自制'
WHEN bPurchase=1 and bProxyForeign=1 and bSelf=1 then '外购、委外、自制' else '' end as 物料属性
from UJ_JRBOM GROUP BY cInvCodeHead,SortSeq ,cInvNameHead ,cInvStdHead ,


cInvAddCodeHead ,cEngineerFigNoHead ,cInvDefine7Head ,
VersionEffDate ,cCusAdd ,cCusInvCode ,CreateUser ,
CreateDate ,ModifyUser ,RELSDate
,cInvCode ,cInvName ,cInvStd ,cInvAddCode ,BaseQtyN ,
cComUnitName,cEngineerFigNo ,cInvDefine1  ,free7 ,Remark ,
cInvDefine7,bPurchase,bProxyForeign,bSelf
ORDER  BY  母件编码