[Home] [Help]
PACKAGE BODY: APPS.PER_IN_ADD_LEG_HOOK
Source
1 PACKAGE BODY per_in_add_leg_hook AS
2 /* $Header: peinlhad.pkb 120.3.12010000.2 2008/08/06 09:13:28 ubhat ship $ */
3 p_cnt number;
4 g_debug BOOLEAN;
5 g_package CONSTANT VARCHAR2(100) := 'per_in_add_leg_hook.';
6 l_message VARCHAR2(255);
7
8 CURSOR csr_pin_codes IS
9 SELECT lookup_code,
10 meaning
11 FROM hr_lookups
12 WHERE lookup_type='IN_PIN_CODES';
13
14
15 /*--------------------------------------------------------------------------
16 -- Name : CHECK_ADDRESS --
17 -- Type : Procedure --
18 -- Access : Private --
19 -- Description : Procedure is the driver procedure for the validation--
20 -- of the address. --
21 -- Parameters : --
22 -- IN : p_style IN VARCHAR2 --
23 -- p_add_information15 IN VARCHAR2 --
24 -- p_postal_code IN VARCHAR2 --
25 --------------------------------------------------------------------------*/
26
27 PROCEDURE check_address(p_style IN VARCHAR2
28 ,p_add_information15 IN VARCHAR2 -- State
29 ,p_postal_code IN VARCHAR2) AS
30
31 l_proc VARCHAR2(50) ;
32 l_token VARCHAR2(80);
33 g_debug BOOLEAN;
34 l_procedure VARCHAR2(100);
35 l_message VARCHAR2(255);
36 /*--------------------------------------------------------------------------
37 -- Name : validate_pin_code --
38 -- Type : Function --
39 -- Access : Private --
40 -- Description : This is the main function that performs pin code --
41 -- validation of the address. --
42 --------------------------------------------------------------------------*/
43
44 FUNCTION validate_pin_code(p_state hr_lookups.lookup_code%TYPE,
45 p_postal_code hr_lookups.meaning%TYPE)
46 RETURN BOOLEAN IS
47 l_postal_code hr_lookups.meaning%TYPE;
48 l_func VARCHAR2(72);
49 g_debug BOOLEAN;
50 l_state hr_lookups.lookup_code%TYPE;
51
52 BEGIN
53 g_debug := hr_utility.debug_enabled ;
54 l_procedure := g_package ||'validate_pin_code';
55 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
56
57 IF g_debug THEN
58 pay_in_utils.trace('**************************************************','********************');
59 pay_in_utils.trace('p_state',p_state);
60 pay_in_utils.trace('p_postal_code',p_postal_code);
61 pay_in_utils.trace('**************************************************','********************');
62 END IF;
63
64 l_postal_code :=substr(p_postal_code,1,2);
65 select decode(p_state,'JH','BR','CG','MP',p_state) into l_state from dual;
66
67 FOR i IN 1..tab_pin_code.COUNT LOOP
68 --
69 IF g_debug THEN
70 pay_in_utils.set_location(g_debug,l_procedure,20);
71 END IF;
72
73 IF instr(tab_pin_code(i).state_code,l_state)>0 THEN
74
75 IF g_debug THEN
76 pay_in_utils.set_location(g_debug,l_procedure,30);
77 END IF;
78
79 IF instr(tab_pin_code(i).pin_code,l_postal_code) =0 THEN
80 --
81
82 IF substr(tab_pin_code(i).pin_code,1,2)=substr(tab_pin_code(i).pin_code,-2,2) THEN
83 l_token :=tab_pin_code(i).pin_code;
84
85 IF g_debug THEN
86 pay_in_utils.set_location(g_debug,l_procedure,40);
87 END IF;
88 ELSE
89 l_token :=rtrim(tab_pin_code(i).pin_code,substr(tab_pin_code(i).pin_code,-3,3))||' or '||substr(tab_pin_code(i).pin_code,-2,2);
90
91 IF g_debug THEN
92 pay_in_utils.set_location(g_debug,l_procedure,50);
93 END IF;
94
95 END IF;
96
97 IF g_debug THEN
98 pay_in_utils.set_location(g_debug,l_procedure,60);
99 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,70);
100 END IF;
101 --
102 RETURN FALSE;
103 ELSE
104 IF g_debug THEN
105 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,80);
106 END IF;
107 RETURN TRUE;
108 END IF;
109 END IF;
110 --
111 --
112 END LOOP;
113 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,90);
114 RETURN FALSE;
115 EXCEPTION
116 WHEN OTHERS THEN
117 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
118 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
119 pay_in_utils.trace(l_message,l_procedure);
120 RAISE;
121 RETURN FALSE;
122 END;
123
124 BEGIN
125 g_debug := hr_utility.debug_enabled ;
126
127 IF g_debug THEN
128 l_procedure := g_package ||'check_address';
129 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
130 pay_in_utils.trace('**************************************************','********************');
131 pay_in_utils.trace('p_style',p_style);
132 pay_in_utils.trace('p_add_information15',p_add_information15);
133 pay_in_utils.trace('p_postal_code',p_postal_code);
134 pay_in_utils.trace('**************************************************','********************');
135 END IF;
136
137 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
138 IF g_debug THEN
139 pay_in_utils.trace('IN Legislation not installed. Not performing the validations','20');
140 END IF;
141
142 RETURN;
143 END IF;
144
145 IF g_debug THEN
146 pay_in_utils.set_location(g_debug,l_procedure,30);
147 END IF;
148 --
149 IF p_style='IN' THEN
150
151 IF p_postal_code IS NOT NULL AND p_add_information15 IS NOT NULL THEN
152 IF NOT validate_pin_code(p_add_information15,p_postal_code) THEN
153 hr_utility.set_message(800,'PER_IN_INVALID_PIN_CODE');
154 hr_utility.set_message_token('VALUE',l_token);
155 hr_utility.raise_error;
156 END IF;
157 END IF;
158
159 END IF;
160 IF g_debug THEN
161 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
162 END IF;
163 /*
164 EXCEPTION
165 WHEN OTHERS THEN
166 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
167 hr_utility.set_message(800,'PER_IN_ORACLE_GENERIC_ERROR');
168 hr_utility.set_message_token('FUNCTION',l_procedure);
169 hr_utility.set_message_token('SQLERRMC',sqlerrm);
170 hr_utility.raise_error;*/
171 END check_address;
172
173 --------------------------------------------------------------------------
174 -- --
175 -- Name : CHECK_PER_ADDRESS_INS --
176 -- Type : Procedure --
177 -- Access : Public --
178 -- Description : Procedure is the driver procedure for the validation--
179 -- of address of a person. --
180 -- This procedure is the hook procedure for the --
181 -- address when address is inserted. --
182 -- Parameters : --
183 -- IN : p_style IN VARCHAR2 --
184 -- p_address_id IN VARCHAR2 --
185 -- p_add_information14 IN VARCHAR2 --
186 -- p_add_information15 IN VARCHAR2 --
187 -- p_postal_code IN VARCHAR2 --
188 ---------------------------------------------------------------------------
189 PROCEDURE check_per_address_ins(p_style IN VARCHAR2
190 ,p_address_id IN NUMBER
191 ,p_add_information14 IN VARCHAR2
192 ,p_add_information15 IN VARCHAR2 -- State
193 ,p_postal_code IN VARCHAR2)
194 IS
195
196 l_address_type VARCHAR2(30);
197 l_proc VARCHAR2(50) ;
198 l_get_migrator_status varchar2(1);
199 g_debug BOOLEAN;
200 l_procedure VARCHAR2(100);
201 l_message VARCHAR2(255);
202 BEGIN
203 g_debug := hr_utility.debug_enabled ;
204
205 IF g_debug THEN
206 l_procedure := g_package ||'check_per_address_ins';
207 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
208 pay_in_utils.trace('**************************************************','********************');
209 pay_in_utils.trace('p_style',p_style);
210 pay_in_utils.trace('p_address_id',p_address_id);
211 pay_in_utils.trace('p_add_information14',p_add_information14);
212 pay_in_utils.trace('p_add_information15',p_add_information15);
213 pay_in_utils.trace('p_postal_code',p_postal_code);
214 pay_in_utils.trace('**************************************************','********************');
215 END IF;
216 check_address (p_style => p_style
217 ,p_add_information15 => p_add_information15
218 ,p_postal_code => p_postal_code);
219 IF g_debug THEN
220 pay_in_utils.set_location(g_debug,l_procedure,20);
221 END IF;
222
223 IF p_style ='IN' THEN
224 l_get_migrator_status:=hr_general.g_data_migrator_mode;
225
226 hr_general.g_data_migrator_mode:='Y';
227 IF UPPER(p_add_information14) IN ('DELHI','NEW DELHI','KOLKATA','CHENNAI','MUMBAI') THEN
228 UPDATE per_addresses
229 SET add_information16='Y'
230 WHERE address_id =p_address_id;
231 ELSE
232 UPDATE per_addresses
233 SET add_information16='N'
234 WHERE address_id =p_address_id;
235 END IF;
236 hr_general.g_data_migrator_mode:=l_get_migrator_status;
237 END IF;
238
239 IF g_debug THEN
240 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
241 END IF;
242 /*
243 EXCEPTION
244 WHEN OTHERS THEN
245 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
246 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 40);
247 pay_in_utils.trace(l_message,l_procedure);
248 RAISE;*/
249 END check_per_address_ins;
250
251 --------------------------------------------------------------------------
252 -- --
253 -- Name : CHECK_LOC_ADDRESS_INS --
254 -- Type : Procedure --
255 -- Access : Public --
256 -- Description : Procedure is the driver procedure for the validation--
257 -- of address of a location. --
258 -- This procedure is the hook procedure for the --
259 -- address when address is inserted. --
260 -- Parameters : --
261 -- IN : p_style IN VARCHAR2 --
262 -- p_loc_information16 IN VARCHAR2 --
263 -- p_postal_code IN VARCHAR2 --
264 ---------------------------------------------------------------------------
265 PROCEDURE check_loc_address_ins(p_style IN VARCHAR2
266 ,p_loc_information16 IN VARCHAR2 -- State
267 ,p_postal_code IN VARCHAR2)
268 IS
269 l_proc VARCHAR2(50);
270 g_debug BOOLEAN;
271 l_procedure VARCHAR2(100);
272 l_message VARCHAR2(255);
273 BEGIN
274 g_debug := hr_utility.debug_enabled ;
275
276 IF g_debug THEN
277 l_procedure := g_package ||'check_loc_address_ins';
278 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
279 pay_in_utils.trace('**************************************************','********************');
280 pay_in_utils.trace('p_style',p_style);
281 pay_in_utils.trace('p_loc_information16',p_loc_information16);
282 pay_in_utils.trace('p_postal_code',p_postal_code);
283 pay_in_utils.trace('**************************************************','********************');
284 END IF;
285
286 check_address (p_style => p_style
287 ,p_add_information15 => p_loc_information16
288 ,p_postal_code => p_postal_code);
289 IF g_debug THEN
290 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
291 END IF;
292 /*
293 EXCEPTION
294 WHEN OTHERS THEN
295 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
296 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
297 pay_in_utils.trace(l_message,l_procedure);
298 RAISE;*/
299 END check_loc_address_ins;
300
301
302 --------------------------------------------------------------------------
303 -- --
304 -- Name : CHECK_PER_ADDRESS_UPD --
305 -- Type : Procedure --
306 -- Access : Public --
307 -- Description : Procedure is the driver procedure for the validation--
308 -- of the address of a person. --
309 -- This procedure is the hook procedure for the --
310 -- address when address is updated. --
311 -- Parameters : --
312 -- IN : p_address_id IN NUMBER --
313 -- p_add_information14 IN VARCHAR2 --
314 -- p_add_information15 IN VARCHAR2 --
315 -- p_postal_code IN VARCHAR2 --
316 -- --
317 --------------------------------------------------------------------------
318 PROCEDURE check_per_address_upd
319 (p_address_id IN NUMBER
320 ,p_add_information14 IN VARCHAR2
321 ,p_add_information15 IN VARCHAR2
322 ,p_postal_code IN VARCHAR2)
323 IS
324 l_style per_addresses.style%TYPE;
325 l_get_migrator_status VARCHAR2(1);
326 l_proc VARCHAR2(50) ;
327 g_debug BOOLEAN;
328 l_procedure VARCHAR2(100);
329 l_message VARCHAR2(255);
330
331 CURSOR csr_addr_style(p_address_id NUMBER)IS
332 SELECT addr.style
333 FROM per_addresses addr
334 WHERE addr.address_id = p_address_id ;
335
336 BEGIN
337 g_debug := hr_utility.debug_enabled ;
338
339 IF g_debug THEN
340 l_procedure := g_package ||'check_per_address_upd';
341 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
342 pay_in_utils.trace('**************************************************','********************');
343 pay_in_utils.trace('p_address_id',p_address_id);
344 pay_in_utils.trace('p_add_information14',p_add_information14);
345 pay_in_utils.trace('p_add_information15',p_add_information15);
346 pay_in_utils.trace('p_postal_code',p_postal_code);
347 pay_in_utils.trace('**************************************************','********************');
348 END IF;
349
350 OPEN csr_addr_style(p_address_id);
351 FETCH csr_addr_style INTO l_style;
352 CLOSE csr_addr_style;
353
354 IF g_debug THEN
355 pay_in_utils.set_location(g_debug,l_procedure,20);
356 END IF;
357
358 check_address (p_style => l_style
359 ,p_add_information15 => p_add_information15
360 ,p_postal_code => p_postal_code);
361
362 IF g_debug THEN
363 pay_in_utils.set_location(g_debug,l_procedure,30);
364 END IF;
365
366 IF l_style='IN' THEN
367 l_get_migrator_status:=hr_general.g_data_migrator_mode;
368
369 hr_general.g_data_migrator_mode:='Y';
370 IF UPPER(p_add_information14) IN ('DELHI','NEW DELHI','KOLKATA','CHENNAI','MUMBAI') THEN
371 UPDATE per_addresses
372 SET add_information16='Y'
373 WHERE address_id =p_address_id;
374 ELSE
375 UPDATE per_addresses
376 SET add_information16='N'
377 WHERE address_id =p_address_id;
378 END IF;
379 hr_general.g_data_migrator_mode:=l_get_migrator_status;
380 END IF;
381
382 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);/*
383 EXCEPTION
384 WHEN OTHERS THEN
385 IF csr_addr_style%ISOPEN THEN
386 CLOSE csr_addr_style;
387 END IF;
388 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
389 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
390 pay_in_utils.trace(l_message,l_procedure);
391 RAISE;*/
392 END check_per_address_upd;
393 --------------------------------------------------------------------------
394 -- --
395 -- Name : check_loc_address_upd --
396 -- Type : Procedure --
397 -- Access : Public --
398 -- Description : Procedure is the driver procedure for the validation--
399 -- of address of a location. --
400 -- This procedure is the hook procedure for the --
401 -- address when address is updated. --
402 -- Parameters : --
403 -- IN : p_address_id IN NUMBER --
404 -- p_loc_information16 IN VARCHAR2 --
405 -- p_postal_code IN VARCHAR2 --
406 -- --
407 --------------------------------------------------------------------------
408 PROCEDURE check_loc_address_upd(p_location_id IN NUMBER
409 ,p_loc_information16 IN VARCHAR2 -- State
410 ,p_postal_code IN VARCHAR2)
411 IS
412 l_style per_addresses.style%TYPE;
413 l_proc VARCHAR2(50);
414 g_debug BOOLEAN;
415 l_procedure VARCHAR2(100);
416 l_message VARCHAR2(255);
417
418 CURSOR csr_addr_style(p_location_id NUMBER)IS
419 SELECT hl.style
420 FROM hr_locations_all hl
421 WHERE hl.location_id = p_location_id ;
422
423
424 BEGIN
425 g_debug := hr_utility.debug_enabled ;
426
427 IF g_debug THEN
428 l_procedure := g_package ||'check_loc_address_upd';
429 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
430 pay_in_utils.trace('**************************************************','********************');
431 pay_in_utils.trace('p_location_id',p_location_id);
432 pay_in_utils.trace('p_loc_information16',p_loc_information16);
433 pay_in_utils.trace('p_postal_code',p_postal_code);
434 pay_in_utils.trace('**************************************************','********************');
435 END IF;
436
437 OPEN csr_addr_style(p_location_id);
438 FETCH csr_addr_style INTO l_style;
439 CLOSE csr_addr_style;
440
441 IF g_debug THEN
442 pay_in_utils.set_location(g_debug,l_procedure,20);
443 END IF;
444
445 check_address (p_style => l_style
446 ,p_add_information15 => p_loc_information16
447 ,p_postal_code => p_postal_code);
448 IF g_debug THEN
449 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
450 END IF;/*
451 EXCEPTION
452 WHEN OTHERS THEN
453 IF csr_addr_style%ISOPEN THEN
454 CLOSE csr_addr_style;
455 END IF;
456 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
457 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 40);
458 pay_in_utils.trace(l_message,l_procedure);
459 RAISE;*/
460 END check_loc_address_upd;
461
462 BEGIN
463
464 g_debug := hr_utility.debug_enabled ;
465
466 IF g_debug THEN
467 pay_in_utils.set_location(g_debug,'Entering: '||g_package,10);
468 END IF;
469 p_cnt:=1;
470
471 --
472 -- Populate PL/SQL table with state_code and valid pin codes
473 --
474
475 OPEN csr_pin_codes;
476 LOOP
477 FETCH csr_pin_codes into tab_pin_code(p_cnt).state_code,tab_pin_code(p_cnt).pin_code;
478 EXIT WHEN csr_pin_codes%NOTFOUND;
479 p_cnt := p_cnt+1;
480 END LOOP;
481 CLOSE csr_pin_codes;
482
483 IF g_debug THEN
484 pay_in_utils.set_location(g_debug,g_package,20);
485 END IF;
486
487 pay_in_utils.set_location(g_debug,'Leaving: '||g_package,30);
488 EXCEPTION
489 WHEN OTHERS THEN
490 --
491 IF csr_pin_codes%ISOPEN THEN
492 IF g_debug THEN
493 pay_in_utils.set_location(g_debug,g_package,40);
494 END IF;
495 CLOSE csr_pin_codes;
496 END IF;
497 --
498 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'PACKAGE:'||g_package, 'SQLERRMC:'||sqlerrm);
499 pay_in_utils.set_location(g_debug,'Leaving : '||g_package, 50);
500 pay_in_utils.trace(l_message,g_package);
501 RAISE;
502 END per_in_add_leg_hook;