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