Search Results assignment_pk

Overview

EDW_HR_ASGN_ASSGNMNT_LCV is a public view owned by the APPS schema within the HRI (Human Resources Intelligence) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. The suffix LCV denotes a "logical column view" — a column-normalized presentation layer used by the Oracle HR Intelligence (Daily Business Intelligence / EDW) reporting stack rather than by transactional Oracle HRMS forms.

The view exposes a flattened, denormalized projection of employee assignment data. It provides a stable, descriptive reporting interface over one or more base HR tables, shielding downstream extract, transform, and load (ETL) processes from the underlying joins and code lookups. The leading /* $HEADER: HRIVIEW.LDT 115.3 2001/09/18 10:51:35 PKM SHIP $ */ comment in the view text confirms this is a shipped HRI view definition maintained since the early 11i releases and carried forward into R12.

Because the object name contains "ASGN_ASSGNMNT," the view centers on the HR assignment entity — the record of a person's placement into a position, organization, and payroll within a business group. It is oriented toward workforce analytics and star-schema style reporting, not toward transactional maintenance.

Underlying Base Objects

The documented view text is defined over a single base object:

• EDWBV_HR_ASGN_ASSGNMNT_LCV — the "BV" (base view) layer object from which this LCV selects.

No further base tables are documented in the ETRM metadata. In practice, the EDW BV layer resolves down through HRI's internal EDW_* and EDW_HR_* structures to the HRMS tables PER_ALL_ASSIGNMENTS_F (the datetracked assignment entity), PER_ALL_PEOPLE_F, HR_ALL_ORGANIZATION_UNITS, and the assignment status/type lookup tables. The LCV adds no joins of its own; it performs a straight column projection plus five trailing NULL literals, which act as positional placeholders for user-attribute columns (USER_ATTRIBUTE1–USER_ATTRIBUTE5) that the LCV physically exposes but does not populate.

Key Columns

• ASSIGNMENT_PK — the surrogate primary key for the assignment row, the most commonly referenced column and the one underlying the search for "assignment_pk". It is the join key for all downstream EDW fact and dimension tables.
• ALL_FK — a foreign key back to the PER_ALL_ASSIGNMENTS_F transactional row, bridging the EDW layer to the HRMS operational entity.
• ASSIGNMENT_ID — the native HRMS assignment identifier; distinct from the surrogate ASSIGNMENT_PK.
• NAME / ASSIGNMENT_NUMBER / ASSIGNMENT_DP — descriptive assignment label, the human-readable assignment number, and its display format.
• BUSINESS_GROUP / BUSINESS_GROUP_ID / PEOPLE_GROUP_ID — the organizational context of the assignment.
• START_DATE / END_DATE / EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — datetrack boundaries of the assignment record.
• PRIMARY_FLAG — indicates whether this is the employee's primary assignment.
• ASSIGNMENT_STATUS / ASSIGNMENT_TYPE / ASSIGNMENT_STATUS_TYPE_ID — coded status and type descriptors (e.g., Active, Terminated, Applicant).
• TITLE / MANAGER_FLAG — job title and managerial indicator.
• NORMAL_HOURS / NORMAL_HOURS_FREQUENCY / TIME_NORMAL_START / TIME_NORMAL_END — working-hours attributes.
• PROBATION_PERIOD / PERF_REVIEW_PERIOD / SLRY_REVIEW_PERIOD (with associated frequency and unit columns) — review and probation cycle metrics.
• HOURLY_SALARIED_FLAG / INSTANCE — pay-basis and source-instance indicators.
• CREATION_DATE / LAST_UPDATE_DATE — audit columns supporting incremental ETL extraction.

Common Use Cases and Queries

Typical usage includes incremental extraction into an EDW staging table (filtered on LAST_UPDATE_DATE), population of the assignment dimension in a workforce star schema, and ad-hoc reconciliation between EDW aggregates and the HRMS base table.

Retrieve a single assignment by surrogate key:

SELECT assignment_pk, all_fk, assignment_id, name,
       assignment_number, start_date, end_date,
       primary_flag, assignment_status
  FROM apps.edw_hr_asgn_assgnmnt_lcv
 WHERE assignment_pk = :p_assignment_pk;

Incremental extraction since a watermark:

SELECT assignment_pk, all_fk, assignment_id,
       business_group_id, effective_start_date,
       effective_end_date, last_update_date
  FROM apps.edw_hr_asgn_assgnmnt_lcv
 WHERE last_update_date >= :p_last_run
 ORDER BY last_update_date;

Reconcile an EDW key back to the transactional assignment:

SELECT l.assignment_pk, a.assignment_id, a.assignment_number,
       a.primary_flag, a.assignment_status_type_id
  FROM apps.edw_hr_asgn_assgnmnt_lcv l,
       apps.per_all_assignments_f a
 WHERE l.all_fk = a.assignment_id
   AND a.effective_start_date = l.effective_start_date;

Because the LCV is a read-only reporting object, queries against it should always be schema-qualified as APPS.EDW_HR_ASGN_ASSGNMNT_LCV and restricted by business group or effective date to avoid datetrack fan-out.