Subledger-Ledger Linkage (Accounts Receivable)

AR (Cash Receipt)

select distinct
       arc.receipt_number
      ,arc.receipt_date
      ,gjl.entered_cr
      ,gjl.entered_dr
      ,gjl.accounted_cr
      ,gjl.accounted_dr
      ,gjh.name journal_name
      ,gjh.creation_date journal_creation_date
      ,glc.segment1
  from ar_cash_receipt_history_all arh
      ,ar_distributions_all ard
      ,ar_cash_receipts_all arc
      ,ar_batches_all arb
      ,gl_code_combinations glc
      ,xla.xla_transaction_entities te
      ,xla_ae_headers xah
      ,xla_ae_lines xal
      ,xla_distribution_links xdl
      ,gl_import_references gir
      ,gl_je_headers gjh
      ,gl_je_lines gjl
 where arh.cash_receipt_history_id = ard.source_id
   and arc.cash_receipt_id = arh.cash_receipt_id
   and arh.batch_id = arb.batch_id (+)
   and ard.source_table = 'CRH'
   and arc.type = 'CASH'
   and arh.posting_control_id <> -3
   and nvl (te.source_id_int_1, -99) = arc.cash_receipt_id
   and te.application_id = 222
   and te.entity_code = 'RECEIPTS'
   and xah.entity_id = te.entity_id
   and xah.event_id = arh.event_id
   and xah.ae_header_id = xal.ae_header_id
   and xal.code_combination_id = ard.code_combination_id
   and xdl.ae_header_id = xah.ae_header_id
   and xdl.ae_header_id = xal.ae_header_id
   and xdl.event_id = xah.event_id
   and gir.gl_sl_link_id = xal.gl_sl_link_id
   and gjh.je_header_id = gir.je_header_id
   and xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
   and gjh.je_source = 'Receivables'
   and gjh.je_category = 'Receipts'
   and xah.event_type_code <> 'MANUAL'
   and xal.gl_sl_link_table = 'XLAJEL'
   and gir.je_line_num = gjl.je_line_num
   and gjl.code_combination_id = glc.code_combination_id
   and gjl.je_header_id = gjh.je_header_id
   --and arc.receipt_number = 'MH1206201210' 
;

AR (Misc Receipt)

select distinct
       acr.receipt_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
  from gl_code_combinations gcc
      ,gl_je_lines gjl
      ,gl_je_headers gjh
      ,gl_je_batches glb
      ,gl_import_references gir
      ,xla_ae_lines xal
      ,xla_ae_headers xah
      ,xla_distribution_links xdl
      ,ar_misc_cash_distributions_all msd
      ,ar_distributions_all dist
      ,ar_cash_receipts_all acr
      ,ar_receivables_trx_all art
 where gcc.code_combination_id = gjl.code_combination_id
   and gjh.je_header_id = gjl.je_header_id
   and glb.je_batch_id = gjh.je_batch_id
   and gjl.status = 'P'
   and gjh.actual_flag = 'A'
   and gir.je_header_id = gjh.je_header_id
   and gir.gl_sl_link_id = xal.gl_sl_link_id
   and xah.ae_header_id = xal.ae_header_id
   and xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
   and gjh.je_source = 'Receivables'
   and gjh.je_category = 'Misc Receipts'
   and xdl.event_id = xah.event_id
   and xah.ae_header_id = xdl.ae_header_id
   and msd.event_id = xah.event_id
   and msd.posting_control_id <> -3
   and msd.misc_cash_distribution_id = dist.source_id
   and dist.source_type = 'MISCCASH'
   and dist.code_combination_id = gjl.code_combination_id
   and acr.cash_receipt_id = msd.cash_receipt_id
   and acr.type = 'MISC'
   and art.receivables_trx_id = acr.receivables_trx_id  
   --and acr.receipt_number = '100009 IONA GEDDES'  
union
select distinct
       acr.receipt_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
  from gl_code_combinations gcc
      ,gl_je_lines gjl
      ,gl_je_headers gjh
      ,gl_je_batches glb
      ,gl_import_references gir
      ,xla_ae_lines xal
      ,xla_ae_headers xah
      ,xla_distribution_links xdl
      ,ar_cash_receipt_history_all arh
      ,ar_distributions_all dist
      ,ar_cash_receipts_all acr
      ,ar_receivables_trx_all art
 where gcc.code_combination_id = gjl.code_combination_id
   and gjh.je_header_id = gjl.je_header_id
   and glb.je_batch_id = gjh.je_batch_id
   and gjl.status = 'P'
   and gjh.actual_flag = 'A'
   and gir.je_header_id = gjh.je_header_id
   and gir.gl_sl_link_id = xal.gl_sl_link_id
   and xah.ae_header_id = xal.ae_header_id
   and xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
   and gjh.je_source = 'Receivables'
   and gjh.je_category = 'Misc Receipts'
   and xdl.event_id = xah.event_id
   and xah.ae_header_id = xdl.ae_header_id
   and arh.event_id = xah.event_id
   and arh.posting_control_id <> -3
   and arh.cash_receipt_history_id = dist.source_id
   and dist.code_combination_id = gjl.code_combination_id
   and dist.line_id = xdl.source_distribution_id_num_1
   and acr.cash_receipt_id = arh.cash_receipt_id
   and acr.type = 'MISC'
   and art.receivables_trx_id = acr.receivables_trx_id  
   --and acr.receipt_number = 'AG308260712NY'
;

AR (Invoice)

select distinct
       rat.trx_number
      ,rat.trx_date transaction_date
      ,gjl.entered_cr
      ,gjl.entered_dr
      ,gjl.accounted_cr
      ,gjl.accounted_dr    
      ,gjh.name journal_name
      ,gjh.creation_date journal_creation_date
      ,glc.segment1,gjh.je_category
  from ra_cust_trx_line_gl_dist_all rac
      ,ra_customer_trx_all rat
      ,ra_batches_all rab
      ,ra_customer_trx_lines_all ral
      ,gl_code_combinations glc
      ,xla_distribution_links xdl
      ,xla_ae_headers xah
      ,xla_ae_lines xal
      ,gl_import_references gir
      ,gl_je_lines gjl
      ,gl_je_headers gjh
 where rat.customer_trx_id = rac.customer_trx_id
   and rac.customer_trx_line_id = ral.customer_trx_line_id (+)
   and rab.batch_id (+) = rat.batch_id
   and rac.posting_control_id <> -3
   and xdl.source_distribution_id_num_1 =  rac.cust_trx_line_gl_dist_id
   and xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
   and gjh.je_source = 'Receivables'
   and gjh.je_category = 'Sales Invoices'
   and xah.event_type_code <> ' MANUAL'
   and xah.ae_header_id = xdl.ae_header_id
   and xal.ae_header_id = xah.ae_header_id
   and xal.ae_line_num = xdl.ae_line_num
   and gir.gl_sl_link_id = xal.gl_sl_link_id
   and gjl.je_header_id = gir.je_header_id
   and gir.je_line_num = gjl.je_line_num
   and gjh.je_header_id = gjl.je_header_id
   and glc.code_combination_id = gjl.code_combination_id
   and rac.code_combination_id = gjl.code_combination_id
   and gjh.je_header_id = gir.je_header_id
   --and rat.trx_number = 'I0069600'
;

AR (Invoice Adjustment)

select distinct
       rat.trx_number
      ,rat.trx_date
      ,gjl.entered_cr
      ,gjl.entered_dr
      ,gjl.accounted_cr
      ,gjl.accounted_dr    
      ,gjh.name journal_name
      ,gjh.creation_date journal_creation_date
      ,glc.segment1
  from ar_adjustments_all ara
      ,ar_distributions_all ard
      ,ra_customer_trx_all rat
      ,ar_receivables_trx_all art
      ,gl_code_combinations_kfv glc
      ,xla_distribution_links xdl
      ,xla_ae_headers xah
      ,xla_ae_lines xal
      ,xla.xla_transaction_entities te
      ,gl_import_references gir
      ,gl_je_headers gjh
      ,gl_je_lines gjl
 where glc.code_combination_id = ard.code_combination_id
   and ara.adjustment_id = ard.source_id
   and rat.customer_trx_id = ara.customer_trx_id
   and ara.receivables_trx_id = art.receivables_trx_id
   and ard.source_table = 'ADJ'
   and ara.posting_control_id <> -3
   and xdl.source_distribution_id_num_1 = ard.line_id
   and xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
   and gjh.je_source = 'Receivables'
   and gjh.je_category = 'Adjustment'
   and xdl.event_id = xah.event_id
   and xah.ae_header_id = xdl.ae_header_id
   and xah.ae_header_id = xal.ae_header_id
   and xal.code_combination_id = ard.code_combination_id
   and xah.entity_id = te.entity_id
   and te.application_id = 222
   and te.entity_code = 'ADJUSTMENTS'
   and nvl (te.source_id_int_1, (-99)) = ara.adjustment_id
   and gir.gl_sl_link_id = xal.gl_sl_link_id
   and gjh.je_header_id = gir.je_header_id
   and gjh.je_header_id = gjl.je_header_id
   and xal.code_combination_id = gjl.code_combination_id
   --and rat.trx_number = 'A0005418'
;

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'
;

Subledger-Ledger Linkage (Purchasing)

PO (Receipt)

select distinct
       rsh.receipt_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
  from rcv_shipment_headers rsh
      ,rcv_shipment_lines rsl
      ,rcv_transactions rt
      ,rcv_receiving_sub_ledger rrsl
      ,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 rsl.shipment_header_id = rsh.shipment_header_id
   and rt.shipment_line_id = rsl.shipment_line_id
   and rrsl.rcv_transaction_id = rt.transaction_id
   and rrsl.rcv_sub_ledger_id = xdl.source_distribution_id_num_1
   and xdl.ae_line_num = xal.ae_line_num
   and xdl.ae_header_id = xal.ae_header_id
   and xdl.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'
   and xal.gl_sl_link_table = gir.gl_sl_link_table
   and xal.gl_sl_link_id = gir.gl_sl_link_id
   and xah.ae_header_id = xdl.ae_header_id
   and xah.ae_header_id = xal.ae_header_id
   and xdl.event_id = xah.event_id
   and gir.je_header_id = gjl.je_header_id
   and gir.je_line_num = gjl.je_line_num
   and gjl.je_header_id = gjh.je_header_id
   and gjh.je_batch_id = gjb.je_batch_id
   and gcc.code_combination_id = gjl.code_combination_id
   --and rsh.receipt_num = '23478'
;

PO (Transaction)

select pha.segment1 po#
      ,plla.shipment_num
      ,pra.release_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
  from po_headers_all pha
      ,po_lines_all pla
      ,po_line_locations_all plla
      ,po_distributions_all pda
      ,po_releases_all pra
      ,xla_distribution_links xdl
      ,xla_ae_lines xal
      ,gl_import_references gir
      ,gl_je_lines gjl
      ,gl_je_headers gjh
      ,gl_je_batches gjb
      ,gl_code_combinations gcc
 where pla.po_header_id = pha.po_header_id
   and plla.po_line_id = pla.po_line_id
   and pda.line_location_id = plla.line_location_id
   and pda.po_release_id = pra.po_release_id(+) 
   and pda.po_distribution_id = xdl.source_distribution_id_num_1
   and xdl.source_distribution_type = 'PO_DISTRIBUTIONS_ALL'
   and xdl.application_id = 201
   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.je_header_id = gjh.je_header_id
   and gjh.je_batch_id = gjb.je_batch_id
   and gjl.code_combination_id = gcc.code_combination_id
   --and pha.segment1= 'P12367450'
;