Oracle GL Subledger Drill Down Query

 WITH 

entity_sec_hrchy as(

select regexp_substr (val, '[^|]+', 1, 1)   level0

      ,regexp_substr (val, '[^|]+', 1, 2)   level1

  from (select ltrim(sys_connect_by_path(pk1_start_value, '|'), '|') val

          from (select ftn.pk1_start_value

                      ,ftn.parent_pk1_value

                  from fnd_tree_node        ftn

                      ,fnd_tree_version_tl  ftvt

                 where 1                         = 1

                   and ftn.tree_version_id       = ftvt.tree_version_id

                   and ftvt.tree_version_name    = 'XX_ENTITY_SECURITY_HIERARCHY'

                   and ftvt.tree_code            = 'XX_ENTITY_SECURITY_HIERARCHY'

                   and ftvt.tree_structure_code  = 'GL_ACCT_FLEX'

                   and ftvt.language             = 'US')

         where connect_by_isleaf  = 1

    start with parent_pk1_value is null

    connect by nocycle parent_pk1_value = prior pk1_start_value)

),

data_acc_set as(

select gllv.ledger_id

      ,gllv.ledger_name

      ,entity_sec_hrchy.level0

      ,entity_sec_hrchy.level1      flex_segment_value

      ,gllv.legal_entity_id

      ,gllv.legal_entity_name

      ,null                         ledger_set_name

  from entity_sec_hrchy

      ,gl_legal_entities_bsvs         gleb

      ,gl_ledger_le_v                 gllv

      ,fun_user_role_data_asgnmnts    furda

      ,gl_access_set_norm_assign      gasna

 where 1 = 1

   and gllv.legal_entity_id       = gleb.legal_entity_id

   and gleb.flex_segment_value    = entity_sec_hrchy.level1

   and furda.user_guid            = FND_GLOBAL.USER_GUID

   and gasna.access_set_id        = furda.access_set_id

   and furda.active_flag          = 'Y'

   and trunc(sysdate) between trunc(start_date_active) and trunc(nvl(end_date_active,sysdate + 1))

   and gasna.segment_value        = entity_sec_hrchy.level0

union all

select distinct gl.ledger_id

      ,gllv.ledger_name

      ,null

      ,gleb.flex_segment_value

      ,gllv.legal_entity_id

      ,gllv.legal_entity_name

      ,null                         ledger_set_name

  from fun_user_role_data_asgnmnts    furda

      ,gl_access_set_norm_assign      gasa

      ,gl_ledgers                     gl

      ,gl_ledger_le_v                 gllv

      ,gl_legal_entities_bsvs         gleb

      ,gl_access_sets                 gas

 where furda.user_guid            = FND_GLOBAL.USER_GUID

   and gasa.access_set_id         = furda.access_set_id

   and furda.active_flag          = 'Y'

   and trunc(sysdate) between trunc(start_date_active) and trunc(nvl(end_date_active,sysdate + 1))

   and gasa.ledger_id             = gl.ledger_id

   and gl.object_type_code        = 'L'

   and gllv.legal_entity_id       = gleb.legal_entity_id

   and gasa.access_set_id         = gas.access_set_id

   and gas.security_segment_code  = 'F'

   and gleb.legal_entity_id       = gllv.legal_entity_id

   and gllv.ledger_id             = gasa.ledger_id

union all

select distinct gls1.ledger_id

      ,gls1.name

      ,null

      ,gleb.flex_segment_value

      ,gllv.legal_entity_id

      ,gllv.legal_entity_name

      ,gl.name                     ledger_set_name

  from fun_user_role_data_asgnmnts    furda

      ,gl_access_set_norm_assign      gasa

      ,gl_ledgers                     gl

      ,gl_ledger_set_assignments      glsa

      ,gl_ledgers                     gls1

      ,gl_legal_entities_bsvs         gleb

      ,gl_access_sets                 gas

      ,gl_ledger_le_v                 gllv

 where furda.user_guid             = FND_GLOBAL.USER_GUID

   and gasa.access_set_id          = furda.access_set_id

   and furda.active_flag           = 'Y'

   and trunc(sysdate) between trunc(start_date_active) and trunc(nvl(end_date_active,sysdate + 1))

   and gasa.ledger_id              = gl.ledger_id

   and gasa.all_segment_value_flag = 'Y'

   and gl.ledger_id                = glsa.ledger_set_id

   and gls1.ledger_id              = glsa.ledger_id

   and gasa.access_set_id          = gas.access_set_id

   and gas.security_segment_code   = 'F'

   and gleb.legal_entity_id        = gllv.legal_entity_id

   and gllv.ledger_id              = gls1.ledger_id

),

DYN_GL_XX_DATA AS

  (SELECT glg.NAME LEDGER,

    gjh.period_name PERIOD,

    gjh.je_source,

    gjh.je_category,

    gjh.doc_sequence_value DOC_SEQ,

    TO_CHAR(gjh.default_effective_date, 'DD-MON-YYYY','nls_date_language=american') ACCOUNTING_DATE,

    TO_CHAR(gjh.posted_date, 'DD-MON-YYYY','nls_date_language=american') POSTED_DATE,

data_acc_set.flex_segment_value accounting_entity,

    gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 1, data_acc_set.flex_segment_value) accounting_entity_desc,

    gcc.segment2 cost_centre,

    gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 2, gcc.segment2) cost_centre_desc,

    gcc.segment3 nominal_account,

    gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 3, gcc.segment3) nominal_account_desc,

    gcc.segment4 sub_analysis,

    gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 4, gcc.segment4) sub_analysis_desc,

    gcc.segment5 intercompany,

    gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 5, gcc.segment5) intercompany_desc,

    gcc.segment6 location,

    gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 6, gcc.segment6) location_desc,

    gcc.segment7 project,

    gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 7, gcc.segment7) project_desc,

    gcc.segment8 flow,

    gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 8, gcc.segment8) flow_desc,

gcc.segment9 spare1,

gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 9, gcc.segment9) spare1_desc,

gcc.segment10 spare2,

gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 10, gcc.segment10) spare2_desc,

gcc.code_combination_id,

    gjl.currency_code ENT_CUR,

    gjl.entered_dr ENT_DR,

    gjl.entered_cr ENT_CR,

    NVL(gjl.entered_dr,0)-NVL(gjl.entered_cr,0) ENT_NET,

    gjl.accounted_dr ACCTD_DR,

    gjl.accounted_cr ACCTD_CR,

    NVL(gjl.accounted_dr,0)-NVL(gjl.accounted_cr,0) ACCTD_NET,

    xal.accounting_class_code ACTCLS_CODE,

    xal.entered_dr XENTERED_DR,

    xal.entered_cr XENTERED_CR,

    xal.accounted_dr XACCOUNTED_DR,

    xal.accounted_cr XACCOUNTED_CR,

    nvl(xal.currency_conversion_type,gjl.currency_conversion_type) CUR_CONV_TYPE,

    TO_CHAR(nvl(xal.currency_conversion_date,gjl.currency_conversion_date), 'DD-MON-YYYY','nls_date_language=american') CUR_CONV_DATE,

    nvl(xal.currency_conversion_rate,gjl.currency_conversion_rate) CUR_CONV_RATE,

    gjb.name BATCH,

    gjb.description BATCH_DESC,

    gjh.name JOUR_NAME,

    gjh.description JOUR_DESC,

    gjl.description JOURLINE_DESC,

    gjh.created_by JOUR_PREP,

(SELECT user_id FROM gl_je_action_log WHERE je_batch_id = gjb.je_batch_id AND action_code = 'POSTED') POSTED_BY,

fsv_acc_seq.header_name      ACC_SEQUENCE_NAME,

    gjh.posting_acct_seq_value   ACC_SEQUENCE_NUMBER,

    fsv_rep_seq.header_name      REP_SEQUENCE_NAME,

    gjh.close_acct_seq_value     REP_SEQUENCE_NUMBER,

gjl.je_line_num              JOURNAL_LINE_NUMBER,

(SELECT display_name FROM per_person_names_f WHERE person_id = gjb.approver_employee_id AND name_type = 'GLOBAL') JOUR_APPR,

    --DECODE(gjh.accrual_rev_effective_date, NULL, 'N', 'Y') REV_FLAG,

decode(gjh.accrual_rev_status, 'R', 'Reversed', 'Not Reversed') REV_FLAG,

    TO_CHAR(gjh.accrual_rev_effective_date, 'DD-MON-YYYY','nls_date_language=american') REV_DATE,

    xev.entity_id,

    xah.ae_header_id,

gjl.REFERENCE_1 GL_REF1,

xal.accounting_class_code,

    xal.ae_line_num

  FROM xla_events xev,

    xla_ae_headers xah,

    xla_ae_lines xal,

    gl_import_references gir,

    gl_je_headers gjh,

fun_seq_versions fsv_acc_seq,

    fun_seq_versions fsv_rep_seq,

    gl_je_lines gjl,

    gl_ledgers glg,

    gl_je_batches gjb,

gl_code_combinations gcc,

gl_je_sources gjss,

gl_je_categories gjcc,

data_acc_set

  WHERE 1                        =1

  AND xah.event_id               = xev.event_id

  AND xah.ae_header_id           = xal.ae_header_id

  AND xah.gl_transfer_status_code= 'Y'

  AND xal.gl_sl_link_id          =gir.gl_sl_link_id

  AND gir.gl_sl_link_table       = xal.gl_sl_link_table

  AND gjl.je_header_id           =gjh.je_header_id

  AND gjh.je_header_id           =gir.je_header_id

  AND gjl.je_header_id           =gir.je_header_id

  AND gir.je_line_num            =gjl.je_line_num

  AND glg.ledger_id              = gjh.ledger_id

  AND gjh.je_batch_id            = gjb.je_batch_id

  --AND GCC.SEGMENT3 = '5002002'

  --and gjb.je_batch_id            = gjal_ct.je_batch_id

  AND gcc.code_combination_id    =xal.code_combination_id

  AND gcc.code_combination_id    =gjl.code_combination_id

  and gjh.posting_acct_seq_version_id  = fsv_acc_seq.seq_version_id(+)

  and gjh.close_acct_seq_version_id    = fsv_rep_seq.seq_version_id(+)

  AND gjss.je_source_name = gjh.je_source

  AND gjcc.je_category_name = gjh.je_category

  AND gjss.LANGUAGE = USERENV('LANG')

  AND gjcc.LANGUAGE = USERENV('LANG')

  AND gjh.status                 ='P'

  AND gjh.actual_flag            ='A'

  AND gjh.je_source IN ('Payables','Receivables','Cash Management','Assets')

  --AND glg.NAME                 = :P_LEDGER

   and (data_acc_set.ledger_name = :p_ledger

    or data_acc_set.ledger_set_name = :p_ledger)

   and glg.ledger_id             = data_acc_set.ledger_id

and ((coalesce(null, :p_acct_entity) is null)

    or (data_acc_set.flex_segment_value in (:p_acct_entity)))

  and ((coalesce(null, :p_cost_centre) is null)

    or (gcc.segment2 in (:p_cost_centre)))

  and ((coalesce(null, :p_account) is null)

    or (gcc.segment3 in (:p_account)))

  and ((coalesce(null, :p_sub_analysis) is null)

    or (gcc.segment4 in (:p_sub_analysis)))

  and ((coalesce(null, :p_intercmpny) is null)

    or (gcc.segment5 in (:p_intercmpny)))

  and ((coalesce(null, :p_location) is null)

    or (gcc.segment6 in (:p_location)))

  and ((coalesce(null, :p_project) is null)

    or (gcc.segment7 in (:p_project)))

  and ((coalesce(null, :p_flow) is null)

    or (gcc.segment8 in (:p_flow)))

  and ((coalesce(null, :p_spare1) is null)

    or (gcc.segment9 in (:p_spare1)))

  and ((coalesce(null, :p_spare2) is null)

    or (gcc.segment10 in (:p_spare2)))

 and ((coalesce(null, :P_JE_SOURCE) is null)

    or (gjss.user_je_source_name in (:P_JE_SOURCE)))

 and ((coalesce(null, :P_JE_CATEGORY) is null)

    or (gjcc.user_je_category_name in (:P_JE_CATEGORY)))

and gjh.period_name in (select period_name

                             from gl_periods

                            where start_date     >= (select start_date

                                                       from gl_periods

                                                      where period_name     = :P_PERIOD_FROM

                                                        and period_set_name = 'XX_GLOBAL_CAL')

                              and end_date       <= (select end_date

                                                       from gl_periods

                                                      where period_name     = :P_PERIOD_TO

                                                        and period_set_name = 'XX_GLOBAL_CAL')

                              and period_set_name = 'XX_GLOBAL_CAL')  

 

  ),

  DYN_INVOICE_TYPE AS

  (SELECT MEANING,

    LOOKUP_CODE

  FROM FND_LOOKUP_VALUES

  WHERE LOOKUP_TYPE='INVOICE TYPE'

  AND LANGUAGE     =USERENV('LANG')

  ),

  DYN_VENDOR_TYPE AS

  (SELECT MEANING,

    LOOKUP_CODE

  FROM FND_LOOKUP_VALUES

  WHERE LOOKUP_TYPE='POZ_VENDOR_TYPE'

  AND LANGUAGE     =USERENV('LANG')

  ),

  DYN_INVOICE_LINE_TYPE AS

  (SELECT MEANING,

    LOOKUP_CODE

  FROM FND_LOOKUP_VALUES

  WHERE LOOKUP_TYPE='INVOICE LINE TYPE'

  AND LANGUAGE     =USERENV('LANG')

  )

--Purchase Invoices

SELECT DGSD.LEDGER,

  DGSD.PERIOD,

  DGSD.ACCOUNTING_DATE,

  DGSD.accounting_entity,

  DGSD.cost_centre,

  DGSD.nominal_account,

  DGSD.sub_analysis,

  DGSD.intercompany,

  DGSD.location,

  DGSD.project,

  DGSD.flow,

  DGSD.spare1,

  DGSD.spare2,

  (DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||

  DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,

  DGSD.JE_SOURCE,

  DGSD.JE_CATEGORY,

  DGSD.DOC_SEQ,

  DGSD.POSTED_DATE,

  DGSD.ENT_CUR,

  DGSD.XENTERED_DR ENT_DR,

  DGSD.XENTERED_CR ENT_CR,

  nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,

  DGSD.XACCOUNTED_DR ACCTD_DR,

  DGSD.XACCOUNTED_CR ACCTD_CR,

  nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,

  DGSD.CUR_CONV_TYPE,

  DGSD.CUR_CONV_DATE,

  DGSD.CUR_CONV_RATE,

  DGSD.BATCH,

  DGSD.BATCH_DESC,

  DGSD.JOUR_NAME,

  DGSD.JOUR_DESC,

  DGSD.JOURLINE_DESC,

  DGSD.JOUR_PREP,

  DGSD.JOUR_APPR,

  DGSD.REV_FLAG,

  DGSD.REV_DATE,

  DGSD.ACC_SEQUENCE_NAME,

  DGSD.ACC_SEQUENCE_NUMBER,

  DGSD.REP_SEQUENCE_NAME,

  DGSD.REP_SEQUENCE_NUMBER,

  DGSD.JOURNAL_LINE_NUMBER,

  DGSD.ACTCLS_CODE AP_ACCOUNTING_TYPE,

  aia.invoice_num AP_INV_NUMBER,

  (SELECT MEANING

  FROM DYN_INVOICE_TYPE

  WHERE LOOKUP_CODE = AIA.INVOICE_TYPE_LOOKUP_CODE

  ) AP_INV_TYPE,

  aia.source AP_SOURCE,

  TO_CHAR(aia.invoice_date, 'DD-MON-YYYY','nls_date_language=american') AP_INV_DATE,

  aia.invoice_amount AP_INVOICE_AMOUNT,

  hp.party_name AP_SUP_NAME,

  aps.segment1 AP_SUP_NUM,

  poss.vendor_site_code AP_SUPSITE,

  (SELECT MEANING

  FROM DYN_VENDOR_TYPE

  WHERE LOOKUP_CODE = aps.VENDOR_TYPE_LOOKUP_CODE

  ) AP_SUPTYPE,

  (SELECT MAX(PU.USERNAME)

       FROM ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

PER_USERS PU 

  WHERE aid.invoice_id = ail.invoice_id

  and aid.invoice_distribution_id = xdl.source_distribution_id_num_1

  and aia.invoice_id = ail.invoice_id

  and xdl.source_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

      and PU.PERSON_ID=ail.requester_id

  )  AP_REQUESTOR,

  aia.description AP_INVDESC,

  (SELECT MAX(ail.description)

       FROM ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl

  WHERE aid.invoice_id = ail.invoice_id

  and aid.invoice_distribution_id = xdl.source_distribution_id_num_1

  and aia.invoice_id = ail.invoice_id

  and xdl.source_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INVLINEDESC,

  (SELECT MAX(dtf.MEANING)

       FROM ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl,

DYN_INVOICE_LINE_TYPE dtf

  WHERE aid.invoice_id = ail.invoice_id

  and aid.invoice_distribution_id = xdl.source_distribution_id_num_1

  and aia.invoice_id = ail.invoice_id

  and xdl.source_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and dtf.LOOKUP_CODE = AIL.LINE_TYPE_LOOKUP_CODE

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INVLINE_TYPE,

  (SELECT SEGMENT1 FROM PO_HEADERS_ALL WHERE PO_HEADER_ID=AIA.PO_HEADER_ID

  ) AP_PO,

  (SELECT MAX(prha.requisition_number)

  FROM po_distributions_all pda ,

    por_req_distributions_all prda ,

    por_requisition_lines_all prla ,

    por_requisition_headers_all prha

  WHERE aia.po_header_id         = pda.po_header_id

  AND pda.req_distribution_id    = prda.distribution_id

  AND prda.requisition_line_id   = prla.requisition_line_id

  AND prla.requisition_header_id = prha.requisition_header_id

  ) AP_POREQ,

  (SELECT LISTAGG(rsh.RECEIPT_NUM,'|') WITHIN GROUP (

  ORDER BY rsh.RECEIPT_NUM)

  FROM rcv_shipment_headers rsh,

    rcv_shipment_lines rsl

  WHERE rsh.shipment_header_id=rsl.shipment_header_id

  AND rsl.po_header_id        = aia.po_header_id

  ) AP_RECEIPT,

  (SELECT LISTAGG(check_number,'|') WITHIN GROUP (ORDER BY check_number)

   FROM (SELECT aca.check_number,sum(aipa.amount) AMT

                 FROM ap_checks_all aca,

                           ap_invoice_payments_all aipa

              WHERE aca.check_id  = aipa.check_id

                   AND aipa.invoice_id = aia.invoice_id

              GROUP BY aca.check_number)

 WHERE AMT!=0 -- To exclude cancelled checks

  ) AP_PMT,

  att.name  AP_PAYMENT_TERMS,

  NULL AP_PAYMENT_DUE_DATE,

  to_char(aia.terms_date,'DD-MON-YYYY','nls_date_language=american') AP_PAYMENT_TERM_DATE,

  NULL AP_PAYMENT_DATE,

  NULL AP_PAYMENT_STATUS,

  NULL AP_XX_BANK_ACC_NUMB,

  NULL FA_ACCOUNTING_TYPE,

  NULL FA_ASSET_NUM,

  NULL FA_TRANS_CATE,

  NULL FA_MAJ_CATE,

  NULL FA_MIN_CATE,

  NULL FA_ASSET_TYPE,

  NULL FA_ASSET_DESC,

  NULL FA_DATE_PLACED,

  NULL FA_ASST_LOC_COUNTRY,

  NULL FA_ASST_LOC_STATE,

  NULL FA_ASST_LOC_CITY,

  NULL FA_ASST_LOC_SPARE,

  NULL FA_ASST_REM_LIFE,

  NULL FA_ASST_DEP_MET,

  NULL FA_USEFUL_LIFE,

  NULL FA_BOOK_TYPE_CODE,

  NULL FA_LEASE_NUMBER,

  NULL FA_LEASE_DESCRIPTION,

  NULL FA_LEASE_START_DATE,

  NULL FA_LEASE_END_DATE,

  NULL AR_ACCOUNTING_TYPE,

  NULL AR_TRXNUM,

  NULL AR_INV_LINE_DESC,

  NULL AR_TRXDATE,

  NULL AR_TRXAMT,

  NULL AR_CUSTNAME,

  NULL AR_CUSTSITE,

  NULL AR_CUSTNUM,

  NULL AR_TRXTYPE,

  NULL AR_TRXSOURCE,

  NULL AR_RECEIPTNUM,

  NULL AR_RECEIPTDATE,

  NULL CE_TRX_TYPE,

  NULL CE_TRX_DATE,

  NULL CE_REF,

  NULL CE_DESC,

  NULL CE_STMT_IDENTIFIER,

  NULL CE_STMT_DATE,

  NULL CE_XX_BANK_ACC,

  DGSD.GL_REF1

FROM DYN_GL_XX_DATA DGSD,

  ap_invoices_all aia,

  xla_transaction_entities xte,

  poz_suppliers aps,

  ap_terms_tl  att,

  poz_supplier_sites_all_m poss,

  hz_parties hp

WHERE 1                         =1

AND aia.invoice_id              = xte.source_id_int_1

AND aps.party_id                = hp.party_id(+)

AND aia.vendor_id               =aps.vendor_id(+)

AND aia.vendor_site_id          = poss.vendor_site_id(+)

AND aps.vendor_id               = poss.vendor_id(+)

and aia.terms_id                     = att.term_id

and att.language                     = 'US'

AND DGSD.entity_id              = xte.entity_id

AND xte.entity_code             = 'AP_INVOICES'

AND DGSD.je_source              = 'Payables'

AND DGSD.je_category            = 'Purchase Invoices'

UNION ALL

--Payments

SELECT DISTINCT DGSD.LEDGER,

  DGSD.PERIOD,

  DGSD.ACCOUNTING_DATE,

  DGSD.accounting_entity,

  DGSD.cost_centre,

  DGSD.nominal_account,

  DGSD.sub_analysis,

  DGSD.intercompany,

  DGSD.location,

  DGSD.project,

  DGSD.flow,

  DGSD.spare1,

  DGSD.spare2,

  (DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||

  DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,

  DGSD.JE_SOURCE,

  DGSD.JE_CATEGORY,

  DGSD.DOC_SEQ,

  DGSD.POSTED_DATE,

  DGSD.ENT_CUR,

  DGSD.XENTERED_DR ENT_DR,

  DGSD.XENTERED_CR ENT_CR,

  nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,

  DGSD.XACCOUNTED_DR ACCTD_DR,

  DGSD.XACCOUNTED_CR ACCTD_CR,

  nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,

  DGSD.CUR_CONV_TYPE,

  DGSD.CUR_CONV_DATE,

  DGSD.CUR_CONV_RATE,

  DGSD.BATCH,

  DGSD.BATCH_DESC,

  DGSD.JOUR_NAME,

  DGSD.JOUR_DESC,

  DGSD.JOURLINE_DESC,

  DGSD.JOUR_PREP,

  DGSD.JOUR_APPR,

  DGSD.REV_FLAG,

  DGSD.REV_DATE,

  DGSD.ACC_SEQUENCE_NAME,

  DGSD.ACC_SEQUENCE_NUMBER,

  DGSD.REP_SEQUENCE_NAME,

  DGSD.REP_SEQUENCE_NUMBER,

  DGSD.JOURNAL_LINE_NUMBER,

  DGSD.ACTCLS_CODE AP_ACCOUNTING_TYPE,

  (SELECT max(aia.invoice_num)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl 

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INV_NUMBER,

  (SELECT max(dff.MEANING)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

DYN_INVOICE_TYPE dff

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and AIA.INVOICE_TYPE_LOOKUP_CODE = dff.LOOKUP_CODE

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INV_TYPE,  

    (SELECT max(aia.source)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl 

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_SOURCE,

   (SELECT max(TO_CHAR(aia.invoice_date, 'DD-MON-YYYY','nls_date_language=american') )

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl 

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INV_DATE,  

  (SELECT max(aia.invoice_amount)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl 

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INVOICE_AMOUNT,

  (SELECT max(hp.party_name)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

hz_parties hp,

poz_suppliers aps

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  and aps.vendor_id = aia.vendor_id

  and aps.party_id = hp.party_id

  ) AP_SUP_NAME,

  (SELECT max(aps.segment1)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

poz_suppliers aps

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  and aps.vendor_id = aia.vendor_id

  ) AP_SUP_NUM,

  (SELECT max(poss.vendor_site_code)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

poz_suppliers aps,

poz_supplier_sites_all_m poss

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  and aps.vendor_id = aia.vendor_id

  and poss.vendor_id = aps.vendor_id

  AND poss.vendor_site_id  = aia.vendor_site_id

  )  AP_SUPSITE,

  (SELECT max(dff.MEANING)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

poz_suppliers aps,

DYN_VENDOR_TYPE dff

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  and aps.vendor_id = aia.vendor_id

  and dff.LOOKUP_CODE = aps.VENDOR_TYPE_LOOKUP_CODE

  )  AP_SUPTYPE,

  (SELECT max(pu.username)

       from ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

PER_USERS pu

  where aid.invoice_id = ail.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

      and PU.PERSON_ID=ail.requester_id

  ) AP_REQUESTOR,

   (SELECT max(aia.description)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl 

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INVDESC,

  (SELECT max(ail.description)

       from ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl 

  where aid.invoice_id = ail.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INVLINEDESC,

  (SELECT max(dff.MEANING)

       from ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

DYN_INVOICE_LINE_TYPE dff

  where aid.invoice_id = ail.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  and dff.LOOKUP_CODE = AIL.LINE_TYPE_LOOKUP_CODE

  )  AP_INVLINE_TYPE,

  (SELECT max(pha.SEGMENT1)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl,

             PO_HEADERS_ALL pha

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  and pha.PO_HEADER_ID=AIA.PO_HEADER_ID

  ) AP_PO,

  (SELECT max(prha.requisition_number)

    FROM po_headers_all poh, 

    po_distributions_all pda , 

    por_req_distributions_all prda , 

    por_requisition_lines_all prla , 

    por_requisition_headers_all prha ,

ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl 

    WHERE poh.po_header_id = pda.po_header_id 

    AND poh.po_header_id = aia.po_header_id

    AND pda.req_distribution_id = prda.distribution_id 

    AND prda.requisition_line_id = prla.requisition_line_id 

    AND prla.requisition_header_id = prha.requisition_header_id

and aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_POREQ,

  (SELECT LISTAGG(RECEIPT_NUM,'|') WITHIN GROUP (

  ORDER BY RECEIPT_NUM)

  FROM

  (SELECT rsh.RECEIPT_NUM

  FROM rcv_shipment_headers rsh,

    rcv_shipment_lines rsl,

ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl 

  WHERE rsh.shipment_header_id=rsl.shipment_header_id

  AND rsl.po_header_id        = aia.po_header_id

  and aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  and aid.invoice_distribution_id = xdl.applied_to_dist_id_num_1

  and xdl.applied_to_distribution_type = 'AP_INV_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  GROUP BY rsh.RECEIPT_NUM)

  )  AP_RECEIPT,

  TO_CHAR(aca.check_number) AP_PMT,

  NULL AP_PAYMENT_TERMS,

  --to_char(aps.due_date,'DD-MON-YYYY','nls_date_language=american')  AP_PAYMENT_DUE_DATE,

  NULL AP_PAYMENT_DUE_DATE,

  NULL AP_PAYMENT_TERM_DATE,

  to_char(aca.check_date,'DD-MON-YYYY','nls_date_language=american') AP_PAYMENT_DATE,

  flv.meaning  AP_PAYMENT_STATUS,

  ieba.bank_account_num AP_XX_BANK_ACC_NUMB,

  NULL FA_ACCOUNTING_TYPE,

  NULL FA_ASSET_NUM,

  NULL FA_TRANS_CATE,

  NULL FA_MAJ_CATE,

  NULL FA_MIN_CATE,

  NULL FA_ASSET_TYPE,

  NULL FA_ASSET_DESC,

  NULL FA_DATE_PLACED,

  NULL FA_ASST_LOC_COUNTRY,

  NULL FA_ASST_LOC_STATE,

  NULL FA_ASST_LOC_CITY,

  NULL FA_ASST_LOC_SPARE,

  NULL FA_ASST_REM_LIFE,

  NULL FA_ASST_DEP_MET,

  NULL FA_USEFUL_LIFE,

  NULL FA_BOOK_TYPE_CODE,

  NULL FA_LEASE_NUMBER,

  NULL FA_LEASE_DESCRIPTION,

  NULL FA_LEASE_START_DATE,

  NULL FA_LEASE_END_DATE,

  NULL AR_ACCOUNTING_TYPE,

  NULL AR_TRXNUM,

  NULL AR_INV_LINE_DESC,

  NULL AR_TRXDATE,

  NULL AR_TRXAMT,

  NULL AR_CUSTNAME,

  NULL AR_CUSTSITE,

  NULL AR_CUSTNUM,

  NULL AR_TRXTYPE,

  NULL AR_TRXSOURCE,

  NULL AR_RECEIPTNUM,

  NULL AR_RECEIPTDATE,

  NULL CE_TRX_TYPE,

  NULL CE_TRX_DATE,

  NULL CE_REF,

  NULL CE_DESC,

  NULL CE_STMT_IDENTIFIER,

  NULL CE_STMT_DATE,

  NULL CE_XX_BANK_ACC,

  DGSD.GL_REF1

FROM DYN_GL_XX_DATA DGSD,

  ap_checks_all aca,

  ap_invoice_payments_all aipa,

  ap_payment_schedules_all aps,

  fnd_lookup_values flv,

  iby_ext_bank_accounts ieba,

  xla_transaction_entities xte

WHERE 1                              =1

AND aca.check_id                     = xte.source_id_int_1

AND DGSD.entity_id                   = xte.entity_id

and aipa.check_id                    = aca.check_id(+)

and aps.invoice_id  = aipa.invoice_id

and aca.status_lookup_code           = flv.lookup_code(+)

and aca.external_bank_account_id     = ieba.ext_bank_account_id(+)

and flv.lookup_type               (+)= 'CHECK STATE'

and flv.language                  (+)= 'US'

AND DGSD.je_source                   = 'Payables'

AND DGSD.je_category                 = 'Payments'

AND xte.entity_code                  = 'AP_PAYMENTS'

UNION ALL

--Reconciled Payments

SELECT DISTINCT DGSD.LEDGER,

  DGSD.PERIOD,

  DGSD.ACCOUNTING_DATE,

  DGSD.accounting_entity,

  DGSD.cost_centre,

  DGSD.nominal_account,

  DGSD.sub_analysis,

  DGSD.intercompany,

  DGSD.location,

  DGSD.project,

  DGSD.flow,

  DGSD.spare1,

  DGSD.spare2,

  (DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||

  DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,

  DGSD.JE_SOURCE,

  DGSD.JE_CATEGORY,

  DGSD.DOC_SEQ,

  DGSD.POSTED_DATE,

  DGSD.ENT_CUR,

  DGSD.XENTERED_DR ENT_DR,

  DGSD.XENTERED_CR ENT_CR,

  nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,

  DGSD.XACCOUNTED_DR ACCTD_DR,

  DGSD.XACCOUNTED_CR ACCTD_CR,

  nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,

  DGSD.CUR_CONV_TYPE,

  DGSD.CUR_CONV_DATE,

  DGSD.CUR_CONV_RATE,

  DGSD.BATCH,

  DGSD.BATCH_DESC,

  DGSD.JOUR_NAME,

  DGSD.JOUR_DESC,

  DGSD.JOURLINE_DESC,

  DGSD.JOUR_PREP,

  DGSD.JOUR_APPR,

  DGSD.REV_FLAG,

  DGSD.REV_DATE,

  DGSD.ACC_SEQUENCE_NAME,

  DGSD.ACC_SEQUENCE_NUMBER,

  DGSD.REP_SEQUENCE_NAME,

  DGSD.REP_SEQUENCE_NUMBER,

  DGSD.JOURNAL_LINE_NUMBER,

  DGSD.ACTCLS_CODE AP_ACCOUNTING_TYPE,

  (SELECT max(aia.invoice_num)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

ap_payment_hist_dists aipd

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INV_NUMBER,

  (SELECT max(dff.MEANING)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

DYN_INVOICE_TYPE dff,

ap_payment_hist_dists aipd

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and AIA.INVOICE_TYPE_LOOKUP_CODE = dff.LOOKUP_CODE

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INV_TYPE,  

    (SELECT max(aia.source)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

ap_payment_hist_dists aipd

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_SOURCE,

   (SELECT max(TO_CHAR(aia.invoice_date, 'DD-MON-YYYY','nls_date_language=american') )

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

ap_payment_hist_dists aipd

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INV_DATE,  

  (SELECT max(aia.invoice_amount)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

ap_payment_hist_dists aipd

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INVOICE_AMOUNT,

  (SELECT max(hp.party_name)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

hz_parties hp,

poz_suppliers aps,

ap_payment_hist_dists aipd

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  and aps.vendor_id = aia.vendor_id

  and aps.party_id = hp.party_id

  ) AP_SUP_NAME,

  (SELECT max(aps.segment1)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

poz_suppliers aps,

ap_payment_hist_dists aipd

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  and aps.vendor_id = aia.vendor_id

  ) AP_SUP_NUM,

  (SELECT max(poss.vendor_site_code)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

poz_suppliers aps,

poz_supplier_sites_all_m poss ,

ap_payment_hist_dists aipd

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  and aps.vendor_id = aia.vendor_id

  and poss.vendor_id = aps.vendor_id

  AND poss.vendor_site_id  = aia.vendor_site_id

  )  AP_SUPSITE,

  (SELECT max(dff.MEANING)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

poz_suppliers aps,

DYN_VENDOR_TYPE dff ,

ap_payment_hist_dists aipd

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  and aps.vendor_id = aia.vendor_id

  and dff.LOOKUP_CODE = aps.VENDOR_TYPE_LOOKUP_CODE

  )  AP_SUPTYPE,

  (SELECT max(pu.username)

       from ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

PER_USERS pu ,

ap_payment_hist_dists aipd

  where aid.invoice_id = ail.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

      and PU.PERSON_ID=ail.requester_id

  ) AP_REQUESTOR,

   (SELECT max(aia.description)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl  ,

ap_payment_hist_dists aipd

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INVDESC,

  (SELECT max(ail.description)

       from ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl  ,

ap_payment_hist_dists aipd

  where aid.invoice_id = ail.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_INVLINEDESC,

  (SELECT max(dff.MEANING)

       from ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

DYN_INVOICE_LINE_TYPE dff ,

ap_payment_hist_dists aipd

  where aid.invoice_id = ail.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  and dff.LOOKUP_CODE = AIL.LINE_TYPE_LOOKUP_CODE

  )  AP_INVLINE_TYPE,

  (SELECT max(pha.SEGMENT1)

       from ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl,

             PO_HEADERS_ALL pha ,

ap_payment_hist_dists aipd

  where aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  and pha.PO_HEADER_ID=AIA.PO_HEADER_ID

  ) AP_PO,

  (SELECT max(prha.requisition_number)

    FROM po_headers_all poh, 

    po_distributions_all pda , 

    por_req_distributions_all prda , 

    por_requisition_lines_all prla , 

    por_requisition_headers_all prha ,

ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl  ,

ap_payment_hist_dists aipd

    WHERE poh.po_header_id = pda.po_header_id 

    AND poh.po_header_id = aia.po_header_id

    AND pda.req_distribution_id = prda.distribution_id 

    AND prda.requisition_line_id = prla.requisition_line_id 

    AND prla.requisition_header_id = prha.requisition_header_id

and aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  )  AP_POREQ,

  (SELECT LISTAGG(RECEIPT_NUM,'|') WITHIN GROUP (

  ORDER BY RECEIPT_NUM)

  FROM

  (SELECT rsh.RECEIPT_NUM 

  FROM rcv_shipment_headers rsh,

    rcv_shipment_lines rsl,

ap_invoices_all aia,

             ap_invoice_lines_all ail,

ap_invoice_distributions_all aid,

xla_distribution_links xdl ,

ap_payment_hist_dists aipd

  WHERE rsh.shipment_header_id=rsl.shipment_header_id

  AND rsl.po_header_id        = aia.po_header_id

  and aia.invoice_id = ail.invoice_id

      and ail.line_number = aid.invoice_line_number

  and aid.invoice_id = aia.invoice_id

  AND aipd.payment_hist_dist_id = xdl.applied_to_dist_id_num_1

      AND aipd.invoice_distribution_id = aid.invoice_distribution_id

      AND xdl.applied_to_distribution_type = 'AP_PMT_DIST'

  and xdl.ae_header_id = DGSD.ae_header_id

  and xdl.ae_line_num = DGSD.ae_line_num

  GROUP BY rsh.RECEIPT_NUM)

  )  AP_RECEIPT,

  TO_CHAR(aca.check_number) AP_PMT,

  NULL AP_PAYMENT_TERMS,

  --to_char(aps.due_date,'DD-MON-YYYY','nls_date_language=american')  AP_PAYMENT_DUE_DATE,

  NULL AP_PAYMENT_DUE_DATE,

  NULL AP_PAYMENT_TERM_DATE,

  to_char(aca.check_date,'DD-MON-YYYY','nls_date_language=american') AP_PAYMENT_DATE,

  flv.meaning  AP_PAYMENT_STATUS,

  ieba.bank_account_num AP_XX_BANK_ACC_NUMB,

  NULL FA_ACCOUNTING_TYPE,

  NULL FA_ASSET_NUM,

  NULL FA_TRANS_CATE,

  NULL FA_MAJ_CATE,

  NULL FA_MIN_CATE,

  NULL FA_ASSET_TYPE,

  NULL FA_ASSET_DESC,

  NULL FA_DATE_PLACED,

  NULL FA_ASST_LOC_COUNTRY,

  NULL FA_ASST_LOC_STATE,

  NULL FA_ASST_LOC_CITY,

  NULL FA_ASST_LOC_SPARE,

  NULL FA_ASST_REM_LIFE,

  NULL FA_ASST_DEP_MET,

  NULL FA_USEFUL_LIFE,

  NULL FA_BOOK_TYPE_CODE,

  NULL FA_LEASE_NUMBER,

  NULL FA_LEASE_DESCRIPTION,

  NULL FA_LEASE_START_DATE,

  NULL FA_LEASE_END_DATE,

  NULL AR_ACCOUNTING_TYPE,

  NULL AR_TRXNUM,

  NULL AR_INV_LINE_DESC,

  NULL AR_TRXDATE,

  NULL AR_TRXAMT,

  NULL AR_CUSTNAME,

  NULL AR_CUSTSITE,

  NULL AR_CUSTNUM,

  NULL AR_TRXTYPE,

  NULL AR_TRXSOURCE,

  NULL AR_RECEIPTNUM,

  NULL AR_RECEIPTDATE,

  NULL CE_TRX_TYPE,

  NULL CE_TRX_DATE,

  NULL CE_REF,

  NULL CE_DESC,

  NULL CE_STMT_IDENTIFIER,

  NULL CE_STMT_DATE,

  NULL CE_XX_BANK_ACC,

  DGSD.GL_REF1

FROM DYN_GL_XX_DATA DGSD,

  ap_checks_all aca,

  ap_invoice_payments_all aipa,

  ap_payment_schedules_all aps,

  fnd_lookup_values flv,

  iby_ext_bank_accounts ieba,

  xla_transaction_entities xte

WHERE 1                              =1

AND aca.check_id                     = xte.source_id_int_1

AND DGSD.entity_id                   = xte.entity_id

and aipa.check_id                    = aca.check_id(+)

and aps.invoice_id  = aipa.invoice_id

and aca.status_lookup_code           = flv.lookup_code(+)

and aca.external_bank_account_id     = ieba.ext_bank_account_id(+)

and flv.lookup_type               (+)= 'CHECK STATE'

and flv.language                  (+)= 'US'

AND DGSD.je_source                   = 'Payables'

AND xte.entity_code                  = 'AP_PAYMENTS'

AND DGSD.je_category                 = 'Reconciled Payments'

UNION ALL

--Receivables Invoice Distributions

SELECT DGSD.LEDGER,

  DGSD.PERIOD,

 DGSD.ACCOUNTING_DATE,

  DGSD.accounting_entity,

  DGSD.cost_centre,

  DGSD.nominal_account,

  DGSD.sub_analysis,

  DGSD.intercompany,

  DGSD.location,

  DGSD.project,

  DGSD.flow,

  DGSD.spare1,

  DGSD.spare2,

  (DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||

  DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,

  DGSD.JE_SOURCE,

  DGSD.JE_CATEGORY,

  DGSD.DOC_SEQ,

  DGSD.POSTED_DATE,

  DGSD.ENT_CUR,

  DGSD.XENTERED_DR ENT_DR,

  DGSD.XENTERED_CR ENT_CR,

  nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,

  DGSD.XACCOUNTED_DR ACCTD_DR,

  DGSD.XACCOUNTED_CR ACCTD_CR,

  nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,

  DGSD.CUR_CONV_TYPE,

  DGSD.CUR_CONV_DATE,

  DGSD.CUR_CONV_RATE,

  DGSD.BATCH,

  DGSD.BATCH_DESC,

  DGSD.JOUR_NAME,

  DGSD.JOUR_DESC,

  DGSD.JOURLINE_DESC,

  DGSD.JOUR_PREP,

  DGSD.JOUR_APPR,

  DGSD.REV_FLAG,

  DGSD.REV_DATE,

  DGSD.ACC_SEQUENCE_NAME,

  DGSD.ACC_SEQUENCE_NUMBER,

  DGSD.REP_SEQUENCE_NAME,

  DGSD.REP_SEQUENCE_NUMBER,

  DGSD.JOURNAL_LINE_NUMBER,

  NULL AP_ACCOUNTING_TYPE,

  NULL AP_INV_NUMBER,

  NULL AP_INV_TYPE,

  NULL AP_SOURCE,

  NULL AP_INV_DATE,

  NULL AP_INVOICE_AMOUNT,

  NULL AP_SUP_NAME,

  NULL AP_SUP_NUM,

  NULL AP_SUPSITE,

  NULL AP_SUPTYPE,

  NULL AP_REQUESTOR,

  NULL AP_INVDESC,

  NULL AP_INVLINEDESC,

  NULL AP_INVLINE_TYPE,

  NULL AP_PO,

  NULL AP_POREQ,

  NULL AP_RECEIPT,

  NULL AP_PMT,

  NULL AP_PAYMENT_TERMS,

  NULL AP_PAYMENT_DUE_DATE,

  NULL AP_PAYMENT_TERM_DATE,

  NULL AP_PAYMENT_DATE,

  NULL AP_PAYMENT_STATUS,

  NULL AP_XX_BANK_ACC_NUMB,

  NULL FA_ACCOUNTING_TYPE,

  NULL FA_ASSET_NUM,

  NULL FA_TRANS_CATE,

  NULL FA_MAJ_CATE,

  NULL FA_MIN_CATE,

  NULL FA_ASSET_TYPE,

  NULL FA_ASSET_DESC,

  NULL FA_DATE_PLACED,

  NULL FA_ASST_LOC_COUNTRY,

  NULL FA_ASST_LOC_STATE,

  NULL FA_ASST_LOC_CITY,

  NULL FA_ASST_LOC_SPARE,

  NULL FA_ASST_REM_LIFE,

  NULL FA_ASST_DEP_MET,

  NULL FA_USEFUL_LIFE,

  NULL FA_BOOK_TYPE_CODE,

  NULL FA_LEASE_NUMBER,

  NULL FA_LEASE_DESCRIPTION,

  NULL FA_LEASE_START_DATE,

  NULL FA_LEASE_END_DATE,

  DGSD.ACTCLS_CODE AR_ACCOUNTING_TYPE,

  rcta.trx_number AR_TRXNUM,

  (SELECT description

  FROM ra_customer_trx_lines_all

  WHERE customer_trx_id=rcta.customer_trx_id

  AND ROWNUM<2

  )AR_INV_LINE_DESC,

  TO_CHAR(rcta.trx_date, 'DD-MON-YYYY','nls_date_language=american') AR_TRXDATE,

  (SELECT SUM(extended_amount)

  FROM ra_customer_trx_lines_all

  WHERE customer_trx_id=rcta.customer_trx_id

  ) AR_TRXAMT,

  hp.party_name AR_CUSTNAME,

  hcsu.location AR_CUSTSITE,

  hca.account_number AR_CUSTNUM,

  rctta.name AR_TRXTYPE,

  rabs.name AR_TRXSOURCE,

  (SELECT LISTAGG(RECEIPT_NUMBER,'|') WITHIN GROUP (ORDER BY RECEIPT_DATE) 

  FROM (SELECT ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,SUM(AMOUNT_APPLIED) AMT,ARC.RECEIPT_NUMBER

FROM AR_CASH_RECEIPTS_ALL ARC,

          AR_RECEIVABLE_APPLICATIONS_ALL ARAA

WHERE ARC.CASH_RECEIPT_ID   = ARAA.CASH_RECEIPT_ID

  AND ARAA.STATUS                  ='APP'

  AND ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID

GROUP BY ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,ARC.RECEIPT_NUMBER

)

WHERE AMT!=0 -- Exclude cancelled receipts

  ) AR_RECEIPTNUM,

  (SELECT LISTAGG(TO_CHAR(RECEIPT_DATE, 'DD-MON-YYYY','nls_date_language=american'),'|') WITHIN GROUP (ORDER BY RECEIPT_DATE)

  FROM (SELECT ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,SUM(AMOUNT_APPLIED) AMT,ARC.RECEIPT_NUMBER

FROM AR_CASH_RECEIPTS_ALL ARC,

          AR_RECEIVABLE_APPLICATIONS_ALL ARAA

WHERE ARC.CASH_RECEIPT_ID   = ARAA.CASH_RECEIPT_ID

  AND ARAA.STATUS                  ='APP'

  AND ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID

GROUP BY ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,ARC.RECEIPT_NUMBER

)

WHERE AMT!=0 -- Exclude cancelled receipts

  ) AR_RECEIPTDATE,

  NULL CE_TRX_TYPE,

  NULL CE_TRX_DATE,

  NULL CE_REF,

  NULL CE_DESC,

  NULL CE_STMT_IDENTIFIER,

  NULL CE_STMT_DATE,

  NULL CE_XX_BANK_ACC,

  DGSD.GL_REF1

FROM DYN_GL_XX_DATA DGSD,

  xla_transaction_entities xte,

  ra_customer_trx_all rcta,

  ra_cust_trx_line_gl_dist_all rctda,

  xla_distribution_links xdl,

  hz_cust_accounts hca,

  hz_parties hp,

  hz_party_sites hps,

  hz_cust_acct_sites_all hcsa,

  hz_cust_site_uses_all hcsu,

  ra_cust_trx_types_all rctta,

  ra_batch_sources_all rabs

WHERE 1                              =1

AND hca.cust_account_id              = hcsa.cust_account_id

AND rcta.bill_to_site_use_id         = hcsu.site_use_id

AND hcsa.cust_acct_site_id           = hcsu.cust_acct_site_id

AND hps.party_site_id                = hcsa.party_site_id

AND rcta.bill_to_customer_id         = hca.cust_account_id

AND hca.party_id                     = hp.party_id

AND rctta.cust_trx_type_seq_id       = rcta.cust_trx_type_seq_id

AND rabs.batch_source_seq_id         = rcta.batch_source_seq_id

AND rcta.customer_trx_id             = xte.source_id_int_1

AND DGSD.entity_id                   = xte.entity_id

AND rcta.customer_trx_id             = rctda.customer_trx_id

AND xdl.ae_header_id                 = DGSD.ae_header_id

AND xdl.ae_line_num                  = DGSD.ae_line_num

AND xdl.source_distribution_id_num_1 = rctda.cust_trx_line_gl_dist_id

AND xdl.source_distribution_type     = 'RA_CUST_TRX_LINE_GL_DIST_ALL'

AND DGSD.je_source                   = 'Receivables'

UNION ALL 

-- Receivable Applications

SELECT DGSD.LEDGER,

  DGSD.PERIOD,

  DGSD.ACCOUNTING_DATE,

  DGSD.accounting_entity,

  DGSD.cost_centre,

  DGSD.nominal_account,

  DGSD.sub_analysis,

  DGSD.intercompany,

  DGSD.location,

  DGSD.project,

  DGSD.flow,

  DGSD.spare1,

  DGSD.spare2,

  (DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||

  DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,

  DGSD.JE_SOURCE,

  DGSD.JE_CATEGORY,

  DGSD.DOC_SEQ,

  DGSD.POSTED_DATE,

  DGSD.ENT_CUR,

  DGSD.XENTERED_DR ENT_DR,

  DGSD.XENTERED_CR ENT_CR,

  nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,

  DGSD.XACCOUNTED_DR ACCTD_DR,

  DGSD.XACCOUNTED_CR ACCTD_CR,

  nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,

  DGSD.CUR_CONV_TYPE,

  DGSD.CUR_CONV_DATE,

  DGSD.CUR_CONV_RATE,

  DGSD.BATCH,

  DGSD.BATCH_DESC,

  DGSD.JOUR_NAME,

  DGSD.JOUR_DESC,

  DGSD.JOURLINE_DESC,

  DGSD.JOUR_PREP,

  DGSD.JOUR_APPR,

  DGSD.REV_FLAG,

  DGSD.REV_DATE,

  DGSD.ACC_SEQUENCE_NAME,

  DGSD.ACC_SEQUENCE_NUMBER,

  DGSD.REP_SEQUENCE_NAME,

  DGSD.REP_SEQUENCE_NUMBER,

  DGSD.JOURNAL_LINE_NUMBER,

  NULL AP_ACCOUNTING_TYPE,

  NULL AP_INV_NUMBER,

  NULL AP_INV_TYPE,

  NULL AP_SOURCE,

  NULL AP_INV_DATE,

  NULL AP_INVOICE_AMOUNT,

  NULL AP_SUP_NAME,

  NULL AP_SUP_NUM,

  NULL AP_SUPSITE,

  NULL AP_SUPTYPE,

  NULL AP_REQUESTOR,

  NULL AP_INVDESC,

  NULL AP_INVLINEDESC,

  NULL AP_INVLINE_TYPE,

  NULL AP_PO,

  NULL AP_POREQ,

  NULL AP_RECEIPT,

  NULL AP_PMT,

  NULL AP_PAYMENT_TERMS,

  NULL AP_PAYMENT_DUE_DATE,

  NULL AP_PAYMENT_TERM_DATE,

  NULL AP_PAYMENT_DATE,

  NULL AP_PAYMENT_STATUS,

  NULL AP_XX_BANK_ACC_NUMB,

  NULL FA_ACCOUNTING_TYPE,

  NULL FA_ASSET_NUM,

  NULL FA_TRANS_CATE,

  NULL FA_MAJ_CATE,

  NULL FA_MIN_CATE,

  NULL FA_ASSET_TYPE,

  NULL FA_ASSET_DESC,

  NULL FA_DATE_PLACED,

  NULL FA_ASST_LOC_COUNTRY,

  NULL FA_ASST_LOC_STATE,

  NULL FA_ASST_LOC_CITY,

  NULL FA_ASST_LOC_SPARE,

  NULL FA_ASST_REM_LIFE,

  NULL FA_ASST_DEP_MET,

  NULL FA_USEFUL_LIFE,

  NULL FA_BOOK_TYPE_CODE,

  NULL FA_LEASE_NUMBER,

  NULL FA_LEASE_DESCRIPTION,

  NULL FA_LEASE_START_DATE,

  NULL FA_LEASE_END_DATE,

  DGSD.ACTCLS_CODE AR_ACCOUNTING_TYPE,

  rcta.trx_number AR_TRXNUM,

  (SELECT description

  FROM ra_customer_trx_lines_all

  WHERE customer_trx_id=rcta.customer_trx_id

  AND ROWNUM<2

  )AR_INV_LINE_DESC,

  TO_CHAR(rcta.trx_date, 'DD-MON-YYYY','nls_date_language=american') AR_TRXDATE,

  (SELECT SUM(extended_amount)

  FROM ra_customer_trx_lines_all

  WHERE customer_trx_id=rcta.customer_trx_id

  ) AR_TRXAMT,

  hp.party_name AR_CUSTNAME,

  hcsu.location AR_CUSTSITE,

  hca.account_number AR_CUSTNUM,

  rctta.name AR_TRXTYPE,

  rabs.name AR_TRXSOURCE,

  (SELECT LISTAGG(RECEIPT_NUMBER,'|') WITHIN GROUP (ORDER BY RECEIPT_DATE) 

  FROM (SELECT ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,SUM(AMOUNT_APPLIED) AMT,ARC.RECEIPT_NUMBER

FROM AR_CASH_RECEIPTS_ALL ARC

WHERE ARC.CASH_RECEIPT_ID   = ARAA.CASH_RECEIPT_ID

  AND ARAA.STATUS                  ='APP'

  AND ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID

GROUP BY ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,ARC.RECEIPT_NUMBER

)

WHERE AMT!=0 -- Exclude cancelled receipts

  ) AR_RECEIPTNUM,

  (SELECT LISTAGG(TO_CHAR(RECEIPT_DATE, 'DD-MON-YYYY','nls_date_language=american'),'|') WITHIN GROUP (ORDER BY RECEIPT_DATE)

  FROM (SELECT ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,SUM(AMOUNT_APPLIED) AMT,ARC.RECEIPT_NUMBER

FROM AR_CASH_RECEIPTS_ALL ARC

WHERE ARC.CASH_RECEIPT_ID   = ARAA.CASH_RECEIPT_ID

  AND ARAA.STATUS                  ='APP'

  AND ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID

GROUP BY ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,ARC.RECEIPT_NUMBER

)

WHERE AMT!=0 -- Exclude cancelled receipts

  ) AR_RECEIPTDATE,

  NULL CE_TRX_TYPE,

  NULL CE_TRX_DATE,

  NULL CE_REF,

  NULL CE_DESC,

  NULL CE_STMT_IDENTIFIER,

  NULL CE_STMT_DATE,

  NULL CE_XX_BANK_ACC,

  DGSD.GL_REF1

FROM DYN_GL_XX_DATA DGSD,

  xla_transaction_entities xte,

  ra_customer_trx_all rcta,

  ar_distributions_all rctda,

  ar_receivable_applications_all araa,

  xla_distribution_links xdl,

  hz_cust_accounts hca,

  hz_parties hp,

  hz_party_sites hps,

  hz_cust_acct_sites_all hcsa,

  hz_cust_site_uses_all hcsu,

  ra_cust_trx_types_all rctta,

  ra_batch_sources_all rabs

WHERE 1                              =1

AND hca.cust_account_id              = hcsa.cust_account_id

AND rcta.bill_to_site_use_id         = hcsu.site_use_id

AND hcsa.cust_acct_site_id           = hcsu.cust_acct_site_id

AND hps.party_site_id                = hcsa.party_site_id

AND rcta.bill_to_customer_id         = hca.cust_account_id

AND hca.party_id                     = hp.party_id

AND rctta.cust_trx_type_seq_id       = rcta.cust_trx_type_seq_id

AND rabs.batch_source_seq_id         = rcta.batch_source_seq_id

AND rcta.customer_trx_id             = xte.source_id_int_1

AND DGSD.entity_id                   = xte.entity_id

AND xdl.ae_header_id                 = DGSD.ae_header_id

AND xdl.ae_line_num                  = DGSD.ae_line_num

AND xdl.source_distribution_id_num_1 = rctda.line_id

AND araa.receivable_application_id = rctda.source_id

AND rcta.customer_trx_id = araa.customer_trx_id

AND xdl.source_distribution_type     = 'AR_DISTRIBUTIONS_ALL'

AND DGSD.je_source                   = 'Receivables'

UNION ALL 

--Receivables Adjustments

SELECT DGSD.LEDGER,

  DGSD.PERIOD,

  DGSD.ACCOUNTING_DATE,

  DGSD.accounting_entity,

  DGSD.cost_centre,

  DGSD.nominal_account,

  DGSD.sub_analysis,

  DGSD.intercompany,

  DGSD.location,

  DGSD.project,

  DGSD.flow,

  DGSD.spare1,

  DGSD.spare2,

  (DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||

  DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,

  DGSD.JE_SOURCE,

  DGSD.JE_CATEGORY,

  DGSD.DOC_SEQ,

  DGSD.POSTED_DATE,

  DGSD.ENT_CUR,

  DGSD.XENTERED_DR ENT_DR,

  DGSD.XENTERED_CR ENT_CR,

  nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,

  DGSD.XACCOUNTED_DR ACCTD_DR,

  DGSD.XACCOUNTED_CR ACCTD_CR,

  nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,

  DGSD.CUR_CONV_TYPE,

  DGSD.CUR_CONV_DATE,

  DGSD.CUR_CONV_RATE,

  DGSD.BATCH,

  DGSD.BATCH_DESC,

  DGSD.JOUR_NAME,

  DGSD.JOUR_DESC,

  DGSD.JOURLINE_DESC,

  DGSD.JOUR_PREP,

  DGSD.JOUR_APPR,

  DGSD.REV_FLAG,

  DGSD.REV_DATE,

  DGSD.ACC_SEQUENCE_NAME,

  DGSD.ACC_SEQUENCE_NUMBER,

  DGSD.REP_SEQUENCE_NAME,

  DGSD.REP_SEQUENCE_NUMBER,

  DGSD.JOURNAL_LINE_NUMBER,

  NULL AP_ACCOUNTING_TYPE,

  NULL AP_INV_NUMBER,

  NULL AP_INV_TYPE,

  NULL AP_SOURCE,

  NULL AP_INV_DATE,

  NULL AP_INVOICE_AMOUNT,

  NULL AP_SUP_NAME,

  NULL AP_SUP_NUM,

  NULL AP_SUPSITE,

  NULL AP_SUPTYPE,

  NULL AP_REQUESTOR,

  NULL AP_INVDESC,

  NULL AP_INVLINEDESC,

  NULL AP_INVLINE_TYPE,

  NULL AP_PO,

  NULL AP_POREQ,

  NULL AP_RECEIPT,

  NULL AP_PMT,

  NULL AP_PAYMENT_TERMS,

  NULL AP_PAYMENT_DUE_DATE,

  NULL AP_PAYMENT_TERM_DATE,

  NULL AP_PAYMENT_DATE,

  NULL AP_PAYMENT_STATUS,

  NULL AP_XX_BANK_ACC_NUMB,

  NULL FA_ACCOUNTING_TYPE,

  NULL FA_ASSET_NUM,

  NULL FA_TRANS_CATE,

  NULL FA_MAJ_CATE,

  NULL FA_MIN_CATE,

  NULL FA_ASSET_TYPE,

  NULL FA_ASSET_DESC,

  NULL FA_DATE_PLACED,

  NULL FA_ASST_LOC_COUNTRY,

  NULL FA_ASST_LOC_STATE,

  NULL FA_ASST_LOC_CITY,

  NULL FA_ASST_LOC_SPARE,

  NULL FA_ASST_REM_LIFE,

  NULL FA_ASST_DEP_MET,

  NULL FA_USEFUL_LIFE,

  NULL FA_BOOK_TYPE_CODE,

  NULL FA_LEASE_NUMBER,

  NULL FA_LEASE_DESCRIPTION,

  NULL FA_LEASE_START_DATE,

  NULL FA_LEASE_END_DATE,

  DGSD.ACTCLS_CODE AR_ACCOUNTING_TYPE,

  rcta.trx_number AR_TRXNUM,

  (SELECT DISTINCT description

  FROM ra_customer_trx_lines_all

  WHERE customer_trx_id=rcta.customer_trx_id

  AND ROWNUM<2

  ) AR_INV_LINE_DESC,

  TO_CHAR(rcta.trx_date, 'DD-MON-YYYY','nls_date_language=american') AR_TRXDATE,

  (SELECT SUM(extended_amount)

  FROM ra_customer_trx_lines_all

  WHERE customer_trx_id=rcta.customer_trx_id

  ) AR_TRXAMT,

  hp.party_name AR_CUSTNAME,

  hcsu.location AR_CUSTSITE,

  hca.account_number AR_CUSTNUM,

  rctta.name AR_TRXTYPE,

  rabs.name AR_TRXSOURCE,

  (SELECT LISTAGG(RECEIPT_NUMBER,'|') WITHIN GROUP (ORDER BY RECEIPT_DATE) 

  FROM (SELECT ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,SUM(AMOUNT_APPLIED) AMT,ARC.RECEIPT_NUMBER

FROM AR_CASH_RECEIPTS_ALL ARC,

          AR_RECEIVABLE_APPLICATIONS_ALL ARAA

WHERE ARC.CASH_RECEIPT_ID   = ARAA.CASH_RECEIPT_ID

  AND ARAA.STATUS                  ='APP'

  AND ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID

GROUP BY ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,ARC.RECEIPT_NUMBER

)

WHERE AMT!=0 -- Exclude cancelled receipts

  ) AR_RECEIPTNUM,

  (SELECT LISTAGG(TO_CHAR(RECEIPT_DATE, 'DD-MON-YYYY','nls_date_language=american'),'|') WITHIN GROUP (ORDER BY RECEIPT_DATE)

  FROM (SELECT ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,SUM(AMOUNT_APPLIED) AMT,ARC.RECEIPT_NUMBER

FROM AR_CASH_RECEIPTS_ALL ARC,

          AR_RECEIVABLE_APPLICATIONS_ALL ARAA

WHERE ARC.CASH_RECEIPT_ID   = ARAA.CASH_RECEIPT_ID

  AND ARAA.STATUS                  ='APP'

  AND ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID

GROUP BY ARAA.CASH_RECEIPT_ID,ARC.RECEIPT_DATE,ARC.RECEIPT_NUMBER

)

WHERE AMT!=0 -- Exclude cancelled receipts

  ) AR_RECEIPTDATE,

  NULL CE_TRX_TYPE,

  NULL CE_TRX_DATE,

  NULL CE_REF,

  NULL CE_DESC,

  NULL CE_STMT_IDENTIFIER,

  NULL CE_STMT_DATE,

  NULL CE_XX_BANK_ACC,

  DGSD.GL_REF1

FROM DYN_GL_XX_DATA DGSD,

  xla_transaction_entities xte,

  ra_customer_trx_all rcta,

  ar_adjustments_all ada,

  hz_cust_accounts hca,

  hz_parties hp,

  hz_party_sites hps,

  hz_cust_acct_sites_all hcsa,

  hz_cust_site_uses_all hcsu,

  ra_cust_trx_types_all rctta,

  ra_batch_sources_all rabs

WHERE 1                              =1

AND hca.cust_account_id              = hcsa.cust_account_id

AND rcta.bill_to_site_use_id         = hcsu.site_use_id

AND hcsa.cust_acct_site_id           = hcsu.cust_acct_site_id

AND hps.party_site_id                = hcsa.party_site_id

AND rcta.bill_to_customer_id         = hca.cust_account_id

AND hca.party_id                     = hp.party_id

AND rctta.cust_trx_type_seq_id       = rcta.cust_trx_type_seq_id

AND rabs.batch_source_seq_id         = rcta.batch_source_seq_id

AND rcta.customer_trx_id             = ada.customer_trx_id

AND DGSD.entity_id                   = xte.entity_id

AND xte.source_id_int_1 = ada.adjustment_id

 AND ada.customer_trx_id = rcta.customer_trx_id

AND DGSD.je_source                   = 'Receivables'

AND DGSD.je_category = 'Adjustment'

AND xte.entity_code = 'ADJUSTMENTS'

UNION ALL

--Receipts Balancing

SELECT DGSD.LEDGER,

  DGSD.PERIOD,

  DGSD.ACCOUNTING_DATE,

  DGSD.accounting_entity,

  DGSD.cost_centre,

  DGSD.nominal_account,

  DGSD.sub_analysis,

  DGSD.intercompany,

  DGSD.location,

  DGSD.project,

  DGSD.flow,

  DGSD.spare1,

  DGSD.spare2,

  (DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||

  DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,

  DGSD.JE_SOURCE,

  DGSD.JE_CATEGORY,

  DGSD.DOC_SEQ,

  DGSD.POSTED_DATE,

  DGSD.ENT_CUR,

  DGSD.XENTERED_DR ENT_DR,

  DGSD.XENTERED_CR ENT_CR,

  nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,

  DGSD.XACCOUNTED_DR ACCTD_DR,

  DGSD.XACCOUNTED_CR ACCTD_CR,

  nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,

  DGSD.CUR_CONV_TYPE,

  DGSD.CUR_CONV_DATE,

  DGSD.CUR_CONV_RATE,

  DGSD.BATCH,

  DGSD.BATCH_DESC,

  DGSD.JOUR_NAME,

  DGSD.JOUR_DESC,

  DGSD.JOURLINE_DESC,

  DGSD.JOUR_PREP,

  DGSD.JOUR_APPR,

  DGSD.REV_FLAG,

  DGSD.REV_DATE,

  DGSD.ACC_SEQUENCE_NAME,

  DGSD.ACC_SEQUENCE_NUMBER,

  DGSD.REP_SEQUENCE_NAME,

  DGSD.REP_SEQUENCE_NUMBER,

  DGSD.JOURNAL_LINE_NUMBER,

  NULL AP_ACCOUNTING_TYPE,

  NULL AP_INV_NUMBER,

  NULL AP_INV_TYPE,

  NULL AP_SOURCE,

  NULL AP_INV_DATE,

  NULL AP_INVOICE_AMOUNT,

  NULL AP_SUP_NAME,

  NULL AP_SUP_NUM,

  NULL AP_SUPSITE,

  NULL AP_SUPTYPE,

  NULL AP_REQUESTOR,

  NULL AP_INVDESC,

  NULL AP_INVLINEDESC,

  NULL AP_INVLINE_TYPE,

  NULL AP_PO,

  NULL AP_POREQ,

  NULL AP_RECEIPT,

  NULL AP_PMT,

  NULL AP_PAYMENT_TERMS,

  NULL AP_PAYMENT_DUE_DATE,

  NULL AP_PAYMENT_TERM_DATE,

  NULL AP_PAYMENT_DATE,

  NULL AP_PAYMENT_STATUS,

  NULL AP_XX_BANK_ACC_NUMB,

  NULL FA_ACCOUNTING_TYPE,

  NULL FA_ASSET_NUM,

  NULL FA_TRANS_CATE,

  NULL FA_MAJ_CATE,

  NULL FA_MIN_CATE,

  NULL FA_ASSET_TYPE,

  NULL FA_ASSET_DESC,

  NULL FA_DATE_PLACED,

  NULL FA_ASST_LOC_COUNTRY,

  NULL FA_ASST_LOC_STATE,

  NULL FA_ASST_LOC_CITY,

  NULL FA_ASST_LOC_SPARE,

  NULL FA_ASST_REM_LIFE,

  NULL FA_ASST_DEP_MET,

  NULL FA_USEFUL_LIFE,

  NULL FA_BOOK_TYPE_CODE,

  NULL FA_LEASE_NUMBER,

  NULL FA_LEASE_DESCRIPTION,

  NULL FA_LEASE_START_DATE,

  NULL FA_LEASE_END_DATE,

  DGSD.ACTCLS_CODE AR_ACCOUNTING_TYPE,

  (SELECT LISTAGG(trx_number,'|') WITHIN GROUP (ORDER BY customer_trx_id)

     FROM

  (SELECT rcta.trx_number ,rcta.customer_trx_id

      FROM ra_customer_trx_all rcta,

             ar_receivable_applications_all araa

WHERE rcta.customer_trx_id = araa.applied_customer_trx_id

     AND araa.cash_receipt_id = acr.cash_receipt_id

AND araa.status='APP'

GROUP BY rcta.trx_number,rcta.customer_trx_id

  )) AR_TRXNUM,  

  (SELECT DISTINCT rctla.description

  FROM ra_customer_trx_lines_all rctla             

  WHERE 1=1

      AND ROWNUM<2

      AND exists (select 1 from ar_receivable_applications_all araa 

                    where rctla.customer_trx_id=araa.applied_customer_trx_id

                            AND araa.cash_receipt_id = acr.cash_receipt_id

AND araa.status='APP')

  ) AR_INV_LINE_DESC,

  (SELECT LISTAGG(trx_date,'|') WITHIN GROUP (ORDER BY customer_trx_id)

     FROM

  (SELECT TO_CHAR(rcta.trx_date, 'DD-MON-YYYY','nls_date_language=american')  trx_date,rcta.customer_trx_id

      FROM ra_customer_trx_all rcta,

             ar_receivable_applications_all araa

WHERE rcta.customer_trx_id = araa.applied_customer_trx_id

     AND araa.cash_receipt_id = acr.cash_receipt_id

AND araa.status='APP'

GROUP BY rcta.trx_date,rcta.customer_trx_id

  ))  AR_TRXDATE,

  (SELECT SUM(rctla.extended_amount) extended_amount

  FROM ra_customer_trx_lines_all rctla             

  WHERE 1=1

      AND exists (select 1 from ar_receivable_applications_all araa 

                    where rctla.customer_trx_id=araa.applied_customer_trx_id

                            AND araa.cash_receipt_id = acr.cash_receipt_id

AND araa.status='APP')

  ) AR_TRXAMT,

  hp.party_name AR_CUSTNAME,

  (SELECT hcsu.location

  FROM hz_party_sites hps,

    hz_cust_acct_sites_all hcsa,

    hz_cust_site_uses_all hcsu

  WHERE 1                      =1

  AND hca.cust_account_id      = hcsa.cust_account_id

  AND acr.customer_site_use_id = hcsu.site_use_id

  AND hcsa.cust_acct_site_id   = hcsu.cust_acct_site_id

  AND hps.party_site_id        = hcsa.party_site_id

  ) AR_CUSTSITE,

  hca.account_number AR_CUSTNUM,  

  (SELECT LISTAGG(name,'|') WITHIN GROUP (ORDER BY customer_trx_id)

     FROM

  (SELECT rctta.name ,rcta.customer_trx_id

      FROM ra_customer_trx_all rcta,

             ar_receivable_applications_all araa,

ra_cust_trx_types_all rctta

WHERE rcta.customer_trx_id = araa.applied_customer_trx_id

     AND araa.cash_receipt_id = acr.cash_receipt_id

AND araa.status='APP'

AND rctta.cust_trx_type_seq_id = rcta.cust_trx_type_seq_id

GROUP BY rctta.name,rcta.customer_trx_id

  ))  AR_TRXTYPE,

  (SELECT LISTAGG(name,'|') WITHIN GROUP (ORDER BY customer_trx_id)

     FROM

  (SELECT rabs.name ,rcta.customer_trx_id

      FROM ra_customer_trx_all rcta,

             ar_receivable_applications_all araa,

ra_batch_sources_all rabs

WHERE rcta.customer_trx_id = araa.applied_customer_trx_id

     AND araa.cash_receipt_id = acr.cash_receipt_id

AND rabs.batch_source_seq_id = rcta.batch_source_seq_id

AND araa.status='APP'

GROUP BY rabs.name,rcta.customer_trx_id

  )) AR_TRXSOURCE,

  acr.RECEIPT_NUMBER AR_RECEIPTNUM,

  TO_CHAR(acr.RECEIPT_DATE, 'DD-MON-YYYY','nls_date_language=american') AR_RECEIPTDATE,

  NULL CE_TRX_TYPE,

  NULL CE_TRX_DATE,

  NULL CE_REF,

  NULL CE_DESC,

  NULL CE_STMT_IDENTIFIER,

  NULL CE_STMT_DATE,

  NULL CE_XX_BANK_ACC,

  DGSD.GL_REF1

FROM DYN_GL_XX_DATA DGSD,

  xla_transaction_entities xte,

  ar_cash_receipts_all acr,

  hz_cust_accounts hca,

  hz_parties hp

WHERE 1                            =1

AND acr.cash_receipt_id           = xte.source_id_int_1

AND DGSD.entity_id                  = xte.entity_id

AND DGSD.je_source                = 'Receivables'

AND DGSD.je_category             = 'Receipts'

AND acr.pay_from_customer     = hca.cust_account_id(+)

AND hca.party_id                      = hp.party_id(+)

UNION ALL

--Cash Management

SELECT DGSD.LEDGER,

  DGSD.PERIOD,

  DGSD.ACCOUNTING_DATE,

  DGSD.accounting_entity,

  DGSD.cost_centre,

  DGSD.nominal_account,

  DGSD.sub_analysis,

  DGSD.intercompany,

  DGSD.location,

  DGSD.project,

  DGSD.flow,

  DGSD.spare1,

  DGSD.spare2,

  (DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||

  DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,

  DGSD.JE_SOURCE,

  DGSD.JE_CATEGORY,

  DGSD.DOC_SEQ,

  DGSD.POSTED_DATE,

  DGSD.ENT_CUR,

  DGSD.XENTERED_DR ENT_DR,

  DGSD.XENTERED_CR ENT_CR,

  nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,

  DGSD.XACCOUNTED_DR ACCTD_DR,

  DGSD.XACCOUNTED_CR ACCTD_CR,

  nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,

  DGSD.CUR_CONV_TYPE,

  DGSD.CUR_CONV_DATE,

  DGSD.CUR_CONV_RATE,

  DGSD.BATCH,

  DGSD.BATCH_DESC,

  DGSD.JOUR_NAME,

  DGSD.JOUR_DESC,

  DGSD.JOURLINE_DESC,

  DGSD.JOUR_PREP,

  DGSD.JOUR_APPR,

  DGSD.REV_FLAG,

  DGSD.REV_DATE,

  DGSD.ACC_SEQUENCE_NAME,

  DGSD.ACC_SEQUENCE_NUMBER,

  DGSD.REP_SEQUENCE_NAME,

  DGSD.REP_SEQUENCE_NUMBER,

  DGSD.JOURNAL_LINE_NUMBER,

  NULL AP_ACCOUNTING_TYPE,

  NULL AP_INV_NUMBER,

  NULL AP_INV_TYPE,

  NULL AP_SOURCE,

  NULL AP_INV_DATE,

  NULL AP_INVOICE_AMOUNT,

  NULL AP_SUP_NAME,

  NULL AP_SUP_NUM,

  NULL AP_SUPSITE,

  NULL AP_SUPTYPE,

  NULL AP_REQUESTOR,

  NULL AP_INVDESC,

  NULL AP_INVLINEDESC,

  NULL AP_INVLINE_TYPE,

  NULL AP_PO,

  NULL AP_POREQ,

  NULL AP_RECEIPT,

  NULL AP_PMT,

  NULL AP_PAYMENT_TERMS,

  NULL AP_PAYMENT_DUE_DATE,

  NULL AP_PAYMENT_TERM_DATE,

  NULL AP_PAYMENT_DATE,

  NULL AP_PAYMENT_STATUS,

  NULL AP_XX_BANK_ACC_NUMB,

  NULL FA_ACCOUNTING_TYPE,

  NULL FA_ASSET_NUM,

  NULL FA_TRANS_CATE,

  NULL FA_MAJ_CATE,

  NULL FA_MIN_CATE,

  NULL FA_ASSET_TYPE,

  NULL FA_ASSET_DESC,

  NULL FA_DATE_PLACED,

  NULL FA_ASST_LOC_COUNTRY,

  NULL FA_ASST_LOC_STATE,

  NULL FA_ASST_LOC_CITY,

  NULL FA_ASST_LOC_SPARE,

  NULL FA_ASST_REM_LIFE,

  NULL FA_ASST_DEP_MET,

  NULL FA_USEFUL_LIFE,

  NULL FA_BOOK_TYPE_CODE,

  NULL FA_LEASE_NUMBER,

  NULL FA_LEASE_DESCRIPTION,

  NULL FA_LEASE_START_DATE,

  NULL FA_LEASE_END_DATE,

  NULL AR_ACCOUNTING_TYPE,

  NULL AR_TRXNUM,

  NULL AR_INV_LINE_DESC,

  NULL AR_TRXDATE,

  NULL AR_TRXAMT,

  NULL AR_CUSTNAME,

  NULL AR_CUSTSITE,

  NULL AR_CUSTNUM,

  NULL AR_TRXTYPE,

  NULL AR_TRXSOURCE,

  NULL AR_RECEIPTNUM,

  NULL AR_RECEIPTDATE,

  cet.transaction_type CE_TRX_TYPE,

  TO_CHAR(cet.transaction_date, 'DD-MON-YYYY','nls_date_language=american') CE_TRX_DATE,

  cet.reference_text CE_REF,

  cet.description CE_DESC,

  to_char(csh.statement_number) CE_STMT_IDENTIFIER,

  TO_CHAR(csh.statement_date, 'DD-MON-YYYY','nls_date_language=american') CE_STMT_DATE,

  to_char(ieba.bank_account_num) CE_XX_BANK_ACC,

  DGSD.GL_REF1

FROM DYN_GL_XX_DATA DGSD,

  xla_transaction_entities xte,

  ce_bank_accounts   cba,

  ce_statement_headers  csh,

  iby_ext_bank_accounts  ieba,

  ce_external_transactions cet

WHERE 1                 =1

AND xte.source_id_int_1 = cet.transaction_id

AND xte.entity_id       = DGSD.entity_id

and cet.bank_account_id              = cba.bank_account_id

and csh.bank_account_id              = cba.bank_account_id

and CET.bank_account_id              = ieba.ext_bank_account_id

AND DGSD.je_source      = 'Cash Management'

UNION ALL

--Assets Depreciation

SELECT DGSD.LEDGER,

  DGSD.PERIOD,

  DGSD.ACCOUNTING_DATE,

  DGSD.accounting_entity,

  DGSD.cost_centre,

  DGSD.nominal_account,

  DGSD.sub_analysis,

  DGSD.intercompany,

  DGSD.location,

  DGSD.project,

  DGSD.flow,

  DGSD.spare1,

  DGSD.spare2,

  (DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||

  DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,

  DGSD.JE_SOURCE,

  DGSD.JE_CATEGORY,

  DGSD.DOC_SEQ,

  DGSD.POSTED_DATE,

  DGSD.ENT_CUR,

  DGSD.XENTERED_DR ENT_DR,

  DGSD.XENTERED_CR ENT_CR,

  nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,

  DGSD.XACCOUNTED_DR ACCTD_DR,

  DGSD.XACCOUNTED_CR ACCTD_CR,

  nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,

  DGSD.CUR_CONV_TYPE,

  DGSD.CUR_CONV_DATE,

  DGSD.CUR_CONV_RATE,

  DGSD.BATCH,

  DGSD.BATCH_DESC,

  DGSD.JOUR_NAME,

  DGSD.JOUR_DESC,

  DGSD.JOURLINE_DESC,

  DGSD.JOUR_PREP,

  DGSD.JOUR_APPR,

  DGSD.REV_FLAG,

  DGSD.REV_DATE,

  DGSD.ACC_SEQUENCE_NAME,

  DGSD.ACC_SEQUENCE_NUMBER,

  DGSD.REP_SEQUENCE_NAME,

  DGSD.REP_SEQUENCE_NUMBER,

  DGSD.JOURNAL_LINE_NUMBER,

  NULL AP_ACCOUNTING_TYPE,

  NULL AP_INV_NUMBER,

  NULL AP_INV_TYPE,

  NULL AP_SOURCE,

  NULL AP_INV_DATE,

  NULL AP_INVOICE_AMOUNT,

  NULL AP_SUP_NAME,

  NULL AP_SUP_NUM,

  NULL AP_SUPSITE,

  NULL AP_SUPTYPE,

  NULL AP_REQUESTOR,

  NULL AP_INVDESC,

  NULL AP_INVLINEDESC,

  NULL AP_INVLINE_TYPE,

  NULL AP_PO,

  NULL AP_POREQ,

  NULL AP_RECEIPT,

  NULL AP_PMT,

  NULL AP_PAYMENT_TERMS,

  NULL AP_PAYMENT_DUE_DATE,

  NULL AP_PAYMENT_TERM_DATE,

  NULL AP_PAYMENT_DATE,

  NULL AP_PAYMENT_STATUS,

  NULL AP_XX_BANK_ACC_NUMB,

  DGSD.ACTCLS_CODE FA_ACCOUNTING_TYPE,

  fas.asset_number FA_ASSET_NUM,

  DGSD.je_category FA_TRANS_CATE,

  fac.segment1 FA_MAJ_CATE,

  fac.segment2 FA_MIN_CATE,

  fas.asset_type FA_ASSET_TYPE,

  fasl.description FA_ASSET_DESC,

  TO_CHAR(fab.date_placed_in_service, 'DD-MON-YYYY','nls_date_language=american') FA_DATE_PLACED,

  fl.segment1 FA_ASST_LOC_COUNTRY,

  fl.segment2 FA_ASST_LOC_STATE,

  fl.segment3 FA_ASST_LOC_CITY,

  fl.segment4 FA_ASST_LOC_SPARE,

  ''                FA_ASST_REM_LIFE,

  fam.method_code FA_ASST_DEP_MET,

  fam.life_in_months FA_USEFUL_LIFE,

  fab.book_type_code FA_BOOK_TYPE_CODE,

  NULL FA_LEASE_NUMBER,

  NULL FA_LEASE_DESCRIPTION,

  NULL FA_LEASE_START_DATE,

  NULL FA_LEASE_END_DATE,

  NULL AR_ACCOUNTING_TYPE,

  NULL AR_TRXNUM,

  NULL AR_INV_LINE_DESC,

  NULL AR_TRXDATE,

  NULL AR_TRXAMT,

  NULL AR_CUSTNAME,

  NULL AR_CUSTSITE,

  NULL AR_CUSTNUM,

  NULL AR_TRXTYPE,

  NULL AR_TRXSOURCE,

  NULL AR_RECEIPTNUM,

  NULL AR_RECEIPTDATE,

  NULL CE_TRX_TYPE,

  NULL CE_TRX_DATE,

  NULL CE_REF,

  NULL CE_DESC,

  NULL CE_STMT_IDENTIFIER,

  NULL CE_STMT_DATE,

  NULL CE_XX_BANK_ACC,

  DGSD.GL_REF1

FROM DYN_GL_XX_DATA DGSD,

  xla_transaction_entities xte,

  fa_additions_b fas,

  fa_additions_tl fasl,

  fa_categories_b fac,

  fa_books fab,

  fa_distribution_history fdh,

  fa_locations fl,

  fa_category_book_defaults fcb,

  fa_methods fam

WHERE 1                   =1

AND fas.asset_id          = xte.source_id_int_1

AND fasl.asset_id         = fas.asset_id

AND fab.transaction_header_id_out is null

AND fasl.language         = USERENV('LANG')

AND fab.book_type_code    = xte.source_id_char_1

AND fab.book_type_code    = fdh.book_type_code

AND fab.transaction_header_id_out is null

AND fas.asset_category_id = fac.category_id

AND fab.asset_id          = fas.asset_id

AND fcb.category_id       = fac.category_id

AND fcb.book_type_code    = fab.book_type_code

and fdh.distribution_id in (select max(fdh1.distribution_id)

                             from fa_distribution_history fdh1

where 1=1

and fab.asset_id              = fdh1.asset_id 

AND fab.book_type_code        = fdh1.book_type_code)

--AND DGSD.code_combination_id = fdh.code_combination_id

AND fcb.method_id         = fam.method_id

and fab.asset_id                      = fdh.asset_id

and fdh.location_id                  = fl.location_id

AND DGSD.entity_id        = xte.entity_id

AND DGSD.je_source        = 'Assets'

AND DGSD.je_category      = 'Depreciation'

AND xte.entity_code ='DEPRECIATION'

UNION ALL

--Assets Addition

SELECT DGSD.LEDGER,

  DGSD.PERIOD,

  DGSD.ACCOUNTING_DATE,

  DGSD.accounting_entity,

  DGSD.cost_centre,

  DGSD.nominal_account,

  DGSD.sub_analysis,

  DGSD.intercompany,

  DGSD.location,

  DGSD.project,

  DGSD.flow,

  DGSD.spare1,

  DGSD.spare2,

  (DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||

  DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,

  DGSD.JE_SOURCE,

  DGSD.JE_CATEGORY,

  DGSD.DOC_SEQ,

  DGSD.POSTED_DATE,

  DGSD.ENT_CUR,

  DGSD.XENTERED_DR ENT_DR,

  DGSD.XENTERED_CR ENT_CR,

  nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,

  DGSD.XACCOUNTED_DR ACCTD_DR,

  DGSD.XACCOUNTED_CR ACCTD_CR,

  nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,

  DGSD.CUR_CONV_TYPE,

  DGSD.CUR_CONV_DATE,

  DGSD.CUR_CONV_RATE,

  DGSD.BATCH,

  DGSD.BATCH_DESC,

  DGSD.JOUR_NAME,

  DGSD.JOUR_DESC,

  DGSD.JOURLINE_DESC,

  DGSD.JOUR_PREP,

  DGSD.JOUR_APPR,

  DGSD.REV_FLAG,

  DGSD.REV_DATE,

  DGSD.ACC_SEQUENCE_NAME,

  DGSD.ACC_SEQUENCE_NUMBER,

  DGSD.REP_SEQUENCE_NAME,

  DGSD.REP_SEQUENCE_NUMBER,

  DGSD.JOURNAL_LINE_NUMBER,

  NULL AP_ACCOUNTING_TYPE,

  NULL AP_INV_NUMBER,

  NULL AP_INV_TYPE,

  NULL AP_SOURCE,

  NULL AP_INV_DATE,

  NULL AP_INVOICE_AMOUNT,

  NULL AP_SUP_NAME,

  NULL AP_SUP_NUM,

  NULL AP_SUPSITE,

  NULL AP_SUPTYPE,

  NULL AP_REQUESTOR,

  NULL AP_INVDESC,

  NULL AP_INVLINEDESC,

  NULL AP_INVLINE_TYPE,

  NULL AP_PO,

  NULL AP_POREQ,

  NULL AP_RECEIPT,

  NULL AP_PMT,

  NULL AP_PAYMENT_TERMS,

  NULL AP_PAYMENT_DUE_DATE,

  NULL AP_PAYMENT_TERM_DATE,

  NULL AP_PAYMENT_DATE,

  NULL AP_PAYMENT_STATUS,

  NULL AP_XX_BANK_ACC_NUMB,

  DGSD.ACTCLS_CODE FA_ACCOUNTING_TYPE,

  fas.asset_number FA_ASSET_NUM,

  DGSD.je_category FA_TRANS_CATE,

  fac.segment1 FA_MAJ_CATE,

  fac.segment2 FA_MIN_CATE,

  fas.asset_type FA_ASSET_TYPE,

  fasl.description FA_ASSET_DESC,

  TO_CHAR(fab.date_placed_in_service, 'DD-MON-YYYY','nls_date_language=american') FA_DATE_PLACED,

   fl.segment1 FA_ASST_LOC_COUNTRY,

          fl.segment2 FA_ASST_LOC_STATE,

          fl.segment3 FA_ASST_LOC_CITY,

          fl.segment4 FA_ASST_LOC_SPARE,

  ''                FA_ASST_REM_LIFE,

  fam.method_code FA_ASST_DEP_MET,

  fam.life_in_months FA_USEFUL_LIFE,

  fab.book_type_code FA_BOOK_TYPE_CODE,

  NULL FA_LEASE_NUMBER,

  NULL FA_LEASE_DESCRIPTION,

  NULL FA_LEASE_START_DATE,

  NULL FA_LEASE_END_DATE,

  NULL AR_ACCOUNTING_TYPE,

  NULL AR_TRXNUM,

  NULL AR_INV_LINE_DESC,

  NULL AR_TRXDATE,

  NULL AR_TRXAMT,

  NULL AR_CUSTNAME,

  NULL AR_CUSTSITE,

  NULL AR_CUSTNUM,

  NULL AR_TRXTYPE,

  NULL AR_TRXSOURCE,

  NULL AR_RECEIPTNUM,

  NULL AR_RECEIPTDATE,

  NULL CE_TRX_TYPE,

  NULL CE_TRX_DATE,

  NULL CE_REF,

  NULL CE_DESC,

  NULL CE_STMT_IDENTIFIER,

  NULL CE_STMT_DATE,

  NULL CE_XX_BANK_ACC,

  DGSD.GL_REF1

FROM DYN_GL_XX_DATA DGSD,

  xla_transaction_entities xte,

  fa_additions_b fas,

  fa_additions_tl fasl,

  fa_categories_b fac,

  fa_books fab,

  fa_category_book_defaults fcb,

  fa_distribution_history fdh,

  fa_locations fl,

  fa_transaction_headers fth,

  fa_methods fam

WHERE 1                       =1

AND fth.transaction_header_id = xte.source_id_int_1

AND fas.asset_id              = fth.asset_id

AND fas.asset_id              = fab.asset_id

AND fasl.asset_id             = fas.asset_id

AND fasl.language             = USERENV('LANG')

AND fab.book_type_code        = xte.source_id_char_1

AND fab.transaction_header_id_out is null

AND fab.book_type_code        = fth.book_type_code

AND fab.book_type_code        = fdh.book_type_code

AND fab.transaction_header_id_out is null

AND fas.asset_category_id     = fac.category_id

AND fcb.category_id           = fac.category_id

and fab.asset_id              = fdh.asset_id

and fdh.location_id           = fl.location_id

AND fcb.book_type_code        = fab.book_type_code

AND fcb.method_id             = fam.method_id

AND DGSD.entity_id            = xte.entity_id

and fdh.distribution_id in (select max(fdh1.distribution_id)

                             from fa_distribution_history fdh1

where 1=1

and fab.asset_id              = fdh1.asset_id 

AND fab.book_type_code        = fdh1.book_type_code)

AND DGSD.JE_SOURCE            = 'Assets'

AND DGSD.je_category         IN ('Transfer', 'Retirement', 'Adjustment', 'CIP Adjustment', 'Addition', 'CIP Addition')

UNION ALL

--FA LEASE

SELECT DGSD.LEDGER,

  DGSD.PERIOD,

  DGSD.ACCOUNTING_DATE,

  DGSD.accounting_entity,

  DGSD.cost_centre,

  DGSD.nominal_account,

  DGSD.sub_analysis,

  DGSD.intercompany,

  DGSD.location,

  DGSD.project,

  DGSD.flow,

  DGSD.spare1,

  DGSD.spare2,

  (DGSD.accounting_entity_desc||'.'||DGSD.cost_centre_desc||'.'||DGSD.nominal_account_desc||'.'||DGSD.sub_analysis_desc||'.'||DGSD.intercompany_desc||'.'||

  DGSD.location_desc||'.'||DGSD.project_desc||'.'||DGSD.flow_desc||'.'||DGSD.spare1_desc||'.'||DGSD.spare2_desc) CODE_COMBINATION_DESC,

  DGSD.JE_SOURCE,

  DGSD.JE_CATEGORY,

  DGSD.DOC_SEQ,

  DGSD.POSTED_DATE,

  DGSD.ENT_CUR,

  DGSD.XENTERED_DR ENT_DR,

  DGSD.XENTERED_CR ENT_CR,

  nvl(DGSD.XENTERED_DR,0)-nvl(DGSD.XENTERED_CR,0) ENT_NET,

  DGSD.XACCOUNTED_DR ACCTD_DR,

  DGSD.XACCOUNTED_CR ACCTD_CR,

  nvl(DGSD.XACCOUNTED_DR,0)-nvl(DGSD.XACCOUNTED_CR,0) ACCTD_NET,

  DGSD.CUR_CONV_TYPE,

  DGSD.CUR_CONV_DATE,

  DGSD.CUR_CONV_RATE,

  DGSD.BATCH,

  DGSD.BATCH_DESC,

  DGSD.JOUR_NAME,

  DGSD.JOUR_DESC,

  DGSD.JOURLINE_DESC,

  DGSD.JOUR_PREP,

  DGSD.JOUR_APPR,

  DGSD.REV_FLAG,

  DGSD.REV_DATE,

  DGSD.ACC_SEQUENCE_NAME,

  DGSD.ACC_SEQUENCE_NUMBER,

  DGSD.REP_SEQUENCE_NAME,

  DGSD.REP_SEQUENCE_NUMBER,

  DGSD.JOURNAL_LINE_NUMBER,

  NULL AP_ACCOUNTING_TYPE,

  NULL AP_INV_NUMBER,

  NULL AP_INV_TYPE,

  NULL AP_SOURCE,

  NULL AP_INV_DATE,

  NULL AP_INVOICE_AMOUNT,

  NULL AP_SUP_NAME,

  NULL AP_SUP_NUM,

  NULL AP_SUPSITE,

  NULL AP_SUPTYPE,

  NULL AP_REQUESTOR,

  NULL AP_INVDESC,

  NULL AP_INVLINEDESC,

  NULL AP_INVLINE_TYPE,

  NULL AP_PO,

  NULL AP_POREQ,

  NULL AP_RECEIPT,

  NULL AP_PMT,

  NULL AP_PAYMENT_TERMS,

  NULL AP_PAYMENT_DUE_DATE,

  NULL AP_PAYMENT_TERM_DATE,

  NULL AP_PAYMENT_DATE,

  NULL AP_PAYMENT_STATUS,

  NULL AP_XX_BANK_ACC_NUMB,

  DGSD.ACTCLS_CODE FA_ACCOUNTING_TYPE,

  fas.asset_number FA_ASSET_NUM,

  DGSD.je_category FA_TRANS_CATE,

  fac.segment1 FA_MAJ_CATE,

  fac.segment2 FA_MIN_CATE,

  fas.asset_type FA_ASSET_TYPE,

  fasl.description FA_ASSET_DESC,

  TO_CHAR(fab.date_placed_in_service, 'DD-MON-YYYY','nls_date_language=american') FA_DATE_PLACED,

  fl.segment1 FA_ASST_LOC_COUNTRY,

  fl.segment2 FA_ASST_LOC_STATE,

  fl.segment3 FA_ASST_LOC_CITY,

  fl.segment4 FA_ASST_LOC_SPARE,

  ''                FA_ASST_REM_LIFE,

  fam.method_code FA_ASST_DEP_MET,

  fam.life_in_months FA_USEFUL_LIFE,

  fab.book_type_code FA_BOOK_TYPE_CODE,

  fal.lease_number     FA_LEASE_NUMBER,

  fal.description      FA_LEASE_DESCRIPTION,

  to_char(fal.lease_start_date, 'DD-MON-YYYY','nls_date_language=american') FA_LEASE_START_DATE,

  to_char(fal.lease_end_date, 'DD-MON-YYYY','nls_date_language=american')   FA_LEASE_END_DATE,

  NULL AR_ACCOUNTING_TYPE,

  NULL AR_TRXNUM,

  NULL AR_INV_LINE_DESC,

  NULL AR_TRXDATE,

  NULL AR_TRXAMT,

  NULL AR_CUSTNAME,

  NULL AR_CUSTSITE,

  NULL AR_CUSTNUM,

  NULL AR_TRXTYPE,

  NULL AR_TRXSOURCE,

  NULL AR_RECEIPTNUM,

  NULL AR_RECEIPTDATE,

  NULL CE_TRX_TYPE,

  NULL CE_TRX_DATE,

  NULL CE_REF,

  NULL CE_DESC,

  NULL CE_STMT_IDENTIFIER,

  NULL CE_STMT_DATE,

  NULL CE_XX_BANK_ACC,

  DGSD.GL_REF1

FROM DYN_GL_XX_DATA DGSD,

  xla_transaction_entities xte,

  fa_additions_b fas,

  fa_additions_tl fasl,

  fa_categories_b fac,

  fa_books fab,

  fa_distribution_history fdh,

  fa_locations fl,

  fa_category_book_defaults fcb,

  FA_LEASES fal,

  fa_methods fam

WHERE 1                   =1

AND fas.asset_id          = xte.source_id_int_1

AND fal.lease_id          = fab.lease_id

AND fasl.asset_id         = fas.asset_id

AND fab.transaction_header_id_out is null

AND fasl.language         = USERENV('LANG')

AND fab.book_type_code    = xte.source_id_char_1

AND fab.transaction_header_id_out is null

AND fas.asset_category_id = fac.category_id

AND fab.asset_id          = fas.asset_id

AND fcb.category_id       = fac.category_id

AND fcb.book_type_code    = fab.book_type_code

AND fab.book_type_code    = fdh.book_type_code

AND fcb.method_id         = fam.method_id

and fab.asset_id          = fdh.asset_id

and fdh.location_id       = fl.location_id

AND DGSD.entity_id        = xte.entity_id

and fdh.distribution_id in (select max(fdh1.distribution_id)

                             from fa_distribution_history fdh1

where 1=1

and fab.asset_id              = fdh1.asset_id 

AND fab.book_type_code        = fdh1.book_type_code)

AND DGSD.je_source        = 'Assets'

AND DGSD.je_category      = 'Lease Expense'

AND xte.entity_code ='LEASE_EXPENSE'

UNION ALL

--All Other Sources

SELECT 

 glg.NAME LEDGER,

 gjh.period_name PERIOD,

 TO_CHAR(gjh.default_effective_date, 'DD-MON-YYYY','nls_date_language=american') ACCOUNTING_DATE,

 data_acc_set.flex_segment_value accounting_entity,

 gcc.segment2 cost_centre,

 gcc.segment3 nominal_account,

 gcc.segment4 sub_analysis,

 gcc.segment5 intercompany,

 gcc.segment6 location,

 gcc.segment7 project,

 gcc.segment8 flow,

 gcc.segment9 spare1,

 gcc.segment10 spare2,

 (gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 1, data_acc_set.flex_segment_value)||'.'||

 gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 2, gcc.segment2)||'.'||

 gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 3, gcc.segment3)||'.'||

 gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 4, gcc.segment4)||'.'||

 gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 5, gcc.segment5)||'.'||

 gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 6, gcc.segment6)||'.'||

 gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 7, gcc.segment7)||'.'||

 gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 8, gcc.segment8)||'.'||

 gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 9, gcc.segment9)||'.'||

 gl_flexfields_pkg.get_description_sql(glg.chart_of_accounts_id, 10, gcc.segment10))CODE_COMBINATION_DESC,

 gjs.user_je_source_name JE_SOURCE,

 gjc.user_je_category_name JE_CATEGORY,

 gjh.doc_sequence_value DOC_SEQ, 

 TO_CHAR(gjh.posted_date, 'DD-MON-YYYY','nls_date_language=american') POSTED_DATE,

 gjl.currency_code ENT_CUR,

 nvl(xal.entered_dr, gjl.entered_dr) ENT_DR,

 nvl(xal.entered_cr, gjl.entered_cr) ENT_CR,

 nvl(nvl(xal.entered_dr, gjl.entered_dr),0)-nvl(nvl(xal.entered_cr, gjl.entered_cr),0) ENT_NET,

 nvl(xal.accounted_dr, gjl.accounted_dr) ACCTD_DR,

 nvl(xal.accounted_cr, gjl.accounted_cr) ACCTD_CR,

 nvl(nvl(xal.accounted_dr, gjl.accounted_dr),0)-nvl(nvl(xal.accounted_cr, gjl.accounted_cr),0) ACCTD_NET,

 nvl(xal.currency_conversion_type,gjl.currency_conversion_type) CUR_CONV_TYPE,

 TO_CHAR(nvl(xal.currency_conversion_date,gjl.currency_conversion_date), 'DD-MON-YYYY','nls_date_language=american') CUR_CONV_DATE,

 nvl(xal.currency_conversion_rate,gjl.currency_conversion_rate) CUR_CONV_RATE,

 gjb.name BATCH,

 gjb.description BATCH_DESC,

 gjh.name JOUR_NAME,

 gjh.description JOUR_DESC,

 gjl.description JOURLINE_DESC,

 gjh.created_by JOUR_PREP,

 (SELECT display_name FROM per_person_names_f WHERE person_id = gjb.approver_employee_id AND name_type = 'GLOBAL') JOUR_APPR,

 --DECODE(gjh.accrual_rev_effective_date, NULL, 'N', 'Y') REV_FLAG,

 decode(gjh.accrual_rev_status, 'R', 'Reversed', 'Not Reversed') REV_FLAG,

TO_CHAR(gjh.accrual_rev_effective_date, 'DD-MON-YYYY','nls_date_language=american')  REV_DATE,

fsv_acc_seq.header_name      ACC_SEQUENCE_NAME,

gjh.posting_acct_seq_value   ACC_SEQUENCE_NUMBER,

fsv_rep_seq.header_name      REP_SEQUENCE_NAME,

gjh.close_acct_seq_value     REP_SEQUENCE_NUMBER,

gjl.je_line_num              JOURNAL_LINE_NUMBER,

NULL AP_ACCOUNTING_TYPE,

NULL AP_INV_NUMBER,

NULL AP_INV_TYPE,

NULL AP_SOURCE,

NULL AP_INV_DATE,

NULL AP_INVOICE_AMOUNT,

NULL AP_SUP_NAME,

NULL AP_SUP_NUM,

NULL AP_SUPSITE,

NULL AP_SUPTYPE,

NULL AP_REQUESTOR,

NULL AP_INVDESC,

NULL AP_INVLINEDESC,

NULL AP_INVLINE_TYPE,

NULL AP_PO,

NULL AP_POREQ,

NULL AP_RECEIPT,

NULL AP_PMT,

NULL AP_PAYMENT_TERMS,

NULL AP_PAYMENT_DUE_DATE,

NULL AP_PAYMENT_TERM_DATE,

NULL AP_PAYMENT_DATE,

NULL AP_PAYMENT_STATUS,

NULL AP_XX_BANK_ACC_NUMB,

NULL FA_ACCOUNTING_TYPE,

NULL FA_ASSET_NUM,

NULL FA_TRANS_CATE,

NULL FA_MAJ_CATE,

NULL FA_MIN_CATE,

NULL FA_ASSET_TYPE,

NULL FA_ASSET_DESC,

NULL FA_DATE_PLACED,

NULL FA_ASST_LOC_COUNTRY,

NULL FA_ASST_LOC_STATE,

NULL FA_ASST_LOC_CITY,

NULL FA_ASST_LOC_SPARE,

NULL FA_ASST_REM_LIFE,

NULL FA_ASST_DEP_MET,

NULL FA_USEFUL_LIFE,

NULL FA_BOOK_TYPE_CODE,

NULL FA_LEASE_NUMBER,

NULL FA_LEASE_DESCRIPTION,

NULL FA_LEASE_START_DATE,

NULL FA_LEASE_END_DATE,

NULL AR_ACCOUNTING_TYPE,

NULL AR_TRXNUM,

NULL AR_INV_LINE_DESC,

NULL AR_TRXDATE,

NULL AR_TRXAMT,

NULL AR_CUSTNAME,

NULL AR_CUSTSITE,

NULL AR_CUSTNUM,

NULL AR_TRXTYPE,

NULL AR_TRXSOURCE,

NULL AR_RECEIPTNUM,

NULL AR_RECEIPTDATE,

NULL CE_TRX_TYPE,

NULL CE_TRX_DATE,

NULL CE_REF,

NULL CE_DESC,

NULL CE_STMT_IDENTIFIER,

NULL CE_STMT_DATE,

NULL CE_XX_BANK_ACC,

gjl.REFERENCE_1 GL_REF1 

FROM

  gl_je_headers gjh,

  gl_je_lines  gjl,

  gl_ledgers glg,

  gl_je_batches gjb,

  gl_code_combinations gcc,

  gl_import_references gir,

  fun_seq_versions fsv_acc_seq,

  fun_seq_versions fsv_rep_seq,

  gl_je_sources gjs,

  gl_je_categories gjc,

  xla_ae_lines xal,

  data_acc_set

WHERE 1=1

 AND gjl.je_header_id=gjh.je_header_id

 AND glg.ledger_id = gjh.ledger_id

 AND gjs.je_source_name = gjh.je_source

 AND gjc.je_category_name = gjh.je_category

 AND gjs.LANGUAGE = USERENV('LANG')

 AND gjc.LANGUAGE = USERENV('LANG')

 AND gjh.je_batch_id = gjb.je_batch_id

 AND gjl.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 gcc.code_combination_id=gjl.code_combination_id

 and gjh.posting_acct_seq_version_id  = fsv_acc_seq.seq_version_id(+)

 and gjh.close_acct_seq_version_id    = fsv_rep_seq.seq_version_id(+)

 AND gjh.status='P'

 AND gjh.actual_flag='A'

 AND gjh.je_source NOT IN ('Payables','Receivables','Cash Management','Assets')

-- AND glg.NAME                 = :P_LEDGER

   and (data_acc_set.ledger_name = :p_ledger

    or data_acc_set.ledger_set_name = :p_ledger)

   and glg.ledger_id             = data_acc_set.ledger_id

and ((coalesce(null, :p_acct_entity) is null)

    or (data_acc_set.flex_segment_value in (:p_acct_entity)))

 and ((coalesce(null, :p_cost_centre) is null)

    or (gcc.segment2 in (:p_cost_centre)))

 and ((coalesce(null, :p_account) is null)

    or (gcc.segment3 in (:p_account)))

 and ((coalesce(null, :p_sub_analysis) is null)

    or (gcc.segment4 in (:p_sub_analysis)))

 and ((coalesce(null, :p_intercmpny) is null)

    or (gcc.segment5 in (:p_intercmpny)))

 and ((coalesce(null, :p_location) is null)

    or (gcc.segment6 in (:p_location)))

 and ((coalesce(null, :p_project) is null)

    or (gcc.segment7 in (:p_project)))

 and ((coalesce(null, :p_flow) is null)

    or (gcc.segment8 in (:p_flow)))

 and ((coalesce(null, :p_spare1) is null)

    or (gcc.segment9 in (:p_spare1)))

 and ((coalesce(null, :p_spare2) is null)

    or (gcc.segment10 in (:p_spare2)))

 and ((coalesce(null, :P_JE_SOURCE) is null)

    or (gjs.user_je_source_name in (:P_JE_SOURCE)))

 and ((coalesce(null, :P_JE_CATEGORY) is null)

    or (gjc.user_je_category_name in (:P_JE_CATEGORY)))

and gjh.period_name in (select period_name

                             from gl_periods

                            where start_date     >= (select start_date

                                                       from gl_periods

                                                      where period_name     = :P_PERIOD_FROM

                                                        and period_set_name = 'XX_GLOBAL_CAL')

                              and end_date       <= (select end_date

                                                       from gl_periods

                                                      where period_name     = :P_PERIOD_TO

                                                        and period_set_name = 'XX_GLOBAL_CAL')

                              and period_set_name = 'XX_GLOBAL_CAL')