CTO_COMPLETE_INSTANCE_EXPL

Details

Schema: FUSION
Object owner: FUSION
Object type: VIEW

Columns

Name
ITEM_NUMBER DESCRIPTION LINE_ID PARENT_LINE_ID BASE_CONFIG_ITEM_ID COMPONENT_SEQUENCE_ID BILL_SEQUENCE_ID COMPONENT_TYPE SUB_ITEM_TYPE ORGANIZATION_ID INVENTORY_ITEM_ID REVISION_ID USAGE_QTY UOM_CODE OPTIONAL_COMPONENT SUBSTITUTE_COMPONENT EFFECTIVITY_DATE DISABLE_DATE WIP_SUPPLY_TYPE PLANNING_FACTOR CHECK_ATP COMPONENT_YIELD_FACTOR BASIS_TYPE COMPONENT_HIERARCHY COMPONENT_ITEM_HIERARCHY PARENT_INVENTORY_ITEM_ID ROOT_PHANTOM_ITEM_ID ROUNDING_DIRECTION ITEM_CLASS_ID OPTION_CLASS_ITEM_ID CONFIG_ITEM_ID LINE_EXPL_ID SUBSTITTUE_FOR_COMPONENT PARENT_ITEM_NUMBER BASE_CONFIG_ITEM_NUMBER CONFIG_EFFECTIVITY_DATE ONE_TIME_CONFIG_FLAG CONFIG_ITEM_VERSION

Name

ITEM_NUMBER

DESCRIPTION

LINE_ID

PARENT_LINE_ID

BASE_CONFIG_ITEM_ID

COMPONENT_SEQUENCE_ID

BILL_SEQUENCE_ID

COMPONENT_TYPE

SUB_ITEM_TYPE

ORGANIZATION_ID

INVENTORY_ITEM_ID

REVISION_ID

USAGE_QTY

UOM_CODE

OPTIONAL_COMPONENT

SUBSTITUTE_COMPONENT

EFFECTIVITY_DATE

DISABLE_DATE

WIP_SUPPLY_TYPE

PLANNING_FACTOR

CHECK_ATP

COMPONENT_YIELD_FACTOR

BASIS_TYPE

COMPONENT_HIERARCHY

COMPONENT_ITEM_HIERARCHY

PARENT_INVENTORY_ITEM_ID

ROOT_PHANTOM_ITEM_ID

ROUNDING_DIRECTION

ITEM_CLASS_ID

OPTION_CLASS_ITEM_ID

CONFIG_ITEM_ID

LINE_EXPL_ID

SUBSTITTUE_FOR_COMPONENT

PARENT_ITEM_NUMBER

BASE_CONFIG_ITEM_NUMBER

CONFIG_EFFECTIVITY_DATE

ONE_TIME_CONFIG_FLAG

CONFIG_ITEM_VERSION

Query

SQL_Statement
SELECT ITM.ITEM_NUMBER, ITM.DESCRIPTION, LINE_ID, PARENT_LINE_ID,BASE_CONFIG_ITEM_ID, COMPONENT_SEQUENCE_ID, BILL_SEQUENCE_ID, COMPONENT_TYPE, SUB_ITEM_TYPE, BASE.ORGANIZATION_ID, V.INVENTORY_ITEM_ID, REVISION_ID, USAGE_QTY, UOM_CODE, OPTIONAL_COMPONENT,SUBSTITUTE_COMPONENT, EFFECTIVITY_DATE, DISABLE_DATE, V.WIP_SUPPLY_TYPE, PLANNING_FACTOR, CHECK_ATP, COMPONENT_YIELD_FACTOR, BASIS_TYPE, COMPONENT_HIERARCHY,COMPONENT_ITEM_HIERARCHY, PARENT_INVENTORY_ITEM_ID, ROOT_PHANTOM_ITEM_ID, ITM.ROUNDING_CONTROL_TYPE ROUNDING_DIRECTION,ITM.ITEM_CATALOG_GROUP_ID ITEM_CLASS_ID,DECODE(PAR.BOM_ITEM_TYPE,2,PARENT_INVENTORY_ITEM_ID,NULL) OPTION_CLASS_ITEM_ID, CONFIG_ITEM_ID,LINE_EXPL_ID,SUBSTITTUE_FOR_COMPONENT,PAR.ITEM_NUMBER PARENT_ITEM_NUMBER,BASE.ITEM_NUMBER BASE_CONFIG_ITEM_NUMBER,CONFIG_EFFECTIVITY_DATE,ONE_TIME_CONFIG_FLAG,CONFIG_ITEM_VERSION FROM ( SELECT LINE_ID, PARENT_LINE_ID,CONFIG_ITEM_ID BASE_CONFIG_ITEM_ID , DECODE(L.COMPONENT_SEQUENCE_ID, 0, L.BILL_SEQUENCE_ID, L.COMPONENT_SEQUENCE_ID) COMPONENT_SEQUENCE_ID, L.BILL_SEQUENCE_ID, DECODE(L.COMPONENT_SEQUENCE_ID,0, 1, -1) COMPONENT_TYPE, SUB_ITEM_TYPE, L.ORGANIZATION_ID, L.INVENTORY_ITEM_ID, COMPONENT_ITEM_REVISION_ID REVISION_ID, UNIT_QTY USAGE_QTY, L.UNIT_UOM UOM_CODE, DECODE(optional, 2, 'N', 'Y') as OPTIONAL_COMPONENT, SUBSTITUTE_COMPONENT, EFFECTIVITY_DATE, DISABLE_DATE, L.WIP_SUPPLY_TYPE, 100 PLANNING_FACTOR, CHECK_ATP, COMPONENT_YIELD_FACTOR, BASIS_TYPE, COMP_SEQ_PATH COMPONENT_HIERARCHY, INVENTORY_ITEM_PATH COMPONENT_ITEM_HIERARCHY, PARENT_INVENTORY_ITEM_ID, L.ROOT_PHANTOM_ITEM_ID, CONFIG_ITEM_ID,LINE_EXPL_ID,SUBSTITTUE_FOR_COMPONENT,CONFIG_EFFECTIVITY_DATE,ONE_TIME_CONFIG_FLAG,CONFIG_ITEM_VERSION FROM ( SELECT L.LINE_ID, PARENT_LINE_ID,CONFIG_ITEM_ID , L.COMPONENT_SEQUENCE_ID, L.BILL_SEQUENCE_ID, SUB_ITEM_TYPE, L.ORGANIZATION_ID, L.INVENTORY_ITEM_ID, COMPONENT_ITEM_REVISION_ID, UNIT_QTY, L.UNIT_UOM, OPTIONAL, 'N' SUBSTITUTE_COMPONENT, c.EFFECTIVITY_DATE, DISABLE_DATE, L.WIP_SUPPLY_TYPE, PLANNING_FACTOR, CHECK_ATP, COMPONENT_YIELD_FACTOR, BASIS_TYPE, L.ROOT_PHANTOM_ITEM_ID,COMP_SEQ_PATH,INVENTORY_ITEM_PATH,PARENT_INVENTORY_ITEM_ID,LINE_EXPL_ID,null SUBSTITTUE_FOR_COMPONENT,L.EFFECTIVITY_DATE CONFIG_EFFECTIVITY_DATE, L.ONE_TIME_CONFIG_FLAG,L.CONFIG_ITEM_VERSION FROM CTO_MATCH_LINES_EXPL L, EGP_COMPONENTS_B C WHERE L.COMPONENT_SEQUENCE_ID = C.COMPONENT_SEQUENCE_ID (+) AND (C.DISABLE_DATE > sysdate or C.DISABLE_DATE is null) UNION ALL SELECT LINE_ID, PARENT_LINE_ID,CONFIG_ITEM_ID, L.COMPONENT_SEQUENCE_ID, L.BILL_SEQUENCE_ID, SUB_ITEM_TYPE, L.ORGANIZATION_ID, S.SUBSTITUTE_COMPONENT_ID INVENTORY_ITEM_ID, COMPONENT_ITEM_REVISION_ID, S.SUBSTITUTE_ITEM_QUANTITY UNIT_QTY, L.UNIT_UOM, OPTIONAL, 'Y' SUBSTITUTE_COMPONENT,c.EFFECTIVITY_DATE, DISABLE_DATE, L.WIP_SUPPLY_TYPE, 100 PLANNING_FACTOR, CHECK_ATP, COMPONENT_YIELD_FACTOR, BASIS_TYPE, L.ROOT_PHANTOM_ITEM_ID,COMP_SEQ_PATH,substr(INVENTORY_ITEM_PATH,1,instr(inventory_item_path,'-',-1)-1) \|\| '-' \|\| S.SUBSTITUTE_COMPONENT_ID,PARENT_INVENTORY_ITEM_ID,LINE_EXPL_ID,L.INVENTORY_ITEM_ID SUBSTITTUE_FOR_COMPONENT,L.EFFECTIVITY_DATE CONFIG_EFFECTIVITY_DATE, L.ONE_TIME_CONFIG_FLAG,L.CONFIG_ITEM_VERSION FROM CTO_MATCH_LINES_EXPL L, EGP_COMPONENTS_B C, EGP_SUBSTITUTE_COMPONENTS S WHERE L.COMPONENT_SEQUENCE_ID = C.COMPONENT_SEQUENCE_ID AND C.COMPONENT_SEQUENCE_ID = S.COMPONENT_SEQUENCE_ID AND (C.DISABLE_DATE > sysdate or C.DISABLE_DATE is null) ) L ) V, EGP_SYSTEM_ITEMS_VL ITM, EGP_SYSTEM_ITEMS_VL PAR, EGP_SYSTEM_ITEMS_VL BASE WHERE ITM.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID AND ITM.ORGANIZATION_ID = V.ORGANIZATION_ID AND PAR.INVENTORY_ITEM_ID=V.PARENT_INVENTORY_ITEM_ID AND PAR.ORGANIZATION_ID=V.ORGANIZATION_ID AND BASE.INVENTORY_ITEM_ID=V.BASE_CONFIG_ITEM_ID AND BASE.ORGANIZATION_ID=V.ORGANIZATION_ID

SQL_Statement

SELECT ITM.ITEM_NUMBER, ITM.DESCRIPTION, LINE_ID, PARENT_LINE_ID,BASE_CONFIG_ITEM_ID, COMPONENT_SEQUENCE_ID, BILL_SEQUENCE_ID,

COMPONENT_TYPE, SUB_ITEM_TYPE, BASE.ORGANIZATION_ID, V.INVENTORY_ITEM_ID, REVISION_ID,

USAGE_QTY, UOM_CODE, OPTIONAL_COMPONENT,SUBSTITUTE_COMPONENT, EFFECTIVITY_DATE, DISABLE_DATE, V.WIP_SUPPLY_TYPE, PLANNING_FACTOR, CHECK_ATP, COMPONENT_YIELD_FACTOR, BASIS_TYPE,

COMPONENT_HIERARCHY,COMPONENT_ITEM_HIERARCHY, PARENT_INVENTORY_ITEM_ID, ROOT_PHANTOM_ITEM_ID, ITM.ROUNDING_CONTROL_TYPE ROUNDING_DIRECTION,ITM.ITEM_CATALOG_GROUP_ID ITEM_CLASS_ID,DECODE(PAR.BOM_ITEM_TYPE,2,PARENT_INVENTORY_ITEM_ID,NULL) OPTION_CLASS_ITEM_ID,

CONFIG_ITEM_ID,LINE_EXPL_ID,SUBSTITTUE_FOR_COMPONENT,PAR.ITEM_NUMBER PARENT_ITEM_NUMBER,BASE.ITEM_NUMBER BASE_CONFIG_ITEM_NUMBER,CONFIG_EFFECTIVITY_DATE,ONE_TIME_CONFIG_FLAG,CONFIG_ITEM_VERSION

FROM

(

SELECT LINE_ID, PARENT_LINE_ID,CONFIG_ITEM_ID BASE_CONFIG_ITEM_ID , DECODE(L.COMPONENT_SEQUENCE_ID, 0, L.BILL_SEQUENCE_ID, L.COMPONENT_SEQUENCE_ID) COMPONENT_SEQUENCE_ID, L.BILL_SEQUENCE_ID,

DECODE(L.COMPONENT_SEQUENCE_ID,0, 1, -1) COMPONENT_TYPE, SUB_ITEM_TYPE, L.ORGANIZATION_ID, L.INVENTORY_ITEM_ID, COMPONENT_ITEM_REVISION_ID REVISION_ID,

UNIT_QTY USAGE_QTY, L.UNIT_UOM UOM_CODE, DECODE(optional, 2, 'N', 'Y') as OPTIONAL_COMPONENT,

SUBSTITUTE_COMPONENT, EFFECTIVITY_DATE, DISABLE_DATE, L.WIP_SUPPLY_TYPE, 100 PLANNING_FACTOR, CHECK_ATP, COMPONENT_YIELD_FACTOR, BASIS_TYPE,

COMP_SEQ_PATH COMPONENT_HIERARCHY,

INVENTORY_ITEM_PATH COMPONENT_ITEM_HIERARCHY,

PARENT_INVENTORY_ITEM_ID, L.ROOT_PHANTOM_ITEM_ID, CONFIG_ITEM_ID,LINE_EXPL_ID,SUBSTITTUE_FOR_COMPONENT,CONFIG_EFFECTIVITY_DATE,ONE_TIME_CONFIG_FLAG,CONFIG_ITEM_VERSION

FROM

(

SELECT L.LINE_ID, PARENT_LINE_ID,CONFIG_ITEM_ID , L.COMPONENT_SEQUENCE_ID, L.BILL_SEQUENCE_ID, SUB_ITEM_TYPE, L.ORGANIZATION_ID, L.INVENTORY_ITEM_ID, COMPONENT_ITEM_REVISION_ID,

UNIT_QTY, L.UNIT_UOM, OPTIONAL, 'N' SUBSTITUTE_COMPONENT, c.EFFECTIVITY_DATE, DISABLE_DATE, L.WIP_SUPPLY_TYPE, PLANNING_FACTOR, CHECK_ATP, COMPONENT_YIELD_FACTOR, BASIS_TYPE,

L.ROOT_PHANTOM_ITEM_ID,COMP_SEQ_PATH,INVENTORY_ITEM_PATH,PARENT_INVENTORY_ITEM_ID,LINE_EXPL_ID,null SUBSTITTUE_FOR_COMPONENT,L.EFFECTIVITY_DATE CONFIG_EFFECTIVITY_DATE,

L.ONE_TIME_CONFIG_FLAG,L.CONFIG_ITEM_VERSION

FROM CTO_MATCH_LINES_EXPL L, EGP_COMPONENTS_B C

WHERE L.COMPONENT_SEQUENCE_ID = C.COMPONENT_SEQUENCE_ID (+)

AND (C.DISABLE_DATE > sysdate or C.DISABLE_DATE is null)

UNION ALL

SELECT LINE_ID, PARENT_LINE_ID,CONFIG_ITEM_ID, L.COMPONENT_SEQUENCE_ID, L.BILL_SEQUENCE_ID, SUB_ITEM_TYPE, L.ORGANIZATION_ID, S.SUBSTITUTE_COMPONENT_ID INVENTORY_ITEM_ID, COMPONENT_ITEM_REVISION_ID,

S.SUBSTITUTE_ITEM_QUANTITY UNIT_QTY, L.UNIT_UOM, OPTIONAL, 'Y' SUBSTITUTE_COMPONENT,c.EFFECTIVITY_DATE, DISABLE_DATE, L.WIP_SUPPLY_TYPE, 100 PLANNING_FACTOR, CHECK_ATP, COMPONENT_YIELD_FACTOR, BASIS_TYPE,

L.ROOT_PHANTOM_ITEM_ID,COMP_SEQ_PATH,substr(INVENTORY_ITEM_PATH,1,instr(inventory_item_path,'-',-1)-1) || '-' || S.SUBSTITUTE_COMPONENT_ID,PARENT_INVENTORY_ITEM_ID,LINE_EXPL_ID,L.INVENTORY_ITEM_ID SUBSTITTUE_FOR_COMPONENT,L.EFFECTIVITY_DATE CONFIG_EFFECTIVITY_DATE,

L.ONE_TIME_CONFIG_FLAG,L.CONFIG_ITEM_VERSION

FROM CTO_MATCH_LINES_EXPL L, EGP_COMPONENTS_B C, EGP_SUBSTITUTE_COMPONENTS S

WHERE L.COMPONENT_SEQUENCE_ID = C.COMPONENT_SEQUENCE_ID

AND C.COMPONENT_SEQUENCE_ID = S.COMPONENT_SEQUENCE_ID

AND (C.DISABLE_DATE > sysdate or C.DISABLE_DATE is null)

) L

) V,

EGP_SYSTEM_ITEMS_VL ITM,

EGP_SYSTEM_ITEMS_VL PAR,

EGP_SYSTEM_ITEMS_VL BASE

WHERE ITM.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID

AND ITM.ORGANIZATION_ID = V.ORGANIZATION_ID

AND PAR.INVENTORY_ITEM_ID=V.PARENT_INVENTORY_ITEM_ID

AND PAR.ORGANIZATION_ID=V.ORGANIZATION_ID

AND BASE.INVENTORY_ITEM_ID=V.BASE_CONFIG_ITEM_ID

AND BASE.ORGANIZATION_ID=V.ORGANIZATION_ID