MSC_AP_WD_OPERATIONS

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
ORGANIZATION_CODE ORGANIZATION_ID ASSEMBLY_NAME ASSEMBLY_ITEM_ID BILL_SEQUENCE_ID WORK_DEFINITION_CODE OPERATION_SEQ_NUMBER EFFECTIVITY_DATE WD_OPERATION_ID WORK_DEFINITION_ID OPERATION_DESCRIPTION DISABLE_DATE OPTION_DEPENDENT_FLAG OPERATION_TYPE MINIMUM_TRANSFER_QUANTITY SR_WORK_CENTER_ID OPERATION_LEAD_TIME_PERCENT CUMULATIVE_YIELD REVERSE_CUMULATIVE_YIELD NET_PLANNING_PERCENT OSP_ITEM_ID OSP_OPERATION_TYPE SUPPLIER_ID SUPPLIER_SITE_ID SUPPLIER_NAME SUPPLIER_SITE_CODE LEAD_TIME_UOM FIXED_LEAD_TIME VARIABLE_LEAD_TIME WORK_CENTER_CODE YIELD REFERENCED_FLAG WORK_METHOD_CODE PRIMARY_UOM_CODE

Name

ORGANIZATION_CODE

ORGANIZATION_ID

ASSEMBLY_NAME

ASSEMBLY_ITEM_ID

BILL_SEQUENCE_ID

WORK_DEFINITION_CODE

OPERATION_SEQ_NUMBER

EFFECTIVITY_DATE

WD_OPERATION_ID

WORK_DEFINITION_ID

OPERATION_DESCRIPTION

DISABLE_DATE

OPTION_DEPENDENT_FLAG

OPERATION_TYPE

MINIMUM_TRANSFER_QUANTITY

SR_WORK_CENTER_ID

OPERATION_LEAD_TIME_PERCENT

CUMULATIVE_YIELD

REVERSE_CUMULATIVE_YIELD

NET_PLANNING_PERCENT

OSP_ITEM_ID

OSP_OPERATION_TYPE

SUPPLIER_ID

SUPPLIER_SITE_ID

SUPPLIER_NAME

SUPPLIER_SITE_CODE

LEAD_TIME_UOM

FIXED_LEAD_TIME

VARIABLE_LEAD_TIME

WORK_CENTER_CODE

YIELD

REFERENCED_FLAG

WORK_METHOD_CODE

PRIMARY_UOM_CODE

Query

SQL_Statement
/Work Definitions can be versioned. However the operation effectivity information is maintained at the operation level and is independent of the work definition version level effectivity dates. Planning can ignore the Work Definition versions and the effectivity dates at the work definition header level (with one exception to that rule as noted below). Planning will just need to consider the operation level effectivity dates. The only cases where the work definition header level effectivity dates should be considered is to make sure that any operation effective start date is not earlier than the start date of the first version of the work definition and any operation effective end date is not later than the disable date of the last version of the work definition. Operation Effective Start Date = max [operation effective start date as specified at the operation level, start date of the first version of the work definition] Operation Effective End Date = min [operation effective end date as specified at the operation level, disable date of the last version of the work definition] / with first_version as ( select wv.work_definition_id, min(wv.effective_from_date) effective_from_date from WIS_WD_VERSIONS_V wv group by wv.work_definition_id ), last_version as ( select wv.work_definition_id, MAX(NVL(WV.EFFECTIVE_TO_DATE, TO_DATE(3457147, 'J'))) effective_to_date from WIS_WD_VERSIONS_V wv group by wv.work_definition_id ) SELECT /+ leading(wob) / esi.organization_code, esi.organization_id, esi.item_number assembly_name, esi.inventory_item_id assembly_item_id, wd.bill_sequence_id, wd.work_definition_code, wob.operation_seq_number, GREATEST(wob.effective_from_date, first_version.effective_from_date) effectivity_date, wob.wd_operation_id, wd.work_definition_id, wob.operation_description, NULLIF(LEAST(wob.effective_to_date, last_version.effective_to_date), TO_DATE(3457147, 'J')) disable_date , DECODE(wob.option_dependent_flag,'Y',1, 'N', 2, 2) option_dependent_flag, 1 operation_type, DECODE(wob.operation_type, 'SUPPLIER', null, wd.minimum_transfer_quantity) minimum_transfer_quantity, wob.work_center_id sr_work_center_id, null operation_lead_time_percent, null cumulative_yield, null reverse_cumulative_yield, wob.planning_percent net_planning_percent, wob.osp_item_id osp_item_id, DECODE(wob.operation_type, 'SUPPLIER',2,1) osp_operation_type, wob.supplier_id supplier_id, wob.supplier_site_id supplier_site_id, supHZP.partner_name supplier_name, pvsa.vendor_site_code supplier_site_code, wob.lead_time_uom lead_time_uom, wob.fixed_lead_time fixed_lead_time, wob.variable_lead_time variable_lead_time, wob.work_center_code, 1 yield, wob.referenced_flag, DECODE(wd.work_method_code,'PROCESS_MANUFACTURING', 2, 1) work_method_code, esi.uom_code primary_uom_code FROM wis_work_definitions_v wd, wis_wd_operations_v wob, msc_planned_egp_system_items esi, msc_ap_suppliers_v suphzp, poz_supplier_sites_all_m pvsa, first_version , last_version WHERE wd.work_definition_id = wob.work_definition_id AND wd.work_definition_id = first_version.work_definition_id AND wd.work_definition_id = last_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 supHZP.sr_tp_id(+) = wob.supplier_id AND pvsa.vendor_site_id(+) = wob.supplier_site_id

SQL_Statement

/*Work Definitions can be versioned. However the operation effectivity information is maintained at the operation level and is independent of the work definition version level effectivity dates. Planning can ignore the Work Definition versions and the effectivity dates at the work definition header level (with one exception to that rule as noted below). Planning will just need to consider the operation level effectivity dates.

The only cases where the work definition header level effectivity dates should be considered is to make sure that any operation effective start date is not earlier than the start date of the first version of the work definition and any operation effective end date is not later than the disable date of the last version of the work definition.

Operation Effective Start Date = max [operation effective start date as specified at the operation level, start date of the first version of the work definition]

Operation Effective End Date = min [operation effective end date as specified at the operation level, disable date of the last version of the work definition]

*/

with first_version as (

select

wv.work_definition_id, min(wv.effective_from_date) effective_from_date

from WIS_WD_VERSIONS_V wv

group by wv.work_definition_id

),

last_version as (

select

wv.work_definition_id,

MAX(NVL(WV.EFFECTIVE_TO_DATE, TO_DATE(3457147, 'J'))) effective_to_date

from WIS_WD_VERSIONS_V wv

group by wv.work_definition_id

)

SELECT /*+ leading(wob) */

esi.organization_code,

esi.organization_id,

esi.item_number assembly_name,

esi.inventory_item_id assembly_item_id,

wd.bill_sequence_id,

wd.work_definition_code,

wob.operation_seq_number,

GREATEST(wob.effective_from_date, first_version.effective_from_date) effectivity_date,

wob.wd_operation_id,

wd.work_definition_id,

wob.operation_description,

NULLIF(LEAST(wob.effective_to_date, last_version.effective_to_date), TO_DATE(3457147, 'J')) disable_date ,

DECODE(wob.option_dependent_flag,'Y',1, 'N', 2, 2) option_dependent_flag,

1 operation_type,

DECODE(wob.operation_type, 'SUPPLIER', null, wd.minimum_transfer_quantity) minimum_transfer_quantity,

wob.work_center_id sr_work_center_id,

null operation_lead_time_percent,

null cumulative_yield,

null reverse_cumulative_yield,

wob.planning_percent net_planning_percent,

wob.osp_item_id osp_item_id,

DECODE(wob.operation_type, 'SUPPLIER',2,1) osp_operation_type,

wob.supplier_id supplier_id,

wob.supplier_site_id supplier_site_id,

supHZP.partner_name supplier_name,

pvsa.vendor_site_code supplier_site_code,

wob.lead_time_uom lead_time_uom,

wob.fixed_lead_time fixed_lead_time,

wob.variable_lead_time variable_lead_time,

wob.work_center_code,

1 yield,

wob.referenced_flag,

DECODE(wd.work_method_code,'PROCESS_MANUFACTURING', 2, 1) work_method_code,

esi.uom_code primary_uom_code

FROM

wis_work_definitions_v wd,

wis_wd_operations_v wob,

msc_planned_egp_system_items esi,

msc_ap_suppliers_v suphzp,

poz_supplier_sites_all_m pvsa,

first_version ,

last_version

WHERE wd.work_definition_id = wob.work_definition_id

AND wd.work_definition_id = first_version.work_definition_id

AND wd.work_definition_id = last_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 supHZP.sr_tp_id(+) = wob.supplier_id

AND pvsa.vendor_site_id(+) = wob.supplier_site_id