Results for “igf_sl_cl_change_fields”

9 results




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

Overview

APPS.IGF_SL_CLCHSN_DTLS_V is a reporting and integration view within the Oracle E-Business Suite Financial Aid subsystem, specifically the Student Loan (SL) module of the U.S. Department of Education's Title IV processing framework delivered through the Oracle Financial Aid (IGF) product family. The view exposes the detailed contents of change (CLCHSN) records that the institution generates and transmits to lenders, guarantors, and the Common Origination and Disbursement (COD) system. Each row represents a discrete change-record line associated with a loan version, capturing the field that changed, its prior and revised values, and the processing status of the change notification.

The view is primarily consumed by concurrent program extract processes, letter and correspondence generation, and operational reporting queries that must reconcile institution-side change activity against external acknowledgements. It is a runtime view rather than a stored table, so its contents always reflect the current state of the underlying detail table at the moment of query.

Underlying Base Objects

The view is defined over two documented base objects:

  • IGF_SL_CLCHSN_DTLS (aliased CLCHGSND) — the change-detail transaction table that holds one row per changed field or disbursement event, keyed by CLCHGSND_ID and linked to a loan version through CL_VERSION_CODE.
  • IGF_SL_CL_CHG_REC_TYPES (aliased CLCHRTS) — the change-record-type reference table, joined on CL_VERSION_CODE, supplying the human-readable change code description and record description text.

The view is a simple equi-join between these two tables. In addition, several columns are resolved at query time through calls to IGF_AW_GEN.LOOKUP_DESC, which translates stored lookup codes into their descriptive values using the lookup types IGF_SL_CL_CHG_ISSUE_CODES, IGF_SL_CL_CHANGE_FIELDS, IGF_SL_CL_CHG_REINST_CODES, IGF_SL_CL_CHG_REISSUE_CODES, IGF_SL_CL_DB_FUND_DISB_METH, IGF_SL_CL_CHG_RESP_STATUS, and IGF_SL_CL_CHG_REC_STATUS. Because these lookups are function-based, they are evaluated per row and are not indexed.

Key Columns

Common Use Cases and Queries

Typical usage includes identifying change records still awaiting transmission, tracking responses from external parties, and producing disbursement adjustment reconciliations.

SELECT clchgsnd_id,
       loan_number_txt,
       disbursement_number,
       change_code_txt,
       old_value_txt,
       new_value_txt,
       status_code,
       status_date
FROM   apps.igf_sl_clchsn_dtls_v
WHERE  status_code = 'PENDING'
ORDER  BY creation_date DESC;

To list all open change records grouped by response status:

SELECT response_status_code,
       COUNT(*)
FROM   apps.igf_sl_clchsn_dtls_v
WHERE  response_status_code NOT IN ('ACCEPTED','REJECTED')
GROUP  BY response_status_code;

Because the view performs lookup decoding and a reference-table join, queries that filter on decoded description text rather than the stored code incur additional processing. Filtering on the stored code columns (STATUS_CODE, RESPONSE_STATUS_CODE, CHANGE_ISSUE_CODE) is the recommended practice for large extracts.