[Home] [Help]
PACKAGE BODY: APPS.FND_OAM_KBF_SUBS
Source
1 PACKAGE BODY FND_OAM_KBF_SUBS AS
2 /* $Header: AFOAMSBB.pls 120.1.12010000.2 2008/08/12 18:45:30 tshort 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 SELECT
227 b.function_name, t.user_function_name
228 INTO
229 l_comp_name, l_comp_display_name
230 --, b.APPLICATION_ID, b.function_id, t.DESCRIPTION
231 FROM
232 FND_FORM_FUNCTIONS b, FND_FORM_FUNCTIONS_TL t
233 WHERE
234 b.function_id = comp_id
235 and b.function_id = t.function_id
236 and t.language = userenv('LANG');
237
238 END IF;
239
240
241 comp_name :=l_comp_name;
242 comp_display_name :=l_comp_display_name;
243
244 fdebug('Component Name=' || l_comp_name || ' Component Display Name=' || l_comp_display_name);
245
246 fdebug('OUT:FND_OAM_KBF_SUBS.retriveComponentInfo');
247 END retriveComponentInfo;
248
249 --------------------------------------------------------------------------------
250 procedure setWFAttributes(itemtype in varchar2,
251 itemkey in varchar2)
252 IS
253 l_app_sn FND_APPLICATION_VL.APPLICATION_SHORT_NAME%TYPE;
254 l_comp_sn FND_APP_COMPONENTS_VL.COMPONENT_NAME%TYPE;
255 l_comp_fn FND_APP_COMPONENTS_VL.DISPLAY_NAME%TYPE;
256 l_severity FND_LOG_EXCEPTIONS.SEVERITY%TYPE;
257 l_system VARCHAR2(200);
258 l_app_id FND_LOG_TRANSACTION_CONTEXT.COMPONENT_APPL_ID%TYPE;
259 l_comp_id FND_LOG_TRANSACTION_CONTEXT.COMPONENT_ID%TYPE;
260 l_comp_type FND_LOG_TRANSACTION_CONTEXT.COMPONENT_TYPE%TYPE;
261 BEGIN
262 fdebug('In:FND_OAM_KBF_SUBS.setWFAttributes');
263
264 SELECT fa.application_short_name
265 , flue.severity, fltc.component_appl_id, fltc.component_type
266 , fltc.component_id
267 INTO
268 l_app_sn, l_severity, l_app_id, l_comp_type, l_comp_id
269 FROM fnd_log_transaction_context fltc,
270 fnd_log_messages flm,
271 fnd_log_exceptions fle,
272 FND_LOG_UNIQUE_EXCEPTIONS flue,
273 fnd_application fa
274 WHERE
275 flm.log_sequence = to_number(itemkey)
276 and flm.log_sequence = fle.log_sequence
277 and fle.UNIQUE_EXCEPTION_ID = flue.UNIQUE_EXCEPTION_ID
278 and fltc.transaction_context_id = flm.transaction_context_id
279 and fltc.component_appl_id = fa.application_id (+);
280
281 retriveComponentInfo(l_app_id, l_comp_type, l_comp_id, l_comp_sn, l_comp_fn);
282 select VALUE into l_system from V$PARAMETER where NAME='db_name';
283
284 fdebug('l_app_id=' || l_app_id);
285 fdebug('l_app_sn=' || l_app_sn);
286 fdebug('l_comp_type=' || l_comp_type);
287 fdebug('l_comp_id=' || l_comp_id);
288 fdebug('l_comp_sn=' || l_comp_sn);
289 fdebug('l_comp_fn=' || l_comp_fn);
290 fdebug('l_severity=' || l_severity);
291
292
293 WF_ENGINE.SetItemAttrtext(itemtype, itemkey, 'SYSTEM', l_system);
294 WF_ENGINE.SetItemAttrtext(itemtype, itemkey, 'APP_SHORT_NAME', l_app_sn);
295 WF_ENGINE.SetItemAttrtext(itemtype, itemkey, 'COMP_SHORT_NAME', l_comp_sn);
296 WF_ENGINE.SetItemAttrtext(itemtype, itemkey, 'SEVERITY', l_severity);
297
298 fdebug('Out:FND_OAM_KBF_SUBS.setWFAttributes');
299 END setWFAttributes;
300 --------------------------------------------------------------------------------
301 PROCEDURE createSubList(itemtype in varchar2,
302 itemkey in varchar2,
303 actid in number,
304 funcmode in varchar2,
305 resultout out NOCOPY varchar2)
306 IS
307 l_unique_ex_id FND_LOG_EXCEPTIONS.UNIQUE_EXCEPTION_ID%TYPE;
308 l_msg_id FND_LOG_EXCEPTIONS.LOG_SEQUENCE%TYPE;
309 l_app_id FND_LOG_TRANSACTION_CONTEXT.COMPONENT_APPL_ID%TYPE;
310 l_comp_type FND_LOG_TRANSACTION_CONTEXT.COMPONENT_TYPE%TYPE;
311 l_comp_type_s FND_LOG_TRANSACTION_CONTEXT.COMPONENT_TYPE%TYPE;
312 l_comp_id FND_LOG_TRANSACTION_CONTEXT.COMPONENT_ID%TYPE;
313 -- l_biz_flow_id FND_LOG_EXCEPTION_CONTEXT.LOG_SEQUENCE%TYPE;
314 l_category FND_LOG_EXCEPTIONS.CATEGORY%TYPE;
315 l_severity FND_LOG_EXCEPTIONS.SEVERITY%TYPE;
316
317 l_sub_list VARCHAR2(32000);
318 l_role_name VARCHAR2(2000);
319 l_display_name VARCHAR2(100);
320
321 err_num NUMBER;
322 err_msg VARCHAR2(100);
323 l_BE_SUBJECT VARCHAR2(100);
324 l_BE_MESSAGE1 VARCHAR2(100);
325 l_BE_MESSAGE2 VARCHAR2(100);
326 l_role_users wf_directory.userTable;
327 l_ii NUMBER;
328
329
330 CURSOR subs_cur is
331 SELECT subscription_id, role_id, component_type, severity, category, component_id
332 FROM FND_OAM_BIZEX_SUBSCRIP
333 WHERE
334 (application_id = l_app_id)
335 OR(application_id IS NULL)
336 ;
337
338 BEGIN
339 fdebug('In:FND_OAM_KBF_SUBS.CreateSubList');
340 fdebug('itemkey:' || itemkey);
341
342 SELECT fltc.COMPONENT_APPL_ID, fltc.COMPONENT_TYPE
343 ,flue.CATEGORY, flue.SEVERITY
344 ,fltc.component_id, fle.UNIQUE_EXCEPTION_ID
345 INTO
346 l_app_id, l_comp_type
347 ,l_category, l_severity
348 ,l_comp_id, l_unique_ex_id
349 FROM FND_LOG_MESSAGES flm
350 ,FND_LOG_TRANSACTION_CONTEXT fltc
351 ,FND_LOG_EXCEPTIONS fle
352 ,FND_LOG_UNIQUE_EXCEPTIONS flue
353 WHERE
354 flm.LOG_SEQUENCE = TO_NUMBER(itemkey)
355 AND fle.LOG_SEQUENCE = flm.LOG_SEQUENCE
356 AND fltc.TRANSACTION_CONTEXT_ID = flm.TRANSACTION_CONTEXT_ID
357 AND fle.UNIQUE_EXCEPTION_ID = flue.UNIQUE_EXCEPTION_ID;
358
359 l_ii := 0;
360 FOR subs_record in subs_cur LOOP
361 l_comp_type_s := subs_record.component_type;
362
363 --Check if Notification is already send.
364 IF (HAS_NOTIFIED(subs_record.subscription_id, l_unique_ex_id) = TRUE) THEN
365 GOTO next_record;
366 END IF;
367
368 IF (
369 (l_comp_type_s = COMP_TYPE_UNKNOWN)
370 AND (
371 (l_app_id IS NULL)
372 OR(l_app_id = -1)
373 OR(l_comp_type IS NULL)
374 OR(l_comp_id IS NULL)
375 OR(l_comp_id = -1)
376 )
377 ) THEN
378 --UnKnown Type
379 IF (SHALL_ADD_SUBS(subs_record.CATEGORY, l_category) = FALSE) THEN
380 GOTO next_record;
381 END IF;
382 IF (SHALL_ADD_SUBS_SEVERITY(subs_record.severity, l_severity) = FALSE) THEN
383 GOTO next_record;
384 END IF; --For Unknown
385 ELSE----------------------------------Known Types
386 IF (SHALL_ADD_SUBS(subs_record.component_type, l_comp_type) = FALSE) THEN
387 GOTO next_record;
388 END IF;
389 IF (SHALL_ADD_SUBS(subs_record.CATEGORY, l_category) = FALSE) THEN
390 GOTO next_record;
391 END IF;
395 IF (SHALL_ADD_SUBS(subs_record.component_id, l_comp_id) = FALSE) THEN
392 IF (SHALL_ADD_SUBS_SEVERITY(subs_record.severity, l_severity) = FALSE) THEN
393 GOTO next_record;
394 END IF;
396 GOTO next_record;
397 END IF;
398
399 END IF; --Else (known Types)
400
401 --Check If already Added to List
402 IF (instr(l_sub_list, subs_record.role_id) > 0) THEN
403 fdebug('Skip role already added=' || subs_record.role_id);
404 GOTO next_record;
405 END IF;
406
407
408 --Add to List
409 l_ii := l_ii + 1;
410 l_role_users(l_ii) := subs_record.role_id;
411 IF l_sub_list IS NULL THEN
412 l_sub_list := subs_record.role_id;
413 ELSE
414 l_sub_list := l_sub_list || ','|| subs_record.role_id;
415 END IF;
416 insert into FND_OAM_BIZEX_SENT_NOTIF(UNIQUE_EXCEPTION_ID, SUBSCRIPTION_ID, SENT) values
417 (l_unique_ex_id, subs_record.SUBSCRIPTION_ID, sysdate);
418
419 ---fdebug('l_sub_list=' || l_sub_list);
420
421 <<next_record>> --Go to next record
422 fdebug('Skip role(no match)=' || subs_record.role_id);
423 NULL;
424 END LOOP; --subs_cur
425
426 fdebug('l_sub_list=' || l_sub_list);
427
428
429 --- This is for test
430 --- l_sub_list := 'RMOHAN2';
431
432 IF l_sub_list IS NOT NULL THEN
433 fdebug('Valid List: Calling CreateAdHocRole');
434 WF_DIRECTORY.CreateAdHocRole2(role_name=>l_role_name
435 , role_display_name=>l_display_name, ROLE_USERS=>l_role_users);
436 WF_ENGINE.SetItemAttrtext(itemtype, itemkey, 'ADHC_ROLE_NAME'
437 , l_role_name);
438
439 --Sets other attributes as app short name severity etc.
440 setWFAttributes(itemtype, itemkey);
441
442 l_BE_SUBJECT := 'plsql:FND_OAM_KBF_SUBS.createSubject/' || itemkey;
443 fdebug('l_BE_SUBJECT=' || l_BE_SUBJECT);
444 WF_ENGINE.SetItemAttrtext(itemtype, itemkey, 'BE_MAIL_SUBJECT'
445 , l_BE_SUBJECT);
446
447 l_BE_MESSAGE1 := 'plsql:FND_OAM_KBF_SUBS.createBusExcepDoc/' || itemkey;
448 fdebug('l_BE_MESSAGE1=' || l_BE_MESSAGE1);
449 WF_ENGINE.SetItemAttrtext(itemtype, itemkey, 'BE_MESSAGE1'
450 , l_BE_MESSAGE1);
451
452 l_BE_MESSAGE2 := 'plsql:FND_OAM_KBF_SUBS.createBusExcepDocPart1/' || itemkey;
453 fdebug('l_BE_MESSAGE2=' || l_BE_MESSAGE2);
454 WF_ENGINE.SetItemAttrtext(itemtype, itemkey, 'EMAIL_BODY_PART1'
455 , l_BE_MESSAGE2);
456
457 ELSE
458 l_sub_list := 'NULL';
459 ----This is for test due to wf bug
460 --- WF_ENGINE.SetItemAttrtext(itemtype, itemkey, 'ADHC_ROLE_NAME'
461 --- , l_sub_list);
462 END IF;
463
464
465
466 resultout := l_sub_list;
467
468 fdebug('Out:FND_OAM_KBF_SUBS.CreateSubList');
469 --- EXCEPTION
470 --- WHEN OTHERS THEN
471 --- err_num := SQLCODE;
472 --- err_msg := SUBSTR(SQLERRM, 1, 1000);
473 --- fdebug('Error:FND_OAM_KBF_SUBS.CreateSubList.');
474 --- fdebug('Error Num: ' || err_num);
475 --- fdebug('Error Msg: ' || err_msg);
476
477 --- raise;
478
479 END createSubList;
480 ---------------------------------------------------------------------------------
481
482
483 procedure createSubject(document_id in varchar2,
484 display_type in varchar2,
485 document in out NOCOPY varchar2,
486 document_type in out NOCOPY varchar2)
487 IS
488 l_app_sn FND_APPLICATION_VL.APPLICATION_SHORT_NAME%TYPE;
489 l_comp_sn FND_APP_COMPONENTS_VL.COMPONENT_NAME%TYPE;
490 l_severity fnd_lookups.MEANING%TYPE; --FND_LOG_EXCEPTIONS.SEVERITY%TYPE;
491 l_system VARCHAR2(200);
492
493 l_app_id FND_LOG_TRANSACTION_CONTEXT.COMPONENT_APPL_ID%TYPE;
494 l_comp_id FND_LOG_TRANSACTION_CONTEXT.COMPONENT_ID%TYPE;
495 l_comp_type FND_LOG_TRANSACTION_CONTEXT.COMPONENT_TYPE%TYPE;
496 l_comp_fn FND_APP_COMPONENTS_VL.DISPLAY_NAME%TYPE;
497 BEGIN
498 fdebug('In:FND_OAM_KBF_SUBS.createBusExcepDocSubject');
499
500 SELECT
501 fa.application_short_name, fl.meaning,
502 fltc.component_appl_id, fltc.component_type, fltc.component_id
503 INTO
504 l_app_sn, l_severity, l_app_id, l_comp_type, l_comp_id
505 FROM fnd_log_transaction_context fltc,
506 fnd_log_messages flm,
507 fnd_log_exceptions fle,
508 FND_LOG_UNIQUE_EXCEPTIONS flue,
509 fnd_application_vl fa,
510 fnd_lookups fl
511 WHERE
512 flm.log_sequence = document_id
513 and flm.log_sequence = fle.log_sequence
514 and fle.UNIQUE_EXCEPTION_ID = flue.UNIQUE_EXCEPTION_ID
515 and fltc.transaction_context_id = flm.transaction_context_id
516 and fltc.component_appl_id = fa.application_id (+)
517 and flue.severity = fl.lookup_code (+)
518 and fl.lookup_type = 'FND_KBF_SEVERITY'
519 and flm.log_sequence = document_id;
520
521 retriveComponentInfo(l_app_id, l_comp_type, l_comp_id, l_comp_sn, l_comp_fn);
522 select VALUE into l_system from V$PARAMETER where NAME='db_name';
523
524 FND_MESSAGE.CLEAR;
528 FND_MESSAGE.SET_TOKEN(token=>'APP', value=>l_app_sn);
525 FND_MESSAGE.SET_NAME(application=>'FND', name=>'AF_OAM_BE_SUB');
526 FND_MESSAGE.SET_TOKEN(token=>'SYSTEM', value=>l_system);
527 FND_MESSAGE.SET_TOKEN(token=>'SEVERITY', value=>l_severity);
529 FND_MESSAGE.SET_TOKEN(token=>'COMP', value=>l_comp_sn);
530 document := FND_MESSAGE.GET;
531 FND_MESSAGE.CLEAR;
532
533 fdebug('Subject:'|| document);
534 fdebug('Out:FND_OAM_KBF_SUBS.createBusExcepDocSubject');
535 /*
536 EXCEPTION
537 WHEN OTHERS THEN
538 fdebug('Error:FND_OAM_KBF_SUBS.createBusExcepDocSubject.');
539 raise;
540 */
541 END createSubject;
542
543
544
545
546 procedure createBusExcepDoc(document_id in varchar2,
547 display_type in varchar2,
548 document in out NOCOPY varchar2,
549 document_type in out NOCOPY varchar2)
550 IS
551 l_msg_id FND_LOG_EXCEPTIONS.LOG_SEQUENCE%TYPE;
552 BEGIN
553 fdebug('In:FND_OAM_KBF_SUBS.createBusExcepDoc');
554
555 l_msg_id := TO_NUMBER(document_id);
556 document := FND_LOG.GET_TEXT(l_msg_id);
557 --document := 'Rm test:document_id '|| document_id ;
558 document_type := 'text/plain';
559 fdebug('document' || document);
560
561 fdebug('Out:FND_OAM_KBF_SUBS.createBusExcepDoc');
562 END createBusExcepDoc;
563
564
565
566
567 procedure createBusExcepDocPart1(document_id in varchar2,
568 display_type in varchar2,
569 document in out NOCOPY varchar2,
570 document_type in out NOCOPY varchar2)
571 IS
572 l_msg_id FND_LOG_EXCEPTIONS.LOG_SEQUENCE%TYPE;
573 l_subject VARCHAR2(200);
574
575 l_app_sn FND_APPLICATION_VL.APPLICATION_SHORT_NAME%TYPE;
576 l_app_fn FND_APPLICATION_VL.APPLICATION_NAME%TYPE;
577
578 l_comp_sn FND_APP_COMPONENTS_VL.COMPONENT_NAME%TYPE;
579
580 -- l_biz_flow_id FND_LOG_EXCEPTION_CONTEXT.LOG_SEQUENCE%TYPE;
581 l_comp_type_d fnd_lookups.MEANING%TYPE;
582
583 l_app_id FND_LOG_TRANSACTION_CONTEXT.COMPONENT_APPL_ID%TYPE;
584 l_comp_id FND_LOG_TRANSACTION_CONTEXT.COMPONENT_ID%TYPE;
585 l_comp_type FND_LOG_TRANSACTION_CONTEXT.COMPONENT_TYPE%TYPE;
586 l_comp_fn FND_APP_COMPONENTS_VL.DISPLAY_NAME%TYPE;
587
588 BEGIN
589 fdebug('In:FND_OAM_KBF_SUBS.createBusExcepDocPart1');
590 ---Set up the Subject
591
592 SELECT fa.application_short_name, fa.application_name
593 , flu.meaning, fltc.component_appl_id, fltc.component_type
594 , fltc.component_id
595 INTO
596 l_app_sn, l_app_fn, l_comp_type_d, l_app_id, l_comp_type, l_comp_id
597 FROM fnd_log_transaction_context fltc,
598 fnd_log_messages flm,
599 fnd_application_vl fa,
600 fnd_lookups flu
601 WHERE
602 flm.log_sequence = document_id
603 and fltc.transaction_context_id = flm.transaction_context_id
604 and fltc.component_appl_id = fa.application_id (+)
605 and fltc.component_type = flu.lookup_code (+)
606 and flu.lookup_type = 'FND_COMPONENT_TYPE';
607
608 document_type := 'text/plain';
609 retriveComponentInfo(l_app_id, l_comp_type, l_comp_id, l_comp_sn, l_comp_fn);
610
611 --Documnet other Part
612 FND_MESSAGE.clear;
613 FND_MESSAGE.SET_NAME(application=>'FND', name=>'AF_OAM_BE_CTX');
614 document := FND_MESSAGE.GET;
615 ---fdebug('document' || document);
616
617 FND_MESSAGE.clear;
618 FND_MESSAGE.SET_NAME(application=>'FND', name=>'AF_OAM_BE_ALERT_ID');
619 FND_MESSAGE.SET_TOKEN(token=>'ID', value=>document_id);
620 document := document || WF_CORE.NEWLINE || FND_MESSAGE.GET;
621 ---fdebug('document' || document);
622
623 FND_MESSAGE.clear;
624 FND_MESSAGE.SET_NAME(application=>'FND', name=>'AF_OAM_BE_APP');
625 FND_MESSAGE.SET_TOKEN(token=>'APP_FULL_NAME', value=>l_app_fn);
626 FND_MESSAGE.SET_TOKEN(token=>'APP_SN', value=>l_app_sn);
627 document := document || WF_CORE.NEWLINE || FND_MESSAGE.GET;
628 ---fdebug('document' || document);
629
630 FND_MESSAGE.clear;
631 FND_MESSAGE.SET_NAME(application=>'FND', name=>'AF_OAM_BE_COMP_TYPE');
632 FND_MESSAGE.SET_TOKEN(token=>'COMP_TYPE', value=>l_comp_type);
633 document := document || WF_CORE.NEWLINE || FND_MESSAGE.GET;
634 --fdebug('document' || document);
635
636 FND_MESSAGE.clear;
637 FND_MESSAGE.SET_NAME(application=>'FND', name=>'AF_OAM_BE_COMP');
638 FND_MESSAGE.SET_TOKEN(token=>'COMP_FULL_NAME', value=>l_comp_fn);
639 FND_MESSAGE.SET_TOKEN(token=>'COMP_SN', value=>l_comp_sn);
640 document := document || WF_CORE.NEWLINE || FND_MESSAGE.GET;
641 --fdebug('document' || document);
642
643 fdebug('Out:FND_OAM_KBF_SUBS.createBusExcepDocPart1');
644 END createBusExcepDocPart1;
645
646
647
648
649
650
651 --------------------------------------------------------------------------------
652 -------------DEBUG METHODS
653 --------------------------------------------------------------------------------
654 procedure fdebug(msg in varchar2)
655 IS
656 l_msg VARCHAR2(1);
657 BEGIN
658 ---dbms_output.put_line(msg);
659 l_msg := null;
660 END fdebug;
661
662
663 FUNCTION raise_oamEvent
664 (v_comm IN VARCHAR2)
665 RETURN VARCHAR2
666 IS
667 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
668 BEGIN
669 fdebug('In:FND_OAM_KBF_SUBS.raise_oamEvent');
670
671
672 wf_event.AddParameterToList(p_name=>'ORG_ID', p_value=>'Rm Org Id', p_parameterlist=>l_parameter_list);
673
674 wf_event.AddParameterToList(p_name=>'PM1',
675 p_value=>'PM1Val',
676 p_parameterlist=>l_parameter_list);
677
678 fdebug('Before Raise');
679 wf_event.raise( p_event_name => 'oracle.apps.fnd.system.exception',
680 p_event_key => v_comm,
681 p_parameters => l_parameter_list);
682 l_parameter_list.DELETE;
683 commit;
684 fdebug('Out:FND_OAM_KBF_SUBS.raise_oamEvent');
685
686 return v_comm || ' success ';
687 -- END;
688 exception
689 when others then
690 fdebug('Error:Unable to raise event');
691 fdebug('Error Num: ' || SQLCODE);
692 fdebug('Error Msg: ' || SQLERRM);
693 RAISE_APPLICATION_ERROR(-20202,'Unable to raise event');
694 END raise_oamEvent;
695
696 --------------------------------------------------------------------------------
697
698
699
700
701
702
703
704 --End Functions
705 END FND_OAM_KBF_SUBS;