Results for “staffed_asgmt_system_status”

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

Overview

APPS.PA_RES_LEG_DET_V is a lightweight Oracle EBS view that resolves the lookup meaning associated with a project's staffed-assignment system status. It joins the lookup table PA_LOOKUPS to the project status table PA_PROJECT_STATUSES and exposes two columns: the lookup meaning (LUP.MEANING) and the project status name (ST.PROJECT_STATUS_NAME). The view is documented in ETRM 12.2.2 and behaves identically in 12.1.1.

The view derives its importance from the fact that "staffed_asgmt_system_status" is stored as a system status code on the project record, not as descriptive text. This view supplies the human-readable meaning, which is exactly what the reporting or integration layer requires. Note that the view is restricted by both a LOOKUP_TYPE = 'STAFFED_ASGMT_SYSTEM_STATUS' predicate and a PROJECT_SYSTEM_STATUS_CODE IN (...) predicate limited to STAFFED_ASGMT_CONF and STAFFED_ASGMT_PROV. It is therefore a "detail" (LEG_DET) view: it returns only those statuses that are legal in the context of staffed assignments, rather than the full status set.

Underlying Base Objects

The view references two documented base objects: PA_LOOKUPS (VIEW) and PA_PROJECT_STATUSES (SYNONYM).

• PA_LOOKUPS — the source of the seeded lookup values. The join predicate requires the lookup code to match the project's system status code and restricts LOOKUP_TYPE to STAFFED_ASGMT_SYSTEM_STATUS.
• PA_PROJECT_STATUSES — the project status repository, supplying the system status code and the descriptive status name.

Because both sides are keyed on the status code, the view is essentially a lookup-translation layer. The ORDER BY clause uses a DECODE mapping (STAFFED_ASGMT_CONF=1, STAFFED_ASGMT_PROV=2, OPEN_ASGMT=3, CONFIRMED_ADMIN=4, AVAILABLE=5, otherwise 6), which imposes a deterministic business ordering on the results, even though only the first two values are actually returned, making the ordering functionally a fixed sequence of two rows.

Key Columns

• MEANING — the descriptive text attached to the staffed assignment system status in PA_LOOKUPS, e.g., the display meaning for Confirmed or Provisional.
• PROJECT_STATUS_NAME — the status name from PA_PROJECT_STATUSES associated with the same system status code.

Both columns are character values sourced through the join. The view exposes no IDs, project numbers, or other foreign keys; consumers join it back to context as needed.

Common Use Cases and Queries

The view is commonly used to populate LOVs, display labels, or translation tables for staffed-assignment status codes in custom reports, OAF pages, and integration mappings.

SELECT meaning, project_status_name
FROM   apps.pa_res_leg_det_v;

A frequent pattern is to join it to a project query to resolve the status label:

SELECT p.project_number,
       p.project_system_status_code,
       v.meaning
FROM   pa_projects_all       p,
       apps.pa_res_leg_det_v v
WHERE  p.project_system_status_code = v.meaning
       -- or, more reliably, join on the code
;

Because the view returns exactly two rows, it is typically dereferenced for display rather than filtered. Verify the current lookup meanings in PA_LOOKUPS, as meanings are language-dependent and returned in the session language. Treat the view as read-only metadata for reporting and do not update it directly.