[Home] [Help]
PACKAGE BODY: APPS.EGO_GROUP_WF_PKG
Source
1 PACKAGE BODY EGO_GROUP_WF_PKG AS
2 /* $Header: EGOPGWFB.pls 120.1 2006/01/31 01:40:19 vkeerthi noship $ */
3
4 G_PACKAGE_NAME CONSTANT VARCHAR2(30) := 'EGO_GROUP_WF_PKG';
5 G_USER_MAIL_PREFERENCE CONSTANT VARCHAR2(30) := 'MAILHTML';
6 G_ITEM_TYPE CONSTANT VARCHAR2(30) := 'EGOGROUP';
7 G_GROUP_OBJECT_NAME CONSTANT VARCHAR2(30) := 'EGO_GROUP';
8
9 -- G_OWNER_GROUP_REL_TYPE CONSTANT VARCHAR2(30) := 'EGO_GROUP_OWNERSHIP';
10 -- G_OWNER_GROUP_REL_CODE CONSTANT VARCHAR2(30) := 'OWNER_OF';
11
12 G_MEMBER_GROUP_REL_TYPE CONSTANT VARCHAR2(30) := 'MEMBERSHIP';
13 G_MEMBER_GROUP_REL_CODE CONSTANT VARCHAR2(30) := 'MEMBER_OF';
14
15 ------------------------------------------------------------------------
16 -- Attributes used for populating by the Unmarshall_Xml procedure
17 ------------------------------------------------------------------------
18 G_OWNER_ID CONSTANT VARCHAR2(50) :='EGO_OWNER_ID';
19 G_OWNER_NAME CONSTANT VARCHAR2(50) :='EGO_OWNER_NAME';
20 G_OWNER_USER_NAME CONSTANT VARCHAR2(50) :='EGO_OWNER_USER_NAME';
21 G_GROUP_ID CONSTANT VARCHAR2(50) :='EGO_GROUP_ID';
22 G_GROUP_NAME CONSTANT VARCHAR2(50) :='EGO_GROUP_NAME';
23 G_MEMBER_ID CONSTANT VARCHAR2(50) :='EGO_MEMBER_ID';
24 G_MEMBER_NAME CONSTANT VARCHAR2(50) :='EGO_MEMBER_NAME';
25 G_MEMBER_USER_NAME CONSTANT VARCHAR2(50) :='EGO_MEMBER_USER_NAME';
26 G_GROUP_MEMBER_REL_ID CONSTANT VARCHAR2(50) :='EGO_GROUP_MEMBER_REL_ID';
27 G_MEMBER_NOTE CONSTANT VARCHAR2(50) :='EGO_MEMBER_NOTE';
28 G_OWNER_NOTE CONSTANT VARCHAR2(50) :='EGO_OWNER_NOTE';
29 G_RESPONDER_NAME CONSTANT VARCHAR2(50) :='EGO_RESPONDER_NAME';
30 ------------------------------------------------------------------------
31 -- Process types (used for branching in code)
32 ------------------------------------------------------------------------
33 G_ADD_GROUP_MEMBER_TYPE CONSTANT NUMBER := 0;
34 G_REMOVE_GROUP_MEMBER_TYPE CONSTANT NUMBER := 1;
35 G_DELETE_GROUP_TYPE CONSTANT NUMBER := 2;
36 G_SUBSCR_OWNER_NOTF_TYPE CONSTANT NUMBER := 3;
37 G_UNSUBSCR_OWNER_NOTF_TYPE CONSTANT NUMBER := 4;
38 G_ALL_POSSIBLE_VALUES_TYPE CONSTANT NUMBER := 100;
39 G_IDS_NAMES_USERNAMES_TYPE CONSTANT NUMBER := 101;
40
41 ------------------------------------------------------------------------
42 -- Process names
43 ------------------------------------------------------------------------
44 G_ADD_GROUP_MEMBER_PROCESS CONSTANT VARCHAR2(30) := 'ADD_GROUP_MEMBER_PROCESS';
45 G_REMOVE_GROUP_MEMBER_PROCESS CONSTANT VARCHAR2(30) := 'REMOVE_GROUP_MEMBER_PROCESS';
46 G_DELETE_GROUP_PROCESS CONSTANT VARCHAR2(30) := 'DELETE_GROUP_PROCESS';
47 G_SUBSCR_OWNER_NOTF_PROCESS CONSTANT VARCHAR2(30) := 'NOTIFY_SUBSCR_CONF_PROCESS';
48 G_UNSUBSCR_OWNER_NOTF_PROCESS CONSTANT VARCHAR2(30) := 'NOTIFY_UNSUBSCR_CONF_PROCESS';
49
50 G_NOTE VARCHAR2(50) :='NOTE';
51 G_COMPLETE_STATUS VARCHAR2(50) :='COMPLETE';
52
53 -----------------------------------------------
54 -- CURSOR to get the administrators list --
55 -----------------------------------------------
56 --PERF TUNING :4956096
57 CURSOR c_get_admin_list (cp_group_id IN NUMBER ) IS
58 SELECT user1.user_name, user1.party_id, user1.party_name, f.grantee_key
59 FROM fnd_grants f, fnd_menus m, fnd_objects o, ego_user_v user1
60 WHERE f.instance_pk1_value = to_char(cp_group_id)
61 AND f.start_date <= SYSDATE
62 AND NVL(f.end_date, SYSDATE) >= SYSDATE
63 AND (f.grantee_key like 'HZ_PARTY:%'
64 AND REPLACE(f.grantee_key,'HZ_PARTY:','') = user1.party_id)
65 AND f.menu_id = m.menu_id
66 AND m.menu_name = 'EGO_MANAGE_GROUP'
67 AND f.object_id = o.object_id
68 AND o.obj_name = 'EGO_GROUP';
69
70 ----------------------------------------------------------------------------
71 -- PROCEDURES THAT ARE CALLED INTERNALLY --
72 -- NO INTERFACE PROVIDED TO EXTERNAL WORLD --
73 ----------------------------------------------------------------------------
74
75 ---------------------------------------------------------------------
76 -- For debugging purposes.
77 PROCEDURE mdebug (msg IN varchar2) IS
78 BEGIN
79 -- dbms_output.put_line(msg);
80 --sri_debug('3354437 - '||msg);
81 null;
82 END mdebug;
83 ---------------------------------------------------------------------
84
85 ----------------------------------------------------------------------------
86 -- A. Get_Mail_Pref
87 ----------------------------------------------------------------------------
88 FUNCTION get_mail_pref(p_party_id IN NUMBER) RETURN VARCHAR2 IS
89 ----------------------------------------------------------------------
90 -- Start OF comments
91 --
92 -- Function name : get_email_pref
93 -- Type : Private
94 -- Pre-reqs : None
95 -- Functionality : Gets the mail preferences of the user
96 -- Notes :
97 --
98 -- Parameters:
99 -- IN : p_party_id IN NUMBER (Required)
100 -- party whose email preference is required.
101 --
102 -- History :
103 -- 21-jul-2002 Sridhar Rajaparthi Creation
104 --
105 -- END OF comments
106 ----------------------------------------------------------------------
107
108 CURSOR c_get_mail_pref (cp_party_id NUMBER) IS
109 SELECT preference_value
110 FROM fnd_user_preferences user_prefs,
111 fnd_user users,
112 hz_parties parties
113 WHERE user_prefs.preference_name = 'MAILTYPE'
114 AND user_prefs.user_name = users.user_name
115 AND users.customer_id = parties.party_id
116 AND parties.party_id = cp_party_id;
117
118 l_mail_pref FND_USER_PREFERENCES.PREFERENCE_VALUE%TYPE;
119
120 BEGIN
121 --retrieve mail preference for the owner
122 OPEN c_get_mail_pref(cp_party_id => p_party_id);
123 FETCH c_get_mail_pref INTO l_mail_pref;
124 IF c_get_mail_pref%NOTFOUND THEN
125 l_mail_pref := G_USER_MAIL_PREFERENCE;
126 ELSE
127 IF l_mail_pref IS NULL THEN
128 l_mail_pref := G_USER_MAIL_PREFERENCE;
129 END IF;
130 END IF;
131 CLOSE c_get_mail_pref;
132 mdebug('mail pref : party_id '|| To_char(p_party_id) || ' is ' || l_mail_pref);
133 RETURN l_mail_pref;
134 EXCEPTION
135 WHEN OTHERS THEN
136 IF c_get_mail_pref%ISOPEN THEN
137 CLOSE c_get_mail_pref;
138 END IF;
139 RAISE;
140 END get_mail_pref;
141 ---------------------------------------------------------------------
142
143 ----------------------------------------------------------------------------
144 -- B. Parse_Name_Value_Pairs_Msg
145 ----------------------------------------------------------------------------
146 PROCEDURE Parse_Name_Value_Pairs_Msg
147 (p_message IN VARCHAR2
148 ,x_name_tbl OUT NOCOPY VARCHAR_TBL_TYPE
149 ,x_value_tbl OUT NOCOPY VARCHAR_TBL_TYPE
150 ) IS
151 ----------------------------------------------------------------------
152 -- Start OF comments
153 --
154 -- Function name : Parse_Name_Value_Pairs_Msg
155 -- Type : Private
156 -- Pre-reqs : None
157 -- Functionality : Parse message into name and value tables
158 -- implements Hashtable like functionality
159 -- Notes :
160 --
161 -- Parameters:
162 -- IN : p_message IN VARCHAR2 (Required)
163 -- text whose name,value pair is desired
164 --
165 -- Called From:
166 -- Start_Subscription_Process
167 --
168 -- History :
169 -- 21-jul-2002 Sridhar Rajaparthi Creation
170 --
171 -- END OF comments
172 ----------------------------------------------------------------------
173
174 l_index NUMBER;
175 pos1 NUMBER;
176 pos2 NUMBER;
177 pos3 NUMBER;
178 l_message VARCHAR2(32767);
179
180 BEGIN
181 -- parse the payload
182 -- TO DO: parse the event_payload and intialize a table of records
183 -- mimicing a hashtable
184 l_message := p_message;
185 l_index := 0;
186 WHILE length(l_message) > 0 LOOP
187 pos1:=instr(l_message,'<');
188 pos2:=instr(l_message,'>');
189 IF (pos1 >0) THEN
190 x_name_tbl(l_index) := substr(l_message, pos1+1, pos2- (pos1+1));
191 pos3 := instr(l_message,'</') ;
192 x_value_tbl(l_index) := substr(l_message, pos2+1, pos3 - (pos2+1));
193 l_message := substr(l_message, pos2 - pos1 + pos3 + 2);
194 l_index := l_index + 1;
195 ELSE
196 EXIT;
197 END IF;
198 END LOOP;
199
200 EXCEPTION
201 WHEN OTHERS THEN
202 RAISE;
203 END Parse_Name_Value_Pairs_Msg;
204 ---------------------------------------------------------------------
205
206 ----------------------------------------------------------------------------
207 -- C. Unmarshall_Xml
208 ----------------------------------------------------------------------------
209 PROCEDURE Unmarshall_Xml
210 (
211 p_process_type IN NUMBER,
212 p_name_values_xml IN VARCHAR2,
213 x_names_tbl OUT NOCOPY EGO_GROUP_WF_PKG.VARCHAR_TBL_TYPE,
214 x_values_tbl OUT NOCOPY EGO_GROUP_WF_PKG.VARCHAR_TBL_TYPE
215 )
216 IS
217 ----------------------------------------------------------------------
218 -- Start OF comments
219 --
220 -- Procedure name : Unmarshall_Xml
221 -- Type : Private
222 -- Pre-reqs : None
223 -- Functionality : Returns Names and Values tables.
224 -- Contents of these tables are dictated by p_process_type
225 --
226 -- Notes :
227 --
228 -- Parameters:
229 -- IN : process_type IN NUMBER (Required)
230 -- process_type
231
232 -- IN : p_names_values_xml IN VARCHAR2 (Required)
233 -- name value pairs combination string
234
235 -- IN : p_names_tbl OUT EGO_GROUP_WF_PKG..VARCHAR_TBL_TYPE
236 -- names table (generated after parsing p_names_values_xml)
237
238 -- IN : p_values_tbl OUT EGO_GROUP_WF_PKG..VARCHAR_TBL_TYPE
239 -- values table (generated after parsing p_values_values_xml)
240 --
241 -- History :
242 -- 21-jul-2002 Sridhar Rajaparthi Creation
243 --
244 -- END OF comments
245 ----------------------------------------------------------------------
246
247 l_names_tbl EGO_GROUP_WF_PKG.VARCHAR_TBL_TYPE;
248 l_values_tbl EGO_GROUP_WF_PKG.VARCHAR_TBL_TYPE;
249
250 l_names_out_tbl EGO_GROUP_WF_PKG.VARCHAR_TBL_TYPE;
251 l_values_out_tbl EGO_GROUP_WF_PKG.VARCHAR_TBL_TYPE;
252
253 --index for looping
254 i NUMBER;
255
256 l_owner_id_exists BOOLEAN :=FALSE;
257 l_owner_name_exists BOOLEAN :=FALSE;
258 l_owner_user_name_exists BOOLEAN :=FALSE;
259 l_group_id_exists BOOLEAN :=FALSE;
260 l_group_name_exists BOOLEAN :=FALSE;
261 l_member_id_exists BOOLEAN :=FALSE;
262 l_member_name_exists BOOLEAN :=FALSE;
263 l_member_user_name_exists BOOLEAN :=FALSE;
264 l_group_member_rel_id_exists BOOLEAN :=FALSE;
265 l_note_exists BOOLEAN :=FALSE;
266
267 l_owner_id NUMBER;
268 l_owner_name HZ_PARTIES.PARTY_NAME%TYPE;
269 l_owner_user_name VARCHAR2(50);
270 l_group_id NUMBER;
271 l_group_name HZ_PARTIES.PARTY_NAME%TYPE;
272 l_member_id NUMBER;
273 l_member_name HZ_PARTIES.PARTY_NAME%TYPE;
274 l_member_user_name VARCHAR2(50);
275 l_group_member_rel_id NUMBER;
276 l_note VARCHAR2(999);
277
278 CURSOR get_user_party_names_c (p_party_id NUMBER) IS
279 -- fix for 3102621 changing the query
280 -- SELECT users.user_name, parties.party_name
281 -- FROM fnd_user users, hz_parties parties
282 -- WHERE users.customer_id = parties.party_id
283 -- AND parties.party_id = p_party_id;
284 --PERF TUNINIG :4956096
285 SELECT user_name, party_name
286 FROM ego_user_v
287 WHERE party_id = p_party_id;
288
289 -- CURSOR get_owner_user_party_names_c (p_group_id NUMBER) IS
290 -- SELECT users.user_name, parties.party_name
291 -- FROM fnd_user users, hz_parties parties, hz_relationships grp_owner
292 -- WHERE grp_owner.object_id = p_group_id
293 -- AND grp_owner.relationship_type = G_OWNER_GROUP_REL_TYPE
294 -- AND grp_owner.status = 'A'
295 -- AND SYSDATE BETWEEN grp_owner.start_date
296 -- AND NVL(grp_owner.end_date,SYSDATE)
297 -- AND parties.party_id = grp_owner.subject_id
298 -- AND users.customer_id = parties.party_id;
299
300
301 CURSOR get_group_member_rel_id_c (p_group_id NUMBER, p_member_id NUMBER) IS
302 SELECT relationship_id
303 FROM hz_relationships
304 WHERE subject_id = p_member_id
305 AND object_id = p_group_id
306 AND status = 'A'
307 AND relationship_type = G_MEMBER_GROUP_REL_TYPE
308 AND SYSDATE BETWEEN start_date AND NVL(end_date,SYSDATE);
309
310
311 BEGIN
312 mdebug (' UNMARSHALL_XML (UXML) : ....1.... ');
313
314 -- Parse the attributes
315 Parse_Name_Value_Pairs_Msg
316 (p_message => p_name_values_xml
317 ,x_name_tbl => l_names_tbl
318 ,x_value_tbl => l_values_tbl
319 );
323 mdebug('UXML: Name - ' || l_names_tbl(i)|| ' Value - '||l_values_tbl(i));
320
321 IF ( l_names_tbl.count > 0) THEN
322 FOR i IN l_names_tbl.first .. l_names_tbl.last LOOP
324 IF( l_names_tbl(i) = G_OWNER_ID ) THEN
325 l_owner_id_exists := TRUE;
326 l_owner_id := To_number(l_values_tbl(i));
327 ELSIF (l_names_tbl(i) = G_OWNER_NAME ) THEN
328 l_owner_name_exists := TRUE;
329 l_owner_name := l_values_tbl(i);
330 ELSIF (l_names_tbl(i) = G_OWNER_USER_NAME ) THEN
331 l_owner_user_name_exists := TRUE;
332 l_owner_user_name := l_values_tbl(i);
333 ELSIF( l_names_tbl(i) = G_GROUP_ID ) THEN
334 l_group_id_exists := TRUE;
335 l_group_id := To_number(l_values_tbl(i));
336 ELSIF (l_names_tbl(i) = G_GROUP_NAME ) THEN
337 l_group_name_exists := TRUE;
338 l_group_name := l_values_tbl(i);
339 ELSIF( l_names_tbl(i) = G_MEMBER_ID ) THEN
340 l_member_id_exists := TRUE;
341 l_member_id := To_number(l_values_tbl(i));
342 ELSIF (l_names_tbl(i) = G_MEMBER_NAME ) THEN
343 l_member_name_exists := TRUE;
344 l_member_name := l_values_tbl(i);
345 ELSIF (l_names_tbl(i) = G_MEMBER_USER_NAME ) THEN
346 l_member_user_name_exists := TRUE;
347 l_member_user_name := l_values_tbl(i);
348 ELSIF (l_names_tbl(i) = G_MEMBER_USER_NAME ) THEN
349 l_member_user_name_exists := TRUE;
350 l_member_user_name := l_values_tbl(i);
351 ELSIF (l_names_tbl(i) = G_GROUP_MEMBER_REL_ID ) THEN
352 l_group_member_rel_id_exists := TRUE;
353 l_group_member_rel_id := l_values_tbl(i);
354 ELSIF (l_names_tbl(i) = G_NOTE ) THEN
355 l_note_exists := TRUE;
356 l_note := l_values_tbl(i);
357 END IF;
358 END LOOP;
359 END IF;
360
361 IF (p_process_type = G_ADD_GROUP_MEMBER_TYPE OR
362 p_process_type = G_REMOVE_GROUP_MEMBER_TYPE
363 ) THEN
364
365 -- while adding member or removing group member,
366 -- we need to set the notifications to allthe administrators
367 -- current functionality does not support owners
368 -- This is taken care by the create_grp_admin_wf_role procedure
369 --
370 -- --If owner_id exists, and owner_name and owner_user_name doesnot
371 -- --exist, then populate those fields
372 -- IF (l_owner_id_exists = TRUE AND
373 -- (l_owner_name_exists = FALSE OR l_owner_user_name_exists = FALSE)
374 -- ) THEN
375 --
376 -- OPEN get_user_party_names_c(l_owner_id);
377 -- FETCH get_user_party_names_c INTO l_owner_user_name, l_owner_name;
378 -- CLOSE get_user_party_names_c;
379 -- mdebug('Owner user name just retrieved : '||l_owner_user_name);
380 --
381 -- END IF;
382 --
383 -- --If owner_id doesnot exist, and group_id exists, then derive
384 -- --owner_name and owner_user_name doesnot from group_id
385 -- IF (l_owner_id_exists = FALSE AND l_group_id_exists = TRUE) THEN
386 -- OPEN get_owner_user_party_names_c(l_group_id);
387 -- FETCH get_owner_user_party_names_c INTO l_owner_user_name, l_owner_name;
388 -- CLOSE get_owner_user_party_names_c;
389 -- END IF;
390 --
391 --
392
393 --If member_id exists, and member_name and member_user_name doesnot
394 --exist, then populate those fields
395 IF (l_member_id_exists = TRUE AND
396 (l_member_name_exists = FALSE OR l_member_user_name_exists = FALSE)
397 ) THEN
398 mdebug(' UXML: Setting the Member User name and Name');
399 OPEN get_user_party_names_c(l_member_id);
400 FETCH get_user_party_names_c INTO l_member_user_name, l_member_name;
401 CLOSE get_user_party_names_c;
402 END IF;
403
404 END IF; -- IF (p_process_type = G_ADD_GROUP_MEMBER_TYPE OR
405 -- p_process_type = G_REMOVE_GROUP_MEMBER_TYPE) THEN
406
407 --in case remove group member, retrieve rel id.
408 IF (p_process_type = G_REMOVE_GROUP_MEMBER_TYPE) THEN
409 IF (l_group_member_rel_id_exists = FALSE) THEN
410 OPEN get_group_member_rel_id_c(l_group_id, l_member_id);
411 FETCH get_group_member_rel_id_c INTO l_group_member_rel_id;
412 CLOSE get_group_member_rel_id_c;
413 END IF;
414 END IF;
415
416 --populate new tables, and return to the caller
417 --NOTE: following code common to all the process_types
418 --Even if some attributes are not retrieved above, the following
419 --nvl / decode functions will take care of populating the output table.
420 i := 0;
421 l_names_out_tbl(i) := G_OWNER_ID;
422 --decode can only be used in SQL stmt
423 SELECT Decode (l_owner_id, null, '', To_char(l_owner_id))
424 INTO l_values_out_tbl(i)
425 FROM dual;
426
427 i := i+1;
428 l_names_out_tbl(i) := G_OWNER_NAME;
432 l_names_out_tbl(i) := G_OWNER_USER_NAME;
429 l_values_out_tbl(i) := Nvl(l_owner_name,'');
430
431 i := i+1;
433 l_values_out_tbl(i) := Nvl(l_owner_user_name,'');
434
435 i := i+1;
436 l_names_out_tbl(i) := G_GROUP_ID;
437 SELECT Decode (l_group_id, null, '', To_char(l_group_id))
438 INTO l_values_out_tbl(i)
439 FROM dual;
440
441 i := i+1;
442 l_names_out_tbl(i) := G_GROUP_NAME;
443 l_values_out_tbl(i) := Nvl(l_group_name,'');
444
445 i := i+1;
446 l_names_out_tbl(i) := G_MEMBER_ID;
447 SELECT Decode (l_member_id, null, '', To_char(l_member_id))
448 INTO l_values_out_tbl(i)
449 FROM dual;
450
451 i := i+1;
452 l_names_out_tbl(i) := G_MEMBER_NAME;
453 l_values_out_tbl(i) := Nvl(l_member_name,'');
454
455 i := i+1;
456 l_names_out_tbl(i) := G_MEMBER_USER_NAME;
457 l_values_out_tbl(i) := Nvl(l_member_user_name,'');
458
459 i := i+1;
460 l_names_out_tbl(i) := G_GROUP_MEMBER_REL_ID;
461 SELECT Decode (l_group_member_rel_id, null, '', To_char(l_group_member_rel_id))
462 INTO l_values_out_tbl(i)
463 FROM dual;
464
465 i := i+1;
466 l_names_out_tbl(i) := G_NOTE;
467 l_values_out_tbl(i) := Nvl(l_note,'');
468
469 --set OUT parameters
470 x_names_tbl := l_names_out_tbl;
471 x_values_tbl := l_values_out_tbl;
472
473 EXCEPTION
474 WHEN OTHERS THEN
475 IF get_user_party_names_c%ISOPEN THEN
476 CLOSE get_user_party_names_c;
477 END IF;
478 -- IF get_owner_user_party_names_c%ISOPEN THEN
479 -- CLOSE get_owner_user_party_names_c;
480 -- END IF;
481 IF get_group_member_rel_id_c%ISOPEN THEN
482 CLOSE get_group_member_rel_id_c;
483 END IF;
484 RAISE;
485
486 END Unmarshall_Xml;
487
488
489 ----------------------------------------------------------------------------
490 -- D. setWFItemAttributes
491 ----------------------------------------------------------------------------
492 PROCEDURE setWFItemAttributes
493 (
494 p_process_type IN NUMBER,
495 p_item_type IN VARCHAR2,
496 p_item_key IN NUMBER,
497 p_name_values_xml IN VARCHAR2
498 )
499 IS
500 ----------------------------------------------------------------------
501 -- Start OF comments
502 --
503 -- Procedure name : setWFItemAttributes
504 -- Type : private
505 -- Pre-reqs : None
506 -- Functionality : Set Workflow Item Level attributes
507 --
508 -- Notes :
509 --
510 -- Parameters:
511 --
512 -- IN : process_type IN NUMBER (Required)
513 -- process_type in the workflow
514
515 -- IN : p_item_type IN VARCHAR2 (Required)
519 -- Item key of the workflow
516 -- Item type of the workflow
517
518 -- IN : p_item_key IN VARCHAR2 (Required)
520
521 -- IN : p_names_values_xml IN VARCHAR2 (Required)
522 -- name value pairs combination string
523
524 -- History :
525 -- 21-jul-2002 Sridhar Rajaparthi Creation
526 --
527 -- END OF comments
528 ----------------------------------------------------------------------
529
530 l_names_tbl EGO_GROUP_WF_PKG.VARCHAR_TBL_TYPE;
531 l_values_tbl EGO_GROUP_WF_PKG.VARCHAR_TBL_TYPE;
532
533 l_owner_id NUMBER;
534 l_owner_name HZ_PARTIES.PARTY_NAME%TYPE;
535 l_owner_user_name FND_USER.USER_NAME%TYPE;
536 l_group_id NUMBER;
537 l_group_name HZ_PARTIES.PARTY_NAME%TYPE;
538 l_member_id NUMBER;
539 l_member_name HZ_PARTIES.PARTY_NAME%TYPE;
540 l_member_user_name FND_USER.USER_NAME%TYPE;
541 l_group_member_rel_id NUMBER;
542 --used to generate plsql document
543 l_msg_document_plsql_proc VARCHAR2(9999);
544 l_mail_pref FND_USER_PREFERENCES.PREFERENCE_VALUE%TYPE;
545
546 l_member_note VARCHAR2(2000);
547 l_admin_note VARCHAR2(2000);
548 l_temp_message VARCHAR2(2000);
549
550 BEGIN
551
552 mdebug (' SET WF ITEM ATTRIBUTES (SWFIA) : ....1.... ');
553 unmarshall_xml(p_process_type, p_name_values_xml, l_names_tbl, l_values_tbl);
554 --assign Workflow item level attributes with the unmarshalled values
555 IF (p_process_type = G_ADD_GROUP_MEMBER_TYPE OR
556 p_process_type = G_REMOVE_GROUP_MEMBER_TYPE OR
557 p_process_type = G_DELETE_GROUP_TYPE
558 ) THEN
559
560 IF ( l_names_tbl.count > 0) THEN
561 FOR i IN l_names_tbl.first .. l_names_tbl.last LOOP
562 mdebug('setting attribute: '|| l_names_tbl(i) || ' value - ' || l_values_tbl(i));
563 IF( l_names_tbl(i) = G_OWNER_ID ) THEN
564 l_owner_id := To_number(l_values_tbl(i));
565 --set owner id as the item level attribute
566 wf_engine.SetItemAttrNumber( itemtype => p_item_type,
567 itemkey => p_item_key,
568 aname => G_OWNER_ID,
569 avalue => To_number(l_values_tbl(i)));
570
571 ELSIF (l_names_tbl(i) = G_OWNER_NAME ) THEN
572 l_owner_name := l_values_tbl(i);
573 --set owner name as the item level attribute
574 wf_engine.SetItemAttrText( itemtype => p_item_type,
575 itemkey => p_item_key,
576 aname => G_OWNER_NAME,
577 avalue => l_values_tbl(i));
578 ELSIF (l_names_tbl(i) = G_OWNER_USER_NAME ) THEN
579 l_owner_user_name := l_values_tbl(i);
580 --set owner user name as the item level attribute
581 wf_engine.SetItemAttrText( itemtype => p_item_type,
582 itemkey => p_item_key,
583 aname => G_OWNER_USER_NAME,
584 avalue => l_values_tbl(i));
588 wf_engine.SetItemAttrNumber( itemtype => p_item_type,
585 ELSIF( l_names_tbl(i) = G_GROUP_ID ) THEN
586 l_group_id := To_number(l_values_tbl(i));
587 --set group id as the item level attribute
589 itemkey => p_item_key,
590 aname => G_GROUP_ID,
591 avalue => To_number(l_values_tbl(i)));
592 ELSIF (l_names_tbl(i) = G_GROUP_NAME ) THEN
593 l_group_name := l_values_tbl(i);
594 --set group name as the item level attribute
595 wf_engine.SetItemAttrText( itemtype => p_item_type,
596 itemkey => p_item_key,
597 aname => G_GROUP_NAME,
598 avalue => l_values_tbl(i));
599 ELSIF( l_names_tbl(i) = G_MEMBER_ID ) THEN
600 l_member_id := To_number(l_values_tbl(i));
601 --set member id as the item level attribute
602 wf_engine.SetItemAttrNumber( itemtype => p_item_type,
603 itemkey => p_item_key,
604 aname => G_MEMBER_ID,
605 avalue => To_number(l_values_tbl(i)));
606 ELSIF (l_names_tbl(i) = G_MEMBER_NAME ) THEN
607 l_member_name := l_values_tbl(i);
608 --set member name as the item level attribute
609 wf_engine.SetItemAttrText( itemtype => p_item_type,
610 itemkey => p_item_key,
611 aname => G_MEMBER_NAME,
612 avalue => l_values_tbl(i));
613 ELSIF (l_names_tbl(i) = G_MEMBER_USER_NAME ) THEN
614 l_member_user_name := l_values_tbl(i);
615 --set member user name as the item level attribute
616 wf_engine.SetItemAttrText( itemtype => p_item_type,
617 itemkey => p_item_key,
618 aname => G_MEMBER_USER_NAME,
619 avalue => l_values_tbl(i));
620 ELSIF( l_names_tbl(i) = G_GROUP_MEMBER_REL_ID ) THEN
621 l_member_id := To_number(l_values_tbl(i));
622 --set member id as the item level attribute
623 wf_engine.SetItemAttrNumber( itemtype => p_item_type,
624 itemkey => p_item_key,
625 aname => G_GROUP_MEMBER_REL_ID,
626 avalue => To_number(l_values_tbl(i)));
627 ELSIF (l_names_tbl(i) = G_NOTE ) THEN
628 --set member user name as the item level attribute
629 wf_engine.SetItemAttrText( itemtype => p_item_type,
630 itemkey => p_item_key,
631 aname => G_MEMBER_NOTE,
632 avalue => l_values_tbl(i));
633 END IF;
634 END LOOP; --FOR i IN l_names_tbl.first .. l_names_tbl.last LOOP
635 END IF; --IF ( l_names_tbl.count > 0) THEN
636 mdebug (' SWFIA : All attributes sent ');
637
638 l_member_note := wf_engine.GetItemAttrText( itemtype => p_item_type,
639 itemkey => p_item_key,
640 aname => G_MEMBER_NOTE);
641 IF (p_process_type = G_ADD_GROUP_MEMBER_TYPE) THEN
642
643 --**Set the Message Subject for Owner Approval Request
644 ----------------------------------------------------------------------
645 fnd_message.set_name('EGO', 'EGO_ADD_GROUP_MEMBER_SUBJECT');
646 fnd_message.set_token('MEMBER_NAME', l_member_name);
647 fnd_message.set_token('GROUP_NAME', l_group_name);
648
649 --set message subject as the item level attribute
650 wf_engine.SetItemAttrText( itemtype => p_item_type,
651 itemkey => p_item_key,
652 aname =>'EGO_OWNER_APPROVAL_REQ_SUBJECT',
653 avalue => fnd_message.get);
654 ----------------------------------------------------------------------
655
656 --This is a call for creating a PLSQL document.
660 l_msg_document_plsql_proc:='PLSQL:EGO_GROUP_WF_PKG.Add_GrpMem_Approval_Req_Doc/'
657 --Add_GrpMem_Approval_Req_Doc gets the message from FND_NEW_MESSAGES
658 --and stubs in the token values, and is used as a approval notification
659 --for the owner
661 ||p_item_type||':'||p_item_key;
662 --set Owner Approval Request body as the item level attribute
663 wf_engine.SetItemAttrText( itemtype => p_item_type,
664 itemkey => p_item_key,
665 aname =>'EGO_OWNER_APPROVAL_REQ_BODY',
666 avalue => l_msg_document_plsql_proc);
667
668 --*******Set the subsequent notifications body texts**********
669 --**Set the Approval Message body
670 ----------------------------------------------------------------------
671
672 fnd_message.set_name('EGO', 'EGO_ADD_GROUPMEM_CONF_SUBJ');
673 fnd_message.set_token('GROUP_NAME', l_group_name);
674
675 --set message subject as the item level attribute
676 wf_engine.SetItemAttrText( itemtype => p_item_type,
677 itemkey => p_item_key,
678 aname =>'EGO_MEMBER_APPROVAL_SUBJECT',
679 avalue => fnd_message.get);
680
681 -- fix for 3096076 removing the reference of setting the attribute
682 -- and calling the package to dynamically create the document
683 l_msg_document_plsql_proc:='PLSQL:EGO_GROUP_WF_PKG.Add_GrpMem_Approval_Msg_Doc/'
684 ||p_item_type||':'||p_item_key;
685 --set Reject Notification body as the item level attribute
686 wf_engine.SetItemAttrText( itemtype => p_item_type,
687 itemkey => p_item_key,
688 aname =>'EGO_MEMBER_APPROVAL_MSG_BODY',
689 avalue => l_msg_document_plsql_proc);
690 --
691 -- --get mail preference of the member bug : 1726010
692 -- l_mail_pref := get_mail_pref(l_member_id);
693 -- IF (l_mail_pref = 'MAILTEXT') THEN
694 -- fnd_message.set_name('EGO', 'EGO_ADD_GROUPMEM_CONF_BODY');
695 -- ELSE
696 -- fnd_message.set_name('EGO', 'EGO_ADD_GROUPMEM_CNF_HTM_BODY');
697 -- END IF;
698 --
699 -- fnd_message.set_token('GROUP_NAME', l_group_name);
700 -- --This method needs to be taken out, so that this 'NOTE' token is
701 -- --dynamically replaced with the Owner's Comments
702 ---- fnd_message.set_token('NOTE', '');
703 -- -- currently in workflow, the comments are not comming..
704 -- fnd_message.set_token('GROUP_ADMIN_COMMENTS', NULL);
705 -- --set message body as the item level attribute
706 -- wf_engine.SetItemAttrText( itemtype => p_item_type,
707 -- itemkey => p_item_key,
708 -- aname =>'EGO_MEMBER_APPROVAL_MSG_BODY',
709 -- avalue => fnd_message.get);
710 --
711 ----------------------------------------------------------------------
712
713 --**Set the Rejection Message body
714 ----------------------------------------------------------------------
715 fnd_message.set_name('EGO', 'EGO_ADD_GROUPMEM_REJECT_SUBJ');
716 fnd_message.set_token('GROUP_NAME', l_group_name);
717
718 --set message subject as the item level attribute
722 avalue => fnd_message.get);
719 wf_engine.SetItemAttrText( itemtype => p_item_type,
720 itemkey => p_item_key,
721 aname =>'EGO_MEMBER_REJECTION_SUBJECT',
723
724 -- fix for 3096076 removing the reference of setting the attribute
725 -- and calling the package to dynamically create the document
726 l_msg_document_plsql_proc:='PLSQL:EGO_GROUP_WF_PKG.Add_GrpMem_Reject_Msg_Doc/'
727 ||p_item_type||':'||p_item_key;
728 --set Reject Notification body as the item level attribute
729 wf_engine.SetItemAttrText( itemtype => p_item_type,
730 itemkey => p_item_key,
731 aname =>'EGO_MEMBER_REJECTION_MSG_BODY',
732 avalue => l_msg_document_plsql_proc);
733
734 --
735 -- --get mail preference of the member bug : 1726010
736 --
737 -- l_mail_pref := get_mail_pref(l_member_id);
738 -- IF (l_mail_pref = 'MAILTEXT') THEN
739 -- fnd_message.set_name('EGO', 'EGO_ADD_GROUPMEM_REJECT_BODY');
740 -- ELSE
741 -- fnd_message.set_name('EGO', 'EGO_ADD_GROUPMEM_REJ_HTM_BODY');
742 -- END IF;
743 --
744 -- fnd_message.set_token('GROUP_NAME', l_group_name);
745 -- --This method needs to be taken out, so that this 'NOTE' token is
746 -- --dynamically replaced with the Owner's Comments
747 ---- fnd_message.set_token('NOTE', '');
748 -- fnd_message.set_token('GROUP_ADMIN_COMMENTS', NULL);
749 --
750 -- --set message subject as the item level attribute
751 -- wf_engine.SetItemAttrText( itemtype => p_item_type,
752 -- itemkey => p_item_key,
753 -- aname =>'EGO_MEMBER_REJECTION_MSG_BODY',
754 -- avalue => fnd_message.get);
755 --
756
757 ----------------------------------------------------------------------
758 --*****END: Set the subsequent notifications body texts*********
759
760 END IF; -- IF (p_process_type = G_ADD_GROUP_MEMBER_TYPE)
761
762
763 IF (p_process_type = G_REMOVE_GROUP_MEMBER_TYPE) THEN
764
765 --**Set the Message Subject and Body for Owner Unsubscription Notf FYI
766 ----------------------------------------------------------------------
767 fnd_message.set_name('EGO', 'EGO_UNSUBSCR_GROUP_MEMBER_SUBJ');
768 fnd_message.set_token('MEMBER_NAME', l_member_name);
769 fnd_message.set_token('GROUP_NAME', l_group_name);
770
771 --set message subject as the item level attribute
772 wf_engine.SetItemAttrText( itemtype => p_item_type,
773 itemkey => p_item_key,
774 aname =>'EGO_OWNER_UNSUBSCR_FYI_SUBJECT',
775 avalue => fnd_message.get);
776
777 -- fix for 3096076 removing the reference of setting the attribute
778 -- and calling the package to dynamically create the document
779 mdebug (' Remove Group Member Subject Set '|| fnd_message.get);
780 l_msg_document_plsql_proc:='PLSQL:EGO_GROUP_WF_PKG.Unsub_Member_Owner_FYI_Doc/'
781 ||p_item_type||':'||p_item_key;
782 --set Reject Notification body as the item level attribute
783 wf_engine.SetItemAttrText( itemtype => p_item_type,
784 itemkey => p_item_key,
785 aname =>'EGO_OWNER_UNSUBSCR_FYI_BODY',
786 avalue => l_msg_document_plsql_proc);
787 --
788 -- l_mail_pref := get_mail_pref(l_member_id);
789 --
790 -- IF (l_mail_pref = 'MAILTEXT') THEN
791 -- IF l_member_note IS NOT NULL THEN
792 -- fnd_message.set_name('EGO', 'EGO_GROUPMEM_COMMENTS');
793 -- fnd_message.set_token('MEMBER_NAME', l_member_name);
794 -- fnd_message.set_token('NOTE', l_member_note);
795 -- l_temp_message := fnd_message.get;
796 -- ELSE
800 -- ELSE
797 -- l_temp_message := NULL;
798 -- END IF;
799 -- fnd_message.set_name('EGO', 'EGO_UNSUB_GRPMEM_FYI_BODY');
801 -- IF l_member_note IS NOT NULL THEN
802 -- fnd_message.set_name('EGO', 'EGO_GROUPMEM_COMMENTS_HTM');
803 -- fnd_message.set_token('MEMBER_NAME', l_member_name);
804 -- fnd_message.set_token('NOTE', l_member_note);
805 -- l_temp_message := fnd_message.get;
806 -- ELSE
807 -- l_temp_message := NULL;
808 -- END IF;
809 -- fnd_message.set_name('EGO', 'EGO_UNSUB_GRPMEM_FYI_HTM_BODY');
810 -- END IF;
811 --
812 -- fnd_message.set_token('MEMBER_NAME', l_member_name);
813 -- fnd_message.set_token('GROUP_NAME', l_group_name);
814 -- fnd_message.set_token('GROUP_MEM_COMMENTS', l_temp_message);
815 --
816 -- --set message subject as the item level attribute
817 -- wf_engine.SetItemAttrText( itemtype => p_item_type,
818 -- itemkey => p_item_key,
819 -- aname =>'EGO_OWNER_UNSUBSCR_FYI_BODY',
820 -- avalue => fnd_message.get);
821 -- mdebug (' Remove Group Member Body Set '|| fnd_message.get);
822 --
823 ----------------------------------------------------------------------
824
825 --*******Set the subsequent notifications body texts**********
826 --**Set the Member Unsubscription Confirmation Message Subject and Body
827 ----------------------------------------------------------------------
828 fnd_message.set_name('EGO', 'EGO_UNSUBSCR_GRPMEM_CONF_SUBJ');
829 fnd_message.set_token('GROUP_NAME', l_group_name);
830
831 --set message subject as the item level attribute
832 wf_engine.SetItemAttrText( itemtype => p_item_type,
833 itemkey => p_item_key,
834 aname =>'EGO_UNSUBSCR_GRPMEM_CONF_SUBJ',
835 avalue => fnd_message.get);
836
837 -- fix for 3096076 removing the reference of setting the attribute
838 -- and calling the package to dynamically create the document
839 l_msg_document_plsql_proc:='PLSQL:EGO_GROUP_WF_PKG.Unsub_Member_Conf_Mem_Doc/'
840 ||p_item_type||':'||p_item_key;
841 --set Reject Notification body as the item level attribute
842 wf_engine.SetItemAttrText( itemtype => p_item_type,
843 itemkey => p_item_key,
844 aname =>'EGO_UNSUBSCR_GRPMEM_CONF_BODY',
845 avalue => l_msg_document_plsql_proc);
846 --
847 -- --get mail preference of the member bug : 1726010
848 --
849 -- l_mail_pref := get_mail_pref(l_member_id);
850 -- IF (l_mail_pref = 'MAILTEXT') THEN
851 -- fnd_message.set_name('EGO', 'EGO_UNSUBSCR_GRPMEM_CONF_BODY');
852 -- ELSE
853 -- fnd_message.set_name('EGO', 'EGO_UNSUB_GRPMEM_CNF_HTM_BODY');
854 -- END IF;
855 --
856 -- fnd_message.set_token('GROUP_NAME', l_group_name);
857 --
858 -- --set message body as the item level attribute
859 -- wf_engine.SetItemAttrText( itemtype => p_item_type,
860 -- itemkey => p_item_key,
861 -- aname =>'EGO_UNSUBSCR_GRPMEM_CONF_BODY',
862 -- avalue => fnd_message.get);
863 --
864 ----------------------------------------------------------------------
865 --*****END: Set the subsequent notifications body texts*********
866
867 END IF; -- IF (p_process_type = G_REMOVE_GROUP_MEMBER_TYPE)
868
869
870 END IF; --IF (p_process_type = G_ADD_GROUP_MEMBER_TYPE OR
871 -- p_process_type = G_REMOVE_GROUP_MEMBER_TYPE) THEN
872 -- p_process_type = G_DELETE_GROUP_TYPE)
873
874 EXCEPTION
875 WHEN OTHERS THEN
876 RAISE;
877 END ;
878
879 ----------------------------------------------------------------------------
880 -- E. Start_Subscription_Process (common to add / remove process)
881 ----------------------------------------------------------------------------
882 PROCEDURE Start_Subscription_Process
883 (
884 p_process_type IN NUMBER,
885 p_group_id IN NUMBER,
886 p_group_name IN VARCHAR2,
887 p_member_id IN NUMBER,
888 p_member_name IN VARCHAR2,
889 p_name_value_pairs IN VARCHAR2
890 )
891 IS
892 ----------------------------------------------------------------------
893 -- Start OF comments
894 --
895 -- Procedure name : Start_Subscription_Process
896 -- Type : Private
897 -- Pre-reqs : None
898 -- Functionality : Starts the workflow process to Add/Remove Group Member.
899 -- Notes : Called from Start_Add_Group_Member_Process and
900 -- Start_Add_Group_Member_Process
901 --
902 -- Parameters:
903 -- IN : p_process_type IN NUMBER (Required)
904 -- mentions whether we are Add OR Remove Group Member
905 -- IN : p_group_id IN NUMBER (Required)
906 -- Group Id
907 -- IN : p_group_name IN VARCHAR2 (Required)
908 -- Group Name
909 -- used to set the Workflow item attribute
913 -- Member Name
910 -- IN : p_member_id IN NUMBER (Required)
911 -- Member Id
912 -- IN : p_member_name IN VARCHAR2 (Required)
914 -- used to set the Workflow item attribute
915 -- IN : p_name_value_pairs IN VARCHAR2 (Optional)
916 -- Name value pairs provided as XML string
917 -- This is parsed by : Parse_Name_Value_Pairs_Msg
918 -- which creates a x_name_tbl and x_value_tbl
919 -- These are used to set the item attributes for the
920 -- IPDGROUP workflow item type
921 --
922 -- Called From:
923 -- Start_Add_Group_Member_Process
924 -- Start_Rem_Group_Member_Process
925 --
926 -- History :
927 -- 21-jul-2002 Sridhar Rajaparthi Creation
928 --
929 -- END OF comments
930 ----------------------------------------------------------------------
931
932 --workflow item type info. needed to start the workflow
933 l_item_type VARCHAR2(30) := G_ITEM_TYPE;
934
935 l_wf_process VARCHAR2(30);
936 l_item_key_id NUMBER;
937 l_item_key VARCHAR2(30);
938 l_item_user_key VARCHAR2(30) := null;
939
940 --xml string for name value pairs
941 l_xml_str VARCHAR2(32767);
942
943 CURSOR get_item_key_id_c IS
944 SELECT EGO_GROUP_WF_MGMT_S.NEXTVAL
945 FROM dual;
946 --
947 -- Removed as the owner is now obtained from fnd_grants
948 --
949 -- CURSOR c_get_owner_name(cp_group_id NUMBER) IS
950 -- SELECT users.user_name
951 -- FROM fnd_user users
952 -- ,hz_parties parties
953 -- ,hz_relationships grp_owner
954 -- WHERE users.customer_id = parties.party_id
955 -- AND parties.party_id = grp_owner.subject_id
956 -- WHERE users.customer_id = grp_owner.subject_id
957 -- AND grp_owner.relationship_type = G_OWNER_GROUP_REL_TYPE
958 -- AND grp_owner.relationship_code = G_OWNER_GROUP_REL_CODE
959 -- AND grp_owner.status = 'A'
960 -- AND SYSDATE BETWEEN grp_owner.start_date
961 -- AND NVL(grp_owner.end_date, SYSDATE)
962 -- AND grp_owner.object_id = cp_group_id;
963
964 -- CURSOR c_get_user_name(cp_party_id NUMBER) IS
965 -- SELECT users.user_name
966 -- FROM fnd_user users
967 -- WHERE users.customer_id = cp_party_id;
968
969 l_mail_pref wf_users.notification_preference%TYPE;
970 l_owner_name fnd_user.user_name%TYPE;
971 l_user_name fnd_user.user_name%TYPE;
972
973 BEGIN
974
975 mdebug (' START_SUBSCRIPTION_PROCESS (SSP) : ....1.... '|| p_process_type);
976 OPEN get_item_key_id_c;
977 FETCH get_item_key_id_c INTO l_item_key_id;
978 CLOSE get_item_key_id_c;
979
980 l_item_key := To_char(l_item_key_id);
981 mdebug (' SSP: Item Key Fetched ' || l_item_key);
982
983 l_mail_pref := get_mail_pref(p_party_id => p_member_id);
987 IF (p_process_type = G_ADD_GROUP_MEMBER_TYPE ) THEN
984 mdebug (' SSP: Mail Preference ' || l_mail_pref);
985
986 --set the workflow process to be started
988 l_wf_process := G_ADD_GROUP_MEMBER_PROCESS;
989 ELSE
990 l_wf_process := G_REMOVE_GROUP_MEMBER_PROCESS;
991 END IF;
992 mdebug (' SSP: setting the process type for WF ' || l_wf_process);
993
994 mdebug (' SSP: setup of parameters complete for WF Creation ');
995 --create the process EGOGROUP
996 wf_engine.CreateProcess ( itemtype => l_item_type,
997 itemkey => l_item_key,
998 process => l_wf_process);
999
1000 mdebug (' SSP: WF process created ');
1001 --no delimiters specified between name value pair combinations.
1002 --The parser handles this scenario.
1003 l_xml_str := '<'||G_GROUP_ID||'>'||To_char(p_group_id)
1004 ||'</'||G_GROUP_ID||'>';
1005 l_xml_str := l_xml_str||'<'||G_GROUP_NAME||'>'
1006 ||p_group_name||'</'||G_GROUP_NAME||'>';
1007 l_xml_str := l_xml_str||'<'||G_MEMBER_ID||'>'
1008 ||To_char(p_member_id)||'</'||G_MEMBER_ID||'>';
1009 --comment out member_name, because in unmarshall_xml both member_user_name
1010 --and member_name will be retrieved
1011 --l_xml_str := l_xml_str||'<'||G_MEMBER_NAME||'>'||To_char(p_member_name)||'</'||G_MEMBER_NAME||'>';
1012
1013 --To parse the name value pairs, along with the prepared xml
1014 IF p_name_value_pairs IS NOT NULL THEN
1015 l_xml_str := l_xml_str||p_name_value_pairs;
1016 END IF;
1017
1018 mdebug (' SSP: '|| substr(l_xml_str,1,100));
1019 mdebug (' SSP: '|| substr(l_xml_str,101,100));
1020 --set Workflow Item attributes
1021 setWFItemAttributes(p_process_type, l_item_type, l_item_key, l_xml_str);
1022
1023 mdebug (' SSP: WFItemAtributes set ');
1024 --Now that all the above global variables are available to the process,
1025 --Start the process EGOGROUP
1026 --The first process is to request an approval from Owner of the group
1027 wf_engine.StartProcess ( itemtype => l_item_type,
1028 itemkey => l_item_key );
1029
1030 mdebug (' SSP: BYE - WF Process Started ');
1031 EXCEPTION
1032 WHEN OTHERS THEN
1033 mdebug (' SSP: EXCEPTION ');
1034 wf_core.context(G_PACKAGE_NAME,l_wf_process,l_item_type,l_item_key);
1035 IF get_item_key_id_c%ISOPEN THEN
1036 CLOSE get_item_key_id_c;
1037 END IF;
1038 RAISE;
1039 END Start_Subscription_Process;
1040
1041 ----------------------------------------------------------------------------
1042 -- --
1043 -- PROCEDURES THAT ARE CALLED EXTERNALLY --
1044 -- --
1045 ----------------------------------------------------------------------------
1046
1047 ----------------------------------------------------------------------------
1048 -- 1. Start_Add_Group_Member_Process
1049 ----------------------------------------------------------------------------
1050 PROCEDURE Start_Add_Group_Member_Process
1051 (
1052 p_group_id IN NUMBER,
1053 p_group_name IN VARCHAR2,
1054 p_member_id IN NUMBER,
1055 p_member_name IN VARCHAR2,
1056 p_name_value_pairs IN VARCHAR2
1057 )
1058 IS
1059 ----------------------------------------------------------------------
1060 -- Start OF comments
1061 --
1062 -- Procedure name : Start_Add_Group_Member_Process
1063 -- Type : Public
1064 -- Pre-reqs : None
1065 -- Functionality : Starts the workflow process to Add Group Member.
1066 -- Notes :
1067 --
1068 -- History :
1069 -- 21-jul-2002 Sridhar Rajaparthi Creation
1070 --
1071 -- END OF comments
1072 ----------------------------------------------------------------------
1073
1074 BEGIN
1075
1076 mdebug(' Started Add_Group_Member_Process ');
1077 --Start Add Group Member process
1078 Start_Subscription_Process
1079 (G_ADD_GROUP_MEMBER_TYPE,
1080 p_group_id,
1081 p_group_name,
1082 p_member_id,
1083 p_member_name,
1084 p_name_value_pairs
1085 );
1086
1087 END Start_Add_Group_Member_Process;
1088
1089
1090 ----------------------------------------------------------------------------
1091 -- 2. Start_Rem_Group_Member_Process
1092 ----------------------------------------------------------------------------
1093 PROCEDURE Start_Rem_Group_Member_Process
1094 (
1095 p_group_id IN NUMBER,
1096 p_group_name IN VARCHAR2,
1097 p_member_id IN NUMBER,
1098 p_member_name IN VARCHAR2,
1099 p_name_value_pairs IN VARCHAR2
1100 )
1101 IS
1102 ----------------------------------------------------------------------
1103 -- Start OF comments
1104 --
1105 -- Procedure name : Start_Rem_Group_Member_Process
1106 -- Type : Public
1107 -- Pre-reqs : None
1108 -- Functionality : Starts the workflow process to Remove Group Member.
1109 -- Notes :
1110 --
1111 -- History :
1115 ----------------------------------------------------------------------
1112 -- 21-jul-2002 Sridhar Rajaparthi Creation
1113 --
1114 -- END OF comments
1116
1117 BEGIN
1118
1119 --Start Remove Group Member process
1120 mdebug (' Remove Group Member: WF Process Started ');
1121 Start_Subscription_Process
1122 (G_REMOVE_GROUP_MEMBER_TYPE,
1123 p_group_id,
1124 p_group_name,
1125 p_member_id,
1126 p_member_name,
1127 p_name_value_pairs
1128 );
1129
1130 END Start_Rem_Group_Member_Process;
1131
1132
1133 ----------------------------------------------------------------------------
1134 -- 4. Start_Delete_Group_Process
1135 ----------------------------------------------------------------------------
1136 PROCEDURE Start_Delete_Group_Process
1137 (
1138 p_group_id IN NUMBER,
1139 p_group_name IN VARCHAR2,
1140 p_name_value_pairs IN VARCHAR2
1141 )
1142 IS
1143 ----------------------------------------------------------------------
1144 -- Start OF comments
1145 --
1146 -- Procedure name : Start_Delete_Group_Process
1147 -- Type : Public
1148 -- Pre-reqs : None
1149 -- Functionality : Starts the workflow process to Delete Group.
1150 -- Notes :
1151 --
1152 -- History :
1153 -- 21-jul-2002 Sridhar Rajaparthi Creation
1154 --
1155 -- END OF comments
1156 ----------------------------------------------------------------------
1157
1158 l_return_status VARCHAR2(100);
1159 l_msg_count NUMBER;
1160 l_msg_data VARCHAR2(2000);
1161
1162 --workflow item type info. needed to start the workflow
1163 l_item_type VARCHAR2(30) := G_ITEM_TYPE;
1164 l_wf_process VARCHAR2(30) := G_DELETE_GROUP_PROCESS;
1165 l_item_key_id NUMBER;
1166 l_item_key VARCHAR2(30);
1167
1168 --xml string for name value pairs
1169 l_xml_str VARCHAR2(32767);
1170
1171 CURSOR get_item_key_id_c IS
1172 SELECT EGO_GROUP_WF_MGMT_S.NEXTVAL
1173 FROM dual;
1174
1175 BEGIN
1176 mdebug (' EGO_GROUP_WF_PKG.Start_Delete_Group_Process ');
1177 mdebug('1');
1178 OPEN get_item_key_id_c;
1179 FETCH get_item_key_id_c INTO l_item_key_id;
1180 CLOSE get_item_key_id_c;
1181 l_item_key := To_char(l_item_key_id);
1182
1183 mdebug('2 -- ' || l_item_key);
1184
1185 --create the process EGOGROUP
1186 wf_engine.CreateProcess ( itemtype => l_item_type,
1187 itemkey => l_item_key,
1188 process => l_wf_process);
1189 mdebug('3');
1190 --no delimiters specified between name value pair combinations.
1191 --The parser handles this scenario.
1192 l_xml_str := '<'||G_GROUP_ID||'>'||To_char(p_group_id)
1193 ||'</'||G_GROUP_ID||'>';
1194 l_xml_str := l_xml_str||'<'||G_GROUP_NAME||'>'
1195 ||p_group_name||'</'||G_GROUP_NAME||'>';
1196
1197 --To parse the name value pairs, along with the prepared xml
1198 mdebug('4');
1199 IF p_name_value_pairs IS NOT NULL THEN
1200 l_xml_str := l_xml_str||p_name_value_pairs;
1201 END IF;
1202
1203 --set Workflow Item attributes
1204 mdebug('5');
1205 setWFItemAttributes(G_DELETE_GROUP_TYPE, l_item_type, l_item_key, l_xml_str);
1206
1207 --Now that all the above global variables are available to the process,
1208 --Start the process EGOGROUP
1209 mdebug(' 6 ' || l_item_type || ' xx ' || l_item_key);
1210 wf_engine.StartProcess ( itemtype => l_item_type,
1211 itemkey => l_item_key );
1212
1213 EXCEPTION
1214 WHEN OTHERS THEN
1215 wf_core.context(G_PACKAGE_NAME,l_wf_process,l_item_type,l_item_key);
1216 IF get_item_key_id_c%ISOPEN THEN
1217 CLOSE get_item_key_id_c;
1218 END IF;
1219 raise;
1220
1221 END Start_Delete_Group_Process;
1222
1223
1224 ----------------------------------------------------------------------------
1225 -- 5. Start_Unsub_Owner_Notf_Process
1226 ----------------------------------------------------------------------------
1227 PROCEDURE Start_Unsub_Owner_Notf_Process
1228 (
1229 p_group_id IN NUMBER,
1230 p_group_name IN VARCHAR2,
1231 p_member_id IN NUMBER,
1232 p_member_name IN VARCHAR2,
1233 p_name_value_pairs IN VARCHAR2
1234 )
1235 IS
1236 ----------------------------------------------------------------------
1237 -- Start OF comments
1238 --
1239 -- Procedure name : Start_Unsub_Owner_Notf_Process
1240 -- Type : Public
1241 -- Pre-reqs : None
1242 -- Functionality : Starts the workflow process to Notify the owner
1243 -- when he is unsubscribed as owner.
1244 -- Notes :
1245 --
1246 -- History :
1247 -- 21-jul-2002 Sridhar Rajaparthi Creation
1248 --
1249 -- END OF comments
1250 ----------------------------------------------------------------------
1251
1252 l_return_status VARCHAR2(100);
1253 l_msg_count NUMBER;
1254 l_msg_data VARCHAR2(2000);
1255
1256 --workflow item type info. needed to start the workflow
1257 l_item_type VARCHAR2(30) := G_ITEM_TYPE;
1261
1258 l_wf_process VARCHAR2(30) := '_PROCESS';
1259 l_item_key_id NUMBER;
1260 l_item_key VARCHAR2(30);
1262 CURSOR get_item_key_id_c IS
1263 SELECT EGO_GROUP_WF_MGMT_S.NEXTVAL
1264 FROM dual;
1265
1266 BEGIN
1267
1268 OPEN get_item_key_id_c;
1269 FETCH get_item_key_id_c INTO l_item_key_id;
1270 CLOSE get_item_key_id_c;
1271
1272 l_item_key := To_char(l_item_key_id);
1273
1274 EXCEPTION
1275 WHEN OTHERS THEN
1276 wf_core.context(G_PACKAGE_NAME,l_wf_process,l_item_type,l_item_key);
1277 raise;
1278
1279 END Start_Unsub_Owner_Notf_Process;
1280
1281
1282 ----------------------------------------------------------------------------
1283 -- 6. Start_Subsc_Owner_Notf_Process
1284 ----------------------------------------------------------------------------
1285 PROCEDURE Start_Subsc_Owner_Notf_Process
1286 (
1287 p_group_id IN NUMBER,
1288 p_group_name IN VARCHAR2,
1289 p_member_id IN NUMBER,
1290 p_member_name IN VARCHAR2,
1291 p_name_value_pairs IN VARCHAR2
1292 )
1293 IS
1294 ----------------------------------------------------------------------
1295 -- Start OF comments
1296 --
1297 -- Procedure name : Start_Subsc_Owner_Notf_Process
1298 -- Type : Public
1299 -- Pre-reqs : None
1300 -- Functionality : Starts the workflow process to Notify the owner
1301 -- when he is subscribed as owner.
1302 -- Notes :
1303 --
1304 -- History :
1305 -- 21-jul-2002 Sridhar Rajaparthi Creation
1306 --
1307 -- END OF comments
1308 ----------------------------------------------------------------------
1309
1310 l_return_status VARCHAR2(100);
1311 l_msg_count NUMBER;
1312 l_msg_data VARCHAR2(2000);
1313
1314 --workflow item type info. needed to start the workflow
1315 l_item_type VARCHAR2(30) := G_ITEM_TYPE;
1316 l_wf_process VARCHAR2(30) := '_PROCESS';
1317 l_item_key_id NUMBER;
1318 l_item_key VARCHAR2(30);
1319
1320 CURSOR get_item_key_id_c IS
1321 SELECT EGO_GROUP_WF_MGMT_S.NEXTVAL
1322 FROM dual;
1323
1324 BEGIN
1325
1326 OPEN get_item_key_id_c;
1327 FETCH get_item_key_id_c INTO l_item_key_id;
1328 CLOSE get_item_key_id_c;
1329
1330 l_item_key := To_char(l_item_key_id);
1331
1332 EXCEPTION
1333 WHEN OTHERS THEN
1334 wf_core.context(G_PACKAGE_NAME,l_wf_process,l_item_type,l_item_key);
1335 IF get_item_key_id_c%ISOPEN THEN
1336 CLOSE get_item_key_id_c;
1337 END IF;
1338 raise;
1339
1340 END Start_Subsc_Owner_Notf_Process;
1341
1342
1343 ----------------------------------------------------------------------------
1344 -- 7. Add_Group_Member
1345 ----------------------------------------------------------------------------
1346 PROCEDURE Add_Group_Member
1347 (
1348 p_item_type IN VARCHAR2,
1349 p_item_key IN VARCHAR2,
1350 p_actid IN NUMBER,
1351 p_funcmode IN VARCHAR2,
1352 x_result OUT NOCOPY VARCHAR2
1353 )
1354 IS
1355 ----------------------------------------------------------------------
1356 -- Start OF comments
1357 --
1358 -- Procedure name : Add_Group_Member
1359 -- Type : Public
1360 -- Pre-reqs : None
1361 -- Functionality : Adds the group member to the group
1362 -- (After approval from the owner)
1363 --
1364 -- Notes :
1365 --
1366 -- History :
1367 -- 21-jul-2002 Sridhar Rajaparthi Creation
1368 --
1369 -- END OF comments
1370 ----------------------------------------------------------------------
1371
1372 CURSOR c_get_member(cp_group_id NUMBER
1373 ,cp_member_id NUMBER) IS
1374 SELECT 'Y'
1375 FROM EGO_GROUP_MEMBERS_V
1376 WHERE group_id = cp_group_id
1377 AND member_person_id = cp_member_id;
1378
1379 l_already_member VARCHAR2(1):='N';
1380
1381 l_member_id NUMBER;
1382 l_group_id NUMBER;
1383 l_return_status VARCHAR2(100);
1384 l_msg_count NUMBER;
1385 l_msg_data VARCHAR2(2000);
1386 l_group_member_rel_id NUMBER;
1387
1388 BEGIN
1389
1390 mdebug (' ADD_GROUP_MEMBER (AGM) : ....1.... ');
1391 IF ( p_funcmode = 'RUN' ) THEN
1392
1393 l_group_id := wf_engine.GetItemAttrNumber( itemtype => p_item_type,
1394 itemkey => p_item_key,
1395 aname => G_GROUP_ID);
1396
1397 l_member_id := wf_engine.GetItemAttrNumber( itemtype => p_item_type,
1398 itemkey => p_item_key,
1399 aname => G_MEMBER_ID);
1400
1401 OPEN c_get_member(cp_group_id => l_group_id
1402 ,cp_member_id => l_member_id);
1403 FETCH c_get_member INTO l_already_member;
1404 CLOSE c_get_member;
1405 IF l_already_member ='Y' THEN
1406 mdebug (' ADD_GROUP_MEMBER (AGM) : Trying to add an already existing meber ');
1407 x_result := G_COMPLETE_STATUS;
1408 RETURN;
1409 END IF;
1410
1411 mdebug (' AGM : Before calling EGO_PARTY_PUB.ADD_GROUP_MEMBER');
1415 p_commit => FND_API.G_TRUE,
1412 EGO_PARTY_PUB.Add_Group_Member(
1413 p_api_version => 1.0,
1414 p_init_msg_list => FND_API.G_TRUE,
1416 p_member_id => l_member_id,
1417 p_group_id => l_group_id,
1418 p_start_date => SYSDATE,
1419 p_end_date => NULL,
1420 x_return_status => l_return_status,
1421 x_msg_count => l_msg_count,
1422 x_msg_data => l_msg_data,
1423 x_relationship_id => l_group_member_rel_id
1424 );
1425
1426 mdebug (' AGM : Exiting out of EGO_PARTY_PUB.ADD_GROUP_MEMBER ');
1427 --store the relationship id generated, as item attribute
1428 wf_engine.SetItemAttrText( itemtype => p_item_type,
1429 itemkey => p_item_key,
1430 aname => G_GROUP_MEMBER_REL_ID,
1431 avalue => l_group_member_rel_id);
1432 mdebug (' AGM : Successfully set the parameters into the workflow ');
1433 x_result := G_COMPLETE_STATUS;
1434 RETURN;
1435 ELSIF (p_funcmode IN ('CANCEL', 'TIMEOUT')) THEN
1436 x_result := G_COMPLETE_STATUS;
1437 RETURN;
1438 END IF;
1439
1440
1441 EXCEPTION
1442 WHEN OTHERS THEN
1443 wf_core.context(G_PACKAGE_NAME,'Add_Group_Member',p_item_type,p_item_key);
1444 IF c_get_member%ISOPEN THEN
1445 CLOSE c_get_member;
1446 END IF;
1447 raise;
1448
1449 END Add_Group_Member;
1450
1451
1452 ----------------------------------------------------------------------------
1453 -- 8. Remove_Group_Member
1454 ----------------------------------------------------------------------------
1455 PROCEDURE Remove_Group_Member
1456 (
1457 p_item_type IN VARCHAR2,
1458 p_item_key IN VARCHAR2,
1459 p_actid IN NUMBER,
1460 p_funcmode IN VARCHAR2,
1461 x_result OUT NOCOPY VARCHAR2
1462 )
1463 IS
1464 ----------------------------------------------------------------------
1465 -- Start OF comments
1466 --
1467 -- Procedure name : Remove_Group_Member
1468 -- Type : Public
1469 -- Pre-reqs : None
1470 -- Functionality : Adds the group member from the group
1471 -- (After approval from the owner)
1472 --
1473 -- Notes :
1474 --
1475 -- History :
1476 -- 21-jul-2002 Sridhar Rajaparthi Creation
1477 --
1478 -- END OF comments
1479 ----------------------------------------------------------------------
1480
1481 l_group_id NUMBER;
1482 l_return_status VARCHAR2(100);
1483 l_msg_count NUMBER;
1484 l_msg_data VARCHAR2(2000);
1485 l_group_member_rel_id NUMBER;
1486 l_object_version_number NUMBER;
1487
1488 CURSOR c_get_object_ver_no (cp_relationship_id IN NUMBER) IS
1489 SELECT object_version_number
1490 FROM hz_relationships
1491 WHERE relationship_id = cp_relationship_id;
1492
1493 BEGIN
1494
1495 mdebug(' Entered Remove Group Member Process (RGMP)');
1496 IF ( p_funcmode = 'RUN' ) THEN
1497
1498 -- get the group member relationship id from Item attribute
1499 l_group_member_rel_id :=
1500 wf_engine.GetItemAttrNumber( itemtype => p_item_type,
1501 itemkey => p_item_key,
1502 aname => G_GROUP_MEMBER_REL_ID);
1503 mdebug(' RGMP ' || to_char(l_group_member_rel_id));
1504 --
1505 -- get the object version number of the relationship
1506 --
1507 OPEN c_get_object_ver_no (cp_relationship_id => l_group_member_rel_id);
1508 FETCH c_get_object_ver_no INTO l_object_version_number;
1509 IF c_get_object_ver_no%NOTFOUND THEN
1510 l_object_version_number := 0;
1511 END IF;
1512 CLOSE c_get_object_ver_no;
1513
1514 mdebug(' RGMP Before calling EGO_PARTY_PUB.Remove_Group_member') ;
1515 EGO_PARTY_PUB.Remove_Group_Member(
1516 p_api_version => 1.0,
1517 p_init_msg_list => FND_API.G_TRUE,
1518 p_commit => FND_API.G_TRUE,
1519 p_relationship_id => l_group_member_rel_id,
1520 p_object_version_no_rel => l_object_version_number,
1521 x_return_status => l_return_status,
1522 x_msg_count => l_msg_count,
1523 x_msg_data => l_msg_data
1524 );
1525 mdebug(' RGMP return status ' || l_return_status) ;
1526 x_result := G_COMPLETE_STATUS;
1527 RETURN;
1528 ELSIF (p_funcmode IN ('CANCEL', 'TIMEOUT')) THEN
1529 x_result := G_COMPLETE_STATUS;
1530 RETURN;
1531 END IF;
1532
1533 EXCEPTION
1534 WHEN OTHERS THEN
1535 wf_core.context(G_PACKAGE_NAME,'Remove_Group_Member',p_item_type,p_item_key);
1536 IF c_get_object_ver_no%ISOPEN THEN
1537 CLOSE c_get_object_ver_no;
1538 END IF;
1539 raise;
1540
1541 END Remove_Group_Member;
1542
1543
1544 ----------------------------------------------------------------------------
1545 -- 9. Delete_Group
1546 ----------------------------------------------------------------------------
1547 PROCEDURE Delete_Group
1548 (
1549 p_item_type IN VARCHAR2,
1550 p_item_key IN VARCHAR2,
1551 p_actid IN NUMBER,
1552 p_funcmode IN VARCHAR2,
1553 x_result OUT NOCOPY VARCHAR2
1554 )
1555 IS
1559 -- Procedure name : Delete_Group
1556 ----------------------------------------------------------------------
1557 -- Start OF comments
1558 --
1560 -- Type : Public
1561 -- Pre-reqs : None
1562 -- Functionality : Deletes the group
1563 --
1564 -- Notes :
1565 --
1566 -- History :
1567 -- 21-jul-2002 Sridhar Rajaparthi Creation
1568 --
1569 -- END OF comments
1570 ----------------------------------------------------------------------
1571
1572 l_group_id NUMBER;
1573 l_return_status VARCHAR2(100);
1574 l_msg_count NUMBER;
1575 l_msg_data VARCHAR2(2000);
1576 l_object_version_number NUMBER;
1577
1578 CURSOR c_get_object_ver_no (cp_group_id IN NUMBER) IS
1579 SELECT object_version_number
1580 FROM hz_parties
1581 WHERE party_id = cp_group_id
1582 AND party_type = 'GROUP'
1583 AND status = 'A';
1584
1585
1586 BEGIN
1587 mdebug (' Entered Delete Group after sending notifications ');
1588 IF ( p_funcmode = 'RUN' ) THEN
1589
1590 -- get the group id from Item attribute
1591 l_group_id := wf_engine.GetItemAttrNumber( itemtype => p_item_type,
1592 itemkey => p_item_key,
1593 aname => G_GROUP_ID);
1594 mdebug (' Group to be deleted ' || to_char(l_group_id));
1595 OPEN c_get_object_ver_no (cp_group_id => l_group_id);
1596 FETCH c_get_object_ver_no INTO l_object_version_number;
1597 IF c_get_object_ver_no%NOTFOUND THEN
1598 l_object_version_number := 0;
1599 END IF;
1600 CLOSE c_get_object_ver_no;
1601 mdebug (' Calling EGO_PARTY_PUB.Delete Group ');
1602 EGO_PARTY_PUB.Delete_group
1603 (p_api_version => 1.0
1604 ,p_init_msg_list => FND_API.G_TRUE
1605 ,p_commit => FND_API.G_FALSE
1606 ,p_group_id => l_group_id
1607 ,p_object_version_no_group => l_object_version_number
1608 ,x_return_status => l_return_status
1609 ,x_msg_count => l_msg_count
1610 ,x_msg_data => l_msg_data
1611 );
1612 mdebug (' Exited out of EGO_PARTY_PUB.Delete Group ');
1613 x_result := G_COMPLETE_STATUS;
1614 RETURN;
1615 ELSIF (p_funcmode IN ('CANCEL', 'TIMEOUT') ) THEN
1616 x_result := G_COMPLETE_STATUS;
1617 RETURN;
1618 END IF;
1619
1620
1621 EXCEPTION
1622 WHEN OTHERS THEN
1623 wf_core.context(G_PACKAGE_NAME,'Delete_Group',p_item_type,p_item_key);
1624 IF c_get_object_ver_no%ISOPEN THEN
1625 CLOSE c_get_object_ver_no;
1626 END IF;
1627 raise;
1628
1629 END Delete_Group;
1630
1631 ----------------------------------------------------------------------------
1632 -- 10. Group_Del_Ntf_All_Members
1633 ----------------------------------------------------------------------------
1634 PROCEDURE Group_Del_Ntf_All_Members
1635 (
1636 p_item_type IN VARCHAR2,
1637 p_item_key IN VARCHAR2,
1638 p_actid IN NUMBER,
1639 p_funcmode IN VARCHAR2,
1640 x_result OUT NOCOPY VARCHAR2
1641 ) IS
1642 ----------------------------------------------------------------------
1643 -- Start OF comments
1644 --
1645 -- Procedure name : Group_Del_Ntf_All_Members
1646 -- Type : Public
1647 -- Pre-reqs : None
1648 -- Functionality : Notifies all Group Members
1649 --
1650 -- Notes :
1651 --
1652 -- History :
1653 -- 21-jul-2002 Sridhar Rajaparthi Creation
1654 --
1655 -- END OF comments
1656 ----------------------------------------------------------------------
1657
1658 CURSOR c_get_member_party_names (cp_group_id IN NUMBER) IS
1659 SELECT member_user_name, member_person_name, member_person_id
1660 FROM ego_group_members_v
1661 WHERE group_id = cp_group_id;
1662
1663 l_group_id NUMBER;
1664 l_group_name HZ_PARTIES.PARTY_NAME%TYPE;
1665 l_msg_document_plsql_proc VARCHAR2(9999);
1666 l_notification_id NUMBER;
1667 l_context VARCHAR2(500);
1668 l_item_type VARCHAR2(30) := G_ITEM_TYPE;
1669 l_group_del_member_notif VARCHAR2(50) := 'GROUP_DEL_MEMBER_NOTF';
1670
1671
1672 BEGIN
1673 mdebug (' Entered Group_Del_Ntf_All_Members Starts...');
1674
1675 l_context:=p_item_type ||':'||p_item_key ||':'|| to_char(p_actid );
1676
1677 IF ( p_funcmode = 'RUN' ) THEN
1678 mdebug (' 1 ');
1679 -- get the group id from Item attribute
1680 l_group_id := wf_engine.GetItemAttrNumber( itemtype => p_item_type,
1681 itemkey => p_item_key,
1682 aname => G_GROUP_ID);
1683 l_group_name := wf_engine.GetItemAttrText( itemtype => p_item_type,
1684 itemkey => p_item_key,
1685 aname => G_GROUP_NAME);
1686 mdebug (' 2 ' || l_group_name);
1687 wf_engine.SetItemAttrText(itemtype => p_item_type,
1688 itemkey => p_item_key,
1689 aname => 'FROM_ROLE',
1690 avalue => fnd_global.user_name());
1691 mdebug (' 3 ' || fnd_global.user_name());
1695 -- --
1692 ------------------------------------------------
1693 -- --
1694 -- Set the subject outside the loop --
1696 ------------------------------------------------
1697 fnd_message.set_name('EGO', 'EGO_GROUP_DEL_MEM_NOTF_SUBJ');
1698 fnd_message.set_token('GROUP_NAME', l_group_name);
1699 --set message subject as the item level attribute
1700 wf_engine.SetItemAttrText(itemtype => p_item_type,
1701 itemkey => p_item_key,
1702 aname => 'EGO_GROUP_DEL_MEM_NOTF_SUBJ',
1703 avalue => fnd_message.get);
1704
1705 l_msg_document_plsql_proc:='PLSQL:EGO_GROUP_WF_PKG.Del_Grp_Admin_Notif_Doc/'
1706 ||p_item_type||':'||p_item_key;
1707
1708 --set Owner Group Del Message body as the item level attribute
1709 wf_engine.SetItemAttrText(itemtype => p_item_type,
1710 itemkey => p_item_key,
1711 aname =>'EGO_GROUP_DEL_MEM_NOTF_BODY',
1712 avalue => l_msg_document_plsql_proc);
1713 ------------------------------------------------
1714 -- --
1715 -- Send notifications to all admins --
1716 -- --
1717 ------------------------------------------------
1718 FOR cr in c_get_admin_list (l_group_id) LOOP
1719 mdebug (' 4 ' || cr.user_name);
1720 l_notification_id := WF_NOTIFICATION.SEND
1721 (
1722 role => cr.user_name,
1723 msg_type => l_item_type,
1724 msg_name => l_group_del_member_notif,
1725 due_date => NULL,
1726 callback => 'WF_ENGINE.CB',
1727 context => l_context,
1728 send_comment => NULL,
1729 priority => NULL
1730 );
1731 END LOOP;
1732 ------------------------------------------------
1733 -- --
1734 -- Send notifications to all members --
1735 -- --
1736 ------------------------------------------------
1737 l_msg_document_plsql_proc:='PLSQL:EGO_GROUP_WF_PKG.Del_Grp_Mem_Notif_Doc/'
1738 ||p_item_type||':'||p_item_key;
1739 --set Group Del Message body for Member as the item level attribute
1740 wf_engine.SetItemAttrText(itemtype => p_item_type,
1741 itemkey => p_item_key,
1742 aname =>'EGO_GROUP_DEL_MEM_NOTF_BODY',
1743 avalue => l_msg_document_plsql_proc);
1744 FOR cr in c_get_member_party_names (cp_group_id => l_group_id) LOOP
1745 mdebug (' 5 ' || cr.member_user_name);
1746 l_notification_id := WF_NOTIFICATION.SEND
1747 (
1748 role => cr.member_user_name,
1749 msg_type => l_item_type,
1750 msg_name => l_group_del_member_notif,
1751 due_date => NULL,
1752 callback => 'WF_ENGINE.CB',
1753 context => l_context,
1754 send_comment => NULL,
1755 priority => NULL
1756 );
1757 END LOOP;
1758 END IF;
1759 x_result := G_COMPLETE_STATUS;
1760 mdebug (' Group_Del_Ntf_All_Members Ends...');
1761 RETURN;
1762 EXCEPTION
1763 WHEN OTHERS THEN
1764 wf_core.context(G_PACKAGE_NAME,'Group_Del_Ntf_All_Members',p_item_type,p_item_key);
1765 IF c_get_member_party_names%ISOPEN THEN
1766 CLOSE c_get_member_party_names;
1767 END IF;
1768 IF c_get_admin_list%ISOPEN THEN
1769 CLOSE c_get_admin_list;
1770 END IF;
1771 RAISE;
1772 END Group_Del_Ntf_All_Members;
1773
1774
1775 ----------------------------------------------------------------------------
1776 -- 11. Add_GrpMem_Approval_Req_Doc
1777 ----------------------------------------------------------------------------
1778 PROCEDURE Add_GrpMem_Approval_Req_Doc
1779 (
1780 document_id IN VARCHAR2,
1781 display_type IN VARCHAR2,
1782 document IN OUT NOCOPY VARCHAR2,
1783 document_type IN OUT NOCOPY VARCHAR2
1784 )
1785 IS
1786 ----------------------------------------------------------------------
1787 -- Start OF comments
1788 --
1789 -- Procedure name : Add_GrpMem_Approval_Req_Doc
1790 -- Type : Public
1791 -- Pre-reqs : None
1792 -- Functionality : Prepares Message Document
1793 --
1794 -- Notes :
1795 --
1796 -- Called through following format:
1797 -- PLSQL:<package.procedure>/<Document ID>
1798 --
1799 -- A PL/SQL Document is generated with display type of 'text/html'
1800 -- when the message is viewed through web page. Else it is 'text/plain'
1801 --
1802 -- History :
1803 -- 21-jul-2002 Sridhar Rajaparthi Creation
1804 --
1805 -- END OF comments
1809 -- l_owner_name VARCHAR2(100);
1806 ----------------------------------------------------------------------
1807
1808 l_group_name hz_parties.party_name%TYPE;
1810 l_member_name hz_parties.party_name%TYPE;
1811 l_member_note VARCHAR2(999);
1812
1813 l_item_type VARCHAR2(30);
1814 l_item_key VARCHAR2(30);
1815
1816 -- xxxx
1817 -- group name is not unique in APPS
1818 --
1819 -- CURSOR c_get_ownerid(cp_group_name VARCHAR2) IS
1820 -- SELECT grp_owner.subject_id
1821 -- FROM hz_parties grp, hz_relationships grp_owner
1822 -- WHERE grp.party_name = cp_group_name
1823 -- AND grp.application_id = EGO_PARTY_PUB.get_application_id
1824 -- AND grp.party_type = 'GROUP'
1825 -- AND grp_owner.object_id = grp.party_id
1826 -- AND grp_owner.status = 'A'
1827 -- AND SYSDATE BETWEEN grp_owner.start_date AND NVL(grp_owner.end_date, SYSDATE)
1828 -- AND grp_owner.relationship_type = G_OWNER_GROUP_REL_TYPE;
1829
1830 -- l_owner_id NUMBER;
1831 l_mail_pref FND_USER_PREFERENCES.PREFERENCE_VALUE%TYPE;
1832 l_temp_message VARCHAR2(2000);
1833
1834 BEGIN
1835
1836 -- parse document_id for the ':' dividing item type name from item key value
1837 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
1838 -- release 2.5
1839 l_item_type := nvl(substr(document_id, 1, instr(document_id,':')-1),G_ITEM_TYPE);
1840 l_item_key := substr(document_id , instr(document_id,':')+1);
1841
1842 l_group_name := wf_engine.GetItemAttrText( itemtype => l_item_type,
1843 itemkey => l_item_key,
1844 aname => G_GROUP_NAME);
1845
1846 --
1847 -- l_owner_name := wf_engine.GetItemAttrText( itemtype => l_item_type,
1848 -- itemkey => l_item_key,
1849 -- aname => G_OWNER_NAME);
1850 --
1851
1852 l_member_name := wf_engine.GetItemAttrText( itemtype => l_item_type,
1853 itemkey => l_item_key,
1854 aname => G_MEMBER_NAME);
1855
1856
1857 l_member_note := wf_engine.GetItemAttrText( itemtype => l_item_type,
1858 itemkey => l_item_key,
1859 aname => G_MEMBER_NOTE);
1860
1861 -- OPEN c_get_ownerid(cp_group_name => l_group_name);
1862 -- FETCH c_get_ownerid INTO l_owner_id;
1863 -- CLOSE c_get_ownerid;
1864
1865 -- l_mail_pref := get_mail_pref(l_owner_id);
1866 l_mail_pref := G_USER_MAIL_PREFERENCE;
1867
1868 IF (l_mail_pref = 'MAILTEXT') THEN
1869 IF l_member_note IS NOT NULL THEN
1870 fnd_message.set_name('EGO', 'EGO_GROUPMEM_COMMENTS');
1871 fnd_message.set_token('MEMBER_NAME', l_member_name);
1872 fnd_message.set_token('NOTE', l_member_note);
1873 l_temp_message := fnd_message.get;
1874 ELSE
1875 l_temp_message := NULL;
1876 END IF;
1877 fnd_message.set_name('EGO', 'EGO_ADD_GROUP_MEMBER_BODY');
1878 ELSE
1879 -- mail preference is MAILHTML
1880 IF l_member_note IS NOT NULL THEN
1881 fnd_message.set_name('EGO', 'EGO_GROUPMEM_COMMENTS_HTM');
1882 fnd_message.set_token('MEMBER_NAME', l_member_name);
1883 fnd_message.set_token('NOTE', l_member_note);
1884 l_temp_message := fnd_message.get;
1885 ELSE
1886 l_temp_message := NULL;
1887 END IF;
1888 fnd_message.set_name('EGO', 'EGO_ADD_GROUPMEM_APPR_HTM_BODY');
1889 END IF;
1890 fnd_message.set_token('MEMBER_NAME', l_member_name);
1891 fnd_message.set_token('GROUP_NAME', l_group_name);
1892 fnd_message.set_token('GROUP_MEM_COMMENTS',l_temp_message);
1893 document:=fnd_message.get;
1894
1895 document_type := display_type;
1896
1897 EXCEPTION
1898 WHEN OTHERS THEN
1899 wf_core.context(G_PACKAGE_NAME,'Add_GrpMem_Approval_Req_Doc',l_item_type,l_item_key);
1900 -- IF c_get_ownerid%ISOPEN THEN
1901 -- CLOSE c_get_ownerid;
1902 -- END IF;
1903 RAISE;
1904
1905 END Add_GrpMem_Approval_Req_Doc;
1906
1907
1908 ----------------------------------------------------------------------------
1909 -- 12. Add_GrpMem_Reject_Msg_Doc
1910 ----------------------------------------------------------------------------
1911 PROCEDURE Add_GrpMem_Reject_Msg_Doc
1912 (
1913 document_id IN VARCHAR2,
1914 display_type IN VARCHAR2,
1915 document IN OUT NOCOPY VARCHAR2,
1916 document_type IN OUT NOCOPY VARCHAR2
1917 ) IS
1918 ----------------------------------------------------------------------
1919 -- Start Of comments
1920 --
1921 -- Procedure name : Add_GrpMem_Reject_Msg_Doc
1922 -- Type : Public
1923 -- Pre-reqs : None
1924 -- Functionality : Prepares Message Document
1925 --
1926 -- Notes : Created as per Bug 3096076
1927 --
1928 -- Called through following format:
1929 -- PLSQL:<package.procedure>/<Document ID>
1930 --
1931 -- A PL/SQL Document is generated with display type of 'text/html'
1932 -- when the message is viewed through web page. Else it is 'text/plain'
1933 --
1934 -- History :
1935 -- 03-SEP-2003 Sridhar Rajaparthi Creation
1936 --
1937 -- END OF comments
1938 ----------------------------------------------------------------------
1939 -- PERF TUNING :4956096
1943 WHERE user_name = cp_user_name;
1940 CURSOR c_get_party_name (cp_user_name IN VARCHAR2) IS
1941 SELECT party_name
1942 FROM ego_user_v
1944
1945 l_group_name HZ_PARTIES.PARTY_NAME%TYPE;
1946 l_item_type VARCHAR2(30);
1947 l_item_key VARCHAR2(30);
1948 l_mail_pref FND_USER_PREFERENCES.PREFERENCE_VALUE%TYPE;
1949 l_respondent HZ_PARTIES.PARTY_NAME%TYPE;
1950 l_user_name FND_USER.USER_NAME%TYPE;
1951 l_member_id NUMBER;
1952
1953 BEGIN
1954 -- parse document_id for the ':' dividing item type name from item key value
1955 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
1956 -- release 2.5
1957 l_item_type := nvl(substr(document_id, 1, instr(document_id,':')-1),G_ITEM_TYPE);
1958 l_item_key := substr(document_id , instr(document_id,':')+1);
1959
1960 l_group_name := wf_engine.GetItemAttrText( itemtype => l_item_type,
1961 itemkey => l_item_key,
1962 aname => G_GROUP_NAME);
1963
1964 l_member_id := wf_engine.GetItemAttrNumber(itemtype => l_item_type,
1965 itemkey => l_item_key,
1966 aname => G_MEMBER_ID);
1967 l_mail_pref := get_mail_pref(l_member_id);
1968 IF (display_type = 'text/plain') THEN
1969 fnd_message.set_name('EGO', 'EGO_ADD_GROUPMEM_REJECT_BODY');
1970 ELSE
1971 fnd_message.set_name('EGO', 'EGO_ADD_GROUPMEM_REJ_HTM_BODY');
1972 END IF;
1973
1974 fnd_message.set_token('GROUP_NAME', l_group_name);
1975
1976 l_user_name := wf_engine.GetItemAttrText( itemtype => l_item_type,
1977 itemkey => l_item_key,
1978 aname => G_RESPONDER_NAME);
1979 OPEN c_get_party_name (cp_user_name => l_user_name);
1980 FETCH c_get_party_name INTO l_respondent;
1981 CLOSE c_get_party_name;
1982
1983 fnd_message.set_token('GROUP_ADMIN_REJECTOR',l_respondent);
1984 document:=fnd_message.get;
1985 document_type := display_type;
1986 EXCEPTION
1987 WHEN OTHERS THEN
1988 wf_core.context(G_PACKAGE_NAME,'Add_GrpMem_Reject_Msg_Doc',l_item_type,l_item_key);
1989 IF c_get_party_name%ISOPEN THEN
1990 CLOSE c_get_party_name;
1991 END IF;
1992 RAISE;
1993 END Add_GrpMem_Reject_Msg_Doc;
1994
1995
1996 ----------------------------------------------------------------------------
1997 -- 13. Add_GrpMem_Approval_Msg_Doc
1998 ----------------------------------------------------------------------------
1999 PROCEDURE Add_GrpMem_Approval_Msg_Doc
2000 (
2001 document_id IN VARCHAR2,
2002 display_type IN VARCHAR2,
2003 document IN OUT NOCOPY VARCHAR2,
2004 document_type IN OUT NOCOPY VARCHAR2
2005 ) IS
2006 ----------------------------------------------------------------------
2007 -- Start Of comments
2008 --
2009 -- Procedure name : Add_GrpMem_Approval_Msg_Doc
2010 -- Type : Public
2011 -- Pre-reqs : None
2012 -- Functionality : Prepares Message Document
2013 --
2014 -- Notes : Created as per Bug 3096076
2015 --
2016 -- Called through following format:
2017 -- PLSQL:<package.procedure>/<Document ID>
2018 --
2019 -- A PL/SQL Document is generated with display type of 'text/html'
2020 -- when the message is viewed through web page. Else it is 'text/plain'
2021 --
2022 -- History :
2023 -- 03-SEP-2003 Sridhar Rajaparthi Creation
2024 --
2025 -- END OF comments
2026 ----------------------------------------------------------------------
2027
2028 l_group_name HZ_PARTIES.PARTY_NAME%TYPE;
2029 l_item_type VARCHAR2(30);
2030 l_item_key VARCHAR2(30);
2031 l_mail_pref FND_USER_PREFERENCES.PREFERENCE_VALUE%TYPE;
2032 l_member_id NUMBER;
2033
2034 BEGIN
2035 -- parse document_id for the ':' dividing item type name from item key value
2036 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
2037 -- release 2.5
2038 l_item_type := nvl(substr(document_id, 1, instr(document_id,':')-1),G_ITEM_TYPE);
2039 l_item_key := substr(document_id , instr(document_id,':')+1);
2040
2041 l_group_name := wf_engine.GetItemAttrText( itemtype => l_item_type,
2042 itemkey => l_item_key,
2043 aname => G_GROUP_NAME);
2044
2045 l_member_id := wf_engine.GetItemAttrNumber( itemtype => l_item_type,
2046 itemkey => l_item_key,
2047 aname => G_MEMBER_ID);
2048 l_mail_pref := get_mail_pref(l_member_id);
2049 IF (l_mail_pref = 'MAILTEXT') THEN
2050 fnd_message.set_name('EGO', 'EGO_ADD_GROUPMEM_CONF_BODY');
2051 ELSE
2052 fnd_message.set_name('EGO', 'EGO_ADD_GROUPMEM_CNF_HTM_BODY');
2053 END IF;
2054
2055
2056 IF (display_type = 'text/plain') THEN
2057 fnd_message.set_name('EGO', 'EGO_ADD_GROUPMEM_CONF_BODY');
2058 ELSE
2059 fnd_message.set_name('EGO', 'EGO_ADD_GROUPMEM_CNF_HTM_BODY');
2060 END IF;
2061
2062 fnd_message.set_token('GROUP_NAME', l_group_name);
2063 fnd_message.set_token('GROUP_ADMIN_COMMENTS', NULL);
2064
2065 document:=fnd_message.get;
2066 document_type := display_type;
2067
2068 EXCEPTION
2069 WHEN OTHERS THEN
2073
2070 wf_core.context(G_PACKAGE_NAME,'Add_GrpMem_Approval_Msg_Doc',l_item_type,l_item_key);
2071 RAISE;
2072 END Add_GrpMem_Approval_Msg_Doc;
2074
2075 ----------------------------------------------------------------------------
2076 -- 14. Unsub_Member_Owner_FYI_Doc
2077 ----------------------------------------------------------------------------
2078 PROCEDURE Unsub_Member_Owner_FYI_Doc
2079 (
2080 document_id IN VARCHAR2,
2081 display_type IN VARCHAR2,
2082 document IN OUT NOCOPY VARCHAR2,
2083 document_type IN OUT NOCOPY VARCHAR2
2084 ) IS
2085 ----------------------------------------------------------------------
2086 -- Start Of comments
2087 --
2088 -- Procedure name : Unsub_Member_Owner_FYI_Doc
2089 -- Type : Public
2090 -- Pre-reqs : None
2091 -- Functionality : Prepares Message Document
2092 --
2093 -- Notes : Created as per Bug 3096076
2094 --
2095 -- Called through following format:
2096 -- PLSQL:<package.procedure>/<Document ID>
2097 --
2098 -- A PL/SQL Document is generated with display type of 'text/html'
2099 -- when the message is viewed through web page. Else it is 'text/plain'
2100 --
2101 -- History :
2102 -- 03-SEP-2003 Sridhar Rajaparthi Creation
2103 --
2104 -- END OF comments
2105 ----------------------------------------------------------------------
2106
2107 l_group_name HZ_PARTIES.PARTY_NAME%TYPE;
2108 l_item_type VARCHAR2(30);
2109 l_item_key VARCHAR2(30);
2110 l_member_id NUMBER;
2111
2112 l_member_name HZ_PARTIES.PARTY_NAME%TYPE;
2113 l_mail_pref FND_USER_PREFERENCES.PREFERENCE_VALUE%TYPE;
2114 l_member_note VARCHAR2(2000);
2115 l_temp_message VARCHAR2(2000);
2116
2117 BEGIN
2118 -- parse document_id for the ':' dividing item type name from item key value
2119 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
2120 -- release 2.5
2121 l_item_type := nvl(substr(document_id, 1, instr(document_id,':')-1),G_ITEM_TYPE);
2122 l_item_key := substr(document_id , instr(document_id,':')+1);
2123
2124 l_group_name := wf_engine.GetItemAttrText( itemtype => l_item_type,
2125 itemkey => l_item_key,
2126 aname => G_GROUP_NAME);
2127 l_member_name := wf_engine.GetItemAttrText(itemtype => l_item_type
2128 ,itemkey => l_item_key
2129 ,aname => G_MEMBER_NAME);
2130 l_member_note := wf_engine.GetItemAttrText( itemtype => l_item_type
2131 ,itemkey => l_item_key
2132 ,aname => G_MEMBER_NOTE);
2133 l_member_id := wf_engine.GetItemAttrNumber( itemtype => l_item_type,
2134 itemkey => l_item_key,
2135 aname => G_MEMBER_ID);
2136 l_mail_pref := get_mail_pref(l_member_id);
2137 IF (l_mail_pref = 'MAILTEXT') THEN
2138 IF l_member_note IS NOT NULL THEN
2139 fnd_message.set_name('EGO', 'EGO_GROUPMEM_COMMENTS');
2140 fnd_message.set_token('MEMBER_NAME', l_member_name);
2141 fnd_message.set_token('NOTE', l_member_note);
2142 l_temp_message := fnd_message.get;
2143 ELSE
2144 l_temp_message := NULL;
2145 END IF;
2146 fnd_message.set_name('EGO', 'EGO_UNSUB_GRPMEM_FYI_BODY');
2147 ELSE
2148 IF l_member_note IS NOT NULL THEN
2149 fnd_message.set_name('EGO', 'EGO_GROUPMEM_COMMENTS_HTM');
2150 fnd_message.set_token('MEMBER_NAME', l_member_name);
2151 fnd_message.set_token('NOTE', l_member_note);
2152 l_temp_message := fnd_message.get;
2153 ELSE
2154 l_temp_message := NULL;
2155 END IF;
2156 fnd_message.set_name('EGO', 'EGO_UNSUB_GRPMEM_FYI_HTM_BODY');
2157 END IF;
2158
2159 fnd_message.set_token('MEMBER_NAME', l_member_name);
2160 fnd_message.set_token('GROUP_NAME', l_group_name);
2161 fnd_message.set_token('GROUP_MEM_COMMENTS', l_temp_message);
2162
2163 document:=fnd_message.get;
2164 document_type := display_type;
2165
2166 EXCEPTION
2167 WHEN OTHERS THEN
2168 wf_core.context(G_PACKAGE_NAME,'Unsub_Member_Owner_FYI_Doc',l_item_type,l_item_key);
2169 RAISE;
2170 END Unsub_Member_Owner_FYI_Doc;
2171
2172
2173 ----------------------------------------------------------------------------
2174 -- 15. Unsub_Member_Conf_Mem_Doc
2175 ----------------------------------------------------------------------------
2176 PROCEDURE Unsub_Member_Conf_Mem_Doc
2177 (
2178 document_id IN VARCHAR2,
2179 display_type IN VARCHAR2,
2180 document IN OUT NOCOPY VARCHAR2,
2181 document_type IN OUT NOCOPY VARCHAR2
2182 ) IS
2183 ----------------------------------------------------------------------
2184 -- Start Of comments
2185 --
2186 -- Procedure name : Unsub_Member_Conf_Mem_Doc
2187 -- Type : Public
2188 -- Pre-reqs : None
2189 -- Functionality : Prepares Message Document
2190 --
2191 -- Notes : Created as per Bug 3096076
2192 --
2193 -- Called through following format:
2194 -- PLSQL:<package.procedure>/<Document ID>
2195 --
2199 -- History :
2196 -- A PL/SQL Document is generated with display type of 'text/html'
2197 -- when the message is viewed through web page. Else it is 'text/plain'
2198 --
2200 -- 03-SEP-2003 Sridhar Rajaparthi Creation
2201 --
2202 -- END OF comments
2203 ----------------------------------------------------------------------
2204
2205 l_member_id NUMBER;
2206 l_group_name HZ_PARTIES.PARTY_NAME%TYPE;
2207 l_item_type VARCHAR2(30);
2208 l_item_key VARCHAR2(30);
2209 l_mail_pref FND_USER_PREFERENCES.PREFERENCE_VALUE%TYPE;
2210
2211 BEGIN
2212 -- parse document_id for the ':' dividing item type name from item key value
2213 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
2214 -- release 2.5
2215 l_item_type := nvl(substr(document_id, 1, instr(document_id,':')-1),G_ITEM_TYPE);
2216 l_item_key := substr(document_id , instr(document_id,':')+1);
2217
2218 l_group_name := wf_engine.GetItemAttrText( itemtype => l_item_type,
2219 itemkey => l_item_key,
2220 aname => G_GROUP_NAME);
2221 l_member_id := wf_engine.GetItemAttrNumber(itemtype => l_item_type,
2222 itemkey => l_item_key,
2223 aname => G_MEMBER_ID);
2224 l_mail_pref := get_mail_pref(l_member_id);
2225 IF (l_mail_pref = 'MAILTEXT') THEN
2226 fnd_message.set_name('EGO', 'EGO_UNSUBSCR_GRPMEM_CONF_BODY');
2227 ELSE
2228 fnd_message.set_name('EGO', 'EGO_UNSUB_GRPMEM_CNF_HTM_BODY');
2229 END IF;
2230
2231 fnd_message.set_token('GROUP_NAME', l_group_name);
2232
2233 document:=fnd_message.get;
2234 document_type := display_type;
2235
2236 EXCEPTION
2237 WHEN OTHERS THEN
2238 wf_core.context(G_PACKAGE_NAME,'Unsub_Member_Conf_Mem_Doc',l_item_type,l_item_key);
2239 RAISE;
2240 END Unsub_Member_Conf_Mem_Doc;
2241
2242
2243 ----------------------------------------------------------------------------
2244 -- 16. Del_Grp_Admin_Notif_Doc
2245 ----------------------------------------------------------------------------
2246 PROCEDURE Del_Grp_Admin_Notif_Doc
2247 (
2248 document_id IN VARCHAR2,
2249 display_type IN VARCHAR2,
2250 document IN OUT NOCOPY VARCHAR2,
2251 document_type IN OUT NOCOPY VARCHAR2
2252 ) IS
2253 ----------------------------------------------------------------------
2254 -- Start Of comments
2255 --
2256 -- Procedure name : Del_Grp_Admin_Notif_Doc
2257 -- Type : Public
2258 -- Pre-reqs : None
2259 -- Functionality : Prepares Message Document
2260 --
2261 -- Notes : Created as per Bug 3096076
2262 --
2263 -- Called through following format:
2264 -- PLSQL:<package.procedure>/<Document ID>
2265 --
2266 -- A PL/SQL Document is generated with display type of 'text/html'
2267 -- when the message is viewed through web page. Else it is 'text/plain'
2268 --
2269 -- History :
2270 -- 03-SEP-2003 Sridhar Rajaparthi Creation
2271 --
2272 -- END OF comments
2273 ----------------------------------------------------------------------
2274
2275 l_group_name HZ_PARTIES.PARTY_NAME%TYPE;
2276 l_item_type VARCHAR2(30);
2277 l_item_key VARCHAR2(30);
2278 l_mail_pref FND_USER_PREFERENCES.PREFERENCE_VALUE%TYPE;
2279 l_deletion_note VARCHAR2(999);
2280 l_temp_message VARCHAR2(2000);
2281 l_member_id NUMBER;
2282
2283 BEGIN
2284 -- parse document_id for the ':' dividing item type name from item key value
2285 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
2286 -- release 2.5
2287 l_item_type := nvl(substr(document_id, 1, instr(document_id,':')-1),G_ITEM_TYPE);
2288 l_item_key := substr(document_id , instr(document_id,':')+1);
2289
2290 l_group_name := wf_engine.GetItemAttrText( itemtype => l_item_type,
2291 itemkey => l_item_key,
2292 aname => G_GROUP_NAME);
2293
2294 l_deletion_note := wf_engine.GetItemAttrText( itemtype => l_item_type,
2295 itemkey => l_item_key,
2296 aname => G_MEMBER_NOTE);
2297
2298 IF l_deletion_note IS NOT NULL THEN
2299 fnd_message.set_name('EGO', 'EGO_GROUP_ADMIN_COMMENTS');
2300 fnd_message.set_token('NOTE', l_deletion_note);
2301 l_temp_message := fnd_message.get;
2302 ELSE
2303 l_temp_message := NULL;
2304 END IF;
2305
2306 l_member_id := wf_engine.GetItemAttrNumber( itemtype => l_item_type,
2307 itemkey => l_item_key,
2308 aname => G_MEMBER_ID);
2309 l_mail_pref := get_mail_pref(l_member_id);
2310 IF (l_mail_pref = 'MAILTEXT') THEN
2311 fnd_message.set_name('EGO', 'EGO_GROUP_DEL_OWN_NOTF_BODY');
2312 ELSE
2313 fnd_message.set_name('EGO', 'EGO_DEL_GROUPOWN_CNF_HTM_BODY');
2314 END IF;
2318 document:= fnd_message.get;
2315 fnd_message.set_token('GROUP_NAME', l_group_name);
2316 fnd_message.set_token('GROUP_ADMIN_COMMENTS', l_temp_message);
2317
2319 document_type := display_type;
2320
2321 EXCEPTION
2322 WHEN OTHERS THEN
2323 wf_core.context(G_PACKAGE_NAME,'Del_Grp_Admin_Notif_Doc',l_item_type,l_item_key);
2324 RAISE;
2325 END Del_Grp_Admin_Notif_Doc;
2326
2327
2328 ----------------------------------------------------------------------------
2329 -- 17. Del_Grp_Mem_Notif_Doc
2330 ----------------------------------------------------------------------------
2331 PROCEDURE Del_Grp_Mem_Notif_Doc
2332 (
2333 document_id IN VARCHAR2,
2334 display_type IN VARCHAR2,
2335 document IN OUT NOCOPY VARCHAR2,
2336 document_type IN OUT NOCOPY VARCHAR2
2337 ) IS
2338 ----------------------------------------------------------------------
2339 -- Start Of comments
2340 --
2341 -- Procedure name : Del_Grp_Mem_Notif_Doc
2342 -- Type : Public
2343 -- Pre-reqs : None
2344 -- Functionality : Prepares Message Document
2345 --
2346 -- Notes : Created as per Bug 3096076
2347 --
2348 -- Called through following format:
2349 -- PLSQL:<package.procedure>/<Document ID>
2350 --
2351 -- A PL/SQL Document is generated with display type of 'text/html'
2352 -- when the message is viewed through web page. Else it is 'text/plain'
2353 --
2354 -- History :
2355 -- 03-SEP-2003 Sridhar Rajaparthi Creation
2356 --
2357 -- END OF comments
2358 ----------------------------------------------------------------------
2359
2360 l_group_name HZ_PARTIES.PARTY_NAME%TYPE;
2361 l_item_type VARCHAR2(30);
2362 l_item_key VARCHAR2(30);
2363 l_mail_pref FND_USER_PREFERENCES.PREFERENCE_VALUE%TYPE;
2364 l_deletion_note VARCHAR2(999);
2365 l_temp_message VARCHAR2(2000);
2366 l_member_id NUMBER;
2367
2368 BEGIN
2369 -- parse document_id for the ':' dividing item type name from item key value
2370 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
2371 -- release 2.5
2372 l_item_type := nvl(substr(document_id, 1, instr(document_id,':')-1),G_ITEM_TYPE);
2373 l_item_key := substr(document_id , instr(document_id,':')+1);
2374
2375 l_group_name := wf_engine.GetItemAttrText( itemtype => l_item_type,
2376 itemkey => l_item_key,
2377 aname => G_GROUP_NAME);
2378
2379 l_deletion_note := wf_engine.GetItemAttrText( itemtype => l_item_type,
2380 itemkey => l_item_key,
2381 aname => G_MEMBER_NOTE);
2382
2383 IF l_deletion_note IS NOT NULL THEN
2384 fnd_message.set_name('EGO', 'EGO_GROUP_ADMIN_COMMENTS');
2385 fnd_message.set_token('NOTE', l_deletion_note);
2386 l_temp_message := fnd_message.get;
2387 ELSE
2388 l_temp_message := NULL;
2389 END IF;
2390
2391 l_member_id := wf_engine.GetItemAttrNumber( itemtype => l_item_type,
2392 itemkey => l_item_key,
2393 aname => G_MEMBER_ID);
2394 l_mail_pref := get_mail_pref(l_member_id);
2395 IF (l_mail_pref = 'MAILTEXT') THEN
2396 fnd_message.set_name('EGO', 'EGO_GROUP_DEL_MEM_NOTF_BODY');
2397 ELSE
2398 fnd_message.set_name('EGO', 'EGO_DEL_GROUPMEM_CNF_HTM_BODY');
2399 END IF;
2400
2401 fnd_message.set_token('GROUP_NAME', l_group_name);
2402 fnd_message.set_token('GROUP_ADMIN_COMMENTS', l_temp_message);
2403
2404 document:= fnd_message.get;
2405 document_type := display_type;
2406
2407 EXCEPTION
2408 WHEN OTHERS THEN
2409 wf_core.context(G_PACKAGE_NAME,'Del_Grp_Mem_Notif_Doc',l_item_type,l_item_key);
2410 RAISE;
2411 END Del_Grp_Mem_Notif_Doc;
2412
2413 ----------------------------------------------------------------------------
2414 -- 18. Get_Responder_Name
2415 ----------------------------------------------------------------------------
2416 PROCEDURE Get_Responder_name
2417 (itemtype IN VARCHAR2 ,
2418 itemkey IN VARCHAR2 ,
2419 actid IN NUMBER ,
2420 funcmode IN VARCHAR2 ,
2421 resultout OUT NOCOPY VARCHAR2
2422 )
2423 IS
2424 ----------------------------------------------------------------------
2425 -- Start OF comments
2426 --
2427 -- Procedure name : Get_Responder_Name
2428 -- Type : Public
2429 -- Pre-reqs : None
2430 -- Functionality : Store the approver's name
2431 --
2432 -- Notes :
2433 --
2434 -- History :
2435 -- 09-sep-2003 Sridhar Rajaparthi Creation
2436 --
2437 -- END OF comments
2438 ----------------------------------------------------------------------
2439 l_email_start_loc NUMBER;
2440 l_email_end_loc NUMBER;
2441 l_responder_name VARCHAR2(32767);
2442 l_email_address VARCHAR2(32767);
2443 BEGIN
2444
2445 mdebug (' GET_RESPONDER_NAME (GRN) : ....1.... mode: ' || funcmode ||
2446 ' - key: '|| itemkey||' - text: '||wf_engine.context_text);
2447 IF (funcmode = 'RESPOND' ) THEN
2448 -- bug 3354437
2452 IF l_email_start_loc <> 0 THEN
2449 -- get the responder name from the email address
2450 l_responder_name := wf_engine.context_text;
2451 l_email_start_loc := INSTR(l_responder_name,'<');
2453 l_email_end_loc := INSTR(l_responder_name,'>');
2454 l_email_address := SUBSTR(l_responder_name, l_email_start_loc+1, l_email_end_loc-l_email_start_loc-1);
2455 mdebug (' GET_RESPONDER_NAME (GRN) : ....5.... email: ' || l_email_address);
2456 BEGIN
2457 SELECT A.user_name
2458 INTO l_responder_name
2459 FROM wf_user_roles a, wf_users b
2460 WHERE a.user_name = b.name
2461 AND a.role_name = G_GROUP_OBJECT_NAME || itemkey
2462 AND upper(b.EMAIL_ADDRESS) = upper(l_email_address)
2463 AND rownum = 1;
2464 EXCEPTION
2465 WHEN OTHERS THEN
2466 NULL;
2467 END;
2468 END IF;
2469 mdebug (' GET_RESPONDER_NAME (GRN) : ....8.... responder: ' || l_responder_name);
2470 -- set the item attribute
2471 Wf_Engine.SetItemAttrText( ItemType => itemtype,
2472 ItemKey => itemkey,
2473 aname => G_RESPONDER_NAME,
2474 avalue => l_responder_name
2475 -- avalue => wf_engine.context_text
2476 );
2477
2478 Resultout:= G_COMPLETE_STATUS;
2479 RETURN;
2480 END IF;
2481
2482 EXCEPTION
2483 WHEN OTHERS THEN
2484 mdebug (' GET_RESPONDER_NAME (GRN) : EXCEPTION ');
2485 wf_core.context(G_PACKAGE_NAME,'GET_RESPONDER_NAME',itemtype,itemkey);
2486 raise;
2487 END GET_RESPONDER_NAME;
2488
2489
2490 ----------------------------------------------------------------------------
2491 -- 19. Create_Grp_Admin_WF_Role
2492 ----------------------------------------------------------------------------
2493 PROCEDURE create_grp_admin_wf_role
2494 (itemtype IN VARCHAR2 ,
2495 itemkey IN VARCHAR2 ,
2496 actid IN NUMBER ,
2497 funcmode IN VARCHAR2 ,
2498 resultout OUT NOCOPY VARCHAR2
2499 ) IS
2500 ------------------------------------------------------------------------
2501 -- Start OF comments
2502 -- API name : create_wf_role
2503 -- TYPE : Public
2504 -- Functionality : Create the WF Roles dynamically
2505 -- Notes : This procedure will create a role for all the
2506 -- administrators of the group
2507 --
2508 -- Parameters:
2509 -- IN : itemtype IN VARCHAR2 (Required)
2510 -- Item type of the workflow
2511
2512 -- IN : itemkey IN VARCHAR2 (Required)
2513 -- Item key of the workflow
2514
2515 -- IN : actid IN NUMBER (Required)
2516 -- action
2517
2518 -- IN : funcmode IN VARCHAR2 (Required)
2519 -- function mode
2520
2521 -- OUT : resultout OUT VARCHAR2
2522 -- Status of the workflow activity.
2523 --
2524 --
2525 -- called from:
2526 -- Workflow - processes
2527 --
2528 -- HISTORY
2529 -- 13-FEB-2003 Sridhar Rajaparthi Created
2530 --
2531 -- Notes :
2532 --
2533 -- END OF comments
2534 ------------------------------------------------------------------------
2535
2536 CURSOR c_dup_user (cp_user_name VARCHAR2,
2537 cp_role_name VARCHAR2) IS
2538 SELECT count(1)
2539 FROM wf_local_user_roles
2540 WHERE user_name = cp_user_name
2541 AND role_name = cp_role_name
2542 AND role_orig_system = 'WF_LOCAL_ROLES'
2543 AND role_orig_system_id = 0;
2544
2545 l_dup_user NUMBER := 0;
2546 l_api_name VARCHAR2(30) := 'SET_WF_ROLES';
2547 l_role_name VARCHAR2(360) ;
2548 l_role_display_name VARCHAR2(100);
2549 l_grantee_key fnd_grants.grantee_key%TYPE;
2550 l_user_name fnd_user.user_name%TYPE;
2551 l_party_id hz_parties.party_id%TYPE;
2552 l_group_id hz_parties.party_id%TYPE;
2553
2554 l_create_role_name VARCHAR2(2000);
2555 l_num NUMBER;
2556 l_group_name hz_parties.party_name%TYPE;
2557
2558 BEGIN
2559
2560 mdebug( ' Create group admin wf role ');
2561 l_role_name := G_GROUP_OBJECT_NAME || itemkey ;
2562 l_group_name := wf_engine.GetItemAttrText( itemtype => itemtype,
2563 itemkey => itemkey,
2564 aname => G_GROUP_NAME);
2565 fnd_message.set_name('EGO', 'EGO_GROUP_APPROVER_LIST_NAME');
2566 fnd_message.set_token('GROUP_NAME', l_group_name);
2567 l_role_display_name := fnd_message.get;
2568 --
2569 -- set notification username
2570 --
2571 wf_engine.SetItemAttrText (itemtype => itemtype,
2572 itemkey => itemkey,
2573 aname => 'FROM_ROLE',
2574 avalue => fnd_global.user_name() );
2575
2576 IF (funcmode = 'RUN') THEN
2577 -- create the adhoc role
2578 Wf_Directory.CreateAdHocRole (role_name => l_role_name,
2582 itemkey => itemkey,
2579 role_display_name => l_role_display_name
2580 );
2581 l_group_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2583 aname => G_GROUP_ID);
2584 FOR cr in c_get_admin_list( l_group_id ) LOOP
2585 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
2586 role_users => cr.user_name);
2587 END LOOP; -- c_get_admin_list
2588 Wf_Engine.SetItemAttrText( ItemType => itemtype,
2589 ItemKey => itemkey,
2590 aname => G_OWNER_USER_NAME,
2591 avalue => l_role_name
2592 );
2593 Resultout:= G_COMPLETE_STATUS;
2594 RETURN;
2595 END IF;
2596 EXCEPTION
2597 WHEN OTHERS THEN
2598 wf_core.context(G_PACKAGE_NAME,'create_grp_admin_wf_role',ItemType,ItemKey);
2599 IF c_get_admin_list%ISOPEN THEN
2600 CLOSE c_get_admin_list;
2601 END IF;
2602 IF c_dup_user%ISOPEN THEN
2603 CLOSE c_dup_user;
2604 END IF;
2605 RAISE;
2606 END create_grp_admin_wf_role;
2607
2608 END EGO_GROUP_WF_PKG;