[Home] [Help]
PACKAGE BODY: APPS.PAY_PAYGBSOE_XMLP_PKG
Source
1 PACKAGE BODY PAY_PAYGBSOE_XMLP_PKG AS
2 /* $Header: PAYGBSOEB.pls 120.3 2007/12/27 11:27:08 srikrish noship $ */
3
4 function c_amount_paidformula(C_PAYMENT_TOTAL in number, C_DEDUCTION_TOTAL in number) return number is
5 begin
6
7 return (C_PAYMENT_TOTAL - C_DEDUCTION_TOTAL);
8 end;
9
10 function BeforeReport return boolean is
11 begin
12 /*srw.user_exit('FND SRWINIT');*/null;
13
14
15 declare
16 l_test number;
17 test varchar2(150);
18 l_session_date date;
19 SORT_ORDER1 boolean;
20 SORT_ORDER2 boolean;
21 SORT_ORDER3 boolean;
22 SORT_ORDER4 boolean;
23 SORT_ORDER5 boolean;
24 SORT_ORDER6 boolean;
25
26
27 cursor csr_session_date is
28 select end_date
29 from per_time_periods
30 where time_period_id = p_time_period_id;
31
32 begin
33 null;
34 SORT_ORDER1:=P_SORT_ORDER1ValidTrigger;
35 SORT_ORDER2 :=P_SORT_ORDER2ValidTrigger;
36 SORT_ORDER3:=P_SORT_ORDER3ValidTrigger;
37 SORT_ORDER4:=P_SORT_ORDER4ValidTrigger;
38 SORT_ORDER5:=P_SORT_ORDER5ValidTrigger;
39 SORT_ORDER6:=P_SORT_ORDER6ValidTrigger;
40
41 PAY_GB_PAYROLL_ACTIONS_PKG.get_input_values_id;
42
43 open csr_session_date;
44 fetch csr_session_date into l_session_date;
45 close csr_session_date;
46
47 insert into fnd_sessions (session_id, effective_date)
48 select userenv('sessionid'), trunc(l_session_date)
49 from dual
50 where not exists
51 (select 1
52 from fnd_sessions fs
53 where fs.session_id = userenv('sessionid'));
54 select session_id
55 into l_test
56 from fnd_sessions
57 where session_id = userenv('sessionid');
58 exception
59 when no_data_found then
60 null;
61 END;
62
63 return (TRUE);
64 end;
65
66 function P_SORT_ORDER3ValidTrigger return boolean is
67 begin
68
69 P_SORT_ORDER3_dup := nvl(P_SORT_ORDER3_dup,'NULL'); return (TRUE);
70 end;
71
72 function P_SORT_ORDER4ValidTrigger return boolean is
73 begin
74
75 P_SORT_ORDER4_dup := NVL(P_SORT_ORDER4_dup,'NULL'); return (TRUE);
76 end;
77
78 function P_SORT_ORDER5ValidTrigger return boolean is
79 begin
80
81 P_SORT_ORDER5_dup := NVL(P_SORT_ORDER5_dup,'NULL'); return (TRUE);
82 end;
83
84 function P_SORT_ORDER6ValidTrigger return boolean is
85 begin
86
87 P_SORT_ORDER6_dup := NVL(P_SORT_ORDER6_dup,'NULL'); return (TRUE);
88 end;
89
90 function c_nameformula(TITLE in varchar2, INITIALS in varchar2, LAST_NAME in varchar2) return varchar2 is
91 begin
92
93 DECLARE
94 A VARCHAR2(300);
95 BEGIN
96 A := TITLE ||' '|| INITIALS||' '|| LAST_NAME ;
97 RETURN(A);
98 END;
99 RETURN NULL; end;
100
101 Function Segment1 return varchar2 is
102 Segment_name varchar2(30);
103 Seg_num varchar2(60);
104 begin
105 If p_sort_order1_dup = 'NULL' then
106 return(null);
107 else
108 Seg_num := upper(p_sort_order1_dup);
109 --seg_name() );
110 Seg_name(Seg_num,Segment_name);
111 return(Segment_name);
112 end if;
113 RETURN NULL; end;
114
115 Procedure Seg_name(a in varchar2 , b out NOCOPY varchar2) is
116 begin
117 Select segment_name into b from fnd_id_flex_segments_vl
118 where application_id between 801 and 899
119 and id_flex_code = 'GRP'
120 and application_column_name = a
121 and id_flex_num = (Select people_group_structure from per_business_groups
122 Where business_group_id = p_bus_grp_id);
123
124
125
126 exception
127 When Others then
128 null;
129
130 end;
131
132 Function Segment2 return varchar2 is
133 Segment_name varchar2(30);
134 Seg_num varchar2(60);
135 Begin
136 If p_sort_order2_dup = 'NULL' then
137 return(null);
138 else
139 Seg_num := upper(p_sort_order2_dup);
140 --seg_name() );
141 Seg_name(Seg_num,Segment_name);
142 return(Segment_name);
143 end if;
144 RETURN NULL; End;
145
146 Function Segment3 return varchar2 is
147 Segment_name varchar2(30);
148 Seg_num varchar2(60);
149 Begin
150 If p_sort_order3_dup = 'NULL' then
151 return(null);
152 else
153 Seg_num := upper(p_sort_order3_dup);
154 --seg_name() );
155 Seg_name(Seg_num,Segment_name);
156 return(Segment_name);
157 end if;
158 RETURN NULL; end;
159
160 Function Segment4 return varchar2 is
161 Segment_name varchar2(30);
162 Seg_num varchar2(60);
163 Begin
164 If p_sort_order4_dup = 'NULL' then
165 return(null);
166 else
167 Seg_num := upper(p_sort_order4_dup);
168 --seg_name() );
169 Seg_name(Seg_num , Segment_name);
170 return(Segment_name);
171 end if;
172 RETURN NULL; end;
173
174 Function Segment5 return varchar2 is
175 Segment_name varchar2(30);
176 Seg_num varchar2(60);
177 Begin
178 If p_sort_order5_dup = 'NULL' then
179 return(null);
180 else
181 Seg_num := upper(p_sort_order5_dup);
182 --seg_name() );
183 Seg_name(Seg_num , Segment_name);
184 return(Segment_name);
185 end if;
186 RETURN NULL; end;
187
188 Function Segment6 return varchar2 is
189 Segment_name varchar2(30);
190 Seg_num varchar2(60);
191 begin
192 If p_sort_order6_dup = 'NULL' then
193 return(null);
194 else
195 Seg_num := upper(p_sort_order6_dup);
196 --seg_name() );
197 Seg_name(Seg_num,Segment_name);
198 return(Segment_name);
199 end if;
200 RETURN NULL; end;
201
202 function P_SORT_ORDER1ValidTrigger return boolean is
203 begin
204
205 P_SORT_ORDER1_dup := NVL(P_SORT_ORDER1_dup,'NULL');
206 return (TRUE);
207 end;
208
209 function P_SORT_ORDER2ValidTrigger return boolean is
210 begin
211
212 P_SORT_ORDER2_dup := NVL(P_SORT_ORDER2_dup,'NULL'); return (TRUE);
213 end;
214
215 function AfterPForm return boolean is
216 begin
217
218 BEGIN
219 IF P_SORT_ORDER1 = 'SEGMENTX' THEN
220 P_SORT_ORDER1_dup := 'NULL';
221 else
222 p_sort_order1_dup := p_sort_order1;
223 END IF;
224 IF P_SORT_ORDER2 = 'SEGMENTX' THEN
225 P_SORT_ORDER2_dup := 'NULL';
226 else
227 p_sort_order2_dup := p_sort_order2;
228 END IF;
229 IF P_SORT_ORDER3 = 'SEGMENTX' THEN
230 P_SORT_ORDER3_dup := 'NULL';
231 else
232 p_sort_order3_dup := p_sort_order3;
233 END IF;
234 IF P_SORT_ORDER4 = 'SEGMENTX' THEN
235 P_SORT_ORDER4_dup := 'NULL';
236 else
237 p_sort_order4_dup := p_sort_order4;
238 END IF;
239 IF P_SORT_ORDER5 = 'SEGMENTX' THEN
240 P_SORT_ORDER5_dup := 'NULL';
241 else
242 p_sort_order5_dup := p_sort_order5;
243 END IF;
244 IF P_SORT_ORDER6 = 'SEGMENTX' THEN
245 P_SORT_ORDER6_dup := 'NULL';
246 else
247 p_sort_order6_dup := p_sort_order6;
248 END IF;
249
250 END; return (TRUE);
251 end;
252
253 function BeforePForm return boolean is
254 begin
255
256 declare
257 begin
258
259
260
261 null;
262 END;
263
264 return (TRUE);
265 end;
266
267 function cf_euro_amountformula(c_amount_paid in number) return number is
268 calc_amount number;
269 currency_code varchar2(15);
270 begin
271
272 begin
273
274 select currency_code
275 into currency_code
276 from per_business_groups
277 where business_group_id=p_bus_grp_id;
278
279 calc_amount := hr_currency_pkg.convert_amount(
280 currency_code,
281 'EUR',
282 c_pay_date,
283 nvl(c_amount_paid,0),
284 'H');
285 exception
286 when others then
287 calc_amount := 0;
288 end;
289
290 return(calc_amount);
291
292 end;
293
294 function AfterReport return boolean is
295 begin
296 /*srw.user_exit('FND SRWEXIT');*/null;
297
298 return (TRUE);
299 end;
300
301 --Functions to refer Oracle report placeholders--
302
303 Function C_ADDRESS1_p return varchar2 is
304 Begin
305 return C_ADDRESS1;
306 END;
307 Function C_ADDRESS2_p return varchar2 is
308 Begin
309 return C_ADDRESS2;
310 END;
311 Function C_ADDRESS3_p return varchar2 is
312 Begin
313 return C_ADDRESS3;
314 END;
315 Function C_REGION1_p return varchar2 is
316 Begin
317 return C_REGION1;
318 END;
319 Function C_REGION2_p return varchar2 is
320 Begin
321 return C_REGION2;
322 END;
323 Function C_REGION3_p return varchar2 is
324 Begin
325 return C_REGION3;
326 END;
327 Function C_ACCOUNT_NO_p return number is
328 Begin
329 return C_ACCOUNT_NO;
330 END;
331 Function C_TOWN_p return varchar2 is
332 Begin
333 return C_TOWN;
334 END;
335 Function C_ANNUAL_SALARY_p return number is
336 Begin
337 return C_ANNUAL_SALARY;
338 END;
339 Function C_PAY_DATE_p return date is
340 Begin
341 return C_PAY_DATE;
342 END;
343 Function C_TAX_PERIOD_p return varchar2 is
344 Begin
345 return C_TAX_PERIOD;
346 END;
347 Function C_TAX_REFERENCE_NO_p return varchar2 is
348 Begin
349 return C_TAX_REFERENCE_NO;
350 END;
351 Function C_TAX_CODE_p return varchar2 is
352 Begin
353 return C_TAX_CODE;
354 END;
355 Function C_TAX_BASIS_p return varchar2 is
356 Begin
357 return C_TAX_BASIS;
358 END;
359 Function C_NI_CATEGORY_p return varchar2 is
360 Begin
361 return C_NI_CATEGORY;
362 END;
363 Function C_TAX_TEL_NO_p return varchar2 is
364 Begin
365 return C_TAX_TEL_NO;
366 END;
367 Function C_BALANCE_R1_TXT_p return varchar2 is
368 Begin
369 return C_BALANCE_R1_TXT;
370 END;
371 Function C_BALANCE_R1_VAL_p return number is
372 Begin
373 return C_BALANCE_R1_VAL;
374 END;
375 Function C_BALANCE_R2_TXT_p return varchar2 is
376 Begin
377 return C_BALANCE_R2_TXT;
378 END;
379 Function C_BALANCE_R2_VAL_p return number is
380 Begin
381 return C_BALANCE_R2_VAL;
382 END;
383 Function C_BALANCE_R3_TXT_p return varchar2 is
384 Begin
385 return C_BALANCE_R3_TXT;
386 END;
387 Function C_BALANCE_R3_VAL_p return number is
388 Begin
389 return C_BALANCE_R3_VAL;
390 END;
391 Function C_BALANCE_R4_TXT_p return varchar2 is
392 Begin
393 return C_BALANCE_R4_TXT;
394 END;
395 Function C_BALANCE_R4_VAL_p return number is
396 Begin
397 return C_BALANCE_R4_VAL;
398 END;
399 Function C_BALANCE_R5_TXT_p return varchar2 is
400 Begin
401 return C_BALANCE_R5_TXT;
402 END;
403 Function C_BALANCE_R5_VAL_p return number is
404 Begin
405 return C_BALANCE_R5_VAL;
406 END;
407 Function C_BALANCE_R6_TXT_p return varchar2 is
408 Begin
409 return C_BALANCE_R6_TXT;
410 END;
411 Function C_BALANCE_R6_VAL_p return number is
412 Begin
413 return C_BALANCE_R6_VAL;
414 END;
415 Function C_BALANCE_R7_TXT_p return varchar2 is
416 Begin
417 return C_BALANCE_R7_TXT;
418 END;
419 Function C_BALANCE_R7_VAL_p return number is
420 Begin
421 return C_BALANCE_R7_VAL;
422 END;
423 Function C_BALANCE_R8_TXT_p return varchar2 is
424 Begin
425 return C_BALANCE_R8_TXT;
426 END;
427 Function C_BALANCE_R8_VAL_p return number is
428 Begin
429 return C_BALANCE_R8_VAL;
430 END;
431 Function C_BALANCE_R9_TXT_p return varchar2 is
432 Begin
433 return C_BALANCE_R9_TXT;
434 END;
435 Function C_BALANCE_R9_VAL_p return number is
436 Begin
437 return C_BALANCE_R9_VAL;
438 END;
439 Function C_BALANCE_R10_TXT_p return varchar2 is
440 Begin
441 return C_BALANCE_R10_TXT;
442 END;
443 Function C_BALANCE_R10_VAL_p return number is
444 Begin
445 return C_BALANCE_R10_VAL;
446 END;
447 Function C_BALANCE_R11_TXT_p return varchar2 is
448 Begin
449 return C_BALANCE_R11_TXT;
450 END;
451 Function C_BALANCE_R11_VAL_p return number is
452 Begin
453 return C_BALANCE_R11_VAL;
454 END;
455 Function C_BALANCE_R12_TXT_p return varchar2 is
456 Begin
457 return C_BALANCE_R12_TXT;
458 END;
459 Function C_BALANCE_R12_VAL_p return number is
460 Begin
461 return C_BALANCE_R12_VAL;
462 END;
463 Function C_OUTPUT2_p return number is
464 Begin
465 return C_OUTPUT2;
466 END;
467 Function C_OUTPUT_p return number is
468 Begin
469 return C_OUTPUT;
470 END;
471 Function C_2_p return number is
472 Begin
473 return C_2;
474 END;
475 Function C_FORMULA_ID_p return number is
476 Begin
477 return C_FORMULA_ID;
478 END;
479 Function C_DATE_EARNED_p return varchar2 is
480 Begin
481 return C_DATE_EARNED;
482 END;
483 Function C_FORMULA_ID2_p return number is
484 Begin
485 return C_FORMULA_ID2;
486 END;
487 Function C_OUTPUT3_p return varchar2 is
488 Begin
489 return C_OUTPUT3;
490 END;
491
492 /* ADDED BY P.SREEVALLI*/
493 function populate_fields(expense_check_send_to_address IN VARCHAR2,
494 person_id IN NUMBER,segment1c IN VARCHAR2,segment2c IN VARCHAR2,segment3c IN VARCHAR2,segment4c IN VARCHAR2,segment5c IN VARCHAR2,segment6c IN VARCHAR2) return number is
495 l_address1 varchar2(240);
496 l_address2 varchar2(240);
497 l_address3 varchar2(240);
498 l_region1 varchar2(120);
499 l_region2 varchar2(120);
500 l_region3 varchar2(120);
501 l_town varchar2(60);
502 l_cont varchar2(60);
503 l_code varchar2(60);
504
505 begin
506
507 IF expense_check_send_to_address = 'H' THEN
508 pay_gb_payroll_actions_pkg.get_home_add
509 (p_person_id => person_id,
510 p_add1 => l_address1,
511 p_add2 => l_address2,
512 p_add3 => l_address3,
513 p_reg1 => l_region1,
514 p_reg2 => l_region2,
515 p_reg3 => l_region3,
516 p_twnc => l_town);
517
518 c_address1 := substr(l_address1,1,27);
519 c_address2 := substr(l_address2,1,27);
520 c_address3 := substr(l_address3,1,27);
521 c_region1 := substr(l_region1,1,27);
522 c_region2 := substr(l_region2,1,27);
523 c_region3 := substr(l_region3,1,27);
524 c_town := substr(l_town,1,27);
525
526
527
528 elsif (expense_check_send_to_address <> 'H' or expense_check_send_to_address is null) then
529
530 c_address1 := segment1c ;
531 c_address2 := segment2c ;
532 c_address3 := segment3c ;
533 c_town := segment4c ;
534 c_region1 := segment5c ;
535 c_region2 := segment6c ;
536
537 END if;
538
539 return (c_output2);
540 end populate_fields;
541
542
543 function get_ff_data(run_effective_date IN DATE ,date_earned IN DATE,assignment_id IN NUMBER
544 ,run_assignment_action_id IN NUMBER,payroll_action_id IN NUMBER,p_bus_grp_id IN NUMBER)
545 return number is
546
547 l_tax_period varchar2(30);
548 l_tax_reference_no varchar2(30);
549 l_tax_tel_no varchar2(20);
550 L_tax_code varchar2(15);
551 l_tax_basis varchar2(30);
552 l_ni_category varchar2(30);
553
554 l_balance_r1_txt varchar2(20);
555 l_balance_r2_txt varchar2(20);
556 l_balance_r3_txt varchar2(20);
557 l_balance_r4_txt varchar2(20);
558 l_balance_r5_txt varchar2(20);
559 l_balance_r6_txt varchar2(20);
560 L_balance_r7_txt varchar2(20);
561 l_balance_r8_txt varchar2(20);
562 l_balance_r9_txt varchar2(20);
563 l_balance_r10_txt varchar2(20);
564 l_balance_r11_txt varchar2(20);
565 l_balance_r12_txt varchar2(20);
566 l_balance_r1_val number;
567 l_balance_r2_val number;
568 l_balance_r3_val number;
569 l_balance_r4_val number;
570 l_balance_r5_val number;
571 l_balance_r6_val number;
572 l_balance_r7_val number;
573 l_balance_r8_val number;
574 l_balance_r9_val number;
575 l_balance_r10_val number;
576 l_balance_r11_val number;
577 l_balance_r12_val number;
578
579 begin
580
581 c_pay_date := run_effective_date;
582 c_date_earned:= to_char(date_earned,'YYYY/MM/DD');
583
584
585 PAY_GB_PAYROLL_ACTIONS_PKG.get_report_db_items
586 (p_assignment_id => assignment_id,
587 p_run_assignment_action_id => run_assignment_action_id,
588 p_date_earned => to_char(run_effective_date,'YYYY/MM/DD'),
589 p_payroll_action_id => payroll_action_id,
590 p_tax_period => l_tax_period,
591 p_tax_refno => l_tax_reference_no,
592 p_tax_phone => l_tax_tel_no,
593 p_tax_code => l_tax_code,
594 p_tax_basis => l_tax_basis,
595 p_ni_category => l_ni_category);
596
597 c_tax_period := l_tax_period;
598 c_tax_reference_no := l_tax_reference_no;
599 c_tax_tel_no := SUBSTR(l_tax_tel_no,1,11);
600 c_tax_code := l_tax_code;
601 c_tax_basis := l_tax_basis;
602 c_ni_category := l_ni_category;
603
604
605 PAY_GB_PAYROLL_ACTIONS_PKG.get_report_balances
606 (p_assignment_action_id => run_assignment_action_id,
607 p_business_group_id => p_bus_grp_id,
608 p_label_1 => L_BALANCE_R1_TXT,
609 p_value_1 => L_BALANCE_R1_VAL,
610 p_label_2 => L_BALANCE_R2_TXT,
611 p_value_2 => L_BALANCE_R2_VAL,
612 p_label_3 => L_BALANCE_R3_TXT,
613 p_value_3 => L_BALANCE_R3_VAL,
614 p_label_4 => L_BALANCE_R4_TXT,
615 p_value_4 => L_BALANCE_R4_VAL,
616 p_label_5 => L_BALANCE_R5_TXT,
617 p_value_5 => L_BALANCE_R5_VAL,
618 p_label_6 => L_BALANCE_R6_TXT,
619 p_value_6 => L_BALANCE_R6_VAL,
620 p_label_7 => L_BALANCE_R7_TXT,
621 p_value_7 => L_BALANCE_R7_VAL,
622 p_label_8 => L_BALANCE_R8_TXT,
623 p_value_8 => L_BALANCE_R8_VAL,
624 p_label_9 => L_BALANCE_R9_TXT,
625 p_value_9 => L_BALANCE_R9_VAL,
626 p_label_a => L_BALANCE_R10_TXT,
627 p_value_a => L_BALANCE_R10_VAL,
628 p_label_b => L_BALANCE_R11_TXT,
629 p_value_b => L_BALANCE_R11_VAL,
630 p_label_c => L_BALANCE_R12_TXT,
631 p_value_c => L_BALANCE_R12_VAL);
632
633
634
635 c_balance_r1_txt := l_balance_r1_txt;
636 c_balance_r1_val := l_balance_r1_val;
637 c_balance_r2_txt := l_balance_r2_txt;
638 c_balance_r2_val := l_balance_r2_val;
639 c_balance_r3_txt := l_balance_r3_txt;
640 c_balance_r3_val := l_balance_r3_val;
641 c_balance_r4_txt := l_balance_r4_txt;
642 c_balance_r4_val := l_balance_r4_val;
643 c_balance_r5_txt := l_balance_r5_txt;
644 c_balance_r5_val := l_balance_r5_val;
645 c_balance_r6_txt := l_balance_r6_txt;
646 c_balance_r6_val := l_balance_r6_val;
647 c_balance_r7_txt := l_balance_r7_txt;
648 c_balance_r7_val := l_balance_r7_val;
649 c_balance_r8_txt := l_balance_r8_txt;
650 c_balance_r8_val := l_balance_r8_val;
651 c_balance_r9_txt := l_balance_r9_txt;
652 c_balance_r9_val := l_balance_r9_val;
653 c_balance_r10_txt := l_balance_r10_txt;
654 c_balance_r10_val := l_balance_r10_val;
655 c_balance_r11_txt := l_balance_r11_txt;
656 c_balance_r11_val := l_balance_r11_val;
657 c_balance_r12_txt := l_balance_r12_txt;
658 c_balance_r12_val := l_balance_r12_val;
659
660
661
662
663
664
665
666 return (c_output);
667 end;
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694 END PAY_PAYGBSOE_XMLP_PKG ;