VIEW: APPS.HRI_CL_AGE_BAND_X_V

Source

View Text - Preformatted

SELECT to_char(bnds.band_min_months) || '-' || NVL(to_char(bnds.band_max_months), 'NA_EDW') ID , RTRIM(DECODE(bnds.band_min_months, 0,'0 ', NULL) || DECODE(bnds.age_min_years, 0, NULL, 1, bnds.age_min_years || ' ' || hr_bis.bis_decode_lookup('FREQUENCY','Y') || ' ', bnds.age_min_years || ' ' || hr_bis.bis_decode_lookup('QUALIFYING_UNITS','Y') || ' ') || DECODE(bnds.age_min_months, 0, NULL, 1, bnds.age_min_months || ' ' || hr_bis.bis_decode_lookup('FREQUENCY','CM') || ' ', bnds.age_min_months || ' ' || hr_bis.bis_decode_lookup('QUALIFYING_UNITS','M') || ' ') || ' - ' || DECODE(bnds.age_max_years, TO_NUMBER(NULL),NULL, 0, NULL, 1, bnds.age_max_years || ' ' || hr_bis.bis_decode_lookup('FREQUENCY','Y') || ' ', bnds.age_max_years || ' ' || hr_general.decode_lookup('QUALIFYING_UNITS','Y') || ' ') || DECODE(bnds.age_max_months, TO_NUMBER(NULL),NULL, 0, NULL, 1, bnds.age_max_months || ' ' || hr_bis.bis_decode_lookup('FREQUENCY','CM') || ' ', bnds.age_max_months || ' ' || hr_bis.bis_decode_lookup('QUALIFYING_UNITS','M') || ' ')) value , hr_general.start_of_time date_from , hr_general.end_of_time date_to , bnds.band_min_months age_min , bnds.age_min_months age_min_months , bnds.age_min_years age_min_years , bnds.band_max_months age_max , bnds.age_max_months age_max_months , bnds.age_max_years age_max_years , bnds.last_update_date last_update_date FROM (SELECT FLOOR(agb.band_min_total_months/12) age_min_years , MOD(agb.band_min_total_months,12) age_min_months , FLOOR(agb.band_max_total_months/12) age_max_years , MOD(agb.band_max_total_months,12) age_max_months , agb.band_min_total_months band_min_months , agb.band_max_total_months band_max_months , ROUND(agb.band_min_total_months*365/12) band_min_days , ROUND(agb.band_max_total_months*365/12) band_max_days , agb.last_update_date last_update_date FROM hri_age_bands agb ) bnds WITH READ ONLY

View Text - HTML Formatted

SELECT TO_CHAR(BNDS.BAND_MIN_MONTHS) || '-' || NVL(TO_CHAR(BNDS.BAND_MAX_MONTHS)
, 'NA_EDW') ID
, RTRIM(DECODE(BNDS.BAND_MIN_MONTHS
, 0
, '0 '
, NULL) || DECODE(BNDS.AGE_MIN_YEARS
, 0
, NULL
, 1
, BNDS.AGE_MIN_YEARS || ' ' || HR_BIS.BIS_DECODE_LOOKUP('FREQUENCY'
, 'Y') || ' '
, BNDS.AGE_MIN_YEARS || ' ' || HR_BIS.BIS_DECODE_LOOKUP('QUALIFYING_UNITS'
, 'Y') || ' ') || DECODE(BNDS.AGE_MIN_MONTHS
, 0
, NULL
, 1
, BNDS.AGE_MIN_MONTHS || ' ' || HR_BIS.BIS_DECODE_LOOKUP('FREQUENCY'
, 'CM') || ' '
, BNDS.AGE_MIN_MONTHS || ' ' || HR_BIS.BIS_DECODE_LOOKUP('QUALIFYING_UNITS'
, 'M') || ' ') || ' - ' || DECODE(BNDS.AGE_MAX_YEARS
, TO_NUMBER(NULL)
, NULL
, 0
, NULL
, 1
, BNDS.AGE_MAX_YEARS || ' ' || HR_BIS.BIS_DECODE_LOOKUP('FREQUENCY'
, 'Y') || ' '
, BNDS.AGE_MAX_YEARS || ' ' || HR_GENERAL.DECODE_LOOKUP('QUALIFYING_UNITS'
, 'Y') || ' ') || DECODE(BNDS.AGE_MAX_MONTHS
, TO_NUMBER(NULL)
, NULL
, 0
, NULL
, 1
, BNDS.AGE_MAX_MONTHS || ' ' || HR_BIS.BIS_DECODE_LOOKUP('FREQUENCY'
, 'CM') || ' '
, BNDS.AGE_MAX_MONTHS || ' ' || HR_BIS.BIS_DECODE_LOOKUP('QUALIFYING_UNITS'
, 'M') || ' ')) VALUE
, HR_GENERAL.START_OF_TIME DATE_FROM
, HR_GENERAL.END_OF_TIME DATE_TO
, BNDS.BAND_MIN_MONTHS AGE_MIN
, BNDS.AGE_MIN_MONTHS AGE_MIN_MONTHS
, BNDS.AGE_MIN_YEARS AGE_MIN_YEARS
, BNDS.BAND_MAX_MONTHS AGE_MAX
, BNDS.AGE_MAX_MONTHS AGE_MAX_MONTHS
, BNDS.AGE_MAX_YEARS AGE_MAX_YEARS
, BNDS.LAST_UPDATE_DATE LAST_UPDATE_DATE
FROM (SELECT FLOOR(AGB.BAND_MIN_TOTAL_MONTHS/12) AGE_MIN_YEARS
, MOD(AGB.BAND_MIN_TOTAL_MONTHS
, 12) AGE_MIN_MONTHS
, FLOOR(AGB.BAND_MAX_TOTAL_MONTHS/12) AGE_MAX_YEARS
, MOD(AGB.BAND_MAX_TOTAL_MONTHS
, 12) AGE_MAX_MONTHS
, AGB.BAND_MIN_TOTAL_MONTHS BAND_MIN_MONTHS
, AGB.BAND_MAX_TOTAL_MONTHS BAND_MAX_MONTHS
, ROUND(AGB.BAND_MIN_TOTAL_MONTHS*365/12) BAND_MIN_DAYS
, ROUND(AGB.BAND_MAX_TOTAL_MONTHS*365/12) BAND_MAX_DAYS
, AGB.LAST_UPDATE_DATE LAST_UPDATE_DATE
FROM HRI_AGE_BANDS AGB ) BNDS WITH READ ONLY