Reports2017-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 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.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,
&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
bcd1.calendar_date
from
bom_calendar_dates bcd0,
bom_calendar_dates bcd1
where
crp.resource_date=bcd0.calendar_date and
bcd0.seq_num+rowgen.column_value-1=bcd1.seq_num and
mp.calendar_code=bcd0.calendar_code and
mp.calendar_code=bcd1.calendar_code and
bcd0.exception_set_id=-1 and
bcd1.exception_set_id=-1
) 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,
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
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,
(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 '&enable_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
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(+)
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
Expand Lot Time
,y.resource_seq_num, y.transaction_id
LOV
Show Active Only
(wo.quantity_in_queue>0 or wo.quantity_running>0 or wo.quantity_waiting_to_move>0)
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
Assembly
msiv.concatenated_segments like :assembly
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
Resource Code
br.resource_code=:resource_code
LOV
Department
bd.department_code=:department_code
LOV
Resource Description
br.description=:resource_description
LOV
Organization Code
mfp.organization_id=:organization_id and
mfp0.organization_id=:organization_id
Plan
mfp.compile_designator=:compile_designator and
mfp0.compile_designator=:compile_designator
Show Components
,wro.inventory_item_id
Show Components
,resource_hours
Show Components
msiv2.concatenated_segments component,
msiv2.description component_description,
wro.required_quantity,
wro.quantity_issued,
greatest(wro.required_quantity-wro.quantity_issued,0) quantity_open,
moqd.on_hand,
wro.date_required,
xxen_util.meaning(wro.wip_supply_type,'WIP_SUPPLY',700) supply_type,
wro.supply_subinventory supply_subinv,
milk.concatenated_segments supply_locator,
xxen_util.meaning(msiv2.planning_make_buy_code,'MTL_PLANNING_MAKE_BUY',700) make_buy,
Expand Lot Time
y.transaction_id,
Expand Lot Time
y.resource_seq_num resource_sequence,
Organization Code
crp.organization_id=:organization_id
LOV Oracle
Plan
crp.designator=:compile_designator
LOV
Show Components
Y
LOV Oracle
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