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.
- 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.
- 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.
- 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.
-
- insert into custom_lines_load_tbl (column1,column2,….) values ( field1,field2,……)
- Now using these rows with this statement (generated worksheet) we create a simple text file (copy and paste).
- As a next step we upload this file as an attachment to the order header that we have created in the step number 1.
- 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.
- 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;
Oracle eBusiness Suite 