Search Results record_process_code

Overview

The IGF_SL_CLCHRS_DTLS_V view is an APPS-owned database view within the IGF (Financial Aid) product family of Oracle E-Business Suite, available in releases 12.1.1 and 12.2.2. It exposes all change response records associated with a loan record for the CommonLine Release 4 transaction set. CommonLine is the standardized electronic data interchange format used by the U.S. federal student loan industry for loan origination, disbursement, and change transactions; Release 4 responses represent the guarantor or servicer's answer to a change request submitted by the school or lender.

The view is primarily a reconciliation and reporting artifact. When a change request is transmitted and a CommonLine change response is received and loaded by the financial aid batch processes, the resulting response detail rows become queryable through this view. Its principal integration value lies in column RECORD_PROCESS_DESC, which is not stored physically but is derived at query time by calling IGF_AW_GEN.LOOKUP_DESC against the lookup type IGF_SL_CL_ACK_REC_STATUS, translating the internal RECORD_PROCESS_CODE into a human-readable acknowledgement status. This is the exact linkage a user searches for with "igf_sl_cl_ack_rec_status" — the view is the standard consumer of that lookup.

Underlying Base Objects

The view is defined over a single base table, IGF_SL_CLCHRS_DTLS, aliased as CLCHGRSP. ETRM documentation lists no other referenced base objects, so all data is sourced from that one change-response detail table. The view does not persist data; it is a straight projection of the columns with the addition of one derived lookup description column.

Because the definition has no joins, no DISTINCT clause, and no aggregation, the row count and cardinality of the view match those of the base table exactly. Deletes, updates, and inserts performed against the base table by the CommonLine inbound processing programs are immediately visible. The view carries the ROWID of the underlying table row as its first column, which allows the view to be treated as updateable. No object dependencies toward other EBS tables are documented beyond the PL/SQL function call used for the description column.

Key Columns

• ROW_ID / CLCHGRSP_ID — Identifier of the underlying change response row; CLCHGRSP_ID is the primary key of the base table.
• CLRP1_ID — Foreign key linking the change response to the originating loan record. This is the join path back to the loan-level data.
• RECORD_CODE — CommonLine record code identifying the response record type within the Release 4 layout.
• SEND_RECORD_TXT — The full flat-file text of the response record as received or transmitted.
• ERROR_MESSAGE_1_CODE through ERROR_MESSAGE_5_CODE — Up to five error or rejection codes returned for the record, each corresponding to a validation failure detected by the receiver.
• RECORD_PROCESS_CODE — Internal code representing the processing/acknowledgement state of the record.
• RECORD_PROCESS_DESC — Derived description of RECORD_PROCESS_CODE, resolved through the IGF_SL_CL_ACK_REC_STATUS lookup; not a stored column on the base table.
• Created/Updated audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.
• Concurrent request columns — REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, identifying the batch program that last touched the row.

Common Use Cases and Queries

Typical scenarios include troubleshooting rejected change responses, auditing what was transmitted for a given loan, and reporting the acknowledgement status distribution across a processing run.

• List all responses for a loan with readable status:
  SELECT clrp1_id, record_code, record_process_code, record_process_desc, send_record_txt FROM igf_sl_clchrs_dtls_v WHERE clrp1_id = :loan_id;
• Identify records rejected by the receiver:
  SELECT clchgrsp_id, clrp1_id, error_message_1_code FROM igf_sl_clchrs_dtls_v WHERE error_message_1_code IS NOT NULL AND record_process_desc <> 'Accepted';
• Audit a specific concurrent program run:
  SELECT record_process_desc, COUNT(*) FROM igf_sl_clchrs_dtls_v WHERE request_id = :request_id GROUP BY record_process_desc;
• Trace the batch that processed a response:
  SELECT clchgrsp_id, program_id, program_application_id, last_update_date FROM igf_sl_clchrs_dtls_v WHERE creation_date >= TRUNC(SYSDATE);

Because RECORD_PROCESS_DESC invokes a lookup function, queries filtering or grouping on that column execute the function per row; filtering instead on RECORD_PROCESS_CODE is more efficient when the target code value is known.