Search Results jg_zz_vat_alloc_rules_v

Overview

The JG_ZZ_VAT_ALLOC_RULES_V view is a reporting and integration object within the JG – Regional Localizations product of Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2. It resides in the APPS schema and is defined over the VAT allocation rules configuration used by regional Value Added Tax (VAT) reporting solutions, particularly for jurisdictions such as Greece that require detailed VAT box allocation and recoverability reporting.

The view exposes the configuration metadata that governs how tax and taxable amounts, both recoverable and non-recoverable, are allocated to reporting boxes on a VAT return. Because a single physical column set is stored in the base table, the view flattens it into a convenient read model that joins allocation rules to reporting entities, tax codes, tax statuses, jurisdictions, and effective date ranges.

Its principal role is to support VAT reporting engines, tax extract programs, and reconciliation reports that need to resolve which reporting box a given tax amount maps to. The user search term tax_status is directly relevant here: the view surfaces a TAX_STATUS column, which together with TAX_CODE and TAX_ID enables downstream logic to pick the correct allocation rule for a transaction line based on the status of the tax determining factor.

Underlying Base Objects

Per the documented ETRM metadata, the view is defined over a single base object:

• JG_ZZ_VAT_ALLOC_RULES (SYNONYM) — the underlying allocation rules table.

The view text is a straightforward projection (no joins or aggregations). It selects ROWID plus the full set of allocation rule attributes from JG_ZZ_VAT_ALLOC_RULES. No filtering predicate or WHERE clause is applied, so the view returns all rows present in the base table. This makes the view a read-only, one-to-one representation of the base table, which simplifies grants, synonyms, and integration access while preserving the canonical structure.

The presence of the ROWID pseudo-column as the first projected item, exposed as ROW_ID, gives consumers a stable reference to the physical row for update or de-duplication logic, even though the view itself is intended for query access.

Key Columns

The view exposes the following notable columns:

• ALLOCATION_RULE_ID — Primary identifier for each allocation rule row.
• VAT_REPORTING_ENTITY_ID — The reporting entity (legal entity/registration) to which the rule is scoped.
• SOURCE and FINANCIAL_DOCUMENT_TYPE — Identify the originating process or document class the rule applies to.
• VAT_TRANSACTION_TYPE — The VAT transaction category driving the allocation.
• TAX_ID, TAX_CODE, TAX_STATUS — The tax classification keys. TAX_STATUS distinguishes the tax state (for example, recoverable versus non-recoverable treatment), and is central to selecting the correct rule.
• TAX_JURISDICTION_CODE, TAX_RATE_CODE, TAX_RATE_ID — Jurisdiction and rate references.
• TAX_BOX_RECOVERABLE, TAX_BOX_NON_RECOVERABLE, TAXABLE_BOX_RECOVERABLE, TAXABLE_BOX_NON_RECOVERABLE, TOTAL_BOX — Target VAT return boxes for tax and taxable amounts.
• EFFECTIVE_FROM_DATE, EFFECTIVE_TO_DATE, PERIOD_TYPE — Date and period validity of the rule.
• TAX_REC_SIGN_FLAG, TAX_NON_REC_SIGN_FLAG, TAXABLE_REC_SIGN_FLAG, TAXABLE_NON_REC_SIGN_FLAG — Sign indicators controlling debit/credit direction in each box.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard audit columns.

Common Use Cases and Queries

The view is typically queried to retrieve the active allocation rule for a given tax code and status within a reporting period, or to audit configured rules for a reporting entity.

SELECT allocation_rule_id,
       vat_reporting_entity_id,
       tax_code,
       tax_status,
       tax_box_recoverable,
       tax_box_non_recoverable,
       effective_from_date,
       effective_to_date
FROM   apps.jg_zz_vat_alloc_rules_v
WHERE  tax_code = :p_tax_code
AND    tax_status = :p_tax_status
AND    TRUNC(SYSDATE) BETWEEN effective_from_date AND NVL(effective_to_date, TRUNC(SYSDATE));

Additional scenarios include:

• Populating VAT return box totals by joining resolved rules to tax lines for a reporting entity and period.
• Validating that no overlapping effective date ranges exist for a given tax code, status, and reporting entity.
• Reconciling recoverable versus non-recoverable amounts using the corresponding sign flags.

Because the view is unfiltered and defined directly over the base synonym, it is well suited to integration extracts and ad-hoc reporting while minimizing exposure of the full physical table definition.