MSC_AP_COMPONENT_SUBSTITUTES

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
BILL_SEQUENCE_ID ALTERNATE_BOM_DESIGNATOR USAGE_QUANTITY PRIORITY ORGANIZATION_ID ORGANIZATION_CODE SUBSTITUTE_ITEM_ID COMPONENT_SEQUENCE_ID SUB_ITEM_NAME INVENTORY_ITEM_NAME ASSEMBLY_NAME EFFECTIVITY_DATE OPERATION_SEQ_NUM ROUNDING_DIRECTION ACD_TYPE BSCROW_ID

Name

BILL_SEQUENCE_ID

ALTERNATE_BOM_DESIGNATOR

USAGE_QUANTITY

PRIORITY

ORGANIZATION_ID

ORGANIZATION_CODE

SUBSTITUTE_ITEM_ID

COMPONENT_SEQUENCE_ID

SUB_ITEM_NAME

INVENTORY_ITEM_NAME

ASSEMBLY_NAME

EFFECTIVITY_DATE

OPERATION_SEQ_NUM

ROUNDING_DIRECTION

ACD_TYPE

BSCROW_ID

Query

SQL_Statement
SELECT bom.bill_sequence_id, bom.alternate_bom_designator, bsc.substitute_item_quantity usage_quantity, bsc.planning_priority priority, bom.pk2_value organization_id, item.organization_code, bsc.substitute_component_id substitute_item_id, bsc.component_sequence_id, sub_item.item_number sub_item_name, comp.item_number inventory_item_name, item.item_number assembly_name, bc.effectivity_date, bc.operation_seq_num , bsc.enforce_int_requirements rounding_direction, bsc.acd_type, bsc.rowid bscrow_id FROM egp_substitute_components bsc, egp_components_b bc, egp_structures_b bom, MSC_PLANNED_EGP_SYSTEM_ITEMS item, MSC_PLANNED_EGP_SYSTEM_ITEMS sub_item, MSC_PLANNED_EGP_SYSTEM_ITEMS comp WHERE bsc.component_sequence_id = bc.component_sequence_id AND (bc.disable_date IS NULL OR bc.disable_date >= TRUNC(SYSDATE)) AND bom.common_bill_sequence_id = bc.bill_sequence_id AND TO_NUMBER(bom.pk2_value) = item.organization_id AND bom.obj_name = 'EGO_ITEM' AND bom.structure_type_id = 1 AND NVL(bom.effectivity_control,-1) <> 4 AND TO_NUMBER(bom.pk1_value) = item.inventory_item_id AND bc.implementation_date IS NOT NULL AND sub_item.inventory_item_id = bsc.substitute_component_id AND sub_item.organization_id = TO_NUMBER(bc.pk2_value) AND comp.inventory_item_id = TO_NUMBER(bc.pk1_value) AND comp.organization_id = TO_NUMBER(bc.pk2_value)

SQL_Statement

SELECT

bom.bill_sequence_id,

bom.alternate_bom_designator,

bsc.substitute_item_quantity usage_quantity,

bsc.planning_priority priority,

bom.pk2_value organization_id,

item.organization_code,

bsc.substitute_component_id substitute_item_id,

bsc.component_sequence_id,

sub_item.item_number sub_item_name,

comp.item_number inventory_item_name,

item.item_number assembly_name,

bc.effectivity_date,

bc.operation_seq_num ,

bsc.enforce_int_requirements rounding_direction,

bsc.acd_type,

bsc.rowid bscrow_id

FROM egp_substitute_components bsc,

egp_components_b bc,

egp_structures_b bom,

MSC_PLANNED_EGP_SYSTEM_ITEMS item,

MSC_PLANNED_EGP_SYSTEM_ITEMS sub_item,

MSC_PLANNED_EGP_SYSTEM_ITEMS comp

WHERE bsc.component_sequence_id = bc.component_sequence_id

AND (bc.disable_date IS NULL OR bc.disable_date >= TRUNC(SYSDATE))

AND bom.common_bill_sequence_id = bc.bill_sequence_id

AND TO_NUMBER(bom.pk2_value) = item.organization_id

AND bom.obj_name = 'EGO_ITEM'

AND bom.structure_type_id = 1

AND NVL(bom.effectivity_control,-1) <> 4

AND TO_NUMBER(bom.pk1_value) = item.inventory_item_id

AND bc.implementation_date IS NOT NULL

AND sub_item.inventory_item_id = bsc.substitute_component_id

AND sub_item.organization_id = TO_NUMBER(bc.pk2_value)

AND comp.inventory_item_id = TO_NUMBER(bc.pk1_value)

AND comp.organization_id = TO_NUMBER(bc.pk2_value)