Search Results jg_zz_vat_alloc_rules_u1

Overview

JG.JG_ZZ_VAT_ALLOC_RULES is a transaction data table in the Oracle E-Business Suite 12.1.1 and 12.2.2 ETRM (E-Business Tax Reporting and Management) schema. It stores the VAT allocation rules that drive the allocation process used to assign VAT boxes to individual transaction lines for tax reporting purposes. For each combination of transaction source, financial document type, and VAT transaction type, the table captures the rule that determines which reporting box is applied to lines categorized as TAX or TAXABLE. This makes the table the central configuration repository connecting tax determination data with country-specific VAT return box layouts.

The table is owned by the JG schema and is registered in FND Design Data as JG.JG_ZZ_VAT_ALLOC_RULES. It resides in the APPS_TS_TX_DATA tablespace, with indexes stored separately in APPS_TS_TX_IDX. Based on the mined foreign-key structure, the Data Vault classification heuristic suggests a satellite-leaning model: the table records descriptive allocation attributes that are dependent on the reporting entity and the rule's effective dating context, rather than acting as a pure hub or link.

Key Information Stored

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

• ALLOCATION_RULE_ID — the surrogate primary key (NUMBER(15)), enforced by the unique index JG_ZZ_VAT_ALLOC_RULES_U1 and the primary key constraint JG_ZZ_VAT_ALLOC_RULES_PK.
• VAT_REPORTING_ENTITY_ID — identifies the reporting entity to which the rule belongs; also a foreign key to JG_ZZ_VAT_REP_ENTITIES.
• SOURCE — the transaction source; documented values are AP, AR, and GL.
• FINANCIAL_DOCUMENT_TYPE and VAT_TRANSACTION_TYPE — together with SOURCE, define the transaction context in which the rule applies.
• TAX_CODE, TAX_STATUS, TAX_JURISDICTION_CODE, TAX_RATE_CODE, and TAX_RATE_ID — the tax determination attributes matched by the rule.
• TAX_BOX_RECOVERABLE / TAX_BOX_NON_RECOVERABLE — the VAT box numbers assigned to tax lines depending on recoverability.
• TAXABLE_BOX_RECOVERABLE / TAXABLE_BOX_NON_RECOVERABLE — the VAT box numbers assigned to taxable lines.
• TOTAL_BOX — the box used for total amounts.
• EFFECTIVE_FROM_DATE, EFFECTIVE_TO_DATE, and PERIOD_TYPE — the effective dating that makes the rule valid for a particular reporting period.

The second unique index, JG_ZZ_VAT_ALLOC_RULES_U2, spans the business-key combination of VAT_REPORTING_ENTITY_ID, SOURCE, FINANCIAL_DOCUMENT_TYPE, VAT_TRANSACTION_TYPE, TAX_CODE, TAX_STATUS, TAX_JURISDICTION_CODE, TAX_RATE_CODE, PERIOD_TYPE, EFFECTIVE_FROM_DATE, the four box columns, and TAX_RATE_ID. This enforces that only one active allocation rule exists per entity/transaction/tax context and effective period.

Common Use Cases and Queries

Typical uses include auditing VAT return box mapping, troubleshooting missing or incorrect box assignments during VAT reporting, and verifying rule coverage across sources and entities. A common query retrieves all active rules for an entity as of a reporting date:

• SELECT allocation_rule_id, source, financial_document_type, vat_transaction_type, tax_code, tax_box_recoverable, tax_box_non_recoverable, taxable_box_recoverable, taxable_box_non_recoverable FROM jg_zz_vat_alloc_rules WHERE vat_reporting_entity_id = :entity_id AND effective_from_date <= :report_date AND (effective_to_date IS NULL OR effective_to_date >= :report_date);
• Joining to JG_ZZ_VAT_REP_ENTITIES to resolve the entity name for a given rule.
• Joining to JG_ZZ_VAT_BOX_ALLOCS on ALLOCATION_RULE_ID to trace which lines were actually allocated using a specific rule.
• Detecting duplicate or overlapping effective-date ranges using the U2 business key columns.

Related Objects

The following objects are most significant to JG_ZZ_VAT_ALLOC_RULES:

• JG_ZZ_VAT_REP_ENTITIES — referenced via VAT_REPORTING_ENTITY_ID; supplies reporting entity definitions.
• JG_ZZ_VAT_BOX_ALLOCS — references this table via ALLOCATION_RULE_ID; stores the resulting box allocations applied to transaction lines.
• MSC_SR_ASSIGNMENTS — references ALLOCATION_RULE_ID; links allocation rules to assignment records.
• JG_ZZ_VAT_ALLOC_RULES_U1 and U2 — unique indexes enforcing the surrogate and business keys.
• JG_ZZ_VAT_ALLOC_RULES_PK — primary key constraint on ALLOCATION_RULE_ID.