MSC_AP_WORK_DEFINITIONS

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
ORGANIZATION_CODE ASSEMBLY_NAME ASSEMBLY_ITEM_ID ORGANIZATION_ID SR_WD_ID WORK_DEFINITION_TYPE WORK_DEF_NAME WORK_DEF_NAME_DESCRIPTION PROJECT_ID TASK_ID LINE_ID COMMON_ROUTING_SEQUENCE_ID PRODUCTION_PRIORITY CFM_ROUTING_FLAG SR_COMPLETION_LOCATOR_ID COMPLETION_SUBINVENTORY_CODE CTP_FLAG TOTAL_PRODUCT_CYCLE_TIME MIXED_MODEL_MAP_FLAG WORK_DEFINITION_CODE SR_BILL_SEQUECE_ID SR_ALTERNATE_BOM_DESIGNATOR EFFECTIVE_FROM_DATE EFFECTIVE_TO_DATE WD_ROWID WDB_ROWID WDTL_ROWID WORK_DEFINITION_HEADER_NAME TOTAL_OUTPUT_QUANTITY MINIMUM_TRANSFER_QUANTITY UOM_CODE WORK_METHOD_CODE PRIMARY_FLAG PRODUCTION_LINE_ID

Name

ORGANIZATION_CODE

ASSEMBLY_NAME

ASSEMBLY_ITEM_ID

ORGANIZATION_ID

SR_WD_ID

WORK_DEFINITION_TYPE

WORK_DEF_NAME

WORK_DEF_NAME_DESCRIPTION

PROJECT_ID

TASK_ID

LINE_ID

COMMON_ROUTING_SEQUENCE_ID

PRODUCTION_PRIORITY

CFM_ROUTING_FLAG

SR_COMPLETION_LOCATOR_ID

COMPLETION_SUBINVENTORY_CODE

CTP_FLAG

TOTAL_PRODUCT_CYCLE_TIME

MIXED_MODEL_MAP_FLAG

WORK_DEFINITION_CODE

SR_BILL_SEQUECE_ID

SR_ALTERNATE_BOM_DESIGNATOR

EFFECTIVE_FROM_DATE

EFFECTIVE_TO_DATE

WD_ROWID

WDB_ROWID

WDTL_ROWID

WORK_DEFINITION_HEADER_NAME

TOTAL_OUTPUT_QUANTITY

MINIMUM_TRANSFER_QUANTITY

UOM_CODE

WORK_METHOD_CODE

PRIMARY_FLAG

PRODUCTION_LINE_ID

Query

SQL_Statement
with wd_eff as ( select min(wv2.effective_from_date) effective_from_date, max(nvl(wv2.effective_to_date, trunc(sysdate) + 1000000)) effective_to_date, wv2.work_definition_id from WIS_WD_VERSIONS_V wv2 where nvl(wv2.effective_to_date, sysdate + 1) >= trunc(sysdate) group by wv2.work_definition_id ) SELECT esi.organization_code organization_code, esi.item_number assembly_name, wd.inventory_item_id assembly_item_id, wd.organization_id , wd.work_definition_id sr_wd_id, DECODE(wd.work_definition_type, 'STANDARD', 1, 2) work_definition_type, wd.work_def_name work_def_name, wd.work_def_name_description work_def_name_description, null project_id, null task_id, null line_id, null common_routing_sequence_id , wd.production_priority production_priority, null cfm_routing_flag , null sr_completion_locator_id, null completion_subinventory_code, null ctp_flag, null total_product_cycle_time, null mixed_model_map_flag, wd.work_definition_code work_definition_code, wd.bill_sequence_id sr_bill_sequece_id , esb.alternate_bom_designator sr_alternate_bom_designator, wd_eff.effective_from_date effective_from_date, DECODE(wd_eff.effective_to_date, TRUNC(SYSDATE) + 1000000, to_timestamp(null), wd_eff.effective_to_date) effective_to_date, wd.row_id wd_rowid, wd.names_b_row_id wdb_rowid, wd.names_tl_row_id wdtl_rowid, wd.work_definition_header_name, wd.quantity total_output_quantity, wd.minimum_transfer_quantity minimum_transfer_quantity, wd.uom_code, DECODE(wd.work_method_code, 'PROCESS_MANUFACTURING', 2, 'DISCRETE_MANUFACTURING', 1, 4) work_method_code, DECODE(wd.production_priority, 1, 1 ,2) primary_flag, wd.production_line_id FROM wis_work_definitions_v wd, msc_planned_egp_system_items esi, egp_structures_b esb, wd_eff WHERE wd.work_definition_id = wd_eff.work_definition_id AND wd.organization_id = esi.organization_id AND wd.inventory_item_id = esi.inventory_item_id AND wd.bill_sequence_id = esb.bill_sequence_id(+) AND wd.used_in_planning_flag = 'Y' AND wd.work_method_code IN ('DISCRETE_MANUFACTURING','PROCESS_MANUFACTURING','FLOW_MANUFACTURING') AND wd.status_code IN ('ACTIVE', 'PENDING_INACTIVE') ORDER BY wd.work_definition_id

SQL_Statement

with

wd_eff as (

select

min(wv2.effective_from_date) effective_from_date,

max(nvl(wv2.effective_to_date, trunc(sysdate) + 1000000)) effective_to_date,

wv2.work_definition_id

from WIS_WD_VERSIONS_V wv2

where nvl(wv2.effective_to_date, sysdate + 1) >= trunc(sysdate)

group by wv2.work_definition_id

)

SELECT

esi.organization_code organization_code,

esi.item_number assembly_name,

wd.inventory_item_id assembly_item_id,

wd.organization_id ,

wd.work_definition_id sr_wd_id,

DECODE(wd.work_definition_type, 'STANDARD', 1, 2) work_definition_type,

wd.work_def_name work_def_name,

wd.work_def_name_description work_def_name_description,

null project_id,

null task_id,

null line_id,

null common_routing_sequence_id ,

wd.production_priority production_priority,

null cfm_routing_flag ,

null sr_completion_locator_id,

null completion_subinventory_code,

null ctp_flag,

null total_product_cycle_time,

null mixed_model_map_flag,

wd.work_definition_code work_definition_code,

wd.bill_sequence_id sr_bill_sequece_id ,

esb.alternate_bom_designator sr_alternate_bom_designator,

wd_eff.effective_from_date effective_from_date,

DECODE(wd_eff.effective_to_date, TRUNC(SYSDATE) + 1000000, to_timestamp(null), wd_eff.effective_to_date) effective_to_date,

wd.row_id wd_rowid,

wd.names_b_row_id wdb_rowid,

wd.names_tl_row_id wdtl_rowid,

wd.work_definition_header_name,

wd.quantity total_output_quantity,

wd.minimum_transfer_quantity minimum_transfer_quantity,

wd.uom_code,

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

DECODE(wd.production_priority, 1, 1 ,2) primary_flag,

wd.production_line_id

FROM wis_work_definitions_v wd,

msc_planned_egp_system_items esi,

egp_structures_b esb,

wd_eff

WHERE wd.work_definition_id = wd_eff.work_definition_id

AND wd.organization_id = esi.organization_id

AND wd.inventory_item_id = esi.inventory_item_id

AND wd.bill_sequence_id = esb.bill_sequence_id(+)

AND wd.used_in_planning_flag = 'Y'

AND wd.work_method_code IN ('DISCRETE_MANUFACTURING','PROCESS_MANUFACTURING','FLOW_MANUFACTURING')

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

ORDER BY wd.work_definition_id