[Home] [Help]
PACKAGE BODY: APPS.MTH_EVENT_PKG
Source
1 PACKAGE Body mth_event_PKG AS
2 /*$Header: mthevntb.pls 120.16.12020000.2 2012/07/25 04:03:56 sasuren ship $ */
3
4 p_event_actions ActionHandlerTableType;
5 p_action_statuses ActionStatusTableType;
6 num_status_rec NUMBER;
7
8 PROCEDURE handle_event(p_equipment_fk_key IN NUMBER,
9 p_event_type IN VARCHAR2,
10 p_Shift_workday_fk_key IN NUMBER,
11 p_Workorder_fk_key IN NUMBER,
12 p_Reading_time IN DATE,
13 p_reason_code IN VARCHAR2,
14 p_equip_status IN NUMBER,
15 p_event_description IN VARCHAR2)
16 AS
17 l_event_id NUMBER := 0;
18
19 BEGIN
20 init_action_handler_rec;
21
22 l_event_id := create_mth_event(p_equipment_fk_key,
23 p_event_type,
24 p_Shift_workday_fk_key,
25 p_Workorder_fk_key,
26 p_Reading_time,
27 p_reason_code,
28 p_equip_status,
29 p_event_description);
30
31 IF l_event_id = 0 THEN
32 raise_application_error(-20001,'Fail to create event in MTH_EVENTS_D');
33 END IF;
34
35 action_handler_lookup(p_equipment_fk_key, p_event_type, p_reason_code, p_event_actions);
36
37 IF p_event_actions.Count <> 0 THEN
38 FOR j IN p_event_actions.FIRST .. p_event_actions.LAST
39 LOOP
40
41 init_action_status_rec;
42 ACTION_HANDLER_DISPATCHER(l_event_id, p_event_actions(j), p_action_statuses);
43 update_mth_event_action(l_event_id, p_action_statuses);
44 END LOOP;
45 END IF;
46
47 exception
48 when OTHERS
49 then raise_application_error(-20000,'Unknown Exception in create_mth_event');
50
51 END handle_event;
52
53 PROCEDURE init_action_handler_rec
54 AS
55
56 BEGIN
57 p_event_actions := ActionHandlerTableType();
58 END init_action_handler_rec;
59
60 PROCEDURE init_action_status_rec
61 AS
62
63 BEGIN
64 p_action_statuses := ActionStatusTableType();
65 num_status_rec:= 0;
66 END init_action_status_rec;
67
68
69 FUNCTION create_mth_event(p_equipment_fk_key IN NUMBER,
70 p_event_type IN VARCHAR2,
71 p_Shift_workday_fk_key IN NUMBER,
72 p_Workorder_fk_key IN NUMBER,
73 p_Reading_time IN DATE,
74 p_reason_code IN VARCHAR2,
75 p_equip_status IN NUMBER,
76 p_event_description IN VARCHAR2) RETURN NUMBER
77 AS
78 l_event_id NUMBER := 0;
79
80 BEGIN
81 INSERT INTO MTH_EVENTS
82 (EVENT_ID,EVENT_TYPE,EVENT_DESCRIPTION,REASON_CODE,TAG_READING_TIME,EQUIPMENT_FK_KEY,
83 SHIFT_WORKDAY_FK_KEY,WORKORDER_FK_KEY,EQUIP_STATUS,
84 CREATION_DATE, LAST_UPDATE_DATE,CREATION_SYSTEM_ID,LAST_UPDATE_SYSTEM_ID,CREATED_BY,
85 LAST_UPDATE_LOGIN,LAST_UPDATED_BY)
86 VALUES (mth_events_d_seq.nextval, p_event_type, p_event_description, p_reason_code, p_reading_time, p_equipment_fk_key,
87 p_shift_workday_fk_key, p_workorder_fk_key, p_equip_status, SYSDATE, SYSDATE, -1, -99999, -1,
88 -99999, -99999);
89
90 -- l_event_id := mth_events_d_seq.CURRVAL;
91 SELECT mth_events_d_seq.CURRVAL INTO l_event_id FROM dual;
92
93
94 -- commit the event generation no matter what SQL error caught
95 COMMIT;
96 RETURN l_event_id;
97
98 exception
99 when OTHERS THEN
100 raise_application_error(-20001,'Unknown Exception in create_mth_event');
101 RETURN l_event_id;
102
103 END create_mth_event;
104
105 PROCEDURE ACTION_HANDLER_LOOKUP (p_equipment_fk_key IN NUMBER,
106 p_event_type IN VARCHAR2,
107 p_reason_code IN VARCHAR2,
108 p_event_actions OUT NOCOPY ActionHandlerTableType)
109 AS
110 l_event_actions ActionHandlerTableType := ActionHandlerTableType();
111 l_event_action_rec ActionHandlerRec;
112 l_rec NUMBER := 0;
113
114 CURSOR cur
115 IS
116 SELECT A.PERSONNEL_FK_KEY, A.EMAIL_NOTIFICATION, A.MOBILE_NOTIFICATION,
117 A.ACTION_TYPE_CODE,
118 CASE WHEN A.ACTION_HANDLER_CODE IS NOT NULL THEN
119 DECODE( A.ACTION_HANDLER_CODE, A.ACTION_HANDLER_CODE,
120 (SELECT B.DESCRIPTION FROM FND_LOOKUPS B
121 WHERE B.LOOKUP_CODE = A.ACTION_HANDLER_CODE
122 AND B.LOOKUP_TYPE IN ('MTH_CUSTOM_PLSQL_API','MTH_CUSTOM_WS_API') ))
123 END AS ACTION_HANLDER_API, A.DOMAIN_NAME
124 FROM MTH_EVENT_ACTION_SETUP A, MTH_EVENT_SETUP C, MTH_PERSONNEL_D D
125 WHERE A.EVENT_SETUP_ID = C.EVENT_SETUP_ID
126 AND C.EVENT_TYPE_CODE = p_event_type
127 AND C.EQUIPMENT_FK_KEY = p_equipment_fk_key
128 AND A.PERSONNEL_FK_KEY = D.PERSONNEL_PK_KEY (+)
129 AND SYSDATE BETWEEN Nvl(D.EFFECTIVE_START_DATE, SYSDATE) AND Nvl(D.EFFECTIVE_END_DATE, SYSDATE)
130 AND Nvl(C.REASON_CODE,'x') = Nvl(p_reason_code,'x');
131
132 BEGIN
133 OPEN cur;
134 LOOP
135 FETCH cur INTO l_event_action_rec;
136 EXIT WHEN cur%NOTFOUND;
137 l_event_actions.extend;
138 l_rec := l_rec + 1;
139 l_event_actions(l_rec) := l_event_action_rec;
140
141 END LOOP;
142 CLOSE cur;
143 p_event_actions := l_event_actions;
144
145 EXCEPTION
146 WHEN OTHERS THEN
147 raise_application_error(-20002,'Unknown Exception to get Action Handler');
148
149 END ACTION_HANDLER_LOOKUP;
150
151
152 PROCEDURE ACTION_HANDLER_DISPATCHER (p_event_id IN NUMBER,
153 p_event_action_rec IN ActionHandlerRec,
154 p_action_statuses OUT NOCOPY ActionStatusTableType)
155 AS
156 plsql_method_not_defined EXCEPTION;
157 l_action_statuses ActionStatusTableType := ActionStatusTableType();
158 l_action_status_rec ActionStatusRec;
159
160 BEGIN
161 CASE p_event_action_rec.action_type_code
162 WHEN 'NOTIFICATION' THEN
163 BEGIN
164 INVOKE_EVENT_NOTIFICATION(p_event_id, p_event_action_rec, p_action_statuses);
165 END;
166 WHEN 'EAM_WR' THEN
167 BEGIN
168 INVOKE_EVENT_EAM_WR(p_event_id, p_event_action_rec, p_action_statuses);
169 END;
170 WHEN 'PLSQL_API' THEN
171 IF p_event_action_rec.action_handler_api IS NULL OR
172 Length(p_event_action_rec.Action_Handler_API) = 0 THEN
173 RAISE plsql_method_not_defined;
174 ELSE
175 BEGIN
176 INVOKE_EVENT_PLSQL_API(p_event_id, p_event_action_rec, p_action_statuses);
177 END;
178 END IF;
179 WHEN 'BPEL' THEN
180 IF p_event_action_rec.action_handler_api IS NULL OR
181 Length(p_event_action_rec.Action_Handler_API) = 0 THEN
182 RAISE plsql_method_not_defined;
183 ELSE
184 BEGIN
185 INVOKE_EVENT_BPEL(p_event_id, p_event_action_rec, p_action_statuses);
186 END;
187 END IF;
188 ELSE
189 RAISE plsql_method_not_defined;
190 END CASE;
191
192 EXCEPTION
193 WHEN plsql_method_not_defined THEN
194 l_action_status_rec.action_type_code := p_event_action_rec.action_type_code;
195 l_action_status_rec.action_status := 'Failed to process action due to API not found';
196 l_action_status_rec.action_handler_api := p_event_action_rec.action_handler_api;
197 l_action_statuses.extend;
198 num_status_rec:=num_status_rec+1;
199 l_action_statuses(num_status_rec) := l_action_status_rec;
200 p_action_statuses := l_action_statuses;
201
202 WHEN OTHERS THEN
203 l_action_status_rec.action_type_code := p_event_action_rec.action_type_code;
204 l_action_status_rec.action_status := 'Failed to process Custom API. An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM;
205 l_action_status_rec.action_handler_api := p_event_action_rec.action_handler_api;
206 l_action_statuses.extend;
207 num_status_rec:=num_status_rec+1;
208 l_action_statuses(num_status_rec) := l_action_status_rec;
209 p_action_statuses := l_action_statuses;
210
211
212 END ACTION_HANDLER_DISPATCHER;
213
214
215 PROCEDURE UPDATE_MTH_EVENT_ACTION (p_event_id IN NUMBER,
216 p_action_statuses IN ActionStatusTableType)
217 AS
218 l_action_status_rec ActionStatusRec;
219
220 BEGIN
221
222 IF p_action_statuses.Count <> 0 THEN
223
224 FOR j IN p_action_statuses.FIRST .. p_action_statuses.LAST
225 LOOP
226 l_action_status_rec := p_action_statuses(j);
227
228 INSERT INTO MTH_EVENT_ACTIONS
229 (EVENT_ID, ACTION_TYPE_CODE, LINE_NUM, NOTIFICATION_ID, NOTIFICATION_CONTENT, ACTION_REFERENCE_ID,
230 ACTION_STATUS, ACTION_HANDLER_API, CREATION_DATE, LAST_UPDATE_DATE, CREATION_SYSTEM_ID, LAST_UPDATE_SYSTEM_ID)
231 VALUES (p_event_id, l_action_status_rec.action_type_code, j,
232 l_action_status_rec.notification_id,
233 l_action_status_rec.notification_content,
234 l_action_status_rec.action_reference_id,
235 l_action_status_rec.action_status,
236 l_action_status_rec.action_handler_api,
237 SYSDATE, SYSDATE, -99999,-99999);
238
239 END LOOP;
240 END IF;
241
242 -- commit the event actions
243 COMMIT;
244
245 EXCEPTION
246 WHEN OTHERS THEN
247 raise_application_error(-20003,'Unknown Exception to Insert MTH_EVENT_ACTIONS');
248
249 END UPDATE_MTH_EVENT_ACTION;
250
251
252 PROCEDURE INVOKE_EVENT_NOTIFICATION(p_event_id IN NUMBER,
253 p_event_action_rec IN ActionHandlerRec,
254 p_action_statuses OUT NOCOPY ActionStatusTableType)
255 AS
256 l_email_id MTH_EVENT_ACTIONS.NOTIFICATION_ID%TYPE;
257 l_mobile_id MTH_EVENT_ACTIONS.NOTIFICATION_ID%TYPE;
258 l_subject VARCHAR2(100);
259 l_content VARCHAR2(1000);
260 l_status VARCHAR2(1024);
261 l_equip_name VARCHAR2(60);
262 l_event_type VARCHAR2(120);
263 l_event_description VARCHAR2(1024);
264 l_event_time VARCHAR2(24);
265 l_action_statuses ActionStatusTableType := ActionStatusTableType();
266 l_action_status_rec ActionStatusRec;
267 mailaddress_not_defined EXCEPTION;
268
269 BEGIN
270
271 SELECT b.equipment_name, a.event_type, a.event_description, To_Char(a.creation_date,'yyyy.mm.dd hh24:mi:ss')
272 INTO l_equip_name, l_event_type, l_event_description, l_event_time
273 FROM MTH_EVENTS a, MTH_EQUIPMENTS_D b
274 WHERE a.event_id = p_event_id
275 AND a.equipment_fk_key = b.equipment_pk_key;
276
277 IF p_event_action_rec.person_fk_key IS NOT NULL
278 THEN
279 IF p_event_action_rec.email_notification = 'Y' OR
280 p_event_action_rec.email_notification = '1'
281 THEN
282 SELECT email_address
283 INTO l_email_id
284 FROM MTH_PERSONNEL_D
285 WHERE personnel_pk_key = p_event_action_rec.person_fk_key;
286
287 l_subject := 'Alert! Equipment :'|| l_equip_name ||' , ' || 'Event Type : ' || l_event_type ;
288 l_content := 'Event condition :' || l_event_description || ' and Time of event : ' || l_event_time ;
289
290 IF l_email_id IS NULL OR Length(l_email_id) = 0 OR InStr(l_email_id,'@') = 0
291 THEN
292 RAISE mailaddress_not_defined;
293 ELSE
294 l_status := send_notification(l_email_id, l_subject, l_content);
295 l_action_status_rec.action_type_code := p_event_action_rec.action_type_code;
296 l_action_status_rec.notification_id := l_email_id;
297 l_action_status_rec.notification_content := l_content;
298 l_action_status_rec.action_status := l_status;
299 l_action_status_rec.action_handler_api := 'MTH_EVENT_PKG.INVOKE_EVENT_NOTIFICATION';
300 l_action_statuses.extend;
301 num_status_rec := num_status_rec + 1;
302 l_action_statuses(num_status_rec) := l_action_status_rec;
303 END IF;
304 END IF;
305
306 IF p_event_action_rec.mobile_notification = 'Y' OR
307 p_event_action_rec.mobile_notification = '1'
308 THEN
309 SELECT mobile_phone_number
310 INTO l_mobile_id
311 FROM MTH_PERSONNEL_D
312 WHERE personnel_pk_key = p_event_action_rec.person_fk_key;
313
314 IF l_mobile_id IS NULL OR Length(l_mobile_id) = 0
315 THEN
316 RAISE mailaddress_not_defined;
317 ELSE
318 l_mobile_id := l_mobile_id||p_event_action_rec.domain_name;
319 l_status := send_notification(l_mobile_id, l_subject, l_content);
320 l_action_status_rec.action_type_code := p_event_action_rec.action_type_code;
321 l_action_status_rec.notification_id := l_mobile_id;
322 l_action_status_rec.notification_content := l_content;
323 l_action_status_rec.action_status := l_status;
324 l_action_status_rec.action_handler_api := 'MTH_EVENT_PKG.INVOKE_EVENT_NOTIFICATION';
325 l_action_statuses.extend;
326 num_status_rec := num_status_rec + 1;
327 l_action_statuses(num_status_rec) := l_action_status_rec;
328 END IF;
329 END IF;
330 END IF;
331 p_action_statuses := l_action_statuses;
332
333 EXCEPTION
334 WHEN mailaddress_not_defined THEN
335 l_action_status_rec.action_type_code := p_event_action_rec.action_type_code;
336 l_action_status_rec.action_status := 'Failed to process notification. Please setup email address or mobile number.';
337 l_action_status_rec.action_handler_api := 'MTH_EVENT_PKG.INVOKE_EVENT_NOTIFICATION';
338 l_action_statuses.extend;
339 num_status_rec:=num_status_rec+1;
340 l_action_statuses(num_status_rec) := l_action_status_rec;
341 p_action_statuses := l_action_statuses;
342
343 WHEN OTHERS THEN
344 raise_application_error(-20003,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
345
346 END INVOKE_EVENT_NOTIFICATION;
347
348 FUNCTION SEND_NOTIFICATION(p_send_to varchar2,
349 p_subject varchar2,
350 p_text varchar2 ) RETURN VARCHAR2
351 AS
352 --l_mailhost VARCHAR2(64) := 'mail.oracle.com';
353 --l_mailport NUMBER := 25;
354 l_mailhost VARCHAR2(64) := fnd_profile.value('MTH_MAIL_SERVER_NAME');
355 /* The defaul mail server port usually is 25 by industry standard.
356 If port number is not set, should not affect sending notification.*/
357 l_mailport NUMBER := Nvl(fnd_profile.value('MTH_MAIL_SERVER_PORT'),25);
358 l_mail_conn UTL_SMTP.connection;
359 l_mesg VARCHAR2(1000);
360 l_flag NUMBER := 0;
361 crlf CONSTANT VARCHAR2(2):= fnd_global.local_chr(13) ||
362 fnd_global.local_chr(10);
363
364 BEGIN
365 IF l_mailhost IS NOT NULL AND Length(l_mailhost) <> 0
366 THEN
367 l_mail_conn := UTL_SMTP.open_connection(l_mailhost, l_mailport);
368 l_mesg := 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss') || crlf ||
369 'From: '|| NVL(fnd_profile.value('MTH_NOTIFICATION_DISPLAY_NAME'), 'Default User') || crlf ||
370 'Subject: '|| p_subject || crlf ||
371 'To: '||p_send_to || crlf || '' || crlf || p_text;
372
373 UTL_SMTP.helo(l_mail_conn, l_mailhost);
374 UTL_SMTP.mail(l_mail_conn, p_send_to);
375 UTL_SMTP.rcpt(l_mail_conn, p_send_to);
376 utl_smtp.open_data(l_mail_conn);
377 utl_smtp.write_data(l_mail_conn, l_mesg);
378 utl_smtp.close_data(l_mail_conn);
379 UTL_SMTP.quit(l_mail_conn);
380
381 RETURN 'Succeeded';
382 ELSE
383 RETURN 'Failed to send notification due to no configruation of mail server.';
384 END IF;
385
386 EXCEPTION
387 WHEN UTL_SMTP.INVALID_OPERATION THEN
388 RETURN 'Failed due to Invalid Operation in Mail attempt using UTL_SMTP.';
389 WHEN UTL_SMTP.TRANSIENT_ERROR THEN
390 RETURN 'Failed due to Temporary e-mail issue - try again';
391 WHEN UTL_SMTP.PERMANENT_ERROR THEN
392 RETURN 'Failed due to Permanent Error Encountered.';
393 WHEN OTHERS THEN
394 RETURN 'Failed due to Other errors occurs.';
395
396 END SEND_NOTIFICATION;
397
398 PROCEDURE INVOKE_EVENT_EAM_WR(p_event_id IN NUMBER,
399 p_event_action_rec IN ActionHandlerRec,
400 p_action_statuses OUT NOCOPY ActionStatusTableType)
401 AS
402 l_work_request_id NUMBER;
403 l_return_status VARCHAR2(50);
404 l_msg_count NUMBER;
405 l_msg_data VARCHAR2(50);
406 l_api_version NUMBER := 1.0;
407 l_init_msg_list VARCHAR2(100) := FND_API.G_FALSE;
408 l_commit VARCHAR2(100) := FND_API.G_TRUE;
409 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
410 l_mode VARCHAR2(50) := 'CREATE';
411 l_request_log VARCHAR2(10) := '';
412 l_user_id NUMBER := -1;
413 l_serial_number MTH_ASSET_MST.SERIAL_NUMBER%TYPE;
414 l_organization_id WIP_EAM_WORK_REQUESTS.ORGANIZATION_ID%TYPE;
415 l_asset_number WIP_EAM_WORK_REQUESTS.ASSET_NUMBER%TYPE;
416 l_asset_group WIP_EAM_WORK_REQUESTS.ASSET_GROUP%TYPE;
417 l_action_statuses ActionStatusTableType := ActionStatusTableType();
418 l_action_status_rec ActionStatusRec;
419 serial_number_not_found EXCEPTION;
420 asset_info_not_found EXCEPTION;
421 source_service_not_found EXCEPTION;
422 l_dblink VARCHAR2(100) := fnd_profile.value('MTH_SOURCE_INSTANCE_DBLINK');
423 l_sql VARCHAR2(4000);
424 l_schema_name VARCHAR2(50);
425 l_str VARCHAR2(1000);
426 l_system_pk VARCHAR2(30);
427 CURSOR cur_wr
428 IS
429 SELECT b.serial_number, d.ebs_organization_id, c.ASSET_NUMBER, c.ASSET_GROUP_ID
430 FROM MTH_EVENTS a, MTH_EQUIPMENTS_D b, MTH_ASSET_MST c, MTH_ORGANIZATIONS_L d ,MTH_SYSTEMS_SETUP e
431
432 WHERE a.event_id = p_event_id
433 AND a.equipment_fk_key = b.equipment_pk_key
434 AND d.ORGANIZATION_CODE = c.maintenance_org_code
435 AND b.serial_number = c.serial_number (+)
436 AND l_system_pk=e.system_pk
437 AND d.system_fk_key=e.system_pk_key;
438
439 BEGIN
440
441 l_schema_name := USER;
442 l_str := 'SELECT fnd_profile.Value@ ' || fnd_profile.Value('MTH_SOURCE_INSTANCE_DBLINK') || '(''MTH_EBS_GLOBAL_NAME'') FROM dual';
443
444 EXECUTE IMMEDIATE l_str INTO l_system_pk;
445
446 OPEN cur_wr;
447 LOOP
448 FETCH cur_wr INTO l_serial_number, l_organization_id, l_asset_number, l_asset_group;
449 EXIT WHEN cur_wr%NOTFOUND;
450
451 IF l_serial_number IS NULL THEN
452 RAISE serial_number_not_found;
453 END IF;
454
455 IF l_asset_number IS NULL OR
456 l_organization_id IS NULL OR
457 l_asset_group IS NULL
458 THEN
459 RAISE asset_info_not_found;
460 END IF;
461
462 IF l_dblink IS NULL THEN
463 RAISE source_service_not_found;
464 END IF;
465
466 EXECUTE IMMEDIATE
467 'BEGIN MTH_SRC_PKG.MTH_INVOKE_EAM_WORK_REQUEST@'||l_dblink||'(:1, :2, :3, :4); END; '
468 USING p_event_id, l_asset_number, l_asset_group, l_organization_id;
469
470 l_sql := 'SELECT WORK_REQUEST_ID ' ||
471 ' FROM WIP_EAM_WORK_REQUESTS@'||l_dblink||
472 ' WHERE ASSET_NUMBER = :1 ' ||
473 ' AND ASSET_GROUP = :2 ' ||
474 ' AND ORGANIZATION_ID = :3 ' ||
475 ' AND DESCRIPTION = :4 ';
476
477 BEGIN
478 EXECUTE IMMEDIATE l_sql INTO l_work_request_id
479 USING l_asset_number, l_asset_group, l_organization_id, 'moc_event_id_'||p_event_id;
480 EXCEPTION
481 WHEN No_Data_Found THEN
482 l_return_status := 'Failed';
483 END;
484
485 IF l_work_request_id IS NOT NULL THEN
486 l_return_status := 'Succeeded';
487 ELSE
488 l_return_status := 'Failed';
489 END IF;
490
491 l_action_status_rec.action_type_code := p_event_action_rec.action_type_code;
492 l_action_status_rec.action_reference_id := l_work_request_id;
493 l_action_status_rec.action_status := l_return_status;
494 l_action_status_rec.action_handler_api := 'WIP_EAM_WORKREQUEST_PUB.WORK_REQUEST_IMPORT';
495 l_action_statuses.extend;
496 num_status_rec := num_status_rec + 1;
497 l_action_statuses(num_status_rec) := l_action_status_rec;
498 END LOOP;
499 CLOSE cur_wr;
500 p_action_statuses := l_action_statuses;
501
502 EXCEPTION
503 WHEN serial_number_not_found THEN
504 l_action_status_rec.action_type_code := p_event_action_rec.action_type_code;
505 l_action_status_rec.action_status := 'Failed to invoke EAM_WR due to Serial Number Not Found.';
506 l_action_status_rec.action_handler_api := 'MTH_EVENT_PKG.INVOKE_EVENT_EAM_WR';
507 l_action_statuses.extend;
508 num_status_rec := num_status_rec + 1;
509 l_action_statuses(num_status_rec) := l_action_status_rec;
510 p_action_statuses := l_action_statuses;
511
512 WHEN asset_info_not_found THEN
513 l_action_status_rec.action_type_code := p_event_action_rec.action_type_code;
514 l_action_status_rec.action_status := 'Failed to invoke EAM_WR due to Asset Information Not Found.';
515 l_action_status_rec.action_handler_api := 'MTH_EVENT_PKG.INVOKE_EVENT_EAM_WR';
516 l_action_statuses.extend;
517 num_status_rec := num_status_rec + 1;
518 l_action_statuses(num_status_rec) := l_action_status_rec;
519 p_action_statuses := l_action_statuses;
520
521 WHEN source_service_not_found THEN
522 l_action_status_rec.action_type_code := p_event_action_rec.action_type_code;
523 l_action_status_rec.action_status := 'Failed to creat EAM WR due to Source Service is not available.';
524 l_action_status_rec.action_handler_api := 'MTH_EVENT_PKG.INVOKE_EVENT_EAM_WR';
525 l_action_statuses.extend;
526 num_status_rec := num_status_rec + 1;
527 l_action_statuses(num_status_rec) := l_action_status_rec;
528 p_action_statuses := l_action_statuses;
529
530 WHEN OTHERS THEN
531 l_action_status_rec.action_type_code := p_event_action_rec.action_type_code;
532 l_action_status_rec.action_status := 'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM;
533 l_action_status_rec.action_handler_api := 'MTH_EVENT_PKG.INVOKE_EVENT_EAM_WR';
534 l_action_statuses.extend;
535 num_status_rec := num_status_rec + 1;
536 l_action_statuses(num_status_rec) := l_action_status_rec;
537 p_action_statuses := l_action_statuses;
538
539 END INVOKE_EVENT_EAM_WR;
540
541 PROCEDURE INVOKE_EVENT_PLSQL_API(p_event_id IN NUMBER,
542 p_event_action_rec IN ActionHandlerRec,
543 p_action_statuses OUT NOCOPY ActionStatusTableType)
544 AS
545 l_action_statuses ActionStatusTableType := ActionStatusTableType();
546 l_action_status_rec ActionStatusRec;
547 l_return_status VARCHAR2(1024);
548
549 BEGIN
550
551 EXECUTE IMMEDIATE
552 'BEGIN ' ||
553 p_event_action_rec.action_handler_api || '(:1, :2); END;'
554 USING IN p_event_id, OUT l_return_status;
555
556 l_action_status_rec.action_type_code := p_event_action_rec.action_type_code;
557 l_action_status_rec.action_status := l_return_status;
558 l_action_status_rec.action_handler_api := p_event_action_rec.action_handler_api;
559 l_action_statuses.extend;
560 num_status_rec := num_status_rec + 1;
561 l_action_statuses(num_status_rec) := l_action_status_rec;
562 p_action_statuses := l_action_statuses;
563
564 EXCEPTION
565 WHEN OTHERS THEN
566 l_action_status_rec.action_type_code := p_event_action_rec.action_type_code;
567 l_action_status_rec.action_status := 'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM;
568 l_action_status_rec.action_handler_api := p_event_action_rec.action_handler_api;
569 l_action_statuses.extend;
570 num_status_rec := num_status_rec + 1;
571 l_action_statuses(num_status_rec) := l_action_status_rec;
572 p_action_statuses := l_action_statuses;
573
574
575 END INVOKE_EVENT_PLSQL_API;
576
577 PROCEDURE INVOKE_EVENT_BPEL(p_event_id IN NUMBER,
578 p_event_action_rec IN ActionHandlerRec,
579 p_action_statuses OUT NOCOPY ActionStatusTableType)
580 AS
581 l_action_statuses ActionStatusTableType := ActionStatusTableType();
582 l_action_status_rec ActionStatusRec;
583 l_return VARCHAR2(30000);
584 l_url VARCHAR2(30000);
585 l_namespace VARCHAR2(30000);
586 l_action VARCHAR2(30000);
587 l_ws_operation NUMBER;
588 BEGIN
589
590 IF p_event_action_rec.action_handler_api IS NOT NULL OR Length(p_event_action_rec.action_handler_api) <> 0
591 THEN
592 l_url := SubStr(p_event_action_rec.action_handler_api,
593 InStr(p_event_action_rec.action_handler_api, 'URL=')+4,
594 InStr(p_event_action_rec.action_handler_api,',')-(InStr(p_event_action_rec.action_handler_api, 'URL=')+4));
595 l_namespace := SubStr(p_event_action_rec.action_handler_api, InStr(p_event_action_rec.action_handler_api, 'NameSpace=')+10);
596 l_ws_operation := InStr(p_event_action_rec.action_handler_api, 'Operation=');
597
598 IF l_ws_operation = 0 THEN
599 l_action := 'process';
600 ELSE
601 l_action := SubStr(p_event_action_rec.action_handler_api, InStr(p_event_action_rec.action_handler_api, 'Operation=')+10);
602 END IF;
603
604
605 IF (l_url IS NOT NULL AND Length(l_url) <> 0) AND
606 (l_namespace IS NOT NULL AND Length(l_namespace) <> 0)
607 THEN
608 -- invoke HTTP Request
609 l_return := invoke_http_request(p_event_id => p_event_id,
610 p_url => l_url,
611 p_namespace => l_namespace,
612 p_action => l_action);
613
614 l_action_status_rec.action_type_code := p_event_action_rec.action_type_code;
615 l_action_status_rec.action_status := l_return;
616 l_action_status_rec.action_handler_api := p_event_action_rec.action_handler_api;
617 l_action_statuses.extend;
618 num_status_rec := num_status_rec + 1;
619 l_action_statuses(num_status_rec) := l_action_status_rec;
620 END IF;
621 END IF;
622 p_action_statuses := l_action_statuses;
623
624 END INVOKE_EVENT_BPEL;
625
626 FUNCTION invoke_http_request(p_event_id IN NUMBER,
627 p_url IN VARCHAR2,
628 p_namespace IN VARCHAR2,
629 p_action IN VARCHAR2) RETURN VARCHAR2
630 AS
631 -- ---------------------------------------------------------------------
632 soap_request varchar2(30000);
633 soap_respond varchar2(30000);
634 l_proxy_server VARCHAR2(100) := fnd_profile.value('MTH_SOA_PROXY_SERVER');
635 --l_proxy_server VARCHAR2(100) := 'www-proxy.us.oracle.com';
636 l_action VARCHAR2(100);
637 http_req utl_http.req;
638 http_resp utl_http.resp;
639 launch_url varchar2(240) ;
640 l_returned_status varchar2(1024);
641 l_from NUMBER;
642 l_end NUMBER;
643
644 BEGIN
645 -- Set proxy details if no direct net connection.
646 IF l_proxy_server IS NULL THEN
647 RETURN 'Failed to process BPEL due to no proxy server setting.';
648 END IF;
649
650 UTL_HTTP.set_proxy(l_proxy_server, NULL);
651 --UTL_HTTP.set_persistent_conn_support(TRUE);
652
653 soap_request:= '<?xml version="1.0" encoding="UTF-8"?>
654 <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
655 <soap:Header/>
656 <soap:Body xmlns:ns1="'||p_namespace||'">'||
657 '<ns1:event_id>'||To_Char(p_event_id)||'</ns1:event_id>'||
658 '</soap:Body></soap:Envelope>';
659
660 http_req:= utl_http.begin_request(p_url,'POST','HTTP/1.0');
661 utl_http.set_header(http_req, 'Content-Type', 'text/xml') ;
662 utl_http.set_header(http_req, 'Content-Length', length(soap_request)) ;
663
664 --utl_http.set_header(http_req, 'SOAPAction', 'initiate');
665 utl_http.set_header(http_req, 'SOAPAction', p_action);
666 utl_http.write_text(http_req, soap_request) ;
667 http_resp:= utl_http.get_response(http_req) ;
668 utl_http.read_text(http_resp, soap_respond) ;
669 utl_http.end_response(http_resp) ;
670
671 soap_respond := REPLACE(soap_respond,' ','');
672
673 l_from := InStr(SubStr(soap_respond, instr (soap_respond, '<return_status')), '>');
674 l_end := instr(SubStr(soap_respond, instr (soap_respond, '<return_status')), '</return_status>');
675
676 l_returned_status := SubStr(SubStr(soap_respond, instr (soap_respond, '<return_status')),l_from+1,l_end-l_from-1);
677
678 IF l_returned_status IS NULL OR Length(l_returned_status) = 0 THEN
679 l_returned_status := 'Failed';
680 END IF;
681
682 RETURN l_returned_status;
683
684 EXCEPTION
685 WHEN UTL_HTTP.INIT_FAILED THEN
686 RETURN 'Initialization of the HTTP-callout subsystem fails.';
687 WHEN UTL_HTTP.REQUEST_FAILED THEN
688 RETURN 'The HTTP call fails due to Network problem.';
689 WHEN OTHERS THEN
690 RETURN 'Failed to process HTTP Request due to Other errors occurs.';
691
692 END invoke_http_request;
693
694 END MTH_EVENT_PKG;