Item attributes are information about an item, such as item cost, lead time, and revision control. Type of attributes are Main, Operational and User Defined etc.,
- Main attributes: Are common to all items, and are part of the item’s data model. They describe essential aspects of the item like item_number, Description, Status, Item Class, Primary UOM etc.,
- Operational Attributes: Are part of the item’s data model. They determine the behavior of the item with respect to various applications outside Oracle Product Hub, such as Oracle Purchasing or Inventory Management or Order Mgmt. You can control the operational attributes at the master organization level or at the organization level.
- User-Defined Attributes: Define attribute groups and attributes to capture item specifications and other information relevant to a product’s definition that you want to add to the item’s data model. Uses extensible flexfields to support attribute groups (by using flexfield contexts) and attributes.
Operational attributes are key for the functionality in the respective Purchasing, Inventory, Order Mgmt, Manufacturing, Costing, Receivables modules etc., It is important to validate the operational attributes prior to entering any transaction(s).
Here is the SQL Query to validate operational item attributes for newly created items or converted items.
SELECT item_number,
(SELECT ITEM_CLASS_NAME
FROM EGP_ITEM_CLASSES_VL
WHERE item_class_id = esiv.ITEM_CATALOG_GROUP_ID) item_class,
esiv.item_type,
(SELECT V72673585.MEANING
FROM FND_LOOKUP_VALUES_VL V72673585
WHERE V72673585.LOOKUP_TYPE = ‘EGP_ITEM_TYPE’
AND LOOKUP_CODE = esiv.ITEM_TYPE) USER_ITEM_TYPE,
inventory_item_status_code item_status,
iop.organization_code inventory_org,
DECODE (esiv.BOM_ITEM_TYPE,
1, ‘Model’,
2, ‘Option Class’,
3, ‘Planning’,
4, ‘Standard’,
5, ‘Product Family’) STRUCTURE_ITEM_TYPE,
— OM Attributes
(SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = ‘EGP_ORDATPCHECKVS_TYPE’
AND LOOKUP_CODE = ESIV.ATP_FLAG
AND ROWNUM = 1) CHECK_ATP,
(SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = ‘EGP_ORDATPCHECKVS_TYPE’
AND LOOKUP_CODE = ESIV.ATP_COMPONENTS_FLAG
AND ROWNUM = 1) CHECK_ATP_COMPONENTS,
esiv.SHIPPABLE_ITEM_FLAG,
esiv.BACK_TO_BACK_ENABLED,
— Planning
(SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = ‘EGP_PLANNING_MAKE_BUY’
AND LOOKUP_CODE = ESIV.PLANNING_MAKE_BUY_CODE
AND ROWNUM = 1) MAKE_OR_BUY,
(SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = ‘EGP_PLANNING_CODE’
AND LOOKUP_CODE = ESIV.MRP_PLANNING_CODE
AND ROWNUM = 1) MRP_MPS_PLANNING_METHOD,
(SELECT ECB.CATEGORY_NAME
FROM EGP_ITEM_CATEGORIES EIC,
FUSION.EGP_CATEGORIES_VL ECB,
FUSION.EGP_CATEGORY_SETS_VL ECSB
WHERE EIC.INVENTORY_ITEM_ID = ESIv.INVENTORY_ITEM_ID
AND EIC.ORGANIZATION_ID = ESIv.ORGANIZATION_ID
AND ECB.CATEGORY_ID = EIC.CATEGORY_ID
AND ECSB.CATEGORY_SET_ID = EIC.CATEGORY_SET_ID
AND ECSB.CATALOG_CODE = ‘Planning_Catalog’) Planning_Catalog,
— Inventory flags
INVENTORY_ITEM_FLAG, STOCK_ENABLED_FLAG, MTL_TRANSACTIONS_ENABLED_FLAG, RESERVABLE_TYPE,
(SELECT MEANING
FROM FND_LOOKUP_VALUES_TL
WHERE LOOKUP_TYPE = ‘EGP_SERIAL_NUMBER_CONTROL_TYPE’
AND VIEW_APPLICATION_ID = 0
AND SET_ID = 0
AND LANGUAGE = ‘US’
AND LOOKUP_CODE = ESIV.SERIAL_NUMBER_CONTROL_CODE
AND ROWNUM = 1)
SERIAL_NUMBER_CONTROL,
— Lead Times
esiv.preprocessing_lead_time,
esiv.postprocessing_lead_time,
esiv.full_lead_time
processing_time,
— purchasing
esiv.PURCHASING_ITEM_FLAG,
esiv.PURCHASING_ENABLED_FLAG,
esiv.LIST_PRICE_PER_UNIT
FROM egp_system_items_vl esiv, inv_org_parameters iop
WHERE esiv.organization_id = iop.organization_id
— and item_number =’AS46334′
AND iop.organization_code = ‘003’
ORDER BY esiv.organization_id, item_number
Sample Report Output
