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.

Want to give back to the society? (Do you have something which you think can be used by folks working on Oracle world wide?) Then Click here, share your email id and a brief description of the topic. And keep your photo and the content ready.

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