View: EDW_LEVEL_RELATIONS_MD_V

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

View Text

SELECT LVLREL.ELEMENTID REL_ID
, DIM.ELEMENTID DIM_ID
, DIM.NAME DIM_NAME
, HIER.ELEMENTID HIER_ID
, HIER.NAME HIER_NAME
, LVLREL.PARENTLEVEL PARENT_LVL_ID
, PLVL.NAME PARENT_LVL_NAME
, PLVL.PREFIX PARENT_LVL_PREFIX
, PTBL.ELEMENTID PARENT_LVLTBL_ID
, PLVL.NAME || '_LTC' PARENT_LVLTBL_NAME
, UK.ELEMENTID UK_ID
, UK.NAME UK_NAME
, LVLREL.CHILDLEVEL CHILD_LVL_ID
, CLVL.NAME CHILD_LVL_NAME
, CLVL.PREFIX CHILD_LVL_PREFIX
, CTBL.ELEMENTID CHILD_LVLTBL_ID
, CLVL.NAME || '_LTC' CHILD_LVLTBL_NAME
, FK.ELEMENTID FK_ID
, FK.NAME FK_NAME
FROM CMPWBDIMENSION_V DIM
, CMPITEMHIERARCHY_V HIER
, CMPLEVELRELATIONSHIP_V LVLREL
, CMPLEVEL_V PLVL
, EDW_TABLES_MD_V PTBL
, CMPUNIQUEKEY_V UK
, CMPLEVEL_V CLVL
, EDW_TABLES_MD_V CTBL
, CMPFOREIGNKEY_V FK
WHERE DIM.ELEMENTID = HIER.OWNDIMENSION
AND HIER.ELEMENTID = LVLREL.HIERARCHY
AND LVLREL.PARENTLEVEL = PLVL.ELEMENTID
AND PLVL.NAME || '_LTC' = PTBL.NAME (+)
AND PTBL.NAME IS NOT NULL
AND PTBL.ELEMENTID = UK.OWNINGRELATION (+)
AND LVLREL.CHILDLEVEL = CLVL.ELEMENTID
AND CLVL.NAME || '_LTC' = CTBL.NAME (+)
AND CTBL.NAME IS NOT NULL
AND CTBL.ELEMENTID = FK.OWNINGRELATION (+)
AND FK.UNIQUEKEY = UK.ELEMENTID /*
AND EXISTS( SELECT 1
FROM DUAL
WHERE FK.UNIQUEKEY = UK.ELEMENTID ) */ UNION ALL SELECT LVLREL.ELEMENTID REL_ID
, DIM.ELEMENTID DIM_ID
, DIM.NAME DIM_NAME
, HIER.ELEMENTID HIER_ID
, HIER.NAME HIER_NAME
, LVLREL.PARENTLEVEL PARENT_LVL_ID
, PLVL.NAME PARENT_LVL_NAME
, PLVL.PREFIX PARENT_LVL_PREFIX
, TO_NUMBER(NULL) PARENT_LVLTBL_ID
, NULL PARENT_LVLTBL_NAME
, TO_NUMBER(NULL) UK_ID
, NULL UK_NAME
, LVLREL.CHILDLEVEL CHILD_LVL_ID
, CLVL.NAME CHILD_LVL_NAME
, CLVL.PREFIX CHILD_LVL_PREFIX
, TO_NUMBER(NULL) CHILD_LVLTBL_ID
, NULL CHILD_LVLTBL_NAME
, TO_NUMBER(NULL) FK_ID
, NULL FK_NAME
FROM CMPWBDIMENSION_V DIM
, CMPITEMHIERARCHY_V HIER
, CMPLEVELRELATIONSHIP_V LVLREL
, CMPLEVEL_V PLVL
, EDW_TABLES_MD_V PTBL
, CMPLEVEL_V CLVL
, EDW_TABLES_MD_V CTBL
WHERE DIM.ELEMENTID = HIER.OWNDIMENSION
AND HIER.ELEMENTID = LVLREL.HIERARCHY
AND LVLREL.PARENTLEVEL = PLVL.ELEMENTID
AND PLVL.NAME || '_LTC' = PTBL.NAME (+)
AND PTBL.NAME IS NULL
AND LVLREL.CHILDLEVEL = CLVL.ELEMENTID
AND CLVL.NAME || '_LTC' = CTBL.NAME (+)
AND CTBL.NAME IS NULL WITH READ ONLY

Columns