Results for “igs_ps_occup_titles”

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

Overview

IGS_PS_OCCUP_TITLES is a multi-organization secured view in the Oracle E-Business Suite Student System (IGS) product family, owned by the APPS schema and delivered with a status of VALID in both 12.1.1 and 12.2.2. The view exposes program occupational title assignments, allowing institutions to associate occupational title codes with academic program codes. In the Oracle Student System model, occupational titles support reporting and compliance requirements such as gainful employment, vocational outcome tracking, and workforce alignment reporting, where a program's graduates map to specific standard occupational classifications.

The view's principal role in EBS reporting and integration is to provide a secured, single-organization projection of the underlying entity table. Rather than granting direct access to the _ALL table, the view applies an ORG_ID predicate derived from the session's CLIENT_INFO, so that queries executed from a given operating unit return only records belonging to that organization. Report definitions, concurrent programs, and third-party integrations that need program-to-occupation mappings normally read from this view rather than the base table, ensuring organizational data segregation.

Underlying Base Objects

The view is defined over a single documented base object: IGS_PS_OCCUP_TITLES_ALL. The view text is a straightforward column projection with a WHERE clause that filters on ORG_ID. The selection logic compares the ORG_ID of each row against the organization identifier parsed from USERENV('CLIENT_INFO'). The DECODE/SUBSTRB construct reads the first ten characters of CLIENT_INFO, treating a leading blank as NULL, and coerces the result to a number. Rows whose ORG_ID matches that value — or whose ORG_ID is NULL, falling back to a sentinel of -99 — are returned.

No additional joins, lookups, or aggregations are present in the view definition, meaning all descriptive attributes such as the occupational title description and program name reside in separate IGS tables that must be joined explicitly by the consumer. The view is therefore a thin security wrapper rather than a denormalized reporting structure.

Key Columns

• ROW_ID — the ROWID of the underlying _ALL row, useful for identifying and updating specific records.
• PROGRAM_OCCUPATIONAL_TITLE_ID — the surrogate primary key identifying the association between a program and an occupational title.
• PROGRAM_CODE — the code of the academic program to which the occupational title is attached.
• OCCUPATIONAL_TITLE_CODE — the code of the occupational title associated with the program.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS WHO columns recording audit and concurrency information.
• ORG_ID — the operating unit identifier that drives the view's multi-organization security filter.

Common Use Cases and Queries

Typical uses include validating that every active program has at least one occupational title defined, extracting program-to-occupation mappings for regulatory reporting, and reconciling records loaded through interface or conversion programs. Because the view is organization-secured, queries must be executed in a session whose CLIENT_INFO is initialized (for example, through the standard FND initialization used by concurrent programs and forms).

A representative query listing mappings for the current organization is:

• SELECT program_code, occupational_title_code, org_id FROM apps.igs_ps_occup_titles ORDER BY program_code;

To find programs lacking an occupational title, a not-exists pattern can be used:

• SELECT p.program_code FROM apps.igs_ps_programs p WHERE NOT EXISTS (SELECT 1 FROM apps.igs_ps_occup_titles o WHERE o.program_code = p.program_code);

For auditing recently changed associations:

• SELECT program_code, occupational_title_code, last_updated_by, last_update_date FROM apps.igs_ps_occup_titles WHERE last_update_date >= TRUNC(SYSDATE) - 30;

When organization-wide analysis is required across operating units, the underlying IGS_PS_OCCUP_TITLES_ALL table should be queried directly, subject to appropriate security review.