General Ledger & Intercompany

Get Involved. Join the Conversation.

Topic

    Prasanna Reka
    How to Store Subledger references like Transaction # on GL...
    Topic posted May 10, 2017 by Prasanna RekaSilver Medal: 2,000+ Points, last edited January 30, 2019, tagged Financials, General Ledger, Period Close / Reconciliation, Reports, Subledger Accounting 
    725 Views, 6 Comments
    Title:
    How to Store Subledger references like Transaction # on GL Journal Lines
    Summary:
    SL GL Link
    Content:

    How to store Sub Ledger transaction reference on GL Journal line and write a query to get data from GL Jounal Lines with source and transaction details. Any body worked on GL SL Query in Oracle Fusion?

    Comment

     

    • Prithis Das

      It is possible to configure Journal Description Rules in Subledger Accounting (SLA) to get details like invoice number, transaction type, customer name, etc. from AR and invoice number, PO number, supplier name etc from AP on the Journal Line description.

      You would need to configure (copy the seeded setup and modify) Subledger Accounting Method, Journal Description Rules and journal Line Rules.

      Keep in mind that for the journal categories that you plan to get the details, you would need to configure these to post in detail instead of summary So you would need to change the subledger accounting options. Be aware that this would significantly add to the amount of lines created on each journal entry and is not considered to be best practice by large organizations. So you would want to do this selectively, for a few journal line types.

      Once you do this, you should get the details that you want on the journal line description instead of the description "Journal import created". You can then build a query on the Journal Entry Lines to get the details on a custom report. However, the canned reports that display JE lines will display these details without modification.

    • Chris Pinto

      Hi Prasanna,

       

      Could you highlight the reason for which you want to store the subledger information in the journal line? 

      The most ideal way of doing this would be to modify the Journal Line Description SLA as mentioned by Prithis. However, there is a seeded report available which readily provide a link between GL and the subledger so you don't have to modify the description lines and increase the quantum in GL. I have attached a report titled "General Ledger and Subledger Accounting by Journal Lines Report" for your reference.

    • Prasanna Reka

      Thanks Prithis and Chris ...volume is high as Prithis said we are not considering detailed posting as it can create lot of overhead on GL.

      We are exploring options to write custom BI report to link GL and Sub ledgers using SLA table links.

    • Ba Kwon

      A couple of suggestions to explore before writing a custom report.

      1. Run the "General Ledger and Subledger Accounting by Journal Lines Report" as noted above. Download the XML data file and open in Excel. You will find additional columns not exposed on the report. If the data file has what you need, then you could create a custom BIP template for the report instead of writing a custom report.

      2. Check OTBI Subledger Accounting - Journals Real Time subject area. If it has what you need, you can create an OTBI report instead of a BIP report.

      If you must write a BIP custom report, you could look at the data model for the standard report to leverage the query. To write your own query is certainly doable but you would have to look at the tables and columns to join with gl_import_references and xla_ae_lines.

      Here's a sample query on JE lines with partial AP details based on GSE Demo data:

      select led.name "Ledger"
      , h.period_name "Period"
      , xal.accounting_date "Accounting Date"
      , b.name "Journal Batch Name"
      , b.description "Batch Description"
      , s.user_je_source_name "Journal Source"
      , h.name "Journal Name"
      , h.description "Journal Description"
      , cat.user_je_category_name "Journal Category"
      , cc.segment1||'-'||cc.segment2||'-'||cc.segment3||'-'||cc.segment4||'-'||cc.segment5||'-'||cc.segment6 "Account Combination"
      , h.je_batch_id "JE Batch ID"
      , h.je_header_id "JE Header ID"
      , l.je_line_num "JE Line"
      , l.accounted_dr "JE Line Debit"
      , l.accounted_cr "JE Line Credit"
      , xal.accounted_dr "Trans Debit"
      , xal.accounted_cr "Trans Credit"
      , sup.vendor_name "Supplier"
      , xal.description "Transaction Description"
      , xal.accounting_class_code "Subledger Account Class"
      from gl_je_batches b
      , gl_je_headers h
      , gl_je_lines l
      , gl_je_sources s
      , gl_je_categories cat
      , gl_code_combinations cc
      , gl_ledgers led
      , gl_import_references gir
      , xla_ae_lines xal
      , poz_suppliers_v sup
      where b.je_batch_id = h.je_batch_id
      and h.je_header_id = l.je_header_id
      and h.je_source = s.je_source_name
      and h.je_category = cat.je_category_name
      and l.code_combination_id = cc.code_combination_id
      and h.je_batch_id = gir.je_batch_id
      and h.je_header_id = gir.je_header_id
      and l.je_line_num = gir.je_line_num
      and gir.gl_sl_link_id = xal.gl_sl_link_id
      and h.ledger_id = led.ledger_id
      and h.ledger_id = xal.ledger_id
      and xal.party_id = sup.vendor_id
      and s.user_je_source_name = 'Payables'
      and cat.je_category_name = 'Purchase Invoices'
      and led.name = 'US Primary Ledger'
      and h.period_name = '08-16'

       

    • Ba Kwon

      For suggestion #1, I was actually thinking about the Account Analysis Report. You may want to run that one too and download the XML data file. If it has additional details for you can create a BIP template for the Account Analysis Report.

    • Jairo Rojas Mendez

      IS Correct For suggestion #1, I was actually thinking about the Account Analysis Report. You may want to run that one too and download the XML data file. If it has additional details for you can create a BIP template for the Account Analysis Report.