[Home] [Help]
PACKAGE BODY: APPS.JTF_FM_QUERY_LINK_PKG
Source
1 PACKAGE BODY JTF_FM_QUERY_LINK_PKG AS
2 /* $Header: jtffmgqb.pls 120.0 2005/05/11 08:14:18 appldev ship $*/
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_FM_QUERY_LINK_PKG';
4 G_FILE_NAME CONSTANT VARCHAR2(12) := 'jtffmgqb.pls';
5 --
6 G_VALID_LEVEL_LOGIN CONSTANT NUMBER := FND_API.G_VALID_LEVEL_FULL;
7 --
8 ----------------------------- Private Portion ---------------------------------
9 --------------------------------------------------------------------------------
10 -- We use the following private utility procedures
11 --
12 --------------------------------------------------------------------------------
13 --
14 PROCEDURE Add_Error_Message
15 (
16 p_api_name IN VARCHAR2,
17 p_error_msg IN VARCHAR2
18 );
19
20 PROCEDURE Print_Message
21 (
22 p_error_msg IN VARCHAR2
23 );
24
25 -- Utility procedure to get the last error message
26 PROCEDURE Get_Error_Message
27 (
28 x_msg_data OUT NOCOPY VARCHAR2
29 ) ;
30
31 --
32 -- Start of comments
33 -- API name : Add_Error_Message
34 -- Type : Private
35 --
36 PROCEDURE Add_Error_Message
37 (
38 p_api_name IN VARCHAR2,
39 p_error_msg IN VARCHAR2
40 ) IS
41 BEGIN
42 -- To Be Developed.
43 PRINT_MESSAGE('p_api_name = ' || p_api_name);
44 PRINT_MESSAGE('p_error_msg = ' || p_error_msg);
45 END Add_Error_Message;
46
47 PROCEDURE Print_Message
48 (
49 p_error_msg IN VARCHAR2
50 ) IS
51 BEGIN
52 NULL;
53 -- Uncomment the line below for debug messages.
54 -- DBMS_OUTPUT.PUT_LINE('p_debug_msg = ' || p_error_msg);
55 END Print_Message;
56
57 PROCEDURE Get_Error_Message
58 (
59 x_msg_data OUT NOCOPY VARCHAR2
60 ) IS
61 l_count NUMBER := 0;
62 l_msg_index_out NUMBER := 0;
63 j NUMBER;
64 BEGIN
65 x_msg_data := NULL;
66 l_count := FND_MSG_PUB.Count_Msg;
67 IF l_count > 0 THEN
68 FND_MSG_PUB.Get(p_msg_index => l_count,
69 p_encoded => FND_API.G_FALSE,
70 p_data => x_msg_data,
71 p_msg_index_out => l_msg_index_out);
72 END IF;
73 END Get_Error_Message;
74 ---------------------------------------------------------------
75 -- PROCEDURE
76 -- Link_Content_To_Query
77 --
78 -- HISTORY
79 -- 07-24-2001 Colin Furtaw created.
80 -- 25-Jul-2001 M Petrosino modified.
81 ---------------------------------------------------------------
82
83 PROCEDURE Link_Content_To_Query
84 (
85 p_api_version IN NUMBER,
86 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
87 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
88 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
89 x_return_status OUT NOCOPY VARCHAR2,
90 x_msg_count OUT NOCOPY NUMBER,
91 x_msg_data OUT NOCOPY VARCHAR2,
92 p_content_id IN NUMBER,
93 p_query_id IN NUMBER
94 ) IS
95 l_api_name CONSTANT VARCHAR2(30) := 'Link_Content_To_Query';
96 l_api_version CONSTANT NUMBER := 1.0;
97 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
98 --
99 l_user_id NUMBER := -1;
100 l_login_user_id NUMBER := -1;
101 l_login_user_status NUMBER;
102 l_Error_Msg VARCHAR2(2000);
103 l_content_id NUMBER;
104 l_query_id NUMBER;
105 --
106 -- Attachment_type of 20 is a master document
107 CURSOR VALIDATE_CONTENT(p_content_id NUMBER) IS
108 select attachment_used_by_id
109 from jtf_fm_amv_attach_vl
110 where attachment_used_by_id = p_content_id;
111
112 CURSOR VALIDATE_QUERY(p_query_id NUMBER) IS
113 select query_id
114 from jtf_fm_queries_all
115 where query_id = p_query_id;
116
117 CURSOR VALIDATE_UNIQUE(p_query_id NUMBER, p_content_id NUMBER) IS
118 select query_id, mes_doc_id
119 from jtf_fm_query_mes
120 where query_id = p_query_id
121 and mes_doc_id = p_content_id;
122
123 BEGIN
124 -- Standard begin of API savepoint
125 SAVEPOINT LINK_CONTENT;
126
127 IF NOT FND_API.Compatible_API_Call (
128 l_api_version,
129 p_api_version,
130 l_api_name,
131 G_PKG_NAME)
132 THEN
133 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
134 END IF;
135
136 --Initialize message list if p_init_msg_list is TRUE.
137 IF FND_API.To_Boolean (p_init_msg_list) THEN
138 FND_MSG_PUB.initialize;
139 END IF;
140
141 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
142 FND_MESSAGE.Set_Name('JTF', 'JTF_FM_API_DEBUG_MESSAGE');
143 FND_MESSAGE.Set_Token('ARG1', l_full_name||': Start');
144 FND_MSG_PUB.Add;
145 END IF;
146
147 -- Initialize API return status to success
148 x_return_status := FND_API.G_RET_STS_SUCCESS;
149
150 PRINT_MESSAGE('Link_Content_to_Query called by ' || to_number(FND_GLOBAL.USER_ID));
151
152
153 OPEN VALIDATE_UNIQUE(p_query_id, p_content_id);
154 FETCH VALIDATE_UNIQUE INTO l_query_id, l_content_id;
155
156 IF (VALIDATE_UNIQUE%NOTFOUND)
157 THEN
158 OPEN VALIDATE_CONTENT(p_content_id);
159 FETCH VALIDATE_CONTENT INTO l_content_id;
160 IF (VALIDATE_CONTENT%NOTFOUND)
161 THEN
162 l_Error_Msg := p_content_id || ' is not a valid content_id';
163 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
164 FND_MESSAGE.Set_NAme('JTF', 'JTF_FM_API_CONTENT_INVALID');
165 FND_MESSAGE.Set_Token('ARG1', p_content_id);
166 FND_MSG_PUB.Add;
167 END IF;
168 RAISE FND_API.G_EXC_ERROR;
169 END IF;
170
171 CLOSE VALIDATE_CONTENT;
172
173 OPEN VALIDATE_QUERY(p_query_id);
174 FETCH VALIDATE_QUERY INTO l_query_id;
175 IF (VALIDATE_QUERY%NOTFOUND)
176 THEN
177 l_Error_Msg := p_query_id || ' is not a valid query_id';
178 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
179 FND_MESSAGE.Set_NAme('JTF', 'JTF_FM_API_QUERY_INVALID');
180 FND_MESSAGE.Set_Token('ARG1', p_query_id);
181 FND_MSG_PUB.Add;
182 END IF;
183 RAISE FND_API.G_EXC_ERROR;
184 END IF;
185
186 CLOSE VALIDATE_QUERY;
187
188 INSERT INTO jtf_fm_query_mes (
189 mes_doc_id,
190 query_id,
191 LAST_UPDATE_DATE,
192 LAST_UPDATED_BY,
193 LAST_UPDATE_LOGIN,
194 CREATION_DATE,
195 CREATED_BY
196 ) values (
197 p_content_id,
198 p_query_id,
199 sysdate,
200 FND_GLOBAL.USER_ID,
201 FND_GLOBAL.LOGIN_ID,
202 sysdate,
203 FND_GLOBAL.USER_ID
204 );
205
206 ELSE
207 l_Error_Msg := 'A Link already exists between content_id ';
208 l_Error_Msg := l_Error_Msg || p_content_id || ' and query_id ';
209 l_Error_Msg := l_Error_Msg || p_query_id;
210 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
211 FND_MESSAGE.Set_NAme('JTF', 'JTF_FM_API_CONT_QRY_LNK_EXISTS');
212 FND_MESSAGE.Set_Token('ARG1', p_content_id);
213 FND_MESSAGE.Set_Token('ARG2', p_query_id);
214 FND_MSG_PUB.Add;
215 END IF;
216 RAISE FND_API.G_EXC_ERROR;
217 END IF;
218
219 CLOSE VALIDATE_UNIQUE;
220 --Standard check of commit
221 IF FND_API.To_Boolean ( p_commit ) THEN
222 COMMIT WORK;
223 END IF;
224
225
226 EXCEPTION
227 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
228 ROLLBACK TO LINK_CONTENT;
229 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
230 Add_Error_Message (l_api_name, l_Error_Msg);
231 -- Standard call to get message count and if count=1, get the message
232 FND_MSG_PUB.count_and_get(
233 p_encoded => FND_API.g_false,
234 p_count => x_msg_count,
235 p_data => x_msg_data
236 );
237 Get_Error_Message(x_msg_data);
238 WHEN FND_API.G_EXC_ERROR THEN
239 ROLLBACK TO LINK_CONTENT;
240 x_return_status := FND_API.G_RET_STS_ERROR;
241 Add_Error_Message (l_api_name, l_Error_Msg);
242 -- Standard call to get message count and if count=1, get the message
243 FND_MSG_PUB.count_and_get(
244 p_encoded => FND_API.g_false,
245 p_count => x_msg_count,
246 p_data => x_msg_data
247 );
248 Get_Error_Message(x_msg_data);
249 WHEN OTHERS THEN
250 ROLLBACK TO LINK_CONTENT;
251 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
252 Add_Error_Message (l_api_name, SQLERRM);
253 IF FND_MSG_PUB.Check_Msg_Level
254 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
255 THEN
256 FND_MSG_PUB.Add_Exc_Msg
257 (G_PKG_NAME, l_api_name);
258 END IF;
259 -- Standard call to get message count and if count=1, get the message
260 FND_MSG_PUB.count_and_get(
261 p_encoded => FND_API.g_false,
262 p_count => x_msg_count,
263 p_data => x_msg_data
264 );
265 Get_Error_Message(x_msg_data);
266
267 END Link_Content_To_Query;
268 ---------------------------------------------------------------
269 -- PROCEDURE
270 -- UnLink_Content_To_Query
271 --
272 -- HISTORY
273 -- 26-Jul-2001 M Petrosino modified.
274 ---------------------------------------------------------------
275
276 PROCEDURE UnLink_Content_To_Query
277 (
278 p_api_version IN NUMBER,
279 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
280 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
281 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
282 x_return_status OUT NOCOPY VARCHAR2,
283 x_msg_count OUT NOCOPY NUMBER,
284 x_msg_data OUT NOCOPY VARCHAR2,
285 p_content_id IN NUMBER,
286 p_query_id IN NUMBER
287 ) IS
288 l_api_name CONSTANT VARCHAR2(30) := 'UnLink_Content_To_Query';
289 l_api_version CONSTANT NUMBER := 1.0;
290 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
291 --
292 l_user_id NUMBER := -1;
293 l_login_user_id NUMBER := -1;
294 l_login_user_status NUMBER;
295 l_Error_Msg VARCHAR2(2000);
296 l_content_id NUMBER;
297 l_query_id NUMBER;
298 --
299 -- Attachment_type of 20 is a master document
300 CURSOR VALIDATE_CONTENT(p_content_id NUMBER) IS
301 select attachment_used_by_id
302 from jtf_fm_amv_attach_vl
303 where attachment_used_by_id = p_content_id;
304
305 CURSOR VALIDATE_QUERY(p_query_id NUMBER) IS
306 select query_id
307 from jtf_fm_queries_all
308 where query_id = p_query_id;
309
310 CURSOR VALIDATE_UNIQUE(p_query_id NUMBER, p_content_id NUMBER) IS
311 select query_id, mes_doc_id
312 from jtf_fm_query_mes
313 where query_id = p_query_id
314 and mes_doc_id = p_content_id;
315
316 BEGIN
317 -- Standard begin of API savepoint
318 SAVEPOINT UNLINK_CONTENT;
319
320 IF NOT FND_API.Compatible_API_Call (
321 l_api_version,
322 p_api_version,
323 l_api_name,
324 G_PKG_NAME)
325 THEN
326 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
327 END IF;
328
329 --Initialize message list if p_init_msg_list is TRUE.
330 IF FND_API.To_Boolean (p_init_msg_list) THEN
331 FND_MSG_PUB.initialize;
332 END IF;
333
334 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
335 FND_MESSAGE.Set_Name('JTF', 'JTF_FM_API_DEBUG_MESSAGE');
336 FND_MESSAGE.Set_Token('ARG1', l_full_name||': Start');
337 FND_MSG_PUB.Add;
338 END IF;
339
340 -- Initialize API return status to success
341 x_return_status := FND_API.G_RET_STS_SUCCESS;
342
343 PRINT_MESSAGE('Unlink_Content_to_query called by ' || to_number(FND_GLOBAL.USER_ID));
344
345
346 OPEN VALIDATE_UNIQUE(p_query_id, p_content_id);
347 FETCH VALIDATE_UNIQUE INTO l_query_id, l_content_id;
348
349 IF (VALIDATE_UNIQUE%NOTFOUND)
350 THEN
351 NULL;
352 PRINT_MESSAGE('Link does not exist. Unlink_Content_to_query doing nothing.');
353 ELSE
354
355 DELETE FROM jtf_fm_query_mes
356 WHERE mes_doc_id = p_content_id
357 and query_id = p_query_id;
358
359 END IF;
360
361 CLOSE VALIDATE_UNIQUE;
362
363 --Standard check of commit
364 IF FND_API.To_Boolean ( p_commit ) THEN
365 COMMIT WORK;
366 END IF;
367
368
369 EXCEPTION
370 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
371 ROLLBACK TO UNLINK_CONTENT;
372 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
373 Add_Error_Message (l_api_name, l_Error_Msg);
374 -- Standard call to get message count and if count=1, get the message
375 FND_MSG_PUB.count_and_get(
376 p_encoded => FND_API.g_false,
377 p_count => x_msg_count,
378 p_data => x_msg_data
379 );
380 Get_Error_Message(x_msg_data);
381 WHEN FND_API.G_EXC_ERROR THEN
382 ROLLBACK TO UNLINK_CONTENT;
383 x_return_status := FND_API.G_RET_STS_ERROR;
384 Add_Error_Message (l_api_name, l_Error_Msg);
385 -- Standard call to get message count and if count=1, get the message
386 FND_MSG_PUB.count_and_get(
387 p_encoded => FND_API.g_false,
388 p_count => x_msg_count,
389 p_data => x_msg_data
390 );
391 Get_Error_Message(x_msg_data);
392 WHEN OTHERS THEN
393 ROLLBACK TO UNLINK_CONTENT;
394 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
395 Add_Error_Message (l_api_name, SQLERRM);
396 IF FND_MSG_PUB.Check_Msg_Level
397 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
398 THEN
399 FND_MSG_PUB.Add_Exc_Msg
400 (G_PKG_NAME, l_api_name);
401 END IF;
402 -- Standard call to get message count and if count=1, get the message
403 FND_MSG_PUB.count_and_get(
404 p_encoded => FND_API.g_false,
405 p_count => x_msg_count,
406 p_data => x_msg_data
407 );
408 Get_Error_Message(x_msg_data);
409
410 END UnLink_Content_To_Query;
411 END JTF_FM_QUERY_LINK_PKG;