View: EDW_HR_COST_CENTER_ORGS_NA_V

View Text

SELECT UNITSTL.NAME ORGANIZATION_NAME
, CC.ORG_INFORMATION3 COMPANY_ID
, COMPNAME.DESCRIPTION COMPANY_NAME
, CC.ORG_INFORMATION5 COST_CENTER_ID
, CCNAME.DESCRIPTION COST_CENTER_NAME
, PBG.BUSINESS_GROUP_ID
, PBG.NAME BUSINESS_GROUP_NAME
FROM HR_ALL_ORGANIZATION_UNITS UNITS
, HR_ALL_ORGANIZATION_UNITS_TL UNITSTL
, HR_ORGANIZATION_INFORMATION CLASS
, HR_ORGANIZATION_INFORMATION CC
, PER_BUSINESS_GROUPS PBG
, FND_FLEX_VALUES_VL COMPNAME
, FND_FLEX_VALUES_VL CCNAME
WHERE UNITS.ORGANIZATION_ID = UNITSTL.ORGANIZATION_ID
AND UNITSTL.LANGUAGE = USERENV ('LANG')
AND UNITS.ORGANIZATION_ID = CLASS.ORGANIZATION_ID
AND CLASS.ORG_INFORMATION_CONTEXT = 'CLASS'
AND CLASS.ORG_INFORMATION1 = 'CC'
AND CLASS.ORGANIZATION_ID = CC.ORGANIZATION_ID
AND CC.ORG_INFORMATION_CONTEXT = 'COMPANY COST CENTER'
AND UNITS.BUSINESS_GROUP_ID = PBG.BUSINESS_GROUP_ID
AND CC.ORG_INFORMATION2 = COMPNAME.FLEX_VALUE_SET_ID
AND CC.ORG_INFORMATION3 = COMPNAME.FLEX_VALUE
AND CC.ORG_INFORMATION4 = CCNAME.FLEX_VALUE_SET_ID
AND CC.ORG_INFORMATION5 = CCNAME.FLEX_VALUE
AND NOT EXISTS ( SELECT 1
FROM HR_ORGANIZATION_INFORMATION ORGALIAS
WHERE CC.ORGANIZATION_ID = ORGALIAS.ORGANIZATION_ID
AND ORGALIAS.ORG_INFORMATION_CONTEXT = 'ORGANIZATION NAME ALIAS')

Columns