[Home] [Help]
PACKAGE BODY: APPS.JTF_CTM_UTILITY_PVT
Source
1 PACKAGE BODY JTF_CTM_UTILITY_PVT as
2 /* $Header: jtfvutlb.pls 120.0 2005/06/02 18:23:13 appldev ship $ */
3 --
4 -- NAME
5 -- JTF_CTM_UTILITY_PVT
6 --
7 -- HISTORY
8 -- 8/16/99 JDOCHERT CREATED
9 -- 04/19/00 VNEDUNGA Adding get_message procedure
10 -- 07/05/00 JDOCHERT Removed hard-coded reference to APPS
11 -- 03/17/04 ACHANDA Fix bug# 3511203
12
13
14 G_PKG_NAME CONSTANT VARCHAR2(30):='JTF_CTM_UTILITY_PVT';
15 G_FILE_NAME CONSTANT VARCHAR2(12):='jtfvutlb.pls';
16
17 G_APPL_ID NUMBER := FND_GLOBAL.Prog_Appl_Id;
18 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
19 G_PROGRAM_ID NUMBER := FND_GLOBAL.Conc_Program_Id;
20 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
21 G_REQUEST_ID NUMBER := FND_GLOBAL.Conc_Request_Id;
22
23
24 -- this function returns TRUE if the value of a foreign key is valid,
25 -- otherwise returns FALSE
26 FUNCTION fk_id_is_valid ( p_fk_value NUMBER,
27 p_fk_col_name VARCHAR2,
28 p_fk_table_name VARCHAR2)
29 RETURN VARCHAR2
30 IS
31
32 TYPE Ref_Cursor_Type IS REF CURSOR;
33 c_chk_fk_id Ref_Cursor_Type;
34 query_str VARCHAR2(200);
35
36 l_return_csr VARCHAR2(1);
37
38 l_return_variable VARCHAR2(1) := FND_API.G_TRUE;
39
40 BEGIN
41
42 /* cursor SELECT statement */
43 query_str := 'SELECT ''X'' FROM ' || p_fk_table_name || ' WHERE ' || p_fk_col_name || ' = :arg1';
44
45 OPEN c_chk_fk_id FOR query_str USING p_fk_value;
46
47 FETCH c_chk_fk_id INTO l_return_csr;
48
49 IF c_chk_fk_id%NOTFOUND THEN
50
51 l_return_variable := FND_API.G_FALSE;
52
53 /* Debug message */
54 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
55 FND_MESSAGE.Set_Name ('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
56 FND_MESSAGE.Set_Token ('TABLE_NAME', p_fk_table_name);
57 FND_MESSAGE.Set_Token ('COLUMN_NAME', p_fk_col_name);
58 FND_MESSAGE.Set_Token ('VALUE', p_fk_value);
59 FND_MSG_PUB.ADD;
60 END IF;
61
62 END IF;
63
64 CLOSE c_chk_fk_id;
65
66 RETURN l_return_variable;
67
68 END fk_id_is_valid;
69
70
71 -- this function returns TRUE if the lookup value of an item is valid,
72 -- otherwise returns FALSE
73 FUNCTION lookup_code_is_valid ( p_lookup_code VARCHAR2,
74 p_lookup_type VARCHAR2,
75 p_lookup_table_name VARCHAR2)
76 RETURN VARCHAR2
77 IS
78
79 TYPE Ref_Cursor_Type IS REF CURSOR;
80 c_chk_lookup_code Ref_Cursor_Type;
81 query_str VARCHAR2(200);
82
83 l_return_csr VARCHAR2(1);
84
85 l_return_variable VARCHAR2(1) := FND_API.G_TRUE;
86
87 BEGIN
88
89 -- cursor SELECT statement
90 query_str := 'SELECT ''X'' FROM ' || p_lookup_table_name ||
91 ' WHERE lookup_type = :arg1 AND lookup_code = :arg2';
92
93 OPEN c_chk_lookup_code FOR query_str USING p_lookup_type, p_lookup_code;
94
95 FETCH c_chk_lookup_code INTO l_return_csr;
96
97 IF c_chk_lookup_code%NOTFOUND THEN
98
99 l_return_variable := FND_API.G_FALSE;
100
101 -- Debug message
102 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
103 FND_MESSAGE.Set_Name ('JTF', 'JTF_TERR_INVALID_LOOKUP_CODE');
104 FND_MESSAGE.Set_Token ('LOOKUP_TYPE', p_lookup_type);
105 FND_MESSAGE.Set_Token ('LOOKUP_CODE', p_lookup_code);
106 FND_MSG_PUB.ADD;
107 END IF;
108
109 END IF;
110
111 CLOSE c_chk_lookup_code;
112
113 RETURN l_return_variable;
114
115 END lookup_code_is_valid;
116
117
118
119 FUNCTION get_subOrderBy(p_col_choice IN NUMBER, p_col_name IN VARCHAR2)
120 RETURN VARCHAR2 IS
121 l_col_name varchar2(30);
122 begin
123
124 if (p_col_choice is NULL and p_col_name is NOT NULL)
125 or (p_col_choice is NOT NULL and p_col_name is NULL)
126 then
127 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
128 then
129 fnd_message.set_name('AS', 'API_MISSING_ORDERBY_ELEMENT');
130 fnd_msg_pub.add;
131 end if;
132 raise fnd_api.g_exc_error;
133 end if;
134
135
136 if (nls_upper(p_col_name) = 'CUSTOMER_NAME')
137 then
138 l_col_name := ' nls_upper' ||'(' ||p_col_name|| ')';
139 else
140 l_col_name := p_col_name;
141 end if;
142 if (mod(p_col_choice, 10) = 1)
143 then
144 return(l_col_name || ' ASC, ');
145 elsif (mod(p_col_choice, 10) = 0)
146 then
147 return(l_col_name || ' DESC, ');
148 else
149 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
150 then
151 fnd_message.set_name('AS', 'API_INVALID_ORDERBY_CHOICE');
152 fnd_message.set_token('PARAM',p_col_choice, false);
153 fnd_msg_pub.add;
154 end if;
155 raise fnd_api.g_exc_error;
156 return '';
157 end if;
158 end;
159
160 PROCEDURE Translate_OrderBy
161 ( p_api_version IN NUMBER,
162 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
163 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
164 x_return_status OUT NOCOPY VARCHAR2,
165 x_msg_count OUT NOCOPY NUMBER,
166 x_msg_data OUT NOCOPY VARCHAR2,
167 p_order_by_tbl IN util_order_by_tbl_type,
168 x_order_by_clause OUT NOCOPY VARCHAR2
169 ) IS
170
171 l_api_name CONSTANT VARCHAR2(30) := 'Translate_OrderBy';
172 l_api_version CONSTANT NUMBER := 1.0;
173
174 TYPE OrderByTabTyp is TABLE of VARCHAR2(80) INDEX BY BINARY_INTEGER;
175
176 l_sortedOrderBy_tbl OrderByTabTyp;
177 i NUMBER := 1;
178 j NUMBER := 1;
179 l_order_by_clause VARCHAR2(2000) := NULL;
180
181
182 BEGIN
183 -- Standard call to check for call compatibility.
184 IF NOT FND_API.Compatible_API_Call (l_api_version,
185 p_api_version,
186 l_api_name,
187 G_PKG_NAME)
188 THEN
189 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
190 THEN
191 FND_MESSAGE.Set_Name('JTF', 'API_UNEXP_ERROR_IN_PROCESSING');
192 FND_MESSAGE.Set_Token('ROW', 'TRANSLATE_ORDERBY', TRUE);
193 FND_MSG_PUB.ADD;
194 END IF;
195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
196 END IF;
197
198 -- Initialize message list if p_init_msg_list is set to TRUE.
199 IF FND_API.to_Boolean( p_init_msg_list )
200 THEN
201 FND_MSG_PUB.initialize;
202 END IF;
203
204 -- Initialize API return status to success
205 --
206 x_return_status := FND_API.G_RET_STS_SUCCESS;
207
208 --
209 -- API body
210 --
211
212 -- initialize the table to ''.
213 for i in 1..p_order_by_tbl.count loop
214 l_sortedOrderBy_tbl(i) := '';
215 end loop;
216
217 -- We allow the choice seqence order such as 41, 20, 11, ...
218 -- So, we need to sort it first(put them into a table),
219 -- then loop through the whole table.
220
221 for j in 1..p_order_by_tbl.count loop
222 if (p_order_by_tbl(j).col_choice is NOT NULL)
223 then
224 l_sortedOrderBy_tbl(floor(p_order_by_tbl(j).col_choice/10)) :=
225 get_subOrderBy(p_order_by_tbl(j).col_choice,
226 p_order_by_tbl(j).col_name);
227 end if;
228 end loop;
229
230 for i in 1..p_order_by_tbl.count loop
231 l_order_by_clause := l_order_by_clause || l_sortedOrderBy_tbl(i);
232 end loop;
233
234 l_order_by_clause := rtrim(l_order_by_clause); -- trim ''
235 l_order_by_clause := rtrim(l_order_by_clause, ','); -- trim last ,
236 x_order_by_clause := l_order_by_clause;
237
238 EXCEPTION
239
240 WHEN FND_API.G_EXC_ERROR THEN
241
242 x_return_status := FND_API.G_RET_STS_ERROR ;
243
244 FND_MSG_PUB.Count_And_Get
245 ( p_count => x_msg_count,
246 p_data => x_msg_data
247 );
248
249
250 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
251
252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
253
254 FND_MSG_PUB.Count_And_Get
255 ( p_count => x_msg_count,
256 p_data => x_msg_data
257 );
258
259
260 WHEN OTHERS THEN
261
262
263 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
264
265 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
266 THEN
267 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
268 END IF;
269
270 FND_MSG_PUB.Count_And_Get
271 ( p_count => x_msg_count,
272 p_data => x_msg_data
273 );
274
275 END Translate_OrderBy;
276
277 -- Start of Comments
278 --
279 -- API name : Split
280 -- Type : Private
281 -- Function : Splits the incomming string as substrings based on
282 -- the delimter
283 --
284 --
285 -- Paramaeters :
286 -- IN :
287 -- p_Input_String IN VARCHAR2
288 -- p_Delimiter IN VARCHAR2
289 --
290 -- OUT
291 -- x_return_status OUT VARCHAR2
292 -- x_String_Tbl OUT util_string_tbl_type
293 -- x_order_by_clause OUT VARCHAR2
294 --
295 -- Version : Current version 1.0
296 -- Initial version 1.0
297 --
298 --
299 --
300 -- End of Comments
301
302 PROCEDURE Split
303 ( p_Input_String IN VARCHAR2,
304 p_Delimiter IN VARCHAR2,
305 x_return_status OUT NOCOPY VARCHAR2,
306 x_String_Tbl OUT NOCOPY util_string_tbl_type )
307 AS
308 loc number := 0;
309 old_loc number := 1;
310 l_counter number := 0;
311 BEGIN
312 LOOP
313 l_counter := l_counter + 1;
314 loc := instr(p_Input_String, p_Delimiter, old_loc);
315 if loc=0 then -- At the end of the string
316 --dbms_output.put_line('Inside loc = 0');
317 x_String_Tbl(l_counter) := ltrim(substr(p_Input_String, old_loc));
318 --dbms_output.put_line(x_String_Tbl(l_counter));
319 exit;
320 else
321 x_String_Tbl(l_counter) := ltrim(substr(p_Input_String, old_loc, loc-old_loc));
322 old_loc := loc+1;
323
324 --dbms_output.put_line(x_String_Tbl(l_counter));
325 end if;
326 END LOOP;
327 END Split;
328
329 -- Start of Comments
330 --
331 -- API name : Format_View_Text
332 -- Type : Private
333 -- Function : Formats the view text as columns and tables
334 --
335 --
336 -- Paramaeters :
337 -- IN :
338 -- p_Input_String IN VARCHAR2
339 --
340 -- OUT
341 -- x_return_status OUT VARCHAR2
342 -- x_String_Tbl OUT util_string_tbl_type,
343 -- x_Where_Clause OUT VARCHAR2,
344 -- X_No_Of_Columns OUT NUMBER,
345 -- X_No_Of_Tables OUT NUMBER
346 --
347 -- Version : Current version 1.0
348 -- Initial version 1.0
349 --
350 --
351 --
352 -- End of Comments
353
354 PROCEDURE Format_View_Text
355 ( p_View_Name IN VARCHAR2,
356 x_return_status OUT NOCOPY VARCHAR2,
357 x_view_Columns_Tbl OUT NOCOPY util_View_Columns_Tbl_type,
358 x_view_From_Tbl OUT NOCOPY util_View_From_Tbl_type ,
359 X_Where_Clause OUT NOCOPY VARCHAR2,
360 X_From_Clause OUT NOCOPY VARCHAR2,
361 X_Select_Clause OUT NOCOPY VARCHAR2,
362 X_No_Of_Columns OUT NOCOPY NUMBER,
363 X_No_Of_Tables OUT NOCOPY NUMBER )
364 AS
365 CURSOR c_ColAlias IS
366 select COLUMN_NAME
367 from user_tab_columns
368 where table_name = p_View_Name;
369
370 l_view_Text VARCHAR2(10000);
371 c_select VARCHAR2(5000);
372 l_Select number;
373 l_from number;
374 l_where number;
375 l_return_status VARCHAR2(01);
376 l_Column_Tbl JTF_CTM_UTILITY_PVt.util_string_tbl_type;
377 l_String_Tbl JTF_CTM_UTILITY_PVt.util_string_tbl_type;
378 l_TblName_Tbl JTF_CTM_UTILITY_PVt.util_string_tbl_type;
379 i NUMBER;
380 j NUMBER;
381 l_apps_schema_name VARCHAR2(30);
382 BEGIN
383 --dbms_output.put_line('Inside Format_View_Text procedure');
384
385 /* ACHANDA : Bug # 3511203 : get apps schema and use it to get the view text from all_views */
386 SELECT oracle_username
387 INTO l_apps_schema_name
388 FROM fnd_oracle_userid
389 WHERE read_only_flag = 'U';
390
391 SELECT Text into l_view_Text from all_views where view_name = p_View_Name and owner = l_apps_schema_name;
392
393 l_select := instr(l_view_Text, 'SELECT');
394 --dbms_output.put_line('l_select - ' || to_char(l_select));
395
396 l_from := instr(l_view_Text, 'FROM');
397 --dbms_output.put_line('l_from - ' || to_char(l_from));
398
399 l_where := instr(l_view_Text, 'WHERE');
400 --dbms_output.put_line('l_where - ' || to_char(l_where));
401
402 c_select := substr(l_view_Text, (l_select + 6), (l_from - (l_select + 6)) );
403 X_Select_Clause := c_select;
404 --dbms_output.put_line(substr( c_select, 1, 100));
405
406 X_From_Clause := substr(l_view_Text, (l_from + 4), (l_where-(l_from+5)));
407 --dbms_output.put_line(X_From_Clause);
408
409 X_Where_Clause := substr(l_view_Text, l_where + 5);
410 --dbms_output.put_line(X_Where_Clause);
411
412 -- Seperate the SELECT columns
413 JTF_CTM_UTILITY_PVT.Split(c_select, ',', l_return_status, l_Column_Tbl);
414 --
415 X_No_Of_Columns := l_Column_Tbl.Count;
416 --
417 --dbms_output.put_line('l_Column_Tbl.Count - ' || to_char(l_Column_Tbl.Count));
418 --
419 -- Split the column into table alias and column name
420 FOR i in l_Column_Tbl.first .. l_Column_Tbl.Count LOOP
421 --
422 --dbms_output.put_line('Before COLUMN JTF_CTM_UTILITY_PVT.Split - ' || l_Column_Tbl(i) );
423 JTF_CTM_UTILITY_PVT.Split(l_Column_Tbl(i), '.', l_return_status, l_String_Tbl);
424 --dbms_output.put_line('After COLUMN JTF_CTM_UTILITY_PVT.Split - ' || l_String_Tbl(1));
425 --
426 -- If the use hasn't specified an alias
427 If l_String_Tbl.Count = 2 Then
428 x_view_Columns_Tbl(i).Table_Alias := l_String_Tbl(1);
429 x_view_Columns_Tbl(i).col_name := l_String_Tbl(2);
430 Else
431 x_view_Columns_Tbl(i).Table_Alias := NULL;
432 x_view_Columns_Tbl(i).col_name := l_String_Tbl(1);
433 End If;
434 --
435 End LOOP;
436
437 -- Seperate the FROM columns
438 JTF_CTM_UTILITY_PVT.Split(X_From_Clause, ',', l_return_status, l_TblName_Tbl);
439 -- Split the column into table alias and column name
440 FOR i in l_TblName_Tbl.first .. l_TblName_Tbl.Count LOOP
441 --
442 --dbms_output.put_line('Before FROM JTF_CTM_UTILITY_PVT.Split - ' || l_TblName_Tbl(i));
443 JTF_CTM_UTILITY_PVT.Split(l_TblName_Tbl(i), ' ', l_return_status, l_String_Tbl);
444 --dbms_output.put_line('After FROM JTF_CTM_UTILITY_PVT.Split - ' || l_String_Tbl(1));
445 --
446 -- If the use hasn't specified an alias
447 If l_String_Tbl.Count = 2 Then
448 x_view_From_Tbl(i).Table_Alias := l_String_Tbl(2);
449 x_view_From_Tbl(i).Table_Name := l_String_Tbl(1);
450 Else
451 x_view_From_Tbl(i).Table_Alias := NULL;
452 x_view_From_Tbl(i).Table_Name := l_String_Tbl(1);
453 End If;
454 --
455 End LOOP;
456
457 FOR i in x_view_Columns_Tbl.first .. x_view_Columns_Tbl.Count LOOP
458 --
459 --dbms_output.put_line(' x_view_Columns_Tbl(i).Table_Alias - ' || nvl(x_view_Columns_Tbl(i).Table_Alias, '<NULL>'));
460 FOR j in x_view_From_Tbl.first .. x_view_From_Tbl.Count LOOP
461 --dbms_output.put_line(' x_view_From_Tbl(j).Table_Alias - ' || x_view_From_Tbl(j).Table_Alias);
462 If x_view_Columns_Tbl(i).Table_alias = x_view_From_Tbl(j).Table_Alias Then
463 x_view_Columns_Tbl(i).Table_Name := x_view_From_Tbl(j).Table_Name;
464 exit;
465 End If;
466 x_view_Columns_Tbl(i).Table_Name := NULL;
467 END LOOP;
468 --
469 END LOOP;
470
471 -- Load the column name from att_tab_columns table
472 i := 0;
473 FOR C in c_ColAlias LOOP
474 i := i + 1;
475 x_view_Columns_Tbl(i).Col_Alias := c.Column_Name;
476 END LOOP;
477
478 --
479 END Format_View_Text;
480
481
482 PROCEDURE Get_Messages (
483 p_message_count IN NUMBER,
484 x_msgs OUT NOCOPY VARCHAR2)
485 IS
486 l_msg_list VARCHAR2(5000) := '';
487 l_temp_msg VARCHAR2(2000);
488 l_appl_short_name VARCHAR2(20) ;
489 l_message_name VARCHAR2(30) ;
490
491 l_id NUMBER;
492 l_message_num NUMBER;
493
494 l_msg_count NUMBER;
495 l_msg_data VARCHAR2(2000);
496
497 Cursor Get_Appl_Id (x_short_name VARCHAR2) IS
498 SELECT application_id
499 FROM fnd_application_vl
500 WHERE application_short_name = x_short_name;
501
502 Cursor Get_Message_Num (x_msg VARCHAR2, x_id NUMBER, x_lang_id NUMBER) IS
503 SELECT msg.message_number
504 FROM fnd_new_messages msg, fnd_languages_vl lng
505 WHERE msg.message_name = x_msg
506 and msg.application_id = x_id
507 and lng.LANGUAGE_CODE = msg.language_code
508 and lng.language_id = x_lang_id;
509 BEGIN
510 FOR l_count in 1..p_message_count LOOP
511
512 l_temp_msg := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_true);
513 fnd_message.parse_encoded(l_temp_msg, l_appl_short_name, l_message_name);
514 OPEN Get_Appl_Id (l_appl_short_name);
515 FETCH Get_Appl_Id into l_id;
516 CLOSE Get_Appl_Id;
517
518 l_message_num := NULL;
519 IF l_id is not NULL
520 THEN
521 OPEN Get_Message_Num (l_message_name, l_id,
522 to_number(NVL(FND_PROFILE.Value('LANGUAGE'), '0')));
523 FETCH Get_Message_Num into l_message_num;
524 CLOSE Get_Message_Num;
525 END IF;
526
527 l_temp_msg := fnd_msg_pub.get(fnd_msg_pub.g_previous, fnd_api.g_true);
528
529 IF NVL(l_message_num, 0) <> 0
530 THEN
531 l_temp_msg := 'APP-' || to_char(l_message_num) || ': ';
532 ELSE
533 l_temp_msg := NULL;
534 END IF;
535
536 IF l_count = 1
537 THEN
538 l_msg_list := l_msg_list || l_temp_msg ||
539 fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false);
540 ELSE
541 l_msg_list := l_msg_list || l_temp_msg ||
542 fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);
543 END IF;
544
545 l_msg_list := l_msg_list || '';
546
547 END LOOP;
548
549 x_msgs := l_msg_list;
550
551 END Get_Messages;
552
553
554 END JTF_CTM_UTILITY_PVT;