[Home] [Help]
SELECT bgrT.name business_group_name ,DECODE(TRUNC((months_between(dt.today,peo.date_of_birth))/12),1,1,2,1,3,1,4,1,5,1,6,1,7, 1,8,1,9,1,10,1,11,1,12,1,13,1,14,1,15,1,16,1,17,1,18,1,19,1,0) age_less_than_20 ,DECODE(TRUNC((months_between(dt.today,peo.date_of_birth))/12) ,20,1,21,1,22,1,23,1,24,1,25,1,0) age_20_25 ,DECODE(TRUNC((months_between(dt.today,peo.date_of_birth))/12),26,1,27,1,28,1,29,1,30,1,0) age_26_30 ,DECODE(TRUNC((months_between(dt.today,peo.date_of_birth))/12),31,1,32,1,33,1,34,1,35,1,0) age_31_35 ,DECODE(TRUNC((months_between(dt.today,peo.date_of_birth))/12),36,1,37,1,38,1,39,1,40,1,0) age_36_40 ,DECODE(TRUNC((months_between(dt.today,peo.date_of_birth))/12),41,1,42,1,43,1,44,1,45,1,0) age_41_45 ,DECODE(TRUNC((months_between(dt.today,peo.date_of_birth))/12),46,1,47,1,48,1,49,1,50,1,0) age_46_50 ,DECODE(TRUNC((months_between(dt.today,peo.date_of_birth))/12),51,1,52,1,53,1,54,1,55,1,0) age_51_55 ,DECODE(TRUNC((months_between(dt.today,peo.date_of_birth))/12),56,1,57,1,58,1,59,1,60,1,0) age_56_60 ,DECODE(TRUNC((months_between(dt.today,peo.date_of_birth))/12),61,1,62,1,63,1,64,1,65,1,0) age_61_65 ,DECODE(TRUNC((months_between(dt.today,peo.date_of_birth))/12),66,1,67,1,68,1,69,1,70,1,0) age_66_70 ,DECODE(SIGN(months_between(dt.today, peo.date_of_birth) - (71*12)), 1, 1, 0, 1, 0) age_more_than_70 ,DECODE(peo.date_of_birth,null,1,0) age_unknown ,DECODE(peo.date_of_birth, null,0, TRUNC((months_between(dt.today,peo.date_of_birth))/12)) age_in_years ,peo.business_group_id business_group_id ,peo.person_id person_id FROM hr_all_organization_units_tl bgrT ,per_people_x peo ,(SELECT sysdate today FROM dual) dt WHERE peo.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND peo.business_group_id = NVL(hr_bis.get_sec_profile_bg_id,peo.business_group_id) WITH READ ONLY
SELECT BGRT.NAME BUSINESS_GROUP_NAME
, DECODE(TRUNC((MONTHS_BETWEEN(DT.TODAY
, PEO.DATE_OF_BIRTH))/12)
, 1
, 1
, 2
, 1
, 3
, 1
, 4
, 1
, 5
, 1
, 6
, 1
, 7
, 1
, 8
, 1
, 9
, 1
, 10
, 1
, 11
, 1
, 12
, 1
, 13
, 1
, 14
, 1
, 15
, 1
, 16
, 1
, 17
, 1
, 18
, 1
, 19
, 1
, 0) AGE_LESS_THAN_20
, DECODE(TRUNC((MONTHS_BETWEEN(DT.TODAY
, PEO.DATE_OF_BIRTH))/12)
, 20
, 1
, 21
, 1
, 22
, 1
, 23
, 1
, 24
, 1
, 25
, 1
, 0) AGE_20_25
, DECODE(TRUNC((MONTHS_BETWEEN(DT.TODAY
, PEO.DATE_OF_BIRTH))/12)
, 26
, 1
, 27
, 1
, 28
, 1
, 29
, 1
, 30
, 1
, 0) AGE_26_30
, DECODE(TRUNC((MONTHS_BETWEEN(DT.TODAY
, PEO.DATE_OF_BIRTH))/12)
, 31
, 1
, 32
, 1
, 33
, 1
, 34
, 1
, 35
, 1
, 0) AGE_31_35
, DECODE(TRUNC((MONTHS_BETWEEN(DT.TODAY
, PEO.DATE_OF_BIRTH))/12)
, 36
, 1
, 37
, 1
, 38
, 1
, 39
, 1
, 40
, 1
, 0) AGE_36_40
, DECODE(TRUNC((MONTHS_BETWEEN(DT.TODAY
, PEO.DATE_OF_BIRTH))/12)
, 41
, 1
, 42
, 1
, 43
, 1
, 44
, 1
, 45
, 1
, 0) AGE_41_45
, DECODE(TRUNC((MONTHS_BETWEEN(DT.TODAY
, PEO.DATE_OF_BIRTH))/12)
, 46
, 1
, 47
, 1
, 48
, 1
, 49
, 1
, 50
, 1
, 0) AGE_46_50
, DECODE(TRUNC((MONTHS_BETWEEN(DT.TODAY
, PEO.DATE_OF_BIRTH))/12)
, 51
, 1
, 52
, 1
, 53
, 1
, 54
, 1
, 55
, 1
, 0) AGE_51_55
, DECODE(TRUNC((MONTHS_BETWEEN(DT.TODAY
, PEO.DATE_OF_BIRTH))/12)
, 56
, 1
, 57
, 1
, 58
, 1
, 59
, 1
, 60
, 1
, 0) AGE_56_60
, DECODE(TRUNC((MONTHS_BETWEEN(DT.TODAY
, PEO.DATE_OF_BIRTH))/12)
, 61
, 1
, 62
, 1
, 63
, 1
, 64
, 1
, 65
, 1
, 0) AGE_61_65
, DECODE(TRUNC((MONTHS_BETWEEN(DT.TODAY
, PEO.DATE_OF_BIRTH))/12)
, 66
, 1
, 67
, 1
, 68
, 1
, 69
, 1
, 70
, 1
, 0) AGE_66_70
, DECODE(SIGN(MONTHS_BETWEEN(DT.TODAY
, PEO.DATE_OF_BIRTH) - (71*12))
, 1
, 1
, 0
, 1
, 0) AGE_MORE_THAN_70
, DECODE(PEO.DATE_OF_BIRTH
, NULL
, 1
, 0) AGE_UNKNOWN
, DECODE(PEO.DATE_OF_BIRTH
, NULL
, 0
, TRUNC((MONTHS_BETWEEN(DT.TODAY
, PEO.DATE_OF_BIRTH))/12)) AGE_IN_YEARS
, PEO.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PEO.PERSON_ID PERSON_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_PEOPLE_X PEO
, (SELECT SYSDATE TODAY
FROM DUAL) DT
WHERE PEO.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND PEO.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PEO.BUSINESS_GROUP_ID) WITH READ ONLY
|
|
|
|