Search Results igf_sl_clchrs_dtls




Overview
Explore in AI Chat

Overview

The IGF_SL_CLCHRS_DTLS table resides in the IGF schema and is a component of the Oracle E-Business Suite Financial Aid module (IGF). It is documented in ETRM as the CommonLine Change Send Response Table, and its role is to store responses returned for CommonLine change transactions submitted to student loan servicers and guarantors. In the CommonLine (Common Account Maintenance) processing cycle, change requests are transmitted to trading partners, and the responses — success or error notifications — are captured here for reconciliation, audit, and downstream exception handling.

The table is defined in both Oracle EBS 12.1.1 and 12.2.2 with an identical 19-column physical schema under the IGF owner. Its primary key, IGF_SL_CLCHRS_DTLS_PK, is enforced on the surrogate column CLCHGRSP_ID. A single foreign key relationship exists: CLRP1_ID references IGF_SL_CL_RESP_R1_ALL, linking each detail response back to its parent CommonLine response record.

Based on the mined foreign-key structure, the heuristic Data Vault classification for this object is satellite-leaning. In other words, it behaves as a descriptive satellite attached to the parent response entity (IGF_SL_CL_RESP_R1_ALL), capturing the variable, non-key attributes of each change response. This classification should be treated as a modeling suggestion rather than a mandated design pattern.

Key Information Stored

The following are the most significant columns among the 19 documented:

  • CLCHGRSP_ID — Surrogate primary key; uniquely identifies each change send response detail row.
  • CLRP1_ID — Foreign key to IGF_SL_CL_RESP_R1_ALL; establishes the parent-child linkage for the response.
  • RECORD_CODE — Identifies the CommonLine record type (e.g., change transaction record) associated with the response.
  • SEND_RECORD_TXT — The raw text of the sent record, preserved for transmission audit and troubleshooting.
  • ERROR_MESSAGE_1_CODE through ERROR_MESSAGE_5_CODE — Up to five servicer- or guarantor-returned error codes, providing diagnostic detail when a change request is rejected or partially accepted.
  • RECORD_PROCESS_CODE — Indicates the processing outcome of the record (e.g., accepted, rejected, warning).
  • REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID — Standard concurrent program context columns, enabling traceability to the process that created the row.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard WHO/audit columns tracking user and timestamp of insert and update.
  • PROGRAM_UPDATE_DATE — Records the date the originating concurrent program last touched the row.

The only documented unique index is the primary key on CLCHGRSP_ID; no separate business-key unique index is documented. The business-key candidate for this table remains the surrogate primary key plus the parent foreign key combination.

Common Use Cases and Queries

Typical usage centers on reconciliation of change transaction responses and identification of failed transmissions. A common reporting query joins the detail table to its parent response table to summarise outcomes:

  • Error analysis — extract rows where ERROR_MESSAGE_1_CODE IS NOT NULL or RECORD_PROCESS_CODE indicates rejection, grouped by error code for trend reporting.
  • Audit trail — retrieve SEND_RECORD_TXT alongside WHO columns to reconstruct exactly what was transmitted and when.
  • Concurrent program traceability — filter on REQUEST_ID or PROGRAM_ID to isolate responses produced by a specific CommonLine change send run.

A representative join pattern uses IGF_SL_CLCHRS_DTLS.CLRP1_ID = IGF_SL_CL_RESP_R1_ALL.CLRP1_ID to associate each detail response with its parent header, then filters on processing status or error codes to surface exceptions requiring corrective action.

Related Objects

  • IGF_SL_CL_RESP_R1_ALL — The sole documented parent table; joined via IGF_SL_CLCHRS_DTLS.CLRP1_ID = IGF_SL_CL_RESP_R1_ALL.CLRP1_ID. This is the primary relationship to understand.
  • IGF_SL_CLCHRS_DTLS_PK — The unique index (CLCHGRSP_ID) enforcing row identity.
  • CommonLine processing interfaces and change send concurrent programs within the IGF module, which populate this table through REQUEST_ID and PROGRAM_ID context columns.
  • Student loan application and disbursement tables within the IGF Financial Aid schema that reference the parent response record chain.
  • Reporting views and DBI (data extraction) objects consuming change response data for financial aid reconciliation.

Because the documented metadata is limited to the primary key and single foreign key, further dependency analysis should be performed against the live 12.1.1/12.2.2 data dictionary using ALL_DEPENDENCIES and ALL_CONSTRAINTS for the IGF schema.