Results for “valid_from”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The HR_DE_TAX_EXEMPTIONS_V view resides in the APPS schema of Oracle E-Business Suite and belongs to the PER (Human Resources) product family. It presents German statutory tax exemption data that is stored in Oracle Payroll and Human Resources as element entries. Rather than maintaining a dedicated German tax exemption table, the application models each exemption record as an element entry, storing the substantive tax attributes in the generic ENTRY_INFORMATION descriptive flexfield segments of the element entry.

The view's principal role is to expose those embedded values in a structured, reporting-friendly shape. It flattens the flexfield segments into named columns (form number, issue date, validity dates, and tax-free remuneration) and applies type conversions so that downstream consumers—BI Publisher reports, extracts, interfaces, and ad hoc queries—can work with the data without decoding ENTRY_INFORMATIONn directly. Because it is a view and carries no data of its own, it always reflects the current state of the underlying element entries.

Underlying Base Objects

The view is defined over three documented base objects:

• PAY_ELEMENT_ENTRIES_F (synonym) — the primary source. This is the element entry entity that holds the assignment, validity dates, and the descriptive flexfield segments (ENTRY_INFORMATION1 through ENTRY_INFORMATION5).
• FND_SESSIONS (synonym) — joined via USERENV('SESSIONID') = SES.SESSION_ID to obtain the current session's effective date, which drives the year-based filter applied to the element entries.
• FND_DATE (package) — supplies CANONICAL_TO_DATE, used to convert the canonical date strings stored in ENTRY_INFORMATION3 and ENTRY_INFORMATION4 into true date values.

The join predicates restrict the result set to entries whose context category is DE_TAX EXEMPTIONS and whose entry type is E. Entries are further constrained so that their EFFECTIVE_START_DATE and EFFECTIVE_END_DATE fall within the calendar year of the session's effective date, meaning the view returns exemption records relevant to the year in which the query is executed.

Key Columns

• ROW_ID — the ROWID of the underlying element entry row.
• OBJECT_VERSION_NUMBER — optimistic locking version number, useful for any update processing.
• ELEMENT_ENTRY_ID — primary identifier of the element entry.
• FORM_NUMBER — first 30 characters of ENTRY_INFORMATION1; the German tax exemption certificate/form reference.
• ISSUE_DATE — first 30 characters of ENTRY_INFORMATION2; the date the form was issued.
• VALID_FROM — ENTRY_INFORMATION3 converted via FND_DATE.CANONICAL_TO_DATE; the start of the exemption's validity window. This is the column most closely associated with the search term valid_from.
• VALID_TO — ENTRY_INFORMATION4 converted to a date; the end of the validity window.
• TAX_FREE_REMUNERATION — first 30 characters of ENTRY_INFORMATION5; the tax-free amount.
• ASSIGNMENT_ID — the assignment to which the element entry belongs.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-effective range of the element entry itself, distinct from the exemption's VALID_FROM/VALID_TO.

Common Use Cases and Queries

Typical uses include extracting active German tax exemptions for a payroll period or year, auditing which employees hold valid exemption certificates, and feeding downstream tax reporting. Because the view already scopes results to the current session year, callers should be aware that older or future-year records are not returned.

Retrieving exemptions for a specific assignment:

• SELECT element_entry_id, form_number, issue_date, valid_from, valid_to, tax_free_remuneration FROM apps.hr_de_tax_exemptions_v WHERE assignment_id = :p_assignment_id;

Finding exemptions valid on a given date:

• SELECT assignment_id, form_number, valid_from, valid_to FROM apps.hr_de_tax_exemptions_v WHERE :p_date BETWEEN valid_from AND valid_to ORDER BY assignment_id, valid_from;

Listing all exemptions issued in the current session year:

• SELECT form_number, assignment_id, valid_from, valid_to FROM apps.hr_de_tax_exemptions_v ORDER BY issue_date DESC;

Note that VALID_FROM and VALID_TO are canonical date conversions and may be null where the corresponding flexfield segment is unpopulated; queries relying on date comparisons should guard against nulls accordingly.