Submit GL Journal Import From PLSQL

Apr 1, 2014

—

in General Ledger, Oracle Apps, Oracle Apps Technical, Oracle PLSQL

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”.


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;

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.

GL Import GL Journal Import Journal Import Oracle General Ledger Oracle GL

Comments

13 responses to “Submit GL Journal Import From PLSQL”

Venkat

August 8, 2014

Thanks Arun ur code really helps me…

Reply
1. sarunraj
  
  August 8, 2014
  
  Glad that it helped you 🙂
  
  Reply
BetoLCC

March 2, 2015

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?

Reply
1. ArunRaj
  
  March 3, 2015
  
  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
  
  Reply
venkatesh

March 25, 2016

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..

Reply
1. ArunRaj
  
  March 25, 2016
  
  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
  
  Reply
  1. venkatesh
    
    March 25, 2016
    
    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
    
    Reply
    1. venkatesh
      
      March 25, 2016
      
      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
      );
      
      Reply
      1. ArunRaj
        
        March 26, 2016
        
        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
venkatesh

March 28, 2016

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

Reply
1. ArunRaj
  
  March 28, 2016
  
  Ok great. Thanks for letting me know.
  
  Cheers,
  Arun
  
  Reply
Parimala

December 1, 2016

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.

Reply
1. ArunRaj
  
  December 13, 2016
  
  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
  
  Reply