[Home] [Help]
PACKAGE BODY: APPS.PQH_FR_WF_NTF
Source
1 PACKAGE BODY PQH_FR_WF_NTF as
2 /* $Header: pqfrpswf.pkb 115.6 2004/04/28 02:39:16 svorugan noship $ */
3 g_package varchar2(60) := 'PQH_FR_WF_NTF' ;
4 function get_person_name(p_person_id in varchar2) return varchar2 is
5 l_person_name per_all_people_f.full_name%TYPE;
6 cursor csr_person_name IS
7 SELECT full_name
8 FROM per_all_people_f
9 WHERE person_id = p_person_id
10 ORDER BY effective_start_date DESC;
11 begin
12
13 OPEN csr_person_name;
14 FETCH csr_person_name INTO l_person_name;
15 CLOSE csr_person_name;
16 IF l_person_name IS NULL THEN
17 hr_utility.set_location('employee search failed',10);
18 END IF;
19 return l_person_name;
20 end get_person_name;
21
22 function get_user_role(p_validation_id in number,
23 p_person_id in number,
24 p_role_name in varchar2,
25 p_role_id in number,
26 p_user_name in varchar2) return varchar2
27 IS
28 l_user_role Varchar2(200);
29 cursor csr_last_event(p_valid_id number)
30 IS
31 SELECT event_code
32 FROM pqh_fr_validation_events
33 WHERE validation_id = p_valid_id
34 ORDER BY creation_date DESC;
35 cursor csr_emp_user IS
36 SELECT user_name
37 FROM fnd_user
38 WHERE employee_id = p_person_id;
39 l_event_code Varchar2(30);
40 /* Bug Number: 3539224; added l_role_name */
41 l_role_name Varchar2(30) := 'PQH_ROLE:'||p_role_id;
42
43 BEGIN
44
45 If (p_role_name is null) Then
46 --
47 l_role_name := p_role_name;
48 --
49 End if;
50
51 OPEN csr_last_event(p_validation_id);
52 FETCH csr_last_event into l_event_code;
53 CLOSE csr_last_event;
54 IF l_event_code IN ('200','300','400','800') THEN
55 OPEN csr_emp_user;
56 FETCH csr_emp_user INTO l_user_role;
57 CLOSE csr_emp_user;
58 ELSE
59 l_user_role := NVL(l_role_name,NVL(p_user_name,l_role_name));
60 END IF;
61 RETURN l_user_role;
62 END get_user_role;
63
64
65 PROCEDURE psv_ntf_api(
66 p_validation_id in number
67 , p_person_id in number
68 , p_role_name in varchar2
69 , p_role_id in number
70 , p_user_name in varchar2
71 , p_user_id in number
72 , p_comments in varchar2
73 , p_param1_name in varchar2
74 , p_param1_value in varchar2
75 , p_param2_name in varchar2
76 , p_param2_value in varchar2
77 , p_param3_name in varchar2
78 , p_param3_value in varchar2
79 , p_param4_name in varchar2
80 , p_param4_value in varchar2
81 , p_param5_name in varchar2
82 , p_param5_value in varchar2
83 , p_param6_name in varchar2
84 , p_param6_value in varchar2
85 , p_param7_name in varchar2
86 , p_param7_value in varchar2
87 , p_param8_name in varchar2
88 , p_param8_value in varchar2
89 , p_param9_name in varchar2
90 , p_param9_value in varchar2
91 , p_param10_name in varchar2
92 , p_param10_value in varchar2
93 )
94 IS
95 l_proc varchar2(61) := g_package||':'||'psv_ntf_api';
96 l_itemkey number;
97 l_itemtype varchar2(60) := 'PQHGEN';
98 l_process_name varchar2(60) := 'PQHFRNTF';
99 l_message_name varchar2(100);
100 l_person_name per_all_people_f.full_name%TYPE;
101 l_url Varchar2(2000);
102 l_user_role Varchar2(200);
103 Begin
104 hr_utility.set_location(l_proc || ' Entering',10);
105 hr_utility.set_location(l_proc || ' Params - l_itemtype '|| l_itemtype,15);
106 hr_utility.set_location(l_proc || ' Params - l_process name '|| l_process_name,15);
107 select PQH_FR_WF_NOTIFICATION_S.NEXTVAL into l_itemkey from dual;
108 hr_utility.set_location(l_proc || ' Params - l_itemkey '|| l_itemkey,35);
109
110 wf_engine.createProcess( ItemType => l_itemtype,
111 ItemKey => l_ItemKey,
112 process => l_process_name );
113
114 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
115 , itemkey => l_itemkey
116 , aname => 'VALIDATION_ID'
117 , avalue => p_validation_id);
118
119 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
120 , itemkey => l_itemkey
121 , aname => 'PERSON_ID'
122 , avalue => p_person_id);
123
124 l_person_name := get_person_name(p_person_id);
125
126 wf_engine.SetItemAttrText( itemtype => l_itemtype
127 , itemkey => l_itemkey
128 , aname => 'PSV_PERSON_NAME'
129 , avalue => l_person_name);
130
131 l_user_role := get_user_role(p_validation_id =>p_validation_id,
132 p_person_id => p_person_id,
133 p_role_name => p_role_name,
134 p_role_id => p_role_id,
135 p_user_name => p_user_name);
136 IF l_user_role IS NULL THEN
137 hr_utility.set_location(l_proc || 'User /Role Not Entered for PSV',40);
138 RETURN;
139 END IF;
140 hr_utility.set_location(l_proc || 'User /Role '||l_user_role,40);
141 wf_engine.SetItemAttrText( itemtype => l_itemtype
142 , itemkey => l_itemkey
143 , aname => 'PSV_USER_ROLE'
144 , avalue => l_user_role);
145
146 wf_engine.SetItemAttrText( itemtype => l_itemtype
147 , itemkey => l_itemkey
148 , aname => 'COMMENTS'
149 , avalue => p_comments);
150
151 wf_engine.SetItemAttrText( itemtype => l_itemtype
152 , itemkey => l_itemkey
153 , aname => 'ROUTED_BY_USER'
154 , avalue => fnd_global.user_name);
155
156 /* begin ns - 26-mar-2004: This is no longer needed as the url for embedded region is
157 specified in the the message attribute itself.
158 l_url := 'JSP:/'||'OA_HTML'||'/'||'OA.jsp?akRegionCode=FR_PQH_PSV_VIEW_TOP'||'&'||'akRegionApplicationId=8302'||'&'||'pPersonId='||p_person_id||'&'||'pValidationId='||p_validation_id||'&'||'pNotification=Y';
159
160 wf_engine.SetItemAttrText( itemtype => l_itemtype
161 , itemkey => l_itemkey
162 , aname => 'PSV_URL'
163 , avalue => l_url);
164
165 end ns */
166
167 -- Setting the parameter name and values only if they are not null
168 -- for performance reasons
169
170 if ( p_param1_name is not null) then
171 wf_engine.SetItemAttrText( itemtype => l_itemtype
172 , itemkey => l_itemkey
173 , aname => 'PARAMETER1_NAME'
174 , avalue => p_param1_name);
175 end if;
176
177 if ( p_param2_name is not null) then
178 wf_engine.SetItemAttrText( itemtype => l_itemtype
179 , itemkey => l_itemkey
180 , aname => 'PARAMETER2_NAME'
181 , avalue => p_param2_name);
182 end if;
183
184 if ( p_param3_name is not null) then
185 wf_engine.SetItemAttrText( itemtype => l_itemtype
186 , itemkey => l_itemkey
187 , aname => 'PARAMETER3_NAME'
188 , avalue => p_param3_name);
189 end if;
190
191 if ( p_param4_name is not null) then
192 wf_engine.SetItemAttrText( itemtype => l_itemtype
193 , itemkey => l_itemkey
194 , aname => 'PARAMETER4_NAME'
195 , avalue => p_param4_name);
196 end if;
197
198 if ( p_param5_name is not null) then
199 wf_engine.SetItemAttrText( itemtype => l_itemtype
200 , itemkey => l_itemkey
201 , aname => 'PARAMETER5_NAME'
202 , avalue => p_param5_name);
203 end if;
204
205 if ( p_param6_name is not null) then
206 wf_engine.SetItemAttrText( itemtype => l_itemtype
207 , itemkey => l_itemkey
208 , aname => 'PARAMETER6_NAME'
209 , avalue => p_param6_name);
210 end if;
211
212 if ( p_param7_name is not null) then
213 wf_engine.SetItemAttrText( itemtype => l_itemtype
214 , itemkey => l_itemkey
215 , aname => 'PARAMETER7_NAME'
216 , avalue => p_param7_name);
217 end if;
218
219 if ( p_param8_name is not null) then
220 wf_engine.SetItemAttrText( itemtype => l_itemtype
221 , itemkey => l_itemkey
222 , aname => 'PARAMETER8_NAME'
223 , avalue => p_param8_name);
224 end if;
225
226 if ( p_param9_name is not null) then
227 wf_engine.SetItemAttrText( itemtype => l_itemtype
228 , itemkey => l_itemkey
229 , aname => 'PARAMETER9_NAME'
230 , avalue => p_param9_name);
231 end if;
232
233 if ( p_param10_name is not null) then
234 wf_engine.SetItemAttrText( itemtype => l_itemtype
235 , itemkey => l_itemkey
236 , aname => 'PARAMETER10_NAME'
237 , avalue => p_param10_name);
238 end if;
239
240
241 if ( p_param1_value is not null) then
242 wf_engine.SetItemAttrText( itemtype => l_itemtype
243 , itemkey => l_itemkey
244 , aname => 'PARAMETER1_VALUE'
245 , avalue => p_param1_value);
246
247 end if;
248
249 if ( p_param2_value is not null) then
250 wf_engine.SetItemAttrText( itemtype => l_itemtype
251 , itemkey => l_itemkey
252 , aname => 'PARAMETER2_VALUE'
253 , avalue => p_param2_value);
254 end if;
255
256 if ( p_param3_value is not null) then
257 wf_engine.SetItemAttrText( itemtype => l_itemtype
258 , itemkey => l_itemkey
259 , aname => 'PARAMETER3_VALUE'
260 , avalue => p_param3_value);
261 end if;
262
263 if ( p_param4_value is not null) then
264 wf_engine.SetItemAttrText( itemtype => l_itemtype
265 , itemkey => l_itemkey
266 , aname => 'PARAMETER4_VALUE'
267 , avalue => p_param4_value);
268 end if;
269
270 if ( p_param5_value is not null) then
271 wf_engine.SetItemAttrText( itemtype => l_itemtype
272 , itemkey => l_itemkey
273 , aname => 'PARAMETER5_VALUE'
274 , avalue => p_param5_value);
275 end if;
276
277 if ( p_param6_value is not null) then
278 wf_engine.SetItemAttrText( itemtype => l_itemtype
279 , itemkey => l_itemkey
280 , aname => 'PARAMETER6_VALUE'
281 , avalue => p_param6_value);
282 end if;
283
284 if ( p_param7_value is not null) then
285 wf_engine.SetItemAttrText( itemtype => l_itemtype
286 , itemkey => l_itemkey
287 , aname => 'PARAMETER7_VALUE'
288 , avalue => p_param7_value);
289 end if;
290
291 if ( p_param8_value is not null) then
292 wf_engine.SetItemAttrText( itemtype => l_itemtype
293 , itemkey => l_itemkey
294 , aname => 'PARAMETER8_VALUE'
295 , avalue => p_param8_value);
296 end if;
297
298 if ( p_param9_value is not null) then
299 wf_engine.SetItemAttrText( itemtype => l_itemtype
300 , itemkey => l_itemkey
301 , aname => 'PARAMETER9_VALUE'
302 , avalue => p_param9_value);
303 end if;
304
305 if ( p_param10_value is not null) then
306 wf_engine.SetItemAttrText( itemtype => l_itemtype
307 , itemkey => l_itemkey
308 , aname => 'PARAMETER10_VALUE'
309 , avalue => p_param10_value);
310 end if;
311
312 hr_utility.set_location(l_proc || ' Start Process',15);
313
314 wf_engine.StartProcess ( ItemType => l_itemtype,
315 ItemKey => l_ItemKey );
316 hr_utility.set_location(l_proc || ' Exiting ',100);
317 End psv_ntf_api;
318
319 PROCEDURE WHICH_MESSAGE (
320 itemtype in varchar2
321 , itemkey in varchar2
322 , actid in number
323 , funcmode in varchar2
324 , result out nocopy varchar2)
325 IS
326 l_proc varchar2(61) := g_package||':'||'psv_ntf_api';
327 cursor csr_last_event(p_valid_id number)
328 IS
329 SELECT event_code
330 FROM pqh_fr_validation_events
331 WHERE validation_id = p_valid_id
332 ORDER BY creation_date DESC;
333 l_event_code pqh_fr_validation_events.event_code%TYPE;
334 l_validation_id Number;
335 BEGIN
336 l_validation_id := wf_engine.GetItemAttrText(
337 itemtype => itemtype,
338 itemkey => ItemKey,
339 aname => 'VALIDATION_ID');
340
341 OPEN csr_last_event(l_validation_id);
342 FETCH csr_last_event INTO l_event_code;
343 CLOSE csr_last_event;
344 IF l_event_code = '200' THEN
345 result := 'RECEIVE_BSCT';
346 ELSIF l_event_code = '300' THEN
347 result := 'RECEIVE_REQUEST';
348 ELSIF l_event_code = '400' THEN
349 result := 'RECEIVE_FILE_REQUEST';
350 ELSIF l_event_code = '800' THEN
351 result := 'SEND_QUOTE';
352 ELSIF l_event_code = '1100' THEN
353 result := 'DEDUCTION_DEFINED';
354 ELSE
355 result := 'FR_PQH_PSV_NOTIFY';
356 END IF;
357 hr_utility.set_location(l_proc || ' Exiting with result '||result,100);
358 end which_message;
359 END PQH_FR_WF_NTF;