[Home] [Help]
PACKAGE BODY: APPS.FND_OAM_KBF_SUBS
Source
1 PACKAGE BODY FND_OAM_KBF_SUBS AS
2 /* $Header: AFOAMSBB.pls 120.4.12020000.2 2012/07/03 13:01:28 rlandows ship $ */
3
4
5 --Common constants
6 procedure fdebug(msg in varchar2);
7
8 ---------------------------------------------------------------------------------
9 --Private Functions
10 ---------------------------------------------------------------------------------
11 FUNCTION HAS_NOTIFIED(
12 pSubID IN FND_OAM_BIZEX_SUBSCRIP.SUBSCRIPTION_ID%TYPE
13 , pUniqueExId IN FND_LOG_EXCEPTIONS.UNIQUE_EXCEPTION_ID%TYPE
14 )
15 RETURN BOOLEAN
16 IS
17 l_retu BOOLEAN;
18 l_count NUMBER;
19 BEGIN
20 fdebug('In:FND_OAM_KBF_SUBS.HAS_NOTIFIED');
21 fdebug('pSubID:' || pSubID);
22 fdebug('pUniqueExId:' || pUniqueExId);
23
24 select count(*) into l_count
25 from FND_OAM_BIZEX_SENT_NOTIF
26 where
27 UNIQUE_EXCEPTION_ID = pUniqueExId
28 and SUBSCRIPTION_ID = pSubID
29 ;
30
31 IF l_count > 0 THEN
32 fdebug('TRUE');
33 l_retu := TRUE;
34 ELSE
35 fdebug('FALSE');
36 l_retu := FALSE;
37 END IF;
38
39 fdebug('Out:FND_OAM_KBF_SUBS.SHALL_ADD_SUBS');
40 RETURN (l_retu);
41 EXCEPTION
42 WHEN NO_DATA_FOUND THEN
43 RETURN (FALSE);
44 END HAS_NOTIFIED;
45 --------------------------------------------------------------------------------
46 FUNCTION SHALL_ADD_SUBS
47 (pItemSub IN VARCHAR2, pItemException IN VARCHAR2)
48 RETURN BOOLEAN
49 IS
50 l_retu BOOLEAN;
51 BEGIN
52 fdebug('In:FND_OAM_KBF_SUBS.SHALL_ADD_SUBS');
53 fdebug('pItemSub:' || pItemSub);
54 fdebug('pItemException:' || pItemException);
55
56 IF TRIM(pItemSub) IS NULL THEN
57 -- fdebug('In:Null');
58 l_retu := TRUE;
59 ELSIF TRIM(pItemSub) = 'ANY' THEN
60 l_retu := TRUE;
61 ELSIF TRIM(pItemSub) = TRIM(pItemException) THEN
62 l_retu := TRUE;
63 ELSE
64 -- fdebug('In:Else');
65 l_retu := FALSE;
66 END IF;
67
68 IF l_retu = FALSE THEN
69 fdebug('Return=' || 'False');
70 ELSE
71 fdebug('Return=' || 'TRUE');
72 END IF;
73 fdebug('Out:FND_OAM_KBF_SUBS.SHALL_ADD_SUBS');
74 RETURN (l_retu);
75 EXCEPTION
76 WHEN NO_DATA_FOUND THEN
77 RETURN (FALSE);
78 END SHALL_ADD_SUBS;
79 --------------------------------------------------------------------------------
80
81 /* 6874184
82 * There are 4 severities that can be subscribed to in OAM:
83 * CRITICAL
84 * ERROR
85 * WARNING
86 * ANY
87 *
88 * These are stored in FND_OAM_BIZEX_SUBSCRIP and are passed in to the
89 * function below as pItemSub.
90 *
91 *
92 *
93 * A message in the message dictionary will have a severity of:
94 * CRITICAL
95 * ERROR
96 * WARNING
97 *
98 * pItemException comes from fnd_log_unique_exceptions which is ultimately
99 * from the message dictionary.
100 *
101 *
102 * Below logic should be:
103 *
104 * if setup to get a notification for ANY return true
105 *
106 * if setup to get a notification for WARNING return true (because that is the
107 * lowest level in the message dictionary).
108 *
109 * if setup to get a notification for ERROR return true if the severity in
110 * fnd_log_unique_exceptions is ERROR or CRITICAL.
111 *
112 * if setup to only get CRITICAL notifications only return true if
113 * fnd_log_unique_exceptions has a CRITICAL severity.
114 *
115 * */
116
117
118
119 FUNCTION SHALL_ADD_SUBS_SEVERITY
120 (pItemSub IN VARCHAR2, pItemException IN VARCHAR2)
121 RETURN BOOLEAN
122 IS
123 l_retu BOOLEAN;
124 BEGIN
125 fdebug('In:FND_OAM_KBF_SUBS.SHALL_ADD_SUBS_SEVERITY');
126 fdebug('pItemSub:' || pItemSub);
127 fdebug('pItemException:' || pItemException);
128
129 IF TRIM(pItemSub) IS NULL THEN
130 fdebug('In:Null');
131 l_retu := TRUE;
132 ELSIF TRIM(pItemSub) = TRIM(pItemException) THEN
133 l_retu := TRUE;
134 ELSIF TRIM(pItemSub) = 'ANY' THEN
135 l_retu := TRUE;
136 ELSIF TRIM(pItemSub) = 'WARNING' THEN
137 l_retu := TRUE;
138 --6874184, modified logic below
139 ELSIF TRIM(pItemSub) = 'ERROR' THEN
140 fdebug('In:ERROR');
141 IF TRIM(pItemException) = 'CRITICAL' THEN
142 l_retu := TRUE;
143 ELSE
144 l_retu := FALSE;
145 END IF;
146 ELSE
147 -- fdebug('In:Else');
148 l_retu := FALSE;
149 END IF;
150
151
152 --For debug
153 IF l_retu = FALSE THEN
154 fdebug('Return=' || 'False');
155 ELSE
156 fdebug('Return=' || 'TRUE');
157 END IF;
158
159 fdebug('Out:FND_OAM_KBF_SUBS.SHALL_ADD_SUBS_SEVERITY');
160 RETURN (l_retu);
161 EXCEPTION
162 WHEN NO_DATA_FOUND THEN
163 RETURN (FALSE);
164 END SHALL_ADD_SUBS_SEVERITY;
165
166
167 -------------------------------------------------------------------------------
168 procedure retriveComponentInfo(app_id in number,
169 comp_type in varchar2,
170 comp_id in number,
171 comp_name out NOCOPY varchar2,
172 comp_display_name out NOCOPY varchar2)
173 IS
174 l_comp_name FND_APP_COMPONENTS_VL.COMPONENT_NAME%TYPE;
175 l_comp_display_name FND_APP_COMPONENTS_VL.DISPLAY_NAME%TYPE;
176 BEGIN
177 fdebug('In:FND_OAM_KBF_SUBS.retriveComponentInfo');
178 fdebug('app_id=' || app_id || ' comp_type=' || comp_type || ' comp_id=' || comp_id );
179
180 IF (comp_type = 'CONCURRENT_PROGRAM') THEN
181 SELECT
182 b.CONCURRENT_PROGRAM_NAME, t.USER_CONCURRENT_PROGRAM_NAME
183 INTO
184 l_comp_name, l_comp_display_name
185 --, b.APPLICATION_ID, b.CONCURRENT_PROGRAM_ID, t.DESCRIPTION
186 FROM
187 FND_CONCURRENT_PROGRAMS B,FND_CONCURRENT_PROGRAMS_TL t
188 WHERE
189 b.application_id = app_id
190 and b.concurrent_program_id = comp_id
191 and b.application_id = t.application_id
192 and b.concurrent_program_id = t.concurrent_program_id
193 and t.language = userenv('LANG');
194
195 ELSIF(comp_type = 'FORM') THEN
196 SELECT
197 b.FORM_NAME, t.USER_FORM_NAME
198 INTO
199 l_comp_name, l_comp_display_name
200 --, b.APPLICATION_ID, b.FORM_ID, t.DESCRIPTION
201 FROM
202 FND_FORM B, FND_FORM_TL t
203 WHERE
204 b.application_id = app_id
205 and b.form_id = comp_id
206 and b.application_id = t.application_id
207 and b.form_id = t.form_id
208 and t.language = userenv('LANG');
209
210 ELSIF(comp_type = 'SERVICE_INSTANCE') THEN
211 SELECT
212 b.CONCURRENT_QUEUE_NAME, t.USER_CONCURRENT_QUEUE_NAME
213 INTO
214 l_comp_name, l_comp_display_name
215 --, b.APPLICATION_ID, b.concurrent_queue_id , t.DESCRIPTION
216 FROM
217 FND_CONCURRENT_QUEUES b, FND_CONCURRENT_QUEUES_TL t
218 WHERE
219 b.application_id = app_id
220 and b.concurrent_queue_id = comp_id
221 and b.application_id = t.application_id
222 and b.concurrent_queue_id = t.concurrent_queue_id
223 and t.language = userenv('LANG');
224
225 ELSIF(comp_type = 'FUNCTION') THEN
226
227 --10254432
228 begin
229 SELECT
230 b.function_name, t.user_function_name
231 INTO
232 l_comp_name, l_comp_display_name
233 --, b.APPLICATION_ID, b.function_id, t.DESCRIPTION
234 FROM
235 FND_FORM_FUNCTIONS b, FND_FORM_FUNCTIONS_TL t
236 WHERE
237 b.function_id = comp_id
238 and b.function_id = t.function_id
239 and t.language = userenv('LANG');
240 exception when others then
241 l_comp_name := 'UNKNOWN';
242 l_comp_display_name := 'UNKNOWN';
243 end;
244
245 END IF;
246
247
248 comp_name :=l_comp_name;
249 comp_display_name :=l_comp_display_name;
250
251 fdebug('Component Name=' || l_comp_name || ' Component Display Name=' || l_comp_display_name);
252
253 fdebug('OUT:FND_OAM_KBF_SUBS.retriveComponentInfo');
254 END retriveComponentInfo;
255
256 --------------------------------------------------------------------------------
257 procedure setWFAttributes(itemtype in varchar2,
258 itemkey in varchar2)
259 IS
260 l_app_sn FND_APPLICATION_VL.APPLICATION_SHORT_NAME%TYPE;
261 l_comp_sn FND_APP_COMPONENTS_VL.COMPONENT_NAME%TYPE;
262 l_comp_fn FND_APP_COMPONENTS_VL.DISPLAY_NAME%TYPE;
263 l_severity FND_LOG_EXCEPTIONS.SEVERITY%TYPE;
264 l_system VARCHAR2(200);
265 l_app_id FND_LOG_TRANSACTION_CONTEXT.COMPONENT_APPL_ID%TYPE;
266 l_comp_id FND_LOG_TRANSACTION_CONTEXT.COMPONENT_ID%TYPE;
267 l_comp_type FND_LOG_TRANSACTION_CONTEXT.COMPONENT_TYPE%TYPE;
268 BEGIN
269 fdebug('In:FND_OAM_KBF_SUBS.setWFAttributes');
270
271 SELECT fa.application_short_name
272 , flue.severity, fltc.component_appl_id, fltc.component_type
273 , fltc.component_id
274 INTO
275 l_app_sn, l_severity, l_app_id, l_comp_type, l_comp_id
276 FROM fnd_log_transaction_context fltc,
277 fnd_log_messages flm,
278 fnd_log_exceptions fle,
279 FND_LOG_UNIQUE_EXCEPTIONS flue,
280 fnd_application fa
281 WHERE
282 flm.log_sequence = to_number(itemkey)
283 and flm.log_sequence = fle.log_sequence
284 and fle.UNIQUE_EXCEPTION_ID = flue.UNIQUE_EXCEPTION_ID
285 and fltc.transaction_context_id = flm.transaction_context_id
286 and fltc.component_appl_id = fa.application_id (+);
287
288 retriveComponentInfo(l_app_id, l_comp_type, l_comp_id, l_comp_sn, l_comp_fn);
289 select VALUE into l_system from V$PARAMETER where NAME='db_name';
290
291 fdebug('l_app_id=' || l_app_id);
292 fdebug('l_app_sn=' || l_app_sn);
293 fdebug('l_comp_type=' || l_comp_type);
294 fdebug('l_comp_id=' || l_comp_id);
295 fdebug('l_comp_sn=' || l_comp_sn);
296 fdebug('l_comp_fn=' || l_comp_fn);
297 fdebug('l_severity=' || l_severity);
298
299
300 WF_ENGINE.SetItemAttrtext(itemtype, itemkey, 'SYSTEM', l_system);
301 WF_ENGINE.SetItemAttrtext(itemtype, itemkey, 'APP_SHORT_NAME', l_app_sn);
302 WF_ENGINE.SetItemAttrtext(itemtype, itemkey, 'COMP_SHORT_NAME', l_comp_sn);
303 WF_ENGINE.SetItemAttrtext(itemtype, itemkey, 'SEVERITY', l_severity);
304
305 fdebug('Out:FND_OAM_KBF_SUBS.setWFAttributes');
306 END setWFAttributes;
307 --------------------------------------------------------------------------------
308 PROCEDURE createSubList(itemtype in varchar2,
309 itemkey in varchar2,
310 actid in number,
311 funcmode in varchar2,
312 resultout out NOCOPY varchar2)
313 IS
314 l_unique_ex_id FND_LOG_EXCEPTIONS.UNIQUE_EXCEPTION_ID%TYPE;
315 l_msg_id FND_LOG_EXCEPTIONS.LOG_SEQUENCE%TYPE;
316 l_app_id FND_LOG_TRANSACTION_CONTEXT.COMPONENT_APPL_ID%TYPE;
317 l_comp_type FND_LOG_TRANSACTION_CONTEXT.COMPONENT_TYPE%TYPE;
318 l_comp_type_s FND_LOG_TRANSACTION_CONTEXT.COMPONENT_TYPE%TYPE;
319 l_comp_id FND_LOG_TRANSACTION_CONTEXT.COMPONENT_ID%TYPE;
320 -- l_biz_flow_id FND_LOG_EXCEPTION_CONTEXT.LOG_SEQUENCE%TYPE;
321 l_category FND_LOG_EXCEPTIONS.CATEGORY%TYPE;
322 l_severity FND_LOG_EXCEPTIONS.SEVERITY%TYPE;
323
324 l_sub_list VARCHAR2(32000);
325 l_role_name VARCHAR2(2000);
326 l_display_name VARCHAR2(100);
327
328 err_num NUMBER;
329 err_msg VARCHAR2(100);
330 l_BE_SUBJECT VARCHAR2(100);
331 l_BE_MESSAGE1 VARCHAR2(100);
332 l_BE_MESSAGE2 VARCHAR2(100);
333 l_role_users wf_directory.userTable;
334 l_ii NUMBER;
335
336
337 CURSOR subs_cur is
338 SELECT subscription_id, role_id, component_type, severity, category, component_id
339 FROM FND_OAM_BIZEX_SUBSCRIP
340 WHERE
341 (application_id = l_app_id)
342 OR(application_id IS NULL)
343 ;
344
345 BEGIN
346 fdebug('In:FND_OAM_KBF_SUBS.CreateSubList');
347 fdebug('itemkey:' || itemkey);
348
349 SELECT fltc.COMPONENT_APPL_ID, fltc.COMPONENT_TYPE
350 ,flue.CATEGORY, flue.SEVERITY
351 ,fltc.component_id, fle.UNIQUE_EXCEPTION_ID
352 INTO
353 l_app_id, l_comp_type
354 ,l_category, l_severity
355 ,l_comp_id, l_unique_ex_id
356 FROM FND_LOG_MESSAGES flm
357 ,FND_LOG_TRANSACTION_CONTEXT fltc
358 ,FND_LOG_EXCEPTIONS fle
359 ,FND_LOG_UNIQUE_EXCEPTIONS flue
360 WHERE
361 flm.LOG_SEQUENCE = TO_NUMBER(itemkey)
362 AND fle.LOG_SEQUENCE = flm.LOG_SEQUENCE
363 AND fltc.TRANSACTION_CONTEXT_ID = flm.TRANSACTION_CONTEXT_ID
364 AND fle.UNIQUE_EXCEPTION_ID = flue.UNIQUE_EXCEPTION_ID;
365
366 l_ii := 0;
367 FOR subs_record in subs_cur LOOP
368 l_comp_type_s := subs_record.component_type;
369
370 --Check if Notification is already send.
371 IF (HAS_NOTIFIED(subs_record.subscription_id, l_unique_ex_id) = TRUE) THEN
372 GOTO next_record;
373 END IF;
374
375 IF (
376 (l_comp_type_s = COMP_TYPE_UNKNOWN)
377 AND (
378 (l_app_id IS NULL)
379 OR(l_app_id = -1)
380 OR(l_comp_type IS NULL)
381 OR(l_comp_id IS NULL)
382 OR(l_comp_id = -1)
383 )
384 ) THEN
385 --UnKnown Type
386 IF (SHALL_ADD_SUBS(subs_record.CATEGORY, l_category) = FALSE) THEN
387 GOTO next_record;
388 END IF;
389 IF (SHALL_ADD_SUBS_SEVERITY(subs_record.severity, l_severity) = FALSE) THEN
390 GOTO next_record;
391 END IF; --For Unknown
392 ELSE----------------------------------Known Types
393 IF (SHALL_ADD_SUBS(subs_record.component_type, l_comp_type) = FALSE) THEN
394 GOTO next_record;
395 END IF;
396 IF (SHALL_ADD_SUBS(subs_record.CATEGORY, l_category) = FALSE) THEN
397 GOTO next_record;
398 END IF;
399 IF (SHALL_ADD_SUBS_SEVERITY(subs_record.severity, l_severity) = FALSE) THEN
400 GOTO next_record;
401 END IF;
402 IF (SHALL_ADD_SUBS(subs_record.component_id, l_comp_id) = FALSE) THEN
403 GOTO next_record;
404 END IF;
405
406 END IF; --Else (known Types)
407
408 --Check If already Added to List
409 IF (instr(l_sub_list, subs_record.role_id) > 0) THEN
410 fdebug('Skip role already added=' || subs_record.role_id);
411 GOTO next_record;
412 END IF;
413
414
415 --Add to List
416 l_ii := l_ii + 1;
417 l_role_users(l_ii) := subs_record.role_id;
418 IF l_sub_list IS NULL THEN
419 l_sub_list := subs_record.role_id;
420 ELSE
421 l_sub_list := l_sub_list || ','|| subs_record.role_id;
422 END IF;
423 insert into FND_OAM_BIZEX_SENT_NOTIF(UNIQUE_EXCEPTION_ID, SUBSCRIPTION_ID, SENT) values
424 (l_unique_ex_id, subs_record.SUBSCRIPTION_ID, sysdate);
425
426 ---fdebug('l_sub_list=' || l_sub_list);
427
428 <<next_record>> --Go to next record
429 fdebug('Skip role(no match)=' || subs_record.role_id);
430 NULL;
431 END LOOP; --subs_cur
432
433 fdebug('l_sub_list=' || l_sub_list);
434
435
436 --- This is for test
437 --- l_sub_list := 'RMOHAN2';
438
439 IF l_sub_list IS NOT NULL THEN
440 fdebug('Valid List: Calling CreateAdHocRole');
441 WF_DIRECTORY.CreateAdHocRole2(role_name=>l_role_name
442 , role_display_name=>l_display_name, ROLE_USERS=>l_role_users);
443 WF_ENGINE.SetItemAttrtext(itemtype, itemkey, 'ADHC_ROLE_NAME'
444 , l_role_name);
445
446 --Sets other attributes as app short name severity etc.
447 setWFAttributes(itemtype, itemkey);
448
449 l_BE_SUBJECT := 'plsql:FND_OAM_KBF_SUBS.createSubject/' || itemkey;
450 fdebug('l_BE_SUBJECT=' || l_BE_SUBJECT);
451 WF_ENGINE.SetItemAttrtext(itemtype, itemkey, 'BE_MAIL_SUBJECT'
452 , l_BE_SUBJECT);
453
454 l_BE_MESSAGE1 := 'plsql:FND_OAM_KBF_SUBS.createBusExcepDoc/' || itemkey;
455 fdebug('l_BE_MESSAGE1=' || l_BE_MESSAGE1);
456 WF_ENGINE.SetItemAttrtext(itemtype, itemkey, 'BE_MESSAGE1'
457 , l_BE_MESSAGE1);
458
459 l_BE_MESSAGE2 := 'plsql:FND_OAM_KBF_SUBS.createBusExcepDocPart1/' || itemkey;
460 fdebug('l_BE_MESSAGE2=' || l_BE_MESSAGE2);
461 WF_ENGINE.SetItemAttrtext(itemtype, itemkey, 'EMAIL_BODY_PART1'
462 , l_BE_MESSAGE2);
463
464 ELSE
465 l_sub_list := 'NULL';
466 ----This is for test due to wf bug
467 --- WF_ENGINE.SetItemAttrtext(itemtype, itemkey, 'ADHC_ROLE_NAME'
468 --- , l_sub_list);
469 END IF;
470
471
472
473 resultout := l_sub_list;
474
475 fdebug('Out:FND_OAM_KBF_SUBS.CreateSubList');
476 --- EXCEPTION
477 --- WHEN OTHERS THEN
478 --- err_num := SQLCODE;
479 --- err_msg := SUBSTR(SQLERRM, 1, 1000);
480 --- fdebug('Error:FND_OAM_KBF_SUBS.CreateSubList.');
481 --- fdebug('Error Num: ' || err_num);
482 --- fdebug('Error Msg: ' || err_msg);
483
484 --- raise;
485
486 END createSubList;
487 ---------------------------------------------------------------------------------
488
489
490 procedure createSubject(document_id in varchar2,
491 display_type in varchar2,
492 document in out NOCOPY varchar2,
493 document_type in out NOCOPY varchar2)
494 IS
495 l_app_sn FND_APPLICATION_VL.APPLICATION_SHORT_NAME%TYPE;
496 l_comp_sn FND_APP_COMPONENTS_VL.COMPONENT_NAME%TYPE;
497 l_severity fnd_lookups.MEANING%TYPE; --FND_LOG_EXCEPTIONS.SEVERITY%TYPE;
498 l_system VARCHAR2(200);
499
500 l_app_id FND_LOG_TRANSACTION_CONTEXT.COMPONENT_APPL_ID%TYPE;
501 l_comp_id FND_LOG_TRANSACTION_CONTEXT.COMPONENT_ID%TYPE;
502 l_comp_type FND_LOG_TRANSACTION_CONTEXT.COMPONENT_TYPE%TYPE;
503 l_comp_fn FND_APP_COMPONENTS_VL.DISPLAY_NAME%TYPE;
504 BEGIN
505 fdebug('In:FND_OAM_KBF_SUBS.createBusExcepDocSubject');
506
507 SELECT
508 fa.application_short_name, fl.meaning,
509 fltc.component_appl_id, fltc.component_type, fltc.component_id
510 INTO
511 l_app_sn, l_severity, l_app_id, l_comp_type, l_comp_id
512 FROM fnd_log_transaction_context fltc,
513 fnd_log_messages flm,
514 fnd_log_exceptions fle,
515 FND_LOG_UNIQUE_EXCEPTIONS flue,
516 fnd_application_vl fa,
517 fnd_lookups fl
518 WHERE
519 flm.log_sequence = document_id
520 and flm.log_sequence = fle.log_sequence
521 and fle.UNIQUE_EXCEPTION_ID = flue.UNIQUE_EXCEPTION_ID
522 and fltc.transaction_context_id = flm.transaction_context_id
523 and fltc.component_appl_id = fa.application_id (+)
524 and flue.severity = fl.lookup_code (+)
525 and fl.lookup_type = 'FND_KBF_SEVERITY'
526 and flm.log_sequence = document_id;
527
528 retriveComponentInfo(l_app_id, l_comp_type, l_comp_id, l_comp_sn, l_comp_fn);
529 select VALUE into l_system from V$PARAMETER where NAME='db_name';
530
531 FND_MESSAGE.CLEAR;
532 FND_MESSAGE.SET_NAME(application=>'FND', name=>'AF_OAM_BE_SUB');
533 FND_MESSAGE.SET_TOKEN(token=>'SYSTEM', value=>l_system);
534 FND_MESSAGE.SET_TOKEN(token=>'SEVERITY', value=>l_severity);
535 FND_MESSAGE.SET_TOKEN(token=>'APP', value=>l_app_sn);
536 FND_MESSAGE.SET_TOKEN(token=>'COMP', value=>l_comp_sn);
537 document := FND_MESSAGE.GET;
538 FND_MESSAGE.CLEAR;
539
540 fdebug('Subject:'|| document);
541 fdebug('Out:FND_OAM_KBF_SUBS.createBusExcepDocSubject');
542 /*
543 EXCEPTION
544 WHEN OTHERS THEN
545 fdebug('Error:FND_OAM_KBF_SUBS.createBusExcepDocSubject.');
546 raise;
547 */
548 END createSubject;
549
550
551
552
553 procedure createBusExcepDoc(document_id in varchar2,
554 display_type in varchar2,
555 document in out NOCOPY varchar2,
556 document_type in out NOCOPY varchar2)
557 IS
558 l_msg_id FND_LOG_EXCEPTIONS.LOG_SEQUENCE%TYPE;
559 BEGIN
560 fdebug('In:FND_OAM_KBF_SUBS.createBusExcepDoc');
561
562 l_msg_id := TO_NUMBER(document_id);
563 document := FND_LOG.GET_TEXT(l_msg_id);
564 --document := 'Rm test:document_id '|| document_id ;
565 document_type := 'text/plain';
566 fdebug('document' || document);
567
568 fdebug('Out:FND_OAM_KBF_SUBS.createBusExcepDoc');
569 END createBusExcepDoc;
570
571
572
573
574 procedure createBusExcepDocPart1(document_id in varchar2,
575 display_type in varchar2,
576 document in out NOCOPY varchar2,
577 document_type in out NOCOPY varchar2)
578 IS
579 l_msg_id FND_LOG_EXCEPTIONS.LOG_SEQUENCE%TYPE;
580 l_subject VARCHAR2(200);
581
582 l_app_sn FND_APPLICATION_VL.APPLICATION_SHORT_NAME%TYPE;
583 l_app_fn FND_APPLICATION_VL.APPLICATION_NAME%TYPE;
584
585 l_comp_sn FND_APP_COMPONENTS_VL.COMPONENT_NAME%TYPE;
586
587 -- l_biz_flow_id FND_LOG_EXCEPTION_CONTEXT.LOG_SEQUENCE%TYPE;
588 l_comp_type_d fnd_lookups.MEANING%TYPE;
589
590 l_app_id FND_LOG_TRANSACTION_CONTEXT.COMPONENT_APPL_ID%TYPE;
591 l_comp_id FND_LOG_TRANSACTION_CONTEXT.COMPONENT_ID%TYPE;
592 l_comp_type FND_LOG_TRANSACTION_CONTEXT.COMPONENT_TYPE%TYPE;
593 l_comp_fn FND_APP_COMPONENTS_VL.DISPLAY_NAME%TYPE;
594
595 BEGIN
596 fdebug('In:FND_OAM_KBF_SUBS.createBusExcepDocPart1');
597 ---Set up the Subject
598 --10254432, added decode in the where clause. The row in fnd_log_transaction_context is
599 --created in fnd_log_repository.INIT_TRANS_INT_WITH_CONTEXT and from what I can see there is
600 --no ICX_APP_MODULE there. But the lookups are seeded this way. It may be safest to just use
601 --a decode here in case anyone else is using 'FUNCTION' instead of changing the way the record is logged.
602 SELECT fa.application_short_name, fa.application_name
603 , flu.meaning, fltc.component_appl_id, fltc.component_type
604 , fltc.component_id
605 INTO
606 l_app_sn, l_app_fn, l_comp_type_d, l_app_id, l_comp_type, l_comp_id
607 FROM fnd_log_transaction_context fltc,
608 fnd_log_messages flm,
609 fnd_application_vl fa,
610 fnd_lookups flu
611 WHERE
612 flm.log_sequence = document_id
613 and fltc.transaction_context_id = flm.transaction_context_id
614 and fltc.component_appl_id = fa.application_id (+)
615 and decode(fltc.component_type,'FUNCTION','ICX_APP_MODULE',fltc.component_type) = flu.lookup_code (+)
616 and flu.lookup_type = 'FND_COMPONENT_TYPE';
617
618 document_type := 'text/plain';
619 retriveComponentInfo(l_app_id, l_comp_type, l_comp_id, l_comp_sn, l_comp_fn);
620
621 --Documnet other Part
622 FND_MESSAGE.clear;
623 FND_MESSAGE.SET_NAME(application=>'FND', name=>'AF_OAM_BE_CTX');
624 document := FND_MESSAGE.GET;
625 ---fdebug('document' || document);
626
627 FND_MESSAGE.clear;
628 FND_MESSAGE.SET_NAME(application=>'FND', name=>'AF_OAM_BE_ALERT_ID');
629 FND_MESSAGE.SET_TOKEN(token=>'ID', value=>document_id);
630 document := document || WF_CORE.NEWLINE || FND_MESSAGE.GET;
631 ---fdebug('document' || document);
632
633 FND_MESSAGE.clear;
634 FND_MESSAGE.SET_NAME(application=>'FND', name=>'AF_OAM_BE_APP');
635 FND_MESSAGE.SET_TOKEN(token=>'APP_FULL_NAME', value=>l_app_fn);
636 FND_MESSAGE.SET_TOKEN(token=>'APP_SN', value=>l_app_sn);
637 document := document || WF_CORE.NEWLINE || FND_MESSAGE.GET;
638 ---fdebug('document' || document);
639
640 FND_MESSAGE.clear;
641 FND_MESSAGE.SET_NAME(application=>'FND', name=>'AF_OAM_BE_COMP_TYPE');
642 FND_MESSAGE.SET_TOKEN(token=>'COMP_TYPE', value=>l_comp_type);
643 document := document || WF_CORE.NEWLINE || FND_MESSAGE.GET;
644 --fdebug('document' || document);
645
646 --10254432
647 if (l_comp_fn<>'UNKNOWN') then
648 FND_MESSAGE.clear;
649 FND_MESSAGE.SET_NAME(application=>'FND', name=>'AF_OAM_BE_COMP');
650 FND_MESSAGE.SET_TOKEN(token=>'COMP_FULL_NAME', value=>l_comp_fn);
651 FND_MESSAGE.SET_TOKEN(token=>'COMP_SN', value=>l_comp_sn);
652 document := document || WF_CORE.NEWLINE || FND_MESSAGE.GET;
653 --fdebug('document' || document);
654 end if;
655
656 fdebug('Out:FND_OAM_KBF_SUBS.createBusExcepDocPart1');
657 END createBusExcepDocPart1;
658
659
660
661
662
663
664 --------------------------------------------------------------------------------
665 -------------DEBUG METHODS
666 --------------------------------------------------------------------------------
667 procedure fdebug(msg in varchar2)
668 IS
669 l_msg VARCHAR2(1);
670 BEGIN
671 ---dbms_output.put_line(msg);
672 l_msg := null;
673 END fdebug;
674
675
676 FUNCTION raise_oamEvent
677 (v_comm IN VARCHAR2)
678 RETURN VARCHAR2
679 IS
680 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
681 BEGIN
682 fdebug('In:FND_OAM_KBF_SUBS.raise_oamEvent');
683
684
685 wf_event.AddParameterToList(p_name=>'ORG_ID', p_value=>'Rm Org Id', p_parameterlist=>l_parameter_list);
686
687 wf_event.AddParameterToList(p_name=>'PM1',
688 p_value=>'PM1Val',
689 p_parameterlist=>l_parameter_list);
690
691 fdebug('Before Raise');
692 wf_event.raise( p_event_name => 'oracle.apps.fnd.system.exception',
693 p_event_key => v_comm,
694 p_parameters => l_parameter_list);
695 l_parameter_list.DELETE;
696 commit;
697 fdebug('Out:FND_OAM_KBF_SUBS.raise_oamEvent');
698
699 return v_comm || ' success ';
700 -- END;
701 exception
702 when others then
703 fdebug('Error:Unable to raise event');
704 fdebug('Error Num: ' || SQLCODE);
705 fdebug('Error Msg: ' || SQLERRM);
706 RAISE_APPLICATION_ERROR(-20202,'Unable to raise event');
707 END raise_oamEvent;
708
709 --------------------------------------------------------------------------------
710
711
712
713
714
715
716
717 --End Functions
718 END FND_OAM_KBF_SUBS;