This discussion is archived
10 Replies Latest reply: Dec 10, 2012 8:39 AM by user12210196 RSS

Journal Import

user12210196 Newbie
Currently Being Moderated
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 Employee ACE
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points