[Home] [Help]
PACKAGE BODY: APPS.QP_FORMULA_RULES_PVT
Source
1 PACKAGE BODY QP_FORMULA_RULES_PVT AS
2 /* $Header: QPXPFORB.pls 120.2.12010000.2 2008/08/12 08:59:53 smbalara ship $ */
3
4 g_schema VARCHAR2(30);
5 g_conc_mode VARCHAR2(1);
6 line_number NUMBER := 0;
7 segment_ctr NUMBER := 0;
8
9
10 PROCEDURE Put_Line (Text Varchar2)
11 IS
12 BEGIN
13
14 if g_conc_mode is null then
15
16 if nvl(fnd_profile.value('CONC_REQUEST_ID'),0) <> 0 then
17 g_conc_mode := 'Y';
18 else
19 g_conc_mode := 'N';
20 end if;
21
22 end if;
23
24 if g_conc_mode = 'Y' then
25 FND_FILE.PUT_LINE(FND_FILE.LOG, Text);
26 end if;
27
28 END Put_Line;
29
30 PROCEDURE Init_Applsys_Schema
31 IS
32 l_app_info BOOLEAN;
33 l_status VARCHAR2(30);
34 l_industry VARCHAR2(30);
35 BEGIN
36
37 if g_schema is null then
38
39 l_app_info := FND_INSTALLATION.GET_APP_INFO
40 ('FND',l_status, l_industry, g_schema);
41
42 end if;
43
44 END;
45
46 PROCEDURE New_Line
47 IS
48 BEGIN
49
50 line_number := line_number + 1;
51 ad_ddl.build_package(' ',line_number);
52 -- oe_debug_pub.add(' ');
53
54 END New_Line;
55
56 PROCEDURE Text
57 ( p_string IN VARCHAR2
58 , p_level IN NUMBER default 1
59 )
60 IS
61 BEGIN
62
63 line_number := line_number + 1;
64 ad_ddl.build_package(LPAD(p_string,p_level*2+LENGTH(p_string)),line_number);
65 -- oe_debug_pub.add(LPAD(p_string,p_level*2+LENGTH(p_string)));
66 --dbms_output.put_line(LPAD(p_string,p_level*2+LENGTH(p_string)));
67
68 END text;
69
70 PROCEDURE Formula_Text
71 ( p_string IN VARCHAR2
72 )
73 IS
74 TYPE T_FORMULA_TAB_TYPE IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
75 l_formula_tab T_FORMULA_TAB_TYPE;
76 l_formula varchar2(32000) := p_string;
77 l_length number;
78 l_start number:=1;
79 i integer := 0;
80 j integer := 1;
81 BEGIN
82 l_length := length(l_formula);
83 select replace(l_formula,'''','''''') into l_formula from dual;--6726052,7249280 smbalara
84 while (l_length > 200)
85 loop
86 i := i + 1;
87 l_formula_tab(i) := substr(l_formula,l_start,200);
88 l_start := l_start + 200;
89 l_length := l_length - 200;
90 end loop;
91 i := i + 1;
92 l_formula_tab(i) := substr(l_formula,l_start,200);
93
94 Text('''''',1);
95 while (j <= i)
96 loop
97 Text(' || ''' || l_formula_tab(j) || '''',2);
98 j := j + 1;
99 end loop;
100
101 END Formula_Text;
102
103 PROCEDURE Comment
104 ( p_comment IN VARCHAR2
105 , p_level IN NUMBER default 1
106 )
107 IS
108 BEGIN
109
110 Text('-- '||p_comment,p_level);
111
112 END Comment;
113
114 PROCEDURE Break_Text(p_string IN VARCHAR2, p_level IN NUMBER default 1)
115 IS
116
117 l_value_string varchar2(32000) := p_string;
118 l_temp1 varchar2(32000);
119 l_temp2 varchar2(32000);
120
121 lp_position number := 0;
122 rp_position number := 0;
123 c_position number := 0;
124 s_position number := 0;
125 p number := 0;
126
127 begin
128
129 while length(l_value_string) > 200 loop
130
131 lp_position := 0;
132 rp_position := 0;
133 c_position := 0;
134 s_position := 0;
135 p := 0;
136
137 lp_position := instr(l_value_string,'(');
138 rp_position := instr(l_value_string,')');
139 c_position := instr(l_value_string,',');
140 s_position := instr(l_value_string,' ');
141
142 if (lp_position > 0) and (p = 0) then
143 p := lp_position;
144 elsif (c_position > 0) and (p = 0) then
145 p:= c_position;
146 elsif (s_position > 0) and (p = 0) then
147 p:= s_position;
148 elsif (rp_position > 0) and (p = 0) then
149 p:= rp_position;
150 end if;
151
152 if (lp_position > 0) and (lp_position <= 200) then
153 l_temp1 := substr(l_value_string,1,lp_position);
154 l_temp2 := substr(l_value_string,lp_position+1);
155 l_value_string := l_temp2;
156 Text(l_temp1 , p_level);
157 elsif (c_position > 0) and (c_position <= 200) then
158 l_temp1 := substr(l_value_string,1,c_position);
159 l_temp2 := substr(l_value_string,c_position+1);
160 l_value_string := l_temp2;
161 Text(l_temp1 , p_level);
162 elsif (s_position > 0) and (s_position <= 200) then
163 l_temp1 := substr(l_value_string,1,s_position);
164 l_temp2 := substr(l_value_string,s_position+1);
165 l_value_string := l_temp2;
166 Text(l_temp1 , p_level);
167 elsif (rp_position > 0) and (rp_position <= 200) then
168 l_temp1 := substr(l_value_string,1,rp_position);
169 l_temp2 := substr(l_value_string,rp_position+1);
170 l_value_string := l_temp2;
171 Text(l_temp1 , p_level);
172 else
173 l_temp1 := substr(l_value_string,1,p);
174 l_temp2 := substr(l_value_string,p+1);
175 l_value_string := l_temp2;
176 Text(l_temp1 , p_level);
177 end if;
178 end loop;
179
180 if length(l_value_string) > 0 then
181 Text(l_value_string || ';' , p_level);
182 end if;
183 END Break_Text;
184
185
186 PROCEDURE Pkg_End
187 ( p_pkg_name IN VARCHAR2
188 , p_pkg_type IN VARCHAR2
189 )
190 IS
191
192 l_is_pkg_body VARCHAR2(30);
193 n NUMBER := 0;
194 l_pkg_name VARCHAR2(30);
195 l_new_pkg_name CONSTANT VARCHAR2(30) := 'QP_BUILD_FORMULA_RULES';
196 v_segment_id number;
197 v_count BINARY_INTEGER := 1;
198 CURSOR errors IS
199 select line, text
200 from user_errors
201 where name = upper(l_pkg_name)
202 and type = decode(p_pkg_type,'SPEC','PACKAGE',
203 'BODY','PACKAGE BODY');
204 BEGIN
205
206 -- end statement.
207 Text('END '||p_pkg_name||';',0);
208
209 -- Show errors.
210 IF p_pkg_type = 'BODY' THEN
211 l_is_pkg_body := 'TRUE';
212 ELSE
213 l_is_pkg_body := 'FALSE';
214 END IF;
215
216 PUT_LINE( 'Call AD_DDL to create '||p_pkg_type||' of package '||p_pkg_name);
217 oe_debug_pub.add('Call AD_DDL to create '||p_pkg_type||' of package '||p_pkg_name);
218
219
220 ad_ddl.create_package(applsys_schema => g_schema
221 ,application_short_name => 'QP'
222 ,package_name => p_pkg_name
223 ,is_package_body => l_is_pkg_body
224 ,lb => 1
225 ,ub => line_number);
226
227 -- if there were any errors when creating this package, print out
228 -- the errors in the log file
229 l_pkg_name := p_pkg_name;
230 FOR error IN errors LOOP
231 if n= 0 then
232 PUT_LINE('ERROR in creating PACKAGE '||p_pkg_type||' :'||p_pkg_name);
233 oe_debug_pub.add('ERROR in creating PACKAGE '||p_pkg_type||' :'||p_pkg_name);
234 end if;
235 PUT_LINE('LINE :'||error.line||' '||substr(error.text,1,200));
236 oe_debug_pub.add('LINE :'||error.line||' '||substr(error.text,1,200));
237 n := 1;
238 END LOOP;
239
240 -- if there was an error in compiling the package, raise
241 -- an error
242 if n > 0 then
243 RAISE FND_API.G_EXC_ERROR;
244 end if;
245
246
247 IF n = 0 THEN
248 --no errors in the QP_BUILD_FORMULA_RULES_TMP
249 --now go ahead generate the package
250 --as QP_BUILD_FORMULA_RULES
251
252 PUT_LINE('PACKAGE '||p_pkg_type||' Name to :' ||l_pkg_name||' compiled successfully ');
253
254 oe_debug_pub.add('PACKAGE '||p_pkg_type||' Name to :' || l_pkg_name||' compiled successfully ');
255
256 PUT_LINE('Now create PACKAGE '||p_pkg_type||' : ' ||l_new_pkg_name);
257
258 oe_debug_pub.add('Now create PACKAGE '||p_pkg_type||' : ' ||l_new_pkg_name);
259
260 IF instr(ad_ddl.glprogtext(1),p_pkg_name) > 0 THEN
261
262 ad_ddl.glprogtext(1) := REPLACE(ad_ddl.glprogtext(1)
263 ,p_pkg_name
264 ,l_new_pkg_name);
265
266 PUT_LINE('First change : ' ||ad_ddl.glprogtext(1));
267
268 oe_debug_pub.add('First change : ' ||ad_ddl.glprogtext(1));
269
270 ad_ddl.glprogtext(line_number) := REPLACE(ad_ddl.glprogtext(line_number)
271 ,p_pkg_name
272 ,l_new_pkg_name);
273
274 PUT_LINE('Second change : ' ||' '||ad_ddl.glprogtext(line_number));
275
276 oe_debug_pub.add('Second change : ' ||' '||ad_ddl.glprogtext(line_number));
277
278 PUT_LINE('Trying to create PACKAGE '||p_pkg_type ||' :'||l_new_pkg_name);
279
280 oe_debug_pub.add('Trying to create PACKAGE '||p_pkg_type ||' :'||l_new_pkg_name);
281
282 ad_ddl.create_package(applsys_schema => g_schema
283 ,application_short_name => 'QP'
284 ,package_name => l_new_pkg_name
285 ,is_package_body => l_is_pkg_body
286 ,lb => 1
287 ,ub => line_number);
288
289 l_pkg_name := l_new_pkg_name;
290
291 -- if there were any errors
292 -- when creating this package, print out
293 -- the errors in the log file
294 FOR error IN errors LOOP
295 if n = 0 then
296 PUT_LINE('ERROR in creating PACKAGE ' ||p_pkg_type||' :'||l_pkg_name);
297
298 oe_debug_pub.add('ERROR in creating PACKAGE ' ||p_pkg_type||' :'||l_pkg_name);
299
300 end if;
301 PUT_LINE('LINE :'||error.line||' ' ||substr(error.text,1,200));
302
303 oe_debug_pub.add('LINE :'||error.line||' ' ||substr(error.text,1,200));
304 n := 1;
305 END LOOP;
306
307 -- if there was an error in compiling the package, raise
308 -- an error
309
310 if n > 0 then
311 RAISE FND_API.G_EXC_ERROR;
312 end if;
313
314 PUT_LINE('Generated PACKAGE '||p_pkg_type ||' :'||l_new_pkg_name ||' Successfully');
315
316 oe_debug_pub.add('Generated PACKAGE '||p_pkg_type ||' :'||l_new_pkg_name ||' Successfully');
317
318 ELSE
319 null;
320 END IF;--instr
321 END IF;--n=0
322
323 exception
324 when FND_API.G_EXC_ERROR then
325 raise FND_API.G_EXC_ERROR;
326 when others THEN
327 raise_application_error(-20000,SQLERRM||' '||ad_ddl.error_buf);
328 -- PUT_LINE('Iam into exception' ||ad_ddl.error_buf);
329 -- RAISE FND_API.G_EXC_ERROR;
330
331 END Pkg_End;
332
333 -- Generates the Package Header for the package SPEC and BODY
334 PROCEDURE Pkg_Header
335 ( p_pkg_name IN VARCHAR2
336 , p_pkg_type IN VARCHAR2
337 )
338 IS
339 header_string VARCHAR2(200);
340 BEGIN
341
342 -- Initialize line number
343 line_number := 0;
344
345 -- Define package.
346
347 IF p_pkg_type = 'BODY' THEN
348 Text ('CREATE or REPLACE PACKAGE BODY '||
349 p_pkg_name|| ' AS',0);
350 ELSE
351 Text ('CREATE or REPLACE PACKAGE '||
352 p_pkg_name|| ' AUTHID CURRENT_USER AS',0);
353 END IF;
354
355 -- $Header clause.
356 header_string := 'Header: QPXVBSFB.pls 115.0 '||sysdate||' 11:11:11 appldev ship ';
357 Text('/* $'||header_string||'$ */',0);
358 New_Line;
359
360 -- Copyright section.
361
362 Comment ( '',0 );
363 Comment (
364 'Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA',0);
365 Comment ( 'All rights reserved.',0);
366 Comment ( '',0);
367 Comment ( 'FILENAME',0);
368 Comment ( '',0);
369 Comment ( ' '||p_pkg_name,0);
370 Comment ( '',0);
371 Comment ( 'DESCRIPTION',0);
372 Comment ( '',0);
373 Comment ( ' '||INITCAP(p_pkg_type)||' of package '
374 ||p_pkg_name,0);
375 Comment ( '',0);
376 Comment ('NOTES',0);
377 Comment ( '',0);
378 Comment ('HISTORY',0);
379 Comment ( '',0);
380 Comment ( TO_CHAR(SYSDATE)||' Created',0);
381 Comment ( '',0);
382 New_Line;
383
384 -- Global constant holding package name.
385
386 IF p_pkg_type = 'BODY' THEN
387 Comment ( 'Global constant holding the package name',0);
388 --Text (RPAD('G_PKG_NAME',30)||'CONSTANT '||
389 -- 'VARCHAR2(30) := '''||p_pkg_name||''';',0);
390 New_Line;
391 END IF;
392
393 END Pkg_Header;
394
395
396 PROCEDURE FORMULAS
397 (err_buff out NOCOPY /* file.sql.39 change */ VARCHAR2,
398 retcode out NOCOPY /* file.sql.39 change */ NUMBER)
399 IS
400
401 CURSOR price_formulas_cur
402 IS
403 SELECT distinct formula
404 FROM qp_price_formulas_b
405 WHERE NVL(end_date_active,SYSDATE) >= SYSDATE; --Added for 5713302 to discard expired formulas
406
407 l_price_formula_id NUMBER := NULL;
408 l_result NUMBER;
409
410 l_formula_string VARCHAR2(32000) := '';
411 l_formula VARCHAR2(32000) := '';
412 l_number VARCHAR2(32000) := '';
413 l_component_string VARCHAR2(32000) := '';
414 l_temp_component_string VARCHAR2(32000) := '';
415 l_using_clause VARCHAR2(32000) := '';
416 l_new_formula VARCHAR2(32000) := '';
417 l_temp_new_formula VARCHAR2(32000) := '';
418 l_select_stmt VARCHAR2(32000) := '';
419 l_expression VARCHAR2(32000) := '';
420 l_temp_formula VARCHAR2(32000) := '';
421 l_category VARCHAR2(30) := '';
422 l_char VARCHAR2(1) := '';
423 ctr number:=0; -- modified by rassharm 5713302
424 l_formula_String_f varchar2(32000):=' '; -- modified by rassharm 5713302
425
426 BEGIN
427
428 oe_debug_pub.add('##### Begin Build Formulas #####');
429
430 Init_Applsys_Schema;
431
432 -- Writing out the body
433
434
435
436
437 Pkg_Header('QP_BUILD_FORMULA_RULES_TMP', 'BODY');
438 New_Line;
439 Text('PROCEDURE Get_Formula_Values',0);
440 Text('( p_Formula IN VARCHAR2',0);
441 Text(', p_Operand_Tbl IN QP_FORMULA_RULES_PVT.t_Operand_Tbl_Type',0);
442 Text(', p_procedure_type IN VARCHAR2',0); --sfiresto
443 Text(', x_formula_value OUT NOCOPY /* file.sql.39 change */ NUMBER',0);
444 Text(', x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2',0);
445 Text(')',0);
446 Text('IS',0);
447
448 -- modified by rassharm 5713302 --6726052,7249280 smbalara
449 -- New_line;
450 -- Text('l_oper QP_FORMULA_RULES_PVT.t_Operand_Tbl_Type; ',0);
451 -- end
452
453
454 New_line;
455 Text('BEGIN',0);
456 Text('BEGIN',1);
457 Text('NULL;',2);
458 New_Line;
459
460
461 FOR l_rec IN price_formulas_cur
462 LOOP
463
464 l_formula_string := '';
465 l_formula := '';
466 l_char := '';
467 l_number := '';
468 l_component_string := '';
469 l_temp_component_string := '';
470 l_new_formula := '';
471 l_temp_new_formula := '';
472 l_using_clause := '';
473 l_select_stmt := '';
474
475 l_formula := l_rec.formula;
476 --dbms_output.put_line('l_formula - ' || substr(l_formula,1,220));
477
478 FOR i IN 1..LENGTH(l_formula)
479 LOOP
480
481 l_char := SUBSTR(l_formula, i, 1);
482
483 IF (l_char = '0') OR (l_char = '1') OR (l_char = '2') OR (l_char = '3') OR
484 (l_char = '4') OR (l_char = '5') OR (l_char = '6') OR (l_char = '7') OR
485 (l_char = '8') OR (l_char = '9')
486 THEN
487 --If retrieved character is a digit
488 l_number := l_number || l_char;
489
490 IF i = LENGTH(l_formula) THEN
491 BEGIN
492 l_component_string := 'p_Operand_Tbl(' || l_number || ')' ;
493 l_temp_component_string := ':p_Operand_Tbl' || l_number;
494 l_using_clause := l_using_clause || l_component_string || ',';
495 EXCEPTION
496 WHEN OTHERS THEN
497 l_component_string := '';
498 l_temp_component_string := '';
499 END;
500
501 -- modidied by rassharm 5713302
502 /*IF instr(l_formula_String_f,'l_oper('||l_number||')')=0 THEN
503 Text('l_oper('||l_number||'):=TO_NUMBER(TO_CHAR('||l_component_string ||'));', 0);
504 l_Formula_String_f:= l_Formula_String_f||'l_oper('||l_number||')';
505 END IF;
506 l_new_formula:=l_new_formula||'l_oper('||l_number||')';
507 NEW_LINE;
508 l_temp_new_formula := l_temp_new_formula || 'TO_NUMBER(' ||'TO_CHAR(' || l_temp_component_string || '))';
509 */
510
511 --6726052,7249280 smbalara uncommenting changes
512 l_new_formula := l_new_formula || 'TO_NUMBER(' ||
513 'TO_CHAR(' || l_component_string || '))';
514 l_temp_new_formula := l_temp_new_formula || 'TO_NUMBER(' ||
515 'TO_CHAR(' || l_temp_component_string || '))';
516
517 if nvl(l_number,-1)>ctr then--6726052,7249280 smbalara check this
518 ctr:=l_number;
519 end if;
520 l_number := '';
521 END IF;
522
523 ELSE -- If character is not a number
524
525 IF l_number IS NOT NULL THEN
526 -- Convert number to step_number and append the component value of
527 -- that step_number to new_formula
528 BEGIN
529 l_component_string := 'p_Operand_Tbl(' || l_number || ')' ;
530 l_temp_component_string := ':p_Operand_Tbl' || l_number;
531 l_using_clause := l_using_clause || l_component_string || ',';
532 EXCEPTION
533 WHEN OTHERS THEN
534 l_component_string := '';
535 l_temp_component_string := '';
536 END;
537 -- modidied by rassharm 5713302
538 /* IF instr(l_formula_String_f,'l_oper('||l_number||')')=0 THEN
539 Text('l_oper('||l_number||'):=TO_NUMBER(TO_CHAR('||l_component_string ||'));', 0);
540 l_Formula_String_f:= l_Formula_String_f||'l_oper('||l_number||')';
541 END IF;
542 l_new_formula:=l_new_formula||'l_oper('||l_number||')';
543 NEW_LINE;
544 l_temp_new_formula := l_temp_new_formula || 'TO_NUMBER(' ||'TO_CHAR(' || l_temp_component_string || '))';
545 */
546 --smbalara uncommenting
547 l_new_formula := l_new_formula || 'TO_NUMBER(' ||
548 'TO_CHAR(' || l_component_string || '))';
549 l_temp_new_formula := l_temp_new_formula || 'TO_NUMBER(' ||
550 'TO_CHAR(' || l_temp_component_string || '))';
551
552 if nvl(l_number,-1)>ctr then
553 ctr:=l_number;
554 end if;
555
556 l_number := '';
557 END IF;
558
559 l_new_formula := l_new_formula || l_char;
560 l_temp_new_formula := l_temp_new_formula || l_char;
561
562 END IF; -- If character is a number or not
563
564 END LOOP; -- Loop through every character in the Formula String
565
566 --dbms_output.put_line('l_new_formula - ' || substr(l_new_formula,1,220));
567 l_temp_formula := substr(l_using_clause,length(l_using_clause),1);
568 if (l_temp_formula = ',') then
569 l_temp_formula := substr(l_using_clause,1, length(l_using_clause) - 1);
570 l_using_clause := l_temp_formula;
571 end if;
572 --dbms_output.put_line('l_temp_new_form - ' || substr(l_temp_new_formula,1,220));
573 --dbms_output.put_line('l_using_clause - ' || substr(l_using_clause,1,220));
574
575 l_temp_formula := ltrim(rtrim(l_new_formula));
576 l_new_formula := l_temp_formula;
577 l_temp_formula := ltrim(rtrim(l_temp_new_formula));
578 l_temp_new_formula := l_temp_formula;
579 Begin
580 -- modified by rassharm changed l_expression replacing p_operand_tbl to l_oper for immediate execution 5713302
581 -- l_expression := 'declare l_res number; TYPE t_Operand_Tbl_Type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; l_oper t_Operand_Tbl_Type; begin l_res := ' || l_new_formula || '; end;';
582 l_expression := 'declare l_res number; TYPE t_Operand_Tbl_Type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; p_Operand_Tbl t_Operand_Tbl_Type; begin l_res := ' || l_new_formula || '; end;';
583 --dbms_output.put_line('Exp is - ' || substr(l_expression,1,220));
584 execute immediate l_expression;
585 l_category := 'EXPRESSION';
586 EXCEPTION
587 WHEN NO_DATA_FOUND THEN
588 l_category := 'EXPRESSION';
589 WHEN OTHERS THEN
590 Begin
591 -- modified by rassharm changed l_expression replacing p_operand_tbl to l_oper for immediate execution 5713302
592 --l_expression := 'declare l_res number; TYPE t_Operand_Tbl_Type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; l_oper t_Operand_Tbl_Type; begin Select ' || l_new_formula || ' into l_res from dual; end;';
593 l_expression := 'declare l_res number; TYPE t_Operand_Tbl_Type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; p_Operand_Tbl t_Operand_Tbl_Type; begin Select ' || l_new_formula || ' into l_res from dual; end;';
594 --dbms_output.put_line('Exp is - ' || substr(l_expression,1,220));
595 Text(' dbms_output.put_line('''|| l_expression||''');',3);
596 execute immediate l_expression;
597 l_category := 'PLSQL';
598 EXCEPTION
599 WHEN NO_DATA_FOUND THEN
600 l_category := 'PLSQL';
601 WHEN OTHERS THEN
602 Begin
603 l_category := 'SQL';
604 End;
605 End;
606 END;
607
608 --dbms_output.put_line('Category - ' || l_category || ' Formula_Id - ' || l_price_formula_id);
609
610 Text('IF p_Formula = ',2);
611 Formula_Text(l_formula);
612 Text(' THEN', 2);
613 Text('IF p_procedure_type != ''S'' THEN', 3); --sfiresto
614 New_Line;
615 IF l_category = 'EXPRESSION' THEN
616 --Break_Text('oe_debug_pub.add(''Formula - ' || l_new_formula || ''')',4);
617 Break_Text('x_formula_value := ' || l_new_formula, 4);
618 Text('x_return_status := ''S''' || ';',4);
619 --dbms_output.put_line('IN EXPRESSION ');
620 ELSIF l_category = 'PLSQL' THEN
621 --Break_Text('oe_debug_pub.add(''Formula - ' || l_new_formula || ''')',4);
622 l_select_stmt := 'SELECT '|| l_new_formula || ' INTO x_formula_value FROM DUAL';
623 Break_Text(l_select_stmt, 4);
624 Text('x_return_status := ''S''' || ';',4);
625 --dbms_output.put_line('IN PLSQL ');
626 ELSIF l_category = 'SQL' THEN
627 --Break_Text('oe_debug_pub.add(''Formula - ' || l_new_formula || ''')',4);
628 select replace(l_temp_new_formula,'''','''''') into l_temp_new_formula from dual;--6726052,7249280 smbalara
629 l_select_stmt := 'EXECUTE IMMEDIATE '' SELECT '|| l_temp_new_formula || ' FROM DUAL '' INTO x_formula_value using ' || l_using_clause;
630 Break_Text(l_select_stmt, 4);
631 Text('x_return_status := ''S''' || ';',4);
632 --dbms_output.put_line('IN SQL ');
633 END IF;
634 Text('ELSE', 3); --sfiresto
635 Text('x_return_status := ''T''' || ';', 4); --sfiresto
636 Text('END IF' || ';',3); --sfiresto
637 Text('RETURN' || ';',3); --sfiresto
638 Text('END IF' || ';',2);
639 New_Line;
640
641 l_category := '';
642
643 END LOOP;
644
645 Text('x_return_status := ''F''' || ';',2); --sfiresto
646 New_Line;
647 Text('EXCEPTION',1);
648 Text('WHEN OTHERS THEN',2);
649 Text('x_return_status := ''E''' || ';',3);
650 Text('oe_debug_pub.add(''Error in QP_BUILD_FORMULA_RULES_TMP -''||sqlerrm)'||';');
651 Text('END' || ';',1);
652 Text('END Get_Formula_Values;',0);
653 New_Line;
654
655 Pkg_End('QP_BUILD_FORMULA_RULES_TMP', 'BODY');
656
657 FND_MESSAGE.SET_NAME('QP','QP_BUILD_FORMULAS_SUCCESS');
658 err_buff := FND_MESSAGE.GET;
659 PUT_LINE(err_buff);
660 retcode := 0;
661 oe_debug_pub.add('##### ' || err_buff || ' #####');
662 oe_debug_pub.add('##### End Build Formulas #####');
663
664 EXCEPTION
665
666 WHEN FND_API.G_EXC_ERROR THEN
667 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_SOURCING_ERROR');
668 FND_MESSAGE.SET_TOKEN('PACKAGE_TYPE','BODY');
669 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME','QP_BUILD_FORMULA_RULES');
670 FND_MESSAGE.SET_TOKEN('ERRMSG',substr(SQLERRM,1,150));
671 err_buff := FND_MESSAGE.GET;
672 PUT_LINE(err_buff);
673 retcode := 2;
674 oe_debug_pub.add('##### ' || err_buff || ' #####');
675 oe_debug_pub.add('##### End Build Formulas #####');
676
677 WHEN OTHERS THEN
678 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_SOURCING_ERROR');
679 FND_MESSAGE.SET_TOKEN('PACKAGE_TYPE','BODY');
680 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME','QP_BUILD_FORMULA_RULES');
681 FND_MESSAGE.SET_TOKEN('ERRMSG',substr(SQLERRM,1,150));
682 err_buff := FND_MESSAGE.GET;
683 PUT_LINE(err_buff);
684 retcode := 2;
685 oe_debug_pub.add('##### ' || err_buff || ' #####');
686 oe_debug_pub.add('##### End Build Formulas #####');
687
688 END FORMULAS;
689
690 END QP_FORMULA_RULES_PVT;