SQL写出带扩展数量的BOM多级清单代码,OracleEBS代码库,德仔网

代码语言
代码分类
【OracleEBS】 SQL写出带扩展数量的BOM多级清单
作者:专注Oracle EBS / 发布于2012/11/29/ 1114
只用SQL的原因可能为：

因为没有权限去修改和定义程序。
我们唯一能用的手段就是Discover Report，因此需要用SQL来实现多级BOM展开。

代码

Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/--> 1 with t as(
 SELECT boms.organization_id,
        boms.organization_id || '>' || connect_by_root assembly_number || sys_connect_by_path(boms.component_number, '>') code_chain,
        boms.organization_id || '>' || connect_by_root bill_sequence_id || sys_connect_by_path(boms.component_sequence_id, '>') id_chain,
        connect_by_root assembly_number assembly_number,
        boms.assembly_description,
        LEVEL bom_level,
        boms.component_number component_number,
        lpad(' ', (LEVEL - 1) * 2, ' ') || boms.component_number ind_component_number,
        boms.component_description,
        boms.primary_uom_code uom,
        boms.component_quantity component_quantity,
        boms.planning_factor,
        boms.component_yield_factor,
        boms.effectivity_date
   FROM (SELECT bom1.organization_id,
                bom1.assembly_item_id,
                mst1.segment1 assembly_number,
                mst1.description assembly_description,
                bom1.bill_sequence_id bill_sequence_id,
                bom1.alternate_bom_designator assembly_alternate,
                bomc.component_sequence_id,
                bomc.component_item_id,
                mstc.segment1 component_number,
                mstc.description component_description,
                mstc.primary_uom_code,
                bomc.component_quantity,
                bomc.effectivity_date,
                bomc.planning_factor,
                bomc.component_yield_factor,
                bomc.supply_subinventory
           FROM apps.bom_bill_of_materials    bom1,
                inv.mtl_system_items_b        mst1,
                apps.bom_inventory_components bomc,
                inv.mtl_system_items_b        mstc
          WHERE bom1.organization_id = mst1.organization_id
            AND bom1.assembly_item_id = mst1.inventory_item_id
            AND bom1.bill_sequence_id = bomc.bill_sequence_id
            AND bom1.organization_id = mstc.organization_id
            AND bomc.component_item_id = mstc.inventory_item_id
               --Item
            AND mst1.bom_enabled_flag = 'Y'
            AND mst1.bom_item_type IN (1, 2, 3, 4) --Dependent
               --BOM Header
            AND bom1.assembly_type = 1 --1 Manufature,2 ENG
            AND nvl(bom1.effectivity_control, 1) <= 3
               --BOM Line
            AND nvl(bomc.disable_date, SYSDATE) >= SYSDATE
            AND bomc.effectivity_date <= SYSDATE
            AND bomc.implementation_date IS NOT NULL
            AND nvl(bomc.eco_for_production, 2) = 2
               --Filters
            AND mst1.organization_id = 207
            AND bom1.alternate_bom_designator IS NULL) boms
 CONNECT BY PRIOR boms.organization_id = boms.organization_id
        AND PRIOR boms.component_item_id = boms.assembly_item_id
 )
 SELECT t1.organization_id,
        t1.code_chain,
        t1.assembly_number,
        t1.assembly_description,
        t1.bom_level,
        t1.component_number,
        t1.component_description,
        t1.uom,
        t1.ind_component_number,
        t1.id_chain,
        t1.component_quantity,
        (SELECT power(10,
                      SUM(CASE
                            WHEN t2.component_quantity = 0 THEN
                             0
                            ELSE
                             log(10, abs(t2.component_quantity))
                          END)) * decode(MOD(COUNT(decode(sign(t2.component_quantity), -1, 1)), 2), 1, -1, 1) *
                (CASE
                   WHEN COUNT(decode(t2.component_quantity, 0, 1)) >= 1 THEN
                    0
                   ELSE
                    1
                 END)
           FROM t t2
          WHERE t1.id_chain LIKE t2.id_chain || '%') extended_quantity,
        t1.planning_factor,
        t1.component_yield_factor,
        t1.effectivity_date
   FROM t t1
 ORDER BY t1.id_chain;
试试其它关键字
　BOM多级清单　
同语言下
可能有用的
专注Oracle EBS贡献的其它代码(66)