Search Results sponsor_cd




Overview
Explore in AI Chat

Overview

IGS_FI_FEE_AS is a reporting and integration view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the IGS (Student System) product family. It is a secured (multi-tenant aware) view over the fee/sponsorship transaction entity, presenting a denormalised, ORG_ID-filtered projection of fee assessment and sponsor billing records. The view is query-only; it holds no storage of its own and is intended to shield consumers from the underlying table's partitioning and organisation_id column naming, exposing a stable column list for concurrent programs, OBIEE/BI Publisher extracts, and interface staging.

Because the view applies an ORG_ID predicate based on USERENV('CLIENT_INFO'), it returns only the rows belonging to the operating unit currently set in the session. This makes it the preferred access path for reporting where row-level operating unit security must be enforced without custom WHERE clauses.

Underlying Base Objects

The view is defined over a single documented base object, IGS_FI_FEE_AS_ALL, which is the multi-org table holding fee assessment records across all operating units. The definition is a straight projection of columns from the alias A, with one addition: A.ROWID is exposed as the pseudo-column ROW_ID to give each row a unique handle for downstream processing (for example, correlated updates or record identification in a flat file).

The only transformational logic in the view text is the security predicate:

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

This compares the row's ORG_ID against the operating unit parsed from the first ten bytes of the CLIENT_INFO session string, substituting -99 where neither value is derivable. Practically, this means a session with no operating unit context can only see rows whose ORG_ID is null — a behaviour worth remembering when a query unexpectedly returns no data.

Key Columns

The column list mirrors the fee assessment entity. For the searched term SPONSOR_CD, the view exposes the sponsor code identifying the third party (employer, government body, or other funding organisation) responsible for the fee transaction. This is the column to filter or group on when reporting sponsored students, sponsor invoicing, or reconciliation of sponsor-funded fee lines.

Common Use Cases and Queries

Typical scenarios include extracting sponsor-liable fee transactions for invoicing, reporting fee assessments by calendar and category, and reconciling transaction amounts against general ledger entries. Because the view is already org-secured, the operating unit must be initialised before querying.

  • List active sponsor-funded transactions:

SELECT person_id, transaction_id, fee_type, transaction_amount, currency_cd, sponsor_cd
FROM apps.igs_fi_fee_as
WHERE sponsor_cd IS NOT NULL
AND logical_delete_dt IS NULL;

  • Aggregate fees by sponsor and calendar:

SELECT sponsor_cd, fee_cal_type, currency_cd, SUM(transaction_amount) amount
FROM apps.igs_fi_fee_as
WHERE logical_delete_dt IS NULL
GROUP BY sponsor_cd, fee_cal_type, currency_cd;

  • Trace a single transaction by its surrogate key:

SELECT * FROM apps.igs_fi_fee_as WHERE transaction_id = :p_transaction_id;

Reports should always exclude logically deleted rows via LOGICAL_DELETE_DT IS NULL, and joins to person or sponsor validation tables should be made on PERSON_ID and SPONSOR_CD respectively.