Reports2017-11-18T12:27:27+00:00

MRP Exceptions

Description
Categories: Enginatics, Manufacturing
MRP planning exception messages

select
medv.organization_code,
medv.compile_designator plan,
medv.item_segments item,
msiv.description item_description,
xxen_util.meaning(msiv.planning_make_buy_code,'MTL_PLANNING_MAKE_BUY',700) make_or_buy,
ppx.full_name default_buyer,
msiv.planner_code planner,
mpl.description planner_description,
medv.exception_type_text exception_message,
medv.project_number,
medv.task_number,
medv.to_project_number,
medv.to_task_number,
medv.due_date,
medv.from_date,
medv.to_date,
medv.days_compressed,
medv.quantity,
medv.lot_number,
medv.order_number,
medv.supply_type,
medv.end_item_segments end_item,
medv.end_order_number,
medv.planning_group,
medv.department_line_code,
medv.resource_code,
medv.utilization_rate,
medv.exception_type
from
mrp_exception_details_v medv,
&xrrpv_table
mtl_system_items_vl msiv,
per_people_x ppx,
mtl_planners mpl
where
1=1 and
(
medv.exception_type<>19 or not exists
(
select
null
from
mrp_exception_details_v medv2
where
medv.exception_type=medv2.exception_type and
medv.compile_designator=medv2.compile_designator and
medv.organization_id=medv2.organization_id and
medv.inventory_item_id=medv2.inventory_item_id and
medv.to_project_number=medv2.project_number and
medv.to_task_number=medv2.task_number
)
) and
medv.exception_type<>5 and
medv.exception_type in (1,2,3,4,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29) and
medv.organization_id=msiv.organization_id(+) and
medv.inventory_item_id=msiv.inventory_item_id(+) and
msiv.buyer_id=ppx.person_id(+) and
msiv.planner_code=mpl.planner_code(+) and
msiv.organization_id=mpl.organization_id(+)

Parameter Name SQL text Validation
Item
msiv.concatenated_segments=:item
LOV
Planner Code
msiv.planner_code=:planner_code
LOV
Buyer
msiv.buyer_id in
(
select
ppx.person_id
from
per_people_x ppx
where
replace(ppx.full_name,',')=:buyer and
ppx.current_employee_flag='Y'
)
LOV
Exception
medv.exception_type_text=:exception
LOV
organization_id|inventory_item_id
xrrpv.organization_id=medv.organization_id and
xrrpv.compile_designator=medv.compile_designator and
xrrpv.inventory_item_id=medv.inventory_item_id
Organization Code
medv.organization_id=:organization_id
LOV Oracle
Plan
medv.compile_designator=:plan
LOV
organization_id|inventory_item_id
(
select distinct
dbms_lob.substr(xrrpv.value,instr(xrrpv.value,'|')-1) organization_id,
xxinv_supply_demand.default_compile_designator(dbms_lob.substr(xrrpv.value,instr(xrrpv.value,'|')-1)) compile_designator,
dbms_lob.substr(xrrpv.value,20,instr(xrrpv.value,'|')+1) inventory_item_id
from
xxen_report_run_param_values xrrpv
where
xrrpv.run_id=:run_id
) xrrpv,