1 PACKAGE BODY pay_ca_payreg_extract_pkg AS
2 /* $Header: pycaprpe.pkb 120.9 2011/10/21 09:08:03 sbachu ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1996 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_ca_payreg_extract_pkg
21
22 Description : Package for the Payment Report. The package
23 generated the output file in the specified user
24 format. The current formats supported are
25 - HTML
26 - CSV
27
28 Change List
29 -----------
30 Date Name Vers Bug No Description
31 ---- ---- ------ ------- -----------
32 10-OCT-2001 ssattini 115.0 1380269 Created.
33 20-NOV-2001 ssattini 115.1 Added dbdrv line.
34 05-DEC-2001 ssattini 115.2 Fixed bug#2133040.
35 19-DEC-2001 ssattini 115.3 Fixed bug#2139427,2136857.
36 21-DEC-2001 ssattini 115.4 Changed 'Pay Date' format
37 using
38 fnd_date.date_to_canonical
39 function.
40 08-JAN-2002 ssattini 115.5 Fixed bug#2134726, 2133345,
41 2134807 and 2134821.
42 28-JAN-2002 ssattini 115.6 Fixed bug#2164160 and printed
43 'Total Payment Amount' just
44 below the 'Amount' field.
45 14-MAR-2002 trugless 115.7 Modified c_personal_paymeth_info
46 cursor for utf8 requirements
47 20-MAR-2002 mmukherj 115.8 2271482 Modified the cursor c_assignments
48 to fix the bug 2241782. Since the
49 cursor was joining consolidation_set_id
50 parameter with the consolidation_set_id
51 of pay_all_payrolls_f if the payroll is
52 run for a different consolidation_set_id
53 then the cursor was not picking up any
54 record.Now the cusrsor has been changed
55 so that it checks the consolidation_set_id
56 in pay_payroll_actions table. Also an nvl
57 has been added in this join, so that it
58 picks up records if consolidation_set_id
59 has not been passed from the process.
60 20-MAR-2002 mmukherj 115.9 2271482 Bug no in the history has been correctd.
61 21-MAR-2002 mmukherj 115.10 2271482 Modified the cursor
62 c_personal_paymeth_info, while makimg the
63 UTF8 changes(version 115.7), the decode
64 statement was incorrectly written, and
65 it was fetching incorrect data
66 for bank_number as 'US', even though there
67 were bank_numbers for an employee.
68 The cursor has been corrected to that
69 it brings correct bank_number.
70 30-OCT-2002 TCLEWIS 115.11 Modified the c_payment_period cursor
71 in the payment_extract procedure.
72 Instead of returning paa.serial_number
73 now calling pay_us_employee_payslip_web
74 .get_check_number to retrieve the check
75 number.
76 14-NOV-2002 tclewis 115.12 Changed order of parameters to
77 pay_us_employee_payslip_web.
78 get_check_number. AA_ID PP_ID/
79 22-JAN-2003 ssattini 115.14 2745577 Commented out the validation
80 in c_assignments cursor to
81 print 'Third Party Payments',
82 fix for bug#2745577.
83
84 29-JAN-2003 ssattini 115.15 2745577 Added logic to print two
85 additional columns 'Case/ Court
86 Order Number' and 'Payee Name'
87 to support Third Party payments,
88 fix for bug#2745577.
89 30-JAN-2003 ssattini 115.16 2771166 Changed the c_payment_period and
90 c_payroll_paydate cursors,
91 removed reference to per_time_periods
92 in c_payment_period and added it to
93 c_payroll_paydate cursor.
94 Fix for bug#2771166.
95 30-JAN-2003 ssattini 115.17 Tuned the c_assignments cursor
96 to avoid full table scan on
97 pay_org_payment_method_f and
98 pay_pre_payments tables.
99 03-FEB-2003 ssattini 115.18 2745577 Fixed the issue to print correct
100 Court Order/Case Number when
101 ran with multiple garnishment
102 elements for each assignment,
103 fix for bug#2745577.
104 04-AUG-2003 trugless 115.19 3039110 Replaced payment_labels function
105 with lookup to
106 FND_COMMON_LOOKUPS table using
107 hr_general.decode_fnd_comm_lookup
108 function. Deleted gv_title heading
109 which was not being used.
110 17-DEC-2003 ssattini 115.20 3316062 Modified the cursor c_assignments to
111 to fix the bug#3316062, corrected
112 paa_key inline view to pick up
113 T4A Employee Payments also.
114 08-JAN-2004 ssattini 115.21 3359412 Modified the cursor c_assignments to
115 to fix the 11510 performance bug#3359412.
116 04-MAR-2004 ssattini 115.22 3479270 Modified the cursor c_assignments to
117 to fix the bug#3479270, corrected
118 paa_key inline view to avoid duplicate
119 payment records.
120 23-MAR-2004 ssattini 115.23 3517534 Modified the cursor c_assignments to
121 to fix the bug#3517534, corrected
122 parameter values validation.
123 02-May-2006 ssmukher 115.24 5178951 Added a new column to display whether
124 the Cheque/Third Party cheque/Deposit Advice has been voided.
125 Modified the procedure payment_extract.Added a new cursor
126 c_payment_status.
127 03_May-2006 ssmukher 115.25 5178951 Removed the effective date check from the
128 cursor c_payment_status.
129 16-May-2006 ydevi 115.26 5225939 Modified the code to get the check number and direct deposit
130 number printed for voided payments too in the payment report
131
132 26-Sep-2006 schowta 115.27 5383895 - Following modifications are done. search for 5383895
133 for all the changes done.
134
135 a. pay_ca_payreg_extract_pkg > c_payment_period and
136 other cursors the join to date_earned has been changed to effective_date
137 in all instances except in c_payroll_paydate
138
139 cursor c_tp_pmt_check is merged with c_assignments cursor.
140 c_assignments cursor is modified to include ,popm.defined_balance_id.
141 Associated open cursor is modified to check if it is null.
142 13-Nov-2006 schowta 115.28 Line No. 196 - Observed that "" was missing. Modified to " "
143
144 28-May-2009 sapalani 115.29 7280782 Modified cursor c_assignments in procedure
145 payment_extract. Used paa_pre.tax_unit_id
146 instead of paa_run.tax_unit_id to use the
147 tax unit id from pre-payment action.
148 30-Sep-2011 sbachu 115.31 11076333 Modified cursor c_payment_period so that
149 it returns both void and reissued cheque
150 details.
151 21-Oct-2011 sbachu 115.32 11076333 Modified cursor c_payment_status so that status
152 is displayed correctly for direct deposit. Voided
153 record amount does not add to total from now.*/
154
155 /************************************************************
156 ** Local Package Variables
157 ************************************************************/
158 gc_csv_delimiter VARCHAR2(1) := ',';
159 gc_csv_data_delimiter VARCHAR2(1) := '"';
160
161 gv_html_start_data VARCHAR2(5) := '<td>' ;
162 gv_html_end_data VARCHAR2(5) := '</td>' ;
163
164 gv_package_name VARCHAR2(50) := 'pay_ca_payreg_extract_pkg';
165
166 gv_leg_code VARCHAR2(3);
167 gv_business_group_id NUMBER;
168 gv_tot_amt_lbl VARCHAR2(100) := ' ';
169
170
171 /******************************************************************
172 ** Function Returns the formated input string based on the
173 ** Output format. If the format is CSV then the values are returned
174 ** seperated by comma (,). If the format is HTML then the returned
175 ** string as the HTML tags. The parameter p_bold only works for
176 ** the HTML format.
177 ******************************************************************/
178 FUNCTION formated_data_string
179 (p_input_string in varchar2
180 ,p_output_file_type in varchar2
181 ,p_bold in varchar2 default 'N'
182 )
183 RETURN VARCHAR2
184 IS
185
186 lv_format varchar2(1000);
187
188 BEGIN
189 hr_utility.set_location(gv_package_name || '.formated_data_string', 10);
190 if p_output_file_type = 'CSV' then
191 hr_utility.set_location(gv_package_name || '.formated_data_string', 20);
192 lv_format := gc_csv_data_delimiter || p_input_string ||
193 gc_csv_data_delimiter || gc_csv_delimiter;
194 elsif p_output_file_type = 'HTML' then
195 if ltrim(rtrim(p_input_string)) is null then
196 hr_utility.set_location(gv_package_name ||
197 '.formated_data_string', 30);
198
199 lv_format := gv_html_start_data || ' ' || gv_html_end_data;
200 else
201 if p_bold = 'Y' then
202 hr_utility.set_location(gv_package_name ||
203 '.formated_data_string',40);
204 if p_input_string = gv_tot_amt_lbl then
205 lv_format := '<td align="right" colspan=12>'||
206 '<b> ' || p_input_string|| '</b>' || gv_html_end_data;
207 else
208 lv_format := gv_html_start_data || '<b> ' || p_input_string
209 || '</b>' || gv_html_end_data;
210 end if;
211 else
212 hr_utility.set_location(gv_package_name ||
213 '.formated_data_string',50);
214
215 lv_format := gv_html_start_data || p_input_string ||
216 gv_html_end_data;
217
218 end if;
219 end if;
220 end if;
221
222 hr_utility.set_location(gv_package_name || '.formated_data_string', 60);
223 return lv_format;
224
225 END formated_data_string;
226
227
228 /** Function to get the Column labels for report **/
229
230 FUNCTION payment_labels(p_lookup_type in varchar2,
231 p_lookup_code in varchar2,
232 p_person_language in varchar2 default NULL)
233 return varchar2 IS
234
235 CURSOR get_meaning IS
236 select 1 ord, meaning
237 from fnd_lookup_values
238 where lookup_type = p_lookup_type
239 and lookup_code = p_lookup_code
240 and ( ( p_person_language is null and language = 'US' ) or
241 ( p_person_language is not null and language = p_person_language ) )
242 union all
243 select 2 ord, meaning
244 from fnd_lookup_values
245 where lookup_type = p_lookup_type
246 and lookup_code = p_lookup_code
247 and ( language = 'US' and p_person_language is not null
248 and language <> p_person_language )
249 order by 1;
250
251 lv_meaning varchar2(100);
252 lv_order number;
253
254 BEGIN
255 open get_meaning;
256
257 fetch get_meaning into lv_order,lv_meaning;
258
259 if get_meaning%notfound then
260 lv_meaning := 'xx';
261 end if;
262
263 close get_meaning;
264
265 return lv_meaning;
266
267 END payment_labels;
268
269
270 /************************************************************
271 ** Function returns the string with the HTML Header tags
272 ************************************************************/
273 FUNCTION formated_header_string
274 (p_input_string in varchar2
275 ,p_output_file_type in varchar2
276 )
277 RETURN VARCHAR2
278 IS
279
280 lv_format varchar2(1000);
281
282 BEGIN
283 hr_utility.set_location(gv_package_name || '.formated_header_string', 10);
284 if p_output_file_type = 'CSV' then
285 hr_utility.set_location(gv_package_name|| '.formated_header_string', 20);
286
287 lv_format := p_input_string;
288 elsif p_output_file_type = 'HTML' then
289 hr_utility.set_location(gv_package_name|| '.formated_header_string', 30);
290 lv_format := '<HTML><HEAD> <CENTER> <H1> <B>' || p_input_string ||
291 '</B></H1></CENTER></HEAD>';
292 end if;
293
294 hr_utility.set_location(gv_package_name || '.formated_header_string', 40);
295 return lv_format;
296
297 END formated_header_string;
298
299
300 /*****************************************************************
301 ** This procudure returns the Mandatory Static Labels and the
302 ** Other Additional Static columns. The other static columns are
303 ** printed after all the Payment Information is printed for each
304 ** employee assignment.
305 ** The users can add hooks to this package to print more additional
306 ** data which they require for this report.
307 ** The package prints the user data from a PL/SQL table. The users
308 ** can insert data and the label in this PL/SQL table which will
309 ** be printed at the end of the report.
310 ** The PL/SQL table which needs to be populated is
311 ** LTT_PAYMENT_EXTRACT_DATA. This PL/SQL table is defined in the
312 ** Package pay_ca_payreg_extract_data_pkg (pycaprpd.pkh/pkb).
313 *****************************************************************/
314 PROCEDURE formated_static_header(
315 p_output_file_type in varchar2
316 ,p_static_label1 out NOCOPY varchar2
317 ,p_static_label2 out NOCOPY varchar2
318 )
319 IS
320
321 lv_format1 varchar2(32000);
322 lv_format2 varchar2(32000);
323 lv_bank_code varchar2(20);
324 lv_leg_code varchar2(3);
325
326 BEGIN
327
328 hr_utility.set_location(gv_package_name || '.formated_static_header', 10);
329
330 begin
331 select legislation_code into lv_leg_code
332 from per_business_groups
333 where business_group_id = gv_business_group_id;
334
335 exception
336 when no_data_found then
337 null;
338 end;
339
340 /** The following condition added to check legislation code and print the
341 label for bank information **/
342
343 if lv_leg_code = 'US' then
344 lv_bank_code := 'BNK_NAME';
345 elsif lv_leg_code = 'CA' then
346 lv_bank_code := 'BNK_NUM';
347 end if;
348
349 gv_leg_code := lv_leg_code;
350
351 lv_format1 :=
352 formated_data_string (p_input_string =>
353 hr_general.decode_fnd_comm_lookup
354 ('PAYMENT_REGISTER_LABELS',
355 'PMT_TYPE')
356 ,p_bold => 'Y'
357 ,p_output_file_type => p_output_file_type) ||
358
359 formated_data_string (p_input_string =>
360 hr_general.decode_fnd_comm_lookup
361 ('PAYMENT_REGISTER_LABELS',
362 'PMT_METH')
363 ,p_bold => 'Y'
364 ,p_output_file_type => p_output_file_type) ||
365
366 formated_data_string (p_input_string =>
367 hr_general.decode_fnd_comm_lookup
368 ('PAYMENT_REGISTER_LABELS',
369 'PAY_NAME')
370 ,p_bold => 'Y'
371 ,p_output_file_type => p_output_file_type) ||
372
373 formated_data_string (p_input_string =>
374 hr_general.decode_fnd_comm_lookup
375 ('PAYMENT_REGISTER_LABELS',
376 'GRE')
377 ,p_bold => 'Y'
378 ,p_output_file_type => p_output_file_type) ||
379
380 formated_data_string (p_input_string =>
381 hr_general.decode_fnd_comm_lookup
382 ('PAYMENT_REGISTER_LABELS',
383 'PAY_DATE')
384 ,p_bold => 'Y'
385 ,p_output_file_type => p_output_file_type) ||
386
387 formated_data_string (p_input_string =>
388 hr_general.decode_fnd_comm_lookup
389 ('PAYMENT_REGISTER_LABELS',
390 'PERIOD')
391 ,p_bold => 'Y'
392 ,p_output_file_type => p_output_file_type) ||
393
394 formated_data_string (p_input_string =>
395 hr_general.decode_fnd_comm_lookup
396 ('PAYMENT_REGISTER_LABELS',
397 'EMP_NAME')
398 ,p_bold => 'Y'
399 ,p_output_file_type => p_output_file_type) ||
400
401 formated_data_string (p_input_string =>
402 hr_general.decode_fnd_comm_lookup
403 ('PAYMENT_REGISTER_LABELS',
404 'ASG_NUM')
405 ,p_bold => 'Y'
406 ,p_output_file_type => p_output_file_type) ||
407
408 formated_data_string (p_input_string =>
409 hr_general.decode_fnd_comm_lookup
410 ('PAYMENT_REGISTER_LABELS',
411 'PMT_NUM')
412 ,p_bold => 'Y'
413 ,p_output_file_type => p_output_file_type);
414
415 hr_utility.set_location(gv_package_name || '.formated_static_header', 20);
416
417 lv_format2 :=
418 formated_data_string (p_input_string =>
419 hr_general.decode_fnd_comm_lookup
420 ('PAYMENT_REGISTER_LABELS',
421 lv_bank_code)
422 ,p_bold => 'Y'
423 ,p_output_file_type => p_output_file_type) ||
424
425 formated_data_string (p_input_string =>
426 hr_general.decode_fnd_comm_lookup
427 ('PAYMENT_REGISTER_LABELS',
428 'TRAN_CODE')
429 ,p_bold => 'Y'
430 ,p_output_file_type => p_output_file_type) ||
431
432 formated_data_string (p_input_string =>
433 hr_general.decode_fnd_comm_lookup
434 ('PAYMENT_REGISTER_LABELS',
435 'ACC_NUM')
436 ,p_bold => 'Y'
437 ,p_output_file_type => p_output_file_type) ||
438
439 formated_data_string (p_input_string =>
440 hr_general.decode_fnd_comm_lookup
441 ('PAYMENT_REGISTER_LABELS',
442 'PMT_AMT')
443 ,p_bold => 'Y'
444 ,p_output_file_type => p_output_file_type) ||
445
446 formated_data_string (p_input_string =>
447 hr_general.decode_fnd_comm_lookup
448 ('PAYMENT_REGISTER_LABELS',
449 'CASE_NUM')
450 ,p_bold => 'Y'
451 ,p_output_file_type => p_output_file_type) ||
452
453 formated_data_string (p_input_string =>
454 hr_general.decode_fnd_comm_lookup
455 ('PAYMENT_REGISTER_LABELS',
456 'PAYEE_NAME')
457 ,p_bold => 'Y'
458 ,p_output_file_type => p_output_file_type) ||
459
460 formated_data_string (p_input_string =>
461 hr_general.decode_fnd_comm_lookup
462 ('PAYMENT_REGISTER_LABELS',
463 'PMT_STATUS')
464 ,p_bold => 'Y'
465 ,p_output_file_type => p_output_file_type) ;
466
467
468
469 /*******************************************************************
470 ** Print the User Defined data for each Employee Assignment at the
471 ** end of the report
472 *******************************************************************/
473 hr_utility.set_location(gv_package_name || '.formated_static_header', 30);
474
475
476 /*******************************************************************
477 ** Only do this if there is some configuration data present
478 *******************************************************************/
479 if pay_ca_payreg_extract_data_pkg.ltt_payment_extract_label.count > 0 then
480 for i in pay_ca_payreg_extract_data_pkg.ltt_payment_extract_label.first ..
481 pay_ca_payreg_extract_data_pkg.ltt_payment_extract_label.last
482 loop
483
484 lv_format2 := lv_format2 ||
485 formated_data_string (
486 p_input_string =>
487 pay_ca_payreg_extract_data_pkg.ltt_payment_extract_label(i)
488 ,p_bold => 'Y'
489 ,p_output_file_type => p_output_file_type);
490
491 end loop;
492 end if;
493
494
495 p_static_label1 := lv_format1;
496 p_static_label2 := lv_format2;
497 hr_utility.trace('Static Label1 = ' || lv_format1);
498 hr_utility.trace('Static Label2 = ' || lv_format2);
499 hr_utility.set_location(gv_package_name || '.formated_static_header', 40);
500
501 END;
502
503
504 /*****************************************************************
505 ** This procudure returns the Mandatory Static Labels and the
506 ** Other Additional Static columns. The other static columns are
507 ** printed after all the Payment Information is printed for each
508 ** employee assignment.
509 ** The users can add hooks to this package to print more additional
510 ** data which they require for this report.
511 ** The package prints the user data from a PL/SQL table. The users
512 ** can insert data and the label in this PL/SQL table which will
513 ** be printed at the end of the report.
514 ** The PL/SQL table which needs to be populated is
515 ** LTT_PAYMENT_EXTRACT_DATA. This PL/SQL table is defined in the
516 ** Package pay_ca_payreg_extract_data_pkg (pycaprpd.pkh/pkb).
517 *****************************************************************/
518 /* Added two columns p_case_number, p_payee_name to format the
519 Third Party Payments, to fix bug#2745577 */
520 PROCEDURE formated_static_data(
521 p_employee_full_name in varchar2
522 ,p_employee_number in varchar2
523 ,p_payment_type in varchar2
524 ,p_payment_number in varchar2
525 ,p_bank_number_bank_name in varchar2
526 ,p_transit_code in varchar2
527 ,p_account_number in varchar2
528 ,p_payment_amount in varchar2
529 ,p_payroll_name in varchar2
530 ,p_gre_name in varchar2
531 ,p_period in varchar2
532 ,p_payment_method in varchar2
533 ,p_pay_date in varchar2
534 ,p_case_number in varchar2
535 ,p_payee_name in varchar2
536 ,p_payment_status in varchar2
537 ,p_output_file_type in varchar2
538 ,p_static_data1 out NOCOPY varchar2
539 ,p_static_data2 out NOCOPY varchar2
540 )
541 IS
542
543 lv_format1 VARCHAR2(32000);
544 lv_format2 VARCHAR2(32000);
545 sv_amount varchar2(200);
546
547 BEGIN
548
549 hr_utility.set_location(gv_package_name || '.formated_static_data_tp', 10);
550 lv_format1 :=
551 formated_data_string (p_input_string => p_payment_type
552 ,p_output_file_type => p_output_file_type) ||
553
554 formated_data_string (p_input_string => p_payment_method
555 ,p_output_file_type => p_output_file_type) ||
556
557 formated_data_string (p_input_string => p_payroll_name
558 ,p_output_file_type => p_output_file_type) ||
559
560 formated_data_string (p_input_string => p_gre_name
561 ,p_output_file_type => p_output_file_type) ||
562
563 formated_data_string (p_input_string => p_pay_date
564 ,p_output_file_type => p_output_file_type) ||
565
566 formated_data_string (p_input_string => p_period
567 ,p_output_file_type => p_output_file_type) ||
568 formated_data_string (p_input_string => p_employee_full_name
569 ,p_output_file_type => p_output_file_type) ||
570
571 formated_data_string (p_input_string => p_employee_number
572 ,p_output_file_type => p_output_file_type) ||
573
574 formated_data_string (p_input_string => p_payment_number
575 ,p_output_file_type => p_output_file_type);
576
577 hr_utility.set_location(gv_package_name || '.formated_static_data_tp', 20);
578
579 if p_output_file_type = 'HTML' then
580 sv_amount := '<td align="right">'||p_payment_amount||gv_html_end_data;
581 elsif p_output_file_type = 'CSV' then
582 sv_amount := formated_data_string (p_input_string => p_payment_amount
583 ,p_output_file_type => p_output_file_type);
584 end if;
585
586 lv_format2 :=
587 formated_data_string (p_input_string => p_bank_number_bank_name
588 ,p_output_file_type => p_output_file_type) ||
589
590 formated_data_string (p_input_string => p_transit_code
591 ,p_output_file_type => p_output_file_type) ||
592
593 formated_data_string (p_input_string => p_account_number
594 ,p_output_file_type => p_output_file_type) ||
595 sv_amount ||
596
597 formated_data_string (p_input_string => p_case_number
598 ,p_output_file_type => p_output_file_type) ||
599
600 formated_data_string (p_input_string => p_payee_name
601 ,p_output_file_type => p_output_file_type) ||
602
603 formated_data_string (p_input_string => p_payment_status
604 ,p_output_file_type => p_output_file_type);
605
606
607 /*******************************************************************
608 ** Print the User Defined data for each Employee Assignment at the
609 ** end of the report
610 *******************************************************************/
611 hr_utility.set_location(gv_package_name || '.formated_static_data_tp', 30);
612 hr_utility.trace('Before Loop ');
613
614 hr_utility.trace('Static Data1 = ' || lv_format1);
615 hr_utility.trace('Static Data2 = ' || lv_format2);
616
617 /*******************************************************************
618 ** Only do this if there is some configuration data present
619 *******************************************************************/
620 if pay_ca_payreg_extract_data_pkg.ltt_payment_extract_label.count > 0 then
621
622 for i in pay_ca_payreg_extract_data_pkg.ltt_payment_extract_data.first ..
623 pay_ca_payreg_extract_data_pkg.ltt_payment_extract_data.last
624 loop
625
626 lv_format2 := lv_format2 ||
627 formated_data_string (
628 p_input_string =>
629 pay_ca_payreg_extract_data_pkg.ltt_payment_extract_data(i)
630 ,p_output_file_type => p_output_file_type);
631
632 end loop;
633 end if;
634
635
636 p_static_data1 := lv_format1;
637 p_static_data2 := lv_format2;
638 hr_utility.trace('After Loop ');
639 hr_utility.trace('Static Data1 = ' || lv_format1);
640 hr_utility.trace('Static Data2 = ' || lv_format2);
641 hr_utility.set_location(gv_package_name || '.formated_static_data_tp', 40);
642
643 END;
644
645
646 /*****************************************************************
647 ** This is the main procedure which is called from the Concurrent
648 ** Request. All the paramaters are passed based on which it will
649 ** either print a CSV format or an HTML format file.
650 *****************************************************************/
651 PROCEDURE payment_extract
652 (errbuf out NOCOPY varchar2
653 ,retcode out NOCOPY number
654 ,p_business_group_id in number
655 ,p_start_date in varchar2
656 ,p_end_date in varchar2
657 ,p_payroll_id in number
658 ,p_consolidation_set_id in number
659 ,p_tax_unit_id in number
660 ,p_payment_type_id in number
661 ,p_payment_method_id in number
662 ,p_output_file_type in varchar2
663 )
664 IS
665
666 /************************************************************
667 ** Cursor to get all the employee information, Payment info'n
668 ** and assignment data. This cursor will return one row for each
669 ** Assignment Action for the selection parameters entered by the
670 ** user in the SRS. The Assignment Action returned by this cursor
671 ** is used to retreive the Payment Number and Period Name.
672 ************************************************************/
673 cursor c_assignments (
674 cp_start_date in date
675 ,cp_end_date in date
676 ,cp_payroll_id in number default NULL
677 ,cp_consolidation_set_id in number
678 ,cp_payment_type_id in number default NULL
679 ,cp_tax_unit_id in number default NULL
680 ,cp_payment_method_id in number default NULL
681 ,cp_business_group_id in number
682 ) is
683 select hou.name
684 ,paa_key.tax_unit_id
685 ,ppf.full_name
686 ,ppf.employee_number
687 ,paf.assignment_number
688 ,ppt_tl.payment_type_name
689 ,ppp.value /* Payment Amount */
690 ,ppp.personal_payment_method_id
691 ,popm.org_payment_method_id
692 ,popm_tl.org_payment_method_name
693 ,ppf.person_id
694 ,pap.payroll_name
695 ,ppp.pre_payment_id
696 ,paa_key.assignment_action_id
697 ,paa_key.date_earned
698 ,paa_key.effective_date /* BUG: 5383895 paa_key.date_earned changed to paa_key.effective_date */
699 ,popm.defined_balance_id /* BUG: 5383895 added to avoid the cursor c_tp_pmt_check */
700 from per_all_people_f ppf
701 ,per_all_assignments_f paf
702 ,hr_all_organization_units_tl hou
703 ,pay_all_payrolls_f pap
704 ,pay_payment_types_tl ppt_tl
705 ,pay_payment_types ppt
706 ,pay_org_payment_methods_f_tl popm_tl
707 ,pay_org_payment_methods_f popm
708 ,pay_pre_payments ppp
709 ,(select distinct paa_pre.assignment_action_id /* Locked Action Id */
710 --,paa_run.tax_unit_id -- commented for bug 7280782
711 ,paa_pre.tax_unit_id -- Added for bug 7280782
712 ,ppa_pre.date_earned
713 ,paa_pre.assignment_id
714 ,ppa_pre.payroll_id
715 ,ppa_pre.effective_date /* BUG: 5383895 added ppa_pre.effective_date */
716 from pay_run_types_f prt
717 ,pay_assignment_actions paa_run
718 ,pay_action_interlocks pai
719 ,pay_assignment_actions paa_pre
720 ,pay_payroll_actions ppa_pre
721 where ppa_pre.business_group_id = cp_business_group_id
722 and ppa_pre.effective_date between cp_start_date and cp_end_date /* BUG: 5383895 ppa_pre.date_earned changed to ppa_pre.effective_date */
723 and ppa_pre.action_status = 'C'
724 and ppa_pre.action_type in ('U','P')
725 and ((ppa_pre.consolidation_set_id = cp_consolidation_set_id) OR
726 (cp_consolidation_set_id is NULL))
727 and ppa_pre.payroll_action_id = paa_pre.payroll_action_id
728 and paa_pre.action_status = 'C'
729 and pai.locking_action_id = paa_pre.assignment_action_id
730 and paa_run.assignment_action_id = pai.locked_action_id
731 and ((paa_run.tax_unit_id = cp_tax_unit_id) OR
732 (cp_tax_unit_id is NULL))
733 and paa_run.action_status = 'C'
734 and paa_run.run_type_id is not NULL
735 and prt.run_type_id = paa_run.run_type_id
736 and prt.run_method <> 'C'
737 ) paa_key
738 where pap.business_group_id = cp_business_group_id
739 and pap.payroll_id = paa_key.payroll_id
740 and paa_key.effective_date between pap.effective_start_date
741 and pap.effective_end_date /* BUG: 5383895 paa_key.date_earned changed to paa_key.effective_date */
742 and ((pap.payroll_id = cp_payroll_id) OR
743 (cp_payroll_id is NULL))
744 and ppp.assignment_action_id = paa_key.assignment_action_id
745 and ppp.org_payment_method_id = popm.org_payment_method_id
746 and paa_key.effective_date between popm.effective_start_date and
747 popm.effective_end_date /* BUG: 5383895 paa_key.date_earned changed to paa_key.effective_date */
748 and popm.business_group_id = cp_business_group_id
749 and ((popm.org_payment_method_id = cp_payment_method_id) OR
750 (cp_payment_method_id is NULL))
751 and popm.org_payment_method_id = popm_tl.org_payment_method_id
752 and popm_tl.language = userenv('LANG')
753 and ppt.payment_type_id = popm.payment_type_id
754 and ppt.payment_type_id = ppt_tl.payment_type_id
755 and ppt_tl.language = userenv('LANG')
756 and ((ppt.payment_type_id = cp_payment_type_id) OR
757 (cp_payment_type_id is NULL))
758 and hou.organization_id = paa_key.tax_unit_id
759 and hou.language = userenv('LANG')
760 and paf.assignment_id = paa_key.assignment_id
761 and paa_key.effective_date between paf.effective_start_date and
762 paf.effective_end_date /* BUG: 5383895 paa_key.date_earned changed to paa_key.effective_date */
763 and paf.person_id = ppf.person_id
764 and paa_key.effective_date between ppf.effective_start_date and
765 ppf.effective_end_date /* BUG: 5383895 paa_key.date_earned changed to paa_key.effective_date */
766 order by ppt_tl.payment_type_name,popm_tl.org_payment_method_name,
767 ppf.full_name;
768
769 /*************************************************************
770 ** This cursor returns the Payments processed for a particular
771 ** assignment action and the Payment Amount. The
772 ** cursor also accepts payroll_name, payment_method and
773 ** payment_type as an input. Only the payment amount that is paid
774 ** by the given payment_type or payment_method are returned .
775 **************************************************************/
776 Cursor c_payment_period (
777 cp_start_date in date
778 ,cp_end_date in date
779 ,cp_business_group_id in number
780 ,cp_assignment_action_id in number
781 ,cp_pre_payment_id in number
782 ) is
783
784 /* BUG 11076333 :modified Cursor to get both void and reissued cheque details*/
785 select decode(ppa.action_type,
786 'M', to_char(NVL(ppp.source_action_id,cp_assignment_action_id)),
787 'H', nvl(to_number(paa.serial_number),paa.assignment_action_id),
788 paa.serial_number),
789 paa.assignment_action_id,
790 paa.payroll_action_id,
791 paa.assignment_id,
792 ppa.effective_date
793 from
794 pay_payroll_actions ppa,
795 pay_assignment_actions paa,
796 pay_action_interlocks pai,
797 pay_pre_payments ppp
798 where pai.locked_action_id = cp_assignment_action_id
799 and pai.locking_action_id = paa.assignment_action_id
800 and paa.action_status = 'C'
801 and paa.pre_payment_id = cp_pre_payment_id
802 and paa.payroll_action_id = ppa.payroll_action_id
803 and ppa.effective_date between cp_start_date and cp_end_date /* BUG: 5383895 ppa.date_earned changed to ppa.effective_date */
804 and ppa.business_group_id = cp_business_group_id
805 and ppp.pre_payment_id = paa.pre_payment_id;
806 /*BUG 11076333 ends here*/
807
808 cursor c_personal_paymeth_info (cp_personal_paymeth_id in number
809 default NULL,
810 cp_effective_date in date) IS /* BUG: 5383895 cp_date_earned changed to cp_date_earned */
811 /* New Query to get the Personal Payment Method Information
812 for Payment Report */
813 select decode(gv_leg_code,'CA',
814 decode(pea.segment7,NULL,' ',
815 rtrim(substrb(pea.segment7,1,150))), 'US',--bug 2254026
816 decode(pea.segment5,NULL,' ',
817 rtrim(substrb(pea.segment5,1,150))))
818 /*Per'l Payment Method Bank_number for CA, Bank Name for US */
819 ,pea.segment4 /* Per'l Payment Method Transit_code */
820 ,pea.segment3 /* Per'l Payment Method Account_Number */
821 from pay_personal_payment_methods_f pppm /*added newly to fix bug#2133040 */
822 ,pay_external_accounts pea
823 where pppm.personal_payment_method_id = cp_personal_paymeth_id
824 and cp_effective_date between pppm.effective_start_date and
825 pppm.effective_end_date /* BUG: 5383895 cp_date_earned changed to cp_effective_date */
826 and pppm.external_account_id = pea.external_account_id(+);
827
828
829 cursor c_payroll_paydate (cp_assignment_action_id in number,
830 cp_business_group_id in number) IS
831 /* Query to get the Pay Date of Quickpay or Payroll run */
832 select ppa.effective_date,ptp.period_name
833 from per_time_periods ptp,
834 pay_payroll_actions ppa,
835 pay_assignment_actions paa,
836 pay_action_interlocks pai
837 where pai.locking_action_id = cp_assignment_action_id
838 and pai.locked_action_id = paa.assignment_action_id
839 and paa.action_status = 'C'
840 and paa.run_type_id is not null
841 and paa.payroll_action_id = ppa.payroll_action_id
842 and ppa.action_type in ('Q','R')
843 and ppa.business_group_id = cp_business_group_id
844 and ptp.payroll_id = ppa.payroll_id
845 and ppa.date_earned between ptp.start_date and ptp.end_date;
846
847 /* Third Party Payment Query to get the Court Order/Case number
848 Added this curosr to fix bug#2745577 */
849 cursor c_case_number (cp_asg_id number,
850 cp_effective_date date,
851 cp_persnl_pmt_meth_id number,
852 cp_pmt_amount number) IS /* BUG: 5383895 cp_date_earned changed to cp_effective_date */
853 select peev.screen_entry_value
854 from
855 pay_element_entry_values_f peev,
856 pay_input_values_f piv_att,
857 pay_element_entries_f peef,
858 pay_element_types_f pet
859 where peef.assignment_id = cp_asg_id
860 AND EXISTS (select null from pay_element_links_f pelf
861 where pelf.element_link_id= peef.element_link_id
862 and pelf.element_type_id = pet.element_type_id
863 and cp_effective_date between
864 pelf.effective_start_date and pelf.effective_end_date /* BUG: 5383895 date_earned changed to effective_date */
865 and cp_effective_date between
866 pet.effective_start_date and pet.effective_end_date /* BUG: 5383895 date_earned changed to effective_date */
867 AND pet.third_party_pay_only_flag = 'Y')
868 AND cp_effective_date between
869 peef.effective_start_date and peef.effective_end_date /* BUG: 5383895 date_earned changed to effective_date */
870 AND pet.element_type_id = piv_att.element_type_id
871 AND upper(piv_att.name) = 'ATTACHMENT NUMBER'
872 AND cp_effective_date between
873 piv_att.effective_start_date and piv_att.effective_end_date /* BUG: 5383895 date_earned changed to effective_date */
874 AND peef.element_entry_id = peev.element_entry_id
875 AND piv_att.input_value_id = peev.input_value_id
876 AND cp_effective_date between
877 peev.effective_start_date and peev.effective_end_date /* BUG: 5383895 date_earned changed to effective_date */
878 AND peef.personal_payment_method_id = cp_persnl_pmt_meth_id
879 AND peef.entry_information22 = cp_pmt_amount;
880
881
882 /* Third Party Payment Check flag, to fix bug#2745577 */
883 cursor c_tp_pmt_check (cp_org_pmt_method_id number) IS
884 select 'Y'
885 from pay_org_payment_methods_f
886 where org_payment_method_id = cp_org_pmt_method_id
887 and defined_balance_id is null;
888
889 /* New Query to get the Payee_type, Payee_id
890 for Third Party Payments, to fix bug#2745577 */
891 cursor c_tp_payee_info (cp_personal_paymeth_id number
892 default NULL,
893 cp_effective_date date) IS /* BUG: 5383895 cp_date_earned changed to cp_effective_date */
894 select pppm.payee_type,pppm.payee_id
895 from pay_personal_payment_methods_f pppm
896 where pppm.personal_payment_method_id = cp_personal_paymeth_id
897 and cp_effective_date between pppm.effective_start_date and
898 pppm.effective_end_date; /* BUG: 5383895 cp_date_earned changed to cp_effective_date */
899
900 /* Query to get the payee_name for payee_type 'O'
901 Added this curosr to fix bug#2745577 */
902 cursor c_payee_org_name (cp_payee_id number) IS
903 select name from hr_all_organization_units_tl
904 where organization_id = cp_payee_id
905 and language = userenv('LANG');
906
907 /* Query to get the payee_name for payee_type 'P'
908 Added this curosr to fix bug#2745577 */
909 cursor c_payee_full_name (cp_payee_id number,cp_effective_date date) IS /* BUG: 5383895 cp_date_earned changed to cp_effective_date */
910 select initcap(rtrim(ppf.title))||' '||rtrim(ppf.first_name)||' '||rtrim(ppf.last_name)
911 from per_all_people_f ppf
912 where ppf.person_id = cp_payee_id
913 and cp_effective_date between ppf.effective_start_date and
914 ppf.effective_end_date; /* BUG: 5383895 cp_date_earned changed to cp_effective_date */
915
916 /*************************************************************
917 To fetch the Payment status for Cheques/Deposit Advice Bug#5178951
918 *************************************************************/
919 cursor c_payment_status (p_payact_id number,
920 p_chkno number,
921 p_astact_id number /*BUG 11076333*/
922 ) IS
923 SELECT void_pa.effective_date
924 FROM pay_assignment_actions chq_or_mag_aa,
925 pay_action_interlocks,
926 pay_assignment_actions void_aa,
927 pay_payroll_actions void_pa
928 WHERE chq_or_mag_aa.payroll_action_id = p_payact_id
929 AND ((fnd_number.canonical_to_number(chq_or_mag_aa.serial_number)
930 = p_chkno) OR ( p_chkno is NULL) OR (chq_or_mag_aa.assignment_action_id = p_astact_id)) /*BUG 11076333*/
931 AND locked_action_id = chq_or_mag_aa.assignment_action_id
932 AND locking_action_id = void_aa.assignment_action_id
933 AND void_pa.payroll_action_id = void_aa.payroll_action_id
934 AND void_pa.action_type = 'D';
935
936 /***************************************************************
937 added to fetch the payment number for voided payments bug#5225939
938 ********************************************************************/
939 Cursor c_check_number(cp_pre_payment_action in number
940 ,cp_pre_payment_id in number) is
941 select decode(ppa_pymt.action_type,
942 'M', to_char(NVL(ppp.source_action_id,cp_pre_payment_action)),
943 paa_pymt.serial_number)
944 from pay_pre_payments ppp,
945 pay_assignment_actions paa_pymt,
946 pay_payroll_actions ppa_pymt,
947 pay_action_interlocks pai
948 where pai.locked_action_id = cp_pre_payment_action
949 and paa_pymt.assignment_action_id = pai.locking_action_id
950 and ppa_pymt.payroll_action_id = paa_pymt.payroll_action_id
951 and ppa_pymt.action_type in ('M','H', 'E')
952 and paa_pymt.pre_payment_id = cp_pre_payment_id
953 and ppp.pre_payment_id = paa_pymt.pre_payment_id;
954
955 /*************************************************************
956 ** Local Variables
957 *************************************************************/
958 ln_assignment_action_id NUMBER;
959 ln_assignment_id NUMBER;
960 ln_person_id NUMBER;
961 ld_effective_date DATE;
962 ld_date_earned DATE;
963 lv_action_type VARCHAR2(100);
964 ln_payroll_action_id NUMBER;
965 lv_gre_name VARCHAR2(100);
966 lv_emp_last_name VARCHAR2(100);
967 lv_emp_first_name VARCHAR2(100);
968 lv_emp_middle_names VARCHAR2(100);
969 lv_emp_number VARCHAR2(100);
970 lv_emp_full_name VARCHAR2(200);
971 ld_start_date DATE;
972 ld_end_date DATE;
973 lv_assignment_number VARCHAR2(100);
974 lv_payroll_name VARCHAR2(100);
975 lv_consolidation_set_name VARCHAR2(100);
976 ln_time_period_id NUMBER;
977 lv_period_name VARCHAR2(100);
978 ln_payroll_id NUMBER;
979 ln_tax_unit_id NUMBER;
980 lv_pmt_type_name VARCHAR2(100);
981 lv_check_no NUMBER;
982 ln_pmt_amount NUMBER;
983 ln_pmt_total_amount NUMBER := 0;
984 ln_persnl_pmt_method_id NUMBER;
985 ln_org_pmt_method_id NUMBER;
986 lv_org_pmt_method_name VARCHAR2(100);
987 lv_bank_number VARCHAR2(100);
988 lv_acct_number VARCHAR2(100);
989 ld_pay_date DATE;
990 lv_transit_code VARCHAR2(100);
991 lv_total VARCHAR2(100);
992 lv_total_label VARCHAR2(100);
993 ln_locked_action_id NUMBER;
994 ln_pre_payment_id NUMBER;
995
996 lb_print_row BOOLEAN := FALSE;
997
998 lv_header_label VARCHAR2(32000);
999 lv_header_label1 VARCHAR2(32000);
1000 lv_header_label2 VARCHAR2(32000);
1001
1002 lv_data_row VARCHAR2(32000);
1003 lv_data_row1 VARCHAR2(32000);
1004 lv_data_row2 VARCHAR2(32000);
1005
1006 ln_count NUMBER := 0;
1007
1008 /* Third Party Payment Check variables */
1009 lv_tp_payment_flag VARCHAR2(5):= 'N';
1010 lv_case_number VARCHAR2(25);
1011 lv_payee_type VARCHAR2(5);
1012 lv_payee_name VARCHAR2(200);
1013 ln_payee_id NUMBER := NULL;
1014 lv_payroll_actid NUMBER;
1015 lv_void_date DATE;
1016 lv_payment_status VARCHAR2(10);
1017
1018 ln_defined_balance_id pay_org_payment_methods_f.defined_balance_id%TYPE; /* BUG: 5383895 added */
1019
1020 BEGIN
1021 hr_utility.set_location(gv_package_name || '.payment_extract', 10);
1022 /* hr_utility.trace_on(null, 'ORACLE'); */
1023
1024 hr_utility.trace('Payment Type ID = ' ||
1025 nvl(to_char(p_payment_type_id), 'NULL'));
1026 hr_utility.trace('Payment Method ID = ' ||
1027 nvl(to_char(p_payment_method_id), 'NULL'));
1028 hr_utility.trace('Consolidation Set ID = ' ||
1029 nvl(to_char(p_consolidation_set_id), 'NULL'));
1030 hr_utility.trace('Payroll ID = ' ||
1031 nvl(to_char(p_payroll_id), 'NULL'));
1032 hr_utility.trace('Tax Unit ID = ' ||
1033 nvl(to_char(p_tax_unit_id), 'NULL'));
1034 hr_utility.trace('Business Group Id = ' ||
1035 nvl(to_char(p_business_group_id), 'NULL'));
1036 hr_utility.trace('Start Date = ' ||
1037 nvl(p_start_date, 'NULL'));
1038 hr_utility.trace('End Date = ' ||
1039 nvl(p_end_date, 'NULL'));
1040
1041 gv_business_group_id := p_business_group_id;
1042
1043 formated_static_header( p_output_file_type
1044 ,lv_header_label1
1045 ,lv_header_label2);
1046
1047 lv_header_label := lv_header_label1;
1048
1049 hr_utility.set_location(gv_package_name || '.payment_extract', 70);
1050 /****************************************************************
1051 ** Concatenating the second Header Label which includes the User
1052 ** Defined data set so that it is printed at the end of the
1053 ** report.
1054 ****************************************************************/
1055 lv_header_label := lv_header_label || lv_header_label2;
1056 hr_utility.set_location(gv_package_name || '.payment_extract', 80);
1057 hr_utility.trace('Static and Payment Label = ' || lv_header_label);
1058
1059
1060 fnd_file.put_line(fnd_file.output, formated_header_string(
1061 hr_general.decode_fnd_comm_lookup
1062 ('PAYMENT_REGISTER_LABELS',
1063 'TITLE')
1064 ,p_output_file_type
1065 ));
1066
1067 hr_utility.set_location(gv_package_name || '.payment_extract', 90);
1068
1069 /****************************************************************
1070 ** Print the Header Information. If the format is HTML then open
1071 ** the body and table before printing the header info, otherwise
1072 ** just print the header information.
1073 ****************************************************************/
1074 if p_output_file_type ='HTML' then
1075 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
1076 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
1077 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
1078 end if;
1079 fnd_file.put_line(fnd_file.output, lv_header_label);
1080
1081 if p_output_file_type ='HTML' then
1082 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
1083 end if;
1084
1085 hr_utility.set_location(gv_package_name || '.payment_extract', 100);
1086 /*****************************************************
1087 ** Start of the Data Section of the Report
1088 *****************************************************/
1089 hr_utility.trace('Before open of c_assignments cursor');
1090 open c_assignments( fnd_date.canonical_to_date(p_start_date)
1091 ,fnd_date.canonical_to_date(p_end_date)
1092 ,p_payroll_id
1093 ,p_consolidation_set_id
1094 ,p_payment_type_id
1095 ,p_tax_unit_id
1096 ,p_payment_method_id
1097 ,p_business_group_id
1098 );
1099
1100 loop
1101
1102 fetch c_assignments into lv_gre_name
1103 ,ln_tax_unit_id
1104 ,lv_emp_full_name
1105 ,lv_emp_number
1106 ,lv_assignment_number
1107 ,lv_pmt_type_name
1108 ,ln_pmt_amount
1109 ,ln_persnl_pmt_method_id
1110 ,ln_org_pmt_method_id
1111 ,lv_org_pmt_method_name
1112 ,ln_person_id
1113 ,lv_payroll_name
1114 ,ln_pre_payment_id
1115 ,ln_locked_action_id
1116 ,ld_date_earned
1117 ,ld_effective_date /* BUG: 5383895 added */
1118 ,ln_defined_balance_id; /* BUG: 5383895 added */
1119
1120
1121 if c_assignments%notfound then
1122 hr_utility.set_location(gv_package_name || '.payment_extract', 105);
1123 exit;
1124 end if;
1125
1126 hr_utility.trace('Before open of c_payment_period cursor');
1127 open c_payment_period ( fnd_date.canonical_to_date(p_start_date)
1128 ,fnd_date.canonical_to_date(p_end_date)
1129 ,p_business_group_id
1130 ,ln_locked_action_id
1131 ,ln_pre_payment_id
1132 );
1133 loop /*to get payment_number and period_name*/
1134
1135 fetch c_payment_period into lv_check_no
1136 ,ln_assignment_action_id
1137 ,lv_payroll_actid
1138 ,ln_assignment_id
1139 ,ld_effective_date ;
1140
1141 /***********************************************************************
1142 *** The following condition checks whether the pre-payment is paid ***
1143 *** or not, if its not paid then the c_payment_period cursor doesn't ***
1144 *** return any record and we will not display that record, ***
1145 *** also we will not consider that payment amount for total payment ***
1146 ***********************************************************************/
1147
1148 if c_payment_period%notfound then
1149 hr_utility.set_location(gv_package_name || '.payment_extract',108);
1150 exit;
1151 else
1152 if ln_pmt_amount > 0 then
1153 lb_print_row := TRUE;
1154 /*BUG 11076333*/
1155 /*ln_pmt_total_amount := ln_pmt_total_amount + ln_pmt_amount; */
1156 end if;
1157 end if;
1158
1159 open c_payment_status(lv_payroll_actid,
1160 lv_check_no,
1161 ln_assignment_action_id); /*BUG 11076333*/
1162 fetch c_payment_status into lv_void_date;
1163 IF c_payment_status%found THEN
1164 lv_payment_status := 'Voided';
1165 /*****added against 5225939****/
1166 if lv_check_no is NULL then /*BUG 11076333*/
1167 open c_check_number(ln_locked_action_id ,ln_pre_payment_id);
1168 fetch c_check_number into lv_check_no;
1169 close c_check_number;
1170 end if;
1171 /****end 5225939********/
1172 ELSE
1173 lv_payment_status := ' ';
1174 END IF;
1175 close c_payment_status;
1176 /*BUG 11076333 starts here*/
1177 if ln_pmt_amount > 0 and lv_payment_status <> 'Voided' then
1178 ln_pmt_total_amount := ln_pmt_total_amount + ln_pmt_amount;
1179 end if;
1180 /*BUG 11076333 ends here*/
1181
1182 if ln_persnl_pmt_method_id is NULL then
1183 lv_bank_number := NULL;
1184 lv_transit_code := NULL;
1185 lv_acct_number := NULL;
1186 else
1187 begin
1188
1189 hr_utility.trace('Before open of c_personal_paymeth_info cursor');
1190 open c_personal_paymeth_info(ln_persnl_pmt_method_id,
1191 ld_effective_date); /* BUG: 5383895 ld_date_earned changed to ld_effective_date */
1192
1193 fetch c_personal_paymeth_info into lv_bank_number,
1194 lv_transit_code,
1195 lv_acct_number;
1196
1197 hr_utility.trace('Bank Number = '||lv_bank_number);
1198 hr_utility.trace('Transit code = '||lv_transit_code);
1199 hr_utility.trace('Acct Number = '||lv_acct_number);
1200
1201 if c_personal_paymeth_info%NOTFOUND then
1202 hr_utility.trace('Org_Paymeth found and Personal Paymeth not found ');
1203
1204 lv_bank_number := NULL;
1205 lv_transit_code := NULL;
1206 lv_acct_number := NULL;
1207 end if;
1208 close c_personal_paymeth_info;
1209
1210 exception when others then
1211 hr_utility.trace('Exception in Persl Paymeth Cursor ');
1212 lv_bank_number := NULL;
1213 lv_transit_code := NULL;
1214 lv_acct_number := NULL;
1215 close c_personal_paymeth_info;
1216
1217 end;
1218 end if; /* Validation for personal payment method ends here */
1219
1220 hr_utility.trace('Before open of c_payroll_paydate cursor');
1221 open c_payroll_paydate(ln_locked_action_id
1222 ,p_business_group_id);
1223
1224 fetch c_payroll_paydate into ld_pay_date, lv_period_name;
1225 if c_payroll_paydate%NOTFOUND then
1226 ld_pay_date := null;
1227 lv_period_name := null;
1228 hr_utility.trace('c_payroll_paydate not found ');
1229 hr_utility.trace('Assignment Action ID = '||ln_assignment_action_id);
1230 end if;
1231 close c_payroll_paydate;
1232
1233 /* Third Party Payment Check start1
1234 Added this curosr to fix bug#2745577
1235
1236 hr_utility.trace('Before open of c_tp_pmt_check cursor');
1237 open c_tp_pmt_check(ln_org_pmt_method_id);
1238 fetch c_tp_pmt_check into lv_tp_payment_flag;
1239
1240 if c_tp_pmt_check%NOTFOUND then
1241 lv_tp_payment_flag := null;
1242 hr_utility.trace('c_tp_pmt_check not found ');
1243 hr_utility.trace('Assignment Action ID = '||ln_assignment_action_id);
1244 hr_utility.trace('Org Pmt Method id = '||ln_org_pmt_method_id);
1245 end if;
1246 close c_tp_pmt_check;
1247
1248 If lv_tp_payment_flag = 'Y' then Commented BUG: 5383895 */
1249
1250
1251 hr_utility.trace('Before check ln_defined_balance_id is NULL ');
1252
1253 If ln_defined_balance_id is NULL then /* 5383895 added in place of the c_tp_pmt_check cursor */
1254
1255 hr_utility.trace('Third Party Payment Method found ');
1256 open c_case_number(ln_assignment_id,ld_effective_date,
1257 ln_persnl_pmt_method_id,ln_pmt_amount); /* BUG: 5383895 ld_date_earned changed to ld_effective_date */
1258 fetch c_case_number into lv_case_number;
1259
1260 if c_case_number%NOTFOUND then
1261 lv_case_number := null;
1262 hr_utility.trace('c_case_number not found ');
1263 hr_utility.trace('Assignment ID = '||ln_assignment_id);
1264 end if;
1265 close c_case_number;
1266
1267 open c_tp_payee_info(ln_persnl_pmt_method_id,ld_effective_date ); /* BUG: 5383895 ld_date_earned changed to ld_effective_date */
1268 fetch c_tp_payee_info into lv_payee_type,ln_payee_id;
1269
1270 if c_tp_payee_info%NOTFOUND then
1271 lv_payee_type := null;
1272 ln_payee_id := null;
1273 hr_utility.trace('c_tp_payee_info not found ');
1274 hr_utility.trace('Assignment Action ID = '||
1275 ln_assignment_action_id);
1276 end if;
1277 close c_tp_payee_info;
1278
1279 if lv_payee_type = 'O' and ln_payee_id is not null then
1280 open c_payee_org_name(ln_payee_id);
1281 fetch c_payee_org_name into lv_payee_name;
1282
1283 if c_payee_org_name%NOTFOUND then
1284 lv_payee_name := null;
1285 hr_utility.trace('c_payee_org_name not found ');
1286 hr_utility.trace('Assignment Action ID = '||
1287 ln_assignment_action_id);
1288 end if;
1289 close c_payee_org_name;
1290
1291 end if;
1292
1293 if lv_payee_type = 'P' and ln_payee_id is not null then
1294 open c_payee_full_name(ln_payee_id,ld_effective_date); /* BUG: 5383895 ld_date_earned changed to ld_effective_date */
1295 fetch c_payee_full_name into lv_payee_name;
1296
1297 if c_payee_full_name%NOTFOUND then
1298 lv_payee_name := null;
1299 hr_utility.trace('c_payee_full_name not found ');
1300 hr_utility.trace('Assignment Action ID = '||
1301 ln_assignment_action_id);
1302 end if;
1303 close c_payee_full_name;
1304
1305 end if;
1306
1307 Else
1308 lv_case_number := NULL;
1309 lv_payee_name := NULL;
1310
1311 End if;
1312 /* Third Party Payment Check end1 */
1313
1314 /*BUG 11076333: These 2 lines were shifted below so that void
1315 cheque is also printed in payment report when reissued*/
1316 /* end loop;
1317 close c_payment_period; */
1318
1319 hr_utility.set_location(gv_package_name || '.payment_extract', 110);
1320 hr_utility.trace('Assignment ID = ' || ln_assignment_id);
1321 hr_utility.trace('Assignment Action ID = ' || ln_assignment_action_id);
1322
1323 hr_utility.set_location(gv_package_name || '.payment_extract', 120);
1324 /********************************************************************
1325 ** Populate the user defined PL/SQL table to print the additional
1326 ** columns in the report.
1327 ********************************************************************/
1328 /* pay_ca_payreg_extract_data_pkg.populate_table(
1329 p_assignment_id => ln_assignment_id
1330 ,p_person_id => ln_person_id
1331 ,p_assignment_action_id => ln_assignment_action_id
1332 ,p_effective_date=> ld_effective_date
1333 );
1334 */
1335
1336 hr_utility.set_location(gv_package_name || '.payment_extract', 125);
1337
1338 formated_static_data(
1339 lv_emp_full_name
1340 ,lv_assignment_number
1341 ,lv_pmt_type_name
1342 ,lv_check_no
1343 ,lv_bank_number
1344 ,lv_transit_code
1345 ,lv_acct_number
1346 ,to_char(ln_pmt_amount,'9999999990.00')
1347 ,lv_payroll_name
1348 ,lv_gre_name
1349 ,lv_period_name
1350 ,lv_org_pmt_method_name
1351 ,to_char(ld_pay_date,'YYYY/MM/DD')
1352 ,lv_case_number
1353 ,lv_payee_name
1354 ,lv_payment_status
1355 ,p_output_file_type
1356 ,lv_data_row1
1357 ,lv_data_row2);
1358
1359 lv_data_row := lv_data_row1;
1360 hr_utility.set_location(gv_package_name || '.payment_extract', 130);
1361 hr_utility.trace('Effective Date = ' || to_char(ld_effective_date,
1362 'dd-mon-yyyy'));
1363
1364 hr_utility.trace('Assignment Action ID = ' || ln_assignment_action_id);
1365
1366 hr_utility.trace('Actual Data lv_data_row1 = ' || lv_data_row1);
1367 hr_utility.trace('Actual Data lv_data_row2 = ' || lv_data_row2);
1368
1369 /****************************************************************
1370 ** Concatnating the second Header Label which includes the User
1371 ** Defined data set so that it is printed at the end of the
1372 ** report.
1373 ****************************************************************/
1374
1375 /*** The following condition added to print only Non-Zero Payments ***/
1376 if lb_print_row then
1377
1378 lv_data_row := lv_data_row || lv_data_row2;
1379
1380 if p_output_file_type ='HTML' then
1381 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1382 end if;
1383
1384 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1385
1386 end if;
1387
1388 /*** End of condition for Non-Zero Payments ***/
1389
1390 /*BUG 11076333 starts here*/
1391 end loop;
1392 close c_payment_period;
1393 /* BUG 11076333 ends here*/
1394
1395 /*****************************************************************
1396 ** initialize Print Row valiable again
1397 *****************************************************************/
1398 lb_print_row := FALSE;
1399
1400 /*****************************************************************
1401 ** initialize Data varaibles
1402 *****************************************************************/
1403 lv_data_row := null;
1404 lv_data_row1 := null;
1405 lv_data_row2 := null;
1406 lv_tp_payment_flag := 'N';
1407 lv_payee_type := null;
1408 ln_payee_id := null;
1409
1410 end loop;
1411 close c_assignments;
1412
1413 /*** Added to print Payment_Total for output_type HTML only ***/
1414
1415 if p_output_file_type ='HTML' then
1416
1417
1418 gv_tot_amt_lbl := hr_general.decode_fnd_comm_lookup --bug 3039110
1419 ('PAYMENT_REGISTER_LABELS',
1420 'TOT_PMT_AMT');
1421 -- gv_tot_amt_lbl := payment_labels('PAYMENT_REGISTER_LABELS',
1422 -- 'TOT_PMT_AMT');
1423 lv_total_label :=
1424 formated_data_string (p_input_string =>
1425 hr_general.decode_fnd_comm_lookup --bug 3039110
1426 ('PAYMENT_REGISTER_LABELS',
1427 'TOT_PMT_AMT')
1428 ,p_bold => 'Y'
1429 ,p_output_file_type =>
1430 p_output_file_type);
1431
1432 lv_total := '<td align="right">'||'<b> '||
1433 to_char(ln_pmt_total_amount,'999999999990.00')||'</b>'||
1434 gv_html_end_data;
1435
1436 /* lv_total := formated_data_string (p_input_string =>
1437 to_char(ln_pmt_total_amount,
1438 '999999999990.00')
1439 ,p_bold => 'Y'
1440 ,p_output_file_type => p_output_file_type);
1441 */
1442
1443 lv_data_row := lv_total_label||lv_total;
1444 lv_data_row := '<tr>'||lv_data_row||'</tr>';
1445
1446 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1447
1448 lv_data_row := null;
1449 end if;
1450
1451 /*** Payment Total Print ends here ***/
1452
1453 /*****************************************************
1454 ** Close of the Data Section of the Report
1455 *****************************************************/
1456 if p_output_file_type ='HTML' then
1457 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
1458 end if;
1459 hr_utility.trace('Concurrent Request ID = ' || FND_GLOBAL.CONC_REQUEST_ID);
1460
1461
1462 /**********************************************************
1463 ** Not Required as the output file type is HTML by default
1464 ***********************************************************
1465 if p_output_file_type ='HTML' then
1466 update fnd_concurrent_requests
1467 set output_file_type = 'HTML'
1468 where request_id = FND_GLOBAL.CONC_REQUEST_ID ;
1469
1470 commit;
1471 end if;
1472 **********************************************************/
1473
1474 gv_leg_code := NULL;
1475 /* hr_utility.trace_off; */
1476
1477 END payment_extract;
1478
1479 end pay_ca_payreg_extract_pkg;
[Home] [Help]