CTO_COMPLETE_INSTANCE

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 ONE_TIME_CONFIG_FLAG CONFIG_ITEM_VERSION

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

ONE_TIME_CONFIG_FLAG

CONFIG_ITEM_VERSION

Query

SQL_Statement
SELECT ci.LINE_ID, ci.PARENT_LINE_ID, ci.BASE_CONFIG_ITEM_ID, to_char(ci.COMPONENT_SEQUENCE_ID) COMPONENT_SEQUENCE_ID, ci.BILL_SEQUENCE_ID, ci.COMPONENT_TYPE, ci.ORGANIZATION_ID, ci.SUB_ITEM_TYPE, to_number(ci.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID , ci.CONFIG_ITEM_ID, to_number(ci.REVISION_ID) REVISION_ID, ci.OPTION_CLASS_ITEM_ID, ci.USAGE_QTY, ci.UOM_CODE, ci.OPTIONAL_COMPONENT, SUBSTITUTE_COMPONENT, ci.EFFECTIVITY_DATE, ci.DISABLE_DATE, to_number(ci.WIP_SUPPLY_TYPE) WIP_SUPPLY_TYPE, to_number(ci.PLANNING_FACTOR) PLANNING_FACTOR , ci.CHECK_ATP, to_number(ci.COMPONENT_YIELD_FACTOR) COMPONENT_YIELD_FACTOR, ci.rounding_control_type ROUNDING_DIRECTION, to_number(ci.BASIS_TYPE) BASIS_TYPE, ci.COMPONENT_HIERARCHY, ci.COMPONENT_ITEM_HIERARCHY, ci.ONE_TIME_CONFIG_FLAG, ci.CONFIG_ITEM_VERSION FROM (select case when v.non_cto_component = 'Y' then null else ctl.line_id end line_id, case when v.non_cto_component = 'Y' then ctl.line_id else ctl.parent_line_id end parent_line_id, v.config_item_id AS base_config_item_id, v.component_sequence_id, v.bill_sequence_id, v.component_type, v.organization_id, case when v.non_cto_component = 'Y' then 4 else ctl.sub_item_type end sub_item_type, 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)) inventory_item_id, v.config_item_id, null revision_id, ctl.option_class_id option_class_item_id, case when v.non_cto_component = 'Y' then v.component_quantity else ctl.unit_qty end usage_qty, case when v.non_cto_component = 'Y' then v.primary_uom_code else ctl.unit_uom end uom_code, v.optional_component, Substitute_Component, case when v.non_cto_component = 'Y' then v.effectivity_date else ctl.creation_date end effectivity_date, case when v.non_cto_component = 'Y' then v.disable_date else null end disable_date, v.wip_supply_type, v.planning_factor, v.check_atp, v.component_yield_factor, null rounding_direction, v.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, ctl.linked_ato_line_id, v.rounding_control_type, 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,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, 1 component_quantity, null primary_uom_code, cto.creation_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, cto.one_time_config_flag,cto.config_item_version from cto_match_headers cto, egp_structures_b bsb, cto_item_org_gt configlist where bsb.pk1_value = to_char(cto.base_model_id) and bsb.alternate_bom_designator = 'Primary' AND cto.config_item_id=configlist.config_item_id AND bsb.pk2_value = configlist.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, bcb.bom_item_type, -1 component_type, bcb.component_quantity, bcb.primary_uom_code, bcb.effectivity_date, bcb.disable_date, bcb.wip_supply_type,100 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, null common_bill_sequence_id, ctl.header_id , bcb.pk1_value bom_item_id, case when bcb.optional = 2 AND bcb.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, 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,null,null from EGP_COMPONENTS_B bcb UNION select bcb.bill_sequence_id ,bcb.optional,pk1_value,bcb.bom_item_type,bcb.component_sequence_id,bsc.SUBSTITUTE_ITEM_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,'Y' SUBSTITUTE_COMPONENT, bcb.enforce_int_requirements,to_char(bsc.SUBSTITUTE_COMPONENT_ID) substitute_component_id,null,null from EGP_COMPONENTS_B bcb,EGP_SUBSTITUTE_COMPONENTS bsc where bcb.component_sequence_id=bsc.component_Sequence_id) bcb, EGP_STRUCTURES_B bsb, pim_structure, cto_match_lines ctl 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.header_id in (select header_id from cto_match_headers mh,cto_item_org_gt ctolist where mh.config_item_id=ctolist.config_item_id) AND ctl.sub_item_type IN (1,2) AND ((bcb.pk1_value in (select inventory_item_id from cto_match_lines ctl1 where ctl1.header_id=pim_structure.header_id) and bcb.optional=1) or (bcb.optional=2 /and bcb.bom_item_type='4'/)) AND ctl.header_id = pim_structure.header_id and bsb.alternate_bom_designator = 'Primary' ) 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 , (select distinct config_item_id from cto_item_org_gt) configlist WHERE MH.HEADER_ID = ML.HEADER_ID AND MH.CONFIG_ITEM_ID=configlist.config_item_id START WITH ML.PARENT_LINE_ID IS NULL 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 IN (1,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))) ) ci

SQL_Statement

SELECT ci.LINE_ID, ci.PARENT_LINE_ID, ci.BASE_CONFIG_ITEM_ID, to_char(ci.COMPONENT_SEQUENCE_ID) COMPONENT_SEQUENCE_ID, ci.BILL_SEQUENCE_ID, ci.COMPONENT_TYPE,

ci.ORGANIZATION_ID, ci.SUB_ITEM_TYPE, to_number(ci.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID , ci.CONFIG_ITEM_ID, to_number(ci.REVISION_ID) REVISION_ID, ci.OPTION_CLASS_ITEM_ID, ci.USAGE_QTY, ci.UOM_CODE,

ci.OPTIONAL_COMPONENT, SUBSTITUTE_COMPONENT, ci.EFFECTIVITY_DATE, ci.DISABLE_DATE, to_number(ci.WIP_SUPPLY_TYPE) WIP_SUPPLY_TYPE, to_number(ci.PLANNING_FACTOR) PLANNING_FACTOR , ci.CHECK_ATP,

to_number(ci.COMPONENT_YIELD_FACTOR) COMPONENT_YIELD_FACTOR, ci.rounding_control_type ROUNDING_DIRECTION, to_number(ci.BASIS_TYPE) BASIS_TYPE, ci.COMPONENT_HIERARCHY, ci.COMPONENT_ITEM_HIERARCHY,

ci.ONE_TIME_CONFIG_FLAG, ci.CONFIG_ITEM_VERSION

FROM

(select

case when v.non_cto_component = 'Y' then null else ctl.line_id end line_id,

case when v.non_cto_component = 'Y' then ctl.line_id else ctl.parent_line_id end parent_line_id,

v.config_item_id AS base_config_item_id, v.component_sequence_id, v.bill_sequence_id, v.component_type, v.organization_id,

case when v.non_cto_component = 'Y' then 4 else ctl.sub_item_type end sub_item_type,

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)) inventory_item_id,

v.config_item_id, null revision_id, ctl.option_class_id option_class_item_id,

case when v.non_cto_component = 'Y' then v.component_quantity else ctl.unit_qty end usage_qty,

case when v.non_cto_component = 'Y' then v.primary_uom_code else ctl.unit_uom end uom_code,

v.optional_component, Substitute_Component,

case when v.non_cto_component = 'Y' then v.effectivity_date else ctl.creation_date end effectivity_date,

case when v.non_cto_component = 'Y' then v.disable_date else null end disable_date,

v.wip_supply_type, v.planning_factor, v.check_atp, v.component_yield_factor, null rounding_direction, v.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,

ctl.linked_ato_line_id,

v.rounding_control_type,

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,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, 1 component_quantity, null primary_uom_code, cto.creation_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,

cto.one_time_config_flag,cto.config_item_version

from cto_match_headers cto, egp_structures_b bsb,

cto_item_org_gt configlist

where bsb.pk1_value = to_char(cto.base_model_id)

and bsb.alternate_bom_designator = 'Primary'

AND cto.config_item_id=configlist.config_item_id

AND bsb.pk2_value = configlist.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, bcb.bom_item_type, -1 component_type,

bcb.component_quantity, bcb.primary_uom_code, bcb.effectivity_date, bcb.disable_date, bcb.wip_supply_type,100 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,

null common_bill_sequence_id, ctl.header_id ,

bcb.pk1_value bom_item_id, case when bcb.optional = 2 AND bcb.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,

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,null,null from EGP_COMPONENTS_B bcb

UNION

select bcb.bill_sequence_id ,bcb.optional,pk1_value,bcb.bom_item_type,bcb.component_sequence_id,bsc.SUBSTITUTE_ITEM_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,'Y' SUBSTITUTE_COMPONENT, bcb.enforce_int_requirements,to_char(bsc.SUBSTITUTE_COMPONENT_ID) substitute_component_id,null,null from EGP_COMPONENTS_B bcb,EGP_SUBSTITUTE_COMPONENTS bsc where bcb.component_sequence_id=bsc.component_Sequence_id) bcb,

EGP_STRUCTURES_B bsb, pim_structure, cto_match_lines ctl

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.header_id in (select header_id from cto_match_headers mh,cto_item_org_gt ctolist where mh.config_item_id=ctolist.config_item_id)

AND ctl.sub_item_type IN (1,2)

AND ((bcb.pk1_value in (select inventory_item_id from cto_match_lines ctl1 where ctl1.header_id=pim_structure.header_id) and bcb.optional=1) or (bcb.optional=2 /*and bcb.bom_item_type='4'*/))

AND ctl.header_id = pim_structure.header_id

and bsb.alternate_bom_designator = 'Primary'

)

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 ,

(select distinct config_item_id from cto_item_org_gt) configlist

WHERE MH.HEADER_ID = ML.HEADER_ID

AND MH.CONFIG_ITEM_ID=configlist.config_item_id

START WITH ML.PARENT_LINE_ID IS NULL

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 IN (1,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)))

) ci