FSQL 11

SQL Query FOR JOURNAL TOTAL IN ORACLE CLOUD FUSION R13

SELECT gjh.posting_acct_seq_value                               journal_number

       ,SUM(xal.accounted_dr)          total_debit

       ,SUM(xal.accounted_cr)          total_credit

 FROM gl_je_headers                   gjh

       ,gl_je_batches                  gjb

       ,gl_ledgers                     gld

       ,gl_je_lines                    gjl

       ,gl_code_combinations           gcc

       ,gl_import_references           gir

       ,xla_ae_lines                   xal

       ,xla_ae_headers                 xah

       ,xla_events                     xae

       ,xla_transaction_entities       xte

       ,hr_operating_units             hou

       ,per_addresses_f                paf

       ,per_location_details_f         pldf

       ,hr_all_organization_units_f    haouf

       ,xle_entity_profiles            xfi

       ,hr_all_organization_units_tl   hroutl_bg

       ,hr_all_organization_units_tl   hroutl_ou

       ,hr_organization_units          glop

       ,per_email_addresses            pea

       ,per_person_names_f             ppnf

 WHERE gjh.ledger_id                     = gld.ledger_id

   AND gjb.je_batch_id                   = gjh.je_batch_id

   AND gjh.je_header_id                  = gjl.je_header_id

   AND gjh.ledger_id                     = gjl.ledger_id

   AND gjl.code_combination_id           = gcc.code_combination_id

   AND gjh.je_header_id                  = gir.je_header_id

   AND gjl.je_line_num                   = gir.je_line_num

   AND gir.gl_sl_link_id                 = xal.gl_sl_link_id

   AND gir.gl_sl_link_table              = xal.gl_sl_link_table

   AND xal.application_id                = xah.application_id

   AND xal.ae_header_id                  = xah.ae_header_id

   AND xah.event_id                      = xae.event_id

   AND xah.application_id                = xae.application_id

   AND xae.application_id                = xte.application_id

   AND xae.entity_id                     = xte.entity_id

   AND hou.organization_id               = haouf.organization_id

   AND haouf.location_id                 = pldf.location_id

   AND pldf.main_address_id              = paf.address_id

   AND xfi.legal_entity_id               = hou.default_legal_context_id

   AND glop.organization_id              = hou.organization_id

   AND hroutl_bg.organization_id         = hou.business_group_id

   AND hroutl_ou.organization_id         = hou.organization_id

   AND hou.set_of_books_id               = gld.ledger_id

   AND gjh.created_by                    = pea.email_address(+)

   AND pea.person_id                     = ppnf.person_id(+)

   AND ppnf.name_type(+)                 = 'GLOBAL'

   AND hou.name                          = :p_bu_name

   AND gjh.posting_acct_seq_value        BETWEEN NVL(:p_journal_from_number, gjh.posting_acct_seq_value) AND NVL(:p_journal_to_number, gjh.posting_acct_seq_value)

   AND gjh.created_by                    = NVL(:p_created_by, gjh.created_by)

  AND TO_DATE(gjh.default_effective_date, 'YYYY-MM-DD')  BETWEEN NVL(TO_DATE(TO_CHAR(:p_from_date_created,'YYYY-MM-DD'), 'YYYY-MM-DD'), TO_DATE(gjh.default_effective_date, 'YYYY-MM-DD'))

                                                    AND NVL(TO_DATE(TO_CHAR(:p_to_date_created,'YYYY-MM-DD'), 'YYYY-MM-DD'), TO_DATE(gjh.default_effective_date, 'YYYY-MM-DD'))

   GROUP BY gjh.posting_acct_seq_value