产成品按BOM展开层级--多条件筛选（可查子件）

代码示例

--declare @ItemCode nvarchar(20)
--declare @tmp int
--select    itemcode from oitm t4 where t4.itemcode='[%0]';
--select    itemname from oitm t4 where t4.itemname='[%1]';
--select    T4.frgnname from oitm  t4 where t4.frgnname='[%2]';
--select @tmp=count(*) from oitm t4 where t4.itemcode='[%0]'
--select @tmp=count(*) from oitm t4 where t4.itemname='[%1]'
--select @tmp=count(*) from oitm t4 where t4.frgnname='[%2]'
--set @itemcode='[%0]';
declare @itemcode nvarchar(100)
declare @itemname nvarchar(200)
declare @frgnname nvarchar(200)
set @itemcode = '%' + [%0] + '%';
set @itemname = '%' + [%1] + '%';
set @frgnname = '%' + [%2] + '%';
select   @frgnname=frgnname from oitm t4 where t4.frgnname='[%1]';
WITH Bom(Father, code, BomLevel,sort,warehouse,Qty,issue,mecomment) AS 
(
    SELECT itt1.father, itt1.code, 1 AS bomlevel,convert(varchar(255), itt1.father+char(9) + RIGHT('00000'+CAST(itt1.childnum as varchar(10)),5) +  char(9) +itt1.code),itt1.warehouse, cast (itt1.quantity / OITT.QAuNTITY AS NUMERIC (19, 6)) AS quantity,itt1.issuemthd,itt1.comment
    FROM itt1 INNER JOIN oitt ON oitt.code = itt1.father inner join OITM T99 on T99.itemcode=itt1.father
    where ( T99.itemcode like @itemcode or T99.itemcode is null) and (T99.frgnname like @frgnname or T99.frgnname is null) and  (T99.itemname like @itemname or T99.itemname is null)
    UNION ALL
    SELECT e.father, e.code, bomlevel + 1,convert(varchar(255),rtrim(sort)+char(9)+ RIGHT('00000'+CAST(e.childnum as varchar(10)),5) +  char(9) +e.code),e.warehouse,cast (e.quantity / OITT.Qauntity   AS NUMERIC (19, 6)) AS quantity,e.issuemthd,e.comment
    FROM itt1 e INNER JOIN OITT ON E.FATHER=OITT.CODE
        INNER JOIN Bom d
        ON e.father = d.code 
)


select 产成品,品名,产成品规格,深度,物料号码,规格,物料描述,注释,用量,发货仓库,发货方法 from (

SELECT null as 产成品,null as 品名, sort, '.'+  space((bom.bomlevel)*4)+cast( bomlevel as nvarchar)  as 深度,    bom.code as 物料号码 , oitm.itemname as 物料描述, cast(bom.qty as nvarchar) AS 用量,bom.warehouse as 发货仓库,
     case bom.issue when 'M' then N'手动' when 'B' then N'倒冲' end as 发货方法,oitm.frgnname as 规格,null as 产成品规格,bom.mecomment 注释
 from bom
left join oitm on bom.code=oitm.itemcode
union all 
select distinct t.father,oitm.itemname,t.father,'',null,null,cast(oitt.Qauntity as nvarchar),null,null,null,oitm.frgnname as 产成品规格,
null
from itt1 t
left join oitm on t.father =oitm.itemcode   inner join oitt on t.father=oitt.code inner join OITM T99 on T99.itemcode=t.father 
where t.father not in (select code from itt1) and ( T99.itemcode like @itemcode  or T99.itemcode  is null) and (T99.frgnname like @frgnname or T99.frgnname  is null)  and  (T99.itemname like @itemname or T99.itemname is null)
) t9
order by sort