SELECT DISTINCT hca.account_number customer_number,
                hp.party_name customer_name,
                hps.party_site_number site_number, hl.address1 address1,
                hl.address2 address2, hl.address3 address3,
                hl.address4 address4, city,
                hl.postal_code postal_code, hl.state state,
                ftt.territory_short_name country,
                hcsua1.LOCATION bill_to_location,
                hcsua2.LOCATION ship_to_location
           FROM hz_parties hp,
                hz_party_sites hps,
                hz_cust_accounts hca,
                hz_cust_acct_sites_all hcasa1,
                hz_cust_site_uses_all hcsua1,
                hz_locations hl,
                fnd_territories_tl ftt,
                hz_cust_acct_sites_all hcasa2,
                hz_cust_site_uses_all hcsua2
          WHERE hp.party_id = hps.party_id(+)
            AND hp.party_id = hca.party_id(+)
            AND hcasa1.party_site_id(+) = hps.party_site_id
            AND hcasa2.party_site_id(+) = hps.party_site_id
            AND hcsua1.cust_acct_site_id(+) = hcasa1.cust_acct_site_id
            AND hcsua2.cust_acct_site_id(+) = hcasa2.cust_acct_site_id
            AND hcsua1.site_use_code(+) = ‘bill_to’
            AND hcsua2.site_use_code(+) = ‘ship_to’
            AND hcasa1.org_id(+) = fnd_profile.VALUE (‘org_id’)
            AND hcasa2.org_id(+) = fnd_profile.VALUE (‘org_id’)
            AND hps.location_id = hl.location_id
            AND = ftt.territory_code
            AND ftt.LANGUAGE = USERENV (‘lang’)
       ORDER BY customer_number;
SELECT   acra.cash_receipt_id,
         DECODE (acra.TYPE,
                 ‘cash’, ‘cash receipt receipt’,
                 ‘misc’, ‘miscellaneous’,
                ) receipt_type,
         acra.currency_code, acra.doc_sequence_value receipt_number,
         acra.receipt_number reference_number,
         TRUNC (acra.receipt_date) receipt_date, hp.party_name received_from,
         acra.misc_payment_source, hca.account_number customer_no,
         NVL (acra.amount, 0) entered_amount,
         NVL (acra.amount, 0) * NVL (acra.exchange_rate, 1) functional_amount,
         arm.NAME payment_method, abaa.bank_account_num bank_acc_num,
         abb.bank_name, abb.bank_branch_name, acra.comments description
    FROM ar_cash_receipts_all acra,
         ar_receipt_methods arm,
         ap_bank_accounts_all abaa,
         ap_bank_branches abb,
         hz_cust_accounts hca,
         hz_parties hp
   WHERE acra.pay_from_customer = hca.cust_account_id(+)
     AND acra.org_id = abaa.org_id(+)
     AND hca.party_id = hp.party_id(+)
     AND acra.receipt_method_id = arm.receipt_method_id
     AND acra.remittance_bank_account_id = abaa.bank_account_id
     AND abaa.bank_branch_id = abb.bank_branch_id
ORDER BY TRUNC (acra.receipt_date), acra.doc_sequence_value;
Below are some of the different statuses of Sales Order Line with brief explanation

OM = Order Management Sales order form
SE = Shipping Transactions or execution form

1) Entered (OM): Order is saved but not booked

2) Booked (OM): Order is Booked.

3) Awaiting Shipping (OM): Order is booked but lines are not yet picked.
Navigating to Shipping Execution, the delivery line status flow is:

4) Not Ready to Release (SE): A delivery line may be in this status when it is interfaced manually into Shipping, is not scheduled and has no reservations. When lines are imported automatically from Order Management this status is not used

5) Released to Warehouse (SE): Pick Release has started but not yet completed. One of the reason could be allocation have not been pick confirmed. The Pick Release process creates a Move Order Header & Mover Order Line in Inventory. This is a common status for users that perform a two-step pick release process. This status indicates that inventory allocation has occurred however pick conformation has not yet taken place.

6) Ready to Release (SE): Order Line is booked and passed to shipping execution. The line is now eligible to pick Release.

7) Backordered(SE): The status of Backorderd is assigned to a line under the following circumstances.

·         The Pick Release process attempted to allocate inventory to the line and all or a partial quantity of the item was not available. In this case the system automatically backorders the discrepant quantity.
·         At Ship confirm the user enters a shipped quantity for an item that is less than the original requested quantity.
·         The user manually Backorders the entire delivery.

8) Shipped (SE): The delivery line is shipped confirmed.

9) Confirmed (SE): The delivery line is shipped or backordered and the trip stops are open.

10) Picked (OM): Pick release is complete, both allocations and pick confirm

11) Picked Partial (OM): This status occurs when a delivery line is not allocated the full quantity during Pick Release and Ship Confirm has not occurred

12) Interfaced (SE): The delivery line is shipped and Inventory interface concurrent process is complete.

13) Awaiting Fulfillment (OM): When fulfillment set is used, Not all shippable lines in a fulfillment set or a
configuration are fulfilled

14) Fulfilled (OM): All lines in a fulfillment set are fulfilled.

15) Interfaced to Receivables (OM): The order is linked with Receivables and the invoice is created.

16) Partially Interfaced to Receivables (OM): This status is used in a PTO flow and indicates that the particular PTO item is required for revenue.

17) Closed (OM): Closed indicates that the line is closed.

18) Canceled (OM): Indicates that the line has been completely canceled. No further processing will occur for this line.

This article gives you simple steps to load large sales orders in Order Management using Excel Spreasheet.
We arrived at this solution using some good features available in Oracle Database and Oracle Applications. Here are the steps.
  1. We create order header only (using sales order form) with all the necessary information like customer, order type, price list, sales person, sales credits, warehouse and the other values as deemed necessary.
  2. Now we create line information in Excel worksheet (a template is created). We enter values for the fields like Item, quantity, price list name (if that is different from header), Line Type, Sales Person (different from header), any DFF segments, Shipping Instructions if any, warehouse name so on. We can have as many fields as that can fit in the order lines table. But instead of entering all the values, we use defaulting rules to default a lot of values from the header.
  3. In this worksheet there is a simple marco written that we use to generate another worksheet in the same file for the entered rows.This macro does some basic validations on the information entered in the worksheet and generates the worksheet with this statement for each row.
    1.  insert into custom_lines_load_tbl  (column1,column2,….) values ( field1,field2,……)
  4. Now using these rows with this statement (generated worksheet) we create a simple text file (copy and paste).
  5. As a next step we upload this file as an attachment to the order header that we have created in the step number 1.
  6. Now we run a custom program to create order lines into this order. This program takes order number as a parameter. This program reads the file contents line by line and executes the insert statements. This is the heart of the solution which is to read the contents of the file which is stored as LOB and executing the statements. After all the insert statements are executed successfully, the program calls oe_order_pub API to create order lines.
  7. As you can see the program uses this feature : reading the file which is stored in the database as LOB line by line. Here is the code snippet to read the attached file stored as a file.

PROCEDURE parse_and_load_file (
      ERROR_CODE       OUT      VARCHAR2,
      retcode          OUT      NUMBER,
      p_order_number   IN       NUMBER
      fil             BLOB;
      pos             INTEGER         := 0;
      amt             BINARY_INTEGER  := 32767;
      buf             RAW (2000);
      l_start         RAW (3000);
      l_end           RAW (3000);
      l_start_pos     INTEGER;
      l_length        INTEGER;
      nth             INTEGER         := 1;
      i               INTEGER         := 2;
      l_text          VARCHAR2 (4000);
      l_end_pos       INTEGER;
      l_plsql_block   VARCHAR2 (4000);
      k               INTEGER;
      l_write_buff    RAW (2000);
      l_text1         VARCHAR2 (4000);
      x_errbuf        VARCHAR2 (4000);
      x_retcode       NUMBER;
    delete custom_lines_upload_tbl
    where order_number=p_order_number
    and line_Id is null; –this is to make sure we do not load duplicate data
    exception when others then

SELECT file_data
  INTO fil
  FROM fnd_lobs
 WHERE file_id =
          (SELECT media_id
             FROM fnd_documents_vl
            WHERE document_id IN (
                     SELECT a.document_id
                       FROM fnd_attached_documents a, fnd_documents b
                      WHERE a.document_id = b.document_id
                        AND b.category_id =
                                     (SELECT category_id
                                        FROM fnd_document_categories_tl
                                       WHERE user_name = ‘Custom Lines Upload’)
                        AND entity_name = ‘OE_ORDER_HEADERS’
                        AND pk1_value = (SELECT header_id
                                           FROM oe_order_headers_all
                                          WHERE order_number = p_order_number)))
         FOR UPDATE;
         WHEN OTHERS
               (   ‘Order :’
                || p_order_number
                || ‘ does not have any file attached to it to load. Please attach the file and then try running this program’

      k := DBMS_LOB.ISOPEN (fil);

      — debug_log(‘Checking whether the file is already open’);
      IF k = 1
         –debug_log(‘file is already open. Closing it’);
         DBMS_LOB.CLOSE (fil);
      END IF;

         –debug_log(‘Opening the file’);
         DBMS_LOB.OPEN (fil, DBMS_LOB.lob_readonly);
         k := DBMS_LOB.ISOPEN (fil);
         WHEN OTHERS
                    (‘Exception occured in checking whether the file is open’);

      IF k = 1
            l_start := UTL_RAW.cast_to_raw (‘insert’);
            l_end := UTL_RAW.cast_to_raw (‘;’);
            l_start_pos := DBMS_LOB.INSTR (fil, l_start, 1, nth);
            pos := DBMS_LOB.INSTR (fil, l_end, 1, nth);
            buf := ”;
            –debug_log(‘Start position is :’||l_start_pos);
            –debug_log(‘End position is :’||pos);
            l_end_pos := (pos + 1) – (l_start_pos);
            –debug_log(‘Length of the string is :’||l_end_pos);
            –debug_log(‘Reading the file’);
            DBMS_LOB.READ (fil, l_end_pos, l_start_pos, buf);
            l_text := UTL_RAW.cast_to_varchar2 (buf);
            l_plsql_block :=
               || fnd_global.NEWLINE ()
               || l_text
               || fnd_global.NEWLINE ()
               || ‘commit;’
               || fnd_global.NEWLINE ()
               || ‘exception when others then’
               || fnd_global.NEWLINE ()
                  –’debug_log(”Exception occured in processing the line”);’||fnd_global.NewLine()||
               || fnd_global.NEWLINE ()
               || ‘end;’;

            –Now execute the dynamic pl/sql block to insert rows into custom table from the file
               EXECUTE IMMEDIATE l_plsql_block;
               WHEN OTHERS
                  –debug_log(‘Exception Occured’);
                  –debug_log(‘Exception is :’||substrb(sqlerrm, 1, 55));

            nth := nth + 1;
         END LOOP;

         DBMS_LOB.CLOSE (fil);
      END IF;

         k := NULL;
         k := DBMS_LOB.ISOPEN (fil);

         IF k = 1
            DBMS_LOB.CLOSE (fil);
         END IF;
            — debug_log(‘End of File reached in Exception. Closing file’);
            k := NULL;
            k := DBMS_LOB.ISOPEN (fil);

            IF k = 1
               DBMS_LOB.CLOSE (fil);
            END IF;

         –Here call the oe_order_pub API to create the lines passing the line table from this custom table.
   END parse_and_load_file;


l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
l_header_rec_in oe_order_pub.header_rec_type; — pl/sql table and record definition to be used as IN parameters
l_line_tbl_in oe_order_pub.line_tbl_type; — pl/sql table and record definition to be used as IN parameters
l_action_request_tbl_in oe_order_pub.request_tbl_type;– Used to assigining Book Order related input parameters
l_header_rec_out oe_order_pub.header_rec_type; — pl/sql table and record definition to be used as OUT parameters
l_line_tbl_out oe_order_pub.line_tbl_type;
l_header_val_rec_out oe_order_pub.header_val_rec_type;
l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;
l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;
l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;
l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;
l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;
l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;
l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
l_line_val_tbl_out oe_order_pub.line_val_tbl_type;
l_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;
l_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;
l_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;
l_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;
l_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;
l_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;
l_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;
l_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;
l_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;
l_action_request_tbl_out oe_order_pub.request_tbl_type;
l_chr_program_unit_name VARCHAR2 (100); — To store the package and procedure name for logging
l_chr_ret_status VARCHAR2 (1000) := NULL;– To store the error message code returned by API
l_msg_count NUMBER := 0; — To store the number of error messages API has encountered
l_msg_data VARCHAR2 (2000); — To store the error message text returned by API
l_num_api_version NUMBER := 1.0; — API version

CURSOR c_so_details IS

SELECT oh.order_number, ol.*
FROM oe_order_lines_all ol, oe_order_headers_all oh
WHERE oh.header_id = ol.header_id
AND oh.org_id = ol.org_id
AND NVL (ol.cancelled_flag,’N’) = ‘N’
AND oh.order_number = ‘10001690’ — Enter the Order Number
AND ol.line_number = 1 — Enter the Line Number
AND ol.shipment_number = 2 — Enter the Shipment Number
AND ol.flow_status_code = ‘AWAITING_SHIPPING’;


SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = ‘A42485’;

SELECT responsibility_id, application_id
INTO l_resp_id, l_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = ‘Order Management Super User’;

fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

FOR iso_rec IN c_so_details LOOP

l_line_tbl_in (1) := oe_order_pub.g_miss_line_rec;
l_line_tbl_in (1).line_id := iso_rec.line_id;
l_line_tbl_in (1).ordered_quantity := 0;
l_line_tbl_in (1).change_reason := ‘Admin Error’;
l_line_tbl_in (1).change_comments := ‘CANCEL ORDER’;
l_line_tbl_in (1).operation := oe_globals.g_opr_update;

mo_global.init (‘ONT’);
mo_global.set_policy_context (‘S’, iso_rec.org_id);

(p_api_version_number => l_num_api_version,
p_org_id => mo_global.get_current_org_id,
p_init_msg_list => fnd_api.g_false,
p_return_values => fnd_api.g_false,
p_action_commit => fnd_api.g_false,
p_line_tbl => l_line_tbl_in,
x_header_rec => l_header_rec_out,
x_header_val_rec => l_header_val_rec_out,
x_header_adj_tbl => l_header_adj_tbl_out,
x_header_adj_val_tbl => l_header_adj_val_tbl_out,
x_header_price_att_tbl => l_header_price_att_tbl_out,
x_header_adj_att_tbl => l_header_adj_att_tbl_out,
x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_out,
x_header_scredit_tbl => l_header_scredit_tbl_out,
x_header_scredit_val_tbl=> l_header_scredit_val_tbl_out,
x_line_tbl => l_line_tbl_out,
x_line_val_tbl => l_line_val_tbl_out,
x_line_adj_tbl => l_line_adj_tbl_out,
x_line_adj_val_tbl => l_line_adj_val_tbl_out,
x_line_price_att_tbl => l_line_price_att_tbl_out,
x_line_adj_att_tbl => l_line_adj_att_tbl_out,
x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_out,
x_line_scredit_tbl => l_line_scredit_tbl_out,
x_line_scredit_val_tbl => l_line_scredit_val_tbl_out,
x_lot_serial_tbl => l_lot_serial_tbl_out,
x_lot_serial_val_tbl => l_lot_serial_val_tbl_out,
x_action_request_tbl => l_action_request_tbl_out,
x_return_status => l_chr_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);

l_msg_data := NULL;

IF l_chr_ret_status <> ‘S’ THEN

FOR iindx IN 1 .. l_msg_count LOOP

l_msg_data := l_msg_data ‘ .’ oe_msg_pub.get (iindx);




DBMS_OUTPUT.put_line (‘Sales Order => ‘
‘ – Line Number => ‘
‘ – Shipment Number => ‘
‘ Having Line ID=> ‘
‘ Cancelled Successfully’ );

DBMS_OUTPUT.put_line (‘Return Status: ‘ l_chr_ret_status);
DBMS_OUTPUT.put_line (‘Error Message: ‘ l_msg_data);