[Home] [Help]
PACKAGE BODY: APPS.HR_H2PI_VALIDATE
Source
1 PACKAGE BODY hr_h2pi_validate AS
2 /* $Header: hrh2pivd.pkb 120.0 2005/05/31 00:42:19 appldev noship $ */
3
4 g_package VARCHAR2(33) := ' hr_h2pi_validate.';
5 MAPPING_ID_MISSING EXCEPTION;
6 PRAGMA EXCEPTION_INIT (MAPPING_ID_MISSING, -20010);
7 --
8 -- For bg and gre valdation
9 --
10 PROCEDURE validate_bg_and_gre(p_from_client_id VARCHAR2) IS
11
12 TYPE bg_and_gre_tab_type is TABLE OF NUMBER(15)
13 INDEX BY BINARY_INTEGER;
14
15 bg_and_gre_tab bg_and_gre_tab_type;
16
17 CURSOR csr_bg_and_gre IS
18 SELECT * FROM hr_h2pi_bg_and_gre
19 WHERE client_id = p_from_client_id;
20
21 CURSOR csr_pay_bg_and_gre IS
22 SELECT organization_id
23 FROM hr_h2pi_bg_and_gre_v
24 WHERE business_group_id = hr_h2pi_upload.g_to_business_group_id;
25
26 CURSOR csr_hr_data_removed (p_organization_id NUMBER) IS
27 SELECT organization_id
28 FROM hr_h2pi_bg_and_gre_v bg
29 WHERE bg.business_group_id = hr_h2pi_upload.g_to_business_group_Id
30 AND bg.organization_id = p_organization_id
31 AND NOT EXISTS (SELECT 'X'
32 FROM hr_h2pi_id_mapping him,
33 hr_h2pi_bg_and_gre bg1
34 WHERE him.to_id = bg.organization_id
35 AND bg1.organization_id = him.from_id
36 AND him.table_name = 'HR_ALL_ORGANIZATION_UNITS'
37 AND him.to_business_group_id = bg.business_group_id);
38
39 CURSOR csr_payroll_data_added(p_organization_id NUMBER) IS
40 SELECT organization_id
41 FROM hr_h2pi_bg_and_gre_v bg
42 WHERE bg.business_group_id = hr_h2pi_upload.g_to_business_group_id
43 AND bg.organization_id = p_organization_id
44 AND NOT EXISTS (SELECT 'X'
45 FROM hr_h2pi_id_mapping him
46 WHERE him.to_id = bg.organization_id
47 AND him.table_name = 'HR_ALL_ORGANIZATION_UNITS'
48 AND him.to_business_group_id =
49 hr_h2pi_upload.g_to_business_group_id);
50
51 l_ed_rec hr_h2pi_bg_and_gre_v%ROWTYPE;
52 l_organization_to_id hr_h2pi_bg_and_gre.organization_id%TYPe;
53 l_counter BINARY_INTEGER ;
54 l_organization_id hr_h2pi_bg_and_gre.organization_id%TYPE;
55 l_location_id hr_h2pi_bg_and_gre.location_id%TYPE;
56 l_record_ok BOOLEAN;
57
58 l_proc VARCHAR2(72) := g_package||'validate_bg_and_gre';
59
60 l_method_of_gen_emp_num per_business_groups.method_of_generation_emp_num%TYPE;
61 BEGIN
62 hr_utility.set_location('Entering:'|| l_proc, 10);
63 BEGIN
64 SELECT method_of_generation_emp_num
65 INTO l_method_of_gen_emp_num
66 FROM per_business_groups
67 WHERE business_group_id = hr_h2pi_upload.g_to_business_group_id;
68 IF l_method_of_gen_emp_num <> 'M' THEN
69 hr_h2pi_error.data_error(p_from_id => p_from_client_id,
70 p_table_name => 'HR_H2PI_BG_AND_GRE',
71 p_message_level => 'FATAL',
72 p_message_name => 'HR_289292_EMP_NUM_GEN_MANUAL');
73 END IF;
74 EXCEPTION
75 WHEN NO_DATA_FOUND THEN
76 NULL;
77 END;
78
79 l_counter := 0;
80
81 <<main_loop>>
82 FOR v_ud_rec IN csr_bg_and_gre LOOP
83 SAVEPOINT org_start;
84 hr_utility.set_location(l_proc, 20);
85 l_organization_to_id := hr_h2pi_map.get_to_id
86 (p_table_name => 'HR_ALL_ORGANIZATION_UNITS',
87 p_from_id => v_ud_rec.organization_id);
88
89 IF l_organization_to_id = -1 THEN
90 hr_utility.set_location(l_proc, 30);
91 hr_h2pi_error.data_error(p_from_id => v_ud_rec.organization_id,
92 p_table_name => 'HR_H2PI_BG_AND_GRE',
93 p_message_level => 'FATAL',
94 p_message_name => 'HR_289260_UD_DATA_ADDED');
95 ELSE
96 hr_utility.set_location(l_proc, 40);
97 BEGIN
98 SELECT DISTINCT *
99 INTO l_ed_rec
100 FROM hr_h2pi_bg_and_gre_v
101 WHERE organization_id = l_organization_to_id;
102
103 l_counter := l_counter + 1 ;
104 bg_and_gre_tab(l_counter) := l_organization_to_id;
105
106 l_location_id := hr_h2pi_map.get_to_id
107 (p_table_name => 'HR_LOCATIONS_ALL',
108 p_from_id => v_ud_rec.location_id,
109 p_report_error => TRUE);
110 IF l_ed_rec.date_from <> v_ud_rec.date_from OR
111 l_ed_rec.date_to <> v_ud_rec.date_to OR
112 NVL(l_ed_rec.location_id, -1) <> NVL(l_location_id, -1) THEN
113 hr_utility.set_location(l_proc, 50);
114 hr_h2pi_error.data_error(p_from_id => v_ud_rec.organization_id,
115 p_table_name => 'HR_H2PI_BG_AND_GRE',
116 p_message_level => 'FATAL',
117 p_message_name => 'HR_289237_DATA_MISMATCH');
118 END IF;
119
120 EXCEPTION
121 WHEN NO_DATA_FOUND THEN
122 hr_utility.set_location(l_proc, 60);
123 hr_h2pi_error.data_error(p_from_id => v_ud_rec.organization_id,
124 p_table_name => 'HR_H2PI_BG_AND_GRE',
125 p_message_level=> 'FATAL',
126 p_message_name => 'HR_289235_ED_DATA_REMOVED');
127 WHEN MAPPING_ID_MISSING THEN
128 hr_utility.set_location(l_proc, 70);
129 END;
130 END IF;
131 END LOOP main_loop;
132
133 BEGIN
134 <<outer_loop>>
135 hr_utility.set_location(l_proc, 80);
136 FOR csr_pay_bg_and_gre_rec in csr_pay_bg_and_gre LOOP
137 hr_utility.set_location(l_proc, 90);
138 l_record_ok := FALSE;
139
140 <<inner_loop>>
141 FOR j IN 1..l_counter LOOP
142 IF bg_and_gre_tab(j) = csr_pay_bg_and_gre_rec.organization_id THEN
143 l_record_ok := TRUE;
144 END IF;
145 END LOOP inner_loop;
146
147 IF NOT l_record_ok THEN
148 hr_utility.set_location(l_proc, 100);
149 OPEN csr_payroll_data_added(csr_pay_bg_and_gre_rec.organization_id);
150 FETCH csr_payroll_data_added INTO l_organization_id;
151
152 IF csr_payroll_data_added%FOUND then
153 hr_utility.set_location(l_proc, 110);
154
155 hr_h2pi_error.data_error(p_from_id => l_organization_id,
156 p_table_name => 'HR_H2PI_BG_AND_GRE',
157 p_message_level => 'FATAL',
158 p_message_name => 'HR_289259_ED_DATA_ADDED');
159 CLOSE csr_payroll_data_added;
160
161 ELSE
162
163 CLOSE csr_payroll_data_added;
164
165 OPEN csr_hr_data_removed(csr_pay_bg_and_gre_rec.organization_id);
166 FETCH csr_hr_data_removed into l_organization_id;
167 IF csr_hr_data_removed%FOUND then
168 hr_utility.set_location(l_proc, 120);
169 --HR Data removed
170 hr_h2pi_error.data_error(p_from_id => l_organization_id,
171 p_table_name => 'HR_H2PI_BG_AND_GRE',
172 p_message_level => 'FATAL',
173 p_message_name => 'HR_289236_UD_DATA_REMOVED');
174 END IF;
175 CLOSE csr_hr_data_removed;
176 END IF;
177 END IF;
178 END LOOP outer_loop;
179 hr_utility.set_location(l_proc, 130);
180 END ;
181 COMMIT;
182 hr_utility.set_location('Leaving:' || l_proc, 140);
183 END validate_bg_and_gre;
184
185 --
186 -- For Pay Basis validation
187 --
188
189 PROCEDURE validate_pay_basis(p_from_client_id VARCHAR2) IS
190
191 TYPE pay_bases_tab_type is TABLE OF NUMBER(15)
192 INDEX BY BINARY_INTEGER;
193
194 pay_bases_tab pay_bases_tab_type;
195
196 CURSOR csr_pay_bases IS
197 SELECT * FROM hr_h2pi_pay_bases
198 WHERE client_id = p_from_client_id;
199
200 CURSOR csr_pay_pay_bases IS
201 SELECT pay_basis_id
202 FROM hr_h2pi_pay_bases_v
203 WHERE business_group_id = hr_h2pi_upload.g_to_business_group_id;
204
205 CURSOR csr_hr_data_removed (p_pay_basis_id NUMBER) IS
206 SELECT pay_basis_id
207 FROM hr_h2pi_pay_bases_v pay
208 WHERE pay.business_group_id = hr_h2pi_upload.g_to_business_group_Id
209 AND pay.pay_basis_id = p_pay_basis_id
210 AND NOT EXISTS (SELECT 'X'
211 FROM hr_h2pi_id_mapping him,
212 hr_h2pi_pay_bases pay1
213 WHERE him.to_id = pay.pay_basis_id
214 AND pay1.pay_basis_id = him.from_id
215 AND him.table_name = 'PER_PAY_BASES'
216 AND him.to_business_group_id = pay.business_group_id);
217
218 CURSOR csr_payroll_data_added(p_pay_basis_id NUMBER) IS
219 SELECT pay_basis_id
220 FROM hr_h2pi_pay_bases_v pay
221 WHERE pay.business_group_id = hr_h2pi_upload.g_to_business_group_id
222 AND pay.pay_basis_id = p_pay_basis_id
223 AND NOT EXISTS (SELECT 'X'
224 FROM hr_h2pi_id_mapping him
225 WHERE him.to_id = pay.pay_basis_id
226 AND him.table_name = 'PER_PAY_BASES'
227 AND him.to_business_group_id =
228 hr_h2pi_upload.g_to_business_group_id);
229
230 l_ed_rec hr_h2pi_pay_bases_v%ROWTYPE;
231 l_pay_basis_to_id hr_h2pi_pay_bases.pay_basis_id%TYPe;
232 l_counter BINARY_INTEGER ;
233 l_pay_basis_id hr_h2pi_pay_bases.pay_basis_id%TYPE;
234 l_record_ok BOOLEAN;
235
236 l_proc VARCHAR2(72) := g_package||'validate_pay_basis';
237
238 BEGIN
239 hr_utility.set_location('Entering:'|| l_proc, 10);
240 l_counter := 0;
241
242 <<main_loop>>
243 FOR v_ud_rec IN csr_pay_bases LOOP
244 SAVEPOINT pay_basis_start;
245 hr_utility.set_location(l_proc, 20);
246 l_pay_basis_to_id := hr_h2pi_map.get_to_id
247 (p_table_name => 'PER_PAY_BASES',
248 p_from_id => v_ud_rec.pay_basis_id);
249
250 IF l_pay_basis_to_id = -1 THEN
251 hr_utility.set_location(l_proc, 30);
252 hr_h2pi_error.data_error(p_from_id => v_ud_rec.pay_basis_id,
253 p_table_name => 'HR_H2PI_PAY_BASES',
254 p_message_level => 'FATAL',
255 p_message_name => 'HR_289260_UD_DATA_ADDED');
256 ELSE
257 hr_utility.set_location(l_proc, 40);
258 BEGIN
259 SELECT *
260 INTO l_ed_rec
261 FROM hr_h2pi_pay_bases_v
262 WHERE pay_basis_id = l_pay_basis_to_id;
263
264 l_counter := l_counter + 1 ;
265 pay_bases_tab(l_counter) := l_pay_basis_to_id;
266
267 IF l_ed_rec.name <> v_ud_rec.name OR
268 l_ed_rec.pay_basis <> v_ud_rec.pay_basis THEN
269 hr_utility.set_location(l_proc, 50);
270 hr_h2pi_error.data_error(p_from_id => v_ud_rec.pay_basis_id,
271 p_table_name => 'HR_H2PI_PAY_BASES',
272 p_message_level => 'FATAL',
273 p_message_name => 'HR_289237_DATA_MISMATCH');
274 END IF;
275
276 EXCEPTION
277 WHEN NO_DATA_FOUND THEN
278 hr_utility.set_location(l_proc, 60);
279 hr_h2pi_error.data_error(p_from_id => v_ud_rec.pay_basis_id,
280 p_table_name => 'HR_H2PI_PAY_BASES',
281 p_message_level=> 'FATAL',
282 p_message_name => 'HR_289235_ED_DATA_REMOVED');
283 WHEN MAPPING_ID_MISSING THEN
284 hr_utility.set_location(l_proc, 70);
285 END;
286 END IF;
287 END LOOP main_loop;
288
289 BEGIN
290 <<outer_loop>>
291 FOR csr_pay_pay_bases_rec in csr_pay_pay_bases LOOP
292 hr_utility.set_location(l_proc, 80);
293 l_record_ok := FALSE;
294
295 <<inner_loop>>
296 FOR j IN 1..l_counter LOOP
297 IF pay_bases_tab(j) = csr_pay_pay_bases_rec.pay_basis_id THEN
298 l_record_ok := TRUE;
299 END IF;
300 END LOOP inner_loop;
301
302 IF NOT l_record_ok THEN
303 hr_utility.set_location(l_proc, 90);
304 OPEN csr_payroll_data_added(csr_pay_pay_bases_rec.pay_basis_id);
305 FETCH csr_payroll_data_added INTO l_pay_basis_id;
306
307 IF csr_payroll_data_added%FOUND then
308 -- Pay basis data added.
309 hr_utility.set_location(l_proc, 100);
310 hr_h2pi_error.data_error(p_from_id => l_pay_basis_id,
311 p_table_name => 'HR_H2PI_PAY_BASES',
312 p_message_level => 'FATAL',
313 p_message_name => 'HR_289259_ED_DATA_ADDED');
314 CLOSE csr_payroll_data_added;
315
316 ELSE
317
318 hr_utility.set_location(l_proc, 110);
319 CLOSE csr_payroll_data_added;
320
321 OPEN csr_hr_data_removed(csr_pay_pay_bases_rec.pay_basis_id);
322 FETCH csr_hr_data_removed into l_pay_basis_id;
323 IF csr_hr_data_removed%FOUND then
324 --HR Data removed
325 hr_utility.set_location(l_proc, 120);
326 hr_h2pi_error.data_error(p_from_id => l_pay_basis_id,
327 p_table_name => 'HR_H2PI_PAY_BASES',
328 p_message_level => 'FATAL',
329 p_message_name => 'HR_289236_UD_DATA_REMOVED');
330 END IF;
331 CLOSE csr_hr_data_removed;
332 END IF;
333 END IF;
334 END LOOP outer_loop;
335 END ;
336 COMMIT;
337 hr_utility.set_location('Leaving:' || l_proc, 130);
338 END validate_pay_basis;
339
340 --
341 -- For Payroll validation
342 --
343
344 PROCEDURE validate_payroll(p_from_client_id VARCHAR2) IS
345
346 TYPE payrolls_tab_type is TABLE OF NUMBER(15)
347 INDEX BY BINARY_INTEGER;
348
349 payrolls_tab payrolls_tab_type;
350
351 CURSOR csr_payrolls IS
352 SELECT * FROM hr_h2pi_payrolls
353 WHERE client_id = p_from_client_id;
354
355 CURSOR csr_pay_payrolls IS
356 SELECT payroll_id
357 FROM hr_h2pi_payrolls_v
358 WHERE business_group_id = hr_h2pi_upload.g_to_business_group_id;
359
360 CURSOR csr_hr_data_removed (p_payroll_id NUMBER) IS
361 SELECT payroll_id
362 FROM hr_h2pi_payrolls_v pay
363 WHERE pay.business_group_id = hr_h2pi_upload.g_to_business_group_Id
364 AND pay.payroll_id = p_payroll_id
365 AND NOT EXISTS (SELECT 'X'
366 FROM hr_h2pi_id_mapping him,
367 hr_h2pi_payrolls pay1
368 WHERE him.to_id = pay.payroll_id
369 AND pay1.payroll_id = him.from_id
370 AND him.table_name = 'PAY_ALL_PAYROLLS_F'
371 AND him.to_business_group_id = pay.business_group_id);
372
373 CURSOR csr_payroll_data_added(p_payroll_id NUMBER) IS
374 SELECT payroll_id
375 FROM hr_h2pi_payrolls_v pay
376 WHERE pay.business_group_id = hr_h2pi_upload.g_to_business_group_id
377 AND pay.payroll_id = p_payroll_id
378 AND NOT EXISTS (SELECT 'X'
379 FROM hr_h2pi_id_mapping him
380 WHERE him.to_id = pay.payroll_id
381 AND him.table_name = 'PAY_ALL_PAYROLLS_F'
382 AND him.to_business_group_id =
383 hr_h2pi_upload.g_to_business_group_id);
384
385 l_ed_rec hr_h2pi_payrolls_v%ROWTYPE;
386 l_payroll_to_id pay_all_payrolls_f.payroll_id%TYPe;
387 l_counter BINARY_INTEGER ;
388 l_payroll_id pay_all_payrolls_f.payroll_id%TYPE;
389 l_record_ok BOOLEAN;
390
391 l_proc VARCHAR2(72) := g_package||'validate_payroll';
392
393 BEGIN
394 hr_utility.set_location('Entering:' || l_proc, 10);
395 l_counter := 0;
396
397 <<main_loop>>
398 FOR v_ud_rec IN csr_payrolls LOOP
399 SAVEPOINT payroll_start;
400 hr_utility.set_location(l_proc, 20);
401 l_payroll_to_id := hr_h2pi_map.get_to_id
402 (p_table_name => 'PAY_ALL_PAYROLLS_F',
403 p_from_id => v_ud_rec.payroll_id);
404
405 IF l_payroll_to_id = -1 THEN
406 hr_utility.set_location(l_proc, 30);
407 hr_h2pi_error.data_error(p_from_id => v_ud_rec.payroll_id,
408 p_table_name => 'HR_H2PI_PAYROLLS',
409 p_message_level => 'FATAL',
410 p_message_name => 'HR_289260_UD_DATA_ADDED');
411 ELSE
412 BEGIN
413 hr_utility.set_location(l_proc, 40);
414 SELECT *
415 INTO l_ed_rec
416 FROM hr_h2pi_payrolls_v
417 WHERE payroll_id = l_payroll_to_id
418 AND effective_start_date = v_ud_rec.effective_start_date
419 AND effective_end_date = v_ud_rec.effective_end_date;
420
421 l_counter := l_counter + 1 ;
422 payrolls_tab(l_counter) := l_payroll_to_id;
423
424 IF l_ed_rec.payroll_name <> v_ud_rec.payroll_name OR
425 l_ed_rec.first_period_end_date <> v_ud_rec.first_period_end_date OR
426 l_ed_rec.number_of_years <> v_ud_rec.number_of_years OR
427 l_ed_rec.period_type <> v_ud_rec.period_type OR
428 l_ed_rec.effective_start_date <> v_ud_rec.effective_start_date OR
429 l_ed_rec.effective_end_date <> v_ud_rec.effective_end_date THEN
430 hr_utility.set_location(l_proc, 50);
431 hr_h2pi_error.data_error(p_from_id => v_ud_rec.payroll_id,
432 p_table_name => 'HR_H2PI_PAYROLLS',
433 p_message_level => 'FATAL',
434 p_message_name => 'HR_289237_DATA_MISMATCH');
435 END IF;
436
437 EXCEPTION
438 WHEN NO_DATA_FOUND THEN
439 hr_utility.set_location(l_proc, 60);
440 hr_h2pi_error.data_error(p_from_id => v_ud_rec.payroll_id,
441 p_table_name => 'HR_H2PI_PAYROLLS',
442 p_message_level=> 'FATAL',
443 p_message_name => 'HR_289235_ED_DATA_REMOVED');
444 WHEN MAPPING_ID_MISSING THEN
445 hr_utility.set_location(l_proc, 70);
446 END;
447 END IF;
448 END LOOP main_loop;
449
450 BEGIN
451 <<outer_loop>>
452 FOR csr_pay_payrolls_rec in csr_pay_payrolls LOOP
453 hr_utility.set_location(l_proc, 80);
454 l_record_ok := FALSE;
455
456 <<inner_loop>>
457 FOR j IN 1..l_counter LOOP
458 IF payrolls_tab(j) = csr_pay_payrolls_rec.payroll_id THEN
459 l_record_ok := TRUE;
460 END IF;
461 END LOOP inner_loop;
462
463 IF NOT l_record_ok THEN
464 hr_utility.set_location(l_proc, 90);
465 OPEN csr_payroll_data_added(csr_pay_payrolls_rec.payroll_id);
466 FETCH csr_payroll_data_added INTO l_payroll_id;
467
468 IF csr_payroll_data_added%FOUND then
469 -- Payroll Data added.
470 hr_utility.set_location(l_proc, 100);
471 hr_h2pi_error.data_error(p_from_id => l_payroll_id,
472 p_table_name => 'HR_H2PI_PAYROLLS',
473 p_message_level => 'FATAL',
474 p_message_name => 'HR_289259_ED_DATA_ADDED');
475 CLOSE csr_payroll_data_added;
476
477 ELSE
478
479 hr_utility.set_location(l_proc, 110);
480 CLOSE csr_payroll_data_added;
481
482 OPEN csr_hr_data_removed(csr_pay_payrolls_rec.payroll_id);
483 FETCH csr_hr_data_removed into l_payroll_id;
484 IF csr_hr_data_removed%FOUND then
485 --HR Data removed
486 hr_utility.set_location(l_proc, 120);
487 hr_h2pi_error.data_error(p_from_id => l_payroll_id,
488 p_table_name => 'HR_H2PI_PAYROLLS',
489 p_message_level => 'FATAL',
490 p_message_name => 'HR_289236_UD_DATA_REMOVED');
491 END IF;
492 CLOSE csr_hr_data_removed;
493 END IF;
494 END IF;
495 END LOOP outer_loop;
496 END ;
497 COMMIT;
498 hr_utility.set_location('Leaving:' || l_proc, 130);
499 END validate_payroll;
500
501 --
502 -- For Element Type validation
503 --
504
505 PROCEDURE validate_element_type(p_from_client_id VARCHAR2) IS
506
507 TYPE element_type_tab_type is TABLE OF NUMBER(15)
508 INDEX BY BINARY_INTEGER;
509
510 element_type_tab element_type_tab_type;
511
512 CURSOR csr_element_type IS
513 SELECT * FROM hr_h2pi_element_types
514 WHERE client_id = p_from_client_id
515 AND legislation_code IS NULL;
516
517 CURSOR csr_pay_element_type IS
518 SELECT element_type_id
519 FROM hr_h2pi_element_types_v
520 WHERE business_group_id = hr_h2pi_upload.g_to_business_group_id;
521
522 CURSOR csr_hr_data_removed (p_element_type_id NUMBER) IS
523 SELECT element_type_id
524 FROM hr_h2pi_element_types_v ele
525 WHERE ele.business_group_id = hr_h2pi_upload.g_to_business_group_Id
526 AND ele.element_type_id = p_element_type_id
527 AND NOT EXISTS (SELECT 'X'
528 FROM hr_h2pi_id_mapping him,
529 hr_h2pi_element_types ele1
530 WHERE him.to_id = ele.element_type_id
531 AND ele1.element_type_id = him.from_id
532 AND him.table_name = 'PAY_ELEMENT_TYPES_F'
533 AND him.to_business_group_id = ele.business_group_id);
534
535 CURSOR csr_payroll_data_added(p_element_type_id NUMBER) IS
536 SELECT element_type_id
537 FROM hr_h2pi_element_types_v ele
538 WHERE ele.business_group_id = hr_h2pi_upload.g_to_business_group_id
539 AND ele.element_type_id = p_element_type_id
540 AND NOT EXISTS (SELECT 'X'
541 FROM hr_h2pi_id_mapping him
542 WHERE him.to_id = ele.element_type_id
543 AND him.table_name = 'PAY_ELEMENT_TYPES_F'
544 AND him.to_business_group_id =
545 hr_h2pi_upload.g_to_business_group_id);
546
547 l_ed_rec hr_h2pi_element_types_v%ROWTYPE;
548 l_element_type_to_id hr_h2pi_element_types.element_type_id%TYPe;
549 l_counter BINARY_INTEGER ;
550 l_element_type_id hr_h2pi_element_types.element_type_id%TYPE;
551 l_record_ok BOOLEAN;
552
553 l_proc VARCHAR2(72) := g_package||'validate_element_type';
554
555 BEGIN
556 hr_utility.set_location('Entering:' || l_proc, 10);
557 l_counter := 0;
558
559 <<main_loop>>
560 FOR v_ud_rec IN csr_element_type LOOP
561 SAVEPOINT element_type_start;
562 hr_utility.set_location(l_proc, 20);
563 l_element_type_to_id := hr_h2pi_map.get_to_id
564 (p_table_name => 'PAY_ELEMENT_TYPES_F',
565 p_from_id => v_ud_rec.element_type_id);
566
567 IF l_element_type_to_id = -1 THEN
568 hr_utility.set_location(l_proc, 30);
569 hr_h2pi_error.data_error(p_from_id => v_ud_rec.element_type_id,
570 p_table_name => 'HR_H2PI_ELEMENT_TYPES',
571 p_message_level => 'FATAL',
572 p_message_name => 'HR_289260_UD_DATA_ADDED');
573 ELSE
574 BEGIN
575 hr_utility.set_location(l_proc, 40);
576 SELECT *
577 INTO l_ed_rec
578 FROM hr_h2pi_element_types_v
579 WHERE element_type_id = l_element_type_to_id
580 AND effective_start_date = v_ud_rec.effective_start_date
581 AND effective_end_date = v_ud_rec.effective_end_date;
582
583 l_counter := l_counter + 1 ;
584 element_type_tab(l_counter) := l_element_type_to_id;
585
586 IF l_ed_rec.element_name <> v_ud_rec.element_name OR
587 l_ed_rec.processing_type <> v_ud_rec.processing_type OR
588 l_ed_rec.effective_start_date <> v_ud_rec.effective_start_date OR
589 l_ed_rec.effective_end_date <> v_ud_rec.effective_end_date THEN
590 hr_utility.set_location(l_proc, 50);
591 hr_h2pi_error.data_error(p_from_id => v_ud_rec.element_type_id,
592 p_table_name => 'HR_H2PI_ELEMENT_TYPES',
593 p_message_level => 'FATAL',
594 p_message_name => 'HR_289237_DATA_MISMATCH');
595 END IF;
596
597 EXCEPTION
598 WHEN NO_DATA_FOUND THEN
599 hr_utility.set_location(l_proc, 60);
600 hr_h2pi_error.data_error(p_from_id => v_ud_rec.element_type_id,
601 p_table_name => 'HR_H2PI_ELEMENT_TYPES',
602 p_message_level=> 'FATAL',
603 p_message_name => 'HR_289235_ED_DATA_REMOVED');
604 WHEN MAPPING_ID_MISSING THEN
605 hr_utility.set_location(l_proc, 70);
606 END;
607 END IF;
608 END LOOP main_loop;
609
610 BEGIN
611 <<outer_loop>>
612 FOR csr_pay_element_type_rec in csr_pay_element_type LOOP
613 hr_utility.set_location(l_proc, 80);
614 l_record_ok := FALSE;
615
616 <<inner_loop>>
617 FOR j IN 1..l_counter LOOP
618 IF element_type_tab(j) = csr_pay_element_type_rec.element_type_id THEN
619 l_record_ok := TRUE;
620 END IF;
621 END LOOP inner_loop;
622
623 IF NOT l_record_ok THEN
624 hr_utility.set_location(l_proc, 90);
625 OPEN csr_payroll_data_added(csr_pay_element_type_rec.element_type_id);
626 FETCH csr_payroll_data_added INTO l_element_type_id;
627
628 IF csr_payroll_data_added%FOUND then
629
630 hr_utility.set_location(l_proc, 100);
631 hr_h2pi_error.data_error(p_from_id => l_element_type_id,
632 p_table_name => 'HR_H2PI_ELEMENT_TYPES',
633 p_message_level => 'FATAL',
634 p_message_name => 'HR_289259_ED_DATA_ADDED');
635 CLOSE csr_payroll_data_added;
636
637 ELSE
638
639 hr_utility.set_location(l_proc, 110);
640 CLOSE csr_payroll_data_added;
641
642 OPEN csr_hr_data_removed(csr_pay_element_type_rec.element_type_id);
643 FETCH csr_hr_data_removed into l_element_type_id;
644 IF csr_hr_data_removed%FOUND then
645 hr_utility.set_location(l_proc, 130);
646 --HR Data removed
647 hr_h2pi_error.data_error(p_from_id => l_element_type_id,
648 p_table_name => 'HR_H2PI_ELEMENT_TYPES',
649 p_message_level => 'FATAL',
650 p_message_name => 'HR_289236_UD_DATA_REMOVED');
651 END IF;
652 CLOSE csr_hr_data_removed;
653 END IF;
654 END IF;
655 END LOOP outer_loop;
656 END ;
657 COMMIT;
658 hr_utility.set_location('Leaving:' || l_proc, 130);
659 END validate_element_type;
660
661 --
662 -- For org payment method validation
663 --
664 PROCEDURE validate_org_payment_method(p_from_client_id VARCHAR2) IS
665
666 TYPE org_payment_method_tab_type is TABLE OF NUMBER(15)
667 INDEX BY BINARY_INTEGER;
668
669 org_payment_method_tab org_payment_method_tab_type;
670
671 CURSOR csr_org_payment_method IS
672 SELECT * FROM hr_h2pi_org_payment_methods
673 WHERE client_id = p_from_client_id;
674
675 CURSOR csr_pay_org_payment_method IS
676 SELECT org_payment_method_id
677 FROM hr_h2pi_org_payment_methods_v
678 WHERE business_group_id = hr_h2pi_upload.g_to_business_group_id;
679
680 CURSOR csr_hr_data_removed (p_org_payment_method_id NUMBER) IS
681 SELECT org_payment_method_id
682 FROM hr_h2pi_org_payment_methods_v ele
683 WHERE ele.business_group_id = hr_h2pi_upload.g_to_business_group_Id
684 AND ele.org_payment_method_id = p_org_payment_method_id
685 AND NOT EXISTS (SELECT 'X'
686 FROM hr_h2pi_id_mapping him,
687 hr_h2pi_org_payment_methods ele1
688 WHERE him.to_id = ele.org_payment_method_id
689 AND ele1.org_payment_method_id = him.from_id
690 AND him.table_name = 'PAY_ORG_PAYMENT_METHODS_F'
691 AND him.to_business_group_id = ele.business_group_id);
692
693 CURSOR csr_payroll_data_added(p_org_payment_method_id NUMBER) IS
694 SELECT org_payment_method_id
695 FROM hr_h2pi_org_payment_methods_v ele
696 WHERE ele.business_group_id = hr_h2pi_upload.g_to_business_group_id
697 AND ele.org_payment_method_id = p_org_payment_method_id
698 AND NOT EXISTS (SELECT 'X'
699 FROM hr_h2pi_id_mapping him
700 WHERE him.to_id = ele.org_payment_method_id
701 AND him.table_name = 'PAY_ORG_PAYMENT_METHODS_F'
702 AND him.to_business_group_id =
703 hr_h2pi_upload.g_to_business_group_id);
704
705 l_ed_rec hr_h2pi_org_payment_methods_v%ROWTYPE;
706 l_org_payment_method_to_id
707 hr_h2pi_org_payment_methods.org_payment_method_id%TYPe;
708 l_counter BINARY_INTEGER ;
709 l_org_payment_method_id
710 hr_h2pi_org_payment_methods.org_payment_method_id%TYPE;
711 l_record_ok BOOLEAN;
712
713 l_proc VARCHAR2(72) := g_package||'validate_org_payment_methods';
714
715 BEGIN
716 hr_utility.set_location('Entering:'|| l_proc, 10);
717 l_counter := 0;
718
719 <<main_loop>>
720 FOR v_ud_rec IN csr_org_payment_method LOOP
721 SAVEPOINT org_payment_start;
722 hr_utility.set_location(l_proc, 20);
723 l_org_payment_method_to_id := hr_h2pi_map.get_to_id
724 (p_table_name => 'PAY_ORG_PAYMENT_METHODS_F',
725 p_from_id => v_ud_rec.org_payment_method_id);
726
727 IF l_org_payment_method_to_id = -1 THEN
728 hr_utility.set_location(l_proc, 30);
729 hr_h2pi_error.data_error(p_from_id => v_ud_rec.org_payment_method_id,
730 p_table_name => 'HR_H2PI_ORG_PAYMENT_METHODS',
731 p_message_level => 'FATAL',
732 p_message_name => 'HR_289260_UD_DATA_ADDED');
733 ELSE
734 hr_utility.set_location(l_proc, 40);
735 BEGIN
736 SELECT *
737 INTO l_ed_rec
738 FROM hr_h2pi_org_payment_methods_v
739 WHERE org_payment_method_id = l_org_payment_method_to_id
740 AND effective_start_date = v_ud_rec.effective_start_date
741 AND effective_end_date = v_ud_rec.effective_end_date;
742
743 l_counter := l_counter + 1 ;
744 org_payment_method_tab(l_counter) := l_org_payment_method_to_id;
745
746 IF l_ed_rec.org_payment_method_name <> v_ud_rec.org_payment_method_name OR
747 l_ed_rec.effective_start_date <> v_ud_rec.effective_start_date OR
748 l_ed_rec.effective_end_date <> v_ud_rec.effective_end_date OR
749 --l_ed_rec.external_account_id <> v_ud_rec.external_account_id OR
750 l_ed_rec.currency_code <> v_ud_rec.currency_code OR
751 l_ed_rec.payment_type_name <> v_ud_rec.payment_type_name OR
752 l_ed_rec.territory_code <> v_ud_rec.territory_code THEN
753 hr_utility.set_location(l_proc, 50);
754 hr_h2pi_error.data_error(p_from_id => v_ud_rec.org_payment_method_id,
755 p_table_name => 'HR_H2PI_ORG_PAYMENT_METHODS',
756 p_message_level => 'FATAL',
757 p_message_name => 'HR_289237_DATA_MISMATCH');
758 END IF;
759
760 EXCEPTION
761 WHEN NO_DATA_FOUND THEN
762 hr_utility.set_location(l_proc, 60);
763 hr_h2pi_error.data_error(p_from_id => v_ud_rec.org_payment_method_id,
764 p_table_name => 'HR_H2PI_ORG_PAYMENT_METHODS',
765 p_message_level =>'FATAL',
766 p_message_name =>'HR_289235_ED_DATA_REMOVED');
767 WHEN MAPPING_ID_MISSING THEN
768 hr_utility.set_location(l_proc, 70);
769 END;
770 END IF;
771 END LOOP main_loop;
772
773 BEGIN
774 <<outer_loop>>
775 FOR csr_pay_org_payment_method_rec in csr_pay_org_payment_method LOOP
776 hr_utility.set_location(l_proc, 80);
777 l_record_ok := FALSE;
778
779 <<inner_loop>>
780 FOR j IN 1..l_counter LOOP
781 IF org_payment_method_tab(j) = csr_pay_org_payment_method_rec.org_payment_method_id THEN
782 l_record_ok := TRUE;
783 END IF;
784 END LOOP inner_loop;
785
786 IF NOT l_record_ok THEN
787 hr_utility.set_location(l_proc, 90);
788 OPEN csr_payroll_data_added(csr_pay_org_payment_method_rec.org_payment_method_id);
789 FETCH csr_payroll_data_added INTO l_org_payment_method_id;
790
791 IF csr_payroll_data_added%FOUND then
792 hr_utility.set_location(l_proc, 100);
793
794 hr_h2pi_error.data_error(p_from_id => l_org_payment_method_id,
795 p_table_name => 'HR_H2PI_ORG_PAYMENT_METHODS',
796 p_message_level => 'FATAL',
797 p_message_name => 'HR_289259_ED_DATA_ADDED');
798 CLOSE csr_payroll_data_added;
799
800 ELSE
801
802 hr_utility.set_location(l_proc, 110);
803 CLOSE csr_payroll_data_added;
804
805 OPEN csr_hr_data_removed(csr_pay_org_payment_method_rec.org_payment_method_id);
806 FETCH csr_hr_data_removed into l_org_payment_method_id;
807 hr_utility.set_location(l_proc, 120);
808 IF csr_hr_data_removed%FOUND then
809 --HR Data removed
810 hr_h2pi_error.data_error(p_from_id => l_org_payment_method_id,
811 p_table_name => 'HR_H2PI_ORG_PAYMENT_METHODS',
812 p_message_level => 'FATAL',
813 p_message_name => 'HR_289236_UD_DATA_REMOVED');
814 END IF;
815 CLOSE csr_hr_data_removed;
816 END IF;
817 END IF;
818 END LOOP outer_loop;
819 END ;
820 COMMIT;
821 hr_utility.set_location('Leaving: '|| l_proc, 130);
822 END validate_org_payment_method;
823
824 --
825 -- For Element Link validation
826 --
827
828 PROCEDURE validate_element_link(p_from_client_id VARCHAR2) IS
829
830 TYPE element_link_tab_type is TABLE OF NUMBER(15)
831 INDEX BY BINARY_INTEGER;
832
833 element_link_tab element_link_tab_type;
834
835 CURSOR csr_element_link IS
836 SELECT el.* FROM hr_h2pi_element_links el,
837 hr_h2pi_element_types et
838 WHERE el.client_id = p_from_client_id
839 AND el.element_type_id = et.element_type_id
840 AND el.effective_start_date BETWEEN et.effective_start_date
841 AND et.effective_end_date
842 AND et.legislation_code IS NULL
843 AND et.client_id = p_from_client_id;
844
845 CURSOR csr_pay_element_link IS
846 SELECT element_link_id
847 FROM hr_h2pi_element_links_v
848 WHERE business_group_id = hr_h2pi_upload.g_to_business_group_id;
849
850 CURSOR csr_hr_data_removed (p_element_link_id NUMBER) IS
851 SELECT element_link_id
852 FROM hr_h2pi_element_links_v ele
853 WHERE ele.business_group_id = hr_h2pi_upload.g_to_business_group_Id
854 AND ele.element_link_id = p_element_link_id
855 AND NOT EXISTS (SELECT 'X'
856 FROM hr_h2pi_id_mapping him,
857 hr_h2pi_element_links ele1
858 WHERE him.to_id = ele.element_link_id
859 AND ele1.element_link_id = him.from_id
860 AND him.table_name = 'PAY_ELEMENT_LINKS_F'
861 AND him.to_business_group_id = ele.business_group_id);
862
863 CURSOR csr_payroll_data_added(p_element_link_id NUMBER) IS
864 SELECT element_link_id
865 FROM hr_h2pi_element_links_v ele
866 WHERE ele.business_group_id = hr_h2pi_upload.g_to_business_group_id
867 AND ele.element_link_id = p_element_link_id
868 AND NOT EXISTS (SELECT 'X'
869 FROM hr_h2pi_id_mapping him
870 WHERE him.to_id = ele.element_link_id
871 AND him.table_name = 'PAY_ELEMENT_LINKS_F'
872 AND him.to_business_group_id =
873 hr_h2pi_upload.g_to_business_group_id);
874
875 l_ed_rec hr_h2pi_element_links_v%ROWTYPE;
876 l_element_link_to_id hr_h2pi_element_links.element_link_id%TYPe;
877 l_counter BINARY_INTEGER ;
878 l_element_link_id hr_h2pi_element_links.element_link_id%TYPE;
879 l_payroll_id hr_h2pi_element_links.payroll_id%TYPE;
880 l_pay_basis_id hr_h2pi_element_links.pay_basis_id%TYPE;
881 l_organization_id hr_h2pi_element_links.organization_id%TYPE;
882 l_record_ok BOOLEAN;
883
884 l_proc VARCHAR2(72) := g_package||'validate_element_link';
885
886 BEGIN
887 hr_utility.set_location('Leaving: '|| l_proc, 10);
888 l_counter := 0;
889
890 <<main_loop>>
891 FOR v_ud_rec IN csr_element_link LOOP
892 SAVEPOINT element_link_start;
893 hr_utility.set_location(l_proc, 20);
894 l_element_link_to_id := hr_h2pi_map.get_to_id
895 (p_table_name => 'PAY_ELEMENT_LINKS_F',
896 p_from_id => v_ud_rec.element_link_id);
897
898 IF l_element_link_to_id = -1 THEN
899 hr_utility.set_location(l_proc, 30);
900 hr_h2pi_error.data_error(p_from_id => v_ud_rec.element_link_id,
901 p_table_name => 'HR_H2PI_ELEMENT_LINKS',
902 p_message_level => 'FATAL',
903 p_message_name => 'HR_289260_UD_DATA_ADDED');
904 ELSE
905 BEGIN
906 hr_utility.set_location(l_proc, 40);
907 SELECT *
908 INTO l_ed_rec
909 FROM hr_h2pi_element_links_v
910 WHERE element_link_id = l_element_link_to_id
911 AND effective_start_date = v_ud_rec.effective_start_date
912 AND effective_end_date = v_ud_rec.effective_end_date;
913
914 l_counter := l_counter + 1 ;
915 element_link_tab(l_counter) := l_element_link_to_id;
916
917 l_payroll_id := hr_h2pi_map.get_to_id
918 (p_table_name => 'PAY_ALL_PAYROLLS_F',
919 p_from_id => v_ud_rec.payroll_id,
920 p_report_error => TRUE);
921 l_organization_id := hr_h2pi_map.get_to_id
922 (p_table_name => 'HR_ALL_ORGANIZATIONS_UNITS',
923 p_from_id => v_ud_rec.organization_id,
924 p_report_error => TRUE);
925 l_pay_basis_id := hr_h2pi_map.get_to_id
926 (p_table_name => 'PER_PAY_BASES',
927 p_from_id => v_ud_rec.pay_basis_id,
928 p_report_error => TRUE);
929 IF l_ed_rec.effective_start_date <> v_ud_rec.effective_start_date OR
930 l_ed_rec.effective_end_date <> v_ud_rec.effective_end_date OR
931 NVL(l_ed_rec.payroll_id,-1) <> NVL(l_payroll_id, -1) OR
932 l_ed_rec.cost_allocation_keyflex_id
933 <> v_ud_rec.cost_allocation_keyflex_id OR
934 NVL(l_ed_rec.organization_id, -1)
935 <> NVL(l_organization_id, -1) OR
936 NVL(l_ed_rec.pay_basis_id, -1) <> NVL(l_pay_basis_id, -1) OR
937 l_ed_rec.link_to_all_payrolls_flag
938 <> v_ud_rec.link_to_all_payrolls_flag THEN
939 hr_utility.set_location(l_proc, 50);
940 hr_h2pi_error.data_error(p_from_id => v_ud_rec.element_link_id,
941 p_table_name => 'HR_H2PI_ELEMENT_LINKS',
942 p_message_level => 'FATAL',
943 p_message_name => 'HR_289237_DATA_MISMATCH');
944 END IF;
945
946 EXCEPTION
947 WHEN NO_DATA_FOUND THEN
948 hr_utility.set_location(l_proc, 60);
949 hr_h2pi_error.data_error(p_from_id => v_ud_rec.element_link_id,
950 p_table_name => 'HR_H2PI_ELEMENT_LINKS',
951 p_message_level=> 'FATAL',
952 p_message_name => 'HR_289235_ED_DATA_REMOVED');
953 WHEN MAPPING_ID_MISSING THEN
954 hr_utility.set_location(l_proc, 70);
955 END;
956 END IF;
957 END LOOP main_loop;
958
959 BEGIN
960 <<outer_loop>>
961 FOR csr_pay_element_link_rec in csr_pay_element_link LOOP
962 hr_utility.set_location(l_proc, 80);
963 l_record_ok := FALSE;
964
965 <<inner_loop>>
966 FOR j IN 1..l_counter LOOP
967 IF element_link_tab(j) = csr_pay_element_link_rec.element_link_id THEN
968 l_record_ok := TRUE;
969 END IF;
970 END LOOP inner_loop;
971
972 IF NOT l_record_ok THEN
973 hr_utility.set_location(l_proc, 90);
974 OPEN csr_payroll_data_added(csr_pay_element_link_rec.element_link_id);
975 FETCH csr_payroll_data_added INTO l_element_link_id;
976
977 IF csr_payroll_data_added%FOUND then
978 hr_utility.set_location(l_proc, 100);
979
980 hr_h2pi_error.data_error(p_from_id => l_element_link_id,
981 p_table_name => 'HR_H2PI_ELEMENT_LINKS',
982 p_message_level => 'FATAL',
983 p_message_name => 'HR_289259_ED_DATA_ADDED');
984 CLOSE csr_payroll_data_added;
985
986 ELSE
987
988 hr_utility.set_location(l_proc, 110);
989 CLOSE csr_payroll_data_added;
990
991 OPEN csr_hr_data_removed(csr_pay_element_link_rec.element_link_id);
992 FETCH csr_hr_data_removed into l_element_link_id;
993 IF csr_hr_data_removed%FOUND then
994 --HR Data removed
995 hr_utility.set_location(l_proc, 120);
996 hr_h2pi_error.data_error(p_from_id => l_element_link_id,
997 p_table_name => 'HR_H2PI_ELEMENT_LINKS',
998 p_message_level => 'FATAL',
999 p_message_name => 'HR_289236_UD_DATA_REMOVED');
1000 END IF;
1001 CLOSE csr_hr_data_removed;
1002 END IF;
1003 END IF;
1004 END LOOP outer_loop;
1005 END ;
1006 COMMIT;
1007 hr_utility.set_location('Leaving: '|| l_proc, 130);
1008 END validate_element_link;
1009 --
1010 -- This procedure checks for the GEOCODE changes on HR and Payroll system
1011 -- If HR or Payroll systems are more then one patches out of sync then
1012 -- this procedure insert a record in hr_h2pi_message lines table with message
1013 -- for the same.
1014 --
1015 -- Following are other three cases
1016 -- HR is one version ahead of Payroll
1017 -- Payroll is one version ahead of HR
1018 -- HR and Payroll are on same version of Geocode patch
1019 --
1020 --
1021 PROCEDURE validate_geocode(p_from_client_id VARCHAR2) IS
1022
1023 CURSOR csr_ud_geocode IS
1024 SELECT NVL(MAX(patch_name),'GEOCODE_1900_Q1') FROM hr_h2pi_patch_status
1025 WHERE client_id = p_from_client_id;
1026 CURSOR csr_ed_geocode IS
1027 SELECT NVL(MAX(patch_name),'GEOCODE_1900_Q1') FROM hr_h2pi_patch_status_v;
1028
1029 l_geocode_status VARCHAR2(30);
1030
1031 l_ud_geocode hr_h2pi_patch_status.patch_name%TYPE;
1032 l_ed_geocode hr_h2pi_patch_status.patch_name%TYPE;
1033
1034 l_jurisdiction_code hr_h2pi_city_tax_rules.jurisdiction_code%TYPE;
1035 l_jurisdiction_code_comp hr_h2pi_city_tax_rules.jurisdiction_code%TYPE;
1036
1037 l_state_code hr_h2pi_us_modified_geocodes.state_code%TYPE;
1038 l_county_code hr_h2pi_us_modified_geocodes.county_code%TYPE;
1039 l_city_code hr_h2pi_us_modified_geocodes.old_city_code%TYPE;
1040
1041 --
1042 l_input_value1 hr_h2pi_input_values.input_value_id%Type;
1043 l_input_value2 hr_h2pi_input_values.input_value_id%Type;
1044 --
1045
1046 -- Cursor to find out input_value_id for VERTEX and Worker Compensation
1047 CURSOR csr_input_values IS
1048 SELECT iv.input_value_id
1049 FROM pay_input_values_f iv,
1050 pay_element_types_f et
1051 WHERE et.element_name IN ('VERTEX','Workers Compensation')
1052 AND iv.name = 'Jurisdiction'
1053 AND iv.element_type_id = et.element_type_id;
1054
1055 CURSOR csr_ud_city_tax IS
1056 SELECT jurisdiction_code jurisdiction_code
1057 FROM hr_h2pi_city_tax_rules
1058 WHERE client_id = p_from_client_id
1059 UNION ALL
1060 SELECT screen_entry_value jurisdiction_code
1061 FROM hr_h2pi_element_entry_values
1062 WHERE input_value_id in ( l_input_value1, l_input_value2)
1063 AND screen_entry_value IS NOT NULL
1064 AND client_id = p_from_client_id;
1065
1066 CURSOR csr_ud_city_tax_comp(p_state_code VARCHAR2,
1067 p_county_code VARCHAR2,
1068 p_city_code VARCHAR2) IS
1069 SELECT old_city_code
1070 FROM hr_h2pi_us_modified_geocodes
1071 WHERE state_code = p_state_code
1072 AND county_code = p_county_code
1073 AND new_city_code = p_city_code;
1074
1075 CURSOR csr_ed_city_tax IS
1076 SELECT jurisdiction_code jurisdiction_code
1077 FROM hr_h2pi_city_tax_rules_v
1078 WHERE business_group_id = hr_h2pi_upload.g_to_business_group_id
1079 UNION ALL
1080 SELECT screen_entry_value jurisdiction_code
1081 FROM hr_h2pi_element_entry_values_v eev,
1082 pay_input_values_f iv,
1083 pay_element_types_f et
1084 WHERE screen_entry_value is NOT NULL
1085 AND iv.element_type_id = et.element_type_id
1086 AND eev.input_value_id = iv.input_value_id
1087 AND et.element_name IN ('VERTEX','Workers Compensation')
1088 AND iv.name = 'Jurisdiction'
1089 AND eev.business_group_id = hr_h2pi_upload.g_to_business_group_id;
1090
1091 CURSOR csr_ed_city_tax_comp(p_state_code VARCHAR2,
1092 p_county_code VARCHAR2,
1093 p_city_code VARCHAR2) IS
1094 SELECT new_city_code
1095 FROM hr_h2pi_us_modified_geocodes_v
1096 WHERE state_code = p_state_code
1097 AND county_code = p_county_code
1098 AND new_city_code = p_city_code;
1099
1100 e_ud_more_then_two_ver_ahead EXCEPTION;
1101 e_ed_more_then_two_ver_ahead EXCEPTION;
1102
1103 l_ud_more_then_two_ver_ahead NUMBER(3);
1104 l_ed_more_then_two_ver_ahead NUMBER(3);
1105
1106 l_proc VARCHAR2(72) := g_package||'validate_geocodes';
1107
1108 BEGIN
1109 SAVEPOINT geocode_start;
1110 hr_utility.set_location('Entering:'|| l_proc, 10);
1111 OPEN csr_ud_geocode;
1112 OPEN csr_ed_geocode;
1113 FETCH csr_ud_geocode INTO l_ud_geocode;
1114 SELECT count(*)
1115 INTO l_ed_more_then_two_ver_ahead
1116 FROM hr_h2pi_patch_status_v
1117 WHERE patch_name > l_ud_geocode;
1118
1119 IF l_ed_more_then_two_ver_ahead > 1 then
1120 hr_utility.set_location(l_proc, 20);
1121 RAISE e_ed_more_then_two_ver_ahead;
1122 END IF;
1123
1124 FETCH csr_ed_geocode INTO l_ed_geocode;
1125 SELECT count(*)
1126 INTO l_ud_more_then_two_ver_ahead
1127 FROM hr_h2pi_patch_status
1128 WHERE patch_name > l_ed_geocode
1129 AND client_id = p_from_client_id;
1130
1131 IF l_ud_more_then_two_ver_ahead > 1 then
1132 hr_utility.set_location(l_proc, 30);
1133 RAISE e_ud_more_then_two_ver_ahead;
1134 END IF;
1135 IF l_ud_geocode < l_ed_geocode THEN
1136 hr_utility.set_location(l_proc, 40);
1137 l_geocode_status := 'UD LATER';
1138 ELSIF l_ud_geocode > l_ed_geocode THEN
1139 hr_utility.set_location(l_proc, 50);
1140 l_geocode_status := 'ED LATER';
1141 ELSE
1142 hr_utility.set_location(l_proc, 60);
1143 l_geocode_status := 'MATCHES';
1144 END IF;
1145 CLOSE csr_ud_geocode;
1146 CLOSE csr_ed_geocode;
1147
1148 IF l_geocode_status = 'ED LATER' then
1149 hr_utility.set_location(l_proc, 70);
1150 --
1151 open csr_input_values;
1152 fetch csr_input_values into l_input_value1;
1153 fetch csr_input_values into l_input_value2;
1154 l_input_value1 :=
1155 hr_h2pi_map.get_from_id(p_table_name=>'PAY_INPUT_VALUES_F',
1156 p_to_id => l_input_value1,
1157 p_report_error => TRUE);
1158 l_input_value2 :=
1159 hr_h2pi_map.get_from_id(p_table_name=>'PAY_INPUT_VALUES_F',
1160 p_to_id => l_input_value2,
1161 p_report_error => TRUE);
1162
1163 --
1164 FOR csr_ud_city_tax_rec IN csr_ud_city_tax LOOP
1165 hr_utility.set_location(l_proc, 80);
1166 l_state_code := substr(csr_ud_city_tax_rec.jurisdiction_code,1,2);
1167 l_county_code := substr(csr_ud_city_tax_rec.jurisdiction_code,4,3);
1168 l_city_code := substr(csr_ud_city_tax_rec.jurisdiction_code,8,4);
1169 -- This is user defined city write an error
1170 FOR csr_ud_city_tax_comp_rec IN csr_ud_city_tax_comp(l_state_code,
1171 l_county_code,
1172 l_city_code) LOOP
1173 IF l_city_code LIKE 'U%' THEN
1174 hr_utility.set_location(l_proc, 90);
1175 hr_h2pi_error.data_error(p_from_id => l_state_code ||
1176 l_county_code ||
1177 REPLACE(l_city_code,'U',0),
1178 p_table_name => 'HR_H2PI_US_MODIFIED_GEOCODES',
1179 p_message_level => 'FATAL',
1180 p_message_name => 'HR_289269_USER_CITY_CODE');
1181 -- Message name to be changed.
1182 ELSIF csr_ud_city_tax_comp%FOUND THEN
1183 hr_utility.set_location(l_proc, 100);
1184 hr_h2pi_error.data_error(p_from_id => l_state_code ||
1185 l_county_code ||
1186 REPLACE(l_city_code,'U',0),
1187 p_table_name => 'HR_H2PI_US_MODIFIED_GEOCODES',
1188 p_message_level => 'FATAL',
1189 p_message_name => 'HR_289236_UD_DATA_REMOVED');
1190 EXIT;
1191 ELSE
1192 NULL;
1193 EXIT;
1194 END IF;
1195 END LOOP;
1196 END LOOP;
1197 ELSIF l_geocode_status = 'UD LATER' then
1198 hr_utility.set_location(l_proc, 110);
1199 FOR csr_ed_city_tax_rec IN csr_ed_city_tax LOOP
1200 l_state_code := substr(csr_ed_city_tax_rec.jurisdiction_code,1,2);
1201 l_county_code := substr(csr_ed_city_tax_rec.jurisdiction_code,4,3);
1202 l_city_code := substr(csr_ed_city_tax_rec.jurisdiction_code,8,4);
1203 hr_utility.set_location(l_proc, 120);
1204 FOR csr_ed_city_tax_comp_rec IN csr_ed_city_tax_comp(l_state_code,
1205 l_county_code,
1206 l_city_code) LOOP
1207 IF l_city_code LIKE 'U%' THEN
1208
1209 hr_h2pi_error.data_error(p_from_id => l_state_code ||
1210 l_county_code ||
1211 REPLACE(l_city_code,'U',0),
1212 p_table_name => 'HR_H2PI_US_MODIFIED_GEOCODES',
1213 p_message_level => 'FATAL',
1214 p_message_name => 'HR_289269_USER_CITY_CODE');
1215 ELSIF csr_ed_city_tax_comp%FOUND THEN
1216 hr_h2pi_error.data_error(p_from_id => l_state_code ||
1217 l_county_code ||
1218 REPLACE(l_city_code,'U',0),
1219 p_table_name => 'HR_H2PI_US_MODIFIED_GEOCODES',
1220 p_message_level => 'FATAL',
1221 p_message_name => 'HR_289235_ED_DATA_REMOVED');
1222
1223 EXIT;
1224 ELSE
1225 NULL;
1226 EXIT;
1227 END IF;
1228 END LOOP;
1229 END LOOP;
1230 END IF;
1231 COMMIT;
1232 hr_utility.set_location('Leaving:'|| l_proc, 130);
1233 EXCEPTION
1234 WHEN e_ed_more_then_two_ver_ahead THEN
1235 hr_utility.set_location(l_proc, 140);
1236 hr_h2pi_error.data_error(p_from_id => '99999',
1237 p_table_name => 'HR_H2PI_US_MODIFIED_GEOCODES',
1238 p_message_level => 'FATAL',
1239 p_message_name => 'HR_289235_ED_DATA_REMOVED');
1240 --RAISE;
1241 WHEN e_ud_more_then_two_ver_ahead THEN
1242 hr_utility.set_location(l_proc, 150);
1243 hr_h2pi_error.data_error(p_from_id => '99999',
1244 p_table_name => 'HR_H2PI_US_MODIFIED_GEOCODES',
1245 p_message_level => 'FATAL',
1246 p_message_name => 'HR_289235_ED_DATA_REMOVED');
1247 --RAISE;
1248 WHEN MAPPING_ID_MISSING THEN
1249 hr_utility.set_location(l_proc, 160);
1250
1251 END validate_geocode;
1252
1253 END hr_h2pi_validate;