Results for “db_cr_flag”

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

Overview

IGF_GR_ELEC_STAT_DET is an Oracle E-Business Suite 12.1.1 / 12.2.2 view owned by the APPS schema in the IGF (Financial Aid) product family. It exposes electronic status detail records related to Pell Grant reporting under the Grants and Pell (GR) sub-module. The view is a Multi-Org secured (MOAC) reporting layer defined over the base table IGF_GR_ELEC_STAT_DET_ALL, filtering rows so that the caller sees only records belonging to the operating unit indicated by the session's CLIENT_INFO setting. This pattern is standard for Oracle EBS "_ALL" tables that carry an ORG_ID column.

The object is classified as a VIEW and is marked VALID in the ETRM 12.2.2 repository. Its role is primarily reporting and integration: it gives downstream queries, concurrent programs, and interface logic a flat, operating-unit-aware projection of electronic status transaction rows without exposing the underlying Multi-Org storage table directly.

Underlying Base Objects

The view is defined over a single base object: IGF_GR_ELEC_STAT_DET_ALL, aliased GRDET in the view text. No other base tables or views are documented in the ETRM metadata.

The defining predicate is:

• NVL(GRDET.ORG_ID, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))), -99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))), -99)

This is the classic Multi-Org view filter: rows whose ORG_ID matches the current operating unit (read from the first ten characters of CLIENT_INFO) are returned; if CLIENT_INFO is blank, the comparison resolves to -99. The selected columns are a straight projection of the base table's columns with ROWID exposed as ROW_ID for row identity.

Key Columns

• ROW_ID — the physical ROWID of the underlying GRDET row, provided for direct row addressing.
• ESD_ID — primary key of the electronic status detail record.
• REP_PELL_ID — identifier linking the row to its Pell Grant report.
• DUNS_ID — the institution's DUNS number reference used in federal reporting.
• GAPS_AWARD_NUM — award number from the federal GAPS (Grant Administration and Payment System).
• TRANSACTION_DATE — date of the electronic status transaction.
• DB_CR_FLAG — debit/credit indicator distinguishing the direction of the adjustment.
• ADJ_AMT — the adjustment amount. This is the column most relevant to the user's "adj_amt" search; it carries the monetary adjustment value for each electronic status detail row.
• GAPS_PROCESS_DATE — date the transaction was processed through GAPS.
• ADJ_BATCH_ID — identifier of the adjustment batch that produced the row.
• Standard WHO 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.
• ORG_ID — the operating unit used by the Multi-Org filter.

Common Use Cases and Queries

Typical scenarios include reconciling Pell adjustment batches, reporting debit/credit adjustments to GAPS, and auditing electronic status changes by award or DUNS. Because of the MOAC predicate, the CLIENT_INFO setting must be initialized (as it is automatically within Oracle Forms and concurrent programs) for correct results.

Example: retrieve adjustment amounts for a Pell report within the current operating unit.

• SELECT esd_id, rep_pell_id, gaps_award_num, transaction_date, db_cr_flag, adj_amt, adj_batch_id
• FROM apps.igf_gr_elec_stat_det
• WHERE rep_pell_id = :p_pell_id
• ORDER BY transaction_date, esd_id;

Example: aggregate net adjustment by batch.

• SELECT adj_batch_id,
• SUM(CASE WHEN db_cr_flag = 'C' THEN adj_amt ELSE -adj_amt END) net_adj
• FROM apps.igf_gr_elec_stat_det
• GROUP BY adj_batch_id;

Because the view filters on ORG_ID, any query executed outside an initialized Multi-Org session may return no rows; callers should confirm the active operating unit before diagnosing an apparently empty result set.