产成品按BOM展开各层级--按物料编码

代码示例

declare @ItemCode nvarchar(20)
declare @tmp int
select @tmp=count(*) from oitm t4 where t4.itemcode='[%0]'
set @itemcode='[%0]';
WITH Bom(Father, code, BomLevel,sort,warehouse,Qty,issue) 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
    FROM itt1 INNER JOIN oitt ON oitt.code = itt1.father
    where itt1.father not in (select code from itt1)
    and (itt1.father=@itemcode or @itemcode='')
    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
    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,bomlevel as 深度,'.'+ space((bom.bomlevel)*4) + 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 产成品规格
 from bom
left join oitm on bom.code=oitm.itemcode
union all
select distinct t.father,oitm.itemname,t.father,0,null,null,cast(oitt.Qauntity as nvarchar),null,null,null,oitm.frgnname as 产成品规格
from itt1 t
left join oitm on t.father =oitm.itemcode inner join oitt on t.father=oitt.code
where t.father not in (select code from itt1) and (t.father=@itemcode or @itemcode='')
) t9
order by sort