Results for “igi_rpi_items_all”

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

Overview

IGI_RPI_ITEMS_ALL is a transactional and reference table within the IGI (Public Sector Financials International) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It defines the items and services that are used in standing charges — the recurring, periodically billed services commonly associated with public sector receivables processing, such as facilities charges, maintenance fees, or subscription-based assessments. The table serves as the master definition point for each billable item, holding both its descriptive identity and its pricing information.

Because the table acts as the authoritative source for item identity while being referenced by multiple downstream transactional records, its heuristic Data Vault classification is hub-leaning. Under that modeling lens, ITEM_ID functions as the hub key, with price, effective-date, and audit attributes behaving like satellite payload. Practically, in the EBS architecture, the table is a parent/master object whose primary key is consumed by setup, update, audit, and line-detail processes.

Key Information Stored

Each row represents one standing-charge item or service, scoped to a set of books and, where relevant, an operating unit. The documented physical structure contains 21 columns; the most consequential are the following.

• ITEM_ID — the surrogate primary key, defined by the constraint IGI_RPI_ITEMS_PK. It is the value propagated to every dependent table.
• SET_OF_BOOKS_ID — the ledger context in which the item is defined, essential for multi-organization, multi-ledger deployments.
• ORG_ID — the operating unit or organization identifier, supporting data partitioning and security.
• ITEM_CODE — the human-readable business identifier for the item; the primary business-key candidate, since ITEM_ID is only a technical surrogate.
• DESCRIPTION — the textual name or narrative of the item or service.
• PRICE — the base charge amount applied for the item.
• PRICE_EFFECTIVE_DATE — the date from which the base price takes effect.
• REVISED_PRICE and REVISED_PRICE_EFF_DATE — a second pricing pair, supporting a revised rate and its own effective date, enabling mid-stream price changes without losing the original.
• UNIT_OF_MEASURE — the unit basis on which the item is charged.
• START_EFFECTIVE_DATE and INACTIVE_DATE — the active lifecycle window that determines whether an item may currently be billed.
• ENABLED_FLAG — the standard EBS status flag controlling whether the item is available for use.
• VAT_TAX_ID — the tax code applied to the item.
• REVENUE_CODE_COMBINATION_ID — the accounting flexfield combination to which revenue is posted.
• RUN_ID — links the item to a processing run, supporting batch or update workflows.
• Standard audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN, providing full WHO-column traceability.

Common Use Cases and Queries

The most common requirement is retrieving the current valid price for an item at a given point in time. A typical pattern resolves the effective pricing by comparing the price effective dates against a transaction date and observing the active window.

• Item lookup by business code: SELECT ITEM_ID, DESCRIPTION, PRICE FROM IGI_RPI_ITEMS_ALL WHERE ITEM_CODE = :p_code AND SET_OF_BOOKS_ID = :p_sob AND ENABLED_FLAG = 'Y'.
• Active item listing: filter on ENABLED_FLAG = 'Y' and a date between START_EFFECTIVE_DATE and NVL(INACTIVE_DATE, SYSDATE).
• Price history / revisions: select ITEM_ID, PRICE, PRICE_EFFECTIVE_DATE, REVISED_PRICE, REVISED_PRICE_EFF_DATE for items flagged with a non-null REVISED_PRICE.
• Revenue reconciliation: join REVENUE_CODE_COMBINATION_ID to GL_CODE_COMBINATIONS to report the accounting distribution of standing-charge revenue.
• Batch run tracing: group by RUN_ID to audit which items were touched during a processing run.

These patterns support standing-charge billing extracts, price-change audits, and revenue-code analysis commonly requested by public sector finance teams.

Related Objects

Five documented child tables reference IGI_RPI_ITEMS_ALL through foreign keys, all keyed on ITEM_ID or its role as an endpoint of an update range.

• IGI_RPI_AUDIT_ITEMS_ALL — join on ITEM_ID; stores the item-level audit trail.
• IGI_RPI_LINE_DETAILS_ALL — join on ITEM_ID; the transactional line detail for charged items.
• IGI_RPI_UPDATE_HDR_ALL — referenced twice, via ITEM_ID_FROM and ITEM_ID_TO, describing mass-update header ranges.
• IGI_RPI_UPDATE_LINES_ALL — join on ITEM_ID; the affected items within each update.

Together these form the standing-charge processing chain: item master, update/versioning, and billing detail and audit.