[Home] [Help]
PACKAGE BODY: APPS.PAY_US_W2_WF
Source
1 PACKAGE BODY pay_us_w2_wf
2 /* $Header: pyusw2wf.pkb 115.5 2002/12/04 21:06:13 meshah 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_w2_wf
21
22 Description : Contains workflow code the W2 Notifications Workflow
23
24 Uses :
25
26 Change List
27 -----------
28 Date Name Vers Description
29 ---- ---- ---- -----------
30 22-MAR-2002 meshah 115.0 Created.
31 26-MAR-2002 meshah 115.1 Added dbdrv command.
32 17-MAY-2002 fusman 115.2 Added set verify off.
33 19-AUG-2002 fusman 115.3 Added whenever OS error command.
34 24-SEP-2002 fusman 115.4 Fix for Bug:2479954. Changed c_gre_contact
35 04-DEC-2002 meshah 115.5 nocopy.
36 *******************************************************************/
37 AS
38
39 /******************************************************************
40 ** private package global declarations
41 ******************************************************************/
42 gv_package VARCHAR2(50) := 'pay_us_w2_wf';
43
44
45 PROCEDURE set_custom_wf_globals(p_itemtype in varchar2
46 ,p_itemkey in varchar2)
47 /******************************************************************
48 **
49 ** Description:
50 ** initializes package variables of the custom approval package
51 **
52 ** Access Status:
53 ** Private
54 **
55 ******************************************************************/
56 IS
57
58 BEGIN
59 hr_approval_custom.g_itemtype := p_itemtype;
60 hr_approval_custom.g_itemkey := p_itemkey;
61
62 end set_custom_wf_globals;
63
64
65 PROCEDURE get_w2_notifier(itemtype in varchar2,
66 itemkey in varchar2,
67 actid in number,
68 funcmode in varchar2,
69 result out nocopy varchar2
70 )
71 /******************************************************************
72 **
73 ** Description:
74 ** Gets the next payroll rep who needs to be notified and sets
75 ** the forward from/to item attributes as proper.
76 **
77 **
78 ** Access Status:
79 ** Public
80 **
81 ******************************************************************/
82 IS
83
84 l_proc varchar2(61) := gv_package||'get_w2_notifier';
85 ln_current_assignment_id per_assignments_f.assignment_id%TYPE;
86 lv_contact_source VARCHAR2(50);
87 lv_curr_contact_user VARCHAR2(100);
88 lv_next_contact_user VARCHAR2(100);
89 lv_dummy VARCHAR2(100);
90 ln_curr_contact_person_id per_people_f.person_id%TYPE;
91 ln_next_contact_person_id per_people_f.person_id%TYPE;
92
93 CURSOR c_payroll_contact IS
94 select usr.employee_id,
95 paf.assignment_id
96 from pay_payrolls_f prl,
97 per_assignments_f paf,
98 fnd_user usr
99 where prl.payroll_id = paf.payroll_id
100 and usr.user_name = prl.prl_information1
101 and prl.prl_information_category = 'US'
102 and paf.assignment_id = ln_current_assignment_id
103 and sysdate between prl.effective_start_date and prl.effective_end_date
104 and sysdate between paf.effective_start_date and paf.effective_end_date
105 order by paf.assignment_id asc;
106
107 CURSOR c_gre_contact IS
108 select usr.employee_id,
109 paf.assignment_id
110 from hr_organization_information org,
111 per_assignments_f paf,
112 fnd_user usr
113 where org.organization_id = (select hsc.segment1
114 from hr_soft_coding_keyflex hsc
115 where hsc.soft_coding_keyflex_id = paf.soft_coding_keyflex_id)
116 and usr.user_name = org.org_information1
117 and paf.assignment_id = ln_current_assignment_id
118 and org.org_information_context = 'Contact Information'
119 and sysdate between paf.effective_start_date and paf.effective_end_date
120 order by paf.assignment_id asc;
121
122 begin
123 --hr_utility.trace_on(null,'oracle');
124 hr_utility.set_location('Entering: ' || l_proc || ':'|| funcmode,5);
125
126 if ( funcmode = 'RUN' ) then
127
128 lv_contact_source := fnd_profile.value('HR_PAYROLL_CONTACT_SOURCE');
129
130 hr_utility.trace('Profile Option value is : '|| lv_contact_source);
131
132 ln_current_assignment_id := wf_engine.GetItemAttrNumber
133 (itemtype => itemtype
134 ,itemkey => itemkey
135 ,aname => 'CURRENT_ASSIGNMENT_ID');
136
137 hr_utility.trace('Assignment Id is : '|| to_char(ln_current_assignment_id));
138
139 lv_curr_contact_user := wf_engine.GetItemAttrText
140 (itemtype => itemtype
141 ,itemkey => itemkey
142 ,aname => 'CONTACT_USERNAME');
143
144 ln_curr_contact_person_id := wf_engine.GetItemAttrNumber
145 (itemtype => itemtype
146 ,itemkey => itemkey
147 ,aname => 'CONTACT_PERSON_ID');
148
149 if lv_contact_source = 'CUSTOM' then
150 -- -----------------------------------------------------------------------
151 -- expose the wf control variables to the custom package
152 -- -----------------------------------------------------------------------
153 set_custom_wf_globals
154 (p_itemtype => itemtype
155 ,p_itemkey => itemkey);
156
157 -- set the next forward to
158
159 ln_next_contact_person_id :=
160 hr_approval_custom.Get_Next_Payroll_Notifier
161 (p_person_id => ln_curr_contact_person_id);
162
163 elsif lv_contact_source = 'PAYROLL' then
164 open c_payroll_contact;
165 fetch c_payroll_contact into ln_next_contact_person_id,
166 ln_current_assignment_id;
167 close c_payroll_contact;
168
169 elsif lv_contact_source = 'GRE' then
170 open c_gre_contact;
171 fetch c_gre_contact into ln_next_contact_person_id,
172 ln_current_assignment_id;
173 close c_gre_contact;
174
175 else
176 hr_utility.trace('If this prints then this is bad ');
177 result := 'ERROR:UNKNOWN_CONTACT_SOURCE';
178 return;
179 end if;
180
181 hr_utility.trace('Contact is ' || to_char(ln_next_contact_person_id));
182 hr_utility.trace('Assignment is ' || to_char(ln_current_assignment_id));
183
184 if ( ln_next_contact_person_id is null ) then
185 result := 'COMPLETE:F';
186
187 else
188 wf_directory.GetUserName
189 (p_orig_system => 'PER'
190 ,p_orig_system_id => ln_next_contact_person_id
191 ,p_name => lv_next_contact_user
192 ,p_display_name => lv_dummy);
193
194 wf_engine.SetItemAttrNumber
195 (itemtype => itemtype
196 ,itemkey => itemkey
197 ,aname => 'CONTACT_PERSON_ID'
198 ,avalue => ln_next_contact_person_id);
199
200 wf_engine.SetItemAttrNumber
201 (itemtype => itemtype
202 ,itemkey => itemkey
203 ,aname => 'CURRENT_ASSIGNMENT_ID'
204 ,avalue => ln_current_assignment_id);
205
206 wf_engine.SetItemAttrText
207 (itemtype => itemtype
208 ,itemkey => itemkey
209 ,aname => 'CONTACT_USERNAME'
210 ,avalue => lv_next_contact_user);
211
212 hr_utility.set_location('Leaving: ' || l_proc,100);
213 result := 'COMPLETE:T';
214
215 end if;
216
217 elsif ( funcmode = 'CANCEL' ) then
218 null;
219
220 end if;
221 --
222 end get_w2_notifier;
223
224 END pay_us_w2_wf;