1 PACKAGE BODY per_fr_people_leg_hook AS
2 /* $Header: pefrlhre.pkb 120.0.12000000.2 2007/02/28 10:20:14 spendhar ship $ */
3 --
4 g_package VARCHAR2(33) := 'per_fr_people_leg_hook.';
5 --
6 --
7 -- Service functions to return TRUE if the value passed has been changed.
8 --
9 FUNCTION val_changed(p_value IN NUMBER) RETURN BOOLEAN IS
10 BEGIN
11 RETURN (p_value IS NULL OR p_value <> hr_api.g_number);
12 END val_changed;
13 --
14 FUNCTION val_changed(p_value IN VARCHAR2) RETURN BOOLEAN IS
15 BEGIN
16 RETURN (p_value IS NULL OR p_value <> hr_api.g_varchar2);
17 END val_changed;
18 --
19 FUNCTION val_changed(p_value IN DATE) RETURN BOOLEAN IS
20 BEGIN
21 RETURN (p_value IS NULL OR p_value <> hr_api.g_date);
22 END val_changed;
23 --
24 PROCEDURE check_regn_entry_ins(p_region_of_birth VARCHAR2
25 ,p_country_of_birth VARCHAR2
26 ,p_per_information10 VARCHAR2
27 ,p_hire_date DATE
28 ) IS
29
30 l_proc VARCHAR2(200) := g_package||'check_regn_entry_ins';
31 l_dept hr_lookups.meaning%TYPE;
32 l_dept_code hr_lookups.lookup_code%TYPE;
33
34 CURSOR cur_dept IS
35 select hl.meaning, hl.lookup_code
36 from hr_lookups hl
37 where hl.lookup_type = 'FR_DEPARTMENT'
38 and p_hire_date
39 between nvl(hl.start_date_active,p_hire_date)
40 and nvl(hl.end_date_active,p_hire_date)
41 and hl.lookup_code = p_region_of_birth
42 and hl.enabled_flag = 'Y' order by hl.meaning;
43
44
45 BEGIN
46
47 --
48 /* Added for GSI Bug 5472781 */
49 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'FR') THEN
50 hr_utility.set_location('Leaving:'||l_proc , 10);
51 return;
52 END IF;
53 --
54 hr_utility.set_location('Entering:'|| l_proc, 10);
55
56 IF p_region_of_birth IS NOT NULL THEN
57 IF (p_country_of_birth <> 'FR') THEN
58 hr_utility.set_message(800,'PER_FR_REGION_NULL_INFO');
59 hr_utility.raise_error;
60 ELSE
61
62 OPEN cur_dept;
63 FETCH cur_dept INTO l_dept, l_dept_code;
64 IF cur_dept%NOTFOUND THEN
65 CLOSE cur_dept;
66 hr_utility.set_message(800,'PER_FR_REGION_INVALID_INFO');
67 hr_utility.raise_error;
68 END IF;
69 CLOSE cur_dept;
70 END IF;
71 END IF;
72
73 IF p_country_of_birth = 'FR'
74 AND p_per_information10 IS NOT NULL THEN
75 hr_utility.set_message(800,'PER_FR_DATE_ENTERED_INFO');
76 hr_utility.raise_error;
77
78 END IF;
79
80 hr_utility.set_location(' Leaving:'|| l_proc, 20);
81 END check_regn_entry_ins;
82
83
84
85 PROCEDURE check_regn_entry_upd(p_region_of_birth VARCHAR2
86 ,p_country_of_birth VARCHAR2
87 ,p_per_information10 VARCHAR2
88 ,p_effective_date DATE
89 ,p_person_id NUMBER
90 ) IS
91
92 l_proc VARCHAR2(200) := g_package||'check_regn_entry_upd';
93 l_dept hr_lookups.meaning%TYPE;
94 l_dept_code hr_lookups.lookup_code%TYPE;
95 l_old_region_of_birth per_all_people_f.region_of_birth%TYPE;
96 l_old_country_of_birth per_all_people_f.country_of_birth%TYPE;
97 l_old_information10 per_all_people_f.per_information10%TYPE;
98 l_use_region_of_birth per_all_people_f.region_of_birth%TYPE;
99 l_use_country_of_birth per_all_people_f.country_of_birth%TYPE;
100 l_use_information10 per_all_people_f.per_information10%TYPE;
101
102 CURSOR cur_dept IS
103 select hl.meaning, hl.lookup_code
104 from hr_lookups hl
105 where hl.lookup_type = 'FR_DEPARTMENT'
106 and p_effective_date
107 between nvl(hl.start_date_active,p_effective_date)
108 and nvl(hl.end_date_active,p_effective_date)
109 and hl.lookup_code = l_use_region_of_birth
110 and hl.enabled_flag = 'Y' order by hl.meaning;
111
112
113 BEGIN
114
115 --
116 /* Added for GSI Bug 5472781 */
117 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'FR') THEN
118 hr_utility.set_location('Leaving:'|| l_proc , 10);
119 return;
120 END IF;
121 --
122 hr_utility.set_location('Entering:'|| l_proc, 10);
123 hr_utility.trace('region:'|| p_region_of_birth);
124 hr_utility.trace('country:'|| p_country_of_birth);
125 hr_utility.trace('entered:'|| p_per_information10);
126 hr_utility.trace('date:'|| to_char(p_effective_date));
127 -- get the old values, or the updated new values - remove the default placeholders
128 select region_of_birth, country_of_birth, per_information10
129 into l_old_region_of_birth, l_old_country_of_birth, l_old_information10
130 from per_all_people_f
131 where person_id = p_person_id
132 and p_effective_Date between effective_start_date and effective_end_Date;
133 -- only use the old values if the new are not changing
134 IF val_changed(p_region_of_birth) THEN
135 l_use_region_of_birth := p_region_of_birth;
136 ELSE
137 l_use_region_of_birth := l_old_region_of_birth;
138 END IF;
139 --
140 IF val_changed(p_country_of_birth) THEN
141 l_use_country_of_birth := p_country_of_birth;
142 ELSE
143 l_use_country_of_birth := l_old_country_of_birth;
144 END IF;
145 --
146 IF val_changed(p_per_information10) THEN
147 l_use_information10 := p_per_information10;
148 ELSE
149 l_use_information10 := l_old_information10;
150 END IF;
151 -- validation of these fields is only necessary if one of them has changed
152 --
153 IF l_use_region_of_birth IS NOT NULL THEN
154 IF (nvl(l_use_country_of_birth, ' ' ) <> 'FR') THEN
155 --Error if region is set country must be France
156 hr_utility.set_message(800,'PER_FR_REGION_NULL_INFO');
157 hr_utility.raise_error;
158 ELSE
159 -- Validate the department of birth
160 OPEN cur_dept;
161 FETCH cur_dept INTO l_dept, l_dept_code;
162 IF cur_dept%NOTFOUND THEN
163 CLOSE cur_dept;
164 hr_utility.set_message(800,'PER_FR_REGION_INVALID_INFO');
165 hr_utility.raise_error;
166 END IF;
167 CLOSE cur_dept;
168 END IF;
169 END IF;
170
171 IF nvl(l_use_country_of_birth,' ') = 'FR' AND l_use_information10 IS NOT NULL THEN
172 --Error must not set both FR as country, and date first entered France
173 hr_utility.set_message(800,'PER_FR_DATE_ENTERED_INFO');
174 hr_utility.raise_error;
175 END IF;
176
177 hr_utility.set_location(' Leaving:'||l_proc, 20);
178 END check_regn_entry_upd;
179 --
180 END per_fr_people_leg_hook;
[Home] [Help]