MSC_AP_WO_OP_DEPENDENCIES

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
ASSEMBLY_NAME ORGANIZATION_CODE WIP_ENTITY_NAME PREV_OP_SEQ_NUM NEXT_OP_SEQ_NUM DEPENDENCY_TYPE TRANSFER_PERCENT ORDER_TYPE WORK_ORDER_ID ASSEMBLY_ITEM_ID ORGANIZATION_ID WO_OP_DEPENDENCY_ID

Name

ASSEMBLY_NAME

ORGANIZATION_CODE

WIP_ENTITY_NAME

PREV_OP_SEQ_NUM

NEXT_OP_SEQ_NUM

DEPENDENCY_TYPE

TRANSFER_PERCENT

ORDER_TYPE

WORK_ORDER_ID

ASSEMBLY_ITEM_ID

ORGANIZATION_ID

WO_OP_DEPENDENCY_ID

Query

SQL_Statement
WITH job_status as ( SELECT 'UNRELEASED' valid_status FROM msc_apps_instances WHERE rownum = 1 UNION ALL SELECT 'RELEASED' valid_status FROM msc_apps_instances WHERE rownum = 1 UNION ALL SELECT 'ON_HOLD' valid_status FROM msc_apps_instances WHERE rownum = 1 UNION ALL SELECT 'PENDING_APPROVAL' valid_status FROM msc_apps_instances WHERE rownum = 1 UNION ALL SELECT 'COMPLETED' valid_status FROM msc_apps_instances WHERE rownum = 1 AND NVL(fnd_profile.value('MSC_COLLECT_COMPLETED_JOBS'), '1') = '1' ) SELECT esi.item_number assembly_name, esi.organization_code organization_code, wob.work_order_number wip_entity_name, wop.operation_seq_number prev_op_seq_num, wop1.operation_seq_number next_op_seq_num, DECODE(wod.dependency_type,'ORA_FINISH_TO_START',1,'ORA_START_TO_START',2,NULL) dependency_type, wod.transfer_percent, DECODE(wob.work_order_type, 'STANDARD', 3, 'NON_STANDARD', 7,'CORRECTIVE',70,'PREVENTIVE',70,'FIELDSERVICE',70,7) order_type, wob.work_order_id, esi.inventory_item_id assembly_item_id, esi.organization_id, wod.wo_op_dependency_id FROM WIE_WORK_ORDERS_V wob, WIE_WO_OPERATIONS_B wop, WIE_WO_OPERATIONS_B wop1, WIE_WO_OP_DEPENDENCIES wod, MSC_PLANNED_EGP_SYSTEM_ITEMS_V esi WHERE wob.work_order_id = wod.work_order_id AND wop.wo_operation_id(+) = wod.from_wo_operation_id AND wop1.wo_operation_id(+) = wod.to_wo_operation_id AND wob.inventory_item_id = esi.inventory_item_id AND wob.organization_id = esi.organization_id AND wob.system_status_code in (SELECT valid_status FROM job_status) AND wob.work_method_code in ('DISCRETE_MANUFACTURING','PROCESS_MANUFACTURING')

SQL_Statement

WITH job_status as (

SELECT 'UNRELEASED' valid_status FROM msc_apps_instances WHERE rownum = 1

UNION ALL

SELECT 'RELEASED' valid_status FROM msc_apps_instances WHERE rownum = 1

UNION ALL

SELECT 'ON_HOLD' valid_status FROM msc_apps_instances WHERE rownum = 1

UNION ALL

SELECT 'PENDING_APPROVAL' valid_status FROM msc_apps_instances WHERE rownum = 1

UNION ALL

SELECT 'COMPLETED' valid_status FROM msc_apps_instances

WHERE rownum = 1

AND NVL(fnd_profile.value('MSC_COLLECT_COMPLETED_JOBS'), '1') = '1'

)

SELECT

esi.item_number assembly_name,

esi.organization_code organization_code,

wob.work_order_number wip_entity_name,

wop.operation_seq_number prev_op_seq_num,

wop1.operation_seq_number next_op_seq_num,

DECODE(wod.dependency_type,'ORA_FINISH_TO_START',1,'ORA_START_TO_START',2,NULL) dependency_type,

wod.transfer_percent,

DECODE(wob.work_order_type, 'STANDARD', 3, 'NON_STANDARD', 7,'CORRECTIVE',70,'PREVENTIVE',70,'FIELDSERVICE',70,7) order_type,

wob.work_order_id,

esi.inventory_item_id assembly_item_id,

esi.organization_id,

wod.wo_op_dependency_id

FROM

WIE_WORK_ORDERS_V wob,

WIE_WO_OPERATIONS_B wop,

WIE_WO_OPERATIONS_B wop1,

WIE_WO_OP_DEPENDENCIES wod,

MSC_PLANNED_EGP_SYSTEM_ITEMS_V esi

WHERE wob.work_order_id = wod.work_order_id

AND wop.wo_operation_id(+) = wod.from_wo_operation_id

AND wop1.wo_operation_id(+) = wod.to_wo_operation_id

AND wob.inventory_item_id = esi.inventory_item_id

AND wob.organization_id = esi.organization_id

AND wob.system_status_code in (SELECT valid_status FROM job_status)

AND wob.work_method_code in ('DISCRETE_MANUFACTURING','PROCESS_MANUFACTURING')