4.移转 Aras PLM大小事-Agile 汇出 Part & BOM (2)

第4话 Agile 汇出 Part & BOM (2)

本篇讲解如何汇出BOM表
主料BOM表要汇出的表格如下

select
        item_par.item_number source_id, ---父阶料号
        bom.item_number related_id, ---子阶料号
        bom.find_number sort_order, ---序号
        NVL(bom.quantity,'1') quantity,  ---用量
        NVL(bom.text01,' ') alternative, ---替代料顺序
        (select  rtrim(xmlagg(xmlelement(e, label, ',')).extract('//text()').getclobval(), ',') 
            from refdesig  r 
            where r.bom = bom.id) reference_designator ---组成用量
        ,bom.text02 cn_attrition_rate ---自订损耗率
        ,( select  f.text
            from agile_flex f 
            where f.row_id = bom.id and f.attid =1036
        ) cn_bom_note ---备注
from bom bom
inner join item item_par on item_par.id = bom.item
inner join rev rev_p on rev_p.item = bom.item
inner join nodetable nb on nb.id = item_par.subclass
where
NVL (bom.change_out, 0) = 0
AND (SUBSTR (bom.flags, 5, 1) = 1)
AND (bom.text01 is null OR Trim(bom.text01)= 'R1')
AND rev_p.latest_flag = 1
AND rev_p.release_date >= TO_DATE('2008-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND rev_p.release_date <= TO_DATE('2008-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
order by bom.item,lpad( bom.find_number, 3 )

• 各个栏位如何搜索可参考Select，这里说明Where条件

BOM替代料汇出

select
        item_par.item_number source_id,
        (
            select bom.item_number from bom 
            where
            NVL (bom.change_out, 0) = 0
            AND (SUBSTR (bom.flags, 5, 1) = 1)
            AND bom.item = alterBOM.item
            AND bom.find_number = alterbom.find_number
            AND bom.text01 = 'R1'
        ) main_part,
        alterBOM.item_number related_id,
        alterBOM.find_number sort_order,
        to_number(SPLITSTR(alterBOM.text01,2,'R'))-1   alterantive,
        NVL(alterbom.quantity,'1') quantity
        ,alterBOM.text02 cn_attrition_rate
        ,( select  f.text
            from agile_flex f 
            where f.row_id = alterBOM.id and f.attid =1036
        ) cn_bom_note
    from bom alterBOM
    inner join item item_par on item_par.id = alterBOM.item
    inner join rev rev_p on rev_p.item = alterBOM.item
       inner join nodetable nb on nb.id = item_par.subclass
    where
    NVL (alterBOM.change_out, 0) = 0
    AND (SUBSTR (alterBOM.flags, 5, 1) = 1)
    AND alterbom.text01 <> 'R1'
    AND alterbom.text01 is not null
    AND rev_p.release_date >= TO_DATE('2005-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
    AND rev_p.release_date <= TO_DATE('2009-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
    AND rev_p.LATEST_FLAG = 1 
    order by item_par.item_number,lpad( alterBOM.find_number, 3 )

• Where 条件的Alternative必须跳过R1、空白
• to_number(SPLITSTR(alterBOM.text01,2,'R'))-1 计算R的顺序

可参考其他文章说明
https://www.cnblogs.com/namelessmyth/p/13042193.html