MRP Reorder Point

Description
Categories: BI Publisher, Manufacturing
Application: Master Scheduling/MRP
Source: Reorder Point Report (XML)
Short Name: MRPRPROP_XML
DB package: MRP_MRPRPROP_XMLP_PKG
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 NameSQL textValidation
User Id
 
Number
Category Structure
 
Number
Organization Id
 
Number
Display Additional Information
 
LOV Oracle
Display Item Description
 
LOV Oracle
Include Non-nettable
 
LOV Oracle
Include Interface Supply
 
LOV Oracle
Include WIP Supply
 
LOV Oracle
Include PO Supply
 
LOV Oracle
ABC Class
 
LOV Oracle
ABC Assignment Group
 
LOV Oracle
To
 
Categories From
 
Category Set
 
LOV Oracle
To
 
LOV Oracle
Buyers From
 
LOV Oracle
To
 
LOV Oracle
Planners From
 
LOV Oracle
To
 
Items From
 
Third Sort
 
LOV Oracle
Second Sort
 
LOV Oracle
First Sort
 
LOV Oracle
Forecast
 
LOV Oracle
Default Delivery To
 
LOV Oracle
Restock
 
LOV Oracle
Supply Cutoff Date
 
Date
Demand Cutoff Date
 
Date
Item Selection
 
LOV Oracle