Search Results lookup_code




Overview
Explore in AI Chat

Overview

APPS.EDWBV_HR_ASCH_ASG_CHNG_LCV is an Oracle E-Business Suite Business Intelligence (BI) Warehouse "Bulk View" (BV) that defines a level of the HR Assignment Changes (ASCH) dimensional hierarchy. It belongs to the ETRM (E-Business Tax and Reporting Model) family of objects and is registered under the APPS schema. The view materializes the lookup-code-driven change-flag combinations that describe how an assignment changed between two points in time — covering organization, job, position, grade, geography, and other change attributes as well as the resulting change status. Its central role is to supply deterministic dimension rows (a surrogate primary key plus display names) to downstream ETL processes and BI reports, so that assignment-change facts can be sliced by combinations of "Yes/No" lookup values. It is a conformed dimension source: the composite key it generates is referenced by related ETRM views and by the OLAP/BI Warehouse star schemas that load from it.

Underlying Base Objects

Although the documented metadata lists no base objects, the view text is explicit about its sources. Every column is generated from the HR_LOOKUPS table (the HR lookup values table, keyed by lookup_type). The definition joins seven inline subqueries, each aliased t1 through t7, and each of these selects rows from hr_lookups where lookup_type = 'YES_NO'. Each subquery projects a decoded value via hr_general.decode_lookup('YES_NO', lookup_code), the raw lookup_code, and (in the first alias only) creation_date and last_update_date. The cross-product of the seven "Yes/No" lookup codes produces the dimension's members. The view therefore depends on the HR lookup mechanism and the HR_GENERAL PL/SQL package for decoding, rather than on transactional assignment tables.

Key Columns

  • ASG_CHANGE_PK — Concatenation of the seven lookup codes plus instance_code, forming the level's dimension primary key (e.g. Y-Y-N-N-N-N-A-1).
  • NAME / ASG_CHANGE_DP — Human-readable dimension member name, the same concatenation of lookup codes with the instance code in parentheses; ASG_CHANGE_DP is the descriptive display label.
  • ORG_CHANGE_FLAG, JOB_CHANGE_FLAG, POS_CHANGE_FLAG, GRD_CHANGE_FLAG, GEOG_CHANGE_FLAG, OTHER_CHANGE_FLAG — Flag columns derived from t1–t6, each indicating whether that attribute changed in the assignment (decoded Yes/No).
  • CHANGE_STATUS — Derived from t7.lookup_code, classifying the overall change status.
  • INSTANCE — The instance_code identifying the source instance or operating unit context.
  • ALL_FK — Constant value 'ALL', used for shared/all-context foreign-key joins.
  • CREATION_DATE and NVL(LAST_UPDATE_DATE, 01-JAN-2000) — Standard BI Warehouse audit columns.
  • ASG_CHANGE_CODE — Exposed as NULL; reserved for a future or alternate change code attribute.

Common Use Cases and Queries

This view is typically consumed during the ETL load of the HR assignment-change dimension and in ad-hoc validation of lookup-driven change flags. A representative query listing the members is:

SELECT asg_change_pk, name, change_status, instance
FROM   apps.edwbv_hr_asch_asg_chng_lcv
ORDER  BY asg_change_pk;

To isolate members where an organization change occurred:

SELECT name, org_change_flag, job_change_flag, change_status
FROM   apps.edwbv_hr_asch_asg_chng_lcv
WHERE  org_change_flag = 'Yes';

Because the primary key is deterministic and derived entirely from lookup codes, the view is also used to reconcile fact rows against the dimension and to confirm that all expected Yes/No combinations of assignment changes are represented before report execution.