MRP Reorder Point

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Reorder Point Report
Application: Master Scheduling/MRP
Source: Reorder Point Report (XML)
Short Name: MRPRPROP_XML
DB package: MRP_MRPRPROP_XMLP_PKG
Run MRP Reorder Point and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT 	null C_item,
	null C_category,
	sys.planner_code C_planner,
                  emp.full_name C_buyer,
                  &P_DYNAMIC_ABC C_abc_class_name,
	sys.description C_description,
	nvl(sys.preprocessing_lead_time,0) + nvl(sys.full_lead_time,0) + nvl(sys.postprocessing_lead_time,0) C_ord_lead_time,
                  nvl(sys.preprocessing_lead_time,0) + nvl(sys.full_lead_time,0) C_pur_lead_time,
	sys.inventory_item_id C_item_id,
                  sys.planning_make_buy_code C_make_buy,
                  sys.fixed_lead_time C_fixed_lead_time,
                  sys.variable_lead_time C_variable_lead_time,
                  sys.primary_uom_code C_primary_uom,
                  sys.fixed_lot_multiplier C_fix_lot_mult,
                  sys.minimum_order_quantity C_min_ord_qty,
                  sys.maximum_order_quantity C_max_ord_qty,
                  param.ap_accrual_account C_accrual_acct,
                  param.invoice_price_var_account C_ipv_acct,
                  NVL(sys.encumbrance_account, param.encumbrance_account) C_budget_acct,
                  DECODE(sys.inventory_asset_flag, 'Y', param.material_account,  NVL(sys.expense_account, param.expense_account)) C_charge_acct,
                  sys.purchasing_enabled_flag C_purch_flag,
                  sys.internal_order_enabled_flag C_order_flag,
                  sys.list_price_per_unit C_unit_price,
                  sys.build_in_wip_flag C_build_in_wip,
                  sys.pick_components_flag C_pick_components,
                  NVL(param.process_enabled_flag,'N')    C_process_enabled,
                  NVL(sys.recipe_enabled_flag,'N')     C_recipe_enabled,
                  NVL(sys.process_execution_enabled_flag,'N')   C_execution_enabled,
                  sys.repetitive_planning_flag C_repetitive_planned_item,
                  NVL(sys.source_type, param.source_type) C_src_type,
                  DECODE(sys.source_type, NULL, DECODE(param.source_type, NULL, NULL, param.source_organization_id), sys.source_organization_id) C_SRC_ORG,
                  DECODE(sys.source_type, NULL, DECODE(param.source_type, NULL, NULL, param.source_subinventory), sys.source_subinventory) C_src_subinv,
	sys.lot_control_code C_lot_control, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item', 'INV', 'MSTK', 101, sys.ORGANIZATION_ID, sys.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_FLEX_ITEM, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat', 'INV', 'MCAT', cat.STRUCTURE_ID, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_FLEX_CAT, 
	MRP_MRPRPROP_XMLP_PKG.c_safety_stockformula(sys.inventory_item_id, nvl ( sys.preprocessing_lead_time , 0 ) + nvl ( sys.full_lead_time , 0 ) + nvl ( sys.postprocessing_lead_time , 0 )) C_SAFETY_STOCK, 
	MRP_MRPRPROP_XMLP_PKG.c_reorder_pointformula(MRP_MRPRPROP_XMLP_PKG.c_safety_stockformula(sys.inventory_item_id, nvl ( sys.preprocessing_lead_time , 0 ) + nvl ( sys.full_lead_time , 0 ) + nvl ( sys.postprocessing_lead_time , 0 ))) C_REORDER_POINT, 
	MRP_MRPRPROP_XMLP_PKG.c_demand_qtyformula(sys.inventory_item_id, nvl ( sys.preprocessing_lead_time , 0 ) + nvl ( sys.full_lead_time , 0 ) + nvl ( sys.postprocessing_lead_time , 0 ), sys.lot_control_code) C_DEMAND_QTY, 
	MRP_MRPRPROP_XMLP_PKG.c_reorder_qtyformula(sys.inventory_item_id, MRP_MRPRPROP_XMLP_PKG.c_tot_availformula(MRP_MRPRPROP_XMLP_PKG.c_demand_qtyformula(sys.inventory_item_id, nvl ( sys.preprocessing_lead_time , 0 ) + nvl ( sys.full_lead_time , 0 ) + nvl ( sys.postprocessing_lead_time , 0 ), sys.lot_control_code)), MRP_MRPRPROP_XMLP_PKG.c_reorder_pointformula(MRP_MRPRPROP_XMLP_PKG.c_safety_stockformula(sys.inventory_item_id, nvl ( sys.preprocessing_lead_time , 0 ) + nvl ( sys.full_lead_time , 0 ) + nvl ( sys.postprocessing_lead_time , 0 ))), sys.fixed_lot_multiplier, sys.minimum_order_quantity, sys.maximum_order_quantity) C_REORDER_QTY, 
	MRP_MRPRPROP_XMLP_PKG.c_tot_availformula(MRP_MRPRPROP_XMLP_PKG.c_demand_qtyformula(sys.inventory_item_id, nvl ( sys.preprocessing_lead_time , 0 ) + nvl ( sys.full_lead_time , 0 ) + nvl ( sys.postprocessing_lead_time , 0 ), sys.lot_control_code)) C_TOT_AVAIL, 
	MRP_MRPRPROP_XMLP_PKG.c_run_restockformula(MRP_MRPRPROP_XMLP_PKG.c_tot_availformula(MRP_MRPRPROP_XMLP_PKG.c_demand_qtyformula(sys.inventory_item_id, nvl ( sys.preprocessing_lead_time , 0 ) + nvl ( sys.full_lead_time , 0 ) + nvl ( sys.postprocessing_lead_time , 0 ), sys.lot_control_code)), MRP_MRPRPROP_XMLP_PKG.c_reorder_pointformula(MRP_MRPRPROP_XMLP_PKG.c_safety_stockformula(sys.inventory_item_id, nvl ( sys.preprocessing_lead_time , 0 ) + nvl ( sys.full_lead_time , 0 ) + nvl ( sys.postprocessing_lead_time , 0 ))), sys.repetitive_planning_flag, sys.planning_make_buy_code, DECODE ( sys.inventory_asset_flag , 'Y' , param.material_account , NVL ( sys.expense_account , param.expense_account ) ), param.ap_accrual_account, param.invoice_price_var_account, NVL ( sys.encumbrance_account , param.encumbrance_account ), NVL ( sys.source_type , param.source_type ), DECODE ( sys.source_type , NULL , DECODE ( param.source_type , NULL , NULL , param.source_organization_id ) , sys.source_organization_id ), sys.internal_order_enabled_flag, sys.purchasing_enabled_flag, NVL ( param.process_enabled_flag , 'N' ), sys.build_in_wip_flag, sys.pick_components_flag, NVL ( sys.recipe_enabled_flag , 'N' ), NVL ( sys.process_execution_enabled_flag , 'N' ), sys.inventory_item_id, :C_REORDER_QTY, sys.fixed_lead_time, sys.variable_lead_time, nvl ( sys.preprocessing_lead_time , 0 ) + nvl ( sys.full_lead_time , 0 ), sys.primary_uom_code, sys.list_price_per_unit, sys.description, DECODE ( sys.source_type , NULL , DECODE ( param.source_type , NULL , NULL , param.source_subinventory ) , sys.source_subinventory )) C_RUN_RESTOCK,
	MRP_MRPRPROP_XMLP_PKG.C_LEAD_TIME_DEMAND_p C_LEAD_TIME_DEMAND,
	MRP_MRPRPROP_XMLP_PKG.C_ONHAND_QTY_p C_ONHAND_QTY,
	MRP_MRPRPROP_XMLP_PKG.C_SUPPLY_QTY_p C_SUPPLY_QTY
FROM 	mtl_categories cat,
                  per_all_people_f emp,
                  &C_ABC_FROM
	mtl_item_categories ic,
                  mtl_parameters param,
	mtl_system_items_vl sys
WHERE	cat.category_id = ic.category_id
	AND	sys.inventory_item_flag = 'Y'
		&C_PLANNER_RANGE
		&C_BUYER_RANGE
                                     &C_ABC_RANGE
                                     &C_ABC_WHERE
		&C_CATEGORY_WHERE
	AND	&P_ITEM_RANGE
	AND	&P_CAT_RANGE
                   AND	emp.person_id(+) = sys.buyer_id
              	AND	sysdate between emp.effective_start_date(+) and emp.effective_end_date(+)
	AND	ic.organization_id = sys.organization_id
	AND	sys.organization_id = :P_ORG_ID
                  AND          param.organization_id = sys.organization_id
	AND	sys.inventory_planning_code = '1'
                  AND	:P_LEVEL = 1
	AND	sys.inventory_item_flag = 'Y'
	AND 	ic.category_set_id = :P_CATEGORY_SET
	AND	ic.inventory_item_id = sys.inventory_item_id
UNION
SELECT 	null C_item,
	null C_category,
	sys.planner_code C_planner,
                  emp.full_name C_buyer,
                  &P_DYNAMIC_ABC C_abc_class_name,
	sys.description C_description,
	nvl(sub.preprocessing_lead_time,0) + nvl(sub.processing_lead_time,0)  + nvl(sub.postprocessing_lead_time,0) C_ord_lead_time,
                   nvl(sub.preprocessing_lead_time,0) + nvl(sub.processing_lead_time,0) C_pur_lead_time,
	sys.inventory_item_id C_item_id,
                  sys.planning_make_buy_code C_make_buy,
                  sys.fixed_lead_time C_fixed_lead_time,
                  sys.variable_lead_time C_variable_lead_time,
                  sys.primary_uom_code C_primary_uom,
                  sub.fixed_lot_multiple C_fix_lot_mult,
                  sub.minimum_order_quantity C_min_ord_qty,
                  sub.maximum_order_quantity C_max_ord_qty,
                  param.ap_accrual_account C_accrual_acct,
                  param.invoice_price_var_account C_ipv_acct,
                  NVL(sec.encumbrance_account, NVL(sec.encumbrance_account,NVL(sys.encumbrance_account, param.encumbrance_account))) C_budget_acct,
                  DECODE(sys.inventory_asset_flag, 'Y', sec.material_account,  NVL(sec.expense_account, NVL(sys.expense_account, param.expense_account))) C_charge_acct,
                  sys.purchasing_enabled_flag C_purch_flag,
                  sys.internal_order_enabled_flag C_order_flag,
                  sys.list_price_per_unit C_unit_price,
                  sys.build_in_wip_flag C_build_in_wip,
                  sys.pick_components_flag C_pick_components,
                  NVL(param.process_enabled_flag,'N')    C_process_enabled,
                  NVL(sys.recipe_enabled_flag,'N')     C_recipe_enabled,
                  NVL(sys.process_execution_enabled_flag,'N')   C_execution_enabled,
                  sys.repetitive_planning_flag C_repetitive_planned_item,
                  NVL(sub.source_type, NVL(sec.source_type, NVL(sys.source_type, param.source_type))) C_src_type,
                  DECODE(sub.source_type, NULL, DECODE(sec.source_type, NULL, DECODE(sys.source_type, NULL, DECODE(param.source_type, NULL, NULL, param.source_organization_id), sys.source_organization_id), sec.source_organization_id), sub.source_organization_id) C_SRC_ORG,
                  DECODE(sub.source_type, NULL, DECODE(sec.source_type, NULL, DECODE(sys.source_type, NULL, DECODE(param.source_type, NULL, NULL, param.source_subinventory), sys.source_subinventory), sec.source_subinventory), sub.source_subinventory) C_src_subinv,
	sys.lot_control_code C_lot_control, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item', 'INV', 'MSTK', 101, sys.ORGANIZATION_ID, sys.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_FLEX_ITEM, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat', 'INV', 'MCAT', cat.STRUCTURE_ID, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_FLEX_CAT, 
	MRP_MRPRPROP_XMLP_PKG.c_safety_stockformula(sys.inventory_item_id, nvl ( sys.preprocessing_lead_time , 0 ) + nvl ( sys.full_lead_time , 0 ) + nvl ( sys.postprocessing_lead_time , 0 )) C_SAFETY_STOCK, 
	MRP_MRPRPROP_XMLP_PKG.c_reorder_pointformula(MRP_MRPRPROP_XMLP_PKG.c_safety_stockformula(sys.inventory_item_id, nvl ( sys.preprocessing_lead_time , 0 ) + nvl ( sys.full_lead_time , 0 ) + nvl ( sys.postprocessing_lead_time , 0 ))) C_REORDER_POINT, 
	MRP_MRPRPROP_XMLP_PKG.c_demand_qtyformula(sys.inventory_item_id, nvl ( sys.preprocessing_lead_time , 0 ) + nvl ( sys.full_lead_time , 0 ) + nvl ( sys.postprocessing_lead_time , 0 ), sys.lot_control_code) C_DEMAND_QTY, 
	MRP_MRPRPROP_XMLP_PKG.c_reorder_qtyformula(sys.inventory_item_id,MRP_MRPRPROP_XMLP_PKG.c_tot_availformula(MRP_MRPRPROP_XMLP_PKG.c_demand_qtyformula(sys.inventory_item_id, nvl ( sys.preprocessing_lead_time , 0 ) + nvl ( sys.full_lead_time , 0 ) + nvl ( sys.postprocessing_lead_time , 0 ), sys.lot_control_code)), MRP_MRPRPROP_XMLP_PKG.c_reorder_pointformula(MRP_MRPRPROP_XMLP_PKG.c_safety_stockformula(sys.inventory_item_id, nvl ( sys.preprocessing_lead_time , 0 ) + nvl ( sys.full_lead_time , 0 ) + nvl ( sys.postprocessing_lead_time , 0 ))), sys.fixed_lot_multiplier, sys.minimum_order_quantity, sys.maximum_order_quantity) C_REORDER_QTY, 
	MRP_MRPRPROP_XMLP_PKG.c_tot_availformula(MRP_MRPRPROP_XMLP_PKG.c_demand_qtyformula(sys.inventory_item_id, nvl ( sys.preprocessing_lead_time , 0 ) + nvl ( sys.full_lead_time , 0 ) + nvl ( sys.postprocessing_lead_time , 0 ), sys.lot_control_code)) C_TOT_AVAIL, 
	MRP_MRPRPROP_XMLP_PKG.c_run_restockformula(MRP_MRPRPROP_XMLP_PKG.c_tot_availformula(MRP_MRPRPROP_XMLP_PKG.c_demand_qtyformula(sys.inventory_item_id, nvl ( sys.preprocessing_lead_time , 0 ) + nvl ( sys.full_lead_time , 0 ) + nvl ( sys.postprocessing_lead_time , 0 ), sys.lot_control_code)), MRP_MRPRPROP_XMLP_PKG.c_reorder_pointformula(MRP_MRPRPROP_XMLP_PKG.c_safety_stockformula(sys.inventory_item_id, nvl ( sys.preprocessing_lead_time , 0 ) + nvl ( sys.full_lead_time , 0 ) + nvl ( sys.postprocessing_lead_time , 0 ))), sys.repetitive_planning_flag, sys.planning_make_buy_code, DECODE ( sys.inventory_asset_flag , 'Y' , param.material_account , NVL ( sys.expense_account , param.expense_account ) ), param.ap_accrual_account, param.invoice_price_var_account, NVL ( sys.encumbrance_account , param.encumbrance_account ), NVL ( sys.source_type , param.source_type ), DECODE ( sys.source_type , NULL , DECODE ( param.source_type , NULL , NULL , param.source_organization_id ) , sys.source_organization_id ), sys.internal_order_enabled_flag, sys.purchasing_enabled_flag, NVL ( param.process_enabled_flag , 'N' ), sys.build_in_wip_flag, sys.pick_components_flag, NVL ( sys.recipe_enabled_flag , 'N' ), NVL ( sys.process_execution_enabled_flag , 'N' ), sys.inventory_item_id, :C_REORDER_QTY, sys.fixed_lead_time, sys.variable_lead_time, nvl ( sys.preprocessing_lead_time , 0 ) + nvl ( sys.full_lead_time , 0 ), sys.primary_uom_code, sys.list_price_per_unit, sys.description, DECODE ( sys.source_type , NULL , DECODE ( param.source_type , NULL , NULL , param.source_subinventory ) , sys.source_subinventory )) C_RUN_RESTOCK,
	MRP_MRPRPROP_XMLP_PKG.C_LEAD_TIME_DEMAND_p C_LEAD_TIME_DEMAND,
	MRP_MRPRPROP_XMLP_PKG.C_ONHAND_QTY_p C_ONHAND_QTY,
	MRP_MRPRPROP_XMLP_PKG.C_SUPPLY_QTY_p C_SUPPLY_QTY
FROM 	mtl_categories cat,
                  per_all_people_f emp,
                  &C_ABC_FROM
	mtl_item_categories ic,
                  mtl_parameters param,
	mtl_system_items_vl sys,
	mtl_secondary_inventories sec,
	mtl_item_sub_inventories sub
WHERE	cat.category_id = ic.category_id
	AND	sys.inventory_item_flag = 'Y'
		&C_PLANNER_RANGE
		&C_BUYER_RANGE
                                     &C_ABC_RANGE 
                                     &C_ABC_WHERE
		&C_CATEGORY_WHERE
	AND	&P_ITEM_RANGE
	AND	&P_CAT_RANGE
                  AND	emp.person_id(+) = sys.buyer_id
	AND	sysdate between emp.effective_start_date(+) and emp.effective_end_date(+)
	AND	ic.organization_id = sys.organization_id
	AND	sys.organization_id = :P_ORG_ID
                  AND          param.organization_id = sys.organization_id
	AND	sub.inventory_planning_code = '1'
                  AND	:P_LEVEL = 2
	AND	sys.inventory_item_flag = 'Y'
	AND 	ic.category_set_id = :P_CATEGORY_SET
	AND	ic.inventory_item_id = sys.inventory_item_id
	AND	sub.inventory_item_id = sys.inventory_item_id
	AND	sub.organization_id = sys.organization_id
	AND	sec.organization_id = sys.organization_id
	AND	sec.secondary_inventory_name = :P_SUBINV
                  AND	sec.secondary_inventory_name = sub.secondary_inventory
ORDER BY
	&C_ORDER_BY
Parameter Name SQL text Validation
Item Selection