Search Results igi_exp_dus




Overview
Explore in AI Chat

Overview

APPS.IGI_EXP_DUS_NOT_IN_TUS_V is a reporting view in the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environments, owned by the APPS schema. It belongs to the IGI (Oracle Grants / Public Sector) product family and specifically supports the Expediture Certification / Document Understanding feature set. As its name implies, the view exposes Document Under (DU) expenditure records that are not yet associated with a Transaction Under (TU). It is therefore a "pending" or "unmatched" expenditure view, presenting those DU records whose TU_ID is null and which are awaiting linkage to a Transaction Under record.

The view is designed for reporting and integration rather than transaction processing. It denormalizes data from several base tables into a single, human-readable result set, adding descriptive text such as application name, currency description, status meaning, and the user name of the person who created the DU. This makes it suitable for concurrent programs, Oracle Reports, OBIEE/XML Publisher extracts, and ad-hoc SQL used by grant administrators reconciling unmatched expenditures.

Underlying Base Objects

The view is defined over six documented base objects, joined as follows:

The join predicates link DU to its type header (DU_TYPE_HEADER_ID), to the application (IEDTH.APPLICATION_ID = FAL.APPLICATION_ID), to the currency (FC.CURRENCY_CODE = IED.DU_CURRENCY_CODE), to the creating user (FU.USER_ID = IED.DU_BY_USER_ID), and to the lookup for status (IL.LOOKUP_CODE = IED.DU_STATUS AND IL.LOOKUP_TYPE = 'IGI_EXP_DU_STATUS'). The critical filter is IED.TU_ID IS NULL, which restricts the result set to unmatched DU records.

Key Columns

  • ORG_ID — the operating unit / organization identifier, supporting multi-org security.
  • ROW_ID — the ROWID of the underlying IGI_EXP_DUS row.
  • DU_ID — the primary identifier of the Document Under record.
  • DU_TYPE_HEADER_ID / DU_TYPE_NAME / APPLICATION_ID / APPLICATION_NAME — the DU type classification and the owning application.
  • DU_ORDER_NUMBER, DU_LEGAL_NUMBER, DU_DESCRIPTION — business identifiers and description.
  • DU_CURRENCY_CODE, DU_CURRENCY — currency code and its descriptive name.
  • DU_AMOUNT, DU_PREPAY_AMOUNT — the expenditure amount and any prepayment applied.
  • DU_STATUS, STATUS_DESC — the status code and its translated meaning via IGI_LOOKUPS.
  • DU_STP_ID, DU_STP_SITE_ID — supplier and supplier site references.
  • TU_ID — the Transaction Under link; always NULL in this view by definition.
  • PRINT_DATE, DU_FISCAL_YEAR, DU_DATE — dates relevant to the expenditure.
  • DU_BY_USER_ID, DU_BY_USER_NAME — the user who raised the DU.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard audit Who columns.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the descriptive flexfield segments.

Common Use Cases and Queries

The principal use case is identifying Document Under expenditures that have not yet been matched to a Transaction Under, so that they can be reviewed, reconciled, or interfaced onward. Because the view already resolves codes into descriptions, it is convenient for direct reporting without additional lookups.

A typical query lists pending DUs for a given operating unit:

SELECT DU_ID, DU_TYPE_NAME, DU_ORDER_NUMBER, DU_LEGAL_NUMBER,
       DU_AMOUNT, DU_CURRENCY, STATUS_DESC, DU_DATE, DU_BY_USER_NAME
FROM   APPS.IGI_EXP_DUS_NOT_IN_TUS_V
WHERE  ORG_ID = :p_org_id
ORDER  BY DU_DATE DESC;

A second query aggregates pending amounts by status and currency:

SELECT STATUS_DESC, DU_CURRENCY, COUNT(*) DU_COUNT, SUM(DU_AMOUNT) TOTAL_AMOUNT
FROM   APPS.IGI_EXP_DUS_NOT_IN_TUS_V
GROUP  BY STATUS_DESC, DU_CURRENCY;

A third restricts to records created within a fiscal year:

SELECT DU_ID, DU_FISCAL_YEAR, DU_AMOUNT, STATUS_DESC
FROM   APPS.IGI_EXP_DUS_NOT_IN_TUS_V
WHERE  DU_FISCAL_YEAR = :p_year;

Because the view is read-only and derived from the transactional base table IGI_EXP_DUS, joins to other IGI tables on DU_ID can be added where deeper detail is required.