MSC_AP_WD_COMP_SUBSTITUTES

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
SUB_MATERIAL_ID ORGANIZATION_CODE ORGANIZATION_ID SUBSTITUTE_ITEM_QUANTITY PRIORITY_RANKING SUB_ITEM_NAME ASSEMBLY_NAME EFFECTIVITY_DATE WORK_DEFINITION_CODE OPERATION_SEQ_NUMBER FIND_NUMBER COMPONENT_NAME ROUNDING_DIRECTION SUB_ROWID WD_OPERATION_ID WORK_DEFINITION_ID

Name

SUB_MATERIAL_ID

ORGANIZATION_CODE

ORGANIZATION_ID

SUBSTITUTE_ITEM_QUANTITY

PRIORITY_RANKING

SUB_ITEM_NAME

ASSEMBLY_NAME

EFFECTIVITY_DATE

WORK_DEFINITION_CODE

OPERATION_SEQ_NUMBER

FIND_NUMBER

COMPONENT_NAME

ROUNDING_DIRECTION

SUB_ROWID

WD_OPERATION_ID

WORK_DEFINITION_ID

Query

SQL_Statement
WITH first_version AS ( SELECT wv.work_definition_id, MIN(wv.effective_from_date) effective_from_date FROM wis_wd_versions wv WHERE wv.approval_status_code = 'APPROVED' GROUP BY wv.work_definition_id ) SELECT subs.sub_material_id, esi.organization_code, wd.organization_id, subs.substitute_item_quantity, subs.priority_ranking, sub_item.item_number sub_item_name, esi.item_number assembly_name, greatest( nvl(subs.EFFECTIVITY_DATE ,greatest(wob.effective_from_date, first_version.effective_from_date)),greatest(wob.effective_from_date, first_version.effective_from_date)) EFFECTIVITY_DATE, wd.work_definition_code, wob.operation_seq_number, subs.find_number, comp_item.item_number component_name, subs.enforce_int_requirements rounding_direction, subs.row_id sub_rowid, wob.wd_operation_id, wd.work_definition_id FROM WIS_WD_OP_SUB_MATERIALS_V SUBS, wis_work_definitions_v wd, wis_wd_operations_v wob, msc_planned_egp_system_items esi, first_version , msc_planned_egp_system_items sub_item, msc_planned_egp_system_items comp_item WHERE wob.wd_operation_id = subs.wd_operation_id AND wd.work_definition_id = wob.work_definition_id AND wd.work_definition_id = first_version.work_definition_id AND wd.organization_id = esi.organization_id AND wd.inventory_item_id = esi.inventory_item_id AND wd.used_in_planning_flag = 'Y' AND wd.status_code IN ('ACTIVE','PENDING_INACTIVE') AND wd.work_method_code NOT IN ('MAINTENANCE') and sub_item.inventory_item_id= subs.sub_component_id AND wd.organization_id = sub_item.organization_id and comp_item.inventory_item_id= subs.inventory_item_id AND wd.organization_id = comp_item.organization_id

SQL_Statement

WITH first_version AS (

SELECT wv.work_definition_id, MIN(wv.effective_from_date) effective_from_date

FROM wis_wd_versions wv

WHERE wv.approval_status_code = 'APPROVED'

GROUP BY wv.work_definition_id

)

SELECT

subs.sub_material_id,

esi.organization_code,

wd.organization_id,

subs.substitute_item_quantity,

subs.priority_ranking,

sub_item.item_number sub_item_name,

esi.item_number assembly_name,

greatest( nvl(subs.EFFECTIVITY_DATE ,greatest(wob.effective_from_date, first_version.effective_from_date)),greatest(wob.effective_from_date, first_version.effective_from_date)) EFFECTIVITY_DATE,

wd.work_definition_code,

wob.operation_seq_number,

subs.find_number,

comp_item.item_number component_name,

subs.enforce_int_requirements rounding_direction,

subs.row_id sub_rowid,

wob.wd_operation_id,

wd.work_definition_id

FROM WIS_WD_OP_SUB_MATERIALS_V SUBS,

wis_work_definitions_v wd,

wis_wd_operations_v wob,

msc_planned_egp_system_items esi,

first_version ,

msc_planned_egp_system_items sub_item,

msc_planned_egp_system_items comp_item

WHERE wob.wd_operation_id = subs.wd_operation_id

AND wd.work_definition_id = wob.work_definition_id

AND wd.work_definition_id = first_version.work_definition_id

AND wd.organization_id = esi.organization_id

AND wd.inventory_item_id = esi.inventory_item_id

AND wd.used_in_planning_flag = 'Y'

AND wd.status_code IN ('ACTIVE','PENDING_INACTIVE')

AND wd.work_method_code NOT IN ('MAINTENANCE')

and sub_item.inventory_item_id= subs.sub_component_id

AND wd.organization_id = sub_item.organization_id

and comp_item.inventory_item_id= subs.inventory_item_id

AND wd.organization_id = comp_item.organization_id