Search Results reporting_code_char_value

Overview

ZX_REPORT_CODES_ASSOC is a transactional configuration table in the ZX (E-Business Tax) schema of Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the association between reporting types and reporting codes and the tax entities to which they apply, such as taxes, tax statuses, and tax rates. Each row records the assignment of a reporting code to one specific entity, allowing a single reporting code to be reused across multiple taxes, rates, or statuses without duplicating the code definition itself.

The table is owned by the ZX schema and holds a VALID status in the ETRM dictionary with seventeen documented columns. Its heuristic Data Vault classification, mined from the foreign-key structure, is standalone. This is best treated as a modeling suggestion: the table has no outgoing foreign keys to other ZX tables in the documented metadata, so it functions as a self-contained association entity rather than a hub, link, or satellite in a strict Data Vault sense. In practice it behaves much like a link table resolving many-to-many relationships between reporting codes and taxable entities.

Key Information Stored

The most operationally significant columns are those that define the association and carry its effective-dating semantics.

• REPORTING_CODE_ASSOC_ID — surrogate primary key of the row. It is enforced by the unique index ZX_REPORT_CODES_ASSOC_U1.
• ENTITY_CODE — discriminates the entity type being associated (for example, tax, status, or rate). Part of the composite business key.
• ENTITY_ID — the surrogate identifier of the specific entity instance referenced by ENTITY_CODE.
• REPORTING_TYPE_ID — identifies the reporting type to which the reporting code belongs.
• REPORTING_CODE_ID — the reporting code being associated with the entity.
• EXCEPTION_CODE — captures any exception qualifier applied to the association.
• EFFECTIVE_FROM and EFFECTIVE_TO — date-range columns that bound the validity of the association.
• REPORTING_CODE_CHAR_VALUE, REPORTING_CODE_DATE_VALUE, and REPORTING_CODE_NUM_VALUE — typed value columns holding the actual reporting code value in character, date, and numeric formats respectively.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the Oracle Application Framework (OAF) and concurrent programs.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN — standard WHO audit columns present on all EBS transactional tables.

The composite unique index ZX_REPORT_CODES_ASSOC_U2 (ENTITY_CODE, ENTITY_ID, REPORTING_TYPE_ID, EXCEPTION_CODE, EFFECTIVE_FROM) is the principal business-key candidate and mirrors the primary key constraint ZX_REPORT_CODES_ASSOC_PK. The duplication of the surrogate key alongside the composite key is typical of EBS seeded configuration tables and allows the row to be referenced by a stable numeric identifier while preserving the natural uniqueness of the entity/code/effective-date combination.

Common Use Cases and Queries

Typical scenarios include reporting on which taxes and rates have been assigned a particular reporting code, validating effective-dated associations during tax determination, and reconciling reporting code assignments before and after upgrades or patches.

A common pattern is to join ZX_REPORT_CODES_ASSOC to ZX_REPORT_CODES on REPORTING_CODE_ID and to the relevant entity table using ENTITY_CODE and ENTITY_ID. For example, to find all currently active associations for a given reporting code:

• SELECT a.entity_code, a.entity_id, a.reporting_code_id, a.effective_from, a.effective_to FROM zx_report_codes_assoc a WHERE a.reporting_code_id = :p_code_id AND TRUNC(SYSDATE) BETWEEN a.effective_from AND NVL(a.effective_to, TRUNC(SYSDATE));
• To identify duplicate or overlapping periods for the same entity, group by ENTITY_CODE, ENTITY_ID, REPORTING_TYPE_ID, and EXCEPTION_CODE and check for overlapping EFFECTIVE_FROM/EFFECTIVE_TO ranges.

Because the table is date-effective, reporting queries should always constrain on SYSDATE or a supplied reporting date to avoid returning historical versions.

Related Objects

The documented metadata describes this table as a standalone association entity with no outgoing foreign keys, but the natural join columns identified in its business key imply the following significant relationships:

• ZX_REPORT_CODES — joined on REPORTING_CODE_ID to retrieve code name, description, and code type.
• ZX_REPORTING_TYPES — joined on REPORTING_TYPE_ID to resolve the reporting type context.
• ZX_TAXES — the tax entity targeted when ENTITY_CODE identifies a tax.
• ZX_RATES — the rate entity targeted when ENTITY_CODE identifies a rate.
• ZX_STATUS — the status entity targeted when ENTITY_CODE identifies a status.
• ZX_REPORTING_CODES_VL — the translated view typically used for reporting code descriptions.
• ZX_API_PUB — the E-Business Tax public API package that reads and maintains reporting code associations during tax configuration.

Any query combining these objects must carefully resolve ENTITY_CODE before joining, since ENTITY_ID is only meaningful in the context of its owning entity table.