Results for “status_code”

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

Overview

PA_REP_ASMT_SCH_STATUS_V is an Oracle Projects (PA) reporting view owned by the APPS schema. Its stated purpose in the ETRM documentation is to show all schedule statuses for existing project assignments. In the context of Oracle EBS 12.1.1 and 12.2.2, the view functions as a lightweight, denormalized lookup that consolidates assignment status values into a two-column result set of status code and status name. It abstracts the join logic required to translate assignment status codes into human-readable meanings, which makes it a convenient source for reports, concurrent programs, and integrations that need to populate status selection lists or resolve assignment schedule statuses without re-implementing that logic. Notably, the view text explicitly filters out records where ASSIGNMENT_TYPE equals 'OPEN_ASSIGNMENT', which directly answers the "open_assignment" search term: open assignments are a distinct assignment classification and are deliberately excluded from the output of this view.

Underlying Base Objects

The view is defined over three documented referenced base objects: PA_PROJECT_ASSIGNMENTS (referenced as a synonym), PA_PROJECT_STATUSES (referenced as a synonym), and PA_LOOKUPS (referenced as a view).

• PA_PROJECT_ASSIGNMENTS — the transactional table holding project assignment records. The view reads ASMT.STATUS_CODE, ASMT.ASSIGNMENT_TYPE, and ASMT.MULTIPLE_STATUS_FLAG from this table.
• PA_PROJECT_STATUSES — supplies the project status code and name pairing via PS.PROJECT_STATUS_CODE and PS.PROJECT_STATUS_NAME.
• PA_LOOKUPS — supplies the lookup meaning for the multi-status case, filtered on LOOKUP_TYPE = 'MULTIPLE_STATUS_TEXT' and LOOKUP_CODE = 'MULTIPLE'.

The view text is built as a UNION ALL of two branches. The first branch selects status code and name from PA_PROJECT_STATUSES for any status code that exists on an assignment whose ASSIGNMENT_TYPE is not 'OPEN_ASSIGNMENT', constrained by ROWNUM = 1. The second branch selects the assignment status code joined to the PA_LOOKUPS meaning for assignments flagged MULTIPLE_STATUS_FLAG = 'Y', again excluding 'OPEN_ASSIGNMENT' and limited by ROWNUM = 1.

Key Columns

• STATUS_CODE — the assignment schedule status code. In the first UNION branch this derives from PA_PROJECT_STATUSES.PROJECT_STATUS_CODE; in the second branch it derives from PA_PROJECT_ASSIGNMENTS.STATUS_CODE.
• STATUS_NAME — the descriptive status name. In the first branch this is PA_PROJECT_STATUSES.PROJECT_STATUS_NAME; in the second branch it is PA_LOOKUPS.MEANING for the 'MULTIPLE' lookup code.

The two-column shape makes the view suitable for value-set style consumption where a code and its display name are both required.

Common Use Cases and Queries

Typical scenarios include populating status list-of-values queries, building assignment schedule status reports, and validating status codes before update operations. The exclusion of 'OPEN_ASSIGNMENT' means the view is appropriate when only concrete, scheduled assignments are of interest.

• Retrieving all available schedule statuses:

  SELECT status_code, status_name
  FROM   apps.pa_rep_asmt_sch_status_v;

• Resolving a specific status name:

  SELECT status_name
  FROM   apps.pa_rep_asmt_sch_status_v
  WHERE  status_code = :p_status_code;

• Confirming which assignments drive the status list, while explicitly noting that open assignments are omitted:

  SELECT DISTINCT asmt.status_code, asmt.assignment_type
  FROM   apps.pa_project_assignments asmt
  WHERE  asmt.assignment_type <> 'OPEN_ASSIGNMENT';

Because both UNION branches include ROWNUM = 1, the view is not intended to return one row per assignment; it returns a representative status code/name set rather than a full assignment listing. Queries requiring assignment-level granularity should read PA_PROJECT_ASSIGNMENTS directly.