[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_24QC_ER_RETURNS
Source
1 PACKAGE BODY pay_in_24qc_er_returns AS
2 /* $Header: pyin24cr.pkb 120.3.12010000.1 2008/07/27 22:52:05 appldev ship $ */
3
4 g_debug BOOLEAN;
8 -- --
5 g_package CONSTANT VARCHAR2(40) := 'pay_in_24qc_er_returns.';
6
7 --------------------------------------------------------------------------
9 -- Name : GET_FILE_SEQ_NO --
10 -- Type : FUNCTION --
11 -- Access : Public --
12 -- Description : This function returns the file sequence no of the --
13 -- Correction Report --
14 -- Parameters : --
15 -- IN : p_gre_org_id VARCHAR2 --
16 -- p_assess_period VARCHAR2 --
17 -- p_quarter VARCHAR2 --
18 --------------------------------------------------------------------------
19 FUNCTION get_file_seq_no (p_gre_org_id IN VARCHAR2
20 ,p_assess_year IN VARCHAR2
21 ,p_quarter IN VARCHAR2
22 )
23 RETURN VARCHAR2 IS
24
25 CURSOR csr_file_seq_no(p_gre_org_id VARCHAR2
26 ,p_assess_year VARCHAR2
27 ,p_quarter VARCHAR2)
28 IS
29 SELECT TO_CHAR(COUNT(*) + 1)
30 FROM hr_organization_information
31 WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
32 AND org_information1 = p_assess_year
33 AND org_information2 = p_quarter
34 AND org_information6 = 'C'
35 AND organization_id = p_gre_org_id;
36
37 l_file_seq_no VARCHAR2(10);
38 l_proc VARCHAR2(100);
39 l_message VARCHAR2(240);
40 BEGIN
41
42 l_proc := g_package||'get_file_seq_no';
43 g_debug := hr_utility.debug_enabled;
44 pay_in_utils.set_location(g_debug, 'Entering: ' || l_proc, 10);
45
46 IF g_debug THEN
47 pay_in_utils.trace('**************************************************','********************');
48 pay_in_utils.trace('p_gre_org_id',p_gre_org_id);
49 pay_in_utils.trace('p_assess_year',p_assess_year);
50 pay_in_utils.trace('p_quarter',p_quarter);
51 pay_in_utils.trace('**************************************************','********************');
52 END IF;
53
54 OPEN csr_file_seq_no(p_gre_org_id,p_assess_year,p_quarter);
55 FETCH csr_file_seq_no INTO l_file_seq_no;
56 CLOSE csr_file_seq_no;
57
58 IF g_debug THEN
59 pay_in_utils.trace('**************************************************','********************');
60 pay_in_utils.trace('l_file_seq_no',l_file_seq_no);
61 pay_in_utils.trace('**************************************************','********************');
62 END IF;
63
64 pay_in_utils.set_location(g_debug, 'Leaving: ' || l_proc, 10);
65
66 RETURN l_file_seq_no;
67
68 EXCEPTION
69 WHEN OTHERS THEN
70 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_proc, 'SQLERRMC:'||sqlerrm);
71 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc, 50);
72 pay_in_utils.trace(l_message,l_proc);
73
74 END get_file_seq_no;
75
76
77 --------------------------------------------------------------------------
78 -- --
79 -- Name : CHALLAN_REC_COUNT_24QC --
80 -- Type : FUNCTION --
81 -- Access : Public --
82 -- Description : This function returns the Total number of challan --
83 -- records for a particular correction type --
84 -- Parameters : --
85 -- IN : p_gre_org_id VARCHAR2 --
86 -- p_assess_period VARCHAR2 --
87 -- p_max_action_id VARCHAR2 --
88 -- p_correction_type VARCHAR2 --
89 --------------------------------------------------------------------------
90 FUNCTION challan_rec_count_24qc (p_gre_org_id IN VARCHAR2
91 ,p_assess_period IN VARCHAR2
92 ,p_max_action_id IN VARCHAR2
93 ,p_correction_type IN VARCHAR2)
94 RETURN VARCHAR2 IS
95
96 CURSOR c_count
97 IS
98 SELECT COUNT(DISTINCT action_information1)
99 FROM pay_action_information pai
100 WHERE action_information_category = 'IN_24QC_CHALLAN'
101 AND action_context_type = 'PA'
102 AND action_information3 = p_gre_org_id
103 AND action_information2 = p_assess_period
104 AND pai.action_context_id= p_max_action_id
105 AND fnd_date.canonical_to_date(pai.action_information5) <= fnd_date.CHARDATE_TO_DATE(SYSDATE)
106 AND action_information29 LIKE '%' || p_correction_type || '%';
107
108 l_count NUMBER;
109 l_proc VARCHAR2(100);
110 l_message VARCHAR2(240);
111
112 BEGIN
113
114 l_proc := g_package||'challan_rec_count_24qc';
115 g_debug := hr_utility.debug_enabled;
116 pay_in_utils.set_location(g_debug, 'Entering: ' || l_proc, 10);
117 IF g_debug THEN
118 pay_in_utils.trace('**************************************************','********************');
119 pay_in_utils.trace('p_gre_org_id',p_gre_org_id);
120 pay_in_utils.trace('p_assess_period',p_assess_period);
121 pay_in_utils.trace('p_max_action_id',p_max_action_id);
122 pay_in_utils.trace('p_correction_type',p_correction_type);
126 OPEN c_count;
123 pay_in_utils.trace('**************************************************','********************');
124 END IF;
125
127 FETCH c_count INTO l_count;
128 IF c_count%NOTFOUND THEN
129 CLOSE c_count;
130 RETURN '0';
131 END IF;
132 CLOSE c_count;
133
134 IF g_debug THEN
135 pay_in_utils.trace('**************************************************','********************');
136 pay_in_utils.trace('l_count',l_count);
137 pay_in_utils.trace('**************************************************','********************');
138 END IF;
139
140 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc, 30);
141
142 RETURN TO_CHAR(l_count);
143
144 EXCEPTION
145 WHEN OTHERS THEN
146 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_proc, 'SQLERRMC:'||sqlerrm);
147 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc, 50);
148 pay_in_utils.trace(l_message,l_proc);
149
150 END challan_rec_count_24qc;
151
152 --------------------------------------------------------------------------
153 -- --
154 -- Name : GET_RRR_NO --
155 -- Type : FUNCTION --
156 -- Access : Public --
157 -- Description : This function returns the Original/Last --
158 -- 24Q Receipt Number --
159 -- Parameters : --
160 -- IN : p_gre_org_id VARCHAR2 --
161 -- p_assess_period VARCHAR2 --
162 -- p_quarter VARCHAR2 --
163 -- p_correction_type VARCHAR2 --
164 -- p_receipt VARCHAR2 --
165 --------------------------------------------------------------------------
166 FUNCTION get_rrr_no (p_gre_org_id IN VARCHAR2
167 ,p_assess_year IN VARCHAR2
168 ,p_quarter IN VARCHAR2
169 ,p_receipt IN VARCHAR2)
170 RETURN VARCHAR2 IS
171
172 CURSOR csr_orig_rrr_no(p_gre_org_id IN VARCHAR2
173 ,p_assess_year IN VARCHAR2
174 ,p_quarter IN VARCHAR2)
175 IS
176 SELECT org_information4
177 ,org_information3
178 FROM hr_organization_information
179 WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
180 AND org_information1 = p_assess_year
181 AND org_information2 = p_quarter
182 AND org_information6 ='O'
183 AND organization_id = p_gre_org_id
184 ORDER BY org_information3 ASC;
185
186 CURSOR csr_prev_rrr_no(p_gre_org_id IN VARCHAR2
187 ,p_assess_year IN VARCHAR2
188 ,p_quarter IN VARCHAR2)
189 IS
190 SELECT org_information4
191 ,org_information3
192 FROM hr_organization_information
193 WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
194 AND org_information1 = p_assess_year
195 AND org_information2 = p_quarter
196 AND organization_id = p_gre_org_id
197 ORDER BY org_information3 DESC;
198
199 l_rrr_no VARCHAR2(15);
200 l_dummy VARCHAR2(15);
201 l_proc VARCHAR2(100);
202 l_message VARCHAR2(240);
203
204 BEGIN
205
206 l_proc := g_package||'get_rrr_no';
207 g_debug := hr_utility.debug_enabled;
208 pay_in_utils.set_location(g_debug, 'Entering: ' || l_proc, 10);
209
210 IF g_debug THEN
211 pay_in_utils.trace('**************************************************','********************');
212 pay_in_utils.trace('p_gre_org_id',p_gre_org_id);
213 pay_in_utils.trace('p_assess_year',p_assess_year);
214 pay_in_utils.trace('p_quarter',p_quarter);
215 pay_in_utils.trace('**************************************************','********************');
216 END IF;
217
218 IF p_receipt = 'Original' THEN
219 OPEN csr_orig_rrr_no(p_gre_org_id,p_assess_year,p_quarter);
220 FETCH csr_orig_rrr_no INTO l_rrr_no, l_dummy;
221 CLOSE csr_orig_rrr_no;
222 END IF;
223
224 IF p_receipt = 'Previous' THEN
225 OPEN csr_prev_rrr_no(p_gre_org_id,p_assess_year,p_quarter);
226 FETCH csr_prev_rrr_no INTO l_rrr_no, l_dummy;
227 CLOSE csr_prev_rrr_no;
228 END IF;
229
230 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc, 30);
231
232 IF g_debug THEN
233 pay_in_utils.trace('**************************************************','********************');
234 pay_in_utils.trace('l_rrr_no',l_rrr_no);
235 pay_in_utils.trace('**************************************************','********************');
236 END IF;
237 RETURN NVL(l_rrr_no,'Not Found');
238
239 EXCEPTION
240 WHEN OTHERS THEN
241 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_proc, 'SQLERRMC:'||sqlerrm);
242 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc, 50);
243 pay_in_utils.trace(l_message,l_proc);
244
245 END get_rrr_no;
246
247 --------------------------------------------------------------------------
248 -- --
249 -- Name : GROSS_TOT_TDS_CHALLAN_24Q --
250 -- Type : FUNCTION --
251 -- Access : Public --
255 -- IN : p_gre_org_id VARCHAR2 --
252 -- Description : This function returns the Gross Total of TDS --
253 -- --
254 -- Parameters : --
256 -- p_assess_period VARCHAR2 --
257 -- p_max_action_id VARCHAR2 --
258 -- p_correction_type VARCHAR2 --
259 --------------------------------------------------------------------------
260 FUNCTION gross_tot_tds_challan_24q(p_gre_org_id IN VARCHAR2
261 ,p_assess_period IN VARCHAR2
262 ,p_max_action_id IN VARCHAR2
263 ,p_correction_type IN VARCHAR2)
264 RETURN VARCHAR2 IS
265
266 CURSOR c_challan_tax_tot IS
267 SELECT SUM (TDS)
268 , SUM (SUR)
269 , SUM (EC)
270 , SUM (INTR)
271 , SUM (OTH)
272 FROM ( SELECT DISTINCT pai.action_information1
273 , NVL(pai.action_information6,0) TDS
274 , NVL(pai.action_information7,0) SUR
275 , NVL(pai.action_information8,0) EC
276 , NVL(pai.action_information9,0) INTR
277 , NVL(pai.action_information10,0) OTH
278 FROM pay_action_information pai
279 WHERE action_information_category = 'IN_24QC_CHALLAN'
280 AND action_context_type = 'PA'
281 AND action_information3 = p_gre_org_id
282 AND action_information2 = p_assess_period
283 AND pai.action_context_id = p_max_action_id
284 AND NVL(pai.action_information18, 'NC') <> 'NC'
285 AND pai.action_information29 like '%'||p_correction_type||'%'
286 AND fnd_date.canonical_to_date(pai.action_information5) <= fnd_date.CHARDATE_TO_DATE(SYSDATE)
287 );
288
289 l_tds NUMBER := 0;
290 l_sur NUMBER := 0;
291 l_ec NUMBER := 0;
292 l_intr NUMBER := 0;
293 l_oth NUMBER := 0;
294 l_total VARCHAR2(20);
295 l_proc VARCHAR2(100);
296 l_message VARCHAR2(240);
297
298 BEGIN
299
300 l_proc := g_package||'gross_tot_tds_challan_24q';
301 g_debug := hr_utility.debug_enabled;
302 pay_in_utils.set_location(g_debug, 'Entering: ' || l_proc, 10);
303
304 IF g_debug THEN
305 pay_in_utils.trace('**************************************************','********************');
306 pay_in_utils.trace('p_gre_org_id',p_gre_org_id);
307 pay_in_utils.trace('p_assess_period',p_assess_period);
308 pay_in_utils.trace('p_max_action_id',p_max_action_id);
309 pay_in_utils.trace('p_correction_type',p_correction_type);
310 pay_in_utils.trace('**************************************************','********************');
311 END IF;
312
313 OPEN c_challan_tax_tot;
314 FETCH c_challan_tax_tot INTO l_tds, l_sur, l_ec, l_intr, l_oth;
315 CLOSE c_challan_tax_tot;
316
317 l_total := TO_CHAR(NVL(ROUND((l_tds + l_sur + l_ec + l_intr + l_oth), 0), 0))||'.00';
318
319 IF g_debug THEN
320 pay_in_utils.trace('**************************************************','********************');
321 pay_in_utils.trace('l_total',l_total);
322 pay_in_utils.trace('**************************************************','********************');
323 END IF;
324
325 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc, 30);
326
327 RETURN SUBSTR(l_total,1,15);
328
329 EXCEPTION
330 WHEN OTHERS THEN
331 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_proc, 'SQLERRMC:'||sqlerrm);
332 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc, 50);
333 pay_in_utils.trace(l_message,l_proc);
334
335 END gross_tot_tds_challan_24q;
336
337
338 --------------------------------------------------------------------------
339 -- --
340 -- Name : GET_PREV_NIL_CHALLAN_IND --
341 -- Type : FUNCTION --
342 -- Access : Public --
343 -- Description : This function returns the last NIL Challan Indicator--
344 -- Parameters : --
345 -- IN : p_gre_org_id VARCHAR2 --
346 -- p_assess_period VARCHAR2 --
347 -- p_max_action_id VARCHAR2 --
348 -- p_nil_challan VARCHAR2 --
349 --------------------------------------------------------------------------
350 FUNCTION get_prev_nil_challan_ind (p_gre_org_id IN VARCHAR2
351 ,p_assess_period IN VARCHAR2
352 ,p_max_action_id IN VARCHAR2)
353 RETURN VARCHAR2 IS
354
355 CURSOR c_nil_challan
356 IS
357 SELECT action_information25
358 FROM pay_action_information pai
359 WHERE action_information_category = 'IN_24QC_ORG'
360 AND action_context_type = 'PA'
361 AND pai.action_information1 = p_gre_org_id
362 AND pai.action_information3 = p_assess_period
363 AND pai.action_context_id = p_max_action_id;
364
365 l_nil_challan_ind VARCHAR2(10);
366 l_proc VARCHAR2(100);
367 l_message VARCHAR2(240);
368
369 BEGIN
370
371 l_proc := g_package||'get_prev_nil_challan_ind';
372 g_debug := hr_utility.debug_enabled;
373 pay_in_utils.set_location(g_debug, 'Entering: ' || l_proc, 10);
377 pay_in_utils.trace('p_gre_org_id',p_gre_org_id);
374
375 IF g_debug THEN
376 pay_in_utils.trace('**************************************************','********************');
378 pay_in_utils.trace('p_assess_period',p_assess_period);
379 pay_in_utils.trace('p_max_action_id',p_max_action_id);
380 pay_in_utils.trace('**************************************************','********************');
381 END IF;
382
383 OPEN c_nil_challan;
384 FETCH c_nil_challan INTO l_nil_challan_ind;
385 CLOSE c_nil_challan;
386
387 IF g_debug THEN
388 pay_in_utils.trace('**************************************************','********************');
389 pay_in_utils.trace('l_nil_challan_ind',l_nil_challan_ind);
390 pay_in_utils.trace('**************************************************','********************');
391 END IF;
392
393 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc, 30);
394
395 RETURN l_nil_challan_ind;
396
397 EXCEPTION
398 WHEN OTHERS THEN
399 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_proc, 'SQLERRMC:'||sqlerrm);
400 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc, 50);
401 pay_in_utils.trace(l_message,l_proc);
402
403 END get_prev_nil_challan_ind;
404
405 --------------------------------------------------------------------------
406 -- --
407 -- Name : DEDUCTEE_REC_COUNT_24Q --
408 -- Type : FUNCTION --
409 -- Access : Public --
410 -- Description : This function returns the count of deductee records --
411 -- for a challan in a correction archival --
412 -- Parameters : --
413 -- IN : p_gre_org_id VARCHAR2 --
414 -- p_max_action_id VARCHAR2 --
415 -- p_challan VARCHAR2 --
416 --------------------------------------------------------------------------
417 FUNCTION deductee_rec_count_24q (p_gre_org_id IN VARCHAR2
418 ,p_max_action_id IN VARCHAR2
419 ,p_challan IN VARCHAR2)
420 RETURN VARCHAR2 IS
421
422 CURSOR c_count
423 IS
424 SELECT COUNT(DISTINCT source_id)
425 FROM pay_action_information pai
426 WHERE action_information_category = 'IN_24QC_DEDUCTEE'
427 AND action_context_type = 'AAP'
428 AND action_information3 = p_gre_org_id
429 AND EXISTS (SELECT 1
430 FROM pay_assignment_actions paa
431 WHERE paa.payroll_action_id = p_max_action_id
432 AND paa.assignment_action_id = pai.action_context_id)
433 AND pai.action_information1 = p_challan;
434
435 l_count NUMBER;
436 l_proc VARCHAR2(100);
437 l_message VARCHAR2(240);
438
439 BEGIN
440
441 l_proc := g_package||'deductee_rec_count_24q';
442 g_debug := hr_utility.debug_enabled;
443 pay_in_utils.set_location(g_debug, 'Entering: ' || l_proc, 10);
444
445 IF g_debug THEN
446 pay_in_utils.trace('**************************************************','********************');
447 pay_in_utils.trace('p_gre_org_id',p_gre_org_id);
448 pay_in_utils.trace('p_max_action_id',p_max_action_id);
449 pay_in_utils.trace('p_challan',p_challan);
450 pay_in_utils.trace('**************************************************','********************');
451 END IF;
452
453 OPEN c_count;
454 FETCH c_count INTO l_count;
455 IF c_count%NOTFOUND THEN
456 CLOSE c_count;
457 RETURN '0';
458 END IF;
459 CLOSE c_count;
460
461 IF g_debug THEN
462 pay_in_utils.trace('**************************************************','********************');
463 pay_in_utils.trace('l_count',l_count);
464 pay_in_utils.trace('**************************************************','********************');
465 END IF;
466
467 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc, 30);
468
469 RETURN TO_CHAR(l_count);
470
471 EXCEPTION
472 WHEN OTHERS THEN
473 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_proc, 'SQLERRMC:'||sqlerrm);
474 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc, 50);
475 pay_in_utils.trace(l_message,l_proc);
476
477 END deductee_rec_count_24q;
478
479 --------------------------------------------------------------------------
480 -- --
481 -- Name : GET_24QC_TAX_VALUES --
482 -- Type : FUNCTION --
483 -- Access : Public --
484 -- Description : This function returns the Tax Values String --
485 -- Parameters : --
486 -- IN : p_challan_number VARCHAR2 --
487 -- p_gre_org_id VARCHAR2 --
488 -- p_max_action_id VARCHAR2 --
489 --------------------------------------------------------------------------
490 FUNCTION get_24QC_tax_values(
491 p_challan_number IN VARCHAR2
492 ,p_gre_org_id IN VARCHAR2
493 ,p_max_action_id IN VARCHAR2
494 )
495 RETURN VARCHAR2 IS
496
497 CURSOR c_form24QC_tax_values IS
498 SELECT SUM(DECODE(action_information15, 'D', -1 * NVL(action_information16, 0)
499 , NVL(action_information16, 0)))
500 - SUM(DECODE(action_information15, 'U', NVL(action_information24, 0)
501 , 0)) tax_deposited
502 ,SUM(DECODE(action_information15, 'D', -1 * NVL(action_information6, 0)
503 , NVL(action_information6, 0)))
504 - SUM(DECODE(action_information15, 'U', NVL(action_information21, 0)
505 , 0)) tds
506 ,SUM(DECODE(action_information15, 'D', -1 * NVL(action_information7, 0)
507 , NVL(action_information7, 0)))
508 - SUM(DECODE(action_information15, 'U', NVL(action_information22, 0)
509 , 0)) surcharge
510 ,SUM(DECODE(action_information15, 'D', -1 * NVL(action_information8, 0)
511 , NVL(action_information8, 0)))
512 - SUM(DECODE(action_information15, 'U', NVL(action_information23, 0)
513 , 0)) cess
514 FROM (
515 SELECT DISTINCT source_id
516 ,pay_in_24qc_er_returns.remove_curr_format(action_information15) action_information15
517 ,pay_in_24qc_er_returns.remove_curr_format(action_information16) action_information16
518 ,pay_in_24qc_er_returns.remove_curr_format(action_information24) action_information24
519 ,pay_in_24qc_er_returns.remove_curr_format(action_information6) action_information6
520 ,pay_in_24qc_er_returns.remove_curr_format(action_information21) action_information21
521 ,pay_in_24qc_er_returns.remove_curr_format(action_information7) action_information7
522 ,pay_in_24qc_er_returns.remove_curr_format(action_information22) action_information22
523 ,pay_in_24qc_er_returns.remove_curr_format(action_information8) action_information8
524 ,pay_in_24qc_er_returns.remove_curr_format(action_information23) action_information23
525 FROM pay_action_information
526 WHERE action_information_category ='IN_24QC_DEDUCTEE'
527 AND action_context_type = 'AAP'
528 AND action_information3 = p_gre_org_id
529 AND action_information1 = p_challan_number
530 AND INSTR(NVL(action_information19,'0'),'C5') = 0
531 AND EXISTS ( SELECT 1
532 FROM pay_assignment_actions paa
533 WHERE paa.payroll_action_id = p_max_action_id
534 AND paa.assignment_action_id = action_context_id));
535
536 l_proc VARCHAR2(100);
537 l_message VARCHAR2(240);
538 l_value29 VARCHAR2(20);
539 l_value30 VARCHAR2(20);
540 l_value31 VARCHAR2(20);
541 l_value32 VARCHAR2(20);
542 l_value33 VARCHAR2(20);
543 l_total_tax_values VARCHAR2(100);
544
545 BEGIN
546
547 l_proc := g_package||'get_24qc_tax_values';
548 g_debug := hr_utility.debug_enabled;
549 pay_in_utils.set_location(g_debug, 'Entering: ' || l_proc, 10);
550
551 IF g_debug THEN
552 pay_in_utils.trace('**************************************************','********************');
553 pay_in_utils.trace('p_gre_org_id',p_gre_org_id);
554 pay_in_utils.trace('p_max_action_id',p_max_action_id);
555 pay_in_utils.trace('p_challan',p_challan_number);
556 pay_in_utils.trace('**************************************************','********************');
557 END IF;
558
559 l_value29 := 0;
560 l_value30 := 0;
561 l_value31 := 0;
562 l_value32 := 0;
563 l_value33 := 0;
564
568
565 OPEN c_form24QC_tax_values;
566 FETCH c_form24QC_tax_values INTO l_value29, l_value30, l_value31, l_value32;
567 CLOSE c_form24QC_tax_values;
569 l_value33 := TO_NUMBER(l_value30) + TO_NUMBER(l_value31) + TO_NUMBER(l_value32);
570
571 l_value29 := pay_in_24q_er_returns.get_format_value(l_value29);
572 l_value30 := pay_in_24q_er_returns.get_format_value(l_value30);
573 l_value31 := pay_in_24q_er_returns.get_format_value(l_value31);
574 l_value32 := pay_in_24q_er_returns.get_format_value(l_value32);
575 l_value33 := pay_in_24q_er_returns.get_format_value(l_value33);
576
577 l_total_tax_values := l_value29||'^'||l_value30||'^'||l_value31||'^'||l_value32||'^'||l_value33||'^';
578
579 IF g_debug THEN
580 pay_in_utils.trace('**************************************************','********************');
581 pay_in_utils.trace('l_total_tax_values',l_total_tax_values);
582 pay_in_utils.trace('**************************************************','********************');
583 END IF;
584
585 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc, 30);
586
587 RETURN l_total_tax_values;
588
589 EXCEPTION
590 WHEN OTHERS THEN
591 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_proc, 'SQLERRMC:'||sqlerrm);
592 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc, 50);
593 pay_in_utils.trace(l_message,l_proc);
594
595 END get_24qc_tax_values;
596
597 --------------------------------------------------------------------------
598 -- --
599 -- Name : GET_ARCHIVE_PAY_ACTION --
600 -- Type : FUNCTION --
601 -- Access : Public --
602 -- Description : This function returns the latest archival payroll --
603 -- action id for a period --
604 -- Parameters : --
605 -- IN : p_gre_org_id VARCHAR2 --
606 -- p_period VARCHAR2 --
607 --------------------------------------------------------------------------
608 FUNCTION get_archive_pay_action (p_gre_org_id IN VARCHAR2
609 ,p_period IN VARCHAR2)
610 RETURN NUMBER IS
611
612 CURSOR csr_arch_action_id
613 IS
614 SELECT MAX(action_context_id)
615 FROM pay_action_information
616 WHERE action_information1 = p_gre_org_id
617 AND action_information3 = p_period
618 AND action_context_type = 'PA'
619 AND action_information_category = 'IN_24QC_ORG';
620
621 l_arch_action_id NUMBER;
622 l_proc VARCHAR2(100);
623 l_message VARCHAR2(240);
624
625 BEGIN
626
627 l_proc := g_package||'get_archive_pay_action';
628 g_debug := hr_utility.debug_enabled;
629 pay_in_utils.set_location(g_debug, 'Entering: ' || l_proc, 10);
630
631 IF g_debug THEN
632 pay_in_utils.trace('**************************************************','********************');
633 pay_in_utils.trace('p_gre_org_id',p_gre_org_id);
634 pay_in_utils.trace('p_period',p_period);
635 pay_in_utils.trace('**************************************************','********************');
636 END IF;
637
638 OPEN csr_arch_action_id;
639 FETCH csr_arch_action_id INTO l_arch_action_id;
640 IF csr_arch_action_id%NOTFOUND THEN
641 CLOSE csr_arch_action_id;
642 RETURN 0;
643 END IF;
644 CLOSE csr_arch_action_id;
645
646 IF g_debug THEN
647 pay_in_utils.trace('**************************************************','********************');
648 pay_in_utils.trace('l_arch_action_id',l_arch_action_id);
649 pay_in_utils.trace('**************************************************','********************');
650 END IF;
651
652 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc, 30);
653
654 RETURN l_arch_action_id;
655
656 EXCEPTION
657 WHEN OTHERS THEN
658 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_proc, 'SQLERRMC:'||sqlerrm);
659 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc, 50);
660 pay_in_utils.trace(l_message,l_proc);
661
662 END get_archive_pay_action;
663
664
665 --------------------------------------------------------------------------
666 -- --
667 -- Name : REMOVE_CURR_FORMAT --
668 -- Type : FUNCTION --
669 -- Access : Public --
670 -- Description : This function returns the latest archival payroll --
671 -- action id for a period --
672 -- Parameters : --
673 -- IN : p_value VARCHAR2 --
674 -- --
675 --------------------------------------------------------------------------
676 FUNCTION remove_curr_format (p_value IN VARCHAR2)
677 RETURN VARCHAR2 IS
678 l_return_value VARCHAR2(240);
679 l_proc VARCHAR2(100);
680 l_message VARCHAR2(240);
681 BEGIN
682
683 l_proc := g_package||'remove_curr_format';
684 g_debug := hr_utility.debug_enabled;
685 pay_in_utils.set_location(g_debug, 'Entering: ' || l_proc, 10);
686
687 IF g_debug THEN
691 END IF;
688 pay_in_utils.trace('**************************************************','********************');
689 pay_in_utils.trace('p_value',p_value);
690 pay_in_utils.trace('**************************************************','********************');
692
693 l_return_value := REPLACE(REPLACE(NVL(p_value,'0'), ',', ''), '+', '');
694
695 IF g_debug THEN
696 pay_in_utils.trace('**************************************************','********************');
697 pay_in_utils.trace('l_return_value',l_return_value);
698 pay_in_utils.trace('**************************************************','********************');
699 END IF;
700
701 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc, 30);
702
703 RETURN l_return_value;
704
705 EXCEPTION
706 WHEN OTHERS THEN
707 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_proc, 'SQLERRMC:'||sqlerrm);
708 pay_in_utils.set_location(g_debug,'Leaving : '||l_proc, 50);
709 pay_in_utils.trace(l_message,l_proc);
710
711 END remove_curr_format;
712
713
714 --------------------------------------------------------------------------
715 -- --
716 -- Name : TOTAL_GROSS_TOT_INCOME --
717 -- Type : FUNCTION --
718 -- Access : Public --
719 -- Description : This function returns the total of Gross Total --
720 -- Income as per salary details annexure --
721 -- Parameters : --
722 -- IN : p_gre_org_id VARCHAR2 --
723 -- p_assess_period VARCHAR2 --
724 -- p_correction_type VARCHAR2 --
725 -- p_max_action_id VARCHAR2 --
726 --------------------------------------------------------------------------
727 FUNCTION total_gross_tot_income (p_gre_org_id IN VARCHAR2
728 ,p_assess_period IN VARCHAR2
729 ,p_correction_type IN VARCHAR2
730 ,p_max_action_id IN VARCHAR2)
731 RETURN VARCHAR2 IS
732
733 CURSOR csr_income_details(p_balance VARCHAR2,p_action_context_id NUMBER,p_source_id IN NUMBER)
734 IS
735 SELECT NVL(SUM(action_information2),0)
736 FROM pay_action_information
737 WHERE action_information_category = 'IN_24QC_SALARY'
738 AND action_context_type = 'AAP'
739 AND action_information1 = p_balance
740 AND action_context_id = p_action_context_id
741 AND source_id = p_source_id;
742
743 CURSOR csr_get_max_cont_id IS
744 SELECT MAX(pai.action_context_id) action_cont_id
745 ,source_id sour_id
746 FROM pay_action_information pai
747 ,pay_assignment_actions paa
748 ,per_assignments_f asg
749 WHERE paa.payroll_action_id = p_max_action_id
750 AND pai.action_context_id = paa.assignment_action_id
751 AND pai.action_information_category = 'IN_24QC_PERSON'
752 AND pai.action_information3 = p_gre_org_id
753 AND pai.action_information2 = p_assess_period
754 AND pai.action_information1 = asg.person_id
755 AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
756 AND asg.assignment_id = pai.assignment_id
757 AND pai.action_context_type = 'AAP'
758 AND pai.action_information11 = 'C4'
759 GROUP BY pai.action_information1,pai.action_information9,source_id;
760
761
762 l_total_gross NUMBER:=0;
763 l_value1 NUMBER:=0;
764 l_value2 NUMBER:=0;
765 l_total_value VARCHAR2(20);
766 l_procedure varchar2(100);
767
768 BEGIN
769 g_debug := hr_utility.debug_enabled;
770 l_procedure := g_package ||'total_gross_tot_income';
771 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
772
773 IF g_debug THEN
774 pay_in_utils.trace('**************************************************','********************');
775 pay_in_utils.trace('p_gre_org_id',p_gre_org_id);
776 pay_in_utils.trace('p_assess_period',p_assess_period);
777 pay_in_utils.trace('p_correction_type',p_correction_type);
778 pay_in_utils.trace('p_max_action_id',p_max_action_id);
779 pay_in_utils.trace('**************************************************','********************');
780 END IF;
781
782 IF (p_correction_type <> 'C4') THEN
783 IF g_debug THEN
784 pay_in_utils.trace('**************************************************','********************');
785 pay_in_utils.trace('l_total_gross','^');
786 pay_in_utils.trace('**************************************************','********************');
787 END IF;
788 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
789 RETURN '^';
790 ELSE
791
792 FOR i IN csr_get_max_cont_id
793 LOOP
794 OPEN csr_income_details('F16 Gross Total Income',i.action_cont_id,i.sour_id);
795 FETCH csr_income_details INTO l_value1;
796 CLOSE csr_income_details;
797
798 OPEN csr_income_details('Prev F16 Gross Total Income',i.action_cont_id,i.sour_id);
799 FETCH csr_income_details INTO l_value2;
800 CLOSE csr_income_details;
801
802 l_total_gross:= l_total_gross + l_value1+l_value2;
803 END LOOP;
804
805 l_total_value :=pay_in_24q_er_returns.get_format_value(l_total_gross);
806 END IF;
807
808 IF g_debug THEN
812 END IF;
809 pay_in_utils.trace('**************************************************','********************');
810 pay_in_utils.trace('l_total_value',SUBSTR(l_total_value,1,15)||'^');
811 pay_in_utils.trace('**************************************************','********************');
813
814 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
815
816 RETURN SUBSTR(l_total_value,1,15)||'^';
817
818 END total_gross_tot_income;
819
820
821 --------------------------------------------------------------------------
822 -- --
823 -- Name : SALARY_REC_COUNT --
824 -- Type : FUNCTION --
825 -- Access : Public --
826 -- Description : This function returns the Total number of records --
827 -- in the Salary Details of the Magtape --
828 -- Parameters : --
829 -- IN : p_gre_org_id VARCHAR2 --
830 -- p_assess_period VARCHAR2 --
831 -- p_correction_type VARCHAR2 --
832 -- p_max_action_id VARCHAR2 --
833 --------------------------------------------------------------------------
834 FUNCTION salary_rec_count (p_gre_org_id IN VARCHAR2
835 ,p_assess_period IN VARCHAR2
836 ,p_correction_type IN VARCHAR2
837 ,p_max_action_id IN VARCHAR2)
838 RETURN VARCHAR2 IS
839
840 CURSOR c_count
841 IS
842 SELECT COUNT(*)
843 FROM pay_action_information
844 WHERE action_information_category = 'IN_24QC_PERSON'
845 AND action_context_type = 'AAP'
846 AND action_information2 = p_assess_period
847 AND action_information3 = p_gre_org_id
848 AND action_information11 = p_correction_type
849 AND action_context_id IN (SELECT MAX(pai.action_context_id)
850 FROM pay_action_information pai
851 ,pay_assignment_actions paa
852 ,per_assignments_f asg
853 WHERE paa.payroll_action_id = p_max_action_id
854 AND pai.action_context_id = paa.assignment_action_id
855 AND pai.action_information_category = 'IN_24QC_PERSON'
856 AND pai.action_context_type = 'AAP'
857 AND pai.action_information1 = asg.person_id
858 AND pai.assignment_id = asg.assignment_id
859 AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
860 AND pai.action_information2 = p_assess_period
861 AND pai.action_information3 = p_gre_org_id
862 AND pai.action_information11 = p_correction_type
863 GROUP BY pai.assignment_id,pai.action_information1,pai.action_information9
864 );
865
866
867 l_count NUMBER;
868 l_procedure varchar2(100);
869
870 BEGIN
871 g_debug := hr_utility.debug_enabled;
872 l_procedure := g_package ||'salary_rec_count';
873 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
874
875 IF g_debug THEN
876 pay_in_utils.trace('**************************************************','********************');
877 pay_in_utils.trace('p_gre_org_id',p_gre_org_id);
878 pay_in_utils.trace('p_assess_period',p_assess_period);
879 pay_in_utils.trace('p_correction_type',p_correction_type);
880 pay_in_utils.trace('p_max_action_id',p_max_action_id);
881 pay_in_utils.trace('**************************************************','********************');
882 END IF;
883
884 IF(p_correction_type NOT IN ('C4','C5'))THEN
885 IF g_debug THEN
886 pay_in_utils.trace('**************************************************','********************');
887 pay_in_utils.trace('l_count','^');
888 pay_in_utils.trace('**************************************************','********************');
889 END IF;
890 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
891 RETURN '^';
892 ELSE
893 OPEN c_count;
894 FETCH c_count INTO l_count;
895 IF c_count%NOTFOUND THEN
896 CLOSE c_count;
897 IF g_debug THEN
898 pay_in_utils.trace('**************************************************','********************');
899 pay_in_utils.trace('l_count','0^');
900 pay_in_utils.trace('**************************************************','********************');
901 END IF;
902 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
903 RETURN '0^';
904 END IF;
905 CLOSE c_count;
906
907
908 IF g_debug THEN
909 pay_in_utils.trace('**************************************************','********************');
910 pay_in_utils.trace('l_count',TO_CHAR(l_count)||'^');
911 pay_in_utils.trace('**************************************************','********************');
912 END IF;
913
914 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
915
916 RETURN TO_CHAR(l_count)||'^';
917 END IF;
918
919 END salary_rec_count;
920
921
922 --------------------------------------------------------------------------
923 -- --
924 -- Name : CHAPTER_VIA_REC_COUNT --
925 -- Type : FUNCTION --
926 -- Access : Public --
927 -- Description : This function returns the Total number of records --
928 -- in the Chapter-VIA Details of the Magtape --
929 -- Parameters : --
930 -- IN : p_action_context_id VARCHAR2 --
931 -- p_source_id VARCHAR2 --
932 --------------------------------------------------------------------------
933 FUNCTION chapter_VIA_rec_count (p_action_context_id IN VARCHAR2
934 ,p_source_id IN VARCHAR2)
935 RETURN VARCHAR2 IS
936
937 CURSOR c_count
938 IS
939 SELECT COUNT(*)
940 FROM pay_action_information
941 WHERE action_information_category = 'IN_24QC_VIA'
942 AND action_context_type = 'AAP'
943 AND action_context_id = p_action_context_id
944 AND source_id =p_source_id;
945
946 l_count NUMBER;
947 l_procedure varchar2(100);
948
949 BEGIN
950 g_debug := hr_utility.debug_enabled;
951 l_procedure := g_package ||'chapter_VIA_rec_count';
952 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
953
954 IF g_debug THEN
955 pay_in_utils.trace('**************************************************','********************');
956 pay_in_utils.trace('p_action_context_id',p_action_context_id);
957 pay_in_utils.trace('p_source_id',p_source_id);
958 pay_in_utils.trace('**************************************************','********************');
959 END IF;
960
961 OPEN c_count;
962 FETCH c_count INTO l_count;
963 IF c_count%NOTFOUND THEN
964 CLOSE c_count;
965 IF g_debug THEN
966 pay_in_utils.trace('**************************************************','********************');
967 pay_in_utils.trace('l_count','0');
968 pay_in_utils.trace('**************************************************','********************');
969 END IF;
970 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
971 RETURN '0';
972 END IF;
973 CLOSE c_count;
974
975
976 IF g_debug THEN
977 pay_in_utils.trace('**************************************************','********************');
978 pay_in_utils.trace('l_count',TO_CHAR(l_count));
979 pay_in_utils.trace('**************************************************','********************');
980 END IF;
981
982 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
983
984 RETURN TO_CHAR(l_count);
985
986 END chapter_VIA_rec_count;
987
988
989 END pay_in_24qc_er_returns;