[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_WF_PKG
Source
1 PACKAGE BODY pay_ca_wf_pkg AS
2 /* $Header: paycawfpkg.pkb 120.5 2012/01/19 07:55:56 rpahune ship $*/
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 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 Package Body Name : pay_ca_wf_pkg
21 Package File Name : paycawfpkg.pkb
22 Description : This package declares functions which initiate the
23 Canadian Payroll Workflow
24
25 Change List
26 -----------
27 Date Name Vers Bug No Description
28 ----------- ---------- ------ ------- --------------------------
29 27-APR-2004 ssouresr 115.0 Created
30 12-JUL-2004 ssouresr 115.2 Retrieved User from correct
31 Payroll segment in function
32 get_notifier
33 21-JUL-2004 ssouresr 115.3 Changed business group and
34 batch id attributes to varchar2
35 Also made changes so that the
36 assignment set used by the RetroPay
37 by Element process is the same as
38 for Retro-Notification if one exists
39 23-Jun-2011 rgottipa 115.4 12548555 Override the procedures
40 payroll_wf_process and StartProcess
41 30-Jun-2011 rgottipa 115.5 12548555 Handle the extra parameters for
42 Direct Deposit.
43 15-Jul-2011 rgottipa 115.6 12704242 Added parameters to overloaded procedures
44 payroll_wf_process and StartProcess
45 to call Retro-Notification (enhanced) PDF
46 and RetroPay (enhanced)
47
48 *******************************************************************/
49
50 /******************************************************************
51 ** private package global declarations
52 ******************************************************************/
53
54
55 procedure payroll_wf_process(errbuf OUT nocopy varchar2,
56 retcode OUT nocopy number,
57 p_wf_item_type varchar2,
58 p_business_group_id number ,
59 p_batch_id number ,
60 p_payroll_id varchar2,
61 p_consolidation_set_id varchar2,
62 p_date_earned varchar2,
63 p_date_paid varchar2,
64 p_retro_event_group varchar2,
65 p_retro_assignment_set varchar2,
66 p_retropay_assignment_set varchar2,
67 p_retropay_element_set varchar2,
68 p_retro_start_date varchar2,
69 p_payroll_element_set varchar2,
70 p_payroll_assignment_set varchar2,
71 p_payroll_run_type varchar2,
72 p_gre_or_tax_group varchar2,
73 p_is_gre varchar2,
74 p_gre varchar2,
75 p_is_tax_group varchar2,
76 p_tax_group varchar2,
77 p_gross_to_net_period varchar2,
78 p_payroll_process varchar2,
79 p_session_date varchar2,
80 p_organization varchar2,
81 p_location varchar2,
82 p_quebec varchar2,
83 p_qhsf_account_number varchar2,
84 p_qhsf_override_table varchar2,
85 p_sdr_federal varchar2,
86 p_sdr_province varchar2,
87 p_sdr_reporting_dimension varchar2,
88 p_exp_legislation_code varchar2,
89 p_exp_report_or_group varchar2,
90 p_exp_is_group varchar2,
91 p_exp_group_name varchar2,
92 p_exp_is_report varchar2,
93 p_exp_report_name varchar2,
94 p_exp_variance_type varchar2,
95 p_exp_variance_value varchar2,
96 p_payment_method_override varchar2,
97 p_ele_selection_criterion varchar2,
98 p_ele_is_element_set varchar2,
99 p_ele_element_set varchar2,
100 p_ele_is_element_class varchar2,
101 p_ele_element_class varchar2,
102 p_ele_is_element_name varchar2,
103 p_ele_element_name varchar2,
104 p_ele_employee varchar2,
105 p_deduction_class varchar2,
106 p_deduction_name varchar2,
107 p_deduction_sort_one varchar2,
108 p_deduction_sort_two varchar2,
109 p_deduction_sort_three varchar2,
110 p_reg_suppress_zero varchar2,
111 p_reg_full_report_ver varchar2,
112 p_reg_reporting_dim varchar2,
113 p_reg_sort_one varchar2,
114 p_reg_sort_two varchar2,
115 p_reg_sort_three varchar2,
116 p_reg_employee_page_break varchar2,
117 p_reg_req_num varchar2,
118 p_dd_payment_type varchar2,
119 p_dd_payment_method varchar2,
120 p_dd_override_date varchar2,
121 p_dd_financial_inst varchar2,
122 p_dd_cpa_code varchar2,
123 p_dd_file_number varchar2,
124 p_cheque_payment_type varchar2,
125 p_cheque_payment_method varchar2,
126 p_cheque_sort_sequence varchar2,
127 p_cheque_style varchar2,
128 p_start_cheque_number varchar2,
129 p_end_cheque_number varchar2,
130 p_da_data_type varchar2,
131 p_payment_rep_payment_type varchar2,
132 p_payment_rep_payment_method varchar2,
133 p_roe_assignment_set varchar2,
134 p_roe_worksheet_person varchar2,
135 p_roe_mag_media_person varchar2,
136 p_roe_mag_roe_type varchar2,
137 p_cost_summary_accruals varchar2,
138 p_cost_summary_file_out varchar2,
139 p_cost_detail_selection varchar2,
140 p_cost_detail_is_ele_set varchar2,
141 p_cost_detail_ele_set varchar2,
142 p_cost_detail_is_class varchar2,
143 p_cost_detail_class varchar2,
144 p_cost_detail_is_element varchar2,
145 p_cost_detail_element varchar2,
146 p_cost_detail_asg_set varchar2,
147 p_cost_detail_accruals varchar2
148 ) is
149
150 l_workflowprocess varchar2(100);
151 l_ProcessDesc varchar2(100);
152 l_RequestorUsername varchar2(100) := 'SYSADMIN';
153 l_ProcessOwner varchar2(100);
154 l_item_type varchar2(100);
155 l_item_key varchar2(100);
156 lv_runnable_process varchar2(1);
157
158
159 begin
160 --hr_utility.trace_on(null,'PYWF');
161
162 gv_package := 'pay_ca_wf_pkg';
163
164 select to_char(sysdate,'DDHH24MISS') into l_item_key from dual;
165
166 -- initialise variable - 0 is SRS Success, 1 is SRS Warning, 2 is SRS Error
167 retcode := 0;
168
169
170 l_workflowprocess := 'PAYCAPROCESSWF';
171 l_item_type := p_wf_item_type; -- PAYCAPWF
172
173 begin
174 select runnable_flag
175 into lv_runnable_process
176 from wf_activities
177 where item_type = p_wf_item_type
178 and type = 'PROCESS'
179 and name = l_workflowprocess
180 and end_date is null;
181
182 exception when no_data_found then
183 hr_utility.trace('Exception: No Data Found in payroll_wf_process');
184 null;
185
186 end;
187
188 hr_utility.trace('Item Type : '|| l_item_type);
189 hr_utility.trace('Item Key : '|| l_item_key);
190 hr_utility.trace('Workflow Process : '|| l_workflowprocess);
191
192 if lv_runnable_process = 'Y' then
193
194 StartProcess(p_business_group_id,
195 p_batch_id,
196 p_payroll_id,
197 p_consolidation_set_id,
198 p_date_earned,
199 p_date_paid,
200 p_retro_event_group,
201 p_retro_assignment_set,
202 p_retropay_assignment_set,
203 p_retropay_element_set,
204 p_retro_start_date,
205 p_payroll_element_set,
206 p_payroll_assignment_set,
207 p_payroll_run_type,
208 p_gre_or_tax_group,
209 p_is_gre,
210 p_gre,
211 p_is_tax_group,
212 p_tax_group,
213 p_gross_to_net_period,
214 p_payroll_process,
215 p_session_date,
216 p_organization,
217 p_location,
218 p_quebec,
219 p_qhsf_account_number,
220 p_qhsf_override_table,
221 p_sdr_federal,
222 p_sdr_province,
223 p_sdr_reporting_dimension,
224 p_exp_legislation_code,
225 p_exp_report_or_group,
226 p_exp_is_group,
227 p_exp_group_name,
228 p_exp_is_report,
229 p_exp_report_name,
230 p_exp_variance_type,
231 p_exp_variance_value,
232 p_payment_method_override,
233 p_ele_selection_criterion,
234 p_ele_is_element_set,
235 p_ele_element_set,
236 p_ele_is_element_class,
237 p_ele_element_class,
238 p_ele_is_element_name,
239 p_ele_element_name,
240 p_ele_employee,
241 p_deduction_class,
242 p_deduction_name,
243 p_deduction_sort_one,
244 p_deduction_sort_two,
245 p_deduction_sort_three,
246 p_reg_suppress_zero,
247 p_reg_full_report_ver,
248 p_reg_reporting_dim,
249 p_reg_sort_one,
250 p_reg_sort_two,
251 p_reg_sort_three,
252 p_reg_employee_page_break,
253 p_reg_req_num,
254 p_dd_payment_type,
255 p_dd_payment_method,
256 p_dd_override_date,
257 p_dd_financial_inst,
258 p_dd_cpa_code,
259 p_dd_file_number,
260 p_cheque_payment_type,
261 p_cheque_payment_method,
262 p_cheque_sort_sequence,
263 p_cheque_style,
264 p_start_cheque_number,
265 p_end_cheque_number,
266 p_da_data_type,
267 p_payment_rep_payment_type,
268 p_payment_rep_payment_method,
269 p_roe_assignment_set,
270 p_roe_worksheet_person,
271 p_roe_mag_media_person,
272 p_roe_mag_roe_type,
273 p_cost_summary_accruals,
274 p_cost_summary_file_out,
275 p_cost_detail_selection,
276 p_cost_detail_is_ele_set,
277 p_cost_detail_ele_set,
278 p_cost_detail_is_class,
279 p_cost_detail_class,
280 p_cost_detail_is_element,
281 p_cost_detail_element,
282 p_cost_detail_asg_set,
283 p_cost_detail_accruals,
284 l_ProcessDesc,
285 l_RequestorUsername,
286 l_ProcessOwner,
287 l_workflowprocess,
288 l_item_type,
289 l_item_key
290 );
291
292 end if;
293
294 exception
295
296 when hr_utility.hr_error then
297 --
298 -- Set up error message and error return code.
299 --
300 hr_utility.trace('in the exception');
301
302 errbuf := hr_utility.get_message;
303 retcode := 2;
304
305 when others then
306
307 -- Set up error message and return code.
308 errbuf := sqlerrm;
309 retcode := 2;
310
311 end payroll_wf_process;
312
313 procedure payroll_wf_process(errbuf OUT nocopy varchar2,
314 retcode OUT nocopy number,
315 p_wf_item_type varchar2,
316 p_business_group_id number ,
317 p_batch_id number ,
318 p_payroll_id varchar2,
319 p_consolidation_set_id varchar2,
320 p_date_earned varchar2,
321 p_date_paid varchar2,
322 p_retro_event_group varchar2,
323 p_retro_valid_template_list varchar2,--bug 12704242
324 p_retro_template_name varchar2,--bug 12704242
325 p_retro_assignment_set varchar2,
326 p_retro_generate_report varchar2,--bug 12704242
327 p_retropay_assignment_set varchar2,
328 p_retropay_element_set varchar2,
329 p_retro_start_date varchar2,
330 p_retropay_entry_creation_date varchar2, --bug 12704242
331 p_payroll_element_set varchar2,
332 p_payroll_assignment_set varchar2,
333 p_payroll_run_type varchar2,
334 p_gre_or_tax_group varchar2,
335 p_is_gre varchar2,
336 p_gre varchar2,
337 p_is_tax_group varchar2,
338 p_tax_group varchar2,
339 p_gross_to_net_period varchar2,
340 p_payroll_process varchar2,
341 p_session_date varchar2,
342 p_organization varchar2,
343 p_location varchar2,
344 p_quebec varchar2,
345 p_qhsf_account_number varchar2,
346 p_qhsf_override_table varchar2,
347 p_sdr_federal varchar2,
348 p_sdr_province varchar2,
349 p_sdr_reporting_dimension varchar2,
350 p_exp_legislation_code varchar2,
351 p_exp_report_or_group varchar2,
352 p_exp_is_group varchar2,
353 p_exp_group_name varchar2,
354 p_exp_is_report varchar2,
355 p_exp_report_name varchar2,
356 p_exp_variance_type varchar2,
357 p_exp_variance_value varchar2,
358 p_payment_method_override varchar2,
359 p_ele_selection_criterion varchar2,
360 p_ele_is_element_set varchar2,
361 p_ele_element_set varchar2,
362 p_ele_is_element_class varchar2,
363 p_ele_element_class varchar2,
364 p_ele_is_element_name varchar2,
365 p_ele_element_name varchar2,
366 p_ele_employee varchar2,
367 p_deduction_class varchar2,
368 p_deduction_name varchar2,
369 p_deduction_sort_one varchar2,
370 p_deduction_sort_two varchar2,
371 p_deduction_sort_three varchar2,
372 p_reg_suppress_zero varchar2,
373 p_reg_full_report_ver varchar2,
374 p_reg_reporting_dim varchar2,
375 p_reg_sort_one varchar2,
376 p_reg_sort_two varchar2,
377 p_reg_sort_three varchar2,
378 p_reg_employee_page_break varchar2,
379 p_reg_req_num varchar2,
380 p_dd_payment_type varchar2,
381 p_dd_payment_method varchar2,
382 p_dd_override_date varchar2,
383 p_dd_financial_inst varchar2,
384 p_dd_cpa_code varchar2,
385 p_dd_file_number varchar2,
386 p_cheque_payment_type varchar2,
387 p_cheque_payment_method varchar2,
388 p_cheque_sort_sequence varchar2,
389 p_cheque_style varchar2,
390 p_start_cheque_number varchar2,
391 p_end_cheque_number varchar2,
392 p_da_data_type varchar2,
393 p_payment_rep_payment_type varchar2,
394 p_payment_rep_payment_method varchar2,
395 p_roe_assignment_set varchar2,
396 p_roe_worksheet_person varchar2,
397 p_roe_mag_media_person varchar2,
398 p_roe_mag_roe_type varchar2,
399 p_cost_summary_accruals varchar2,
400 p_cost_summary_file_out varchar2,
401 p_cost_detail_selection varchar2,
402 p_cost_detail_is_ele_set varchar2,
403 p_cost_detail_ele_set varchar2,
404 p_cost_detail_is_class varchar2,
405 p_cost_detail_class varchar2,
406 p_cost_detail_is_element varchar2,
407 p_cost_detail_element varchar2,
408 p_cost_detail_asg_set varchar2,
409 p_cost_detail_accruals varchar2,
410 p_action_parameter_group varchar2 -- bug 12548555
411 ) is
412
413 l_workflowprocess varchar2(100);
414 l_ProcessDesc varchar2(100);
415 l_RequestorUsername varchar2(100) := 'SYSADMIN';
416 l_ProcessOwner varchar2(100);
417 l_item_type varchar2(100);
418 l_item_key varchar2(100);
419 lv_runnable_process varchar2(1);
420
421
422 begin
423 --hr_utility.trace_on(null,'PYWF');
424
425 gv_package := 'pay_ca_wf_pkg';
426
427 select to_char(sysdate,'DDHH24MISS') into l_item_key from dual;
428
429 -- initialise variable - 0 is SRS Success, 1 is SRS Warning, 2 is SRS Error
430 retcode := 0;
431
432
433 l_workflowprocess := 'PAYCAPROCESSWF';
434 l_item_type := p_wf_item_type; -- PAYCAPWF
435
436 begin
437 select runnable_flag
438 into lv_runnable_process
439 from wf_activities
440 where item_type = p_wf_item_type
441 and type = 'PROCESS'
442 and name = l_workflowprocess
443 and end_date is null;
444
445 exception when no_data_found then
446 hr_utility.trace('Exception: No Data Found in payroll_wf_process');
447 null;
448
449 end;
450
451 hr_utility.trace('Item Type : '|| l_item_type);
452 hr_utility.trace('Item Key : '|| l_item_key);
453 hr_utility.trace('Workflow Process : '|| l_workflowprocess);
454
455 if lv_runnable_process = 'Y' then
456
457 StartProcess(p_business_group_id,
458 p_batch_id,
459 p_payroll_id,
460 p_consolidation_set_id,
461 p_date_earned,
462 p_date_paid,
463 p_retro_event_group,
464 p_retro_valid_template_list,--bug 12704242
465 p_retro_template_name,--bug 12704242
466 p_retro_assignment_set,
467 p_retro_generate_report,--bug 12704242
468 p_retropay_assignment_set,
469 p_retropay_element_set,
470 p_retro_start_date,
471 p_retropay_entry_creation_date, --bug 12704242
472 p_payroll_element_set,
473 p_payroll_assignment_set,
474 p_payroll_run_type,
475 p_gre_or_tax_group,
476 p_is_gre,
477 p_gre,
478 p_is_tax_group,
479 p_tax_group,
480 p_gross_to_net_period,
481 p_payroll_process,
482 p_session_date,
483 p_organization,
484 p_location,
485 p_quebec,
486 p_qhsf_account_number,
487 p_qhsf_override_table,
488 p_sdr_federal,
489 p_sdr_province,
490 p_sdr_reporting_dimension,
491 p_exp_legislation_code,
492 p_exp_report_or_group,
493 p_exp_is_group,
494 p_exp_group_name,
495 p_exp_is_report,
496 p_exp_report_name,
497 p_exp_variance_type,
498 p_exp_variance_value,
499 p_payment_method_override,
500 p_ele_selection_criterion,
501 p_ele_is_element_set,
502 p_ele_element_set,
503 p_ele_is_element_class,
504 p_ele_element_class,
505 p_ele_is_element_name,
506 p_ele_element_name,
507 p_ele_employee,
508 p_deduction_class,
509 p_deduction_name,
510 p_deduction_sort_one,
511 p_deduction_sort_two,
512 p_deduction_sort_three,
513 p_reg_suppress_zero,
514 p_reg_full_report_ver,
515 p_reg_reporting_dim,
516 p_reg_sort_one,
517 p_reg_sort_two,
518 p_reg_sort_three,
519 p_reg_employee_page_break,
520 p_reg_req_num,
521 p_dd_payment_type,
522 p_dd_payment_method,
523 p_dd_override_date,
524 p_dd_financial_inst,
525 p_dd_cpa_code,
526 p_dd_file_number,
527 p_cheque_payment_type,
528 p_cheque_payment_method,
529 p_cheque_sort_sequence,
530 p_cheque_style,
531 p_start_cheque_number,
532 p_end_cheque_number,
533 p_da_data_type,
534 p_payment_rep_payment_type,
535 p_payment_rep_payment_method,
536 p_roe_assignment_set,
537 p_roe_worksheet_person,
538 p_roe_mag_media_person,
539 p_roe_mag_roe_type,
540 p_cost_summary_accruals,
541 p_cost_summary_file_out,
542 p_cost_detail_selection,
543 p_cost_detail_is_ele_set,
544 p_cost_detail_ele_set,
545 p_cost_detail_is_class,
546 p_cost_detail_class,
547 p_cost_detail_is_element,
548 p_cost_detail_element,
549 p_cost_detail_asg_set,
550 p_cost_detail_accruals,
551 p_action_parameter_group, --bug 12548555
552 l_ProcessDesc,
553 l_RequestorUsername,
554 l_ProcessOwner,
555 l_workflowprocess,
556 l_item_type,
557 l_item_key
558 );
559
560 end if;
561
562 exception
563
564 when hr_utility.hr_error then
565 --
566 -- Set up error message and error return code.
567 --
568 hr_utility.trace('in the exception');
569
570 errbuf := hr_utility.get_message;
571 retcode := 2;
572
573 when others then
574
575 -- Set up error message and return code.
576 errbuf := sqlerrm;
577 retcode := 2;
578
579 end payroll_wf_process;
580
581 -- Start Workflow Process will Create a Process and Set the Attributes
582 -- for the Workflow Process.
583
584
585 procedure StartProcess (
586 p_business_group_id number,
587 p_batch_id number,
588 p_payroll_id varchar2,
589 p_consolidation_set_id varchar2,
590 p_date_earned varchar2,
591 p_date_paid varchar2,
592 p_retro_event_group varchar2,
593 p_retro_assignment_set varchar2,
594 p_retropay_assignment_set varchar2,
595 p_retropay_element_set varchar2,
596 p_retro_start_date varchar2,
597 p_payroll_element_set varchar2,
598 p_payroll_assignment_set varchar2,
599 p_payroll_run_type varchar2,
600 p_gre_or_tax_group varchar2,
601 p_is_gre varchar2,
602 p_gre varchar2,
603 p_is_tax_group varchar2,
604 p_tax_group varchar2,
605 p_gross_to_net_period varchar2,
606 p_payroll_process varchar2,
607 p_session_date varchar2,
608 p_organization varchar2,
609 p_location varchar2,
610 p_quebec varchar2,
611 p_qhsf_account_number varchar2,
612 p_qhsf_override_table varchar2,
613 p_sdr_federal varchar2,
614 p_sdr_province varchar2,
615 p_sdr_reporting_dimension varchar2,
616 p_exp_legislation_code varchar2,
617 p_exp_report_or_group varchar2,
618 p_exp_is_group varchar2,
619 p_exp_group_name varchar2,
620 p_exp_is_report varchar2,
621 p_exp_report_name varchar2,
622 p_exp_variance_type varchar2,
623 p_exp_variance_value varchar2,
624 p_payment_method_override varchar2,
625 p_ele_selection_criterion varchar2,
626 p_ele_is_element_set varchar2,
627 p_ele_element_set varchar2,
628 p_ele_is_element_class varchar2,
629 p_ele_element_class varchar2,
630 p_ele_is_element_name varchar2,
631 p_ele_element_name varchar2,
632 p_ele_employee varchar2,
633 p_deduction_class varchar2,
634 p_deduction_name varchar2,
635 p_deduction_sort_one varchar2,
636 p_deduction_sort_two varchar2,
637 p_deduction_sort_three varchar2,
638 p_reg_suppress_zero varchar2,
639 p_reg_full_report_ver varchar2,
640 p_reg_reporting_dim varchar2,
641 p_reg_sort_one varchar2,
642 p_reg_sort_two varchar2,
643 p_reg_sort_three varchar2,
644 p_reg_employee_page_break varchar2,
645 p_reg_req_num varchar2,
646 p_dd_payment_type varchar2,
647 p_dd_payment_method varchar2,
648 p_dd_override_date varchar2,
649 p_dd_financial_inst varchar2,
650 p_dd_cpa_code varchar2,
651 p_dd_file_number varchar2,
652 p_cheque_payment_type varchar2,
653 p_cheque_payment_method varchar2,
654 p_cheque_sort_sequence varchar2,
655 p_cheque_style varchar2,
656 p_start_cheque_number varchar2,
657 p_end_cheque_number varchar2,
658 p_da_data_type varchar2,
659 p_payment_rep_payment_type varchar2,
660 p_payment_rep_payment_method varchar2,
661 p_roe_assignment_set varchar2,
662 p_roe_worksheet_person varchar2,
663 p_roe_mag_media_person varchar2,
664 p_roe_mag_roe_type varchar2,
665 p_cost_summary_accruals varchar2,
666 p_cost_summary_file_out varchar2,
667 p_cost_detail_selection varchar2,
668 p_cost_detail_is_ele_set varchar2,
669 p_cost_detail_ele_set varchar2,
670 p_cost_detail_is_class varchar2,
671 p_cost_detail_class varchar2,
672 p_cost_detail_is_element varchar2,
673 p_cost_detail_element varchar2,
674 p_cost_detail_asg_set varchar2,
675 p_cost_detail_accruals varchar2,
676 ProcessDesc in varchar2,
677 RequestorUsername in varchar2,
678 ProcessOwner in varchar2,
679 Workflowprocess in varchar2 default null,
680 item_type in varchar2 default null,
681 item_key in varchar2
682 ) is
683
684 ItemType varchar2(30) := item_type;
685 ItemKey varchar2(30) := item_key;
686 ItemUserKey varchar2(80) := ProcessDesc;
687 l_business_group_id number(30) := p_business_group_id;
688 l_payroll_id number(16) := to_number(p_payroll_id);
689 l_consolidation_set_id number(16) := to_number(p_consolidation_set_id);
690
691
692 lv_prc_list varchar2(3200);
693 lv_process_list_subject varchar2(240);
694 lv_process_list_text varchar2(3200);
695 lv_process_list_html_1 varchar2(3200);
696 lv_process_list_html_2 varchar2(3200);
697
698 lv_contact_user_name varchar2(80);
699 lv_orig_system varchar2(40);
700 lv_orig_system_id varchar2(40);
701 lv_role_name varchar2(50);
702 lv_role_display_name varchar2(50);
703
704 lv_payroll_name varchar2(80);
705 lv_consolidation_set_name varchar2(80);
706 lv_gre_name varchar2(80);
707 lv_tax_group varchar2(80);
708 lv_organization varchar2(80);
709 lv_location varchar2(80);
710 lv_date_time varchar2(80);
711
712 lv_is_batch varchar2(1) := 'N';
713 lv_is_retropay varchar2(1) := 'N';
714 lv_is_retro_notification varchar2(1) := 'N';
715 lv_is_gtn varchar2(1);
716 lv_is_sdr varchar2(1);
717 lv_is_payroll_register varchar2(1);
718 lv_is_element_register varchar2(1);
719 lv_is_deductions varchar2(1);
720 lv_is_direct_deposit varchar2(1);
721 lv_is_chequewriter varchar2(1);
722 lv_is_deposit_advice varchar2(1);
723 lv_is_roe varchar2(1);
724 lv_is_roe_mag varchar2(1);
725 lv_is_costing_summary varchar2(1);
726 lv_is_consolidation_set varchar2(1);
727 lv_is_exception varchar2(1);
728
729
730 lv_workflow_mode varchar2(1);
731 lv_batch_name varchar2(80);
732 lv_retro_period varchar2(80);
733 lv_event_group_name varchar2(80);
734 lv_retropay_asg_set varchar2(80);
735 lv_retropay_element_set varchar2(80);
736 lv_payroll_element_set varchar2(80);
737 lv_payroll_assignment_set varchar2(80);
738 lv_payroll_run_type varchar2(80);
739 lv_gtn_period varchar2(80);
740 lv_payroll_process varchar2(80);
741 lv_account_number varchar2(80);
742 lv_federal varchar2(80);
743 lv_province varchar2(80);
744 lv_reporting_dimension varchar2(80);
745 lv_exp_group_name varchar2(80);
746 lv_exp_report_name varchar2(80);
747 lv_exp_vartype_name varchar2(80);
748 lv_exp_finder_pqp varchar2(80);
749 lv_prepay_payment_method varchar2(80);
750 lv_ele_element_set varchar2(80);
751 lv_ele_element_class varchar2(80);
752 lv_ele_element_name varchar2(80);
753 lv_ele_employee varchar2(80);
754 lv_ded_consolidation_dummy varchar2(80);
755 lv_deduction_class varchar2(80);
756 lv_deduction_name varchar2(80);
757 lv_reg_suppress_zero varchar2(80);
758 lv_reg_full_report_ver varchar2(80);
759 lv_reg_reporting_dimension varchar2(80);
760 lv_reg_sort_one varchar2(80);
761 lv_reg_sort_two varchar2(80);
762 lv_reg_sort_three varchar2(80);
763 lv_reg_employee_page_break varchar2(80);
764 lv_cheque_payment_type varchar2(80);
765 lv_cheque_payment_method varchar2(80);
766 lv_cheque_sort_sequence varchar2(80);
767 lv_cheque_style varchar2(80);
768 lv_da_data_type varchar2(80);
769 lv_roe_asg_set varchar2(80);
770 lv_roe_worksheet_person varchar2(80);
771 lv_roe_mag_media_person varchar2(80);
772 lv_cost_summary_accruals varchar2(80);
773 lv_cost_summary_file_out varchar2(80);
774 lv_cost_detail_ele_set varchar2(80);
775 lv_cost_detail_ele_class varchar2(80);
776 lv_cost_detail_ele_name varchar2(80);
777 lv_cost_detail_asg_set varchar2(80);
778 lv_cost_detail_accruals varchar2(80);
779 l_dd_dummy char(1); -- bug 12548555
780
781 l_user_id number;
782 l_resp_id number;
783 l_resp_appl_id number;
784 l_org_id number;
785 l_per_sec_id number;
786 l_sec_grp_id number;
787
788 varname wf_engine.NameTabTyp;
789 varval wf_engine.TextTabTyp;
790 num_varname wf_engine.NameTabTyp;
791 num_varvalue wf_engine.NumTabTyp;
792
793 TYPE char80_table IS TABLE OF VARCHAR2(80)
794 INDEX BY BINARY_INTEGER;
795
796 lv_conc_prog_name char80_table;
797 i number;
798 j number;
799 k number;
800
801 l_proc varchar2(80) := gv_package||'.StartProcess';
802
803 cursor cur_workflow_mode is
804 select nvl(substr(parameter_value,1,1), 'W')
805 from pay_action_parameters
806 where parameter_name = 'PAYROLL_CA_WF_NOTIFY_ACTION';
807
808 cursor cur_retro_period is
809 select period_name
810 from per_time_periods
811 where payroll_id = l_payroll_id
812 and end_date = fnd_date.canonical_to_date(p_date_earned);
813
814 cursor cur_event_group is
815 select event_group_name
816 from pay_event_groups
817 where business_group_id = p_business_group_id
818 and event_group_id =
819 to_number(substr(p_retro_event_group,(length('EVT_GRP_ID=')+1)));
820
821 cursor cur_retropay_asg_set is
822 select assignment_set_name
823 from hr_assignment_sets
824 where assignment_set_id = to_number(p_retropay_assignment_set)
825 and business_group_id = p_business_group_id;
826
827 cursor cur_retropay_element_set is
828 select element_set_name
829 from pay_element_sets
830 where element_set_id = to_number(p_retropay_element_set)
831 and business_group_id = p_business_group_id;
832
833 cursor cur_payroll_element_set is
834 select element_set_name
835 from pay_element_sets
836 where element_set_id = to_number(p_payroll_element_set)
837 and business_group_id = p_business_group_id;
838
839 cursor cur_payroll_asg_set is
840 select assignment_set_name
841 from hr_assignment_sets
842 where assignment_set_id = to_number(p_payroll_assignment_set)
843 and payroll_id = l_payroll_id;
844
845 cursor cur_payroll_run_type is
846 select t.run_type_name
847 from pay_run_types_f_tl t,
848 pay_run_types_f r
849 where r.run_type_id = to_number(p_payroll_run_type)
850 and r.run_type_id = t.run_type_id
851 and t.language = userenv('LANG')
852 and fnd_date.canonical_to_date(p_date_earned)
853 between r.effective_start_date and r.effective_end_date;
854
855 cursor cur_sdr_reporting_dimension is
856 select meaning
857 from hr_lookups
858 where lookup_code = p_sdr_reporting_dimension
859 and lookup_type = 'PAY_CA_REPORT_DIMENSION'
860 and application_id = 800;
861
862 cursor cur_exp_group_name is
863 select exception_group_name
864 from pqp_exception_report_groups
865 where exception_group_id = to_number(p_exp_group_name);
866
867 cursor cur_exp_report_name is
868 select exception_report_name
869 from pqp_exception_reports
870 where exception_report_id = to_number(p_exp_report_name);
871
872 cursor cur_exp_variance_type is
873 select meaning
874 from fnd_common_lookups
875 where lookup_type = 'PQP_VARIANCE_TYPE'
876 and lookup_code = p_exp_variance_type;
877
878 cursor cur_prepay_payment_method is
879 select org_payment_method_name
880 from pay_org_payment_methods_f_tl
881 where org_payment_method_id = to_number(p_payment_method_override)
882 and language = userenv('LANG');
883
884 cursor cur_ele_element_set is
885 select element_set_name
886 from pay_element_sets
887 where element_set_id = to_number(p_ele_element_set)
888 and element_set_type = 'C';
889
890 cursor cur_ele_element_class is
891 select classification_name
892 from pay_element_classifications
893 where classification_id = to_number(p_ele_element_class);
894
895 cursor cur_ele_element_name is
896 select element_name
897 from pay_element_types_f_tl
898 where element_type_id = to_number(p_ele_element_name)
899 and language = userenv('LANG');
900
901 cursor cur_ele_employee is
902 select full_name||'(Person ID='||person_id||')'
903 from per_people_f
904 where person_id = to_number(p_ele_employee)
905 and fnd_date.canonical_to_date(p_date_earned)
906 between effective_start_date and effective_end_date;
907
908 cursor cur_ded_class is
909 select classification_name
910 from pay_element_classifications
911 where classification_id = to_number(p_deduction_class);
912
913 cursor cur_ded_name is
914 select element_name
915 from pay_element_types_f
916 where element_type_id = to_number(p_deduction_name)
917 and fnd_date.canonical_to_date(p_date_earned)
918 between effective_start_date and effective_end_date;
919
920 cursor cur_reg_dimension is
921 select flv.meaning
922 from fnd_lookup_values flv, fnd_lookup_types flt
923 where flv.lookup_code = p_reg_reporting_dim
924 and flt.lookup_type = 'CA_DIMENSION'
925 and flt.application_id = 800
926 and flt.lookup_type = flv.lookup_type;
927
928 cursor cur_cheque_payment_type is
929 select payment_type_name
930 from pay_payment_types
931 where payment_type_id = to_number(p_cheque_payment_type)
932 and territory_code = 'CA'
933 and category = 'CH';
934
935 cursor cur_cheque_payment_method is
936 select org_payment_method_name
937 from pay_org_payment_methods_f_tl
938 where org_payment_method_id = to_number(p_cheque_payment_method);
939
940 cursor cur_cheque_sort_seq is
941 select meaning
942 from hr_lookups
943 where lookup_code = p_cheque_sort_sequence
944 and lookup_type = 'CHEQUE_PROCEDURE'
945 and enabled_flag = 'Y';
946
947 cursor cur_cheque_style is
948 select meaning
949 from hr_lookups
950 where lookup_code = p_cheque_style
951 and lookup_type = 'CA_CHEQUE_DEPADV'
952 and enabled_flag = 'Y';
953
954 cursor cur_deposit_advice_data_type is
955 select meaning
956 from hr_lookups
957 where lookup_code = p_da_data_type
958 and lookup_type = 'DAR_REPORT'
959 and enabled_flag = 'Y';
960
961 cursor cur_roe_asg_set is
962 select assignment_set_name
963 from hr_assignment_sets
964 where assignment_set_id =
965 to_number(substr(p_roe_assignment_set,(length('ASSIGNMENT_SET_ID=')+1)));
966
967 cursor cur_roe_worksheet_person is
968 select full_name
969 from per_all_people_f
970 where person_id = to_number(p_roe_worksheet_person)
971 and fnd_date.canonical_to_date(p_date_earned)
972 between effective_start_date and effective_end_date;
973
974 cursor cur_roe_mag_media_person is
975 select full_name
976 from per_all_people_f
977 where person_id =
978 to_number(substr(p_roe_mag_media_person,(length('PERSON_ID=')+1)))
979 and fnd_date.canonical_to_date(p_date_earned)
980 between effective_start_date and effective_end_date;
981
982 cursor cur_costing_summary_accruals is
983 select meaning
984 from hr_lookups
985 where lookup_code = p_cost_summary_accruals
986 and lookup_type = 'PAY_PAYRPCBR';
987
988 cursor cur_costing_summary_file_out is
989 select meaning
990 from fnd_common_lookups
991 where lookup_code = p_cost_summary_file_out
992 and lookup_type = 'REPORT_OUTPUT_TYPE'
993 and application_id = 801
994 and enabled_flag = 'Y';
995
996 cursor cur_costing_detail_ele_set is
997 select element_set_name
998 from pay_element_sets
999 where element_set_id = to_number(p_cost_detail_ele_set)
1000 and element_set_type = 'C';
1001
1002 cursor cur_costing_detail_ele_class is
1003 select classification_name
1004 from pay_element_classifications
1005 where classification_id = to_number(p_cost_detail_class);
1006
1007 cursor cur_costing_detail_ele_name is
1008 select tl.element_name
1009 from pay_element_types_f el, pay_element_types_f_tl tl
1010 where el.element_type_id = to_number(p_cost_detail_element)
1011 and el.element_type_id = tl.element_type_id
1012 and tl.language = userenv('LANG')
1013 and fnd_date.canonical_to_date(p_date_earned)
1014 between el.effective_start_date and el.effective_end_date;
1015
1016 cursor cur_costing_detail_asg_set is
1017 select assignment_set_name
1018 from hr_assignment_sets
1019 where assignment_set_id = to_number(p_cost_detail_asg_set);
1020
1021 cursor cur_costing_detail_accruals is
1022 select meaning
1023 from hr_lookups
1024 where lookup_code = p_cost_detail_accruals
1025 and lookup_type = 'PAY_PAYRPCBR';
1026
1027
1028 begin
1029
1030 hr_utility.set_location('Starting: ' || l_proc, 100);
1031
1032 lv_conc_prog_name(1) := 'BEE Batch Process (Transfer)';
1033 lv_conc_prog_name(2) := 'Retro-Notifications Report (Enhanced) - PDF';
1034 lv_conc_prog_name(3) := 'RetroPay By Element';
1035 lv_conc_prog_name(4) := 'Process Payroll Run';
1036 lv_conc_prog_name(5) := 'Gross to Net Summary';
1037 lv_conc_prog_name(6) := 'Payroll Message Report';
1038 lv_conc_prog_name(7) := 'Employee Assignments Not Paid';
1039 lv_conc_prog_name(8) := 'Payroll Exception Report';
1040 lv_conc_prog_name(9) := 'Statutory Deductions Report';
1041 lv_conc_prog_name(10) := 'Quebec Health Services Fund';
1042 lv_conc_prog_name(11) := 'Pre Payments';
1043 lv_conc_prog_name(12) := 'Payroll Register Report';
1044 lv_conc_prog_name(13) := 'Element Register Report';
1045 lv_conc_prog_name(14) := 'Deductions Report';
1046 lv_conc_prog_name(15) := 'Direct Deposit';
1047 lv_conc_prog_name(16) := 'Canadian Payroll Archiver';
1048 lv_conc_prog_name(17) := 'Canadian Chequewriter';
1049 lv_conc_prog_name(18) := 'Canadian Deposit Advice';
1050 lv_conc_prog_name(19) := 'Payment Report';
1051 lv_conc_prog_name(20) := 'Record of Employment by Assignment Set';
1052 lv_conc_prog_name(21) := 'ROE Worksheet';
1053 lv_conc_prog_name(22) := 'ROE-Magnetic Media';
1054 lv_conc_prog_name(23) := 'Costing';
1055 lv_conc_prog_name(24) := 'Costing Detail Report';
1056 lv_conc_prog_name(25) := 'Costing Summary Report';
1057
1058 hr_utility.trace('In StartProcess Item Type is : '|| ItemType);
1059 hr_utility.trace('In StartProcess Item Key is : '|| ItemKey);
1060 hr_utility.trace('Workflow process name is : '|| Workflowprocess);
1061
1062 select to_char(sysdate,'DD-MON-YYYY') ||' '|| to_char(sysdate,'HH24:MI:SS')
1063 into lv_date_time from dual;
1064
1065
1066 hr_utility.trace('before create_process');
1067
1068 wf_engine.createProcess(ItemType => ItemType,
1069 ItemKey => ItemKey,
1070 process => Workflowprocess);
1071
1072 wf_engine.SetItemUserKey(ItemType => ItemType,
1073 ItemKey => ItemKey,
1074 UserKey => ItemUserKey);
1075
1076 hr_utility.trace('In StartProcess before HR_SIGNON.Initialize_HR_Security');
1077
1078 HR_SIGNON.Initialize_HR_Security;
1079
1080 hr_utility.trace('After HR_SIGNON.Initialize_HR_Security');
1081
1082 l_user_id := FND_GLOBAL.USER_ID;
1083 l_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
1084 l_resp_id := FND_GLOBAL.RESP_ID;
1085 l_org_id := FND_GLOBAL.ORG_ID;
1086 l_per_sec_id := FND_GLOBAL.PER_SECURITY_PROFILE_ID;
1087 l_sec_grp_id := FND_GLOBAL.SECURITY_GROUP_ID;
1088
1089 hr_utility.trace('l_user_id = ' || l_user_id);
1090 hr_utility.trace('l_resp_appl_id = ' || l_resp_appl_id);
1091 hr_utility.trace('l_resp_id = ' || l_resp_id);
1092 hr_utility.trace('l_org_id = ' || l_org_id);
1093 hr_utility.trace('l_per_sec_id = ' || l_per_sec_id);
1094 hr_utility.trace('l_sec_grp_id = ' || l_sec_grp_id);
1095 hr_utility.trace('business group = ' || to_char(p_business_group_id));
1096
1097 lv_gre_name := '';
1098 lv_tax_group := '';
1099
1100 open cur_workflow_mode;
1101 fetch cur_workflow_mode into lv_workflow_mode;
1102
1103 if cur_workflow_mode%notfound then
1104 lv_workflow_mode := 'W';
1105 end if;
1106
1107 close cur_workflow_mode;
1108
1109 hr_utility.trace('Workflow Mode = ' || lv_workflow_mode);
1110
1111 open cur_retro_period;
1112 fetch cur_retro_period into lv_retro_period;
1113 close cur_retro_period;
1114
1115 hr_utility.trace('Retro Period = ' || lv_retro_period);
1116
1117 open cur_event_group;
1118 fetch cur_event_group into lv_event_group_name;
1119 close cur_event_group;
1120
1121 hr_utility.trace('Event Group = ' || lv_event_group_name);
1122
1123 open cur_retropay_asg_set;
1124 fetch cur_retropay_asg_set into lv_retropay_asg_set;
1125 close cur_retropay_asg_set;
1126
1127 hr_utility.trace('RetroPay Assignment Set = ' || lv_retropay_asg_set);
1128
1129 open cur_retropay_element_set;
1130 fetch cur_retropay_element_set into lv_retropay_element_set;
1131 close cur_retropay_element_set;
1132
1133 hr_utility.trace('RetroPay Element Set = ' || lv_retropay_element_set);
1134
1135 open cur_payroll_element_set;
1136 fetch cur_payroll_element_set into lv_payroll_element_set;
1137 close cur_payroll_element_set;
1138
1139 hr_utility.trace('Payroll Run Element Set = ' || lv_payroll_element_set);
1140
1141 open cur_payroll_asg_set;
1142 fetch cur_payroll_asg_set into lv_payroll_assignment_set;
1143 close cur_payroll_asg_set;
1144
1145 hr_utility.trace('Payroll Run Assignment Set = ' || lv_payroll_assignment_set);
1146
1147 open cur_payroll_run_type;
1148 fetch cur_payroll_run_type into lv_payroll_run_type;
1149 close cur_payroll_run_type;
1150
1151 hr_utility.trace('Payroll Run Type = ' || lv_payroll_run_type);
1152
1153 open cur_sdr_reporting_dimension;
1154 fetch cur_sdr_reporting_dimension into lv_reporting_dimension;
1155 close cur_sdr_reporting_dimension;
1156
1157 hr_utility.trace('SDR Reporting Dimension = ' || lv_reporting_dimension);
1158
1159 open cur_exp_group_name;
1160 fetch cur_exp_group_name into lv_exp_group_name;
1161 close cur_exp_group_name;
1162
1163 hr_utility.trace('Exception Group Name = ' || lv_exp_group_name);
1164
1165 open cur_exp_report_name;
1166 fetch cur_exp_report_name into lv_exp_report_name;
1167 close cur_exp_report_name;
1168
1169 hr_utility.trace('Exception Report Name = ' || lv_exp_report_name);
1170
1171 open cur_exp_variance_type;
1172 fetch cur_exp_variance_type into lv_exp_vartype_name;
1173 close cur_exp_variance_type;
1174
1175 hr_utility.trace('Exception Variance Type = ' || lv_exp_vartype_name);
1176
1177 open cur_prepay_payment_method;
1178 fetch cur_prepay_payment_method into lv_prepay_payment_method;
1179 close cur_prepay_payment_method;
1180
1181 hr_utility.trace('PrePayment Payment Method = ' || lv_prepay_payment_method);
1182
1183 open cur_ele_element_set;
1184 fetch cur_ele_element_set into lv_ele_element_set;
1185 close cur_ele_element_set;
1186
1187 hr_utility.trace('Element Register Element Set = ' || lv_ele_element_set);
1188
1189 open cur_ele_element_class;
1190 fetch cur_ele_element_class into lv_ele_element_class;
1191 close cur_ele_element_class;
1192
1193 hr_utility.trace('Element Register Element Classification = ' || lv_ele_element_class);
1194
1195 open cur_ele_element_name;
1196 fetch cur_ele_element_name into lv_ele_element_name;
1197 close cur_ele_element_name;
1198
1199 hr_utility.trace('Element Register Element Name = ' || lv_ele_element_name);
1200
1201 open cur_ele_employee;
1202 fetch cur_ele_employee into lv_ele_employee;
1203 close cur_ele_employee;
1204
1205 hr_utility.trace('Element Register Employee = ' || lv_ele_employee);
1206
1207 open cur_ded_class;
1208 fetch cur_ded_class into lv_deduction_class;
1209 close cur_ded_class;
1210
1211 hr_utility.trace('Deduction Report Dedn Classification = ' || lv_deduction_class);
1212
1213 open cur_ded_name;
1214 fetch cur_ded_name into lv_deduction_name;
1215 close cur_ded_name;
1216
1217 hr_utility.trace('Deduction Report Dedn Name = ' || lv_deduction_name);
1218
1219 open cur_reg_dimension;
1220 fetch cur_reg_dimension into lv_reg_reporting_dimension;
1221 close cur_reg_dimension;
1222
1223 hr_utility.trace('Payroll Register Reporting Dimension = ' || lv_reg_reporting_dimension);
1224
1225 open cur_cheque_payment_type;
1226 fetch cur_cheque_payment_type into lv_cheque_payment_type;
1227 close cur_cheque_payment_type;
1228
1229 hr_utility.trace('Chequewriter Payment Type = ' || lv_cheque_payment_type);
1230
1231 open cur_cheque_payment_method;
1232 fetch cur_cheque_payment_method into lv_cheque_payment_method;
1233 close cur_cheque_payment_method;
1234
1235 hr_utility.trace('Chequewriter Payment Method = ' || lv_cheque_payment_method);
1236
1237 open cur_cheque_sort_seq;
1238 fetch cur_cheque_sort_seq into lv_cheque_sort_sequence;
1239 close cur_cheque_sort_seq;
1240
1241 hr_utility.trace('Chequewriter Sort Seq = ' || lv_cheque_sort_sequence);
1242
1243 open cur_cheque_style;
1244 fetch cur_cheque_style into lv_cheque_style;
1245 close cur_cheque_style;
1246
1247 hr_utility.trace('Chequewriter Style = ' || lv_cheque_style);
1248
1249 open cur_deposit_advice_data_type;
1250 fetch cur_deposit_advice_data_type into lv_da_data_type;
1251 close cur_deposit_advice_data_type;
1252
1253 hr_utility.trace('Deposit Advice Data Type = ' || lv_da_data_type);
1254
1255 open cur_roe_asg_set;
1256 fetch cur_roe_asg_set into lv_roe_asg_set;
1257 close cur_roe_asg_set;
1258
1259 hr_utility.trace('ROE Assignment Set = ' || lv_roe_asg_set);
1260
1261 open cur_roe_worksheet_person;
1262 fetch cur_roe_worksheet_person into lv_roe_worksheet_person;
1263 close cur_roe_worksheet_person;
1264
1265 hr_utility.trace('ROE Worksheet Person = ' || lv_roe_worksheet_person);
1266
1267 open cur_roe_mag_media_person;
1268 fetch cur_roe_mag_media_person into lv_roe_mag_media_person;
1269 close cur_roe_mag_media_person;
1270
1271 hr_utility.trace('ROE Magnetic Media Person = ' || lv_roe_mag_media_person);
1272
1273 open cur_costing_summary_accruals;
1274 fetch cur_costing_summary_accruals into lv_cost_summary_accruals;
1275 close cur_costing_summary_accruals;
1276
1277 hr_utility.trace('Costing Summary Accruals = ' || lv_cost_summary_accruals);
1278
1279 open cur_costing_summary_file_out;
1280 fetch cur_costing_summary_file_out into lv_cost_summary_file_out;
1281 close cur_costing_summary_file_out;
1282
1283 hr_utility.trace('Costing Summary File Format = ' || lv_cost_summary_file_out);
1284
1285 open cur_costing_detail_ele_set;
1286 fetch cur_costing_detail_ele_set into lv_cost_detail_ele_set;
1287 close cur_costing_detail_ele_set;
1288
1289 hr_utility.trace('Costing Detail Element Set = ' || lv_cost_detail_ele_set);
1290
1291 open cur_costing_detail_ele_name;
1292 fetch cur_costing_detail_ele_name into lv_cost_detail_ele_name;
1293 close cur_costing_detail_ele_name;
1294
1295 hr_utility.trace('Costing Detail Element Name = ' || lv_cost_detail_ele_name);
1296
1297 open cur_costing_detail_ele_class;
1298 fetch cur_costing_detail_ele_class into lv_cost_detail_ele_class;
1299 close cur_costing_detail_ele_class;
1300
1301 hr_utility.trace('Costing Detail Element Classification = ' || lv_cost_detail_ele_class);
1302
1303 open cur_costing_detail_asg_set;
1304 fetch cur_costing_detail_asg_set into lv_cost_detail_asg_set;
1305 close cur_costing_detail_asg_set;
1306
1307 hr_utility.trace('Costing Detail Assignment Set = ' || lv_cost_detail_asg_set);
1308
1309 open cur_costing_detail_accruals;
1310 fetch cur_costing_detail_accruals into lv_cost_detail_accruals;
1311 close cur_costing_detail_accruals;
1312
1313 hr_utility.trace('Costing Detail Accruals = ' || lv_cost_detail_accruals);
1314
1315 begin
1316 select period_name
1317 into lv_gtn_period
1318 from per_time_periods
1319 where payroll_id = to_number(p_payroll_id)
1320 and time_period_id = to_number(p_gross_to_net_period);
1321
1322 hr_utility.trace('lv_gtn_period = ' || lv_gtn_period);
1323
1324 exception when no_data_found then
1325 hr_utility.trace('lv_gtn_period exception: no_data_found ');
1326 null;
1327
1328 end;
1329
1330 begin
1331 select fnd_date.date_to_chardate(effective_date)||'('||to_char(request_id)||')'
1332 into lv_payroll_process
1333 from pay_payroll_actions
1334 where payroll_action_id = to_number(p_payroll_process);
1335
1336 hr_utility.trace('lv_payroll_process = ' || lv_payroll_process);
1337
1338 exception when no_data_found then
1339 hr_utility.trace('lv_payroll_process exception: no_data_found ');
1340 null;
1341
1342 end;
1343
1344 begin
1345 select to_char(sysdate, 'HHSSSS')
1346 into lv_exp_finder_pqp
1347 from dual;
1348
1349 hr_utility.trace('lv_exp_finder_pqp = ' || lv_exp_finder_pqp);
1350
1351 exception when no_data_found then
1352 hr_utility.trace('lv_exp_finder_pqp exception: no_data_found ');
1353 null;
1354
1355 end;
1356
1357 -- Payroll Name
1358 begin
1359 select payroll_name
1360 into lv_payroll_name
1361 from pay_all_payrolls_f
1362 where payroll_id = l_payroll_id
1363 and fnd_date.canonical_to_date(p_date_earned)
1364 between effective_start_date and effective_end_date;
1365
1366 hr_utility.trace('lv_payroll_name = ' || lv_payroll_name);
1367
1368 exception when no_data_found then
1369 hr_utility.trace('lv_payroll_name exception: no_data_found ');
1370 null;
1371
1372 end;
1373
1374 -- Consolidation Set Name
1375 begin
1376 select consolidation_set_name
1377 into lv_consolidation_set_name
1378 from pay_consolidation_sets
1379 where consolidation_set_id = l_consolidation_set_id;
1380
1381 hr_utility.trace('lv_consolidation_set_name = ' || lv_consolidation_set_name);
1382
1383 exception when no_data_found then
1384 hr_utility.trace('lv_consolidation_set_name exception: no_data_found ');
1385 null;
1386
1387 end;
1388
1389 -- Organization
1390 begin
1391 select name
1392 into lv_organization
1393 from per_organization_units
1394 where organization_id = to_number(p_organization)
1395 and business_group_id = p_business_group_id
1396 and fnd_date.canonical_to_date(p_date_earned) between
1397 date_from and nvl(date_to,to_date('31/12/4712','DD/MM/YYYY'));
1398
1399
1400 hr_utility.trace('lv_organization = ' || lv_organization);
1401
1402 exception when no_data_found then
1403 hr_utility.trace('lv_organization exception: no_data_found ');
1404 null;
1405
1406 end;
1407
1408 -- Location
1409 begin
1410 select location_code||' '||description
1411 into lv_location
1412 from hr_locations
1413 where location_id = to_number(p_location);
1414
1415 hr_utility.trace('lv_location = ' || lv_location);
1416
1417 exception when no_data_found then
1418 hr_utility.trace('lv_location exception: no_data_found ');
1419 null;
1420
1421 end;
1422
1423 -- GRE Name
1424 if p_gre_or_tax_group = 'GRE' then
1425 begin
1426 select name
1427 into lv_gre_name
1428 from hr_ca_tax_units_v
1429 where tax_unit_id = to_number(p_gre)
1430 and business_group_id = p_business_group_id
1431 and fnd_date.canonical_to_date(p_date_earned) between
1432 date_from and nvl(date_to,to_date('31/12/4712','DD/MM/YYYY'));
1433
1434 hr_utility.trace('lv_gre_name = ' || lv_gre_name);
1435
1436 exception when no_data_found then
1437 hr_utility.trace('lv_gre_name exception: no_data_found ');
1438 null;
1439
1440 end;
1441 end if;
1442
1443 -- Tax Group Name
1444 if p_gre_or_tax_group = 'Tax Group' then
1445 begin
1446 select hou.name
1447 into lv_tax_group
1448 from hr_all_organization_units hou,
1449 hr_organization_information hoi
1450 where hou.organization_id = hoi.organization_id
1451 and hoi.org_information_context = 'CLASS'
1452 and hoi.org_information1 = 'CA_TAX_GROUP'
1453 and hou.business_group_id = p_business_group_id
1454 and hou.organization_id = to_number(p_tax_group)
1455 and fnd_date.canonical_to_date(p_date_earned) between
1456 hou.date_from and nvl(hou.date_to,to_date('31/12/4712','DD/MM/YYYY'));
1457
1458 hr_utility.trace('lv_tax_group = ' || lv_tax_group);
1459
1460 exception when no_data_found then
1461 hr_utility.trace('lv_tax_group exception: no_data_found ');
1462 null;
1463
1464 end;
1465 end if;
1466
1467 -- Account Number
1468 if p_quebec = 'Y' then
1469 begin
1470 select pcp.account_number
1471 into lv_account_number
1472 from hr_organization_information hoi1,
1473 hr_organization_information hoi2,
1474 pay_ca_pmed_accounts pcp
1475 where hoi1.org_information_context = 'CLASS'
1476 and hoi1.org_information1 = 'CA_PMED'
1477 and hoi1.org_information2 = 'Y'
1478 and hoi1.organization_id = hoi2.organization_id
1479 and pcp.organization_id = hoi1.organization_id
1480 and pcp.business_group_id = p_business_group_id
1481 and pcp.source_id = to_number(p_qhsf_account_number)
1482 and hoi2.org_information1 = 'QC'
1483 and hoi2.org_information_context = 'Provincial Information';
1484
1485 hr_utility.trace('lv_account_number = ' || lv_account_number);
1486
1487 exception when no_data_found then
1488 hr_utility.trace('lv_account_number exception: no_data_found ');
1489 null;
1490
1491 end;
1492 end if;
1493
1494 -- Federal/Province Flags for SDR
1495
1496 if p_sdr_federal = 'Y' then
1497 lv_federal := 'Yes';
1498 elsif p_sdr_federal = 'N' then
1499 lv_federal := 'No';
1500 else
1501 lv_federal := '';
1502 end if;
1503
1504 if p_sdr_province = 'Y' then
1505 lv_province := 'Yes';
1506 elsif p_sdr_province = 'N' then
1507 lv_province := 'No';
1508 else
1509 lv_province := '';
1510 end if;
1511
1512
1513 if p_consolidation_set_id is null then
1514 lv_ded_consolidation_dummy := 'X';
1515 else
1516 lv_ded_consolidation_dummy := '';
1517 end if;
1518
1519 if p_reg_suppress_zero = 'Y' then
1520 lv_reg_suppress_zero := 'Yes';
1521 elsif p_reg_suppress_zero = 'N' then
1522 lv_reg_suppress_zero := 'No';
1523 end if;
1524
1525 if p_reg_full_report_ver = 'Y' then
1526 lv_reg_full_report_ver := 'Yes';
1527 elsif p_reg_full_report_ver = 'N' then
1528 lv_reg_full_report_ver := 'No';
1529 end if;
1530
1531 if p_reg_employee_page_break = 'Y' then
1532 lv_reg_employee_page_break := 'Yes';
1533 elsif p_reg_employee_page_break = 'N' then
1534 lv_reg_employee_page_break := 'No';
1535 end if;
1536
1537
1538 begin
1539 select meaning
1540 into lv_reg_sort_one
1541 from hr_lookups
1542 where lookup_code = p_reg_sort_one
1543 and lookup_type = 'PAY_CA_YE_SORT_CODE';
1544
1545 hr_utility.trace('lv_reg_sort_one = ' || lv_reg_sort_one );
1546
1547 exception when no_data_found then
1548 hr_utility.trace('lv_reg_sort_one exception: no_data_found ');
1549 null;
1550 end;
1551
1552 begin
1553 select meaning
1554 into lv_reg_sort_two
1555 from hr_lookups
1556 where lookup_code = p_reg_sort_two
1557 and lookup_type = 'PAY_CA_YE_SORT_CODE';
1558
1559 hr_utility.trace('lv_reg_sort_two = ' || lv_reg_sort_two );
1560
1561 exception when no_data_found then
1562 hr_utility.trace('lv_reg_sort_two exception: no_data_found ');
1563 null;
1564 end;
1565
1566 begin
1567 select meaning
1568 into lv_reg_sort_three
1569 from hr_lookups
1570 where lookup_code = p_reg_sort_three
1571 and lookup_type = 'PAY_CA_YE_SORT_CODE';
1572
1573 hr_utility.trace('lv_reg_sort_three = ' || lv_reg_sort_three );
1574
1575 exception when no_data_found then
1576 hr_utility.trace('lv_reg_sort_three exception: no_data_found ');
1577 null;
1578 end;
1579
1580 begin
1581
1582 if p_batch_id is not null then
1583
1584 select batch_name
1585 into lv_batch_name
1586 from pay_batch_headers
1587 where batch_id = p_batch_id;
1588
1589 hr_utility.trace('Batch Name :' ||lv_batch_name);
1590
1591 end if;
1592
1593 exception when no_data_found then
1594 lv_batch_name := '';
1595 hr_utility.trace('lv_batch_name exception: no_data_found ');
1596 null;
1597
1598 end;
1599
1600 begin
1601 /* currently we are only looking at the context at payroll level
1602 as we do not have any required parameter for GRE on the SRS
1603 screen, if we want to use the contact defined at GRE level then
1604 we need to modify the package and SRS definition to get the
1605 GRE_ID , and pass it in the following function.
1606 */
1607
1608 lv_contact_user_name := get_notifier(to_number(p_payroll_id),
1609 to_number(p_gre),
1610 p_date_earned);
1611
1612 exception when others then
1613 hr_utility.trace('lv_contact_user_name exception: OTHERS at get_notifier');
1614 null;
1615
1616 end;
1617
1618 begin
1619
1620 select orig_system,
1621 orig_system_id,
1622 name,
1623 display_name
1624 into lv_orig_system,
1625 lv_orig_system_id,
1626 lv_role_name,
1627 lv_role_display_name
1628 from wf_roles
1629 where name = lv_contact_user_name ;
1630
1631 exception when no_data_found then
1632 hr_utility.trace('lv_role_name exception: no_data_found');
1633 null;
1634
1635 end;
1636
1637 hr_utility.trace('orig system : '|| lv_orig_system);
1638 hr_utility.trace('orig system id : '|| lv_orig_system_id);
1639 hr_utility.trace('role name = ' || lv_role_name);
1640
1641 wf_engine.SetItemOwner (itemtype => ItemType,
1642 itemkey => ItemKey,
1643 owner => ProcessOwner);
1644
1645 lv_process_list_html_2 := 'WF_NOTIFICATION(ATTRS';
1646
1647 /* set variables that run processes */
1648
1649 -- BEE Transfer
1650 if p_batch_id is not null then
1651 lv_is_batch := 'Y';
1652 lv_prc_list := lv_prc_list||lv_conc_prog_name(1)||wf_core.newline;
1653 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS01';
1654 else
1655 lv_is_batch := 'N';
1656 end if;
1657
1658 -- Retro Notification Report
1659
1660 if ((p_retro_assignment_set is not null) and
1661 (p_retro_event_group is not null)) then
1662
1663 lv_is_retro_notification := 'Y';
1664 lv_prc_list := lv_prc_list||lv_conc_prog_name(2)||wf_core.newline;
1665 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS02';
1666
1667 -- If the Retro Notification is going to be run
1668 -- then the RetroPay assignment set should be the
1669 -- same one that is generated by Retro Notification
1670
1671 lv_retropay_asg_set := p_retro_assignment_set;
1672
1673 else
1674 lv_is_retro_notification := 'N';
1675 end if;
1676
1677 -- RetroPay By Element
1678
1679 if (((p_retropay_assignment_set is not null) or
1680 (p_retro_assignment_set is not null)) and
1681 (p_retropay_element_set is not null) and
1682 (p_retro_start_date is not null)) then
1683 lv_is_retropay := 'Y';
1684 lv_prc_list := lv_prc_list||lv_conc_prog_name(3)||wf_core.newline;
1685 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS03';
1686 else
1687 lv_is_retropay := 'N';
1688 end if;
1689
1690 lv_prc_list := lv_prc_list||lv_conc_prog_name(4)||wf_core.newline;
1691 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS04';
1692
1693 -- Gross to Net Summary
1694
1695 if ((p_gross_to_net_period is not null)) then
1696 lv_is_gtn := 'Y';
1697 lv_prc_list := lv_prc_list||lv_conc_prog_name(5)||wf_core.newline;
1698 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS05';
1699 else
1700 lv_is_gtn := 'N';
1701 end if;
1702
1703 lv_prc_list := lv_prc_list||lv_conc_prog_name(6)||wf_core.newline;
1704 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS06';
1705
1706 -- Employee Assignments Not Paid
1707 if ((p_consolidation_set_id is not null)) then
1708 lv_is_consolidation_set := 'Y';
1709 lv_prc_list := lv_prc_list||lv_conc_prog_name(7)||wf_core.newline;
1710 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS07';
1711 else
1712 lv_is_consolidation_set := 'N';
1713 end if;
1714
1715 -- Payroll Exception Report
1716 if ((p_consolidation_set_id is not null) and
1717 ((p_exp_report_name is not null) or
1718 (p_exp_group_name is not null))) then
1719 lv_is_exception := 'Y';
1720 lv_prc_list := lv_prc_list||lv_conc_prog_name(8)||wf_core.newline;
1721 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS08';
1722 else
1723 lv_is_exception := 'N';
1724 end if;
1725
1726 -- Statutory Deductions Report
1727
1728 if ((p_sdr_federal is not null) and
1729 (p_sdr_reporting_dimension is not null)) then
1730 lv_is_sdr := 'Y';
1731 lv_prc_list := lv_prc_list||lv_conc_prog_name(9)||wf_core.newline;
1732 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS09';
1733 else
1734 lv_is_sdr := 'N';
1735 end if;
1736
1737 -- Quebec Health Services Fund
1738
1739 if (p_quebec = 'Y') then
1740 lv_prc_list := lv_prc_list||lv_conc_prog_name(10)||wf_core.newline;
1741 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS10';
1742 end if;
1743
1744
1745 -- Pre Payment
1746
1747 if (p_consolidation_set_id is not null) then
1748 lv_prc_list := lv_prc_list||lv_conc_prog_name(11)||wf_core.newline;
1749 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS11';
1750 end if;
1751
1752
1753 -- Payroll Register Report
1754
1755 if ((p_consolidation_set_id is not null) and
1756 (p_reg_suppress_zero is not null) and
1757 (p_reg_req_num is not null) and
1758 (p_reg_reporting_dim is not null)) then
1759 lv_is_payroll_register := 'Y';
1760 lv_prc_list := lv_prc_list||lv_conc_prog_name(12)||wf_core.newline;
1761 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS12';
1762 else
1763 lv_is_payroll_register := 'N';
1764 end if;
1765
1766 -- Element Register Report
1767
1768 if (p_ele_selection_criterion is not null)
1769 then
1770 lv_is_element_register := 'Y';
1771 lv_prc_list := lv_prc_list||lv_conc_prog_name(13)||wf_core.newline;
1772 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS13';
1773 else
1774 lv_is_element_register := 'N';
1775 end if;
1776
1777 -- Deductions Report
1778
1779 if ((p_deduction_sort_one is not null) and
1780 (p_deduction_sort_two is not null) and
1781 (p_deduction_sort_three is not null))
1782 then
1783 lv_is_deductions := 'Y';
1784 lv_prc_list := lv_prc_list||lv_conc_prog_name(14)||wf_core.newline;
1785 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS14';
1786 else
1787 lv_is_deductions := 'N';
1788 end if;
1789
1790 -- Direct Deposit
1791
1792 if ((p_consolidation_set_id is not null) and
1793 (p_dd_payment_method is not null))
1794 then
1795 lv_is_direct_deposit := 'Y';
1796 lv_prc_list := lv_prc_list||lv_conc_prog_name(15)||wf_core.newline;
1797 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS15';
1798 else
1799 lv_is_direct_deposit := 'N';
1800 end if;
1801
1802
1803 -- Canadian Payroll Archiver
1804
1805 if (p_consolidation_set_id is not null) then
1806 lv_prc_list := lv_prc_list||lv_conc_prog_name(16)||wf_core.newline;
1807 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS16';
1808 end if;
1809
1810
1811 -- Canadian Cheque Writer
1812
1813 if ((p_consolidation_set_id is not null) and
1814 (p_cheque_payment_method is not null) and
1815 (p_cheque_payment_type is not null) and
1816 (p_cheque_sort_sequence is not null) and
1817 (p_cheque_style is not null) and
1818 (p_start_cheque_number is not null)) then
1819 lv_is_chequewriter := 'Y';
1820 lv_prc_list := lv_prc_list||lv_conc_prog_name(17)||wf_core.newline;
1821 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS17';
1822 else
1823 lv_is_chequewriter := 'N';
1824 end if;
1825
1826 -- Canadian Deposit Advice
1827
1828 if ((p_da_data_type is not null) and
1829 (p_consolidation_set_id is not null)) then
1830 lv_is_deposit_advice := 'Y';
1831 lv_prc_list := lv_prc_list||lv_conc_prog_name(18)||wf_core.newline;
1832 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS18';
1833 else
1834 lv_is_deposit_advice := 'N';
1835 end if;
1836
1837 -- Payment Report
1838
1839 if (p_consolidation_set_id is not null) then
1840 lv_prc_list := lv_prc_list||lv_conc_prog_name(19)||wf_core.newline;
1841 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS19';
1842 end if;
1843
1844 -- Record of Employment
1845
1846 if ((p_roe_assignment_set is not null)) then
1847 lv_is_roe := 'Y';
1848 lv_prc_list := lv_prc_list||lv_conc_prog_name(20)||wf_core.newline;
1849 lv_prc_list := lv_prc_list||lv_conc_prog_name(21)||wf_core.newline;
1850 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS20';
1851 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS21';
1852
1853 if (p_roe_mag_roe_type is not null) then
1854 lv_is_roe_mag := 'Y';
1855 lv_prc_list := lv_prc_list||lv_conc_prog_name(22)||wf_core.newline;
1856 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS22';
1857 else
1858 lv_is_roe_mag := 'N';
1859 end if;
1860 else
1861 lv_is_roe := 'N';
1862 lv_is_roe_mag := 'N';
1863 end if;
1864
1865 -- Costing
1866
1867 if ((p_consolidation_set_id is not null)) then
1868 lv_prc_list := lv_prc_list||lv_conc_prog_name(23)||wf_core.newline;
1869 lv_prc_list := lv_prc_list||lv_conc_prog_name(24)||wf_core.newline;
1870 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS23';
1871 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS24';
1872
1873 if (p_cost_summary_file_out is not null) then
1874 lv_is_costing_summary := 'Y';
1875 lv_prc_list := lv_prc_list||lv_conc_prog_name(25)||wf_core.newline;
1876 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS25';
1877 else
1878 lv_is_costing_summary := 'N';
1879 end if;
1880 else
1881 lv_is_costing_summary := 'N';
1882 end if;
1883
1884 lv_process_list_subject := 'List of Processes and Reports which will be submitted';
1885
1886 if lv_workflow_mode = 'W' then
1887
1888 lv_process_list_text := 'Review the List of Processes and Reports which will be submitted based on the input parameters, please click on the '||''''||'Continue'||''''||' button to start processing.'||wf_core.newline||wf_core.newline||lv_prc_list;
1889
1890 lv_process_list_html_1 := 'Review the List of Processes and Reports which will be submitted based on the input parameters, please click on the '||''''||'Continue'||''''||' button to start processing.';
1891
1892 else
1893
1894 lv_process_list_text := 'Review the List of Processes and Reports which will be submitted based on the input parameters.'||wf_core.newline||wf_core.newline||lv_prc_list;
1895
1896 lv_process_list_html_1 := 'Review the List of Processes and Reports which will be submitted based on the input parameters.';
1897
1898 end if;
1899
1900 lv_process_list_html_2 := lv_process_list_html_2||')';
1901
1902 hr_utility.trace('p_payroll_id = ' || p_payroll_id);
1903 hr_utility.trace('p_consolidation_set_id = ' || p_consolidation_set_id);
1904 hr_utility.trace('p_date_earned = ' || p_date_earned);
1905 hr_utility.trace('p_date_paid = ' || p_date_paid);
1906 hr_utility.trace('p_payroll_assignment_set = ' || p_payroll_assignment_set);
1907 hr_utility.trace('p_payroll_run_type = ' || p_payroll_run_type);
1908
1909 begin
1910
1911 num_varname(1) := 'USER_ID';
1912 num_varvalue(1) := l_user_id;
1913 num_varname(2) := 'APPLICATION_ID';
1914 num_varvalue(2) := l_resp_appl_id;
1915 num_varname(3) := 'RESPONSIBILITY_ID';
1916 num_varvalue(3) := l_resp_id;
1917 num_varname(4) := 'ORG_ID';
1918 num_varvalue(4) := l_org_id;
1919 num_varname(5) := 'PER_SECURITY_PROFILE_ID';
1920 num_varvalue(5) := l_per_sec_id;
1921 num_varname(6) := 'SECURITY_GROUP_ID';
1922 num_varvalue(6) := l_sec_grp_id;
1923
1924
1925 i := 0;
1926
1927 varname(i) := 'P_BUSINESS_GROUP_ID';
1928 varval(i) := to_char(p_business_group_id);
1929 i := i+1;
1930 varname(i) := 'WORKFLOW_MODE';
1931 varval(i) := lv_workflow_mode;
1932 i := i+1;
1933 varname(i) := 'CURR_ITEM_TYPE';
1934 varval(i) := ItemType;
1935 i := i+1;
1936 varname(i) := 'CURR_ITEM_KEY';
1937 varval(i) := ItemKey;
1938 i := i+1;
1939 varname(i) := 'ROLE_NAME';
1940 varval(i) := lv_role_name;
1941 i := i+1;
1942 varname(i) := 'USER';
1943 varval(i) := lv_contact_user_name;
1944 i := i+1;
1945
1946
1947 varname(i) := 'IS_CONSOLIDATION_SET';
1948 varval(i) := lv_is_consolidation_set;
1949 i := i+1;
1950 varname(i) := 'PAYROLL_NAME';
1951 varval(i) := lv_payroll_name;
1952 i := i+1;
1953 varname(i) := 'PAYROLL_ID';
1954 varval(i) := p_payroll_id;
1955 i := i+1;
1956 varname(i) := 'CONSOLIDATION_SET_NAME';
1957 varval(i) := lv_consolidation_set_name;
1958 i := i+1;
1959 varname(i) := 'CONSOLIDATION_SET_ID';
1960 varval(i) := p_consolidation_set_id;
1961 i := i+1;
1962 varname(i) := 'DATE_EARNED';
1963 varval(i) := p_date_earned;
1964 i := i+1;
1965 varname(i) := 'DATE_PAID';
1966 varval(i) := p_date_paid;
1967 i := i+1;
1968 varname(i) := 'DATE_TIME';
1969 varval(i) := lv_date_time;
1970 i := i+1;
1971 varname(i) := 'GRE';
1972 varval(i) := p_gre;
1973 i := i+1;
1974 varname(i) := 'GRE_NAME';
1975 varval(i) := lv_gre_name;
1976 i := i+1;
1977 varname(i) := 'TAX_GROUP';
1978 varval(i) := p_tax_group;
1979 i := i+1;
1980 varname(i) := 'TAX_GROUP_NAME';
1981 varval(i) := lv_tax_group;
1982 i := i+1;
1983 varname(i) := 'ORGANIZATION';
1984 varval(i) := p_organization;
1985 i := i+1;
1986 varname(i) := 'ORGANIZATION_NAME';
1987 varval(i) := lv_organization;
1988 i := i+1;
1989 varname(i) := 'LOCATION';
1990 varval(i) := p_location;
1991 i := i+1;
1992 varname(i) := 'LOCATION_NAME';
1993 varval(i) := lv_location;
1994 i := i+1;
1995
1996 -- BEE (Transfer)
1997
1998 varname(i) := 'IS_BATCH';
1999 varval(i) := lv_is_batch;
2000 i := i+1;
2001 varname(i) := 'BATCH_ID';
2002 varval(i) := to_char(p_batch_id);
2003 i := i+1;
2004 varname(i) := 'BEE_BATCH_NAME';
2005 varval(i) := lv_batch_name;
2006 i := i+1;
2007
2008 -- Retro-Notification Report
2009
2010 varname(i) := 'IS_RETRO_NOTIFICATION';
2011 varval(i) := lv_is_retro_notification;
2012 i := i+1;
2013 varname(i) := 'RETRO_START_DATE_DUMMY';
2014 varval(i) := 'START_DATE=1900/01/01 00:00:00';
2015 i := i+1;
2016 varname(i) := 'END_DATE_LEG_PARAM';
2017 varval(i) := 'END_DATE='||p_date_earned;
2018 i := i+1;
2019 varname(i) := 'PAYROLL_LEG_PARAM';
2020 varval(i) := 'PAYROLL_ID='||p_payroll_id;
2021 i := i+1;
2022 varname(i) := 'RETRO_PERIOD';
2023 varval(i) := lv_retro_period;
2024 i := i+1;
2025 varname(i) := 'EVENT_GROUP';
2026 varval(i) := p_retro_event_group;
2027 i := i+1;
2028 varname(i) := 'EVENT_GROUP_NAME';
2029 varval(i) := lv_event_group_name;
2030 i := i+1;
2031 varname(i) := 'RETRO_ASSIGNMENT_SET_NAME';
2032 varval(i) := p_retro_assignment_set;
2033 i := i+1;
2034 varname(i) := 'RETRO_ASSIGNMENT_SET_NAME_DUMY';
2035 varval(i) := 'ASG_SET='||p_retro_assignment_set;
2036 i := i+1;
2037
2038 -- RetroPay By Element
2039
2040 varname(i) := 'IS_RETROPAY';
2041 varval(i) := lv_is_retropay;
2042 i := i+1;
2043 varname(i) := 'RETRO_ASSIGNMENT_SET_ID';
2044 varval(i) := p_retropay_assignment_set;
2045 i := i+1;
2046 varname(i) := 'RETROPAY_ASG_SET_NAME';
2047 varval(i) := lv_retropay_asg_set;
2048 i := i+1;
2049 varname(i) := 'RETRO_ELEMENT_SET';
2050 varval(i) := p_retropay_element_set;
2051 i := i+1;
2052 varname(i) := 'RETROPAY_ELEMENT_SET_NAME';
2053 varval(i) := lv_retropay_element_set;
2054 i := i+1;
2055 varname(i) := 'RETRO_START_DATE';
2056 varval(i) := p_retro_start_date;
2057 i := i+1;
2058
2059 -- Payroll Run Process
2060
2061 varname(i) := 'PAYROLL_ELEMENT_SET_ID';
2062 varval(i) := p_payroll_element_set;
2063 i := i+1;
2064 varname(i) := 'PAYROLL_ELEMENT_SET_NAME';
2065 varval(i) := lv_payroll_element_set;
2066 i := i+1;
2067 varname(i) := 'PAYROLL_ASSIGNMENT_SET_ID';
2068 varval(i) := p_payroll_assignment_set;
2069 i := i+1;
2070 varname(i) := 'PAYROLL_ASSIGNMENT_SET_NAME';
2071 varval(i) := lv_payroll_assignment_set;
2072 i := i+1;
2073 varname(i) := 'PAYROLL_RUN_TYPES_ID';
2074 varval(i) := p_payroll_run_type;
2075 i := i+1;
2076 varname(i) := 'PAYROLL_RUN_TYPE_NAME';
2077 varval(i) := lv_payroll_run_type;
2078 i := i+1;
2079
2080 -- Gross to Net Summary
2081
2082 varname(i) := 'IS_GTN';
2083 varval(i) := lv_is_gtn;
2084 i := i+1;
2085 varname(i) := 'GTN_PERIOD';
2086 varval(i) := p_gross_to_net_period;
2087 i := i+1;
2088 varname(i) := 'GTN_PERIOD_NAME';
2089 varval(i) := lv_gtn_period;
2090 i := i+1;
2091
2092 -- Payroll Message Report
2093
2094 varname(i) := 'SESSION_DATE';
2095 varval(i) := p_session_date;
2096 i := i+1;
2097 varname(i) := 'PAYROLL_ACTION_ID';
2098 varval(i) := p_payroll_process;
2099 i := i+1;
2100 varname(i) := 'PAYROLL_MSG_PAYROLL_PROCESS';
2101 varval(i) := lv_payroll_process;
2102 i := i+1;
2103
2104 -- Quebec Health Services Fund
2105
2106 varname(i) := 'IS_QUEBEC';
2107 varval(i) := p_quebec;
2108 i := i+1;
2109 varname(i) := 'ACCOUNT_NUMBER_ID';
2110 varval(i) := p_qhsf_account_number;
2111 i := i+1;
2112 varname(i) := 'ACCOUNT_NUMBER';
2113 varval(i) := lv_account_number;
2114 i := i+1;
2115 varname(i) := 'OVERRIDE_TABLE';
2116 varval(i) := p_qhsf_override_table;
2117 i := i+1;
2118
2119 -- Statutory Deductions Report
2120
2121 varname(i) := 'IS_SDR';
2122 varval(i) := lv_is_sdr;
2123 i := i+1;
2124 varname(i) := 'FEDERAL';
2125 varval(i) := p_sdr_federal;
2126 i := i+1;
2127 varname(i) := 'FEDERAL_NAME';
2128 varval(i) := lv_federal;
2129 i := i+1;
2130 varname(i) := 'PROVINCE';
2131 varval(i) := p_sdr_province;
2132 i := i+1;
2133 varname(i) := 'PROVINCE_NAME';
2134 varval(i) := lv_province;
2135 i := i+1;
2136 varname(i) := 'REPORTING_DIMENSION';
2137 varval(i) := p_sdr_reporting_dimension;
2138 i := i+1;
2139 varname(i) := 'REPORTING_DIMENSION_NAME';
2140 varval(i) := lv_reporting_dimension;
2141 i := i+1;
2142
2143
2144 -- Exception Report
2145
2146 varname(i) := 'IS_EXCEPTION';
2147 varval(i) := lv_is_exception;
2148 i := i+1;
2149 varname(i) := 'SELECT_REPORT_OR_GROUP';
2150 varval(i) := p_exp_report_or_group;
2151 i := i+1;
2152 varname(i) := 'EXCEPTION_LEGISLATION_CODE';
2153 varval(i) := p_exp_legislation_code;
2154 i := i+1;
2155 varname(i) := 'IS_EXCEPTION_GROUP';
2156 varval(i) := p_exp_is_group;
2157 i := i+1;
2158 varname(i) := 'EXCEPTION_GROUP_NAME';
2159 varval(i) := p_exp_group_name;
2160 i := i+1;
2161 varname(i) := 'EXCEPTION_GROUP_NAME_NAME';
2162 varval(i) := lv_exp_group_name;
2163 i := i+1;
2164 varname(i) := 'EXCEPTION_GROUP_NAME_DMY';
2165 varval(i) := 'TRANSFER_GROUP='||p_exp_group_name;
2166 i := i+1;
2167 varname(i) := 'IS_EXCEPTION_REPORT';
2168 varval(i) := p_exp_is_report;
2169 i := i+1;
2170 varname(i) := 'EXCEPTION_REPORT_NAME';
2171 varval(i) := p_exp_report_name;
2172 i := i+1;
2173 varname(i) := 'EXCEPTION_REPORT_NAME_NAME';
2174 varval(i) := lv_exp_report_name;
2175 i := i+1;
2176 varname(i) := 'EXCEPTION_REPORT_NAME_DMY';
2177 varval(i) := 'TRANSFER_REPORT='||p_exp_report_name;
2178 i := i+1;
2179 varname(i) := 'OVER_RIDE_VARIANCE_TYPE';
2180 varval(i) := p_exp_variance_type;
2181 i := i+1;
2182 varname(i) := 'OVER_RIDE_VARIANCE_TYPE_NAME';
2183 varval(i) := lv_exp_vartype_name;
2184 i := i+1;
2185 varname(i) := 'OVER_RIDE_VARIANCE_TYPE_DMY';
2186 varval(i) := 'TRANSFER_VARTYPE='||p_exp_variance_type;
2187 i := i+1;
2188 varname(i) := 'OVER_RIDE_VARIANCE_VALUE';
2189 varval(i) := p_exp_variance_value;
2190 i := i+1;
2191 varname(i) := 'OVER_RIDE_VARIANCE_VALUE_DMY';
2192 varval(i) := 'TRANSFER_VARVALUE='||p_exp_variance_value;
2193 i := i+1;
2194 varname(i) := 'TRANSFER_DATE';
2195 varval(i) := 'TRANSFER_DATE='||p_date_paid;
2196 i := i+1;
2197 varname(i) := 'PPA_FINDER_PQP';
2198 varval(i) := lv_exp_finder_pqp;
2199 i := i+1;
2200 varname(i) := 'TRANSFER_PPA_FINDER_PQP';
2201 varval(i) := 'TRANSFER_PPA_FINDER='||lv_exp_finder_pqp;
2202 i := i+1;
2203 varname(i) := 'TRANSFER_PAYROLL';
2204 varval(i) := 'TRANSFER_PAYROLL='||p_payroll_id;
2205 i := i+1;
2206 varname(i) := 'TRANSFER_CONC_SET';
2207 varval(i) := 'TRANSFER_CONC_SET='||p_consolidation_set_id;
2208 i := i+1;
2209
2210 -- PrePayment
2211
2212 varname(i) := 'PREPAY_PAYMENT_METHOD_OVERRIDE';
2213 varval(i) := p_payment_method_override;
2214 i := i+1;
2215 varname(i) := 'PREPAY_PAYMENT_METHOD_NAME';
2216 varval(i) := lv_prepay_payment_method;
2217 i := i+1;
2218
2219 -- Element Register Report
2220
2221 varname(i) := 'IS_ELEMENT';
2222 varval(i) := lv_is_element_register;
2223 i := i+1;
2224 varname(i) := 'SELECTION_CRITERION';
2225 varval(i) := p_ele_selection_criterion;
2226 i := i+1;
2227 varname(i) := 'IS_ELEMENT_SET';
2228 varval(i) := p_ele_is_element_set;
2229 i := i+1;
2230 varname(i) := 'ELE_REG_ELEMENT_SET';
2231 varval(i) := p_ele_element_set;
2232 i := i+1;
2233 varname(i) := 'ELE_REG_ELEMENT_SET_NAME';
2234 varval(i) := lv_ele_element_set;
2235 i := i+1;
2236 varname(i) := 'IS_ELEMENT_CLASSIFICATION';
2237 varval(i) := p_ele_is_element_class;
2238 i := i+1;
2239 varname(i) := 'ELEMENT_CLASSIFICATION';
2240 varval(i) := p_ele_element_class;
2241 i := i+1;
2242 varname(i) := 'ELEMENT_CLASSIFICATION_NAME';
2243 varval(i) := lv_ele_element_class;
2244 i := i+1;
2245 varname(i) := 'IS_ELEMENT_NAME';
2246 varval(i) := p_ele_is_element_name;
2247 i := i+1;
2248 varname(i) := 'ELEMENT';
2249 varval(i) := p_ele_element_name;
2250 i := i+1;
2251 varname(i) := 'ELEMENT_NAME';
2252 varval(i) := lv_ele_element_name;
2253 i := i+1;
2254 varname(i) := 'EMP_ID';
2255 varval(i) := p_ele_employee;
2256 i := i+1;
2257 varname(i) := 'EMP_NAME';
2258 varval(i) := lv_ele_employee;
2259 i := i+1;
2260
2261 -- Deduction Report(CA)
2262
2263 varname(i) := 'IS_DEDUCTIONS';
2264 varval(i) := lv_is_deductions;
2265 i := i+1;
2266 varname(i) := 'DEDN_CONSOLIDATION_DMY';
2267 varval(i) := lv_ded_consolidation_dummy;
2268 i := i+1;
2269 varname(i) := 'DEDN_CLASSIFICATION';
2270 varval(i) := p_deduction_class;
2271 i := i+1;
2272 varname(i) := 'DEDN_CLASSIFICATION_NAME';
2273 varval(i) := lv_deduction_class;
2274 i := i+1;
2275 varname(i) := 'DEDN_ID';
2276 varval(i) := p_deduction_name;
2277 i := i+1;
2278 varname(i) := 'DEDN_NAME';
2279 varval(i) := lv_deduction_name;
2280 i := i+1;
2281 varname(i) := 'SORT_ONE';
2282 varval(i) := p_deduction_sort_one;
2283 i := i+1;
2284 varname(i) := 'SORT_TWO';
2285 varval(i) := p_deduction_sort_two;
2286 i := i+1;
2287 varname(i) := 'SORT_THREE';
2288 varval(i) := p_deduction_sort_three;
2289 i := i+1;
2290
2291 -- Payroll Register Report
2292
2293
2294 varname(i) := 'IS_PAYROLL_REGISTER';
2295 varval(i) := lv_is_payroll_register;
2296 i := i+1;
2297 varname(i) := 'BUSINESS_GROUP_ID_DMY';
2298 varval(i) := 'B_G_ID='||p_business_group_id;
2299 i := i+1;
2300 varname(i) := 'PAYROLL_DMY';
2301 varval(i) := 'PY_ID='||p_payroll_id;
2302 i := i+1;
2303 varname(i) := 'CONSOLIDATION_SET_DMY';
2304 varval(i) := 'C_ST_ID='||p_consolidation_set_id;
2305 i := i+1;
2306 varname(i) := 'GRE_DMY';
2307 varval(i) := 'T_U_ID='||p_gre;
2308 i := i+1;
2309 varname(i) := 'LOCATION_DMY';
2310 varval(i) := 'L_ID='||p_location;
2311 i := i+1;
2312 varname(i) := 'ORGANIZATION_DMY';
2313 varval(i) := 'O_ID='||p_organization;
2314 i := i+1;
2315 varname(i) := 'PERSON_DMY';
2316 varval(i) := 'P_ID='||p_ele_employee;
2317 i := i+1;
2318 varname(i) := 'SUPPRESS_ZERO_CODE';
2319 varval(i) := p_reg_suppress_zero;
2320 i := i+1;
2321 varname(i) := 'SUPPRESS_ZERO';
2322 varval(i) := lv_reg_suppress_zero;
2323 i := i+1;
2324 varname(i) := 'SUPPRESS_ZERO_DMY';
2325 varval(i) := 'S_Z='||p_reg_suppress_zero;
2326 i := i+1;
2327 varname(i) := 'FULL_REPORT_VERSION_CODE';
2328 varval(i) := p_reg_full_report_ver;
2329 i := i+1;
2330 varname(i) := 'FULL_REPORT_VERSION';
2331 varval(i) := lv_reg_full_report_ver;
2332 i := i+1;
2333 varname(i) := 'FULL_REPORT_VERSION_DMY';
2334 varval(i) := 'F_R='||p_reg_full_report_ver;
2335 i := i+1;
2336 varname(i) := 'PAYREG_REPORTING_DIMENSION_C';
2337 varval(i) := p_reg_reporting_dim;
2338 i := i+1;
2339 varname(i) := 'PAYREG_REPORTING_DIMENSION';
2340 varval(i) := lv_reg_reporting_dimension;
2341 i := i+1;
2342 varname(i) := 'PAYREG_REPORTING_DIMENSION_DMY';
2343 varval(i) := 'RP_DM='||p_reg_reporting_dim;
2344 i := i+1;
2345 varname(i) := 'SORT_ONE_CODE';
2346 varval(i) := p_reg_sort_one;
2347 i := i+1;
2348 varname(i) := 'REGISTER_SORT_ONE';
2349 varval(i) := lv_reg_sort_one;
2350 i := i+1;
2351 varname(i) := 'SORT_ONE_DMY';
2352 varval(i) := 'P_S1='||p_reg_sort_one;
2353 i := i+1;
2354 varname(i) := 'SORT_TWO_CODE';
2355 varval(i) := p_reg_sort_two;
2356 i := i+1;
2357 varname(i) := 'REGISTER_SORT_TWO';
2358 varval(i) := lv_reg_sort_two;
2359 i := i+1;
2360 varname(i) := 'SORT_TWO_DMY';
2361 varval(i) := 'P_S2='||p_reg_sort_two;
2362 i := i+1;
2363 varname(i) := 'SORT_THREE_CODE';
2364 varval(i) := p_reg_sort_three;
2365 i := i+1;
2366 varname(i) := 'REGISTER_SORT_THREE';
2367 varval(i) := lv_reg_sort_three;
2368 i := i+1;
2369 varname(i) := 'SORT_THREE_DMY';
2370 varval(i) := 'P_S3='||p_reg_sort_three;
2371 i := i+1;
2372 varname(i) := 'EMPLOYEE_PAGE_BREAK_CODE';
2373 varval(i) := p_reg_employee_page_break;
2374 i := i+1;
2375 varname(i) := 'EMPLOYEE_PAGE_BREAK';
2376 varval(i) := lv_reg_employee_page_break;
2377 i := i+1;
2378 varname(i) := 'EMPLOYEE_PAGE_BREAK_DMY';
2379 varval(i) := 'EMP_BRK='||p_reg_employee_page_break;
2380 i := i+1;
2381 varname(i) := 'SEQ_NUM';
2382 varval(i) := p_reg_req_num;
2383 i := i+1;
2384 varname(i) := 'SEQ_NUM_DMY';
2385 varval(i) := 'S_N='||p_reg_req_num;
2386 i := i+1;
2387
2388 -- Direct Deposit
2389
2390 varname(i) := 'IS_DIRECT_DEPOSIT';
2391 varval(i) := lv_is_direct_deposit;
2392 i := i+1;
2393 varname(i) := 'PAYMENT_TYPE_ID';
2394 varval(i) := p_dd_payment_type;
2395 i := i+1;
2396 varname(i) := 'PAYMENT_METHOD_ID';
2397 varval(i) := p_dd_payment_method;
2398 i := i+1;
2399 varname(i) := 'OVERRIDE_DD_DATE';
2400 varval(i) := p_dd_override_date;
2401 i := i+1;
2402 varname(i) := 'FINANCIAL_INSTITUTION_CODE';
2403 varval(i) := p_dd_financial_inst;
2404 i := i+1;
2405 varname(i) := 'FINANCIAL_INSTITUTION_HIDDEN';
2406 varval(i) := 'MAGTAPE_REPORT_ID='||p_dd_financial_inst;
2407 i := i+1;
2408 varname(i) := 'OVERRIDE_CPA_CODE';
2409 varval(i) := p_dd_cpa_code;
2410 i := i+1;
2411 varname(i) := 'OVERRIDE_FILE_CREATION_NUMBER';
2412 varval(i) := p_dd_file_number;
2413 i := i+1;
2414 varname(i) := 'OVERRIDE_FCN';
2415 varval(i) := 'FILE_CREATION_NUMBER_OVERRIDE='||p_dd_file_number;
2416 i := i+1;
2417 varname(i) := 'FILE_CREATION_DATE';
2418 varval(i) := '|FILE_CREATION_DATE='||p_session_date||'|';
2419 i := i+1;
2420 varname(i) := 'OVERRIDE_CPA_HIDDEN';
2421 varval(i) := 'OVERRIDE_CPA_CODE='||p_dd_cpa_code;
2422 i := i+1;
2423 if (p_dd_financial_inst IN ('TD','NOVA_SCOT','CIBC')
2424 and NVL(fnd_profile.value_specific('PAY_CA_DD_XMLP_RUN'),'Y')='Y') then
2425 l_dd_dummy := 'Y';
2426 else l_dd_dummy := '';
2427 end if;
2428 varname(i) := 'DD_DUMMY';
2429 varval(i) := l_dd_dummy;
2430 i := i+1;
2431 varname(i) := 'REPORT_GROUP';
2432 if l_dd_dummy = 'Y' then
2433 varval(i) := 'REP_GROUP=XML_PAYMENT';
2434 else
2435 varval(i) := '';
2436 end if;
2437 i := i+1;
2438 varname(i) := 'REPORT_CATEGORY';
2439 if l_dd_dummy = 'Y' then
2440 varval(i) := 'REP_CAT=PAY_CA_DD_'||p_dd_financial_inst;
2441 else
2442 varval(i) := '';
2443 end if;
2444 i := i+1;
2445
2446
2447 -- Canadian Payroll Archiver
2448
2449 varname(i) := 'TRANSFER_PAYROLL_ARCHIVER';
2450 varval(i) := 'TRANSFER_PAYROLL_ID='||p_payroll_id;
2451 i := i+1;
2452 varname(i) := 'TRANSFER_CONC_SET_ARCHIVER';
2453 varval(i) := 'TRANSFER_CONSOLIDATION_SET_ID='||p_consolidation_set_id;
2454 i := i+1;
2455
2456
2457 -- Canadian Chequewriter
2458
2459 varname(i) := 'IS_CHEQUE_WRITER';
2460 varval(i) := lv_is_chequewriter;
2461 i := i+1;
2462 varname(i) := 'CHEQUE_PAYMENT_TYPE_ID';
2463 varval(i) := p_cheque_payment_type;
2464 i := i+1;
2465 varname(i) := 'CHEQUE_PAYMENT_TYPE_NAME';
2466 varval(i) := lv_cheque_payment_type;
2467 i := i+1;
2468 varname(i) := 'CHEQUE_PAYMENT_METHOD_ID';
2469 varval(i) := p_cheque_payment_method;
2470 i := i+1;
2471 varname(i) := 'CHEQUE_PAYMENT_METHOD_NAME';
2472 varval(i) := lv_cheque_payment_method;
2473 i := i+1;
2474 varname(i) := 'CHEQUE_SORT_SEQUENCE';
2475 varval(i) := p_cheque_sort_sequence;
2476 i := i+1;
2477 varname(i) := 'CHEQUE_SORT_SEQUENCE_MEANING';
2478 varval(i) := lv_cheque_sort_sequence;
2479 i := i+1;
2480 varname(i) := 'CHEQUE_STYLE';
2481 varval(i) := p_cheque_style;
2482 i := i+1;
2483 varname(i) := 'CHEQUE_STYLE_MEANING';
2484 varval(i) := lv_cheque_style;
2485 i := i+1;
2486 varname(i) := 'CHEQUE_START_NUMBER';
2487 varval(i) := p_start_cheque_number;
2488 i := i+1;
2489 varname(i) := 'CHEQUE_END_NUMBER';
2490 varval(i) := p_end_cheque_number;
2491 i := i+1;
2492
2493 -- Deposit Advice
2494
2495 varname(i) := 'IS_DEPOSIT_ADVICE';
2496 varval(i) := lv_is_deposit_advice;
2497 i := i+1;
2498 varname(i) := 'ADVICE_REPORT_CATEGORY';
2499 varval(i) := p_da_data_type;
2500 i := i+1;
2501 varname(i) := 'ADVICE_REPORT_CATEGORY_NAME';
2502 varval(i) := lv_da_data_type;
2503 i := i+1;
2504 varname(i) := 'ADVICE_PAYROLL';
2505 varval(i) := 'PAYROLL_ID='||p_payroll_id;
2506 i := i+1;
2507 varname(i) := 'ADVICE_CONSOLIDATION_SET';
2508 varval(i) := 'CONSOLIDATION_SET_ID='||p_consolidation_set_id;
2509 i := i+1;
2510 varname(i) := 'ADVICE_START_DATE_DMY';
2511 varval(i) := 'START_DATE='||p_date_paid;
2512 i := i+1;
2513 varname(i) := 'ADVICE_END_DATE_DMY';
2514 varval(i) := 'END_DATE='||p_date_paid;
2515 i := i+1;
2516 varname(i) := 'ADVICE_ASG_SET_DMY';
2517 varval(i) := 'ASG_SET_ID='||p_payroll_assignment_set;
2518 i := i+1;
2519
2520 -- Payment Report
2521
2522 varname(i) := 'PAYMENT_PAYMENT_TYPE_ID';
2523 varval(i) := p_payment_rep_payment_type;
2524 i := i+1;
2525 varname(i) := 'PAYMENT_PAYMENT_METHOD_ID';
2526 varval(i) := p_payment_rep_payment_method;
2527 i := i+1;
2528
2529 -- ROE
2530
2531 varname(i) := 'IS_ROE';
2532 varval(i) := lv_is_roe;
2533 i := i+1;
2534 varname(i) := 'ROE_ASG_SET';
2535 varval(i) := p_roe_assignment_set;
2536 i := i+1;
2537 varname(i) := 'ROE_ASG_SET_NAME';
2538 varval(i) := lv_roe_asg_set;
2539 i := i+1;
2540
2541 -- ROE Worksheet
2542
2543 varname(i) := 'ROE_WORKSHEET_PERSON';
2544 varval(i) := p_roe_worksheet_person;
2545 i := i+1;
2546 varname(i) := 'ROE_WORKSHEET_PERSON_NAME';
2547 varval(i) := lv_roe_worksheet_person;
2548 i := i+1;
2549
2550 -- ROE Magnetic Media
2551
2552 varname(i) := 'IS_ROE_MAG';
2553 varval(i) := lv_is_roe_mag;
2554 i := i+1;
2555 varname(i) := 'ROE_MAG_PERSON';
2556 varval(i) := p_roe_mag_media_person;
2557 i := i+1;
2558 varname(i) := 'ROE_MAG_PERSON_NAME';
2559 varval(i) := lv_roe_mag_media_person;
2560 i := i+1;
2561 varname(i) := 'ROE_TYPE';
2562 varval(i) := p_roe_mag_roe_type;
2563 i := i+1;
2564 varname(i) := 'ROE_TYPE_HIDDEN';
2565 varval(i) := 'ROE_TYPE='||p_roe_mag_roe_type;
2566 i := i+1;
2567
2568 -- Costing
2569
2570 -- Costing Summary
2571
2572 varname(i) := 'IS_COSTING_SUMMARY';
2573 varval(i) := lv_is_costing_summary;
2574 i := i+1;
2575 varname(i) := 'SUMMARY_COSTING_ACCRUALS';
2576 varval(i) := p_cost_summary_accruals;
2577 i := i+1;
2578 varname(i) := 'SUMMARY_COSTING_ACCRUALS_NAME';
2579 varval(i) := lv_cost_summary_accruals;
2580 i := i+1;
2581 varname(i) := 'SUMMARY_COSTING_FILE_FORMAT';
2582 varval(i) := p_cost_summary_file_out;
2583 i := i+1;
2584 varname(i) := 'SUMMARY_COSTING_FILE_FMT_NAME';
2585 varval(i) := lv_cost_summary_file_out;
2586 i := i+1;
2587
2588 -- Costing Detail
2589
2590 varname(i) := 'DETAIL_COSTING_CRITERION';
2591 varval(i) := p_cost_detail_selection;
2592 i := i+1;
2593 varname(i) := 'DETAIL_COSTING_IS_ELEMENT_SET';
2594 varval(i) := p_cost_detail_is_ele_set;
2595 i := i+1;
2596 varname(i) := 'DETAIL_COSTING_ELEMENT_SET';
2597 varval(i) := p_cost_detail_ele_set;
2598 i := i+1;
2599 varname(i) := 'DETAIL_COSTING_ELE_SET_NAME';
2600 varval(i) := lv_cost_detail_ele_set;
2601 i := i+1;
2602 varname(i) := 'DETAIL_COSTING_IS_ELEMENT_C';
2603 varval(i) := p_cost_detail_is_class;
2604 i := i+1;
2605 varname(i) := 'DETAIL_COSTING_ELEMENT_C';
2606 varval(i) := p_cost_detail_class;
2607 i := i+1;
2608 varname(i) := 'DETAIL_COSTING_ELEMENT_C_NAME';
2609 varval(i) := lv_cost_detail_ele_class;
2610 i := i+1;
2611 varname(i) := 'DETAIL_COSTING_IS_ELEMENT';
2612 varval(i) := p_cost_detail_is_element;
2613 i := i+1;
2614 varname(i) := 'DETAIL_COSTING_ELEMENT';
2615 varval(i) := p_cost_detail_element;
2616 i := i+1;
2617 varname(i) := 'DETAIL_COSTING_ELEMENT_NAME';
2618 varval(i) := lv_cost_detail_ele_name;
2619 i := i+1;
2620 varname(i) := 'DETAIL_COSTING_ASG_SET';
2621 varval(i) := p_cost_detail_asg_set;
2622 i := i+1;
2623 varname(i) := 'DETAIL_COSTING_ASG_SET_NAME';
2624 varval(i) := lv_cost_detail_asg_set;
2625 i := i+1;
2626 varname(i) := 'DETAIL_COSTING_ACCRUALS';
2627 varval(i) := p_cost_detail_accruals;
2628 i := i+1;
2629 varname(i) := 'DETAIL_COSTING_ACCRUALS_NAME';
2630 varval(i) := lv_cost_detail_accruals;
2631 i := i+1;
2632
2633
2634 -- Notification Attributes
2635
2636 varname(i) := 'PROCESS_LIST_SUBJECT';
2637 varval(i) := lv_process_list_subject;
2638 i := i+1;
2639 varname(i) := 'PROCESS_LIST_TEXT';
2640 varval(i) := lv_process_list_text;
2641 i := i+1;
2642 varname(i) := 'PROCESS_LIST_HTML1';
2643 varval(i) := lv_process_list_html_1;
2644 i := i+1;
2645 varname(i) := 'PROCESS_LIST_HTML2';
2646 varval(i) := lv_process_list_html_2;
2647 i := i+1;
2648
2649 -- print out set values for attributes
2650
2651 for j in varname.first..varname.last loop
2652 hr_utility.trace( 'varname '|| j ||' = '||varname(j));
2653 hr_utility.trace( 'varval '|| j ||' = '||varval(j));
2654 end loop;
2655
2656
2657 wf_engine.SetItemAttrTextArray(ItemType, ItemKey, varname, varval);
2658 hr_utility.trace( 'Total Count = '||to_char(varname.COUNT));
2659
2660 for k in num_varname.first..num_varname.last loop
2661 hr_utility.trace( 'num_varname '|| k ||' = '||num_varname(k));
2662 hr_utility.trace( 'num_varval '|| k ||' = '||num_varvalue(k));
2663 end loop;
2664
2665
2666 wf_engine.SetItemAttrNumberArray(ItemType, ItemKey, num_varname, num_varvalue);
2667 hr_utility.trace( 'Total Num Count = '||to_char(num_varname.COUNT));
2668
2669 exception
2670 when OTHERS then
2671 hr_utility.trace('varname exception: OTHERS of TextArray');
2672 raise;
2673 end;
2674
2675 hr_utility.trace('Before StartProcess');
2676
2677 wf_engine.StartProcess (ItemType => ItemType,
2678 ItemKey => ItemKey );
2679
2680 hr_utility.trace('After StartProcess');
2681
2682 hr_utility.set_location('Leaving: ' || l_proc, 100);
2683
2684
2685 exception
2686 when others then
2687 wf_core.Context('pay_ca_wf_pkg',
2688 'StartProcess',
2689 ItemType,
2690 RequestorUsername,
2691 ProcessOwner,
2692 Workflowprocess);
2693
2694 error;
2695 RAISE;
2696
2697 end StartProcess;
2698
2699
2700 procedure StartProcess (
2701 p_business_group_id number,
2702 p_batch_id number,
2703 p_payroll_id varchar2,
2704 p_consolidation_set_id varchar2,
2705 p_date_earned varchar2,
2706 p_date_paid varchar2,
2707 p_retro_event_group varchar2,
2708 p_retro_valid_template_list varchar2,--bug 12704242
2709 p_retro_template_name varchar2,--bug 12704242
2710 p_retro_assignment_set varchar2,
2711 p_retro_generate_report varchar2,--bug 12704242
2712 p_retropay_assignment_set varchar2,
2713 p_retropay_element_set varchar2,
2714 p_retro_start_date varchar2,
2715 p_retropay_entry_creation_date varchar2, --bug 12704242
2716 p_payroll_element_set varchar2,
2717 p_payroll_assignment_set varchar2,
2718 p_payroll_run_type varchar2,
2719 p_gre_or_tax_group varchar2,
2720 p_is_gre varchar2,
2721 p_gre varchar2,
2722 p_is_tax_group varchar2,
2723 p_tax_group varchar2,
2724 p_gross_to_net_period varchar2,
2725 p_payroll_process varchar2,
2726 p_session_date varchar2,
2727 p_organization varchar2,
2728 p_location varchar2,
2729 p_quebec varchar2,
2730 p_qhsf_account_number varchar2,
2731 p_qhsf_override_table varchar2,
2732 p_sdr_federal varchar2,
2733 p_sdr_province varchar2,
2734 p_sdr_reporting_dimension varchar2,
2735 p_exp_legislation_code varchar2,
2736 p_exp_report_or_group varchar2,
2737 p_exp_is_group varchar2,
2738 p_exp_group_name varchar2,
2739 p_exp_is_report varchar2,
2740 p_exp_report_name varchar2,
2741 p_exp_variance_type varchar2,
2742 p_exp_variance_value varchar2,
2743 p_payment_method_override varchar2,
2744 p_ele_selection_criterion varchar2,
2745 p_ele_is_element_set varchar2,
2746 p_ele_element_set varchar2,
2747 p_ele_is_element_class varchar2,
2748 p_ele_element_class varchar2,
2749 p_ele_is_element_name varchar2,
2750 p_ele_element_name varchar2,
2751 p_ele_employee varchar2,
2752 p_deduction_class varchar2,
2753 p_deduction_name varchar2,
2754 p_deduction_sort_one varchar2,
2755 p_deduction_sort_two varchar2,
2756 p_deduction_sort_three varchar2,
2757 p_reg_suppress_zero varchar2,
2758 p_reg_full_report_ver varchar2,
2759 p_reg_reporting_dim varchar2,
2760 p_reg_sort_one varchar2,
2761 p_reg_sort_two varchar2,
2762 p_reg_sort_three varchar2,
2763 p_reg_employee_page_break varchar2,
2764 p_reg_req_num varchar2,
2765 p_dd_payment_type varchar2,
2766 p_dd_payment_method varchar2,
2767 p_dd_override_date varchar2,
2768 p_dd_financial_inst varchar2,
2769 p_dd_cpa_code varchar2,
2770 p_dd_file_number varchar2,
2771 p_cheque_payment_type varchar2,
2772 p_cheque_payment_method varchar2,
2773 p_cheque_sort_sequence varchar2,
2774 p_cheque_style varchar2,
2775 p_start_cheque_number varchar2,
2776 p_end_cheque_number varchar2,
2777 p_da_data_type varchar2,
2778 p_payment_rep_payment_type varchar2,
2779 p_payment_rep_payment_method varchar2,
2780 p_roe_assignment_set varchar2,
2781 p_roe_worksheet_person varchar2,
2782 p_roe_mag_media_person varchar2,
2783 p_roe_mag_roe_type varchar2,
2784 p_cost_summary_accruals varchar2,
2785 p_cost_summary_file_out varchar2,
2786 p_cost_detail_selection varchar2,
2787 p_cost_detail_is_ele_set varchar2,
2788 p_cost_detail_ele_set varchar2,
2789 p_cost_detail_is_class varchar2,
2790 p_cost_detail_class varchar2,
2791 p_cost_detail_is_element varchar2,
2792 p_cost_detail_element varchar2,
2793 p_cost_detail_asg_set varchar2,
2794 p_cost_detail_accruals varchar2,
2795 p_action_parameter_group varchar2, --bug 12548555
2796 ProcessDesc in varchar2,
2797 RequestorUsername in varchar2,
2798 ProcessOwner in varchar2,
2799 Workflowprocess in varchar2 default null,
2800 item_type in varchar2 default null,
2801 item_key in varchar2
2802 ) is
2803
2804 ItemType varchar2(30) := item_type;
2805 ItemKey varchar2(30) := item_key;
2806 ItemUserKey varchar2(80) := ProcessDesc;
2807 l_business_group_id number(30) := p_business_group_id;
2808 l_payroll_id number(16) := to_number(p_payroll_id);
2809 l_consolidation_set_id number(16) := to_number(p_consolidation_set_id);
2810
2811
2812 lv_prc_list varchar2(3200);
2813 lv_process_list_subject varchar2(240);
2814 lv_process_list_text varchar2(3200);
2815 lv_process_list_html_1 varchar2(3200);
2816 lv_process_list_html_2 varchar2(3200);
2817
2818 lv_contact_user_name varchar2(80);
2819 lv_orig_system varchar2(40);
2820 lv_orig_system_id varchar2(40);
2821 lv_role_name varchar2(50);
2822 lv_role_display_name varchar2(50);
2823
2824 lv_payroll_name varchar2(80);
2825 lv_consolidation_set_name varchar2(80);
2826 lv_gre_name varchar2(80);
2827 lv_tax_group varchar2(80);
2828 lv_organization varchar2(80);
2829 lv_location varchar2(80);
2830 lv_date_time varchar2(80);
2831
2832 lv_is_batch varchar2(1) := 'N';
2833 lv_is_retropay varchar2(1) := 'N';
2834 lv_is_retro_notification varchar2(1) := 'N';
2835 lv_is_gtn varchar2(1);
2836 lv_is_sdr varchar2(1);
2837 lv_is_payroll_register varchar2(1);
2838 lv_is_element_register varchar2(1);
2839 lv_is_deductions varchar2(1);
2840 lv_is_direct_deposit varchar2(1);
2841 lv_is_chequewriter varchar2(1);
2842 lv_is_deposit_advice varchar2(1);
2843 lv_is_roe varchar2(1);
2844 lv_is_roe_mag varchar2(1);
2845 lv_is_costing_summary varchar2(1);
2846 lv_is_consolidation_set varchar2(1);
2847 lv_is_exception varchar2(1);
2848
2849
2850 lv_workflow_mode varchar2(1);
2851 lv_batch_name varchar2(80);
2852 lv_retro_period varchar2(80);
2853 lv_event_group_name varchar2(80);
2854 lv_retropay_asg_set varchar2(80);
2855 lv_retropay_element_set varchar2(80);
2856 lv_retropay_definition varchar2(80);--bug 12704242
2857 lv_payroll_element_set varchar2(80);
2858 lv_payroll_assignment_set varchar2(80);
2859 lv_payroll_run_type varchar2(80);
2860 lv_gtn_period varchar2(80);
2861 lv_payroll_process varchar2(80);
2862 lv_account_number varchar2(80);
2863 lv_federal varchar2(80);
2864 lv_province varchar2(80);
2865 lv_reporting_dimension varchar2(80);
2866 lv_exp_group_name varchar2(80);
2867 lv_exp_report_name varchar2(80);
2868 lv_exp_vartype_name varchar2(80);
2869 lv_exp_finder_pqp varchar2(80);
2870 lv_prepay_payment_method varchar2(80);
2871 lv_ele_element_set varchar2(80);
2872 lv_ele_element_class varchar2(80);
2873 lv_ele_element_name varchar2(80);
2874 lv_ele_employee varchar2(80);
2875 lv_ded_consolidation_dummy varchar2(80);
2876 lv_deduction_class varchar2(80);
2877 lv_deduction_name varchar2(80);
2878 lv_reg_suppress_zero varchar2(80);
2879 lv_reg_full_report_ver varchar2(80);
2880 lv_reg_reporting_dimension varchar2(80);
2881 lv_reg_sort_one varchar2(80);
2882 lv_reg_sort_two varchar2(80);
2883 lv_reg_sort_three varchar2(80);
2884 lv_reg_employee_page_break varchar2(80);
2885 lv_cheque_payment_type varchar2(80);
2886 lv_cheque_payment_method varchar2(80);
2887 lv_cheque_sort_sequence varchar2(80);
2888 lv_cheque_style varchar2(80);
2889 lv_da_data_type varchar2(80);
2890 lv_roe_asg_set varchar2(80);
2891 lv_roe_worksheet_person varchar2(80);
2892 lv_roe_mag_media_person varchar2(80);
2893 lv_cost_summary_accruals varchar2(80);
2894 lv_cost_summary_file_out varchar2(80);
2895 lv_cost_detail_ele_set varchar2(80);
2896 lv_cost_detail_ele_class varchar2(80);
2897 lv_cost_detail_ele_name varchar2(80);
2898 lv_cost_detail_asg_set varchar2(80);
2899 lv_cost_detail_accruals varchar2(80);
2900 l_dd_dummy char(1); -- bug 12548555
2901
2902 l_user_id number;
2903 l_resp_id number;
2904 l_resp_appl_id number;
2905 l_org_id number;
2906 l_per_sec_id number;
2907 l_sec_grp_id number;
2908
2909 varname wf_engine.NameTabTyp;
2910 varval wf_engine.TextTabTyp;
2911 num_varname wf_engine.NameTabTyp;
2912 num_varvalue wf_engine.NumTabTyp;
2913
2914 TYPE char80_table IS TABLE OF VARCHAR2(80)
2915 INDEX BY BINARY_INTEGER;
2916
2917 lv_conc_prog_name char80_table;
2918 i number;
2919 j number;
2920 k number;
2921
2922 l_proc varchar2(80) := gv_package||'.StartProcess';
2923
2924 cursor cur_workflow_mode is
2925 select nvl(substr(parameter_value,1,1), 'W')
2926 from pay_action_parameters
2927 where parameter_name = 'PAYROLL_CA_WF_NOTIFY_ACTION';
2928
2929 cursor cur_retro_period is
2930 select period_name
2931 from per_time_periods
2932 where payroll_id = l_payroll_id
2933 and end_date = fnd_date.canonical_to_date(p_date_earned);
2934
2935 cursor cur_event_group is
2936 select event_group_name
2937 from pay_event_groups
2938 where business_group_id = p_business_group_id
2939 and event_group_id =
2940 to_number(substr(p_retro_event_group,(length('EVT_GRP_ID=')+1)));
2941
2942 cursor cur_retropay_asg_set is
2943 select assignment_set_name
2944 from hr_assignment_sets
2945 where assignment_set_id = to_number(p_retropay_assignment_set)
2946 and business_group_id = p_business_group_id;
2947
2948 cursor cur_retropay_element_set is
2949 select element_set_name
2950 from pay_element_sets
2951 where element_set_id = to_number(p_retropay_element_set)
2952 and business_group_id = p_business_group_id;
2953
2954 --bug 12704242
2955 cursor cur_retropay_definition is
2956 select prd.retro_definition_id
2957 from pay_retro_definitions prd,
2958 per_business_groups p
2959 where prd.legislation_code = p.legislation_code
2960 and p.business_group_id = p_business_group_id;
2961
2962 cursor cur_payroll_element_set is
2963 select element_set_name
2964 from pay_element_sets
2965 where element_set_id = to_number(p_payroll_element_set)
2966 and business_group_id = p_business_group_id;
2967
2968 cursor cur_payroll_asg_set is
2969 select assignment_set_name
2970 from hr_assignment_sets
2971 where assignment_set_id = to_number(p_payroll_assignment_set)
2972 and payroll_id = l_payroll_id;
2973
2974 cursor cur_payroll_run_type is
2975 select t.run_type_name
2976 from pay_run_types_f_tl t,
2977 pay_run_types_f r
2978 where r.run_type_id = to_number(p_payroll_run_type)
2979 and r.run_type_id = t.run_type_id
2980 and t.language = userenv('LANG')
2981 and fnd_date.canonical_to_date(p_date_earned)
2982 between r.effective_start_date and r.effective_end_date;
2983
2984 cursor cur_sdr_reporting_dimension is
2985 select meaning
2986 from hr_lookups
2987 where lookup_code = p_sdr_reporting_dimension
2988 and lookup_type = 'PAY_CA_REPORT_DIMENSION'
2989 and application_id = 800;
2990
2991 cursor cur_exp_group_name is
2992 select exception_group_name
2993 from pqp_exception_report_groups
2994 where exception_group_id = to_number(p_exp_group_name);
2995
2996 cursor cur_exp_report_name is
2997 select exception_report_name
2998 from pqp_exception_reports
2999 where exception_report_id = to_number(p_exp_report_name);
3000
3001 cursor cur_exp_variance_type is
3002 select meaning
3003 from fnd_common_lookups
3004 where lookup_type = 'PQP_VARIANCE_TYPE'
3005 and lookup_code = p_exp_variance_type;
3006
3007 cursor cur_prepay_payment_method is
3008 select org_payment_method_name
3009 from pay_org_payment_methods_f_tl
3010 where org_payment_method_id = to_number(p_payment_method_override)
3011 and language = userenv('LANG');
3012
3013 cursor cur_ele_element_set is
3014 select element_set_name
3015 from pay_element_sets
3016 where element_set_id = to_number(p_ele_element_set)
3017 and element_set_type = 'C';
3018
3019 cursor cur_ele_element_class is
3020 select classification_name
3021 from pay_element_classifications
3022 where classification_id = to_number(p_ele_element_class);
3023
3024 cursor cur_ele_element_name is
3025 select element_name
3026 from pay_element_types_f_tl
3027 where element_type_id = to_number(p_ele_element_name)
3028 and language = userenv('LANG');
3029
3030 cursor cur_ele_employee is
3031 select full_name||'(Person ID='||person_id||')'
3032 from per_people_f
3033 where person_id = to_number(p_ele_employee)
3034 and fnd_date.canonical_to_date(p_date_earned)
3035 between effective_start_date and effective_end_date;
3036
3037 cursor cur_ded_class is
3038 select classification_name
3039 from pay_element_classifications
3040 where classification_id = to_number(p_deduction_class);
3041
3042 cursor cur_ded_name is
3043 select element_name
3044 from pay_element_types_f
3045 where element_type_id = to_number(p_deduction_name)
3046 and fnd_date.canonical_to_date(p_date_earned)
3047 between effective_start_date and effective_end_date;
3048
3049 cursor cur_reg_dimension is
3050 select flv.meaning
3051 from fnd_lookup_values flv, fnd_lookup_types flt
3052 where flv.lookup_code = p_reg_reporting_dim
3053 and flt.lookup_type = 'CA_DIMENSION'
3054 and flt.application_id = 800
3055 and flt.lookup_type = flv.lookup_type;
3056
3057 cursor cur_cheque_payment_type is
3058 select payment_type_name
3059 from pay_payment_types
3060 where payment_type_id = to_number(p_cheque_payment_type)
3061 and territory_code = 'CA'
3062 and category = 'CH';
3063
3064 cursor cur_cheque_payment_method is
3065 select org_payment_method_name
3066 from pay_org_payment_methods_f_tl
3067 where org_payment_method_id = to_number(p_cheque_payment_method);
3068
3069 cursor cur_cheque_sort_seq is
3070 select meaning
3071 from hr_lookups
3072 where lookup_code = p_cheque_sort_sequence
3073 and lookup_type = 'CHEQUE_PROCEDURE'
3074 and enabled_flag = 'Y';
3075
3076 cursor cur_cheque_style is
3077 select meaning
3078 from hr_lookups
3079 where lookup_code = p_cheque_style
3080 and lookup_type = 'CA_CHEQUE_DEPADV'
3081 and enabled_flag = 'Y';
3082
3083 cursor cur_deposit_advice_data_type is
3084 select meaning
3085 from hr_lookups
3086 where lookup_code = p_da_data_type
3087 and lookup_type = 'DAR_REPORT'
3088 and enabled_flag = 'Y';
3089
3090 cursor cur_roe_asg_set is
3091 select assignment_set_name
3092 from hr_assignment_sets
3093 where assignment_set_id =
3094 to_number(substr(p_roe_assignment_set,(length('ASSIGNMENT_SET_ID=')+1)));
3095
3096 cursor cur_roe_worksheet_person is
3097 select full_name
3098 from per_all_people_f
3099 where person_id = to_number(p_roe_worksheet_person)
3100 and fnd_date.canonical_to_date(p_date_earned)
3101 between effective_start_date and effective_end_date;
3102
3103 cursor cur_roe_mag_media_person is
3104 select full_name
3105 from per_all_people_f
3106 where person_id =
3107 to_number(substr(p_roe_mag_media_person,(length('PERSON_ID=')+1)))
3108 and fnd_date.canonical_to_date(p_date_earned)
3109 between effective_start_date and effective_end_date;
3110
3111 cursor cur_costing_summary_accruals is
3112 select meaning
3113 from hr_lookups
3114 where lookup_code = p_cost_summary_accruals
3115 and lookup_type = 'PAY_PAYRPCBR';
3116
3117 cursor cur_costing_summary_file_out is
3118 select meaning
3119 from fnd_common_lookups
3120 where lookup_code = p_cost_summary_file_out
3121 and lookup_type = 'REPORT_OUTPUT_TYPE'
3122 and application_id = 801
3123 and enabled_flag = 'Y';
3124
3125 cursor cur_costing_detail_ele_set is
3126 select element_set_name
3127 from pay_element_sets
3128 where element_set_id = to_number(p_cost_detail_ele_set)
3129 and element_set_type = 'C';
3130
3131 cursor cur_costing_detail_ele_class is
3132 select classification_name
3133 from pay_element_classifications
3134 where classification_id = to_number(p_cost_detail_class);
3135
3136 cursor cur_costing_detail_ele_name is
3137 select tl.element_name
3138 from pay_element_types_f el, pay_element_types_f_tl tl
3139 where el.element_type_id = to_number(p_cost_detail_element)
3140 and el.element_type_id = tl.element_type_id
3141 and tl.language = userenv('LANG')
3142 and fnd_date.canonical_to_date(p_date_earned)
3143 between el.effective_start_date and el.effective_end_date;
3144
3145 cursor cur_costing_detail_asg_set is
3146 select assignment_set_name
3147 from hr_assignment_sets
3148 where assignment_set_id = to_number(p_cost_detail_asg_set);
3149
3150 cursor cur_costing_detail_accruals is
3151 select meaning
3152 from hr_lookups
3153 where lookup_code = p_cost_detail_accruals
3154 and lookup_type = 'PAY_PAYRPCBR';
3155
3156
3157 begin
3158
3159 hr_utility.set_location('Starting: ' || l_proc, 100);
3160
3161 lv_conc_prog_name(1) := 'BEE Batch Process (Transfer)';
3162 lv_conc_prog_name(2) := 'Retro-Notifications Report (Enhanced) - PDF';
3163 lv_conc_prog_name(3) := 'Retropay (Enhanced)';
3164 lv_conc_prog_name(4) := 'Process Payroll Run';
3165 lv_conc_prog_name(5) := 'Gross to Net Summary';
3166 lv_conc_prog_name(6) := 'Payroll Message Report';
3167 lv_conc_prog_name(7) := 'Employee Assignments Not Paid';
3168 lv_conc_prog_name(8) := 'Payroll Exception Report';
3169 lv_conc_prog_name(9) := 'Statutory Deductions Report';
3170 lv_conc_prog_name(10) := 'Quebec Health Services Fund';
3171 lv_conc_prog_name(11) := 'Pre Payments';
3172 lv_conc_prog_name(12) := 'Payroll Register Report';
3173 lv_conc_prog_name(13) := 'Element Register Report';
3174 lv_conc_prog_name(14) := 'Deductions Report';
3175 lv_conc_prog_name(15) := 'Direct Deposit';
3176 lv_conc_prog_name(16) := 'Canadian Payroll Archiver';
3177 lv_conc_prog_name(17) := 'Canadian Chequewriter';
3178 lv_conc_prog_name(18) := 'Canadian Deposit Advice';
3179 lv_conc_prog_name(19) := 'Payment Report';
3180 lv_conc_prog_name(20) := 'Record of Employment by Assignment Set';
3181 lv_conc_prog_name(21) := 'ROE Worksheet';
3182 lv_conc_prog_name(22) := 'ROE-Magnetic Media';
3183 lv_conc_prog_name(23) := 'Costing';
3184 lv_conc_prog_name(24) := 'Costing Detail Report';
3185 lv_conc_prog_name(25) := 'Costing Summary Report';
3186
3187 hr_utility.trace('In StartProcess Item Type is : '|| ItemType);
3188 hr_utility.trace('In StartProcess Item Key is : '|| ItemKey);
3189 hr_utility.trace('Workflow process name is : '|| Workflowprocess);
3190
3191 select to_char(sysdate,'DD-MON-YYYY') ||' '|| to_char(sysdate,'HH24:MI:SS')
3192 into lv_date_time from dual;
3193
3194
3195 hr_utility.trace('before create_process');
3196
3197 wf_engine.createProcess(ItemType => ItemType,
3198 ItemKey => ItemKey,
3199 process => Workflowprocess);
3200
3201 wf_engine.SetItemUserKey(ItemType => ItemType,
3202 ItemKey => ItemKey,
3203 UserKey => ItemUserKey);
3204
3205 hr_utility.trace('In StartProcess before HR_SIGNON.Initialize_HR_Security');
3206
3207 HR_SIGNON.Initialize_HR_Security;
3208
3209 hr_utility.trace('After HR_SIGNON.Initialize_HR_Security');
3210
3211 l_user_id := FND_GLOBAL.USER_ID;
3212 l_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
3213 l_resp_id := FND_GLOBAL.RESP_ID;
3214 l_org_id := FND_GLOBAL.ORG_ID;
3215 l_per_sec_id := FND_GLOBAL.PER_SECURITY_PROFILE_ID;
3216 l_sec_grp_id := FND_GLOBAL.SECURITY_GROUP_ID;
3217
3218 hr_utility.trace('l_user_id = ' || l_user_id);
3219 hr_utility.trace('l_resp_appl_id = ' || l_resp_appl_id);
3220 hr_utility.trace('l_resp_id = ' || l_resp_id);
3221 hr_utility.trace('l_org_id = ' || l_org_id);
3222 hr_utility.trace('l_per_sec_id = ' || l_per_sec_id);
3223 hr_utility.trace('l_sec_grp_id = ' || l_sec_grp_id);
3224 hr_utility.trace('business group = ' || to_char(p_business_group_id));
3225
3226 lv_gre_name := '';
3227 lv_tax_group := '';
3228
3229 open cur_workflow_mode;
3230 fetch cur_workflow_mode into lv_workflow_mode;
3231
3232 if cur_workflow_mode%notfound then
3233 lv_workflow_mode := 'W';
3234 end if;
3235
3236 close cur_workflow_mode;
3237
3238 hr_utility.trace('Workflow Mode = ' || lv_workflow_mode);
3239
3240 open cur_retro_period;
3241 fetch cur_retro_period into lv_retro_period;
3242 close cur_retro_period;
3243
3244 hr_utility.trace('Retro Period = ' || lv_retro_period);
3245
3246 -- bug 12704242
3247 open cur_retropay_definition;
3248 fetch cur_retropay_definition into lv_retropay_definition;
3249 close cur_retropay_definition;
3250
3251 hr_utility.trace('Retro Definition id = ' || lv_retropay_definition);
3252
3253 open cur_event_group;
3254 fetch cur_event_group into lv_event_group_name;
3255 close cur_event_group;
3256
3257 hr_utility.trace('Event Group = ' || lv_event_group_name);
3258
3259 open cur_retropay_asg_set;
3260 fetch cur_retropay_asg_set into lv_retropay_asg_set;
3261 close cur_retropay_asg_set;
3262
3263 hr_utility.trace('RetroPay Assignment Set = ' || lv_retropay_asg_set);
3264
3265 open cur_retropay_element_set;
3266 fetch cur_retropay_element_set into lv_retropay_element_set;
3267 close cur_retropay_element_set;
3268
3269 hr_utility.trace('RetroPay Element Set = ' || lv_retropay_element_set);
3270
3271 open cur_payroll_element_set;
3272 fetch cur_payroll_element_set into lv_payroll_element_set;
3273 close cur_payroll_element_set;
3274
3275 hr_utility.trace('Payroll Run Element Set = ' || lv_payroll_element_set);
3276
3277 open cur_payroll_asg_set;
3278 fetch cur_payroll_asg_set into lv_payroll_assignment_set;
3279 close cur_payroll_asg_set;
3280
3281 hr_utility.trace('Payroll Run Assignment Set = ' || lv_payroll_assignment_set);
3282
3283 open cur_payroll_run_type;
3284 fetch cur_payroll_run_type into lv_payroll_run_type;
3285 close cur_payroll_run_type;
3286
3287 hr_utility.trace('Payroll Run Type = ' || lv_payroll_run_type);
3288
3289 open cur_sdr_reporting_dimension;
3290 fetch cur_sdr_reporting_dimension into lv_reporting_dimension;
3291 close cur_sdr_reporting_dimension;
3292
3293 hr_utility.trace('SDR Reporting Dimension = ' || lv_reporting_dimension);
3294
3295 open cur_exp_group_name;
3296 fetch cur_exp_group_name into lv_exp_group_name;
3297 close cur_exp_group_name;
3298
3299 hr_utility.trace('Exception Group Name = ' || lv_exp_group_name);
3300
3301 open cur_exp_report_name;
3302 fetch cur_exp_report_name into lv_exp_report_name;
3303 close cur_exp_report_name;
3304
3305 hr_utility.trace('Exception Report Name = ' || lv_exp_report_name);
3306
3307 open cur_exp_variance_type;
3308 fetch cur_exp_variance_type into lv_exp_vartype_name;
3309 close cur_exp_variance_type;
3310
3311 hr_utility.trace('Exception Variance Type = ' || lv_exp_vartype_name);
3312
3313 open cur_prepay_payment_method;
3314 fetch cur_prepay_payment_method into lv_prepay_payment_method;
3315 close cur_prepay_payment_method;
3316
3317 hr_utility.trace('PrePayment Payment Method = ' || lv_prepay_payment_method);
3318
3319 open cur_ele_element_set;
3320 fetch cur_ele_element_set into lv_ele_element_set;
3321 close cur_ele_element_set;
3322
3323 hr_utility.trace('Element Register Element Set = ' || lv_ele_element_set);
3324
3325 open cur_ele_element_class;
3326 fetch cur_ele_element_class into lv_ele_element_class;
3327 close cur_ele_element_class;
3328
3329 hr_utility.trace('Element Register Element Classification = ' || lv_ele_element_class);
3330
3331 open cur_ele_element_name;
3332 fetch cur_ele_element_name into lv_ele_element_name;
3333 close cur_ele_element_name;
3334
3335 hr_utility.trace('Element Register Element Name = ' || lv_ele_element_name);
3336
3337 open cur_ele_employee;
3338 fetch cur_ele_employee into lv_ele_employee;
3339 close cur_ele_employee;
3340
3341 hr_utility.trace('Element Register Employee = ' || lv_ele_employee);
3342
3343 open cur_ded_class;
3344 fetch cur_ded_class into lv_deduction_class;
3345 close cur_ded_class;
3346
3347 hr_utility.trace('Deduction Report Dedn Classification = ' || lv_deduction_class);
3348
3349 open cur_ded_name;
3350 fetch cur_ded_name into lv_deduction_name;
3351 close cur_ded_name;
3352
3353 hr_utility.trace('Deduction Report Dedn Name = ' || lv_deduction_name);
3354
3355 open cur_reg_dimension;
3356 fetch cur_reg_dimension into lv_reg_reporting_dimension;
3357 close cur_reg_dimension;
3358
3359 hr_utility.trace('Payroll Register Reporting Dimension = ' || lv_reg_reporting_dimension);
3360
3361 open cur_cheque_payment_type;
3362 fetch cur_cheque_payment_type into lv_cheque_payment_type;
3363 close cur_cheque_payment_type;
3364
3365 hr_utility.trace('Chequewriter Payment Type = ' || lv_cheque_payment_type);
3366
3367 open cur_cheque_payment_method;
3368 fetch cur_cheque_payment_method into lv_cheque_payment_method;
3369 close cur_cheque_payment_method;
3370
3371 hr_utility.trace('Chequewriter Payment Method = ' || lv_cheque_payment_method);
3372
3373 open cur_cheque_sort_seq;
3374 fetch cur_cheque_sort_seq into lv_cheque_sort_sequence;
3375 close cur_cheque_sort_seq;
3376
3377 hr_utility.trace('Chequewriter Sort Seq = ' || lv_cheque_sort_sequence);
3378
3379 open cur_cheque_style;
3380 fetch cur_cheque_style into lv_cheque_style;
3381 close cur_cheque_style;
3382
3383 hr_utility.trace('Chequewriter Style = ' || lv_cheque_style);
3384
3385 open cur_deposit_advice_data_type;
3386 fetch cur_deposit_advice_data_type into lv_da_data_type;
3387 close cur_deposit_advice_data_type;
3388
3389 hr_utility.trace('Deposit Advice Data Type = ' || lv_da_data_type);
3390
3391 open cur_roe_asg_set;
3392 fetch cur_roe_asg_set into lv_roe_asg_set;
3393 close cur_roe_asg_set;
3394
3395 hr_utility.trace('ROE Assignment Set = ' || lv_roe_asg_set);
3396
3397 open cur_roe_worksheet_person;
3398 fetch cur_roe_worksheet_person into lv_roe_worksheet_person;
3399 close cur_roe_worksheet_person;
3400
3401 hr_utility.trace('ROE Worksheet Person = ' || lv_roe_worksheet_person);
3402
3403 open cur_roe_mag_media_person;
3404 fetch cur_roe_mag_media_person into lv_roe_mag_media_person;
3405 close cur_roe_mag_media_person;
3406
3407 hr_utility.trace('ROE Magnetic Media Person = ' || lv_roe_mag_media_person);
3408
3409 open cur_costing_summary_accruals;
3410 fetch cur_costing_summary_accruals into lv_cost_summary_accruals;
3411 close cur_costing_summary_accruals;
3412
3413 hr_utility.trace('Costing Summary Accruals = ' || lv_cost_summary_accruals);
3414
3415 open cur_costing_summary_file_out;
3416 fetch cur_costing_summary_file_out into lv_cost_summary_file_out;
3417 close cur_costing_summary_file_out;
3418
3419 hr_utility.trace('Costing Summary File Format = ' || lv_cost_summary_file_out);
3420
3421 open cur_costing_detail_ele_set;
3422 fetch cur_costing_detail_ele_set into lv_cost_detail_ele_set;
3423 close cur_costing_detail_ele_set;
3424
3425 hr_utility.trace('Costing Detail Element Set = ' || lv_cost_detail_ele_set);
3426
3427 open cur_costing_detail_ele_name;
3428 fetch cur_costing_detail_ele_name into lv_cost_detail_ele_name;
3429 close cur_costing_detail_ele_name;
3430
3431 hr_utility.trace('Costing Detail Element Name = ' || lv_cost_detail_ele_name);
3432
3433 open cur_costing_detail_ele_class;
3434 fetch cur_costing_detail_ele_class into lv_cost_detail_ele_class;
3435 close cur_costing_detail_ele_class;
3436
3437 hr_utility.trace('Costing Detail Element Classification = ' || lv_cost_detail_ele_class);
3438
3439 open cur_costing_detail_asg_set;
3440 fetch cur_costing_detail_asg_set into lv_cost_detail_asg_set;
3441 close cur_costing_detail_asg_set;
3442
3443 hr_utility.trace('Costing Detail Assignment Set = ' || lv_cost_detail_asg_set);
3444
3445 open cur_costing_detail_accruals;
3446 fetch cur_costing_detail_accruals into lv_cost_detail_accruals;
3447 close cur_costing_detail_accruals;
3448
3449 hr_utility.trace('Costing Detail Accruals = ' || lv_cost_detail_accruals);
3450
3451 begin
3452 select period_name
3453 into lv_gtn_period
3454 from per_time_periods
3455 where payroll_id = to_number(p_payroll_id)
3456 and time_period_id = to_number(p_gross_to_net_period);
3457
3458 hr_utility.trace('lv_gtn_period = ' || lv_gtn_period);
3459
3460 exception when no_data_found then
3461 hr_utility.trace('lv_gtn_period exception: no_data_found ');
3462 null;
3463
3464 end;
3465
3466 begin
3467 select fnd_date.date_to_chardate(effective_date)||'('||to_char(request_id)||')'
3468 into lv_payroll_process
3469 from pay_payroll_actions
3470 where payroll_action_id = to_number(p_payroll_process);
3471
3472 hr_utility.trace('lv_payroll_process = ' || lv_payroll_process);
3473
3474 exception when no_data_found then
3475 hr_utility.trace('lv_payroll_process exception: no_data_found ');
3476 null;
3477
3478 end;
3479
3480 begin
3481 select to_char(sysdate, 'HHSSSS')
3482 into lv_exp_finder_pqp
3483 from dual;
3484
3485 hr_utility.trace('lv_exp_finder_pqp = ' || lv_exp_finder_pqp);
3486
3487 exception when no_data_found then
3488 hr_utility.trace('lv_exp_finder_pqp exception: no_data_found ');
3489 null;
3490
3491 end;
3492
3493 -- Payroll Name
3494 begin
3495 select payroll_name
3496 into lv_payroll_name
3497 from pay_all_payrolls_f
3498 where payroll_id = l_payroll_id
3499 and fnd_date.canonical_to_date(p_date_earned)
3500 between effective_start_date and effective_end_date;
3501
3502 hr_utility.trace('lv_payroll_name = ' || lv_payroll_name);
3503
3504 exception when no_data_found then
3505 hr_utility.trace('lv_payroll_name exception: no_data_found ');
3506 null;
3507
3508 end;
3509
3510 -- Consolidation Set Name
3511 begin
3512 select consolidation_set_name
3513 into lv_consolidation_set_name
3514 from pay_consolidation_sets
3515 where consolidation_set_id = l_consolidation_set_id;
3516
3517 hr_utility.trace('lv_consolidation_set_name = ' || lv_consolidation_set_name);
3518
3519 exception when no_data_found then
3520 hr_utility.trace('lv_consolidation_set_name exception: no_data_found ');
3521 null;
3522
3523 end;
3524
3525 -- Organization
3526 begin
3527 select name
3528 into lv_organization
3529 from per_organization_units
3530 where organization_id = to_number(p_organization)
3531 and business_group_id = p_business_group_id
3532 and fnd_date.canonical_to_date(p_date_earned) between
3533 date_from and nvl(date_to,to_date('31/12/4712','DD/MM/YYYY'));
3534
3535
3536 hr_utility.trace('lv_organization = ' || lv_organization);
3537
3538 exception when no_data_found then
3539 hr_utility.trace('lv_organization exception: no_data_found ');
3540 null;
3541
3542 end;
3543
3544 -- Location
3545 begin
3546 select location_code||' '||description
3547 into lv_location
3548 from hr_locations
3549 where location_id = to_number(p_location);
3550
3551 hr_utility.trace('lv_location = ' || lv_location);
3552
3553 exception when no_data_found then
3554 hr_utility.trace('lv_location exception: no_data_found ');
3555 null;
3556
3557 end;
3558
3559 -- GRE Name
3560 if p_gre_or_tax_group = 'GRE' then
3561 begin
3562 select name
3563 into lv_gre_name
3564 from hr_ca_tax_units_v
3565 where tax_unit_id = to_number(p_gre)
3566 and business_group_id = p_business_group_id
3567 and fnd_date.canonical_to_date(p_date_earned) between
3568 date_from and nvl(date_to,to_date('31/12/4712','DD/MM/YYYY'));
3569
3570 hr_utility.trace('lv_gre_name = ' || lv_gre_name);
3571
3572 exception when no_data_found then
3573 hr_utility.trace('lv_gre_name exception: no_data_found ');
3574 null;
3575
3576 end;
3577 end if;
3578
3579 -- Tax Group Name
3580 if p_gre_or_tax_group = 'Tax Group' then
3581 begin
3582 select hou.name
3583 into lv_tax_group
3584 from hr_all_organization_units hou,
3585 hr_organization_information hoi
3586 where hou.organization_id = hoi.organization_id
3587 and hoi.org_information_context = 'CLASS'
3588 and hoi.org_information1 = 'CA_TAX_GROUP'
3589 and hou.business_group_id = p_business_group_id
3590 and hou.organization_id = to_number(p_tax_group)
3591 and fnd_date.canonical_to_date(p_date_earned) between
3592 hou.date_from and nvl(hou.date_to,to_date('31/12/4712','DD/MM/YYYY'));
3593
3594 hr_utility.trace('lv_tax_group = ' || lv_tax_group);
3595
3596 exception when no_data_found then
3597 hr_utility.trace('lv_tax_group exception: no_data_found ');
3598 null;
3599
3600 end;
3601 end if;
3602
3603 -- Account Number
3604 if p_quebec = 'Y' then
3605 begin
3606 select pcp.account_number
3607 into lv_account_number
3608 from hr_organization_information hoi1,
3609 hr_organization_information hoi2,
3610 pay_ca_pmed_accounts pcp
3611 where hoi1.org_information_context = 'CLASS'
3612 and hoi1.org_information1 = 'CA_PMED'
3613 and hoi1.org_information2 = 'Y'
3614 and hoi1.organization_id = hoi2.organization_id
3615 and pcp.organization_id = hoi1.organization_id
3616 and pcp.business_group_id = p_business_group_id
3617 and pcp.source_id = to_number(p_qhsf_account_number)
3618 and hoi2.org_information1 = 'QC'
3619 and hoi2.org_information_context = 'Provincial Information';
3620
3621 hr_utility.trace('lv_account_number = ' || lv_account_number);
3622
3623 exception when no_data_found then
3624 hr_utility.trace('lv_account_number exception: no_data_found ');
3625 null;
3626
3627 end;
3628 end if;
3629
3630 -- Federal/Province Flags for SDR
3631
3632 if p_sdr_federal = 'Y' then
3633 lv_federal := 'Yes';
3634 elsif p_sdr_federal = 'N' then
3635 lv_federal := 'No';
3636 else
3637 lv_federal := '';
3638 end if;
3639
3640 if p_sdr_province = 'Y' then
3641 lv_province := 'Yes';
3642 elsif p_sdr_province = 'N' then
3643 lv_province := 'No';
3644 else
3645 lv_province := '';
3646 end if;
3647
3648
3649 if p_consolidation_set_id is null then
3650 lv_ded_consolidation_dummy := 'X';
3651 else
3652 lv_ded_consolidation_dummy := '';
3653 end if;
3654
3655 if p_reg_suppress_zero = 'Y' then
3656 lv_reg_suppress_zero := 'Yes';
3657 elsif p_reg_suppress_zero = 'N' then
3658 lv_reg_suppress_zero := 'No';
3659 end if;
3660
3661 if p_reg_full_report_ver = 'Y' then
3662 lv_reg_full_report_ver := 'Yes';
3663 elsif p_reg_full_report_ver = 'N' then
3664 lv_reg_full_report_ver := 'No';
3665 end if;
3666
3667 if p_reg_employee_page_break = 'Y' then
3668 lv_reg_employee_page_break := 'Yes';
3669 elsif p_reg_employee_page_break = 'N' then
3670 lv_reg_employee_page_break := 'No';
3671 end if;
3672
3673
3674 begin
3675 select meaning
3676 into lv_reg_sort_one
3677 from hr_lookups
3678 where lookup_code = p_reg_sort_one
3679 and lookup_type = 'PAY_CA_YE_SORT_CODE';
3680
3681 hr_utility.trace('lv_reg_sort_one = ' || lv_reg_sort_one );
3682
3683 exception when no_data_found then
3684 hr_utility.trace('lv_reg_sort_one exception: no_data_found ');
3685 null;
3686 end;
3687
3688 begin
3689 select meaning
3690 into lv_reg_sort_two
3691 from hr_lookups
3692 where lookup_code = p_reg_sort_two
3693 and lookup_type = 'PAY_CA_YE_SORT_CODE';
3694
3695 hr_utility.trace('lv_reg_sort_two = ' || lv_reg_sort_two );
3696
3697 exception when no_data_found then
3698 hr_utility.trace('lv_reg_sort_two exception: no_data_found ');
3699 null;
3700 end;
3701
3702 begin
3703 select meaning
3704 into lv_reg_sort_three
3705 from hr_lookups
3706 where lookup_code = p_reg_sort_three
3707 and lookup_type = 'PAY_CA_YE_SORT_CODE';
3708
3709 hr_utility.trace('lv_reg_sort_three = ' || lv_reg_sort_three );
3710
3711 exception when no_data_found then
3712 hr_utility.trace('lv_reg_sort_three exception: no_data_found ');
3713 null;
3714 end;
3715
3716 begin
3717
3718 if p_batch_id is not null then
3719
3720 select batch_name
3721 into lv_batch_name
3722 from pay_batch_headers
3723 where batch_id = p_batch_id;
3724
3725 hr_utility.trace('Batch Name :' ||lv_batch_name);
3726
3727 end if;
3728
3729 exception when no_data_found then
3730 lv_batch_name := '';
3731 hr_utility.trace('lv_batch_name exception: no_data_found ');
3732 null;
3733
3734 end;
3735
3736 begin
3737 /* currently we are only looking at the context at payroll level
3738 as we do not have any required parameter for GRE on the SRS
3739 screen, if we want to use the contact defined at GRE level then
3740 we need to modify the package and SRS definition to get the
3741 GRE_ID , and pass it in the following function.
3742 */
3743
3744 lv_contact_user_name := get_notifier(to_number(p_payroll_id),
3745 to_number(p_gre),
3746 p_date_earned);
3747
3748 exception when others then
3749 hr_utility.trace('lv_contact_user_name exception: OTHERS at get_notifier');
3750 null;
3751
3752 end;
3753
3754 begin
3755
3756 select orig_system,
3757 orig_system_id,
3758 name,
3759 display_name
3760 into lv_orig_system,
3761 lv_orig_system_id,
3762 lv_role_name,
3763 lv_role_display_name
3764 from wf_roles
3765 where name = lv_contact_user_name ;
3766
3767 exception when no_data_found then
3768 hr_utility.trace('lv_role_name exception: no_data_found');
3769 null;
3770
3771 end;
3772
3773 hr_utility.trace('orig system : '|| lv_orig_system);
3774 hr_utility.trace('orig system id : '|| lv_orig_system_id);
3775 hr_utility.trace('role name = ' || lv_role_name);
3776
3777 wf_engine.SetItemOwner (itemtype => ItemType,
3778 itemkey => ItemKey,
3779 owner => ProcessOwner);
3780
3781 lv_process_list_html_2 := 'WF_NOTIFICATION(ATTRS';
3782
3783 /* set variables that run processes */
3784
3785 -- BEE Transfer
3786 if p_batch_id is not null then
3787 lv_is_batch := 'Y';
3788 lv_prc_list := lv_prc_list||lv_conc_prog_name(1)||wf_core.newline;
3789 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS01';
3790 else
3791 lv_is_batch := 'N';
3792 end if;
3793
3794 -- Retro Notification Report
3795
3796 /* if ((p_retro_assignment_set is not null) and
3797 (p_retro_event_group is not null)) then
3798
3799 lv_is_retro_notification := 'Y';
3800 lv_prc_list := lv_prc_list||lv_conc_prog_name(2)||wf_core.newline;
3801 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS02';
3802
3803 -- If the Retro Notification is going to be run
3804 -- then the RetroPay assignment set should be the
3805 -- same one that is generated by Retro Notification
3806
3807 lv_retropay_asg_set := p_retro_assignment_set;
3808
3809 else
3810 lv_is_retro_notification := 'N';
3811 end if;*/
3812 -- Retro Notification Report (enhanced) PDF
3813 -- bug 12704242
3814 if (p_retro_template_name is not null) then
3815
3816 lv_is_retro_notification := 'Y';
3817 lv_prc_list := lv_prc_list||lv_conc_prog_name(2)||wf_core.newline;
3818 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS02';
3819
3820 -- If the Retro Notification is going to be run
3821 -- then the RetroPay assignment set should be the
3822 -- same one that is generated by Retro Notification
3823
3824 lv_retropay_asg_set := p_retro_assignment_set;
3825
3826 else
3827 lv_is_retro_notification := 'N';
3828 end if;
3829
3830 -- RetroPay By Element
3831
3832 /* if (((p_retropay_assignment_set is not null) or
3833 (p_retro_assignment_set is not null)) and
3834 (p_retropay_element_set is not null) and
3835 (p_retro_start_date is not null)) then
3836 lv_is_retropay := 'Y';
3837 lv_prc_list := lv_prc_list||lv_conc_prog_name(3)||wf_core.newline;
3838 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS03';
3839 else
3840 lv_is_retropay := 'N';
3841 end if;
3842
3843 lv_prc_list := lv_prc_list||lv_conc_prog_name(4)||wf_core.newline;
3844 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS04';*/
3845
3846 -- RetroPay (enhanced)
3847 -- bug 12704242
3848
3849 if (lv_is_retro_notification = 'Y') then
3850 lv_is_retropay := 'Y';
3851 lv_prc_list := lv_prc_list||lv_conc_prog_name(3)||wf_core.newline;
3852 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS03';
3853 else
3854 lv_is_retropay := 'N';
3855 end if;
3856
3857 lv_prc_list := lv_prc_list||lv_conc_prog_name(4)||wf_core.newline;
3858 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS04';
3859
3860 -- Gross to Net Summary
3861
3862 if ((p_gross_to_net_period is not null)) then
3863 lv_is_gtn := 'Y';
3864 lv_prc_list := lv_prc_list||lv_conc_prog_name(5)||wf_core.newline;
3865 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS05';
3866 else
3867 lv_is_gtn := 'N';
3868 end if;
3869
3870 lv_prc_list := lv_prc_list||lv_conc_prog_name(6)||wf_core.newline;
3871 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS06';
3872
3873 -- Employee Assignments Not Paid
3874 if ((p_consolidation_set_id is not null)) then
3875 lv_is_consolidation_set := 'Y';
3876 lv_prc_list := lv_prc_list||lv_conc_prog_name(7)||wf_core.newline;
3877 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS07';
3878 else
3879 lv_is_consolidation_set := 'N';
3880 end if;
3881
3882 -- Payroll Exception Report
3883 if ((p_consolidation_set_id is not null) and
3884 ((p_exp_report_name is not null) or
3885 (p_exp_group_name is not null))) then
3886 lv_is_exception := 'Y';
3887 lv_prc_list := lv_prc_list||lv_conc_prog_name(8)||wf_core.newline;
3888 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS08';
3889 else
3890 lv_is_exception := 'N';
3891 end if;
3892
3893 -- Statutory Deductions Report
3894
3895 if ((p_sdr_federal is not null) and
3896 (p_sdr_reporting_dimension is not null)) then
3897 lv_is_sdr := 'Y';
3898 lv_prc_list := lv_prc_list||lv_conc_prog_name(9)||wf_core.newline;
3899 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS09';
3900 else
3901 lv_is_sdr := 'N';
3902 end if;
3903
3904 -- Quebec Health Services Fund
3905
3906 if (p_quebec = 'Y') then
3907 lv_prc_list := lv_prc_list||lv_conc_prog_name(10)||wf_core.newline;
3908 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS10';
3909 end if;
3910
3911
3912 -- Pre Payment
3913
3914 if (p_consolidation_set_id is not null) then
3915 lv_prc_list := lv_prc_list||lv_conc_prog_name(11)||wf_core.newline;
3916 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS11';
3917 end if;
3918
3919
3920 -- Payroll Register Report
3921
3922 if ((p_consolidation_set_id is not null) and
3923 (p_reg_suppress_zero is not null) and
3924 (p_reg_req_num is not null) and
3925 (p_reg_reporting_dim is not null)) then
3926 lv_is_payroll_register := 'Y';
3927 lv_prc_list := lv_prc_list||lv_conc_prog_name(12)||wf_core.newline;
3928 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS12';
3929 else
3930 lv_is_payroll_register := 'N';
3931 end if;
3932
3933 -- Element Register Report
3934
3935 if (p_ele_selection_criterion is not null)
3936 then
3937 lv_is_element_register := 'Y';
3938 lv_prc_list := lv_prc_list||lv_conc_prog_name(13)||wf_core.newline;
3939 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS13';
3940 else
3941 lv_is_element_register := 'N';
3942 end if;
3943
3944 -- Deductions Report
3945
3946 if ((p_deduction_sort_one is not null) and
3947 (p_deduction_sort_two is not null) and
3948 (p_deduction_sort_three is not null))
3949 then
3950 lv_is_deductions := 'Y';
3951 lv_prc_list := lv_prc_list||lv_conc_prog_name(14)||wf_core.newline;
3952 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS14';
3953 else
3954 lv_is_deductions := 'N';
3955 end if;
3956
3957 -- Direct Deposit
3958
3959 if ((p_consolidation_set_id is not null) and
3960 (p_dd_payment_method is not null))
3961 then
3962 lv_is_direct_deposit := 'Y';
3963 lv_prc_list := lv_prc_list||lv_conc_prog_name(15)||wf_core.newline;
3964 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS15';
3965 else
3966 lv_is_direct_deposit := 'N';
3967 end if;
3968
3969
3970 -- Canadian Payroll Archiver
3971
3972 if (p_consolidation_set_id is not null) then
3973 lv_prc_list := lv_prc_list||lv_conc_prog_name(16)||wf_core.newline;
3974 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS16';
3975 end if;
3976
3977
3978 -- Canadian Cheque Writer
3979
3980 if ((p_consolidation_set_id is not null) and
3981 (p_cheque_payment_method is not null) and
3982 (p_cheque_payment_type is not null) and
3983 (p_cheque_sort_sequence is not null) and
3984 (p_cheque_style is not null) and
3985 (p_start_cheque_number is not null)) then
3986 lv_is_chequewriter := 'Y';
3987 lv_prc_list := lv_prc_list||lv_conc_prog_name(17)||wf_core.newline;
3988 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS17';
3989 else
3990 lv_is_chequewriter := 'N';
3991 end if;
3992
3993 -- Canadian Deposit Advice
3994
3995 if ((p_da_data_type is not null) and
3996 (p_consolidation_set_id is not null)) then
3997 lv_is_deposit_advice := 'Y';
3998 lv_prc_list := lv_prc_list||lv_conc_prog_name(18)||wf_core.newline;
3999 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS18';
4000 else
4001 lv_is_deposit_advice := 'N';
4002 end if;
4003
4004 -- Payment Report
4005
4006 if (p_consolidation_set_id is not null) then
4007 lv_prc_list := lv_prc_list||lv_conc_prog_name(19)||wf_core.newline;
4008 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS19';
4009 end if;
4010
4011 -- Record of Employment
4012
4013 if ((p_roe_assignment_set is not null)) then
4014 lv_is_roe := 'Y';
4015 lv_prc_list := lv_prc_list||lv_conc_prog_name(20)||wf_core.newline;
4016 lv_prc_list := lv_prc_list||lv_conc_prog_name(21)||wf_core.newline;
4017 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS20';
4018 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS21';
4019
4020 if (p_roe_mag_roe_type is not null) then
4021 lv_is_roe_mag := 'Y';
4022 lv_prc_list := lv_prc_list||lv_conc_prog_name(22)||wf_core.newline;
4023 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS22';
4024 else
4025 lv_is_roe_mag := 'N';
4026 end if;
4027 else
4028 lv_is_roe := 'N';
4029 lv_is_roe_mag := 'N';
4030 end if;
4031
4032 -- Costing
4033
4034 if ((p_consolidation_set_id is not null)) then
4035 lv_prc_list := lv_prc_list||lv_conc_prog_name(23)||wf_core.newline;
4036 lv_prc_list := lv_prc_list||lv_conc_prog_name(24)||wf_core.newline;
4037 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS23';
4038 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS24';
4039
4040 if (p_cost_summary_file_out is not null) then
4041 lv_is_costing_summary := 'Y';
4042 lv_prc_list := lv_prc_list||lv_conc_prog_name(25)||wf_core.newline;
4043 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS25';
4044 else
4045 lv_is_costing_summary := 'N';
4046 end if;
4047 else
4048 lv_is_costing_summary := 'N';
4049 end if;
4050
4051 lv_process_list_subject := 'List of Processes and Reports which will be submitted';
4052
4053 if lv_workflow_mode = 'W' then
4054
4055 lv_process_list_text := 'Review the List of Processes and Reports which will be submitted based on the input parameters, please click on the '||''''||'Continue'||''''||' button to start processing.'||wf_core.newline||wf_core.newline||lv_prc_list;
4056
4057 lv_process_list_html_1 := 'Review the List of Processes and Reports which will be submitted based on the input parameters, please click on the '||''''||'Continue'||''''||' button to start processing.';
4058
4059 else
4060
4061 lv_process_list_text := 'Review the List of Processes and Reports which will be submitted based on the input parameters.'||wf_core.newline||wf_core.newline||lv_prc_list;
4062
4063 lv_process_list_html_1 := 'Review the List of Processes and Reports which will be submitted based on the input parameters.';
4064
4065 end if;
4066
4067 lv_process_list_html_2 := lv_process_list_html_2||')';
4068
4069 hr_utility.trace('p_payroll_id = ' || p_payroll_id);
4070 hr_utility.trace('p_consolidation_set_id = ' || p_consolidation_set_id);
4071 hr_utility.trace('p_date_earned = ' || p_date_earned);
4072 hr_utility.trace('p_date_paid = ' || p_date_paid);
4073 hr_utility.trace('p_payroll_assignment_set = ' || p_payroll_assignment_set);
4074 hr_utility.trace('p_payroll_run_type = ' || p_payroll_run_type);
4075
4076 begin
4077
4078 num_varname(1) := 'USER_ID';
4079 num_varvalue(1) := l_user_id;
4080 num_varname(2) := 'APPLICATION_ID';
4081 num_varvalue(2) := l_resp_appl_id;
4082 num_varname(3) := 'RESPONSIBILITY_ID';
4083 num_varvalue(3) := l_resp_id;
4084 num_varname(4) := 'ORG_ID';
4085 num_varvalue(4) := l_org_id;
4086 num_varname(5) := 'PER_SECURITY_PROFILE_ID';
4087 num_varvalue(5) := l_per_sec_id;
4088 num_varname(6) := 'SECURITY_GROUP_ID';
4089 num_varvalue(6) := l_sec_grp_id;
4090
4091
4092 i := 0;
4093
4094 varname(i) := 'P_BUSINESS_GROUP_ID';
4095 varval(i) := to_char(p_business_group_id);
4096 i := i+1;
4097 varname(i) := 'WORKFLOW_MODE';
4098 varval(i) := lv_workflow_mode;
4099 i := i+1;
4100 varname(i) := 'CURR_ITEM_TYPE';
4101 varval(i) := ItemType;
4102 i := i+1;
4103 varname(i) := 'CURR_ITEM_KEY';
4104 varval(i) := ItemKey;
4105 i := i+1;
4106 varname(i) := 'ROLE_NAME';
4107 varval(i) := lv_role_name;
4108 i := i+1;
4109 varname(i) := 'USER';
4110 varval(i) := lv_contact_user_name;
4111 i := i+1;
4112
4113
4114 varname(i) := 'IS_CONSOLIDATION_SET';
4115 varval(i) := lv_is_consolidation_set;
4116 i := i+1;
4117 varname(i) := 'PAYROLL_NAME';
4118 varval(i) := lv_payroll_name;
4119 i := i+1;
4120 varname(i) := 'PAYROLL_ID';
4121 varval(i) := p_payroll_id;
4122 i := i+1;
4123 varname(i) := 'CONSOLIDATION_SET_NAME';
4124 varval(i) := lv_consolidation_set_name;
4125 i := i+1;
4126 varname(i) := 'CONSOLIDATION_SET_ID';
4127 varval(i) := p_consolidation_set_id;
4128 i := i+1;
4129 varname(i) := 'DATE_EARNED';
4130 varval(i) := p_date_earned;
4131 i := i+1;
4132 varname(i) := 'DATE_PAID';
4133 varval(i) := p_date_paid;
4134 i := i+1;
4135 varname(i) := 'DATE_TIME';
4136 varval(i) := lv_date_time;
4137 i := i+1;
4138 varname(i) := 'GRE';
4139 varval(i) := p_gre;
4140 i := i+1;
4141 varname(i) := 'GRE_NAME';
4142 varval(i) := lv_gre_name;
4143 i := i+1;
4144 varname(i) := 'TAX_GROUP';
4145 varval(i) := p_tax_group;
4146 i := i+1;
4147 varname(i) := 'TAX_GROUP_NAME';
4148 varval(i) := lv_tax_group;
4149 i := i+1;
4150 varname(i) := 'ORGANIZATION';
4151 varval(i) := p_organization;
4152 i := i+1;
4153 varname(i) := 'ORGANIZATION_NAME';
4154 varval(i) := lv_organization;
4155 i := i+1;
4156 varname(i) := 'LOCATION';
4157 varval(i) := p_location;
4158 i := i+1;
4159 varname(i) := 'LOCATION_NAME';
4160 varval(i) := lv_location;
4161 i := i+1;
4162
4163 -- BEE (Transfer)
4164
4165 varname(i) := 'IS_BATCH';
4166 varval(i) := lv_is_batch;
4167 i := i+1;
4168 varname(i) := 'BATCH_ID';
4169 varval(i) := to_char(p_batch_id);
4170 i := i+1;
4171 varname(i) := 'BEE_BATCH_NAME';
4172 varval(i) := lv_batch_name;
4173 i := i+1;
4174
4175 -- Retro-Notification Report
4176
4177 varname(i) := 'IS_RETRO_NOTIFICATION';
4178 varval(i) := lv_is_retro_notification;
4179 i := i+1;
4180 varname(i) := 'RETRO_START_DATE_DUMMY';
4181 varval(i) := 'START_DATE=1900/01/01 00:00:00';
4182 i := i+1;
4183 varname(i) := 'END_DATE_LEG_PARAM';
4184 varval(i) := 'END_DATE='||p_date_earned;
4185 i := i+1;
4186 varname(i) := 'PAYROLL_LEG_PARAM';
4187 varval(i) := 'PAYROLL_ID='||p_payroll_id;
4188 i := i+1;
4189 varname(i) := 'RETRO_PERIOD';
4190 varval(i) := lv_retro_period;
4191 i := i+1;
4192 varname(i) := 'EVENT_GROUP';
4193 varval(i) := p_retro_event_group;
4194 i := i+1;
4195 varname(i) := 'EVENT_GROUP_NAME';
4196 varval(i) := lv_event_group_name;
4197 i := i+1;
4198 varname(i) := 'RETRO_ASSIGNMENT_SET_NAME';
4199 varval(i) := p_retro_assignment_set;
4200 i := i+1;
4201 varname(i) := 'RETRO_ASSIGNMENT_SET_NAME_DUMY';
4202 varval(i) := 'ASG_SET='||p_retro_assignment_set;
4203 i := i+1;
4204 --bug 12704242
4205 varname(i) := 'RETRO_REPORT_DATE';
4206 varval(i) := 'REPORT_DATE='||to_char(sysdate,'DD/MM/YYYYHH24:MI:SS');
4207 i := i+1;
4208 varname(i) := 'RETRO_TEMPLATE_NAME';
4209 varval(i) := p_retro_template_name;
4210 i := i+1;
4211 varname(i) := 'RETRO_GENERATE_REPORT';
4212 varval(i) := p_retro_generate_report;
4213 i := i+1;
4214
4215
4216 -- RetroPay By Element
4217
4218 varname(i) := 'IS_RETROPAY';
4219 varval(i) := lv_is_retropay;
4220 i := i+1;
4221 varname(i) := 'RETRO_ASSIGNMENT_SET_ID';
4222 varval(i) := p_retropay_assignment_set;
4223 i := i+1;
4224 varname(i) := 'RETROPAY_ASG_SET_NAME';
4225 varval(i) := lv_retropay_asg_set;
4226 i := i+1;
4227 varname(i) := 'RETRO_ELEMENT_SET';
4228 varval(i) := p_retropay_element_set;
4229 i := i+1;
4230 varname(i) := 'RETROPAY_ELEMENT_SET_NAME';
4231 varval(i) := lv_retropay_element_set;
4232 i := i+1;
4233 varname(i) := 'RETRO_START_DATE';
4234 varval(i) := p_retro_start_date;
4235 i := i+1;
4236 varname(i) := 'RETRO_DEFINITION_ID';
4237 varval(i) := lv_retropay_definition;
4238 i := i+1;
4239 varname(i) := 'RETRO_ENTRY_CREATION_DATE';
4240 varval(i) := p_retropay_entry_creation_date;
4241 i := i+1;
4242
4243 -- Payroll Run Process
4244
4245 varname(i) := 'PAYROLL_ELEMENT_SET_ID';
4246 varval(i) := p_payroll_element_set;
4247 i := i+1;
4248 varname(i) := 'PAYROLL_ELEMENT_SET_NAME';
4249 varval(i) := lv_payroll_element_set;
4250 i := i+1;
4251 varname(i) := 'PAYROLL_ASSIGNMENT_SET_ID';
4252 varval(i) := p_payroll_assignment_set;
4253 i := i+1;
4254 varname(i) := 'PAYROLL_ASSIGNMENT_SET_NAME';
4255 varval(i) := lv_payroll_assignment_set;
4256 i := i+1;
4257 varname(i) := 'PAYROLL_RUN_TYPES_ID';
4258 varval(i) := p_payroll_run_type;
4259 i := i+1;
4260 varname(i) := 'PAYROLL_RUN_TYPE_NAME';
4261 varval(i) := lv_payroll_run_type;
4262 i := i+1;
4263
4264 -- Gross to Net Summary
4265
4266 varname(i) := 'IS_GTN';
4267 varval(i) := lv_is_gtn;
4268 i := i+1;
4269 varname(i) := 'GTN_PERIOD';
4270 varval(i) := p_gross_to_net_period;
4271 i := i+1;
4272 varname(i) := 'GTN_PERIOD_NAME';
4273 varval(i) := lv_gtn_period;
4274 i := i+1;
4275
4276 -- Payroll Message Report
4277
4278 varname(i) := 'SESSION_DATE';
4279 varval(i) := p_session_date;
4280 i := i+1;
4281 varname(i) := 'PAYROLL_ACTION_ID';
4282 varval(i) := p_payroll_process;
4283 i := i+1;
4284 varname(i) := 'PAYROLL_MSG_PAYROLL_PROCESS';
4285 varval(i) := lv_payroll_process;
4286 i := i+1;
4287
4288 -- Quebec Health Services Fund
4289
4290 varname(i) := 'IS_QUEBEC';
4291 varval(i) := p_quebec;
4292 i := i+1;
4293 varname(i) := 'ACCOUNT_NUMBER_ID';
4294 varval(i) := p_qhsf_account_number;
4295 i := i+1;
4296 varname(i) := 'ACCOUNT_NUMBER';
4297 varval(i) := lv_account_number;
4298 i := i+1;
4299 varname(i) := 'OVERRIDE_TABLE';
4300 varval(i) := p_qhsf_override_table;
4301 i := i+1;
4302
4303 -- Statutory Deductions Report
4304
4305 varname(i) := 'IS_SDR';
4306 varval(i) := lv_is_sdr;
4307 i := i+1;
4308 varname(i) := 'FEDERAL';
4309 varval(i) := p_sdr_federal;
4310 i := i+1;
4311 varname(i) := 'FEDERAL_NAME';
4312 varval(i) := lv_federal;
4313 i := i+1;
4314 varname(i) := 'PROVINCE';
4315 varval(i) := p_sdr_province;
4316 i := i+1;
4317 varname(i) := 'PROVINCE_NAME';
4318 varval(i) := lv_province;
4319 i := i+1;
4320 varname(i) := 'REPORTING_DIMENSION';
4321 varval(i) := p_sdr_reporting_dimension;
4322 i := i+1;
4323 varname(i) := 'REPORTING_DIMENSION_NAME';
4324 varval(i) := lv_reporting_dimension;
4325 i := i+1;
4326
4327
4328 -- Exception Report
4329
4330 varname(i) := 'IS_EXCEPTION';
4331 varval(i) := lv_is_exception;
4332 i := i+1;
4333 varname(i) := 'SELECT_REPORT_OR_GROUP';
4334 varval(i) := p_exp_report_or_group;
4335 i := i+1;
4336 varname(i) := 'EXCEPTION_LEGISLATION_CODE';
4337 varval(i) := p_exp_legislation_code;
4338 i := i+1;
4339 varname(i) := 'IS_EXCEPTION_GROUP';
4340 varval(i) := p_exp_is_group;
4341 i := i+1;
4342 varname(i) := 'EXCEPTION_GROUP_NAME';
4343 varval(i) := p_exp_group_name;
4344 i := i+1;
4345 varname(i) := 'EXCEPTION_GROUP_NAME_NAME';
4346 varval(i) := lv_exp_group_name;
4347 i := i+1;
4348 varname(i) := 'EXCEPTION_GROUP_NAME_DMY';
4349 varval(i) := 'TRANSFER_GROUP='||p_exp_group_name;
4350 i := i+1;
4351 varname(i) := 'IS_EXCEPTION_REPORT';
4352 varval(i) := p_exp_is_report;
4353 i := i+1;
4354 varname(i) := 'EXCEPTION_REPORT_NAME';
4355 varval(i) := p_exp_report_name;
4356 i := i+1;
4357 varname(i) := 'EXCEPTION_REPORT_NAME_NAME';
4358 varval(i) := lv_exp_report_name;
4359 i := i+1;
4360 varname(i) := 'EXCEPTION_REPORT_NAME_DMY';
4361 varval(i) := 'TRANSFER_REPORT='||p_exp_report_name;
4362 i := i+1;
4363 varname(i) := 'OVER_RIDE_VARIANCE_TYPE';
4364 varval(i) := p_exp_variance_type;
4365 i := i+1;
4366 varname(i) := 'OVER_RIDE_VARIANCE_TYPE_NAME';
4367 varval(i) := lv_exp_vartype_name;
4368 i := i+1;
4369 varname(i) := 'OVER_RIDE_VARIANCE_TYPE_DMY';
4370 varval(i) := 'TRANSFER_VARTYPE='||p_exp_variance_type;
4371 i := i+1;
4372 varname(i) := 'OVER_RIDE_VARIANCE_VALUE';
4373 varval(i) := p_exp_variance_value;
4374 i := i+1;
4375 varname(i) := 'OVER_RIDE_VARIANCE_VALUE_DMY';
4376 varval(i) := 'TRANSFER_VARVALUE='||p_exp_variance_value;
4377 i := i+1;
4378 varname(i) := 'TRANSFER_DATE';
4379 varval(i) := 'TRANSFER_DATE='||p_date_paid;
4380 i := i+1;
4381 varname(i) := 'PPA_FINDER_PQP';
4382 varval(i) := lv_exp_finder_pqp;
4383 i := i+1;
4384 varname(i) := 'TRANSFER_PPA_FINDER_PQP';
4385 varval(i) := 'TRANSFER_PPA_FINDER='||lv_exp_finder_pqp;
4386 i := i+1;
4387 varname(i) := 'TRANSFER_PAYROLL';
4388 varval(i) := 'TRANSFER_PAYROLL='||p_payroll_id;
4389 i := i+1;
4390 varname(i) := 'TRANSFER_CONC_SET';
4391 varval(i) := 'TRANSFER_CONC_SET='||p_consolidation_set_id;
4392 i := i+1;
4393
4394 -- PrePayment
4395
4396 varname(i) := 'PREPAY_PAYMENT_METHOD_OVERRIDE';
4397 varval(i) := p_payment_method_override;
4398 i := i+1;
4399 varname(i) := 'PREPAY_PAYMENT_METHOD_NAME';
4400 varval(i) := lv_prepay_payment_method;
4401 i := i+1;
4402
4403 -- Element Register Report
4404
4405 varname(i) := 'IS_ELEMENT';
4406 varval(i) := lv_is_element_register;
4407 i := i+1;
4408 varname(i) := 'SELECTION_CRITERION';
4409 varval(i) := p_ele_selection_criterion;
4410 i := i+1;
4411 varname(i) := 'IS_ELEMENT_SET';
4412 varval(i) := p_ele_is_element_set;
4413 i := i+1;
4414 varname(i) := 'ELE_REG_ELEMENT_SET';
4415 varval(i) := p_ele_element_set;
4416 i := i+1;
4417 varname(i) := 'ELE_REG_ELEMENT_SET_NAME';
4418 varval(i) := lv_ele_element_set;
4419 i := i+1;
4420 varname(i) := 'IS_ELEMENT_CLASSIFICATION';
4421 varval(i) := p_ele_is_element_class;
4422 i := i+1;
4423 varname(i) := 'ELEMENT_CLASSIFICATION';
4424 varval(i) := p_ele_element_class;
4425 i := i+1;
4426 varname(i) := 'ELEMENT_CLASSIFICATION_NAME';
4427 varval(i) := lv_ele_element_class;
4428 i := i+1;
4429 varname(i) := 'IS_ELEMENT_NAME';
4430 varval(i) := p_ele_is_element_name;
4431 i := i+1;
4432 varname(i) := 'ELEMENT';
4433 varval(i) := p_ele_element_name;
4434 i := i+1;
4435 varname(i) := 'ELEMENT_NAME';
4436 varval(i) := lv_ele_element_name;
4437 i := i+1;
4438 varname(i) := 'EMP_ID';
4439 varval(i) := p_ele_employee;
4440 i := i+1;
4441 varname(i) := 'EMP_NAME';
4442 varval(i) := lv_ele_employee;
4443 i := i+1;
4444
4445 -- Deduction Report(CA)
4446
4447 varname(i) := 'IS_DEDUCTIONS';
4448 varval(i) := lv_is_deductions;
4449 i := i+1;
4450 varname(i) := 'DEDN_CONSOLIDATION_DMY';
4451 varval(i) := lv_ded_consolidation_dummy;
4452 i := i+1;
4453 varname(i) := 'DEDN_CLASSIFICATION';
4454 varval(i) := p_deduction_class;
4455 i := i+1;
4456 varname(i) := 'DEDN_CLASSIFICATION_NAME';
4457 varval(i) := lv_deduction_class;
4458 i := i+1;
4459 varname(i) := 'DEDN_ID';
4460 varval(i) := p_deduction_name;
4461 i := i+1;
4462 varname(i) := 'DEDN_NAME';
4463 varval(i) := lv_deduction_name;
4464 i := i+1;
4465 varname(i) := 'SORT_ONE';
4466 varval(i) := p_deduction_sort_one;
4467 i := i+1;
4468 varname(i) := 'SORT_TWO';
4469 varval(i) := p_deduction_sort_two;
4470 i := i+1;
4471 varname(i) := 'SORT_THREE';
4472 varval(i) := p_deduction_sort_three;
4473 i := i+1;
4474
4475 -- Payroll Register Report
4476
4477
4478 varname(i) := 'IS_PAYROLL_REGISTER';
4479 varval(i) := lv_is_payroll_register;
4480 i := i+1;
4481 varname(i) := 'BUSINESS_GROUP_ID_DMY';
4482 varval(i) := 'B_G_ID='||p_business_group_id;
4483 i := i+1;
4484 varname(i) := 'PAYROLL_DMY';
4485 varval(i) := 'PY_ID='||p_payroll_id;
4486 i := i+1;
4487 varname(i) := 'CONSOLIDATION_SET_DMY';
4488 varval(i) := 'C_ST_ID='||p_consolidation_set_id;
4489 i := i+1;
4490 varname(i) := 'GRE_DMY';
4491 varval(i) := 'T_U_ID='||p_gre;
4492 i := i+1;
4493 varname(i) := 'LOCATION_DMY';
4494 varval(i) := 'L_ID='||p_location;
4495 i := i+1;
4496 varname(i) := 'ORGANIZATION_DMY';
4497 varval(i) := 'O_ID='||p_organization;
4498 i := i+1;
4499 varname(i) := 'PERSON_DMY';
4500 varval(i) := 'P_ID='||p_ele_employee;
4501 i := i+1;
4502 varname(i) := 'SUPPRESS_ZERO_CODE';
4503 varval(i) := p_reg_suppress_zero;
4504 i := i+1;
4505 varname(i) := 'SUPPRESS_ZERO';
4506 varval(i) := lv_reg_suppress_zero;
4507 i := i+1;
4508 varname(i) := 'SUPPRESS_ZERO_DMY';
4509 varval(i) := 'S_Z='||p_reg_suppress_zero;
4510 i := i+1;
4511 varname(i) := 'FULL_REPORT_VERSION_CODE';
4512 varval(i) := p_reg_full_report_ver;
4513 i := i+1;
4514 varname(i) := 'FULL_REPORT_VERSION';
4515 varval(i) := lv_reg_full_report_ver;
4516 i := i+1;
4517 varname(i) := 'FULL_REPORT_VERSION_DMY';
4518 varval(i) := 'F_R='||p_reg_full_report_ver;
4519 i := i+1;
4520 varname(i) := 'PAYREG_REPORTING_DIMENSION_C';
4521 varval(i) := p_reg_reporting_dim;
4522 i := i+1;
4523 varname(i) := 'PAYREG_REPORTING_DIMENSION';
4524 varval(i) := lv_reg_reporting_dimension;
4525 i := i+1;
4526 varname(i) := 'PAYREG_REPORTING_DIMENSION_DMY';
4527 varval(i) := 'RP_DM='||p_reg_reporting_dim;
4528 i := i+1;
4529 varname(i) := 'SORT_ONE_CODE';
4530 varval(i) := p_reg_sort_one;
4531 i := i+1;
4532 varname(i) := 'REGISTER_SORT_ONE';
4533 varval(i) := lv_reg_sort_one;
4534 i := i+1;
4535 varname(i) := 'SORT_ONE_DMY';
4536 varval(i) := 'P_S1='||p_reg_sort_one;
4537 i := i+1;
4538 varname(i) := 'SORT_TWO_CODE';
4539 varval(i) := p_reg_sort_two;
4540 i := i+1;
4541 varname(i) := 'REGISTER_SORT_TWO';
4542 varval(i) := lv_reg_sort_two;
4543 i := i+1;
4544 varname(i) := 'SORT_TWO_DMY';
4545 varval(i) := 'P_S2='||p_reg_sort_two;
4546 i := i+1;
4547 varname(i) := 'SORT_THREE_CODE';
4548 varval(i) := p_reg_sort_three;
4549 i := i+1;
4550 varname(i) := 'REGISTER_SORT_THREE';
4551 varval(i) := lv_reg_sort_three;
4552 i := i+1;
4553 varname(i) := 'SORT_THREE_DMY';
4554 varval(i) := 'P_S3='||p_reg_sort_three;
4555 i := i+1;
4556 varname(i) := 'EMPLOYEE_PAGE_BREAK_CODE';
4557 varval(i) := p_reg_employee_page_break;
4558 i := i+1;
4559 varname(i) := 'EMPLOYEE_PAGE_BREAK';
4560 varval(i) := lv_reg_employee_page_break;
4561 i := i+1;
4562 varname(i) := 'EMPLOYEE_PAGE_BREAK_DMY';
4563 varval(i) := 'EMP_BRK='||p_reg_employee_page_break;
4564 i := i+1;
4565 varname(i) := 'SEQ_NUM';
4566 varval(i) := p_reg_req_num;
4567 i := i+1;
4568 varname(i) := 'SEQ_NUM_DMY';
4569 varval(i) := 'S_N='||p_reg_req_num;
4570 i := i+1;
4571
4572 -- Direct Deposit
4573
4574 varname(i) := 'IS_DIRECT_DEPOSIT';
4575 varval(i) := lv_is_direct_deposit;
4576 i := i+1;
4577 varname(i) := 'PAYMENT_TYPE_ID';
4578 varval(i) := p_dd_payment_type;
4579 i := i+1;
4580 varname(i) := 'PAYMENT_METHOD_ID';
4581 varval(i) := p_dd_payment_method;
4582 i := i+1;
4583 varname(i) := 'OVERRIDE_DD_DATE';
4584 varval(i) := p_dd_override_date;
4585 i := i+1;
4586 varname(i) := 'FINANCIAL_INSTITUTION_CODE';
4587 varval(i) := p_dd_financial_inst;
4588 i := i+1;
4589 varname(i) := 'FINANCIAL_INSTITUTION_HIDDEN';
4590 varval(i) := 'MAGTAPE_REPORT_ID='||p_dd_financial_inst;
4591 i := i+1;
4592 varname(i) := 'OVERRIDE_CPA_CODE';
4593 varval(i) := p_dd_cpa_code;
4594 i := i+1;
4595 varname(i) := 'OVERRIDE_FILE_CREATION_NUMBER';
4596 varval(i) := p_dd_file_number;
4597 i := i+1;
4598 varname(i) := 'OVERRIDE_FCN';
4599 varval(i) := 'FILE_CREATION_NUMBER_OVERRIDE='||p_dd_file_number;
4600 i := i+1;
4601 varname(i) := 'FILE_CREATION_DATE';
4602 varval(i) := '|FILE_CREATION_DATE='||p_session_date||'|';
4603 i := i+1;
4604 varname(i) := 'OVERRIDE_CPA_HIDDEN';
4605 varval(i) := 'OVERRIDE_CPA_CODE='||p_dd_cpa_code;
4606 i := i+1;
4607 if (p_dd_financial_inst IN ('TD','NOVA_SCOT','CIBC')
4608 and NVL(fnd_profile.value_specific('PAY_CA_DD_XMLP_RUN'),'Y')='Y') then
4609 l_dd_dummy := 'Y';
4610 else l_dd_dummy := '';
4611 end if;
4612 varname(i) := 'DD_DUMMY';
4613 varval(i) := l_dd_dummy;
4614 i := i+1;
4615 varname(i) := 'REPORT_GROUP';
4616 if l_dd_dummy = 'Y' then
4617 varval(i) := 'REP_GROUP=XML_PAYMENT';
4618 else
4619 varval(i) := '';
4620 end if;
4621 i := i+1;
4622 varname(i) := 'REPORT_CATEGORY';
4623 if l_dd_dummy = 'Y' then
4624 varval(i) := 'REP_CAT=PAY_CA_DD_'||p_dd_financial_inst;
4625 else
4626 varval(i) := '';
4627 end if;
4628 i := i+1;
4629
4630
4631 -- Canadian Payroll Archiver
4632
4633 varname(i) := 'TRANSFER_PAYROLL_ARCHIVER';
4634 varval(i) := 'TRANSFER_PAYROLL_ID='||p_payroll_id;
4635 i := i+1;
4636 varname(i) := 'TRANSFER_CONC_SET_ARCHIVER';
4637 varval(i) := 'TRANSFER_CONSOLIDATION_SET_ID='||p_consolidation_set_id;
4638 i := i+1;
4639
4640
4641 -- Canadian Chequewriter
4642
4643 varname(i) := 'IS_CHEQUE_WRITER';
4644 varval(i) := lv_is_chequewriter;
4645 i := i+1;
4646 varname(i) := 'CHEQUE_PAYMENT_TYPE_ID';
4647 varval(i) := p_cheque_payment_type;
4648 i := i+1;
4649 varname(i) := 'CHEQUE_PAYMENT_TYPE_NAME';
4650 varval(i) := lv_cheque_payment_type;
4651 i := i+1;
4652 varname(i) := 'CHEQUE_PAYMENT_METHOD_ID';
4653 varval(i) := p_cheque_payment_method;
4654 i := i+1;
4655 varname(i) := 'CHEQUE_PAYMENT_METHOD_NAME';
4656 varval(i) := lv_cheque_payment_method;
4657 i := i+1;
4658 varname(i) := 'CHEQUE_SORT_SEQUENCE';
4659 varval(i) := p_cheque_sort_sequence;
4660 i := i+1;
4661 varname(i) := 'CHEQUE_SORT_SEQUENCE_MEANING';
4662 varval(i) := lv_cheque_sort_sequence;
4663 i := i+1;
4664 varname(i) := 'CHEQUE_STYLE';
4665 varval(i) := p_cheque_style;
4666 i := i+1;
4667 varname(i) := 'CHEQUE_STYLE_MEANING';
4668 varval(i) := lv_cheque_style;
4669 i := i+1;
4670 varname(i) := 'CHEQUE_START_NUMBER';
4671 varval(i) := p_start_cheque_number;
4672 i := i+1;
4673 varname(i) := 'CHEQUE_END_NUMBER';
4674 varval(i) := p_end_cheque_number;
4675 i := i+1;
4676
4677 -- Deposit Advice
4678
4679 varname(i) := 'IS_DEPOSIT_ADVICE';
4680 varval(i) := lv_is_deposit_advice;
4681 i := i+1;
4682 varname(i) := 'ADVICE_REPORT_CATEGORY';
4683 varval(i) := p_da_data_type;
4684 i := i+1;
4685 varname(i) := 'ADVICE_REPORT_CATEGORY_NAME';
4686 varval(i) := lv_da_data_type;
4687 i := i+1;
4688 varname(i) := 'ADVICE_PAYROLL';
4689 varval(i) := 'PAYROLL_ID='||p_payroll_id;
4690 i := i+1;
4691 varname(i) := 'ADVICE_CONSOLIDATION_SET';
4692 varval(i) := 'CONSOLIDATION_SET_ID='||p_consolidation_set_id;
4693 i := i+1;
4694 varname(i) := 'ADVICE_START_DATE_DMY';
4695 varval(i) := 'START_DATE='||p_date_paid;
4696 i := i+1;
4697 varname(i) := 'ADVICE_END_DATE_DMY';
4698 varval(i) := 'END_DATE='||p_date_paid;
4699 i := i+1;
4700 varname(i) := 'ADVICE_ASG_SET_DMY';
4701 varval(i) := 'ASG_SET_ID='||p_payroll_assignment_set;
4702 i := i+1;
4703
4704 -- Payment Report
4705
4706 varname(i) := 'PAYMENT_PAYMENT_TYPE_ID';
4707 varval(i) := p_payment_rep_payment_type;
4708 i := i+1;
4709 varname(i) := 'PAYMENT_PAYMENT_METHOD_ID';
4710 varval(i) := p_payment_rep_payment_method;
4711 i := i+1;
4712
4713 -- ROE
4714
4715 varname(i) := 'IS_ROE';
4716 varval(i) := lv_is_roe;
4717 i := i+1;
4718 varname(i) := 'ROE_ASG_SET';
4719 varval(i) := p_roe_assignment_set;
4720 i := i+1;
4721 varname(i) := 'ROE_ASG_SET_NAME';
4722 varval(i) := lv_roe_asg_set;
4723 i := i+1;
4724
4725 -- ROE Worksheet
4726
4727 varname(i) := 'ROE_WORKSHEET_PERSON';
4728 varval(i) := p_roe_worksheet_person;
4729 i := i+1;
4730 varname(i) := 'ROE_WORKSHEET_PERSON_NAME';
4731 varval(i) := lv_roe_worksheet_person;
4732 i := i+1;
4733
4734 -- ROE Magnetic Media
4735
4736 varname(i) := 'IS_ROE_MAG';
4737 varval(i) := lv_is_roe_mag;
4738 i := i+1;
4739 varname(i) := 'ROE_MAG_PERSON';
4740 varval(i) := p_roe_mag_media_person;
4741 i := i+1;
4742 varname(i) := 'ROE_MAG_PERSON_NAME';
4743 varval(i) := lv_roe_mag_media_person;
4744 i := i+1;
4745 varname(i) := 'ROE_TYPE';
4746 varval(i) := p_roe_mag_roe_type;
4747 i := i+1;
4748 varname(i) := 'ROE_TYPE_HIDDEN';
4749 varval(i) := 'ROE_TYPE='||p_roe_mag_roe_type;
4750 i := i+1;
4751
4752 -- Costing
4753
4754 -- Costing Summary
4755
4756 varname(i) := 'IS_COSTING_SUMMARY';
4757 varval(i) := lv_is_costing_summary;
4758 i := i+1;
4759 varname(i) := 'SUMMARY_COSTING_ACCRUALS';
4760 varval(i) := p_cost_summary_accruals;
4761 i := i+1;
4762 varname(i) := 'SUMMARY_COSTING_ACCRUALS_NAME';
4763 varval(i) := lv_cost_summary_accruals;
4764 i := i+1;
4765 varname(i) := 'SUMMARY_COSTING_FILE_FORMAT';
4766 varval(i) := p_cost_summary_file_out;
4767 i := i+1;
4768 varname(i) := 'SUMMARY_COSTING_FILE_FMT_NAME';
4769 varval(i) := lv_cost_summary_file_out;
4770 i := i+1;
4771
4772 -- Costing Detail
4773
4774 varname(i) := 'DETAIL_COSTING_CRITERION';
4775 varval(i) := p_cost_detail_selection;
4776 i := i+1;
4777 varname(i) := 'DETAIL_COSTING_IS_ELEMENT_SET';
4778 varval(i) := p_cost_detail_is_ele_set;
4779 i := i+1;
4780 varname(i) := 'DETAIL_COSTING_ELEMENT_SET';
4781 varval(i) := p_cost_detail_ele_set;
4782 i := i+1;
4783 varname(i) := 'DETAIL_COSTING_ELE_SET_NAME';
4784 varval(i) := lv_cost_detail_ele_set;
4785 i := i+1;
4786 varname(i) := 'DETAIL_COSTING_IS_ELEMENT_C';
4787 varval(i) := p_cost_detail_is_class;
4788 i := i+1;
4789 varname(i) := 'DETAIL_COSTING_ELEMENT_C';
4790 varval(i) := p_cost_detail_class;
4791 i := i+1;
4792 varname(i) := 'DETAIL_COSTING_ELEMENT_C_NAME';
4793 varval(i) := lv_cost_detail_ele_class;
4794 i := i+1;
4795 varname(i) := 'DETAIL_COSTING_IS_ELEMENT';
4796 varval(i) := p_cost_detail_is_element;
4797 i := i+1;
4798 varname(i) := 'DETAIL_COSTING_ELEMENT';
4799 varval(i) := p_cost_detail_element;
4800 i := i+1;
4801 varname(i) := 'DETAIL_COSTING_ELEMENT_NAME';
4802 varval(i) := lv_cost_detail_ele_name;
4803 i := i+1;
4804 varname(i) := 'DETAIL_COSTING_ASG_SET';
4805 varval(i) := p_cost_detail_asg_set;
4806 i := i+1;
4807 varname(i) := 'DETAIL_COSTING_ASG_SET_NAME';
4808 varval(i) := lv_cost_detail_asg_set;
4809 i := i+1;
4810 varname(i) := 'DETAIL_COSTING_ACCRUALS';
4811 varval(i) := p_cost_detail_accruals;
4812 i := i+1;
4813 varname(i) := 'DETAIL_COSTING_ACCRUALS_NAME';
4814 varval(i) := lv_cost_detail_accruals;
4815 i := i+1;
4816 --following code added for bug 12548555 p_action_parameter_group, --bug 12548555
4817 varname(i) := 'PAY_ACTION_PARAMETER_GROUP_ID';
4818 varval(i) := p_action_parameter_group;
4819 i := i+1;
4820
4821 -- Notification Attributes
4822
4823 varname(i) := 'PROCESS_LIST_SUBJECT';
4824 varval(i) := lv_process_list_subject;
4825 i := i+1;
4826 varname(i) := 'PROCESS_LIST_TEXT';
4827 varval(i) := lv_process_list_text;
4828 i := i+1;
4829 varname(i) := 'PROCESS_LIST_HTML1';
4830 varval(i) := lv_process_list_html_1;
4831 i := i+1;
4832 varname(i) := 'PROCESS_LIST_HTML2';
4833 varval(i) := lv_process_list_html_2;
4834 i := i+1;
4835
4836 -- print out set values for attributes
4837
4838 for j in varname.first..varname.last loop
4839 hr_utility.trace( 'varname '|| j ||' = '||varname(j));
4840 hr_utility.trace( 'varval '|| j ||' = '||varval(j));
4841 end loop;
4842
4843
4844 wf_engine.SetItemAttrTextArray(ItemType, ItemKey, varname, varval);
4845 hr_utility.trace( 'Total Count = '||to_char(varname.COUNT));
4846
4847 for k in num_varname.first..num_varname.last loop
4848 hr_utility.trace( 'num_varname '|| k ||' = '||num_varname(k));
4849 hr_utility.trace( 'num_varval '|| k ||' = '||num_varvalue(k));
4850 end loop;
4851
4852
4853 wf_engine.SetItemAttrNumberArray(ItemType, ItemKey, num_varname, num_varvalue);
4854 hr_utility.trace( 'Total Num Count = '||to_char(num_varname.COUNT));
4855
4856 exception
4857 when OTHERS then
4858 hr_utility.trace('varname exception: OTHERS of TextArray');
4859 raise;
4860 end;
4861
4862 hr_utility.trace('Before StartProcess');
4863
4864 wf_engine.StartProcess (ItemType => ItemType,
4865 ItemKey => ItemKey );
4866
4867 hr_utility.trace('After StartProcess');
4868
4869 hr_utility.set_location('Leaving: ' || l_proc, 100);
4870
4871
4872 exception
4873 when others then
4874 wf_core.Context('pay_ca_wf_pkg',
4875 'StartProcess',
4876 ItemType,
4877 RequestorUsername,
4878 ProcessOwner,
4879 Workflowprocess);
4880
4881 error;
4882 RAISE;
4883
4884 end StartProcess;
4885
4886 function get_notifier(p_payroll_id in number,
4887 p_gre_id in number,
4888 p_effective_date in varchar2) return varchar2 is
4889
4890 l_proc varchar2(80) := gv_package||'.get_notifier';
4891 lv_contact_source varchar2(50);
4892 lv_contact_user_name varchar2(150);
4893
4894 -- get the payroll contact
4895
4896 cursor c_payroll_contact is
4897 select prl_information7
4898 from pay_payrolls_f
4899 where payroll_id = p_payroll_id
4900 and prl_information_category = 'CA'
4901 and fnd_date.canonical_to_date(p_effective_date) between
4902 effective_start_date and effective_end_date;
4903
4904 -- get the GRE contact
4905
4906 cursor c_gre_contact is
4907 select org_information1
4908 from hr_organization_information
4909 where org_information_context || '' = 'Contact Information'
4910 and organization_id = p_gre_id;
4911
4912 begin
4913
4914 hr_utility.set_location('Starting: ' || l_proc, 100);
4915
4916 lv_contact_source := fnd_profile.value('HR_PAYROLL_CONTACT_SOURCE');
4917
4918 hr_utility.trace('Profile Value : '|| lv_contact_source);
4919 hr_utility.trace('Payroll Id : '|| p_payroll_id);
4920 hr_utility.trace('GRE Id : '|| p_gre_id);
4921 hr_utility.trace('Effective Date : '|| p_effective_date);
4922
4923
4924 if lv_contact_source = 'PAYROLL' then
4925
4926 open c_payroll_contact;
4927 fetch c_payroll_contact into lv_contact_user_name;
4928
4929 hr_utility.trace('Contact User : '|| lv_contact_user_name);
4930
4931 if c_payroll_contact%NOTFOUND then
4932 lv_contact_user_name := 'SYSADMIN';
4933 end if;
4934
4935 close c_payroll_contact;
4936
4937
4938 elsif lv_contact_source = 'GRE' then
4939
4940 open c_gre_contact;
4941 fetch c_gre_contact into lv_contact_user_name;
4942
4943 if c_gre_contact%NOTFOUND then
4944 lv_contact_user_name := 'SYSADMIN';
4945 end if;
4946
4947 close c_gre_contact;
4948
4949 elsif lv_contact_source = 'CUSTOM' then
4950 lv_contact_user_name := 'SYSADMIN';
4951
4952 else
4953 -- some other source we don't understand yet
4954 lv_contact_user_name := 'SYSADMIN';
4955 end if;
4956
4957 return lv_contact_user_name;
4958
4959 hr_utility.set_location('Leaving: ' || l_proc, 100);
4960
4961 end get_notifier;
4962
4963
4964 procedure error is
4965 begin
4966
4967 hr_utility.trace('Run time error in test script');
4968 hr_utility.trace('Sqlerror: '||sqlerrm);
4969 hr_utility.trace('Errname: '||wf_core.error_name);
4970 hr_utility.trace('Errmsg: '||substr(wf_core.error_message, 1, 200));
4971 hr_utility.trace('Stack1: '||substr(wf_core.error_stack, 1, 200));
4972 hr_utility.trace('Stack2: '||substr(wf_core.error_stack, 200, 200));
4973 hr_utility.trace('Stack3: '||substr(wf_core.error_stack, 400, 200));
4974 hr_utility.trace('Stack4: '||substr(wf_core.error_stack, 600, 200));
4975 hr_utility.trace('Stack5: '||substr(wf_core.error_stack, 800, 200));
4976 hr_utility.trace('Stack6: '||substr(wf_core.error_stack, 1000, 200));
4977 hr_utility.trace('Stack7: '||substr(wf_core.error_stack, 1200, 200));
4978 hr_utility.trace('Stack8: '||substr(wf_core.error_stack, 1400, 200));
4979 hr_utility.trace('Stack9: '||substr(wf_core.error_stack, 1600, 200));
4980 hr_utility.trace('Stack10: '||substr(wf_core.error_stack, 1800, 200));
4981
4982 end error;
4983
4984
4985 end pay_ca_wf_pkg;