Search Results igs_ps_nsus_rtn_dtl




Overview
Explore in AI Chat

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

Overview

IGS_PS_NSUS_RTN_DTL is a transactional detail table within the Oracle E-Business Suite Student System (IGS) product family, owned by the IGS schema. As documented in the ETRM repository for release 12.1.1, the table stores information pertaining to Retention master setup, functioning as a child of the retention header entity. The table carries a VALID status in the data dictionary and contains twelve documented columns.

In Data Vault modeling terms, the mined relationship structure classifies this object as satellite-leaning. This classification is a heuristic suggestion derived from the foreign key topology: the table holds a single foreign key to IGS_PS_NSUS_RTN and does not itself act as a junction between multiple hubs. Consequently, it is best understood as a descriptive satellite that records the retention rules — percentages, amounts, and offset dates — defined beneath a parent retention definition.

Key Information Stored

The physical schema exposes twelve columns, of which the following are most significant for functional and reporting purposes:

The distinction between the surrogate PK (NON_STD_USEC_RTN_DTL_ID) and the business-key candidates defined by the two unique indexes is important: integration and data-load routines should resolve the natural key of parent plus offset rather than assuming the surrogate identifier is portable across environments.

Common Use Cases and Queries

The table is typically queried in retention configuration reporting, student retention exposure analysis, and interfaces that migrate retention master data between environments. A common pattern joins details to their parent to present a complete retention definition:

  • Listing all retention rules for a given parent: SELECT d.offset_value, d.retention_percent, d.retention_amount, d.offset_date FROM igs_ps_nsus_rtn_dtl d WHERE d.non_std_usec_rtn_id = :p_rtn_id ORDER BY d.offset_value;
  • Validating configuration completeness by counting detail rows per parent and flagging parents with no details.
  • Auditing changes using the standard WHO columns, filtering on last_update_date for incremental loads.
  • Detecting duplicate business keys, which should be impossible given IGS_PS_NSUS_RTN_DTL_U1 but is nonetheless a frequent reconciliation check after direct data loads.

Related Objects

The principal related objects are:

  • IGS_PS_NSUS_RTN — the parent retention header table, joined on NON_STD_USEC_RTN_ID = IGS_PS_NSUS_RTN.NON_STD_USEC_RTN_ID; this is the only documented foreign key relationship.
  • IGS_PS_NSUS_RTN_DTL_PK — primary key constraint on NON_STD_USEC_RTN_DTL_ID.
  • IGS_PS_NSUS_RTN_DTL_U1 — unique index on (NON_STD_USEC_RTN_ID, OFFSET_VALUE), defining the business key.
  • Retention setup concurrent programs, validation APIs, and Student System reporting views that consume retention definitions through the parent table, since the detail rows are only meaningful in the context of their header.