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