MRP Reorder Point

Description

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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Copy

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

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

LOV Oracle

Demand Cutoff Date

Date

Supply Cutoff Date

Date

Restock

LOV Oracle

Default Delivery To

LOV Oracle

Forecast

LOV Oracle

First Sort

LOV Oracle

Second Sort

LOV Oracle

Third Sort

LOV Oracle

Items From

Char

Planners From

LOV Oracle

To 2

LOV Oracle

Buyers From

LOV Oracle

To 3

LOV Oracle

Category Set

LOV Oracle

Categories From

Char

To 4

Char

ABC Assignment Group

LOV Oracle

ABC Class

LOV Oracle

Include PO Supply

LOV Oracle

Include WIP Supply

LOV Oracle

Include Interface Supply

LOV Oracle

Include Non-nettable

LOV Oracle

Display Item Description

LOV Oracle

Display Additional Information

LOV Oracle

Download

Related Blitz Reports

GL Account Analysis PO Purchase Price Variance GL Account Analysis (Distributions) GL Account Analysis (Drilldown) ECC Procurement, Agreements PO Requisition Upload XXAMZ OTIF Delivered Orders(XXONT1253) ECC Procurement, Requisitions ECC Procurement, Purchase Orders

Blitz Report™

Blitz Report™ provides multiple benefits:

Perfectly formatted Excel reports
Create new or ad-hoc reports in minutes
User configurable reports
Includes seeded reports for all core areas
Toolkits for tuning, management and support
Data governance and audit tracking
Central report repository & version control
Imports BI-Publisher and Discoverer reports
EBS forms interface for user adoption

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Find out more

Features

Download

FAQ