Results for “job_title”

50+ results




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

Overview

BIL_DIMV_JOB_TITLE is a dimensional view belonging to the Sales Intelligence (BIL) product family within Oracle E-Business Suite. It is a dimension view used in the BIL star schema, where the "DIMV" prefix indicates a dimension view that conforms to ETRM's dimensional modeling standards. The object supplies the job title dimension used by Sales Intelligence analytics, presenting a consolidated, de-duplicated list of sales representative job titles annotated with surrogate key values. The view is documented in ETRM 12.2.2 and is classified as obsolete; the accompanying implementation note states that the object is "not implemented in this database," indicating it is a legacy artifact retained for documentation purposes only.

Its principal role is to expose a JOB_PK surrogate key paired with a descriptive JOB_TITLE, allowing reporting layers, Discoverer workbooks, and downstream BI tooling to group, filter, and drill sales performance facts by job title without querying transactional assignment tables directly.

Underlying Base Objects

The view is defined as a UNION ALL over two sources.

  • PER_ALL_ASSIGNMENTS_F (PAAF) — the primary source. The view selects DISTINCT JOB_ID, TITLE, TO_CHAR(JOB_ID) as ID, and TITLE as VALUE, restricted to rows where TITLE is not null and PRIMARY_FLAG = 'Y'. This ensures only the primary assignment for each employee contributes a job title.
  • FND_LOOKUPS (FL) — the secondary source, contributing a single sentinel row. It selects TO_NUMBER(LOOKUP_CODE) as JOB_PK, SUBSTR(MEANING,1,30) as JOB_TITLE, LOOKUP_CODE as ID, and MEANING as VALUE, filtered by LOOKUP_TYPE = 'BIL_VALUE_TYPE' and LOOKUP_CODE = '-999'. This provides the standard "unknown/unassigned" dimension member required by the Sales Intelligence star schema.

The view text terminates with a WITH READ ONLY clause, preventing DML against the view. ETRM documents no additional referenced base objects.

Key Columns

  • JOB_PK — the surrogate key. Derived from PAAF.JOB_ID on the PER_ALL_ASSIGNMENTS_F branch and cast from the lookup code on the FND_LOOKUPS branch. It is the column referenced by the user search term "job_pk" and is the join key to Sales Intelligence fact tables.
  • JOB_TITLE — the descriptive label. Sourced from PAAF.TITLE; on the FND_LOOKUPS branch it is truncated to 30 characters via SUBSTR, producing the "unknown" member label.
  • ID — the string representation of the key (TO_CHAR of the job ID, or the raw lookup code). Used where a character-form identifier is required, such as in LOV or flexfield-style displays.
  • VALUE — the display value, aligned with ID and sourced from TITLE or MEANING respectively. This pairing supports standard BIL dimension interface conventions where ID and VALUE are exposed together.

Common Use Cases and Queries

Typical use is joining JOB_PK to a Sales Intelligence fact table to slice revenue or opportunity metrics by job title, or using the view as a Discoverer/BI dimension list of values. Because the object is obsolete and unimplemented, most queries are of an investigative nature — confirming historical metadata or reconciling a legacy ETRM report definition against the current data model.

  • Enumerate the available job title dimension members: SELECT job_pk, job_title FROM bil_dimv_job_title ORDER BY job_title;
  • Confirm the sentinel member exists: SELECT job_pk, job_title FROM bil_dimv_job_title WHERE job_pk = -999;
  • Join to a fact table for title-level analysis: SELECT d.job_title, SUM(f.amount) FROM bil_fact f, bil_dimv_job_title d WHERE f.job_pk = d.job_pk GROUP BY d.job_title;
  • Verify overlap between assignment titles and primary-flag filtering by comparing against PER_ALL_ASSIGNMENTS_F directly.

Because the view is documented as not implemented, these statements should be validated against the target instance before use.