[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_FORM_24Q_WEB_ADI
Source
1 PACKAGE BODY pay_in_form_24q_web_adi AS
2 /* $Header: pyinwadi.pkb 120.17 2007/11/22 06:33:43 rsaharay noship $ */
3 g_package CONSTANT VARCHAR2(100) := 'pay_in_form_24q_web_adi.';
4 g_debug BOOLEAN ;
5 --
6 --------------------------------------------------------------------------
7 -- --
8 -- Name : GET_ASSESSMENT_YEAR --
9 -- Type : FUNCTION --
10 -- Access : Public --
11 -- Description : Function to return the assessment year --
12 -- --
13 -- Parameters : --
14 -- IN : --
15 -- RETURN : VARCHAR2 --
16 ---------------------------------------------------------------------------
17 FUNCTION get_assessment_year
18 RETURN VARCHAR2
19 IS
20 l_procedure VARCHAR2(250);
21 l_message VARCHAR2(250);
22
23 BEGIN
24 g_debug := hr_utility.debug_enabled;
25 l_procedure := g_package ||'get_assessment_year';
26 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
27 IF (g_debug)
28 THEN
29 pay_in_utils.trace('**************************************************','********************');
30 pay_in_utils.trace('Assessment Year is :',g_assessment_year);
31 pay_in_utils.trace('**************************************************','********************');
32 END IF;
33 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
34 RETURN g_assessment_year;
35 END get_assessment_year;
36
37 --------------------------------------------------------------------------
38 -- --
39 -- Name : SET_ASSESSMENT_YEAR --
40 -- Type : PROCEDURE --
41 -- Access : Public --
42 -- Description : Function to set the assessment year --
43 -- --
44 -- Parameters : --
45 -- IN : VARCHAR2 --
46 ---------------------------------------------------------------------------
47 PROCEDURE set_assessment_year(p_assessment_year VARCHAR2)
48 IS
49 l_procedure VARCHAR2(250);
50 l_message VARCHAR2(250);
51 BEGIN
52 g_debug := hr_utility.debug_enabled;
53 l_procedure := g_package ||'set_assessment_year';
54 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
55 g_assessment_year := p_assessment_year;
56 IF (g_debug)
57 THEN
58 pay_in_utils.trace('**************************************************','********************');
59 pay_in_utils.trace('p_assessment_year is :',p_assessment_year);
60 pay_in_utils.trace('g_assessment_year is :',g_assessment_year);
61 pay_in_utils.trace('**************************************************','********************');
62 END IF;
63 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
64 END set_assessment_year;
65 --------------------------------------------------------------------------
66 -- --
67 -- Name : GET_EE_VALUE --
68 -- Type : FUNCTION --
69 -- Access : Public --
70 -- Description : Function to return the element entry value --
71 -- --
72 -- Parameters : --
73 -- IN : p_element_entry_id NUMBER --
74 -- p_input_name VARCHAR2 --
75 -- p_effective_date DATE --
76 -- RETURN : VARCHAR2 --
77 ---------------------------------------------------------------------------
78 FUNCTION get_ee_value
79 (p_element_entry_id IN NUMBER
80 ,p_input_name IN VARCHAR2
81 ,p_effective_date IN DATE
82 )
83 RETURN VARCHAR2
84 IS
85 CURSOR c_entry_value
86 IS
87 SELECT val.screen_entry_value
88 FROM pay_element_entry_values_f val
89 ,pay_input_values_f inputs
90 WHERE val.input_value_id = inputs.input_value_id
91 AND val.element_entry_id = p_element_entry_id
92 AND inputs.name = p_input_name
93 AND inputs.legislation_code = 'IN'
94 AND p_effective_date between val.effective_start_date AND val.effective_end_date
95 AND p_effective_date between inputs.effective_start_date AND inputs.effective_end_date;
96 --
97 l_screen_entry_value pay_element_entry_values_f.screen_entry_value%TYPE := NULL;
98 l_procedure VARCHAR2(250);
99 l_message VARCHAR2(250);
100 BEGIN
101 g_debug := hr_utility.debug_enabled;
102 l_procedure := g_package ||'get_ee_value';
103 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
104
105 IF (g_debug)
106 THEN
107 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
108 pay_in_utils.trace('**************************************************','********************');
109 pay_in_utils.trace('p_element_entry_id ', p_element_entry_id);
110 pay_in_utils.trace('p_input_name ', p_input_name);
111 pay_in_utils.trace('p_effective_date ', p_effective_date);
112 END IF;
113
114 OPEN c_entry_value;
115 FETCH c_entry_value INTO l_screen_entry_value;
116 CLOSE c_entry_value;
117
118 IF (g_debug)
119 THEN
120 pay_in_utils.trace('Screen Entry Value is :',l_screen_entry_value);
121 pay_in_utils.trace('**************************************************','********************');
122 END IF;
123
124 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
125 RETURN l_screen_entry_value;
126 END get_ee_value;
127
128 --------------------------------------------------------------------------
129 -- --
130 -- Name : GET_ORG_ID --
131 -- Type : FUNCTION --
132 -- Access : Public --
133 -- Description : Function to return the organization id -
134 -- --
135 -- Parameters : --
136 -- IN : p_tan_number VARCHAR2 --
137 -- RETURN : VARCHAR2 --
138 ---------------------------------------------------------------------------
139 FUNCTION get_org_id
140 (p_tan_number IN VARCHAR2
141 )
142 RETURN VARCHAR2
143 IS
144 CURSOR c_tan_number
145 IS
146 SELECT hou.organization_id
147 FROM hr_organization_units hou
148 ,hr_organization_information hoi
149 WHERE hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
150 AND hoi.organization_id = hou.organization_id
151 AND hoi.org_information_context = 'PER_IN_INCOME_TAX_DF'
152 AND hoi.org_information1 = p_tan_number;
153
154 l_organization_id VARCHAR2(255);
155 l_procedure VARCHAR2(250);
156 l_message VARCHAR2(250);
157 BEGIN
158 g_debug := hr_utility.debug_enabled;
159 l_procedure := g_package ||'get_org_id';
160 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
161
162 IF (g_debug)
163 THEN
164 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
165 pay_in_utils.trace('**************************************************','********************');
166 pay_in_utils.trace('p_tan_number ', p_tan_number);
167 END IF;
168
169 OPEN c_tan_number;
170 FETCH c_tan_number INTO l_organization_id;
171 CLOSE c_tan_number;
172
173 IF (g_debug)
174 THEN
175 pay_in_utils.trace('l_organization_id',l_organization_id);
176 pay_in_utils.trace('**************************************************','********************');
177 END IF;
178
179 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
180
181 RETURN NVL(l_organization_id,'%');
182 END get_org_id;
183
184 --------------------------------------------------------------------------
185 -- --
186 -- Name : GET_TAN_NUMBER --
187 -- Type : FUNCTION --
188 -- Access : Public --
189 -- Description : Function to return the tan number for an organization-
190 -- --
191 -- Parameters : --
192 -- IN : p_assignment_id NUMBER --
193 -- p_effective_date DATE --
194 -- RETURN : VARCHAR2 --
195 ---------------------------------------------------------------------------
196 FUNCTION get_tan_number
197 (p_assignment_id IN NUMBER
198 ,p_effective_date IN DATE
199 )
200 RETURN VARCHAR2
201 IS
202 CURSOR c_tan_number
203 IS
204 SELECT hoi.org_information1
205 FROM hr_organization_units hou
206 ,hr_organization_information hoi
207 ,per_assignments_f asg
208 ,hr_soft_coding_keyflex scl
209 WHERE asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
210 AND asg.business_group_id = hou.business_group_id
211 AND asg.assignment_id = p_assignment_id
212 AND TO_NUMBER(scl.segment1) = hoi.organization_id
213 AND hoi.organization_id = hou.organization_id
214 AND hoi.org_information_context = 'PER_IN_INCOME_TAX_DF'
215 AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
216 AND p_effective_date BETWEEN hou.date_from AND NVL(date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
217 --
218 l_tan_number hr_organization_information.org_information1%TYPE;
219 l_procedure VARCHAR2(250);
220 l_message VARCHAR2(250);
221 BEGIN
222 g_debug := hr_utility.debug_enabled;
223 l_procedure := g_package ||'get_tan_number';
224 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
225 IF (g_debug)
226 THEN
227 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
228 pay_in_utils.trace('**************************************************','********************');
229 pay_in_utils.trace('p_assignment_id ',p_assignment_id);
230 pay_in_utils.trace('p_effective_date ',p_effective_date);
231 END IF;
232
233 OPEN c_tan_number;
234 FETCH c_tan_number INTO l_tan_number;
235 CLOSE c_tan_number;
236
237 IF (g_debug)
238 THEN
239 pay_in_utils.trace('l_tan_number',l_tan_number);
240 pay_in_utils.trace('**************************************************','********************');
241 END IF;
242
243 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
244 RETURN l_tan_number;
245 END;
246
247 --------------------------------------------------------------------------
248 -- --
249 -- Name : GET_TAN_NUMBER_EE --
250 -- Type : FUNCTION --
251 -- Access : Public --
252 -- Description : Function to return the tan number for an organization-
253 -- --
254 -- Parameters : --
255 -- IN : p_element_entry_id NUMBER --
256 -- RETURN : VARCHAR2 --
257 ---------------------------------------------------------------------------
258 FUNCTION get_tan_number_ee
259 (p_element_entry_id IN NUMBER
260 )
261 RETURN VARCHAR2
262 IS
263 CURSOR c_element_details
264 IS
265 SELECT effective_start_date
266 ,assignment_id
267 FROM pay_element_entries_f
268 WHERE element_entry_id = p_element_entry_id;
269
270 l_effective_date DATE;
271 l_assignment_id NUMBER;
272 l_ee_payment_date DATE;
273 l_tan_number hr_organization_information.org_information1%TYPE;
274 l_procedure VARCHAR2(250);
275 l_message VARCHAR2(250);
276 BEGIN
277 g_debug := hr_utility.debug_enabled;
278 l_procedure := g_package ||'get_tan_number_ee';
279 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
280
281 IF (g_debug)
282 THEN
283 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
284 pay_in_utils.trace('**************************************************','********************');
285 pay_in_utils.trace('p_element_entry_id',p_element_entry_id);
286 END IF;
287
288 OPEN c_element_details;
289 FETCH c_element_details INTO l_effective_date,l_assignment_id;
290 CLOSE c_element_details;
291
292 IF (g_debug)
293 THEN
294 pay_in_utils.trace('l_effective_date',l_effective_date);
295 pay_in_utils.trace('l_assignment_id',l_assignment_id);
296 END IF;
297 l_ee_payment_date := fnd_date.canonical_to_date
298 (
299 get_ee_value(p_element_entry_id
300 ,'Payment Date'
301 ,l_effective_date
302 )
303 );
304 pay_in_utils.trace('l_ee_payment_date',l_ee_payment_date);
305
306 l_tan_number := get_tan_number(l_assignment_id
307 ,l_ee_payment_date
308 );
309 pay_in_utils.trace('l_tan_number',l_tan_number);
310 pay_in_utils.trace('**************************************************','********************');
311 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
312 RETURN l_tan_number;
313 END get_tan_number_ee;
314
315 --------------------------------------------------------------------------
316 -- --
317 -- Name : GET_DATE_EARNED --
318 -- Type : FUNCTION --
319 -- Access : Public --
320 -- Description : Function to return the date earned --
321 -- --
322 -- Parameters : --
323 -- IN : p_assignment_action_id NUMBER --
324 -- RETURN : DATE --
325 ---------------------------------------------------------------------------
326 FUNCTION get_date_earned
327 (p_assignment_action_id IN NUMBER
328 )
329 RETURN DATE
330 IS
331
332 CURSOR c_date_earned
333 IS
334 SELECT NVL(ppa.date_earned,ppa.effective_date)
335 FROM pay_payroll_actions ppa
336 ,pay_assignment_actions paa
337 ,pay_action_interlocks pai
338 WHERE pai.locking_action_id = p_assignment_action_id
339 AND pai.locked_action_id = paa.assignment_action_id
343 ORDER BY TO_NUMBER(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id) DESC ;
340 AND paa.payroll_action_id = ppa.payroll_action_Id
341 AND ppa.action_type IN ('Q','R','A')
342 AND ppa.action_status = 'C'
344
345
346 l_date_earned DATE;
347 l_procedure VARCHAR2(250);
348 l_message VARCHAR2(250);
349 BEGIN
350 g_debug := hr_utility.debug_enabled;
351 l_procedure := g_package ||'get_date_earned';
352 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
353 IF (g_debug)
354 THEN
355 pay_in_utils.trace('**************************************************','********************');
356 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
357 pay_in_utils.trace('p_assignment_action_id ',p_assignment_action_id);
358 END IF;
359
360 OPEN c_date_earned;
361 FETCH c_date_earned INTO l_date_earned;
362 CLOSE c_date_earned;
363
364 IF (g_debug)
365 THEN
366 pay_in_utils.trace('l_date_earned',l_date_earned);
367 END IF;
368
369 pay_in_utils.trace('**************************************************','********************');
370 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
371 RETURN l_date_earned;
372 END get_date_earned;
373 --------------------------------------------------------------------------
374 -- --
375 -- Name : GET_DATE_EARNED_EE --
376 -- Type : FUNCTION --
377 -- Access : Public --
378 -- Description : Function to return the date earned --
379 -- --
380 -- Parameters : --
381 -- IN : p_element_entry_id NUMBER --
382 -- RETURN : DATE --
383 ---------------------------------------------------------------------------
384 FUNCTION get_date_earned_ee
385 (p_element_entry_id IN NUMBER
386 )
387 RETURN DATE
388 IS
389 CURSOR c_element_details
390 IS
391 SELECT effective_start_date
392 ,assignment_id
393 FROM pay_element_entries_f
394 WHERE element_entry_id = p_element_entry_id;
395
396 CURSOR c_prepayment_record(p_assignment_id NUMBER
397 ,p_effective_date DATE
398 )
399 IS
400 SELECT assignment_action_id
401 FROM pay_payroll_actions pre_ppa
402 ,pay_assignment_actions pre_paa
403 WHERE pre_ppa.payroll_action_id = pre_paa.payroll_action_id
404 AND pre_paa.assignment_id = p_assignment_id
405 AND pre_ppa.action_type IN ('P','U')
406 AND pre_ppa.action_status = 'C'
407 AND (pre_ppa.date_earned = p_effective_date
408 OR
409 pre_ppa.effective_date = p_effective_date
410 );
411
412 CURSOR c_prepayment_record_one(p_assignment_id NUMBER
413 ,p_effective_date DATE
414 ,p_ee_payment_date DATE
415 )
416 IS
417 SELECT assignment_action_id
418 FROM pay_payroll_actions pre_ppa
419 ,pay_assignment_actions pre_paa
420 WHERE pre_ppa.payroll_action_id = pre_paa.payroll_action_id
421 AND pre_paa.assignment_id = p_assignment_id
422 AND pre_ppa.action_type IN ('P','U')
423 AND pre_ppa.action_status = 'C'
424 AND ((pre_ppa.date_earned <= p_ee_payment_date AND pre_ppa.date_earned >= p_effective_date)
425 OR
426 (pre_ppa.effective_date <= p_ee_payment_date AND pre_ppa.effective_date >= p_effective_date)
427 )
428 ORDER BY assignment_action_id desc;
429
430 CURSOR c_prepayment_record_two(p_assignment_id NUMBER
431 ,p_effective_date DATE
432 ,p_ee_payment_date DATE
433 )
434 IS
435 SELECT assignment_action_id
436 FROM pay_payroll_actions pre_ppa
437 ,pay_assignment_actions pre_paa
438 WHERE pre_ppa.payroll_action_id = pre_paa.payroll_action_id
439 AND pre_paa.assignment_id = p_assignment_id
440 AND pre_ppa.action_type IN ('P','U')
441 AND pre_ppa.action_status = 'C'
442 AND (pre_ppa.date_earned >= p_ee_payment_date
443 OR
444 pre_ppa.effective_date >= p_ee_payment_date
445 )
446 ORDER BY assignment_action_id asc;
447
448 CURSOR c_payroll_run_record(p_assignment_id NUMBER
449 ,p_assignment_action_id NUMBER
450 )
451 IS
452 SELECT NVL(date_earned,effective_date)
453 FROM pay_payroll_actions ppa
454 ,pay_assignment_actions paa
455 ,pay_action_interlocks pai
456 WHERE ppa.payroll_action_id = paa.payroll_action_id
457 AND paa.assignment_id = p_assignment_id
458 AND ppa.action_type IN ('Q','R','A')
459 AND ppa.action_status = 'C'
460 AND paa.source_action_id IS NOT NULL
464
461 AND pai.locking_action_id = p_assignment_action_id
462 AND pai.locked_action_id = paa.assignment_action_id
463 ORDER BY TO_NUMBER(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id) DESC ;
465
466 l_effective_date DATE;
467 l_assignment_id NUMBER;
468 l_ee_payment_date DATE;
469 l_asg_action_id NUMBER;
470 l_date DATE;
471 l_procedure VARCHAR2(250);
472 l_message VARCHAR2(250);
473 BEGIN
474 g_debug := hr_utility.debug_enabled;
475 l_procedure := g_package ||'get_date_earned_ee';
476 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
477 IF (g_debug)
478 THEN
479 pay_in_utils.trace('**************************************************','********************');
480 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
481 pay_in_utils.trace('p_element_entry_id',p_element_entry_id);
482 END IF;
483
484 OPEN c_element_details;
485 FETCH c_element_details INTO l_effective_date,l_assignment_id;
486 CLOSE c_element_details;
487
488 IF (g_debug)
489 THEN
490 pay_in_utils.trace('l_effective_date',l_effective_date);
491 pay_in_utils.trace('l_assignment_id',l_assignment_id);
492 END IF;
493
494 l_ee_payment_date := fnd_date.canonical_to_date
495 (
496 get_ee_value(p_element_entry_id
497 ,'Payment Date'
498 ,l_effective_date
499 )
500 );
501
502 IF (g_debug)
503 THEN
504 pay_in_utils.trace('l_ee_payment_date',l_ee_payment_date);
505 END IF;
506
507 OPEN c_prepayment_record(l_assignment_id,l_ee_payment_date);
508 FETCH c_prepayment_record INTO l_asg_action_id;
509 CLOSE c_prepayment_record;
510
511 IF (g_debug)
512 THEN
513 pay_in_utils.trace('l_asg_action_id',l_asg_action_id);
514 END IF;
515
516 IF (l_asg_action_id IS NULL)
517 THEN
518 OPEN c_prepayment_record_one(l_assignment_id,l_effective_date,l_ee_payment_date);
519 FETCH c_prepayment_record_one INTO l_asg_action_id;
520 CLOSE c_prepayment_record_one;
521
522 IF (g_debug)
523 THEN
524 pay_in_utils.trace('l_asg_action_id',l_asg_action_id);
525 END IF;
526
527 IF (l_asg_action_id IS NULL)
528 THEN
529 OPEN c_prepayment_record_two(l_assignment_id,l_effective_date,l_ee_payment_date);
530 FETCH c_prepayment_record_two INTO l_asg_action_id;
531 CLOSE c_prepayment_record_two;
532
533 IF (g_debug)
534 THEN
535 pay_in_utils.trace('l_asg_action_id',l_asg_action_id);
536 END IF;
537 END IF;
538 END IF;
539
540 OPEN c_payroll_run_record(l_assignment_id,l_asg_action_id);
541 FETCH c_payroll_run_record INTO l_date;
542 CLOSE c_payroll_run_record;
543
544 IF (g_debug)
545 THEN
546 pay_in_utils.trace('l_date',l_date);
547 END IF;
548
549 pay_in_utils.trace('**************************************************','********************');
550 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
551 RETURN l_date;
552
553 END get_date_earned_ee;
554
555 --------------------------------------------------------------------------
556 -- --
557 -- Name : GET_BALANCE_VALUE --
558 -- Type : FUNCTION --
559 -- Access : Public --
560 -- Description : Function to return the balance value --
561 -- --
562 ---------------------------------------------------------------------------
563 FUNCTION get_balance_value(p_assignment_action_id IN NUMBER
564 ,p_balance_name IN VARCHAR2
565 ,p_dimension IN VARCHAR2
566 )
567 RETURN VARCHAR2
568 IS
569
570 CURSOR c_action_type
571 IS
572 SELECT ppa.action_type
573 FROM pay_payroll_actions ppa
574 ,pay_assignment_actions paa
575 WHERE ppa.payroll_action_id = paa.payroll_action_id
576 AND paa.assignment_action_id = p_assignment_action_id
577 AND ppa.action_status = 'C';
578
579 CURSOR c_master_asg_action_id
580 IS
581 SELECT pai.locked_action_id assignment_action_id
582 ,paa.assignment_id
583 FROM pay_action_interlocks pai
584 ,pay_assignment_actions paa
585 WHERE pai.locking_action_id = p_assignment_action_id
586 AND pai.locked_action_id = paa.assignment_action_id
587 AND paa.action_status = 'C'
588 AND paa.source_action_id IS NULL
589 ORDER BY pai.locked_action_id DESC;
590
591 CURSOR c_child_asg_actions(p_assignment_id NUMBER
592 ,p_master_asg_act_id NUMBER
596 FROM pay_assignment_actions
593 )
594 IS
595 SELECT assignment_action_id child_actions
597 WHERE assignment_id = p_assignment_id
598 AND action_status = 'C'
599 AND source_action_id = p_master_asg_act_id;
600
601 l_action_type pay_payroll_actions.action_type%TYPE;
602 l_assignment_action_id NUMBER;
603 l_balance_value NUMBER;
604 l_procedure VARCHAR2(250);
605 l_message VARCHAR2(250);
606 BEGIN
607 g_debug := hr_utility.debug_enabled;
608 l_procedure := g_package ||'get_balance_value';
609 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
610 IF (g_debug)
611 THEN
612 pay_in_utils.trace('**************************************************','********************');
613 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
614 pay_in_utils.trace('p_assignment_action_id',p_assignment_action_id);
615 pay_in_utils.trace('p_balance_name',p_balance_name);
616 pay_in_utils.trace('p_dimension',p_dimension);
617 END IF;
618
619 OPEN c_action_type;
620 FETCH c_action_type INTO l_action_type;
621 CLOSE c_action_type;
622
623 IF (g_debug)
624 THEN
625 pay_in_utils.trace('l_action_type',l_action_type);
626 END IF;
627
628 IF (l_action_type IN ('P','U'))
629 THEN
630 l_balance_value := 0;
631 FOR c_rec IN c_master_asg_action_id
632 LOOP
633 FOR c_record IN c_child_asg_actions(c_rec.assignment_id
634 ,c_rec.assignment_action_id
635 )
636 LOOP
637 l_balance_value := l_balance_value + pay_in_tax_utils.get_balance_value
638 (
639 c_record.child_actions
640 ,p_balance_name
641 ,p_dimension
642 ,'NULL'
643 ,'NULL'
644 );
645 END LOOP;
646 END LOOP;
647 ELSE
648 l_assignment_action_id := p_assignment_action_id;
649 l_balance_value := pay_in_tax_utils.get_balance_value
650 (
651 l_assignment_action_id
652 ,p_balance_name
653 ,p_dimension
654 ,'NULL'
655 ,'NULL'
656 );
657 END IF;
658
659 IF (g_debug)
660 THEN
661 pay_in_utils.trace('l_assignment_action_id',l_assignment_action_id);
662 pay_in_utils.trace('l_balance_value',l_balance_value);
663 END IF;
664 pay_in_utils.trace('**************************************************','********************');
665 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
666 RETURN TO_CHAR(l_balance_value);
667
668 END get_balance_value;
669 --------------------------------------------------------------------------
670 -- --
671 -- Name : GET_TOTAL_TAX_DEPOSITED --
672 -- Type : FUNCTION --
673 -- Access : Public --
674 -- Description : Function to return the total tax deposited --
675 -- --
676 ---------------------------------------------------------------------------
677 FUNCTION get_total_tax_deposited(p_assignment_action_id IN NUMBER
678 ,p_element_entry_id IN NUMBER
679 ,p_effective_date IN DATE DEFAULT NULL
680 )
681 RETURN VARCHAR2
682 IS
683 l_total_tax NUMBER;
684 l_procedure VARCHAR2(250);
685 l_message VARCHAR2(250);
686 BEGIN
687 g_debug := hr_utility.debug_enabled;
688 l_procedure := g_package ||'get_total_tax_deposited';
689 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
690 IF (g_debug)
691 THEN
692 pay_in_utils.trace('**************************************************','********************');
693 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
694 pay_in_utils.trace('p_assignment_action_id',p_assignment_action_id);
695 pay_in_utils.trace('p_element_entry_id',p_element_entry_id);
696 pay_in_utils.trace('p_effective_date',p_effective_date);
697 END IF;
698
699 IF (p_element_entry_id IS NULL)
700 THEN
701 --Use the balance values
702 l_total_tax := TO_NUMBER(get_balance_value(p_assignment_action_id
703 ,'Income Tax This Pay'
704 ,'_ASG_RUN'
705 )
706 );
707 l_total_tax := l_total_tax +
711 )
708 TO_NUMBER(get_balance_value(p_assignment_action_id
709 ,'Surcharge This Pay'
710 ,'_ASG_RUN'
712 );
713 l_total_tax := l_total_tax +
714 TO_NUMBER(get_balance_value(p_assignment_action_id
715 ,'Education Cess This Pay'
716 ,'_ASG_RUN'
717 )
718 );
719 l_total_tax := l_total_tax +
720 TO_NUMBER(get_balance_value(p_assignment_action_id
721 ,'TDS on Direct Payments'
722 ,'_ASG_RUN'
723 )
724 );
725 ELSE
726 -- Use Element Entry ID
727 l_total_tax := TO_NUMBER(get_ee_value(p_element_entry_id
728 ,'Income Tax Deducted'
729 ,p_effective_date
730 )
731 );
732 l_total_tax := l_total_tax +
733 TO_NUMBER(get_ee_value(p_element_entry_id
734 ,'Surcharge Deducted'
735 ,p_effective_date
736 )
737 );
738 l_total_tax := l_total_tax +
739 TO_NUMBER(get_ee_value(p_element_entry_id
740 ,'Education Cess Deducted'
741 ,p_effective_date
742 )
743 );
744 END IF;
745
746 IF (g_debug)
747 THEN
748 pay_in_utils.trace('l_total_tax',l_total_tax);
749 END IF;
750 pay_in_utils.trace('**************************************************','********************');
751 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
752
753 RETURN (TO_CHAR(NVL(l_total_tax,0),fnd_currency.get_format_mask('INR',40)));
754 END;
755 --------------------------------------------------------------------------
756 -- --
757 -- Name : GET_BG_ID --
758 -- Type : FUNCTION --
759 -- Access : Public --
760 -- Description : Function to return the business group id --
761 -- --
762 -- Parameters : --
763 -- IN : --
764 -- RETURN : VARCHAR2 --
765 ---------------------------------------------------------------------------
766 FUNCTION get_bg_id
767 RETURN NUMBER
768 IS
769 CURSOR c_bg
770 IS
771 SELECT FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
772 FROM dual;
773 --
774 l_bg NUMBER;
775 l_procedure VARCHAR2(250);
776 l_message VARCHAR2(250);
777 BEGIN
778 g_debug := hr_utility.debug_enabled;
779 l_procedure := g_package ||'get_bg_id';
780 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
781
782 OPEN c_bg;
783 FETCH c_bg INTO l_bg;
784 CLOSE c_bg;
785
786 IF (g_debug)
787 THEN
788 pay_in_utils.trace('**************************************************','********************');
789 pay_in_utils.trace('l_bg',l_bg);
790 pay_in_utils.trace('**************************************************','********************');
791 END IF;
792 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
793
794 RETURN l_bg;
795
796 END get_bg_id;
797
798 --------------------------------------------------------------------------
799 -- --
800 -- Name : CREATE_FORM_24 --
801 -- Type : PROCEDURE --
802 -- Access : Public --
803 -- Description : Function to create the element as per the details --
804 -- passed from the Web ADI Excel Sheet. --
805 ---------------------------------------------------------------------------
806 PROCEDURE create_form_24
807 (p_assessment_year IN VARCHAR2 DEFAULT NULL
808 ,p_payroll_name IN VARCHAR2 DEFAULT NULL
809 ,p_period IN VARCHAR2 DEFAULT NULL
810 ,p_earned_date IN DATE DEFAULT NULL
811 ,p_pre_payment_date IN DATE
812 ,p_employee_id IN VARCHAR2
813 ,p_employee_name IN VARCHAR2 DEFAULT NULL
814 ,p_taxable_income IN NUMBER DEFAULT NULL
815 ,p_income_tax_deducted IN NUMBER DEFAULT NULL
816 ,p_surcharge_deducted IN NUMBER DEFAULT NULL
820 ,p_voucher_number IN VARCHAR2
817 ,p_education_cess_deducted IN NUMBER DEFAULT NULL
818 ,p_total_tax_deducted IN NUMBER DEFAULT NULL
819 ,p_amount_deposited IN NUMBER
821 ,p_correction_flag IN VARCHAR2
822 ,p_last_updated_date IN DATE DEFAULT NULL
823 ,p_element_entry_id IN NUMBER DEFAULT NULL
824 ,p_tan_number IN VARCHAR2 DEFAULT NULL
825 ,p_purge_record IN VARCHAR2 DEFAULT NULL
826 ,p_assignment_id IN NUMBER
827 )
828 IS
829 --If element entry id id not null, then use it to determine the element details
830 CURSOR c_element_details(p_effective_date DATE,
831 p_business_group_id NUMBER
832 )
833 IS
834 SELECT element_type_id
835 ,element_link_id
836 ,asg.assignment_id
837 ,entry.object_version_number
838 FROM pay_element_entries_f entry
839 ,per_assignments_f asg
840 WHERE asg.business_group_id = p_business_group_id
841 AND asg.assignment_id = entry.assignment_id
842 AND entry.element_entry_id = p_element_entry_id
843 AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
844 AND p_effective_date BETWEEN entry.effective_start_date AND entry.effective_end_date;
845
846
847 --Get Element Details (type id and link id)
848 CURSOR csr_element_details(p_assignment_id NUMBER
849 ,p_effective_date DATE
850 )
851 IS
852 SELECT types.element_type_id
853 ,link.element_link_id
854 FROM per_assignments_f assgn
855 , pay_element_links_f link
856 , pay_element_types_f types
857 WHERE assgn.assignment_id = p_assignment_id
858 AND link.element_link_id = pay_in_utils.get_element_link_id(p_assignment_id
859 ,p_pre_payment_date
860 ,types.element_type_id
861 )
862 AND (types.processing_type = 'R' OR assgn.payroll_id IS NOT NULL)
863 AND link.business_group_id = assgn.business_group_id
864 AND link.element_type_id = types.element_type_id
865 AND types.element_name = 'Income Tax Challan Information'
866 AND p_effective_date BETWEEN assgn.effective_start_date AND assgn.effective_end_date
867 AND p_effective_date BETWEEN link.effective_start_date AND link.effective_end_date
868 AND p_effective_date BETWEEN types.effective_start_date AND types.effective_end_date;
869
870 -- Creating the input value name,id pair records
871 CURSOR c_input_rec(p_element_type_id NUMBER
872 ,p_effective_date DATE
873 )
874 IS
875 SELECT inputs.name name
876 , inputs.input_value_id id
877 FROM pay_element_types_f types
878 , pay_input_values_f inputs
879 WHERE types.element_type_id = p_element_type_id
880 AND inputs.element_type_id = types.element_type_id
881 AND inputs.legislation_code = 'IN'
882 AND p_effective_date BETWEEN types.effective_start_date AND types.effective_end_date
883 AND p_effective_date BETWEEN inputs.effective_start_date AND inputs.effective_end_date
884 ORDER BY inputs.display_sequence;
885
886 -- Cursor to retreive the element effective start date
887 CURSOR c_effective_start_date
888 IS
889 SELECT effective_start_date,object_version_number
890 FROM pay_element_entries_f
891 WHERE element_entry_id = p_element_entry_id
892 ORDER BY object_version_number DESC;
893
894 -- Cursor to determine the employee number
895 CURSOR c_check_emp_number(p_element_entry_id NUMBER
896 ,p_employee_number VARCHAR2
897 ,p_effective_date DATE
898 ,p_busines_group_id NUMBER
899 )
900 IS
901 SELECT 1
902 FROM per_people_f pep
903 ,per_assignments_f asg
904 ,pay_element_entries_f entry
905 WHERE entry.element_entry_id = p_element_entry_id
906 AND asg.assignment_id = entry.assignment_id
907 AND asg.person_id = pep.person_id
908 AND asg.business_group_id = pep.business_group_id
909 AND pep.employee_number = p_employee_number
910 AND asg.business_group_id = p_busines_group_id
911 AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
912 AND p_effective_date BETWEEN pep.effective_start_date AND pep.effective_end_date
913 AND p_effective_date BETWEEN entry.effective_start_date AND entry.effective_end_date;
914
915 --Variables Initialization
916 TYPE t_input_values_rec IS RECORD
917 (input_name pay_input_values_f.name%TYPE
918 ,input_value_id pay_input_values_f.input_value_id%TYPE
919 );
920 TYPE t_input_values_tab IS TABLE OF t_input_values_rec INDEX BY BINARY_INTEGER;
921
922 l_assignment_id NUMBER;
923 l_element_type_id NUMBER;
924 l_element_link_id NUMBER;
925 l_element_entry_id NUMBER;
926 l_input_values_rec t_input_values_tab;
930 l_effective_end_date DATE;
927 l_count NUMBER;
928 l_effective_date DATE;
929 l_effective_start_date DATE;
931 l_object_version_number NUMBER;
932 l_warnings BOOLEAN;
933 l_business_group_id NUMBER;
934 l_pre_payment_date DATE;
935 flag BOOLEAN;
936 l_flag NUMBER := NULL;
937 l_procedure VARCHAR2(250);
938 l_message VARCHAR2(250);
939 BEGIN
940 g_debug := hr_utility.debug_enabled;
941 l_procedure := g_package ||'create_form_24';
942 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
943 IF (g_debug)
944 THEN
945 pay_in_utils.trace('**************************************************','********************');
946 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
947 pay_in_utils.trace('p_assessment_year ',p_assessment_year );
948 pay_in_utils.trace('p_payroll_name ',p_payroll_name );
949 pay_in_utils.trace('p_period ',p_period );
950 pay_in_utils.trace('p_earned_date ',p_earned_date );
951 pay_in_utils.trace('p_pre_payment_date ',p_pre_payment_date );
952 pay_in_utils.trace('p_employee_id ',p_employee_id );
953 pay_in_utils.trace('p_employee_name ',p_employee_name );
954 pay_in_utils.trace('p_taxable_income ',p_taxable_income );
955 pay_in_utils.trace('p_income_tax_deducted ',p_income_tax_deducted );
956 pay_in_utils.trace('p_surcharge_deducted ',p_surcharge_deducted );
957 pay_in_utils.trace('p_education_cess_deducted',p_education_cess_deducted);
958 pay_in_utils.trace('p_total_tax_deducted ',p_total_tax_deducted );
959 pay_in_utils.trace('p_amount_deposited ',p_amount_deposited );
960 pay_in_utils.trace('p_voucher_number ',p_voucher_number );
961 pay_in_utils.trace('p_correction_flag ',p_correction_flag );
962 pay_in_utils.trace('p_last_updated_date ',p_last_updated_date );
963 pay_in_utils.trace('p_element_entry_id ',p_element_entry_id );
964 pay_in_utils.trace('p_tan_number ',p_tan_number );
965 pay_in_utils.trace('p_purge_record ',p_purge_record );
966 pay_in_utils.trace('p_assignment_id ',p_assignment_id );
967 END IF;
968 flag := FALSE;
969 l_effective_date := pay_in_utils.get_effective_date(p_pre_payment_date);
970 l_business_group_id := get_bg_id();
971
972 IF (g_debug)
973 THEN
974 pay_in_utils.trace('l_effective_date',l_effective_date);
975 pay_in_utils.trace('l_business_group_id',l_business_group_id);
976 END IF;
977 --Record Deletion Starts
978 IF (NVL(p_purge_record,'N')= 'Y')
979 THEN
980
981 IF (p_element_entry_id IS NOT NULL)
982 THEN
983 OPEN c_effective_start_date;
984 FETCH c_effective_start_date INTO l_pre_payment_date,l_object_version_number;
985 CLOSE c_effective_start_date;
986
987 IF (g_debug)
988 THEN
989 pay_in_utils.trace('l_pre_payment_date',l_pre_payment_date);
990 pay_in_utils.trace('l_object_version_number',l_object_version_number);
991 END IF;
992
993 pay_in_utils.set_location(g_debug,'Calling Deletion API',20);
994
995 --Delete the element entry id.
996 pay_element_entry_api.delete_element_entry
997 (p_validate => FALSE
998 ,p_datetrack_delete_mode => hr_api.g_delete
999 ,p_effective_date => l_pre_payment_date
1000 ,p_element_entry_id => p_element_entry_id
1001 ,p_object_version_number => l_object_version_number
1002 ,p_effective_start_date => l_effective_start_date
1003 ,p_effective_end_date => l_effective_end_date
1004 ,p_delete_warning => l_warnings
1005 );
1006
1007 pay_in_utils.set_location(g_debug,'Deletion API Successful',30);
1008 pay_in_utils.trace('**************************************************','********************');
1009 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
1010 RETURN;
1011 ELSE
1012 pay_in_utils.trace('**************************************************','********************');
1013 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
1014 RETURN;
1015 END IF;
1016 END IF;
1017 --Record Deletion ends
1018
1019 IF (p_element_entry_id IS NOT NULL)
1020 THEN
1021 l_pre_payment_date := fnd_date.canonical_to_date(get_ee_value(p_element_entry_id,'Payment Date',l_effective_date));
1022
1023 IF (g_debug)
1024 THEN
1025 pay_in_utils.trace('l_pre_payment_date',l_pre_payment_date);
1026 END IF;
1027
1028 OPEN c_check_emp_number(p_element_entry_id,p_employee_id,l_effective_date,l_business_group_id);
1032 IF (g_debug)
1029 FETCH c_check_emp_number INTO l_flag;
1030 CLOSE c_check_emp_number;
1031
1033 THEN
1034 pay_in_utils.trace('l_flag',l_flag);
1035 END IF;
1036
1037 IF ((l_pre_payment_date IS NULL) OR (l_flag IS NULL))
1038 THEN
1039
1040 OPEN c_effective_start_date;
1041 FETCH c_effective_start_date INTO l_pre_payment_date,l_object_version_number;
1042 CLOSE c_effective_start_date;
1043
1044 IF (g_debug)
1045 THEN
1046 pay_in_utils.trace('l_pre_payment_date',l_pre_payment_date);
1047 pay_in_utils.trace('l_object_version_number',l_object_version_number);
1048 END IF;
1049 pay_in_utils.set_location(g_debug,'Calling Deletion API',20);
1050
1051 --Delete the element entry id.
1052 pay_element_entry_api.delete_element_entry
1053 (p_validate => FALSE
1054 ,p_datetrack_delete_mode => hr_api.g_delete
1055 ,p_effective_date => l_pre_payment_date
1056 ,p_element_entry_id => p_element_entry_id
1057 ,p_object_version_number => l_object_version_number
1058 ,p_effective_start_date => l_effective_start_date
1059 ,p_effective_end_date => l_effective_end_date
1060 ,p_delete_warning => l_warnings
1061 );
1062 pay_in_utils.set_location(g_debug,'Deletion API Successful',30);
1063 flag := TRUE;
1064
1065 ELSE
1066 OPEN c_element_details(l_effective_date,l_business_group_id);
1067 FETCH c_element_details INTO l_element_type_id,l_element_link_id,l_assignment_id,l_object_version_number;
1068 CLOSE c_element_details;
1069
1070 IF (g_debug)
1071 THEN
1072 pay_in_utils.trace('l_element_type_id',l_element_type_id);
1073 pay_in_utils.trace('l_element_link_id',l_element_link_id);
1074 pay_in_utils.trace('l_assignment_id',l_assignment_id);
1075 pay_in_utils.trace('l_object_version_number',l_object_version_number);
1076 END IF;
1077 END IF;
1078 END IF;
1079
1080 IF ((p_element_entry_id IS NULL) OR (flag))
1081 THEN
1082
1083 l_assignment_id := p_assignment_id ;
1084
1085 IF (g_debug)
1086 THEN
1087 pay_in_utils.trace('l_assignment_id',l_assignment_id);
1088 END IF;
1089
1090 OPEN csr_element_details(l_assignment_id,l_effective_date);
1091 FETCH csr_element_details INTO l_element_type_id,l_element_link_id;
1092 CLOSE csr_element_details;
1093
1094 IF (g_debug)
1095 THEN
1096 pay_in_utils.trace('l_element_type_id',l_element_type_id);
1097 pay_in_utils.trace('l_element_link_id',l_element_link_id);
1098 END IF;
1099
1100 END IF;
1101
1102 IF l_element_link_id IS NULL THEN
1103 hr_utility.set_message(800, 'PER_IN_MISSING_LINK');
1104 hr_utility.set_message_token('ELEMENT_NAME', 'Income Tax Challan Information');
1105 hr_utility.raise_error;
1106 END IF;
1107
1108 --Populate the input value id, name records
1109 l_count := 1;
1110 FOR c_rec IN c_input_rec(l_element_type_id,l_effective_date)
1111 LOOP
1112 l_input_values_rec(l_count).input_name := c_rec.name;
1113 l_input_values_rec(l_count).input_value_id := c_rec.id;
1114 l_count := l_count + 1;
1115 END LOOP;
1116
1117 IF ((p_element_entry_id IS NULL) OR (flag))
1118 THEN
1119 pay_element_entry_api.create_element_entry
1120 (p_effective_date => l_effective_date
1121 ,p_business_group_id => l_business_group_id
1122 ,p_assignment_id => l_assignment_id
1123 ,p_element_link_id => l_element_link_id
1124 ,p_entry_type => 'E'
1125 ,p_input_value_id1 => l_input_values_rec(1).input_value_id
1126 ,p_input_value_id2 => l_input_values_rec(2).input_value_id
1127 ,p_input_value_id3 => l_input_values_rec(3).input_value_id
1128 ,p_input_value_id4 => l_input_values_rec(4).input_value_id
1129 ,p_input_value_id5 => l_input_values_rec(5).input_value_id
1130 ,p_input_value_id6 => l_input_values_rec(6).input_value_id
1131 ,p_input_value_id7 => l_input_values_rec(7).input_value_id
1132 ,p_input_value_id8 => l_input_values_rec(8).input_value_id
1133 ,p_input_value_id9 => l_input_values_rec(9).input_value_id
1134 ,p_entry_value1 => p_voucher_number
1135 ,p_entry_value2 => p_pre_payment_date
1136 ,p_entry_value3 => p_taxable_income
1137 ,p_entry_value4 => p_income_tax_deducted
1138 ,p_entry_value5 => p_surcharge_deducted
1139 ,p_entry_value6 => p_education_cess_deducted
1140 ,p_entry_value7 => p_amount_deposited
1141 ,p_entry_value8 => NVL(p_correction_flag,'N')
1142 ,p_entry_value9 => sysdate
1143 ,p_effective_start_date => l_effective_start_date
1144 ,p_effective_end_date => l_effective_end_date
1145 ,p_element_entry_id => l_element_entry_id
1146 ,p_object_version_number => l_object_version_number
1147 ,p_create_warning => l_warnings
1148 );
1149
1150 UPDATE pay_element_entry_values_f
1151 SET screen_entry_value = fnd_date.date_to_canonical(sysdate)
1152 WHERE input_value_id = l_input_values_rec(9).input_value_id
1153 AND element_entry_id = l_element_entry_id;
1154
1155 ELSIF ((p_element_entry_id IS NOT NULL) AND (flag = FALSE))
1156 THEN
1157 pay_element_entry_api.update_element_entry
1158 (p_datetrack_update_mode => hr_api.g_correction
1159 ,p_effective_date => l_effective_date
1160 ,p_business_group_id => l_business_group_id
1161 ,p_element_entry_id => p_element_entry_id
1162 ,p_object_version_number => l_object_version_number
1163 ,p_input_value_id1 => l_input_values_rec(1).input_value_id
1164 ,p_input_value_id2 => l_input_values_rec(2).input_value_id
1165 ,p_input_value_id3 => l_input_values_rec(3).input_value_id
1166 ,p_input_value_id4 => l_input_values_rec(4).input_value_id
1167 ,p_input_value_id5 => l_input_values_rec(5).input_value_id
1168 ,p_input_value_id6 => l_input_values_rec(6).input_value_id
1169 ,p_input_value_id7 => l_input_values_rec(7).input_value_id
1170 ,p_input_value_id8 => l_input_values_rec(8).input_value_id
1171 ,p_input_value_id9 => l_input_values_rec(9).input_value_id
1172 ,p_entry_value1 => p_voucher_number
1173 ,p_entry_value2 => p_pre_payment_date
1174 ,p_entry_value3 => p_taxable_income
1175 ,p_entry_value4 => p_income_tax_deducted
1176 ,p_entry_value5 => p_surcharge_deducted
1177 ,p_entry_value6 => p_education_cess_deducted
1178 ,p_entry_value7 => p_amount_deposited
1179 ,p_entry_value8 => NVL(p_correction_flag,'N')
1180 ,p_entry_value9 => sysdate
1181 ,p_effective_start_date => l_effective_start_date
1182 ,p_effective_end_date => l_effective_end_date
1183 ,p_update_warning => l_warnings
1184 );
1185
1186 UPDATE pay_element_entry_values_f
1187 SET screen_entry_value = fnd_date.date_to_canonical(sysdate)
1188 WHERE input_value_id = l_input_values_rec(9).input_value_id
1189 AND element_entry_id = p_element_entry_id;
1190
1191 END IF;
1192 pay_in_utils.trace('**************************************************','********************');
1193 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
1194 END create_form_24;
1195
1196 END pay_in_form_24q_web_adi;