Bill of Materials - BOM Total Explosion Query in Oracle Applications

 SELECT TRIM (RPAD (' ', LEVEL + 1, '.')) || (LEVEL) assm_level,
                 LEVEL COMPONENT_LEVEL,
                 c.component_item_id,
                 (Select segment1 from apps.mtl_system_items_b msi where msi.inventory_item_id = c.component_item_id and organization_id = 87) component_name,
                 (Select description from apps.mtl_system_items_b msi where msi.inventory_item_id = c.component_item_id and organization_id = 87) component_description,
                 a.organization_id,
                 :P_ORG org_code,
                 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,
                 c.attribute6 assm_eco
            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 = (Select organization_id from apps.mtl_parameters mp where  mp.organization_code = :P_ORG)
                    ) a
           WHERE 1 = 1 AND a.common_bill_sequence_id = c.bill_sequence_id
      START WITH a.assembly_item_id = (Select msi.inventory_item_id from apps.mtl_system_items_b msi where segment1 = :P_ITEM and organization_id = 87)
      CONNECT BY NOCYCLE PRIOR c.component_item_id = a.assembly_item_id;


_________________________________________________________________________________

    SELECT TRIM (RPAD (' ', LEVEL + 1, '.')) || (LEVEL) assm_level,
           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,
           c.attribute6 assm_eco
      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 = 6047
             ) a,
           (SELECT msi.inventory_item_id, mp.organization_id
              FROM apps.mtl_system_items_b msi, apps.mtl_parameters mp
             WHERE     mp.organization_id = msi.organization_id
                   AND mp.organization_code = :P_ORG
                   AND msi.segment1 = :P_ITEM) item
     WHERE 1 = 1 AND a.organization_id = item.organization_id and a.common_bill_sequence_id = c.bill_sequence_id
START WITH a.assembly_item_id = item.inventory_item_id
CONNECT BY NOCYCLE PRIOR c.component_item_id = a.assembly_item_id;