GL Journal Import
GL Journal Import is used to import journals from sub ledgers and external systems into Oracle GL. We can do it manually or programatically. This post describes how to call the GL Journal Import from plsql procedure. We will be looking at importing AR (Receivable) Journal Entries.
Pre Requisite step is to insert the journal records to be imported into GL_INTERFACE Table. The status of the records in the GL_INTERFACE table for new records should be “NEW”.
[sql]
DECLARE
l_conc_id NUMBER;
l_int_run_id NUMBER;
l_access_set_id NUMBER;
l_org_id NUMBER := 81;
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
);
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!! Let me know your comments and feedback. If you have any questions you can leave a comment or email me @ oracle@quest4apps.com.
Thanks for reading.
Thanks Arun ur code really helps me…
Glad that it helped you 🙂
Hi, I’m trying to use this pl-sql code in SQL Developer for Oracle EBS 11i. It’s not working for me, can yo help me please?
Can you paste the code please? Let me know what error you are getting, if any.
Are you using the exact same code?
Thanks,
Raj
Hi i am trying to use this pl-sql code in 11i. It’s not working for me.. can you please help me?.. i am not getting any error.. request id is returning 0..
Hi Venkatesh,
Make sure you have used correct values for the below. Try hard coding the user id, responsibility id and responsibility application id.
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
);
Instead of using the below command:
mo_global.set_policy_context(‘S’,l_org_id);
try using “dbms_application_info.set_client_info(‘‘)” to set the org_id.
Also make sure you have data in GL_INTERFACE Table and GL_INTERFACE_CONTROL Tables.
The below code snippet should have details pertaining to your instance. In the example I have used ‘VISION OPERATIONS SET’.
In your code, use the value applicable to your instance.
SELECT access_set_id
INTO l_access_set_id
FROM gl_access_sets
WHERE name = ‘VISION OPERATIONS SET’ ;
If you still have problems, let me know.
Thanks,
Arun
Hi Arun
Thanks for information
I have using apps initializing like this
fnd_global.apps_initialize (fnd_profile.VALUE (‘USER_ID’),
fnd_profile.VALUE (‘RESP_ID’),
fnd_profile.VALUE (‘RESP_APPL_ID’)
);
I can’t able to see gl_access_sets table in my db
I have used set of book id in argument2 parameter in place of l_access_set_id
But no use..
Please check below code and suggest me
fnd_request.submit_request (application => ‘SQLGL’,
program => ‘GLLEZL’,
description => NULL,
start_time => SYSDATE,
sub_request => FALSE
,argument1 => gl_journal_import_s.Nextval –interface run id
,argument2 => v_sob –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
);
Hi Venkatesh,
Let me take a look at 11i DB and I will update you. Email me @ arunraj@quest4apps.com and we can continue working on this.
Thanks,
Arun
Hi Arun
Thanks for your cooperation. Issue is resolved.
Actually not passing values in interface_control table..
After i used below code the issue got resolved.
gl_journal_import_pkg.populate_interface_control
(user_je_source_name => ‘Manual’,
GROUP_ID => p_grp_id,
set_of_books_id => v_sob,
interface_run_id => v_int_run_id
);
Venkatesh
Ok great. Thanks for letting me know.
Cheers,
Arun
Hi Arun,
My question is If the data is avaialble in GL_Interface table, why again to insert into gl_interface_control table??
I am confused by seeing the log of the Journal Import, it works on gl_interface_control table and mentions as record updated.
Can anyone explain? please?
Thanks,
Parimala.
Hi Parimala,
During the import process the process uses both the tables. The record in the GL_INTERFACE_CONTROL table is what helps the Journal Import process to identify that there are records to be imported.
So you need data in both GL_INTERFACE and GL_INTERFACE_CONTROL Tables.
Whenever you start Journal Import from the Import Journals form, a row is inserted into this table for each source and group id that you specified. so we are trying to replicate the same process using PL/SQL. So we need to insert data into GL_INTERFACE_CONTROL table explicitly.
Let me know if that helps.
Thanks,
Arun