Results for “position_dp”

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

Overview

EDWBV_HR_PSTN_POSITION_LCV is a database view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the Human Resources Intelligence (HRI) product family. Its name follows the Oracle Business Intelligence System (OBIS) / Enterprise Data Warehouse (EDW) naming convention, in which the EDWBV prefix denotes an EDW Business View, HR identifies the Human Resources domain, PSTN abbreviates Position, and the _LCV suffix designates a "List of Values" / concatenated-value view used for dimensional flattening.

The view presents a denormalized, reporting-ready representation of the PER_ALL_POSITIONS entity. It combines each position with its owning business group (organization unit), its position definition, and the local instance code, and it derives several synthesized columns such as a composite primary key and a descriptive display name. Because it is a view rather than a table, it exposes no independently stored data; it is a query-time projection intended for consumption by HR Intelligence extract programs, Oracle Discoverer workbooks, and downstream EDW staging routines. Status is documented as VALID in the APPS schema, indicating the underlying objects resolve cleanly on both EBS 12.1.1 and 12.2.2.

Underlying Base Objects

The view text joins four documented base objects in the FROM clause:

• PER_ALL_POSITIONS POS — the primary source of position rows, supplying position identifiers, dates, working hours, and definition/group foreign keys.
• PER_POSITION_DEFINITIONS POD — joined on POSITION_DEFINITION_ID to supply the reusable position definition record.
• HR_ALL_ORGANIZATION_UNITS BGR — joined on POS.BUSINESS_GROUP_ID = BGR.ORGANIZATION_ID to resolve the business group name.
• EDW_LOCAL_INSTANCE INST — a single-row HRI control table supplying the INSTANCE_CODE that identifies the local EBS instance.

The join is an inner join across PER_ALL_POSITIONS, PER_POSITION_DEFINITIONS, and HR_ALL_ORGANIZATION_UNITS, with EDW_LOCAL_INSTANCE included to stamp the instance context. Lineage flags are embedded as literal columns: '_DF:PER:PER_POSITIONS:POS' marks the data foundation for the position record, and '_KF:PER:POS:POD' marks the key flexfield linkage to the position definition. Although ETRM lists "none documented" for referenced base objects, the shipped view text explicitly names the four tables above.

Key Columns

• POSITION_PK — synthetic key formed as POSITION_ID concatenated with INSTANCE_CODE via a hyphen; used as the surrogate key in dimensional models.
• INSTANCE — the instance code from EDW_LOCAL_INSTANCE, supporting multi-instance consolidation.
• NAME / POSITION_DP — display strings composed as position name (business group, instance code) for list-of-values and reporting labels.
• BUSINESS_GROUP — resolved from HR_ALL_ORGANIZATION_UNITS.NAME.
• ORGANIZATION_ID, JOB_ID, POSITION_ID, POSITION_DEFINITION_ID, BUSINESS_GROUP_ID — core identifiers enabling joins back to transactional HR tables.
• POSITION_FROM_DATE / POSITION_TO_DATE — date-effectivity bounds sourced from DATE_EFFECTIVE and DATE_END.
• PROBATION_PERIOD, REPLACEMENT_REQUIRED, TIME_NORMAL_START, TIME_NORMAL_FINISH, WORKING_HOURS — position attributes carried through from PER_ALL_POSITIONS.
• WORKING_HOUR_FREQ — decoded from the FREQUENCY lookup via HR_GENERAL.DECODE_LOOKUP.
• LAST_UPDATE_DATE / CREATION_DATE — the greatest (most recent) update timestamp across POS, BGR, and POD, with a floor value of 01-JAN-2000, used to drive incremental extract logic.

Common Use Cases and Queries

Typical uses include position list-of-values generation, incremental HR extracts into the EDW, and dimensional population for HR Intelligence Discoverer workbooks.

Example: list active positions for a business group.

SELECT position_pk, name, business_group,
       position_from_date, position_to_date
FROM   apps.edwbv_hr_pstn_position_lcv
WHERE  business_group = :p_business_group
AND    NVL(position_to_date, SYSDATE+1) > SYSDATE;

Example: incremental delta pull using the derived timestamp.

SELECT position_id, position_definition_id, organization_id,
       last_update_date
FROM   apps.edwbv_hr_pstn_position_lcv
WHERE  last_update_date >= :p_last_run_date;

Example: resolve a display name for a known position key.

SELECT position_dp
FROM   apps.edwbv_hr_pstn_position_lcv
WHERE  position_pk = '12345-PROD';

Because the view is read-only and derived, performance depends entirely on the indexed foreign-key joins on PER_ALL_POSITIONS, PER_POSITION_DEFINITIONS, and HR_ALL_ORGANIZATION_UNITS; restricting by BUSINESS_GROUP_ID or date ranges is recommended for large deployments.