[Home] [Help]
PACKAGE BODY: APPS.ALR_MLR_PACKAGE
Source
1 package body ALR_MLR_PACKAGE as
2 /* $Header: alrwfmlrb.pls 120.0 2006/08/02 22:10:11 jwsmith noship $ */
3
4 --
5 -- Generic mailer routines
6 --
7 -- Send
8 -- Calls the wf_mail.send routine to send emails to the recipients in the
9 -- receipient list.
10
11 procedure Send(a_idstring in varchar2,
12 a_module in varchar2,
13 a_replyto in varchar2 default null,
14 a_subject in varchar2,
15 a_message in varchar2)
16 is
17 clobvar CLOB := EMPTY_CLOB;
18 len BINARY_INTEGER;
19 begin
20 dbms_lob.createtemporary(clobvar, TRUE);
21 dbms_lob.open(clobvar, dbms_lob.lob_readwrite);
22 len := length(a_message);
23 dbms_lob.writeappend(clobvar, len, a_message);
24 dbms_lob.close(clobvar);
25
26 WF_MAIL.SEND(p_idstring => a_idstring,
27 p_module => a_module,
28 p_recipient_list => ALR_EMAIL_TABLE,
29 p_replyto => a_replyto,
30 p_subject => a_subject,
31 p_message => clobvar);
32
33 exception
34 when others then
35 APP_EXCEPTION.RAISE_EXCEPTION;
36
37 dbms_lob.freetemporary(clobvar);
38
39 end;
40
41 procedure Send2(a_idstring in varchar2,
42 a_module in varchar2,
43 a_replyto in varchar2 default null,
44 a_subject in varchar2,
45 a_chunk1 in varchar2,
46 a_chunk2 in varchar2)
47 is
48 a_message CLOB;
49 OFFSET number;
50 LEN number;
51 begin
52 DBMS_LOB.CREATETEMPORARY(a_message, FALSE);
53 DBMS_LOB.OPEN(a_message, DBMS_LOB.LOB_READWRITE);
54 OFFSET := 1;
55 LEN := LENGTH(a_chunk1);
56 DBMS_LOB.WRITE(a_message, LEN, OFFSET, a_chunk1);
57 OFFSET := OFFSET + LEN;
58 if (a_chunk2 is not null) then
59 LEN := LENGTH(a_chunk2);
60 DBMS_LOB.WRITE(a_message, LEN, OFFSET, a_chunk2);
61 OFFSET := OFFSET + LEN;
62 end if;
63 WF_MAIL.SEND(p_idstring => a_idstring,
64 p_module => a_module,
65 p_recipient_list => ALR_EMAIL_TABLE,
66 p_replyto => a_replyto,
67 p_subject => a_subject,
68 p_message => a_message);
69
70 DBMS_LOB.FREETEMPORARY(a_message);
71
72 exception
73 when others then
74 APP_EXCEPTION.RAISE_EXCEPTION;
75
76 end;
77
78 procedure InitRecipientList
79 is
80 begin
81 ALR_EMAIL_TABLE.delete;
82 end;
83
84 procedure AddRecipientToList(p_name in varchar2,
85 p_value in varchar2,
86 p_recipient_type in varchar2)
87 is
88 j number;
89 begin
90
91 if (ALR_EMAIL_TABLE.COUNT = 0) then
92 ALR_EMAIL_TABLE(1).name := p_name;
93 ALR_EMAIL_TABLE(1).address := p_value;
94 ALR_EMAIL_TABLE(1).recipient_type := p_recipient_type;
95 else
96 --
97 -- parameter list exists, add parameter to list
98 --
99 j := ALR_EMAIL_TABLE.COUNT+1;
100
101 ALR_EMAIL_TABLE(j).name := p_name;
102 ALR_EMAIL_TABLE(j).address := p_value;
103 ALR_EMAIL_TABLE(j).recipient_type := p_recipient_type;
104
105 end if;
106
107 end;
108
109 function Response(p_subscription_guid in raw,
110 p_event in out NOCOPY WF_EVENT_T) return varchar2
111 is
112
113 l_responses wf_xml.wf_responseList_t;
114 --TYPE wf_response_rec_t IS RECORD
115 -- NAME varchar2(30),
116 -- TYPE varchar2(8),
117 -- FORMAT varchar2(240),
118 -- VALUE varchar2(32000)
119
120 l_node varchar2(30);
121 l_version integer;
122 l_from varchar2(2000);
123 l_eventName varchar2(80);
124 l_eventkey varchar2(80);
125 l_paramlist wf_parameter_list_t;
126 l_eventData CLOB;
127 l_messageHandle varchar2(100);
128 tk pls_integer;
129 reqid number;
130
131 -- ALP_NO_RESPONSE = "N"
132 -- ALP_VALID_RESPONSE = "V"
133 -- ALP_INVALID_RESPONSE = "I"
134 resp_type varchar(1);
135 l_alert_id varchar2(100);
136 l_app_id varchar2(100);
137
138 -- For event key parsing
139 l_node_handle varchar2(100);
140 l_message_handle varchar2(100);
141 l_morcl_id varchar2(100);
142 ptpos1 pls_integer;
143 ptpos2 pls_integer;
144 ptpos3 pls_integer;
145
146 --GetMessageDetails
147 alr_msg_dtls_tbl alr_msg_dtls_tbl_type;
148 count_var number;
149 msg_handle number;
150 node_handle number;
151 morcl_id number;
152
153 --InitResponseVar
154 alr_init_resp_tbl alr_init_resp_tbl_type;
155
156 -- InitValidResponses
157 alr_init_valid_resp_tbl alr_init_valid_resp_tbl_type;
158
159 -- GetRespActions
160 test_resp_set_id number;
161 alr_get_resp_act_tbl alr_get_resp_act_tbl_type;
162 alr_match_resp_act_tbl alr_match_resp_act_tbl_type;
163
164 -- GetOutputValues
165 alr_resp_var_values_tbl alr_resp_var_values_tbl_type;
166
167 -- SaveRespHistory
168 received varchar2(240);
169
170 -- SaveRespActHistory
171 response_msg_id number;
172 oracle_id number;
173 seq number;
174 version_num number;
175 success_flag varchar2(1);
176
177 -- CloseResp
178 action_set_pass_fail varchar2(1);
179 open_closed varchar2(1);
180
181 found number;
182
183 step varchar2(200);
184 begin
185 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
186 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
187 'alr.plsql.ALR_MLR_PACKAGE.Response', 'BEGIN');
188 end if;
189 l_eventkey := p_event.GetEventKey();
190 l_eventName := p_event.getEventName();
191 l_paramList := p_event.getParameterList();
192 l_eventData := p_event.getEventData();
193
194 -- parse the event key into the message handle
195 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
196 wf_log_pkg.string(WF_LOG_PKG.level_statement,
197 'alr.plsql.ALR_MLR_PACKAGE.Response',
198 'Parsing for the node and message handle');
199 end if;
200
201 ptpos1 := instrb(l_eventKey, '.', 1)+1;
202 ptpos2 := instrb(l_eventKey, '.', -1);
203
204 ptpos3 := length(l_eventKey);
205 l_node_handle := substrb(l_eventKey, 1, ptpos1 -2);
206 l_message_handle := substrb(l_eventKey, ptpos1, ptpos2 - ptpos1);
207 l_morcl_id := substrb(l_eventKey, ptpos2+1, ptpos3 - ptpos2);
208
209 -- convert to number
210 msg_handle := TO_NUMBER(l_message_handle);
211 node_handle := TO_NUMBER(l_node_handle);
212 morcl_id := TO_NUMBER(l_morcl_id);
213
214 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
215 wf_log_pkg.string(WF_LOG_PKG.level_statement,
216 'alr.plsql.ALR_MLR_PACKAGE.Response',
217 'Node ['||l_node_handle||'] Msg Handle ['||
218 l_message_handle||']');
219 end if;
220 if l_eventName = 'oracle.apps.alr.response.receive' then
221 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
222 wf_log_pkg.string(WF_LOG_PKG.level_statement,
223 'alr.plsql.ALR_MLR_PACKAGE.Response',
224 'Getting response details');
225 end if;
226 WF_XML.getResponseDetails(message => l_eventData,
227 node => l_node,
228 version => l_version,
229 fromRole => l_from,
230 responses => l_responses);
231 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
232 wf_log_pkg.string(WF_LOG_PKG.level_statement,
233 'alr.plsql.ALR_MLR_PACKAGE.Response',
234 'Got response details from ['||l_from||']');
235 end if;
236
237 step := 'Processing responses';
238 -- first check if invalid response
239 if (l_responses.COUNT = 0) then
240 if (wf_log_pkg.level_procedure >=
241 fnd_log.g_current_runtime_level) then
242 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
243 'alr.plsql.ALR_MLR_PACKAGE.Response',
244 'Invalid response (response count is zero).');
245 end if;
246
247 -- get application id, alert id, and response set
248 step := 'Getting messge details';
249 GetMessageDetails(msg_handle, node_handle,
250 alr_msg_dtls_tbl);
251
252 if (wf_log_pkg.level_procedure >=
253 fnd_log.g_current_runtime_level) then
254 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
255 'alr.plsql.ALR_MLR_PACKAGE.Response',
256 'After get mesage details.');
257 end if;
258
259 step := 'Saving History';
260 SaveRespHistory(msg_handle, node_handle,
261 alr_msg_dtls_tbl,
262 l_from,
263 'INVALID RESPONSE',
264 0);
265
266 if (wf_log_pkg.level_procedure >=
267 fnd_log.g_current_runtime_level) then
268 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
269 'alr.plsql.ALR_MLR_PACKAGE.Response',
270 'After saveresphist');
271 end if;
272
273 reqid := fnd_request.submit_request(application => 'ALR',
274 program => 'ALPPWF',
275 argument1 => l_node_handle,
276 argument2 => l_message_handle,
277 argument3 => l_morcl_id,
278 argument4 => 'I');
279 if (reqid = 0) then
280 if (wf_log_pkg.level_statement >=
281 fnd_log.g_current_runtime_level) then
282 wf_log_pkg.string(WF_LOG_PKG.level_statement,
283 'alr.plsql.ALR_MLR_PACKAGE.Response',
284 'The request ID retued 0. Raising -20160 app error');
285 end if;
286 raise_application_error(-20160, FND_MESSAGE.GET);
287 end if;
288
289 if (wf_log_pkg.level_statement >=
290 fnd_log.g_current_runtime_level) then
291 wf_log_pkg.string(WF_LOG_PKG.level_statement,
292 'alr.plsql.ALR_MLR_PACKAGE.Response',
293 'Concurrent request ['||to_char(reqid)||'] submitted');
294 end if;
295 else
296 -- process valid responses
297 for tk in 1..l_responses.COUNT loop
298 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
299 wf_log_pkg.string(WF_LOG_PKG.level_statement,
300 'alr.plsql.ALR_MLR_PACKAGE.Response',
301 'Processing ['||l_responses(tk).name||
302 '] ['||l_responses(tk).value||']');
303 end if;
304
305 -- Alert specific processing here
306 -- Check for open responses
307 count_var := OpenResponses;
308 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
309 wf_log_pkg.string(WF_LOG_PKG.level_statement,
310 'alr.plsql.ALR_MLR_PACKAGE.Response',
311 'Open responses ['||to_char(count_var)||']');
312 end if;
313
314 step := 'Processing open responses';
315 if (count_var > 0) then
316 -- get application id, alert id, and response set
317 step := 'Getting messge details';
318 GetMessageDetails(msg_handle, node_handle,
319 alr_msg_dtls_tbl);
320
321 If (alr_msg_dtls_tbl(1).open_closed = 'O') then
322 -- Initialize the response
323 step := 'Initializing the response';
324 InitResponseVar(alr_msg_dtls_tbl,
325 alr_init_resp_tbl);
326
327 -- Initialize valid responses
328 step := 'Initializing the valid response';
329 InitValidResponses(alr_msg_dtls_tbl,
330 alr_init_valid_resp_tbl);
331
332 -- Select response actions
333 step := 'Getting the response actions';
334 GetRespActions(alr_msg_dtls_tbl,
335 alr_init_valid_resp_tbl,
336 alr_get_resp_act_tbl);
337
338 found := 0;
339
340 -- Check user's response to see if valid response
341 -- First case: They entered a text value, no variables
342 for t_counter IN 1..alr_init_valid_resp_tbl.LAST LOOP
343 -- if value in email is plain text
344 if (l_responses(tk).value = alr_init_valid_resp_tbl(t_counter).resp_text) then
345 -- insert response received into history
346 found := 1;
347 step := 'Saving History';
348 received := SYSDATE;
349 SaveRespHistory(msg_handle, node_handle,
350 alr_msg_dtls_tbl,
351 l_from,
352 l_responses(tk).value,
353 alr_init_valid_resp_tbl(t_counter).resp_id);
354
355 -- If it matches here then they did not reply
356 -- with a response variable. So, update all
357 -- response variables with default values and
358 -- save to history.
359 step := 'Saving the value history with default values';
360 SaveRespVar(alr_msg_dtls_tbl,
361 msg_handle, node_handle,
362 alr_init_resp_tbl);
363
364 -- CALL CONCURRENT PROGRAM TO PERFORM ACTIONS
365 if (wf_log_pkg.level_procedure >=
366 fnd_log.g_current_runtime_level) then
367 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
368 'alr.plsql.ALR_MLR_PACKAGE.Response',
369 'Submitting Action Processor');
373 argument1 => l_node_handle,
370 end if;
371 reqid := fnd_request.submit_request(application => 'ALR',
372 program => 'ALPPWF',
374 argument2 => l_message_handle,
375 argument3 => l_morcl_id,
376 argument4 => 'V',
377 argument5 => l_responses(tk).name,
378 argument6 => l_responses(tk).value,
379 argument7 => l_responses(tk).format);
380 if reqid = 0 then
381 if (wf_log_pkg.level_statement >=
382 fnd_log.g_current_runtime_level) then
383 wf_log_pkg.string(WF_LOG_PKG.level_statement,
384 'alr.plsql.ALR_MLR_PACKAGE.Response',
385 'The request ID retued 0. Raising -20160 app error');
386 end if;
387 raise_application_error(-20160, FND_MESSAGE.GET);
388 end if;
389 end if;
390 END LOOP;
391
392 -- 2nd case is if they entered a variable but no value
393 -- ? is in value.
394 if (l_responses(tk).value = '?' and
395 found = 0) then
396 for t_counter in 1..alr_init_valid_resp_tbl.LAST LOOP
397 if (l_responses(tk).format =
398 alr_init_valid_resp_tbl(t_counter).resp_text) then
399 found := 1;
400 -- insert response received into history
401 step := 'Saving History';
402 SaveRespHistory(msg_handle, node_handle,
403 alr_msg_dtls_tbl,
404 l_from,
405 l_responses(tk).value,
406 alr_init_valid_resp_tbl(t_counter).resp_id);
407
408 -- If it matches here then they did not reply
409 -- with a response variable. So, update all
410 -- response variables with default values and
411 -- save to history.
412 step := 'Saving the value history with default values';
413 SaveRespVar(alr_msg_dtls_tbl,
414 msg_handle, node_handle,
415 alr_init_resp_tbl);
416
417 -- CALL CONCURRENT PROGRAM TO PERFORM ACTIONS
418 if (wf_log_pkg.level_procedure >=
419 fnd_log.g_current_runtime_level) then
420 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
421 'alr.plsql.ALR_MLR_PACKAGE.Response',
422 'Submitting Action Processor');
423 end if;
424 reqid := fnd_request.submit_request(application => 'ALR',
425 program => 'ALPPWF',
426 argument1 => l_node_handle,
427 argument2 => l_message_handle,
428 argument3 => l_morcl_id,
429 argument4 => 'V',
430 argument5 => l_responses(tk).name,
431 argument6 => l_responses(tk).value,
432 argument7 => l_responses(tk).format);
433 if (reqid = 0) then
434 if (wf_log_pkg.level_statement >=
435 fnd_log.g_current_runtime_level) then
436 wf_log_pkg.string(WF_LOG_PKG.level_statement,
437 'alr.plsql.ALR_MLR_PACKAGE.Response',
438 'The request ID retued 0. Raising -20160 app error');
439 end if;
440 raise_application_error(-20160, FND_MESSAGE.GET);
441 end if;
442 end if;
443 end loop;
444 end if;
445
446 -- Third case is if they entered a variable and a value
447 if (found = 0) then
448 for t_counter IN 1..alr_init_valid_resp_tbl.LAST LOOP
449 if (l_responses(tk).format =
450 alr_init_valid_resp_tbl(t_counter).resp_text) then
451 found := 1;
452
453 -- insert response received into history
454 step := 'Saving History';
455 SaveRespHistory(msg_handle, node_handle,
456 alr_msg_dtls_tbl,
457 l_from,
458 l_responses(tk).value,
459 alr_init_valid_resp_tbl(t_counter).resp_id);
460
461 SaveOneRespVar(alr_msg_dtls_tbl,
462 msg_handle, node_handle,
463 l_responses(tk).name,
464 l_responses(tk).value,
465 alr_init_resp_tbl);
466
467 -- CALL CONCURRENT PROGRAM TO PERFORM ACTIONS
468 if (wf_log_pkg.level_procedure >=
469 fnd_log.g_current_runtime_level) then
470 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
471 'alr.plsql.ALR_MLR_PACKAGE.Response',
472 'Submitting Action Processor');
473 end if;
474 reqid := fnd_request.submit_request(application => 'ALR',
475 program => 'ALPPWF',
479 argument4 => 'V',
476 argument1 => l_node_handle,
477 argument2 => l_message_handle,
478 argument3 => l_morcl_id,
480 argument5 => l_responses(tk).name,
481 argument6 => l_responses(tk).value,
482 argument7 => l_responses(tk).format);
483 if (reqid = 0) then
484 if (wf_log_pkg.level_statement >=
485 fnd_log.g_current_runtime_level) then
486 wf_log_pkg.string(WF_LOG_PKG.level_statement,
487 'alr.plsql.ALR_MLR_PACKAGE.Response',
488 'The request ID retued 0. Raising -20160 app error');
489 end if;
490 raise_application_error(-20160, FND_MESSAGE.GET);
491 end if;
492 end if;
493 end loop;
494 end if;
495
496 -- last case is invalid response
497 if (found=0) then
498 if (wf_log_pkg.level_procedure >=
499 fnd_log.g_current_runtime_level) then
500 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
501 'alr.plsql.ALR_MLR_PACKAGE.Response',
502 'Submitting Action Processor');
503 end if;
504 reqid := fnd_request.submit_request(application => 'ALR',
505 program => 'ALPPWF',
506 argument1 => l_node_handle,
507 argument2 => l_message_handle,
508 argument3 => l_morcl_id,
509 argument4 => 'V',
510 argument5 => l_responses(tk).name,
511 argument6 => l_responses(tk).value,
512 argument7 => l_responses(tk).format);
513 if (reqid = 0) then
514 if (wf_log_pkg.level_statement >=
515 fnd_log.g_current_runtime_level) then
516 wf_log_pkg.string(WF_LOG_PKG.level_statement,
517 'alr.plsql.ALR_MLR_PACKAGE.Response',
518 'The request ID retued 0. Raising -20160 app error');
519 end if;
520 raise_application_error(-20160, FND_MESSAGE.GET);
521 end if;
522
523 end if;
524
525 -- Close all stmts.
526 end if;
527 end if;
528 end loop;
529 end if;
530 end if;
531
532 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
533 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
534 'alr.plsql.ALR_MLR_PACKAGE.Response', 'END');
535 end if;
536
537 return 'SUCCESS';
538
539 exception
540 when others then
541 wf_core.context('ALR_MLR_PKG', 'RESPONSE', l_eventName, l_eventKey,
542 'Problem encountered when performing ['||step||']');
543 wf_event.SetErrorInfo(p_event, 'ERROR');
544 return 'ERROR';
545 end Response;
546
547 function OpenResponses return number is
548 count_var number;
549 begin
550 select count(*)
551 into count_var
552 from alr_actual_responses
553 where open_closed = 'O';
554
555 return (count_var);
556
557 exception
558 when others then
559 wf_core.context('ALR_MLR_PKG', 'RESPONSE',
560 'Exception during OpenResponses');
561 raise;
562 end OpenResponses;
563
564 procedure GetMessageDetails(
565 msg_handle in number,
566 node_handle in number,
567 alr_msg_dtls_tbl out NOCOPY alr_msg_dtls_tbl_type)
568 is
569 begin
570 select application_id, alert_id, response_set_id, open_closed
571 into alr_msg_dtls_tbl(1).app_id,
572 alr_msg_dtls_tbl(1).alert_id,
573 alr_msg_dtls_tbl(1).response_set_id,
574 alr_msg_dtls_tbl(1).open_closed
575 from alr_actual_responses
576 where message_handle=msg_handle and
577 node_handle= node_handle;
578
579 exception
580 when no_data_found then
581 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
582 wf_log_pkg.string(WF_LOG_PKG.level_statement,
583 'alr.plsql.ALR_MLR_PACKAGE.GetMessageDetails',
584 'Error, message handle does not exist in db.');
585 end if;
586 wf_core.context('ALR_MLR_PKG', 'RESPONSE',
587 'Exception during GetMessageDetails');
588 raise;
589
590 when others then
591 wf_core.context('ALR_MLR_PKG', 'RESPONSE',
592 'Exception during GetMessageDetails');
593 raise;
594 end GetMessageDetails;
595
596 procedure InitResponseVar(
597 alr_msg_dtls_tbl in alr_msg_dtls_tbl_type,
598 alr_init_resp_tbl out NOCOPY alr_init_resp_tbl_type)
599 is
600
601 CURSOR c_responses_csr (p_app_id number,
602 p_alert_id number,
603 p_response_set_id number) IS
604 select variable_number, name, data_type, default_value,
605 NVL(detail_max_len,0)
606 from alr_response_variables
607 where application_id = p_app_id and
608 alert_id=p_alert_id and
609 response_set_id=p_response_set_id;
610 i number;
611 begin
612 i := 1;
613 OPEN c_responses_csr(alr_msg_dtls_tbl(1).app_id,
617 FETCH c_responses_csr into
614 alr_msg_dtls_tbl(1).alert_id,
615 alr_msg_dtls_tbl(1).response_set_id);
616 LOOP
618 alr_init_resp_tbl(i).var_num,
619 alr_init_resp_tbl(i).name,
620 alr_init_resp_tbl(i).data_type,
621 alr_init_resp_tbl(i).default_value,
622 alr_init_resp_tbl(i).max_len;
623
624 -- response variable name can have a leading &, which we want to ignore
625 -- var->detail_max_len = (word)(maxlen !=0 ? maxlen : 5000);
626 EXIT WHEN c_responses_csr%NOTFOUND;
627 i := i + 1;
628 END LOOP;
629 CLOSE c_responses_csr;
630
631 exception
632 when others then
633 wf_core.context('ALR_MLR_PKG', 'RESPONSE',
634 'Exception during InitResponseVar');
635 raise;
636 end InitResponseVar;
637
638 procedure InitValidResponses(
639 alr_msg_dtls_tbl in alr_msg_dtls_tbl_type,
640 alr_init_valid_resp_tbl out NOCOPY alr_init_valid_resp_tbl_type)
641 is
642 CURSOR c_validresp (p_app_id number,
643 p_alert_id number,
644 p_response_set_id number) IS
645 select response_id, type, response_text, response_name
646 from alr_valid_responses
647 where application_id = p_app_id and
648 alert_id=p_alert_id and
649 response_set_id=p_response_set_id;
650 i number;
651 begin
652 i := 1;
653 OPEN c_validresp(alr_msg_dtls_tbl(1).app_id,
654 alr_msg_dtls_tbl(1).alert_id,
655 alr_msg_dtls_tbl(1).response_set_id);
656 LOOP
657 FETCH c_validresp
658 into alr_init_valid_resp_tbl(i).resp_id,
659 alr_init_valid_resp_tbl(i).resp_type,
660 alr_init_valid_resp_tbl(i).resp_text,
661 alr_init_valid_resp_tbl(i).resp_name;
662 exit when c_validresp%NOTFOUND;
663 i := i + 1;
664 END LOOP;
665
666 CLOSE c_validresp;
667
668 exception
669 when others then
670 wf_core.context('ALR_MLR_PKG', 'RESPONSE',
671 'Exception during InitValidResponses');
672 raise;
673 end InitValidResponses;
674
675 procedure GetRespActions(
676 alr_msg_dtls_tbl in alr_msg_dtls_tbl_type,
677 alr_init_valid_resp_tbl in alr_init_valid_resp_tbl_type,
678 alr_get_resp_act_tbl out NOCOPY alr_get_resp_act_tbl_type)
679 is
680 CURSOR c_getrespact (p_app_id number,
681 p_alert_id number,
682 p_response_set_id number,
683 p_response_id number) IS
684 select r.response_id, r.action_id, a.name, NVL(a.action_type,'R'),
685 a.body, a.concurrent_program_id,
686 DECODE(a.list_id, NULL, a.to_recipients, d.to_recipients),
687 DECODE(a.list_id, NULL, a.cc_recipients, d.cc_recipients),
688 DECODE(a.list_id, NULL, a.bcc_recipients, d.bcc_recipients),
689 DECODE(a.list_id, NULL, a.print_recipients, d.print_recipients),
690 DECODE(a.list_id, NULL, a.printer, d.printer),
691 a.subject, a.reply_to, a.column_wrap_flag,
692 a.maximum_summary_message_width, a.action_level_type,
693 ' ', a.file_name, a.argument_string, a.program_application_id,
694 a.list_application_id, a.response_set_id, a.follow_up_after_days,
695 NVL(a.version_number,0)
696 from alr_actions a, alr_distribution_lists d,
697 alr_response_actions r
698 where r.application_id = p_app_id and
699 r.alert_id = p_alert_id and
700 r.response_set_id = p_response_set_id and
701 r.response_id = p_response_id and
702 r.application_id = a.application_id(+) and
703 r.action_id = a.action_id(+) and
704 r.enabled_flag= 'Y' and
705 NVL(r.end_date_active,SYSDATE)>=SYSDATE and
706 r.enabled_flag = a.enabled_flag(+) and
707 NVL(d.end_date_active(+),SYSDATE+1) >= SYSDATE and
708 a.application_id = d.application_id(+) and
709 a.list_id = d.list_id(+)
710 order by r.sequence;
711 i number;
712 t_counter number;
713 begin
714 i := 1;
715 t_counter := 1;
716 FOR t_counter IN 1..alr_init_valid_resp_tbl.LAST LOOP
717
718 OPEN c_getrespact(alr_msg_dtls_tbl(1).app_id,
719 alr_msg_dtls_tbl(1).alert_id,
720 alr_msg_dtls_tbl(1).response_set_id,
721 alr_init_valid_resp_tbl(t_counter).resp_id);
722 LOOP
723 FETCH c_getrespact
724 into alr_get_resp_act_tbl(i).response_id,
725 alr_get_resp_act_tbl(i).action_id,
726 alr_get_resp_act_tbl(i).action_name,
727 alr_get_resp_act_tbl(i).action_type,
728 alr_get_resp_act_tbl(i).action_body,
729 alr_get_resp_act_tbl(i).conc_pgm_id,
730 alr_get_resp_act_tbl(i).to_recip,
731 alr_get_resp_act_tbl(i).cc_recip,
732 alr_get_resp_act_tbl(i).bcc_recip,
733 alr_get_resp_act_tbl(i).print_recip,
734 alr_get_resp_act_tbl(i).printer,
735 alr_get_resp_act_tbl(i).subject,
736 alr_get_resp_act_tbl(i).reply_to,
737 alr_get_resp_act_tbl(i).column_wrap_flag,
738 alr_get_resp_act_tbl(i).max_sum_msg_width,
739 alr_get_resp_act_tbl(i).action_level_type,
740 alr_get_resp_act_tbl(i).action_level,
741 alr_get_resp_act_tbl(i).file_name,
742 alr_get_resp_act_tbl(i).arg_string,
743 alr_get_resp_act_tbl(i).pgm_app_id,
744 alr_get_resp_act_tbl(i).list_app_id,
745 alr_get_resp_act_tbl(i).act_resp_set_id,
746 alr_get_resp_act_tbl(i).follow_up_after_days,
750 i := i + 1;
747 alr_get_resp_act_tbl(i).version_num;
748 EXIT WHEN c_getrespact%NOTFOUND;
749
751 END LOOP;
752 CLOSE c_getrespact;
753 END LOOP;
754
755 exception
756 when others then
757 wf_core.context('ALR_MLR_PKG', 'RESPONSE',
758 'Exception during GetRespActions');
759 raise;
760 end GetRespActions;
761
762 procedure GetOutputValues(msg_handle in number,
763 node_handle in number,
764 alr_resp_var_values_tbl out NOCOPY
765 alr_resp_var_values_tbl_type)
766 is
767 CURSOR c_resp_var_csr (p_msg_handle number,
768 p_node_handle number) IS
769 select variable_name, value, data_type, detail_max_len
770 from alr_response_variable_values
771 where message_handle = p_msg_handle and
772 node_handle = p_node_handle;
773 i number;
774 begin
775 i := 1;
776 OPEN c_resp_var_csr(msg_handle,
777 node_handle);
778 LOOP
779 FETCH c_resp_var_csr into
780 alr_resp_var_values_tbl(i).variable_name,
781 alr_resp_var_values_tbl(i).value,
782 alr_resp_var_values_tbl(i).data_type,
783 alr_resp_var_values_tbl(i).detail_max_len;
784 EXIT WHEN c_resp_var_csr%NOTFOUND;
785 i := i + 1;
786 END LOOP;
787 CLOSE c_resp_var_csr;
788
789 exception
790 when others then
791 wf_core.context('ALR_MLR_PKG', 'RESPONSE',
792 'Exception during GetOutputValues');
793 raise;
794 end GetOutputValues;
795
796 procedure SaveRespHistory(msg_handle in number,
797 node_handle in number,
798 alr_msg_dtls_tbl in alr_msg_dtls_tbl_type,
799 l_from in varchar2,
800 p_response_body in varchar2,
801 p_resp_id in number)
802 is
803 p_action_id number;
804 p_version_num number;
805 seq number;
806 received varchar2(240);
807 p_to_recip varchar2(240);
808 p_cc_recip varchar2(240);
809 p_bcc_recip varchar2(240);
810 p_reply_to varchar2(240);
811 p_subject varchar2(240);
812 maintain_history_days number;
813 begin
814
815 maintain_history_days := 0;
816 select maintain_history_days into
817 maintain_history_days from
818 alr_alerts where
819 application_id=alr_msg_dtls_tbl(1).app_id and
820 alert_id = alr_msg_dtls_tbl(1).alert_id;
821
822 if (maintain_history_days > 0) then
823 select action_id into p_action_id from alr_action_history
824 where message_handle=msg_handle and
825 node_handle=node_handle;
826
827 select max(version_number) into p_version_num from
828 alr_actions where action_id=p_action_id;
829
830 select to_recipients, cc_recipients, bcc_recipients,
831 reply_to, subject
832 into p_to_recip, p_cc_recip, p_bcc_recip,
833 p_reply_to, p_subject
834 from alr_actions
835 where action_id = p_action_id and
836 version_number = p_version_num;
837
838 select alr_response_messages_s.nextval into seq from dual;
839 received := SYSDATE;
840
841 insert into alr_response_messages(message_handle, node_handle,
842 application_id, alert_id, response_set_id, response_type,
843 response_id, response_message_id, date_processed,
844 to_recipients, cc_recipients, bcc_recipients, sent_from,
845 reply_to, subject, body, received)
846 values(msg_handle, node_handle, alr_msg_dtls_tbl(1).app_id,
847 alr_msg_dtls_tbl(1).alert_id,
848 alr_msg_dtls_tbl(1).response_set_id,
849 'V',
850 p_resp_id, seq,
851 SYSDATE,
852 p_to_recip,
853 p_cc_recip,
854 p_bcc_recip,
855 l_from,
856 p_reply_to,
857 p_subject,
858 p_response_body,
859 received);
860 end if;
861
862 exception
863 when others then
864 wf_core.context('ALR_MLR_PKG', 'RESPONSE',
865 'Exception during SaveRespHistory');
866 if (wf_log_pkg.level_procedure >=
867 fnd_log.g_current_runtime_level) then
868 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
869 'alr.plsql.ALR_MLR_PACKAGE.Response',
870 'Exception in SaveRespHistory');
871 end if;
872 raise;
873 end SaveRespHistory;
874
875 procedure SaveRespVar(alr_msg_dtls_tbl in alr_msg_dtls_tbl_type,
876 msg_handle in number,
877 node_handle in number,
878 alr_init_resp_tbl in
879 alr_init_resp_tbl_type)
880 is
881 i number;
882 begin
883 i := 1;
884 if (alr_init_resp_tbl.COUNT > 0) then
885 for i IN 1..alr_init_resp_tbl.LAST LOOP
886 insert into alr_response_variable_values(application_id, alert_id,
887 response_set_id, message_handle, node_handle, variable_name,
888 value, data_type, detail_max_len)
889 select alr_msg_dtls_tbl(1).app_id,
890 alr_msg_dtls_tbl(1).alert_id,
891 alr_msg_dtls_tbl(1).response_set_id,
892 msg_handle,
893 node_handle,
894 alr_init_resp_tbl(i).name,
898 from dual where not exists
895 alr_init_resp_tbl(i).default_value,
896 alr_init_resp_tbl(i).data_type,
897 alr_init_resp_tbl(i).max_len
899 (select null from alr_response_variable_values
900 where message_handle=msg_handle and
901 node_handle=node_handle and
902 variable_name=alr_init_resp_tbl(i).name);
903 END LOOP;
904 end if;
905
906 exception
907 when others then
908 wf_core.context('ALR_MLR_PKG', 'RESPONSE',
909 'Exception during SaveResp');
910 raise;
911
912 end SaveRespVar;
913
914 procedure SaveOneRespVar(alr_msg_dtls_tbl in alr_msg_dtls_tbl_type,
915 msg_handle in number,
916 node_handle in number,
917 variable_name in varchar2,
918 value in varchar2,
919 alr_init_resp_tbl in
920 alr_init_resp_tbl_type)
921 is
922 t_counter number;
923
924 begin
925 t_counter := 1;
926
927 -- Loop through the alr_init_resp_tbl to find the
928 -- variable name that matches what the user entered in the body.
929 FOR t_counter IN 1..alr_init_resp_tbl.LAST loop
930 if (alr_init_resp_tbl(t_counter).name = variable_name) then
931 insert into alr_response_variable_values(application_id, alert_id,
932 response_set_id, message_handle, node_handle, variable_name,
933 value, data_type, detail_max_len)
934 select alr_msg_dtls_tbl(1).app_id,
935 alr_msg_dtls_tbl(1).alert_id,
936 alr_msg_dtls_tbl(1).response_set_id,
937 msg_handle,
938 node_handle,
939 variable_name,
940 value,
941 alr_init_resp_tbl(t_counter).data_type,
942 alr_init_resp_tbl(t_counter).max_len
943 from dual where not exists
944 (select null from alr_response_variable_values
945 where message_handle=msg_handle and
946 node_handle=node_handle and
947 variable_name=variable_name);
948 end if;
949 END LOOP;
950
951 exception
952 when others then
953 wf_core.context('ALR_MLR_PKG', 'RESPONSE',
954 'Exception during SaveOneRespVar');
955 raise;
956 end SaveOneRespVar;
957
958 procedure SaveRespActHistory(msg_handle in number,
959 node_handle in number,
960 response_msg_id in number,
961 oracle_id in number,
962 seq in number,
963 alr_msg_dtls_tbl in
964 alr_msg_dtls_tbl_type,
965 alr_get_resp_act_tbl in
966 alr_get_resp_act_tbl_type,
967 version_num in number,
968 success_flag in varchar2)
969 is
970 begin
971 insert into alr_response_action_history(message_handle,
972 node_handle, response_message_id, oracle_id, sequence, application_id,
973 alert_id, action_id, version_number, success_flag)
974 values (msg_handle, node_handle, response_msg_id, oracle_id,
975 seq, alr_msg_dtls_tbl(1).app_id,
976 alr_msg_dtls_tbl(1).alert_id,
977 alr_get_resp_act_tbl(1).action_id, version_num,
978 success_flag);
979
980 exception
981 when others then
982 wf_core.context('ALR_MLR_PKG', 'RESPONSE',
983 'Exception during SaveRespActHistory');
984 raise;
985 end SaveRespActHistory;
986
987 procedure CloseResp(msg_handle in number,
988 node_handle in number,
989 alr_init_valid_resp_tbl in
990 alr_init_valid_resp_tbl_type,
991 open_closed in varchar2,
992 action_set_pass_fail in varchar2)
993 is
994 begin
995 update alr_actual_responses
996 set response_id=alr_init_valid_resp_tbl(1).resp_id,
997 open_closed= open_closed, action_set_pass_fail=action_set_pass_fail
998 where message_handle = msg_handle and node_handle=node_handle;
999
1000 exception
1001 when others then
1002 wf_core.context('ALR_MLR_PKG', 'RESPONSE',
1003 'Exception during CloseResp');
1004 raise;
1005 end CloseResp;
1006
1007 procedure Test
1008 is
1009
1010 reqid number;
1011
1012 -- ALP_NO_RESPONSE = "N"
1013 -- ALP_VALID_RESPONSE = "V"
1014 -- ALP_INVALID_RESPONSE = "I"
1015 resp_type varchar(1);
1016
1017 --GetResponseDetails
1018 count_var number;
1019 msg_handle number;
1020 node_handle number;
1021 alr_msg_dtls_tbl alr_msg_dtls_tbl_type;
1022
1023 --InitResponseVar
1024 alr_init_resp_tbl alr_init_resp_tbl_type;
1025
1026 -- InitValidResponses
1027 alr_init_valid_resp_tbl alr_init_valid_resp_tbl_type;
1028
1029 -- GetRespActions
1030 alr_get_resp_act_tbl alr_get_resp_act_tbl_type;
1031
1032 -- GetOutputValues
1033 alr_resp_var_values_tbl alr_resp_var_values_tbl_type;
1034
1035 -- SaveRespHistory
1036 l_from varchar2(240);
1037 received varchar2(240);
1038
1039 l_node_handle varchar2(20);
1040 l_msg_handle varchar2(20);
1041 l_morcl_id varchar2(20);
1042 l_name varchar2(20);
1043 l_format varchar2(20);
1044 l_to_recip varchar2(20);
1045 l_cc_recip varchar2(20);
1046 l_bcc_recip varchar2(20);
1050
1047 l_reply_to varchar2(20);
1048 l_subject varchar2(20);
1049 l_response_body varchar2(20);
1051 t_counter number;
1052 found number;
1053 input varchar2(20);
1054
1055 begin
1056
1057 -- Check for open responses
1058 count_var := OpenResponses;
1059 if (count_var > 0) then
1060 node_handle := 1;
1061 msg_handle := 114145;
1062 l_node_handle := '1';
1063 l_msg_handle := '114145';
1064 received := SYSDATE;
1065 l_to_recip := '[email protected]';
1066 l_cc_recip := '[email protected]';
1067 l_bcc_recip := '[email protected]';
1068 l_reply_to := '[email protected]';
1069 l_subject := 'testing';
1070 l_from := '[email protected]';
1071 found := 0;
1072 input := '200';
1073 l_name := 'TESTVAR';
1074 l_format := 'TESTVAR="?"';
1075 l_response_body := input;
1076 l_morcl_id := '900';
1077
1078
1079 -- get application id, alert id, and response set
1080 GetMessageDetails(msg_handle, node_handle,
1081 alr_msg_dtls_tbl);
1082 -- Initialize the response
1083 InitResponseVar(alr_msg_dtls_tbl,
1084 alr_init_resp_tbl);
1085
1086 -- Initialize valid responses
1087 InitValidResponses(alr_msg_dtls_tbl,
1088 alr_init_valid_resp_tbl);
1089
1090 GetRespActions(alr_msg_dtls_tbl,
1091 alr_init_valid_resp_tbl,
1092 alr_get_resp_act_tbl);
1093
1094 -- Check user's response to see if valid response
1095 -- First case: They entered a text value, no variables
1096 for t_counter IN 1..alr_init_valid_resp_tbl.LAST LOOP
1097 -- if value in email is plain text
1098 if (input = alr_init_valid_resp_tbl(t_counter).resp_text) then
1099 -- insert response received into history
1100 found := 1;
1101 SaveRespHistory(msg_handle, node_handle,
1102 alr_msg_dtls_tbl,
1103 l_from,
1104 'fake body',
1105 alr_init_valid_resp_tbl(t_counter).resp_id);
1106
1107 -- If it matches here then they did not reply
1108 -- with a response variable. So, update all
1109 -- response variables with default values and
1110 -- save to history.
1111 SaveRespVar(alr_msg_dtls_tbl,
1112 msg_handle, node_handle,
1113 alr_init_resp_tbl);
1114
1115 -- CALL CONCURRENT PROGRAM TO PERFORM ACTIONS
1116 reqid := fnd_request.submit_request(application => 'ALR',
1117 program => 'ALPPWF',
1118 argument1 => l_node_handle,
1119 argument2 => l_msg_handle,
1120 argument3 => l_morcl_id,
1121 argument4 => 'V',
1122 argument5 => l_name,
1123 argument6 => input,
1124 argument7 => l_format);
1125 if reqid = 0 then
1126 if (wf_log_pkg.level_statement >=
1127 fnd_log.g_current_runtime_level) then
1128 wf_log_pkg.string(WF_LOG_PKG.level_statement,
1129 'alr.plsql.ALR_MLR_PACKAGE.Response',
1130 'The request ID retued 0. Raising -20160 app error');
1131 end if;
1132 raise_application_error(-20160, FND_MESSAGE.GET);
1133 end if;
1134 end if;
1135 END LOOP;
1136
1137 -- 2nd case is if they entered a variable but no value
1138 -- ? is in value.
1139 if (input = '?' and found = 0) then
1140 for t_counter in 1..alr_init_valid_resp_tbl.LAST LOOP
1141 if (l_format =
1142 alr_init_valid_resp_tbl(t_counter).resp_text) then
1143 found := 1;
1144 -- insert response received into history
1145 SaveRespHistory(msg_handle, node_handle,
1146 alr_msg_dtls_tbl,
1147 l_from,
1148 'fake body',
1149 alr_init_valid_resp_tbl(t_counter).resp_id);
1150
1151 -- If it matches here then they did not reply
1152 -- with a response variable. So, update all
1153 -- response variables with default values and
1154 -- save to history.
1155 SaveRespVar(alr_msg_dtls_tbl,
1156 msg_handle, node_handle,
1157 alr_init_resp_tbl);
1158
1159 -- CALL CONCURRENT PROGRAM TO PERFORM ACTIONS
1160 reqid := fnd_request.submit_request(application => 'ALR',
1161 program => 'ALPPWF',
1162 argument1 => l_node_handle,
1163 argument2 => l_msg_handle,
1164 argument3 => l_morcl_id,
1165 argument4 => 'V',
1166 argument5 => l_name,
1170 if (wf_log_pkg.level_statement >=
1167 argument6 => input,
1168 argument7 => l_format);
1169 if (reqid = 0) then
1171 fnd_log.g_current_runtime_level) then
1172 wf_log_pkg.string(WF_LOG_PKG.level_statement,
1173 'alr.plsql.ALR_MLR_PACKAGE.Response',
1174 'The request ID retued 0. Raising -20160 app error');
1175 end if;
1176 raise_application_error(-20160, FND_MESSAGE.GET);
1177 end if;
1178 end if;
1179 end loop;
1180 end if;
1181
1182 -- Third case is if they entered a variable and a value
1183 if (found = 0) then
1184 for t_counter IN 1..alr_init_valid_resp_tbl.LAST LOOP
1185 if (l_format =
1186 alr_init_valid_resp_tbl(t_counter).resp_text) then
1187 found := 1;
1188
1189 -- insert response received into history
1190 SaveRespHistory(msg_handle, node_handle,
1191 alr_msg_dtls_tbl,
1192 l_from,
1193 'fake body',
1194 alr_init_valid_resp_tbl(t_counter).resp_id);
1195
1196 SaveOneRespVar(alr_msg_dtls_tbl,
1197 msg_handle, node_handle,
1198 l_name,
1199 input,
1200 alr_init_resp_tbl);
1201
1202 -- CALL CONCURRENT PROGRAM TO PERFORM ACTIONS
1203 if (wf_log_pkg.level_procedure >=
1204 fnd_log.g_current_runtime_level) then
1205 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
1206 'alr.plsql.ALR_MLR_PACKAGE.Response',
1207 'Submitting Action Processor');
1208 end if;
1209 reqid := fnd_request.submit_request(application => 'ALR',
1210 program => 'ALPPWF',
1211 argument1 => l_node_handle,
1212 argument2 => l_msg_handle,
1213 argument3 => l_morcl_id,
1214 argument4 => 'V',
1215 argument5 => l_name,
1216 argument6 => input,
1217 argument7 => l_format);
1218 if (reqid = 0) then
1219 if (wf_log_pkg.level_statement >=
1220 fnd_log.g_current_runtime_level) then
1221 wf_log_pkg.string(WF_LOG_PKG.level_statement,
1222 'alr.plsql.ALR_MLR_PACKAGE.Response',
1223 'The request ID retued 0. Raising -20160 app error');
1224 end if;
1225 raise_application_error(-20160, FND_MESSAGE.GET);
1226 end if;
1227 end if;
1228 end loop;
1229 end if;
1230
1231 -- last case is invalid response
1232 if (found=0) then
1233 reqid := fnd_request.submit_request(application => 'ALR',
1234 program => 'ALPPWF',
1235 argument1 => l_node_handle,
1236 argument2 => l_msg_handle,
1237 argument3 => l_morcl_id,
1238 argument4 => 'V',
1239 argument5 => l_name,
1240 argument6 => input,
1241 argument7 => l_format);
1242 if (reqid = 0) then
1243 if (wf_log_pkg.level_statement >=
1244 fnd_log.g_current_runtime_level) then
1245 wf_log_pkg.string(WF_LOG_PKG.level_statement,
1246 'alr.plsql.ALR_MLR_PACKAGE.Response',
1247 'The request ID retued 0. Raising -20160 app error');
1248 end if;
1249 raise_application_error(-20160, FND_MESSAGE.GET);
1250 end if;
1251
1252 end if;
1253
1254 end if;
1255
1256 end Test;
1257
1258 end; /*ALR_MLR_PACKAGE*/