VIEW: APPS.EGO_ALL_ATTR_BASE_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'