Subledger-Ledger Linkage (Accounts Payables)

AP (Item Lines)

select distinct
       api.invoice_num
      ,gjl.entered_cr
      ,gjl.entered_dr
      ,gjl.accounted_cr
      ,gjl.accounted_dr
      ,gjh.name journal_name
      ,gjh.creation_date journal_creation_date
      ,gcc.segment1    
      --,xdl.accounting_line_code
  from gl_import_references gir
      ,xla_ae_lines xal
      ,xla_ae_headers xah
      ,xla_distribution_links xdl
      ,ap_invoices_all api
      ,ap_invoice_lines_all apl
      ,ap_invoice_distributions_all apd
      ,gl_je_lines gjl
      ,gl_je_headers gjh
      ,gl_code_combinations gcc    
 where api.invoice_id = apl.invoice_id
   and apl.invoice_id = apd.invoice_id
   and apl.line_number = apd.invoice_line_number
   and gir.gl_sl_link_table = xal.gl_sl_link_table
   and gir.gl_sl_link_id = xal.gl_sl_link_id
   and xal.ae_header_id = xah.ae_header_id
   and xdl.ae_header_id = xah.ae_header_id
   and gjl.je_header_id = gjh.je_header_id
   and gjl.je_header_id = gir.je_header_id
   and gjl.je_line_num = gir.je_line_num 
   and xdl.event_id = apd.accounting_event_id
   and xdl.source_distribution_id_num_1 = apd.invoice_distribution_id
   and xdl.source_distribution_type = 'AP_INV_DIST'
   and xal.gl_sl_link_table = 'XLAJEL'
   and xah.event_type_code <> 'MANUAL'
   and gjh.je_source = 'Payables'
   and gjh.je_category = 'Purchase Invoices'
   and gcc.code_combination_id = gjl.code_combination_id
   --and api.invoice_num = 'IE-365010'
;

AP (Prepayment Lines)

select distinct
       api.invoice_num
      ,gjl.entered_cr
      ,gjl.entered_dr
      ,gjl.accounted_cr
      ,gjl.accounted_dr
      ,gjh.name journal_name
      ,gjh.creation_date journal_creation_date
      ,gcc.segment1
      --,xdl.accounting_line_code
  from gl_import_references gir
      ,xla_ae_lines xal
      ,xla_ae_headers xah
      ,xla_distribution_links xdl
      ,ap_invoices_all api
      ,ap_invoice_lines_all apl
      ,ap_invoice_distributions_all apd
      ,gl_je_lines gjl
      ,gl_je_headers gjh
      ,gl_code_combinations gcc
 where api.invoice_id = apl.invoice_id
   and apl.invoice_id = apd.invoice_id
   and apl.line_number = apd.invoice_line_number
   and gir.gl_sl_link_table = xal.gl_sl_link_table
   and gir.gl_sl_link_id = xal.gl_sl_link_id
   and xal.ae_header_id = xah.ae_header_id
   and xdl.ae_header_id = xah.ae_header_id
   and gjl.je_header_id = gjh.je_header_id
   and gjl.je_header_id = gir.je_header_id
   and gjl.je_line_num = gir.je_line_num
   and xdl.event_id = apd.accounting_event_id
   and xdl.applied_to_dist_id_num_1 = apd.prepay_distribution_id
   and xdl.source_distribution_type = 'AP_PREPAY'
   and xal.gl_sl_link_table = 'XLAJEL'
   and xah.event_type_code <> 'MANUAL'
   and gjh.je_source = 'Payables'
   and gjh.je_category = 'Purchase Invoices'
   and gcc.code_combination_id = gjl.code_combination_id
   --and api.invoice_num = 'IE-365010'
;

AP (Payments)

select distinct
       aca.checkrun_name
      ,aca.check_number
      ,gjl.entered_cr
      ,gjl.entered_dr
      ,gjl.accounted_cr
      ,gjl.accounted_dr
      ,gjh.name journal_name
      ,gjh.creation_date journal_creation_date
      ,gcc.segment1
      --,xdl.accounting_line_code
  from ap_payment_hist_dists aphd
      ,ap_payment_history_all aph
      ,ap_checks_all aca
      ,xla_distribution_links xdl
      ,xla_ae_lines xal
      ,xla_ae_headers xah
      ,gl_import_references gir
      ,gl_je_lines gjl
      ,gl_je_headers gjh
      ,gl_je_batches gjb
      ,gl_code_combinations gcc
 where xal.ae_header_id = xah.ae_header_id
   and xdl.ae_header_id = xah.ae_header_id
   and xdl.ae_line_num = xal.ae_line_num
   and xdl.ae_header_id = xal.ae_header_id
   and xal.gl_sl_link_table = gir.gl_sl_link_table
   and xal.gl_sl_link_id = gir.gl_sl_link_id
   and gir.je_header_id = gjl.je_header_id
   and gir.je_line_num = gjl.je_line_num
   and gjl.code_combination_id = gcc.code_combination_id
   and gjl.je_header_id = gjh.je_header_id
   and gjh.je_batch_id = gjb.je_batch_id
   and aphd.payment_hist_dist_id = xdl.source_distribution_id_num_1
   and xdl.source_distribution_type = 'AP_PMT_DIST'
   and gjh.je_source = 'Payables'
   and gjh.je_category = 'Payments'
   and xah.event_type_code <> 'MANUAL'
   and xal.gl_sl_link_table = 'XLAJEL'
   and aphd.payment_history_id = aph.payment_history_id
   and aph.check_id = aca.check_id
   --and aca.check_number = '25467'
;

AP (Treasury Confirmation)

select distinct
       aca.checkrun_name
      ,aca.check_number
      ,gjl.entered_cr
      ,gjl.entered_dr
      ,gjl.accounted_cr
      ,gjl.accounted_dr    
      ,gjh.name journal_name
      ,gjh.creation_date journal_creation_date
      ,gcc.segment1
      --,xdl.accounting_line_code
  from ap_payment_hist_dists aphd
      ,ap_invoice_payments_all aip
      ,fv_treasury_confirmations_all ftc
      ,ap_checks_all aca
      ,xla_distribution_links xdl
      ,xla_ae_lines xal
      ,xla_ae_headers xah
      ,gl_import_references gir
      ,gl_je_lines gjl
      ,gl_je_headers gjh
      ,gl_je_batches gjb
      ,gl_code_combinations gcc
 where xal.ae_header_id = xah.ae_header_id
   and xdl.ae_header_id = xah.ae_header_id
   and xdl.ae_line_num = xal.ae_line_num
   and xdl.ae_header_id = xal.ae_header_id
   and xal.gl_sl_link_table = gir.gl_sl_link_table
   and xal.gl_sl_link_id = gir.gl_sl_link_id
   and gir.je_header_id = gjl.je_header_id
   and gir.je_line_num = gjl.je_line_num
   and gjl.code_combination_id = gcc.code_combination_id
   and gjl.je_header_id = gjh.je_header_id
   and gjh.je_batch_id = gjb.je_batch_id
   and aphd.payment_hist_dist_id = xdl.source_distribution_id_num_1
   and xdl.source_distribution_type = 'FV_TREASURY_CONFIRMATIONS_ALL'
   and gjh.je_source = 'Payables'
   and xah.event_type_code <> 'MANUAL'
   and aphd.invoice_payment_id = aip.invoice_payment_id
   and aip.check_id = aca.check_id
   and aca.payment_instruction_id = ftc.payment_instruction_id
   --and aca.check_number = '25467'
;