CTO_COMPLETE_INSTANCE_CTX_V

Details

• Schema: FUSION

• Object owner: CTO

• Object type: VIEW

Columns

Name

LINE_ID PARENT_LINE_ID BASE_CONFIG_ITEM_ID COMPONENT_SEQUENCE_ID BILL_SEQUENCE_ID COMPONENT_TYPE ORGANIZATION_ID SUB_ITEM_TYPE INVENTORY_ITEM_ID CONFIG_ITEM_ID REVISION_ID OPTION_CLASS_ITEM_ID USAGE_QTY UOM_CODE OPTIONAL_COMPONENT SUBSTITUTE_COMPONENT EFFECTIVITY_DATE DISABLE_DATE WIP_SUPPLY_TYPE PLANNING_FACTOR CHECK_ATP COMPONENT_YIELD_FACTOR ROUNDING_DIRECTION BASIS_TYPE COMPONENT_HIERARCHY COMPONENT_ITEM_HIERARCHY COMPONENT_EFFECTIVITY_DATE COMPONENT_DISABLE_DATE COMPONENT_QUANTITY ONE_TIME_CONFIG_FLAG CONFIG_ITEM_VERSION

Query

SQL_Statement

SELECT CASE WHEN ctl.inventory_item_id = v.inventory_item_id THEN ctl.line_id ELSE ctl.line_id END line_id, CASE WHEN ctl.inventory_item_id = v.inventory_item_id THEN ctl.parent_line_id ELSE ctl.line_id END parent_line_id, v.config_item_id AS base_config_item_id, to_char(v.component_sequence_id) component_sequence_id, CASE WHEN parent_line_id IS NULL AND ctl.inventory_item_id = v.inventory_item_id THEN v.bill_sequence_id ELSE v.common_bill_sequence_id END bill_sequence_id, v.component_type, v.organization_id, CASE WHEN ctl.inventory_item_id = v.inventory_item_id THEN ctl.sub_item_type ELSE 4 END sub_item_type, CAST(decode(ctl.linked_ato_line_id, NULL, decode(v.substitute_component, 'Y', v.substitute_component_id, v.inventory_item_id), ( SELECT config_item_id FROM cto_match_headers WHERE top_level_line_id = ctl.linked_ato_line_id )) AS NUMBER) inventory_item_id, v.config_item_id, CAST(NULL AS NUMBER) revision_id, ctl.option_class_id option_class_item_id, CASE WHEN ctl.inventory_item_id = v.inventory_item_id THEN ctl.unit_qty ELSE v.component_quantity END usage_qty, nvl(v.primary_uom_code, ctl.unit_uom) uom_code, v.optional_component, substitute_component, CASE WHEN v.non_cto_component = 'Y' OR optional_component = 'N' THEN v.effectivity_date ELSE CAST(ctl.creation_date AS DATE) END effectivity_date, CAST( CASE WHEN ctl.inventory_item_id = v.inventory_item_id THEN NULL ELSE v.disable_date END AS DATE) disable_date, CAST(v.wip_supply_type AS NUMBER) wip_supply_type, CAST(v.planning_factor AS NUMBER) planning_factor, CAST(v.check_atp AS NUMBER) check_atp, CAST(v.component_yield_factor AS NUMBER) component_yield_factor, 1 rounding_direction, CAST(v.basis_type AS NUMBER) basis_type, CASE WHEN v.component_hierarchy IS NULL THEN to_char(v.bill_sequence_id) ELSE v.root_bill_sequence_id || v.component_hierarchy END component_hierarchy, CASE WHEN v.component_item_hierarchy IS NULL THEN to_char(v.config_item_id) WHEN ctl.linked_ato_line_id IS NOT NULL THEN v.config_item_id || replace(v.component_item_hierarchy, v.inventory_item_id,( SELECT config_item_id FROM cto_match_headers WHERE top_level_line_id = ctl.linked_ato_line_id )) ELSE v.config_item_id || v.component_item_hierarchy END component_item_hierarchy, CAST(component_effectivity_date AS DATE) component_effectivity_date, CAST(component_disable_date AS DATE) component_disable_date, CAST(component_quantity AS NUMBER) component_quantity, v.one_time_config_flag, v.config_item_version FROM ( ( WITH pim_structure ( config_item_id, bill_sequence_id, parent_inventory_item_id, inventory_item_id, organization_id, item_type, parent_item_path, optional_component, component_sequence_id, bom_item_type, component_type, component_quantity, primary_uom_code, effectivity_date, disable_date, wip_supply_type, planning_factor, check_atp, component_yield_factor, basis_type, component_hierarchy, component_item_hierarchy, common_bill_sequence_id, header_id, bom_item_id, non_cto_component, root_bill_sequence_id, linked_ato_line_id, rounding_control_type, substitute_component, substitute_component_id, component_item_revision_id, component_effectivity_date, component_disable_date, cto_line_id, one_time_config_flag, config_item_version ) AS ( SELECT cto.config_item_id, bsb.bill_sequence_id bill_sequence_id, to_char(cto.config_item_id) parent_inventory_item_id, to_char(cto.config_item_id) inventory_item_id, bsb.pk2_value organization_id, 1 item_type, NULL parent_item_path, 'Y' optional_component, NULL component_sequence_id, 1 bom_item_type, 1 component_type, CAST(NULL AS NUMBER) component_quantity, NULL primary_uom_code, CAST(cto.creation_date AS DATE) effectivity_date, NULL disable_date, NULL wip_supply_type, NULL planning_factor, NULL check_atp, NULL component_yield_factor, NULL basis_type, NULL component_hierarchy, NULL component_item_hierarchy, bsb.common_bill_sequence_id common_bill_sequence_id, cto.header_id, bsb.pk1_value bom_item_id, 'N' non_cto_component, bsb.bill_sequence_id root_bill_sequence_id, NULL linked_ato_line_id, NULL rounding_control_type, 'N' substitute_component, NULL substitute_component_id, NULL component_item_revision_id, NULL component_effectivity_date, NULL component_disable_date, cto.top_level_line_id cto_line_id, cto.one_time_config_flag, cto.config_item_version FROM cto_match_headers cto, egp_structures_b bsb WHERE bsb.pk1_value = to_char(cto.base_model_id) AND bsb.alternate_bom_designator = 'Primary' AND cto.header_id = sys_context('CTO_CV_CONTEXT', 'HEADER_ID') AND bsb.pk2_value = sys_context('CTO_CV_CONTEXT', 'ORGANIZATION_ID') UNION ALL SELECT pim_structure.config_item_id, bsb.bill_sequence_id, pim_structure.inventory_item_id parent_inventory_item_id, bcb.pk1_value inventory_item_id, pim_structure.organization_id organization_id, bcb.bom_item_type item_type, ltrim(pim_structure.parent_item_path || '> ' || pim_structure.inventory_item_id, '> ') parent_item_path, decode(optional, 2, 'N', 'Y') optional_component, bcb.component_sequence_id, egp.bom_item_type, - 1 component_type, bcb.component_quantity, bcb.primary_uom_code, bcb.effectivity_date, bcb.disable_date, bcb.wip_supply_type, bcb.planning_factor, bcb.check_atp, bcb.component_yield_factor, bcb.basis_type, pim_structure.component_hierarchy || '-' || bcb.component_sequence_id component_hierarchy, pim_structure.component_item_hierarchy || '-' || decode(bcb.substitute_component, 'Y', bcb.substitute_component_id, bcb.pk1_value) component_item_hierarchy, bcb.bill_sequence_id common_bill_sequence_id, ctl.header_id, bcb.pk1_value bom_item_id, CASE WHEN bcb.optional = 2 AND egp.bom_item_type = 4 THEN 'Y' ELSE 'N' END non_cto_component, pim_structure.root_bill_sequence_id root_bill_sequence_id, ctl.linked_ato_line_id, nvl(bcb.enforce_int_requirements, 0) rounding_control_type, bcb.substitute_component, bcb.substitute_component_id, bcb.component_item_revision_id, bcb.effectivity_date component_effectivity_date, bcb.disable_date component_disable_date, ctl.line_id cto_line_id, one_time_config_flag, config_item_version FROM ( SELECT bcb.bill_sequence_id, bcb.optional, bcb.pk1_value, bcb.bom_item_type, bcb.component_sequence_id, bcb.component_quantity, bcb.primary_uom_code, bcb.effectivity_date, bcb.disable_date, bcb.wip_supply_type, bcb.check_atp, bcb.component_yield_factor, bcb.basis_type, 'N' substitute_component, bcb.enforce_int_requirements, NULL substitute_component_id, bcb.planning_factor, bcb.component_item_revision_id, bcb.effectivity_date component_effectivity_date, bcb.disable_date component_disable_date, bcb.implementation_date FROM egp_components_b bcb ) bcb, egp_structures_b bsb, pim_structure, cto_match_lines ctl, egp_system_items_vl egp WHERE bcb.bill_sequence_id = bsb.common_bill_sequence_id AND bsb.pk1_value = pim_structure.bom_item_id AND bsb.pk2_value = pim_structure.organization_id AND bsb.pk1_value = ctl.inventory_item_id AND ctl.sub_item_type IN ( 1, 2 ) AND ctl.header_id = pim_structure.header_id AND bsb.alternate_bom_designator = 'Primary' AND ctl.header_id = sys_context('CTO_CV_CONTEXT', 'HEADER_ID') AND bsb.pk2_value = sys_context('CTO_CV_CONTEXT', 'ORGANIZATION_ID') AND egp.inventory_item_id = bcb.pk1_value AND egp.organization_id = sys_context('CTO_CV_CONTEXT', 'ORGANIZATION_ID') AND ( bcb.component_effectivity_date IS NULL OR bcb.component_effectivity_date <= sysdate ) AND ( bcb.component_disable_date IS NULL OR bcb.component_disable_date > sysdate ) AND bcb.implementation_date IS NOT NULL AND ( ctl.parent_line_id = pim_structure.cto_line_id OR ctl.parent_line_id IS NULL ) ) SELECT * FROM pim_structure ) ) v, ( SELECT mh.config_item_id, decode(ml.parent_line_id, NULL, mh.config_item_id, ml.inventory_item_id) inventory_item_id, ltrim(sys_connect_by_path(decode(PRIOR ml.inventory_item_id, mh.base_model_id, mh.config_item_id, PRIOR ml.inventory_item_id ), '> '), '> ') parent_item_path, ml.line_id, PRIOR ml.line_id parent_line_id, ml.sub_item_type, ml.unit_qty, ml.unit_uom, ml.creation_date, decode(PRIOR ml.sub_item_type, 2, PRIOR ml.inventory_item_id, NULL) option_class_id, ltrim(sys_connect_by_path(decode(ml.inventory_item_id, mh.base_model_id, mh.config_item_id, ml.inventory_item_id), '> '), '> ') component_item_path, linked_ato_line_id, mh.one_time_config_flag, mh.config_item_version FROM cto_match_lines ml, cto_match_headers mh WHERE mh.header_id = ml.header_id AND mh.header_id = sys_context('CTO_CV_CONTEXT', 'HEADER_ID') START WITH ml.parent_line_id IS NULL AND ml.header_id = sys_context('CTO_CV_CONTEXT', 'HEADER_ID') CONNECT BY ( ml.parent_line_id = PRIOR ml.line_id ) ) ctl WHERE ctl.config_item_id = v.config_item_id AND ( ctl.inventory_item_id = v.inventory_item_id OR ( v.optional_component = 'N' AND v.parent_inventory_item_id = ctl.inventory_item_id AND v.bom_item_type = 4 AND ( ( ctl.sub_item_type = 1 AND ctl.parent_line_id IS NULL ) OR ctl.sub_item_type = 2 ) ) ) AND ( ( nvl(ctl.parent_item_path, - 1) = nvl(v.parent_item_path, - 1) ) OR ( v.optional_component = 'N' AND nvl(ctl.component_item_path, - 1) = nvl(v.parent_item_path, - 1) ) )