[Home] [Help]
5: SELECT 'Y'
6: FROM dual
7: WHERE EXISTS (SELECT 'x'
8: FROM per_people_extra_info pei,
9: per_all_people_f ppf
10: WHERE ppf.business_group_id = p_bg_id
11: AND ppf.person_id = pei.person_id
12: AND pei.information_type = 'PER_SUCCESSION_PLANNING');
13: l_data_exist VARCHAR2(10);
34: FROM hr_lookups h
35: WHERE lookup_type = 'PER_SUCC_PLAN_POTENTIAL'
36: AND lookup_code IN (SELECT distinct pei_information1
37: FROM per_people_extra_info pei,
38: per_all_people_f ppf
39: WHERE pei.information_type = 'PER_SUCCESSION_PLANNING'
40: AND pei.person_id = ppf.person_id
41: AND trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
42: AND ppf.business_group_id = p_bg_id)
50: FROM hr_lookups h
51: WHERE lookup_type = 'PER_SUCC_PLAN_RISK_LEVEL'
52: AND lookup_code IN (SELECT distinct pei_information2
53: FROM per_people_extra_info pei,
54: per_all_people_f ppf
55: WHERE pei.information_type = 'PER_SUCCESSION_PLANNING'
56: AND pei.person_id = ppf.person_id
57: AND trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
58: AND ppf.business_group_id = p_bg_id)
98: ,pei_information3 --- key person
99: ,pei_information1 --- potential
100: ,pei_information2 --- risk of loss
101: FROM per_people_extra_info pei
102: ,per_all_people_f ppf
103: WHERE ppf.business_group_id = p_bg_id
104: AND trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
105: AND ppf.person_id = pei.person_id
106: AND pei.information_type = 'PER_SUCCESSION_PLANNING'