VIEW: APPS.HRI_CL_GEO_REGION_V

Source

View Text - Preformatted

SELECT DECODE(c.parent_territory_code||c.child_territory_code, NULL, lkp.lookup_code, c.child_territory_code, c.child_territory_code||'+'||lkp.lookup_code, c.parent_territory_code, c.parent_territory_code||'+'||lkp.lookup_code, c.parent_territory_code||'+'||c.child_territory_code||'+'||lkp.lookup_code) ID , lkp.meaning value , NVL(lkp.start_date_active, hr_general.start_of_time) start_date , NVL(lkp.end_date_active, hr_general.end_of_time) end_date , lkp.meaning ORDER_BY , lkp.lookup_code region_code , NVL(c.child_territory_code, 'NA_EDW') country_code , NVL(c.parent_territory_code, 'NA_EDW') area_code , lkp.enabled_flag valid_flag_code FROM bis_territory_hierarchies th , bis_territory_hierarchies c , fnd_lookups lkp WHERE lkp.lookup_type ='REGION' AND lkp.lookup_code = th.child_territory_code(+) AND c.child_territory_code (+) = th.parent_territory_code AND th.child_territory_type (+) = 'REGION' AND th.parent_territory_type (+) = 'COUNTRY' AND c.child_territory_type (+) = 'COUNTRY' AND c.parent_territory_type (+) = 'AREA' UNION ALL SELECT 'NA_EDW' ID , NULL value , hr_general.start_of_time start_date , hr_general.end_of_time end_date , ' ' ORDER_BY , 'NA_EDW' region_code , 'NA_EDW' country_code , 'NA_EDW' area_code , 'Y' valid_flag_code FROM dual WITH READ ONLY

View Text - HTML Formatted

SELECT DECODE(C.PARENT_TERRITORY_CODE||C.CHILD_TERRITORY_CODE
, NULL
, LKP.LOOKUP_CODE
, C.CHILD_TERRITORY_CODE
, C.CHILD_TERRITORY_CODE||'+'||LKP.LOOKUP_CODE
, C.PARENT_TERRITORY_CODE
, C.PARENT_TERRITORY_CODE||'+'||LKP.LOOKUP_CODE
, C.PARENT_TERRITORY_CODE||'+'||C.CHILD_TERRITORY_CODE||'+'||LKP.LOOKUP_CODE) ID
, LKP.MEANING VALUE
, NVL(LKP.START_DATE_ACTIVE
, HR_GENERAL.START_OF_TIME) START_DATE
, NVL(LKP.END_DATE_ACTIVE
, HR_GENERAL.END_OF_TIME) END_DATE
, LKP.MEANING ORDER_BY
, LKP.LOOKUP_CODE REGION_CODE
, NVL(C.CHILD_TERRITORY_CODE
, 'NA_EDW') COUNTRY_CODE
, NVL(C.PARENT_TERRITORY_CODE
, 'NA_EDW') AREA_CODE
, LKP.ENABLED_FLAG VALID_FLAG_CODE
FROM BIS_TERRITORY_HIERARCHIES TH
, BIS_TERRITORY_HIERARCHIES C
, FND_LOOKUPS LKP
WHERE LKP.LOOKUP_TYPE ='REGION'
AND LKP.LOOKUP_CODE = TH.CHILD_TERRITORY_CODE(+)
AND C.CHILD_TERRITORY_CODE (+) = TH.PARENT_TERRITORY_CODE
AND TH.CHILD_TERRITORY_TYPE (+) = 'REGION'
AND TH.PARENT_TERRITORY_TYPE (+) = 'COUNTRY'
AND C.CHILD_TERRITORY_TYPE (+) = 'COUNTRY'
AND C.PARENT_TERRITORY_TYPE (+) = 'AREA' UNION ALL SELECT 'NA_EDW' ID
, NULL VALUE
, HR_GENERAL.START_OF_TIME START_DATE
, HR_GENERAL.END_OF_TIME END_DATE
, ' ' ORDER_BY
, 'NA_EDW' REGION_CODE
, 'NA_EDW' COUNTRY_CODE
, 'NA_EDW' AREA_CODE
, 'Y' VALID_FLAG_CODE
FROM DUAL WITH READ ONLY