1 PACKAGE BODY HR_RU_ASG_HOOK AS
2 /* $Header: peruexar.pkb 120.1 2006/09/20 13:33:57 mgettins noship $ */
3
4 PROCEDURE validate_asg_upd_details(p_assignment_id IN NUMBER
5 ,p_effective_date IN DATE
6 ,p_datetrack_update_mode IN VARCHAR2
7 ,p_assignment_status_type_id IN NUMBER
8 ,p_segment1 IN VARCHAR2
9 ,p_segment2 IN VARCHAR2
10 ,p_segment3 IN VARCHAR2
11 ,p_segment4 IN VARCHAR2
12 ,p_segment5 IN VARCHAR2
13 ,p_segment6 IN VARCHAR2
14 ,p_segment7 IN VARCHAR2
15 ,p_segment8 IN VARCHAR2
16 ,p_segment9 IN VARCHAR2
17 ,p_segment10 IN VARCHAR2
18 ,p_segment11 IN VARCHAR2
19 ,p_segment12 IN VARCHAR2
20 ,p_segment13 IN VARCHAR2
21 ,p_segment14 IN VARCHAR2
22 ,p_segment15 IN VARCHAR2
23 ) AS
24 CURSOR c_min_start_date(p_assignment_id NUMBER) IS
25 SELECT min(effective_start_date)
26 FROM per_all_assignments_f
27 WHERE assignment_id = p_assignment_id;
28
29 CURSOR c_asg_details(p_assignment_id NUMBER,p_effective_date DATE) IS
30 SELECT person_id,effective_start_date,effective_end_date,business_group_id
31 FROM per_all_assignments_f
32 WHERE assignment_id = p_assignment_id
33 AND p_effective_date between effective_start_date and effective_end_date;
34 l_org_id NUMBER;
35 l_asg_status VARCHAR2(1);
36 l_cont_status VARCHAR2(1);
37 l_eff_start_date DATE;
38 l_eff_end_date DATE;
39 l_min_start_date DATE;
40 l_person_id NUMBER;
41 l_business_group_id NUMBER;
42 l_lookup_exists VARCHAR2(1);
43 BEGIN
44 --
45 -- Added for GSI Bug 5472781
46 --
47 IF hr_utility.chk_product_install('Oracle Human Resources', 'RU') THEN
48 --
49 OPEN c_asg_details(p_assignment_id,p_effective_date);
50 FETCH c_asg_details INTO l_person_id,l_eff_start_date,l_eff_end_date,l_business_group_id;
51 CLOSE c_asg_details;
52 OPEN c_min_start_date(p_assignment_id);
53 FETCH c_min_start_date INTO l_min_start_date;
54 CLOSE c_min_start_date;
55 IF p_datetrack_update_mode = 'UPDATE' THEN
56 l_eff_start_date := p_effective_date;
57 ELSIF p_datetrack_update_mode = 'UPDATE_CHANGE_INSERT' THEN
58 l_eff_start_date := p_effective_date;
59 ELSIF p_datetrack_update_mode = 'UPDATE_OVERRIDE' THEN
60 l_eff_start_date := p_effective_date;
61 l_eff_end_date := hr_general.end_of_time;
62 ELSE
63 l_eff_start_date := l_eff_start_date;
64 l_eff_end_date := l_eff_end_date;
65 END IF;
66 IF p_segment1 <> hr_api.g_varchar2 THEN
67 BEGIN
68 select distinct hou.organization_id
69 INTO l_org_id
70 from HR_ALL_ORGANIZATION_UNITS hou, HR_ALL_ORGANIZATION_UNITS_TL hout, HR_ORGANIZATION_INFORMATION hoi
71 where hou.organization_id = hoi.organization_id
72 AND hou.organization_id = hout.organization_id
73 AND hou.organization_id = to_number(p_segment1)
74 AND hoi.org_information_context = 'CLASS'
75 AND hoi.org_information1 = 'HR_LEGAL_EMPLOYER'
76 AND hoi.org_information2 = 'Y'
77 AND hout.language = userenv('LANG')
78 AND p_effective_date >= hou.date_from
79 AND p_effective_date <= NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
80 EXCEPTION
81 WHEN NO_DATA_FOUND THEN
82 hr_utility.set_message (800, 'HR_7209_API_LOOK_INVALID');
83 hr_utility.set_message_token ('ARGUMENT', hr_general.decode_lookup('RU_FORM_LABELS','EMPLOYER'));
84 hr_utility.raise_error;
85 END;
86 END IF;
87 IF p_segment2 <> hr_api.g_varchar2 OR p_segment2 IS NULL THEN
88 IF p_segment2 IS NOT NULL THEN
89 hr_ru_utility.check_lookup_value(hr_general.decode_lookup('RU_FORM_LABELS','EMP_TYPE'),
90 p_segment2,
91 'YES_NO',
92 p_effective_date);
93 END IF;
94 IF nvl(p_segment2,'N') = 'N' THEN
95 l_asg_status := hr_ru_utility.check_assign_category(l_eff_start_date
96 ,l_eff_end_date
97 ,p_assignment_id
98 ,l_person_id
99 ,l_business_group_id
100 );
101 IF l_asg_status = 'Y' THEN
102 hr_utility.set_message(800, 'HR_RU_INVALID_EMP_TYPE');
103 hr_utility.raise_error;
104 END IF;
105 END IF;
106 END IF;
107 IF p_segment3 <> hr_api.g_varchar2 THEN
108 l_cont_status := hr_ru_utility.check_contract_number_unique(p_segment3
109 ,p_assignment_id
110 ,l_business_group_id
111 );
112 IF l_cont_status = 'Y' THEN
113 hr_utility.set_message(800, 'HR_RU_INVALID_CONTRACT_NUMBER');
114 hr_utility.raise_error;
115 END IF;
116 END IF;
117 IF p_segment4 <> hr_api.g_varchar2 THEN
118 IF fnd_date.canonical_to_date(p_segment4) < l_min_start_date THEN
119 hr_utility.set_message(800, 'HR_RU_INVALID_ISSUE_DATE');
120 hr_utility.raise_error;
121 END IF;
122 END IF;
123 IF p_segment5 <> hr_api.g_varchar2 THEN
124 IF fnd_date.canonical_to_date(p_segment5) < l_min_start_date THEN
125 hr_utility.set_message(800, 'HR_RU_INVALID_END_DATE');
126 hr_utility.raise_error;
127 END IF;
128 END IF;
129 IF p_segment6 <> hr_api.g_varchar2 THEN
130 hr_ru_utility.check_lookup_value(hr_general.decode_lookup('RU_FORM_LABELS','CONT_LIMIT_REASON'),
131 p_segment6,
132 'RU_LIMITED_CONTRACT_REASON',
133 p_effective_date);
134 END IF;
135 IF p_assignment_status_type_id <> hr_api.g_number THEN
136 IF (p_assignment_status_type_id = 3 OR p_assignment_status_type_id = 8) AND
137 p_segment7 IS NULL THEN
138 hr_utility.set_message (800, 'HR_7209_API_LOOK_INVALID');
139 hr_utility.set_message_token ('ARGUMENT', hr_general.decode_lookup('RU_FORM_LABELS','TERM_REASON'));
140 hr_utility.raise_error;
141 END IF;
142 END IF;
143 IF p_segment7 <> hr_api.g_varchar2 THEN
144 hr_ru_utility.check_lookup_value(hr_general.decode_lookup('RU_FORM_LABELS','END_REASON'),
145 p_segment7,
146 'LEAV_REAS',
147 p_effective_date);
148 END IF;
149 IF p_segment8 <> hr_api.g_varchar2 THEN
150 BEGIN
151 SELECT '1' INTO l_lookup_exists FROM hr_lookups WHERE lookup_type='RU_SPECIAL_WORK_CONDITIONS'
152 AND lookup_code=p_segment8 AND enabled_flag='Y';
153 EXCEPTION
154 WHEN NO_DATA_FOUND THEN
155 hr_utility.set_message (800, 'HR_7209_API_LOOK_INVALID');
156 hr_utility.set_message_token ('ARGUMENT', hr_general.decode_lookup('RU_FORM_LABELS','SPECIAL_WORK_CONDITIONS'));
157 hr_utility.raise_error;
158 END;
159 END IF;
160 IF p_segment12 <> hr_api.g_varchar2 THEN
161 BEGIN
162 SELECT '1' INTO l_lookup_exists FROM hr_lookups WHERE lookup_type='RU_LONG_SERVICE'
163 AND lookup_code=p_segment12 AND enabled_flag='Y';
164 EXCEPTION
165 WHEN NO_DATA_FOUND THEN
166 hr_utility.set_message (800, 'HR_7209_API_LOOK_INVALID');
167 hr_utility.set_message_token ('ARGUMENT', hr_general.decode_lookup('RU_FORM_LABELS','LONG_SERVICE_REASON'));
168 hr_utility.raise_error;
169 END;
170 END IF;
171 IF p_segment9 <> hr_api.g_varchar2 THEN
172 hr_ru_utility.check_lookup_value(hr_general.decode_lookup('RU_FORM_LABELS','TERRITORY_CONDITIONS'),
173 p_segment9,
174 'RU_TERRITORY_CONDITIONS',
175 p_effective_date);
176 END IF;
177 IF p_segment10 <> hr_api.g_varchar2 THEN
178 hr_ru_utility.check_lookup_value(hr_general.decode_lookup('RU_FORM_LABELS','RECORD_SERVICE_REASON'),
179 p_segment10,
180 'RU_CALC_RECORD_SERVICE',
181 p_effective_date);
182 END IF;
183 IF p_segment14 <> hr_api.g_varchar2 THEN
184 hr_ru_utility.check_lookup_value(hr_general.decode_lookup('RU_FORM_LABELS','UNINTERRUPTED_SERVICE_RECORD'),
185 p_segment14,
186 'YES_NO',
187 p_effective_date);
188 END IF;
189 IF p_segment15 <> hr_api.g_varchar2 THEN
190 hr_ru_utility.check_lookup_value(hr_general.decode_lookup('RU_FORM_LABELS','TOTAL_SERVICE_RECORD'),
191 p_segment15,
192 'YES_NO',
193 p_effective_date);
194 END IF;
195 END IF;
196 END validate_asg_upd_details;
197
198 PROCEDURE validate_asg_create_details(p_person_id IN NUMBER
199 ,p_effective_date IN DATE
200 ,p_assignment_status_type_id IN NUMBER
201 ,p_scl_segment1 IN VARCHAR2
202 ,p_scl_segment2 IN VARCHAR2
203 ,p_scl_segment3 IN VARCHAR2
204 ,p_scl_segment4 IN VARCHAR2
205 ,p_scl_segment5 IN VARCHAR2
206 ,p_scl_segment6 IN VARCHAR2
207 ,p_scl_segment7 IN VARCHAR2
208 ,p_scl_segment8 IN VARCHAR2
209 ,p_scl_segment9 IN VARCHAR2
210 ,p_scl_segment10 IN VARCHAR2
211 ,p_scl_segment11 IN VARCHAR2
212 ,p_scl_segment12 IN VARCHAR2
213 ,p_scl_segment13 IN VARCHAR2
214 ,p_scl_segment14 IN VARCHAR2
215 ,p_scl_segment15 IN VARCHAR2
216 ) AS
217 CURSOR c_person_details(p_person_id NUMBER,p_effective_date DATE) IS
218 SELECT business_group_id
219 FROM per_all_people_f
220 WHERE person_id = p_person_id
221 AND p_effective_date between effective_start_date and effective_end_date;
222 l_org_id NUMBER;
223 l_asg_status VARCHAR2(1);
224 l_cont_status VARCHAR2(1);
225 l_eff_start_date DATE;
226 l_eff_end_date DATE;
227 l_person_id NUMBER;
228 l_business_group_id NUMBER;
229 l_lookup_exists VARCHAR2(1);
230 BEGIN
231 --
232 -- Added for GSI Bug 5472781
233 --
234 IF hr_utility.chk_product_install('Oracle Human Resources', 'RU') THEN
235 --
236 l_eff_start_date := p_effective_date;
237 l_eff_end_date := hr_general.end_of_time;
238 OPEN c_person_details(p_person_id,p_effective_date);
239 FETCH c_person_details INTO l_business_group_id;
240 CLOSE c_person_details;
241 IF p_scl_segment1 IS NOT NULL THEN
242 BEGIN
243 select distinct hou.organization_id
244 INTO l_org_id
245 from HR_ALL_ORGANIZATION_UNITS hou, HR_ALL_ORGANIZATION_UNITS_TL hout, HR_ORGANIZATION_INFORMATION hoi
246 where hou.organization_id = hoi.organization_id
247 AND hou.organization_id = hout.organization_id
248 AND hou.organization_id = to_number(p_scl_segment1)
249 AND hoi.org_information_context = 'CLASS'
250 AND hoi.org_information1 = 'HR_LEGAL_EMPLOYER'
251 AND hoi.org_information2 = 'Y'
252 AND hout.language = userenv('LANG')
253 AND p_effective_date >= hou.date_from
254 AND p_effective_date <= NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
255 EXCEPTION
256 WHEN NO_DATA_FOUND THEN
257 hr_utility.set_message (800, 'HR_7209_API_LOOK_INVALID');
258 hr_utility.set_message_token ('ARGUMENT', hr_general.decode_lookup('RU_FORM_LABELS','EMPLOYER'));
259 hr_utility.raise_error;
260 END;
261 END IF;
262 IF p_scl_segment2 IS NOT NULL THEN
263 hr_ru_utility.check_lookup_value(hr_general.decode_lookup('RU_FORM_LABELS','EMP_TYPE'),
264 p_scl_segment2,
265 'YES_NO',
266 p_effective_date);
267 END IF;
268 IF nvl(p_scl_segment2,'N') = 'N' THEN
269 l_asg_status := hr_ru_utility.check_assign_category(l_eff_start_date
270 ,l_eff_end_date
271 ,NULL
272 ,p_person_id
273 ,l_business_group_id
274 );
275 IF l_asg_status = 'Y' THEN
276 hr_utility.set_message(800, 'HR_RU_INVALID_EMP_TYPE');
277 hr_utility.raise_error;
278 END IF;
279 END IF;
280 IF p_scl_segment3 IS NOT NULL THEN
281 l_cont_status := hr_ru_utility.check_contract_number_unique(p_scl_segment3
282 ,NULL
283 ,l_business_group_id
284 );
285 IF l_cont_status = 'Y' THEN
286 hr_utility.set_message(800, 'HR_RU_INVALID_CONTRACT_NUMBER');
287 hr_utility.raise_error;
288 END IF;
289 END IF;
290 IF p_scl_segment4 IS NOT NULL THEN
291 IF fnd_date.canonical_to_date(p_scl_segment4) < p_effective_date THEN
292 hr_utility.set_message(800, 'HR_RU_INVALID_ISSUE_DATE');
293 hr_utility.raise_error;
294 END IF;
295 END IF;
296 IF p_scl_segment5 IS NOT NULL THEN
297 IF fnd_date.canonical_to_date(p_scl_segment5) < p_effective_date THEN
298 hr_utility.set_message(800, 'HR_RU_INVALID_END_DATE');
299 hr_utility.raise_error;
300 END IF;
301 END IF;
302 IF p_scl_segment6 IS NOT NULL THEN
303 hr_ru_utility.check_lookup_value(hr_general.decode_lookup('RU_FORM_LABELS','CONT_LIMIT_REASON'),
304 p_scl_segment6,
305 'RU_LIMITED_CONTRACT_REASON',
306 p_effective_date);
307 END IF;
308 IF (p_assignment_status_type_id = 3 OR p_assignment_status_type_id = 8) AND
309 p_scl_segment7 IS NULL THEN
310 hr_utility.set_message (800, 'HR_7209_API_LOOK_INVALID');
311 hr_utility.set_message_token ('ARGUMENT', hr_general.decode_lookup('RU_FORM_LABELS','TERM_REASON'));
312 hr_utility.raise_error;
313 END IF;
314 IF p_scl_segment7 IS NOT NULL THEN
315 hr_ru_utility.check_lookup_value(hr_general.decode_lookup('RU_FORM_LABELS','END_REASON'),
316 p_scl_segment7,
317 'LEAV_REAS',
318 p_effective_date);
319 END IF;
320 IF p_scl_segment8 <> hr_api.g_varchar2 THEN
321 BEGIN
322 SELECT '1' INTO l_lookup_exists FROM hr_lookups WHERE lookup_type='RU_SPECIAL_WORK_CONDITIONS'
323 AND lookup_code=p_scl_segment8 AND enabled_flag='Y';
324 EXCEPTION
325 WHEN NO_DATA_FOUND THEN
326 hr_utility.set_message (800, 'HR_7209_API_LOOK_INVALID');
327 hr_utility.set_message_token ('ARGUMENT', hr_general.decode_lookup('RU_FORM_LABELS','SPECIAL_WORK_CONDITIONS'));
328 hr_utility.raise_error;
329 END;
330 END IF;
331 IF p_scl_segment12 <> hr_api.g_varchar2 THEN
332 BEGIN
333 SELECT '1' INTO l_lookup_exists FROM hr_lookups WHERE lookup_type='RU_LONG_SERVICE'
334 AND lookup_code=p_scl_segment12 AND enabled_flag='Y';
335 EXCEPTION
336 WHEN NO_DATA_FOUND THEN
337 hr_utility.set_message (800, 'HR_7209_API_LOOK_INVALID');
338 hr_utility.set_message_token ('ARGUMENT', hr_general.decode_lookup('RU_FORM_LABELS','LONG_SERVICE_REASON'));
339 hr_utility.raise_error;
340 END;
341 END IF;
342 IF p_scl_segment9 <> hr_api.g_varchar2 THEN
343 hr_ru_utility.check_lookup_value(hr_general.decode_lookup('RU_FORM_LABELS','TERRITORY_CONDITIONS'),
344 p_scl_segment9,
345 'RU_TERRITORY_CONDITIONS',
346 p_effective_date);
347 END IF;
348 IF p_scl_segment10 <> hr_api.g_varchar2 THEN
349 hr_ru_utility.check_lookup_value(hr_general.decode_lookup('RU_FORM_LABELS','RECORD_SERVICE_REASON'),
350 p_scl_segment10,
351 'RU_CALC_RECORD_SERVICE',
352 p_effective_date);
353 END IF;
354 IF p_scl_segment14 <> hr_api.g_varchar2 THEN
355 hr_ru_utility.check_lookup_value(hr_general.decode_lookup('RU_FORM_LABELS','UNINTERRUPTED_SERVICE_RECORD'),
356 p_scl_segment14,
357 'YES_NO',
358 p_effective_date);
359 END IF;
360 IF p_scl_segment15 <> hr_api.g_varchar2 THEN
361 hr_ru_utility.check_lookup_value(hr_general.decode_lookup('RU_FORM_LABELS','TOTAL_SERVICE_RECORD'),
362 p_scl_segment15,
363 'YES_NO',
364 p_effective_date);
365 END IF;
366 END IF;
367 END validate_asg_create_details;
368 END hr_ru_asg_hook;
[Home] [Help]