Order Management Query & Tables in Oracle Apps R12 - Orapoint

The Order management tables in Oracle Apps R12 will be discussed in this post. Oracle apps use order management tables to hold several sorts of sales order data. sales order tables, sales order shipping tables, sales order price list tables, and sales order AR invoices tables are all examples of sales order tables.

In Oracle Apps R12, we’ll strive to share the most significant order management tables. According to Oracle Apps r12, the Order Management tables store many types of information. We’ll also try to run a sales order detail SQL query in Oracle Apps R12 utilizing the order management tables.

Oracle Supply Chain and Order Management Analytics Overview

Orders, invoices, backlogs, and inventories are all part of the Oracle Supply Chain and Order Management Analytics application. The first step in the sales process is to create a sales order. The fulfilment procedure comes to an end with invoices. Backlogs are areas of your fulfilment process that are congested. This includes information on which items have been booked, backlogged, and invoiced. This gives you the ability to assess the sales success of individual sales reps or departments. Inventory Transactions, Inventory Balances, Bill of Materials, and Customer and Supplier Returns are all included in the Oracle Supply Chain and Order Management Analytics application.

Oracle Apps R12 Order Management Tables

1. OE_ORDER_HEADERS_ALL
2. OE_ORDER_LINES_ALL
3. QP_LIST_HEADERS
4. QP_LIST_LINES
5. QP_PRICING_ATTRIBUTES
6. OE_PRICE_ADJUSTMENTS
7. OE_ORDER_HOLDS_ALL
8. OE_ORDER_CREDIT_CHECK_RULES
9. WSH_DELIVERY_DETAILS
10. WSH_DELIVERY_ASSIGNMENTS
11. WSH_NEW_DELIVERIES
12. QP_LIST_HEADERS
13. QP_LIST_LINES
14. QP_PRICING_ATTRIBUTES

(1) Query to Get Order Management Details in Oracle Apps R12

SELECT  QLH.NAME,
        QLH.DESCRIPTION,
        QLH.START_DATE_ACTIVE,
        QLL.OPERAND,
        QLL.ARITHMETIC_OPERATOR,
        OOLA.ordered_quantity,
        oola.order_quantity_uom,
        oola.ordered_item,
        ooha.order_number
  FROM  apps.qp_list_headers QLH ,
        apps.qp_list_lines QLL,
        apps.qp_pricing_attributes qpa,
        apps.oe_order_lines_all oola,
        apps.oe_order_headers_all ooha
  WHERE QLH.list_HEADER_ID=QLL.list_HEADER_ID
    and qpa.LIST_LINE_ID=qll.LIST_LINE_ID
    and qpa.list_HEADER_ID=qlh.list_HEADER_ID
    and to_char(oola.INVENTORY_ITEM_ID) =QPA.PRODUCT_ATTR_VALUE
    and oola.header_id=ooha.header_id
    and ooha.order_number=:P_ORDER_NUMBER;

(2) Query to get Order management Details in Oracle Apps R12

SELECT distinct
       ooh.order_number,
       ac.customer_name,
       ooh.org_id,
       ooh.ORDERED_DATE,
       ooh.FLOW_STATUS_CODE SO_Status,
       ool.line_number,
       msi.SEGMENT1 Item_Name,
       ool.ordered_quantity,
       wdd.shipped_quantity,
       rctl.QUANTITY_invoiced,
       wda.delivery_id shipment_number,
       rct.TRX_NUMBER Invoice_Num,
       rct.TRX_date Invoice_Date,
       rct.STATUS_TRX,
       decode(rct.COMPLETE_FLAG,'Y','Completed','In Complete') Inv_Status,
       ool.UNIT_SELLING_price*ool.ordered_quantity line_total
  FROM apps.oe_order_headers_all ooh,
       apps.ar_customers ac,
       apps.wsh_delivery_details wdd,
       apps.oe_order_lines_all ool,
       apps.wsh_delivery_assignments wda,
       apps.hz_cust_accounts hca,
       apps.ra_customer_trx_lines_all rctl,
       apps.ra_customer_trx_all rct,
       apps.mtl_system_items msi
 WHERE
       ooh.header_id=ool.header_id
   and ooh.sold_to_org_id=hca.cust_account_id
   and ooh.header_id=wdd.source_header_id
   and ool.line_id=wdd.source_line_id
   and hca.cust_account_id=ac.customer_id
   and msi.INVENTORY_ITEM_ID=ool.INVENTORY_ITEM_ID
   and msi.ORGANIZATION_ID=ool.SHIP_FROM_ORG_ID
   and wda.delivery_detail_id=wdd.delivery_detail_id
   and ooh.org_id=:P_ORG_ID
   and rct.CUSTOMER_TRX_ID = rctl.CUSTOMER_TRX_ID
   and rctl.LINE_TYPE = 'LINE'
   and rctl.interface_line_attribute1 = to_char(ooh.ORDER_NUMBER)
   and  rctl.interface_line_attribute3=to_char(wda.delivery_id)
   and ooh.order_number=:P_order_number
ORDER BY ool.line_number;