[Home] [Help]
PACKAGE BODY: APPS.JTF_USR_HKS
Source
1 package body JTF_USR_HKS as
2 /* $Header: JTFUHKSB.pls 120.4 2005/10/21 12:24:43 kjayapra ship $ */
3
4 -------------------------------------------------------------------------
5 G_PKJ_NAME CONSTANT VARCHAR2(25) := 'JTF_USR_HKS';
6
7 /* function to check for execution of pre/post logic */
8
9 Function Ok_To_Execute( p_Pkg_name varchar2,
10 p_API_name varchar2,
11 p_Process_type varchar2,
12 p_User_hook_type varchar2
13 ) Return Boolean is
14 l_execute Varchar2(1);
15 l_conc_pgm_id Number;
16 l_conc_pgm_name Varchar2(25) := 'DEFAULT';
17
18 Begin
19
20 Begin
21
22 Select EXECUTE_FLAG
23 into l_execute
24 from
25 JTF_USER_HOOKS
26 Where
27 pkg_name = p_pkg_name and
28 api_name = p_api_name and
29 processing_type = p_process_type and
30 user_hook_type = p_user_hook_type;
31 Exception
32 When NO_DATA_FOUND then
33 Return FALSE;
34
35 End;
36
37 If ( l_execute = 'Y' ) then
38 l_conc_pgm_id := fnd_global.conc_program_id;
39
40
41 If ( l_conc_pgm_id = -1 ) then
42 return TRUE;
43 else
44 /* actual logic should have a statement to find out conc program name from
45 fnd_concurrent programs for the conc_pgm_id and compare for that */
46
47 if ( l_conc_pgm_name = 'CRM_SUBSCRIBER') AND
48 ( p_User_hook_type = 'M' ) then
49 return FALSE;
50 else
51 return TRUE;
52 end if;
53 End if;
54 Else
55 return FALSE;
56 End if;
57
58 End Ok_To_Execute;
59
60
61
62 /* Procedure to launch non message generating workflow */
63
64 Procedure WrkflowLaunch( p_Wf_item_name varchar2,
65 p_Wf_item_process_name varchar2,
66 p_Wf_item_key varchar2,
67 p_Bind_data_id Number,
68 x_return_code Out NOCOPY varchar2
69 ) is
70
71 l_bind_data_id Number := p_bind_data_id;
72
73 Cursor ATT_BIND_DATA is
74 Select bind_name, bind_value, data_type
75 From JTF_BIND_DATA
76 Where bind_data_id = l_bind_data_id And
77 bind_type = 'W';
78
79 l_wf_item_exists Boolean := FALSE;
80 l_owner_name Varchar2(100);
81
82 Begin
83
84
85 -- Check for existence of same workflow instance.
86 l_wf_item_exists := wf_item.item_exist( itemtype => p_wf_item_name,
87 itemkey => p_wf_item_key );
88 if ( l_wf_item_exists ) then
89 FND_MESSAGE.SET_NAME('JTF','JTF_WF_ALREADY_EXISTS');
90 FND_MESSAGE.SET_TOKEN('WF_NAME',p_wf_item_name);
91 FND_MESSAGE.SET_TOKEN('WF_KEY',p_wf_item_key);
92 FND_MSG_PUB.Add;
93 RAISE FND_API.G_EXC_ERROR;
94 else
95 -- Create workflow process.
96 wf_engine.CreateProcess( itemType => p_Wf_item_name,
97 itemKey => p_Wf_item_key,
98 process => p_Wf_item_process_name
99 );
100
101 -- Set workflow instance owner
102
103 l_owner_name := FND_GLOBAL.USER_NAME;
104
105 wf_engine.SetItemOwner( itemtype => p_wf_item_name,
106 itemkey => p_wf_item_key,
107 owner => l_owner_name );
108
109 -- Set Workflow item attributes
110
111 FOR wf_att IN att_bind_data LOOP
112
113 IF ( wf_att.data_type = 'T' ) then
114 wf_engine.setitemattrText( itemtype => p_wf_item_name,
115 itemkey => p_wf_item_key,
116 aname => wf_att.bind_name,
117 avalue => wf_att.bind_value );
118
119 ELSIF ( wf_att.data_type = 'N' ) then
120 wf_engine.setitemattrNumber( itemtype => p_wf_item_name,
121 itemkey => p_wf_item_key,
122 aname => wf_att.bind_name,
123 avalue => wf_att.bind_value );
124
125 ELSIF ( wf_att.data_type = 'D' ) then
126 wf_engine.setitemattrDate( itemtype => p_wf_item_name,
127 itemkey => p_wf_item_key,
128 aname => wf_att.bind_name,
129 avalue => to_date(wf_att.bind_value,'YYYY/MM/DD') );
130
131 END IF;
132 END LOOP;
133
134
135 /* start workflow process */
136 wf_engine.StartProcess( itemType => p_wf_item_name,
137 itemKey => p_wf_item_key
138 );
139
140 FND_MESSAGE.SET_NAME('JTF','JTF_WF_LAUNCH_SUCCESS');
141 FND_MESSAGE.SET_TOKEN('WF_NAME',p_wf_item_name);
142 FND_MESSAGE.SET_TOKEN('WF_KEY',p_wf_item_key);
143 FND_MSG_PUB.Add;
144
145 /* Purge Bind data table */
146 JTF_USR_HKS.Purge_Bind_Data( p_bind_data_id => l_bind_data_id,
147 p_bind_type => 'W'
148 );
149
150 x_return_code := FND_API.G_RET_STS_SUCCESS;
151
152 end if;
153 Exception
154 When FND_API.G_EXC_ERROR then
155 x_return_code := FND_API.G_RET_STS_ERROR;
156
157 When FND_API.G_EXC_UNEXPECTED_ERROR then
158 x_return_code := FND_API.G_RET_STS_UNEXP_ERROR;
159
160 When OTHERS then
161 x_return_code := FND_API.G_RET_STS_UNEXP_ERROR;
162 FND_MESSAGE.SET_NAME('JTF','JTF_WF_LAUNCH_ERROR');
163 FND_MESSAGE.SET_TOKEN('WF_NAME',p_wf_item_name);
164 FND_MESSAGE.SET_TOKEN('WF_KEY',p_wf_item_key);
165 FND_MSG_PUB.Add;
166
167 End WrkflowLaunch;
168
169
170 /* Procedure to launch message generating workflow */
171
172 Procedure GenMsgWrkflowLaunch(
173 p_Wf_item_name varchar2,
174 p_Wf_item_process_name varchar2,
175 p_Wf_item_key varchar2,
176 p_prod_code varchar2,
177 p_bus_obj_code varchar2,
178 p_bus_obj_name varchar2,
179 p_action_code varchar2,
180 p_correlation varchar2,
181 p_bind_data_id Number,
182 p_OAI_param varchar2,
183 p_OAI_array JTF_USR_HKS.OAI_data_array_type,
184 x_return_code Out NOCOPY varchar2
185 ) is
186
187 l_bind_data_id Number := p_bind_data_id;
188
189 Cursor ATT_BIND_DATA is
190 Select bind_name, bind_value, data_type
191 From JTF_BIND_DATA
192 Where bind_data_id = l_bind_data_id And
193 bind_type = 'W';
194
195 l_wf_item_exists Boolean := FALSE;
196 l_owner_name Varchar2(100);
197
198 Begin
199
200
201 -- Check for existence of same workflow instance.
202 l_wf_item_exists := wf_item.item_exist( itemtype => p_wf_item_name,
203 itemkey => p_wf_item_key );
204 if ( l_wf_item_exists ) then
205 FND_MESSAGE.SET_NAME('JTF','JTF_WF_ALREADY_EXISTS');
206 FND_MESSAGE.SET_TOKEN('WF_NAME',p_wf_item_name);
207 FND_MESSAGE.SET_TOKEN('WF_KEY',p_wf_item_key);
208 FND_MSG_PUB.Add;
209 RAISE FND_API.G_EXC_ERROR;
210 else
211 -- Create workflow process.
212 wf_engine.CreateProcess( itemType => p_Wf_item_name,
213 itemKey => p_Wf_item_key,
214 process => p_Wf_item_process_name
215 );
216
217 -- Set workflow instance owner
218
219 l_owner_name := FND_GLOBAL.USER_NAME;
220
221 wf_engine.SetItemOwner( itemtype => p_wf_item_name,
222 itemkey => p_wf_item_key,
223 owner => l_owner_name );
224
225 -- Set Workflow item attributes
226
227 FOR wf_att IN att_bind_data LOOP
228
229 IF ( wf_att.data_type = 'T' ) then
230 wf_engine.setitemattrText( itemtype => p_wf_item_name,
231 itemkey => p_wf_item_key,
232 aname => wf_att.bind_name,
233 avalue => wf_att.bind_value );
234
235 ELSIF ( wf_att.data_type = 'N' ) then
236 wf_engine.setitemattrNumber( itemtype => p_wf_item_name,
237 itemkey => p_wf_item_key,
238 aname => wf_att.bind_name,
239 avalue => wf_att.bind_value );
240
241 ELSIF ( wf_att.data_type = 'D' ) then
242 wf_engine.setitemattrDate( itemtype => p_wf_item_name,
243 itemkey => p_wf_item_key,
244 aname => wf_att.bind_name,
245 avalue => to_date(wf_att.bind_value,'YYYY/MM/DD') );
246
247 END IF;
248 END LOOP;
249
250 wf_engine.setitemattrNumber( itemtype => p_wf_item_name,
251 itemkey => p_wf_item_key,
252 aname => 'BIND_DATA_ID',
253 avalue => p_bind_data_id );
254
255 wf_engine.setitemattrText( itemtype => p_wf_item_name,
256 itemkey => p_wf_item_key,
257 aname => 'PRODUCT_CODE',
258 avalue => p_prod_code );
259
260 wf_engine.setitemattrText( itemtype => p_wf_item_name,
261 itemkey => p_wf_item_key,
262 aname => 'BUS_OBJ_CODE',
263 avalue => p_bus_obj_code);
264
265 wf_engine.setitemattrText( itemtype => p_wf_item_name,
266 itemkey => p_wf_item_key,
267 aname => 'BUS_OBJ_NAME',
268 avalue => p_bus_obj_name);
269
270 wf_engine.setitemattrText( itemtype => p_wf_item_name,
271 itemkey => p_wf_item_key,
272 aname => 'ACTION_CODE',
273 avalue => p_action_code);
274
275 wf_engine.setitemattrText( itemtype => p_wf_item_name,
276 itemkey => p_wf_item_key,
277 aname => 'CORRELATION',
278 avalue => p_correlation);
279 -- start workflow process
280 wf_engine.StartProcess( itemType => p_wf_item_name,
281 itemKey => p_wf_item_key
282 );
283
284 FND_MESSAGE.SET_NAME('JTF','JTF_WF_LAUNCH_SUCCESS');
285 FND_MESSAGE.SET_TOKEN('WF_NAME',p_wf_item_name);
286 FND_MESSAGE.SET_TOKEN('WF_KEY',p_wf_item_key);
287 FND_MSG_PUB.Add;
288
289 -- Purge Bind data table
290 JTF_USR_HKS.Purge_Bind_Data( p_bind_data_id => l_bind_data_id,
291 p_bind_type => 'W'
292 );
293
294 x_return_code := FND_API.G_RET_STS_SUCCESS;
295
296 end if;
297 Exception
298 When FND_API.G_EXC_ERROR then
299 x_return_code := FND_API.G_RET_STS_ERROR;
300
301 When FND_API.G_EXC_UNEXPECTED_ERROR then
302 x_return_code := FND_API.G_RET_STS_UNEXP_ERROR;
303
304 When OTHERS then
305 x_return_code := FND_API.G_RET_STS_UNEXP_ERROR;
306 FND_MESSAGE.SET_NAME('JTF','JTF_WF_LAUNCH_ERROR');
307 FND_MESSAGE.SET_TOKEN('WF_NAME',p_wf_item_name);
308 FND_MESSAGE.SET_TOKEN('WF_KEY',p_wf_item_key);
309 FND_MSG_PUB.Add;
310
311 End GenMsgWrkflowLaunch;
312
313
314 /* This procedure starts an autonomous transaction for commit a msg in queue */
315 procedure Queue_Sync_Msg( p_prod_code varchar2,
316 p_bus_obj_code varchar2,
317 p_bus_obj_name varchar2,
318 p_correlation varchar2,
319 p_msg_XML clob ) is
320
321 PRAGMA AUTONOMOUS_TRANSACTION;
322 Begin
323 JTF_Message.Queue_Message(
324 p_prod_code => p_prod_code,
325 p_bus_obj_code => p_bus_obj_code,
326 p_bus_obj_name => p_bus_obj_name,
327 p_correlation => p_correlation,
328 p_message => p_msg_XML );
329 Commit;
330
331 End Queue_Sync_Msg ;
332
333
334 /* Procedure to Genearate message for publishing only */
335 Procedure Generate_message(
336 p_prod_code varchar2,
337 p_bus_obj_code varchar2,
338 p_bus_obj_name varchar2 ,
339 p_action_code varchar2,
340 p_correlation varchar2,
341 p_bind_data_id number,
342 p_OAI_param varchar2,
343 p_OAI_array JTF_USR_HKS.OAI_data_array_type,
344 x_return_code Out NOCOPY varchar2
345 ) is
346 l_msg_SQL CLOB;
347 l_msg_mode number;
348 l_msg_type varchar2(20):= 'PUBLISH';
349 l_bus_obj_name varchar2(50);
350
351 Begin
352
353
354 Begin
355 select bus_obj_sql, nvl(msg_mode,1) , bus_obj_name
356 into l_msg_SQL, l_msg_mode, l_bus_obj_name
357 from JTF_MESSAGE_OBJECTS
358 where
359 PRODUCT_CODE = p_prod_code and
360 BUS_OBJ_CODE = p_bus_obj_code and
361 ACTION_CODE = p_action_code and
362 ACTIVE_FLAG = 'Y';
363 Exception
364 When NO_DATA_FOUND then
365 FND_MESSAGE.SET_NAME('JTF','JTF_NO_BUS_OBJECT');
366 FND_MESSAGE.SET_TOKEN('BO_CODE',p_bus_obj_code);
367 FND_MSG_PUB.ADD;
368 RAISE FND_API.G_EXC_ERROR;
369 When OTHERS then
370 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
371 End;
372 -- dbms_output.put_line(' Got message mode');
373
374 If ( l_msg_mode = 1 ) then /* Online Messaging */
375
376 Publish_message(
377 p_prod_code => p_prod_code ,
378 p_bus_obj_code => p_bus_obj_code,
379 p_bus_obj_name => p_bus_obj_name,
380 p_action_code => p_action_code ,
381 p_correlation => p_correlation ,
382 p_bind_data_id => p_bind_data_id,
383 p_msg_type => l_msg_type
384 );
385
386 Elsif ( l_msg_mode = 2 ) then /* off line messaging */
387
388 Stage_Message(
389 p_prod_code => p_prod_code ,
390 p_bus_obj_code => p_bus_obj_code,
391 p_action_code => p_action_code ,
392 p_correlation => p_correlation ,
393 p_bind_data_id => p_bind_data_id
394 );
395
396 Else
397
398 FND_MESSAGE.SET_NAME('JTF','JTF_INVALID_MSG_MODE');
399 FND_MESSAGE.SET_TOKEN('MSG_MODE', to_char(l_msg_mode) );
400 FND_MSG_PUB.ADD;
401 RAISE FND_API.G_EXC_ERROR;
402 End If;
403
404 x_return_code := FND_API.G_RET_STS_SUCCESS;
405
406 Exception
407 When FND_API.G_EXC_ERROR then
408 x_return_code := FND_API.G_RET_STS_ERROR;
409 When OTHERS then
410 x_return_code := FND_API.G_RET_STS_UNEXP_ERROR;
411 FND_MESSAGE.SET_NAME('JTF','JTF_MSG_GEN_ERROR');
412 FND_MESSAGE.SET_TOKEN('PROD_CODE',p_prod_code);
413 FND_MESSAGE.SET_TOKEN('BO_CODE',p_bus_obj_code);
414 FND_MSG_PUB.ADD;
415
416 End Generate_Message;
417
418 /* For sending Reply type message */
419 Procedure Generate_message(
420 p_prod_code varchar2,
421 p_bus_obj_code varchar2,
422 p_bus_obj_name varchar2 ,
423 p_action_code varchar2,
424 p_correlation varchar2,
425 p_bind_data_id number,
426 p_ref_sender varchar2,
427 p_ref_msg_id number,
428 p_OAI_param varchar2,
429 p_OAI_array JTF_USR_HKS.OAI_data_array_type,
430 x_return_code Out NOCOPY varchar2
431 ) is
432 l_msg_SQL CLOB;
433 l_msg_mode number;
434 l_msg_type varchar2(20):= 'REPLY';
435 l_bus_obj_name varchar2(20);
436
437 Begin
438
439
440 Begin
441 select bus_obj_sql ,nvl(msg_mode,1) , bus_obj_name
442 into l_msg_SQL, l_msg_mode, l_bus_obj_name
443 from JTF_MESSAGE_OBJECTS
444 where
445 PRODUCT_CODE = p_prod_code and
446 BUS_OBJ_CODE = p_bus_obj_code and
447 ACTION_CODE = p_action_code and
448 ACTIVE_FLAG = 'Y';
449 Exception
450 When NO_DATA_FOUND then
451 FND_MESSAGE.SET_NAME('JTF','JTF_NO_BUS_OBJECT');
452 FND_MESSAGE.SET_TOKEN('BO_CODE',p_bus_obj_code);
453 FND_MSG_PUB.ADD;
454 RAISE FND_API.G_EXC_ERROR;
455 When OTHERS then
456 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
457 End;
458 -- dbms_output.put_line(' Got message mode');
459
460 Publish_message(
461 p_prod_code => p_prod_code ,
462 p_bus_obj_code => p_bus_obj_code,
463 p_bus_obj_name => p_bus_obj_name,
464 p_action_code => p_action_code ,
465 p_correlation => p_correlation ,
466 p_bind_data_id => p_bind_data_id,
467 p_msg_type => l_msg_type,
468 p_ref_sender => p_ref_sender,
469 p_ref_msg_id => p_ref_msg_id
470 );
471
472 x_return_code := FND_API.G_RET_STS_SUCCESS;
473
474 Exception
475 When FND_API.G_EXC_ERROR then
476 x_return_code := FND_API.G_RET_STS_ERROR;
477 When OTHERS then
478 x_return_code := FND_API.G_RET_STS_UNEXP_ERROR;
479 FND_MESSAGE.SET_NAME('JTF','JTF_MSG_GEN_ERROR');
480 FND_MESSAGE.SET_TOKEN('PROD_CODE',p_prod_code);
481 FND_MESSAGE.SET_TOKEN('BO_CODE',p_bus_obj_code);
482 FND_MSG_PUB.ADD;
483
484 End Generate_Message;
485
486 /* For sync/Async request/reply message */
487 Procedure Generate_message(
488 p_prod_code varchar2,
489 p_bus_obj_code varchar2,
490 p_bus_obj_name varchar2 ,
491 p_action_code varchar2,
492 p_correlation varchar2,
493 p_bind_data_id number,
494 p_timeout number,
495 p_OAI_param varchar2,
496 p_OAI_array JTF_USR_HKS.OAI_data_array_type,
497 x_msg_id Out NOCOPY number,
498 x_reply_msg Out NOCOPY CLOB,
499 x_return_code Out NOCOPY varchar2
500 ) is
501 l_msg_SQL CLOB;
502 l_msg_mode number;
503 l_msg_type varchar2(20);
504 l_bus_obj_name varchar2(20);
505 l_wait_time number;
506 l_alert_name varchar2(100);
507 l_alert_message varchar2(1);
508 l_alert_status number;
509
510 Begin
511
512
513 Begin
514 select bus_obj_sql ,nvl(msg_mode,1) , bus_obj_name
515 into l_msg_SQL, l_msg_mode, l_bus_obj_name
516 from JTF_MESSAGE_OBJECTS
517 where
518 PRODUCT_CODE = p_prod_code and
519 BUS_OBJ_CODE = p_bus_obj_code and
520 ACTION_CODE = p_action_code and
521 ACTIVE_FLAG = 'Y';
522 Exception
523 When NO_DATA_FOUND then
524 FND_MESSAGE.SET_NAME('JTF','JTF_NO_BUS_OBJECT');
525 FND_MESSAGE.SET_TOKEN('BO_CODE',p_bus_obj_code);
526 FND_MSG_PUB.ADD;
527 RAISE FND_API.G_EXC_ERROR;
528 When OTHERS then
529 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
530 End;
531
532 select jtf_msg_id_s.nextval
533 into x_msg_id
534 from dual;
535
536 if (p_timeout = 0) then
537 l_msg_type := 'ASYNC_REQUEST';
538 l_wait_time := 0;
539 elsif (p_timeout < 0) then
540 l_msg_type := 'SYNC_REQUEST';
541 l_wait_time := DBMS_ALERT.MAXWAIT;
542 else
543 l_msg_type := 'SYNC_REQUEST';
544 l_wait_time := p_timeout;
545 end if;
546
547
548 Publish_message(
549 p_prod_code => p_prod_code ,
550 p_bus_obj_code => p_bus_obj_code,
551 p_bus_obj_name => p_bus_obj_name,
552 p_action_code => p_action_code ,
553 p_correlation => p_correlation ,
554 p_bind_data_id => p_bind_data_id,
555 p_msg_type => l_msg_type,
556 p_ref_msg_id => x_msg_id,
557 p_timeout => p_timeout
558 );
559
560 if (p_timeout <> 0) then
561 l_alert_name := 'JTF' || x_msg_id;
562
563 DBMS_ALERT.REGISTER(l_alert_name);
564 DBMS_ALERT.WAITONE(
565 l_alert_name,
566 l_alert_message,
567 l_alert_status,
568 l_wait_time
569 );
570
571 DBMS_ALERT.REMOVE(l_alert_name);
572
573 if (l_alert_status = 0) then
574
575 delete from JTF_SYNC_REPLY_MSG
576 where msg_id = x_msg_id
577 returning reply_msg into x_reply_msg;
578
579 x_return_code := FND_API.G_RET_STS_SUCCESS;
580 else
581 x_return_code := FND_API.G_RET_STS_ERROR;
582 end if;
583
584 else
585 x_return_code := FND_API.G_RET_STS_SUCCESS;
586 end if;
587
588 Exception
589 When FND_API.G_EXC_ERROR then
590 x_return_code := FND_API.G_RET_STS_ERROR;
591 When OTHERS then
592 x_return_code := FND_API.G_RET_STS_UNEXP_ERROR;
593 FND_MESSAGE.SET_NAME('JTF','JTF_MSG_GEN_ERROR');
594 FND_MESSAGE.SET_TOKEN('PROD_CODE',p_prod_code);
595 FND_MESSAGE.SET_TOKEN('BO_CODE',p_bus_obj_code);
596 FND_MSG_PUB.ADD;
597
598 End Generate_Message;
599
600
601 /* For sending pre-generated XML message */
602 Procedure Generate_message(
603 p_prod_code varchar2,
604 p_bus_obj_code varchar2,
605 p_bus_obj_name varchar2,
606 p_correlation varchar2,
607 p_timeout number,
608 p_message CLOB,
609 p_msg_type varchar2,
610 x_msg_id Out NOCOPY number,
611 x_reply_msg Out NOCOPY CLOB,
612 x_return_code Out NOCOPY varchar2
613 ) is
614 l_wait_time number;
615 l_alert_name varchar2(100);
616 l_alert_message varchar2(1);
617 l_alert_status number;
618
619 Begin
620
621
622 if ( p_msg_type = 'R' ) then
623
624 select jtf_msg_id_s.nextval
625 into x_msg_id
626 from dual;
627
628 if (p_timeout = 0) then
629 l_wait_time := 0;
630 elsif (p_timeout < 0) then
631 l_wait_time := DBMS_ALERT.MAXWAIT;
632 else
633 l_wait_time := p_timeout;
634 end if;
635 end if;
636
637 if ( ( p_msg_type = 'P') OR ( p_timeout = 0 ) ) then
638
639 JTF_Message.Queue_Message( p_prod_code => p_prod_code,
640 p_bus_obj_code => p_bus_obj_code,
641 p_bus_obj_name => p_bus_obj_name,
642 p_correlation => p_correlation,
643 p_message => p_message );
644 else
645 /* To take care of sync req/reply scenario */
646 Queue_Sync_Msg(
647 p_prod_code => p_prod_code,
648 p_bus_obj_code => p_bus_obj_code,
649 p_bus_obj_name => p_bus_obj_name,
650 p_correlation => p_correlation,
651 p_msg_XML => p_message );
652 end if;
653
654 if (( p_msg_type = 'R') AND ( p_timeout <> 0)) then
655
656 l_alert_name := 'JTF' || x_msg_id;
657
658 DBMS_ALERT.REGISTER(l_alert_name);
659 DBMS_ALERT.WAITONE(
660 l_alert_name,
661 l_alert_message,
662 l_alert_status,
663 l_wait_time
664 );
665
666 DBMS_ALERT.REMOVE(l_alert_name);
667
668 if (l_alert_status = 0) then
669
670 delete from JTF_SYNC_REPLY_MSG
671 where msg_id = x_msg_id
672 returning reply_msg into x_reply_msg;
673
674 x_return_code := FND_API.G_RET_STS_SUCCESS;
675 else
676 x_return_code := FND_API.G_RET_STS_ERROR;
677 end if;
678
679 else
680 x_return_code := FND_API.G_RET_STS_SUCCESS;
681 end if;
682
683 Exception
684 When FND_API.G_EXC_ERROR then
685 x_return_code := FND_API.G_RET_STS_ERROR;
686 When OTHERS then
687 x_return_code := FND_API.G_RET_STS_UNEXP_ERROR;
688 FND_MESSAGE.SET_NAME('JTF','JTF_MSG_GEN_ERROR');
689 FND_MESSAGE.SET_TOKEN('PROD_CODE',p_prod_code);
690 FND_MESSAGE.SET_TOKEN('BO_CODE',p_bus_obj_code);
691 FND_MSG_PUB.ADD;
692
693 End Generate_Message;
694
695 /* Function to return Bind data Id */
696
697 Function Get_Bind_Data_Id Return Number is
698
699 l_db_id Number;
700 Begin
701 l_db_id := JTF_USR_HKS.Get_Bus_Obj_Id;
702
703 return(l_db_id);
704
705 End Get_Bind_Data_Id;
706
707
708
709 /* Procedure Load_Bind_data will load the bind data to
710 JTF_BIND_DATA table
711 */
712
713 Procedure Load_Bind_Data( p_bind_data_id Number,
714 p_bind_name Varchar2,
715 p_bind_value Varchar2,
716 p_bind_type Varchar2,
717 p_data_type Varchar2
718 ) is
719 Begin
720
721 Insert into JTF_BIND_DATA(
722 bind_data_id,
723 bind_name,
724 bind_value,
725 bind_type,
726 data_type )
727 Values (
728 p_bind_data_id,
729 p_bind_name,
730 p_bind_value,
731 p_bind_type,
732 p_data_type );
733 End Load_Bind_Data;
734
735
736 /* Procedure to purge bind data table */
737
738 Procedure Purge_Bind_Data( p_Bind_Data_Id Number,
739 p_bind_type Varchar2 ) is
740
741 Begin
742 Delete from JTF_BIND_DATA
743 Where BIND_DATA_ID = p_Bind_Data_Id And
744 BIND_TYPE = p_bind_type ;
745 Exception
746 When NO_DATA_FOUND then
747 null;
748
749 End Purge_Bind_Data;
750
751
752 /* Function to return user hook Id */
753
754 Function Get_User_Hook_Id Return Number is
755
756 l_hk_id Number;
757 Begin
758 Select JTF_USER_HOOKS_S.NEXTVAL
759 into l_hk_id
760 from Dual;
761
762 return(l_hk_id);
763
764 End Get_User_Hook_Id;
765
766
767 /* Function to return Bus Obj Id */
768
769 Function Get_Bus_Obj_Id Return Number is
770
771 l_bo_id Number;
772 Begin
773 Select JTF_MSG_OBJ_S.NEXTVAL
774 into l_bo_id
775 from Dual;
776
777 return(l_bo_id);
778
779 End Get_Bus_Obj_Id;
780
781
782 procedure Generate_Hdrxml(
783 p_prodcode IN varchar2,
784 p_bo_code IN varchar2,
785 p_noun IN varchar2 ,
786 p_verb IN varchar2 ,
787 p_type IN varchar2 ,
788 p_sender IN varchar2 ,
789 p_msg_id IN varchar2 ,
790 x_hdrxml OUT NOCOPY varchar2 ) Is
791
792 l_hdrxml varchar2(8000) ;
793 l_line varchar2(1000);
794 l_newline varchar2(20) := fnd_global.newline;
795
796 BEGIN
797 -- dbms_output.enable(20000);
798 l_line := '<CNTRLAREA>';
799 l_hdrxml := l_hdrxml||l_line||l_newline;
800
801 l_line := lpad(' ',4)||'<prodcode>'||ltrim(rtrim(p_prodcode))||'</prodcode>';
802 l_hdrxml := l_hdrxml||l_line||l_newline;
803
804 l_line := lpad(' ',4)||'<bocode>'||ltrim(rtrim(p_bo_code))||'</bocode>';
805 l_hdrxml := l_hdrxml||l_line||l_newline;
806
807 l_line := lpad(' ',4)||'<verb>'||ltrim(rtrim(p_verb))||'</verb>';
808 l_hdrxml := l_hdrxml||l_line||l_newline;
809
810 if ( p_noun <> FND_API.G_MISS_CHAR ) then
811 l_line := lpad(' ',4)||'<noun>'||ltrim(rtrim(p_noun))||'</noun>';
812 l_hdrxml := l_hdrxml||l_line||l_newline;
813 end if;
814
815 if ( p_type <> FND_API.G_MISS_CHAR ) then
816 l_line := lpad(' ',4)||'<type>'||ltrim(rtrim(p_type))||'</type>';
817 l_hdrxml := l_hdrxml||l_line||l_newline;
818 end if;
819
820 if ( p_sender <> FND_API.G_MISS_CHAR ) then
821 l_line := lpad(' ',4)||'<sender>'||ltrim(rtrim(p_sender))||'</sender>';
822 l_hdrxml := l_hdrxml||l_line||l_newline;
823 end if;
824
825 if ( p_msg_id <> FND_API.G_MISS_CHAR ) then
826 l_line := '<msg_id>'||ltrim(rtrim(p_msg_id))||'</msg_id>';
827 l_hdrxml := l_hdrxml||l_line||l_newline;
828 end if;
829
830 l_line := '</CNTRLAREA>';
831 l_hdrxml := l_hdrxml||l_line;
832
833 -- dbms_output.put_line(l_hdrxml );
834
835 x_hdrxml := l_hdrxml;
836 End Generate_Hdrxml;
837
838
839 /* Procedure to publish message */
840 Procedure Publish_message(
841 p_prod_code varchar2,
842 p_bus_obj_code varchar2,
843 p_bus_obj_name varchar2,
844 p_action_code varchar2,
845 p_correlation varchar2,
846 p_bind_data_id number ,
847 p_msg_type varchar2,
848 p_ref_sender Varchar2,
849 p_ref_msg_id Number,
850 p_timeout Number
851 ) is
852
853 l_hdrXML_len Number;
854 l_hdrXML_str Varchar2(9000);
855 l_msg_XML CLOB;
856 l_msg_SQL CLOB;
857 l_hdr_DTD CLOB;
858 l_hdr_XML CLOB;
859 l_msg_mode number;
860 l_bus_obj_name varchar2(50);
861
862 amount number := 240;
863 position number := 1;
864 charstr varchar2(255);
865 queryCtx DBMS_XMLquery.ctxType;
866
867 Cursor SQL_BIND_DATA(v_bind_data_id Number) is
868 Select bind_name, bind_value
869 From JTF_BIND_DATA
870 Where bind_data_id = v_bind_data_id And
871 bind_type = 'S';
872
873 Begin
874
875
876 Begin
877 select bus_obj_sql, nvl(msg_mode,1), bus_obj_name
878 into l_msg_SQL, l_msg_mode, l_bus_obj_name
879 from JTF_MESSAGE_OBJECTS
880 where
881 PRODUCT_CODE = p_prod_code and
882 BUS_OBJ_CODE = p_bus_obj_code and
883 ACTION_CODE = p_action_code and
884 ACTIVE_FLAG = 'Y';
885 Exception
886 When NO_DATA_FOUND then
887 FND_MESSAGE.SET_NAME('JTF','JTF_NO_BUS_OBJECT');
888 FND_MESSAGE.SET_TOKEN('BO_CODE',p_bus_obj_code);
889 FND_MSG_PUB.ADD;
890 RAISE FND_API.G_EXC_ERROR;
891 When OTHERS then
892 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
893 End;
894 -- dbms_output.put_line(' Got message sql');
895 -- set up the query context...!
896 queryCtx := DBMS_XMLQuery.newContext(l_msg_SQL);
897 Begin
898 select HEADER_DTD into l_hdr_DTD
899 from JTF_HEADER_DTD
900 where
901 ACTIVE_FLAG = 'Y';
902 Exception
903 When NO_DATA_FOUND then
904 FND_MESSAGE.SET_NAME('JTF','JTF_NO_HDR_DTD');
905 FND_MSG_PUB.ADD;
906 RAISE FND_API.G_EXC_ERROR;
907 When OTHERS then
908 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
909 End;
910
911 -- dbms_output.put_line(' Got header dtd');
912
913 Generate_Hdrxml( p_prodcode => p_prod_code,
914 p_bo_code => p_bus_obj_code ,
915 p_noun => l_bus_obj_name ,
916 p_verb => p_action_code ,
917 p_type => p_msg_type,
918 p_sender => p_ref_sender,
919 p_msg_id => p_ref_msg_id,
920 x_hdrxml => l_hdrXML_str ) ;
921
922 -- dbms_output.put_line(' generate header xml :' ||chr(10)||l_hdrXML_str );
923
924 l_hdrXML_len := length(l_hdrXML_str);
925 -- dbms_output.put_line(' getting length');
926 dbms_lob.createtemporary( l_hdr_XML, true, dbms_lob.call);
927 -- dbms_output.put_line(' creating temp lob');
928 dbms_lob.write( l_hdr_XML, l_hdrXML_len, 1, l_hdrXML_str);
929 -- dbms_output.put_line(' after writting to lob');
930
931 /* Set bind variables */
932
933
934 --DBMS_XMLQUERY.clearBindValues;
935
936 FOR sql_bind IN sql_bind_data(p_bind_data_id) LOOP
937 DBMS_XMLQUERY.setBindValue( queryCtx,sql_bind.bind_name, sql_bind.bind_value );
938 END LOOP;
939
940 DBMS_XMLQUERY.setRowSetTag( ctxHdl => queryCtx, Tag => 'DATAAREA');
941 DBMS_XMLQUERY.setMetaHeader( ctxHdl => queryCtx, Header => l_hdr_DTD);
942 DBMS_XMLQUERY.setDataHeader( ctxHdl => queryCtx, Header => l_hdr_XML,
943 Tag => 'BUS_OBJ');
944
945
946 /*
947 begin
948 loop
949 dbms_lob.read(l_msg_SQL,amount,position,charstr);
950 dbms_output.put_line(charstr);
951 position := position + amount;
952 end loop;
953 exception
954 when NO_DATA_FOUND then
955 null;
956 end;
957
958 dbms_lob.createtemporary( l_msg_SQL_lob, true, dbms_lob.call);
959 l_msg_SQL_len := dbms_lob.getlength(l_msg_SQL);
960 dbms_lob.copy( l_msg_SQL_lob, l_msg_SQL, l_msg_SQL_len, 1, 1);
961 */
962
963
964 l_msg_XML := DBMS_XMLQUERY.getXML ( ctxHdl => queryCtx,
965 metatype => DBMS_XMLQUERY.DTD);
966
967
968 /*
969 position := 1;
970 begin
971 loop
972 dbms_lob.read(l_msg_XML,amount,position,charstr);
973 dbms_output.put_line(charstr);
974 position := position + amount;
975 end loop;
976 exception
977 when NO_DATA_FOUND then
978 null;
979 end;
980 */
981
982 -- dbms_output.put_line(' before queue message');
983
984 /* JTF_Message.Queue_Message routine enqueues the message in message queue */
985
986 if ( p_timeout = 0 ) then
987
988 JTF_Message.Queue_Message( p_prod_code => p_prod_code,
989 p_bus_obj_code => p_bus_obj_code,
990 p_bus_obj_name => p_bus_obj_name,
991 p_correlation => p_correlation,
992 p_message => l_msg_XML );
993 else
994 /* To take care of sync req/reply scenario */
995 Queue_Sync_Msg(
996 p_prod_code => p_prod_code,
997 p_bus_obj_code => p_bus_obj_code,
998 p_bus_obj_name => p_bus_obj_name,
999 p_correlation => p_correlation,
1000 p_msg_XML => l_msg_XML );
1001 end if;
1002 -- dbms_output.put_line(' after queue message');
1003
1004 /* Purge Bind data table */
1005 JTF_USR_HKS.Purge_Bind_Data( p_bind_data_id => p_bind_data_id,
1006 p_bind_type => 'S'
1007 );
1008
1009 End Publish_Message;
1010
1011
1012 Procedure get_prod_info( p_apps_short_name varchar2,
1013 x_schema Out NOCOPY varchar2 ) IS
1014 l_schema varchar2(30);
1015 l_status varchar2(1);
1016 l_industry varchar2(1);
1017 begin
1018 if ( FND_INSTALLATION.get_app_info( p_apps_short_name, l_status, l_industry,
1019 l_schema ) ) then
1020 x_schema := l_schema;
1021 else
1022 raise_application_error(-20000, 'Failed to get Info for Product'||
1023 p_apps_short_name );
1024 end if;
1025 end get_prod_info;
1026
1027
1028 Procedure Stage_Message(
1029 p_prod_code Varchar2,
1030 p_bus_obj_code Varchar2,
1031 p_action_code Varchar2,
1032 p_correlation Varchar2,
1033 p_bind_data_id Number
1034 ) IS
1035
1036 l_stage_obj SYSTEM.JTF_STAGING_MSG_OBJ :=
1037 SYSTEM.JTF_STAGING_MSG_OBJ( null,null,
1038 null,null,
1039 0,null );
1040 l_enqueue_options dbms_aq.enqueue_options_t;
1041 l_message_properties dbms_aq.message_properties_t;
1042 l_Qname Varchar2(55) := 'JTF_STAGING_MSG_QUEUE';
1043 l_msg_id RAW(16);
1044 l_schema Varchar2(30);
1045
1046 Begin
1047
1048 get_prod_info( 'JTF', l_schema);
1049
1050 l_Qname := l_schema||'.'||l_Qname;
1051
1052 l_stage_obj.prod_code := p_prod_code;
1053 l_stage_obj.bus_obj_code := p_bus_obj_code;
1054 l_stage_obj.action_code := p_action_code;
1055 l_stage_obj.correlation := p_correlation;
1056 l_stage_obj.bind_data_id := p_bind_data_id;
1057
1058 dbms_aq.enqueue( queue_name => l_Qname ,
1059 enqueue_options => l_enqueue_options ,
1060 message_properties => l_message_properties ,
1061 payload => l_stage_obj ,
1062 msgid => l_msg_id );
1063 Exception
1064 When OTHERS then
1065 JTF_USR_HKS.Handle_msg_Excep(
1066 p_prod_code => p_prod_code,
1067 p_bus_obj_code => p_bus_obj_code ,
1068 p_action_code => p_action_code ,
1069 p_correlation => p_correlation ,
1070 p_bind_data_id => p_bind_data_id ,
1071 p_msg_type => 'O' ,
1072 p_err_msg => 'Error in writting to stage Queue' );
1073 End Stage_Message;
1074
1075
1076 Procedure Handle_msg_Excep(
1077 p_prod_code Varchar2,
1078 p_bus_obj_code Varchar2,
1079 p_action_code Varchar2,
1080 p_correlation Varchar2,
1081 p_bind_data_id Number,
1082 p_msg_type Varchar2,
1083 p_err_msg Varchar2 ) Is
1084
1085 l_excep_obj SYSTEM.JTF_EXCEP_MSG_OBJ :=
1086 SYSTEM.JTF_EXCEP_MSG_OBJ( null,null,
1087 null,null,
1088 0,null,null );
1089 l_enqueue_options dbms_aq.enqueue_options_t;
1090 l_message_properties dbms_aq.message_properties_t;
1091 l_Qname Varchar2(55) := 'JTF_EXCEP_MSG_QUEUE';
1092 l_msg_id RAW(16);
1093 l_schema varchar2(30);
1094 Begin
1095
1096
1097 get_prod_info( 'JTF', l_schema);
1098
1099 l_Qname := l_schema||'.'||l_Qname;
1100
1101 l_excep_obj.prod_code := p_prod_code;
1102 l_excep_obj.bus_obj_code := p_bus_obj_code;
1103 l_excep_obj.action_code := p_action_code;
1104 l_excep_obj.correlation := p_correlation;
1105 l_excep_obj.bind_data_id := p_bind_data_id;
1106 l_excep_obj.msg_type := p_msg_type;
1107 l_excep_obj.err_msg := p_err_msg;
1108
1109 l_message_properties.correlation := p_correlation;
1110
1111 dbms_aq.enqueue( queue_name => l_Qname ,
1112 enqueue_options => l_enqueue_options ,
1113 message_properties => l_message_properties ,
1114 payload => l_excep_obj ,
1115 msgid => l_msg_id );
1116
1117 End Handle_msg_Excep;
1118
1119
1120 END jtf_usr_hks;