Item Details Query in Oracle R12 to Fetch Imp Columns

Here is the query you can use to fetch the item details in Oracle R12. Query Returns following Columns:

• Item Number
• Organization Code
• Inventory Item Id
• Full Lead Time
• Planner Code
• Item Status Code
• Minimum Order Quantity
• Maximum Order Quantity
• Fixed Lot Multiplier
• Inventory Planning Code
• Minimum Min Max Quantity
• Maximum Min Max Quantity
• Customer Order Enabled Flag
• SO Transactions Flag
• Primary UOM Code
• Unit Length
• Unit Width
• Unit Height
• Unit Weight

But before we start, let’g get to the basics. Item is an essential entity in Oracle e-Business Suit and most of the information related to item is stored in MTL_SYSTEM_ITEMS_B table in Oracle R12. In this table, Item Number is stored in SEGMENT1 column. And another important point, Item Number and Inventory Org give you a unique row in the table which in other words is that there can be multiple rows for same segment1 in MTL_SYSTEM_ITEMS_B table.

Item Details Query

SELECT item.segment1 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 mtl_system_items_b item,
mtl_parameters org
WHERE 1 = 1
AND item.organization_id = org.organization_id
AND org.organization_code = :INV_ORG_CODE
and item.segment1 = :ITEM_NUMBER

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..