Results for “non_std_usec_rtn_dtl_id”

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:

• NON_STD_USEC_RTN_DTL_ID — the surrogate primary key of the table, enforced by the IGS_PS_NSUS_RTN_DTL_PK constraint.
• NON_STD_USEC_RTN_ID — the foreign key to IGS_PS_NSUS_RTN, linking each detail row to its parent retention definition. This column also participates in the business-key unique index.
• OFFSET_VALUE — the offset quantity associated with the retention rule; together with NON_STD_USEC_RTN_ID it forms the second unique index, IGS_PS_NSUS_RTN_DTL_U1, which guarantees a single rule per parent retention and offset combination.
• RETENTION_PERCENT — the percentage of the base amount retained at the applicable offset points.
• RETENTION_AMOUNT — the absolute monetary value retained under the rule.
• OFFSET_DATE — the date from which the offset is calculated.
• OVERRIDE_DATE_FLAG — indicates whether the calculated offset date may be overridden.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard Oracle EBS audit columns capturing row provenance and change history.

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.