[Home] [Help]
PACKAGE BODY: APPS.AMS_DMSOURCE_PVT
Source
1 PACKAGE BODY AMS_DMSource_PVT as
2 /* $Header: amsvdsrb.pls 120.0 2005/05/31 14:45:59 appldev noship $ */
3 -- Start of Comments
4 -- Package name : AMS_DMSource_PVT
5 -- Purpose :
6 -- History :
7 -- 25-Jan-2001 choang Removed req validation of model_type.
8 -- 26-Jan-2001 choang Added increment of object ver num in update api.
9 -- 29-Jan-2001 choang Removed return statement from item validation.
10 -- 30-jan-2001 choang Changed p_tree_node to p_rule_id.
11 -- 28-Feb-2001 choang Removed model_type from validation.
12 -- 10-Jul-2001 choang Added bin_probability.
13 -- 12-Jul-2001 choang Added process_scores.
14 -- 26-Jul-2001 choang Added generate_odm_input_views, randomize_build_data
15 -- 16-Aug-2001 choang Added columns to view definitions.
16 -- 31-Aug-2001 choang Changed interest_others_flag to interest_other_flag.
17 -- 18-Oct-2001 choang Fixed training_data_flag updation logic.
18 -- 26-Nov-2001 choang Changed process_scores logic to use local staging table
19 -- for performance.
20 -- 07-Jan-2002 choang Removed security group id
21 -- 09-May-2002 choang Changed training/test data to 70/30 ratio
22 -- 17-May-2002 choang bug 2380113: g_user_id and g_login_id removed
23 -- 07-Jun-2002 choang Added support for data mining data sources.
24 -- 15-May-2003 rosharma Bug # 2961532.
25 -- 24-June-2003 kbasavar For Balanced Data Set enhancement.
26 -- 28-Jul-2003 nyostos Added PERCENTILE column and code in bin_probability to calculate the
27 -- score percentile.
28 -- 15-Sep-2003 nyostos Changes related to parallel mining processes using Global Temp Tables.
29 -- 20-Sep-2003 rosharma Changes related to Audience data source uptake.
30 -- 22-Sep-2003 nyostos Fixed error handling in generate_odm_input_views.
31 -- 08-Oct-2003 nyostos Changed logic to CREATE or REPLACE synonyms for ODM views in case
32 -- synonyms are not dropped because of errors in build/apply.
33 -- 31-Oct-2003 kbasavar Changed get_from_where_clause to handle CUSTOMER PROFITABILITY MODEL
34 -- 31-Oct-2003 rosharma Changed get_select_fields to filter out attributes which are not NUMBER or VARCHAR2
35 -- 18-Nov-2003 rosharma obsoleted profile AMS_ODM_OUTPUT_SCHEMA, use ODM schema always
36 -- 30-Dec-2003 kbasavar grant select privilage and create synonym only if single instance setup.
37 -- Basically check if profile AMS_DM_USE_DBLINK is N or null
38 -- 23-Jan-2004 rosharma Bug # 3390720
39 -- 12-Feb-2004 rosharma Fixed bug # 3436093
40 -- 24-Feb-2004 rosharma Fixed bug # 3461297
41 -- 24-Feb-2004 kbasavar Fixed bug # 3466690
42 -- 08-Nov-2004 spendem Fixed bug # 4027150 (Exclude source_id and target_value from selected fields)
43 -- (Added table alias before the column name training_data_flag in create_build_views procedure )
44
45 -- 17-Dec-2004 kbasavar Perf Bug 4074433 Modified get_from_where_clause to return two where clause strings
46 -- one for creating view and the other for querying.String for selections with bind variables.
47 -- NOTE :
48 -- End of Comments
49
50
51 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_DMSource_PVT';
52 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvdsrb.pls';
53
54 /***
55 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
56 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
57 ***/
58
59
60 --
61 -- PURPOSE
62 -- Randomly set the build data flag to indicate that
63 -- an individual row in model data selection is to be
64 -- used for either training or testing.
65 --
66 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
67 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
68 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
69
70
71 -- ODM External Schema Lookup Code
72 G_ODM_SCHEMA_LOOKUP VARCHAR2(30) := 'ODMSCHEMA';
73
74 -- Default ODM Schema name
75 G_ODM_SCHEMA_NAME VARCHAR2(30) := 'ODM';
76
77
78 PROCEDURE randomize_build_data (
79 p_object_type IN VARCHAR2,
80 p_object_id IN NUMBER
81 );
82
83
84 --
85 -- PURPOSE
86 -- obseleted
87 PROCEDURE get_select_list (
88 p_target_type IN VARCHAR2,
89 x_select_list OUT NOCOPY VARCHAR2
90 );
91
92
93 PROCEDURE create_build_views (
94 p_object_id IN NUMBER,
95 p_select_list IN VARCHAR2,
96 p_from_clause IN VARCHAR2,
97 p_where_clause IN VARCHAR2
98 );
99
100
101 PROCEDURE create_apply_views (
102 p_object_id IN NUMBER,
103 p_select_list IN VARCHAR2,
104 p_from_clause IN VARCHAR2,
105 p_where_clause IN VARCHAR2
106 );
107
108
109 ---------------------------------------------------------------
110 -- Purpose:
111 -- Retrieve the selected fields for model building.
112 --
113 -- Parameter:
114 -- p_data_source_id
115 -- x_select_fields
116 -- x_return_status
117 ---------------------------------------------------------------
118 PROCEDURE get_select_fields (
119 p_data_source_id IN NUMBER,
120 p_target_id IN NUMBER,
121 p_is_b2bcustprof IN BOOLEAN,
122 x_select_fields OUT NOCOPY VARCHAR2,
123 x_return_status OUT NOCOPY VARCHAR2
124 );
125
126
127 ---------------------------------------------------------------
128 -- Purpose:
129 -- Retrieve the from and where clauses for the dynamic
130 -- creation of the input views for ODM.
131 --
132 -- Parameter:
133 -- p_object_type
134 -- p_object_id
135 -- p_data_source_id
136 -- x_from_clause
137 -- x_where_clause
138 -- x_return_status
139 ---------------------------------------------------------------
140 PROCEDURE get_from_where_clause (
141 p_object_type IN VARCHAR2,
142 p_object_id IN NUMBER,
143 p_data_source_id IN NUMBER,
144 x_from_clause OUT NOCOPY VARCHAR2,
145 x_where_clause_sel OUT NOCOPY VARCHAR2,
146 x_where_clause OUT NOCOPY VARCHAR2,
147 x_return_status OUT NOCOPY VARCHAR2
148 );
149
150
151 -- Hint: Primary key needs to be returned.
152 PROCEDURE Lock_Source(
153 p_api_version IN NUMBER,
154 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
155
156 X_Return_Status OUT NOCOPY VARCHAR2,
157 X_Msg_Count OUT NOCOPY NUMBER,
158 X_Msg_Data OUT NOCOPY VARCHAR2,
159
160 p_SOURCE_ID IN NUMBER,
161 p_object_version IN NUMBER
162 )
163
164 IS
165 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Source';
166 l_api_version_number CONSTANT NUMBER := 1.0;
167 l_full_name CONSTANT VARCHAR2(61) := g_pkg_name ||'.'|| l_api_name;
168 l_SOURCE_ID NUMBER;
169
170 CURSOR c_Source IS
171 SELECT SOURCE_ID
172 FROM ams_dm_source
173 WHERE SOURCE_ID = p_SOURCE_ID
174 AND object_version_number = p_object_version
175 FOR UPDATE NOWAIT;
176
177 BEGIN
178
179 -- Debug Message
180 IF (AMS_DEBUG_HIGH_ON) THEN
181
182 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' start');
183 END IF;
184
185 -- Initialize message list if p_init_msg_list is set to TRUE.
186 IF FND_API.to_Boolean( p_init_msg_list )
187 THEN
188 FND_MSG_PUB.initialize;
189 END IF;
190
191 -- Standard call to check for call compatibility.
192 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
193 p_api_version,
194 l_api_name,
195 G_PKG_NAME)
196 THEN
197 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
198 END IF;
199
200
201 -- Initialize API return status to SUCCESS
202 x_return_status := FND_API.G_RET_STS_SUCCESS;
203
204
205 ------------------------ lock -------------------------
206
207 IF (AMS_DEBUG_HIGH_ON) THEN
208
209
210
211 AMS_Utility_PVT.debug_message(l_full_name||': start');
212
213 END IF;
214 OPEN c_Source;
215
216 FETCH c_Source INTO l_SOURCE_ID;
217
218 IF (c_Source%NOTFOUND) THEN
219 CLOSE c_Source;
220 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
221 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
222 FND_MSG_PUB.add;
223 END IF;
224 RAISE FND_API.g_exc_error;
225 END IF;
226
227 CLOSE c_Source;
228
229 -------------------- finish --------------------------
230 FND_MSG_PUB.count_and_get(
231 p_encoded => FND_API.g_false,
232 p_count => x_msg_count,
233 p_data => x_msg_data);
234 IF (AMS_DEBUG_HIGH_ON) THEN
235
236 AMS_Utility_PVT.debug_message(l_full_name ||': end');
237 END IF;
238 EXCEPTION
239
240 WHEN AMS_Utility_PVT.resource_locked THEN
241 x_return_status := FND_API.g_ret_sts_error;
242 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
243 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
244 FND_MSG_PUB.add;
245 END IF;
246
247 WHEN FND_API.G_EXC_ERROR THEN
248 ROLLBACK TO LOCK_source_PVT;
249 x_return_status := FND_API.G_RET_STS_ERROR;
250 -- Standard call to get message count and if count=1, get the message
251 FND_MSG_PUB.Count_And_Get (
252 p_encoded => FND_API.G_FALSE,
253 p_count => x_msg_count,
254 p_data => x_msg_data
255 );
256
257 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
258 ROLLBACK TO LOCK_source_PVT;
259 x_return_status := FND_API.G_RET_STS_UNEXP_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 OTHERS THEN
268 ROLLBACK TO LOCK_source_PVT;
269 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
270 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
271 THEN
272 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
273 END IF;
274 -- Standard call to get message count and if count=1, get the message
275 FND_MSG_PUB.Count_And_Get (
276 p_encoded => FND_API.G_FALSE,
277 p_count => x_msg_count,
278 p_data => x_msg_data
279 );
280 End Lock_Source;
281
282
283
284 -- Hint: Primary key needs to be returned.
285 PROCEDURE Create_Source(
286 p_api_version IN NUMBER,
287 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
288 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
289 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
290
291 X_Return_Status OUT NOCOPY VARCHAR2,
292 X_Msg_Count OUT NOCOPY NUMBER,
293 X_Msg_Data OUT NOCOPY VARCHAR2,
294
295 P_source_rec IN Source_Rec_Type := G_MISS_source_rec,
296 X_SOURCE_ID OUT NOCOPY NUMBER
297 )
298
299 IS
300 l_api_name CONSTANT VARCHAR2(30) := 'Create_Source';
301 l_api_version_number CONSTANT NUMBER := 1.0;
302 l_return_status_full VARCHAR2(1);
303 l_object_version_number NUMBER := 1;
304 l_org_id NUMBER := FND_API.G_MISS_NUM;
305 l_SOURCE_ID NUMBER;
306 l_dummy NUMBER;
307
308 CURSOR c_id IS
309 SELECT AMS_dm_source_s.NEXTVAL
310 FROM dual;
311
312 CURSOR c_id_exists (l_id IN NUMBER) IS
313 SELECT 1 FROM dual
314 WHERE EXISTS (SELECT 1 FROM ams_dm_source
315 WHERE SOURCE_ID = l_id);
316
317 BEGIN
318 -- Standard Start of API savepoint
319 SAVEPOINT CREATE_source_PVT;
320
321 -- Standard call to check for call compatibility.
322 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
323 p_api_version,
324 l_api_name,
325 G_PKG_NAME)
326 THEN
327 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
328 END IF;
329
330 -- Initialize message list if p_init_msg_list is set to TRUE.
331 IF FND_API.to_Boolean( p_init_msg_list )
332 THEN
333 FND_MSG_PUB.initialize;
334 END IF;
335
336 -- Debug Message
337 IF (AMS_DEBUG_HIGH_ON) THEN
338
339 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' start');
340 END IF;
341
342
343 -- Initialize API return status to SUCCESS
344 x_return_status := FND_API.G_RET_STS_SUCCESS;
345
346 -- Local variable initialization
347
348 IF p_source_rec.SOURCE_ID IS NULL OR p_source_rec.SOURCE_ID = FND_API.g_miss_num THEN
349 LOOP
350 l_dummy := NULL;
351 OPEN c_id;
352 FETCH c_id INTO l_SOURCE_ID;
353 CLOSE c_id;
354
355 OPEN c_id_exists(l_SOURCE_ID);
356 FETCH c_id_exists INTO l_dummy;
357 CLOSE c_id_exists;
358 EXIT WHEN l_dummy IS NULL;
359 END LOOP;
360 ELSE
361 l_source_id := p_source_rec.source_id;
362 END IF;
363
364 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
365 THEN
366 -- Debug message
367 IF (AMS_DEBUG_HIGH_ON) THEN
368
369 AMS_UTILITY_PVT.debug_message('Private API: Validate_Source');
370 END IF;
371
372 -- Invoke validation procedures
373 Validate_Source(
374 p_api_version => 1.0,
375 p_init_msg_list => FND_API.G_FALSE,
376 p_validation_level => p_validation_level,
377 p_validation_mode => JTF_PLSQL_API.g_create,
378 P_source_rec => P_source_rec,
379 x_return_status => x_return_status,
380 x_msg_count => x_msg_count,
381 x_msg_data => x_msg_data);
382 END IF;
383
384 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
385 RAISE FND_API.G_EXC_ERROR;
386 END IF;
387
388
389 -- Debug Message
390 IF (AMS_DEBUG_HIGH_ON) THEN
391
392 AMS_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
393 END IF;
394
395 -- Invoke table handler(ams_dm_source_PKG.Insert_Row)
396 BEGIN
397 ams_dm_source_PKG.Insert_Row(
398 px_SOURCE_ID => l_SOURCE_ID,
399 p_LAST_UPDATE_DATE => SYSDATE,
400 p_LAST_UPDATED_BY => FND_GLOBAL.user_id,
401 p_CREATION_DATE => SYSDATE,
402 p_CREATED_BY => FND_GLOBAL.user_id,
403 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
404 px_OBJECT_VERSION_NUMBER => l_object_version_number,
405 p_MODEL_TYPE => p_source_rec.model_type,
406 p_ARC_USED_FOR_OBJECT => p_source_rec.arc_used_for_object,
407 p_USED_FOR_OBJECT_ID => p_source_rec.used_for_object_id,
408 p_PARTY_ID => p_source_rec.party_id,
409 p_SCORE_RESULT => p_source_rec.score_result,
410 p_TARGET_VALUE => p_source_rec.target_value,
411 p_CONFIDENCE => p_source_rec.confidence,
412 p_CONTINUOUS_SCORE => p_source_rec.continuous_score,
413 p_decile => p_source_rec.decile,
414 p_percentile => p_source_rec.percentile);
415 EXCEPTION
416 WHEN NO_DATA_FOUND THEN
417 AMS_Utility_PVT.error_message ('AMS_API_NO_INSERT');
418 RAISE FND_API.g_exc_error;
419 END;
420 --
421 -- End of API body
422 --
423 x_source_id := l_source_id;
424 -- Standard check for p_commit
428 END IF;
425 IF FND_API.to_Boolean( p_commit )
426 THEN
427 COMMIT WORK;
429
430
431 -- Debug Message
432 IF (AMS_DEBUG_HIGH_ON) THEN
433
434 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' end');
435 END IF;
436
437 -- Standard call to get message count and if count is 1, get message info.
438 FND_MSG_PUB.Count_And_Get
439 (p_count => x_msg_count,
440 p_data => x_msg_data
441 );
442 EXCEPTION
443
444 WHEN AMS_Utility_PVT.resource_locked THEN
445 x_return_status := FND_API.g_ret_sts_error;
446 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
447 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
448 FND_MSG_PUB.add;
449 END IF;
450
451 WHEN FND_API.G_EXC_ERROR THEN
452 ROLLBACK TO CREATE_source_PVT;
453 x_return_status := FND_API.G_RET_STS_ERROR;
454 -- Standard call to get message count and if count=1, get the message
455 FND_MSG_PUB.Count_And_Get (
456 p_encoded => FND_API.G_FALSE,
457 p_count => x_msg_count,
458 p_data => x_msg_data
459 );
460
461 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
462 ROLLBACK TO CREATE_source_PVT;
463 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
464 -- Standard call to get message count and if count=1, get the message
465 FND_MSG_PUB.Count_And_Get (
466 p_encoded => FND_API.G_FALSE,
467 p_count => x_msg_count,
468 p_data => x_msg_data
469 );
470
471 WHEN OTHERS THEN
472 ROLLBACK TO CREATE_source_PVT;
473 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
474 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
475 THEN
476 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
477 END IF;
478 -- Standard call to get message count and if count=1, get the message
479 FND_MSG_PUB.Count_And_Get (
480 p_encoded => FND_API.G_FALSE,
481 p_count => x_msg_count,
482 p_data => x_msg_data
483 );
484 End Create_Source;
485
486
487 PROCEDURE Update_Source(
488 p_api_version IN NUMBER,
489 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
490 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
491 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
492
493 X_Return_Status OUT NOCOPY VARCHAR2,
494 X_Msg_Count OUT NOCOPY NUMBER,
495 X_Msg_Data OUT NOCOPY VARCHAR2,
496
497 P_source_rec IN Source_Rec_Type,
498 X_Object_Version_Number OUT NOCOPY NUMBER
499 )
500 IS
501 l_api_name CONSTANT VARCHAR2(30) := 'Update_Source';
502 l_api_version_number CONSTANT NUMBER := 1.0;
503
504 CURSOR c_reference (p_source_id IN NUMBER) IS
505 SELECT *
506 FROM ams_dm_source
507 WHERE source_id = p_source_id;
508
509 -- Local Variables
510 l_object_version_number NUMBER;
511 l_SOURCE_ID NUMBER;
512 l_reference_rec c_reference%ROWTYPE;
513 l_tar_source_rec AMS_DMSource_PVT.Source_Rec_Type := P_source_rec;
514 l_rowid ROWID;
515
516 BEGIN
517 -- Standard Start of API savepoint
518 SAVEPOINT UPDATE_source_PVT;
519
520 -- Standard call to check for call compatibility.
521 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
522 p_api_version,
523 l_api_name,
524 G_PKG_NAME)
525 THEN
526 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
527 END IF;
528
529 -- Initialize message list if p_init_msg_list is set to TRUE.
530 IF FND_API.to_Boolean( p_init_msg_list )
531 THEN
532 FND_MSG_PUB.initialize;
533 END IF;
534
535 -- Debug Message
536 IF (AMS_DEBUG_HIGH_ON) THEN
537
538 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' start');
539 END IF;
540
541
542 -- Initialize API return status to SUCCESS
543 x_return_status := FND_API.G_RET_STS_SUCCESS;
544
545 -- Debug Message
546 IF (AMS_DEBUG_HIGH_ON) THEN
547
548 AMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
549 END IF;
550
551 OPEN c_reference (l_tar_source_rec.source_id);
552 FETCH c_reference INTO l_reference_rec;
553 IF ( c_reference%NOTFOUND) THEN
554 CLOSE c_reference;
555 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
556 FND_MESSAGE.Set_Name('AMS', 'API_MISSING_UPDATE_TARGET');
557 FND_MESSAGE.Set_Token ('INFO', 'dm_source', FALSE);
558 FND_MSG_PUB.Add;
559 END IF;
560 RAISE FND_API.g_exc_error;
561 END IF;
562 -- Debug Message
563 IF (AMS_DEBUG_HIGH_ON) THEN
564
565 AMS_UTILITY_PVT.debug_message('Private API: - Close Cursor');
566 END IF;
567 CLOSE c_reference;
568
569 -- Check Whether record has been changed by someone else
570 IF (l_tar_source_rec.object_version_number <> l_reference_rec.object_version_number) THEN
574 FND_MSG_PUB.ADD;
571 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
572 FND_MESSAGE.Set_Name('AMS', 'API_RECORD_CHANGED');
573 FND_MESSAGE.Set_Token('INFO', 'dm_source', FALSE);
575 END IF;
576 RAISE FND_API.g_exc_error;
577 END IF;
578
579 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
580 -- Debug message
581 IF (AMS_DEBUG_HIGH_ON) THEN
582
583 AMS_UTILITY_PVT.debug_message('Private API: Validate_Source');
584 END IF;
585
586 -- Invoke validation procedures
587 Validate_Source(
588 p_api_version => 1.0,
589 p_init_msg_list => FND_API.G_FALSE,
590 p_validation_level => p_validation_level,
591 p_validation_mode => JTF_PLSQL_API.g_update,
592 P_source_rec => P_source_rec,
593 x_return_status => x_return_status,
594 x_msg_count => x_msg_count,
595 x_msg_data => x_msg_data);
596 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
597 RAISE FND_API.G_EXC_ERROR;
598 END IF;
599
600 END IF;
601
602 -- Debug Message
603 IF (AMS_DEBUG_LOW_ON) THEN
604
605 AMS_UTILITY_PVT.debug_message('Private API: Calling update table handler', FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
606 END IF;
607
608 -- Invoke table handler(ams_dm_source_PKG.Update_Row)
609 ams_dm_source_PKG.Update_Row(
610 p_source_id => p_source_rec.source_id,
611 p_last_update_date => SYSDATE,
612 p_last_updated_by => FND_GLOBAL.user_id,
613 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
614 p_object_version_number => p_source_rec.object_version_number + 1,
615 p_model_type => p_source_rec.model_type,
616 p_arc_used_for_object => p_source_rec.arc_used_for_object,
617 p_used_for_object_id => p_source_rec.used_for_object_id,
618 p_party_id => p_source_rec.party_id,
619 p_score_result => p_source_rec.score_result,
620 p_target_value => p_source_rec.target_value,
621 p_confidence => p_source_rec.confidence,
622 p_continuous_score => p_source_rec.continuous_score,
623 p_decile => p_source_rec.decile,
624 p_percentile => p_source_rec.percentile);
625 --
626 -- End of API body.
627 --
628
629 -- Standard check for p_commit
630 IF FND_API.to_Boolean( p_commit )
631 THEN
632 COMMIT WORK;
633 END IF;
634
635
636 -- Debug Message
637 IF (AMS_DEBUG_HIGH_ON) THEN
638
639 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' end');
640 END IF;
641
642 -- Standard call to get message count and if count is 1, get message info.
643 FND_MSG_PUB.Count_And_Get
644 (p_count => x_msg_count,
645 p_data => x_msg_data
646 );
647 EXCEPTION
648
649 WHEN AMS_Utility_PVT.resource_locked THEN
650 x_return_status := FND_API.g_ret_sts_error;
651 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
652 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
653 FND_MSG_PUB.add;
654 END IF;
655
656 WHEN FND_API.G_EXC_ERROR THEN
657 ROLLBACK TO UPDATE_source_PVT;
658 x_return_status := FND_API.G_RET_STS_ERROR;
659 -- Standard call to get message count and if count=1, get the message
660 FND_MSG_PUB.Count_And_Get (
661 p_encoded => FND_API.G_FALSE,
662 p_count => x_msg_count,
663 p_data => x_msg_data
664 );
665
666 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
667 ROLLBACK TO UPDATE_source_PVT;
668 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
669 -- Standard call to get message count and if count=1, get the message
670 FND_MSG_PUB.Count_And_Get (
671 p_encoded => FND_API.G_FALSE,
672 p_count => x_msg_count,
673 p_data => x_msg_data
674 );
675
676 WHEN OTHERS THEN
677 ROLLBACK TO UPDATE_source_PVT;
678 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
679 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
680 THEN
681 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
682 END IF;
683 -- Standard call to get message count and if count=1, get the message
684 FND_MSG_PUB.Count_And_Get (
685 p_encoded => FND_API.G_FALSE,
686 p_count => x_msg_count,
687 p_data => x_msg_data
688 );
689 End Update_Source;
690
691
692 PROCEDURE Delete_Source(
693 p_api_version IN NUMBER,
694 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
695 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
696 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
697 X_Return_Status OUT NOCOPY VARCHAR2,
698 X_Msg_Count OUT NOCOPY NUMBER,
699 X_Msg_Data OUT NOCOPY VARCHAR2,
700 P_SOURCE_ID IN NUMBER,
704 CURSOR c_obj_version(c_id NUMBER) IS
701 P_Object_Version_Number IN NUMBER
702 )
703 IS
705 SELECT object_version_number
706 FROM ams_dm_source
707 WHERE source_id = c_id;
708
709 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Source';
710 l_api_version_number CONSTANT NUMBER := 1.0;
711 l_object_version_number NUMBER;
712
713 BEGIN
714 -- Standard Start of API savepoint
715 SAVEPOINT DELETE_source_PVT;
716
717 -- Standard call to check for call compatibility.
718 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
719 p_api_version,
720 l_api_name,
721 G_PKG_NAME)
722 THEN
723 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
724 END IF;
725
726 -- Initialize message list if p_init_msg_list is set to TRUE.
727 IF FND_API.to_Boolean( p_init_msg_list )
728 THEN
729 FND_MSG_PUB.initialize;
730 END IF;
731
732 -- Debug Message
733 IF (AMS_DEBUG_HIGH_ON) THEN
734
735 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' start');
736 END IF;
737
738 -- Initialize API return status to SUCCESS
739 x_return_status := FND_API.G_RET_STS_SUCCESS;
740
741 IF (AMS_DEBUG_HIGH_ON) THEN
742
743
744
745 AMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
746
747 END IF;
748
749 OPEN c_obj_version(p_source_id);
750 FETCH c_obj_version INTO l_object_version_number;
751 IF ( c_obj_version%NOTFOUND) THEN
752 AMS_Utility_PVT.error_message ('AMS_API_RECORD_NOT_FOUND');
753 RAISE FND_API.g_exc_error;
754 END IF;
755 -- Debug Message
756 IF (AMS_DEBUG_HIGH_ON) THEN
757
758 AMS_UTILITY_PVT.debug_message('Private API: - Close Cursor');
759 END IF;
760 CLOSE c_obj_version;
761
762 --
763 -- Api body
764 --
765 -- Debug Message
766 IF P_Object_Version_Number = l_object_version_number THEN
767 IF (AMS_DEBUG_HIGH_ON) THEN
768
769 AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
770 END IF;
771
772 -- Invoke table handler(AMS_DM_MODEL_SCORES_B_PKG.Delete_Row)
773 BEGIN
774 ams_dm_source_pkg.Delete_Row(
775 p_source_id => p_source_id);
776 EXCEPTION
777 WHEN NO_DATA_FOUND THEN
778 AMS_Utility_PVT.error_message ('AMS_API_RECORD_NOT_FOUND');
779 RAISE FND_API.g_exc_error;
780 END;
781 --
782 -- End of API body
783 --
787 THEN
784
785 -- Standard check for p_commit
786 IF FND_API.to_Boolean( p_commit )
788 COMMIT WORK;
789 END IF;
790
791
792 -- Debug Message
793 IF (AMS_DEBUG_HIGH_ON) THEN
794
795 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' end');
796 END IF;
797
798 -- Standard call to get message count and if count is 1, get message info.
799 FND_MSG_PUB.Count_And_Get
800 (p_count => x_msg_count,
801 p_data => x_msg_data
802 );
803 ELSE
804 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
805 FND_MESSAGE.Set_Name('AMS', 'API_RECORD_CHANGED');
806 FND_MESSAGE.Set_Token('INFO', 'dm_source', FALSE);
807 FND_MSG_PUB.ADD;
808 END IF;
809 raise FND_API.G_EXC_ERROR;
810 END IF;
811
812 EXCEPTION
813
814 WHEN AMS_Utility_PVT.resource_locked THEN
815 x_return_status := FND_API.g_ret_sts_error;
816 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
817 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
818 FND_MSG_PUB.add;
819 END IF;
820
821 WHEN FND_API.G_EXC_ERROR THEN
822 ROLLBACK TO DELETE_source_PVT;
823 x_return_status := FND_API.G_RET_STS_ERROR;
824 -- Standard call to get message count and if count=1, get the message
825 FND_MSG_PUB.Count_And_Get (
826 p_encoded => FND_API.G_FALSE,
827 p_count => x_msg_count,
828 p_data => x_msg_data
829 );
830
831 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
832 ROLLBACK TO DELETE_source_PVT;
833 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
834 -- Standard call to get message count and if count=1, get the message
835 FND_MSG_PUB.Count_And_Get (
836 p_encoded => FND_API.G_FALSE,
837 p_count => x_msg_count,
838 p_data => x_msg_data
839 );
840
841 WHEN OTHERS THEN
842 ROLLBACK TO DELETE_source_PVT;
843 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
844 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
845 THEN
846 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
847 END IF;
848 -- Standard call to get message count and if count=1, get the message
849 FND_MSG_PUB.Count_And_Get (
850 p_encoded => FND_API.G_FALSE,
851 p_count => x_msg_count,
852 p_data => x_msg_data
853 );
854 End Delete_Source;
855
856
857 PROCEDURE check_source_uk_items(
858 p_source_rec IN Source_Rec_Type,
859 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
860 x_return_status OUT NOCOPY VARCHAR2)
861 IS
862 l_valid_flag VARCHAR2(1);
863
864 BEGIN
865 IF (AMS_DEBUG_HIGH_ON) THEN
866
867 AMS_UTILITY_PVT.debug_message('Private API:check_source_uk_items');
868 END IF;
869 x_return_status := FND_API.g_ret_sts_success;
870 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
871 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
872 'ams_dm_source',
873 'SOURCE_ID = ''' || p_source_rec.SOURCE_ID ||''''
874 );
875 ELSE
876 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
877 'ams_dm_source',
878 'SOURCE_ID = ''' || p_source_rec.SOURCE_ID ||
879 ''' AND SOURCE_ID <> ' || p_source_rec.SOURCE_ID
880 );
881 END IF;
882
883 IF l_valid_flag = FND_API.g_false THEN
884 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
885 FND_MESSAGE.set_name('AMS', 'AMS_DM_SOURCE_ID_DUPLICATE');
886 FND_MSG_PUB.add;
887 END IF;
888 x_return_status := FND_API.g_ret_sts_error;
889 END IF;
890
891 END check_source_uk_items;
892
893 PROCEDURE check_source_req_items(
894 p_source_rec IN Source_Rec_Type,
895 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
896 x_return_status OUT NOCOPY VARCHAR2
897 )
898 IS
899 BEGIN
900 IF (AMS_DEBUG_HIGH_ON) THEN
901
902 AMS_UTILITY_PVT.debug_message('Private API:check_source_req_items');
903 END IF;
904 x_return_status := FND_API.g_ret_sts_success;
905
906 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
907 IF p_source_rec.ARC_USED_FOR_OBJECT = FND_API.g_miss_char OR p_source_rec.ARC_USED_FOR_OBJECT IS NULL THEN
908 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
909 FND_MESSAGE.set_name('AMS', 'AMS_DM_SRC_NO_ARC_USED_FOR_OBJ');
910 FND_MSG_PUB.add;
911 END IF;
912 x_return_status := FND_API.g_ret_sts_error;
913 END IF;
914
915
916 IF p_source_rec.USED_FOR_OBJECT_ID = FND_API.g_miss_num OR p_source_rec.USED_FOR_OBJECT_ID IS NULL THEN
917 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
918 FND_MESSAGE.set_name('AMS', 'AMS_DM_SRC_NO_USED_FOR_OBJ_ID');
919 FND_MSG_PUB.add;
920 END IF;
924
921 x_return_status := FND_API.g_ret_sts_error;
922 END IF;
923
925 IF p_source_rec.PARTY_ID = FND_API.g_miss_num OR p_source_rec.PARTY_ID IS NULL THEN
926 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
927 FND_MESSAGE.set_name('AMS', 'AMS_DM_SOURCE_NO_PARTY_ID');
928 FND_MSG_PUB.add;
929 END IF;
930 x_return_status := FND_API.g_ret_sts_error;
931 END IF;
932 ELSE -- update mode
933 IF p_source_rec.SOURCE_ID IS NULL THEN
934 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
935 FND_MESSAGE.set_name('AMS', 'AMS_DM_SOURCE_NO_SOURCE_ID');
936 FND_MSG_PUB.add;
937 END IF;
938 x_return_status := FND_API.g_ret_sts_error;
939 END IF;
940
941 IF p_source_rec.ARC_USED_FOR_OBJECT IS NULL THEN
942 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
943 FND_MESSAGE.set_name('AMS', 'AMS_DM_SRC_NO_ARC_USED_FOR_OBJ');
944 FND_MSG_PUB.add;
945 END IF;
946 x_return_status := FND_API.g_ret_sts_error;
947 END IF;
948
949
950 IF p_source_rec.USED_FOR_OBJECT_ID IS NULL THEN
951 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
952 FND_MESSAGE.set_name('AMS', 'AMS_DM_SRC_NO_USED_FOR_OBJ_ID');
953 FND_MSG_PUB.add;
954 END IF;
955 x_return_status := FND_API.g_ret_sts_error;
956 END IF;
957
958
959 IF p_source_rec.PARTY_ID IS NULL THEN
960 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
961 FND_MESSAGE.set_name('AMS', 'AMS_DM_SOURCE_NO_PARTY_ID');
962 FND_MSG_PUB.add;
963 END IF;
964 x_return_status := FND_API.g_ret_sts_error;
965 END IF;
966 END IF;
967
968 END check_source_req_items;
969
970 PROCEDURE check_source_FK_items(
971 p_source_rec IN Source_Rec_Type,
972 x_return_status OUT NOCOPY VARCHAR2
973 )
974 IS
975 BEGIN
976 IF (AMS_DEBUG_HIGH_ON) THEN
977
978 AMS_UTILITY_PVT.debug_message('Private API:check_source_fk_items');
979 END IF;
980 x_return_status := FND_API.g_ret_sts_success;
981
982 --------------------used_for_object_id---------------------------
983 IF p_source_rec.arc_used_for_object = 'MODL' THEN
984 IF p_source_rec.used_for_object_id <> FND_API.g_miss_num THEN
985 IF AMS_Utility_PVT.check_fk_exists(
986 'ams_dm_models_all_b',
987 'model_id',
988 p_source_rec.used_for_object_id
989 ) = FND_API.g_false
990 THEN
991 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
992 FND_MESSAGE.set_name('AMS', 'AMS_DM_SOURCE_BAD_MODEL_ID');
993 FND_MSG_PUB.add;
994 END IF;
995 x_return_status := FND_API.g_ret_sts_error;
996 END IF;
997 END IF;
998 ELSIF p_source_rec.arc_used_for_object = 'SCOR' THEN
999 IF p_source_rec.used_for_object_id <> FND_API.g_miss_num THEN
1000 IF AMS_Utility_PVT.check_fk_exists(
1001 'ams_dm_scores_all_b',
1002 'score_id',
1003 p_source_rec.used_for_object_id
1004 ) = FND_API.g_false
1005 THEN
1006 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1007 FND_MESSAGE.set_name('AMS', 'AMS_DM_SOURCE_BAD_SCORE_ID');
1008 FND_MSG_PUB.add;
1009 END IF;
1010 x_return_status := FND_API.g_ret_sts_error;
1011 END IF;
1012 END IF;
1013 END IF;
1014
1015 END check_source_FK_items;
1016
1017 PROCEDURE check_source_Lookup_items(
1018 p_source_rec IN Source_Rec_Type,
1019 x_return_status OUT NOCOPY VARCHAR2
1020 )
1021 IS
1022 BEGIN
1023 x_return_status := FND_API.g_ret_sts_success;
1024
1025 END check_source_Lookup_items;
1026
1027 PROCEDURE Check_source_Items (
1028 P_source_rec IN Source_Rec_Type,
1029 p_validation_mode IN VARCHAR2,
1030 x_return_status OUT NOCOPY VARCHAR2
1031 )
1032 IS
1033 BEGIN
1034
1035 -- Check Items Uniqueness API calls
1036 check_source_uk_items(
1037 p_source_rec => p_source_rec,
1038 p_validation_mode => p_validation_mode,
1039 x_return_status => x_return_status);
1040
1041 -- Check Items Required/NOT NULL API calls
1042 check_source_req_items(
1043 p_source_rec => p_source_rec,
1044 p_validation_mode => p_validation_mode,
1045 x_return_status => x_return_status);
1046
1047 -- Check Items Foreign Keys API calls
1048 check_source_FK_items(
1049 p_source_rec => p_source_rec,
1050 x_return_status => x_return_status);
1051
1052 -- Check Items Lookups
1053 check_source_Lookup_items(
1054 p_source_rec => p_source_rec,
1055 x_return_status => x_return_status);
1056
1057 END Check_source_Items;
1058
1059
1060 PROCEDURE Complete_source_rec (
1061 P_source_rec IN Source_Rec_Type,
1062 x_complete_rec OUT NOCOPY Source_Rec_Type
1063 )
1067 --
1064 IS
1065 BEGIN
1066
1068 -- Check Items API calls
1069 NULL;
1070 --
1071
1072 END Complete_source_rec;
1073
1074 PROCEDURE Validate_Source(
1075 p_api_version IN NUMBER,
1076 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1077 P_Validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1078 P_Validation_mode IN VARCHAR2,
1079 P_source_rec IN Source_Rec_Type,
1080 X_Return_Status OUT NOCOPY VARCHAR2,
1081 X_Msg_Count OUT NOCOPY NUMBER,
1082 X_Msg_Data OUT NOCOPY VARCHAR2
1083 )
1084 IS
1085 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Source';
1086 l_api_version_number CONSTANT NUMBER := 1.0;
1087 l_object_version_number NUMBER;
1088 l_source_rec AMS_DMSource_PVT.Source_Rec_Type;
1089
1090 BEGIN
1091 -- Standard call to check for call compatibility.
1092 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1093 p_api_version,
1094 l_api_name,
1095 G_PKG_NAME)
1096 THEN
1097 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1098 END IF;
1099
1100 -- Initialize message list if p_init_msg_list is set to TRUE.
1101 IF FND_API.to_Boolean( p_init_msg_list )
1102 THEN
1103 FND_MSG_PUB.initialize;
1104 END IF;
1105 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1106 Check_source_Items(
1107 p_source_rec => p_source_rec,
1108 p_validation_mode => p_validation_mode,
1109 x_return_status => x_return_status
1110 );
1111
1112 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1113 RAISE FND_API.G_EXC_ERROR;
1114 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1115 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1116 END IF;
1117 END IF;
1118
1119 Complete_source_rec(
1120 p_source_rec => p_source_rec,
1121 x_complete_rec => l_source_rec
1122 );
1123
1124 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
1125 Validate_source_rec(
1126 p_api_version => 1.0,
1127 p_init_msg_list => FND_API.G_FALSE,
1128 x_return_status => x_return_status,
1129 x_msg_count => x_msg_count,
1130 x_msg_data => x_msg_data,
1131 P_source_rec => l_source_rec);
1132
1133 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1134 RAISE FND_API.G_EXC_ERROR;
1135 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1136 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1137 END IF;
1138 END IF;
1139
1140
1141 -- Debug Message
1142 IF (AMS_DEBUG_HIGH_ON) THEN
1143
1144 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' start');
1145 END IF;
1146
1147
1148 -- Initialize API return status to SUCCESS
1149 x_return_status := FND_API.G_RET_STS_SUCCESS;
1150
1151
1152 -- Debug Message
1153 IF (AMS_DEBUG_HIGH_ON) THEN
1154
1155 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' end');
1156 END IF;
1157
1158 -- Standard call to get message count and if count is 1, get message info.
1159 FND_MSG_PUB.Count_And_Get
1160 (p_count => x_msg_count,
1161 p_data => x_msg_data
1162 );
1163 EXCEPTION
1164
1165 WHEN AMS_Utility_PVT.resource_locked THEN
1166 x_return_status := FND_API.g_ret_sts_error;
1167 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1168 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
1169 FND_MSG_PUB.add;
1170 END IF;
1171
1172 WHEN FND_API.G_EXC_ERROR THEN
1173 x_return_status := FND_API.G_RET_STS_ERROR;
1174 -- Standard call to get message count and if count=1, get the message
1175 FND_MSG_PUB.Count_And_Get (
1176 p_encoded => FND_API.G_FALSE,
1177 p_count => x_msg_count,
1178 p_data => x_msg_data
1179 );
1180
1181 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1182 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1183 -- Standard call to get message count and if count=1, get the message
1184 FND_MSG_PUB.Count_And_Get (
1185 p_encoded => FND_API.G_FALSE,
1186 p_count => x_msg_count,
1187 p_data => x_msg_data
1188 );
1189
1190 WHEN OTHERS THEN
1191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1192 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1193 THEN
1194 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1195 END IF;
1196 -- Standard call to get message count and if count=1, get the message
1197 FND_MSG_PUB.Count_And_Get (
1198 p_encoded => FND_API.G_FALSE,
1199 p_count => x_msg_count,
1200 p_data => x_msg_data
1204
1201 );
1202 End Validate_Source;
1203
1205 PROCEDURE Validate_source_rec(
1206 p_api_version IN NUMBER,
1207 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1208 X_Return_Status OUT NOCOPY VARCHAR2,
1209 X_Msg_Count OUT NOCOPY NUMBER,
1210 X_Msg_Data OUT NOCOPY VARCHAR2,
1211 P_source_rec IN Source_Rec_Type
1212 )
1213 IS
1214 BEGIN
1215 -- Initialize message list if p_init_msg_list is set to TRUE.
1216 IF FND_API.to_Boolean( p_init_msg_list )
1217 THEN
1218 FND_MSG_PUB.initialize;
1219 END IF;
1220
1221 -- Initialize API return status to SUCCESS
1222 x_return_status := FND_API.G_RET_STS_SUCCESS;
1223
1224 -- Hint: Validate data
1225 -- If data not valid
1226 -- THEN
1227 -- x_return_status := FND_API.G_RET_STS_ERROR;
1228
1229 -- Debug Message
1230 IF (AMS_DEBUG_HIGH_ON) THEN
1231
1232 AMS_UTILITY_PVT.debug_message('Private API: Validate_dm_model_rec');
1233 END IF;
1234 -- Standard call to get message count and if count is 1, get message info.
1235 FND_MSG_PUB.Count_And_Get
1236 (p_count => x_msg_count,
1237 p_data => x_msg_data
1238 );
1239 END Validate_source_rec;
1240
1241
1242 PROCEDURE bin_probability (
1243 p_api_version IN NUMBER,
1244 p_init_msg_list IN VARCHAR2,
1245 p_commit IN VARCHAR2,
1246 x_return_status OUT NOCOPY VARCHAR2,
1247 x_msg_count OUT NOCOPY NUMBER,
1248 x_msg_data OUT NOCOPY VARCHAR2,
1249 p_score_id IN NUMBER
1250 )
1251 IS
1252 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1253 L_API_NAME CONSTANT VARCHAR2(30) := 'bin_probability';
1254 BEGIN
1255 -- Standard Start of API savepoint
1256 SAVEPOINT bin_probability;
1257
1258 -- Standard call to check for call compatibility.
1259 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1260 p_api_version,
1261 l_api_name,
1262 G_PKG_NAME)
1263 THEN
1264 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1265 END IF;
1266
1267 -- Initialize API return status to SUCCESS
1268 x_return_status := FND_API.G_RET_STS_SUCCESS;
1269
1270 -- Initialize message list if p_init_msg_list is set to TRUE.
1271 IF FND_API.to_Boolean (p_init_msg_list) THEN
1272 FND_MSG_PUB.initialize;
1273 END IF;
1274
1275 UPDATE ams_dm_source
1276 SET decile = (10 - FLOOR (LEAST (99, continuous_score)/10))
1277 WHERE arc_used_for_object = 'SCOR'
1278 AND used_for_object_id = p_score_id
1279 AND continuous_score IS NOT NULL;
1280
1281 UPDATE ams_dm_source
1282 SET percentile = (100 - FLOOR (LEAST (99, continuous_score)))
1283 WHERE arc_used_for_object = 'SCOR'
1284 AND used_for_object_id = p_score_id
1285 AND continuous_score IS NOT NULL;
1286
1287 -- Standard check for p_commit
1288 IF FND_API.to_Boolean (p_commit) THEN
1289 COMMIT WORK;
1290 END IF;
1291 EXCEPTION
1292 WHEN OTHERS THEN
1293 ROLLBACK TO bin_probability;
1294 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1295 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1296 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1297 END IF;
1298 -- Standard call to get message count and if count=1, get the message
1299 FND_MSG_PUB.Count_And_Get (
1300 p_encoded => FND_API.G_FALSE,
1301 p_count => x_msg_count,
1302 p_data => x_msg_data
1303 );
1304 END bin_probability;
1305
1306
1307 --
1308 -- ASSUMPTIONS
1309 -- - data mining engine output table: ODM_OMO_APPLY_RESULT
1310 -- - output table columns: idkey, score, probability
1311 -- - if AMS_ODM_DBLINK profile is set, the database
1312 -- link must exist.
1313 --
1314 PROCEDURE process_scores (
1315 p_api_version IN NUMBER,
1316 p_init_msg_list IN VARCHAR2,
1317 p_commit IN VARCHAR2,
1318 p_score_id IN NUMBER,
1319 x_return_status OUT NOCOPY VARCHAR2,
1320 x_msg_count OUT NOCOPY NUMBER,
1321 x_msg_data OUT NOCOPY VARCHAR2
1322 )
1323 IS
1324 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1325 L_API_NAME CONSTANT VARCHAR2(30) := 'process_scores';
1326
1327 l_source_object VARCHAR2(100) := 'ODM_OMO_APPLY_RESULT';
1328
1329 l_dblink VARCHAR2(30);
1330 l_odm_schema VARCHAR2(30);
1331 l_sql VARCHAR2(32000);
1332
1333 l_target_positive_value VARCHAR2(30);
1334
1335 l_return_status varchar2(1);
1336 l_db_instance VARCHAR2(9);
1337
1338 CURSOR c_db_instance_name IS
1339 SELECT name
1340 FROM V$DATABASE;
1341
1342 CURSOR c_target_positive_value (p_score_id IN NUMBER) IS
1343 SELECT m.target_positive_value
1347 BEGIN
1344 FROM ams_dm_scores_all_b s, ams_dm_models_all_b m
1345 WHERE s.model_id = m.model_id
1346 AND s.score_id = p_score_id;
1348
1349
1350 -- Get the name of the database instance. The scoring run results table
1351 -- that is created by ODM contains the DB instance name and scoring run id.
1352 OPEN c_db_instance_name ();
1353 FETCH c_db_instance_name INTO l_db_instance;
1354 CLOSE c_db_instance_name;
1355
1356 l_source_object := 'OMO_' || l_db_instance || '_' || p_score_id || '_SRT';
1357
1358 IF (AMS_DEBUG_HIGH_ON) THEN
1359 AMS_Utility_PVT.debug_message (L_API_NAME || ' - l_source_object: ' || l_source_object);
1360 END IF;
1361
1362 -- Standard Start of API savepoint
1363 SAVEPOINT process_scores;
1364
1365 -- Standard call to check for call compatibility.
1366 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1367 p_api_version,
1368 l_api_name,
1369 G_PKG_NAME)
1370 THEN
1371 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1372 END IF;
1373
1374 -- Initialize message list if p_init_msg_list is set to TRUE.
1375 IF FND_API.to_Boolean (p_init_msg_list) THEN
1376 FND_MSG_PUB.initialize;
1377 END IF;
1378
1379 -- Initialize API return status to SUCCESS
1380 x_return_status := FND_API.G_RET_STS_SUCCESS;
1381
1382 -- if the data mining database is remote
1383 -- from the CRM database, then we need to
1384 -- access the results through a database
1385 -- link.
1386 --
1387 -- note: if the profile is null or -1, then
1388 -- the data mining database is local to the
1389 -- CRM database.
1390 l_dblink := FND_PROFILE.value ('AMS_ODM_DBLINK');
1391 IF l_dblink IS NOT NULL OR l_dblink <> '-1' THEN
1392 l_source_object := l_source_object || '@' || l_dblink;
1393 END IF;
1394
1395 -- changed rosharma 18-nov-2003
1396 --l_odm_schema := FND_PROFILE.value ('AMS_ODM_OUTPUT_SCHEMA');
1397 l_odm_schema := FND_ORACLE_SCHEMA.GetOuValue('ODMSCHEMA');
1398 IF (AMS_DEBUG_HIGH_ON) THEN
1399 AMS_Utility_PVT.debug_message (L_API_NAME || ' - ODM output schema: ' || l_odm_schema);
1400 END IF;
1401 -- end change rosharma 18-nov-2003
1402 IF l_odm_schema IS NOT NULL THEN
1403 l_source_object := l_odm_schema || '.' || l_source_object;
1404 END IF;
1405
1406 -- stage all data into local table for processing
1407 -- nyostos - Sep 15, 2003 - Use Global Temporary Table
1408 -- l_sql := 'INSERT INTO ams_dm_apply_stg (SELECT idkey, score, probability FROM ' || l_source_object || ')';
1409 l_sql := 'INSERT INTO ams_dm_apply_stg_gt (SELECT idkey, score, probability FROM ' || l_source_object || ')';
1410
1411 IF (AMS_DEBUG_HIGH_ON) THEN
1412 AMS_Utility_PVT.debug_message (L_API_NAME || ' - SQL: ' || l_sql);
1413 END IF;
1414
1415 -- execute dynamic sql
1416 EXECUTE IMMEDIATE l_sql;
1417
1418 --
1419 -- choang - 03-jul-2002 - without the commit, the application
1420 -- throws a ora-00164 exception which workflow has trouble
1421 -- handling
1422 COMMIT;
1423
1424 -- due to the potential requirement
1425 -- of the database link, we need to
1426 -- construct the sql statement and
1427 -- execute dynamically.
1428 OPEN c_target_positive_value (p_score_id);
1429 FETCH c_target_positive_value INTO l_target_positive_value;
1430 CLOSE c_target_positive_value;
1431
1432 UPDATE ams_dm_source s
1433 SET (score_result, confidence, continuous_score) = (SELECT score, probability * 100, DECODE (score, l_target_positive_value, probability, 1 - probability) * 100
1434 -- FROM ams_dm_apply_stg stg
1435 FROM ams_dm_apply_stg_gt stg
1436 WHERE stg.source_id = s.source_id)
1437 -- WHERE s.source_id IN (SELECT source_id from ams_dm_apply_stg);
1438 WHERE s.source_id IN (SELECT source_id from ams_dm_apply_stg_gt);
1439
1440 -- DELETE FROM ams_dm_apply_stg;
1441 DELETE FROM ams_dm_apply_stg_gt;
1442
1443 -- Standard check for p_commit
1444 IF FND_API.to_Boolean (p_commit) THEN
1445 COMMIT WORK;
1446 END IF;
1447 EXCEPTION
1448 WHEN OTHERS THEN
1449 ROLLBACK TO process_scores;
1450 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1451 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1452 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1453 END IF;
1454 -- Standard call to get message count and if count=1, get the message
1455 FND_MSG_PUB.Count_And_Get (
1456 p_encoded => FND_API.G_FALSE,
1457 p_count => x_msg_count,
1458 p_data => x_msg_data
1459 );
1460 END process_scores;
1461
1462 -- cleanup_odm_input_views
1463 --
1464 -- 15-Sep-2003 nyostos Created.
1465 -- Added to remove ODM input views as patr of allowing parallel mining processes.
1466 --
1467 PROCEDURE cleanup_odm_input_views (
1468 p_object_type IN VARCHAR2,
1469 p_object_id IN NUMBER
1470 )
1471 IS
1472 L_API_NAME CONSTANT VARCHAR2(30) := 'cleanup_odm_input_views';
1473 l_training_view VARCHAR2(30);
1477 BEGIN
1474 l_test_view VARCHAR2(30);
1475 l_apply_view VARCHAR2(30);
1476 l_odm_schema VARCHAR2(30);
1478
1479
1480 IF (AMS_DEBUG_HIGH_ON) THEN
1481 AMS_Utility_PVT.debug_message (L_API_NAME || ': BEGIN');
1482 END IF;
1483
1484 -- Get the ODM schema name that willbe used later for GRANTS and SYNONYMS
1485 l_odm_schema := fnd_oracle_schema.GetOuValue(G_ODM_SCHEMA_LOOKUP);
1486 IF l_odm_schema IS NULL THEN
1487 l_odm_schema := G_ODM_SCHEMA_NAME;
1488 END IF;
1489
1490
1491
1492 IF p_object_type = 'MODL' THEN
1493 l_training_view := 'AMS_DM_' || p_object_id || '_TRAIN_V';
1494 l_test_view := 'AMS_DM_' || p_object_id || '_TEST_V';
1495
1496 IF (AMS_DEBUG_HIGH_ON) THEN
1497 AMS_Utility_PVT.debug_message ('ODM Schema' || l_odm_schema );
1498 AMS_Utility_PVT.debug_message ('Dropping Training View ' || l_training_view );
1499 AMS_Utility_PVT.debug_message ('Dropping Test View ' || l_test_view );
1500 END IF;
1501
1502 EXECUTE IMMEDIATE 'DROP VIEW ' || l_training_view ;
1503 EXECUTE IMMEDIATE 'DROP VIEW ' || l_test_view;
1504
1505 IF FND_PROFILE.Value('AMS_DM_USE_DBLINK') = 'N' OR FND_PROFILE.Value('AMS_DM_USE_DBLINK') IS NULL THEN
1506 -- Also delete synonyms for the views in the ODM schema
1507 EXECUTE IMMEDIATE 'DROP synonym ' || l_odm_schema || '.' || l_training_view;
1508 EXECUTE IMMEDIATE 'DROP synonym ' || l_odm_schema || '.' || l_test_view;
1509
1510 IF (AMS_DEBUG_HIGH_ON) THEN
1511 AMS_Utility_PVT.debug_message ('deleting synonyms for views in ' || l_odm_schema || ' schema.' );
1512 END IF;
1513 END IF;
1514
1515 ELSE
1516 l_apply_view := 'AMS_DM_' || p_object_id || '_APPLY_V';
1517
1518 IF (AMS_DEBUG_HIGH_ON) THEN
1519 AMS_Utility_PVT.debug_message ('ODM Schema' || l_odm_schema );
1520 AMS_Utility_PVT.debug_message ('Dropping APPLY View ' || l_apply_view);
1521 END IF;
1522
1523 -- drop the apply view
1524 EXECUTE IMMEDIATE 'DROP VIEW ' || l_apply_view;
1525
1526
1527 IF FND_PROFILE.Value('AMS_DM_USE_DBLINK') = 'N' OR FND_PROFILE.Value('AMS_DM_USE_DBLINK') IS NULL THEN
1528 -- Also drop synonyms for the view in the ODM schema
1529 EXECUTE IMMEDIATE 'DROP synonym ' || l_odm_schema || '.' || l_apply_view ;
1530 IF (AMS_DEBUG_HIGH_ON) THEN
1531 AMS_Utility_PVT.debug_message ('drop synonyms for apply views in ' || l_odm_schema || ' schema.' );
1532 END IF;
1533 END IF;
1534
1535 END IF;
1536
1537 IF (AMS_DEBUG_HIGH_ON) THEN
1538 AMS_Utility_PVT.debug_message (L_API_NAME || ': END');
1539 END IF;
1540
1541 END cleanup_odm_input_views;
1542
1543
1544 PROCEDURE generate_odm_input_views (
1545 p_api_version IN NUMBER,
1546 p_init_msg_list IN VARCHAR2,
1547 p_object_type IN VARCHAR2,
1548 p_object_id IN NUMBER,
1549 p_data_source_id IN VARCHAR2,
1550 x_return_status OUT NOCOPY VARCHAR2,
1551 x_msg_count OUT NOCOPY NUMBER,
1552 x_msg_data OUT NOCOPY VARCHAR2
1553 )
1554 IS
1555 L_SEEDED_ID_THRESHOLD CONSTANT NUMBER := 10000;
1556 L_API_VERSION_NUMBER CONSTANT NUMBER := 2.0;
1557 L_API_NAME CONSTANT VARCHAR2(30) := 'generate_odm_input_views';
1558
1559 l_select_list VARCHAR2(32000);
1560 l_from_clause VARCHAR2(4000);
1561 l_where_clause VARCHAR2(32000);
1562 l_target_id NUMBER;
1563
1564 l_where_clause_sel VARCHAR2(32000);
1565
1566 l_return_status VARCHAR2(1);
1567 l_log_return_status VARCHAR2(1);
1568 --SOURCE_OBJECT_ALIAS VARCHAR2(2) := 'ds';
1569
1570 CURSOR c_target_model (p_model_id IN NUMBER) IS
1571 SELECT model.target_id
1572 FROM ams_dm_models_all_b model
1573 WHERE model.model_id = p_model_id
1574 ;
1575
1576 CURSOR c_target_score (p_score_id IN NUMBER) IS
1577 SELECT model.target_id,model.model_id
1578 FROM ams_dm_scores_all_b score, ams_dm_models_all_b model
1579 WHERE model.model_id = score.model_id
1580 AND score.score_id = p_score_id
1581 ;
1582
1583 CURSOR c_ds_pk_field (p_data_source_id IN NUMBER) IS
1584 SELECT SOURCE_OBJECT_NAME || '.' || SOURCE_OBJECT_PK_FIELD
1585 FROM AMS_LIST_SRC_TYPES
1586 WHERE LIST_SOURCE_TYPE_ID = p_data_source_id
1587 ;
1588
1589 CURSOR c_model_type(p_model_id IN NUMBER) is
1590 SELECT model_type
1591 FROM ams_dm_models_vl
1592 WHERE model_id=p_model_id
1593 ;
1594
1595 l_model_id NUMBER;
1596 l_model_type VARCHAR2(30);
1597 l_is_b2b BOOLEAN := FALSE;
1598 l_is_seeded BOOLEAN := FALSE;
1599 l_is_b2b_cust BOOLEAN := FALSE;
1600
1601 l_ds_pk_field VARCHAR2(61);
1602 l_check_sql VARCHAR2(32000);
1603 l_dummy NUMBER;
1604
1605 BEGIN
1606 -- Standard call to check for call compatibility.
1607 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1608 p_api_version,
1612 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1609 l_api_name,
1610 G_PKG_NAME)
1611 THEN
1613 END IF;
1614
1615 -- Initialize message list if p_init_msg_list is set to TRUE.
1616 IF FND_API.to_Boolean (p_init_msg_list) THEN
1617 FND_MSG_PUB.initialize;
1618 END IF;
1619
1620 -- Initialize API return status to SUCCESS
1621 x_return_status := FND_API.G_RET_STS_SUCCESS;
1622
1623 -- kbasavar 27-Jun-2003 validate for object type. Call randomize_build_data only for build
1624 IF p_object_type = 'MODL' THEN
1625 randomize_build_data (
1626 p_object_type => p_object_type,
1627 p_object_id => p_object_id
1628 );
1629 END IF;
1630
1631 IF p_object_type = 'MODL' THEN
1632 OPEN c_target_model (p_object_id);
1633 FETCH c_target_model INTO l_target_id;
1634 CLOSE c_target_model;
1635 l_model_id := p_object_id;
1636 ELSE
1637 OPEN c_target_score (p_object_id);
1638 FETCH c_target_score INTO l_target_id,l_model_id;
1639 CLOSE c_target_score;
1640 END IF;
1641
1642
1643 OPEN c_model_type(l_model_id);
1644 FETCH c_model_type into l_model_type;
1645 CLOSE c_model_type;
1646
1647 IF l_target_id < L_SEEDED_ID_THRESHOLD THEN
1648 l_is_seeded := TRUE;
1649 END IF;
1650
1651 IF l_model_type = 'CUSTOMER_PROFITABILITY' AND l_is_seeded THEN
1652 AMS_DMSelection_PVT.is_b2b_data_source(
1653 p_model_id => l_model_id,
1654 x_is_b2b => l_is_b2b
1655 );
1656
1657 IF l_is_b2b THEN
1658 l_is_b2b_cust := TRUE ;
1659 END IF;
1660 END IF;
1661
1662 IF l_is_b2b_cust THEN
1663 AMS_UTILITY_PVT.debug_message('Note: None of the attributes from the data source "Organization Contacts" will be used for mining as they are not relevant for this model.');
1664 END IF;
1665
1666 get_select_fields (
1667 p_data_source_id => p_data_source_id,
1668 p_target_id => l_target_id,
1669 p_is_b2bcustprof => l_is_b2b_cust,
1670 x_select_fields => l_select_list,
1671 x_return_status => l_return_status
1672 );
1673
1674 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1675 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1676 END IF;
1677
1678 --kbasavar 12/17/2004 Perf Bug 4074433
1679 --This procedure will return two where clause strings one for creating view and the other for querying
1680 --The string for selections comes with bind variables.
1681 get_from_where_clause (
1682 p_object_type => p_object_type,
1683 p_object_id => p_object_id,
1684 p_data_source_id => p_data_source_id,
1685 x_from_clause => l_from_clause,
1686 x_where_clause_sel => l_where_clause_sel,
1687 x_where_clause => l_where_clause,
1688 x_return_status => l_return_status
1689 );
1690
1691 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1692 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1693 END IF;
1694
1695 AMS_Utility_PVT.create_log (
1696 x_return_status => l_log_return_status,
1697 p_arc_log_used_by => p_object_type,
1698 p_log_used_by_id => p_object_id,
1699 p_msg_data => SUBSTR ('SQL: ' || l_select_list || l_from_clause || l_where_clause, 1, 4000),
1700 p_msg_type => 'DEBUG'
1701 );
1702
1703 --added rosharma 20-sep-2003
1704 --check to see that only one row exists in the data source for the primary key
1705 OPEN c_ds_pk_field (p_data_source_id);
1706 FETCH c_ds_pk_field INTO l_ds_pk_field;
1707 CLOSE c_ds_pk_field;
1708
1709 l_check_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (';
1710 l_check_sql := l_check_sql || 'SELECT ' || l_ds_pk_field || ', COUNT(*) ' || l_from_clause || l_where_clause_sel;
1711 l_check_sql := l_check_sql || ' GROUP BY ' || l_ds_pk_field || ' HAVING COUNT(*) > 1)';
1712
1713
1714 AMS_Utility_PVT.create_log (
1715 x_return_status => l_log_return_status,
1716 p_arc_log_used_by => p_object_type,
1717 p_log_used_by_id => p_object_id,
1718 p_msg_data => SUBSTR ('SQL: ' || l_check_sql, 1, 4000)
1719 );
1720
1721 BEGIN
1722 EXECUTE IMMEDIATE l_check_sql INTO l_dummy USING p_object_type,p_object_id;
1723 EXCEPTION
1724 WHEN NO_DATA_FOUND THEN
1725 l_dummy := 0;
1726 END;
1727
1728 AMS_Utility_PVT.create_log (
1729 x_return_status => l_log_return_status,
1730 p_arc_log_used_by => p_object_type,
1731 p_log_used_by_id => p_object_id,
1732 p_msg_data => 'l_dummy :: ' || l_dummy
1733 );
1734
1735 IF l_dummy = 1 THEN
1736 AMS_UTILITY_PVT.debug_message(L_API_NAME || ' :: ' || 'Data Source mapping not 1-to-1, raising error...');
1737 AMS_Utility_PVT.Error_Message('AMS_DM_DS_MAPPING_ERROR');
1738 RAISE FND_API.G_EXC_ERROR;
1739 END IF;
1740
1741 IF p_object_type = 'MODL' THEN
1742 create_build_views (
1743 p_object_id,
1744 l_select_list,
1745 l_from_clause,
1746 l_where_clause
1747 );
1748 ELSE
1749 create_apply_views (
1750 p_object_id,
1751 l_select_list,
1755 END IF;
1752 l_from_clause,
1753 l_where_clause
1754 );
1756 EXCEPTION
1757 WHEN OTHERS THEN
1758 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1759 AMS_Utility_PVT.create_log (
1760 x_return_status => l_log_return_status,
1761 p_arc_log_used_by => p_object_type,
1762 p_log_used_by_id => p_object_id,
1763 p_msg_data => ' Exception in generate_odm_input_views status ' || x_return_status
1764 );
1765 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1766 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1767 END IF;
1768 -- Standard call to get message count and if count=1, get the message
1769 FND_MSG_PUB.Count_And_Get (
1770 p_encoded => FND_API.G_FALSE,
1771 p_count => x_msg_count,
1772 p_data => x_msg_data
1773 );
1774 END generate_odm_input_views;
1775
1776
1777 PROCEDURE randomize_build_data (
1778 p_object_type IN VARCHAR2,
1779 p_object_id IN NUMBER
1780
1781 )
1782 IS
1783 CURSOR c_check_targets(data_flag VARCHAR2) IS
1784 SELECT COUNT(*) FROM ams_dm_source
1785 WHERE training_data_flag = data_flag
1786 AND arc_used_for_object = p_object_type
1787 AND used_for_object_id = p_object_id
1788 AND TARGET_VALUE = '1';
1789 l_target_count NUMBER;
1790
1791 BEGIN
1792 DBMS_RANDOM.initialize (TO_NUMBER (TO_CHAR (SYSDATE, 'DDSSSS')));
1793
1794 -- choang - 09-may-2002
1795 -- per ODM development, changed build data split as:
1796 -- 70% training data, 30% test data.
1797 -- UPDATE ams_dm_source
1798 -- SET training_data_flag = DECODE (MOD (DBMS_RANDOM.random, 2), 0, 'N', 'Y')
1799 -- SET training_data_flag = DECODE (MOD (ABS (DBMS_RANDOM.random), 10), 0, 'N', 1, 'N', 2, 'N', 'Y')
1800 -- WHERE arc_used_for_object = p_object_type
1801 -- AND used_for_object_id = p_object_id;
1802
1803 -- kbasavar - 24-June-2003
1804 -- per 11.5.10 enhancements changed to split the positive and negative targets by 70-30
1805 UPDATE ams_dm_source
1806 SET training_data_flag = DECODE (MOD (ABS (DBMS_RANDOM.random), 10), 0, 'N', 1, 'N', 2, 'N', 'Y')
1807 WHERE arc_used_for_object = p_object_type
1808 AND used_for_object_id = p_object_id
1809 AND TARGET_VALUE = '0';
1810
1811 UPDATE ams_dm_source
1812 SET training_data_flag = DECODE (MOD (ABS (DBMS_RANDOM.random), 10), 0, 'N', 1, 'N', 2, 'N', 'Y')
1813 WHERE arc_used_for_object = p_object_type
1814 AND used_for_object_id = p_object_id
1815 AND TARGET_VALUE = '1';
1816
1817 OPEN c_check_targets('Y');
1818 FETCH c_check_targets INTO l_target_count;
1819
1820 IF (AMS_DEBUG_HIGH_ON) THEN
1821 AMS_Utility_PVT.debug_message ('Training dataset target count..' || l_target_count);
1822 END IF;
1823
1824 IF (c_check_targets%NOTFOUND OR l_target_count=0) THEN
1825 AMS_Utility_PVT.Error_Message('AMS_MODEL_NO_BAL_POSITIVE_TGTS');
1826 RAISE FND_API.G_EXC_ERROR;
1827 END IF;
1828
1829 CLOSE c_check_targets;
1830
1831 OPEN c_check_targets('N');
1832 FETCH c_check_targets INTO l_target_count;
1833
1834 IF (AMS_DEBUG_HIGH_ON) THEN
1835 AMS_Utility_PVT.debug_message ('Test dataset target count..' || l_target_count);
1836 END IF;
1837
1838 IF (c_check_targets%NOTFOUND OR l_target_count=0) THEN
1839 AMS_Utility_PVT.Error_Message('AMS_MODEL_NO_BAL_POSITIVE_TGTS');
1840 RAISE FND_API.G_EXC_ERROR;
1841 END IF;
1842
1843 CLOSE c_check_targets;
1844
1845 DBMS_RANDOM.terminate;
1846 END randomize_build_data;
1847
1848
1849 -- choang - 03-jun-2002 - obseleted
1850 PROCEDURE get_select_list (
1851 p_target_type IN VARCHAR2,
1852 x_select_list OUT NOCOPY VARCHAR2
1853 )
1854 IS
1855 BEGIN
1856 x_select_list := '';
1857
1858 END get_select_list;
1859
1860
1861 --
1862 -- NOTE
1863 -- Using ad_ddl.do_ddl, the table names must be fully qualified
1864 -- with the schema name, otherwise, a table or view not found
1865 -- exception will be thrown.
1866 --
1867 PROCEDURE create_build_views (
1868 p_object_id IN NUMBER,
1869 p_select_list IN VARCHAR2,
1870 p_from_clause IN VARCHAR2,
1871 p_where_clause IN VARCHAR2
1872 )
1873 IS
1874
1875 l_return_status VARCHAR2(1);
1876
1877 l_result BOOLEAN;
1878 l_status VARCHAR2(10);
1879 l_industry VARCHAR2(10);
1880 l_sql_str VARCHAR2(32000);
1881 l_training_view VARCHAR2(30);
1882 l_test_view VARCHAR2(30);
1883 l_odm_schema VARCHAR2(30);
1884
1885 BEGIN
1886 IF (AMS_DEBUG_HIGH_ON) THEN
1887 AMS_Utility_PVT.debug_message ('create_build_views BEGIN' );
1888 END IF;
1889
1890 -- Get the ODM schema name that willbe used later for GRANTS and SYNONYMS
1891 l_odm_schema := fnd_oracle_schema.GetOuValue(G_ODM_SCHEMA_LOOKUP);
1892 IF l_odm_schema IS NULL THEN
1893 l_odm_schema := G_ODM_SCHEMA_NAME;
1894 END IF;
1895
1896 l_sql_str := 'SELECT ' || p_select_list;
1897 l_sql_str := l_sql_str || p_from_clause;
1898 l_sql_str := l_sql_str || p_where_clause;
1902
1899
1900 l_training_view := 'AMS_DM_' || p_object_id || '_TRAIN_V';
1901 l_test_view := 'AMS_DM_' || p_object_id || '_TEST_V';
1903 IF (AMS_DEBUG_HIGH_ON) THEN
1904 AMS_Utility_PVT.debug_message ('ODM Schema' || l_odm_schema );
1905 AMS_Utility_PVT.debug_message ('Creating Training View ' || l_training_view );
1906 AMS_Utility_PVT.debug_message ('Creating Test View ' || l_test_view );
1907
1908 END IF;
1909
1910 EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ' || l_training_view || ' AS ' || l_sql_str || ' AND s.training_data_flag = ''Y''';
1911 -- fix for bug # 4027150. ( Added table alias before the column name )
1912
1913 EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ' || l_test_view || ' AS ' || l_sql_str || ' AND s.training_data_flag = ''N''';
1914 -- fix for bug # 4027150. ( Added table alias before the column name )
1915
1916 IF FND_PROFILE.Value('AMS_DM_USE_DBLINK') = 'N' OR FND_PROFILE.Value('AMS_DM_USE_DBLINK') IS NULL THEN
1917 -- Grant SELECT permissions to the ODM schema to read from these view
1918 EXECUTE IMMEDIATE 'GRANT SELECT on ' || l_training_view || ' to ' || l_odm_schema;
1919 EXECUTE IMMEDIATE 'GRANT SELECT on ' || l_test_view || ' to ' || l_odm_schema;
1920
1921 -- Also create synonyms for the views in the ODM schema
1922 EXECUTE IMMEDIATE 'CREATE OR REPLACE synonym ' || l_odm_schema || '.' || l_training_view || ' for ' || l_training_view;
1923 EXECUTE IMMEDIATE 'CREATE OR REPLACE synonym ' || l_odm_schema || '.' || l_test_view || ' for ' || l_test_view;
1924
1925 IF (AMS_DEBUG_HIGH_ON) THEN
1926 AMS_Utility_PVT.debug_message ('Created synonyms for views in ' || l_odm_schema || ' schema.' );
1927 END IF;
1928 END IF;
1929
1930 IF (AMS_DEBUG_HIGH_ON) THEN
1931 AMS_Utility_PVT.debug_message ('create_build_views END' );
1932 END IF;
1933
1934 END create_build_views;
1935
1936
1937 --
1938 -- NOTE
1939 -- Using ad_ddl.do_ddl, the table names must be fully qualified
1940 -- with the schema name, otherwise, a table or view not found
1941 -- exception will be thrown.
1942 --
1943 PROCEDURE create_apply_views (
1944 p_object_id IN NUMBER,
1945 p_select_list IN VARCHAR2,
1946 p_from_clause IN VARCHAR2,
1947 p_where_clause IN VARCHAR2
1948 )
1949 IS
1950
1951 l_result BOOLEAN;
1952 l_status VARCHAR2(10);
1953 l_industry VARCHAR2(10);
1954 l_sql_str VARCHAR2(32000);
1955 l_apply_view VARCHAR2(30);
1956 l_odm_schema VARCHAR2(30);
1957 BEGIN
1958
1959 IF (AMS_DEBUG_HIGH_ON) THEN
1960 AMS_Utility_PVT.debug_message ('create_apply_views BEGIN' );
1961 END IF;
1962
1963 -- Get the ODM schema name that willbe used later for GRANTS and SYNONYMS
1964 l_odm_schema := fnd_oracle_schema.GetOuValue(G_ODM_SCHEMA_LOOKUP);
1965 IF l_odm_schema IS NULL THEN
1966 l_odm_schema := G_ODM_SCHEMA_NAME;
1967 END IF;
1968
1969
1970 l_sql_str := 'SELECT ' || p_select_list;
1971 l_sql_str := l_sql_str || p_from_clause;
1972 l_sql_str := l_sql_str || p_where_clause;
1973
1974 l_apply_view := 'AMS_DM_' || p_object_id || '_APPLY_V';
1975
1976 IF (AMS_DEBUG_HIGH_ON) THEN
1977 AMS_Utility_PVT.debug_message ('ODM Schema' || l_odm_schema );
1978 AMS_Utility_PVT.debug_message ('Creating APPLY View ' || l_apply_view);
1979 END IF;
1980
1981 EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ' || l_apply_view || ' AS ' || l_sql_str;
1982
1983 IF FND_PROFILE.Value('AMS_DM_USE_DBLINK') = 'N' OR FND_PROFILE.Value('AMS_DM_USE_DBLINK') IS NULL THEN
1984 -- Grant SELECT permissions to the ODM schema to read from apply view
1985 EXECUTE IMMEDIATE 'GRANT SELECT on ' || l_apply_view || ' to ' || l_odm_schema;
1986
1987 -- Also create synonyms for the view in the ODM schema
1988 EXECUTE IMMEDIATE 'CREATE OR REPLACE synonym ' || l_odm_schema || '.' || l_apply_view || ' for ' || l_apply_view;
1989 IF (AMS_DEBUG_HIGH_ON) THEN
1990 AMS_Utility_PVT.debug_message ('Created synonyms for views in ' || l_odm_schema || ' schema.' );
1991 END IF;
1992 END IF;
1993
1994 IF (AMS_DEBUG_HIGH_ON) THEN
1995 AMS_Utility_PVT.debug_message ('create_apply_views END' );
1996 END IF;
1997
1998 END create_apply_views;
1999
2000
2001 ---------------------------------------------------------------
2002 -- Purpose:
2003 -- Retrieve the selected fields for model building.
2004 --
2005 -- NOTE:
2006 -- Only select fields from the data source which are active
2007 -- and not used as a target field.
2008 --
2009 -- Maximum number of fields depends on the size of the
2010 -- field name. The maximum number of characters the
2011 -- x_select_fields varchar2 buffer is 32000.
2012 -- Parameter:
2013 -- p_data_source_id
2014 -- x_select_fields
2015 -- x_return_status
2016 ---------------------------------------------------------------
2017 PROCEDURE get_select_fields (
2018 p_data_source_id IN NUMBER,
2019 p_target_id IN NUMBER,
2020 p_is_b2bcustprof IN BOOLEAN,
2021 x_select_fields OUT NOCOPY VARCHAR2,
2022 x_return_status OUT NOCOPY VARCHAR2
2023 )
2024 IS
2028 WHERE (lst.list_source_type_id = p_data_source_id
2025 CURSOR c_dup_fields (p_data_source_id IN NUMBER, p_target_field IN NUMBER, p_target_id IN NUMBER) IS
2026 SELECT lsf.source_column_name
2027 FROM ams_list_src_fields lsf , ams_list_src_types lst
2029 OR lst.list_source_type_id IN
2030 (SELECT dts.data_source_id
2031 FROM ams_dm_target_sources dts , ams_list_src_type_assocs lsa
2032 WHERE dts.target_id = p_target_id
2033 AND lsa.sub_source_type_id = dts.data_source_id
2034 AND lsa.master_source_type_id = p_data_source_id
2035 AND lsa.enabled_flag = 'Y')
2036 )
2037 AND lst.enabled_flag = 'Y'
2038 AND lsf.list_source_type_id = lst.list_source_type_id
2039 AND lsf.analytics_flag = 'Y'
2040 AND lsf.field_data_type in ('NUMBER' , 'VARCHAR2')
2041 AND lsf.list_source_field_id <> p_target_field
2042 AND lsf.enabled_flag = 'Y'
2043 GROUP BY lsf.source_column_name
2044 HAVING COUNT(*) > 1
2045 ;
2046
2047 CURSOR c_fields (p_data_source_id IN NUMBER, p_target_field IN NUMBER, p_target_id IN NUMBER) IS
2048 SELECT lst.source_object_name || '.' || lsf.source_column_name
2049 FROM ams_list_src_fields lsf , ams_list_src_types lst
2050 WHERE (lst.list_source_type_id = p_data_source_id
2051 OR lst.list_source_type_id IN
2052 (SELECT dts.data_source_id
2053 FROM ams_dm_target_sources dts , ams_list_src_type_assocs lsa
2054 WHERE dts.target_id = p_target_id
2055 AND lsa.sub_source_type_id = dts.data_source_id
2056 AND lsa.master_source_type_id = p_data_source_id
2057 AND lsa.enabled_flag = 'Y')
2058 )
2059 AND lst.enabled_flag = 'Y'
2060 AND lsf.list_source_type_id = lst.list_source_type_id
2061 AND lsf.analytics_flag = 'Y'
2062 AND lsf.field_data_type in ('NUMBER' , 'VARCHAR2')
2063 AND lsf.list_source_field_id <> p_target_field
2064 AND lsf.enabled_flag = 'Y'
2065 AND lsf.source_column_name not in ('SOURCE_ID', 'TARGET_VALUE')
2066 -- Fix for bug # 4027150, added a filter not to select source_id and target_value
2067 ;
2068
2069 CURSOR c_dup_fields_custprof (p_data_source_id IN NUMBER, p_target_field IN NUMBER, p_target_id IN NUMBER) IS
2070 SELECT lsf.source_column_name
2071 FROM ams_list_src_fields lsf , ams_list_src_types lst
2072 WHERE lst.list_source_type_id IN
2073 (SELECT dts.data_source_id
2074 FROM ams_dm_target_sources dts , ams_list_src_type_assocs lsa
2075 WHERE dts.target_id = p_target_id
2076 AND lsa.sub_source_type_id = dts.data_source_id
2077 AND lsa.master_source_type_id = p_data_source_id
2078 AND lsa.enabled_flag = 'Y')
2079 AND lst.enabled_flag = 'Y'
2080 AND lsf.list_source_type_id = lst.list_source_type_id
2081 AND lsf.analytics_flag = 'Y'
2082 AND lsf.field_data_type in ('NUMBER' , 'VARCHAR2')
2083 AND lsf.list_source_field_id <> p_target_field
2084 AND lsf.enabled_flag = 'Y'
2085 GROUP BY lsf.source_column_name
2086 HAVING COUNT(*) > 1
2087 ;
2088
2089 CURSOR c_fields_custprof (p_data_source_id IN NUMBER, p_target_field IN NUMBER, p_target_id IN NUMBER) IS
2090 SELECT lst.source_object_name || '.' || lsf.source_column_name
2091 FROM ams_list_src_fields lsf , ams_list_src_types lst
2092 WHERE lst.list_source_type_id IN
2093 (SELECT dts.data_source_id
2094 FROM ams_dm_target_sources dts , ams_list_src_type_assocs lsa
2095 WHERE dts.target_id = p_target_id
2096 AND lsa.sub_source_type_id = dts.data_source_id
2097 AND lsa.master_source_type_id = p_data_source_id
2098 AND lsa.enabled_flag = 'Y')
2099 AND lst.enabled_flag = 'Y'
2100 AND lsf.list_source_type_id = lst.list_source_type_id
2101 AND lsf.analytics_flag = 'Y'
2102 AND lsf.field_data_type in ('NUMBER' , 'VARCHAR2')
2103 AND lsf.list_source_field_id <> p_target_field
2104 AND lsf.enabled_flag = 'Y'
2105 ;
2106
2107 CURSOR c_target_field (p_target_id IN NUMBER) IS
2108 SELECT target.source_field_id
2109 FROM ams_dm_targets_b target
2110 WHERE target.target_id = p_target_id
2111 ;
2112
2113 l_field VARCHAR2(151);
2114 l_target_field NUMBER;
2115 l_count NUMBER;
2116 l_dup_fields VARCHAR2(32767);
2117 BEGIN
2118 x_return_status := FND_API.G_RET_STS_SUCCESS;
2119
2120 x_select_fields := 'distinct s.source_id'; -- mandatory identifier for ODM results
2121
2122 OPEN c_target_field (p_target_id);
2123 FETCH c_target_field INTO l_target_field;
2124 CLOSE c_target_field;
2125
2126 IF p_is_b2bcustprof THEN
2127 OPEN c_dup_fields_custprof (p_data_source_id, l_target_field, p_target_id);
2128 FETCH c_dup_fields_custprof INTO l_field;
2129 l_dup_fields := l_field;
2130 LOOP
2131 l_field := NULL;
2132 FETCH c_dup_fields_custprof INTO l_field;
2133 EXIT WHEN c_dup_fields_custprof%NOTFOUND;
2134 l_dup_fields := l_dup_fields || ', ' || l_field;
2135 END LOOP;
2136 l_count:=c_dup_fields_custprof%ROWCOUNT;
2137 IF l_count <> 0 THEN
2141 ELSE
2138 IF l_count <= 15 THEN
2139 AMS_Utility_PVT.error_message ('AMS_DM_DUP_SOURCE_FIELDS' , 'FIELDS' , l_dup_fields);
2140 x_return_status := FND_API.G_RET_STS_ERROR;
2142 AMS_Utility_PVT.error_message ('AMS_DM_DUP_SOURCE_FIELDS_NUM' , 'FIELD_COUNT' , l_count);
2143 x_return_status := FND_API.G_RET_STS_ERROR;
2144 END IF;
2145 CLOSE c_dup_fields_custprof;
2146 return;
2147 END IF;
2148 CLOSE c_dup_fields_custprof;
2149
2150 OPEN c_fields_custprof (p_data_source_id, l_target_field, p_target_id);
2151 -- logic:
2152 -- fetch first field into out param
2153 -- loop through all other fields
2154 -- append ", <column name>" to out param
2155 -- if cursor has no rows, return error
2156 FETCH c_fields_custprof INTO l_field;
2157 --changed rosharma 15-may-2003 bug # 2961532
2158 x_select_fields := x_select_fields || ', ' || l_field;
2159 --x_select_fields := x_select_fields || ', ds' || l_counter || '.' || l_field;
2160 --end change rosharma 15-may-2003 bug # 2961532
2161 LOOP
2162 l_field := NULL;
2163 -- if only one field or no field was fetched
2164 -- exit immediately
2165 FETCH c_fields_custprof INTO l_field;
2166 EXIT WHEN c_fields_custprof%NOTFOUND;
2167 --changed rosharma 15-may-2003 bug # 2961532
2168 x_select_fields := x_select_fields || ', ' || l_field;
2169 --x_select_fields := x_select_fields || ', ds' || l_counter || '.' || l_field;
2170 --end change rosharma 15-may-2003 bug # 2961532
2171 END LOOP;
2172
2173 IF c_fields_custprof%ROWCOUNT = 0 THEN
2174 AMS_Utility_PVT.error_message ('AMS_DM_NO_VALID_SOURCE_FIELDS');
2175 x_return_status := FND_API.G_RET_STS_ERROR;
2176 END IF;
2177 CLOSE c_fields_custprof;
2178
2179
2180
2181 ELSE
2182 OPEN c_dup_fields (p_data_source_id, l_target_field, p_target_id);
2183 FETCH c_dup_fields INTO l_field;
2184 l_dup_fields := l_field;
2185 LOOP
2186 l_field := NULL;
2187 FETCH c_dup_fields INTO l_field;
2188 EXIT WHEN c_dup_fields%NOTFOUND;
2189 l_dup_fields := l_dup_fields || ', ' || l_field;
2190 END LOOP;
2191 l_count:=c_dup_fields%ROWCOUNT;
2192 IF l_count <> 0 THEN
2193 IF l_count <= 15 THEN
2194 AMS_Utility_PVT.error_message ('AMS_DM_DUP_SOURCE_FIELDS' , 'FIELDS' , l_dup_fields);
2195 x_return_status := FND_API.G_RET_STS_ERROR;
2196 ELSE
2197 AMS_Utility_PVT.error_message ('AMS_DM_DUP_SOURCE_FIELDS_NUM' , 'FIELD_COUNT' , l_count);
2198 x_return_status := FND_API.G_RET_STS_ERROR;
2199 END IF;
2200 CLOSE c_dup_fields;
2201 return;
2202 END IF;
2203 CLOSE c_dup_fields;
2204
2205
2206 OPEN c_fields (p_data_source_id, l_target_field, p_target_id);
2207 -- logic:
2208 -- fetch first field into out param
2209 -- loop through all other fields
2210 -- append ", <column name>" to out param
2211 -- if cursor has no rows, return error
2212 FETCH c_fields INTO l_field;
2213 --changed rosharma 15-may-2003 bug # 2961532
2214 x_select_fields := x_select_fields || ', ' || l_field;
2215 --x_select_fields := x_select_fields || ', ds' || l_counter || '.' || l_field;
2216 --end change rosharma 15-may-2003 bug # 2961532
2217 LOOP
2218 l_field := NULL;
2219 -- if only one field or no field was fetched
2220 -- exit immediately
2221 FETCH c_fields INTO l_field;
2222 EXIT WHEN c_fields%NOTFOUND;
2223 --changed rosharma 15-may-2003 bug # 2961532
2224 x_select_fields := x_select_fields || ', ' || l_field;
2225 --x_select_fields := x_select_fields || ', ds' || l_counter || '.' || l_field;
2226 --end change rosharma 15-may-2003 bug # 2961532
2227 END LOOP;
2228
2229 IF c_fields%ROWCOUNT = 0 THEN
2230 AMS_Utility_PVT.error_message ('AMS_DM_NO_VALID_SOURCE_FIELDS');
2231 x_return_status := FND_API.G_RET_STS_ERROR;
2232 END IF;
2233 CLOSE c_fields;
2234
2235 END IF;
2236
2237 x_select_fields := x_select_fields || ', s.target_value'; -- mandatory target field
2238 END get_select_fields;
2239
2240
2241 PROCEDURE get_from_where_clause (
2242 p_object_type IN VARCHAR2,
2243 p_object_id IN NUMBER,
2244 p_data_source_id IN NUMBER,
2245 x_from_clause OUT NOCOPY VARCHAR2,
2246 x_where_clause_sel OUT NOCOPY VARCHAR2,
2247 x_where_clause OUT NOCOPY VARCHAR2,
2248 x_return_status OUT NOCOPY VARCHAR2
2249 )
2250 IS
2251 L_SEEDED_ID_THRESHOLD CONSTANT NUMBER := 10000;
2252
2253 -- assume data source is enabled.
2254 CURSOR c_data_source (p_data_source_id IN NUMBER) IS
2255 SELECT source_object_name
2256 , source_object_name||decode(UPPER(remote_flag),'Y','@'||database_link,'')
2260 ;
2257 , source_object_pk_field
2258 FROM ams_list_src_types
2259 WHERE list_source_type_id = p_data_source_id
2261
2262 l_object_name VARCHAR2(30);
2263 l_object_name_full VARCHAR2(151);
2264 l_pk_field VARCHAR2(30);
2265
2266 CURSOR c_target_id_model (p_model_id IN NUMBER) IS
2267 SELECT target_id
2268 FROM ams_dm_models_v
2269 WHERE model_id = p_model_id
2270 ;
2271
2272 CURSOR c_target_id_score (p_score_id IN NUMBER) IS
2273 SELECT model.target_id,model.model_id
2274 FROM ams_dm_models_v model , ams_dm_scores_v score
2275 WHERE model.model_id = score.model_id
2276 AND score.score_id = p_score_id
2277 ;
2278
2279 l_target_id NUMBER;
2280
2281 CURSOR c_child_sources (p_target_id IN NUMBER) IS
2282 SELECT a.source_object_name , a.source_object_name||decode(UPPER(a.remote_flag),'Y','@'||a.database_link,''), a.list_source_type_id
2283 FROM ams_list_src_types a, ams_dm_target_sources b
2284 WHERE a.list_source_type_id = b.data_source_id
2285 AND a.enabled_flag = 'Y'
2286 AND b.target_id = p_target_id
2287 AND EXISTS (SELECT 1 FROM ams_list_src_type_assocs c,ams_dm_targets_b d
2288 WHERE d.target_id = p_target_id
2289 AND c.MASTER_SOURCE_TYPE_ID = d.data_source_id
2290 AND c.SUB_SOURCE_TYPE_ID = b.data_source_id
2291 AND c.enabled_flag = 'Y')
2292 ;
2293
2294 CURSOR c_model_type(p_model_id IN NUMBER) is
2295 SELECT model_type
2296 FROM ams_dm_models_vl
2297 WHERE model_id=p_model_id
2298 ;
2299
2300
2301 l_child_object_name VARCHAR2(30);
2302 l_child_object_name_full VARCHAR2(151);
2303 l_child_ds_id NUMBER;
2304 l_relation_cond VARCHAR2(15000) := '';
2305 l_composite_relation_cond VARCHAR2(15000) := '';
2306
2307 l_result BOOLEAN;
2308 l_status VARCHAR2(10);
2309 l_industry VARCHAR2(10);
2310 l_ams_schema VARCHAR2(30);
2311 l_apps_schema VARCHAR2(30);
2312
2313 l_model_id NUMBER;
2314 l_model_type VARCHAR2(30);
2315 l_is_b2b BOOLEAN := FALSE;
2316 l_is_seeded BOOLEAN := FALSE;
2317
2318 BEGIN
2319 x_return_status := FND_API.G_RET_STS_SUCCESS;
2320
2321 OPEN c_data_source (p_data_source_id);
2322 FETCH c_data_source INTO l_object_name, l_object_name_full, l_pk_field;
2323 IF c_data_source%NOTFOUND THEN
2324 CLOSE c_data_source;
2325 x_return_status := FND_API.G_RET_STS_ERROR;
2326 RETURN;
2327 END IF;
2328 CLOSE c_data_source;
2329
2330 IF p_object_type = 'MODL' THEN
2331 OPEN c_target_id_model (p_object_id);
2332 FETCH c_target_id_model INTO l_target_id;
2333 CLOSE c_target_id_model;
2334 l_model_id := p_object_id;
2335 ELSE
2336 OPEN c_target_id_score (p_object_id);
2337 FETCH c_target_id_score INTO l_target_id,l_model_id;
2338 CLOSE c_target_id_score;
2339 END IF;
2340
2341
2342 OPEN c_model_type(l_model_id);
2343 FETCH c_model_type into l_model_type;
2344 CLOSE c_model_type;
2345
2346 IF l_target_id < L_SEEDED_ID_THRESHOLD THEN
2347 l_is_seeded := TRUE;
2348 END IF;
2349
2350 IF l_model_type = 'CUSTOMER_PROFITABILITY' AND l_is_seeded THEN
2351 AMS_DMSelection_PVT.is_b2b_data_source(
2352 p_model_id => l_model_id,
2353 x_is_b2b => l_is_b2b
2354 );
2355 END IF;
2356
2357 l_result := fnd_installation.get_app_info(
2358 'AMS',
2359 l_status,
2360 l_industry,
2361 l_ams_schema
2362 );
2363
2364 x_from_clause := ' FROM ' || l_ams_schema || '.ams_dm_source s, ';
2365 x_from_clause := x_from_clause || l_object_name_full;
2366 IF l_model_type = 'CUSTOMER_PROFITABILITY' AND l_is_b2b AND l_is_seeded THEN
2367 x_where_clause := ' WHERE s.party_id = ' || l_object_name || '.ORGANIZATION_ID';
2368 ELSE
2369 x_where_clause := ' WHERE s.party_id = ' || l_object_name || '.' || l_pk_field;
2370 END IF;
2371
2372 OPEN c_child_sources (l_target_id);
2373 LOOP
2374 FETCH c_child_sources INTO l_child_object_name , l_child_object_name_full, l_child_ds_id;
2375 EXIT WHEN c_child_sources%NOTFOUND;
2376 x_from_clause := x_from_clause || ', ' || l_child_object_name_full;
2377 -- Get the relation conditions for all the related data sources and plug in
2378 AMS_DMSelection_PVT.get_related_ds_condition ( p_master_ds_id => p_data_source_id,
2379 p_child_ds_id => l_child_ds_id,
2380 x_sql_stmt => l_relation_cond);
2381 IF LENGTH(l_composite_relation_cond) > 0 THEN
2382 l_composite_relation_cond := l_composite_relation_cond || ' AND ';
2383 END IF;
2384 l_composite_relation_cond := l_composite_relation_cond || l_relation_cond;
2385 END LOOP;
2386 CLOSE c_child_sources;
2387
2388 IF (AMS_DEBUG_HIGH_ON) THEN
2392 IF l_model_type = 'CUSTOMER_PROFITABILITY' AND l_is_seeded THEN
2389 AMS_Utility_PVT.debug_message ('get_from_where_clause' || ' :: relation condition : ' || l_composite_relation_cond);
2390 END IF;
2391 IF LENGTH(l_composite_relation_cond) > 0 THEN
2393 l_composite_relation_cond := REPLACE(l_composite_relation_cond , 'AMS_DM_PARTY_PROFIT_V.PARTY_ID' , 'AMS_DM_PARTY_PROFIT_V.PARTY_ID(+)');
2394 IF l_is_b2b THEN
2395 l_composite_relation_cond := REPLACE(l_composite_relation_cond , 'AMS_DM_PARTY_ATTRIBUTES_V.PARTY_ID = AMS_ORG_CONTACT_DETAILS_V.PARTY_ID' , 'AMS_DM_PARTY_ATTRIBUTES_V.PARTY_ID = AMS_ORG_CONTACT_DETAILS_V.ORGANIZATION_ID');
2396 END IF;
2397 END IF;
2398 x_where_clause := x_where_clause || ' AND ' || l_composite_relation_cond;
2399 END IF;
2400 x_where_clause_sel := x_where_clause || ' AND s.arc_used_for_object = :1 ';
2401 x_where_clause_sel := x_where_clause_sel || ' AND s.used_for_object_id = :2 ';
2402
2403 x_where_clause := x_where_clause || ' AND s.arc_used_for_object = ''' || p_object_type || '''';
2404 x_where_clause := x_where_clause || ' AND s.used_for_object_id = ' || p_object_id;
2405
2406 IF (AMS_DEBUG_HIGH_ON) THEN
2407 AMS_Utility_PVT.debug_message ('get_from_where_clause' || ' :: from condition for data source : ' || x_from_clause);
2408 AMS_Utility_PVT.debug_message ('get_from_where_clause' || ' :: where condition for data source : ' || x_where_clause);
2409 END IF;
2410 END get_from_where_clause;
2411
2412
2413 End AMS_DMSource_PVT;