Results for “igf_sl_clchsn_dtls”

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

Overview

IGF_SL_CLCHSN_DTLS is a Financial Aid (IGF) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores change history records associated with student loan records transmitted under the CommonLine Release 4 specification. CommonLine is an industry data-exchange standard used by lenders, guarantors, schools, and servicers to exchange loan origination, disbursement, and change transaction data electronically. This table captures the individual change transactions (change records) submitted against an existing loan, preserving both the prior and revised values so that the full lifecycle of loan amendments remains auditable.

The table resides in the IGF schema and contains 47 documented columns. Its primary key is IGF_SL_CLCHSN_DTLS_PK, defined on the CLCHGSND_ID column — the identifier referenced in the search term "clchgsnd_id." Under a heuristic Data Vault classification mined from the foreign-key structure, this table is assessed as satellite-leaning. In Data Vault modeling terms, a satellite stores descriptive, time-variant attributes that describe a parent hub or link entity. That characterization is consistent with the table's function here: it holds the descriptive change detail and before/after values that qualify a loan entity (IGF_SL_LOANS_ALL) and an award entity (IGF_AW_AWARD_ALL), rather than acting as an independent hub or a pure association link.

Key Information Stored

The most significant columns fall into three groups:

• Identity and relationship keys: CLCHGSND_ID (surrogate primary key), AWARD_ID (foreign key to IGF_AW_AWARD_ALL), and LOAN_NUMBER_TXT (foreign key to IGF_SL_LOANS_ALL). The only documented unique index is the primary key itself; no separate business-key unique index is published in the metadata.
• Change characterization: CL_VERSION_CODE, CHANGE_FIELD_CODE, CHANGE_RECORD_TYPE_TXT, CHANGE_CODE_TXT, CHANGE_ISSUE_CODE, and STATUS_CODE with STATUS_DATE and RESPONSE_STATUS_CODE.
• Before/after value pairs: OLD_VALUE_TXT and NEW_VALUE_TXT; OLD_DATE and NEW_DATE; OLD_AMT and NEW_AMT — the core of the audit trail.
• Disbursement detail: DISBURSEMENT_NUMBER, DISBURSEMENT_DATE, DISBURSEMENT_CANCEL_DATE, DISBURSEMENT_CANCEL_AMT, DISBURSEMENT_REVISED_AMT, DISBURSEMENT_REVISED_DATE, DISBURSEMENT_REISSUE_CODE, DISBURSEMENT_REINST_CODE, and the return amount/date/code triplets (DISBURSEMENT_RETURN_*, POST_WITH_DISB_RETURN_*, PREV_WITH_DISB_RETURN_*).
• Party-specific notes and transmission: SCHOOL_USE_TXT, LENDER_USE_TXT, GUARANTOR_USE_TXT, VALIDATION_EDIT_TXT, SEND_RECORD_TXT.
• Standard EBS audit and concurrent columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE.

Common Use Cases and Queries

Typical scenarios include auditing a specific loan's change history, reconciling disbursement adjustments, and reporting on change responses from CommonLine trading partners.

• Loan change audit: SELECT clchgsnd_id, change_field_code, old_value_txt, new_value_txt, creation_date FROM igf.igf_sl_clchsn_dtls WHERE loan_number_txt = :p_loan ORDER BY creation_date;
• Award-level change review: filter by AWARD_ID and CL_VERSION_CODE to isolate changes for a particular award and release version.
• Disbursement impact analysis: query rows where DISBURSEMENT_REVISED_AMT or DISBURSEMENT_CANCEL_AMT is populated, joining to the loan master for the borrower context.
• Status/response tracking: group by STATUS_CODE and RESPONSE_STATUS_CODE for operational dashboards, using STATUS_DATE for aging.
• Concurrent program traceability: filter on REQUEST_ID to reproduce the set of records touched by a specific batch run.

Related Objects

The metadata documents two foreign-key relationships and the primary key linkage:

• IGF_SL_LOANS_ALL — joined on IGF_SL_CLCHSN_DTLS.LOAN_NUMBER_TXT = IGF_SL_LOANS_ALL.LOAN_NUMBER_TXT. The parent loan entity that each change record qualifies.
• IGF_AW_AWARD_ALL — joined on IGF_SL_CLCHSN_DTLS.AWARD_ID = IGF_AW_AWARD_ALL.AWARD_ID. The financial aid award associated with the loan.
• IGF_SL_CLCHSN_DTLS_PK — the primary key on CLCHGSND_ID that uniquely identifies each change record and supports child or dependent references.
• CommonLine processing and Outbound/Inbound (e.g., SEND_RECORD_TXT-driven flows) and concurrent manager programs — referenced indirectly through REQUEST_ID, PROGRAM_ID, and PROGRAM_APPLICATION_ID, which link the rows to the concurrent requests and application definitions that generated them.
• Student loan disbursement and CommonLine change/response interfaces — rely on change codes and disbursement columns maintained here to build outbound CommonLine Release 4 transactions and to reconcile inbound responses.