Results for “item_document_status”

16 results




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

Overview

The GR_ITEM_DOC_STATUSES table is a Process Manufacturing Regulatory Management (GR) data object within Oracle E-Business Suite, available in both 12.1.1 and 12.2.2 releases. It resides in the GR schema and stores the current document status and last change date associated with a given item/document code combination. In regulatory management workflows, item-level documentation (such as MSDS sheets, compliance certificates, or specification documents) must be tracked per item, and this table serves as the authoritative status register that records where each item-document pairing stands in its lifecycle.

From a heuristic Data Vault modeling perspective, this table is classified as satellite-leaning. It does not behave as a classic hub (no single surrogate identity anchor) nor as a pure link. Instead it describes descriptive state attributes—status, version, approval user, update timestamps—keyed to the combination of two business entities (item and document). This pattern is characteristic of a satellite attached to the intersection of an item hub and a document hub, which is consistent with its composite primary key on ITEM_CODE and DOCUMENT_CODE.

Key Information Stored

The table contains 13 documented columns, keyed by a composite primary key defined through GR_ITEM_DOC_STATUSES_PK. The most significant columns are:

  • ITEM_CODE — Business-key column and part of the composite primary key. Identifies the regulatory-managed item; also a foreign key to GR_ITEM_GENERAL.
  • DOCUMENT_CODE — Business-key column and the second part of the primary key. Identifies the document type; foreign key to GR_DOCUMENT_CODES.
  • ITEM_DOCUMENT_STATUS — The current lifecycle status of the item-document pairing (for example, approved, pending, or obsolete). This is the primary state attribute the table is named for.
  • ITEM_DOCUMENT_VERSION — The version indicator of the item document, supporting revision tracking.
  • LAST_APPROVAL_USER — The user who last approved the document, providing audit traceability.
  • LAST_DOC_UPDATE_DATE — The last date the document status changed, matching the table's stated description of "last change date."
  • REBUILD_ITEM_DOC_FLAG — A processing indicator, likely used by concurrent programs to signal that the item document must be regenerated or rebuilt.
  • LAST_DOC_AUTHOR_LOCK — A lock or state marker associated with the document authoring process.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard Oracle EBS WHO columns capturing creation and audit metadata.

The unique index GR_ITEM_DOC_STATUSES_PK (ITEM_CODE, DOCUMENT_CODE) is the definitive business-key candidate. There is no separate single-column surrogate key documented; the composite business key doubles as the primary key.

Common Use Cases and Queries

Typical usage centers on regulatory reporting and item document status monitoring. A common query retrieves the status and version of all documents tied to a specific item:

  • Status lookup by item: SELECT DOCUMENT_CODE, ITEM_DOCUMENT_STATUS, ITEM_DOCUMENT_VERSION, LAST_DOC_UPDATE_DATE FROM GR_ITEM_DOC_STATUSES WHERE ITEM_CODE = :item_code;
  • Documents pending approval or recently changed, filtered on LAST_APPROVAL_USER and LAST_DOC_UPDATE_DATE, for compliance dashboards.
  • Joins to GR_ITEM_GENERAL and GR_DOCUMENT_CODES to resolve item descriptions and document names for reporting.
  • Operational monitoring of REBUILD_ITEM_DOC_FLAG to identify records requiring document regeneration.

Related Objects

The following objects are most significant in relation to this table, based on documented foreign-key relationships:

  • GR_ITEM_GENERAL — Referenced by GR_ITEM_DOC_STATUSES.ITEM_CODE; the parent item master record.
  • GR_DOCUMENT_CODES — Referenced by GR_ITEM_DOC_STATUSES.DOCUMENT_CODE; defines available document types.
  • GR_ITEM_DOCUMENT_DTLS — References this table via ITEM_CODE and DOCUMENT_CODE; holds detailed item-document content and depends on the status record.
  • GR_ITEM_DOC_STATUSES_PK — Supporting unique index enforcing the composite business key.

Together these relationships establish GR_ITEM_DOC_STATUSES as the central status registry connecting items, document definitions, and item-document detail records within Oracle Process Manufacturing Regulatory Management.