Search Results igf_sl_cl_batch_all_u1

Overview

IGF.IGF_SL_CL_BATCH_ALL is a transactional table in the Oracle E-Business Suite Financials (IGF) product family that maintains CommonLine batch file information. CommonLine is the standardized electronic data exchange protocol used within U.S. federal student financial aid processing to transmit loan application, disbursement, and response records between schools, lenders, and service providers. This table serves as the master registry of batch files handled by the Oracle Student Loan (SL) module, recording both send files created by the module and response files received and loaded into it.

Each row represents a single CommonLine batch header, capturing the participants on both sides of the exchange (source and recipient), the transmission and creation timestamps, the file identification code, and aggregated monetary totals for the records contained in the batch. Because the table anchors downstream response and disbursement records that reference the batch via the CBTH_ID key, it functions as the central control point for reconciliation, audit, and status reporting across the CommonLine file lifecycle.

From a Data Vault modeling perspective, the heuristic classification of this table is hub-leaning: the primary key CBTH_ID behaves as a natural business key that is referenced by dependent detail tables, while the descriptive attributes (dates, identifiers, totals) occupy a satellite-like role. This classification is offered as a modeling suggestion rather than a physical implementation directive.

Key Information Stored

IGF_SL_CL_BATCH_ALL contains 29 documented columns in the 12.1.1 schema. The most significant include:

• CBTH_ID (NUMBER, 15) — Surrogate identifier for the batch. This is the primary key (IGF_SL_CL_BATCH_ALL_PK) and also carries the unique index IGF_SL_CL_BATCH_ALL_U1, making it the business-key candidate as well.
• BATCH_ID (VARCHAR2, 40) — The external batch identifier assigned to the created or loaded file.
• FILE_CREATION_DATE / FILE_TRANS_DATE (DATE) — The date the file was produced and the date it was transmitted, respectively.
• FILE_IDENT_CODE (VARCHAR2, 30) — Encodes whether the file holds production or test data and its version.
• RECIPIENT_ID and RECIP_NON_ED_BRC_ID — The lender or service provider receiving a send file (or the school for a response file), plus its non-ED branch identifier.
• SOURCE_ID and SOURCE_NON_ED_BRC_ID — The originating school (send file) or lender/service provider (response file) as recorded in the header.
• SEND_RESP (VARCHAR2, 30) — Classifies the file as application send, application response, change send, change response, or disbursement roster.
• ORG_ID — Multi-org operating unit context.
• RECORD_COUNT_NUM — Count of records within the batch.
• TOTAL_NET_DISB_AMT, TOTAL_NET_EFT_AMT, TOTAL_NET_NON_EFT_AMT, TOTAL_REISSUE_AMT, TOTAL_CANCEL_AMT, TOTAL_DEFICIT_AMT, TOTAL_NET_CANCEL_AMT, TOTAL_NET_OUT_CANCEL_AMT — The financial summary amounts associated with the batch.
• Standard Who columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) and concurrent program context (REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID).

Common Use Cases and Queries

Typical usage centers on reconciling batch headers with their detail records, reporting on transmission status, and auditing monetary totals. A common pattern joins the batch header to response details:

SELECT b.batch_id, b.send_resp, b.file_trans_date,
       r.cbth_id, r.record_count
FROM   igf.igf_sl_cl_batch_all b,
       igf.igf_sl_cl_resp_r1_all r
WHERE  b.cbth_id = r.cbth_id
AND    b.org_id = :p_org_id;

Financial reconciliation queries aggregate batch totals by file type, while operational reports filter on FILE_TRANS_DATE ranges to identify late or missing transmissions. Disbursement roster batches are frequently paired with IGF_DB_CL_DISB_RESP_ALL to verify net disbursement amounts against detail records.

Related Objects

• IGF_DB_CL_DISB_RESP_ALL — References this table via CBTH_ID; holds CommonLine disbursement response detail for batches.
• IGF_SL_CL_RESP_R1_ALL — References this table via CBTH_ID; holds CommonLine response record detail.
• IGF_SL_CL_BATCH_ALL_PK — Primary key constraint on CBTH_ID.
• IGF_SL_CL_BATCH_ALL_U1 — Unique index on CBTH_ID.

These dependent detail tables inherit the batch context established here, making CBTH_ID the essential join column across the CommonLine schema.