View: EGO_ALL_ATTR_BASE_V

Product:	EGO - Advanced Product Catalog
Description:	Each rov of this view displays all the information (values) associated with base and user-defined attributes for a given item.
Implementation/DBA Data:	APPS.EGO_ALL_ATTR_BASE_V

View Text

SELECT EXTENSION_ID AS EXTENSION_ID
, ORGANIZATION_ID AS ORGANIZATION_ID
, INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID
, REVISION_ID AS REVISION_ID
, DATA_LEVEL_ID AS DATA_LEVEL_ID
, PK1_VALUE AS PK1_VALUE
, PK2_VALUE AS PK2_VALUE
, PK3_VALUE AS PK3_VALUE
, PK4_VALUE AS PK4_VALUE
, PK5_VALUE AS PK5_VALUE
, AG.ATTR_GROUP_ID AS ATTRIBUTEGROUP_ID
, AGC.APPLICATION_ID AS APPLICATION_ID
, AG.ATTR_GROUP_NAME AS ATTRIBUTE_GROUP_NAME
, AGC.ATTR_ID AS ATTRIBUTE_ID
, AGC.ATTR_NAME AS ATTRIBUTE_NAME
, AGC.DATA_TYPE_CODE AS DATA_TYPE_CODE
, DECODE (AGC.DATA_TYPE_CODE
, 'C'
, DECODE (AGC.DATABASE_COLUMN
, 'C_EXT_ATTR1'
, UDA.C_EXT_ATTR1
, 'C_EXT_ATTR2'
, UDA.C_EXT_ATTR2
, 'C_EXT_ATTR3'
, UDA.C_EXT_ATTR3
, 'C_EXT_ATTR4'
, UDA.C_EXT_ATTR4
, 'C_EXT_ATTR5'
, UDA.C_EXT_ATTR5
, 'C_EXT_ATTR6'
, UDA.C_EXT_ATTR6
, 'C_EXT_ATTR7'
, UDA.C_EXT_ATTR7
, 'C_EXT_ATTR8'
, UDA.C_EXT_ATTR8
, 'C_EXT_ATTR9'
, UDA.C_EXT_ATTR9
, 'C_EXT_ATTR10'
, UDA.C_EXT_ATTR10
, 'C_EXT_ATTR11'
, UDA.C_EXT_ATTR11
, 'C_EXT_ATTR12'
, UDA.C_EXT_ATTR12
, 'C_EXT_ATTR13'
, UDA.C_EXT_ATTR13
, 'C_EXT_ATTR14'
, UDA.C_EXT_ATTR14
, 'C_EXT_ATTR15'
, UDA.C_EXT_ATTR15
, 'C_EXT_ATTR16'
, UDA.C_EXT_ATTR16
, 'C_EXT_ATTR17'
, UDA.C_EXT_ATTR17
, 'C_EXT_ATTR18'
, UDA.C_EXT_ATTR18
, 'C_EXT_ATTR19'
, UDA.C_EXT_ATTR19
, 'C_EXT_ATTR20'
, UDA.C_EXT_ATTR20
, 'C_EXT_ATTR21'
, UDA.C_EXT_ATTR21
, 'C_EXT_ATTR22'
, UDA.C_EXT_ATTR22
, 'C_EXT_ATTR23'
, UDA.C_EXT_ATTR23
, 'C_EXT_ATTR24'
, UDA.C_EXT_ATTR24
, 'C_EXT_ATTR25'
, UDA.C_EXT_ATTR25
, 'C_EXT_ATTR26'
, UDA.C_EXT_ATTR26
, 'C_EXT_ATTR27'
, UDA.C_EXT_ATTR27
, 'C_EXT_ATTR28'
, UDA.C_EXT_ATTR28
, 'C_EXT_ATTR29'
, UDA.C_EXT_ATTR29
, 'C_EXT_ATTR30'
, UDA.C_EXT_ATTR30
, 'C_EXT_ATTR31'
, UDA.C_EXT_ATTR31
, 'C_EXT_ATTR32'
, UDA.C_EXT_ATTR32
, 'C_EXT_ATTR33'
, UDA.C_EXT_ATTR33
, 'C_EXT_ATTR34'
, UDA.C_EXT_ATTR34
, 'C_EXT_ATTR35'
, UDA.C_EXT_ATTR35
, 'C_EXT_ATTR36'
, UDA.C_EXT_ATTR36
, 'C_EXT_ATTR37'
, UDA.C_EXT_ATTR37
, 'C_EXT_ATTR38'
, UDA.C_EXT_ATTR38
, 'C_EXT_ATTR39'
, UDA.C_EXT_ATTR39
, 'C_EXT_ATTR40'
, UDA.C_EXT_ATTR40 ) ) AS ATTRIBUTE_CHAR_VALUE
, (DECODE (AGC.DATA_TYPE_CODE
, 'N'
, DECODE (AGC.DATABASE_COLUMN
, 'N_EXT_ATTR1'
, UDA.N_EXT_ATTR1
, 'N_EXT_ATTR2'
, UDA.N_EXT_ATTR2
, 'N_EXT_ATTR3'
, UDA.N_EXT_ATTR3
, 'N_EXT_ATTR4'
, UDA.N_EXT_ATTR4
, 'N_EXT_ATTR5'
, UDA.N_EXT_ATTR5
, 'N_EXT_ATTR6'
, UDA.N_EXT_ATTR6
, 'N_EXT_ATTR7'
, UDA.N_EXT_ATTR7
, 'N_EXT_ATTR8'
, UDA.N_EXT_ATTR8
, 'N_EXT_ATTR9'
, UDA.N_EXT_ATTR9
, 'N_EXT_ATTR10'
, UDA.N_EXT_ATTR10
, 'N_EXT_ATTR11'
, UDA.N_EXT_ATTR11
, 'N_EXT_ATTR12'
, UDA.N_EXT_ATTR12
, 'N_EXT_ATTR13'
, UDA.N_EXT_ATTR13
, 'N_EXT_ATTR14'
, UDA.N_EXT_ATTR14
, 'N_EXT_ATTR15'
, UDA.N_EXT_ATTR15
, 'N_EXT_ATTR16'
, UDA.N_EXT_ATTR16
, 'N_EXT_ATTR17'
, UDA.N_EXT_ATTR17
, 'N_EXT_ATTR18'
, UDA.N_EXT_ATTR18
, 'N_EXT_ATTR19'
, UDA.N_EXT_ATTR19
, 'N_EXT_ATTR20'
, UDA.N_EXT_ATTR20 ) )/NVL( (SELECT CONVERSION_RATE
FROM MTL_UOM_CONVERSIONS UOMLIST
WHERE UOMLIST.UOM_CODE = DECODE (AGC.DATA_TYPE_CODE
, 'N'
, DECODE (AGC.DATABASE_COLUMN
, 'N_EXT_ATTR1'
, UDA.UOM_EXT_ATTR1
, 'N_EXT_ATTR2'
, UDA.UOM_EXT_ATTR2
, 'N_EXT_ATTR3'
, UDA.UOM_EXT_ATTR3
, 'N_EXT_ATTR4'
, UDA.UOM_EXT_ATTR4
, 'N_EXT_ATTR5'
, UDA.UOM_EXT_ATTR5
, 'N_EXT_ATTR6'
, UDA.UOM_EXT_ATTR6
, 'N_EXT_ATTR7'
, UDA.UOM_EXT_ATTR7
, 'N_EXT_ATTR8'
, UDA.UOM_EXT_ATTR8
, 'N_EXT_ATTR9'
, UDA.UOM_EXT_ATTR9
, 'N_EXT_ATTR10'
, UDA.UOM_EXT_ATTR10
, 'N_EXT_ATTR11'
, UDA.UOM_EXT_ATTR11
, 'N_EXT_ATTR12'
, UDA.UOM_EXT_ATTR12
, 'N_EXT_ATTR13'
, UDA.UOM_EXT_ATTR13
, 'N_EXT_ATTR14'
, UDA.UOM_EXT_ATTR14
, 'N_EXT_ATTR15'
, UDA.UOM_EXT_ATTR15
, 'N_EXT_ATTR16'
, UDA.UOM_EXT_ATTR16
, 'N_EXT_ATTR17'
, UDA.UOM_EXT_ATTR17
, 'N_EXT_ATTR18'
, UDA.UOM_EXT_ATTR18
, 'N_EXT_ATTR19'
, UDA.UOM_EXT_ATTR19
, 'N_EXT_ATTR20'
, UDA.UOM_EXT_ATTR20 ) )
AND INVENTORY_ITEM_ID = 0 )
, 1) ) AS ATTRIBUTE_NUMBER_VALUE
, DECODE (AGC.DATA_TYPE_CODE
, 'N'
, DECODE (AGC.DATABASE_COLUMN
, 'N_EXT_ATTR1'
, UDA.UOM_EXT_ATTR1
, 'N_EXT_ATTR2'
, UDA.UOM_EXT_ATTR2
, 'N_EXT_ATTR3'
, UDA.UOM_EXT_ATTR3
, 'N_EXT_ATTR4'
, UDA.UOM_EXT_ATTR4
, 'N_EXT_ATTR5'
, UDA.UOM_EXT_ATTR5
, 'N_EXT_ATTR6'
, UDA.UOM_EXT_ATTR6
, 'N_EXT_ATTR7'
, UDA.UOM_EXT_ATTR7
, 'N_EXT_ATTR8'
, UDA.UOM_EXT_ATTR8
, 'N_EXT_ATTR9'
, UDA.UOM_EXT_ATTR9
, 'N_EXT_ATTR10'
, UDA.UOM_EXT_ATTR10
, 'N_EXT_ATTR11'
, UDA.UOM_EXT_ATTR11
, 'N_EXT_ATTR12'
, UDA.UOM_EXT_ATTR12
, 'N_EXT_ATTR13'
, UDA.UOM_EXT_ATTR13
, 'N_EXT_ATTR14'
, UDA.UOM_EXT_ATTR14
, 'N_EXT_ATTR15'
, UDA.UOM_EXT_ATTR15
, 'N_EXT_ATTR16'
, UDA.UOM_EXT_ATTR16
, 'N_EXT_ATTR17'
, UDA.UOM_EXT_ATTR17
, 'N_EXT_ATTR18'
, UDA.UOM_EXT_ATTR18
, 'N_EXT_ATTR19'
, UDA.UOM_EXT_ATTR19
, 'N_EXT_ATTR20'
, UDA.UOM_EXT_ATTR20 ) ) AS ATTRIBUTE_UOM_VALUE
, TO_CHAR(DECODE(AGC.DATA_TYPE_CODE
, 'X'
, DECODE(AGC.DATABASE_COLUMN
, 'D_EXT_ATTR1'
, UDA.D_EXT_ATTR1
, 'D_EXT_ATTR2'
, UDA.D_EXT_ATTR2
, 'D_EXT_ATTR3'
, UDA.D_EXT_ATTR3
, 'D_EXT_ATTR4'
, UDA.D_EXT_ATTR4
, 'D_EXT_ATTR5'
, UDA.D_EXT_ATTR5
, 'D_EXT_ATTR6'
, UDA.D_EXT_ATTR6
, 'D_EXT_ATTR7'
, UDA.D_EXT_ATTR7
, 'D_EXT_ATTR8'
, UDA.D_EXT_ATTR8
, 'D_EXT_ATTR9'
, UDA.D_EXT_ATTR9
, 'D_EXT_ATTR10'
, UDA.D_EXT_ATTR10))
, 'MM/DD/YYYY') AS ATTRIBUTE_DATE_VALUE
, TO_CHAR(DECODE(AGC.DATA_TYPE_CODE
, 'Y'
, DECODE(AGC.DATABASE_COLUMN
, 'D_EXT_ATTR1'
, UDA.D_EXT_ATTR1
, 'D_EXT_ATTR2'
, UDA.D_EXT_ATTR2
, 'D_EXT_ATTR3'
, UDA.D_EXT_ATTR3
, 'D_EXT_ATTR4'
, UDA.D_EXT_ATTR4
, 'D_EXT_ATTR5'
, UDA.D_EXT_ATTR5
, 'D_EXT_ATTR6'
, UDA.D_EXT_ATTR6
, 'D_EXT_ATTR7'
, UDA.D_EXT_ATTR7
, 'D_EXT_ATTR8'
, UDA.D_EXT_ATTR8
, 'D_EXT_ATTR9'
, UDA.D_EXT_ATTR9
, 'D_EXT_ATTR10'
, UDA.D_EXT_ATTR10))
, 'MM/DD/YYYY HH24:MI:SS') AS ATTRIBUTE_DATETIME_VALUE
FROM EGO_ATTRS_V AGC
, EGO_ATTR_GROUPS_V AG
, EGO_MTL_SY_ITEMS_EXT_B UDA
WHERE UDA.ATTR_GROUP_ID = AG.ATTR_GROUP_ID
AND AGC.APPLICATION_ID = AG.APPLICATION_ID
AND AGC.ATTR_GROUP_TYPE = AG.ATTR_GROUP_TYPE
AND AGC.ATTR_GROUP_NAME = AG.ATTR_GROUP_NAME
AND AGC.DATA_TYPE_CODE != 'A'

Columns