Topic: Order Management

How To: Create a Sales Order 0

In the Order to Cash (OTC) process in Oracle Applications, one of the first steps is to Create a Sales Order in Order Management Module.

The usual process of creating a Sales Order involves inserting data into an inbound staging table and then calling a Custom concurrent program. The detailed steps are as below:

  • Insert data into staging tables.
  • Validate data in the staging tables.
  • Insert the validated data into Oracle Order Management Interface Tables.
  • Run the Seeded Oracle “Order Import” Concurrent program to Create Sales Order.

These steps are explained as below:

  • Insert data into staging tables: Basing on the source system, data is inserted into Custom staging tables via a middleware application like Webmethods or SOA.
  • Validating Data in the staging tables: Some of the major validations that need to be done before inserting data into Interface tables are:
    • Item Validation: The item that is being used on the Sales order need to be validated. The item has to exist in the Item Master as well should be assigned to the inventory org from where the Item is being shipped from.
      SELECT *
        FROM mtl_system_items_b
       WHERE segment1 = 'TEST_ITEM_1234';
    • Customer Validation: The Customer for which the order is being created for, should exist in the system already.
      SELECT *
        FROM hz_cust_accounts
       WHERE account_number = '12345678'; -- p_customer_number
    • Get BILL_TO Validation: For the Customer that exists, check to see if a valid BILL_TO Site exists.
        SELECT hcsua.org_id, hcsua.site_use_id, hps.party_site_id
                  FROM hz_cust_accounts        hca
                      ,hz_cust_acct_sites_all  hcasa
                      ,hz_cust_site_uses_all   hcsua
                      ,hz_party_sites          hps
                  WHERE     hca.cust_account_id = hcasa.cust_account_id
                        AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
                        AND hcasa.status = 'A'
                        AND hcsua.status = 'A'
                        AND hcasa.org_id = hcsua.org_id
                        AND hcsua.site_use_code = 'BILL_TO'
                        AND hps.identifying_address_flag = 'Y'
                        AND hps.party_site_id = hcasa.party_site_id
                        AND hca.cust_account_id = '&Cust_account_id'
                        AND hcsua.org_id = '&Operating_unit_id'
                        AND ROWNUM < 2;
    • GL_Period Validation: Validate whether the Order data has Open GL period or not.
      SELECT gps.period_name,
        FROM gl_periods          gp,
             gl_period_statuses  gps,
             gl_sets_of_books    gsob,
             hr_operating_units  hou
       WHERE     gp.period_name = gps.period_name
             AND gp.period_set_name = gsob.period_set_name
             AND gsob.set_of_books_id = hou.set_of_books_id
             AND gps.application_id = 222
             AND gps.set_of_books_id = gsob.set_of_books_id
             AND '&Order Date' BETWEEN gp.start_date AND gp.end_date
             AND hou.organization_id = '&Organization_id';
    • UOM Code Validation: Validate Unit of Measure validation.
      SELECT uom_code FROM mtl_units_of_measure_vl;
    • Operating Unit Validation: Validate the Operating Unit for which the order is being created in.
      SELECT organization_id
        FROM hr_operating_units
       WHERE name = ''
    • Get Price List: Derive the Price List.
      SELECT list_header_id, name
        FROM qp_list_headers qlh
       WHERE     qlh.orig_org_id = '&Organization_id'
             AND currency_code = '&Currency_code'
             AND TRUNC (SYSDATE) BETWEEN NVL (TRUNC (qlh.start_date_active),
                                              TRUNC (SYSDATE) - 1)
                                     AND NVL (TRUNC (qlh.end_date_active),
                                              TRUNC (SYSDATE) + 1)
             AND qlh.active_flag = 'Y';
    • Derive Tax Rate Code: Derive the tax rate code based on the tax code passed by the source system.
      SELECT zrab.tax_regime_code,
        FROM zx_rates_b zrab, zx_regimes_b zrgb
       WHERE     zrab.tax_regime_code = zrgb.tax_regime_code
             AND TRUNC (SYSDATE) BETWEEN NVL (zrab.effective_from,
                                              TRUNC (SYSDATE) - 1)
                                     AND NVL (zrab.effective_to,
                                              TRUNC (SYSDATE) + 1)
             AND zrab.active_flag = 'Y'                     -- Added for Defect#7614
             AND zrab.tax_rate_code = '&tax_rate_code';
  • Inserting Data into Interface Table: Upon Validation, Data is inserted into the following Order Interface Tables:
    • Headers Interface: OE_HEADERS_IFACE_ALL.
      • This is the Headers Interface table that captures all the data that goes into the Order Header data (later into OE_ORDER_HEADERS_ALL table). Some of the important columns to be populated are: Operation_code, order_number, orig_sys_document_ref, org_id, Ordered_date, order_type_id, sold_to_org_id, ship_from_org_id, invoice_to_org_id, booked_flag, change_sequence etc.
    • Lines Interface: OE_LINES_IFACE_ALL
      • This is the Lines Interface table that captures all the data that goes onto the Line Level on the Sales order (later onto the OE_ORDER_LINES_ALL table). Some of the important columns are: Org_id, orig_sys_document_ref, inventory_item_id (item), ordered_quantity, order_quantity_uom, unit_list_price, unit_selling_price, fulfillment_set_id etc.
    • Payments Interface: OE_PAYMENTS_IFACE_ALL
      • The Payments Interface table captures data that is shown later on the payments screen on the order. This table has the data that is later populated into OE_PAYMENTS table. Some important columns are: Payment_number, orig_sys_document_ref, orig_sys_payment_ref, payment_type_code, payment_amount, check_number, credit_card_number, credit_card_expiration_date etc.
    • Actions Interface: OE_ACTIONS_IFACE_ALL
      • The Actions interface table is used to load data that will perform any Action to be taken on the order once the order is created. Some uses include Cancelling a line, Applying Hold etc.. Some important columns are: Org_id, Hold_id, Hold_type_code, Operation_Code.
    • Adjustment Interface: OE_PRICE_ADJS_IFACE_ALL
      • The Adjustment interface table holds the data that later goes to the OE_Price_Adjustments table. This table is used to store price adjustments that have been applied to an order or a line.
  • Submit Order Import program: Once data is inserted into the above interface tables, the Seeded Oracle job – Order Import is submitted as a Concurrent program. The code to call the program is as below:
    • x_request_id :=
                  fnd_request.submit_request (
                     application   => 'ONT',
                     program       => 'OEOIMP',
                     description   => 'EBS Order Import',
                     start_time    => SYSDATE,
                     sub_request   => FALSE,
                     argument1     => Operating Unit,
                     argument2     => Order Source,
                     argument3     => Orig Sys Doc Ref,
                     argument4     => Operation Code,
                     argument5     => Validate Only -- Yes or No,
                     argument6     => Debug Level,   -- 1
                     argument7     => Number of Instances,
                     argument8     => Sold To Org ID,
                     argument9     => Sold To Org,
                     argument10    => Change Sequence,
                     argument11    => Enable Single Line Queue for Instances,
                     argument12    => Trim Trailing Blanks, --- Y
                     argument13    => Process Orders With No Org Specified,
                     argument14    => Default Operating Unit,
                     argument15    =>  Validate DFFs ?     )
  • Order Interface Errors: During importing the Orders from Interface tables to the Order base tables, if an error occurs due to a setup issue or a data issue, then the corresponding error messages are stored in OE_PROCESSING_MSGS_TL table.
    AND ORIGINAL_SYS_DOCUMENT_REF = dof_ref; -- ORIGINAL_SYS_DOCUMENT_REF in headers interface
  • Oracle Base Tables: Upon completion of the “Order Import” seeded program, the base tables where order information is stored are:
    • Header Data: OE_ORDER_HEADERS_ALL
    • Lines Data: OE_ORDER_LINES_ALL
    • Payments Data: OE_PAYMENTS
    • Adjustments Data: OE_PRICE_ADJUSTMENTS
    • Holds Data: OE_ORDER_HOLDS_ALL
    • Order Sources: OE_ORDER_SOURCES
    • Sets data – Arrival Set, Ship Set, Fulfillment Set: OE_SETS
    • Transaction Types: OE_TRANSACTION_TYPES_TL.

Oracle apps order error interface table 0

During a Sales Order Creation scenario, when data is inserted into Interface table – sometimes Orders can fail to get created due to issues in the Interface table. The corresponding error information is stored in the oe_processing_msgs_tl table.

The query to find out error messages in the Order Interface table is:

FROM oe_processing_msgs opm, 
     oe_processing_msgs_tl opmt
WHERE opm.transaction_id = opmt.transaction_id
AND opm.original_sys_document_ref = '123456';  
--- where 123456 is the Order Number in the interface table.

--- Interface table information. 

FROM oe_headers_iface_all 
WHERE order_number = '123456';

FROM oe_lines_iface_all 
WHERE orig_sys_document_ref = (SELECT orig_sys_document_ref 
                               FROM oe_headers_iface_all 
                               WHERE order_number = '123456');