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)