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

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

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

Query

SQL_Statement
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, 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 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 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 ('UNRELEASED','RELEASED','COMPLETED','ON_HOLD','PENDING_APPROVAL') 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 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 , nvl(wob.nettable_supply_qty_override,outputs.output_quantity) 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, (NVL(woop.cumulative_scrap_quantity,0) / wob.planned_start_quantity)outputs.output_quantity 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 , (outputs.output_quantity-((greatest( wob.planned_start_quantitynvl(esi.shrinkage_rate,0), NVL(woop.cumulative_scrap_quantity,0)) / wob.planned_start_quantity)outputs.output_quantity + NVL(outputs.completed_quantity,0))) 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 , nvl(wo_yield.CUMULATIVE_YIELD,1) CUMULATIVE_YIELD , outputs.compl_subinventory_code subinventory_code, null fs_wd_header_id 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, ( 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 WHERE wob.work_method_code ='PROCESS_MANUFACTURING' AND wob.system_status_code IN ('UNRELEASED','RELEASED','COMPLETED','ON_HOLD','PENDING_APPROVAL') 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

SQL_Statement

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,

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

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

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 ('UNRELEASED','RELEASED','COMPLETED','ON_HOLD','PENDING_APPROVAL')

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

nvl(wob.nettable_supply_qty_override,outputs.output_quantity) 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,

(NVL(woop.cumulative_scrap_quantity,0)

/ wob.planned_start_quantity)*outputs.output_quantity 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 ,

(outputs.output_quantity-((greatest( wob.planned_start_quantity*nvl(esi.shrinkage_rate,0), NVL(woop.cumulative_scrap_quantity,0))

/ wob.planned_start_quantity)*outputs.output_quantity

+ NVL(outputs.completed_quantity,0))) 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 ,

nvl(wo_yield.CUMULATIVE_YIELD,1) CUMULATIVE_YIELD ,

outputs.compl_subinventory_code subinventory_code,

null fs_wd_header_id

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,

(

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

WHERE wob.work_method_code ='PROCESS_MANUFACTURING'

AND wob.system_status_code IN ('UNRELEASED','RELEASED','COMPLETED','ON_HOLD','PENDING_APPROVAL')

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