[Home] [Help]
PACKAGE BODY: APPS.OKE_COMM_ACT_UTILS
Source
1 PACKAGE BODY OKE_COMM_ACT_UTILS AS
2 /* $Header: OKEACTUB.pls 120.2 2008/04/14 09:43:15 serukull ship $ */
3
4 --
5 -- Name : Update_Text
6 -- Pre-reqs : None
7 -- Function : This procedure updates communication text
8 --
9 PROCEDURE Update_Text(
10 X_k_header_id NUMBER,
11 X_communication_num VARCHAR2,
12 X_text VARCHAR2
13 ) is
14
15 begin
16
17 update OKE_K_COMMUNICATIONS
18 set text = X_text
19 where k_header_id = X_k_header_id
20 and communication_num = X_communication_num;
21
22 end Update_Text;
23
24
25 --
26 -- Name : Action_Workflow
27 -- Pre-reqs : None
28 -- Function : This procedure launches the workflow when
29 -- a communication was created
30 -- or the communication action was changed
31 --
32 PROCEDURE Action_Workflow
33 ( P_K_Header_ID IN NUMBER
34 , P_K_Line_ID IN NUMBER
35 , P_Deliverable_ID IN NUMBER
36 , P_Communication_Num IN VARCHAR2
37 , P_Type_Name IN VARCHAR2
38 , P_Reason_Name IN VARCHAR2
39 , P_Party_Name IN VARCHAR2
40 , P_Party_Location IN VARCHAR2
41 , P_Party_Role IN VARCHAR2
42 , P_Party_Contact IN VARCHAR2
43 , P_New_Action_Code IN VARCHAR2
44 , P_Owner IN NUMBER
45 , P_Priority_Name IN VARCHAR2
46 , P_Communication_Date IN DATE
47 , P_Communication_Text IN VARCHAR2
48 , P_Updated_By IN NUMBER
49 , P_Update_Date IN DATE
50 , P_Login_ID IN NUMBER
51 , P_WF_ITEM_KEY OUT NOCOPY VARCHAR2
52 ) IS
53
54 CURSOR kh IS
55 SELECT k_number
56 ,k_type
57 ,authoring_org_id
58 FROM oke_k_headers_full_v
59 WHERE k_header_id = P_K_Header_ID;
60
61 CURSOR kl IS
62 SELECT line_number
63 FROM oke_k_lines_full_v
64 WHERE k_line_id = P_K_Line_ID;
65
66 CURSOR kd IS
67 SELECT deliverable_num
68 FROM oke_k_deliverables_vl
69 WHERE Deliverable_id = P_Deliverable_ID;
70
71 CURSOR o IS
72 SELECT user_name
73 FROM fnd_user
74 WHERE user_id = P_Updated_By;
75
76 CURSOR new_action IS
77 SELECT wf_item_type
78 , wf_process
79 , comm_action_name
80 FROM oke_comm_actions_vl
81 WHERE comm_action_code = P_New_Action_Code;
82
83 -- Jun 20, 2002 : bug 2435609. Person_id is not unique.
84 -- Add effective_start_date and effective_end_date.
85 -- Only one person can be active at one time.
86 --
87 -- Aut 08, 2003 : bug 3051397. Send notification to Action Owner
88 -- if action owner is a WF_ROLE (must be a FND_USER), otherwise send to
89 -- Requestor who log the communication.
90 -- Use wf_roiles instead of per_people_f
91 CURSOR p IS
92 SELECT name, display_name
93 FROM wf_roles
94 WHERE orig_system = 'PER'
95 AND orig_system_id = P_Owner;
96
97 l_wf_item_type VARCHAR2(8);
98 l_wf_process VARCHAR2(30);
99 l_wf_item_key VARCHAR2(240);
100 l_wf_user_key VARCHAR2(240);
101
102 -- Aug 08, 2003 Bug 3051397. Change variable length from 30 to 100
103 -- according to the column length of FND_USER
104 l_user_name VARCHAR2(100);
105 l_requestor VARCHAR2(100);
106
107 l_contract_num VARCHAR2(240);
108 l_k_type VARCHAR2(240);
109 l_line_number VARCHAR2(240);
110 l_dts_number VARCHAR2(240);
111 l_new_action VARCHAR2(240);
112 l_action_owner VARCHAR2(240);
113 l_org_id NUMBER;
114
115 BEGIN
116
117 OPEN new_action;
118 FETCH new_action INTO l_wf_item_type , l_wf_process , l_new_action;
119 CLOSE new_action;
120
121 IF ( l_wf_item_type IS NOT NULL and l_wf_process IS NOT NULL) THEN
122
123 OPEN kh;
124 FETCH kh INTO l_contract_num,l_k_type,l_org_id;
125 CLOSE kh;
126
127 OPEN kl;
128 FETCH kl INTO l_line_number;
129 CLOSE kl;
130
131 OPEN kd;
132 FETCH kd INTO l_dts_number;
133 CLOSE kd;
134
135 OPEN o;
136 FETCH o INTO l_user_name;
137 CLOSE o;
138
139 -- OPEN p;
140 -- FETCH p INTO l_action_owner;
141 -- CLOSE p;
142 -- Aut 08, 2003 : bug 3051397. Send notification to Action Owner
143 -- if action owner is a WF_ROLE, otherwise send to
144 -- Requestor who log the communication.
145
146 IF (P_Owner is null) THEN
147 l_requestor := l_user_name;
148 ELSE
149 OPEN p;
150 FETCH p INTO l_requestor, l_action_owner;
151
152 IF p%notfound THEN
153 l_requestor := l_user_name;
154 SELECT full_name into l_action_owner
155 FROM per_all_people_f
156 WHERE person_id = P_Owner
157 AND sysdate between effective_start_date and effective_end_date;
158 END IF;
159 CLOSE p;
160 END IF;
161
162 l_wf_item_key := P_k_header_ID || '-' ||
163 P_Communication_Num || '-' ||
164 P_New_Action_Code || '-' ||
165 TO_CHAR(sysdate,'YYYYMMDDHH24MISS');
166
167 l_wf_user_key := l_Contract_Num || '-' ||
168 P_Communication_Num || '-' ||
169 P_New_Action_Code || '-' ||
170 TO_CHAR(sysdate,'YYYYMMDDHH24MISS');
171
172 P_WF_ITEM_KEY:=l_wf_item_key;
173
174 WF_ENGINE.CreateProcess( itemtype => l_wf_item_type
175 , itemkey => l_wf_item_key
176 , process => l_wf_process );
177
178 WF_ENGINE.SetItemOwner ( itemtype => l_wf_item_type
179 , itemkey => l_wf_item_key
180 , owner => l_user_name );
181
182 WF_ENGINE.SetItemUserKey( itemtype => l_wf_item_type
183 , itemkey => l_wf_item_key
184 , userkey => l_wf_user_key );
185
186 -------------------------------------------------------------------
187 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
188 , itemkey => l_wf_item_key
189 , aname => 'DOC_TYPE'
190 , avalue => l_k_type );
191
192 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
193 , itemkey => l_wf_item_key
194 , aname => 'DOC_NUMBER'
195 , avalue => l_Contract_Num );
196
197 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
198 , itemkey => l_wf_item_key
199 , aname => 'LINE_NUMBER'
200 , avalue => l_Line_Number );
201
202 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
203 , itemkey => l_wf_item_key
204 , aname => 'DTS_NUMBER'
205 , avalue => l_DTS_Number );
206
207 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
208 , itemkey => l_wf_item_key
209 , aname => 'REQUESTOR'
210 , avalue => l_requestor);
211
212 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
213 , itemkey => l_wf_item_key
214 , aname => 'ADMINISTRATOR'
215 , avalue => l_user_name );
216
217 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
218 , itemkey => l_wf_item_key
219 , aname => 'COMMUNICATION_NUM'
220 , avalue => P_Communication_Num );
221
222 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
223 , itemkey => l_wf_item_key
224 , aname => 'LOGGED_BY'
225 , avalue => l_user_name );
226
227 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
228 , itemkey => l_wf_item_key
229 , aname => 'COMMUNICATION_TYPE'
230 , avalue => P_Type_Name );
231
232 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
233 , itemkey => l_wf_item_key
234 , aname => 'COMMUNICATION_REASON'
235 , avalue => P_Reason_Name );
236
237 -------------------------------------------------------------------
238 --Party Name
239 -------------------------------------------------------------------
240 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
241 , itemkey => l_wf_item_key
242 , aname => 'PARTY_NAME'
243 , avalue => P_Party_Name );
244
245 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
246 , itemkey => l_wf_item_key
247 , aname => 'PARTY_LOCATION'
248 , avalue => P_PARTY_LOCATION );
249
250 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
251 , itemkey => l_wf_item_key
252 , aname => 'PARTY_ROLE'
253 , avalue => P_PARTY_ROLE );
254
255 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
256 , itemkey => l_wf_item_key
257 , aname => 'PARTY_CONTACT'
258 , avalue => P_PARTY_CONTACT );
259
260 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
261 , itemkey => l_wf_item_key
262 , aname => 'ACTION_NAME'
263 , avalue => l_new_action );
264
265 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
266 , itemkey => l_wf_item_key
267 , aname => 'ACTION_OWNER'
268 , avalue => l_action_owner);
269
270 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
271 , itemkey => l_wf_item_key
272 , aname => 'PRIORITY_NAME'
273 , avalue => P_Priority_Name);
274
275 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
276 , itemkey => l_wf_item_key
277 , aname => 'COMMUNICATION_DATE'
278 , avalue => P_COMMUNICATION_DATE );
279
280 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
281 , itemkey => l_wf_item_key
282 , aname => 'COMMUNICATION_TEXT'
283 , avalue => P_COMMUNICATION_TEXT );
284
285 WF_ENGINE.SetItemAttrNumber( itemtype => l_wf_item_type
286 , itemkey => l_wf_item_key
287 , aname => 'ORG_ID'
288 , avalue => l_org_id );
289
290 WF_ENGINE.StartProcess( itemtype => l_wf_item_type
291 , itemkey => l_wf_item_key );
292
293 END IF;
294
295 END Action_Workflow;
296
297
298 --
299 -- Name : Comm_Action
300 -- Pre-reqs : None
301 -- Function : This procedure performs utility functions during
302 -- a change request status change.
303 --
304 --
305 -- Parameters :
306 -- IN : None
307 -- OUT : None
308 --
309 -- Returns : None
310 --
311
312 PROCEDURE Comm_Action
313 ( P_K_Header_ID IN NUMBER
314 , P_K_Line_ID IN NUMBER
315 , P_Deliverable_ID IN NUMBER
316 , P_Communication_Num IN VARCHAR2
317 , P_Type IN VARCHAR2
318 , P_Reason_Code IN VARCHAR2
319 , P_K_Party_ID IN NUMBER
320 , P_Party_Location IN VARCHAR2
321 , P_Party_Role IN VARCHAR2
322 , P_Party_Contact IN VARCHAR2
323 , P_New_Action_Code IN VARCHAR2
324 , P_Owner IN NUMBER
325 , P_Priority_Code IN VARCHAR2
326 , P_Communication_Date IN DATE
327 , P_Communication_Text IN VARCHAR2
328 , P_Updated_By IN NUMBER
329 , P_Update_Date IN DATE
330 , P_Login_ID IN NUMBER
331 , P_WF_ITEM_KEY OUT NOCOPY VARCHAR2
332 ) IS
333
334 CURSOR kh IS
335 SELECT k_number
336 ,k_type
337 FROM oke_k_headers_full_v
338 WHERE k_header_id = P_K_Header_ID;
339
340 CURSOR kl IS
341 SELECT line_number
342 FROM oke_k_lines_full_v
343 WHERE k_line_id = P_K_Line_ID;
344
345 CURSOR kd IS
346 SELECT deliverable_num
347 FROM oke_k_deliverables_vl
348 WHERE Deliverable_id = P_Deliverable_ID;
349
350 CURSOR o IS
351 SELECT user_name
352 FROM fnd_user
353 WHERE user_id = P_Updated_By;
354
355 CURSOR comm_type IS
356 SELECT meaning
357 FROM fnd_lookup_values_vl
358 WHERE lookup_type='COMMUNICATION_TYPE'
359 AND lookup_code=P_TYPE
360 AND view_application_id=777;
361
362 CURSOR comm_reason IS
363 SELECT meaning
364 FROM fnd_lookup_values_vl
365 WHERE lookup_type='COMMUNICATION_REASON_CODE'
366 AND lookup_code=P_REASON_CODE
367 AND view_application_id=777;
368
369 CURSOR new_action IS
370 SELECT wf_item_type
371 , wf_process
372 , comm_action_name
373 FROM oke_comm_actions_vl
374 WHERE comm_action_code = P_New_Action_Code;
375
376 -- Jun 20, 2002 : bug 2435609. Person_id is not unique.
377 -- Add effective_start_date and effective_end_date.
378 -- Only one person can be active at one time.
379 --
380 -- Aut 08, 2003 : bug 3051397. Send notification to Action Owner
381 -- if action owner is a WF_ROLE (must be a FND_USER), otherwise send to
382 -- Requestor who log the communication.
383 -- Use wf_roiles instead of per_people_f
384 CURSOR p IS
385 SELECT name, display_name
386 FROM wf_roles
387 WHERE orig_system = 'PER'
388 AND orig_system_id = P_Owner;
389
390 CURSOR comm_priority IS
391 SELECT meaning
392 FROM fnd_lookup_values_vl
393 WHERE lookup_type='COMMUNICATION_PRIORITY'
394 AND lookup_code=P_PRIORITY_CODE
395 AND view_application_id=777;
396
397 l_wf_item_type VARCHAR2(8);
398 l_wf_process VARCHAR2(30);
399 l_wf_item_key VARCHAR2(240);
400 l_wf_user_key VARCHAR2(240);
401 l_wf_threshold NUMBER;
402
403 -- Aug 08, 2003 Bug 3051397. Change variable length from 30 to 100
404 -- according to the column length of FND_USER
405 l_user_name VARCHAR2(100);
406 l_requestor VARCHAR2(100);
407
408 l_contract_num VARCHAR2(240);
409 l_k_type VARCHAR2(240);
410 l_line_number VARCHAR2(240);
411 l_dts_number VARCHAR2(240);
412 l_communication_type VARCHAR2(240);
413 l_communication_reason VARCHAR2(240);
414
415 -- Nov 3, 2002 : Bug 2637717 - UTF8 HR Column Expansion
416 -- l_party_name is not used anywhere in this package.
417 -- Change the varialbe length in case used in the future.
418 -- l_party_name VARCHAR2(240);
419 l_party_name VARCHAR2(360);
420
421 l_new_action VARCHAR2(240);
422 l_action_owner VARCHAR2(240);
423 l_priority_name VARCHAR2(240);
424
425 CURSOR cur_party
426 IS
427 SELECT JTOT_Object1_Code
428 , Object1_ID1
429 , Object1_ID2
430 , Role
431 FROM okc_k_party_roles_v
432 WHERE ID = P_K_Party_ID;
433
434 l_Party_Object_Code VARCHAR2(30);
435 l_Party_Object_ID1 VARCHAR2(40);
436 l_Party_Object_ID2 VARCHAR2(200);
437 l_Party_Role_Name VARCHAR2(80);
438 L_Dummy_String VARCHAR2(2000);
439
440 BEGIN
441
442 OPEN new_action;
443 FETCH new_action INTO l_wf_item_type , l_wf_process , l_new_action;
444 CLOSE new_action;
445
446 IF ( l_wf_item_type IS NOT NULL and l_wf_process IS NOT NULL) THEN
447
448 OPEN kh;
449 FETCH kh INTO l_contract_num,l_k_type;
450 CLOSE kh;
451
452 OPEN kl;
453 FETCH kl INTO l_line_number;
454 CLOSE kl;
455
456 OPEN kd;
457 FETCH kd INTO l_dts_number;
458 CLOSE kd;
459
460 OPEN o;
461 FETCH o INTO l_user_name;
462 CLOSE o;
463
464 OPEN comm_type;
465 FETCH comm_type INTO l_communication_type;
466 CLOSE comm_type;
467
468 OPEN comm_reason;
469 FETCH comm_reason INTO l_communication_reason;
470 CLOSE comm_reason;
471
472 -- OPEN p;
473 -- FETCH p INTO l_action_owner;
474 -- CLOSE p;
475 -- Aut 08, 2003 : bug 3051397. Send notification to Action Owner
476 -- if action owner is a WF_ROLE, otherwise send to
477 -- Requestor who log the communication.
478
479 IF (P_Owner is null) THEN
480 l_requestor := l_user_name;
481 ELSE
482 OPEN p;
483 FETCH p INTO l_requestor, l_action_owner;
484
485 IF p%notfound THEN
486 l_requestor := l_user_name;
487 SELECT full_name into l_action_owner
488 FROM per_all_people_f
489 WHERE person_id = P_Owner
490 AND sysdate between effective_start_date and effective_end_date;
491 END IF;
492 CLOSE p;
493 END IF;
494
495 OPEN comm_priority;
496 FETCH comm_priority INTO l_priority_name;
497 CLOSE comm_priority;
498
499 -- bug 6491257 fix
500 OPEN cur_party ;
501 FETCH cur_party
502 INTO l_Party_Object_Code , l_Party_Object_ID1, l_Party_Object_ID2, l_Party_Role_Name;
503 CLOSE cur_party ;
504
505 OKC_UTIL.Get_Name_Desc_From_JTFV
506 ( P_Object_Code => l_Party_Object_Code
507 , P_ID1 => l_Party_Object_ID1
508 , P_ID2 => l_Party_Object_ID2
509 , X_Name => l_party_name
510 , X_Description => L_Dummy_String);
511
512
513 l_wf_item_key := P_k_header_ID || '-' ||
514 P_Communication_Num || '-' ||
515 P_New_Action_Code || '-' ||
516 TO_CHAR(sysdate,'YYYYMMDDHH24MISS');
517
518 l_wf_user_key := l_Contract_Num || '-' ||
519 P_Communication_Num || '-' ||
520 P_New_Action_Code || '-' ||
521 TO_CHAR(sysdate,'YYYYMMDDHH24MISS');
522
523 P_WF_ITEM_KEY:=l_wf_item_key;
524
525 WF_ENGINE.CreateProcess( itemtype => l_wf_item_type
526 , itemkey => l_wf_item_key
527 , process => l_wf_process );
528
529 WF_ENGINE.SetItemOwner ( itemtype => l_wf_item_type
530 , itemkey => l_wf_item_key
531 , owner => l_user_name );
532
533 WF_ENGINE.SetItemUserKey( itemtype => l_wf_item_type
534 , itemkey => l_wf_item_key
535 , userkey => l_wf_user_key );
536
537 -------------------------------------------------------------------
538 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
539 , itemkey => l_wf_item_key
540 , aname => 'DOC_TYPE'
541 , avalue => l_k_type );
542
543 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
544 , itemkey => l_wf_item_key
545 , aname => 'DOC_NUMBER'
546 , avalue => l_Contract_Num );
547
548 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
549 , itemkey => l_wf_item_key
550 , aname => 'LINE_NUMBER'
551 , avalue => l_Line_Number );
552
553 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
554 , itemkey => l_wf_item_key
555 , aname => 'DTS_NUMBER'
556 , avalue => l_DTS_Number );
557
558 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
559 , itemkey => l_wf_item_key
560 , aname => 'REQUESTOR'
561 , avalue => l_requestor);
562
563 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
564 , itemkey => l_wf_item_key
565 , aname => 'ADMINISTRATOR'
566 , avalue => l_user_name );
567
568 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
569 , itemkey => l_wf_item_key
570 , aname => 'COMMUNICATION_NUM'
571 , avalue => P_Communication_Num );
572
573 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
574 , itemkey => l_wf_item_key
575 , aname => 'LOGGED_BY'
576 , avalue => l_user_name );
577
578 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
579 , itemkey => l_wf_item_key
580 , aname => 'COMMUNICATION_TYPE'
581 , avalue => l_communication_type );
582
583 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
584 , itemkey => l_wf_item_key
585 , aname => 'COMMUNICATION_REASON'
586 , avalue => l_communication_reason );
587
588 -------------------------------------------------------------------
589 --Party Name
590 -------------------------------------------------------------------
591 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
592 , itemkey => l_wf_item_key
593 , aname => 'PARTY_NAME'
594 , avalue => l_party_name);
595
596 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
597 , itemkey => l_wf_item_key
598 , aname => 'PARTY_LOCATION'
599 , avalue => P_PARTY_LOCATION );
600
601 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
602 , itemkey => l_wf_item_key
603 , aname => 'PARTY_ROLE_NAME'
604 , avalue => l_Party_Role_Name );
605
606 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
607 , itemkey => l_wf_item_key
608 , aname => 'PARTY_CONTACT'
609 , avalue => P_PARTY_CONTACT );
610
611 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
612 , itemkey => l_wf_item_key
613 , aname => 'ACTION_NAME'
614 , avalue => l_new_action );
615
616 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
617 , itemkey => l_wf_item_key
618 , aname => 'ACTION_OWNER'
619 , avalue => l_action_owner);
620
621 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
622 , itemkey => l_wf_item_key
623 , aname => 'PRIORITY_NAME'
624 , avalue => l_priority_name);
625
626 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
627 , itemkey => l_wf_item_key
628 , aname => 'COMMUNICATION_DATE'
629 , avalue => P_COMMUNICATION_DATE );
630
631 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
632 , itemkey => l_wf_item_key
633 , aname => 'COMMUNICATION_TEXT'
634 , avalue => P_COMMUNICATION_TEXT );
635
636 WF_ENGINE.StartProcess( itemtype => l_wf_item_type
637 , itemkey => l_wf_item_key );
638
639 END IF;
640
641 END Comm_Action;
642
643
644 END OKE_COMM_ACT_UTILS;