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