按BOM层级展开取最后采购报价记录单价合计BOM成本

图例

财务

示例代码

提示

按BOM层级展开取最后采购报价记录单价合计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 itt1.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)
    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 * D.Qty 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 注释,ts.price as 最后价格, bom.qty *ts.price  as 总计成本
 from bom
left join oitm on bom.code=oitm.itemcode
left join 
(
SELECT T0.[ItemCode], T0.[Price] FROM PQT1 T0
INNER JOIN (
SELECT T0.[ItemCode],MAX(T0.[ShipDate]) AS'DATE'
FROM PQT1 T0   
GROUP BY T0.[ItemCode]
)T1 ON T1.itemcode=t0.itemcode WHERE T0.[ShipDate] =t1.date
) ts on ts.itemcode=bom.code


union all 
select distinct t.father,oitm.itemname,t.father,'',null,null,null,null,null,null,oitm.frgnname as 产成品规格,
null, null  as 最后价格, null as 总计成本
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