Tax Engine Integration — Orion, ONESOURCE & ZX_LINES
Canon uses two external tax engines: Azentio Orion and Thomson Reuters ONESOURCE. Both feed their determinations back into Oracle's ZX_LINES, making ZX_LINES the single source of truth for tax data regardless of which engine calculated it.
Critical Architecture Principle
DO: Use ZX_LINES as primary tax source
- ✓ ZX_LINES captures output from both Orion and ONESOURCE
- ✓ One consistent structure regardless of engine
- ✓ Already reconciled to Oracle AP/AR transactions
- ✓ Includes TAX_PROVIDER_ID to identify which engine
AVOID: Querying Orion/ONESOURCE directly for amounts
- ✗ Risk of double-counting (ZX + engine = 2x lines!)
- ✗ Different schemas per engine = maintenance burden
- ✗ Engine internals may not match final posted amounts
- ✗ Orion schema is proprietary / less documented
The #1 Rule: One Invoice Line = One Row in Output
ZX_LINES can have multiple rows per invoice line (split tax, compound rates). All workflows below use GroupBy BEFORE joining to guarantee no line explosion.
| Explosion Cause | Fix |
|---|---|
| Multiple ZX rows per invoice line | GroupBy TRX_ID + TRX_LINE_ID with SUM(TAX_AMT) |
| Joining both Orion + ZX for same amounts | Use ZX_LINES only; Orion for config/audit |
| AP_DISTRIBUTIONS (multiple per line) | Skip distributions or GroupBy first |
| Supplier with multiple sites | Always join on VENDOR_SITE_ID too |
| Joining on TRX_ID alone (header) | Include TRX_LINE_ID in all joins |
How Tax Flows Through the Stack
Oracle EBS Tax Engines Single Source of Truth
────────── ─────────── ──────────────────────
AP_INVOICES_ALL ─────┐
AP_INVOICE_LINES ────┤ ┌──────────────┐
RA_CUSTOMER_TRX ─────┼───────▶│ Orion │──────┐
│ │ (Azentio) │ │ ┌─────────────────────┐
│ └──────────────┘ ├───────▶│ ZX_LINES │
│ │ │ ═══════════════ │
│ ┌──────────────┐ │ │ Use THIS for all │
└───────▶│ ONESOURCE │──────┘ │ tax amounts, │
│ (Thomson R) │ │ reconciliation, │
└──────────────┘ │ and reporting │
└─────────────────────┘
│
▼
┌─────────────────────┐
│ Your KNIME │
│ Workflows │
└─────────────────────┘
Interactive KNIME Workflows
Click any node for detailed configuration. Each workflow also has an equivalent SQL query you can toggle.
Workflow 1: Tax Reconciliation (ZX → AP Invoice Lines)
Join ZX_LINES tax data with AP invoice lines. GroupBy first to prevent duplication. Tags each line with which engine (Orion/ONESOURCE) calculated it.
Workflow 2: Rate Change Impact Simulator
Model 'what if' scenarios: if a country changes its VAT rate, what's the annual financial impact per Canon entity? Uses historical ZX_LINES volumes.
Workflow 3: Registration Gap Detector
Cross-reference Orion registrations against actual ZX_LINES activity. Finds jurisdictions with transactions but no VAT registration — a compliance risk.
Workflow 4: Supplier VAT Number Validation
Check supplier VAT number format validity. Identifies missing, malformed, or suspiciously short numbers for data cleanup.
Workflow 5: Period-End Tax Completeness Check
Before closing a VAT period: verify every AP invoice has a corresponding ZX_LINES entry. Missing entries = the tax engine didn't process them.
When IS Orion Data Useful Directly?
Use Orion For (Config/Audit):
- ▸ Rule configuration audit — what rules exist, changes, who changed them
- ▸ Rate management — configured rates, upcoming effective date changes
- ▸ Registration tracking — entity registrations, expiry alerts
- ▸ Determination trace — WHY a specific rate was chosen
- ▸ Error analysis — failed determinations, override history
Use ZX_LINES For (Amounts/Reporting):
- ▸ VAT return preparation — actual posted tax amounts
- ▸ Reconciliation — GL to tax matching
- ▸ Reporting — tax by period, entity, jurisdiction
- ▸ Reverse charge — SELF_ASSESSED_FLAG in ZX
- ▸ E-invoicing — tax amounts going to IDF
Quick Decision Matrix
| I need... | Source | Why |
|---|---|---|
| Tax amounts for VAT return | ZX_LINES | Final posted amounts from both engines |
| Which engine calculated it | ZX_LINES (TAX_PROVIDER_ID) | Tags each determination |
| Reverse charge transactions | ZX_LINES (SELF_ASSESSED_FLAG) | Authoritative regardless of engine |
| Why a rate was chosen | Orion (DET_LOG/AUDIT) | Rule trace not in ZX |
| VAT registrations per entity | Orion (REGISTRATIONS) | Compliance config |
| Upcoming rate changes | Orion (TAX_RATES) | Future-dated config |
Orion Schema Discovery
Note: Orion table names above are inferred from tax engine patterns. Run these discovery queries to map YOUR actual instance, then adjust workflows accordingly.
List All Orion Tables
SELECT table_name, num_rows FROM all_tables WHERE owner = 'ORION' ORDER BY table_name;
Find Cross-System Bridge Columns
SELECT table_name, column_name FROM all_tab_columns
WHERE owner = 'ORION'
AND (column_name LIKE '%SOURCE%' OR column_name LIKE '%TRX%'
OR column_name LIKE '%ORG_ID%' OR column_name LIKE '%INVOICE%')
ORDER BY table_name;KNIME No-Code: Browse Tables
Use DB Table Selector → browse mode to see all tables. Then DB Reader with TOP 100 to explore. No SQL needed for initial discovery!