EGP_ITEM_CAT_ASSIGNMENTS
Product: EGPmasterPer inventory orgThe item-to-category assignment — one row per item, organization, catalog (category set), and category; the bridge every category rollup crosses
The familiar EGP_ITEM_CATEGORIES name is a VIEW over this table in the Fusion dictionary — land this base table. The grain includes CATEGORY_ID: catalogs that allow multiple assignments produce several rows per item per catalog, so aggregate or filter to one catalog before joining to quantities or a rollup double-counts.
What the badges mean
- master
- Data class: what the table holds — master data, transaction documents, control/configuration, interface/staging, or a documented view.
Extract access
The delivered surfaces that reach this table — the BICC extract data store (PVO) for bulk extraction and the OTBI subject areas for real-time queries. There is no SQL path to the SaaS database.
- FscmTopModelAM.ScmExtractAM.EgpBiccExtractAM.ItemCatAssignmentsExtractPVOOTBI: Product Management - Item Catalog Category Hierarchy Real Time
Item Category Assignments data store — the item-to-category relationship this table stores.
Oracle data-store documentation →
Fields
8 fields · 4 key
| # | Field | Description | Type | Flags |
|---|---|---|---|---|
| 1 | INVENTORY_ITEM_ID | Item being categorized | NUMBER | Key |
| 2 | ORGANIZATION_ID | Organization of the item row | NUMBER | Key |
| 3 | CATEGORY_SET_ID | Catalog (category set) of the assignment | NUMBER | Key |
| 4 | CATEGORY_ID | Category within the catalog | NUMBER | Key |
| 5 | ITEM_CATEGORY_ASSIGNMENT_ID | Surrogate id of the assignment row | NUMBER | |
| 6 | START_DATE | Assignment effective-from date | DATE | |
| 7 | END_DATE | Assignment effective-to date | DATE | |
| 8 | SEQUENCE_NUMBER | Display ordering of categories in the catalog | NUMBER |
Field provenance: hand-curated. 4 key fields.
Boilerplate SQL
Starting point for reading the BICC-landed copy of EGP_ITEM_CAT_ASSIGNMENTSon Databricks — real DATE columns need no conversion, and the org anchor and the LAST_UPDATE_DATE watermark (the column incremental BICC extracts key on) are already in place. Set your Unity Catalog location, schema, and org values below; they’re substituted into the SQL and the copy button.
-- ============================================================
-- Table : EGP_ITEM_CAT_ASSIGNMENTS — The item-to-category assignment — one row per item, organization, catalog (category set), and category; the bridge every category rollup crosses
-- Purpose: Column-selected read of EGP_ITEM_CAT_ASSIGNMENTS — auto-generated from field metadata
-- Grain : One row per inventory org (ORGANIZATION_ID) + INVENTORY_ITEM_ID + CATEGORY_SET_ID + CATEGORY_ID
-- Notes : Auto-generated skeleton for Oracle Fusion Cloud data landed in your lakehouse by a BICC extract — there is no SQL path to the SaaS database. Column names follow Oracle's table documentation — if your landed data still carries PVO attribute headers, map names first; see quirks #pvo-drift. Dates are real DATE/TIMESTAMP columns — no conversion needed. WHO audit columns omitted (see the quirks guide); the optional LAST_UPDATE_DATE watermark filter supports incremental extracts.
-- ============================================================
SELECT
t.INVENTORY_ITEM_ID AS "Item being categorized",
t.ORGANIZATION_ID AS "Organization of the item row",
t.CATEGORY_SET_ID AS "Catalog (category set) of the assignment",
t.CATEGORY_ID AS "Category within the catalog",
t.ITEM_CATEGORY_ASSIGNMENT_ID AS "Surrogate id of the assignment row",
t.START_DATE AS "Assignment effective-from date",
t.END_DATE AS "Assignment effective-to date",
t.SEQUENCE_NUMBER AS "Display ordering of categories in the catalog"
FROM <catalog>.<schema>.EGP_ITEM_CAT_ASSIGNMENTS t
WHERE
t.ORGANIZATION_ID = <inventory_org_id> -- inventory org, NOT the business unit — see quirks guide #item-org-striping
-- AND t.INVENTORY_ITEM_ID = <INVENTORY_ITEM_ID>
-- AND t.CATEGORY_SET_ID = <CATEGORY_SET_ID>
-- AND t.CATEGORY_ID = <CATEGORY_ID>
-- AND t.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>' -- the column incremental BICC extracts key on
ORDER BY t.INVENTORY_ITEM_ID;7 parameters not filled: <catalog>, <schema>, <inventory_org_id>, <INVENTORY_ITEM_ID>, <CATEGORY_SET_ID>, <CATEGORY_ID>, <watermark>
Relationships
1-hop neighbors — click a table to navigate there. FND lookup decode and translation edges are highlighted; they’re the joins newcomers most often get wrong.
Join details
ON EGP_ITEM_CAT_ASSIGNMENTS.INVENTORY_ITEM_ID = EGP_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID AND EGP_ITEM_CAT_ASSIGNMENTS.ORGANIZATION_ID = EGP_SYSTEM_ITEMS_B.ORGANIZATION_IDON EGP_ITEM_CAT_ASSIGNMENTS.CATEGORY_ID = EGP_CATEGORIES_B.CATEGORY_IDON EGP_ITEM_CAT_ASSIGNMENTS.CATEGORY_SET_ID = EGP_CATEGORY_SETS_B.CATEGORY_SET_ID