Search Results je_be_vat_rep_rules_pk

Overview

JE_BE_VAT_REP_RULES is a reference table owned by the GL schema within the JE - European Localizations product family in Oracle EBS 12.1.1 and 12.2.2. It stores the reporting rules that determine how tax and transaction amounts are allocated or reallocated to specific VAT reporting boxes on Belgian VAT returns and related statutory reports. Each row defines a mapping between a source, transaction types, line type, and tax name on one side, and the destination VAT reporting boxes (regular and non-recoverable) on the other. The table therefore acts as the configuration backbone for VAT box derivation rather than as a transactional or accounting entry table.

From a dimensional modeling perspective, the mined FK structure classifies this object as a standalone table, meaning it has no declared foreign key dependencies and is not naturally a hub, link, or satellite. Where a Data Vault model is desired, it is most appropriately treated as a reference satellite or lookup dimension, since it carries descriptive rule attributes keyed by a composite business key rather than participating in entity relationships.

Key Information Stored

The primary key, JE_BE_VAT_REP_RULES_PK, is a composite surrogate/business key spanning SET_OF_BOOKS_ID, SOURCE, OF_TRANS_TYPE, VAT_TRANS_TYPE, LINE_TYPE, and TAX_NAME. A second unique index, JE_BE_VAT_REP_RULES_U1, extends this key with VAT_REPORT_BOX and NOREC_VAT_REP_BOX, confirming that box assignment is part of the effective uniqueness of a rule. The most significant columns are:

• SET_OF_BOOKS_ID, the ledger identifier scoping the rule to a specific set of books.
• SOURCE, the originating source of the transaction or data feed.
• OF_TRANS_TYPE, the Oracle Financials transaction type classification.
• VAT_TRANS_TYPE, the VAT-specific transaction type used for reporting logic.
• LINE_TYPE, distinguishing the line category within the transaction.
• TAX_NAME, the tax or tax rate name matched against the transaction.
• VAT_REPORT_BOX, the target VAT reporting box for recoverable amounts.
• NOREC_VAT_REP_BOX, the target box for non-recoverable VAT amounts.
• SIGN_FLAG, controlling whether an amount is added to or deducted from the box.
• START_DATE_ACTIVE and END_DATE_ACTIVE, defining the effective date range of the rule.
• ENABLED_FLAG, the runtime activation indicator.
• The standard WHO audit columns: LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, and CREATED_BY.

Common Use Cases and Queries

Typical usage includes validating that each active tax configuration maps to the intended boxes, diagnosing why an amount landed in an unexpected box, and auditing rule changes over time. A representative query lists active rules for a ledger:

• SELECT source, of_trans_type, vat_trans_type, line_type, tax_name, vat_report_box, norec_vat_rep_box, sign_flag FROM gl.je_be_vat_rep_rules WHERE set_of_books_id = :ledger_id AND enabled_flag = 'Y' AND TRUNC(SYSDATE) BETWEEN start_date_active AND NVL(end_date_active, TRUNC(SYSDATE));
• Identify gaps: LEFT JOIN the rules to the tax names in AP/AR tax setups to find taxes with no box assignment.
• Effective-dating audit: ORDER BY tax_name, start_date_active to review historical rule versions.

Related Objects

Because the metadata classifies this table as standalone, no formal FK relationships are documented. It is nonetheless functionally linked by convention to the following:

• GL_JE_LINES and GL_JE_HEADERS, joined by SET_OF_BOOKS_ID to associate journal activity governed by these rules.
• AP_TAX_CODES / AR_TAX_CODES configurations, joined by TAX_NAME to validate coverage.
• FND_ID_FLEX_STRUCTURES and GL_SETS_OF_BOOKS, joined by SET_OF_BOOKS_ID to resolve ledger context.
• GL_LOOKUPS, used to decode SOURCE, LINE_TYPE, and transaction type values.
• VAT reporting concurrent programs under the JE European Localizations module, which read this table at run time to derive box totals.