Results for “gr_item_toxic”

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

Overview

GR_ITEM_TOXIC is a table in the GR (Process Manufacturing Regulatory Management) module of Oracle E-Business Suite, present in both 12.1.1 and 12.2.2. It stores toxicity information recorded against an inventory or formula item, and the design explicitly permits multiple toxicity rows per item. Each row represents a distinct combination of exposure, route, species, label, and effect, which allows regulatory users to capture the full hazard profile of a substance rather than a single normalized record.

From a Data Vault modeling perspective, the heuristic classification for this object is a link. This suggestion reflects the fact that the table is largely composed of foreign-key references to independent reference entities — labels, items, toxic routes, toxic species, and toxic effects — with a small set of descriptive attributes attached. Functionally it acts as the association table that ties a regulated item to its toxicological characteristics. Because the module is regulatory in nature, the table is read primarily by compliance reporting, MSDS/label generation, and hazard communication processes rather than by transactional manufacturing flows.

Key Information Stored

The table contains 46 documented columns. The most significant are the business-key and descriptive columns below; the remaining columns are the standard 30 descriptive flexfield attributes (ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE30) plus the five WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN).

• TOXIC_EXPOSURE_CODE — code indicating the exposure scenario or condition associated with the toxic record.
• LABEL_CODE — the label context to which the toxicity information applies; foreign key to GR_LABELS_B.
• ITEM_CODE — the regulated item the toxicity rows describe; foreign key to GR_ITEM_GENERAL.
• TOXIC_ROUTE_CODE — route of exposure (for example, inhalation or ingestion); foreign key to GR_TOXIC_ROUTES_B.
• TOXIC_SPECIES_CODE — the test species the toxicological data pertains to; foreign key to GR_TOXIC_SPECIES_B.
• TOXIC_EFFECT_CODE — the observed toxic effect; foreign key to GR_TOXIC_EFFECTS_B.
• TOXIC_DOSE and TOXIC_UOM — the dose quantity and its unit of measure for the recorded effect.
• EXPOSURE_TIME — the duration of exposure associated with the dose.
• TOXIC_NOTE — free-form narrative supporting the toxicological entry.

The unique index GR_ITEM_TOXIC_PK (TOXIC_EXPOSURE_CODE, LABEL_CODE, ITEM_CODE, TOXIC_ROUTE_CODE, TOXIC_SPECIES_CODE) is the business-key candidate and doubles as the primary key. It is a composite natural key spanning five columns rather than a surrogate identifier, which means uniqueness is enforced by the combination of exposure, label, item, route, and species.

Common Use Cases and Queries

The predominant use case is hazard and regulatory reporting: retrieving the complete toxicity profile of an item for label text, safety data sheets, or compliance submittals. A second use case is reference validation — because every code column is a foreign key, users frequently query the table to confirm that a route, species, or effect is actually in use before retiring a reference value. A third is product stewardship analytics, aggregating toxic effects across items or species.

Typical SQL patterns include:

• Item-centric lookup: SELECT * FROM GR.GR_ITEM_TOXIC WHERE ITEM_CODE = :item;
• Joining to reference tables: SELECT i.ITEM_CODE, r.TOXIC_ROUTE_CODE, s.TOXIC_SPECIES_CODE, i.TOXIC_DOSE, i.TOXIC_UOM FROM GR.GR_ITEM_TOXIC i, GR.GR_TOXIC_ROUTES_B r, GR.GR_TOXIC_SPECIES_B s WHERE i.TOXIC_ROUTE_CODE = r.TOXIC_ROUTE_CODE AND i.TOXIC_SPECIES_CODE = s.TOXIC_SPECIES_CODE AND i.ITEM_CODE = :item;
• Effect distribution reporting grouped by TOXIC_EFFECT_CODE and TOXIC_SPECIES_CODE.

Related Objects

• GR_ITEM_GENERAL — joined on ITEM_CODE; the master item record for regulatory attributes.
• GR_LABELS_B — joined on LABEL_CODE; supplies label header information.
• GR_TOXIC_ROUTES_B — joined on TOXIC_ROUTE_CODE; defines valid exposure routes.
• GR_TOXIC_SPECIES_B — joined on TOXIC_SPECIES_CODE; defines valid test species.
• GR_TOXIC_EFFECTS_B — joined on TOXIC_EFFECT_CODE; defines valid toxic effects.

These five reference tables form the core join graph around GR_ITEM_TOXIC; queries against the table are almost always resolved through one or more of them.