[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_EOY_ARCHIVE
Source
1 PACKAGE BODY pay_in_eoy_archive AS
2 /* $Header: pyinpeoy.pkb 120.34.12020000.8 2013/02/06 12:19:45 pthummal ship $ */
3
4 g_asg_tab t_asg_tab;
5 g_pay_gre_tab t_gre_tab;
6 g_count NUMBER;
7 g_global_count NUMBER ;
8 g_debug BOOLEAN;
9
10 g_archive_pact NUMBER;
11 g_employee_type VARCHAR2(20);
12 g_gre_id VARCHAR2(20);
13 g_start_date DATE;
14 g_end_date DATE;
15 g_term_date DATE;
16 g_system_date VARCHAR2(30);
17 g_year VARCHAR2(20);
18 g_bg_id NUMBER;
19 g_package CONSTANT VARCHAR2(100) := 'pay_in_eoy_archive.';
20 --------------------------------------------------------------------------
21 -- --
22 -- Name : RANGE_CODE --
23 -- Type : PROCEDURE --
24 -- Access : Public --
25 -- Description : This procedure returns a sql string to select a --
26 -- range of assignments eligible for archival. --
27 -- --
28 -- Parameters : --
29 -- IN : p_payroll_action_id NUMBER --
30 -- OUT : p_sql VARCHAR2 --
31 -- --
32 -- Change History : --
33 --------------------------------------------------------------------------
34 -- Rev# Date Userid Description --
35 --------------------------------------------------------------------------
36 -- 115.0 23-MAY-2005 aaagarwa Initial Version --
37 --------------------------------------------------------------------------
38 --
39
40 PROCEDURE range_code(
41 p_payroll_action_id IN NUMBER
42 ,p_sql OUT NOCOPY VARCHAR2
43 )
44 IS
45 --
46 l_procedure VARCHAR2(100);
47 l_message VARCHAR2(255);
48 --
49 BEGIN
50
51 g_debug := hr_utility.debug_enabled;
52 l_procedure := g_package || 'range_code';
53 -- Call core package to return SQL string to SELECT a range
54 -- of assignments eligible for archival
55 --
56 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
57 pay_core_payslip_utils.range_cursor(p_payroll_action_id
58 ,p_sql);
59 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
60 --
61 EXCEPTION
62 WHEN OTHERS THEN
63 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
64 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
65 pay_in_utils.trace(l_message,l_procedure);
66 RAISE;
67 --
68 END range_code;
69
70 --------------------------------------------------------------------------
71 -- --
72 -- Name : GET_PARAMETERS --
73 -- Type : PROCEDURE --
74 -- Access : Public --
75 -- Description : This procedure determines the globals applicable --
76 -- through out the tenure of the process --
77 -- Parameters : --
78 -- IN : --
79 -- OUT : N/A --
80 -- --
81 -- Change History : --
82 --------------------------------------------------------------------------
83 -- Rev# Date Userid Description --
84 --------------------------------------------------------------------------
85 -- 115.0 23-MAY-2005 aaagarwa Initial Version --
86 --------------------------------------------------------------------------
87
88 PROCEDURE get_parameters(p_payroll_action_id IN NUMBER,
89 p_token_name IN VARCHAR2,
90 p_token_value OUT NOCOPY VARCHAR2) IS
91
92 CURSOR csr_parameter_info(p_pact_id NUMBER,
93 p_token CHAR) IS
94 SELECT SUBSTR(legislative_parameters,
95 INSTR(legislative_parameters,p_token)+(LENGTH(p_token)+1),
96 INSTR(legislative_parameters,' ',
97 INSTR(legislative_parameters,p_token))
98 - (INSTR(legislative_parameters,p_token)+LENGTH(p_token)))
99 ,business_group_id
100 FROM pay_payroll_actions
101 WHERE payroll_action_id = p_pact_id;
102
103 l_token_value VARCHAR2(50);
104 l_bg_id NUMBER;
105 l_message VARCHAR2(255);
106 l_procedure VARCHAR2(100);
107
108
109 BEGIN
110
111
112 l_procedure := g_package ||'get_parameters';
113 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
114
115
116 IF g_debug THEN
117 pay_in_utils.trace('Payroll Action id ',p_payroll_action_id);
118 pay_in_utils.trace('Token Name ',p_token_name);
119 END IF;
120
121
122 OPEN csr_parameter_info(p_payroll_action_id,
123 p_token_name);
124
125 FETCH csr_parameter_info INTO l_token_value,l_bg_id;
126
127 CLOSE csr_parameter_info;
128
129 p_token_value := TRIM(l_token_value);
130
131 IF (p_token_name = 'BG_ID') THEN
132 p_token_value := l_bg_id;
133 END IF;
134
135 IF (p_token_value IS NULL) THEN
136 p_token_value := '%';
137 END IF;
138
139 IF g_debug THEN
140 pay_in_utils.trace('Token Value ',p_token_value);
141 END IF;
142
143 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
144
145
146 END get_parameters;
147 --------------------------------------------------------------------------
148 -- --
149 -- Name : INITIALIZATION_CODE --
150 -- Type : PROCEDURE --
151 -- Access : Public --
152 -- Description : This procedure is used to set global contexts. --
153 -- The globals used are PL/SQL tables --
154 -- This will be used to define balance and other context-
155 -- --
156 -- Parameters : --
157 -- IN : p_payroll_action_id NUMBER --
158 -- OUT : N/A --
159 -- --
160 -- Change History : --
161 --------------------------------------------------------------------------
162 -- Rev# Date Userid Description --
163 --------------------------------------------------------------------------
164 -- 115.0 23-MAY-2005 aaagarwa Initial Version --
165 --------------------------------------------------------------------------
166 --
167 PROCEDURE initialization_code (
168 p_payroll_action_id IN NUMBER
169 )
170 IS
171 --
172 l_procedure VARCHAR2(100) ;
173 l_message VARCHAR2(255);
174 --
175 BEGIN
176 --
177 g_debug := hr_utility.debug_enabled;
178 l_procedure := g_package || 'initialization_code';
179
180 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
181
182 g_archive_pact := p_payroll_action_id;
183
184 IF g_debug THEN
185 pay_in_utils.trace('Payroll Action id ',p_payroll_action_id);
186 END IF;
187
188 get_parameters(p_payroll_action_id,'YEAR',g_year);
189 get_parameters(p_payroll_action_id,'GRE',g_gre_id);
190 get_parameters(p_payroll_action_id,'EMPLOYEE_TYPE',g_employee_type);
191
192
193 SELECT TRUNC(effective_date)
194 INTO g_system_date
195 FROM fnd_sessions
196 WHERE session_id = USERENV('sessionid');
197
198 pay_in_utils.set_location(g_debug,l_procedure, 20);
199
200 g_start_date := fnd_date.string_to_date(('01/04/'|| SUBSTR(g_year,1,4)),'DD/MM/YYYY');
201 g_end_date := fnd_date.string_to_date(('31/03/'|| SUBSTR(g_year,6)),'DD/MM/YYYY');
202
203 g_start_date := ADD_MONTHS(g_start_date,-12);
204 g_end_date := ADD_MONTHS(g_end_date,-12);
205
206 SELECT FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
207 INTO g_bg_id
208 FROM dual;
209
210 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
211 --
212 EXCEPTION
213 WHEN OTHERS THEN
214 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
215 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 40);
216 pay_in_utils.trace(l_message,l_procedure);
217 RAISE;
218 END initialization_code;
219
220
221 --------------------------------------------------------------------------
222 -- --
223 -- Name : PROCESS_EMPLOYEE_TYPE --
224 -- Type : PROCEDURE --
225 -- Access : Private --
226 -- Description : Procedure to check the archival eligibility of an --
227 -- assignment --
228 -- Parameters : --
229 -- IN : p_employee_type VARCHAR2 --
230 -- p_assignment_id NUMBER --
231 -- p_gre_id VARCHAR2 --
232 -- --
233 -- OUT : N/A --
234 -- --
235 -- Change History : --
236 --------------------------------------------------------------------------
237 -- Rev# Date Userid Description --
238 --------------------------------------------------------------------------
239 -- 115.0 10-JUN-2005 aaagarwa Initial Version --
240 --------------------------------------------------------------------------
241 FUNCTION process_employee_type(p_employee_type VARCHAR2
242 ,p_assignment_id NUMBER
243 ,p_gre_id VARCHAR2
244 )
245 RETURN BOOLEAN
246 IS
247 --This cursor determines termination date of an assignment.
248 CURSOR c_termination_check
249 IS
250 SELECT NVL(pos.actual_termination_date,(fnd_date.string_to_date('31-12-4712','DD-MM-YYYY')))
251 FROM per_all_assignments_f asg
252 ,per_periods_of_service pos
253 WHERE asg.person_id = pos.person_id
254 AND asg.assignment_id = p_assignment_id
255 AND asg.business_group_id = pos.business_group_id
256 AND asg.business_group_id = g_bg_id
257 AND NVL(pos.actual_termination_date,(to_date('31-12-4712','DD-MM-YYYY')))
258 BETWEEN asg.effective_start_date AND asg.effective_end_date
259 ORDER BY 1 desc;
260 --This cursor determines the GRE/Legal Entity as on the end of financial year.
261 CURSOR c_gre_id
262 IS
263 SELECT 1
264 FROM per_all_assignments_f asg
265 ,hr_soft_coding_keyflex scl
266 WHERE asg.assignment_id = p_assignment_id
267 AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
268 AND scl.segment1 = TO_CHAR(g_gre_id)
269 AND g_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
270 --This cursor determines if an assignment had a change in GRE/Legal Entity. If
271 --this cursor retruns 0 or 1 then this means that there was no change in asg's
272 --GRE/Legal entity. This cursor returns 0 if the assignment was created on a
273 --Date prior or equal to g_start_date and scl.segment1 didnot go any change for
274 --the complete period starting from g_start_date and ending on g_end_date.
275 CURSOR c_gre_count
276 IS
277 SELECT COUNT(DISTINCT scl.segment1)
278 FROM per_all_assignments_f asg
279 ,hr_soft_coding_keyflex scl
280 WHERE asg.assignment_id = p_assignment_id
281 AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
282 AND ( asg.effective_start_date BETWEEN g_start_date AND g_end_date
283 OR
284 g_start_date BETWEEN asg.effective_start_date AND g_end_date
285 );
286 --This cursor determines the presence of an assignment in a given GRE/Legal Entity
287 --in a given financial year. Here the purpose is to ascertain the presence of an
288 --employee in a GRE in a given financial year.
289 CURSOR c_gre_employee
290 IS
291 SELECT 1
292 FROM per_all_assignments_f asg
293 ,hr_soft_coding_keyflex scl
294 WHERE asg.assignment_id = p_assignment_id
295 AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
296 AND scl.segment1 = TO_CHAR(g_gre_id)
297 AND (asg.effective_start_date BETWEEN g_start_date AND g_end_date
298 OR
299 g_start_date BETWEEN asg.effective_start_date AND g_end_date
300 )
301 AND ROWNUM = 1;
302 --
303 l_flag NUMBER;
304 l_message VARCHAR2(255);
305 l_procedure VARCHAR2(100);
306
307 --
308 BEGIN
309
310 l_procedure := g_package ||'process_employee_type';
311 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
312
313 -- Determine the presence of an asg in a GRE, if GRE was specified.
314 IF (g_gre_id <> '%')
315 THEN
316 pay_in_utils.set_location(g_debug,l_procedure, 20);
317 OPEN c_gre_employee;
318 FETCH c_gre_employee INTO l_flag;
319 CLOSE c_gre_employee;
320 -- Added NVL for bug 4964645
321 IF (NVL(l_flag,-1) <> 1) THEN
322 pay_in_utils.set_location(g_debug,l_procedure, 30);
323 RETURN FALSE;
324 END IF;
325
326 END IF;
327
328 -- Finding the termination date.
329 OPEN c_termination_check;
330 FETCH c_termination_check INTO g_term_date;
331 CLOSE c_termination_check;
332 --
333 l_flag := NULL;
334 --
335 pay_in_utils.set_location(g_debug,l_procedure, 20);
336 IF (g_employee_type NOT IN('ALL','CURRENT'))
337 THEN
338 pay_in_utils.set_location(g_debug,l_procedure, 30);
339 --Checking for terminated and transferred cases.
340 IF (g_term_date BETWEEN g_start_date AND g_end_date-1)
341 THEN
342 RETURN TRUE;
343 END IF;
344 --Start checking for transferred case.
345 l_flag := NULL;
346 OPEN c_gre_count;
347 FETCH c_gre_count INTO l_flag;
348 CLOSE c_gre_count;
349 pay_in_utils.set_location(g_debug,l_procedure, 40);
350 IF (l_flag < 2)
351 THEN
352 pay_in_utils.set_location(g_debug,l_procedure, 50);
353 RETURN FALSE; /* This assignment did not go any change in GRE/Legal entity and hence
354 returning false */
355 ELSIF(g_gre_id = '%') THEN
356 pay_in_utils.set_location(g_debug,l_procedure, 60);
357 RETURN TRUE; /* Returning true as this asg had changes in GRE/Legal Entity. */
358 ELSE
359 pay_in_utils.set_location(g_debug,l_procedure, 70);
360 l_flag := NULL; -- This assignment was attached to the specified GRE.
361 OPEN c_gre_id; -- Now check for transfer. For this check the GRE as on the
362 FETCH c_gre_id INTO l_flag; -- last day of financial year. If its same, then there was
363 CLOSE c_gre_id; -- no transfer and return false, else return true.
364 IF (l_flag = 1)
365 THEN
366 RETURN FALSE;
367 ELSE
368 RETURN TRUE;
369 END IF;
370 END IF;
371 ELSE
372 --Start Checking for Regular Employee, i.e the employees who are attached to the specified GRE
373 --as on the last day of the financial year.
374 pay_in_utils.set_location(g_debug,l_procedure, 80);
375 IF (g_term_date >= g_end_date)
376 THEN -- Employee is a regular one.
377 pay_in_utils.set_location(g_debug,l_procedure, 90);
378 IF(g_gre_id = '%')
379 THEN
380 RETURN TRUE;
381 ELSE
382 OPEN c_gre_id;
383 FETCH c_gre_id INTO l_flag;
384 CLOSE c_gre_id;
385 IF ((l_flag = 1)OR (g_employee_type ='ALL'))
386 THEN
387 RETURN TRUE;
388 ELSE
389 RETURN FALSE;
390 END IF;
391 END IF;
392 ELSE
393 pay_in_utils.set_location(g_debug,l_procedure, 100);
394 IF (g_employee_type ='ALL')
395 THEN
396 RETURN TRUE;
397 ELSE
398 RETURN FALSE;
399 END IF;
400 END IF;
401 END IF;
402 END process_employee_type;
403 --------------------------------------------------------------------------
404 -- --
405 -- Name : ASSIGNMENT_ACTION_CODE --
406 -- Type : PROCEDURE --
407 -- Access : Public --
408 -- Description : This procedure further restricts the assignment_id's--
409 -- returned by range_code. --
410 -- It filters the assignments selected by range_code --
411 -- procedure. --
412 -- --
413 -- Parameters : --
414 -- IN : p_payroll_action_id NUMBER --
415 -- p_start_person NUMBER --
416 -- p_end_person NUMBER --
417 -- p_chunk NUMBER --
418 -- OUT : N/A --
419 -- --
420 -- Change History : --
421 --------------------------------------------------------------------------
422 -- Rev# Date Userid Description --
423 --------------------------------------------------------------------------
424 -- 115.0 23-MAY-2005 aaagarwa Initial Version --
425 -- 115.1 14-Feb-2006 lnagaraj Introduced c_process_assignments --
426 --------------------------------------------------------------------------
427 --
428 PROCEDURE assignment_action_code(p_payroll_action_id IN NUMBER
429 ,p_start_person IN NUMBER
430 ,p_end_person IN NUMBER
431 ,p_chunk IN NUMBER
432 )
433 IS
434 /*Changed for Bug 4768371*/
435 CURSOR c_process_assignments
436 IS
437 SELECT paf.assignment_id assignment_id
438 FROM per_assignments_f paf
439 ,pay_payroll_actions ppa
440 ,pay_assignment_actions paa
441 WHERE paf.business_group_id = g_bg_id
442 AND paf.person_id BETWEEN p_start_person AND p_end_person
443 AND p_payroll_action_id IS NOT NULL
444 AND paa.tax_unit_id LIKE g_gre_id
445 AND paa.assignment_id =paf.assignment_id
446 AND ppa.action_type IN('P','U','I')
447 AND paa.payroll_action_id = ppa.payroll_action_id
448 AND ppa.action_status = 'C'
449 AND ppa.effective_date BETWEEN g_start_date and g_end_date
450 AND paf.effective_start_date <= g_end_date
451 AND paf.effective_end_date >= g_start_date
452 AND ppa.business_group_id =g_bg_id
453 GROUP BY paf.assignment_id;
454
455
456
457 l_procedure VARCHAR2(100);
458 l_message VARCHAR2(255);
459 l_action_id NUMBER;
460 l_bg_id NUMBER;
461 l_flag BOOLEAN;
462 --
463 BEGIN
464 --
465 l_procedure := g_package || 'assignment_action_code';
466 g_debug := hr_utility.debug_enabled;
467 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
468
469 get_parameters(p_payroll_action_id,'BG_ID',l_bg_id);
470 get_parameters(p_payroll_action_id,'YEAR',g_year);
471 get_parameters(p_payroll_action_id,'GRE',g_gre_id);
472 get_parameters(p_payroll_action_id,'EMPLOYEE_TYPE',g_employee_type);
473
474 pay_in_utils.set_location(g_debug,l_procedure, 20);
475 SELECT TRUNC(effective_date)
476 INTO g_system_date
477 FROM fnd_sessions
478 WHERE session_id = USERENV('sessionid');
479
480 SELECT FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
481 INTO g_bg_id
482 FROM dual;
483
484 g_start_date := fnd_date.string_to_date(('01/04/'|| SUBSTR(g_year,1,4)),'DD/MM/YYYY');
485 g_end_date := fnd_date.string_to_date(('31/03/'|| SUBSTR(g_year,6)),'DD/MM/YYYY');
486
487 g_start_date := ADD_MONTHS(g_start_date,-12);
488 g_end_date := ADD_MONTHS(g_end_date,-12);
489
490 pay_in_utils.set_location(g_debug,l_procedure, 30);
491
492
493 FOR csr_rec IN c_process_assignments
494 LOOP
495 pay_in_utils.set_location(g_debug,l_procedure, 40);
496 l_flag := FALSE;
497
498 IF g_debug THEN
499 pay_in_utils.trace('Assignment id ',csr_rec.assignment_id);
500 END IF;
501
502
503 l_flag := process_employee_type(p_employee_type => g_employee_type
504 ,p_assignment_id => csr_rec.assignment_id
505 ,p_gre_id => g_gre_id);
506 IF (l_flag = TRUE) THEN
507 pay_in_utils.set_location(g_debug,l_procedure, 50);
508 SELECT pay_assignment_actions_s.NEXTVAL
509 INTO l_action_id
510 FROM dual;
511
512 hr_nonrun_asact.insact(lockingactid => l_action_id
513 ,assignid => csr_rec.assignment_id
514 ,pactid => p_payroll_action_id
515 ,chunk => p_chunk
516 );
517 END IF;
518
519 END LOOP;
520 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 60);
521 --
522 EXCEPTION
523 WHEN OTHERS THEN
524 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
525 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 70);
526 pay_in_utils.trace(l_message,l_procedure);
527 RAISE;
528 END assignment_action_code;
529
530 --------------------------------------------------------------------------
531 -- --
532 -- Name : ARCHIVE_PERSON_DATA --
533 -- Type : PROCEDURE --
534 -- Access : Public --
535 -- Description : This procedure archives the person data --
536 -- Parameters : --
537 -- IN : p_run_asg_action_id NUMBER --
538 -- p_arc_asg_action_id NUMBER --
539 -- p_payroll_run_date DATE --
540 -- p_prepayment_date DATE --
541 -- p_assignment_id NUMBER --
542 -- p_gre_id NUMBER --
543 -- OUT : N/A --
544 -- --
545 -- Change History : --
546 --------------------------------------------------------------------------
547 -- Rev# Date Userid Description --
548 --------------------------------------------------------------------------
549 -- 115.0 23-MAY-2005 aaagarwa Initial Version --
550 -- 115.1 03-OCT-2005 snekkala Added code to handle termination --
551 -- 115.2 25-SEP-2007 rsaharay Modified c_pos --
552 --------------------------------------------------------------------------
553 --
554 PROCEDURE archive_person_data(p_run_asg_action_id IN NUMBER
555 ,p_arc_asg_action_id IN NUMBER
556 ,p_arc_payroll_act_id IN NUMBER
557 ,p_prepayment_date IN DATE
558 ,p_assignment_id IN NUMBER
559 ,p_gre_id IN NUMBER
560 ,p_payroll_run_date IN VARCHAR2
561 ,p_effective_start_date IN DATE
562 ,p_effective_end_date IN DATE
563 )
564 IS
565
566 CURSOR c_emp_no
567 IS
568 SELECT pep.employee_number emp_no
569 ,asg.person_id person_id
570 ,DECODE(scl.segment9,'N',DECODE(scl.segment10,'N','N','Y'),'Y')interest
571 ,DECODE(pep.per_information4,NULL,pep.per_information5,pep.per_information4) pan
572 ,DECODE(pep.title,NULL,hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title)
573 ,SUBSTR(hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title)
574 ,INSTR(hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title),' ',1)+1)) name
575 ,pep.title title
576 ,fnd_date.date_to_canonical(pep.date_of_birth) dob
577 ,pep.sex gender
578 ,pep.per_information7 residential_status
579 ,pep.email_address emailAddr
580 FROM per_all_assignments_f asg
581 ,hr_soft_coding_keyflex scl
582 ,per_all_people_f pep
583 WHERE asg.assignment_id = p_assignment_id
584 AND pep.person_id = asg.person_id
585 AND pep.business_group_id = g_bg_id
586 AND asg.business_group_id = g_bg_id
587 AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
588 AND scl.segment1 = TO_CHAR(p_gre_id)
589 AND p_effective_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date
590 AND p_effective_end_date BETWEEN pep.effective_start_date AND pep.effective_end_date ;
591
592 CURSOR c_pos
593 IS
594 SELECT nvl(pos.name,job.name) name, job.name job
595 FROM per_all_positions pos
596 ,per_assignments_f asg
597 ,per_jobs job
598 WHERE asg.position_id=pos.position_id(+)
599 AND asg.job_id=job.job_id(+)
600 AND asg.assignment_id = p_assignment_id
601 AND asg.business_group_id = g_bg_id
602 AND p_effective_end_date BETWEEN pos.date_effective(+) AND NVL(pos.date_end(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
603 AND p_effective_end_date BETWEEN job.date_from(+) AND NVL(job.date_to(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
604 AND p_effective_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
605
606
607
608 CURSOR c_father_name(p_person_id NUMBER)
609 IS
610 SELECT DECODE(pea.title,NULL,hr_in_utility.per_in_full_name(pea.first_name,pea.middle_names,pea.last_name,pea.title)
611 ,SUBSTR(hr_in_utility.per_in_full_name(pea.first_name,pea.middle_names,pea.last_name,pea.title)
612 ,INSTR(hr_in_utility.per_in_full_name(pea.first_name,pea.middle_names,pea.last_name,pea.title),' ',1)+1))father
613 ,pea.title title
614 FROM per_all_people_f pep
615 ,per_all_people_f pea
616 ,per_contact_relationships con
617 WHERE pep.person_id = p_person_id
618 AND pea.person_id =con.contact_person_id
619 AND pep.business_group_id = g_bg_id
620 AND pea.business_group_id = g_bg_id
621 AND con.person_id=pep.person_id
622 AND con.contact_type='JP_FT'
623 AND p_effective_end_date BETWEEN pep.effective_start_date AND pep.effective_end_date
624 AND p_effective_end_date BETWEEN pea.effective_start_date AND pea.effective_end_date;
625
626 CURSOR c_employee_address(p_person_id NUMBER)
627 IS
628 SELECT address_id
629 ,address_type
630 FROM per_addresses
631 WHERE person_id = p_person_id
632 AND address_type = DECODE(address_type,'IN_P','IN_P','IN_C')
633 AND p_effective_end_date BETWEEN date_from AND nvl(date_to,to_date('31-12-4712','DD-MM-YYYY'))
634 ORDER BY address_type DESC;
635
636 CURSOR c_phone(p_person_id NUMBER)
637 IS
638 SELECT phone_number rep_phone_no
639 ,phone_type
640 FROM per_phones
641 WHERE parent_id = p_person_id
642 AND phone_type = DECODE(phone_type,'H1','H1','M')
643 AND p_effective_end_date BETWEEN date_from AND NVL(date_to,TO_DATE('31-12-4712','DD-MM-YYYY'))
644 ORDER BY phone_type ASC;
645
646 l_emp_no per_all_assignments_f.assignment_number%TYPE;
647 l_person_id per_all_people_f.person_id%TYPE;
648 l_dob VARCHAR2(30);
649 l_pan per_all_people_f.per_information4%TYPE;
650 l_residential_status per_all_people_f.per_information7%TYPE;
651 l_name per_all_people_f.full_name%TYPE;
652 l_emp_title per_all_people_f.title%TYPE;
653 l_emp_fath_title per_all_people_f.title%TYPE;
654 l_father_name per_all_people_f.full_name%TYPE;
655 l_gender per_all_people_f.sex%TYPE;
656 l_pos per_all_positions.name%TYPE;
657 l_job per_jobs.name%TYPE;
658 l_email_address per_all_people_f.email_address%TYPE;
659 l_employee_address per_addresses.address_id%TYPE;
660 l_employee_address_type per_addresses.address_type%TYPE;
661 l_phone_no per_phones.phone_number%TYPE;
662 l_phone_type per_phones.phone_type%TYPE;
663 l_interest VARCHAR2(2);
664 l_action_info_id NUMBER;
665 l_ovn NUMBER;
666 flag BOOLEAN;
667 -- Added the variable as part of bug 4621622
668 l_effective_end_date DATE;
669 l_message VARCHAR2(255);
670 l_procedure VARCHAR2(100);
671
672
673 BEGIN
674
675 l_procedure := g_package ||'archive_person_data';
676 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
677
678 IF g_debug THEN
679 pay_in_utils.trace('Run Assignment Action id ',p_run_asg_action_id);
680 pay_in_utils.trace('Archive Assignment Action id ',p_arc_asg_action_id);
681 pay_in_utils.trace('Archive payroll Action id ',p_arc_payroll_act_id);
682 pay_in_utils.trace('Prepayment Date ',p_prepayment_date);
683 pay_in_utils.trace('Assignment id ',p_assignment_id);
684 pay_in_utils.trace('GRE id ',p_gre_id);
685 pay_in_utils.trace('Payroll Run Date ',p_payroll_run_date);
686 pay_in_utils.trace('Effective Start Date ',p_effective_start_date);
687 pay_in_utils.trace('Effective End Date ',p_effective_end_date);
688 END IF;
689
690 OPEN c_emp_no;
691 FETCH c_emp_no INTO l_emp_no,l_person_id,l_interest,l_pan,l_name,l_emp_title,l_dob,l_gender,l_residential_status, l_email_address;
692 CLOSE c_emp_no;
693
694
695 /*
696 OPEN c_person_details(l_person_id);
697 FETCH c_person_details INTO l_pan,l_name,l_emp_title,l_dob,l_gender,l_residential_status;
698 CLOSE c_person_details;
699 */
700 OPEN c_pos;
701 FETCH c_pos INTO l_pos,l_job;
702 CLOSE c_pos;
703
704 pay_in_utils.set_location(g_debug,l_procedure, 20);
705
706 OPEN c_father_name(l_person_id);
707 FETCH c_father_name INTO l_father_name,l_emp_fath_title;
708 CLOSE c_father_name;
709
710 OPEN c_employee_address(l_person_id);
711 FETCH c_employee_address INTO l_employee_address,l_employee_address_type;
712 CLOSE c_employee_address;
713
714 OPEN c_phone(l_person_id);
715 FETCH c_phone INTO l_phone_no,l_phone_type;
716 CLOSE c_phone;
717 pay_in_utils.set_location(g_debug,l_procedure, 30);
718 --
719 -- Bug 4621622 : Added this code to handle termination case
720 --
721 IF p_effective_start_date > p_effective_end_date THEN
722 l_effective_end_date := fnd_date.string_to_date('31-MAR-' || TO_CHAR(add_months(p_effective_start_date,12),'YYYY'),'DD-MM-YYYY');
723 ELSE
724 l_effective_end_date := p_effective_end_date;
725 END IF;
726 --
727 -- Bug 4621622 changes end
728 --
729 pay_in_utils.set_location(g_debug,l_procedure, 40);
730
731 pay_action_information_api.create_action_information
732 (p_action_context_id => p_arc_asg_action_id
733 ,p_action_context_type => 'AAP'
734 ,p_action_information_category => 'IN_EOY_PERSON'
735 ,p_source_id => p_run_asg_action_id
736 ,p_effective_date => p_prepayment_date
737 ,p_assignment_id => p_assignment_id
738 ,p_action_information1 => l_emp_no
739 ,p_action_information2 => g_year
740 ,p_action_information3 => p_gre_id
741 ,p_action_information4 => l_pan
742 ,p_action_information5 => l_name
743 ,p_action_information6 => l_emp_title
744 ,p_action_information7 => l_father_name
745 ,p_action_information8 => l_emp_fath_title
746 ,p_action_information9 => l_pos
747 ,p_action_information10 => l_dob
748 ,p_action_information11 => l_gender
749 ,p_action_information12 => l_interest
750 ,p_action_information13 => l_person_id
751 ,p_action_information14 => l_employee_address
752 ,p_action_information15 => l_residential_status
753 ,p_action_information16 => l_phone_no
754 ,p_action_information17 => p_effective_start_date
755 -- Bug 4621622 : Changed p_effective_end_date to l_effective_end_date
756 ,p_action_information18 => l_effective_end_date
757 ,p_action_information19 => p_arc_payroll_act_id
758 ,p_action_information20 => p_payroll_run_date
759 ,p_action_information21 => l_email_address
760 ,p_action_information22 => l_job
761 ,p_action_information_id => l_action_info_id
762 ,p_object_version_number => l_ovn
763 );
764
765 IF g_debug THEN
766 pay_in_utils.trace('Employee Name ',l_name);
767 pay_in_utils.trace('Employee Number ',l_emp_no);
768 pay_in_utils.trace('Start Date ',p_effective_start_date);
769 pay_in_utils.trace('End Date ',l_effective_end_date);
770 pay_in_utils.trace('Job ',l_job);
771 pay_in_utils.trace('Position ',l_pos);
772 END IF;
773
774 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
775
776 END archive_person_data;
777
778 --------------------------------------------------------------------------
779 -- --
780 -- Name : BALANCE_DIFFERENCE --
781 -- Type : PROCEDURE --
782 -- Access : Public --
783 -- Description : This procedure determines the balance difference. --
784 -- Parameters : --
785 -- IN : p_arc_pay_action_id NUMBER --
786 -- p_gre_id NUMBER --
787 -- p_effective_end_date DATE --
788 -- OUT : N/A --
789 -- --
790 -- Change History : --
791 --------------------------------------------------------------------------
792 -- Rev# Date Userid Description --
793 --------------------------------------------------------------------------
794 -- 115.0 09-SEP-2005 aaagarwa Initial Version --
795 --------------------------------------------------------------------------
796 PROCEDURE balance_difference(g_result_table1 IN pay_balance_pkg.t_detailed_bal_out_tab
797 ,g_result_table2 IN pay_balance_pkg.t_detailed_bal_out_tab
798 ,g_result_table IN OUT NOCOPY pay_balance_pkg.t_detailed_bal_out_tab
799 )
800 IS
801 l_message VARCHAR2(255);
802 l_procedure VARCHAR2(100);
803
804 BEGIN
805
806 l_procedure := g_package ||'balance_difference';
807 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
808
809 FOR i IN 1..GREATEST(g_result_table1.COUNT,g_result_table2.COUNT)
810 LOOP
811 g_result_table(i).balance_value :=
812 NVL(g_result_table1(i).balance_value,0)
813 - NVL(g_result_table2(i).balance_value,0);
814 END LOOP;
815 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
816
817 END;
818 --------------------------------------------------------------------------
819 -- --
820 -- Name : ARCHIVE_BALANCES --
821 -- Type : PROCEDURE --
822 -- Access : Public --
823 -- Description : This generic procedure archives the balances based --
824 -- on the Source Text 2 --
825 -- Parameters : --
826 -- IN : p_arc_pay_action_id NUMBER --
827 -- p_gre_id NUMBER --
828 -- p_effective_end_date DATE --
829 -- OUT : N/A --
830 -- --
831 -- Change History : --
832 --------------------------------------------------------------------------
833 -- Rev# Date Userid Description --
834 --------------------------------------------------------------------------
835 -- 115.0 23-MAY-2005 aaagarwa Initial Version --
836 --------------------------------------------------------------------------
837 PROCEDURE archive_balances(p_run_asg_action_id IN NUMBER
838 ,pre_gre_asg_act_id IN NUMBER DEFAULT NULL
839 ,p_arc_asg_action_id IN NUMBER
840 ,p_gre_id IN NUMBER
841 ,p_action_inf_category IN VARCHAR2
842 ,p_balance_name IN VARCHAR2
843 ,p_balance_name1 IN VARCHAR2 DEFAULT NULL
844 ,p_balance_name2 IN VARCHAR2 DEFAULT NULL
845 ,p_balance_name3 IN VARCHAR2 DEFAULT NULL
846 ,p_balance_dimension IN VARCHAR2
847 ,p_balance_dimension1 IN VARCHAR2 DEFAULT NULL
848 ,p_balance_dimension2 IN VARCHAR2 DEFAULT NULL
849 ,p_balance_dimension3 IN VARCHAR2 DEFAULT NULL
850 ,g_context_table IN OUT NOCOPY pay_balance_pkg.t_context_tab
851 ,g_result_table IN OUT NOCOPY pay_balance_pkg.t_detailed_bal_out_tab
852 ,g_result_table1 IN OUT NOCOPY pay_balance_pkg.t_detailed_bal_out_tab
853 ,g_result_table2 IN OUT NOCOPY pay_balance_pkg.t_detailed_bal_out_tab
854 ,g_result_table3 IN OUT NOCOPY pay_balance_pkg.t_detailed_bal_out_tab
855 ,g_balance_value_tab IN OUT NOCOPY pay_balance_pkg.t_balance_value_tab
856 )
857 IS
858
859 l_action_info_id NUMBER;
860 l_ovn NUMBER;
861 l_result_table1 pay_balance_pkg.t_detailed_bal_out_tab;
862 l_result_table2 pay_balance_pkg.t_detailed_bal_out_tab;
863 l_message VARCHAR2(255);
864 l_procedure VARCHAR2(100);
865 l_result_table4 pay_balance_pkg.t_detailed_bal_out_tab;
866
867
868 BEGIN
869
870 l_procedure := g_package ||'archive_balances';
871 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
872
873
874 IF g_debug THEN
875 pay_in_utils.trace('Run Asg Action id ',p_run_asg_action_id);
876 pay_in_utils.trace('Prev GRE Asg action id ',pre_gre_asg_act_id);
877 pay_in_utils.trace('Archive Asg Action id ',p_arc_asg_action_id);
878 pay_in_utils.trace('GRE id ',p_gre_id);
879 pay_in_utils.trace('Action Info Category ',p_action_inf_category);
880 pay_in_utils.trace('Balance name ',p_balance_name);
881 pay_in_utils.trace('Balance name1 ',p_balance_name1);
882 pay_in_utils.trace('Balance name2 ',p_balance_name2);
883 pay_in_utils.trace('Balance name31 ',p_balance_name3);
884 pay_in_utils.trace('Dimension Name ',p_balance_dimension);
885 pay_in_utils.trace('Dimension Name1 ',p_balance_dimension1);
886 pay_in_utils.trace('Dimension Name2 ',p_balance_dimension2);
887 pay_in_utils.trace('Dimension Name3 ',p_balance_dimension3);
888
889 END IF;
890
891 /* Allowance Advance functionality Start */
892 IF (p_action_inf_category ='IN_EOY_ALLOW') THEN
893 pay_in_utils.set_location(g_debug,l_procedure, 21);
894
895 g_balance_value_tab(1).defined_balance_id :=
896 pay_in_tax_utils.get_defined_balance('Adjusted Advance for Allowances','_ASG_COMP_YTD');
897
898 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
899 ,p_defined_balance_lst => g_balance_value_tab
900 ,p_context_lst => g_context_table
901 ,p_output_table => l_result_table1
902 );
903 pay_in_utils.set_location(g_debug,l_procedure, 22);
904
905 IF pre_gre_asg_act_id IS NOT NULL
906 THEN
907 pay_in_utils.set_location(g_debug,l_procedure, 30);
908 pay_balance_pkg.get_value(p_assignment_action_id => pre_gre_asg_act_id
909 ,p_defined_balance_lst => g_balance_value_tab
910 ,p_context_lst => g_context_table
911 ,p_output_table => l_result_table2
912 );
913 balance_difference(l_result_table1,l_result_table2,l_result_table4);
914 ELSE
915 l_result_table4 := l_result_table1;
916 END IF;
917 pay_in_utils.set_location(g_debug,l_procedure, 23);
918
919 l_result_table1.DELETE;
920 l_result_table2.DELETE;
921
922
923 END IF;
924 pay_in_utils.set_location(g_debug,l_procedure, 25);
925 /* Allowance Advance functionality End*/
926
927 g_balance_value_tab(1).defined_balance_id :=
928 pay_in_tax_utils.get_defined_balance(p_balance_name,p_balance_dimension);
929
930 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
931 ,p_defined_balance_lst => g_balance_value_tab
932 ,p_context_lst => g_context_table
933 ,p_output_table => l_result_table1--g_result_table
934 );
935
936 pay_in_utils.set_location(g_debug,l_procedure, 20);
937
938 IF pre_gre_asg_act_id IS NOT NULL
939 THEN
940 pay_in_utils.set_location(g_debug,l_procedure, 30);
941 pay_balance_pkg.get_value(p_assignment_action_id => pre_gre_asg_act_id
942 ,p_defined_balance_lst => g_balance_value_tab
943 ,p_context_lst => g_context_table
944 ,p_output_table => l_result_table2
945 );
946 balance_difference(l_result_table1,l_result_table2,g_result_table);
947 ELSE
948 g_result_table := l_result_table1;
949 END IF;
950
951 pay_in_utils.set_location(g_debug,l_procedure, 40);
952
953 IF (p_balance_name1 IS NOT NULL)
954 THEN
955 pay_in_utils.set_location(g_debug,l_procedure, 50);
956 g_balance_value_tab(1).defined_balance_id :=
957 pay_in_tax_utils.get_defined_balance(p_balance_name1,p_balance_dimension1);
958
959 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
960 ,p_defined_balance_lst => g_balance_value_tab
961 ,p_context_lst => g_context_table
962 ,p_output_table => l_result_table1--g_result_table1
963 );
964 IF pre_gre_asg_act_id IS NOT NULL AND p_action_inf_category = 'IN_EOY_PERQ'
965 THEN
966 pay_in_utils.set_location(g_debug,l_procedure, 60);
967 pay_balance_pkg.get_value(p_assignment_action_id => pre_gre_asg_act_id
968 ,p_defined_balance_lst => g_balance_value_tab
969 ,p_context_lst => g_context_table
970 ,p_output_table => l_result_table2
971 );
972 balance_difference(l_result_table1,l_result_table2,g_result_table1);
973 ELSE
974 pay_in_utils.set_location(g_debug,l_procedure, 70);
975 g_result_table1 := l_result_table1;
976 END IF;
977 END IF;
978 pay_in_utils.set_location(g_debug,l_procedure, 80);
979
980 IF (p_balance_name2 IS NOT NULL)
981 THEN
982 pay_in_utils.set_location(g_debug,l_procedure, 90);
983 g_balance_value_tab(1).defined_balance_id :=
984 pay_in_tax_utils.get_defined_balance(p_balance_name2,p_balance_dimension2);
985
986 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
987 ,p_defined_balance_lst => g_balance_value_tab
988 ,p_context_lst => g_context_table
989 ,p_output_table => l_result_table1--g_result_table2
990 );
991 IF pre_gre_asg_act_id IS NOT NULL
992 THEN
993 pay_in_utils.set_location(g_debug,l_procedure, 100);
994 pay_balance_pkg.get_value(p_assignment_action_id => pre_gre_asg_act_id
995 ,p_defined_balance_lst => g_balance_value_tab
996 ,p_context_lst => g_context_table
997 ,p_output_table => l_result_table2
998 );
999 balance_difference(l_result_table1,l_result_table2,g_result_table2);
1000 ELSE
1001 g_result_table2 := l_result_table1;
1002 END IF;
1003 END IF;
1004 pay_in_utils.set_location(g_debug,l_procedure, 110);
1005
1006 IF (p_balance_name3 IS NOT NULL)
1007 THEN
1008 pay_in_utils.set_location(g_debug,l_procedure, 120);
1009 g_balance_value_tab(1).defined_balance_id :=
1010 pay_in_tax_utils.get_defined_balance(p_balance_name3,p_balance_dimension3);
1011
1012 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
1013 ,p_defined_balance_lst => g_balance_value_tab
1014 ,p_context_lst => g_context_table
1015 ,p_output_table => g_result_table3
1016 );
1017 END IF;
1018
1019 pay_in_utils.set_location(g_debug,l_procedure, 130);
1020 pay_in_utils.trace('**************************************************','********************');
1021 IF (p_action_inf_category = 'IN_EOY_ALLOW')
1022 THEN
1023 pay_in_utils.set_location(g_debug,l_procedure, 140);
1024 FOR i IN 1..g_context_table.COUNT
1025 LOOP
1026 IF ((g_result_table(i).balance_value <> 0)
1027 OR(NVL(g_result_table1(i).balance_value,0) <> 0)
1028 OR(NVL(g_result_table2(i).balance_value,0) <> 0)
1029 OR(NVL(g_result_table3(i).balance_value,0) <> 0)
1030 OR(NVL(l_result_table4(i).balance_value,0) <> 0)
1031 )
1032 THEN
1033 pay_action_information_api.create_action_information
1034 (p_action_context_id => p_arc_asg_action_id
1035 ,p_action_context_type => 'AAP'
1036 ,p_action_information_category => p_action_inf_category
1037 ,p_source_id => p_run_asg_action_id
1038 ,p_action_information1 => g_context_table(i).source_text2
1039 ,p_action_information2 => (NVL(g_result_table(i).balance_value,0) + NVL(l_result_table4(i).balance_value,0) )
1040 ,p_action_information3 => NVL(g_result_table1(i).balance_value,0)
1041 ,p_action_information4 => NVL(g_result_table2(i).balance_value,0)
1042 ,p_action_information5 => NVL(g_result_table3(i).balance_value,0)
1043 ,p_action_information_id => l_action_info_id
1044 ,p_object_version_number => l_ovn
1045 );
1046 IF g_debug THEN
1047 pay_in_utils.trace('ALLOWANCE Name ',g_context_table(i).source_text2);
1048 pay_in_utils.trace('ALLOWANCE Amt ',NVL(g_result_table(i).balance_value,0));
1049 pay_in_utils.trace('ALLOWANCE Taxable Amt ',NVL(g_result_table1(i).balance_value,0));
1050 pay_in_utils.trace('ALLOWANCE Std Amt ',NVL(g_result_table2(i).balance_value,0));
1051 pay_in_utils.trace('ALLOWANCE Std Taxable Amt ',NVL(g_result_table3(i).balance_value,0));
1052 END IF;
1053
1054 END IF;
1055 END LOOP;
1056 ELSIF (p_action_inf_category = 'IN_EOY_PERQ')
1057 THEN
1058 pay_in_utils.set_location(g_debug,l_procedure, 150);
1059 FOR i IN 1..g_context_table.COUNT
1060 LOOP
1061 IF ((g_result_table(i).balance_value <> 0)
1062 OR(NVL(g_result_table1(i).balance_value,0) <> 0)
1063 )
1064 THEN
1065 pay_action_information_api.create_action_information
1066 (p_action_context_id => p_arc_asg_action_id
1067 ,p_action_context_type => 'AAP'
1068 ,p_action_information_category => p_action_inf_category
1069 ,p_source_id => p_run_asg_action_id
1070 ,p_action_information1 => g_context_table(i).source_text2
1071 ,p_action_information2 => NVL(g_result_table(i).balance_value,0)
1072 ,p_action_information3 => NVL(g_result_table1(i).balance_value,0)
1073 ,p_action_information_id => l_action_info_id
1074 ,p_object_version_number => l_ovn
1075 );
1076
1077 IF g_debug THEN
1078 pay_in_utils.trace('PERQ Name ',g_context_table(i).source_text2);
1079 pay_in_utils.trace('PERQ Taxable Amt ',NVL(g_result_table(i).balance_value,0));
1080 pay_in_utils.trace('PERQ Employee Contribution ',NVL(g_result_table1(i).balance_value,0));
1081 END IF;
1082
1083 END IF;
1084 END LOOP;
1085 ELSE
1086 pay_in_utils.set_location(g_debug,l_procedure, 160);
1087 FOR i IN 1..g_context_table.COUNT
1088 LOOP
1089 IF (g_result_table(i).balance_value <> 0)
1090 THEN
1091 pay_action_information_api.create_action_information
1092 (p_action_context_id => p_arc_asg_action_id
1093 ,p_action_context_type => 'AAP'
1094 ,p_action_information_category => p_action_inf_category
1095 ,p_source_id => p_run_asg_action_id
1096 ,p_action_information1 => g_context_table(i).source_text2
1097 ,p_action_information2 => g_result_table(i).balance_value
1098 ,p_action_information_id => l_action_info_id
1099 ,p_object_version_number => l_ovn
1100 );
1101
1102 IF g_debug THEN
1103 pay_in_utils.trace('Oth Balance name ',g_context_table(i).source_text2);
1104 pay_in_utils.trace('Oth Balance Value ',g_result_table(i).balance_value);
1105 END IF;
1106
1107
1108 END IF;
1109 END LOOP;
1110 END IF;
1111 pay_in_utils.trace('**************************************************','********************');
1112 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 170);
1113
1114 END archive_balances;
1115 --------------------------------------------------------------------------
1116 -- --
1117 -- Name : ARCHIVE_VIA_DETAILS --
1118 -- Type : PROCEDURE --
1119 -- Access : Public --
1120 -- Description : This procedure archives the Chapter VI A related --
1121 -- balance details --
1122 -- Parameters : --
1123 -- IN : p_arc_pay_action_id NUMBER --
1124 -- p_gre_id NUMBER --
1125 -- p_effective_end_date DATE --
1126 -- OUT : N/A --
1127 -- --
1128 -- Change History : --
1129 --------------------------------------------------------------------------
1130 -- Rev# Date Userid Description --
1131 --------------------------------------------------------------------------
1132 -- 115.0 23-MAY-2005 aaagarwa Initial Version --
1133 --------------------------------------------------------------------------
1134 PROCEDURE archive_via_details(p_run_asg_action_id IN NUMBER
1135 ,p_arc_asg_action_id IN NUMBER
1136 ,p_gre_id IN NUMBER
1137 ,p_assignment_id IN NUMBER
1138 ,p_payroll_date IN DATE
1139 )
1140 IS
1141
1142 CURSOR c_defined_balance_id--80D,80DD,80DDB,80G,80GGA
1143 IS
1144 SELECT pdb.defined_balance_id balance_id
1145 ,pbt.balance_name balance_name
1146 FROM pay_balance_types pbt
1147 ,pay_balance_dimensions pbd
1148 ,pay_defined_balances pdb
1149 WHERE pbt.balance_name IN(
1150 'F16 Deductions Sec 80D'
1151 ,'F16 Deductions Sec 80DD'
1152 ,'F16 Deductions Sec 80DDB'
1153 ,'F16 Deductions Sec 80G'
1154 ,'F16 Deductions Sec 80GGA'
1155 ,'F16 Deductions Sec 80CCF'
1156 )
1157 AND pbd.dimension_name='_ASG_LE_DE_PTD'
1158 AND pbt.legislation_code = 'IN'
1159 AND pbd.legislation_code = 'IN'
1160 AND pbt.balance_type_id = pdb.balance_type_id
1161 AND pbd.balance_dimension_id = pdb.balance_dimension_id
1162 ORDER BY pbt.balance_name;
1163
1164 CURSOR c_def_balance_id--80E,80GG and 80U
1165 IS
1166 SELECT pdb.defined_balance_id balance_id
1167 ,pbt.balance_name balance_name
1168 FROM pay_balance_types pbt
1169 ,pay_balance_dimensions pbd
1170 ,pay_defined_balances pdb
1171 WHERE pbt.balance_name IN(
1172 'F16 Deductions Sec 80CCE'
1173 ,'F16 Deductions Sec 80E'
1174 ,'F16 Deductions Sec 80GG'
1175 ,'F16 Deductions Sec 80U'
1176 ,'F16 Employee PF Contribution'
1177 ,'F16 Total Chapter VI A Deductions'
1178 )
1179 AND pbd.dimension_name='_ASG_LE_DE_PTD'
1180 AND pbt.legislation_code = 'IN'
1181 AND pbd.legislation_code = 'IN'
1182 AND pbt.balance_type_id = pdb.balance_type_id
1183 AND pbd.balance_dimension_id = pdb.balance_dimension_id
1184 ORDER BY pbt.balance_name;
1185
1186 g_bal_name_tab t_bal_name_tab;
1187 g_balance_value_tab pay_balance_pkg.t_balance_value_tab;
1188 g_balance_value_tab1 pay_balance_pkg.t_balance_value_tab;
1189 g_context_table pay_balance_pkg.t_context_tab;
1190 g_result_table pay_balance_pkg.t_detailed_bal_out_tab;
1191 g_result_table1 pay_balance_pkg.t_detailed_bal_out_tab;
1192 g_result_table2 pay_balance_pkg.t_detailed_bal_out_tab;
1193 g_result_table3 pay_balance_pkg.t_detailed_bal_out_tab;
1194
1195 i NUMBER;
1196 l_defined_balance_id NUMBER;
1197 l_action_info_id NUMBER;
1198 l_ovn NUMBER;
1199 l_pf_contr NUMBER;
1200 l_da_gross NUMBER;
1201 l_da_qa_amt NUMBER;
1202 l_scss_qa_amt NUMBER;
1203 l_scss_gross NUMBER;
1204 l_li_gross NUMBER;
1205 l_li_qa_amt NUMBER;
1206 l_pension_qa_amt NUMBER;
1207 l_pension_gross NUMBER;
1208 l_balance_defined_id NUMBER;
1209 l_ytd_val NUMBER;
1210 l_ptd_val NUMBER;
1211 l_classification hr_organization_information.org_information3%TYPE;
1212 l_message VARCHAR2(255);
1213 l_procedure VARCHAR2(100);
1214 l_80ccd_gross NUMBER ;
1215 l_80ccd_qa_amt NUMBER ;
1216 BEGIN
1217 --Qualifying Amount determination and archival for 80E,80GG and 80U
1218
1219 l_procedure := g_package ||'archive_via_details';
1220 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1221
1222
1223 i := 1;
1224 g_bal_name_tab.DELETE;
1225
1226
1227 FOR c_rec IN c_def_balance_id
1228 LOOP
1229 g_balance_value_tab(i).defined_balance_id := c_rec.balance_id;
1230 g_bal_name_tab(i).balance_name := c_rec.balance_name;
1231 i := i + 1;
1232 END LOOP;
1233
1234 pay_in_utils.set_location(g_debug,l_procedure, 20);
1235
1236 g_context_table(1).tax_unit_id := p_gre_id;
1237
1238 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
1239 ,p_defined_balance_lst => g_balance_value_tab
1240 ,p_context_lst => g_context_table
1241 ,p_output_table => g_result_table
1242 );
1243
1244 pay_in_utils.set_location(g_debug,l_procedure, 30);
1245 pay_in_utils.trace('**************************************************','********************');
1246 FOR i IN 1..g_balance_value_tab.COUNT
1247 LOOP
1248 IF (g_result_table(i).balance_value <> 0)
1249 THEN
1250 pay_in_utils.set_location(g_debug,l_procedure, 40);
1251 pay_action_information_api.create_action_information
1252 (p_action_context_id => p_arc_asg_action_id
1253 ,p_action_context_type => 'AAP'
1254 ,p_action_information_category => 'IN_EOY_VIA'
1255 ,p_source_id => p_run_asg_action_id
1256 ,p_action_information1 => g_bal_name_tab(i).balance_name
1257 ,p_action_information2 => g_result_table(i).balance_value
1258 ,p_action_information_id => l_action_info_id
1259 ,p_object_version_number => l_ovn
1260 );
1261 IF g_debug THEN
1262 pay_in_utils.trace('VIA Balance name ',g_bal_name_tab(i).balance_name);
1263 pay_in_utils.trace('VIA Balance Value ',g_result_table(i).balance_value);
1264 END IF;
1265
1266 END IF;
1267 END LOOP;
1268
1269 --Qualifying Amount determination for 80D,80DD,80DDB,80G,80GGA
1270 i := 1;
1271 g_bal_name_tab.DELETE;
1272 g_balance_value_tab.DELETE;
1273 g_result_table.DELETE;
1274
1275 pay_in_utils.set_location(g_debug,l_procedure, 50);
1276
1277 FOR c_rec IN c_defined_balance_id
1278 LOOP
1279 g_balance_value_tab(i).defined_balance_id := c_rec.balance_id;
1280 g_bal_name_tab(i).balance_name := c_rec.balance_name;
1281 i := i + 1;
1282 END LOOP;
1283
1284
1285
1286 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
1287 ,p_defined_balance_lst => g_balance_value_tab
1288 ,p_context_lst => g_context_table
1289 ,p_output_table => g_result_table
1290 );
1291 pay_in_utils.set_location(g_debug,l_procedure, 60);
1292
1293 --Gross Amount determination for 80D,80DD,80DDB,80G,80GGA
1294
1295 g_result_table1.DELETE;
1296 g_balance_value_tab1.DELETE;
1297 g_context_table.DELETE;
1298
1299 g_context_table(1).source_text2 := 'Infrastructure Bonds'; -- 80CCF
1300 g_context_table(2).source_text2 := 'Medical Insurance'; -- 80D
1301 g_context_table(3).source_text2 := 'Disabled Dependents'; -- 80DD
1302 g_context_table(4).source_text2 := 'Disease Treatment'; -- 80DDB
1303 g_context_table(5).source_text2 := 'Donations'; -- 80G
1304 g_context_table(6).source_text2 := 'Research Donation'; -- 80GGA
1305
1306 FOR i IN 1..6
1307 LOOP
1308 g_context_table(i).tax_unit_id := p_gre_id;
1309 END LOOP;
1310
1311 g_balance_value_tab1(1).defined_balance_id :=
1312 pay_in_tax_utils.get_defined_balance('Gross Chapter VIA Deductions','_ASG_LE_COMP_DE_PTD');
1313
1314 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
1315 ,p_defined_balance_lst => g_balance_value_tab1
1316 ,p_context_lst => g_context_table
1317 ,p_output_table => g_result_table1
1318 );
1319
1320 pay_in_utils.set_location(g_debug,l_procedure, 70);
1321
1322 --Archiving the QA and Gross Amount in the same record for 80D,80DD,80DDB,80G,80GGA
1323 FOR i IN 1..g_balance_value_tab.COUNT
1324 LOOP
1325 IF ((g_result_table(i).balance_value <> 0)OR(g_result_table1(i).balance_value <> 0))
1326 THEN
1327 pay_in_utils.set_location(g_debug,l_procedure, 80);
1328 pay_action_information_api.create_action_information
1329 (p_action_context_id => p_arc_asg_action_id
1330 ,p_action_context_type => 'AAP'
1331 ,p_action_information_category => 'IN_EOY_VIA'
1332 ,p_source_id => p_run_asg_action_id
1333 ,p_action_information1 => g_bal_name_tab(i).balance_name
1334 ,p_action_information2 => g_result_table(i).balance_value
1335 ,p_action_information3 => g_result_table1(i).balance_value
1336 ,p_action_information_id => l_action_info_id
1337 ,p_object_version_number => l_ovn
1338 );
1339
1340 IF g_debug THEN
1341 pay_in_utils.trace('VIA Balance name ',g_bal_name_tab(i).balance_name);
1342 pay_in_utils.trace('VIA Qualifying Amt ',g_result_table(i).balance_value);
1343 pay_in_utils.trace('VIA Gross Amt ',g_result_table1(i).balance_value);
1344 END IF;
1345
1346
1347 END IF;
1348 END LOOP;
1349
1350 --Archival for 80CCE elements start here
1351 g_balance_value_tab.DELETE;
1352 g_context_table.DELETE;
1353 g_result_table1.DELETE;
1354 g_result_table.DELETE;
1355
1356 g_context_table(1).source_text2 := 'House Loan Repayment';
1357 g_context_table(2).source_text2 := 'Public Provident Fund';
1358 g_context_table(3).source_text2 := 'Interest on NSC';
1359 g_context_table(4).source_text2 := 'Mutual Fund or UTI';
1360 g_context_table(5).source_text2 := 'National Housing Bank';
1361 g_context_table(6).source_text2 := 'ULIP';
1362 g_context_table(7).source_text2 := 'Notified Annuity Plan';
1363 g_context_table(8).source_text2 := 'Notified Pension Fund';
1364 g_context_table(9).source_text2 := 'Public Sector Scheme';
1365 g_context_table(10).source_text2 := 'Superannuation Fund';
1366 g_context_table(11).source_text2 := 'Infrastructure Bonds';
1367 g_context_table(12).source_text2 := 'NSC';
1368 g_context_table(13).source_text2 := 'Deposits in Govt. Security';
1369 g_context_table(14).source_text2 := 'Notified Deposit Scheme';
1370 g_context_table(15).source_text2 := 'Approved Shares or Debentures';
1371 g_context_table(16).source_text2 := 'Approved Mutual Fund';
1372 g_context_table(17).source_text2 := 'Tuition fee';
1373 g_context_table(18).source_text2 := 'Fixed Deposits';
1374 g_context_table(19).source_text2 := 'Five Year Post Office Time Deposit Account';
1375 g_context_table(20).source_text2 := 'NABARD Bank Deposits';
1376 g_context_table(21).source_text2 := 'Stamp Duty for House Property';
1377 g_context_table(22).source_text2 := 'Registration Fees for House Property';
1378 g_context_table(23).source_text2 := 'GSLI';
1379
1380 --Added GPF and GIS for Bug 14796643
1381 g_context_table(24).source_text2 := 'General Provident Fund';
1382 g_context_table(25).source_text2 := 'General Insurance Scheme';
1383
1384
1385
1386 FOR i IN 1..25
1387 LOOP
1388 g_context_table(i).tax_unit_id := p_gre_id;
1389 END LOOP;
1390
1391
1392 archive_balances(p_run_asg_action_id => p_run_asg_action_id
1393 ,p_arc_asg_action_id => p_arc_asg_action_id
1394 ,p_gre_id => p_gre_id
1395 ,p_action_inf_category => 'IN_EOY_VIA'
1396 ,p_balance_name => 'Deductions under Section 80CCE'
1397 ,p_balance_dimension => '_ASG_LE_COMP_DE_PTD'
1398 ,g_context_table => g_context_table
1399 ,g_result_table => g_result_table
1400 ,g_result_table1 => g_result_table1
1401 ,g_result_table2 => g_result_table2
1402 ,g_result_table3 => g_result_table3
1403 ,g_balance_value_tab => g_balance_value_tab
1404 );
1405
1406 pay_in_utils.set_location(g_debug,l_procedure, 90);
1407
1408 --Archive record for Deferred Anuity and Life Insurance Premium
1409 g_context_table.DELETE;
1410 g_result_table1.DELETE;
1411 g_result_table2.DELETE;
1412 g_result_table3.DELETE;
1413 g_result_table.DELETE;
1414 g_balance_value_tab.DELETE;
1415 g_balance_value_tab1.DELETE;
1416
1417 g_context_table(1).source_text2 := 'Life Insurance Premium';
1418 g_context_table(2).source_text2 := 'Deferred Annuity';
1419 g_context_table(3).source_text2 := 'Pension Fund 80CCC';
1420 g_context_table(4).source_text2 := 'Senior Citizens Savings Scheme';
1421
1422 FOR i IN 1..4
1423 LOOP
1424 g_context_table(i).tax_unit_id := p_gre_id;
1425 END LOOP;
1426
1427
1428 g_balance_value_tab(1).defined_balance_id :=
1429 pay_in_tax_utils.get_defined_balance('Deductions under Section 80CCE','_ASG_LE_COMP_DE_PTD');
1430
1431 -- Qualifying Amounts for Life Insurance and Deferred Annuity obtained
1432 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
1433 ,p_defined_balance_lst => g_balance_value_tab
1434 ,p_context_lst => g_context_table
1435 ,p_output_table => g_result_table
1436 );
1437
1438 l_li_qa_amt := NVL(g_result_table(1).balance_value,0);
1439 l_da_qa_amt := NVL(g_result_table(2).balance_value,0);
1440 l_pension_qa_amt := NVL(g_result_table(3).balance_value,0);
1441 l_scss_qa_amt := NVL(g_result_table(4).balance_value,0);
1442
1443 --Gross Amount for Life Insurance
1444 g_context_table.DELETE;
1445
1446 pay_in_utils.set_location(g_debug,l_procedure, 100);
1447 g_context_table(1).source_text2 := 'Life Insurance Premium';
1448 g_context_table(1).tax_unit_id := p_gre_id;
1449 g_balance_value_tab1(1).defined_balance_id :=
1450 pay_in_tax_utils.get_defined_balance('Gross Chapter VIA Deductions','_ASG_LE_COMP_DE_PTD');
1451
1452 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
1453 ,p_defined_balance_lst => g_balance_value_tab1
1454 ,p_context_lst => g_context_table
1455 ,p_output_table => g_result_table1
1456 );
1457
1458 l_li_gross := NVL(g_result_table1(1).balance_value,0);
1459
1460 g_balance_value_tab1.DELETE;
1461 g_result_table1.DELETE;
1462
1463 pay_in_utils.set_location(g_debug,l_procedure, 120);
1464 --Gross Amount for Deferred Annuity
1465 g_context_table.DELETE;
1466 g_context_table(1).tax_unit_id := p_gre_id;
1467
1468 g_balance_value_tab1(1).defined_balance_id :=
1469 pay_in_tax_utils.get_defined_balance('Deferred Annuity','_ASG_LE_DE_PTD');
1470
1471 --Gross Amount for Pension Fund 80CCC
1472
1473 g_balance_value_tab1(2).defined_balance_id :=
1474 pay_in_tax_utils.get_defined_balance('Pension Fund','_ASG_LE_DE_PTD');
1475
1476 --Gross Amount for Senior Citizens
1477
1478 g_balance_value_tab1(3).defined_balance_id :=
1479 pay_in_tax_utils.get_defined_balance('Senior Citizens Savings Scheme','_ASG_LE_DE_PTD');
1480
1481
1482 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
1483 ,p_defined_balance_lst => g_balance_value_tab1
1484 ,p_context_lst => g_context_table
1485 ,p_output_table => g_result_table1
1486 );
1487
1488
1489
1490 l_da_gross := NVL(g_result_table1(1).balance_value,0);
1491
1492 l_pension_gross := NVL(g_result_table1(2).balance_value,0);
1493
1494 l_scss_gross := NVL(g_result_table1(3).balance_value,0);
1495
1496
1497 pay_in_utils.set_location(g_debug,l_procedure, 140);
1498
1499 g_balance_value_tab1.DELETE;
1500 g_result_table1.DELETE;
1501 g_context_table.DELETE;
1502
1503 --Gross Amount and Qualifying Amount for 80CCD
1504 g_context_table(1).tax_unit_id := p_gre_id;
1505 g_balance_value_tab1(1).defined_balance_id :=
1506 pay_in_tax_utils.get_defined_balance('F16 ER Pension Contribution','_ASG_LE_DE_PTD');
1507 g_balance_value_tab1(2).defined_balance_id :=
1508 pay_in_tax_utils.get_defined_balance('F16 Section 80CCD','_ASG_LE_DE_PTD');
1509
1510 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
1511 ,p_defined_balance_lst => g_balance_value_tab1
1512 ,p_context_lst => g_context_table
1513 ,p_output_table => g_result_table1
1514 );
1515
1516 l_80ccd_gross := NVL(g_result_table1(1).balance_value,0);
1517 l_80ccd_qa_amt := NVL(g_result_table1(2).balance_value,0);
1518
1519 g_balance_value_tab1.DELETE;
1520 g_result_table1.DELETE;
1521 g_context_table.DELETE;
1522
1523 --Archival of Deferred Annuity, Pension Fund 80CCC and Life Insurance starts
1524 IF (l_li_qa_amt <> 0 OR l_li_gross <> 0)
1525 THEN
1526 pay_action_information_api.create_action_information
1527 (p_action_context_id => p_arc_asg_action_id
1528 ,p_action_context_type => 'AAP'
1529 ,p_action_information_category => 'IN_EOY_VIA'
1530 ,p_source_id => p_run_asg_action_id
1531 ,p_action_information1 => 'Life Insurance Premium'
1532 ,p_action_information2 => l_li_qa_amt
1533 ,p_action_information3 => l_li_gross
1534 ,p_action_information_id => l_action_info_id
1535 ,p_object_version_number => l_ovn
1536 );
1537 IF g_debug THEN
1538 pay_in_utils.trace('VIA LIC Qualifying Amt ',l_li_qa_amt);
1539 pay_in_utils.trace('VIA LIC Gross Amt ',l_li_gross);
1540 END IF;
1541 END IF;
1542
1543 IF (l_da_gross <> 0 OR l_da_qa_amt <> 0)
1544 THEN
1545 pay_action_information_api.create_action_information
1546 (p_action_context_id => p_arc_asg_action_id
1547 ,p_action_context_type => 'AAP'
1548 ,p_action_information_category => 'IN_EOY_VIA'
1549 ,p_source_id => p_run_asg_action_id
1550 ,p_action_information1 => 'Deferred Annuity'
1551 ,p_action_information2 => l_da_qa_amt
1552 ,p_action_information3 => l_da_gross
1553 ,p_action_information_id => l_action_info_id
1554 ,p_object_version_number => l_ovn
1555 );
1556 IF g_debug THEN
1557 pay_in_utils.trace('VIA Deferred Annuity Qualifying Amt ',l_da_qa_amt);
1558 pay_in_utils.trace('VIA Deferred Annuity Gross Amt ',l_da_gross);
1559 END IF;
1560
1561 END IF;
1562
1563 IF (l_pension_gross <> 0 OR l_pension_qa_amt <> 0)
1564 THEN
1565 pay_action_information_api.create_action_information
1566 (p_action_context_id => p_arc_asg_action_id
1567 ,p_action_context_type => 'AAP'
1568 ,p_action_information_category => 'IN_EOY_VIA'
1569 ,p_source_id => p_run_asg_action_id
1570 ,p_action_information1 => 'Pension Fund 80CCC'
1571 ,p_action_information2 => l_pension_qa_amt
1572 ,p_action_information3 => l_pension_gross
1573 ,p_action_information_id => l_action_info_id
1574 ,p_object_version_number => l_ovn
1575 );
1576
1577 IF g_debug THEN
1578 pay_in_utils.trace('VIA 80CCC Qualifying Amt ',l_li_qa_amt);
1579 pay_in_utils.trace('VIA 80CCC Gross Amt ',l_li_gross);
1580 END IF;
1581
1582 END IF;
1583
1584 IF (l_scss_gross <> 0 OR l_scss_qa_amt <> 0)
1585 THEN
1586 pay_action_information_api.create_action_information
1587 (p_action_context_id => p_arc_asg_action_id
1588 ,p_action_context_type => 'AAP'
1589 ,p_action_information_category => 'IN_EOY_VIA'
1590 ,p_source_id => p_run_asg_action_id
1591 ,p_action_information1 => 'Senior Citizens Savings Scheme'
1592 ,p_action_information2 => l_scss_qa_amt
1593 ,p_action_information3 => l_scss_gross
1594 ,p_action_information_id => l_action_info_id
1595 ,p_object_version_number => l_ovn
1596 );
1597
1598 IF g_debug THEN
1599 pay_in_utils.trace('Senior Citizens Savings Scheme Qualifying Amt ',l_scss_qa_amt);
1600 pay_in_utils.trace('Senior Citizens Savings Scheme Gross Amt ',l_scss_gross);
1601 END IF;
1602
1603 END IF;
1604
1605 --Archival of 80CCD starts
1606 IF (l_80ccd_qa_amt <> 0 OR l_80ccd_gross <> 0)
1607 THEN
1608 pay_action_information_api.create_action_information
1609 (p_action_context_id => p_arc_asg_action_id
1610 ,p_action_context_type => 'AAP'
1611 ,p_action_information_category => 'IN_EOY_VIA'
1612 ,p_source_id => p_run_asg_action_id
1613 ,p_action_information1 => 'Govt Pension Scheme 80CCD'
1614 ,p_action_information2 => l_80ccd_qa_amt
1615 ,p_action_information3 => l_80ccd_gross
1616 ,p_action_information_id => l_action_info_id
1617 ,p_object_version_number => l_ovn
1618 );
1619 IF g_debug THEN
1620 pay_in_utils.trace('VIA Deduction under Section 80CCD Qualifying Amt ',l_80ccd_qa_amt);
1621 pay_in_utils.trace('VIA Deduction under Section 80CCD Gross Amt ',l_80ccd_gross);
1622 END IF;
1623 END IF;
1624 pay_in_utils.trace('**************************************************','********************');
1625 pay_in_utils.set_location(g_debug,l_procedure, 150);
1626
1627 END archive_via_details;
1628 --------------------------------------------------------------------------
1629 -- --
1630 -- Name : ARCHIVE_ALLOWANCES --
1631 -- Type : PROCEDURE --
1632 -- Access : Public --
1633 -- Description : This procedure archives the allowance related values--
1634 -- Parameters : --
1635 -- IN : p_arc_pay_action_id NUMBER --
1636 -- p_gre_id NUMBER --
1637 -- p_effective_end_date DATE --
1638 -- OUT : N/A --
1639 -- --
1640 -- Change History : --
1641 --------------------------------------------------------------------------
1642 -- Rev# Date Userid Description --
1643 --------------------------------------------------------------------------
1644 -- 115.0 23-MAY-2005 aaagarwa Initial Version --
1645 --------------------------------------------------------------------------
1646 PROCEDURE archive_allowances(p_run_asg_action_id IN NUMBER
1647 ,p_arc_asg_action_id IN NUMBER
1648 ,p_gre_id IN NUMBER
1649 ,pre_gre_asg_act_id IN NUMBER DEFAULT NULL
1650 ,p_flag IN BOOLEAN DEFAULT FALSE
1651 )
1652 IS
1653 CURSOR c_hra
1654 IS
1655 SELECT action_information_id
1656 ,object_version_number
1657 FROM pay_action_information
1658 WHERE action_information_category = 'IN_EOY_ALLOW'
1659 AND source_id = p_run_asg_action_id
1660 AND action_context_id = p_arc_asg_action_id
1661 AND action_information1 = 'House Rent Allowance'
1662 ORDER BY action_information_id DESC;
1663
1664 CURSOR c_comp_name
1665 IS
1666 SELECT pur.row_low_range_or_name name
1667 FROM pay_user_rows_f pur,
1668 pay_user_tables put
1669 WHERE pur.user_table_id = put.user_table_id
1670 AND put.user_table_name = 'IN_ALLOWANCES'
1671 AND put.legislation_code = 'IN'
1672 AND (pur.legislation_code = 'IN' OR pur.business_group_id = g_bg_id)
1673 AND g_start_date BETWEEN pur.effective_start_date AND pur.effective_end_date
1674 ORDER by name ASC;
1675
1676 g_balance_value_tab pay_balance_pkg.t_balance_value_tab;
1677 g_context_table pay_balance_pkg.t_context_tab;
1678 g_result_table pay_balance_pkg.t_detailed_bal_out_tab;
1679 l_action_info_id NUMBER;
1680 l_ovn NUMBER;
1681 l_defined_balance_id NUMBER;
1682 g_result_table1 pay_balance_pkg.t_detailed_bal_out_tab;
1683 g_result_table2 pay_balance_pkg.t_detailed_bal_out_tab;
1684 g_result_table3 pay_balance_pkg.t_detailed_bal_out_tab;
1685 l_value NUMBER;
1686 i NUMBER := 0;
1687 l_message VARCHAR2(255);
1688 l_procedure VARCHAR2(100);
1689
1690 BEGIN
1691
1692 l_procedure := g_package ||'archive_allowances';
1693 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1694
1695
1696 FOR c_rec IN c_comp_name
1697 LOOP
1698 i := i + 1;
1699 g_context_table(i).source_text2 := c_rec.name;
1700 END LOOP;
1701
1702 IF g_debug THEN
1703 pay_in_utils.trace('Assignment Action id ',p_run_asg_action_id);
1704 pay_in_utils.trace('Archive Asg Action id ',p_arc_asg_action_id);
1705 pay_in_utils.trace('GRE id ',p_gre_id);
1706 pay_in_utils.trace('Previous GRE Asg Action id ',pre_gre_asg_act_id);
1707 END IF;
1708
1709
1710 archive_balances(p_run_asg_action_id => p_run_asg_action_id
1711 ,pre_gre_asg_act_id => pre_gre_asg_act_id
1712 ,p_arc_asg_action_id => p_arc_asg_action_id
1713 ,p_gre_id => p_gre_id
1714 ,p_action_inf_category => 'IN_EOY_ALLOW'
1715 ,p_balance_name => 'Allowance Amount'
1716 ,p_balance_name1 => 'Allowances Standard Value'
1717 ,p_balance_name2 => 'Taxable Allowances'
1718 ,p_balance_name3 => 'Taxable Allowances for Projection'
1719 ,p_balance_dimension => '_ASG_COMP_YTD'
1720 ,p_balance_dimension1 => '_ASG_COMP_DE_PTD'
1721 ,p_balance_dimension2 => '_ASG_COMP_YTD'
1722 ,p_balance_dimension3 => '_ASG_COMP_DE_PTD'
1723 ,g_context_table => g_context_table
1724 ,g_result_table => g_result_table
1725 ,g_result_table1 => g_result_table1
1726 ,g_result_table2 => g_result_table2
1727 ,g_result_table3 => g_result_table3
1728 ,g_balance_value_tab => g_balance_value_tab
1729 );
1730 pay_in_utils.set_location(g_debug,l_procedure, 20);
1731
1732 OPEN c_hra;
1733 FETCH c_hra INTO l_action_info_id,l_ovn;
1734 CLOSE c_hra;
1735
1736 IF l_action_info_id IS NOT NULL
1737 THEN
1738 pay_in_utils.set_location(g_debug,l_procedure, 30);
1739 IF (pre_gre_asg_act_id IS NOT NULL)--Not the first record
1740 THEN
1741 IF p_flag -- Neither the first nor the last record. Hence diff of THRA _ASG_YTD at 2 diff act ids.
1742 THEN
1743 l_defined_balance_id := pay_in_tax_utils.get_defined_balance('Taxable House Rent Allowance','_ASG_YTD');
1744 l_value := pay_balance_pkg.get_value(p_defined_balance_id => l_defined_balance_id
1745 ,p_assignment_action_id => p_run_asg_action_id
1746 );
1747 l_value := l_value - pay_balance_pkg.get_value(p_defined_balance_id => l_defined_balance_id
1748 ,p_assignment_action_id => pre_gre_asg_act_id
1749 );
1750 ELSE -- Last Record. Hence diff of Projected and YTD value.
1751 l_defined_balance_id :=
1752 pay_in_tax_utils.get_defined_balance('Taxable House Rent Allowance for Projection','_ASG_LE_DE_PTD');
1753
1754 l_value := pay_balance_pkg.get_value(p_defined_balance_id => l_defined_balance_id
1755 ,p_assignment_action_id => p_run_asg_action_id
1756 );
1757 l_defined_balance_id := pay_in_tax_utils.get_defined_balance('Taxable House Rent Allowance','_ASG_YTD');
1758 l_value := l_value - pay_balance_pkg.get_value(p_defined_balance_id => l_defined_balance_id
1759 ,p_assignment_action_id => pre_gre_asg_act_id
1760 );
1761 END IF;
1762 ELSIF p_flag -- First Record in a multi tan scenario, hence take the THRA_ASG_YTD
1763 THEN
1764 pay_in_utils.set_location(g_debug,l_procedure, 40);
1765 l_defined_balance_id := pay_in_tax_utils.get_defined_balance('Taxable House Rent Allowance','_ASG_YTD');
1766 l_value := pay_balance_pkg.get_value(p_defined_balance_id => l_defined_balance_id
1767 ,p_assignment_action_id => p_run_asg_action_id
1768 );
1769 ELSE -- Only a single record exists, hence take the Projetced value
1770 pay_in_utils.set_location(g_debug,l_procedure, 50);
1771 l_defined_balance_id := pay_in_tax_utils.get_defined_balance('Taxable House Rent Allowance for Projection','_ASG_DE_PTD');
1772 l_value := pay_balance_pkg.get_value(p_defined_balance_id => l_defined_balance_id
1773 ,p_assignment_action_id => p_run_asg_action_id
1774 );
1775 END IF;
1776
1777 IF g_debug THEN
1778 pay_in_utils.trace('Balance value ',l_value);
1779 END IF;
1780
1781 pay_action_information_api.update_action_information
1782 (
1783 p_action_information_id => l_action_info_id
1784 ,p_object_version_number => l_ovn
1785 ,p_action_information5 => l_value
1786 );
1787 pay_in_utils.set_location(g_debug,l_procedure, 60);
1788 END IF;
1789 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 70);
1790
1791
1792 END archive_allowances;
1793
1794 --------------------------------------------------------------------------
1795 -- --
1796 -- Name : ARCHIVE_PERQUISISTES --
1797 -- Type : PROCEDURE --
1798 -- Access : Public --
1799 -- Description : This procedure archives the perquisite details --
1800 -- Parameters : --
1801 -- IN : p_arc_pay_action_id NUMBER --
1802 -- p_gre_id NUMBER --
1803 -- p_effective_end_date DATE --
1804 -- OUT : N/A --
1805 -- --
1806 -- Change History : --
1807 --------------------------------------------------------------------------
1808 -- Rev# Date Userid Description --
1809 --------------------------------------------------------------------------
1810 -- 115.0 23-MAY-2005 aaagarwa Initial Version --
1811 --------------------------------------------------------------------------
1812 PROCEDURE archive_perquisites(p_run_asg_action_id IN NUMBER
1813 ,p_arc_asg_action_id IN NUMBER
1814 ,p_gre_id IN NUMBER
1815 ,pre_gre_asg_act_id IN NUMBER DEFAULT NULL
1816 )
1817 IS
1818 g_balance_value_tab pay_balance_pkg.t_balance_value_tab;
1819 g_context_table pay_balance_pkg.t_context_tab;
1820 g_result_table pay_balance_pkg.t_detailed_bal_out_tab;
1821 l_action_info_id NUMBER;
1822 l_ovn NUMBER;
1823 g_result_table1 pay_balance_pkg.t_detailed_bal_out_tab;
1824 g_result_table2 pay_balance_pkg.t_detailed_bal_out_tab;
1825 g_result_table3 pay_balance_pkg.t_detailed_bal_out_tab;
1826 l_message VARCHAR2(255);
1827 l_procedure VARCHAR2(100);
1828
1829 BEGIN
1830 l_procedure := g_package ||'archive_perquisites';
1831 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1832
1833 IF g_debug THEN
1834 pay_in_utils.trace('Assignment Action id ',p_run_asg_action_id);
1835 pay_in_utils.trace('Archive Asg Action id ',p_arc_asg_action_id);
1836 pay_in_utils.trace('GRE id ',p_gre_id);
1837 pay_in_utils.trace('Previous GRE Asg Action id ',pre_gre_asg_act_id);
1838 END IF;
1839
1840 g_context_table.DELETE;
1841 g_result_table.DELETE;
1842 g_result_table1.DELETE;
1843 g_result_table2.DELETE;
1844 g_result_table3.DELETE;
1845 g_balance_value_tab.DELETE;
1846
1847
1848 g_context_table(1).source_text2 := 'Company Accommodation';
1849 g_context_table(2).source_text2 := 'Company Movable Assets';
1850 g_context_table(3).source_text2 := 'Domestic Servant';
1851 g_context_table(4).source_text2 := 'Free Education';
1852 g_context_table(5).source_text2 := 'Gas / Water / Electricity';
1853 g_context_table(6).source_text2 := 'Leave Travel Concession';
1854 g_context_table(7).source_text2 := 'Loan at Concessional Rate';
1855 g_context_table(8).source_text2 := 'Medical';
1856 g_context_table(9).source_text2 := 'Shares';
1857 g_context_table(10).source_text2 := 'Transfer of Company Assets';
1858 g_context_table(11).source_text2 := 'Employer Paid Tax';
1859 g_context_table(12).source_text2 := 'Gift Voucher';
1860 g_context_table(13).source_text2 := 'Travel / Tour / Accommodation';
1861 g_context_table(14).source_text2 := 'Free Transport';
1862 g_context_table(15).source_text2 := 'Credit Cards';
1863 g_context_table(16).source_text2 := 'Club Expenditure';
1864 g_context_table(17).source_text2 := 'Motor Car Perquisite';
1865 g_context_table(18).source_text2 := 'Lunch Perquisite';
1866
1867 pay_in_utils.set_location(g_debug,l_procedure, 20);
1868
1869 archive_balances(p_run_asg_action_id => p_run_asg_action_id
1870 ,pre_gre_asg_act_id => pre_gre_asg_act_id
1871 ,p_arc_asg_action_id => p_arc_asg_action_id
1872 ,p_gre_id => p_gre_id
1873 ,p_action_inf_category => 'IN_EOY_PERQ'
1874 ,p_balance_name => 'Taxable Perquisites'
1875 ,p_balance_name1 => 'Perquisite Employee Contribution'
1876 ,p_balance_dimension => '_ASG_COMP_YTD'
1877 ,p_balance_dimension1 => '_ASG_COMP_YTD'
1878 ,g_context_table => g_context_table
1879 ,g_result_table => g_result_table
1880 ,g_result_table1 => g_result_table1
1881 ,g_result_table2 => g_result_table2
1882 ,g_result_table3 => g_result_table3
1883 ,g_balance_value_tab => g_balance_value_tab
1884 );
1885 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
1886
1887 END archive_perquisites;
1888 --------------------------------------------------------------------------
1889 -- --
1890 -- Name : ARCHIVE_EOY_SALARY --
1891 -- Type : PROCEDURE --
1892 -- Access : Public --
1893 -- Description : This procedure archives the various salary components-
1894 -- Parameters : --
1895 -- IN : p_arc_pay_action_id NUMBER --
1896 -- p_gre_id NUMBER --
1897 -- p_effective_end_date DATE --
1898 -- OUT : N/A --
1899 -- --
1900 -- Change History : --
1901 --------------------------------------------------------------------------
1902 -- Rev# Date Userid Description --
1903 --------------------------------------------------------------------------
1904 -- 115.0 23-MAY-2005 aaagarwa Initial Version --
1905 --------------------------------------------------------------------------
1906 PROCEDURE archive_eoy_salary(p_run_asg_action_id IN NUMBER
1907 ,p_arc_asg_action_id IN NUMBER
1908 ,p_gre_id IN NUMBER)
1909 IS
1910 CURSOR c_defined_balance_id
1911 IS
1912 SELECT pdb.defined_balance_id balance_id
1913 ,pbt.balance_name balance_name
1914 FROM pay_balance_types pbt
1915 ,pay_balance_dimensions pbd
1916 ,pay_defined_balances pdb
1917 WHERE pbt.balance_name IN('Long Term Capital Gains'
1918 ,'Short Term Capital Gains'
1919 ,'Capital Gains'
1920 ,'Loss From House Property'
1921 ,'Business and Profession Gains'
1922 ,'Other Sources of Income'
1923 )
1924 AND pbd.dimension_name='_ASG_DE_PTD'
1925 AND pbt.legislation_code = 'IN'
1926 AND pbd.legislation_code = 'IN'
1927 AND pbt.balance_type_id = pdb.balance_type_id
1928 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
1929
1930 CURSOR c_f16_sal_balances
1931 IS
1932 SELECT pdb.defined_balance_id balance_id
1933 ,pbt.balance_name balance_name
1934 FROM pay_balance_types pbt
1935 ,pay_balance_dimensions pbd
1936 ,pay_defined_balances pdb
1937 WHERE((pbt.balance_name IN('F16 Education Cess till Date'
1938 ,'F16 Sec and HE Cess till Date'
1939 ,'F16 Surcharge till Date'
1940 ,'F16 Income Tax till Date'
1941 ,'F16 Education Cess'
1942 ,'F16 Sec and HE Cess'
1943 ,'F16 Employment Tax'
1944 ,'F16 Entertainment Allowance'
1945 ,'F16 Marginal Relief'
1946 ,'F16 Profit in lieu of Salary'
1947 ,'F16 Relief under Sec 89'
1948 ,'F16 Salary Under Section 17'
1949 ,'F16 Surcharge'
1950 ,'F16 Tax on Total Income'
1951 ,'F16 Value of Perquisites'
1952 ,'F16 Gross Salary'
1953 ,'F16 Gross Salary less Allowances'
1954 ,'F16 Income Chargeable Under head Salaries'
1955 ,'F16 Gross Total Income'
1956 ,'F16 Total Income'
1957 ,'F16 Total Tax payable'
1958 ,'F16 Balance Tax'
1959 ,'F16 Tax Refundable'
1960 ,'F16 Allowances Exempt'
1961 ,'F16 Other Income'
1962 ,'F16 Deductions under Sec 16'
1963 )
1964 AND pbd.dimension_name = '_ASG_LE_DE_PTD')
1965 OR (pbt.balance_name = 'ER Paid Tax on Non Monetary Perquisite'
1966 AND pbd.dimension_name = '_ASG_LE_YTD'))
1967 AND pbt.legislation_code = 'IN'
1968 AND pbd.legislation_code = 'IN'
1969 AND pbt.balance_type_id = pdb.balance_type_id
1970 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
1971
1972 CURSOR c_defined_bal_id
1973 IS
1974 SELECT pdb.defined_balance_id balance_id
1975 ,pbt.balance_name balance_name
1976 FROM pay_balance_types pbt
1977 ,pay_balance_dimensions pbd
1978 ,pay_defined_balances pdb
1979 WHERE pbt.balance_name IN(
1980 'Excess Interest Amount'
1981 ,'Excess PF Amount'
1982 ,'TDS on Previous Employment'
1983 ,'CESS on Previous Employment'
1984 ,'Sec and HE Cess on Previous Employment'
1985 ,'SC on Previous Employment'
1986 ,'Previous Employment Earnings'
1987 )
1988 AND pbd.dimension_name='_ASG_YTD'
1989 AND pbt.legislation_code = 'IN'
1990 AND pbd.legislation_code = 'IN'
1991 AND pbt.balance_type_id = pdb.balance_type_id
1992 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
1993
1994 g_balance_value_tab pay_balance_pkg.t_balance_value_tab;
1995 g_balance_value_tab1 pay_balance_pkg.t_balance_value_tab;
1996 g_result_table pay_balance_pkg.t_detailed_bal_out_tab;
1997
1998 i NUMBER;
1999 j NUMBER;
2000 g_bal_name_tab t_bal_name_tab;
2001 g_bal_name_tab1 t_bal_name_tab;
2002 g_context_table pay_balance_pkg.t_context_tab;
2003 l_action_info_id NUMBER;
2004 l_ovn NUMBER;
2005 l_in_tax_ded NUMBER;
2006 l_message VARCHAR2(255);
2007 l_procedure VARCHAR2(100);
2008 l_total_cess NUMBER ;
2009 l_total_cess_till_date NUMBER ;
2010 l_cess_action_info_id NUMBER;
2011 l_cess_ov_id NUMBER;
2012 l_cess_td_action_info_id NUMBER;
2013 l_cess_td_ov_id NUMBER;
2014
2015 BEGIN
2016 l_procedure := g_package ||'archive_eoy_salary';
2017 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2018
2019 IF g_debug THEN
2020 pay_in_utils.trace('Assignment Action id ',p_run_asg_action_id);
2021 pay_in_utils.trace('Archive Asg Action id ',p_arc_asg_action_id);
2022 pay_in_utils.trace('GRE id ',p_gre_id);
2023 END IF;
2024
2025 i := 1;
2026 g_bal_name_tab.DELETE;
2027 l_in_tax_ded := 0;
2028 l_total_cess:=0;
2029 l_total_cess_till_date:=0;
2030
2031 FOR c_rec IN c_defined_balance_id
2032 LOOP
2033 g_balance_value_tab(i).defined_balance_id := c_rec.balance_id;
2034 g_bal_name_tab(i).balance_name := c_rec.balance_name;
2035 i := i + 1;
2036 END LOOP;
2037
2038 pay_balance_pkg.get_value(p_run_asg_action_id,g_balance_value_tab);
2039
2040 pay_in_utils.set_location(g_debug,l_procedure, 20);
2041
2042
2043
2044 pay_in_utils.trace('**************************************************','********************');
2045 FOR i IN 1..g_balance_value_tab.COUNT
2046 LOOP
2047 IF (g_balance_value_tab(i).balance_value <> 0)
2048 THEN
2049 pay_action_information_api.create_action_information
2050 (p_action_context_id => p_arc_asg_action_id
2051 ,p_action_context_type => 'AAP'
2052 ,p_action_information_category => 'IN_EOY_ASG_SAL'
2053 ,p_source_id => p_run_asg_action_id
2054 ,p_action_information1 => g_bal_name_tab(i).balance_name
2055 ,p_action_information2 => g_balance_value_tab(i).balance_value
2056 ,p_action_information_id => l_action_info_id
2057 ,p_object_version_number => l_ovn
2058 );
2059
2060 IF g_debug THEN
2061 pay_in_utils.trace('SALARY Balance Name ',g_bal_name_tab(i).balance_name);
2062 pay_in_utils.trace('SALARY Balance Value ',g_balance_value_tab(i).balance_value);
2063 END IF;
2064
2065 END IF;
2066 END LOOP;
2067
2068 pay_in_utils.set_location(g_debug,l_procedure, 20);
2069
2070 --Archiving balances having YTD Dimensions
2071 i := 1;
2072 FOR c_rec IN c_defined_bal_id
2073 LOOP
2074 g_balance_value_tab1(i).defined_balance_id := c_rec.balance_id;
2075 g_bal_name_tab1(i).balance_name := c_rec.balance_name;
2076 i := i + 1;
2077 END LOOP;
2078
2079 pay_balance_pkg.get_value(p_run_asg_action_id,g_balance_value_tab1);
2080
2081 pay_in_utils.set_location(g_debug,l_procedure, 30);
2082
2083 FOR i IN 1..g_balance_value_tab1.COUNT
2084 LOOP
2085
2086
2087
2088 IF (g_balance_value_tab1(i).balance_value <> 0)
2089 THEN
2090 pay_action_information_api.create_action_information
2091 (p_action_context_id => p_arc_asg_action_id
2092 ,p_action_context_type => 'AAP'
2093 ,p_action_information_category => 'IN_EOY_ASG_SAL'
2094 ,p_source_id => p_run_asg_action_id
2095 ,p_action_information1 => g_bal_name_tab1(i).balance_name
2096 ,p_action_information2 => g_balance_value_tab1(i).balance_value
2097 ,p_action_information_id => l_action_info_id
2098 ,p_object_version_number => l_ovn
2099 );
2100 IF g_debug THEN
2101 pay_in_utils.trace('SALARY Balance Name ',g_bal_name_tab1(i).balance_name);
2102 pay_in_utils.trace('SALARY Balance Value ',g_balance_value_tab1(i).balance_value);
2103 END IF;
2104
2105 END IF;
2106 END LOOP;
2107
2108
2109 pay_in_utils.set_location(g_debug,l_procedure, 40);
2110 --Archiving balances having LE_PTD Dimensions
2111 i := 1;
2112 g_bal_name_tab1.DELETE;
2113 g_balance_value_tab1.DELETE;
2114 g_context_table(1).tax_unit_id := p_gre_id;
2115
2116
2117
2118 FOR c_rec IN c_f16_sal_balances
2119 LOOP
2120 g_balance_value_tab1(i).defined_balance_id := c_rec.balance_id;
2121 g_bal_name_tab1(i).balance_name := c_rec.balance_name;
2122 i := i + 1;
2123
2124 END LOOP;
2125
2126 pay_in_utils.set_location(g_debug,l_procedure, 50);
2127
2128 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
2129 ,p_defined_balance_lst => g_balance_value_tab1
2130 ,p_context_lst => g_context_table
2131 ,p_output_table => g_result_table
2132 );
2133
2134
2135 FOR i IN 1..g_bal_name_tab1.COUNT
2136 LOOP
2137 IF (g_bal_name_tab1(i).balance_name = 'F16 Education Cess till Date' OR
2138 g_bal_name_tab1(i).balance_name = 'F16 Sec and HE Cess till Date' OR
2139 g_bal_name_tab1(i).balance_name = 'F16 Surcharge till Date' OR
2140 g_bal_name_tab1(i).balance_name = 'F16 Income Tax till Date' ) THEN
2141 l_in_tax_ded := l_in_tax_ded + g_result_table(i).balance_value;
2142 END IF;
2143 IF (g_bal_name_tab1(i).balance_name = 'F16 Education Cess till Date' OR
2144 g_bal_name_tab1(i).balance_name = 'F16 Sec and HE Cess till Date' ) THEN
2145 l_total_cess_till_date:=l_total_cess_till_date + g_result_table(i).balance_value;
2146 END IF ;
2147 IF (g_bal_name_tab1(i).balance_name = 'F16 Education Cess' OR
2148 g_bal_name_tab1(i).balance_name = 'F16 Sec and HE Cess' ) THEN
2149 l_total_cess := l_total_cess + g_result_table(i).balance_value;
2150 END IF ;
2151
2152 END LOOP;
2153 pay_in_utils.set_location(g_debug,l_procedure, 60);
2154
2155 g_bal_name_tab1(g_result_table.COUNT + 1).balance_name := 'Income Tax Deduction';
2156 g_result_table(g_result_table.COUNT + 1).balance_value := l_in_tax_ded;
2157
2158 FOR i IN 1..g_bal_name_tab1.COUNT
2159 LOOP
2160
2161 IF g_result_table(i).balance_value <> 0
2162 THEN
2163 pay_action_information_api.create_action_information
2164 (p_action_context_id => p_arc_asg_action_id
2165 ,p_action_context_type => 'AAP'
2166 ,p_action_information_category => 'IN_EOY_ASG_SAL'
2167 ,p_source_id => p_run_asg_action_id
2168 ,p_action_information1 => g_bal_name_tab1(i).balance_name
2169 ,p_action_information2 => g_result_table(i).balance_value
2170 ,p_action_information_id => l_action_info_id
2171 ,p_object_version_number => l_ovn
2172 );
2173 IF g_bal_name_tab1(i).balance_name='F16 Education Cess' THEN
2174 pay_in_utils.set_location(g_debug,l_procedure, 61);
2175 l_cess_action_info_id:=l_action_info_id;
2176 l_cess_ov_id:=l_ovn;
2177 END IF ;
2178 IF g_bal_name_tab1(i).balance_name='F16 Education Cess till Date' THEN
2179 pay_in_utils.set_location(g_debug,l_procedure, 62);
2180 l_cess_td_action_info_id:=l_action_info_id;
2181 l_cess_td_ov_id:=l_ovn;
2182 END IF ;
2183
2184 IF g_debug THEN
2185 pay_in_utils.set_location(g_debug,l_procedure, 63);
2186 pay_in_utils.trace('SALARY Balance Name ',g_bal_name_tab1(i).balance_name);
2187 pay_in_utils.trace('SALARY Balance Value ',g_result_table(i).balance_value);
2188 END IF;
2189
2190 END IF;
2191 END LOOP;
2192 IF l_total_cess <> 0 THEN
2193 pay_in_utils.set_location(g_debug,l_procedure, 64);
2194
2195 IF l_cess_action_info_id IS NULL THEN
2196 pay_in_utils.set_location(g_debug,l_procedure, 65);
2197 pay_action_information_api.create_action_information
2198 (p_action_context_id => p_arc_asg_action_id
2199 ,p_action_context_type => 'AAP'
2200 ,p_action_information_category => 'IN_EOY_ASG_SAL'
2201 ,p_source_id => p_run_asg_action_id
2202 ,p_action_information1 => 'F16 Education Cess'
2203 ,p_action_information2 => l_total_cess
2204 ,p_action_information_id => l_action_info_id
2205 ,p_object_version_number => l_ovn
2206 );
2207 ELSE
2208 pay_in_utils.set_location(g_debug,l_procedure, 66);
2209 pay_action_information_api.update_action_information
2210 (p_action_information_id => l_cess_action_info_id
2211 ,p_object_version_number => l_cess_ov_id
2212 ,p_action_information1 => 'F16 Education Cess'
2213 ,p_action_information2 => l_total_cess
2214 );
2215 END IF;
2216 END IF ;
2217
2218 IF l_total_cess_till_date <> 0 THEN
2219 pay_in_utils.set_location(g_debug,l_procedure, 67);
2220 IF l_cess_td_action_info_id IS NULL THEN
2221 pay_in_utils.set_location(g_debug,l_procedure, 68);
2222 pay_action_information_api.create_action_information
2223 (p_action_context_id => p_arc_asg_action_id
2224 ,p_action_context_type => 'AAP'
2225 ,p_action_information_category => 'IN_EOY_ASG_SAL'
2226 ,p_source_id => p_run_asg_action_id
2227 ,p_action_information1 => 'F16 Education Cess till Date'
2228 ,p_action_information2 => l_total_cess_till_date
2229 ,p_action_information_id => l_action_info_id
2230 ,p_object_version_number => l_ovn
2231 );
2232 ELSE
2233 pay_in_utils.set_location(g_debug,l_procedure, 69);
2234 pay_action_information_api.update_action_information
2235 (p_action_information_id => l_cess_td_action_info_id
2236 ,p_object_version_number => l_cess_td_ov_id
2237 ,p_action_information1 => 'F16 Education Cess till Date'
2238 ,p_action_information2 => l_total_cess_till_date
2239 );
2240 END IF;
2241 END IF ;
2242 l_cess_action_info_id:=0;
2243 l_cess_ov_id:=0;
2244 l_cess_td_action_info_id:=0;
2245 l_cess_td_ov_id:=0;
2246 pay_in_utils.trace('**************************************************','********************');
2247 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 70);
2248
2249
2250 END archive_eoy_salary;
2251 --------------------------------------------------------------------------
2252 -- --
2253 -- Name : ARCHIVE_OTHER_BALANCES --
2254 -- Type : PROCEDURE --
2255 -- Access : Public --
2256 -- Description : This is called to archive the fields that were not --
2257 -- covered under IN_EOY_ALLOW and IN_EOY_PERQ --
2258 -- Parameters : --
2259 -- IN : p_arc_pay_action_id NUMBER --
2260 -- p_gre_id NUMBER --
2261 -- p_effective_end_date DATE --
2262 -- OUT : N/A --
2263 -- --
2264 -- Change History : --
2265 --------------------------------------------------------------------------
2266 -- Rev# Date Userid Description --
2267 --------------------------------------------------------------------------
2268 -- 115.0 23-MAY-2005 aaagarwa Initial Version --
2269 --------------------------------------------------------------------------
2270 PROCEDURE archive_other_balances(p_run_asg_action_id IN NUMBER
2271 ,p_arc_asg_action_id IN NUMBER
2272 ,pre_gre_asg_act_id IN NUMBER DEFAULT NULL
2273 ,p_gre_id IN NUMBER
2274 ,p_start_date IN DATE
2275 ,p_end_date IN DATE
2276 )
2277 IS
2278
2279 CURSOR c_defined_bal_id
2280 IS
2281 SELECT pdb.defined_balance_id balance_id
2282 ,pbt.balance_name balance_name
2283 FROM pay_balance_types pbt
2284 ,pay_balance_dimensions pbd
2285 ,pay_defined_balances pdb
2286 WHERE pbt.balance_name IN('Taxable Allowances'
2287 ,'Taxable Perquisites'
2288 ,'Monthly Furniture Cost'
2289 ,'Furniture Perquisite'
2290 ,'Cost and Rent of Furniture'
2291 ,'Perquisite Employee Contribution'
2292 ,'ER Paid Tax on Monetary Perquisite'
2293 )
2294 AND pbd.dimension_name='_ASG_YTD'
2295 AND pbt.legislation_code = 'IN'
2296 AND pbd.legislation_code = 'IN'
2297 AND pbt.balance_type_id = pdb.balance_type_id
2298 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
2299
2300
2301
2302
2303 g_balance_value_tab pay_balance_pkg.t_balance_value_tab;
2304 l_balance_value_tab1 pay_balance_pkg.t_balance_value_tab;
2305 l_balance_value_tab2 pay_balance_pkg.t_balance_value_tab;
2306 g_context_table pay_balance_pkg.t_context_tab;
2307 g_result_table pay_balance_pkg.t_detailed_bal_out_tab;
2308 g_bal_name_tab t_bal_name_tab;
2309 i NUMBER;
2310 l_context VARCHAR2(50);
2311 l_defined_balance_id NUMBER;
2312 l_value NUMBER;
2313 l_action_info_id NUMBER;
2314 l_ovn NUMBER;
2315 l_tax_on_direct_pymt NUMBER :=0;
2316 l_message VARCHAR2(255);
2317 l_procedure VARCHAR2(100);
2318
2319 BEGIN
2320
2321 l_procedure := g_package ||'archive_other_balances';
2322 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2323
2324 IF g_debug THEN
2325 pay_in_utils.trace('**************************************************','********************');
2326 pay_in_utils.trace('Assignment Action id ',p_run_asg_action_id);
2327 pay_in_utils.trace('Archive Asg Action id ',p_arc_asg_action_id);
2328 pay_in_utils.trace('GRE id ',p_gre_id);
2329 pay_in_utils.trace('Previous GRE Asg Action id ',pre_gre_asg_act_id);
2330 pay_in_utils.trace('Start Date ',p_start_date);
2331 pay_in_utils.trace('End Date ',p_end_date);
2332 pay_in_utils.trace('**************************************************','********************');
2333 END IF;
2334
2335 --Archiving the various Perquisite and Allowance records
2336
2337 i := 1;
2338 g_context_table.DELETE;
2339 g_bal_name_tab.DELETE;
2340 g_balance_value_tab.DELETE;
2341
2342 FOR c_rec IN c_defined_bal_id
2343 LOOP
2344 g_balance_value_tab(i).defined_balance_id := c_rec.balance_id;
2345 g_bal_name_tab(i).balance_name := c_rec.balance_name;
2346 i := i + 1;
2347 END LOOP;
2348
2349 pay_in_utils.set_location(g_debug,l_procedure, 20);
2350
2351 l_balance_value_tab1 := g_balance_value_tab;
2352 l_balance_value_tab2 := g_balance_value_tab;
2353
2354 pay_balance_pkg.get_value(p_run_asg_action_id,l_balance_value_tab1);
2355
2356 IF pre_gre_asg_act_id IS NOT NULL
2357 THEN
2358 pay_balance_pkg.get_value(pre_gre_asg_act_id,l_balance_value_tab2);
2359 END IF;
2360
2361 pay_in_utils.set_location(g_debug,l_procedure, 30);
2362 FOR i IN 1..g_balance_value_tab.COUNT
2363 LOOP
2364
2365 IF (g_bal_name_tab(i).balance_name <> 'Monthly Furniture Cost')
2366 THEN
2367 g_balance_value_tab(i).balance_value := NVL(l_balance_value_tab1(i).balance_value,0)
2368 - NVL(l_balance_value_tab2(i).balance_value,0);
2369 ELSE
2370 g_balance_value_tab(i).balance_value := NVL(l_balance_value_tab1(i).balance_value,0);
2371 END IF;
2372
2373 IF (g_balance_value_tab(i).balance_value <> 0)
2374 THEN
2375 IF (g_bal_name_tab(i).balance_name = 'Taxable Allowances')
2376 THEN
2377 l_context := 'IN_EOY_ALLOW';
2378 ELSE
2379 l_context := 'IN_EOY_PERQ';
2380 END IF;
2381 pay_in_utils.set_location(g_debug,l_procedure, 40);
2382
2383 pay_action_information_api.create_action_information
2384 (p_action_context_id => p_arc_asg_action_id
2385 ,p_action_context_type => 'AAP'
2386 ,p_action_information_category => l_context
2387 ,p_source_id => p_run_asg_action_id
2388 ,p_action_information1 => g_bal_name_tab(i).balance_name
2389 ,p_action_information2 => g_balance_value_tab(i).balance_value
2390 ,p_action_information_id => l_action_info_id
2391 ,p_object_version_number => l_ovn
2392 );
2393
2394 IF g_debug THEN
2395 pay_in_utils.trace('**************************************************','********************');
2396 pay_in_utils.trace('OTHER Balance Name ', g_bal_name_tab(i).balance_name);
2397 pay_in_utils.trace('OTHER Balance Value ',g_balance_value_tab(i).balance_value);
2398 pay_in_utils.trace('**************************************************','********************');
2399 END IF;
2400
2401 END IF;
2402 END LOOP;
2403 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
2404
2405
2406 END archive_other_balances;
2407
2408 --------------------------------------------------------------------------
2409 -- --
2410 -- Name : ARCHIVE_ORG_DATA --
2411 -- Type : PROCEDURE --
2412 -- Access : Public --
2413 -- Description : Procedure to archive the Organizational details at --
2414 -- Payroll level --
2415 -- Parameters : --
2416 -- IN : p_arc_pay_action_id NUMBER --
2417 -- p_gre_id NUMBER --
2418 -- p_effective_end_date DATE --
2419 -- OUT : N/A --
2420 -- --
2421 -- Change History : --
2422 --------------------------------------------------------------------------
2423 -- Rev# Date Userid Description --
2424 --------------------------------------------------------------------------
2425 -- 115.0 23-MAY-2005 aaagarwa Initial Version --
2426 -- 115.1 25-SEP-2007 rsaharay Modified c_pos,c_rep_address --
2427 --------------------------------------------------------------------------
2428 PROCEDURE archive_org_data(p_arc_pay_action_id IN NUMBER
2429 ,p_gre_id IN NUMBER
2430 ,p_effective_end_date IN DATE
2431 )
2432 IS
2433
2434 CURSOR c_org_inc_tax_df_details
2435 IS
2436 SELECT hoi.org_information1 tan
2437 ,hoi.org_information2 ward
2438 ,hoi.org_information3 emplr_type
2439 ,hoi.org_information4 reg_org_id
2440 ,hoi.org_information5 tan_ack_no
2441 ,hoi.org_information16 income_tax_org_id
2442 ,hou.name org_name
2443 ,hou.location_id location_id
2444 ,hoi.org_information17 dig_sign
2445 ,hoi.org_information18 image_f16
2446 FROM hr_organization_information hoi
2447 ,hr_organization_units hou
2448 WHERE hoi.organization_id = p_gre_id
2449 AND hoi.org_information_context = 'PER_IN_INCOME_TAX_DF'
2450 AND hou.organization_id = hoi.organization_id
2451 AND hou.business_group_id = g_bg_id
2452 AND p_effective_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
2453
2454 CURSOR c_reg_org_details(p_reg_org_id NUMBER)
2455 IS
2456 SELECT hoi.org_information3 pan
2457 ,hoi.org_information4 legal_name
2458 FROM hr_organization_information hoi
2459 ,hr_organization_units hou
2460 WHERE hoi.organization_id = p_reg_org_id
2461 AND hoi.org_information_context = 'PER_IN_COMPANY_DF'
2462 AND hou.organization_id = hoi.organization_id
2463 AND hou.business_group_id = g_bg_id
2464 AND p_effective_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
2465
2466 CURSOR c_income_tax_org_details(p_income_tax_org_id NUMBER)
2467 IS
2468 SELECT location_id
2469 FROM hr_organization_units
2470 WHERE organization_id = p_income_tax_org_id
2471 AND business_group_id = g_bg_id
2472 AND p_effective_end_date BETWEEN date_from AND NVL(date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
2473
2474 CURSOR c_pos(p_person_id NUMBER)
2475 IS
2476 SELECT nvl(pos.name,job.name) name ,job.name job
2477 FROM per_all_positions pos
2478 ,per_assignments_f asg
2479 ,per_jobs job
2480 WHERE asg.position_id=pos.position_id(+)
2481 AND asg.job_id=job.job_id(+)
2482 AND asg.person_id = p_person_id
2483 AND asg.primary_flag = 'Y'
2484 AND asg.business_group_id = g_bg_id
2485 AND p_effective_end_date BETWEEN pos.date_effective(+) AND NVL(pos.date_end(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
2486 AND p_effective_end_date BETWEEN job.date_from(+) AND NVL(job.date_to(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
2487 AND p_effective_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
2488
2489
2490 CURSOR c_father_name(p_person_id NUMBER)
2491 IS
2492 SELECT DECODE(pea.title,NULL,hr_in_utility.per_in_full_name(pea.first_name,pea.middle_names,pea.last_name,pea.title)
2493 ,SUBSTR(hr_in_utility.per_in_full_name(pea.first_name,pea.middle_names,pea.last_name,pea.title)
2494 ,INSTR(hr_in_utility.per_in_full_name(pea.first_name,pea.middle_names,pea.last_name,pea.title),' ',1)+1)) father
2495 ,pea.title title
2496 FROM per_all_people_f pep
2497 ,per_all_people_f pea
2498 ,per_contact_relationships con
2499 WHERE pep.person_id = p_person_id
2500 AND pea.person_id =con.contact_person_id
2501 AND pep.business_group_id = g_bg_id
2502 AND pea.business_group_id = g_bg_id
2503 AND con.person_id=pep.person_id
2504 AND con.contact_type='JP_FT'
2505 AND p_effective_end_date BETWEEN pep.effective_start_date AND pep.effective_end_date
2506 AND p_effective_end_date BETWEEN pea.effective_start_date AND pea.effective_end_date;
2507
2508 CURSOR c_representative_id
2509 IS
2510 SELECT hoi.org_information1 person_id
2511 ,DECODE(pep.title,NULL,hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title)
2512 ,SUBSTR(hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title)
2513 ,INSTR(hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title),' ',1)+1)) rep_name
2514 ,pep.title title
2515 FROM hr_organization_information hoi
2516 ,hr_organization_units hou
2517 ,per_all_people_f pep
2518 WHERE hoi.org_information_context = 'PER_IN_INCOME_TAX_REP_DF'
2519 AND hoi.organization_id = p_gre_id
2520 AND hou.organization_id = hoi.organization_id
2521 AND hou.business_group_id = g_bg_id
2522 AND pep.person_id = hoi.org_information1
2523 AND pep.business_group_id = hou.business_group_id
2524 AND p_effective_end_date BETWEEN pep.effective_start_date AND pep.effective_end_date
2525 AND p_effective_end_date BETWEEN fnd_date.canonical_to_date(hoi.org_information2)
2526 AND NVL(fnd_date.canonical_to_date(hoi.org_information3),TO_DATE('31-12-4712','DD-MM-YYYY'))
2527 AND p_effective_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
2528
2529 CURSOR c_rep_address(p_person_id NUMBER)
2530 IS
2531 SELECT hou.location_id rep_location
2532 FROM per_all_assignments_f asg
2533 ,hr_organization_units hou
2534 WHERE asg.person_id = p_person_id
2535 AND asg.primary_flag = 'Y'
2536 AND asg.business_group_id = g_bg_id
2537 AND hou.organization_id = asg.organization_id
2538 AND hou.business_group_id = asg.business_group_id
2539 AND p_effective_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date
2540 AND p_effective_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
2541
2542 CURSOR c_rep_phone(p_person_id NUMBER)
2543 IS
2544 SELECT phone_number rep_phone_no
2545 ,phone_type
2546 FROM per_phones
2547 WHERE parent_id = p_person_id
2548 AND phone_type = DECODE(phone_type,'H1','H1','M')
2549 AND p_effective_end_date BETWEEN date_from AND NVL(date_to,TO_DATE('31-12-4712','DD-MM-YYYY'))
2550 ORDER BY phone_type ASC;
2551
2552 CURSOR c_rep_work_fax(p_person_id NUMBER)
2553 IS
2554 SELECT phone_number work_fax
2555 FROM per_phones
2556 WHERE parent_id = p_person_id
2557 AND phone_type = 'WF'
2558 AND p_effective_end_date BETWEEN date_from AND NVL(date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
2559
2560 l_tan hr_organization_information.org_information1%TYPE;
2561 l_ward hr_organization_information.org_information2%TYPE;
2562 l_reg_org_id hr_organization_information.org_information4%TYPE;
2563 l_tan_ack_no hr_organization_information.org_information5%TYPE;
2564 l_org_name hr_organization_units.name%TYPE;
2565 l_location_id hr_organization_units.location_id%TYPE;
2566 l_pan hr_organization_information.org_information3%TYPE;
2567 l_legal_name hr_organization_information.org_information4%TYPE;
2568 l_rep_person_id per_all_people_f.person_id%TYPE;
2569 l_rep_name per_all_people_f.full_name%TYPE;
2570 l_position per_all_positions.name%TYPE;
2571 l_job per_jobs.name%TYPE;
2572 l_rep_father per_all_people_f.full_name%TYPE;
2573 l_rep_location hr_organization_units.location_id%TYPE;
2574 l_rep_phone_no per_phones.phone_number%TYPE;
2575 l_phone_type per_phones.phone_type%TYPE;
2576 l_rep_father_title per_all_people_f.title%TYPE;
2577 l_rep_title per_all_people_f.title%TYPE;
2578 l_rep_work_fax per_phones.phone_number%TYPE;
2579 l_action_info_id NUMBER;
2580 l_ovn NUMBER;
2581 l_message VARCHAR2(255);
2582 l_procedure VARCHAR2(100);
2583
2584 l_emplr_type hr_organization_information.org_information3%TYPE;
2585 l_income_tax_org_id hr_organization_information.org_information16%TYPE;
2586 l_itax_location_id hr_organization_units.location_id%TYPE;
2587 l_dig_sign hr_organization_information.org_information17%TYPE;
2588 l_image_form16 hr_organization_information.org_information18%TYPE;
2589
2590
2591 BEGIN
2592 l_procedure := g_package ||'archive_org_data';
2593 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2594
2595 IF g_debug THEN
2596 pay_in_utils.trace('Payroll Action id ',p_arc_pay_action_id);
2597 pay_in_utils.trace('GRE id ',p_gre_id);
2598 pay_in_utils.trace('End Date ',p_effective_end_date);
2599
2600
2601 END IF;
2602
2603
2604 OPEN c_org_inc_tax_df_details;
2605 FETCH c_org_inc_tax_df_details
2606 INTO l_tan,l_ward,l_emplr_type,l_reg_org_id,l_tan_ack_no,
2607 l_income_tax_org_id,l_org_name,l_location_id,l_dig_sign,l_image_form16;
2608 CLOSE c_org_inc_tax_df_details;
2609
2610 pay_in_utils.set_location(g_debug,l_procedure, 20);
2611 IF g_debug THEN
2612 pay_in_utils.trace('l_dig_sign ',l_dig_sign);
2613 pay_in_utils.trace('Form 16 ',l_image_form16);
2614 END IF;
2615
2616 OPEN c_income_tax_org_details(l_income_tax_org_id);
2617 FETCH c_income_tax_org_details INTO l_itax_location_id;
2618 CLOSE c_income_tax_org_details;
2619
2620 OPEN c_reg_org_details(l_reg_org_id);
2621 FETCH c_reg_org_details INTO l_pan,l_legal_name;
2622 CLOSE c_reg_org_details;
2623
2624 pay_in_utils.set_location(g_debug,l_procedure, 30);
2625 OPEN c_representative_id;
2626 FETCH c_representative_id INTO l_rep_person_id,l_rep_name,l_rep_title;
2627 CLOSE c_representative_id;
2628
2629 pay_in_utils.set_location(g_debug,l_procedure, 40);
2630 OPEN c_pos(l_rep_person_id);
2631 FETCH c_pos INTO l_position, l_job;
2632 CLOSE c_pos;
2633
2634 pay_in_utils.set_location(g_debug,l_procedure, 50);
2635 OPEN c_father_name(l_rep_person_id);
2636 FETCH c_father_name INTO l_rep_father,l_rep_father_title;
2637 CLOSE c_father_name;
2638
2639 pay_in_utils.set_location(g_debug,l_procedure, 60);
2640 OPEN c_rep_address(l_rep_person_id);
2641 FETCH c_rep_address INTO l_rep_location;
2642 CLOSE c_rep_address;
2643
2644 pay_in_utils.set_location(g_debug,l_procedure, 70);
2645 OPEN c_rep_phone(l_rep_person_id);
2646 FETCH c_rep_phone INTO l_rep_phone_no,l_phone_type;
2647 CLOSE c_rep_phone;
2648
2649 pay_in_utils.set_location(g_debug,l_procedure, 80);
2650 OPEN c_rep_work_fax(l_rep_person_id);
2651 FETCH c_rep_work_fax INTO l_rep_work_fax;
2652 CLOSE c_rep_work_fax;
2653
2654 pay_in_utils.set_location(g_debug,l_procedure, 90);
2655 pay_action_information_api.create_action_information
2656 (p_action_context_id => p_arc_pay_action_id
2657 ,p_action_context_type => 'PA'
2658 ,p_action_information_category => 'IN_EOY_ORG'
2659 ,p_action_information1 => p_gre_id
2660 ,p_action_information2 => l_pan
2661 ,p_action_information3 => g_year
2662 ,p_action_information4 => l_tan
2663 ,p_action_information5 => l_tan_ack_no
2664 ,p_action_information6 => l_org_name
2665 ,p_action_information7 => l_location_id
2666 ,p_action_information8 => l_legal_name
2667 ,p_action_information9 => l_ward
2668 ,p_action_information10 => l_rep_person_id
2669 ,p_action_information11 => l_rep_name
2670 ,p_action_information12 => l_rep_title
2671 ,p_action_information13 => l_position
2672 ,p_action_information14 => l_rep_father
2673 ,p_action_information15 => l_rep_father_title
2674 ,p_action_information16 => l_rep_location
2675 ,p_action_information17 => l_rep_phone_no
2676 ,p_action_information18 => l_rep_work_fax
2677 ,p_action_information19 => l_itax_location_id
2678 ,p_action_information20 => l_emplr_type
2679 ,p_action_information21 => l_job
2680 ,p_action_information22 => l_dig_sign
2681 ,p_action_information23 => l_image_form16
2682 ,p_action_information_id => l_action_info_id
2683 ,p_object_version_number => l_ovn
2684 );
2685
2686 IF g_debug THEN
2687 pay_in_utils.trace('l_rep_name ',l_rep_name);
2688 pay_in_utils.trace('l_position ',l_position);
2689 pay_in_utils.trace('l_job ',l_job);
2690 END IF;
2691 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 100);
2692
2693
2694 END archive_org_data;
2695 --------------------------------------------------------------------------
2696 -- --
2697 -- Name : ARCHIVE_CODE --
2698 -- Type : PROCEDURE --
2699 -- Access : Public --
2700 -- Description : Procedure to call the internal procedures to --
2701 -- actually archive the data. --
2702 -- Parameters : --
2703 -- IN : p_assignment_action_id NUMBER --
2704 -- p_effective_date DATE --
2705 -- --
2706 -- OUT : N/A --
2707 -- --
2708 -- Change History : --
2709 --------------------------------------------------------------------------
2710 -- Rev# Date Userid Description --
2711 --------------------------------------------------------------------------
2712 -- 115.0 23-MAY-2005 aaagarwa Initial Version --
2713 -- 115.1 05-APR-2006 rpalli Bug#5135223:Modified a parameter --
2714 -- l_run_date_earned passed through --
2715 -- archive_person_data and --
2716 -- archive_via_details --
2717 --
2718 PROCEDURE archive_code (
2719 p_assignment_action_id IN NUMBER
2720 ,p_effective_date IN DATE
2721 )
2722 IS
2723 --This cursor determines the GRE/Legal Entity record
2724
2725 CURSOR get_assignment_pact_id
2726 IS
2727 SELECT paa.assignment_id
2728 ,paa.payroll_action_id
2729 FROM pay_assignment_actions paa
2730 ,per_all_assignments_f paf
2731 WHERE paa.assignment_action_id = p_assignment_action_id
2732 AND paa.assignment_id = paf.assignment_id
2733 AND ROWNUM =1;
2734
2735 CURSOR c_gre_records
2736 IS
2737 SELECT GREATEST(asg.effective_start_date,g_start_date) start_date
2738 ,LEAST(asg.effective_end_date,g_end_date) end_date
2739 ,scl.segment1
2740 FROM per_all_assignments_f asg
2741 ,hr_soft_coding_keyflex scl
2742 ,pay_assignment_actions paa
2743 WHERE asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
2744 AND paa.assignment_action_id = p_assignment_action_id
2745 AND asg.assignment_id = paa.assignment_id
2746 AND scl.segment1 LIKE TO_CHAR(g_gre_id)
2747 AND ( asg.effective_start_date BETWEEN g_start_date AND g_end_date
2748 OR g_start_date BETWEEN asg.effective_start_date AND g_end_date
2749 )
2750 AND GREATEST(asg.effective_start_date,g_start_date) <= LEAST(asg.effective_end_date,g_end_date)
2751 ORDER BY 1 asc;
2752
2753 CURSOR get_eoy_archival_details(p_start_date DATE
2754 ,p_end_date DATE
2755 ,p_tax_unit_id NUMBER
2756 ,p_assignment_id NUMBER
2757 )
2758 IS
2759 SELECT TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) run_asg_action_id
2760 FROM pay_assignment_actions paa
2761 ,pay_payroll_actions ppa
2762 ,per_assignments_f paf
2763 WHERE paf.assignment_id = paa.assignment_id
2764 AND paf.assignment_id = p_assignment_id
2765 AND paa.tax_unit_id = p_tax_unit_id
2766 AND paa.payroll_action_id = ppa.payroll_action_id
2767 AND ppa.action_type IN('R','Q','I','B')
2768 AND ppa.action_status ='C'
2769 AND ppa.effective_date between p_start_date and p_end_date
2770 AND paa.source_action_id IS NULL
2771 AND ppa.payroll_id = paf.payroll_id
2772 AND (1 = DECODE(ppa.action_type,'I',1,0)
2773 OR EXISTS (SELECT ''
2774 FROM pay_action_interlocks intk,
2775 pay_assignment_actions paa1,
2776 pay_payroll_actions ppa1
2777 WHERE intk.locked_action_id = paa.assignment_Action_id
2778 AND intk.locking_action_id = paa1.assignment_action_id
2779 AND paa1.payroll_action_id =ppa1.payroll_action_id
2780 AND paa1.assignment_id = p_assignment_id
2781 AND ppa1.action_type in('P','U')
2782 AND ppa.action_type in('R','Q','B')
2783 AND ppa1.action_status ='C'
2784 AND ppa1.effective_date BETWEEN p_start_date and p_end_date
2785 AND ROWNUM =1 ));
2786
2787 CURSOR c_get_date_earned(l_run_assact NUMBER)
2788 IS
2789 SELECT ppa.date_earned run_date
2790 FROM pay_payroll_actions ppa,
2791 pay_assignment_actions paa
2792 WHERE paa.payroll_action_id = ppa.payroll_action_id
2793 AND paa.assignment_action_id = l_run_assact;
2794
2795 CURSOR get_prepayment_date(l_run_assact NUMBER)
2796 IS
2797 SELECT ppa.effective_date
2798 FROM pay_payroll_actions ppa,
2799 pay_assignment_actions paa,
2800 pay_action_interlocks intk
2801 WHERE intk.locked_action_id = l_run_assact
2802 AND intk.locking_action_id =paa.assignment_action_id
2803 AND ppa.payroll_action_id = paa.payroll_action_id
2804 AND ppa.action_type IN('P','U');
2805
2806 CURSOR c_pay_action_level_check(p_payroll_action_id NUMBER
2807 ,p_gre_id NUMBER)
2808 IS
2809 SELECT 1
2810 FROM pay_action_information
2811 WHERE action_information_category = 'IN_EOY_ORG'
2812 AND action_context_type = 'PA'
2813 AND action_context_id = p_payroll_action_id
2814 AND action_information1 = p_gre_id;
2815
2816 --This cursor determines termination date of an assignment.
2817 CURSOR c_termination_check(p_assignment_id NUMBER)
2818 IS
2819 SELECT NVL(pos.actual_termination_date,(fnd_date.string_to_date('31-12-4712','DD-MM-YYYY')))
2820 FROM per_all_assignments_f asg
2821 ,per_periods_of_service pos
2822 WHERE asg.person_id = pos.person_id
2823 AND asg.assignment_id = p_assignment_id
2824 AND asg.business_group_id = pos.business_group_id
2825 AND asg.business_group_id = g_bg_id
2826 AND NVL(pos.actual_termination_date,(to_date('31-12-4712','DD-MM-YYYY')))
2827 BETWEEN asg.effective_start_date AND asg.effective_end_date
2828 ORDER BY 1 desc;
2829
2830 l_procedure VARCHAR2(100);
2831
2832 l_assignment_id NUMBER;
2833 l_run_asg_action_id NUMBER;
2834 l_run_date_earned DATE;
2835 l_pre_effective_date DATE;
2836 l_arc_pay_action_id NUMBER;
2837 l_check NUMBER;
2838 l_end_date DATE;
2839 l_previous_gre_asg_action_id NUMBER;
2840 l_end NUMBER;
2841 l_start NUMBER;
2842 l_flag BOOLEAN;
2843 l_record_count NUMBER;
2844 l_message VARCHAR2(255);
2845
2846 BEGIN
2847 --
2848
2849 g_debug := hr_utility.debug_enabled;
2850 l_procedure := g_package || 'archive_code';
2851 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2852 --
2853 g_count := 1;
2854 g_asg_tab.DELETE;
2855
2856
2857
2858 OPEN get_assignment_pact_id;
2859 FETCH get_assignment_pact_id INTO l_assignment_id ,l_arc_pay_action_id;
2860 CLOSE get_assignment_pact_id;
2861 pay_in_utils.set_location(g_debug,l_procedure, 20);
2862 --
2863 FOR c_rec IN c_gre_records
2864 LOOP
2865
2866 g_asg_tab(g_count).gre_id := c_rec.segment1;
2867 g_asg_tab(g_count).start_date := c_rec.start_date;
2868 g_asg_tab(g_count).end_date := c_rec.end_date;
2869
2870 IF(
2871 (g_count <>1)
2872 AND
2873 (g_asg_tab(g_count-1).gre_id = g_asg_tab(g_count).gre_id)
2874 AND
2875 (g_asg_tab(g_count-1).end_date + 1 = c_rec.start_date) -- Added for 4964645
2876 )
2877 THEN
2878 g_asg_tab(g_count-1).end_date := g_asg_tab(g_count).end_date;
2879 g_asg_tab(g_count).gre_id := NULL;
2880 g_asg_tab(g_count).start_date := NULL;
2881 g_asg_tab(g_count).end_date := NULL;
2882
2883 g_count := g_count -1;
2884 END IF;
2885
2886 IF g_debug THEN
2887 pay_in_utils.trace('GRE Count No ',g_count);
2888 pay_in_utils.trace('GRE id ',g_asg_tab(g_count).gre_id);
2889 pay_in_utils.trace('Start Date ',g_asg_tab(g_count).start_date);
2890 pay_in_utils.trace('End Date ',g_asg_tab(g_count).end_date );
2891 END IF;
2892
2893 g_count := g_count + 1;
2894 END LOOP;
2895 l_record_count := g_count-1;
2896
2897 pay_in_utils.set_location(g_debug,l_procedure, 30);
2898
2899 IF (g_employee_type = 'ALL')
2900 THEN
2901 l_end := g_count-1;
2902 l_start := 1;
2903 ELSIF (g_employee_type = 'CURRENT')
2904 THEN
2905 IF (g_asg_tab(g_count-1).end_date = g_end_date)
2906 THEN
2907 l_end := g_count-1;
2908 l_start := g_count-1;
2909 ELSE
2910 l_end := 0;
2911 l_start := 1;
2912 END IF;
2913 ELSE
2914 IF (g_asg_tab(g_count-1).end_date = g_end_date)
2915 THEN
2916 IF (g_count - 1)>1
2917 THEN
2918 l_end := g_count-2;
2919 l_start := 1;
2920 ELSE
2921 l_end := 1;
2922 l_start := 1;
2923 END IF;
2924 ELSE
2925 l_end := g_count-1;
2926 l_start := 1;
2927 END IF;
2928 END IF;
2929 pay_in_utils.set_location(g_debug,l_procedure, 50);
2930
2931 IF g_debug THEN
2932 pay_in_utils.trace('Start record ',l_start);
2933 pay_in_utils.trace('End Record ',l_end);
2934 END IF;
2935
2936 FOR i IN l_start..l_end
2937 LOOP
2938
2939 OPEN get_eoy_archival_details(g_asg_tab(i).start_date
2940 ,g_asg_tab(i).end_date
2941 ,g_asg_tab(i).gre_id
2942 ,l_assignment_id
2943 );
2944 FETCH get_eoy_archival_details INTO l_run_asg_action_id;
2945 CLOSE get_eoy_archival_details;
2946
2947 pay_in_utils.set_location(g_debug,l_procedure, 60);
2948
2949 IF l_run_asg_action_id IS NOT NULL THEN
2950 pay_in_utils.set_location(g_debug,l_procedure, 70);
2951 OPEN c_get_date_earned(l_run_asg_action_id);
2952 FETCH c_get_date_earned INTO l_run_date_earned;
2953 CLOSE c_get_date_earned;
2954
2955 OPEN get_prepayment_date(l_run_asg_action_id);
2956 FETCH get_prepayment_date INTO l_pre_effective_date;
2957 CLOSE get_prepayment_date;
2958
2959
2960
2961 pay_in_utils.set_location(g_debug,l_procedure, 80);
2962
2963 l_previous_gre_asg_action_id := NULL;
2964 IF (i > 1 AND i <> l_record_count)-- Neither the first nor the last record. Hence determine the diff
2965 THEN -- Taxable House Rent Allowance_ASG_YTD as on previous and current GRE.
2966 FOR c_rec IN get_eoy_archival_details(g_asg_tab(i-1).start_date,g_asg_tab(i-1).end_date,g_asg_tab(i-1).gre_id,l_assignment_id)
2967 LOOP
2968 l_previous_gre_asg_action_id := c_rec.run_asg_action_id;
2969 EXIT;
2970 END LOOP;
2971
2972 l_flag := TRUE;
2973 ELSIF (i = 1 AND l_record_count > 1)-- This is the first record in a multi tan scenario, hence
2974 THEN -- take the Taxable House Rent Allowance_ASG_YTD only.
2975 l_flag := TRUE;
2976 ELSIF (i = l_record_count AND l_record_count > 1)-- This is the latest record in multi TAN case.
2977 THEN -- Hence take the diff of projected and ytd value.
2978 FOR c_rec IN get_eoy_archival_details(g_asg_tab(i-1).start_date,g_asg_tab(i-1).end_date,g_asg_tab(i-1).gre_id,l_assignment_id)
2979 LOOP
2980 l_previous_gre_asg_action_id := c_rec.run_asg_action_id;
2981 EXIT;
2982 END LOOP;
2983 l_flag := FALSE;
2984 ELSIF (i = 1 AND l_record_count = 1)-- There exists only one record, hence take the Projected value
2985 THEN
2986 l_flag := FALSE;
2987 END IF;
2988 pay_in_utils.set_location(g_debug,l_procedure, 90);
2989
2990 OPEN c_termination_check(l_assignment_id);
2991 FETCH c_termination_check INTO l_end_date;
2992 CLOSE c_termination_check;
2993 pay_in_utils.set_location(g_debug,l_procedure, 100);
2994
2995 archive_person_data(p_run_asg_action_id => l_run_asg_action_id
2996 ,p_arc_asg_action_id => p_assignment_action_id
2997 ,p_arc_payroll_act_id => l_arc_pay_action_id
2998 ,p_prepayment_date => l_pre_effective_date
2999 ,p_assignment_id => l_assignment_id
3000 ,p_gre_id => g_asg_tab(i).gre_id
3001 ,p_payroll_run_date => fnd_date.date_to_canonical(l_run_date_earned)
3002 ,p_effective_start_date => g_asg_tab(i).start_date
3003 ,p_effective_end_date => LEAST(g_asg_tab(i).end_date,l_end_date)
3004 );
3005 pay_in_utils.set_location(g_debug,l_procedure, 110);
3006
3007 archive_via_details(p_run_asg_action_id => l_run_asg_action_id
3008 ,p_arc_asg_action_id => p_assignment_action_id
3009 ,p_gre_id => g_asg_tab(i).gre_id
3010 ,p_assignment_id => l_assignment_id
3011 ,p_payroll_date => l_run_date_earned
3012 );
3013 pay_in_utils.set_location(g_debug,l_procedure, 120);
3014
3015 archive_allowances(p_run_asg_action_id => l_run_asg_action_id
3016 ,p_arc_asg_action_id => p_assignment_action_id
3017 ,p_gre_id => g_asg_tab(i).gre_id
3018 ,pre_gre_asg_act_id => l_previous_gre_asg_action_id
3019 ,p_flag => l_flag
3020 );
3021 pay_in_utils.set_location(g_debug,l_procedure, 130);
3022
3023 archive_perquisites(p_run_asg_action_id => l_run_asg_action_id
3024 ,p_arc_asg_action_id => p_assignment_action_id
3025 ,p_gre_id => g_asg_tab(i).gre_id
3026 ,pre_gre_asg_act_id => l_previous_gre_asg_action_id
3027 );
3028 pay_in_utils.set_location(g_debug,l_procedure, 140);
3029
3030 archive_eoy_salary(p_run_asg_action_id => l_run_asg_action_id
3031 ,p_arc_asg_action_id => p_assignment_action_id
3032 ,p_gre_id => g_asg_tab(i).gre_id
3033 );
3034 pay_in_utils.set_location(g_debug,l_procedure, 150);
3035
3036 archive_other_balances(p_run_asg_action_id => l_run_asg_action_id
3037 ,p_arc_asg_action_id => p_assignment_action_id
3038 ,pre_gre_asg_act_id => l_previous_gre_asg_action_id
3039 ,p_gre_id => g_asg_tab(i).gre_id
3040 ,p_start_date => g_asg_tab(i).start_date
3041 ,p_end_date => g_asg_tab(i).end_date
3042 );
3043 pay_in_utils.set_location(g_debug,l_procedure, 160);
3044
3045 OPEN c_pay_action_level_check(l_arc_pay_action_id,g_asg_tab(i).gre_id);
3046 FETCH c_pay_action_level_check INTO l_check;
3047 CLOSE c_pay_action_level_check;
3048 pay_in_utils.set_location(g_debug,l_procedure, 170);
3049
3050 IF l_check IS NULL
3051 THEN
3052 pay_in_utils.set_location(g_debug,l_procedure, 180);
3053 archive_org_data(p_arc_pay_action_id => l_arc_pay_action_id
3054 ,p_gre_id => g_asg_tab(i).gre_id
3055 ,p_effective_end_date => g_system_date
3056 );
3057 END IF;
3058 END IF;
3059
3060 END LOOP;
3061 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
3062 --
3063 EXCEPTION
3064 WHEN OTHERS THEN
3065 IF get_eoy_archival_details%ISOPEN THEN
3066 CLOSE get_eoy_archival_details;
3067 END IF;
3068 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
3069 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
3070 pay_in_utils.trace(l_message,l_procedure);
3071 RAISE;
3072 END archive_code;
3073
3074 END PAY_IN_EOY_ARCHIVE;