1 PACKAGE BODY amv_aq_utility_pvt AS
2 /* $Header: amvvaqub.pls 120.1 2005/06/21 17:43:17 appldev ship $ */
3 --
4 -- NAME
5 -- AMV_AQ_UTILITY_PVT
6 --
7 -- HISTORY
8 -- 09/17/1999 PWU CREATED
9 -- 05/10/2000 SVATSA UPDATED
10 -- Updated the Dequeue_Message API, to set the dequeue wait option to NO_WAIT.
11 --
12 --
13 --------------------------------------------------------------------------------
14 --------------------------------------------------------------------------------
15 ---- Package Body Variables and Cursors ----
16 --------------------------------------------------------------------------------
17 G_PKG_NAME CONSTANT VARCHAR2(30):='AMV_AQ_UTILITY_PVT';
18 G_FILE_NAME CONSTANT VARCHAR2(12):='amvvaqub.pls';
19
20 CURSOR Get_AQ_table_csr (p_table IN varchar2) IS
21 Select
22 queue_table
23 From user_queue_tables
24 Where queue_table = p_table;
25 --
26 CURSOR Get_AQ_queue_csr (
27 p_queue IN varchar2,
28 p_table IN varchar2
29 ) IS
30 Select
31 name
32 From user_queues
33 Where queue_table = p_table
34 And name = p_queue
35 And queue_type = 'NORMAL_QUEUE';
36 --
37 -- Debug mode
38 g_debug boolean := TRUE;
39 msg_queue_exist number := 0;
40
41 --------------------------------------------------------------------------------
42 --------------------------------------------------------------------------------
43 ---- Private Functions and Procedures Specification ----
44 --------------------------------------------------------------------------------
45 -- Start of comments
46 -- API name : Add_Queue
47 -- Type : Private
48 -- Pre-reqs : None
49 -- Function : Create the AQ table, the AQ itself, and start it.
50 -- Parameters :
51 -- IN : p_queue_table_name VARCHAR2 Optional
52 -- The underline table used by the AQ queue.
53 -- Default 'AMV_MATCHING_QUEUE_TBL'
54 -- This procedure is mainly for MES matching engine.
55 -- However, we provide it for more general purpose.
56 -- The message object to be enqueued to the AQ queue.
57 -- p_queue_name VARCHAR2 Optional
58 -- The underline table used by the AQ queue.
59 -- Default 'AMV_MATCHING_QUEUE'
60 -- p_payload_obj_type VARCHAR2 Optional
61 -- The name of the payload (DB object) handled by the AQ queue.
62 -- Default 'AMV_AQ_MSG_OBJECT_TYPE'
63 -- OUT : None
64 -- Version : Current version 1.0
65 -- Previous version 1.0
66 -- Initial version 1.0
67 -- Note :
68 -- End of comments
69 --
70 PROCEDURE Add_Queue
71 (
72 p_queue_table_name IN VARCHAR2 := AMV_QUEUE.queue_table_name,
73 p_queue_name IN VARCHAR2 := AMV_QUEUE.queue_name,
74 p_payload_obj_type IN VARCHAR2 := 'SYSTEM.AMV_AQ_MSG_OBJECT_TYPE'
75 );
76 --
77 --------------------------------------------------------------------------------
78 -- Start of comments
79 -- API name : Delete_Queue
80 -- Type : Private
81 -- Pre-reqs : None
82 -- Function : Stop the AQ queue, drop it, and then drop the AQ table.
83 -- Parameters :
84 -- IN : p_queue_table_name VARCHAR2 Optional
85 -- The underline table used by the AQ queue.
86 -- Default 'AMV_MATCHING_QUEUE_TBL'
87 -- This procedure is mainly for MES matching engine.
88 -- However, we provide it for more general purpose.
89 -- The message object to be enqueued to the AQ queue.
90 -- p_queue_name VARCHAR2 Optional
91 -- The underline table used by the AQ queue.
92 -- Default 'AMV_MATCHING_QUEUE'
93 -- OUT : None
94 -- Version : Current version 1.0
95 -- Previous version 1.0
96 -- Initial version 1.0
97 -- Note :
98 -- End of comments
99 PROCEDURE Delete_Queue
100 (
101 p_queue_table_name IN VARCHAR2 := AMV_QUEUE.queue_table_name,
102 p_queue_name IN VARCHAR2 := AMV_QUEUE.queue_name
103 );
104 --
105 --------------------------------------------------------------------------------
106 -- Start of comments
107 -- API name : Enqueue_Message
108 -- Type : Private
109 -- Pre-reqs : None
110 -- Function : Put the message into the AQ.
111 -- Parameters :
112 -- IN : p_message_obj AMV_AQ_MSG_OBJECT_TYPE
113 -- Required
114 -- The message object to be enqueued to the AQ queue.
115 -- OUT : None
116 -- Version : Current version 1.0
117 -- Previous version 1.0
118 -- Initial version 1.0
119 -- Note :
120 -- End of comments
121 --
122 PROCEDURE Enqueue_Message
123 (
124 p_message_obj IN SYSTEM.AMV_AQ_MSG_OBJECT_TYPE
125 );
126 --
127 --------------------------------------------------------------------------------
128 -- Start of comments
129 -- API name : Dequeue_Message
130 -- Type : Group
131 -- Pre-reqs : None
132 -- Function : Get a message from the AQ.
133 -- Parameters :
134 -- IN : p_delete_flag VARCHAR2 Optional
135 -- Default = FND_API.G_TRUE
136 -- Flag for delelting the message from the queue.
137 -- IN : None
138 -- OUT : x_message_obj AMV_AQ_MSG_OBJECT_TYPE
139 -- The message object to be dequeued from the AQ queue.
140 -- Version : Current version 1.0
141 -- Previous version 1.0
142 -- Initial version 1.0
143 -- Note :
144 -- End of comments
145 --
146 PROCEDURE Dequeue_Message
147 (
148 p_delete_flag IN VARCHAR2 := FND_API.G_TRUE,
149 x_message_obj OUT NOCOPY SYSTEM.AMV_AQ_MSG_OBJECT_TYPE
150 );
151 --
152 --------------------------------------------------------------------------------
153 --------------------------------------------------------------------------------
154 ---- Private Functions and Procedures Body ----
155 -- All these private procedures does not have real complete error handling.
156 -- They are supported to be handled by the higher level callers.
157 --------------------------------------------------------------------------------
158 PROCEDURE Add_Queue
159 (
160 p_queue_table_name IN VARCHAR2 := AMV_QUEUE.queue_table_name,
161 p_queue_name IN VARCHAR2 := AMV_QUEUE.queue_name,
162 p_payload_obj_type IN VARCHAR2 := 'SYSTEM.AMV_AQ_MSG_OBJECT_TYPE'
163 ) IS
164 --
165 l_comment varchar2(2000) := '';
166 l_temp_str varchar2(200);
167 BEGIN
168 -- Make sure the table does not exist yet.
169 OPEN Get_AQ_table_csr (p_queue_table_name);
170 Fetch Get_AQ_table_csr INTO l_temp_str;
171 IF (Get_AQ_table_csr%NOTFOUND) THEN
172 CLOSE Get_AQ_table_csr;
173 IF p_queue_table_name = AMV_QUEUE.queue_table_name THEN
174 l_comment :=
175 'This AQ queue table is used by MES matching engine AQ queue.';
176 END IF;
177 --Create priority queue table.
178 dbms_aqadm.create_queue_table (
179 queue_table => p_queue_table_name,
180 comment => l_comment,
181 sort_list => 'PRIORITY,ENQ_TIME',
182 queue_payload_type => p_payload_obj_type);
183 ELSE
184 CLOSE Get_AQ_table_csr;
185 -- The table is already there. No need to re-create it.
186 END IF;
187 --
188 -- Make sure the AQ queue does not exist yet.
189 OPEN Get_AQ_queue_csr (p_queue_name, p_queue_table_name);
190 Fetch Get_AQ_queue_csr INTO l_temp_str;
191 IF (Get_AQ_queue_csr%NOTFOUND) THEN
192 CLOSE Get_AQ_queue_csr;
193 IF p_queue_name = AMV_QUEUE.queue_name THEN
194 l_comment :=
195 'This AQ queue is used by MES matching engine AQ queue.';
196 END IF;
197 --Create the AQ queue.
198 dbms_aqadm.create_queue (
199 queue_name => p_queue_name,
200 queue_table => p_queue_table_name,
201 --compatible => '8.1', -- Comment out: current version not latest.
202 comment => l_comment
203 );
204 ELSE
205 CLOSE Get_AQ_queue_csr;
206 -- The queue is already there. No need to re-create it.
207 END IF;
208 --Start the AQ queue just created.
209 dbms_aqadm.start_queue ( queue_name=>p_queue_name);
210 END Add_Queue;
211 --------------------------------------------------------------------------------
212 PROCEDURE Delete_Queue
213 (
214 p_queue_table_name IN VARCHAR2 := AMV_QUEUE.queue_table_name,
215 p_queue_name IN VARCHAR2 := AMV_QUEUE.queue_name
216 ) IS
217 l_temp_str varchar2(200);
218 BEGIN
219 -- Make sure the AQ queue does exist.
220 OPEN Get_AQ_queue_csr (p_queue_name, p_queue_table_name);
221 Fetch Get_AQ_queue_csr INTO l_temp_str;
222 IF (Get_AQ_queue_csr%FOUND) THEN
223 CLOSE Get_AQ_queue_csr;
224 dbms_aqadm.stop_queue(p_queue_name);
225 dbms_aqadm.drop_queue(p_queue_name);
226 ELSE
227 CLOSE Get_AQ_queue_csr;
228 -- The queue does not exist. No need to drop it.
229 END IF;
230 -- Make sure the table does exist.
231 OPEN Get_AQ_table_csr (p_queue_table_name);
232 Fetch Get_AQ_table_csr INTO l_temp_str;
233 IF (Get_AQ_table_csr%FOUND) THEN
234 CLOSE Get_AQ_table_csr;
235 dbms_aqadm.drop_queue_table(p_queue_table_name);
236 ELSE
237 CLOSE Get_AQ_table_csr;
238 -- The table does not exist. No need to drop it.
239 END IF;
240 --POST CODE PHASE:
241 --The code below will drop all queues (in the table) and the queue table.
242 -- dbms_aqadm.drop_queue_table
243 -- (
244 -- queue_table => p_queue_table_name
245 -- force => TRUE
246 -- );
247 --
248 END Delete_Queue;
249 --
250 --------------------------------------------------------------------------------
251 PROCEDURE Enqueue_Message
252 (
253 p_message_obj IN SYSTEM.AMV_AQ_MSG_OBJECT_TYPE
254 ) IS
255 --
256 l_enqueue_options dbms_aq.enqueue_options_t;
257 l_message_properties dbms_aq.message_properties_t;
258 l_message_enq_id RAW(16);
259 --
260 BEGIN
261 --l_message_properties.priority := 10;
262 --We do use priority for special case.
263 l_message_properties.priority := p_message_obj.priority;
264 dbms_aq.enqueue
265 (
266 queue_name => AMV_QUEUE.queue_name,
267 enqueue_options => l_enqueue_options,
268 message_properties => l_message_properties,
269 payload => p_message_obj,
270 msgid => l_message_enq_id
271 );
272 END Enqueue_Message;
273 --
274 --------------------------------------------------------------------------------
275 PROCEDURE Dequeue_Message
276 (
277 p_delete_flag IN VARCHAR2 := FND_API.G_TRUE,
278 x_message_obj OUT NOCOPY SYSTEM.AMV_AQ_MSG_OBJECT_TYPE
279 ) IS
280 l_dequeue_options dbms_aq.dequeue_options_t;
281 l_message_properties dbms_aq.message_properties_t;
282 l_message_handle RAW(16);
283 BEGIN
284 IF (p_delete_flag = FND_API.G_FALSE) THEN
285 l_dequeue_options.dequeue_mode := DBMS_AQ.BROWSE;
286 END IF;
287 -- Set the waiting option for the Dequeue to No_WAIT
288 l_dequeue_options.wait := DBMS_AQ.NO_WAIT;
289 dbms_aq.dequeue
290 (
291 queue_name => AMV_QUEUE.queue_name,
292 dequeue_options => l_dequeue_options,
293 message_properties => l_message_properties,
294 payload => x_message_obj,
295 msgid => l_message_handle
296 );
297 END Dequeue_Message;
298 --------------------------------------------------------------------------------
299 --------------------------------------------------------------------------------
300 PROCEDURE Add_Queue
301 (
302 p_api_version IN NUMBER,
303 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
304 p_commit IN VARCHAR2 := FND_API.G_FALSE,
305 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
306 x_return_status OUT NOCOPY VARCHAR2,
307 x_msg_count OUT NOCOPY NUMBER,
308 x_msg_data OUT NOCOPY VARCHAR2,
309 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
310 p_queue_table_name IN VARCHAR2 := AMV_QUEUE.queue_table_name,
311 p_queue_name IN VARCHAR2 := AMV_QUEUE.queue_name,
312 p_payload_obj_type IN VARCHAR2 := 'SYSTEM.AMV_AQ_MSG_OBJECT_TYPE'
313 ) IS
314 l_api_name CONSTANT VARCHAR2(30) := 'Add_Queue';
315 l_api_version CONSTANT NUMBER := 1.0;
316 l_resource_id NUMBER := -1;
317 l_current_user_id NUMBER := -1;
318 l_current_login_id NUMBER := -1;
319 l_current_user_status VARCHAR2(80);
320 --
321 l_admin_flag VARCHAR2(1);
322 --
323 BEGIN
324 -- Standard call to check for call compatibility.
325 SAVEPOINT Add_Queue_Pvt;
326 IF NOT FND_API.Compatible_API_Call (
327 l_api_version,
328 p_api_version,
329 l_api_name,
330 G_PKG_NAME) THEN
331 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
332 END IF;
333 --Initialize message list if p_init_msg_list is TRUE.
334 IF FND_API.To_Boolean (p_init_msg_list) THEN
335 FND_MSG_PUB.initialize;
336 END IF;
337 -- Initialize API return status to success
338 x_return_status := FND_API.G_RET_STS_SUCCESS;
339 -- Get the current (login) user id.
340 AMV_UTILITY_PVT.Get_UserInfo(
341 x_resource_id => l_resource_id,
342 x_user_id => l_current_user_id,
343 x_login_id => l_current_login_id,
344 x_user_status => l_current_user_status
345 );
346 IF (p_check_login_user = FND_API.G_TRUE) THEN
347 -- Check if user is login and has the required privilege.
348 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
349 -- User is not login.
350 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
351 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
352 FND_MSG_PUB.Add;
353 END IF;
354 RAISE FND_API.G_EXC_ERROR;
355 END IF;
356 AMV_USER_PVT.Is_Administrator
357 (
358 p_api_version => 1.0,
359 x_return_status => x_return_status,
360 x_msg_count => x_msg_count,
361 x_msg_data => x_msg_data,
362 p_check_login_user => FND_API.G_FALSE,
363 p_resource_id => l_resource_id,
364 x_result_flag => l_admin_flag
365 );
366 IF (l_admin_flag <> FND_API.G_TRUE) THEN
367 -- User is not an administrator.
368 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
369 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_HAVE_PRIVILEGE');
370 FND_MSG_PUB.Add;
371 END IF;
372 RAISE FND_API.G_EXC_ERROR;
373 END IF;
374 END IF;
375 --Call the overloaded version to do the job.
376 Add_Queue
377 (
378 p_queue_table_name => p_queue_table_name,
379 p_queue_name => p_queue_name,
380 p_payload_obj_type => p_payload_obj_type
381 );
382 --Standard check of commit
383 IF FND_API.To_Boolean ( p_commit ) THEN
384 COMMIT WORK;
385 END IF;
386 --Standard call to get message count and if count=1, get the message
390 );
387 FND_MSG_PUB.Count_And_Get (
388 p_count => x_msg_count,
389 p_data => x_msg_data
391 EXCEPTION
392 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
393 ROLLBACK TO Add_Queue_Pvt;
394 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
395 -- Standard call to get message count and if count=1, get the message
396 FND_MSG_PUB.Count_And_Get (
397 p_count => x_msg_count,
398 p_data => x_msg_data
399 );
400 WHEN FND_API.G_EXC_ERROR THEN
401 ROLLBACK TO Add_Queue_Pvt;
402 x_return_status := FND_API.G_RET_STS_ERROR;
403 -- Standard call to get message count and if count=1, get the message
404 FND_MSG_PUB.Count_And_Get (
405 p_count => x_msg_count,
406 p_data => x_msg_data
407 );
408 WHEN OTHERS THEN
409 ROLLBACK TO Add_Queue_Pvt;
410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
411 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
412 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
413 END IF;
414 -- Standard call to get message count and if count=1, get the message
415 FND_MSG_PUB.Count_And_Get (
416 p_count => x_msg_count,
417 p_data => x_msg_data
418 );
419 END Add_Queue;
420 --------------------------------------------------------------------------------
421 PROCEDURE Delete_Queue
422 (
423 p_api_version IN NUMBER,
424 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
425 p_commit IN VARCHAR2 := FND_API.G_FALSE,
426 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
427 x_return_status OUT NOCOPY VARCHAR2,
428 x_msg_count OUT NOCOPY NUMBER,
429 x_msg_data OUT NOCOPY VARCHAR2,
430 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
431 p_queue_table_name IN VARCHAR2 := AMV_QUEUE.queue_table_name,
432 p_queue_name IN VARCHAR2 := AMV_QUEUE.queue_name
433 ) IS
434 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Queue';
435 l_api_version CONSTANT NUMBER := 1.0;
436 l_resource_id NUMBER := -1;
437 l_current_user_id NUMBER := -1;
438 l_current_login_id NUMBER := -1;
439 l_current_user_status VARCHAR2(80);
440 --
441 l_admin_flag VARCHAR2(1);
442 BEGIN
443 -- Standard call to check for call compatibility.
444 SAVEPOINT Delete_Queue_Pvt;
445 IF NOT FND_API.Compatible_API_Call (
446 l_api_version,
447 p_api_version,
448 l_api_name,
449 G_PKG_NAME) THEN
450 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
451 END IF;
452 --Initialize message list if p_init_msg_list is TRUE.
453 IF FND_API.To_Boolean (p_init_msg_list) THEN
454 FND_MSG_PUB.initialize;
455 END IF;
456 -- Initialize API return status to success
457 x_return_status := FND_API.G_RET_STS_SUCCESS;
458 -- Get the current (login) user id.
459 AMV_UTILITY_PVT.Get_UserInfo(
460 x_resource_id => l_resource_id,
461 x_user_id => l_current_user_id,
462 x_login_id => l_current_login_id,
463 x_user_status => l_current_user_status
464 );
465 IF (p_check_login_user = FND_API.G_TRUE) THEN
466 -- Check if user is login and has the required privilege.
467 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
468 -- User is not login.
469 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
470 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
471 FND_MSG_PUB.Add;
472 END IF;
473 RAISE FND_API.G_EXC_ERROR;
474 END IF;
475 AMV_USER_PVT.Is_Administrator
476 (
477 p_api_version => 1.0,
478 x_return_status => x_return_status,
479 x_msg_count => x_msg_count,
480 x_msg_data => x_msg_data,
481 p_check_login_user => FND_API.G_FALSE,
482 p_resource_id => l_resource_id,
483 x_result_flag => l_admin_flag
484 );
485 IF (l_admin_flag <> FND_API.G_TRUE) THEN
486 -- User is not an administrator.
487 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
488 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_HAVE_PRIVILEGE');
489 FND_MSG_PUB.Add;
490 END IF;
491 RAISE FND_API.G_EXC_ERROR;
492 END IF;
493 END IF;
494 --Call the overloaded version to do the job.
495 Delete_Queue
496 (
497 p_queue_table_name => p_queue_table_name,
498 p_queue_name => p_queue_name
499 );
500 --Standard check of commit
501 IF FND_API.To_Boolean ( p_commit ) THEN
502 COMMIT WORK;
503 END IF;
504 --Standard call to get message count and if count=1, get the message
505 FND_MSG_PUB.Count_And_Get (
506 p_count => x_msg_count,
507 p_data => x_msg_data
508 );
509 EXCEPTION
510 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
511 ROLLBACK TO Delete_Queue_Pvt;
512 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
513 -- Standard call to get message count and if count=1, get the message
517 );
514 FND_MSG_PUB.Count_And_Get (
515 p_count => x_msg_count,
516 p_data => x_msg_data
518 WHEN FND_API.G_EXC_ERROR THEN
519 ROLLBACK TO Delete_Queue_Pvt;
520 x_return_status := FND_API.G_RET_STS_ERROR;
521 -- Standard call to get message count and if count=1, get the message
522 FND_MSG_PUB.Count_And_Get (
523 p_count => x_msg_count,
524 p_data => x_msg_data
525 );
526 WHEN OTHERS THEN
527 ROLLBACK TO Delete_Queue_Pvt;
528 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
529 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
530 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
531 END IF;
532 -- Standard call to get message count and if count=1, get the message
533 FND_MSG_PUB.Count_And_Get (
534 p_count => x_msg_count,
535 p_data => x_msg_data
536 );
537 END Delete_Queue;
538 --------------------------------------------------------------------------------
539 PROCEDURE Enqueue_Message
540 (
541 p_api_version IN NUMBER,
542 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
543 p_commit IN VARCHAR2 := FND_API.G_FALSE,
544 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
545 x_return_status OUT NOCOPY VARCHAR2,
546 x_msg_count OUT NOCOPY NUMBER,
547 x_msg_data OUT NOCOPY VARCHAR2,
548 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
549 p_message_obj IN SYSTEM.AMV_AQ_MSG_OBJECT_TYPE
550 ) IS
551 l_api_name CONSTANT VARCHAR2(30) := 'Enqueue_Message';
552 l_api_version CONSTANT NUMBER := 1.0;
553 l_resource_id NUMBER := -1;
554 l_current_user_id NUMBER := -1;
555 l_current_login_id NUMBER := -1;
556 l_current_user_status VARCHAR2(80);
557 --
558 l_admin_flag VARCHAR2(1);
559 BEGIN
560 -- Standard call to check for call compatibility.
561 SAVEPOINT Enqueue_Message_Pvt;
562 IF NOT FND_API.Compatible_API_Call (
563 l_api_version,
564 p_api_version,
565 l_api_name,
566 G_PKG_NAME) THEN
567 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
568 END IF;
569 --Initialize message list if p_init_msg_list is TRUE.
570 IF FND_API.To_Boolean (p_init_msg_list) THEN
571 FND_MSG_PUB.initialize;
572 END IF;
573 -- Initialize API return status to success
574 x_return_status := FND_API.G_RET_STS_SUCCESS;
575 -- Get the current (login) user id.
576 AMV_UTILITY_PVT.Get_UserInfo(
577 x_resource_id => l_resource_id,
578 x_user_id => l_current_user_id,
579 x_login_id => l_current_login_id,
580 x_user_status => l_current_user_status
581 );
582 IF (p_check_login_user = FND_API.G_TRUE) THEN
583 -- Check if user is login and has the required privilege.
584 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
585 -- User is not login.
586 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
587 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
588 FND_MSG_PUB.Add;
589 END IF;
590 RAISE FND_API.G_EXC_ERROR;
591 END IF;
592 AMV_USER_PVT.Is_Administrator
593 (
594 p_api_version => 1.0,
595 x_return_status => x_return_status,
596 x_msg_count => x_msg_count,
597 x_msg_data => x_msg_data,
598 p_check_login_user => FND_API.G_FALSE,
599 p_resource_id => l_resource_id,
600 x_result_flag => l_admin_flag
601 );
602 IF (l_admin_flag <> FND_API.G_TRUE) THEN
603 -- User is not an administrator.
604 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
605 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_HAVE_PRIVILEGE');
606 FND_MSG_PUB.Add;
607 END IF;
608 RAISE FND_API.G_EXC_ERROR;
609 END IF;
610 END IF;
611 --Call the overloaded version to do the job.
612 Enqueue_Message
613 (
614 p_message_obj => p_message_obj
615 );
616 --Standard check of commit
617 IF FND_API.To_Boolean ( p_commit ) THEN
618 COMMIT WORK;
619 END IF;
620 --Standard call to get message count and if count=1, get the message
621 FND_MSG_PUB.Count_And_Get (
622 p_count => x_msg_count,
623 p_data => x_msg_data
624 );
625 EXCEPTION
626 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
627 ROLLBACK TO Enqueue_Message_Pvt;
628 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
629 -- Standard call to get message count and if count=1, get the message
630 FND_MSG_PUB.Count_And_Get (
631 p_count => x_msg_count,
632 p_data => x_msg_data
633 );
634 WHEN FND_API.G_EXC_ERROR THEN
635 ROLLBACK TO Enqueue_Message_Pvt;
636 x_return_status := FND_API.G_RET_STS_ERROR;
637 -- Standard call to get message count and if count=1, get the message
638 FND_MSG_PUB.Count_And_Get (
639 p_count => x_msg_count,
640 p_data => x_msg_data
644 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
641 );
642 WHEN OTHERS THEN
643 ROLLBACK TO Enqueue_Message_Pvt;
645 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
646 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
647 END IF;
648 -- Standard call to get message count and if count=1, get the message
649 FND_MSG_PUB.Count_And_Get (
650 p_count => x_msg_count,
651 p_data => x_msg_data
652 );
653 END Enqueue_Message;
654 --------------------------------------------------------------------------------
655 PROCEDURE Dequeue_Message
656 (
657 p_api_version IN NUMBER,
658 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
659 p_commit IN VARCHAR2 := FND_API.G_FALSE,
660 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
661 x_return_status OUT NOCOPY VARCHAR2,
662 x_msg_count OUT NOCOPY NUMBER,
663 x_msg_data OUT NOCOPY VARCHAR2,
664 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
665 p_delete_flag IN VARCHAR2 := FND_API.G_TRUE,
666 x_message_obj OUT NOCOPY SYSTEM.AMV_AQ_MSG_OBJECT_TYPE
667 ) IS
668 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Queue';
669 l_api_version CONSTANT NUMBER := 1.0;
670 l_resource_id NUMBER := -1;
671 l_current_user_id NUMBER := -1;
672 l_current_login_id NUMBER := -1;
673 l_current_user_status VARCHAR2(80);
674 --
675 l_admin_flag VARCHAR2(1);
676 BEGIN
677 -- Standard call to check for call compatibility.
678 SAVEPOINT Delete_Queue_Pvt;
679 IF NOT FND_API.Compatible_API_Call (
680 l_api_version,
681 p_api_version,
682 l_api_name,
683 G_PKG_NAME) THEN
684 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
685 END IF;
686 --Initialize message list if p_init_msg_list is TRUE.
687 IF FND_API.To_Boolean (p_init_msg_list) THEN
688 FND_MSG_PUB.initialize;
689 END IF;
690 -- Initialize API return status to success
691 x_return_status := FND_API.G_RET_STS_SUCCESS;
692 -- Get the current (login) user id.
693 AMV_UTILITY_PVT.Get_UserInfo(
694 x_resource_id => l_resource_id,
695 x_user_id => l_current_user_id,
696 x_login_id => l_current_login_id,
697 x_user_status => l_current_user_status
698 );
699 IF (p_check_login_user = FND_API.G_TRUE) THEN
700 -- Check if user is login and has the required privilege.
701 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
702 -- User is not login.
703 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
704 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
705 FND_MSG_PUB.Add;
706 END IF;
707 RAISE FND_API.G_EXC_ERROR;
708 END IF;
709 AMV_USER_PVT.Is_Administrator
710 (
711 p_api_version => 1.0,
712 x_return_status => x_return_status,
713 x_msg_count => x_msg_count,
714 x_msg_data => x_msg_data,
715 p_check_login_user => FND_API.G_FALSE,
716 p_resource_id => l_resource_id,
717 x_result_flag => l_admin_flag
718 );
719 IF (l_admin_flag <> FND_API.G_TRUE) THEN
720 -- User is not an administrator.
721 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
722 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_HAVE_PRIVILEGE');
723 FND_MSG_PUB.Add;
724 END IF;
725 RAISE FND_API.G_EXC_ERROR;
726 END IF;
727 END IF;
728 --Call the overloaded version to do the job.
729 Dequeue_Message
730 (
731 p_delete_flag => p_delete_flag,
732 x_message_obj => x_message_obj
733 );
734 --Standard check of commit
735 IF FND_API.To_Boolean ( p_commit ) THEN
736 COMMIT WORK;
737 END IF;
738 --Standard call to get message count and if count=1, get the message
739 FND_MSG_PUB.Count_And_Get (
740 p_count => x_msg_count,
741 p_data => x_msg_data
742 );
743 EXCEPTION
744 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
745 ROLLBACK TO Delete_Queue_Pvt;
746 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
747 -- Standard call to get message count and if count=1, get the message
748 FND_MSG_PUB.Count_And_Get (
749 p_count => x_msg_count,
750 p_data => x_msg_data
751 );
752 WHEN FND_API.G_EXC_ERROR THEN
753 ROLLBACK TO Delete_Queue_Pvt;
754 x_return_status := FND_API.G_RET_STS_ERROR;
755 -- Standard call to get message count and if count=1, get the message
756 FND_MSG_PUB.Count_And_Get (
757 p_count => x_msg_count,
758 p_data => x_msg_data
759 );
760 WHEN OTHERS THEN
761 ROLLBACK TO Delete_Queue_Pvt;
762 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
763 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
764 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
765 END IF;
766 -- Standard call to get message count and if count=1, get the message
767 FND_MSG_PUB.Count_And_Get (
768 p_count => x_msg_count,
769 p_data => x_msg_data
770 );
771 END Dequeue_Message;
772 --------------------------------------------------------------------------------
773 --------------------------------------------------------------------------------
774 END amv_aq_utility_pvt;
[Home] [Help]