View: EGO_ITEMS_V

Product:	EGO - Advanced Product Catalog
Description:	This view provides item attributes used for UCCNet product synchronization
Implementation/DBA Data:	APPS.EGO_ITEMS_V

View Text

SELECT MSI.INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID
, MSI.ORGANIZATION_ID AS ORGANIZATION_ID
, MSI.CONCATENATED_SEGMENTS AS CONCATENATED_SEGMENTS
, MP.MASTER_ORGANIZATION_ID AS MASTER_ORGANIZATION_ID
, MXR.CROSS_REFERENCE_TYPE AS CROSS_REFERENCE_TYPE
, MXR.CROSS_REFERENCE AS GTIN
, ( SELECT MXR_TL.DESCRIPTION
FROM MTL_CROSS_REFERENCES_TL MXR_TL
WHERE MXR_TL.CROSS_REFERENCE_ID = MXR.CROSS_REFERENCE_ID
AND MXR_TL.LANGUAGE = USERENV('LANG') ) AS DESCRIPTION
, MSI2.PRIMARY_UOM_CODE AS PRIMARY_UOM_CODE
, MUOM.DESCRIPTION AS PRIMARY_UOM_CODE_DESC
, MSI2.TRADE_ITEM_DESCRIPTOR AS TRADE_ITEM_DESCRIPTOR
, ( SELECT VAL.FLEX_VALUE_MEANING
FROM FND_FLEX_VALUE_SETS VS
, FND_FLEX_VALUES_VL VAL
WHERE VS.FLEX_VALUE_SET_NAME = 'TRADEITEMDESCVS'
AND VAL.FLEX_VALUE_SET_ID = VS.FLEX_VALUE_SET_ID
AND VAL.FLEX_VALUE = MSI2.TRADE_ITEM_DESCRIPTOR ) AS TRADE_ITEM_DESCRIPTOR_DESC
, ( CASE WHEN EXISTS ( SELECT NULL
FROM EGO_UCCNET_EVENTS UE
WHERE UE.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND UE.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
AND UE.EVENT_TYPE = 'PUBLICATION'
AND UE.EVENT_ACTION IN ('NEW_ITEM'
, 'INITIAL_LOAD'
, 'DATA_CHANGE'
, 'CORRECTION')
AND ( DISPOSITION_CODE IS NULL OR DISPOSITION_CODE <> 'FAILED' ) ) THEN 'Y' ELSE NULL END ) AS PUBLICATION_STATUS
, EGA.TOP_GTIN AS TOP_GTIN
, MSI.CUSTOMER_ORDER_ENABLED_FLAG AS CUSTOMER_ORDER_ENABLED_FLAG
, EGA.IS_TRADE_ITEM_A_CONSUMER_UNIT AS IS_TRADE_ITEM_A_CONSUMER_UNIT
, MSI2.UNIT_WEIGHT AS UNIT_WEIGHT
, EGA.GROSS_WEIGHT AS GROSS_WEIGHT
, MSI.ENG_ITEM_FLAG AS ENG_ITEM_FLAG
FROM MTL_SYSTEM_ITEMS_B_KFV MSI
, MTL_PARAMETERS MP
, MTL_SYSTEM_ITEMS_B MSI2
, MTL_CROSS_REFERENCES_B MXR
, EGO_ITEM_GTN_ATTRS_B EGA
, MTL_UNITS_OF_MEASURE_TL MUOM
WHERE MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSI2.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI2.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
AND MXR.INVENTORY_ITEM_ID(+) = MSI2.INVENTORY_ITEM_ID
AND MXR.ORGANIZATION_ID(+) IS NULL
AND MXR.UOM_CODE (+) = MSI2.PRIMARY_UOM_CODE
AND MXR.CROSS_REFERENCE_TYPE(+) = 'GTIN'
AND EGA.INVENTORY_ITEM_ID(+) = MSI2.INVENTORY_ITEM_ID
AND EGA.ORGANIZATION_ID(+) = MSI2.ORGANIZATION_ID
AND MUOM.UOM_CODE = MSI2.PRIMARY_UOM_CODE
AND MUOM.LANGUAGE = USERENV( 'LANG' )

Columns