Sql 获取嵌套式物料清单
Create Table #Temp_ENG_BOMCHILD(ID int identity,FENTRYID int,FMATERIALID int ,FISSKIP int)
declare @FNumber NVarchar(255) = 'BOM物料编码'
declare @Materilid1 int = (Select FMATERIALID From T_BD_MATERIAL where FNUMBER=@FNumber And FUSEORGID='使用组织ID');
--declare @Materilid2 int = (Select FMATERIALID From T_BD_MATERIAL where FNUMBER=@FNumber And FUSEORGID=648572);
declare @Int int =0
IF Not Exists(Select * From #Temp_ENG_BOMCHILD)
Begin
insert #Temp_ENG_BOMCHILD(FMATERIALID,FISSKIP)
Select
T0.FMATERIALID,
T5.FISSKIP
From
T_ENG_BOMCHILD T0
inner join T_ENG_BOM T ON T.FID=T0.FID
inner join T_BD_UNIT_L T1 ON T0.FUNITID=T1.FUNITID And T1.FLOCALEID=2052--单位主表
inner join T_BD_MATERIAL T2 ON T0.FMATERIALID=T2.FMATERIALID--物料主表
inner join T_BD_MATERIAL_L T3 ON T3.FMATERIALID=T2.FMATERIALID And T3.FLOCALEID=2052--物料主表
inner join T_ORG_Organizations_L T4 ON T.FUSEORGID=T4.FORGID And T4.FLOCALEID=2052--组织结构表
inner join T_ENG_BOMCHILD_A T5 ON T0.FENTRYID=T5.FENTRYID
Where T.FMATERIALID IN (@Materilid1)
End
IF Exists(Select * From #Temp_ENG_BOMCHILD)
Begin
While @Int <=30--循环30次
Begin
insert #Temp_ENG_BOMCHILD(FMATERIALID,FISSKIP)
Select
T0.FMATERIALID,
T5.FISSKIP
From
T_ENG_BOMCHILD T0
inner join T_ENG_BOM T ON T.FID=T0.FID
inner join T_BD_UNIT_L T1 ON T0.FUNITID=T1.FUNITID And T1.FLOCALEID=2052
inner join T_BD_MATERIAL T2 ON T0.FMATERIALID=T2.FMATERIALID
inner join T_BD_MATERIAL_L T3 ON T3.FMATERIALID=T2.FMATERIALID And T3.FLOCALEID=2052
inner join T_ORG_Organizations_L T4 ON T.FUSEORGID=T4.FORGID And T4.FLOCALEID=2052
inner join T_ENG_BOMCHILD_A T5 ON T0.FENTRYID=T5.FENTRYID
Where T.FMATERIALID IN (Select distinct FMATERIALID From #Temp_ENG_BOMCHILD)
IF Not Exists(Select * From T_ENG_BOMCHILD_A Where FENTRYID in (Select top 1 FENTRYID from #Temp_ENG_BOMCHILD order by id desc ))
Begin
Break;
End
Set @Int=@Int+1
End
End
Select 层次,物料编码, 物料名称,规格型号,分子用量,分母用量,计量单位 From(
select
L2.FNUMBER as 层次,
T2.FNUMBER as 物料编码,
T3.FNAME as 物料名称,
T3.FSPECIFICATION as 规格型号,
T0.FNUMERATOR as 分子用量,
T0.FDENOMINATOR as 分母用量,
T1.FName as 计量单位
from
T_ENG_BOMCHILD T0
inner join T_ENG_BOM T ON T.FID=T0.FID
inner join T_BD_UNIT_L T1 ON T0.FUNITID=T1.FUNITID And T1.FLOCALEID=2052
inner join T_BD_MATERIAL T2 ON T0.FMATERIALID=T2.FMATERIALID
inner join T_BD_MATERIAL_L T3 ON T3.FMATERIALID=T2.FMATERIALID And T3.FLOCALEID=2052
inner join T_ORG_Organizations_L T4 ON T.FUSEORGID=T4.FORGID And T4.FLOCALEID=2052
inner join T_ENG_BOMCHILD_A T5 ON T0.FENTRYID=T5.FENTRYID
inner join (Select T0.FMATERIALID From #Temp_ENG_BOMCHILD T0 Where T0.FISSKIP=1) L ON L.FMATERIALID=T.FMATERIALID
inner join T_BD_MATERIAL L2 ON L.FMATERIALID=L2.FMATERIALID
) List order by 层次 desc
drop table #Temp_ENG_BOMCHILD
Sql 获取嵌套式物料清单
Create Table #Temp_ENG_BOMCHILD(ID int identity,FENTRYID int,FMATERIALID int ,FISSKIP int)declare @FNumber NVarchar(255...
点击下载文档
本文2024-09-16 18:08:29发表“云星空知识”栏目。
本文链接：https://wenku.my7c.com/article/kingdee-k3cloud-20209.html
相关文章
您需要登录后才可以发表评论， 登录登录 或者 注册
最新文档
热门文章
