[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_WF_PKG
Source
1 PACKAGE BODY pay_ca_wf_pkg AS
2 /* $Header: paycawfpkg.pkb 120.0 2005/05/29 10:48 appldev noship $*/
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 *******************************************************************/
40
41 /******************************************************************
42 ** private package global declarations
43 ******************************************************************/
44
45
46 procedure payroll_wf_process(errbuf OUT nocopy varchar2,
47 retcode OUT nocopy number,
48 p_wf_item_type varchar2,
49 p_business_group_id number ,
50 p_batch_id number ,
51 p_payroll_id varchar2,
52 p_consolidation_set_id varchar2,
53 p_date_earned varchar2,
54 p_date_paid varchar2,
55 p_retro_event_group varchar2,
56 p_retro_assignment_set varchar2,
57 p_retropay_assignment_set varchar2,
58 p_retropay_element_set varchar2,
59 p_retro_start_date varchar2,
60 p_payroll_element_set varchar2,
61 p_payroll_assignment_set varchar2,
62 p_payroll_run_type varchar2,
63 p_gre_or_tax_group varchar2,
64 p_is_gre varchar2,
65 p_gre varchar2,
66 p_is_tax_group varchar2,
67 p_tax_group varchar2,
68 p_gross_to_net_period varchar2,
69 p_payroll_process varchar2,
70 p_session_date varchar2,
71 p_organization varchar2,
72 p_location varchar2,
73 p_quebec varchar2,
74 p_qhsf_account_number varchar2,
75 p_qhsf_override_table varchar2,
76 p_sdr_federal varchar2,
77 p_sdr_province varchar2,
78 p_sdr_reporting_dimension varchar2,
79 p_exp_legislation_code varchar2,
80 p_exp_report_or_group varchar2,
81 p_exp_is_group varchar2,
82 p_exp_group_name varchar2,
83 p_exp_is_report varchar2,
84 p_exp_report_name varchar2,
85 p_exp_variance_type varchar2,
86 p_exp_variance_value varchar2,
87 p_payment_method_override varchar2,
88 p_ele_selection_criterion varchar2,
89 p_ele_is_element_set varchar2,
90 p_ele_element_set varchar2,
91 p_ele_is_element_class varchar2,
92 p_ele_element_class varchar2,
93 p_ele_is_element_name varchar2,
94 p_ele_element_name varchar2,
95 p_ele_employee varchar2,
96 p_deduction_class varchar2,
97 p_deduction_name varchar2,
98 p_deduction_sort_one varchar2,
99 p_deduction_sort_two varchar2,
100 p_deduction_sort_three varchar2,
101 p_reg_suppress_zero varchar2,
102 p_reg_full_report_ver varchar2,
103 p_reg_reporting_dim varchar2,
104 p_reg_sort_one varchar2,
105 p_reg_sort_two varchar2,
106 p_reg_sort_three varchar2,
107 p_reg_employee_page_break varchar2,
108 p_reg_req_num varchar2,
109 p_dd_payment_type varchar2,
110 p_dd_payment_method varchar2,
111 p_dd_override_date varchar2,
112 p_dd_financial_inst varchar2,
113 p_dd_cpa_code varchar2,
114 p_dd_file_number varchar2,
115 p_cheque_payment_type varchar2,
116 p_cheque_payment_method varchar2,
117 p_cheque_sort_sequence varchar2,
118 p_cheque_style varchar2,
119 p_start_cheque_number varchar2,
120 p_end_cheque_number varchar2,
121 p_da_data_type varchar2,
122 p_payment_rep_payment_type varchar2,
123 p_payment_rep_payment_method varchar2,
124 p_roe_assignment_set varchar2,
125 p_roe_worksheet_person varchar2,
126 p_roe_mag_media_person varchar2,
127 p_roe_mag_roe_type varchar2,
128 p_cost_summary_accruals varchar2,
129 p_cost_summary_file_out varchar2,
130 p_cost_detail_selection varchar2,
131 p_cost_detail_is_ele_set varchar2,
132 p_cost_detail_ele_set varchar2,
133 p_cost_detail_is_class varchar2,
134 p_cost_detail_class varchar2,
135 p_cost_detail_is_element varchar2,
136 p_cost_detail_element varchar2,
137 p_cost_detail_asg_set varchar2,
138 p_cost_detail_accruals varchar2
139 ) is
140
141 l_workflowprocess varchar2(100);
142 l_ProcessDesc varchar2(100);
143 l_RequestorUsername varchar2(100) := 'SYSADMIN';
144 l_ProcessOwner varchar2(100);
145 l_item_type varchar2(100);
146 l_item_key varchar2(100);
147 lv_runnable_process varchar2(1);
148
149
150 begin
151 --hr_utility.trace_on(null,'PYWF');
152
153 gv_package := 'pay_ca_wf_pkg';
154
155 select to_char(sysdate,'DDHH24MISS') into l_item_key from dual;
156
157 -- initialise variable - 0 is SRS Success, 1 is SRS Warning, 2 is SRS Error
158 retcode := 0;
159
160
161 l_workflowprocess := 'PAYCAPROCESSWF';
162 l_item_type := p_wf_item_type; -- PAYCAPWF
163
164 begin
165 select runnable_flag
166 into lv_runnable_process
167 from wf_activities
168 where item_type = p_wf_item_type
169 and type = 'PROCESS'
170 and name = l_workflowprocess
171 and end_date is null;
172
173 exception when no_data_found then
174 hr_utility.trace('Exception: No Data Found in payroll_wf_process');
175 null;
176
177 end;
178
179 hr_utility.trace('Item Type : '|| l_item_type);
180 hr_utility.trace('Item Key : '|| l_item_key);
181 hr_utility.trace('Workflow Process : '|| l_workflowprocess);
182
183 if lv_runnable_process = 'Y' then
184
185 StartProcess(p_business_group_id,
186 p_batch_id,
187 p_payroll_id,
188 p_consolidation_set_id,
189 p_date_earned,
190 p_date_paid,
191 p_retro_event_group,
192 p_retro_assignment_set,
193 p_retropay_assignment_set,
194 p_retropay_element_set,
195 p_retro_start_date,
196 p_payroll_element_set,
197 p_payroll_assignment_set,
198 p_payroll_run_type,
199 p_gre_or_tax_group,
200 p_is_gre,
201 p_gre,
202 p_is_tax_group,
203 p_tax_group,
204 p_gross_to_net_period,
205 p_payroll_process,
206 p_session_date,
207 p_organization,
208 p_location,
209 p_quebec,
210 p_qhsf_account_number,
211 p_qhsf_override_table,
212 p_sdr_federal,
213 p_sdr_province,
214 p_sdr_reporting_dimension,
215 p_exp_legislation_code,
216 p_exp_report_or_group,
217 p_exp_is_group,
218 p_exp_group_name,
219 p_exp_is_report,
220 p_exp_report_name,
221 p_exp_variance_type,
222 p_exp_variance_value,
223 p_payment_method_override,
224 p_ele_selection_criterion,
225 p_ele_is_element_set,
226 p_ele_element_set,
227 p_ele_is_element_class,
228 p_ele_element_class,
229 p_ele_is_element_name,
230 p_ele_element_name,
231 p_ele_employee,
232 p_deduction_class,
233 p_deduction_name,
234 p_deduction_sort_one,
235 p_deduction_sort_two,
236 p_deduction_sort_three,
237 p_reg_suppress_zero,
238 p_reg_full_report_ver,
239 p_reg_reporting_dim,
240 p_reg_sort_one,
244 p_reg_req_num,
241 p_reg_sort_two,
242 p_reg_sort_three,
243 p_reg_employee_page_break,
245 p_dd_payment_type,
246 p_dd_payment_method,
247 p_dd_override_date,
248 p_dd_financial_inst,
249 p_dd_cpa_code,
250 p_dd_file_number,
251 p_cheque_payment_type,
252 p_cheque_payment_method,
253 p_cheque_sort_sequence,
254 p_cheque_style,
255 p_start_cheque_number,
256 p_end_cheque_number,
257 p_da_data_type,
258 p_payment_rep_payment_type,
259 p_payment_rep_payment_method,
260 p_roe_assignment_set,
261 p_roe_worksheet_person,
262 p_roe_mag_media_person,
263 p_roe_mag_roe_type,
264 p_cost_summary_accruals,
265 p_cost_summary_file_out,
266 p_cost_detail_selection,
267 p_cost_detail_is_ele_set,
268 p_cost_detail_ele_set,
269 p_cost_detail_is_class,
270 p_cost_detail_class,
271 p_cost_detail_is_element,
272 p_cost_detail_element,
273 p_cost_detail_asg_set,
274 p_cost_detail_accruals,
275 l_ProcessDesc,
276 l_RequestorUsername,
277 l_ProcessOwner,
278 l_workflowprocess,
279 l_item_type,
280 l_item_key
281 );
282
283 end if;
284
285 exception
286
287 when hr_utility.hr_error then
288 --
289 -- Set up error message and error return code.
290 --
291 hr_utility.trace('in the exception');
292
293 errbuf := hr_utility.get_message;
294 retcode := 2;
295
296 when others then
297
298 -- Set up error message and return code.
299 errbuf := sqlerrm;
300 retcode := 2;
301
302 end payroll_wf_process;
303
304 -- Start Workflow Process will Create a Process and Set the Attributes
305 -- for the Workflow Process.
306
307
308 procedure StartProcess (
309 p_business_group_id number,
310 p_batch_id number,
311 p_payroll_id varchar2,
312 p_consolidation_set_id varchar2,
313 p_date_earned varchar2,
314 p_date_paid varchar2,
315 p_retro_event_group varchar2,
316 p_retro_assignment_set varchar2,
317 p_retropay_assignment_set varchar2,
318 p_retropay_element_set varchar2,
319 p_retro_start_date varchar2,
320 p_payroll_element_set varchar2,
321 p_payroll_assignment_set varchar2,
322 p_payroll_run_type varchar2,
323 p_gre_or_tax_group varchar2,
324 p_is_gre varchar2,
325 p_gre varchar2,
326 p_is_tax_group varchar2,
327 p_tax_group varchar2,
328 p_gross_to_net_period varchar2,
329 p_payroll_process varchar2,
330 p_session_date varchar2,
331 p_organization varchar2,
332 p_location varchar2,
333 p_quebec varchar2,
334 p_qhsf_account_number varchar2,
335 p_qhsf_override_table varchar2,
336 p_sdr_federal varchar2,
337 p_sdr_province varchar2,
338 p_sdr_reporting_dimension varchar2,
339 p_exp_legislation_code varchar2,
340 p_exp_report_or_group varchar2,
341 p_exp_is_group varchar2,
342 p_exp_group_name varchar2,
343 p_exp_is_report varchar2,
344 p_exp_report_name varchar2,
345 p_exp_variance_type varchar2,
346 p_exp_variance_value varchar2,
347 p_payment_method_override varchar2,
348 p_ele_selection_criterion varchar2,
349 p_ele_is_element_set varchar2,
350 p_ele_element_set varchar2,
351 p_ele_is_element_class varchar2,
352 p_ele_element_class varchar2,
353 p_ele_is_element_name varchar2,
354 p_ele_element_name varchar2,
355 p_ele_employee varchar2,
356 p_deduction_class varchar2,
357 p_deduction_name varchar2,
358 p_deduction_sort_one varchar2,
359 p_deduction_sort_two varchar2,
360 p_deduction_sort_three varchar2,
361 p_reg_suppress_zero varchar2,
362 p_reg_full_report_ver varchar2,
363 p_reg_reporting_dim varchar2,
367 p_reg_employee_page_break varchar2,
364 p_reg_sort_one varchar2,
365 p_reg_sort_two varchar2,
366 p_reg_sort_three varchar2,
368 p_reg_req_num varchar2,
369 p_dd_payment_type varchar2,
370 p_dd_payment_method varchar2,
371 p_dd_override_date varchar2,
372 p_dd_financial_inst varchar2,
373 p_dd_cpa_code varchar2,
374 p_dd_file_number varchar2,
375 p_cheque_payment_type varchar2,
376 p_cheque_payment_method varchar2,
377 p_cheque_sort_sequence varchar2,
378 p_cheque_style varchar2,
379 p_start_cheque_number varchar2,
380 p_end_cheque_number varchar2,
381 p_da_data_type varchar2,
382 p_payment_rep_payment_type varchar2,
383 p_payment_rep_payment_method varchar2,
384 p_roe_assignment_set varchar2,
385 p_roe_worksheet_person varchar2,
386 p_roe_mag_media_person varchar2,
387 p_roe_mag_roe_type varchar2,
388 p_cost_summary_accruals varchar2,
389 p_cost_summary_file_out varchar2,
390 p_cost_detail_selection varchar2,
391 p_cost_detail_is_ele_set varchar2,
392 p_cost_detail_ele_set varchar2,
393 p_cost_detail_is_class varchar2,
394 p_cost_detail_class varchar2,
395 p_cost_detail_is_element varchar2,
396 p_cost_detail_element varchar2,
397 p_cost_detail_asg_set varchar2,
398 p_cost_detail_accruals varchar2,
399 ProcessDesc in varchar2,
400 RequestorUsername in varchar2,
401 ProcessOwner in varchar2,
402 Workflowprocess in varchar2 default null,
403 item_type in varchar2 default null,
404 item_key in varchar2
405 ) is
406
407 ItemType varchar2(30) := item_type;
408 ItemKey varchar2(30) := item_key;
409 ItemUserKey varchar2(80) := ProcessDesc;
410 l_business_group_id number(30) := p_business_group_id;
411 l_payroll_id number(16) := to_number(p_payroll_id);
412 l_consolidation_set_id number(16) := to_number(p_consolidation_set_id);
413
414
415 lv_prc_list varchar2(3200);
416 lv_process_list_subject varchar2(240);
417 lv_process_list_text varchar2(3200);
418 lv_process_list_html_1 varchar2(3200);
419 lv_process_list_html_2 varchar2(3200);
420
421 lv_contact_user_name varchar2(80);
422 lv_orig_system varchar2(40);
423 lv_orig_system_id varchar2(40);
424 lv_role_name varchar2(50);
425 lv_role_display_name varchar2(50);
426
427 lv_payroll_name varchar2(80);
428 lv_consolidation_set_name varchar2(80);
429 lv_gre_name varchar2(80);
430 lv_tax_group varchar2(80);
431 lv_organization varchar2(80);
432 lv_location varchar2(80);
433 lv_date_time varchar2(80);
434
435 lv_is_batch varchar2(1) := 'N';
436 lv_is_retropay varchar2(1) := 'N';
437 lv_is_retro_notification varchar2(1) := 'N';
438 lv_is_gtn varchar2(1);
439 lv_is_sdr varchar2(1);
440 lv_is_payroll_register varchar2(1);
441 lv_is_element_register varchar2(1);
442 lv_is_deductions varchar2(1);
443 lv_is_direct_deposit varchar2(1);
444 lv_is_chequewriter varchar2(1);
445 lv_is_deposit_advice varchar2(1);
446 lv_is_roe varchar2(1);
447 lv_is_roe_mag varchar2(1);
448 lv_is_costing_summary varchar2(1);
449 lv_is_consolidation_set varchar2(1);
450 lv_is_exception varchar2(1);
451
452
453 lv_workflow_mode varchar2(1);
454 lv_batch_name varchar2(80);
455 lv_retro_period varchar2(80);
456 lv_event_group_name varchar2(80);
457 lv_retropay_asg_set varchar2(80);
458 lv_retropay_element_set varchar2(80);
459 lv_payroll_element_set varchar2(80);
460 lv_payroll_assignment_set varchar2(80);
461 lv_payroll_run_type varchar2(80);
462 lv_gtn_period varchar2(80);
463 lv_payroll_process varchar2(80);
464 lv_account_number varchar2(80);
465 lv_federal varchar2(80);
466 lv_province varchar2(80);
467 lv_reporting_dimension varchar2(80);
468 lv_exp_group_name varchar2(80);
469 lv_exp_report_name varchar2(80);
470 lv_exp_vartype_name varchar2(80);
471 lv_exp_finder_pqp varchar2(80);
472 lv_prepay_payment_method varchar2(80);
473 lv_ele_element_set varchar2(80);
474 lv_ele_element_class varchar2(80);
475 lv_ele_element_name varchar2(80);
476 lv_ele_employee varchar2(80);
477 lv_ded_consolidation_dummy varchar2(80);
478 lv_deduction_class varchar2(80);
479 lv_deduction_name varchar2(80);
480 lv_reg_suppress_zero varchar2(80);
481 lv_reg_full_report_ver varchar2(80);
485 lv_reg_sort_three varchar2(80);
482 lv_reg_reporting_dimension varchar2(80);
483 lv_reg_sort_one varchar2(80);
484 lv_reg_sort_two varchar2(80);
486 lv_reg_employee_page_break varchar2(80);
487 lv_cheque_payment_type varchar2(80);
488 lv_cheque_payment_method varchar2(80);
489 lv_cheque_sort_sequence varchar2(80);
490 lv_cheque_style varchar2(80);
491 lv_da_data_type varchar2(80);
492 lv_roe_asg_set varchar2(80);
493 lv_roe_worksheet_person varchar2(80);
494 lv_roe_mag_media_person varchar2(80);
495 lv_cost_summary_accruals varchar2(80);
496 lv_cost_summary_file_out varchar2(80);
497 lv_cost_detail_ele_set varchar2(80);
498 lv_cost_detail_ele_class varchar2(80);
499 lv_cost_detail_ele_name varchar2(80);
500 lv_cost_detail_asg_set varchar2(80);
501 lv_cost_detail_accruals varchar2(80);
502
503 l_user_id number;
504 l_resp_id number;
505 l_resp_appl_id number;
506 l_org_id number;
507 l_per_sec_id number;
508 l_sec_grp_id number;
509
510 varname wf_engine.NameTabTyp;
511 varval wf_engine.TextTabTyp;
512 num_varname wf_engine.NameTabTyp;
513 num_varvalue wf_engine.NumTabTyp;
514
515 TYPE char80_table IS TABLE OF VARCHAR2(80)
516 INDEX BY BINARY_INTEGER;
517
518 lv_conc_prog_name char80_table;
519 i number;
520 j number;
521 k number;
522
523 l_proc varchar2(80) := gv_package||'.StartProcess';
524
525 cursor cur_workflow_mode is
526 select nvl(substr(parameter_value,1,1), 'W')
527 from pay_action_parameters
528 where parameter_name = 'PAYROLL_CA_WF_NOTIFY_ACTION';
529
530 cursor cur_retro_period is
531 select period_name
532 from per_time_periods
533 where payroll_id = l_payroll_id
534 and end_date = fnd_date.canonical_to_date(p_date_earned);
535
536 cursor cur_event_group is
537 select event_group_name
538 from pay_event_groups
539 where business_group_id = p_business_group_id
540 and event_group_id =
541 to_number(substr(p_retro_event_group,(length('EVT_GRP_ID=')+1)));
542
543 cursor cur_retropay_asg_set is
544 select assignment_set_name
545 from hr_assignment_sets
546 where assignment_set_id = to_number(p_retropay_assignment_set)
547 and business_group_id = p_business_group_id;
548
549 cursor cur_retropay_element_set is
550 select element_set_name
551 from pay_element_sets
552 where element_set_id = to_number(p_retropay_element_set)
553 and business_group_id = p_business_group_id;
554
555 cursor cur_payroll_element_set is
556 select element_set_name
557 from pay_element_sets
558 where element_set_id = to_number(p_payroll_element_set)
559 and business_group_id = p_business_group_id;
560
561 cursor cur_payroll_asg_set is
562 select assignment_set_name
563 from hr_assignment_sets
564 where assignment_set_id = to_number(p_payroll_assignment_set)
565 and payroll_id = l_payroll_id;
566
567 cursor cur_payroll_run_type is
568 select t.run_type_name
569 from pay_run_types_f_tl t,
570 pay_run_types_f r
571 where r.run_type_id = to_number(p_payroll_run_type)
572 and r.run_type_id = t.run_type_id
573 and t.language = userenv('LANG')
574 and fnd_date.canonical_to_date(p_date_earned)
575 between r.effective_start_date and r.effective_end_date;
576
577 cursor cur_sdr_reporting_dimension is
578 select meaning
579 from hr_lookups
580 where lookup_code = p_sdr_reporting_dimension
581 and lookup_type = 'PAY_CA_REPORT_DIMENSION'
582 and application_id = 800;
583
584 cursor cur_exp_group_name is
585 select exception_group_name
586 from pqp_exception_report_groups
587 where exception_group_id = to_number(p_exp_group_name);
588
589 cursor cur_exp_report_name is
590 select exception_report_name
591 from pqp_exception_reports
592 where exception_report_id = to_number(p_exp_report_name);
593
594 cursor cur_exp_variance_type is
595 select meaning
596 from fnd_common_lookups
597 where lookup_type = 'PQP_VARIANCE_TYPE'
598 and lookup_code = p_exp_variance_type;
599
600 cursor cur_prepay_payment_method is
601 select org_payment_method_name
602 from pay_org_payment_methods_f_tl
603 where org_payment_method_id = to_number(p_payment_method_override)
604 and language = userenv('LANG');
605
606 cursor cur_ele_element_set is
607 select element_set_name
608 from pay_element_sets
609 where element_set_id = to_number(p_ele_element_set)
610 and element_set_type = 'C';
611
612 cursor cur_ele_element_class is
613 select classification_name
614 from pay_element_classifications
615 where classification_id = to_number(p_ele_element_class);
616
617 cursor cur_ele_element_name is
618 select element_name
619 from pay_element_types_f_tl
620 where element_type_id = to_number(p_ele_element_name)
621 and language = userenv('LANG');
622
623 cursor cur_ele_employee is
624 select full_name||'(Person ID='||person_id||')'
625 from per_people_f
626 where person_id = to_number(p_ele_employee)
627 and fnd_date.canonical_to_date(p_date_earned)
628 between effective_start_date and effective_end_date;
629
630 cursor cur_ded_class is
631 select classification_name
632 from pay_element_classifications
633 where classification_id = to_number(p_deduction_class);
634
635 cursor cur_ded_name is
636 select element_name
637 from pay_element_types_f
641
638 where element_type_id = to_number(p_deduction_name)
639 and fnd_date.canonical_to_date(p_date_earned)
640 between effective_start_date and effective_end_date;
642 cursor cur_reg_dimension is
643 select flv.meaning
644 from fnd_lookup_values flv, fnd_lookup_types flt
645 where flv.lookup_code = p_reg_reporting_dim
646 and flt.lookup_type = 'CA_DIMENSION'
647 and flt.application_id = 800
648 and flt.lookup_type = flv.lookup_type;
649
650 cursor cur_cheque_payment_type is
651 select payment_type_name
652 from pay_payment_types
653 where payment_type_id = to_number(p_cheque_payment_type)
654 and territory_code = 'CA'
655 and category = 'CH';
656
657 cursor cur_cheque_payment_method is
658 select org_payment_method_name
659 from pay_org_payment_methods_f_tl
660 where org_payment_method_id = to_number(p_cheque_payment_method);
661
662 cursor cur_cheque_sort_seq is
663 select meaning
664 from hr_lookups
665 where lookup_code = p_cheque_sort_sequence
666 and lookup_type = 'CHEQUE_PROCEDURE'
667 and enabled_flag = 'Y';
668
669 cursor cur_cheque_style is
670 select meaning
671 from hr_lookups
672 where lookup_code = p_cheque_style
673 and lookup_type = 'CA_CHEQUE_DEPADV'
674 and enabled_flag = 'Y';
675
676 cursor cur_deposit_advice_data_type is
677 select meaning
678 from hr_lookups
679 where lookup_code = p_da_data_type
680 and lookup_type = 'DAR_REPORT'
681 and enabled_flag = 'Y';
682
683 cursor cur_roe_asg_set is
684 select assignment_set_name
685 from hr_assignment_sets
686 where assignment_set_id =
687 to_number(substr(p_roe_assignment_set,(length('ASSIGNMENT_SET_ID=')+1)));
688
689 cursor cur_roe_worksheet_person is
690 select full_name
691 from per_all_people_f
692 where person_id = to_number(p_roe_worksheet_person)
693 and fnd_date.canonical_to_date(p_date_earned)
694 between effective_start_date and effective_end_date;
695
696 cursor cur_roe_mag_media_person is
697 select full_name
698 from per_all_people_f
699 where person_id =
700 to_number(substr(p_roe_mag_media_person,(length('PERSON_ID=')+1)))
701 and fnd_date.canonical_to_date(p_date_earned)
702 between effective_start_date and effective_end_date;
703
704 cursor cur_costing_summary_accruals is
705 select meaning
706 from hr_lookups
707 where lookup_code = p_cost_summary_accruals
708 and lookup_type = 'PAY_PAYRPCBR';
709
710 cursor cur_costing_summary_file_out is
711 select meaning
712 from fnd_common_lookups
713 where lookup_code = p_cost_summary_file_out
714 and lookup_type = 'REPORT_OUTPUT_TYPE'
715 and application_id = 801
716 and enabled_flag = 'Y';
717
718 cursor cur_costing_detail_ele_set is
719 select element_set_name
720 from pay_element_sets
721 where element_set_id = to_number(p_cost_detail_ele_set)
722 and element_set_type = 'C';
723
724 cursor cur_costing_detail_ele_class is
725 select classification_name
726 from pay_element_classifications
727 where classification_id = to_number(p_cost_detail_class);
728
729 cursor cur_costing_detail_ele_name is
730 select tl.element_name
731 from pay_element_types_f el, pay_element_types_f_tl tl
732 where el.element_type_id = to_number(p_cost_detail_element)
733 and el.element_type_id = tl.element_type_id
734 and tl.language = userenv('LANG')
735 and fnd_date.canonical_to_date(p_date_earned)
736 between el.effective_start_date and el.effective_end_date;
737
738 cursor cur_costing_detail_asg_set is
739 select assignment_set_name
740 from hr_assignment_sets
741 where assignment_set_id = to_number(p_cost_detail_asg_set);
742
743 cursor cur_costing_detail_accruals is
744 select meaning
745 from hr_lookups
746 where lookup_code = p_cost_detail_accruals
750 begin
747 and lookup_type = 'PAY_PAYRPCBR';
748
749
751
752 hr_utility.set_location('Starting: ' || l_proc, 100);
753
754 lv_conc_prog_name(1) := 'BEE Batch Process (Transfer)';
755 lv_conc_prog_name(2) := 'Retro-Notifications Report';
756 lv_conc_prog_name(3) := 'RetroPay By Element';
757 lv_conc_prog_name(4) := 'Process Payroll Run';
758 lv_conc_prog_name(5) := 'Gross to Net Summary';
759 lv_conc_prog_name(6) := 'Payroll Message Report';
760 lv_conc_prog_name(7) := 'Employee Assignments Not Paid';
761 lv_conc_prog_name(8) := 'Payroll Exception Report';
762 lv_conc_prog_name(9) := 'Statutory Deductions Report';
763 lv_conc_prog_name(10) := 'Quebec Health Services Fund';
764 lv_conc_prog_name(11) := 'Pre Payments';
765 lv_conc_prog_name(12) := 'Payroll Register Report';
766 lv_conc_prog_name(13) := 'Element Register Report';
767 lv_conc_prog_name(14) := 'Deductions Report';
768 lv_conc_prog_name(15) := 'Direct Deposit';
769 lv_conc_prog_name(16) := 'Canadian Payroll Archiver';
770 lv_conc_prog_name(17) := 'Canadian Chequewriter';
771 lv_conc_prog_name(18) := 'Canadian Deposit Advice';
772 lv_conc_prog_name(19) := 'Payment Report';
773 lv_conc_prog_name(20) := 'Record of Employment by Assignment Set';
774 lv_conc_prog_name(21) := 'ROE Worksheet';
775 lv_conc_prog_name(22) := 'ROE-Magnetic Media';
776 lv_conc_prog_name(23) := 'Costing';
777 lv_conc_prog_name(24) := 'Costing Detail Report';
778 lv_conc_prog_name(25) := 'Costing Summary Report';
779
780 hr_utility.trace('In StartProcess Item Type is : '|| ItemType);
781 hr_utility.trace('In StartProcess Item Key is : '|| ItemKey);
782 hr_utility.trace('Workflow process name is : '|| Workflowprocess);
783
784 select to_char(sysdate,'DD-MON-YYYY') ||' '|| to_char(sysdate,'HH24:MI:SS')
785 into lv_date_time from dual;
786
787
788 hr_utility.trace('before create_process');
789
790 wf_engine.createProcess(ItemType => ItemType,
791 ItemKey => ItemKey,
792 process => Workflowprocess);
793
794 wf_engine.SetItemUserKey(ItemType => ItemType,
795 ItemKey => ItemKey,
796 UserKey => ItemUserKey);
797
798 hr_utility.trace('In StartProcess before HR_SIGNON.Initialize_HR_Security');
799
800 HR_SIGNON.Initialize_HR_Security;
801
802 hr_utility.trace('After HR_SIGNON.Initialize_HR_Security');
803
804 l_user_id := FND_GLOBAL.USER_ID;
805 l_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
806 l_resp_id := FND_GLOBAL.RESP_ID;
807 l_org_id := FND_GLOBAL.ORG_ID;
808 l_per_sec_id := FND_GLOBAL.PER_SECURITY_PROFILE_ID;
809 l_sec_grp_id := FND_GLOBAL.SECURITY_GROUP_ID;
810
811 hr_utility.trace('l_user_id = ' || l_user_id);
812 hr_utility.trace('l_resp_appl_id = ' || l_resp_appl_id);
813 hr_utility.trace('l_resp_id = ' || l_resp_id);
814 hr_utility.trace('l_org_id = ' || l_org_id);
815 hr_utility.trace('l_per_sec_id = ' || l_per_sec_id);
816 hr_utility.trace('l_sec_grp_id = ' || l_sec_grp_id);
817 hr_utility.trace('business group = ' || to_char(p_business_group_id));
818
819 lv_gre_name := '';
820 lv_tax_group := '';
821
822 open cur_workflow_mode;
823 fetch cur_workflow_mode into lv_workflow_mode;
824
825 if cur_workflow_mode%notfound then
826 lv_workflow_mode := 'W';
827 end if;
828
829 close cur_workflow_mode;
830
831 hr_utility.trace('Workflow Mode = ' || lv_workflow_mode);
832
833 open cur_retro_period;
834 fetch cur_retro_period into lv_retro_period;
835 close cur_retro_period;
836
837 hr_utility.trace('Retro Period = ' || lv_retro_period);
838
839 open cur_event_group;
840 fetch cur_event_group into lv_event_group_name;
841 close cur_event_group;
842
843 hr_utility.trace('Event Group = ' || lv_event_group_name);
844
845 open cur_retropay_asg_set;
846 fetch cur_retropay_asg_set into lv_retropay_asg_set;
847 close cur_retropay_asg_set;
848
849 hr_utility.trace('RetroPay Assignment Set = ' || lv_retropay_asg_set);
850
851 open cur_retropay_element_set;
852 fetch cur_retropay_element_set into lv_retropay_element_set;
853 close cur_retropay_element_set;
854
855 hr_utility.trace('RetroPay Element Set = ' || lv_retropay_element_set);
856
857 open cur_payroll_element_set;
858 fetch cur_payroll_element_set into lv_payroll_element_set;
859 close cur_payroll_element_set;
863 open cur_payroll_asg_set;
860
861 hr_utility.trace('Payroll Run Element Set = ' || lv_payroll_element_set);
862
864 fetch cur_payroll_asg_set into lv_payroll_assignment_set;
865 close cur_payroll_asg_set;
866
867 hr_utility.trace('Payroll Run Assignment Set = ' || lv_payroll_assignment_set);
868
869 open cur_payroll_run_type;
870 fetch cur_payroll_run_type into lv_payroll_run_type;
871 close cur_payroll_run_type;
872
873 hr_utility.trace('Payroll Run Type = ' || lv_payroll_run_type);
874
875 open cur_sdr_reporting_dimension;
876 fetch cur_sdr_reporting_dimension into lv_reporting_dimension;
877 close cur_sdr_reporting_dimension;
878
879 hr_utility.trace('SDR Reporting Dimension = ' || lv_reporting_dimension);
880
881 open cur_exp_group_name;
882 fetch cur_exp_group_name into lv_exp_group_name;
883 close cur_exp_group_name;
884
885 hr_utility.trace('Exception Group Name = ' || lv_exp_group_name);
886
887 open cur_exp_report_name;
888 fetch cur_exp_report_name into lv_exp_report_name;
889 close cur_exp_report_name;
890
891 hr_utility.trace('Exception Report Name = ' || lv_exp_report_name);
892
893 open cur_exp_variance_type;
894 fetch cur_exp_variance_type into lv_exp_vartype_name;
895 close cur_exp_variance_type;
896
897 hr_utility.trace('Exception Variance Type = ' || lv_exp_vartype_name);
898
899 open cur_prepay_payment_method;
900 fetch cur_prepay_payment_method into lv_prepay_payment_method;
901 close cur_prepay_payment_method;
902
903 hr_utility.trace('PrePayment Payment Method = ' || lv_prepay_payment_method);
904
905 open cur_ele_element_set;
906 fetch cur_ele_element_set into lv_ele_element_set;
907 close cur_ele_element_set;
908
909 hr_utility.trace('Element Register Element Set = ' || lv_ele_element_set);
910
911 open cur_ele_element_class;
912 fetch cur_ele_element_class into lv_ele_element_class;
913 close cur_ele_element_class;
914
915 hr_utility.trace('Element Register Element Classification = ' || lv_ele_element_class);
916
917 open cur_ele_element_name;
918 fetch cur_ele_element_name into lv_ele_element_name;
919 close cur_ele_element_name;
920
921 hr_utility.trace('Element Register Element Name = ' || lv_ele_element_name);
922
923 open cur_ele_employee;
924 fetch cur_ele_employee into lv_ele_employee;
925 close cur_ele_employee;
926
927 hr_utility.trace('Element Register Employee = ' || lv_ele_employee);
928
929 open cur_ded_class;
930 fetch cur_ded_class into lv_deduction_class;
931 close cur_ded_class;
932
933 hr_utility.trace('Deduction Report Dedn Classification = ' || lv_deduction_class);
934
935 open cur_ded_name;
936 fetch cur_ded_name into lv_deduction_name;
937 close cur_ded_name;
938
939 hr_utility.trace('Deduction Report Dedn Name = ' || lv_deduction_name);
940
941 open cur_reg_dimension;
942 fetch cur_reg_dimension into lv_reg_reporting_dimension;
943 close cur_reg_dimension;
944
945 hr_utility.trace('Payroll Register Reporting Dimension = ' || lv_reg_reporting_dimension);
946
947 open cur_cheque_payment_type;
948 fetch cur_cheque_payment_type into lv_cheque_payment_type;
949 close cur_cheque_payment_type;
950
951 hr_utility.trace('Chequewriter Payment Type = ' || lv_cheque_payment_type);
952
953 open cur_cheque_payment_method;
954 fetch cur_cheque_payment_method into lv_cheque_payment_method;
955 close cur_cheque_payment_method;
956
957 hr_utility.trace('Chequewriter Payment Method = ' || lv_cheque_payment_method);
958
959 open cur_cheque_sort_seq;
960 fetch cur_cheque_sort_seq into lv_cheque_sort_sequence;
961 close cur_cheque_sort_seq;
962
963 hr_utility.trace('Chequewriter Sort Seq = ' || lv_cheque_sort_sequence);
964
965 open cur_cheque_style;
966 fetch cur_cheque_style into lv_cheque_style;
967 close cur_cheque_style;
968
969 hr_utility.trace('Chequewriter Style = ' || lv_cheque_style);
970
971 open cur_deposit_advice_data_type;
972 fetch cur_deposit_advice_data_type into lv_da_data_type;
973 close cur_deposit_advice_data_type;
974
975 hr_utility.trace('Deposit Advice Data Type = ' || lv_da_data_type);
976
977 open cur_roe_asg_set;
978 fetch cur_roe_asg_set into lv_roe_asg_set;
979 close cur_roe_asg_set;
980
981 hr_utility.trace('ROE Assignment Set = ' || lv_roe_asg_set);
982
983 open cur_roe_worksheet_person;
987 hr_utility.trace('ROE Worksheet Person = ' || lv_roe_worksheet_person);
984 fetch cur_roe_worksheet_person into lv_roe_worksheet_person;
985 close cur_roe_worksheet_person;
986
988
989 open cur_roe_mag_media_person;
990 fetch cur_roe_mag_media_person into lv_roe_mag_media_person;
991 close cur_roe_mag_media_person;
992
993 hr_utility.trace('ROE Magnetic Media Person = ' || lv_roe_mag_media_person);
994
995 open cur_costing_summary_accruals;
996 fetch cur_costing_summary_accruals into lv_cost_summary_accruals;
997 close cur_costing_summary_accruals;
998
999 hr_utility.trace('Costing Summary Accruals = ' || lv_cost_summary_accruals);
1000
1001 open cur_costing_summary_file_out;
1002 fetch cur_costing_summary_file_out into lv_cost_summary_file_out;
1003 close cur_costing_summary_file_out;
1004
1005 hr_utility.trace('Costing Summary File Format = ' || lv_cost_summary_file_out);
1006
1007 open cur_costing_detail_ele_set;
1008 fetch cur_costing_detail_ele_set into lv_cost_detail_ele_set;
1009 close cur_costing_detail_ele_set;
1010
1011 hr_utility.trace('Costing Detail Element Set = ' || lv_cost_detail_ele_set);
1012
1013 open cur_costing_detail_ele_name;
1014 fetch cur_costing_detail_ele_name into lv_cost_detail_ele_name;
1015 close cur_costing_detail_ele_name;
1016
1017 hr_utility.trace('Costing Detail Element Name = ' || lv_cost_detail_ele_name);
1018
1019 open cur_costing_detail_ele_class;
1020 fetch cur_costing_detail_ele_class into lv_cost_detail_ele_class;
1021 close cur_costing_detail_ele_class;
1022
1023 hr_utility.trace('Costing Detail Element Classification = ' || lv_cost_detail_ele_class);
1024
1025 open cur_costing_detail_asg_set;
1026 fetch cur_costing_detail_asg_set into lv_cost_detail_asg_set;
1027 close cur_costing_detail_asg_set;
1028
1029 hr_utility.trace('Costing Detail Assignment Set = ' || lv_cost_detail_asg_set);
1030
1031 open cur_costing_detail_accruals;
1032 fetch cur_costing_detail_accruals into lv_cost_detail_accruals;
1033 close cur_costing_detail_accruals;
1034
1035 hr_utility.trace('Costing Detail Accruals = ' || lv_cost_detail_accruals);
1036
1037 begin
1038 select period_name
1039 into lv_gtn_period
1040 from per_time_periods
1041 where payroll_id = to_number(p_payroll_id)
1042 and time_period_id = to_number(p_gross_to_net_period);
1043
1044 hr_utility.trace('lv_gtn_period = ' || lv_gtn_period);
1045
1046 exception when no_data_found then
1047 hr_utility.trace('lv_gtn_period exception: no_data_found ');
1048 null;
1049
1050 end;
1051
1052 begin
1053 select fnd_date.date_to_chardate(effective_date)||'('||to_char(request_id)||')'
1054 into lv_payroll_process
1055 from pay_payroll_actions
1056 where payroll_action_id = to_number(p_payroll_process);
1057
1058 hr_utility.trace('lv_payroll_process = ' || lv_payroll_process);
1059
1060 exception when no_data_found then
1061 hr_utility.trace('lv_payroll_process exception: no_data_found ');
1062 null;
1063
1064 end;
1065
1066 begin
1067 select to_char(sysdate, 'HHSSSS')
1068 into lv_exp_finder_pqp
1069 from dual;
1070
1071 hr_utility.trace('lv_exp_finder_pqp = ' || lv_exp_finder_pqp);
1072
1073 exception when no_data_found then
1074 hr_utility.trace('lv_exp_finder_pqp exception: no_data_found ');
1075 null;
1076
1077 end;
1078
1079 -- Payroll Name
1080 begin
1081 select payroll_name
1082 into lv_payroll_name
1083 from pay_all_payrolls_f
1084 where payroll_id = l_payroll_id
1085 and fnd_date.canonical_to_date(p_date_earned)
1086 between effective_start_date and effective_end_date;
1087
1088 hr_utility.trace('lv_payroll_name = ' || lv_payroll_name);
1089
1090 exception when no_data_found then
1091 hr_utility.trace('lv_payroll_name exception: no_data_found ');
1092 null;
1093
1094 end;
1095
1096 -- Consolidation Set Name
1097 begin
1098 select consolidation_set_name
1099 into lv_consolidation_set_name
1100 from pay_consolidation_sets
1101 where consolidation_set_id = l_consolidation_set_id;
1102
1103 hr_utility.trace('lv_consolidation_set_name = ' || lv_consolidation_set_name);
1104
1105 exception when no_data_found then
1106 hr_utility.trace('lv_consolidation_set_name exception: no_data_found ');
1107 null;
1108
1109 end;
1110
1111 -- Organization
1112 begin
1113 select name
1114 into lv_organization
1115 from per_organization_units
1116 where organization_id = to_number(p_organization)
1117 and business_group_id = p_business_group_id
1118 and fnd_date.canonical_to_date(p_date_earned) between
1119 date_from and nvl(date_to,to_date('31/12/4712','DD/MM/YYYY'));
1120
1121
1122 hr_utility.trace('lv_organization = ' || lv_organization);
1123
1124 exception when no_data_found then
1125 hr_utility.trace('lv_organization exception: no_data_found ');
1126 null;
1127
1128 end;
1129
1130 -- Location
1131 begin
1132 select location_code||' '||description
1133 into lv_location
1134 from hr_locations
1135 where location_id = to_number(p_location);
1136
1137 hr_utility.trace('lv_location = ' || lv_location);
1138
1139 exception when no_data_found then
1140 hr_utility.trace('lv_location exception: no_data_found ');
1141 null;
1142
1143 end;
1144
1145 -- GRE Name
1149 into lv_gre_name
1146 if p_gre_or_tax_group = 'GRE' then
1147 begin
1148 select name
1150 from hr_ca_tax_units_v
1151 where tax_unit_id = to_number(p_gre)
1152 and business_group_id = p_business_group_id
1153 and fnd_date.canonical_to_date(p_date_earned) between
1154 date_from and nvl(date_to,to_date('31/12/4712','DD/MM/YYYY'));
1155
1156 hr_utility.trace('lv_gre_name = ' || lv_gre_name);
1157
1158 exception when no_data_found then
1159 hr_utility.trace('lv_gre_name exception: no_data_found ');
1160 null;
1161
1162 end;
1163 end if;
1164
1165 -- Tax Group Name
1166 if p_gre_or_tax_group = 'Tax Group' then
1167 begin
1168 select hou.name
1169 into lv_tax_group
1170 from hr_all_organization_units hou,
1171 hr_organization_information hoi
1172 where hou.organization_id = hoi.organization_id
1173 and hoi.org_information_context = 'CLASS'
1174 and hoi.org_information1 = 'CA_TAX_GROUP'
1175 and hou.business_group_id = p_business_group_id
1176 and hou.organization_id = to_number(p_tax_group)
1177 and fnd_date.canonical_to_date(p_date_earned) between
1178 hou.date_from and nvl(hou.date_to,to_date('31/12/4712','DD/MM/YYYY'));
1179
1180 hr_utility.trace('lv_tax_group = ' || lv_tax_group);
1181
1182 exception when no_data_found then
1183 hr_utility.trace('lv_tax_group exception: no_data_found ');
1184 null;
1185
1186 end;
1187 end if;
1188
1189 -- Account Number
1190 if p_quebec = 'Y' then
1191 begin
1192 select pcp.account_number
1193 into lv_account_number
1194 from hr_organization_information hoi1,
1195 hr_organization_information hoi2,
1196 pay_ca_pmed_accounts pcp
1197 where hoi1.org_information_context = 'CLASS'
1198 and hoi1.org_information1 = 'CA_PMED'
1199 and hoi1.org_information2 = 'Y'
1200 and hoi1.organization_id = hoi2.organization_id
1201 and pcp.organization_id = hoi1.organization_id
1202 and pcp.business_group_id = p_business_group_id
1203 and pcp.source_id = to_number(p_qhsf_account_number)
1204 and hoi2.org_information1 = 'QC'
1205 and hoi2.org_information_context = 'Provincial Information';
1206
1207 hr_utility.trace('lv_account_number = ' || lv_account_number);
1208
1209 exception when no_data_found then
1210 hr_utility.trace('lv_account_number exception: no_data_found ');
1211 null;
1212
1213 end;
1214 end if;
1215
1216 -- Federal/Province Flags for SDR
1217
1218 if p_sdr_federal = 'Y' then
1219 lv_federal := 'Yes';
1220 elsif p_sdr_federal = 'N' then
1221 lv_federal := 'No';
1222 else
1223 lv_federal := '';
1224 end if;
1225
1226 if p_sdr_province = 'Y' then
1227 lv_province := 'Yes';
1228 elsif p_sdr_province = 'N' then
1229 lv_province := 'No';
1230 else
1231 lv_province := '';
1232 end if;
1233
1234
1235 if p_consolidation_set_id is null then
1236 lv_ded_consolidation_dummy := 'X';
1237 else
1238 lv_ded_consolidation_dummy := '';
1239 end if;
1240
1241 if p_reg_suppress_zero = 'Y' then
1242 lv_reg_suppress_zero := 'Yes';
1243 elsif p_reg_suppress_zero = 'N' then
1244 lv_reg_suppress_zero := 'No';
1245 end if;
1246
1247 if p_reg_full_report_ver = 'Y' then
1248 lv_reg_full_report_ver := 'Yes';
1249 elsif p_reg_full_report_ver = 'N' then
1250 lv_reg_full_report_ver := 'No';
1251 end if;
1252
1253 if p_reg_employee_page_break = 'Y' then
1254 lv_reg_employee_page_break := 'Yes';
1255 elsif p_reg_employee_page_break = 'N' then
1256 lv_reg_employee_page_break := 'No';
1257 end if;
1258
1259
1260 begin
1261 select meaning
1262 into lv_reg_sort_one
1263 from hr_lookups
1264 where lookup_code = p_reg_sort_one
1265 and lookup_type = 'PAY_CA_YE_SORT_CODE';
1266
1267 hr_utility.trace('lv_reg_sort_one = ' || lv_reg_sort_one );
1268
1269 exception when no_data_found then
1270 hr_utility.trace('lv_reg_sort_one exception: no_data_found ');
1271 null;
1272 end;
1273
1274 begin
1275 select meaning
1276 into lv_reg_sort_two
1277 from hr_lookups
1278 where lookup_code = p_reg_sort_two
1279 and lookup_type = 'PAY_CA_YE_SORT_CODE';
1280
1281 hr_utility.trace('lv_reg_sort_two = ' || lv_reg_sort_two );
1282
1283 exception when no_data_found then
1284 hr_utility.trace('lv_reg_sort_two exception: no_data_found ');
1285 null;
1286 end;
1287
1288 begin
1289 select meaning
1290 into lv_reg_sort_three
1291 from hr_lookups
1292 where lookup_code = p_reg_sort_three
1293 and lookup_type = 'PAY_CA_YE_SORT_CODE';
1294
1295 hr_utility.trace('lv_reg_sort_three = ' || lv_reg_sort_three );
1296
1297 exception when no_data_found then
1298 hr_utility.trace('lv_reg_sort_three exception: no_data_found ');
1299 null;
1300 end;
1301
1302 begin
1303
1304 if p_batch_id is not null then
1305
1309 where batch_id = p_batch_id;
1306 select batch_name
1307 into lv_batch_name
1308 from pay_batch_headers
1310
1311 hr_utility.trace('Batch Name :' ||lv_batch_name);
1312
1313 end if;
1314
1315 exception when no_data_found then
1316 lv_batch_name := '';
1317 hr_utility.trace('lv_batch_name exception: no_data_found ');
1318 null;
1319
1320 end;
1321
1322 begin
1323 /* currently we are only looking at the context at payroll level
1324 as we do not have any required parameter for GRE on the SRS
1325 screen, if we want to use the contact defined at GRE level then
1326 we need to modify the package and SRS definition to get the
1327 GRE_ID , and pass it in the following function.
1328 */
1329
1330 lv_contact_user_name := get_notifier(to_number(p_payroll_id),
1331 to_number(p_gre),
1332 p_date_earned);
1333
1334 exception when others then
1335 hr_utility.trace('lv_contact_user_name exception: OTHERS at get_notifier');
1336 null;
1337
1338 end;
1339
1340 begin
1341
1342 select orig_system,
1343 orig_system_id,
1344 name,
1345 display_name
1346 into lv_orig_system,
1347 lv_orig_system_id,
1348 lv_role_name,
1349 lv_role_display_name
1350 from wf_roles
1351 where name = lv_contact_user_name ;
1352
1353 exception when no_data_found then
1354 hr_utility.trace('lv_role_name exception: no_data_found');
1355 null;
1356
1357 end;
1358
1359 hr_utility.trace('orig system : '|| lv_orig_system);
1360 hr_utility.trace('orig system id : '|| lv_orig_system_id);
1361 hr_utility.trace('role name = ' || lv_role_name);
1362
1363 wf_engine.SetItemOwner (itemtype => ItemType,
1364 itemkey => ItemKey,
1365 owner => ProcessOwner);
1366
1367 lv_process_list_html_2 := 'WF_NOTIFICATION(ATTRS';
1368
1369 /* set variables that run processes */
1370
1371 -- BEE Transfer
1372 if p_batch_id is not null then
1373 lv_is_batch := 'Y';
1374 lv_prc_list := lv_prc_list||lv_conc_prog_name(1)||wf_core.newline;
1375 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS01';
1376 else
1377 lv_is_batch := 'N';
1378 end if;
1379
1380 -- Retro Notification Report
1381
1382 if ((p_retro_assignment_set is not null) and
1383 (p_retro_event_group is not null)) then
1384
1385 lv_is_retro_notification := 'Y';
1386 lv_prc_list := lv_prc_list||lv_conc_prog_name(2)||wf_core.newline;
1387 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS02';
1388
1389 -- If the Retro Notification is going to be run
1390 -- then the RetroPay assignment set should be the
1391 -- same one that is generated by Retro Notification
1392
1393 lv_retropay_asg_set := p_retro_assignment_set;
1394
1395 else
1396 lv_is_retro_notification := 'N';
1397 end if;
1398
1399 -- RetroPay By Element
1400
1401 if (((p_retropay_assignment_set is not null) or
1402 (p_retro_assignment_set is not null)) and
1403 (p_retropay_element_set is not null) and
1404 (p_retro_start_date is not null)) then
1405 lv_is_retropay := 'Y';
1406 lv_prc_list := lv_prc_list||lv_conc_prog_name(3)||wf_core.newline;
1407 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS03';
1408 else
1409 lv_is_retropay := 'N';
1410 end if;
1411
1412 lv_prc_list := lv_prc_list||lv_conc_prog_name(4)||wf_core.newline;
1413 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS04';
1414
1415 -- Gross to Net Summary
1416
1417 if ((p_gross_to_net_period is not null)) then
1418 lv_is_gtn := 'Y';
1419 lv_prc_list := lv_prc_list||lv_conc_prog_name(5)||wf_core.newline;
1420 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS05';
1421 else
1422 lv_is_gtn := 'N';
1423 end if;
1424
1425 lv_prc_list := lv_prc_list||lv_conc_prog_name(6)||wf_core.newline;
1426 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS06';
1427
1428 -- Employee Assignments Not Paid
1429 if ((p_consolidation_set_id is not null)) then
1430 lv_is_consolidation_set := 'Y';
1431 lv_prc_list := lv_prc_list||lv_conc_prog_name(7)||wf_core.newline;
1432 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS07';
1433 else
1434 lv_is_consolidation_set := 'N';
1435 end if;
1436
1437 -- Payroll Exception Report
1438 if ((p_consolidation_set_id is not null) and
1439 ((p_exp_report_name is not null) or
1440 (p_exp_group_name is not null))) then
1441 lv_is_exception := 'Y';
1442 lv_prc_list := lv_prc_list||lv_conc_prog_name(8)||wf_core.newline;
1443 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS08';
1444 else
1445 lv_is_exception := 'N';
1446 end if;
1447
1448 -- Statutory Deductions Report
1449
1450 if ((p_sdr_federal is not null) and
1451 (p_sdr_reporting_dimension is not null)) then
1452 lv_is_sdr := 'Y';
1453 lv_prc_list := lv_prc_list||lv_conc_prog_name(9)||wf_core.newline;
1454 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS09';
1455 else
1456 lv_is_sdr := 'N';
1457 end if;
1458
1459 -- Quebec Health Services Fund
1463 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS10';
1460
1461 if (p_quebec = 'Y') then
1462 lv_prc_list := lv_prc_list||lv_conc_prog_name(10)||wf_core.newline;
1464 end if;
1465
1466
1467 -- Pre Payment
1468
1469 if (p_consolidation_set_id is not null) then
1470 lv_prc_list := lv_prc_list||lv_conc_prog_name(11)||wf_core.newline;
1471 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS11';
1472 end if;
1473
1474
1475 -- Payroll Register Report
1476
1477 if ((p_consolidation_set_id is not null) and
1478 (p_reg_suppress_zero is not null) and
1479 (p_reg_req_num is not null) and
1480 (p_reg_reporting_dim is not null)) then
1481 lv_is_payroll_register := 'Y';
1482 lv_prc_list := lv_prc_list||lv_conc_prog_name(12)||wf_core.newline;
1483 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS12';
1484 else
1485 lv_is_payroll_register := 'N';
1486 end if;
1487
1488 -- Element Register Report
1489
1490 if (p_ele_selection_criterion is not null)
1491 then
1492 lv_is_element_register := 'Y';
1493 lv_prc_list := lv_prc_list||lv_conc_prog_name(13)||wf_core.newline;
1494 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS13';
1495 else
1496 lv_is_element_register := 'N';
1497 end if;
1498
1499 -- Deductions Report
1500
1501 if ((p_deduction_sort_one is not null) and
1502 (p_deduction_sort_two is not null) and
1503 (p_deduction_sort_three is not null))
1504 then
1505 lv_is_deductions := 'Y';
1506 lv_prc_list := lv_prc_list||lv_conc_prog_name(14)||wf_core.newline;
1507 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS14';
1508 else
1509 lv_is_deductions := 'N';
1510 end if;
1511
1512 -- Direct Deposit
1513
1514 if ((p_consolidation_set_id is not null) and
1515 (p_dd_payment_method is not null))
1516 then
1517 lv_is_direct_deposit := 'Y';
1518 lv_prc_list := lv_prc_list||lv_conc_prog_name(15)||wf_core.newline;
1519 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS15';
1520 else
1521 lv_is_direct_deposit := 'N';
1522 end if;
1523
1524
1525 -- Canadian Payroll Archiver
1526
1527 if (p_consolidation_set_id is not null) then
1528 lv_prc_list := lv_prc_list||lv_conc_prog_name(16)||wf_core.newline;
1529 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS16';
1530 end if;
1531
1532
1533 -- Canadian Cheque Writer
1534
1535 if ((p_consolidation_set_id is not null) and
1536 (p_cheque_payment_method is not null) and
1537 (p_cheque_payment_type is not null) and
1538 (p_cheque_sort_sequence is not null) and
1539 (p_cheque_style is not null) and
1540 (p_start_cheque_number is not null)) then
1541 lv_is_chequewriter := 'Y';
1542 lv_prc_list := lv_prc_list||lv_conc_prog_name(17)||wf_core.newline;
1543 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS17';
1544 else
1545 lv_is_chequewriter := 'N';
1546 end if;
1547
1548 -- Canadian Deposit Advice
1549
1550 if ((p_da_data_type is not null) and
1551 (p_consolidation_set_id is not null)) then
1552 lv_is_deposit_advice := 'Y';
1553 lv_prc_list := lv_prc_list||lv_conc_prog_name(18)||wf_core.newline;
1554 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS18';
1555 else
1556 lv_is_deposit_advice := 'N';
1557 end if;
1558
1559 -- Payment Report
1560
1561 if (p_consolidation_set_id is not null) then
1562 lv_prc_list := lv_prc_list||lv_conc_prog_name(19)||wf_core.newline;
1563 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS19';
1564 end if;
1565
1566 -- Record of Employment
1567
1568 if ((p_roe_assignment_set is not null)) then
1569 lv_is_roe := 'Y';
1570 lv_prc_list := lv_prc_list||lv_conc_prog_name(20)||wf_core.newline;
1571 lv_prc_list := lv_prc_list||lv_conc_prog_name(21)||wf_core.newline;
1572 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS20';
1573 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS21';
1574
1575 if (p_roe_mag_roe_type is not null) then
1576 lv_is_roe_mag := 'Y';
1577 lv_prc_list := lv_prc_list||lv_conc_prog_name(22)||wf_core.newline;
1578 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS22';
1579 else
1580 lv_is_roe_mag := 'N';
1581 end if;
1582 else
1583 lv_is_roe := 'N';
1584 lv_is_roe_mag := 'N';
1585 end if;
1586
1587 -- Costing
1588
1589 if ((p_consolidation_set_id is not null)) then
1590 lv_prc_list := lv_prc_list||lv_conc_prog_name(23)||wf_core.newline;
1591 lv_prc_list := lv_prc_list||lv_conc_prog_name(24)||wf_core.newline;
1592 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS23';
1593 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS24';
1594
1595 if (p_cost_summary_file_out is not null) then
1596 lv_is_costing_summary := 'Y';
1597 lv_prc_list := lv_prc_list||lv_conc_prog_name(25)||wf_core.newline;
1598 lv_process_list_html_2 := lv_process_list_html_2||',PROCESS25';
1599 else
1600 lv_is_costing_summary := 'N';
1601 end if;
1602 else
1603 lv_is_costing_summary := 'N';
1604 end if;
1605
1606 lv_process_list_subject := 'List of Processes and Reports which will be submitted';
1607
1611
1608 if lv_workflow_mode = 'W' then
1609
1610 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;
1612 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.';
1613
1614 else
1615
1616 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;
1617
1618 lv_process_list_html_1 := 'Review the List of Processes and Reports which will be submitted based on the input parameters.';
1619
1620 end if;
1621
1622 lv_process_list_html_2 := lv_process_list_html_2||')';
1623
1624 hr_utility.trace('p_payroll_id = ' || p_payroll_id);
1625 hr_utility.trace('p_consolidation_set_id = ' || p_consolidation_set_id);
1626 hr_utility.trace('p_date_earned = ' || p_date_earned);
1627 hr_utility.trace('p_date_paid = ' || p_date_paid);
1628 hr_utility.trace('p_payroll_assignment_set = ' || p_payroll_assignment_set);
1629 hr_utility.trace('p_payroll_run_type = ' || p_payroll_run_type);
1630
1631 begin
1632
1633 num_varname(1) := 'USER_ID';
1634 num_varvalue(1) := l_user_id;
1635 num_varname(2) := 'APPLICATION_ID';
1636 num_varvalue(2) := l_resp_appl_id;
1637 num_varname(3) := 'RESPONSIBILITY_ID';
1638 num_varvalue(3) := l_resp_id;
1639 num_varname(4) := 'ORG_ID';
1640 num_varvalue(4) := l_org_id;
1641 num_varname(5) := 'PER_SECURITY_PROFILE_ID';
1642 num_varvalue(5) := l_per_sec_id;
1643 num_varname(6) := 'SECURITY_GROUP_ID';
1644 num_varvalue(6) := l_sec_grp_id;
1645
1646
1647 i := 0;
1648
1649 varname(i) := 'P_BUSINESS_GROUP_ID';
1650 varval(i) := to_char(p_business_group_id);
1651 i := i+1;
1652 varname(i) := 'WORKFLOW_MODE';
1653 varval(i) := lv_workflow_mode;
1654 i := i+1;
1655 varname(i) := 'CURR_ITEM_TYPE';
1656 varval(i) := ItemType;
1657 i := i+1;
1658 varname(i) := 'CURR_ITEM_KEY';
1659 varval(i) := ItemKey;
1660 i := i+1;
1661 varname(i) := 'ROLE_NAME';
1662 varval(i) := lv_role_name;
1663 i := i+1;
1664 varname(i) := 'USER';
1665 varval(i) := lv_contact_user_name;
1666 i := i+1;
1667
1668
1669 varname(i) := 'IS_CONSOLIDATION_SET';
1670 varval(i) := lv_is_consolidation_set;
1671 i := i+1;
1672 varname(i) := 'PAYROLL_NAME';
1673 varval(i) := lv_payroll_name;
1674 i := i+1;
1675 varname(i) := 'PAYROLL_ID';
1676 varval(i) := p_payroll_id;
1677 i := i+1;
1678 varname(i) := 'CONSOLIDATION_SET_NAME';
1679 varval(i) := lv_consolidation_set_name;
1680 i := i+1;
1681 varname(i) := 'CONSOLIDATION_SET_ID';
1682 varval(i) := p_consolidation_set_id;
1683 i := i+1;
1684 varname(i) := 'DATE_EARNED';
1685 varval(i) := p_date_earned;
1686 i := i+1;
1687 varname(i) := 'DATE_PAID';
1688 varval(i) := p_date_paid;
1689 i := i+1;
1690 varname(i) := 'DATE_TIME';
1691 varval(i) := lv_date_time;
1692 i := i+1;
1693 varname(i) := 'GRE';
1694 varval(i) := p_gre;
1695 i := i+1;
1696 varname(i) := 'GRE_NAME';
1697 varval(i) := lv_gre_name;
1698 i := i+1;
1699 varname(i) := 'TAX_GROUP';
1700 varval(i) := p_tax_group;
1701 i := i+1;
1702 varname(i) := 'TAX_GROUP_NAME';
1703 varval(i) := lv_tax_group;
1704 i := i+1;
1705 varname(i) := 'ORGANIZATION';
1706 varval(i) := p_organization;
1707 i := i+1;
1708 varname(i) := 'ORGANIZATION_NAME';
1709 varval(i) := lv_organization;
1710 i := i+1;
1711 varname(i) := 'LOCATION';
1712 varval(i) := p_location;
1713 i := i+1;
1714 varname(i) := 'LOCATION_NAME';
1715 varval(i) := lv_location;
1716 i := i+1;
1717
1718 -- BEE (Transfer)
1719
1720 varname(i) := 'IS_BATCH';
1721 varval(i) := lv_is_batch;
1722 i := i+1;
1723 varname(i) := 'BATCH_ID';
1724 varval(i) := to_char(p_batch_id);
1725 i := i+1;
1726 varname(i) := 'BEE_BATCH_NAME';
1727 varval(i) := lv_batch_name;
1728 i := i+1;
1729
1730 -- Retro-Notification Report
1731
1732 varname(i) := 'IS_RETRO_NOTIFICATION';
1733 varval(i) := lv_is_retro_notification;
1734 i := i+1;
1735 varname(i) := 'RETRO_START_DATE_DUMMY';
1736 varval(i) := 'START_DATE=1900/01/01 00:00:00';
1737 i := i+1;
1738 varname(i) := 'END_DATE_LEG_PARAM';
1739 varval(i) := 'END_DATE='||p_date_earned;
1740 i := i+1;
1741 varname(i) := 'PAYROLL_LEG_PARAM';
1742 varval(i) := 'PAYROLL_ID='||p_payroll_id;
1743 i := i+1;
1744 varname(i) := 'RETRO_PERIOD';
1745 varval(i) := lv_retro_period;
1746 i := i+1;
1747 varname(i) := 'EVENT_GROUP';
1748 varval(i) := p_retro_event_group;
1749 i := i+1;
1750 varname(i) := 'EVENT_GROUP_NAME';
1751 varval(i) := lv_event_group_name;
1752 i := i+1;
1753 varname(i) := 'RETRO_ASSIGNMENT_SET_NAME';
1754 varval(i) := p_retro_assignment_set;
1755 i := i+1;
1759
1756 varname(i) := 'RETRO_ASSIGNMENT_SET_NAME_DUMY';
1757 varval(i) := 'ASG_SET='||p_retro_assignment_set;
1758 i := i+1;
1760 -- RetroPay By Element
1761
1762 varname(i) := 'IS_RETROPAY';
1763 varval(i) := lv_is_retropay;
1764 i := i+1;
1765 varname(i) := 'RETRO_ASSIGNMENT_SET_ID';
1766 varval(i) := p_retropay_assignment_set;
1767 i := i+1;
1768 varname(i) := 'RETROPAY_ASG_SET_NAME';
1769 varval(i) := lv_retropay_asg_set;
1770 i := i+1;
1771 varname(i) := 'RETRO_ELEMENT_SET';
1772 varval(i) := p_retropay_element_set;
1773 i := i+1;
1774 varname(i) := 'RETROPAY_ELEMENT_SET_NAME';
1775 varval(i) := lv_retropay_element_set;
1776 i := i+1;
1777 varname(i) := 'RETRO_START_DATE';
1778 varval(i) := p_retro_start_date;
1779 i := i+1;
1780
1781 -- Payroll Run Process
1782
1783 varname(i) := 'PAYROLL_ELEMENT_SET_ID';
1784 varval(i) := p_payroll_element_set;
1785 i := i+1;
1786 varname(i) := 'PAYROLL_ELEMENT_SET_NAME';
1787 varval(i) := lv_payroll_element_set;
1788 i := i+1;
1789 varname(i) := 'PAYROLL_ASSIGNMENT_SET_ID';
1790 varval(i) := p_payroll_assignment_set;
1791 i := i+1;
1792 varname(i) := 'PAYROLL_ASSIGNMENT_SET_NAME';
1793 varval(i) := lv_payroll_assignment_set;
1794 i := i+1;
1795 varname(i) := 'PAYROLL_RUN_TYPES_ID';
1796 varval(i) := p_payroll_run_type;
1797 i := i+1;
1798 varname(i) := 'PAYROLL_RUN_TYPE_NAME';
1799 varval(i) := lv_payroll_run_type;
1800 i := i+1;
1801
1802 -- Gross to Net Summary
1803
1804 varname(i) := 'IS_GTN';
1805 varval(i) := lv_is_gtn;
1806 i := i+1;
1807 varname(i) := 'GTN_PERIOD';
1808 varval(i) := p_gross_to_net_period;
1809 i := i+1;
1810 varname(i) := 'GTN_PERIOD_NAME';
1811 varval(i) := lv_gtn_period;
1812 i := i+1;
1813
1814 -- Payroll Message Report
1815
1816 varname(i) := 'SESSION_DATE';
1817 varval(i) := p_session_date;
1818 i := i+1;
1819 varname(i) := 'PAYROLL_ACTION_ID';
1820 varval(i) := p_payroll_process;
1821 i := i+1;
1822 varname(i) := 'PAYROLL_MSG_PAYROLL_PROCESS';
1823 varval(i) := lv_payroll_process;
1824 i := i+1;
1825
1826 -- Quebec Health Services Fund
1827
1828 varname(i) := 'IS_QUEBEC';
1829 varval(i) := p_quebec;
1830 i := i+1;
1831 varname(i) := 'ACCOUNT_NUMBER_ID';
1832 varval(i) := p_qhsf_account_number;
1833 i := i+1;
1834 varname(i) := 'ACCOUNT_NUMBER';
1835 varval(i) := lv_account_number;
1836 i := i+1;
1837 varname(i) := 'OVERRIDE_TABLE';
1838 varval(i) := p_qhsf_override_table;
1839 i := i+1;
1840
1841 -- Statutory Deductions Report
1842
1843 varname(i) := 'IS_SDR';
1844 varval(i) := lv_is_sdr;
1845 i := i+1;
1846 varname(i) := 'FEDERAL';
1847 varval(i) := p_sdr_federal;
1848 i := i+1;
1849 varname(i) := 'FEDERAL_NAME';
1850 varval(i) := lv_federal;
1851 i := i+1;
1852 varname(i) := 'PROVINCE';
1853 varval(i) := p_sdr_province;
1854 i := i+1;
1855 varname(i) := 'PROVINCE_NAME';
1856 varval(i) := lv_province;
1857 i := i+1;
1858 varname(i) := 'REPORTING_DIMENSION';
1859 varval(i) := p_sdr_reporting_dimension;
1860 i := i+1;
1861 varname(i) := 'REPORTING_DIMENSION_NAME';
1862 varval(i) := lv_reporting_dimension;
1863 i := i+1;
1864
1865
1866 -- Exception Report
1867
1868 varname(i) := 'IS_EXCEPTION';
1869 varval(i) := lv_is_exception;
1870 i := i+1;
1871 varname(i) := 'SELECT_REPORT_OR_GROUP';
1872 varval(i) := p_exp_report_or_group;
1873 i := i+1;
1874 varname(i) := 'EXCEPTION_LEGISLATION_CODE';
1875 varval(i) := p_exp_legislation_code;
1876 i := i+1;
1877 varname(i) := 'IS_EXCEPTION_GROUP';
1878 varval(i) := p_exp_is_group;
1879 i := i+1;
1880 varname(i) := 'EXCEPTION_GROUP_NAME';
1881 varval(i) := p_exp_group_name;
1882 i := i+1;
1883 varname(i) := 'EXCEPTION_GROUP_NAME_NAME';
1884 varval(i) := lv_exp_group_name;
1885 i := i+1;
1886 varname(i) := 'EXCEPTION_GROUP_NAME_DMY';
1887 varval(i) := 'TRANSFER_GROUP='||p_exp_group_name;
1888 i := i+1;
1889 varname(i) := 'IS_EXCEPTION_REPORT';
1890 varval(i) := p_exp_is_report;
1891 i := i+1;
1892 varname(i) := 'EXCEPTION_REPORT_NAME';
1893 varval(i) := p_exp_report_name;
1894 i := i+1;
1895 varname(i) := 'EXCEPTION_REPORT_NAME_NAME';
1896 varval(i) := lv_exp_report_name;
1897 i := i+1;
1898 varname(i) := 'EXCEPTION_REPORT_NAME_DMY';
1899 varval(i) := 'TRANSFER_REPORT='||p_exp_report_name;
1900 i := i+1;
1901 varname(i) := 'OVER_RIDE_VARIANCE_TYPE';
1902 varval(i) := p_exp_variance_type;
1903 i := i+1;
1904 varname(i) := 'OVER_RIDE_VARIANCE_TYPE_NAME';
1905 varval(i) := lv_exp_vartype_name;
1906 i := i+1;
1907 varname(i) := 'OVER_RIDE_VARIANCE_TYPE_DMY';
1908 varval(i) := 'TRANSFER_VARTYPE='||p_exp_variance_type;
1909 i := i+1;
1910 varname(i) := 'OVER_RIDE_VARIANCE_VALUE';
1911 varval(i) := p_exp_variance_value;
1915 i := i+1;
1912 i := i+1;
1913 varname(i) := 'OVER_RIDE_VARIANCE_VALUE_DMY';
1914 varval(i) := 'TRANSFER_VARVALUE='||p_exp_variance_value;
1916 varname(i) := 'TRANSFER_DATE';
1917 varval(i) := 'TRANSFER_DATE='||p_date_paid;
1918 i := i+1;
1919 varname(i) := 'PPA_FINDER_PQP';
1920 varval(i) := lv_exp_finder_pqp;
1921 i := i+1;
1922 varname(i) := 'TRANSFER_PPA_FINDER_PQP';
1923 varval(i) := 'TRANSFER_PPA_FINDER='||lv_exp_finder_pqp;
1924 i := i+1;
1925 varname(i) := 'TRANSFER_PAYROLL';
1926 varval(i) := 'TRANSFER_PAYROLL='||p_payroll_id;
1927 i := i+1;
1928 varname(i) := 'TRANSFER_CONC_SET';
1929 varval(i) := 'TRANSFER_CONC_SET='||p_consolidation_set_id;
1930 i := i+1;
1931
1932 -- PrePayment
1933
1934 varname(i) := 'PREPAY_PAYMENT_METHOD_OVERRIDE';
1935 varval(i) := p_payment_method_override;
1936 i := i+1;
1937 varname(i) := 'PREPAY_PAYMENT_METHOD_NAME';
1938 varval(i) := lv_prepay_payment_method;
1939 i := i+1;
1940
1941 -- Element Register Report
1942
1943 varname(i) := 'IS_ELEMENT';
1944 varval(i) := lv_is_element_register;
1945 i := i+1;
1946 varname(i) := 'SELECTION_CRITERION';
1947 varval(i) := p_ele_selection_criterion;
1948 i := i+1;
1949 varname(i) := 'IS_ELEMENT_SET';
1950 varval(i) := p_ele_is_element_set;
1954 i := i+1;
1951 i := i+1;
1952 varname(i) := 'ELE_REG_ELEMENT_SET';
1953 varval(i) := p_ele_element_set;
1955 varname(i) := 'ELE_REG_ELEMENT_SET_NAME';
1956 varval(i) := lv_ele_element_set;
1957 i := i+1;
1958 varname(i) := 'IS_ELEMENT_CLASSIFICATION';
1959 varval(i) := p_ele_is_element_class;
1960 i := i+1;
1961 varname(i) := 'ELEMENT_CLASSIFICATION';
1962 varval(i) := p_ele_element_class;
1963 i := i+1;
1964 varname(i) := 'ELEMENT_CLASSIFICATION_NAME';
1965 varval(i) := lv_ele_element_class;
1966 i := i+1;
1967 varname(i) := 'IS_ELEMENT_NAME';
1968 varval(i) := p_ele_is_element_name;
1969 i := i+1;
1970 varname(i) := 'ELEMENT';
1971 varval(i) := p_ele_element_name;
1972 i := i+1;
1973 varname(i) := 'ELEMENT_NAME';
1974 varval(i) := lv_ele_element_name;
1975 i := i+1;
1976 varname(i) := 'EMP_ID';
1977 varval(i) := p_ele_employee;
1978 i := i+1;
1979 varname(i) := 'EMP_NAME';
1980 varval(i) := lv_ele_employee;
1981 i := i+1;
1982
1983 -- Deduction Report(CA)
1984
1985 varname(i) := 'IS_DEDUCTIONS';
1986 varval(i) := lv_is_deductions;
1987 i := i+1;
1988 varname(i) := 'DEDN_CONSOLIDATION_DMY';
1989 varval(i) := lv_ded_consolidation_dummy;
1990 i := i+1;
1991 varname(i) := 'DEDN_CLASSIFICATION';
1992 varval(i) := p_deduction_class;
1993 i := i+1;
1994 varname(i) := 'DEDN_CLASSIFICATION_NAME';
1995 varval(i) := lv_deduction_class;
1996 i := i+1;
1997 varname(i) := 'DEDN_ID';
1998 varval(i) := p_deduction_name;
1999 i := i+1;
2000 varname(i) := 'DEDN_NAME';
2001 varval(i) := lv_deduction_name;
2002 i := i+1;
2003 varname(i) := 'SORT_ONE';
2004 varval(i) := p_deduction_sort_one;
2005 i := i+1;
2006 varname(i) := 'SORT_TWO';
2007 varval(i) := p_deduction_sort_two;
2008 i := i+1;
2009 varname(i) := 'SORT_THREE';
2010 varval(i) := p_deduction_sort_three;
2011 i := i+1;
2012
2013 -- Payroll Register Report
2014
2015
2016 varname(i) := 'IS_PAYROLL_REGISTER';
2017 varval(i) := lv_is_payroll_register;
2018 i := i+1;
2019 varname(i) := 'BUSINESS_GROUP_ID_DMY';
2020 varval(i) := 'B_G_ID='||p_business_group_id;
2021 i := i+1;
2022 varname(i) := 'PAYROLL_DMY';
2023 varval(i) := 'PY_ID='||p_payroll_id;
2024 i := i+1;
2025 varname(i) := 'CONSOLIDATION_SET_DMY';
2026 varval(i) := 'C_ST_ID='||p_consolidation_set_id;
2027 i := i+1;
2028 varname(i) := 'GRE_DMY';
2029 varval(i) := 'T_U_ID='||p_gre;
2030 i := i+1;
2031 varname(i) := 'LOCATION_DMY';
2032 varval(i) := 'L_ID='||p_location;
2033 i := i+1;
2034 varname(i) := 'ORGANIZATION_DMY';
2035 varval(i) := 'O_ID='||p_organization;
2036 i := i+1;
2037 varname(i) := 'PERSON_DMY';
2038 varval(i) := 'P_ID='||p_ele_employee;
2039 i := i+1;
2040 varname(i) := 'SUPPRESS_ZERO_CODE';
2041 varval(i) := p_reg_suppress_zero;
2042 i := i+1;
2043 varname(i) := 'SUPPRESS_ZERO';
2044 varval(i) := lv_reg_suppress_zero;
2045 i := i+1;
2046 varname(i) := 'SUPPRESS_ZERO_DMY';
2047 varval(i) := 'S_Z='||p_reg_suppress_zero;
2048 i := i+1;
2049 varname(i) := 'FULL_REPORT_VERSION_CODE';
2050 varval(i) := p_reg_full_report_ver;
2051 i := i+1;
2052 varname(i) := 'FULL_REPORT_VERSION';
2053 varval(i) := lv_reg_full_report_ver;
2054 i := i+1;
2055 varname(i) := 'FULL_REPORT_VERSION_DMY';
2056 varval(i) := 'F_R='||p_reg_full_report_ver;
2057 i := i+1;
2058 varname(i) := 'PAYREG_REPORTING_DIMENSION_C';
2059 varval(i) := p_reg_reporting_dim;
2060 i := i+1;
2061 varname(i) := 'PAYREG_REPORTING_DIMENSION';
2062 varval(i) := lv_reg_reporting_dimension;
2063 i := i+1;
2064 varname(i) := 'PAYREG_REPORTING_DIMENSION_DMY';
2065 varval(i) := 'RP_DM='||p_reg_reporting_dim;
2066 i := i+1;
2067 varname(i) := 'SORT_ONE_CODE';
2068 varval(i) := p_reg_sort_one;
2069 i := i+1;
2070 varname(i) := 'REGISTER_SORT_ONE';
2071 varval(i) := lv_reg_sort_one;
2072 i := i+1;
2073 varname(i) := 'SORT_ONE_DMY';
2074 varval(i) := 'P_S1='||p_reg_sort_one;
2075 i := i+1;
2076 varname(i) := 'SORT_TWO_CODE';
2077 varval(i) := p_reg_sort_two;
2078 i := i+1;
2079 varname(i) := 'REGISTER_SORT_TWO';
2080 varval(i) := lv_reg_sort_two;
2081 i := i+1;
2082 varname(i) := 'SORT_TWO_DMY';
2083 varval(i) := 'P_S2='||p_reg_sort_two;
2084 i := i+1;
2085 varname(i) := 'SORT_THREE_CODE';
2086 varval(i) := p_reg_sort_three;
2087 i := i+1;
2088 varname(i) := 'REGISTER_SORT_THREE';
2089 varval(i) := lv_reg_sort_three;
2090 i := i+1;
2091 varname(i) := 'SORT_THREE_DMY';
2092 varval(i) := 'P_S3='||p_reg_sort_three;
2093 i := i+1;
2094 varname(i) := 'EMPLOYEE_PAGE_BREAK_CODE';
2095 varval(i) := p_reg_employee_page_break;
2096 i := i+1;
2097 varname(i) := 'EMPLOYEE_PAGE_BREAK';
2098 varval(i) := lv_reg_employee_page_break;
2099 i := i+1;
2103 varname(i) := 'SEQ_NUM';
2100 varname(i) := 'EMPLOYEE_PAGE_BREAK_DMY';
2101 varval(i) := 'EMP_BRK='||p_reg_employee_page_break;
2102 i := i+1;
2104 varval(i) := p_reg_req_num;
2105 i := i+1;
2106 varname(i) := 'SEQ_NUM_DMY';
2107 varval(i) := 'S_N='||p_reg_req_num;
2108 i := i+1;
2109
2110 -- Direct Deposit
2111
2112 varname(i) := 'IS_DIRECT_DEPOSIT';
2113 varval(i) := lv_is_direct_deposit;
2114 i := i+1;
2115 varname(i) := 'PAYMENT_TYPE_ID';
2116 varval(i) := p_dd_payment_type;
2117 i := i+1;
2118 varname(i) := 'PAYMENT_METHOD_ID';
2119 varval(i) := p_dd_payment_method;
2120 i := i+1;
2121 varname(i) := 'OVERRIDE_DD_DATE';
2122 varval(i) := p_dd_override_date;
2123 i := i+1;
2124 varname(i) := 'FINANCIAL_INSTITUTION_CODE';
2125 varval(i) := p_dd_financial_inst;
2126 i := i+1;
2127 varname(i) := 'FINANCIAL_INSTITUTION_HIDDEN';
2128 varval(i) := 'MAGTAPE_REPORT_ID='||p_dd_financial_inst;
2129 i := i+1;
2130 varname(i) := 'OVERRIDE_CPA_CODE';
2131 varval(i) := p_dd_cpa_code;
2132 i := i+1;
2133 varname(i) := 'OVERRIDE_FILE_CREATION_NUMBER';
2134 varval(i) := p_dd_file_number;
2135 i := i+1;
2136 varname(i) := 'OVERRIDE_FCN';
2137 varval(i) := 'FILE_CREATION_NUMBER_OVERRIDE='||p_dd_file_number;
2138 i := i+1;
2139 varname(i) := 'FILE_CREATION_DATE';
2140 varval(i) := '|FILE_CREATION_DATE='||p_session_date||'|';
2141 i := i+1;
2142
2143
2144 -- Canadian Payroll Archiver
2145
2146 varname(i) := 'TRANSFER_PAYROLL_ARCHIVER';
2147 varval(i) := 'TRANSFER_PAYROLL_ID='||p_payroll_id;
2148 i := i+1;
2149 varname(i) := 'TRANSFER_CONC_SET_ARCHIVER';
2150 varval(i) := 'TRANSFER_CONSOLIDATION_SET_ID='||p_consolidation_set_id;
2151 i := i+1;
2152
2153
2154 -- Canadian Chequewriter
2155
2156 varname(i) := 'IS_CHEQUE_WRITER';
2157 varval(i) := lv_is_chequewriter;
2158 i := i+1;
2159 varname(i) := 'CHEQUE_PAYMENT_TYPE_ID';
2160 varval(i) := p_cheque_payment_type;
2161 i := i+1;
2162 varname(i) := 'CHEQUE_PAYMENT_TYPE_NAME';
2163 varval(i) := lv_cheque_payment_type;
2164 i := i+1;
2165 varname(i) := 'CHEQUE_PAYMENT_METHOD_ID';
2166 varval(i) := p_cheque_payment_method;
2167 i := i+1;
2168 varname(i) := 'CHEQUE_PAYMENT_METHOD_NAME';
2169 varval(i) := lv_cheque_payment_method;
2170 i := i+1;
2171 varname(i) := 'CHEQUE_SORT_SEQUENCE';
2172 varval(i) := p_cheque_sort_sequence;
2173 i := i+1;
2174 varname(i) := 'CHEQUE_SORT_SEQUENCE_MEANING';
2175 varval(i) := lv_cheque_sort_sequence;
2176 i := i+1;
2177 varname(i) := 'CHEQUE_STYLE';
2178 varval(i) := p_cheque_style;
2179 i := i+1;
2180 varname(i) := 'CHEQUE_STYLE_MEANING';
2181 varval(i) := lv_cheque_style;
2182 i := i+1;
2183 varname(i) := 'CHEQUE_START_NUMBER';
2184 varval(i) := p_start_cheque_number;
2185 i := i+1;
2186 varname(i) := 'CHEQUE_END_NUMBER';
2187 varval(i) := p_end_cheque_number;
2188 i := i+1;
2189
2190 -- Deposit Advice
2191
2192 varname(i) := 'IS_DEPOSIT_ADVICE';
2193 varval(i) := lv_is_deposit_advice;
2194 i := i+1;
2195 varname(i) := 'ADVICE_REPORT_CATEGORY';
2196 varval(i) := p_da_data_type;
2197 i := i+1;
2198 varname(i) := 'ADVICE_REPORT_CATEGORY_NAME';
2199 varval(i) := lv_da_data_type;
2200 i := i+1;
2201 varname(i) := 'ADVICE_PAYROLL';
2202 varval(i) := 'PAYROLL_ID='||p_payroll_id;
2203 i := i+1;
2204 varname(i) := 'ADVICE_CONSOLIDATION_SET';
2205 varval(i) := 'CONSOLIDATION_SET_ID='||p_consolidation_set_id;
2206 i := i+1;
2207 varname(i) := 'ADVICE_START_DATE_DMY';
2208 varval(i) := 'START_DATE='||p_date_paid;
2209 i := i+1;
2210 varname(i) := 'ADVICE_END_DATE_DMY';
2211 varval(i) := 'END_DATE='||p_date_paid;
2212 i := i+1;
2213 varname(i) := 'ADVICE_ASG_SET_DMY';
2214 varval(i) := 'ASG_SET_ID='||p_payroll_assignment_set;
2215 i := i+1;
2216
2217 -- Payment Report
2218
2219 varname(i) := 'PAYMENT_PAYMENT_TYPE_ID';
2220 varval(i) := p_payment_rep_payment_type;
2221 i := i+1;
2222 varname(i) := 'PAYMENT_PAYMENT_METHOD_ID';
2223 varval(i) := p_payment_rep_payment_method;
2224 i := i+1;
2225
2226 -- ROE
2227
2228 varname(i) := 'IS_ROE';
2229 varval(i) := lv_is_roe;
2230 i := i+1;
2231 varname(i) := 'ROE_ASG_SET';
2232 varval(i) := p_roe_assignment_set;
2233 i := i+1;
2234 varname(i) := 'ROE_ASG_SET_NAME';
2235 varval(i) := lv_roe_asg_set;
2236 i := i+1;
2237
2238 -- ROE Worksheet
2239
2240 varname(i) := 'ROE_WORKSHEET_PERSON';
2241 varval(i) := p_roe_worksheet_person;
2242 i := i+1;
2243 varname(i) := 'ROE_WORKSHEET_PERSON_NAME';
2244 varval(i) := lv_roe_worksheet_person;
2245 i := i+1;
2246
2247 -- ROE Magnetic Media
2248
2249 varname(i) := 'IS_ROE_MAG';
2250 varval(i) := lv_is_roe_mag;
2251 i := i+1;
2252 varname(i) := 'ROE_MAG_PERSON';
2253 varval(i) := p_roe_mag_media_person;
2254 i := i+1;
2258 varname(i) := 'ROE_TYPE';
2255 varname(i) := 'ROE_MAG_PERSON_NAME';
2256 varval(i) := lv_roe_mag_media_person;
2257 i := i+1;
2259 varval(i) := p_roe_mag_roe_type;
2260 i := i+1;
2261 varname(i) := 'ROE_TYPE_HIDDEN';
2262 varval(i) := 'ROE_TYPE='||p_roe_mag_roe_type;
2263 i := i+1;
2264
2265 -- Costing
2266
2267 -- Costing Summary
2268
2269 varname(i) := 'IS_COSTING_SUMMARY';
2270 varval(i) := lv_is_costing_summary;
2271 i := i+1;
2272 varname(i) := 'SUMMARY_COSTING_ACCRUALS';
2273 varval(i) := p_cost_summary_accruals;
2274 i := i+1;
2275 varname(i) := 'SUMMARY_COSTING_ACCRUALS_NAME';
2276 varval(i) := lv_cost_summary_accruals;
2277 i := i+1;
2278 varname(i) := 'SUMMARY_COSTING_FILE_FORMAT';
2279 varval(i) := p_cost_summary_file_out;
2280 i := i+1;
2281 varname(i) := 'SUMMARY_COSTING_FILE_FMT_NAME';
2282 varval(i) := lv_cost_summary_file_out;
2283 i := i+1;
2284
2285 -- Costing Detail
2286
2287 varname(i) := 'DETAIL_COSTING_CRITERION';
2288 varval(i) := p_cost_detail_selection;
2289 i := i+1;
2290 varname(i) := 'DETAIL_COSTING_IS_ELEMENT_SET';
2291 varval(i) := p_cost_detail_is_ele_set;
2292 i := i+1;
2293 varname(i) := 'DETAIL_COSTING_ELEMENT_SET';
2294 varval(i) := p_cost_detail_ele_set;
2295 i := i+1;
2296 varname(i) := 'DETAIL_COSTING_ELE_SET_NAME';
2297 varval(i) := lv_cost_detail_ele_set;
2298 i := i+1;
2299 varname(i) := 'DETAIL_COSTING_IS_ELEMENT_C';
2300 varval(i) := p_cost_detail_is_class;
2301 i := i+1;
2302 varname(i) := 'DETAIL_COSTING_ELEMENT_C';
2303 varval(i) := p_cost_detail_class;
2304 i := i+1;
2305 varname(i) := 'DETAIL_COSTING_ELEMENT_C_NAME';
2306 varval(i) := lv_cost_detail_ele_class;
2307 i := i+1;
2308 varname(i) := 'DETAIL_COSTING_IS_ELEMENT';
2309 varval(i) := p_cost_detail_is_element;
2310 i := i+1;
2311 varname(i) := 'DETAIL_COSTING_ELEMENT';
2312 varval(i) := p_cost_detail_element;
2313 i := i+1;
2314 varname(i) := 'DETAIL_COSTING_ELEMENT_NAME';
2315 varval(i) := lv_cost_detail_ele_name;
2316 i := i+1;
2317 varname(i) := 'DETAIL_COSTING_ASG_SET';
2318 varval(i) := p_cost_detail_asg_set;
2319 i := i+1;
2320 varname(i) := 'DETAIL_COSTING_ASG_SET_NAME';
2321 varval(i) := lv_cost_detail_asg_set;
2322 i := i+1;
2323 varname(i) := 'DETAIL_COSTING_ACCRUALS';
2324 varval(i) := p_cost_detail_accruals;
2325 i := i+1;
2326 varname(i) := 'DETAIL_COSTING_ACCRUALS_NAME';
2327 varval(i) := lv_cost_detail_accruals;
2328 i := i+1;
2329
2330
2331 -- Notification Attributes
2332
2333 varname(i) := 'PROCESS_LIST_SUBJECT';
2334 varval(i) := lv_process_list_subject;
2335 i := i+1;
2336 varname(i) := 'PROCESS_LIST_TEXT';
2337 varval(i) := lv_process_list_text;
2338 i := i+1;
2339 varname(i) := 'PROCESS_LIST_HTML1';
2340 varval(i) := lv_process_list_html_1;
2341 i := i+1;
2342 varname(i) := 'PROCESS_LIST_HTML2';
2343 varval(i) := lv_process_list_html_2;
2344 i := i+1;
2345
2346 -- print out set values for attributes
2347
2348 for j in varname.first..varname.last loop
2349 hr_utility.trace( 'varname '|| j ||' = '||varname(j));
2350 hr_utility.trace( 'varval '|| j ||' = '||varval(j));
2351 end loop;
2352
2353
2354 wf_engine.SetItemAttrTextArray(ItemType, ItemKey, varname, varval);
2355 hr_utility.trace( 'Total Count = '||to_char(varname.COUNT));
2356
2357 for k in num_varname.first..num_varname.last loop
2358 hr_utility.trace( 'num_varname '|| k ||' = '||num_varname(k));
2359 hr_utility.trace( 'num_varval '|| k ||' = '||num_varvalue(k));
2360 end loop;
2361
2362
2363 wf_engine.SetItemAttrNumberArray(ItemType, ItemKey, num_varname, num_varvalue);
2364 hr_utility.trace( 'Total Num Count = '||to_char(num_varname.COUNT));
2365
2366 exception
2367 when OTHERS then
2368 hr_utility.trace('varname exception: OTHERS of TextArray');
2369 raise;
2370 end;
2371
2372 hr_utility.trace('Before StartProcess');
2373
2374 wf_engine.StartProcess (ItemType => ItemType,
2375 ItemKey => ItemKey );
2376
2377 hr_utility.trace('After StartProcess');
2378
2379 hr_utility.set_location('Leaving: ' || l_proc, 100);
2380
2381
2382 exception
2383 when others then
2384 wf_core.Context('pay_ca_wf_pkg',
2385 'StartProcess',
2386 ItemType,
2387 RequestorUsername,
2388 ProcessOwner,
2389 Workflowprocess);
2390
2391 error;
2392 RAISE;
2393
2394 end StartProcess;
2395
2396 function get_notifier(p_payroll_id in number,
2397 p_gre_id in number,
2398 p_effective_date in varchar2) return varchar2 is
2399
2400 l_proc varchar2(80) := gv_package||'.get_notifier';
2401 lv_contact_source varchar2(50);
2402 lv_contact_user_name varchar2(150);
2403
2404 -- get the payroll contact
2405
2406 cursor c_payroll_contact is
2407 select prl_information7
2408 from pay_payrolls_f
2409 where payroll_id = p_payroll_id
2410 and prl_information_category = 'CA'
2411 and fnd_date.canonical_to_date(p_effective_date) between
2412 effective_start_date and effective_end_date;
2413
2414 -- get the GRE contact
2415
2416 cursor c_gre_contact is
2417 select org_information1
2418 from hr_organization_information
2419 where org_information_context || '' = 'Contact Information'
2420 and organization_id = p_gre_id;
2421
2422 begin
2423
2424 hr_utility.set_location('Starting: ' || l_proc, 100);
2425
2426 lv_contact_source := fnd_profile.value('HR_PAYROLL_CONTACT_SOURCE');
2427
2428 hr_utility.trace('Profile Value : '|| lv_contact_source);
2429 hr_utility.trace('Payroll Id : '|| p_payroll_id);
2430 hr_utility.trace('GRE Id : '|| p_gre_id);
2431 hr_utility.trace('Effective Date : '|| p_effective_date);
2432
2433
2434 if lv_contact_source = 'PAYROLL' then
2435
2436 open c_payroll_contact;
2437 fetch c_payroll_contact into lv_contact_user_name;
2438
2439 hr_utility.trace('Contact User : '|| lv_contact_user_name);
2440
2441 if c_payroll_contact%NOTFOUND then
2442 lv_contact_user_name := 'SYSADMIN';
2443 end if;
2444
2445 close c_payroll_contact;
2446
2447
2448 elsif lv_contact_source = 'GRE' then
2449
2450 open c_gre_contact;
2451 fetch c_gre_contact into lv_contact_user_name;
2452
2453 if c_gre_contact%NOTFOUND then
2454 lv_contact_user_name := 'SYSADMIN';
2455 end if;
2456
2457 close c_gre_contact;
2458
2459 elsif lv_contact_source = 'CUSTOM' then
2460 lv_contact_user_name := 'SYSADMIN';
2461
2462 else
2463 -- some other source we don't understand yet
2464 lv_contact_user_name := 'SYSADMIN';
2465 end if;
2466
2467 return lv_contact_user_name;
2468
2469 hr_utility.set_location('Leaving: ' || l_proc, 100);
2470
2471 end get_notifier;
2472
2473
2474 procedure error is
2475 begin
2476
2477 hr_utility.trace('Run time error in test script');
2478 hr_utility.trace('Sqlerror: '||sqlerrm);
2479 hr_utility.trace('Errname: '||wf_core.error_name);
2480 hr_utility.trace('Errmsg: '||substr(wf_core.error_message, 1, 200));
2481 hr_utility.trace('Stack1: '||substr(wf_core.error_stack, 1, 200));
2482 hr_utility.trace('Stack2: '||substr(wf_core.error_stack, 200, 200));
2483 hr_utility.trace('Stack3: '||substr(wf_core.error_stack, 400, 200));
2484 hr_utility.trace('Stack4: '||substr(wf_core.error_stack, 600, 200));
2485 hr_utility.trace('Stack5: '||substr(wf_core.error_stack, 800, 200));
2486 hr_utility.trace('Stack6: '||substr(wf_core.error_stack, 1000, 200));
2487 hr_utility.trace('Stack7: '||substr(wf_core.error_stack, 1200, 200));
2488 hr_utility.trace('Stack8: '||substr(wf_core.error_stack, 1400, 200));
2489 hr_utility.trace('Stack9: '||substr(wf_core.error_stack, 1600, 200));
2490 hr_utility.trace('Stack10: '||substr(wf_core.error_stack, 1800, 200));
2491
2492 end error;
2493
2494
2495 end pay_ca_wf_pkg;