[Home] [Help]
PACKAGE BODY: APPS.CS_WF_ACTIVITIES_PKG
Source
1 PACKAGE BODY CS_WF_ACTIVITIES_PKG AS
2 /* $Header: cswfactb.pls 120.3.12020000.2 2012/07/05 05:40:45 gasankar ship $ */
3
4
5 -- ***************************************************************************
6 -- * *
7 -- * GLOBAL CURSORS *
8 -- * *
9 -- ***************************************************************************
10
11 CURSOR fs_action_csr
12 (
13 p_incident_id number,
14 p_incident_action_id number
15 ) is
16 SELECT
17 sr.customer_id,
18 --sr.customer_name,
19 sr.customer_number,
20 sr.inventory_item_id,
21 mtl.concatenated_segments product,
22 mtl.description product_description,
23 CS_STD.Get_Item_Valdn_Orgzn_Id organization_id,
24 sr.reference_number,
25 sr.current_serial_number,
26 cp.installation_date,
27 sr.incident_id,
28 sr.incident_number,
29 sr.incident_date,
30 act.task_number,
31 sr.problem_code_meaning problem_code,
32 sr.resolution_code_meaning resolution_code,
33 --act.text_description problem_description,
34 -- act.text_resolution resolution_description,
35 sr.problem_code_description sr_problem_description,
36 sr.resolution_code_description sr_resolution_description,
37 sr.incident_urgency_id,
38 sr.urgency,
39 -- decode( st.status_code ,
40 --CS_WF_Activities_PKG.FS_Planned_Status , 1 ,
41 --CS_WF_Activities_PKG.FS_Cancelled_Status , 2 )
42 sr.incident_status_id,
43 --decode( st.status_code ,
44 --CS_WF_Activities_PKG.FS_Planned_Status , 'Planned' ,
45 --CS_WF_Activities_PKG.FS_Cancelled_Status , 'Cancelled' )
46 st.status_code,
47 sr.incident_date status_date,
48 act.task_type_id incident_type_id,
49 -- act.action_type_meaning incident_type,
50 it.business_process_id,
51 -- act.action_assignee_id dispatcher_id,
52 -- act.action_assignee dispatcher_name,
53 'N' covered_by_contract,
54 -- decode(sr.current_contact_person_id, null, sr.represented_by_name,
55 -- sr.current_contact_name) current_contact_name,
56 -- decode(sr.current_contact_person_id,null, sr.represented_by_telephone,
57 -- sr.current_contact_telephone) current_contact_telephone,
58 -- decode(sr.current_contact_person_id, null, sr.represented_by_area_code,
59 -- sr.current_contact_area_code) current_contact_area_code,
60 -- decode(sr.current_contact_person_id, null, sr.represented_by_extension,
61 -- sr.current_contact_extension) current_contact_extension,
62 -- decode(sr.current_contact_person_id, null, sr.represented_by_fax_number,
63 -- sr.current_contact_fax_number) current_contact_fax_number,
64 -- decode(sr.current_contact_person_id, null, sr.represented_by_fax_area_code,
65 -- sr.current_contact_fax_area_code) current_contact_fax_area_code,
66 -- decode(sr.current_contact_person_id, null, sr.represented_by_email_address,
67 -- sr.current_contact_email_address) current_contact_email_address,
68 hl.address1 || hl.address2 ship_to_address_line1,
69 hl.address3 ship_to_address_line2,
70 substr(hl.address3,instr(hl.address3, ',')+5 , 5 ) postal_code,
71 substr(hl.address3,1,instr(hl.address3, ',') -1 ) city,
72 substr(hl.address3,instr(hl.address3, ',')+2 , 2 ) state,
73 substr(hl.address3,instr(hl.address3, ',', -1, 1)+2 ) country,
74 act.actual_start_date start_time,
75 act.scheduled_end_date end_time,
76 act.actual_end_date end_time,
77 act.actual_start_date earliest_start_time,
78 act.scheduled_end_date latest_finish_time,
79 'N' appointment,
80 null request_duration,
81 -- act.dispatcher_orig_syst_id employee_id,
82 -- act.dispatch_role_name employee_name,
83 sr.incident_severity_id,
84 sr.severity incident_severity_name,
85 jn.notes inc_prob_description,
86 act.task_status_id action_status_id
87 -- act.text problem_summary
88 FROM CS_INCIDENTS_V sr,
89 jtf_tasks_v act,
90 -- CS_INCIDENT_ACTIONS_V act,
91 mtl_system_items_kfv mtl,
92 cs_customer_products cp,
93 cs_incident_types it,
94 cs_incident_statuses st,
95 hz_party_sites hps,
96 hz_locations hl,
97 jtf_notes_vl jn
98 WHERE it.incident_type_id = sr.incident_type_id
99 AND mtl.inventory_item_id(+) = sr.inventory_item_id
100 AND mtl.organization_id = CS_STD.Get_Item_Valdn_Orgzn_ID
101 AND cp.customer_product_id(+) = sr.customer_product_id
102 AND sr.incident_id = p_incident_id
103 AND act.source_object_id = p_incident_id
104 AND act.task_id = p_incident_action_id
105 -- AND act.dispatcher_orig_syst = 'PER'
106 AND act.task_status_id = st.incident_status_id
107 AND sr.customer_id = hps.party_id
108 AND hps.party_site_id = hl.location_id
109 AND sr.incident_id = jn.source_object_id
110 AND jn.source_object_code = 'SR'
111 AND upper(jn.note_type) = 'SR_PROBLEM';
112
113 -- ***************************************************************************
114 -- * *
115 -- * GLOBAL CONSTANTS *
116 -- * *
117 -- ***************************************************************************
118
119 g_fs_itemtype CONSTANT VARCHAR2(10) := 'SRACTION';
120 g_fs_activity CONSTANT VARCHAR2(30) := 'FS_NOTIFY_FIELD_ENGINEER';
121
122
123 -- ***************************************************************************
124 -- * *
125 -- * Service Request Item Type *
126 -- * *
127 -- ***************************************************************************
128
129 ------------------------------------------------------------------------------
130 -- Servereq_Selector
131 -- This procedure sets up the responsibility and organization context for
132 -- multi-org sensitive code.
133 --
134 -- Modification History:
135 --
136 -- Date Name Desc
137 -- -------- ---------- --------------------------------------
138 -- 30-Aug-2005 ANEEMUCH Fixed FP bug 4206834, issue fixed in funcmode=TEST_CTX
139 --
140 -- -----------------------------------------------------------------------
141
142
143 PROCEDURE Servereq_Selector
144 ( itemtype IN VARCHAR2,
145 itemkey IN VARCHAR2,
146 actid IN NUMBER,
147 funcmode IN VARCHAR2,
148 result OUT NOCOPY VARCHAR2
149 )
150 IS
151 l_user_id NUMBER;
152 l_resp_id NUMBER;
153 l_resp_appl_id NUMBER;
154
155 l_g_user_id NUMBER;
156 l_g_resp_id NUMBER;
157 l_g_resp_appl_id NUMBER;
158 BEGIN
159
160 IF (funcmode = 'RUN') THEN
161 result := 'COMPLETE';
162
163 -- Engine calls SET_CTX just before activity execution
164 ELSIF (funcmode = 'SET_CTX') THEN
165
166 -- First get the user id, resp id, and appl id
167 l_user_id := WF_ENGINE.GetItemAttrNumber
168 ( itemtype => itemtype,
169 itemkey => itemkey,
170 aname => 'USER_ID'
171 );
172 l_resp_id := WF_ENGINE.GetItemAttrNumber
173 ( itemtype => itemtype,
174 itemkey => itemkey,
175 aname => 'RESP_ID'
176 );
177 l_resp_appl_id := WF_ENGINE.GetItemAttrNumber
178 ( itemtype => itemtype,
179 itemkey => itemkey,
180 aname => 'RESP_APPL_ID'
181 );
182
183 -- Set the database session context
184
185 IF (NVL(l_user_id,-999) <> FND_GLOBAL.user_id OR
186 NVL(l_resp_id,-999) <> FND_GLOBAL.resp_id OR
187 NVL(l_resp_appl_id,-999) <> FND_GLOBAL.resp_appl_id) THEN
188
189 FND_GLOBAL.Apps_Initialize(l_user_id, l_resp_id, l_resp_appl_id);
190
191 END IF ;
192
193 result := 'COMPLETE';
194
195 -- Notification Viewer form calls TEST_CTX just before launching a form
196 ELSIF (funcmode = 'TEST_CTX') THEN
197 l_user_id := WF_ENGINE.GetItemAttrNumber
198 ( itemtype => itemtype,
199 itemkey => itemkey,
200 aname => 'USER_ID'
201 );
202 l_resp_id := WF_ENGINE.GetItemAttrNumber
203 ( itemtype => itemtype,
204 itemkey => itemkey,
205 aname => 'RESP_ID'
206 );
207 l_resp_appl_id := WF_ENGINE.GetItemAttrNumber
208 ( itemtype => itemtype,
209 itemkey => itemkey,
210 aname => 'RESP_APPL_ID'
211 );
212
213
214 l_g_user_id := fnd_global.user_id;
215 l_g_resp_id := fnd_global.resp_id;
216 l_g_resp_appl_id := fnd_global.resp_appl_id;
217
218 -- result := 'COMPLETE';
219 IF l_g_user_id = l_user_id
220 and l_g_resp_id = l_resp_id
221 and l_g_resp_appl_id = l_resp_appl_id THEN
222 result := 'TRUE';
223 ELSE
224 result := 'NOTSET';
225 END IF;
226
227 END IF;
228
229 EXCEPTION
230 WHEN OTHERS THEN
231 WF_CORE.Context('CS_WF_ACTIVITIES_PKG', 'Servereq_Selector',
232 itemtype, itemkey, actid, funcmode);
233 RAISE;
234 END Servereq_Selector;
235
236
237 -- ---------------------------------------------------------------------------
238 -- Initialize_Request
239 -- This procedure initializes the item attributes that will remain constant
240 -- over the duration of the Workflow. These attributes include REQUEST_ID,
241 -- REQUEST_NUMBER, REQUEST_DATE, and REQUEST_TYPE. In addition, the
242 -- ESCALATION_HISTORY item attribute is initialized with the assignment
243 -- information of the current owner.
244 -- ---------------------------------------------------------------------------
245
246 PROCEDURE Initialize_Request( itemtype VARCHAR2,
247 itemkey VARCHAR2,
248 actid NUMBER,
249 funmode VARCHAR2,
250 result OUT NOCOPY VARCHAR2 ) IS
251
252 l_msg_count NUMBER;
253 l_msg_data VARCHAR2(2000);
254 l_request_number VARCHAR2(64);
255 l_dummy NUMBER;
256 l_return_status VARCHAR2(1);
257 l_API_ERROR EXCEPTION;
258
259 /****
260 Changing this for performance issues due to
261 excessive shared memory and non-mergeable view.
262 rmanabat 03/20/03.
263
264 CURSOR l_ServiceRequest_csr IS
265 SELECT *
266 FROM CS_INCIDENTS_WORKFLOW_V
267 WHERE INCIDENT_NUMBER = l_request_number;
268 ****/
269
270 /** Replacing above cursor with this. Bug 2857365. rmanabat 03/20/02 **/
271 CURSOR l_ServiceRequest_csr IS
272 SELECT INC.INCIDENT_ID,
273 INC.INCIDENT_NUMBER,
274 INC.INCIDENT_DATE,
275 TYPE.NAME INCIDENT_TYPE
276 FROM cs_incidents_all_b INC,
277 CS_INCIDENT_TYPES_TL TYPE
278 WHERE INC.INCIDENT_NUMBER = l_request_number
279 AND INC.INCIDENT_TYPE_ID = TYPE.INCIDENT_TYPE_ID(+)
280 AND TYPE.LANGUAGE(+) = userenv('LANG');
281
282
283 l_ServiceRequest_rec l_ServiceRequest_csr%ROWTYPE;
284 l_errmsg_name VARCHAR2(30);
285
286 BEGIN
287
288 IF (funmode = 'RUN') THEN
289
290 -- Decode the item key to get the service request number
291 CS_WORKFLOW_PUB.Decode_Servereq_Itemkey(
292 p_api_version => 1.0,
293 p_return_status => l_return_status,
294 p_msg_count => l_msg_count,
295 p_msg_data => l_msg_data,
296 p_itemkey => itemkey,
297 p_request_number => l_request_number,
298 p_wf_process_id => l_dummy );
299
300 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
301 wf_core.context( pkg_name => 'CS_WORKFLOW_PUB',
302 proc_name => 'Decode_Servereq_Itemkey',
303 arg1 => 'p_itemkey=>'||itemkey );
304 l_errmsg_name := 'CS_WF_SR_CANT_DECODE_ITEMKEY';
305 raise l_API_ERROR;
306 END IF;
307
308 -- Extract the service request record
309 OPEN l_ServiceRequest_csr;
310 FETCH l_ServiceRequest_csr INTO l_ServiceRequest_rec;
311
312 -- Initialize item attributes that will remain constant
313 WF_ENGINE.SetItemAttrDate(
314 itemtype => 'SERVEREQ',
315 itemkey => itemkey,
316 aname => 'REQUEST_DATE',
317 avalue => l_ServiceRequest_rec.incident_date );
318
319 WF_ENGINE.SetItemAttrNumber(
320 itemtype => 'SERVEREQ',
321 itemkey => itemkey,
322 aname => 'REQUEST_ID',
323 avalue => l_ServiceRequest_rec.incident_id );
324
325 WF_ENGINE.SetItemAttrText(
326 itemtype => 'SERVEREQ',
327 itemkey => itemkey,
328 aname => 'REQUEST_NUMBER',
329 avalue => l_ServiceRequest_rec.incident_number );
330
331 WF_ENGINE.SetItemAttrText(
332 itemtype => 'SERVEREQ',
333 itemkey => itemkey,
334 aname => 'REQUEST_TYPE',
335 avalue => l_ServiceRequest_rec.incident_type );
336
337 CLOSE l_ServiceRequest_csr;
338
339 result := 'COMPLETE';
340
341 ELSIF (funmode = 'CANCEL') THEN
342 result := 'COMPLETE';
343 END IF;
344
345 EXCEPTION
346 WHEN l_API_ERROR THEN
347 WF_CORE.Raise(l_errmsg_name);
348 WHEN OTHERS THEN
349 WF_CORE.Context('CS_WF_ACTIVITIES_PKG', 'Initialize_Request',
350 itemtype, itemkey, actid, funmode);
351 RAISE;
352
353 END Initialize_Request;
354
355
356
357 -- -----------------------------------------------------------------------
358 -- Update_Request_Info
359 -- Refresh the item attributes with the latest values in the database.
360 --
361 -- Modification History:
362 --
363 -- Date Name Desc
364 -- -------- ---------- --------------------------------------
365 -- 05/05/2004 RMANABAT Performance . Used Wf_Engine.SetItemAttrArray
366 -- for array processing instead of individual api calls.
367 -- -----------------------------------------------------------------------
368
369 PROCEDURE Update_Request_Info ( itemtype VARCHAR2,
370 itemkey VARCHAR2,
371 actid NUMBER,
372 funmode VARCHAR2,
373 result OUT NOCOPY VARCHAR2 ) IS
374
375 l_return_status VARCHAR2(1);
376 l_msg_count NUMBER;
377 l_msg_data VARCHAR2(2000);
378 l_request_id NUMBER;
379 l_owner_role VARCHAR2(100);
380 l_owner_name VARCHAR2(240);
381 l_errmsg_name VARCHAR2(30);
382 l_API_ERROR EXCEPTION;
383
384 /****
385 Changing this for performance issues due to
386 excessive shared memory and non-mergeable view.
387 rmanabat 03/20/03.
388
389 CURSOR l_ServiceRequest_csr IS
390 SELECT *
391 FROM cs_incidents_workflow_v
392 WHERE incident_id = l_request_id;
393 ****/
394
395 /** Replacing above cursor with this. Bug 2857365. rmanabat 03/20/02 **/
396 CURSOR l_ServiceRequest_csr IS
397 SELECT INC.INCIDENT_ID,
398 INC.SUMMARY,
399 INC.INCIDENT_OWNER_ID,
400 INC.INVENTORY_ITEM_ID,
401 INC.EXPECTED_RESOLUTION_DATE,
402 INC.INCIDENT_DATE,
403 INC.CUSTOMER_PRODUCT_ID,
404 SEVERITY.NAME SEVERITY,
405 STATUS.NAME STATUS_CODE,
406 URGENCY.NAME URGENCY,
407 RA2.PARTY_NAME CUSTOMER_NAME,
408 CSLKUP.DESCRIPTION PROBLEM_CODE_DESCRIPTION,
409 MTL.DESCRIPTION PRODUCT_DESCRIPTION
410 FROM CS_INCIDENTS_ALL_VL INC,
411 --CS_INCIDENT_SEVERITIES_VL SEVERITY,
412 CS_INCIDENT_SEVERITIES_TL SEVERITY,
413 CS_INCIDENT_STATUSES_VL STATUS,
414 --CS_INCIDENT_URGENCIES_VL URGENCY,
415 CS_INCIDENT_URGENCIES_TL URGENCY,
416 HZ_PARTIES RA2,
417 CS_LOOKUPS CSLKUP,
418 --MTL_SYSTEM_ITEMS_VL MTL
419 MTL_SYSTEM_ITEMS_TL MTL
420 WHERE INC.INCIDENT_ID = l_request_id
421 AND INC.INCIDENT_STATUS_ID = STATUS.INCIDENT_STATUS_ID
422 AND INC.INCIDENT_URGENCY_ID = URGENCY.INCIDENT_URGENCY_ID(+)
423 AND URGENCY.LANGUAGE(+) = userenv('LANG')
424 AND INC.CUSTOMER_ID = RA2.PARTY_ID(+)
425 AND INC.INCIDENT_SEVERITY_ID = SEVERITY.INCIDENT_SEVERITY_ID(+)
426 AND SEVERITY.LANGUAGE(+) = userenv('LANG')
427 AND INC.PROBLEM_CODE = CSLKUP.LOOKUP_CODE(+)
428 AND CSLKUP.LOOKUP_TYPE(+) = 'REQUEST_PROBLEM_CODE'
429 AND MTL.INVENTORY_ITEM_ID(+) = INC.INVENTORY_ITEM_ID
430 AND MTL.LANGUAGE(+) = userenv('LANG')
431 AND (MTL.ORGANIZATION_ID = CS_STD.Get_Item_Valdn_Orgzn_ID OR MTL.ORGANIZATION_ID IS NULL);
432
433
434 l_ServiceRequest_rec l_ServiceRequest_csr%ROWTYPE;
435
436 /***
437 New 03/17/03. rmanabat
438 Fix for bug 2837253.
439 ***/
440
441 l_incident_owner_id NUMBER;
442 l_source_id NUMBER;
443
444 CURSOR l_get_source_id IS
445 SELECT emp.source_id
446 FROM jtf_rs_resource_extns emp
447 WHERE emp.resource_id = l_incident_owner_id;
448
449 /*** end New 03/17/03. rmanabat ***/
450
451 tvarname Wf_Engine.NameTabTyp;
452 tvarval Wf_Engine.TextTabTyp;
453
454 nvarname Wf_Engine.NameTabTyp;
455 nvarval Wf_Engine.NumTabTyp;
456
457
458 BEGIN
459
460 IF (funmode = 'RUN') THEN
461
462 -- Get the service request ID
463 l_request_id := WF_ENGINE.GetItemAttrNumber(
464 itemtype => itemtype,
465 itemkey => itemkey,
466 aname => 'REQUEST_ID' );
467
468 -- Extract the service request record
469 OPEN l_ServiceRequest_csr;
470 FETCH l_ServiceRequest_csr INTO l_ServiceRequest_rec;
471
472 l_incident_owner_id := l_ServiceRequest_rec.incident_owner_id;
473
474 OPEN l_get_source_id;
475 FETCH l_get_source_id INTO l_source_id;
476
477 IF (l_get_source_id%FOUND AND l_source_id IS NOT NULL) THEN
478
479 CLOSE l_get_source_id;
480
481 -- Retrieve the role name for the request owner
482 CS_WORKFLOW_PUB.Get_Employee_Role (
483 p_api_version => 1.0,
484 p_return_status => l_return_status,
485 p_msg_count => l_msg_count,
486 p_msg_data => l_msg_data,
487 --p_employee_id => l_ServiceRequest_rec.incident_owner_id,
488 p_employee_id => l_source_id,
489 p_role_name => l_owner_role,
490 p_role_display_name => l_owner_name );
491
492 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) OR
493 (l_owner_role is NULL) THEN
494 wf_core.context( pkg_name => 'CS_WORKFLOW_PUB',
495 proc_name => 'Get_Employee_Role',
496 arg1 => 'p_employee_id=>'|| l_source_id);
497 --to_char(l_ServiceRequest_rec.incident_owner_id));
498 l_errmsg_name := 'CS_WF_SR_CANT_FIND_OWNER';
499 raise l_API_ERROR;
500 END IF;
501
502 ELSE
503 CLOSE l_get_source_id;
504 wf_core.context( pkg_name => 'CS_WORKFLOW_PUB',
505 proc_name => 'Get_Employee_Role',
506 arg1 => 'p_employee_id=>'||
507 to_char(l_ServiceRequest_rec.incident_owner_id));
508 l_errmsg_name := 'CS_WF_SR_CANT_FIND_OWNER';
509 raise l_API_ERROR;
510
511 END IF;
512
513
514 tvarname(1) := 'OWNER_ROLE';
515 tvarval(1) := l_owner_role;
516 tvarname(2) := 'OWNER_NAME';
517 tvarval(2) := l_owner_name;
518 tvarname(3) := 'PROBLEM_DESCRIPTION';
519 tvarval(3) := l_ServiceRequest_rec.problem_code_description;
520 tvarname(4) := 'PRODUCT_DESCRIPTION';
521 tvarval(4) := l_ServiceRequest_rec.product_description;
522 tvarname(5) := 'REQUEST_CUSTOMER';
523 tvarval(5) := l_ServiceRequest_rec.customer_name;
524 tvarname(6) := 'REQUEST_SEVERITY';
525 tvarval(6) := l_ServiceRequest_rec.severity;
526 tvarname(7) := 'REQUEST_STATUS';
527 tvarval(7) := l_ServiceRequest_rec.status_code;
528 tvarname(8) := 'REQUEST_SUMMARY';
529 tvarval(8) := l_ServiceRequest_rec.summary;
530 tvarname(9) := 'REQUEST_URGENCY';
531 tvarval(9) := l_ServiceRequest_rec.urgency;
532
533 Wf_Engine.SetItemAttrTextArray(itemtype => 'SERVEREQ',
534 itemkey => itemkey,
535 aname => tvarname,
536 avalue => tvarval);
537
538 nvarname(1) := 'OWNER_ID';
539 nvarval(1) := l_source_id;
540 nvarname(2) := 'CUSTOMER_PRODUCT_ID';
541 nvarval(2) := l_ServiceRequest_rec.customer_product_id;
542 nvarname(3) := 'INVENTORY_ITEM_ID';
543 nvarval(3) := l_ServiceRequest_rec.inventory_item_id;
544
545 Wf_Engine.SetItemAttrNumberArray(itemtype => 'SERVEREQ',
546 itemkey => itemkey,
547 aname => nvarname,
548 avalue => nvarval);
549
550 WF_ENGINE.SetItemAttrDate(
551 itemtype => 'SERVEREQ',
552 itemkey => itemkey,
553 aname => 'EXPECTED_RESOLUTION_DATE',
554 avalue => l_ServiceRequest_rec.expected_resolution_date );
555
556 -- Modified on 12/21/1999
557 -- Uncommented 05/29/2001 rmanabat. Required for notification body.
558 -- changed problem_description to problem_code_description .Bug# 1650881
559 -- Uncommented 05/29/2001 rmanabat.
560 -- Required for notification body. Bug# 1650881
561
562 result := 'COMPLETE';
563
564 ELSIF (funmode = 'CANCEL') THEN
565 result := 'COMPLETE';
566 END IF;
567
568 CLOSE l_ServiceRequest_csr;
569
570 EXCEPTION
571 WHEN l_API_ERROR THEN
572 IF (l_ServiceRequest_csr%ISOPEN) THEN
573 CLOSE l_ServiceRequest_csr;
574 END IF;
575 WF_CORE.Raise(l_errmsg_name);
576 WHEN OTHERS THEN
577 WF_CORE.Context('CS_WF_ACTIVITIES_PKG', 'Update_Request_Info',
578 itemtype, itemkey, actid, funmode);
579 RAISE;
580
581 END Update_Request_Info;
582
583
584
585 -- -------------------------------------------------------------------
586 -- Select_Supervisor
587 -- Three item attributes are updated: SUPERVISOR_ID, SUPERVISOR_NAME,
588 -- and SUPERVISOR_ROLE. If the supervisor of the owner cannot be
589 -- found, or if the supervisor does not have a valid workflow role,
590 -- an exception is raised.
591 --
592 -- Modification History:
593 --
594 -- Date Name Desc
595 -- -------- ---------- --------------------------------------
596 -- 05/05/2004 RMANABAT Fix for bug 3612904. Changed cursor table
597 -- from wf_item_activity_statuses to
598 -- wf_item_activity_statuses_h because of change
599 -- in schema behavior.
600 -- 29-Aug-2005 ANEEMUCH Fixed bug 4469412. Parameter item_key and item_type
601 -- should be from parameter list in cursor sel_recipient_role_csr
602 -- -------------------------------------------------------------------
603
604 PROCEDURE Select_Supervisor( itemtype VARCHAR2,
605 itemkey VARCHAR2,
606 actid NUMBER,
607 funmode VARCHAR2,
608 result OUT NOCOPY VARCHAR2 ) IS
609
610 l_return_status VARCHAR2(1);
611 l_msg_count NUMBER;
612 l_msg_data VARCHAR2(2000);
613 l_owner_id NUMBER;
614 l_supervisor_name VARCHAR2(240);
615 l_supervisor_role VARCHAR2(100);
616 l_supervisor_id NUMBER;
617 l_errmsg_name VARCHAR2(30);
618 l_API_ERROR EXCEPTION;
619
620 -- Fix for Bug# 1810781 rmanabat
621 l_owner_role VARCHAR2(240);
622 l_recipient_role VARCHAR2(320); --gasankar 10245601
623 l_employee_id NUMBER;
624 l_status VARCHAR2(8);
625
626 CURSOR sel_recipient_role_csr IS
627 select wf.recipient_role, wf.status
628 from wf_notifications wf,
629 wf_item_activity_statuses_h wi
630 --Bug 2412660 modified for performance issues. related to bug 2365267.rmanabat 06/11/02
631 --wf_item_activity_statuses_v wi
632 where
633 wf.message_name in ('ESCALATION_WITH_EXP_MSG',
634 'ESCALATION_MSG',
635 'ASSIGNMENT_WITH_EXP_MSG',
636 'ASSIGNMENT_MSG')
637 AND wf.original_recipient = l_owner_role
638 AND wi.notification_id = wf.notification_id
639 AND wi.item_type = itemtype
640 AND wi.item_key = itemkey
641 AND wi.activity_status='COMPLETE'
642 --AND wi.activity_status_code='COMPLETE'
643 order by wf.begin_date desc;
644
645 CURSOR sel_employee_id_csr IS
646 select wr.orig_system_id
647 from WF_ROLES wr
648 where wr.orig_system = 'PER'
649 AND wr.name = l_recipient_role;
650
651 -- END of fix for Bug 1810781 rmanabat
652
653 BEGIN
654
655 IF (funmode = 'RUN') THEN
656
657 -- Get the current owner of the request
658 l_owner_id := WF_ENGINE.GetItemAttrNumber(
659 itemtype => itemtype,
660 itemkey => itemkey,
661 aname => 'OWNER_ID' );
662
663 -- Fix for Bug# 1810781 rmanabat
664 l_owner_role := WF_ENGINE.GetItemAttrText(
665 itemtype => itemtype,
666 itemkey => itemkey,
667 aname => 'OWNER_ROLE' );
668
669 -- check only for the latest notification.
670 OPEN sel_recipient_role_csr;
671 FETCH sel_recipient_role_csr into l_recipient_role,l_status;
672
673 -- check if notification was reassigned.
674 IF (l_status = 'CLOSED' AND l_recipient_role <> l_owner_role) THEN
675
676 IF (sel_recipient_role_csr%FOUND AND l_recipient_role IS NOT NULL) THEN
677 OPEN sel_employee_id_csr;
678 FETCH sel_employee_id_csr INTO l_employee_id;
679 IF (sel_employee_id_csr%FOUND AND l_employee_id IS NOT NULL) THEN
680 -- Assign the new Notification owner id if current owner
681 -- is different from recipient .
682 l_owner_id := l_employee_id;
683 END IF;
684 CLOSE sel_employee_id_csr;
685 END IF;
686 END IF;
687
688 CLOSE sel_recipient_role_csr;
689 -- END of fix for Bug 1810781 rmanabat
690
691 -- Get the supervisor information
692 CS_WORKFLOW_PUB.Get_Emp_Supervisor(
693 p_api_version => 1.0,
694 p_return_status => l_return_status,
695 p_init_msg_list => FND_API.G_TRUE,
696 p_msg_count => l_msg_count,
697 p_msg_data => l_msg_data,
698 p_employee_id => l_owner_id,
699 p_supervisor_emp_id => l_supervisor_id,
700 p_supervisor_role => l_supervisor_role,
701 p_supervisor_name => l_supervisor_name );
702
703 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) OR
704 (l_supervisor_role IS NULL) THEN
705 wf_core.context( pkg_name => 'CS_WORKFLOW_PUB',
706 proc_name => 'Get_Emp_Supervisor',
707 arg1 => 'p_employee_id=>'||to_char(l_owner_id) );
708 l_errmsg_name := 'CS_SR_CANT_FIND_SUPERVISOR';
709 raise l_API_ERROR;
710 END IF;
711
712 -- Update the item attributes
713 WF_ENGINE.SetItemAttrNumber(
714 itemtype => itemtype,
715 itemkey => itemkey,
716 aname => 'SUPERVISOR_ID',
717 avalue => l_supervisor_id );
718
719 WF_ENGINE.SetItemAttrText(
720 itemtype => itemtype,
721 itemkey => itemkey,
722 aname => 'SUPERVISOR_ROLE',
723 avalue => l_supervisor_role );
724
725 WF_ENGINE.SetItemAttrText(
726 itemtype => itemtype,
727 itemkey => itemkey,
728 aname => 'SUPERVISOR_NAME',
729 avalue => l_supervisor_name );
730
731 result := 'COMPLETE';
732
733 ELSIF (funmode = 'CANCEL') THEN
734 result := 'COMPLETE';
735 END IF;
736
737 EXCEPTION
738 WHEN l_API_ERROR THEN
739 WF_CORE.Raise(l_errmsg_name);
740 WHEN OTHERS THEN
741 WF_CORE.Context('CS_WF_ACTIVITIES_PKG', 'Select_Supervisor',
742 itemtype, itemkey, actid, funmode);
743 RAISE;
744
745 END Select_Supervisor;
746
747
748 -- -------------------------------------------------------------------
749 -- Update_Owner
750 -- Update the owner of the service request. This procedure will set
751 -- the OWNER_ID of the service request. It also updates the following
752 -- item attributes: OWNER_ID, OWNER_NAME, and OWNER_ROLE.
753 -- In 11i, l_new_owner_id is replaced by resource_id
754 --
755 -- Modification History:
756 --
757 -- Date Name Desc
758 -- -------- ---------- --------------------------------------
759 -- 05/25/2004 RMANABAT Fix for bug 3612904. Passed resp_id and
760 -- rep_appL_id to update_servicerequest() api
761 -- for security validation.
762 -- -------------------------------------------------------------------
763
764 PROCEDURE Update_Owner( itemtype VARCHAR2,
765 itemkey VARCHAR2,
766 actid NUMBER,
767 funmode VARCHAR2,
768 result OUT NOCOPY VARCHAR2 ) IS
769
770 l_owner_id NUMBER;
771 l_new_owner_id NUMBER;
772 l_return_status VARCHAR2(1);
773 l_msg_count NUMBER;
774 l_msg_data VARCHAR2(2000);
775 l_new_owner_role VARCHAR2(240);
776 l_new_owner_name VARCHAR2(240);
777 l_errmsg_name VARCHAR2(30);
778 l_API_ERROR EXCEPTION;
779 l_user_id NUMBER;
780 l_login_id NUMBER;
781 l_request_number VARCHAR2(64);
782 l_wf_process_id NUMBER;
783 l_org_id NUMBER;
784 l_dummy_id NUMBER;
785 l_prev_owner_id NUMBER;
786 l_prev_owner_name VARCHAR2(240);
787 l_prev_owner_role VARCHAR2(240);
788 l_resource_id NUMBER;
789
790 l_resource_type VARCHAR2(240) := 'RS_EMPLOYEE';
791 l_owner_group_id NUMBER;
792 l_object_version_number NUMBER;
793
794 l_resp_id NUMBER;
795 l_resp_appl_id NUMBER;
796
797 --l_service_request_rec CS_ServiceRequest_PVT.service_request_rec_type;
798 --l_notes CS_SERVICEREQUEST_PVT.notes_table;
799 --l_contacts CS_SERVICEREQUEST_PVT.contacts_table;
800 --out_interaction_id NUMBER;
801 --out_wf_process_id NUMBER;
802 l_request_id NUMBER;
803
804
805 -- Fix for bug 1361599, if end_date_active is null, record is not selected
806 -- modify the cursor below added nvl(end_date_active,sysdate +1)
807 --
808 CURSOR sel_resource_csr IS
809 select resource_id from jtf_rs_resource_extns
810 where source_id = l_new_owner_id
811 and category = 'EMPLOYEE'
812 and sysdate between start_date_active and nvl(end_date_active,sysdate + 1);
813
814 BEGIN
815
816 IF (funmode = 'RUN') THEN
817
818 -- Get the current owner of the request
819 l_owner_id := WF_ENGINE.GetItemAttrNumber(
820 itemtype => itemtype,
821 itemkey => itemkey,
822 aname => 'OWNER_ID' );
823
824 -- Get the employee ID of the new owner
825 l_new_owner_id := WF_ENGINE.GetActivityAttrNumber(
826 itemtype => itemtype,
827 itemkey => itemkey,
828 actid => actid,
829 aname => 'NEW_OWNER_ID' );
830
831
832 -- Get the new owner information
833 CS_WORKFLOW_PUB.Get_Employee_Role (
834 p_api_version => 1.0,
835 p_init_msg_list => FND_API.G_FALSE,
836 p_return_status => l_return_status,
837 p_msg_count => l_msg_count,
838 p_msg_data => l_msg_data,
839 p_employee_id => l_new_owner_id,
840 p_role_name => l_new_owner_role,
841 p_role_display_name => l_new_owner_name );
842
843 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) OR
844 (l_new_owner_role IS NULL) THEN
845 wf_core.context( pkg_name => 'CS_WORKFLOW_PUB',
846 proc_name => 'Get_Employee_Role',
847 arg1 => 'p_employee_id=>'||to_char(l_new_owner_id) );
848 l_errmsg_name := 'CS_WF_SR_CANT_FIND_OWNER';
849 raise l_API_ERROR;
850 END IF;
851
852 -- Get the FND_USER ID of the current owner so that we can
853 -- use it for the audit record
854 CS_WORKFLOW_PUB.Get_Emp_Fnd_User_ID(
855 p_api_version => 1.0,
856 p_return_status => l_return_status,
857 p_msg_count => l_msg_count,
858 p_msg_data => l_msg_data,
859 p_employee_id => l_owner_id,
860 p_fnd_user_id => l_user_id );
861
862 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
863 wf_core.context( pkg_name => 'CS_WORKFLOW_PUB',
864 proc_name => 'Get_Emp_Fnd_User_ID',
865 arg1 => 'p_employee_id=>'||to_char(l_owner_id) );
866 l_errmsg_name := 'CS_WF_SR_GET_EMP_USER_ID';
867 raise l_API_ERROR;
868 END IF;
869
870 -- Get the workflow process ID
871 CS_Workflow_PUB.Decode_Servereq_Itemkey(
872 p_api_version => 1.0,
873 p_init_msg_list => FND_API.G_FALSE,
874 p_return_status => l_return_status,
875 p_msg_count => l_msg_count,
876 p_msg_data => l_msg_data,
877 p_itemkey => itemkey,
878 p_request_number => l_request_number,
879 p_wf_process_id => l_wf_process_id );
880
881 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
882 wf_core.context( pkg_name => 'CS_WORKFLOW_PUB',
883 proc_name => 'Decode_Servereq_Itemkey',
884 arg1 => 'p_itemkey=>'||itemkey );
885 l_errmsg_name := 'CS_WF_SR_CANT_DECODE_ITEMKEY';
886 raise l_API_ERROR;
887 END IF;
888
889 BEGIN
890 SELECT login_id INTO l_login_id
891 FROM fnd_logins
892 WHERE login_id = FND_GLOBAL.LOGIN_ID
893 AND user_id = l_user_id;
894 EXCEPTION
895 WHEN OTHERS THEN
896 l_login_id := NULL;
897 END;
898
899 SELECT org_id, object_version_number, incident_id
900 INTO l_org_id, l_object_version_number, l_request_id
901 FROM CS_INCIDENTS_ALL_B
902 WHERE incident_number = l_request_number;
903
904 -- For 11i, replace person_id with resource_id
905 -- l_new_owner_id is the resource_id
906
907 open sel_resource_csr;
908 fetch sel_resource_csr into l_resource_id;
909 if (sel_resource_csr%notfound) then
910 null;
911 end if;
912
913 -- Commented out. Fix for bug 3065468. rmanabat 07/24/03
914 --l_new_owner_id := l_resource_id;
915
916
917 IF (fnd_global.resp_id is null OR fnd_global.resp_id = -1) THEN
918 l_resp_id := WF_ENGINE.GetItemAttrNumber(
919 itemtype => itemtype,
920 itemkey => itemkey,
921 aname => 'RESP_ID' );
922 l_resp_appl_id := WF_ENGINE.GetItemAttrNumber(
923 itemtype => itemtype,
924 itemkey => itemkey,
925 aname => 'RESP_APPL_ID' );
926 ELSE
927 l_resp_id := fnd_global.resp_id;
928 l_resp_appl_id := fnd_global.resp_appl_id;
929 END IF;
930
931 CS_ServiceRequest_PUB.Update_Owner (
932 p_api_version => 2.0,
933 p_init_msg_list => FND_API.G_FALSE,
934 p_commit => FND_API.G_FALSE,
935 x_return_status => l_return_status,
936 x_msg_count => l_msg_count,
937 x_msg_data => l_msg_data,
938 p_user_id => l_user_id,
939 p_login_id => l_login_id,
940 -- Don't need Org id for updates , SR API modified so comment out here
941 --p_org_id => l_org_id,
942 p_request_number => l_request_number,
943 p_object_version_number => l_object_version_number,
944 -- Fix for bug 3065468. rmanabat 07/24/03
945 --p_owner_id => l_new_owner_id,
946 p_resp_id => l_resp_id,
947 p_resp_appl_id => l_resp_appl_id,
948 p_owner_id => l_resource_id,
949 p_owner_group_id => l_owner_group_id,
950 p_resource_type => l_resource_type,
951 p_audit_comments => CS_WF_ACTIVITIES_PKG.Audit_Comments,
952 p_called_by_workflow => FND_API.G_TRUE,
953 p_workflow_process_id => l_wf_process_id,
954 x_interaction_id => l_dummy_id );
955
956 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
957 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count,
958 p_data => l_msg_data,
959 p_encoded => FND_API.G_FALSE );
960 wf_core.context( pkg_name => 'CS_ServiceRequest_PUB',
961 proc_name => 'Update_Owner',
962 arg1 => 'p_user_id=>'||l_user_id,
963 arg2 => 'p_login_id=>'||l_login_id,
964 arg3 => 'p_org_id=>'||l_org_id,
965 arg4 => 'p_owner_id=>'||l_resource_id,
966 arg5 => 'p_msg_data=>'||l_msg_data );
967 l_errmsg_name := 'CS_SR_CANT_UPDATE_OWNER';
968 raise l_API_ERROR;
969 END IF;
970
971 l_prev_owner_id := WF_ENGINE.GetItemAttrNumber(
972 itemtype => itemtype,
973 itemkey => itemkey,
974 aname => 'OWNER_ID' );
975
976 l_prev_owner_name := WF_ENGINE.GetItemAttrText(
977 itemtype => itemtype,
978 itemkey => itemkey,
979 aname => 'OWNER_NAME' );
980
981 l_prev_owner_role := WF_ENGINE.GetItemAttrText(
982 itemtype => itemtype,
983 itemkey => itemkey,
984 aname => 'OWNER_ROLE' );
985
986 WF_ENGINE.SetItemAttrNumber(
987 itemtype => itemtype,
988 itemkey => itemkey,
989 aname => 'PREV_OWNER_ID',
990 avalue => l_prev_owner_id );
991
992 WF_ENGINE.SetItemAttrText(
993 itemtype => itemtype,
994 itemkey => itemkey,
995 aname => 'PREV_OWNER_ROLE',
996 avalue => l_prev_owner_role );
997
998 WF_ENGINE.SetItemAttrText(
999 itemtype => itemtype,
1000 itemkey => itemkey,
1001 aname => 'PREV_OWNER_NAME',
1002 avalue => l_prev_owner_name );
1003
1004 WF_ENGINE.SetItemAttrNumber(
1005 itemtype => itemtype,
1006 itemkey => itemkey,
1007 aname => 'OWNER_ID',
1008 avalue => l_new_owner_id );
1009
1010 WF_ENGINE.SetItemAttrText(
1011 itemtype => itemtype,
1012 itemkey => itemkey,
1013 aname => 'OWNER_ROLE',
1014 avalue => l_new_owner_role );
1015
1016 WF_ENGINE.SetItemAttrText(
1017 itemtype => itemtype,
1018 itemkey => itemkey,
1019 aname => 'OWNER_NAME',
1020 avalue => l_new_owner_name );
1021
1022 result := 'COMPLETE';
1023
1024 ELSIF (funmode = 'CANCEL') THEN
1025 result := 'COMPLETE';
1026 END IF;
1027
1028 EXCEPTION
1029 WHEN l_API_ERROR THEN
1030 WF_CORE.Raise(l_errmsg_name);
1031 WHEN OTHERS THEN
1032 WF_CORE.Context('CS_WF_ACTIVITIES_PKG', 'Update_Owner',
1033 itemtype, itemkey, actid, funmode);
1034 RAISE;
1035
1036 END Update_Owner;
1037
1038
1039 -- ---------------------------------------------------------------------------
1040 -- Update_Status
1041 -- This procedure corresponds to the UPDATE_STATUS function activity. It
1042 -- updates the status of the service request to the value given by the
1043 -- STATUS activity attribute.
1044 --
1045 -- Modification History:
1046 --
1047 -- Date Name Desc
1048 -- -------- ---------- --------------------------------------
1049 -- 05/25/2004 RMANABAT Fix for bug 3612904. Passed resp_id and
1050 -- rep_appL_id to update_servicerequest() api
1051 -- for security validation.
1052 -- ---------------------------------------------------------------------------
1053
1054 PROCEDURE Update_Status( itemtype VARCHAR2,
1055 itemkey VARCHAR2,
1056 actid NUMBER,
1057 funmode VARCHAR2,
1058 result OUT NOCOPY VARCHAR2 ) IS
1059
1060 l_owner_id NUMBER;
1061 l_return_status VARCHAR2(1);
1062 l_msg_count NUMBER;
1063 l_msg_data VARCHAR2(2000);
1064 l_user_id NUMBER;
1065 l_login_id NUMBER;
1066 l_errmsg_name VARCHAR2(30);
1067 l_API_ERROR EXCEPTION;
1068 l_request_number VARCHAR2(64);
1069 l_wf_process_id NUMBER;
1070 l_new_status_id NUMBER;
1071 l_new_status VARCHAR2(30);
1072 l_org_id NUMBER;
1073 l_dummy_id NUMBER;
1074 l_object_version_number NUMBER;
1075
1076 -- Added new variables for # 1528813
1077
1078 l_request_id number;
1079 l_service_request_rec CS_ServiceRequest_PVT.service_request_rec_type;
1080 l_notes CS_SERVICEREQUEST_PVT.notes_table;
1081 l_contacts CS_SERVICEREQUEST_PVT.contacts_table;
1082 out_interaction_id number;
1083 out_wf_process_id number;
1084 l_api_name CONSTANT VARCHAR2(60) := 'CS_WF_ACTIVITIES_PKG.Update_status';
1085 l_msg_index_out number;
1086
1087 l_resp_id NUMBER;
1088 l_resp_appl_id NUMBER;
1089
1090 BEGIN
1091
1092 IF (funmode = 'RUN') THEN
1093
1094 -- Get the current owner of the request
1095 l_owner_id := WF_ENGINE.GetItemAttrNumber(
1096 itemtype => itemtype,
1097 itemkey => itemkey,
1098 aname => 'OWNER_ID' );
1099
1100 -- Get the FND User ID of the owner for the audit record
1101
1102 /* Commenting out this call, l_user_id used for update_service_request
1103 (last_updated_by) should always be the FND_USER .
1104
1105 CS_WORKFLOW_PUB.Get_Emp_Fnd_User_ID(
1106 p_api_version => 1.0,
1107 p_return_status => l_return_status,
1108 p_msg_count => l_msg_count,
1109 p_msg_data => l_msg_data,
1110 p_employee_id => l_owner_id,
1111 p_fnd_user_id => l_user_id );
1112
1113 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1114 wf_core.context( pkg_name => 'CS_WORKFLOW_PUB',
1115 proc_name => 'Get_Emp_Fnd_User_ID',
1116 arg1 => 'p_employee_id=>'||to_char(l_owner_id) );
1117 l_errmsg_name := 'CS_WF_SR_GET_EMP_USER_ID';
1118 raise l_API_ERROR;
1119 END IF;
1120 */
1121 -- Replacing call to CS_WORKFLOW_PUB.Get_Emp_Fnd_User_ID
1122 -- with FND_USER profile. rmanabat 09/18/01
1123
1124 /* Roopa
1125 Fix for bug 2843395
1126
1127 1) See if fnd_profile.valuw returns an id
1128 2) Else, see if the incident_owner_id(source_id) of the sr has a valid value
1129 3) Else, use fnd_global.user_id
1130 */
1131 l_user_id := fnd_profile.value('USER_ID');
1132
1133 IF (l_user_id IS NULL) THEN
1134
1135 IF(l_owner_id IS NOT NULL) THEN
1136 CS_WORKFLOW_PUB.Get_Emp_Fnd_User_ID(
1137 p_api_version => 1.0,
1138 p_return_status => l_return_status,
1139 p_msg_count => l_msg_count,
1140 p_msg_data => l_msg_data,
1141 p_employee_id => l_owner_id,
1142 p_fnd_user_id => l_user_id );
1143
1144 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1145 wf_core.context( pkg_name => 'CS_WORKFLOW_PUB',
1146 proc_name => 'Get_Emp_Fnd_User_ID',
1147 arg1 => 'p_employee_id=>'||to_char(l_owner_id) );
1148 l_errmsg_name := 'CS_WF_SR_GET_EMP_USER_ID';
1149 raise l_API_ERROR;
1150 END IF;
1151 ELSE
1152 l_user_id := fnd_global.USER_ID();
1153 END IF;
1154 END IF;
1155
1156 -- Get the workflow process ID
1157 CS_Workflow_PUB.Decode_Servereq_Itemkey(
1158 p_api_version => 1.0,
1159 p_init_msg_list => FND_API.G_FALSE,
1160 p_return_status => l_return_status,
1161 p_msg_count => l_msg_count,
1162 p_msg_data => l_msg_data,
1163 p_itemkey => itemkey,
1164 p_request_number => l_request_number,
1165 p_wf_process_id => l_wf_process_id );
1166
1167 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1168 wf_core.context( pkg_name => 'CS_WORKFLOW_PUB',
1169 proc_name => 'Decode_Servereq_Itemkey',
1170 arg1 => 'p_itemkey=>'||itemkey );
1171 l_errmsg_name := 'CS_WF_SR_CANT_DECODE_ITEMKEY';
1172 raise l_API_ERROR;
1173 END IF;
1174
1175 --fix for #1528813
1176 -- get object_version_number
1177
1178 begin
1179 --initialise
1180 l_return_status := FND_API.G_RET_STS_SUCCESS;
1181
1182 select object_version_number,incident_id
1183 INTO l_object_version_number,l_request_id
1184 from cs_incidents_all_b
1185 where incident_number = l_request_number;
1186
1187 -- dbms_output.put_line('object version ' || l_object_version_number );
1188
1189 exception
1190 WHEN NO_DATA_FOUND THEN
1191 l_return_status := FND_API.G_RET_STS_ERROR;
1192 CS_ServiceRequest_UTIL.Add_Invalid_Argument_Msg(
1193 p_token_an => l_api_name,
1194 p_token_v => to_char(l_request_id),
1195 p_token_p => 'p_request_id' );
1196 raise FND_API.G_EXC_ERROR;
1197
1198 WHEN TOO_MANY_ROWS THEN
1199 Null;
1200 end;
1201
1202 -- Get the new status
1203 l_new_status_id := WF_ENGINE.GetActivityAttrNumber(
1204 itemtype => itemtype,
1205 itemkey => itemkey,
1206 actid => actid,
1207 aname => 'STATUS' );
1208
1209 BEGIN
1210 SELECT login_id INTO l_login_id
1211 FROM fnd_logins
1212 WHERE login_id = FND_GLOBAL.LOGIN_ID
1213 AND user_id = l_user_id;
1214 EXCEPTION
1215 WHEN OTHERS THEN
1216 l_login_id := NULL;
1217 END;
1218
1219 SELECT org_id INTO l_org_id
1220 FROM CS_INCIDENTS_ALL_B
1221 WHERE incident_number = l_request_number;
1222
1223 -- Update the status. Note that the status ID of 2 is a seeded
1224 -- status that corresponds to 'CLOSED'; we use the ID instead of
1225 -- hardcoded value of 'CLOSED' due to translation issues
1226
1227 --Fix for Bug 1528813
1228 -- Change API call to Update_ServiceRequest since it updates status too
1229 -- Get the object version number from cs_incidents_all_b
1230
1231
1232 /*
1233 CS_ServiceRequest_PUB.Update_Status (
1234 p_api_version => 2.0,
1235 p_init_msg_list => FND_API.G_FALSE,
1236 p_commit => FND_API.G_FALSE,
1237 x_return_status => l_return_status,
1238 x_msg_count => l_msg_count,
1239 x_msg_data => l_msg_data,
1240 p_user_id => l_user_id,
1241 p_login_id => l_login_id,
1242 -- Don't need Org id for updates , SR API modified so comment out here
1243 -- p_org_id => l_org_id,
1244 p_request_number => l_request_number,
1245 p_object_version_number => l_object_version_number,
1246 p_status_id => l_new_status_id,
1247 p_closed_date => sysdate,
1248 p_audit_comments => CS_WF_ACTIVITIES_PKG.Audit_Comments,
1249 p_called_by_workflow => FND_API.G_TRUE,
1250 p_workflow_process_id => l_wf_process_id,
1251 x_interaction_id => l_dummy_id );
1252
1253 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1254 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count,
1255 p_data => l_msg_data,
1256 p_encoded => FND_API.G_FALSE );
1257 wf_core.context( pkg_name => 'CS_ServiceRequest_PUB',
1258 proc_name => 'Update_Status',
1259 arg1 => 'p_user_id=>'||l_user_id,
1260 arg2 => 'p_org_id=>'||l_org_id,
1261 arg3 => 'p_request_number=>'||l_request_number,
1262 arg4 => 'p_status_id=>'||l_new_status_id,
1263 arg5 => 'p_msg_data=>'||l_msg_data );
1264 l_errmsg_name := 'CS_SR_CANT_UPDATE_STATUS';
1265 raise l_API_ERROR;
1266 END IF;
1267 */
1268 -- For bug# 1528813
1269 -- initialize
1270
1271 CS_ServiceRequest_PVT.initialize_rec(l_service_request_rec);
1272
1273 l_service_request_rec.status_id := l_new_status_id;
1274 l_service_request_rec.closed_date := sysdate;
1275
1276 -- Added mandatory parameter last_update_program_code .11/19/02 rmanabat
1277 l_service_request_rec.last_update_program_code := 'SUPPORT.WF';
1278
1279 IF (fnd_global.resp_id is null OR fnd_global.resp_id = -1) THEN
1280 l_resp_id := WF_ENGINE.GetItemAttrNumber(
1281 itemtype => itemtype,
1282 itemkey => itemkey,
1283 aname => 'RESP_ID' );
1284 l_resp_appl_id := WF_ENGINE.GetItemAttrNumber(
1285 itemtype => itemtype,
1286 itemkey => itemkey,
1287 aname => 'RESP_APPL_ID' );
1288 ELSE
1289 l_resp_id := fnd_global.resp_id;
1290 l_resp_appl_id := fnd_global.resp_appl_id;
1291 END IF;
1292
1293
1294 CS_ServiceRequest_PVT.Update_ServiceRequest
1295 ( p_api_version => 3.0, -- Changed from 2.0 for 11.5.9.
1296 p_init_msg_list => fnd_api.g_false,
1297 p_commit => fnd_api.g_true,
1298 p_validation_level => fnd_api.g_valid_level_full,
1299 x_return_status => l_return_status,
1300 x_msg_count => l_msg_count,
1301 x_msg_data => l_msg_data,
1302 p_request_id => l_request_id,
1303 p_last_updated_by => l_user_id,
1304 p_last_update_date => sysdate,
1305 p_service_request_rec => l_service_request_rec,
1306 p_notes => l_notes,
1307 p_contacts => l_contacts,
1308 p_object_version_number => l_object_version_number,
1309 p_resp_appl_id => l_resp_appl_id,
1310 p_resp_id => l_resp_id,
1311 x_interaction_id => out_interaction_id,
1312 x_workflow_process_id => out_wf_process_id
1313 );
1314
1315
1316
1317 -- Check for possible errors returned by the API
1318 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1319 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count,
1320 p_data => l_msg_data,
1321 p_encoded => FND_API.G_FALSE );
1322 wf_core.context( pkg_name => 'CS_ServiceRequest_PUB',
1323 proc_name => 'Update_Status',
1324 arg1 => 'p_user_id=>'||l_user_id,
1325 arg2 => 'p_org_id=>'||l_org_id,
1326 arg3 => 'p_request_number=>'||l_request_number,
1327 arg4 => 'p_status_id=>'||l_new_status_id,
1328 arg5 => 'p_msg_data=>'||l_msg_data );
1329 l_errmsg_name := 'CS_SR_CANT_UPDATE_STATUS';
1330 raise l_API_ERROR;
1331 END IF;
1332 /*
1333 This is the standard error handling but am retaining the older method
1334 as in earlier version
1335 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1336 raise FND_API.G_EXC_ERROR;
1337 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1338 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1339 END IF;
1340 */
1341
1342
1343 /* for testng - error handling when running SQL script you need this
1344 to see if Update_SR API is successful
1345 IF (FND_MSG_PUB.Count_Msg > 1) THEN
1346 --Display all the error messages
1347 FOR j in 1..FND_MSG_PUB.Count_Msg LOOP
1348 FND_MSG_PUB.Get(p_msg_index=>j,
1349 p_encoded=>'F',
1350 p_data=>l_msg_data,
1351 p_msg_index_out=>l_msg_index_out);
1352 DBMS_OUTPUT.PUT_LINE(l_msg_data);
1353 END LOOP;
1354 ELSE
1355 --Only one error
1356 FND_MSG_PUB.Get(p_msg_index=>1,
1357 p_encoded=>'F',
1358 p_data=>l_msg_data,
1359 p_msg_index_out=>l_msg_index_out);
1360 DBMS_OUTPUT.PUT_LINE(l_msg_data);
1361 END IF;
1362
1363 --dbms_output.put_line(' after error mesg ' || l_return_status );
1364
1365 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1366 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count,
1367 p_data => l_msg_data,
1368 p_encoded => FND_API.G_FALSE );
1369 wf_core.context( pkg_name => 'CS_ServiceRequest_PVT',
1370 proc_name => 'Update_ServiceRequest',
1371 arg1 => 'p_user_id=>'||l_user_id,
1372 arg2 => 'p_org_id=>'||l_org_id,
1373 arg3 => 'p_request_number=>'||l_request_number,
1374 arg4 => 'p_status_id=>'||l_new_status_id,
1375 arg5 => 'p_msg_data=>'||l_msg_data );
1376 l_errmsg_name := 'CS_SR_CANT_UPDATE_STATUS';
1377 raise l_API_ERROR;
1378 END IF;
1379 */
1380 --
1381 -- Update the item attribute
1382 SELECT name
1383 INTO l_new_status
1384 FROM cs_incident_statuses
1385 WHERE incident_status_id = 2;
1386
1387 WF_ENGINE.SetItemAttrText(
1388 itemtype => itemtype,
1389 itemkey => itemkey,
1390 aname => 'REQUEST_STATUS',
1391 avalue => l_new_status );
1392
1393 result := 'COMPLETE';
1394
1395 ELSIF (funmode = 'CANCEL') THEN
1396 result := 'COMPLETE';
1397 END IF;
1398
1399 EXCEPTION
1400 /* for new error handling
1401 WHEN FND_API.G_EXC_ERROR THEN
1402 l_return_status := FND_API.G_RET_STS_ERROR;
1403 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count,
1404 p_data => l_msg_data,
1405 p_encoded => FND_API.G_FALSE );
1406 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1407 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1408 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count,
1409 p_data => l_msg_data,
1410 p_encoded => FND_API.G_FALSE );
1411 */
1412 WHEN l_API_ERROR THEN
1413 WF_CORE.Raise(l_errmsg_name);
1414 WHEN OTHERS THEN
1415 WF_CORE.Context('CS_WF_ACTIVITIES_PKG', 'Update_Status',
1416 itemtype, itemkey, actid, funmode);
1417 RAISE;
1418
1419 END Update_Status;
1420
1421
1422
1423 -- -------------------------------------------------------------------
1424 -- Validate_Response_Deadline
1425 -- Return 'N' if the RESPONSE_DEADLINE item attribute is NULL or if
1426 -- it's less than sysdate; otherwise, return 'Y'.
1427 -- -------------------------------------------------------------------
1428
1429 PROCEDURE Validate_Response_Deadline(
1430 itemtype VARCHAR2,
1431 itemkey VARCHAR2,
1432 actid NUMBER,
1433 funmode VARCHAR2,
1434 result OUT NOCOPY VARCHAR2 ) IS
1435 l_response_deadline DATE;
1436
1437 BEGIN
1438 IF (funmode = 'RUN') THEN
1439
1440 -- Get the response deadline
1441 l_response_deadline := WF_ENGINE.GetItemAttrDate(
1442 itemtype => itemtype,
1443 itemkey => itemkey,
1444 aname => 'RESPONSE_DEADLINE' );
1445
1446 IF (l_response_deadline IS NULL) OR
1447 (l_response_deadline < sysdate) THEN
1448 result := 'COMPLETE:N';
1449 ELSE
1450 result := 'COMPLETE:Y';
1451 END IF;
1452
1453 ELSIF (funmode = 'CANCEL') THEN
1454 result := 'COMPLETE';
1455 END IF;
1456
1457 EXCEPTION
1458 WHEN OTHERS THEN
1459 WF_CORE.Context('CS_WF_ACTIVITIES_PKG', 'Validate_Response_Deadline',
1460 itemtype, itemkey, actid, funmode);
1461 RAISE;
1462
1463 END Validate_Response_Deadline;
1464
1465
1466 -- ---------------------------------------------------------------------------
1467 -- Reset_Response_Deadline
1468 -- This procedure corresponds to the RESET_RESPONSE_DEADLINE function
1469 -- activity. It resets the RESPONSE_DEADLINE item attribute back to NULL.
1470 -- ---------------------------------------------------------------------------
1471
1472 PROCEDURE Reset_Response_Deadline( itemtype VARCHAR2,
1473 itemkey VARCHAR2,
1474 actid NUMBER,
1475 funmode VARCHAR2,
1476 result OUT NOCOPY VARCHAR2 ) IS
1477 BEGIN
1478 IF (funmode = 'RUN') THEN
1479
1480 -- Reset the response deadline to NULL
1481 WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
1482 itemkey => itemkey,
1483 aname => 'RESPONSE_DEADLINE',
1484 avalue => '' );
1485
1486 result := 'COMPLETE';
1487
1488 ELSIF (funmode = 'CANCEL') THEN
1489 result := 'COMPLETE';
1490 END IF;
1491
1492 EXCEPTION
1493 WHEN OTHERS THEN
1494 WF_CORE.Context('CS_WF_ACTIVITIES_PKG', 'Reset_Response_Deadline',
1495 itemtype, itemkey, actid, funmode);
1496 RAISE;
1497
1498 END Reset_Response_Deadline;
1499
1500
1501
1502 -- ***************************************************************************
1503 -- * *
1504 -- * System: Error Item Type *
1505 -- * *
1506 -- * Following activities are used in the Service Request Error Process *
1507 -- * *
1508 -- ***************************************************************************
1509
1510
1511 -- -------------------------------------------------------------------
1512 -- Initialize_Errors
1513 -- Retrieve the exception messages from the process that errored out
1514 -- and store them in the item attributes of the error process. Also,
1515 -- get the role of the Workflow administrator.
1516 -- -------------------------------------------------------------------
1517
1518 PROCEDURE Initialize_Errors( itemtype VARCHAR2,
1519 itemkey VARCHAR2,
1520 actid NUMBER,
1521 funmode VARCHAR2,
1522 result OUT NOCOPY VARCHAR2 ) IS
1523
1524 l_error_item_type VARCHAR2(8);
1525 l_error_itemkey VARCHAR2(240);
1526 l_error_name VARCHAR2(30);
1527 l_error_msg VARCHAR2(2000);
1528 l_administrator VARCHAR2(100);
1529 l_monitor_url VARCHAR2(500);
1530
1531 BEGIN
1532 IF (funmode = 'RUN') THEN
1533
1534 --
1535 -- Get the type and the key of the process that errored out
1536 --
1537 l_error_itemkey := WF_ENGINE.GetItemAttrText(
1538 itemtype => itemtype,
1539 itemkey => itemkey,
1540 aname => 'ERROR_ITEM_KEY' );
1541
1542 l_error_item_type := WF_ENGINE.GetItemAttrText(
1543 itemtype => itemtype,
1544 itemkey => itemkey,
1545 aname => 'ERROR_ITEM_TYPE' );
1546
1547 --
1548 -- Get the error message
1549 --
1550 l_error_name := WF_ENGINE.GetItemAttrText(
1551 itemtype => itemtype,
1552 itemkey => itemkey,
1553 aname => 'ERROR_NAME' );
1554
1555 IF (l_error_name IS NOT NULL) THEN
1556 FND_MESSAGE.SET_NAME('CS', l_error_name);
1557 l_error_msg := FND_MESSAGE.GET;
1558 END IF;
1559
1560 --
1561 -- Get the workflow administrator
1562 --
1563 l_administrator := WF_ENGINE.GetItemAttrText(
1564 itemtype => l_error_item_type,
1565 itemkey => l_error_itemkey,
1566 aname => 'WF_ADMINISTRATOR' );
1567 --
1568 -- Set the item attributes of the error process
1569 --
1570 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1571 itemkey => itemkey,
1572 aname => 'ERROR_MESSAGE',
1573 avalue => l_error_msg );
1574
1575 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1576 itemkey => itemkey,
1577 aname => 'SERVEREQ_WF_ADMIN',
1578 avalue => l_administrator );
1579
1580 l_monitor_url := WF_MONITOR.GetEnvelopeURL
1581 ( x_agent => FND_PROFILE.Value('APPS_WEB_AGENT'),
1582 x_item_type => l_error_item_type,
1583 x_item_key => l_error_itemkey,
1584 x_admin_mode => 'YES'
1585 );
1586 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1587 itemkey => itemkey,
1588 aname => 'SERVEREQ_MONITOR_URL',
1589 avalue => l_monitor_url );
1590
1591 result := 'COMPLETE';
1592
1593 ELSIF (funmode = 'CANCEL') THEN
1594 result := 'COMPLETE';
1595 END IF;
1596
1597 EXCEPTION
1598 WHEN OTHERS THEN
1599 WF_CORE.Context('CS_WF_ACTIVITIES_PKG', 'Initialize_Errors',
1600 itemtype, itemkey, actid, funmode);
1601 RAISE;
1602 END Initialize_Errors;
1603
1604
1605
1606 -- ***************************************************************************
1607 -- * *
1608 -- * Service Request Action Item Type *
1609 -- * *
1610 -- ***************************************************************************
1611
1612 ------------------------------------------------------------------------------
1613 -- Action_Selector
1614 -- This procedure sets up the responsibility and organization context for
1615 -- multi-org sensitive code.
1616 ------------------------------------------------------------------------------
1617
1618 PROCEDURE Action_Selector
1619 ( itemtype IN VARCHAR2,
1620 itemkey IN VARCHAR2,
1621 actid IN NUMBER,
1622 funcmode IN VARCHAR2,
1623 result OUT NOCOPY VARCHAR2
1624 )
1625 IS
1626 l_user_id NUMBER;
1627 l_resp_id NUMBER;
1628 l_resp_appl_id NUMBER;
1629 BEGIN
1630
1631 IF (funcmode = 'RUN') THEN
1632 result := 'COMPLETE';
1633
1634 -- Engine calls SET_CTX just before activity execution
1635 ELSIF (funcmode = 'SET_CTX') THEN
1636
1637 -- First get the user id, resp id, and appl id
1638 l_user_id := WF_ENGINE.GetItemAttrNumber
1639 ( itemtype => itemtype,
1640 itemkey => itemkey,
1641 aname => 'USER_ID'
1642 );
1643 l_resp_id := WF_ENGINE.GetItemAttrNumber
1644 ( itemtype => itemtype,
1645 itemkey => itemkey,
1646 aname => 'RESP_ID'
1647 );
1648 l_resp_appl_id := WF_ENGINE.GetItemAttrNumber
1649 ( itemtype => itemtype,
1650 itemkey => itemkey,
1651 aname => 'RESP_APPL_ID'
1652 );
1653
1654 -- Set the database session context
1655 FND_GLOBAL.Apps_Initialize(l_user_id, l_resp_id, l_resp_appl_id);
1656
1657 result := 'COMPLETE';
1658
1659 -- Notification Viewer form calls TEST_CTX just before launching a form
1660 ELSIF (funcmode = 'TEST_CTX') THEN
1661 result := 'COMPLETE';
1662
1663 END IF;
1664
1665 EXCEPTION
1666 WHEN OTHERS THEN
1667 WF_CORE.Context('CS_WF_ACTIVITIES_PKG', 'Action_Selector',
1668 itemtype, itemkey, actid, funcmode);
1669 RAISE;
1670 END Action_Selector;
1671
1672
1673 ------------------------------------------------------------------------------
1674 -- Initialize_Action
1675 -- This procedure initializes the item attributes that will remain constant
1676 -- over the duration of the Workflow. These attributes include
1677 -- REQUEST_ACTION_ID, REQUEST_ID, REQUEST_NUMER, ACTION_NUMBER, ACTION_DATE,
1678 -- ACTION_TYPE, ACTION_STATUS, ACTION_SEVERITY, ASSIGNEE_ID, ASSIGNEE_ROLE,
1679 -- ASSIGNEE_NAME, ACTION_SUMMARY, REQUEST_CUSTOMER, CUSTOMER_PRODUCT_ID,
1680 -- INVENTORY_ITEM_ID, PRODUCT_DESCRIPTION, REQUEST_LOCAION,
1681 -- ACTION_DESCRIPTION, and EXPECTED_RESOLUTION_DATE.
1682 ------------------------------------------------------------------------------
1683
1684 /*PROCEDURE Initialize_Action
1685 ( itemtype IN VARCHAR2,
1686 itemkey IN VARCHAR2,
1687 actid IN NUMBER,
1688 funcmode IN VARCHAR2,
1689 result OUT VARCHAR2
1690 )
1691 IS
1692 l_return_status VARCHAR2(1);
1693 l_msg_count NUMBER;
1694 l_msg_data VARCHAR2(2000);
1695 l_errmsg_name VARCHAR2(30);
1696 l_request_id NUMBER;
1697 l_action_number NUMBER;
1698 l_dummy NUMBER;
1699 l_assignee_role VARCHAR2(100);
1700 l_assignee_name VARCHAR2(240);
1701
1702 l_exc_api_error EXCEPTION;
1703
1704 CURSOR l_action_csr IS
1705 SELECT *
1706 FROM cs_inc_actions_workflow_v
1707 WHERE incident_id = l_request_id
1708 AND action_number = l_action_number;
1709 l_action_rec l_action_csr%ROWTYPE;
1710
1711 BEGIN
1712 IF (funcmode = 'RUN') THEN
1713
1714 -- Decode the item key to get the service request id and action number
1715 CS_Workflow_PUB.Decode_Action_Itemkey
1716 ( p_api_version => 1.0,
1717 p_return_status => l_return_status,
1718 p_msg_count => l_msg_count,
1719 p_msg_data => l_msg_data,
1720 p_itemkey => itemkey,
1721 p_request_id => l_request_id,
1722 p_action_number => l_action_number,
1723 p_wf_process_id => l_dummy
1724 );
1725 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1726 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count,
1727 p_data => l_msg_data,
1728 p_encoded => FND_API.G_FALSE );
1729 WF_CORE.Context( pkg_name => 'CS_Workflow_PUB',
1730 proc_name => 'Decode_Action_Itemkey',
1731 arg1 => 'p_itemkey=>'||itemkey,
1732 arg2 => 'p_msg_data=>'||l_msg_data );
1733 l_errmsg_name := 'CS_WF_SR_CANT_DECODE_ITEMKEY';
1734 RAISE l_exc_api_error;
1735 END IF;
1736
1737 -- Extract the service request action record
1738 OPEN l_action_csr;
1739 FETCH l_action_csr INTO l_action_rec;
1740 CLOSE l_action_csr;
1741
1742 --
1743 -- Set request ID first, in case if error occur, the workflow
1744 -- administrator can drilldown to the service request form from the error
1745 -- notification
1746 --
1747 WF_ENGINE.SetItemAttrNumber
1748 ( itemtype => itemtype,
1749 itemkey => itemkey,
1750 aname => 'REQUEST_ID',
1751 avalue => l_action_rec.incident_id
1752 );
1753
1754 -- Retrieve the role name for the request action assignee
1755 CS_Workflow_PUB.Get_Employee_Role
1756 ( p_api_version => 1.0,
1757 p_return_status => l_return_status,
1758 p_msg_count => l_msg_count,
1759 p_msg_data => l_msg_data,
1760 p_employee_id => l_action_rec.action_assignee_id,
1761 p_role_name => l_assignee_role,
1762 p_role_display_name => l_assignee_name
1763 );
1764 IF ((l_return_status <> FND_API.G_RET_STS_SUCCESS) OR
1765 (l_assignee_role IS NULL)) THEN
1766 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count,
1767 p_data => l_msg_data,
1768 p_encoded => FND_API.G_FALSE );
1769 WF_CORE.Context( pkg_name => 'CS_Workflow_PUB',
1770 proc_name => 'Get_Employee_Role',
1771 arg1 => 'p_employee_id=>'||l_action_rec.action_assignee_id,
1772 arg2 => 'p_msg_data=>'||l_msg_data );
1773 l_errmsg_name := 'CS_WF_SR_CANT_FIND_OWNER';
1774 RAISE l_exc_api_error;
1775 END IF;
1776
1777 -- Initialize item attributes that will remain constant
1778 WF_ENGINE.SetItemAttrNumber
1779 ( itemtype => itemtype,
1780 itemkey => itemkey,
1781 aname => 'REQUEST_ACTION_ID',
1782 avalue => l_action_rec.incident_action_id
1783 );
1784
1785 WF_ENGINE.SetItemAttrText
1786 ( itemtype => itemtype,
1787 itemkey => itemkey,
1788 aname => 'REQUEST_NUMBER',
1789 avalue => l_action_rec.incident_number
1790 );
1791
1792 WF_ENGINE.SetItemAttrNumber
1793 ( itemtype => itemtype,
1794 itemkey => itemkey,
1795 aname => 'ACTION_NUMBER',
1796 avalue => l_action_rec.action_number
1797 );
1798
1799 WF_ENGINE.SetItemAttrDate
1800 ( itemtype => itemtype,
1801 itemkey => itemkey,
1802 aname => 'ACTION_DATE',
1803 avalue => l_action_rec.action_date
1804 );
1805
1806 WF_ENGINE.SetItemAttrText
1807 ( itemtype => itemtype,
1808 itemkey => itemkey,
1809 aname => 'ACTION_TYPE',
1810 avalue => l_action_rec.action_type
1811 );
1812
1813 WF_ENGINE.SetItemAttrText
1814 ( itemtype => itemtype,
1815 itemkey => itemkey,
1816 aname => 'ACTION_STATUS',
1817 avalue => l_action_rec.action_status
1818 );
1819
1820 WF_ENGINE.SetItemAttrText
1821 ( itemtype => itemtype,
1822 itemkey => itemkey,
1823 aname => 'ACTION_SEVERITY',
1824 avalue => l_action_rec.action_severity
1825 );
1826
1827 WF_ENGINE.SetItemAttrNumber
1828 ( itemtype => itemtype,
1829 itemkey => itemkey,
1830 aname => 'ASSIGNEE_ID',
1831 avalue => l_action_rec.action_assignee_id
1832 );
1833
1834 WF_ENGINE.SetItemAttrText
1835 ( itemtype => itemtype,
1836 itemkey => itemkey,
1837 aname => 'ASSIGNEE_ROLE',
1838 avalue => l_assignee_role
1839 );
1840
1841 WF_ENGINE.SetItemAttrText
1842 ( itemtype => itemtype,
1843 itemkey => itemkey,
1844 aname => 'ASSIGNEE_NAME',
1845 avalue => l_assignee_name
1846 );
1847
1848 WF_ENGINE.SetItemAttrText
1849 ( itemtype => itemtype,
1850 itemkey => itemkey,
1851 aname => 'ACTION_SUMMARY',
1852 avalue => l_action_rec.action_summary
1853 );
1854
1855 WF_ENGINE.SetItemAttrText
1856 ( itemtype => itemtype,
1857 itemkey => itemkey,
1858 aname => 'REQUEST_CUSTOMER',
1859 avalue => l_action_rec.incident_customer
1860 );
1861
1862 WF_ENGINE.SetItemAttrNumber
1863 ( itemtype => itemtype,
1864 itemkey => itemkey,
1865 aname => 'CUSTOMER_PRODUCT_ID',
1866 avalue => l_action_rec.customer_product_id
1867 );
1868
1869 WF_ENGINE.SetItemAttrNumber
1870 ( itemtype => itemtype,
1871 itemkey => itemkey,
1872 aname => 'INVENTORY_ITEM_ID',
1873 avalue => l_action_rec.inventory_item_id
1874 );
1875
1876 WF_ENGINE.SetItemAttrText
1877 ( itemtype => itemtype,
1878 itemkey => itemkey,
1879 aname => 'PRODUCT_DESCRIPTION',
1880 avalue => l_action_rec.product_description
1881 );
1882
1883 WF_ENGINE.SetItemAttrText
1884 ( itemtype => itemtype,
1885 itemkey => itemkey,
1886 aname => 'REQUEST_LOCATION',
1887 avalue => l_action_rec.incident_location
1888 );
1889
1890 WF_ENGINE.SetItemAttrText
1891 ( itemtype => itemtype,
1892 itemkey => itemkey,
1893 aname => 'ACTION_DESCRIPTION',
1894 avalue => l_action_rec.action_description
1895 );
1896
1897 WF_ENGINE.SetItemAttrDate
1898 ( itemtype => itemtype,
1899 itemkey => itemkey,
1900 aname => 'EXPECTED_RESOLUTION_DATE',
1901 avalue => l_action_rec.expected_resolution_date
1902 );
1903
1904 result := 'COMPLETE';
1905
1906 ELSIF (funcmode = 'CANCEL') THEN
1907 result := 'COMPLETE';
1908 END IF;
1909
1910 EXCEPTION
1911 WHEN l_exc_api_error THEN
1912 WF_CORE.Raise(l_errmsg_name);
1913 WHEN OTHERS THEN
1914 WF_CORE.Context('CS_WF_ACTIVITIES_PKG', 'Initialize_Action',
1915 itemtype, itemkey, actid, funcmode);
1916 RAISE;
1917 END Initialize_Action;*/
1918
1919
1920 ----------------------------------------------------------------------
1921 -- Is_Launched_From_Dispatch
1922 -- Return 'Y' if the Workflow is launched from the Field Service
1923 -- Dispatch Window; otherwise, return 'N'.
1924 ----------------------------------------------------------------------
1925
1926 PROCEDURE Is_Launched_From_Dispatch
1927 ( itemtype IN VARCHAR2,
1928 itemkey IN VARCHAR2,
1929 actid IN NUMBER,
1930 funcmode IN VARCHAR2,
1931 result OUT NOCOPY VARCHAR2
1932 )
1933 IS
1934 l_launched_by_dispatch VARCHAR2(1);
1935 BEGIN
1936 IF (funcmode = 'RUN') THEN
1937 l_launched_by_dispatch := WF_ENGINE.GetItemAttrText
1938 ( itemtype => itemtype,
1939 itemkey => itemkey,
1940 aname => 'LAUNCHED_BY_DISPATCH'
1941 );
1942 IF FND_API.To_Boolean(l_launched_by_dispatch) THEN
1943 result := 'COMPLETE:Y';
1944 ELSE
1945 result := 'COMPLETE:N';
1946 END IF;
1947
1948 ELSIF (funcmode = 'CANCEL') THEN
1949 result := 'COMPLETE';
1950 END IF;
1951
1952 EXCEPTION
1953 WHEN OTHERS THEN
1954 WF_CORE.Context('CS_WF_ACTIVITIES_PKG', 'Is_Launched_From_Dispatch',
1955 itemtype, itemkey, actid, funcmode);
1956 RAISE;
1957 END Is_Launched_From_Dispatch;
1958
1959
1960 --------------------------------------------------------------------------
1961 -- Get_Dispatcher_Info
1962 -- Populate the item attributes with info of the dispatch person from
1963 -- the database.
1964 --------------------------------------------------------------------------
1965
1966 /*PROCEDURE Get_Dispatcher_Info
1967 ( itemtype IN VARCHAR2,
1968 itemkey IN VARCHAR2,
1969 actid IN NUMBER,
1970 funcmode IN VARCHAR2,
1971 result OUT VARCHAR2
1972 )
1973 IS
1974 l_request_id NUMBER;
1975 l_action_number NUMBER;
1976 l_orig_system VARCHAR2(14);
1977 l_orig_system_id NUMBER;
1978 l_role_name VARCHAR2(100);
1979 l_display_name VARCHAR2(240);
1980 l_errmsg_name VARCHAR2(30);
1981
1982 CURSOR l_action_csr IS
1983 SELECT dispatcher_orig_syst, dispatcher_orig_syst_id, dispatch_role_name
1984 FROM cs_inc_actions_workflow_v
1985 WHERE incident_id = l_request_id
1986 AND action_number = l_action_number;
1987
1988 CURSOR l_dispatch_csr IS
1989 SELECT display_name
1990 FROM wf_roles
1991 WHERE name = l_role_name;
1992
1993 l_exc_invalid_role EXCEPTION;
1994
1995 BEGIN
1996 IF (funcmode = 'RUN') THEN
1997 -- Get the service request ID and action number
1998 l_request_id := WF_ENGINE.GetItemAttrText
1999 ( itemtype => itemtype,
2000 itemkey => itemkey,
2001 aname => 'REQUEST_ID'
2002 );
2003 l_action_number := WF_ENGINE.GetItemAttrText
2004 ( itemtype => itemtype,
2005 itemkey => itemkey,
2006 aname => 'ACTION_NUMBER'
2007 );
2008
2009 -- Extract the info of the dispatch person
2010 OPEN l_action_csr;
2011 FETCH l_action_csr INTO l_orig_system, l_orig_system_id, l_role_name;
2012 CLOSE l_action_csr;
2013
2014 -- Retrieve the display name for the dispatch person
2015 -- Make sure the dispatch role is defined in the Workflow directory
2016 OPEN l_dispatch_csr;
2017 FETCH l_dispatch_csr INTO l_display_name;
2018 IF (l_dispatch_csr%NOTFOUND) THEN
2019 CLOSE l_dispatch_csr;
2020 l_errmsg_name := 'CS_WF_SR_CANT_FIND_DISPATCHER';
2021 RAISE l_exc_invalid_role;
2022 END IF;
2023 CLOSE l_dispatch_csr;
2024
2025 -- Update service request action item attributes
2026 WF_ENGINE.SetItemAttrText
2027 ( itemtype => itemtype,
2028 itemkey => itemkey,
2029 aname => 'DISPATCHER_ORIG_SYST',
2030 avalue => l_orig_system
2031 );
2032
2033 WF_ENGINE.SetItemAttrNumber
2034 ( itemtype => itemtype,
2035 itemkey => itemkey,
2036 aname => 'DISPATCHER_ORIG_SYST_ID',
2037 avalue => l_orig_system_id
2038 );
2039
2040 WF_ENGINE.SetItemAttrText
2041 ( itemtype => itemtype,
2042 itemkey => itemkey,
2043 aname => 'DISPATCHER_ROLE',
2044 avalue => l_role_name
2045 );
2046
2047 WF_ENGINE.SetItemAttrText
2048 ( itemtype => itemtype,
2049 itemkey => itemkey,
2050 aname => 'DISPATCHER_NAME',
2051 avalue => l_display_name
2052 );
2053
2054 result := 'COMPLETE';
2055
2056 ELSIF (funcmode = 'CANCEL') THEN
2057 result := 'COMPLETE';
2058 END IF;
2059
2060 EXCEPTION
2061 WHEN l_exc_invalid_role THEN
2062 WF_CORE.Raise(l_errmsg_name);
2063 WHEN OTHERS THEN
2064 WF_CORE.Context('CS_WF_ACTIVITIES_PKG', 'Get_Dispatcher_Info',
2065 itemtype, itemkey, actid, funcmode);
2066 RAISE;
2067 END Get_Dispatcher_Info;*/
2068
2069 -- ***************************************************************************
2070 -- * *
2071 -- * FIELD SERVICE DISPATCH ACTIVITIES *
2072 -- * *
2073 -- ***************************************************************************
2074
2075 PROCEDURE IS_MOBILE_INSTALLED (
2076 itemtype IN VARCHAR2,
2077 itemkey IN VARCHAR2,
2078 actid IN NUMBER,
2079 funcmode IN VARCHAR2,
2080 result OUT NOCOPY VARCHAR2
2081 ) IS
2082 l_mobile_installed VARCHAR2(1);
2083 BEGIN
2084 IF (funcmode = 'RUN') THEN
2085 SELECT NVL(UPPER(USE_MOBILE_FLD_SRV_FLAG) , 'N')
2086 INTO l_mobile_installed
2087 FROM CS_SYSTEM_PARAMETERS;
2088
2089 IF l_mobile_installed = 'Y' THEN
2090 result := 'COMPLETE:Y';
2091 ELSE
2092 result := 'COMPLETE:N';
2093 END IF;
2094
2095 ELSIF (funcmode = 'CANCEL') THEN
2096 result := 'COMPLETE';
2097 END IF;
2098
2099 EXCEPTION
2100 WHEN OTHERS THEN
2101 WF_CORE.Context('CS_WF_ACTIVITIES_PKG', 'IS_MOBILE_INSTALLED',
2102 itemtype, itemkey, actid, funcmode);
2103 RAISE;
2104 END IS_MOBILE_INSTALLED;
2105
2106
2107 /*PROCEDURE IS_ACTION_CLOSED (
2108 itemtype IN VARCHAR2,
2109 itemkey IN VARCHAR2,
2110 actid IN NUMBER,
2111 funcmode IN VARCHAR2,
2112 result OUT VARCHAR2
2113 ) IS
2114 l_request_id CS_INCIDENT_ACTIONS.INCIDENT_ID%TYPE;
2115 l_action_number CS_INCIDENT_ACTIONS.ACTION_NUM%TYPE;
2116 l_close_flag CS_INCIDENT_STATUSES.CLOSE_FLAG%TYPE;
2117
2118 CURSOR l_stat_cur IS
2119 SELECT b.close_flag
2120 FROM CS_INCIDENT_ACTIONS a, CS_INCIDENT_STATUSES b
2121 WHERE a.incident_id = l_request_id AND
2122 a.action_num = l_action_number AND
2123 a.action_status_id = b.incident_status_id;
2124
2125 BEGIN
2126 IF (funcmode = 'RUN') THEN
2127 -- Get the service request ID and action number
2128 l_request_id := WF_ENGINE.GetItemAttrText (
2129 itemtype => itemtype,
2130 itemkey => itemkey,
2131 aname => 'REQUEST_ID');
2132
2133 l_action_number := WF_ENGINE.GetItemAttrText (
2134 itemtype => itemtype,
2135 itemkey => itemkey,
2136 aname => 'ACTION_NUMBER');
2137
2138 OPEN l_stat_cur;
2139 FETCH l_stat_cur INTO l_close_flag;
2140 CLOSE l_stat_cur;
2141
2142 IF l_close_flag = 'Y' THEN
2143 result := 'COMPLETE:Y';
2144 ELSE
2145 result := 'COMPLETE:N';
2146 END IF;
2147
2148 ELSIF (funcmode = 'CANCEL') THEN
2149 result := 'COMPLETE';
2150 END IF;
2151
2152 EXCEPTION
2153 WHEN OTHERS THEN
2154 IF l_stat_cur%ISOPEN THEN
2155 CLOSE l_stat_cur;
2156 END IF;
2157
2158 WF_CORE.Context('CS_WF_ACTIVITIES_PKG', 'IS_ACTION_CLOSED',
2159 itemtype, itemkey, actid, funcmode);
2160 RAISE;
2161 END IS_ACTION_CLOSED;*/
2162
2163 PROCEDURE IS_FS_INSERT (
2164 itemtype IN VARCHAR2,
2165 itemkey IN VARCHAR2,
2166 actid IN NUMBER,
2167 funcmode IN VARCHAR2,
2168 result OUT NOCOPY VARCHAR2
2169 ) IS
2170 l_fs_mode VARCHAR2(10) := NULL;
2171 BEGIN
2172
2173 --- This attribute identifies whether to do insert or update
2174 --- on the interface table
2175
2176 l_fs_mode := WF_ENGINE.GetItemAttrText (
2177 itemtype => itemtype,
2178 itemkey => itemkey,
2179 aname => 'FS_INTERFACE_MODE');
2180 IF (funcmode = 'RUN') THEN
2181
2182 IF l_fs_mode is NULL THEN
2183 result := 'COMPLETE:Y';
2184 ELSE
2185 result := 'COMPLETE:N';
2186 END IF;
2187 ELSIF (funcmode = 'CANCEL') THEN
2188 result := 'COMPLETE';
2189 END IF;
2190 EXCEPTION
2191 WHEN OTHERS THEN
2192 WF_CORE.Context('CS_WF_ACTIVITIES_PKG', 'IS_FS_INSERT',
2193 itemtype, itemkey, actid, funcmode);
2194 RAISE;
2195 END IS_FS_INSERT;
2196
2197
2198 -- Removed on 12/22/1999 since table CS_MFS_INTERFACE table is no longer present.
2199 /*PROCEDURE INSERT_FS_INTERFACE (
2200 itemtype IN VARCHAR2,
2201 itemkey IN VARCHAR2,
2202 actid IN NUMBER,
2203 funcmode IN VARCHAR2,
2204 result OUT VARCHAR2) IS
2205
2206 action_rec fs_action_csr%ROWTYPE;
2207 l_fs_interface_id NUMBER;
2208
2209 ACTION_NOT_FOUND EXCEPTION;
2210 l_errmsg_name VARCHAR2(30);
2211 l_incident_id NUMBER;
2212 l_incident_action_id NUMBER;
2213 l_response VARCHAR2(80) := NULL ;
2214 BEGIN
2215 l_incident_id := WF_ENGINE.GetItemAttrNumber (
2216 itemtype => itemtype,
2217 itemkey => itemkey,
2218 aname => 'REQUEST_ID');
2219
2220 l_incident_action_id := WF_ENGINE.GetItemAttrNumber (
2221 itemtype => itemtype,
2222 itemkey => itemkey,
2223 aname => 'REQUEST_ACTION_ID');
2224
2225
2226 OPEN fs_action_csr(l_incident_id, l_incident_action_id);
2227 FETCH fs_action_csr into action_rec;
2228
2229 IF fs_action_csr%NOTFOUND THEN
2230 wf_core.context(
2231 pkg_name => 'CS_WF_ACTIVITIES_PKG',
2232 proc_name => 'INSERT_FS_INTERFACE',
2233 arg1 => 'p_itemkey=>'||itemkey );
2234 l_errmsg_name := 'CS_WF_SR_ACTION_NOT_FOUND';
2235 RAISE ACTION_NOT_FOUND;
2236 END IF;
2237
2238 SELECT CS_MFS_INTERFACE_S.nextval
2239 INTO l_fs_interface_id FROM DUAL;
2240
2241 INSERT INTO CS_MFS_INTERFACE
2242 (
2243 FIELD_SERVICE_INTERFACE_ID,
2244 CREATION_DATE,
2245 CREATED_BY,
2246 LAST_UPDATE_DATE,
2247 LAST_UPDATED_BY,
2248 LAST_UPDATE_LOGIN,
2249 STATUS_FLAG,
2250 ORG_ID,
2251 INCIDENT_NUMBER,
2252 ACTION_NUM,
2253 INCIDENT_DATE,
2254 BUSINESS_PROCESS_ID,
2255 PROBLEM_CODE,
2256 PROBLEM_DESCRIPTION,
2257 RESOLUTION_CODE,
2258 RESOLUTION_DESCRIPTION,
2259 INCIDENT_URGENCY_ID,
2260 URGENCY,
2261 INCIDENT_STATUS_ID,
2262 STATUS_CODE,
2263 STATUS_DATE,
2264 INCIDENT_TYPE_ID,
2265 INCIDENT_TYPE,
2266 DISPATCHER_ID,
2267 DISPATCHER_NAME,
2268 COVERED_BY_CONTRACT,
2269 CURRENT_CONTACT_NAME,
2270 CURRENT_CONTACT_TELEPHONE,
2271 CURRENT_CONTACT_AREA_CODE,
2272 CURRENT_CONTACT_EXTENSION,
2273 CURRENT_CONTACT_FAX_NUMBER,
2274 CURRENT_CONTACT_FAX_AREA_CODE,
2275 CURRENT_CONTACT_EMAIL_ADDRESS,
2276 SHIP_TO_ADDRESS_LINE1,
2277 SHIP_TO_ADDRESS_LINE2,
2278 POSTAL_CODE,
2279 CITY,
2280 STATE,
2281 COUNTRY,
2282 START_TIME,
2283 END_TIME,
2284 EARLIEST_START_TIME,
2285 LATEST_FINISH_TIME,
2286 APPOINTMENT,
2287 REQUEST_DURATION,
2288 EMPLOYEE_ID,
2289 EMPLOYEE_NAME,
2290 CUSTOMER_ID,
2291 CUSTOMER_NUMBER,
2292 --CUSTOMER_NAME,
2293 INVENTORY_ITEM_ID,
2294 PRODUCT,
2295 PRODUCT_DESCRIPTION,
2296 ORGANIZATION_ID,
2297 REFERENCE_NUMBER,
2298 CURRENT_SERIAL_NUMBER,
2299 INSTALLATION_DATE,
2300 ATTRIBUTE1,
2301 ATTRIBUTE2,
2302 ATTRIBUTE3,
2303 ATTRIBUTE4,
2304 ATTRIBUTE5,
2305 ATTRIBUTE6,
2306 ATTRIBUTE7,
2307 ATTRIBUTE8,
2308 ATTRIBUTE9,
2309 ATTRIBUTE10,
2310 ATTRIBUTE11,
2311 ATTRIBUTE12,
2312 ATTRIBUTE13,
2313 ATTRIBUTE14,
2314 ATTRIBUTE15,
2315 CONTEXT,
2316 SR_PROBLEM_DESCRIPTION,
2317 SR_RESOLUTION_DESCRIPTION,
2318 incident_severity_id,
2319 incident_severity_name,
2320 inc_prob_description,
2321 action_status_id,
2322 problem_summary)
2323 VALUES(
2324 l_fs_interface_id,
2325 sysdate,
2326 FND_GLOBAL.user_id,
2327 sysdate,
2328 FND_GLOBAL.user_id,
2329 FND_GLOBAL.login_id,
2330 '1',
2331 NULL,
2332 action_rec.incident_number,
2333 action_rec.action_num,
2334 action_rec.incident_date,
2335 action_rec.business_process_id,
2336 action_rec.problem_code,
2337 --action_rec.problem_description,
2338 action_rec.resolution_code,
2339 action_rec.resolution_description,
2340 action_rec.incident_urgency_id,
2341 action_rec.urgency,
2342 action_rec.INCIDENT_STATUS_ID,
2343 action_rec.STATUS_CODE,
2344 action_rec.status_date,
2345 action_rec.incident_type_id,
2346 action_rec.incident_type,
2347 action_rec.dispatcher_id,
2348 action_rec.dispatcher_name,
2349 action_rec.covered_by_contract,
2350 action_rec.current_contact_name,
2351 action_rec.current_contact_telephone,
2352 action_rec.current_contact_area_code,
2353 action_rec.current_contact_extension,
2354 action_rec.current_contact_fax_number,
2355 action_rec.current_contact_fax_area_code,
2356 action_rec.current_contact_email_address,
2357 action_rec.ship_to_address_line1,
2358 action_rec.ship_to_address_line2,
2359 action_rec.postal_code,
2360 action_rec.city,
2361 action_rec.state,
2362 action_rec.country,
2363 action_rec.start_time,
2364 action_rec.end_time,
2365 action_rec.earliest_start_time,
2366 action_rec.latest_finish_time,
2367 action_rec.appointment,
2368 action_rec.request_duration,
2369 action_rec.employee_id,
2370 wf_directory.getroledisplayname(action_rec.employee_name),
2371 action_rec.customer_id,
2372 action_rec.customer_number,
2373 --action_rec.customer_name,
2374 action_rec.inventory_item_id,
2375 action_rec.product,
2376 action_rec.product_description,
2377 action_rec.organization_id,
2378 action_rec.reference_number,
2379 action_rec.current_serial_number,
2380 action_rec.installation_date,
2381 NULL,
2382 NULL,
2383 NULL,
2384 NULL,
2385 NULL,
2386 NULL,
2387 NULL,
2388 NULL,
2389 NULL,
2390 NULL,
2391 NULL,
2392 NULL,
2393 NULL,
2394 NULL,
2395 NULL,
2396 NULL,
2397 -- action_rec.sr_problem_description,
2398 action_rec.sr_resolution_description,
2399 action_rec.incident_severity_id,
2400 action_rec.incident_severity_name,
2401 action_rec.inc_prob_description,
2402 action_rec.action_status_id,
2403 action_rec.problem_summary);
2404 -- Set the mode to updated to have the workflow update this record
2405 -- when it loops through next time
2406
2407 WF_ENGINE.SetItemAttrText(
2408 itemtype => itemtype,
2409 itemkey => itemkey,
2410 aname => 'FS_INTERFACE_MODE',
2411 avalue => 'CREATED');
2412
2413 WF_ENGINE.SetItemAttrNumber(
2414 itemtype => itemtype,
2415 itemkey => itemkey,
2416 aname => 'FS_INTERFACE_ID',
2417 avalue => l_fs_interface_id);
2418 CLOSE fs_action_csr;
2419 EXCEPTION
2420 WHEN ACTION_NOT_FOUND THEN
2421 WF_CORE.Raise(l_errmsg_name);
2422 IF fs_action_csr%ISOPEN THEN
2423 CLOSE fs_action_csr;
2424 END IF;
2425 WHEN OTHERS THEN
2426 WF_CORE.Context('CS_WF_ACTIVITIES_PKG', 'INSERT_FS_INTERFACE',
2427 itemtype, itemkey, actid, funcmode);
2428 IF fs_action_csr%ISOPEN THEN
2429 CLOSE fs_action_csr;
2430 END IF;
2431 RAISE;
2432 END INSERT_FS_INTERFACE;*/
2433
2434 /*PROCEDURE UPDATE_FS_INTERFACE (
2435 itemtype IN VARCHAR2,
2436 itemkey IN VARCHAR2,
2437 actid IN NUMBER,
2438 funcmode IN VARCHAR2,
2439 result OUT VARCHAR2) IS
2440
2441 action_rec fs_action_csr%ROWTYPE;
2442 l_fs_interface_id NUMBER;
2443
2444 ACTION_NOT_FOUND EXCEPTION;
2445 l_errmsg_name VARCHAR2(30);
2446 l_incident_id NUMBER;
2447 l_incident_action_id NUMBER;
2448
2449 BEGIN
2450 l_incident_id := WF_ENGINE.GetItemAttrNumber (
2451 itemtype => itemtype,
2452 itemkey => itemkey,
2453 aname => 'REQUEST_ID');
2454
2455 l_incident_action_id := WF_ENGINE.GetItemAttrNumber (
2456 itemtype => itemtype,
2457 itemkey => itemkey,
2458 aname => 'REQUEST_ACTION_ID');
2459
2460
2461 OPEN fs_action_csr(l_incident_id, l_incident_action_id);
2462 FETCH fs_action_csr into action_rec;
2463
2464 IF fs_action_csr%NOTFOUND THEN
2465 wf_core.context(
2466 pkg_name => 'CS_WF_ACTIVITIES_PKG',
2467 proc_name => 'UPDATE_FS_INTERFACE',
2468 arg1 => 'p_itemkey=>'||itemkey );
2469 l_errmsg_name := 'CS_WF_SR_ACTION_NOT_FOUND';
2470 RAISE ACTION_NOT_FOUND;
2471 END IF;
2472
2473 l_fs_interface_id := WF_ENGINE.GetItemAttrNumber (
2474 itemtype => itemtype,
2475 itemkey => itemkey,
2476 aname => 'FS_INTERFACE_ID');
2477
2478 UPDATE CS_MFS_INTERFACE
2479 SET
2480 LAST_UPDATE_DATE = sysdate,
2481 LAST_UPDATED_BY = FND_GLOBAL.user_id,
2482 LAST_UPDATE_LOGIN = FND_GLOBAL.login_id,
2483 STATUS_FLAG = 1,
2484 INCIDENT_NUMBER =action_rec.incident_number,
2485 ACTION_NUM =action_rec.action_num,
2486 INCIDENT_DATE =action_rec.incident_date,
2487 BUSINESS_PROCESS_ID =action_rec.business_process_id,
2488 PROBLEM_CODE =action_rec.problem_code,
2489 --PROBLEM_DESCRIPTION =action_rec.problem_description,
2490 RESOLUTION_CODE =action_rec.resolution_code,
2491 RESOLUTION_DESCRIPTION =action_rec.resolution_description,
2492 INCIDENT_URGENCY_ID =action_rec.incident_urgency_id,
2493 URGENCY =action_rec.urgency,
2494 INCIDENT_STATUS_ID =action_rec.INCIDENT_STATUS_ID,
2495 STATUS_CODE =action_rec.STATUS_CODE,
2496 STATUS_DATE =action_rec.status_date,
2497 INCIDENT_TYPE_ID =action_rec.incident_type_id,
2498 INCIDENT_TYPE =action_rec.incident_type,
2499 DISPATCHER_ID = action_rec.dispatcher_id,
2500 DISPATCHER_NAME = action_rec.dispatcher_name,
2501 COVERED_BY_CONTRACT =action_rec.covered_by_contract,
2502 CURRENT_CONTACT_NAME = action_rec.current_contact_name,
2503 CURRENT_CONTACT_TELEPHONE = action_rec.current_contact_telephone,
2504 CURRENT_CONTACT_AREA_CODE = action_rec.current_contact_area_code,
2505 CURRENT_CONTACT_EXTENSION =action_rec.current_contact_extension,
2506 CURRENT_CONTACT_FAX_NUMBER =action_rec.current_contact_fax_number,
2507 CURRENT_CONTACT_FAX_AREA_CODE =action_rec.current_contact_fax_area_code,
2508 CURRENT_CONTACT_EMAIL_ADDRESS =action_rec.current_contact_email_address,
2509 SHIP_TO_ADDRESS_LINE1 =action_rec.ship_to_address_line1,
2510 SHIP_TO_ADDRESS_LINE2 = action_rec.ship_to_address_line2,
2511 POSTAL_CODE =action_rec.postal_code,
2512 CITY =action_rec.city,
2513 STATE =action_rec.state,
2514 COUNTRY =action_rec.country,
2515 START_TIME =action_rec.start_time,
2516 END_TIME =action_rec.end_time,
2517 EARLIEST_START_TIME =action_rec.earliest_start_time,
2518 LATEST_FINISH_TIME =action_rec.latest_finish_time,
2519 APPOINTMENT =action_rec.appointment,
2520 REQUEST_DURATION =action_rec.request_duration,
2521 EMPLOYEE_ID =action_rec.employee_id,
2522 EMPLOYEE_NAME =wf_directory.getroledisplayname(action_rec.employee_name),
2523 CUSTOMER_ID =action_rec.customer_id,
2524 CUSTOMER_NUMBER =action_rec.customer_number,
2525 --CUSTOMER_NAME =action_rec.customer_name,
2526 INVENTORY_ITEM_ID =action_rec.inventory_item_id,
2527 PRODUCT =action_rec.product,
2528 PRODUCT_DESCRIPTION =action_rec.product_description,
2529 ORGANIZATION_ID = action_rec.organization_id,
2530 REFERENCE_NUMBER =action_rec.reference_number,
2531 CURRENT_SERIAL_NUMBER =action_rec.current_serial_number,
2532 INSTALLATION_DATE =action_rec.installation_date
2533 WHERE FIELD_SERVICE_INTERFACE_ID = l_fs_interface_id;
2534
2535 WF_ENGINE.SetItemAttrText(
2536 itemtype => itemtype,
2537 itemkey => itemkey,
2538 aname => 'FS_INTERFACE_MODE',
2539 avalue => 'UPDATED');
2540 CLOSE fs_action_csr;
2541
2542 EXCEPTION
2543 WHEN ACTION_NOT_FOUND THEN
2544 WF_CORE.Raise(l_errmsg_name);
2545 IF fs_action_csr%ISOPEN THEN
2546 CLOSE fs_action_csr;
2547 END IF;
2548 WHEN OTHERS THEN
2549 WF_CORE.Context('CS_WF_ACTIVITIES_PKG', 'UPDATE_FS_INTERFACE',
2550 itemtype, itemkey, actid, funcmode);
2551 IF fs_action_csr%ISOPEN THEN
2552 CLOSE fs_action_csr;
2553 END IF;
2554 RAISE;
2555 END UPDATE_FS_INTERFACE;*/
2556
2557
2558
2559 /*PROCEDURE SET_FS_WF_RESPONSE(
2560 p_incident_number IN NUMBER,
2561 p_action_number IN NUMBER,
2562 p_response IN VARCHAR2,
2563 x_return_status OUT NOCOPY VARCHAR2,
2564 x_msg_data OUT NOCOPY VARCHAR2) IS
2565
2566 l_wf_id NUMBER ;
2567 l_wf_key VARCHAR2(2000) ;
2568 l_wf_active VARCHAR2(1) := NULL ;
2569 WF_NOT_ACTIVE EXCEPTION;
2570 l_incident_id CS_INCIDENTS.INCIDENT_ID%TYPE;
2571 x_msg_count NUMBER;
2572
2573 BEGIN
2574 -- Initialize the return status to Success
2575 x_return_status := FND_API.G_RET_STS_SUCCESS;
2576
2577 SELECT inc.incident_id,
2578 act.workflow_process_id,
2579 CS_WORKFLOW_PKG.IS_ACTION_ITEM_ACTIVE(
2580 act.incident_id,
2581 act.action_num,
2582 act.workflow_process_id)
2583 INTO l_incident_id,l_wf_id,l_wf_active
2584 FROM CS_INCIDENT_ACTIONS ACT, CS_INCIDENTS_ALL_B INC
2585 WHERE inc.incident_id = act.incident_id
2586 AND inc.incident_number = p_incident_number
2587 AND act.action_num = p_action_number ;
2588
2589 l_wf_key := to_char(l_incident_id) || '-' ||
2590 to_char(p_action_number) || '-' || to_char(l_wf_id) ;
2591 --dbms_output.put_line('ACTIVE ' || l_wf_active || ' ' || l_wf_key);
2592
2593 IF l_wf_active = 'Y' THEN
2594 WF_ENGINE.CompleteActivity (
2595 itemtype => g_fs_itemtype,
2596 itemkey => l_wf_key,
2597 activity => g_fs_activity,
2598 result => p_response);
2599 ELSE
2600 RAISE WF_NOT_ACTIVE;
2601 END IF ;
2602
2603 EXCEPTION
2604 WHEN WF_NOT_ACTIVE THEN
2605 x_return_status := FND_API.G_RET_STS_ERROR;
2606 FND_MESSAGE.Set_Name('CS', 'CS_SR_WORKFLOW_NOT_ACTIVE');
2607 FND_MSG_PUB.Add;
2608 FND_MSG_PUB.Count_And_Get(
2609 p_count => x_msg_count,
2610 p_data => x_msg_data,
2611 p_encoded => FND_API.G_FALSE );
2612 WHEN OTHERS THEN
2613 x_return_status := FND_API.G_RET_STS_ERROR;
2614 FND_MESSAGE.Set_Name('CS', sqlerrm);
2615 FND_MSG_PUB.Add;
2616 FND_MSG_PUB.Count_And_Get(
2617 p_count => x_msg_count,
2618 p_data => x_msg_data,
2619 p_encoded => FND_API.G_FALSE );
2620
2621 END SET_FS_WF_RESPONSE; */
2622
2623 -- ---------------------------
2624 -- Initialization of package
2625 -- ---------------------------
2626
2627 BEGIN
2628
2629 --
2630 -- We need to get the audit comments from the message dictionary
2631 -- due to translation issues
2632 --
2633 FND_MESSAGE.Set_Name('CS', 'CS_WF_AUDIT_COMMENTS');
2634 CS_WF_ACTIVITIES_PKG.Audit_Comments := FND_MESSAGE.Get;
2635
2636 END CS_WF_ACTIVITIES_PKG;