VIEW: APPS.HRI_CS_LOW_X_V

Source

View Text - Preformatted

SELECT months_between(TRUNC(SYSDATE),per.original_date_of_hire)/12 length_of_work_years ,months_between(TRUNC(SYSDATE),per.original_date_of_hire) length_of_work_months ,trunc(sysdate) effective_date ,per.person_id person_id ,bnds.band_min_total_years || '-' || bnds.band_min_total_months || '-' || bnds.band_min_total_weeks || '-' || bnds.band_min_total_days || '-' || NVL(to_char(bnds.band_max_total_years), 'NA_EDW') || '-' || NVL(to_char(bnds.band_max_total_months),'NA_EDW') || '-' || NVL(to_char(bnds.band_max_total_weeks), 'NA_EDW') || '-' || NVL(to_char(bnds.band_max_total_days), 'NA_EDW') low_band_id , per.original_date_of_hire hire_date , per.business_group_id business_group_id , bnds.band_max_total_years low_length_max_year , bnds.band_max_total_months low_length_max_month , bnds.band_max_total_weeks low_length_max_week , bnds.band_max_total_days low_length_max_day , bnds.band_min_total_years low_length_min_year , bnds.band_min_total_months low_length_min_month , bnds.band_min_total_weeks low_length_min_week , bnds.band_min_total_days low_length_min_day FROM hri_service_bands bnds ,per_all_people_f per WHERE ROUND(MONTHS_BETWEEN(TRUNC(sysdate),per.original_date_of_hire)) BETWEEN bnds.band_min_total_months AND bnds.band_max_total_months WITH READ ONLY

View Text - HTML Formatted

SELECT MONTHS_BETWEEN(TRUNC(SYSDATE)
, PER.ORIGINAL_DATE_OF_HIRE)/12 LENGTH_OF_WORK_YEARS
, MONTHS_BETWEEN(TRUNC(SYSDATE)
, PER.ORIGINAL_DATE_OF_HIRE) LENGTH_OF_WORK_MONTHS
, TRUNC(SYSDATE) EFFECTIVE_DATE
, PER.PERSON_ID PERSON_ID
, BNDS.BAND_MIN_TOTAL_YEARS || '-' || BNDS.BAND_MIN_TOTAL_MONTHS || '-' || BNDS.BAND_MIN_TOTAL_WEEKS || '-' || BNDS.BAND_MIN_TOTAL_DAYS || '-' || NVL(TO_CHAR(BNDS.BAND_MAX_TOTAL_YEARS)
, 'NA_EDW') || '-' || NVL(TO_CHAR(BNDS.BAND_MAX_TOTAL_MONTHS)
, 'NA_EDW') || '-' || NVL(TO_CHAR(BNDS.BAND_MAX_TOTAL_WEEKS)
, 'NA_EDW') || '-' || NVL(TO_CHAR(BNDS.BAND_MAX_TOTAL_DAYS)
, 'NA_EDW') LOW_BAND_ID
, PER.ORIGINAL_DATE_OF_HIRE HIRE_DATE
, PER.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, BNDS.BAND_MAX_TOTAL_YEARS LOW_LENGTH_MAX_YEAR
, BNDS.BAND_MAX_TOTAL_MONTHS LOW_LENGTH_MAX_MONTH
, BNDS.BAND_MAX_TOTAL_WEEKS LOW_LENGTH_MAX_WEEK
, BNDS.BAND_MAX_TOTAL_DAYS LOW_LENGTH_MAX_DAY
, BNDS.BAND_MIN_TOTAL_YEARS LOW_LENGTH_MIN_YEAR
, BNDS.BAND_MIN_TOTAL_MONTHS LOW_LENGTH_MIN_MONTH
, BNDS.BAND_MIN_TOTAL_WEEKS LOW_LENGTH_MIN_WEEK
, BNDS.BAND_MIN_TOTAL_DAYS LOW_LENGTH_MIN_DAY
FROM HRI_SERVICE_BANDS BNDS
, PER_ALL_PEOPLE_F PER
WHERE ROUND(MONTHS_BETWEEN(TRUNC(SYSDATE)
, PER.ORIGINAL_DATE_OF_HIRE)) BETWEEN BNDS.BAND_MIN_TOTAL_MONTHS
AND BNDS.BAND_MAX_TOTAL_MONTHS WITH READ ONLY