TESTEP BOM

SELECT /*+ leading (bom) */ 
       level,
       ch AS item,
       bom.parent,
       description,
       (SELECT /*+ ORDERED */ a.element_value
          FROM apps.mtl_descr_element_values a,
               apps.mtl_descriptive_elements b,
               apps.mtl_item_catalog_groups  c
         WHERE 1 = 1
           AND a.element_name = b.element_name
           AND b.item_catalog_group_id = c_itemCatGrpId
           AND b.item_catalog_group_id = c.item_catalog_group_id
           AND a.element_name = b.element_name
           AND c.segment1 = 'FB Items'
           AND a.element_name LIKE 'Description 1'
           AND a.inventory_item_id = c_id
           AND rownum = 1) AS Catalog_Desc,
       TO_CHAR(c_ed, 'DD-MON-YYYY') AS Effectivity_Date,
       TO_CHAR(c_ed_to, 'DD-MON-YYYY') AS Disable_Date,
       c_qty AS Component_quantity,
       c_puom AS UOM,
       ltrim(sys_connect_by_path(bom.c_qty, '*'), '*') ExtendedQuantityString,
       Routing_Status,
       Operation_Seq_Num,
       resource_code,
       Cost_Center,
       --usage_rate_or_amount,
       Lot_Specific_Time,
       Item_Specific_Time,
       NULL Time_per_Item, -- calculate in report
       (SELECT CIC.LOT_SIZE
          FROM apps.CST_ITEM_COSTS CIC, apps.CST_COST_TYPES CCT
         WHERE 1 = 1
           AND CCT.COST_TYPE_ID = CIC.COST_TYPE_ID
           AND CCT.COST_TYPE = 'Frozen'
           AND CIC.organization_id = c_oi
           AND CIC.INVENTORY_ITEM_ID = c_id
           AND rownum = 1) LotSize_CostType1,
       (SELECT CIC.LOT_SIZE
          FROM apps.CST_ITEM_COSTS CIC, apps.CST_COST_TYPES CCT
         WHERE 1 = 1
           AND CCT.COST_TYPE_ID = CIC.COST_TYPE_ID
           AND CCT.COST_TYPE = 'Frozen'
           AND CIC.organization_id = c_oi
           AND CIC.INVENTORY_ITEM_ID = c_id
           AND rownum = 1) LotSize_CostType2,
       c_item_lot_size,
       (SELECT ROUND(CRC.RESOURCE_RATE * 60, 4)
          FROM apps.CST_RESOURCE_COSTS CRC, apps.CST_COST_TYPES CCT
         WHERE 1 = 1
           AND CCT.COST_TYPE_ID = CRC.COST_TYPE_ID
           AND CCT.COST_TYPE = 'Frozen'
           AND CRC.organization_id = resource_oi
           AND crc.resource_id = Res_Id
           AND rownum = 1) ResourceCost_CostType_1,
       (SELECT CDO.RATE_OR_AMOUNT * 60
          FROM apps.BOM_RESOURCES            RES,
               apps.BOM_RESOURCES            OVH,
               apps.CST_COST_TYPES           CCT,
               apps.CST_RESOURCE_OVERHEADS   CRO,
               apps.CST_DEPARTMENT_OVERHEADS CDO
         WHERE CCT.COST_TYPE_ID = CRO.COST_TYPE_ID
           AND RES.RESOURCE_ID = CRO.RESOURCE_ID
           AND OVH.RESOURCE_ID = CRO.OVERHEAD_ID
           AND CCT.COST_TYPE_ID = CDO.COST_TYPE_ID
           AND OVH.RESOURCE_ID = CDO.OVERHEAD_ID
           AND RES.RESOURCE_ID = Res_Id
           AND CCT.COST_TYPE = 'Frozen'
           AND OVH.ATTRIBUTE2 = 'Variable Overhead'
           AND rownum < 2) VOHCost_Type1,
       (SELECT CDO.RATE_OR_AMOUNT * 60
          FROM apps.BOM_RESOURCES            RES,
               apps.BOM_RESOURCES            OVH,
               apps.CST_COST_TYPES           CCT,
               apps.CST_RESOURCE_OVERHEADS   CRO,
               apps.CST_DEPARTMENT_OVERHEADS CDO
         WHERE CCT.COST_TYPE_ID = CRO.COST_TYPE_ID
           AND RES.RESOURCE_ID = CRO.RESOURCE_ID
           AND OVH.RESOURCE_ID = CRO.OVERHEAD_ID
           AND CCT.COST_TYPE_ID = CDO.COST_TYPE_ID
           AND OVH.RESOURCE_ID = CDO.OVERHEAD_ID
           AND RES.RESOURCE_ID = Res_Id
           AND CCT.COST_TYPE = 'Frozen'
           AND OVH.ATTRIBUTE2 = 'Fixed Overhead 1'
           AND rownum < 2) FOH1Cost_Type1,
       (SELECT CDO.RATE_OR_AMOUNT * 60
          FROM apps.BOM_RESOURCES            RES,
               apps.BOM_RESOURCES            OVH,
               apps.CST_COST_TYPES           CCT,
               apps.CST_RESOURCE_OVERHEADS   CRO,
               apps.CST_DEPARTMENT_OVERHEADS CDO
         WHERE CCT.COST_TYPE_ID = CRO.COST_TYPE_ID
           AND RES.RESOURCE_ID = CRO.RESOURCE_ID
           AND OVH.RESOURCE_ID = CRO.OVERHEAD_ID
           AND CCT.COST_TYPE_ID = CDO.COST_TYPE_ID
           AND OVH.RESOURCE_ID = CDO.OVERHEAD_ID
           AND RES.RESOURCE_ID = Res_Id
           AND CCT.COST_TYPE = 'Frozen'
           AND OVH.ATTRIBUTE2 = 'Fixed Overhead 2'
           AND rownum < 2) FOH2Cost_Type1,
       (SELECT CDO.RATE_OR_AMOUNT * 60
          FROM apps.BOM_RESOURCES            RES,
               apps.BOM_RESOURCES            OVH,
               apps.CST_COST_TYPES           CCT,
               apps.CST_RESOURCE_OVERHEADS   CRO,
               apps.CST_DEPARTMENT_OVERHEADS CDO
         WHERE CCT.COST_TYPE_ID = CRO.COST_TYPE_ID
           AND RES.RESOURCE_ID = CRO.RESOURCE_ID
           AND OVH.RESOURCE_ID = CRO.OVERHEAD_ID
           AND CCT.COST_TYPE_ID = CDO.COST_TYPE_ID
           AND OVH.RESOURCE_ID = CDO.OVERHEAD_ID
           AND RES.RESOURCE_ID = Res_Id
           AND CCT.COST_TYPE = 'Frozen'
           AND OVH.ATTRIBUTE2 = 'Quality Overhead'
           AND rownum < 2) COQCost_Type1,
       (SELECT ROUND(CRC.RESOURCE_RATE * 60, 4)
          FROM apps.CST_RESOURCE_COSTS CRC, apps.CST_COST_TYPES CCT
         WHERE 1 = 1
           AND CCT.COST_TYPE_ID = CRC.COST_TYPE_ID
           AND CCT.COST_TYPE = 'Frozen'
           AND CRC.organization_id = resource_oi
           AND crc.resource_id = Res_Id
           AND rownum = 1) ResourceCost_CostType_2,
       (SELECT CDO.RATE_OR_AMOUNT * 60
          FROM apps.BOM_RESOURCES            RES,
               apps.BOM_RESOURCES            OVH,
               apps.CST_COST_TYPES           CCT,
               apps.CST_RESOURCE_OVERHEADS   CRO,
               apps.CST_DEPARTMENT_OVERHEADS CDO
         WHERE CCT.COST_TYPE_ID = CRO.COST_TYPE_ID
           AND RES.RESOURCE_ID = CRO.RESOURCE_ID
           AND OVH.RESOURCE_ID = CRO.OVERHEAD_ID
           AND CCT.COST_TYPE_ID = CDO.COST_TYPE_ID
           AND OVH.RESOURCE_ID = CDO.OVERHEAD_ID
           AND RES.RESOURCE_ID = Res_Id
           AND CCT.COST_TYPE = 'Frozen'
           AND OVH.ATTRIBUTE2 = 'Variable Overhead'
           AND rownum < 2) VOHCost_Type2,
       (SELECT CDO.RATE_OR_AMOUNT * 60
          FROM apps.BOM_RESOURCES            RES,
               apps.BOM_RESOURCES            OVH,
               apps.CST_COST_TYPES           CCT,
               apps.CST_RESOURCE_OVERHEADS   CRO,
               apps.CST_DEPARTMENT_OVERHEADS CDO
         WHERE CCT.COST_TYPE_ID = CRO.COST_TYPE_ID
           AND RES.RESOURCE_ID = CRO.RESOURCE_ID
           AND OVH.RESOURCE_ID = CRO.OVERHEAD_ID
           AND CCT.COST_TYPE_ID = CDO.COST_TYPE_ID
           AND OVH.RESOURCE_ID = CDO.OVERHEAD_ID
           AND RES.RESOURCE_ID = Res_Id
           AND CCT.COST_TYPE = 'Frozen'
           AND OVH.ATTRIBUTE2 = 'Fixed Overhead 1'
           AND rownum < 2) FOH1Cost_Type2,
       (SELECT CDO.RATE_OR_AMOUNT * 60
          FROM apps.BOM_RESOURCES            RES,
               apps.BOM_RESOURCES            OVH,
               apps.CST_COST_TYPES           CCT,
               apps.CST_RESOURCE_OVERHEADS   CRO,
               apps.CST_DEPARTMENT_OVERHEADS CDO
         WHERE CCT.COST_TYPE_ID = CRO.COST_TYPE_ID
           AND RES.RESOURCE_ID = CRO.RESOURCE_ID
           AND OVH.RESOURCE_ID = CRO.OVERHEAD_ID
           AND CCT.COST_TYPE_ID = CDO.COST_TYPE_ID
           AND OVH.RESOURCE_ID = CDO.OVERHEAD_ID
           AND RES.RESOURCE_ID = Res_Id
           AND CCT.COST_TYPE = 'Frozen'
           AND OVH.ATTRIBUTE2 = 'Fixed Overhead 2'
           AND rownum < 2) FOH2Cost_Type2,
       (SELECT CDO.RATE_OR_AMOUNT * 60
          FROM apps.BOM_RESOURCES            RES,
               apps.BOM_RESOURCES            OVH,
               apps.CST_COST_TYPES           CCT,
               apps.CST_RESOURCE_OVERHEADS   CRO,
               apps.CST_DEPARTMENT_OVERHEADS CDO
         WHERE CCT.COST_TYPE_ID = CRO.COST_TYPE_ID
           AND RES.RESOURCE_ID = CRO.RESOURCE_ID
           AND OVH.RESOURCE_ID = CRO.OVERHEAD_ID
           AND CCT.COST_TYPE_ID = CDO.COST_TYPE_ID
           AND OVH.RESOURCE_ID = CDO.OVERHEAD_ID
           AND RES.RESOURCE_ID = Res_Id
           AND CCT.COST_TYPE = 'Frozen'
           AND OVH.ATTRIBUTE2 = 'Quality Overhead'
           AND rownum < 2) COQCost_Type2
  FROM (SELECT ood.organization_code,
               ood.organization_id AS c_oi,
               msi2.segment1 AS ch,
               msi2.inventory_item_id AS c_id,
               bic.operation_seq_num AS c_a,
               bic.item_num AS c_b,
               msi2.description AS description,
               msi1.segment1 AS parent,
               msi1.inventory_item_id AS parent_id,
               bic.component_quantity AS c_qty,
               bic.effectivity_date AS c_ed,
               bic.implementation_date AS c_ed_from,
               bic.disable_date AS c_ed_to,
               msi2.primary_unit_of_measure AS c_puom,
               msi2.creation_date AS c_cd,
               msi2.created_by AS c_cb,
               msi2.last_update_date AS c_lud,
               msi2.last_updated_by AS c_lub,
               msi2.inventory_item_status_code AS c_is,
               bic.operation_seq_num AS c_os,
               bic.item_num AS c_ItemSeq,
               msi2.item_type AS c_it,
               bic.wip_supply_type AS c_st,
               msi2.preprocessing_lead_time AS c_preplt,
               msi2.full_lead_time AS c_fult,
               msi2.postprocessing_lead_time AS c_poplt,
               msi2.fixed_lead_time AS c_filt,
               msi2.variable_lead_time AS c_vlt,
               msi2.cum_manufacturing_lead_time AS c_cmflt,
               msi2.cumulative_total_lead_time AS c_ctlt,
               msi2.lead_time_lot_size AS c_ltls,
               msi2.ITEM_CATALOG_GROUP_ID AS c_itemCatGrpId,
               msi2.planning_make_buy_code AS C_planning_make_buy_code,
               msi2.STD_LOT_SIZE AS c_item_lot_size,
               bic.component_item_id,
               bbom.assembly_item_id,
               boru.attribute1 Routing_status,
               bor.usage_rate_or_amount,
               gcc.segment5 Cost_Center,
               CASE
                 WHEN bor.basis_type = 2 THEN
                  ROUND(bor.usage_rate_or_amount / 60, 4)
                 ELSE
                  NULL
               END Lot_Specific_Time,
               CASE
                 WHEN bor.basis_type = 1 THEN
                  ROUND(bor.usage_rate_or_amount / 60, 4)
                 ELSE
                  NULL
               END Item_Specific_Time,
               br.resource_id AS Res_Id,
               br.organization_id AS resource_oi,
               bos.Operation_Seq_Num,
               br.resource_code
          FROM
               apps.org_organization_definitions ood,
               apps.bom_bill_of_materials        bbom,
               apps.mtl_system_items             msi1,
               apps.bom_inventory_components     bic,
               apps.mtl_system_items             msi2,
               apps.bom_operational_routings     boru,
               apps.bom_operation_sequences      bos,
               apps.bom_operation_resources      bor,
               apps.bom_resources                br,
               apps.gl_code_combinations         gcc
         WHERE 2 = 2
           AND bbom.assembly_item_id = msi1.inventory_item_id
           AND bbom.organization_id = msi1.organization_id
           AND bbom.alternate_bom_designator IS NULL
           AND msi1.organization_id = ood.organization_id
           AND bbom.bill_sequence_id = bic.bill_sequence_id
           AND bic.component_item_id = msi2.inventory_item_id
           AND ood.organization_id = msi2.organization_id
           AND NVL(bic.disable_date, sysdate) >= SYSDATE
           AND NVL(bic.implementation_date, sysdate) <= SYSDATE
           AND boru.assembly_item_id = msi2.inventory_item_id
           AND boru.organization_id = msi2.organization_id
           AND boru.routing_sequence_id = bos.routing_sequence_id
           AND bor.operation_sequence_id = bos.operation_sequence_id
           AND br.resource_id = bor.resource_id
           AND NVL(bos.disable_date, SYSDATE) >= sysdate
           AND bos.effectivity_date <= sysdate
           AND NVL(boru.alternate_routing_designator, 'Primary') IN
               ('Primary')
              --AND boru.alternate_routing_designator IS NULL
           AND gcc.code_combination_id = br.absorption_account) bom
START WITH bom.parent = :Item
CONNECT BY nocycle PRIOR bom.component_item_id = bom.assembly_item_id
 ORDER SIBLINGS BY c_a, c_b, parent_id