Results for “s_adv_stnd_granting_status”

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

Overview

IGS_AV_STND_UNIT_LVL is a reporting view within the Oracle E-Business Suite Student System (IGS) product family. It presents data relating to advanced standing granted at the unit level, a core function of student records management where students receive recognition for prior learning, external qualifications, or equivalent study toward a unit of a program. The view is owned by the APPS schema and holds a VALID status in the ETRM data dictionary for both 12.1.1 and 12.2.2.

The view is a multi-organization (multi-org) secured view. Its role is to provide a filtered, reporting-friendly projection of the underlying advanced standing unit-level data, restricted by the operating unit (ORG_ID) context of the querying session. Because it abstracts the ORG_ID filter through the USERENV client information, it is well suited to concurrent program reports, Oracle Reports outputs, OBIEE or XML Publisher extracts, and integration interfaces that must respect the operating unit security model.

It should be distinguished from the "_ALL" tables and views in the IGS schema. The "_ALL" objects retain rows across all operating units, whereas this view exposes only the rows matching the current session's operating unit. This design is a standard Oracle multi-org pattern and is why the view is prefixed as a "V" style reporting layer over its base.

Underlying Base Objects

According to the documented view text, IGS_AV_STND_UNIT_LVL is defined exclusively over a single base object: IGS_AV_STND_UNIT_LVL_ALL. No other base tables, synonyms, or views are referenced in the documented definition.

The base table IGS_AV_STND_UNIT_LVL_ALL stores the advanced standing unit-level records together with the ORG_ID column required for multi-org partitioning. The view applies the standard multi-org filter by comparing the ORG_ID value against the operating unit derived from the USERENV('CLIENT_INFO') context:

• When the first character of CLIENT_INFO is a space, the operating unit is treated as NULL.
• Otherwise the first ten characters are interpreted as the operating unit identifier.
• Rows are returned where ORG_ID matches this derived value, defaulting to -99 when no valid operating unit is determined.

The view also exposes the base table's ROWID as ROW_ID, enabling downstream processing, DML through INSTEAD OF triggers if configured, or identification of individual physical rows.

Key Columns

The view exposes a comprehensive set of columns spanning advanced standing attributes, audit fields, and foreign key references. Notable columns include:

• AV_STND_UNIT_LVL_ID — The primary surrogate identifier for the advanced standing unit-level record; unique within the base table.
• PERSON_ID — The student (person) to whom the advanced standing applies.
• AS_COURSE_CD and AS_VERSION_NUMBER — The course and version against which advanced standing is recorded.
• S_ADV_STND_TYPE — The type or category of advanced standing granted.
• S_ADV_STND_GRANTING_STATUS — The status of the advanced standing grant (for example, approved, pending, rejected).
• UNIT_LEVEL and UNIT_LEVEL_MARK — The academic level of the unit and its associated mark.
• CREDIT_POINTS — Credit value awarded for the advanced standing.
• EXEMPTION_INSTITUTION_CD — The institution from which the exemption or prior learning originates.
• APPROVED_DT, GRANTED_DT, EXPIRY_DT, CANCELLED_DT, REVOKED_DT — Lifecycle dates tracking the approval, grant, expiry, cancellation, and revocation of the standing.
• AUTHORISING_PERSON_ID — The person who authorised the grant.
• ORG_ID — The operating unit used for multi-org security filtering.
• CAL_TYPE, CI_SEQUENCE_NUMBER, INSTITUTION_CD, UNIT_DETAILS_ID, TST_RSLT_DTLS_ID, DEG_AUD_DETAIL_ID, QUAL_DETS_ID — Contextual references to calendar type, sequence, institution, unit details, test results, degree audit details, and qualification details.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard WHO audit columns.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrent request and program context columns populated when records are created through batch processes.

Common Use Cases and Queries

The view is typically used to report on advanced standing granted at unit level, to reconcile student unit exemptions, and to feed downstream systems such as student records, progression, or graduation audit interfaces. Because it is multi-org secured, concurrent programs that run within a given operating unit automatically see only the relevant rows.

• Listing advanced standing for a specific student and course:

SELECT avstnd.av_stnd_unit_lvl_id,
       avstnd.person_id,
       avstnd.as_course_cd,
       avstnd.as_version_number,
       avstnd.s_adv_stnd_type,
       avstnd.s_adv_stnd_granting_status,
       avstnd.unit_level,
       avstnd.credit_points,
       avstnd.granted_dt,
       avstnd.approved_dt,
       avstnd.expiry_dt
  FROM igs_av_stnd_unit_lvl avstnd
 WHERE avstnd.person_id = :p_person_id
   AND avstnd.as_course_cd = :p_course_cd
   AND avstnd.as_version_number = :p_version_number
 ORDER BY avstnd.unit_level;

• Identifying grants awaiting approval or nearing expiry:

SELECT avstnd.person_id,
       avstnd.as_course_cd,
       avstnd.s_adv_stnd_granting_status,
       avstnd.granted_dt,
       avstnd.expiry_dt
  FROM igs_av_stnd_unit_lvl avstnd
 WHERE avstnd.s_adv_stnd_granting_status = 'PENDING'
    OR (avstnd.expiry_dt IS NOT NULL
        AND avstnd.expiry_dt <= SYSDATE + 30);

• Aggregating credit points awarded by institution and unit level for a reporting period:

SELECT avstnd.exemption_institution_cd,
       avstnd.unit_level,
       SUM(avstnd.credit_points) total_credit_points,
       COUNT(*) total_grants
  FROM igs_av_stnd_unit_lvl avstnd
 WHERE avstnd.granted_dt BETWEEN :p_from_date AND :p_to_date
 GROUP BY avstnd.exemption_institution_cd,
          avstnd.unit_level;

These query patterns demonstrate the view's principal value: providing secure, operating-unit-scoped access to advanced standing unit-level data for reporting, reconciliation, and integration purposes within the IGS Student System.