[Home] [Help]
PACKAGE BODY: APPS.PAY_US_W4_WF
Source
1 PACKAGE BODY pay_us_w4_wf
2 /* $Header: pyusw4wf.pkb 120.3.12010000.1 2008/07/27 23:59:55 appldev ship $ *
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 2000 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_w4_wf
21
22 Description : Contains workflow code the W4 Notifications Workflow
23
24 Uses :
25
26 Change List
27 -----------
28 Date Name Vers Description
29 ---- ---- ---- -----------
30 28-SEP-2000 dscully 115.0 Created.
31 14-MAY-2001 meshah 115.1 Changed init_tax_notifications procedure
32 cursor c_transaction,c_state_list,
33 check_final_notifier procedure
34 cursor c_payroll_contact,c_gre_contact
35 procedure get_next_notifier
36 cursor c_payroll_contact and c_gre_contact
37 to work with new SS.
38 27-AUG-2001 meshah 115.2 Copy of pyustxwf.pkb
39 28-AUG-2001 meshah 115.3 Now we are conditionally displaying
40 the message in workflow. Added a call
41 to PAY_W4_WF_STATE_MSG.
42 18-OCT-2001 meshah 115.4 setting CURRENT_ASSIGNMENT_ID in
43 init_tax_notifications.
44 Also changed the checking for assignment_id
45 from > to =.
46 23-JUL-2002 meshah 115.3 changed the cursor c_gre_contact to avoid
47 getting a invalid number error.
48 bug# 2477654.
49 26-JUL-2002 meshah 115.4 included a check for pta.transaction_parent_id
50 is null in cursor c_gre_contact. This will
51 get us only one row.
52 08-SEP-2003 irgonzal 115.5 Bug fix 3132854. Modified c_gre_contact
53 cursor: removed reference to 'apps' schema.
54 24-Nov-2003 meshah 115.6 changed the cursor c_state_list and added a
55 new cursor c_state_exempt_list. We will
56 append only those states that are affected
57 by the W4 change. Bug 3151569
58 13-Jan-2004 rsethupa 115.7 Bug fix 3362223.
59 11.5.10 Performance changes
60 26-SEP-2005 jgoswami 115.8 Bug 4599982 modified call from
61 hr_util_web.rtn_msg_txt to
62 hr_util_misc_web.return_msg_text for R12 changes
63 11-aug-2006 jgoswami 115.9 Bug 5198005 - Suppress W4 Notifications for the
64 W4 forms that are exempt or at a level above 10
65 allowances as IRS does not require Employer to
66 Send it. Based on the value of the DFF the
67 Notification will be sent or suppressed. Default
68 the Notification will be Suppressed.
69 Similarly Information message on Review page is
70 also suppressed.
71 21-aug-2006 vaprakas 115.10 Included the item attribute text with
72 aname=PERFORMER.
73
74 *******************************************************************/
75 AS
76
77 /******************************************************************
78 ** private package global declarations
79 ******************************************************************/
80 gv_package VARCHAR2(50) := 'pay_us_w4_wf';
81
82
83 PROCEDURE set_custom_wf_globals(p_itemtype in varchar2
84 ,p_itemkey in varchar2)
85 /******************************************************************
86 **
87 ** Description:
88 ** initializes package variables of the custom approval package
89 **
90 ** Access Status:
91 ** Private
92 **
93 ******************************************************************/
94 IS
95
96 BEGIN
97 hr_approval_custom.g_itemtype := p_itemtype;
98 hr_approval_custom.g_itemkey := p_itemkey;
99
100 end set_custom_wf_globals;
101
102
103 PROCEDURE start_wf(p_transaction_id IN pay_stat_trans_audit.stat_trans_audit_id%TYPE,
104 p_process IN VARCHAR2
105 )
106 /******************************************************************
107 **
108 ** Description:
109 ** initializes and starts workflow process
110 **
111 ** Access Status:
112 ** Public
113 **
114 ******************************************************************/
115 IS
116 lv_itemkey VARCHAR2(100);
117 l_proc VARCHAR2(80) := gv_package || '.start_wf';
118 BEGIN
119
120 hr_utility.set_location('Entering: ' || l_proc,5);
121
122 lv_itemkey := to_char(p_transaction_id);
123
124 wf_engine.createProcess(itemtype => gv_itemtype
125 ,itemkey => lv_itemkey
126 ,process => p_process
127 );
128
129 wf_engine.SetItemAttrNumber(itemtype => gv_itemtype
130 ,itemkey => lv_itemkey
131 ,aname => 'TRANSACTION_ID'
132 ,avalue => p_transaction_id
133 );
134
135 wf_engine.startProcess(itemtype => gv_itemtype
136 ,itemkey => lv_itemkey
137 );
138
139 hr_utility.set_location('Leaving: ' || l_proc,20);
140 END start_wf;
141
142
143 PROCEDURE init_tax_notifications(itemtype in varchar2
144 ,itemkey in varchar2
145 ,actid in number
146 ,funcmode in varchar2
147 ,result out nocopy varchar2
148 )
149 /******************************************************************
150 **
151 ** Description:
152 ** Initializes the item attributes as appropriate.
153 **
154 ** Access Status:
155 ** Public
156 **
157 ******************************************************************/
158 IS
159 ln_transaction_id NUMBER(15);
160 lv_status_code VARCHAR2(30);
161
162 CURSOR c_transaction IS
163 select *
164 from pay_stat_trans_audit
165 where source4_type = 'TRANSACTION_ID'
166 and source4 = ln_transaction_id;
167
168
169 CURSOR c_state_list IS
170 select pus.state_name
171 from pay_stat_trans_audit pta
172 ,pay_us_states pus
173 where pta.source4 = ln_transaction_id
174 and pus.state_code = substr(pta.source1,1,2)
175 and exists(select 'x' from pay_us_state_tax_info_f stif
176 where stif.state_code = pus.state_code
177 and stif.sta_information7 = 'Y'
178 and stif.sta_information9 = 'Y'
179 and pta.transaction_effective_date between
180 stif.effective_start_date and
181 stif.effective_end_date);
182
183
184 CURSOR c_exempt_state_list IS
185 select pus.state_name
186 from pay_stat_trans_audit pta
187 ,pay_us_states pus
188 where pta.source4 = ln_transaction_id
189 and pus.state_code = substr(pta.source1,1,2)
190 and exists(select 'x' from pay_us_state_tax_info_f stif
191 where stif.state_code = pus.state_code
192 and stif.sta_information7 = 'Y'
193 and stif.sta_information9 is null
194 and pta.transaction_effective_date between
195 stif.effective_start_date and
196 stif.effective_end_date);
197
198 CURSOR c_fed_filing_status_code IS
199 select meaning
200 from hr_lookups
201 where lookup_type = 'US_FIT_FILING_STATUS'
202 and lookup_code = lv_status_code;
203
204
205
206 r_trans_rec c_transaction%ROWTYPE;
207 lv_state_list VARCHAR2(10000);
208 lv_state_name VARCHAR2(50);
209 lv_status hr_lookups.meaning%TYPE;
210 lv_username VARCHAR2(80);
211 lv_disp_name VARCHAR2(80);
212 l_proc VARCHAR2(80) := gv_package || '.init_tax_notifications';
213
214 lv_state_msg fnd_new_messages.message_text%TYPE;
215
216 lv_exempt_state_list VARCHAR2(10000);
217 lv_exempt_state_name VARCHAR2(50);
218 lv_exempt_state_msg fnd_new_messages.message_text%TYPE;
219 BEGIN
220 hr_utility.set_location('Entering: ' || l_proc,5);
221
222 if funcmode = 'RUN' then
223 -- get the transaction
224
225 ln_transaction_id := wf_engine.GetItemAttrNumber(
226 itemtype => itemtype
227 ,itemkey => itemkey
228 ,aname => 'TRANSACTION_ID'
229 );
230
231 -- open this cursor with transaction_step_id
232 OPEN c_transaction;
233 FETCH c_transaction into r_trans_rec;
234 CLOSE c_transaction;
235 hr_utility.set_location(l_proc,10);
236
237 wf_directory.GetUserName(p_orig_system => 'PER',
238 p_orig_system_id => r_trans_rec.person_id,
239 p_name => lv_username,
240 p_display_name => lv_disp_name );
241
242 wf_engine.SetItemAttrText(itemtype => itemtype
243 ,itemkey => itemkey
244 ,aname => 'EMPLOYEE_USERNAME'
245 ,avalue => lv_username );
246
247 wf_engine.SetItemAttrText(itemtype => itemtype -- added by vaprakas bug 5673790
248 ,itemkey => itemkey
249 ,aname => 'PERFORMER'
250 ,avalue => lv_username );
251
252 wf_engine.SetItemAttrNumber(itemtype => itemtype
253 ,itemkey => itemkey
254 ,aname => 'EMPLOYEE_PERSON_ID'
255 ,avalue => r_trans_rec.person_id);
256
257 wf_engine.SetItemAttrText(itemtype => itemtype
258 ,itemkey => itemkey
259 ,aname => 'EMPLOYEE_DISPLAY_NAME'
260 ,avalue => lv_disp_name);
261
262 wf_engine.SetItemAttrNumber(itemtype => itemtype
263 ,itemkey => itemkey
264 ,aname => 'CURRENT_ASSIGNMENT_ID'
265 ,avalue => r_trans_rec.assignment_id );
266
267 wf_engine.SetItemAttrText(itemtype => itemtype
268 ,itemkey => itemkey
269 ,aname => 'TRANSACTION_SOURCE'
270 ,avalue => r_trans_rec.source3 );
271
272 wf_engine.SetItemAttrDate(itemtype => itemtype
273 ,itemkey => itemkey
274 ,aname => 'TRANSACTION_DATE'
275 ,avalue => r_trans_rec.transaction_date );
276
277 -- build the submission details
278 if r_trans_rec.transaction_subtype = 'W4' then
279
280 if r_trans_rec.source1 = '00-000-0000' then
281 hr_utility.set_location(l_proc,20);
282
283 lv_status_code := r_trans_rec.audit_information1;
284 open c_fed_filing_status_code;
285 fetch c_fed_filing_status_code into lv_status;
286 if c_fed_filing_status_code%NOTFOUND then
287 lv_status := lv_status_code;
288 end if;
289 close c_fed_filing_status_code;
290
291 wf_engine.SetItemAttrText(
292 itemtype => itemtype
293 ,itemkey => itemkey
294 ,aname => 'FILING_STATUS_LABEL'
295 ,avalue => lv_status
296 );
297
298 wf_engine.SetItemAttrText(
299 itemtype => itemtype
300 ,itemkey => itemkey
301 ,aname => 'ALLOWANCES'
302 ,avalue => r_trans_rec.audit_information2
303 );
304
305 wf_engine.SetItemAttrText(
306 itemtype => itemtype
307 ,itemkey => itemkey
308 ,aname => 'ADDITIONAL_TAX'
309 ,avalue => r_trans_rec.audit_information3
310 );
311
312 wf_engine.SetItemAttrText(
313 itemtype => itemtype
314 ,itemkey => itemkey
315 ,aname => 'FIT_EXEMPT'
316 ,avalue => r_trans_rec.audit_information4
317 );
318
319 -- build the state list
320 open c_state_list;
321 fetch c_state_list into lv_state_name;
322
323 while c_state_list%FOUND LOOP
324 if nvl(instr(lv_state_list,lv_state_name),0) = 0 then
325 lv_state_list := lv_state_list || ' ' || lv_state_name;
326 end if;
327 fetch c_state_list into lv_state_name;
328 end loop;
329
330 if lv_state_list is not null then
331
332 lv_state_msg := hr_util_misc_web.return_msg_text('PAY_W4_WF_STATE_MSG','PAY');
333 lv_state_list := lv_state_msg ||' '|| lv_state_list;
334
335 end if;
336
337 open c_exempt_state_list;
338 fetch c_exempt_state_list into lv_exempt_state_name;
339
340 while c_exempt_state_list%FOUND LOOP
341 if nvl(instr(lv_state_list,lv_exempt_state_name),0) = 0 then
342 lv_state_list := lv_state_list || ' ' || lv_exempt_state_name;
343 end if;
344 fetch c_exempt_state_list into lv_exempt_state_name;
345 end loop;
346
347 -- put the state list details in
348 wf_engine.SetItemAttrText(itemtype => itemtype
349 ,itemkey => itemkey
350 ,aname => 'STATE_LIST'
351 ,avalue => lv_state_list
352 );
353 hr_utility.set_location(l_proc,30);
354
355 end if; -- Federal jurisdiction
356 end if; -- W4 transaction subtype
357 end if; -- funcmode = RUN
358
359 hr_utility.set_location('Leaving: ' || l_proc, 100);
360
361 end init_tax_notifications;
362
363 procedure check_final_notifier( itemtype in varchar2,
364 itemkey in varchar2,
365 actid in number,
366 funcmode in varchar2,
367 result out nocopy varchar2 ) is
368
369 /******************************************************************
370 **
371 ** Description:
372 ** Determines if this is the last person in the payroll notification chain
373 **
374 ** Access Status:
375 ** Public
376 **
377 ******************************************************************/
378
379 l_proc varchar2(80) := gv_package||'check_final_notifier';
380 lv_contact_source VARCHAR2(50);
381 ln_transaction_id pay_stat_trans_audit.stat_trans_audit_id%TYPE;
382 ln_current_assignment_id per_assignments_f.assignment_id%TYPE;
383 lv_contact_user_name VARCHAR2(150);
384 ln_contact_person_id per_people_f.person_id%TYPE;
385 ln_employee_person_id per_people_f.person_id%TYPE;
386
387 -- get the payroll contact
388
389 CURSOR c_payroll_contact IS
390 select /*+ ordered */ prl.prl_information1 --Bug 3362223
391 from pay_stat_trans_audit pta
392 ,per_assignments_f paf
393 ,pay_payrolls_f prl
394 where prl.payroll_id = paf.payroll_id
395 and prl.prl_information_category = 'US'
396 and paf.assignment_id = pta.assignment_id
397 and pta.source4 = ln_transaction_id
398 and pta.source4_type = 'TRANSACTION_ID'
399 and pta.assignment_id = ln_current_assignment_id
400 -- and pta.transaction_parent_id is null
401 and pta.transaction_effective_date between prl.effective_start_date and
402 prl.effective_end_date
403 and pta.transaction_effective_date between paf.effective_start_date and
404 paf.effective_end_date
405 order by pta.assignment_id asc;
406
407 -- get the GRE contact
408
409 CURSOR c_gre_contact IS
410 select org.org_information1
411 from hr_organization_information org
412 where org.org_information_context || '' = 'Contact Information'
413 and org.organization_id =
414 (select /*+ ordered */ hsc.segment1 --Bug 3362223
415 from pay_stat_trans_audit pta
416 ,per_assignments_f paf
417 ,hr_soft_coding_keyflex hsc
418 where hsc.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
419 and paf.assignment_id = pta.assignment_id
420 and pta.source4 = ln_transaction_id
421 and pta.source4_type = 'TRANSACTION_ID'
422 and pta.transaction_parent_id is null
423 and paf.assignment_id = ln_current_assignment_id
424 and pta.transaction_effective_date between paf.effective_start_date
425 and paf.effective_end_date);
426 /*
427 select org.org_information1
428 from hr_organization_information org
429 ,per_assignments_f paf
430 ,pay_stat_trans_audit pta
431 ,hr_soft_coding_keyflex hsc
432 where org.organization_id = hsc.segment1
433 and hsc.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
434 and paf.assignment_id = pta.assignment_id
435 and pta.source4 = ln_transaction_id
436 and pta.source4_type = 'TRANSACTION_ID'
437 and org.org_information_context = 'Contact Information'
438 and pta.assignment_id = ln_current_assignment_id
439 and pta.transaction_effective_date between paf.effective_start_date and
440 paf.effective_end_date
441 order by pta.assignment_id asc;
442 */
443
444 begin
445 --hr_utility.trace_on(null,'oracle');
446
447 hr_utility.set_location('Entering: ' || l_proc || ':' || funcmode, 5);
448
449 if ( funcmode = 'RUN' ) then
450 lv_contact_source := fnd_profile.value('HR_PAYROLL_CONTACT_SOURCE');
451
452 ln_transaction_id := wf_engine.GetItemAttrNumber
453 (itemtype => itemtype
454 ,itemkey => itemkey
455 ,aname => 'TRANSACTION_ID'
456 );
457
458 ln_current_assignment_id := wf_engine.GetItemAttrNumber
459 (itemtype => itemtype
460 ,itemkey => itemkey
461 ,aname => 'CURRENT_ASSIGNMENT_ID'
462 );
463
464 ln_contact_person_id := wf_engine.GetItemAttrNumber
465 (itemtype => itemtype
466 ,itemkey => itemkey
467 ,aname => 'CONTACT_PERSON_ID'
468 );
469
470 ln_employee_person_id := wf_engine.GetItemAttrNumber
471 (itemtype => itemtype
472 ,itemkey => itemkey
473 ,aname => 'EMPLOYEE_PERSON_ID'
474 );
475 hr_utility.trace('Profile Value is : '|| lv_contact_source);
476 hr_utility.trace('Transaction Id is : '|| ln_transaction_id);
477 hr_utility.trace('Assignment Id is : '|| ln_current_assignment_id);
478 hr_utility.trace('Contact Person Id is : '|| ln_contact_person_id);
479 hr_utility.trace('Employee Id is : '|| ln_employee_person_id);
480
481 -- -----------------------------------------------------------------------
482 -- expose the wf control variables to the custom package
483 -- -----------------------------------------------------------------------
484 if lv_contact_source = 'CUSTOM' then
485 set_custom_wf_globals
486 (p_itemtype => itemtype
487 ,p_itemkey => itemkey);
488
489 -- call a custom check final notifier. Returns a 'Yes', 'No' or 'Error'
490
491 result := 'COMPLETE:'||
492 hr_approval_custom.check_final_payroll_notifier
493 (p_forward_to_person_id => ln_contact_person_id
494 ,p_person_id => ln_employee_person_id );
495
496
497 elsif lv_contact_source = 'PAYROLL' then
498 open c_payroll_contact;
499 fetch c_payroll_contact into lv_contact_user_name;
500 hr_utility.trace('Contact User is : '|| lv_contact_user_name);
501 if c_payroll_contact%FOUND then
502 result := 'COMPLETE:N';
503 else
504 result := 'COMPLETE:Y';
505 end if;
506
507 close c_payroll_contact;
508
509 hr_utility.trace('Result is : '|| result);
510
511 elsif lv_contact_source = 'GRE' then
512 open c_gre_contact;
513 fetch c_gre_contact into lv_contact_user_name;
514 if c_gre_contact%FOUND then
515 result := 'COMPLETE:N';
516 else
517 result := 'COMPLETE:Y';
518 end if;
519
520 close c_gre_contact;
521
522 else -- some other source we don't understand yet
523 result := 'ERROR:UNKNOWN_CONTACT_SOURCE';
524 end if;
525
526
527 elsif ( funcmode = 'CANCEL' ) then
528 null;
529
530 end if;
531
532 hr_utility.set_location('Leaving: ' || l_proc, 100);
533 end check_final_notifier;
534
535
536 PROCEDURE get_next_notifier(itemtype in varchar2
537 ,itemkey in varchar2
538 ,actid in number
539 ,funcmode in varchar2
540 ,result out nocopy varchar2
541 )
542 /******************************************************************
543 **
544 ** Description:
545 ** Gets the next payroll rep who needs to be notified and sets
546 ** the forward from/to item attributes as proper.
547 **
548 **
549 ** Access Status:
550 ** Public
551 **
552 ******************************************************************/
553 IS
554
555 l_proc varchar2(61) := gv_package||'get_next_payroll_notifer';
556 ln_transaction_id pay_stat_trans_audit.stat_trans_audit_id%TYPE;
557 ln_current_assignment_id per_assignments_f.assignment_id%TYPE;
558 lv_contact_source VARCHAR2(50);
559 lv_curr_contact_user VARCHAR2(100);
560 lv_next_contact_user VARCHAR2(100);
561 lv_dummy VARCHAR2(100);
562 ln_curr_contact_person_id per_people_f.person_id%TYPE;
563 ln_next_contact_person_id per_people_f.person_id%TYPE;
564
565 CURSOR c_payroll_contact IS
566 select /*+ ordered */ usr.employee_id --Bug 3362223
567 ,pta.assignment_id
568 from pay_stat_trans_audit pta
569 ,per_assignments_f paf
570 ,pay_payrolls_f prl
571 ,fnd_user usr
572 where prl.payroll_id = paf.payroll_id
573 and usr.user_name = prl.prl_information1
574 and prl.prl_information_category = 'US'
575 and paf.assignment_id = pta.assignment_id
576 and pta.source4 = ln_transaction_id
577 and pta.source4_type = 'TRANSACTION_ID'
578 and pta.assignment_id = ln_current_assignment_id
579 -- and pta.transaction_parent_id is null
580 and pta.transaction_effective_date between prl.effective_start_date and
581 prl.effective_end_date
582 and pta.transaction_effective_date between paf.effective_start_date and
583 paf.effective_end_date
584 order by pta.assignment_id asc;
585
586 CURSOR c_gre_contact IS
587 select usr.employee_id
588 ,pta.assignment_id
589 from hr_organization_information org
590 ,pay_stat_trans_audit pta
591 ,fnd_user usr
592 where usr.user_name = org.org_information1
593 and pta.assignment_id = ln_current_assignment_id
594 and pta.source4 = ln_transaction_id
595 and pta.source4_type = 'TRANSACTION_ID'
596 and pta.transaction_parent_id is null
597 and org.org_information_context ||'' = 'Contact Information'
598 and org.organization_id = (select hsc.segment1
599 from per_assignments_f paf
600 ,hr_soft_coding_keyflex hsc
601 where paf.assignment_id = ln_current_assignment_id
602 and hsc.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
603 and pta.transaction_effective_date
604 between paf.effective_start_date
605 and paf.effective_end_date
606 );
607
608
609 begin
610
611 hr_utility.set_location('Entering: ' || l_proc || ':'|| funcmode,5);
612
613 if ( funcmode = 'RUN' ) then
614
615 lv_contact_source := fnd_profile.value('HR_PAYROLL_CONTACT_SOURCE');
616
617 ln_transaction_id := wf_engine.GetItemAttrNumber
618 (itemtype => itemtype
619 ,itemkey => itemkey
620 ,aname => 'TRANSACTION_ID');
621
622 ln_current_assignment_id := wf_engine.GetItemAttrNumber
623 (itemtype => itemtype
624 ,itemkey => itemkey
625 ,aname => 'CURRENT_ASSIGNMENT_ID');
626
627 lv_curr_contact_user := wf_engine.GetItemAttrText
628 (itemtype => itemtype
629 ,itemkey => itemkey
630 ,aname => 'CONTACT_USERNAME');
631
632 ln_curr_contact_person_id := wf_engine.GetItemAttrNumber
633 (itemtype => itemtype
634 ,itemkey => itemkey
635 ,aname => 'CONTACT_PERSON_ID');
636
637 if lv_contact_source = 'CUSTOM' then
638 -- -----------------------------------------------------------------------
639 -- expose the wf control variables to the custom package
640 -- -----------------------------------------------------------------------
641 set_custom_wf_globals
642 (p_itemtype => itemtype
643 ,p_itemkey => itemkey);
644
645 -- set the next forward to
646
647 ln_next_contact_person_id :=
648 hr_approval_custom.Get_Next_Payroll_Notifier
649 (p_person_id => ln_curr_contact_person_id);
650
651 elsif lv_contact_source = 'PAYROLL' then
652 open c_payroll_contact;
653 fetch c_payroll_contact into ln_next_contact_person_id,ln_current_assignment_id;
654 close c_payroll_contact;
655
656 elsif lv_contact_source = 'GRE' then
657 open c_gre_contact;
658 fetch c_gre_contact into ln_next_contact_person_id,ln_current_assignment_id;
659 close c_gre_contact;
660
661 else
662 result := 'ERROR:UNKNOWN_CONTACT_SOURCE';
663 return;
664 end if;
665
666 if ( ln_next_contact_person_id is null ) then
667 result := 'COMPLETE:F';
668
669 else
670 wf_directory.GetUserName
671 (p_orig_system => 'PER'
672 ,p_orig_system_id => ln_next_contact_person_id
673 ,p_name => lv_next_contact_user
674 ,p_display_name => lv_dummy);
675
676 wf_engine.SetItemAttrNumber
677 (itemtype => itemtype
678 ,itemkey => itemkey
679 ,aname => 'CONTACT_PERSON_ID'
680 ,avalue => ln_next_contact_person_id);
681
682 wf_engine.SetItemAttrNumber
683 (itemtype => itemtype
684 ,itemkey => itemkey
685 ,aname => 'CURRENT_ASSIGNMENT_ID'
686 ,avalue => ln_current_assignment_id);
687
688 wf_engine.SetItemAttrText
689 (itemtype => itemtype
690 ,itemkey => itemkey
691 ,aname => 'CONTACT_USERNAME'
692 ,avalue => lv_next_contact_user);
693
694 hr_utility.set_location('Leaving: ' || l_proc,100);
695 result := 'COMPLETE:T';
696
697 end if;
698
699 elsif ( funcmode = 'CANCEL' ) then
700 null;
701
702 end if;
703 --
704 end get_next_notifier;
705
706
707 PROCEDURE check_for_notification(itemtype in varchar2
708 ,itemkey in varchar2
709 ,actid in number
710 ,funcmode in varchar2
711 ,result out nocopy varchar2
712 )
713 /******************************************************************
714 **
715 ** Description:
716 ** Checks the submitted form for conditions that require
717 ** notification to be sent to a Payroll manager.
718 **
719 ** Access Status:
720 ** Public
721 **
722 ******************************************************************/
723 IS
724 ln_transaction_id NUMBER(15);
725
726 CURSOR c_transaction is
727 select transaction_subtype
728 ,business_group_id
729 ,person_id
730 ,assignment_id
731 ,transaction_effective_date
732 ,source1
733 ,source1_type
734 ,source2
735 ,source2_type
736 ,audit_information2
737 ,audit_information4
738 from pay_stat_trans_audit
739 where source4_type = 'TRANSACTION_ID'
740 and source4 = ln_transaction_id;
741
742 CURSOR c_fed_allowance_limit is
743 select fed_information1
744 from pay_us_federal_tax_info_f
745 where fed_information_category = 'ALLOWANCES LIMIT'
746 and trunc(sysdate) between effective_start_date
747 and effective_end_date;
748
749
750 lv_exception_reason VARCHAR2(10000);
751 lv_context VARCHAR2(50);
752 lv_level VARCHAR2(50);
753 lv_notify VARCHAR2(100);
754 lr_trans_rec c_transaction%ROWTYPE;
755 lr_fed_rec c_fed_allowance_limit%ROWTYPE;
756 l_proc VARCHAR2(80) := gv_package || '.check_for_notification';
757
758 BEGIN
759 hr_utility.set_location('Entering: ' || l_proc || ':' || funcmode,5);
760 if (funcmode = 'RUN') then
761 ln_transaction_id := wf_engine.GetItemAttrNumber(itemtype => itemtype
762 ,itemkey => itemkey
763 ,aname => 'TRANSACTION_ID');
764
765 hr_utility.trace('Transaction Id is : '|| to_char(ln_transaction_id));
766
767 -- from the transaction_id get the details
768
769 open c_transaction;
770 fetch c_transaction into lr_trans_rec;
771 close c_transaction;
772
773 hr_utility.trace('lr_trans_rec.transaction_subtype is : '|| lr_trans_rec.transaction_subtype);
774
775 if lr_trans_rec.transaction_subtype = 'W4' then
776
777 -- check the W4 Notification Required setup at Business Group level
778 -- and /or Organization levle
779
780 -- Check the Organization Level and/or Business Group Level Context
781 --is Set for W4 Notifications.
782 -- Organization Level Context
783 lv_context := 'US_ORG_REP_PREFERENCES';
784 lv_level := 'ORG';
785
786 lv_notify := pay_us_web_w4.get_org_context(lr_trans_rec.person_id, lv_context,lv_level);
787
788 hr_utility.trace('ORG pay_us_web_w4.get_org_context lv_notify = '||lv_notify);
789 if lv_notify = 'NOTFOUND' then
790 -- Business Group Level Context
791 lv_context := 'US_BG_REP_PREFERENCES';
792 lv_level := 'BG';
793 lv_notify := pay_us_web_w4.get_org_context(lr_trans_rec.person_id, lv_context,lv_level);
794 hr_utility.trace('BG pay_us_web_w4.get_org_context lv_notify = '||lv_notify);
795 end if;
796
797
798
799 hr_utility.trace('lr_trans_rec.source1 is : '|| lr_trans_rec.source1);
800
801 if lv_notify = 'Y' then
802
803 if lr_trans_rec.source1 = '00-000-0000' then
804
805 /* We check for two conditions for the federal W4:
806 * First we check to see if the allowance is over the allowance limit.
807 * Next, we check to see if FIT exempt = Y
808 */
809 open c_fed_allowance_limit;
810 fetch c_fed_allowance_limit into lr_fed_rec;
811 close c_fed_allowance_limit;
812
813 hr_utility.trace('lr_fed_rec.fed_information1 is : '||lr_fed_rec.fed_information1);
814 if (lr_fed_rec.fed_information1 is not null) and
815 (to_number(lr_trans_rec.audit_information2) >
816 to_number(lr_fed_rec.fed_information1)) then
817
818 hr_utility.trace('Inside if for allowance checking.');
819 hr_utility.set_message(801,'PAY_US_OTF_W4_FED_OVERALLOW');
820
821 wf_engine.SetItemAttrText(itemtype => itemtype
822 ,itemkey => itemkey
823 ,aname => 'EXCEPTION_REASON'
824 ,avalue => hr_utility.get_message);
825
826 result := 'COMPLETE:Y';
827
828 elsif lr_trans_rec.audit_information4 = 'Y' then
829 hr_utility.trace('Inside elsif lr_trans_rec.audit_information4.');
830 hr_utility.set_message(801,'PAY_US_OTF_FED_EXEMPTWARN');
831
832 wf_engine.SetItemAttrText(itemtype => itemtype
833 ,itemkey => itemkey
834 ,aname => 'EXCEPTION_REASON'
835 ,avalue => hr_utility.get_message);
836 result := 'COMPLETE:Y';
837 else
838 hr_utility.trace('failed on allowances and exempt');
839 result := 'COMPLETE:N';
840 end if;
841 else /* lr_trans_rec.source1 = '00-000-0000' */
842
843 hr_utility.trace('source1 <> 00-000-0000');
844 result := 'COMPLETE:N';
845 end if;
846 else /*lv_notify = 'Y' */
847 hr_utility.trace('lv_notify <> Y');
848 result := 'COMPLETE:N';
849 end if;
850 else /* lr_trans_rec.transaction_subtype = 'W4' */
851 hr_utility.trace('transaction_subtype <> W4');
852 result := 'COMPLETE:N';
853 end if;
854 end if;
855 hr_utility.set_location('Leaving: ' || l_proc || ':' || result,100);
856
857 end check_for_notification;
858
859 /* Uncomment following two lines for debug */
860 -- begin
861 -- hr_utility.trace_on(null,'pyusw4wf');
862
863 END pay_us_w4_wf;