AP to XLA GL Query

Account Payables to XLA sunledger tables ...

Xla_Distribution_Links table

AP Invoices:-

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 (Payment) :-

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