MSC_AP_WIP_BYPROD_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 ORGANIZATION_ID WIP_SUPPLY_TYPE ORDER_TYPE REVISION QUANTITY_ISSUED QTY_COMPLETED VARIANCE_QTY QTY_SCRAPPED CONVERSION_RATE WIP_START_QUANTITY WORK_ORDER_SUB_TYPE WORK_METHOD_CODE TOTAL_OUTPUT_QUANTITY UOM_CODE OPERATION_SEQ_NUMBER LAST_OP_COMPLETION_DATE SUBINVENTORY_CODE ORDER_STATUS OP_COMPL_FLAG CUMULATIVE_YIELD

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

ORGANIZATION_ID

WIP_SUPPLY_TYPE

ORDER_TYPE

REVISION

QUANTITY_ISSUED

QTY_COMPLETED

VARIANCE_QTY

QTY_SCRAPPED

CONVERSION_RATE

WIP_START_QUANTITY

WORK_ORDER_SUB_TYPE

WORK_METHOD_CODE

TOTAL_OUTPUT_QUANTITY

UOM_CODE

OPERATION_SEQ_NUMBER

LAST_OP_COMPLETION_DATE

SUBINVENTORY_CODE

ORDER_STATUS

OP_COMPL_FLAG

CUMULATIVE_YIELD

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 wom.row_id row_id, NULL output_rowid, DECODE(wob.firm_planned_flag, 'Y', 1, 'N', 2, 2) firm_planned_type , wom.quantity-1 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.actual_completion_date,wob.planned_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,'PENDING_APPROVAL',18,1) wip_status_code, wom.inventory_item_id inventory_item_id, wom.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 , 15 order_type, wob.item_revision revision, wom.issued_quantity-1 quantity_issued, null qty_completed, null variance_qty, null qty_scrapped, NULL conversion_rate, wom.quantity-1 wip_start_quantity, DECODE (wob.work_order_sub_type, 'STANDARD',1,' REPAIR', 2,' PROTOTYPE',3,' PILOT',4,' TEST',5) work_order_sub_type, 1 work_method_code, NULL total_output_quantity, wob.uom_code uom_code, NULL operation_seq_number, NVL(wob.actual_completion_date,wob.planned_completion_date) last_op_completion_date, NULL subinventory_code, -99 ORDER_STATUS, NULL OP_COMPL_FLAG, 1 CUMULATIVE_YIELD FROM WIE_WORK_ORDERS_V wob, WIE_WO_OPERATION_MATERIALS_V wom, WIE_WO_OPERATIONS_B wopsb , MSC_PLANNED_EGP_SYSTEM_ITEMS_V esi WHERE wob.work_order_id = wopsb.work_order_id AND wopsb.wo_operation_id = wom.wo_operation_id AND wopsb.work_order_id = wom.work_order_id AND wom.inventory_item_id = esi.inventory_item_id AND wom.organization_id = esi.organization_id and wob.system_status_code in (SELECT valid_status FROM job_status) and wom.include_in_planning_flag = 'Y' and wom.inventory_item_id != wob.inventory_item_id and wob.work_method_code in ('DISCRETE_MANUFACTURING') and wob.work_order_type in ('REWORK','TRANSFORM') and wom.quantity < 0 UNION ALL SELECT woop.rowid 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 wob.work_method_code = 'PROCESS_MANUFACTURING' and wd.process_batch_type = 'CALCULATED' THEN nvl(wob.nettable_supply_qty_override,outputs.output_quantity) WHEN wob.work_method_code = 'PROCESS_MANUFACTURING' THEN nvl(wob.nettable_supply_qty_override,outputs.output_quantity/decode(wo_yield.cumulative_yield,null,1,0,1,wo_yield.cumulative_yield)) ELSE wob.planned_start_quantity 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.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 , CASE WHEN wob.work_order_type = 'STANDARD' AND OUTPUTS.OUTPUT_TYPE = 'BY_PRODUCT' THEN 14 WHEN wob.work_order_type = 'NON_STANDARD' AND OUTPUTS.OUTPUT_TYPE = 'BY_PRODUCT' THEN 15 WHEN wob.work_order_type = 'STANDARD' AND OUTPUTS.OUTPUT_TYPE = 'PRODUCT' THEN 16 ELSE NULL END order_type, wob.item_revision revision, NULL quantity_issued, outputs.completed_quantity qty_completed, CASE WHEN wob.work_method_code = 'DISCRETE_MANUFACTURING' THEN NULL WHEN wob.work_method_code = 'PROCESS_MANUFACTURING' and 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 wob.work_method_code = 'PROCESS_MANUFACTURING' and 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 wob.work_method_code = 'PROCESS_MANUFACTURING' and 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 wob.work_method_code = 'PROCESS_MANUFACTURING' and wd.process_batch_type = 'CALCULATED' THEN 0 WHEN wob.work_method_code = 'PROCESS_MANUFACTURING' THEN (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) ELSE ((NVL(woop.cumulative_scrap_quantity,0)+ NVL(woop.scrapped_quantity,0))/ wob.planned_start_quantity)outputs.output_quantity END qty_scrapped, inv_convert.inv_um_convert( outputs.inventory_item_id, 10, NULL, outputs.uom_code, esi.uom_code, NULL, NULL) conversion_rate, /* As per PM suggestion, remaining output qty should consider only cum scrap till previous operation/ CASE WHEN wob.work_method_code = 'PROCESS_MANUFACTURING' and 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 wob.work_method_code = 'PROCESS_MANUFACTURING' and wd.process_batch_type = 'CALCULATED' THEN 0 WHEN wob.work_method_code = 'PROCESS_MANUFACTURING' THEN (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))) ELSE (outputs.output_quantity-(((NVL(woop.cumulative_scrap_quantity,0) + NVL(woop.scrapped_quantity,0)) / wob.planned_start_quantity)outputs.output_quantity + 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, DECODE (wob.work_method_code,'PROCESS_MANUFACTURING',2,1) work_method_code, wob.planned_start_quantity total_output_quantity, wob.uom_code uom_code, woop.operation_seq_number operation_seq_number, NVL(wob.actual_completion_date,wob.planned_completion_date) last_op_completion_date, outputs.compl_subinventory_code subinventory_code, NULL ORDER_STATUS, DECODE(wob.work_method_code,'PROCESS_MANUFACTURING',decode(woop.OP_COMPL_FLAG, 'Y', 1, 'N', 2),NULL) OP_COMPL_FLAG, CASE WHEN wob.work_method_code = 'PROCESS_MANUFACTURING' and nvl(wd.process_batch_type,'USER_DEFINED') = 'USER_DEFINED' THEN NVL(wo_yield.CUMULATIVE_YIELD,1) ELSE 1 END CUMULATIVE_YIELD 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 IN ('PROCESS_MANUFACTURING','DISCRETE_MANUFACTURING') AND wob.system_status_code IN (SELECT valid_status FROM job_status) AND wd.work_method_code(+) = wob.work_method_code AND wd.work_definition_id(+) = wob.work_definition_id and woop.work_order_id = wob.work_order_id and outputs.work_order_id = woop.work_order_id and outputs.wo_operation_id = woop.wo_operation_id AND outputs.output_type IN ('PRODUCT','BY_PRODUCT') AND outputs.primary_flag = 'N' AND outputs.inventory_item_id = esi.inventory_item_id AND outputs.organization_id = esi.organization_id AND NOT(wob.work_method_code = 'DISCRETE_MANUFACTURING' AND wob.work_order_type = 'NON_STANDARD') AND wo_yield.WORK_ORDER_ID(+) = outputs.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

wom.row_id row_id,

NULL output_rowid,

DECODE(wob.firm_planned_flag, 'Y', 1, 'N', 2, 2) firm_planned_type ,

wom.quantity*-1 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.actual_completion_date,wob.planned_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,'PENDING_APPROVAL',18,1) wip_status_code,

wom.inventory_item_id inventory_item_id,

wom.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 ,

15 order_type,

wob.item_revision revision,

wom.issued_quantity*-1 quantity_issued,

null qty_completed,

null variance_qty,

null qty_scrapped,

NULL conversion_rate,

wom.quantity*-1 wip_start_quantity,

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

1 work_method_code,

NULL total_output_quantity,

wob.uom_code uom_code,

NULL operation_seq_number,

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

NULL subinventory_code,

-99 ORDER_STATUS,

NULL OP_COMPL_FLAG,

1 CUMULATIVE_YIELD

FROM WIE_WORK_ORDERS_V wob,

WIE_WO_OPERATION_MATERIALS_V wom,

WIE_WO_OPERATIONS_B wopsb ,

MSC_PLANNED_EGP_SYSTEM_ITEMS_V esi

WHERE wob.work_order_id = wopsb.work_order_id

AND wopsb.wo_operation_id = wom.wo_operation_id

AND wopsb.work_order_id = wom.work_order_id

AND wom.inventory_item_id = esi.inventory_item_id

AND wom.organization_id = esi.organization_id

and wob.system_status_code in (SELECT valid_status FROM job_status)

and wom.include_in_planning_flag = 'Y'

and wom.inventory_item_id != wob.inventory_item_id

and wob.work_method_code in ('DISCRETE_MANUFACTURING')

and wob.work_order_type in ('REWORK','TRANSFORM')

and wom.quantity < 0

UNION ALL

SELECT

woop.rowid 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 wob.work_method_code = 'PROCESS_MANUFACTURING' and wd.process_batch_type = 'CALCULATED'

THEN nvl(wob.nettable_supply_qty_override,outputs.output_quantity)

WHEN wob.work_method_code = 'PROCESS_MANUFACTURING'

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

ELSE wob.planned_start_quantity

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.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 ,

CASE

WHEN wob.work_order_type = 'STANDARD' AND OUTPUTS.OUTPUT_TYPE = 'BY_PRODUCT'

THEN 14

WHEN wob.work_order_type = 'NON_STANDARD' AND OUTPUTS.OUTPUT_TYPE = 'BY_PRODUCT'

THEN 15

WHEN wob.work_order_type = 'STANDARD' AND OUTPUTS.OUTPUT_TYPE = 'PRODUCT'

THEN 16

ELSE NULL

END order_type,

wob.item_revision revision,

NULL quantity_issued,

outputs.completed_quantity qty_completed,

CASE WHEN wob.work_method_code = 'DISCRETE_MANUFACTURING'

THEN NULL

WHEN wob.work_method_code = 'PROCESS_MANUFACTURING' and 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 wob.work_method_code = 'PROCESS_MANUFACTURING' and 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 wob.work_method_code = 'PROCESS_MANUFACTURING' and 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 wob.work_method_code = 'PROCESS_MANUFACTURING' and wd.process_batch_type = 'CALCULATED'

THEN 0

WHEN wob.work_method_code = 'PROCESS_MANUFACTURING'

THEN (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)

ELSE ((NVL(woop.cumulative_scrap_quantity,0)+ NVL(woop.scrapped_quantity,0))/ wob.planned_start_quantity)*outputs.output_quantity

END qty_scrapped,

inv_convert.inv_um_convert(

outputs.inventory_item_id,

10,

NULL,

outputs.uom_code,

esi.uom_code,

NULL,

NULL) conversion_rate,

/* As per PM suggestion, remaining output qty should consider only cum scrap till previous operation*/

CASE WHEN wob.work_method_code = 'PROCESS_MANUFACTURING' and 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 wob.work_method_code = 'PROCESS_MANUFACTURING' and wd.process_batch_type = 'CALCULATED' THEN 0

WHEN wob.work_method_code = 'PROCESS_MANUFACTURING'

THEN (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)))

ELSE (outputs.output_quantity-(((NVL(woop.cumulative_scrap_quantity,0) + NVL(woop.scrapped_quantity,0))

/ wob.planned_start_quantity)*outputs.output_quantity

+ 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,

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

wob.planned_start_quantity total_output_quantity,

wob.uom_code uom_code,

woop.operation_seq_number operation_seq_number,

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

outputs.compl_subinventory_code subinventory_code,

NULL ORDER_STATUS,

DECODE(wob.work_method_code,'PROCESS_MANUFACTURING',decode(woop.OP_COMPL_FLAG, 'Y', 1, 'N', 2),NULL) OP_COMPL_FLAG,

CASE WHEN wob.work_method_code = 'PROCESS_MANUFACTURING' and nvl(wd.process_batch_type,'USER_DEFINED') = 'USER_DEFINED'

THEN NVL(wo_yield.CUMULATIVE_YIELD,1)

ELSE 1

END CUMULATIVE_YIELD

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 IN ('PROCESS_MANUFACTURING','DISCRETE_MANUFACTURING')

AND wob.system_status_code IN (SELECT valid_status FROM job_status)

AND wd.work_method_code(+) = wob.work_method_code

AND wd.work_definition_id(+) = wob.work_definition_id

and woop.work_order_id = wob.work_order_id

and outputs.work_order_id = woop.work_order_id

and outputs.wo_operation_id = woop.wo_operation_id

AND outputs.output_type IN ('PRODUCT','BY_PRODUCT')

AND outputs.primary_flag = 'N'

AND outputs.inventory_item_id = esi.inventory_item_id

AND outputs.organization_id = esi.organization_id

AND NOT(wob.work_method_code = 'DISCRETE_MANUFACTURING' AND wob.work_order_type = 'NON_STANDARD')

AND wo_yield.WORK_ORDER_ID(+) = outputs.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