Tuesday, August 18, 2015

BOM Bill of materials Implosion Query in Oracle Apps



SELECT TRIM (RPAD (' ', LEVEL + P_LEVEL + 1, '.')) || (LEVEL)
                    assm_level,
                 LEVEL + P_LEVEL COMPONENT_LEVEL,
                 c.component_item_id,
                 a.organization_id,
                 c.ITEM_NUM assm_ITEM_NUM,
                 c.OPERATION_SEQ_NUM assm_OPERATION_SEQ_NUM,
                 c.EFFECTIVITY_DATE assm_EFFECTIVITY_DATE,
                 c.DISABLE_DATE assm_DISABLE_DATE,
                 c.COMPONENT_QUANTITY assm_COMPONENT_QUANTITY,
                 (SELECT MAX (revision)
                    FROM mtl_item_revisions
                   WHERE     organization_id = P_ORG_id
                         AND EFFECTIVITY_DATE <= SYSDATE
                         AND inventory_item_id = a.assembly_item_id)
                    assm_item_rev,
                 a.assembly_item_id,
                 a.bill_sequence_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_ORG_id) a
           WHERE 1 = 1 AND a.bill_sequence_id = c.bill_sequence_id
      START WITH c.component_item_id = P_COMPONENT_ITEM_ID
      CONNECT BY NOCYCLE PRIOR a.assembly_item_id = c.component_item_id;

No comments:

Post a Comment