Results for “zx_formula_b”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

ZX_FORMULA_B is the base transactional table within the Oracle E-Business Tax (ZX) module that stores the formulas used to calculate taxable basis and tax amounts in Oracle EBS Release 12.1.1 and 12.2.2. Each row represents a single formula definition that determines either the taxable basis or the tax amount for a specific tax, associated with a tax regime and a specific tax. Because taxation rules frequently require configurable expressions beyond simple rates — such as applying or excluding cash, volume, trading, transfer, transport, insurance, and other charges — this table captures the flags and modifiers that drive those computations.

From a Data Vault modeling perspective, the metadata heuristic classifies ZX_FORMULA_B as hub-leaning. Operationally, this indicates ZX_FORMULA_B functions as the central entity (hub) that anchors formula identity via its surrogate key FORMULA_ID, while descriptive and language-specific attributes are distributed to dependent tables. Its presence in the ZX schema is essential for any tax determination, tax calculation, or reporting flow requiring a stable reference to the underlying computation logic.

Key Information Stored

The table contains 51 documented columns. The most significant include:

• FORMULA_ID — the surrogate primary key (ZX_FORMULA_B_PK) uniquely identifying each formula record.
• FORMULA_CODE and FORMULA_TYPE_CODE — the user-facing identifier and the classification of the formula (e.g., taxable basis versus tax amount).
• TAX_REGIME_CODE and TAX — the tax regime and the specific tax to which the formula applies.
• EFFECTIVE_FROM / EFFECTIVE_TO — the date range that governs when the formula is valid.
• ENABLED_FLAG — indicates whether the formula is currently active.
• TAXABLE_BASIS_TYPE_CODE — identifies the taxable basis type the formula computes.
• BASE_RATE_MODIFIER — the modifier applied against the base rate.
• Charge application flags: CASH_DISCOUNT_APPL_FLAG, VOLUME_DISCOUNT_APPL_FLAG, TRADING_DISCOUNT_APPL_FLAG, TRANSFER_CHARGE_APPL_FLAG, TRANSPORT_CHARGE_APPL_FLAG, INSURANCE_CHARGE_APPL_FLAG, and OTHER_CHARGE_APPL_FLAG — each controlling whether the corresponding charge is included in the taxable basis or amount.
• CONTENT_OWNER_ID and ZD_EDITION_NAME — supporting configuration ownership and edition-based upgrade handling.
• Standard audit columns including CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, REQUEST_ID, OBJECT_VERSION_NUMBER, and ATTRIBUTE_CATEGORY through ATTRIBUTE20.

Two unique indexes serve as business-key candidates: ZX_FORMULA_B_U1 on (FORMULA_ID, ZD_EDITION_NAME) and ZX_FORMULA_B_U2 on (FORMULA_CODE, EFFECTIVE_FROM, ENABLED_FLAG, CONTENT_OWNER_ID, TAX_REGIME_CODE, ZD_EDITION_NAME). The second index effectively defines the natural business key for a formula within a regime and edition.

Common Use Cases and Queries

Typical scenarios include identifying the active formula for a given tax and date, auditing charge-application behavior, and reporting formula coverage. Sample patterns include:

• Retrieving active formulas for a tax within a regime: SELECT FORMULA_ID, FORMULA_CODE, TAXABLE_BASIS_TYPE_CODE FROM ZX_FORMULA_B WHERE TAX_REGIME_CODE = :regime AND TAX = :tax AND ENABLED_FLAG = 'Y' AND SYSDATE BETWEEN EFFECTIVE_FROM AND NVL(EFFECTIVE_TO, SYSDATE) AND ZD_EDITION_NAME = 'ORA_ZB_EDITION';
• Joining to ZX_FORMULA_TL for the translated formula display name.
• Joining to ZX_FORMULA_DETAILS to expand the formula components and operands.
• Reporting on which charge classes are included relative to the taxable basis.

Related Objects

• ZX_FORMULA_TL — joined on FORMULA_ID; holds language-translated rows for the formula.
• ZX_FORMULA_DETAILS — joined on FORMULA_ID; stores the detailed expression components that comprise each formula.
• ZX_RATES_B and ZX_RULES_B — related tax configuration objects that frequently link to formulas during tax determination.
• ZX_TAX_STATUS and ZX_TAX_REGIMES_B — provide the regime and tax context referenced by TAX_REGIME_CODE and TAX.
• ZX_LINES_B — the calculated tax lines whose taxable basis and amounts derive from these formulas.

Together with its detail and translation tables, ZX_FORMULA_B forms the configuration backbone for E-Business Tax calculation logic.