1 PACKAGE BODY IEX_SCORE_NEW_PVT AS
2 /* $Header: iexvscfb.pls 120.21.12010000.2 2008/12/10 18:57:41 ehuh 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 /*
18 || Overview: validates any given objectID/Object_type pair
19 ||
20 || Parameter: p_Object_ID PK of object you wish to score
21 || p_Object_Type Type of Object you wish to score
22 || Alternatively if you wish to score another TYPE of object
23 || not listed pass the following as well:
24 || p_col_name name of colum you wish to select on
25 || p_table_name name of table to select from
26 ||
27 || Return value: True =OK; Falso=Error
28 ||
29 || Source Tables: IEX_DELINQUENCIES_ALL, IEX_CASES_B_ALL, HZ_PARTIES, AR_PAYMENT_SCHEDULES
30 || (these are the "FROM_TABLE" on JTF_OBJECTS_B
31 || Target Tables:
32 ||
33 || Creation date: 01/14/02 3:25:PM
34 ||
35 || Major Modifications: when who what
36 || 01/14/02 raverma created
37 */
38 function validateObjectID (p_object_id in number,
39 p_object_type in varchar2,
40 p_col_name in varchar2,
41 p_table_name in varchar2) return BOOLEAN
42
43 is
44
45 l_msg_count number;
46 l_msg_data varchar2(2000);
47 l_return_status varchar2(1);
48 l_col_name varchar2(200) ;
49 l_table_name varchar2(200) ;
50
51 BEGIN
52
53 l_col_name := p_col_name;
54 l_table_name := p_table_name;
55
56 -- get FROM_TABLE AND SELECT_ID from JTF_OBJECTS_B
57 if l_col_name is null or l_table_name is null then
58 begin
59 Execute Immediate
60 ' Select Select_ID, From_table ' ||
61 ' From jtf_objects_b ' ||
62 ' where object_code = :p_object_code'
63 into l_col_name, l_table_name
64 using p_object_type;
65 Exception
66 When no_data_found then
67 return FALSE;
68 end;
69 end if;
70
71 -- see if the ID passed is OK on the FROM_TABLE/SELECT_ID
72 iex_utilities.validate_any_id(p_col_id => p_object_id,
73 p_col_name => l_col_name,
74 p_table_name => l_table_name,
75 x_msg_count => l_msg_count,
76 x_msg_data => l_msg_data,
77 x_return_status => l_return_status,
78 p_init_msg_list =>fnd_api.g_false);
79
80 If l_return_Status = 'S' then
81 return TRUE;
82 else
83 return FALSE;
84 end if;
85
86 Exception
87 when others then
88 return false;
89
90 END validateObjectID;
91
92 /*
93 || Overview: compares whether the score engine being used for this object is of valid type
94 ||
95 || Parameter: p_score_id => scoring engine; p_object_type => type of object you wish to score
96 ||
97 || Return value: true=OK; FALSE=error
98 ||
99 || Source Tables: IEX_SCORES
100 ||
101 || Target Tables: NA
102 ||
103 || Creation date: 01/14/02 4:47:PM
104 ||
105 || Major Modifications: when who what
106 || 01/14/02 raverma created
107 */
108 function checkObject_Compatibility(p_score_id in number,
109 p_object_type in varchar2) return BOOLEAN
110 is
111 l_object_type varchar2(25);
112
113 begin
114
115 begin
116 Execute Immediate
117 ' Select jtf_object_code ' ||
118 ' From iex_scores ' ||
119 ' where score_id = :p_score_id'
120 into l_object_type
121 using p_score_id;
122 Exception
123 When no_data_found then
124 return FALSE;
125 end;
126
127 if l_object_type = p_object_type then
128 return TRUE;
129 else
130 return FALSE;
131 end if;
132
133 Exception
134 when others then
135 return false;
136
137 end checkObject_Compatibility;
138
139 /*
140 || Overview: Validate Score_Engine
141 ||
142 || Parameter: p_score_id is score engine you wish to validate
143 ||
144 || Source Tables: NA
145 ||
146 || Target Tables: NA
147 ||
148 || Creation date: 01/14/02 3:08:PM
149 ||
150 || Major Modifications: when who what
151 || 01/14/02 raverma created
152 */
153 PROCEDURE Validate_Score_Engine(p_score_id in number) IS
154
155 BEGIN
156 NULL;
157 End Validate_Score_Engine;
158
159
160
161 /*
162 * clchang added this new procedure 10/18/04 for 11.5.11.
163 * this procedure will get the score_range_low, score_range_high,
164 * out_of_range_rule for a given score engine, and update the
165 * global variables: G_MIN_SCORE, G_MAX_SCORE, G_RULE.
166 *
167 * Parameter: P_SCORE_ID Scoring_Engine
168 * Major Modifications:
169 * when who what
170 * 10/18/04 clchang created
171 ******/
172 PROCEDURE getScoreRange(P_SCORE_ID IN NUMBER )
173 IS
174
175 CURSOR c_chk_range(p_score_id NUMBER) IS
176 SELECT NVL(WEIGHT_REQUIRED, 'N'),
177 NVL(SCORE_RANGE_LOW, IEX_SCORE_NEW_PVT.G_MIN_SCORE),
178 NVL(SCORE_RANGE_HIGH, IEX_SCORE_NEW_PVT.G_MAX_SCORE),
179 NVL(OUT_OF_RANGE_RULE, IEX_SCORE_NEW_PVT.G_RULE)
180 FROM IEX_SCORES
181 WHERE SCORE_ID = p_score_id;
182
183 l_weight_required VARCHAR2(3);
184 l_low varchar2(2000);
185 l_high varchar2(2000);
186 l_rule varchar2(20);
187
188 BEGIN
189
190 -- chk the score range
191 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
192 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreRange: score_id=' || p_score_id);
193 END IF;
194
195 BEGIN
196 OPEN c_chk_range(p_score_id);
197 FETCH c_chk_range
198 INTO l_weight_required, l_low, l_high, l_rule;
199 CLOSE c_chk_range;
200
201 EXCEPTION
202 WHEN NO_DATA_FOUND THEN
203 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
204 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreRange: Error getting score range: ' || sqlerrm);
205 END IF;
206 l_weight_required := IEX_SCORE_NEW_PVT.G_WEIGHT_REQUIRED;
207 l_low := IEX_SCORE_NEW_PVT.G_MIN_SCORE;
208 l_high := IEX_SCORE_NEW_PVT.G_MAX_SCORE;
209 l_rule := IEX_SCORE_NEW_PVT.G_RULE;
210 WHEN OTHERS THEN
211 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
212 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreRange: Error getting scr range: ' || sqlerrm);
213 END IF;
214 l_weight_required := IEX_SCORE_NEW_PVT.G_WEIGHT_REQUIRED;
215 l_low := IEX_SCORE_NEW_PVT.G_MIN_SCORE;
216 l_high := IEX_SCORE_NEW_PVT.G_MAX_SCORE;
217 l_rule := IEX_SCORE_NEW_PVT.G_RULE;
218 END;
219
220 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
221 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreRange: weight:' || l_weight_required);
222 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreRange: low:' || l_low);
223 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreRange: high:' || l_high);
224 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreRange: rule:' || l_rule);
225 END IF;
226
227 IEX_SCORE_NEW_PVT.G_WEIGHT_REQUIRED := l_weight_required;
228 IEX_SCORE_NEW_PVT.G_MIN_SCORE := l_low;
229 IEX_SCORE_NEW_PVT.G_MAX_SCORE := l_high;
230 IEX_SCORE_NEW_PVT.G_RULE := l_rule;
231
232 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
233 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreRange: g_weight:' || IEX_SCORE_NEW_PVT.G_WEIGHT_REQUIRED);
234 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreRange: g_low:' || IEX_SCORE_NEW_PVT.G_MIN_SCORE);
235 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreRange: g_high:' || IEX_SCORE_NEW_PVT.G_MAX_SCORE);
236 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreRange: g_rule:' || IEX_SCORE_NEW_PVT.G_RULE);
237 END IF;
238
239 END getScoreRange;
240
241
242
243
244 /*
245 || this procedure will get all components for a given score engine
246 || and return them as a tbl
247 ||
248 || Parameter: P_SCORE_ID Scoring_Engine
249 || X_SCORE_COMP_TBL = table of components attached to the Scoring engine
250 ||
251 || Return value: select statement for the Universe
252 ||
253 || Source Tables: IEX_SCORES, IEX_SCORE_COMPONENTS, IEX_SCORE_COMP_TYPES_B/TL
254 ||
255 || Target Tables: none
256 ||
257 || Creation date: 01/14/02 1:55:PM
258 ||
259 || Major Modifications: when who what
260 || 01/14/02 raverma created
261 || 03/12/02 raverma added function_flag to return tbl
262 || 10/18/04 clchang updated this procedure for scoring engine enhancement
263 || in 11.5.11.
264 || 1. new column METRIC_FLAG in iex_score_components
265 */
266 PROCEDURE getComponents(P_SCORE_ID IN NUMBER,
267 X_SCORE_COMP_TBL OUT NOCOPY IEX_SCORE_NEW_PVT.SCORE_ENG_COMP_TBL)
268 IS
269
270 -- clchang updated the cursor 10/18/04 with metric_flag;
271 --
272 -- this cursor will enumerate all components for a particular engine
273 CURSOR c_score_components(p_score_id NUMBER) IS
274 SELECT
275 SCORE_COMPONENT_ID,
276 SCORE_COMP_WEIGHT,
277 SCORE_COMP_VALUE,
278 NVL(FUNCTION_FLAG, 'N') FUNCTION_FLAG
279 FROM
280 IEX_SCORE_ENG_COMPONENTS_V
281 WHERE SCORE_ID = p_score_id
282 AND NVL(METRIC_FLAG, 'N') = 'N';
283
284
285 l_score_comp_tbl IEX_SCORE_NEW_PVT.SCORE_ENG_COMP_TBL;
286 i NUMBER := 0;
287 l_score_comp_id NUMBER;
288 l_score_component_weight NUMBER(3,2);
289 l_score_comp_value VARCHAR2(2000);
290 l_function_flag VARCHAR2(1);
291
292 Begin
293
294 -- IF PG_DEBUG < 10 THEN
295 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
296 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getComponents: getting Score Engine Components for Engine: ' || p_score_id);
297 END IF;
298
299 OPEN c_score_components(p_score_id);
300 LOOP
301 i := i + 1;
302 FETCH c_score_components INTO
303 l_score_comp_id, l_score_component_weight, l_score_comp_value, l_function_flag;
304 EXIT WHEN c_score_components%NOTFOUND;
305 l_score_comp_tbl(i).SCORE_COMPONENT_ID := l_score_comp_id;
306 l_score_comp_tbl(i).SCORE_COMP_WEIGHT := l_score_component_weight;
307 l_score_comp_tbl(i).SCORE_COMP_VALUE := l_score_comp_value;
308 l_score_comp_tbl(i).FUNCTION_FLAG := l_function_flag;
309 --- Begin - Andre Araujo - 11/02/2004 - New storage mode, Scores_tbl becomes too big - TAR 4040621.994
310 l_score_comp_tbl(i).SCORE_ID := p_score_id;
311 --- End - Andre Araujo - 11/02/2004 - New storage mode, Scores_tbl becomes too big - TAR 4040621.994
312 END LOOP;
313
314 -- IF PG_DEBUG < 10 THEN
315 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
316 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getComponents: components found ' || l_score_comp_tbl.count);
317 END IF;
318 x_score_comp_tbl := l_score_comp_tbl;
319
320 CLOSE c_score_components;
321
322 Exception
323 When No_Data_Found then
324 x_score_comp_tbl := l_score_comp_tbl;
325 CLOSE c_score_components;
326
327 When others Then
328 x_score_comp_tbl := l_score_comp_tbl;
329 CLOSE c_score_components;
330
331 end getComponents;
332
333 /*
334 || Overview: This is the "main" scoring function. It will score any objects using the
335 || table of components passed to. The assumption is that any validation has been done already AND
336 || the table of components passed here is appropriate for the universe of IDS
337 ||
338 || Parameter: p_score_comp_tbl => components to use for scoring execution
339 || t_object_ids => universe of object_ids you wish to score
340 || (this universe MUST be valid for the components definition)
341 || x_scores_tbl => table of scores for the IDs passed
342 ||
343 || 11/02/04 acaraujo x_scores_tbl returns the bridge to the next concurrent prog.
344 || and scores are store as per the G_BATCH_SIZE to the history table
345 ||
346 || Source Tables: iex_score_comp_det
347 ||
348 || Target Tables: NA
349 ||
350 || Creation date: 01/14/02 5:27:PM
351 ||
352 || Major Modifications: when who what
353 || 01/14/02 raverma created
354 || 10/18/04 clchang updated this procedure for scoring engine enhancement
355 || in 11.5.11.
356 || 1. new column METRIC_FLAG in iex_score_components
357 || 2. new columns 'WEIGHT_REQUIRED, SCORE_RANGE_LOW,
358 || SCORE_RANGE_HIGH, OUT_OF_RANGE_RULE' in iex_scores
359 || 3. no 1-100 score limitation;
360 || 4. weight_required decides the weight of comp;
361 || 5. in comp det, the value could be formula;
362 || (only one BIND Var :result could be used.)
363 ||
364 || 11/02/04 acaraujo x_scores_tbl returns the bridge to the next concurrent prog.
365 || and scores are store as per the G_BATCH_SIZE to the history table
366 */
367 procedure getScores(p_score_comp_tbl IN IEX_SCORE_NEW_PVT.SCORE_ENG_COMP_TBL,
368 t_object_ids IN IEX_FILTER_PUB.UNIVERSE_IDS,
369 x_scores_tbl OUT NOCOPY IEX_SCORE_NEW_PVT.SCORES_TBL)
370 IS
371
372 l_api_name varchar2(10) ;
373 l_universe_size number := 0;
374 l_count NUMBER := 0;
375 l_components_count number := 0;
376
377 l_weight_required VARCHAR2(3);
378 l_low varchar2(2000);
379 l_high varchar2(2000);
380 l_rule varchar2(20);
381 --l_raw_score number := IEX_SCORE_NEW_PVT.G_MIN_SCORE;
382 l_raw_score number := 0;
383 l_value VARCHAR2(2000);
384 l_new_value VARCHAR2(2000);
385
386 l_running_score number := 0;
387 l_component_score number := 0;
388 l_count2 number := 0;
389 l_score_comp_tbl IEX_SCORE_NEW_PVT.SCORE_ENG_COMP_TBL ;
390 l_score_component_id NUMBER;
391 l_score_component_sql VARCHAR2(2500);
392 l_scores_tbl IEX_SCORE_NEW_PVT.SCORES_TBL;
393 vSql varchar2(2500);
394
395 type COMPONENT_RANGE is table of NUMBER
396 index by binary_integer;
397 l_component_range_tbl COMPONENT_RANGE;
398 i NUMBER := 0;
399 l_execute_style VARCHAR2(1); -- are we using select or function call
400
401 --- Begin - Andre Araujo - 11/02/2004 - New storage mode, this one respects the commit size - TAR 4040621.994
402 l_new_scores_tbl IEX_SCORE_NEW_PVT.NEW_SCORES_TBL ;
403 l_objects_tbl IEX_SCORE_NEW_PVT.SCORE_OBJECTS_TBL ;
404 l_scorecount number := 0;
405 l_bridge NUMBER;
406 --- End - Andre Araujo - 11/02/2004 - New storage mode, this one respects the commit size - TAR 4040621.994
407
408 -- Begin - Andre Araujo - 12/17/2004 - Store del_buffers only if we need to
409 l_conc_prog_name VARCHAR2(1000);
410 -- End - Andre Araujo - 12/17/2004 - Store del_buffers only if we need to
411
412 BEGIN
413
414 l_api_name := 'getScores';
415 l_score_comp_tbl := p_score_comp_tbl;
416
417 -- IF PG_DEBUG < 10 THEN
418 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
419 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: Start time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
420 END IF;
421 --
422 -- Api body
423 --
424 --- Begin - Andre Araujo - 11/02/2004 - New storage mode, this one respects the commit size - TAR 4040621.994
425 --Commented by schekuri for bug#6373998 by schekuri on 31-Aug-2007
426 --Select IEX_DEL_WF_S.NEXTVAL INTO l_bridge FROM Dual;
427 --- End - Andre Araujo - 11/02/2004 - New storage mode, this one respects the commit size - TAR 4040621.994
428
429 l_bridge := l_del_buff_bridge; --Added by schekuri for bug#6373998 by schekuri on 31-Aug-2007
430 FND_FILE.PUT_LINE(FND_FILE.LOG,'Using bridge id ' || l_bridge);
431 l_universe_size := t_object_ids.count;
432 -- IF PG_DEBUG < 10 THEN
433 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
434 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: Universe size is ' || l_universe_size);
435 END IF;
436
437 -- Begin - Andre Araujo - 12/17/2004 - Store del_buffers only if we need to
438 Begin -- This will be an exception block
439 Select NVL(CONCURRENT_PROG_NAME, 'X')
440 Into l_conc_prog_name
441 From IEX_SCORES scr, IEX_SCORE_COMPONENTS scomp
442 Where scomp.score_component_id = p_score_comp_tbl(1).SCORE_COMPONENT_ID
443 AND scr.Score_ID = scomp.score_id;
444 exception
445 when OTHERS THEN
446 l_conc_prog_name := 'X';
447 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_SCORE: getScores: Exception getting the concurrent program. Error: ' || sqlerrm );
448 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_SCORE: getScores: Program will continue, no concurrent program will be launched' );
449 end;
450 -- End - Andre Araujo - 12/17/2004 - Store del_buffers only if we need to
451
452 FOR l_count IN 1..l_universe_size LOOP
453
454 if PG_DEBUG <= 5 then
455 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
456 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: Scoring item ' || l_count || ' in universe');
457 END IF;
458 end if;
459 l_components_count := p_score_comp_tbl.count;
460 l_running_score := 0;
461
462 --- get1Score removed from here
463 l_running_score := get1Score( l_score_comp_tbl, t_object_ids(l_count) );
464 --- End get1Score removed from here
465 -- /* 3. for each component, execute SQL and get value */
466 -- FOR l_count2 IN 1..l_components_count LOOP
467 -- l_score_component_id := l_score_comp_tbl(l_count2).score_component_id;
468 -- l_score_component_sql := l_score_comp_tbl(l_count2).SCORE_COMP_VALUE;
469 -- l_execute_style := l_score_comp_tbl(l_count2).function_flag;
470 -- -- initialize this to the minimum for any given component
471 -- --l_raw_score := IEX_SCORE_PVT.G_MIN_SCORE;
472 --
473 -- if PG_DEBUG <= 5 then
474 -- IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
475 -- IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: executing Component ' || l_count2 || ' CompID is: ' || l_score_component_id);
476 -- IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: Execute Stmt: ' || l_score_component_sql || ' Execute Style: ' || l_execute_style);
477 -- IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: Bind Variable: ' || t_object_ids(l_count));
478 -- END IF;
479 -- end if;
480 --
481 -- /* executing dynamic sql for component */
482 -- if l_score_component_sql is not null then
483 -- BEGIN
484 --
485 -- -- Execute SQL statement only when function syntax is not found
486 -- if l_execute_style = 'N' then
487 -- -- simple select statement
488 -- EXECUTE IMMEDIATE l_score_component_sql
489 -- INTO l_component_score
490 -- USING t_object_ids(l_count);
491 -- else
492 -- -- function to execute
493 -- -- to do - pass the score component id for Function calls only
494 -- EXECUTE IMMEDIATE l_score_component_sql
495 -- USING in t_object_ids(l_count),
496 -- in l_score_component_id,
497 -- out l_component_score;
498 -- end if;
499 --
500 -- EXCEPTION
501 --
502 -- -- assign the "Lowest" Detail for the component
503 -- -- in order to do this we must know what is "high" and "low" range of component
504 --
505 -- WHEN OTHERS THEN
506 -- -- figure out whether the component details are better higher or worse higher
507 -- IF PG_DEBUG <= 5 THEN
508 -- IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
509 -- IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: Failed to calculate for component ' || l_score_component_id );
510 -- END IF;
511 -- SELECT Range_Low
512 -- BULK COLLECT INTO l_component_range_tbl
513 -- FROM iex_score_comp_det
514 -- where score_component_id = l_score_component_id
515 -- order by value;
516 --
517 -- if l_component_range_tbl(1) < l_component_range_tbl(2) then
518 -- -- assign first comnponent detail row range to value
519 -- l_component_score := l_component_range_tbl(1);
520 -- else
521 -- -- assign last comnponent detail row range to value
522 -- i := l_component_range_tbl.count;
523 -- l_component_score := l_component_range_tbl(i);
524 -- end if;
525 --
526 -- END;
527 --
528 -- if PG_DEBUG <= 5 then
529 -- IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
530 -- IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: Successfully calculated component score: ' || l_component_score);
531 -- END IF;
532 -- end if;
533 --
534 -- end if;
535 --
536 -- /* 4. For each component value, get the details of the component
537 -- and store the value for that score_comp_detail */
538 -- BEGIN
539 -- -- clchang updated 10/18/04 for 11.5.11
540 -- -- new column NEW_VALUE instead of VALUE in iex_score_comp_det;
541 -- --vSql := 'SELECT VALUE ' ||
542 -- vSql := 'SELECT upper(NEW_VALUE) ' ||
543 -- ' FROM IEX_SCORE_COMP_DET ' ||
544 -- ' WHERE SCORE_COMPONENT_ID = :p_score_comp_id AND ' ||
545 -- ' :p_component_score >= RANGE_LOW AND ' ||
546 -- ' :p_component_score <= RANGE_HIGH ';
547 -- if PG_DEBUG <= 5 then
548 -- IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
549 -- IEX_DEBUG_PUB.logMessage('getScores: ' || 'Getting Details for component with ' || vSQL);
550 -- END IF;
551 -- end if;
552 --
553 -- -- clchang updated 10/18/04 for 11.5.11
554 -- -- the value from det could be formula (including bind var :result);
555 -- Execute Immediate vSql
556 -- --INTO l_raw_score
557 -- INTO l_value
558 -- USING l_score_component_id, l_component_score, l_component_score;
559 --
560 -- --IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
561 -- --IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: Component raw score is ' || l_raw_score || ' Component weight is ' || l_score_comp_tbl(l_count2).SCORE_COMP_WEIGHT);
562 -- --END IF;
563 --
564 --
565 -- -- BEGIN clchang added 10/18/04 for scr engine enhancement in 11.5.11
566 --
567 -- IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
568 -- IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: l_value=' || l_value);
569 -- END IF;
570 -- -- chk the value is a formula or not
571 -- IF (INSTR(l_value, ':RESULT') > 0 ) THEN
572 -- l_new_value := replace(l_value, ':RESULT', l_component_score);
573 -- IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
574 -- IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: FORMULA');
575 -- IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: l_new_value=' || l_new_value);
576 -- END IF;
577 -- vSql := 'SELECT ' || l_new_value || ' FROM DUAL';
578 -- IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
579 -- IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: vSql=' || vSql);
580 -- END IF;
581 -- Execute Immediate vSql
582 -- INTO l_raw_score;
583 -- ELSE
584 -- l_raw_score := TO_NUMBER( l_value);
585 -- END IF;
586 -- IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
587 -- IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: l_raw_score=' || l_raw_score);
588 -- END IF;
589 --
590 --
591 -- l_weight_required := IEX_SCORE_NEW_PVT.G_WEIGHT_REQUIRED;
592 --
593 -- -- if weight_required <> Y, sum(score of each comp);
594 -- IF (l_weight_required = 'Y') THEN
595 -- --l_running_score:=l_running_score + round((l_raw_score * l_score_comp_tbl(l_count2).SCORE_COMP_WEIGHT));
596 -- l_running_score:=l_running_score + round((l_raw_score * l_score_comp_tbl(l_count2).SCORE_COMP_WEIGHT),2);
597 -- ELSE
598 -- --l_running_score:=l_running_score + round(l_raw_score );
599 -- l_running_score:=l_running_score + round(l_raw_score,2 );
600 -- END IF;
601 -- IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
602 -- IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: l_running_score=' || l_running_score);
603 -- END IF;
604 -- -- END clchang added 10/18/04 for scr engine enhancement in 11.5.11
605 --
606 -- /*
607 -- l_running_score:=l_running_score + round((l_raw_score * l_score_comp_tbl(l_count2).SCORE_COMP_WEIGHT));
608 -- */
609 --
610 -- --IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
611 -- --IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: Component Running score is ' || l_running_score);
612 -- --END IF;
613 -- EXCEPTION
614 -- WHEN NO_DATA_FOUND THEN
615 -- IF PG_DEBUG < 10 THEN
616 -- IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
617 -- IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: Error getting component detail: ' || sqlerrm);
618 -- END IF;
619 -- l_running_score := l_running_score;
620 -- WHEN OTHERS THEN
621 -- IF PG_DEBUG < 10 THEN
622 -- IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
623 -- IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: Error getting component detail: ' || sqlerrm);
624 -- END IF;
625 -- l_running_score := l_running_score;
626 -- END;
627 --
628 -- END LOOP; -- component loop
629 --
630 --
631 -- -- BEGIN clchang added 10/18/04 for scr engine enhancement in 11.5.11
632 --
633 -- clchang updated the score logic
634 /***************
635 -- if the score value falls above or below the hard coded floor / ceiling we will force the score
636 -- to the floor or ceiling
637 if l_running_score < IEX_SCORE_NEW_PVT.G_MIN_SCORE then
638 l_running_score := IEX_SCORE_NEW_PVT.G_MIN_SCORE;
639 elsif l_running_score > IEX_SCORE_NEW_PVT.G_MAX_SCORE then
640 l_running_score := IEX_SCORE_NEW_PVT.G_MAX_SCORE;
641 end if;
642 *******************************************/
643
644 /*********************
645 * with the new logic on scr engine;
646 * 1.no score limitation 1-100;
647 * 2.the score range should between score_range_low and score_range_high of
648 * each scoring engine;
649 * 3. if the score is out of range, following the out_of_range_rule of
650 * each scoring engine;
651 * ex: one scoring engine with low -50, high 999 and rule 'CLOSEST';
652 * if the score is -100, then the final score should be the closest
653 * score of score range => -50;
654 * if the rule is farthest, then the final score should be 999;
655 ***********************************************************************/
656
657 -- get the final score
658 l_rule := IEX_SCORE_NEW_PVT.G_RULE;
659 IF (l_rule = 'CLOSEST') THEN
660 if l_running_score < IEX_SCORE_NEW_PVT.G_MIN_SCORE then
661 l_running_score := IEX_SCORE_NEW_PVT.G_MIN_SCORE;
662 elsif l_running_score > IEX_SCORE_NEW_PVT.G_MAX_SCORE then
663 l_running_score := IEX_SCORE_NEW_PVT.G_MAX_SCORE;
664 end if;
665 ELSE
666 if l_running_score < IEX_SCORE_NEW_PVT.G_MIN_SCORE then
667 l_running_score := IEX_SCORE_NEW_PVT.G_MAX_SCORE;
668 elsif l_running_score > IEX_SCORE_NEW_PVT.G_MAX_SCORE then
669 l_running_score := IEX_SCORE_NEW_PVT.G_MIN_SCORE;
670 end if;
671 END IF;
672 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
673 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: score:' || l_running_score);
674 END IF;
675 -- END clchang added 10/18/04 for scr engine enhancement in 11.5.11
676
677 -- fill out return table
678 --- Begin - Andre Araujo - 11/02/2004 - New storage mode, this one respects the commit size - TAR 4040621.994
679 --l_scores_tbl(l_count) := l_running_score;
680
681 l_scorecount := l_scorecount + 1;
682 l_objects_tbl(l_scorecount) := t_object_ids(l_count);
683 l_new_scores_tbl(l_scorecount) := l_running_score;
684 if l_scorecount >= G_BATCH_SIZE then
685 storeScoreHistory ( l_score_comp_tbl(1).SCORE_ID, l_objects_tbl, l_new_scores_tbl );
686 -- Begin - Andre Araujo - 12/17/2004 - Store del_buffers only if we need to
687 if (l_conc_prog_name <> 'X') then
688 storeDelBuffers ( l_score_comp_tbl(1).SCORE_ID, l_objects_tbl, l_new_scores_tbl,l_bridge);
689 end if;
690 -- End - Andre Araujo - 12/17/2004 - Store del_buffers only if we need to
691 l_scorecount := 0;
692 l_objects_tbl.delete;
693 l_new_scores_tbl.delete;
694 l_scores_tbl.delete;
695 end if;
696 --- End - Andre Araujo - 11/02/2004 - New storage mode, this one respects the commit size - TAR 4040621.994
697
698 END LOOP; -- universe loop
699
700 --- Begin - Andre Araujo - 11/02/2004 - New storage mode, this one respects the commit size - TAR 4040621.994
701 if l_scorecount > 0 then -- Store the leftovers
702 storeScoreHistory ( l_score_comp_tbl(1).SCORE_ID, l_objects_tbl, l_new_scores_tbl );
703 -- Begin - Andre Araujo - 12/17/2004 - Store del_buffers only if we need to
704 if (l_conc_prog_name <> 'X') then
705 storeDelBuffers ( l_score_comp_tbl(1).SCORE_ID, l_objects_tbl, l_new_scores_tbl,l_bridge);
706 end if;
707 -- End - Andre Araujo - 12/17/2004 - Store del_buffers only if we need to
708 end if;
709
710 l_scores_tbl.delete;
711 l_scores_tbl(1) := l_bridge;
712 --- End - Andre Araujo - 11/02/2004 - New storage mode, this one respects the commit size - TAR 4040621.994
713
714 x_scores_tbl := l_scores_tbl;
715
716 -- IF PG_DEBUG < 10 THEN
717 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
718 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
719 END IF;
720
721 Exception
722
723 When Others Then
724 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in getScores: ' || sqlerrm );
725 RAISE FND_API.G_EXC_ERROR;
726 END getScores;
727
728 /*
729 || Overview: score all objects for a given scoring engine
730 ||
731 || Parameter: p_score_id => scoring engine ID
732 ||
733 || Source Tables: IEX_SCORES, IEX_SCORE_COMPONENTS_VL, IEX_SCORE_COMP_TYPES, IEX_SCORE_COMP_DET,
734 || IEX_OBJECT_FILTERS
735 ||
736 || Target Tables:
737 ||
738 || Creation date: 01/22/02 3:14:PM
739 ||
740 || Major Modifications: when who what
741 || 01/22/02 raverma created
742 */
743 procedure scoreObjects(p_api_version IN NUMBER,
744 p_init_msg_list IN VARCHAR2,
745 p_commit IN VARCHAR2,
746 P_SCORE_ID IN NUMBER,
747 x_return_status OUT NOCOPY VARCHAR2,
748 x_msg_count OUT NOCOPY NUMBER,
749 x_msg_data OUT NOCOPY VARCHAR2)
750 IS
751
752 l_api_name varchar2(25);
753 l_api_version_number number := 1;
754 l_return_status VARCHAR2(1);
755 l_msg_count NUMBER;
756 l_msg_data VARCHAR2(32767);
757
758 --vPLSQL varchar2(5000);
759 l_universe IEX_FILTER_PUB.UNIVERSE_IDS; -- for TestUniverse / testGetScores
760 l_components_tbl IEX_SCORE_NEW_PVT.SCORE_ENG_COMP_TBL;-- for testGetComponents / testGetScores
761 l_scores_tbl IEX_SCORE_NEW_PVT.SCORES_TBL; -- fore testGetScores
762 b_valid BOOLEAN;
763 l_object_type VARCHAR2(25);
764 l_universe_size NUMBER := 0;
765 l_conc_prog_name VARCHAR2(1000);
766 l_submit_request_id NUMBER;
767 l_bridge NUMBER;
768 k NUMBER := 1;
769 l_passes NUMBER := 0;
770 l_mod NUMBER := 0;
771 l_user NUMBER;
772 i NUMBER := 1;
773 l_program NUMBER;
774 l_prog_appl NUMBER;
775 l_request NUMBER;
776
777 --jsanju 06/21/04 --added for wait for request check
778 uphase VARCHAR2(255);
779 dphase VARCHAR2(255);
780 ustatus VARCHAR2(255);
781 dstatus VARCHAR2(255);
782 l_bool BOOLEAN;
783 message VARCHAR2(32000);
784 l_last_obj_scored NUMBER;
785 l_last_batch boolean;
786
787 -- Begin - schekuri - 6156648 - 10/Jun/2007 - Adding wait for request to try to make it wait
788 bReturn boolean;
789 vReturn varchar2(100);
790 -- End - schekuri - 6156648 - 10/Jun/2007 - Adding wait for request to try to make it wait
791
792
793 BEGIN
794 -- Standard Start of API savepoint
795 SAVEPOINT scoreObjects_PVT;
796
797 l_api_name := 'scoreObjects';
798
799 -- Standard call to check for call compatibility.
800 IF NOT FND_API.Compatible_API_Call (l_api_version_number, p_api_version,
801 l_api_name, G_PKG_NAME)
802 THEN
803 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
804 END IF;
805
806 -- Initialize message list IF p_init_msg_list is set to TRUE.
807 IF FND_API.to_Boolean( p_init_msg_list ) THEN
808 FND_MSG_PUB.initialize;
809 END IF;
810
811 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
812 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: Start time:'|| TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
813 END IF;
814
815 -- Initialize API return status to SUCCESS
816 x_return_status := FND_API.G_RET_STS_SUCCESS;
817
818 --
819 -- Api body
820 --
821
822 -- initial variables needed
823 select jtf_object_code into l_object_type
824 from iex_scores
825 where score_id = p_score_id;
826
827 -- enumerate components for this scoring engine
828 iex_score_new_pvt.getComponents(p_score_id => p_score_id ,
829 X_SCORE_COMP_TBL => l_components_tbl);
830
831 if l_components_tbl is null or l_components_tbl.count < 1 then
832 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
833 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: No score components for engine');
834 END IF;
835 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_SCORE: scoreObjects: No score components for engine ' || p_score_id);
836 FND_MESSAGE.Set_Name('IEX', 'IEX_NO_SCORE_ENG_COMPONENTS');
837 FND_MSG_PUB.Add;
838 RAISE FND_API.G_EXC_ERROR;
839 end if;
840
841 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
842 IEX_DEBUG_PUB.logMessage('scoreObjects: ' || 'Batch Size is ' || G_BATCH_SIZE || ' rows');
843 END IF;
844
845 l_user := FND_GLOBAL.USER_ID;
846 l_request := nvl(FND_GLOBAL.Conc_REQUEST_ID,0);
847 l_program := FND_GLOBAL.CONC_PROGRAM_ID;
848 l_prog_appl := FND_GLOBAL.PROG_APPL_ID;
849
850
851 Select IEX_DEL_WF_S.NEXTVAL INTO l_del_buff_bridge FROM Dual; --Added by schekuri for bug#6373998 on 31-Aug-2007
852 FND_FILE.PUT_LINE(FND_FILE.LOG,'Using bridge ' || l_del_buff_bridge || ' one for each scoring engine');
853 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
854 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: got bridge id ' || l_del_buff_bridge || ' once for each scoring engine');
855 END IF;
856
857 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
858 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: building Universe');
859 END IF;
860
861
862
863
864 -- bug#5586925 score in loop to increase scaleablility
865 l_last_batch := false;
866 while not l_last_batch loop
867 l_universe := iex_filter_pub.buildUniverse(p_object_id => p_score_id,
868 p_object_type => 'IEXSCORE',
869 p_last_object_scored => l_last_obj_scored,
870 x_end_of_universe => l_last_batch);
871
872 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: last object ' || l_last_obj_scored);
873
874 if (l_universe is null or l_universe.count < 1) and not l_last_batch then
875 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
876 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: Universe size is zero');
877 END IF;
878 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Universe Size is Zero or Invalid for Engine ' || p_score_id);
879
880 FND_MESSAGE.Set_Name('IEX', 'IEX_UNIVERSE_SIZE_ZERO');
881 FND_MSG_PUB.Add;
882
883 --START jsanju 10/19/05 for bug 3549051
884 --RAISE FND_API.G_EXC_ERROR;
885 RAISE IEX_UNIVERSE_SIZE_ZERO_ERROR;
886 --END jsanju 10/19/05 for bug 3549051
887
888 end if;
889
890 -- begin clchang added 10/20/04 for 11.5.11 score engine enhancement
891 -- get the score_range_low, score_range_high, out_of_range_rule, and
892 -- weight_required of this given score engine;
893 iex_score_new_pvt.getScoreRange(p_score_id => p_score_id );
894 -- if weight_required is Y, then chk weight of each comp is null or not;
895 IF (IEX_SCORE_NEW_PVT.G_WEIGHT_REQUIRED = 'Y') Then
896 FOR i in 1..l_components_tbl.count
897 LOOP
898 if (l_components_tbl(i).score_comp_weight is null) then
899 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
900 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: no comp weight');
901 END IF;
902 FND_FILE.PUT_LINE(FND_FILE.LOG,
903 'Score Comp Weight are required for Engine '|| p_score_id);
904 FND_MESSAGE.Set_Name('IEX', 'IEX_WEIGHT_REQUIRED');
905 FND_MSG_PUB.Add;
906 RAISE FND_API.G_EXC_ERROR;
907 end if;
908 END LOOP;
909 END IF;
910 -- end clchang added 10/20/04 for 11.5.11 score engine enhancement
911
912 -- get the scores for the Universe
913 iex_score_new_pvt.getScores(p_score_comp_tbl => l_components_tbl,
914 t_object_ids => l_universe,
915 x_scores_tbl => l_scores_tbl);
916
917 end loop;
918
919 l_bridge := l_scores_tbl(1); -- The table now contains the bridge to the next concurrent program or nothing
920 l_universe_size := l_universe.count;
921
922 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Scored ' || l_universe_size || ' objects of type ' || l_object_type);
923 Begin
924 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
925 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: Finding any process to spawn...');
926 END IF;
927 Select NVL(cp.Concurrent_Program_Name, 'X')
928 Into l_conc_prog_name
929 From IEX_SCORES scr, fnd_concurrent_programs cp
930 Where scr.concurrent_prog_name = cp.concurrent_program_name AND
931 scr.Score_ID = p_score_id;
932 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_SCORE: scoreObjects: Spawning ' || l_conc_prog_name);
933
934 --- Begin - Eun Huh - 02/15/2007 - bug 5763675/5696238 if run multiple Scoring Engine Harness program at the same time it will pick up only the last one always
935 --select MAX(request_id)
936 -- into l_bridge
937 -- from iex_del_buffers
938 -- where PROGRAM_APPLICATION_ID = l_prog_appl
939 -- and PROGRAM_ID = l_program
940 -- and CREATED_BY = l_user;
941
942 --- End - LKKUMAR - 13-Apr-2006. Replace the SQL with MAX. --Bug5154199.
943
944 -- spawn proces if conc_prog_id is there
945 if l_conc_prog_name <> 'X' then
946 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
947 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: spawning ' || l_conc_prog_name ||
948 ' with bridge ' || l_bridge);
949 END IF;
950
951 --Start MOAC
952 fnd_request.set_org_id(mo_global.get_current_org_id);
953
954 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
955 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: spawning ' || l_conc_prog_name || ' for operating unit: ' || nvl(mo_global.get_ou_name(mo_global.get_current_org_id), 'All'));
956 END IF;
957 --End MOAC
958
959 l_submit_request_id := FND_REQUEST.SUBMIT_REQUEST(
960 APPLICATION => 'IEX',
961 PROGRAM => l_conc_prog_name,
962 DESCRIPTION => 'Oracle Collections Score Engine Spawned Process for Operating Unit: '||
963 nvl(mo_global.get_ou_name(mo_global.get_current_org_id), 'All'),
964 START_TIME => sysdate,
965 SUB_REQUEST => false,
966 ARGUMENT1 => l_bridge);
967 COMMIT;
968
969 -- Begin - schekuri - 6156648 - 10/Jun/2007 - Adding wait for request to try to make it wait
970 bReturn := FND_CONCURRENT.WAIT_FOR_REQUEST(l_submit_request_id,60,0,vReturn,vReturn,vReturn,vReturn,vReturn);
971 -- End - schekuri - 6156648 - 10/Jun/2007 - Adding wait for request to try to make it wait
972
973
974 --jsanju 06/21/04
975 --the main process should wait till the spawned process is
976 --over.
977 IF (l_submit_request_id IS NOT NULL AND l_submit_request_id <> 0) THEN
978 LOOP
979 FND_FILE.PUT_LINE(FND_FILE.LOG,
980 'Start Time of the spawned Process ' ||
981 l_conc_prog_name || ' =>'||to_char (sysdate, 'dd/mon/yyyy :HH:MI:SS'));
982 l_bool := FND_CONCURRENT.wait_for_request(
983 request_id =>l_submit_request_id,
984 interval =>30,
985 max_wait =>144000,
986 phase =>uphase,
987 status =>ustatus,
988 dev_phase =>dphase,
989 dev_status =>dstatus,
990 message =>message);
991
992 IF dphase = 'COMPLETE'
993 --and dstatus = 'NORMAL' --the possible
994 --values are NORMAL/ERROR/WARNING/CANCELLED/TERMINATED
995 THEN
996 FND_FILE.PUT_LINE(FND_FILE.LOG,
997 'End Time of the spawned Process ' ||
998 l_conc_prog_name || ' =>'||to_char (sysdate, 'dd/mon/yyyy :HH:MI:SS'));
999 EXIT;
1000 END If; --dphase
1001
1002 END LOOP;
1003 END IF; -- if l_submit
1004 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_SCORE: scoreObjects: Launched cp '
1005 || l_submit_request_id || ' successfully');
1006 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Operating Unit: ' || nvl(mo_global.get_ou_name(mo_global.get_current_org_id), 'All')); --Added OU Name for MOAC
1007 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1008 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: process spawned '
1009 || l_submit_request_id);
1010 END IF;
1011
1012 end if; --if conc_process is not 'X'
1013 Exception
1014 WHEN NO_DATA_FOUND THEN
1015
1016 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1017 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: no process spawned');
1018 END IF;
1019 NULL;
1020 End;
1021 --
1022 -- End of API body
1023 --
1024
1025 -- Standard check for p_commit
1026 IF FND_API.to_Boolean(p_commit)
1027 THEN
1028 COMMIT WORK;
1029 END IF;
1030
1031 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1032 IEX_DEBUG_PUB.logMessage('scoreObjects: ' || 'PUB: ' || l_api_name || ' end');
1033 IEX_DEBUG_PUB.logMessage('scoreObjects: ' || 'End time:'|| TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1034 END IF;
1035
1036 FND_MSG_PUB.Count_And_Get
1037 (p_count => x_msg_count,
1038 p_data => x_msg_data);
1039
1040 EXCEPTION
1041 WHEN FND_API.G_EXC_ERROR THEN
1042 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1043 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: Expected Error ' || sqlerrm);
1044 END IF;
1045 RAISE FND_API.G_EXC_ERROR;
1046 ROLLBACK TO scoreObjects_PVT;
1047
1048 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1049 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1050 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: UnExpected Error ' || sqlerrm);
1051 END IF;
1052 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1053 ROLLBACK TO scoreObjects_PVT;
1054
1055 --START jsanju 10/19/05 for bug 3549051, pass the exception to the score_concur procedure
1056
1057 WHEN IEX_UNIVERSE_SIZE_ZERO_ERROR THEN
1058 ROLLBACK TO scoreObjects_PVT;
1059 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1060 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1061 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: iex universe size zero Error ' || x_msg_data);
1062 END IF;
1063
1064 RAISE IEX_UNIVERSE_SIZE_ZERO_ERROR;
1065
1066 WHEN OTHERS THEN
1067 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1068 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: Other Error ' || sqlerrm);
1069 END IF;
1070 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1071 ROLLBACK TO scoreObjects_PVT;
1072
1073 END scoreObjects;
1074
1075 /*
1076 || Overview: score a single object given it's ID, it's Type, and it's Scoring Engine
1077 ||
1078 || Parameter: p_score_id => scoring engine ID
1079 ||
1080 || Source Tables: IEX_SCORES, IEX_SCORE_COMPONENTS_VL, IEX_SCORE_COMP_TYPES, IEX_SCORE_COMP_DET,
1081 || IEX_OBJECT_FILTERS
1082 ||
1083 || Target Tables:
1084 ||
1085 || Creation date: 01/22/02 3:14:PM
1086 ||
1087 || Major Modifications: when who what
1088 || 01/22/02 raverma created
1089 */
1090 function scoreObject(p_commit IN VARCHAR2,
1091 P_OBJECT_ID IN NUMBER,
1092 P_OBJECT_TYPE IN VARCHAR2,
1093 P_SCORE_ID IN NUMBER) RETURN NUMBER
1094
1095 IS
1096 --vPLSQL varchar2(5000);
1097 l_universe IEX_FILTER_PUB.UNIVERSE_IDS; -- for TestUniverse / testGetScores
1098 l_components_tbl IEX_SCORE_NEW_PVT.SCORE_ENG_COMP_TBL; -- for testGetComponents / testGetScores
1099
1100 l_scores_tbl IEX_SCORE_NEW_PVT.SCORES_TBL; -- fore testGetScores
1101 l_score_id number ;
1102 l_object_type varchar2(25) ;
1103 b_valid boolean;
1104
1105 BEGIN
1106
1107 l_score_id := p_score_id;
1108 l_object_type := p_object_type;
1109
1110 b_valid := validateObjectID (p_object_id => p_object_id,
1111 p_object_type => p_object_type);
1112
1113 if not b_valid then
1114 FND_MESSAGE.Set_Name('IEX', 'IEX_INVALID_SCORING_OBJECT');
1115 FND_MSG_PUB.Add;
1116 return -1;
1117 end if;
1118
1119 b_valid := iex_score_new_pvt.checkObject_Compatibility(p_score_id => l_score_id ,
1120 p_object_type => l_object_type);
1121 if not b_Valid then
1122 FND_MESSAGE.Set_Name('IEX', 'IEX_INVALID_SCORING_ENGINE');
1123 FND_MSG_PUB.Add;
1124 return -1;
1125 end if;
1126
1127 IEX_SCORE_NEW_PVT.getCOMPONENTS(p_score_id => l_score_id,
1128 x_score_comp_tbl => l_components_tbl);
1129
1130 -- in case of singular object scoring we can ignore universe?
1131 l_universe(1) := p_object_id;
1132
1133 --- Begin - Andre Araujo - 11/02/2004 - Changed storage method, this storage desgin blows up at 414526 records - TAR 4040621.994
1134
1135 l_scores_tbl(1) := get1Score( l_components_tbl, p_object_id );
1136
1137 return l_scores_tbl(1);
1138 -- iex_score_new_pvt.getScores(p_score_comp_tbl => l_components_tbl,
1139 -- t_object_ids => l_universe,
1140 -- x_scores_tbl => l_scores_tbl);
1141 --
1142 -- if (l_scores_tbl is not null) and (l_scores_tbl.count > 0) then
1143 -- IF FND_API.to_Boolean(p_commit)
1144 -- THEN
1145 -- insert into iex_score_histories(SCORE_HISTORY_ID
1146 -- ,SCORE_OBJECT_ID
1147 -- ,SCORE_OBJECT_CODE
1148 -- ,OBJECT_VERSION_NUMBER
1149 -- ,LAST_UPDATE_DATE
1150 -- ,LAST_UPDATED_BY
1151 -- ,LAST_UPDATE_LOGIN
1152 -- ,CREATION_DATE
1153 -- ,CREATED_BY
1154 -- ,SCORE_VALUE
1155 -- ,SCORE_ID
1156 -- ,REQUEST_ID)
1157 -- values(IEX_SCORE_HISTORIES_S.nextval
1158 -- ,l_universe(1)
1159 -- ,l_object_type
1160 -- ,1
1161 -- ,sysdate
1162 -- ,FND_GLOBAL.USER_ID
1163 -- ,FND_GLOBAL.USER_ID
1164 -- ,sysdate
1165 -- ,FND_GLOBAL.USER_ID
1166 -- ,l_scores_tbl(1)
1167 -- ,p_score_id
1168 -- ,nvl(FND_GLOBAL.Conc_REQUEST_ID,0));
1169 --
1170 -- END IF;
1171 --
1172 -- return l_scores_tbl(1);
1173 -- else
1174 -- return -1;
1175 -- end if;
1176 --
1177 --- End - Andre Araujo - 11/02/2004 - Changed storage method, this storage desgin blows up at 414526 records - TAR 4040621.994
1178 --
1179 END scoreObject;
1180
1181 /* this will be called by the concurrent program to score customers
1182 */
1183 Procedure Score_Concur(ERRBUF OUT NOCOPY VARCHAR2,
1184 RETCODE OUT NOCOPY VARCHAR2,
1185 P_ORG_ID IN NUMBER, --Added for MOAC
1186 P_SCORE_ID1 IN NUMBER,
1187 P_Score_ID2 IN NUMBER,
1188 P_Score_ID3 IN NUMBER,
1189 P_Score_ID4 IN NUMBER,
1190 P_Score_ID5 IN NUMBER)
1191 IS
1192
1193 l_return_status VARCHAR2(10);
1194 l_msg_data VARCHAR2(32767);
1195 l_msg_count NUMBER;
1196
1197 -- bug 6128024
1198 l_pf_name varchar2(100);
1199 l_pf_value varchar2(50) := nvl(FND_PROFILE.VALUE('XLA_MO_SECURITY_PROFILE_LEVEL'),'');
1200
1201 type score_ids is table of number index by binary_integer;
1202 l_num_score_engines score_ids;
1203
1204 -- START -jsanju 10/19/05 , set concurrent status to 'WARNING' if universe size exception occurs for bug 3549051
1205 request_status BOOLEAN;
1206 -- END -jsanju 10/19/05 , set concurrent status to 'WARNING' if universe size exception occurs for bug 3549051
1207
1208 BEGIN
1209
1210 RETCODE := 0;
1211 l_num_score_engines(1) := p_score_id1;
1212 l_num_score_engines(2) := p_score_id2;
1213 l_num_score_engines(3) := p_score_id3;
1214 l_num_score_engines(4) := p_score_id4;
1215 l_num_score_engines(5) := p_score_id5;
1216
1217 ---start moac
1218 MO_GLOBAL.INIT('IEX');
1219 if p_org_id is null then
1220 mo_global.set_policy_context('M',NULL);
1221 else
1222 mo_global.set_policy_context('S',p_org_id);
1223 end if;
1224
1225 ---end moac
1226
1227 -- IF PG_DEBUG < 10 THEN
1228 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1229 IEX_DEBUG_PUB.logMessage('Score_Concur: ' || 'IEX_SCORE: scoreConcur: Scoring Harness Accessed');
1230 IEX_DEBUG_PUB.logMessage('Score_Concur: ' || 'IEX_SCORE: scoreConcur: Start time:'|| TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1231 END IF;
1232
1233 -- Begin bug 6128024
1234 begin
1235 select security_profile_name into l_pf_name from per_security_profiles
1236 where security_profile_id = l_pf_value;
1237 exception
1238 when others then l_pf_name := null;
1239 end;
1240
1241 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Profile Value MO: Default Operating Unit : ' || NVL(mo_global.get_ou_name(FND_PROFILE.VALUE('DEFAULT_ORG_ID')), ' '));
1242 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Profile Value MO: Operating Unit: ' || NVL(mo_global.get_ou_name(FND_PROFILE.VALUE('ORG_ID')), ' '));
1243 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Profile Value MO: Security Profile: ' || l_pf_name);
1244 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1245 -- End bug 6128024
1246
1247
1248 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch Size: ' || G_BATCH_SIZE);
1249 for x in 1..5 loop
1250 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Running Scoring Engine: ' || to_char(l_num_score_engines(x)));
1251 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
1252 if l_num_score_engines(x) is not null then
1253 BEGIN
1254 IEX_SCORE_NEW_PVT.scoreObjects(p_api_version => 1.0,
1255 p_init_msg_list => FND_API.G_TRUE,
1256 p_commit => FND_API.G_TRUE,
1257 x_return_status => l_return_status,
1258 x_msg_count => l_msg_count,
1259 x_msg_data => l_msg_data,
1260 p_score_id => l_num_score_engines(x));
1261 FND_FILE.PUT_LINE(FND_FILE.LOG,
1262 'Score Engine: ' || l_num_score_engines(x) ||
1263 ' Status: ' || l_return_status);
1264 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1265 RAISE FND_API.G_EXC_ERROR;
1266 end if;
1267
1268 EXCEPTION
1269
1270 -- note do not set retcode when error is expected
1271 WHEN FND_API.G_EXC_ERROR THEN
1272 RETCODE := -1;
1273 ERRBUF := l_msg_data;
1274 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1275 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreConcur: Expected Error in Score ' || sqlerrm);
1276 END IF;
1277 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR: ' || sqlerrm || ERRBUF);
1278
1279 -- START -jsanju 10/19/05 , set concurrent status to 'WARNING' if universe size exception occurs for bug 3549051
1280 WHEN IEX_UNIVERSE_SIZE_ZERO_ERROR THEN
1281 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1282 IEX_DEBUG_PUB.logMessage('IEX_SCORE: universe size is zero ' || l_msg_data);
1283 END IF;
1284 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Universe Size is Zero ');
1285 request_status := fnd_concurrent.set_completion_status('WARNING'
1286 , 'Universe size is zero');
1287
1288 -- END -jsanju 10/19/05 , set concurrent status to 'WARNING' if universe size exception occurs for bug 3549051
1289
1290 WHEN OTHERS THEN
1291 RETCODE := -1;
1292 ERRBUF := l_msg_data;
1293 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1294 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreConcur: Unexpected Error ' || sqlerrm);
1295 END IF;
1296 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR: ' || sqlerrm || ERRBUF);
1297 END;
1298
1299 end if;
1300 end loop;
1301
1302 -- IF PG_DEBUG < 10 THEN
1303 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1304 IEX_DEBUG_PUB.logMessage('Score_Concur: ' || 'Return status is ' || l_return_status);
1305 IEX_DEBUG_PUB.logMessage('Score_Concur: ' || 'IEX_SCORE: scoreConcur: End time:'|| TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1306 END IF;
1307 Exception
1308
1309 WHEN FND_API.G_EXC_ERROR THEN
1310 RETCODE := -1;
1311 ERRBUF := l_msg_data;
1312 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1313 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreConcur: Expected Error ' || sqlerrm);
1314 END IF;
1315 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR: ' || sqlerrm);
1316
1317 WHEN OTHERS THEN
1318 RETCODE := -1;
1319 ERRBUF := l_msg_data;
1320 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1321 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreConcur: Unexpected Error ' || sqlerrm);
1322 END IF;
1323 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR: ' || sqlerrm);
1324 END SCORE_CONCUR;
1325
1326 /*
1327 || Overview: delete rows from IEX_SCORE_HISTORIES to improve performance
1328 ||
1329 || Parameter: p_score_object_code => score_object_code to erase
1330 || p_from_date => remove from this date
1331 || p_to_Date => remove up to this date
1332 || p_request_id => remove this request
1333 || p_save_last_run => save the last run of the object type
1334 || all parameters are AND logic on the where clause
1335 ||
1336 || Source Tables:
1337 ||
1338 || Target Tables: IEX_SCORE_HISTORIES
1339 ||
1340 || Creation date: 01/28/03 3:14:PM
1341 ||
1342 || Major Modifications: when who what
1343 || 01/28/03 raverma created
1344 */
1345 Procedure eraseScores(ERRBUF OUT NOCOPY VARCHAR2,
1346 RETCODE OUT NOCOPY VARCHAR2,
1347 P_TRUNCATE IN VARCHAR2, -- fix a bug 5765878 to truncate table to perform better by Ehuh 02.19.2007
1348 P_SCORE_OBJECT_ID IN NUMBER ,
1349 P_SCORE_OBJECT_CODE IN VARCHAR2 ,
1350 -- begin bug 4504193 by ctlee 2005/07/26 - update from date to varchar2
1351 P_FROM_DATE IN varchar2 ,
1352 P_TO_DATE IN varchar2 ,
1353 -- end bug 4504193 by ctlee 2005/07/26 - update from date to varchar2
1354 P_REQUEST_ID IN NUMBER ,
1355 P_SAVE_LAST_RUN IN VARCHAR2,
1356 P_BATCH_SIZE IN NUMBER)
1357 IS
1358
1359 vPLSQL VARCHAR2(200);
1360 vPLSQL2 VARCHAR2(500);
1361 l_total NUMBER(38) ;
1362 l_Count NUMBER ;
1363 i NUMBER ;
1364 j NUMBER ;
1365 l_object_code VARCHAR2(50);
1366 Type refCur is Ref Cursor;
1367 sql_cur refCur;
1368 l_conditions IEX_UTILITIES.Condition_Tbl;
1369 l_msg_data VARCHAR2(1000);
1370 l_score_history_ids IEX_FILTER_PUB.UNIVERSE_IDS;
1371
1372 -- clchang updated for sql bind var 05/07/2003
1373 vStr1 VARCHAR2(100) ;
1374 vStr2 VARCHAR2(100) ;
1375 vSqlCur VARCHAR2(1000) ;
1376 -- end
1377
1378 -- Modified By Surya 11/18/2003 Bug 3221769
1379 v_del_sql Varchar2(1000) ;
1380 v_tot_objects Number := 0 ;
1381
1382 -- Andre Added
1383 vWhereClause VARCHAR2(1000) ;
1384 vSelectCount Varchar2(1000) ;
1385 vLoopCount Number;
1386
1387 --clchang 10/29/04 added to fix gscc
1388 l_save_last_run varchar2(10);
1389
1390 -- begin bug 4504193 by ctlee 2005/07/26
1391 v_from_date date;
1392 v_to_date date;
1393 -- end bug 4504193 by ctlee 2005/07/26
1394
1395 l_prod varchar2(04) := 'iex'; -- fix a bug 5765878 to truncate table to perform better by Ehuh 02.19.2007
1396
1397 BEGIN
1398
1399 --clchang 10/29/04 added to fix gscc
1400 -- and before P_SAVE_LAST_RUN has DEFAULT 'Y';
1401 l_save_last_run := p_save_last_run;
1402 if ( l_save_last_run is null) then
1403 l_save_last_run := 'Y';
1404 end if;
1405 -- no default values in declare
1406 l_total := 0;
1407 l_Count := 0;
1408 i := 0;
1409 j := 0;
1410 vStr1 := 'SELECT SCORE_HISTORY_ID ' ;
1411 vStr2 := ' FROM IEX_SCORE_HISTORIES ' ;
1412 v_del_sql := 'DELETE FROM IEX_SCORE_HISTORIES ' ;
1413 vWhereClause := '';
1414 vSelectCount := 'select count(1) from iex_score_histories ';
1415
1416
1417 -- IF PG_DEBUG < 10 THEN
1418 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1419 IEX_DEBUG_PUB.logMessage('IEX_SCORE: eraseScores');
1420 IEX_DEBUG_PUB.logMessage('IEX_SCORE: eraseScores: Start time:'|| TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1421 END IF;
1422
1423 /* build where clause */
1424 if P_SCORE_OBJECT_ID IS NOT NULL then
1425 FND_FILE.PUT_LINE(FND_FILE.LOG,P_SCORE_OBJECT_ID);
1426 i:= i + 1;
1427 l_conditions(i).Col_Name := 'SCORE_OBJECT_ID';
1428 l_conditions(i).Condition := '=';
1429 l_conditions(i).Value := P_SCORE_OBJECT_ID;
1430 end if;
1431 if P_SCORE_OBJECT_CODE IS NOT NULL then
1432 FND_FILE.PUT_LINE(FND_FILE.LOG,P_SCORE_OBJECT_CODE);
1433 i := i + 1;
1434 l_conditions(i).Col_Name := 'SCORE_OBJECT_CODE';
1435 l_conditions(i).Condition := '=';
1436 l_conditions(i).Value := '''' || P_SCORE_OBJECT_CODE || '''';
1437 end if;
1438 if P_FROM_DATE IS NOT NULL then
1439 FND_FILE.PUT_LINE(FND_FILE.LOG,TO_CHAR(P_FROM_DATE));
1440 i := i + 1;
1441 l_conditions(i).Col_Name := 'CREATION_DATE';
1442 l_conditions(i).Condition := '>';
1443 -- l_conditions(i).Value := '''' || to_char(P_FROM_DATE) || '''';
1444 -- begin bug 4504193 by ctlee 2005/07/26
1445 v_from_date := to_date(p_from_date, 'yyyy/mm/dd hh24:mi:ss');
1446 l_conditions(i).Value := '''' || to_char(v_FROM_DATE) || '''';
1447 -- end bug 4504193 by ctlee 2005/07/26
1448 end if;
1449 if P_TO_DATE IS NOT NULL then
1450 FND_FILE.PUT_LINE(FND_FILE.LOG,TO_CHAR(P_TO_DATE));
1451 i := i + 1;
1452 l_conditions(i).Col_Name := 'CREATION_DATE';
1453 l_conditions(i).Condition := '<=';
1454 -- l_conditions(i).Value := '''' || to_char(P_TO_DATE) || '''';
1455 -- begin bug 4504193 by ctlee 2005/07/26
1456 v_to_date := to_date(p_to_date, 'yyyy/mm/dd hh24:mi:ss');
1457 l_conditions(i).Value := '''' || to_char(v_TO_DATE) || '''';
1458 -- end bug 4504193 by ctlee 2005/07/26
1459 end if;
1460 if P_REQUEST_ID IS NOT NULL then
1461 FND_FILE.PUT_LINE(FND_FILE.LOG,P_REQUEST_ID);
1462 i := i + 1;
1463 l_conditions(i).Col_Name := 'REQUEST_ID';
1464 l_conditions(i).Condition := '=';
1465 l_conditions(i).Value := P_REQUEST_ID;
1466 end if;
1467 --if P_SAVE_LAST_RUN <> 'N' then
1468 if L_SAVE_LAST_RUN <> 'N' then
1469 FND_FILE.PUT_LINE(FND_FILE.LOG,L_SAVE_LAST_RUN);
1470 -- Begin - Andre Araujo - 03/02/2005 - BUG#4198055 - Did not increase the count, causes not found exception
1471 i := i + 1;
1472 l_conditions(i).Col_Name := 'trunc(CREATION_DATE)';
1473 l_conditions(i).Condition := '<>';
1474 l_conditions(i).Value := '(SELECT trunc(MAX(creation_date)) FROM iex_Score_histories)'; -- Andre Fixed here so we use date only
1475 --l_conditions(i).Col_Name := 'CREATION_DATE';
1476 --l_conditions(i).Condition := '<>';
1477 --l_conditions(i).Value := '(SELECT MAX(creation_date) FROM iex_Score_histories)';
1478 -- End - Andre Araujo - 03/02/2005 - BUG#4198055 - Did not increase the count, causes not found exception
1479 end if;
1480
1481 -- Added by Surya
1482 if l_conditions.COUNT >= 1 then
1483
1484 vPLSQL2 := IEX_UTILITIES.buildWhereClause(l_conditions);
1485
1486 If NVL(p_truncate,'Y') = 'N' then -- fix a bug 5765878 to truncate table to perform better by Ehuh 2.19.2007
1487 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Delete Filter Applied => '|| vPLSQL2);
1488 end if;
1489
1490 v_del_sql := v_del_sql || VPLSQL2 ;
1491 End If ;
1492
1493 -- Andre Added
1494 vWhereClause := VPLSQL2;
1495
1496 -- IF PG_DEBUG < 10 THEN
1497 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1498 IEX_DEBUG_PUB.logMessage(vPLSQL2);
1499 END IF;
1500
1501 If NVL(p_truncate,'Y') = 'N' then -- fix a bug 5765878 to truncate table to perform better by Ehuh 2.19.2007
1502 FND_FILE.PUT_LINE(FND_FILE.LOG, 'SELECT SCORE_HISTORY_ID ' ||
1503 ' FROM IEX_SCORE_HISTORIES ' ||
1504 vPLSQL2);
1505 End if;
1506
1507 vPLSQL := ' SELECT Count(1), Score_object_code ' ||
1508 ' FROM IEX_SCORE_HISTORIES ' ||
1509 'GROUP BY SCORE_OBJECT_CODE ';
1510
1511 FND_FILE.PUT_LINE(FND_FILE.LOG, '-------------------------------------------------');
1512 FND_FILE.PUT_LINE(FND_FILE.LOG, ' BEFORE PURGE');
1513 FND_FILE.PUT_LINE(FND_FILE.LOG, '-------------------------------------------------');
1514 open sql_cur for
1515 vPLSQL;
1516 LOOP
1517 l_count := 0;
1518 l_object_code := null;
1519 i := i + 1;
1520 fetch sql_cur into l_count, l_object_code;
1521 exit when sql_cur%NOTFOUND;
1522 FND_FILE.PUT_LINE(FND_FILE.LOG, 'OBJECT_CODE: ' || l_object_code || ' OBJECTS: ' || l_count);
1523 v_tot_objects := v_tot_objects + l_count ;
1524 end loop;
1525 close sql_cur;
1526
1527 FND_FILE.PUT_LINE(FND_FILE.LOG, 'TOTAL OBJECTS IN IEX_SCORE_HISTORIES BEFORE PURGE: ' || v_tot_objects);
1528
1529 /* do erasing here */
1530 /* Removed by Andre 06/18/2004, we will need to delete in chunks
1531 EXECUTE IMMEDIATE v_del_sql ;
1532 */
1533
1534 If NVL(p_truncate,'Y') <> 'N' then -- fix a bug 5765878 to truncate table to perform better by Ehuh 2.19.2007
1535 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Truncating Table => '|| p_truncate);
1536 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
1537 EXECUTE IMMEDIATE v_del_sql;
1538 Else -- fix a bug 5765878 to truncate table to perform better by Ehuh 2.19.2007
1539
1540 if l_conditions.COUNT >= 1 then
1541 v_del_sql := v_del_sql || ' AND rownum >= 0 and rownum < ' || p_batch_size;
1542 else
1543 v_del_sql := v_del_sql || ' WHERE rownum >= 0 and rownum < ' || p_batch_size;
1544 end if;
1545
1546
1547 i := 0;
1548 vSelectCount := vSelectCount || vWhereClause;
1549 open sql_cur for vSelectCount;
1550 fetch sql_cur into vLoopCount;
1551 close sql_cur;
1552
1553 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Final delete statement => '|| v_del_sql);
1554 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleting => '|| vLoopCount || ' Records');
1555
1556 loop
1557 EXECUTE IMMEDIATE v_del_sql;
1558 FND_FILE.PUT_LINE(FND_FILE.LOG, 'i => '|| i );
1559 commit;
1560
1561 i := i + p_batch_size;
1562 exit when i > vLoopCount;
1563 end loop;
1564 -- If we miss any because of the loop count...
1565 EXECUTE IMMEDIATE v_del_sql;
1566 commit;
1567
1568 End if; -- fix a bug 5765878 to truncate table to perform better by Ehuh 2.19.2007
1569
1570 -- End changes, Andre 06/18/2004
1571
1572 FND_FILE.PUT_LINE(FND_FILE.LOG, '-------------------------------------------------');
1573 FND_FILE.PUT_LINE(FND_FILE.LOG, ' AFTER PURGE');
1574 FND_FILE.PUT_LINE(FND_FILE.LOG, '-------------------------------------------------');
1575
1576 l_total := 0;
1577 open sql_cur for
1578 vPLSQL;
1579 LOOP
1580 l_count := 0;
1581 l_object_code := null;
1582 i := i + 1;
1583 fetch sql_cur into l_count, l_object_code;
1584 exit when sql_cur%NOTFOUND;
1585 FND_FILE.PUT_LINE(FND_FILE.LOG, 'OBJECT_CODE: ' || l_object_code || ' OBJECTS: ' || l_count);
1586 end loop;
1587 close sql_cur;
1588
1589 Begin
1590 Select Count(1) into l_total
1591 From IEX_SCORE_HISTORIES;
1592 Exception When NO_DATA_FOUND Then
1593 l_total := 0;
1594 END;
1595 FND_FILE.PUT_LINE(FND_FILE.LOG, 'TOTAL OBJECTS IN IEX_SCORE_HISTORIES AFTER PURGE: ' || l_total);
1596
1597 -- IF PG_DEBUG < 10 THEN
1598 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1599 IEX_DEBUG_PUB.logMessage('IEX_SCORE: eraseScores: end time:'|| TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1600 END IF;
1601 Exception
1602 WHEN FND_API.G_EXC_ERROR THEN
1603 RETCODE := -1;
1604 ERRBUF := l_msg_data;
1605 -- IF PG_DEBUG < 10 THEN
1606 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1607 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreConcur: Expected Error ' || sqlerrm );
1608 END IF;
1609 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR: ' || sqlerrm);
1610
1611 WHEN OTHERS THEN
1612 RETCODE := -1;
1613 ERRBUF := l_msg_data;
1614 -- IF PG_DEBUG < 10 THEN
1615 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1616 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreConcur: Unexpected Error ' || sqlerrm);
1617 END IF;
1618 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR: ' || sqlerrm);
1619 --dbms_output.put_line(sqlerrm);
1620
1621 END eraseScores;
1622
1623 /*
1624 || Overview: Returns an array of score values for a given objectID/Type
1625 ||
1626 || Parameter: p_object_id object scored in IEX_SCORE_HISTORIES required
1627 || p_object_code object_code in IEX_SCORE_HISTORIES required
1628 || p_from_Date begin date restriction optional
1629 || p_to_date end date restriction optional
1630 || p_scoreID scoreEngineID used to score object optional
1631 ||
1632 || Return value: SCORE_HISTORY_ID -> PK to IEX_SCORE_HISTORIES
1633 || SCORE_ID -> scoreEngine used to calculate score
1634 || SCORE_VALUE -> score of object
1635 || CREATION_DATE -> date object was scored
1636 ||
1637 || Source Tables: IEX_SCORE_HISTORIES
1638 ||
1639 || Target Tables: NA
1640 ||
1641 || Creation date: 04/22/2003 4:03PM
1642 ||
1643 || Major Modifications: when who what
1644 || 04/22/2003 4:03PM raverma created
1645 */
1646 function getScoreHistory (p_score_object_id IN NUMBER,
1647 p_score_object_code IN VARCHAR2,
1648 p_from_date IN DATE ,
1649 p_to_date IN DATE ,
1650 p_score_id IN NUMBER ) return IEX_SCORE_NEW_PVT.SCORE_HISTORY_TBL
1651 IS
1652
1653 l_score_hist_tbl IEX_SCORE_NEW_PVT.SCORE_HISTORY_TBL;
1654 vPLSQL VARCHAR2(200);
1655 l_total NUMBER(38) ;
1656 i NUMBER ;
1657 j NUMBER ;
1658 l_object_code VARCHAR2(50);
1659 Type refCur is Ref Cursor;
1660 sql_cur refCur;
1661 l_conditions IEX_UTILITIES.Condition_Tbl;
1662
1663 --clchang updated for sql bind var 05/07/2003
1664 vstr1 varchar2(100) ;
1665 vstr2 varchar2(100) ;
1666 vstr3 varchar2(100) ;
1667 vstr4 varchar2(100) ;
1668 vstr5 varchar2(100) ;
1669 vSqlCur varchar2(1000);
1670
1671 BEGIN
1672 -- IF PG_DEBUG < 10 THEN
1673 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1674 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreHistory: Start time:'|| TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1675 END IF;
1676
1677 --clchang updated 10/29/04 no default values in declare
1678 l_total := 0;
1679 i := 0;
1680 j := 0;
1681 vstr1 := 'SELECT SCORE_HISTORY_ID, ';
1682 vstr2 := ' SCORE_ID, ';
1683 vstr3 := ' SCORE_VALUE, ';
1684 vstr4 := ' CREATION_DATE ';
1685 vstr5 := ' FROM IEX_SCORE_HISTORIES ';
1686
1687 /* build where clause */
1688 i:= i + 1;
1689 l_conditions(i).Col_Name := 'SCORE_OBJECT_ID';
1690 l_conditions(i).Condition := '=';
1691 l_conditions(i).Value := P_SCORE_OBJECT_ID;
1692
1693 i := i + 1;
1694 l_conditions(i).Col_Name := 'SCORE_OBJECT_CODE';
1695 l_conditions(i).Condition := '=';
1696 l_conditions(i).Value := '''' || P_SCORE_OBJECT_CODE || '''';
1697
1698 if P_FROM_DATE IS NOT NULL then
1699 i := i + 1;
1700 l_conditions(i).Col_Name := 'CREATION_DATE';
1701 l_conditions(i).Condition := '>';
1702 l_conditions(i).Value := '''' || to_char(P_FROM_DATE) || '''';
1703 end if;
1704 if P_TO_DATE IS NOT NULL then
1705 i := i + 1;
1706 l_conditions(i).Col_Name := 'CREATION_DATE';
1707 l_conditions(i).Condition := '<=';
1708 l_conditions(i).Value := '''' || to_char(P_TO_DATE) || '''';
1709 end if;
1710 if P_SCORE_ID IS NOT NULL then
1711 i := i + 1;
1712 l_conditions(i).Col_Name := 'SCORE_ID';
1713 l_conditions(i).Condition := '=';
1714 l_conditions(i).Value := P_SCORE_ID;
1715 end if;
1716 vPLSQL := IEX_UTILITIES.buildWhereClause(l_conditions);
1717
1718 -- IF PG_DEBUG < 10 THEN
1719 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1720 IEX_DEBUG_PUB.logMessage(vPLSQL);
1721 END IF;
1722 --dbms_output.put_line(vPLSQL);
1723
1724 /* execute history query and fetch */
1725 -- clchang updated for sql bind var 05/07/2003
1726 vSqlCur := vstr1 || vstr2 || vstr3 || vstr4 || vstr5 || vPLSQL;
1727 open sql_cur for vSqlCur;
1728 /*
1729 open sql_cur for
1730 'SELECT SCORE_HISTORY_ID, ' ||
1731 ' SCORE_ID, ' ||
1732 ' SCORE_VALUE, '||
1733 ' CREATION_DATE ' ||
1734 ' FROM IEX_SCORE_HISTORIES ' ||
1735 vPLSQL;
1736 */
1737
1738 LOOP
1739 j := j + 1;
1740 fetch sql_cur into l_score_hist_tbl(j).Score_history_id,
1741 l_score_hist_tbl(j).Score_id,
1742 l_score_hist_tbl(j).score_value,
1743 l_score_hist_tbl(j).creation_date;
1744 exit when sql_cur%NOTFOUND;
1745 end loop;
1746 close sql_cur;
1747
1748 return l_score_hist_tbl;
1749
1750 -- IF PG_DEBUG < 10 THEN
1751 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1752 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreHistory: end time:'|| TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1753 END IF;
1754
1755 Exception
1756 WHEN FND_API.G_EXC_ERROR THEN
1757 -- IF PG_DEBUG < 10 THEN
1758 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1759 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreHistory: Expected Error ' || sqlerrm );
1760 END IF;
1761 RETURN l_score_hist_tbl;
1762
1763 WHEN OTHERS THEN
1764 -- IF PG_DEBUG < 10 THEN
1765 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1766 IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScoreHistory: UnExpected Error ' || sqlerrm );
1767 END IF;
1768 RETURN l_score_hist_tbl;
1769
1770 END getScoreHistory;
1771 --
1772 --- Begin - Andre Araujo - 11/02/2004 - New storage mode, this one respects the commit size - TAR 4040621.994
1773 --
1774 /*
1775 || Overview: Stores the score history given a table of records
1776 ||
1777 || Parameter:
1778 || p_scoreID scoreEngineID used to score object optional
1779 ||
1780 || Return value:
1781 ||
1782 || Source Tables: None
1783 ||
1784 || Target Tables: IEX_SCORE_HISTORIES
1785 ||
1786 || Creation date: 11/02/2004
1787 ||
1788 || Major Modifications: when who what
1789 ||
1790 */
1791 procedure storeScoreHistory ( p_score_id IN NUMBER default null,
1792 p_objects_tbl IN IEX_SCORE_NEW_PVT.SCORE_OBJECTS_TBL,
1793 p_scores_tbl IN IEX_SCORE_NEW_PVT.NEW_SCORES_TBL)
1794 IS
1795
1796 i NUMBER := 1;
1797 n NUMBER := 1;
1798
1799 l_user NUMBER;
1800 l_program NUMBER;
1801 l_prog_appl NUMBER;
1802 l_request NUMBER;
1803 l_object_type VARCHAR2(25);
1804
1805
1806 BEGIN
1807 IF PG_DEBUG < 10 THEN
1808 IEX_DEBUG_PUB.logMessage('IEX_SCORE: storeScoreHistory: Insert records!' );
1809 END IF;
1810
1811 l_user := FND_GLOBAL.USER_ID;
1812 l_request := nvl(FND_GLOBAL.Conc_REQUEST_ID,0);
1813 l_program := FND_GLOBAL.CONC_PROGRAM_ID;
1814 l_prog_appl := FND_GLOBAL.PROG_APPL_ID;
1815
1816 IF p_scores_tbl.count > 0 THEN -- Do we have records to store?
1817 IF PG_DEBUG < 10 THEN
1818 IEX_DEBUG_PUB.logMessage('IEX_SCORE: storeScoreHistory: p_score_id= ' || p_score_id || ' ; Number of scores= ' || p_scores_tbl.count);
1819 END IF;
1820
1821 -- initial variables needed
1822 select jtf_object_code into l_object_type
1823 from iex_scores
1824 where score_id = p_score_id;
1825
1826 FORALL n in i..i + p_scores_tbl.count - 1
1827 insert into iex_score_histories(SCORE_HISTORY_ID
1828 ,SCORE_OBJECT_ID
1829 ,SCORE_OBJECT_CODE
1830 ,OBJECT_VERSION_NUMBER
1831 ,LAST_UPDATE_DATE
1832 ,LAST_UPDATED_BY
1833 ,LAST_UPDATE_LOGIN
1834 ,CREATION_DATE
1835 ,CREATED_BY
1836 ,SCORE_VALUE
1837 ,SCORE_ID
1838 ,REQUEST_ID
1839 ,PROGRAM_ID
1840 ,PROGRAM_APPLICATION_ID
1841 ,PROGRAM_UPDATE_DATE)
1842 values(IEX_SCORE_HISTORIES_S.nextval
1843 ,p_objects_tbl(n)
1844 ,l_object_type
1845 ,1
1846 ,sysdate
1847 ,l_user
1848 ,l_user
1849 ,sysdate
1850 ,l_user
1851 ,p_scores_tbl(n)
1852 ,p_score_id
1853 ,l_request
1854 ,l_program
1855 ,l_prog_appl
1856 ,sysdate);
1857
1858 IF PG_DEBUG < 10 THEN
1859 IEX_DEBUG_PUB.logMessage('IEX_SCORE: storeScoreHistory: Commit records!' );
1860 END IF;
1861
1862 commit;
1863 END IF; -- p_scores_tbl.count > 0
1864
1865 IF PG_DEBUG < 10 THEN
1866 IEX_DEBUG_PUB.logMessage('IEX_SCORE: storeScoreHistory: Return' );
1867 END IF;
1868
1869 Exception
1870 WHEN FND_API.G_EXC_ERROR THEN
1871 IF PG_DEBUG < 10 THEN
1872 IEX_DEBUG_PUB.logMessage('IEX_SCORE: storeScoreHistory: Expected Error ' || sqlerrm );
1873 END IF;
1874 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR: IEX_SCORE: storeScoreHistory:' || sqlerrm);
1875
1876 WHEN OTHERS THEN
1877 IF PG_DEBUG < 10 THEN
1878 IEX_DEBUG_PUB.logMessage('IEX_SCORE: storeScoreHistory: UnExpected Error ' || sqlerrm );
1879 END IF;
1880 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR: IEX_SCORE: storeScoreHistory: UnExpected Error' || sqlerrm);
1881
1882 END storeScoreHistory;
1883
1884 /*
1885 || Overview: Stores the score history given a table of records
1886 ||
1887 || Parameter:
1888 || p_scoreID scoreEngineID used to score object optional
1889 ||
1890 || Return value:
1891 ||
1892 || Source Tables: None
1893 ||
1894 || Target Tables: IEX_SCORE_HISTORIES
1895 ||
1896 || Creation date: 11/02/2004
1897 ||
1898 || Major Modifications: when who what
1899 ||
1900 */
1901 procedure storeDelBuffers ( p_score_id IN NUMBER default null,
1902 p_objects_tbl IN IEX_SCORE_NEW_PVT.SCORE_OBJECTS_TBL,
1903 p_scores_tbl IN IEX_SCORE_NEW_PVT.NEW_SCORES_TBL,
1904 p_bridge IN NUMBER default null)
1905 IS
1906 i NUMBER := 1;
1907 n NUMBER := 1;
1908
1909 l_user NUMBER;
1910 l_program NUMBER;
1911 l_prog_appl NUMBER;
1912 l_request NUMBER;
1913 l_object_type VARCHAR2(25);
1914 l_bridge NUMBER ;
1915
1916
1917
1918 BEGIN
1919 IF PG_DEBUG < 10 THEN
1920 IEX_DEBUG_PUB.logMessage('IEX_SCORE: storeDelBuffers: Insert records!' );
1921 END IF;
1922
1923 l_bridge := p_bridge;
1924 l_user := FND_GLOBAL.USER_ID;
1925 l_request := nvl(FND_GLOBAL.Conc_REQUEST_ID,0);
1926 l_program := FND_GLOBAL.CONC_PROGRAM_ID;
1927 l_prog_appl := FND_GLOBAL.PROG_APPL_ID;
1928
1929 IF p_scores_tbl.count > 0 THEN -- Do we have records to store?
1930 IF PG_DEBUG < 10 THEN
1931 IEX_DEBUG_PUB.logMessage('IEX_SCORE: storeDelBuffers: p_score_id= ' || p_score_id || ' ; Number of scores= ' || p_scores_tbl.count);
1932 END IF;
1933
1934 -- initial variables needed
1935 select jtf_object_code into l_object_type
1936 from iex_scores
1937 where score_id = p_score_id;
1938
1939
1940 FORALL n in i..i + p_scores_tbl.count - 1
1941 insert into IEX_DEL_BUFFERS(DEL_BUFFER_ID
1942 ,SCORE_OBJECT_ID
1943 ,SCORE_OBJECT_CODE
1944 ,OBJECT_VERSION_NUMBER
1948 ,CREATION_DATE
1945 ,LAST_UPDATE_DATE
1946 ,LAST_UPDATED_BY
1947 ,LAST_UPDATE_LOGIN
1949 ,CREATED_BY
1950 ,SCORE_VALUE
1951 ,SCORE_ID
1952 ,REQUEST_ID
1953 ,PROGRAM_ID
1954 ,PROGRAM_APPLICATION_ID
1955 ,PROGRAM_UPDATE_DATE)
1956 values(IEX_DEL_BUFFERS_S.nextval
1957 ,p_objects_tbl(n)
1958 ,l_object_type
1959 ,1
1960 ,sysdate
1961 ,l_user
1962 ,l_user
1963 ,sysdate
1964 ,l_user
1965 ,p_scores_tbl(n)
1966 ,p_score_id
1967 ,nvl(l_bridge,0)
1968 ,l_program
1969 ,l_prog_appl
1970 ,sysdate);
1971
1972
1973 IF PG_DEBUG < 10 THEN
1974 IEX_DEBUG_PUB.logMessage('IEX_SCORE: storeDelBuffers: Commit records!' );
1975 END IF;
1976
1977 commit;
1978 END IF; -- p_scores_tbl.count > 0
1979
1980 IF PG_DEBUG < 10 THEN
1981 IEX_DEBUG_PUB.logMessage('IEX_SCORE: storeDelBuffers: Return' );
1982 END IF;
1983
1984 Exception
1985 WHEN FND_API.G_EXC_ERROR THEN
1986 IF PG_DEBUG < 10 THEN
1987 IEX_DEBUG_PUB.logMessage('IEX_SCORE: storeDelBuffers: Expected Error ' || sqlerrm );
1988 END IF;
1989 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR:IEX_SCORE: storeDelBuffers: Expected Error ' || sqlerrm);
1990
1991 WHEN OTHERS THEN
1992 IF PG_DEBUG < 10 THEN
1993 IEX_DEBUG_PUB.logMessage('IEX_SCORE: storeDelBuffers: UnExpected Error ' || sqlerrm );
1994 END IF;
1995 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR:IEX_SCORE: storeDelBuffers: UnExpected Error ' || sqlerrm);
1996
1997 END storeDelBuffers;
1998
1999
2000 /*
2001 || Overview: Scores 1 item and returns the value
2002 ||
2003 || Parameter:
2004 || p_scoreID scoreEngineID used to score object optional
2005 ||
2006 || Return value:
2007 ||
2008 || Source Tables: None
2009 ||
2010 || Target Tables: None
2011 ||
2012 || Creation date: 11/03/2004
2013 ||
2014 || Major Modifications: when who what
2015 ||
2016 */
2017 function get1Score ( p_score_comp_tbl IN IEX_SCORE_NEW_PVT.SCORE_ENG_COMP_TBL, p_object_id IN NUMBER ) return NUMBER
2018 IS
2019
2020 l_score_component_id NUMBER;
2021 l_score_component_sql VARCHAR2(2500);
2022 l_execute_style VARCHAR2(1); -- are we using select or function call
2023 l_count2 number := 0;
2024 l_component_score number := 0;
2025 type COMPONENT_RANGE is table of NUMBER
2026 index by binary_integer;
2027 l_component_range_tbl COMPONENT_RANGE;
2028 i NUMBER;
2029 l_raw_score number := 0;
2030 l_running_score number := 0;
2031 vSql varchar2(2500);
2032 l_value VARCHAR2(2000);
2033 l_new_value VARCHAR2(2000);
2034 l_weight_required VARCHAR2(3);
2035 l_low varchar2(2000);
2036 l_high varchar2(2000);
2037 l_rule varchar2(20);
2038
2039
2040 BEGIN
2041 IF PG_DEBUG < 10 THEN
2042 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: Begin' );
2043 END IF;
2044
2045 -- copied code
2046 /* 3. for each component, execute SQL and get value */
2047 FOR l_count2 IN 1..p_score_comp_tbl.count LOOP
2048 l_score_component_id := p_score_comp_tbl(l_count2).score_component_id;
2049 l_score_component_sql := p_score_comp_tbl(l_count2).SCORE_COMP_VALUE;
2050 l_execute_style := p_score_comp_tbl(l_count2).function_flag;
2051 -- initialize this to the minimum for any given component
2052 --l_raw_score := IEX_SCORE_PVT.G_MIN_SCORE;
2053
2054 if PG_DEBUG <= 5 then
2055 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: executing Component ' || l_count2 || ' CompID is: ' || l_score_component_id);
2056 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: Execute Stmt: ' || l_score_component_sql || ' Execute Style: ' || l_execute_style);
2057 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: Bind Variable: ' || p_object_id);
2058 end if;
2059
2060 /* executing dynamic sql for component */
2061 --if l_score_component_sql is not null then
2062 BEGIN
2063
2064 -- Execute SQL statement only when function syntax is not found
2065 if l_execute_style = 'N' then
2066 -- simple select statement
2067 EXECUTE IMMEDIATE l_score_component_sql
2068 INTO l_component_score
2069 USING p_object_id;
2070 else
2071 -- function to execute
2072 -- to do - pass the score component id for Function calls only
2073 EXECUTE IMMEDIATE l_score_component_sql
2074 USING in p_object_id,
2075 in l_score_component_id,
2076 out l_component_score;
2077 end if;
2078
2079 EXCEPTION
2080
2081 -- assign the "Lowest" Detail for the component
2082 -- in order to do this we must know what is "high" and "low" range of component
2083
2084 WHEN OTHERS THEN
2085 -- Begin - Andre Araujo - 12/17/2004 - If the detail is not defined this throws a NO DATA FOUND
2086 Begin
2087 -- End - Andre Araujo - 12/17/2004 - If the detail is not defined this throws a NO DATA FOUND
2088 -- figure out whether the component details are better higher or worse higher
2089 IF PG_DEBUG <= 5 THEN
2090 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: Failed to calculate for component ' || l_score_component_id );
2091 IEX_DEBUG_PUB.logMessage('Reason: ' || sqlerrm);
2092 END IF;
2093
2094 SELECT Range_Low
2095 BULK COLLECT INTO l_component_range_tbl
2096 FROM iex_score_comp_det
2097 where score_component_id = l_score_component_id
2098 order by value;
2099
2100 IF PG_DEBUG <= 5 THEN
2101 IEX_DEBUG_PUB.logMessage('Comparing Ranges');
2102 END IF;
2103
2104 if l_component_range_tbl(1) < l_component_range_tbl(2) then
2105 -- assign first comnponent detail row range to value
2106 l_component_score := l_component_range_tbl(1);
2107 else
2108 -- assign last comnponent detail row range to value
2109 i := l_component_range_tbl.count;
2110 l_component_score := l_component_range_tbl(i);
2111 end if;
2112 l_component_range_tbl.delete;
2113 -- Begin - Andre Araujo - 12/17/2004 - If the detail is not defined this throws a NO DATA FOUND
2114 EXCEPTION
2115 WHEN OTHERS THEN -- This will capture the exception from the component detail
2116 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_SCORE: get1Score: Exception selecting component detail range: WRONG ENGINE CONFIGURATION!!!!!');
2117 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_SCORE: get1Score: Score will be 1 - Execution will continue.');
2118 l_component_score := 1;
2119 END;
2120 -- End - Andre Araujo - 12/17/2004 - If the detail is not defined this throws a NO DATA FOUND
2121 END; -- end for exception
2122
2123 if PG_DEBUG <= 5 then
2124 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: Successfully calculated component score: ' || l_component_score);
2125 end if;
2126
2127 --end if;
2128
2129 /* 4. For each component value, get the details of the component
2130 and store the value for that score_comp_detail */
2131 BEGIN
2132 -- clchang updated 10/18/04 for 11.5.11
2133 -- new column NEW_VALUE instead of VALUE in iex_score_comp_det;
2134 --vSql := 'SELECT VALUE ' ||
2135 vSql := 'SELECT upper(NEW_VALUE) ' ||
2136 ' FROM IEX_SCORE_COMP_DET ' ||
2137 ' WHERE SCORE_COMPONENT_ID = :p_score_comp_id AND ' ||
2138 ' :p_component_score >= RANGE_LOW AND ' ||
2139 ' :p_component_score <= RANGE_HIGH ';
2140 if PG_DEBUG <= 5 then
2141 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2142 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: ' || 'Getting Details for component with ' || vSQL);
2143 END IF;
2144 end if;
2145
2146 -- clchang updated 10/18/04 for 11.5.11
2147 -- the value from det could be formula (including bind var :result);
2148 Execute Immediate vSql
2149 --INTO l_raw_score
2150 INTO l_value
2151 USING l_score_component_id, l_component_score, l_component_score;
2152
2153 --IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2154 --IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: Component raw score is ' || l_raw_score || ' Component weight is ' || l_score_comp_tbl(l_count2).SCORE_COMP_WEIGHT);
2155 --END IF;
2156
2157 -- BEGIN clchang added 10/18/04 for scr engine enhancement in 11.5.11
2158
2159 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2160 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: l_value=' || l_value);
2161 END IF;
2162 -- chk the value is a formula or not
2163 IF (INSTR(l_value, ':RESULT') > 0 ) THEN
2164 l_new_value := replace(l_value, ':RESULT', l_component_score);
2165 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2166 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: FORMULA');
2167 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: l_new_value=' || l_new_value);
2168 END IF;
2169 vSql := 'SELECT ' || l_new_value || ' FROM DUAL';
2170 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2171 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: vSql=' || vSql);
2172 END IF;
2173 Execute Immediate vSql
2174 INTO l_raw_score;
2175 ELSE
2176 l_raw_score := TO_NUMBER( l_value);
2177 END IF;
2178 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2179 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: l_raw_score=' || l_raw_score);
2180 END IF;
2181
2182
2183 l_weight_required := IEX_SCORE_NEW_PVT.G_WEIGHT_REQUIRED;
2184
2185 -- if weight_required <> Y, sum(score of each comp);
2186 IF (l_weight_required = 'Y') THEN
2187 --l_running_score:=l_running_score + round((l_raw_score * l_score_comp_tbl(l_count2).SCORE_COMP_WEIGHT));
2188 l_running_score:=l_running_score + round((l_raw_score * p_score_comp_tbl(l_count2).SCORE_COMP_WEIGHT),2);
2189 ELSE
2190 --l_running_score:=l_running_score + round(l_raw_score );
2191 l_running_score:=l_running_score + round(l_raw_score,2 );
2192 END IF;
2193 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2194 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: l_running_score=' || l_running_score);
2195 END IF;
2196 -- END clchang added 10/18/04 for scr engine enhancement in 11.5.11
2197
2198 --l_running_score:=l_running_score + round((l_raw_score * p_score_comp_tbl(l_count2).SCORE_COMP_WEIGHT));
2199 --IEX_DEBUG_PUB.logMessage('IEX_SCORE: getScores: Component Running score is ' || l_running_score);
2200
2201 EXCEPTION
2202 WHEN NO_DATA_FOUND THEN
2203 IF PG_DEBUG < 10 THEN
2204 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: Error getting component detail: ' || sqlerrm);
2205 END IF;
2206 l_running_score := l_running_score;
2207 WHEN OTHERS THEN
2208 IF PG_DEBUG < 10 THEN
2209 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: Error getting component detail: ' || sqlerrm);
2210 END IF;
2211 l_running_score := l_running_score;
2212 END;
2213
2214 END LOOP; -- component loop
2215
2216 -- End copied code
2217
2218 IF PG_DEBUG < 10 THEN
2219 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: Return calculated score: ' || l_running_score );
2220 END IF;
2221
2222 return l_running_score;
2223
2224 Exception
2225 WHEN FND_API.G_EXC_ERROR THEN
2226 IF PG_DEBUG < 10 THEN
2227 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: Expected Error ' || sqlerrm );
2228 END IF;
2229 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR:IEX_SCORE: get1Score: Expected Error ' || sqlerrm);
2230
2231 WHEN OTHERS THEN
2232 IF PG_DEBUG < 10 THEN
2233 IEX_DEBUG_PUB.logMessage('IEX_SCORE: get1Score: UnExpected Error ' || sqlerrm );
2234 END IF;
2235 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR:IEX_SCORE: get1Score: UnExpected Error ' || sqlerrm);
2236
2237 END get1Score;
2238
2239 --
2240 --- End - Andre Araujo - 11/02/2004 - New storage mode, this one respects the commit size - TAR 4040621.994
2241 --
2242
2243
2244 BEGIN
2245 G_Batch_Size := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '1000'));
2246 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2247
2248 G_MIN_SCORE := '1';
2249 G_MAX_SCORE := '100';
2250 G_RULE := 'CLOSEST';
2251 G_WEIGHT_REQUIRED := 'N';
2252
2253
2254 END IEX_SCORE_NEW_PVT;
[Home] [Help]