MSC_AP_WD_OP_MAT_CONFIG

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
COMPONENT_SEQUENCE_ID WD_OPERATION_MATERIAL_ID WORK_DEFINITION_ID WD_OPERATION_ID CONFIG_ITEM_ID ITEM_STRUCT_COMP_REF_FLAG COMPONENT_SEQUENCE_ID_PATH MATERIAL_TYPE ORGANIZATION_ID ORGANIZATION_CODE ASSEMBLY_NAME COMPONENT_NAME WORK_DEFINITION_CODE OPERATION_SEQ_NUMBER MATERIAL_QUANTITY YIELD_FACTOR EFFECTIVE_FROM_DATE EFFECTIVE_TO_DATE OPTIONAL SUPPLY_TYPE CHECK_ATP ROUNDING_DIRECTION MATERIAL_SEQ_NUMBER COMPONENT_TYPE UOM_CODE BASIS_TYPE OP_EFFECTIVE_FROM_DATE

Name

COMPONENT_SEQUENCE_ID

WD_OPERATION_MATERIAL_ID

WORK_DEFINITION_ID

WD_OPERATION_ID

CONFIG_ITEM_ID

ITEM_STRUCT_COMP_REF_FLAG

COMPONENT_SEQUENCE_ID_PATH

MATERIAL_TYPE

ORGANIZATION_ID

ORGANIZATION_CODE

ASSEMBLY_NAME

COMPONENT_NAME

WORK_DEFINITION_CODE

OPERATION_SEQ_NUMBER

MATERIAL_QUANTITY

YIELD_FACTOR

EFFECTIVE_FROM_DATE

EFFECTIVE_TO_DATE

OPTIONAL

SUPPLY_TYPE

CHECK_ATP

ROUNDING_DIRECTION

MATERIAL_SEQ_NUMBER

COMPONENT_TYPE

UOM_CODE

BASIS_TYPE

OP_EFFECTIVE_FROM_DATE

Query

SQL_Statement
SELECT /+ leading (wd wob wom) / wom.component_sequence_id, wom .wd_operation_material_id, wd.work_definition_id, wom.wd_operation_id, wom.config_item_id, DECODE(wom.item_struct_comp_ref_flag,'Y',1,'N',2,2) item_struct_comp_ref_flag, component_sequence_id_path, wom.material_type, wd.organization_id, assy_esi.organization_code , assy_esi.item_number assembly_name, comp_esi.item_number component_name, wd.WORK_DEFINITION_CODE, wom.operation_seq_number, wom.material_quantity, NVL(wom.yield_factor,1) yield_factor, GREATEST(wom.effective_from_date, wob.effective_from_date) effective_from_date, (CASE WHEN (wom.effective_to_date IS NULL AND wob.effective_to_date IS NULL) THEN NULL ELSE LEAST(NVL(wom.effective_to_date, TO_DATE(3457147, 'J')), NVL(wob.effective_to_date, TO_DATE(3457147, 'J'))) END) effective_to_date, DECODE(wom.optional,'Y',1,'N',2,2) optional, wom.supply_type, DECODE(wom.check_atp,'Y',1,'N',2,2) check_atp, DECODE(NVL(wom.rounding_direction,3),0,3,1,2,2,1,3) rounding_direction, wom.material_seq_number, 1 COMPONENT_TYPE, wom.uom_code, wom.basis_type, wob.effective_from_date op_effective_from_date FROM msc_api_wd_operation wob, msc_api_wd_op_materials wom, msc_api_work_def_header wd, msc_planned_egp_system_items comp_esi, msc_planned_egp_system_items assy_esi WHERE wd.work_definition_id = wob.work_definition_id AND wob.wd_operation_id = wom.wd_operation_id AND wd.organization_id = comp_esi.organization_id AND wom.inventory_item_id = comp_esi.inventory_item_id AND wom.config_item_id = assy_esi.inventory_item_id AND wd.organization_id = assy_esi.organization_id AND wom.config_item_id = wob.config_item_id AND wob.config_item_id = wd.config_item_id AND wob.organization_id = wom.organization_id AND wob.organization_id = wd.organization_id

SQL_Statement

SELECT /*+ leading (wd wob wom) */

wom.component_sequence_id,

wom .wd_operation_material_id,

wd.work_definition_id,

wom.wd_operation_id,

wom.config_item_id,

DECODE(wom.item_struct_comp_ref_flag,'Y',1,'N',2,2) item_struct_comp_ref_flag,

component_sequence_id_path,

wom.material_type,

wd.organization_id,

assy_esi.organization_code ,

assy_esi.item_number assembly_name,

comp_esi.item_number component_name,

wd.WORK_DEFINITION_CODE,

wom.operation_seq_number,

wom.material_quantity,

NVL(wom.yield_factor,1) yield_factor,

GREATEST(wom.effective_from_date, wob.effective_from_date) effective_from_date,

(CASE

WHEN (wom.effective_to_date IS NULL AND wob.effective_to_date IS NULL)

THEN NULL

ELSE

LEAST(NVL(wom.effective_to_date, TO_DATE(3457147, 'J')), NVL(wob.effective_to_date, TO_DATE(3457147, 'J')))

END) effective_to_date,

DECODE(wom.optional,'Y',1,'N',2,2) optional,

wom.supply_type,

DECODE(wom.check_atp,'Y',1,'N',2,2) check_atp,

DECODE(NVL(wom.rounding_direction,3),0,3,1,2,2,1,3) rounding_direction,

wom.material_seq_number,

1 COMPONENT_TYPE,

wom.uom_code,

wom.basis_type,

wob.effective_from_date op_effective_from_date

FROM

msc_api_wd_operation wob,

msc_api_wd_op_materials wom,

msc_api_work_def_header wd,

msc_planned_egp_system_items comp_esi,

msc_planned_egp_system_items assy_esi

WHERE wd.work_definition_id = wob.work_definition_id

AND wob.wd_operation_id = wom.wd_operation_id

AND wd.organization_id = comp_esi.organization_id

AND wom.inventory_item_id = comp_esi.inventory_item_id

AND wom.config_item_id = assy_esi.inventory_item_id

AND wd.organization_id = assy_esi.organization_id

AND wom.config_item_id = wob.config_item_id

AND wob.config_item_id = wd.config_item_id

AND wob.organization_id = wom.organization_id

AND wob.organization_id = wd.organization_id