1. Tables and Views for SCM
  2. MSC_AP_WIP_JOB_COMP_DEMANDS_V

MSC_AP_WIP_JOB_COMP_DEMANDS_V

Details

  • Schema: FUSION

  • Object owner: MSC

  • Object type: VIEW

Columns

Name

ORGANIZATION_ID

INVENTORY_ITEM_ID

ITEM_NAME

ORGANIZATION_CODE

USING_REQUIREMENT_QUANTITY

CONVERSION_RATE

USING_ASSEMBLY_DEMAND_DATE

ORDER_NUMBER

OPERATION_SEQ_NUM

WIP_ENTITY_NAME

QUANTITY_ISSUED

WIP_ENTITY_ID

WIP_STATUS_CODE

WIP_SUPPLY_TYPE

ORIGINATION_TYPE

USING_ASSEMBLY_ITEM_ID

DEMAND_TYPE

PROJECT_ID

TASK_ID

PLANNING_GROUP

END_ITEM_UNIT_NUMBER

DEMAND_CLASS

MPS_DATE_REQUIRED

QUANTITY_PER_ASSEMBLY

ASSET_ITEM_ID

ASSET_SERIAL_NUMBER

COMPONENT_SCALING_TYPE

COMPONENT_YIELD_FACTOR

MATERIAL_SEQ_NUMBER

OPERATION_EFFECTIVITY_DATE

OPROWID

OPMROWID

WORK_METHOD_CODE

EXECUTION_SEQUENCE

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

), SCP as ( select msc_snapshot_util.IS_PROFILE_PARAMETER_ENABLED('DisableEnforceIntReq') ENFORCE_INT_REQ_DISABLE from msc_apps_instances where rownum = 1 )

SELECT wob.organization_id,

wom.inventory_item_id,

esib.item_number item_name ,

(SELECT O.ORGANIZATION_CODE FROM INV_ORG_PARAMETERS O WHERE O.ORGANIZATION_ID=esib.ORGANIZATION_ID) as organization_code ,

CASE WHEN NVL(SCP.ENFORCE_INT_REQ_DISABLE,2) != 1 and wom.extended_quantity is NOT NULL and wom.ENFORCE_INT_REQUIREMENTS is NOT NULL and wom.ENFORCE_INT_REQUIREMENTS != 0

THEN

CASE WHEN wmb.work_method_code IN( 'DISCRETE_MANUFACTURING', 'MAINTENANCE')

THEN DECODE(wom.basis_type,1,wom.extended_quantity - DECODE(wom.ENFORCE_INT_REQUIREMENTS,2,CEIL(NVL(wom.issued_quantity, 0)+(wom.quantity_per_product * NVL(WWOOB.cumulative_scrap_quantity, 0))),1,FLOOR(NVL(wom.issued_quantity, 0)+(wom.quantity_per_product * NVL(WWOOB.cumulative_scrap_quantity, 0))))

,2,wom.extended_quantity - greatest(NVL(wom.issued_quantity, 0),0))

WHEN wmb.work_method_code = 'PROCESS_MANUFACTURING' AND NVL(WWOOB.OP_COMPL_FLAG,'N') = 'Y' THEN 0

WHEN wom.basis_type = 2

THEN DECODE(greatest(NVL(WWOOB.completed_quantity, 0)+NVL(WWOOB.scrapped_quantity, 0)+NVL(WWOOB.variance_quantity, 0),0),0,wom.extended_quantity,0)

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

CASE WHEN NVL(wom.issued_quantity, 0) > DECODE(wom.ENFORCE_INT_REQUIREMENTS,2,CEIL(wom.quantity/physical_batch.planned_start_quantity_new*(NVL(WWOOB.completed_quantity, 0)+NVL(WWOOB.scrapped_quantity, 0)+NVL(WWOOB.variance_quantity, 0)))

,1,FLOOR(wom.quantity/physical_batch.planned_start_quantity_new*(NVL(WWOOB.completed_quantity, 0)+NVL(WWOOB.scrapped_quantity, 0)+NVL(WWOOB.variance_quantity, 0))))

THEN wom.extended_quantity - DECODE(wom.ENFORCE_INT_REQUIREMENTS,2,CEIL(NVL(wom.issued_quantity,0)+(wom.quantity/physical_batch.planned_start_quantity_new * (NVL(WWOOB.cumulative_scrap_quantity, 0) + NVL(WWOOB.cumulative_variance_quantity, 0))))

,1,FLOOR(NVL(wom.issued_quantity,0)+(wom.quantity/physical_batch.planned_start_quantity_new * (NVL(WWOOB.cumulative_scrap_quantity, 0) + NVL(WWOOB.cumulative_variance_quantity, 0)))))

ELSE

wom.extended_quantity - DECODE(wom.ENFORCE_INT_REQUIREMENTS,2,CEIL(wom.quantity/physical_batch.planned_start_quantity_new * (NVL(WWOOB.cumulative_scrap_quantity, 0) + NVL(WWOOB.cumulative_variance_quantity, 0)+ NVL(WWOOB.completed_quantity, 0)+NVL(WWOOB.scrapped_quantity, 0)+NVL(WWOOB.variance_quantity, 0)))

,1,FLOOR(wom.quantity/physical_batch.planned_start_quantity_new * (NVL(WWOOB.cumulative_scrap_quantity, 0) + NVL(WWOOB.cumulative_variance_quantity, 0)+ NVL(WWOOB.completed_quantity, 0)+NVL(WWOOB.scrapped_quantity, 0)+NVL(WWOOB.variance_quantity, 0))))

END

WHEN wd.process_batch_type = 'CALCULATED' THEN 0

ELSE

CASE WHEN NVL(wom.issued_quantity, 0) > DECODE(wom.ENFORCE_INT_REQUIREMENTS,2,CEIL(wom.quantity_per_product*(NVL(WWOOB.completed_quantity, 0)+NVL(WWOOB.scrapped_quantity, 0)+NVL(WWOOB.variance_quantity, 0)))

,1,FLOOR(wom.quantity_per_product*(NVL(WWOOB.completed_quantity, 0)+NVL(WWOOB.scrapped_quantity, 0)+NVL(WWOOB.variance_quantity, 0))))

THEN wom.extended_quantity - DECODE(wom.ENFORCE_INT_REQUIREMENTS,2,CEIL(NVL(wom.issued_quantity,0)+(wom.quantity_per_product * (NVL(WWOOB.cumulative_scrap_quantity, 0) + NVL(WWOOB.cumulative_variance_quantity, 0))))

,1,FLOOR(NVL(wom.issued_quantity,0)+(wom.quantity_per_product * (NVL(WWOOB.cumulative_scrap_quantity, 0) + NVL(WWOOB.cumulative_variance_quantity, 0)))))

ELSE

wom.extended_quantity - DECODE(wom.ENFORCE_INT_REQUIREMENTS,2,CEIL(wom.quantity_per_product * (NVL(WWOOB.cumulative_scrap_quantity, 0) + NVL(WWOOB.cumulative_variance_quantity, 0)+ NVL(WWOOB.completed_quantity, 0)+NVL(WWOOB.scrapped_quantity, 0)+NVL(WWOOB.variance_quantity, 0)))

,1,FLOOR(wom.quantity_per_product * (NVL(WWOOB.cumulative_scrap_quantity, 0) + NVL(WWOOB.cumulative_variance_quantity, 0)+ NVL(WWOOB.completed_quantity, 0)+NVL(WWOOB.scrapped_quantity, 0)+NVL(WWOOB.variance_quantity, 0))))

END

END

WHEN wmb.work_method_code IN( 'DISCRETE_MANUFACTURING', 'MAINTENANCE')

THEN

DECODE(wom.basis_type,1,wom.quantity - ( NVL(wom.issued_quantity, 0) + (wom.quantity_per_product * NVL(WWOOB.cumulative_scrap_quantity, 0)))

,2,wom.quantity - greatest(NVL(wom.issued_quantity, 0),0))

WHEN wmb.work_method_code = 'PROCESS_MANUFACTURING' AND NVL(WWOOB.OP_COMPL_FLAG,'N') = 'Y' THEN 0

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

DECODE(wom.basis_type,1,wom.quantity - (wom.quantity/physical_batch.planned_start_quantity_new * NVL(WWOOB.cumulative_scrap_quantity, 0)) - (wom.quantity/physical_batch.planned_start_quantity_new * NVL(WWOOB.cumulative_variance_quantity, 0)) - GREATEST(NVL(wom.issued_quantity, 0), (wom.quantity/physical_batch.planned_start_quantity_new*(NVL(WWOOB.completed_quantity, 0)+NVL(WWOOB.scrapped_quantity, 0)+NVL(WWOOB.variance_quantity, 0))), 0)

,2,DECODE(greatest(NVL(wom.issued_quantity, 0),NVL(WWOOB.completed_quantity, 0)+NVL(WWOOB.scrapped_quantity, 0)+NVL(WWOOB.variance_quantity, 0),0),0,wom.quantity,0))

WHEN wd.process_batch_type = 'CALCULATED' THEN 0

ELSE

/*

cumulative_scrap_quantity= Batch quantity scrapped till last operation

quantity_per_product= Component required to produce one unit of work order. MFG

calculates this as (total_comp_req/WO_batch_qty)

scrapped_quantity= Batch quantity scrapped in current operation, where comp is attached

completed_quantity= Batch quantity completed in current operation, where comp is attached

Current comp demand = (total_comp_req - scrapped_qty till last operation - issued_qty)

DECODE(wom.basis_type,1,wom.quantity - (wom.quantity_per_product * NVL(WWOOB.cumulative_scrap_quantity, 0)) - (wom.quantity_per_product * NVL(WWOOB.cumulative_variance_quantity, 0)) - GREATEST(NVL(wom.issued_quantity, 0), (wom.quantity_per_product*(NVL(WWOOB.completed_quantity, 0)+NVL(WWOOB.scrapped_quantity, 0)+NVL(WWOOB.variance_quantity, 0))), 0)

,2,DECODE(greatest(NVL(wom.issued_quantity, 0),NVL(WWOOB.completed_quantity, 0)+NVL(WWOOB.scrapped_quantity, 0)+NVL(WWOOB.variance_quantity, 0),0),0,wom.quantity,0))

END using_requirement_quantity,

CASE

WHEN wom.uom_code = esib.uom_code

THEN 1

ELSE inv_convert.inv_um_convert(wom.inventory_item_id,

10,

NULL,

wom.uom_code,

esib.UOM_CODE,

NULL,

NULL)

END conversion_rate,

NVL(wom.required_date, NVL(WWOOB.actual_start_date, WWOOB.planned_start_date)) using_assembly_demand_date,

wob.work_order_number order_number,

WWOOB.operation_seq_number operation_seq_num,

wob.work_order_number wip_entity_name,

NVL(wom.issued_quantity, 0) quantity_issued,

wob.work_order_id wip_entity_id,

DECODE(wwsb.wo_system_status_code,'UNRELEASED',1,

'RELEASED',3,

'COMPLETED',4,

'CLOSED', 12,

'ON_HOLD',6,

'PENDING_APPROVAL',18,

'CANCELED', 7) wip_status_code,

DECODE (wom.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', 1003,

'NON_STANDARD', 1002,

'CORRECTIVE', 1050,

'FIELDSERVICE', 1050,

'PREVENTIVE', 1050,

1002) origination_type,

wob.inventory_item_id using_assembly_item_id,

1 demand_type,

NULL project_id,

NULL task_id,

NULL planning_group,

NULL end_item_unit_number,

NULL demand_class,

NULL mps_date_required,

wom.quantity_per_product quantity_per_assembly,

NULL asset_item_id,

NULL asset_serial_number,

wom.basis_type component_scaling_type,

wom.yield_factor component_yield_factor,

wom.material_seq_number,

NVL(WWOOB.actual_start_date, WWOOB.planned_start_date) operation_effectivity_date,

WWOOB.rowid oprowid,

wom.rowid opmrowid,

DECODE(wmb.WORK_METHOD_CODE,'PROCESS_MANUFACTURING', 2, 'MAINTENANCE', 3, 1) WORK_METHOD_CODE,

wwoob.execution_sequence,

decode(wom.contribute_to_yield_flag,'Y',1,'N',2) contribute_to_yield

FROM

WIE_WORK_ORDERS_B wob,

WIE_WO_STATUSES_B wwsb,

WIS_WORK_METHODS_B WMB,

WIE_WO_OPERATION_MATERIALS wom,

WIE_WO_OPERATIONS_B WWOOB,

MSC_PLANNED_EGP_SYSTEM_ITEMS_V esib,

SCP,

WIS_WORK_DEFINITIONS wd,

(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_id = WMB.work_method_id

AND wob.work_definition_id = wd.work_definition_id(+)

AND wmb.work_method_code IN ('DISCRETE_MANUFACTURING','PROCESS_MANUFACTURING', 'MAINTENANCE')

AND wwsb.WO_STATUS_ID = wob.WORK_ORDER_STATUS_ID

AND wwsb.WO_SYSTEM_STATUS_CODE IN (SELECT valid_status FROM job_status)

AND wob.work_order_id = WWOOB.work_order_id

AND WWOOB.wo_operation_id = wom.wo_operation_id

AND WWOOB.work_order_id = wom.work_order_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 != wob.inventory_item_id

AND wom.quantity >= 0

AND physical_batch.WORK_ORDER_ID(+) = wom.work_order_id

AND physical_batch.wo_operation_id(+) = wom.wo_operation_id