[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_RESOURCE_WF_PUB
Source
1 PACKAGE BODY JTF_RS_RESOURCE_WF_PUB AS
2 /* $Header: jtfrswrb.pls 120.0.12010000.2 2008/10/03 08:27:50 avjha ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'JTF_RS_RESOURCE_WF_PUB';
5
6 /* Procedure to start the update resource workflow */
7
8 PROCEDURE start_update_resource_wf (
9 p_api_version IN NUMBER,
10 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
11 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
12 p_resource_id IN jtf_rs_resource_extns.resource_id%type,
13 p_category IN jtf_rs_resource_extns.category%type,
14 p_resource_number IN jtf_rs_resource_extns.resource_number%type,
15 p_resource_name IN jtf_rs_resource_extns_vl.resource_name%type default fnd_api.g_miss_char,
16 p_address_id IN jtf_rs_resource_extns.address_id%type default fnd_api.g_miss_num,
17 p_source_email IN jtf_rs_resource_extns.source_email%type default fnd_api.g_miss_char,
18 p_source_phone IN jtf_rs_resource_extns.source_phone%type default fnd_api.g_miss_char,
19 p_source_office IN jtf_rs_resource_extns.source_office%type default fnd_api.g_miss_char,
20 p_source_location IN jtf_rs_resource_extns.source_location%type default fnd_api.g_miss_char,
21 p_source_mailstop IN jtf_rs_resource_extns.source_mailstop%type default fnd_api.g_miss_char,
22 p_time_zone IN jtf_rs_resource_extns.time_zone%type default fnd_api.g_miss_num,
23 p_support_site_id IN jtf_rs_resource_extns.support_site_id%type default fnd_api.g_miss_num,
24 p_primary_language IN jtf_rs_resource_extns.primary_language%type default fnd_api.g_miss_char,
25 p_secondary_language IN jtf_rs_resource_extns.secondary_language%type default fnd_api.g_miss_char,
26 p_cost_per_hr IN jtf_rs_resource_extns.cost_per_hr%type default fnd_api.g_miss_num,
27 p_attribute_access_level IN jtf_rs_table_attributes_b.attribute_access_level%type,
28 p_object_version_number IN jtf_rs_resource_extns.object_version_number%type,
29 --p_wf_display_name IN VARCHAR2 DEFAULT NULL,
30 p_wf_process IN VARCHAR2 DEFAULT 'EMP_UPDATE_PROCESS',
31 p_wf_item_type IN VARCHAR2 DEFAULT 'EMP_TYPE',
32 x_return_status OUT NOCOPY VARCHAR2,
33 x_msg_count OUT NOCOPY NUMBER,
34 x_msg_data OUT NOCOPY VARCHAR2,
35 p_source_mobile_phone IN jtf_rs_resource_extns.source_mobile_phone%type default fnd_api.g_miss_char,
36 p_source_pager IN jtf_rs_resource_extns.source_pager%type default fnd_api.g_miss_char
37 )
38
39 IS
40
41 l_api_version CONSTANT NUMBER := 1.0;
42 l_api_name CONSTANT VARCHAR2(30) := 'START_RS_RESOURCE_WF_PUB';
43 l_wf_process_id NUMBER;
44 l_itemkey wf_item_activity_statuses.item_key%TYPE;
45 l_userkey varchar2(30);
46
47 l_resource_id jtf_rs_resource_extns.resource_id%type := p_resource_id;
48 l_attr_access_level jtf_rs_table_attributes_b.attribute_access_level%type := p_attribute_access_level;
49 l_object_version_number jtf_rs_resource_extns.object_version_number%type := p_object_version_number;
50
51 l_new_address_id jtf_rs_resource_extns.address_id%type;
52 l_new_resource_name jtf_rs_resource_extns_vl.resource_name%type;
53 l_new_phone_number jtf_rs_resource_extns.source_phone%type;
54 l_new_email_address jtf_rs_resource_extns.source_email%type;
55 l_new_source_office jtf_rs_resource_extns.source_office%type;
56 l_new_source_location jtf_rs_resource_extns.source_location%type;
57 l_new_source_mailstop jtf_rs_resource_extns.source_mailstop%type;
58 l_new_source_mobile_phone jtf_rs_resource_extns.source_mobile_phone%type;
59 l_new_source_pager jtf_rs_resource_extns.source_pager%type;
60 l_new_time_zone jtf_rs_resource_extns.time_zone%type;
61 l_new_support_site_id jtf_rs_resource_extns.support_site_id%type;
62 l_new_primary_language jtf_rs_resource_extns.primary_language%type;
63 l_new_secondary_language jtf_rs_resource_extns.secondary_language%type;
64 l_new_cost_per_hr jtf_rs_resource_extns.cost_per_hr%type;
65
66 l_category jtf_rs_resource_extns.category%type;
67 l_resource_number jtf_rs_resource_extns.resource_number%type;
68 l_source_name jtf_rs_resource_extns.source_name%type;
69 l_old_resource_name jtf_rs_resource_extns_vl.resource_name%type;
70 l_old_address_id jtf_rs_resource_extns.address_id%type;
71 l_old_phone_number jtf_rs_resource_extns.source_phone%type;
72 l_old_email_address jtf_rs_resource_extns.source_email%type;
73 l_old_source_office jtf_rs_resource_extns.source_office%type;
74 l_old_source_location jtf_rs_resource_extns.source_location%type;
75 l_old_source_mailstop jtf_rs_resource_extns.source_mailstop%type;
76 l_old_source_mobile_phone jtf_rs_resource_extns.source_mobile_phone%type;
77 l_old_source_pager jtf_rs_resource_extns.source_pager%type;
78 l_old_time_zone jtf_rs_resource_extns.time_zone%type;
79 l_old_support_site_id jtf_rs_resource_extns.support_site_id%type;
80 l_old_primary_language jtf_rs_resource_extns.primary_language%type;
81 l_old_secondary_language jtf_rs_resource_extns.secondary_language%type;
82 l_old_cost_per_hr jtf_rs_resource_extns.cost_per_hr%type;
83
84 l_old_address varchar2(2000);
85 l_new_address varchar2(2000);
86
87 l_old_timezone_name varchar2(100);
88 l_new_timezone_name varchar2(100);
89
90 l_old_support_site varchar2(100);
91 l_new_support_site varchar2(100);
92
93 l_user_id jtf_rs_resource_extns.user_id%type;
94 l_source_id jtf_rs_resource_extns.source_id%type;
95 l_source_mgr_id jtf_rs_resource_extns.source_mgr_id%type;
96
97 l_source_number jtf_rs_resource_extns.source_number%type;
98
99 l_approver fnd_user.user_name%type;
100 l_requestor fnd_user.user_name%type;
101
102 l_errname varchar2(60);
103 l_errmsg varchar2(2000);
104 l_errstack varchar2(4000);
105
106 l_approved varchar2(2);
107 l_error_flag varchar2(1) := 'N';
108
109 CURSOR c_user (l_employee_id number) IS
110 SELECT user_name
111 FROM fnd_user
112 WHERE employee_id = l_employee_id
113 ORDER by creation_date desc;
114
115 CURSOR c_resource (l_resource_id number) IS
116 SELECT category, resource_number, resource_name, source_name, source_number,
117 source_phone, source_email, source_office, source_location,
118 source_mailstop, source_mobile_phone, source_pager, time_zone,
119 support_site_id, primary_language, secondary_language, cost_per_hr,
120 address_id, user_id, source_id, source_mgr_id
121 FROM jtf_rs_resource_extns_vl
122 WHERE resource_id = l_resource_id;
123
124 CURSOR c_address (l_address_id number) IS
125 SELECT description
126 FROM hr_locations
127 WHERE location_id = l_address_id;
128
129 CURSOR c_timezone (l_timezone_id number) IS
130 SELECT name
131 FROM fnd_timezones_vl
132 WHERE upgrade_tz_id = l_timezone_id;
133
134 CURSOR c_support_site (l_support_site_id number) IS
135 SELECT a.city
136 FROM hz_locations a, hz_party_site_uses c, hz_party_sites b
137 WHERE c.site_use_type = 'SUPPORT_SITE'
138 AND c.party_site_id = b.party_site_id
139 AND a.location_id = b.location_id
140 AND b.party_site_id = l_support_site_id;
141
142 CURSOR c_resource_update (l_resource_id number) IS
143 SELECT
144 DECODE (p_resource_name, fnd_api.g_miss_char, resource_name, p_resource_name) resource_name,
145 DECODE (p_source_email, fnd_api.g_miss_char, source_email, p_source_email) source_email,
146 DECODE (p_source_phone, fnd_api.g_miss_char, source_phone, p_source_phone) source_phone,
147 DECODE (p_source_office, fnd_api.g_miss_char, source_office, p_source_office) source_office,
148 DECODE (p_source_location, fnd_api.g_miss_char, source_location, p_source_location) source_location,
149 DECODE (p_source_mailstop, fnd_api.g_miss_char, source_mailstop, p_source_mailstop) source_mailstop,
150 DECODE (p_source_mobile_phone, fnd_api.g_miss_char, source_mobile_phone, p_source_mobile_phone) source_mobile_phone,
151 DECODE (p_source_pager, fnd_api.g_miss_char, source_pager, p_source_pager) source_pager,
152 DECODE (p_time_zone, fnd_api.g_miss_num, time_zone, p_time_zone) time_zone,
153 DECODE (p_support_site_id, fnd_api.g_miss_num, support_site_id, p_support_site_id) support_site_id,
154 DECODE (p_primary_language, fnd_api.g_miss_char, primary_language, p_primary_language) primary_language,
155 DECODE (p_secondary_language, fnd_api.g_miss_char, secondary_language, p_secondary_language) secondary_language,
156 DECODE (p_cost_per_hr, fnd_api.g_miss_num, cost_per_hr, p_cost_per_hr) cost_per_hr,
157 DECODE (p_address_id, fnd_api.g_miss_num, address_id, p_address_id) address_id
158 FROM jtf_rs_resource_extns_vl
159 WHERE resource_id = l_resource_id;
160
161 resource_rec c_resource_update%ROWTYPE;
162
163 BEGIN
164
165 --dbms_output.put_line ('Begin Workflow API');
166
167 SAVEPOINT start_rs_workflow;
168 x_return_status := fnd_api.g_ret_sts_success;
169
170 IF NOT fnd_api.compatible_api_call (
171 l_api_version,
172 p_api_version,
173 l_api_name,
174 g_pkg_name
175 )
176 THEN
177 RAISE fnd_api.g_exc_unexpected_error;
178 END IF;
179
180 IF fnd_api.to_boolean (p_init_msg_list)
181 THEN
182 fnd_msg_pub.initialize;
183 END IF;
184
185 IF l_attr_access_level = 'UWA' THEN
186 l_approved := 'A';
187 ELSE
188 l_approved := 'T';
189 END IF;
190
191
192 OPEN c_resource(l_resource_id);
193 FETCH c_resource INTO l_category, l_resource_number, l_old_resource_name, l_source_name,
194 l_source_number, l_old_phone_number, l_old_email_address, l_old_source_office,
195 l_old_source_location, l_old_source_mailstop, l_old_source_mobile_phone,
196 l_old_source_pager, l_old_time_zone, l_old_support_site_id, l_old_primary_language,
197 l_old_secondary_language, l_old_cost_per_hr, l_old_address_id, l_user_id,
198 l_source_id, l_source_mgr_id;
199 IF c_resource%NOTFOUND THEN
200 --dbms_output.put_line('The Resource passed is Invalid');
201 fnd_message.set_name('JTF', 'JTF_RS_INVALID_EMP_RESOURCE_ID');
202 fnd_message.set_token('P_EMP_RESOURCE_ID', l_resource_id);
203 fnd_msg_pub.add;
204 x_return_status := fnd_api.g_ret_sts_unexp_error;
205 RAISE fnd_api.g_exc_unexpected_error;
206 END IF;
207 CLOSE c_resource;
208
209 OPEN c_resource_update(l_resource_id);
210 FETCH c_resource_update INTO resource_rec;
211 IF c_resource_update%NOTFOUND THEN
212 --dbms_output.put_line('The Resource passed is Invalid');
213 fnd_message.set_name('JTF', 'JTF_RS_INVALID_EMP_RESOURCE_ID');
214 fnd_message.set_token('P_EMP_RESOURCE_ID', l_resource_id);
215 fnd_msg_pub.add;
216 x_return_status := fnd_api.g_ret_sts_unexp_error;
217 RAISE fnd_api.g_exc_unexpected_error;
218 END IF;
219 CLOSE c_resource_update;
220
221 IF l_attr_access_level = 'UWA' THEN
222 l_approved := 'A';
223 ELSE
224 l_approved := 'T';
225 END IF;
226
227 IF l_source_mgr_id is NULL THEN
228 l_error_flag := 'Y';
229 IF l_attr_access_level = 'UWA' THEN
230 l_approved := 'AE';
231 ELSE
232 l_approved := 'TE';
233 END IF;
234 ELSE
235 OPEN c_user(l_source_mgr_id);
236 FETCH c_user INTO l_approver;
237 IF c_user%NOTFOUND THEN
238 l_error_flag := 'Y';
239 IF l_attr_access_level = 'UWA' THEN
240 l_approved := 'AE';
241 ELSE
242 l_approved := 'TE';
243 END IF;
244 END IF;
245 CLOSE c_user;
246 END IF;
247
248 l_new_address_id := resource_rec.address_id;
249 l_new_resource_name := resource_rec.resource_name;
250 l_new_phone_number := resource_rec.source_phone;
251 l_new_email_address := resource_rec.source_email;
252 l_new_source_office := resource_rec.source_office;
253 l_new_source_location := resource_rec.source_location;
254 l_new_source_mailstop := resource_rec.source_mailstop;
255 l_new_source_mobile_phone := resource_rec.source_mobile_phone;
256 l_new_source_pager := resource_rec.source_pager;
257 l_new_time_zone := resource_rec.time_zone;
258 l_new_support_site_id := resource_rec.support_site_id;
259 l_new_primary_language := resource_rec.primary_language;
260 l_new_secondary_language := resource_rec.secondary_language;
261 l_new_cost_per_hr := resource_rec.cost_per_hr;
262
263 IF l_old_address_id IS NOT NULL THEN
264 OPEN c_address (l_old_address_id);
265 FETCH c_address INTO l_old_address;
266 CLOSE c_address;
267 END IF;
268
269 IF l_old_time_zone IS NOT NULL THEN
270 OPEN c_timezone (l_old_time_zone);
271 FETCH c_timezone INTO l_old_timezone_name;
272 CLOSE c_timezone;
273 END IF;
274
275 IF l_old_support_site_id IS NOT NULL THEN
276 OPEN c_support_site (l_old_support_site_id);
277 FETCH c_support_site INTO l_old_support_site;
278 CLOSE c_support_site;
279 END IF;
280
281 SELECT jtf_rs_resource_wf_s.nextval INTO l_itemkey FROM dual;
282 SELECT fnd_global.user_name INTO l_requestor FROM dual;
283
284 IF p_address_id = fnd_api.g_miss_num THEN
285 l_new_address := l_old_address;
286 ELSE
287 IF l_new_address_id IS NOT NULL THEN
288 OPEN c_address (l_new_address_id);
289 FETCH c_address INTO l_new_address;
290 CLOSE c_address;
291 END IF;
292 END IF;
293
294 IF p_time_zone = fnd_api.g_miss_num THEN
295 l_new_timezone_name := l_old_timezone_name;
296 ELSE
297 IF l_new_time_zone IS NOT NULL THEN
298 OPEN c_timezone (l_new_time_zone);
299 FETCH c_timezone INTO l_new_timezone_name;
300 CLOSE c_timezone;
301 END IF;
302 END IF;
303
304 IF p_support_site_id = fnd_api.g_miss_num THEN
305 l_new_support_site := l_old_support_site;
306 ELSE
307 IF l_new_support_site_id IS NOT NULL THEN
308 OPEN c_support_site (l_new_support_site_id);
309 FETCH c_support_site INTO l_new_support_site;
310 CLOSE c_support_site;
311 END IF;
312 END IF;
313
314 wf_engine.createprocess (
315 itemtype => p_wf_item_type,
316 itemkey => l_itemkey,
317 process => p_wf_process
318 );
319
320 wf_engine.setitemattrtext (
321 itemtype => p_wf_item_type,
322 itemkey => l_itemkey,
323 aname => 'P_APPROVED',
324 avalue => l_approved
325 );
326
327 wf_engine.setitemattrtext (
328 itemtype => p_wf_item_type,
329 itemkey => l_itemkey,
330 aname => 'ERROR_FLAG',
331 avalue => l_error_flag
332 );
333
334 wf_engine.setitemattrtext (
335 itemtype => p_wf_item_type,
336 itemkey => l_itemkey,
337 aname => 'USER',
338 avalue => l_approver
339 );
340
341 wf_engine.setitemattrtext (
342 itemtype => p_wf_item_type,
343 itemkey => l_itemkey,
344 aname => 'REQUESTOR',
345 avalue => l_requestor
346 );
347
348 wf_engine.setitemattrtext (
349 itemtype => p_wf_item_type,
350 itemkey => l_itemkey,
351 aname => 'ATTRIBUTE_ACCESS_LEVEL',
352 avalue => l_attr_access_level
353 );
354
355 wf_engine.setitemattrtext (
356 itemtype => p_wf_item_type,
357 itemkey => l_itemkey,
358 aname => 'OBJECT_VERSION_NUMBER',
359 avalue => l_object_version_number
360 );
361
362 wf_engine.setitemattrtext (
363 itemtype => p_wf_item_type,
364 itemkey => l_itemkey,
365 aname => 'CATEGORY',
366 avalue => l_category
367 );
368
369 wf_engine.setitemattrtext (
370 itemtype => p_wf_item_type,
371 itemkey => l_itemkey,
372 aname => 'ADDRESS_ID',
373 avalue => l_new_address_id
374 );
375
376 wf_engine.setitemattrtext (
377 itemtype => p_wf_item_type,
378 itemkey => l_itemkey,
379 aname => 'OLD_ADDRESS',
380 avalue => l_old_address
381 );
382
383 wf_engine.setitemattrtext (
384 itemtype => p_wf_item_type,
385 itemkey => l_itemkey,
386 aname => 'NEW_ADDRESS',
387 avalue => l_new_address
388 );
389
390 wf_engine.setitemattrtext (
391 itemtype => p_wf_item_type,
392 itemkey => l_itemkey,
393 aname => 'RESOURCE_ID',
394 avalue => l_resource_id
395 );
396
397 wf_engine.setitemattrtext (
398 itemtype => p_wf_item_type,
399 itemkey => l_itemkey,
400 aname => 'RESOURCE_NUMBER',
401 avalue => l_resource_number
402 );
403
404 wf_engine.setitemattrtext (
405 itemtype => p_wf_item_type,
406 itemkey => l_itemkey,
407 aname => 'SOURCE_NUMBER',
408 avalue => l_source_number
409 );
410
411 wf_engine.setitemattrtext (
412 itemtype => p_wf_item_type,
413 itemkey => l_itemkey,
414 aname => 'SOURCE_NAME',
415 avalue => l_source_name
416 );
417
418 wf_engine.setitemattrtext (
419 itemtype => p_wf_item_type,
420 itemkey => l_itemkey,
421 aname => 'RESOURCE_NAME',
422 avalue => l_old_resource_name
423 );
424
425 wf_engine.setitemattrtext (
426 itemtype => p_wf_item_type,
427 itemkey => l_itemkey,
428 aname => 'RESOURCE_NAME_NEW',
429 avalue => l_new_resource_name
430 );
431
432 wf_engine.setitemattrtext (
433 itemtype => p_wf_item_type,
434 itemkey => l_itemkey,
435 aname => 'PHONE_NUMBER',
436 avalue => l_old_phone_number
437 );
438
439 wf_engine.setitemattrtext (
440 itemtype => p_wf_item_type,
441 itemkey => l_itemkey,
442 aname => 'PHONE_NUMBER_NEW',
443 avalue => l_new_phone_number
444 );
445
446 wf_engine.setitemattrtext (
447 itemtype => p_wf_item_type,
448 itemkey => l_itemkey,
449 aname => 'EMAIL_ADDRESS',
450 avalue => l_old_email_address
451 );
452
453 wf_engine.setitemattrtext (
454 itemtype => p_wf_item_type,
455 itemkey => l_itemkey,
456 aname => 'EMAIL_ADDRESS_NEW',
457 avalue => l_new_email_address
458 );
459
460 wf_engine.setitemattrtext (
461 itemtype => p_wf_item_type,
462 itemkey => l_itemkey,
463 aname => 'SOURCE_OFFICE',
464 avalue => l_old_source_office
465 );
466
467 wf_engine.setitemattrtext (
468 itemtype => p_wf_item_type,
469 itemkey => l_itemkey,
470 aname => 'SOURCE_OFFICE_NEW',
471 avalue => l_new_source_office
472 );
473
474 wf_engine.setitemattrtext (
475 itemtype => p_wf_item_type,
476 itemkey => l_itemkey,
477 aname => 'SOURCE_LOCATION',
478 avalue => l_old_source_location
479 );
480
481 wf_engine.setitemattrtext (
482 itemtype => p_wf_item_type,
483 itemkey => l_itemkey,
484 aname => 'SOURCE_LOCATION_NEW',
485 avalue => l_new_source_location
486 );
487
488 wf_engine.setitemattrtext (
489 itemtype => p_wf_item_type,
490 itemkey => l_itemkey,
491 aname => 'SOURCE_MAILSTOP',
492 avalue => l_old_source_mailstop
493 );
494
495 wf_engine.setitemattrtext (
496 itemtype => p_wf_item_type,
497 itemkey => l_itemkey,
498 aname => 'SOURCE_MAILSTOP_NEW',
499 avalue => l_new_source_mailstop
500 );
501
502 wf_engine.setitemattrtext (
503 itemtype => p_wf_item_type,
504 itemkey => l_itemkey,
505 aname => 'SOURCE_MOBILE_PHONE',
506 avalue => l_old_source_mobile_phone
507 );
508
509 wf_engine.setitemattrtext (
510 itemtype => p_wf_item_type,
511 itemkey => l_itemkey,
512 aname => 'SOURCE_MOBILE_PHONE_NEW',
513 avalue => l_new_source_mobile_phone
514 );
515
516 wf_engine.setitemattrtext (
517 itemtype => p_wf_item_type,
518 itemkey => l_itemkey,
519 aname => 'SOURCE_PAGER',
520 avalue => l_old_source_pager
521 );
522
523 wf_engine.setitemattrtext (
524 itemtype => p_wf_item_type,
525 itemkey => l_itemkey,
526 aname => 'SOURCE_PAGER_NEW',
527 avalue => l_new_source_pager
528 );
529
530 wf_engine.setitemattrtext (
531 itemtype => p_wf_item_type,
532 itemkey => l_itemkey,
533 aname => 'TIME_ZONE',
534 avalue => l_new_time_zone
535 );
536
537 wf_engine.setitemattrtext (
538 itemtype => p_wf_item_type,
539 itemkey => l_itemkey,
540 aname => 'TIMEZONE_NAME',
541 avalue => l_old_timezone_name
542 );
543
544 wf_engine.setitemattrtext (
545 itemtype => p_wf_item_type,
546 itemkey => l_itemkey,
547 aname => 'TIMEZONE_NAME_NEW',
548 avalue => l_new_timezone_name
549 );
550
551 wf_engine.setitemattrtext (
552 itemtype => p_wf_item_type,
553 itemkey => l_itemkey,
554 aname => 'SUPPORT_SITE_ID',
555 avalue => l_new_support_site_id
556 );
557
558 wf_engine.setitemattrtext (
559 itemtype => p_wf_item_type,
560 itemkey => l_itemkey,
561 aname => 'SUPPORT_SITE',
562 avalue => l_old_support_site
563 );
564
565 wf_engine.setitemattrtext (
566 itemtype => p_wf_item_type,
567 itemkey => l_itemkey,
568 aname => 'SUPPORT_SITE_NEW',
569 avalue => l_new_support_site
570 );
571
572 wf_engine.setitemattrtext (
573 itemtype => p_wf_item_type,
574 itemkey => l_itemkey,
575 aname => 'PRIMARY_LANGUAGE',
576 avalue => l_old_primary_language
577 );
578
579 wf_engine.setitemattrtext (
580 itemtype => p_wf_item_type,
581 itemkey => l_itemkey,
582 aname => 'PRIMARY_LANGUAGE_NEW',
583 avalue => l_new_primary_language
584 );
585
586 wf_engine.setitemattrtext (
587 itemtype => p_wf_item_type,
588 itemkey => l_itemkey,
589 aname => 'SECONDARY_LANGUAGE',
590 avalue => l_old_secondary_language
591 );
592
593 wf_engine.setitemattrtext (
594 itemtype => p_wf_item_type,
595 itemkey => l_itemkey,
596 aname => 'SECONDARY_LANGUAGE_NEW',
597 avalue => l_new_secondary_language
598 );
599
600 wf_engine.setitemattrtext (
601 itemtype => p_wf_item_type,
602 itemkey => l_itemkey,
603 aname => 'COST_PER_HR',
604 avalue => l_old_cost_per_hr
605 );
606
607 wf_engine.setitemattrtext (
608 itemtype => p_wf_item_type,
609 itemkey => l_itemkey,
610 aname => 'COST_PER_HR_NEW',
611 avalue => l_new_cost_per_hr
612 );
613
614 wf_engine.startprocess (
615 itemtype => p_wf_item_type,
616 itemkey => l_itemkey
617 );
618
619 IF fnd_api.to_boolean (p_commit)
620 THEN
621 COMMIT WORK;
622 END IF;
623
624 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
625
626 EXCEPTION
627 WHEN fnd_api.g_exc_unexpected_error
628 THEN
629
630 ROLLBACK TO start_rs_workflow;
631 x_return_status := fnd_api.g_ret_sts_unexp_error;
632 fnd_msg_pub.count_and_get (
633 p_count => x_msg_count,
634 p_data => x_msg_data
635 );
636 WHEN OTHERS
637 THEN
638 ROLLBACK TO start_rs_workflow ;
639
640 wf_core.get_error(l_errname, l_errmsg, l_errstack);
641
642 if (l_errname is not null) then
643 fnd_message.set_name('FND', 'WF_ERROR');
644 fnd_message.set_token('ERROR_MESSAGE', l_errmsg);
645 fnd_message.set_token('ERROR_STACK', l_errstack);
646 fnd_msg_pub.add;
647 end if;
648
649 x_return_status := fnd_api.g_ret_sts_unexp_error;
650 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
651
652 END start_update_resource_wf;
653
654 PROCEDURE check_error_flag (
655 itemtype IN VARCHAR2,
656 itemkey IN VARCHAR2,
657 actid IN NUMBER,
658 funcmode IN VARCHAR2,
659 resultout OUT NOCOPY VARCHAR2
660 )
661 IS
662
663 l_resultout VARCHAR2(200);
664
665 BEGIN
666 --
667 -- RUN mode - normal process execution
668 --
669 IF (funcmode = 'RUN')
670 THEN
671 --
672 -- Return process to run
673 --
674 l_resultout :=
675 wf_engine.getitemattrtext (
676 itemtype => itemtype,
677 itemkey => itemkey,
678 aname => 'ERROR_FLAG'
679 );
680 resultout := 'COMPLETE:' || l_resultout;
681
682 RETURN;
683 END IF;
684
685 --
686 -- CANCEL mode - activity 'compensation'
687 --
688 IF (funcmode = 'CANCEL')
689 THEN
690 --
691 -- Return process to run
692 --
693 resultout := 'COMPLETE';
694 RETURN;
695 END IF;
696
697 --
698 -- TIMEOUT mode
699 --
700 IF (funcmode = 'TIMEOUT')
701 THEN
702 resultout := 'COMPLETE';
703 RETURN;
704 END IF;
705 --
706 EXCEPTION
707 WHEN OTHERS
708 THEN
709 wf_core.context (
710 'EMP_TYPE',
711 'Check Attribute Access Level',
712 itemtype,
713 itemkey,
714 actid,
715 funcmode
716 );
717 RAISE;
718
719 END check_error_flag;
720
721
722 PROCEDURE check_attr_access_level (
723 itemtype IN VARCHAR2,
724 itemkey IN VARCHAR2,
725 actid IN NUMBER,
726 funcmode IN VARCHAR2,
727 resultout OUT NOCOPY VARCHAR2
728 )
729 IS
730
731 l_resultout VARCHAR2(200);
732
733 BEGIN
734 --
735 -- RUN mode - normal process execution
736 --
737 IF (funcmode = 'RUN')
738 THEN
739 --
740 -- Return process to run
741 --
742 l_resultout :=
743 wf_engine.getitemattrtext (
744 itemtype => itemtype,
745 itemkey => itemkey,
746 aname => 'ATTRIBUTE_ACCESS_LEVEL'
747 );
748 resultout := 'COMPLETE:' || l_resultout;
749
750 RETURN;
751 END IF;
752
753 --
754 -- CANCEL mode - activity 'compensation'
755 --
756 IF (funcmode = 'CANCEL')
757 THEN
758 --
759 -- Return process to run
760 --
761 resultout := 'COMPLETE';
762 RETURN;
763 END IF;
764
765 --
766 -- TIMEOUT mode
767 --
768 IF (funcmode = 'TIMEOUT')
769 THEN
770 resultout := 'COMPLETE';
771 RETURN;
772 END IF;
773 --
774 EXCEPTION
775 WHEN OTHERS
776 THEN
777 wf_core.context (
778 'EMP_TYPE',
779 'Check Attribute Access Level',
780 itemtype,
781 itemkey,
782 actid,
783 funcmode
784 );
785 RAISE;
786
787 END check_attr_access_level;
788
789 PROCEDURE call_update_resource_api (
790 itemtype IN VARCHAR2,
791 itemkey IN VARCHAR2,
792 actid IN NUMBER,
793 funcmode IN VARCHAR2,
794 resultout OUT NOCOPY VARCHAR2
795 )
796 IS
797
798 l_resource_id jtf_rs_resource_extns.resource_id%type;
799 l_address_id jtf_rs_resource_extns.address_id%type;
800 l_resource_number jtf_rs_resource_extns.resource_number%type;
801 l_resource_name jtf_rs_resource_extns_vl.resource_name%type;
802 l_source_name jtf_rs_resource_extns.source_name%type;
803 l_source_phone jtf_rs_resource_extns.source_phone%type;
804 l_source_email jtf_rs_resource_extns.source_email%type;
805 l_source_office jtf_rs_resource_extns.source_office%type;
806 l_source_location jtf_rs_resource_extns.source_location%type;
807 l_source_mailstop jtf_rs_resource_extns.source_mailstop%type;
808 l_source_mobile_phone jtf_rs_resource_extns.source_mobile_phone%type;
809 l_source_pager jtf_rs_resource_extns.source_pager%type;
810 l_time_zone jtf_rs_resource_extns.time_zone%type;
811 l_support_site_id jtf_rs_resource_extns.support_site_id%type;
812 l_primary_language jtf_rs_resource_extns.primary_language%type;
813 l_secondary_language jtf_rs_resource_extns.secondary_language%type;
814 l_cost_per_hr jtf_rs_resource_extns.cost_per_hr%type;
815
816 l_object_version_number jtf_rs_resource_extns.object_version_number%type;
817 x_msg_count number;
818 x_msg_data varchar2(2000);
819 x_resultout varchar2(200);
820 x_return_status varchar2(1);
821
822 l_msg_data varchar2(2000);
823 l_msg_data1 varchar2(2000);
824 l_msg_index_out varchar2(2000);
825
826 l_approved varchar2(2);
827
828 BEGIN
829
830 --
831 -- RUN mode - normal process execution
832 --
833 IF (funcmode = 'RUN')
834 THEN
835 --
836 -- Return process to run
837 --
838
839 l_resource_id :=
840 wf_engine.getitemattrtext (
841 itemtype => itemtype,
842 itemkey => itemkey,
843 aname => 'RESOURCE_ID'
844 );
845
846 l_address_id :=
847 wf_engine.getitemattrtext (
848 itemtype => itemtype,
849 itemkey => itemkey,
850 aname => 'ADDRESS_ID'
851 );
852 l_resource_number :=
853 wf_engine.getitemattrtext (
854 itemtype => itemtype,
855 itemkey => itemkey,
856 aname => 'RESOURCE_NUMBER'
857 );
858 l_source_name :=
859 wf_engine.getitemattrtext (
860 itemtype => itemtype,
861 itemkey => itemkey,
862 aname => 'SOURCE_NAME'
863 );
864 l_approved :=
865 wf_engine.getitemattrtext (
866 itemtype => itemtype,
867 itemkey => itemkey,
868 aname => 'P_APPROVED'
869 );
870 l_resource_name :=
871 wf_engine.getitemattrtext (
872 itemtype => itemtype,
873 itemkey => itemkey,
874 aname => 'RESOURCE_NAME_NEW'
875 );
876 l_source_phone :=
877 wf_engine.getitemattrtext (
878 itemtype => itemtype,
879 itemkey => itemkey,
880 aname => 'PHONE_NUMBER_NEW'
881 );
882 l_source_email :=
883 wf_engine.getitemattrtext (
884 itemtype => itemtype,
885 itemkey => itemkey,
886 aname => 'EMAIL_ADDRESS_NEW'
887 );
888 l_source_location :=
889 wf_engine.getitemattrtext (
890 itemtype => itemtype,
891 itemkey => itemkey,
892 aname => 'SOURCE_LOCATION_NEW'
893 );
894 l_source_office :=
895 wf_engine.getitemattrtext (
896 itemtype => itemtype,
897 itemkey => itemkey,
898 aname => 'SOURCE_OFFICE_NEW'
899 );
900 l_source_mailstop :=
901 wf_engine.getitemattrtext (
902 itemtype => itemtype,
903 itemkey => itemkey,
904 aname => 'SOURCE_MAILSTOP_NEW'
905 );
906 l_source_mobile_phone :=
907 wf_engine.getitemattrtext (
908 itemtype => itemtype,
909 itemkey => itemkey,
910 aname => 'SOURCE_MOBILE_PHONE_NEW'
911 );
912 l_source_pager :=
913 wf_engine.getitemattrtext (
914 itemtype => itemtype,
915 itemkey => itemkey,
916 aname => 'SOURCE_PAGER_NEW'
917 );
918 l_time_zone :=
919 wf_engine.getitemattrtext (
920 itemtype => itemtype,
921 itemkey => itemkey,
922 aname => 'TIME_ZONE'
923 );
924 l_support_site_id :=
925 wf_engine.getitemattrtext (
926 itemtype => itemtype,
927 itemkey => itemkey,
928 aname => 'SUPPORT_SITE_ID'
929 );
930 l_primary_language :=
931 wf_engine.getitemattrtext (
932 itemtype => itemtype,
933 itemkey => itemkey,
934 aname => 'PRIMARY_LANGUAGE_NEW'
935 );
936 l_secondary_language :=
937 wf_engine.getitemattrtext (
938 itemtype => itemtype,
939 itemkey => itemkey,
940 aname => 'SECONDARY_LANGUAGE_NEW'
941 );
942 l_cost_per_hr :=
943 wf_engine.getitemattrtext (
944 itemtype => itemtype,
945 itemkey => itemkey,
946 aname => 'COST_PER_HR_NEW'
947 );
948 l_object_version_number :=
949 wf_engine.getitemattrtext (
950 itemtype => itemtype,
951 itemkey => itemkey,
952 aname => 'OBJECT_VERSION_NUMBER'
953 );
954
955 jtf_rs_res_sswa_pub.update_resource (
956 P_API_VERSION => 1.0,
957 P_RESOURCE_ID => l_resource_id,
958 P_RESOURCE_NUMBER => l_resource_number,
959 P_RESOURCE_NAME => l_resource_name,
960 P_SOURCE_NAME => l_source_name,
961 P_SOURCE_EMAIL => l_source_email,
962 P_SOURCE_PHONE => l_source_phone,
963 P_SOURCE_OFFICE => l_source_office,
964 P_SOURCE_LOCATION => l_source_location,
965 P_SOURCE_MAILSTOP => l_source_mailstop,
966 P_TIME_ZONE => l_time_zone,
967 P_SUPPORT_SITE_ID => l_support_site_id,
968 P_PRIMARY_LANGUAGE => l_primary_language,
969 P_SECONDARY_LANGUAGE => l_secondary_language,
970 P_COST_PER_HR => l_cost_per_hr,
971 P_ADDRESS_ID => l_address_id,
972 P_OBJECT_VERSION_NUMBER => l_object_version_number,
973 P_APPROVED => l_approved,
974 X_RETURN_STATUS => x_return_status,
975 X_MSG_COUNT => x_msg_count,
976 X_MSG_DATA => x_msg_data,
977 P_SOURCE_MOBILE_PHONE => l_source_mobile_phone,
978 P_SOURCE_PAGER => l_source_pager
979 );
980
981 if (x_return_status <> 'S') then
982 x_resultout := 'U';
983 if (fnd_msg_pub.count_msg > 0) then
984 for i in 1..fnd_msg_pub.count_msg loop
985 fnd_msg_pub.get (
986 p_msg_index => i,
987 p_data => l_msg_data,
988 p_encoded => 'F',
989 p_msg_index_out => l_msg_index_out
990 );
991 --dbms_output.put_line(l_msg_data);
992 l_msg_data1 := l_msg_data1 ||''|| l_msg_data;
993 end loop;
994
995 wf_engine.setitemattrtext (
996 itemtype => itemtype,
997 itemkey => itemkey,
998 aname => 'ERROR_MESSAGE',
999 avalue => l_msg_data1
1000 );
1001
1002 end if;
1003 else
1004 x_resultout := x_return_status;
1005 end if;
1006
1007 resultout := 'COMPLETE:' || x_resultout;
1008 RETURN;
1009 END IF;
1010
1011 --
1012 -- CANCEL mode - activity 'compensation'
1013 --
1014 IF (funcmode = 'CANCEL')
1015 THEN
1016 --
1017 -- Return process to run
1018 --
1019 resultout := 'COMPLETE';
1020 RETURN;
1021 END IF;
1022
1023 --
1024 -- TIMEOUT mode
1025 --
1026 IF (funcmode = 'TIMEOUT')
1027 THEN
1028 resultout := 'COMPLETE';
1029 RETURN;
1030 END IF;
1031 --
1032
1033 END call_update_resource_api;
1034
1035 END JTF_RS_RESOURCE_WF_PUB;