[Home] [Help]
PACKAGE BODY: APPS.PAY_WF_PKG
Source
1 PACKAGE BODY PAY_WF_PKG AS
2 /* $Header: payuswfpkg.pkb 120.5 2011/07/20 15:28:33 rgottipa 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_WF_PKG
21 Package File Name : payuswfpkg.pkb
22 Description : This package declares functions which are ....
23
24 Change List
25 -----------
26 Date Name Vers Bug No Description
27 ----------- ---------- ------ ------- --------------------------
28 08-JUN-2003 jgoswami 115.0 Created
29 19-JUN-2003 jgoswami 115.1 3006871 Created New Item attributes.
30 3006753 Modified values passed to
31 message text based on SRS input
32 17-JUL-2003 jgoswami 115.3 3054384 Added Item Attributes for
36 HTML Message as Retro Notification
33 processes and retro notification.
34 17-JUL-2003 jgoswami 115.4 Made GSCC Compliant
35 18-JUL-2003 jgoswami 115.5 Modified the IF Conditions for
37 was not displayed even if the
38 parameters are entered.
39 04-AUG-2003 jgoswami 115.6 3079094 Added parameter to check the
40 required parameters are available
41 to execute the Element Register
42 Report.
43 26-AUG-2003 jgoswami 115.7 modified parameter sequence to
44 corelate with the SRS definition.
45 09-DEC-2003 jgoswami 115.8 3310302 modified condition to check for
46 BEE entered or skiped.
47 12-APR-2004 jgoswami 115.9 3316422 Modified to pass correct Dates for
48 all processes. Added itemattribute
49 required for the workflow. Added
50 functionality to wait for user
51 response in notification based on
52 PAYROLL_WF_NOTIFY_ACTION value
53 WAIT or value is NULL -Default wait
54 NOWAIT - Response not required for
55 any Notification.
56 PAYMENTWAIT - Response required for
57 only payment notifications
58 04-MAY-2004 jgoswami 115.10 3316422 Added Itemattribute to pas value to
59 Notification.
60 10-OCT-2005 jgoswami 115.6 4538713 modified procedure,added parameter
61 for check_writer and Costing Report
62 24-DEC-2009 mikarthi 115.12 9211154 l_profile_sec_grp_id variable was
63 always holding NULL value
64 23-JUN-2011 rgottipa 115.13 12594530 Override the procedures payroll_wf_process
65 and StartProcess
66 19-JUL-2011 rgottipa 115.14 12769566 Added parameters to overloaded procedures
67 payroll_wf_process and StartProcess
68 to call Retro-Notification (enhanced) PDF
69 and RetroPay (enhanced)
70 20-JUL-2011 rgottipa 115.15 12769566 RETRO_START_DATE_DUMMY value changed
71
72 *******************************************************************/
73
74
75
76 procedure payroll_wf_process(errbuf OUT nocopy VARCHAR2,
77 retcode OUT nocopy NUMBER,
78 p_wf_item_type Varchar2,
79 p_business_group_id number ,
80 p_batch_id number ,
81 p_payroll_id varchar2,
82 p_payroll_id_dummy varchar2 ,
83 p_consolidation_set_id varchar2,
84 p_consolidation_set_id_dummy varchar2 ,
85 p_date_earned varchar2,
86 p_date_paid varchar2,
87 p_period varchar2,
88 p_payroll_element_set_name varchar2,
89 p_payroll_assignment_set_name varchar2,
90 p_payroll_run_type varchar2,
91 p_event_group varchar2,
92 p_retro_assignment_set_name varchar2,
93 p_retro_assignment_set_dummy varchar2,
94 p_retro_element_set varchar2,
95 p_retro_start_date varchar2,
96 p_effective_date varchar2,
97 p_gre varchar2,
98 p_gre_id_dummy varchar2,
99 p_p_payroll_action_id varchar2,
100 p_organization varchar2,
101 p_location varchar2,
102 p_legislation_code varchar2,
103 p_select_report_or_group varchar2,
104 p_is_exception_group varchar2,
105 p_exception_group_name varchar2,
106 p_exception_group_name_dummy varchar2,
107 p_is_exception_report varchar2,
108 p_exception_report_name varchar2,
109 p_exception_report_name_dummy varchar2,
110 p_over_ride_variance_type varchar2,
111 p_over_ride_varianc_type_dumy varchar2,
112 p_over_ride_variance_value varchar2,
113 p_over_ride_varinc_value_dumy varchar2,
114 p_payment_method_override varchar2,
115 p_nacha_payment_type varchar2,
116 p_nacha_payment_method varchar2,
117 p_deposit_date_override varchar2,
118 p_file_id_modifier varchar2,
119 p_file_id_modifier_check varchar2,
120 p_thrid_party_check varchar2,
121 p_check_writer_payment_type varchar2,
122 p_check_style varchar2,
126 p_end_check_number varchar2,
123 p_check_writer_payment_method varchar2,
124 p_check_writer_sort_sequence varchar2,
125 p_start_check_number varchar2,
127 p_overriding_cheque_date varchar2,
128 p_payment_method_3rd_party varchar2,
129 p_sort_sequence_3rd_party varchar2,
130 p_start_check_num_3rd_party varchar2,
131 p_end_check_num_3rd_party varchar2,
132 p_da_report_category varchar2,
133 p_da_sort_sequence varchar2,
134 p_da_assignment_set varchar2,
135 p_assignment_set_dummy varchar2,
136 p_selection_criterion varchar2,
137 p_is_element_set varchar2,
138 p_ele_reg_element_set varchar2,
139 p_is_element_classification varchar2,
140 p_element_classification varchar2,
141 p_is_element_name varchar2,
142 p_element varchar2,
143 p_ele_reg_employee varchar2,
144 p_costing_process varchar2,
145 p_dummy_cost_run varchar2,
146 p_cost_summary_accruals varchar2,
147 p_cost_summary_file_out varchar2,
148 p_cost_detail_selection varchar2,
149 p_cost_detail_is_ele_set varchar2,
150 p_cost_detail_ele_set varchar2,
151 p_cost_detail_is_class varchar2,
152 p_cost_detail_class varchar2,
153 p_cost_detail_is_element varchar2,
154 p_cost_detail_element varchar2,
155 p_cost_detail_asg_set varchar2,
156 p_cost_detail_accruals varchar2,
157 p_start_date varchar2,
158 p_end_date varchar2,
159 p_ppa_finder varchar2,
160 p_ppa_finder_pqp varchar2
161 ) is
162
163 l_valid_status varchar2(5);
164 l_program varchar2(100);
165 l_workflowprocess varchar2(100);
166 l_ProcessDesc varchar2(100);
167 l_RequestorUsername Varchar2(100);
168 l_ProcessOwner Varchar2(100);
169 l_item_type Varchar2(100);
170 l_item_key varchar2(100);
171 lv_runnable_process Varchar2(1);
172
173 --
174 --
175 begin
176 --hr_utility.trace_on(null,'PYWF');
177 gv_package := 'pay_wf_pkg';
178 l_RequestorUsername := 'SYSADMIN';
179
180 select to_char(sysdate,'DDHH24MISS') into l_item_key from dual;
181
182 -- initialise variable - 0 is SRS Success, 1 is SRS Warning, 2 is SRS Error
183 retcode := 0;
184
185
186 l_workflowprocess := 'PAYUSPROCESSWF';
187 l_item_type := p_wf_item_type;
188
189 BEGIN
190 select RUNNABLE_FLAG
191 into lv_runnable_process
192 from wf_activities
193 where item_type = p_wf_item_type
194 and type = 'PROCESS'
195 and name = l_workflowprocess
196 and end_date is null;
197
198 Exception when others then
199 hr_utility.trace('In exception: OTHERS at payroll_wf_process');
200 null;
201
202 END;
203
204
205
206 hr_utility.trace('Item Type is : '|| l_item_type);
207 hr_utility.trace('Item Key is : '|| l_item_key);
208 hr_utility.trace('Workflow Process is : '|| l_workflowprocess);
209
210 if lv_runnable_process = 'Y' then
211
212 StartProcess( p_business_group_id,
213 p_batch_id ,
214 p_payroll_id ,
215 p_payroll_id_dummy ,
216 p_consolidation_set_id ,
217 p_consolidation_set_id_dummy ,
218 p_date_earned ,
219 p_date_paid ,
220 p_period ,
221 p_payroll_element_set_name ,
222 p_payroll_assignment_set_name ,
223 p_payroll_run_type ,
224 p_event_group ,
225 p_retro_assignment_set_name ,
226 p_retro_assignment_set_dummy ,
227 p_retro_element_set ,
228 p_retro_start_date ,
229 p_effective_date ,
230 p_gre ,
231 p_gre_id_dummy ,
232 p_p_payroll_action_id ,
233 p_organization ,
234 p_location ,
235 p_legislation_code ,
236 p_select_report_or_group ,
237 p_is_exception_group ,
238 p_exception_group_name ,
239 p_exception_group_name_dummy ,
240 p_is_exception_report ,
241 p_exception_report_name ,
245 p_over_ride_variance_value ,
242 p_exception_report_name_dummy ,
243 p_over_ride_variance_type ,
244 p_over_ride_varianc_type_dumy ,
246 p_over_ride_varinc_value_dumy ,
247 p_payment_method_override ,
248 p_nacha_payment_type ,
249 p_nacha_payment_method ,
250 p_deposit_date_override ,
251 p_file_id_modifier ,
252 p_file_id_modifier_check ,
253 p_thrid_party_check ,
254 p_check_writer_payment_type ,
255 p_check_style ,
256 p_check_writer_payment_method ,
257 p_check_writer_sort_sequence ,
258 p_start_check_number ,
259 p_end_check_number ,
260 p_overriding_cheque_date ,
261 p_payment_method_3rd_party ,
262 p_sort_sequence_3rd_party ,
263 p_start_check_num_3rd_party ,
264 p_end_check_num_3rd_party ,
265 p_da_report_category ,
266 p_da_sort_sequence ,
267 p_da_assignment_set ,
268 p_assignment_set_dummy ,
269 p_selection_criterion ,
270 p_is_element_set ,
271 p_ele_reg_element_set ,
272 p_is_element_classification ,
273 p_element_classification ,
274 p_is_element_name ,
275 p_element ,
276 p_ele_reg_employee ,
277 p_costing_process ,
278 p_dummy_cost_run ,
279 p_cost_summary_accruals ,
280 p_cost_summary_file_out ,
281 p_cost_detail_selection ,
282 p_cost_detail_is_ele_set ,
283 p_cost_detail_ele_set ,
284 p_cost_detail_is_class ,
285 p_cost_detail_class ,
286 p_cost_detail_is_element ,
287 p_cost_detail_element ,
288 p_cost_detail_asg_set ,
289 p_cost_detail_accruals ,
290 p_start_date ,
291 p_end_date ,
292 p_ppa_finder ,
293 p_ppa_finder_pqp ,
294 l_ProcessDesc,
295 l_RequestorUsername,
296 l_ProcessOwner,
297 l_workflowprocess,
298 l_item_type,
299 l_item_key
300 );
301
302 end if;
303
304 EXCEPTION
305 --
306 WHEN hr_utility.hr_error THEN
307 --
308 -- Set up error message and error return code.
309 --
310 --hr_utility.trace('in the exception');
311 errbuf := hr_utility.get_message;
312 retcode := 2;
313 --
314 --
315 WHEN others THEN
316 --
317 -- Set up error message and return code.
318 errbuf := sqlerrm;
319 retcode := 2;
320 end payroll_wf_process;
321
322
323
324
325 procedure payroll_wf_process(errbuf OUT nocopy VARCHAR2,
326 retcode OUT nocopy NUMBER,
327 p_wf_item_type Varchar2,
328 p_business_group_id number ,
329 p_batch_id number ,
330 p_payroll_id varchar2,
331 p_payroll_id_dummy varchar2 ,
332 p_consolidation_set_id varchar2,
333 p_consolidation_set_id_dummy varchar2 ,
334 p_date_earned varchar2,
335 p_date_paid varchar2,
336 p_period varchar2,
337 p_payroll_element_set_name varchar2,
338 p_payroll_assignment_set_name varchar2,
339 p_payroll_run_type varchar2,
340 p_event_group varchar2,
341 p_retro_valid_template_list varchar2,--bug 12704242
342 p_retro_template_name varchar2,--bug 12704242
343 p_retro_assignment_set_name varchar2,
344 p_retro_generate_report varchar2,--bug 12704242
345 p_retro_assignment_set_dummy varchar2,
346 p_retro_element_set varchar2,
347 p_retro_start_date varchar2,
348 p_retropay_entry_creation_date varchar2, --bug 12704242
349 p_effective_date varchar2,
350 p_gre varchar2,
351 p_gre_id_dummy varchar2,
352 p_p_payroll_action_id varchar2,
353 p_organization varchar2,
354 p_location varchar2,
355 p_legislation_code varchar2,
356 p_select_report_or_group varchar2,
357 p_is_exception_group varchar2,
358 p_exception_group_name varchar2,
362 p_exception_report_name_dummy varchar2,
359 p_exception_group_name_dummy varchar2,
360 p_is_exception_report varchar2,
361 p_exception_report_name varchar2,
363 p_over_ride_variance_type varchar2,
364 p_over_ride_varianc_type_dumy varchar2,
365 p_over_ride_variance_value varchar2,
366 p_over_ride_varinc_value_dumy varchar2,
367 p_payment_method_override varchar2,
368 p_nacha_payment_type varchar2,
369 p_nacha_payment_method varchar2,
370 p_deposit_date_override varchar2,
371 p_file_id_modifier varchar2,
372 p_file_id_modifier_check varchar2,
373 p_thrid_party_check varchar2,
374 p_check_writer_payment_type varchar2,
375 p_check_style varchar2,
376 p_check_writer_payment_method varchar2,
377 p_check_writer_sort_sequence varchar2,
378 p_start_check_number varchar2,
379 p_end_check_number varchar2,
380 p_overriding_cheque_date varchar2,
381 p_payment_method_3rd_party varchar2,
382 p_sort_sequence_3rd_party varchar2,
383 p_start_check_num_3rd_party varchar2,
384 p_end_check_num_3rd_party varchar2,
385 p_da_report_category varchar2,
386 p_da_sort_sequence varchar2,
387 p_da_assignment_set varchar2,
388 p_assignment_set_dummy varchar2,
389 p_selection_criterion varchar2,
390 p_is_element_set varchar2,
391 p_ele_reg_element_set varchar2,
392 p_is_element_classification varchar2,
393 p_element_classification varchar2,
394 p_is_element_name varchar2,
395 p_element varchar2,
396 p_ele_reg_employee varchar2,
397 p_costing_process varchar2,
398 p_dummy_cost_run 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_start_date varchar2,
411 p_end_date varchar2,
412 p_ppa_finder varchar2,
413 p_ppa_finder_pqp varchar2,
414 p_action_parameter_group varchar2 -- bug 12594530
415 ) is
416
417 l_valid_status varchar2(5);
418 l_program varchar2(100);
419 l_workflowprocess varchar2(100);
420 l_ProcessDesc varchar2(100);
421 l_RequestorUsername Varchar2(100);
422 l_ProcessOwner Varchar2(100);
423 l_item_type Varchar2(100);
424 l_item_key varchar2(100);
425 lv_runnable_process Varchar2(1);
426
427 --
428 --
429 begin
430 --hr_utility.trace_on(null,'PYWF');
431 gv_package := 'pay_wf_pkg';
432 l_RequestorUsername := 'SYSADMIN';
433
434 select to_char(sysdate,'DDHH24MISS') into l_item_key from dual;
435
436 -- initialise variable - 0 is SRS Success, 1 is SRS Warning, 2 is SRS Error
437 retcode := 0;
438
439
440 l_workflowprocess := 'PAYUSPROCESSWF';
441 l_item_type := p_wf_item_type;
442
443 BEGIN
444 select RUNNABLE_FLAG
445 into lv_runnable_process
446 from wf_activities
447 where item_type = p_wf_item_type
448 and type = 'PROCESS'
449 and name = l_workflowprocess
450 and end_date is null;
451
452 Exception when others then
453 hr_utility.trace('In exception: OTHERS at payroll_wf_process');
454 null;
455
456 END;
457
458
459
460 hr_utility.trace('Item Type is : '|| l_item_type);
461 hr_utility.trace('Item Key is : '|| l_item_key);
462 hr_utility.trace('Workflow Process is : '|| l_workflowprocess);
463
464 if lv_runnable_process = 'Y' then
465
466 StartProcess( p_business_group_id,
467 p_batch_id ,
468 p_payroll_id ,
469 p_payroll_id_dummy ,
470 p_consolidation_set_id ,
471 p_consolidation_set_id_dummy ,
472 p_date_earned ,
473 p_date_paid ,
474 p_period ,
475 p_payroll_element_set_name ,
476 p_payroll_assignment_set_name ,
477 p_payroll_run_type ,
478 p_event_group ,
482 p_retro_generate_report,--bug 12704242
479 p_retro_valid_template_list,--bug 12704242
480 p_retro_template_name,--bug 12704242
481 p_retro_assignment_set_name ,
483 p_retro_assignment_set_dummy ,
484 p_retro_element_set ,
485 p_retro_start_date ,
486 p_retropay_entry_creation_date, --bug 12704242
487 p_effective_date ,
488 p_gre ,
489 p_gre_id_dummy ,
490 p_p_payroll_action_id ,
491 p_organization ,
492 p_location ,
493 p_legislation_code ,
494 p_select_report_or_group ,
495 p_is_exception_group ,
496 p_exception_group_name ,
497 p_exception_group_name_dummy ,
498 p_is_exception_report ,
499 p_exception_report_name ,
500 p_exception_report_name_dummy ,
501 p_over_ride_variance_type ,
502 p_over_ride_varianc_type_dumy ,
503 p_over_ride_variance_value ,
504 p_over_ride_varinc_value_dumy ,
505 p_payment_method_override ,
506 p_nacha_payment_type ,
507 p_nacha_payment_method ,
508 p_deposit_date_override ,
509 p_file_id_modifier ,
510 p_file_id_modifier_check ,
511 p_thrid_party_check ,
512 p_check_writer_payment_type ,
513 p_check_style ,
514 p_check_writer_payment_method ,
515 p_check_writer_sort_sequence ,
516 p_start_check_number ,
517 p_end_check_number ,
518 p_overriding_cheque_date ,
519 p_payment_method_3rd_party ,
520 p_sort_sequence_3rd_party ,
521 p_start_check_num_3rd_party ,
522 p_end_check_num_3rd_party ,
523 p_da_report_category ,
524 p_da_sort_sequence ,
525 p_da_assignment_set ,
526 p_assignment_set_dummy ,
527 p_selection_criterion ,
528 p_is_element_set ,
529 p_ele_reg_element_set ,
530 p_is_element_classification ,
531 p_element_classification ,
532 p_is_element_name ,
533 p_element ,
534 p_ele_reg_employee ,
535 p_costing_process ,
536 p_dummy_cost_run ,
537 p_cost_summary_accruals ,
538 p_cost_summary_file_out ,
539 p_cost_detail_selection ,
540 p_cost_detail_is_ele_set ,
541 p_cost_detail_ele_set ,
542 p_cost_detail_is_class ,
543 p_cost_detail_class ,
544 p_cost_detail_is_element ,
545 p_cost_detail_element ,
546 p_cost_detail_asg_set ,
547 p_cost_detail_accruals ,
548 p_start_date ,
549 p_end_date ,
550 p_ppa_finder ,
551 p_ppa_finder_pqp ,
552 p_action_parameter_group, -- bug 12594530
553 l_ProcessDesc,
554 l_RequestorUsername,
555 l_ProcessOwner,
556 l_workflowprocess,
557 l_item_type,
558 l_item_key
559 );
560
561 end if;
562
563 EXCEPTION
564 --
565 WHEN hr_utility.hr_error THEN
566 --
567 -- Set up error message and error return code.
568 --
569 --hr_utility.trace('in the exception');
570 errbuf := hr_utility.get_message;
571 retcode := 2;
572 --
573 --
574 WHEN others THEN
575 --
576 -- Set up error message and return code.
577 errbuf := sqlerrm;
578 retcode := 2;
579 end payroll_wf_process;
580
581
582 --
583 -- Start Workflow Process will Create a Process and Set the Attributes
584 -- for the Workflow Process.
585
586
587 procedure StartProcess (
588 p_business_group_id number ,
589 p_batch_id number ,
590 p_payroll_id varchar2,
591 p_payroll_id_dummy varchar2 ,
592 p_consolidation_set_id varchar2,
593 p_consolidation_set_id_dummy varchar2 ,
594 p_date_earned varchar2,
595 p_date_paid varchar2,
596 p_period varchar2,
597 p_payroll_element_set_name varchar2,
601 p_retro_assignment_set_name varchar2,
598 p_payroll_assignment_set_name varchar2,
599 p_payroll_run_type varchar2,
600 p_event_group varchar2,
602 p_retro_assignment_set_dummy varchar2,
603 p_retro_element_set varchar2,
604 p_retro_start_date varchar2,
605 p_effective_date varchar2,
606 p_gre varchar2,
607 p_gre_id_dummy varchar2,
608 p_p_payroll_action_id varchar2,
609 p_organization varchar2,
610 p_location varchar2,
611 p_legislation_code varchar2,
612 p_select_report_or_group varchar2,
613 p_is_exception_group varchar2,
614 p_exception_group_name varchar2,
615 p_exception_group_name_dummy varchar2,
616 p_is_exception_report varchar2,
617 p_exception_report_name varchar2,
618 p_exception_report_name_dummy varchar2,
619 p_over_ride_variance_type varchar2,
620 p_over_ride_varianc_type_dumy varchar2,
621 p_over_ride_variance_value varchar2,
622 p_over_ride_varinc_value_dumy varchar2,
623 p_payment_method_override varchar2,
624 p_nacha_payment_type varchar2,
625 p_nacha_payment_method varchar2,
626 p_deposit_date_override varchar2,
627 p_file_id_modifier varchar2,
628 p_file_id_modifier_check varchar2,
629 p_thrid_party_check varchar2,
630 p_check_writer_payment_type varchar2,
631 p_check_style varchar2,
632 p_check_writer_payment_method varchar2,
633 p_check_writer_sort_sequence varchar2,
634 p_start_check_number varchar2,
635 p_end_check_number varchar2,
636 p_overriding_cheque_date varchar2,
637 p_payment_method_3rd_party varchar2,
638 p_sort_sequence_3rd_party varchar2,
639 p_start_check_num_3rd_party varchar2,
640 p_end_check_num_3rd_party varchar2,
641 p_da_report_category varchar2,
642 p_da_sort_sequence varchar2,
643 p_da_assignment_set varchar2,
644 p_assignment_set_dummy varchar2,
645 p_selection_criterion varchar2,
646 p_is_element_set varchar2,
647 p_ele_reg_element_set varchar2,
648 p_is_element_classification varchar2,
649 p_element_classification varchar2,
650 p_is_element_name varchar2,
651 p_element varchar2,
652 p_ele_reg_employee varchar2,
653 p_costing_process varchar2,
654 p_dummy_cost_run varchar2,
655 p_cost_summary_accruals varchar2,
656 p_cost_summary_file_out varchar2,
657 p_cost_detail_selection varchar2,
658 p_cost_detail_is_ele_set varchar2,
659 p_cost_detail_ele_set varchar2,
660 p_cost_detail_is_class varchar2,
661 p_cost_detail_class varchar2,
662 p_cost_detail_is_element varchar2,
663 p_cost_detail_element varchar2,
664 p_cost_detail_asg_set varchar2,
665 p_cost_detail_accruals varchar2,
666 p_start_date varchar2,
667 p_end_date varchar2,
668 p_ppa_finder varchar2,
669 p_ppa_finder_pqp varchar2,
670 ProcessDesc varchar2,
671 RequestorUsername varchar2,
672 ProcessOwner in varchar2,
673 Workflowprocess in varchar2,
674 item_type in varchar2,
675 item_key in varchar2
676 ) is
677
678 ItemType varchar2(30);
679 ItemKey varchar2(30);
680 ItemUserKey varchar2(80);
681 l_business_group_id number(30);
682 l_payroll_id number(16);
683 l_consolidation_set_id number(16);
684 ln_asignment_id NUMBER(30);
685
686 l_message_subject1 varchar2(240);
687 l_message_text1 varchar2(3200) ;
688 l_message_html_text1 varchar2(3200) ;
689 l_message_html_text2 varchar2(3200) ;
690 l_pay_excep_rpt_message_text1 varchar2(3200) ;
691 l_pay_excep_rpt_message_html1 varchar2(3200) ;
692 l_ele_reg_rpt_message_text1 varchar2(3200) ;
693 l_ele_reg_rpt_message_html1 varchar2(3200) ;
694 l_current_user varchar2(40);
695 l_current_user_display_name varchar2(40);
696 l_app_user_name varchar2(40);
697 l_orig_system varchar2(40);
698 l_orig_system_id varchar2(40);
699 l_role_name varchar2(50);
703 lv_is_retro_ntfy varchar2(1);
700 l_role_display_name varchar2(50);
701 lv_currency_code varchar2(5);
702 lv_is_batch varchar2(1);
704 lv_is_nacha varchar2(1);
705 lv_is_check varchar2(1);
706 lv_is_third_party_check varchar2(1);
707 lv_is_deposit_advice varchar2(1);
708 lv_is_payroll_exception_report varchar2(1);
709 lv_is_ele_reg varchar2(1);
710 lv_isResponseRequired varchar2(1) ;
711 lv_isPaymentWait varchar2(1) ;
712 lv_Payroll_WF_Notify_Action Varchar2(30);
713 ln_nacha_payment_type_id Varchar2(30);
714 ln_check_payment_type_id Varchar2(30);
715 lv_da_report Varchar2(30);
716
717 lv_payroll_name varchar2(80);
718 lv_consolidation_set_name varchar2(80);
719 lv_gre_name varchar2(80);
720 lv_org_name varchar2(80);
721 lv_loc_name varchar2(80);
722 lv_pre_ovr_pymt_name varchar2(80);
723 lv_check_pymt_name varchar2(80);
724 lv_nacha_pymt_name varchar2(80);
725 lv_event_group_name varchar2(80);
726 lv_loc_name varchar2(80);
727 lv_exc_grp_name varchar2(80);
728 lv_grp_orv_type varchar2(80);
729 lv_grp_orv_value varchar2(80);
730 lv_exc_rep_name varchar2(80);
731 lv_orv_type varchar2(80);
732 lv_orv_value varchar2(80);
733 lv_check_sort_seq_meaning varchar2(80);
734 lv_period_name varchar2(80);
735 lv_element_name varchar2(80);
736 lv_batch_name varchar2(80);
737
738 --l_profile_per_sec_id VARCHAR2(100);
739 l_profile_per_sec_id Number(15);
740 l_profile_per_bg VARCHAR2(100);
741 --l_profile_sec_grp_id VARCHAR2(100);
742 l_profile_sec_grp_id Number(15);
743 l_profile_user_name VARCHAR2(100);
744 lv_prc_list varchar2(3200);
745 lv_prc_list_html varchar2(3200);
746 lv_date_time varchar2(80);
747 l_proc varchar2(80);
748 lv_contact_user_name VARCHAR2(80);
749 l_user_id NUMBER;
750 l_resp_id NUMBER;
751 l_resp_appl_id NUMBER;
752 l_org_id NUMBER;
753 l_per_sec_id NUMBER;
754 l_sec_grp_id NUMBER;
755 i_count number;
756
757 lv_is_costing_summary varchar2(1);
758 lv_is_costing_detail varchar2(1);
759 lv_cost_summary_accruals varchar2(80);
760 lv_cost_summary_file_out varchar2(80);
761 lv_cost_detail_ele_set varchar2(80);
762 lv_cost_detail_ele_class varchar2(80);
763 lv_cost_detail_ele_name varchar2(80);
764 lv_cost_detail_asg_set varchar2(80);
765 lv_cost_detail_accruals varchar2(80);
766
767 varname Wf_Engine.NameTabTyp;
768 varval Wf_Engine.TextTabTyp;
769
770 num_varname Wf_Engine.NameTabTyp;
771 num_varvalue Wf_Engine.NumTabTyp;
772
773 TYPE char80_table IS TABLE OF VARCHAR2(80)
774 INDEX BY BINARY_INTEGER;
775
776 lv_conc_prog_name char80_table;
777 lv_cur_process char80_table;
778 i number;
779 j number;
780 k number;
781 l number;
782 n number;
783 p number;
784
785
786 cursor cur_costing_summary_accruals is
787 select meaning
788 from hr_lookups
789 where lookup_code = p_cost_summary_accruals
790 and lookup_type = 'PAY_PAYRPCBR';
791
792 cursor cur_costing_summary_file_out is
793 select meaning
794 from fnd_common_lookups
795 where lookup_code = p_cost_summary_file_out
796 and lookup_type = 'REPORT_OUTPUT_TYPE'
797 and application_id = 801
798 and enabled_flag = 'Y';
799
800 cursor cur_costing_detail_ele_set is
801 select element_set_name
802 from pay_element_sets
803 where element_set_id = to_number(p_cost_detail_ele_set)
804 and element_set_type = 'C';
805
806 cursor cur_costing_detail_ele_class is
807 select classification_name
808 from pay_element_classifications
809 where classification_id = to_number(p_cost_detail_class);
810
811 cursor cur_costing_detail_ele_name is
812 select tl.element_name
813 from pay_element_types_f el, pay_element_types_f_tl tl
814 where el.element_type_id = to_number(p_cost_detail_element)
815 and el.element_type_id = tl.element_type_id
816 and tl.language = userenv('LANG')
817 and fnd_date.canonical_to_date(p_date_earned)
818 between el.effective_start_date and el.effective_end_date;
819
820 cursor cur_costing_detail_asg_set is
821 select assignment_set_name
822 from hr_assignment_sets
823 where assignment_set_id = to_number(p_cost_detail_asg_set);
824
825 cursor cur_costing_detail_accruals is
826 select meaning
827 from hr_lookups
828 where lookup_code = p_cost_detail_accruals
829 and lookup_type = 'PAY_PAYRPCBR';
830
831
832 begin
833
834 -- Initialize Variables
835 l_proc := gv_package||'.StartProcess';
836 ItemType := item_type;
837 ItemKey := item_key;
838 ItemUserKey := ProcessDesc;
839 l_business_group_id := p_business_group_id;
840 l_payroll_id := to_number(p_payroll_id);
841 l_consolidation_set_id := to_number(p_consolidation_set_id);
842 lv_is_batch := 'N';
843 lv_is_retro_ntfy := 'N';
844 lv_is_nacha := 'N';
845 lv_is_check := 'N';
846 lv_is_third_party_check := 'N';
847 lv_is_deposit_advice := 'N';
848 lv_is_payroll_exception_report := 'N';
849 lv_is_ele_reg := 'N';
850 --hhh
851
852 -- Initialize the Process and Report List.
853 lv_conc_prog_name(1) := 'BEE Batch Process (Transfer)';
854 lv_conc_prog_name(2) := 'Retro-Notifications Report';
858 lv_conc_prog_name(6) := 'Payroll Message Report';
855 lv_conc_prog_name(3) := 'Retro Pay By Element';
856 lv_conc_prog_name(4) := 'Payroll Process';
857 lv_conc_prog_name(5) := 'US Gross to Net Summary';
859 lv_conc_prog_name(7) := 'Employee Assignments Not Processed';
860 lv_conc_prog_name(8) := 'Payroll Exception Report';
861 lv_conc_prog_name(9) := 'Federal and State Tax Remittance Report';
862 lv_conc_prog_name(10) := 'Pre Payments';
863 lv_conc_prog_name(11) := 'NACHA';
864 lv_conc_prog_name(12) := 'External Process Archive';
865 lv_conc_prog_name(13) := 'Check Writer';
866 lv_conc_prog_name(14) := 'Third Party Check Writer';
867 lv_conc_prog_name(15) := 'Deposit Advice';
868 lv_conc_prog_name(16) := 'Element Register Report';
869 lv_conc_prog_name(17) := 'Payment Register Report';
870 lv_conc_prog_name(18) := 'Third Party Payment Register Report';
871 lv_conc_prog_name(19) := 'Costing Process';
872 lv_conc_prog_name(20) := 'Costing Summary Report';
873 lv_conc_prog_name(21) := 'Costing Detail Report';
874
875 -- wf_core.clear;
876
877 -- make sure the process terminated by aborting it first
878 -- begin
879 -- wf_engine.abortprocess(ItemType, ItemKey);
880 -- exception
881 -- when others then null;
882 -- end;
883
884 hr_utility.trace('In StartProcess Item Type is : '|| ItemType);
885 hr_utility.trace('In StartProcess Item Key is : '|| ItemKey);
886 hr_utility.trace('WF process name is : '|| Workflowprocess);
887
888 begin
889 select to_char(sysdate,'DD-MON-YYYY') ||' '|| to_char(sysdate,'HH24:MI:SS') into lv_date_time from dual;
890 end;
891
892 hr_utility.trace('b4 create_process');
893
894 wf_engine.createProcess( ItemType => ItemType,
895 ItemKey => ItemKey,
896 process => Workflowprocess);
897
898 wf_engine.SetItemUserKey( ItemType => ItemType,
899 ItemKey => ItemKey,
900 UserKey => ItemUserKey);
901 hr_utility.trace('In Start Process b4 HR_SIGNON.Initialize_HR_Security');
902 HR_SIGNON.Initialize_HR_Security;
903 hr_utility.trace('A4 HR_SIGNON.Initialize_HR_Security of Start ');
904
905 l_user_id:= FND_GLOBAL.USER_ID;
906 l_resp_appl_id:= FND_GLOBAL.RESP_APPL_ID;
907 l_resp_id:= FND_GLOBAL.RESP_ID;
908 l_org_id:= FND_GLOBAL.ORG_ID;
909
910 l_per_sec_id := FND_GLOBAL.PER_SECURITY_PROFILE_ID;
911 l_sec_grp_id := FND_GLOBAL.SECURITY_GROUP_ID;
912
913 hr_utility.trace('l_user_id = ' || l_user_id);
914 hr_utility.trace('l_resp_appl_id = ' || l_resp_appl_id);
915 hr_utility.trace('l_resp_id = ' || l_resp_id);
916 hr_utility.trace('l_org_id = ' || l_org_id);
917 hr_utility.trace('l_per_sec_id = ' || l_per_sec_id);
918 hr_utility.trace('l_sec_grp_id = ' || l_sec_grp_id);
919 hr_utility.trace('BG = ' || to_char(p_business_group_id));
920 hr_utility.trace('PAYMENT TYPE = ' || p_nacha_payment_type);
921
922
923 /*
924 fnd_global.apps_initialize(l_user_id,l_resp_id,l_resp_appl_id);
925 */
926
927 Begin
928 select currency_code into lv_currency_code
929 from per_business_groups
930 where business_group_id = p_business_group_id;
931 Exception when others then
932 hr_utility.trace('In exception: OTHERS ');
933 null;
934
935 END;
936
937
938 Begin
939 SELECT to_char(PPT.PAYMENT_TYPE_ID) into ln_nacha_payment_type_id
940 FROM PAY_PAYMENT_TYPES PPT,
941 PER_BUSINESS_GROUPS BG
942 WHERE ( ((PPT.TERRITORY_CODE IS NOT NULL
943 AND PPT.TERRITORY_CODE=BG.LEGISLATION_CODE)
944 OR PPT.TERRITORY_CODE IS NULL)
945 AND BG.BUSINESS_GROUP_ID= p_business_group_id
946 AND PPT.PAYMENT_TYPE_NAME = 'NACHA'
947 );
948 Exception when others then
949 hr_utility.trace('In exception: OTHERS ');
950 null;
951
952 END;
953
954
955 Begin
956 SELECT to_char(PPT.PAYMENT_TYPE_ID)
957 into ln_check_payment_type_id
958 FROM PAY_PAYMENT_TYPES PPT,
959 PER_BUSINESS_GROUPS BG
960 WHERE ( ((PPT.TERRITORY_CODE IS NOT NULL
961 AND PPT.TERRITORY_CODE=BG.LEGISLATION_CODE)
962 OR PPT.TERRITORY_CODE IS NULL)
963 AND BG.BUSINESS_GROUP_ID= p_business_group_id
964 AND PPT.PAYMENT_TYPE_NAME = 'Check'
965 );
966 Exception when others then
967 hr_utility.trace('In exception: OTHERS ');
968 null;
969
970 END;
971
972
973 Begin
974 select report_name into lv_da_report
975 from pay_report_format_mappings_f
976 where report_type = 'DAR'
977 and report_category = p_da_report_category;
978 Exception when others then
979 hr_utility.trace('In exception: OTHERS ');
980 null;
981
982 END;
983
984
985 Begin
986 -- Payroll Name
987 select payroll_name into lv_payroll_name
988 from pay_all_payrolls_f
989 where payroll_id = p_payroll_id
990 and fnd_date.canonical_to_date(p_date_earned)
991 between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
992
993 hr_utility.trace('lv_payroll_name = ' || lv_payroll_name);
994 Exception when others then
995 hr_utility.trace('In exception: OTHERS ');
996 null;
997
998 END;
999
1000 Begin
1001 -- Consolidation Set Name
1005
1002 select consolidation_set_name into lv_consolidation_set_name
1003 from pay_consolidation_sets
1004 where consolidation_set_id = p_consolidation_set_id;
1006 hr_utility.trace('lv_consolidation_set_name = ' || lv_consolidation_set_name);
1007 Exception when others then
1008 hr_utility.trace('In exception: OTHERS ');
1009 null;
1010
1011 END;
1012
1013 Begin
1014 -- GRE Name
1015 select name into lv_gre_name
1016 from hr_tax_units_v
1017 where tax_unit_id = to_number(p_gre);
1018 hr_utility.trace('lv_gre_name = ' || lv_gre_name);
1019
1020 Exception when others then
1021 hr_utility.trace('In exception: OTHERS ');
1022 null;
1023
1024 END;
1025
1026 Begin
1027 -- Period Name
1028 select PERIOD_NAME into lv_period_name
1029 from per_time_periods
1030 where time_period_id = p_period;
1031 hr_utility.trace('lv_period_name = ' || lv_period_name);
1032
1033 Exception when others then
1034 hr_utility.trace('In exception: OTHERS ');
1035 null;
1036
1037 END;
1038
1039 Begin
1040 -- Event Group Name
1041 select event_group_name
1042 into lv_event_group_name
1043 from pay_event_groups
1044 where event_group_id = substr(p_event_group,12,length(p_event_group));
1045
1046 hr_utility.trace('lv_event_group_name = ' || lv_event_group_name);
1047
1048 Exception when others then
1049 hr_utility.trace('In exception: OTHERS ');
1050 null;
1051
1052 END;
1053
1054 Begin
1055 -- Nacha Org Payment Method Name
1056 select ORG_PAYMENT_METHOD_NAME
1057 into lv_nacha_pymt_name
1058 from pay_org_payment_methods_f
1059 where business_group_id = p_business_group_id
1060 and org_payment_method_id = p_nacha_payment_method
1061 and fnd_date.canonical_to_date(p_date_earned)
1062 between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
1063 hr_utility.trace('lv_nacha_pymt_name = ' || lv_nacha_pymt_name);
1064
1065 Exception when others then
1066 hr_utility.trace('In exception: OTHERS Nacha Org Payment Method Name ');
1067 null;
1068
1069 END;
1070
1071 Begin
1072 -- Check Org Payment Method Name
1073 select ORG_PAYMENT_METHOD_NAME
1074 into lv_check_pymt_name
1075 from pay_org_payment_methods_f
1076 where business_group_id = p_business_group_id
1077 and org_payment_method_id = p_check_writer_payment_method
1078 and fnd_date.canonical_to_date(p_date_earned)
1079 between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
1080 hr_utility.trace('lv_check_pymt_name = ' || lv_check_pymt_name);
1081 Exception when others then
1082 hr_utility.trace('In exception: OTHERS of Check Org Payment Method Name ');
1083 null;
1084
1085 END;
1086
1087 Begin
1088 -- Pre Org Payment Method Name Override
1089 select ORG_PAYMENT_METHOD_NAME
1090 into lv_pre_ovr_pymt_name
1091 from pay_org_payment_methods_f
1092 where business_group_id = p_business_group_id
1093 and org_payment_method_id = p_payment_method_override
1094 and fnd_date.canonical_to_date(p_date_earned)
1095 between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
1096 hr_utility.trace('lv_pre_ovr_pymt_name = ' || lv_pre_ovr_pymt_name);
1097 Exception when others then
1098 hr_utility.trace('In exception : OTHERS Pre Org Payment Method Name Override');
1099 null;
1100
1101 END;
1102
1103 begin
1104 open cur_costing_summary_accruals;
1105 fetch cur_costing_summary_accruals into lv_cost_summary_accruals;
1106 close cur_costing_summary_accruals;
1107
1108 hr_utility.trace('Costing Summary Accruals = ' || lv_cost_summary_accruals);
1109
1110 open cur_costing_summary_file_out;
1111 fetch cur_costing_summary_file_out into lv_cost_summary_file_out;
1112 close cur_costing_summary_file_out;
1113
1114 hr_utility.trace('Costing Summary File Format = ' || lv_cost_summary_file_out);
1115
1116 open cur_costing_detail_ele_set;
1117 fetch cur_costing_detail_ele_set into lv_cost_detail_ele_set;
1118 close cur_costing_detail_ele_set;
1119
1120 hr_utility.trace('Costing Detail Element Set = ' || lv_cost_detail_ele_set);
1121
1122 open cur_costing_detail_ele_name;
1123 fetch cur_costing_detail_ele_name into lv_cost_detail_ele_name;
1124 close cur_costing_detail_ele_name;
1125
1126 hr_utility.trace('Costing Detail Element Name = ' || lv_cost_detail_ele_name);
1127
1128 open cur_costing_detail_ele_class;
1129 fetch cur_costing_detail_ele_class into lv_cost_detail_ele_class;
1130 close cur_costing_detail_ele_class;
1131
1132 hr_utility.trace('Costing Detail Element Classification = ' || lv_cost_detail_ele_class);
1133
1134 open cur_costing_detail_asg_set;
1135 fetch cur_costing_detail_asg_set into lv_cost_detail_asg_set;
1136 close cur_costing_detail_asg_set;
1137
1138 hr_utility.trace('Costing Detail Assignment Set = ' || lv_cost_detail_asg_set);
1139
1140 open cur_costing_detail_accruals;
1141 fetch cur_costing_detail_accruals into lv_cost_detail_accruals;
1142 close cur_costing_detail_accruals;
1143
1144 hr_utility.trace('Costing Detail Accruals = ' || lv_cost_detail_accruals);
1145
1146 END;
1147
1148
1149 Begin
1150 -- Check lv_isResponseRequired
1151 select nvl(parameter_value,'WAIT')
1152 into lv_Payroll_WF_Notify_Action
1156 hr_utility.trace('lv_Payroll_WF_Notify_Action = ' || lv_Payroll_WF_Notify_Action);
1153 from pay_action_parameters
1154 where parameter_name = 'PAYROLL_WF_NOTIFY_ACTION';
1155
1157 If lv_Payroll_WF_Notify_Action = 'WAIT' then
1158 lv_isResponseRequired := 'Y';
1159 lv_isPaymentWait := 'Y';
1160 elsif lv_Payroll_WF_Notify_Action = 'NOWAIT' then
1161 lv_isResponseRequired := 'N';
1162 lv_isPaymentWait := 'N';
1163 elsif lv_Payroll_WF_Notify_Action = 'PAYMENTWAIT' then
1164 lv_isResponseRequired := 'N';
1165 lv_isPaymentWait := 'Y';
1166 end if;
1167
1168 hr_utility.trace('lv_isResponseRequire = ' || lv_isResponseRequired);
1169 hr_utility.trace('lv_isPaymentWait = ' || lv_isPaymentWait);
1170
1171 Exception when others then
1172 hr_utility.trace('In exception: OTHERS ');
1173 lv_isResponseRequired := 'Y';
1174 lv_isPaymentWait := 'Y';
1175 hr_utility.trace('lv_isResponseRequire = ' || lv_isResponseRequired);
1176
1177 END;
1178
1179 Begin
1180 -- Check Sorting Sequence
1181 select meaning
1182 into lv_check_sort_seq_meaning
1183 from fnd_common_lookups
1184 where lookup_type = 'CHEQUE_PROCEDURE'
1185 and lookup_code = p_check_writer_sort_sequence;
1186
1187 hr_utility.trace('lv_check_sort_seq_meaning = ' || lv_check_sort_seq_meaning);
1188 Exception when others then
1189 hr_utility.trace('In exception: OTHERS ');
1190 null;
1191
1192 END;
1193
1194 Begin
1195 -- Exception REports Information
1196 select per.exception_report_name ,
1197 hr_general.decode_lookup('PQP_VARIANCE_TYPES',per.variance_type) ovt,
1198 to_char(per.VARIANCE_VALUE)
1199 into lv_exc_rep_name,lv_orv_type,lv_orv_value
1200 from pqp_exception_reports per
1201 where legislation_code = 'US'
1202 and per.exception_report_id = p_exception_report_name;
1203
1204 hr_utility.trace('lv_exc_rep_name = ' || lv_exc_rep_name);
1205 hr_utility.trace('lv_orv_type = ' || lv_orv_type);
1206 hr_utility.trace('lv_orv_value = ' || lv_orv_value);
1207
1208 Exception when others then
1209 hr_utility.trace('In exception: OTHERS ');
1210 null;
1211
1212 END;
1213 /*
1214 Begin
1215 -- Exception Group Information
1216 select perg.exception_report_name ,
1217 hr_general.decode_lookup('PQP_VARIANCE_TYPES',perg.variance_type) ovt,
1218 to_char(VARIANCE_VALUE)
1219 into lv_exc_grp_name,lv_grp_orv_type,lv_grp_orv_value
1220 from pqp_exception_report_groups perg
1221 where legislation_code = 'US'
1222 and perg.EXCEPTION_GROUP_ID = p_exception_group_name;
1223
1224 hr_utility.trace('lv_gre_name = ' || lv_gre_name);
1225 Exception when others then
1226 hr_utility.trace('In exception: OTHERS ');
1227 null;
1228
1229 END;
1230 */
1231 Begin
1232 select element_name
1233 into lv_element_name
1234 from pay_element_types_f
1235 where element_type_id = p_element;
1236 Exception when others then
1237 hr_utility.trace('In exception: OTHERS ');
1238 null;
1239
1240 END;
1241
1242 Begin
1243 If p_batch_id is not null then
1244 select
1245 batch_name
1246 into lv_batch_name
1247 from pay_batch_headers
1248 where batch_id = p_batch_id;
1249 End If;
1250 hr_utility.trace('Batch Name :' ||lv_batch_name);
1251
1252 Exception when others then
1253 lv_batch_name := '';
1254 hr_utility.trace('In exception: Batch Name ');
1255 null;
1256
1257 END;
1258
1259 Begin
1260 /* currently we are only looking at the context at payroll level
1261 as we do not have any required parameter for GRE on the SRS
1262 screen, if we want to use the contact defined at GRE level then
1263 we need to modify the package and SRS defination to get the
1264 GRE_ID , and pass it in the following function.
1265 */
1266
1267 lv_contact_user_name := get_notifier( to_number(p_payroll_id),
1268 to_number(p_gre),
1269 p_date_earned);
1270
1271 Exception when others then
1272 hr_utility.trace('In exception: OTHERS at get_notifier');
1273 null;
1274
1275 END;
1276
1277 Begin
1278
1279 select orig_system,orig_system_id,name,display_name
1280 into l_orig_system,l_orig_system_id,l_role_name,l_role_display_name
1281 from wf_roles
1282 where name = lv_contact_user_name ;
1283
1284 Exception when others then
1285 hr_utility.trace('In exception: OTHERS ');
1286 null;
1287
1288 END;
1289 -- For debugging .. remove the code
1290 Begin
1291 /*
1292 select fnd_profile_server.value('PER_SECURITY_PROFILE_ID')
1293 into l_profile_per_sec_id
1294 from dual;
1295 */
1296 select to_number(fnd_profile_server.value('PER_SECURITY_PROFILE_ID'))
1297 into l_profile_per_sec_id
1298 from dual;
1299 select fnd_profile_server.value('PER_BUSINESS_GROUP_ID')
1300 into l_profile_per_bg
1301 from dual;
1302 /*
1303 select fnd_profile_server.value('SECURITY_GROUP_ID')
1304 into l_profile_sec_grp_id
1305 from dual;
1306 */
1307
1311 from dual;
1308 /* Bug 9211154 - There is no profile 'SECURITY_GROUP_ID' and hence below query will always return NULL'
1309 select to_number(fnd_profile_server.value('SECURITY_GROUP_ID'))
1310 into l_profile_sec_grp_id
1312 */
1313 --Bug 9211154 Setting the value as fnd security group id
1314 l_profile_sec_grp_id := l_sec_grp_id;
1315
1316 select fnd_profile_server.value('USERNAME')
1317 into l_profile_user_name
1318 from dual;
1319
1320 hr_utility.trace('jj payuswfpkg l_profile_per_sec_id = '||l_profile_per_sec_id);
1321 hr_utility.trace('jj payuswfpkg l_profile_per_bg = '||l_profile_per_bg);
1322 hr_utility.trace('jj payuswfpkg l_profile_sec_grp_id = '||l_profile_sec_grp_id);
1323 hr_utility.trace('jj payuswfpkg l_profile_user_namep = '||l_profile_user_name);
1324
1325 Exception when others then
1326 hr_utility.trace('In exception: OTHERS at profile');
1327 null;
1328
1329 END;
1330
1331 --'JJ CA HRMS Manager';
1332 hr_utility.trace('orig ststem : '|| l_orig_system);
1333 hr_utility.trace('orig ststem : '|| l_orig_system_id);
1334 hr_utility.trace('l_role_name = ' || l_role_name);
1335
1336 wf_engine.SetItemOwner ( itemtype => ItemType,
1337 itemkey => ItemKey,
1338 owner => ProcessOwner);
1339
1340 --
1341 /* Set Require Values for the Variables
1342 */
1343
1344 -- BEE Transfer
1345 If p_batch_id is not null then
1346 lv_is_batch := 'Y';
1347 lv_prc_list := lv_prc_list||lv_conc_prog_name(1)||wf_core.newline;
1348 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(1)||'</p>';
1349 else
1350 lv_is_batch := 'N';
1351 end if;
1352
1353 -- Retro Pay By Element and Retro Notification Report
1354
1355 If ((p_retro_assignment_set_name is not null) and
1356 (p_event_group is not null )) then
1357 lv_is_retro_ntfy := 'Y';
1358 lv_prc_list := lv_prc_list||lv_conc_prog_name(2)||wf_core.newline;
1359 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(2)||'</p>';
1360 else
1361 lv_is_retro_ntfy := 'N';
1362 end if;
1363
1364 -- Payroll Exception Report
1365
1366 If ((p_exception_report_name is not null) OR
1367 (p_exception_group_name is not null)) then
1368 lv_is_payroll_exception_report := 'Y';
1369 lv_prc_list := lv_prc_list||lv_conc_prog_name(1)||wf_core.newline;
1370 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(1)||'</p>';
1371 else
1372 lv_is_payroll_exception_report := 'N';
1373 end if;
1374
1375 -- NACHA
1376
1377 If p_nacha_payment_method is not null then
1378 lv_is_nacha := 'Y';
1379 lv_prc_list := lv_prc_list||lv_conc_prog_name(1)||wf_core.newline;
1380 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(1)||'</p>';
1381 else
1382 lv_is_nacha := 'N';
1383 end if;
1384
1385 -- Check
1386 If (p_check_writer_payment_method is not null ) and
1387 (p_start_check_number is not null ) then
1388 lv_is_check := 'Y';
1389 lv_prc_list := lv_prc_list||lv_conc_prog_name(1)||wf_core.newline;
1390 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(1)||'</p>';
1391 else
1392 lv_is_check := 'N';
1393 end if;
1394
1395 -- Third Party Check
1396
1397 If p_payment_method_3rd_party is not null then
1398 lv_is_third_party_check := 'Y';
1399 lv_prc_list := lv_prc_list||lv_conc_prog_name(1)||wf_core.newline;
1400 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(1)||'</p>';
1401 else
1402 lv_is_third_party_check := 'N';
1403 end if;
1404
1405 -- Deposit Advice
1406
1407 If p_da_report_category is not null then
1408 lv_is_deposit_advice := 'Y';
1409 lv_prc_list := lv_prc_list||lv_conc_prog_name(1)||wf_core.newline;
1410 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(1)||'</p>';
1411 else
1412 lv_is_deposit_advice := 'N';
1413 end if;
1414
1415 -- Element Register Report
1416 If (p_selection_criterion is not null ) and
1417 ((p_ele_reg_element_set is not null) or
1418 (p_element_classification is not null) or
1419 (p_element is not null))
1420 then
1421 lv_is_ele_reg := 'Y';
1422 lv_prc_list := lv_prc_list||lv_conc_prog_name(16)||wf_core.newline;
1423 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(16)||'</p>';
1424 else
1425 lv_is_ele_reg := 'N';
1426 end if;
1427
1428 -- Text
1429 for k in 4..19 loop
1430 lv_prc_list := lv_prc_list||lv_conc_prog_name(k);
1431 end loop;
1432
1433 -- HTML
1434 for l in 4..19 loop
1435 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(l)||'</p>';
1436 end loop;
1437
1438 -- Costing Summary and Detail Report
1439 if ((p_consolidation_set_id is not null)) then
1440 lv_is_costing_detail := 'Y';
1441 lv_prc_list := lv_prc_list||lv_conc_prog_name(21)||wf_core.newline;
1442 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(21)||'</p>';
1443
1444 if (p_cost_summary_file_out is not null) then
1445 lv_is_costing_summary := 'Y';
1446 lv_prc_list := lv_prc_list||lv_conc_prog_name(20)||wf_core.newline;
1447 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(20)||'</p>';
1448 else
1452 lv_is_costing_summary := 'N';
1449 lv_is_costing_summary := 'N';
1450 end if;
1451 else
1453 lv_is_costing_detail := 'N';
1454 end if;
1455 l_message_subject1 := 'List of Processes and Reports which will be submitted.';
1456 if lv_isResponseRequired = 'Y' then
1457 l_message_text1 := '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||lv_prc_list;
1458
1459 l_message_html_text1 := 'Review the List of Processes and Reports which will be submitted based on the input parameters, please click on the '||''''||'Continue'||''''||' to start processing.';
1460 else
1461 l_message_text1 := 'Review the List of Processes and Reports which will be submitted based on the input parameters.'||wf_core.newline||lv_prc_list;
1462
1463 l_message_html_text1 := 'Review the List of Processes and Reports which will be submitted based on the input parameters.';
1464 end if;
1465
1466 /*
1467 l_message_html_text1 := '<p> 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.'||'</p>'||'<p>'||lv_prc_list_html||'</p>';
1468
1469 */
1470
1471 /*
1472 l_message_html_text1 := '<p> Review the List of Processes and Reports which will be submitted based on the input parameters, please click on the '||''''||'Continue'||''''||' to start processing.'||'</p>';
1473
1474
1475 l_message_html_text1 := 'Review the List of Processes and Reports which will be submitted based on the input parameters, please click on the '||''''||'Continue'||''''||' to start processing.';
1476
1477 */
1478 /*
1479 if ((lv_is_batch = 'Y') and (lv_is_retro_ntfy = 'Y'))then
1480 l_message_html_text2 := 'WF_NOTIFICATION(ATTRS,MSG_ATTR19,MSG_ATTR20,MSG_ATTR21,MSG_ATTR1,MSG_ATTR2,MSG_ATTR3,MSG_ATTR4,MSG_ATTR5,'
1481 ||'MSG_ATTR6,MSG_ATTR7,MSG_ATTR8,MSG_ATTR9,MSG_ATTR10,MSG_ATTR11,MSG_ATTR12,MSG_ATTR13,MSG_ATTR14,'
1482 ||'MSG_ATTR15,MSG_ATTR16,MSG_ATTR17,MSG_ATTR18)';
1483 elsif ((lv_is_batch = 'Y') and (lv_is_retro_ntfy = 'N' or lv_is_retro_ntfy is null))then
1484 l_message_html_text2 := 'WF_NOTIFICATION(ATTRS,MSG_ATTR19,MSG_ATTR1,MSG_ATTR2,MSG_ATTR3,MSG_ATTR4,MSG_ATTR5,MSG_ATTR6,MSG_ATTR7,'
1485 ||'MSG_ATTR8,MSG_ATTR9,MSG_ATTR10,MSG_ATTR11,MSG_ATTR12,MSG_ATTR13,MSG_ATTR14,'
1486 ||'MSG_ATTR15,MSG_ATTR16,MSG_ATTR17,MSG_ATTR18)';
1487 elsif ((lv_is_batch = 'N' or lv_is_batch is null) and (lv_is_retro_ntfy = 'Y'))then
1488 l_message_html_text2 := 'WF_NOTIFICATION(ATTRS,MSG_ATTR20,MSG_ATTR21,MSG_ATTR1,MSG_ATTR2,MSG_ATTR3,MSG_ATTR4,MSG_ATTR5,MSG_ATTR6,MSG_ATTR7,MSG_ATTR8,'
1489 ||'MSG_ATTR9,MSG_ATTR10,MSG_ATTR11,MSG_ATTR12,MSG_ATTR13,MSG_ATTR14,MSG_ATTR15,'
1490 ||'MSG_ATTR16,MSG_ATTR17,MSG_ATTR18)';
1491 else
1492 l_message_html_text2 := 'WF_NOTIFICATION(ATTRS,MSG_ATTR1,MSG_ATTR2,MSG_ATTR3,MSG_ATTR4,MSG_ATTR5,MSG_ATTR6,MSG_ATTR7,'
1493 ||'MSG_ATTR8,MSG_ATTR9,MSG_ATTR10,MSG_ATTR11,MSG_ATTR12,MSG_ATTR13,MSG_ATTR14,'
1494 ||'MSG_ATTR15,MSG_ATTR16,MSG_ATTR17,MSG_ATTR18)';
1495 end if;
1496 */
1497
1498 /*
1499 -- Testing with multiple functions in the same text of the body for the notification
1500
1501 if (lv_is_batch = 'Y') then
1502 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR19)';
1503 elsif (lv_is_retro_ntfy = 'Y') then
1504 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR20,MSG_ATTR21)';
1505 end if;
1506 -- For Other Processes Dependent on Payroll Run.
1507
1508 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR1,MSG_ATTR2,MSG_ATTR3,MSG_ATTR4,MSG_ATTR5)';
1509
1510 if (lv_is_payroll_exception_report = 'Y') then
1511 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR6)';
1512 end if;
1513
1514 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR7)';
1515
1516 if (lv_is_nacha = 'Y') then
1517 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR8)';
1518 end if;
1519
1520 -- External Process Archive
1521 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR9)';
1522
1523 if (lv_is_check = 'Y') then
1524 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR10)';
1525 elsif (lv_is_third_party_check = 'Y') then
1526 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR11)';
1527 end if;
1528
1529 -- Deposit Advice
1530
1531 if (lv_is_deposit_advice = 'Y') then
1532 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR12)';
1533 end if;
1534
1535 -- Register Reports and Costing Process and Reports.
1536
1537 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR13,MSG_ATTR14,MSG_ATTR15,MSG_ATTR16,MSG_ATTR17,MSG_ATTR18)';
1538
1539 */
1540 --jjj
1541 -- Testing with single functions and all message attributes in the same text of the body for the notification , setting vlalues of the message attribute by setting the values of the item attributes
1542 n := 1;
1543 if (lv_is_batch = 'Y') then
1544 lv_cur_process(n) := lv_conc_prog_name(1);
1545 n := n+1;
1546 end if;
1547
1548 if (lv_is_retro_ntfy = 'Y') then
1549 lv_cur_process(n) := lv_conc_prog_name(2);
1550 n := n+1;
1551 lv_cur_process(n) := lv_conc_prog_name(3);
1552 n := n+1;
1553 end if;
1554 -- For Other Processes Dependent on Payroll Run.
1555
1556 lv_cur_process(n) := lv_conc_prog_name(4);
1557 n := n+1;
1558 lv_cur_process(n) := lv_conc_prog_name(5);
1559 n := n+1;
1560 lv_cur_process(n) := lv_conc_prog_name(6);
1564
1561 n := n+1;
1562 lv_cur_process(n) := lv_conc_prog_name(7);
1563 n := n+1;
1565 if (lv_is_payroll_exception_report = 'Y') then
1566 lv_cur_process(n) := lv_conc_prog_name(8);
1567 n := n+1;
1568 l_pay_excep_rpt_message_text1 := 'Payroll Exception Report(Request Id: &REQ_ID_REP4) :'||'
1569 Start Date: &START_DATE
1570 End Date: &END_DATE
1571 Payroll Name: &PAYROLL
1572 Consolidation Set: &CONSOLIDATION_SET
1573 Exception Group Name: &GROUP_NAME
1574 Exception Report Name: &REPORT_NAME
1575 Override Variance Type: &OVER_VAR_TYPE
1576 Override Variance Value: &OVER_VAR_VALUE ' ;
1577 l_pay_excep_rpt_message_html1 := 'Payroll Exception Report (Request Id: &REQ_ID_REP4) : WF_NOTIFICATION(ATTRS,START_DATE,PAYROLL,CONSOLIDATION_SET,GROUP_NAME,REPORT_NAME,OVER_VAR_TYPE,OVER_VAR_VALUE) ';
1578
1579 else
1580 l_pay_excep_rpt_message_text1 := null;
1581 l_pay_excep_rpt_message_html1 := null;
1582
1583 end if;
1584
1585 --Federal and State Tax Remittance Report
1586
1587 lv_cur_process(n) := lv_conc_prog_name(9);
1588 n := n+1;
1589
1590 --Pre Payments
1591
1592 lv_cur_process(n) := lv_conc_prog_name(10);
1593 n := n+1;
1594
1595 if (lv_is_nacha = 'Y') then
1596 lv_cur_process(n) := lv_conc_prog_name(11);
1597 n := n+1;
1598 end if;
1599
1600 -- External Process Archive
1601 lv_cur_process(n) := lv_conc_prog_name(12);
1602 n := n+1;
1603
1604 if (lv_is_check = 'Y') then
1605 lv_cur_process(n) := lv_conc_prog_name(13);
1606 n := n+1;
1607 end if;
1608
1609 if (lv_is_third_party_check = 'Y') then
1610 lv_cur_process(n) := lv_conc_prog_name(14);
1611 n := n+1;
1612 end if;
1613
1614 -- Deposit Advice
1615
1616 if (lv_is_deposit_advice = 'Y') then
1617 lv_cur_process(n) := lv_conc_prog_name(15);
1618 n := n+1;
1619 end if;
1620
1621 -- Register Reports and Costing Process and Reports.
1622
1623 if (lv_is_ele_reg = 'Y') then
1624 lv_cur_process(n) := lv_conc_prog_name(16);
1625 n := n+1;
1626 l_ele_reg_rpt_message_text1 := 'Element Register(Request ID: &REQ_ID_REP6) ';
1627 l_ele_reg_rpt_message_html1 := 'Element Register Report (Request Id: &REQ_ID_REP6) ';
1628
1629 else
1630 l_ele_reg_rpt_message_text1 := null;
1631 l_ele_reg_rpt_message_html1 := null;
1632
1633 end if;
1634
1635 -- Payment Register
1636
1637 lv_cur_process(n) := lv_conc_prog_name(17);
1638 n := n+1;
1639
1640 -- 3rd Payment Register
1641
1642 lv_cur_process(n) := lv_conc_prog_name(18);
1643 n := n+1;
1644
1645 -- Costing Process
1646
1647 lv_cur_process(n) := lv_conc_prog_name(19);
1648 n := n+1;
1649
1650 -- Costing Summary and Detail Reports
1651
1652 lv_cur_process(n) := lv_conc_prog_name(20);
1653 n := n+1;
1654 lv_cur_process(n) := lv_conc_prog_name(21);
1655 n := n+1;
1656
1657
1658
1659 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR1,MSG_ATTR2,MSG_ATTR3,MSG_ATTR4,MSG_ATTR5)';
1660 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR6,MSG_ATTR7,MSG_ATTR8,MSG_ATTR9,MSG_ATTR10)';
1661 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR11,MSG_ATTR12,MSG_ATTR13,MSG_ATTR14,MSG_ATTR15)';
1662 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR16,MSG_ATTR17,MSG_ATTR18,MSG_ATTR19,MSG_ATTR20,MSG_ATTR21)';
1663
1664 if n < 22 then
1665 for p in n..21 loop
1666 lv_cur_process(p) := null;
1667 end loop;
1668 end if;
1669
1670 hr_utility.trace('p_payroll_id = ' || p_payroll_id);
1671 hr_utility.trace('p_consolidation_set_id = ' || p_consolidation_set_id);
1672 hr_utility.trace('p_date_earned = ' || p_date_earned);
1673 hr_utility.trace('p_date_paid = ' || p_date_paid);
1674 hr_utility.trace('p_payroll_assignment_set_name = ' || p_payroll_assignment_set_name);
1675 hr_utility.trace('p_payroll_run_type = ' || p_payroll_run_type);
1676 hr_utility.trace('ln_nacha_payment_type_id = ' || ln_nacha_payment_type_id);
1677 hr_utility.trace('check_style = ' || p_check_style);
1678 begin
1679
1680 i := 0;
1681
1682 varname(i) := 'CUR_ITEM_TYPE';
1683 varval(i) := ItemType;
1684 i := i+1;
1685 varname(i) := 'CURR_ITEM_KEY';
1686 varval(i) := ItemKey;
1687 i := i+1;
1688 varname(i) := 'IS_BATCH';
1689 varval(i) := lv_is_batch;
1690 i := i+1;
1691 varname(i) := 'IS_RETRO';
1692 varval(i) := lv_is_retro_ntfy;
1693 i := i+1;
1694 varname(i) := 'PAYROLL_NAME';
1695 varval(i) := lv_payroll_name;
1696 i := i+1;
1697 varname(i) := 'PAYROLL_ID';
1698 varval(i) := p_payroll_id;
1699 i := i+1;
1700 varname(i) := 'CONSOLIDATION_SET_NAME';
1701 varval(i) := lv_consolidation_set_name;
1702 i := i+1;
1703 varname(i) := 'CONSOLIDATION_SET_ID';
1704 varval(i) := p_consolidation_set_id;
1705 i := i+1;
1706 varname(i) := 'DATE_EARNED';
1707 varval(i) := p_date_earned;
1708 i := i+1;
1709 varname(i) := 'DATE_PAID';
1710 varval(i) := p_date_paid;
1711 i := i+1;
1712 varname(i) := 'EVENT_GROUP';
1713 varval(i) := p_event_group;
1717 i := i+1;
1714 i := i+1;
1715 varname(i) := 'RETRO_ASSIGNMENT_SET_NAME';
1716 varval(i) := p_retro_assignment_set_name;
1718 varname(i) := 'RETRO_ASSIGNMENT_SET_NAME_DUMY';
1719 varval(i) := p_retro_assignment_set_dummy;
1720 i := i+1;
1721 varname(i) := 'RETRO_ELEMENT_SET';
1722 varval(i) := p_retro_element_set;
1723 i := i+1;
1724 varname(i) := 'RETRO_START_DATE';
1725 varval(i) := p_retro_start_date;
1726 i := i+1;
1727 varname(i) := 'EFFECTIVE_DATE';
1728 varval(i) := p_effective_date;
1729 --varval(i) := p_date_earned;
1730 --varval(i) := p_date_paid;
1731 i := i+1;
1732 varname(i) := 'PAYROLL_ELEMENT_SET_NAME';
1733 varval(i) := p_payroll_element_set_name;
1734 i := i+1;
1735 varname(i) := 'PAYROLL_ASSIGNMENT_SET_NAME';
1736 varval(i) := p_payroll_assignment_set_name;
1737 i := i+1;
1738 varname(i) := 'PAYROLL_RUN_TYPE';
1739 varval(i) := p_payroll_run_type;
1740 i := i+1;
1741 varname(i) := 'GOVERNMENT_REPORTING_ENTITY';
1742 varval(i) := p_gre;
1743 i := i+1;
1744 varname(i) := 'GRE_NAME';
1745 varval(i) := lv_gre_name;
1746 i := i+1;
1747 varname(i) := 'ORGANIZATION';
1748 varval(i) := p_organization;
1749 i := i+1;
1750 varname(i) := 'LOCATION';
1751 varval(i) := p_location;
1752 i := i+1;
1753 varname(i) := 'IS_PAYROLL_EXCEPTION_REPORT';
1754 varval(i) := lv_is_payroll_exception_report;
1755 i := i+1;
1756 varname(i) := 'SELECT_REPORT_OR_GROUP';
1757 varval(i) := p_select_report_or_group;
1758 i := i+1;
1759 varname(i) := 'EXCEPTION_GROUP_NAME';
1760 varval(i) := p_exception_group_name;
1761 i := i+1;
1762 varname(i) := 'EXCPTN_RPT_GRP_NAME';
1763 varval(i) := lv_exc_grp_name;
1764 i := i+1;
1765 varname(i) := 'EXCPTN_RPT_NAME';
1766 varval(i) := lv_exc_rep_name;
1767 i := i+1;
1768 varname(i) := 'EXCEPTION_GROUP_NAME_DMY';
1769 varval(i) := p_exception_group_name_dummy;
1770 i := i+1;
1771 varname(i) := 'EXCEPTION_REPORT_NAME';
1772 varval(i) := p_exception_report_name;
1773 i := i+1;
1774 varname(i) := 'EXCEPTION_REPORT_NAME_DMY';
1775 varval(i) := p_exception_report_name_dummy;
1776 i := i+1;
1777 varname(i) := 'OVER_RIDE_VARIANCE_TYPE';
1778 varval(i) := p_over_ride_variance_type;
1779 i := i+1;
1780 varname(i) := 'ORV_TYPE';
1781 varval(i) := lv_orv_type;
1782 i := i+1;
1783 varname(i) := 'OVER_RIDE_VARIANCE_TYPE_DMY';
1784 varval(i) := p_over_ride_varianc_type_dumy;
1785 i := i+1;
1786 varname(i) := 'OVER_RIDE_VARIANCE_VALUE';
1787 varval(i) := p_over_ride_variance_value;
1788 i := i+1;
1789 varname(i) := 'ORV_VALUE';
1790 varval(i) := lv_orv_value;
1791 i := i+1;
1792 varname(i) := 'OVER_RIDE_VARIANCE_VALUE_DMY';
1793 varval(i) := p_over_ride_varinc_value_dumy;
1794 i := i+1;
1795 varname(i) := 'TRANSFER_DATE';
1796 -- varval(i) := 'TRANSFER_DATE='||p_effective_date;
1797 --varval(i) := 'TRANSFER_DATE='||p_date_earned;
1798 varval(i) := 'TRANSFER_DATE='||p_date_paid;
1799 i := i+1;
1800 varname(i) := 'PPA_FINDER_PQP';
1801 varval(i) := p_ppa_finder_pqp;
1802 i := i+1;
1803 varname(i) := 'TRANSFER_PPA_FINDER_PQP';
1804 varval(i) := 'TRANSFER_PPA_FINDER='||p_ppa_finder_pqp;
1805 i := i+1;
1806 varname(i) := 'PREPAY_PAYMENT_METHOD_OVERRIDE';
1807 varval(i) := p_payment_method_override;
1808 i := i+1;
1809 varname(i) := 'IS_NACHA';
1810 varval(i) := lv_is_nacha;
1811 i := i+1;
1812 varname(i) := 'NACHA_PAYMENT_METHOD';
1813 varval(i) := p_nacha_payment_method;
1814 i := i+1;
1815 varname(i) := 'NACHA_PYMT_NAME';
1816 varval(i) := lv_nacha_pymt_name;
1817 i := i+1;
1818 varname(i) := 'DEPOSIT_DATE_OVERRIDE';
1819 varval(i) := p_deposit_date_override;
1820 i := i+1;
1821 varname(i) := 'FILE_ID_MODIFIER';
1822 varval(i) := p_file_id_modifier;
1823 i := i+1;
1824 varname(i) := 'FILE_ID_MODIFIER_CHECK';
1825 varval(i) := p_file_id_modifier_check;
1826 i := i+1;
1827 varname(i) := 'THRID_PARTY_CHECK';
1828 varval(i) := p_thrid_party_check;
1829 i := i+1;
1830 varname(i) := 'IS_CHECK';
1831 varval(i) := lv_is_check;
1832 i := i+1;
1833 varname(i) := 'CHECK_WRITER_PAYMENT_METHOD';
1834 varval(i) := p_check_writer_payment_method;
1835 i := i+1;
1836 varname(i) := 'CHECK_PYMT_NAME';
1837 varval(i) := lv_check_pymt_name;
1838 i := i+1;
1839 varname(i) := 'CHECK_WRITER_SORT_SEQUENCE';
1840 varval(i) := p_check_writer_sort_sequence;
1841 i := i+1;
1842 varname(i) := 'CHECK_SORT_SEQ_MEANING';
1843 varval(i) := lv_check_sort_seq_meaning;
1844 i := i+1;
1845 varname(i) := 'CHECK_STYLE';
1846 varval(i) := p_check_style;
1847 i := i+1;
1848 varname(i) := 'IS_THIRD_PARTY_CHECK';
1849 varval(i) := lv_is_third_party_check;
1850 i := i+1;
1851 varname(i) := 'PAYMENT_METHOD_3RD_PARTY';
1855 varval(i) := p_sort_sequence_3rd_party;
1852 varval(i) := p_payment_method_3rd_party;
1853 i := i+1;
1854 varname(i) := 'SORT_SEQUENCE_3RD_PARTY';
1856 i := i+1;
1857 varname(i) := 'IS_DEPOSIT_ADVICE';
1858 varval(i) := lv_is_deposit_advice;
1859 i := i+1;
1860 varname(i) := 'DEPOSIT_ADVICE_REPORT_CATEGORY';
1861 varval(i) := p_da_report_category;
1862 i := i+1;
1863 varname(i) := 'DEPOSIT_ADVICE_START_DATE_DMY';
1864 varval(i) := 'START_DATE='||p_date_paid;
1865 i := i+1;
1866 varname(i) := 'DEPOSIT_ADVICE_END_DATE_DMY';
1867 varval(i) := 'END_DATE='||p_date_paid;
1868 i := i+1;
1869 varname(i) := 'DEPOSIT_ADVICE_PAYROLL';
1870 varval(i) := 'PAYROLL_ID='||p_payroll_id;
1871 i := i+1;
1872 varname(i) := 'DEPOSIT_ADVICE_CONC_SET';
1873 varval(i) := 'CONSOLIDATION_SET_ID='||p_consolidation_set_id;
1874 i := i+1;
1875 varname(i) := 'DEPOSIT_ADVICE_SORT_SEQUENCE';
1876 varval(i) := p_da_sort_sequence;
1877 i := i+1;
1878 varname(i) := 'DEPOSIT_ADVICE_ASSIGNMENT_SET';
1879 varval(i) := p_da_assignment_set;
1880 i := i+1;
1881 varname(i) := 'DEPOSIT_ADVICE_ASGSET_DMY';
1882 varval(i) := p_assignment_set_dummy;
1883 i := i+1;
1884 varname(i) := 'DEPOSIT_ADVICE_REPORT_NAME';
1885 varval(i) := lv_da_report;
1886 i := i+1;
1887 varname(i) := 'SELECTION_CRITERION';
1888 varval(i) := p_selection_criterion;
1889 i := i+1;
1890 varname(i) := 'ELE_REG_ELEMENT_SET';
1891 varval(i) := p_ele_reg_element_set;
1892 i := i+1;
1893 varname(i) := 'ELEMENT_CLASSIFICATION';
1894 varval(i) := p_element_classification;
1895 i := i+1;
1896 varname(i) := 'ELEMENT';
1897 varval(i) := p_element;
1898 i := i+1;
1899 varname(i) := 'ELEMENT_NAME';
1900 varval(i) := lv_element_name;
1901 --- i := i+1;
1902 --- varname(i) := 'ELEMENT_REGISTER_EMPLOYEE';
1903 --- varval(i) := p_ele_reg_employee;
1904 i := i+1;
1905 varname(i) := 'EMP_ID';
1906 varval(i) := p_ele_reg_employee;
1907 i := i+1;
1908 varname(i) := 'COSTING_PROCESS';
1909 varval(i) := p_costing_process;
1910 i := i+1;
1911 varname(i) := 'START_DATE';
1912 varval(i) := p_date_earned;
1913 i := i+1;
1914 varname(i) := 'END_DATE';
1915 varval(i) := p_date_paid;
1916 i := i+1;
1917 varname(i) := 'PPA_FINDER';
1918 varval(i) := p_ppa_finder;
1919 i := i+1;
1920 varname(i) := 'USER';
1921 varval(i) := lv_contact_user_name;
1922 --- varval(i) := RequestorUsername;
1923 i := i+1;
1924 varname(i) := 'CONTACT_USERNAME';
1925 varval(i) := lv_contact_user_name;
1926 i := i+1;
1927 varname(i) := 'MESSAGE_SUBJECT1';
1928 varval(i) := l_message_subject1;
1929 i := i+1;
1930 varname(i) := 'MESSAGE_TEXT1';
1931 varval(i) := l_message_text1;
1932 i := i+1;
1933 varname(i) := 'MESSAGE_HTML_TEXT1';
1934 varval(i) := l_message_html_text1;
1935 i := i+1;
1936 varname(i) := 'MESSAGE_HTML_TEXT2';
1937 varval(i) := l_message_html_text2;
1938 i := i+1;
1939 varname(i) := 'ROLE_NAME';
1940 varval(i) := l_role_name;
1941 i := i+1;
1942 varname(i) := 'REPORTING_CURRENCY_CODE';
1943 varval(i) := lv_currency_code;
1944 i := i+1;
1945 varname(i) := 'PPA_FINDER';
1946 varval(i) := p_ppa_finder;
1947 i := i+1;
1948 varname(i) := 'TRANSFER_PPA_FINDER';
1949 varval(i) := 'TRANSFER_PPA_FINDER='||p_ppa_finder;
1950 i := i+1;
1951 varname(i) := 'TRANSFER_GRE';
1952 varval(i) := 'TRANSFER_GRE='||p_gre;
1953 i := i+1;
1954 varname(i) := 'TRANSFER_PAYROLL';
1955 varval(i) := 'TRANSFER_PAYROLL='||p_payroll_id;
1956 i := i+1;
1957 varname(i) := 'TRANSFER_CONC_SET';
1958 varval(i) := 'TRANSFER_CONC_SET='||p_consolidation_set_id;
1959 i := i+1;
1960 varname(i) := 'XFR_TRANSFER_PAYROLL_ID';
1961 varval(i) := 'TRANSFER_PAYROLL_ID='||p_payroll_id;
1962 i := i+1;
1963 varname(i) := 'XFR_TRANS_CONS_SET';
1964 varval(i) := 'TRANSFER_CONSOLIDATION_SET_ID='||p_consolidation_set_id;
1965 i := i+1;
1966 varname(i) := 'FILE_ID_MODIFIER_HIDDEN';
1967 varval(i) := 'FILE_ID_MODIFIER='||p_file_id_modifier;
1968 i := i+1;
1969 varname(i) := 'PERIOD';
1970 varval(i) := p_period;
1971 i := i+1;
1972 varname(i) := 'PERIOD_NAME';
1973 varval(i) := lv_period_name;
1974 i := i+1;
1975 varname(i) := 'P_PAYROLL_ACTION_ID';
1976 varval(i) := p_p_payroll_action_id;
1977 i := i+1;
1978 varname(i) := 'NACHA_PAYMENT_TYPE_ID';
1979 varval(i) := ln_nacha_payment_type_id;
1980 i := i+1;
1981 varname(i) := 'CHECK_PAYMENT_TYPE_ID';
1982 varval(i) := ln_check_payment_type_id;
1983 i := i+1;
1984 varname(i) := 'DATE_TIME';
1985 varval(i) := lv_date_time;
1986 i := i+1;
1987 varname(i) := 'END_DATE_LEG_PARAM';
1988 varval(i) := 'END_DATE='||p_date_earned;
1989 i := i+1;
1990 varname(i) := 'PAYROLL_LEG_PARAM';
1994 varval(i) := 'CONSOLIDATION_SET_ID='||p_consolidation_set_id;
1991 varval(i) := 'PAYROLL_ID='||p_payroll_id;
1992 i := i+1;
1993 varname(i) := 'CONC_SET_LEG_PARAM';
1995 i := i+1;
1996 varname(i) := 'PROCESS_01';
1997 varval(i) := lv_cur_process(1);
1998 i := i+1;
1999 varname(i) := 'PROCESS_02';
2000 varval(i) := lv_cur_process(2);
2001 i := i+1;
2002 varname(i) := 'PROCESS_03';
2003 varval(i) := lv_cur_process(3);
2004 i := i+1;
2005 varname(i) := 'PROCESS_04';
2006 varval(i) := lv_cur_process(4);
2007 i := i+1;
2008 varname(i) := 'PROCESS_05';
2009 varval(i) := lv_cur_process(5);
2010 i := i+1;
2011 varname(i) := 'PROCESS_06';
2012 varval(i) := lv_cur_process(6);
2013 i := i+1;
2014 varname(i) := 'PROCESS_07';
2015 varval(i) := lv_cur_process(7);
2016 i := i+1;
2017 varname(i) := 'PROCESS_08';
2018 varval(i) := lv_cur_process(8);
2019 i := i+1;
2020 varname(i) := 'PROCESS_09';
2021 varval(i) := lv_cur_process(9);
2022 i := i+1;
2023 varname(i) := 'PROCESS_10';
2024 varval(i) := lv_cur_process(10);
2025 i := i+1;
2026 varname(i) := 'PROCESS_11';
2027 varval(i) := lv_cur_process(11);
2028 i := i+1;
2029 varname(i) := 'PROCESS_12';
2030 varval(i) := lv_cur_process(12);
2031 i := i+1;
2032 varname(i) := 'PROCESS_13';
2033 varval(i) := lv_cur_process(13);
2034 i := i+1;
2035 varname(i) := 'PROCESS_14';
2036 varval(i) := lv_cur_process(14);
2037 i := i+1;
2038 varname(i) := 'PROCESS_15';
2039 varval(i) := lv_cur_process(15);
2040 i := i+1;
2041 varname(i) := 'PROCESS_16';
2042 varval(i) := lv_cur_process(16);
2043 i := i+1;
2044 varname(i) := 'PROCESS_17';
2045 varval(i) := lv_cur_process(17);
2046 i := i+1;
2047 varname(i) := 'PROCESS_18';
2048 varval(i) := lv_cur_process(18);
2049 i := i+1;
2050 varname(i) := 'PROCESS_19';
2051 varval(i) := lv_cur_process(19);
2052 i := i+1;
2053 varname(i) := 'PROCESS_20';
2054 varval(i) := lv_cur_process(20);
2055 i := i+1;
2056 varname(i) := 'PROCESS_21';
2057 varval(i) := lv_cur_process(21);
2058 i := i+1;
2059 varname(i) := 'BEE_BATCH_NAME';
2060 varval(i) := lv_batch_name;
2061 i := i+1;
2062 varname(i) := 'PAY_EXCEPTION_RPT_MSG_TEXT1';
2063 varval(i) := l_pay_excep_rpt_message_text1;
2064 i := i+1;
2065 varname(i) := 'PAY_EXCEPTION_RPT_MSG_HTML1';
2066 varval(i) := l_pay_excep_rpt_message_html1;
2067 i := i+1;
2068 varname(i) := 'RETRO_START_DATE_DUMMY';
2069 varval(i) := 'START_DATE='||p_retro_start_date;
2070 i := i+1;
2071 varname(i) := 'ELEMENT_REGISTER_RPT_MSG_TEXT1';
2072 varval(i) := l_ele_reg_rpt_message_text1;
2073 i := i+1;
2074 varname(i) := 'ELEMENT_REGISTER_RPT_MSG_HTML1';
2075 varval(i) := l_ele_reg_rpt_message_html1;
2076 i := i+1;
2077 varname(i) := 'IS_ELEMENT_REGISTER_REPORT';
2078 varval(i) := lv_is_ele_reg;
2079
2080 i := i+1;
2081 varname(i) := 'IS_RESPONSE_REQUIRED';
2082 varval(i) := lv_isResponseRequired;
2083
2084 i := i+1;
2085 varname(i) := 'PAYROLL_WF_NOTIFY_ACTION';
2086 varval(i) := lv_Payroll_WF_Notify_Action;
2087
2088 i := i+1;
2089 varname(i) := 'IS_PAYMENT_WAIT';
2090 varval(i) := lv_isPaymentWait;
2091
2092 i := i+1;
2093 varname(i) := 'LEGISLATION_CODE';
2094 varval(i) := p_legislation_code;
2095
2096 i := i+1;
2097 varname(i) := 'IS_EXCEPTION_GROUP';
2098 varval(i) := p_is_exception_group;
2099
2100 i := i+1;
2101 varname(i) := 'IS_EXCEPTION_REPORT';
2102 varval(i) := p_is_exception_report;
2103
2104 i := i+1;
2105 varname(i) := 'PREPAY_PYMENT_MTHD_OVRIDE_NAME';
2106 varval(i) := lv_pre_ovr_pymt_name;
2107 i := i+1;
2108 varname(i) := 'RETRO_EVENT_GROUP_NAME';
2109 varval(i) := lv_event_group_name;
2110 i := i+1;
2111 varname(i) := 'OVERRIDING_CHEQUE_DATE';
2112 varval(i) := p_overriding_cheque_date;
2113
2114 -- Costing Summary
2115
2116 varname(i) := 'IS_COSTING_SUMMARY';
2117 varval(i) := lv_is_costing_summary;
2118 i := i+1;
2119 varname(i) := 'SUMMARY_COSTING_ACCRUALS';
2120 varval(i) := p_cost_summary_accruals;
2121 i := i+1;
2122 varname(i) := 'SUMMARY_COSTING_ACCRUALS_NAME';
2123 varval(i) := lv_cost_summary_accruals;
2124 i := i+1;
2125 varname(i) := 'SUMMARY_COSTING_FILE_FORMAT';
2126 varval(i) := p_cost_summary_file_out;
2127 i := i+1;
2128 varname(i) := 'SUMMARY_COSTING_FILE_FMT_NAME';
2129 varval(i) := lv_cost_summary_file_out;
2130 i := i+1;
2131
2132 -- Costing Detail
2133
2134 varname(i) := 'IS_COSTING_DETAIL';
2135 varval(i) := lv_is_costing_detail;
2136 i := i+1;
2137 varname(i) := 'DETAIL_COSTING_CRITERION';
2138 varval(i) := p_cost_detail_selection;
2139 i := i+1;
2143 varname(i) := 'DETAIL_COSTING_ELEMENT_SET';
2140 varname(i) := 'DETAIL_COSTING_IS_ELEMENT_SET';
2141 varval(i) := p_cost_detail_is_ele_set;
2142 i := i+1;
2144 varval(i) := p_cost_detail_ele_set;
2145 i := i+1;
2146 varname(i) := 'DETAIL_COSTING_ELE_SET_NAME';
2147 varval(i) := lv_cost_detail_ele_set;
2148 i := i+1;
2149 varname(i) := 'DETAIL_COSTING_IS_ELEMENT_C';
2150 varval(i) := p_cost_detail_is_class;
2151 i := i+1;
2152 varname(i) := 'DETAIL_COSTING_ELEMENT_C';
2153 varval(i) := p_cost_detail_class;
2154 i := i+1;
2155 varname(i) := 'DETAIL_COSTING_ELEMENT_C_NAME';
2156 varval(i) := lv_cost_detail_ele_class;
2157 i := i+1;
2158 varname(i) := 'DETAIL_COSTING_IS_ELEMENT';
2159 varval(i) := p_cost_detail_is_element;
2160 i := i+1;
2161 varname(i) := 'DETAIL_COSTING_ELEMENT';
2162 varval(i) := p_cost_detail_element;
2163 i := i+1;
2164 varname(i) := 'DETAIL_COSTING_ELEMENT_NAME';
2165 varval(i) := lv_cost_detail_ele_name;
2166 i := i+1;
2167 varname(i) := 'DETAIL_COSTING_ASG_SET';
2168 varval(i) := p_cost_detail_asg_set;
2169 i := i+1;
2170 varname(i) := 'DETAIL_COSTING_ASG_SET_NAME';
2171 varval(i) := lv_cost_detail_asg_set;
2172 i := i+1;
2173 varname(i) := 'DETAIL_COSTING_ACCRUALS';
2174 varval(i) := p_cost_detail_accruals;
2175 i := i+1;
2176 varname(i) := 'DETAIL_COSTING_ACCRUALS_NAME';
2177 varval(i) := lv_cost_detail_accruals;
2178 i := i+1;
2179
2180
2181 i := i+1;
2182 num_varname(i) := 'P_BUSINESS_GROUP_ID';
2183 num_varvalue(i) := p_business_group_id;
2184 i := i+1;
2185 num_varname(i) := 'BATCH_ID';
2186 num_varvalue(i) := p_batch_id;
2187 i := i+1;
2188 num_varname(i) := 'START_CHECK_NUMBER';
2189 num_varvalue(i) := to_number(p_start_check_number);
2190 i := i+1;
2191 num_varname(i) := 'END_CHECK_NUMBER';
2192 num_varvalue(i) := to_number(p_end_check_number);
2193 i := i+1;
2194 num_varname(i) := 'START_CHECK_NUMBER_3RD_PARTY';
2195 num_varvalue(i) := to_number(p_start_check_num_3rd_party);
2196 i := i+1;
2197 num_varname(i) := 'END_CHECK_NUMBER_3RD_PARTY';
2198 num_varvalue(i) := to_number(p_end_check_num_3rd_party);
2199 i := i+1;
2200 num_varname(i) := 'USER_ID';
2201 num_varvalue(i) := l_user_id;
2202 i := i+1;
2203 num_varname(i) := 'APPLICATION_ID';
2204 num_varvalue(i) := l_resp_appl_id;
2205 i := i+1;
2206 num_varname(i) := 'RESPONSIBILITY_ID';
2207 num_varvalue(i) := l_resp_id;
2208 i := i+1;
2209 num_varname(i) := 'ORG_ID';
2210 num_varvalue(i) := l_org_id;
2211 i := i+1;
2212 num_varname(i) := 'PER_SECURITY_PROFILE_ID';
2213 num_varvalue(i) := l_profile_per_sec_id;
2214 i := i+1;
2215 num_varname(i) := 'SECURITY_GROUP_ID';
2216 num_varvalue(i) := l_profile_sec_grp_id;
2217
2218 for j in varname.first..varname.last loop
2219 hr_utility.trace( 'J = '|| j );
2220 hr_utility.trace( 'varname = '||varname(j));
2221 hr_utility.trace( 'varval = '||varval(j));
2222 end loop;
2223
2224 Wf_Engine.SetItemAttrTextArray(ItemType, ItemKey, varname, varval);
2225 hr_utility.trace( 'Total Var Kount = '||to_char(varname.COUNT));
2226
2227 for p in num_varname.first..num_varname.last loop
2228 hr_utility.trace( 'p = '|| p );
2229 hr_utility.trace( 'num_varname = '||num_varname(p));
2230 hr_utility.trace( 'num_varval = '||num_varvalue(p));
2231 end loop;
2232
2233 Wf_Engine.SetItemAttrNumberArray(ItemType, ItemKey, num_varname, num_varvalue);
2234
2235 hr_utility.trace( 'Total Num Kount = '||to_char(num_varname.COUNT));
2236 exception
2237 when OTHERS then
2238 hr_utility.trace('In exception: OTHERS of TextArray');
2239 raise;
2240 end;
2241
2242 /*
2243 wf_engine.SetItemAttrText ( itemtype => ItemType,
2244 itemkey => ItemKey,
2245 aname => 'USER_DISPLAY_NAME',
2246 avalue => wf_directory.GetRoleDisplayName(RequestorUsername));
2247
2248 wf_engine.SetItemOwner ( itemtype => ItemType,
2249 itemkey => ItemKey,
2250 owner => ProcessOwner);
2251
2252
2253 wf_engine.SetItemAttrText ( itemtype => ItemType,
2254 itemkey => ItemKey,
2255 aname => 'MESSAGE_SUBJECT1',
2256 avalue => l_message_subject1);
2257
2258
2259 wf_engine.SetItemAttrText ( itemtype => ItemType,
2260 itemkey => ItemKey,
2261 aname => 'MESSAGE_TEXT1',
2262 avalue => l_message_text1);
2263
2264 hr_utility.trace('B4 SetItemAttrText : l_role_name = ' || l_role_name);
2265 wf_engine.SetItemAttrText ( itemtype => ItemType,
2266 itemkey => ItemKey,
2267 aname => 'ROLE_NAME',
2268 avalue => l_role_name);
2269
2270 wf_engine.SetItemAttrText ( itemtype => ItemType,
2271 itemkey => ItemKey,
2272 aname => 'APPLNAME',
2273 avalue => 'PAY');
2274
2278 hr_utility.trace('b4 starting process');
2275 hr_utility.trace('APPLNAME ');
2276 */
2277
2279
2280 wf_engine.StartProcess ( ItemType => ItemType,
2281 ItemKey => ItemKey );
2282 hr_utility.trace('a4 starting process');
2283
2284
2285
2286 exception
2287 when others then
2288 WF_CORE.Context('PAY_WF_PKG', 'StartProcess',ItemType, RequestorUsername, ProcessOwner, Workflowprocess);
2289
2290 error;
2291 RAISE;
2292
2293 end StartProcess;
2294
2295
2296 procedure StartProcess (
2297 p_business_group_id number ,
2298 p_batch_id number ,
2299 p_payroll_id varchar2,
2300 p_payroll_id_dummy varchar2 ,
2301 p_consolidation_set_id varchar2,
2302 p_consolidation_set_id_dummy varchar2 ,
2303 p_date_earned varchar2,
2304 p_date_paid varchar2,
2305 p_period varchar2,
2306 p_payroll_element_set_name varchar2,
2307 p_payroll_assignment_set_name varchar2,
2308 p_payroll_run_type varchar2,
2309 p_event_group varchar2,
2310 p_retro_valid_template_list varchar2,--bug 12704242
2311 p_retro_template_name varchar2,--bug 12704242
2312 p_retro_assignment_set_name varchar2,
2313 p_retro_generate_report varchar2,--bug 12704242
2314 p_retro_assignment_set_dummy varchar2,
2315 p_retro_element_set varchar2,
2316 p_retro_start_date varchar2,
2317 p_retropay_entry_creation_date varchar2, --bug 12704242
2318 p_effective_date varchar2,
2319 p_gre varchar2,
2320 p_gre_id_dummy varchar2,
2321 p_p_payroll_action_id varchar2,
2322 p_organization varchar2,
2323 p_location varchar2,
2324 p_legislation_code varchar2,
2325 p_select_report_or_group varchar2,
2326 p_is_exception_group varchar2,
2327 p_exception_group_name varchar2,
2328 p_exception_group_name_dummy varchar2,
2329 p_is_exception_report varchar2,
2330 p_exception_report_name varchar2,
2331 p_exception_report_name_dummy varchar2,
2332 p_over_ride_variance_type varchar2,
2333 p_over_ride_varianc_type_dumy varchar2,
2334 p_over_ride_variance_value varchar2,
2335 p_over_ride_varinc_value_dumy varchar2,
2336 p_payment_method_override varchar2,
2337 p_nacha_payment_type varchar2,
2338 p_nacha_payment_method varchar2,
2339 p_deposit_date_override varchar2,
2340 p_file_id_modifier varchar2,
2341 p_file_id_modifier_check varchar2,
2342 p_thrid_party_check varchar2,
2343 p_check_writer_payment_type varchar2,
2344 p_check_style varchar2,
2345 p_check_writer_payment_method varchar2,
2346 p_check_writer_sort_sequence varchar2,
2347 p_start_check_number varchar2,
2348 p_end_check_number varchar2,
2349 p_overriding_cheque_date varchar2,
2350 p_payment_method_3rd_party varchar2,
2351 p_sort_sequence_3rd_party varchar2,
2352 p_start_check_num_3rd_party varchar2,
2353 p_end_check_num_3rd_party varchar2,
2354 p_da_report_category varchar2,
2355 p_da_sort_sequence varchar2,
2356 p_da_assignment_set varchar2,
2357 p_assignment_set_dummy varchar2,
2358 p_selection_criterion varchar2,
2359 p_is_element_set varchar2,
2360 p_ele_reg_element_set varchar2,
2361 p_is_element_classification varchar2,
2362 p_element_classification varchar2,
2363 p_is_element_name varchar2,
2364 p_element varchar2,
2365 p_ele_reg_employee varchar2,
2366 p_costing_process varchar2,
2367 p_dummy_cost_run varchar2,
2368 p_cost_summary_accruals varchar2,
2369 p_cost_summary_file_out varchar2,
2370 p_cost_detail_selection varchar2,
2371 p_cost_detail_is_ele_set varchar2,
2372 p_cost_detail_ele_set varchar2,
2373 p_cost_detail_is_class varchar2,
2374 p_cost_detail_class varchar2,
2375 p_cost_detail_is_element varchar2,
2376 p_cost_detail_element varchar2,
2377 p_cost_detail_asg_set varchar2,
2378 p_cost_detail_accruals varchar2,
2382 p_ppa_finder_pqp varchar2,
2379 p_start_date varchar2,
2380 p_end_date varchar2,
2381 p_ppa_finder varchar2,
2383 p_action_parameter_group varchar2, -- bug 12594530
2384 ProcessDesc varchar2,
2385 RequestorUsername varchar2,
2386 ProcessOwner in varchar2,
2387 Workflowprocess in varchar2,
2388 item_type in varchar2,
2389 item_key in varchar2
2390 ) is
2391
2392 ItemType varchar2(30);
2393 ItemKey varchar2(30);
2394 ItemUserKey varchar2(80);
2395 l_business_group_id number(30);
2396 l_payroll_id number(16);
2397 l_consolidation_set_id number(16);
2398 ln_asignment_id NUMBER(30);
2399
2400 l_message_subject1 varchar2(240);
2401 l_message_text1 varchar2(3200) ;
2402 l_message_html_text1 varchar2(3200) ;
2403 l_message_html_text2 varchar2(3200) ;
2404 l_pay_excep_rpt_message_text1 varchar2(3200) ;
2405 l_pay_excep_rpt_message_html1 varchar2(3200) ;
2406 l_ele_reg_rpt_message_text1 varchar2(3200) ;
2407 l_ele_reg_rpt_message_html1 varchar2(3200) ;
2408 l_current_user varchar2(40);
2409 l_current_user_display_name varchar2(40);
2410 l_app_user_name varchar2(40);
2411 l_orig_system varchar2(40);
2412 l_orig_system_id varchar2(40);
2413 l_role_name varchar2(50);
2414 l_role_display_name varchar2(50);
2415 lv_currency_code varchar2(5);
2416 lv_is_batch varchar2(1);
2417 lv_is_retro_ntfy varchar2(1);
2418 lv_is_nacha varchar2(1);
2419 lv_is_check varchar2(1);
2420 lv_is_third_party_check varchar2(1);
2421 lv_is_deposit_advice varchar2(1);
2422 lv_is_payroll_exception_report varchar2(1);
2423 lv_is_ele_reg varchar2(1);
2424 lv_isResponseRequired varchar2(1) ;
2425 lv_isPaymentWait varchar2(1) ;
2426 lv_Payroll_WF_Notify_Action Varchar2(30);
2427 ln_nacha_payment_type_id Varchar2(30);
2428 ln_check_payment_type_id Varchar2(30);
2429 lv_da_report Varchar2(30);
2430 lv_retropay_definition varchar2(80);--bug 12704242
2431
2432 lv_payroll_name varchar2(80);
2433 lv_consolidation_set_name varchar2(80);
2434 lv_gre_name varchar2(80);
2435 lv_org_name varchar2(80);
2436 lv_loc_name varchar2(80);
2437 lv_pre_ovr_pymt_name varchar2(80);
2438 lv_check_pymt_name varchar2(80);
2439 lv_nacha_pymt_name varchar2(80);
2440 lv_event_group_name varchar2(80);
2441 lv_loc_name varchar2(80);
2442 lv_exc_grp_name varchar2(80);
2443 lv_grp_orv_type varchar2(80);
2444 lv_grp_orv_value varchar2(80);
2445 lv_exc_rep_name varchar2(80);
2446 lv_orv_type varchar2(80);
2447 lv_orv_value varchar2(80);
2448 lv_check_sort_seq_meaning varchar2(80);
2449 lv_period_name varchar2(80);
2450 lv_element_name varchar2(80);
2451 lv_batch_name varchar2(80);
2452
2453 --l_profile_per_sec_id VARCHAR2(100);
2454 l_profile_per_sec_id Number(15);
2455 l_profile_per_bg VARCHAR2(100);
2456 --l_profile_sec_grp_id VARCHAR2(100);
2457 l_profile_sec_grp_id Number(15);
2458 l_profile_user_name VARCHAR2(100);
2459 lv_prc_list varchar2(3200);
2460 lv_prc_list_html varchar2(3200);
2461 lv_date_time varchar2(80);
2462 l_proc varchar2(80);
2463 lv_contact_user_name VARCHAR2(80);
2464 l_user_id NUMBER;
2465 l_resp_id NUMBER;
2466 l_resp_appl_id NUMBER;
2467 l_org_id NUMBER;
2468 l_per_sec_id NUMBER;
2469 l_sec_grp_id NUMBER;
2470 i_count number;
2471
2472 lv_is_costing_summary varchar2(1);
2473 lv_is_costing_detail varchar2(1);
2474 lv_cost_summary_accruals varchar2(80);
2475 lv_cost_summary_file_out varchar2(80);
2476 lv_cost_detail_ele_set varchar2(80);
2477 lv_cost_detail_ele_class varchar2(80);
2478 lv_cost_detail_ele_name varchar2(80);
2479 lv_cost_detail_asg_set varchar2(80);
2480 lv_cost_detail_accruals varchar2(80);
2481
2482 varname Wf_Engine.NameTabTyp;
2483 varval Wf_Engine.TextTabTyp;
2484
2485 num_varname Wf_Engine.NameTabTyp;
2486 num_varvalue Wf_Engine.NumTabTyp;
2487
2488 TYPE char80_table IS TABLE OF VARCHAR2(80)
2489 INDEX BY BINARY_INTEGER;
2490
2491 lv_conc_prog_name char80_table;
2492 lv_cur_process char80_table;
2493 i number;
2494 j number;
2495 k number;
2496 l number;
2497 n number;
2498 p number;
2499
2500
2501 cursor cur_costing_summary_accruals is
2502 select meaning
2503 from hr_lookups
2504 where lookup_code = p_cost_summary_accruals
2505 and lookup_type = 'PAY_PAYRPCBR';
2506
2507 cursor cur_costing_summary_file_out is
2508 select meaning
2509 from fnd_common_lookups
2510 where lookup_code = p_cost_summary_file_out
2511 and lookup_type = 'REPORT_OUTPUT_TYPE'
2512 and application_id = 801
2513 and enabled_flag = 'Y';
2514
2515 cursor cur_costing_detail_ele_set is
2516 select element_set_name
2517 from pay_element_sets
2518 where element_set_id = to_number(p_cost_detail_ele_set)
2519 and element_set_type = 'C';
2520
2521 cursor cur_costing_detail_ele_class is
2522 select classification_name
2523 from pay_element_classifications
2527 select tl.element_name
2524 where classification_id = to_number(p_cost_detail_class);
2525
2526 cursor cur_costing_detail_ele_name is
2528 from pay_element_types_f el, pay_element_types_f_tl tl
2529 where el.element_type_id = to_number(p_cost_detail_element)
2530 and el.element_type_id = tl.element_type_id
2531 and tl.language = userenv('LANG')
2532 and fnd_date.canonical_to_date(p_date_earned)
2533 between el.effective_start_date and el.effective_end_date;
2534
2535 cursor cur_costing_detail_asg_set is
2536 select assignment_set_name
2537 from hr_assignment_sets
2538 where assignment_set_id = to_number(p_cost_detail_asg_set);
2539
2540 cursor cur_costing_detail_accruals is
2541 select meaning
2542 from hr_lookups
2543 where lookup_code = p_cost_detail_accruals
2544 and lookup_type = 'PAY_PAYRPCBR';
2545
2546 --bug 12704242
2547 cursor cur_retropay_definition is
2548 select prd.retro_definition_id
2549 from pay_retro_definitions prd,
2550 per_business_groups p
2551 where prd.legislation_code = p.legislation_code
2552 and p.business_group_id = p_business_group_id;
2553
2554
2555 begin
2556
2557 -- Initialize Variables
2558 l_proc := gv_package||'.StartProcess';
2559 ItemType := item_type;
2560 ItemKey := item_key;
2561 ItemUserKey := ProcessDesc;
2562 l_business_group_id := p_business_group_id;
2563 l_payroll_id := to_number(p_payroll_id);
2564 l_consolidation_set_id := to_number(p_consolidation_set_id);
2565 lv_is_batch := 'N';
2566 lv_is_retro_ntfy := 'N';
2567 lv_is_nacha := 'N';
2568 lv_is_check := 'N';
2569 lv_is_third_party_check := 'N';
2570 lv_is_deposit_advice := 'N';
2571 lv_is_payroll_exception_report := 'N';
2572 lv_is_ele_reg := 'N';
2573 --hhh
2574
2575 -- Initialize the Process and Report List.
2576 lv_conc_prog_name(1) := 'BEE Batch Process (Transfer)';
2577 lv_conc_prog_name(2) := 'Retro-Notifications Report (Enhanced) - PDF';
2578 lv_conc_prog_name(3) := 'Retropay (Enhanced)';
2579 lv_conc_prog_name(4) := 'Payroll Process';
2580 lv_conc_prog_name(5) := 'US Gross to Net Summary';
2581 lv_conc_prog_name(6) := 'Payroll Message Report';
2582 lv_conc_prog_name(7) := 'Employee Assignments Not Processed';
2583 lv_conc_prog_name(8) := 'Payroll Exception Report';
2584 lv_conc_prog_name(9) := 'Federal and State Tax Remittance Report';
2585 lv_conc_prog_name(10) := 'Pre Payments';
2586 lv_conc_prog_name(11) := 'NACHA';
2587 lv_conc_prog_name(12) := 'External Process Archive';
2588 lv_conc_prog_name(13) := 'Check Writer';
2589 lv_conc_prog_name(14) := 'Third Party Check Writer';
2590 lv_conc_prog_name(15) := 'Deposit Advice';
2591 lv_conc_prog_name(16) := 'Element Register Report';
2592 lv_conc_prog_name(17) := 'Payment Register Report';
2593 lv_conc_prog_name(18) := 'Third Party Payment Register Report';
2594 lv_conc_prog_name(19) := 'Costing Process';
2595 lv_conc_prog_name(20) := 'Costing Summary Report';
2596 lv_conc_prog_name(21) := 'Costing Detail Report';
2597
2598 -- wf_core.clear;
2599
2600 -- make sure the process terminated by aborting it first
2601 -- begin
2602 -- wf_engine.abortprocess(ItemType, ItemKey);
2603 -- exception
2604 -- when others then null;
2605 -- end;
2606
2607 hr_utility.trace('In StartProcess Item Type is : '|| ItemType);
2608 hr_utility.trace('In StartProcess Item Key is : '|| ItemKey);
2609 hr_utility.trace('WF process name is : '|| Workflowprocess);
2610
2611 begin
2612 select to_char(sysdate,'DD-MON-YYYY') ||' '|| to_char(sysdate,'HH24:MI:SS') into lv_date_time from dual;
2613 end;
2614
2615 hr_utility.trace('b4 create_process');
2616
2617 wf_engine.createProcess( ItemType => ItemType,
2618 ItemKey => ItemKey,
2619 process => Workflowprocess);
2620
2621 wf_engine.SetItemUserKey( ItemType => ItemType,
2622 ItemKey => ItemKey,
2623 UserKey => ItemUserKey);
2624 hr_utility.trace('In Start Process b4 HR_SIGNON.Initialize_HR_Security');
2625 HR_SIGNON.Initialize_HR_Security;
2626 hr_utility.trace('A4 HR_SIGNON.Initialize_HR_Security of Start ');
2627
2628 l_user_id:= FND_GLOBAL.USER_ID;
2629 l_resp_appl_id:= FND_GLOBAL.RESP_APPL_ID;
2630 l_resp_id:= FND_GLOBAL.RESP_ID;
2631 l_org_id:= FND_GLOBAL.ORG_ID;
2632
2633 l_per_sec_id := FND_GLOBAL.PER_SECURITY_PROFILE_ID;
2634 l_sec_grp_id := FND_GLOBAL.SECURITY_GROUP_ID;
2635
2636 hr_utility.trace('l_user_id = ' || l_user_id);
2637 hr_utility.trace('l_resp_appl_id = ' || l_resp_appl_id);
2638 hr_utility.trace('l_resp_id = ' || l_resp_id);
2639 hr_utility.trace('l_org_id = ' || l_org_id);
2640 hr_utility.trace('l_per_sec_id = ' || l_per_sec_id);
2641 hr_utility.trace('l_sec_grp_id = ' || l_sec_grp_id);
2642 hr_utility.trace('BG = ' || to_char(p_business_group_id));
2643 hr_utility.trace('PAYMENT TYPE = ' || p_nacha_payment_type);
2644
2645
2646 /*
2647 fnd_global.apps_initialize(l_user_id,l_resp_id,l_resp_appl_id);
2648 */
2649
2650 Begin
2651 select currency_code into lv_currency_code
2652 from per_business_groups
2653 where business_group_id = p_business_group_id;
2654 Exception when others then
2655 hr_utility.trace('In exception: OTHERS ');
2656 null;
2657
2658 END;
2659
2660
2661 Begin
2662 SELECT to_char(PPT.PAYMENT_TYPE_ID) into ln_nacha_payment_type_id
2663 FROM PAY_PAYMENT_TYPES PPT,
2664 PER_BUSINESS_GROUPS BG
2668 AND BG.BUSINESS_GROUP_ID= p_business_group_id
2665 WHERE ( ((PPT.TERRITORY_CODE IS NOT NULL
2666 AND PPT.TERRITORY_CODE=BG.LEGISLATION_CODE)
2667 OR PPT.TERRITORY_CODE IS NULL)
2669 AND PPT.PAYMENT_TYPE_NAME = 'NACHA'
2670 );
2671 Exception when others then
2672 hr_utility.trace('In exception: OTHERS ');
2673 null;
2674
2675 END;
2676
2677
2678 Begin
2679 SELECT to_char(PPT.PAYMENT_TYPE_ID)
2680 into ln_check_payment_type_id
2681 FROM PAY_PAYMENT_TYPES PPT,
2682 PER_BUSINESS_GROUPS BG
2683 WHERE ( ((PPT.TERRITORY_CODE IS NOT NULL
2684 AND PPT.TERRITORY_CODE=BG.LEGISLATION_CODE)
2685 OR PPT.TERRITORY_CODE IS NULL)
2686 AND BG.BUSINESS_GROUP_ID= p_business_group_id
2687 AND PPT.PAYMENT_TYPE_NAME = 'Check'
2688 );
2689 Exception when others then
2690 hr_utility.trace('In exception: OTHERS ');
2691 null;
2692
2693 END;
2694
2695
2696 Begin
2697 select report_name into lv_da_report
2698 from pay_report_format_mappings_f
2699 where report_type = 'DAR'
2700 and report_category = p_da_report_category;
2701 Exception when others then
2702 hr_utility.trace('In exception: OTHERS ');
2703 null;
2704
2705 END;
2706
2707
2708 Begin
2709 -- Payroll Name
2710 select payroll_name into lv_payroll_name
2711 from pay_all_payrolls_f
2712 where payroll_id = p_payroll_id
2713 and fnd_date.canonical_to_date(p_date_earned)
2714 between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
2715
2716 hr_utility.trace('lv_payroll_name = ' || lv_payroll_name);
2717 Exception when others then
2718 hr_utility.trace('In exception: OTHERS ');
2719 null;
2720
2721 END;
2722
2723 Begin
2724 -- Consolidation Set Name
2725 select consolidation_set_name into lv_consolidation_set_name
2726 from pay_consolidation_sets
2727 where consolidation_set_id = p_consolidation_set_id;
2728
2729 hr_utility.trace('lv_consolidation_set_name = ' || lv_consolidation_set_name);
2730 Exception when others then
2731 hr_utility.trace('In exception: OTHERS ');
2732 null;
2733
2734 END;
2735
2736 Begin
2737 -- GRE Name
2738 select name into lv_gre_name
2739 from hr_tax_units_v
2740 where tax_unit_id = to_number(p_gre);
2741 hr_utility.trace('lv_gre_name = ' || lv_gre_name);
2742
2743 Exception when others then
2744 hr_utility.trace('In exception: OTHERS ');
2745 null;
2746
2747 END;
2748
2749 Begin
2750 -- Period Name
2751 select PERIOD_NAME into lv_period_name
2752 from per_time_periods
2753 where time_period_id = p_period;
2754 hr_utility.trace('lv_period_name = ' || lv_period_name);
2755
2756 Exception when others then
2757 hr_utility.trace('In exception: OTHERS ');
2758 null;
2759
2760 END;
2761
2762 Begin
2763 -- Event Group Name
2764 select event_group_name
2765 into lv_event_group_name
2766 from pay_event_groups
2767 where event_group_id = substr(p_event_group,12,length(p_event_group));
2768
2769 hr_utility.trace('lv_event_group_name = ' || lv_event_group_name);
2770
2771 Exception when others then
2772 hr_utility.trace('In exception: OTHERS ');
2773 null;
2774
2775 END;
2776
2777 Begin
2778 -- Nacha Org Payment Method Name
2779 select ORG_PAYMENT_METHOD_NAME
2780 into lv_nacha_pymt_name
2781 from pay_org_payment_methods_f
2782 where business_group_id = p_business_group_id
2783 and org_payment_method_id = p_nacha_payment_method
2784 and fnd_date.canonical_to_date(p_date_earned)
2785 between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
2786 hr_utility.trace('lv_nacha_pymt_name = ' || lv_nacha_pymt_name);
2787
2788 Exception when others then
2789 hr_utility.trace('In exception: OTHERS Nacha Org Payment Method Name ');
2790 null;
2791
2792 END;
2793
2794 Begin
2795 -- Check Org Payment Method Name
2796 select ORG_PAYMENT_METHOD_NAME
2797 into lv_check_pymt_name
2798 from pay_org_payment_methods_f
2799 where business_group_id = p_business_group_id
2800 and org_payment_method_id = p_check_writer_payment_method
2801 and fnd_date.canonical_to_date(p_date_earned)
2802 between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
2803 hr_utility.trace('lv_check_pymt_name = ' || lv_check_pymt_name);
2804 Exception when others then
2805 hr_utility.trace('In exception: OTHERS of Check Org Payment Method Name ');
2806 null;
2807
2808 END;
2809
2810 Begin
2811 -- Pre Org Payment Method Name Override
2812 select ORG_PAYMENT_METHOD_NAME
2813 into lv_pre_ovr_pymt_name
2814 from pay_org_payment_methods_f
2815 where business_group_id = p_business_group_id
2816 and org_payment_method_id = p_payment_method_override
2817 and fnd_date.canonical_to_date(p_date_earned)
2818 between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
2819 hr_utility.trace('lv_pre_ovr_pymt_name = ' || lv_pre_ovr_pymt_name);
2820 Exception when others then
2821 hr_utility.trace('In exception : OTHERS Pre Org Payment Method Name Override');
2822 null;
2823
2824 END;
2828 fetch cur_costing_summary_accruals into lv_cost_summary_accruals;
2825
2826 begin
2827 open cur_costing_summary_accruals;
2829 close cur_costing_summary_accruals;
2830
2831 hr_utility.trace('Costing Summary Accruals = ' || lv_cost_summary_accruals);
2832
2833 open cur_costing_summary_file_out;
2834 fetch cur_costing_summary_file_out into lv_cost_summary_file_out;
2835 close cur_costing_summary_file_out;
2836
2837 hr_utility.trace('Costing Summary File Format = ' || lv_cost_summary_file_out);
2838
2839 open cur_costing_detail_ele_set;
2840 fetch cur_costing_detail_ele_set into lv_cost_detail_ele_set;
2841 close cur_costing_detail_ele_set;
2842
2843 hr_utility.trace('Costing Detail Element Set = ' || lv_cost_detail_ele_set);
2844
2845 open cur_costing_detail_ele_name;
2846 fetch cur_costing_detail_ele_name into lv_cost_detail_ele_name;
2847 close cur_costing_detail_ele_name;
2848
2849 hr_utility.trace('Costing Detail Element Name = ' || lv_cost_detail_ele_name);
2850
2851 open cur_costing_detail_ele_class;
2852 fetch cur_costing_detail_ele_class into lv_cost_detail_ele_class;
2853 close cur_costing_detail_ele_class;
2854
2855 hr_utility.trace('Costing Detail Element Classification = ' || lv_cost_detail_ele_class);
2856
2857 open cur_costing_detail_asg_set;
2858 fetch cur_costing_detail_asg_set into lv_cost_detail_asg_set;
2859 close cur_costing_detail_asg_set;
2860
2861 hr_utility.trace('Costing Detail Assignment Set = ' || lv_cost_detail_asg_set);
2862
2863 open cur_costing_detail_accruals;
2864 fetch cur_costing_detail_accruals into lv_cost_detail_accruals;
2865 close cur_costing_detail_accruals;
2866
2867 hr_utility.trace('Costing Detail Accruals = ' || lv_cost_detail_accruals);
2868
2869 -- bug 12704242
2870 open cur_retropay_definition;
2871 fetch cur_retropay_definition into lv_retropay_definition;
2872 close cur_retropay_definition;
2873
2874 hr_utility.trace('Retro Definition id = ' || lv_retropay_definition);
2875
2876 END;
2877
2878
2879 Begin
2880 -- Check lv_isResponseRequired
2881 select nvl(parameter_value,'WAIT')
2882 into lv_Payroll_WF_Notify_Action
2883 from pay_action_parameters
2884 where parameter_name = 'PAYROLL_WF_NOTIFY_ACTION';
2885
2886 hr_utility.trace('lv_Payroll_WF_Notify_Action = ' || lv_Payroll_WF_Notify_Action);
2887 If lv_Payroll_WF_Notify_Action = 'WAIT' then
2888 lv_isResponseRequired := 'Y';
2889 lv_isPaymentWait := 'Y';
2890 elsif lv_Payroll_WF_Notify_Action = 'NOWAIT' then
2891 lv_isResponseRequired := 'N';
2892 lv_isPaymentWait := 'N';
2893 elsif lv_Payroll_WF_Notify_Action = 'PAYMENTWAIT' then
2894 lv_isResponseRequired := 'N';
2895 lv_isPaymentWait := 'Y';
2896 end if;
2897
2898 hr_utility.trace('lv_isResponseRequire = ' || lv_isResponseRequired);
2899 hr_utility.trace('lv_isPaymentWait = ' || lv_isPaymentWait);
2900
2901 Exception when others then
2902 hr_utility.trace('In exception: OTHERS ');
2903 lv_isResponseRequired := 'Y';
2904 lv_isPaymentWait := 'Y';
2905 hr_utility.trace('lv_isResponseRequire = ' || lv_isResponseRequired);
2906
2907 END;
2908
2909 Begin
2910 -- Check Sorting Sequence
2911 select meaning
2912 into lv_check_sort_seq_meaning
2913 from fnd_common_lookups
2914 where lookup_type = 'CHEQUE_PROCEDURE'
2915 and lookup_code = p_check_writer_sort_sequence;
2916
2917 hr_utility.trace('lv_check_sort_seq_meaning = ' || lv_check_sort_seq_meaning);
2918 Exception when others then
2919 hr_utility.trace('In exception: OTHERS ');
2920 null;
2921
2922 END;
2923
2924 Begin
2925 -- Exception REports Information
2926 select per.exception_report_name ,
2927 hr_general.decode_lookup('PQP_VARIANCE_TYPES',per.variance_type) ovt,
2928 to_char(per.VARIANCE_VALUE)
2929 into lv_exc_rep_name,lv_orv_type,lv_orv_value
2930 from pqp_exception_reports per
2931 where legislation_code = 'US'
2932 and per.exception_report_id = p_exception_report_name;
2933
2934 hr_utility.trace('lv_exc_rep_name = ' || lv_exc_rep_name);
2935 hr_utility.trace('lv_orv_type = ' || lv_orv_type);
2936 hr_utility.trace('lv_orv_value = ' || lv_orv_value);
2937
2938 Exception when others then
2939 hr_utility.trace('In exception: OTHERS ');
2940 null;
2941
2942 END;
2943 /*
2944 Begin
2945 -- Exception Group Information
2946 select perg.exception_report_name ,
2947 hr_general.decode_lookup('PQP_VARIANCE_TYPES',perg.variance_type) ovt,
2948 to_char(VARIANCE_VALUE)
2949 into lv_exc_grp_name,lv_grp_orv_type,lv_grp_orv_value
2950 from pqp_exception_report_groups perg
2951 where legislation_code = 'US'
2952 and perg.EXCEPTION_GROUP_ID = p_exception_group_name;
2953
2954 hr_utility.trace('lv_gre_name = ' || lv_gre_name);
2955 Exception when others then
2956 hr_utility.trace('In exception: OTHERS ');
2957 null;
2958
2959 END;
2960 */
2961 Begin
2962 select element_name
2963 into lv_element_name
2964 from pay_element_types_f
2965 where element_type_id = p_element;
2966 Exception when others then
2967 hr_utility.trace('In exception: OTHERS ');
2968 null;
2969
2970 END;
2971
2972 Begin
2973 If p_batch_id is not null then
2974 select
2975 batch_name
2979 End If;
2976 into lv_batch_name
2977 from pay_batch_headers
2978 where batch_id = p_batch_id;
2980 hr_utility.trace('Batch Name :' ||lv_batch_name);
2981
2982 Exception when others then
2983 lv_batch_name := '';
2984 hr_utility.trace('In exception: Batch Name ');
2985 null;
2986
2987 END;
2988
2989 Begin
2990 /* currently we are only looking at the context at payroll level
2991 as we do not have any required parameter for GRE on the SRS
2992 screen, if we want to use the contact defined at GRE level then
2993 we need to modify the package and SRS defination to get the
2994 GRE_ID , and pass it in the following function.
2995 */
2996
2997 lv_contact_user_name := get_notifier( to_number(p_payroll_id),
2998 to_number(p_gre),
2999 p_date_earned);
3000
3001 Exception when others then
3002 hr_utility.trace('In exception: OTHERS at get_notifier');
3003 null;
3004
3005 END;
3006
3007 Begin
3008
3009 select orig_system,orig_system_id,name,display_name
3010 into l_orig_system,l_orig_system_id,l_role_name,l_role_display_name
3011 from wf_roles
3012 where name = lv_contact_user_name ;
3013
3014 Exception when others then
3015 hr_utility.trace('In exception: OTHERS ');
3016 null;
3017
3018 END;
3019 -- For debugging .. remove the code
3020 Begin
3021 /*
3022 select fnd_profile_server.value('PER_SECURITY_PROFILE_ID')
3023 into l_profile_per_sec_id
3024 from dual;
3025 */
3026 select to_number(fnd_profile_server.value('PER_SECURITY_PROFILE_ID'))
3027 into l_profile_per_sec_id
3028 from dual;
3029 select fnd_profile_server.value('PER_BUSINESS_GROUP_ID')
3030 into l_profile_per_bg
3031 from dual;
3032 /*
3033 select fnd_profile_server.value('SECURITY_GROUP_ID')
3034 into l_profile_sec_grp_id
3035 from dual;
3036 */
3037
3038 /* Bug 9211154 - There is no profile 'SECURITY_GROUP_ID' and hence below query will always return NULL'
3039 select to_number(fnd_profile_server.value('SECURITY_GROUP_ID'))
3040 into l_profile_sec_grp_id
3041 from dual;
3042 */
3043 --Bug 9211154 Setting the value as fnd security group id
3044 l_profile_sec_grp_id := l_sec_grp_id;
3045
3046 select fnd_profile_server.value('USERNAME')
3047 into l_profile_user_name
3048 from dual;
3049
3050 hr_utility.trace('jj payuswfpkg l_profile_per_sec_id = '||l_profile_per_sec_id);
3051 hr_utility.trace('jj payuswfpkg l_profile_per_bg = '||l_profile_per_bg);
3052 hr_utility.trace('jj payuswfpkg l_profile_sec_grp_id = '||l_profile_sec_grp_id);
3053 hr_utility.trace('jj payuswfpkg l_profile_user_namep = '||l_profile_user_name);
3054
3055 Exception when others then
3056 hr_utility.trace('In exception: OTHERS at profile');
3057 null;
3058
3059 END;
3060
3061 --'JJ CA HRMS Manager';
3062 hr_utility.trace('orig ststem : '|| l_orig_system);
3063 hr_utility.trace('orig ststem : '|| l_orig_system_id);
3064 hr_utility.trace('l_role_name = ' || l_role_name);
3065
3066 wf_engine.SetItemOwner ( itemtype => ItemType,
3067 itemkey => ItemKey,
3068 owner => ProcessOwner);
3069
3070 --
3071 /* Set Require Values for the Variables
3072 */
3073
3074 -- BEE Transfer
3075 If p_batch_id is not null then
3076 lv_is_batch := 'Y';
3077 lv_prc_list := lv_prc_list||lv_conc_prog_name(1)||wf_core.newline;
3078 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(1)||'</p>';
3079 else
3080 lv_is_batch := 'N';
3081 end if;
3082
3083 -- Retro Pay By Element and Retro Notification Report
3084
3085 /* If ((p_retro_assignment_set_name is not null) and
3086 (p_event_group is not null )) then
3087 lv_is_retro_ntfy := 'Y';
3088 lv_prc_list := lv_prc_list||lv_conc_prog_name(2)||wf_core.newline;
3089 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(2)||'</p>';
3090 else
3091 lv_is_retro_ntfy := 'N';
3092 end if;*/
3093 -- Retro Notification Report (enhanced) PDF
3094 -- bug 12704242
3095 if (p_retro_template_name is not null) then
3096 lv_is_retro_ntfy := 'Y';
3097 lv_prc_list := lv_prc_list||lv_conc_prog_name(2)||wf_core.newline;
3098 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(2)||'</p>';
3099 else
3100 lv_is_retro_ntfy := 'N';
3101 end if;
3102
3103 -- Payroll Exception Report
3104
3105 If ((p_exception_report_name is not null) OR
3106 (p_exception_group_name is not null)) then
3107 lv_is_payroll_exception_report := 'Y';
3108 lv_prc_list := lv_prc_list||lv_conc_prog_name(1)||wf_core.newline;
3109 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(1)||'</p>';
3110 else
3111 lv_is_payroll_exception_report := 'N';
3112 end if;
3113
3114 -- NACHA
3115
3116 If p_nacha_payment_method is not null then
3117 lv_is_nacha := 'Y';
3118 lv_prc_list := lv_prc_list||lv_conc_prog_name(1)||wf_core.newline;
3119 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(1)||'</p>';
3120 else
3121 lv_is_nacha := 'N';
3122 end if;
3123
3124 -- Check
3125 If (p_check_writer_payment_method is not null ) and
3126 (p_start_check_number is not null ) then
3127 lv_is_check := 'Y';
3128 lv_prc_list := lv_prc_list||lv_conc_prog_name(1)||wf_core.newline;
3129 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(1)||'</p>';
3130 else
3131 lv_is_check := 'N';
3132 end if;
3133
3134 -- Third Party Check
3135
3136 If p_payment_method_3rd_party is not null then
3137 lv_is_third_party_check := 'Y';
3138 lv_prc_list := lv_prc_list||lv_conc_prog_name(1)||wf_core.newline;
3139 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(1)||'</p>';
3140 else
3141 lv_is_third_party_check := 'N';
3142 end if;
3143
3144 -- Deposit Advice
3145
3146 If p_da_report_category is not null then
3147 lv_is_deposit_advice := 'Y';
3148 lv_prc_list := lv_prc_list||lv_conc_prog_name(1)||wf_core.newline;
3149 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(1)||'</p>';
3150 else
3151 lv_is_deposit_advice := 'N';
3152 end if;
3153
3154 -- Element Register Report
3155 If (p_selection_criterion is not null ) and
3156 ((p_ele_reg_element_set is not null) or
3157 (p_element_classification is not null) or
3158 (p_element is not null))
3159 then
3160 lv_is_ele_reg := 'Y';
3161 lv_prc_list := lv_prc_list||lv_conc_prog_name(16)||wf_core.newline;
3162 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(16)||'</p>';
3163 else
3164 lv_is_ele_reg := 'N';
3165 end if;
3166
3167 -- Text
3168 for k in 4..19 loop
3169 lv_prc_list := lv_prc_list||lv_conc_prog_name(k);
3170 end loop;
3171
3172 -- HTML
3173 for l in 4..19 loop
3174 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(l)||'</p>';
3175 end loop;
3176
3177 -- Costing Summary and Detail Report
3178 if ((p_consolidation_set_id is not null)) then
3179 lv_is_costing_detail := 'Y';
3180 lv_prc_list := lv_prc_list||lv_conc_prog_name(21)||wf_core.newline;
3181 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(21)||'</p>';
3182
3183 if (p_cost_summary_file_out is not null) then
3184 lv_is_costing_summary := 'Y';
3185 lv_prc_list := lv_prc_list||lv_conc_prog_name(20)||wf_core.newline;
3186 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(20)||'</p>';
3187 else
3188 lv_is_costing_summary := 'N';
3189 end if;
3190 else
3191 lv_is_costing_summary := 'N';
3192 lv_is_costing_detail := 'N';
3193 end if;
3194 l_message_subject1 := 'List of Processes and Reports which will be submitted.';
3195 if lv_isResponseRequired = 'Y' then
3196 l_message_text1 := '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||lv_prc_list;
3197
3198 l_message_html_text1 := 'Review the List of Processes and Reports which will be submitted based on the input parameters, please click on the '||''''||'Continue'||''''||' to start processing.';
3199 else
3200 l_message_text1 := 'Review the List of Processes and Reports which will be submitted based on the input parameters.'||wf_core.newline||lv_prc_list;
3201
3202 l_message_html_text1 := 'Review the List of Processes and Reports which will be submitted based on the input parameters.';
3203 end if;
3204
3205 /*
3206 l_message_html_text1 := '<p> 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.'||'</p>'||'<p>'||lv_prc_list_html||'</p>';
3207
3208 */
3209
3210 /*
3211 l_message_html_text1 := '<p> Review the List of Processes and Reports which will be submitted based on the input parameters, please click on the '||''''||'Continue'||''''||' to start processing.'||'</p>';
3212
3213
3214 l_message_html_text1 := 'Review the List of Processes and Reports which will be submitted based on the input parameters, please click on the '||''''||'Continue'||''''||' to start processing.';
3215
3216 */
3217 /*
3218 if ((lv_is_batch = 'Y') and (lv_is_retro_ntfy = 'Y'))then
3219 l_message_html_text2 := 'WF_NOTIFICATION(ATTRS,MSG_ATTR19,MSG_ATTR20,MSG_ATTR21,MSG_ATTR1,MSG_ATTR2,MSG_ATTR3,MSG_ATTR4,MSG_ATTR5,'
3220 ||'MSG_ATTR6,MSG_ATTR7,MSG_ATTR8,MSG_ATTR9,MSG_ATTR10,MSG_ATTR11,MSG_ATTR12,MSG_ATTR13,MSG_ATTR14,'
3221 ||'MSG_ATTR15,MSG_ATTR16,MSG_ATTR17,MSG_ATTR18)';
3222 elsif ((lv_is_batch = 'Y') and (lv_is_retro_ntfy = 'N' or lv_is_retro_ntfy is null))then
3223 l_message_html_text2 := 'WF_NOTIFICATION(ATTRS,MSG_ATTR19,MSG_ATTR1,MSG_ATTR2,MSG_ATTR3,MSG_ATTR4,MSG_ATTR5,MSG_ATTR6,MSG_ATTR7,'
3224 ||'MSG_ATTR8,MSG_ATTR9,MSG_ATTR10,MSG_ATTR11,MSG_ATTR12,MSG_ATTR13,MSG_ATTR14,'
3225 ||'MSG_ATTR15,MSG_ATTR16,MSG_ATTR17,MSG_ATTR18)';
3226 elsif ((lv_is_batch = 'N' or lv_is_batch is null) and (lv_is_retro_ntfy = 'Y'))then
3227 l_message_html_text2 := 'WF_NOTIFICATION(ATTRS,MSG_ATTR20,MSG_ATTR21,MSG_ATTR1,MSG_ATTR2,MSG_ATTR3,MSG_ATTR4,MSG_ATTR5,MSG_ATTR6,MSG_ATTR7,MSG_ATTR8,'
3228 ||'MSG_ATTR9,MSG_ATTR10,MSG_ATTR11,MSG_ATTR12,MSG_ATTR13,MSG_ATTR14,MSG_ATTR15,'
3229 ||'MSG_ATTR16,MSG_ATTR17,MSG_ATTR18)';
3230 else
3231 l_message_html_text2 := 'WF_NOTIFICATION(ATTRS,MSG_ATTR1,MSG_ATTR2,MSG_ATTR3,MSG_ATTR4,MSG_ATTR5,MSG_ATTR6,MSG_ATTR7,'
3232 ||'MSG_ATTR8,MSG_ATTR9,MSG_ATTR10,MSG_ATTR11,MSG_ATTR12,MSG_ATTR13,MSG_ATTR14,'
3233 ||'MSG_ATTR15,MSG_ATTR16,MSG_ATTR17,MSG_ATTR18)';
3234 end if;
3235 */
3236
3237 /*
3238 -- Testing with multiple functions in the same text of the body for the notification
3239
3240 if (lv_is_batch = 'Y') then
3241 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR19)';
3242 elsif (lv_is_retro_ntfy = 'Y') then
3243 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR20,MSG_ATTR21)';
3244 end if;
3245 -- For Other Processes Dependent on Payroll Run.
3246
3247 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR1,MSG_ATTR2,MSG_ATTR3,MSG_ATTR4,MSG_ATTR5)';
3248
3249 if (lv_is_payroll_exception_report = 'Y') then
3250 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR6)';
3251 end if;
3252
3253 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR7)';
3254
3255 if (lv_is_nacha = 'Y') then
3256 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR8)';
3257 end if;
3258
3259 -- External Process Archive
3260 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR9)';
3261
3262 if (lv_is_check = 'Y') then
3263 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR10)';
3264 elsif (lv_is_third_party_check = 'Y') then
3265 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR11)';
3266 end if;
3267
3268 -- Deposit Advice
3269
3270 if (lv_is_deposit_advice = 'Y') then
3271 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR12)';
3272 end if;
3273
3274 -- Register Reports and Costing Process and Reports.
3275
3276 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR13,MSG_ATTR14,MSG_ATTR15,MSG_ATTR16,MSG_ATTR17,MSG_ATTR18)';
3277
3278 */
3279 --jjj
3280 -- Testing with single functions and all message attributes in the same text of the body for the notification , setting vlalues of the message attribute by setting the values of the item attributes
3281 n := 1;
3282 if (lv_is_batch = 'Y') then
3283 lv_cur_process(n) := lv_conc_prog_name(1);
3284 n := n+1;
3285 end if;
3286
3287 if (lv_is_retro_ntfy = 'Y') then
3288 lv_cur_process(n) := lv_conc_prog_name(2);
3289 n := n+1;
3290 lv_cur_process(n) := lv_conc_prog_name(3);
3291 n := n+1;
3292 end if;
3293 -- For Other Processes Dependent on Payroll Run.
3294
3295 lv_cur_process(n) := lv_conc_prog_name(4);
3296 n := n+1;
3297 lv_cur_process(n) := lv_conc_prog_name(5);
3298 n := n+1;
3299 lv_cur_process(n) := lv_conc_prog_name(6);
3300 n := n+1;
3301 lv_cur_process(n) := lv_conc_prog_name(7);
3302 n := n+1;
3303
3304 if (lv_is_payroll_exception_report = 'Y') then
3305 lv_cur_process(n) := lv_conc_prog_name(8);
3306 n := n+1;
3307 l_pay_excep_rpt_message_text1 := 'Payroll Exception Report(Request Id: &REQ_ID_REP4) :'||'
3308 Start Date: &START_DATE
3309 End Date: &END_DATE
3310 Payroll Name: &PAYROLL
3311 Consolidation Set: &CONSOLIDATION_SET
3312 Exception Group Name: &GROUP_NAME
3313 Exception Report Name: &REPORT_NAME
3314 Override Variance Type: &OVER_VAR_TYPE
3315 Override Variance Value: &OVER_VAR_VALUE ' ;
3316 l_pay_excep_rpt_message_html1 := 'Payroll Exception Report (Request Id: &REQ_ID_REP4) : WF_NOTIFICATION(ATTRS,START_DATE,PAYROLL,CONSOLIDATION_SET,GROUP_NAME,REPORT_NAME,OVER_VAR_TYPE,OVER_VAR_VALUE) ';
3317
3318 else
3319 l_pay_excep_rpt_message_text1 := null;
3320 l_pay_excep_rpt_message_html1 := null;
3321
3322 end if;
3323
3324 --Federal and State Tax Remittance Report
3325
3326 lv_cur_process(n) := lv_conc_prog_name(9);
3327 n := n+1;
3328
3329 --Pre Payments
3330
3331 lv_cur_process(n) := lv_conc_prog_name(10);
3332 n := n+1;
3333
3334 if (lv_is_nacha = 'Y') then
3335 lv_cur_process(n) := lv_conc_prog_name(11);
3336 n := n+1;
3337 end if;
3338
3339 -- External Process Archive
3340 lv_cur_process(n) := lv_conc_prog_name(12);
3341 n := n+1;
3342
3343 if (lv_is_check = 'Y') then
3344 lv_cur_process(n) := lv_conc_prog_name(13);
3345 n := n+1;
3346 end if;
3347
3348 if (lv_is_third_party_check = 'Y') then
3349 lv_cur_process(n) := lv_conc_prog_name(14);
3350 n := n+1;
3351 end if;
3352
3353 -- Deposit Advice
3354
3355 if (lv_is_deposit_advice = 'Y') then
3356 lv_cur_process(n) := lv_conc_prog_name(15);
3357 n := n+1;
3358 end if;
3359
3360 -- Register Reports and Costing Process and Reports.
3361
3362 if (lv_is_ele_reg = 'Y') then
3363 lv_cur_process(n) := lv_conc_prog_name(16);
3364 n := n+1;
3365 l_ele_reg_rpt_message_text1 := 'Element Register(Request ID: &REQ_ID_REP6) ';
3366 l_ele_reg_rpt_message_html1 := 'Element Register Report (Request Id: &REQ_ID_REP6) ';
3367
3368 else
3369 l_ele_reg_rpt_message_text1 := null;
3370 l_ele_reg_rpt_message_html1 := null;
3371
3372 end if;
3373
3374 -- Payment Register
3375
3376 lv_cur_process(n) := lv_conc_prog_name(17);
3377 n := n+1;
3378
3379 -- 3rd Payment Register
3380
3381 lv_cur_process(n) := lv_conc_prog_name(18);
3382 n := n+1;
3383
3384 -- Costing Process
3385
3386 lv_cur_process(n) := lv_conc_prog_name(19);
3387 n := n+1;
3388
3389 -- Costing Summary and Detail Reports
3390
3391 lv_cur_process(n) := lv_conc_prog_name(20);
3392 n := n+1;
3393 lv_cur_process(n) := lv_conc_prog_name(21);
3394 n := n+1;
3395
3396
3397
3398 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR1,MSG_ATTR2,MSG_ATTR3,MSG_ATTR4,MSG_ATTR5)';
3399 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR6,MSG_ATTR7,MSG_ATTR8,MSG_ATTR9,MSG_ATTR10)';
3400 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR11,MSG_ATTR12,MSG_ATTR13,MSG_ATTR14,MSG_ATTR15)';
3401 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR16,MSG_ATTR17,MSG_ATTR18,MSG_ATTR19,MSG_ATTR20,MSG_ATTR21)';
3402
3403 if n < 22 then
3404 for p in n..21 loop
3405 lv_cur_process(p) := null;
3406 end loop;
3407 end if;
3408
3409 hr_utility.trace('p_payroll_id = ' || p_payroll_id);
3410 hr_utility.trace('p_consolidation_set_id = ' || p_consolidation_set_id);
3411 hr_utility.trace('p_date_earned = ' || p_date_earned);
3412 hr_utility.trace('p_date_paid = ' || p_date_paid);
3413 hr_utility.trace('p_payroll_assignment_set_name = ' || p_payroll_assignment_set_name);
3414 hr_utility.trace('p_payroll_run_type = ' || p_payroll_run_type);
3415 hr_utility.trace('ln_nacha_payment_type_id = ' || ln_nacha_payment_type_id);
3416 hr_utility.trace('check_style = ' || p_check_style);
3417 begin
3418
3419 i := 0;
3420
3421 varname(i) := 'CUR_ITEM_TYPE';
3422 varval(i) := ItemType;
3423 i := i+1;
3424 varname(i) := 'CURR_ITEM_KEY';
3425 varval(i) := ItemKey;
3426 i := i+1;
3427 varname(i) := 'IS_BATCH';
3428 varval(i) := lv_is_batch;
3429 i := i+1;
3430 varname(i) := 'IS_RETRO';
3431 varval(i) := lv_is_retro_ntfy;
3432 i := i+1;
3433 varname(i) := 'PAYROLL_NAME';
3434 varval(i) := lv_payroll_name;
3435 i := i+1;
3436 varname(i) := 'PAYROLL_ID';
3437 varval(i) := p_payroll_id;
3438 i := i+1;
3439 varname(i) := 'CONSOLIDATION_SET_NAME';
3440 varval(i) := lv_consolidation_set_name;
3441 i := i+1;
3442 varname(i) := 'CONSOLIDATION_SET_ID';
3443 varval(i) := p_consolidation_set_id;
3444 i := i+1;
3445 varname(i) := 'DATE_EARNED';
3446 varval(i) := p_date_earned;
3447 i := i+1;
3448 varname(i) := 'DATE_PAID';
3449 varval(i) := p_date_paid;
3450 i := i+1;
3451 varname(i) := 'EVENT_GROUP';
3452 varval(i) := p_event_group;
3453 i := i+1;
3454 varname(i) := 'RETRO_ASSIGNMENT_SET_NAME';
3455 varval(i) := p_retro_assignment_set_name;
3456 i := i+1;
3457 varname(i) := 'RETRO_ASSIGNMENT_SET_NAME_DUMY';
3458 varval(i) := p_retro_assignment_set_dummy;
3459 i := i+1;
3460 --bug 12704242
3461 varname(i) := 'RETRO_REPORT_DATE';
3462 varval(i) := 'REPORT_DATE='||to_char(sysdate,'DD/MM/YYYYHH24:MI:SS');
3463 i := i+1;
3464 varname(i) := 'RETRO_TEMPLATE_NAME';
3465 varval(i) := p_retro_template_name;
3466 i := i+1;
3467 varname(i) := 'RETRO_GENERATE_REPORT';
3468 varval(i) := p_retro_generate_report;
3469 i := i+1;
3470 varname(i) := 'RETRO_DEFINITION_ID';
3471 varval(i) := lv_retropay_definition;
3472 i := i+1;
3473 varname(i) := 'RETRO_ENTRY_CREATION_DATE';
3474 varval(i) := p_retropay_entry_creation_date;
3475 i := i+1;
3476 varname(i) := 'RETRO_ASSIGNMENT_SET_ID';
3477 varval(i) := '';
3478 i := i+1;
3479 -- end bug 12704242
3480 varname(i) := 'RETRO_ELEMENT_SET';
3481 varval(i) := p_retro_element_set;
3482 i := i+1;
3483 varname(i) := 'RETRO_START_DATE';
3484 varval(i) := '';--p_retro_start_date;
3485 i := i+1;
3486 varname(i) := 'EFFECTIVE_DATE';
3487 varval(i) := p_effective_date;
3488 --varval(i) := p_date_earned;
3489 --varval(i) := p_date_paid;
3490 i := i+1;
3491 varname(i) := 'PAYROLL_ELEMENT_SET_NAME';
3492 varval(i) := p_payroll_element_set_name;
3493 i := i+1;
3494 varname(i) := 'PAYROLL_ASSIGNMENT_SET_NAME';
3495 varval(i) := p_payroll_assignment_set_name;
3496 i := i+1;
3497 varname(i) := 'PAYROLL_RUN_TYPE';
3498 varval(i) := p_payroll_run_type;
3499 i := i+1;
3500 varname(i) := 'GOVERNMENT_REPORTING_ENTITY';
3501 varval(i) := p_gre;
3502 i := i+1;
3503 varname(i) := 'GRE_NAME';
3504 varval(i) := lv_gre_name;
3505 i := i+1;
3506 varname(i) := 'ORGANIZATION';
3507 varval(i) := p_organization;
3508 i := i+1;
3509 varname(i) := 'LOCATION';
3510 varval(i) := p_location;
3511 i := i+1;
3512 varname(i) := 'IS_PAYROLL_EXCEPTION_REPORT';
3513 varval(i) := lv_is_payroll_exception_report;
3514 i := i+1;
3515 varname(i) := 'SELECT_REPORT_OR_GROUP';
3516 varval(i) := p_select_report_or_group;
3517 i := i+1;
3518 varname(i) := 'EXCEPTION_GROUP_NAME';
3519 varval(i) := p_exception_group_name;
3520 i := i+1;
3521 varname(i) := 'EXCPTN_RPT_GRP_NAME';
3522 varval(i) := lv_exc_grp_name;
3523 i := i+1;
3524 varname(i) := 'EXCPTN_RPT_NAME';
3525 varval(i) := lv_exc_rep_name;
3526 i := i+1;
3527 varname(i) := 'EXCEPTION_GROUP_NAME_DMY';
3528 varval(i) := p_exception_group_name_dummy;
3529 i := i+1;
3530 varname(i) := 'EXCEPTION_REPORT_NAME';
3531 varval(i) := p_exception_report_name;
3532 i := i+1;
3533 varname(i) := 'EXCEPTION_REPORT_NAME_DMY';
3534 varval(i) := p_exception_report_name_dummy;
3535 i := i+1;
3536 varname(i) := 'OVER_RIDE_VARIANCE_TYPE';
3537 varval(i) := p_over_ride_variance_type;
3538 i := i+1;
3539 varname(i) := 'ORV_TYPE';
3540 varval(i) := lv_orv_type;
3541 i := i+1;
3542 varname(i) := 'OVER_RIDE_VARIANCE_TYPE_DMY';
3543 varval(i) := p_over_ride_varianc_type_dumy;
3544 i := i+1;
3545 varname(i) := 'OVER_RIDE_VARIANCE_VALUE';
3546 varval(i) := p_over_ride_variance_value;
3547 i := i+1;
3548 varname(i) := 'ORV_VALUE';
3549 varval(i) := lv_orv_value;
3550 i := i+1;
3551 varname(i) := 'OVER_RIDE_VARIANCE_VALUE_DMY';
3552 varval(i) := p_over_ride_varinc_value_dumy;
3553 i := i+1;
3554 varname(i) := 'TRANSFER_DATE';
3555 -- varval(i) := 'TRANSFER_DATE='||p_effective_date;
3556 --varval(i) := 'TRANSFER_DATE='||p_date_earned;
3557 varval(i) := 'TRANSFER_DATE='||p_date_paid;
3558 i := i+1;
3559 varname(i) := 'PPA_FINDER_PQP';
3560 varval(i) := p_ppa_finder_pqp;
3561 i := i+1;
3562 varname(i) := 'TRANSFER_PPA_FINDER_PQP';
3563 varval(i) := 'TRANSFER_PPA_FINDER='||p_ppa_finder_pqp;
3564 i := i+1;
3565 --following code added for bug 12594530 p_action_parameter_group, --bug 12548555
3566 varname(i) := 'PAY_ACTION_PARAMETER_GROUP_ID';
3567 varval(i) := p_action_parameter_group;
3568 i := i+1; --ended bug 12594530
3569 varname(i) := 'PREPAY_PAYMENT_METHOD_OVERRIDE';
3570 varval(i) := p_payment_method_override;
3571 i := i+1;
3572 varname(i) := 'IS_NACHA';
3573 varval(i) := lv_is_nacha;
3574 i := i+1;
3575 varname(i) := 'NACHA_PAYMENT_METHOD';
3576 varval(i) := p_nacha_payment_method;
3577 i := i+1;
3578 varname(i) := 'NACHA_PYMT_NAME';
3579 varval(i) := lv_nacha_pymt_name;
3580 i := i+1;
3581 varname(i) := 'DEPOSIT_DATE_OVERRIDE';
3582 varval(i) := p_deposit_date_override;
3583 i := i+1;
3584 varname(i) := 'FILE_ID_MODIFIER';
3585 varval(i) := p_file_id_modifier;
3586 i := i+1;
3587 varname(i) := 'FILE_ID_MODIFIER_CHECK';
3588 varval(i) := p_file_id_modifier_check;
3589 i := i+1;
3590 varname(i) := 'THRID_PARTY_CHECK';
3591 varval(i) := p_thrid_party_check;
3592 i := i+1;
3593 varname(i) := 'IS_CHECK';
3594 varval(i) := lv_is_check;
3595 i := i+1;
3596 varname(i) := 'CHECK_WRITER_PAYMENT_METHOD';
3597 varval(i) := p_check_writer_payment_method;
3598 i := i+1;
3599 varname(i) := 'CHECK_PYMT_NAME';
3600 varval(i) := lv_check_pymt_name;
3601 i := i+1;
3602 varname(i) := 'CHECK_WRITER_SORT_SEQUENCE';
3603 varval(i) := p_check_writer_sort_sequence;
3604 i := i+1;
3605 varname(i) := 'CHECK_SORT_SEQ_MEANING';
3606 varval(i) := lv_check_sort_seq_meaning;
3607 i := i+1;
3608 varname(i) := 'CHECK_STYLE';
3609 varval(i) := p_check_style;
3610 i := i+1;
3611 varname(i) := 'IS_THIRD_PARTY_CHECK';
3612 varval(i) := lv_is_third_party_check;
3613 i := i+1;
3614 varname(i) := 'PAYMENT_METHOD_3RD_PARTY';
3615 varval(i) := p_payment_method_3rd_party;
3616 i := i+1;
3617 varname(i) := 'SORT_SEQUENCE_3RD_PARTY';
3618 varval(i) := p_sort_sequence_3rd_party;
3619 i := i+1;
3620 varname(i) := 'IS_DEPOSIT_ADVICE';
3621 varval(i) := lv_is_deposit_advice;
3622 i := i+1;
3623 varname(i) := 'DEPOSIT_ADVICE_REPORT_CATEGORY';
3624 varval(i) := p_da_report_category;
3625 i := i+1;
3626 varname(i) := 'DEPOSIT_ADVICE_START_DATE_DMY';
3627 varval(i) := 'START_DATE='||p_date_paid;
3628 i := i+1;
3629 varname(i) := 'DEPOSIT_ADVICE_END_DATE_DMY';
3630 varval(i) := 'END_DATE='||p_date_paid;
3631 i := i+1;
3632 varname(i) := 'DEPOSIT_ADVICE_PAYROLL';
3633 varval(i) := 'PAYROLL_ID='||p_payroll_id;
3634 i := i+1;
3635 varname(i) := 'DEPOSIT_ADVICE_CONC_SET';
3636 varval(i) := 'CONSOLIDATION_SET_ID='||p_consolidation_set_id;
3637 i := i+1;
3638 varname(i) := 'DEPOSIT_ADVICE_SORT_SEQUENCE';
3639 varval(i) := p_da_sort_sequence;
3640 i := i+1;
3641 varname(i) := 'DEPOSIT_ADVICE_ASSIGNMENT_SET';
3642 varval(i) := p_da_assignment_set;
3643 i := i+1;
3644 varname(i) := 'DEPOSIT_ADVICE_ASGSET_DMY';
3645 varval(i) := p_assignment_set_dummy;
3646 i := i+1;
3647 varname(i) := 'DEPOSIT_ADVICE_REPORT_NAME';
3648 varval(i) := lv_da_report;
3649 i := i+1;
3650 varname(i) := 'SELECTION_CRITERION';
3651 varval(i) := p_selection_criterion;
3652 i := i+1;
3653 varname(i) := 'ELE_REG_ELEMENT_SET';
3654 varval(i) := p_ele_reg_element_set;
3655 i := i+1;
3656 varname(i) := 'ELEMENT_CLASSIFICATION';
3657 varval(i) := p_element_classification;
3658 i := i+1;
3659 varname(i) := 'ELEMENT';
3660 varval(i) := p_element;
3661 i := i+1;
3662 varname(i) := 'ELEMENT_NAME';
3663 varval(i) := lv_element_name;
3664 --- i := i+1;
3665 --- varname(i) := 'ELEMENT_REGISTER_EMPLOYEE';
3666 --- varval(i) := p_ele_reg_employee;
3667 i := i+1;
3668 varname(i) := 'EMP_ID';
3669 varval(i) := p_ele_reg_employee;
3670 i := i+1;
3671 varname(i) := 'COSTING_PROCESS';
3672 varval(i) := p_costing_process;
3673 i := i+1;
3674 varname(i) := 'START_DATE';
3675 varval(i) := p_date_earned;
3676 i := i+1;
3677 varname(i) := 'END_DATE';
3678 varval(i) := p_date_paid;
3679 i := i+1;
3680 varname(i) := 'PPA_FINDER';
3681 varval(i) := p_ppa_finder;
3682 i := i+1;
3683 varname(i) := 'USER';
3684 varval(i) := lv_contact_user_name;
3685 --- varval(i) := RequestorUsername;
3686 i := i+1;
3687 varname(i) := 'CONTACT_USERNAME';
3688 varval(i) := lv_contact_user_name;
3689 i := i+1;
3690 varname(i) := 'MESSAGE_SUBJECT1';
3691 varval(i) := l_message_subject1;
3692 i := i+1;
3693 varname(i) := 'MESSAGE_TEXT1';
3694 varval(i) := l_message_text1;
3695 i := i+1;
3696 varname(i) := 'MESSAGE_HTML_TEXT1';
3697 varval(i) := l_message_html_text1;
3698 i := i+1;
3699 varname(i) := 'MESSAGE_HTML_TEXT2';
3700 varval(i) := l_message_html_text2;
3701 i := i+1;
3702 varname(i) := 'ROLE_NAME';
3703 varval(i) := l_role_name;
3704 i := i+1;
3705 varname(i) := 'REPORTING_CURRENCY_CODE';
3706 varval(i) := lv_currency_code;
3707 i := i+1;
3708 varname(i) := 'PPA_FINDER';
3709 varval(i) := p_ppa_finder;
3710 i := i+1;
3711 varname(i) := 'TRANSFER_PPA_FINDER';
3712 varval(i) := 'TRANSFER_PPA_FINDER='||p_ppa_finder;
3713 i := i+1;
3714 varname(i) := 'TRANSFER_GRE';
3715 varval(i) := 'TRANSFER_GRE='||p_gre;
3716 i := i+1;
3717 varname(i) := 'TRANSFER_PAYROLL';
3718 varval(i) := 'TRANSFER_PAYROLL='||p_payroll_id;
3719 i := i+1;
3720 varname(i) := 'TRANSFER_CONC_SET';
3721 varval(i) := 'TRANSFER_CONC_SET='||p_consolidation_set_id;
3722 i := i+1;
3723 varname(i) := 'XFR_TRANSFER_PAYROLL_ID';
3724 varval(i) := 'TRANSFER_PAYROLL_ID='||p_payroll_id;
3725 i := i+1;
3726 varname(i) := 'XFR_TRANS_CONS_SET';
3727 varval(i) := 'TRANSFER_CONSOLIDATION_SET_ID='||p_consolidation_set_id;
3728 i := i+1;
3729 varname(i) := 'FILE_ID_MODIFIER_HIDDEN';
3730 varval(i) := 'FILE_ID_MODIFIER='||p_file_id_modifier;
3731 i := i+1;
3732 varname(i) := 'PERIOD';
3733 varval(i) := p_period;
3734 i := i+1;
3735 varname(i) := 'PERIOD_NAME';
3736 varval(i) := lv_period_name;
3737 i := i+1;
3738 varname(i) := 'P_PAYROLL_ACTION_ID';
3739 varval(i) := p_p_payroll_action_id;
3740 i := i+1;
3741 varname(i) := 'NACHA_PAYMENT_TYPE_ID';
3742 varval(i) := ln_nacha_payment_type_id;
3743 i := i+1;
3744 varname(i) := 'CHECK_PAYMENT_TYPE_ID';
3745 varval(i) := ln_check_payment_type_id;
3746 i := i+1;
3747 varname(i) := 'DATE_TIME';
3748 varval(i) := lv_date_time;
3749 i := i+1;
3750 varname(i) := 'END_DATE_LEG_PARAM';
3751 varval(i) := 'END_DATE='||p_date_earned;
3752 i := i+1;
3753 varname(i) := 'PAYROLL_LEG_PARAM';
3754 varval(i) := 'PAYROLL_ID='||p_payroll_id;
3755 i := i+1;
3756 varname(i) := 'CONC_SET_LEG_PARAM';
3757 varval(i) := 'CONSOLIDATION_SET_ID='||p_consolidation_set_id;
3758 i := i+1;
3759 varname(i) := 'PROCESS_01';
3760 varval(i) := lv_cur_process(1);
3761 i := i+1;
3762 varname(i) := 'PROCESS_02';
3763 varval(i) := lv_cur_process(2);
3764 i := i+1;
3765 varname(i) := 'PROCESS_03';
3766 varval(i) := lv_cur_process(3);
3767 i := i+1;
3768 varname(i) := 'PROCESS_04';
3769 varval(i) := lv_cur_process(4);
3770 i := i+1;
3771 varname(i) := 'PROCESS_05';
3772 varval(i) := lv_cur_process(5);
3773 i := i+1;
3774 varname(i) := 'PROCESS_06';
3775 varval(i) := lv_cur_process(6);
3776 i := i+1;
3777 varname(i) := 'PROCESS_07';
3778 varval(i) := lv_cur_process(7);
3779 i := i+1;
3780 varname(i) := 'PROCESS_08';
3781 varval(i) := lv_cur_process(8);
3782 i := i+1;
3783 varname(i) := 'PROCESS_09';
3784 varval(i) := lv_cur_process(9);
3785 i := i+1;
3786 varname(i) := 'PROCESS_10';
3787 varval(i) := lv_cur_process(10);
3788 i := i+1;
3789 varname(i) := 'PROCESS_11';
3790 varval(i) := lv_cur_process(11);
3791 i := i+1;
3792 varname(i) := 'PROCESS_12';
3793 varval(i) := lv_cur_process(12);
3794 i := i+1;
3795 varname(i) := 'PROCESS_13';
3796 varval(i) := lv_cur_process(13);
3797 i := i+1;
3798 varname(i) := 'PROCESS_14';
3799 varval(i) := lv_cur_process(14);
3800 i := i+1;
3801 varname(i) := 'PROCESS_15';
3802 varval(i) := lv_cur_process(15);
3803 i := i+1;
3804 varname(i) := 'PROCESS_16';
3805 varval(i) := lv_cur_process(16);
3806 i := i+1;
3807 varname(i) := 'PROCESS_17';
3808 varval(i) := lv_cur_process(17);
3809 i := i+1;
3810 varname(i) := 'PROCESS_18';
3811 varval(i) := lv_cur_process(18);
3812 i := i+1;
3813 varname(i) := 'PROCESS_19';
3814 varval(i) := lv_cur_process(19);
3815 i := i+1;
3816 varname(i) := 'PROCESS_20';
3817 varval(i) := lv_cur_process(20);
3818 i := i+1;
3819 varname(i) := 'PROCESS_21';
3820 varval(i) := lv_cur_process(21);
3821 i := i+1;
3822 varname(i) := 'BEE_BATCH_NAME';
3823 varval(i) := lv_batch_name;
3824 i := i+1;
3825 varname(i) := 'PAY_EXCEPTION_RPT_MSG_TEXT1';
3826 varval(i) := l_pay_excep_rpt_message_text1;
3827 i := i+1;
3828 varname(i) := 'PAY_EXCEPTION_RPT_MSG_HTML1';
3829 varval(i) := l_pay_excep_rpt_message_html1;
3830 i := i+1;
3831 varname(i) := 'RETRO_START_DATE_DUMMY';
3832 varval(i) := 'START_DATE=1900/01/01 00:00:00';
3833 i := i+1;
3834 varname(i) := 'ELEMENT_REGISTER_RPT_MSG_TEXT1';
3835 varval(i) := l_ele_reg_rpt_message_text1;
3836 i := i+1;
3837 varname(i) := 'ELEMENT_REGISTER_RPT_MSG_HTML1';
3838 varval(i) := l_ele_reg_rpt_message_html1;
3839 i := i+1;
3840 varname(i) := 'IS_ELEMENT_REGISTER_REPORT';
3841 varval(i) := lv_is_ele_reg;
3842
3843 i := i+1;
3844 varname(i) := 'IS_RESPONSE_REQUIRED';
3845 varval(i) := lv_isResponseRequired;
3846
3847 i := i+1;
3848 varname(i) := 'PAYROLL_WF_NOTIFY_ACTION';
3849 varval(i) := lv_Payroll_WF_Notify_Action;
3850
3851 i := i+1;
3852 varname(i) := 'IS_PAYMENT_WAIT';
3853 varval(i) := lv_isPaymentWait;
3854
3855 i := i+1;
3856 varname(i) := 'LEGISLATION_CODE';
3857 varval(i) := p_legislation_code;
3858
3859 i := i+1;
3860 varname(i) := 'IS_EXCEPTION_GROUP';
3861 varval(i) := p_is_exception_group;
3862
3863 i := i+1;
3864 varname(i) := 'IS_EXCEPTION_REPORT';
3865 varval(i) := p_is_exception_report;
3866
3867 i := i+1;
3868 varname(i) := 'PREPAY_PYMENT_MTHD_OVRIDE_NAME';
3869 varval(i) := lv_pre_ovr_pymt_name;
3870 i := i+1;
3871 varname(i) := 'RETRO_EVENT_GROUP_NAME';
3872 varval(i) := lv_event_group_name;
3873 i := i+1;
3874 varname(i) := 'OVERRIDING_CHEQUE_DATE';
3875 varval(i) := p_overriding_cheque_date;
3876
3877 -- Costing Summary
3878
3879 varname(i) := 'IS_COSTING_SUMMARY';
3880 varval(i) := lv_is_costing_summary;
3881 i := i+1;
3882 varname(i) := 'SUMMARY_COSTING_ACCRUALS';
3883 varval(i) := p_cost_summary_accruals;
3884 i := i+1;
3885 varname(i) := 'SUMMARY_COSTING_ACCRUALS_NAME';
3886 varval(i) := lv_cost_summary_accruals;
3887 i := i+1;
3888 varname(i) := 'SUMMARY_COSTING_FILE_FORMAT';
3889 varval(i) := p_cost_summary_file_out;
3890 i := i+1;
3891 varname(i) := 'SUMMARY_COSTING_FILE_FMT_NAME';
3892 varval(i) := lv_cost_summary_file_out;
3893 i := i+1;
3894
3895 -- Costing Detail
3896
3897 varname(i) := 'IS_COSTING_DETAIL';
3898 varval(i) := lv_is_costing_detail;
3899 i := i+1;
3900 varname(i) := 'DETAIL_COSTING_CRITERION';
3901 varval(i) := p_cost_detail_selection;
3902 i := i+1;
3903 varname(i) := 'DETAIL_COSTING_IS_ELEMENT_SET';
3904 varval(i) := p_cost_detail_is_ele_set;
3905 i := i+1;
3906 varname(i) := 'DETAIL_COSTING_ELEMENT_SET';
3907 varval(i) := p_cost_detail_ele_set;
3908 i := i+1;
3909 varname(i) := 'DETAIL_COSTING_ELE_SET_NAME';
3910 varval(i) := lv_cost_detail_ele_set;
3911 i := i+1;
3912 varname(i) := 'DETAIL_COSTING_IS_ELEMENT_C';
3913 varval(i) := p_cost_detail_is_class;
3914 i := i+1;
3915 varname(i) := 'DETAIL_COSTING_ELEMENT_C';
3916 varval(i) := p_cost_detail_class;
3917 i := i+1;
3918 varname(i) := 'DETAIL_COSTING_ELEMENT_C_NAME';
3919 varval(i) := lv_cost_detail_ele_class;
3920 i := i+1;
3921 varname(i) := 'DETAIL_COSTING_IS_ELEMENT';
3922 varval(i) := p_cost_detail_is_element;
3923 i := i+1;
3924 varname(i) := 'DETAIL_COSTING_ELEMENT';
3925 varval(i) := p_cost_detail_element;
3926 i := i+1;
3927 varname(i) := 'DETAIL_COSTING_ELEMENT_NAME';
3928 varval(i) := lv_cost_detail_ele_name;
3929 i := i+1;
3930 varname(i) := 'DETAIL_COSTING_ASG_SET';
3931 varval(i) := p_cost_detail_asg_set;
3932 i := i+1;
3933 varname(i) := 'DETAIL_COSTING_ASG_SET_NAME';
3934 varval(i) := lv_cost_detail_asg_set;
3935 i := i+1;
3936 varname(i) := 'DETAIL_COSTING_ACCRUALS';
3937 varval(i) := p_cost_detail_accruals;
3938 i := i+1;
3939 varname(i) := 'DETAIL_COSTING_ACCRUALS_NAME';
3940 varval(i) := lv_cost_detail_accruals;
3941 i := i+1;
3942
3943
3944 i := i+1;
3945 num_varname(i) := 'P_BUSINESS_GROUP_ID';
3946 num_varvalue(i) := p_business_group_id;
3947 i := i+1;
3948 num_varname(i) := 'BATCH_ID';
3949 num_varvalue(i) := p_batch_id;
3950 i := i+1;
3951 num_varname(i) := 'START_CHECK_NUMBER';
3952 num_varvalue(i) := to_number(p_start_check_number);
3953 i := i+1;
3954 num_varname(i) := 'END_CHECK_NUMBER';
3955 num_varvalue(i) := to_number(p_end_check_number);
3956 i := i+1;
3957 num_varname(i) := 'START_CHECK_NUMBER_3RD_PARTY';
3958 num_varvalue(i) := to_number(p_start_check_num_3rd_party);
3959 i := i+1;
3960 num_varname(i) := 'END_CHECK_NUMBER_3RD_PARTY';
3961 num_varvalue(i) := to_number(p_end_check_num_3rd_party);
3962 i := i+1;
3963 num_varname(i) := 'USER_ID';
3964 num_varvalue(i) := l_user_id;
3965 i := i+1;
3966 num_varname(i) := 'APPLICATION_ID';
3967 num_varvalue(i) := l_resp_appl_id;
3968 i := i+1;
3969 num_varname(i) := 'RESPONSIBILITY_ID';
3970 num_varvalue(i) := l_resp_id;
3971 i := i+1;
3972 num_varname(i) := 'ORG_ID';
3973 num_varvalue(i) := l_org_id;
3974 i := i+1;
3975 num_varname(i) := 'PER_SECURITY_PROFILE_ID';
3976 num_varvalue(i) := l_profile_per_sec_id;
3977 i := i+1;
3978 num_varname(i) := 'SECURITY_GROUP_ID';
3979 num_varvalue(i) := l_profile_sec_grp_id;
3980
3981 for j in varname.first..varname.last loop
3982 hr_utility.trace( 'J = '|| j );
3983 hr_utility.trace( 'varname = '||varname(j));
3984 hr_utility.trace( 'varval = '||varval(j));
3985 end loop;
3986
3987 Wf_Engine.SetItemAttrTextArray(ItemType, ItemKey, varname, varval);
3988 hr_utility.trace( 'Total Var Kount = '||to_char(varname.COUNT));
3989
3990 for p in num_varname.first..num_varname.last loop
3991 hr_utility.trace( 'p = '|| p );
3992 hr_utility.trace( 'num_varname = '||num_varname(p));
3993 hr_utility.trace( 'num_varval = '||num_varvalue(p));
3994 end loop;
3995
3996 Wf_Engine.SetItemAttrNumberArray(ItemType, ItemKey, num_varname, num_varvalue);
3997
3998 hr_utility.trace( 'Total Num Kount = '||to_char(num_varname.COUNT));
3999 exception
4000 when OTHERS then
4001 hr_utility.trace('In exception: OTHERS of TextArray');
4002 raise;
4003 end;
4004
4005 /*
4006 wf_engine.SetItemAttrText ( itemtype => ItemType,
4007 itemkey => ItemKey,
4008 aname => 'USER_DISPLAY_NAME',
4009 avalue => wf_directory.GetRoleDisplayName(RequestorUsername));
4010
4011 wf_engine.SetItemOwner ( itemtype => ItemType,
4012 itemkey => ItemKey,
4013 owner => ProcessOwner);
4014
4015
4016 wf_engine.SetItemAttrText ( itemtype => ItemType,
4017 itemkey => ItemKey,
4018 aname => 'MESSAGE_SUBJECT1',
4019 avalue => l_message_subject1);
4020
4021
4022 wf_engine.SetItemAttrText ( itemtype => ItemType,
4023 itemkey => ItemKey,
4024 aname => 'MESSAGE_TEXT1',
4025 avalue => l_message_text1);
4026
4027 hr_utility.trace('B4 SetItemAttrText : l_role_name = ' || l_role_name);
4028 wf_engine.SetItemAttrText ( itemtype => ItemType,
4029 itemkey => ItemKey,
4030 aname => 'ROLE_NAME',
4031 avalue => l_role_name);
4032
4033 wf_engine.SetItemAttrText ( itemtype => ItemType,
4034 itemkey => ItemKey,
4035 aname => 'APPLNAME',
4036 avalue => 'PAY');
4037
4038 hr_utility.trace('APPLNAME ');
4039 */
4040
4041 hr_utility.trace('b4 starting process');
4042
4043 wf_engine.StartProcess ( ItemType => ItemType,
4044 ItemKey => ItemKey );
4045 hr_utility.trace('a4 starting process');
4046
4047
4048
4049 exception
4050 when others then
4051 WF_CORE.Context('PAY_WF_PKG', 'StartProcess',ItemType, RequestorUsername, ProcessOwner, Workflowprocess);
4052
4053 error;
4054 RAISE;
4055
4056 end StartProcess;
4057
4058 function get_notifier( ln_payroll_id in number,
4059 ln_gre_id in number,
4060 l_effective_date in varchar2
4061 ) return varchar2 is
4062
4063 /******************************************************************
4064 **
4065 ** Description:
4066 **
4067 ** Access Status:
4068 **
4069 ******************************************************************/
4070
4071 l_proc varchar2(80);
4072 lv_contact_source VARCHAR2(50);
4073 ln_current_assignment_id per_assignments_f.assignment_id%TYPE;
4074 lv_contact_user_name VARCHAR2(150);
4075 ln_contact_person_id per_people_f.person_id%TYPE;
4076 ln_employee_person_id per_people_f.person_id%TYPE;
4077
4078 -- get the payroll contact
4079
4080 CURSOR c_payroll_contact IS
4081 select prl.prl_information1
4082 from pay_payrolls_f prl
4083 where prl.payroll_id = ln_payroll_id
4084 and prl.prl_information_category = 'US'
4085 and fnd_date.canonical_to_date(l_effective_date) between
4086 prl.effective_start_date and prl.effective_end_date;
4087
4088
4089 -- get the GRE contact
4090
4091 CURSOR c_gre_contact IS
4092 select org.org_information1
4093 from hr_organization_information org
4094 where org.org_information_context || '' = 'Contact Information'
4095 and org.organization_id = ln_gre_id;
4096
4097 begin
4098
4099 l_proc := gv_package||'.get_notifier';
4100 lv_contact_source := fnd_profile.value('HR_PAYROLL_CONTACT_SOURCE');
4101
4102 hr_utility.trace('Profile Value is : '|| lv_contact_source);
4103 hr_utility.trace('Payroll Id is : '|| ln_payroll_id);
4104 hr_utility.trace('GRE Id is : '|| ln_gre_id);
4105 hr_utility.trace('Effective Date is : '|| l_effective_date);
4106
4107
4108
4109 if lv_contact_source = 'PAYROLL' then
4110 open c_payroll_contact;
4111 fetch c_payroll_contact into lv_contact_user_name;
4112 hr_utility.trace('Contact User is : '|| lv_contact_user_name);
4113 if c_payroll_contact%NOTFOUND then
4114 lv_contact_user_name := 'SYSADMIN';
4115 end if;
4116
4117 close c_payroll_contact;
4118
4119
4120 elsif lv_contact_source = 'GRE' then
4121 open c_gre_contact;
4122 fetch c_gre_contact into lv_contact_user_name;
4123 if c_gre_contact%NOTFOUND then
4124 lv_contact_user_name := 'SYSADMIN';
4125 end if;
4126
4127 close c_gre_contact;
4128
4129 elsif lv_contact_source = 'CUSTOM' then
4130 lv_contact_user_name := 'SYSADMIN';
4131 else -- some other source we don't understand yet
4132 lv_contact_user_name := 'SYSADMIN';
4133 --lv_contact_user_name := null;
4134 end if;
4135
4136 return lv_contact_user_name;
4137
4138 hr_utility.set_location('Leaving: ' || l_proc, 100);
4139 end get_notifier;
4140
4141
4142 procedure error
4143 is
4144 begin
4145 /*
4146 dbms_output.put_line('Run time error in test script');
4147 dbms_output.put_line('Sqlerror: '||sqlerrm);
4148 dbms_output.put_line('Errname: '||wf_core.error_name);
4149 dbms_output.put_line('Errmsg: '||substr(wf_core.error_message, 1, 200));
4150 dbms_output.put_line('Stack1: '||substr(wf_core.error_stack, 1, 200));
4151 dbms_output.put_line('Stack2: '||substr(wf_core.error_stack, 200, 200));
4152 dbms_output.put_line('Stack3: '||substr(wf_core.error_stack, 400, 200));
4153 dbms_output.put_line('Stack4: '||substr(wf_core.error_stack, 600, 200));
4154 dbms_output.put_line('Stack5: '||substr(wf_core.error_stack, 800, 200));
4155 dbms_output.put_line('Stack6: '||substr(wf_core.error_stack, 1000, 200));
4156 dbms_output.put_line('Stack7: '||substr(wf_core.error_stack, 1200, 200));
4157 dbms_output.put_line('Stack8: '||substr(wf_core.error_stack, 1400, 200));
4158 dbms_output.put_line('Stack9: '||substr(wf_core.error_stack, 1600, 200));
4159 dbms_output.put_line('Stack10: '||substr(wf_core.error_stack, 1800, 200));
4160 */
4161
4162 hr_utility.trace('Run time error in test script');
4163 hr_utility.trace('Sqlerror: '||sqlerrm);
4164 hr_utility.trace('Errname: '||wf_core.error_name);
4165 hr_utility.trace('Errmsg: '||substr(wf_core.error_message, 1, 200));
4166 hr_utility.trace('Stack1: '||substr(wf_core.error_stack, 1, 200));
4167 hr_utility.trace('Stack2: '||substr(wf_core.error_stack, 200, 200));
4168 hr_utility.trace('Stack3: '||substr(wf_core.error_stack, 400, 200));
4169 hr_utility.trace('Stack4: '||substr(wf_core.error_stack, 600, 200));
4170 hr_utility.trace('Stack5: '||substr(wf_core.error_stack, 800, 200));
4171 hr_utility.trace('Stack6: '||substr(wf_core.error_stack, 1000, 200));
4172 hr_utility.trace('Stack7: '||substr(wf_core.error_stack, 1200, 200));
4173 hr_utility.trace('Stack8: '||substr(wf_core.error_stack, 1400, 200));
4174 hr_utility.trace('Stack9: '||substr(wf_core.error_stack, 1600, 200));
4175 hr_utility.trace('Stack10: '||substr(wf_core.error_stack, 1800, 200));
4176
4177 end error;
4178
4179
4180 end PAY_WF_PKG;