[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_EOY_ER_RETURNS
Source
1 PACKAGE BODY pay_in_eoy_er_returns AS
2 /* $Header: pyinerit.pkb 120.4 2006/05/19 08:53:37 abhjain noship $ */
3 g_debug BOOLEAN;
4 g_package CONSTANT VARCHAR2(100) := 'pay_in_eoy_er_returns.' ;
5 --------------------------------------------------------------------------
6 -- --
7 -- Name : CHALLAN_REC_COUNT --
8 -- Type : FUNCTION --
9 -- Access : Public --
10 -- Description : This function returns the Total number of records --
11 -- in the Challan Details of the Magtape --
12 -- Parameters : --
13 -- IN : p_gre_org_id VARCHAR2 --
14 -- p_assess_year VARCHAR2 --
15 --------------------------------------------------------------------------
16 FUNCTION challan_rec_count (p_gre_org_id IN VARCHAR2
17 ,p_assess_year IN VARCHAR2)
18 RETURN VARCHAR2 IS
19
20 CURSOR c_count
21 IS
22 SELECT COUNT(*)
23 FROM hr_organization_information ch_b
24 , hr_organization_information it_ch
25 WHERE it_ch.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
26 AND ch_b.org_information_context = 'PER_IN_CHALLAN_BANK'
27 AND it_ch.organization_id = p_gre_org_id
28 AND it_ch.org_information1 = TO_CHAR((TO_NUMBER(SUBSTR(p_assess_year,1,4)) - 1)||'-'||SUBSTR(p_assess_year,1,4))
29 AND it_ch.organization_id = ch_b.organization_id
30 AND TO_NUMBER(it_ch.org_information5) = ch_b.org_information_id;
31
32 l_count NUMBER;
33 l_procedure varchar2(100);
34
35 BEGIN
36 g_debug := hr_utility.debug_enabled;
37 l_procedure := g_package ||'challan_rec_count';
38 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
39
40 IF g_debug THEN
41 pay_in_utils.trace('**************************************************','********************');
42 pay_in_utils.trace('p_gre_org_id',p_gre_org_id);
43 pay_in_utils.trace('p_assess_year',p_assess_year);
44 pay_in_utils.trace('**************************************************','********************');
45 END IF;
46
47 OPEN c_count;
48 FETCH c_count INTO l_count;
49 IF c_count%NOTFOUND THEN
50 CLOSE c_count;
51 RETURN '0';
52 END IF;
53 CLOSE c_count;
54
55 IF g_debug THEN
56 pay_in_utils.trace('l_count',TO_CHAR(l_count));
57 END IF;
58
59 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
60
61 RETURN TO_CHAR(l_count);
62
63 END challan_rec_count;
64
65 --------------------------------------------------------------------------
69 -- Access : Public --
66 -- --
67 -- Name : DEDUCTEE_REC_COUNT --
68 -- Type : FUNCTION --
70 -- Description : This function returns the Total number of records --
71 -- in the Deductee Details of the Magtape --
72 -- Parameters : --
73 -- IN : p_gre_org_id VARCHAR2 --
74 -- p_assess_year VARCHAR2 --
75 --------------------------------------------------------------------------
76
77 FUNCTION deductee_rec_count (p_gre_org_id IN VARCHAR2
78 ,p_assess_year IN VARCHAR2)
79 RETURN VARCHAR2 IS
80
81 CURSOR c_count
82 IS
83 SELECT COUNT(*)
84 FROM pay_action_information
85 WHERE action_information_category = 'IN_EOY_PERSON'
86 AND action_context_type = 'AAP'
87 AND action_information2 = p_assess_year
88 AND action_information3 = p_gre_org_id
89 AND action_context_id IN ( SELECT MAX(pai.action_context_id)
90 FROM pay_action_information pai
91 ,pay_assignment_actions paa
92 ,per_assignments_f asg
93 WHERE pai.action_information_category = 'IN_EOY_PERSON'
94 AND pai.action_context_type = 'AAP'
95 AND pai.assignment_id = asg.assignment_id
96 AND paa.assignment_id = asg.assignment_id
97 AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
98 AND pai.action_information2 = p_assess_year
99 AND pai.action_information3 = p_gre_org_id
100 AND pai.source_id = paa.assignment_action_id
101 GROUP BY pai.action_information1,pai.action_information17 );
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_assess_year',p_assess_year);
115 pay_in_utils.trace('**************************************************','********************');
116 END IF;
117
118 OPEN c_count;
119 FETCH c_count INTO l_count;
120 IF c_count%NOTFOUND THEN
121 CLOSE c_count;
122 RETURN '0';
123 END IF;
124 CLOSE c_count;
125
126
127
128 IF g_debug THEN
129 pay_in_utils.trace('l_count',TO_CHAR(l_count));
130 END IF;
131
132 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
133
134 RETURN TO_CHAR(l_count);
135
136 END deductee_rec_count;
137
138 --------------------------------------------------------------------------
139 -- --
140 -- Name : PERQ_REC_COUNT --
141 -- Type : FUNCTION --
142 -- Access : Public --
143 -- Description : This function returns the Total number of records --
144 -- in the Perquisite Details of the Magtape --
145 -- Parameters : --
146 -- IN : p_gre_org_id VARCHAR2 --
147 -- p_assess_year VARCHAR2 --
148 --------------------------------------------------------------------------
149
150 FUNCTION perq_rec_count (p_gre_org_id IN VARCHAR2
151 ,p_assess_year IN VARCHAR2)
152 RETURN VARCHAR2 IS
153
154 CURSOR c_count
155 IS
156 SELECT COUNT(*)
157 FROM pay_action_information
158 WHERE action_information_category = 'IN_EOY_PERSON'
159 AND action_context_type = 'AAP'
160 AND action_information2 = p_assess_year
161 AND action_information3 = p_gre_org_id
162 AND action_context_id IN ( SELECT MAX(pai.action_context_id)
163 FROM pay_action_information pai
164 ,pay_assignment_actions paa
165 ,per_assignments_f asg
166 WHERE pai.action_information_category = 'IN_EOY_PERSON'
167 AND pai.action_context_type = 'AAP'
168 AND pai.assignment_id = asg.assignment_id
169 AND paa.assignment_id = asg.assignment_id
170 AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
171 AND pai.action_information2 = p_assess_year
172 AND pai.action_information3 = p_gre_org_id
173 AND pai.source_id = paa.assignment_action_id
174 GROUP BY pai.action_information1,pai.action_information17 );
175
176
177 l_count NUMBER;
178 l_procedure varchar2(100);
179
180 BEGIN
181 g_debug := hr_utility.debug_enabled;
182 l_procedure := g_package ||'perq_rec_count';
183 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
184
185 IF g_debug THEN
186 pay_in_utils.trace('**************************************************','********************');
187 pay_in_utils.trace('p_gre_org_id',p_gre_org_id);
188 pay_in_utils.trace('p_assess_year',p_assess_year);
189 pay_in_utils.trace('**************************************************','********************');
190 END IF;
191
192 OPEN c_count;
193 FETCH c_count INTO l_count;
194 IF c_count%NOTFOUND THEN
195 CLOSE c_count;
196 RETURN '0';
197 END IF;
198 CLOSE c_count;
199
200
201
202 IF g_debug THEN
203 pay_in_utils.trace('l_count',TO_CHAR(l_count));
204 END IF;
205
206 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
207
208 RETURN TO_CHAR(l_count);
209
210 END perq_rec_count;
211
212 --------------------------------------------------------------------------
213 -- --
214 -- Name : GROSS_TOT_TDS_CHALLAN --
215 -- Type : FUNCTION --
216 -- Access : Public --
217 -- Description : This function returns the Gross Total of TDS --
218 -- deducted as per Challan details annexure --
219 -- Parameters : --
220 -- IN : p_gre_org_id VARCHAR2 --
221 -- p_assess_year VARCHAR2 --
222 --------------------------------------------------------------------------
223
224 FUNCTION gross_tot_tds_challan(p_gre_org_id IN VARCHAR2
225 ,p_assess_year IN VARCHAR2)
226 RETURN VARCHAR2 IS
227
228 CURSOR c_challan_tax_tot IS
229 SELECT SUM(NVL(it_ch.org_information4,0)) TDS
230 , SUM(NVL(it_ch.org_information7,0)) SUR
231 , SUM(NVL(it_ch.org_information8,0)) EC
232 , SUM(NVL(it_ch.org_information9,0)) INTR
233 , SUM(NVL(it_ch.org_information10,0)) OTH
234 FROM hr_organization_information ch_b
235 , hr_organization_information it_ch
236 WHERE it_ch.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
237 AND ch_b.org_information_context = 'PER_IN_CHALLAN_BANK'
238 AND it_ch.organization_id = p_gre_org_id
239 AND it_ch.org_information1 = TO_CHAR((TO_NUMBER(SUBSTR(p_assess_year,1,4)) - 1)||'-'||SUBSTR(p_assess_year,1,4))
240 AND it_ch.organization_id = ch_b.organization_id
241 AND TO_NUMBER(it_ch.org_information5) = ch_b.org_information_id;
242
243 l_tot NUMBER:= 0;
244 l_tds NUMBER:= 0;
245 l_sur NUMBER:= 0;
246 l_ec NUMBER:= 0;
247 l_intr NUMBER:= 0;
248 l_oth NUMBER:= 0;
249 l_procedure varchar2(100);
250
251 BEGIN
252 g_debug := hr_utility.debug_enabled;
253 l_procedure := g_package ||'gross_tot_tds_challan';
254 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
255
256 IF g_debug THEN
257 pay_in_utils.trace('**************************************************','********************');
258 pay_in_utils.trace('p_gre_org_id',p_gre_org_id);
259 pay_in_utils.trace('p_assess_year',p_assess_year);
260 pay_in_utils.trace('**************************************************','********************');
261 END IF;
262
263 OPEN c_challan_tax_tot;
264 FETCH c_challan_tax_tot INTO l_tds,l_sur,l_ec,l_intr,l_oth;
265 IF c_challan_tax_tot%NOTFOUND THEN
266 CLOSE c_challan_tax_tot;
267 RETURN '0';
268 END IF;
269 CLOSE c_challan_tax_tot;
270
271 l_tot := l_tds + l_sur + l_ec + l_intr + l_oth;
272 l_tot := NVL(l_tot,0) * 100;
273
274
275 IF g_debug THEN
276 pay_in_utils.trace('l_tot',TO_CHAR(l_tot));
277 END IF;
278 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
279
280 RETURN TO_CHAR(l_tot);
281
282
283 END gross_tot_tds_challan;
284
285 --------------------------------------------------------------------------
286 -- --
287 -- Name : GROSS_TOT_TDS_DEDUCTEE --
288 -- Type : FUNCTION --
289 -- Access : Public --
290 -- Description : This function returns the Gross Total of TDS --
291 -- deducted as per Deductee details annexure --
292 -- Parameters : --
293 -- IN : p_gre_org_id VARCHAR2 --
297 FUNCTION gross_tot_tds_deductee (p_gre_org_id IN VARCHAR2
294 -- p_assess_year VARCHAR2 --
295 --------------------------------------------------------------------------
296
298 ,p_assess_year IN VARCHAR2)
299 RETURN VARCHAR2 IS
300
301 CURSOR csr_tax_details(p_balance VARCHAR2,p_action_context_id NUMBER,p_source_id IN NUMBER)
302 IS
303 SELECT NVL(SUM(action_information2),0)
304 FROM pay_action_information
305 WHERE action_information_category = 'IN_EOY_ASG_SAL'
306 AND action_context_type = 'AAP'
307 AND action_information1 = p_balance
308 AND action_context_id = p_action_context_id
309 AND source_id = p_source_id;
310
311 CURSOR csr_get_max_cont_id IS
312 SELECT MAX(pai.action_context_id) action_cont_id
313 ,source_id sour_id
314 FROM pay_action_information pai
315 ,pay_assignment_actions paa
316 ,per_assignments_f asg
317 WHERE pai.action_information_category = 'IN_EOY_PERSON'
318 AND pai.assignment_id = asg.assignment_id
319 AND paa.assignment_id = asg.assignment_id
320 AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
321 AND pai.action_information3 = p_gre_org_id
322 AND pai.action_information2 = p_assess_year
323 AND pai.action_context_type = 'AAP'
324 AND pai.source_id = paa.assignment_action_id
325 GROUP BY pai.action_information1,pai.action_information17,source_id;
326
327 l_tds NUMBER:=0;
328 l_it_td NUMBER;
329 l_sc_td NUMBER;
330 l_ec_td NUMBER;
331 l_value NUMBER:=0;
332 l_procedure varchar2(100);
333
334 BEGIN
335 g_debug := hr_utility.debug_enabled;
336 l_procedure := g_package ||'gross_tot_tds_deductee';
337 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
338
339 IF g_debug THEN
340 pay_in_utils.trace('**************************************************','********************');
341 pay_in_utils.trace('p_gre_org_id',p_gre_org_id);
342 pay_in_utils.trace('p_assess_year',p_assess_year);
343 pay_in_utils.trace('**************************************************','********************');
344 END IF;
345
346 FOR i IN csr_get_max_cont_id
347 LOOP
348 OPEN csr_tax_details('Income Tax Deduction',i.action_cont_id,i.sour_id);
349 FETCH csr_tax_details INTO l_value;
350 CLOSE csr_tax_details;
351
352 l_tds := l_tds + l_value;
353 END LOOP;
354
355 l_tds := l_tds * 100;
356
357 IF g_debug THEN
358 pay_in_utils.trace('l_tds',TO_CHAR(l_tds));
359 END IF;
360 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
361 RETURN TO_CHAR(l_tds);
362
363 END gross_tot_tds_deductee;
364
365 --------------------------------------------------------------------------
366 -- --
367 -- Name : GET_EOY_VALUES --
368 -- Type : FUNCTION --
369 -- Access : Public --
370 -- Description : This function returns the values corresponding to --
371 -- the F16 Balances --
372 -- Parameters : --
373 -- IN : p_category VARCHAR2 --
374 -- p_component_name VARCHAR2 --
375 -- p_context_id NUMBER --
376 -- p_segment_num NUMBER --
377 --------------------------------------------------------------------------
378
379 FUNCTION get_eoy_values (p_category IN VARCHAR2
380 ,p_component_name IN VARCHAR2
381 ,p_context_id IN NUMBER
382 ,p_source_id IN NUMBER
383 ,p_segment_num IN NUMBER)
384 RETURN VARCHAR2 IS
385
386 CURSOR c_form24_values IS
387 SELECT NVL(action_information2,'0')
388 ,NVL(action_information3,'0')
389 ,NVL(action_information4,'0')
390 ,NVL(action_information5,'0')
391 FROM pay_action_information
392 WHERE action_information_category = p_category
393 AND action_information1 = p_component_name
394 AND action_context_id = p_context_id
395 AND source_id = p_source_id;
396
397 l_ai2 VARCHAR2(240);
398 l_ai3 VARCHAR2(240);
399 l_ai4 VARCHAR2(240);
400 l_ai5 VARCHAR2(240);
401 l_procedure varchar2(100);
402
403 BEGIN
404 g_debug := hr_utility.debug_enabled;
405 l_procedure := g_package ||'get_eoy_values';
406 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
407
408 IF g_debug THEN
409 pay_in_utils.trace('**************************************************','********************');
410 pay_in_utils.trace('p_category',p_category);
411 pay_in_utils.trace('p_component_name',p_component_name);
412 pay_in_utils.trace('p_context_id',to_char(p_context_id));
413 pay_in_utils.trace('p_source_id',to_char(p_source_id));
414 pay_in_utils.trace('p_segment_num',to_char(p_segment_num));
415 pay_in_utils.trace('**************************************************','********************');
416
417 END IF;
418
419 OPEN c_form24_values;
420 FETCH c_form24_values INTO l_ai2,l_ai3,l_ai4,l_ai5;
421 IF c_form24_values%NOTFOUND THEN
422 CLOSE c_form24_values;
423 RETURN '0';
424 END IF;
425 CLOSE c_form24_values;
426
427 l_ai2 := TO_CHAR(TO_NUMBER(l_ai2) * 100);
428 l_ai3 := TO_CHAR(TO_NUMBER(l_ai3) * 100);
429 l_ai4 := TO_CHAR(TO_NUMBER(l_ai4) * 100);
430 l_ai5 := TO_CHAR(TO_NUMBER(l_ai5) * 100);
431
432 pay_in_utils.set_location (g_debug,'l_ai2'||l_ai2,20);
433 pay_in_utils.set_location (g_debug,'l_ai3'||l_ai3,30);
434 pay_in_utils.set_location (g_debug,'l_ai4'||l_ai4,40);
435 pay_in_utils.set_location (g_debug,'l_ai5'||l_ai5,50);
436
437
438 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,60);
439
440 IF p_segment_num = 2 THEN
441 RETURN l_ai2;
442 ELSIF p_segment_num = 3 THEN
443 RETURN l_ai3;
444 ELSIF p_segment_num = 4 THEN
445 RETURN l_ai4;
446 ELSIF p_segment_num = 5 THEN
447 RETURN l_ai5;
448 END IF;
449
450 END get_eoy_values;
451
452
453 --------------------------------------------------------------------------
454 -- --
455 -- Name : GET_TDE_REMARKS --
456 -- Type : FUNCTION --
457 -- Access : Public --
458 -- Description : This function returns the remarks entered at the --
459 -- assignment extra Information --
460 -- Parameters : --
461 -- IN : p_person_id VARCHAR2 --
462 -- p_assess_year VARCHAR2 --
463 --------------------------------------------------------------------------
464
465 FUNCTION get_tde_remarks (p_person_id IN VARCHAR2
466 ,p_assess_year IN VARCHAR2
467 ,p_date IN VARCHAR2)
468 RETURN VARCHAR2 IS
469
470 CURSOR c_remarks IS
471 SELECT paei.aei_information2
472 FROM per_assignment_extra_info paei
473 ,per_all_assignments_f paa
474 WHERE paei.information_type = 'PER_IN_TAX_EXEMPTION_DF'
475 AND paei.aei_information_category = 'PER_IN_TAX_EXEMPTION_DF'
476 AND paei.assignment_id = paa.assignment_id
477 AND paa.person_id = TO_NUMBER(p_person_id)
478 AND paei.aei_information1 = TO_CHAR((TO_NUMBER(SUBSTR(p_assess_year,1,4)) - 1)||'-'||SUBSTR(p_assess_year,1,4))
479 AND fnd_date.CHARDATE_TO_DATE(p_date) BETWEEN paa.effective_start_date AND paa.effective_end_date
480 AND ROWNUM = 1;
481
482
483 l_remarks VARCHAR2(150);
484 l_procedure varchar2(100);
485
486 BEGIN
487 g_debug := hr_utility.debug_enabled;
488 l_procedure := g_package ||'get_tde_remarks';
489 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
490
491
492 IF g_debug THEN
493 pay_in_utils.trace('**************************************************','********************');
494 pay_in_utils.trace('p_person_id',p_person_id);
495 pay_in_utils.trace('p_assess_year',p_assess_year);
496 pay_in_utils.trace('p_date',p_date);
497 pay_in_utils.trace('**************************************************','********************');
498 END IF;
499
500 OPEN c_remarks;
501 FETCH c_remarks INTO l_remarks;
502 IF c_remarks%NOTFOUND THEN
503 CLOSE c_remarks;
504 RETURN ' ';
505 END IF;
506 CLOSE c_remarks;
507
508 IF g_debug THEN
509 pay_in_utils.trace('l_remarks',l_remarks);
510 END IF;
511
512 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
513
514 RETURN l_remarks;
515
516 END get_tde_remarks;
517
518 --------------------------------------------------------------------------
519 -- --
520 -- Name : GET_EMPLOYER_CLASS --
521 -- Type : FUNCTION --
522 -- Access : Public --
523 -- Description : This function returns the employer classfication --
524 -- --
525 -- Parameters : --
526 -- IN : p_gre_org_id VARCHAR2 --
527 --------------------------------------------------------------------------
528
529 FUNCTION get_emplr_class (p_gre_org_id IN VARCHAR2)
530 RETURN VARCHAR2 IS
531
532 CURSOR c_employer IS
533 SELECT org_information3
534 FROM hr_organization_information
535 WHERE org_information_context = 'PER_IN_INCOME_TAX_DF'
536 AND organization_id = p_gre_org_id;
537
538 l_emplr_class VARCHAR2(150);
539 l_procedure varchar2(100);
540
541 BEGIN
542 g_debug := hr_utility.debug_enabled;
543 l_procedure := g_package ||'get_emplr_class';
544 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
545
546 IF g_debug THEN
547 pay_in_utils.trace('p_gre_org_id',p_gre_org_id);
548 END IF;
549
550 OPEN c_employer;
551 FETCH c_employer INTO l_emplr_class;
552 IF c_employer%NOTFOUND THEN
553 CLOSE c_employer;
554 RETURN 'XYZ';
555 END IF;
556 CLOSE c_employer;
557
558
559 IF g_debug THEN
560 pay_in_utils.trace('l_emplr_class',l_emplr_class);
561 END IF;
562 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
563
564 RETURN l_emplr_class;
565
566 END get_emplr_class;
567
568 --------------------------------------------------------------------------
569 -- --
570 -- Name : GET_LOCATION_DETAILS --
571 -- Type : FUNCTION --
572 -- Access : Public --
573 -- Description : This function gets the gre location details --
574 -- --
575 -- Parameters : --
576 -- IN : p_location_id hr_locations.location_id --
577 -- : p_concatenate VARCHAR2 --
578 -- p_field VARCHAR2 --
579 --------------------------------------------------------------------------
580 FUNCTION get_location_details ( p_location_id IN hr_locations.location_id%TYPE)
581 RETURN VARCHAR2
582 IS
583
584 CURSOR csr_add IS
585 SELECT address_line_1,
586 NVL(address_line_2,' '),
587 NVL(address_line_3,' '),
588 NVL(loc_information14,' '),
589 loc_information15,
590 NVL(hr_general.decode_lookup('IN_STATE_CODES',loc_information16),' '),
591 NVL(postal_code,' ')
592 FROM hr_locations
593 WHERE location_id = p_location_id;
594
595 l_add_1 hr_locations.address_line_1%TYPE;
596 l_add_2 hr_locations.address_line_2%TYPE;
597 l_add_3 hr_locations.address_line_3%TYPE;
598 l_add_4 hr_locations.loc_information14%TYPE;
599 l_add_5 hr_locations.loc_information15%TYPE;
600 l_state hr_lookups.meaning%TYPE;
601 l_pin hr_locations.postal_code%TYPE;
602 p_address VARCHAR2(200);
603 --
604 l_procedure varchar2(100);
605
606 BEGIN
607 g_debug := hr_utility.debug_enabled;
608 l_procedure := g_package ||'get_location_details';
609 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
610
611 IF g_debug THEN
612 pay_in_utils.trace('p_location_id',p_location_id);
613 END IF;
614
615 OPEN csr_add;
616 FETCH csr_add INTO l_add_1, l_add_2, l_add_3, l_add_4, l_add_5, l_state, l_pin;
617 IF csr_add%NOTFOUND THEN
618 CLOSE csr_add;
619 RETURN RPAD(' ',133,' ');
620 END IF;
621 CLOSE csr_add;
622
623 p_address := '';
624
625 IF LENGTH(l_add_1) <=25 THEN
626 p_address := p_address||RPAD(l_add_1,25,' ');
627 ELSE
628 p_address := p_address||SUBSTR(l_add_1,1,25);
629 l_add_2 := SUBSTR(l_add_1,26)||', '||l_add_2;
630 END IF;
631
632 IF LENGTH(l_add_2) <=25 THEN
633 p_address := p_address||RPAD(l_add_2,25,' ');
634 ELSE
635 p_address := p_address||SUBSTR(l_add_2,1,25);
636 l_add_3 := SUBSTR(l_add_2,26)||', '||l_add_3;
637 END IF;
638
639 IF LENGTH(l_add_3) <=25 THEN
640 p_address := p_address||RPAD(l_add_3,25,' ');
641 ELSE
642 p_address := p_address||SUBSTR(l_add_3,1,25);
643 l_add_4 := SUBSTR(l_add_3,26)||', '||l_add_4;
644 END IF;
645
646 IF LENGTH(l_add_4) <=25 THEN
647 p_address := p_address||RPAD(l_add_4,25,' ');
648 ELSE
649 p_address := p_address||SUBSTR(l_add_4,1,25);
650 l_add_5 := SUBSTR(l_add_4,26)||', '||l_add_5;
651 END IF;
652
653 IF LENGTH(l_add_5) <=25 THEN
654 p_address := p_address||RPAD(l_add_5,25,' ');
655 ELSE
656 p_address := p_address||SUBSTR(l_add_5,1,25);
657 END IF;
658
659 p_address := p_address||l_state||l_pin;
660
661
662 IF g_debug THEN
663 pay_in_utils.trace('p_address',p_address);
664 END IF;
665
666 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
667
668
669 RETURN p_address;
670
671
672 END get_location_details;
673
674 END pay_in_eoy_er_returns;