MSC_AP_RESOURCE_REQUIREMENTS_V

Details

• Schema: FUSION

• Object owner: MSC

• Object type: VIEW

Columns

Name

QUANTITY_IN_QUEUE QUANTITY_WAITING_TO_MOVE USAGE_RATE START_DATE END_DATE ORGANIZATION_ID ORGANIZATION_CODE ROUTING_SEQUENCE_ID OPERATION_SEQUENCE_ID RESOURCE_SEQ_NUM ALTERNATE_NUMBER RESOURCE_ID RESOURCE_CODE DEPARTMENT_ID DEPARTMENT_CODE REQUIRED_USAGE WIP_ENTITY_NAME ASSIGNED_UNITS ASSEMBLY_ITEM_ID OPERATION_SEQ_NUMBER HOURS_EXPENDED BASIS_TYPE SCHEDULED_COMPLETION_DATE SUPPLY_TYPE QUANTITY_COMPLETED WIP_ENTITY_ID YIELD SHUTDOWN_TYPE ACTIVITY_GROUP_ID PRINCIPAL_FLAG MINIMUM_TRANSFER_QUANTITY FIRM_FLAG SCHEDULE_FLAG PARENT_SEQ_NUM SETUP_ID ORIG_RESOURCE_SEQ_NUM GROUP_SEQUENCE_ID GROUP_SEQUENCE_NUMBER BATCH_NUMBER MAXIMUM_ASSIGNED_UNITS PLANNED_START_QUANTITY CUMULATIVE_SCRAPPED_QUANTITY ACTUAL_START_DATE WIP_JOB_TYPE ACTUAL_END_DATE OPERATION_CODE TOTAL_RESOURCE_HOURS ACTIVITY_NAME MINIMUM_CAPACITY MAXIMUM_CAPACITY BREAKABLE_ACTIVITY_FLAG STEP_QUANTITY STEP_QUANTITY_UOM OPERATION_STATUS UTILIZATION EFFICIENCY UTILIZATION_PERCENTAGE EFFICIENCY_PERCENTAGE UOM_CODE OSP_ITEM_ID OSP_OPERATION_TYPE SUPPLIER_ID SUPPLIER_SITE_ID LEAD_TIME_UOM FIXED_LEAD_TIME VARIABLE_LEAD_TIME OPROWID OPRROWID OSP_ITEM_NAME SUPPLIER_NAME SUPPLIER_SITE_CODE WORK_METHOD_CODE EXECUTION_SEQUENCE

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 wopsb.ready_quantity quantity_in_queue, NULL quantity_waiting_to_move, DECODE(greatest(0, DECODE(esi.rounding_control_type, 1, ROUND(wob.planned_start_quantity - (NVL(wopsb.completed_quantity, 0) + NVL(wopsb.scrapped_quantity, 0)), 6 ), wob.planned_start_quantity - (NVL(wopsb.completed_quantity, 0) + NVL(wopsb.scrapped_quantity, 0)) ) ), 0, 0, NVL(wor.usage_rate * uom2.conversion_rate / uom1.conversion_rate, 0) ) usage_rate, CASE WHEN wob.work_method_code = 'PROCESS_MANUFACTURING' AND NVL(wopsb.OP_COMPL_FLAG,'N') = 'Y' AND wob.actual_completion_date IS NOT NULL THEN NVL(wopsb.actual_start_date, NVL(wob.actual_start_date,wor.planned_start_date)) ELSE NVL(wor.planned_start_date, NVL(NVL(wopsb.PLANNED_START_DATE,wopsb.ACTUAL_START_DATE),NVL(wob.PLANNED_START_DATE,wob.ACTUAL_START_DATE))) END start_date, CASE WHEN wob.work_method_code = 'PROCESS_MANUFACTURING' AND NVL(wopsb.OP_COMPL_FLAG,'N') = 'Y' AND wob.actual_completion_date IS NOT NULL THEN NVL(wopsb.actual_completion_date, NVL(wob.actual_completion_date,wor.planned_completion_date)) ELSE wor.planned_completion_date END end_date, wor.organization_id, wr.organization_code, wob.work_definition_id routing_sequence_id, wopsb.wd_operation_id operation_sequence_id, wor.resource_seq_number resource_seq_num, 0 alternate_number, wor.resource_id, wr.resource_code, wor.work_center_id department_id, wr.department_code, wor.required_usage, wob.work_order_number wip_entity_name, wor.assigned_units, wob.inventory_item_id assembly_item_id, wopsb.operation_seq_number, CASE WHEN wob.work_method_code = 'PROCESS_MANUFACTURING' AND wd.process_batch_type = 'CALCULATED' AND physical_batch.planned_start_quantity_new > 0 THEN GREATEST(NVL(wor.ACTUAL_RESOURCE_USAGE, 0), decode(wor.basis_type,1,(wor.required_usage/physical_batch.planned_start_quantity_new * uom2.conversion_rate / uom1.conversion_rate*(NVL(wopsb.completed_quantity, 0)+NVL(wopsb.scrapped_quantity, 0)+NVL(wopsb.variance_quantity,0)+NVL(wopsb.cumulative_variance_quantity,0))), decode(NVL(wopsb.completed_quantity, 0)+NVL(wopsb.scrapped_quantity, 0)+NVL(wopsb.variance_quantity,0),0,0,wor.usage_rate * uom2.conversion_rate / uom1.conversion_rate)),0) WHEN wob.work_method_code = 'PROCESS_MANUFACTURING' AND wd.process_batch_type = 'CALCULATED' THEN 0 WHEN wob.work_method_code = 'PROCESS_MANUFACTURING' THEN GREATEST(NVL(wor.ACTUAL_RESOURCE_USAGE, 0), decode(wor.basis_type,1,(wor.usage_rate * uom2.conversion_rate / uom1.conversion_rate*(NVL(wopsb.completed_quantity, 0)+NVL(wopsb.scrapped_quantity, 0)+NVL(wopsb.variance_quantity,0)+NVL(wopsb.cumulative_variance_quantity,0))), decode(NVL(wopsb.completed_quantity, 0)+NVL(wopsb.scrapped_quantity, 0)+NVL(wopsb.variance_quantity,0),0,0,wor.usage_rate * uom2.conversion_rate / uom1.conversion_rate)),0) ELSE NVL(wor.ACTUAL_RESOURCE_USAGE * uom2.conversion_rate / uom1.conversion_rate,0) END hours_expended, to_number(wor.basis_type) basis_type, wor.planned_completion_date scheduled_completion_date, DECODE(wob.work_order_type, 'STANDARD', 1, 'NON_STANDARD', 3,3) supply_type, wopsb.completed_quantity quantity_completed, wob.work_order_id wip_entity_id, nvl(wopsb.OP_YIELD_FACTOR,1) yield, NULL shutdown_type, NULL activity_group_id, DECODE (wor.principal_flag,'Y', 1 , 'N', 2) principal_flag, NULL minimum_transfer_quantity, NULL firm_flag, DECODE(wor.scheduled_flag, 'YES', 1, 'NO', 2, 'PRIOR', 3, 'NEXT', 4) schedule_flag, NULL parent_seq_num, NULL setup_id, wor.resource_seq_number orig_resource_seq_num, NULL group_sequence_id, NULL group_sequence_number, NULL batch_number, NULL maximum_assigned_units, CASE WHEN wob.work_method_code = 'PROCESS_MANUFACTURING' AND wd.process_batch_type = 'CALCULATED' AND NOT(physical_batch.planned_start_quantity_new > 0) THEN 0 ELSE Decode(wor.basis_type, 1, NVL(wor.required_usage * uom2.conversion_rate / uom1.conversion_rate, 0), NVL(wor.usage_rate * uom2.conversion_rate / uom1.conversion_rate, 0)) END planned_start_quantity, CASE WHEN wob.work_method_code = 'PROCESS_MANUFACTURING' AND wd.process_batch_type = 'CALCULATED' AND physical_batch.planned_start_quantity_new > 0 THEN Decode(wor.basis_type, 1, (wopsb.cumulative_scrap_quantity)*(NVL(wor.required_usage/physical_batch.planned_start_quantity_new * uom2.conversion_rate / uom1.conversion_rate, 0)),0) WHEN wob.work_method_code = 'PROCESS_MANUFACTURING' AND wd.process_batch_type = 'CALCULATED' THEN 0 ELSE Decode(wor.basis_type, 1, (wopsb.cumulative_scrap_quantity)*(NVL(wor.usage_rate * uom2.conversion_rate / uom1.conversion_rate, 0)),0) END cumulative_scrapped_quantity, wob.actual_start_date, DECODE (wob.WORK_ORDER_TYPE, 'STANDARD', 1, 'NON_STANDARD', 2,2) WIP_JOB_TYPE, NULL actual_end_date, NULL operation_code, NULL total_resource_hours, NULL activity_name, NULL minimum_capacity, NULL maximum_capacity, NULL breakable_activity_flag, NULL step_quantity, NULL step_quantity_uom, NULL operation_status, DECODE(wr.utilization, NULL, 100, 0, 100, wr.utilization) utilization, DECODE(wr.efficiency, NULL, 100, 0, 100, wr.efficiency) efficiency, DECODE(wr.utilization, NULL, 1, 0, 1, wr.utilization/100) utilization_percentage, DECODE(wr.efficiency, NULL, 1, 0, 1, wr.efficiency /100) efficiency_percentage, uom1.uom_code, NULL OSP_ITEM_ID, 1 OSP_OPERATION_TYPE, NULL SUPPLIER_ID, NULL SUPPLIER_SITE_ID, NULL LEAD_TIME_UOM, NULL FIXED_LEAD_TIME, NULL VARIABLE_LEAD_TIME, wopsb.row_id oprowid, wor.rowid oprrowid, NULL osp_item_name, NULL supplier_name, NULL supplier_site_code, DECODE(wob.WORK_METHOD_CODE,'PROCESS_MANUFACTURING', 2, 1) WORK_METHOD_CODE, wopsb.execution_sequence FROM WIE_WORK_ORDERS_V wob, WIE_WO_OPERATIONS_V wopsb, WIE_WO_OPERATION_RESOURCES_V wor, msc_ap_resources_v wr, inv_uom_conversions uom1, inv_uom_conversions uom2, msc_planned_egp_system_items esi, 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 wopsb.operation_type = 'IN_HOUSE' and wob.work_order_id = wopsb.work_order_id AND wob.organization_id = esi.organization_id AND wob.inventory_item_id = esi.inventory_item_id AND wob.system_status_code in (SELECT valid_status FROM job_status) AND wob.work_definition_id = wd.work_definition_id(+) AND wopsb.wo_operation_id = wor.wo_operation_id AND wor.work_center_id = wr.department_id AND wor.resource_id = wr.resource_id AND wor.scheduled_flag <> 'NO' AND uom2.uom_code(+) = wor.uom_code AND uom1.inventory_item_id(+) = 0 AND uom2.inventory_item_id(+) = 0 AND uom1.uom_class(+) = uom2.uom_class AND wor.resource_id IS NOT NULL and wob.work_method_code in ('DISCRETE_MANUFACTURING','PROCESS_MANUFACTURING') AND physical_batch.WORK_ORDER_ID(+) = wopsb.work_order_id AND physical_batch.wo_operation_id(+) = wopsb.wo_operation_id UNION ALL SELECT wopsb.ready_quantity quantity_in_queue, NULL quantity_waiting_to_move, DECODE(greatest(0, DECODE(esi_osp.rounding_control_type, 1, ROUND(wob.planned_start_quantity - (NVL(wopsb.completed_quantity, 0) + NVL(wopsb.scrapped_quantity, 0)), 6 ), wob.planned_start_quantity - (NVL(wopsb.completed_quantity, 0) + NVL(wopsb.scrapped_quantity, 0)) ) ), 0, 0, NVL(wor.usage_rate * uom2.conversion_rate / uom1.conversion_rate, 0) ) usage_rate, NVL(wor.planned_start_date,NVL(NVL(wopsb.PLANNED_START_DATE,wopsb.ACTUAL_START_DATE),NVL(wob.PLANNED_START_DATE,wob.ACTUAL_START_DATE))) start_date, NVL(wor.planned_completion_date,NVL(NVL(wopsb.planned_completion_date,wopsb.ACTUAL_completion_date),NVL(wob.planned_completion_date,wob.ACTUAL_completion_date))) end_date, wor.organization_id, (select organization_code from inv_org_parameters org where org.organization_id = wopsb.organization_id) organization_code, wob.work_definition_id routing_sequence_id, wopsb.wd_operation_id operation_sequence_id, wor.resource_seq_number resource_seq_num, 0 alternate_number, wor.resource_id, wr.resource_code, wor.work_center_id department_id, wr.department_code, wor.required_usage, wob.work_order_number wip_entity_name, wor.assigned_units, wob.inventory_item_id assembly_item_id, wopsb.operation_seq_number, CASE WHEN wob.work_method_code = 'PROCESS_MANUFACTURING' THEN GREATEST(NVL(wor.ACTUAL_RESOURCE_USAGE, 0), decode(wor.basis_type,1,(wor.usage_rate * uom2.conversion_rate / uom1.conversion_rate*(NVL(wopsb.completed_quantity, 0)+NVL(wopsb.scrapped_quantity, 0)+NVL(wopsb.variance_quantity,0)+NVL(wopsb.cumulative_variance_quantity,0))), decode(NVL(wopsb.completed_quantity, 0)+NVL(wopsb.scrapped_quantity, 0)+NVL(wopsb.variance_quantity,0),0,0,wor.usage_rate * uom2.conversion_rate / uom1.conversion_rate)),0) ELSE NVL(wor.ACTUAL_RESOURCE_USAGE * uom2.conversion_rate / uom1.conversion_rate,0) END hours_expended, to_number(wor.basis_type) basis_type, NVL(wor.planned_completion_date,NVL(NVL(wopsb.planned_completion_date,wopsb.ACTUAL_completion_date),NVL(wob.planned_completion_date,wob.ACTUAL_completion_date))) scheduled_completion_date, DECODE(wob.work_order_type, 'STANDARD', 1, 'NON_STANDARD', 3,3) supply_type, wopsb.completed_quantity quantity_completed, wob.work_order_id wip_entity_id, nvl(wopsb.OP_YIELD_FACTOR,1) yield, NULL shutdown_type, NULL activity_group_id, DECODE (wor.principal_flag,'Y', 1 , 'N', 2) principal_flag, NULL minimum_transfer_quantity, NULL firm_flag, DECODE(wor.scheduled_flag, 'YES', 1, 'NO', 2, 'PRIOR', 3, 'NEXT', 4) schedule_flag, NULL parent_seq_num, NULL setup_id, wor.resource_seq_number orig_resource_seq_num, NULL group_sequence_id, NULL group_sequence_number, NULL batch_number, NULL maximum_assigned_units, Decode(wor.basis_type, 1, NVL(wor.required_usage * uom2.conversion_rate / uom1.conversion_rate, 0), NVL(wor.usage_rate * uom2.conversion_rate / uom1.conversion_rate, 0)) planned_start_quantity, Decode(wor.basis_type, 1, (wopsb.cumulative_scrap_quantity)*(NVL(wor.usage_rate * uom2.conversion_rate / uom1.conversion_rate, 0)),0) cumulative_scrapped_quantity, wob.actual_start_date, DECODE (wob.WORK_ORDER_TYPE, 'STANDARD', 1, 'NON_STANDARD', 2,2) WIP_JOB_TYPE, NULL actual_end_date, NULL operation_code, NULL total_resource_hours, NULL activity_name, NULL minimum_capacity, NULL maximum_capacity, NULL breakable_activity_flag, NULL step_quantity, NULL step_quantity_uom, NULL operation_status, DECODE(wr.utilization, NULL, 100, 0, 100, wr.utilization) utilization, DECODE(wr.efficiency, NULL, 100, 0, 100, wr.efficiency) efficiency, DECODE(wr.utilization, NULL, 1, 0, 1, wr.utilization/100) utilization_percentage, DECODE(wr.efficiency, NULL, 1, 0, 1, wr.efficiency /100) efficiency_percentage, uom1.uom_code, wopsb.OSP_ITEM_ID, decode(wopsb.OPERATION_TYPE, 'SUPPLIER',2,1) OSP_OPERATION_TYPE, wopsb.SUPPLIER_ID, wopsb.SUPPLIER_SITE_ID, wopsb.LEAD_TIME_UOM, wopsb.FIXED_LEAD_TIME, wopsb.VARIABLE_LEAD_TIME, wopsb.row_id oprowid, wor.rowid oprrowid, esi_osp.item_number osp_item_name, hp.PARTY_NAME supplier_name, pvsa.VENDOR_SITE_CODE supplier_site_code, DECODE(wob.WORK_METHOD_CODE,'PROCESS_MANUFACTURING', 2, 1) WORK_METHOD_CODE, wopsb.execution_Sequence FROM WIE_WORK_ORDERS_V wob, WIE_WO_OPERATIONS_V wopsb, WIE_WO_OPERATION_RESOURCES_V wor, msc_ap_resources_v wr, inv_uom_conversions uom1, inv_uom_conversions uom2, msc_planned_egp_system_items esi_osp, poz_suppliers v, hz_parties hp, POZ_SUPPLIER_SITES_ALL_M pvsa WHERE wopsb.operation_type = 'SUPPLIER' AND wob.work_order_id = wopsb.work_order_id AND wob.system_status_code in (SELECT valid_status FROM job_status) AND wopsb.wo_operation_id = wor.wo_operation_id(+) AND wor.work_center_id = wr.department_id(+) AND wor.resource_id = wr.resource_id(+) AND wor.scheduled_flag(+) <> 'NO' AND uom2.uom_code(+) = wor.uom_code AND uom1.inventory_item_id(+) = 0 AND uom2.inventory_item_id(+) = 0 AND uom1.uom_class(+) = uom2.uom_class AND wopsb.osp_item_id = esi_osp.inventory_item_id AND wopsb.organization_id = esi_osp.organization_id AND wopsb.SUPPLIER_ID = v.vendor_id AND hp.party_id = v.party_id AND wopsb.supplier_site_id = pvsa.vendor_site_id AND v.vendor_id = pvsa.vendor_id and wob.work_method_code in ('DISCRETE_MANUFACTURING','PROCESS_MANUFACTURING')