[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_24Q_ER_RETURNS
Source
1 PACKAGE BODY pay_in_24q_er_returns AS
2 /* $Header: pyineqit.pkb 120.19.12020000.2 2012/07/06 11:00:39 vmaripal ship $ */
3 g_debug BOOLEAN ;
4 g_package CONSTANT VARCHAR2(100) := 'pay_in_24q_er_returns.';
5
6
7 --------------------------------------------------------------------------
8 -- --
9 -- Name : CHALLAN_REC_COUNT --
10 -- Type : FUNCTION --
11 -- Access : Public --
12 -- Description : This function returns the Total number of records --
13 -- in the Challan Details of the Magtape --
14 -- Parameters : --
15 -- IN : p_gre_org_id VARCHAR2 --
16 -- p_assess_period VARCHAR2 --
17 -- p_max_action_id VARCHAR2 --
18 --------------------------------------------------------------------------
19 FUNCTION challan_rec_count (p_gre_org_id IN VARCHAR2
20 ,p_assess_period IN VARCHAR2
21 ,p_max_action_id IN VARCHAR2)
22 RETURN VARCHAR2 IS
23
24 CURSOR c_count
25 IS
26 SELECT DECODE(COUNT(DISTINCT action_information1),0,'1',
27 COUNT(DISTINCT action_information1))
28 FROM pay_action_information pai
29 WHERE action_information_category = 'IN_24Q_CHALLAN'
30 AND action_context_type = 'PA'
31 AND action_information3 = p_gre_org_id
32 AND action_information2 = p_assess_period
33 AND pai.action_context_id= p_max_action_id
34 AND fnd_date.canonical_to_date(pai.action_information5)<=fnd_date.CHARDATE_TO_DATE(SYSDATE);
35
36 l_count NUMBER;
37 l_procedure varchar2(100);
38
39 BEGIN
40 g_debug := hr_utility.debug_enabled;
41 l_procedure := g_package ||'challan_rec_count';
42 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
43
44 IF g_debug THEN
45 pay_in_utils.trace('**************************************************','********************');
46 pay_in_utils.trace('p_gre_org_id',p_gre_org_id);
47 pay_in_utils.trace('p_assess_period',p_assess_period);
48 pay_in_utils.trace('p_max_action_id',p_max_action_id);
49 pay_in_utils.trace('**************************************************','********************');
50 END IF;
51
52 OPEN c_count;
53 FETCH c_count INTO l_count;
54 IF c_count%NOTFOUND THEN
55 CLOSE c_count;
56 RETURN '1';
57 END IF;
58 CLOSE c_count;
59
60
61 IF g_debug THEN
62 pay_in_utils.trace('l_count',TO_CHAR(l_count));
63 END IF;
64 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
65
66 RETURN TO_CHAR(l_count);
67
68 END challan_rec_count;
69
70 --------------------------------------------------------------------------
71 -- --
72 -- Name : GET_PRODUCT_RELEASE --
73 -- Type : FUNCTION --
74 -- Access : Public --
75 -- Description : This function returns the name of the software --
76 -- used for preparing the e-TDS statement in File --
77 -- Header --
78 --------------------------------------------------------------------------
79
80 FUNCTION get_product_release
81 RETURN VARCHAR2 IS
82 l_product_release VARCHAR2(50);
83 BEGIN
84 SELECT substr(p.product_version,1,2) INTO l_product_release
85 FROM fnd_application a, fnd_application_tl t, fnd_product_installations p
86 WHERE a.application_id = p.application_id
87 AND a.application_id = t.application_id
88 AND t.language = Userenv ('LANG')
89 AND Substr (a.application_short_name, 1, 5) = 'PAY';
90 RETURN l_product_release;
91 END get_product_release;
92
93 --------------------------------------------------------------------------
94 -- --
95 -- Name : DEDUCTEE_REC_COUNT --
96 -- Type : FUNCTION --
97 -- Access : Public --
98 -- Description : This function returns the Total number of records --
99 -- in the Deductee Details of the Magtape --
100 -- Parameters : --
101 -- IN : p_gre_org_id VARCHAR2 --
102 -- p_max_action_id VARCHAR2 --
103 -- p_challan VARCHAR2 --
104 --------------------------------------------------------------------------
105 FUNCTION deductee_rec_count (p_gre_org_id IN VARCHAR2
106 ,p_max_action_id IN VARCHAR2
107 ,p_challan IN VARCHAR2)
108 RETURN VARCHAR2 IS
109
110 CURSOR c_count
111 IS
112 SELECT COUNT(*)
113 FROM pay_action_information pai
114 WHERE action_information_category = 'IN_24Q_DEDUCTEE'
115 AND action_context_type = 'AAP'
116 AND action_information3 = p_gre_org_id
117 AND EXISTS (SELECT 1 FROM pay_assignment_actions paa
118 WHERE paa.payroll_action_id = p_max_action_id
119 AND paa.assignment_action_id = pai.action_context_id)
120 AND pai.action_information1 = p_challan
121 AND pay_in_24q_er_returns.get_format_value(NVL(pai.action_information5,'0')) <> '0.00'
122 -- AND fnd_date.canonical_to_date(pai.action_information4)<=fnd_date.CHARDATE_TO_DATE(SYSDATE)
123 ORDER BY action_information1, action_information2 ASC;
124
125 l_count NUMBER;
126 l_procedure varchar2(100);
127
128 BEGIN
129 g_debug := hr_utility.debug_enabled;
130 l_procedure := g_package ||'deductee_rec_count';
131 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
132
133 IF g_debug THEN
134 pay_in_utils.trace('**************************************************','********************');
135 pay_in_utils.trace('p_gre_org_id',p_gre_org_id);
136 pay_in_utils.trace('p_max_action_id',p_max_action_id);
137 pay_in_utils.trace('p_challan',p_challan);
138 pay_in_utils.trace('**************************************************','********************');
139 END IF;
140
141 OPEN c_count;
142 FETCH c_count INTO l_count;
143 IF c_count%NOTFOUND THEN
144 CLOSE c_count;
145 RETURN '0';
146 END IF;
147 CLOSE c_count;
148
149
150
151 IF g_debug THEN
152 pay_in_utils.trace('l_count',TO_CHAR(l_count));
153 END IF;
154
155 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
156
157 RETURN TO_CHAR(l_count);
158
159 END deductee_rec_count;
160
161
162 --------------------------------------------------------------------------
163 -- --
164 -- Name : GROSS_TOT_TDS_CHALLAN --
165 -- Type : FUNCTION --
166 -- Access : Public --
167 -- Description : This function returns the Gross Total of TDS --
168 -- deducted as per Challan details annexure --
169 -- Parameters : --
170 -- IN : p_gre_org_id VARCHAR2 --
171 -- p_assess_period VARCHAR2 --
172 -- p_max_action_id VARCHAR2 --
173 --------------------------------------------------------------------------
174 FUNCTION gross_tot_tds_challan(p_gre_org_id IN VARCHAR2
175 ,p_assess_period IN VARCHAR2
176 ,p_max_action_id IN VARCHAR2)
177 RETURN VARCHAR2 IS
178
179 CURSOR c_challan_tax_tot IS
180 SELECT SUM (TDS)
181 , SUM (SUR)
182 , SUM (EC)
183 , SUM (INTR)
184 , SUM (OTH)
185 FROM ( SELECT DISTINCT pai.action_information1
186 , NVL(pai.action_information6,0) TDS
187 , NVL(pai.action_information7,0) SUR
188 , NVL(pai.action_information8,0) EC
189 , NVL(pai.action_information9,0) INTR
190 , NVL(pai.action_information10,0) OTH
191 FROM pay_action_information pai
192 WHERE action_information_category = 'IN_24Q_CHALLAN'
193 AND action_context_type = 'PA'
194 AND action_information3 = p_gre_org_id
195 AND action_information2 = p_assess_period
196 AND pai.action_context_id= p_max_action_id
197 AND fnd_date.canonical_to_date(pai.action_information5)<=fnd_date.CHARDATE_TO_DATE(SYSDATE));
198
199 l_tot NUMBER:= 0;
200 l_tds NUMBER:= 0;
201 l_sur NUMBER:= 0;
202 l_ec NUMBER:= 0;
203 l_intr NUMBER:= 0;
204 l_oth NUMBER:= 0;
205 l_total VARCHAR2(20);
206 l_procedure varchar2(100);
207
208 BEGIN
209 g_debug := hr_utility.debug_enabled;
210 l_procedure := g_package ||'gross_tot_tds_challan';
211 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
212
213 IF g_debug THEN
214 pay_in_utils.trace('**************************************************','********************');
215 pay_in_utils.trace('p_gre_org_id',p_gre_org_id);
216 pay_in_utils.trace('p_assess_period',p_assess_period);
217 pay_in_utils.trace('p_max_action_id',p_max_action_id);
218 pay_in_utils.trace('**************************************************','********************');
219 END IF;
220
221 OPEN c_challan_tax_tot;
222 FETCH c_challan_tax_tot INTO l_tds,l_sur,l_ec,l_intr,l_oth;
223 CLOSE c_challan_tax_tot;
224
225 l_tot := l_tds + l_sur + l_ec + l_intr + l_oth;
226
227 l_tot :=ROUND(l_tot,0);
228 l_total :=TO_CHAR(NVL(l_tot,0))||'.00';
229
230
231
232 IF g_debug THEN
233 pay_in_utils.trace('l_total',SUBSTR(l_total,1,15));
234 END IF;
235
236 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
237
238 RETURN SUBSTR(l_total,1,15);
239
240 END gross_tot_tds_challan;
241
242 --------------------------------------------------------------------------
243 -- --
244 -- Name : GET_FORMAT_VALUE --
245 -- Type : FUNCTION --
246 -- Access : Public --
247 -- Description : This function returns value with precision --
248 -- of two decimal place --
249 -- --
250 -- Parameters : --
251 -- IN : p_value VARCHAR2 --
252 --------------------------------------------------------------------------
253 FUNCTION get_format_value(p_value IN VARCHAR2)
254 RETURN VARCHAR2 IS
255
256 l_value VARCHAR2(20);
257 l_value_temp VARCHAR2(20);
258 l_procedure varchar2(100);
259
260 BEGIN
261 g_debug := hr_utility.debug_enabled;
262 l_procedure := g_package ||'get_format_value';
263 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
264 IF g_debug THEN
265 pay_in_utils.trace('p_value',p_value);
266 END IF;
267
268 l_value_temp := trunc(fnd_number.canonical_to_number(p_value), 2);
269
270 IF(NVL(l_value_temp,0)=0) THEN
271 RETURN '0.00';
272 END IF;
273
274 l_value := trim(to_char(l_value_temp,'99999999999999990.99'));
275 --Added the above line and commented the below 2 lines for 14061882
276 /* l_value := (l_value_temp*100);
277 l_value := SUBSTR(l_value,1,length(l_value)-2)||'.'||SUBSTR(l_value,length(l_value)-1,length(l_value));
278 */
279
280
281
282
283 IF g_debug THEN
284 pay_in_utils.trace('l_value',l_value);
285 END IF;
286
287 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
288
289 RETURN l_value;
290
291 END get_format_value;
292
293
294 --------------------------------------------------------------------------
295 -- --
296 -- Name : GET_24Q_TAX_VALUES --
297 -- Type : FUNCTION --
298 -- Access : Public --
299 -- Description : This function returns the Gross Total of TDS --
300 -- deducted as per Deductee details annexure --
301 -- Parameters : --
302 -- IN : p_challan_number VARCHAR2 --
303 -- p_gre_org_id VARCHAR2 --
304 -- p_max_action_id VARCHAR2 --
305 --------------------------------------------------------------------------
306 FUNCTION get_24Q_tax_values(
307 p_challan_number IN VARCHAR2
308 ,p_gre_org_id IN VARCHAR2
309 ,p_max_action_id IN VARCHAR2
310 )
311 RETURN VARCHAR2 IS
312
313 CURSOR c_form24Q_tax_values IS
314 SELECT SUM(NVL(pai.action_information9,0)) -----total tax paid
315 ,SUM(NVL(pai.action_information6,0))
316 ,SUM(NVL(pai.action_information7,0))
317 ,SUM(NVL(pai.action_information8,0))
318 FROM pay_action_information pai
319 WHERE action_information_category ='IN_24Q_DEDUCTEE'
320 AND action_context_type = 'AAP'
321 AND action_information3 =p_gre_org_id
322 AND action_information1=p_challan_number
323 AND EXISTS ( SELECT 1
324 FROM pay_assignment_actions paa
325 WHERE paa.payroll_action_id = p_max_action_id
326 AND paa.assignment_action_id = pai.action_context_id)
327 -- AND fnd_date.canonical_to_date(pai.action_information4)<=fnd_date.CHARDATE_TO_DATE(SYSDATE)
328 ORDER BY action_information1, action_information2 ASC;
329
330
331 l_value29 VARCHAR2(20);
332 l_value30 VARCHAR2(20);
333 l_value31 VARCHAR2(20);
334 l_value32 VARCHAR2(20);
335 l_value33 VARCHAR2(20);
336 l_total_tax_values VARCHAR2(100);
337 l_procedure varchar2(100);
338
339 BEGIN
340 g_debug := hr_utility.debug_enabled;
341 l_procedure := g_package ||'gross_tot_tds_challan';
342 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
343
344 IF g_debug THEN
345 pay_in_utils.trace('**************************************************','********************');
346 pay_in_utils.trace('p_challan_number',p_challan_number);
347 pay_in_utils.trace('p_gre_org_id',p_gre_org_id);
348 pay_in_utils.trace('p_max_action_id',p_max_action_id);
349 pay_in_utils.trace('**************************************************','********************');
350 END IF;
351
352
353 l_value29 :=0;
354 l_value30 :=0;
355 l_value31 :=0;
356 l_value32 :=0;
357 l_value33 :=0;
358
359 OPEN c_form24Q_tax_values;
360 FETCH c_form24Q_tax_values INTO l_value29,l_value30,l_value31,l_value32;
361 CLOSE c_form24Q_tax_values;
362
363 l_value33 :=l_value30+l_value31+l_value32;
364
365 l_value29 :=get_format_value(l_value29);
366 l_value30 :=get_format_value(l_value30);
367 l_value31 :=get_format_value(l_value31);
368 l_value32 :=get_format_value(l_value32);
369 l_value33 :=get_format_value(l_value33);
370
371 l_total_tax_values := l_value29||'^'||l_value30||'^'||l_value31||'^'||l_value32||'^'||l_value33||'^';
372
373
374 IF g_debug THEN
375 pay_in_utils.trace('l_total_tax_values',l_total_tax_values);
376 END IF;
377
378 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
379
380 RETURN l_total_tax_values;
381
382 END get_24Q_tax_values;
383
384
385
386 --------------------------------------------------------------------------
387 -- --
388 -- Name : CHAPTER_VIA_REC_COUNT --
389 -- Type : FUNCTION --
390 -- Access : Public --
391 -- Description : This function returns the Total number of records --
392 -- in the Chapter-VIA Details of the Magtape --
393 -- Parameters : --
394 -- IN : p_action_context_id VARCHAR2 --
395 -- p_source_id VARCHAR2 --
396 --------------------------------------------------------------------------
397 FUNCTION chapter_VIA_rec_count (p_action_context_id IN VARCHAR2
398 ,p_source_id IN VARCHAR2)
399 RETURN VARCHAR2 IS
400
401 CURSOR c_count
402 IS
403 SELECT COUNT(*)
404 FROM pay_action_information
405 WHERE action_information_category = 'IN_24Q_VIA'
406 AND action_context_type = 'AAP'
407 AND action_context_id = p_action_context_id
408 AND source_id =p_source_id;
409
410 l_count NUMBER;
411 l_procedure varchar2(100);
412
413 BEGIN
414 g_debug := hr_utility.debug_enabled;
415 l_procedure := g_package ||'chapter_VIA_rec_count';
416 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
417
418 IF g_debug THEN
419 pay_in_utils.trace('**************************************************','********************');
420 pay_in_utils.trace('p_action_context_id',p_action_context_id);
421 pay_in_utils.trace('p_source_id',p_source_id);
422 pay_in_utils.trace('**************************************************','********************');
423 END IF;
424
425 OPEN c_count;
426 FETCH c_count INTO l_count;
427 IF c_count%NOTFOUND THEN
428 CLOSE c_count;
429 RETURN '0';
430 END IF;
431 CLOSE c_count;
432
433
434 IF g_debug THEN
435 pay_in_utils.trace('l_count',TO_CHAR(l_count));
436 END IF;
437
438 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
439
440 RETURN TO_CHAR(l_count);
441
442 END chapter_VIA_rec_count;
443
444
445
446 --------------------------------------------------------------------------
447 -- --
448 -- Name : SALARY_REC_COUNT --
449 -- Type : FUNCTION --
450 -- Access : Public --
451 -- Description : This function returns the Total number of records --
452 -- in the Salary Details of the Magtape --
453 -- Parameters : --
454 -- IN : p_gre_org_id VARCHAR2 --
455 -- p_assess_period VARCHAR2 --
456 --------------------------------------------------------------------------
457 FUNCTION salary_rec_count (p_gre_org_id IN VARCHAR2
458 ,p_assess_period IN VARCHAR2)
459 RETURN VARCHAR2 IS
460
461 CURSOR c_count
462 IS
463 SELECT COUNT(*)
464 FROM pay_action_information
465 WHERE action_information_category = 'IN_24Q_PERSON'
466 AND action_context_type = 'AAP'
467 AND action_information2 = p_assess_period
468 AND action_information3 = p_gre_org_id
469 AND action_context_id IN (SELECT MAX(pai.action_context_id)
470 FROM pay_action_information pai
471 ,pay_assignment_actions paa
472 ,per_assignments_f asg
473 WHERE pai.action_information_category = 'IN_24Q_PERSON'
474 AND pai.action_context_type = 'AAP'
475 AND pai.action_information1 = asg.person_id
476 AND pai.assignment_id = asg.assignment_id
477 AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
478 AND pai.action_information2 = p_assess_period
479 AND pai.action_information3 = p_gre_org_id
480 AND pai.source_id = paa.assignment_action_id
481 GROUP BY pai.assignment_id,pai.action_information1,pai.action_information9
482 );
483
484
485 l_count NUMBER;
486 l_procedure varchar2(100);
487
488 BEGIN
489 g_debug := hr_utility.debug_enabled;
490 l_procedure := g_package ||'salary_rec_count';
491 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
492
493 IF g_debug THEN
494 pay_in_utils.trace('**************************************************','********************');
495 pay_in_utils.trace('p_gre_org_id',p_gre_org_id);
496 pay_in_utils.trace('p_assess_period',p_assess_period);
497 pay_in_utils.trace('**************************************************','********************');
498 END IF;
499 OPEN c_count;
500 FETCH c_count INTO l_count;
501 IF c_count%NOTFOUND THEN
502 CLOSE c_count;
503 RETURN '0';
504 END IF;
505 CLOSE c_count;
506
507
508 IF g_debug THEN
509 pay_in_utils.trace('l_count',TO_CHAR(l_count));
510 END IF;
511
512 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
513
514 RETURN TO_CHAR(l_count);
515
516 END salary_rec_count;
517
518
519
520 --------------------------------------------------------------------------
521 -- --
522 -- Name : DEDUCTEE_SALARY_COUNT --
523 -- Type : FUNCTION --
524 -- Access : Public --
525 -- Description : This function returns the Total number of Salary --
526 -- Details records of a particular employee --
527 -- Parameters : --
528 -- IN : p_gre_org_id VARCHAR2 --
529 -- p_assess_period VARCHAR2 --
530 -- p_assignment_id VARCHAR2 --
531 --------------------------------------------------------------------------
532 FUNCTION deductee_salary_count (p_gre_org_id IN VARCHAR2
533 ,p_assess_year IN VARCHAR2
534 ,p_assignment_id IN VARCHAR2)
535 RETURN VARCHAR2 IS
536
537 CURSOR c_count
538 IS
539 SELECT COUNT(*)
540 FROM pay_action_information
541 WHERE action_information_category = 'IN_24Q_PERSON'
542 AND action_context_type = 'AAP'
543 AND SUBSTR(action_information2,1,9) = p_assess_year
544 AND action_information3 = p_gre_org_id
545 AND assignment_id = p_assignment_id
546 AND action_context_id IN ( SELECT MAX(action_context_id)
547 FROM pay_action_information pai
548 ,pay_assignment_actions paa
549 ,per_assignments_f asg
550 WHERE action_information_category = 'IN_24Q_PERSON'
551 AND action_context_type = 'AAP'
552 AND pai.action_information1 = asg.person_id
553 AND SUBSTR(pai.action_information2,1,9) = p_assess_year
554 AND pai.action_information3 = p_gre_org_id
555 AND asg.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
556 AND pai.source_id = paa.assignment_action_id
557 AND pai.assignment_id = asg.assignment_id
558 GROUP BY pai.assignment_id,action_information1,action_information9
559 );
560
561
562 l_count NUMBER;
563 l_procedure varchar2(100);
564
565 BEGIN
566 g_debug := hr_utility.debug_enabled;
567 l_procedure := g_package ||'deductee_salary_count';
568 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
569
570 IF g_debug THEN
571 pay_in_utils.trace('**************************************************','********************');
572 pay_in_utils.trace('p_gre_org_id',p_gre_org_id);
573 pay_in_utils.trace('p_assess_year',p_assess_year);
574 pay_in_utils.trace('p_assignment_id',p_assignment_id);
575 pay_in_utils.trace('**************************************************','********************');
576 END IF;
577
578 OPEN c_count;
579 FETCH c_count INTO l_count;
580 IF c_count%NOTFOUND THEN
581 CLOSE c_count;
582 RETURN '0';
583 END IF;
584 CLOSE c_count;
585
586
587
588 IF g_debug THEN
589 pay_in_utils.trace('l_count',TO_CHAR(l_count));
590 END IF;
591
592 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
593
594 RETURN TO_CHAR(l_count);
595
596 END deductee_salary_count;
597
598
599 --------------------------------------------------------------------------
600 -- --
601 -- Name : GET_24Q_VALUES --
602 -- Type : FUNCTION --
603 -- Access : Public --
604 -- Description : This function returns the values corresponding to --
605 -- the F16 Balances --
606 -- Parameters : --
607 -- IN : p_category VARCHAR2 --
608 -- p_component_name VARCHAR2 --
609 -- p_context_id NUMBER --
610 -- p_source_id NUMBER --
611 -- p_segment_num NUMBER --
612 --------------------------------------------------------------------------
613 FUNCTION get_24Q_values (p_category IN VARCHAR2
614 ,p_component_name IN VARCHAR2
615 ,p_context_id IN NUMBER
616 ,p_source_id IN NUMBER
617 ,p_segment_num IN NUMBER )
618 RETURN VARCHAR2 IS
619
620 -- Bug 10365553: Added round() to fetch values upto 2 decimal places
621 CURSOR c_form24Q_values IS
622 SELECT round(NVL(fnd_number.canonical_to_number(action_information2),0),2)
623 ,round(NVL(fnd_number.canonical_to_number(action_information3),0),2)
624 FROM pay_action_information
625 WHERE action_information_category = p_category
626 AND action_information1 = p_component_name
627 AND action_context_id = p_context_id
628 AND source_id = p_source_id;
629
630 l_value1 VARCHAR2(20);
631 l_value2 VARCHAR2(20);
632 l_procedure varchar2(100);
633
634 BEGIN
635 g_debug := hr_utility.debug_enabled;
636 l_procedure := g_package ||'get_24Q_values';
637 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
638
639 IF g_debug THEN
640 pay_in_utils.trace('**************************************************','********************');
641 pay_in_utils.trace('p_category',p_category);
642 pay_in_utils.trace('p_component_name',p_component_name);
643 pay_in_utils.trace('p_context_id',TO_CHAR(p_context_id));
644 pay_in_utils.trace('p_source_id',TO_CHAR(p_source_id));
645 pay_in_utils.trace('p_segment_num',TO_CHAR(p_segment_num));
646 pay_in_utils.trace('**************************************************','********************');
647 END IF;
648
649 OPEN c_form24Q_values;
650 FETCH c_form24Q_values INTO l_value1,l_value2;
651 IF c_form24Q_values%NOTFOUND THEN
652 CLOSE c_form24Q_values;
653 RETURN '0.00';
654 END IF;
655 CLOSE c_form24Q_values;
656
657 l_value2 := get_format_value(l_value2+l_value1);
658 l_value1 := get_format_value(l_value1);
659
660
661 pay_in_utils.set_location(g_debug,'l_value1 = : '||l_value1,20);
662 pay_in_utils.set_location(g_debug,'l_value2 = : '||l_value2,30);
663
664 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,40);
665
666 IF(p_segment_num=1) THEN
667 RETURN l_value1;
668 ELSIF(p_segment_num=2) THEN
669 RETURN l_value2;
670 END IF;
671
672
673 END get_24Q_values;
674
675
676
677 --------------------------------------------------------------------------
678 -- --
679 -- Name : GET_EMPLOYER_CLASS --
680 -- Type : FUNCTION --
681 -- Access : Public --
682 -- Description : This function returns the employer classfication --
683 -- --
684 -- Parameters : --
685 -- IN : p_gre_org_id VARCHAR2 --
686 --------------------------------------------------------------------------
687 FUNCTION get_emplr_class (p_gre_org_id IN VARCHAR2)
688 RETURN VARCHAR2 IS
689
690 CURSOR c_employer IS
691 SELECT org_information6
692 FROM hr_organization_information
693 WHERE org_information_context = 'PER_IN_INCOME_TAX_DF'
694 AND organization_id = p_gre_org_id;
695
696 l_emplr_class VARCHAR2(150);
697 l_procedure varchar2(100);
698
699 BEGIN
700 g_debug := hr_utility.debug_enabled;
701 l_procedure := g_package ||'get_emplr_class';
702 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
703
704 IF g_debug THEN
705 pay_in_utils.trace('p_gre_org_id',p_gre_org_id);
706 END IF;
707
708 OPEN c_employer;
709 FETCH c_employer INTO l_emplr_class;
710 IF c_employer%NOTFOUND THEN
711 CLOSE c_employer;
712 RETURN 'XYZ';
713 END IF;
714 CLOSE c_employer;
715
716
717 IF g_debug THEN
718 pay_in_utils.trace('l_emplr_class',l_emplr_class);
719 END IF;
720
721 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
722
723 RETURN l_emplr_class;
724
725 END get_emplr_class;
726
727
728 --------------------------------------------------------------------------
729 -- --
730 -- Name : TOTAL_GROSS_TOT_INCOME --
731 -- Type : FUNCTION --
732 -- Access : Public --
733 -- Description : This function returns the total of Gross Total --
734 -- Income as per salary details annexure --
735 -- Parameters : --
736 -- IN : p_gre_org_id VARCHAR2 --
737 -- p_assess_period VARCHAR2 --
738 --------------------------------------------------------------------------
739 FUNCTION total_gross_tot_income (p_gre_org_id IN VARCHAR2
740 ,p_assess_period IN VARCHAR2
741 )
742 RETURN VARCHAR2 IS
743
744 CURSOR csr_income_details(p_balance VARCHAR2,p_action_context_id NUMBER,p_source_id IN NUMBER)
745 IS
746 SELECT NVL(SUM(fnd_number.canonical_to_number(action_information2)),0)
747 FROM pay_action_information
748 WHERE action_information_category = 'IN_24Q_SALARY'
749 AND action_context_type = 'AAP'
750 AND action_information1 = p_balance
751 AND action_context_id = p_action_context_id
752 AND source_id = p_source_id;
753
754 CURSOR csr_get_max_cont_id IS
755 SELECT MAX(pai.action_context_id) action_cont_id
756 ,source_id sour_id
757 FROM pay_action_information pai
758 ,pay_assignment_actions paa
759 ,per_assignments_f asg
760 WHERE pai.action_information_category = 'IN_24Q_PERSON'
761 AND pai.action_information3 = p_gre_org_id
762 AND pai.action_information2 = p_assess_period
763 AND pai.action_information1 = asg.person_id
764 AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
765 AND asg.assignment_id = pai.assignment_id
766 AND pai.action_context_type = 'AAP'
767 AND pai.source_id = paa.assignment_action_id
768 GROUP BY pai.action_information1,pai.action_information9,source_id;
769
770
771 l_total_gross NUMBER:=0;
772 l_value NUMBER:=0;
773 l_total_value VARCHAR2(20);
774 l_procedure varchar2(100);
775
776 BEGIN
777 g_debug := hr_utility.debug_enabled;
778 l_procedure := g_package ||'total_gross_tot_income';
779 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
780
781 IF g_debug THEN
782 pay_in_utils.trace('**************************************************','********************');
783 pay_in_utils.trace('p_gre_org_id',p_gre_org_id);
784 pay_in_utils.trace('p_assess_period',p_assess_period);
785 pay_in_utils.trace('**************************************************','********************');
786 END IF;
787 FOR i IN csr_get_max_cont_id
788 LOOP
789 OPEN csr_income_details('F16 Gross Total Income',i.action_cont_id,i.sour_id);
790 FETCH csr_income_details INTO l_value;
791 CLOSE csr_income_details;
792 l_total_gross:= l_total_gross + l_value;
793 END LOOP;
794
795 l_total_value :=get_format_value(l_total_gross);
796
797 IF g_debug THEN
798 pay_in_utils.trace('l_total_value',SUBSTR(l_total_value,1,15));
799 END IF;
800
801 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
802
803 RETURN SUBSTR(l_total_value,1,15);
804
805 END total_gross_tot_income;
806
807 --------------------------------------------------------------------------
808 -- --
809 -- Name : GET_LOCATION_DETAILS --
810 -- Type : FUNCTION --
811 -- Access : Public --
812 -- Description : This function gets the gre location details --
813 -- --
814 -- Parameters : --
815 -- IN : p_location_id hr_locations.location_id --
816 -- : p_concatenate VARCHAR2 --
817 -- p_field VARCHAR2 --
818 --------------------------------------------------------------------------
819 FUNCTION get_location_details ( p_location_id IN hr_locations.location_id%TYPE
820 ,p_rep_email_id IN VARCHAR2
821 ,p_rep_phone IN VARCHAR2
822 ,p_segment_num IN NUMBER
823 ,p_person_type IN VARCHAR2)
824 RETURN VARCHAR2
825 IS
826
827 CURSOR csr_add IS
828 SELECT address_line_1,
829 address_line_2,
830 address_line_3,
831 loc_information14,
832 loc_information15,
833 NVL(hr_general.decode_lookup('IN_STATE_CODES',loc_information16),'^'),
834 NVL(postal_code,'^'),
835 NVL(LOC_INFORMATION17,'^'),
836 NVL(TELEPHONE_NUMBER_1,'^')
837 FROM hr_locations
838 WHERE location_id = p_location_id;
839
840 l_add_1 hr_locations.address_line_1%TYPE;
841 l_add_2 hr_locations.address_line_2%TYPE;
842 l_add_3 hr_locations.address_line_3%TYPE;
843 l_add_4 hr_locations.loc_information14%TYPE;
844 l_add_5 hr_locations.loc_information15%TYPE;
845 l_email hr_locations.loc_information17%TYPE;
846 l_phone hr_locations.telephone_number_1%TYPE;
847 l_mobile hr_locations.telephone_number_1%TYPE; -- Bug 12332524
848 l_state hr_lookups.meaning%TYPE;
849 l_pin hr_locations.postal_code%TYPE;
850 l_std hr_locations.TELEPHONE_NUMBER_1%TYPE;
851 l_telph hr_locations.TELEPHONE_NUMBER_1%TYPE;
852 l_mob hr_locations.TELEPHONE_NUMBER_1%TYPE; -- Bug 12332524
853 l_remark VARCHAR2(75);
854 p_address1 VARCHAR2(60);
855 p_address2 VARCHAR2(60);
856 p_address3 VARCHAR2(60);
857 p_address4 VARCHAR2(80);
858 p_address5 VARCHAR2(60);
859 p_address6 VARCHAR2(60); -- Bug 12332524
860 l_procedure varchar2(100);
861
862 BEGIN
863 g_debug := hr_utility.debug_enabled;
864 l_procedure := g_package ||'get_location_details';
865 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
866
867 IF g_debug THEN
868 pay_in_utils.trace('**************************************************','********************');
869 pay_in_utils.trace('p_location_id ',p_location_id );
870 pay_in_utils.trace('p_rep_email_id',p_rep_email_id);
871 pay_in_utils.trace('p_rep_phone ',p_rep_phone );
872 pay_in_utils.trace('p_segment_num ',to_char(p_segment_num) );
873 pay_in_utils.trace('p_person_type ',p_person_type );
874 pay_in_utils.trace('**************************************************','********************');
875 END IF;
876
877
878 OPEN csr_add;
879 FETCH csr_add INTO l_add_1, l_add_2, l_add_3, l_add_4, l_add_5, l_state, l_pin, l_email, l_phone;
880 IF csr_add%NOTFOUND THEN
881 CLOSE csr_add;
882 IF(p_segment_num=5 AND p_person_type='REP') THEN
883
884 IF g_debug THEN
885 pay_in_utils.trace('3',RPAD('^',3,'^'));
886 END IF;
887
888 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
889
890 RETURN RPAD('^',3,'^');
891 ELSE
892 IF g_debug THEN
893 pay_in_utils.trace('2',RPAD('^',2,'^'));
894 END IF;
895 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,30);
896 RETURN RPAD('^',2,'^');
897 END IF;
898 END IF;
899 CLOSE csr_add;
900
901 l_std :='^';
902 l_telph :='^';
903 l_remark:='^';
904 l_mob :=''; -- Bug 12332524
905 l_mobile := '^'; -- Bug 12332524
906
907 -----------Address1-------------
908
909 IF LENGTH(l_add_1)<=25 THEN
910 p_address1 := p_address1||l_add_1;
911 ELSE
912 p_address1 :=p_address1||SUBSTR(l_add_1,1,25);
913 l_add_2 := SUBSTR(l_add_1,26)||', '||l_add_2;
914 END IF;
915 p_address1 := p_address1||'^';
916
917 IF l_add_2 IS NOT NULL THEN
918 IF LENGTH(l_add_2) <=25 THEN
919 p_address1 := p_address1||l_add_2||'^';
920 ELSE
921 p_address1 := p_address1||SUBSTR(l_add_2,1,25)||'^';
922 l_add_3 := SUBSTR(l_add_2,26)||', '||l_add_3;
923 END IF ;
924 ELSE
925 p_address1 := p_address1||'^';
926 END IF;
927
928 IF (p_segment_num=1) THEN
929 IF g_debug THEN
930 pay_in_utils.trace('p_address1',p_address1);
931 END IF;
932
933 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,40);
934
935 RETURN p_address1;
936 END IF;
937
938 ---------Address2-------------------------
939
940 IF l_add_3 IS NOT NULL THEN
941 IF LENGTH(l_add_3) <=25 THEN
942 p_address2 := p_address2||l_add_3||'^';
943 ELSE
944 p_address2 := p_address2||SUBSTR(l_add_3,1,25)||'^';
945 l_add_4 := SUBSTR(l_add_3,26)||', '||l_add_4;
946 END IF ;
947 ELSE
948 p_address2 := p_address2||'^';
949 END IF;
950
951
952 IF l_add_4 IS NOT NULL THEN
953 IF LENGTH(l_add_4) <=25 THEN
954 p_address2 := p_address2||l_add_4||'^';
955 ELSE
956 p_address2 := p_address2||SUBSTR(l_add_4,1,25)||'^';
957 l_add_5 := SUBSTR(l_add_4,26)||', '||l_add_5;
958 END IF ;
959 ELSE
960 p_address2 := p_address2||'^';
961 END IF;
962
963 IF (p_segment_num=2) THEN
964 IF g_debug THEN
965 pay_in_utils.trace('p_address2',p_address2);
966 END IF;
967 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,50);
968 RETURN p_address2;
969 END IF ;
970
971 ---------Address3-------------------------
972
973 IF LENGTH(l_add_5) <=25 THEN
974 p_address3 := p_address3||l_add_5;
975 ELSE
976 p_address3 := p_address3||SUBSTR(l_add_5,1,25);
977 END IF ;
978 p_address3 := p_address3||'^';
979
980 IF(l_state<>'^' ) THEN
981 l_state:=l_state||'^';
982 END IF ;
983
984 IF(l_pin <>'^' ) THEN
985 l_pin:=l_pin||'^';
986 END IF;
987 p_address3 :=p_address3||l_state||l_pin;
988
989 IF (p_segment_num=3) THEN
990 IF g_debug THEN
991 pay_in_utils.trace('p_address3',p_address3);
992 END IF;
993
994 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,60);
995
996 RETURN p_address3;
997 END IF ;
998
999 ---------Address4-------------------------
1000
1001 IF(p_person_type='REP') THEN
1002 l_email :=NVL(p_rep_email_id,'^');
1003 l_phone :=NVL(p_rep_phone,'^');
1004 END IF;
1005
1006 IF (l_email<>'^') THEN
1007 l_email :=SUBSTR (l_email,1,74)||'^';
1008 END IF ;
1009 p_address4 :=l_email;
1010
1011 IF (p_segment_num=4) THEN
1012 IF g_debug THEN
1013 pay_in_utils.trace('p_address4',p_address4);
1014 END IF;
1015 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,70);
1016 RETURN p_address4;
1017 END IF ;
1018
1019 ---------Address5-------------------------
1020
1021 IF (l_phone <>'^') THEN
1022 SELECT SUBSTR (l_phone,INSTR(l_phone,'-',1,1)+1,INSTR(l_phone,'-',1,2)-INSTR (l_phone,'-',1,1)-1) STD
1023 ,SUBSTR (l_phone,INSTR(l_phone,'-',1,2)+1) TELPH
1024 INTO l_std,l_telph
1025 FROM dual;
1026 l_std :=SUBSTR(l_std,1,5)||'^';
1027 l_telph := SUBSTR(l_telph,1,10)||'^';
1028 END IF;
1029
1030 IF(p_person_type='EMP') THEN
1031 p_address5:=l_std||l_telph;
1032 ELSE
1033 p_address5:=l_remark||l_std||l_telph;
1034 END IF;
1035
1036 IF (p_segment_num=5) THEN
1037 IF g_debug THEN
1038 pay_in_utils.trace('p_address5',p_address5);
1039 END IF;
1040 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,80);
1041 RETURN p_address5;
1042 END IF;
1043
1044 --------------------------------------------
1045 -- Bug 12332524: Rep Mobile Number
1046
1047 IF(p_person_type='REP') THEN
1048 l_mobile :=NVL(p_rep_phone,'^');
1049 END IF;
1050
1051 IF (l_mobile <>'^') THEN
1052 l_mob := SUBSTR(l_mobile,1,10);
1053 END IF;
1054
1055 p_address6 := l_mob;
1056
1057 IF (p_segment_num=6) THEN
1058 IF g_debug THEN
1059 pay_in_utils.trace('p_address6',p_address6);
1060 END IF;
1061 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,85);
1062 RETURN p_address6;
1063 END IF;
1064
1065 END get_location_details;
1066
1067 --------------------------------------------------------------------------
1068 -- --
1069 -- Name : GET_EMP_CATEGORY --
1070 -- Type : FUNCTION --
1071 -- Access : Public --
1072 -- Description : This function gets the employee category --
1073 -- --
1074 -- Parameters : --
1075 -- IN : p_person_id VARCHAR2 --
1076 -- : --
1077 -- Returns : l_emp_category --
1078 --------------------------------------------------------------------------
1079 FUNCTION get_emp_category(p_person_id IN VARCHAR2)
1080 RETURN VARCHAR2 IS
1081 CURSOR csr_person_category
1082 IS
1083 SELECT sex
1084 ,TO_CHAR(MONTHS_BETWEEN(SYSDATE, date_of_birth)) l_age
1085 FROM per_all_people_f
1086 WHERE person_id = p_person_id
1087 AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
1088
1089 l_procedure VARCHAR2(100);
1090 l_sex VARCHAR2(500);
1091 l_age VARCHAR2(100);
1092 l_emp_category VARCHAR2(100);
1093
1094 BEGIN
1095 g_debug := hr_utility.debug_enabled;
1096 l_procedure := g_package ||'get_emp_category';
1097 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1098
1099 IF g_debug THEN
1100 pay_in_utils.trace('**************************************************','********************');
1101 pay_in_utils.trace('p_person_id ',p_person_id );
1102 pay_in_utils.trace('**************************************************','********************');
1103 END IF;
1104
1105 OPEN csr_person_category;
1106 FETCH csr_person_category INTO l_sex, l_age;
1107 CLOSE csr_person_category;
1108
1109 IF (l_sex = 'F') THEN
1110 l_emp_category := 'W';
1111 ELSIF (l_sex = 'M' AND l_age >= 780) THEN
1112 l_emp_category := 'S';
1113 ELSE
1114 l_emp_category := 'G';
1115 END IF;
1116
1117 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
1118
1119 RETURN l_emp_category;
1120
1121 IF g_debug THEN
1122 pay_in_utils.trace('**************************************************','********************');
1123 pay_in_utils.trace('l_emp_category ',l_emp_category );
1124 pay_in_utils.trace('**************************************************','********************');
1125 END IF;
1126
1127 END get_emp_category;
1128
1129 END pay_in_24q_er_returns;