VIEW: APPS.EDWBV_HR_AGE_AGE_BAND_LCV

Source

View Text - Preformatted

SELECT /* $Header: hrisvag.odf 120.0 2005/05/29 06:43:05 appldev noship $ */ to_char(bnds.band_min_months) || '-' || NVL(to_char(bnds.band_max_months), 'NA_EDW') || '-' || inst.instance_code age_band_pk ,inst.instance_code instance ,'ALL' all_fk ,RTRIM(DECODE(bnds.band_min_months, 0,'0 ', NULL) || DECODE(bnds.age_min_years, 0, NULL, 1, bnds.age_min_years || ' ' || hr_general.decode_lookup('FREQUENCY','Y') || ' ', bnds.age_min_years || ' ' || hr_general.decode_lookup('QUALIFYING_UNITS','Y') || ' ') || DECODE(bnds.age_min_months, 0, NULL, 1, bnds.age_min_months || ' ' || hr_general.decode_lookup('FREQUENCY','M') || ' ', bnds.age_min_months || ' ' || hr_general.decode_lookup('QUALIFYING_UNITS','M') || ' ') || ' - ' || DECODE(bnds.age_max_years, TO_NUMBER(NULL),NULL, 0, NULL, 1, bnds.age_max_years || ' ' || hr_general.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_general.decode_lookup('FREQUENCY','M') || ' ', bnds.age_max_months || ' ' || hr_general.decode_lookup('QUALIFYING_UNITS','M') || ' ')) name ,RTRIM(DECODE(bnds.band_min_months, 0,'0 ', NULL) || DECODE(bnds.age_min_years, 0, NULL, 1, bnds.age_min_years || ' ' || hr_general.decode_lookup('FREQUENCY','Y') || ' ', bnds.age_min_years || ' ' || hr_general.decode_lookup('QUALIFYING_UNITS','Y') || ' ') || DECODE(bnds.age_min_months, 0, NULL, 1, bnds.age_min_months || ' ' || hr_general.decode_lookup('FREQUENCY','M') || ' ', bnds.age_min_months || ' ' || hr_general.decode_lookup('QUALIFYING_UNITS','M') || ' ') || ' - ' || DECODE(bnds.age_max_years, TO_NUMBER(NULL),NULL, 0, NULL, 1, bnds.age_max_years || ' ' || hr_general.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_general.decode_lookup('FREQUENCY','M') || ' ', bnds.age_max_months || ' ' || hr_general.decode_lookup('QUALIFYING_UNITS','M') || ' ')) age_band_dp ,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 ,NVL(bnds.last_update_date, to_date('01-01-2000','DD-MM-YYYY')) last_update_date ,to_date('01-01-2000','DD-MM-YYYY') creation_date FROM edw_local_instance inst ,(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 ,agb.last_update_date last_update_date FROM hri_age_bands agb ) bnds

View Text - HTML Formatted

SELECT /* $HEADER: HRISVAG.ODF 120.0 2005/05/29 06:43:05 APPLDEV NOSHIP $ */ TO_CHAR(BNDS.BAND_MIN_MONTHS) || '-' || NVL(TO_CHAR(BNDS.BAND_MAX_MONTHS)
, 'NA_EDW') || '-' || INST.INSTANCE_CODE AGE_BAND_PK
, INST.INSTANCE_CODE INSTANCE
, 'ALL' ALL_FK
, RTRIM(DECODE(BNDS.BAND_MIN_MONTHS
, 0
, '0 '
, NULL) || DECODE(BNDS.AGE_MIN_YEARS
, 0
, NULL
, 1
, BNDS.AGE_MIN_YEARS || ' ' || HR_GENERAL.DECODE_LOOKUP('FREQUENCY'
, 'Y') || ' '
, BNDS.AGE_MIN_YEARS || ' ' || HR_GENERAL.DECODE_LOOKUP('QUALIFYING_UNITS'
, 'Y') || ' ') || DECODE(BNDS.AGE_MIN_MONTHS
, 0
, NULL
, 1
, BNDS.AGE_MIN_MONTHS || ' ' || HR_GENERAL.DECODE_LOOKUP('FREQUENCY'
, 'M') || ' '
, BNDS.AGE_MIN_MONTHS || ' ' || HR_GENERAL.DECODE_LOOKUP('QUALIFYING_UNITS'
, 'M') || ' ') || ' - ' || DECODE(BNDS.AGE_MAX_YEARS
, TO_NUMBER(NULL)
, NULL
, 0
, NULL
, 1
, BNDS.AGE_MAX_YEARS || ' ' || HR_GENERAL.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_GENERAL.DECODE_LOOKUP('FREQUENCY'
, 'M') || ' '
, BNDS.AGE_MAX_MONTHS || ' ' || HR_GENERAL.DECODE_LOOKUP('QUALIFYING_UNITS'
, 'M') || ' ')) NAME
, RTRIM(DECODE(BNDS.BAND_MIN_MONTHS
, 0
, '0 '
, NULL) || DECODE(BNDS.AGE_MIN_YEARS
, 0
, NULL
, 1
, BNDS.AGE_MIN_YEARS || ' ' || HR_GENERAL.DECODE_LOOKUP('FREQUENCY'
, 'Y') || ' '
, BNDS.AGE_MIN_YEARS || ' ' || HR_GENERAL.DECODE_LOOKUP('QUALIFYING_UNITS'
, 'Y') || ' ') || DECODE(BNDS.AGE_MIN_MONTHS
, 0
, NULL
, 1
, BNDS.AGE_MIN_MONTHS || ' ' || HR_GENERAL.DECODE_LOOKUP('FREQUENCY'
, 'M') || ' '
, BNDS.AGE_MIN_MONTHS || ' ' || HR_GENERAL.DECODE_LOOKUP('QUALIFYING_UNITS'
, 'M') || ' ') || ' - ' || DECODE(BNDS.AGE_MAX_YEARS
, TO_NUMBER(NULL)
, NULL
, 0
, NULL
, 1
, BNDS.AGE_MAX_YEARS || ' ' || HR_GENERAL.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_GENERAL.DECODE_LOOKUP('FREQUENCY'
, 'M') || ' '
, BNDS.AGE_MAX_MONTHS || ' ' || HR_GENERAL.DECODE_LOOKUP('QUALIFYING_UNITS'
, 'M') || ' ')) AGE_BAND_DP
, 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
, NVL(BNDS.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY')) LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY') CREATION_DATE
FROM EDW_LOCAL_INSTANCE INST
, (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
, AGB.LAST_UPDATE_DATE LAST_UPDATE_DATE
FROM HRI_AGE_BANDS AGB ) BNDS