Data Lake → Extract Builder

KNIME Extract Builder

Build multi-table extracts step by step using DB nodes. Know which table is authoritative for each field, how to join correctly, and when to filter and group to avoid row explosion.

The Golden Pattern: Filter → Join → GroupBy → Join

Complex extracts across Oracle modules require a specific sequence. You cannot simply select all tables and let a single query handle everything — the joins multiply rows unless you control cardinality at each step.

1. Filter lines (ITEM only)2. Join to tax (ZX_LINES)3. GroupBy to 1 row per line4. Join enrichment tables5. Output

This ensures: 1 invoice line = 1 output row. The enrichment joins (supplier name, GL account, entity name) are safe because they are N:1 lookups.

Field Finder

Search for any field — see ALL tables where it exists, which one to use, and the exact join key to get it.

Invoice / Transaction Number

The human-readable document reference (invoice number, credit note number, AR transaction number).

AP_INVOICES_ALL.INVOICE_NUMVARCHAR2(50)

AP supplier invoices. Join from lines via INVOICE_ID.

USE THIS
Join: Direct — this is the AP header table
RA_CUSTOMER_TRX_ALL.TRX_NUMBERVARCHAR2(20)

AR sales invoices and credit memos. Join from ZX_LINES via TRX_ID where APPLICATION_ID = 222.

USE THIS
Join: Direct — this is the AR header table
AP_CHECKS_ALL.CHECK_NUMBERNUMBER

Payment document number — not the invoice number itself.

Join: Join from AP_INVOICE_PAYMENTS_ALL.CHECK_ID
IDF_EINVOICE_LOG.SOURCE_TRX_IDNUMBER

References the invoice by ID, not by number. Join to AP/AR header for the actual number.

Join: SOURCE_TRX_ID = INVOICE_ID (AP) or CUSTOMER_TRX_ID (AR)
Warning: AP and AR use DIFFERENT column names for the same concept (INVOICE_NUM vs TRX_NUMBER). If you need both in one extract, use COALESCE(ai.INVOICE_NUM, rct.TRX_NUMBER) AS DOCUMENT_NUMBER.
Tip: ZX_LINES does NOT store the invoice number — it only stores TRX_ID (numeric). You must join back to AP_INVOICES_ALL or RA_CUSTOMER_TRX_ALL to get the human-readable number.

Invoice / Line Amount (excl. tax)

The monetary amount of an invoice or line item, excluding tax.

AP_INVOICES_ALL.INVOICE_AMOUNTNUMBER

Header-level total (includes all lines). Use for total invoice value.

USE THIS
Join: Direct — header table
AP_INVOICE_LINES_ALL.AMOUNTNUMBER

Per-line amount. Filter LINE_TYPE_LOOKUP_CODE = ITEM to exclude tax/freight lines.

USE THIS
Join: Join via INVOICE_ID
RA_CUSTOMER_TRX_LINES_ALL.EXTENDED_AMOUNTNUMBER

AR line amounts. Filter LINE_TYPE = LINE (not TAX).

USE THIS
Join: Join via CUSTOMER_TRX_ID
AP_INVOICE_DISTRIBUTIONS_ALL.AMOUNTNUMBER

Distribution-level — may be MULTIPLE rows per line (split by GL account). SUM needed.

Join: Join via INVOICE_ID + INVOICE_LINE_NUMBER
ZX_LINES.TAXABLE_AMTNUMBER

The base amount on which tax was calculated. Useful for verification.

Join: TRX_ID = INVOICE_ID + TRX_LINE_ID = LINE_NUMBER
Warning: AP_INVOICE_DISTRIBUTIONS_ALL has MULTIPLE rows per invoice line (cost center splits). Never use this as your line amount without GROUP BY INVOICE_ID + INVOICE_LINE_NUMBER first.
Tip: For "net amount per line": use AP_INVOICE_LINES_ALL.AMOUNT with filter LINE_TYPE_LOOKUP_CODE = ITEM.

Tax Amount

The tax (VAT/GST) amount calculated for a transaction.

ZX_LINES.TAX_AMTNUMBER

★ SINGLE SOURCE OF TRUTH. Contains output from both Orion and ONESOURCE engines.

USE THIS
Join: TRX_ID = INVOICE_ID, TRX_LINE_ID = LINE_NUMBER, APPLICATION_ID = 200(AP)/222(AR)
AP_INVOICE_LINES_ALL.AMOUNT (where LINE_TYPE = TAX)NUMBER

⚠ AVOID — these are duplicates of ZX_LINES. Using both causes double-counting.

Join: INVOICE_ID
ORION_DET_LOG.(no amount column)—

Audit log only — does not store amounts. Use ZX_LINES instead.

Join: SOURCE_TRX_ID = INVOICE_ID
Warning: NEVER join both ZX_LINES and AP tax lines (LINE_TYPE=TAX) — they contain the same data and will double your tax amounts. Use ZX_LINES only.
Tip: If one invoice line has multiple tax types, ZX_LINES has multiple rows. GroupBy TRX_ID + TRX_LINE_ID with SUM(TAX_AMT) to get one total per line.

Tax Rate / Percentage

The tax rate percentage applied to a transaction line.

ZX_LINES.TAX_RATENUMBER

The ACTUAL rate applied to this specific transaction. Use this for reporting.

USE THIS
Join: TRX_ID = INVOICE_ID
ZX_RATES_B.PERCENTAGE_RATENUMBER

Master rate definitions with validity dates. Use for rate-change analysis, not for transaction reporting.

Join: TAX_RATE_CODE (logical match)
ORION_TAX_RATES.TAX_RATE_PERCENTNUMBER(5,2)

Orion configuration. Shows what rate Orion WOULD apply, not what was actually used.

Join: RATE_ID
Tip: ZX_LINES.TAX_RATE is the transactional rate. ZX_RATES_B.PERCENTAGE_RATE is the master definition. For "what rate was applied to invoice X" always use ZX_LINES.

Tax Regime / Country VAT System

Which country/tax system applies (e.g., NL_VAT, DE_VAT, FR_VAT).

ZX_LINES.TAX_REGIME_CODEVARCHAR2(30)

The regime applied to this transaction. Filter here for country-specific VAT returns.

USE THIS
Join: Direct on tax line
ZX_REGIMES_B.TAX_REGIME_CODEVARCHAR2(30)

Regime definitions with country code and full name. Join for COUNTRY_CODE or display name.

Join: Match on TAX_REGIME_CODE
ORION_JURISDICTIONS.COUNTRY_CODEVARCHAR2(2)

Orion equivalent. Maps to ZX regime via COUNTRY_CODE.

Join: JURISDICTION_ID

Reverse Charge / Self-Assessment

Whether VAT is reverse-charged (buyer accounts for VAT instead of seller).

ZX_LINES.SELF_ASSESSED_FLAGVARCHAR2(1)

Y = reverse charge applied. This is the transactional flag set by the tax engine.

USE THIS
Join: TRX_ID = INVOICE_ID
ORION_TAX_RULES.REVERSE_CHARGE_FLAGVARCHAR2(1)

Configuration only — shows rules where reverse charge CAN apply, not where it DID apply.

Join: RULE_ID
Tip: Filter ZX_LINES.SELF_ASSESSED_FLAG = Y to find all reverse charge transactions. These go in a separate box on the VAT return.

Supplier Name

The name of the supplier/vendor on a purchase invoice.

AP_SUPPLIERS.VENDOR_NAMEVARCHAR2(240)

Master supplier table. Join: AP_INVOICES_ALL.VENDOR_ID = AP_SUPPLIERS.VENDOR_ID

USE THIS
Join: VENDOR_ID (from AP_INVOICES_ALL)
HZ_PARTIES.PARTY_NAMEVARCHAR2(360)

TCA party model — same name, different join path. Used more in AR.

Join: AP_SUPPLIERS.PARTY_ID = HZ_PARTIES.PARTY_ID
Tip: AP_INVOICES_ALL does NOT store the supplier name directly — only VENDOR_ID. You always need to join to AP_SUPPLIERS.

Supplier VAT / Tax Registration Number

The VAT registration number of a supplier.

AP_SUPPLIER_SITES_ALL.VAT_REGISTRATION_NUMVARCHAR2(20)

Per-SITE VAT number. Multi-country suppliers have different numbers per site. Join via VENDOR_SITE_ID.

USE THIS
Join: VENDOR_SITE_ID (from AP_INVOICES_ALL)
AP_SUPPLIERS.VAT_REGISTRATION_NUMVARCHAR2(20)

Header-level default. Often blank or only one country. Less reliable than site-level.

Join: VENDOR_ID
ZX_PARTY_TAX_PROFILE.REP_REGISTRATION_NUMBERVARCHAR2(30)

eBTax party tax profile. More complete but harder join path.

Join: Complex: via HZ_PARTIES.PARTY_ID
ZX_REGISTRATIONS.REGISTRATION_NUMBERVARCHAR2(30)

All registered VAT numbers per party. Useful for "where is this supplier registered" questions.

Join: PARTY_TAX_PROFILE_ID
Warning: Always use VENDOR_SITE_ID (not just VENDOR_ID) when joining for VAT numbers. A supplier like "Amazon" may have NL, DE, FR sites each with a different VAT number.
← Data Lake HubMore KNIME Workflows →