Reports 2017-11-18T12:27:27+00:00

CRP Resource Plan

Description
Categories: Enginatics, Manufacturing
Capacity plans resource load hours including load source e.g. planned order or WIP job number, date and required hours.

select
y.order_number,
y.department_code,
y.resource_code,
y.resource_description,
y.resource_date,
y.resource_hours,
y.supply_type,
y.assembly,
y.assembly_description,
y.job_number,
y.job_status,
y.schedule_group,
y.daily_rate,
y.schedule_quantity,
y.schedule_date,
y.firm,
y.operation_sequence,
wo.description operation_description,
bd.department_code department,
bd.description department_desc,
y.start_quantity,
wo.quantity_completed completed,
wo.quantity_in_queue in_queue,
wo.quantity_waiting_to_move to_move,
wo.quantity_scrapped scrapped,
(y.start_quantity-wo.quantity_completed+wo.quantity_scrapped) remaining_open,
wo.first_unit_start_date,
wo.last_unit_completion_date,
y.plan,
y.organization_code,
y.wip_entity_id
from
(
select
x.order_number,
bd.department_code,
br.resource_code,
br.description resource_description,
crp.resource_date,
crp.resource_hours,
xxen_util.meaning(mr.order_type,'MRP_ORDER_TYPE',700) supply_type,
msiv.concatenated_segments assembly,
msiv.description assembly_description,
we.wip_entity_name job_number,
xxen_util.meaning(wdj.status_type,'WIP_JOB_STATUS',700) job_status,
wsg.schedule_group_name schedule_group,
wdj.start_quantity,
crp.operation_seq_num operation_sequence,
mr.daily_rate,
mr.new_order_quantity schedule_quantity,
mr.new_schedule_date schedule_date,
xxen_util.meaning(mr.firm_planned_type,'SYS_YES_NO',700) firm,
crp.designator plan,
mp.organization_code,
we.wip_entity_id,
we.organization_id
from
crp_resource_plan crp,
bom_departments bd,
bom_resources br,
mtl_parameters mp,
mtl_system_items_vl msiv,
mrp_recommendations mr,
wip_entities we,
wip_discrete_jobs wdj,
wip_schedule_groups wsg,
(
select distinct
mfp.transaction_id,
listagg(mfp.order_number,', ') within group (order by mfp.order_number) over (partition by mfp.transaction_id) order_number
from
(
select distinct
mfp.transaction_id,
ooha.order_number,
count(distinct ooha.order_number) over (partition by mfp.transaction_id) order_count
from
mrp_full_pegging mfp,
mrp_full_pegging mfp0,
mrp_gross_requirements mgr,
oe_order_lines_all oola,
oe_order_headers_all ooha
where
2=2 and
mfp.end_pegging_id=mfp0.pegging_id and
mfp0.demand_id=mgr.demand_id and
mgr.origination_type=6 and
mgr.reservation_id=oola.line_id and
oola.header_id=ooha.header_id
) mfp
where
mfp.order_count<440
) x
where
1=1 and
crp.repetitive_type=1 and
crp.department_id=bd.department_id and
crp.resource_id=br.resource_id and
crp.organization_id=mp.organization_id and
crp.organization_id=msiv.organization_id and
crp.assembly_item_id=msiv.inventory_item_id and
crp.source_transaction_id=mr.transaction_id and
case when mr.order_type in (3,7,14,15,27,28) then mr.disposition_id end=we.wip_entity_id(+) and
we.wip_entity_id=wdj.wip_entity_id(+) and
we.organization_id=wdj.organization_id(+) and
wdj.schedule_group_id=wsg.schedule_group_id(+) and
crp.source_transaction_id=x.transaction_id(+)
) y,
wip_operations wo,
bom_departments bd
where
y.wip_entity_id=wo.wip_entity_id(+) and
y.operation_sequence=wo.operation_seq_num(+) and
y.organization_id=wo.organization_id(+) and
wo.department_id=bd.department_id(+)
order by
y.organization_code,
y.plan,
y.department_code,
y.resource_code,
y.resource_date,
y.resource_hours desc

Parameter Name SQL text Validation
Department
bd.department_code=:department_code
LOV
Resource
br.resource_code=:resource_code
LOV
Plan
crp.designator=:compile_designator
LOV
Organization Code
crp.organization_id=:organization_id
LOV Oracle
Plan
mfp.compile_designator=:compile_designator and
mfp0.compile_designator=:compile_designator
Organization Code
mfp.organization_id=:organization_id and
mfp0.organization_id=:organization_id