Search Results assignment_number

Overview

HRIBV_ASGMVORGHRCHY is a view owned by the APPS schema in Oracle E-Business Suite, catalogued under the HRI (Human Resources Intelligence) product family. In the ETRM metadata for 12.1.1 and 12.2.2 this product is flagged as Obsolete, meaning the object is retained for backward compatibility with historical Oracle HRMS Intelligence and Enterprise Data Warehouse (EDW) extractions, but is not part of the current strategic reporting stack. The view presents assignment-level movement within an organization hierarchy — specifically the "Org Hierarchy Within" form of employee movement, where a person's assignment is compared against a prior or related assignment row to derive organizational gain, loss, and hierarchy transition attributes.

Its principal purpose is to flatten the relationship between assignment records, people, organizations, and organization structure versions into a single denormalized row suitable for downstream HR data warehouse loading. Each row carries the originating assignment, a comparison assignment, the source and target organization, and the effective-dated organization hierarchy version in force at that time.

Underlying Base Objects

The view is defined over the following documented objects:

• HRI_ORG_HRCHY_SUMMARY (synonym) — the driving summary table, supplying ORG_STRUCTURE_VERSION_ID, PARENT_ORG_ID, and the hierarchy summary context.
• PER_ORG_STRUCTURE_VERSIONS (synonym) — supplies VERSION_NUMBER, DATE_FROM, and DATE_TO for the organization hierarchy version. Note that the view aliases this object twice (OSV1, OSV2) to align the hierarchy version with both the assignment and its comparison row.
• PER_ORGANIZATION_STRUCTURES (synonym) — supplies ORGANIZATION_STRUCTURE_ID and the hierarchy name.
• PER_ASSIGNMENTS_F (view) — self-joined as ASG1 and ASG2, providing assignment number, effective dates, person, organization, and business group for both the primary and comparison assignments.
• HR_ALL_ORGANIZATION_UNITS_TL, PER_ALL_PEOPLE_F — joined to resolve organization names and the employee full name.
• HR_BIS (package) — called via BIS_DECODE_LOOKUP to return decoded movement, gain, and loss type values from the HRI_MOVE_TYPES, HRI_GAIN_TYPES, and HRI_LOSS_TYPES lookups.
• HR_GENERAL (package) — supplies END_OF_TIME for the NVL that guarantees a non-null hierarchy version end date.
• HR_SECURITY (package) — enforces organization security on the returned organization rows.

Key Columns

• MOVE_TYPE, GAIN_TYPE, LOSS_TYPE — decoded movement classifications, with fixed codes ORGHRCHY_WITHIN and NA_EDW.
• ORG_STRUCTURE_VERSION_ID / VERSION_NUMBER / HIERARCHY_VERSION_DATE_FROM / HIERARCHY_VERSION_DATE_TO_NN — the organization hierarchy version and its effective window; the _NN column is non-null via NVL(..., HR_GENERAL.END_OF_TIME).
• ORGANIZATION_STRUCTURE_ID, PARENT_ORG_ID, ORGANIZATION_HRCHY_NAME, TOP_ORGANIZATION_NAME — hierarchy definition context.
• ASSIGNMENT_ID, ASSIGNMENT_NUMBER, PERSON_ID, BUSINESS_GROUP_ID, EMPLOYEE_NAME — the assignment and person identity.
• FROM_ORGANIZATION_NAME, TO_ORGANIZATION_NAME, ORGANIZATION_ID_FROM, ORGANIZATION_ID_TO — the direction of the organizational transition.
• Effective date pairs (ASG_EFFECTIVE_START_DATE, OTHER_ASG_EFFECTIVE_START_DATE, etc.) — temporal keys for the source and comparison assignment rows.

Common Use Cases and Queries

Typical usage is in historical HR intelligence reporting — headcount movement by organization hierarchy, gain/loss analysis, and EDW staging extracts that predate the current OTBI/BICC subject areas.

SELECT organization_name,
       from_organization_name,
       to_organization_name,
       org_hierarchy_version,
       change_date
  FROM apps.hribv_asgmvorghrchy
 WHERE org_structure_version_id = :p_version_id
   AND change_date BETWEEN :p_from AND :p_to;

A second common pattern filters by assignment to trace a specific employee's organizational movement:

SELECT assignment_number, employee_name,
       organization_name, other_organization_name, change_date
  FROM apps.hribv_asgmvorghrchy
 WHERE assignment_id = :p_assignment_id;

Because the view applies HR_SECURITY, results are automatically restricted to the organizations the querying responsibility is permitted to see. Given its obsolete status, new development should target CONCURRENT/BI Publisher extracts against PER_ASSIGNMENTS_F and PER_ORG_STRUCTURE_VERSIONS directly, using this view only where existing legacy interfaces depend upon it.