[Home] [Help]
PACKAGE BODY: APPS.IEX_SCOREAPI_PUB
Source
1 PACKAGE BODY IEX_SCOREAPI_PUB AS
2 /* $Header: iexpsrab.pls 120.0 2005/06/15 17:39:57 acaraujo ship $ */
3
4 PG_DEBUG NUMBER;
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IEX_SCOREAPI_PUB';
7 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexpsrab.pls';
8 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
9
10 G_components_tbl IEX_SCORE_NEW_PVT.SCORE_ENG_COMP_TBL;
11 G_OBJECT_CODE VARCHAR2(25);
12
13
14 procedure getScore( p_api_version IN NUMBER,
15 p_init_msg_list IN VARCHAR2,
16 p_SCORE_ID IN NUMBER,
17 p_OBJECT_ID IN NUMBER,
18 x_SCORE OUT NOCOPY NUMBER,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR)
22 IS
23
24 l_api_name CONSTANT VARCHAR2(30) := 'IEX_SCOREAPI_PUB';
25 l_api_version CONSTANT NUMBER := 1.0;
26 l_return_status VARCHAR2(1);
27 l_msg_count NUMBER;
28 l_msg_data VARCHAR2(32767);
29
30 l_scores_tbl IEX_SCORE_NEW_PVT.SCORES_TBL;
31 l_object_ids IEX_FILTER_PUB.UNIVERSE_IDS;
32 l_count NUMBER;
33 l_validobjectcode boolean;
34 l_validobjectID boolean;
35 --- Begin - Andre Araujo - 11/02/2004 - This has been changed because of a bug in the storage design in the scoring API
36 l_running_score NUMBER := null;
37 --- End - Andre Araujo - 11/02/2004 - This has been changed because of a bug in the storage design in the scoring API
38
39 BEGIN
40
41 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
42 G_OBJECT_CODE := null;
43 l_validobjectcode := TRUE;
44 l_validobjectID := TRUE;
45
46 /*--------------------------------------------------+
47 | Standard call to check for call compatibility |
48 +--------------------------------------------------*/
49
50 IF NOT FND_API.Compatible_API_Call(
51 l_api_version,
52 p_api_version,
53 l_api_name,
54 G_PKG_NAME
55 )
56 THEN
57 x_return_status := FND_API.G_RET_STS_ERROR ;
58
59 /*--------------------------------------------------+
60 | Get message count and if 1, return message data |
61 +---------------------------------------------------*/
62
63 FND_MSG_PUB.Count_And_Get(
64 p_encoded => FND_API.G_FALSE,
65 p_count => l_msg_count,
66 p_data => l_msg_data
67 );
68 IF PG_DEBUG in ('Y', 'C') THEN
69 arp_util.debug('Compatility error occurred.', G_MSG_ERROR);
70 END IF;
71
72 x_msg_count := l_msg_count;
73 x_msg_data := l_msg_data;
74
75 RETURN ;
76
77 END IF;
78
79 /*-------------------------------------------------------------+
80 | Initialize message list if p_init_msg_list is set to TRUE |
81 +-------------------------------------------------------------*/
82
83 IF FND_API.to_Boolean( p_init_msg_list )
84 THEN
85 FND_MSG_PUB.initialize;
86 END IF;
87
88
89 -- Initialize API return status to success
90 l_return_status := FND_API.G_RET_STS_SUCCESS;
91
92 -- START OF BODY OF API
93 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
94 iex_debug_pub.LogMessage(l_api_name || ': Start of API');
95 END IF;
96
97
98 -- enumerate components for this scoring engine
99 IEX_SCORE_NEW_PVT.getComponents(p_score_id => p_score_id ,
100 X_SCORE_COMP_TBL => g_components_tbl);
101
102 IF g_components_tbl is null or g_components_tbl.count < 1 then
103
104 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_SCORE: scoreObjects: No score components for engine ' || p_score_id);
105 FND_MESSAGE.Set_Name('IEX', 'IEX_NO_SCORE_ENG_COMPONENTS');
106 FND_MSG_PUB.Add;
107 --RAISE FND_API.G_EXC_ERROR;
108 x_return_status := FND_API.G_RET_STS_ERROR;
109 FND_MSG_PUB.Count_And_Get(
110 p_encoded => FND_API.G_FALSE,
111 p_count => l_msg_count,
112 p_data => l_msg_data
113 );
114
115 if (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
116 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: No score components for engine');
117 end if;
118
119 x_msg_count := l_msg_count;
120 x_msg_data := l_msg_data;
121
122 RETURN;
123
124 END IF;
125
126
127 -- Validate Scoring Engine
128
129 l_validobjectcode := checkObject_Compatibility(P_SCORE_ID);
130 IF l_validobjectcode = FALSE THEN
131
132 if (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
133 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: Score Object is not match to engine');
134 end if;
135
136 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_SCORE: scoreObjects: Score Object is not match to engine ' || p_score_id);
137 FND_MESSAGE.Set_Name('IEX', 'IEX_INVALID_SCORING_ENGINE');
138 FND_MSG_PUB.Add;
139 -- RAISE FND_API.G_EXC_ERROR;
140 x_return_status := FND_API.G_RET_STS_ERROR;
141 FND_MSG_PUB.Count_And_Get(
142 p_encoded => FND_API.G_FALSE,
143 p_count => l_msg_count,
144 p_data => l_msg_data
145 );
146 x_msg_count := l_msg_count;
147 x_msg_data := l_msg_data;
148 RETURN;
149
150 END IF;
151
152 -- Validate Object ID
153
154 l_validobjectID := IEX_SCORE_NEW_PVT.validateObjectID(P_OBJECT_ID,G_OBJECT_CODE);
155
156 IF l_validobjectID = FALSE THEN
157
158 if (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
159 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: Score Object ID is not match to engine');
160 end if;
161
162 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_SCORE: scoreObjects: Score Object ID is not match to engine ' || p_object_id);
163 FND_MESSAGE.Set_Name('IEX', 'IEX_INVALID_SCORING_ENGINE');
164 FND_MSG_PUB.Add;
165 -- RAISE FND_API.G_EXC_ERROR;
166 x_return_status := FND_API.G_RET_STS_ERROR;
167 FND_MSG_PUB.Count_And_Get(
168 p_encoded => FND_API.G_FALSE,
169 p_count => l_msg_count,
170 p_data => l_msg_data
171 );
172 x_msg_count := l_msg_count;
173 x_msg_data := l_msg_data;
174 RETURN;
175
176 END IF;
177
178
179 l_object_ids(1) := p_object_id;
180
181
182 -- get the scores for the object
183
184 l_count := l_scores_tbl.count;
185
186 For i in 1..l_count Loop
187 l_scores_tbl(i) := 0;
188 End Loop;
189
190 --- Begin - Andre Araujo - 11/02/2004 - This has been changed because of a bug in the storage design in the scoring API
191 -- IEX_SCORE_NEW_PVT.getScores(p_score_comp_tbl => g_componentS_tbl,
192 -- t_object_ids => l_object_ids,
193 -- x_scores_tbl => l_scores_tbl
194 -- );
195 l_running_score := IEX_SCORE_NEW_PVT.get1Score( g_components_tbl, l_object_ids(1) );
196
197 l_scores_tbl(1) := l_running_score;
198
199 IF l_running_score is null then
200 -- IF l_scores_tbl is null or l_scores_tbl.count < 1 then
201 --- End - Andre Araujo - 11/02/2004 - This has been changed because of a bug in the storage design in the scoring API
202
203 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No Scores Calculated for Engine: ' || p_score_id);
204 FND_MESSAGE.Set_Name('IEX', 'IEX_UNABLE_TO_COMPUTE_SCORES');
205 FND_MSG_PUB.Add;
206 --RAISE FND_API.G_EXC_ERROR;
207 x_return_status := FND_API.G_RET_STS_ERROR;
208 FND_MSG_PUB.Count_And_Get(
209 p_encoded => FND_API.G_FALSE,
210 p_count => l_msg_count,
211 p_data => l_msg_data
212 );
213
214 if (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
215 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: Unable to compute scores');
216 end if;
217
218 x_msg_count := l_msg_count;
219 x_msg_data := l_msg_data;
220
221 RETURN;
222
223 END if;
224
225 x_return_status := l_return_status ;
226
227 /*--------------------------------------------------+
228 | Get message count and if 1, return message data |
229 +---------------------------------------------------*/
230
231 FND_MSG_PUB.Count_And_Get(
232 p_encoded => FND_API.G_FALSE,
233 p_count => l_msg_count,
234 p_data => l_msg_data
235 );
236
237 x_msg_count := l_msg_count;
238 x_msg_data := l_msg_data;
239
240 X_SCORE := l_scores_tbl(1);
241
242 exception
243 when others then
244 x_return_status := FND_API.G_RET_STS_ERROR;
245 x_msg_count := l_msg_count;
246 x_msg_data := l_msg_data;
247 RETURN;
248
249 END;
250
251
252 procedure getStatus( p_api_version IN NUMBER,
253 p_init_msg_list IN VARCHAR2,
254 p_commit IN VARCHAR2,
255 p_SCORE_ID IN NUMBER,
256 p_SCORE IN NUMBER,
257 x_STATUS OUT NOCOPY VARCHAR2,
258 x_return_status OUT NOCOPY VARCHAR2,
259 x_msg_count OUT NOCOPY NUMBER,
260 x_msg_data OUT NOCOPY VARCHAR)
261 IS
262 l_api_name CONSTANT VARCHAR2(30) := 'IEX_SCOREAPI_PUB';
263 l_api_version CONSTANT NUMBER := 1.0;
264 l_return_status VARCHAR2(1);
265 l_msg_count NUMBER;
266 l_msg_data VARCHAR2(32767);
267
268 CURSOR C_getStatus(P_SCORE_ID NUMBER,P_SCORE NUMBER) is
269 SELECT del_status
270 FROM iex_del_statuses
271 WHERE P_SCORE between score_value_low and score_value_high
272 AND score_id = P_SCORE_ID;
273
274 l_validobjectcode boolean;
275 l_status varchar2(25);
276
277 BEGIN
278 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
279 G_OBJECT_CODE := null;
280 l_validobjectcode := TRUE;
281 l_status := null;
282
283 /*--------------------------------------------------+
284 | Standard call to check for call compatibility |
285 +--------------------------------------------------*/
286
287 IF NOT FND_API.Compatible_API_Call(
288 l_api_version,
289 p_api_version,
290 l_api_name,
291 G_PKG_NAME
292 )
293 THEN
294 x_return_status := FND_API.G_RET_STS_ERROR ;
295
296 /*--------------------------------------------------+
297 | Get message count and if 1, return message data |
298 +---------------------------------------------------*/
299
300 FND_MSG_PUB.Count_And_Get(
301 p_encoded => FND_API.G_FALSE,
302 p_count => l_msg_count,
303 p_data => l_msg_data
304 );
305 IF PG_DEBUG in ('Y', 'C') THEN
306 arp_util.debug('Compatility error occurred.', G_MSG_ERROR);
307 END IF;
308
309 x_msg_count := l_msg_count;
310 x_msg_data := l_msg_data;
311 RETURN ;
312 END IF;
313
314 /*-------------------------------------------------------------+
315 | Initialize message list if p_init_msg_list is set to TRUE |
316 +-------------------------------------------------------------*/
317
318 IF FND_API.to_Boolean( p_init_msg_list )
319 THEN
320 FND_MSG_PUB.initialize;
321 END IF;
322
323
324 -- Initialize API return status to success
325 l_return_status := FND_API.G_RET_STS_SUCCESS;
326
327 -- START OF BODY OF API
328 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
329 iex_debug_pub.LogMessage(l_api_name || ': Start of API');
330 END IF;
331
332
333 l_validobjectcode := checkObject_Compatibility(P_SCORE_ID);
334
335 IF l_validobjectcode = FALSE THEN
336
337 if (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
338 IEX_DEBUG_PUB.logMessage('IEX_SCORE: scoreObjects: Score Object is not match to engine');
339 end if;
340
341 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_SCORE: scoreObjects: Score Object is not match to engine ' || p_score_id);
342 FND_MESSAGE.Set_Name('IEX', 'IEX_INVALID_SCORING_ENGINE');
343 FND_MSG_PUB.Add;
344 -- RAISE FND_API.G_EXC_ERROR;
345 x_return_status := FND_API.G_RET_STS_ERROR;
346 FND_MSG_PUB.Count_And_Get(
347 p_encoded => FND_API.G_FALSE,
348 p_count => l_msg_count,
349 p_data => l_msg_data
350 );
351 x_msg_count := l_msg_count;
352 x_msg_data := l_msg_data;
353 RETURN;
354
355 ELSE
356
357 Open C_getStatus(P_SCORE_ID,P_SCORE);
358 Fetch C_getStatus into l_status;
359 IF C_getStatus%NOTFOUND then
360 x_STATUS := null;
361 x_return_status := FND_API.G_RET_STS_ERROR;
362 if PG_DEBUG in ('Y', 'C') THEN
363 arp_util.debug('Get Status : ' ||'No Status for Score Engine,Score provided. ');
364 end if;
365 ELSE
366 x_STATUS := l_status;
367 x_return_status := l_return_status;
368 END IF;
369
370 END IF;
371
372 FND_MSG_PUB.Count_And_Get(
373 p_encoded => FND_API.G_FALSE,
374 p_count => l_msg_count,
375 p_data => l_msg_data
376 );
377
378 x_msg_count := l_msg_count;
379 x_msg_data := l_msg_data;
380 RETURN;
381
382
383 EXCEPTION
384 When Others then
385 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_SCORE: No Status found ' || p_score_id);
386 FND_MESSAGE.Set_Name('AR', 'AR_OPLB_NO_DATA_FOUND');
387 FND_MSG_PUB.Add;
388 x_return_status := FND_API.G_RET_STS_ERROR;
389 IF PG_DEBUG in ('Y', 'C') THEN
390 arp_util.debug('Get Status : ' ||'No Status for Score Engine,Score provided. ');
391 END IF;
392 FND_MSG_PUB.Count_And_Get(
393 p_encoded => FND_API.G_FALSE,
394 p_count => l_msg_count,
395 p_data => l_msg_data
396 );
397 x_msg_count := l_msg_count;
398 x_msg_data := l_msg_data;
399 RETURN;
400
401 END;
402
403
404 procedure getScoreStatus( p_api_version IN NUMBER,
405 p_init_msg_list IN VARCHAR2,
406 p_SCORE_ID IN NUMBER,
407 p_OBJECT_ID IN NUMBER,
408 x_STATUS OUT NOCOPY VARCHAR2,
409 x_SCORE OUT NOCOPY NUMBER,
410 x_return_status OUT NOCOPY VARCHAR2,
411 x_msg_count OUT NOCOPY NUMBER,
412 x_msg_data OUT NOCOPY VARCHAR)
413 IS
414 l_api_name CONSTANT VARCHAR2(30) := 'IEX_SCOREAPI_PUB';
415 l_api_version CONSTANT NUMBER := 1.0;
416 l_return_status VARCHAR2(1);
417 l_msg_count NUMBER;
418 l_msg_data VARCHAR2(32767);
419
420 l_validobjectcode boolean;
421 l_status varchar2(25);
422 l_score number;
423
424 BEGIN
425
426 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
427 G_OBJECT_CODE := null;
428 l_validobjectcode := TRUE;
429 l_status := null;
430 l_score := 0;
431 /*--------------------------------------------------+
432 | Standard call to check for call compatibility |
433 +--------------------------------------------------*/
434
435 IF NOT FND_API.Compatible_API_Call(
436 l_api_version,
437 p_api_version,
438 l_api_name,
439 G_PKG_NAME
440 )
441 THEN
442 x_return_status := FND_API.G_RET_STS_ERROR ;
443
444 /*--------------------------------------------------+
445 | Get message count and if 1, return message data |
446 +---------------------------------------------------*/
447
448 FND_MSG_PUB.Count_And_Get(
449 p_encoded => FND_API.G_FALSE,
450 p_count => l_msg_count,
451 p_data => l_msg_data
452 );
453 IF PG_DEBUG in ('Y', 'C') THEN
454 arp_util.debug('Compatility error occurred.', G_MSG_ERROR);
455 END IF;
456
457 x_msg_count := l_msg_count;
458 x_msg_data := l_msg_data;
459
460 RETURN ;
461
462 END IF;
463
464 /*-------------------------------------------------------------+
465 | Initialize message list if p_init_msg_list is set to TRUE |
466 +-------------------------------------------------------------*/
467
468 IF FND_API.to_Boolean( p_init_msg_list )
469 THEN
470 FND_MSG_PUB.initialize;
471 END IF;
472
473
474 -- Initialize API return status to success
475 l_return_status := FND_API.G_RET_STS_SUCCESS;
476
477 -- START OF BODY OF API
478 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
479 iex_debug_pub.LogMessage(l_api_name || ': Start of API');
480 END IF;
481
482
483 getScore( p_api_version => 1.0,
484 p_init_msg_list => FND_API.G_FALSE,
485 p_SCORE_ID => p_score_id,
486 p_OBJECT_ID => p_object_id,
487 x_SCORE => l_score,
488 x_return_status => l_return_status,
489 x_msg_count => l_msg_count,
490 x_msg_data => l_msg_data);
491
492 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
493 x_return_status := FND_API.G_RET_STS_ERROR;
494 IF PG_DEBUG in ('Y', 'C') THEN
495 arp_util.debug('Getting Score : ' ||'No Score for Object provided. ');
496 END IF;
497 FND_MSG_PUB.Count_And_Get(
498 p_encoded => FND_API.G_FALSE,
499 p_count => l_msg_count,
500 p_data => l_msg_data
501 );
502 x_msg_count := l_msg_count;
503 x_msg_data := l_msg_data;
504 RETURN;
505 END IF;
506
507 x_score := l_score;
508
509 getStatus( p_api_version => 1.0,
510 p_init_msg_list => FND_API.G_FALSE,
511 p_commit => FND_API.G_FALSE,
512 p_SCORE_ID => p_score_id,
513 p_SCORE => l_score,
514 x_STATUS => l_status,
515 x_return_status => l_return_status,
516 x_msg_count => l_msg_count,
517 x_msg_data => l_msg_data);
518
519 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
520 x_return_status := FND_API.G_RET_STS_ERROR;
521 IF PG_DEBUG in ('Y', 'C') THEN
522 arp_util.debug('Getting Status : ' ||'No Status for Object provided. ');
523 END IF;
524 FND_MSG_PUB.Count_And_Get(
525 p_encoded => FND_API.G_FALSE,
526 p_count => l_msg_count,
527 p_data => l_msg_data
528 );
529 x_msg_count := l_msg_count;
530 x_msg_data := l_msg_data;
531 RETURN;
532 END IF;
533
534 x_msg_count := l_msg_count;
535 x_msg_data := l_msg_data;
536 x_status := l_status;
537 x_return_status := l_return_status;
538
539 exception
540 when others then
541 x_return_status := FND_API.G_RET_STS_ERROR;
542 x_msg_count := l_msg_count;
543 x_msg_data := l_msg_data;
544 RETURN;
545
546 END;
547
548
549 Function checkObject_Compatibility(p_score_id in number)
550 return BOOLEAN
551 is
552 l_object_code varchar2(25);
553 l_valid_invoice_object varchar2(25);
554 l_valid_case_object varchar2(25);
555 l_valid_loan_object varchar2(25);
556
557 begin
558
559 l_valid_invoice_object := 'IEX_INVOICES';
560 l_valid_case_object := 'IEX_CASES';
561 l_valid_loan_object := 'IEX_LOANS';
562
563 begin
564 Execute Immediate
565 ' Select jtf_object_code ' ||
566 ' From iex_scores ' ||
567 ' where score_id = :p_score_id'
568 into l_object_code
569 using p_score_id;
570 Exception
571 When no_data_found then
572 G_OBJECT_CODE := null;
573 return FALSE;
574 When others then
575 G_OBJECT_CODE := null;
576 return FALSE;
577 end;
578
579 G_OBJECT_CODE := l_object_code;
580 return TRUE;
581
582 /* fixed a bug 3799715 by ehuh
583 if (l_object_code = l_valid_invoice_object) OR
584 (l_object_code = l_valid_loan_object) OR
585 (l_object_code = l_valid_case_object) THEN
586 return TRUE;
587 else
588 G_OBJECT_CODE := null;
589 return FALSE;
590 end if;
591 */
592
593 Exception
594 when others then
595 G_OBJECT_CODE := null;
596 return false;
597
598 end;
599
600 END;