CRP Resource Plan

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Detail report for each resource code, showing the resource date, hours, work start and end date, type of work scheduled, and the quantity scheduled.
Run CRP Resource Plan and other Oracle EBS reports with Blitz Report™ on our demo environment
select distinct
y.order_number,
y.department_code,
y.department_description,
y.resource_code,
y.resource_description,
y.resource_date,
case when nvl(wro.row_number,1)=1 then
sum(y.resource_hours) over (partition by y.order_number,y.department_code,y.resource_code,y.resource_date,y.assembly,y.job_number,y.operation_seq_num,y.supply_type &partition_by)
end resource_hours,
min(y.start_date) over (partition by y.order_number,y.department_code,y.resource_code,y.resource_date,y.assembly,y.job_number,y.operation_seq_num,y.supply_type &partition_by) start_date,
max(y.end_date) over (partition by y.order_number,y.department_code,y.resource_code,y.resource_date,y.assembly,y.job_number,y.operation_seq_num,y.supply_type &partition_by) end_date,
y.supply_type,
y.assembly,
y.assembly_description,
y.assembly_item_type,
y.job_number,
y.job_status,
y.schedule_group,
max(y.daily_rate) over (partition by y.order_number,y.department_code,y.resource_code,y.resource_date,y.assembly,y.job_number,y.operation_seq_num,y.supply_type &partition_by) daily_rate,
max(y.schedule_quantity) over (partition by y.order_number,y.department_code,y.resource_code,y.resource_date,y.assembly,y.job_number,y.operation_seq_num,y.supply_type &partition_by) schedule_quantity,
min(y.schedule_date) over (partition by y.order_number,y.department_code,y.resource_code,y.resource_date,y.assembly,y.job_number,y.operation_seq_num,y.supply_type &partition_by) schedule_date,
max(y.firm) over (partition by y.order_number,y.department_code,y.resource_code,y.resource_date,y.assembly,y.job_number,y.operation_seq_num,y.supply_type &partition_by) firm,
y.operation_seq_num operation_sequence,
&resource_sequence
wo.description operation_description,
xxen_util.client_time(wo.date_last_moved) date_last_moved,
y.start_quantity,
wo.quantity_in_queue in_queue,
wo.quantity_running running,
wo.quantity_waiting_to_move to_move,
wo.quantity_rejected rejected,
wo.quantity_scrapped scrapped,
wo.quantity_completed completed,
(y.start_quantity-wo.quantity_completed+wo.quantity_scrapped) remaining_open,
xxen_util.client_time(wo.first_unit_start_date) first_unit_start_date,
xxen_util.client_time(wo.last_unit_completion_date) last_unit_completion_date,
pha.segment1 po_number,
xxen_util.client_time(plla.need_by_date) need_by_date,
xxen_util.client_time(plla.promised_date) promised_date,
&component_columns
y.plan,
y.organization_code,
y.resource_id,
y.assembly_item_id,
y.wip_entity_id,
&transaction_id
y.organization_id
from
(
select
x.order_number,
bd.department_code,
bd.description department_description,
br.resource_code,
br.description resource_description,
(select xxen_util.calendar_date_offset(crp.resource_date,mp.calendar_code,rowgen.column_value-1) from dual) resource_date,
crp.daily_resource_hours resource_hours,
crp.resource_date start_date,
crp.resource_end_date end_date,
crp.resource_hours total_hours,
xxen_util.meaning(mr.order_type,'MRP_ORDER_TYPE',700) supply_type,
msiv.concatenated_segments assembly,
msiv.description assembly_description,
xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) assembly_item_type,
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,
crp.resource_seq_num,
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,
crp.resource_id,
crp.assembly_item_id,
we.wip_entity_id,
we.organization_id,
crp.transaction_id,
rowgen.column_value date_sequence
from
crp_resource_plan crp,
table(xxen_util.rowgen(crp.resource_hours/xxen_util.zero_to_null(crp.daily_resource_hours))) rowgen,
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
mtl_parameters mp,
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
mp.organization_id=mfp.organization_id and
mp.organization_id=mfp0.organization_id 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,
(select row_number() over (partition by wro.wip_entity_id, wro.operation_seq_num, wro.organization_id order by wro.inventory_item_id) row_number, wro.* from wip_requirement_operations wro where '&show_wro'='Y' and wro.wip_supply_type<>6) wro,
mtl_system_items_vl msiv2,
mtl_units_of_measure_tl muot,
mtl_item_locations_kfv milk,
(
select
sum(moqd.primary_transaction_quantity) on_hand,
moqd.organization_id,
moqd.inventory_item_id
from
mtl_onhand_quantities_detail moqd,
mtl_secondary_inventories msi
where
moqd.subinventory_code=msi.secondary_inventory_name and
moqd.organization_id=msi.organization_id and
msi.availability_type=1
group by
moqd.organization_id,
moqd.inventory_item_id
) moqd,
(
select distinct
pda.wip_entity_id,
pda.wip_operation_seq_num,
pda.destination_organization_id,
max(pda.line_location_id) keep (dense_rank last order by pda.po_distribution_id) over (partition by pda.wip_entity_id, pda.wip_operation_seq_num, pda.destination_organization_id) line_location_id,
max(pda.po_line_id) keep (dense_rank last order by pda.po_distribution_id) over (partition by pda.wip_entity_id, pda.wip_operation_seq_num, pda.destination_organization_id) po_line_id,
max(pda.po_header_id) keep (dense_rank last order by pda.po_distribution_id) over (partition by pda.wip_entity_id, pda.wip_operation_seq_num, pda.destination_organization_id) po_header_id
from
po_distributions_all pda
) pda,
po_lines_all pla,
po_line_locations_all plla,
po_headers_all pha
where
3=3 and
y.wip_entity_id=wo.wip_entity_id(+) and
y.operation_seq_num=wo.operation_seq_num(+) and
y.organization_id=wo.organization_id(+) and
y.wip_entity_id=wro.wip_entity_id(+) and
y.operation_seq_num=wro.operation_seq_num(+) and
y.organization_id=wro.organization_id(+) and
wro.organization_id=msiv2.organization_id(+) and
wro.inventory_item_id=msiv2.inventory_item_id(+) and
msiv2.primary_uom_code=muot.uom_code(+) and
muot.language(+)=userenv('lang') and
wro.supply_locator_id=milk.inventory_location_id(+) and
wro.organization_id=milk.organization_id(+) and
wro.inventory_item_id=moqd.inventory_item_id(+) and
wro.organization_id=moqd.organization_id(+) and
y.wip_entity_id=pda.wip_entity_id(+) and
y.operation_seq_num=pda.wip_operation_seq_num(+) and
y.organization_id=pda.destination_organization_id(+) and
pda.line_location_id=plla.line_location_id(+) and
pda.po_line_id=pla.po_line_id(+) and
pda.po_header_id=pha.po_header_id(+)
order by
y.organization_code,
y.plan,
y.department_code,
y.resource_code,
y.resource_date,
y.operation_seq_num
&component_order_by
Parameter Name SQL text Validation
Department 
bd.department_code=:department_code
 LOV
Resource Code 
br.resource_code=:resource_code
 LOV
Resource Description 
br.description=:resource_description
 LOV
Sales Order 
crp.source_transaction_id in (
select
mfp.transaction_id
from
mrp_full_pegging mfp,
mrp_full_pegging mfp0,
mrp_gross_requirements mgr,
oe_order_lines_all oola,
oe_order_headers_all ooha
where
ooha.order_number=:order_number and
mfp.organization_id=mfp0.organization_id and
mfp.compile_designator=mfp0.compile_designator and
mfp.end_pegging_id=mfp0.pegging_id and
mfp0.organization_id=:organization_id and
mfp0.compile_designator=:compile_designator 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
)
 LOV
Planner 
msiv.planner_code=:planner_code
 LOV
Job 
crp.source_transaction_id in (
select
mr.transaction_id
from
wip_entities we,
mrp_recommendations mr
where
we.wip_entity_name=:job_name and
we.wip_entity_id=mr.disposition_id and
mr.order_type in (3,7,14,15,27,28)
)
 LOV
Assembly 
msiv.concatenated_segments like :assembly
 LOV
Component 
(crp.source_transaction_id,crp.operation_seq_num) in (
select
mr.transaction_id,
wro.operation_seq_num
from
mtl_system_items_vl msiv2,
wip_requirement_operations wro,
mrp_recommendations mr
where
msiv2.concatenated_segments=:component and
msiv2.organization_id=wro.organization_id and
msiv2.inventory_item_id=wro.inventory_item_id and
wro.wip_entity_id=mr.disposition_id and
wro.organization_id=mr.organization_id and
mr.order_type in (3,7,14,15,27,28)
)
 LOV
Show Active only 
(wo.quantity_in_queue>0 or wo.quantity_running>0 or wo.quantity_waiting_to_move>0)
 LOV
Expand Lot Time 
,y.resource_seq_num, y.transaction_id
 LOV
Show Components 
Y
 LOV Oracle
Plan 
crp.designator=:compile_designator
 LOV
Organization Code 
mp.organization_code=:organization_code
 LOV
Download
 
Blitz Report™

Blitz Report™ provides multiple benefits: