Oracle EBSR12环境下的定制化报表骨架SQL。它整合了总账日记账行GL_JE_LINES、科目段值GCC、子分类账XLA以及事务来源表FUN_TRX / RA_CUSTOMER_TRX适用于BI Publisher (RTF) 或直接PL/SQL输出。适用场景需要从总账追溯至子分类账应收、应付、资产等交易明细并展示完整科目、金额、客户/供应商信息的财务分析报表。骨架SQLSELECT -- 基础标识 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 11 -- 筛选条件可根据需要调整 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_sl_link_idGL与子分类账链接IDXLA表核心关联键gcc.segment1~5根据实际COA结构修改段数量与名称fun_trx_allOracle Fusion / R12通用事务表覆盖AP、FA、PA等ra_customer_trx_all应收模块标准事务头表xal.source_distribution_type决定连接哪张业务表的关键字段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等绑定变量方便用户交互。