[Home] [Help]
PACKAGE BODY: APPS.MSD_VALIDATE
Source
1 package body msd_validate as
2 /* $Header: msdvaleb.pls 115.30 2004/05/12 16:59:36 jarora ship $ */
3
4 v_m2a_dblink VARCHAR2(128);
5 v_a2m_dblink VARCHAR2(128);
6 v_instance_type number;
7 v_apps_ver number;
8 v_user_name VARCHAR2(100):= NULL;
9 v_resp_name VARCHAR2(100):= NULL;
10 v_application_name VARCHAR2(240):= NULL;
11 v_application_code varchar2(3);
12
13 v_instance_id number;
14 v_plan_id number;
15 v_instance_code varchar2(4);
16 v_cp_enabled NUMBER;
17 procedure write_output(p_text in varchar2) is
18 begin
19 /* remove occurences of null string and write to output */
20 fnd_file.put_line(fnd_file.output, replace(p_text, fnd_global.local_chr(0)));
21 /* dbms_output.put_line(p_text);
22 insert into msd_test values(p_text);*/
23 end write_output;
24
25
26 procedure write_log(p_text in varchar2) is
27 begin
28 fnd_file.put_line(fnd_file.log, p_text);
29 /*insert into msd_test values(p_text);*/
30 end write_log;
31
32
33
34 procedure run_validation(errbuf out nocopy varchar2,
35 retcode out nocopy varchar2,
36 p_function in varchar2) is
37
38 begin
39 run_validation(errbuf => errbuf,
40 retcode => retcode,
41 p_function => p_function,
42 p_detail => '1',
43 p_application_code => 'MSD',
44 p_token1 => null,
45 p_token2 => null,
46 p_token3 => null);
47 end;
48
49
50 procedure run_validation(errbuf out nocopy varchar2,
51 retcode out nocopy varchar2,
52 p_function in varchar2,
53 p_detail in varchar2) is
54
55 begin
56 run_validation(errbuf => errbuf,
57 retcode => retcode,
58 p_function => p_function,
59 p_detail => p_detail,
60 p_application_code => 'MSD',
61 p_token1 => null,
62 p_token2 => null,
63 p_token3 => null);
64 end;
65
66
67 procedure run_validation_all (errbuf out nocopy varchar2,
68 retcode out nocopy varchar2,
69 p_application_code in varchar2,
70 p_function in varchar2,
71 p_plan_id in number,
72 p_instance_id in number,
73 p_report_type in varchar2) is
74 begin
75 run_validation_all (errbuf => errbuf,
76 retcode => retcode,
77 p_application_code => p_application_code,
78 p_function => p_function,
79 p_plan_id => p_plan_id,
80 p_instance_id => p_instance_id,
81 p_report_type => p_report_type,
82 p_token1 => null,
83 p_token2 => null,
84 p_token3 => null);
85 end;
86
87 /* run_validation is the original procedure called by MSD. Retaining this for existing customers
88 of MSD. This package will call run_validation_all. This procedure is now a wrapper procedure */
89
90 procedure run_validation(errbuf out nocopy varchar2,
91 retcode out nocopy varchar2,
92 p_function in varchar2,
93 p_detail in varchar2,
94 p_application_code varchar2,
95 p_token1 in number,
96 p_token2 in number,
97 p_token3 in number) is
98
99 lv_errbuf VARCHAR2(500);
100 lv_ret_code number ;
101 lv_report_type varchar2(1);
102
103 begin
104
105 select DECODE(p_detail,'1','2','2','1') into lv_report_type from DUAL;
106 run_validation_all (lv_errbuf,
107 lv_ret_code,
108 'MSD',
109 p_function,
110 -1, --plan
111 0, --instance
112 lv_report_type, -- Detail report complete
113 p_token1 ,
114 p_token2 ,
115 p_token3 );
116
117 errbuf:= lv_errbuf;
118 retcode := lv_ret_code;
119
120 end run_validation;
121
122 /* Added generic procedure run_all_validation for MSC, MSD, MSR, MSO, ATP, UI*/
123 /* report type 1 = SUMMARY
124 2 = Complete Detail
125 3 = Detail with Errors
126 4 = Detail with Errors and Warnings */
127 PROCEDURE RUN_VALIDATION_ALL (errbuf out nocopy varchar2,
128 retcode out nocopy varchar2,
129 p_application_code in varchar2,
130 p_function in varchar2,
131 p_plan_id in number,
132 p_instance_id in number,
133 p_report_type in varchar2, -- Detail report complete
134 p_token1 in number,
135 p_token2 in number,
136 p_token3 in number) is
137
138 TYPE CurTyp is ref cursor;
139 TYPE SelectRec is record(c1 msd_audit_sql_statements.column1%TYPE,
140 c2 msd_audit_sql_statements.column1%TYPE,
141 c3 msd_audit_sql_statements.column1%TYPE,
142 c4 msd_audit_sql_statements.column1%TYPE,
143 c5 msd_audit_sql_statements.column1%TYPE,
144 c6 msd_audit_sql_statements.column1%TYPE,
145 c7 msd_audit_sql_statements.column1%TYPE,
146 c8 msd_audit_sql_statements.column1%TYPE,
147 c9 msd_audit_sql_statements.column1%TYPE,
148 c10 msd_audit_sql_statements.column1%TYPE,
149 c11 msd_audit_sql_statements.column1%TYPE);
150
151 p_summary boolean := (p_report_type = '1');
152 write_label boolean := FALSE;
153 existing_dp_functionality boolean := FALSE;
154 label varchar2(4000);
155 log_label varchar2(4000);
156 p_report_type_summ boolean ;
157 p_report_type_err boolean ;
158 p_report_type_warn_err boolean ;
159 noop boolean := true;
160 v_function_name varchar2(200);
161 v_appl_name varchar2(200);
162 lv_where_clause varchar2(1);
163 lv_count number := 0;
164 lv_count_summ number := 0;
165
166 cv CurTyp;
167 selrow SelectRec;
168
169 v_sql_stmt long;
170 norows boolean := true;
171 str varchar2(4000);
172 log_str varchar2(4000);
173
174 cursor statements (c_application_code in varchar2) is
175 select * from msd_audit_sql_statements
176 where function = p_function
177 and application_code = c_application_code
178 and nvl(enabled, 'Y') = 'Y'
179 order by statement_id, STATEMENT_DESCRIPTION;
180
181 cursor c_instances(c_instance_id in number) is
182 SELECT DECODE( M2A_DBLINK, NULL, NULL_DBLINK, '@'||M2A_DBLINK||' ') M2A_DBLINK,
183 INSTANCE_TYPE, apps_ver,
184 DECODE( A2M_DBLINK, NULL, NULL_DBLINK, '@'||A2M_DBLINK||' ') A2M_DBLINK,
185 instance_code,
186 instance_id
187 FROM MSC_APPS_INSTANCES
188 where instance_id = decode(c_instance_id, -1, instance_id,
189 0, instance_id,
190 c_instance_id);
191
192
193 BEGIN
194 retcode := '0';
195 v_instance_id := nvl(p_INSTANCE_ID,-1);
196 v_application_code := p_application_code;
197 v_plan_id := p_plan_id;
198 IF v_instance_id = 0 THEN
199 existing_dp_functionality := TRUE;
200 v_application_code := p_application_code;
201 END IF;
202 /*
203 The application Code being passed are MSC, MSD, MSD, IO, ATP
204 For IO and ATP THe appropriate application code needs to be populated
205 The following combinations need to be converted appropriate application_code
206 application ATP + function COLL_DATA ---> application MSC
207 application ATP + function ATP_DATA ---> application MSC
208 application IO + function IO_DATA ---> application MSO
209 application IO + function COLL_DATA ---> application MSC
210 application IO + function UI_DATA ---> application MSC
211 HLS queries will be seeded as MSC
212 */
213
214 IF existing_dp_functionality = FALSE THEN
215 /* With ATP COLL_DATA and ATP_DATA are being passed */
216 IF p_application_code = 'ATP' THEN
217 v_application_code := 'MSC';
218 END IF;
219 /* With IO COLL_DATA , UI_DATA and IO_DATA are being passed */
220 IF p_application_code = 'IO' and p_function in ('COLL_DATA','UI_DATA') THEN
221 v_application_code := 'MSC';
222 END IF;
223 IF p_application_code = 'IO' and p_function = 'IO_DATA' THEN
224 v_application_code := 'MSR';
225 END IF;
226
227 /* For USER DEFINED CUSTOM QUERIES functions which are not in the seeded functions
228 For IO and ATP THe appropriate application code needs to be populated*/
229 IF p_application_code = 'ATP' THEN
230 v_application_code := 'MSC';
231 END IF;
232
233 IF p_application_code = 'IO'
234 and p_function not in ('COLL_DATA','UI_DATA', 'IO_DATA', 'PLN_DATA','ATP_DATA',
235 'FACT_DATA','LEVEL_VALUES')
236 THEN v_application_code := 'MSR';
237 END IF;
238
239
240 END IF; --existing DP functionality
241
242 -- If an instance is passed
243 IF v_instance_id not in (-1,0) THEN
244 BEGIN
245 SELECT DECODE( M2A_DBLINK, NULL, NULL_DBLINK, '@'||M2A_DBLINK||' '),
246 INSTANCE_TYPE, apps_ver,
247 DECODE( A2M_DBLINK, NULL, NULL_DBLINK, '@'||A2M_DBLINK||' '),
248 instance_code
249 INTO v_m2a_dblink,
250 v_instance_type, v_apps_ver,
251 v_a2m_dblink,
252 v_instance_code
253 FROM MSC_APPS_INSTANCES
254 WHERE INSTANCE_ID= p_INSTANCE_ID;
255
256
257 EXCEPTION
258
259 WHEN NO_DATA_FOUND THEN
260 RETCODE := 2;
261 FND_MESSAGE.SET_NAME('MSC', 'MSC_DP_INVALID_INSTANCE_ID');
262 FND_MESSAGE.SET_TOKEN('INSTANCE_ID', p_INSTANCE_ID);
263 ERRBUF:= FND_MESSAGE.GET;
264 RETURN;
265 WHEN OTHERS THEN
266 RETCODE := 2;
267 ERRBUF := SQLERRM;
268 RETURN;
269
270
271 END;
272 END IF;
273 /* Get the User Information */
274 SELECT FND_GLOBAL.USER_NAME,
275 FND_GLOBAL.RESP_NAME,
276 FND_GLOBAL.APPLICATION_NAME
277 INTO v_user_name,
278 v_resp_name,
279 v_application_name
280 FROM dual;
281
282 /* If the input to Instances is ALL INSTANCES (-1) THEN LOOP thru all the instances
283 If the input to Instances is 0 It indicates that the existing DP Reports are making a call
284 exit after first loop. This is being done to retain existing functionality of DP
285 If an actual instance is passed then the loop will only work once */
286 For c_inst in c_instances (v_instance_id) LOOP --Instance Loop
287
288 v_instance_id := c_inst.instance_id;
289 v_a2m_dblink := c_inst.a2m_dblink;
290 v_m2a_dblink := c_inst.m2a_dblink;
291 v_apps_ver := c_inst.apps_ver;
292 v_instance_code := c_inst.instance_code;
293 v_instance_type := c_inst.instance_type;
294
295
296 /* get descriptive name */
297 begin
298 select meaning
299 into v_function_name
300 from fnd_lookup_values_vl
301 where lookup_type = 'MSD_AUDIT_REPORT'
302 and lookup_code = p_function;
303
304 EXCEPTION
305 when others then
306 v_function_name := p_function;
307 end;
308
309 IF existing_dp_functionality THEN /* For existing functionality of DP*/
310 str := get_translated_string('MSD_AUDIT_REPORT_TITLE', 'MSD', 'REPORT_TITLE', v_function_name);
311 write_output('<title>' || str || '</title>'||
312 '<h3>' || str || '</h3>');
313 write_log(str);
314 ELSE
315 str := get_translated_string('MSC_AUDIT_REPORT_TITLE', 'MSC', 'REPORT_TITLE', v_function_name);
316 str := str || ' For Instance '||v_instance_code;
317 write_output('<title>' || str || '</title>'||
318 '<h3>' || str || '</h3>');
319 write_log(str);
320
321 END IF;
322
323 for s in statements(v_application_code) loop
324 lv_count := 0;
325 lv_count_summ := 0;
326 noop := false;
327 norows := true;
328 write_label:= true;
329
330 /* Generate sql for dynamic cursor. The goal is to generate columns
331 of the form <td>column</td> for all the non-null COLUMN columns.
332 */
333 if s.summary_message_only = 'Y' then
334 /* query should only return tokenized summary message rows */
335
336 v_sql_stmt :=
337 'SELECT ' ||
338 get_td_tag('msd_validate.get_translated_string(' ||
339 '''' || s.summary_message || ''', ' ||
340 '''' || v_application_code || ''', ' ||
341 '''' || s.summary_token1 || ''', ' ||
342 nvl(s.summary_token1_value,'''''') || ', ' ||
343 '''' || s.summary_token2 || ''', ' ||
344 nvl(s.summary_token2_value,'''''') || ', ' ||
345 '''' || s.summary_token3 || ''', ' ||
346 nvl(s.summary_token3_value,'''''') || ')') ||
347 ', '''', '''', '''', '''', '''', '''', '''', '''', '''', '''' ';
348 else
349 /* select all query columns */
350 v_sql_stmt :=
351 'SELECT ' ||
352 get_td_tag(s.column1) || ',' ||
353 get_td_tag(s.column2) || ',' ||
354 get_td_tag(s.column3) || ',' ||
355 get_td_tag(s.column4) || ',' ||
356 get_td_tag(s.column5) || ',' ||
357 get_td_tag(s.column6) || ',' ||
358 get_td_tag(s.column7) || ',' ||
359 get_td_tag(s.column8) || ',' ||
360 get_td_tag(s.column9) || ',' ||
361 get_td_tag(s.column10) || ',' ||
362 get_td_tag(s.column11);
363 end if;
364
365 select decode(nvl(s.where_clause,'N'),'N','N','Y') into lv_where_clause from dual;
366 If lv_where_clause = 'Y' then
367 v_sql_stmt := v_sql_stmt || ' FROM ' || replace(s.from_clause,'@M2A_DBLINK',v_m2a_dblink) ||
368 ' WHERE '|| replace(replace(replace(replace(replace(s.where_clause,'@M2A_DBLINK',v_m2a_dblink),'@INSTANCE_ID',v_instance_id),'@PLAN_ID',v_plan_id),'@INSTANCE_CODE',v_instance_code),'@A2M_DBLINK',v_a2m_dblink);
369 else
370 v_sql_stmt := v_sql_stmt || ' FROM ' || replace(s.from_clause,'@M2A_DBLINK',v_m2a_dblink);
371
372 end if;
373
374 v_sql_stmt := replace(replace(v_sql_stmt,'@USER_NAME',v_user_name),'@RESP_NAME',v_resp_name);
375
376
377 /* process this sql statement */
378 write_output('<i>'||
379 get_translated_string(s.statement_description,
380 s.application_code) ||
381 '</i><br>');
382 write_log( get_translated_string(s.statement_description, s.application_code) );
383
384
385 begin
386 write_output('<table border=1 cellspacing=1 cellpadding=1>');
387
388 /* open cursor for tokenized query */
389 if (p_token1 is null) then
390 open cv for v_sql_stmt;
391 elsif (p_token2 is null) then
392 open cv for v_sql_stmt using p_token1;
393 elsif (p_token3 is null) then
394 open cv for v_sql_stmt using p_token1, p_token2;
395 else
396 open cv for v_sql_stmt using p_token1, p_token2, p_token3;
397 end if;
398
399 loop
400 fetch cv into selrow;
401 exit when cv%NOTFOUND;
402
403 /* warning code */
404 retcode := '1';
405
406 /* write header row if detailed output is needed */
407 if (norows and
408 not(p_summary) and
409 nvl(s.summary_message_only, 'N') <> 'Y') then
410 log_str := ' ' ||
411 ' ' ||
412 get_translated_string(s.description1, s.application_code) ||
413 ' ' ||
414 get_translated_string(s.description2, s.application_code) ||
415 ' ' ||
416 get_translated_string(s.description3, s.application_code) ||
417 ' ' ||
418 get_translated_string(s.description4, s.application_code) ||
419 ' ' ||
420 get_translated_string(s.description5, s.application_code) ||
421 ' ' ||
422 get_translated_string(s.description6, s.application_code) ||
423 ' ' ||
424 get_translated_string(s.description7, s.application_code) ||
425 ' ' ||
426 get_translated_string(s.description8, s.application_code) ||
427 ' ' ||
428 get_translated_string(s.description9, s.application_code) ||
429 ' ' ||
430 get_translated_string(s.description10, s.application_code) ||
431 ' ' ||
432 get_translated_string(s.description11, s.application_code) ||
433 ' ' ;
434 log_str := replace (replace (log_str, '<td>',' '),'</td>',' ');
435
436 str := '<tr>' ||
437 '<th>' ||
438 get_translated_string(s.description1, s.application_code) ||
439 '</th><th>' ||
440 get_translated_string(s.description2, s.application_code) ||
441 '</th><th>' ||
442 get_translated_string(s.description3, s.application_code) ||
443 '</th><th>' ||
444 get_translated_string(s.description4, s.application_code) ||
445 '</th><th>' ||
446 get_translated_string(s.description5, s.application_code) ||
447 '</th><th>' ||
448 get_translated_string(s.description6, s.application_code) ||
449 '</th><th>' ||
450 get_translated_string(s.description7, s.application_code) ||
451 '</th><th>' ||
452 get_translated_string(s.description8, s.application_code) ||
453 '</th><th>' ||
454 get_translated_string(s.description9, s.application_code) ||
455 '</th><th>' ||
456 get_translated_string(s.description10, s.application_code) ||
457 '</th><th>' ||
458 get_translated_string(s.description11, s.application_code) ||
459 '</th></tr>';
460
461 str := replace(str, '<th></th>');
462 label:=str;
463 log_label := log_str;
464 -- write_output(str);
465 end if;
466
467 norows := false;
468
469 log_str := ' ' || selrow.c1 || selrow.c2 || selrow.c3 || selrow.c4 ||selrow.c5 ||
470 selrow.c6 || selrow.c7 || selrow.c8 || selrow.c9 ||selrow.c10 || selrow.c11 || ' ';
471 log_str := replace (replace (log_str, '<td>',' '),'</td>',' ');
472
473 str := '<tr>' || selrow.c1 || selrow.c2 || selrow.c3 || selrow.c4 ||selrow.c5 ||
474 selrow.c6 || selrow.c7 || selrow.c8 || selrow.c9 || selrow.c10 || selrow.c11 || '</tr>';
475 str := replace(str, '<td></td>', '<td> </td>');
476
477 /* The Summary report will print a count for Warnings and Errors only */
478 p_report_type_summ := ((instr(str,'INVALID') + instr(str,'ERROR') + instr(str,'WARNING')) <> 0);
479 IF p_report_type_summ THEN
480 lv_count_summ := lv_count_summ + 1 ;
481 END IF;
482 /* The Summary report will print a count for Warnings and Errors only */
483
484 /* write to output */
485 if not(p_summary) then
486 -- All Details
487 IF p_report_type = '2' THEN
488 IF write_label then
489 write_output(label);
490 write_log(log_label);
491 write_label := FALSE;
492 END IF;
493 lv_count:= lv_count + 1;
494 write_output(str);
495 write_log(log_str);
496 END IF;
497 -- Errors, Invalids only
498 p_report_type_err := ((instr(str,'INVALID') + instr(str,'ERROR')) <> 0);
499 IF p_report_type = '3' and p_report_type_err THEN
500 IF write_label then
501 write_output(label);
502 write_log(log_label);
503 write_label := FALSE;
504 END IF;
505 lv_count:= lv_count + 1;
506 write_output(str);
507 write_log(log_str);
508 END IF;
509 -- Errors and Warnings Only
510 p_report_type_warn_err := ((instr(str,'WARNING') + instr(str,'ERROR')) <> 0);
511 IF p_report_type = '4' and p_report_type_warn_err THEN
512 IF write_label then
513 write_output(label);
514 write_log(log_label);
515 write_label := FALSE;
516 END IF;
517 lv_count:= lv_count + 1;
518 write_output(str);
519 write_log(log_str);
520 END IF;
521
522 end if;
523
524 end loop;
525
526 /* print error count */
527 if not(p_summary) then --1
528 if (lv_count = 0) then -- 2
529 str := get_translated_string('MSD_AUDIT_NO_ERRORS');
530 else
531 str := '';
532 if s.error_message is not null then --3
533 str := get_translated_string(s.error_message, v_application_code, 'COUNT', lv_count);
534 end if; --3
535 end if; --2
536 else --1
537 IF existing_dp_functionality THEN --2
538 if (cv%rowcount = 0) then --3
539 str := get_translated_string('MSD_AUDIT_NO_ERRORS');
540 else
541 str := '';
542 if s.error_message is not null then --4
543 str := get_translated_string(s.error_message, v_application_code, 'COUNT', cv%rowcount);
544 end if; --4
545 end if; --3
546 ELSE --2
547 if (lv_count_summ = 0) then --3
548 str := get_translated_string('MSD_AUDIT_NO_ERRORS');
549 else
550 str := '';
551 if s.error_message is not null then --4
552 str := get_translated_string(s.error_message, v_application_code, 'COUNT', lv_count_summ);
553 end if; --4
554 end if; --3
555 END IF; --2
556 end if; --1
557 log_str := str;
558 write_output('</table>' || str || '<br><br>');
559 write_log (log_str);
560 close cv;
561 exception
562 when others then
563 write_output('</table><font color=red>' ||
564 get_translated_string('MSD_AUDIT_SQL_ERROR') ||
565 '</font><br><br>');
566 write_log('Error executing statement: ' || v_sql_stmt);
567 write_log(substr(sqlerrm, 1, 150));
568 retcode := '1';
569 end;
570
571
572 end loop;
573
574 if noop then
575 retcode := 1;
576
577 /* get application name */
578 begin
579 select application_name
580 into v_appl_name
581 from fnd_application_vl
582 where application_short_name = v_application_code;
583
584 EXCEPTION
585 when others then
586 v_appl_name := v_application_code;
587 end;
588
589 str := get_translated_string('MSD_AUDIT_NO_STATEMENTS', 'MSD',
590 'REPORT', v_function_name,
591 'APPLICATION', v_appl_name) || '<br>';
592
593 write_output(str);
594 end if;
595
596 IF existing_dp_functionality THEN
597 EXIT ;
598 END IF; -- If called from existing DP Reports then loop once
599
600 END LOOP; --Instance Loop
601 EXCEPTION
602 when others then
603 retcode := '2';
604 errbuf := substr(SQLERRM,1,150);
605
606 end run_validation_all ;
607
608
609 /*
610 If str is null then return ''
611 otherwise, return <td>||str||</td>
612 */
613 function get_td_tag(string varchar2) return varchar2 is
614 begin
615 if (string is null)
616 then return '''''';
617 else return ('''<td>''|| ' || string || ' || ''</td>''');
618 end if;
619 end get_td_tag;
620
621
622 /*
623 Get translated message
624 */
625 function get_translated_string(string varchar2) return varchar2 is
626 begin
627 return
628 get_translated_string(string => string,
629 appcode => 'MSD',
630 p_token1 => null,
631 p_token1_value => null,
632 p_token2 => null,
633 p_token2_value => null,
634 p_token3 => null,
635 p_token3_value => null);
636 end;
637
638 /*
639 Get translated message
640 */
641 function get_translated_string(string varchar2,
642 appcode varchar2) return varchar2 is
643 begin
644 return
645 get_translated_string(string => string ,
646 appcode => appcode,
647 p_token1 => null,
648 p_token1_value => null,
649 p_token2 => null,
650 p_token2_value => null,
651 p_token3 => null,
652 p_token3_value => null);
653 end;
654
655 /*
656 Get translated message
657 */
658 function get_translated_string(string varchar2,
659 appcode varchar2,
660 p_token1 varchar2,
661 p_token1_value varchar2) return varchar2 is
662 begin
663 return
664 get_translated_string(string => string ,
665 appcode => appcode,
666 p_token1 => p_token1,
667 p_token1_value => p_token1_value,
668 p_token2 => null,
669 p_token2_value => null,
670 p_token3 => null,
671 p_token3_value => null);
672 end;
673
674
675
676 /*
677 Get translated message
678 */
679 function get_translated_string(string varchar2,
680 appcode varchar2,
681 p_token1 varchar2,
682 p_token1_value varchar2,
683 p_token2 varchar2,
684 p_token2_value varchar2) return varchar2 is
685 begin
686 return
687 get_translated_string(string => string ,
688 appcode => appcode,
689 p_token1 => p_token1,
690 p_token1_value => p_token1_value,
691 p_token2 => p_token2,
692 p_token2_value => p_token2_value,
693 p_token3 => null,
694 p_token3_value => null);
695 end;
696
697
698
699 /*
700 Get translated message
701 */
702 function get_translated_string(string varchar2,
703 appcode varchar2,
704 p_token1 varchar2,
705 p_token1_value varchar2,
706 p_token2 varchar2,
707 p_token2_value varchar2,
708 p_token3 varchar2,
709 p_token3_value varchar2) return varchar2 is
710 begin
711
712 fnd_message.set_name(appcode, string);
713
714 /* set message tokens */
715 if p_token1 is not null then
716 fnd_message.set_token(p_token1, p_token1_value);
717 end if;
718 if p_token2 is not null then
719 fnd_message.set_token(p_token2, p_token2_value);
720 end if;
721 if p_token3 is not null then
722 fnd_message.set_token(p_token3, p_token3_value);
723 end if;
724
725 return fnd_message.get;
726
727 exception
728 when others then
729 return string;
730
731 end get_translated_string;
732
733
734 /*
735 Returns 0 if p_sr_pk is a valid source level pk for the
736 given level_id and instance, -1 otherwise
737 */
738 function is_valid_sr_pk(p_sr_pk varchar2, p_level_id number, p_instance varchar2) return varchar2 is
739 v_count number;
740 begin
741 select count(*)
742 into v_count
743 from msd_level_values
744 where level_id = p_level_id
745 and instance = p_instance
746 and sr_level_pk = p_sr_pk;
747
748
749 if (v_count > 0) then
750 return 0; /*ok*/
751 else
752 return -1; /*error*/
753 end if;
754
755 end is_valid_sr_pk;
756
757
758 end;