[Home] [Help]
PACKAGE BODY: APPS.IBE_WF_MSG_MAPPING_PVT
Source
1 package body IBE_WF_MSG_MAPPING_PVT as
2 /* $Header: IBEVWMMB.pls 115.8 2002/12/27 13:09:08 schak ship $ */
3
4 --debug_mode boolean DEFAULT TRUE;
5 debug_mode boolean DEFAULT FALSE;
6 /** Globals to hold Logging attributs **/
7 g_fd utl_file.file_type; -- Log file descriptor
8 procedure debug(p_msg IN VARCHAR2) IS
9 l_debug VARCHAR2(1);
10
11 begin
12 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
13
14 if( debug_mode = TRUE) then
15 --dbms_output.put_line(p_msg);
16 IF (l_debug = 'Y') THEN
17 IBE_UTIL.debug(p_msg);
18 END IF;
19 end if;
20 end;
21
22 procedure TraceLog(err_msg IN VARCHAR2, module IN VARCHAR2) is
23 l_dbg_lvl NUMBER := 0;
24 l_dbgdir VARCHAR2(128);
25 l_dbgfile VARCHAR2(32) := 'IBEVRUTB.log';
26 l_err_msg VARCHAR2(256);
27 l_ndx NUMBER;
28 l_prevndx NUMBER;
29 l_strlen NUMBER;
30 begin
31 select value
32 into l_dbgdir
33 from v$PARAMETER
34 where name = 'utl_file_dir';
35
36 if( instr(l_dbgdir, ',') > 0 ) then
37 l_dbgdir := substr(l_dbgdir, 1, instr(l_dbgdir, ',')-1);
38 end if;
39
40 -- open the log file
41 g_fd := utl_file.fopen(l_dbgdir, l_dbgfile, 'a');
42 utl_file.put_line(g_fd, '');
43 select to_char(sysdate, 'DD-MON-YY:HH.MI.SS') into l_err_msg from dual;
44 utl_file.put_line(g_fd, 'IBEVRUTB: ******** New Session. : '||l_err_msg||' **********');
45 utl_file.put_line(g_fd, module||': ' || err_msg);
46 utl_file.fflush(g_fd);
47 EXCEPTION
48 when utl_file.INVALID_PATH then
49 --dbms_output.put_line('*********Error: Invalid Path ');
50 null;
51 when others then
52 l_err_msg := substr(sqlerrm, 1, 240);
53 --dbms_output.put_line('***** SQL Error: ' || l_err_msg);
54 null;
55 end TraceLog;
56
57 procedure find_msite(
58 p_msite_id IN NUMBER,
59 p_notif_setup_id IN NUMBER,
60 x_msite_tbl OUT NOCOPY WFMSG_TBL_TYPE) IS
61 l_msite_tbl WFMSG_TBL_TYPE;
62 cursor msite_csr(p_notif_setup_id IN NUMBER, p_msite_id IN NUMBER) IS
63 select notif_msg_map_id, notif_setup_id, message_name, enabled_flag, msite_id, org_id, user_type,
64 all_msite_flag, all_org_flag, all_user_Type_flag
65 from ibe_wf_notif_msg_maps
66 where notif_setup_id = p_notif_setup_id
67 and msite_id = p_msite_id;
68
69 cursor msite_null_csr(p_notif_setup_id IN NUMBER, p_msite_id IN NUMBER) IS
70 select notif_msg_map_id, notif_setup_id, message_name, enabled_flag, msite_id, org_id, user_type,
71 all_msite_flag, all_org_flag, all_user_Type_flag
72 from ibe_wf_notif_msg_maps
73 where notif_setup_id = p_notif_setup_id
74 and msite_id is null;
75
76 l_idx NUMBER := 1;
77 l_wfmsg_rec WFMSG_REC_TYPE;
78 BEGIN
79 if( p_msite_id is null ) then
80 open msite_null_csr(p_notif_setup_id, p_msite_id);
81 LOOP
82 fetch msite_null_csr into l_wfmsg_rec.notif_msg_map_id, l_wfmsg_Rec.notif_setup_id, l_wfmsg_rec.message_name,
83 l_wfmsg_rec.enabled_flag, l_wfmsg_rec.msite_id, l_wfmsg_rec.org_id, l_wfmsg_rec.user_type,
84 l_wfmsg_rec.all_msite_flag, l_wfmsg_rec.all_org_flag, l_wfmsg_rec.all_user_type_flag;
85 EXIT when msite_null_csr%NOTFOUND;
86 l_msite_tbl(l_idx) := l_wfmsg_rec;
87 l_idx := l_idx + 1;
88 END LOOP;
89 close msite_null_csr;
90 else
91 open msite_csr(p_notif_setup_id, p_msite_id);
92 LOOP
93 fetch msite_csr into l_wfmsg_rec.notif_msg_map_id, l_wfmsg_Rec.notif_setup_id, l_wfmsg_rec.message_name,
94 l_wfmsg_rec.enabled_flag, l_wfmsg_rec.msite_id, l_wfmsg_rec.org_id, l_wfmsg_rec.user_type,
95 l_wfmsg_rec.all_msite_flag, l_wfmsg_rec.all_org_flag, l_wfmsg_rec.all_user_type_flag;
96 EXIT when msite_csr%NOTFOUND;
97 l_msite_tbl(l_idx) := l_wfmsg_rec;
98 l_idx := l_idx + 1;
99 END LOOP;
100 close msite_csr;
101 end if;
102 x_msite_tbl := l_msite_tbl;
103 END;
104
105 procedure find_all_msite(
106 p_notif_setup_id IN NUMBER,
107 x_msite_tbl OUT NOCOPY WFMSG_TBL_TYPE) IS
108 l_msite_tbl WFMSG_TBL_TYPE;
109 cursor msite_csr(p_notif_setup_id IN NUMBER) IS
110 select notif_msg_map_id, notif_setup_id, message_name, enabled_flag, msite_id, org_id, user_type,
111 all_msite_flag, all_org_flag, all_user_Type_flag
112 from ibe_wf_notif_msg_maps
113 where notif_setup_id = p_notif_setup_id
114 and all_msite_flag = 'Y';
115 l_idx NUMBER := 1;
116 l_wfmsg_rec WFMSG_REC_TYPE;
117 BEGIN
118 open msite_csr(p_notif_setup_id);
119 LOOP
120 fetch msite_csr into l_wfmsg_rec.notif_msg_map_id, l_wfmsg_rec.notif_setup_id, l_wfmsg_rec.message_name,
121 l_wfmsg_rec.enabled_flag, l_wfmsg_rec.msite_id, l_wfmsg_rec.org_id, l_wfmsg_rec.user_type,
122 l_wfmsg_rec.all_msite_flag, l_wfmsg_rec.all_org_flag, l_wfmsg_rec.all_user_Type_flag;
123 EXIT when msite_csr%NOTFOUND;
124 l_msite_tbl(l_idx) := l_wfmsg_rec;
125 l_idx := l_idx + 1;
126 END LOOP;
127 close msite_csr;
128 x_msite_tbl := l_msite_tbl;
129 end;
130
131 procedure find_org(
132 p_in_msg_tbl IN WFMSG_TBL_TYPE,
133 p_org_id IN NUMBER,
134 x_out_msg_tbl OUT NOCOPY WFMSG_TBL_TYPE) IS
135 l_out_msg_tbl WFMSG_TBL_TYPE;
136 l_out_idx NUMBER;
137 BEGIN
138 debug('find_org ' || p_org_id);
139 l_out_idx := 1;
140 for i in 1..p_in_msg_tbl.COUNT LOOP
141 debug('p_in_msg_tbl(i).org_id = ' || p_in_msg_tbl(i).org_id);
142 if( p_org_id is null ) then
143 if( p_in_msg_tbl(i).org_id is null ) then
144 l_out_msg_tbl(l_out_idx) := p_in_msg_tbl(i);
145 l_out_idx := l_out_idx + 1;
146 end if;
147 else
148 if( p_in_msg_tbl(i).org_id = p_org_id ) THEN
149 l_out_msg_tbl(l_out_idx) := p_in_msg_tbl(i);
150 l_out_idx := l_out_idx +1;
151 end if;
152 end if;
153 end loop;
154 debug('l_out_idx = ' || l_out_idx);
155 x_out_msg_tbl := l_out_msg_tbl;
156 END;
157
158 procedure find_all_org(
159 p_in_msg_tbl IN WFMSG_TBL_TYPE,
160 x_out_msg_tbl OUT NOCOPY WFMSG_TBL_TYPE) IS
161 l_out_msg_tbl WFMSG_TBL_TYPE;
162 l_out_idx NUMBER;
163 BEGIN
164 l_out_idx := 1;
165 for i in 1..p_in_msg_tbl.COUNT LOOP
166 if( p_in_msg_tbl(i).all_org_flag = 'Y') THEN
167 l_out_msg_tbl(l_out_idx) := p_in_msg_tbl(i);
168 l_out_idx := l_out_idx + 1;
169 end if;
170 end lOOP;
171 x_out_msg_tbl := l_out_msg_tbl;
172 END;
173
174 procedure find_user_type(
175 p_in_msg_tbl IN WFMSG_TBL_TYPE,
176 p_user_type IN VARCHAR2,
177 x_out_msg_tbl OUT NOCOPY WFMSG_TBL_TYPE) IS
178 l_out_msg_tbl WFMSG_TBL_TYPE;
179 l_out_idx NUMBER := 1;
180 BEGIN
181 --dbms_output.put_line('p_user_type is ' || p_user_type);
182 for i in 1..p_in_msg_tbl.COUNT LOOP
183 -- dbms_output.put_line('p_in_msg_tbl(i).user_type is ' || p_in_msg_tbl(i).user_type);
184 if( upper(p_in_msg_tbl(i).user_type) = upper(p_user_type) ) THEN
185 -- dbms_output.put_line('found match');
186 l_out_msg_tbl(l_out_idx) := p_in_msg_tbl(i);
187 l_out_idx := l_out_idx + 1;
188 end if;
189 end loop;
190 --dbms_output.put_line('l_out_idx is ' ||l_out_msg_tbl.COUNT);
191 x_out_msg_tbl := l_out_msg_tbl;
192 END;
193
194 procedure find_all_user_type(
195 p_in_msg_tbl IN WFMSG_TBL_TYPE,
196 x_out_msg_tbl OUT NOCOPY WFMSG_TBL_TYPE) IS
197 l_out_msg_tbl WFMSG_TBL_TYPE;
198 l_out_idx NUMBER := 1;
199 BEGIN
200 for i in 1..p_in_msg_tbl.COUNT LOOP
201 if( p_in_msg_tbl(i).all_user_type_flag = 'Y' ) THEN
202 l_out_msg_tbl(l_out_idx) := p_in_msg_tbl(i);
203 l_out_idx := l_out_idx + 1;
204 end if;
205 end loop;
206 x_out_msg_tbl := l_out_msg_tbl;
207 END;
208
209 procedure get_default_msg(
210 x_wf_msg_name OUT NOCOPY VARCHAR2,
211 x_enabled_flag OUT NOCOPY VARCHAR2,
212 p_notif_Setup_id IN NUMBER) IS
213 cursor default_msg(p_notif_setup_id IN NUMBER) is
214 select message_name, enabled_flag
215 from IBE_WF_NOTIF_MSG_MAPS
216 WHERE notif_setup_id = p_notif_setup_id
217 and default_msg_map_flag = 'Y';
218 l_wf_msg_name VARCHAR2(30);
219 l_enabled_flag VARCHAR2(1);
220 BEGIN
221 open default_msg(p_notif_setup_id);
222 LOOP
223 fetch default_msg into l_wf_msg_name, l_enabled_flag;
224 exit when default_msg%NOTFOUND;
225 end LOOP;
226 close default_msg;
227 x_wf_msg_name := l_wf_msg_name;
228 x_enabled_flag := l_enabled_flag;
229 END;
230
231 procedure Get_MSGNAME_BY_ORGUSERTYPE(
232 x_wf_msg_name OUT NOCOPY VARCHAR2,
233 x_enabled_flag OUT NOCOPY VARCHAR2,
234 p_notif_setup_id IN NUMBER,
235 p_org_id IN NUMBER,
236 p_user_type IN VARCHAR2,
237 p_in_msg_tbl IN WFMSG_TBL_TYPE) IS
238 l_org_tbl WFMSG_TBL_TYPE;
239 l_user_type_tbl WFMSG_TBL_TYPE;
240 l_wf_msg_name VARCHAR2(30);
241 l_enabled_flag VARCHAR2(1);
242 l_wfmsg_tbl WFMSG_TBL_TYPE := p_in_msg_tbl;
243 BEGIN
244 debug('get_msgname_by_orgusertype 1');
245 find_org(l_wfmsg_tbl, p_org_id, l_org_tbl);
246 if( l_org_tbl.COUNT = 0 ) then
247 debug('get_msgname_by_orgusertype 2');
248 find_all_org(l_wfmsg_tbl, l_org_tbl);
249 if( l_org_tbl.COUNT = 0 ) THEN
250 debug('get_msgname_by_orgusertype 3');
251 get_default_msg(l_wf_msg_name, l_enabled_flag, p_notif_setup_id);
252 else
253 debug('get_msgname_by_orgusertype 4');
254 find_user_type(l_org_tbl, p_user_type, l_user_type_tbl);
255 if( l_user_type_Tbl.COUNT = 0 ) then
256 debug('get_msgname_by_orgusertype 5');
257 find_all_user_type(l_org_tbl, l_user_type_tbl);
258 if( l_user_type_tbl.COUNT = 0 ) then
259 debug('get_msgname_by_orgusertype 6');
260 get_default_msg(l_wf_msg_name, l_enabled_flag, p_notif_setup_id);
261 else
262 debug('get_msgname_by_orgusertype 7');
263 l_wf_msg_name := l_user_type_tbl(1).message_name;
264 l_enabled_flag := l_user_type_tbl(1).enabled_flag;
265 end if;
266 else
267 debug('get_msgname_by_orgusertype 8');
268 l_wf_msg_name := l_user_type_tbl(1).message_name;
269 l_enabled_flag := l_user_type_tbl(1).enabled_flag;
270 end if;
271 end if;
272 else
273 debug('get_msgname_by_orgusertype 9');
274 find_user_type(l_org_tbl, p_user_type, l_user_type_tbl);
275 if( l_user_type_Tbl.COUNT = 0 ) then
276 debug('get_msgname_by_orgusertype 10');
277 find_all_user_type(l_org_tbl, l_user_type_tbl);
278 if( l_user_type_tbl.COUNT = 0 ) then
279 debug('get_msgname_by_orgusertype 11');
280 get_default_msg(l_wf_msg_name, l_enabled_flag, p_notif_setup_id);
281 else
282 debug('get_msgname_by_orgusertype 12');
283 l_wf_msg_name := l_user_type_tbl(1).message_name;
284 l_enabled_flag := l_user_type_tbl(1).enabled_flag;
285
286 end if;
287 else
288 debug('get_msgname_by_orgusertype 13');
289 l_wf_msg_name := l_user_type_tbl(1).message_name;
290 l_enabled_flag := l_user_type_tbl(1).enabled_flag;
291 end if;
292 end if;
293
294 x_wf_msg_name := l_wf_msg_name;
295 x_enabled_flag := l_enabled_flag;
296 END;
297
298 Procedure get_msg_name_by_org(
299 x_wf_msg_name OUT NOCOPY VARCHAR2,
300 x_enabled_flag OUT NOCOPY VARCHAR2,
301 p_notif_setup_id IN NUMBER,
302 p_org_id IN NUMBER,
303 p_in_msg_tbl IN WFMSG_TBL_TYPE) IS
304 l_wf_msg_name VARCHAR2(30);
305 l_enabled_flag VARCHAR2(1);
306 l_org_tbl WFMSG_TBL_TYPE;
307 l_wfmsg_tbl WFMSG_TBL_TYPE := p_in_msg_tbl;
308 BEGIN
309 debug('get_msg_name_by_org 1');
310 find_org(l_wfmsg_tbl, p_org_id, l_org_tbl);
311 if( l_org_tbl.COUNT = 0 ) then
312 debug('get_msg_name_by_org 2');
313 find_all_org(l_wfmsg_tbl, l_org_tbl);
314 if( l_org_tbl.COUNT = 0 ) THEN
315 debug('get_msg_name_by_org 3');
316 get_default_msg(l_wf_msg_name, l_enabled_flag, p_notif_setup_id);
317 else
318 debug('get_msg_name_by_org 4');
319 l_wf_msg_name := l_org_tbl(1).message_name;
320 l_enabled_flag := l_org_tbl(1).enabled_flag;
321 end if;
322 else
323 debug('get_msg_name_by_org 5');
324 l_wf_msg_name := l_org_tbl(1).message_name;
325 l_enabled_flag := l_org_tbl(1).enabled_flag;
326 end if;
327 x_wf_msg_name := l_wf_msg_name;
328 x_enabled_flag := l_enabled_flag;
329 END;
330
331 procedure get_msg_name_by_usertype(
332 x_wf_msg_name OUT NOCOPY VARCHAR2,
333 x_enabled_flag OUT NOCOPY VARCHAR2,
334 p_notif_Setup_id IN NUMBER,
335 p_user_type IN VARCHAR2,
336 p_in_msg_tbl IN WFMSG_TBL_TYPE) IS
337 l_wf_msg_name VARCHAR2(30);
338 l_enabled_flag VARCHAR2(1);
339 l_user_type_tbl WFMSG_TBL_TYPE;
340 l_wfmsg_tbl WFMSG_TBL_TYPE := p_in_msg_tbl;
341 BEGIN
342 debug('get_msg_name_by_usertype 1');
343 find_user_type(l_wfmsg_tbl, p_user_type, l_user_type_tbl);
344 if( l_user_type_tbl.COUNT = 0 ) THEN
345 debug('get_msg_name_by_usertype 2');
346 find_all_user_type(l_wfmsg_tbl, l_user_type_tbl);
347 if( l_user_type_tbl.COUNT = 0 ) THEN
348 debug('get_msg_name_by_usertype 3');
349 get_default_msg(l_wf_msg_name, l_enabled_flag, p_notif_setup_id);
350 else
351 debug('get_msg_name_by_usertype 4');
352 l_wf_msg_name := l_user_type_tbl(1).message_name;
353 l_enabled_flag := l_user_type_tbl(1).enabled_flag;
354 end if;
355 else
356 debug('get_msg_name_by_usertype 5');
357 l_wf_msg_name := l_user_type_tbl(1).message_name;
358 l_enabled_flag := l_user_type_tbl(1).enabled_flag;
359 end if;
360 x_wf_msg_name := l_wf_msg_name;
361 x_enabled_flag := l_enabled_flag;
362 END;
363
364 Procedure find_org_no_msite(
365 p_org_id IN NUMBER,
366 p_notif_setup_id IN NUMBER,
367 x_org_tbl OUT NOCOPY WFMSG_TBL_TYPE) IS
368 l_org_tbl WFMSG_TBL_TYPE;
369 cursor org_null_csr(p_notif_setup_id IN NUMBER, p_org_id IN NUMBER) IS
370 select notif_msg_map_id, notif_setup_id, message_name, enabled_flag, msite_id, org_id, user_type,
371 all_msite_flag, all_org_flag, all_user_Type_flag
372 from ibe_wf_notif_msg_maps
373 where notif_setup_id = p_notif_setup_id
374 and org_id is null;
375 cursor org_csr(p_notif_setup_id IN NUMBER, p_org_id IN NUMBER) IS
376 select notif_msg_map_id, notif_setup_id, message_name, enabled_flag, msite_id, org_id, user_type,
377 all_msite_flag, all_org_flag, all_user_Type_flag
378 from ibe_wf_notif_msg_maps
379 where notif_setup_id = p_notif_setup_id
380 and org_id = p_org_id;
381 l_idx NUMBER := 1;
382 l_wfmsg_rec WFMSG_REC_TYPE;
383 BEGIN
384 if( p_org_id is null ) then
385 open org_null_csr(p_notif_setup_id, p_org_id);
386 LOOP
387 fetch org_null_csr into l_wfmsg_rec.notif_msg_map_id, l_wfmsg_Rec.notif_setup_id, l_wfmsg_rec.message_name,
388 l_wfmsg_rec.enabled_flag, l_wfmsg_rec.msite_id, l_wfmsg_rec.org_id, l_wfmsg_rec.user_type,
389 l_wfmsg_rec.all_msite_flag, l_wfmsg_rec.all_org_flag, l_wfmsg_rec.all_user_type_flag;
390 EXIT when org_null_csr%NOTFOUND;
391 l_org_tbl(l_idx) := l_wfmsg_rec;
392 l_idx := l_idx + 1;
393 END LOOP;
394 close org_null_csr;
395 else
396 open org_csr(p_notif_setup_id, p_org_id);
397 LOOP
398 fetch org_csr into l_wfmsg_rec.notif_msg_map_id, l_wfmsg_Rec.notif_setup_id, l_wfmsg_rec.message_name,
402 l_org_tbl(l_idx) := l_wfmsg_rec;
399 l_wfmsg_rec.enabled_flag, l_wfmsg_rec.msite_id, l_wfmsg_rec.org_id, l_wfmsg_rec.user_type,
400 l_wfmsg_rec.all_msite_flag, l_wfmsg_rec.all_org_flag, l_wfmsg_rec.all_user_type_flag;
401 EXIT when org_csr%NOTFOUND;
403 l_idx := l_idx + 1;
404 END LOOP;
405 close org_csr;
406 end if;
407 x_org_tbl := l_org_tbl;
408 END;
409
410 procedure find_all_org_no_msite(
411 p_notif_setup_id IN NUMBER,
412 x_org_tbl OUT NOCOPY WFMSG_TBL_TYPE) IS
413 l_org_tbl WFMSG_TBL_TYPE;
414 cursor org_csr(p_notif_setup_id IN NUMBER) IS
415 select notif_msg_map_id, notif_setup_id, message_name, enabled_flag, msite_id, org_id, user_type,
416 all_msite_flag, all_org_flag, all_user_Type_flag
417 from ibe_wf_notif_msg_maps
418 where notif_setup_id = p_notif_setup_id
419 and all_org_flag = 'Y';
420 l_idx NUMBER := 1;
421 l_wfmsg_rec WFMSG_REC_TYPE;
422 BEGIN
423 open org_csr(p_notif_setup_id);
424 LOOP
425 fetch org_csr into l_wfmsg_rec.notif_msg_map_id, l_wfmsg_rec.notif_setup_id, l_wfmsg_rec.message_name,
426 l_wfmsg_rec.enabled_flag, l_wfmsg_rec.msite_id, l_wfmsg_rec.org_id, l_wfmsg_rec.user_type,
427 l_wfmsg_rec.all_msite_flag, l_wfmsg_rec.all_org_flag, l_wfmsg_rec.all_user_Type_flag;
428 EXIT when org_csr%NOTFOUND;
429 l_org_tbl(l_idx) := l_wfmsg_rec;
430 l_idx := l_idx + 1;
431 END LOOP;
432 close org_csr;
433 x_org_tbl := l_org_tbl;
434 END;
435
436 Procedure find_user_type_only(
437 p_user_type IN VARCHAR2,
438 p_notif_setup_id IN NUMBER,
439 x_user_type_tbl OUT NOCOPY WFMSG_TBL_TYPE) IS
440 l_user_type_tbl WFMSG_TBL_TYPE;
441 cursor user_type_csr(p_notif_setup_id IN NUMBER, p_user_type IN VARCHAR2) IS
442 select notif_msg_map_id, notif_setup_id, message_name, enabled_flag, msite_id, org_id, user_type,
443 all_msite_flag, all_org_flag, all_user_Type_flag
444 from ibe_wf_notif_msg_maps
445 where notif_setup_id = p_notif_setup_id
446 and user_type = p_user_type;
447 l_idx NUMBER := 1;
448 l_wfmsg_rec WFMSG_REC_TYPE;
449 BEGIN
450 open user_type_csr(p_notif_setup_id, p_user_type);
451 LOOP
452 fetch user_type_csr into l_wfmsg_rec.notif_msg_map_id, l_wfmsg_Rec.notif_setup_id, l_wfmsg_rec.message_name,
453 l_wfmsg_rec.enabled_flag, l_wfmsg_rec.msite_id, l_wfmsg_rec.org_id, l_wfmsg_rec.user_type,
454 l_wfmsg_rec.all_msite_flag, l_wfmsg_rec.all_org_flag, l_wfmsg_rec.all_user_type_flag;
455 EXIT when user_type_csr%NOTFOUND;
456 l_user_type_tbl(l_idx) := l_wfmsg_rec;
457 l_idx := l_idx + 1;
458 END LOOP;
459 close user_type_csr;
460 x_user_type_tbl := l_user_type_tbl;
461 END;
462
463 procedure find_all_user_type_only(
464 p_notif_setup_id IN NUMBER,
465 x_user_type_tbl OUT NOCOPY WFMSG_TBL_TYPE) IS
466 l_user_type_tbl WFMSG_TBL_TYPE;
467 cursor user_type_csr(p_notif_setup_id IN NUMBER) IS
468 select notif_msg_map_id, notif_setup_id, message_name, enabled_flag, msite_id, org_id, user_type,
469 all_msite_flag, all_org_flag, all_user_Type_flag
470 from ibe_wf_notif_msg_maps
471 where notif_setup_id = p_notif_setup_id
472 and all_user_type_flag = 'Y';
473 l_idx NUMBER := 1;
474 l_wfmsg_rec WFMSG_REC_TYPE;
475 BEGIN
476 open user_type_csr(p_notif_setup_id);
477 LOOP
478 fetch user_type_csr into l_wfmsg_rec.notif_msg_map_id, l_wfmsg_rec.notif_setup_id, l_wfmsg_rec.message_name,
479 l_wfmsg_rec.enabled_flag, l_wfmsg_rec.msite_id, l_wfmsg_rec.org_id, l_wfmsg_rec.user_type,
480 l_wfmsg_rec.all_msite_flag, l_wfmsg_rec.all_org_flag, l_wfmsg_rec.all_user_Type_flag;
481 EXIT when user_type_csr%NOTFOUND;
482 l_user_type_tbl(l_idx) := l_wfmsg_rec;
483 l_idx := l_idx + 1;
484 END LOOP;
485 close user_type_csr;
486 x_user_type_tbl := l_user_type_tbl;
487 END;
488
489 procedure Retrieve_Msg_Mapping
490 (
491 p_org_id IN NUMBER,
492 p_msite_id IN NUMBER,
493 p_user_type IN VARCHAR2,
494 x_enabled_flag OUT NOCOPY VARCHAR2,
495 p_notif_name IN VARCHAR2,
496 x_wf_message_name OUT NOCOPY VARCHAR2,
497 x_return_status OUT NOCOPY VARCHAR2,
498 x_msg_data OUT NOCOPY VARCHAR2,
499 x_msg_count OUT NOCOPY NUMBER
500 ) IS
501 l_return_status VARCHAR2(1);
502 l_msg_count NUMBER;
503 l_msg_data VARCHAR2(2000);
504 l_org_id_flag VARCHAR2(1);
505 l_msite_id_flag VARCHAR2(1);
506 l_user_type_flag VARCHAR2(1);
507 l_enabled_flag VARCHAR2(1);
508 l_menabled_flag VARCHAR2(1);
509 l_notif_setup_id NUMBER;
510 --l_msg_sql VARCHAR2(2000);
511 l_wf_msg_name VARCHAR2(30);
512 l_all_msite_flag VARCHAR2(1) := 'Y';
513 l_all_org_id_flag VARCHAR2(1) := 'Y';
514 l_all_user_type_flag VARCHAR2(1) := 'Y';
515 l_msite_csr t_genref;
516 l_wfmsg_rec WFMSG_REC_TYPE;
517 l_wfmsg_tbl WFMSG_TBL_TYPE;
518 l_org_tbl WFMSG_TBL_TYPE;
519 l_user_type_tbl WFMSG_TBL_TYPE;
520 l_idx NUMBER := 1;
521 BEGIN
522 debug('retrieve_msg_mapping 1');
523 get_notif_metadata(
527 x_notif_setup_id => l_notif_setup_id,
524 x_msg_count => l_msg_count,
525 x_msg_data => l_msg_data,
526 x_return_status => l_return_status,
528 p_notification_name => p_notif_name,
529 x_org_id_flag => l_org_id_flag,
530 x_msite_id_flag => l_msite_id_flag,
531 x_user_type_flag => l_user_type_flag,
532 x_enabled_flag => l_menabled_flag);
533
534 if( l_return_status = FND_API.G_RET_STS_ERROR ) then
535 raise FND_API.G_EXC_ERROR;
536 elsif( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) then
537 raise FND_API.G_EXC_UNEXPECTED_ERROR;
538 end if;
539
540 debug('retrieve_msg_mapping 2');
541
542 if( l_msite_id_flag = 'Y' AND l_org_id_flag = 'Y' and l_user_type_flag = 'Y' ) then
543 debug('retrieve_msg_mapping Y Y Y 1');
544 find_msite(p_msite_id, l_notif_setup_id, l_wfmsg_tbl);
545 if( l_wfmsg_tbl.COUNT > 0 ) THEN
546 debug('retrieve_msg_mapping Y Y Y 2');
547 get_msgname_by_orgusertype(l_wf_msg_name, l_enabled_flag, l_notif_setup_id, p_org_id, p_user_type, l_wfmsg_tbl);
548 else
549 debug('retrieve_msg_mapping Y Y Y 3');
550 find_all_msite( l_notif_setup_id, l_wfmsg_tbl);
551 if( l_wfmsg_tbl.COUNT > 0 ) THEN
552 debug('retrieve_msg_mapping Y Y Y 4');
553 get_msgname_by_orgusertype(l_wf_msg_name, l_enabled_flag, l_notif_setup_id, p_org_id, p_user_type, l_wfmsg_tbl);
554 else
555 debug('retrieve_msg_mapping Y Y Y 5');
556 get_default_msg(l_wf_msg_name, l_enabled_flag, l_notif_setup_id);
557 END IF;
558 END IF;
559 elsif( l_msite_id_flag = 'Y' AND l_org_id_flag = 'Y' AND l_user_type_flag = 'N' ) then
560 find_msite(p_msite_id, l_notif_setup_id, l_wfmsg_tbl);
561 if( l_wfmsg_tbl.COUNT > 0 ) THEN
562 get_msg_name_by_org(l_wf_msg_name, l_enabled_flag, l_notif_setup_id, p_org_id, l_wfmsg_tbl);
563 else
564 find_all_msite( l_notif_setup_id, l_wfmsg_tbl);
565 if( l_wfmsg_tbl.COUNT > 0 ) then
566 get_msg_name_by_org(l_wf_msg_name, l_enabled_flag, l_notif_setup_id, p_org_id, l_wfmsg_tbl);
567 else
568 get_default_msg(l_wf_msg_name, l_enabled_flag, l_notif_setup_id);
569 END IF;
570 END IF;
571 elsif( l_msite_id_flag = 'Y' AND l_org_id_flag = 'N' AND l_user_type_flag = 'Y' ) then
572 debug('retrieve_msg_mapping Y N Y 1');
573 find_msite(p_msite_id, l_notif_setup_id, l_wfmsg_tbl);
574 debug('retrieve_msg_mapping Y N Y 2');
575 if( l_wfmsg_tbl.COUNT > 0) then
576 debug('retrieve_msg_mapping Y N Y 3');
577 get_msg_name_by_usertype(l_wf_msg_name, l_enabled_flag, l_notif_setup_id, p_user_type, l_wfmsg_tbl);
578 else
579 debug('retrieve_msg_mapping Y N Y 4');
580 find_all_msite( l_notif_setup_id, l_wfmsg_Tbl);
581 if( l_wfmsg_tbl.COUNT > 0 ) THEN
582 debug('retrieve_msg_mapping Y N Y 5');
583 get_msg_name_by_usertype(l_wf_msg_name, l_enabled_flag, l_notif_setup_id, p_user_type, l_wfmsg_tbl);
584 else
585 debug('retrieve_msg_mapping Y N Y 6');
586 get_default_msg(l_wf_msg_name, l_enabled_flag, l_notif_setup_id);
587 END IF;
588 END IF;
589 elsif( l_msite_id_flag = 'Y' AND l_org_id_flag = 'N' AND l_user_type_flag = 'N' ) then
590 find_msite(p_msite_id, l_notif_setup_id, l_wfmsg_tbl);
591 if( l_wfmsg_tbl.COUNT > 0 ) THEN
592 l_wf_msg_name := l_wfmsg_tbl(1).message_name;
593 else
594 find_all_msite( l_notif_setup_id, l_wfmsg_tbl);
595 if( l_wfmsg_tbl.COUNT > 0 ) THEN
596 l_wf_msg_name := l_wfmsg_tbl(1).message_name;
597 l_enabled_flag := l_wfmsg_tbl(1).enabled_flag;
598 else
599 get_default_msg(l_wf_msg_name, l_enabled_flag, l_notif_setup_id);
600 END IF;
601 END IF;
602 elsif( l_msite_id_flag = 'N' AND l_org_id_flag = 'Y' AND l_user_type_flag = 'Y' ) then
603 find_org_no_msite(p_org_id, l_notif_setup_id, l_wfmsg_tbl);
604 if( l_wfmsg_tbl.COUNT > 0 ) THEN
605 get_msg_name_by_usertype(l_wf_msg_name, l_enabled_flag, l_notif_setup_id, p_user_type, l_wfmsg_tbl);
606 else
607 find_all_org_no_msite(l_notif_setup_id, l_wfmsg_tbl);
608 if( l_wfmsg_tbl.COUNT > 0) THEN
609 get_msg_name_by_usertype(l_wf_msg_name, l_enabled_flag, l_notif_setup_id, p_user_type, l_wfmsg_tbl);
610 else
611 get_default_msg(l_wf_msg_name, l_enabled_flag, l_notif_setup_id);
612 end if;
613 end if;
614 elsif( l_msite_id_flag = 'N' AND l_org_id_flag = 'Y' AND l_user_type_flag = 'N' ) then
615 find_org_no_msite(p_org_id, l_notif_setup_id, l_wfmsg_tbl);
616 if( l_wfmsg_tbl.COUNT > 0 ) then
617 l_wf_msg_name := l_wfmsg_tbl(1).message_name;
618 l_enabled_flag := l_wfmsg_tbl(1).enabled_flag;
619 else
620 find_all_org_no_msite(l_notif_setup_id, l_wfmsg_tbl);
621 if( l_wfmsg_tbl.COUNT > 0 ) THEN
622 l_wf_msg_name := l_wfmsg_tbl(1).message_name;
623 l_enabled_flag := l_wfmsg_tbl(1).enabled_flag;
624 else
625 get_default_msg(l_wf_msg_name, l_enabled_flag, l_notif_setup_id);
626 end if;
627 END IF;
628 elsif( l_msite_id_flag = 'N' AND l_org_id_flag = 'N' AND l_user_type_flag = 'Y' ) then
629 find_user_type_only(p_user_type, l_notif_setup_id, l_wfmsg_tbl);
630 if( l_wfmsg_tbl.COUNT > 0 ) then
631 l_wf_msg_name := l_wfmsg_tbl(1).message_name;
632 l_enabled_flag := l_wfmsg_tbl(1).enabled_flag;
633 else
637 l_enabled_flag := l_wfmsg_tbl(1).enabled_flag;
634 find_all_user_type_only(l_notif_setup_id, l_wfmsg_tbl);
635 if( l_wfmsg_tbl.COUNT > 0 ) THEN
636 l_wf_msg_name := l_wfmsg_tbl(1).message_name;
638 else
639 get_default_msg(l_wf_msg_name, l_enabled_flag, l_notif_setup_id);
640 end if;
641 END IF;
642 elsif( l_msite_id_flag = 'N' AND l_org_id_flag = 'N' AND l_user_type_flag = 'N' ) then
643 BEGIN
644 select message_name, enabled_flag
645 into l_wf_msg_name, l_enabled_flag
646 from IBE_WF_NOTIF_MSG_MAPS
647 where notif_setup_id = l_notif_setup_id
648 AND default_msg_map_flag <> 'Y'
649 and rownum < 2;
650 EXCEPTION
651 when NO_DATA_FOUND THEN
652 get_default_msg(l_wf_msg_name, l_enabled_flag, l_notif_setup_id);
653 END;
654 end if;
655 x_wf_message_name := l_wf_msg_name;
656 x_enabled_flag := l_enabled_flag;
657 x_return_status := FND_API.G_RET_STS_SUCCESS;
658 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
659 x_msg_data := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
660 EXCEPTION
661 WHEN NO_DATA_FOUND THEN
662 x_return_status := FND_API.G_RET_STS_ERROR;
663 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_MSG_MAP_NOT_FOUND');
664 FND_MESSAGE.SET_TOKEN('NOTIF', p_notif_name);
665 FND_MSG_PUB.ADD;
666 FND_MSG_PUB.Count_And_Get(p_count=> x_msg_count, p_data => x_msg_data);
667 x_msg_data := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
668
669 WHEN FND_API.G_EXC_ERROR THEN
670 x_return_status := FND_API.G_RET_STS_ERROR;
671 -- Get message count and data
672 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
673 x_msg_data := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
674
675 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
676 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
677 -- Get message count and data
678 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
679 x_msg_data := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
680
681 WHEN OTHERS THEN
682 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
683 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
684 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Retrieve_Msg_Mapping');
685 END IF;
686
687 -- Get message count and data
688 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
689 x_msg_data := FND_MSG_PUB.GET(1, FND_API.G_FALSE);
690 END Retrieve_Msg_Mapping;
691
692 procedure Get_Notif_Metadata
693 (
694 x_msg_count OUT NOCOPY NUMBER,
695 x_msg_data OUT NOCOPY VARCHAR2,
696 x_return_status OUT NOCOPY VARCHAR2,
697 x_notif_setup_id OUT NOCOPY NUMBER,
698 p_notification_name IN VARCHAR2,
699 x_org_id_flag OUT NOCOPY VARCHAR2,
700 x_msite_id_flag OUT NOCOPY VARCHAR2,
701 x_user_type_flag OUT NOCOPY VARCHAR2,
702 x_enabled_flag OUT NOCOPY VARCHAR2
703 ) IS
704 l_org_id_flag VARCHAR2(1);
705 l_msite_id_flag VARCHAR2(1);
706 l_user_type_flag VARCHAR2(1);
707 l_enabled_flag VARCHAR2(1);
708 l_notif_setup_id NUMBER;
709 BEGIN
710
711 select org_id_flag, msite_id_flag, enabled_flag, notif_setup_id, user_type_flag
712 into l_org_id_flag, l_msite_id_flag, l_enabled_flag, l_notif_setup_id, l_user_type_flag
713 from ibe_wf_notif_setup
714 where notification_name = p_notification_name;
715
716 x_return_status := FND_API.G_RET_STS_SUCCESS;
717 x_org_id_flag := l_org_id_flag;
718 x_msite_id_flag := l_msite_id_flag;
719 x_user_type_flag := l_user_type_flag;
720 x_enabled_flag := l_enabled_flag;
721 x_notif_setup_id := l_notif_setup_id;
722 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
723
724 EXCEPTION
725 when NO_DATA_FOUND then
726 FND_MESSAGE.SET_NAME('IBE', 'IBE_WF_NOTIF_NAME_NOT_FOUND');
727 FND_MESSAGE.SET_TOKEN('NAME', p_notification_name);
728 FND_MSG_PUB.ADD;
729 x_return_status := FND_API.G_RET_STS_ERROR;
730 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
731 END Get_Notif_Metadata;
732
733
734 END IBE_WF_MSG_MAPPING_PVT;