Oracle EBS（R12）环境下的定制化报表骨架SQL。它整合了总账日记账行（GL_JE_LINES）、科目段值（GCC）、子分类账（XLA）以及事务来源表（FUN_TRX / RA_CUSTOMER_TRX），适用于BI Publisher (RTF) 或直接PL/SQL输出。
适用场景：
需要从总账追溯至子分类账（应收、应付、资产等）交易明细，并展示完整科目、金额、客户/供应商信息的财务分析报表。
骨架SQL
SELECT -- ====================== 基础标识 ====================== gjh.je_batch_id, gjh.je_header_id, gjh.name AS journal_name, gjh.doc_sequence_value AS voucher_number, gjh.period_name, gjh.default_effective_date AS gl_date, -- ====================== 科目结构（GCC） ====================== gcc.segment1 AS company, gcc.segment2 AS cost_center, gcc.segment3 AS account, gcc.segment4 AS sub_account, gcc.segment5 AS product, gcc.chart_of_accounts_id, -- ====================== 行明细 ====================== gjl.je_line_num, gjl.description AS line_description, gjl.accounted_dr AS dr_amount, gjl.accounted_cr AS cr_amount, NVL(gjl.accounted_dr, 0) - NVL(gjl.accounted_cr, 0) AS net_amount, -- ====================== 子分类账（XLA） ====================== xah.event_type_code, xah.event_date, xal.source_distribution_type, xal.source_distribution_id_num_1, xal.currency_code, xal.accounted_amount AS xla_amount, -- ====================== 业务实体（FUN_TRX / AR） ====================== -- 优先取 FUN_TRX（应付/资产/项目等） fun_trx.trx_number AS fun_trx_number, fun_trx.vendor_name AS fun_vendor_name, fun_trx.invoice_date AS fun_invoice_date, -- 其次取应收客户事务 rct.trx_number AS ar_trx_number, rct.bill_to_customer_name AS ar_customer_name, rct.trx_date AS ar_trx_date, -- ====================== 辅助字段 ====================== gjl.status AS line_status, gjl.reference1, gjl.reference2, gjl.reference5 FROM gl_je_lines gjl INNER JOIN gl_je_headers gjh ON gjh.je_header_id = gjl.je_header_id INNER JOIN gl_code_combinations gcc ON gcc.code_combination_id = gjl.code_combination_id -- ---------- 左连接 XLA 子分类账行 ---------- LEFT JOIN xla.xla_ae_lines xal ON xal.gl_sl_link_id = gjl.gl_sl_link_id AND xal.application_id = gjl.application_id AND xal.ledger_id = gjl.ledger_id LEFT JOIN xla.xla_ae_headers xah ON xah.ae_header_id = xal.ae_header_id AND xah.application_id = xal.application_id -- ---------- 左连接 FUN_TRX（应付/资产/项目等） ---------- LEFT JOIN ( SELECT ft.transaction_number AS trx_number, ft.transaction_date AS invoice_date, pov.vendor_name, ft.source_table, ft.source_id FROM fun_trx_all ft LEFT JOIN po_vendors pov ON pov.vendor_id = ft.vendor_id WHERE ft.source_table IN ('AP_INVOICES', 'FA_TRANSACTIONS', 'PA_EXPENDITURE_ITEMS') ) fun_trx ON fun_trx.source_table = xal.source_distribution_type AND fun_trx.source_id = xal.source_distribution_id_num_1 -- ---------- 左连接应收客户事务 ---------- LEFT JOIN ( SELECT ct.customer_trx_id, ct.trx_number, hp.party_name AS bill_to_customer_name, ct.trx_date FROM ra_customer_trx_all ct LEFT JOIN hz_cust_accounts hca ON hca.cust_account_id = ct.bill_to_customer_id LEFT JOIN hz_parties hp ON hp.party_id = hca.party_id ) rct ON rct.customer_trx_id = xal.source_distribution_id_num_1 AND xal.source_distribution_type = 'RA_CUSTOMER_TRX' WHERE 1=1 -- ====================== 筛选条件（可根据需要调整） ====================== AND gjh.period_name BETWEEN '2026-JAN' AND '2026-DEC' -- 会计期范围 AND gcc.segment3 LIKE '6%' -- 示例：只取费用类科目（以6开头） AND gjh.status = 'P' -- 过账凭证 AND gjl.status = 'P' ORDER BY gjh.default_effective_date, gjh.je_header_id, gjl.je_line_num;关键说明
字段/表 | 含义与用途 |
|---|---|
| GL与子分类账链接ID，XLA表核心关联键 |
| 根据实际COA结构修改段数量与名称 |
| Oracle Fusion / R12通用事务表，覆盖AP、FA、PA等 |
| 应收模块标准事务头表 |
| 决定连接哪张业务表的关键字段 |
BI Publisher RTF 模板提示
数据源：将此SQL放入
Data Model > SQL Query。布局：
表格列对应上述
SELECT字段。对
dr_amount,cr_amount设置格式掩码#,##0.00。分组可按
period_name或company做分组小计。
参数：建议添加
p_period_from,p_period_to,p_account_segment等绑定变量，方便用户交互。
)
