产成品按BOM展开各层级--按物料编码(可查子件)
大约 1 分钟
代码示例
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=@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