Search Results chg_num

Overview

IGF.IGF_SL_DL_CHG_SEND_ALL is a transactional staging table within the Oracle E-Business Suite Student Loan (IGF) module, which supports Title IV federal financial aid processing. The table persists change records associated with Direct Loan disbursement activity — specifically, the set of field values that have been modified on a Direct Loan record and that must eventually be transmitted to the federal Loan Origination Center (LOC). Each row captures a single changed attribute, its new value, and a processing status indicating whether that change has been sent.

The table resides in the APPS_TS_TX_DATA tablespace, consistent with its role as a high-volume transactional workspace rather than reference data. From a Data Vault modeling perspective, the column composition — a surrogate identifier (CHG_NUM), descriptive attributes (CHG_CODE, NEW_VALUE, STATUS), an association to a parent batch, and full standard Who columns — suggests a satellite-leaning structure rather than a hub or link. This classification is a heuristic generated from the foreign key and index profile and should be treated as a modeling suggestion, not a normative ETRM designation.

Records are written as part of the change-detection and outbound transmission workflow. Until STATUS is advanced to a sent condition, the record represents pending work that must be picked up by the Direct Loan outbound process. This makes the table operationally sensitive: rows left in a pre-send status generally indicate an incomplete transmission cycle.

Key Information Stored

The primary key is CHG_NUM (NUMBER(12)), a surrogate identifier assigned to each individual Direct Loan value-change record. A unique index, IGF_SL_DL_CHG_SEND_ALL_U1 (in APPS_TS_TX_IDX), enforces uniqueness on this column. In practice CHG_NUM is the sole documented business-key candidate; no alternate composite unique constraint is recorded.

• DBTH_ID — foreign key to IGF_SL_DL_BATCH_ALL, tying each change row to its originating transmission batch.
• LOAN_NUMBER (VARCHAR2(30)) — the Direct Loan identifier the change applies to.
• CHG_CODE (VARCHAR2(30)) — the database field name whose value changed; identifies which loan attribute is being reported.
• NEW_VALUE (VARCHAR2(4000)) — the replacement value, sized generously to accommodate maximum field lengths in the change file.
• STATUS (VARCHAR2(30)) — lifecycle state of the record, typically distinguishing "ready to send" from "sent to LOC."
• ORG_ID (NUMBER(15)) — operating unit identifier, enabling multi-org data separation.
• Standard Who columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN.
• Concurrent program columns — REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE, identifying the process that wrote or last touched the row.

Common Use Cases and Queries

The most frequent operational query identifies unsent change records for a given loan or batch, typically to diagnose a stalled outbound transmission:

SELECT c.CHG_NUM, c.LOAN_NUMBER, c.CHG_CODE, c.NEW_VALUE, c.STATUS
FROM   IGF.IGF_SL_DL_CHG_SEND_ALL c
WHERE  c.STATUS <> 'SENT'
AND    c.ORG_ID = :org_id;

Reconciliation reporting aggregates pending changes by batch to confirm that every batch eventually clears:

SELECT DBTH_ID, STATUS, COUNT(*) 
FROM   IGF.IGF_SL_DL_CHG_SEND_ALL
GROUP  BY DBTH_ID, STATUS;

Audit queries trace which concurrent request generated a change set, using REQUEST_ID joined to the standard concurrent request views. Reviewers also query by LOAN_NUMBER and CHG_CODE to reconstruct the history of a specific loan attribute over time, since each change is preserved as its own row rather than overwriting the prior value.

Related Objects

The documented dependency structure centers on the batch parent. The single recorded foreign key is DBTH_ID referencing IGF.IGF_SL_DL_BATCH_ALL, so the principal join is:

FROM IGF.IGF_SL_DL_CHG_SEND_ALL c, IGF.IGF_SL_DL_BATCH_ALL b
WHERE c.DBTH_ID = b.DBTH_ID

• IGF.IGF_SL_DL_BATCH_ALL — parent batch header; join on DBTH_ID.
• IGF.IGF_SL_DL_CHG_SEND_ALL own indexes — IGF_SL_DL_CHG_SEND_ALL_PK and IGF_SL_DL_CHG_SEND_ALL_U1, both on CHG_NUM.
• Direct Loan outbound transmission programs that read pending rows and update STATUS.
• Standard concurrent request views, joined via REQUEST_ID, for process auditing.
• Direct Loan origination and disbursement tables keyed by LOAN_NUMBER, useful for reconstructing current loan state.