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
--where MB025<>'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
(select a.*,MB002,MB003,bod_fxgx_tou=left(bod_fxgx,1)
from FN_GetBomtreeForWorkCenter(@Mrno,'',1) a
inner join INVMB b on MB001=bod_zxdm) a
*/
--SELECT TOP 100 MB001 FROM INVMB
