View: EDW_FACT_ATTRIBUTES_MD_V

Product:	BIS - Applications BIS
Description:	EDW_FACT_ATTRIBUTES_MD_V
Implementation/DBA Data:	APPS.EDW_FACT_ATTRIBUTES_MD_V

View Text

SELECT FACT.ELEMENTID FACT_ID
, FACT.NAME FACT_NAME
, DECODE((SELECT NAME
FROM CMPDATATYPE_V DATA
WHERE MEASURE.DOMAIN = DATA.ELEMENTID)
, 'NUMBER'
, 'MEASURE'
, NULL) ATTRIBUTE_TYPE
, MEASURE.ELEMENTID ATTRIBUTE_ID
, MEASURE.NAME ATTRIBUTE_NAME
, MEASURE.LONGNAME ATTRIBUTE_LONG_NAME
, DECODE(UK.NAME
, NULL
, NULL
, 'UK')
, UK.ELEMENTID UK_ID
, UK.NAME UK_NAME
FROM CMPWBCUBE_V FACT
, CMPWBCUBEATTRIBUTE_V MEASURE
, CMPUNIQUEKEY_V UK
, CMPWBITEMSETUSAGE_V UKEYCOL
WHERE FACT.ELEMENTID = MEASURE.OWNINGRELATION
AND UKEYCOL.ATTRIBUTE (+) = MEASURE.ELEMENTID
AND UK.ELEMENTID (+) = UKEYCOL.ITEMSET UNION SELECT FACT.ELEMENTID FACT_ID
, FACT.NAME FACT_NAME
, NULL ATTRIBUTE_TYPE
, UCOL.ELEMENTID ATTRIBUTE_ID
, UCOL.NAME ATTRIBUTE_NAME
, UCOL.LONGNAME ATTRIBUTE_LONG_NAME
, 'UK'
, UK.ELEMENTID UK_ID
, UK.NAME UK_NAME
FROM CMPWBCUBE_V FACT
, CMPUNIQUEKEY_V UK
, CMPWBITEMSETUSAGE_V UKEYCOL
, CMPRELATIONALDBITEM_V UCOL
WHERE FACT.ELEMENTID = UK.OWNINGRELATION
AND UK.ELEMENTID = UKEYCOL.ITEMSET
AND UKEYCOL.ATTRIBUTE = UCOL.ELEMENTID UNION SELECT FACT.ELEMENTID FACT_ID
, FACT.NAME FACT_NAME
, NULL ATTRIBUTE_TYPE
, FCOL.ELEMENTID ATTRIBUTE_ID
, FCOL.NAME ATTRIBUTE_NAME
, FCOL.LONGNAME ATTRIBUTE_LONG_NAME
, 'FK'
, FK.ELEMENTID FK_ID
, FK.NAME FK_NAME
FROM CMPWBCUBE_V FACT
, CMPFOREIGNKEY_V FK
, CMPWBITEMSETUSAGE_V FKEYCOL
, CMPRELATIONALDBITEM_V FCOL
WHERE FACT.ELEMENTID = FK.OWNINGRELATION
AND FK.ELEMENTID = FKEYCOL.ITEMSET
AND FKEYCOL.ATTRIBUTE = FCOL.ELEMENTID WITH READ ONLY

Columns