VIEW: APPS.EGO_ITEMS_V

Source

View Text - Preformatted

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' )

View Text - HTML Formatted

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' )