1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Apps Journal transactions SQL script

Discussion in 'Oracle Financials' started by Sibusiso, Feb 17, 2010.

  1. Sibusiso

    Sibusiso Guest

    Hi

    I'm and Oracle App dba, I've been give a task in writing this an sql query for the GL module and I'm struggling.
    Please be patient with me as I'm new in the technical developer scene.
    This is what the want

    The Journal transactions should include:

    The Journals transactions should include a full dump of the journals data for the period 01/01/2009 to 31/12/2009.

    Journals
    Book
    Source
    User_Je_Category_Name
    Balance_Type
    Batch_Name
    Batch_Status
    Journal_Num
    JRNL_Name
    JRNL_Desc
    Period_Name
    Currency_Code
    JE_Line_Num
    Effective_Date
    Entry_Date
    Entered_DR
    Entered_CR
    Account_Type
    Description
    Cost_Centre
    Account
    User_Code
    User_Name
    Created_By
    Creation_Date

    This the script that I wrote and I'm battling, please have a look @ it and advise me.
    Code (SQL):

    SELECT   c.SET_OF_BOOKS_ID "BOOKS",
             d.JE_SOURCE_NAME "SOURCE",
             g.USER_JE_CATEGORY_NAME,
             e.NAME "BATCH NAME",
             e.STATUS "Batch_Status",
             c.JE_HEADER_ID "Journal_num",
             c.NAME "JRNL_Name",
             c.PERIOD_NAME,
             f.EFFECTIVE_DATE,
             f.JE_LINE_NUM,
             f.ENTERED_DR,
             f.ENTE RED_CR,
             f.CREATED_BY,
             f.CREATION_DATE,
             c.CURRENCY_COD E,
             h.COST_CENTRE
      FROM   GL_JE_LINES_DFV h,
             GL_JE_HEADERS c,
             GL_JE_BATCHES e,
             GL_JE_LINES f,
             GL_JE_CATEGORIES_TL g,
             GL_JE_INCLUSION_RULES d
     WHERE       e.JE_BATCH_ID = e.JE_BATCH_ID
             AND g.JE_CATEGORY_NAME = d.JE_CATEGORY_NAME
             AND c.JE_HEADER_ID = f.JE_HEADER_ID;


    Thanks
     
  2. apps_expert

    apps_expert Forum Expert

    Messages:
    325
    Likes Received:
    28
    Trophy Points:
    330
    Location:
    Chennai, India
    Re: Oracle financials script

    Hi, I have a script for a similar requirement I had. Try and modify this for your own needs

    Code (SQL):

    SELECT   SUBSTR (B.name, 1, 35) batch,
             SUBSTR (H.name, 1, 15) journal,
             SUBSTR (JE_LINE_NUM, 1, 4) J_LN,
             SUBSTR (h.JE_SOURCE, 1, 9) SOURCE,
             SUBSTR (l.PERIOD_NAME, 1, 6) PERIOD,
             l.accounteD_DR DBT,
             l.accounted_cr CRDT,
             SUBSTR (L.DESCRIPTION, 1, 50) DSC
      FROM   gl_je_lines L,
             gl_je_headers H,
             gl_je_batches B,
             GL_CODE_COMBINATIONS GLC
     WHERE                                                    --h.STATUS = 'P' and
                                              --upper(h.je_source) = 'PAYABLES'and
                        --((l.accounteD_DR > 5048) OR (l.accounted_cr > 5048)) and
                 l.je_header_id = h.je_header_id
             AND b.je_batch_id = h.je_batch_id
             AND h.ACTUAL_FLAG = 'A'
             AND UPPER (l.period_name) IN ('JAN-07')
             AND L.CODE_COMBINATION_ID = GLC.CODE_COMBINATION_ID
             AND (    SUBSTR (GLC.SEGMENT1, 1, 2) = '10'
                  AND SUBSTR (GLC.SEGMENT2, 1, 4) = '0000'
                  AND SUBSTR (GLC.SEGMENT3, 1, 4) = '2999'
                  AND SUBSTR (GLC.SEGMENT4, 1, 2) = '00'
                  AND SUBSTR (GLC.SEGMENT5, 1, 3) = '000'
                  AND SUBSTR (GLC.SEGMENT6, 1, 2) = '00')