Results for “iv_effective_start_date”
8 results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
The PAY_VIEW_ACCRUAL_PLANS_V view, owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2, is a user-interface support view within the Payroll (PAY) product module. Its documented status is VALID and it is described in Oracle's ETRM metadata simply as "Used to support user interface." In practice this view denormalizes the accrual plan configuration and its associated payroll element constructs — accrual plan header, payroll, assignment, element entry, element type, element link, and input value — into a single queryable result set. It is not intended as an end-user reporting object but rather as a presentation-layer dependency for accrual plan forms and related PTO (paid time off) pages. Because the view flattens several effective-dated _F tables, it is frequently referenced when developers or analysts need to reconcile an assignment to its accrual plan element entry, its element link, and the input value that drives the accrual.
Underlying Base Objects
The view joins objects across HR and PAY schemas exposed via APPS synonyms. The documented referenced objects include HR_LOOKUPS (VIEW), PAY_ACCRUAL_PLANS and PAY_ACCRUAL_PLANS_TL (SYNONYMS), PAY_ELEMENT_ENTRIES_F, PAY_ELEMENT_LINKS_F, PAY_ELEMENT_TYPES_F, PAY_INPUT_VALUES_F, PAY_NET_CALCULATION_RULES, and PER_ALL_ASSIGNMENTS_F (all SYNONYMS). HR_API (PACKAGE) is also listed as a referenced base object, reflecting the forms/API dependency behind the accrual plan definition.
The defining SQL joins:
- PAY_ACCRUAL_PLANS (aliased PAP) as the driving table, supplying accrual plan identity, name, category, units of measure, business group, and the element type / input value foreign keys.
- HR_LOOKUPS (HRL, HRL1) to translate ACCRUAL_CATEGORY via lookup type 'US_PTO_ACCRUAL' and ACCRUAL_UNITS_OF_MEASURE via lookup type 'HOURS_OR_DAYS' into meaningful descriptions.
- PER_ALL_ASSIGNMENTS_F (ASS), PAY_ELEMENT_ENTRIES_F (PEE), PAY_ELEMENT_LINKS_F (PEL), PAY_ELEMENT_TYPES_F (PET), and PAY_INPUT_VALUES_F (PIV), chained through assignment ID, element link ID, element type ID, and input value ID.
Key Columns
- ACCRUAL_PLAN_ID / ACCRUAL_PLAN_NAME — the plan identity from PAY_ACCRUAL_PLANS.
- ACCRUAL_CATEGORY / ACCRUAL_CATEGORY_NAME — the coded value and its decoded meaning.
- ACCRUAL_UNITS_OF_MEASURE / ACCRUAL_UNITS_OF_MEASURE_NAME — coded value plus HOURS_OR_DAYS meaning.
- PAYROLL_ID, BUSINESS_GROUP_ID — payroll and business group context (BUSINESS_GROUP_ID is coerced with + 0).
- ASSIGNMENT_ID, ELEMENT_ENTRY_ID, PERSON_ID — the assignment, its accrual element entry, and person.
- ABSENCE_ELEMENT_TYPE_ID — the accrual plan's associated element type.
- ASG_EFFECTIVE_START_DATE / ASG_EFFECTIVE_END_DATE — the assignment-level effective dates; this is the column users typically search for as "asg_effective_start_date."
- IV_EFFECTIVE_START_DATE / IV_EFFECTIVE_END_DATE — input value effective dates.
- E_ENTRY_EFFECTIVE_START_DATE / E_ENTRY_EFFECTIVE_END_DATE — element entry effective dates.
- E_TYPE_EFFECTIVE_START_DATE / E_TYPE_EFFECTIVE_END_DATE — element type effective dates.
- E_LINK_EFFECTIVE_START_DATE / E_LINK_EFFECTIVE_END_DATE — element link effective dates.
The multiple date pairs are the dominant feature of this view: each _F table contributes its own date-tracked effectivity, and date-overlap logic must be supplied by the calling query.
Common Use Cases and Queries
Typical scenarios include: identifying the accrual plan element entry for a given assignment as of a date; reconciling element link, element type, and input value effectivity; and feeding user-interface regions for accrual plan administration.
To retrieve the assignment effective start date for a person's accrual plans:
SELECT accrual_plan_name,
assignment_id,
asg_effective_start_date,
asg_effective_end_date,
e_entry_effective_start_date
FROM apps.pay_view_accrual_plans_v
WHERE person_id = :p_person_id
AND TRUNC(:p_effective_date) BETWEEN asg_effective_start_date
AND asg_effective_end_date;
To list accrual plans and their decoded categories for a business group:
SELECT DISTINCT accrual_plan_id, accrual_plan_name,
accrual_category_name, accrual_units_of_measure_name
FROM apps.pay_view_accrual_plans_v
WHERE business_group_id = :p_business_group_id;
Because the view joins several effective-dated tables without date predicates of its own, queries must always constrain the relevant effective date columns to avoid multiplication of rows across effectivity ranges. Joins back to PAY_ELEMENT_ENTRIES_F on ELEMENT_ENTRY_ID, or to PER_ALL_ASSIGNMENTS_F on ASSIGNMENT_ID, should also be date-bounded to preserve the intended result set.
-
Used to support user interface
APPS.PAY_VIEW_ACCRUAL_PLANS_V·↳ HR_API·↳ HR_LOOKUPS·↳ PAY_ACCRUAL_PLANS·Explore PAY module →
-
Used to support user interface
APPS.PAY_VIEW_ACCRUAL_PLANS_V·↳ FND_GLOBAL·↳ HR_API·↳ HR_LOOKUPS·Explore PAY module →
-
eTRM - PAY Tables and Views 12.2.2
Temporary table used to hold invalid location addresses.
-
eTRM - PAY Tables and Views 12.1.1
Temporary table used to hold invalid location addresses.