[Home] [Help]
PACKAGE BODY: APPS.JTF_PLSQL_API
Source
1 PACKAGE BODY JTF_PLSQL_API as
2 /* $Header: JTFPAPIB.pls 115.2 2000/08/17 00:09:15 pkm ship $ */
3
4 --
5 -- NAME
6 -- JTF_PLSQL_API
7 --
8 -- HISTORY
9 -- 08/11/99 AWU CREATED(as AS_UTILITY)
10 -- 09/09/99 SOLIN UPDATED(change to JTF_PLSQL_API)
11 --
12
13 G_PKG_NAME CONSTANT VARCHAR2(30):='JTF_PLSQL_API';
14 G_FILE_NAME CONSTANT VARCHAR2(12):='jtfpapib.pls';
15
16 G_APPL_ID NUMBER := FND_GLOBAL.Prog_Appl_Id;
17 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
18 G_PROGRAM_ID NUMBER := FND_GLOBAL.Conc_Program_Id;
19 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
20 G_REQUEST_ID NUMBER := FND_GLOBAL.Conc_Request_Id;
21
22
23
24 PROCEDURE Start_API(
25 p_api_name IN VARCHAR2,
26 p_pkg_name IN VARCHAR2,
27 p_init_msg_list IN VARCHAR2,
28 p_l_api_version IN NUMBER,
29 p_api_version IN NUMBER,
30 p_api_type IN VARCHAR2,
31 x_return_status OUT VARCHAR2)
32 IS
33 BEGIN
34 NULL;
35 END Start_API;
36
37
38 PROCEDURE End_API(
39 x_msg_count OUT NUMBER,
40 x_msg_data OUT VARCHAR2)
41 IS
42 BEGIN
43 NULL;
44 END End_API;
45
46
47 PROCEDURE Handle_Exceptions(
48 P_API_NAME IN VARCHAR2,
49 P_PKG_NAME IN VARCHAR2,
50 P_EXCEPTION_LEVEL IN NUMBER := FND_API.G_MISS_NUM,
51 P_SQLCODE IN NUMBER DEFAULT NULL,
52 P_SQLERRM IN VARCHAR2 DEFAULT NULL,
53 P_PACKAGE_TYPE IN VARCHAR2,
54 P_ROLLBACK_FLAG IN VARCHAR2 := 'Y',
55 X_MSG_COUNT OUT NUMBER,
56 X_MSG_DATA OUT VARCHAR2,
57 X_RETURN_STATUS OUT VARCHAR2)
58 IS
59 l_api_name VARCHAR2(30);
60 BEGIN
61 l_api_name := UPPER(p_api_name);
62
63 IF p_rollback_flag = 'Y'
64 THEN
65 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(l_api_name || p_package_type);
66 END IF;
67
68 IF p_exception_level = FND_MSG_PUB.G_MSG_LVL_ERROR
69 THEN
70 x_return_status := FND_API.G_RET_STS_ERROR;
71 FND_MSG_PUB.Count_And_Get(
72 p_count => x_msg_count,
73 p_data => x_msg_data);
74 ELSIF p_exception_level = FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
75 THEN
76 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
77 FND_MSG_PUB.Count_And_Get(
78 p_count => x_msg_count,
79 p_data => x_msg_data);
80 ELSIF p_exception_level = G_EXC_OTHERS
81 THEN
82 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
83
84 FND_MESSAGE.Set_Name('AS', 'Error number ' || to_char(P_SQLCODE));
85 FND_MSG_PUB.Add;
86 FND_MESSAGE.Set_Name('AS', 'Error text ' || P_SQLERRM);
87 FND_MSG_PUB.Add;
88
89 FND_MSG_PUB.Add_Exc_Msg(p_pkg_name, p_api_name);
90 FND_MSG_PUB.Count_And_Get(
91 p_count => x_msg_count,
92 p_data => x_msg_data);
93 END IF;
94
95 END Handle_Exceptions;
96
97
98
99
100 FUNCTION get_subOrderBy(p_col_choice IN NUMBER, p_col_name IN VARCHAR2)
101 RETURN VARCHAR2 IS
102 l_col_name varchar2(30);
103 begin
104
105 if (p_col_choice is NULL and p_col_name is NOT NULL)
106 or (p_col_choice is NOT NULL and p_col_name is NULL)
107 then
108 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
109 then
110 fnd_message.set_name('AS', 'API_MISSING_ORDERBY_ELEMENT');
111 fnd_msg_pub.add;
112 end if;
113 raise fnd_api.g_exc_error;
114 end if;
115
116
117 if (nls_upper(p_col_name) = 'CUSTOMER_NAME')
118 then
119 l_col_name := ' nls_upper' ||'(' ||p_col_name|| ')';
120 else
121 l_col_name := p_col_name;
122 end if;
123
124 if (mod(p_col_choice, 10) = 1)
125 then
126 return(l_col_name || ' ASC, ');
127 elsif (mod(p_col_choice, 10) = 0)
128 then
129 return(l_col_name || ' DESC, ');
130 else
131 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
132 then
133 fnd_message.set_name('AS', 'API_INVALID_ORDERBY_CHOICE');
134 fnd_message.set_token('PARAM',p_col_choice, false);
135 fnd_msg_pub.add;
136 end if;
137 raise fnd_api.g_exc_error;
138 return '';
139 end if;
140 end;
141
142 PROCEDURE Translate_OrderBy
143 ( p_api_version_number IN NUMBER,
144 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
145 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
146 p_order_by_tbl IN UTIL_ORDER_BY_TBL_TYPE,
147 x_order_by_clause OUT VARCHAR2,
148 x_return_status OUT VARCHAR2,
149 x_msg_count OUT NUMBER,
150 x_msg_data OUT VARCHAR2
151 ) IS
152
153 TYPE OrderByTabTyp is TABLE of VARCHAR2(80) INDEX BY BINARY_INTEGER;
154 l_sortedOrderBy_tbl OrderByTabTyp;
155 i BINARY_INTEGER := 1;
156 j BINARY_INTEGER := 1;
157 l_order_by_clause VARCHAR2(2000) := NULL;
158 l_api_name CONSTANT VARCHAR2(30) := 'Translate_OrderBy';
159 l_api_version_number CONSTANT NUMBER := 1.0;
160 begin
161 -- Standard call to check for call compatibility.
162 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
163 p_api_version_number,
164 l_api_name,
165 G_PKG_NAME)
166 THEN
167 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
168 THEN
169 FND_MESSAGE.Set_Name('AS', 'API_UNEXP_ERROR_IN_PROCESSING');
170 FND_MESSAGE.Set_Token('ROW', 'TRANSLATE_ORDERBY', TRUE);
171 FND_MSG_PUB.ADD;
172 END IF;
173 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
174 END IF;
175
176 -- Initialize message list if p_init_msg_list is set to TRUE.
177 IF FND_API.to_Boolean( p_init_msg_list )
178 THEN
179 FND_MSG_PUB.initialize;
180 END IF;
181
182 -- Initialize API return status to success
183 --
184 x_return_status := FND_API.G_RET_STS_SUCCESS;
185
186 --
187 -- API body
188 --
189
190 -- Validate Environment
191
192 IF G_User_Id IS NULL
193 THEN
194 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
195 THEN
196 FND_MESSAGE.Set_Name('AS', 'UT_CANNOT_GET_PROFILE_VALUE');
197 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
198 FND_MSG_PUB.ADD;
199 END IF;
200 END IF;
201
202 -- initialize the table to ''.
203 for i in 1..p_order_by_tbl.count loop
204 l_sortedOrderBy_tbl(i) := '';
205 end loop;
206
207 -- We allow the choice seqence order such as 41, 20, 11, ...
208 -- So, we need to sort it first(put them into a table),
209 -- then loop through the whole table.
210
211 for j in 1..p_order_by_tbl.count loop
212 if (p_order_by_tbl(j).col_choice is NOT NULL)
213 then
214 l_sortedOrderBy_tbl(floor(p_order_by_tbl(j).col_choice/10)) :=
215 get_subOrderBy(p_order_by_tbl(j).col_choice,
216 p_order_by_tbl(j).col_name);
217 end if;
218 end loop;
219
220 for i in 1..p_order_by_tbl.count loop
221 l_order_by_clause := l_order_by_clause || l_sortedOrderBy_tbl(i);
222 end loop;
223 l_order_by_clause := rtrim(l_order_by_clause); -- trim ''
224 l_order_by_clause := rtrim(l_order_by_clause, ','); -- trim last ,
225 x_order_by_clause := l_order_by_clause;
226
227 EXCEPTION
228
229 WHEN FND_API.G_EXC_ERROR THEN
230
231 x_return_status := FND_API.G_RET_STS_ERROR ;
232
233 FND_MSG_PUB.Count_And_Get
234 ( p_count => x_msg_count,
235 p_data => x_msg_data
236 );
237
238
239 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
240
241 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
242
243 FND_MSG_PUB.Count_And_Get
244 ( p_count => x_msg_count,
245 p_data => x_msg_data
246 );
247
248
249 WHEN OTHERS THEN
250
251
252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
253
254 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
255 THEN
256 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
257 END IF;
258
259 FND_MSG_PUB.Count_And_Get
260 ( p_count => x_msg_count,
261 p_data => x_msg_data
262 );
263
264 end Translate_OrderBy;
265
266
267 PROCEDURE Get_Messages (
268 p_message_count IN NUMBER,
269 x_msgs OUT VARCHAR2)
270 IS
271 l_msg_list VARCHAR2(10000) := ' ';
272 l_temp_msg VARCHAR2(2000);
273 l_appl_short_name VARCHAR2(20) ;
274 l_message_name VARCHAR2(30) ;
275 l_newline varchar2(20) := fnd_global.newline;
276 l_id NUMBER;
277 l_message_num NUMBER;
278
279 l_msg_count NUMBER;
280 l_msg_data VARCHAR2(2000);
281
282 Cursor Get_Appl_Id (x_short_name VARCHAR2) IS
283 SELECT application_id
284 FROM fnd_application_vl
285 WHERE application_short_name = x_short_name;
286
287 Cursor Get_Message_Num (x_msg VARCHAR2, x_id NUMBER, x_lang_id NUMBER) IS
288 SELECT msg.message_number
289 FROM fnd_new_messages msg, fnd_languages_vl lng
290 WHERE msg.message_name = x_msg
291 and msg.application_id = x_id
292 and lng.LANGUAGE_CODE = msg.language_code
293 and lng.language_id = x_lang_id;
294 BEGIN
295 FOR l_count in 1..p_message_count LOOP
296
297 l_temp_msg := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_true);
298 fnd_message.parse_encoded(l_temp_msg, l_appl_short_name, l_message_name);
299 OPEN Get_Appl_Id (l_appl_short_name);
300 FETCH Get_Appl_Id into l_id;
301 CLOSE Get_Appl_Id;
302
303 l_message_num := NULL;
304 IF l_id is not NULL
305 THEN
306 OPEN Get_Message_Num (l_message_name, l_id,
307 to_number(NVL(FND_PROFILE.Value('LANGUAGE'), '0')));
308 FETCH Get_Message_Num into l_message_num;
309 CLOSE Get_Message_Num;
310 END IF;
311
312 l_temp_msg := fnd_msg_pub.get(fnd_msg_pub.g_previous, fnd_api.g_true);
313
314 IF NVL(l_message_num, 0) <> 0
315 THEN
316 l_temp_msg := 'APP-' || to_char(l_message_num) || ': ';
317 ELSE
318 l_temp_msg := NULL;
319 END IF;
320
321 IF l_count = 1
322 THEN
323 l_msg_list := l_msg_list || l_temp_msg ||
324 fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false);
325 ELSE
326 l_msg_list := l_msg_list || l_temp_msg ||
327 fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);
328 END IF;
329
330 l_msg_list := l_msg_list || l_newline;
331
332 END LOOP;
333
334 x_msgs := l_msg_list;
335
336 END Get_Messages;
337
338
339 PROCEDURE Debug_Message(
340 p_msg_level IN NUMBER,
341 p_app_name IN VARCHAR2,
342 p_msg IN VARCHAR2)
343 IS
344 l_length NUMBER;
345 l_start NUMBER := 1;
346 l_substring VARCHAR2(30);
347 BEGIN
348 IF FND_MSG_PUB.Check_Msg_Level(p_msg_level)
349 THEN
350 /*
351 l_length := length(p_msg);
352
353 -- FND_MESSAGE doesn't allow message name to be over 30 chars
354 -- chop message name if length > 30
355 WHILE l_length > 30 LOOP
356 l_substring := substr(p_msg, l_start, 30);
357
358 FND_MESSAGE.Set_Name('AS', l_substring);
359 -- FND_MESSAGE.Set_Name(p_app_name, l_substring);
360 l_start := l_start + 30;
361 l_length := l_length - 30;
362 FND_MSG_PUB.Add;
363 END LOOP;
364
365 l_substring := substr(p_msg, l_start);
366 FND_MESSAGE.Set_Name('AS', l_substring);
367 -- dbms_output.put_line('l_substring: ' || l_substring);
368 -- FND_MESSAGE.Set_Name(p_app_name, p_msg);
369 FND_MSG_PUB.Add;
370 */
371 l_length := length(p_msg);
372
373 -- FND_MESSAGE doesn't allow message name to be over 30 chars
374 -- chop message name if length > 30
375 IF l_length > 30
376 THEN
377 l_substring := substr(p_msg, l_start, 30);
378 FND_MESSAGE.Set_Name(p_app_name, l_substring);
379 ELSE
380 FND_MESSAGE.Set_Name(p_app_name, p_msg);
381 END IF;
382
383 FND_MSG_PUB.Add;
384 END IF;
385 END Debug_Message;
386
387
388 PROCEDURE Set_Message(
389 p_msg_level IN NUMBER,
390 p_app_name IN VARCHAR2,
391 p_msg_name IN VARCHAR2,
392 p_token1 IN VARCHAR2,
393 p_token1_value IN VARCHAR2
394 )
395 IS
396 BEGIN
397 IF FND_MSG_PUB.Check_Msg_Level(p_msg_level)
398 THEN
399 FND_MESSAGE.Set_Name('AS', p_msg_name);
400 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
401 FND_MSG_PUB.Add;
402 END IF;
403 END Set_Message;
404
405 PROCEDURE Set_Message(
406 p_msg_level IN NUMBER,
407 p_app_name IN VARCHAR2,
408 p_msg_name IN VARCHAR2,
409 p_token1 IN VARCHAR2,
410 p_token1_value IN VARCHAR2,
411 p_token2 IN VARCHAR2,
412 p_token2_value IN VARCHAR2
413 )
414 IS
415 BEGIN
416 IF FND_MSG_PUB.Check_Msg_Level(p_msg_level)
417 THEN
418 FND_MESSAGE.Set_Name('AS', p_msg_name);
419 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
420 FND_MESSAGE.Set_Token(p_token2, p_token2_value);
421 FND_MSG_PUB.Add;
422 END IF;
423 END Set_Message;
424
425 PROCEDURE Set_Message(
426 p_msg_level IN NUMBER,
427 p_app_name IN VARCHAR2,
428 p_msg_name IN VARCHAR2,
429 p_token1 IN VARCHAR2,
430 p_token1_value IN VARCHAR2,
431 p_token2 IN VARCHAR2,
432 p_token2_value IN VARCHAR2,
433 p_token3 IN VARCHAR2,
434 p_token3_value IN VARCHAR2
435 )
436 IS
437 BEGIN
438 IF FND_MSG_PUB.Check_Msg_Level(p_msg_level)
439 THEN
440 FND_MESSAGE.Set_Name('AS', p_msg_name);
441 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
442 FND_MESSAGE.Set_Token(p_token2, p_token2_value);
443 FND_MESSAGE.Set_Token(p_token3, p_token3_value);
444 FND_MSG_PUB.Add;
445 END IF;
446 END Set_Message;
447
448
449
450 PROCEDURE Set_Message(
451 p_msg_level IN NUMBER,
452 p_app_name IN VARCHAR2,
453 p_msg_name IN VARCHAR2,
454 p_token1 IN VARCHAR2,
455 p_token1_value IN VARCHAR2,
456 p_token2 IN VARCHAR2,
457 p_token2_value IN VARCHAR2,
458 p_token3 IN VARCHAR2,
459 p_token3_value IN VARCHAR2,
460 p_token4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
461 p_token4_value IN VARCHAR2 := FND_API.G_MISS_CHAR,
462 p_token5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
463 p_token5_value IN VARCHAR2 := FND_API.G_MISS_CHAR,
464 p_token6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
465 p_token6_value IN VARCHAR2 := FND_API.G_MISS_CHAR,
466 p_token7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
467 p_token7_value IN VARCHAR2 := FND_API.G_MISS_CHAR
468 )
469 IS
470 BEGIN
471 IF FND_MSG_PUB.Check_Msg_Level(p_msg_level)
472 THEN
473 FND_MESSAGE.Set_Name('AS', p_msg_name);
474 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
475 FND_MESSAGE.Set_Token(p_token2, p_token2_value);
476 FND_MESSAGE.Set_Token(p_token3, p_token3_value);
477 FND_MESSAGE.Set_Token(p_token4, p_token4_value);
478 FND_MESSAGE.Set_Token(p_token5, p_token5_value);
479 FND_MESSAGE.Set_Token(p_token6, p_token6_value);
480 FND_MESSAGE.Set_Token(p_token7, p_token7_value);
481 FND_MSG_PUB.Add;
482 END IF;
483 END Set_Message;
484
485 PROCEDURE Gen_Flexfield_Where(
486 p_flex_where_tbl_type IN JTF_PLSQL_API.flex_where_tbl_type,
487 x_flex_where_clause OUT VARCHAR2) IS
488 l_flex_where_cl VARCHAR2(2000) := NULL;
489 BEGIN
490 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
491 null;
492 -- dbms_output.put_line('JTF_PLSQL_API Generate Flexfield Where: begin');
493 END IF;
494
495 FOR i IN 1..p_flex_where_tbl_type.count LOOP
496 IF (p_flex_where_tbl_type(i).value IS NOT NULL
497 AND p_flex_where_tbl_type(i).value <> FND_API.G_MISS_CHAR) THEN
498 l_flex_where_cl := l_flex_where_cl||' AND '||p_flex_where_tbl_type(i).name
499 || ' = :p_ofso_flex_var'||i;
500 END IF;
501 END LOOP;
502 x_flex_where_clause := l_flex_where_cl;
503
504 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
505 null;
506 -- dbms_output.put_line('JTF_PLSQL_API Generate Flexfield Where: end');
507 END IF;
508 END;
509
510 PROCEDURE Bind_Flexfield_Where(
511 p_cursor_id IN NUMBER,
512 p_flex_where_tbl_type IN JTF_PLSQL_API.flex_where_tbl_type) IS
513 BEGIN
514 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
515 null;
516 -- dbms_output.put_line('JTF_PLSQL_API Bind Flexfield Where: begin');
517 END IF;
518
519 FOR i IN 1..p_flex_where_tbl_type.count LOOP
520 IF (p_flex_where_tbl_type(i).value IS NOT NULL
521 AND p_flex_where_tbl_type(i).value <> FND_API.G_MISS_CHAR) THEN
522 DBMS_SQL.Bind_Variable(p_cursor_id, ':p_ofso_flex_var'||i,
523 p_flex_where_tbl_type(i).value);
524 END IF;
525 END LOOP;
526
527 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
528 null;
529 -- dbms_output.put_line('JTF_PLSQL_API Bind Flexfield Where: end');
530 END IF;
531 END;
532
533
534 END JTF_PLSQL_API;