Tuesday, August 18, 2015

BOM Bill of Materials explosion query in BOM Oracle Apps

SELECT TRIM (RPAD (' ', LEVEL + 1, '.')) || (LEVEL) assm_level,
                 LEVEL COMPONENT_LEVEL,
                 c.ITEM_NUM assm_ITEM_NUM,
                 c.OPERATION_SEQ_NUM assm_OPERATION_SEQ_NUM,
                 c.DISABLE_DATE assm_DISABLE_DATE,
                 (SELECT MAX (revision)
                    FROM mtl_item_revisions
                   WHERE     organization_id = P_ORGANIZATION_ID
                         AND EFFECTIVITY_DATE <= SYSDATE
                         AND inventory_item_id = c.COMPONENT_ITEM_ID)
            FROM (SELECT *
                    FROM apps.bom_inventory_components
                   WHERE TRUNC (SYSDATE) BETWEEN TRUNC (effectivity_date)
                                             AND TRUNC (
                                                    NVL (disable_date,
                                                         SYSDATE + 1))) c,
                 (SELECT *
                    FROM apps.bom_bill_of_materials
                   WHERE     alternate_bom_designator IS NULL
                         AND organization_id = P_organization_id) a
           WHERE 1 = 1 AND a.bill_sequence_id = c.bill_sequence_id
      START WITH a.assembly_item_id = P_ITEM_ID
      CONNECT BY NOCYCLE PRIOR c.component_item_id = a.assembly_item_id;

No comments:

Post a Comment