[Home] [Help]
PACKAGE BODY: APPS.PAY_WF_PKG
Source
1 PACKAGE BODY PAY_WF_PKG AS
2 /* $Header: payuswfpkg.pkb 120.1 2005/10/10 16:36:06 jgoswami noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Package Body Name : PAY_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
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
36 HTML Message as Retro Notification
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
63 *******************************************************************/
64
65
66
67 procedure payroll_wf_process(errbuf OUT nocopy VARCHAR2,
68 retcode OUT nocopy NUMBER,
69 p_wf_item_type Varchar2,
70 p_business_group_id number ,
71 p_batch_id number ,
72 p_payroll_id varchar2,
73 p_payroll_id_dummy varchar2 ,
74 p_consolidation_set_id varchar2,
75 p_consolidation_set_id_dummy varchar2 ,
76 p_date_earned varchar2,
77 p_date_paid varchar2,
78 p_period varchar2,
79 p_payroll_element_set_name varchar2,
80 p_payroll_assignment_set_name varchar2,
81 p_payroll_run_type varchar2,
82 p_event_group varchar2,
83 p_retro_assignment_set_name varchar2,
84 p_retro_assignment_set_dummy varchar2,
85 p_retro_element_set varchar2,
86 p_retro_start_date varchar2,
87 p_effective_date varchar2,
88 p_gre varchar2,
89 p_gre_id_dummy varchar2,
90 p_p_payroll_action_id varchar2,
91 p_organization varchar2,
92 p_location varchar2,
93 p_legislation_code varchar2,
94 p_select_report_or_group varchar2,
95 p_is_exception_group varchar2,
96 p_exception_group_name varchar2,
97 p_exception_group_name_dummy varchar2,
98 p_is_exception_report varchar2,
99 p_exception_report_name varchar2,
100 p_exception_report_name_dummy varchar2,
101 p_over_ride_variance_type varchar2,
102 p_over_ride_varianc_type_dumy varchar2,
103 p_over_ride_variance_value varchar2,
104 p_over_ride_varinc_value_dumy varchar2,
105 p_payment_method_override varchar2,
106 p_nacha_payment_type varchar2,
107 p_nacha_payment_method varchar2,
108 p_deposit_date_override varchar2,
109 p_file_id_modifier varchar2,
110 p_file_id_modifier_check varchar2,
111 p_thrid_party_check varchar2,
112 p_check_writer_payment_type varchar2,
113 p_check_style varchar2,
114 p_check_writer_payment_method varchar2,
115 p_check_writer_sort_sequence varchar2,
116 p_start_check_number varchar2,
117 p_end_check_number varchar2,
118 p_overriding_cheque_date varchar2,
119 p_payment_method_3rd_party varchar2,
120 p_sort_sequence_3rd_party varchar2,
121 p_start_check_num_3rd_party varchar2,
122 p_end_check_num_3rd_party varchar2,
123 p_da_report_category varchar2,
124 p_da_sort_sequence varchar2,
125 p_da_assignment_set varchar2,
126 p_assignment_set_dummy varchar2,
127 p_selection_criterion varchar2,
128 p_is_element_set varchar2,
129 p_ele_reg_element_set varchar2,
130 p_is_element_classification varchar2,
131 p_element_classification varchar2,
132 p_is_element_name varchar2,
133 p_element varchar2,
134 p_ele_reg_employee varchar2,
135 p_costing_process varchar2,
136 p_dummy_cost_run varchar2,
137 p_cost_summary_accruals varchar2,
138 p_cost_summary_file_out varchar2,
139 p_cost_detail_selection varchar2,
140 p_cost_detail_is_ele_set varchar2,
141 p_cost_detail_ele_set varchar2,
142 p_cost_detail_is_class varchar2,
143 p_cost_detail_class varchar2,
144 p_cost_detail_is_element varchar2,
145 p_cost_detail_element varchar2,
146 p_cost_detail_asg_set varchar2,
147 p_cost_detail_accruals varchar2,
148 p_start_date varchar2,
149 p_end_date varchar2,
150 p_ppa_finder varchar2,
151 p_ppa_finder_pqp varchar2
152 ) is
153
154 l_valid_status varchar2(5);
155 l_program varchar2(100);
156 l_workflowprocess varchar2(100);
157 l_ProcessDesc varchar2(100);
158 l_RequestorUsername Varchar2(100);
159 l_ProcessOwner Varchar2(100);
160 l_item_type Varchar2(100);
161 l_item_key varchar2(100);
162 lv_runnable_process Varchar2(1);
163
164 --
165 --
166 begin
167 --hr_utility.trace_on(null,'PYWF');
168 gv_package := 'pay_wf_pkg';
169 l_RequestorUsername := 'SYSADMIN';
170
171 select to_char(sysdate,'DDHH24MISS') into l_item_key from dual;
172
173 -- initialise variable - 0 is SRS Success, 1 is SRS Warning, 2 is SRS Error
174 retcode := 0;
175
176
177 l_workflowprocess := 'PAYUSPROCESSWF';
178 l_item_type := p_wf_item_type;
179
180 BEGIN
181 select RUNNABLE_FLAG
182 into lv_runnable_process
183 from wf_activities
184 where item_type = p_wf_item_type
185 and type = 'PROCESS'
186 and name = l_workflowprocess
187 and end_date is null;
188
189 Exception when others then
190 hr_utility.trace('In exception: OTHERS at payroll_wf_process');
191 null;
192
193 END;
194
195
196
197 hr_utility.trace('Item Type is : '|| l_item_type);
198 hr_utility.trace('Item Key is : '|| l_item_key);
199 hr_utility.trace('Workflow Process is : '|| l_workflowprocess);
200
201 if lv_runnable_process = 'Y' then
202
203 StartProcess( p_business_group_id,
204 p_batch_id ,
205 p_payroll_id ,
206 p_payroll_id_dummy ,
207 p_consolidation_set_id ,
208 p_consolidation_set_id_dummy ,
209 p_date_earned ,
210 p_date_paid ,
211 p_period ,
212 p_payroll_element_set_name ,
213 p_payroll_assignment_set_name ,
214 p_payroll_run_type ,
215 p_event_group ,
216 p_retro_assignment_set_name ,
217 p_retro_assignment_set_dummy ,
218 p_retro_element_set ,
219 p_retro_start_date ,
220 p_effective_date ,
221 p_gre ,
222 p_gre_id_dummy ,
223 p_p_payroll_action_id ,
224 p_organization ,
225 p_location ,
226 p_legislation_code ,
227 p_select_report_or_group ,
228 p_is_exception_group ,
229 p_exception_group_name ,
230 p_exception_group_name_dummy ,
231 p_is_exception_report ,
232 p_exception_report_name ,
233 p_exception_report_name_dummy ,
234 p_over_ride_variance_type ,
235 p_over_ride_varianc_type_dumy ,
236 p_over_ride_variance_value ,
237 p_over_ride_varinc_value_dumy ,
238 p_payment_method_override ,
239 p_nacha_payment_type ,
240 p_nacha_payment_method ,
241 p_deposit_date_override ,
242 p_file_id_modifier ,
243 p_file_id_modifier_check ,
244 p_thrid_party_check ,
245 p_check_writer_payment_type ,
246 p_check_style ,
247 p_check_writer_payment_method ,
248 p_check_writer_sort_sequence ,
249 p_start_check_number ,
250 p_end_check_number ,
251 p_overriding_cheque_date ,
252 p_payment_method_3rd_party ,
253 p_sort_sequence_3rd_party ,
254 p_start_check_num_3rd_party ,
255 p_end_check_num_3rd_party ,
256 p_da_report_category ,
257 p_da_sort_sequence ,
258 p_da_assignment_set ,
259 p_assignment_set_dummy ,
260 p_selection_criterion ,
261 p_is_element_set ,
262 p_ele_reg_element_set ,
263 p_is_element_classification ,
264 p_element_classification ,
265 p_is_element_name ,
266 p_element ,
267 p_ele_reg_employee ,
268 p_costing_process ,
269 p_dummy_cost_run ,
270 p_cost_summary_accruals ,
271 p_cost_summary_file_out ,
272 p_cost_detail_selection ,
273 p_cost_detail_is_ele_set ,
274 p_cost_detail_ele_set ,
275 p_cost_detail_is_class ,
276 p_cost_detail_class ,
277 p_cost_detail_is_element ,
278 p_cost_detail_element ,
279 p_cost_detail_asg_set ,
280 p_cost_detail_accruals ,
281 p_start_date ,
282 p_end_date ,
283 p_ppa_finder ,
284 p_ppa_finder_pqp ,
285 l_ProcessDesc,
286 l_RequestorUsername,
287 l_ProcessOwner,
288 l_workflowprocess,
289 l_item_type,
290 l_item_key
291 );
292
293 end if;
294
295 EXCEPTION
296 --
297 WHEN hr_utility.hr_error THEN
298 --
299 -- Set up error message and error return code.
300 --
301 --hr_utility.trace('in the exception');
302 errbuf := hr_utility.get_message;
303 retcode := 2;
304 --
305 --
306 WHEN others THEN
307 --
308 -- Set up error message and return code.
309 errbuf := sqlerrm;
310 retcode := 2;
311 end payroll_wf_process;
312
313
314 --
315 -- Start Workflow Process will Create a Process and Set the Attributes
316 -- for the Workflow Process.
317
318
319 procedure StartProcess (
320 p_business_group_id number ,
321 p_batch_id number ,
322 p_payroll_id varchar2,
323 p_payroll_id_dummy varchar2 ,
324 p_consolidation_set_id varchar2,
325 p_consolidation_set_id_dummy varchar2 ,
326 p_date_earned varchar2,
327 p_date_paid varchar2,
328 p_period varchar2,
329 p_payroll_element_set_name varchar2,
330 p_payroll_assignment_set_name varchar2,
331 p_payroll_run_type varchar2,
332 p_event_group varchar2,
333 p_retro_assignment_set_name varchar2,
334 p_retro_assignment_set_dummy varchar2,
335 p_retro_element_set varchar2,
336 p_retro_start_date varchar2,
337 p_effective_date varchar2,
338 p_gre varchar2,
339 p_gre_id_dummy varchar2,
340 p_p_payroll_action_id varchar2,
341 p_organization varchar2,
342 p_location varchar2,
343 p_legislation_code varchar2,
344 p_select_report_or_group varchar2,
345 p_is_exception_group varchar2,
346 p_exception_group_name varchar2,
347 p_exception_group_name_dummy varchar2,
348 p_is_exception_report varchar2,
349 p_exception_report_name varchar2,
350 p_exception_report_name_dummy varchar2,
351 p_over_ride_variance_type varchar2,
352 p_over_ride_varianc_type_dumy varchar2,
353 p_over_ride_variance_value varchar2,
354 p_over_ride_varinc_value_dumy varchar2,
355 p_payment_method_override varchar2,
356 p_nacha_payment_type varchar2,
357 p_nacha_payment_method varchar2,
358 p_deposit_date_override varchar2,
359 p_file_id_modifier varchar2,
360 p_file_id_modifier_check varchar2,
361 p_thrid_party_check varchar2,
362 p_check_writer_payment_type varchar2,
363 p_check_style varchar2,
364 p_check_writer_payment_method varchar2,
365 p_check_writer_sort_sequence varchar2,
366 p_start_check_number varchar2,
367 p_end_check_number varchar2,
368 p_overriding_cheque_date varchar2,
369 p_payment_method_3rd_party varchar2,
370 p_sort_sequence_3rd_party varchar2,
371 p_start_check_num_3rd_party varchar2,
372 p_end_check_num_3rd_party varchar2,
373 p_da_report_category varchar2,
374 p_da_sort_sequence varchar2,
375 p_da_assignment_set varchar2,
376 p_assignment_set_dummy varchar2,
377 p_selection_criterion varchar2,
378 p_is_element_set varchar2,
379 p_ele_reg_element_set varchar2,
380 p_is_element_classification varchar2,
381 p_element_classification varchar2,
382 p_is_element_name varchar2,
383 p_element varchar2,
384 p_ele_reg_employee varchar2,
385 p_costing_process varchar2,
386 p_dummy_cost_run varchar2,
387 p_cost_summary_accruals varchar2,
388 p_cost_summary_file_out varchar2,
389 p_cost_detail_selection varchar2,
390 p_cost_detail_is_ele_set varchar2,
391 p_cost_detail_ele_set varchar2,
392 p_cost_detail_is_class varchar2,
393 p_cost_detail_class varchar2,
394 p_cost_detail_is_element varchar2,
395 p_cost_detail_element varchar2,
396 p_cost_detail_asg_set varchar2,
397 p_cost_detail_accruals varchar2,
398 p_start_date varchar2,
399 p_end_date varchar2,
400 p_ppa_finder varchar2,
401 p_ppa_finder_pqp varchar2,
402 ProcessDesc varchar2,
403 RequestorUsername varchar2,
404 ProcessOwner in varchar2,
405 Workflowprocess in varchar2,
406 item_type in varchar2,
407 item_key in varchar2
408 ) is
409
410 ItemType varchar2(30);
411 ItemKey varchar2(30);
412 ItemUserKey varchar2(80);
413 l_business_group_id number(30);
414 l_payroll_id number(16);
415 l_consolidation_set_id number(16);
416 ln_asignment_id NUMBER(30);
417
418 l_message_subject1 varchar2(240);
419 l_message_text1 varchar2(3200) ;
420 l_message_html_text1 varchar2(3200) ;
421 l_message_html_text2 varchar2(3200) ;
422 l_pay_excep_rpt_message_text1 varchar2(3200) ;
423 l_pay_excep_rpt_message_html1 varchar2(3200) ;
424 l_ele_reg_rpt_message_text1 varchar2(3200) ;
425 l_ele_reg_rpt_message_html1 varchar2(3200) ;
426 l_current_user varchar2(40);
427 l_current_user_display_name varchar2(40);
428 l_app_user_name varchar2(40);
429 l_orig_system varchar2(40);
430 l_orig_system_id varchar2(40);
431 l_role_name varchar2(50);
432 l_role_display_name varchar2(50);
433 lv_currency_code varchar2(5);
434 lv_is_batch varchar2(1);
435 lv_is_retro_ntfy varchar2(1);
436 lv_is_nacha varchar2(1);
437 lv_is_check varchar2(1);
438 lv_is_third_party_check varchar2(1);
439 lv_is_deposit_advice varchar2(1);
440 lv_is_payroll_exception_report varchar2(1);
441 lv_is_ele_reg varchar2(1);
442 lv_isResponseRequired varchar2(1) ;
443 lv_isPaymentWait varchar2(1) ;
444 lv_Payroll_WF_Notify_Action Varchar2(30);
445 ln_nacha_payment_type_id Varchar2(30);
446 ln_check_payment_type_id Varchar2(30);
447 lv_da_report Varchar2(30);
448
449 lv_payroll_name varchar2(80);
450 lv_consolidation_set_name varchar2(80);
451 lv_gre_name varchar2(80);
452 lv_org_name varchar2(80);
453 lv_loc_name varchar2(80);
454 lv_pre_ovr_pymt_name varchar2(80);
455 lv_check_pymt_name varchar2(80);
456 lv_nacha_pymt_name varchar2(80);
457 lv_event_group_name varchar2(80);
458 lv_loc_name varchar2(80);
459 lv_exc_grp_name varchar2(80);
460 lv_grp_orv_type varchar2(80);
461 lv_grp_orv_value varchar2(80);
462 lv_exc_rep_name varchar2(80);
463 lv_orv_type varchar2(80);
464 lv_orv_value varchar2(80);
465 lv_check_sort_seq_meaning varchar2(80);
466 lv_period_name varchar2(80);
467 lv_element_name varchar2(80);
468 lv_batch_name varchar2(80);
469
470 --l_profile_per_sec_id VARCHAR2(100);
471 l_profile_per_sec_id Number(15);
472 l_profile_per_bg VARCHAR2(100);
473 --l_profile_sec_grp_id VARCHAR2(100);
474 l_profile_sec_grp_id Number(15);
475 l_profile_user_name VARCHAR2(100);
476 lv_prc_list varchar2(3200);
477 lv_prc_list_html varchar2(3200);
478 lv_date_time varchar2(80);
479 l_proc varchar2(80);
480 lv_contact_user_name VARCHAR2(80);
481 l_user_id NUMBER;
482 l_resp_id NUMBER;
483 l_resp_appl_id NUMBER;
484 l_org_id NUMBER;
485 l_per_sec_id NUMBER;
486 l_sec_grp_id NUMBER;
487 i_count number;
488
489 lv_is_costing_summary varchar2(1);
490 lv_is_costing_detail varchar2(1);
491 lv_cost_summary_accruals varchar2(80);
492 lv_cost_summary_file_out varchar2(80);
493 lv_cost_detail_ele_set varchar2(80);
494 lv_cost_detail_ele_class varchar2(80);
495 lv_cost_detail_ele_name varchar2(80);
496 lv_cost_detail_asg_set varchar2(80);
497 lv_cost_detail_accruals varchar2(80);
498
499 varname Wf_Engine.NameTabTyp;
500 varval Wf_Engine.TextTabTyp;
501
502 num_varname Wf_Engine.NameTabTyp;
503 num_varvalue Wf_Engine.NumTabTyp;
504
505 TYPE char80_table IS TABLE OF VARCHAR2(80)
506 INDEX BY BINARY_INTEGER;
507
508 lv_conc_prog_name char80_table;
509 lv_cur_process char80_table;
510 i number;
511 j number;
512 k number;
513 l number;
514 n number;
515 p number;
516
517
518 cursor cur_costing_summary_accruals is
519 select meaning
520 from hr_lookups
521 where lookup_code = p_cost_summary_accruals
522 and lookup_type = 'PAY_PAYRPCBR';
523
524 cursor cur_costing_summary_file_out is
525 select meaning
526 from fnd_common_lookups
527 where lookup_code = p_cost_summary_file_out
528 and lookup_type = 'REPORT_OUTPUT_TYPE'
529 and application_id = 801
530 and enabled_flag = 'Y';
531
532 cursor cur_costing_detail_ele_set is
533 select element_set_name
534 from pay_element_sets
535 where element_set_id = to_number(p_cost_detail_ele_set)
536 and element_set_type = 'C';
537
538 cursor cur_costing_detail_ele_class is
539 select classification_name
540 from pay_element_classifications
541 where classification_id = to_number(p_cost_detail_class);
542
543 cursor cur_costing_detail_ele_name is
544 select tl.element_name
545 from pay_element_types_f el, pay_element_types_f_tl tl
546 where el.element_type_id = to_number(p_cost_detail_element)
547 and el.element_type_id = tl.element_type_id
548 and tl.language = userenv('LANG')
549 and fnd_date.canonical_to_date(p_date_earned)
550 between el.effective_start_date and el.effective_end_date;
551
552 cursor cur_costing_detail_asg_set is
553 select assignment_set_name
554 from hr_assignment_sets
555 where assignment_set_id = to_number(p_cost_detail_asg_set);
556
557 cursor cur_costing_detail_accruals is
558 select meaning
559 from hr_lookups
560 where lookup_code = p_cost_detail_accruals
561 and lookup_type = 'PAY_PAYRPCBR';
562
563
564 begin
565
566 -- Initialize Variables
567 l_proc := gv_package||'.StartProcess';
568 ItemType := item_type;
569 ItemKey := item_key;
570 ItemUserKey := ProcessDesc;
571 l_business_group_id := p_business_group_id;
572 l_payroll_id := to_number(p_payroll_id);
573 l_consolidation_set_id := to_number(p_consolidation_set_id);
574 lv_is_batch := 'N';
575 lv_is_retro_ntfy := 'N';
576 lv_is_nacha := 'N';
577 lv_is_check := 'N';
578 lv_is_third_party_check := 'N';
579 lv_is_deposit_advice := 'N';
580 lv_is_payroll_exception_report := 'N';
581 lv_is_ele_reg := 'N';
582 --hhh
583
584 -- Initialize the Process and Report List.
585 lv_conc_prog_name(1) := 'BEE Batch Process (Transfer)';
586 lv_conc_prog_name(2) := 'Retro-Notifications Report';
587 lv_conc_prog_name(3) := 'Retro Pay By Element';
588 lv_conc_prog_name(4) := 'Payroll Process';
589 lv_conc_prog_name(5) := 'US Gross to Net Summary';
590 lv_conc_prog_name(6) := 'Payroll Message Report';
591 lv_conc_prog_name(7) := 'Employee Assignments Not Processed';
592 lv_conc_prog_name(8) := 'Payroll Exception Report';
593 lv_conc_prog_name(9) := 'Federal and State Tax Remittance Report';
594 lv_conc_prog_name(10) := 'Pre Payments';
595 lv_conc_prog_name(11) := 'NACHA';
596 lv_conc_prog_name(12) := 'External Process Archive';
597 lv_conc_prog_name(13) := 'Check Writer';
598 lv_conc_prog_name(14) := 'Third Party Check Writer';
599 lv_conc_prog_name(15) := 'Deposit Advice';
600 lv_conc_prog_name(16) := 'Element Register Report';
601 lv_conc_prog_name(17) := 'Payment Register Report';
602 lv_conc_prog_name(18) := 'Third Party Payment Register Report';
603 lv_conc_prog_name(19) := 'Costing Process';
604 lv_conc_prog_name(20) := 'Costing Summary Report';
605 lv_conc_prog_name(21) := 'Costing Detail Report';
606
607 -- wf_core.clear;
608
609 -- make sure the process terminated by aborting it first
610 -- begin
611 -- wf_engine.abortprocess(ItemType, ItemKey);
612 -- exception
613 -- when others then null;
614 -- end;
615
616 hr_utility.trace('In StartProcess Item Type is : '|| ItemType);
617 hr_utility.trace('In StartProcess Item Key is : '|| ItemKey);
618 hr_utility.trace('WF process name is : '|| Workflowprocess);
619
620 begin
621 select to_char(sysdate,'DD-MON-YYYY') ||' '|| to_char(sysdate,'HH24:MI:SS') into lv_date_time from dual;
622 end;
623
624 hr_utility.trace('b4 create_process');
625
626 wf_engine.createProcess( ItemType => ItemType,
627 ItemKey => ItemKey,
628 process => Workflowprocess);
629
630 wf_engine.SetItemUserKey( ItemType => ItemType,
631 ItemKey => ItemKey,
632 UserKey => ItemUserKey);
633 hr_utility.trace('In Start Process b4 HR_SIGNON.Initialize_HR_Security');
634 HR_SIGNON.Initialize_HR_Security;
635 hr_utility.trace('A4 HR_SIGNON.Initialize_HR_Security of Start ');
636
637 l_user_id:= FND_GLOBAL.USER_ID;
638 l_resp_appl_id:= FND_GLOBAL.RESP_APPL_ID;
639 l_resp_id:= FND_GLOBAL.RESP_ID;
640 l_org_id:= FND_GLOBAL.ORG_ID;
641
642 l_per_sec_id := FND_GLOBAL.PER_SECURITY_PROFILE_ID;
643 l_sec_grp_id := FND_GLOBAL.SECURITY_GROUP_ID;
644
645 hr_utility.trace('l_user_id = ' || l_user_id);
646 hr_utility.trace('l_resp_appl_id = ' || l_resp_appl_id);
647 hr_utility.trace('l_resp_id = ' || l_resp_id);
648 hr_utility.trace('l_org_id = ' || l_org_id);
649 hr_utility.trace('l_per_sec_id = ' || l_per_sec_id);
650 hr_utility.trace('l_sec_grp_id = ' || l_sec_grp_id);
651 hr_utility.trace('BG = ' || to_char(p_business_group_id));
652 hr_utility.trace('PAYMENT TYPE = ' || p_nacha_payment_type);
653
654
655 /*
656 fnd_global.apps_initialize(l_user_id,l_resp_id,l_resp_appl_id);
657 */
658
659 Begin
660 select currency_code into lv_currency_code
661 from per_business_groups
662 where business_group_id = p_business_group_id;
663 Exception when others then
664 hr_utility.trace('In exception: OTHERS ');
665 null;
666
667 END;
668
669
670 Begin
671 SELECT to_char(PPT.PAYMENT_TYPE_ID) into ln_nacha_payment_type_id
672 FROM PAY_PAYMENT_TYPES PPT,
673 PER_BUSINESS_GROUPS BG
674 WHERE ( ((PPT.TERRITORY_CODE IS NOT NULL
675 AND PPT.TERRITORY_CODE=BG.LEGISLATION_CODE)
676 OR PPT.TERRITORY_CODE IS NULL)
677 AND BG.BUSINESS_GROUP_ID= p_business_group_id
678 AND PPT.PAYMENT_TYPE_NAME = 'NACHA'
679 );
680 Exception when others then
681 hr_utility.trace('In exception: OTHERS ');
682 null;
683
684 END;
685
686
687 Begin
688 SELECT to_char(PPT.PAYMENT_TYPE_ID)
689 into ln_check_payment_type_id
690 FROM PAY_PAYMENT_TYPES PPT,
691 PER_BUSINESS_GROUPS BG
692 WHERE ( ((PPT.TERRITORY_CODE IS NOT NULL
693 AND PPT.TERRITORY_CODE=BG.LEGISLATION_CODE)
694 OR PPT.TERRITORY_CODE IS NULL)
695 AND BG.BUSINESS_GROUP_ID= p_business_group_id
696 AND PPT.PAYMENT_TYPE_NAME = 'Check'
697 );
698 Exception when others then
699 hr_utility.trace('In exception: OTHERS ');
700 null;
701
702 END;
703
704
705 Begin
706 select report_name into lv_da_report
707 from pay_report_format_mappings_f
708 where report_type = 'DAR'
709 and report_category = p_da_report_category;
710 Exception when others then
711 hr_utility.trace('In exception: OTHERS ');
712 null;
713
714 END;
715
716
717 Begin
718 -- Payroll Name
719 select payroll_name into lv_payroll_name
720 from pay_all_payrolls_f
721 where payroll_id = p_payroll_id
722 and fnd_date.canonical_to_date(p_date_earned)
723 between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
724
725 hr_utility.trace('lv_payroll_name = ' || lv_payroll_name);
726 Exception when others then
727 hr_utility.trace('In exception: OTHERS ');
728 null;
729
730 END;
731
732 Begin
733 -- Consolidation Set Name
734 select consolidation_set_name into lv_consolidation_set_name
735 from pay_consolidation_sets
736 where consolidation_set_id = p_consolidation_set_id;
737
738 hr_utility.trace('lv_consolidation_set_name = ' || lv_consolidation_set_name);
739 Exception when others then
740 hr_utility.trace('In exception: OTHERS ');
741 null;
742
743 END;
744
745 Begin
746 -- GRE Name
747 select name into lv_gre_name
748 from hr_tax_units_v
749 where tax_unit_id = to_number(p_gre);
750 hr_utility.trace('lv_gre_name = ' || lv_gre_name);
751
752 Exception when others then
753 hr_utility.trace('In exception: OTHERS ');
754 null;
755
756 END;
757
758 Begin
759 -- Period Name
760 select PERIOD_NAME into lv_period_name
761 from per_time_periods
762 where time_period_id = p_period;
763 hr_utility.trace('lv_period_name = ' || lv_period_name);
764
765 Exception when others then
766 hr_utility.trace('In exception: OTHERS ');
767 null;
768
769 END;
770
771 Begin
772 -- Event Group Name
773 select event_group_name
774 into lv_event_group_name
775 from pay_event_groups
776 where event_group_id = substr(p_event_group,12,length(p_event_group));
777
778 hr_utility.trace('lv_event_group_name = ' || lv_event_group_name);
779
780 Exception when others then
781 hr_utility.trace('In exception: OTHERS ');
782 null;
783
784 END;
785
786 Begin
787 -- Nacha Org Payment Method Name
788 select ORG_PAYMENT_METHOD_NAME
789 into lv_nacha_pymt_name
790 from pay_org_payment_methods_f
791 where business_group_id = p_business_group_id
792 and org_payment_method_id = p_nacha_payment_method
793 and fnd_date.canonical_to_date(p_date_earned)
794 between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
795 hr_utility.trace('lv_nacha_pymt_name = ' || lv_nacha_pymt_name);
796
797 Exception when others then
798 hr_utility.trace('In exception: OTHERS Nacha Org Payment Method Name ');
799 null;
800
801 END;
802
803 Begin
804 -- Check Org Payment Method Name
805 select ORG_PAYMENT_METHOD_NAME
806 into lv_check_pymt_name
807 from pay_org_payment_methods_f
808 where business_group_id = p_business_group_id
809 and org_payment_method_id = p_check_writer_payment_method
810 and fnd_date.canonical_to_date(p_date_earned)
811 between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
812 hr_utility.trace('lv_check_pymt_name = ' || lv_check_pymt_name);
813 Exception when others then
814 hr_utility.trace('In exception: OTHERS of Check Org Payment Method Name ');
815 null;
816
817 END;
818
819 Begin
820 -- Pre Org Payment Method Name Override
821 select ORG_PAYMENT_METHOD_NAME
822 into lv_pre_ovr_pymt_name
823 from pay_org_payment_methods_f
824 where business_group_id = p_business_group_id
825 and org_payment_method_id = p_payment_method_override
826 and fnd_date.canonical_to_date(p_date_earned)
827 between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
828 hr_utility.trace('lv_pre_ovr_pymt_name = ' || lv_pre_ovr_pymt_name);
829 Exception when others then
830 hr_utility.trace('In exception : OTHERS Pre Org Payment Method Name Override');
831 null;
832
833 END;
834
835 begin
836 open cur_costing_summary_accruals;
837 fetch cur_costing_summary_accruals into lv_cost_summary_accruals;
838 close cur_costing_summary_accruals;
839
840 hr_utility.trace('Costing Summary Accruals = ' || lv_cost_summary_accruals);
841
842 open cur_costing_summary_file_out;
843 fetch cur_costing_summary_file_out into lv_cost_summary_file_out;
844 close cur_costing_summary_file_out;
845
846 hr_utility.trace('Costing Summary File Format = ' || lv_cost_summary_file_out);
847
848 open cur_costing_detail_ele_set;
849 fetch cur_costing_detail_ele_set into lv_cost_detail_ele_set;
850 close cur_costing_detail_ele_set;
851
852 hr_utility.trace('Costing Detail Element Set = ' || lv_cost_detail_ele_set);
853
854 open cur_costing_detail_ele_name;
855 fetch cur_costing_detail_ele_name into lv_cost_detail_ele_name;
856 close cur_costing_detail_ele_name;
857
858 hr_utility.trace('Costing Detail Element Name = ' || lv_cost_detail_ele_name);
859
860 open cur_costing_detail_ele_class;
861 fetch cur_costing_detail_ele_class into lv_cost_detail_ele_class;
862 close cur_costing_detail_ele_class;
863
864 hr_utility.trace('Costing Detail Element Classification = ' || lv_cost_detail_ele_class);
865
866 open cur_costing_detail_asg_set;
867 fetch cur_costing_detail_asg_set into lv_cost_detail_asg_set;
868 close cur_costing_detail_asg_set;
869
870 hr_utility.trace('Costing Detail Assignment Set = ' || lv_cost_detail_asg_set);
871
872 open cur_costing_detail_accruals;
873 fetch cur_costing_detail_accruals into lv_cost_detail_accruals;
874 close cur_costing_detail_accruals;
875
876 hr_utility.trace('Costing Detail Accruals = ' || lv_cost_detail_accruals);
877
878 END;
879
880
881 Begin
882 -- Check lv_isResponseRequired
883 select nvl(parameter_value,'WAIT')
884 into lv_Payroll_WF_Notify_Action
885 from pay_action_parameters
886 where parameter_name = 'PAYROLL_WF_NOTIFY_ACTION';
887
888 hr_utility.trace('lv_Payroll_WF_Notify_Action = ' || lv_Payroll_WF_Notify_Action);
889 If lv_Payroll_WF_Notify_Action = 'WAIT' then
890 lv_isResponseRequired := 'Y';
891 lv_isPaymentWait := 'Y';
892 elsif lv_Payroll_WF_Notify_Action = 'NOWAIT' then
893 lv_isResponseRequired := 'N';
894 lv_isPaymentWait := 'N';
895 elsif lv_Payroll_WF_Notify_Action = 'PAYMENTWAIT' then
896 lv_isResponseRequired := 'N';
897 lv_isPaymentWait := 'Y';
898 end if;
899
900 hr_utility.trace('lv_isResponseRequire = ' || lv_isResponseRequired);
901 hr_utility.trace('lv_isPaymentWait = ' || lv_isPaymentWait);
902
903 Exception when others then
904 hr_utility.trace('In exception: OTHERS ');
905 lv_isResponseRequired := 'Y';
906 lv_isPaymentWait := 'Y';
907 hr_utility.trace('lv_isResponseRequire = ' || lv_isResponseRequired);
908
909 END;
910
911 Begin
912 -- Check Sorting Sequence
913 select meaning
914 into lv_check_sort_seq_meaning
915 from fnd_common_lookups
916 where lookup_type = 'CHEQUE_PROCEDURE'
917 and lookup_code = p_check_writer_sort_sequence;
918
919 hr_utility.trace('lv_check_sort_seq_meaning = ' || lv_check_sort_seq_meaning);
920 Exception when others then
921 hr_utility.trace('In exception: OTHERS ');
922 null;
923
924 END;
925
926 Begin
927 -- Exception REports Information
928 select per.exception_report_name ,
929 hr_general.decode_lookup('PQP_VARIANCE_TYPES',per.variance_type) ovt,
930 to_char(per.VARIANCE_VALUE)
931 into lv_exc_rep_name,lv_orv_type,lv_orv_value
932 from pqp_exception_reports per
933 where legislation_code = 'US'
934 and per.exception_report_id = p_exception_report_name;
935
936 hr_utility.trace('lv_exc_rep_name = ' || lv_exc_rep_name);
937 hr_utility.trace('lv_orv_type = ' || lv_orv_type);
938 hr_utility.trace('lv_orv_value = ' || lv_orv_value);
939
940 Exception when others then
941 hr_utility.trace('In exception: OTHERS ');
942 null;
943
944 END;
945 /*
946 Begin
947 -- Exception Group Information
948 select perg.exception_report_name ,
949 hr_general.decode_lookup('PQP_VARIANCE_TYPES',perg.variance_type) ovt,
950 to_char(VARIANCE_VALUE)
951 into lv_exc_grp_name,lv_grp_orv_type,lv_grp_orv_value
952 from pqp_exception_report_groups perg
953 where legislation_code = 'US'
954 and perg.EXCEPTION_GROUP_ID = p_exception_group_name;
955
956 hr_utility.trace('lv_gre_name = ' || lv_gre_name);
957 Exception when others then
958 hr_utility.trace('In exception: OTHERS ');
959 null;
960
961 END;
962 */
963 Begin
964 select element_name
965 into lv_element_name
966 from pay_element_types_f
967 where element_type_id = p_element;
968 Exception when others then
969 hr_utility.trace('In exception: OTHERS ');
970 null;
971
972 END;
973
974 Begin
975 If p_batch_id is not null then
976 select
977 batch_name
978 into lv_batch_name
979 from pay_batch_headers
980 where batch_id = p_batch_id;
981 End If;
982 hr_utility.trace('Batch Name :' ||lv_batch_name);
983
984 Exception when others then
985 lv_batch_name := '';
986 hr_utility.trace('In exception: Batch Name ');
987 null;
988
989 END;
990
991 Begin
992 /* currently we are only looking at the context at payroll level
993 as we do not have any required parameter for GRE on the SRS
994 screen, if we want to use the contact defined at GRE level then
995 we need to modify the package and SRS defination to get the
996 GRE_ID , and pass it in the following function.
997 */
998
999 lv_contact_user_name := get_notifier( to_number(p_payroll_id),
1000 to_number(p_gre),
1001 p_date_earned);
1002
1003 Exception when others then
1004 hr_utility.trace('In exception: OTHERS at get_notifier');
1005 null;
1006
1007 END;
1008
1009 Begin
1010
1011 select orig_system,orig_system_id,name,display_name
1012 into l_orig_system,l_orig_system_id,l_role_name,l_role_display_name
1013 from wf_roles
1014 where name = lv_contact_user_name ;
1015
1016 Exception when others then
1017 hr_utility.trace('In exception: OTHERS ');
1018 null;
1019
1020 END;
1021 -- For debugging .. remove the code
1022 Begin
1023 /*
1024 select fnd_profile_server.value('PER_SECURITY_PROFILE_ID')
1025 into l_profile_per_sec_id
1026 from dual;
1027 */
1028 select to_number(fnd_profile_server.value('PER_SECURITY_PROFILE_ID'))
1029 into l_profile_per_sec_id
1030 from dual;
1031 select fnd_profile_server.value('PER_BUSINESS_GROUP_ID')
1032 into l_profile_per_bg
1033 from dual;
1034 /*
1035 select fnd_profile_server.value('SECURITY_GROUP_ID')
1036 into l_profile_sec_grp_id
1037 from dual;
1038 */
1039 select to_number(fnd_profile_server.value('SECURITY_GROUP_ID'))
1040 into l_profile_sec_grp_id
1041 from dual;
1042
1043 select fnd_profile_server.value('USERNAME')
1044 into l_profile_user_name
1045 from dual;
1046
1047 hr_utility.trace('jj payuswfpkg l_profile_per_sec_id = '||l_profile_per_sec_id);
1048 hr_utility.trace('jj payuswfpkg l_profile_per_bg = '||l_profile_per_bg);
1049 hr_utility.trace('jj payuswfpkg l_profile_sec_grp_id = '||l_profile_sec_grp_id);
1050 hr_utility.trace('jj payuswfpkg l_profile_user_namep = '||l_profile_user_name);
1051
1052 Exception when others then
1053 hr_utility.trace('In exception: OTHERS at profile');
1054 null;
1055
1056 END;
1057
1058 --'JJ CA HRMS Manager';
1059 hr_utility.trace('orig ststem : '|| l_orig_system);
1060 hr_utility.trace('orig ststem : '|| l_orig_system_id);
1061 hr_utility.trace('l_role_name = ' || l_role_name);
1062
1063 wf_engine.SetItemOwner ( itemtype => ItemType,
1064 itemkey => ItemKey,
1065 owner => ProcessOwner);
1066
1067 --
1068 /* Set Require Values for the Variables
1069 */
1070
1071 -- BEE Transfer
1072 If p_batch_id is not null then
1073 lv_is_batch := 'Y';
1074 lv_prc_list := lv_prc_list||lv_conc_prog_name(1)||wf_core.newline;
1075 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(1)||'</p>';
1076 else
1077 lv_is_batch := 'N';
1078 end if;
1079
1080 -- Retro Pay By Element and Retro Notification Report
1081
1082 If ((p_retro_assignment_set_name is not null) and
1083 (p_event_group is not null )) then
1084 lv_is_retro_ntfy := 'Y';
1085 lv_prc_list := lv_prc_list||lv_conc_prog_name(2)||wf_core.newline;
1086 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(2)||'</p>';
1087 else
1088 lv_is_retro_ntfy := 'N';
1089 end if;
1090
1091 -- Payroll Exception Report
1092
1093 If ((p_exception_report_name is not null) OR
1094 (p_exception_group_name is not null)) then
1095 lv_is_payroll_exception_report := 'Y';
1096 lv_prc_list := lv_prc_list||lv_conc_prog_name(1)||wf_core.newline;
1097 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(1)||'</p>';
1098 else
1099 lv_is_payroll_exception_report := 'N';
1100 end if;
1101
1102 -- NACHA
1103
1104 If p_nacha_payment_method is not null then
1105 lv_is_nacha := 'Y';
1106 lv_prc_list := lv_prc_list||lv_conc_prog_name(1)||wf_core.newline;
1107 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(1)||'</p>';
1108 else
1109 lv_is_nacha := 'N';
1110 end if;
1111
1112 -- Check
1113 If (p_check_writer_payment_method is not null ) and
1114 (p_start_check_number is not null ) then
1115 lv_is_check := 'Y';
1116 lv_prc_list := lv_prc_list||lv_conc_prog_name(1)||wf_core.newline;
1117 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(1)||'</p>';
1118 else
1119 lv_is_check := 'N';
1120 end if;
1121
1122 -- Third Party Check
1123
1124 If p_payment_method_3rd_party is not null then
1125 lv_is_third_party_check := 'Y';
1126 lv_prc_list := lv_prc_list||lv_conc_prog_name(1)||wf_core.newline;
1127 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(1)||'</p>';
1128 else
1129 lv_is_third_party_check := 'N';
1130 end if;
1131
1132 -- Deposit Advice
1133
1134 If p_da_report_category is not null then
1135 lv_is_deposit_advice := 'Y';
1136 lv_prc_list := lv_prc_list||lv_conc_prog_name(1)||wf_core.newline;
1137 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(1)||'</p>';
1138 else
1139 lv_is_deposit_advice := 'N';
1140 end if;
1141
1142 -- Element Register Report
1143 If (p_selection_criterion is not null ) and
1144 ((p_ele_reg_element_set is not null) or
1145 (p_element_classification is not null) or
1146 (p_element is not null))
1147 then
1148 lv_is_ele_reg := 'Y';
1149 lv_prc_list := lv_prc_list||lv_conc_prog_name(16)||wf_core.newline;
1150 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(16)||'</p>';
1151 else
1152 lv_is_ele_reg := 'N';
1156 for k in 4..19 loop
1153 end if;
1154
1155 -- Text
1157 lv_prc_list := lv_prc_list||lv_conc_prog_name(k);
1158 end loop;
1159
1160 -- HTML
1161 for l in 4..19 loop
1162 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(l)||'</p>';
1163 end loop;
1164
1165 -- Costing Summary and Detail Report
1166 if ((p_consolidation_set_id is not null)) then
1167 lv_is_costing_detail := 'Y';
1168 lv_prc_list := lv_prc_list||lv_conc_prog_name(21)||wf_core.newline;
1169 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(21)||'</p>';
1170
1171 if (p_cost_summary_file_out is not null) then
1172 lv_is_costing_summary := 'Y';
1173 lv_prc_list := lv_prc_list||lv_conc_prog_name(20)||wf_core.newline;
1174 lv_prc_list_html := lv_prc_list_html||'<p>'||lv_conc_prog_name(20)||'</p>';
1175 else
1176 lv_is_costing_summary := 'N';
1177 end if;
1178 else
1179 lv_is_costing_summary := 'N';
1180 lv_is_costing_detail := 'N';
1181 end if;
1182 l_message_subject1 := 'List of Processes and Reports which will be submitted.';
1183 if lv_isResponseRequired = 'Y' then
1184 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;
1185
1186 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.';
1187 else
1188 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;
1189
1190 l_message_html_text1 := 'Review the List of Processes and Reports which will be submitted based on the input parameters.';
1191 end if;
1192
1193 /*
1194 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>';
1195
1196 */
1197
1198 /*
1199 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>';
1200
1201
1202 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.';
1203
1204 */
1205 /*
1206 if ((lv_is_batch = 'Y') and (lv_is_retro_ntfy = 'Y'))then
1207 l_message_html_text2 := 'WF_NOTIFICATION(ATTRS,MSG_ATTR19,MSG_ATTR20,MSG_ATTR21,MSG_ATTR1,MSG_ATTR2,MSG_ATTR3,MSG_ATTR4,MSG_ATTR5,'
1208 ||'MSG_ATTR6,MSG_ATTR7,MSG_ATTR8,MSG_ATTR9,MSG_ATTR10,MSG_ATTR11,MSG_ATTR12,MSG_ATTR13,MSG_ATTR14,'
1209 ||'MSG_ATTR15,MSG_ATTR16,MSG_ATTR17,MSG_ATTR18)';
1210 elsif ((lv_is_batch = 'Y') and (lv_is_retro_ntfy = 'N' or lv_is_retro_ntfy is null))then
1211 l_message_html_text2 := 'WF_NOTIFICATION(ATTRS,MSG_ATTR19,MSG_ATTR1,MSG_ATTR2,MSG_ATTR3,MSG_ATTR4,MSG_ATTR5,MSG_ATTR6,MSG_ATTR7,'
1212 ||'MSG_ATTR8,MSG_ATTR9,MSG_ATTR10,MSG_ATTR11,MSG_ATTR12,MSG_ATTR13,MSG_ATTR14,'
1213 ||'MSG_ATTR15,MSG_ATTR16,MSG_ATTR17,MSG_ATTR18)';
1214 elsif ((lv_is_batch = 'N' or lv_is_batch is null) and (lv_is_retro_ntfy = 'Y'))then
1215 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,'
1216 ||'MSG_ATTR9,MSG_ATTR10,MSG_ATTR11,MSG_ATTR12,MSG_ATTR13,MSG_ATTR14,MSG_ATTR15,'
1217 ||'MSG_ATTR16,MSG_ATTR17,MSG_ATTR18)';
1218 else
1219 l_message_html_text2 := 'WF_NOTIFICATION(ATTRS,MSG_ATTR1,MSG_ATTR2,MSG_ATTR3,MSG_ATTR4,MSG_ATTR5,MSG_ATTR6,MSG_ATTR7,'
1220 ||'MSG_ATTR8,MSG_ATTR9,MSG_ATTR10,MSG_ATTR11,MSG_ATTR12,MSG_ATTR13,MSG_ATTR14,'
1221 ||'MSG_ATTR15,MSG_ATTR16,MSG_ATTR17,MSG_ATTR18)';
1222 end if;
1223 */
1224
1225 /*
1226 -- Testing with multiple functions in the same text of the body for the notification
1227
1228 if (lv_is_batch = 'Y') then
1229 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR19)';
1230 elsif (lv_is_retro_ntfy = 'Y') then
1231 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR20,MSG_ATTR21)';
1232 end if;
1233 -- For Other Processes Dependent on Payroll Run.
1234
1235 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR1,MSG_ATTR2,MSG_ATTR3,MSG_ATTR4,MSG_ATTR5)';
1236
1237 if (lv_is_payroll_exception_report = 'Y') then
1238 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR6)';
1239 end if;
1240
1241 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR7)';
1242
1243 if (lv_is_nacha = 'Y') then
1244 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR8)';
1245 end if;
1246
1247 -- External Process Archive
1248 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR9)';
1249
1250 if (lv_is_check = 'Y') then
1251 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR10)';
1252 elsif (lv_is_third_party_check = 'Y') then
1253 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR11)';
1254 end if;
1255
1256 -- Deposit Advice
1257
1261
1258 if (lv_is_deposit_advice = 'Y') then
1259 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR12)';
1260 end if;
1262 -- Register Reports and Costing Process and Reports.
1263
1264 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR13,MSG_ATTR14,MSG_ATTR15,MSG_ATTR16,MSG_ATTR17,MSG_ATTR18)';
1265
1266 */
1267 --jjj
1268 -- 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
1269 n := 1;
1270 if (lv_is_batch = 'Y') then
1271 lv_cur_process(n) := lv_conc_prog_name(1);
1272 n := n+1;
1273 end if;
1274
1275 if (lv_is_retro_ntfy = 'Y') then
1276 lv_cur_process(n) := lv_conc_prog_name(2);
1277 n := n+1;
1278 lv_cur_process(n) := lv_conc_prog_name(3);
1279 n := n+1;
1280 end if;
1281 -- For Other Processes Dependent on Payroll Run.
1282
1283 lv_cur_process(n) := lv_conc_prog_name(4);
1284 n := n+1;
1285 lv_cur_process(n) := lv_conc_prog_name(5);
1286 n := n+1;
1287 lv_cur_process(n) := lv_conc_prog_name(6);
1288 n := n+1;
1289 lv_cur_process(n) := lv_conc_prog_name(7);
1290 n := n+1;
1291
1292 if (lv_is_payroll_exception_report = 'Y') then
1293 lv_cur_process(n) := lv_conc_prog_name(8);
1294 n := n+1;
1295 l_pay_excep_rpt_message_text1 := 'Payroll Exception Report(Request Id: &REQ_ID_REP4) :'||'
1296 Start Date: &START_DATE
1297 End Date: &END_DATE
1298 Payroll Name: &PAYROLL
1299 Consolidation Set: &CONSOLIDATION_SET
1300 Exception Group Name: &GROUP_NAME
1301 Exception Report Name: &REPORT_NAME
1302 Override Variance Type: &OVER_VAR_TYPE
1303 Override Variance Value: &OVER_VAR_VALUE ' ;
1304 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) ';
1305
1306 else
1307 l_pay_excep_rpt_message_text1 := null;
1308 l_pay_excep_rpt_message_html1 := null;
1309
1310 end if;
1311
1312 --Federal and State Tax Remittance Report
1313
1314 lv_cur_process(n) := lv_conc_prog_name(9);
1315 n := n+1;
1316
1317 --Pre Payments
1318
1319 lv_cur_process(n) := lv_conc_prog_name(10);
1320 n := n+1;
1321
1322 if (lv_is_nacha = 'Y') then
1323 lv_cur_process(n) := lv_conc_prog_name(11);
1324 n := n+1;
1325 end if;
1326
1327 -- External Process Archive
1328 lv_cur_process(n) := lv_conc_prog_name(12);
1329 n := n+1;
1330
1331 if (lv_is_check = 'Y') then
1332 lv_cur_process(n) := lv_conc_prog_name(13);
1333 n := n+1;
1334 end if;
1335
1336 if (lv_is_third_party_check = 'Y') then
1337 lv_cur_process(n) := lv_conc_prog_name(14);
1338 n := n+1;
1339 end if;
1340
1341 -- Deposit Advice
1342
1343 if (lv_is_deposit_advice = 'Y') then
1344 lv_cur_process(n) := lv_conc_prog_name(15);
1345 n := n+1;
1346 end if;
1347
1348 -- Register Reports and Costing Process and Reports.
1349
1350 if (lv_is_ele_reg = 'Y') then
1351 lv_cur_process(n) := lv_conc_prog_name(16);
1352 n := n+1;
1353 l_ele_reg_rpt_message_text1 := 'Element Register(Request ID: &REQ_ID_REP6) ';
1354 l_ele_reg_rpt_message_html1 := 'Element Register Report (Request Id: &REQ_ID_REP6) ';
1355
1356 else
1357 l_ele_reg_rpt_message_text1 := null;
1358 l_ele_reg_rpt_message_html1 := null;
1359
1360 end if;
1361
1362 -- Payment Register
1363
1364 lv_cur_process(n) := lv_conc_prog_name(17);
1365 n := n+1;
1366
1367 -- 3rd Payment Register
1368
1369 lv_cur_process(n) := lv_conc_prog_name(18);
1370 n := n+1;
1371
1372 -- Costing Process
1373
1374 lv_cur_process(n) := lv_conc_prog_name(19);
1375 n := n+1;
1376
1377 -- Costing Summary and Detail Reports
1378
1379 lv_cur_process(n) := lv_conc_prog_name(20);
1380 n := n+1;
1381 lv_cur_process(n) := lv_conc_prog_name(21);
1382 n := n+1;
1383
1384
1385
1386 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR1,MSG_ATTR2,MSG_ATTR3,MSG_ATTR4,MSG_ATTR5)';
1387 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR6,MSG_ATTR7,MSG_ATTR8,MSG_ATTR9,MSG_ATTR10)';
1388 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR11,MSG_ATTR12,MSG_ATTR13,MSG_ATTR14,MSG_ATTR15)';
1389 l_message_html_text2 := l_message_html_text2 ||'WF_NOTIFICATION(ATTRS,MSG_ATTR16,MSG_ATTR17,MSG_ATTR18,MSG_ATTR19,MSG_ATTR20,MSG_ATTR21)';
1390
1391 if n < 22 then
1392 for p in n..21 loop
1393 lv_cur_process(p) := null;
1394 end loop;
1395 end if;
1396
1397 hr_utility.trace('p_payroll_id = ' || p_payroll_id);
1398 hr_utility.trace('p_consolidation_set_id = ' || p_consolidation_set_id);
1399 hr_utility.trace('p_date_earned = ' || p_date_earned);
1400 hr_utility.trace('p_date_paid = ' || p_date_paid);
1401 hr_utility.trace('p_payroll_assignment_set_name = ' || p_payroll_assignment_set_name);
1402 hr_utility.trace('p_payroll_run_type = ' || p_payroll_run_type);
1403 hr_utility.trace('ln_nacha_payment_type_id = ' || ln_nacha_payment_type_id);
1404 hr_utility.trace('check_style = ' || p_check_style);
1405 begin
1406
1407 i := 0;
1408
1409 varname(i) := 'CUR_ITEM_TYPE';
1410 varval(i) := ItemType;
1411 i := i+1;
1412 varname(i) := 'CURR_ITEM_KEY';
1413 varval(i) := ItemKey;
1414 i := i+1;
1415 varname(i) := 'IS_BATCH';
1416 varval(i) := lv_is_batch;
1417 i := i+1;
1418 varname(i) := 'IS_RETRO';
1419 varval(i) := lv_is_retro_ntfy;
1420 i := i+1;
1421 varname(i) := 'PAYROLL_NAME';
1422 varval(i) := lv_payroll_name;
1423 i := i+1;
1424 varname(i) := 'PAYROLL_ID';
1425 varval(i) := p_payroll_id;
1426 i := i+1;
1427 varname(i) := 'CONSOLIDATION_SET_NAME';
1428 varval(i) := lv_consolidation_set_name;
1429 i := i+1;
1430 varname(i) := 'CONSOLIDATION_SET_ID';
1431 varval(i) := p_consolidation_set_id;
1432 i := i+1;
1433 varname(i) := 'DATE_EARNED';
1434 varval(i) := p_date_earned;
1435 i := i+1;
1436 varname(i) := 'DATE_PAID';
1437 varval(i) := p_date_paid;
1438 i := i+1;
1439 varname(i) := 'EVENT_GROUP';
1440 varval(i) := p_event_group;
1441 i := i+1;
1442 varname(i) := 'RETRO_ASSIGNMENT_SET_NAME';
1443 varval(i) := p_retro_assignment_set_name;
1444 i := i+1;
1445 varname(i) := 'RETRO_ASSIGNMENT_SET_NAME_DUMY';
1446 varval(i) := p_retro_assignment_set_dummy;
1447 i := i+1;
1448 varname(i) := 'RETRO_ELEMENT_SET';
1449 varval(i) := p_retro_element_set;
1450 i := i+1;
1451 varname(i) := 'RETRO_START_DATE';
1452 varval(i) := p_retro_start_date;
1453 i := i+1;
1454 varname(i) := 'EFFECTIVE_DATE';
1455 varval(i) := p_effective_date;
1456 --varval(i) := p_date_earned;
1457 --varval(i) := p_date_paid;
1458 i := i+1;
1459 varname(i) := 'PAYROLL_ELEMENT_SET_NAME';
1460 varval(i) := p_payroll_element_set_name;
1461 i := i+1;
1462 varname(i) := 'PAYROLL_ASSIGNMENT_SET_NAME';
1463 varval(i) := p_payroll_assignment_set_name;
1464 i := i+1;
1465 varname(i) := 'PAYROLL_RUN_TYPE';
1466 varval(i) := p_payroll_run_type;
1467 i := i+1;
1468 varname(i) := 'GOVERNMENT_REPORTING_ENTITY';
1469 varval(i) := p_gre;
1470 i := i+1;
1471 varname(i) := 'GRE_NAME';
1472 varval(i) := lv_gre_name;
1473 i := i+1;
1474 varname(i) := 'ORGANIZATION';
1475 varval(i) := p_organization;
1476 i := i+1;
1477 varname(i) := 'LOCATION';
1478 varval(i) := p_location;
1479 i := i+1;
1480 varname(i) := 'IS_PAYROLL_EXCEPTION_REPORT';
1481 varval(i) := lv_is_payroll_exception_report;
1482 i := i+1;
1483 varname(i) := 'SELECT_REPORT_OR_GROUP';
1484 varval(i) := p_select_report_or_group;
1485 i := i+1;
1486 varname(i) := 'EXCEPTION_GROUP_NAME';
1487 varval(i) := p_exception_group_name;
1488 i := i+1;
1489 varname(i) := 'EXCPTN_RPT_GRP_NAME';
1490 varval(i) := lv_exc_grp_name;
1491 i := i+1;
1492 varname(i) := 'EXCPTN_RPT_NAME';
1493 varval(i) := lv_exc_rep_name;
1494 i := i+1;
1495 varname(i) := 'EXCEPTION_GROUP_NAME_DMY';
1496 varval(i) := p_exception_group_name_dummy;
1497 i := i+1;
1498 varname(i) := 'EXCEPTION_REPORT_NAME';
1499 varval(i) := p_exception_report_name;
1500 i := i+1;
1501 varname(i) := 'EXCEPTION_REPORT_NAME_DMY';
1502 varval(i) := p_exception_report_name_dummy;
1503 i := i+1;
1504 varname(i) := 'OVER_RIDE_VARIANCE_TYPE';
1505 varval(i) := p_over_ride_variance_type;
1506 i := i+1;
1507 varname(i) := 'ORV_TYPE';
1508 varval(i) := lv_orv_type;
1509 i := i+1;
1510 varname(i) := 'OVER_RIDE_VARIANCE_TYPE_DMY';
1511 varval(i) := p_over_ride_varianc_type_dumy;
1512 i := i+1;
1513 varname(i) := 'OVER_RIDE_VARIANCE_VALUE';
1514 varval(i) := p_over_ride_variance_value;
1515 i := i+1;
1516 varname(i) := 'ORV_VALUE';
1517 varval(i) := lv_orv_value;
1518 i := i+1;
1519 varname(i) := 'OVER_RIDE_VARIANCE_VALUE_DMY';
1520 varval(i) := p_over_ride_varinc_value_dumy;
1521 i := i+1;
1522 varname(i) := 'TRANSFER_DATE';
1523 -- varval(i) := 'TRANSFER_DATE='||p_effective_date;
1524 --varval(i) := 'TRANSFER_DATE='||p_date_earned;
1525 varval(i) := 'TRANSFER_DATE='||p_date_paid;
1526 i := i+1;
1527 varname(i) := 'PPA_FINDER_PQP';
1528 varval(i) := p_ppa_finder_pqp;
1529 i := i+1;
1530 varname(i) := 'TRANSFER_PPA_FINDER_PQP';
1531 varval(i) := 'TRANSFER_PPA_FINDER='||p_ppa_finder_pqp;
1532 i := i+1;
1533 varname(i) := 'PREPAY_PAYMENT_METHOD_OVERRIDE';
1534 varval(i) := p_payment_method_override;
1535 i := i+1;
1536 varname(i) := 'IS_NACHA';
1537 varval(i) := lv_is_nacha;
1538 i := i+1;
1539 varname(i) := 'NACHA_PAYMENT_METHOD';
1540 varval(i) := p_nacha_payment_method;
1541 i := i+1;
1542 varname(i) := 'NACHA_PYMT_NAME';
1543 varval(i) := lv_nacha_pymt_name;
1544 i := i+1;
1545 varname(i) := 'DEPOSIT_DATE_OVERRIDE';
1546 varval(i) := p_deposit_date_override;
1547 i := i+1;
1548 varname(i) := 'FILE_ID_MODIFIER';
1549 varval(i) := p_file_id_modifier;
1550 i := i+1;
1551 varname(i) := 'FILE_ID_MODIFIER_CHECK';
1552 varval(i) := p_file_id_modifier_check;
1553 i := i+1;
1554 varname(i) := 'THRID_PARTY_CHECK';
1555 varval(i) := p_thrid_party_check;
1556 i := i+1;
1557 varname(i) := 'IS_CHECK';
1558 varval(i) := lv_is_check;
1559 i := i+1;
1560 varname(i) := 'CHECK_WRITER_PAYMENT_METHOD';
1561 varval(i) := p_check_writer_payment_method;
1562 i := i+1;
1563 varname(i) := 'CHECK_PYMT_NAME';
1564 varval(i) := lv_check_pymt_name;
1565 i := i+1;
1566 varname(i) := 'CHECK_WRITER_SORT_SEQUENCE';
1567 varval(i) := p_check_writer_sort_sequence;
1568 i := i+1;
1569 varname(i) := 'CHECK_SORT_SEQ_MEANING';
1570 varval(i) := lv_check_sort_seq_meaning;
1571 i := i+1;
1572 varname(i) := 'CHECK_STYLE';
1573 varval(i) := p_check_style;
1574 i := i+1;
1575 varname(i) := 'IS_THIRD_PARTY_CHECK';
1576 varval(i) := lv_is_third_party_check;
1577 i := i+1;
1578 varname(i) := 'PAYMENT_METHOD_3RD_PARTY';
1579 varval(i) := p_payment_method_3rd_party;
1580 i := i+1;
1581 varname(i) := 'SORT_SEQUENCE_3RD_PARTY';
1582 varval(i) := p_sort_sequence_3rd_party;
1583 i := i+1;
1584 varname(i) := 'IS_DEPOSIT_ADVICE';
1585 varval(i) := lv_is_deposit_advice;
1586 i := i+1;
1587 varname(i) := 'DEPOSIT_ADVICE_REPORT_CATEGORY';
1588 varval(i) := p_da_report_category;
1589 i := i+1;
1590 varname(i) := 'DEPOSIT_ADVICE_START_DATE_DMY';
1591 varval(i) := 'START_DATE='||p_date_paid;
1592 i := i+1;
1593 varname(i) := 'DEPOSIT_ADVICE_END_DATE_DMY';
1594 varval(i) := 'END_DATE='||p_date_paid;
1595 i := i+1;
1596 varname(i) := 'DEPOSIT_ADVICE_PAYROLL';
1597 varval(i) := 'PAYROLL_ID='||p_payroll_id;
1598 i := i+1;
1599 varname(i) := 'DEPOSIT_ADVICE_CONC_SET';
1600 varval(i) := 'CONSOLIDATION_SET_ID='||p_consolidation_set_id;
1601 i := i+1;
1602 varname(i) := 'DEPOSIT_ADVICE_SORT_SEQUENCE';
1603 varval(i) := p_da_sort_sequence;
1604 i := i+1;
1605 varname(i) := 'DEPOSIT_ADVICE_ASSIGNMENT_SET';
1606 varval(i) := p_da_assignment_set;
1607 i := i+1;
1608 varname(i) := 'DEPOSIT_ADVICE_ASGSET_DMY';
1609 varval(i) := p_assignment_set_dummy;
1610 i := i+1;
1611 varname(i) := 'DEPOSIT_ADVICE_REPORT_NAME';
1612 varval(i) := lv_da_report;
1613 i := i+1;
1614 varname(i) := 'SELECTION_CRITERION';
1615 varval(i) := p_selection_criterion;
1616 i := i+1;
1617 varname(i) := 'ELE_REG_ELEMENT_SET';
1618 varval(i) := p_ele_reg_element_set;
1619 i := i+1;
1620 varname(i) := 'ELEMENT_CLASSIFICATION';
1621 varval(i) := p_element_classification;
1622 i := i+1;
1623 varname(i) := 'ELEMENT';
1624 varval(i) := p_element;
1625 i := i+1;
1626 varname(i) := 'ELEMENT_NAME';
1627 varval(i) := lv_element_name;
1628 --- i := i+1;
1629 --- varname(i) := 'ELEMENT_REGISTER_EMPLOYEE';
1630 --- varval(i) := p_ele_reg_employee;
1631 i := i+1;
1632 varname(i) := 'EMP_ID';
1633 varval(i) := p_ele_reg_employee;
1634 i := i+1;
1635 varname(i) := 'COSTING_PROCESS';
1636 varval(i) := p_costing_process;
1637 i := i+1;
1638 varname(i) := 'START_DATE';
1639 varval(i) := p_date_earned;
1640 i := i+1;
1641 varname(i) := 'END_DATE';
1642 varval(i) := p_date_paid;
1643 i := i+1;
1644 varname(i) := 'PPA_FINDER';
1645 varval(i) := p_ppa_finder;
1646 i := i+1;
1647 varname(i) := 'USER';
1648 varval(i) := lv_contact_user_name;
1649 --- varval(i) := RequestorUsername;
1650 i := i+1;
1651 varname(i) := 'CONTACT_USERNAME';
1652 varval(i) := lv_contact_user_name;
1653 i := i+1;
1654 varname(i) := 'MESSAGE_SUBJECT1';
1655 varval(i) := l_message_subject1;
1656 i := i+1;
1657 varname(i) := 'MESSAGE_TEXT1';
1658 varval(i) := l_message_text1;
1659 i := i+1;
1660 varname(i) := 'MESSAGE_HTML_TEXT1';
1661 varval(i) := l_message_html_text1;
1662 i := i+1;
1663 varname(i) := 'MESSAGE_HTML_TEXT2';
1664 varval(i) := l_message_html_text2;
1665 i := i+1;
1666 varname(i) := 'ROLE_NAME';
1667 varval(i) := l_role_name;
1668 i := i+1;
1669 varname(i) := 'REPORTING_CURRENCY_CODE';
1670 varval(i) := lv_currency_code;
1671 i := i+1;
1672 varname(i) := 'PPA_FINDER';
1673 varval(i) := p_ppa_finder;
1674 i := i+1;
1675 varname(i) := 'TRANSFER_PPA_FINDER';
1676 varval(i) := 'TRANSFER_PPA_FINDER='||p_ppa_finder;
1677 i := i+1;
1678 varname(i) := 'TRANSFER_GRE';
1679 varval(i) := 'TRANSFER_GRE='||p_gre;
1680 i := i+1;
1681 varname(i) := 'TRANSFER_PAYROLL';
1682 varval(i) := 'TRANSFER_PAYROLL='||p_payroll_id;
1683 i := i+1;
1684 varname(i) := 'TRANSFER_CONC_SET';
1685 varval(i) := 'TRANSFER_CONC_SET='||p_consolidation_set_id;
1686 i := i+1;
1687 varname(i) := 'XFR_TRANSFER_PAYROLL_ID';
1688 varval(i) := 'TRANSFER_PAYROLL_ID='||p_payroll_id;
1689 i := i+1;
1690 varname(i) := 'XFR_TRANS_CONS_SET';
1691 varval(i) := 'TRANSFER_CONSOLIDATION_SET_ID='||p_consolidation_set_id;
1692 i := i+1;
1693 varname(i) := 'FILE_ID_MODIFIER_HIDDEN';
1694 varval(i) := 'FILE_ID_MODIFIER='||p_file_id_modifier;
1695 i := i+1;
1696 varname(i) := 'PERIOD';
1697 varval(i) := p_period;
1698 i := i+1;
1699 varname(i) := 'PERIOD_NAME';
1700 varval(i) := lv_period_name;
1701 i := i+1;
1702 varname(i) := 'P_PAYROLL_ACTION_ID';
1703 varval(i) := p_p_payroll_action_id;
1704 i := i+1;
1705 varname(i) := 'NACHA_PAYMENT_TYPE_ID';
1709 varval(i) := ln_check_payment_type_id;
1706 varval(i) := ln_nacha_payment_type_id;
1707 i := i+1;
1708 varname(i) := 'CHECK_PAYMENT_TYPE_ID';
1710 i := i+1;
1711 varname(i) := 'DATE_TIME';
1712 varval(i) := lv_date_time;
1713 i := i+1;
1714 varname(i) := 'END_DATE_LEG_PARAM';
1715 varval(i) := 'END_DATE='||p_date_earned;
1716 i := i+1;
1717 varname(i) := 'PAYROLL_LEG_PARAM';
1718 varval(i) := 'PAYROLL_ID='||p_payroll_id;
1719 i := i+1;
1720 varname(i) := 'CONC_SET_LEG_PARAM';
1721 varval(i) := 'CONSOLIDATION_SET_ID='||p_consolidation_set_id;
1722 i := i+1;
1723 varname(i) := 'PROCESS_01';
1724 varval(i) := lv_cur_process(1);
1725 i := i+1;
1726 varname(i) := 'PROCESS_02';
1727 varval(i) := lv_cur_process(2);
1728 i := i+1;
1729 varname(i) := 'PROCESS_03';
1730 varval(i) := lv_cur_process(3);
1731 i := i+1;
1732 varname(i) := 'PROCESS_04';
1733 varval(i) := lv_cur_process(4);
1734 i := i+1;
1735 varname(i) := 'PROCESS_05';
1736 varval(i) := lv_cur_process(5);
1737 i := i+1;
1738 varname(i) := 'PROCESS_06';
1739 varval(i) := lv_cur_process(6);
1740 i := i+1;
1741 varname(i) := 'PROCESS_07';
1742 varval(i) := lv_cur_process(7);
1743 i := i+1;
1744 varname(i) := 'PROCESS_08';
1745 varval(i) := lv_cur_process(8);
1746 i := i+1;
1747 varname(i) := 'PROCESS_09';
1748 varval(i) := lv_cur_process(9);
1749 i := i+1;
1750 varname(i) := 'PROCESS_10';
1751 varval(i) := lv_cur_process(10);
1752 i := i+1;
1753 varname(i) := 'PROCESS_11';
1754 varval(i) := lv_cur_process(11);
1755 i := i+1;
1756 varname(i) := 'PROCESS_12';
1757 varval(i) := lv_cur_process(12);
1758 i := i+1;
1759 varname(i) := 'PROCESS_13';
1760 varval(i) := lv_cur_process(13);
1761 i := i+1;
1762 varname(i) := 'PROCESS_14';
1763 varval(i) := lv_cur_process(14);
1764 i := i+1;
1765 varname(i) := 'PROCESS_15';
1766 varval(i) := lv_cur_process(15);
1767 i := i+1;
1768 varname(i) := 'PROCESS_16';
1769 varval(i) := lv_cur_process(16);
1770 i := i+1;
1771 varname(i) := 'PROCESS_17';
1772 varval(i) := lv_cur_process(17);
1773 i := i+1;
1774 varname(i) := 'PROCESS_18';
1775 varval(i) := lv_cur_process(18);
1776 i := i+1;
1777 varname(i) := 'PROCESS_19';
1778 varval(i) := lv_cur_process(19);
1779 i := i+1;
1780 varname(i) := 'PROCESS_20';
1781 varval(i) := lv_cur_process(20);
1782 i := i+1;
1783 varname(i) := 'PROCESS_21';
1784 varval(i) := lv_cur_process(21);
1785 i := i+1;
1786 varname(i) := 'BEE_BATCH_NAME';
1790 varval(i) := l_pay_excep_rpt_message_text1;
1787 varval(i) := lv_batch_name;
1788 i := i+1;
1789 varname(i) := 'PAY_EXCEPTION_RPT_MSG_TEXT1';
1791 i := i+1;
1792 varname(i) := 'PAY_EXCEPTION_RPT_MSG_HTML1';
1793 varval(i) := l_pay_excep_rpt_message_html1;
1794 i := i+1;
1795 varname(i) := 'RETRO_START_DATE_DUMMY';
1796 varval(i) := 'START_DATE='||p_retro_start_date;
1797 i := i+1;
1798 varname(i) := 'ELEMENT_REGISTER_RPT_MSG_TEXT1';
1799 varval(i) := l_ele_reg_rpt_message_text1;
1800 i := i+1;
1801 varname(i) := 'ELEMENT_REGISTER_RPT_MSG_HTML1';
1802 varval(i) := l_ele_reg_rpt_message_html1;
1803 i := i+1;
1804 varname(i) := 'IS_ELEMENT_REGISTER_REPORT';
1805 varval(i) := lv_is_ele_reg;
1806
1807 i := i+1;
1808 varname(i) := 'IS_RESPONSE_REQUIRED';
1809 varval(i) := lv_isResponseRequired;
1810
1811 i := i+1;
1812 varname(i) := 'PAYROLL_WF_NOTIFY_ACTION';
1813 varval(i) := lv_Payroll_WF_Notify_Action;
1814
1815 i := i+1;
1816 varname(i) := 'IS_PAYMENT_WAIT';
1817 varval(i) := lv_isPaymentWait;
1818
1819 i := i+1;
1820 varname(i) := 'LEGISLATION_CODE';
1821 varval(i) := p_legislation_code;
1822
1823 i := i+1;
1824 varname(i) := 'IS_EXCEPTION_GROUP';
1825 varval(i) := p_is_exception_group;
1826
1827 i := i+1;
1828 varname(i) := 'IS_EXCEPTION_REPORT';
1829 varval(i) := p_is_exception_report;
1830
1831 i := i+1;
1832 varname(i) := 'PREPAY_PYMENT_MTHD_OVRIDE_NAME';
1833 varval(i) := lv_pre_ovr_pymt_name;
1834 i := i+1;
1835 varname(i) := 'RETRO_EVENT_GROUP_NAME';
1836 varval(i) := lv_event_group_name;
1837 i := i+1;
1838 varname(i) := 'OVERRIDING_CHEQUE_DATE';
1839 varval(i) := p_overriding_cheque_date;
1840
1841 -- Costing Summary
1842
1843 varname(i) := 'IS_COSTING_SUMMARY';
1844 varval(i) := lv_is_costing_summary;
1845 i := i+1;
1846 varname(i) := 'SUMMARY_COSTING_ACCRUALS';
1847 varval(i) := p_cost_summary_accruals;
1848 i := i+1;
1849 varname(i) := 'SUMMARY_COSTING_ACCRUALS_NAME';
1850 varval(i) := lv_cost_summary_accruals;
1851 i := i+1;
1852 varname(i) := 'SUMMARY_COSTING_FILE_FORMAT';
1853 varval(i) := p_cost_summary_file_out;
1854 i := i+1;
1855 varname(i) := 'SUMMARY_COSTING_FILE_FMT_NAME';
1856 varval(i) := lv_cost_summary_file_out;
1857 i := i+1;
1858
1859 -- Costing Detail
1860
1861 varname(i) := 'IS_COSTING_DETAIL';
1862 varval(i) := lv_is_costing_detail;
1863 i := i+1;
1864 varname(i) := 'DETAIL_COSTING_CRITERION';
1865 varval(i) := p_cost_detail_selection;
1866 i := i+1;
1867 varname(i) := 'DETAIL_COSTING_IS_ELEMENT_SET';
1868 varval(i) := p_cost_detail_is_ele_set;
1869 i := i+1;
1870 varname(i) := 'DETAIL_COSTING_ELEMENT_SET';
1874 varval(i) := lv_cost_detail_ele_set;
1871 varval(i) := p_cost_detail_ele_set;
1872 i := i+1;
1873 varname(i) := 'DETAIL_COSTING_ELE_SET_NAME';
1875 i := i+1;
1876 varname(i) := 'DETAIL_COSTING_IS_ELEMENT_C';
1877 varval(i) := p_cost_detail_is_class;
1878 i := i+1;
1879 varname(i) := 'DETAIL_COSTING_ELEMENT_C';
1880 varval(i) := p_cost_detail_class;
1881 i := i+1;
1882 varname(i) := 'DETAIL_COSTING_ELEMENT_C_NAME';
1883 varval(i) := lv_cost_detail_ele_class;
1884 i := i+1;
1885 varname(i) := 'DETAIL_COSTING_IS_ELEMENT';
1886 varval(i) := p_cost_detail_is_element;
1887 i := i+1;
1888 varname(i) := 'DETAIL_COSTING_ELEMENT';
1889 varval(i) := p_cost_detail_element;
1890 i := i+1;
1891 varname(i) := 'DETAIL_COSTING_ELEMENT_NAME';
1892 varval(i) := lv_cost_detail_ele_name;
1893 i := i+1;
1894 varname(i) := 'DETAIL_COSTING_ASG_SET';
1895 varval(i) := p_cost_detail_asg_set;
1896 i := i+1;
1897 varname(i) := 'DETAIL_COSTING_ASG_SET_NAME';
1898 varval(i) := lv_cost_detail_asg_set;
1899 i := i+1;
1900 varname(i) := 'DETAIL_COSTING_ACCRUALS';
1901 varval(i) := p_cost_detail_accruals;
1902 i := i+1;
1903 varname(i) := 'DETAIL_COSTING_ACCRUALS_NAME';
1904 varval(i) := lv_cost_detail_accruals;
1905 i := i+1;
1906
1907
1908 i := i+1;
1909 num_varname(i) := 'P_BUSINESS_GROUP_ID';
1910 num_varvalue(i) := p_business_group_id;
1911 i := i+1;
1912 num_varname(i) := 'BATCH_ID';
1913 num_varvalue(i) := p_batch_id;
1914 i := i+1;
1915 num_varname(i) := 'START_CHECK_NUMBER';
1916 num_varvalue(i) := to_number(p_start_check_number);
1917 i := i+1;
1918 num_varname(i) := 'END_CHECK_NUMBER';
1919 num_varvalue(i) := to_number(p_end_check_number);
1920 i := i+1;
1921 num_varname(i) := 'START_CHECK_NUMBER_3RD_PARTY';
1922 num_varvalue(i) := to_number(p_start_check_num_3rd_party);
1923 i := i+1;
1924 num_varname(i) := 'END_CHECK_NUMBER_3RD_PARTY';
1925 num_varvalue(i) := to_number(p_end_check_num_3rd_party);
1926 i := i+1;
1927 num_varname(i) := 'USER_ID';
1928 num_varvalue(i) := l_user_id;
1929 i := i+1;
1930 num_varname(i) := 'APPLICATION_ID';
1931 num_varvalue(i) := l_resp_appl_id;
1932 i := i+1;
1933 num_varname(i) := 'RESPONSIBILITY_ID';
1934 num_varvalue(i) := l_resp_id;
1935 i := i+1;
1936 num_varname(i) := 'ORG_ID';
1937 num_varvalue(i) := l_org_id;
1938 i := i+1;
1939 num_varname(i) := 'PER_SECURITY_PROFILE_ID';
1940 num_varvalue(i) := l_profile_per_sec_id;
1941 i := i+1;
1942 num_varname(i) := 'SECURITY_GROUP_ID';
1943 num_varvalue(i) := l_profile_sec_grp_id;
1944
1945 for j in varname.first..varname.last loop
1946 hr_utility.trace( 'J = '|| j );
1947 hr_utility.trace( 'varname = '||varname(j));
1948 hr_utility.trace( 'varval = '||varval(j));
1949 end loop;
1950
1951 Wf_Engine.SetItemAttrTextArray(ItemType, ItemKey, varname, varval);
1952 hr_utility.trace( 'Total Var Kount = '||to_char(varname.COUNT));
1953
1954 for p in num_varname.first..num_varname.last loop
1955 hr_utility.trace( 'p = '|| p );
1956 hr_utility.trace( 'num_varname = '||num_varname(p));
1957 hr_utility.trace( 'num_varval = '||num_varvalue(p));
1958 end loop;
1959
1963 exception
1960 Wf_Engine.SetItemAttrNumberArray(ItemType, ItemKey, num_varname, num_varvalue);
1961
1962 hr_utility.trace( 'Total Num Kount = '||to_char(num_varname.COUNT));
1964 when OTHERS then
1965 hr_utility.trace('In exception: OTHERS of TextArray');
1966 raise;
1967 end;
1968
1969 /*
1970 wf_engine.SetItemAttrText ( itemtype => ItemType,
1971 itemkey => ItemKey,
1972 aname => 'USER_DISPLAY_NAME',
1973 avalue => wf_directory.GetRoleDisplayName(RequestorUsername));
1974
1975 wf_engine.SetItemOwner ( itemtype => ItemType,
1976 itemkey => ItemKey,
1977 owner => ProcessOwner);
1978
1979
1980 wf_engine.SetItemAttrText ( itemtype => ItemType,
1981 itemkey => ItemKey,
1982 aname => 'MESSAGE_SUBJECT1',
1983 avalue => l_message_subject1);
1984
1985
1986 wf_engine.SetItemAttrText ( itemtype => ItemType,
1987 itemkey => ItemKey,
1988 aname => 'MESSAGE_TEXT1',
1989 avalue => l_message_text1);
1990
1991 hr_utility.trace('B4 SetItemAttrText : l_role_name = ' || l_role_name);
1992 wf_engine.SetItemAttrText ( itemtype => ItemType,
1993 itemkey => ItemKey,
1994 aname => 'ROLE_NAME',
1995 avalue => l_role_name);
1996
1997 wf_engine.SetItemAttrText ( itemtype => ItemType,
1998 itemkey => ItemKey,
1999 aname => 'APPLNAME',
2000 avalue => 'PAY');
2001
2002 hr_utility.trace('APPLNAME ');
2003 */
2004
2005 hr_utility.trace('b4 starting process');
2006
2007 wf_engine.StartProcess ( ItemType => ItemType,
2008 ItemKey => ItemKey );
2009 hr_utility.trace('a4 starting process');
2010
2011
2012
2013 exception
2014 when others then
2015 WF_CORE.Context('PAY_WF_PKG', 'StartProcess',ItemType, RequestorUsername, ProcessOwner, Workflowprocess);
2016
2017 error;
2018 RAISE;
2019
2020 end StartProcess;
2021
2022 function get_notifier( ln_payroll_id in number,
2023 ln_gre_id in number,
2024 l_effective_date in varchar2
2025 ) return varchar2 is
2026
2027 /******************************************************************
2028 **
2029 ** Description:
2030 **
2031 ** Access Status:
2032 **
2033 ******************************************************************/
2034
2035 l_proc varchar2(80);
2036 lv_contact_source VARCHAR2(50);
2037 ln_current_assignment_id per_assignments_f.assignment_id%TYPE;
2038 lv_contact_user_name VARCHAR2(150);
2039 ln_contact_person_id per_people_f.person_id%TYPE;
2040 ln_employee_person_id per_people_f.person_id%TYPE;
2041
2042 -- get the payroll contact
2043
2044 CURSOR c_payroll_contact IS
2045 select prl.prl_information1
2046 from pay_payrolls_f prl
2047 where prl.payroll_id = ln_payroll_id
2048 and prl.prl_information_category = 'US'
2049 and fnd_date.canonical_to_date(l_effective_date) between
2050 prl.effective_start_date and prl.effective_end_date;
2051
2052
2053 -- get the GRE contact
2054
2055 CURSOR c_gre_contact IS
2056 select org.org_information1
2057 from hr_organization_information org
2058 where org.org_information_context || '' = 'Contact Information'
2059 and org.organization_id = ln_gre_id;
2060
2061 begin
2062
2063 l_proc := gv_package||'.get_notifier';
2064 lv_contact_source := fnd_profile.value('HR_PAYROLL_CONTACT_SOURCE');
2065
2066 hr_utility.trace('Profile Value is : '|| lv_contact_source);
2067 hr_utility.trace('Payroll Id is : '|| ln_payroll_id);
2068 hr_utility.trace('GRE Id is : '|| ln_gre_id);
2069 hr_utility.trace('Effective Date is : '|| l_effective_date);
2070
2071
2072
2073 if lv_contact_source = 'PAYROLL' then
2074 open c_payroll_contact;
2075 fetch c_payroll_contact into lv_contact_user_name;
2076 hr_utility.trace('Contact User is : '|| lv_contact_user_name);
2077 if c_payroll_contact%NOTFOUND then
2078 lv_contact_user_name := 'SYSADMIN';
2079 end if;
2080
2081 close c_payroll_contact;
2082
2083
2084 elsif lv_contact_source = 'GRE' then
2085 open c_gre_contact;
2086 fetch c_gre_contact into lv_contact_user_name;
2087 if c_gre_contact%NOTFOUND then
2088 lv_contact_user_name := 'SYSADMIN';
2089 end if;
2090
2091 close c_gre_contact;
2092
2093 elsif lv_contact_source = 'CUSTOM' then
2094 lv_contact_user_name := 'SYSADMIN';
2095 else -- some other source we don't understand yet
2096 lv_contact_user_name := 'SYSADMIN';
2097 --lv_contact_user_name := null;
2098 end if;
2099
2100 return lv_contact_user_name;
2101
2102 hr_utility.set_location('Leaving: ' || l_proc, 100);
2103 end get_notifier;
2104
2105
2106 procedure error
2107 is
2108 begin
2109 /*
2110 dbms_output.put_line('Run time error in test script');
2111 dbms_output.put_line('Sqlerror: '||sqlerrm);
2112 dbms_output.put_line('Errname: '||wf_core.error_name);
2113 dbms_output.put_line('Errmsg: '||substr(wf_core.error_message, 1, 200));
2114 dbms_output.put_line('Stack1: '||substr(wf_core.error_stack, 1, 200));
2115 dbms_output.put_line('Stack2: '||substr(wf_core.error_stack, 200, 200));
2116 dbms_output.put_line('Stack3: '||substr(wf_core.error_stack, 400, 200));
2117 dbms_output.put_line('Stack4: '||substr(wf_core.error_stack, 600, 200));
2118 dbms_output.put_line('Stack5: '||substr(wf_core.error_stack, 800, 200));
2119 dbms_output.put_line('Stack6: '||substr(wf_core.error_stack, 1000, 200));
2120 dbms_output.put_line('Stack7: '||substr(wf_core.error_stack, 1200, 200));
2121 dbms_output.put_line('Stack8: '||substr(wf_core.error_stack, 1400, 200));
2122 dbms_output.put_line('Stack9: '||substr(wf_core.error_stack, 1600, 200));
2123 dbms_output.put_line('Stack10: '||substr(wf_core.error_stack, 1800, 200));
2124 */
2125
2126 hr_utility.trace('Run time error in test script');
2127 hr_utility.trace('Sqlerror: '||sqlerrm);
2128 hr_utility.trace('Errname: '||wf_core.error_name);
2129 hr_utility.trace('Errmsg: '||substr(wf_core.error_message, 1, 200));
2130 hr_utility.trace('Stack1: '||substr(wf_core.error_stack, 1, 200));
2131 hr_utility.trace('Stack2: '||substr(wf_core.error_stack, 200, 200));
2132 hr_utility.trace('Stack3: '||substr(wf_core.error_stack, 400, 200));
2133 hr_utility.trace('Stack4: '||substr(wf_core.error_stack, 600, 200));
2134 hr_utility.trace('Stack5: '||substr(wf_core.error_stack, 800, 200));
2135 hr_utility.trace('Stack6: '||substr(wf_core.error_stack, 1000, 200));
2136 hr_utility.trace('Stack7: '||substr(wf_core.error_stack, 1200, 200));
2137 hr_utility.trace('Stack8: '||substr(wf_core.error_stack, 1400, 200));
2138 hr_utility.trace('Stack9: '||substr(wf_core.error_stack, 1600, 200));
2139 hr_utility.trace('Stack10: '||substr(wf_core.error_stack, 1800, 200));
2140
2141 end error;
2142
2143
2144 end PAY_WF_PKG;