1 PACKAGE amv_aq_utility_pvt AS
2 /* $Header: amvvaqus.pls 120.1 2005/06/22 17:22:23 appldev ship $ */
3 -- Start of Comments
4 --
5 -- NAME
6 -- AMV_AQ_UTILITY_PVT
7 --
8 -- PURPOSE
9 -- This package is a private Oracle 8i AQ utility API in AMV.
10 -- It will hide all the AQ details from the callers and provide high level
11 -- procedures to other APIs. The callers do not have to know AQ
12 -- to use this package.
13 --
14 -- In case we determine to use better technology, other than AQ, all we
15 -- need to change is this packgae. Other portions of the match engine
16 -- remains the same. However, the AQ has lots of features we might
17 -- use in later for enhancement. Likely we will continue to use AQ.
18 --
19 -- Procedures:
20 --
21 -- Add_Queue
22 -- Delete_Queue
23 -- Enqueue_Message
24 -- Dequeue_Message
25 --
26 -- NOTES
27 --
28 --
29 -- HISTORY
30 -- 09/17/1999 PWU CREATED
31 -- End of Comments
32 --
33 --
34 --------------------------------------------------------------------------------
35 -- Start of comments
36 -- API name : Add_Queue
37 -- Type : Private
38 -- Pre-reqs : None
39 -- Function : Create the AQ table, the AQ itself, and start it.
40 -- Parameters :
41 -- IN p_api_version NUMBER Required
42 -- p_init_msg_list VARCHAR2 Optional
43 -- Default = FND_API.G_FALSE
44 -- p_commit VARCHAR2 Optional
45 -- Default = FND_API.G_FALSE
46 -- p_validation_level NUMBER Optional
47 -- Default = FND_API.G_VALID_LEVEL_FULL
48 -- p_check_login_user VARCHAR2 Optional
49 -- Default = FND_API.G_TRUE
50 -- Flag for checking user privilege and if user is login.
51 -- 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
62 -- by the AQ queue. Default 'AMV_AQ_MSG_OBJECT_TYPE'
63 -- OUT : x_return_status VARCHAR2
64 -- x_msg_count NUMBER
65 -- x_msg_data VARCHAR2
66 -- Notes :
67 --
68 -- End of comments
69 --
70 PROCEDURE Add_Queue
71 (
72 p_api_version IN NUMBER,
73 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
74 p_commit IN VARCHAR2 := FND_API.G_FALSE,
75 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
76 x_return_status OUT NOCOPY VARCHAR2,
77 x_msg_count OUT NOCOPY NUMBER,
78 x_msg_data OUT NOCOPY VARCHAR2,
79 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
80 p_queue_table_name IN VARCHAR2 := AMV_QUEUE.queue_table_name,
81 p_queue_name IN VARCHAR2 := AMV_QUEUE.queue_name,
82 p_payload_obj_type IN VARCHAR2 := 'SYSTEM.AMV_AQ_MSG_OBJECT_TYPE'
83 );
84 --
85 -- Algorithm:
86 -- BEGIN
87 -- Verify API version compatibility
88 -- Check that login user's status is active
89 -- IF user not active THEN
90 -- Return error 'user account currently suspended'
91 -- ENDIF
92 -- Check that user has administrative privileges
93 -- IF user not privileged THEN
94 -- Return error 'User does not have proper privileges.'
95 -- ENDIF
96 -- Set rollback SAVEPOINT
97 -- Call the overloaded version to create the AQ queue and its related obj.
98 -- The overloaded version check if the AQ table exist.
99 -- If so, do not try to re-create the table.
100 -- Same for the AQ queue itself.
101 -- Commit transaction if requested
102 -- END
103 --
104 -- Notes :
105 -- End of comments
106 --
107 --------------------------------------------------------------------------------
108 -- Start of comments
109 -- API name : Delete_Queue
110 -- Type : Private
111 -- Pre-reqs : None
112 -- Function : Stop the AQ queue, drop it, and then drop the AQ table.
113 -- Parameters :
114 -- IN : p_api_version NUMBER Required
115 -- p_init_msg_list VARCHAR2 Optional
116 -- Default = FND_API.G_FALSE
117 -- p_commit VARCHAR2 Optional
118 -- Default = FND_API.G_FALSE
119 -- p_validation_level NUMBER Optional
120 -- Default = FND_API.G_VALID_LEVEL_FULL
121 -- p_check_login_user VARCHAR2 Optional
122 -- Default = FND_API.G_TRUE
123 -- Flag for checking user privilege and if user is login.
124 -- p_queue_table_name VARCHAR2 Optional
125 -- The underline table used by the AQ queue.
126 -- Default 'AMV_MATCHING_QUEUE_TBL'
127 -- This procedure is mainly for MES matching engine.
128 -- However, we provide it for more general purpose.
129 -- The message object to be enqueued to the AQ queue.
130 -- p_queue_name VARCHAR2 Optional
131 -- The underline table used by the AQ queue.
132 -- Default 'AMV_MATCHING_QUEUE'
133 -- OUT : None
134 -- Version : Current version 1.0
135 -- Previous version 1.0
136 -- Initial version 1.0
137 -- Note :
138 -- End of comments
139 --
140 PROCEDURE Delete_Queue
141 (
142 p_api_version IN NUMBER,
143 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
144 p_commit IN VARCHAR2 := FND_API.G_FALSE,
145 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
146 x_return_status OUT NOCOPY VARCHAR2,
147 x_msg_count OUT NOCOPY NUMBER,
148 x_msg_data OUT NOCOPY VARCHAR2,
149 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
150 p_queue_table_name IN VARCHAR2 := AMV_QUEUE.queue_table_name,
151 p_queue_name IN VARCHAR2 := AMV_QUEUE.queue_name
152 );
153 --
154 -- Algorithm:
155 -- BEGIN
156 -- Verify API version compatibility
157 -- Check that login user's status is active
158 -- IF user not active THEN
159 -- Return error 'user account currently suspended'
160 -- ENDIF
161 -- Check that user has administrative privileges
162 -- IF user not privileged THEN
163 -- Return error 'User does not have proper privileges.'
164 -- ENDIF
165 -- Set rollback SAVEPOINT
166 -- Call the overloaded version to drop the AQ queue and its related table.
167 -- The overloaded version check if the AQ queue exist.
168 -- If not, do not try to drop the queue.
169 -- Same for the AQ queue table.
170 -- Commit transaction if requested
171 -- END
172 --
173 -- End of comments
174 --
175 --------------------------------------------------------------------------------
176 -- Start of comments
177 -- API name : Enqueue_Message
178 -- Type : Private
179 -- Pre-reqs : None
180 -- Function : Put (enqueue) the message into the AQ queue.
181 -- Parameters :
182 -- IN : p_api_version NUMBER Required
183 -- p_init_msg_list VARCHAR2 Optional
184 -- Default = FND_API.G_FALSE
185 -- p_commit VARCHAR2 Optional
186 -- Default = FND_API.G_FALSE
187 -- p_validation_level NUMBER Optional
188 -- Default = FND_API.G_VALID_LEVEL_FULL
189 -- p_check_login_user VARCHAR2 Optional
190 -- Default = FND_API.G_TRUE
191 -- Flag for checking user privilege and if user is login.
192 -- p_queue_table_name VARCHAR2 Optional
193 -- p_message_obj AMV_AQ_MSG_OBJECT_TYPE
194 -- Required
195 -- The message object to be enqueued to the AQ queue.
196 -- OUT : None
197 -- Version : Current version 1.0
198 -- Previous version 1.0
199 -- Initial version 1.0
200 -- Note :
201 -- End of comments
202 --
203 PROCEDURE Enqueue_Message
204 (
205 p_api_version IN NUMBER,
206 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
207 p_commit IN VARCHAR2 := FND_API.G_FALSE,
208 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
209 x_return_status OUT NOCOPY VARCHAR2,
210 x_msg_count OUT NOCOPY NUMBER,
211 x_msg_data OUT NOCOPY VARCHAR2,
212 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
213 p_message_obj IN SYSTEM.AMV_AQ_MSG_OBJECT_TYPE
214 );
215 --
216 -- Algorithm:
217 -- BEGIN
218 -- Verify API version compatibility
219 -- Check that login user's status is active
220 -- IF user not active THEN
221 -- Return error 'user account currently suspended'
222 -- ENDIF
223 -- Check that user has administrative privileges
224 -- IF user not privileged THEN
225 -- Return error 'User does not have proper privileges.'
226 -- ENDIF
227 -- Set rollback SAVEPOINT
228 -- Call the overloaded private version to do the job:
229 -- enqueue the message to the AQ queue.
230 -- Commit transaction if requested
231 -- END
232 --
233 -- End of comments
234 --
235 --------------------------------------------------------------------------------
236 -- Start of comments
237 -- API name : Dequeue_Message
238 -- Type : Group
239 -- Pre-reqs : None
240 -- Function : Get (dequeue) a message from the AQ queue.
241 -- Parameters :
242 -- IN : p_api_version NUMBER Required
243 -- p_init_msg_list VARCHAR2 Optional
244 -- Default = FND_API.G_FALSE
245 -- p_commit VARCHAR2 Optional
246 -- Default = FND_API.G_FALSE
247 -- p_validation_level NUMBER Optional
248 -- Default = FND_API.G_VALID_LEVEL_FULL
249 -- p_check_login_user VARCHAR2 Optional
250 -- Default = FND_API.G_TRUE
251 -- Flag for checking user privilege and if user is login.
252 -- p_delete_flag VARCHAR2 Optional
253 -- Default = FND_API.G_TRUE
254 -- Flag for delete the message from the queue.
255 -- OUT : x_message_obj AMV_AQ_MSG_OBJECT_TYPE
256 -- The message object to be dequeued from the AQ queue.
257 -- Version : Current version 1.0
258 -- Previous version 1.0
259 -- Initial version 1.0
260 -- Note :
261 -- End of comments
262 --
263 PROCEDURE Dequeue_Message
264 (
265 p_api_version IN NUMBER,
266 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
267 p_commit IN VARCHAR2 := FND_API.G_FALSE,
268 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
269 x_return_status OUT NOCOPY VARCHAR2,
270 x_msg_count OUT NOCOPY NUMBER,
271 x_msg_data OUT NOCOPY VARCHAR2,
272 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
273 p_delete_flag IN VARCHAR2 := FND_API.G_TRUE,
274 x_message_obj OUT NOCOPY SYSTEM.AMV_AQ_MSG_OBJECT_TYPE
275 );
276 --
277 -- Algorithm:
278 -- BEGIN
279 -- Verify API version compatibility
280 -- Check that login user's status is active
281 -- IF user not active THEN
282 -- Return error 'user account currently suspended'
283 -- ENDIF
284 -- Check that user has administrative privileges
285 -- IF user not privileged THEN
286 -- Return error 'User does not have proper privileges.'
287 -- ENDIF
288 -- Set rollback SAVEPOINT
289 -- Call the overloaded private version to do the job:
290 -- enqueue the message to the AQ queue.
291 -- If p_delete_flag is true, the message is deleted from the queue.
292 -- Commit transaction if requested
293 -- END
294 --
295 -- End of comments
296 --
297 --------------------------------------------------------------------------------
298 END amv_aq_utility_pvt;