A knowledge of the Oracle SLA data model is essential for the design and build of custom sub-ledger drill-down reports. The following PowerPoint presentation provides an overview…
Here is the GL to AP Drill-down Query in a copy-paste-able format…
SELECT * FROM gl_je_batches gjb , gl_je_headers gjh , gl_je_lines gjl , gl_period_statuses gps , gl_code_combinations gcc , gl_import_references gir , xla_ae_lines xal , xla_ae_headers xah , xla.xla_transaction_entities xte -- must use XLA base table, not APPS synonym , xla_distribution_links xdl , ap_invoice_distributions_all aida , ap_invoices_all aia WHERE 1 = 1 -- identify GL journal lines AND gps.application_id = 101 AND gps.ledger_id = 1 AND gps.effective_period_num >= &P_EFF_PERIOD_NUM_FROM AND gps.effective_period_num <= &P_EFF_PERIOD_NUM_TO AND gjb.je_batch_id = gjh.je_batch_id AND gjh.period_name = gps.period_name AND gjh.ledger_id = gps.ledger_id AND gjh.status = 'P' AND gjh.actual_flag = 'A' AND gjh.je_source = 'Payables' AND gjcv.je_category_key(+) = gjh.je_category AND gjh.je_header_id = gjl.je_header_id AND gjl.code_combination_id = gcc.code_combination_id AND gcc.summary_flag = 'N' -- join GL journal line to SLA headers and lines AND NVL(gir.je_batch_id,-1) = NVL(gjh.je_batch_id,-1) AND gir.je_header_id = gjh.je_header_id AND gir.je_line_num = gjl.je_line_num AND gir.gl_sl_link_table = xal.gl_sl_link_table AND gir.gl_sl_link_id = xal.gl_sl_link_id AND gcc.code_combination_id = xal.code_combination_id AND xah.ae_header_id = xal.ae_header_id AND xah.application_id = xal.application_id AND xah.gl_transfer_status_code = 'Y' -- join SLA header to subledger transaction header AND xah.entity_id = xte.entity_id AND xte.application_id = xah.application_id AND xte.entity_code = 'AP_INVOICES' AND aia.invoice_id = xte.source_id_int_1 -- join SLA line to SLA distributions, if they exist AND xal.ae_header_id = xdl.ae_header_id(+) AND xal.ae_line_num = xdl.ae_line_num(+) AND xal.application_id = xdl.application_id(+) AND xah.event_id = NVL(xdl.event_id,xah.event_id) -- join SLA distribution to subledger tranaction distribution AND xdl.source_distribution_type(+) = 'AP_INV_DIST' AND xdl.source_distribution_id_num_1 = aida.invoice_distribution_id(+) -- join to other subledger transaction information AND aia.invoice_id = NVL(aida.invoice_id,aia.invoice_id) AND aia.org_id = NVL(aida.org_id,aia.org_id)