[Home] [Help]
PACKAGE BODY: APPS.PAY_US_TAX_WF
Source
1 PACKAGE BODY pay_us_tax_wf
2 /* $Header: pyustxwf.pkb 115.3 2004/01/13 06:30:30 rsethupa noship $ *
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_tax_wf
21
22 Description : Contains workflow code the Tax 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 27-AUG-2001 meshah 115.2 rolling back to 115.0
32 so that we can work with old
33 workflow process.
34 13-Jan-2004 rsethupa 115.3 Bug Fix 3361934
35 11.5.10 Performance Changes
36 Added NOCOPY after out parameter
37 in procedures.
38 *******************************************************************/
39 AS
40
41 /******************************************************************
45
42 ** private package global declarations
43 ******************************************************************/
44 gv_package VARCHAR2(50) := 'pay_us_tax_wf';
46
47 PROCEDURE set_custom_wf_globals(p_itemtype in varchar2
48 ,p_itemkey in varchar2)
49 /******************************************************************
50 **
51 ** Description:
52 ** initializes package variables of the custom approval package
53 **
54 ** Access Status:
55 ** Private
56 **
57 ******************************************************************/
58 IS
59
60 BEGIN
61 hr_approval_custom.g_itemtype := p_itemtype;
62 hr_approval_custom.g_itemkey := p_itemkey;
63
64 end set_custom_wf_globals;
65
66
67 PROCEDURE start_wf(p_transaction_id IN pay_stat_trans_audit.stat_trans_audit_id%TYPE,
68 p_process IN VARCHAR2
69 )
70 /******************************************************************
71 **
72 ** Description:
73 ** initializes and starts workflow process
74 **
75 ** Access Status:
76 ** Public
77 **
78 ******************************************************************/
79 IS
80 lv_itemkey VARCHAR2(100);
81 l_proc VARCHAR2(80) := gv_package || '.start_wf';
82 BEGIN
83
84 hr_utility.set_location('Entering: ' || l_proc,5);
85
86 lv_itemkey := to_char(p_transaction_id);
87
88 wf_engine.createProcess(itemtype => gv_itemtype
89 ,itemkey => lv_itemkey
90 ,process => p_process
91 );
92
93 wf_engine.SetItemAttrNumber(itemtype => gv_itemtype
94 ,itemkey => lv_itemkey
95 ,aname => 'TRANSACTION_ID'
96 ,avalue => p_transaction_id
97 );
98
99 wf_engine.startProcess(itemtype => gv_itemtype
100 ,itemkey => lv_itemkey
101 );
102
103 hr_utility.set_location('Leaving: ' || l_proc,20);
104 END start_wf;
105
106
107 PROCEDURE init_tax_notifications(itemtype in varchar2
108 ,itemkey in varchar2
109 ,actid in number
110 ,funcmode in varchar2
111 ,result out NOCOPY varchar2
112 )
113 /******************************************************************
114 **
115 ** Description:
116 ** Initializes the item attributes as appropriate.
117 **
118 ** Access Status:
119 ** Public
120 **
121 ******************************************************************/
122 IS
123 ln_transaction_id NUMBER(15);
124 lv_status_code VARCHAR2(30);
125
126 CURSOR c_transaction IS
127 select *
128 from pay_stat_trans_audit
129 where stat_trans_audit_id = ln_transaction_id;
130
131 CURSOR c_state_list IS
132 select pus.state_name
133 from pay_stat_trans_audit pta
134 ,pay_us_states pus
135 where pta.transaction_parent_id = ln_transaction_id
136 and pus.state_code = substr(pta.source1,1,2)
137 and exists(select 'x' from pay_us_state_tax_info_f stif
138 where stif.state_code = pus.state_code
139 and stif.sta_information7 = 'Y'
140 and pta.transaction_effective_date between
141 stif.effective_start_date and
142 stif.effective_end_date);
143
144 CURSOR c_fed_filing_status_code IS
145 select meaning
146 from hr_lookups
147 where lookup_type = 'US_FIT_FILING_STATUS'
148 and lookup_code = lv_status_code;
149
150
151
152 r_trans_rec c_transaction%ROWTYPE;
153 lv_state_list VARCHAR2(10000);
154 lv_state_name VARCHAR2(50);
155 lv_status hr_lookups.meaning%TYPE;
156 lv_username VARCHAR2(80);
157 lv_disp_name VARCHAR2(80);
158 l_proc VARCHAR2(80) := gv_package || '.init_tax_notifications';
159
160 BEGIN
161 hr_utility.set_location('Entering: ' || l_proc,5);
162
163 if funcmode = 'RUN' then
164 -- get the transaction
165 ln_transaction_id := wf_engine.GetItemAttrNumber(
166 itemtype => itemtype
167 ,itemkey => itemkey
168 ,aname => 'TRANSACTION_ID'
169 );
170
171 OPEN c_transaction;
172 FETCH c_transaction into r_trans_rec;
173 CLOSE c_transaction;
174 hr_utility.set_location(l_proc,10);
175
176 wf_directory.GetUserName( p_orig_system => 'PER',
177 p_orig_system_id => r_trans_rec.person_id,
178 p_name => lv_username,
179 p_display_name => lv_disp_name );
180
181 wf_engine.SetItemAttrText(itemtype => itemtype
182 ,itemkey => itemkey
183 ,aname => 'EMPLOYEE_USERNAME'
184 ,avalue => lv_username
185 );
186
187 wf_engine.SetItemAttrNumber(itemtype => itemtype
188 ,itemkey => itemkey
189 ,aname => 'EMPLOYEE_PERSON_ID'
190 ,avalue => r_trans_rec.person_id);
191
192 wf_engine.SetItemAttrText(itemtype => itemtype
193 ,itemkey => itemkey
194 ,aname => 'EMPLOYEE_DISPLAY_NAME'
195 ,avalue => lv_disp_name
196 );
197
198 wf_engine.SetItemAttrNumber(itemtype => itemtype
199 ,itemkey => itemkey
200 ,aname => 'CURRENT_ASSIGNMENT_ID'
201 ,avalue => 0
202 );
203
204 wf_engine.SetItemAttrText(itemtype => itemtype
205 ,itemkey => itemkey
206 ,aname => 'TRANSACTION_SOURCE'
207 ,avalue => r_trans_rec.source3
208 );
209
213 ,avalue => r_trans_rec.transaction_date
210 wf_engine.SetItemAttrDate(itemtype => itemtype
211 ,itemkey => itemkey
212 ,aname => 'TRANSACTION_DATE'
214 );
215
216 -- build the submission details
217 if r_trans_rec.transaction_subtype = 'W4' then
218
219 if r_trans_rec.source1 = '00-000-0000' then
220 hr_utility.set_location(l_proc,20);
221 lv_status_code := r_trans_rec.audit_information1;
222 open c_fed_filing_status_code;
223 fetch c_fed_filing_status_code into lv_status;
224 if c_fed_filing_status_code%NOTFOUND then
225 lv_status := lv_status_code;
226 end if;
227 close c_fed_filing_status_code;
228
229 wf_engine.SetItemAttrText(
230 itemtype => itemtype
231 ,itemkey => itemkey
232 ,aname => 'FILING_STATUS_LABEL'
233 ,avalue => lv_status
234 );
235
236 wf_engine.SetItemAttrText(
237 itemtype => itemtype
238 ,itemkey => itemkey
239 ,aname => 'ALLOWANCES'
240 ,avalue => r_trans_rec.audit_information2
241 );
242
243 wf_engine.SetItemAttrText(
244 itemtype => itemtype
245 ,itemkey => itemkey
246 ,aname => 'ADDITIONAL_TAX'
247 ,avalue => r_trans_rec.audit_information3
248 );
249
250 wf_engine.SetItemAttrText(
251 itemtype => itemtype
252 ,itemkey => itemkey
253 ,aname => 'FIT_EXEMPT'
254 ,avalue => r_trans_rec.audit_information4
255 );
256
257 -- build the state list
258 open c_state_list;
259 fetch c_state_list into lv_state_name;
260
261 while c_state_list%FOUND LOOP
262 if nvl(instr(lv_state_list,lv_state_name),0) = 0 then
263 lv_state_list := lv_state_list || ' ' || lv_state_name;
264 end if;
265 fetch c_state_list into lv_state_name;
266 end loop;
267
268 -- put the state list details in
269 wf_engine.SetItemAttrText(itemtype => itemtype
270 ,itemkey => itemkey
271 ,aname => 'STATE_LIST'
272 ,avalue => lv_state_list
273 );
274 hr_utility.set_location(l_proc,30);
275
276 end if; -- Federal jurisdiction
277 end if; -- W4 transaction subtype
278 end if; -- funcmode = RUN
279
280 hr_utility.set_location('Leaving: ' || l_proc, 100);
281
282 end init_tax_notifications;
283
284 procedure check_final_notifier( itemtype in varchar2,
285 itemkey in varchar2,
286 actid in number,
287 funcmode in varchar2,
288 result out NOCOPY varchar2 ) is
289
290 /******************************************************************
291 **
292 ** Description:
293 ** Determines if this is the last person in the payroll notification chain
294 **
295 ** Access Status:
296 ** Public
297 **
298 ******************************************************************/
299
300 l_proc varchar2(80) := gv_package||'check_final_notifier';
301 lv_contact_source VARCHAR2(50);
302 ln_transaction_id pay_stat_trans_audit.stat_trans_audit_id%TYPE;
303 ln_current_assignment_id per_assignments_f.assignment_id%TYPE;
304 lv_contact_user_name VARCHAR2(150);
305 ln_contact_person_id per_people_f.person_id%TYPE;
306 ln_employee_person_id per_people_f.person_id%TYPE;
307
308 CURSOR c_payroll_contact IS
309 select /*+ ordered */ prl.prl_information1 --Bug 3361934
310 from pay_stat_trans_audit pta
311 ,per_assignments_f paf
312 ,pay_payrolls_f prl
313 where prl.payroll_id = paf.payroll_id
314 and prl.prl_information_category = 'US'
315 and paf.assignment_id = pta.assignment_id
316 and pta.transaction_parent_id = ln_transaction_id
317 and pta.assignment_id > ln_current_assignment_id
318 and pta.transaction_effective_date between prl.effective_start_date and
319 prl.effective_end_date
320 and pta.transaction_effective_date between paf.effective_start_date and
321 paf.effective_end_date
322 order by pta.assignment_id asc;
323
324 CURSOR c_gre_contact IS
325 select /*+ ordered */ org.org_information1 --Bug 3361934
326 from pay_stat_trans_audit pta
327 ,per_assignments_f paf
328 ,hr_soft_coding_keyflex hsc
329 ,hr_organization_information org
330 where org.organization_id = hsc.segment1
331 and hsc.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
332 and paf.assignment_id = pta.assignment_id
333 and pta.transaction_parent_id = ln_transaction_id
334 and org.org_information_context = 'Contact Information'
335 and pta.assignment_id > ln_current_assignment_id
336 and pta.transaction_effective_date between paf.effective_start_date and
337 paf.effective_end_date
338 order by pta.assignment_id asc;
339
340
341 begin
342
343 hr_utility.set_location('Entering: ' || l_proc || ':' || funcmode, 5);
344
345 if ( funcmode = 'RUN' ) then
346 lv_contact_source := fnd_profile.value('HR_PAYROLL_CONTACT_SOURCE');
347
348 ln_transaction_id := wf_engine.GetItemAttrNumber
349 (itemtype => itemtype
350 ,itemkey => itemkey
351 ,aname => 'TRANSACTION_ID'
352 );
353
354 ln_current_assignment_id := wf_engine.GetItemAttrNumber
355 (itemtype => itemtype
359
356 ,itemkey => itemkey
357 ,aname => 'CURRENT_ASSIGNMENT_ID'
358 );
360 ln_contact_person_id := wf_engine.GetItemAttrNumber
361 (itemtype => itemtype
362 ,itemkey => itemkey
363 ,aname => 'CONTACT_PERSON_ID'
364 );
365
366 ln_employee_person_id := wf_engine.GetItemAttrNumber
367 (itemtype => itemtype
368 ,itemkey => itemkey
369 ,aname => 'EMPLOYEE_PERSON_ID'
370 );
371
372 -- -----------------------------------------------------------------------
373 -- expose the wf control variables to the custom package
374 -- -----------------------------------------------------------------------
375 if lv_contact_source = 'CUSTOM' then
376 set_custom_wf_globals
377 (p_itemtype => itemtype
378 ,p_itemkey => itemkey);
379
380 -- call a custom check final notifier. Returns a 'Yes', 'No' or 'Error'
381
382 result := 'COMPLETE:'||
383 hr_approval_custom.check_final_payroll_notifier
384 (p_forward_to_person_id => ln_contact_person_id
385 ,p_person_id => ln_employee_person_id );
386
387
388 elsif lv_contact_source = 'PAYROLL' then
389 open c_payroll_contact;
390 fetch c_payroll_contact into lv_contact_user_name;
391 if c_payroll_contact%FOUND then
392 result := 'COMPLETE:N';
393 else
394 result := 'COMPLETE:Y';
395 end if;
396
397 close c_payroll_contact;
398
399 elsif lv_contact_source = 'GRE' then
400 open c_gre_contact;
401 fetch c_gre_contact into lv_contact_user_name;
402 if c_gre_contact%FOUND then
403 result := 'COMPLETE:N';
404 else
405 result := 'COMPLETE:Y';
406 end if;
407
408 close c_gre_contact;
409
410 else -- some other source we don't understand yet
411 result := 'ERROR:UNKNOWN_CONTACT_SOURCE';
412 end if;
413
414
415 elsif ( funcmode = 'CANCEL' ) then
416 null;
417
418 end if;
419
420 hr_utility.set_location('Leaving: ' || l_proc, 100);
421 end check_final_notifier;
422
423
424 PROCEDURE get_next_notifier(itemtype in varchar2
425 ,itemkey in varchar2
426 ,actid in number
427 ,funcmode in varchar2
428 ,result out NOCOPY varchar2
429 )
430 /******************************************************************
431 **
432 ** Description:
433 ** Gets the next payroll rep who needs to be notified and sets
434 ** the forward from/to item attributes as proper.
435 **
436 **
437 ** Access Status:
438 ** Public
439 **
440 ******************************************************************/
441 IS
442
443 l_proc varchar2(61) := gv_package||'get_next_payroll_notifer';
444 ln_transaction_id pay_stat_trans_audit.stat_trans_audit_id%TYPE;
445 ln_current_assignment_id per_assignments_f.assignment_id%TYPE;
446 lv_contact_source VARCHAR2(50);
447 lv_curr_contact_user VARCHAR2(100);
448 lv_next_contact_user VARCHAR2(100);
449 lv_dummy VARCHAR2(100);
450 ln_curr_contact_person_id per_people_f.person_id%TYPE;
451 ln_next_contact_person_id per_people_f.person_id%TYPE;
452
453 CURSOR c_payroll_contact IS
454 select /*+ ordered */ usr.employee_id --Bug 3361934
455 ,pta.assignment_id
456 from pay_stat_trans_audit pta
457 ,per_assignments_f paf
458 ,pay_payrolls_f prl
459 ,fnd_user usr
460 where prl.payroll_id = paf.payroll_id
461 and usr.user_name = prl.prl_information1
462 and prl.prl_information_category = 'US'
463 and paf.assignment_id = pta.assignment_id
464 and pta.transaction_parent_id = ln_transaction_id
465 and pta.assignment_id > ln_current_assignment_id
466 and pta.transaction_effective_date between prl.effective_start_date and
467 prl.effective_end_date
468 and pta.transaction_effective_date between paf.effective_start_date and
469 paf.effective_end_date
470 order by pta.assignment_id asc;
471
472 CURSOR c_gre_contact IS
473 select /*+ ordered */ usr.employee_id --Bug 3361934
474 ,pta.assignment_id
475 from pay_stat_trans_audit pta
476 ,per_assignments_f paf
477 ,hr_soft_coding_keyflex hsc
478 ,hr_organization_information org
479 ,fnd_user usr
480 where org.organization_id = hsc.segment1
481 and usr.user_name = org.org_information1
482 and hsc.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
483 and paf.assignment_id = pta.assignment_id
484 and pta.transaction_parent_id = ln_transaction_id
485 and org.org_information_context = 'Contact Information'
486 and pta.assignment_id > ln_current_assignment_id
487 and pta.transaction_effective_date between paf.effective_start_date and
488 paf.effective_end_date
489 order by pta.assignment_id asc;
490
491 begin
492
493 hr_utility.set_location('Entering: ' || l_proc || ':'|| funcmode,5);
494 if ( funcmode = 'RUN' ) then
495 lv_contact_source := fnd_profile.value('HR_PAYROLL_CONTACT_SOURCE');
496
497 ln_transaction_id := wf_engine.GetItemAttrNumber
498 (itemtype => itemtype
499 ,itemkey => itemkey
500 ,aname => 'TRANSACTION_ID'
501 );
502
503 ln_current_assignment_id := wf_engine.GetItemAttrNumber
504 (itemtype => itemtype
505 ,itemkey => itemkey
506 ,aname => 'CURRENT_ASSIGNMENT_ID'
507 );
508
509 lv_curr_contact_user := wf_engine.GetItemAttrText
510 (itemtype => itemtype
514
511 ,itemkey => itemkey
512 ,aname => 'CONTACT_USERNAME'
513 );
515 ln_curr_contact_person_id := wf_engine.GetItemAttrNumber
516 (itemtype => itemtype
517 ,itemkey => itemkey
518 ,aname => 'CONTACT_PERSON_ID'
519 );
520
521 if lv_contact_source = 'CUSTOM' then
522 -- -----------------------------------------------------------------------
523 -- expose the wf control variables to the custom package
524 -- -----------------------------------------------------------------------
525 set_custom_wf_globals
526 (p_itemtype => itemtype
527 ,p_itemkey => itemkey);
528
529 -- set the next forward to
530
531 ln_next_contact_person_id :=
532 hr_approval_custom.Get_Next_Payroll_Notifier
533 (p_person_id => ln_curr_contact_person_id);
534
535 elsif lv_contact_source = 'PAYROLL' then
536 open c_payroll_contact;
537 fetch c_payroll_contact into ln_next_contact_person_id,ln_current_assignment_id;
538 close c_payroll_contact;
539
540 elsif lv_contact_source = 'GRE' then
541 open c_gre_contact;
542 fetch c_gre_contact into ln_next_contact_person_id,ln_current_assignment_id;
543 close c_gre_contact;
544
545 else
546 result := 'ERROR:UNKNOWN_CONTACT_SOURCE';
547 return;
548 end if;
549
550 if ( ln_next_contact_person_id is null ) then
551 result := 'COMPLETE:F';
552
553 else
554 wf_directory.GetUserName
555 (p_orig_system => 'PER'
556 ,p_orig_system_id => ln_next_contact_person_id
557 ,p_name => lv_next_contact_user
558 ,p_display_name => lv_dummy);
559
560 wf_engine.SetItemAttrNumber
561 (itemtype => itemtype
562 ,itemkey => itemkey
563 ,aname => 'CONTACT_PERSON_ID'
564 ,avalue => ln_next_contact_person_id);
565
566 wf_engine.SetItemAttrNumber
567 (itemtype => itemtype
568 ,itemkey => itemkey
569 ,aname => 'CURRENT_ASSIGNMENT_ID'
570 ,avalue => ln_current_assignment_id);
571
572 wf_engine.SetItemAttrText
573 (itemtype => itemtype
574 ,itemkey => itemkey
575 ,aname => 'CONTACT_USERNAME'
576 ,avalue => lv_next_contact_user);
577
578 hr_utility.set_location('Leaving: ' || l_proc,100);
579 result := 'COMPLETE:T';
580
581 end if;
582
583 elsif ( funcmode = 'CANCEL' ) then
584 null;
585
586 end if;
587 --
588 end get_next_notifier;
589
590
591 PROCEDURE check_for_notification(itemtype in varchar2
592 ,itemkey in varchar2
593 ,actid in number
594 ,funcmode in varchar2
595 ,result out NOCOPY varchar2
596 )
597 /******************************************************************
598 **
599 ** Description:
600 ** Checks the submitted form for conditions that require
601 ** notification to be sent to a Payroll manager.
602 **
603 ** Access Status:
604 ** Public
605 **
606 ******************************************************************/
607 IS
608 ln_transaction_id NUMBER(15);
609
610 CURSOR c_transaction is
611 select transaction_parent_id
612 ,transaction_type
613 ,transaction_subtype
614 ,source1
615 ,source1_type
616 ,audit_information2
617 ,audit_information4
618 from pay_stat_trans_audit
619 where stat_trans_audit_id = ln_transaction_id;
620
621 CURSOR c_fed_allowance_limit is
622 select fed_information1
623 from pay_us_federal_tax_info_f
624 where fed_information_category = 'ALLOWANCES LIMIT'
625 and trunc(sysdate) between effective_start_date and effective_end_date;
626
627
628 lv_exception_reason VARCHAR2(10000);
629 lr_trans_rec c_transaction%ROWTYPE;
630 lr_fed_rec c_fed_allowance_limit%ROWTYPE;
631 l_proc VARCHAR2(80) := gv_package || '.check_for_notification';
632
633 BEGIN
634 hr_utility.set_location('Entering: ' || l_proc || ':' || funcmode,5);
635 if (funcmode = 'RUN') then
636 ln_transaction_id := wf_engine.GetItemAttrNumber(itemtype => itemtype
637 ,itemkey => itemkey
638 ,aname => 'TRANSACTION_ID');
639 open c_transaction;
640 fetch c_transaction into lr_trans_rec;
641 close c_transaction;
642
643 if lr_trans_rec.transaction_subtype = 'W4' then
644 if lr_trans_rec.source1 = '00-000-0000' then
645
646 /* We check for two conditions for the federal W4:
647 * First we check to see if the allowance is over the allowance limit.
648 * Next, we check to see if FIT exempt = Y
649 */
650
651 open c_fed_allowance_limit;
652 fetch c_fed_allowance_limit into lr_fed_rec;
653 close c_fed_allowance_limit;
654
655 if (lr_fed_rec.fed_information1 is not null) and
656 (to_number(lr_trans_rec.audit_information2) >
657 to_number(lr_fed_rec.fed_information1)) then
658 hr_utility.set_message(801,'PAY_US_OTF_W4_FED_OVERALLOW');
659
660 wf_engine.SetItemAttrText(itemtype => itemtype
661 ,itemkey => itemkey
662 ,aname => 'EXCEPTION_REASON'
663 ,avalue => hr_utility.get_message);
664 result := 'COMPLETE:Y';
665
666 elsif lr_trans_rec.audit_information4 = 'Y' then
667 hr_utility.set_message(801,'PAY_US_OTF_FED_EXEMPTWARN');
668
672 ,avalue => hr_utility.get_message);
669 wf_engine.SetItemAttrText(itemtype => itemtype
670 ,itemkey => itemkey
671 ,aname => 'EXCEPTION_REASON'
673 result := 'COMPLETE:Y';
674 else
675 result := 'COMPLETE:N';
676 end if;
677 else
678 result := 'COMPLETE:N';
679 end if;
680 else
681 result := 'COMPLETE:N';
682 end if;
683 end if;
684 hr_utility.set_location('Leaving: ' || l_proc || ':' || result,100);
685
686 end check_for_notification;
687
688 END pay_us_tax_wf;