[Home] [Help]
PACKAGE BODY: APPS.QP_FORMULA_RULES_PVT
Source
1 PACKAGE BODY QP_FORMULA_RULES_PVT AS
2 /* $Header: QPXPFORB.pls 120.4.12020000.2 2012/07/04 12:07:05 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 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 L_MAINT_DYN_SRC_VER VARCHAR2(1) := NVL(Fnd_Profile.value('QP_MAINTAIN_DYNAMIC_SOURCE_VERSIONS'),'N'); -- 13638721
205 l_pkg_ver VARCHAR2(10):='1';
206 l_insert_row VARCHAR2(10):='N';
207 BEGIN
208
209 -- end statement.
210 Text('END '||p_pkg_name||';',0);
211
212 -- Show errors.
213 IF p_pkg_type = 'BODY' THEN
214 l_is_pkg_body := 'TRUE';
215 ELSE
216 l_is_pkg_body := 'FALSE';
217 END IF;
218
219 PUT_LINE( 'Call AD_DDL to create '||p_pkg_type||' of package '||p_pkg_name);
220 oe_debug_pub.add('Call AD_DDL to create '||p_pkg_type||' of package '||p_pkg_name);
221
222
223 ad_ddl.create_package(applsys_schema => g_schema
224 ,application_short_name => 'QP'
225 ,package_name => p_pkg_name
226 ,is_package_body => l_is_pkg_body
227 ,lb => 1
228 ,ub => line_number);
229
230 -- if there were any errors when creating this package, print out
231 -- the errors in the log file
232 l_pkg_name := p_pkg_name;
233 FOR error IN errors LOOP
234 if n= 0 then
235 PUT_LINE('ERROR in creating PACKAGE '||p_pkg_type||' :'||p_pkg_name);
236 oe_debug_pub.add('ERROR in creating PACKAGE '||p_pkg_type||' :'||p_pkg_name);
237 end if;
238 PUT_LINE('LINE :'||error.line||' '||substr(error.text,1,200));
239 oe_debug_pub.add('LINE :'||error.line||' '||substr(error.text,1,200));
240 n := 1;
241 END LOOP;
242
243 -- if there was an error in compiling the package, raise
244 -- an error
245 if n > 0 then
246 RAISE FND_API.G_EXC_ERROR;
247 end if;
248
249
250 IF n = 0 THEN
251 --no errors in the QP_BUILD_FORMULA_RULES_TMP
252 --now go ahead generate the package
253 --as QP_BUILD_FORMULA_RULES
254
255 PUT_LINE('PACKAGE '||p_pkg_type||' Name to :' ||l_pkg_name||' compiled successfully ');
256
257 oe_debug_pub.add('PACKAGE '||p_pkg_type||' Name to :' || l_pkg_name||' compiled successfully ');
258
259 PUT_LINE('Now create PACKAGE '||p_pkg_type||' : ' ||l_new_pkg_name);
260
261 oe_debug_pub.add('Now create PACKAGE '||p_pkg_type||' : ' ||l_new_pkg_name);
262 PUT_LINE('Maintain Dynamic pkgs profile is :'||L_MAINT_DYN_SRC_VER);
263
264 oe_debug_pub.add('Maintain Dynamic pkgs profile is :'||L_MAINT_DYN_SRC_VER);
265 -- 13638721
266 IF (L_MAINT_DYN_SRC_VER='Y') THEN
267 BEGIN
268 SELECT SEEDED_VALUE INTO l_pkg_ver FROM qp_parameters_b WHERE PARAMETER_CODE='BLD_FORMULA_ACTV_VER' AND ROWNUM=1;
269
270 IF (l_pkg_ver='5') THEN
271 l_new_pkg_name := l_new_pkg_name || '1';
272 ELSE
273 l_new_pkg_name := l_new_pkg_name || To_Char(To_Number(l_pkg_ver)+1);
274 END IF;
275 PUT_LINE('found row in qp_params for code BLD_FORMULA_ACTV_VER new pkg name: ' || l_new_pkg_name);
276
277 oe_debug_pub.add('found row in qp_params for code BLD_FORMULA_ACTV_VER new pkg name: ' || l_new_pkg_name);
278
279 EXCEPTION
280 WHEN No_Data_Found THEN
281 l_insert_row := 'Y';
282 l_new_pkg_name := l_new_pkg_name || '1';
283 PUT_LINE('no data found row in qp_params for code BLD_FORMULA_ACTV_VER new pkg name: ' || l_new_pkg_name);
284
285 oe_debug_pub.add('no data found row in qp_params for code BLD_FORMULA_ACTV_VER new pkg name: ' || l_new_pkg_name);
286 WHEN OTHERS THEN
287 PUT_LINE('when others row in qp_params for code BLD_FORMULA_ACTV_VER new pkg name: ' || l_new_pkg_name);
288
289 oe_debug_pub.add('when others row in qp_params for code BLD_FORMULA_ACTV_VER new pkg name: ' || l_new_pkg_name);
290 END;
291
292 END IF;
293 -- 13638721
294 IF instr(ad_ddl.glprogtext(1),p_pkg_name) > 0 THEN
295
296 ad_ddl.glprogtext(1) := REPLACE(ad_ddl.glprogtext(1)
297 ,p_pkg_name
298 ,l_new_pkg_name);
299
300 PUT_LINE('First change : ' ||ad_ddl.glprogtext(1));
301
302 oe_debug_pub.add('First change : ' ||ad_ddl.glprogtext(1));
303
304 ad_ddl.glprogtext(line_number) := REPLACE(ad_ddl.glprogtext(line_number)
305 ,p_pkg_name
306 ,l_new_pkg_name);
307
308 PUT_LINE('Second change : ' ||' '||ad_ddl.glprogtext(line_number));
309
310 oe_debug_pub.add('Second change : ' ||' '||ad_ddl.glprogtext(line_number));
311
312 PUT_LINE('Trying to create PACKAGE '||p_pkg_type ||' :'||l_new_pkg_name);
313
314 oe_debug_pub.add('Trying to create PACKAGE '||p_pkg_type ||' :'||l_new_pkg_name);
315
316 ad_ddl.create_package(applsys_schema => g_schema
317 ,application_short_name => 'QP'
318 ,package_name => l_new_pkg_name
319 ,is_package_body => l_is_pkg_body
320 ,lb => 1
324
321 ,ub => line_number);
322
323 l_pkg_name := l_new_pkg_name;
325 -- if there were any errors
326 -- when creating this package, print out
327 -- the errors in the log file
328 FOR error IN errors LOOP
329 if n = 0 then
330 PUT_LINE('ERROR in creating PACKAGE ' ||p_pkg_type||' :'||l_pkg_name);
331
332 oe_debug_pub.add('ERROR in creating PACKAGE ' ||p_pkg_type||' :'||l_pkg_name);
333
334 end if;
335 PUT_LINE('LINE :'||error.line||' ' ||substr(error.text,1,200));
336
337 oe_debug_pub.add('LINE :'||error.line||' ' ||substr(error.text,1,200));
338 n := 1;
339 END LOOP;
340
341 -- if there was an error in compiling the package, raise
342 -- an error
343
344 if n > 0 then
345 RAISE FND_API.G_EXC_ERROR;
346 end if;
347
348 PUT_LINE('Generated PACKAGE '||p_pkg_type ||' :'||l_new_pkg_name ||' Successfully');
349
350 oe_debug_pub.add('Generated PACKAGE '||p_pkg_type ||' :'||l_new_pkg_name ||' Successfully');
351 -- 13638721
352 IF (L_MAINT_DYN_SRC_VER='Y') THEN
353
354 IF (l_insert_row = 'N') THEN
355
356 PUT_LINE('found row in qp_params row updated with new version: ' || To_Char(To_Number(l_pkg_ver)+1));
357
358 oe_debug_pub.add('found row in qp_params row updated with new version: ' || To_Char(To_Number(l_pkg_ver)+1));
359 UPDATE qp_parameters_b
360 SET SEEDED_VALUE=Decode(l_pkg_ver,'5','1',To_Char(To_Number(l_pkg_ver)+1)),
361 LAST_UPDATE_DATE = sysdate,
362 LAST_UPDATED_BY = nvl(TO_NUMBER (FND_PROFILE.VALUE ('USER_ID')),-1) ,
363 LAST_UPDATE_LOGIN = nvl(TO_NUMBER (FND_PROFILE.VALUE ('LOGIN_ ID')),-1)
364 WHERE parameter_code='BLD_FORMULA_ACTV_VER';
365 -- do not add any statements(even debug statements) in this area
366 IF SQL%ROWCOUNT = 0 THEN
367 INSERT INTO qp_parameters_b (parameter_id, parameter_level,parameter_code,value_set_id,
368 ADVANCED_PRICING_ONLY,SEEDED_VALUE,CREATION_DATE,CREATED_BY,
369 LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
370 VALUES (-8888,'CONC','BLD_FORMULA_ACTV_VER',-8888,
371 'N','1',SYSDATE,
372 -1,SYSDATE,-1,-1);
373 PUT_LINE('found row in qp_params but no row updated so insert: ' );
374
375 oe_debug_pub.add('found row in qp_params but no row updated so insert: ');
376 END IF;
377 ELSE
378 PUT_LINE('insert row flag is Y ' );
379
380 oe_debug_pub.add('insert row flag is Y ');
381 INSERT INTO qp_parameters_b (parameter_id, parameter_level,parameter_code,value_set_id,
382 ADVANCED_PRICING_ONLY,SEEDED_VALUE,CREATION_DATE,CREATED_BY,
383 LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
384 VALUES (-8888,'CONC','BLD_FORMULA_ACTV_VER',-8888,
385 'N','1',SYSDATE,
386 -1,SYSDATE,-1,-1);
387 END IF;
388
389 END IF;
390 -- 13638721
391 COMMIT;
392 ELSE
393 null;
394 END IF;--instr
395 END IF;--n=0
396
397 exception
398 when FND_API.G_EXC_ERROR then
399 raise FND_API.G_EXC_ERROR;
400 when others THEN
401 raise_application_error(-20000,SQLERRM||' '||ad_ddl.error_buf);
402 -- PUT_LINE('Iam into exception' ||ad_ddl.error_buf);
403 -- RAISE FND_API.G_EXC_ERROR;
404
405 END Pkg_End;
406
407 -- Generates the Package Header for the package SPEC and BODY
408 PROCEDURE Pkg_Header
409 ( p_pkg_name IN VARCHAR2
410 , p_pkg_type IN VARCHAR2
411 )
412 IS
413 header_string VARCHAR2(200);
414 BEGIN
415
416 -- Initialize line number
417 line_number := 0;
418
419 -- Define package.
420
421 IF p_pkg_type = 'BODY' THEN
422 Text ('CREATE or REPLACE PACKAGE BODY '||
423 p_pkg_name|| ' AS',0);
424 ELSE
425 Text ('CREATE or REPLACE PACKAGE '||
426 p_pkg_name|| ' AUTHID CURRENT_USER AS',0);
427 END IF;
428
429 -- $Header clause.
430 header_string := 'Header: QPXVBSFB.pls 115.0 '||sysdate||' 11:11:11 appldev ship ';
431 Text('/* $'||header_string||'$ */',0);
432 New_Line;
433
434 -- Copyright section.
435
436 Comment ( '',0 );
437 Comment (
438 'Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA',0);
439 Comment ( 'All rights reserved.',0);
440 Comment ( '',0);
441 Comment ( 'FILENAME',0);
442 Comment ( '',0);
443 Comment ( ' '||p_pkg_name,0);
444 Comment ( '',0);
445 Comment ( 'DESCRIPTION',0);
446 Comment ( '',0);
447 Comment ( ' '||INITCAP(p_pkg_type)||' of package '
448 ||p_pkg_name,0);
449 Comment ( '',0);
450 Comment ('NOTES',0);
451 Comment ( '',0);
452 Comment ('HISTORY',0);
453 Comment ( '',0);
454 Comment ( TO_CHAR(SYSDATE)||' Created',0);
455 Comment ( '',0);
456 New_Line;
457
458 -- Global constant holding package name.
459
460 IF p_pkg_type = 'BODY' THEN
461 Comment ( 'Global constant holding the package name',0);
462 --Text (RPAD('G_PKG_NAME',30)||'CONSTANT '||
463 -- 'VARCHAR2(30) := '''||p_pkg_name||''';',0);
464 New_Line;
465 END IF;
469
466
467 END Pkg_Header;
468
470 PROCEDURE FORMULAS
471 (err_buff out NOCOPY /* file.sql.39 change */ VARCHAR2,
472 retcode out NOCOPY /* file.sql.39 change */ NUMBER)
473 IS
474
475 CURSOR price_formulas_cur
476 IS
477 SELECT distinct formula
478 FROM qp_price_formulas_b
479 WHERE NVL(end_date_active,SYSDATE) >= SYSDATE; --Added for 5713302 to discard expired formulas
480
481 l_price_formula_id NUMBER := NULL;
482 l_result NUMBER;
483
484 l_formula_string VARCHAR2(32000) := '';
485 l_formula VARCHAR2(32000) := '';
486 l_number VARCHAR2(32000) := '';
487 l_component_string VARCHAR2(32000) := '';
488 l_temp_component_string VARCHAR2(32000) := '';
489 l_using_clause VARCHAR2(32000) := '';
490 l_new_formula VARCHAR2(32000) := '';
491 l_temp_new_formula VARCHAR2(32000) := '';
492 l_select_stmt VARCHAR2(32000) := '';
493 l_expression VARCHAR2(32000) := '';
494 l_temp_formula VARCHAR2(32000) := '';
495 l_category VARCHAR2(30) := '';
496 l_char VARCHAR2(1) := '';
497 ctr number:=0; -- modified by rassharm 5713302
498 l_formula_String_f varchar2(32000):=' '; -- modified by rassharm 5713302
499
500 BEGIN
501
502 oe_debug_pub.add('##### Begin Build Formulas #####');
503
504 Init_Applsys_Schema;
505
506 -- Writing out the body
507
508
509
510
511 Pkg_Header('QP_BUILD_FORMULA_RULES_TMP', 'BODY');
512 New_Line;
513 Text('PROCEDURE Get_Formula_Values',0);
514 Text('( p_Formula IN VARCHAR2',0);
515 Text(', p_Operand_Tbl IN QP_FORMULA_RULES_PVT.t_Operand_Tbl_Type',0);
516 Text(', p_procedure_type IN VARCHAR2',0); --sfiresto
517 Text(', x_formula_value OUT NOCOPY /* file.sql.39 change */ NUMBER',0);
518 Text(', x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2',0);
519 Text(')',0);
520 Text('IS',0);
521
522 -- modified by rassharm 5713302 --6726052,7249280 smbalara
523 -- New_line;
524 -- Text('l_oper QP_FORMULA_RULES_PVT.t_Operand_Tbl_Type; ',0);
525 -- end
526
527
528 New_line;
529 Text('BEGIN',0);
530 Text('BEGIN',1);
531 Text('NULL;',2);
532 New_Line;
533
534
535 FOR l_rec IN price_formulas_cur
536 LOOP
537
538 l_formula_string := '';
539 l_formula := '';
540 l_char := '';
541 l_number := '';
542 l_component_string := '';
543 l_temp_component_string := '';
544 l_new_formula := '';
545 l_temp_new_formula := '';
546 l_using_clause := '';
547 l_select_stmt := '';
548
549 l_formula := l_rec.formula;
550 --dbms_output.put_line('l_formula - ' || substr(l_formula,1,220));
551
552 FOR i IN 1..LENGTH(l_formula)
553 LOOP
554
555 l_char := SUBSTR(l_formula, i, 1);
556
557 IF (l_char = '0') OR (l_char = '1') OR (l_char = '2') OR (l_char = '3') OR
558 (l_char = '4') OR (l_char = '5') OR (l_char = '6') OR (l_char = '7') OR
559 (l_char = '8') OR (l_char = '9')
560 THEN
561 --If retrieved character is a digit
562 l_number := l_number || l_char;
563
564 IF i = LENGTH(l_formula) THEN
565 BEGIN
566 l_component_string := 'p_Operand_Tbl(' || l_number || ')' ;
567 l_temp_component_string := ':p_Operand_Tbl' || l_number;
568 l_using_clause := l_using_clause || l_component_string || ',';
569 EXCEPTION
570 WHEN OTHERS THEN
571 l_component_string := '';
572 l_temp_component_string := '';
573 END;
574
575 -- modidied by rassharm 5713302
576 /*IF instr(l_formula_String_f,'l_oper('||l_number||')')=0 THEN
577 Text('l_oper('||l_number||'):=TO_NUMBER(TO_CHAR('||l_component_string ||'));', 0);
578 l_Formula_String_f:= l_Formula_String_f||'l_oper('||l_number||')';
579 END IF;
580 l_new_formula:=l_new_formula||'l_oper('||l_number||')';
581 NEW_LINE;
582 l_temp_new_formula := l_temp_new_formula || 'TO_NUMBER(' ||'TO_CHAR(' || l_temp_component_string || '))';
583 */
584
585 --6726052,7249280 smbalara uncommenting changes
586 l_new_formula := l_new_formula || 'TO_NUMBER(' ||
587 'TO_CHAR(' || l_component_string || '))';
588 l_temp_new_formula := l_temp_new_formula || 'TO_NUMBER(' ||
589 'TO_CHAR(' || l_temp_component_string || '))';
590
591 if nvl(l_number,-1)>ctr then--6726052,7249280 smbalara check this
592 ctr:=l_number;
593 end if;
594 l_number := '';
595 END IF;
596
597 ELSE -- If character is not a number
598
599 IF l_number IS NOT NULL THEN
600 -- Convert number to step_number and append the component value of
601 -- that step_number to new_formula
602 BEGIN
603 l_component_string := 'p_Operand_Tbl(' || l_number || ')' ;
604 l_temp_component_string := ':p_Operand_Tbl' || l_number;
605 l_using_clause := l_using_clause || l_component_string || ',';
606 EXCEPTION
607 WHEN OTHERS THEN
608 l_component_string := '';
609 l_temp_component_string := '';
610 END;
614 l_Formula_String_f:= l_Formula_String_f||'l_oper('||l_number||')';
611 -- modidied by rassharm 5713302
612 /* IF instr(l_formula_String_f,'l_oper('||l_number||')')=0 THEN
613 Text('l_oper('||l_number||'):=TO_NUMBER(TO_CHAR('||l_component_string ||'));', 0);
615 END IF;
616 l_new_formula:=l_new_formula||'l_oper('||l_number||')';
617 NEW_LINE;
618 l_temp_new_formula := l_temp_new_formula || 'TO_NUMBER(' ||'TO_CHAR(' || l_temp_component_string || '))';
619 */
620 --smbalara uncommenting
621 l_new_formula := l_new_formula || 'TO_NUMBER(' ||
622 'TO_CHAR(' || l_component_string || '))';
623 l_temp_new_formula := l_temp_new_formula || 'TO_NUMBER(' ||
624 'TO_CHAR(' || l_temp_component_string || '))';
625
626 if nvl(l_number,-1)>ctr then
627 ctr:=l_number;
628 end if;
629
630 l_number := '';
631 END IF;
632
633 l_new_formula := l_new_formula || l_char;
634 l_temp_new_formula := l_temp_new_formula || l_char;
635
636 END IF; -- If character is a number or not
637
638 END LOOP; -- Loop through every character in the Formula String
639
640 --dbms_output.put_line('l_new_formula - ' || substr(l_new_formula,1,220));
641 l_temp_formula := substr(l_using_clause,length(l_using_clause),1);
642 if (l_temp_formula = ',') then
643 l_temp_formula := substr(l_using_clause,1, length(l_using_clause) - 1);
644 l_using_clause := l_temp_formula;
645 end if;
646 --dbms_output.put_line('l_temp_new_form - ' || substr(l_temp_new_formula,1,220));
647 --dbms_output.put_line('l_using_clause - ' || substr(l_using_clause,1,220));
648
649 l_temp_formula := ltrim(rtrim(l_new_formula));
650 l_new_formula := l_temp_formula;
651 l_temp_formula := ltrim(rtrim(l_temp_new_formula));
652 l_temp_new_formula := l_temp_formula;
653 l_temp_new_formula := REPLACE(l_temp_new_formula,'''','''''');--smbalara 8348005
654 Begin
655 -- modified by rassharm changed l_expression replacing p_operand_tbl to l_oper for immediate execution 5713302
656 -- 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;';
657 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;';
658 --dbms_output.put_line('Exp is - ' || substr(l_expression,1,220));
659 execute immediate l_expression;
660 l_category := 'EXPRESSION';
661 EXCEPTION
662 WHEN NO_DATA_FOUND THEN
663 l_category := 'EXPRESSION';
664 WHEN OTHERS THEN
665 Begin
666 -- modified by rassharm changed l_expression replacing p_operand_tbl to l_oper for immediate execution 5713302
667 --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;';
668 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;';
669 --dbms_output.put_line('Exp is - ' || substr(l_expression,1,220));
670 Text(' dbms_output.put_line('''|| l_expression||''');',3);
671 execute immediate l_expression;
672 l_category := 'PLSQL';
673 EXCEPTION
674 WHEN NO_DATA_FOUND THEN
675 l_category := 'PLSQL';
676 WHEN OTHERS THEN
677 Begin
678 l_category := 'SQL';
679 End;
680 End;
681 END;
682
683 --dbms_output.put_line('Category - ' || l_category || ' Formula_Id - ' || l_price_formula_id);
684
685 Text('IF p_Formula = ',2);
686 Formula_Text(l_formula);
687 Text(' THEN', 2);
688 Text('IF p_procedure_type != ''S'' THEN', 3); --sfiresto
689 New_Line;
690 IF l_category = 'EXPRESSION' THEN
691 --Break_Text('oe_debug_pub.add(''Formula - ' || l_new_formula || ''')',4);
692 Break_Text('x_formula_value := ' || l_new_formula, 4);
693 Text('x_return_status := ''S''' || ';',4);
694 --dbms_output.put_line('IN EXPRESSION ');
695 ELSIF l_category = 'PLSQL' THEN
696 --Break_Text('oe_debug_pub.add(''Formula - ' || l_new_formula || ''')',4);
697 l_select_stmt := 'SELECT '|| l_new_formula || ' INTO x_formula_value FROM DUAL';
698 Break_Text(l_select_stmt, 4);
699 Text('x_return_status := ''S''' || ';',4);
700 --dbms_output.put_line('IN PLSQL ');
701 ELSIF l_category = 'SQL' THEN
702 --Break_Text('oe_debug_pub.add(''Formula - ' || l_new_formula || ''')',4);
703 --select replace(l_temp_new_formula,'''','''''') into l_temp_new_formula from dual;--6726052,7249280 smbalara:commented for 8348005
704 l_select_stmt := 'EXECUTE IMMEDIATE '' SELECT '|| l_temp_new_formula || ' FROM DUAL '' INTO x_formula_value using ' || l_using_clause;
705 Break_Text(l_select_stmt, 4);
706 Text('x_return_status := ''S''' || ';',4);
707 --dbms_output.put_line('IN SQL ');
708 END IF;
709 Text('ELSE', 3); --sfiresto
710 Text('x_return_status := ''T''' || ';', 4); --sfiresto
711 Text('END IF' || ';',3); --sfiresto
712 Text('RETURN' || ';',3); --sfiresto
713 Text('END IF' || ';',2);
714 New_Line;
715
716 l_category := '';
717
718 END LOOP;
719
720 Text('x_return_status := ''F''' || ';',2); --sfiresto
721 New_Line;
722 Text('EXCEPTION',1);
723 Text('WHEN OTHERS THEN',2);
727 Text('END Get_Formula_Values;',0);
724 Text('x_return_status := ''E''' || ';',3);
725 Text('oe_debug_pub.add(''Error in QP_BUILD_FORMULA_RULES_TMP -''||sqlerrm)'||';');
726 Text('END' || ';',1);
728 New_Line;
729
730 Pkg_End('QP_BUILD_FORMULA_RULES_TMP', 'BODY');
731
732 FND_MESSAGE.SET_NAME('QP','QP_BUILD_FORMULAS_SUCCESS');
733 err_buff := FND_MESSAGE.GET;
734 PUT_LINE(err_buff);
735 retcode := 0;
736 oe_debug_pub.add('##### ' || err_buff || ' #####');
737 oe_debug_pub.add('##### End Build Formulas #####');
738
739 EXCEPTION
740
741 WHEN FND_API.G_EXC_ERROR THEN
742 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_SOURCING_ERROR');
743 FND_MESSAGE.SET_TOKEN('PACKAGE_TYPE','BODY');
744 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME','QP_BUILD_FORMULA_RULES');
745 FND_MESSAGE.SET_TOKEN('ERRMSG',substr(SQLERRM,1,150));
746 err_buff := FND_MESSAGE.GET;
747 PUT_LINE(err_buff);
748 retcode := 2;
749 oe_debug_pub.add('##### ' || err_buff || ' #####');
750 oe_debug_pub.add('##### End Build Formulas #####');
751
752 WHEN OTHERS THEN
753 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_SOURCING_ERROR');
754 FND_MESSAGE.SET_TOKEN('PACKAGE_TYPE','BODY');
755 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME','QP_BUILD_FORMULA_RULES');
756 FND_MESSAGE.SET_TOKEN('ERRMSG',substr(SQLERRM,1,150));
757 err_buff := FND_MESSAGE.GET;
758 PUT_LINE(err_buff);
759 retcode := 2;
760 oe_debug_pub.add('##### ' || err_buff || ' #####');
761 oe_debug_pub.add('##### End Build Formulas #####');
762
763 END FORMULAS;
764
765 END QP_FORMULA_RULES_PVT;