MSC_AP_WD_OPERATIONS

詳細

スキーマ: FUSION
オブジェクト所有者: MSC
オブジェクト・タイプ: VIEW

列

名前
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

名前

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

問合せ

SQL_Statement
/Work定義はバージョン管理できます。ただし、工程有効性情報は工程レベルで保守され、作業定義バージョン・レベルの有効日とは無関係です。計画では、作業定義バージョンおよび作業定義ヘッダー・レベルでの有効日は無視できます(次に示すように、そのルールに1つの例外があります)。計画では、工程レベルの有効日のみを考慮する必要があります。 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, null 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定義はバージョン管理できます。ただし、工程有効性情報は工程レベルで保守され、作業定義バージョン・レベルの有効日とは無関係です。計画では、作業定義バージョンおよび作業定義ヘッダー・レベルでの有効日は無視できます(次に示すように、そのルールに1つの例外があります)。計画では、工程レベルの有効日のみを考慮する必要があります。

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,

null 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