[Home] [Help]
PACKAGE BODY: APPS.AMS_SCORERESULT_PVT
Source
1 PACKAGE BODY AMS_Scoreresult_PVT as
2 /* $Header: amsvdrsb.pls 120.0 2005/05/31 20:21:35 appldev noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_Scoreresult_PVT
7 -- Purpose
8 --
9 -- History
10 -- 24-Jan-2001 choang Created.
11 -- 24-Jan-2001 choang Added setting of out id after create api.
12 -- 24-Jan-2001 choang Changed call to update_row api to have object
13 -- version number + 1.
14 -- 26-Jan-2001 choang 1) added summarize_results 2) changed response
15 -- to score.
16 -- 12-Feb-2001 choang Cursor in summarize_results needed to join to
17 -- ams_dm_rules to get tree_node.
18 -- 28-Feb-2001 choang Renamed some messages so name length is less than 30.
19 -- 13-Apr-2001 choang Added generate_list and parse_tree_node_str
20 -- 21-May-2001 choang Added overloaded generate_list
21 -- 10-Jul-2001 choang Replaced tree_node with decile
22 -- 09-Oct-2001 choang Added filter to summarize_results
23 -- 27-Nov-2001 choang Fixed callout to generate_list
24 -- 07-Jan-2002 choang Removed security group id
25 -- 17-May-2002 choang bug 2380113: removed g_user_id and g_login_id
26 -- 10-Jun-2002 choang Fixed gscc error for bug 2380113.
27 -- 08-Jul-2002 nyostos Removed hardcoded ListSourceType in generate_list().
28 -- Added code to look up the SOURCE_TYPE_CODE for the list_source_type_id
29 -- 19-Mar-2003 choang Bug 2856138 - Added return status for OSOException in JSP.
30 -- 04-Apr-2003 choang Bug 2888007 - fixed primary_key param in list create API
31 -- invocation.
32 -- 17-Jun-2003 nyostos Added procedure insert_percentile_results to summarize scoring
33 -- run results by percentile in AMS_DM_SCORE_PCT_RESULTS table.
34 -- These results are used in calculating the Optimal Targeting Chart.
35 -- 31-Jul-2003 nyostos Fixed logic in insert_percentile_results to account for having fewer
36 -- than 100 records.
37 -- 12-Aug-2003 kbasavar For Customer profitability
38 -- 15-Aug-2003 nyostos Fixed logic for updating random_result in insert_percentile_results.
39 -- 10-Sep-2003 kbasavar Changes made to check for seeded targets for Customer profitability
40 -- 15-Sep-2003 nyostos Changes related to parallel mining processes using Global Temp Tables.
41 -- 20-Oct-2003 nyostos Added check if no records found in insert_percentile_results.
42 -- 06-Nov-2003 rosharma Renamed ams_dm_org_contacts_stg to ams_dm_org_contacts
43 -- 17-Dec-2003 rosharma Fixed to update ams_dm_source when inserting percentiles in AMS_DM_SCORE_PCT_RESULTS
44 -- 30-Dec-2003 kbasavar Call insert_percentile_results only for models enabled for Optimal Targeting
45 -- 20-Jan-2004 rosharma bug # 3380057
46 -- 22-Jan-2004 kbasavar If List Generation is successful then insert into AMS_DM_SCORE_LISTS.
47 -- 23-Jan-2004 kbasavar Org Product Affinity Changes.
48 -- 20-Apr-2004 pkanukol SQL Bind Vars issue fixed
49 -- 22-Apr-2004 choang Fixed install bug 3588127; reverted bind var change for insert_percentile_results
50 -- 4-May-2004 pkanukol Tuned insert_percentile_results
51 -- NOTE
52 --
53 -- End of Comments
54 -- ===============================================================
55
56
57 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_Scoreresult_PVT';
58 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvdrsb.pls';
59
60 /***
61 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
62 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
63 ***/
64
65 G_LIST_SOURCE_NAME VARCHAR2(30) := 'AMS_DM_SOURCE';
66
67 -- package global types
68 TYPE tree_node_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
69
70 --Model types
71 G_MODEL_TYPE_EMAIL CONSTANT VARCHAR2(30) := 'EMAIL';
72 G_MODEL_TYPE_DMAIL CONSTANT VARCHAR2(30) := 'TELEMARKETING';
73 G_MODEL_TYPE_TELEM CONSTANT VARCHAR2(30) := 'DIRECTMAIL';
74 G_MODEL_TYPE_PROD CONSTANT VARCHAR2(30) := 'PRODUCT_AFFINITY';
75
76
77 -- forward procedure/function declarations
78 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
79 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
80 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
81
82 /*
83 -- Moved the definition to body
84 -- 3/24/2005 kbasavar for 4259733
85 -- 17-jun-2003 added by nyostos
86 PROCEDURE insert_percentile_results (
87 p_score_id IN NUMBER
88 );
89 */
90
91 PROCEDURE parse_tree_node_str (
92 p_tree_node_str IN VARCHAR2,
93 x_tree_table OUT NOCOPY tree_node_table_type,
94 x_tab_ctr OUT NOCOPY NUMBER
95 );
96
97
98 -- Hint: Primary key needs to be returned.
99 PROCEDURE Create_Scoreresult(
100 p_api_version IN NUMBER,
101 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
102 p_commit IN VARCHAR2 := FND_API.G_FALSE,
103 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
104
105 x_return_status OUT NOCOPY VARCHAR2,
106 x_msg_count OUT NOCOPY NUMBER,
107 x_msg_data OUT NOCOPY VARCHAR2,
108
109 p_scoreresult_rec IN scoreresult_rec_type := g_miss_scoreresult_rec,
110 x_score_result_id OUT NOCOPY NUMBER
111 )
112
113 IS
114 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Scoreresult';
115 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
116 l_return_status_full VARCHAR2(1);
117 l_object_version_number NUMBER := 1;
118 l_org_id NUMBER := FND_API.G_MISS_NUM;
119 l_SCORE_RESULT_ID NUMBER;
120 l_dummy NUMBER;
121
122 CURSOR c_id IS
123 SELECT AMS_DM_SCORE_RESULTS_s.NEXTVAL
124 FROM dual;
125
126 CURSOR c_id_exists (l_id IN NUMBER) IS
127 SELECT 1 FROM dual
128 WHERE EXISTS (SELECT 1 FROM AMS_DM_SCORE_RESULTS
129 WHERE SCORE_RESULT_ID = l_id);
130
131 BEGIN
132 -- Standard Start of API savepoint
133 SAVEPOINT CREATE_Scoreresult_PVT;
134
135 -- Standard call to check for call compatibility.
136 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
137 p_api_version,
138 l_api_name,
139 G_PKG_NAME)
140 THEN
141 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
142 END IF;
143
144 -- Initialize message list if p_init_msg_list is set to TRUE.
145 IF FND_API.to_Boolean( p_init_msg_list ) THEN
146 FND_MSG_PUB.initialize;
147 END IF;
148
149 -- Debug Message
150 IF (AMS_DEBUG_HIGH_ON) THEN
151
152 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
153 END IF;
154
155
156 -- Initialize API return status to SUCCESS
157 x_return_status := FND_API.G_RET_STS_SUCCESS;
158
159 -- Local variable initialization
160
161 IF p_scoreresult_rec.SCORE_RESULT_ID IS NULL OR p_scoreresult_rec.SCORE_RESULT_ID = FND_API.g_miss_num THEN
162 LOOP
163 l_dummy := NULL;
164 OPEN c_id;
165 FETCH c_id INTO l_SCORE_RESULT_ID;
166 CLOSE c_id;
167
168 OPEN c_id_exists(l_SCORE_RESULT_ID);
169 FETCH c_id_exists INTO l_dummy;
170 CLOSE c_id_exists;
171 EXIT WHEN l_dummy IS NULL;
172 END LOOP;
173 END IF;
174
175 -- =========================================================================
176 -- Validate Environment
177 -- =========================================================================
178
179 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
180 THEN
181 -- Debug message
182 IF (AMS_DEBUG_HIGH_ON) THEN
183
184 AMS_UTILITY_PVT.debug_message('Private API: Validate_Scoreresult');
185 END IF;
186
187 -- Invoke validation procedures
188 Validate_scoreresult(
189 p_api_version => 1.0,
190 p_init_msg_list => FND_API.G_FALSE,
191 p_validation_level => p_validation_level,
192 p_validation_mode => JTF_PLSQL_API.g_create,
193 p_scoreresult_rec => p_scoreresult_rec,
194 x_return_status => x_return_status,
195 x_msg_count => x_msg_count,
196 x_msg_data => x_msg_data);
197 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
198 RAISE FND_API.G_EXC_ERROR;
199 END IF;
200 END IF;
201
202 -- Debug Message
203 IF (AMS_DEBUG_HIGH_ON) THEN
204
205 AMS_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
206 END IF;
207
208 -- Invoke table handler(AMS_DM_SCORE_RESULTS_PKG.Insert_Row)
209 AMS_DM_SCORE_RESULTS_PKG.Insert_Row(
210 px_score_result_id => l_score_result_id,
211 p_last_update_date => SYSDATE,
212 p_last_updated_by => FND_GLOBAL.USER_ID,
213 p_creation_date => SYSDATE,
214 p_created_by => FND_GLOBAL.USER_ID,
215 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
216 px_object_version_number => l_object_version_number,
217 p_score_id => p_scoreresult_rec.score_id,
218 p_decile => p_scoreresult_rec.decile,
219 p_num_records => p_scoreresult_rec.num_records,
220 p_score => p_scoreresult_rec.score,
221 p_confidence => p_scoreresult_rec.confidence
222 );
223 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
224 RAISE FND_API.G_EXC_ERROR;
225 END IF;
226
227 x_score_result_id := l_score_result_id;
228
229 --
230 -- End of API body
231 --
232
233 -- Standard check for p_commit
234 IF FND_API.to_Boolean( p_commit )
235 THEN
236 COMMIT WORK;
237 END IF;
238
239
240 -- Debug Message
241 IF (AMS_DEBUG_HIGH_ON) THEN
242
243 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
244 END IF;
245
246 -- Standard call to get message count and if count is 1, get message info.
247 FND_MSG_PUB.Count_And_Get
248 (p_count => x_msg_count,
249 p_data => x_msg_data
250 );
251 EXCEPTION
252
253 WHEN AMS_Utility_PVT.resource_locked THEN
254 x_return_status := FND_API.g_ret_sts_error;
255 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
256
257 WHEN FND_API.G_EXC_ERROR THEN
258 ROLLBACK TO CREATE_Scoreresult_PVT;
259 x_return_status := FND_API.G_RET_STS_ERROR;
260 -- Standard call to get message count and if count=1, get the message
261 FND_MSG_PUB.Count_And_Get (
262 p_encoded => FND_API.G_FALSE,
263 p_count => x_msg_count,
264 p_data => x_msg_data
265 );
266
267 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
268 ROLLBACK TO CREATE_Scoreresult_PVT;
269 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
270 -- Standard call to get message count and if count=1, get the message
271 FND_MSG_PUB.Count_And_Get (
272 p_encoded => FND_API.G_FALSE,
273 p_count => x_msg_count,
274 p_data => x_msg_data
275 );
276
277 WHEN OTHERS THEN
278 ROLLBACK TO CREATE_Scoreresult_PVT;
279 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
280 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
281 THEN
282 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
283 END IF;
284 -- Standard call to get message count and if count=1, get the message
285 FND_MSG_PUB.Count_And_Get (
286 p_encoded => FND_API.G_FALSE,
287 p_count => x_msg_count,
288 p_data => x_msg_data
289 );
290 End Create_Scoreresult;
291
292
293 PROCEDURE Update_Scoreresult(
294 p_api_version IN NUMBER,
295 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
296 p_commit IN VARCHAR2 := FND_API.G_FALSE,
297 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
298
299 x_return_status OUT NOCOPY VARCHAR2,
300 x_msg_count OUT NOCOPY NUMBER,
301 x_msg_data OUT NOCOPY VARCHAR2,
302
303 p_scoreresult_rec IN scoreresult_rec_type,
304 x_object_version_number OUT NOCOPY NUMBER
305 )
306 IS
307 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Scoreresult';
308 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
309 -- Local Variables
310 l_object_version_number NUMBER;
311 l_SCORE_RESULT_ID NUMBER;
312 l_tar_scoreresult_rec AMS_Scoreresult_PVT.scoreresult_rec_type := P_scoreresult_rec;
313
314 CURSOR c_get_scoreresult(p_score_result_id NUMBER) IS
315 SELECT *
316 FROM AMS_DM_SCORE_RESULTS
317 WHERE score_result_id = p_score_result_id;
318 l_ref_scoreresult_rec c_get_Scoreresult%ROWTYPE ;
319 BEGIN
320 -- Standard Start of API savepoint
321 SAVEPOINT UPDATE_Scoreresult_PVT;
322
323 -- Standard call to check for call compatibility.
324 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
325 p_api_version,
326 l_api_name,
327 G_PKG_NAME)
328 THEN
329 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
330 END IF;
331
332 -- Initialize message list if p_init_msg_list is set to TRUE.
333 IF FND_API.to_Boolean( p_init_msg_list )
334 THEN
335 FND_MSG_PUB.initialize;
336 END IF;
337
338 -- Debug Message
339 IF (AMS_DEBUG_HIGH_ON) THEN
340
341 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
342 END IF;
343
344
345 -- Initialize API return status to SUCCESS
346 x_return_status := FND_API.G_RET_STS_SUCCESS;
347
348 -- Debug Message
349 IF (AMS_DEBUG_HIGH_ON) THEN
350
351 AMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
352 END IF;
353
354 OPEN c_get_Scoreresult( l_tar_scoreresult_rec.score_result_id);
355
356 FETCH c_get_Scoreresult INTO l_ref_scoreresult_rec ;
357
358 IF ( c_get_Scoreresult%NOTFOUND) THEN
359 AMS_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
360 p_token_name => 'INFO',
361 p_token_value => 'Scoreresult') ;
362 RAISE FND_API.G_EXC_ERROR;
363 END IF;
364 -- Debug Message
365 IF (AMS_DEBUG_HIGH_ON) THEN
366
367 AMS_UTILITY_PVT.debug_message('Private API: - Close Cursor');
368 END IF;
369 CLOSE c_get_Scoreresult;
370
371 -- Check Whether record has been changed by someone else
372 IF (l_tar_scoreresult_rec.object_version_number <> l_ref_scoreresult_rec.object_version_number) THEN
373 AMS_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED',
374 p_token_name => 'INFO',
375 p_token_value => 'Scoreresult') ;
376 RAISE FND_API.G_EXC_ERROR;
377 End if;
378 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
379 -- Debug message
380 IF (AMS_DEBUG_HIGH_ON) THEN
381
382 AMS_UTILITY_PVT.debug_message('Private API: Validate_Scoreresult');
383 END IF;
384
385 -- Invoke validation procedures
386 Validate_scoreresult(
387 p_api_version => 1.0,
388 p_init_msg_list => FND_API.G_FALSE,
389 p_validation_level => p_validation_level,
390 p_validation_mode => JTF_PLSQL_API.g_update,
391 p_scoreresult_rec => p_scoreresult_rec,
392 x_return_status => x_return_status,
393 x_msg_count => x_msg_count,
394 x_msg_data => x_msg_data);
395 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
396 RAISE FND_API.G_EXC_ERROR;
397 END IF;
398 END IF;
399
400 -- Debug Message
401 IF (AMS_DEBUG_HIGH_ON) THEN
402
403 AMS_UTILITY_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling update table handler');
404 END IF;
405
406 -- Invoke table handler(AMS_DM_SCORE_RESULTS_PKG.Update_Row)
407 AMS_DM_SCORE_RESULTS_PKG.Update_Row(
408 p_score_result_id => p_scoreresult_rec.score_result_id,
409 p_last_update_date => SYSDATE,
410 p_last_updated_by => FND_GLOBAL.USER_ID,
411 p_creation_date => SYSDATE,
412 p_created_by => l_ref_scoreresult_rec.created_by,
413 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
414 p_object_version_number => p_scoreresult_rec.object_version_number + 1,
415 p_score_id => p_scoreresult_rec.score_id,
416 p_decile => p_scoreresult_rec.decile,
417 p_num_records => p_scoreresult_rec.num_records,
418 p_score => p_scoreresult_rec.score,
419 p_confidence => p_scoreresult_rec.confidence
420 );
421 --
422 -- End of API body.
423 --
424
425 -- Standard check for p_commit
426 IF FND_API.to_Boolean( p_commit )
427 THEN
428 COMMIT WORK;
429 END IF;
430
431
432 -- Debug Message
433 IF (AMS_DEBUG_HIGH_ON) THEN
434
435 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
436 END IF;
437
438 -- Standard call to get message count and if count is 1, get message info.
439 FND_MSG_PUB.Count_And_Get
440 (p_count => x_msg_count,
441 p_data => x_msg_data
442 );
443 EXCEPTION
444
445 WHEN AMS_Utility_PVT.resource_locked THEN
446 x_return_status := FND_API.g_ret_sts_error;
447 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
448
449 WHEN FND_API.G_EXC_ERROR THEN
450 ROLLBACK TO UPDATE_Scoreresult_PVT;
451 x_return_status := FND_API.G_RET_STS_ERROR;
452 -- Standard call to get message count and if count=1, get the message
453 FND_MSG_PUB.Count_And_Get (
454 p_encoded => FND_API.G_FALSE,
455 p_count => x_msg_count,
456 p_data => x_msg_data
457 );
458
459 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
460 ROLLBACK TO UPDATE_Scoreresult_PVT;
461 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
462 -- Standard call to get message count and if count=1, get the message
463 FND_MSG_PUB.Count_And_Get (
464 p_encoded => FND_API.G_FALSE,
465 p_count => x_msg_count,
466 p_data => x_msg_data
467 );
468
469 WHEN OTHERS THEN
470 ROLLBACK TO UPDATE_Scoreresult_PVT;
471 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
472 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
473 THEN
474 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
475 END IF;
476 -- Standard call to get message count and if count=1, get the message
477 FND_MSG_PUB.Count_And_Get (
478 p_encoded => FND_API.G_FALSE,
479 p_count => x_msg_count,
480 p_data => x_msg_data
481 );
482 End Update_Scoreresult;
483
484
485 PROCEDURE Delete_Scoreresult(
486 p_api_version IN NUMBER,
487 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
488 p_commit IN VARCHAR2 := FND_API.G_FALSE,
489 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
490 x_return_status OUT NOCOPY VARCHAR2,
491 x_msg_count OUT NOCOPY NUMBER,
492 x_msg_data OUT NOCOPY VARCHAR2,
493 p_score_result_id IN NUMBER,
494 p_object_version_number IN NUMBER
495 )
496 IS
497 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Scoreresult';
498 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
499 BEGIN
500 -- Standard Start of API savepoint
501 SAVEPOINT DELETE_Scoreresult_PVT;
502
503 -- Standard call to check for call compatibility.
504 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
505 p_api_version,
506 l_api_name,
507 G_PKG_NAME)
508 THEN
509 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
510 END IF;
511
512 -- Initialize message list if p_init_msg_list is set to TRUE.
513 IF FND_API.to_Boolean( p_init_msg_list )
514 THEN
515 FND_MSG_PUB.initialize;
516 END IF;
517
518 -- Debug Message
519 IF (AMS_DEBUG_HIGH_ON) THEN
520
521 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
522 END IF;
523
524
525 -- Initialize API return status to SUCCESS
526 x_return_status := FND_API.G_RET_STS_SUCCESS;
527
528 --
529 -- Api body
530 --
531 -- Debug Message
532 IF (AMS_DEBUG_HIGH_ON) THEN
533
534 AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
535 END IF;
536
537 -- Invoke table handler(AMS_DM_SCORE_RESULTS_PKG.Delete_Row)
538 AMS_DM_SCORE_RESULTS_PKG.Delete_Row(
539 p_SCORE_RESULT_ID => p_SCORE_RESULT_ID);
540 --
541 -- End of API body
542 --
543
544 -- Standard check for p_commit
545 IF FND_API.to_Boolean( p_commit )
546 THEN
547 COMMIT WORK;
548 END IF;
549
550
551 -- Debug Message
552 IF (AMS_DEBUG_HIGH_ON) THEN
553
554 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
555 END IF;
556
557 -- Standard call to get message count and if count is 1, get message info.
558 FND_MSG_PUB.Count_And_Get
559 (p_count => x_msg_count,
560 p_data => x_msg_data
561 );
562 EXCEPTION
563
564 WHEN AMS_Utility_PVT.resource_locked THEN
565 x_return_status := FND_API.g_ret_sts_error;
566 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
567
568 WHEN FND_API.G_EXC_ERROR THEN
569 ROLLBACK TO DELETE_Scoreresult_PVT;
570 x_return_status := FND_API.G_RET_STS_ERROR;
571 -- Standard call to get message count and if count=1, get the message
572 FND_MSG_PUB.Count_And_Get (
573 p_encoded => FND_API.G_FALSE,
574 p_count => x_msg_count,
575 p_data => x_msg_data
576 );
577
578 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
579 ROLLBACK TO DELETE_Scoreresult_PVT;
580 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
581 -- Standard call to get message count and if count=1, get the message
582 FND_MSG_PUB.Count_And_Get (
583 p_encoded => FND_API.G_FALSE,
584 p_count => x_msg_count,
585 p_data => x_msg_data
586 );
587
588 WHEN OTHERS THEN
589 ROLLBACK TO DELETE_Scoreresult_PVT;
590 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
591 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
592 THEN
593 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
594 END IF;
595 -- Standard call to get message count and if count=1, get the message
596 FND_MSG_PUB.Count_And_Get (
597 p_encoded => FND_API.G_FALSE,
598 p_count => x_msg_count,
599 p_data => x_msg_data
600 );
601 End Delete_Scoreresult;
602
603
604
605 -- Hint: Primary key needs to be returned.
606 PROCEDURE Lock_Scoreresult(
607 p_api_version IN NUMBER,
608 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
609
610 x_return_status OUT NOCOPY VARCHAR2,
611 x_msg_count OUT NOCOPY NUMBER,
612 x_msg_data OUT NOCOPY VARCHAR2,
613
614 p_score_result_id IN NUMBER,
615 p_object_version IN NUMBER
616 )
617 IS
618 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Scoreresult';
619 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
620 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
621 l_SCORE_RESULT_ID NUMBER;
622
623 CURSOR c_Scoreresult IS
624 SELECT SCORE_RESULT_ID
625 FROM AMS_DM_SCORE_RESULTS
626 WHERE SCORE_RESULT_ID = p_SCORE_RESULT_ID
627 AND object_version_number = p_object_version
628 FOR UPDATE NOWAIT;
629
630 BEGIN
631
632 -- Debug Message
633 IF (AMS_DEBUG_HIGH_ON) THEN
634
635 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
636 END IF;
637
638 -- Initialize message list if p_init_msg_list is set to TRUE.
639 IF FND_API.to_Boolean( p_init_msg_list )
640 THEN
641 FND_MSG_PUB.initialize;
642 END IF;
643
644 -- Standard call to check for call compatibility.
645 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
646 p_api_version,
647 l_api_name,
648 G_PKG_NAME)
649 THEN
650 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
651 END IF;
652
653
654 -- Initialize API return status to SUCCESS
655 x_return_status := FND_API.G_RET_STS_SUCCESS;
656
657
658 ------------------------ lock -------------------------
659
660 IF (AMS_DEBUG_HIGH_ON) THEN
661
662
663
664 AMS_Utility_PVT.debug_message(l_full_name||': start');
665
666 END IF;
667 OPEN c_Scoreresult;
668
669 FETCH c_Scoreresult INTO l_SCORE_RESULT_ID;
670
671 IF (c_Scoreresult%NOTFOUND) THEN
672 CLOSE c_Scoreresult;
673 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
674 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
675 FND_MSG_PUB.add;
676 END IF;
677 RAISE FND_API.g_exc_error;
678 END IF;
679
680 CLOSE c_Scoreresult;
681
682 -------------------- finish --------------------------
683 FND_MSG_PUB.count_and_get(
684 p_encoded => FND_API.g_false,
685 p_count => x_msg_count,
686 p_data => x_msg_data);
687 IF (AMS_DEBUG_HIGH_ON) THEN
688
689 AMS_Utility_PVT.debug_message(l_full_name ||': end');
690 END IF;
691 EXCEPTION
692
693 WHEN AMS_Utility_PVT.resource_locked THEN
694 x_return_status := FND_API.g_ret_sts_error;
695 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
696
697 WHEN FND_API.G_EXC_ERROR THEN
698 ROLLBACK TO LOCK_Scoreresult_PVT;
699 x_return_status := FND_API.G_RET_STS_ERROR;
700 -- Standard call to get message count and if count=1, get the message
701 FND_MSG_PUB.Count_And_Get (
702 p_encoded => FND_API.G_FALSE,
703 p_count => x_msg_count,
704 p_data => x_msg_data
705 );
706
707 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
708 ROLLBACK TO LOCK_Scoreresult_PVT;
709 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
710 -- Standard call to get message count and if count=1, get the message
711 FND_MSG_PUB.Count_And_Get (
712 p_encoded => FND_API.G_FALSE,
713 p_count => x_msg_count,
714 p_data => x_msg_data
715 );
716
717 WHEN OTHERS THEN
718 ROLLBACK TO LOCK_Scoreresult_PVT;
719 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
720 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
721 THEN
722 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
723 END IF;
724 -- Standard call to get message count and if count=1, get the message
725 FND_MSG_PUB.Count_And_Get (
726 p_encoded => FND_API.G_FALSE,
727 p_count => x_msg_count,
728 p_data => x_msg_data
729 );
730 End Lock_Scoreresult;
731
732
733 PROCEDURE check_scoreresult_uk_items(
734 p_scoreresult_rec IN scoreresult_rec_type,
735 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
736 x_return_status OUT NOCOPY VARCHAR2)
737 IS
738 l_valid_flag VARCHAR2(1);
739
740 BEGIN
741 x_return_status := FND_API.g_ret_sts_success;
742 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
743 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
744 'AMS_DM_SCORE_RESULTS',
745 'SCORE_RESULT_ID = ''' || p_scoreresult_rec.SCORE_RESULT_ID ||''''
746 );
747 ELSE
748 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
749 'AMS_DM_SCORE_RESULTS',
750 'SCORE_RESULT_ID = ''' || p_scoreresult_rec.SCORE_RESULT_ID ||
751 ''' AND SCORE_RESULT_ID <> ' || p_scoreresult_rec.SCORE_RESULT_ID
752 );
753 END IF;
754
755 IF l_valid_flag = FND_API.g_false THEN
756 AMS_Utility_PVT.Error_Message('AMS_API_DUPLICATE_ID', 'ID_FIELD','SCORE_RESULT_ID');
757 x_return_status := FND_API.g_ret_sts_error;
758 END IF;
759
760 END check_scoreresult_uk_items;
761
762 PROCEDURE check_scoreresult_req_items(
763 p_scoreresult_rec IN scoreresult_rec_type,
764 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
765 x_return_status OUT NOCOPY VARCHAR2
766 )
767 IS
768 BEGIN
769 x_return_status := FND_API.g_ret_sts_success;
770
771 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
772 IF p_scoreresult_rec.score_id = FND_API.g_miss_num OR p_scoreresult_rec.score_id IS NULL THEN
773 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_SCORRES_NO_SCORE_ID');
774 x_return_status := FND_API.g_ret_sts_error;
775 END IF;
776
777
778 IF p_scoreresult_rec.decile = FND_API.g_miss_char OR p_scoreresult_rec.decile IS NULL THEN
779 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_SCORRES_NO_DECILE');
780 x_return_status := FND_API.g_ret_sts_error;
781 END IF;
782
783
784 IF p_scoreresult_rec.num_records = FND_API.g_miss_num OR p_scoreresult_rec.num_records IS NULL THEN
785 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_SCORRES_NO_NUM_RECORDS');
786 x_return_status := FND_API.g_ret_sts_error;
787 END IF;
788
789
790 IF p_scoreresult_rec.score = FND_API.g_miss_char OR p_scoreresult_rec.score IS NULL THEN
791 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_SCORRES_NO_SCORE');
792 x_return_status := FND_API.g_ret_sts_error;
793 END IF;
794
795
796 IF p_scoreresult_rec.confidence = FND_API.g_miss_num OR p_scoreresult_rec.confidence IS NULL THEN
797 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_SCORRES_NO_CONFIDENCE');
798 x_return_status := FND_API.g_ret_sts_error;
799 END IF;
800 ELSE -- update mode
801 IF p_scoreresult_rec.score_result_id IS NULL THEN
802 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_SCORRES_NO_ID');
803 x_return_status := FND_API.g_ret_sts_error;
804 END IF;
805
806
807 IF p_scoreresult_rec.score_id IS NULL THEN
808 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_SCORRES_NO_score_id');
809 x_return_status := FND_API.g_ret_sts_error;
810 END IF;
811
812
813 IF p_scoreresult_rec.decile IS NULL THEN
814 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_SCORRES_NO_DECILE');
815 x_return_status := FND_API.g_ret_sts_error;
816 END IF;
817
818
819 IF p_scoreresult_rec.num_records IS NULL THEN
820 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_SCORRES_NO_NUM_RECORDS');
821 x_return_status := FND_API.g_ret_sts_error;
822 END IF;
823
824
825 IF p_scoreresult_rec.score IS NULL THEN
826 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_SCORRES_NO_SCORE');
827 x_return_status := FND_API.g_ret_sts_error;
828 END IF;
829
830
831 IF p_scoreresult_rec.confidence IS NULL THEN
832 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_SCORRES_NO_CONFIDENCE');
833 x_return_status := FND_API.g_ret_sts_error;
834 END IF;
835 END IF;
836
837 END check_scoreresult_req_items;
838
839 PROCEDURE check_scoreresult_FK_items(
840 p_scoreresult_rec IN scoreresult_rec_type,
841 x_return_status OUT NOCOPY VARCHAR2
842 )
843 IS
844 BEGIN
845 x_return_status := FND_API.g_ret_sts_success;
846
847 IF p_scoreresult_rec.score_id <> FND_API.g_miss_num THEN
848 IF AMS_Utility_PVT.check_fk_exists (
849 'ams_dm_scores_all_b',
850 'score_id',
851 p_scoreresult_rec.score_id
852 ) = FND_API.g_false THEN
853 AMS_Utility_PVT.error_message ('AMS_SCORRES_BAD_SCORE_ID');
854 x_return_status := FND_API.g_ret_sts_error;
855 END IF;
856 END IF;
857
858 END check_scoreresult_FK_items;
859
860 PROCEDURE check_scoreresult_Lookup_items(
861 p_scoreresult_rec IN scoreresult_rec_type,
862 x_return_status OUT NOCOPY VARCHAR2
863 )
864 IS
865 BEGIN
866 x_return_status := FND_API.g_ret_sts_success;
867
868 END check_scoreresult_Lookup_items;
869
870 PROCEDURE Check_scoreresult_Items (
871 P_scoreresult_rec IN scoreresult_rec_type,
872 p_validation_mode IN VARCHAR2,
873 x_return_status OUT NOCOPY VARCHAR2
874 )
875 IS
876 BEGIN
877
878 -- Check Items Uniqueness API calls
879
880 check_scoreresult_uk_items(
881 p_scoreresult_rec => p_scoreresult_rec,
882 p_validation_mode => p_validation_mode,
883 x_return_status => x_return_status);
884 IF x_return_status <> FND_API.g_ret_sts_success THEN
885 RETURN;
886 END IF;
887
888 -- Check Items Required/NOT NULL API calls
889
890 check_scoreresult_req_items(
891 p_scoreresult_rec => p_scoreresult_rec,
892 p_validation_mode => p_validation_mode,
893 x_return_status => x_return_status);
894 IF x_return_status <> FND_API.g_ret_sts_success THEN
895 RETURN;
896 END IF;
897 -- Check Items Foreign Keys API calls
898
899 check_scoreresult_FK_items(
900 p_scoreresult_rec => p_scoreresult_rec,
901 x_return_status => x_return_status);
902 IF x_return_status <> FND_API.g_ret_sts_success THEN
903 RETURN;
904 END IF;
905 -- Check Items Lookups
906
907 check_scoreresult_Lookup_items(
908 p_scoreresult_rec => p_scoreresult_rec,
909 x_return_status => x_return_status);
910 IF x_return_status <> FND_API.g_ret_sts_success THEN
911 RETURN;
912 END IF;
913
914 END Check_scoreresult_Items;
915
916
917 PROCEDURE Complete_scoreresult_Rec (
918 P_scoreresult_rec IN scoreresult_rec_type,
919 x_complete_rec OUT NOCOPY scoreresult_rec_type
920 )
921 IS
922 BEGIN
923
924 --
925 -- Check Items API calls
926 NULL;
927 --
928
929 END Complete_scoreresult_Rec;
930
931 PROCEDURE Validate_scoreresult(
932 p_api_version IN NUMBER,
933 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
934 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
935 p_validation_mode IN VARCHAR2,
936 p_scoreresult_rec IN scoreresult_rec_type,
937 x_return_status OUT NOCOPY VARCHAR2,
938 x_msg_count OUT NOCOPY NUMBER,
939 x_msg_data OUT NOCOPY VARCHAR2
940 )
941 IS
942 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Scoreresult';
943 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
944 l_object_version_number NUMBER;
945 l_scoreresult_rec AMS_Scoreresult_PVT.scoreresult_rec_type;
946
947 BEGIN
948 -- Standard Start of API savepoint
949 SAVEPOINT VALIDATE_Scoreresult_;
950
951 -- Standard call to check for call compatibility.
952 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
953 p_api_version,
954 l_api_name,
955 G_PKG_NAME)
956 THEN
957 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
958 END IF;
959
960 -- Initialize message list if p_init_msg_list is set to TRUE.
961 IF FND_API.to_Boolean( p_init_msg_list )
962 THEN
963 FND_MSG_PUB.initialize;
964 END IF;
965 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
966 Check_scoreresult_Items(
967 p_scoreresult_rec => p_scoreresult_rec,
968 p_validation_mode => JTF_PLSQL_API.g_update,
969 x_return_status => x_return_status
970 );
971
972 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
973 RAISE FND_API.G_EXC_ERROR;
974 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
975 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
976 END IF;
977 END IF;
978
979 Complete_scoreresult_Rec(
980 p_scoreresult_rec => p_scoreresult_rec,
981 x_complete_rec => l_scoreresult_rec
982 );
983
984 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
985 Validate_scoreresult_Rec(
986 p_api_version => 1.0,
987 p_init_msg_list => FND_API.G_FALSE,
988 x_return_status => x_return_status,
989 x_msg_count => x_msg_count,
990 x_msg_data => x_msg_data,
991 p_scoreresult_rec => l_scoreresult_rec);
992
993 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
994 RAISE FND_API.G_EXC_ERROR;
995 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
996 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
997 END IF;
998 END IF;
999
1000
1001 -- Debug Message
1002 IF (AMS_DEBUG_HIGH_ON) THEN
1003
1004 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1005 END IF;
1006
1007
1008 -- Initialize API return status to SUCCESS
1009 x_return_status := FND_API.G_RET_STS_SUCCESS;
1010
1011
1012 -- Debug Message
1013 IF (AMS_DEBUG_HIGH_ON) THEN
1014 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1015 END IF;
1016
1017 -- Standard call to get message count and if count is 1, get message info.
1018 FND_MSG_PUB.Count_And_Get
1019 (p_count => x_msg_count,
1020 p_data => x_msg_data
1021 );
1022 EXCEPTION
1023
1024 WHEN AMS_Utility_PVT.resource_locked THEN
1025 x_return_status := FND_API.g_ret_sts_error;
1026 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1027
1028 WHEN FND_API.G_EXC_ERROR THEN
1029 ROLLBACK TO VALIDATE_Scoreresult_;
1030 x_return_status := FND_API.G_RET_STS_ERROR;
1031 -- Standard call to get message count and if count=1, get the message
1032 FND_MSG_PUB.Count_And_Get (
1033 p_encoded => FND_API.G_FALSE,
1034 p_count => x_msg_count,
1035 p_data => x_msg_data
1036 );
1037
1038 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1039 ROLLBACK TO VALIDATE_Scoreresult_;
1040 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1041 -- Standard call to get message count and if count=1, get the message
1042 FND_MSG_PUB.Count_And_Get (
1043 p_encoded => FND_API.G_FALSE,
1044 p_count => x_msg_count,
1045 p_data => x_msg_data
1046 );
1047
1048 WHEN OTHERS THEN
1049 ROLLBACK TO VALIDATE_Scoreresult_;
1050 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1051 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1052 THEN
1053 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1054 END IF;
1055 -- Standard call to get message count and if count=1, get the message
1056 FND_MSG_PUB.Count_And_Get (
1057 p_encoded => FND_API.G_FALSE,
1058 p_count => x_msg_count,
1059 p_data => x_msg_data
1060 );
1061 End Validate_Scoreresult;
1062
1063
1064 PROCEDURE Validate_scoreresult_rec(
1065 p_api_version IN NUMBER,
1066 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1067 x_return_status OUT NOCOPY VARCHAR2,
1068 x_msg_count OUT NOCOPY NUMBER,
1069 x_msg_data OUT NOCOPY VARCHAR2,
1070 p_scoreresult_rec IN scoreresult_rec_type
1071 )
1072 IS
1073 BEGIN
1074 -- Initialize message list if p_init_msg_list is set to TRUE.
1075 IF FND_API.to_Boolean( p_init_msg_list )
1076 THEN
1077 FND_MSG_PUB.initialize;
1078 END IF;
1079
1080 -- Initialize API return status to SUCCESS
1081 x_return_status := FND_API.G_RET_STS_SUCCESS;
1082
1083 -- Hint: Validate data
1084 -- If data not valid
1085 -- THEN
1086 -- x_return_status := FND_API.G_RET_STS_ERROR;
1087
1088 -- Debug Message
1089 IF (AMS_DEBUG_HIGH_ON) THEN
1090
1091 AMS_UTILITY_PVT.debug_message('Private API: Validate_dm_model_rec');
1092 END IF;
1093 -- Standard call to get message count and if count is 1, get message info.
1094 FND_MSG_PUB.Count_And_Get
1095 (p_count => x_msg_count,
1096 p_data => x_msg_data
1097 );
1098 END Validate_scoreresult_Rec;
1099
1100 -- 17-jun-2003 added by nyostos
1101 PROCEDURE insert_percentile_results (
1102 p_score_id IN NUMBER
1103 )
1104 IS
1105 L_API_NAME VARCHAR2(30) := 'insert_percentile_results';
1106
1107 CURSOR c_total_records (p_score_id IN NUMBER) IS
1108 SELECT count(*)
1109 FROM ams_dm_source s
1110 WHERE s.arc_used_for_object = 'SCOR'
1111 AND s.used_for_object_id = p_score_id
1112 AND s.continuous_score IS NOT NULL;
1113
1114 CURSOR c_results (p_score_id IN NUMBER) IS
1115 SELECT (s.continuous_score/100) confidence , party_id
1116 FROM ams_dm_source s
1117 WHERE s.arc_used_for_object = 'SCOR'
1118 AND s.used_for_object_id = p_score_id
1119 AND s.continuous_score IS NOT NULL
1120 ORDER BY s.continuous_score desc;
1121
1122 l_total_records NUMBER := 0;
1123 l_record_count NUMBER := 0;
1124 l_records_per_pct NUMBER := 0;
1125 l_num_records_cum NUMBER := 0;
1126 l_confidence NUMBER := 0;
1127 l_confidence_cum NUMBER := 0;
1128 l_avg_confidence NUMBER := 0;
1129 l_avg_confidence_cum NUMBER := 0;
1130 l_percentile NUMBER := 1;
1131 l_percentile_multiplier NUMBER := 1;
1132 l_counter NUMBER := 1;
1133 l_score_id NUMBER := p_score_id;
1134 l_party_ids VARCHAR2(4000) := '';
1135 l_sql_str VARCHAR2(5000) := '';
1136 l_temp_party_id_list dbms_sql.Varchar2_Table;
1137 l_party_id_list dbms_sql.Varchar2_Table;
1138 l_conf_list dbms_sql.NUMBER_table;
1139 l_bs_rows NUMBER := 0;
1140 l_ctr NUMBER := 0;
1141 l_ctr_2 NUMBER :=0;
1142 l_percentile_list dbms_sql.NUMBER_table;
1143 l_record_count_list dbms_sql.NUMBER_table;
1144 l_num_recs_cum_list dbms_sql.NUMBER_table;
1145 l_avg_conf_list dbms_sql.NUMBER_table;
1146 l_avg_conf_cum_list dbms_sql.NUMBER_table;
1147
1148 BEGIN
1149
1150 -- Standard Start of API savepoint
1151 SAVEPOINT insert_percentile_results;
1152
1153 -- Debug Message
1154 IF (AMS_DEBUG_HIGH_ON) THEN
1155 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' start');
1156 END IF;
1157
1158 -- clear results from any previous execution
1159 DELETE FROM ams_dm_score_pct_results
1160 WHERE score_id = l_score_id;
1161
1162 -- First get the total number of parties in ams_dm_scource
1163 OPEN c_total_records (l_score_id);
1164 FETCH c_total_records INTO l_total_records;
1165 CLOSE c_total_records;
1166
1167 -- If there are no records return
1168 IF l_total_records <= 0 THEN
1169 IF (AMS_DEBUG_HIGH_ON) THEN
1170 AMS_UTILITY_PVT.debug_message('No score results found in AMS_DM_SOURCE. Exiting.' );
1171 END IF;
1172 RETURN;
1173 END IF;
1174
1175 l_bs_rows:= fnd_profile.value_specific('AMS_BATCH_SIZE');
1176 IF (l_bs_rows IS NULL OR l_bs_rows < 1) THEN
1177 l_bs_rows :=1000;
1178 END IF;
1179
1180 -- Calculate the number of records in each percent
1181 l_records_per_pct := CEIL(l_total_records / 100);
1182
1183 IF l_total_records < 100 THEN
1184 l_percentile_multiplier := ROUND(100 / l_total_records, 4);
1185 END IF;
1186
1187 -- Debug Message
1188 IF (AMS_DEBUG_HIGH_ON) THEN
1189 AMS_UTILITY_PVT.debug_message('Number of Parties in Scored Population : ' || l_total_records );
1190 AMS_UTILITY_PVT.debug_message('Number of Parties per Percentile : ' || l_records_per_pct );
1191 AMS_UTILITY_PVT.debug_message('Percentile Multiplier: ' || l_percentile_multiplier );
1192 END IF;
1193
1194
1195 --FOR l_result_rec IN c_results (l_score_id) LOOP
1196
1197 OPEN c_results(l_score_id);
1198 LOOP
1199 FETCH c_results BULK COLLECT INTO l_conf_list, l_party_id_list LIMIT l_bs_rows;
1200
1201 FOR i IN 1..l_conf_list.COUNT LOOP
1202
1203 l_record_count := l_record_count + 1;
1204 l_num_records_cum := l_num_records_cum + 1;
1205 l_confidence := l_confidence + l_conf_list(i);
1206 l_confidence_cum := l_confidence_cum + l_conf_list(i);
1207
1208 l_ctr := l_ctr+1;
1209 l_temp_party_id_list(l_ctr) := l_party_id_list(i);
1210
1211 /* if LENGTH(l_party_ids) > 0 THEN
1212 l_party_ids := l_party_ids || ', ' || l_result_rec.party_id;
1213 ELSE
1214 l_party_ids := l_result_rec.party_id;
1215 END IF; */
1216
1217 IF (l_record_count >= l_records_per_pct OR l_num_records_cum = l_total_records) THEN
1218
1219 l_ctr_2 := l_ctr_2 +1;
1220
1221 -- Calculate percentile
1222 IF l_num_records_cum = l_total_records THEN
1223 l_percentile := 100;
1224 ELSE
1225 l_percentile := TRUNC(l_counter * l_percentile_multiplier);
1226 END IF;
1227
1228 -- Calculate average confidence for the percentile
1229 l_avg_confidence := l_confidence / l_record_count;
1230
1231 -- Calculate cumulative average confidence up to this percentile
1232 l_avg_confidence_cum := l_confidence_cum / l_num_records_cum;
1233
1234 -- Build the lists required for the bulk insert
1235 l_percentile_list(l_ctr_2) := l_percentile;
1236 l_record_count_list(l_ctr_2) := l_record_count;
1237 l_num_recs_cum_list(l_ctr_2) := l_num_records_cum;
1238 l_avg_conf_list(l_ctr_2) := l_avg_confidence;
1239 l_avg_conf_cum_list(l_ctr_2) := l_avg_confidence_cum;
1240
1241 /*INSERT INTO ams_dm_score_pct_results (
1242 score_result_id,
1243 last_update_date,
1244 last_updated_by,
1245 creation_date,
1246 created_by,
1247 last_update_login,
1248 object_version_number,
1249 score_id,
1250 percentile,
1251 num_records,
1252 num_records_cum,
1253 confidence,
1254 confidence_cum,
1255 random_result
1256 ) VALUES (
1257 ams_dm_score_pct_results_s.NEXTVAL,
1258 SYSDATE,
1259 FND_GLOBAL.user_id,
1260 SYSDATE,
1261 FND_GLOBAL.user_id,
1262 FND_GLOBAL.conc_login_id,
1263 1,
1264 l_score_id,
1265 l_percentile,
1266 l_record_count,
1267 l_num_records_cum,
1268 l_avg_confidence,
1269 l_avg_confidence_cum,
1270 0
1271 ); */
1272
1273 --update ams_dm_source set percentile = l_percentile
1274 --WHERE arc_used_for_object = 'SCOR'
1275 --AND used_for_object_id = p_score_id
1276 --AND to_char(party_id) IN (l_party_ids);
1277 /*** reverting to old code until batch solution can be implemented
1278 target build 38
1279 l_sql_str := 'update ams_dm_source set percentile = :1' ;
1280 l_sql_str := l_sql_str || ' WHERE arc_used_for_object = ''SCOR''';
1281 l_sql_str := l_sql_str || ' AND used_for_object_id = :2';
1282 l_sql_str := l_sql_str || ' AND party_id IN (:3)' ;
1283 ***/
1284 /* l_sql_str := 'update ams_dm_source set percentile = :1' ;
1285 l_sql_str := l_sql_str || ' WHERE arc_used_for_object = ''SCOR''';
1286 l_sql_str := l_sql_str || ' AND used_for_object_id = :2';
1287 l_sql_str := l_sql_str || ' AND party_id IN (' || l_party_ids || ')' ; */
1288
1289 -- bug 3588127 - invalid package due to incorrect commenting
1290 -- reverted bind change for party ids; need to implement batch
1291 -- bug 3569505 - SQL Bind Vars fix - pkanukol
1292 -- EXECUTE IMMEDIATE l_sql_str using l_percentile, p_score_id, l_party_ids;
1293 -- EXECUTE IMMEDIATE l_sql_str using l_percentile, p_score_id;
1294
1295 FORALL k in 1..l_temp_party_id_list.COUNT
1296 update ams_dm_source set percentile = l_percentile
1297 WHERE arc_used_for_object = 'SCOR' AND used_for_object_id = l_score_id AND party_id = l_temp_party_id_list(k) ;
1298
1299 -- reset record counter and confidence
1300 l_record_count := 0;
1301 l_confidence := 0;
1302 -- l_party_ids := '';
1303 l_ctr := 0;
1304 l_temp_party_id_list.delete;
1305
1306 -- Increment counter
1307 l_counter := l_counter + 1;
1308
1309 END IF;
1310 END LOOP;
1311 l_conf_list.delete;
1312 l_party_id_list.delete;
1313 EXIT WHEN c_results%NOTFOUND;
1314 END LOOP;
1315 CLOSE c_results;
1316
1317
1318 -- Debug Message
1319 IF (AMS_DEBUG_HIGH_ON) THEN
1320 AMS_UTILITY_PVT.debug_message('Inserted: ' || l_total_records || ' Records');
1321 END IF;
1322
1323 -- If some records were inserted, then insert a record for the zero percentile
1324 -- to start the graph from the origin
1325 IF l_total_records > 0 THEN
1326 FORALL h IN 1..l_percentile_list.COUNT
1327 INSERT INTO ams_dm_score_pct_results (
1328 score_result_id,
1329 last_update_date,
1330 last_updated_by,
1331 creation_date,
1332 created_by,
1333 last_update_login,
1334 object_version_number,
1335 score_id,
1336 percentile,
1337 num_records,
1338 num_records_cum,
1339 confidence,
1340 confidence_cum,
1341 random_result
1342 ) VALUES (
1343 ams_dm_score_pct_results_s.NEXTVAL,
1344 SYSDATE,
1345 FND_GLOBAL.user_id,
1346 SYSDATE,
1347 FND_GLOBAL.user_id,
1348 FND_GLOBAL.conc_login_id,
1349 1,
1350 l_score_id,
1351 l_percentile_list(h),
1352 l_record_count_list(h),
1353 l_num_recs_cum_list(h),
1354 l_avg_conf_list(h),
1355 l_avg_conf_cum_list(h),
1356 0
1357 );
1358 INSERT INTO ams_dm_score_pct_results (
1359 score_result_id,
1360 last_update_date,
1361 last_updated_by,
1362 creation_date,
1363 created_by,
1364 last_update_login,
1365 object_version_number,
1366 score_id,
1367 percentile,
1368 num_records,
1369 num_records_cum,
1370 confidence,
1371 confidence_cum,
1372 random_result
1373 ) VALUES (
1374 ams_dm_score_pct_results_s.NEXTVAL,
1375 SYSDATE,
1376 FND_GLOBAL.user_id,
1377 SYSDATE,
1378 FND_GLOBAL.user_id,
1379 FND_GLOBAL.conc_login_id,
1380 1,
1381 l_score_id,
1382 0,
1383 0,
1384 0,
1385 0,
1386 0,
1387 0
1388 );
1389
1390 l_percentile_list.delete;
1391 l_record_count_list.delete;
1392 l_num_recs_cum_list.delete;
1393 l_avg_conf_list.delete;
1394 l_avg_conf_cum_list.delete;
1395 l_ctr_2 := 0;
1396 IF (AMS_DEBUG_HIGH_ON) THEN
1397 AMS_UTILITY_PVT.debug_message('Inserted record for zeroth percentile: ');
1398 AMS_UTILITY_PVT.debug_message('Cumulative Average Confidence for 100th percentile: ' || l_avg_confidence_cum );
1399 END IF;
1400
1401 UPDATE ams_dm_score_pct_results
1402 SET RANDOM_RESULT = l_avg_confidence_cum
1403 WHERE score_id = l_score_id
1404 AND percentile > 0;
1405 END IF;
1406
1407 -- Debug Message
1408 IF (AMS_DEBUG_HIGH_ON) THEN
1409 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' end');
1410 END IF;
1411
1412 EXCEPTION
1413 WHEN FND_API.G_EXC_ERROR THEN
1414 ROLLBACK TO insert_percentile_results;
1415 AMS_UTILITY_PVT.debug_message(l_api_name || ': Error ');
1416 RAISE FND_API.G_EXC_ERROR;
1417
1418 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1419 ROLLBACK TO insert_percentile_results;
1420 AMS_UTILITY_PVT.debug_message(l_api_name || ': Error ');
1421 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1422
1423 WHEN OTHERS THEN
1424 ROLLBACK TO insert_percentile_results;
1425 AMS_UTILITY_PVT.debug_message(l_api_name || ': Error ');
1426 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1427
1428 END insert_percentile_results;
1429
1430
1431 PROCEDURE summarize_results (
1432 p_api_version IN NUMBER,
1433 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1434 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1435 x_return_status OUT NOCOPY VARCHAR2,
1436 x_msg_count OUT NOCOPY NUMBER,
1437 x_msg_data OUT NOCOPY VARCHAR2,
1438 p_score_id IN NUMBER
1439 )
1440 IS
1441 L_API_NAME VARCHAR2(30) := 'Summarize_Results';
1442 L_API_VERSION_NUMBER NUMBER := 1.0;
1443
1444 CURSOR c_results (p_score_id IN NUMBER) IS
1445 SELECT s.decile,
1446 s.score_result,
1447 AVG (s.continuous_score) confidence,
1448 COUNT(*) row_count
1449 FROM ams_dm_source s
1450 WHERE s.arc_used_for_object = 'SCOR'
1451 AND s.used_for_object_id = p_score_id
1452 AND s.decile IS NOT NULL
1453 AND s.continuous_score IS NOT NULL
1454 GROUP BY s.decile, s.score_result;
1455
1456 CURSOR c_model_type (p_score_id IN NUMBER) IS
1457 SELECT m.model_type, m.model_id
1458 FROM ams_dm_scores_all_b s, ams_dm_models_all_b m
1459 WHERE s.score_id = p_score_id
1460 AND m.model_id = s.model_id;
1461
1462 l_model_id NUMBER;
1463 l_model_type VARCHAR2(30);
1464 l_is_org_prod BOOLEAN;
1465 BEGIN
1466 -- Standard Start of API savepoint
1467 SAVEPOINT summarize_Scoreresult_PVT;
1468
1469 -- Standard call to check for call compatibility.
1470 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1471 p_api_version,
1472 l_api_name,
1473 G_PKG_NAME)
1474 THEN
1475 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1476 END IF;
1477
1478 -- Initialize message list if p_init_msg_list is set to TRUE.
1479 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1480 FND_MSG_PUB.initialize;
1481 END IF;
1482
1483 -- Debug Message
1484 IF (AMS_DEBUG_HIGH_ON) THEN
1485 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' start');
1486 END IF;
1487
1488
1489 -- Initialize API return status to SUCCESS
1490 x_return_status := FND_API.G_RET_STS_SUCCESS;
1491
1492 -- clear results from any previous execution
1493 DELETE FROM ams_dm_score_results
1494 WHERE score_id = p_score_id;
1495
1496 FOR l_result_rec IN c_results (p_score_id) LOOP
1497 -- can't batch insert because of group by
1498 -- constraints on the sequence.
1499 INSERT INTO ams_dm_score_results (
1500 score_result_id,
1501 last_update_date,
1502 last_updated_by,
1503 creation_date,
1504 created_by,
1505 last_update_login,
1506 object_version_number,
1507 score_id,
1508 decile,
1509 num_records,
1510 score,
1511 confidence
1512 ) VALUES (
1513 ams_dm_score_results_s.NEXTVAL,
1514 SYSDATE,
1515 FND_GLOBAL.user_id,
1516 SYSDATE,
1517 FND_GLOBAL.user_id,
1518 FND_GLOBAL.conc_login_id,
1519 1,
1520 p_score_id,
1521 l_result_rec.decile,
1522 l_result_rec.row_count,
1523 l_result_rec.score_result,
1524 l_result_rec.confidence
1525 );
1526 END LOOP;
1527
1528 -- Debug Message
1529 IF (AMS_DEBUG_HIGH_ON) THEN
1530 AMS_UTILITY_PVT.debug_message('Inserted Decile Records ');
1531 END IF;
1532
1533 --kbasavar 12/30/2003 Modified to Call insert_percentile_results only for models enabled for optimal targeting
1534 OPEN c_model_type(p_score_id);
1535 FETCH c_model_type INTO l_model_type,l_model_id;
1536 CLOSE c_model_type;
1537
1538 AMS_DMSelection_PVT.is_org_prod_affn(
1539 p_model_id => l_model_id,
1540 x_is_org_prod => l_is_org_prod
1541 );
1542
1543 IF l_model_type IN (G_MODEL_TYPE_EMAIL, G_MODEL_TYPE_DMAIL, G_MODEL_TYPE_TELEM, G_MODEL_TYPE_PROD) AND l_is_org_prod = FALSE THEN
1544 -- 17-Jun-2003 nyostos Insert percentile records
1545 insert_percentile_results (p_score_id);
1546 END IF;
1547 --kbasavar 12/30/2003 End Modified to Call insert_percentile_results only for models enabled for optimal targeting
1548
1549 IF p_commit = FND_API.g_true THEN
1550 COMMIT;
1551 END IF;
1552
1553 -- Debug Message
1554 IF (AMS_DEBUG_HIGH_ON) THEN
1555 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' end');
1556 END IF;
1557
1558 EXCEPTION
1559 WHEN FND_API.G_EXC_ERROR THEN
1560 ROLLBACK TO summarize_Scoreresult_PVT;
1561 x_return_status := FND_API.G_RET_STS_ERROR;
1562 -- Standard call to get message count and if count=1, get the message
1563 FND_MSG_PUB.Count_And_Get (
1564 p_encoded => FND_API.G_FALSE,
1565 p_count => x_msg_count,
1566 p_data => x_msg_data
1567 );
1568 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1569 ROLLBACK TO summarize_Scoreresult_PVT;
1570 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1571 -- Standard call to get message count and if count=1, get the message
1572 FND_MSG_PUB.Count_And_Get (
1573 p_encoded => FND_API.G_FALSE,
1574 p_count => x_msg_count,
1575 p_data => x_msg_data
1576 );
1577 WHEN OTHERS THEN
1578 ROLLBACK TO summarize_Scoreresult_PVT;
1579 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1580 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1581 THEN
1582 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1583 END IF;
1584 -- Standard call to get message count and if count=1, get the message
1585 FND_MSG_PUB.Count_And_Get (
1586 p_encoded => FND_API.G_FALSE,
1587 p_count => x_msg_count,
1588 p_data => x_msg_data
1589 );
1590 END Summarize_Results;
1591
1592
1593 PROCEDURE parse_tree_node_str (
1594 p_tree_node_str IN VARCHAR2,
1595 x_tree_table OUT NOCOPY tree_node_table_type,
1596 x_tab_ctr OUT NOCOPY NUMBER
1597 ) IS
1598 v_tree_node VARCHAR2(10);
1599 l_num NUMBER;
1600 v_num NUMBER;
1601 l_tree_node_str VARCHAR2(400);
1602 l_tab_ctr NUMBER := 0;
1603 BEGIN
1604 l_num := 0;
1605 l_tree_node_str := p_tree_node_str || ',';
1606
1607 WHILE l_num < LENGTH(l_tree_node_str) LOOP
1608 v_num := l_num + 1;
1609 l_num := INSTR(l_tree_node_str, ',', l_num + 1);
1610 v_tree_node := SUBSTR(l_tree_node_str, v_num,l_num - v_num);
1611
1612 l_tab_ctr := l_tab_ctr + 1;
1613 x_tree_table(l_tab_ctr) := TO_NUMBER(v_tree_node);
1614 END LOOP;
1615
1616 x_tab_ctr := l_tab_ctr;
1617 END parse_tree_node_str;
1618
1619 PROCEDURE generate_list(
1620 p_score_id IN NUMBER,
1621 p_model_type IN VARCHAR2, -- misnomer of variable; should be target group type
1622 p_tree_node_str IN VARCHAR2,
1623 p_list_name IN VARCHAR2 ,
1624 p_owner_user_id IN NUMBER,
1625 x_return_status OUT NOCOPY VARCHAR2,
1626 x_msg_count OUT NOCOPY NUMBER,
1627 x_msg_data OUT NOCOPY VARCHAR2,
1628 x_list_header_id OUT NOCOPY VARCHAR2
1629 ) IS
1630 l_rule_id NUMBER;
1631 l_model_id NUMBER;
1632 l_sql_str VARCHAR2(2000);
1633 l_master_type VARCHAR2(30);
1634 l_primary_key VARCHAR2(30);
1635
1636 l_tree_table tree_node_table_type;
1637 l_tab_ctr NUMBER;
1638
1639 CURSOR c_ListSourceTypeCode IS
1640 SELECT L.SOURCE_TYPE_CODE, l.source_object_pk_field,T.target_id
1641 FROM AMS_DM_SCORES_ALL_B S, AMS_DM_MODELS_ALL_B M, AMS_DM_TARGETS_B T, AMS_LIST_SRC_TYPES L
1642 WHERE S.SCORE_ID = p_score_id
1643 AND S.MODEL_ID = M.MODEL_ID
1644 AND M.TARGET_ID = T.TARGET_ID
1645 AND T.DATA_SOURCE_ID = L.LIST_SOURCE_TYPE_ID;
1646
1647 CURSOR c_model_type(p_model_id IN NUMBER) IS
1648 SELECT model_type
1649 FROM ams_dm_models_vl
1650 WHERE model_id=p_model_id;
1651
1652 CURSOR c_model_id(p_scor_id IN NUMBER) IS
1653 SELECT model_id
1654 FROM AMS_DM_SCORES_ALL_B
1655 WHERE score_id=p_scor_id;
1656
1657 l_model_type VARCHAR2(30);
1658 l_is_b2b BOOLEAN := FALSE;
1659 l_modl_id NUMBER;
1660 l_target_id NUMBER;
1661 l_seeded_target BOOLEAN := FALSE;
1662 L_SEEDED_ID_THRESHOLD CONSTANT NUMBER := 10000;
1663
1664 CURSOR c_scoreListId IS
1665 SELECT AMS_DM_SCORE_LISTS_S.NEXTVAL
1666 FROM dual;
1667 l_insertSql VARCHAR2(1000);
1668 l_scoreListId NUMBER;
1669
1670 BEGIN
1671 Fnd_Msg_Pub.initialize;
1672
1673 IF (AMS_DEBUG_HIGH_ON) THEN
1674 AMS_UTILITY_PVT.debug_message('Private API: generate_list start');
1675 END IF;
1676
1677 OPEN c_ListSourceTypeCode;
1678 FETCH c_ListSourceTypeCode INTO l_master_type, l_primary_key, l_target_id;
1679
1680 IF (c_ListSourceTypeCode%NOTFOUND) THEN
1681 CLOSE c_ListSourceTypeCode;
1682 RAISE FND_API.G_EXC_ERROR;
1683 END IF;
1684
1685 CLOSE c_ListSourceTypeCode;
1686
1687 OPEN c_model_id(p_score_id);
1688 FETCH c_model_id into l_modl_id;
1689 CLOSE c_model_id;
1690
1691 AMS_DMSelection_PVT.is_b2b_data_source(
1692 p_model_id => l_modl_id,
1693 x_is_b2b => l_is_b2b
1694 );
1695
1696 IF l_target_id < L_SEEDED_ID_THRESHOLD THEN
1697 l_seeded_target := TRUE;
1698 END IF;
1699
1700 OPEN c_model_type(l_modl_id);
1701 FETCH c_model_type INTO l_model_type;
1702 CLOSE c_model_type;
1703
1704 IF (AMS_DEBUG_HIGH_ON) THEN
1705 IF l_is_b2b then
1706 AMS_Utility_PVT.debug_message ('B2B');
1707 else
1708 AMS_Utility_PVT.debug_message ('B2C');
1709 end if;
1710 AMS_Utility_PVT.debug_message ('Model Type: ' || l_model_type);
1711 END IF;
1712
1713 -- start change rosharma 20-jan-2004 bug # 3380057
1714 -- IF l_is_b2b AND l_model_type = 'CUSTOMER_PROFITABILITY' THEN
1715 IF l_is_b2b AND l_seeded_target AND l_model_type = 'CUSTOMER_PROFITABILITY' THEN
1716 -- end change rosharma 20-jan-2004 bug # 3380057
1717 -- nyostos Sep 15, 2003 - Use Global Temporary Table
1718 -- l_sql_str := 'SELECT ams_dm_org_contacts_stg.party_id, '''|| l_master_type || '''';
1719 -- l_sql_str := l_sql_str || ' FROM ams_dm_org_contacts_stg, ams_dm_source';
1720 -- l_sql_str := l_sql_str || ' WHERE ams_dm_org_contacts_stg.arc_object_used_by= ''SCOR''';
1721 -- l_sql_str := l_sql_str || ' AND ams_dm_org_contacts_stg.object_used_by_id = '|| p_score_id;
1722 -- l_sql_str := l_sql_str || ' AND ams_dm_source.arc_used_for_object = ''SCOR''';
1723 -- l_sql_str := l_sql_str || ' AND ams_dm_source.used_for_object_id = '|| p_score_id;
1724 -- l_sql_str := l_sql_str || ' AND ams_dm_source.party_id = ams_dm_org_contacts_stg.org_party_id';
1725 -- l_sql_str := l_sql_str || ' AND ams_dm_source.decile IN (' || p_tree_node_str || ')';
1726 l_sql_str := 'SELECT ams_dm_org_contacts.party_id, '''|| l_master_type || '''';
1727 l_sql_str := l_sql_str || ' FROM ams_dm_org_contacts, ams_dm_source';
1728 l_sql_str := l_sql_str || ' WHERE ams_dm_org_contacts.arc_object_used_by= ''SCOR''';
1729 l_sql_str := l_sql_str || ' AND ams_dm_org_contacts.object_used_by_id = '|| p_score_id;
1730 l_sql_str := l_sql_str || ' AND ams_dm_source.arc_used_for_object = ''SCOR''';
1731 l_sql_str := l_sql_str || ' AND ams_dm_source.used_for_object_id = '|| p_score_id;
1732 l_sql_str := l_sql_str || ' AND ams_dm_source.party_id = ams_dm_org_contacts.org_party_id';
1733 l_sql_str := l_sql_str || ' AND ams_dm_source.decile IN (' || p_tree_node_str || ')';
1734
1735 -- G_LIST_SOURCE_NAME := 'AMS_DM_ORG_CONTACTS_STG';
1736 G_LIST_SOURCE_NAME := 'ams_dm_org_contacts';
1737
1738 ELSE
1739 l_sql_str := 'SELECT party_id, '''|| l_master_type || '''';
1740 l_sql_str := l_sql_str || ' FROM ams_dm_source';
1741 l_sql_str := l_sql_str || ' WHERE arc_used_for_object = ''SCOR''';
1742 l_sql_str := l_sql_str || ' AND used_for_object_id = '|| p_score_id;
1743 l_sql_str := l_sql_str || ' AND decile IN (' || p_tree_node_str || ')';
1744 END IF;
1745
1746 IF (AMS_DEBUG_HIGH_ON) THEN
1747 AMS_Utility_PVT.debug_message ('generate_list SQL = ' || l_sql_str);
1748 END IF;
1749
1750 --
1751 -- choang - 03-apr-2003
1752 -- p_primary_key is the party_id from ams_dm_source which
1753 -- holds the primary key defined from the data source. For
1754 -- seeded data sources, this holds the party id from TCA.
1755 AMS_LISTGENERATION_PKG.CREATE_LIST (
1756 p_api_version => 1.0,
1757 p_init_msg_list => FND_API.G_FALSE,
1758 p_commit => FND_API.G_TRUE,
1759 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1760 p_list_name => p_list_name,
1761 p_owner_user_id => p_owner_user_id,
1762 p_sql_string => l_sql_str,
1763 p_primary_key => 'PARTY_ID',
1764 p_source_object_name => G_LIST_SOURCE_NAME,
1765 p_master_type => l_master_type,
1766 x_return_status => x_return_status,
1767 x_msg_count => x_msg_count,
1768 x_msg_data => x_msg_data,
1769 x_list_header_id => x_list_header_id
1770 );
1771 IF x_return_status = Fnd_Api.G_RET_STS_ERROR THEN
1772 RAISE Fnd_Api.G_EXC_ERROR;
1773 ELSIF x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
1774 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1775 ELSE -- If List Generation is successful then insert into AMS_DM_SCORE_LISTS. kbasavar 1/22/2004 for 3363509
1776
1777 l_insertSql := 'INSERT INTO AMS_DM_SCORE_LISTS(SCORE_LIST_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,';
1778 l_insertSql := l_insertSql || 'CREATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER, SCORE_ID, LIST_HEADER_ID)' ;
1779 l_insertSql := l_insertSql || ' VALUES(AMS_DM_SCORE_LISTS_S.NEXTVAL, SYSDATE, :1, SYSDATE, :2 ' ;
1780 l_insertSql := l_insertSql || ', :3 , 1, :4, :5)';
1781
1782 IF (AMS_DEBUG_HIGH_ON) THEN
1783 AMS_Utility_PVT.debug_message ('SCORE LISTS SQL = ' || l_insertSql);
1784 END IF;
1785
1786 EXECUTE Immediate l_insertSql USING FND_GLOBAL.USER_ID,FND_GLOBAL.USER_ID,FND_GLOBAL.CONC_LOGIN_ID,p_score_id, x_list_header_id ;
1787
1788 END IF;
1789 EXCEPTION
1790 WHEN Fnd_Api.G_EXC_ERROR THEN
1791 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1792 -- Standard call to get message count and if count=1, get the message
1793 Fnd_Msg_Pub.Count_And_Get (
1794 p_encoded => Fnd_Api.G_FALSE,
1795 p_count => x_msg_count,
1796 p_data => x_msg_data
1797 );
1798
1799 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1800 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1801 -- Standard call to get message count and if count=1, get the message
1802 Fnd_Msg_Pub.Count_And_Get (
1803 p_encoded => Fnd_Api.G_FALSE,
1804 p_count => x_msg_count,
1805 p_data => x_msg_data
1806 );
1807 END generate_list;
1808
1809
1810 PROCEDURE generate_list (
1811 p_score_id IN NUMBER,
1812 p_tree_node_str IN VARCHAR2,
1813 p_list_name IN VARCHAR2 ,
1814 x_return_status OUT NOCOPY VARCHAR2,
1815 x_msg_count OUT NOCOPY NUMBER,
1816 x_msg_data OUT NOCOPY VARCHAR2,
1817 x_list_header_id OUT NOCOPY VARCHAR2
1818 )
1819 IS
1820 l_model_type VARCHAR2(30); -- misnomer of variable; should be target group type
1821 l_owner_user_id NUMBER;
1822
1823 CURSOR c_model_type (p_score_id IN NUMBER) IS
1824 SELECT m.target_group_type, s.owner_user_id
1825 FROM ams_dm_scores_all_b s, ams_dm_models_all_b m
1826 WHERE s.score_id = p_score_id
1827 AND m.model_id = s.model_id;
1828 BEGIN
1829 OPEN c_model_type (p_score_id);
1830 FETCH c_model_type INTO l_model_type, l_owner_user_id;
1831 CLOSE c_model_type;
1832
1833 generate_list (
1834 p_score_id => p_score_id,
1835 p_model_type => l_model_type,
1836 p_tree_node_str => p_tree_node_str,
1837 p_list_name => p_list_name,
1838 p_owner_user_id => l_owner_user_id,
1839 x_return_status => x_return_status,
1840 x_msg_count => x_msg_count,
1841 x_msg_data => x_msg_data,
1842 x_list_header_id => x_list_header_id
1843 );
1844 IF x_return_status = Fnd_Api.G_RET_STS_ERROR THEN
1845 RAISE Fnd_Api.G_EXC_ERROR;
1846 ELSIF x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
1847 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1848 END IF;
1849 EXCEPTION
1850 WHEN Fnd_Api.G_EXC_ERROR THEN
1851 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1852 -- Standard call to get message count and if count=1, get the message
1853 Fnd_Msg_Pub.Count_And_Get (
1854 p_encoded => Fnd_Api.G_FALSE,
1855 p_count => x_msg_count,
1856 p_data => x_msg_data
1857 );
1858
1859 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1860 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1861 -- Standard call to get message count and if count=1, get the message
1862 Fnd_Msg_Pub.Count_And_Get (
1863 p_encoded => Fnd_Api.G_FALSE,
1864 p_count => x_msg_count,
1865 p_data => x_msg_data
1866 );
1867 END generate_list;
1868
1869
1870 END AMS_Scoreresult_PVT;