Charge x Capacity with Resource Efficiency Restriction

In this post I will show one of the functionalities with MRP and ASCP. Inside a shop floor we have several processes going on at the same time, and from a planning point of view, one of the main problems is charge and capacity control. Within the various configurations that are required, it is possible to measure the efficiency of a resource in a department, on machine or person. In the register below you can determine an efficiency of a productive resource.

This efficiency is considered in the charge calculations required in MRP and ASCP, after launch the plans the information is available on the workbench.

This calculation can be seen in the column above. One column shows the value of 100% efficiency and the other shows 86% more considering that this feature will have a lower yield. Another possibility to verify this information is by the horizontal plane of a resource.

However this can not be enough, to have a more consistent analysis can be developed graphs that more agilely show the problems in the most critical resources. Below is a query I put together that I use to pass on to the business area to assemble charts for analysis.

Query of charge

WITH w_carga AS (SELECT organization_id,
 resource_id,
 department_id,
 assembly_item_id,
 order_type,
 standard_operation_code,
 resource_date start_date,
 resource_end_date end_date,
 plan_Id,
 SUM(daily_resource_hours) carga
 FROM msc_resource_requirements_v
 WHERE plan_Id <> -1
 AND NVL (resource_hours, 0) <> 0
 AND ( (NVL (daily_resource_hours, resource_hours) > 0 AND record_source = 'ATP')
 OR NVL (record_source, 'Planning') <> 'ATP')
 AND NVL (view_type, 'RESOURCE') IN ('RESOURCE', 'INSTANCE')
 AND resource_view = 1
 GROUP BY organization_id,
 resource_id,
 department_id,
 assembly_item_id,
 order_type,
 standard_operation_code,
 resource_date,
 resource_end_date,
 plan_Id)
 SELECT mib.item_name item,
 planos.compile_designator planos,
 mcb.segment1 programa,
 mrr.resource_id,
 rec.resource_code recurso,
 mrr.department_id,
 rec.department_code departamento,
 rec.department_class classe_departamento,
 rec.resource_group_name grupo_recursos,
 mrr.carga,
 mrr.start_date,
 mrr.end_date,
 mib.plan_id,
 mib.organization_id,
 mib.organization_code,
 order_type origem,
 DECODE(br.resource_type,1,'Machine',2,'Person',4,'Miscellaneous',5,'Amount',3,'Currency') tipo_recurso,
 calendar_code calendario
 FROM w_carga mrr,
 msc_department_resources rec,
 msc_system_items mib,
 mtl_item_categories mic,
 mtl_categories_b mcb,
 msc_plans planos,
 bom_resources br,
 bom_standard_operations bso,
 mtl_parameters mp
 WHERE mib.plan_id = planos.plan_id
 AND rec.department_id = mrr.department_id
 AND rec.resource_id = mrr.resource_id
 AND rec.plan_id = mrr.plan_id
 AND planos.plan_id <> -1
 AND rec.plan_id = mib.plan_id
 AND mib.inventory_item_id = mrr.assembly_item_id
 AND mib.organization_id = mrr.organization_id
 AND mib.sr_inventory_item_id = mic.inventory_item_id(+)
 AND mib.organization_id = mic.organization_id(+)
 AND mic.category_set_id(+) = 83
 AND mcb.category_id(+) = mic.category_id
 AND mrr.organization_id = br.organization_id
 AND mrr.resource_id / 2 = br.resource_id
 AND bso.operation_code(+) = mrr.standard_operation_code
 AND bso.organization_id(+) = mrr.organization_id
 AND mp.organization_id = mrr.organization_id

Query of capacity

SELECT SUM ( (to_time - from_time) / 3600 * net.capacity_units) capacidade,
 net.department_id,
 net.resource_id,
 shift_date data,
 plan_id,
 mp.organization_id,
 a.instance_code || ':' || mp.organization_code organizacao,
 bdr.capacity_units unidades_disponiveis
 FROM msc_net_resource_avail net,
 msc_apps_instances a,
 msc_instance_orgs b,
 mtl_parameters mp,
 bom_department_resources bdr
 WHERE mp.organization_id = b.organization_id
 AND a.instance_id = b.sr_instance_id
 AND net.organization_id = mp.organization_id
 AND net.resource_id / 2 = bdr.resource_id
 AND net.department_id / 2 = bdr.department_Id
 AND parent_id <> -1
 AND plan_id <> -1
 GROUP BY net.department_id,
 net.resource_id,
 shift_date,
 plan_id,
 mp.organization_id,
 instance_code || ':' || mp.organization_code,
 bdr.capacity_units

With This information, the analysis can be very easy

I holp this help.