CTO_COMPLETE_INSTANCE_CTX

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

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

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

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

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

) 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

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

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

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)

)