1. Tables and Views for SCM
  2. MSC_AP_WIP_BYPROD_SUPPLIES_V

MSC_AP_WIP_BYPROD_SUPPLIES_V

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

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

Query

SQL_Statement

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

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

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'

THEN nvl(wob.nettable_supply_qty_override,outputs.output_quantity)

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 = 'PROCESS_MANUFACTURING'

THEN (NVL(woop.cumulative_scrap_quantity,0) / wob.planned_start_quantity)*outputs.output_quantity

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'

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

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

FROM WIE_WORK_ORDERS_V wob,

WIE_WO_OPERATION_OUTPUTS outputs,

WIE_WO_OPERATIONS_B WOOP,

MSC_PLANNED_EGP_SYSTEM_ITEMS_V esi

WHERE wob.work_method_code IN ('PROCESS_MANUFACTURING','DISCRETE_MANUFACTURING')

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

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