1 PACKAGE BODY CZ_UTILS AS
2 /* $Header: czcutilb.pls 120.3 2006/01/20 02:14:02 amdixit ship $ */
3
4 PROCEDURE get_App_Info(p_app_short_name IN VARCHAR2,
5 x_oracle_schema OUT NOCOPY VARCHAR2) IS
6
7 v_status VARCHAR2(255);
8 v_industry VARCHAR2(255);
9 v_ret BOOLEAN;
10 BEGIN
11 v_ret := FND_INSTALLATION.GET_APP_INFO(APPLICATION_SHORT_NAME => p_app_short_name,
12 STATUS => v_status,
13 INDUSTRY => v_industry,
14 ORACLE_SCHEMA => x_oracle_schema);
15 END;
16
17 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<o>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
18 FUNCTION REPORT (Msg in VARCHAR2, Urgency in NUMBER, ByCaller in VARCHAR2, StatusCode in NUMBER)
19 RETURN BOOLEAN IS
20
21 PRAGMA AUTONOMOUS_TRANSACTION;
22
23 /* This calls a reporting function and runs it as an autonomous transation.
24 Autonomous transactions will not work in distributed environment */
25
26 BEGIN
27 DECLARE
28 x_check_log_report_f BOOLEAN:=FALSE;
29 BEGIN
30 x_check_log_report_f := log_report(Msg, urgency, ByCaller, StatusCode);
31
32 IF (x_check_log_report_f) THEN
33 commit;
34 RETURN TRUE;
35 ELSE
36 rollback;
37 RETURN FALSE;
38 END IF;
39 END;
40 END REPORT;
41
42 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<o>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
43 FUNCTION LOG_REPORT (Msg in VARCHAR2, Urgency in NUMBER, ByCaller in VARCHAR2, StatusCode in NUMBER)
44 RETURN BOOLEAN IS
45 BEGIN
46 RETURN LOG_REPORT(Msg,Urgency,ByCaller,StatusCode,NULL);
47 END;
48
49 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<o>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
50 FUNCTION LOG_REPORT (Msg in VARCHAR2, Urgency in NUMBER, ByCaller in VARCHAR2, StatusCode in NUMBER, RunId in NUMBER)
51 RETURN BOOLEAN IS
52 v_oracle_schema VARCHAR2(255);
53 l_msg VARCHAR2(2000);
54 BEGIN
55
56 IF FND_GLOBAL.CONC_REQUEST_ID > 0 THEN
57 l_msg:=SUBSTR(RunId||':'||ByCaller||'.'||StatusCode||':'||Msg,1,2000);
58 FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg);
59 END IF;
60
61 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
62 fnd_log.string(fnd_log.LEVEL_ERROR, ByCaller || '.' || StatusCode, RunId||':'||Msg);
63 END IF;
64
65 /* Reporting function. This does not the commit the changes.
66 The calling function should commit or rollback the changes explicitly */
67
68 DECLARE
69
70 x_get_dbsettings_debug_f BOOLEAN:=FALSE;
71 x_get_dbsettings_report_f BOOLEAN:=FALSE;
72 p_error_flag CHAR(1):='';
73 sLogConst CZ_DB_SETTINGS.SETTING_ID%TYPE;
74
75 CURSOR get_dbsettings IS
76 SELECT VALUE FROM CZ_DB_SETTINGS WHERE SETTING_ID = sLogConst;
77
78 -- Make sure that the DataSet exists
79 BEGIN
80
81 /* If the nDebugLevel or nReportLevel are not set */
82 /* Then read these values from the Dbase */
83 IF( nDebugLevel IS NULL ) THEN
84 /* Get the DBMS Output report level */
85 sLogConst:='LOG_MINIMUMDEBUGLEVEL';
86 OPEN get_dbsettings;
87 FETCH get_dbsettings INTO nDebugLevel;
88 x_get_dbsettings_debug_f := get_dbsettings%FOUND;
89 CLOSE get_dbsettings;
90 END IF;
91
92 IF( nReportLevel IS NULL) THEN
93 sLogConst:='LOG_MINIMUMREPORTLEVEL';
94 OPEN get_dbsettings;
95 FETCH get_dbsettings INTO nReportLevel;
96 x_get_dbsettings_report_f := get_dbsettings%FOUND;
97 CLOSE get_dbsettings;
98 END IF;
99
100 IF( (nDebugLevel IS NULL AND nReportLevel IS NULL ) OR
101 (URGENCY < nDebugLevel AND URGENCY < nReportLevel )) THEN
102 return TRUE;
103 END IF;
104
105 get_App_Info('CZ',v_oracle_schema);
106
107 /* Output To DB_LOG table if URGENCY is above the threshold */
108 IF (nReportLevel IS NOT NULL AND URGENCY >= nReportLevel ) THEN
109 BEGIN
110 BEGIN
111 INSERT INTO CZ_DB_LOGS (LOGTIME, LOGUSER, URGENCY, CALLER, STATUSCODE, MESSAGE, RUN_ID)
112 VALUES(SYSDATE, USER, URGENCY, ByCaller, StatusCode, Msg, RunId);
113 EXCEPTION
114 WHEN OTHERS THEN
115 RETURN FALSE;
116 END;
117 END;
118 END IF;
119
120 /* Output To screen If URGENCY is above the threshold */
121 IF (nDebugLevel IS NOT NULL AND URGENCY >= nDebugLevel ) THEN
122 NULL;
123 --dbms_output.put_line(TO_CHAR(SYSDATE,'MON-DD-YYYY')||
124 --' : User=('||USER||') '||'Urgency=('||URGENCY||') '||
125 --'Caller=('||ByCaller||') '||'StatusCode=('||TO_CHAR(StatusCode)||')');
126 END IF;
127
128 END;
129 RETURN TRUE;
130 END LOG_REPORT;
131
132
133 FUNCTION TODATE (fromString in VARCHAR2) return DATE
134 is
135 outdate date;
136 begin
137 begin
138 outdate := TO_DATE (fromstring, 'MM-DD-YYYY HH24:MI:SS');
139 return outdate;
140 exception
141 when others THEN null;
142 end;
143 begin
144 outdate := TO_DATE (fromstring, 'YYYY-MM-DD HH24:MI:SS');
145 return outdate;
146 exception
147 when others THEN null;
148 end;
149 begin
150 outdate := TO_DATE (fromstring, 'YYYY/MM/DD HH24:MI:SS');
151 return outdate;
152 exception
153 when others then null;
154 end;
155
156 outdate := TO_DATE (fromstring, 'MM/DD/YYYY HH24:MI:SS');
157 return outdate;
158 end TODATE;
159
160 FUNCTION GET_PK_USEEXPANSION_FLAG(TABLE_NAME IN VARCHAR2,
161 inXFR_GROUP IN VARCHAR2)
162 RETURN NUMBER IS
163 BEGIN
164 DECLARE
165 PK_USEEXPANSION_FLAG NUMBER;
166 BEGIN
167 SELECT DECODE(PK_USEEXPANSION,NULL,0,'0',0,1) INTO PK_USEEXPANSION_FLAG
168 FROM CZ_XFR_TABLES WHERE DST_TABLE=TABLE_NAME AND XFR_GROUP=inXFR_GROUP
169 AND ROWNUM=1;
170
171 RETURN PK_USEEXPANSION_FLAG;
172 EXCEPTION
173 WHEN OTHERS THEN
174 -- x_error:=CZ_UTILS.REPORT(SQLERRM,1,'CZ_UTILS.GET_PK_USEEXPANSION_FLAG',11276);
175 log_report('cz_utils', 'GET_PK_USEEXPANSION_FLAG', 1, SQLERRM, fnd_log.LEVEL_UNEXPECTED);
176 RETURN 0;
177 END;
178 END GET_PK_USEEXPANSION_FLAG;
179
180 FUNCTION GET_NOUPDATE_FLAG(TABLE_NAME IN VARCHAR2,
181 COLUMN_NAME IN VARCHAR2,
182 inXFR_GROUP IN VARCHAR2)
183 RETURN NUMBER IS
184 BEGIN
185 DECLARE
186 NOUPDATE_FLAG CZ_XFR_FIELDS.NOUPDATE%TYPE;
187 BEGIN
188 SELECT NOUPDATE INTO NOUPDATE_FLAG
189 FROM CZ_XFR_FIELDS,CZ_XFR_TABLES
190 WHERE CZ_XFR_TABLES.DST_TABLE=TABLE_NAME AND CZ_XFR_FIELDS.DST_FIELD=COLUMN_NAME
191 AND CZ_XFR_TABLES.ORDER_SEQ=CZ_XFR_FIELDS.ORDER_SEQ
192 AND CZ_XFR_TABLES.XFR_GROUP=CZ_XFR_FIELDS.XFR_GROUP
193 AND CZ_XFR_FIELDS.XFR_GROUP=inXFR_GROUP AND ROWNUM=1;
194
195 RETURN TO_NUMBER(NVL(NOUPDATE_FLAG,'0'));
196
197 EXCEPTION
198 WHEN OTHERS THEN
199 -- x_error:=CZ_UTILS.REPORT(SQLERRM,0,'CZ_UTILS.GET_NOUPDATE_FLAG',11276);
200 log_report('cz_utils', 'GET_NOUPDATE_FLAG', 1, SQLERRM, fnd_log.LEVEL_UNEXPECTED);
201 RETURN 0;
202 END;
203 END GET_NOUPDATE_FLAG;
204
205 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<o>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
206 FUNCTION ISNUM(nVALUE IN VARCHAR2) RETURN BOOLEAN IS
207 BEGIN
208 DECLARE xVALUE VARCHAR2(255);
209 BEGIN
210 SELECT TO_NUMBER(nVALUE) INTO xVALUE FROM DUAL;
211 RETURN TRUE;
212 EXCEPTION
213 WHEN OTHERS THEN
214 RETURN FALSE;
215 END;
216 END;
217 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<o>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
218 PROCEDURE GET_USER_NAME (forSPXID IN NUMBER, outNAME OUT NOCOPY VARCHAR2) IS
219 sUSER ALL_USERS.USERNAME%TYPE;
220 sFND FND_USER.USER_NAME%TYPE;
221
222 BEGIN
223 IF forSPXID<0 THEN
224 SELECT DISTINCT USERNAME INTO sUSER
225 FROM ALL_USERS
226 WHERE USER_ID=-forSPXID;
227 outNAME:=sUSER;
228 ELSE
229 SELECT USER_NAME INTO sFND
230 FROM FND_USER
231 WHERE USER_ID=forSPXID;
232 outNAME:=sFND;
233 END IF;
234 EXCEPTION
235 WHEN OTHERS THEN
236 -- X_ERROR:=CZ_UTILS.REPORT(SQLERRM,1,'CZ_UTILS.GET_USER_NAME',11276);
237 log_report('cz_utils', 'GET_USER_NAME', 1, SQLERRM, fnd_log.LEVEL_UNEXPECTED);
238 END GET_USER_NAME;
239
240 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<o>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
241 function conv_num(str varchar2)
242 return number is
243 begin
244 return conv_num(str, '9999999999999999D99999999');
245 end conv_num;
246 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<o>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
247 function conv_num(str varchar2, format varchar2)
248 return number is
249 begin
250 return to_number(str, format);
251 exception
252 when value_error then
253 return null;
254 end conv_num;
255 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<o>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
256
257 FUNCTION SPX_UID RETURN INTEGER IS
258 RET INTEGER:=-2;
259 BEGIN
260 IF FND_GLOBAL.USER_ID<>-1 THEN
261 RET:=FND_GLOBAL.USER_ID;
262 ELSE
263 RET:=(-1*UID);
264 END IF;
265 RETURN RET;
266 EXCEPTION
267 WHEN OTHERS THEN
268 RETURN RET;
269 END;
270 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<o>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
271 FUNCTION SPX_LOGIN_TYPE RETURN CHAR IS
272 RET CHAR(1):='*';
273 BEGIN
274 IF FND_GLOBAL.USER_ID<>-1 THEN
275 RET:='A';
276 ELSE
277 RET:='D';
278 END IF;
279 RETURN RET;
280 EXCEPTION
281 WHEN OTHERS THEN
282 RETURN RET;
283 END;
284
285 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<o>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
286
287 FUNCTION EPOCH_BEGIN RETURN DATE IS
288 BEGIN
289 RETURN EPOCH_BEGIN_;
290 END;
291 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<o>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
292
293 FUNCTION EPOCH_END RETURN DATE IS
294 BEGIN
295 RETURN EPOCH_END_;
296 END;
297 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<o>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
298
299 FUNCTION GET_TEXT(inMessageName IN VARCHAR2) RETURN VARCHAR2 IS
300 v_String VARCHAR2(2000);
301 BEGIN
302 FND_MESSAGE.SET_NAME('CZ', inMessageName);
303 v_String := FND_MESSAGE.GET;
304
305 RETURN v_String;
306 END;
307
308 FUNCTION GET_TEXT(inMessageName IN VARCHAR2,
309 inToken1 IN VARCHAR2, inValue1 IN VARCHAR2) RETURN VARCHAR2 IS
310 v_String VARCHAR2(2000);
311 BEGIN
312 FND_MESSAGE.SET_NAME('CZ', inMessageName);
313 FND_MESSAGE.SET_TOKEN(inToken1, inValue1);
314 v_String := FND_MESSAGE.GET;
315
316 RETURN v_String;
317 END;
318
319 FUNCTION GET_TEXT(inMessageName IN VARCHAR2,
320 inToken1 IN VARCHAR2, inValue1 IN VARCHAR2,
321 inToken2 IN VARCHAR2, inValue2 IN VARCHAR2) RETURN VARCHAR2 IS
322 v_String VARCHAR2(2000);
323 BEGIN
324 FND_MESSAGE.SET_NAME('CZ', inMessageName);
325 FND_MESSAGE.SET_TOKEN(inToken1, inValue1);
326 FND_MESSAGE.SET_TOKEN(inToken2, inValue2);
327 v_String := FND_MESSAGE.GET;
328
329 RETURN v_String;
330 END;
331
332 FUNCTION GET_TEXT(inMessageName IN VARCHAR2,
333 inToken1 IN VARCHAR2, inValue1 IN VARCHAR2,
334 inToken2 IN VARCHAR2, inValue2 IN VARCHAR2,
335 inToken3 IN VARCHAR2, inValue3 IN VARCHAR2) RETURN VARCHAR2 IS
336 v_String VARCHAR2(2000);
337 BEGIN
338 FND_MESSAGE.SET_NAME('CZ', inMessageName);
339 FND_MESSAGE.SET_TOKEN(inToken1, inValue1);
340 FND_MESSAGE.SET_TOKEN(inToken2, inValue2);
341 FND_MESSAGE.SET_TOKEN(inToken3, inValue3);
342 v_String := FND_MESSAGE.GET;
343
344 RETURN v_String;
345 END;
346
347 FUNCTION GET_TEXT(inMessageName IN VARCHAR2,
348 inToken1 IN VARCHAR2, inValue1 IN VARCHAR2,
349 inToken2 IN VARCHAR2, inValue2 IN VARCHAR2,
350 inToken3 IN VARCHAR2, inValue3 IN VARCHAR2,
351 inToken4 IN VARCHAR2, inValue4 IN VARCHAR2) RETURN VARCHAR2 IS
352 v_String VARCHAR2(2000);
353 BEGIN
354 FND_MESSAGE.SET_NAME('CZ', inMessageName);
355 FND_MESSAGE.SET_TOKEN(inToken1, inValue1);
356 FND_MESSAGE.SET_TOKEN(inToken2, inValue2);
357 FND_MESSAGE.SET_TOKEN(inToken3, inValue3);
358 FND_MESSAGE.SET_TOKEN(inToken4, inValue4);
359 v_String := FND_MESSAGE.GET;
360
361 RETURN v_String;
362 END;
363
364 FUNCTION GET_TEXT(inMessageName IN VARCHAR2,
365 inToken1 IN VARCHAR2, inValue1 IN VARCHAR2,
366 inToken2 IN VARCHAR2, inValue2 IN VARCHAR2,
367 inToken3 IN VARCHAR2, inValue3 IN VARCHAR2,
368 inToken4 IN VARCHAR2, inValue4 IN VARCHAR2,
369 inToken5 IN VARCHAR2, inValue5 IN VARCHAR2) RETURN VARCHAR2 IS
370 v_String VARCHAR2(2000);
371 BEGIN
372 FND_MESSAGE.SET_NAME('CZ', inMessageName);
373 FND_MESSAGE.SET_TOKEN(inToken1, inValue1);
374 FND_MESSAGE.SET_TOKEN(inToken2, inValue2);
378 v_String := FND_MESSAGE.GET;
375 FND_MESSAGE.SET_TOKEN(inToken3, inValue3);
376 FND_MESSAGE.SET_TOKEN(inToken4, inValue4);
377 FND_MESSAGE.SET_TOKEN(inToken5, inValue5);
379
380 RETURN v_String;
381 END;
382
383 FUNCTION GET_TEXT(inMessageName IN VARCHAR2,
384 inToken1 IN VARCHAR2, inValue1 IN VARCHAR2,
385 inToken2 IN VARCHAR2, inValue2 IN VARCHAR2,
386 inToken3 IN VARCHAR2, inValue3 IN VARCHAR2,
387 inToken4 IN VARCHAR2, inValue4 IN VARCHAR2,
388 inToken5 IN VARCHAR2, inValue5 IN VARCHAR2,
389 inToken6 IN VARCHAR2, inValue6 IN VARCHAR2) RETURN VARCHAR2 IS
390 v_String VARCHAR2(2000);
391 BEGIN
392 FND_MESSAGE.SET_NAME('CZ', inMessageName);
393 FND_MESSAGE.SET_TOKEN(inToken1, inValue1);
394 FND_MESSAGE.SET_TOKEN(inToken2, inValue2);
395 FND_MESSAGE.SET_TOKEN(inToken3, inValue3);
396 FND_MESSAGE.SET_TOKEN(inToken4, inValue4);
397 FND_MESSAGE.SET_TOKEN(inToken5, inValue5);
398 FND_MESSAGE.SET_TOKEN(inToken6, inValue6);
399 v_String := FND_MESSAGE.GET;
400
401 RETURN v_String;
402 END;
403
404 ----function that checks if installed languages are the same on the source and target server
405 ----returns 0 if correct validation else 1
406
407 FUNCTION check_installed_lang(p_server_id IN NUMBER)
408 RETURN NUMBER AS
409
410 TYPE ref_cursor IS REF CURSOR;
411
412 v_db_link VARCHAR2(128);
413 tgt_lang_cur ref_cursor;
414 v_return NUMBER;
415 x_error BOOLEAN:=FALSE;
419 v_db_link := retrieve_db_link(p_server_id);
416
417 BEGIN
418
420
421 IF(p_server_id <> 0 AND v_db_link IS NULL)THEN RETURN 1; END IF;
422
423 OPEN tgt_lang_cur FOR
424 'SELECT NULL FROM fnd_languages' || v_db_link || ' remote ' ||
425 ' WHERE installed_flag IN (''B'', ''I'') AND NOT EXISTS ' ||
426 ' (SELECT NULL FROM fnd_languages local ' ||
427 ' WHERE local.language_code = remote.language_code ' ||
428 ' AND local.installed_flag = remote.installed_flag) ' ||
429 'UNION ' ||
430 'SELECT NULL FROM fnd_languages local ' ||
431 ' WHERE installed_flag IN (''B'', ''I'') AND NOT EXISTS ' ||
432 ' (SELECT NULL FROM fnd_languages' || v_db_link || ' remote ' ||
433 ' WHERE local.language_code = remote.language_code ' ||
434 ' AND local.installed_flag = remote.installed_flag)';
435
436 LOOP
437 FETCH tgt_lang_cur INTO v_return;
438 EXIT WHEN tgt_lang_cur%NOTFOUND;
439
440 RETURN 1;
441 END LOOP;
442
443 RETURN 0;
444
445 EXCEPTION
446 WHEN OTHERS THEN
447 -- x_error:=CZ_UTILS.REPORT(SQLERRM,1,'CZ_UTILS.CHECK_INSTALLED_LANG',11276);
448 log_report('cz_utils', 'check_installed_lang', 1, SQLERRM, fnd_log.LEVEL_UNEXPECTED);
449 RETURN 1;
450 END check_installed_lang;
451
452 -------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>
453 -- Currently this procedure is not used at all. It is commented out because of
454 -- bug 3451160. If we do need this procdure later on, we will need to move it
455 -- to other package.
456 PROCEDURE report_html_tags IS
457 /*
458 TYPE StringTable IS TABLE OF VARCHAR2(10);
459
460 --Modify the list to include/exclude html tags to report. Tags in the list
461 --must be in capital letters.
462
463 searchList StringTable := StringTable('<APPLET',
464 '<SCRIPT',
465 '<OBJECT',
466 '<EMBED');
467
468 --Predefined reporting parameters, modify here if necessary.
469
470 defStatusCode NUMBER := 17000;
471 defUrgency NUMBER := 1;
472 defCaller VARCHAR2(40) := 'report_html_tags';
473
474 textString VARCHAR2(2000);
475
476 PROCEDURE REPORT(inMessage IN VARCHAR2) IS
477 BEGIN
478 log_report('CZ_UTILS', defCaller, defStatusCode, inMessage, fnd_log.LEVEL_STATEMENT);
479 -- INSERT INTO cz_db_logs (message, statuscode, caller, urgency, logtime)
480 -- VALUES (inMessage, defStatusCode, defCaller, defUrgency, SYSDATE);
481
482 END;
483 */
484 BEGIN
485 /*
486 --Checks for the DELETED_FLAG are intentionally omitted to make the search
487 --more extensive.
488
489 --cz_localized_text...
490
491 FOR c IN (SELECT intl_text_id, localized_str, language FROM cz_localized_texts
492 -- WHERE deleted_flag = '0'
493 ) LOOP
494
495 textString := UPPER(c.localized_str);
496 FOR i IN 1..searchList.COUNT LOOP
497
498 IF(INSTR(textString, searchList(i)) > 0)THEN
499
500 REPORT('HTML tag ' || searchList(i) ||
501 ' found in CZ_LOCALIZED_TEXTS table for INTL_TEXT_ID = ' || c.intl_text_id ||
502 ', LANGUAGE = ' || c.language ||
503 ': ' || c.localized_str);
504 END IF;
505 END LOOP;
506 END LOOP;
507
508 --cz_ps_nodes...
509
510 FOR c IN (SELECT ps_node_id, name FROM cz_ps_nodes
511 -- WHERE deleted_flag = '0'
512 ) LOOP
513
514 textString := UPPER(c.name);
515 FOR i IN 1..searchList.COUNT LOOP
516
517 IF(INSTR(textString, searchList(i)) > 0)THEN
518
519 REPORT('HTML tag ' || searchList(i) ||
520 ' found in CZ_PS_NODES table for PS_NODE_ID = ' || c.ps_node_id ||
521 ': ' || c.name);
522 END IF;
523 END LOOP;
524 END LOOP;
525
526 --fnd_new_messages...
527
528 FOR c IN (SELECT message_name, message_text FROM fnd_new_messages
529 WHERE application_id = 708) LOOP
530
531 textString := UPPER(c.message_text);
532 FOR i IN 1..searchList.COUNT LOOP
533
534 IF(INSTR(textString, searchList(i)) > 0)THEN
535
536 REPORT('HTML tag ' || searchList(i) ||
537 ' found in FND_NEW_MESSAGES table for MESSAGE_NAME = ' || c.message_name ||
538 ': ' || c.message_text);
539 END IF;
540 END LOOP;
541 END LOOP;
542
543 --cz_config_inputs...
544
545 FOR c IN (SELECT config_hdr_id, config_rev_nbr, config_input_id, input_val
546 FROM cz_config_inputs
547 WHERE input_type_code = 2
548 -- AND deleted_flag = '0'
549 ) LOOP
550
551 textString := UPPER(c.input_val);
552 FOR i IN 1..searchList.COUNT LOOP
553
554 IF(INSTR(textString, searchList(i)) > 0)THEN
555
556 REPORT('HTML tag ' || searchList(i) ||
557 ' found in CZ_CONFIG_INPUTS for CONFIG_HDR_ID = ' || c.config_hdr_id ||
558 ', CONFIG_REV_NBR = ' || c.config_rev_nbr ||
559 ', CONFIG_INPUT_ID = ' || c.config_input_id ||
560 ': ' || c.input_val);
561 END IF;
562 END LOOP;
563 END LOOP;
564
565 --cz_config_items...
566
567 FOR c IN (SELECT config_hdr_id, config_rev_nbr, config_item_id, item_val
568 FROM cz_config_items
569 WHERE value_type_code = 2
570 -- AND deleted_flag = '0'
571 ) LOOP
572
573
574 textString := UPPER(c.item_val);
578
575 FOR i IN 1..searchList.COUNT LOOP
576
577 IF(INSTR(textString, searchList(i)) > 0)THEN
579 REPORT('HTML tag ' || searchList(i) ||
580 ' found in CZ_CONFIG_ITEMS for CONFIG_HDR_ID = ' || c.config_hdr_id ||
581 ', CONFIG_REV_NBR = ' || c.config_rev_nbr ||
582 ', CONFIG_ITEM_ID = ' || c.config_item_id ||
583 ': ' || c.item_val);
584 END IF;
585 END LOOP;
586 END LOOP;
587 */ NULL;
588 END report_html_tags;
589
590 -------------------------------------------------
591 PROCEDURE LOG_REPORT(p_pkg_name VARCHAR2,
592 p_routine VARCHAR2,
593 p_ndebug NUMBER,
594 p_msg VARCHAR2,
595 p_log_level NUMBER)
596 IS
597 PRAGMA AUTONOMOUS_TRANSACTION;
598
599 l_module_name VARCHAR2(2000);
600
601 BEGIN
602 IF (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
603 l_module_name := 'cz.plsql.'||p_pkg_name||'.'||p_routine||'.'||p_ndebug;
604 FND_LOG.STRING(p_log_level,l_module_name,p_msg);
605 END IF;
606 COMMIT;
607 END LOG_REPORT;
608
609 -------------------------------
610 FUNCTION retrieve_db_link(p_server_id IN PLS_INTEGER)
611 RETURN VARCHAR2
612 IS
613 v_db_link cz_servers.fndnam_link_name%TYPE;
614
615 BEGIN
616 IF (p_server_id = 0) THEN
617 v_db_link := ' ';
618 ELSE
619 SELECT FNDNAM_LINK_NAME
620 INTO v_db_link
621 FROM cz_servers
622 WHERE cz_servers.server_local_id = p_server_id;
623 v_db_link := '@'||v_db_link||' ';
624 END IF;
625 RETURN v_db_link ;
626 EXCEPTION
627 WHEN NO_DATA_FOUND THEN
628 RETURN '-1';
629 WHEN OTHERS THEN
630 RETURN '-1';
631 END retrieve_db_link;
632
633 ---------------------------------------------
634
635 /* use this procedure to add an error message
636 to the stack. In some cases, the return status needs to be set,
637 use the statement x_return_status := FND_API.G_RET_STS_ERROR;
638 to set the error condition before calling this routine
639 */
640 PROCEDURE add_error_message_to_stack(p_message_name IN VARCHAR2,
641 p_token_name1 IN VARCHAR2 ,
642 p_token_value1 IN VARCHAR2 ,
643 p_token_name2 IN VARCHAR2 ,
644 p_token_value2 IN VARCHAR2 ,
645 p_token_name3 IN VARCHAR2 ,
646 p_token_value3 IN VARCHAR2 ,
647 x_msg_count IN OUT NOCOPY NUMBER,
648 x_msg_data IN OUT NOCOPY VARCHAR2) IS
649
650 BEGIN
651 FND_MESSAGE.SET_NAME('CZ', p_message_name);
652 IF p_token_name1 IS NOT NULL THEN
653 FND_MESSAGE.SET_TOKEN(p_token_name1, p_token_value1);
654 END IF;
655 IF p_token_name2 IS NOT NULL THEN
656 FND_MESSAGE.SET_TOKEN(p_token_name2, p_token_value2);
657 END IF;
658 IF p_token_name3 IS NOT NULL THEN
659 FND_MESSAGE.SET_TOKEN(p_token_name3, p_token_value3);
660 END IF;
661 FND_MSG_PUB.ADD;
662 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
663 p_data => x_msg_data);
664 END add_error_message_to_stack;
665
666
667
668 /* use this procedure to add an error message
669 to the stack. In some cases, the return status needs to be set,
670 use the statement x_return_status := FND_API.G_RET_STS_ERROR;
671 to set the error condition before calling this routine
672 */
673 PROCEDURE add_error_message_to_stack(p_message_name IN VARCHAR2,
674 p_token_name1 IN VARCHAR2 ,
675 p_token_value1 IN VARCHAR2 ,
676 x_msg_count IN OUT NOCOPY NUMBER,
677 x_msg_data IN OUT NOCOPY VARCHAR2) IS
678
679 BEGIN
680 FND_MESSAGE.SET_NAME('CZ', p_message_name);
681 IF p_token_name1 IS NOT NULL THEN
682 FND_MESSAGE.SET_TOKEN(p_token_name1, p_token_value1);
683 END IF;
684 FND_MSG_PUB.ADD;
685 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
686 p_data => x_msg_data);
687 END add_error_message_to_stack;
688
689
690
691 /* use this procedure to add an error message
692 to the stack. In some cases, the return status needs to be set,
693 use the statement x_return_status := FND_API.G_RET_STS_ERROR;
694 to set the error condition before calling this routine
695 */
696 PROCEDURE add_error_message_to_stack(p_message_name IN VARCHAR2,
697 p_token_name1 IN VARCHAR2 ,
698 p_token_value1 IN VARCHAR2 ,
699 p_token_name2 IN VARCHAR2 ,
700 p_token_value2 IN VARCHAR2 ,
701 x_msg_count IN OUT NOCOPY NUMBER,
702 x_msg_data IN OUT NOCOPY VARCHAR2) IS
703
704 BEGIN
705 FND_MESSAGE.SET_NAME('CZ', p_message_name);
706 IF p_token_name1 IS NOT NULL THEN
707 FND_MESSAGE.SET_TOKEN(p_token_name1, p_token_value1);
708 END IF;
709 IF p_token_name2 IS NOT NULL THEN
710 FND_MESSAGE.SET_TOKEN(p_token_name2, p_token_value2);
711 END IF;
712 FND_MSG_PUB.ADD;
713 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
714 p_data => x_msg_data);
715 END add_error_message_to_stack;
716
717
718
719 /* use this procedure to add an error message
720 to the stack. In some cases, the return status needs to be set,
721 use the statement return_status := FND_API.G_RET_STS_ERROR, and
725
722 add msg_count := 1 to initialize the message coutnt
723 to set the error condition before calling this routine
724 */
726 PROCEDURE add_exc_msg_to_fndstack
727 (p_package_name IN VARCHAR2,
728 p_procedure_name IN VARCHAR2,
729 p_error_message IN VARCHAR2)
730 IS
731 l_msg_data VARCHAR2(32000);
732 BEGIN
733 fnd_msg_pub.add_exc_msg(p_package_name, p_procedure_name, p_error_message);
734 END add_exc_msg_to_fndstack;
735 ---------------------------------------------
736
737 END CZ_UTILS;
[Home] [Help]