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 |
