Item detail query will be needed whenever you need to find the various attributes/ properties of items in Oracle Fusion. In this post we will share the query needed to fetch those details. Of course, this query is primarily based on EGP_SYSTEM_ITEMS_B which is the base table to store the item details in Oracle Fusion. Again, since Items are stored in Oracle Fusion at the Inventory level, while querying the details you must specify the Inventory org for which you are trying to query the details for. Generally this Inventory Org which is used to store the specifications, is called Item Master Org. But of course, there can be exception to it and you might need to query the details from another Inventory org too, depending on the solution.
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
Item Detail Query
This query will fetch you following details:
1. Item Number
2. Inventory Org Name
3. Full Lead Time
4. Planner Code
5. MRP Planning Code
6. Item Status
7. Minimum Order Quantity
8. Maximum Order Quantity
9. Fixed Order Quantity
10. Fixed Lot Multiplier
11. Inventory Planning Code
12. Min-Max – Minimum Quantity
13. Min-Max Maximum Quantity
14. Transaction Enabled Flag
15. Customer Ordered Enabled Flag
16. SO Transaction Flag
17. Primary UOM Code
18. Unit Length
19. Unit Width
20. Unit Weight
21. Unit Height
Query
SELECT item.item_number,
org.organization_code,
item.inventory_item_id,
item.full_lead_time,
item.planner_code,
DECODE (item.mrp_planning_code, 3, ‘MRP Planned’, 4, ‘MPS Planned’, 6, ‘Not Planned’,
7, ‘MRP/MPP Planned’, 8, ‘MPS/MPP Planned’, 9, ‘MPP Planned’, NULL) mrp_planning_code,
item.inventory_item_status_code,
item.minimum_order_quantity,
item.maximum_order_quantity,
item.fixed_order_quantity,
item.fixed_lot_multiplier,
DECODE (item.inventory_planning_code, 6, ‘Not Planned’, 2, ‘Min-Max’, 1, ‘Reorder Point’, 7, ‘Vendor Managed’) inventory_planning_code,
item.min_minmax_quantity,
item.max_minmax_quantity,
item.mtl_transactions_enabled_flag,
item.customer_order_enabled_flag,
item.so_transactions_flag,
item.primary_uom_code,
item.unit_length,
item.unit_width,
item.unit_height,
item.unit_weight,
item.created_by,
item.creation_date,
item.last_updated_by,
item.last_update_date
FROM egp_system_items item,
inv_org_parameters org
WHERE 1 = 1
AND item.organization_id = org.organization_id
AND org.organization_code = :INV_ORG_CODE
Related Posts
Item Table
Item Catalog – Category Assignment in Fusion
Item Catalog Category Table Details
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
Reference:
support.oracle.com
Check out the Amazon Deals of the day based on your interests..
