Search Results jg_zz_vat_alloc_rules_pk

Overview

The JG_ZZ_VAT_ALLOC_RULES table resides in the JG schema and belongs to the Regional Localizations product family within Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to capture the rules used by the VAT allocation process to assign VAT boxes to transactions. In practice, this table acts as the configuration backbone for jurisdictions — particularly those in Latin America and other regulated regions — where statutory VAT reporting requires that each fiscal transaction be mapped to specific reporting boxes (recoverable/non-recoverable tax, taxable base, and complementary informational boxes) on the tax return.

Where country-specific localization logic executes during period-end VAT processing, the allocation engine consults these rule rows to determine, per transaction, which box receives the value. The rule set is scoped to a reporting entity and discriminated by source, financial document type, VAT transaction type, tax code, and a large composite of tax attributes, allowing highly granular mapping behavior.

From a Data Vault modeling perspective, the FK topology places this table in a satellite-leaning role: it is primarily a descriptive attribute carrier keyed by a surrogate PK and referencing a reporting-entity hub, rather than a pure associative link between two business hubs.

Key Information Stored

• ALLOCATION_RULE_ID — surrogate primary key (JG_ZZ_VAT_ALLOC_RULES_PK); the technical identifier used by all downstream joins.
• VAT_REPORTING_ENTITY_ID — FK to JG_ZZ_VAT_REP_ENTITIES; scopes the rule to a specific VAT reporting entity.
• SOURCE — identifies the originating subsystem or process (e.g., AP, AR, GL) that the rule applies to.
• FINANCIAL_DOCUMENT_TYPE and VAT_TRANSACTION_TYPE — classify the transaction being allocated.
• TAX_CODE, TAX_STATUS, TAX_RATE_CODE, TAX_RATE_ID, TAX_JURISDICTION_CODE — tax determination attributes used to match the rule to a transaction.
• TAX_BOX_RECOVERABLE, TAX_BOX_NON_RECOVERABLE, TAXABLE_BOX_RECOVERABLE, TAXABLE_BOX_NON_RECOVERABLE, TOTAL_BOX — the target VAT reporting boxes for the tax and taxable amounts.
• TAX_REC_SIGN_FLAG, TAX_NON_REC_SIGN_FLAG, TAXABLE_REC_SIGN_FLAG, TAXABLE_NON_REC_SIGN_FLAG — control the sign (debit/credit direction) applied when posting to the box.
• EFFECTIVE_FROM_DATE, EFFECTIVE_TO_DATE, PERIOD_TYPE — date-effective and period-scoping controls that govern rule validity over time.
• Standard audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.

Business-key candidates are represented by the unique indexes. The most significant is JG_ZZ_VAT_ALLOC_RULES_U2, a broad composite over entity, source, document type, transaction type, tax code/status/jurisdiction/rate code, period type, effective-from date, the four box columns, and TAX_RATE_ID — effectively the natural key of the rule.

Common Use Cases and Queries

Typical scenarios include auditing VAT box mappings before a statutory filing, diagnosing why a transaction was posted to an unexpected box, and verifying that no overlapping effective-date rules exist for the same tax combination.

• Rules for a reporting entity and tax code: query JG_ZZ_VAT_ALLOC_RULES filtered by VAT_REPORTING_ENTITY_ID and TAX_CODE, ordered by EFFECTIVE_FROM_DATE, to review historical rule revisions.
• Effective rule lookup: join to JG_ZZ_VAT_REP_ENTITIES and filter where SYSDATE BETWEEN EFFECTIVE_FROM_DATE AND NVL(EFFECTIVE_TO_DATE, high-date) to reproduce what the allocation engine would apply for a given period.
• Reconciliation: join to JG_ZZ_VAT_BOX_ALLOCS on ALLOCATION_RULE_ID to trace each allocated box entry back to the governing rule.
• Exception reporting: detect rules with missing box assignments or conflicting sign flags across the same TAX_RATE_ID.
• Period-end validation: verify that every active tax code in use has at least one rule for the reporting entity before running the VAT allocation process.

Related Objects

• JG_ZZ_VAT_REP_ENTITIES — parent table joined via VAT_REPORTING_ENTITY_ID; defines the reporting entity context.
• JG_ZZ_VAT_BOX_ALLOCS — child table; its ALLOCATION_RULE_ID column references this table's primary key, storing the actual box allocations produced by each rule.
• MSC_SR_ASSIGNMENTS — references ALLOCATION_RULE_ID, linking allocation rules to supply-chain/planning assignment records.
• JG_ZZ_VAT_ALLOC_RULES_PK — the primary key constraint enforcing surrogate uniqueness.
• JG_ZZ_VAT_ALLOC_RULES_U1 / _U2 — unique indexes backing the surrogate and composite business keys.

Together these objects form the rule-definition, entity-scoping, and allocation-output chain used by the VAT reporting localization for Oracle EBS.