General Ledger & Intercompany

Get Involved. Join the Conversation.

Topic

    Kunal Daya
    Audit who has approved a Journal
    Topic posted October 12, 2019 by Kunal DayaRed Ribbon: 250+ Points, tagged Configuration, Financials, General Ledger, Help Center, Reports, Sample Reports, Setup / Administration, Subledger Accounting 
    53 Views, 8 Comments
    Title:
    Audit who has approved a Journal
    Summary:
    Audit Trail or SQL Report to show all approvers of Journals
    Content:

    Hi Community,

    We require a report which shows who has approved a Journal, please advise if this is available through audit trail or BI reports. 

    Kind Regards,

    Version:
    19C

    Comment

     

    • Julien Dubouis

      Hi Kunal,
      You can create easily an OTBI report using General Ledger - Journals Real Time. It's under the Batch details and the column is "Final Approver".
      Thanks

    • Sudhakara Rao Kovuru

      Transactional level audit not been enabled so you need to build you own custom BI Report else go with OTBI as per above comment by Julien.

    • Kunal Daya

      Thanks guys... OTBI only seems to have the Final Approver ideally, it would be good if we could view every approver of the Journal for more complex rules or who it is pending approval with.

    • James McBride

      Kunal,

      Your question sent me on a curiosity quest to see if this is something that could be done. The result of that quest is the below query. This query is by no means 100% complete, but it will pull the approval history of a journal showing all actions in sequence. I plan to clean this up to my business needs and share it with my team. Please feel free to take it an clean it up as necessary for your purpose. Thanks!

      James

        select  distinct GJH.JE_HEADER_ID  JOURNAL_ID 
      ,GJH.NAME          JOURNAL_NAME
      ,GJH.DATE_CREATED      JOURNAL_DATE
      ,GJH.POSTED_DATE POSTED_DATE
      ,GJH.STATUS JOURNAL_STATUS
      ,GJH.CURRENCY_CODE       CURRENCY_CODE
      ,GLL.NAME                LEDGER_NAME
      ,GJH.PERIOD_NAME PERIOD_NAME
      ,'Q'||GLP.QUARTER_NUM    QUARTER_NUM
      ,GLP.PERIOD_YEAR         YEAR
                      ,GJH.CREATED_BY
      ,GJH.JE_CATEGORY
      ,GJH.JE_SOURCE
      ,GJH.DESCRIPTION  
      ,GJH.JE_BATCH_ID
              , GJH.ACCRUAL_REV_STATUS
      , GLB.NAME
      , GJAL.ACTION_CODE
      , GJAL.ACTION_DATE
      , GJAL.USER_ID
      , PPNF.FULL_NAME
      from GL_JE_HEADERS GJH
          , GL_JE_BATCHES GLB
      , GL_PERIODS GLP
      , GL_LEDGERS  GLL
      , GL_JE_ACTION_LOG GJAL
      , PER_PEOPLE_F PPF
      , PER_PERSON_NAMES_F PPNF
      WHERE GJH.JE_BATCH_ID  = GLB.JE_BATCH_ID
      AND GJH.PERIOD_NAME= GLP.PERIOD_NAME
      and GLP.PERIOD_SET_NAME = GLL.PERIOD_SET_NAME -- Added this condition for getting period set name from ledger
      and GLL.LEDGER_ID = GJH.LEDGER_ID
      and GJAL.JE_BATCH_ID = GJH.JE_BATCH_ID
      and GJAL.USER_ID = PPF.PERSON_NUMBER
      and PPF.PERSON_ID = PPNF.PERSON_ID
      AND SYSDATE BETWEEN PPNF.EFFECTIVE_START_DATE and PPNF.EFFECTIVE_END_DATE
      AND PPNF.NAME_TYPE = 'GLOBAL'
      AND GLB.JE_BATCH_ID = '1384244'
      ORDER BY GJAL.ACTION_DATE DESC