Importing Sales Orders From Excel

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
   )
   IS
      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;
   BEGIN
      BEGIN
    begin
    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
    null;
    end;

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;
      EXCEPTION
         WHEN OTHERS
         THEN
            debug_log
               (   ‘Order :’
                || p_order_number
                || ‘ does not have any file attached to it to load. Please attach the file and then try running this program’
               );
      END;

      k := DBMS_LOB.ISOPEN (fil);

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

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

      IF k = 1
      THEN
         LOOP
            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 :=
                  ‘begin’
               || 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()||
                  ‘NULL;’
               || fnd_global.NEWLINE ()
               || ‘end;’;

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

            nth := nth + 1;
         END LOOP;

         DBMS_LOB.CLOSE (fil);
      END IF;

      COMMIT;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         k := NULL;
         k := DBMS_LOB.ISOPEN (fil);

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

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

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