SQL展易飞BOM
--SQL展易BOM
CREATE FUNCTION dbo.FN_GetBomtreeForWorkCenter
(
@INVNO as varchar(20), --品号
@INV_ENGNO as varchar(30),--工程
@INVFLAG bit =1 --输入条件识别1为品0为工程号
)
RETURNS @treeinfo table
(
[bod_fxdm] [varchar] (20) NULL, [bod_fxgx] [varchar] (6) NULL, [bod_zxdm] [varchar] (20) NULL, [bod_zxgx] [varchar] (6) NULL,[bod_dwyl] [numeric](19, 5) NULL, [bod_wldw] [varchar] (6) NULL, [bod_dwbs] [numeric](9, 0) NULL, [bod_shjs] [numeric](6, 2) NULL,[bod_shl] [numeric](8, 5) NULL, [bod_ksrq] [datetime]NULL, [bod_sxrq][datetime] NULL,[bod_xsbz] [char] (1) NULL, [bod_bz] [varchar](255) NULL, [ilevel] [int], [ilevel_list] [int],[InvPropert] [char](1), [ilevel_desc] [varchar](20),[wc_cs_code] [varchar](10) null,[bhzc] [char](1) null,[ck] [char](6) null,[PURMA002] [VarChar](250) NULL
)
AS
BEGIN DECLARE @level AS int ,@Max_level int
declare @fxdm AS varchar(20)
if @INVFLAG=1
set @fxdm=rtrim(ltrim(@INVNO))
else
select @fxdm=MB001 from INVMB where MB110=@INV_ENGNO
SELECT @level = 1
--select *from BOMMC
INSERT INTO @treeinfo SELECT MB001,isnull(c.MF004,'zzzz'),MB001,isnull(c.MF004,''),1,MB004,1,0,0,null ,null,'','',0,0,'','','','','',''
FROM (select * from INVMB where MB109='Y') as INVMB_effv --核准交易
left outer join
(SELECT MF001, MAX(MF004) AS MF004 --BOMMF_gxdb
FROM dbo.BOMMF
GROUP BY MF001) c on c.MF001=MB001
WHERE MB001=@fxdm
INSERT INTO @treeinfo
SELECT a.MD001,isnull(c.MF004,''),a.MD003,isnull(b.MF004,''),MD006,MD004,
MD007*MC004,0 as bod_shjs,MD008 as bod_shl,MD011 as bod_ksrq,MD012 as bod_sxrq,
'N' AS bod_xsbz,convert(varchar(255),a.MD016),@level,0,'','','','','',''
from BOMMD a join BOMMC x on x.MC001=a.MD001
left outer join
(SELECT MF001, MAX(MF004) AS MF004
FROM dbo.BOMMF
GROUP BY MF001) b on b.MF001=a.MD003 --BOMMF_gxdb
left outer join
(SELECT MF001, MAX(MF004) AS MF004
FROM dbo.BOMMF
GROUP BY MF001) c on c.MF001=a.MD001
join INVMB itm on itm.MB001=a.MD003
where MD001=@fxdm --and MB025<>'P'
WHILE @@ROWCOUNT > 0
BEGIN
SET @level = @level+1
INSERT INTO @treeinfo
SELECT a.MD001,isnull(c.MF004,'zzzz'),a.MD003,
isnull(b.MF004,''),MD006,MD004,MD007*x.MC004,0 as bod_shjs,
MD008 as bod_shl,MD011 as bod_ksrq,MD012 as bod_sxrq,
'N' AS bod_xsbz,convert(varchar(255),a.MD016),@level,0,'','','','','',''
from BOMMD AS a JOIN @treeinfo AS T
ON a.MD001 =T.bod_zxdm AND T.ilevel = @level-1
join BOMMC x on x.MC001=a.MD001
left outer join
(SELECT MF001, MAX(MF004) AS MF004
FROM dbo.BOMMF
GROUP BY MF001) b on b.MF001=a.MD003
left outer join
(SELECT MF001, MAX(MF004) AS MF004
FROM dbo.BOMMF
GROUP BY MF001) c on c.MF001=a.MD001
join INVMB itm on itm.MB001=a.MD001
--whereMB025<>'P'
END
update @treeinfo set ilevel_list=z.ilevel
from @treeinfo a join
( select left(bod_fxgx,1) as fxgx,min(ilevel) as ilevel from @treeinfo group by left(bod_fxgx,1)) as z
on left(bod_fxgx,1)=fxgx
update @treeinfo set InvPropert=MB025,bod_wldw=MB004 from @treeinfo a join INVMB b on a.bod_zxdm=b.MB001--加工中心
update @treeinfo set wc_cs_code=MB068 from @treeinfo a join INVMB b on a.bod_zxdm=b.MB001 where InvPropert='M'--委外厂商
update @treeinfo set wc_cs_code=MB032 from @treeinfo a join INVMB b on a.bod_zxdm=b.MB001 where InvPropert='S'--补货政策
update @treeinfo set bhzc=MB034 from @treeinfo a join INVMB b on a.bod_zxdm=b.MB001 --主要仓库
update @treeinfo set ck=MB017 from @treeinfo a join INVMB b on a.bod_zxdm=b.MB001
update @treeinfo set ilevel_desc=case when ilevel=0 then '0'
when ilevel=1 then '.1'
when ilevel=2 then '..2'
when ilevel=3 then '...3'
when ilevel=4 then '....4'
when ilevel=5 then '.....5'
when ilevel=6 then '......6'
when ilevel=7 then '.......7'
when ilevel=8 then '........8'
when ilevel=9 then '.........9'
when ilevel=10 then '..........10'
when ilevel=11 then '............11'
when ilevel=12 then '..............12'
when ilevel=13 then '................13'
end
--DELETE FROM@treeinfo WHERE LEFT(bod_fxgx,1) NOT IN ('A','F','E')
RETURN
END
/*declare@Mrno nvarchar(20),@gzzx nvarchar(10)
select@Mrno='110100300' , @gzzx='%'
select a.*
from(
selecta.*,MB002,MB003,bod_fxgx_tou=left(bod_fxgx,1)
fromFN_GetBomtreeForWorkCenter(@Mrno,'',1) a
inner joinINVMB b on MB001=bod_zxdm) a
*/
📱 扫码关注公众号
扫描二维码关注我们，获取更多精彩内容
实时更新 · 干货满满