10 Replies Latest reply: Dec 10, 2012 10:39 AM by user12210196 RSS

    Journal Import

    user12210196
      Hello,

      I am integrating an external system to EBS.

      I am not a EBS developer, so excuse my knowledge.

      From my external system, I have brought GL data into GL_INTERFACE table.

      I can run the Import process through EBS->GL->Journals->Import->Run. It runs perfectly without errors.

      But I want to schedule and run this process at a set time every day. It can be from back end or through a concurrent program which ever is easier. How do I go about doing this?

      Please help me out with some resources or steps to do so.
        • 1. Re: Journal Import
          Hussein Sawwan-Oracle
          I am integrating an external system to EBS.
          What external system?

          Please post the details of the application release, database version and OS.
          But I want to schedule and run this process at a set time every day. It can be from back end or through a concurrent program which ever is easier. How do I go about doing this?
          Use PACKAGE: APPS.FND_REQUEST Package -- http://etrm.oracle.com/pls/et1211d9/etrm_pnav.show_object?c_name=FND_REQUEST&c_owner=APPS&c_type=PACKAGE

          https://forums.oracle.com/forums/search.jspa?threadID=&q=FND_REQUEST.SUBMIT_REQUEST&objID=c3&dateRange=all&userID=&numResults=15&rankBy=10001

          Thanks,
          Hussein
          • 2. Re: Journal Import
            Sanjay Desai
            Hi,

            Pl.follow steps mentioned below.

            (1) You populate the GL_INTERFACE table from external system using PL/SQL stored procedure means write down your GL IMPORT code in PL/SQL stored procedure.

            (2) Register this procedure as Concurrent Program Executable with Execution method = 'PL/SQL Stored Procedure'
            Navigation Path : Application Developer responsibility > Concurrent > Executable

            (3) Register this procedure as Concurrent Program with parameters.
            Navigation Path : Application Developer responsibility > Concurrent > Program

            (4) Then, Schedule this concurrent prorgam using MENU > VIEW > REQUEST > SUBMIT NEW REQUEST

            HTH
            Sanjay
            • 3. Re: Journal Import
              user12210196
              Hussein Sawwan wrote:
              >

              Please post the details of the application release, database version and OS.

              >
              Thank You for the guidance.

              R12.1 and oracle 11g
              • 4. Re: Journal Import
                user12210196
                I am using an ETL process to bring data to GL_INTERFACE table. I can schedule that.

                I only want to schedule the GL import process from the back end. Please let me know how to do just that.
                • 5. Re: Journal Import
                  Hussein Sawwan-Oracle
                  user12210196 wrote:
                  I am using an ETL process to bring data to GL_INTERFACE table. I can schedule that.

                  I only want to schedule the GL import process from the back end. Please let me know how to do just that.
                  Do you mean from PL/SQL? If yes, please use FND_REQUEST.SUBMIT_REQUEST as mentioned above.

                  Thanks,
                  Hussein
                  • 6. Re: Journal Import
                    user12210196
                    Hello,

                    Thanks for the answer, another query.


                    Can I use the program/code in GLLEZL some way to do this? If so Can I please get some steps?

                    Thanks in advance.
                    • 7. Re: Journal Import
                      Hussein Sawwan-Oracle
                      Can I use the program/code in GLLEZL some way to do this? If so Can I please get some steps?
                      What do you mean about using the same code?

                      If you want more details about using the API, then please see the link referenced in one my previous replies above.

                      Thanks,
                      Hussein
                      • 8. Re: Journal Import
                        user12210196
                        I have the following code (is that correct?), But it throws me an error when I submit it as a concurrent request. I know I Am doing something wrong as I am new to EBS. Please help me out to submit this request and run the Journal import process.

                        set serveroutput on

                        define sob_id     = '&1'
                        define out_file = '&2'

                        declare

                        cur_sob_id               number := '&1';
                        cur_interface_run_id     number;
                        cur_request_id          number;

                        cur_start_date          date     := '';
                        cur_end_date          date     := '';

                        begin

                        select gl_journal_import_s.nextval
                        into cur_interface_run_id
                        from dual;

                        insert into gl_interface_control (
                             JE_SOURCE_NAME,
                             STATUS,
                             INTERFACE_RUN_ID,
                             GROUP_ID,
                             SET_OF_BOOKS_ID,
                             PACKET_ID)
                        select distinct
                             i.user_je_source_name,
                             'S',
                             cur_interface_run_id,
                             '',
                             cur_sob_id,
                             ''
                        from gl_interface i,
                             gl_je_sources s
                        where s.user_je_source_name = i.user_je_source_name
                        and nvl(s.attribute1,'N') = 'Y';



                        cur_request_id := fnd_request.submit_request(
                                  'SQLGL',                    -- Application shortname
                             'GLLEZL',                    -- Conc. program shortname
                             '',                         -- description
                             '',                         -- start time
                             FALSE,                         -- Sub-Request
                             to_char(cur_interface_run_id),          -- Interface run id
                             to_char(cur_sob_id),               -- Set of books id
                             'N',                         -- Post errors to suspense
                             to_char(cur_start_date, 'YYYY/MM/DD'),     -- Start date
                             to_char(cur_end_date, 'YYYY/MM/DD'),     -- End date
                             'N',                         -- Summary journal
                             'N',                         -- Import desc. flexfields
                                  chr(0),
                             '', '', '', '', '', '', '', '', '', '',
                             '', '', '', '', '', '', '', '', '', '',
                             '', '', '', '', '', '', '', '', '', '',
                             '', '', '', '', '', '', '', '', '', '',
                             '', '', '', '', '', '', '', '', '', '',
                             '', '', '', '', '', '', '', '', '', '',
                             '', '', '', '', '', '', '', '', '', '',
                             '', '', '', '', '', '', '', '', '', '',
                             '', '', '', '', '', '', '', '', '', '',
                             '', '');


                        dbms_output.put_line('Request_ID: ' || to_char(cur_request_id));

                        exception
                        when others then
                             raise_application_error(-20001, 'Error');

                        end;
                        /

                        commit;


                        set heading on
                        set feedback on


                        select     i.user_je_source_name,
                             count(*) transaction_count
                        from gl_interface i,
                        gl_je_sources s
                        where s.user_je_source_name = i.user_je_source_name
                        and nvl(s.attribute1,'N') = 'Y'
                        group by i.user_je_source_name



                        exit;
                        • 9. Re: Journal Import
                          Sanjay Desai
                          user12210196 wrote:
                          I have the following code (is that correct?), But it throws me an error when I submit it as a concurrent request. I know I Am doing something wrong as I am new to EBS. Please help me out to submit this request and run the Journal import process.

                          set serveroutput on

                          define sob_id     = '&1'
                          define out_file = '&2'

                          declare

                          cur_sob_id               number := '&1';
                          cur_interface_run_id     number;
                          cur_request_id          number;

                          cur_start_date          date     := '';
                          cur_end_date          date     := '';

                          begin

                          select gl_journal_import_s.nextval
                          into cur_interface_run_id
                          from dual;

                          insert into gl_interface_control (
                               JE_SOURCE_NAME,
                               STATUS,
                               INTERFACE_RUN_ID,
                               GROUP_ID,
                               SET_OF_BOOKS_ID,
                               PACKET_ID)
                          select distinct
                               i.user_je_source_name,
                               'S',
                               cur_interface_run_id,
                               '',
                               cur_sob_id,
                               ''
                          from gl_interface i,
                               gl_je_sources s
                          where s.user_je_source_name = i.user_je_source_name
                          and nvl(s.attribute1,'N') = 'Y';



                          cur_request_id := fnd_request.submit_request(
                                    'SQLGL',                    -- Application shortname
                               'GLLEZL',                    -- Conc. program shortname
                               '',                         -- description
                               '',                         -- start time
                               FALSE,                         -- Sub-Request
                               to_char(cur_interface_run_id),          -- Interface run id
                               to_char(cur_sob_id),               -- Set of books id
                               'N',                         -- Post errors to suspense
                               to_char(cur_start_date, 'YYYY/MM/DD'),     -- Start date
                               to_char(cur_end_date, 'YYYY/MM/DD'),     -- End date
                               'N',                         -- Summary journal
                               'N',                         -- Import desc. flexfields
                                    chr(0),
                               '', '', '', '', '', '', '', '', '', '',
                               '', '', '', '', '', '', '', '', '', '',
                               '', '', '', '', '', '', '', '', '', '',
                               '', '', '', '', '', '', '', '', '', '',
                               '', '', '', '', '', '', '', '', '', '',
                               '', '', '', '', '', '', '', '', '', '',
                               '', '', '', '', '', '', '', '', '', '',
                               '', '', '', '', '', '', '', '', '', '',
                               '', '', '', '', '', '', '', '', '', '',
                               '', '');


                          dbms_output.put_line('Request_ID: ' || to_char(cur_request_id));

                          exception
                          when others then
                               raise_application_error(-20001, 'Error');

                          end;
                          /

                          commit;


                          set heading on
                          set feedback on


                          select     i.user_je_source_name,
                               count(*) transaction_count
                          from gl_interface i,
                          gl_je_sources s
                          where s.user_je_source_name = i.user_je_source_name
                          and nvl(s.attribute1,'N') = 'Y'
                          group by i.user_je_source_name



                          exit;
                          Hi,


                          Pl. correct your code as per sample code given below. It's working fine at our end. we have EBS 11i.

                          Apps.FND_GLOBAL.APPS_INITIALIZE (V_User_Id, V_Resp_Id, V_Appl_Id) ;

                          Apps.Gl_Journal_Import_Pkg.Populate_Interface_Control( User_Je_Source_Name => V_Source --'Payroll'
                          , Group_Id => P_Grp_Id
                          , Set_Of_Books_Id => P_Set_Of_Book_Id
                          , Interface_Run_Id => P_Iface_Run_Id
                          , Table_Name => NULL
                          , Processed_Data_Action => NULL ) ; -- Program - Import Journals


                          Update
                          Apps.Gl_Interface_Control
                          Set Group_Id = NULL
                          Where Interface_Run_Id = P_Iface_Run_Id
                          And Je_Source_Name = V_Source;

                          -- DBMS_OUTPUT.PUT_LINE('IFACE ID : '||p_iface_run_id||'/'||V_Source||'/'||P_Grp_Id||'/'||P_Set_Of_Book_Id );

                          Begin
                          V_Conc_Req_Id := Apps.FND_REQUEST.SUBMIT_REQUEST ( Application => 'SQLGL' -- application short name
                          , Program => 'GLLEZL' -- program short name
                          , Description => 'Payroll Import' -- program name
                          , Start_Time => SysDate -- start date
                          , Sub_Request => FALSE -- sub-request
                          , Argument1 => P_Iface_Run_Id -- interface run id
                          , Argument2 => P_Set_Of_Book_Id -- set of books id
                          , Argument3 => 'N' -- error to suspense flag
                          , Argument4 => NULL -- from accounting date
                          , Argument5 => NULL -- to accounting date
                          , Argument6 => 'N' -- l_summary_flag, -- create summary flag
                          , Argument7 => 'N' -- import desc flex flag
                          , Argument8 => 'Y' -- Data security mode flag
                          );
                               COMMIT;
                                    
                          DBMS_OUTPUT.PUT_LINE('IFACE ID : '||p_iface_run_id||'/'||V_Conc_Req_Id );

                          Begin
                          Select Phase_Code, Status_Code, Completion_Text
                          Into V_Phase, V_Status, V_CText
                          From APPS.Fnd_Concurrent_Requests
                          Where Request_Id = V_Conc_Req_Id;
                          Exception
                          When No_Data_Found Then
                          DBMS_OUTPUT.PUT_LINE('Concurrent Request '||V_Conc_Req_Id||' not found in FND_CONCURRENT_REQUESTS ');
                          End;
                               
                          If V_Conc_Req_Id = 0 THEN
                          DBMS_OUTPUT.PUT_LINE('ERROR = '||SQLCODE||'/'||SQLERRM);
                          End If;
                          Exception
                          When Others Then
                               DBMS_OUTPUT.PUT_LINE('Error while importing Payroll data to GL : '||SqlErrM );
                          End;


                          Also, pl.check the concurrent request id generated after running your process.

                          HTH
                          Sanjay

                          Edited by: Sanjay Desai on Dec 7, 2012 8:27 AM
                          • 10. Re: Journal Import
                            user12210196
                            Thank you for the code. I am new to EBS.

                            I am in R 12.1.1, will this code work, looks like it should..

                            Question: What kind of concurrent program type (SQL*plus, PL/SQL etc) should I choose in the definition? Should I also place this code in the TOP\GL\SQL directory? Should I provide parameters in the definition?