1 PACKAGE BODY IBE_UTIL AS
2 /* $$Header: IBEUTILB.pls 120.0 2005/05/30 02:39:04 appldev noship $$ */
3 /*----------------------------------------------------------------------------*
4 | PUBLIC PROCEDURES |
5 | debug - Display text message if in debug mode |
6 | enable_debug_new - Enable run time debugging |
7 | disable_debug_new - Disable run time debugging |
8 | file_debug - Write text messages into a file if in |
9 | file_debug mode |
10 | |
11 | enable_file_debug - Enable writing debug messages to a file. |
12 | Requires file patch (directory), THIS SHOULD BE |
13 | DEFINED IN INIT.ORA PARAMETER 'UTIL_FILE_DIR', |
14 | file name (Any valid OS file name). |
15 | disable_file_debug - Stops writing into the file. |
16 | get_install_info - Calls FND_INSTALLATION.get() to determine if an |
17 | application is installed. |
18 | |
19 | DESCRIPTION |
20 | Generate standard debug information for PL/SQL apis by sending it to |
21 | to a operating system file. |
22 | |
23 | HOW TO USE ISTORE DEBUGGING: |
24 | |
25 | 1. Turn on the Debugging |
26 | a) If calling pl/sql from java before calling PL/SQL api from java itself |
27 | enable runtime debugging. and after pl/sql is finished, disable run time|
28 | debugging. |
29 | Example: |
30 | ocsStmt.append("BEGIN " + |
31 | IBEUtil.getEnableDebugString() + <= To turn on debugging|
32 | "IBE_Quote_W1_Pvt.SaveWrapper(" + <= Your PL/SQL api |
33 | ..... |
34 | ..... |
35 | ..... |
36 | ); |
37 | ..... |
38 | ocsStmt.append(IBEUtil.getDisableDebugString() + "END;"); <= Turn off |
39 | debugging |
40 | |
41 | b) If calling pl/sql api from within PL/SQL itself |
42 | call ibe_util.enable_debug_new before and call |
43 | ibe_util.disable_debug_new afterwards |
44 | |
45 | 2. Within your PL/SQL api's call IBE_UTIL.debug to print the messages |
46 | NO NEED TO CALL enable_Debug or disable_debug from within pl/sql api |
47 | |
48 | |
49 | REQUIRES |
50 | |
51 | |
52 | EXCEPTIONS RAISED |
53 | |
54 | KNOWN BUGS |
55 | |
56 | NOTES |
57 | |
58 | HISTORY |
59 | Harish Ekkirala Created 04/03/2000. |
60 | 05/05/2000 - Audrey Yu added get_install_info |
61 | 05/11/2001 - Modified the logging mechanism to use OM log file. Now you |
62 | turn on the logging for a specific user by turning on |
63 | IBE_DEBUG profile at user level. -- hekkiral |
64 | 07/29/2002 - achalana - Changed the Debugging mechanism for performance | |
65 | fix (bug 2406789) |
66 | 08/26/2003 - abhandar - added procedure check_jtf_permission() |
67 | 09/04/2003 - abhandar - Modified for NOCOPY . |
68 | 01/14/2004 - batoleti - Added nls_number_format function |
69 | 02/18/2004 - ssekar - Added insert_into_temp_table and |
70 | delete_from_temp_table created by Anita |
71 *----------------------------------------------------------------------------*/
72
73 -------------------------------------------------------------------------
74 -- Private Variables
75 -------------------------------------------------------------------------
76 l_file_name VARCHAR2(100) := NULL;
77 l_path_name VARCHAR2(100) := NULL;
78 l_file_type utl_file.file_type;
79 l_debug_flag boolean := false;
80
81 procedure file_debug(p_line in varchar2) IS
82
83 begin
84 if (l_file_name is not null) THEN
85 utl_file.put_line(l_file_type, p_line);
86 utl_file.fflush(l_file_type);
87 end if;
88 end file_debug;
89
90 procedure enable_file_debug(p_path_name in varchar2,
91 p_file_name in varchar2) IS
92 begin
93
94 if (l_file_name is null) THEN
95 l_file_type := utl_file.fopen(p_path_name, p_file_name, 'a');
96 l_file_name := p_file_name;
97 l_path_name := p_path_name;
98 end if;
99
100 exception
101 when utl_file.invalid_path then
102 app_exception.raise_exception;
103 when utl_file.invalid_mode then
104 app_exception.raise_exception;
105
106 end ;
107
108 procedure disable_file_debug is
109 begin
110 if (l_file_name is not null) THEN
111 utl_file.fclose(l_file_type);
112 end if;
113 end;
114
115 procedure debug(p_line in varchar2 ) is
116 x_rest varchar2(32767);
117 debug_msg varchar2(32767);
118 buffer_overflow exception;
119 pragma exception_init(buffer_overflow, -20000);
120 begin
121
122 /**
123 Changed to not call fnd_profile everytime but to check IBE_UTIL.DEBUGON global variable
124 - achalana 07/29
125 **/
126 -- If fnd_profile.value_specific('IBE_DEBUG',FND_GLOBAL.USER_ID,null,null) = 'Y' Then
127 If IBE_UTIL.G_DEBUGON = FND_API.G_TRUE Then
128 -- utl_file.put_line(ASO_DEBUG_PUB.G_FILE_PTR, to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS')||'IBE IBE_UTIL:Using New Debugging');
129 enable_debug_pvt();
130 x_rest := p_line;
131 loop
132 if (x_rest is null) then
133 exit;
134 else
135 --OE_DEBUG_PUB.ADD(to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS')||' '||substr(x_rest,1,255));
136 debug_msg := to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS')||' IBE '||substr(x_rest,1,255);
137 utl_file.put_line(ASO_DEBUG_PUB.G_FILE_PTR, debug_msg);
138 utl_file.fflush(ASO_DEBUG_PUB.G_FILE_PTR);
139 x_rest := substr(x_rest,256);
140 end if;
141 end loop;
142
143 Elsif IBE_UTIL.G_DEBUGON = FND_API.G_FALSE Then
144 disable_debug_pvt();
145 Elsif IBE_UTIL.G_DEBUGON IS NULL Then
146 /**
147 wrote this section of code for backward compatibility, so that code that was not changed
148 for new debugging should work
149 **/
150 If fnd_profile.value_specific('IBE_DEBUG',FND_GLOBAL.USER_ID,null,null) = 'Y' Then
151 enable_debug_pvt();
152 x_rest := p_line;
153 loop
154 if (x_rest is null) then
155 exit;
156 else
157 --OE_DEBUG_PUB.ADD(to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS')||' '||substr(x_rest,1,255));
158 debug_msg := to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS')||' IBE '||substr(x_rest,1,255);
159 utl_file.put_line(ASO_DEBUG_PUB.G_FILE_PTR, debug_msg);
160 utl_file.fflush(ASO_DEBUG_PUB.G_FILE_PTR);
161 x_rest := substr(x_rest,256);
162 end if;
163 end loop;
164 End If;
165 End If;
166
167 exception
168 when buffer_overflow then
169 null; -- buffer overflow, ignore
170 when others then
171 -- raise; -- Modified so that it will not raise any exceptions.
172 null;
173 end;
174
175 procedure enable_debug is
176 l_file_name VARCHAR2(100);
177 begin
178 null; -- Modified so that this will not be used by developers.
179 end;
180
181 /* New procedure 07/29 - achalana */
182 procedure enable_debug_new(p_check_profile varchar2 default NULL) is
183 l_file_name VARCHAR2(100);
184 begin
185
186 /**
187 If in java before calling enable_debug_new we already have checked the cookie by calling
188 IBEUtil.logEnabled() you can pass p_check_profile = 'N' to this api, it will not check
189 fnd_profile again, and set G_DEBUGON to true
190 **/
191
192 If p_check_profile = 'N' Then
193 IBE_UTIL.G_DEBUGON := FND_API.G_TRUE;
194 IBE_UTIL.debug('IBE_UTIL.enable_debug_new p_check_profile is N');
195 Else
196 If fnd_profile.value_specific('IBE_DEBUG',FND_GLOBAL.USER_ID,null,null) = 'Y' Then
197 IBE_UTIL.G_DEBUGON := FND_API.G_TRUE;
198 Else
199 IBE_UTIL.G_DEBUGON := FND_API.G_FALSE;
200 End If;
201
202 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
203 IBE_UTIL.debug('IBE_UTIL.enable_debug_new p_check_profile is not passed');
204 END IF;
205 End If;
206 end;
207
208
209 procedure enable_debug_pvt is
210 begin
211 --l_debug_flag := true;
212 --dbms_output.enable;
213 /* Modified the procedure so that we can enable debug at user level. If the
214 profile IBE: Enable Debug is Set to 'Yes' for a User, we will start writing
215 the debug messages into a file. */
216
217 IF (ASO_DEBUG_PUB.G_FILE is NULL OR ASO_DEBUG_PUB.G_FILE <> 'IBE_'||FND_GLOBAL.USER_NAME||'.log') Then
218 ASO_DEBUG_PUB.G_DEBUG_MODE := 'FILE';
219 ASO_DEBUG_PUB.G_FILE := 'IBE_'||FND_GLOBAL.USER_NAME||'.log';
220 ASO_DEBUG_PUB.G_FILE_PTR := utl_file.fopen(ASO_DEBUG_PUB.G_DIR,ASO_DEBUG_PUB.G_FILE,'a');
221 ASO_DEBUG_PUB.debug_on;
222 ASO_DEBUG_PUB.setdebuglevel(ASO_DEBUG_PUB.G_DEBUG_LEVEL);
223 /* Setting OM Debug variables on */
224 OE_DEBUG_PUB.G_DEBUG_MODE := 'FILE';
225 OE_DEBUG_PUB.G_FILE := 'IBE_'||FND_GLOBAL.USER_NAME||'.log';
226 OE_DEBUG_PUB.G_FILE_PTR := ASO_DEBUG_PUB.G_FILE_PTR;
227 OE_DEBUG_PUB.debug_on;
228 OE_DEBUG_PUB.setdebuglevel(ASO_DEBUG_PUB.G_DEBUG_LEVEL);
229
230 END IF;
231 end;
232
233 procedure disable_debug is
234 begin
235 null; -- Modified to developers don't use this.
236 end;
237
238 /* New procedure 07/29 - achalana */
239 procedure disable_debug_new is
240 begin
241 disable_debug_pvt();
242 IBE_UTIL.G_DEBUGON := FND_API.G_FALSE;
243 end;
244
245 /* New procedure 07/29 - achalana */
246 procedure reset_debug is
247 begin
248 disable_debug_pvt();
249 IBE_UTIL.G_DEBUGON := NULL;
250 end;
251
252 procedure disable_debug_pvt is
253 begin
254 ASO_DEBUG_PUB.Debug_off;
255 ASO_DEBUG_PUB.G_FILE := null;
256 OE_DEBUG_PUB.Debug_off;
257 OE_DEBUG_PUB.G_FILE := null;
258 If utl_file.is_Open(ASO_DEBUG_PUB.G_FILE_PTR) Then
259 utl_file.fclose(ASO_DEBUG_PUB.G_FILE_PTR);
260 End If;
261 exception
262 When Others Then
263 null;
264 end;
265
266
267 procedure get_install_info(p_appl_id in number,
268 p_dep_appl_id in number,
269 x_status out NOCOPY varchar2,
270 x_industry out NOCOPY varchar2,
271 x_installed out NOCOPY number)
272 IS
273 l_installed BOOLEAN;
274
275 BEGIN
276 l_installed := fnd_installation.get( appl_id => p_appl_id,
277 dep_appl_id => p_dep_appl_id,
278 status => x_status,
279 industry => x_industry );
280 IF (l_installed) THEN
281 x_installed := 1;
282 ELSE
283 x_installed := 0;
284 END IF;
285
286 END get_install_info;
287
288 -- replicates RequestCtx.userHasPermission() functionality
289 -- always returns true for IBE_INDIVIDUAL user
290 FUNCTION check_user_permission(
291 p_permission in VARCHAR2,
292 p_user_name in VARCHAR2 :=fnd_global.user_name
293 ) RETURN BOOLEAN
294 IS
295
296 Cursor c_get_party_type(c_user_name VARCHAR2) IS
297 select d.party_type
298 from fnd_user c , hz_parties d
299 where d.party_id=c.customer_id
300 and c.user_name=c_user_name;
301
302 l_PartyType Varchar2(30);
303
304 BEGIN
305 l_PartyType :='';
306
307 open c_get_party_type(p_user_name);
308 fetch c_get_party_type into l_PartyType;
309 CLOSE c_get_party_type;
310 if (l_PartyType='PERSON') then
311 return true;
312 else return check_jtf_permission(p_permission,p_user_name);
313 end if;
314
315
316 END check_user_permission;
317
318
319 -- added by abhandar :new procedure
320 -- replicates jtf SecurityManager.check() function
321 FUNCTION check_jtf_permission(
322 p_permission in VARCHAR2,
323 p_user_name in VARCHAR2 :=fnd_global.user_name
324 ) RETURN BOOLEAN
325 IS
326
327 l_api_name CONSTANT VARCHAR2(40) := 'check_jtf_permission';
328 l_temp_var VARCHAR2(1);
329 l_hasPermission boolean :=false;
330
331 CURSOR c_get_permission(l_user_name VARCHAR2,l_permission VARCHAR2) IS
332 SELECT 'Y' FROM JTF_AUTH_PERMISSIONS_B A,
333 JTF_AUTH_PRINCIPAL_MAPS B,
334 JTF_AUTH_ROLE_PERMS C,
335 JTF_AUTH_PRINCIPALS_B D,
336 JTF_AUTH_PRINCIPALS_B E,
337 JTF_AUTH_DOMAINS_B F
338 WHERE D.PRINCIPAL_NAME = l_user_name AND D.IS_USER_FLAG = 1 AND
339 D.JTF_AUTH_PRINCIPAL_ID = B.JTF_AUTH_PRINCIPAL_ID AND
340 B.JTF_AUTH_PARENT_PRINCIPAL_ID=E.JTF_AUTH_PRINCIPAL_ID AND
341 E.IS_USER_FLAG = 0 AND
342 E.JTF_AUTH_PRINCIPAL_ID = C.JTF_AUTH_PRINCIPAL_ID AND
343 C.JTF_AUTH_PERMISSION_ID = A.JTF_AUTH_PERMISSION_ID AND
344 C.POSITIVE_FLAG=1 AND
345 B.JTF_AUTH_DOMAIN_ID = F.JTF_AUTH_DOMAIN_ID AND
346 F.DOMAIN_NAME ='CRM_DOMAIN' AND
347 A.PERMISSION_NAME = l_permission;
348
349 BEGIN
350 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
351 ibe_util.debug(l_api_name||'input user name ='|| p_user_name);
352 ibe_util.debug(l_api_name||'input permission ='|| p_permission);
353 END IF;
354
355 OPEN c_get_permission(p_user_name,p_permission);
356 FETCH c_get_permission INTO l_temp_var;
357 IF (c_get_permission%FOUND) THEN
358 l_hasPermission:=true;
359 END IF;
360
361 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
362 ibe_util.debug(l_api_name||'l_temp_var ='|| l_temp_var);
363 END IF;
364
365 return l_hasPermission;
366
367 END check_jtf_permission;
368
369 /*
370
371 nls_number_format function is used to convert the number to
372 canonical format.
373 The input is a number in string.
374 The out put is also a string.
375
376 */
377
378 FUNCTION nls_number_format(
379 p_number_in in VARCHAR2
380 ) RETURN VARCHAR2
381 IS
382
383 l_number_out VARCHAR2(50);
384
385 BEGIN
386
387 l_number_out := to_char(fnd_number.canonical_to_number(p_number_in));
388
389 RETURN (l_number_out);
390
391 END nls_number_format;
392
393 --This function deletes the data from the temporary table
394 FUNCTION delete_from_temp_table (p_keyString IN VARCHAR2) RETURN VARCHAR2
395
396 IS
397 BEGIN
398 delete from ibe_temp_table where key=p_keyString;
399 IF SQL%FOUND THEN
400 return FND_API.G_TRUE;
401 ELSE
402 return FND_API.G_FALSE;
403 END IF;
404 END delete_from_temp_table;
405
406 --Concatenates phone elements to phone format of iStore
407 FUNCTION format_phone(
408 p_country_code in VARCHAR2,
409 p_area_code in VARCHAR2,
410 p_phone_number in VARCHAR2,
411 p_phone_ext in VARCHAR2
412 ) RETURN VARCHAR2
413 IS
414 l_phone_number_out VARCHAR2(86);
415 BEGIN
416 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
417 IBE_UTIL.debug('Begin format_phone,input cc,ac,phnum,phext '||p_country_code||','||p_area_code||','||p_phone_number||','||p_phone_ext);
418 END IF;
419
420 IF p_country_code IS NOT NULL AND p_country_code <> FND_API.G_MISS_CHAR THEN
421 l_phone_number_out := p_country_code||'-';
422 END IF;
423 IF p_area_code IS NOT NULL AND p_area_code <> FND_API.G_MISS_CHAR THEN
424 l_phone_number_out := l_phone_number_out || p_area_code ||'-';
425 END IF;
426 IF p_phone_number IS NOT NULL AND p_phone_number <> FND_API.G_MISS_CHAR THEN
427 l_phone_number_out := l_phone_number_out || p_phone_number ||' ';
428 END IF;
429 IF p_phone_ext IS NOT NULL AND p_phone_ext <> FND_API.G_MISS_CHAR THEN
430 l_phone_number_out := l_phone_number_out || 'x' || p_phone_ext;
431 END IF;
432
433 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
434 IBE_UTIL.debug('Exit format_phone, output phone number '||l_phone_number_out);
435 END IF;
436 RETURN l_phone_number_out;
437 END format_phone;
438
439 --This procudure inserts the data from the temporary table
440 PROCEDURE insert_into_temp_table (p_inString IN VARCHAR2,
441 p_Type IN VARCHAR2,
442 p_keyString IN VARCHAR2,
443 x_QueryString OUT NOCOPY VARCHAR2)
444 IS
445
446 BEGIN
447
448 IF IBE_UTIL.G_DEBUGON = FND_API.G_TRUE THEN
449 IBE_UTIL.debug('START: insert_into_temp_table');
450 END IF;
451
452 IF p_Type = 'CHAR' THEN
453 INSERT into IBE_TEMP_TABLE (KEY, CHAR_VAL) VALUES (p_keyString,p_inString);
454 ELSIF p_Type = 'NUM' THEN
455 INSERT into IBE_TEMP_TABLE (KEY, NUM_VAL) VALUES (p_keyString,to_number(p_inString));
456 END IF;
457
458 IF p_Type = 'CHAR' THEN
459 x_QueryString := 'SELECT CHAR_VAL FROM IBE_TEMP_TABLE WHERE KEY = :1';
460 ELSIF p_Type = 'NUM' THEN
461 x_QueryString := 'SELECT NUM_VAL FROM IBE_TEMP_TABLE WHERE KEY = :1';
462 END IF;
463
464 EXCEPTION
465 WHEN OTHERS then
466 IF IBE_UTIL.G_DEBUGON = FND_API.G_TRUE THEN
467 IBE_UTIL.debug('Exception.....'||sqlerrm);
468 END IF;
469 Raise;
470
471 END insert_into_temp_table;
472
473 END IBE_UTIL;