[Home] [Help]
PACKAGE BODY: APPS.PER_IN_PERSON_TERM_LEG_HOOK
Source
1 PACKAGE BODY per_in_person_term_leg_hook AS
2 /* $Header: peinlhte.pkb 120.4.12000000.5 2007/10/04 12:14:13 lnagaraj ship $ */
3 --
4 --
5 -- Globals
6 --
7 g_package constant VARCHAR2(100) := 'per_in_person_term_leg_hook.' ;
8 g_debug BOOLEAN ;
9 g_message_name VARCHAR2(30);
10 g_token_name pay_in_utils.char_tab_type;
11 g_token_value pay_in_utils.char_tab_type;
12
13 --------------------------------------------------------------------------
14 -- --
15 -- Name : ACTUAL_TERMINATION_EMP_INT --
16 -- Type : PROCEDURE --
17 -- Access : Public --
18 -- Description : Internal Procedure for IN localization --
19 -- Parameters : --
20 -- IN : p_period_of_service_id NUMBER --
21 -- p_business_group_id NUMBER --
22 -- OUT : N/A --
23 -- RETURN : N/A --
24 -- --
25 -- Change History : --
26 --------------------------------------------------------------------------
27 -- Rev# Date Userid Bug Description --
28 --------------------------------------------------------------------------
29 -- 1.0 30-Sep-04 sshankar 3801926 Created this procedure --
30 -- 1.1 26-Oct-04 statkar 3847355 Removed call to pay_in_gratuity_pkg-
31 --------------------------------------------------------------------------
32 PROCEDURE actual_termination_emp_int
33 (p_effective_date IN DATE
34 ,p_period_of_service_id IN NUMBER
35 ,p_actual_termination_date IN DATE
36 ,p_business_group_id IN NUMBER
37 ,p_calling_procedure IN VARCHAR2
38 ,p_message_name OUT NOCOPY VARCHAR2
39 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
40 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type)
41 IS
42
43 --
44 l_procedure VARCHAR2(100);
45 l_message VARCHAR2(255);
46
47 --
48 BEGIN
49 --
50 g_debug := hr_utility.debug_enabled ;
51 l_procedure := g_package || 'actual_termination_emp_int' ;
52 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
53
54 if g_debug then
55 pay_in_utils.trace('******************************','********************');
56 pay_in_utils.trace('p_effective_date : ',p_effective_date);
57 pay_in_utils.trace('p_period_of_service_id : ',p_period_of_service_id);
58 pay_in_utils.trace('p_actual_termination_date : ',p_actual_termination_date);
59 pay_in_utils.trace('p_business_group_id : ',p_business_group_id);
60 pay_in_utils.trace('p_calling_procedure : ',p_calling_procedure);
61 pay_in_utils.trace('******************************','********************');
62 end if;
63
64 pay_in_utils.null_message(g_token_name, g_token_value);
65 g_message_name := 'SUCCESS';
66
67 pay_in_termination_pkg.create_termination_elements
68 (p_period_of_service_id => p_period_of_service_id
69 ,p_business_group_id => p_business_group_id
70 ,p_actual_termination_date => p_actual_termination_date
71 ,p_calling_procedure => p_calling_procedure
72 ,p_message_name => p_message_name
73 ,p_token_name => p_token_name
74 ,p_token_value => p_token_value);
75
76 if g_debug then
77 pay_in_utils.trace('******************************','********************');
78 pay_in_utils.trace('p_message_name : ',p_message_name);
79 pay_in_utils.trace('******************************','********************');
80 end if;
81
82 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
83
84 --
85 END actual_termination_emp_int ;
86
87 --------------------------------------------------------------------------
88 -- --
89 -- Name : REVERSE_TERMINATION_EMP --
90 -- Type : PROCEDURE --
91 -- Access : Public --
92 -- Description : Generic Procedure to be called for IN localization --
93 -- Parameters : --
94 -- IN : p_period_of_service_id NUMBER --
95 -- p_business_group_id NUMBER --
96 -- OUT : N/A --
97 -- RETURN : N/A --
98 -- --
99 -- Change History : --
100 --------------------------------------------------------------------------
101 -- Rev# Date Userid Bug Description --
102 --------------------------------------------------------------------------
103 -- 1.0 30-Sep-04 sshankar 3801926 Created this procedure --
104 -- 1.1 26-Oct-04 statkar 3847355 Removed call to pay_in_gratuity_pkg-
105 --------------------------------------------------------------------------
106 PROCEDURE reverse_termination_emp_int
107 (p_effective_date IN DATE
108 ,p_period_of_service_id IN NUMBER
109 ,p_actual_termination_date IN DATE
110 ,p_business_group_id IN NUMBER
111 ,p_calling_procedure IN VARCHAR2
112 ,p_message_name OUT NOCOPY VARCHAR2
113 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
114 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
115 )
116 IS
117 l_procedure VARCHAR2(100);
118 l_message VARCHAR2(255);
119 --
120 BEGIN
121 --
122 g_debug := hr_utility.debug_enabled ;
123 l_procedure := g_package || 'reverse_termination_emp_int' ;
124 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
125
126 if g_debug then
127 pay_in_utils.trace('******************************','********************');
128 pay_in_utils.trace('p_effective_date : ',p_effective_date);
129 pay_in_utils.trace('p_period_of_service_id : ',p_period_of_service_id);
130 pay_in_utils.trace('p_actual_termination_date : ',p_actual_termination_date);
131 pay_in_utils.trace('p_business_group_id : ',p_business_group_id);
132 pay_in_utils.trace('p_calling_procedure : ',p_calling_procedure);
133 pay_in_utils.trace('******************************','********************');
134 end if;
135
136 pay_in_utils.null_message(g_token_name, g_token_value);
137 g_message_name := 'SUCCESS';
138
139 pay_in_termination_pkg.delete_termination_elements
140 (p_period_of_service_id => p_period_of_service_id
141 ,p_business_group_id => p_business_group_id
142 ,p_actual_termination_date => p_actual_termination_date
143 ,p_calling_procedure => p_calling_procedure
144 ,p_message_name => p_message_name
145 ,p_token_name => p_token_name
146 ,p_token_value => p_token_value);
147
148 if g_debug then
149 pay_in_utils.trace('******************************','********************');
150 pay_in_utils.trace('p_message_name : ',p_message_name);
151 pay_in_utils.trace('******************************','********************');
152 end if;
153
154 --
155 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
156
157 --
158 END reverse_termination_emp_int ;
159
160 --------------------------------------------------------------------------
161 -- --
162 -- Name : ACTUAL_TERMINATION_EMP --
163 -- Type : PROCEDURE --
164 -- Access : Public --
165 -- Description : Generic Procedure to be called for IN localization --
166 -- Parameters : --
167 -- IN : p_period_of_service_id NUMBER --
168 -- p_business_group_id NUMBER --
169 -- OUT : N/A --
170 -- RETURN : N/A --
171 -- --
172 -- Change History : --
173 --------------------------------------------------------------------------
174 -- Rev# Date Userid Bug Description --
175 --------------------------------------------------------------------------
176 -- 1.0 30-Sep-04 sshankar 3801926 Created this procedure --
177 -- 1.1 11-Jul-06 rpalli 5242205 Restored orig proc with content --
178 -- as NULL. This proc would not be --
179 -- used further --
180 -- 1.2 27-Sep-06 aaagarwa 5559210 --
181 -- 1.3 11-Jul-07 rsaharay 6199974 Removed the select from --
182 -- fnd_sessions --
183 --------------------------------------------------------------------------
184 PROCEDURE actual_termination_emp
185 (p_effective_date IN DATE
186 ,p_period_of_service_id IN NUMBER
187 ,p_actual_termination_date IN DATE
188 ,p_business_group_id IN NUMBER
189 )
190 IS
191 CURSOR c_pds_details
192 IS
193 SELECT final_process_date
194 ,actual_termination_date
195 FROM per_periods_of_service
196 WHERE period_of_service_id = p_period_of_service_id;
197
198 l_final_process_date DATE;
199 l_actual_termination_date DATE;
200 l_effective_date DATE;
201 l_procedure VARCHAR2(100);
202 l_message VARCHAR2(255);
203 --
204 BEGIN
205 --
206
207 g_debug := hr_utility.debug_enabled ;
208 l_procedure := g_package || 'actual_termination_emp';
209 pay_in_utils.set_location(g_debug,'Entering: '|| l_procedure,10);
210
211 IF g_debug
212 THEN
213 pay_in_utils.trace('******************************','********************');
214 pay_in_utils.trace('p_effective_date : ',p_effective_date);
215 pay_in_utils.trace('p_period_of_service_id : ',p_period_of_service_id);
216 pay_in_utils.trace('p_actual_termination_date : ',p_actual_termination_date);
217 pay_in_utils.trace('p_business_group_id : ',p_business_group_id);
218 pay_in_utils.trace('******************************','********************');
219 END IF;
220
221 OPEN c_pds_details;
222 FETCH c_pds_details INTO l_final_process_date,l_actual_termination_date;
223 CLOSE c_pds_details;
224
225 pay_in_utils.trace('l_final_process_date : ',l_final_process_date);
226 pay_in_utils.trace('l_actual_termination_date : ',l_actual_termination_date);
227
228 g_message_name := 'SUCCESS';
229 pay_in_utils.null_message (g_token_name, g_token_value);
230 IF hr_utility.chk_product_install('Oracle Payroll','IN') THEN
231 IF (l_final_process_date IS NULL)
232 THEN
233 actual_termination_emp_int
234 (p_effective_date => l_effective_date
235 ,p_period_of_service_id => p_period_of_service_id
236 ,p_actual_termination_date => l_actual_termination_date
237 ,p_business_group_id => p_business_group_id
238 ,p_calling_procedure => l_procedure
239 ,p_message_name => g_message_name
240 ,p_token_name => g_token_name
241 ,p_token_value => g_token_value
242 ) ;
243 END IF;
244 END IF;
245 pay_in_utils.set_location(g_debug,'Leaving: '|| l_procedure,30);
246 RETURN;
247 END actual_termination_emp ;
248
249 --------------------------------------------------------------------------
250 -- --
251 -- Name : FINAL_PROCESS_EMP --
252 -- Type : PROCEDURE --
253 -- Access : Public --
254 -- Description : Generic Procedure to be called for IN localization --
255 -- Parameters : --
256 -- IN : p_period_of_service_id NUMBER --
257 -- OUT : N/A --
258 -- RETURN : N/A --
259 -- --
260 -- Change History : --
261 --------------------------------------------------------------------------
262 -- Rev# Date Userid Bug Description --
263 --------------------------------------------------------------------------
264 -- 1.0 11-Jul-06 rpalli 5242205 Created this procedure --
265 -- 1.2 11-Jul-07 rsaharay 6199974 Removed the select from --
266 -- fnd_sessions --
267 --------------------------------------------------------------------------
268 PROCEDURE final_process_emp
269 (p_period_of_service_id IN NUMBER)
270 IS
271
272 --
273 CURSOR c_pos_dtls IS
274 SELECT pos.business_group_id
275 ,pos.actual_termination_date
276 FROM per_periods_of_service pos
277 WHERE pos.period_of_service_id = p_period_of_service_id;
278
279 l_business_group_id NUMBER;
280 l_actual_termination_date DATE;
281 l_effective_date DATE;
282 --
283 --
284 l_procedure VARCHAR2(100);
285 l_message VARCHAR2(255);
286
287 --
288 BEGIN
289 --
290 g_debug := hr_utility.debug_enabled ;
291
292 l_procedure := g_package || 'final_process_emp' ;
293 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
294
295 if g_debug then
296 pay_in_utils.trace('******************************','********************');
297 pay_in_utils.trace('p_period_of_service_id : ',p_period_of_service_id);
298 pay_in_utils.trace('******************************','********************');
299 end if;
300
301 g_message_name := 'SUCCESS';
302 pay_in_utils.null_message (g_token_name, g_token_value);
303 --
304 -- Check if PAY is installed for India Localization
305 --
306 IF hr_utility.chk_product_install('Oracle Payroll','IN') THEN
307
308 pay_in_utils.set_location(g_debug,l_procedure,20);
309
310 OPEN c_pos_dtls;
311 FETCH c_pos_dtls
312 INTO l_business_group_id, l_actual_termination_date;
313 CLOSE c_pos_dtls;
314
315
316 if g_debug then
317 pay_in_utils.trace('l_effective_date : ',l_effective_date);
318 pay_in_utils.trace('l_actual_termination_date : ',l_actual_termination_date);
319 pay_in_utils.trace('l_business_group_id : ',l_business_group_id);
320 end if;
321
322 pay_in_utils.set_location(g_debug,l_procedure,25);
323
324 actual_termination_emp_int
325 (p_effective_date => l_effective_date
326 ,p_period_of_service_id => p_period_of_service_id
327 ,p_actual_termination_date => l_actual_termination_date
328 ,p_business_group_id => l_business_group_id
329 ,p_calling_procedure => l_procedure
330 ,p_message_name => g_message_name
331 ,p_token_name => g_token_name
332 ,p_token_value => g_token_value
333 ) ;
334 --
335 END IF ;
336
337 if g_debug then
338 pay_in_utils.trace('******************************','********************');
339 pay_in_utils.trace('g_message_name : ',g_message_name);
340 pay_in_utils.trace('******************************','********************');
341 end if;
342
343 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
344 pay_in_utils.raise_message(800, g_message_name, g_token_name, g_token_value);
345 --
346 END final_process_emp ;
347
348 --------------------------------------------------------------------------
349 -- --
350 -- Name : REVERSE_TERMINATION_EMP --
351 -- Type : PROCEDURE --
352 -- Access : Public --
353 -- Description : Generic Procedure to be called for IN localization --
354 -- Parameters : --
355 -- IN : p_period_of_service_id NUMBER --
356 -- p_business_group_id NUMBER --
357 -- OUT : N/A --
358 -- RETURN : N/A --
359 -- --
360 -- Change History : --
361 --------------------------------------------------------------------------
362 -- Rev# Date Userid Bug Description --
363 --------------------------------------------------------------------------
364 -- 1.0 30-Sep-04 sshankar 3801926 Created this procedure --
365 --------------------------------------------------------------------------
366 PROCEDURE reverse_termination_emp
367 (p_effective_date IN DATE
368 ,p_period_of_service_id IN NUMBER
369 ,p_actual_termination_date IN DATE
370 ,p_business_group_id IN NUMBER
371 )
372 IS
373 l_procedure VARCHAR2(100);
374 l_message VARCHAR2(255);
375
376 --
377 BEGIN
378 --
379 g_debug := hr_utility.debug_enabled ;
380
381 l_procedure := g_package || 'reverse_termination_emp' ;
382 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
383
384 if g_debug then
385 pay_in_utils.trace('******************************','********************');
386 pay_in_utils.trace('p_effective_date : ',p_effective_date);
387 pay_in_utils.trace('p_period_of_service_id : ',p_period_of_service_id);
388 pay_in_utils.trace('p_actual_termination_date : ',p_actual_termination_date);
389 pay_in_utils.trace('p_business_group_id : ',p_business_group_id);
390 pay_in_utils.trace('******************************','********************');
391 end if;
392
393 g_message_name := 'SUCCESS';
394 pay_in_utils.null_message (g_token_name, g_token_value);
395 --
396 -- Check if PAY is installed for India Localization
397 --
398 IF hr_utility.chk_product_install('Oracle Payroll','IN') THEN
399
400 pay_in_utils.set_location(g_debug,l_procedure,20);
401
402 reverse_termination_emp_int
403 (p_effective_date => p_effective_date
404 ,p_period_of_service_id => p_period_of_service_id
405 ,p_actual_termination_date => p_actual_termination_date
406 ,p_business_group_id => p_business_group_id
407 ,p_calling_procedure => l_procedure
408 ,p_message_name => g_message_name
409 ,p_token_name => g_token_name
410 ,p_token_value => g_token_value
411 ) ;
412 --
413 END IF ;
414
415 if g_debug then
416 pay_in_utils.trace('******************************','********************');
417 pay_in_utils.trace('g_message_name : ',g_message_name);
418 pay_in_utils.trace('******************************','********************');
419 end if;
420
421 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
422 pay_in_utils.raise_message(800, g_message_name, g_token_name, g_token_value);
423
424 --
425 END reverse_termination_emp ;
426
427
428 --------------------------------------------------------------------------
429 -- --
430 -- Name : UPDATE_PDS_DETAILS --
431 -- Type : PROCEDURE --
432 -- Access : Public --
433 -- Description : Generic Procedure to be called for IN localization --
434 -- Parameters : --
435 -- IN : p_period_of_service_id NUMBER --
436 -- p_effective_date DATE --
437 -- p_leaving_reason VARCHAR2 --
438 -- OUT : N/A --
439 -- RETURN : N/A --
440 -- --
441 -- Change History : --
442 --------------------------------------------------------------------------
443 -- Rev# Date Userid Bug Description --
444 --------------------------------------------------------------------------
445 -- 1.0 19-Nov-04 aaagarwa 3977410 Created this procedure --
446 -- 1.1 25-Sep-07 rsaharay 6401091 Modified cursor c_pos_dtls --
447 --------------------------------------------------------------------------
448 PROCEDURE update_pds_details
449 (p_period_of_service_id IN NUMBER
450 ,p_effective_date IN DATE
451 ,p_leaving_reason IN VARCHAR2
452 )
453 IS
454 CURSOR c_pos_dtls IS
455 SELECT pos.actual_termination_date
456 ,ppf.business_group_id
457 FROM per_periods_of_service pos
458 ,per_assignments_f paf
459 ,per_people_f ppf
460 WHERE pos.period_of_service_id = p_period_of_service_id
461 AND pos.period_of_service_id = paf.period_of_service_id
462 AND paf.person_id = ppf.person_id
463 AND p_effective_date BETWEEN ppf.effective_start_date
464 AND ppf.effective_end_date
465 AND p_effective_date BETWEEN paf.effective_start_date
466 AND paf.effective_end_date;
467
468 l_procedure VARCHAR2(100);
469 l_message VARCHAR2(255);
470 l_actual_termination_date DATE;
471 l_business_group_id NUMBER;
472 --
473 BEGIN
474 --
475 g_debug := hr_utility.debug_enabled ;
476 l_procedure := g_package || 'update_pds_details' ;
477 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
478
479 if g_debug then
480 pay_in_utils.trace('******************************','********************');
481 pay_in_utils.trace('p_period_of_service_id : ',p_period_of_service_id);
482 pay_in_utils.trace('p_effective_date : ',p_effective_date);
483 pay_in_utils.trace('p_leaving_reason : ',p_leaving_reason);
484 pay_in_utils.trace('******************************','********************');
485 end if;
486
487 g_message_name := 'SUCCESS';
488
489 pay_in_termination_pkg.g_leaving_reason := p_leaving_reason;
490
491 pay_in_utils.null_message (g_token_name, g_token_value);
492 --
493 -- Check if PAY is installed for India Localization
494 --
495
496 IF hr_utility.chk_product_install('Oracle Payroll','IN') THEN
497
498 pay_in_utils.set_location(g_debug,l_procedure,20);
499 OPEN c_pos_dtls;
500 FETCH c_pos_dtls
501 INTO l_actual_termination_date, l_business_group_id;
502 CLOSE c_pos_dtls;
503
504 if g_debug then
505 pay_in_utils.trace('l_business_group_id : ',l_business_group_id);
506 pay_in_utils.trace('l_actual_termination_date : ',l_actual_termination_date);
507 end if;
508
509 pay_in_utils.set_location(g_debug,l_procedure,30);
510 pay_in_termination_pkg.delete_gratuity_entry
511 (p_period_of_service_id => p_period_of_service_id
512 ,p_business_group_id => l_business_group_id
513 ,p_actual_termination_date => l_actual_termination_date
514 ,p_calling_procedure => l_procedure
515 ,p_message_name => g_message_name
516 ,p_token_name => g_token_name
517 ,p_token_value => g_token_value
518 );
519
520 if g_debug then
521 pay_in_utils.trace('******************************','********************');
522 pay_in_utils.trace('g_message_name : ',g_message_name);
523 pay_in_utils.trace('******************************','********************');
524 end if;
525
526 pay_in_utils.set_location(g_debug,l_procedure,40);
527 pay_in_utils.raise_message(800, g_message_name, g_token_name, g_token_value);
528 g_message_name := 'SUCCESS';
529 pay_in_utils.null_message (g_token_name, g_token_value);
530
531
532
533 pay_in_termination_pkg.create_gratuity_entry
534 (p_period_of_service_id => p_period_of_service_id
535 ,p_business_group_id => l_business_group_id
536 ,p_actual_termination_date => l_actual_termination_date
537 ,p_calling_procedure => l_procedure
538 ,p_message_name => g_message_name
539 ,p_token_name => g_token_name
540 ,p_token_value => g_token_value
541 );
542
543 if g_debug then
544 pay_in_utils.trace('******************************','********************');
545 pay_in_utils.trace('g_message_name : ',g_message_name);
546 pay_in_utils.trace('******************************','********************');
547 end if;
548
549 pay_in_utils.set_location(g_debug,l_procedure,50);
550 pay_in_utils.raise_message(800, g_message_name, g_token_name, g_token_value);
551
552 END IF ;
553
554 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,60);
555
556 END update_pds_details;
557
558 END per_in_person_term_leg_hook;