[Home] [Help]
PACKAGE BODY: APPS.IEX_SCORE_NEW_PVT
Source
1 PACKAGE BODY IEX_SCORE_NEW_PVT AS
2 /* $Header: iexvscfb.pls 120.41.12020000.3 2013/03/04 08:28:58 sunagesh ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IEX_SCORE_NEW_PVT';
6 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexvscfb.pls';
7 --G_Debug_Level NUMBER := to_number(nvl(fnd_profile.value('IEX_DEBUG_LEVEL'), '0'));
8 G_Batch_Size NUMBER ;
9 PG_DEBUG NUMBER ;
10
11 G_MIN_SCORE VARCHAR2(100);
12 G_MAX_SCORE VARCHAR2(100);
13 G_RULE VARCHAR2(20) ;
14 G_WEIGHT_REQUIRED VARCHAR2(20) ;
15
16 l_del_buff_bridge number; --Added by schekuri for bug#6373998 on 31-Aug-2007
17 tempResult CLOB;
18 l_new_line varchar2(1):='
19 ';
20
21 /*
22 || Overview: validates any given objectID/Object_type pair
23 ||
24 || Parameter: p_Object_ID PK of object you wish to score
25 || p_Object_Type Type of Object you wish to score
26 || Alternatively if you wish to score another TYPE of object
27 || not listed pass the following as well:
28 || p_col_name name of colum you wish to select on
29 || p_table_name name of table to select from
30 ||
31 || Return value: True =OK; Falso=Error
32 ||
33 || Source Tables: IEX_DELINQUENCIES_ALL, IEX_CASES_B_ALL, HZ_PARTIES, AR_PAYMENT_SCHEDULES
34 || (these are the "FROM_TABLE" on JTF_OBJECTS_B
35 || Target Tables:
36 ||
37 || Creation date: 01/14/02 3:25:PM
38 ||
39 || Major Modifications: when who what
40 || 01/14/02 raverma created
41 */
42 function validateObjectID (p_object_id in number,
43 p_object_type in varchar2,
44 p_col_name in varchar2,
45 p_table_name in varchar2) return BOOLEAN
46
47 is
48
49 l_msg_count number;
50 l_msg_data varchar2(2000);
51 l_return_status varchar2(1);
52 l_col_name varchar2(200) ;
53 l_table_name varchar2(200) ;
54
55 BEGIN
56
57 l_col_name := p_col_name;
58 l_table_name := p_table_name;
59
60 -- get FROM_TABLE AND SELECT_ID from JTF_OBJECTS_B
61 if l_col_name is null or l_table_name is null then
62 begin
63 Execute Immediate
64 ' Select Select_ID, From_table ' ||
65 ' From jtf_objects_b ' ||
66 ' where object_code = :p_object_code'
67 into l_col_name, l_table_name
68 using p_object_type;
69 Exception
70 When no_data_found then
71 return FALSE;
72 end;
73 end if;
74
75 -- see if the ID passed is OK on the FROM_TABLE/SELECT_ID
76 iex_utilities.validate_any_id(p_col_id => p_object_id,
77 p_col_name => l_col_name,
78 p_table_name => l_table_name,
79 x_msg_count => l_msg_count,
80 x_msg_data => l_msg_data,
81 x_return_status => l_return_status,
82 p_init_msg_list =>fnd_api.g_false);
83
84 If l_return_Status = 'S' then
85 return TRUE;
86 else
87 return FALSE;
88 end if;
89
90 Exception
91 when others then
92 return false;
93
94 END validateObjectID;
95
96 /*
97 || Overview: compares whether the score engine being used for this object is of valid type
98 ||
99 || Parameter: p_score_id => scoring engine; p_object_type => type of object you wish to score
100 ||
101 || Return value: true=OK; FALSE=error
102 ||
103 || Source Tables: IEX_SCORES
104 ||
105 || Target Tables: NA
106 ||
107 || Creation date: 01/14/02 4:47:PM
108 ||
109 || Major Modifications: when who what
110 || 01/14/02 raverma created
111 */
112 function checkObject_Compatibility(p_score_id in number,
113 p_object_type in varchar2) return BOOLEAN
114 is
115 l_object_type varchar2(25);
116
117 begin
118
119 begin
120 Execute Immediate
121 ' Select jtf_object_code ' ||
122 ' From iex_scores ' ||
123 ' where score_id = :p_score_id'
124 into l_object_type
125 using p_score_id;
126 Exception
127 When no_data_found then
128 return FALSE;
129 end;
130
131 if l_object_type = p_object_type then
132 return TRUE;
133 else
134 return FALSE;
135 end if;
136
137 Exception
138 when others then
139 return false;
140
141 end checkObject_Compatibility;
142
143 /*
144 || Overview: Validate Score_Engine
145 ||
146 || Parameter: p_score_id is score engine you wish to validate
147 ||
148 || Source Tables: NA
149 ||
150 || Target Tables: NA
151 ||
152 || Creation date: 01/14/02 3:08:PM
153 ||
154 || Major Modifications: when who what
155 || 01/14/02 raverma created
156 */
157 PROCEDURE Validate_Score_Engine(p_score_id in number) IS
158
159 BEGIN
160 NULL;
161 End Validate_Score_Engine;
162
163 --Begin Bug 8933776 30-Nov-2009 barathsr
164 /*
165 || Overview: format_string-To convert the unsupported strings to XML format
166 ||
167 || Parameter: p_string - the string tat has <,>,<>
168 ||
169 || Source Tables: NA
170 ||
171 || Target Tables: NA
172 ||
173 || Creation date: 30-Nov-2009
174 ||
175 || Major Modifications: when who what
176 || 30-Nov-2009 barathsr created
177 */
178 FUNCTION format_string(p_string varchar2) return varchar2 IS
179
180 l_string varchar2(2000);
181 BEGIN
182
183 l_string := replace(p_string,'&','&'||'amp;');
184 l_string := replace(l_string,'<','&'||'lt;');
185 l_string := replace(l_string,'>','&'||'gt;');
186 -- l_string := replace(p_string,'<>','!=');
187
188 RETURN l_string;
189
190 END format_string;
191
192
193 /*
194 || Overview: print_clob-To format the xml data and write to o/p file
195 ||
196 || Parameter: lob_loc - the clob that needs to be formatted and written to o/p file
197 ||
198 || Source Tables: NA
199 ||
200 || Target Tables: NA
201 ||
202 || Creation date: 30-Nov-2009
203 ||
204 || Major Modifications: when who what
205 || 30-Nov-2009 barathsr created
206 */
207 PROCEDURE PRINT_CLOB (lob_loc in clob) IS
208
209 /*-----------------------------------------------------------------------+
210 | Local Variable Declarations and initializations |
211 +-----------------------------------------------------------------------*/
212
213 l_api_name CONSTANT VARCHAR2(30) := 'PRINT_CLOB';
214 l_api_version CONSTANT NUMBER := 1.0;
215 c_endline CONSTANT VARCHAR2 (1) := '
216 ';
217 c_endline_len CONSTANT NUMBER := LENGTH (c_endline);
218 l_start NUMBER := 1;
219 l_end NUMBER;
220 l_one_line VARCHAR2 (7000);
221 l_charset VARCHAR2(100);
222
223 /*-----------------------------------------------------------------------+
224 | Cursor Declarations |
225 +-----------------------------------------------------------------------*/
226 BEGIN
227 --iex_debug_pub.LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
228 FND_FILE.put_line( FND_FILE.LOG,'inside print_clob');
229
230 LOOP
231 l_end :=
232 DBMS_LOB.INSTR (lob_loc => lob_loc,
233 pattern => c_endline,
234 offset => l_start,
235 nth => 1
236 );
237
238 -- FND_FILE.put_line( FND_FILE.LOG,'l_end-->'||l_end);
239
240
241 IF (NVL (l_end, 0) < 1)
242 THEN
243 EXIT;
244 END IF;
245
246 l_one_line :=
247 DBMS_LOB.SUBSTR (lob_loc => lob_loc,
248 amount => l_end - l_start,
249 offset => l_start
250 );
251 -- FND_FILE.put_line( FND_FILE.LOG,'l_one_line-->'||l_one_line);
252 -- FND_FILE.put_line( FND_FILE.LOG,'c_endline_len-->'||c_endline_len);
253 l_start := l_end + c_endline_len;
254 -- FND_FILE.put_line( FND_FILE.LOG,'l_start-->'||l_start);
255 -- FND_FILE.put_line( FND_FILE.LOG,'32');
256 Fnd_File.PUT_line(Fnd_File.OUTPUT,l_one_line);
257
258 END LOOP;
259
260 END PRINT_CLOB;
261
262 --End Bug 8933776 30-Nov-2009 barathsr
263
264 /*
265 * clchang added this new procedure 10/18/04 for 11.5.11.
266 * this procedure will get the score_range_low, score_range_high,
267 * out_of_range_rule for a given score engine, and update the
268 * global variables: G_MIN_SCORE, G_MAX_SCORE, G_RULE.
269 *
270 * Parameter: P_SCORE_ID Scoring_Engine
271 * Major Modifications:
272 * when who what
273 * 10/18/04 clchang created
274 ******/
275 PROCEDURE getScoreRange(P_SCORE_ID IN NUMBER )
276 IS
277
278 CURSOR c_chk_range(p_score_id NUMBER) IS
279 SELECT NVL(WEIGHT_REQUIRED, 'N'),
280 NVL(SCORE_RANGE_LOW, IEX_SCORE_NEW_PVT.G_MIN_SCORE),
281 NVL(SCORE_RANGE_HIGH, IEX_SCORE_NEW_PVT.G_MAX_SCORE),
282 NVL(OUT_OF_RANGE_RULE, IEX_SCORE_NEW_PVT.G_RULE)
283 FROM IEX_SCORES
284 WHERE SCORE_ID = p_score_id;
285
286 l_weight_required VARCHAR2(3);
287 l_low varchar2(2000);
288 l_high varchar2(2000);
289 l_rule varchar2(20);
290
291 BEGIN
292
293 -- chk the score range
294 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
295 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreRange: score_id=' || p_score_id);
296 END IF;
297
298 BEGIN
299 OPEN c_chk_range(p_score_id);
300 FETCH c_chk_range
301 INTO l_weight_required, l_low, l_high, l_rule;
302 CLOSE c_chk_range;
303
304 EXCEPTION
305 WHEN NO_DATA_FOUND THEN
306 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
307 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreRange: Error getting score range: ' || sqlerrm);
308 END IF;
309 l_weight_required := IEX_SCORE_NEW_PVT.G_WEIGHT_REQUIRED;
310 l_low := IEX_SCORE_NEW_PVT.G_MIN_SCORE;
311 l_high := IEX_SCORE_NEW_PVT.G_MAX_SCORE;
312 l_rule := IEX_SCORE_NEW_PVT.G_RULE;
313 WHEN OTHERS THEN
314 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
315 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreRange: Error getting scr range: ' || sqlerrm);
316 END IF;
317 l_weight_required := IEX_SCORE_NEW_PVT.G_WEIGHT_REQUIRED;
318 l_low := IEX_SCORE_NEW_PVT.G_MIN_SCORE;
319 l_high := IEX_SCORE_NEW_PVT.G_MAX_SCORE;
320 l_rule := IEX_SCORE_NEW_PVT.G_RULE;
321 END;
322
323 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
324 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreRange: weight:' || l_weight_required);
325 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreRange: low:' || l_low);
326 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreRange: high:' || l_high);
327 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreRange: rule:' || l_rule);
328 END IF;
329
330 IEX_SCORE_NEW_PVT.G_WEIGHT_REQUIRED := l_weight_required;
331 IEX_SCORE_NEW_PVT.G_MIN_SCORE := l_low;
332 IEX_SCORE_NEW_PVT.G_MAX_SCORE := l_high;
333 IEX_SCORE_NEW_PVT.G_RULE := l_rule;
334
335 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
336 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreRange: g_weight:' || IEX_SCORE_NEW_PVT.G_WEIGHT_REQUIRED);
337 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreRange: g_low:' || IEX_SCORE_NEW_PVT.G_MIN_SCORE);
338 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreRange: g_high:' || IEX_SCORE_NEW_PVT.G_MAX_SCORE);
339 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreRange: g_rule:' || IEX_SCORE_NEW_PVT.G_RULE);
340 END IF;
341
342 END getScoreRange;
343
344
345
346
347 /*
348 || this procedure will get all components for a given score engine
349 || and return them as a tbl
350 ||
351 || Parameter: P_SCORE_ID Scoring_Engine
352 || X_SCORE_COMP_TBL = table of components attached to the Scoring engine
353 ||
354 || Return value: select statement for the Universe
355 ||
356 || Source Tables: IEX_SCORES, IEX_SCORE_COMPONENTS, IEX_SCORE_COMP_TYPES_B/TL
357 ||
358 || Target Tables: none
359 ||
360 || Creation date: 01/14/02 1:55:PM
361 ||
362 || Major Modifications: when who what
363 || 01/14/02 raverma created
364 || 03/12/02 raverma added function_flag to return tbl
365 || 10/18/04 clchang updated this procedure for scoring engine enhancement
366 || in 11.5.11.
367 || 1. new column METRIC_FLAG in iex_score_components
368 */
369 PROCEDURE getComponents(P_SCORE_ID IN NUMBER,
370 X_SCORE_COMP_TBL OUT NOCOPY IEX_SCORE_NEW_PVT.SCORE_ENG_COMP_TBL)
371 IS
372
373 -- clchang updated the cursor 10/18/04 with metric_flag;
374 --
375 -- this cursor will enumerate all components for a particular engine
376 CURSOR c_score_components(p_score_id NUMBER) IS
377 SELECT
378 SCORE_COMPONENT_ID,
379 SCORE_COMP_WEIGHT,
380 SCORE_COMP_VALUE,
381 NVL(FUNCTION_FLAG, 'N') FUNCTION_FLAG
382 FROM
383 IEX_SCORE_ENG_COMPONENTS_V
384 WHERE SCORE_ID = p_score_id
385 AND NVL(METRIC_FLAG, 'N') = 'N'
386 order by score_component_id;
387
388
389 l_score_comp_tbl IEX_SCORE_NEW_PVT.SCORE_ENG_COMP_TBL;
390 i NUMBER := 0;
391 l_score_comp_id NUMBER;
392 l_score_component_weight NUMBER(3,2);
393 l_score_comp_value VARCHAR2(2000);
394 l_function_flag VARCHAR2(1);
395
396 Begin
397
398 -- IF PG_DEBUG < 10 THEN
399 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
400 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getComponents: getting Score Engine Components for Engine: ' || p_score_id);
401 END IF;
402
403 OPEN c_score_components(p_score_id);
404 LOOP
405 i := i + 1;
406 FETCH c_score_components INTO
407 l_score_comp_id, l_score_component_weight, l_score_comp_value, l_function_flag;
408 EXIT WHEN c_score_components%NOTFOUND;
409 l_score_comp_tbl(i).SCORE_COMPONENT_ID := l_score_comp_id;
410 l_score_comp_tbl(i).SCORE_COMP_WEIGHT := l_score_component_weight;
411 l_score_comp_tbl(i).SCORE_COMP_VALUE := l_score_comp_value;
412 l_score_comp_tbl(i).FUNCTION_FLAG := l_function_flag;
413 --- Begin - Andre Araujo - 11/02/2004 - New storage mode, Scores_tbl becomes too big - TAR 4040621.994
414 l_score_comp_tbl(i).SCORE_ID := p_score_id;
415 --- End - Andre Araujo - 11/02/2004 - New storage mode, Scores_tbl becomes too big - TAR 4040621.994
416 END LOOP;
417
418 -- IF PG_DEBUG < 10 THEN
419 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
420 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getComponents: components found ' || l_score_comp_tbl.count);
421 END IF;
422 x_score_comp_tbl := l_score_comp_tbl;
423
424 CLOSE c_score_components;
425
426 Exception
427 When No_Data_Found then
428 x_score_comp_tbl := l_score_comp_tbl;
429 CLOSE c_score_components;
430
431 When others Then
432 x_score_comp_tbl := l_score_comp_tbl;
433 CLOSE c_score_components;
434
435 end getComponents;
436
437 /*
438 || Overview: This is the "main" scoring function. It will score any objects using the
439 || table of components passed to. The assumption is that any validation has been done already AND
440 || the table of components passed here is appropriate for the universe of IDS
441 ||
442 || Parameter: p_score_comp_tbl => components to use for scoring execution
443 || t_object_ids => universe of object_ids you wish to score
444 || (this universe MUST be valid for the components definition)
445 || x_scores_tbl => table of scores for the IDs passed
446 ||
447 || 11/02/04 acaraujo x_scores_tbl returns the bridge to the next concurrent prog.
448 || and scores are store as per the G_BATCH_SIZE to the history table
449 ||
450 || Source Tables: iex_score_comp_det
451 ||
452 || Target Tables: NA
453 ||
454 || Creation date: 01/14/02 5:27:PM
455 ||
456 || Major Modifications: when who what
457 || 01/14/02 raverma created
458 || 10/18/04 clchang updated this procedure for scoring engine enhancement
459 || in 11.5.11.
460 || 1. new column METRIC_FLAG in iex_score_components
461 || 2. new columns 'WEIGHT_REQUIRED, SCORE_RANGE_LOW,
462 || SCORE_RANGE_HIGH, OUT_OF_RANGE_RULE' in iex_scores
463 || 3. no 1-100 score limitation;
464 || 4. weight_required decides the weight of comp;
465 || 5. in comp det, the value could be formula;
466 || (only one BIND Var :result could be used.)
467 ||
468 || 11/02/04 acaraujo x_scores_tbl returns the bridge to the next concurrent prog.
469 || and scores are store as per the G_BATCH_SIZE to the history table
470 */
471 procedure getScores(p_score_comp_tbl IN IEX_SCORE_NEW_PVT.SCORE_ENG_COMP_TBL,
472 t_object_ids IN IEX_FILTER_PUB.UNIVERSE_IDS,
473 x_scores_tbl OUT NOCOPY IEX_SCORE_NEW_PVT.SCORES_TBL)
474 IS
475
476 l_api_name varchar2(10) ;
477 l_universe_size number := 0;
478 l_count NUMBER := 0;
479 l_components_count number := 0;
480
481 l_weight_required VARCHAR2(3);
482 l_low varchar2(2000);
483 l_high varchar2(2000);
484 l_rule varchar2(20);
485 --l_raw_score number := IEX_SCORE_NEW_PVT.G_MIN_SCORE;
486 l_raw_score number := 0;
487 l_value VARCHAR2(2000);
488 l_new_value VARCHAR2(2000);
489
490 l_running_score number := 0;
491 l_component_score number := 0;
492 l_count2 number := 0;
493 l_score_comp_tbl IEX_SCORE_NEW_PVT.SCORE_ENG_COMP_TBL ;
494 l_score_component_id NUMBER;
495 l_score_component_sql VARCHAR2(2500);
496 l_scores_tbl IEX_SCORE_NEW_PVT.SCORES_TBL;
497 vSql varchar2(2500);
498
499 type COMPONENT_RANGE is table of NUMBER
500 index by binary_integer;
501 l_component_range_tbl COMPONENT_RANGE;
502 i NUMBER := 0;
503 l_execute_style VARCHAR2(1); -- are we using select or function call
504
505 --- Begin - Andre Araujo - 11/02/2004 - New storage mode, this one respects the commit size - TAR 4040621.994
506 l_new_scores_tbl IEX_SCORE_NEW_PVT.NEW_SCORES_TBL ;
507 l_objects_tbl IEX_SCORE_NEW_PVT.SCORE_OBJECTS_TBL ;
508 l_scorecount number := 0;
509 l_bridge NUMBER;
510 --- End - Andre Araujo - 11/02/2004 - New storage mode, this one respects the commit size - TAR 4040621.994
511
512 -- Begin - Andre Araujo - 12/17/2004 - Store del_buffers only if we need to
513 l_conc_prog_name VARCHAR2(1000);
514 -- End - Andre Araujo - 12/17/2004 - Store del_buffers only if we need to
515 --Begin Bug 8933776 30-Nov-2009 barathsr
516 l_xml_body_2 varchar2(1000);
517 l_jtf_obj_code varchar2(100);
518 l_object_id number;
519 -- l_party_name varchar(360);
520 l_object_name varchar2(360);
521 l_acct_number varchar2(100);
522 --End Bug 8933776 30-Nov-2009 barathsr
523 t_object_code varchar2(100); -- added for bug 13426796 by sunagesh on 25.11.2011
524 t_prf_score_trans varchar2(1) := nvl(fnd_profile.value('IEX_STORE_PS_SCORE'),'Y'); -- added for bug 13426796 by sunagesh on 25.11.2011
525 BEGIN
526
527 l_api_name := 'getScores';
528 l_score_comp_tbl := p_score_comp_tbl;
529
530 -- IF PG_DEBUG < 10 THEN
531 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
532 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: Start time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
533 END IF;
534 --
535 -- Api body
536 --
537 --- Begin - Andre Araujo - 11/02/2004 - New storage mode, this one respects the commit size - TAR 4040621.994
538 --Commented by schekuri for bug#6373998 by schekuri on 31-Aug-2007
539 --Select IEX_DEL_WF_S.NEXTVAL INTO l_bridge FROM Dual;
540 --- End - Andre Araujo - 11/02/2004 - New storage mode, this one respects the commit size - TAR 4040621.994
541
542 l_bridge := l_del_buff_bridge; --Added by schekuri for bug#6373998 by schekuri on 31-Aug-2007
543 FND_FILE.PUT_LINE(FND_FILE.LOG,'Using bridge id ' || l_bridge);
544 l_universe_size := t_object_ids.count;
545 -- IF PG_DEBUG < 10 THEN
546 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
547 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: Universe size is ' || l_universe_size);
548 END IF;
549
550 -- Begin - Andre Araujo - 12/17/2004 - Store del_buffers only if we need to
551 Begin -- This will be an exception block
552 Select NVL(CONCURRENT_PROG_NAME, 'X')
553 Into l_conc_prog_name
554 From IEX_SCORES scr, IEX_SCORE_COMPONENTS scomp
555 Where scomp.score_component_id = p_score_comp_tbl(1).SCORE_COMPONENT_ID
556 AND scr.Score_ID = scomp.score_id;
557 exception
558 when OTHERS THEN
559 l_conc_prog_name := 'X';
560 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_SCORE: getScores: Exception getting the concurrent program. Error: ' || sqlerrm );
561 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_SCORE: getScores: Program will continue, no concurrent program will be launched' );
562 end;
563 -- End - Andre Araujo - 12/17/2004 - Store del_buffers only if we need to
564
565 FOR l_count IN 1..l_universe_size LOOP
566
567 if PG_DEBUG <= 5 then
568 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
569 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: Scoring item ' || l_count || ' in universe');
570 END IF;
571 end if;
572 l_components_count := p_score_comp_tbl.count;
573 l_running_score := 0;
574 -- fnd_file.put_line(fnd_file.log,'universe size-->'||l_universe_size);
575
576 -- l_xml_body_2:=l_new_line||'<COMP_DET>'||l_new_line;
577 -- dbms_lob.writeAppend(tempResult, length(l_xml_body_2), l_xml_body_2);
578
579 --Begin Bug 8933776 30-Nov-2009 barathsr
580 begin
581 select score.jtf_object_code
582 into l_jtf_obj_code
583 from iex_scores score
584 where score_id=p_score_comp_tbl(1).score_id;
585 fnd_file.put_line(fnd_file.log,'obj_code-->'||l_jtf_obj_code);
586 fnd_file.put_line(fnd_file.log,'unv_id-->'||t_object_ids(l_count));
587
588 if l_jtf_obj_code is not null then
589
590 if l_jtf_obj_code='PARTY' then
591 select party_name
592 into l_object_name
593 from hz_parties
594 where party_id=t_object_ids(l_count);
595 l_object_id:=t_object_ids(l_count);
596 elsif l_jtf_obj_code='IEX_ACCOUNT' then
597 select account_number
598 into l_object_name
599 from hz_cust_accounts
600 where cust_account_id=t_object_ids(l_count);
601 l_object_id:=t_object_ids(l_count);
602 elsif l_jtf_obj_code='IEX_BILLTO' then
603 select hcsua.location,hca.account_number
604 into l_object_name,l_acct_number
605 from hz_cust_site_uses_all hcsua,hz_cust_acct_sites_all hcasa, hz_cust_accounts hca
606 where hcsua.cust_acct_site_id=hcasa.cust_acct_site_id
607 and hcasa.cust_account_id=hca.cust_account_id
608 and hcsua.site_use_code='BILL_TO'
609 and hcsua.site_use_id=t_object_ids(l_count);
610 l_object_id:=t_object_ids(l_count);
611 elsif l_jtf_obj_code='IEX_INVOICES' then
612 select aps.trx_number,hca.account_number
613 into l_object_name,l_acct_number
614 from ar_payment_schedules_all aps,hz_cust_accounts hca
615 where aps.customer_id=hca.cust_account_id
616 and aps.payment_schedule_id=t_object_ids(l_count)
617 and aps.payment_schedule_id>0;
618 l_object_id:=t_object_ids(l_count);
619 else
620 l_object_id:=t_object_ids(l_count); --Added for Bug 9790046 22-Jun-2010 barathsr
621 end if;
622 end if;
623 -- FND_FILE.put_line( FND_FILE.LOG,'*****get various score details************');
624 /*l_xml_body_2:=l_new_line||'<COMP_DET>';
625 -- l_xml_body_2:=l_xml_body_2||l_new_line||'<PARTY_NAME>'||format_string(l_obj_name)||'</PARTY_NAME>';
626 l_xml_body_2:=l_xml_body_2||l_new_line||'<OBJECT_NAME>'||format_string(l_object_name)||'</OBJECT_NAME>';
627 l_xml_body_2:=l_xml_body_2||l_new_line||'<ACCT_NUMBER>'||format_string(l_acct_number)||'</ACCT_NUMBER>';
628 l_xml_body_2:=l_xml_body_2||l_new_line||'<OBJECT_ID>'||l_object_id||'</OBJECT_ID>';
629 l_xml_body_2:=l_xml_body_2||l_new_line||'<COMP_VALUES>';
630 dbms_lob.writeAppend(tempResult, length(l_xml_body_2), l_xml_body_2);*/
631 --Commented to fix 12711600 SNUTHALA 5/JUL/11
632 exception
633 when others then
634 FND_FILE.PUT_LINE(FND_FILE.LOG, 'error in get scores in getting jtf obj details'||sqlerrm);
635 IEX_DEBUG_PUB.logMessage('error in get scores in getting jtf obj details'||sqlerrm);
636 end;
637 --Begin fix 12711600 SNUTHALA 5/JUL/11
638 l_xml_body_2:=l_new_line||'<COMP_DET>';
639
640 l_xml_body_2:=l_xml_body_2||l_new_line||'<OBJECT_NAME>'||format_string(l_object_name)||'</OBJECT_NAME>';
641 if l_acct_number is not null then
642 l_xml_body_2:=l_xml_body_2||l_new_line||'<ACCT_NUMBER>'||format_string(l_acct_number)||'</ACCT_NUMBER>';
643 end if;
644 if l_object_id is not null then
645 l_xml_body_2:=l_xml_body_2||l_new_line||'<OBJECT_ID>'||l_object_id||'</OBJECT_ID>';
646 end if;
647 l_xml_body_2:=l_xml_body_2||l_new_line||'<COMP_VALUES>';
648 dbms_lob.writeAppend(tempResult, length(l_xml_body_2), l_xml_body_2);
649 --End fix 12711600 SNUTHALA 5/JUL/11
650
651 --End Bug 8933776 30-Nov-2009 barathsr
652 -- fnd_file.put_line(fnd_file.log,'callin get1score');
653
654 --- get1Score removed from here
655 l_running_score := get1Score( l_score_comp_tbl, t_object_ids(l_count) );
656 -- fnd_file.put_line(fnd_file.log,'out of get1score');
657 --- End get1Score removed from here
658 -- /* 3. for each component, execute SQL and get value */
659 -- FOR l_count2 IN 1..l_components_count LOOP
660 -- l_score_component_id := l_score_comp_tbl(l_count2).score_component_id;
661 -- l_score_component_sql := l_score_comp_tbl(l_count2).SCORE_COMP_VALUE;
662 -- l_execute_style := l_score_comp_tbl(l_count2).function_flag;
663 -- -- initialize this to the minimum for any given component
664 -- --l_raw_score := IEX_SCORE_PVT.G_MIN_SCORE;
665 --
666 -- if PG_DEBUG <= 5 then
667 -- IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
668 -- IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: executing Component ' || l_count2 || ' CompID is: ' || l_score_component_id);
669 -- IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: Execute Stmt: ' || l_score_component_sql || ' Execute Style: ' || l_execute_style);
670 -- IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: Bind Variable: ' || t_object_ids(l_count));
671 -- END IF;
672 -- end if;
673 --
674 -- /* executing dynamic sql for component */
675 -- if l_score_component_sql is not null then
676 -- BEGIN
677 --
678 -- -- Execute SQL statement only when function syntax is not found
679 -- if l_execute_style = 'N' then
680 -- -- simple select statement
681 -- EXECUTE IMMEDIATE l_score_component_sql
682 -- INTO l_component_score
683 -- USING t_object_ids(l_count);
684 -- else
685 -- -- function to execute
686 -- -- to do - pass the score component id for Function calls only
687 -- EXECUTE IMMEDIATE l_score_component_sql
688 -- USING in t_object_ids(l_count),
689 -- in l_score_component_id,
690 -- out l_component_score;
691 -- end if;
692 --
693 -- EXCEPTION
694 --
695 -- -- assign the "Lowest" Detail for the component
696 -- -- in order to do this we must know what is "high" and "low" range of component
697 --
698 -- WHEN OTHERS THEN
699 -- -- figure out whether the component details are better higher or worse higher
700 -- IF PG_DEBUG <= 5 THEN
701 -- IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
702 -- IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: Failed to calculate for component ' || l_score_component_id );
703 -- END IF;
704 -- SELECT Range_Low
705 -- BULK COLLECT INTO l_component_range_tbl
706 -- FROM iex_score_comp_det
707 -- where score_component_id = l_score_component_id
708 -- order by value;
709 --
710 -- if l_component_range_tbl(1) < l_component_range_tbl(2) then
711 -- -- assign first comnponent detail row range to value
712 -- l_component_score := l_component_range_tbl(1);
713 -- else
714 -- -- assign last comnponent detail row range to value
715 -- i := l_component_range_tbl.count;
716 -- l_component_score := l_component_range_tbl(i);
717 -- end if;
718 --
719 -- END;
720 --
721 -- if PG_DEBUG <= 5 then
722 -- IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
723 -- IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: Successfully calculated component score: ' || l_component_score);
724 -- END IF;
725 -- end if;
726 --
727 -- end if;
728 --
729 -- /* 4. For each component value, get the details of the component
730 -- and store the value for that score_comp_detail */
731 -- BEGIN
732 -- -- clchang updated 10/18/04 for 11.5.11
733 -- -- new column NEW_VALUE instead of VALUE in iex_score_comp_det;
734 -- --vSql := 'SELECT VALUE ' ||
735 -- vSql := 'SELECT upper(NEW_VALUE) ' ||
736 -- ' FROM IEX_SCORE_COMP_DET ' ||
737 -- ' WHERE SCORE_COMPONENT_ID = :p_score_comp_id AND ' ||
738 -- ' :p_component_score >= RANGE_LOW AND ' ||
739 -- ' :p_component_score <= RANGE_HIGH ';
740 -- if PG_DEBUG <= 5 then
741 -- IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
742 -- IEX_DEBUG_PUB.logMessage('getScores: ' || 'Getting Details for component with ' || vSQL);
743 -- END IF;
744 -- end if;
745 --
746 -- -- clchang updated 10/18/04 for 11.5.11
747 -- -- the value from det could be formula (including bind var :result);
748 -- Execute Immediate vSql
749 -- --INTO l_raw_score
750 -- INTO l_value
751 -- USING l_score_component_id, l_component_score, l_component_score;
752 --
753 -- --IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
754 -- --IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: Component raw score is ' || l_raw_score ||
755 -- ' Component weight is ' || l_score_comp_tbl(l_count2).SCORE_COMP_WEIGHT);
756 -- --END IF;
757 --
758 --
759 -- -- BEGIN clchang added 10/18/04 for scr engine enhancement in 11.5.11
760 --
761 -- IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
762 -- IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: l_value=' || l_value);
763 -- END IF;
764 -- -- chk the value is a formula or not
765 -- IF (INSTR(l_value, ':RESULT') > 0 ) THEN
766 -- l_new_value := replace(l_value, ':RESULT', l_component_score);
767 -- IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
768 -- IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: FORMULA');
769 -- IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: l_new_value=' || l_new_value);
770 -- END IF;
771 -- vSql := 'SELECT ' || l_new_value || ' FROM DUAL';
772 -- IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
773 -- IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: vSql=' || vSql);
774 -- END IF;
775 -- Execute Immediate vSql
776 -- INTO l_raw_score;
777 -- ELSE
778 -- l_raw_score := TO_NUMBER( l_value);
779 -- END IF;
780 -- IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
781 -- IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: l_raw_score=' || l_raw_score);
782 -- END IF;
783 --
784 --
785 -- l_weight_required := IEX_SCORE_NEW_PVT.G_WEIGHT_REQUIRED;
786 --
787 -- -- if weight_required <> Y, sum(score of each comp);
788 -- IF (l_weight_required = 'Y') THEN
789 -- --l_running_score:=l_running_score + round((l_raw_score * l_score_comp_tbl(l_count2).SCORE_COMP_WEIGHT));
790 -- l_running_score:=l_running_score + round((l_raw_score * l_score_comp_tbl(l_count2).SCORE_COMP_WEIGHT),2);
791 -- ELSE
792 -- --l_running_score:=l_running_score + round(l_raw_score );
793 -- l_running_score:=l_running_score + round(l_raw_score,2 );
794 -- END IF;
795 -- IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
796 -- IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: l_running_score=' || l_running_score);
797 -- END IF;
798 -- -- END clchang added 10/18/04 for scr engine enhancement in 11.5.11
799 --
800 -- /*
801 -- l_running_score:=l_running_score + round((l_raw_score * l_score_comp_tbl(l_count2).SCORE_COMP_WEIGHT));
802 -- */
803 --
804 -- --IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
805 -- --IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: Component Running score is ' || l_running_score);
806 -- --END IF;
807 -- EXCEPTION
808 -- WHEN NO_DATA_FOUND THEN
809 -- IF PG_DEBUG < 10 THEN
810 -- IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
811 -- IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: Error getting component detail: ' || sqlerrm);
812 -- END IF;
813 -- l_running_score := l_running_score;
814 -- WHEN OTHERS THEN
815 -- IF PG_DEBUG < 10 THEN
816 -- IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
817 -- IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: Error getting component detail: ' || sqlerrm);
818 -- END IF;
819 -- l_running_score := l_running_score;
820 -- END;
821 --
822 -- END LOOP; -- component loop
823 --
824 --
825 -- -- BEGIN clchang added 10/18/04 for scr engine enhancement in 11.5.11
826 --
827 -- clchang updated the score logic
828 /***************
829 -- if the score value falls above or below the hard coded floor / ceiling we will force the score
830 -- to the floor or ceiling
831 if l_running_score < IEX_SCORE_NEW_PVT.G_MIN_SCORE then
832 l_running_score := IEX_SCORE_NEW_PVT.G_MIN_SCORE;
833 elsif l_running_score > IEX_SCORE_NEW_PVT.G_MAX_SCORE then
834 l_running_score := IEX_SCORE_NEW_PVT.G_MAX_SCORE;
835 end if;
836 *******************************************/
837
838 /*********************
839 * with the new logic on scr engine;
840 * 1.no score limitation 1-100;
841 * 2.the score range should between score_range_low and score_range_high of
842 * each scoring engine;
843 * 3. if the score is out of range, following the out_of_range_rule of
844 * each scoring engine;
845 * ex: one scoring engine with low -50, high 999 and rule 'CLOSEST';
846 * if the score is -100, then the final score should be the closest
847 * score of score range => -50;
848 * if the rule is farthest, then the final score should be 999;
849 ***********************************************************************/
850
851 -- get the final score
852 l_rule := IEX_SCORE_NEW_PVT.G_RULE;
853 IF (l_rule = 'CLOSEST') THEN
854 if l_running_score < IEX_SCORE_NEW_PVT.G_MIN_SCORE then
855 l_running_score := IEX_SCORE_NEW_PVT.G_MIN_SCORE;
856 elsif l_running_score > IEX_SCORE_NEW_PVT.G_MAX_SCORE then
857 l_running_score := IEX_SCORE_NEW_PVT.G_MAX_SCORE;
858 end if;
859 ELSE
860 if l_running_score < IEX_SCORE_NEW_PVT.G_MIN_SCORE then
861 l_running_score := IEX_SCORE_NEW_PVT.G_MAX_SCORE;
862 elsif l_running_score > IEX_SCORE_NEW_PVT.G_MAX_SCORE then
863 l_running_score := IEX_SCORE_NEW_PVT.G_MIN_SCORE;
864 end if;
865 END IF;
866 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
867 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: score:' || l_running_score);
868 END IF;
869 -- END clchang added 10/18/04 for scr engine enhancement in 11.5.11
870 --Begin Bug 8933776 30-Nov-2009 barathsr
871 l_xml_body_2:=l_new_line||'</COMP_VALUES>';
872 l_xml_body_2:=l_xml_body_2||l_new_line||'<FINAL_SCORE>'||l_running_score||'</FINAL_SCORE>';
873 l_xml_body_2:=l_xml_body_2||l_new_line||'</COMP_DET>';
874
875 dbms_lob.writeAppend(tempResult, length(l_xml_body_2), l_xml_body_2);
876 --End Bug 8933776 30-Nov-2009 barathsr
877
878 -- fnd_file.put_line(fnd_file.log,'end of comp_det tag');
879
880
881 -- fill out return table
882 --- Begin - Andre Araujo - 11/02/2004 - New storage mode, this one respects the commit size - TAR 4040621.994
883 --l_scores_tbl(l_count) := l_running_score;
884
885 l_scorecount := l_scorecount + 1;
886 l_objects_tbl(l_scorecount) := t_object_ids(l_count);
887 l_new_scores_tbl(l_scorecount) := l_running_score;
888 if l_scorecount >= G_BATCH_SIZE then
889 --start bug 13426796 by sunagesh on 25-11-2011
890 select jtf_object_code into t_object_code
891 from iex_scores
892 where score_id = l_score_comp_tbl(1).SCORE_ID;
893 if t_prf_score_trans = 'Y' then
894 storeScoreHistory ( l_score_comp_tbl(1).SCORE_ID, l_objects_tbl, l_new_scores_tbl );
895 else
896 if t_object_code<>'IEX_INVOICES' then
897 storeScoreHistory ( l_score_comp_tbl(1).SCORE_ID, l_objects_tbl, l_new_scores_tbl );
898 end if;
899 end if;
900 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
901 IEX_DEBUG_PUB.logMessage('IEX_STORE_PS_SCORE: getScores:Profile:Store Score Histories for Transactions: ' ||t_prf_score_trans);
902 END IF;
903 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Profile:Store Score Histories for Transactions: '||t_prf_score_trans);
904 --end bug 13426796 by sunagesh on 25-11-2011
905 -- Begin - Andre Araujo - 12/17/2004 - Store del_buffers only if we need to
906 if (l_conc_prog_name <> 'X') then
907 storeDelBuffers ( l_score_comp_tbl(1).SCORE_ID, l_objects_tbl, l_new_scores_tbl,l_bridge);
908 end if;
909 -- End - Andre Araujo - 12/17/2004 - Store del_buffers only if we need to
910
911
912 l_scorecount := 0;
913 l_objects_tbl.delete;
914 l_new_scores_tbl.delete;
915 l_scores_tbl.delete;
916 end if;
917 --- End - Andre Araujo - 11/02/2004 - New storage mode, this one respects the commit size - TAR 4040621.994
918
919
920
921 END LOOP; -- universe loop
922
923 -- fnd_file.put_line(fnd_file.log,'out of universe loop in get scores');
924
925 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Getting the final score: ');
926 --l_xml_body_2:=l_xml_body_2||l_new_line||'</COMP_DET>'||l_new_line;
927
928 -- dbms_lob.writeAppend(tempResult, length(l_xml_body_2), l_xml_body_2);
929 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Closing comp_det ');
930
931 --- Begin - Andre Araujo - 11/02/2004 - New storage mode, this one respects the commit size - TAR 4040621.994
932 if l_scorecount > 0 then -- Store the leftovers
933 --start bug 13426796 by sunagesh on 25-11-2011
934 select jtf_object_code into t_object_code
935 from iex_scores
936 where score_id = l_score_comp_tbl(1).SCORE_ID;
937 if t_prf_score_trans = 'Y' then
938 storeScoreHistory ( l_score_comp_tbl(1).SCORE_ID, l_objects_tbl, l_new_scores_tbl );
939 else
940 if t_object_code<>'IEX_INVOICES' then
941 storeScoreHistory ( l_score_comp_tbl(1).SCORE_ID, l_objects_tbl, l_new_scores_tbl );
942 end if;
943 end if;
944 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
945 IEX_DEBUG_PUB.logMessage('IEX_STORE_PS_SCORE: getScores:Profile:Store Score Histories for Transactions: ' ||t_prf_score_trans);
946 END IF;
947 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Profile:Store Score Histories for Transactions: '||t_prf_score_trans);
948 --end bug 13426796 by sunagesh on 25-11-2011
949 -- Begin - Andre Araujo - 12/17/2004 - Store del_buffers only if we need to
950 if (l_conc_prog_name <> 'X') then
951 storeDelBuffers ( l_score_comp_tbl(1).SCORE_ID, l_objects_tbl, l_new_scores_tbl,l_bridge);
952 end if;
953 -- End - Andre Araujo - 12/17/2004 - Store del_buffers only if we need to
954
955
956 end if;
957
958 -- fnd_file.put_line(fnd_file.log,'end of get scores');
959
960 l_scores_tbl.delete;
961 l_scores_tbl(1) := l_bridge;
962 --- End - Andre Araujo - 11/02/2004 - New storage mode, this one respects the commit size - TAR 4040621.994
963
964 x_scores_tbl := l_scores_tbl;
965
966 -- IF PG_DEBUG < 10 THEN
967 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
968 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
969 END IF;
970
971 Exception
972
973 When Others Then
974 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in getScores: ' || sqlerrm );
975 RAISE FND_API.G_EXC_ERROR;
976 END getScores;
977
978 /*
979 || Overview: score all objects for a given scoring engine
980 ||
981 || Parameter: p_score_id => scoring engine ID
982 ||
983 || Source Tables: IEX_SCORES, IEX_SCORE_COMPONENTS_VL, IEX_SCORE_COMP_TYPES, IEX_SCORE_COMP_DET,
984 || IEX_OBJECT_FILTERS
985 ||
986 || Target Tables:
987 ||
988 || Creation date: 01/22/02 3:14:PM
989 ||
990 || Major Modifications: when who what
991 || 01/22/02 raverma created
992 */
993 procedure scoreObjects(p_api_version IN NUMBER,
994 p_init_msg_list IN VARCHAR2,
995 p_commit IN VARCHAR2,
996 P_SCORE_ID IN NUMBER,
997 p_unv_obj_id in varchar2, --Added for Bug 8933776 17-Dec-2009 barathsr
998 p_limit_rows_val in number default null, --Added for Bug 8933776 17-Dec-2009 barathsr
999 x_return_status OUT NOCOPY VARCHAR2,
1000 x_msg_count OUT NOCOPY NUMBER,
1001 x_msg_data OUT NOCOPY VARCHAR2)
1002 IS
1003
1004 l_api_name varchar2(25);
1005 l_api_version_number number := 1;
1006 l_return_status VARCHAR2(1);
1007 l_msg_count NUMBER;
1008 l_msg_data VARCHAR2(32767);
1009
1010 --vPLSQL varchar2(5000);
1011 l_universe IEX_FILTER_PUB.UNIVERSE_IDS; -- for TestUniverse / testGetScores
1012 l_components_tbl IEX_SCORE_NEW_PVT.SCORE_ENG_COMP_TBL;-- for testGetComponents / testGetScores
1013 l_scores_tbl IEX_SCORE_NEW_PVT.SCORES_TBL; -- fore testGetScores
1014 b_valid BOOLEAN;
1015 -- l_object_type VARCHAR2(25);
1016 l_universe_size NUMBER := 0;
1017 l_conc_prog_name VARCHAR2(1000);
1018 l_submit_request_id NUMBER;
1019 l_bridge NUMBER;
1020 k NUMBER := 1;
1021 l_passes NUMBER := 0;
1022 l_mod NUMBER := 0;
1023 l_user NUMBER;
1024 i NUMBER := 1;
1025 l_program NUMBER;
1026 l_prog_appl NUMBER;
1027 l_request NUMBER;
1028 --Begin Bug 8933776 30-Nov-2009 barathsr
1029 -- for bug 16383981 increased size of l_xml_body
1030 l_xml_body varchar2(32767);
1031 -- l_new_line varchar2(1);
1032 l_score_name varchar2(100);
1033 l_object_code varchar2(100);
1034 l_object_type varchar2(100);
1035 l_obj_filter_name varchar2(100);
1036 l_obj_filter_view varchar2(100);
1037 l_cp_name varchar2(100);
1038 l_sts_det varchar2(10);
1039 l_score_low number;
1040 l_score_high number;
1041 l_score_comp_name varchar2(100);
1042 l_function_flg varchar2(10);
1043 l_score_comp_wgt number;
1044 l_score_comp_id number;
1045 l_sc_range_low IEX_SCORE_NEW_PVT.SCORES_TBL;
1046 l_sc_range_high IEX_SCORE_NEW_PVT.SCORES_TBL;
1047 l_sc_val IEX_SCORE_NEW_PVT.SCORES_TBL;
1048 l_cnt number;
1049 l_score_comp_val varchar2(5000);
1050 --End Bug 8933776 30-Nov-2009 barathsr
1051
1052 --jsanju 06/21/04 --added for wait for request check
1053 uphase VARCHAR2(255);
1054 dphase VARCHAR2(255);
1055 ustatus VARCHAR2(255);
1056 dstatus VARCHAR2(255);
1057 l_bool BOOLEAN;
1058 message VARCHAR2(32000);
1059 l_last_obj_scored NUMBER;
1060 l_last_batch boolean;
1061
1062
1063 -- Begin - schekuri - 6156648 - 10/Jun/2007 - Adding wait for request to try to make it wait
1064 bReturn boolean;
1065 vReturn varchar2(100);
1066 -- End - schekuri - 6156648 - 10/Jun/2007 - Adding wait for request to try to make it wait
1067
1068 -- start for bug 9387044
1069 vsql varchar2(1000);
1070 l_count number;
1071 l_return boolean;
1072 univ_size number;
1073 Type refCur is Ref Cursor;
1074 Universe_cur refCur;
1075 -- end for bug 9387044
1076
1077 BEGIN
1078 -- Standard Start of API savepoint
1079 SAVEPOINT scoreObjects_PVT;
1080
1081 l_api_name := 'scoreObjects';
1082
1083 -- Standard call to check for call compatibility.
1084 IF NOT FND_API.Compatible_API_Call (l_api_version_number, p_api_version,
1085 l_api_name, G_PKG_NAME)
1086 THEN
1087 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1088 END IF;
1089
1090 -- Initialize message list IF p_init_msg_list is set to TRUE.
1091 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1092 FND_MSG_PUB.initialize;
1093 END IF;
1094
1095 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1096 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: Start time:'|| TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1097 END IF;
1098
1099 -- Initialize API return status to SUCCESS
1100 x_return_status := FND_API.G_RET_STS_SUCCESS;
1101
1102 /*select score_name
1103 into l_score_name
1104 from iex_scores
1105 where score_id=p_score_id;
1106 --
1107 -- Api body
1108 --
1109
1110 -- initial variables needed
1111 select jtf_object_code into l_object_type
1112 from iex_scores
1113 where score_id = p_score_id;*/
1114 --Begin Bug 8933776 30-Nov-2009 barathsr
1115 begin
1116 SELECT score.score_name,
1117 score.jtf_object_code,
1118 obj.object_filter_name,
1119 obj.entity_name,
1120 score.concurrent_prog_name,
1121 score.status_determination,
1122 score.score_range_low,
1123 score.score_range_high
1124 into l_score_name,l_object_code,l_obj_filter_name,l_obj_filter_view,
1125 l_cp_name,l_sts_det,l_score_low,l_score_high
1126 FROM iex_scores score,
1127 iex_object_filters obj
1128 WHERE score.score_id =obj.object_id
1129 AND obj.object_filter_type='IEXSCORE'
1130 and score.score_id=p_score_id;
1131
1132 SELECT NAME
1133 into l_object_type
1134 FROM jtf_objects_vl
1135 where object_code=l_object_code;
1136
1137 -- FND_FILE.PUT_LINE(FND_FILE.LOG, '***start of xml body***');
1138 l_xml_body:= l_xml_body||l_new_line||'<SCOREDET>';
1139 l_xml_body:= l_xml_body||l_new_line||'<SCORE_ID>'||p_score_id||'</SCORE_ID>';
1140 l_xml_body:= l_xml_body||l_new_line||'<SCORE_NAME>'||format_string(l_score_name)||'</SCORE_NAME>';
1141 l_xml_body:= l_xml_body||l_new_line||'<OBJ_CODE>'||format_string(l_object_code)||'</OBJ_CODE>';
1142 l_xml_body:= l_xml_body||l_new_line||'<OBJ_TYPE>'||format_string(l_object_type)||'</OBJ_TYPE>';
1143 l_xml_body:= l_xml_body||l_new_line||'<FILTER_NAME>'||format_string(l_obj_filter_name)||'</FILTER_NAME>';
1144 l_xml_body:= l_xml_body||l_new_line||'<FILTER_VIEW>'||format_string(l_obj_filter_view)||'</FILTER_VIEW>';
1145 l_xml_body:= l_xml_body||l_new_line||'<PROGRAM_NAME>'||format_string(l_cp_name)||'</PROGRAM_NAME>';
1146 l_xml_body:= l_xml_body||l_new_line||'<STATUS_DET>'||l_sts_det||'</STATUS_DET>';
1147 l_xml_body:= l_xml_body||l_new_line||'<SCORE_LOW>'||l_score_low||'</SCORE_LOW>';
1148 l_xml_body:= l_xml_body||l_new_line||'<SCORE_HIGH>'||l_score_high||'</SCORE_HIGH>';
1149 l_xml_body:= l_xml_body||l_new_line||'<COMPONENTS>';
1150 exception
1151 when others then
1152 FND_FILE.PUT_LINE(FND_FILE.LOG, 'error in score objects in getting score details'||sqlerrm);
1153 IEX_DEBUG_PUB.logMessage('error in score objects in getting score details'||sqlerrm);
1154 end;
1155 --End Bug 8933776 30-Nov-2009 barathsr
1156 -- enumerate components for this scoring engine
1157 iex_score_new_pvt.getComponents(p_score_id => p_score_id ,
1158 X_SCORE_COMP_TBL => l_components_tbl);
1159
1160 if l_components_tbl is null or l_components_tbl.count < 1 then
1161 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1162 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: No score components for engine');
1163 END IF;
1164 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_SCORE: scoreObjects: No score components for engine ' || p_score_id);
1165 FND_MESSAGE.Set_Name('IEX', 'IEX_NO_SCORE_ENG_COMPONENTS');
1166 --Begin Bug 8933776 30-Nov-2009 barathsr
1167 l_xml_body:= l_xml_body||l_new_line||'<ERROR>'||'No Score components available for this scoring engine'||'</ERROR>';
1168 l_xml_body:= l_xml_body||l_new_line||'</COMPONENTS>';
1169 l_xml_body:= l_xml_body||l_new_line||'</SCOREDET>';
1170 -- l_xml_body:= l_xml_body||l_new_line||'</SCORE_REPORT>';
1171 dbms_lob.writeAppend(tempResult, length(l_xml_body), l_xml_body);
1172 --End Bug 8933776 30-Nov-2009 barathsr
1173 FND_MSG_PUB.Add;
1174 RAISE FND_API.G_EXC_ERROR;
1175 end if;
1176
1177 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1178 IEX_DEBUG_PUB.logMessage('scoreObjects: ' || 'Batch Size is ' || G_BATCH_SIZE || ' rows');
1179 END IF;
1180
1181 l_user := FND_GLOBAL.USER_ID;
1182 l_request := nvl(FND_GLOBAL.Conc_REQUEST_ID,0);
1183 l_program := FND_GLOBAL.CONC_PROGRAM_ID;
1184 l_prog_appl := FND_GLOBAL.PROG_APPL_ID;
1185
1186 --Begin Bug 8933776 30-Nov-2009 barathsr
1187 if l_components_tbl is not null or l_components_tbl.count>1 then
1188 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'count in comp tab-->'||l_components_tbl.count);
1189 for comp in l_components_tbl.first..l_components_tbl.last loop
1190 begin
1191 SELECT sc_typ_tl.score_comp_name,
1192 sc_typ.function_flag,
1193 sc.score_comp_weight,
1194 sc_typ.score_comp_value
1195 into l_score_comp_name,l_function_flg,l_score_comp_wgt,l_score_comp_val
1196 FROM iex_score_components sc,
1197 iex_score_comp_types_tl sc_typ_tl,
1198 iex_score_comp_types_b sc_typ
1199 WHERE sc.score_comp_type_id = sc_typ.score_comp_type_id
1200 AND sc_typ.score_comp_type_id= sc_typ_tl.score_comp_type_id
1201 AND sc_typ_tl.language ='US'
1202 AND sc.score_component_id=l_components_tbl(comp).score_component_id;
1203 l_score_comp_id:=l_components_tbl(comp).score_component_id;
1204 -- FND_FILE.PUT_LINE(FND_FILE.LOG, '***get the component details***');
1205 l_xml_body:= l_xml_body||l_new_line||'<COMPONENT>';
1206 l_xml_body:= l_xml_body||l_new_line||'<SC_COMP_ID>'||l_score_comp_id||'</SC_COMP_ID>';
1207 l_xml_body:= l_xml_body||l_new_line||'<COMP_NAME>'||format_string(l_score_comp_name)||'</COMP_NAME>';
1208 l_xml_body:= l_xml_body||l_new_line||'<FUNCTION_FLAG>'||l_function_flg||'</FUNCTION_FLAG>';
1209 l_xml_body:= l_xml_body||l_new_line||'<COMP_WEIGHT>'||l_score_comp_wgt||'</COMP_WEIGHT>';
1210 l_xml_body:= l_xml_body||l_new_line||'<COMP_FN_QRY>'||format_string(l_score_comp_val)||'</COMP_FN_QRY>';
1211 exception
1212 when others then
1213 FND_FILE.PUT_LINE(FND_FILE.LOG, 'error in score objects in getting score component details'||sqlerrm);
1214 IEX_DEBUG_PUB.logMessage('error in score objects in getting score component details'||sqlerrm);
1215 end;
1216
1217
1218 begin
1219 select count(*)
1220 into l_cnt
1221 from iex_score_comp_det
1222 where score_component_id=l_components_tbl(comp).score_component_id;
1223 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'count in comp_det tab-->'||l_cnt);
1224 for cnt in 1..l_cnt loop
1225 select range_low,range_high,new_value
1226 bulk collect into l_sc_range_low,l_sc_range_high,l_sc_val
1227 from iex_score_comp_det sc_det
1228 where score_component_id=l_components_tbl(comp).score_component_id;
1229 end loop;
1230 if l_sc_range_low.count > 0 then
1231 for val in l_sc_range_low.first..l_sc_range_low.last loop
1232 l_xml_body:= l_xml_body||l_new_line||'<COMP_RANGE>';
1233 l_xml_body:=l_xml_body||l_new_line||'<COMP_RANGE_LOW>'||l_sc_range_low(val)||'</COMP_RANGE_LOW>';
1234 l_xml_body:=l_xml_body||l_new_line||'<COMP_RANGE_HIGH>'||l_sc_range_high(val)||'</COMP_RANGE_HIGH>';
1235 l_xml_body:=l_xml_body||l_new_line||'<COMP_RANGE_VAL>'||l_sc_val(val)||'</COMP_RANGE_VAL>';
1236 l_xml_body:= l_xml_body||l_new_line||'</COMP_RANGE>';
1237 end loop;
1238 end if;
1239
1240 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'out of comp_det loop');
1241 l_xml_body:= l_xml_body||l_new_line||'</COMPONENT>';
1242 exception
1243 when others then
1244 FND_FILE.PUT_LINE(FND_FILE.LOG, 'error in score objects in getting score component cnt/range details'||sqlerrm);
1245 IEX_DEBUG_PUB.logMessage('error in score objects in getting score component cnt/range details'||sqlerrm);
1246 end;
1247 end loop;
1248 end if;
1249
1250 -- FND_FILE.PUT_LINE(FND_FILE.LOG, '***close component details***');
1251 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'tempres-->'||tempResult);
1252 --End Bug 8933776 30-Nov-2009 barathsr
1253
1254
1255 Select IEX_DEL_WF_S.NEXTVAL INTO l_del_buff_bridge FROM Dual; --Added by schekuri for bug#6373998 on 31-Aug-2007
1256 FND_FILE.PUT_LINE(FND_FILE.LOG,'Using bridge ' || l_del_buff_bridge || ' one for each scoring engine');
1257 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1258 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: got bridge id ' || l_del_buff_bridge || ' once for each scoring engine');
1259 END IF;
1260
1261 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1262 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: building Universe');
1263 END IF;
1264
1265
1266
1267 -- start for bug 9387044
1268 -- bug#5586925 score in loop to increase scaleablility
1269 /* l_last_batch := false;
1270 while not l_last_batch loop
1271 l_universe := iex_filter_pub.buildUniverse(p_object_id => p_score_id,
1272 p_query_obj_id => p_unv_obj_id, --Added for Bug 8933776 17-Dec-2009 barathsr
1273 p_limit_rows => p_limit_rows_val, --Added for Bug 8933776 17-Dec-2009 barathsr
1274 p_object_type => 'IEXSCORE',
1275 p_last_object_scored => l_last_obj_scored,
1276 x_end_of_universe => l_last_batch);
1277
1278 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: last object ' || l_last_obj_scored);
1279
1280 if (l_universe is null or l_universe.count < 1) and not l_last_batch then
1281 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1282 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: Universe size is zero');
1283 END IF;
1284 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Universe Size is Zero or Invalid for Engine ' || p_score_id);
1285
1286 FND_MESSAGE.Set_Name('IEX', 'IEX_UNIVERSE_SIZE_ZERO');
1287 FND_MSG_PUB.Add;
1288
1289 --START jsanju 10/19/05 for bug 3549051
1290 --RAISE FND_API.G_EXC_ERROR;
1291 RAISE IEX_UNIVERSE_SIZE_ZERO_ERROR;
1292 --END jsanju 10/19/05 for bug 3549051
1293
1294 end if; */
1295
1296 l_count := 0;
1297 l_universe_size:=0;
1298 univ_size:=0;
1299 vsql := iex_filter_pub.buildsql(p_object_id => p_score_id, p_object_type => 'IEXSCORE',
1300 p_query_obj_id => p_unv_obj_id, --Added for Bug 9670348 27-May-2009 barathsr
1301 p_limit_rows => p_limit_rows_val);--Added for Bug 9670348 27-May-2009 barathsr
1302
1303 open universe_cur for vsql;
1304
1305 loop
1306 l_universe.delete;
1307
1308 l_count := l_count +1;
1309 fetch universe_cur bulk collect into l_universe limit G_BATCH_SIZE;
1310
1311 if (l_universe is null or l_universe.count < 1) and l_count = 1 then
1312 l_return := fnd_concurrent.set_completion_status (status => 'WARNING',
1313 message => 'Zero objects scored. Check object filter of the Scoring engine');
1314 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Warning!!! Zero objects scored. Check object filter of the Scoring engine ');
1315 close universe_cur;
1316 return; -- bug 9570425
1317 end if;
1318
1319 FND_FILE.PUT_LINE(FND_FILE.LOG,'Scoring objects in batch ' || l_count
1320 || ' is ' || l_universe.count || ' at ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSS') );
1321 if l_universe.count = 0 then
1322 close universe_cur;
1323 exit;
1324 end if;
1325
1326 univ_size := l_universe.count;
1327 FND_FILE.PUT_LINE(FND_FILE.LOG, 'universe size ' ||univ_size);
1328 l_universe_size:=l_universe_size+univ_size;
1329
1330 --Begin Bug 8933776 30-Nov-2009 barathsr
1331 if l_count=1 then
1332 l_xml_body:= l_xml_body||l_new_line||'</COMPONENTS>';
1333 dbms_lob.writeAppend(tempResult, length(l_xml_body), l_xml_body);
1334 --End Bug 8933776 30-Nov-2009 barathsr
1335 end if;
1336
1337
1338 -- begin clchang added 10/20/04 for 11.5.11 score engine enhancement
1339 -- get the score_range_low, score_range_high, out_of_range_rule, and
1340 -- weight_required of this given score engine;
1341 iex_score_new_pvt.getScoreRange(p_score_id => p_score_id );
1342 -- if weight_required is Y, then chk weight of each comp is null or not;
1343 IF (IEX_SCORE_NEW_PVT.G_WEIGHT_REQUIRED = 'Y') Then
1344 FOR i in 1..l_components_tbl.count
1345 LOOP
1346 if (l_components_tbl(i).score_comp_weight is null) then
1347 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1348 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: no comp weight');
1349 END IF;
1350 FND_FILE.PUT_LINE(FND_FILE.LOG,
1351 'Score Comp Weight are required for Engine '|| p_score_id);
1352 FND_MESSAGE.Set_Name('IEX', 'IEX_WEIGHT_REQUIRED');
1353 FND_MSG_PUB.Add;
1354 RAISE FND_API.G_EXC_ERROR;
1355 end if;
1356 END LOOP;
1357 END IF;
1358 -- end clchang added 10/20/04 for 11.5.11 score engine enhancement
1359
1360 -- get the scores for the Universe
1361 --Begin Bug 8933776 30-Nov-2009 barathsr
1362 if l_count=1 then
1363 l_xml_body:= l_new_line||'<COMP_DETAILS>';
1364 -- l_xml_body:=l_xml_body||l_new_line||'<COMP_DET>';
1365 dbms_lob.writeAppend(tempResult, length(l_xml_body), l_xml_body);
1366 end if;
1367 --End Bug 8933776 30-Nov-2009 barathsr
1368 iex_score_new_pvt.getScores(p_score_comp_tbl => l_components_tbl,
1369 t_object_ids => l_universe,
1370 x_scores_tbl => l_scores_tbl);
1371 --l_xml_body:=l_new_line||'</COMP_DET>';
1372 end loop;
1373
1374 --Begin Bug 8933776 30-Nov-2009 barathsr
1375 l_xml_body:=l_new_line||'</COMP_DETAILS>';
1376 l_xml_body:= l_xml_body||l_new_line||'<UNIV_SIZE>'||l_universe_size||'</UNIV_SIZE>';
1377 dbms_lob.writeAppend(tempResult, length(l_xml_body), l_xml_body);
1378 --End Bug 8933776 30-Nov-2009 barathsr
1379
1380 l_bridge := l_scores_tbl(1); -- The table now contains the bridge to the next concurrent program or nothing
1381
1382
1383 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Completed Scoring ' || ' objects of type ' || l_object_type);
1384 Begin
1385 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1386 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: Finding any process to spawn...');
1387 END IF;
1388 Select NVL(cp.Concurrent_Program_Name, 'X')
1389 Into l_conc_prog_name
1390 From IEX_SCORES scr, fnd_concurrent_programs cp
1391 Where scr.concurrent_prog_name = cp.concurrent_program_name AND
1392 scr.Score_ID = p_score_id;
1393 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_SCORE: scoreObjects: Spawning ' || l_conc_prog_name);
1394
1395 --- Begin - Eun Huh - 02/15/2007 - bug 5763675/5696238 if run multiple Scoring Engine Harness program
1396 --at the same time it will pick up only the last one always
1397 --select MAX(request_id)
1398 -- into l_bridge
1399 -- from iex_del_buffers
1400 -- where PROGRAM_APPLICATION_ID = l_prog_appl
1401 -- and PROGRAM_ID = l_program
1402 -- and CREATED_BY = l_user;
1403
1404 --- End - LKKUMAR - 13-Apr-2006. Replace the SQL with MAX. --Bug5154199.
1405
1406 -- spawn proces if conc_prog_id is there
1407 if l_conc_prog_name <> 'X' then
1408 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1409 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: spawning ' || l_conc_prog_name ||
1410 ' with bridge ' || l_bridge);
1411 END IF;
1412
1413 --Start MOAC
1414 fnd_request.set_org_id(mo_global.get_current_org_id);
1415
1416 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1417 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: spawning ' || l_conc_prog_name || ' for operating unit: ' ||
1418 nvl(mo_global.get_ou_name(mo_global.get_current_org_id), 'All'));
1419 END IF;
1420 --End MOAC
1421
1422 l_submit_request_id := FND_REQUEST.SUBMIT_REQUEST(
1423 APPLICATION => 'IEX',
1424 PROGRAM => l_conc_prog_name,
1425 DESCRIPTION => 'Oracle Collections Score Engine Spawned Process for Operating Unit: '||
1426 nvl(mo_global.get_ou_name(mo_global.get_current_org_id), 'All'),
1427 START_TIME => sysdate,
1428 SUB_REQUEST => false,
1429 ARGUMENT1 => l_bridge);
1430 COMMIT;
1431
1432 -- Begin - schekuri - 6156648 - 10/Jun/2007 - Adding wait for request to try to make it wait
1433 bReturn := FND_CONCURRENT.WAIT_FOR_REQUEST(l_submit_request_id,60,0,vReturn,vReturn,vReturn,vReturn,vReturn);
1434 -- End - schekuri - 6156648 - 10/Jun/2007 - Adding wait for request to try to make it wait
1435
1436
1437 --jsanju 06/21/04
1438 --the main process should wait till the spawned process is
1439 --over.
1440 IF (l_submit_request_id IS NOT NULL AND l_submit_request_id <> 0) THEN
1441 LOOP
1442 FND_FILE.PUT_LINE(FND_FILE.LOG,
1443 'Start Time of the spawned Process ' ||
1444 l_conc_prog_name || ' =>'||to_char (sysdate, 'dd/mon/yyyy :HH:MI:SS'));
1445 l_bool := FND_CONCURRENT.wait_for_request(
1446 request_id =>l_submit_request_id,
1447 interval =>30,
1448 max_wait =>144000,
1449 phase =>uphase,
1450 status =>ustatus,
1451 dev_phase =>dphase,
1452 dev_status =>dstatus,
1453 message =>message);
1454
1455 IF dphase = 'COMPLETE'
1456 --and dstatus = 'NORMAL' --the possible
1457 --values are NORMAL/ERROR/WARNING/CANCELLED/TERMINATED
1458 THEN
1459 FND_FILE.PUT_LINE(FND_FILE.LOG,
1460 'End Time of the spawned Process ' ||
1461 l_conc_prog_name || ' =>'||to_char (sysdate, 'dd/mon/yyyy :HH:MI:SS'));
1462 EXIT;
1463 END If; --dphase
1464
1465 END LOOP;
1466 END IF; -- if l_submit
1467 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_SCORE: scoreObjects: Launched cp '
1468 || l_submit_request_id || ' successfully');
1469 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Operating Unit: ' ||
1470 nvl(mo_global.get_ou_name(mo_global.get_current_org_id), 'All')); --Added OU Name for MOAC
1471 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1472 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: process spawned '
1473 || l_submit_request_id);
1474 END IF;
1475
1476 end if; --if conc_process is not 'X'
1477 Exception
1478 WHEN NO_DATA_FOUND THEN
1479
1480 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1481 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: no process spawned');
1482 END IF;
1483 NULL;
1484 End;
1485 --
1486 -- End of API body
1487 --
1488
1489 -- Standard check for p_commit
1490 IF FND_API.to_Boolean(p_commit)
1491 THEN
1492 COMMIT WORK;
1493 END IF;
1494
1495 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1496 IEX_DEBUG_PUB.logMessage('scoreObjects: ' || 'PUB: ' || l_api_name || ' end');
1497 IEX_DEBUG_PUB.logMessage('scoreObjects: ' || 'End time:'|| TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1498 END IF;
1499
1500 FND_MSG_PUB.Count_And_Get
1501 (p_count => x_msg_count,
1502 p_data => x_msg_data);
1503 --Begin Bug 8933776 30-Nov-2009 barathsr
1504 l_xml_body:=l_new_line||'</SCOREDET>'||l_new_line;
1505 dbms_lob.writeAppend(tempResult, length(l_xml_body), l_xml_body);
1506 --End Bug 8933776 30-Nov-2009 barathsr
1507
1508 -- FND_FILE.PUT_LINE(FND_FILE.LOG, '***close score details body***');
1509
1510 EXCEPTION
1511 WHEN FND_API.G_EXC_ERROR THEN
1512 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1513 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: Expected Error ' || sqlerrm);
1514 END IF;
1515 RAISE FND_API.G_EXC_ERROR;
1516 ROLLBACK TO scoreObjects_PVT;
1517
1518 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1519 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1520 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: UnExpected Error ' || sqlerrm);
1521 END IF;
1522 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1523 ROLLBACK TO scoreObjects_PVT;
1524
1525 --START jsanju 10/19/05 for bug 3549051, pass the exception to the score_concur procedure
1526
1527 WHEN IEX_UNIVERSE_SIZE_ZERO_ERROR THEN
1528 ROLLBACK TO scoreObjects_PVT;
1529 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1530 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1531 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: iex universe size zero Error ' || x_msg_data);
1532 END IF;
1533
1534 RAISE IEX_UNIVERSE_SIZE_ZERO_ERROR;
1535
1536 WHEN OTHERS THEN
1537 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1538 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: Other Error ' || sqlerrm);
1539 END IF;
1540 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1541 ROLLBACK TO scoreObjects_PVT;
1542
1543 END scoreObjects;
1544
1545 /*
1546 || Overview: score a single object given it's ID, it's Type, and it's Scoring Engine
1547 ||
1548 || Parameter: p_score_id => scoring engine ID
1549 ||
1550 || Source Tables: IEX_SCORES, IEX_SCORE_COMPONENTS_VL, IEX_SCORE_COMP_TYPES, IEX_SCORE_COMP_DET,
1551 || IEX_OBJECT_FILTERS
1552 ||
1553 || Target Tables:
1554 ||
1555 || Creation date: 01/22/02 3:14:PM
1556 ||
1557 || Major Modifications: when who what
1558 || 01/22/02 raverma created
1559 */
1560 function scoreObject(p_commit IN VARCHAR2,
1561 P_OBJECT_ID IN NUMBER,
1562 P_OBJECT_TYPE IN VARCHAR2,
1563 P_SCORE_ID IN NUMBER) RETURN NUMBER
1564
1565 IS
1566 --vPLSQL varchar2(5000);
1567 l_universe IEX_FILTER_PUB.UNIVERSE_IDS; -- for TestUniverse / testGetScores
1568 l_components_tbl IEX_SCORE_NEW_PVT.SCORE_ENG_COMP_TBL; -- for testGetComponents / testGetScores
1569
1570 l_scores_tbl IEX_SCORE_NEW_PVT.SCORES_TBL; -- fore testGetScores
1571 l_score_id number ;
1572 l_object_type varchar2(25) ;
1573 b_valid boolean;
1574
1575 BEGIN
1576
1577 l_score_id := p_score_id;
1578 l_object_type := p_object_type;
1579
1580 b_valid := validateObjectID (p_object_id => p_object_id,
1581 p_object_type => p_object_type);
1582
1583 if not b_valid then
1584 FND_MESSAGE.Set_Name('IEX', 'IEX_INVALID_SCORING_OBJECT');
1585 FND_MSG_PUB.Add;
1586 return -1;
1587 end if;
1588
1589 b_valid := iex_score_new_pvt.checkObject_Compatibility(p_score_id => l_score_id ,
1590 p_object_type => l_object_type);
1591 if not b_Valid then
1592 FND_MESSAGE.Set_Name('IEX', 'IEX_INVALID_SCORING_ENGINE');
1593 FND_MSG_PUB.Add;
1594 return -1;
1595 end if;
1596
1597 IEX_SCORE_NEW_PVT.getCOMPONENTS(p_score_id => l_score_id,
1598 x_score_comp_tbl => l_components_tbl);
1599
1600 -- in case of singular object scoring we can ignore universe?
1601 l_universe(1) := p_object_id;
1602
1603 --- Begin - Andre Araujo - 11/02/2004 - Changed storage method, this storage desgin blows up at 414526 records - TAR 4040621.994
1604
1605 l_scores_tbl(1) := get1Score( l_components_tbl, p_object_id );
1606
1607 return l_scores_tbl(1);
1608 -- iex_score_new_pvt.getScores(p_score_comp_tbl => l_components_tbl,
1609 -- t_object_ids => l_universe,
1610 -- x_scores_tbl => l_scores_tbl);
1611 --
1612 -- if (l_scores_tbl is not null) and (l_scores_tbl.count > 0) then
1613 -- IF FND_API.to_Boolean(p_commit)
1614 -- THEN
1615 -- insert into iex_score_histories(SCORE_HISTORY_ID
1616 -- ,SCORE_OBJECT_ID
1617 -- ,SCORE_OBJECT_CODE
1618 -- ,OBJECT_VERSION_NUMBER
1619 -- ,LAST_UPDATE_DATE
1620 -- ,LAST_UPDATED_BY
1621 -- ,LAST_UPDATE_LOGIN
1622 -- ,CREATION_DATE
1623 -- ,CREATED_BY
1624 -- ,SCORE_VALUE
1625 -- ,SCORE_ID
1626 -- ,REQUEST_ID)
1627 -- values(IEX_SCORE_HISTORIES_S.nextval
1628 -- ,l_universe(1)
1629 -- ,l_object_type
1630 -- ,1
1631 -- ,sysdate
1632 -- ,FND_GLOBAL.USER_ID
1633 -- ,FND_GLOBAL.USER_ID
1634 -- ,sysdate
1635 -- ,FND_GLOBAL.USER_ID
1636 -- ,l_scores_tbl(1)
1637 -- ,p_score_id
1638 -- ,nvl(FND_GLOBAL.Conc_REQUEST_ID,0));
1639 --
1640 -- END IF;
1641 --
1642 -- return l_scores_tbl(1);
1643 -- else
1644 -- return -1;
1645 -- end if;
1646 --
1647 --- End - Andre Araujo - 11/02/2004 - Changed storage method, this storage desgin blows up at 414526 records - TAR 4040621.994
1648 --
1649 END scoreObject;
1650
1651 /* this will be called by the concurrent program to score customers
1652 */
1653 Procedure Score_Concur(ERRBUF OUT NOCOPY VARCHAR2,
1654 RETCODE OUT NOCOPY VARCHAR2,
1655 P_ORG_ID IN NUMBER, --Added for MOAC
1656 P_SCORE_ID1 IN NUMBER,
1657 P_Score_ID2 IN NUMBER,
1658 P_Score_ID3 IN NUMBER,
1659 P_Score_ID4 IN NUMBER,
1660 P_Score_ID5 IN NUMBER,
1661 p_show_output in varchar2 default null,--Added for Bug 8933776 30-Nov-2009 barathsr
1662 p_object_id in varchar2,--Added for Bug 8933776 17-Dec-2009 barathsr
1663 p_limit_rows in number)--Added for Bug 8933776 17-Dec-2009 barathsr
1664 --Added for Bug 8933776 30-Nov-2009 barathsr
1665 IS
1666
1667 l_return_status VARCHAR2(10);
1668 l_msg_data VARCHAR2(32767);
1669 l_msg_count NUMBER;
1670
1671 -- bug 6128024
1672 l_pf_name varchar2(100);
1673 l_pf_value varchar2(50) := nvl(FND_PROFILE.VALUE('XLA_MO_SECURITY_PROFILE_LEVEL'),'');
1674
1675 type score_ids is table of number index by binary_integer;
1676 l_num_score_engines score_ids;
1677
1678 -- START -jsanju 10/19/05 , set concurrent status to 'WARNING' if universe size exception occurs for bug 3549051
1679 request_status BOOLEAN;
1680 -- END -jsanju 10/19/05 , set concurrent status to 'WARNING' if universe size exception occurs for bug 3549051
1681
1682 l_xml_header clob;
1683 l_xml_header_length number;
1684 l_close_tag clob;
1685 l_org_id varchar2(100);
1686 l_api_name varchar2(100):='Score_Concur';
1687 l_score_name1 varchar2(200);
1688 l_score_name2 varchar2(200);
1689 l_score_name3 varchar2(200);
1690 l_score_name4 varchar2(200);
1691 l_score_name5 varchar2(200);
1692 --Begin Bug 8933776 30-Nov-2009 barathsr
1693 l_show_out varchar2(10);
1694 l_obj_ids varchar2(10);
1695 l_max_rows varchar2(10);
1696 --End Bug 8933776 30-Nov-2009 barathsr
1697 l_encoding VARCHAR2(100); --Added for bug 10210488 snuthala 19th Oct 10
1698 l_sysdate varchar2(1000);
1699
1700 BEGIN
1701
1702 RETCODE := 0;
1703 l_num_score_engines(1) := p_score_id1;
1704 l_num_score_engines(2) := p_score_id2;
1705 l_num_score_engines(3) := p_score_id3;
1706 l_num_score_engines(4) := p_score_id4;
1707 l_num_score_engines(5) := p_score_id5;
1708
1709 ---start moac
1710 MO_GLOBAL.INIT('IEX');
1711 if p_org_id is null then
1712 mo_global.set_policy_context('M',NULL);
1713 else
1714 mo_global.set_policy_context('S',p_org_id);
1715 end if;
1716
1717 ---end moac
1718
1719 -- IF PG_DEBUG < 10 THEN
1720 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1721 IEX_DEBUG_PUB.logMessage('Score_Concur: ' || 'IEX_SCORE: scoreConcur: Scoring Harness Accessed');
1722 IEX_DEBUG_PUB.logMessage('Score_Concur: ' || 'IEX_SCORE: scoreConcur: Start time:'|| TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1723 END IF;
1724 select TO_CHAR(sysdate,'YYYY-MM-DD')
1725 into l_sysdate
1726 from dual;
1727 -- Begin bug 6128024
1728 begin
1729 select security_profile_name into l_pf_name from per_security_profiles
1730 where security_profile_id = l_pf_value;
1731 exception
1732 when others then l_pf_name := null;
1733 end;
1734
1735 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Profile Value MO: Default Operating Unit : ' ||
1736 NVL(mo_global.get_ou_name(FND_PROFILE.VALUE('DEFAULT_ORG_ID')), ' '));
1737 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Profile Value MO: Operating Unit: ' ||
1738 NVL(mo_global.get_ou_name(FND_PROFILE.VALUE('ORG_ID')), ' '));
1739 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Profile Value MO: Security Profile: ' || l_pf_name);
1740 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1741 -- End bug 6128024
1742
1743 --Begin Bug 8933776 30-Nov-2009 barathsr
1744 begin
1745 if p_org_id is not null then
1746 select name
1747 into l_org_id
1748 from hr_operating_units
1749 where organization_id=p_org_id;
1750 else
1751 l_org_id:='All';
1752 end if;
1753
1754 FND_FILE.PUT_LINE(FND_FILE.LOG, '***start of xml hdr***');
1755
1756 --Start adding for bug 10210488 snuthala 19th Oct 10
1757 --l_xml_header := '<?xml version="1.0" encoding="UTF-8"?>';
1758 -- Instead of hard coding the value, pick the charcter set value from "ICX: Client IANA Encoding" profile.
1759 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
1760 l_xml_header := '<?xml version="1.0" encoding="'||l_encoding||'"?>';
1761 --End adding for bug 10210488 snuthala 19th Oct 10
1762
1763 l_xml_header:=l_xml_header||l_new_line||'<SCORE_REPORT>';
1764 l_xml_header:=l_xml_header||l_new_line||'<PARAMETERS>';
1765 --l_xml_header:=l_xml_header||l_new_line||'<ORG_ID>'||l_org_id||'</ORG_ID>';
1766 l_xml_header:=l_xml_header||l_new_line||'<ORG_ID>'||'<![CDATA['||l_org_id||']]>'||'</ORG_ID>'; -- bug 13629982
1767
1768 if p_score_id1 is not null then
1769 select score_name into l_score_name1 from iex_scores where score_id=p_score_id1;
1770 else
1771 l_score_name1:='NA';
1772 end if;
1773 l_xml_header:=l_xml_header||l_new_line||'<SCORE_ENGINE_1>'||format_string(l_score_name1)||'</SCORE_ENGINE_1>';
1774 if p_score_id2 is not null then
1775 select score_name into l_score_name2 from iex_scores where score_id=p_score_id2;
1776 else
1777 l_score_name2:='NA';
1778 end if;
1779 l_xml_header:=l_xml_header||l_new_line||'<SCORE_ENGINE_2>'||format_string(l_score_name2)||'</SCORE_ENGINE_2>';
1780 if p_score_id3 is not null then
1781 select score_name into l_score_name3 from iex_scores where score_id=p_score_id3;
1782 else
1783 l_score_name3:='NA';
1784 end if;
1785 l_xml_header:=l_xml_header||l_new_line||'<SCORE_ENGINE_3>'||format_string(l_score_name3)||'</SCORE_ENGINE_3>';
1786 if p_score_id4 is not null then
1787 select score_name into l_score_name4 from iex_scores where score_id=p_score_id4;
1788 else
1789 l_score_name4:='NA';
1790 end if;
1791 l_xml_header:=l_xml_header||l_new_line||'<SCORE_ENGINE_4>'||format_string(l_score_name4)||'</SCORE_ENGINE_4>';
1792 if p_score_id5 is not null then
1793 select score_name into l_score_name5 from iex_scores where score_id=p_score_id5;
1794 else
1795 l_score_name5:='NA';
1796 end if;
1797 l_xml_header:=l_xml_header||l_new_line||'<SCORE_ENGINE_5>'||format_string(l_score_name5)||'</SCORE_ENGINE_5>';
1798 /* if nvl(p_show_output,'No')='Yes' then
1799 l_show_out:='Yes';
1800 else
1801 l_show_out:='No';
1802 end if;*/
1803 if p_object_id is not null then
1804 l_xml_header:=l_xml_header||l_new_line||'<OBJ_IDS>'||p_object_id||'</OBJ_IDS>';
1805 else
1806 l_obj_ids:='NA';
1807 l_xml_header:=l_xml_header||l_new_line||'<OBJ_IDS>'||l_obj_ids||'</OBJ_IDS>';
1808 end if;
1809 if p_limit_rows is not null then
1810 l_xml_header:=l_xml_header||l_new_line||'<MAX_ROWS>'||p_limit_rows||'</MAX_ROWS>';
1811 else
1812 l_max_rows:='NA';
1813 l_xml_header:=l_xml_header||l_new_line||'<MAX_ROWS>'||l_max_rows||'</MAX_ROWS>';
1814 end if;
1815 l_xml_header:=l_xml_header||l_new_line||'<SHOW_OUTPUT>'||p_show_output||'</SHOW_OUTPUT>';
1816 l_xml_header:=l_xml_header||l_new_line||'<CURR_DATE>'||l_sysdate||'</CURR_DATE>';
1817 l_xml_header:=l_xml_header||l_new_line||'</PARAMETERS>';
1818 l_close_tag:='</SCORE_REPORT>'||l_new_line;
1819
1820 l_xml_header_length := length(l_xml_header);
1821 -- tempResult := l_xml_header;
1822 -- FND_FILE.put_line( FND_FILE.LOG,'Constructing the XML Header is success');
1823
1824 dbms_lob.createtemporary(tempResult,FALSE,DBMS_LOB.CALL);
1825 dbms_lob.open(tempResult,dbms_lob.lob_readwrite);
1826 dbms_lob.writeAppend(tempResult, length(l_xml_header), l_xml_header);
1827 FND_FILE.put_line( FND_FILE.LOG,'Constructing the XML Header is success');
1828 exception
1829 when others then
1830 FND_FILE.put_line( FND_FILE.LOG,'err in xml header-->'||sqlerrm);
1831 iex_debug_pub.LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME || '.' || l_api_name || '-'||sqlerrm);
1832 end;
1833 --End Bug 8933776 30-Nov-2009 barathsr
1834
1835 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch Size: ' || G_BATCH_SIZE);
1836 for x in 1..5 loop
1837 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Running Scoring Engine: ' || to_char(l_num_score_engines(x)));
1838 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Operating Unit: ' || nvl(mo_global.get_ou_name(mo_global.get_current_org_id), 'All')); --Added for moac
1839 if l_num_score_engines(x) is not null then
1840 BEGIN
1841 IEX_SCORE_NEW_PVT.scoreObjects(p_api_version => 1.0,
1842 p_init_msg_list => FND_API.G_TRUE,
1843 p_commit => FND_API.G_TRUE,
1844 x_return_status => l_return_status,
1845 x_msg_count => l_msg_count,
1846 x_msg_data => l_msg_data,
1847 p_score_id => l_num_score_engines(x),
1848 p_unv_obj_id => p_object_id,--Added for Bug 8933776 17-Dec-2009 barathsr
1849 p_limit_rows_val => p_limit_rows);--Added for Bug 8933776 30-Nov-2009 barathsr
1850 FND_FILE.PUT_LINE(FND_FILE.LOG,
1851 'Score Engine: ' || l_num_score_engines(x) ||
1852 ' Status: ' || l_return_status);
1853
1854
1855
1856 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1857 RAISE FND_API.G_EXC_ERROR;
1858 end if;
1859
1860
1861
1862
1863 EXCEPTION
1864
1865 -- note do not set retcode when error is expected
1866 WHEN FND_API.G_EXC_ERROR THEN
1867 RETCODE := -1;
1868 ERRBUF := l_msg_data;
1869 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1870 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreConcur: Expected Error in Score ' || sqlerrm);
1871 END IF;
1872 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR: ' || sqlerrm || ERRBUF);
1873
1874 -- START -jsanju 10/19/05 , set concurrent status to 'WARNING' if universe size exception occurs for bug 3549051
1875 WHEN IEX_UNIVERSE_SIZE_ZERO_ERROR THEN
1876 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1877 IEX_DEBUG_PUB.logMessage('IEX_SCORE: universe size is zero ' || l_msg_data);
1878 END IF;
1879 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Universe Size is Zero ');
1880 request_status := fnd_concurrent.set_completion_status('WARNING'
1881 , 'Universe size is zero');
1882
1883 -- END -jsanju 10/19/05 , set concurrent status to 'WARNING' if universe size exception occurs for bug 3549051
1884
1885 WHEN OTHERS THEN
1886 RETCODE := -1;
1887 ERRBUF := l_msg_data;
1888 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1889 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreConcur: Unexpected Error ' || sqlerrm);
1890 END IF;
1891 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR: ' || sqlerrm || ERRBUF);
1892 END;
1893
1894 end if;
1895 end loop;
1896
1897 FND_FILE.PUT_LINE(FND_FILE.LOG,'1');
1898 dbms_lob.writeAppend(tempResult, length(l_close_tag), l_close_tag);
1899 --print to the o/p file
1900 FND_FILE.PUT_LINE(FND_FILE.LOG,'2');
1901 FND_FILE.PUT_LINE(FND_FILE.LOG,'len_tempRes-->'||length(tempResult));
1902 -- FND_FILE.PUT_LINE(FND_FILE.LOG,substr(tempResult,16000,length(tempResult)));
1903 if nvl(p_show_output,'No')='Yes' then
1904 print_clob(lob_loc => tempResult);
1905 else
1906 tempResult:=l_xml_header;
1907 -- dbms_lob.writeAppend(tempResult, length(l_xml_header), l_xml_header);
1908 dbms_lob.writeAppend(tempResult, length(l_close_tag), l_close_tag);
1909 print_clob(lob_loc => tempResult);
1910 end if;
1911 -- Fnd_File.PUT_line(FND_FILE.LOG,substr(tempResult,1,length(tempResult)));
1912 FND_FILE.PUT_LINE(FND_FILE.LOG,'3');
1913
1914 -- IF PG_DEBUG < 10 THEN
1915 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1916 IEX_DEBUG_PUB.logMessage('Score_Concur: ' || 'Return status is ' || l_return_status);
1917 IEX_DEBUG_PUB.logMessage('Score_Concur: ' || 'IEX_SCORE: scoreConcur: End time:'|| TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1918 END IF;
1919 Exception
1920
1921 WHEN FND_API.G_EXC_ERROR THEN
1922 RETCODE := -1;
1923 ERRBUF := l_msg_data;
1924 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1925 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreConcur: Expected Error ' || sqlerrm);
1926 END IF;
1927 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR: ' || sqlerrm);
1928
1929 WHEN OTHERS THEN
1930 RETCODE := -1;
1931 ERRBUF := l_msg_data;
1932 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1933 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreConcur: Unexpected Error ' || sqlerrm);
1934 END IF;
1935 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR: ' || sqlerrm);
1936 END SCORE_CONCUR;
1937
1938 /*
1939 || Overview: delete rows from IEX_SCORE_HISTORIES to improve performance
1940 ||
1941 || Parameter: p_score_object_code => score_object_code to erase
1942 || p_from_date => remove from this date
1943 || p_to_Date => remove up to this date
1944 || p_request_id => remove this request
1945 || p_save_last_run => save the last run of the object type
1946 || all parameters are AND logic on the where clause
1947 ||
1948 || Source Tables:
1949 ||
1950 || Target Tables: IEX_SCORE_HISTORIES
1951 ||
1952 || Creation date: 01/28/03 3:14:PM
1953 ||
1954 || Major Modifications: when who what
1955 || 01/28/03 raverma created
1956 */
1957 Procedure eraseScores(ERRBUF OUT NOCOPY VARCHAR2,
1958 RETCODE OUT NOCOPY VARCHAR2,
1959 p_org_id IN NUMBER,
1960 P_TRUNCATE IN VARCHAR2, -- fix a bug 5765878 to truncate table to perform better by Ehuh 02.19.2007
1961 P_SCORE_OBJECT_ID IN NUMBER ,
1962 P_SCORE_OBJECT_CODE IN VARCHAR2 ,
1963 -- begin bug 4504193 by ctlee 2005/07/26 - update from date to varchar2
1964 P_FROM_DATE IN varchar2 ,
1965 P_TO_DATE IN varchar2 ,
1966 -- end bug 4504193 by ctlee 2005/07/26 - update from date to varchar2
1967 P_REQUEST_ID IN NUMBER ,
1968 P_SAVE_LAST_RUN IN VARCHAR2,
1969 P_BATCH_SIZE IN NUMBER)
1970 IS
1971
1972 vPLSQL VARCHAR2(200);
1973 vPLSQL2 VARCHAR2(500);
1974 l_total NUMBER(38) ;
1975 l_Count NUMBER ;
1976 i NUMBER ;
1977 j NUMBER ;
1978 l_object_code VARCHAR2(50);
1979 Type refCur is Ref Cursor;
1980 sql_cur refCur;
1981 l_conditions IEX_UTILITIES.Condition_Tbl;
1982 l_msg_data VARCHAR2(1000);
1983 l_score_history_ids IEX_FILTER_PUB.UNIVERSE_IDS;
1984
1985 -- clchang updated for sql bind var 05/07/2003
1986 vStr1 VARCHAR2(100) ;
1987 vStr2 VARCHAR2(100) ;
1988 vSqlCur VARCHAR2(1000) ;
1989 -- end
1990
1991 -- Modified By Surya 11/18/2003 Bug 3221769
1992 v_del_sql Varchar2(1000) ;
1993 v_tot_objects Number := 0 ;
1994
1995 -- Andre Added
1996 vWhereClause VARCHAR2(1000) ;
1997 vSelectCount Varchar2(1000) ;
1998 vLoopCount Number;
1999
2000 --clchang 10/29/04 added to fix gscc
2001 l_save_last_run varchar2(10);
2002
2003 -- begin bug 4504193 by ctlee 2005/07/26
2004 v_from_date date;
2005 v_to_date date;
2006 -- end bug 4504193 by ctlee 2005/07/26
2007
2008 l_prod varchar2(10); -- fix a bug 5765878 to truncate table to perform better by Ehuh 02.19.2007
2009 v_num NUMBER; --Added for bug 8605501 gnramasa 20th Oct 09
2010
2011 l_out_status varchar2(100);
2012 l_out_industry varchar2(100);
2013 x boolean;
2014 l_sql_stmt varchar2(1000);
2015
2016 BEGIN
2017
2018 --clchang 10/29/04 added to fix gscc
2019 -- and before P_SAVE_LAST_RUN has DEFAULT 'Y';
2020 l_save_last_run := p_save_last_run;
2021 if ( l_save_last_run is null) then
2022 l_save_last_run := 'Y';
2023 end if;
2024 -- no default values in declare
2025 l_total := 0;
2026 l_Count := 0;
2027 i := 0;
2028 j := 0;
2029 vStr1 := 'SELECT SCORE_HISTORY_ID ' ;
2030 vStr2 := ' FROM IEX_SCORE_HISTORIES ' ;
2031 v_del_sql := 'DELETE FROM IEX_SCORE_HISTORIES ' ;
2032 vWhereClause := '';
2033 vSelectCount := 'select count(1) from iex_score_histories ';
2034
2035
2036 -- IF PG_DEBUG < 10 THEN
2037 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2038 IEX_DEBUG_PUB.logMessage('IEX_SCORE: eraseScores');
2039 IEX_DEBUG_PUB.logMessage('IEX_SCORE: eraseScores: Start time:'|| TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2040 END IF;
2041
2042 /* build where clause */
2043 if P_SCORE_OBJECT_ID IS NOT NULL then
2044 FND_FILE.PUT_LINE(FND_FILE.LOG,P_SCORE_OBJECT_ID);
2045 i:= i + 1;
2046 l_conditions(i).Col_Name := 'SCORE_OBJECT_ID';
2047 l_conditions(i).Condition := '=';
2048 l_conditions(i).Value := P_SCORE_OBJECT_ID;
2049 end if;
2050 if P_SCORE_OBJECT_CODE IS NOT NULL then
2051 FND_FILE.PUT_LINE(FND_FILE.LOG,P_SCORE_OBJECT_CODE);
2052 i := i + 1;
2053 l_conditions(i).Col_Name := 'SCORE_OBJECT_CODE';
2054 l_conditions(i).Condition := '=';
2055 l_conditions(i).Value := '''' || P_SCORE_OBJECT_CODE || '''';
2056 end if;
2057 if P_FROM_DATE IS NOT NULL then
2058 FND_FILE.PUT_LINE(FND_FILE.LOG,TO_CHAR(P_FROM_DATE));
2059 i := i + 1;
2060 l_conditions(i).Col_Name := 'CREATION_DATE';
2061 l_conditions(i).Condition := '>';
2062 -- l_conditions(i).Value := '''' || to_char(P_FROM_DATE) || '''';
2063 -- begin bug 4504193 by ctlee 2005/07/26
2064 v_from_date := to_date(p_from_date, 'yyyy/mm/dd hh24:mi:ss');
2065 l_conditions(i).Value := '''' || to_char(v_FROM_DATE) || '''';
2066 -- end bug 4504193 by ctlee 2005/07/26
2067 end if;
2068 if P_TO_DATE IS NOT NULL then
2069 FND_FILE.PUT_LINE(FND_FILE.LOG,TO_CHAR(P_TO_DATE));
2070 i := i + 1;
2071 l_conditions(i).Col_Name := 'CREATION_DATE';
2072 l_conditions(i).Condition := '<=';
2073 -- l_conditions(i).Value := '''' || to_char(P_TO_DATE) || '''';
2074 -- begin bug 4504193 by ctlee 2005/07/26
2075 v_to_date := to_date(p_to_date, 'yyyy/mm/dd hh24:mi:ss');
2076 l_conditions(i).Value := '''' || to_char(v_TO_DATE) || '''';
2077 -- end bug 4504193 by ctlee 2005/07/26
2078 end if;
2079 if P_REQUEST_ID IS NOT NULL then
2080 FND_FILE.PUT_LINE(FND_FILE.LOG,P_REQUEST_ID);
2081 i := i + 1;
2082 l_conditions(i).Col_Name := 'REQUEST_ID';
2083 l_conditions(i).Condition := '=';
2084 l_conditions(i).Value := P_REQUEST_ID;
2085 end if;
2086 --if P_SAVE_LAST_RUN <> 'N' then
2087 if L_SAVE_LAST_RUN <> 'N' then
2088 FND_FILE.PUT_LINE(FND_FILE.LOG,L_SAVE_LAST_RUN);
2089 -- Begin - Andre Araujo - 03/02/2005 - BUG#4198055 - Did not increase the count, causes not found exception
2090 i := i + 1;
2091 l_conditions(i).Col_Name := 'trunc(CREATION_DATE)';
2092 l_conditions(i).Condition := '<>';
2093 l_conditions(i).Value := '(SELECT trunc(MAX(creation_date)) FROM iex_Score_histories)'; -- Andre Fixed here so we use date only
2094 --l_conditions(i).Col_Name := 'CREATION_DATE';
2095 --l_conditions(i).Condition := '<>';
2096 --l_conditions(i).Value := '(SELECT MAX(creation_date) FROM iex_Score_histories)';
2097 -- End - Andre Araujo - 03/02/2005 - BUG#4198055 - Did not increase the count, causes not found exception
2098 end if;
2099 -- added for bug 13335079 pnaveenk
2100
2101 if P_ORG_ID IS NOT NULL then
2102 FND_FILE.PUT_LINE(FND_FILE.LOG,P_ORG_ID);
2103 i := i + 1;
2104 l_conditions(i).Col_Name := 'ORG_ID';
2105 l_conditions(i).Condition := '=';
2106 l_conditions(i).Value := P_ORG_ID;
2107 end if;
2108 -- end for bug 13335079
2109 -- Added by Surya
2110 if l_conditions.COUNT >= 1 then
2111
2112 vPLSQL2 := IEX_UTILITIES.buildWhereClause(l_conditions);
2113
2114 If NVL(p_truncate,'Y') = 'N' then -- fix a bug 5765878 to truncate table to perform better by Ehuh 2.19.2007
2115 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Delete Filter Applied => '|| vPLSQL2);
2116 end if;
2117
2118 v_del_sql := v_del_sql || VPLSQL2 ;
2119 End If ;
2120
2121 -- Andre Added
2122 vWhereClause := VPLSQL2;
2123
2124 -- IF PG_DEBUG < 10 THEN
2125 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2126 IEX_DEBUG_PUB.logMessage(vPLSQL2);
2127 END IF;
2128
2129 If NVL(p_truncate,'Y') = 'N' then -- fix a bug 5765878 to truncate table to perform better by Ehuh 2.19.2007
2130 FND_FILE.PUT_LINE(FND_FILE.LOG, 'SELECT SCORE_HISTORY_ID ' ||
2131 ' FROM IEX_SCORE_HISTORIES ' ||
2132 vPLSQL2);
2133 End if;
2134
2135 vPLSQL := ' SELECT Count(1), Score_object_code ' ||
2136 ' FROM IEX_SCORE_HISTORIES ' ||
2137 'GROUP BY SCORE_OBJECT_CODE ';
2138
2139 FND_FILE.PUT_LINE(FND_FILE.LOG, '-------------------------------------------------');
2140 FND_FILE.PUT_LINE(FND_FILE.LOG, ' BEFORE PURGE');
2141 FND_FILE.PUT_LINE(FND_FILE.LOG, '-------------------------------------------------');
2142 open sql_cur for
2143 vPLSQL;
2144 LOOP
2145 l_count := 0;
2146 l_object_code := null;
2147 i := i + 1;
2148 fetch sql_cur into l_count, l_object_code;
2149 exit when sql_cur%NOTFOUND;
2150 FND_FILE.PUT_LINE(FND_FILE.LOG, 'OBJECT_CODE: ' || l_object_code || ' OBJECTS: ' || l_count);
2151 v_tot_objects := v_tot_objects + l_count ;
2152 end loop;
2153 close sql_cur;
2154
2155 FND_FILE.PUT_LINE(FND_FILE.LOG, 'TOTAL OBJECTS IN IEX_SCORE_HISTORIES BEFORE PURGE: ' || v_tot_objects);
2156
2157 /* do erasing here */
2158 /* Removed by Andre 06/18/2004, we will need to delete in chunks
2159 EXECUTE IMMEDIATE v_del_sql ;
2160 */
2161 -- added for bug 13335079
2162 If NVL(p_truncate,'Y') <> 'N' THEN -- fix a bug 5765878 to truncate table to perform better by Ehuh 2.19.2007
2163
2164 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Truncating Table => IEX_SCORE_HISTORIES');
2165
2166 --v_del_sql := 'truncate table '||l_prod||'.IEX_SCORE_HISTORIES'; -- fix a bug 5765878 to truncate table to perform better by Ehuh 2.19.2007
2167 --EXECUTE IMMEDIATE v_del_sql;
2168
2169
2170 x := fnd_installation.get_app_info ('FND',l_out_status,l_out_industry,l_prod);
2171
2172 IEX_DEBUG_PUB.logMessage('Schema:'||l_prod);
2173 IEX_DEBUG_PUB.LogMessage('Truncating IEX_SCORE_HISTORIES');
2174
2175
2176 ad_ddl.do_ddl( l_prod,
2177 'IEX',
2178 AD_DDL.TRUNCATE_TABLE,
2179 'TRUNCATE TABLE IEX_SCORE_HISTORIES',
2180 'IEX_SCORE_HISTORIES' );
2181
2182 IEX_DEBUG_PUB.LogMessage('Truncated IEX_SCORE_HISTORIES');
2183
2184 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Truncated IEX_SCORE_HISTORIES');
2185 --Start bug 8605501 gnramasa 20th Oct 09
2186 BEGIN
2187 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Table IEX_SCORE_HISTORIES has been truncated, so will reset the Sequence '||
2188 'IEX_SCORE_HISTORIES_S value to 10000');
2189
2190 -- EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_prod||'.IEX_SCORE_HISTORIES_S INCREMENT BY -1';
2191 l_sql_stmt := 'ALTER SEQUENCE IEX_SCORE_HISTORIES_S INCREMENT BY -1' ;
2192 ad_ddl.do_ddl( l_prod, 'IEX', ad_ddl.alter_sequence, l_sql_stmt,'IEX_SCORE_HISTORIES_S');
2193
2194 EXECUTE IMMEDIATE 'select IEX_SCORE_HISTORIES_S.NEXTVAL +1 FROM DUAL' into v_num;
2195 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Before altering Sequence IEX_SCORE_HISTORIES_S value is: '|| v_num);
2196
2197 if v_num <> 10000 then
2198 --EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_prod||'.IEX_SCORE_HISTORIES_S INCREMENT BY '|| ((v_num -10000)* -1);
2199 l_sql_stmt := 'ALTER SEQUENCE IEX_SCORE_HISTORIES_S INCREMENT BY ' || ((v_num -10000)* -1);
2200 ad_ddl.do_ddl( l_prod, 'IEX', ad_ddl.alter_sequence, l_sql_stmt,'IEX_SCORE_HISTORIES_S');
2201 EXECUTE IMMEDIATE 'select IEX_SCORE_HISTORIES_S.NEXTVAL FROM DUAL' into v_num;
2202 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Sequence IEX_SCORE_HISTORIES_S value is: 10000');
2203 else
2204 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Sequence IEX_SCORE_HISTORIES_S value is already 10000, so no need to change it again');
2205 end if;
2206 --EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_prod||'.IEX_SCORE_HISTORIES_S INCREMENT BY 1';
2207 l_sql_stmt := 'ALTER SEQUENCE IEX_SCORE_HISTORIES_S INCREMENT BY 1';
2208 ad_ddl.do_ddl( l_prod, 'IEX', ad_ddl.alter_sequence, l_sql_stmt,'IEX_SCORE_HISTORIES_S');
2209 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Sequence IEX_SCORE_HISTORIES_S value is set to 10000');
2210 END;
2211 --End bug 8605501 gnramasa 20th Oct 09
2212
2213 Else -- fix a bug 5765878 to truncate table to perform better by Ehuh 2.19.2007
2214
2215 if l_conditions.COUNT >= 1 then
2216 v_del_sql := v_del_sql || ' AND rownum >= 0 and rownum < ' || p_batch_size;
2217 else
2218 v_del_sql := v_del_sql || ' WHERE rownum >= 0 and rownum < ' || p_batch_size;
2219 end if;
2220
2221
2222 i := 0;
2223 vSelectCount := vSelectCount || vWhereClause;
2224 open sql_cur for vSelectCount;
2225 fetch sql_cur into vLoopCount;
2226 close sql_cur;
2227
2228 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Final delete statement => '|| v_del_sql);
2229 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleting => '|| vLoopCount || ' Records');
2230
2231 loop
2232 EXECUTE IMMEDIATE v_del_sql;
2233 FND_FILE.PUT_LINE(FND_FILE.LOG, 'i => '|| i );
2234 commit;
2235
2236 i := i + p_batch_size;
2237 exit when i > vLoopCount;
2238 end loop;
2239 -- If we miss any because of the loop count...
2240 EXECUTE IMMEDIATE v_del_sql;
2241 commit;
2242
2243 End if; -- fix a bug 5765878 to truncate table to perform better by Ehuh 2.19.2007
2244
2245 -- End changes, Andre 06/18/2004
2246
2247 FND_FILE.PUT_LINE(FND_FILE.LOG, '-------------------------------------------------');
2248 FND_FILE.PUT_LINE(FND_FILE.LOG, ' AFTER PURGE');
2249 FND_FILE.PUT_LINE(FND_FILE.LOG, '-------------------------------------------------');
2250
2251 l_total := 0;
2252 open sql_cur for
2253 vPLSQL;
2254 LOOP
2255 l_count := 0;
2256 l_object_code := null;
2257 i := i + 1;
2258 fetch sql_cur into l_count, l_object_code;
2259 exit when sql_cur%NOTFOUND;
2260 FND_FILE.PUT_LINE(FND_FILE.LOG, 'OBJECT_CODE: ' || l_object_code || ' OBJECTS: ' || l_count);
2261 end loop;
2262 close sql_cur;
2263
2264 Begin
2265 Select Count(1) into l_total
2266 From IEX_SCORE_HISTORIES;
2267 Exception When NO_DATA_FOUND Then
2268 l_total := 0;
2269 END;
2270 FND_FILE.PUT_LINE(FND_FILE.LOG, 'TOTAL OBJECTS IN IEX_SCORE_HISTORIES AFTER PURGE: ' || l_total);
2271
2272 -- IF PG_DEBUG < 10 THEN
2273 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2274 IEX_DEBUG_PUB.logMessage('IEX_SCORE: eraseScores: end time:'|| TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2275 END IF;
2276 Exception
2277 WHEN FND_API.G_EXC_ERROR THEN
2278 RETCODE := -1;
2279 ERRBUF := l_msg_data;
2280 -- IF PG_DEBUG < 10 THEN
2281 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2282 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreConcur: Expected Error ' || sqlerrm );
2283 END IF;
2284 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR: ' || sqlerrm);
2285
2286 WHEN OTHERS THEN
2287 RETCODE := -1;
2288 ERRBUF := l_msg_data;
2289 -- IF PG_DEBUG < 10 THEN
2290 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2291 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreConcur: Unexpected Error ' || sqlerrm);
2292 END IF;
2293 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR: ' || sqlerrm);
2294 --dbms_output.put_line(sqlerrm);
2295
2296 END eraseScores;
2297
2298 /*
2299 || Overview: Returns an array of score values for a given objectID/Type
2300 ||
2301 || Parameter: p_object_id object scored in IEX_SCORE_HISTORIES required
2302 || p_object_code object_code in IEX_SCORE_HISTORIES required
2303 || p_from_Date begin date restriction optional
2304 || p_to_date end date restriction optional
2305 || p_scoreID scoreEngineID used to score object optional
2306 ||
2307 || Return value: SCORE_HISTORY_ID -> PK to IEX_SCORE_HISTORIES
2308 || SCORE_ID -> scoreEngine used to calculate score
2309 || SCORE_VALUE -> score of object
2310 || CREATION_DATE -> date object was scored
2311 ||
2312 || Source Tables: IEX_SCORE_HISTORIES
2313 ||
2314 || Target Tables: NA
2315 ||
2316 || Creation date: 04/22/2003 4:03PM
2317 ||
2318 || Major Modifications: when who what
2319 || 04/22/2003 4:03PM raverma created
2320 */
2321 function getScoreHistory (p_score_object_id IN NUMBER,
2322 p_score_object_code IN VARCHAR2,
2323 p_from_date IN DATE ,
2324 p_to_date IN DATE ,
2325 p_score_id IN NUMBER ) return IEX_SCORE_NEW_PVT.SCORE_HISTORY_TBL
2326 IS
2327
2328 l_score_hist_tbl IEX_SCORE_NEW_PVT.SCORE_HISTORY_TBL;
2329 vPLSQL VARCHAR2(200);
2330 l_total NUMBER(38) ;
2331 i NUMBER ;
2332 j NUMBER ;
2333 l_object_code VARCHAR2(50);
2334 Type refCur is Ref Cursor;
2335 sql_cur refCur;
2336 l_conditions IEX_UTILITIES.Condition_Tbl;
2337
2338 --clchang updated for sql bind var 05/07/2003
2339 vstr1 varchar2(100) ;
2340 vstr2 varchar2(100) ;
2341 vstr3 varchar2(100) ;
2342 vstr4 varchar2(100) ;
2343 vstr5 varchar2(100) ;
2344 vSqlCur varchar2(1000);
2345
2346 BEGIN
2347 -- IF PG_DEBUG < 10 THEN
2348 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2349 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreHistory: Start time:'|| TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2350 END IF;
2351
2352 --clchang updated 10/29/04 no default values in declare
2353 l_total := 0;
2354 i := 0;
2355 j := 0;
2356 vstr1 := 'SELECT SCORE_HISTORY_ID, ';
2357 vstr2 := ' SCORE_ID, ';
2358 vstr3 := ' SCORE_VALUE, ';
2359 vstr4 := ' CREATION_DATE ';
2360 vstr5 := ' FROM IEX_SCORE_HISTORIES ';
2361
2362 /* build where clause */
2363 i:= i + 1;
2364 l_conditions(i).Col_Name := 'SCORE_OBJECT_ID';
2365 l_conditions(i).Condition := '=';
2366 l_conditions(i).Value := P_SCORE_OBJECT_ID;
2367
2368 i := i + 1;
2369 l_conditions(i).Col_Name := 'SCORE_OBJECT_CODE';
2370 l_conditions(i).Condition := '=';
2371 l_conditions(i).Value := '''' || P_SCORE_OBJECT_CODE || '''';
2372
2373 if P_FROM_DATE IS NOT NULL then
2374 i := i + 1;
2375 l_conditions(i).Col_Name := 'CREATION_DATE';
2376 l_conditions(i).Condition := '>';
2377 l_conditions(i).Value := '''' || to_char(P_FROM_DATE) || '''';
2378 end if;
2379 if P_TO_DATE IS NOT NULL then
2380 i := i + 1;
2381 l_conditions(i).Col_Name := 'CREATION_DATE';
2382 l_conditions(i).Condition := '<=';
2383 l_conditions(i).Value := '''' || to_char(P_TO_DATE) || '''';
2384 end if;
2385 if P_SCORE_ID IS NOT NULL then
2386 i := i + 1;
2387 l_conditions(i).Col_Name := 'SCORE_ID';
2388 l_conditions(i).Condition := '=';
2389 l_conditions(i).Value := P_SCORE_ID;
2390 end if;
2391 vPLSQL := IEX_UTILITIES.buildWhereClause(l_conditions);
2392
2393 -- IF PG_DEBUG < 10 THEN
2394 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2395 IEX_DEBUG_PUB.logMessage(vPLSQL);
2396 END IF;
2397 --dbms_output.put_line(vPLSQL);
2398
2399 /* execute history query and fetch */
2400 -- clchang updated for sql bind var 05/07/2003
2401 vSqlCur := vstr1 || vstr2 || vstr3 || vstr4 || vstr5 || vPLSQL;
2402 open sql_cur for vSqlCur;
2403 /*
2404 open sql_cur for
2405 'SELECT SCORE_HISTORY_ID, ' ||
2406 ' SCORE_ID, ' ||
2407 ' SCORE_VALUE, '||
2408 ' CREATION_DATE ' ||
2409 ' FROM IEX_SCORE_HISTORIES ' ||
2410 vPLSQL;
2411 */
2412
2413 LOOP
2414 j := j + 1;
2415 fetch sql_cur into l_score_hist_tbl(j).Score_history_id,
2416 l_score_hist_tbl(j).Score_id,
2417 l_score_hist_tbl(j).score_value,
2418 l_score_hist_tbl(j).creation_date;
2419 exit when sql_cur%NOTFOUND;
2420 end loop;
2421 close sql_cur;
2422
2423 return l_score_hist_tbl;
2424
2425 -- IF PG_DEBUG < 10 THEN
2426 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2427 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreHistory: end time:'|| TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2428 END IF;
2429
2430 Exception
2431 WHEN FND_API.G_EXC_ERROR THEN
2432 -- IF PG_DEBUG < 10 THEN
2433 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2434 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreHistory: Expected Error ' || sqlerrm );
2435 END IF;
2436 RETURN l_score_hist_tbl;
2437
2438 WHEN OTHERS THEN
2439 -- IF PG_DEBUG < 10 THEN
2440 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2441 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreHistory: UnExpected Error ' || sqlerrm );
2442 END IF;
2443 RETURN l_score_hist_tbl;
2444
2445 END getScoreHistory;
2446 --
2447 --- Begin - Andre Araujo - 11/02/2004 - New storage mode, this one respects the commit size - TAR 4040621.994
2448 --
2449 /*
2450 || Overview: Stores the score history given a table of records
2451 ||
2452 || Parameter:
2453 || p_scoreID scoreEngineID used to score object optional
2454 ||
2455 || Return value:
2456 ||
2457 || Source Tables: None
2458 ||
2459 || Target Tables: IEX_SCORE_HISTORIES
2460 ||
2461 || Creation date: 11/02/2004
2462 ||
2463 || Major Modifications: when who what
2464 ||
2465 */
2466 procedure storeScoreHistory ( p_score_id IN NUMBER default null,
2467 p_objects_tbl IN IEX_SCORE_NEW_PVT.SCORE_OBJECTS_TBL,
2468 p_scores_tbl IN IEX_SCORE_NEW_PVT.NEW_SCORES_TBL)
2469 IS
2470
2471 i NUMBER := 1;
2472 n NUMBER := 1;
2473
2474 l_user NUMBER;
2475 l_program NUMBER;
2476 l_prog_appl NUMBER;
2477 l_request NUMBER;
2478 l_object_type VARCHAR2(25);
2479 l_org_id NUMBER; -- added for bug 13335079 pnaveenk
2480
2481 BEGIN
2482 IF PG_DEBUG < 10 THEN
2483 IEX_DEBUG_PUB.logMessage('IEX_SCORE: storeScoreHistory: Insert records!' );
2484 END IF;
2485
2486 l_user := FND_GLOBAL.USER_ID;
2487 l_request := nvl(FND_GLOBAL.Conc_REQUEST_ID,0);
2488 l_program := FND_GLOBAL.CONC_PROGRAM_ID;
2489 l_prog_appl := FND_GLOBAL.PROG_APPL_ID;
2490
2491 -- start fro bug 13335079 pnaveenk added org_id to iex_score_histories
2492 l_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID;
2493
2494 IF p_scores_tbl.count > 0 THEN -- Do we have records to store?
2495 IF PG_DEBUG < 10 THEN
2496 IEX_DEBUG_PUB.logMessage('IEX_SCORE: storeScoreHistory: p_score_id= ' || p_score_id ||
2497 ' ; Number of scores= ' || p_scores_tbl.count);
2498 END IF;
2499
2500 -- initial variables needed
2501 select jtf_object_code into l_object_type
2502 from iex_scores
2503 where score_id = p_score_id;
2504
2505 FORALL n in i..i + p_scores_tbl.count - 1
2506 insert into iex_score_histories(SCORE_HISTORY_ID
2507 ,SCORE_OBJECT_ID
2508 ,SCORE_OBJECT_CODE
2509 ,OBJECT_VERSION_NUMBER
2510 ,LAST_UPDATE_DATE
2511 ,LAST_UPDATED_BY
2512 ,LAST_UPDATE_LOGIN
2513 ,CREATION_DATE
2514 ,CREATED_BY
2515 ,SCORE_VALUE
2516 ,SCORE_ID
2517 ,REQUEST_ID
2518 ,PROGRAM_ID
2519 ,PROGRAM_APPLICATION_ID
2520 ,PROGRAM_UPDATE_DATE
2521 ,ORG_ID)
2522 values(IEX_SCORE_HISTORIES_S.nextval
2523 ,p_objects_tbl(n)
2524 ,l_object_type
2525 ,1
2526 ,sysdate
2527 ,l_user
2528 ,l_user
2529 ,sysdate
2530 ,l_user
2531 ,p_scores_tbl(n)
2532 ,p_score_id
2533 ,l_request
2534 ,l_program
2535 ,l_prog_appl
2536 ,SYSDATE
2537 ,l_org_id);
2538
2539 IF PG_DEBUG < 10 THEN
2540 IEX_DEBUG_PUB.logMessage('IEX_SCORE: storeScoreHistory: Commit records!' );
2541 END IF;
2542
2543 commit;
2544 END IF; -- p_scores_tbl.count > 0
2545 -- end for bug 13335079
2546 IF PG_DEBUG < 10 THEN
2547 IEX_DEBUG_PUB.logMessage('IEX_SCORE: storeScoreHistory: Return' );
2548 END IF;
2549
2550 Exception
2551 WHEN FND_API.G_EXC_ERROR THEN
2552 IF PG_DEBUG < 10 THEN
2553 IEX_DEBUG_PUB.logMessage('IEX_SCORE: storeScoreHistory: Expected Error ' || sqlerrm );
2554 END IF;
2555 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR: IEX_SCORE: storeScoreHistory:' || sqlerrm);
2556
2557 WHEN OTHERS THEN
2558 IF PG_DEBUG < 10 THEN
2559 IEX_DEBUG_PUB.logMessage('IEX_SCORE: storeScoreHistory: UnExpected Error ' || sqlerrm );
2560 END IF;
2561 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR: IEX_SCORE: storeScoreHistory: UnExpected Error' || sqlerrm);
2562
2563 END storeScoreHistory;
2564
2565 /*
2566 || Overview: Stores the score history given a table of records
2567 ||
2568 || Parameter:
2569 || p_scoreID scoreEngineID used to score object optional
2570 ||
2571 || Return value:
2572 ||
2573 || Source Tables: None
2574 ||
2575 || Target Tables: IEX_SCORE_HISTORIES
2576 ||
2577 || Creation date: 11/02/2004
2578 ||
2579 || Major Modifications: when who what
2580 ||
2581 */
2582 procedure storeDelBuffers ( p_score_id IN NUMBER default null,
2583 p_objects_tbl IN IEX_SCORE_NEW_PVT.SCORE_OBJECTS_TBL,
2584 p_scores_tbl IN IEX_SCORE_NEW_PVT.NEW_SCORES_TBL,
2585 p_bridge IN NUMBER default null)
2586 IS
2587 i NUMBER := 1;
2588 n NUMBER := 1;
2589
2590 l_user NUMBER;
2591 l_program NUMBER;
2592 l_prog_appl NUMBER;
2593 l_request NUMBER;
2594 l_object_type VARCHAR2(25);
2595 l_bridge NUMBER ;
2596
2597
2598
2599 BEGIN
2600 IF PG_DEBUG < 10 THEN
2601 IEX_DEBUG_PUB.logMessage('IEX_SCORE: storeDelBuffers: Insert records!' );
2602 END IF;
2603
2604 l_bridge := p_bridge;
2605 l_user := FND_GLOBAL.USER_ID;
2606 l_request := nvl(FND_GLOBAL.Conc_REQUEST_ID,0);
2607 l_program := FND_GLOBAL.CONC_PROGRAM_ID;
2608 l_prog_appl := FND_GLOBAL.PROG_APPL_ID;
2609
2610 IF p_scores_tbl.count > 0 THEN -- Do we have records to store?
2611 IF PG_DEBUG < 10 THEN
2612 IEX_DEBUG_PUB.logMessage('IEX_SCORE: storeDelBuffers: p_score_id= ' || p_score_id ||
2613 ' ; Number of scores= ' || p_scores_tbl.count);
2614 END IF;
2615
2616 -- initial variables needed
2617 select jtf_object_code into l_object_type
2618 from iex_scores
2619 where score_id = p_score_id;
2620
2621
2622 FORALL n in i..i + p_scores_tbl.count - 1
2623 insert into IEX_DEL_BUFFERS(DEL_BUFFER_ID
2624 ,SCORE_OBJECT_ID
2625 ,SCORE_OBJECT_CODE
2626 ,OBJECT_VERSION_NUMBER
2627 ,LAST_UPDATE_DATE
2628 ,LAST_UPDATED_BY
2629 ,LAST_UPDATE_LOGIN
2630 ,CREATION_DATE
2631 ,CREATED_BY
2632 ,SCORE_VALUE
2633 ,SCORE_ID
2634 ,REQUEST_ID
2635 ,PROGRAM_ID
2636 ,PROGRAM_APPLICATION_ID
2637 ,PROGRAM_UPDATE_DATE)
2638 values(IEX_DEL_BUFFERS_S.nextval
2639 ,p_objects_tbl(n)
2640 ,l_object_type
2641 ,1
2642 ,sysdate
2643 ,l_user
2644 ,l_user
2645 ,sysdate
2646 ,l_user
2647 ,p_scores_tbl(n)
2648 ,p_score_id
2649 ,nvl(l_bridge,0)
2650 ,l_program
2651 ,l_prog_appl
2652 ,sysdate);
2653
2654
2655 IF PG_DEBUG < 10 THEN
2656 IEX_DEBUG_PUB.logMessage('IEX_SCORE: storeDelBuffers: Commit records!' );
2657 END IF;
2658
2659 commit;
2660 END IF; -- p_scores_tbl.count > 0
2661
2662 IF PG_DEBUG < 10 THEN
2663 IEX_DEBUG_PUB.logMessage('IEX_SCORE: storeDelBuffers: Return' );
2664 END IF;
2665
2666 Exception
2667 WHEN FND_API.G_EXC_ERROR THEN
2668 IF PG_DEBUG < 10 THEN
2669 IEX_DEBUG_PUB.logMessage('IEX_SCORE: storeDelBuffers: Expected Error ' || sqlerrm );
2670 END IF;
2671 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR:IEX_SCORE: storeDelBuffers: Expected Error ' || sqlerrm);
2672
2673 WHEN OTHERS THEN
2674 IF PG_DEBUG < 10 THEN
2675 IEX_DEBUG_PUB.logMessage('IEX_SCORE: storeDelBuffers: UnExpected Error ' || sqlerrm );
2676 END IF;
2677 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR:IEX_SCORE: storeDelBuffers: UnExpected Error ' || sqlerrm);
2678
2679 END storeDelBuffers;
2680
2681
2682 /*
2683 || Overview: Scores 1 item and returns the value
2684 ||
2685 || Parameter:
2686 || p_scoreID scoreEngineID used to score object optional
2687 ||
2688 || Return value:
2689 ||
2690 || Source Tables: None
2691 ||
2692 || Target Tables: None
2693 ||
2694 || Creation date: 11/03/2004
2695 ||
2696 || Major Modifications: when who what
2697 ||
2698 */
2699 function get1Score ( p_score_comp_tbl IN IEX_SCORE_NEW_PVT.SCORE_ENG_COMP_TBL, p_object_id IN NUMBER ) return NUMBER
2700 IS
2701
2702 l_score_component_id NUMBER;
2703 l_score_component_sql VARCHAR2(2500);
2704 l_execute_style VARCHAR2(1); -- are we using select or function call
2705 l_count2 number := 0;
2706 l_component_score number := 0;
2707 type COMPONENT_RANGE is table of NUMBER
2708 index by binary_integer;
2709 l_component_range_tbl COMPONENT_RANGE;
2710 i NUMBER;
2711 l_raw_score number := 0;
2712 l_running_score number := 0;
2713 vSql varchar2(2500);
2714 l_value VARCHAR2(2000);
2715 l_new_value VARCHAR2(2000);
2716 l_weight_required VARCHAR2(3);
2717 l_low varchar2(2000);
2718 l_high varchar2(2000);
2719 l_rule varchar2(20);
2720 --Begin Bug 8933776 30-Nov-2009 barathsr
2721 l_xml_body_1 varchar2(8000);
2722 -- l_new_line varchar2(1);
2723 l_jtf_obj_code varchar2(50);
2724 l_party_name varchar2(360);
2725 l_object_id number;
2726 l_object_name varchar2(30);
2727 l_wtg_com_score number:=0;
2728 l_score_comp_name varchar2(300);
2729 l_score_comp_wtg number;
2730 --End Bug 8933776 30-Nov-2009 barathsr
2731
2732
2733 BEGIN
2734 IF PG_DEBUG < 10 THEN
2735 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: Begin' );
2736 END IF;
2737
2738 -- l_xml_body_1:=l_new_line||'<COMP_DET>';
2739
2740 -- copied code
2741 /* 3. for each component, execute SQL and get value */
2742 FOR l_count2 IN 1..p_score_comp_tbl.count LOOP
2743 l_score_component_id := p_score_comp_tbl(l_count2).score_component_id;
2744 l_score_component_sql := p_score_comp_tbl(l_count2).SCORE_COMP_VALUE;
2745 l_execute_style := p_score_comp_tbl(l_count2).function_flag;
2746 -- initialize this to the minimum for any given component
2747 --l_raw_score := IEX_SCORE_PVT.G_MIN_SCORE;
2748 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'score_comp_cnt-->'||p_score_comp_tbl.count);
2749 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'score_comp_val-->'|| p_score_comp_tbl(l_count2).SCORE_COMP_VALUE);
2750 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'score_comp_id-->'|| p_score_comp_tbl(l_count2).score_component_id);
2751
2752
2753 if PG_DEBUG <= 5 then
2754 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: executing Component ' || l_count2 ||
2755 ' CompID is: ' || l_score_component_id);
2756 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: Execute Stmt: ' || l_score_component_sql ||
2757 ' Execute Style: ' || l_execute_style);
2758 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: Bind Variable: ' || p_object_id);
2759 end if;
2760
2761 /* executing dynamic sql for component */
2762 --if l_score_component_sql is not null then
2763 BEGIN
2764
2765 -- Execute SQL statement only when function syntax is not found
2766 if l_execute_style = 'N' then
2767 -- simple select statement
2768 EXECUTE IMMEDIATE l_score_component_sql
2769 INTO l_component_score
2770 USING p_object_id;
2771 else
2772 -- function to execute
2773 -- to do - pass the score component id for Function calls only
2774 EXECUTE IMMEDIATE l_score_component_sql
2775 USING in p_object_id,
2776 in l_score_component_id,
2777 out l_component_score;
2778 end if;
2779
2780 EXCEPTION
2781
2782 -- assign the "Lowest" Detail for the component
2783 -- in order to do this we must know what is "high" and "low" range of component
2784
2785 WHEN OTHERS THEN
2786 -- Begin - Andre Araujo - 12/17/2004 - If the detail is not defined this throws a NO DATA FOUND
2787 Begin
2788 -- End - Andre Araujo - 12/17/2004 - If the detail is not defined this throws a NO DATA FOUND
2789 -- figure out whether the component details are better higher or worse higher
2790 IF PG_DEBUG <= 5 THEN
2791 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: Failed to calculate for component ' || l_score_component_id );
2792 IEX_DEBUG_PUB.logMessage('Reason: ' || sqlerrm);
2793 END IF;
2794
2795 SELECT Range_Low
2796 BULK COLLECT INTO l_component_range_tbl
2797 FROM iex_score_comp_det
2798 where score_component_id = l_score_component_id
2799 order by value;
2800
2801 IF PG_DEBUG <= 5 THEN
2802 IEX_DEBUG_PUB.logMessage('Comparing Ranges');
2803 END IF;
2804
2805 if l_component_range_tbl(1) < l_component_range_tbl(2) then
2806 -- assign first comnponent detail row range to value
2807 l_component_score := l_component_range_tbl(1);
2808 else
2809 -- assign last comnponent detail row range to value
2810 i := l_component_range_tbl.count;
2811 l_component_score := l_component_range_tbl(i);
2812 end if;
2813 l_component_range_tbl.delete;
2814 -- Begin - Andre Araujo - 12/17/2004 - If the detail is not defined this throws a NO DATA FOUND
2815 EXCEPTION
2816 WHEN OTHERS THEN -- This will capture the exception from the component detail
2817 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_SCORE: get1Score: Exception selecting '||
2818 'component detail range: WRONG ENGINE CONFIGURATION!!!!!');
2819 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_SCORE: get1Score: Score will be 1 - Execution will continue.');
2820 l_component_score := 1;
2821 END;
2822 -- End - Andre Araujo - 12/17/2004 - If the detail is not defined this throws a NO DATA FOUND
2823 END; -- end for exception
2824
2825 if PG_DEBUG <= 5 then
2826 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: Successfully calculated component score: ' || l_component_score);
2827 end if;
2828
2829
2830 /* exception
2831 WHEN NO_DATA_FOUND THEN
2832 IF PG_DEBUG < 10 THEN
2833 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: Error in getting tag details for report: ' || sqlerrm);
2834 END IF;
2835
2836 WHEN OTHERS THEN
2837 IF PG_DEBUG < 10 THEN
2838 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: Error in getting tag details for report: ' || sqlerrm);
2839 END IF;
2840
2841 END; */
2842
2843
2844 --end if;
2845 --Begin Bug 8933776 30-Nov-2009 barathsr
2846 l_xml_body_1:=l_new_line||'<COMP_VAL>';
2847 l_xml_body_1:=l_xml_body_1||l_new_line||'<SCORE_COMP_ID>'||l_score_component_id||'</SCORE_COMP_ID>';
2848 begin
2849 select sctl.score_comp_name,sc.score_comp_weight
2850 into l_score_comp_name,l_score_comp_wtg
2851 from iex_score_components sc, iex_score_comp_types_tl sctl
2852 where sc.score_comp_type_id=sctl.score_comp_type_id
2853 and sc.score_component_id=l_score_component_id
2854 and sctl.language='US';
2855 l_xml_body_1:=l_xml_body_1||l_new_line||'<SCORE_COMP_NAME>'||format_string(l_score_comp_name)||'</SCORE_COMP_NAME>';
2856 l_xml_body_1:=l_xml_body_1||l_new_line||'<COMP_SCORE>'||l_component_score||'</COMP_SCORE>';
2857 l_xml_body_1:=l_xml_body_1||l_new_line||'<COMP_WGT>'||l_score_comp_wtg||'</COMP_WGT>';
2858 exception
2859 when others then
2860 FND_FILE.PUT_LINE(FND_FILE.LOG, 'error in get1score in getting score component details'||sqlerrm);
2861 IEX_DEBUG_PUB.logMessage('error in get1score in getting score component details'||sqlerrm);
2862 end;
2863 --End Bug 8933776 30-Nov-2009 barathsr
2864 /* 4. For each component value, get the details of the component
2865 and store the value for that score_comp_detail */
2866 BEGIN
2867 -- clchang updated 10/18/04 for 11.5.11
2868 -- new column NEW_VALUE instead of VALUE in iex_score_comp_det;
2869 --vSql := 'SELECT VALUE ' ||
2870 vSql := 'SELECT upper(NEW_VALUE) ' ||
2871 ' FROM IEX_SCORE_COMP_DET ' ||
2872 ' WHERE SCORE_COMPONENT_ID = :p_score_comp_id AND ' ||
2873 ' :p_component_score >= RANGE_LOW AND ' ||
2874 ' :p_component_score <= RANGE_HIGH ';
2875 if PG_DEBUG <= 5 then
2876 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2877 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: ' || 'Getting Details for component with ' || vSQL);
2878 END IF;
2879 end if;
2880
2881 -- clchang updated 10/18/04 for 11.5.11
2882 -- the value from det could be formula (including bind var :result);
2883 Execute Immediate vSql
2884 --INTO l_raw_score
2885 INTO l_value
2886 USING l_score_component_id, l_component_score, l_component_score;
2887
2888 --IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2889 --IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: Component raw score is ' || l_raw_score ||
2890 --' Component weight is ' || l_score_comp_tbl(l_count2).SCORE_COMP_WEIGHT);
2891 --END IF;
2892
2893 -- BEGIN clchang added 10/18/04 for scr engine enhancement in 11.5.11
2894
2895 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2896 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: l_value=' || l_value);
2897 END IF;
2898 -- chk the value is a formula or not
2899 IF (INSTR(l_value, ':RESULT') > 0 ) THEN
2900 l_new_value := replace(l_value, ':RESULT', l_component_score);
2901 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2902 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: FORMULA');
2903 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: l_new_value=' || l_new_value);
2904 END IF;
2905 vSql := 'SELECT ' || l_new_value || ' FROM DUAL';
2906 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2907 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: vSql=' || vSql);
2908 END IF;
2909 Execute Immediate vSql
2910 INTO l_raw_score;
2911 ELSE
2912 l_raw_score := TO_NUMBER( l_value);
2913 END IF;
2914 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2915 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: l_raw_score=' || l_raw_score);
2916 END IF;
2917
2918 l_xml_body_1:=l_xml_body_1||l_new_line||'<RAW_SCORE>'||l_raw_score||'</RAW_SCORE>';--Added for Bug 8933776 30-Nov-2009 barathsr
2919
2920 l_weight_required := IEX_SCORE_NEW_PVT.G_WEIGHT_REQUIRED;
2921
2922 -- if weight_required <> Y, sum(score of each comp);
2923 IF (l_weight_required = 'Y') THEN
2924 --l_running_score:=l_running_score + round((l_raw_score * l_score_comp_tbl(l_count2).SCORE_COMP_WEIGHT));
2925 l_wtg_com_score:=round((l_raw_score * p_score_comp_tbl(l_count2).SCORE_COMP_WEIGHT),2);
2926 l_running_score:=l_running_score + round((l_raw_score * p_score_comp_tbl(l_count2).SCORE_COMP_WEIGHT),2);
2927 ELSE
2928 --l_running_score:=l_running_score + round(l_raw_score );
2929 l_wtg_com_score:=round(l_raw_score,2);
2930 l_running_score:=l_running_score + round(l_raw_score,2 );
2931 END IF;
2932 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2933 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: l_running_score=' || l_running_score);
2934 END IF;
2935 --Begin Bug 8933776 30-Nov-2009 barathsr
2936 l_xml_body_1:=l_xml_body_1||l_new_line||'<WEIGHTED_COMP_SCORE>'||l_wtg_com_score||'</WEIGHTED_COMP_SCORE>';
2937 l_xml_body_1:=l_xml_body_1||l_new_line||'</COMP_VAL>';
2938 dbms_lob.writeAppend(tempResult, length(l_xml_body_1), l_xml_body_1);
2939 --End Bug 8933776 30-Nov-2009 barathsr
2940
2941 -- FND_FILE.put_line( FND_FILE.LOG,'*****end of score details************');
2942
2943 -- END clchang added 10/18/04 for scr engine enhancement in 11.5.11
2944
2945 --l_running_score:=l_running_score + round((l_raw_score * p_score_comp_tbl(l_count2).SCORE_COMP_WEIGHT));
2946 --IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: Component Running score is ' || l_running_score);
2947
2948 EXCEPTION
2949 WHEN NO_DATA_FOUND THEN
2950 IF PG_DEBUG < 10 THEN
2951 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: Error getting component detail: ' || sqlerrm);
2952 END IF;
2953 l_running_score := l_running_score;
2954 WHEN OTHERS THEN
2955 IF PG_DEBUG < 10 THEN
2956 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: Error getting component detail: ' || sqlerrm);
2957 END IF;
2958 l_running_score := l_running_score;
2959 END;
2960
2961 END LOOP; -- component loop
2962
2963
2964
2965 -- End copied code
2966
2967 IF PG_DEBUG < 10 THEN
2968 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: Return calculated score: ' || l_running_score );
2969 END IF;
2970
2971 return l_running_score;
2972
2973 Exception
2974 WHEN FND_API.G_EXC_ERROR THEN
2975 IF PG_DEBUG < 10 THEN
2976 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: Expected Error ' || sqlerrm );
2977 END IF;
2978 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR:IEX_SCORE: get1Score: Expected Error ' || sqlerrm);
2979
2980 WHEN OTHERS THEN
2981 IF PG_DEBUG < 10 THEN
2982 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: UnExpected Error ' || sqlerrm );
2983 END IF;
2984 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR:IEX_SCORE: get1Score: UnExpected Error ' || sqlerrm);
2985
2986 END get1Score;
2987
2988 --
2989 --- End - Andre Araujo - 11/02/2004 - New storage mode, this one respects the commit size - TAR 4040621.994
2990 --
2991 --Begin Bug 10354240 27-Sep-2010 barathsr
2992 function getComponentScore(p_scorecomp_id in number,
2993 p_object_id in number)
2994 return varchar
2995 is
2996 l_scorecomp_exe varchar2(4000);
2997 l_function_flag varchar2(1);
2998 l_component_score varchar2(4000);
2999 begin
3000 if p_scorecomp_id is not null then
3001 SELECT
3002 SCORE_COMP_VALUE,
3003 NVL(FUNCTION_FLAG, 'N') FUNCTION_FLAG
3004 into l_scorecomp_exe,l_function_flag
3005 FROM
3006 IEX_SCORE_COMP_TYPES_VL
3007 WHERE score_comp_type_id=p_scorecomp_id
3008 AND NVL(METRIC_FLAG, 'N') = 'N';
3009
3010 if l_function_flag = 'N' then
3011 -- simple select statement
3012 EXECUTE IMMEDIATE l_scorecomp_exe
3013 INTO l_component_score
3014 USING p_object_id;
3015 else
3016 -- function to execute
3017 -- to do - pass the score component id for Function calls only
3018 EXECUTE IMMEDIATE l_scorecomp_exe
3019 USING in p_object_id,
3020 in p_scorecomp_id,
3021 out l_component_score;
3022 end if;
3023 return l_component_score;
3024 end if;
3025 exception
3026 when others then
3027 return '1';
3028 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR:IEX_SCORE: getComponentScore: UnExpected Error ' || sqlerrm);
3029 end;
3030
3031 --End Bug 10354240 27-Sep-2010 barathsr
3032
3033
3034
3035 BEGIN
3036 G_Batch_Size := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '1000'));
3037 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3038
3039 G_MIN_SCORE := '1';
3040 G_MAX_SCORE := '100';
3041 G_RULE := 'CLOSEST';
3042 G_WEIGHT_REQUIRED := 'N';
3043
3044
3045 END IEX_SCORE_NEW_PVT;