ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8E2FF36EDEF379D2E0530100007F1FF2 INV Item (master) Y Y select msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv where msiv.organization_id=( select max(mp.master_organization_id) master_organization_id from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or fnd_profile.value('MFG_ORGANIZATION_ID') is null and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) ) order by msiv.concatenated_segments US Any item from item master org, including non costing enabled A7B8737C10DD6A1EE053B46B63584443 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 Y US TESTEP BOM :item 1 10 :item LOV INV Item (master) 8E2FF36EDEF379D2E0530100007F1FF2 Y Y select msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv where msiv.organization_id=( select max(mp.master_organization_id) master_organization_id from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or fnd_profile.value('MFG_ORGANIZATION_ID') is null and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) ) order by msiv.concatenated_segments US Item