[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_EOY_ARCHIVE
Source
1 PACKAGE BODY pay_in_eoy_archive AS
2 /* $Header: pyinpeoy.pkb 120.30.12010000.3 2008/08/06 07:28:41 ubhat 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 FROM per_all_assignments_f asg
580 ,hr_soft_coding_keyflex scl
581 ,per_all_people_f pep
582 WHERE asg.assignment_id = p_assignment_id
583 AND pep.person_id = asg.person_id
584 AND pep.business_group_id = g_bg_id
585 AND asg.business_group_id = g_bg_id
586 AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
587 AND scl.segment1 = TO_CHAR(p_gre_id)
588 AND p_effective_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date
589 AND p_effective_end_date BETWEEN pep.effective_start_date AND pep.effective_end_date ;
590
591 CURSOR c_pos
592 IS
593 SELECT nvl(pos.name,job.name) name
594 FROM per_all_positions pos
595 ,per_assignments_f asg
596 ,per_jobs job
597 WHERE asg.position_id=pos.position_id(+)
598 AND asg.job_id=job.job_id(+)
599 AND asg.assignment_id = p_assignment_id
600 AND asg.business_group_id = g_bg_id
601 AND p_effective_end_date BETWEEN pos.date_effective(+) AND NVL(pos.date_end(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
602 AND p_effective_end_date BETWEEN job.date_from(+) AND NVL(job.date_to(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
603 AND p_effective_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
604
605
606
607 CURSOR c_father_name(p_person_id NUMBER)
608 IS
609 SELECT DECODE(pea.title,NULL,hr_in_utility.per_in_full_name(pea.first_name,pea.middle_names,pea.last_name,pea.title)
610 ,SUBSTR(hr_in_utility.per_in_full_name(pea.first_name,pea.middle_names,pea.last_name,pea.title)
611 ,INSTR(hr_in_utility.per_in_full_name(pea.first_name,pea.middle_names,pea.last_name,pea.title),' ',1)+1))father
612 ,pea.title title
613 FROM per_all_people_f pep
614 ,per_all_people_f pea
615 ,per_contact_relationships con
616 WHERE pep.person_id = p_person_id
617 AND pea.person_id =con.contact_person_id
618 AND pep.business_group_id = g_bg_id
619 AND pea.business_group_id = g_bg_id
620 AND con.person_id=pep.person_id
621 AND con.contact_type='JP_FT'
622 AND p_effective_end_date BETWEEN pep.effective_start_date AND pep.effective_end_date
623 AND p_effective_end_date BETWEEN pea.effective_start_date AND pea.effective_end_date;
624
625 CURSOR c_employee_address(p_person_id NUMBER)
626 IS
627 SELECT address_id
628 ,address_type
629 FROM per_addresses
630 WHERE person_id = p_person_id
631 AND address_type = DECODE(address_type,'IN_P','IN_P','IN_C')
632 AND p_effective_end_date BETWEEN date_from AND nvl(date_to,to_date('31-12-4712','DD-MM-YYYY'))
633 ORDER BY address_type DESC;
634
635 CURSOR c_phone(p_person_id NUMBER)
636 IS
637 SELECT phone_number rep_phone_no
638 ,phone_type
639 FROM per_phones
640 WHERE parent_id = p_person_id
641 AND phone_type = DECODE(phone_type,'H1','H1','M')
642 AND p_effective_end_date BETWEEN date_from AND NVL(date_to,TO_DATE('31-12-4712','DD-MM-YYYY'))
643 ORDER BY phone_type ASC;
644
645 l_emp_no per_all_assignments_f.assignment_number%TYPE;
646 l_person_id per_all_people_f.person_id%TYPE;
647 l_dob VARCHAR2(30);
648 l_pan per_all_people_f.per_information4%TYPE;
649 l_residential_status per_all_people_f.per_information7%TYPE;
650 l_name per_all_people_f.full_name%TYPE;
651 l_emp_title per_all_people_f.title%TYPE;
652 l_emp_fath_title per_all_people_f.title%TYPE;
653 l_father_name per_all_people_f.full_name%TYPE;
654 l_gender per_all_people_f.sex%TYPE;
655 l_pos per_all_positions.name%TYPE;
656 l_employee_address per_addresses.address_id%TYPE;
657 l_employee_address_type per_addresses.address_type%TYPE;
658 l_phone_no per_phones.phone_number%TYPE;
659 l_phone_type per_phones.phone_type%TYPE;
660 l_interest VARCHAR2(2);
661 l_action_info_id NUMBER;
662 l_ovn NUMBER;
663 flag BOOLEAN;
664 -- Added the variable as part of bug 4621622
665 l_effective_end_date DATE;
666 l_message VARCHAR2(255);
667 l_procedure VARCHAR2(100);
668
669
670 BEGIN
671
672 l_procedure := g_package ||'archive_person_data';
673 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
674
675 IF g_debug THEN
676 pay_in_utils.trace('Run Assignment Action id ',p_run_asg_action_id);
677 pay_in_utils.trace('Archive Assignment Action id ',p_arc_asg_action_id);
678 pay_in_utils.trace('Archive payroll Action id ',p_arc_payroll_act_id);
679 pay_in_utils.trace('Prepayment Date ',p_prepayment_date);
680 pay_in_utils.trace('Assignment id ',p_assignment_id);
681 pay_in_utils.trace('GRE id ',p_gre_id);
682 pay_in_utils.trace('Payroll Run Date ',p_payroll_run_date);
683 pay_in_utils.trace('Effective Start Date ',p_effective_start_date);
684 pay_in_utils.trace('Effective End Date ',p_effective_end_date);
685 END IF;
686
687 OPEN c_emp_no;
688 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;
689 CLOSE c_emp_no;
690
691
692 /*
693 OPEN c_person_details(l_person_id);
694 FETCH c_person_details INTO l_pan,l_name,l_emp_title,l_dob,l_gender,l_residential_status;
695 CLOSE c_person_details;
696 */
697 OPEN c_pos;
698 FETCH c_pos INTO l_pos;
699 CLOSE c_pos;
700
701 pay_in_utils.set_location(g_debug,l_procedure, 20);
702
703 OPEN c_father_name(l_person_id);
704 FETCH c_father_name INTO l_father_name,l_emp_fath_title;
705 CLOSE c_father_name;
706
707 OPEN c_employee_address(l_person_id);
708 FETCH c_employee_address INTO l_employee_address,l_employee_address_type;
709 CLOSE c_employee_address;
710
711 OPEN c_phone(l_person_id);
712 FETCH c_phone INTO l_phone_no,l_phone_type;
713 CLOSE c_phone;
714 pay_in_utils.set_location(g_debug,l_procedure, 30);
715 --
716 -- Bug 4621622 : Added this code to handle termination case
717 --
718 IF p_effective_start_date > p_effective_end_date THEN
719 l_effective_end_date := fnd_date.string_to_date('31-MAR-' || TO_CHAR(add_months(p_effective_start_date,12),'YYYY'),'DD-MM-YYYY');
720 ELSE
721 l_effective_end_date := p_effective_end_date;
722 END IF;
723 --
724 -- Bug 4621622 changes end
725 --
726 pay_in_utils.set_location(g_debug,l_procedure, 40);
727
728 pay_action_information_api.create_action_information
729 (p_action_context_id => p_arc_asg_action_id
730 ,p_action_context_type => 'AAP'
731 ,p_action_information_category => 'IN_EOY_PERSON'
732 ,p_source_id => p_run_asg_action_id
733 ,p_effective_date => p_prepayment_date
734 ,p_assignment_id => p_assignment_id
735 ,p_action_information1 => l_emp_no
736 ,p_action_information2 => g_year
737 ,p_action_information3 => p_gre_id
738 ,p_action_information4 => l_pan
739 ,p_action_information5 => l_name
740 ,p_action_information6 => l_emp_title
741 ,p_action_information7 => l_father_name
742 ,p_action_information8 => l_emp_fath_title
743 ,p_action_information9 => l_pos
744 ,p_action_information10 => l_dob
745 ,p_action_information11 => l_gender
746 ,p_action_information12 => l_interest
747 ,p_action_information13 => l_person_id
748 ,p_action_information14 => l_employee_address
749 ,p_action_information15 => l_residential_status
750 ,p_action_information16 => l_phone_no
751 ,p_action_information17 => p_effective_start_date
752 -- Bug 4621622 : Changed p_effective_end_date to l_effective_end_date
753 ,p_action_information18 => l_effective_end_date
754 ,p_action_information19 => p_arc_payroll_act_id
755 ,p_action_information20 => p_payroll_run_date
756 ,p_action_information_id => l_action_info_id
757 ,p_object_version_number => l_ovn
758 );
759
760 IF g_debug THEN
761 pay_in_utils.trace('Employee Name ',l_name);
762 pay_in_utils.trace('Employee Number ',l_emp_no);
763 pay_in_utils.trace('Start Date ',p_effective_start_date);
764 pay_in_utils.trace('End Date ',l_effective_end_date);
765 END IF;
766
767 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
768
769 END archive_person_data;
770
771 --------------------------------------------------------------------------
772 -- --
773 -- Name : BALANCE_DIFFERENCE --
774 -- Type : PROCEDURE --
775 -- Access : Public --
776 -- Description : This procedure determines the balance difference. --
777 -- Parameters : --
778 -- IN : p_arc_pay_action_id NUMBER --
779 -- p_gre_id NUMBER --
780 -- p_effective_end_date DATE --
781 -- OUT : N/A --
782 -- --
783 -- Change History : --
784 --------------------------------------------------------------------------
785 -- Rev# Date Userid Description --
786 --------------------------------------------------------------------------
787 -- 115.0 09-SEP-2005 aaagarwa Initial Version --
788 --------------------------------------------------------------------------
789 PROCEDURE balance_difference(g_result_table1 IN pay_balance_pkg.t_detailed_bal_out_tab
790 ,g_result_table2 IN pay_balance_pkg.t_detailed_bal_out_tab
791 ,g_result_table IN OUT NOCOPY pay_balance_pkg.t_detailed_bal_out_tab
792 )
793 IS
794 l_message VARCHAR2(255);
795 l_procedure VARCHAR2(100);
796
797 BEGIN
798
799 l_procedure := g_package ||'balance_difference';
800 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
801
802 FOR i IN 1..GREATEST(g_result_table1.COUNT,g_result_table2.COUNT)
803 LOOP
804 g_result_table(i).balance_value :=
805 NVL(g_result_table1(i).balance_value,0)
806 - NVL(g_result_table2(i).balance_value,0);
807 END LOOP;
808 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
809
810 END;
811 --------------------------------------------------------------------------
812 -- --
813 -- Name : ARCHIVE_BALANCES --
814 -- Type : PROCEDURE --
815 -- Access : Public --
816 -- Description : This generic procedure archives the balances based --
817 -- on the Source Text 2 --
818 -- Parameters : --
819 -- IN : p_arc_pay_action_id NUMBER --
820 -- p_gre_id NUMBER --
821 -- p_effective_end_date DATE --
822 -- OUT : N/A --
823 -- --
824 -- Change History : --
825 --------------------------------------------------------------------------
826 -- Rev# Date Userid Description --
827 --------------------------------------------------------------------------
828 -- 115.0 23-MAY-2005 aaagarwa Initial Version --
829 --------------------------------------------------------------------------
830 PROCEDURE archive_balances(p_run_asg_action_id IN NUMBER
831 ,pre_gre_asg_act_id IN NUMBER DEFAULT NULL
832 ,p_arc_asg_action_id IN NUMBER
833 ,p_gre_id IN NUMBER
834 ,p_action_inf_category IN VARCHAR2
835 ,p_balance_name IN VARCHAR2
836 ,p_balance_name1 IN VARCHAR2 DEFAULT NULL
837 ,p_balance_name2 IN VARCHAR2 DEFAULT NULL
838 ,p_balance_name3 IN VARCHAR2 DEFAULT NULL
839 ,p_balance_dimension IN VARCHAR2
840 ,p_balance_dimension1 IN VARCHAR2 DEFAULT NULL
841 ,p_balance_dimension2 IN VARCHAR2 DEFAULT NULL
842 ,p_balance_dimension3 IN VARCHAR2 DEFAULT NULL
843 ,g_context_table IN OUT NOCOPY pay_balance_pkg.t_context_tab
844 ,g_result_table IN OUT NOCOPY pay_balance_pkg.t_detailed_bal_out_tab
845 ,g_result_table1 IN OUT NOCOPY pay_balance_pkg.t_detailed_bal_out_tab
846 ,g_result_table2 IN OUT NOCOPY pay_balance_pkg.t_detailed_bal_out_tab
847 ,g_result_table3 IN OUT NOCOPY pay_balance_pkg.t_detailed_bal_out_tab
848 ,g_balance_value_tab IN OUT NOCOPY pay_balance_pkg.t_balance_value_tab
849 )
850 IS
851
852 l_action_info_id NUMBER;
853 l_ovn NUMBER;
854 l_result_table1 pay_balance_pkg.t_detailed_bal_out_tab;
855 l_result_table2 pay_balance_pkg.t_detailed_bal_out_tab;
856 l_message VARCHAR2(255);
857 l_procedure VARCHAR2(100);
858 l_result_table4 pay_balance_pkg.t_detailed_bal_out_tab;
859
860
861 BEGIN
862
863 l_procedure := g_package ||'archive_balances';
864 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
865
866
867 IF g_debug THEN
868 pay_in_utils.trace('Run Asg Action id ',p_run_asg_action_id);
869 pay_in_utils.trace('Prev GRE Asg action id ',pre_gre_asg_act_id);
870 pay_in_utils.trace('Archive Asg Action id ',p_arc_asg_action_id);
871 pay_in_utils.trace('GRE id ',p_gre_id);
872 pay_in_utils.trace('Action Info Category ',p_action_inf_category);
873 pay_in_utils.trace('Balance name ',p_balance_name);
874 pay_in_utils.trace('Balance name1 ',p_balance_name1);
875 pay_in_utils.trace('Balance name2 ',p_balance_name2);
876 pay_in_utils.trace('Balance name31 ',p_balance_name3);
877 pay_in_utils.trace('Dimension Name ',p_balance_dimension);
878 pay_in_utils.trace('Dimension Name1 ',p_balance_dimension1);
879 pay_in_utils.trace('Dimension Name2 ',p_balance_dimension2);
880 pay_in_utils.trace('Dimension Name3 ',p_balance_dimension3);
881
882 END IF;
883
884 /* Allowance Advance functionality Start */
885 IF (p_action_inf_category ='IN_EOY_ALLOW') THEN
886 pay_in_utils.set_location(g_debug,l_procedure, 21);
887
888 g_balance_value_tab(1).defined_balance_id :=
889 pay_in_tax_utils.get_defined_balance('Adjusted Advance for Allowances','_ASG_COMP_YTD');
890
891 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
892 ,p_defined_balance_lst => g_balance_value_tab
893 ,p_context_lst => g_context_table
894 ,p_output_table => l_result_table1
895 );
896 pay_in_utils.set_location(g_debug,l_procedure, 22);
897
898 IF pre_gre_asg_act_id IS NOT NULL
899 THEN
900 pay_in_utils.set_location(g_debug,l_procedure, 30);
901 pay_balance_pkg.get_value(p_assignment_action_id => pre_gre_asg_act_id
902 ,p_defined_balance_lst => g_balance_value_tab
903 ,p_context_lst => g_context_table
904 ,p_output_table => l_result_table2
905 );
906 balance_difference(l_result_table1,l_result_table2,l_result_table4);
907 ELSE
908 l_result_table4 := l_result_table1;
909 END IF;
910 pay_in_utils.set_location(g_debug,l_procedure, 23);
911
912 l_result_table1.DELETE;
913 l_result_table2.DELETE;
914
915
916 END IF;
917 pay_in_utils.set_location(g_debug,l_procedure, 25);
918 /* Allowance Advance functionality End*/
919
920 g_balance_value_tab(1).defined_balance_id :=
921 pay_in_tax_utils.get_defined_balance(p_balance_name,p_balance_dimension);
922
923 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
924 ,p_defined_balance_lst => g_balance_value_tab
925 ,p_context_lst => g_context_table
926 ,p_output_table => l_result_table1--g_result_table
927 );
928
929 pay_in_utils.set_location(g_debug,l_procedure, 20);
930
931 IF pre_gre_asg_act_id IS NOT NULL
932 THEN
933 pay_in_utils.set_location(g_debug,l_procedure, 30);
934 pay_balance_pkg.get_value(p_assignment_action_id => pre_gre_asg_act_id
935 ,p_defined_balance_lst => g_balance_value_tab
936 ,p_context_lst => g_context_table
937 ,p_output_table => l_result_table2
938 );
939 balance_difference(l_result_table1,l_result_table2,g_result_table);
940 ELSE
941 g_result_table := l_result_table1;
942 END IF;
943
944 pay_in_utils.set_location(g_debug,l_procedure, 40);
945
946 IF (p_balance_name1 IS NOT NULL)
947 THEN
948 pay_in_utils.set_location(g_debug,l_procedure, 50);
949 g_balance_value_tab(1).defined_balance_id :=
950 pay_in_tax_utils.get_defined_balance(p_balance_name1,p_balance_dimension1);
951
952 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
953 ,p_defined_balance_lst => g_balance_value_tab
954 ,p_context_lst => g_context_table
955 ,p_output_table => l_result_table1--g_result_table1
956 );
957 IF pre_gre_asg_act_id IS NOT NULL AND p_action_inf_category = 'IN_EOY_PERQ'
958 THEN
959 pay_in_utils.set_location(g_debug,l_procedure, 60);
960 pay_balance_pkg.get_value(p_assignment_action_id => pre_gre_asg_act_id
961 ,p_defined_balance_lst => g_balance_value_tab
962 ,p_context_lst => g_context_table
963 ,p_output_table => l_result_table2
964 );
965 balance_difference(l_result_table1,l_result_table2,g_result_table1);
966 ELSE
967 pay_in_utils.set_location(g_debug,l_procedure, 70);
968 g_result_table1 := l_result_table1;
969 END IF;
970 END IF;
971 pay_in_utils.set_location(g_debug,l_procedure, 80);
972
973 IF (p_balance_name2 IS NOT NULL)
974 THEN
975 pay_in_utils.set_location(g_debug,l_procedure, 90);
976 g_balance_value_tab(1).defined_balance_id :=
977 pay_in_tax_utils.get_defined_balance(p_balance_name2,p_balance_dimension2);
978
979 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
980 ,p_defined_balance_lst => g_balance_value_tab
981 ,p_context_lst => g_context_table
982 ,p_output_table => l_result_table1--g_result_table2
983 );
984 IF pre_gre_asg_act_id IS NOT NULL
985 THEN
986 pay_in_utils.set_location(g_debug,l_procedure, 100);
987 pay_balance_pkg.get_value(p_assignment_action_id => pre_gre_asg_act_id
988 ,p_defined_balance_lst => g_balance_value_tab
989 ,p_context_lst => g_context_table
990 ,p_output_table => l_result_table2
991 );
992 balance_difference(l_result_table1,l_result_table2,g_result_table2);
993 ELSE
994 g_result_table2 := l_result_table1;
995 END IF;
996 END IF;
997 pay_in_utils.set_location(g_debug,l_procedure, 110);
998
999 IF (p_balance_name3 IS NOT NULL)
1000 THEN
1001 pay_in_utils.set_location(g_debug,l_procedure, 120);
1002 g_balance_value_tab(1).defined_balance_id :=
1003 pay_in_tax_utils.get_defined_balance(p_balance_name3,p_balance_dimension3);
1004
1005 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
1006 ,p_defined_balance_lst => g_balance_value_tab
1007 ,p_context_lst => g_context_table
1008 ,p_output_table => g_result_table3
1009 );
1010 END IF;
1011
1012 pay_in_utils.set_location(g_debug,l_procedure, 130);
1013 pay_in_utils.trace('**************************************************','********************');
1014 IF (p_action_inf_category = 'IN_EOY_ALLOW')
1015 THEN
1016 pay_in_utils.set_location(g_debug,l_procedure, 140);
1017 FOR i IN 1..g_context_table.COUNT
1018 LOOP
1019 IF ((g_result_table(i).balance_value <> 0)
1020 OR(NVL(g_result_table1(i).balance_value,0) <> 0)
1021 OR(NVL(g_result_table2(i).balance_value,0) <> 0)
1022 OR(NVL(g_result_table3(i).balance_value,0) <> 0)
1023 OR(NVL(l_result_table4(i).balance_value,0) <> 0)
1024 )
1025 THEN
1026 pay_action_information_api.create_action_information
1027 (p_action_context_id => p_arc_asg_action_id
1028 ,p_action_context_type => 'AAP'
1029 ,p_action_information_category => p_action_inf_category
1030 ,p_source_id => p_run_asg_action_id
1031 ,p_action_information1 => g_context_table(i).source_text2
1032 ,p_action_information2 => (NVL(g_result_table(i).balance_value,0) + NVL(l_result_table4(i).balance_value,0) )
1033 ,p_action_information3 => NVL(g_result_table1(i).balance_value,0)
1034 ,p_action_information4 => NVL(g_result_table2(i).balance_value,0)
1035 ,p_action_information5 => NVL(g_result_table3(i).balance_value,0)
1036 ,p_action_information_id => l_action_info_id
1037 ,p_object_version_number => l_ovn
1038 );
1039 IF g_debug THEN
1040 pay_in_utils.trace('ALLOWANCE Name ',g_context_table(i).source_text2);
1041 pay_in_utils.trace('ALLOWANCE Amt ',NVL(g_result_table(i).balance_value,0));
1042 pay_in_utils.trace('ALLOWANCE Taxable Amt ',NVL(g_result_table1(i).balance_value,0));
1043 pay_in_utils.trace('ALLOWANCE Std Amt ',NVL(g_result_table2(i).balance_value,0));
1044 pay_in_utils.trace('ALLOWANCE Std Taxable Amt ',NVL(g_result_table3(i).balance_value,0));
1045 END IF;
1046
1047 END IF;
1048 END LOOP;
1049 ELSIF (p_action_inf_category = 'IN_EOY_PERQ')
1050 THEN
1051 pay_in_utils.set_location(g_debug,l_procedure, 150);
1052 FOR i IN 1..g_context_table.COUNT
1053 LOOP
1054 IF ((g_result_table(i).balance_value <> 0)
1055 OR(NVL(g_result_table1(i).balance_value,0) <> 0)
1056 )
1057 THEN
1058 pay_action_information_api.create_action_information
1059 (p_action_context_id => p_arc_asg_action_id
1060 ,p_action_context_type => 'AAP'
1061 ,p_action_information_category => p_action_inf_category
1062 ,p_source_id => p_run_asg_action_id
1063 ,p_action_information1 => g_context_table(i).source_text2
1064 ,p_action_information2 => NVL(g_result_table(i).balance_value,0)
1065 ,p_action_information3 => NVL(g_result_table1(i).balance_value,0)
1066 ,p_action_information_id => l_action_info_id
1067 ,p_object_version_number => l_ovn
1068 );
1069
1070 IF g_debug THEN
1071 pay_in_utils.trace('PERQ Name ',g_context_table(i).source_text2);
1072 pay_in_utils.trace('PERQ Taxable Amt ',NVL(g_result_table(i).balance_value,0));
1073 pay_in_utils.trace('PERQ Employee Contribution ',NVL(g_result_table1(i).balance_value,0));
1074 END IF;
1075
1076 END IF;
1077 END LOOP;
1078 ELSE
1079 pay_in_utils.set_location(g_debug,l_procedure, 160);
1080 FOR i IN 1..g_context_table.COUNT
1081 LOOP
1082 IF (g_result_table(i).balance_value <> 0)
1083 THEN
1084 pay_action_information_api.create_action_information
1085 (p_action_context_id => p_arc_asg_action_id
1086 ,p_action_context_type => 'AAP'
1087 ,p_action_information_category => p_action_inf_category
1088 ,p_source_id => p_run_asg_action_id
1089 ,p_action_information1 => g_context_table(i).source_text2
1090 ,p_action_information2 => g_result_table(i).balance_value
1091 ,p_action_information_id => l_action_info_id
1092 ,p_object_version_number => l_ovn
1093 );
1094
1095 IF g_debug THEN
1096 pay_in_utils.trace('Oth Balance name ',g_context_table(i).source_text2);
1097 pay_in_utils.trace('Oth Balance Value ',g_result_table(i).balance_value);
1098 END IF;
1099
1100
1101 END IF;
1102 END LOOP;
1103 END IF;
1104 pay_in_utils.trace('**************************************************','********************');
1105 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 170);
1106
1107 END archive_balances;
1108 --------------------------------------------------------------------------
1109 -- --
1110 -- Name : ARCHIVE_VIA_DETAILS --
1111 -- Type : PROCEDURE --
1112 -- Access : Public --
1113 -- Description : This procedure archives the Chapter VI A related --
1114 -- balance details --
1115 -- Parameters : --
1116 -- IN : p_arc_pay_action_id NUMBER --
1117 -- p_gre_id NUMBER --
1118 -- p_effective_end_date DATE --
1119 -- OUT : N/A --
1120 -- --
1121 -- Change History : --
1122 --------------------------------------------------------------------------
1123 -- Rev# Date Userid Description --
1124 --------------------------------------------------------------------------
1125 -- 115.0 23-MAY-2005 aaagarwa Initial Version --
1126 --------------------------------------------------------------------------
1127 PROCEDURE archive_via_details(p_run_asg_action_id IN NUMBER
1128 ,p_arc_asg_action_id IN NUMBER
1129 ,p_gre_id IN NUMBER
1130 ,p_assignment_id IN NUMBER
1131 ,p_payroll_date IN DATE
1132 )
1133 IS
1134
1135 CURSOR c_defined_balance_id--80D,80DD,80DDB,80G,80GGA
1136 IS
1137 SELECT pdb.defined_balance_id balance_id
1138 ,pbt.balance_name balance_name
1139 FROM pay_balance_types pbt
1140 ,pay_balance_dimensions pbd
1141 ,pay_defined_balances pdb
1142 WHERE pbt.balance_name IN(
1143 'F16 Deductions Sec 80D'
1144 ,'F16 Deductions Sec 80DD'
1145 ,'F16 Deductions Sec 80DDB'
1146 ,'F16 Deductions Sec 80G'
1147 ,'F16 Deductions Sec 80GGA'
1148 )
1149 AND pbd.dimension_name='_ASG_LE_PTD'
1150 AND pbt.legislation_code = 'IN'
1151 AND pbd.legislation_code = 'IN'
1152 AND pbt.balance_type_id = pdb.balance_type_id
1153 AND pbd.balance_dimension_id = pdb.balance_dimension_id
1154 ORDER BY pbt.balance_name;
1155
1156 CURSOR c_def_balance_id--80E,80GG and 80U
1157 IS
1158 SELECT pdb.defined_balance_id balance_id
1159 ,pbt.balance_name balance_name
1160 FROM pay_balance_types pbt
1161 ,pay_balance_dimensions pbd
1162 ,pay_defined_balances pdb
1163 WHERE pbt.balance_name IN(
1164 'F16 Deductions Sec 80CCE'
1165 ,'F16 Deductions Sec 80E'
1166 ,'F16 Deductions Sec 80GG'
1167 ,'F16 Deductions Sec 80U'
1168 ,'F16 Employee PF Contribution'
1169 ,'F16 Total Chapter VI A Deductions'
1170 )
1171 AND pbd.dimension_name='_ASG_LE_PTD'
1172 AND pbt.legislation_code = 'IN'
1173 AND pbd.legislation_code = 'IN'
1174 AND pbt.balance_type_id = pdb.balance_type_id
1175 AND pbd.balance_dimension_id = pdb.balance_dimension_id
1176 ORDER BY pbt.balance_name;
1177
1178 g_bal_name_tab t_bal_name_tab;
1179 g_balance_value_tab pay_balance_pkg.t_balance_value_tab;
1180 g_balance_value_tab1 pay_balance_pkg.t_balance_value_tab;
1181 g_context_table pay_balance_pkg.t_context_tab;
1182 g_result_table pay_balance_pkg.t_detailed_bal_out_tab;
1183 g_result_table1 pay_balance_pkg.t_detailed_bal_out_tab;
1184 g_result_table2 pay_balance_pkg.t_detailed_bal_out_tab;
1185 g_result_table3 pay_balance_pkg.t_detailed_bal_out_tab;
1186
1187 i NUMBER;
1188 l_defined_balance_id NUMBER;
1189 l_action_info_id NUMBER;
1190 l_ovn NUMBER;
1191 l_pf_contr NUMBER;
1192 l_da_gross NUMBER;
1193 l_da_qa_amt NUMBER;
1194 l_scss_qa_amt NUMBER;
1195 l_scss_gross NUMBER;
1196 l_li_gross NUMBER;
1197 l_li_qa_amt NUMBER;
1198 l_pension_qa_amt NUMBER;
1199 l_pension_gross NUMBER;
1200 l_balance_defined_id NUMBER;
1201 l_ytd_val NUMBER;
1202 l_ptd_val NUMBER;
1203 l_classification hr_organization_information.org_information3%TYPE;
1204 l_message VARCHAR2(255);
1205 l_procedure VARCHAR2(100);
1206 l_80ccd_gross NUMBER ;
1207 l_80ccd_qa_amt NUMBER ;
1208 BEGIN
1209 --Qualifying Amount determination and archival for 80E,80GG and 80U
1210
1211 l_procedure := g_package ||'archive_via_details';
1212 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1213
1214
1215 i := 1;
1216 g_bal_name_tab.DELETE;
1217
1218
1219 FOR c_rec IN c_def_balance_id
1220 LOOP
1221 g_balance_value_tab(i).defined_balance_id := c_rec.balance_id;
1222 g_bal_name_tab(i).balance_name := c_rec.balance_name;
1223 i := i + 1;
1224 END LOOP;
1225
1226 pay_in_utils.set_location(g_debug,l_procedure, 20);
1227
1228 g_context_table(1).tax_unit_id := p_gre_id;
1229
1230 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
1231 ,p_defined_balance_lst => g_balance_value_tab
1232 ,p_context_lst => g_context_table
1233 ,p_output_table => g_result_table
1234 );
1235
1236 pay_in_utils.set_location(g_debug,l_procedure, 30);
1237 pay_in_utils.trace('**************************************************','********************');
1238 FOR i IN 1..g_balance_value_tab.COUNT
1239 LOOP
1240 IF (g_result_table(i).balance_value <> 0)
1241 THEN
1242 pay_in_utils.set_location(g_debug,l_procedure, 40);
1243 pay_action_information_api.create_action_information
1244 (p_action_context_id => p_arc_asg_action_id
1245 ,p_action_context_type => 'AAP'
1246 ,p_action_information_category => 'IN_EOY_VIA'
1247 ,p_source_id => p_run_asg_action_id
1248 ,p_action_information1 => g_bal_name_tab(i).balance_name
1249 ,p_action_information2 => g_result_table(i).balance_value
1250 ,p_action_information_id => l_action_info_id
1251 ,p_object_version_number => l_ovn
1252 );
1253 IF g_debug THEN
1254 pay_in_utils.trace('VIA Balance name ',g_bal_name_tab(i).balance_name);
1255 pay_in_utils.trace('VIA Balance Value ',g_result_table(i).balance_value);
1256 END IF;
1257
1258 END IF;
1259 END LOOP;
1260
1261 --Qualifying Amount determination for 80D,80DD,80DDB,80G,80GGA
1262 i := 1;
1263 g_bal_name_tab.DELETE;
1264 g_balance_value_tab.DELETE;
1265 g_result_table.DELETE;
1266
1267 pay_in_utils.set_location(g_debug,l_procedure, 50);
1268
1269 FOR c_rec IN c_defined_balance_id
1270 LOOP
1271 g_balance_value_tab(i).defined_balance_id := c_rec.balance_id;
1272 g_bal_name_tab(i).balance_name := c_rec.balance_name;
1273 i := i + 1;
1274 END LOOP;
1275
1276
1277
1278 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
1279 ,p_defined_balance_lst => g_balance_value_tab
1280 ,p_context_lst => g_context_table
1281 ,p_output_table => g_result_table
1282 );
1283 pay_in_utils.set_location(g_debug,l_procedure, 60);
1284
1285 --Gross Amount determination for 80D,80DD,80DDB,80G,80GGA
1286
1287 g_result_table1.DELETE;
1288 g_balance_value_tab1.DELETE;
1289 g_context_table.DELETE;
1290
1291 g_context_table(1).source_text2 := 'Medical Insurance'; -- 80D
1292 g_context_table(2).source_text2 := 'Disabled Dependents'; -- 80DD
1293 g_context_table(3).source_text2 := 'Disease Treatment'; -- 80DDB
1294 g_context_table(4).source_text2 := 'Donations'; -- 80G
1295 g_context_table(5).source_text2 := 'Research Donation'; -- 80GGA
1296
1297 FOR i IN 1..5
1298 LOOP
1299 g_context_table(i).tax_unit_id := p_gre_id;
1300 END LOOP;
1301
1302 g_balance_value_tab1(1).defined_balance_id :=
1303 pay_in_tax_utils.get_defined_balance('Gross Chapter VIA Deductions','_ASG_LE_COMP_PTD');
1304
1305 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
1306 ,p_defined_balance_lst => g_balance_value_tab1
1307 ,p_context_lst => g_context_table
1308 ,p_output_table => g_result_table1
1309 );
1310
1311 pay_in_utils.set_location(g_debug,l_procedure, 70);
1312
1313 --Archiving the QA and Gross Amount in the same record for 80D,80DD,80DDB,80G,80GGA
1314 FOR i IN 1..g_balance_value_tab.COUNT
1315 LOOP
1316 IF ((g_result_table(i).balance_value <> 0)OR(g_result_table1(i).balance_value <> 0))
1317 THEN
1318 pay_in_utils.set_location(g_debug,l_procedure, 80);
1319 pay_action_information_api.create_action_information
1320 (p_action_context_id => p_arc_asg_action_id
1321 ,p_action_context_type => 'AAP'
1322 ,p_action_information_category => 'IN_EOY_VIA'
1323 ,p_source_id => p_run_asg_action_id
1324 ,p_action_information1 => g_bal_name_tab(i).balance_name
1325 ,p_action_information2 => g_result_table(i).balance_value
1326 ,p_action_information3 => g_result_table1(i).balance_value
1327 ,p_action_information_id => l_action_info_id
1328 ,p_object_version_number => l_ovn
1329 );
1330
1331 IF g_debug THEN
1332 pay_in_utils.trace('VIA Balance name ',g_bal_name_tab(i).balance_name);
1333 pay_in_utils.trace('VIA Qualifying Amt ',g_result_table(i).balance_value);
1334 pay_in_utils.trace('VIA Gross Amt ',g_result_table1(i).balance_value);
1335 END IF;
1336
1337
1338 END IF;
1339 END LOOP;
1340
1341 --Archival for 80CCE elements start here
1342 g_balance_value_tab.DELETE;
1343 g_context_table.DELETE;
1344 g_result_table1.DELETE;
1345 g_result_table.DELETE;
1346
1347 g_context_table(1).source_text2 := 'House Loan Repayment';
1348 g_context_table(2).source_text2 := 'Public Provident Fund';
1349 g_context_table(3).source_text2 := 'Interest on NSC';
1350 g_context_table(4).source_text2 := 'Mutual Fund or UTI';
1351 g_context_table(5).source_text2 := 'National Housing Bank';
1352 g_context_table(6).source_text2 := 'ULIP';
1353 g_context_table(7).source_text2 := 'Notified Annuity Plan';
1354 g_context_table(8).source_text2 := 'Notified Pension Fund';
1355 g_context_table(9).source_text2 := 'Public Sector Scheme';
1356 g_context_table(10).source_text2 := 'Superannuation Fund';
1357 g_context_table(11).source_text2 := 'Infrastructure Bonds';
1358 g_context_table(12).source_text2 := 'NSC';
1359 g_context_table(13).source_text2 := 'Deposits in Govt. Security';
1360 g_context_table(14).source_text2 := 'Notified Deposit Scheme';
1361 g_context_table(15).source_text2 := 'Approved Shares or Debentures';
1362 g_context_table(16).source_text2 := 'Approved Mutual Fund';
1363 g_context_table(17).source_text2 := 'Tuition fee';
1364 g_context_table(18).source_text2 := 'Fixed Deposits';
1365 g_context_table(19).source_text2 := 'Five Year Post Office Time Deposit Account';
1366 g_context_table(20).source_text2 := 'NABARD Bank Deposits';
1367
1368
1369 FOR i IN 1..20
1370 LOOP
1371 g_context_table(i).tax_unit_id := p_gre_id;
1372 END LOOP;
1373
1374
1375 archive_balances(p_run_asg_action_id => p_run_asg_action_id
1376 ,p_arc_asg_action_id => p_arc_asg_action_id
1377 ,p_gre_id => p_gre_id
1378 ,p_action_inf_category => 'IN_EOY_VIA'
1379 ,p_balance_name => 'Deductions under Section 80CCE'
1380 ,p_balance_dimension => '_ASG_LE_COMP_PTD'
1381 ,g_context_table => g_context_table
1382 ,g_result_table => g_result_table
1383 ,g_result_table1 => g_result_table1
1384 ,g_result_table2 => g_result_table2
1385 ,g_result_table3 => g_result_table3
1386 ,g_balance_value_tab => g_balance_value_tab
1387 );
1388
1389 pay_in_utils.set_location(g_debug,l_procedure, 90);
1390
1391 --Archive record for Deferred Anuity and Life Insurance Premium
1392 g_context_table.DELETE;
1393 g_result_table1.DELETE;
1394 g_result_table2.DELETE;
1395 g_result_table3.DELETE;
1396 g_result_table.DELETE;
1397 g_balance_value_tab.DELETE;
1398 g_balance_value_tab1.DELETE;
1399
1400 g_context_table(1).source_text2 := 'Life Insurance Premium';
1401 g_context_table(2).source_text2 := 'Deferred Annuity';
1402 g_context_table(3).source_text2 := 'Pension Fund 80CCC';
1403 g_context_table(4).source_text2 := 'Senior Citizens Savings Scheme';
1404
1405 FOR i IN 1..4
1406 LOOP
1407 g_context_table(i).tax_unit_id := p_gre_id;
1408 END LOOP;
1409
1410
1411 g_balance_value_tab(1).defined_balance_id :=
1412 pay_in_tax_utils.get_defined_balance('Deductions under Section 80CCE','_ASG_LE_COMP_PTD');
1413
1414 -- Qualifying Amounts for Life Insurance and Deferred Annuity obtained
1415 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
1416 ,p_defined_balance_lst => g_balance_value_tab
1417 ,p_context_lst => g_context_table
1418 ,p_output_table => g_result_table
1419 );
1420
1421 l_li_qa_amt := NVL(g_result_table(1).balance_value,0);
1422 l_da_qa_amt := NVL(g_result_table(2).balance_value,0);
1423 l_pension_qa_amt := NVL(g_result_table(3).balance_value,0);
1424 l_scss_qa_amt := NVL(g_result_table(4).balance_value,0);
1425
1426 --Gross Amount for Life Insurance
1427 g_context_table.DELETE;
1428
1429 pay_in_utils.set_location(g_debug,l_procedure, 100);
1430 g_context_table(1).source_text2 := 'Life Insurance Premium';
1431 g_context_table(1).tax_unit_id := p_gre_id;
1432 g_balance_value_tab1(1).defined_balance_id :=
1433 pay_in_tax_utils.get_defined_balance('Gross Chapter VIA Deductions','_ASG_LE_COMP_PTD');
1434
1435 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
1436 ,p_defined_balance_lst => g_balance_value_tab1
1437 ,p_context_lst => g_context_table
1438 ,p_output_table => g_result_table1
1439 );
1440
1441 l_li_gross := NVL(g_result_table1(1).balance_value,0);
1442
1443 g_balance_value_tab1.DELETE;
1444 g_result_table1.DELETE;
1445
1446 pay_in_utils.set_location(g_debug,l_procedure, 120);
1447 --Gross Amount for Deferred Annuity
1448 g_context_table.DELETE;
1449 g_context_table(1).tax_unit_id := p_gre_id;
1450
1451 g_balance_value_tab1(1).defined_balance_id :=
1452 pay_in_tax_utils.get_defined_balance('Deferred Annuity','_ASG_LE_PTD');
1453
1454 --Gross Amount for Pension Fund 80CCC
1455
1456 g_balance_value_tab1(2).defined_balance_id :=
1457 pay_in_tax_utils.get_defined_balance('Pension Fund','_ASG_LE_PTD');
1458
1459 --Gross Amount for Senior Citizens
1460
1461 g_balance_value_tab1(3).defined_balance_id :=
1462 pay_in_tax_utils.get_defined_balance('Senior Citizens Savings Scheme','_ASG_LE_PTD');
1463
1464
1465 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
1466 ,p_defined_balance_lst => g_balance_value_tab1
1467 ,p_context_lst => g_context_table
1468 ,p_output_table => g_result_table1
1469 );
1470
1471
1472
1473 l_da_gross := NVL(g_result_table1(1).balance_value,0);
1474
1475 l_pension_gross := NVL(g_result_table1(2).balance_value,0);
1476
1477 l_scss_gross := NVL(g_result_table1(3).balance_value,0);
1478
1479
1480 pay_in_utils.set_location(g_debug,l_procedure, 140);
1481
1482 g_balance_value_tab1.DELETE;
1483 g_result_table1.DELETE;
1484 g_context_table.DELETE;
1485
1486 --Gross Amount and Qualifying Amount for 80CCD
1487 g_context_table(1).tax_unit_id := p_gre_id;
1488 g_balance_value_tab1(1).defined_balance_id :=
1489 pay_in_tax_utils.get_defined_balance('F16 ER Pension Contribution','_ASG_LE_PTD');
1490 g_balance_value_tab1(2).defined_balance_id :=
1491 pay_in_tax_utils.get_defined_balance('F16 Section 80CCD','_ASG_LE_PTD');
1492
1493 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
1494 ,p_defined_balance_lst => g_balance_value_tab1
1495 ,p_context_lst => g_context_table
1496 ,p_output_table => g_result_table1
1497 );
1498
1499 l_80ccd_gross := NVL(g_result_table1(1).balance_value,0);
1500 l_80ccd_qa_amt := NVL(g_result_table1(2).balance_value,0);
1501
1502 g_balance_value_tab1.DELETE;
1503 g_result_table1.DELETE;
1504 g_context_table.DELETE;
1505
1506 --Archival of Deferred Annuity, Pension Fund 80CCC and Life Insurance starts
1507 IF (l_li_qa_amt <> 0 OR l_li_gross <> 0)
1508 THEN
1509 pay_action_information_api.create_action_information
1510 (p_action_context_id => p_arc_asg_action_id
1511 ,p_action_context_type => 'AAP'
1512 ,p_action_information_category => 'IN_EOY_VIA'
1513 ,p_source_id => p_run_asg_action_id
1514 ,p_action_information1 => 'Life Insurance Premium'
1515 ,p_action_information2 => l_li_qa_amt
1516 ,p_action_information3 => l_li_gross
1517 ,p_action_information_id => l_action_info_id
1518 ,p_object_version_number => l_ovn
1519 );
1520 IF g_debug THEN
1521 pay_in_utils.trace('VIA LIC Qualifying Amt ',l_li_qa_amt);
1522 pay_in_utils.trace('VIA LIC Gross Amt ',l_li_gross);
1523 END IF;
1524 END IF;
1525
1526 IF (l_da_gross <> 0 OR l_da_qa_amt <> 0)
1527 THEN
1528 pay_action_information_api.create_action_information
1529 (p_action_context_id => p_arc_asg_action_id
1530 ,p_action_context_type => 'AAP'
1531 ,p_action_information_category => 'IN_EOY_VIA'
1532 ,p_source_id => p_run_asg_action_id
1533 ,p_action_information1 => 'Deferred Annuity'
1534 ,p_action_information2 => l_da_qa_amt
1535 ,p_action_information3 => l_da_gross
1536 ,p_action_information_id => l_action_info_id
1537 ,p_object_version_number => l_ovn
1538 );
1539 IF g_debug THEN
1540 pay_in_utils.trace('VIA Deferred Annuity Qualifying Amt ',l_da_qa_amt);
1541 pay_in_utils.trace('VIA Deferred Annuity Gross Amt ',l_da_gross);
1542 END IF;
1543
1544 END IF;
1545
1546 IF (l_pension_gross <> 0 OR l_pension_qa_amt <> 0)
1547 THEN
1548 pay_action_information_api.create_action_information
1549 (p_action_context_id => p_arc_asg_action_id
1550 ,p_action_context_type => 'AAP'
1551 ,p_action_information_category => 'IN_EOY_VIA'
1552 ,p_source_id => p_run_asg_action_id
1553 ,p_action_information1 => 'Pension Fund 80CCC'
1554 ,p_action_information2 => l_pension_qa_amt
1555 ,p_action_information3 => l_pension_gross
1556 ,p_action_information_id => l_action_info_id
1557 ,p_object_version_number => l_ovn
1558 );
1559
1560 IF g_debug THEN
1561 pay_in_utils.trace('VIA 80CCC Qualifying Amt ',l_li_qa_amt);
1562 pay_in_utils.trace('VIA 80CCC Gross Amt ',l_li_gross);
1563 END IF;
1564
1565 END IF;
1566
1567 IF (l_scss_gross <> 0 OR l_scss_qa_amt <> 0)
1568 THEN
1569 pay_action_information_api.create_action_information
1570 (p_action_context_id => p_arc_asg_action_id
1571 ,p_action_context_type => 'AAP'
1572 ,p_action_information_category => 'IN_EOY_VIA'
1573 ,p_source_id => p_run_asg_action_id
1574 ,p_action_information1 => 'Senior Citizens Savings Scheme'
1575 ,p_action_information2 => l_scss_qa_amt
1576 ,p_action_information3 => l_scss_gross
1577 ,p_action_information_id => l_action_info_id
1578 ,p_object_version_number => l_ovn
1579 );
1580
1581 IF g_debug THEN
1582 pay_in_utils.trace('Senior Citizens Savings Scheme Qualifying Amt ',l_scss_qa_amt);
1583 pay_in_utils.trace('Senior Citizens Savings Scheme Gross Amt ',l_scss_gross);
1584 END IF;
1585
1586 END IF;
1587
1588 --Archival of 80CCD starts
1589 IF (l_80ccd_qa_amt <> 0 OR l_80ccd_gross <> 0)
1590 THEN
1591 pay_action_information_api.create_action_information
1592 (p_action_context_id => p_arc_asg_action_id
1593 ,p_action_context_type => 'AAP'
1594 ,p_action_information_category => 'IN_EOY_VIA'
1595 ,p_source_id => p_run_asg_action_id
1596 ,p_action_information1 => 'Govt Pension Scheme 80CCD'
1597 ,p_action_information2 => l_80ccd_qa_amt
1598 ,p_action_information3 => l_80ccd_gross
1599 ,p_action_information_id => l_action_info_id
1600 ,p_object_version_number => l_ovn
1601 );
1602 IF g_debug THEN
1603 pay_in_utils.trace('VIA Deduction under Section 80CCD Qualifying Amt ',l_80ccd_qa_amt);
1604 pay_in_utils.trace('VIA Deduction under Section 80CCD Gross Amt ',l_80ccd_gross);
1605 END IF;
1606 END IF;
1607 pay_in_utils.trace('**************************************************','********************');
1608 pay_in_utils.set_location(g_debug,l_procedure, 150);
1609
1610 END archive_via_details;
1611 --------------------------------------------------------------------------
1612 -- --
1613 -- Name : ARCHIVE_ALLOWANCES --
1614 -- Type : PROCEDURE --
1615 -- Access : Public --
1616 -- Description : This procedure archives the allowance related values--
1617 -- Parameters : --
1618 -- IN : p_arc_pay_action_id NUMBER --
1619 -- p_gre_id NUMBER --
1620 -- p_effective_end_date DATE --
1621 -- OUT : N/A --
1622 -- --
1623 -- Change History : --
1624 --------------------------------------------------------------------------
1625 -- Rev# Date Userid Description --
1626 --------------------------------------------------------------------------
1627 -- 115.0 23-MAY-2005 aaagarwa Initial Version --
1628 --------------------------------------------------------------------------
1629 PROCEDURE archive_allowances(p_run_asg_action_id IN NUMBER
1630 ,p_arc_asg_action_id IN NUMBER
1631 ,p_gre_id IN NUMBER
1632 ,pre_gre_asg_act_id IN NUMBER DEFAULT NULL
1633 ,p_flag IN BOOLEAN DEFAULT FALSE
1634 )
1635 IS
1636 CURSOR c_hra
1637 IS
1638 SELECT action_information_id
1639 ,object_version_number
1640 FROM pay_action_information
1641 WHERE action_information_category = 'IN_EOY_ALLOW'
1642 AND source_id = p_run_asg_action_id
1643 AND action_context_id = p_arc_asg_action_id
1644 AND action_information1 = 'House Rent Allowance'
1645 ORDER BY action_information_id DESC;
1646
1647 CURSOR c_comp_name
1648 IS
1649 SELECT pur.row_low_range_or_name name
1650 FROM pay_user_rows_f pur,
1651 pay_user_tables put
1652 WHERE pur.user_table_id = put.user_table_id
1653 AND put.user_table_name = 'IN_ALLOWANCES'
1654 AND put.legislation_code = 'IN'
1655 AND (pur.legislation_code = 'IN' OR pur.business_group_id = g_bg_id)
1656 AND g_start_date BETWEEN pur.effective_start_date AND pur.effective_end_date
1657 ORDER by name ASC;
1658
1659 g_balance_value_tab pay_balance_pkg.t_balance_value_tab;
1660 g_context_table pay_balance_pkg.t_context_tab;
1661 g_result_table pay_balance_pkg.t_detailed_bal_out_tab;
1662 l_action_info_id NUMBER;
1663 l_ovn NUMBER;
1664 l_defined_balance_id NUMBER;
1665 g_result_table1 pay_balance_pkg.t_detailed_bal_out_tab;
1666 g_result_table2 pay_balance_pkg.t_detailed_bal_out_tab;
1667 g_result_table3 pay_balance_pkg.t_detailed_bal_out_tab;
1668 l_value NUMBER;
1669 i NUMBER := 0;
1670 l_message VARCHAR2(255);
1671 l_procedure VARCHAR2(100);
1672
1673 BEGIN
1674
1675 l_procedure := g_package ||'archive_allowances';
1676 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1677
1678
1679 FOR c_rec IN c_comp_name
1680 LOOP
1681 i := i + 1;
1682 g_context_table(i).source_text2 := c_rec.name;
1683 END LOOP;
1684
1685 IF g_debug THEN
1686 pay_in_utils.trace('Assignment Action id ',p_run_asg_action_id);
1687 pay_in_utils.trace('Archive Asg Action id ',p_arc_asg_action_id);
1688 pay_in_utils.trace('GRE id ',p_gre_id);
1689 pay_in_utils.trace('Previous GRE Asg Action id ',pre_gre_asg_act_id);
1690 END IF;
1691
1692
1693 archive_balances(p_run_asg_action_id => p_run_asg_action_id
1694 ,pre_gre_asg_act_id => pre_gre_asg_act_id
1695 ,p_arc_asg_action_id => p_arc_asg_action_id
1696 ,p_gre_id => p_gre_id
1697 ,p_action_inf_category => 'IN_EOY_ALLOW'
1698 ,p_balance_name => 'Allowance Amount'
1699 ,p_balance_name1 => 'Allowances Standard Value'
1700 ,p_balance_name2 => 'Taxable Allowances'
1701 ,p_balance_name3 => 'Taxable Allowances for Projection'
1702 ,p_balance_dimension => '_ASG_COMP_YTD'
1703 ,p_balance_dimension1 => '_ASG_COMP_PTD'
1704 ,p_balance_dimension2 => '_ASG_COMP_YTD'
1705 ,p_balance_dimension3 => '_ASG_COMP_PTD'
1706 ,g_context_table => g_context_table
1707 ,g_result_table => g_result_table
1708 ,g_result_table1 => g_result_table1
1709 ,g_result_table2 => g_result_table2
1710 ,g_result_table3 => g_result_table3
1711 ,g_balance_value_tab => g_balance_value_tab
1712 );
1713 pay_in_utils.set_location(g_debug,l_procedure, 20);
1714
1715 OPEN c_hra;
1716 FETCH c_hra INTO l_action_info_id,l_ovn;
1717 CLOSE c_hra;
1718
1719 IF l_action_info_id IS NOT NULL
1720 THEN
1721 pay_in_utils.set_location(g_debug,l_procedure, 30);
1722 IF (pre_gre_asg_act_id IS NOT NULL)--Not the first record
1723 THEN
1724 IF p_flag -- Neither the first nor the last record. Hence diff of THRA _ASG_YTD at 2 diff act ids.
1725 THEN
1726 l_defined_balance_id := pay_in_tax_utils.get_defined_balance('Taxable House Rent Allowance','_ASG_YTD');
1727 l_value := pay_balance_pkg.get_value(p_defined_balance_id => l_defined_balance_id
1728 ,p_assignment_action_id => p_run_asg_action_id
1729 );
1730 l_value := l_value - pay_balance_pkg.get_value(p_defined_balance_id => l_defined_balance_id
1731 ,p_assignment_action_id => pre_gre_asg_act_id
1732 );
1733 ELSE -- Last Record. Hence diff of Projected and YTD value.
1734 l_defined_balance_id :=
1735 pay_in_tax_utils.get_defined_balance('Taxable House Rent Allowance for Projection','_ASG_LE_PTD');
1736
1737 l_value := pay_balance_pkg.get_value(p_defined_balance_id => l_defined_balance_id
1738 ,p_assignment_action_id => p_run_asg_action_id
1739 );
1740 l_defined_balance_id := pay_in_tax_utils.get_defined_balance('Taxable House Rent Allowance','_ASG_YTD');
1741 l_value := l_value - pay_balance_pkg.get_value(p_defined_balance_id => l_defined_balance_id
1742 ,p_assignment_action_id => pre_gre_asg_act_id
1743 );
1744 END IF;
1745 ELSIF p_flag -- First Record in a multi tan scenario, hence take the THRA_ASG_YTD
1746 THEN
1747 pay_in_utils.set_location(g_debug,l_procedure, 40);
1748 l_defined_balance_id := pay_in_tax_utils.get_defined_balance('Taxable House Rent Allowance','_ASG_YTD');
1749 l_value := pay_balance_pkg.get_value(p_defined_balance_id => l_defined_balance_id
1750 ,p_assignment_action_id => p_run_asg_action_id
1751 );
1752 ELSE -- Only a single record exists, hence take the Projetced value
1753 pay_in_utils.set_location(g_debug,l_procedure, 50);
1754 l_defined_balance_id := pay_in_tax_utils.get_defined_balance('Taxable House Rent Allowance for Projection','_ASG_PTD');
1755 l_value := pay_balance_pkg.get_value(p_defined_balance_id => l_defined_balance_id
1756 ,p_assignment_action_id => p_run_asg_action_id
1757 );
1758 END IF;
1759
1760 IF g_debug THEN
1761 pay_in_utils.trace('Balance value ',l_value);
1762 END IF;
1763
1764 pay_action_information_api.update_action_information
1765 (
1766 p_action_information_id => l_action_info_id
1767 ,p_object_version_number => l_ovn
1768 ,p_action_information5 => l_value
1769 );
1770 pay_in_utils.set_location(g_debug,l_procedure, 60);
1771 END IF;
1772 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 70);
1773
1774
1775 END archive_allowances;
1776
1777 --------------------------------------------------------------------------
1778 -- --
1779 -- Name : ARCHIVE_PERQUISISTES --
1780 -- Type : PROCEDURE --
1781 -- Access : Public --
1782 -- Description : This procedure archives the perquisite details --
1783 -- Parameters : --
1784 -- IN : p_arc_pay_action_id NUMBER --
1785 -- p_gre_id NUMBER --
1786 -- p_effective_end_date DATE --
1787 -- OUT : N/A --
1788 -- --
1789 -- Change History : --
1790 --------------------------------------------------------------------------
1791 -- Rev# Date Userid Description --
1792 --------------------------------------------------------------------------
1793 -- 115.0 23-MAY-2005 aaagarwa Initial Version --
1794 --------------------------------------------------------------------------
1795 PROCEDURE archive_perquisites(p_run_asg_action_id IN NUMBER
1796 ,p_arc_asg_action_id IN NUMBER
1797 ,p_gre_id IN NUMBER
1798 ,pre_gre_asg_act_id IN NUMBER DEFAULT NULL
1799 )
1800 IS
1801 g_balance_value_tab pay_balance_pkg.t_balance_value_tab;
1802 g_context_table pay_balance_pkg.t_context_tab;
1803 g_result_table pay_balance_pkg.t_detailed_bal_out_tab;
1804 l_action_info_id NUMBER;
1805 l_ovn NUMBER;
1806 g_result_table1 pay_balance_pkg.t_detailed_bal_out_tab;
1807 g_result_table2 pay_balance_pkg.t_detailed_bal_out_tab;
1808 g_result_table3 pay_balance_pkg.t_detailed_bal_out_tab;
1809 l_message VARCHAR2(255);
1810 l_procedure VARCHAR2(100);
1811
1812 BEGIN
1813 l_procedure := g_package ||'archive_perquisites';
1814 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1815
1816 IF g_debug THEN
1817 pay_in_utils.trace('Assignment Action id ',p_run_asg_action_id);
1818 pay_in_utils.trace('Archive Asg Action id ',p_arc_asg_action_id);
1819 pay_in_utils.trace('GRE id ',p_gre_id);
1820 pay_in_utils.trace('Previous GRE Asg Action id ',pre_gre_asg_act_id);
1821 END IF;
1822
1823 g_context_table.DELETE;
1824 g_result_table.DELETE;
1825 g_result_table1.DELETE;
1826 g_result_table2.DELETE;
1827 g_result_table3.DELETE;
1828 g_balance_value_tab.DELETE;
1829
1830
1831 g_context_table(1).source_text2 := 'Company Accommodation';
1832 g_context_table(2).source_text2 := 'Company Movable Assets';
1833 g_context_table(3).source_text2 := 'Domestic Servant';
1834 g_context_table(4).source_text2 := 'Free Education';
1835 g_context_table(5).source_text2 := 'Gas / Water / Electricity';
1836 g_context_table(6).source_text2 := 'Leave Travel Concession';
1837 g_context_table(7).source_text2 := 'Loan at Concessional Rate';
1838 g_context_table(8).source_text2 := 'Medical';
1839 g_context_table(9).source_text2 := 'Shares';
1840 g_context_table(10).source_text2 := 'Transfer of Company Assets';
1841 g_context_table(11).source_text2 := 'Employer Paid Tax';
1842 g_context_table(12).source_text2 := 'Gift Voucher';
1843 g_context_table(13).source_text2 := 'Travel / Tour / Accommodation';
1844 g_context_table(14).source_text2 := 'Free Transport';
1845 g_context_table(15).source_text2 := 'Credit Cards';
1846 g_context_table(16).source_text2 := 'Club Expenditure';
1847 g_context_table(17).source_text2 := 'Motor Car Perquisite';
1848 g_context_table(18).source_text2 := 'Lunch Perquisite';
1849
1850 pay_in_utils.set_location(g_debug,l_procedure, 20);
1851
1852 archive_balances(p_run_asg_action_id => p_run_asg_action_id
1853 ,pre_gre_asg_act_id => pre_gre_asg_act_id
1854 ,p_arc_asg_action_id => p_arc_asg_action_id
1855 ,p_gre_id => p_gre_id
1856 ,p_action_inf_category => 'IN_EOY_PERQ'
1857 ,p_balance_name => 'Taxable Perquisites'
1858 ,p_balance_name1 => 'Perquisite Employee Contribution'
1859 ,p_balance_dimension => '_ASG_COMP_YTD'
1860 ,p_balance_dimension1 => '_ASG_COMP_YTD'
1861 ,g_context_table => g_context_table
1862 ,g_result_table => g_result_table
1863 ,g_result_table1 => g_result_table1
1864 ,g_result_table2 => g_result_table2
1865 ,g_result_table3 => g_result_table3
1866 ,g_balance_value_tab => g_balance_value_tab
1867 );
1868 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
1869
1870 END archive_perquisites;
1871 --------------------------------------------------------------------------
1872 -- --
1873 -- Name : ARCHIVE_EOY_SALARY --
1874 -- Type : PROCEDURE --
1875 -- Access : Public --
1876 -- Description : This procedure archives the various salary components-
1877 -- Parameters : --
1878 -- IN : p_arc_pay_action_id NUMBER --
1879 -- p_gre_id NUMBER --
1880 -- p_effective_end_date DATE --
1881 -- OUT : N/A --
1882 -- --
1883 -- Change History : --
1884 --------------------------------------------------------------------------
1885 -- Rev# Date Userid Description --
1886 --------------------------------------------------------------------------
1887 -- 115.0 23-MAY-2005 aaagarwa Initial Version --
1888 --------------------------------------------------------------------------
1889 PROCEDURE archive_eoy_salary(p_run_asg_action_id IN NUMBER
1890 ,p_arc_asg_action_id IN NUMBER
1891 ,p_gre_id IN NUMBER)
1892 IS
1893 CURSOR c_defined_balance_id
1894 IS
1895 SELECT pdb.defined_balance_id balance_id
1896 ,pbt.balance_name balance_name
1897 FROM pay_balance_types pbt
1898 ,pay_balance_dimensions pbd
1899 ,pay_defined_balances pdb
1900 WHERE pbt.balance_name IN('Long Term Capital Gains'
1901 ,'Short Term Capital Gains'
1902 ,'Capital Gains'
1903 ,'Loss From House Property'
1904 ,'Business and Profession Gains'
1905 ,'Other Sources of Income'
1906 )
1907 AND pbd.dimension_name='_ASG_PTD'
1908 AND pbt.legislation_code = 'IN'
1909 AND pbd.legislation_code = 'IN'
1910 AND pbt.balance_type_id = pdb.balance_type_id
1911 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
1912
1913 CURSOR c_f16_sal_balances
1914 IS
1915 SELECT pdb.defined_balance_id balance_id
1916 ,pbt.balance_name balance_name
1917 FROM pay_balance_types pbt
1918 ,pay_balance_dimensions pbd
1919 ,pay_defined_balances pdb
1920 WHERE((pbt.balance_name IN('F16 Education Cess till Date'
1921 ,'F16 Sec and HE Cess till Date'
1922 ,'F16 Surcharge till Date'
1923 ,'F16 Income Tax till Date'
1924 ,'F16 Education Cess'
1925 ,'F16 Sec and HE Cess'
1926 ,'F16 Employment Tax'
1927 ,'F16 Entertainment Allowance'
1928 ,'F16 Marginal Relief'
1929 ,'F16 Profit in lieu of Salary'
1930 ,'F16 Relief under Sec 89'
1931 ,'F16 Salary Under Section 17'
1932 ,'F16 Surcharge'
1933 ,'F16 Tax on Total Income'
1934 ,'F16 Value of Perquisites'
1935 ,'F16 Gross Salary'
1936 ,'F16 Gross Salary less Allowances'
1937 ,'F16 Income Chargeable Under head Salaries'
1938 ,'F16 Gross Total Income'
1939 ,'F16 Total Income'
1940 ,'F16 Total Tax payable'
1941 ,'F16 Balance Tax'
1942 ,'F16 Tax Refundable'
1943 ,'F16 Allowances Exempt'
1944 ,'F16 Other Income'
1945 ,'F16 Deductions under Sec 16'
1946 )
1947 AND pbd.dimension_name = '_ASG_LE_PTD')
1948 OR (pbt.balance_name = 'ER Paid Tax on Non Monetary Perquisite'
1949 AND pbd.dimension_name = '_ASG_LE_YTD'))
1950 AND pbt.legislation_code = 'IN'
1951 AND pbd.legislation_code = 'IN'
1952 AND pbt.balance_type_id = pdb.balance_type_id
1953 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
1954
1955 CURSOR c_defined_bal_id
1956 IS
1957 SELECT pdb.defined_balance_id balance_id
1958 ,pbt.balance_name balance_name
1959 FROM pay_balance_types pbt
1960 ,pay_balance_dimensions pbd
1961 ,pay_defined_balances pdb
1962 WHERE pbt.balance_name IN(
1963 'Excess Interest Amount'
1964 ,'Excess PF Amount'
1965 ,'TDS on Previous Employment'
1966 ,'CESS on Previous Employment'
1967 ,'Sec and HE Cess on Previous Employment'
1968 ,'SC on Previous Employment'
1969 ,'Previous Employment Earnings'
1970 )
1971 AND pbd.dimension_name='_ASG_YTD'
1972 AND pbt.legislation_code = 'IN'
1973 AND pbd.legislation_code = 'IN'
1974 AND pbt.balance_type_id = pdb.balance_type_id
1975 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
1976
1977 g_balance_value_tab pay_balance_pkg.t_balance_value_tab;
1978 g_balance_value_tab1 pay_balance_pkg.t_balance_value_tab;
1979 g_result_table pay_balance_pkg.t_detailed_bal_out_tab;
1980
1981 i NUMBER;
1982 j NUMBER;
1983 g_bal_name_tab t_bal_name_tab;
1984 g_bal_name_tab1 t_bal_name_tab;
1985 g_context_table pay_balance_pkg.t_context_tab;
1986 l_action_info_id NUMBER;
1987 l_ovn NUMBER;
1988 l_in_tax_ded NUMBER;
1989 l_message VARCHAR2(255);
1990 l_procedure VARCHAR2(100);
1991 l_total_cess NUMBER ;
1992 l_total_cess_till_date NUMBER ;
1993 l_cess_action_info_id NUMBER;
1994 l_cess_ov_id NUMBER;
1995 l_cess_td_action_info_id NUMBER;
1996 l_cess_td_ov_id NUMBER;
1997
1998 BEGIN
1999 l_procedure := g_package ||'archive_eoy_salary';
2000 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2001
2002 IF g_debug THEN
2003 pay_in_utils.trace('Assignment Action id ',p_run_asg_action_id);
2004 pay_in_utils.trace('Archive Asg Action id ',p_arc_asg_action_id);
2005 pay_in_utils.trace('GRE id ',p_gre_id);
2006 END IF;
2007
2008 i := 1;
2009 g_bal_name_tab.DELETE;
2010 l_in_tax_ded := 0;
2011 l_total_cess:=0;
2012 l_total_cess_till_date:=0;
2013
2014 FOR c_rec IN c_defined_balance_id
2015 LOOP
2016 g_balance_value_tab(i).defined_balance_id := c_rec.balance_id;
2017 g_bal_name_tab(i).balance_name := c_rec.balance_name;
2018 i := i + 1;
2019 END LOOP;
2020
2021 pay_balance_pkg.get_value(p_run_asg_action_id,g_balance_value_tab);
2022
2023 pay_in_utils.set_location(g_debug,l_procedure, 20);
2024
2025
2026
2027 pay_in_utils.trace('**************************************************','********************');
2028 FOR i IN 1..g_balance_value_tab.COUNT
2029 LOOP
2030 IF (g_balance_value_tab(i).balance_value <> 0)
2031 THEN
2032 pay_action_information_api.create_action_information
2033 (p_action_context_id => p_arc_asg_action_id
2034 ,p_action_context_type => 'AAP'
2035 ,p_action_information_category => 'IN_EOY_ASG_SAL'
2036 ,p_source_id => p_run_asg_action_id
2037 ,p_action_information1 => g_bal_name_tab(i).balance_name
2038 ,p_action_information2 => g_balance_value_tab(i).balance_value
2039 ,p_action_information_id => l_action_info_id
2040 ,p_object_version_number => l_ovn
2041 );
2042
2043 IF g_debug THEN
2044 pay_in_utils.trace('SALARY Balance Name ',g_bal_name_tab(i).balance_name);
2045 pay_in_utils.trace('SALARY Balance Value ',g_balance_value_tab(i).balance_value);
2046 END IF;
2047
2048 END IF;
2049 END LOOP;
2050
2051 pay_in_utils.set_location(g_debug,l_procedure, 20);
2052
2053 --Archiving balances having YTD Dimensions
2054 i := 1;
2055 FOR c_rec IN c_defined_bal_id
2056 LOOP
2057 g_balance_value_tab1(i).defined_balance_id := c_rec.balance_id;
2058 g_bal_name_tab1(i).balance_name := c_rec.balance_name;
2059 i := i + 1;
2060 END LOOP;
2061
2062 pay_balance_pkg.get_value(p_run_asg_action_id,g_balance_value_tab1);
2063
2064 pay_in_utils.set_location(g_debug,l_procedure, 30);
2065
2066 FOR i IN 1..g_balance_value_tab1.COUNT
2067 LOOP
2068
2069
2070
2071 IF (g_balance_value_tab1(i).balance_value <> 0)
2072 THEN
2073 pay_action_information_api.create_action_information
2074 (p_action_context_id => p_arc_asg_action_id
2075 ,p_action_context_type => 'AAP'
2076 ,p_action_information_category => 'IN_EOY_ASG_SAL'
2077 ,p_source_id => p_run_asg_action_id
2078 ,p_action_information1 => g_bal_name_tab1(i).balance_name
2079 ,p_action_information2 => g_balance_value_tab1(i).balance_value
2080 ,p_action_information_id => l_action_info_id
2081 ,p_object_version_number => l_ovn
2082 );
2083 IF g_debug THEN
2084 pay_in_utils.trace('SALARY Balance Name ',g_bal_name_tab1(i).balance_name);
2085 pay_in_utils.trace('SALARY Balance Value ',g_balance_value_tab1(i).balance_value);
2086 END IF;
2087
2088 END IF;
2089 END LOOP;
2090
2091
2092 pay_in_utils.set_location(g_debug,l_procedure, 40);
2093 --Archiving balances having LE_PTD Dimensions
2094 i := 1;
2095 g_bal_name_tab1.DELETE;
2096 g_balance_value_tab1.DELETE;
2097 g_context_table(1).tax_unit_id := p_gre_id;
2098
2099
2100
2101 FOR c_rec IN c_f16_sal_balances
2102 LOOP
2103 g_balance_value_tab1(i).defined_balance_id := c_rec.balance_id;
2104 g_bal_name_tab1(i).balance_name := c_rec.balance_name;
2105 i := i + 1;
2106
2107 END LOOP;
2108
2109 pay_in_utils.set_location(g_debug,l_procedure, 50);
2110
2111 pay_balance_pkg.get_value(p_assignment_action_id => p_run_asg_action_id
2112 ,p_defined_balance_lst => g_balance_value_tab1
2113 ,p_context_lst => g_context_table
2114 ,p_output_table => g_result_table
2115 );
2116
2117
2118 FOR i IN 1..g_bal_name_tab1.COUNT
2119 LOOP
2120 IF (g_bal_name_tab1(i).balance_name = 'F16 Education Cess till Date' OR
2121 g_bal_name_tab1(i).balance_name = 'F16 Sec and HE Cess till Date' OR
2122 g_bal_name_tab1(i).balance_name = 'F16 Surcharge till Date' OR
2123 g_bal_name_tab1(i).balance_name = 'F16 Income Tax till Date' ) THEN
2124 l_in_tax_ded := l_in_tax_ded + g_result_table(i).balance_value;
2125 END IF;
2126 IF (g_bal_name_tab1(i).balance_name = 'F16 Education Cess till Date' OR
2127 g_bal_name_tab1(i).balance_name = 'F16 Sec and HE Cess till Date' ) THEN
2128 l_total_cess_till_date:=l_total_cess_till_date + g_result_table(i).balance_value;
2129 END IF ;
2130 IF (g_bal_name_tab1(i).balance_name = 'F16 Education Cess' OR
2131 g_bal_name_tab1(i).balance_name = 'F16 Sec and HE Cess' ) THEN
2132 l_total_cess := l_total_cess + g_result_table(i).balance_value;
2133 END IF ;
2134
2135 END LOOP;
2136 pay_in_utils.set_location(g_debug,l_procedure, 60);
2137
2138 g_bal_name_tab1(g_result_table.COUNT + 1).balance_name := 'Income Tax Deduction';
2139 g_result_table(g_result_table.COUNT + 1).balance_value := l_in_tax_ded;
2140
2141 FOR i IN 1..g_bal_name_tab1.COUNT
2142 LOOP
2143
2144 IF g_result_table(i).balance_value <> 0
2145 THEN
2146 pay_action_information_api.create_action_information
2147 (p_action_context_id => p_arc_asg_action_id
2148 ,p_action_context_type => 'AAP'
2149 ,p_action_information_category => 'IN_EOY_ASG_SAL'
2150 ,p_source_id => p_run_asg_action_id
2151 ,p_action_information1 => g_bal_name_tab1(i).balance_name
2152 ,p_action_information2 => g_result_table(i).balance_value
2153 ,p_action_information_id => l_action_info_id
2154 ,p_object_version_number => l_ovn
2155 );
2156 IF g_bal_name_tab1(i).balance_name='F16 Education Cess' THEN
2157 l_cess_action_info_id:=l_action_info_id;
2158 l_cess_ov_id:=l_ovn;
2159 END IF ;
2160 IF g_bal_name_tab1(i).balance_name='F16 Education Cess till Date' THEN
2161 l_cess_td_action_info_id:=l_action_info_id;
2162 l_cess_td_ov_id:=l_ovn;
2163 END IF ;
2164
2165 IF g_debug THEN
2166 pay_in_utils.trace('SALARY Balance Name ',g_bal_name_tab1(i).balance_name);
2167 pay_in_utils.trace('SALARY Balance Value ',g_result_table(i).balance_value);
2168 END IF;
2169
2170 END IF;
2171 END LOOP;
2172 IF l_total_cess <> 0 THEN
2173 pay_action_information_api.update_action_information
2174 (p_action_information_id => l_cess_action_info_id
2175 ,p_object_version_number => l_cess_ov_id
2176 ,p_action_information1 => 'F16 Education Cess'
2177 ,p_action_information2 => l_total_cess
2178 );
2179 END IF ;
2180 IF l_total_cess_till_date <> 0 THEN
2181 pay_action_information_api.update_action_information
2182 (p_action_information_id => l_cess_td_action_info_id
2183 ,p_object_version_number => l_cess_td_ov_id
2184 ,p_action_information1 => 'F16 Education Cess till Date'
2185 ,p_action_information2 => l_total_cess_till_date
2186 );
2187 END IF ;
2188 l_cess_action_info_id:=0;
2189 l_cess_ov_id:=0;
2190 l_cess_td_action_info_id:=0;
2191 l_cess_td_ov_id:=0;
2192 pay_in_utils.trace('**************************************************','********************');
2193 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 70);
2194
2195
2196 END archive_eoy_salary;
2197 --------------------------------------------------------------------------
2198 -- --
2199 -- Name : ARCHIVE_OTHER_BALANCES --
2200 -- Type : PROCEDURE --
2201 -- Access : Public --
2202 -- Description : This is called to archive the fields that were not --
2203 -- covered under IN_EOY_ALLOW and IN_EOY_PERQ --
2204 -- Parameters : --
2205 -- IN : p_arc_pay_action_id NUMBER --
2206 -- p_gre_id NUMBER --
2207 -- p_effective_end_date DATE --
2208 -- OUT : N/A --
2209 -- --
2210 -- Change History : --
2211 --------------------------------------------------------------------------
2212 -- Rev# Date Userid Description --
2213 --------------------------------------------------------------------------
2214 -- 115.0 23-MAY-2005 aaagarwa Initial Version --
2215 --------------------------------------------------------------------------
2216 PROCEDURE archive_other_balances(p_run_asg_action_id IN NUMBER
2217 ,p_arc_asg_action_id IN NUMBER
2218 ,pre_gre_asg_act_id IN NUMBER DEFAULT NULL
2219 ,p_gre_id IN NUMBER
2220 ,p_start_date IN DATE
2221 ,p_end_date IN DATE
2222 )
2223 IS
2224
2225 CURSOR c_defined_bal_id
2226 IS
2227 SELECT pdb.defined_balance_id balance_id
2228 ,pbt.balance_name balance_name
2229 FROM pay_balance_types pbt
2230 ,pay_balance_dimensions pbd
2231 ,pay_defined_balances pdb
2232 WHERE pbt.balance_name IN('Taxable Allowances'
2233 ,'Taxable Perquisites'
2234 ,'Monthly Furniture Cost'
2235 ,'Furniture Perquisite'
2236 ,'Cost and Rent of Furniture'
2237 ,'Perquisite Employee Contribution'
2238 ,'ER Paid Tax on Monetary Perquisite'
2239 )
2240 AND pbd.dimension_name='_ASG_YTD'
2241 AND pbt.legislation_code = 'IN'
2242 AND pbd.legislation_code = 'IN'
2243 AND pbt.balance_type_id = pdb.balance_type_id
2244 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
2245
2246
2247
2248
2249 g_balance_value_tab pay_balance_pkg.t_balance_value_tab;
2250 l_balance_value_tab1 pay_balance_pkg.t_balance_value_tab;
2251 l_balance_value_tab2 pay_balance_pkg.t_balance_value_tab;
2252 g_context_table pay_balance_pkg.t_context_tab;
2253 g_result_table pay_balance_pkg.t_detailed_bal_out_tab;
2254 g_bal_name_tab t_bal_name_tab;
2255 i NUMBER;
2256 l_context VARCHAR2(50);
2257 l_defined_balance_id NUMBER;
2258 l_value NUMBER;
2259 l_action_info_id NUMBER;
2260 l_ovn NUMBER;
2261 l_tax_on_direct_pymt NUMBER :=0;
2262 l_message VARCHAR2(255);
2263 l_procedure VARCHAR2(100);
2264
2265 BEGIN
2266
2267 l_procedure := g_package ||'archive_other_balances';
2268 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2269
2270 IF g_debug THEN
2271 pay_in_utils.trace('**************************************************','********************');
2272 pay_in_utils.trace('Assignment Action id ',p_run_asg_action_id);
2273 pay_in_utils.trace('Archive Asg Action id ',p_arc_asg_action_id);
2274 pay_in_utils.trace('GRE id ',p_gre_id);
2275 pay_in_utils.trace('Previous GRE Asg Action id ',pre_gre_asg_act_id);
2276 pay_in_utils.trace('Start Date ',p_start_date);
2277 pay_in_utils.trace('End Date ',p_end_date);
2278 pay_in_utils.trace('**************************************************','********************');
2279 END IF;
2280
2281 --Archiving the various Perquisite and Allowance records
2282
2283 i := 1;
2284 g_context_table.DELETE;
2285 g_bal_name_tab.DELETE;
2286 g_balance_value_tab.DELETE;
2287
2288 FOR c_rec IN c_defined_bal_id
2289 LOOP
2290 g_balance_value_tab(i).defined_balance_id := c_rec.balance_id;
2291 g_bal_name_tab(i).balance_name := c_rec.balance_name;
2292 i := i + 1;
2293 END LOOP;
2294
2295 pay_in_utils.set_location(g_debug,l_procedure, 20);
2296
2297 l_balance_value_tab1 := g_balance_value_tab;
2298 l_balance_value_tab2 := g_balance_value_tab;
2299
2300 pay_balance_pkg.get_value(p_run_asg_action_id,l_balance_value_tab1);
2301
2302 IF pre_gre_asg_act_id IS NOT NULL
2303 THEN
2304 pay_balance_pkg.get_value(pre_gre_asg_act_id,l_balance_value_tab2);
2305 END IF;
2306
2307 pay_in_utils.set_location(g_debug,l_procedure, 30);
2308 FOR i IN 1..g_balance_value_tab.COUNT
2309 LOOP
2310
2311 IF (g_bal_name_tab(i).balance_name <> 'Monthly Furniture Cost')
2312 THEN
2313 g_balance_value_tab(i).balance_value := NVL(l_balance_value_tab1(i).balance_value,0)
2314 - NVL(l_balance_value_tab2(i).balance_value,0);
2315 ELSE
2316 g_balance_value_tab(i).balance_value := NVL(l_balance_value_tab1(i).balance_value,0);
2317 END IF;
2318
2319 IF (g_balance_value_tab(i).balance_value <> 0)
2320 THEN
2321 IF (g_bal_name_tab(i).balance_name = 'Taxable Allowances')
2322 THEN
2323 l_context := 'IN_EOY_ALLOW';
2324 ELSE
2325 l_context := 'IN_EOY_PERQ';
2326 END IF;
2327 pay_in_utils.set_location(g_debug,l_procedure, 40);
2328
2329 pay_action_information_api.create_action_information
2330 (p_action_context_id => p_arc_asg_action_id
2331 ,p_action_context_type => 'AAP'
2332 ,p_action_information_category => l_context
2333 ,p_source_id => p_run_asg_action_id
2334 ,p_action_information1 => g_bal_name_tab(i).balance_name
2335 ,p_action_information2 => g_balance_value_tab(i).balance_value
2336 ,p_action_information_id => l_action_info_id
2337 ,p_object_version_number => l_ovn
2338 );
2339
2340 IF g_debug THEN
2341 pay_in_utils.trace('**************************************************','********************');
2342 pay_in_utils.trace('OTHER Balance Name ', g_bal_name_tab(i).balance_name);
2343 pay_in_utils.trace('OTHER Balance Value ',g_balance_value_tab(i).balance_value);
2344 pay_in_utils.trace('**************************************************','********************');
2345 END IF;
2346
2347 END IF;
2348 END LOOP;
2349 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
2350
2351
2352 END archive_other_balances;
2353
2354 --------------------------------------------------------------------------
2355 -- --
2356 -- Name : ARCHIVE_ORG_DATA --
2357 -- Type : PROCEDURE --
2358 -- Access : Public --
2359 -- Description : Procedure to archive the Organizational details at --
2360 -- Payroll level --
2361 -- Parameters : --
2362 -- IN : p_arc_pay_action_id NUMBER --
2363 -- p_gre_id NUMBER --
2364 -- p_effective_end_date DATE --
2365 -- OUT : N/A --
2366 -- --
2367 -- Change History : --
2368 --------------------------------------------------------------------------
2369 -- Rev# Date Userid Description --
2370 --------------------------------------------------------------------------
2371 -- 115.0 23-MAY-2005 aaagarwa Initial Version --
2372 -- 115.1 25-SEP-2007 rsaharay Modified c_pos,c_rep_address --
2373 --------------------------------------------------------------------------
2374 PROCEDURE archive_org_data(p_arc_pay_action_id IN NUMBER
2375 ,p_gre_id IN NUMBER
2376 ,p_effective_end_date IN DATE
2377 )
2378 IS
2379
2380 CURSOR c_org_inc_tax_df_details
2381 IS
2382 SELECT hoi.org_information1 tan
2383 ,hoi.org_information2 ward
2384 ,hoi.org_information4 reg_org_id
2385 ,hoi.org_information5 tan_ack_no
2386 ,hou.name org_name
2387 ,hou.location_id location_id
2388 FROM hr_organization_information hoi
2389 ,hr_organization_units hou
2390 WHERE hoi.organization_id = p_gre_id
2391 AND hoi.org_information_context = 'PER_IN_INCOME_TAX_DF'
2392 AND hou.organization_id = hoi.organization_id
2393 AND hou.business_group_id = g_bg_id
2394 AND p_effective_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
2395
2396 CURSOR c_reg_org_details(p_reg_org_id NUMBER)
2397 IS
2398 SELECT hoi.org_information3 pan
2399 ,hoi.org_information4 legal_name
2400 FROM hr_organization_information hoi
2401 ,hr_organization_units hou
2402 WHERE hoi.organization_id = p_reg_org_id
2403 AND hoi.org_information_context = 'PER_IN_COMPANY_DF'
2404 AND hou.organization_id = hoi.organization_id
2405 AND hou.business_group_id = g_bg_id
2406 AND p_effective_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
2407
2408 CURSOR c_pos(p_person_id NUMBER)
2409 IS
2410 SELECT nvl(pos.name,job.name) name
2411 FROM per_all_positions pos
2412 ,per_assignments_f asg
2413 ,per_jobs job
2414 WHERE asg.position_id=pos.position_id(+)
2415 AND asg.job_id=job.job_id(+)
2416 AND asg.person_id = p_person_id
2417 AND asg.primary_flag = 'Y'
2418 AND asg.business_group_id = g_bg_id
2419 AND p_effective_end_date BETWEEN pos.date_effective(+) AND NVL(pos.date_end(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
2420 AND p_effective_end_date BETWEEN job.date_from(+) AND NVL(job.date_to(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
2421 AND p_effective_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
2422
2423
2424 CURSOR c_father_name(p_person_id NUMBER)
2425 IS
2426 SELECT DECODE(pea.title,NULL,hr_in_utility.per_in_full_name(pea.first_name,pea.middle_names,pea.last_name,pea.title)
2427 ,SUBSTR(hr_in_utility.per_in_full_name(pea.first_name,pea.middle_names,pea.last_name,pea.title)
2428 ,INSTR(hr_in_utility.per_in_full_name(pea.first_name,pea.middle_names,pea.last_name,pea.title),' ',1)+1)) father
2429 ,pea.title title
2430 FROM per_all_people_f pep
2431 ,per_all_people_f pea
2432 ,per_contact_relationships con
2433 WHERE pep.person_id = p_person_id
2434 AND pea.person_id =con.contact_person_id
2435 AND pep.business_group_id = g_bg_id
2436 AND pea.business_group_id = g_bg_id
2437 AND con.person_id=pep.person_id
2438 AND con.contact_type='JP_FT'
2439 AND p_effective_end_date BETWEEN pep.effective_start_date AND pep.effective_end_date
2440 AND p_effective_end_date BETWEEN pea.effective_start_date AND pea.effective_end_date;
2441
2442 CURSOR c_representative_id
2443 IS
2444 SELECT hoi.org_information1 person_id
2445 ,DECODE(pep.title,NULL,hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title)
2446 ,SUBSTR(hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title)
2447 ,INSTR(hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title),' ',1)+1)) rep_name
2448 ,pep.title title
2449 FROM hr_organization_information hoi
2450 ,hr_organization_units hou
2451 ,per_all_people_f pep
2452 WHERE hoi.org_information_context = 'PER_IN_INCOME_TAX_REP_DF'
2453 AND hoi.organization_id = p_gre_id
2454 AND hou.organization_id = hoi.organization_id
2455 AND hou.business_group_id = g_bg_id
2456 AND pep.person_id = hoi.org_information1
2457 AND pep.business_group_id = hou.business_group_id
2458 AND p_effective_end_date BETWEEN pep.effective_start_date AND pep.effective_end_date
2459 AND p_effective_end_date BETWEEN fnd_date.canonical_to_date(hoi.org_information2)
2460 AND NVL(fnd_date.canonical_to_date(hoi.org_information3),TO_DATE('31-12-4712','DD-MM-YYYY'))
2461 AND p_effective_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
2462
2463 CURSOR c_rep_address(p_person_id NUMBER)
2464 IS
2465 SELECT hou.location_id rep_location
2466 FROM per_all_assignments_f asg
2467 ,hr_organization_units hou
2468 WHERE asg.person_id = p_person_id
2469 AND asg.primary_flag = 'Y'
2470 AND asg.business_group_id = g_bg_id
2471 AND hou.organization_id = asg.organization_id
2472 AND hou.business_group_id = asg.business_group_id
2473 AND p_effective_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date
2474 AND p_effective_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
2475
2476 CURSOR c_rep_phone(p_person_id NUMBER)
2477 IS
2478 SELECT phone_number rep_phone_no
2479 ,phone_type
2480 FROM per_phones
2481 WHERE parent_id = p_person_id
2482 AND phone_type = DECODE(phone_type,'H1','H1','M')
2483 AND p_effective_end_date BETWEEN date_from AND NVL(date_to,TO_DATE('31-12-4712','DD-MM-YYYY'))
2484 ORDER BY phone_type ASC;
2485
2486 CURSOR c_rep_work_fax(p_person_id NUMBER)
2487 IS
2488 SELECT phone_number work_fax
2489 FROM per_phones
2490 WHERE parent_id = p_person_id
2491 AND phone_type = 'WF'
2492 AND p_effective_end_date BETWEEN date_from AND NVL(date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
2493
2494 l_tan hr_organization_information.org_information1%TYPE;
2495 l_ward hr_organization_information.org_information2%TYPE;
2496 l_reg_org_id hr_organization_information.org_information4%TYPE;
2497 l_tan_ack_no hr_organization_information.org_information5%TYPE;
2498 l_org_name hr_organization_units.name%TYPE;
2499 l_location_id hr_organization_units.location_id%TYPE;
2500 l_pan hr_organization_information.org_information3%TYPE;
2501 l_legal_name hr_organization_information.org_information4%TYPE;
2502 l_rep_person_id per_all_people_f.person_id%TYPE;
2503 l_rep_name per_all_people_f.full_name%TYPE;
2504 l_position per_all_positions.name%TYPE;
2505 l_rep_father per_all_people_f.full_name%TYPE;
2506 l_rep_location hr_organization_units.location_id%TYPE;
2507 l_rep_phone_no per_phones.phone_number%TYPE;
2508 l_phone_type per_phones.phone_type%TYPE;
2509 l_rep_father_title per_all_people_f.title%TYPE;
2510 l_rep_title per_all_people_f.title%TYPE;
2511 l_rep_work_fax per_phones.phone_number%TYPE;
2512 l_action_info_id NUMBER;
2513 l_ovn NUMBER;
2514 l_message VARCHAR2(255);
2515 l_procedure VARCHAR2(100);
2516
2517
2518
2519 BEGIN
2520 l_procedure := g_package ||'archive_org_data';
2521 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2522
2523 IF g_debug THEN
2524 pay_in_utils.trace('Payroll Action id ',p_arc_pay_action_id);
2525 pay_in_utils.trace('GRE id ',p_gre_id);
2526 pay_in_utils.trace('End Date ',p_effective_end_date);
2527 END IF;
2528
2529
2530 OPEN c_org_inc_tax_df_details;
2531 FETCH c_org_inc_tax_df_details INTO l_tan,l_ward,l_reg_org_id,l_tan_ack_no,l_org_name,l_location_id;
2532 CLOSE c_org_inc_tax_df_details;
2533
2534 pay_in_utils.set_location(g_debug,l_procedure, 20);
2535
2536 OPEN c_reg_org_details(l_reg_org_id);
2537 FETCH c_reg_org_details INTO l_pan,l_legal_name;
2538 CLOSE c_reg_org_details;
2539
2540 pay_in_utils.set_location(g_debug,l_procedure, 30);
2541 OPEN c_representative_id;
2542 FETCH c_representative_id INTO l_rep_person_id,l_rep_name,l_rep_title;
2543 CLOSE c_representative_id;
2544
2545 pay_in_utils.set_location(g_debug,l_procedure, 40);
2546 OPEN c_pos(l_rep_person_id);
2547 FETCH c_pos INTO l_position;
2548 CLOSE c_pos;
2549
2550 pay_in_utils.set_location(g_debug,l_procedure, 50);
2551 OPEN c_father_name(l_rep_person_id);
2552 FETCH c_father_name INTO l_rep_father,l_rep_father_title;
2553 CLOSE c_father_name;
2554
2555 pay_in_utils.set_location(g_debug,l_procedure, 60);
2556 OPEN c_rep_address(l_rep_person_id);
2557 FETCH c_rep_address INTO l_rep_location;
2558 CLOSE c_rep_address;
2559
2560 pay_in_utils.set_location(g_debug,l_procedure, 70);
2561 OPEN c_rep_phone(l_rep_person_id);
2562 FETCH c_rep_phone INTO l_rep_phone_no,l_phone_type;
2563 CLOSE c_rep_phone;
2564
2565 pay_in_utils.set_location(g_debug,l_procedure, 80);
2566 OPEN c_rep_work_fax(l_rep_person_id);
2567 FETCH c_rep_work_fax INTO l_rep_work_fax;
2568 CLOSE c_rep_work_fax;
2569
2570 pay_in_utils.set_location(g_debug,l_procedure, 90);
2571 pay_action_information_api.create_action_information
2572 (p_action_context_id => p_arc_pay_action_id
2573 ,p_action_context_type => 'PA'
2574 ,p_action_information_category => 'IN_EOY_ORG'
2575 ,p_action_information1 => p_gre_id
2576 ,p_action_information2 => l_pan
2577 ,p_action_information3 => g_year
2578 ,p_action_information4 => l_tan
2579 ,p_action_information5 => l_tan_ack_no
2580 ,p_action_information6 => l_org_name
2581 ,p_action_information7 => l_location_id
2582 ,p_action_information8 => l_legal_name
2583 ,p_action_information9 => l_ward
2584 ,p_action_information10 => l_rep_person_id
2585 ,p_action_information11 => l_rep_name
2586 ,p_action_information12 => l_rep_title
2587 ,p_action_information13 => l_position
2588 ,p_action_information14 => l_rep_father
2589 ,p_action_information15 => l_rep_father_title
2590 ,p_action_information16 => l_rep_location
2591 ,p_action_information17 => l_rep_phone_no
2592 ,p_action_information18 => l_rep_work_fax
2593 ,p_action_information_id => l_action_info_id
2594 ,p_object_version_number => l_ovn
2595 );
2596 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 100);
2597
2598
2599 END archive_org_data;
2600 --------------------------------------------------------------------------
2601 -- --
2602 -- Name : ARCHIVE_CODE --
2603 -- Type : PROCEDURE --
2604 -- Access : Public --
2605 -- Description : Procedure to call the internal procedures to --
2606 -- actually archive the data. --
2607 -- Parameters : --
2608 -- IN : p_assignment_action_id NUMBER --
2609 -- p_effective_date DATE --
2610 -- --
2611 -- OUT : N/A --
2612 -- --
2613 -- Change History : --
2614 --------------------------------------------------------------------------
2615 -- Rev# Date Userid Description --
2616 --------------------------------------------------------------------------
2617 -- 115.0 23-MAY-2005 aaagarwa Initial Version --
2618 -- 115.1 05-APR-2006 rpalli Bug#5135223:Modified a parameter --
2619 -- l_run_date_earned passed through --
2620 -- archive_person_data and --
2621 -- archive_via_details --
2622 --
2623 PROCEDURE archive_code (
2624 p_assignment_action_id IN NUMBER
2625 ,p_effective_date IN DATE
2626 )
2627 IS
2628 --This cursor determines the GRE/Legal Entity record
2629
2630 CURSOR get_assignment_pact_id
2631 IS
2632 SELECT paa.assignment_id
2633 ,paa.payroll_action_id
2634 FROM pay_assignment_actions paa
2635 ,per_all_assignments_f paf
2636 WHERE paa.assignment_action_id = p_assignment_action_id
2637 AND paa.assignment_id = paf.assignment_id
2638 AND ROWNUM =1;
2639
2640 CURSOR c_gre_records
2641 IS
2642 SELECT GREATEST(asg.effective_start_date,g_start_date) start_date
2643 ,LEAST(asg.effective_end_date,g_end_date) end_date
2644 ,scl.segment1
2645 FROM per_all_assignments_f asg
2646 ,hr_soft_coding_keyflex scl
2647 ,pay_assignment_actions paa
2648 WHERE asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
2649 AND paa.assignment_action_id = p_assignment_action_id
2650 AND asg.assignment_id = paa.assignment_id
2651 AND scl.segment1 LIKE TO_CHAR(g_gre_id)
2652 AND ( asg.effective_start_date BETWEEN g_start_date AND g_end_date
2653 OR g_start_date BETWEEN asg.effective_start_date AND g_end_date
2654 )
2655 AND GREATEST(asg.effective_start_date,g_start_date) <= LEAST(asg.effective_end_date,g_end_date)
2656 ORDER BY 1 asc;
2657
2658 CURSOR get_eoy_archival_details(p_start_date DATE
2659 ,p_end_date DATE
2660 ,p_tax_unit_id NUMBER
2661 ,p_assignment_id NUMBER
2662 )
2663 IS
2664 SELECT TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) run_asg_action_id
2665 FROM pay_assignment_actions paa
2666 ,pay_payroll_actions ppa
2667 ,per_assignments_f paf
2668 WHERE paf.assignment_id = paa.assignment_id
2669 AND paf.assignment_id = p_assignment_id
2670 AND paa.tax_unit_id = p_tax_unit_id
2671 AND paa.payroll_action_id = ppa.payroll_action_id
2672 AND ppa.action_type IN('R','Q','I','B')
2673 AND ppa.action_status ='C'
2674 AND ppa.effective_date between p_start_date and p_end_date
2675 AND paa.source_action_id IS NULL
2676 AND ppa.payroll_id = paf.payroll_id
2677 AND (1 = DECODE(ppa.action_type,'I',1,0)
2678 OR EXISTS (SELECT ''
2679 FROM pay_action_interlocks intk,
2680 pay_assignment_actions paa1,
2681 pay_payroll_actions ppa1
2682 WHERE intk.locked_action_id = paa.assignment_Action_id
2683 AND intk.locking_action_id = paa1.assignment_action_id
2684 AND paa1.payroll_action_id =ppa1.payroll_action_id
2685 AND paa1.assignment_id = p_assignment_id
2686 AND ppa1.action_type in('P','U')
2687 AND ppa.action_type in('R','Q','B')
2688 AND ppa1.action_status ='C'
2689 AND ppa1.effective_date BETWEEN p_start_date and p_end_date
2690 AND ROWNUM =1 ));
2691
2692 CURSOR c_get_date_earned(l_run_assact NUMBER)
2693 IS
2694 SELECT ppa.date_earned run_date
2695 FROM pay_payroll_actions ppa,
2696 pay_assignment_actions paa
2697 WHERE paa.payroll_action_id = ppa.payroll_action_id
2698 AND paa.assignment_action_id = l_run_assact;
2699
2700 CURSOR get_prepayment_date(l_run_assact NUMBER)
2701 IS
2702 SELECT ppa.effective_date
2703 FROM pay_payroll_actions ppa,
2704 pay_assignment_actions paa,
2705 pay_action_interlocks intk
2706 WHERE intk.locked_action_id = l_run_assact
2707 AND intk.locking_action_id =paa.assignment_action_id
2708 AND ppa.payroll_action_id = paa.payroll_action_id
2709 AND ppa.action_type IN('P','U');
2710
2711 CURSOR c_pay_action_level_check(p_payroll_action_id NUMBER
2712 ,p_gre_id NUMBER)
2713 IS
2714 SELECT 1
2715 FROM pay_action_information
2716 WHERE action_information_category = 'IN_EOY_ORG'
2717 AND action_context_type = 'PA'
2718 AND action_context_id = p_payroll_action_id
2719 AND action_information1 = p_gre_id;
2720
2721 --This cursor determines termination date of an assignment.
2722 CURSOR c_termination_check(p_assignment_id NUMBER)
2723 IS
2724 SELECT NVL(pos.actual_termination_date,(fnd_date.string_to_date('31-12-4712','DD-MM-YYYY')))
2725 FROM per_all_assignments_f asg
2726 ,per_periods_of_service pos
2727 WHERE asg.person_id = pos.person_id
2728 AND asg.assignment_id = p_assignment_id
2729 AND asg.business_group_id = pos.business_group_id
2730 AND asg.business_group_id = g_bg_id
2731 AND NVL(pos.actual_termination_date,(to_date('31-12-4712','DD-MM-YYYY')))
2732 BETWEEN asg.effective_start_date AND asg.effective_end_date
2733 ORDER BY 1 desc;
2734
2735 l_procedure VARCHAR2(100);
2736
2737 l_assignment_id NUMBER;
2738 l_run_asg_action_id NUMBER;
2739 l_run_date_earned DATE;
2740 l_pre_effective_date DATE;
2741 l_arc_pay_action_id NUMBER;
2742 l_check NUMBER;
2743 l_end_date DATE;
2744 l_previous_gre_asg_action_id NUMBER;
2745 l_end NUMBER;
2746 l_start NUMBER;
2747 l_flag BOOLEAN;
2748 l_record_count NUMBER;
2749 l_message VARCHAR2(255);
2750
2751 BEGIN
2752 --
2753
2754 g_debug := hr_utility.debug_enabled;
2755 l_procedure := g_package || 'archive_code';
2756 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2757 --
2758 g_count := 1;
2759 g_asg_tab.DELETE;
2760
2761
2762
2763 OPEN get_assignment_pact_id;
2764 FETCH get_assignment_pact_id INTO l_assignment_id ,l_arc_pay_action_id;
2765 CLOSE get_assignment_pact_id;
2766 pay_in_utils.set_location(g_debug,l_procedure, 20);
2767 --
2768 FOR c_rec IN c_gre_records
2769 LOOP
2770
2771 g_asg_tab(g_count).gre_id := c_rec.segment1;
2772 g_asg_tab(g_count).start_date := c_rec.start_date;
2773 g_asg_tab(g_count).end_date := c_rec.end_date;
2774
2775 IF(
2776 (g_count <>1)
2777 AND
2778 (g_asg_tab(g_count-1).gre_id = g_asg_tab(g_count).gre_id)
2779 AND
2780 (g_asg_tab(g_count-1).end_date + 1 = c_rec.start_date) -- Added for 4964645
2781 )
2782 THEN
2783 g_asg_tab(g_count-1).end_date := g_asg_tab(g_count).end_date;
2784 g_asg_tab(g_count).gre_id := NULL;
2785 g_asg_tab(g_count).start_date := NULL;
2786 g_asg_tab(g_count).end_date := NULL;
2787
2788 g_count := g_count -1;
2789 END IF;
2790
2791 IF g_debug THEN
2792 pay_in_utils.trace('GRE Count No ',g_count);
2793 pay_in_utils.trace('GRE id ',g_asg_tab(g_count).gre_id);
2794 pay_in_utils.trace('Start Date ',g_asg_tab(g_count).start_date);
2795 pay_in_utils.trace('End Date ',g_asg_tab(g_count).end_date );
2796 END IF;
2797
2798 g_count := g_count + 1;
2799 END LOOP;
2800 l_record_count := g_count-1;
2801
2802 pay_in_utils.set_location(g_debug,l_procedure, 30);
2803
2804 IF (g_employee_type = 'ALL')
2805 THEN
2806 l_end := g_count-1;
2807 l_start := 1;
2808 ELSIF (g_employee_type = 'CURRENT')
2809 THEN
2810 IF (g_asg_tab(g_count-1).end_date = g_end_date)
2811 THEN
2812 l_end := g_count-1;
2813 l_start := g_count-1;
2814 ELSE
2815 l_end := 0;
2816 l_start := 1;
2817 END IF;
2818 ELSE
2819 IF (g_asg_tab(g_count-1).end_date = g_end_date)
2820 THEN
2821 IF (g_count - 1)>1
2822 THEN
2823 l_end := g_count-2;
2824 l_start := 1;
2825 ELSE
2826 l_end := 1;
2827 l_start := 1;
2828 END IF;
2829 ELSE
2830 l_end := g_count-1;
2831 l_start := 1;
2832 END IF;
2833 END IF;
2834 pay_in_utils.set_location(g_debug,l_procedure, 50);
2835
2836 IF g_debug THEN
2837 pay_in_utils.trace('Start record ',l_start);
2838 pay_in_utils.trace('End Record ',l_end);
2839 END IF;
2840
2841 FOR i IN l_start..l_end
2842 LOOP
2843
2844 OPEN get_eoy_archival_details(g_asg_tab(i).start_date
2845 ,g_asg_tab(i).end_date
2846 ,g_asg_tab(i).gre_id
2847 ,l_assignment_id
2848 );
2849 FETCH get_eoy_archival_details INTO l_run_asg_action_id;
2850 CLOSE get_eoy_archival_details;
2851
2852 pay_in_utils.set_location(g_debug,l_procedure, 60);
2853
2854 IF l_run_asg_action_id IS NOT NULL THEN
2855 pay_in_utils.set_location(g_debug,l_procedure, 70);
2856 OPEN c_get_date_earned(l_run_asg_action_id);
2857 FETCH c_get_date_earned INTO l_run_date_earned;
2858 CLOSE c_get_date_earned;
2859
2860 OPEN get_prepayment_date(l_run_asg_action_id);
2861 FETCH get_prepayment_date INTO l_pre_effective_date;
2862 CLOSE get_prepayment_date;
2863
2864
2865
2866 pay_in_utils.set_location(g_debug,l_procedure, 80);
2867
2868 l_previous_gre_asg_action_id := NULL;
2869 IF (i > 1 AND i <> l_record_count)-- Neither the first nor the last record. Hence determine the diff
2870 THEN -- Taxable House Rent Allowance_ASG_YTD as on previous and current GRE.
2871 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)
2872 LOOP
2873 l_previous_gre_asg_action_id := c_rec.run_asg_action_id;
2874 EXIT;
2875 END LOOP;
2876
2877 l_flag := TRUE;
2878 ELSIF (i = 1 AND l_record_count > 1)-- This is the first record in a multi tan scenario, hence
2879 THEN -- take the Taxable House Rent Allowance_ASG_YTD only.
2880 l_flag := TRUE;
2881 ELSIF (i = l_record_count AND l_record_count > 1)-- This is the latest record in multi TAN case.
2882 THEN -- Hence take the diff of projected and ytd value.
2883 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)
2884 LOOP
2885 l_previous_gre_asg_action_id := c_rec.run_asg_action_id;
2886 EXIT;
2887 END LOOP;
2888 l_flag := FALSE;
2889 ELSIF (i = 1 AND l_record_count = 1)-- There exists only one record, hence take the Projected value
2890 THEN
2891 l_flag := FALSE;
2892 END IF;
2893 pay_in_utils.set_location(g_debug,l_procedure, 90);
2894
2895 OPEN c_termination_check(l_assignment_id);
2896 FETCH c_termination_check INTO l_end_date;
2897 CLOSE c_termination_check;
2898 pay_in_utils.set_location(g_debug,l_procedure, 100);
2899
2900 archive_person_data(p_run_asg_action_id => l_run_asg_action_id
2901 ,p_arc_asg_action_id => p_assignment_action_id
2902 ,p_arc_payroll_act_id => l_arc_pay_action_id
2903 ,p_prepayment_date => l_pre_effective_date
2904 ,p_assignment_id => l_assignment_id
2905 ,p_gre_id => g_asg_tab(i).gre_id
2906 ,p_payroll_run_date => fnd_date.date_to_canonical(l_run_date_earned)
2907 ,p_effective_start_date => g_asg_tab(i).start_date
2908 ,p_effective_end_date => LEAST(g_asg_tab(i).end_date,l_end_date)
2909 );
2910 pay_in_utils.set_location(g_debug,l_procedure, 110);
2911
2912 archive_via_details(p_run_asg_action_id => l_run_asg_action_id
2913 ,p_arc_asg_action_id => p_assignment_action_id
2914 ,p_gre_id => g_asg_tab(i).gre_id
2915 ,p_assignment_id => l_assignment_id
2916 ,p_payroll_date => l_run_date_earned
2917 );
2918 pay_in_utils.set_location(g_debug,l_procedure, 120);
2919
2920 archive_allowances(p_run_asg_action_id => l_run_asg_action_id
2921 ,p_arc_asg_action_id => p_assignment_action_id
2922 ,p_gre_id => g_asg_tab(i).gre_id
2923 ,pre_gre_asg_act_id => l_previous_gre_asg_action_id
2924 ,p_flag => l_flag
2925 );
2926 pay_in_utils.set_location(g_debug,l_procedure, 130);
2927
2928 archive_perquisites(p_run_asg_action_id => l_run_asg_action_id
2929 ,p_arc_asg_action_id => p_assignment_action_id
2930 ,p_gre_id => g_asg_tab(i).gre_id
2931 ,pre_gre_asg_act_id => l_previous_gre_asg_action_id
2932 );
2933 pay_in_utils.set_location(g_debug,l_procedure, 140);
2934
2935 archive_eoy_salary(p_run_asg_action_id => l_run_asg_action_id
2936 ,p_arc_asg_action_id => p_assignment_action_id
2937 ,p_gre_id => g_asg_tab(i).gre_id
2938 );
2939 pay_in_utils.set_location(g_debug,l_procedure, 150);
2940
2941 archive_other_balances(p_run_asg_action_id => l_run_asg_action_id
2942 ,p_arc_asg_action_id => p_assignment_action_id
2943 ,pre_gre_asg_act_id => l_previous_gre_asg_action_id
2944 ,p_gre_id => g_asg_tab(i).gre_id
2945 ,p_start_date => g_asg_tab(i).start_date
2946 ,p_end_date => g_asg_tab(i).end_date
2947 );
2948 pay_in_utils.set_location(g_debug,l_procedure, 160);
2949
2950 OPEN c_pay_action_level_check(l_arc_pay_action_id,g_asg_tab(i).gre_id);
2951 FETCH c_pay_action_level_check INTO l_check;
2952 CLOSE c_pay_action_level_check;
2953 pay_in_utils.set_location(g_debug,l_procedure, 170);
2954
2955 IF l_check IS NULL
2956 THEN
2957 pay_in_utils.set_location(g_debug,l_procedure, 180);
2958 archive_org_data(p_arc_pay_action_id => l_arc_pay_action_id
2959 ,p_gre_id => g_asg_tab(i).gre_id
2960 ,p_effective_end_date => g_system_date
2961 );
2962 END IF;
2963 END IF;
2964
2965 END LOOP;
2966 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
2967 --
2968 EXCEPTION
2969 WHEN OTHERS THEN
2970 IF get_eoy_archival_details%ISOPEN THEN
2971 CLOSE get_eoy_archival_details;
2972 END IF;
2973 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
2974 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
2975 pay_in_utils.trace(l_message,l_procedure);
2976 RAISE;
2977 END archive_code;
2978
2979 END PAY_IN_EOY_ARCHIVE;