Results for “gr_item_concentrations”

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

Overview

GR_ITEM_CONCENTRATIONS is a table owned by the GR schema within the Oracle E-Business Suite Process Manufacturing Regulatory Management module (GR). It is implemented in both Oracle EBS 12.1.1 and 12.2.2 and is documented as a temporary table used during the document build process. In practice, the table functions as an intermediate staging area: during the generation of regulatory documents such as Safety Data Sheets, labels, and compliance reports, the GR document build engine resolves item compositions and writes the resulting ingredient concentration values here before final rendering.

Because the contents are transient and rebuilt per document run, the table should not be treated as a durable system of record. Instead, it serves as a working buffer that isolates the volatile, run-specific output of the build process from the persistent master data held in the GR item and concentration detail tables.

Applying a heuristic Data Vault classification based on the documented foreign-key structure and the composite primary key GR_ITEM_CONCENTRATIONS_PK on (ITEM_CODE, INGREDIENT_ITEM_CODE), this object leans toward a satellite modeling pattern. It describes descriptive attributes of the composition relationship at a point in time — specifically the concentration percentage and hierarchy level — rather than acting as a pure hub or link. This classification is a modeling suggestion, not a documented fact.

Key Information Stored

The table exposes ten documented columns. The most significant are:

• ITEM_CODE — the parent (finished or formulated) item whose composition is being described. Part of the composite primary key.
• INGREDIENT_ITEM_CODE — the component/ingredient item appearing in that composition. Also part of the composite primary key. Together these two columns form the business-key candidate GR_ITEM_CONCENTRATIONS_PK.
• LEVEL_HIERARCHY_CODE — indicates the level at which the ingredient sits within the item's composition hierarchy, supporting multi-level structure roll-up during the document build.
• CONCENTRATION_PERCENTAGE — the measured or calculated percentage of the ingredient within the parent item, the primary quantitative value consumed by regulatory reporting logic.
• MIGRATION_IND — flags records created through migration versus day-to-day processing, useful for auditing data provenance.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — the standard EBS "who/when" audit columns recording the last modification.
• CREATED_BY, CREATION_DATE — the standard EBS "who/when" audit columns recording initial row creation.

No separate surrogate single-column key is documented; the primary key is the composite business key of item and ingredient. The audit columns follow the standard Oracle EBS convention and should be filtered on during reconciliation or diagnostic queries.

Common Use Cases and Queries

Typical scenarios involve verifying the output of a document build run, troubleshooting missing or incorrect concentration values on generated documents, and reconciling the temporary build contents against the persistent detail table.

SELECT item_code,
       ingredient_item_code,
       level_hierarchy_code,
       concentration_percentage
FROM   gr.gr_item_concentrations
WHERE  item_code = :p_item_code
ORDER  BY level_hierarchy_code, ingredient_item_code;

A reconciliation query compares the staging table against the durable detail store to confirm the build produced the expected rows:

SELECT d.item_code, d.ingredient_item_code
FROM   gr.gr_item_conc_details d
WHERE  NOT EXISTS (
         SELECT 1
         FROM   gr.gr_item_concentrations c
         WHERE  c.item_code = d.item_code
         AND    c.ingredient_item_code = d.ingredient_item_code);

Other reporting uses include validating that concentration percentages sum acceptably at each hierarchy level and auditing which users or concurrent programs last touched a given build.

Related Objects

The documented foreign-key structure places this table at the centre of two relationships:

• GR_ITEM_GENERAL — GR_ITEM_CONCENTRATIONS.ITEM_CODE references GR_ITEM_GENERAL.ITEM_CODE. This is the master item definition supplying item-level attributes joined during the build.
• GR_ITEM_CONC_DETAILS — GR_ITEM_CONC_DETAILS.ITEM_CODE and GR_ITEM_CONC_DETAILS.INGREDIENT_ITEM_CODE reference this table. The detail table holds the persistent composition data from which the temporary concentration rows are derived.

Beyond these, the GR document build process itself (the generating concurrent program and related PL/SQL build logic) is the principal consumer and producer of the data. Because the table is transient, integrations and reports should generally be written against GR_ITEM_CONC_DETAILS and GR_ITEM_GENERAL rather than against this staging object, except where the live build output must be inspected directly.