MSC_AP_WIP_JOB_SUPPLIES

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
ROW_ID OUTPUT_ROWID FIRM_PLANNED_TYPE NEW_ORDER_QUANTITY WIP_ENTITY_NAME WORK_ORDER_ID ORGANIZATION_CODE ITEM_NAME NEW_WIP_START_DATE NEW_SCHEDULE_DATE ACTUAL_START_DATE SCHEDULE_PRIORITY WIP_STATUS_CODE INVENTORY_ITEM_ID ORDER_NUMBER ORGANIZATION_ID WIP_SUPPLY_TYPE ORDER_TYPE EXPECTED_SCRAP_QTY QTY_COMPLETED VARIANCE_QTY QTY_SCRAPPED REVISION ALTERNATE_BOM_DESIGNATOR ALTERNATE_ROUTING_DESIGNATOR ROUTING_SEQUENCE_ID CONVERSION_RATE WIP_START_QUANTITY WORK_ORDER_SUB_TYPE WORK_METHOD_CODE PROCESS_NAME TOTAL_OUTPUT_QUANTITY UOM_CODE LAST_OP_COMPLETION_DATE OPERATION_SEQ_NUMBER PJC_PROJECT_ID PJC_TASK_ID UNDER_COMPLETED_FLAG CUMULATIVE_YIELD SUBINVENTORY_CODE FS_WD_HEADER_ID LAST_COMPL_OP_SEQ_NUM NUM_OF_REMAINING_STEPS OP_COMPL_FLAG

Name

ROW_ID

OUTPUT_ROWID

FIRM_PLANNED_TYPE

NEW_ORDER_QUANTITY

WIP_ENTITY_NAME

WORK_ORDER_ID

ORGANIZATION_CODE

ITEM_NAME

NEW_WIP_START_DATE

NEW_SCHEDULE_DATE

ACTUAL_START_DATE

SCHEDULE_PRIORITY

WIP_STATUS_CODE

INVENTORY_ITEM_ID

ORDER_NUMBER

ORGANIZATION_ID

WIP_SUPPLY_TYPE

ORDER_TYPE

EXPECTED_SCRAP_QTY

QTY_COMPLETED

VARIANCE_QTY

QTY_SCRAPPED

REVISION

ALTERNATE_BOM_DESIGNATOR

ALTERNATE_ROUTING_DESIGNATOR

ROUTING_SEQUENCE_ID

CONVERSION_RATE

WIP_START_QUANTITY

WORK_ORDER_SUB_TYPE

WORK_METHOD_CODE

PROCESS_NAME

TOTAL_OUTPUT_QUANTITY

UOM_CODE

LAST_OP_COMPLETION_DATE

OPERATION_SEQ_NUMBER

PJC_PROJECT_ID

PJC_TASK_ID

UNDER_COMPLETED_FLAG

CUMULATIVE_YIELD

SUBINVENTORY_CODE

FS_WD_HEADER_ID

LAST_COMPL_OP_SEQ_NUM

NUM_OF_REMAINING_STEPS

OP_COMPL_FLAG

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 wob.row_id row_id, NULL output_rowid, DECODE(DECODE(wob.work_method_code, 'FLOW_MANUFACTURING', 'Y', DECODE(wob.work_order_type,'REWORK','Y','TRANSFORM','Y','CORRECTIVE','Y','PREVENTIVE','Y','FIELDSERVICE','Y',wob.firm_planned_flag) ), 'Y', 1, 'N', 2, 2) firm_planned_type , wob.nettable_supply_qty_override new_order_quantity, wob.work_order_number wip_entity_name, wob.WORK_ORDER_ID, esi.organization_code organization_code, esi.item_number item_name, NVL(wob.planned_start_date,wob.actual_start_date) new_wip_start_date, NVL(wob.planned_completion_date,wob.actual_completion_date) new_schedule_date, wob.actual_start_date actual_start_date, wob.work_order_priority schedule_priority, DECODE(wob.system_status_code,'UNRELEASED',1,'RELEASED',3,'COMPLETED',4,'ON_HOLD',6,'PENDING_APPROVAL',18,1) wip_status_code, wob.inventory_item_id inventory_item_id, wob.work_order_number order_number, wob.organization_id organization_id, DECODE (wob.SUPPLY_TYPE, 'ASSEMBLY_PULL', 2, 'BASED_ON_WD', 7, 'BULK', 4, 'OPERATION_PULL',3, 'PUSH',1, 'SUPPLIER',5,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,7) wip_supply_type , DECODE(wob.work_method_code, 'FLOW_MANUFACTURING', 27, DECODE(wob.work_order_type, 'STANDARD', 3, 'NON_STANDARD', 7,'CORRECTIVE',70,'PREVENTIVE',70,'FIELDSERVICE',70,7) ) order_type, greatest(0, DECODE(wob.work_order_type, 'STANDARD', wob.planned_start_quantity - NVL(wob.completed_quantity, 0) - NVL(wob.scrapped_quantity, 0), 0) * DECODE(SIGN(esi.shrinkage_rate), -1, 0, NVL(esi.shrinkage_rate, 0))) expected_scrap_qty, wob.completed_quantity qty_completed, null variance_qty, wob.scrapped_quantity QTY_SCRAPPED, wob.item_revision revision, decode(wob.WORK_METHOD_CODE,'MAINTENANCE',Null,wd.work_definition_code) alternate_bom_designator, decode(wob.WORK_METHOD_CODE,'MAINTENANCE',Null,wd.work_definition_code) alternate_routing_designator, decode(wob.WORK_METHOD_CODE,'MAINTENANCE',to_number(null),wd.WORK_DEFINITION_ID) routing_sequence_id, NULL conversion_rate , wob.planned_start_quantity wip_start_quantity, DECODE (wob.WORK_ORDER_SUB_TYPE, 'STANDARD',1,' REPAIR', 2,' PROTOTYPE',3,' PILOT',4,' TEST',5) WORK_ORDER_SUB_TYPE, decode(wob.WORK_METHOD_CODE,'DISCRETE_MANUFACTURING',1,'MAINTENANCE',3,'FLOW_MANUFACTURING',4,1) WORK_METHOD_CODE , NULL process_name, NULL total_output_quantity, wob.uom_code uom_code, NVL(wob.actual_completion_date,wob.planned_completion_date) last_op_completion_date, NULL operation_seq_number, wob.pjc_project_id pjc_project_id, wob.pjc_task_id pjc_task_id, DECODE(WOB.UNDER_COMPLETED_FLAG , 'Y', 1 ,2 ) UNDER_COMPLETED_FLAG , nvl(wo_yield.CUMULATIVE_YIELD,1) CUMULATIVE_YIELD , wob.compl_subinventory_code subinventory_code, wob.fs_wd_header_id, wob.last_compl_op_seq_num, (prod_line.num_of_line_operations - wwob.line_op_seq_number) num_of_remaining_steps, null OP_COMPL_FLAG FROM WIS_WORK_DEFINITIONS_V wd, WIE_WORK_ORDERS_V wob, MSC_PLANNED_EGP_SYSTEM_ITEMS_V esi, ( SELECT WORK_ORDER_ID, REVERSE_CUMULATIVE_YIELD CUMULATIVE_YIELD FROM ( select WORK_ORDER_ID, OPERATION_SEQ_NUMBER, REVERSE_CUMULATIVE_YIELD, row_number() over (partition by WORK_ORDER_ID order by OPERATION_SEQ_NUMBER,WORK_ORDER_ID) rnum from WIE_WO_OP_YIELD_V ) where rnum = 1 ) wo_yield, ( select fs_wd_header_id, operation_seq_number, wd_operation_id from ( SELECT fs_wd_header_id, operation_seq_number, wd_operation_id, row_number() over (partition by fs_wd_header_id, operation_seq_number, wd_operation_id order by fs_wd_header_id) no_duplicate FROM wie_fs_wd_op_materials ) where no_duplicate = 1 ) wo_flow_op, wis_wd_operations_b wwob, wis_production_lines_b prod_line WHERE wd.WORK_DEFINITION_ID(+) = wob.work_definition_id AND wd.status_code(+) in ('ACTIVE','PENDING_INACTIVE') 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','MAINTENANCE','FLOW_MANUFACTURING') AND wd.used_in_planning_flag(+) ='Y' AND wo_yield.WORK_ORDER_ID(+) = wob.work_order_id AND wo_flow_op.fs_wd_header_id(+) = wob.fs_wd_header_id AND wo_flow_op.operation_seq_number(+) = wob.last_compl_op_seq_num AND wwob.wd_operation_id(+) = wo_flow_op.wd_operation_id AND prod_line.production_line_id(+) = wob.production_line_id AND DECODE(wob.work_method_code,'FLOW_MANUFACTURING',wob.fs_wd_header_id, -1) IS NOT NULL UNION ALL SELECT wob.row_id row_id, outputs.rowid output_rowid, DECODE(DECODE(wob.work_order_type, 'REWORK','Y', 'TRANSFORM','Y', wob.firm_planned_flag), 'Y', 1, 'N', 2, 2) firm_planned_type , CASE WHEN wd.process_batch_type = 'CALCULATED' THEN nvl(wob.nettable_supply_qty_override,outputs.output_quantity) ELSE nvl(wob.nettable_supply_qty_override,outputs.output_quantity/decode(wo_yield.cumulative_yield,null,1,0,1,wo_yield.cumulative_yield)) END new_order_quantity, wob.work_order_number wip_entity_name, wob.work_order_id, esi.organization_code organization_code, esi.item_number item_name, NVL(wob.planned_start_date, wob.actual_start_date) new_wip_start_date, NVL(woop.actual_completion_date, woop.planned_completion_date) new_schedule_date, woop.actual_start_date actual_start_date, wob.work_order_priority schedule_priority, DECODE(wob.system_status_code, 'UNRELEASED',1, 'RELEASED',3, 'COMPLETED',4, 'PENDING_APPROVAL',18, 'ON_HOLD',6,1) wip_status_code, wob.inventory_item_id inventory_item_id, wob.work_order_number order_number, wob.organization_id organization_id, DECODE(wob.supply_type, 'ASSEMBLY_PULL', 2, 'BASED_ON_WD', 7, 'BULK', 4, 'OPERATION_PULL',3, 'PUSH',1, 'SUPPLIER',5, '1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,7) wip_supply_type , DECODE(wob.work_order_type, 'STANDARD', 3, 'NON_STANDARD', 7,7) order_type, NULL expected_scrap_qty, outputs.completed_quantity qty_completed, CASE WHEN wd.process_batch_type = 'CALCULATED' AND physical_batch.planned_start_quantity_new > 0 THEN (NVL(woop.cumulative_variance_quantity,0) / physical_batch.planned_start_quantity_new)outputs.output_quantity WHEN wd.process_batch_type = 'CALCULATED' THEN 0 ELSE (NVL(woop.cumulative_variance_quantity,0) / wob.planned_start_quantity)outputs.output_quantity/decode(wo_yield.cumulative_yield,null,1,0,1,wo_yield.cumulative_yield) END variance_qty , CASE WHEN wd.process_batch_type = 'CALCULATED' AND physical_batch.planned_start_quantity_new > 0 THEN (NVL(woop.cumulative_scrap_quantity,0)/ physical_batch.planned_start_quantity_new)outputs.output_quantity WHEN wd.process_batch_type = 'CALCULATED' THEN 0 ELSE (NVL(woop.cumulative_scrap_quantity,0)/ wob.planned_start_quantity)outputs.output_quantity/decode(wo_yield.cumulative_yield,null,1,0,1,wo_yield.cumulative_yield) END qty_scrapped, wob.item_revision revision, wd.work_definition_code alternate_bom_designator, wd.work_definition_code alternate_routing_designator, wd.work_definition_id routing_sequence_id, inv_convert.inv_um_convert( outputs.inventory_item_id, 10, NULL, outputs.uom_code, esi.UOM_CODE, NULL, NULL) conversion_rate , CASE WHEN wd.process_batch_type = 'CALCULATED' AND physical_batch.planned_start_quantity_new > 0 THEN (outputs.output_quantity -(((greatest( physical_batch.planned_start_quantity_newnvl(esi.shrinkage_rate,0), NVL(woop.cumulative_scrap_quantity,0)) + NVL(woop.CUMULATIVE_VARIANCE_QUANTITY,0)) / physical_batch.planned_start_quantity_new)outputs.output_quantity + NVL(outputs.completed_quantity,0))) WHEN wd.process_batch_type = 'CALCULATED' THEN 0 ELSE (outputs.output_quantity/decode(wo_yield.cumulative_yield,null,1,0,1,wo_yield.cumulative_yield) -(((greatest( wob.planned_start_quantitynvl(esi.shrinkage_rate,0), NVL(woop.cumulative_scrap_quantity,0)) + NVL(woop.CUMULATIVE_VARIANCE_QUANTITY,0)) / wob.planned_start_quantity)outputs.output_quantity/decode(wo_yield.cumulative_yield,null,1,0,1,wo_yield.cumulative_yield) + NVL(outputs.completed_quantity,0))) END wip_start_quantity, DECODE (wob.work_order_sub_type, 'STANDARD', 1, 'REPAIR', 2, 'PROTOTYPE', 3, 'PILOT', 4, 'TEST', 5) work_order_sub_type, 2 work_method_code, wd.work_definition_header_name process_name, wob.planned_start_quantity total_output_quantity, wob.uom_code uom_code, NVL(wob.actual_completion_date,wob.planned_completion_date) last_op_completion_date, woop.operation_seq_number operation_seq_number, wob.pjc_project_id pjc_project_id, wob.pjc_task_id pjc_task_id, DECODE(WOB.UNDER_COMPLETED_FLAG , 'Y', 1 ,2 ) UNDER_COMPLETED_FLAG , CASE WHEN wd.process_batch_type = 'CALCULATED' THEN 1 ELSE nvl(wo_yield.CUMULATIVE_YIELD,1) END CUMULATIVE_YIELD , outputs.compl_subinventory_code subinventory_code, null fs_wd_header_id, null last_compl_op_seq_num, null num_of_remaining_steps, decode(woop.OP_COMPL_FLAG, 'Y', 1, 'N', 2) OP_COMPL_FLAG FROM WIE_WORK_ORDERS_V wob, WIE_WO_OPERATION_OUTPUTS outputs, WIE_WO_OPERATIONS_B woop, MSC_PLANNED_EGP_SYSTEM_ITEMS_V esi, WIS_WORK_DEFINITIONS_V wd, WIE_WO_OP_YIELD_V wo_yield, (select * FROM ( select wop.work_order_id, wop.wo_operation_id, sum(wom.quantity) over (partition by wop.work_order_id order by wop.operation_seq_number ) planned_start_quantity_new , row_number() over (partition by wop.work_order_id , wop.wo_operation_id order by wop.operation_seq_number ) dup_rec from WIS_WORK_DEFINITIONS_V wd1, WIE_WORK_ORDERS_V wob1, WIE_WO_OPERATIONS_B wop, WIE_WO_OPERATION_MATERIALS wom, MSC_PLANNED_EGP_SYSTEM_ITEMS_V esib where wd1.PROCESS_BATCH_TYPE = 'CALCULATED' and wd1.status_code in ('ACTIVE','PENDING_INACTIVE') and wd1.used_in_planning_flag = 'Y' and wd1.work_definition_id = wob1.work_definition_id and wob1.work_method_code ='PROCESS_MANUFACTURING' and wob1.system_status_code in (SELECT valid_status FROM job_status) and wob1.work_order_id = wop.work_order_id and wop.work_order_id = wom.work_order_id(+) and wop.wo_operation_id = wom.wo_operation_id(+) and wom.inventory_item_id = esib.inventory_item_id(+) and wom.organization_id = esib.organization_id(+) and wom.include_in_planning_flag(+) = 'Y' and wom.material_type(+) in ('COMPONENT','INDIRECT') and wom.inventory_item_id(+) != wob1.inventory_item_id and wom.quantity(+) > 0 and nvl(wom.contribute_to_yield_flag(+),'N') = 'Y' and wom.SUPPLY_TYPE(+) in ('PUSH','1') ) where dup_rec = 1 ) physical_batch WHERE wob.work_method_code ='PROCESS_MANUFACTURING' AND wob.system_status_code IN (SELECT valid_status FROM job_status) AND wob.work_order_id = woop.work_order_id AND woop.work_order_id = outputs.work_order_id AND woop.wo_operation_id = outputs.wo_operation_id AND outputs.primary_flag = 'Y' AND outputs.inventory_item_id = esi.inventory_item_id AND outputs.organization_id = esi.organization_id /* currently MFG supports only STD Process Work orders Hence we will go with inner join with work definitions */ AND wd.work_method_code = wob.work_method_code AND wd.work_definition_id = wob.work_definition_id AND wd.status_code IN ('ACTIVE','PENDING_INACTIVE') AND wd.used_in_planning_flag = 'Y' AND wo_yield.WORK_ORDER_ID(+) = wob.work_order_id AND wo_yield.wo_operation_id(+) = outputs.wo_operation_id AND physical_batch.WORK_ORDER_ID(+) = wob.work_order_id AND physical_batch.wo_operation_id(+) = woop.wo_operation_id

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

wob.row_id row_id,

NULL output_rowid,

DECODE(DECODE(wob.work_method_code, 'FLOW_MANUFACTURING', 'Y',

DECODE(wob.work_order_type,'REWORK','Y','TRANSFORM','Y','CORRECTIVE','Y','PREVENTIVE','Y','FIELDSERVICE','Y',wob.firm_planned_flag)

), 'Y', 1, 'N', 2, 2) firm_planned_type ,

wob.nettable_supply_qty_override new_order_quantity,

wob.work_order_number wip_entity_name,

wob.WORK_ORDER_ID,

esi.organization_code organization_code,

esi.item_number item_name,

NVL(wob.planned_start_date,wob.actual_start_date) new_wip_start_date,

NVL(wob.planned_completion_date,wob.actual_completion_date) new_schedule_date,

wob.actual_start_date actual_start_date,

wob.work_order_priority schedule_priority,

DECODE(wob.system_status_code,'UNRELEASED',1,'RELEASED',3,'COMPLETED',4,'ON_HOLD',6,'PENDING_APPROVAL',18,1) wip_status_code,

wob.inventory_item_id inventory_item_id,

wob.work_order_number order_number,

wob.organization_id organization_id,

DECODE (wob.SUPPLY_TYPE, 'ASSEMBLY_PULL', 2, 'BASED_ON_WD', 7, 'BULK', 4, 'OPERATION_PULL',3, 'PUSH',1, 'SUPPLIER',5,'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,7) wip_supply_type ,

DECODE(wob.work_method_code, 'FLOW_MANUFACTURING', 27,

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

) order_type,

greatest(0, DECODE(wob.work_order_type, 'STANDARD', wob.planned_start_quantity - NVL(wob.completed_quantity, 0) - NVL(wob.scrapped_quantity, 0), 0) * DECODE(SIGN(esi.shrinkage_rate), -1, 0, NVL(esi.shrinkage_rate, 0))) expected_scrap_qty,

wob.completed_quantity qty_completed,

null variance_qty,

wob.scrapped_quantity QTY_SCRAPPED,

wob.item_revision revision,

decode(wob.WORK_METHOD_CODE,'MAINTENANCE',Null,wd.work_definition_code) alternate_bom_designator,

decode(wob.WORK_METHOD_CODE,'MAINTENANCE',Null,wd.work_definition_code) alternate_routing_designator,

decode(wob.WORK_METHOD_CODE,'MAINTENANCE',to_number(null),wd.WORK_DEFINITION_ID) routing_sequence_id,

NULL conversion_rate ,

wob.planned_start_quantity wip_start_quantity,

DECODE (wob.WORK_ORDER_SUB_TYPE, 'STANDARD',1,' REPAIR', 2,' PROTOTYPE',3,' PILOT',4,' TEST',5) WORK_ORDER_SUB_TYPE,

decode(wob.WORK_METHOD_CODE,'DISCRETE_MANUFACTURING',1,'MAINTENANCE',3,'FLOW_MANUFACTURING',4,1) WORK_METHOD_CODE ,

NULL process_name,

NULL total_output_quantity,

wob.uom_code uom_code,

NVL(wob.actual_completion_date,wob.planned_completion_date) last_op_completion_date,

NULL operation_seq_number,

wob.pjc_project_id pjc_project_id,

wob.pjc_task_id pjc_task_id,

DECODE(WOB.UNDER_COMPLETED_FLAG , 'Y', 1 ,2 ) UNDER_COMPLETED_FLAG ,

nvl(wo_yield.CUMULATIVE_YIELD,1) CUMULATIVE_YIELD ,

wob.compl_subinventory_code subinventory_code,

wob.fs_wd_header_id,

wob.last_compl_op_seq_num,

(prod_line.num_of_line_operations - wwob.line_op_seq_number) num_of_remaining_steps,

null OP_COMPL_FLAG

FROM

WIS_WORK_DEFINITIONS_V wd,

WIE_WORK_ORDERS_V wob,

MSC_PLANNED_EGP_SYSTEM_ITEMS_V esi,

(

SELECT

WORK_ORDER_ID,

REVERSE_CUMULATIVE_YIELD CUMULATIVE_YIELD

FROM (

select

WORK_ORDER_ID,

OPERATION_SEQ_NUMBER,

REVERSE_CUMULATIVE_YIELD,

row_number() over (partition by WORK_ORDER_ID order by OPERATION_SEQ_NUMBER,WORK_ORDER_ID) rnum

from WIE_WO_OP_YIELD_V

)

where rnum = 1

) wo_yield,

(

select fs_wd_header_id, operation_seq_number, wd_operation_id

from (

SELECT fs_wd_header_id, operation_seq_number, wd_operation_id,

row_number() over (partition by fs_wd_header_id, operation_seq_number, wd_operation_id order by fs_wd_header_id) no_duplicate

FROM wie_fs_wd_op_materials

)

where no_duplicate = 1

) wo_flow_op,

wis_wd_operations_b wwob,

wis_production_lines_b prod_line

WHERE wd.WORK_DEFINITION_ID(+) = wob.work_definition_id

AND wd.status_code(+) in ('ACTIVE','PENDING_INACTIVE')

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','MAINTENANCE','FLOW_MANUFACTURING')

AND wd.used_in_planning_flag(+) ='Y'

AND wo_yield.WORK_ORDER_ID(+) = wob.work_order_id

AND wo_flow_op.fs_wd_header_id(+) = wob.fs_wd_header_id

AND wo_flow_op.operation_seq_number(+) = wob.last_compl_op_seq_num

AND wwob.wd_operation_id(+) = wo_flow_op.wd_operation_id

AND prod_line.production_line_id(+) = wob.production_line_id

AND DECODE(wob.work_method_code,'FLOW_MANUFACTURING',wob.fs_wd_header_id, -1) IS NOT NULL

UNION ALL

SELECT wob.row_id row_id,

outputs.rowid output_rowid,

DECODE(DECODE(wob.work_order_type,

'REWORK','Y',

'TRANSFORM','Y',

wob.firm_planned_flag),

'Y', 1,

'N', 2, 2) firm_planned_type ,

CASE WHEN wd.process_batch_type = 'CALCULATED' THEN

nvl(wob.nettable_supply_qty_override,outputs.output_quantity)

ELSE

nvl(wob.nettable_supply_qty_override,outputs.output_quantity/decode(wo_yield.cumulative_yield,null,1,0,1,wo_yield.cumulative_yield))

END new_order_quantity,

wob.work_order_number wip_entity_name,

wob.work_order_id,

esi.organization_code organization_code,

esi.item_number item_name,

NVL(wob.planned_start_date, wob.actual_start_date) new_wip_start_date,

NVL(woop.actual_completion_date, woop.planned_completion_date) new_schedule_date,

woop.actual_start_date actual_start_date,

wob.work_order_priority schedule_priority,

DECODE(wob.system_status_code,

'UNRELEASED',1,

'RELEASED',3,

'COMPLETED',4,

'PENDING_APPROVAL',18,

'ON_HOLD',6,1) wip_status_code,

wob.inventory_item_id inventory_item_id,

wob.work_order_number order_number,

wob.organization_id organization_id,

DECODE(wob.supply_type,

'ASSEMBLY_PULL', 2,

'BASED_ON_WD', 7,

'BULK', 4,

'OPERATION_PULL',3,

'PUSH',1,

'SUPPLIER',5,

'1',1,'2',2,'3',3,'4',4,'5',5,'6',6,'7',7,7) wip_supply_type ,

DECODE(wob.work_order_type,

'STANDARD', 3,

'NON_STANDARD', 7,7) order_type,

NULL expected_scrap_qty,

outputs.completed_quantity qty_completed,

CASE WHEN wd.process_batch_type = 'CALCULATED' AND physical_batch.planned_start_quantity_new > 0 THEN

(NVL(woop.cumulative_variance_quantity,0) / physical_batch.planned_start_quantity_new)*outputs.output_quantity

WHEN wd.process_batch_type = 'CALCULATED' THEN 0

ELSE

(NVL(woop.cumulative_variance_quantity,0) / wob.planned_start_quantity)*outputs.output_quantity/decode(wo_yield.cumulative_yield,null,1,0,1,wo_yield.cumulative_yield)

END variance_qty ,

CASE WHEN wd.process_batch_type = 'CALCULATED' AND physical_batch.planned_start_quantity_new > 0 THEN

(NVL(woop.cumulative_scrap_quantity,0)/ physical_batch.planned_start_quantity_new)*outputs.output_quantity

WHEN wd.process_batch_type = 'CALCULATED' THEN 0

ELSE

(NVL(woop.cumulative_scrap_quantity,0)/ wob.planned_start_quantity)*outputs.output_quantity/decode(wo_yield.cumulative_yield,null,1,0,1,wo_yield.cumulative_yield)

END qty_scrapped,

wob.item_revision revision,

wd.work_definition_code alternate_bom_designator,

wd.work_definition_code alternate_routing_designator,

wd.work_definition_id routing_sequence_id,

inv_convert.inv_um_convert(

outputs.inventory_item_id,

10,

NULL,

outputs.uom_code,

esi.UOM_CODE,

NULL,

NULL) conversion_rate ,

CASE WHEN wd.process_batch_type = 'CALCULATED' AND physical_batch.planned_start_quantity_new > 0 THEN

(outputs.output_quantity -(((greatest( physical_batch.planned_start_quantity_new*nvl(esi.shrinkage_rate,0), NVL(woop.cumulative_scrap_quantity,0)) + NVL(woop.CUMULATIVE_VARIANCE_QUANTITY,0))

/ physical_batch.planned_start_quantity_new)*outputs.output_quantity + NVL(outputs.completed_quantity,0)))

WHEN wd.process_batch_type = 'CALCULATED' THEN 0

ELSE

(outputs.output_quantity/decode(wo_yield.cumulative_yield,null,1,0,1,wo_yield.cumulative_yield) -(((greatest( wob.planned_start_quantity*nvl(esi.shrinkage_rate,0), NVL(woop.cumulative_scrap_quantity,0)) + NVL(woop.CUMULATIVE_VARIANCE_QUANTITY,0))

/ wob.planned_start_quantity)*outputs.output_quantity/decode(wo_yield.cumulative_yield,null,1,0,1,wo_yield.cumulative_yield) + NVL(outputs.completed_quantity,0)))

END wip_start_quantity,

DECODE (wob.work_order_sub_type,

'STANDARD', 1,

'REPAIR', 2,

'PROTOTYPE', 3,

'PILOT', 4,

'TEST', 5) work_order_sub_type,

2 work_method_code,

wd.work_definition_header_name process_name,

wob.planned_start_quantity total_output_quantity,

wob.uom_code uom_code,

NVL(wob.actual_completion_date,wob.planned_completion_date) last_op_completion_date,

woop.operation_seq_number operation_seq_number,

wob.pjc_project_id pjc_project_id,

wob.pjc_task_id pjc_task_id,

DECODE(WOB.UNDER_COMPLETED_FLAG , 'Y', 1 ,2 ) UNDER_COMPLETED_FLAG ,

CASE WHEN wd.process_batch_type = 'CALCULATED' THEN 1

ELSE nvl(wo_yield.CUMULATIVE_YIELD,1)

END CUMULATIVE_YIELD ,

outputs.compl_subinventory_code subinventory_code,

null fs_wd_header_id,

null last_compl_op_seq_num,

null num_of_remaining_steps,

decode(woop.OP_COMPL_FLAG, 'Y', 1, 'N', 2) OP_COMPL_FLAG

FROM WIE_WORK_ORDERS_V wob,

WIE_WO_OPERATION_OUTPUTS outputs,

WIE_WO_OPERATIONS_B woop,

MSC_PLANNED_EGP_SYSTEM_ITEMS_V esi,

WIS_WORK_DEFINITIONS_V wd,

WIE_WO_OP_YIELD_V wo_yield,

(select * FROM (

select wop.work_order_id, wop.wo_operation_id,

sum(wom.quantity) over (partition by wop.work_order_id order by wop.operation_seq_number ) planned_start_quantity_new ,

row_number() over (partition by wop.work_order_id , wop.wo_operation_id order by wop.operation_seq_number ) dup_rec

from WIS_WORK_DEFINITIONS_V wd1,

WIE_WORK_ORDERS_V wob1,

WIE_WO_OPERATIONS_B wop,

WIE_WO_OPERATION_MATERIALS wom,

MSC_PLANNED_EGP_SYSTEM_ITEMS_V esib

where wd1.PROCESS_BATCH_TYPE = 'CALCULATED'

and wd1.status_code in ('ACTIVE','PENDING_INACTIVE')

and wd1.used_in_planning_flag = 'Y'

and wd1.work_definition_id = wob1.work_definition_id

and wob1.work_method_code ='PROCESS_MANUFACTURING'

and wob1.system_status_code in (SELECT valid_status FROM job_status)

and wob1.work_order_id = wop.work_order_id

and wop.work_order_id = wom.work_order_id(+)

and wop.wo_operation_id = wom.wo_operation_id(+)

and wom.inventory_item_id = esib.inventory_item_id(+)

and wom.organization_id = esib.organization_id(+)

and wom.include_in_planning_flag(+) = 'Y'

and wom.material_type(+) in ('COMPONENT','INDIRECT')

and wom.inventory_item_id(+) != wob1.inventory_item_id

and wom.quantity(+) > 0

and nvl(wom.contribute_to_yield_flag(+),'N') = 'Y'

and wom.SUPPLY_TYPE(+) in ('PUSH','1')

) where dup_rec = 1

) physical_batch

WHERE wob.work_method_code ='PROCESS_MANUFACTURING'

AND wob.system_status_code IN (SELECT valid_status FROM job_status)

AND wob.work_order_id = woop.work_order_id

AND woop.work_order_id = outputs.work_order_id

AND woop.wo_operation_id = outputs.wo_operation_id

AND outputs.primary_flag = 'Y'

AND outputs.inventory_item_id = esi.inventory_item_id

AND outputs.organization_id = esi.organization_id

/* currently MFG supports only STD Process Work orders

Hence we will go with inner join with work definitions */

AND wd.work_method_code = wob.work_method_code

AND wd.work_definition_id = wob.work_definition_id

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

AND wd.used_in_planning_flag = 'Y'

AND wo_yield.WORK_ORDER_ID(+) = wob.work_order_id

AND wo_yield.wo_operation_id(+) = outputs.wo_operation_id

AND physical_batch.WORK_ORDER_ID(+) = wob.work_order_id

AND physical_batch.wo_operation_id(+) = woop.wo_operation_id