VIEW: APPS.HRI_CS_AGE_V

Source

View Text - Preformatted

SELECT months_between(tim.report_date,per.date_of_birth)/12 age_in_years , months_between(tim.report_date,per.date_of_birth) age_in_months , tim.report_date-per.date_of_birth age_in_days , bnd.band_min_value age_band_min_value , bnd.band_max_value age_band_max_value /* Bridge Ids */ , per.person_id person_id , bnd.band_min_value age_band_id , tim.report_date effective_start_date , tim.report_date effective_end_date , tim.report_date effective_date /* Other Information */ , per.date_of_birth date_of_birth , per.current_employee_flag emp_flag_code , per.current_npw_flag cwk_flag_code , per.current_applicant_flag apl_flag_code , per.business_group_id business_group_id FROM hri_time_bands bnd , hri_time_day tim , per_all_people_f per WHERE bnd.type = 'AGE' AND per.date_of_birth IS NOT NULL AND tim.report_date BETWEEN per.effective_start_date AND per.effective_end_date AND months_between(tim.start_date, per.date_of_birth) / 12 >= bnd.band_min_value AND ( months_between(tim.start_date, per.date_of_birth) / 12 < bnd.band_max_value OR bnd.band_max_value IS NULL ) UNION ALL SELECT to_number(NULL) age_in_years , to_number(NULL) age_in_months , to_number(NULL) age_in_days , to_number(NULL) age_band_min_value , to_number(NULL) age_band_max_value /* Bridge ids */ , per.person_id person_id , -1 age_band_id , tim.report_date effective_start_date , tim.report_date effective_end_date , tim.report_date effective_date /* Other Information */ , per.date_of_birth date_of_birth , per.current_employee_flag emp_flag_code , per.current_npw_flag cwk_flag_code , per.current_applicant_flag apl_flag_code , per.business_group_id busines_group_id FROM hri_time_day tim , per_all_people_f per WHERE per.date_of_birth IS NULL AND tim.report_date BETWEEN per.effective_start_date AND per.effective_end_date WITH READ ONLY

View Text - HTML Formatted

SELECT MONTHS_BETWEEN(TIM.REPORT_DATE
, PER.DATE_OF_BIRTH)/12 AGE_IN_YEARS
, MONTHS_BETWEEN(TIM.REPORT_DATE
, PER.DATE_OF_BIRTH) AGE_IN_MONTHS
, TIM.REPORT_DATE-PER.DATE_OF_BIRTH AGE_IN_DAYS
, BND.BAND_MIN_VALUE AGE_BAND_MIN_VALUE
, BND.BAND_MAX_VALUE AGE_BAND_MAX_VALUE /* BRIDGE IDS */
, PER.PERSON_ID PERSON_ID
, BND.BAND_MIN_VALUE AGE_BAND_ID
, TIM.REPORT_DATE EFFECTIVE_START_DATE
, TIM.REPORT_DATE EFFECTIVE_END_DATE
, TIM.REPORT_DATE EFFECTIVE_DATE /* OTHER INFORMATION */
, PER.DATE_OF_BIRTH DATE_OF_BIRTH
, PER.CURRENT_EMPLOYEE_FLAG EMP_FLAG_CODE
, PER.CURRENT_NPW_FLAG CWK_FLAG_CODE
, PER.CURRENT_APPLICANT_FLAG APL_FLAG_CODE
, PER.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
FROM HRI_TIME_BANDS BND
, HRI_TIME_DAY TIM
, PER_ALL_PEOPLE_F PER
WHERE BND.TYPE = 'AGE'
AND PER.DATE_OF_BIRTH IS NOT NULL
AND TIM.REPORT_DATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND MONTHS_BETWEEN(TIM.START_DATE
, PER.DATE_OF_BIRTH) / 12 >= BND.BAND_MIN_VALUE
AND ( MONTHS_BETWEEN(TIM.START_DATE
, PER.DATE_OF_BIRTH) / 12 < BND.BAND_MAX_VALUE OR BND.BAND_MAX_VALUE IS NULL ) UNION ALL SELECT TO_NUMBER(NULL) AGE_IN_YEARS
, TO_NUMBER(NULL) AGE_IN_MONTHS
, TO_NUMBER(NULL) AGE_IN_DAYS
, TO_NUMBER(NULL) AGE_BAND_MIN_VALUE
, TO_NUMBER(NULL) AGE_BAND_MAX_VALUE /* BRIDGE IDS */
, PER.PERSON_ID PERSON_ID
, -1 AGE_BAND_ID
, TIM.REPORT_DATE EFFECTIVE_START_DATE
, TIM.REPORT_DATE EFFECTIVE_END_DATE
, TIM.REPORT_DATE EFFECTIVE_DATE /* OTHER INFORMATION */
, PER.DATE_OF_BIRTH DATE_OF_BIRTH
, PER.CURRENT_EMPLOYEE_FLAG EMP_FLAG_CODE
, PER.CURRENT_NPW_FLAG CWK_FLAG_CODE
, PER.CURRENT_APPLICANT_FLAG APL_FLAG_CODE
, PER.BUSINESS_GROUP_ID BUSINES_GROUP_ID
FROM HRI_TIME_DAY TIM
, PER_ALL_PEOPLE_F PER
WHERE PER.DATE_OF_BIRTH IS NULL
AND TIM.REPORT_DATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE WITH READ ONLY