Submitting Autoinvoice using SQL Script

We were importing invoices from one Oracle ERP instance to another Oracle ERP instance. We used AR invoice interface. Since Autoinvoice imports invoices only by batch source, it was cumbersome to run Autoinvoice 5 to 6 times and import invoices from all sources in our test system. Our invoice data had invoices and credit memos. Invoice was created using one batch source and credit memo was creatd using another batch source. so import of credit memo failed because of absence of original invoice. So I had to run Autoinvoice again for all remaining batch sources. So overall i had to run Autoinvoice up to 10 times. i got tired and decided to use following script to submit Autoinvoice for each batch. I executed this sql script using a concurrent program. At end of concurrent program i submit it again to process all failed credit memos. it worked very well. I could go for coffee and by the time i came back all invoices were imported.

declare v_debug_flag VARCHAR2(3) := 'Y'; CURSOR c_inv_batches IS select b.batch_source_id , l.batch_source_name , count(9) from ra_interface_lines_all l, ra_batch_sources_all b where l.batch_source_name = b.name group by batch_source_id , batch_source_name order by 3 ; PROCEDURE write_fnd_log ( p_message IN VARCHAR2, p_msg_type VARCHAR2 DEFAULT 'D' ) IS BEGIN IF p_msg_type = 'E' THEN fnd_file.put_line (fnd_file.LOG, TO_CHAR (SYSDATE, 'HH24:MI:SS') || ' ERROR: ' || p_message ); ELSIF p_msg_type = 'W' THEN fnd_file.put_line (fnd_file.LOG, TO_CHAR (SYSDATE, 'HH24:MI:SS') || ' WARNING: ' || p_message ); ELSE IF p_msg_type = 'I' OR (p_msg_type = 'D' AND v_debug_flag = 'Y') THEN fnd_file.put_line (fnd_file.LOG, TO_CHAR (SYSDATE, 'HH24:MI:SS') || ' ' || p_message ); END IF; END IF; END write_fnd_log; -- -- Procedure submit ( p_app varchar2 , p_prog varchar2 , p_wait number , p_arg01 varchar2 default null , p_arg02 varchar2 default null , p_arg03 varchar2 default null , p_arg04 varchar2 default null , p_arg05 varchar2 default null , p_arg06 varchar2 default null , p_arg07 varchar2 default null , p_arg08 varchar2 default null , p_arg09 varchar2 default null , p_arg10 varchar2 default null , p_arg11 varchar2 default null , p_arg12 varchar2 default null , p_arg13 varchar2 default null , p_arg14 varchar2 default null , p_arg15 varchar2 default null , p_arg16 varchar2 default null , p_arg17 varchar2 default null , p_arg18 varchar2 default null , p_arg19 varchar2 default null , p_arg20 varchar2 default null , p_arg21 varchar2 default null , p_arg22 varchar2 default null , p_arg23 varchar2 default null , p_arg24 varchar2 default null , p_arg25 varchar2 default null , p_arg26 varchar2 default null , p_arg27 varchar2 default null ) IS v_request_id NUMBER := NULL; v_request_status BOOLEAN := FALSE; v_rphase VARCHAR2 (240) := NULL; v_rstatus VARCHAR2 (240) := NULL; v_dphase VARCHAR2 (240) := NULL; v_dstatus VARCHAR2 (240) := NULL; v_reqmessage VARCHAR2 (240) := NULL; BEGIN write_fnd_log ('. ' , 'I'); write_fnd_log ('. ' , 'I'); write_fnd_log ('. ' , 'I'); write_fnd_log ('Submitting : ' || p_prog, 'I'); v_request_id := fnd_request.submit_request (p_app, p_prog, null, '', -- submit now FALSE, -- sub request p_arg01, p_arg02, p_arg03, p_arg04, p_arg05, p_arg06, p_arg07, p_arg08, p_arg09, p_arg10, p_arg11, p_arg12, p_arg13, p_arg14, p_arg15, p_arg16, p_arg17, p_arg18, p_arg19, p_arg20, p_arg21, p_arg22, p_arg23, p_arg24, p_arg25, p_arg26, p_arg27, fnd_global.local_chr(0) ); -- Wait for Request, determine completion status IF NVL (v_request_id, 0) <> 0 THEN write_fnd_log ( 'Committing to begin Request ID #' || TO_CHAR (v_request_id), 'D' ); COMMIT; /* For this program i did not want to wait My aim was to submit autoinvoice for all batch sources and be done with it write_fnd_log ('Waiting for Request to complete complete.', 'D'); v_request_status := fnd_concurrent.wait_for_request (v_request_id, p_wait, 0, v_rphase, v_rstatus, v_dphase, v_dstatus, v_reqmessage ); IF UPPER (v_dphase) = 'COMPLETE' AND UPPER (v_dstatus) = 'NORMAL' THEN write_fnd_log ( 'Request ID #' || TO_CHAR (v_request_id) || ' completed normally.', 'D' ); ELSE write_fnd_log ( 'Request ID #' || TO_CHAR (v_request_id) || ' Phase = ' || v_dphase || ', Status = ' || v_dstatus, 'E' ); write_fnd_log ('Request Message = ' || v_reqmessage, 'E'); RAISE xxap_pmt_process_pkg.exc_submit_request_failed; END IF; */ ELSE write_fnd_log ('Request submit unsuccessful ' , 'E'); write_fnd_log ( 'FND_REQUEST.SUBMIT_REQUEST returned : ' || TO_CHAR (v_request_id), 'E' ); RAISE xxap_pmt_process_pkg.exc_submit_request_failed; END IF; end; procedure inv_import (p_batch_id NUMBER, p_batch_name VARCHAR2) IS begin submit( 'AR' , 'RAXMTR' , 60 , 1 , 81 , p_batch_id , p_batch_name , to_date(trunc(SYSDATE),'YYYY/MM/DD HH24:MI:SS') , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , 'Y' , NULL ) ; end; begin FOR l_rec in c_inv_batches LOOP inv_import(l_rec.batch_source_id, l_rec.batch_source_name); END LOOP; end; /