Vietnam Payment Voucher Report
The Vietnamese statutory payment voucher (Phiếu chi) — each payment in the local voucher format with payee, accounts, amount, and approver, as Vietnamese cash-payment rules require.
Run note · Performance The subledger (XLA) join — especially Projects-to-XLA — is a known BI Publisher performance trap on large ledgers. Pre-join it in the warehouse, or apply tight ledger, period, and source filters before running online.
Sample build of the Vietnam Payment Voucher Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.
| Voucher No | Date | Payee | Account | Amount | Approver |
|---|---|---|---|---|---|
| 1001 | 2026-04-30 | Sample | 1000-2100-000 | $1,240,500.00 | Sample |
| 1002 | 2026-03-31 | — | 1000-5400-000 | $842,150.75 | — |
| 1003 | 2026-02-28 | Sample | 1000-1410-000 | $96,400.00 | Sample |
| 1004 | 2026-01-31 | — | 2000-2100-000 | $1,005,233.10 | — |
| 1005 | 2025-12-31 | Sample | 1000-6300-000 | $58,720.40 | Sample |
| 1001 | 2026-04-30 | Sample | 1000-2100-000 | $1,240,500.00 | Sample |
The report reads AP payments tied to their XLA accounting in the Vietnamese payment-voucher layout.
11 vouchers have no approver recorded — Vietnamese cash-payment rules require an authorized approver on every payment voucher.
Enforce the approval step on payment vouchers; a missing approver invalidates the statutory voucher.
This is the report's BI Publisher data model — the SQL data set BI Publisher runs against Oracle tables to produce the output. The same SQL becomes a dbt model in your warehouse, so one definition drives both the formatted report and the analytics layer.
Data sources
Show / hide SQL
select
iby.payee_name
,ac.description
,ac.check_number payment -- iby.paper_document_number
--,iby.payment_date -- ac.check_date
,to_char(iby.PAYMENT_DATE,'DD-MON-YYYY','nls_date_language=American') PAYMENT_DATE
,iby.ext_bank_account_number sup_bank_account
,iby.ext_bank_name sup_bank_name
,iby.payment_amount
,iby.payment_currency_code
,(case when aia.description is not null then
aia.invoice_num||'-'||aia.description
else
aia.invoice_num
end) narrative
,sum((nvl(xdl.unrounded_entered_dr,0)- nvl(xdl.unrounded_entered_cr,0))) entered_amount
,sum((nvl(xdl.unrounded_accounted_dr,0) - nvl(xdl.unrounded_accounted_cr,0) )) accounted_amount
,xal.CURRENCY_CODE Entered_currency
,(case when xal.ENTERED_DR is not null then
gcc.segment4
when xal.ENTERED_CR is not null then
gcc.segment4
end) Liability_account
,gcc2.segment4 cc_account
,gcc2.segment4 cc_account2
,xdl.accounting_line_code
FROM ap_invoice_payments_all aipa
,ap_checks_all ac
,ap_payment_hist_dists aphd
,xla_distribution_links xdl
,xla_ae_lines xal
,xla_ae_lines xal2
,ap_invoices_all aia
,gl_code_combinations gcc
,iby_payments_all iby
,gl_ledgers gl
--,ce_bank_accounts cba
,gl_code_combinations gcc2
WHERE 1=1
AND ac.check_id = aipa.check_id
AND ac.ORG_ID = aipa.ORG_ID
AND aipa.invoice_payment_id = aphd.invoice_payment_id
AND xdl.source_distribution_id_num_1 = aphd.payment_hist_dist_id
AND xdl.source_distribution_type = 'AP_PMT_DIST'
--AND xdl.accounting_line_code = 'AP_LIAB_CLEAR' --'AP_LIAB_PMT'
AND xdl.accounting_line_code in ( 'AP_LIAB_PMT','AP_LIAB_CLEAR')
AND xdl.ae_header_id = xal.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xdl.application_id = xal.application_id
AND xal.ae_header_id = xal2.ae_header_id
--AND xdl.ae_line_num = xal2.ae_line_num
and xal2.ACCOUNTING_CLASS_CODE in ( 'CASH', 'CASH_CLEARING')
AND xdl.application_id = xal2.application_id
AND gcc2.code_combination_id=xal2.code_combination_id
AND aia.invoice_id = aipa.invoice_id
AND gcc.code_combination_id=xal.code_combination_id
AND iby.payment_id = ac.payment_id
AND iby.void_date is null
AND xal.ledger_id = gl.ledger_id
AND gl.ledger_category_code ='PRIMARY'
AND gl.NAME ='PL VIETNAM VND GAAP'
--AND iby.internal_bank_account_id = cba.bank_account_id
--AND cba.cash_clearing_ccid = gcc2.code_combination_id(+)
--and ac.check_number in ('158','159')
AND ac.check_number = nvl(:p_payment,ac.check_number)
AND aipa.posted_flag ='Y'
AND trunc(iby.payment_date) between :p_from_date AND :p_to_date
AND ((iby.payee_name in (:p_payee_name)) or coalesce (:p_payee_name,null) is null )
AND ac.org_id in (
SELECT distinct bu.bu_id
FROM fusion.fun_all_business_units_v bu
,fusion.fun_user_role_data_asgnmnts role
,fusion.per_users pu
,fusion.per_roles_dn_vl pr
,fusion.per_roles_dn_tl prtl
WHERE role.org_id = bu.bu_id
AND pu.user_guid = role.user_guid
AND pr.role_common_name = role.ROLE_NAME
and role.active_flag ='Y'
and pu.username = fnd_global.user_name
and pr.role_id=prtl.role_id
and prtl.LANGUAGE='US'
and prtl.ROLE_NAME
in ('XXC Accounts Payable Inquiry'
,'XXC Accounts Payables Manager'
,'XXC Accounts Payable Analyst')
)
group by
iby.payee_name
,ac.description
,ac.check_number
,to_char(iby.PAYMENT_DATE,'DD-MON-YYYY','nls_date_language=American')
,iby.ext_bank_account_number
,iby.ext_bank_name
,iby.payment_amount
,iby.payment_currency_code
,(case when aia.description is not null then
aia.invoice_num||'-'||aia.description
else
aia.invoice_num
end)
,xal.currency_code
,(case when xal.entered_dr is not null then
gcc.segment4
when xal.entered_cr is not null then
gcc.segment4
end)
,gcc2.segment4
,xdl.accounting_line_codeThe data-warehouse model — one fact surrounded by conformed dimensions (what you slice by) and measures (what you aggregate), expressed as dbt so it migrates with you. Grain: one row per source transaction.
| Element | Type | Definition |
|---|---|---|
| AP_CHECKS_ALL | dimension | dimension |
| XLA_AE_LINES | dimension | dimension |
| AP_INVOICES_ALL | dimension | dimension |
| GL_CODE_COMBINATIONS | dimension | dimension |
| Amount | measure | measure |
Every source object behind this report. Each linked table has its own page with full column descriptions, drawn from the Oracle BICC lineage and articulated for practitioners.
| Table | Reporting columns | Subject areas |
|---|---|---|
| AP_INVOICE_PAYMENTS_ALL | 22 | 2 |
| AP_CHECKS_ALL | 44 | 6 |
| XLA_AE_LINES | 23 | 17 |
| AP_INVOICES_ALL | 63 | 15 |
| GL_CODE_COMBINATIONS | 7 | 61 |