In my previous post on how to Submit GL Import from PL/SQL. The example given in that blog was for importing Journals pertaining to one particular Journal Source.
- What if you had multiple Journal Source like ‘Receivables‘,’Payables‘ etc?
- How will you submit the gl import and make sure that the journals pertaining to all the sources are imported?
- Do we have to submit GL Import for each of the sources separately?
If you have the same questions as above, then please read on. Also have a look at my previous blog on GL Import.
You can use FND_REQUEST.SUBMIT_REQUEST to call the GL Import program from a PL/SQL Procedure.
Before we call the GL Import program, we need to make sure that control records are inserted into GL_INTERFACE_CONTROL Table for each of the sources that we are trying to import.
For example if you have journals from Receivables and Payables, you need to insert two separate records into GL_INTERFACE_CONTROL Table.
One for Receivables Journal source and one for Payables Journal Source. However, make sure that the interface_run_id value is the same for both the records. In the same way you need to insert separate records into GL_INTERFACE_CONTROL Table for each of the sources and keeping the same interface_run_id value for all the records.
The below code snippet is for importing Journals pertaining to Receivables and Payables. You can also download the PDF Version by clicking on Multiple GL Sources link.
[sql]
<pre>DECLARE
l_conc_id NUMBER;
l_int_run_id NUMBER;
l_access_set_id NUMBER;
l_org_id NUMBER := 101;
l_sob_id NUMBER := 101;
l_user_id NUMBER := FND_GLOBAL.USER_ID;
l_resp_id NUMBER := FND_GLOBAL.RESP_ID;
l_resp_app_id NUMBER := FND_GLOBAL.RESP_APPL_ID;
BEGIN
fnd_global.apps_initialize
( user_id => l_user_id -–User Id
,resp_id => l_resp_id -–Responsibility Id
,resp_appl_id => l_resp_app_id -–Responsibility Application Id
);
mo_global.set_policy_context(‘S’,l_org_id);
SELECT gl_journal_import_s.NEXTVAL
INTO l_int_run_id
FROM dual;
SELECT access_set_id
INTO l_access_set_id
FROM gl_access_sets
WHERE name = ‘VISION OPERATIONS SET’;
INSERT INTO gl_interface_control
(
je_source_name
,interface_run_id
,status
,set_of_books_id
)
VALUES
(
‘Receivables’
,l_int_run_id
,’S’
,l_sob_id
);
INSERT INTO gl_interface_control
(
je_source_name
,interface_run_id
,status
,set_of_books_id)
VALUES
(
‘Payables’
,l_int_run_id
,’S’
,l_sob_id –same interface_run_id for both the records.
);
l_conc_id := fnd_request.submit_request
(
application => ‘SQLGL’
,program => ‘GLLEZL’
,description => NULL
,start_time => SYSDATE
,sub_request => FALSE
,argument1 => l_int_run_id -–interface run id
,argument2 => l_access_set_id -–data access set_id
,argument3 => ‘N’ -–post to suspense
,argument4 => NULL -–from date
,argument5 => NULL -–to date
,argument6 => ‘N’ -–summary mode
,argument7 => ‘N’ -–import DFF
,argument8 => ‘Y’ -–backward mode
);
COMMIT;
DBMS_OUTPUT.PUT_LINE(‘GL Import Submitted. Request Id : ‘||l_conc_id);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Error while submitting the GL Import Program.’);
DBMS_OUTPUT.PUT_LINE(‘Error : ‘||SQLCODE||’-‘||SUBSTR(SQLERRM,1,200));
END;
[/sql]
Hope this helps. Looking forward to your comments /feedback / suggestions. If you have any questions, let me know in the comments section.