1 PACKAGE BODY ams_dm_score_pvt as
2 /* $Header: amsvdmsb.pls 120.3 2006/07/17 12:03:25 kbasavar noship $ */
3 -- Start of Comments
4 -- Package name : AMS_DM_SCORE_PVT
5 -- Purpose : PACKAGE BODY FOR PRIVATE API
6 -- History :
7 -- 20-Nov-2000 julou created
8 -- 21-Nov-2000 julou added foreign key validation and lookup validation
9 -- added name and description to insert_row and update_row
10 -- 18-Dec-2000 julou added validation for object_version_number in delete procedure
11 -- 23-Jan-2001 choang Changed ams_dm_models_b to ams_dm_models_all_b in uk validation.
12 -- 24-Jan-2001 choang Changed package name to AMS_DM_SCORE_PVT.
13 -- 26-Jan-2001 choang Added increment of object ver num in update api.
14 -- 29-Jan-2001 choang Removed return statement in req item validation.
15 -- 12-Feb-2001 choang 1) Changed model_score to score. 2) added new columns.
16 -- 19-Feb-2001 choang Replaced top_down_flag with row_selection_type.
17 -- 20-Feb-2001 choang Added default N for logs_flag during insert.
18 -- 26-Feb-2001 choang Added custom_setup_id and country_id.
19 -- 27-Feb-2001 choang Added access functionality.
20 -- 28-Feb-2001 choang Changed names of some error messages to reduce length to
21 -- less than 30.
22 -- 06-Mar-2001 choang Added default of status if not provided in create api call.
23 -- 10-Mar-2001 choang 1) added wf_itemkey. 2) added callout to wf startprocess,
24 -- wf_revert(), process_score_success()
25 -- 11-Mar-2001 choang Added handle_preview_request
26 -- 04-Apr-2001 choang Added handle of scheduled_date change to cancel_process or change_schedule
27 -- modified callout to startprocess to include scheduled_timezone_id and scheduled_date
28 -- 07-Apr-2001 choang Added copy_score.
29 -- 11-Apr-2001 choang 1) changed spec of wf_revert 2) added wf_score
30 -- 12-Apr-2001 choang 1) Fixed scheduled date comparison to NULL in update_score. 2) added
31 -- model_used_for_scoring
32 -- 26-Apr-2001 sveerave Changed column names to be of application column names from db column names
33 -- in get_column_value call-out.
34 -- 09-May-2001 choang handle_preview_request was not returning the request_id
35 -- 17-Aug-2001 choang Added custom_setup_id in out param of create api.
36 -- 01-Oct-2001 choang bug 2024520: custom setup id was not being returned by
37 -- the create api.
38 -- 18-Oct-2001 choang Changed logic to check for scoring run submission when
39 -- in DRAFT status.
40 -- 26-Nov-2001 choang fixed updating of model status in process score success.
41 -- 27-Nov-2001 choang Added score_date and results_flag in process score success.
42 -- 13-Dec-2001 choang Modified callout to change_schedule and added wf_startprocess
43 -- to enable re-scoring.
44 -- 17-Dec-2001 choang Added validation for owner update.
45 -- 20-Dec-2001 choang Added logs_flag update in handle_preview_request.
46 -- 17-May-2002 choang bug 2380113: removed g_user_id and g_login_id
47 -- 11-Jun-2002 choang Fixed gscc error for bug 2380113.
48 -- 04-Oct-2002 choang 1) Added cancel_run_request
49 -- 2) Implementation of new Score States: PREVIEWING,
50 -- INVALID and FAILED
51 -- 3) Added cleanupPreviousScoreData
52 -- 28-Nov-2002 rosharma Added validations for
53 -- - min/max records >= 0
54 -- - max records >= min records
55 -- - 0 <= pct random <= 100
56 -- - If selection method is random, random pct is entered
57 -- - If selection method is every nth row, number of rows is entered
58 --
59 -- 30-Jan-2003 nyostos Fixed the following:
60 -- - Changed score name uniqueness code.
61 -- - Bug related to WF process hanging when score owner is not a valid WF approver
62 -- 07-Feb-2003 nyostos Added a different return status for errors encountered in submitting score wf process.
63 -- 09-Feb-2003 rosharma Random % should be > 0. Every Nth Row value should be greater than 0.
64 -- 10-Feb-2003 rosharma in copy method, Copy pct_random and nth_rowalso from ref record, if selection type is 'NTH_RECORD' or 'RANDOM'.
65 -- 14-Mar-2003 nyostos Fixed return status for errors encountered in submitting score wf process.
66 -- 24-Mar-2003 nyostos Fixed bug 2863861.
67 -- 01-May-2003 nyostos Fixed copying of Data Selections Bug 2934000.
68 -- 17-Jun-2003 nyostos Added cleanup code for AMS_DM_SCORE_PCT_RESULTS table.
69 -- 14-Aug-2003 nyostos Added call to cleanupPreviousScoreData in wf_score.
70 -- 20-Aug-2003 rosharma Fixed bug 3104201.
71 -- 22-Aug-2003 nyostos Changed create_score logic to insert appropriate custom_setup_id (145 or 146)
72 -- depending on Scoring Run type. Response and Product Affinity scoring runs
73 -- use custom_setup_id=146 which has Optimal Targeting Chart option.
74 -- 04-Sep-2003 rosharma Fixed bug 3127555.
75 -- 21-Sep-2003 rosharma Audience Data Source uptake changes to copy_score.
76 -- 09-Dec-2003 kbasavar Org Product Affinity change in create_score.
77 -- 09-May-2005 srivikri Fix for bug 4357993.
78 -- 19-May-2005 srivikri fix for bug 4220828
79 --
80 -- NOTE :
81 -- End of Comments
82
83
84 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_DM_SCORE_PVT';
85 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvdmsb.pls';
86 G_DEFAULT_STATUS CONSTANT VARCHAR2(30) := 'DRAFT';
87 G_OBJECT_TYPE_SCORE CONSTANT VARCHAR2(30) := 'SCOR';
88 G_STATUS_TYPE_SCORE CONSTANT VARCHAR2(30) := 'AMS_DM_SCORE_STATUS';
89 G_STATUS_TYPE_MODEL CONSTANT VARCHAR2(30) := 'AMS_DM_MODEL_STATUS';
90 G_STATUS_SCORING CONSTANT VARCHAR2(30) := 'SCORING';
91 G_STATUS_AVAILABLE CONSTANT VARCHAR2(30) := 'AVAILABLE';
92 G_STATUS_COMPLETED CONSTANT VARCHAR2(30) := 'COMPLETED';
93 G_STATUS_SCHEDULED CONSTANT VARCHAR2(30) := 'SCHEDULED';
94 G_STATUS_QUEUED CONSTANT VARCHAR2(30) := 'QUEUED';
95 G_STATUS_INVALID CONSTANT VARCHAR2(30) := 'INVALID';
96 G_STATUS_FAILED CONSTANT VARCHAR2(30) := 'FAILED';
97 G_STATUS_PREVIEWING CONSTANT VARCHAR2(30) := 'PREVIEWING';
98 G_STATUS_ARCHIVED CONSTANT VARCHAR2(30) := 'ARCHIVED';
99
100 -- 22-Aug-2003 nyostos Added for cue card menu with Optimal Targeting Chart option.
101 G_OTGT_ACTIVITY_TYPE CONSTANT VARCHAR2(30) := 'OTGT';
102 G_MODEL_TYPE_EMAIL CONSTANT VARCHAR2(30) := 'EMAIL';
103 G_MODEL_TYPE_DMAIL CONSTANT VARCHAR2(30) := 'TELEMARKETING';
104 G_MODEL_TYPE_TELEM CONSTANT VARCHAR2(30) := 'DIRECTMAIL';
105 G_MODEL_TYPE_PROD CONSTANT VARCHAR2(30) := 'PRODUCT_AFFINITY';
106
107 --start changes rosharma 20-aug-2003 bug 3104201
108 G_SEEDED_ID_THRESHOLD CONSTANT NUMBER := 10000;
109 --end changes rosharma 20-aug-2003 bug 3104201
110
111 /***
112 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
113 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
114 ***/
115
116 -- global cursors
117 CURSOR c_user_status_id (p_status_type IN VARCHAR2, p_status_code IN VARCHAR2) IS
118 SELECT user_status_id
119 FROM ams_user_statuses_b
120 WHERE system_status_type = p_status_type
121 AND system_status_code = p_status_code
122 AND default_flag = 'Y'
123 AND enabled_flag = 'Y'
124 ;
125
126 -- Cursor to get the system_status_code for a specific system_status_type and user_status_id
127 CURSOR c_user_status_code (p_status_type IN VARCHAR2, p_status_id IN NUMBER) IS
128 SELECT system_status_code
129 FROM ams_user_statuses_b
130 WHERE system_status_type = p_status_type
131 AND user_status_id = p_status_id
132 ;
133
134 -- foreward procedure and function declarations
135 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
136 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
137 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
138
139 PROCEDURE Complete_Score_Rec (
140 p_score_rec IN Score_Rec_Type,
141 x_complete_rec OUT NOCOPY Score_Rec_Type
142 );
143
144
145 PROCEDURE check_access (
146 p_score_rec IN score_rec_type,
147 x_return_status OUT NOCOPY VARCHAR2
148 );
149
150
151 FUNCTION model_used_for_scoring (
152 p_model_id IN NUMBER,
153 p_current_score_id IN NUMBER
154 ) RETURN VARCHAR2;
155
156
157 --
158 -- Purpose
159 -- Start Workflow process for Previewing Data Selections for the Scoring Run.
160 --
161 -- History
162 -- 04-Oct-2002 nyostos Created.
163 PROCEDURE wf_startPreviewProcess (
164 p_score_id IN NUMBER,
165 p_orig_status_id IN NUMBER,
166 x_tar_score_rec IN OUT NOCOPY score_rec_type,
167 x_return_status OUT NOCOPY VARCHAR2
168 );
169
170 --
171 -- Purpose
172 -- Cleanup previous Scoring Run results data and
173 -- records from ams_dm_source
174 --
175 -- History
176 -- 04-Oct-2002 nyostos Created.
177 PROCEDURE cleanupPreviousScoreData(
178 p_score_id IN NUMBER
179 );
180
181 --
182 -- Purpose
183 -- To check if a Preview request can be started. We cannot Preview
184 -- data selections for a Scoring Run if it has any of the following statuses:
185 -- SCHEDULED, SCORING, PREVIEWING, ARCHIVED, QUEUED..
186 --
187 -- History
188 -- 04-Oct-2002 nyostos Created.
189 PROCEDURE proceedWithPreview(
190 p_score_id IN NUMBER,
191 x_proceed_flag OUT NOCOPY VARCHAR2
192 );
193
194 --
195 -- Purpose
196 -- To check if there is data selected to be Previewed. We cannot Preview
197 -- data selections for a Scoring Run if it has list, segment, workbook,... selected.
198 --
199 -- History
200 -- 04-Oct-2002 nyostos Created.
201 PROCEDURE dataToPreview(
202 p_score_id IN NUMBER,
203 x_data_exists_flag OUT NOCOPY VARCHAR2
204 );
205
206 --
207 -- Purpose
208 -- Returns Scoring Run Status_Code and User_Status_Id for a Scoring Run
209 --
210 -- History
211 -- 04-Oct-2002 nyostos Created.
212 PROCEDURE getScoreStatus(
213 p_score_id IN NUMBER,
214 x_status_code OUT NOCOPY VARCHAR2,
215 x_user_status_id OUT NOCOPY NUMBER
216 );
217
218 --
219 -- Purpose
220 -- To check if Scoring Run data selection sizing options and selection
221 -- method have changed. This would INVALIDate a COMPLETED Scoring Run.
222 --
223 -- History
224 -- 07-Oct-2002 nyostos Created.
225 PROCEDURE check_data_size_changes(
226 p_input_score_rec IN score_rec_type,
227 x_selections_changed_flag OUT NOCOPY VARCHAR2
228 );
229
230
231 --
232 -- Purpose
233 -- Start Workflow process for the scoring run.
234 --
235 -- History
236 -- 12-Dec-2001 choang Created.
237 PROCEDURE wf_startprocess (
238 p_score_id IN NUMBER,
239 p_scheduled_date IN DATE,
240 p_scheduled_timezone_id IN NUMBER,
241 p_orig_status_id IN NUMBER,
242 x_tar_score_rec IN OUT NOCOPY score_rec_type,
243 x_return_status OUT NOCOPY VARCHAR2
244 );
245
246
247 -- Hint: Primary key needs to be returned.
248 PROCEDURE Lock_Score(
249 p_api_version IN NUMBER,
250 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
251 x_return_status OUT NOCOPY VARCHAR2,
252 x_msg_count OUT NOCOPY NUMBER,
253 x_msg_data OUT NOCOPY VARCHAR2,
254 p_score_id IN NUMBER,
255 p_object_version IN NUMBER
256 )
257 IS
258 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Score';
259 l_api_version_number CONSTANT NUMBER := 1.0;
260 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
261 l_score_id NUMBER;
262
263 CURSOR c_Score_b IS
264 SELECT score_id
265 FROM ams_dm_scores_all_b
266 WHERE score_id = p_score_id
267 AND object_version_number = p_object_version
268 FOR UPDATE NOWAIT;
269
270 CURSOR c_Score_tl IS
271 SELECT score_id
272 FROM ams_dm_scores_all_tl
273 WHERE score_id = p_score_id
274 AND USERENV('LANG') IN (language, source_lang)
275 FOR UPDATE NOWAIT;
276
277 BEGIN
278
279 -- Debug Message
280 IF (AMS_DEBUG_HIGH_ON) THEN
281
282 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
283 END IF;
284
285 -- Initialize message list if p_init_msg_list is set to TRUE.
286 IF FND_API.to_Boolean( p_init_msg_list )
287 THEN
288 FND_MSG_PUB.initialize;
289 END IF;
290
291 -- Standard call to check for call compatibility.
292 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
293 p_api_version,
294 l_api_name,
295 G_PKG_NAME)
296 THEN
297 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
298 END IF;
299
300
301 -- Initialize API return status to SUCCESS
302 x_return_status := FND_API.G_RET_STS_SUCCESS;
303
304
305 ------------------------ lock -------------------------
306
307 IF (AMS_DEBUG_HIGH_ON) THEN
308
309
310
311 AMS_Utility_PVT.debug_message(l_full_name||': start');
312
313 END IF;
314 OPEN c_Score_b;
315 FETCH c_Score_b INTO l_score_id;
316 IF (c_Score_b%NOTFOUND) THEN
317 CLOSE c_Score_b;
318 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
319 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
320 FND_MSG_PUB.add;
321 END IF;
322 RAISE FND_API.g_exc_error;
323 END IF;
324
325 CLOSE c_Score_b;
326
327 -------------------- finish --------------------------
328 FND_MSG_PUB.count_and_get(
329 p_encoded => FND_API.g_false,
330 p_count => x_msg_count,
331 p_data => x_msg_data);
332 IF (AMS_DEBUG_HIGH_ON) THEN
333
334 AMS_Utility_PVT.debug_message(l_full_name ||': end');
335 END IF;
336 EXCEPTION
337
338 WHEN AMS_Utility_PVT.resource_locked THEN
339 x_return_status := FND_API.g_ret_sts_error;
340 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
341 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
342 FND_MSG_PUB.add;
343 END IF;
344
345 WHEN FND_API.G_EXC_ERROR THEN
346 ROLLBACK TO LOCK_Score_PVT;
347 x_return_status := FND_API.G_RET_STS_ERROR;
348 -- Standard call to get message count and if count=1, get the message
349 FND_MSG_PUB.Count_And_Get (
350 p_encoded => FND_API.G_FALSE,
351 p_count => x_msg_count,
352 p_data => x_msg_data
353 );
354
355 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
356 ROLLBACK TO LOCK_Score_PVT;
357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
358 -- Standard call to get message count and if count=1, get the message
359 FND_MSG_PUB.Count_And_Get (
360 p_encoded => FND_API.G_FALSE,
361 p_count => x_msg_count,
362 p_data => x_msg_data
363 );
364
365 WHEN OTHERS THEN
366 ROLLBACK TO LOCK_Score_PVT;
367 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
368 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
369 THEN
370 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
371 END IF;
372 -- Standard call to get message count and if count=1, get the message
373 FND_MSG_PUB.Count_And_Get (
374 p_encoded => FND_API.G_FALSE,
375 p_count => x_msg_count,
376 p_data => x_msg_data
377 );
378 End Lock_Score;
379
380
381
382 -- Hint: Primary key needs to be returned.
383 PROCEDURE Create_Score(
384 p_api_version IN NUMBER,
385 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
386 p_commit IN VARCHAR2 := FND_API.G_FALSE,
387 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
388 x_return_status OUT NOCOPY VARCHAR2,
389 x_msg_count OUT NOCOPY NUMBER,
390 x_msg_data OUT NOCOPY VARCHAR2,
391 p_Score_rec IN Score_Rec_Type := G_MISS_Score_REC,
392 x_custom_setup_id OUT NOCOPY NUMBER,
393 x_score_id OUT NOCOPY NUMBER
394 )
395 IS
396 l_api_name CONSTANT VARCHAR2(30) := 'Create_Score';
397 l_api_version_number CONSTANT NUMBER := 1.0;
398
399 l_object_version_number NUMBER := 1;
400 l_dummy NUMBER;
401 l_score_rec AMS_DM_SCORE_PVT.Score_Rec_Type := P_Score_Rec;
402
403 l_access_rec AMS_Access_PVT.access_rec_type;
404
405 l_model_type VARCHAR2(30);
406
407 l_is_org_prod BOOLEAN;
408
409 CURSOR c_id IS
410 SELECT ams_dm_scores_all_b_s.NEXTVAL
411 FROM dual;
412
413 CURSOR c_id_exists (l_id IN NUMBER) IS
414 SELECT 1
415 FROM ams_dm_scores_all_b
416 WHERE score_id = l_id;
417
418 CURSOR c_status_code (p_user_status_id IN NUMBER) IS
419 SELECT system_status_code
420 FROM ams_user_statuses_vl
421 WHERE user_status_id = p_user_status_id;
422
423 -- 22-Aug-2003 - nyostos
424 -- Changed cursor to retrieve custom_setup_id = 145 or 146 (for cue card menu
425 -- with optimal targeting option) depending on Scoring Run Type
426 CURSOR c_custom_setup_id IS
427 SELECT custom_setup_id
428 FROM ams_custom_setups_b
429 WHERE object_type = G_OBJECT_TYPE_SCORE
430 AND activity_type_code IS NULL
431 AND enabled_flag = 'Y'
432 ;
433 CURSOR c_custom_setup_id_otgt IS
434 SELECT custom_setup_id
435 FROM ams_custom_setups_b
436 WHERE object_type = G_OBJECT_TYPE_SCORE
437 AND activity_type_code = G_OTGT_ACTIVITY_TYPE
438 AND enabled_flag = 'Y'
439 ;
440
441 -- 22-Aug-2003 - nyostos
442 -- Cursor to get the model_type for the scoring run
443 CURSOR c_model_type (l_model_id IN NUMBER) IS
444 SELECT model_type
445 FROM ams_dm_models_all_b
446 WHERE model_id = l_model_id
447 ;
448
449 BEGIN
450 -- Standard Start of API savepoint
451 SAVEPOINT CREATE_SCORE_PVT;
452
453 -- Standard call to check for call compatibility.
454 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
455 p_api_version,
456 l_api_name,
457 G_PKG_NAME)
458 THEN
459 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
460 END IF;
461
462 -- Initialize message list if p_init_msg_list is set to TRUE.
463 IF FND_API.to_Boolean( p_init_msg_list ) THEN
464 FND_MSG_PUB.initialize;
465 END IF;
466
467 -- Debug Message
468 IF (AMS_DEBUG_HIGH_ON) THEN
469
470 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' start');
471 END IF;
472
473
474 -- Initialize API return status to SUCCESS
475 x_return_status := FND_API.G_RET_STS_SUCCESS;
476
477 -- ******************************************************************
478 -- Validate Environment
479 -- ******************************************************************
480 IF FND_GLOBAL.User_Id IS NULL THEN
481 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
482 FND_MESSAGE.Set_Name('AMS', 'USER_PROFILE_MISSING');
483 FND_MSG_PUB.ADD;
484 END IF;
485 RAISE FND_API.G_EXC_ERROR;
486 END IF;
487
488
489 IF l_score_rec.score_id IS NULL OR l_score_rec.score_id = FND_API.g_miss_num THEN
490 LOOP
491 l_dummy := NULL;
492 OPEN c_id;
493 FETCH c_id INTO l_score_rec.score_id;
494 CLOSE c_id;
495
496 OPEN c_id_exists(l_score_rec.score_id);
497 FETCH c_id_exists INTO l_dummy;
498 CLOSE c_id_exists;
499 EXIT WHEN l_dummy IS NULL;
500 END LOOP;
501 END IF;
502
503 IF NVL (l_score_rec.user_status_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM AND NVL (l_score_rec.status_code, FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR THEN
504 OPEN c_user_status_id (G_STATUS_TYPE_SCORE, G_DEFAULT_STATUS);
505 FETCH c_user_status_id INTO l_score_rec.user_status_id;
506 CLOSE c_user_status_id;
507 ELSIF NVL (l_score_rec.user_status_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
508 OPEN c_user_status_id (G_STATUS_TYPE_SCORE, l_score_rec.status_code);
509 FETCH c_user_status_id INTO l_score_rec.user_status_id;
510 CLOSE c_user_status_id;
511 END IF;
512
513 OPEN c_status_code (l_score_rec.user_status_id);
514 FETCH c_status_code INTO l_score_rec.status_code;
515 CLOSE c_status_code;
516
517 l_score_rec.status_date := SYSDATE;
518
519 l_score_rec.country_id := FND_PROFILE.value ('AMS_SRCGEN_USER_CITY');
520
521 -- 22-Aug-2003 - nyostos
522 -- Get the Model type to determine which custom setup id to insert for the Scoring Run
523 OPEN c_model_type (l_score_rec.model_id);
524 FETCH c_model_type INTO l_model_type;
525 CLOSE c_model_type;
526
527 --kbasavar for Org Prod Affinity
528 AMS_DMSelection_PVT.is_org_prod_affn(
529 p_model_id => l_score_rec.model_id,
530 x_is_org_prod => l_is_org_prod
531 );
532
533 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
534 OPEN c_custom_setup_id_otgt;
535 FETCH c_custom_setup_id_otgt INTO l_score_rec.custom_setup_id;
536 CLOSE c_custom_setup_id_otgt;
537 ELSE
538 OPEN c_custom_setup_id;
539 FETCH c_custom_setup_id INTO l_score_rec.custom_setup_id;
540 CLOSE c_custom_setup_id;
541 END IF;
542
543
544 -- default row_selection_type
545 IF NVL (l_score_rec.row_selection_type, FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR THEN
546 l_score_rec.row_selection_type := 'STANDARD';
547 END IF;
548
549 -- Validation Section
550 IF ( p_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
551 -- Debug message
552 IF (AMS_DEBUG_HIGH_ON) THEN
553
554 AMS_UTILITY_PVT.debug_message('Private API: Validate_Score');
555 END IF;
556
557 -- Invoke validation procedures
558 Validate_Score (
559 p_api_version => 1.0,
560 p_init_msg_list => FND_API.G_FALSE,
561 p_validation_level => p_validation_level,
562 P_Score_Rec => l_score_rec,
563 p_validation_mode => JTF_PLSQL_API.g_create,
564 x_return_status => x_return_status,
565 x_msg_count => x_msg_count,
566 x_msg_data => x_msg_data
567 );
568 END IF;
569
570 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
571 RAISE FND_API.G_EXC_ERROR;
572 END IF;
573
574
575 -- Debug Message
576 IF (AMS_DEBUG_HIGH_ON) THEN
577
578 AMS_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
579 END IF;
580
581 -- Invoke table handler(AMS_DM_scoreS_B_PKG.Insert_Row)
582 AMS_DM_scoreS_B_PKG.Insert_Row(
583 p_score_id => l_score_rec.score_ID,
584 p_last_update_date => SYSDATE,
585 p_last_updated_by => FND_GLOBAL.USER_ID,
586 p_creation_date => SYSDATE,
587 p_created_by => FND_GLOBAL.USER_ID,
588 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
589 p_object_version_number => l_object_version_number,
590 p_model_id => l_score_rec.model_id,
591 p_user_status_id => l_score_rec.user_status_id,
592 p_status_code => l_score_rec.status_code,
593 p_status_date => l_score_rec.status_date,
594 p_owner_user_id => l_score_rec.owner_user_id,
595 p_results_flag => NVL (l_score_rec.results_flag,'N'),
596 p_logs_flag => NVL (l_score_rec.logs_flag, 'N'),
597 p_scheduled_date => l_score_rec.scheduled_date,
598 p_scheduled_timezone_id => l_score_rec.scheduled_timezone_id,
599 p_score_date => l_score_rec.score_date,
600 p_total_records => l_score_rec.total_records,
601 p_total_positives => l_score_rec.total_positives,
602 p_expiration_date => l_score_rec.expiration_date,
603 p_min_records => l_score_rec.min_records,
604 p_max_records => l_score_rec.max_records,
605 p_row_selection_type => l_score_rec.row_selection_type,
606 p_every_nth_row => l_score_rec.every_nth_row,
607 p_pct_random => l_score_rec.pct_random,
608 p_custom_setup_id => l_score_rec.custom_setup_id,
609 p_country_id => l_score_rec.country_id,
610 p_wf_itemkey => l_score_rec.wf_itemkey,
611 p_score_name => l_score_rec.score_name,
612 p_description => l_score_rec.description,
613 p_attribute_category => l_score_rec.attribute_category,
614 p_attribute1 => l_score_rec.attribute1,
615 p_attribute2 => l_score_rec.attribute2,
616 p_attribute3 => l_score_rec.attribute3,
617 p_attribute4 => l_score_rec.attribute4,
618 p_attribute5 => l_score_rec.attribute5,
619 p_attribute6 => l_score_rec.attribute6,
620 p_attribute7 => l_score_rec.attribute7,
621 p_attribute8 => l_score_rec.attribute8,
622 p_attribute9 => l_score_rec.attribute9,
623 p_attribute10 => l_score_rec.attribute10,
624 p_attribute11 => l_score_rec.attribute11,
625 p_attribute12 => l_score_rec.attribute12,
626 p_attribute13 => l_score_rec.attribute13,
627 p_attribute14 => l_score_rec.attribute14,
628 p_attribute15 => l_score_rec.attribute15);
629
630 x_score_id := l_score_rec.score_id;
631 x_custom_setup_id := l_score_rec.custom_setup_id;
632
633 -- create an entry to the access table for the current
634 -- user/owner.
635 l_access_rec.act_access_to_object_id := l_score_rec.score_id;
636 l_access_rec.arc_act_access_to_object := G_OBJECT_TYPE_SCORE;
637 l_access_rec.user_or_role_id := l_score_rec.owner_user_id;
638 l_access_rec.arc_user_or_role_type := 'USER';
639 l_access_rec.owner_flag := 'Y';
640 l_access_rec.admin_flag := 'Y';
641 l_access_rec.delete_flag := 'N';
642
643 AMS_Access_PVT.create_access (
644 p_api_version => 1.0,
645 p_init_msg_list => FND_API.G_FALSE,
646 p_commit => FND_API.G_FALSE,
647 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
648 x_return_status => x_return_status,
649 x_msg_count => x_msg_count,
650 x_msg_data => x_msg_data,
651 p_access_rec => l_access_rec,
652 x_access_id => l_access_rec.activity_access_id
653 );
654 --
655 -- End of API body
656 --
657
658 -- Standard check for p_commit
659 IF FND_API.to_Boolean( p_commit ) THEN
660 COMMIT WORK;
661 END IF;
662
663
664 -- Debug Message
665 IF (AMS_DEBUG_HIGH_ON) THEN
666
667 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' end');
668 END IF;
669
670 -- Standard call to get message count and if count is 1, get message info.
671 FND_MSG_PUB.Count_And_Get
672 (p_count => x_msg_count,
673 p_data => x_msg_data
674 );
675 EXCEPTION
676
677 WHEN AMS_Utility_PVT.resource_locked THEN
678 x_return_status := FND_API.g_ret_sts_error;
679 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
680 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
681 FND_MSG_PUB.add;
682 END IF;
683
684 WHEN FND_API.G_EXC_ERROR THEN
685 ROLLBACK TO CREATE_Score_PVT;
686 x_return_status := FND_API.G_RET_STS_ERROR;
687 -- Standard call to get message count and if count=1, get the message
688 FND_MSG_PUB.Count_And_Get (
689 p_encoded => FND_API.G_FALSE,
690 p_count => x_msg_count,
691 p_data => x_msg_data
692 );
693
694 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
695 ROLLBACK TO CREATE_Score_PVT;
696 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
697 -- Standard call to get message count and if count=1, get the message
698 FND_MSG_PUB.Count_And_Get (
699 p_encoded => FND_API.G_FALSE,
700 p_count => x_msg_count,
701 p_data => x_msg_data
702 );
703
704 WHEN OTHERS THEN
705 ROLLBACK TO CREATE_Score_PVT;
706 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
707 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
708 THEN
709 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
710 END IF;
711 -- Standard call to get message count and if count=1, get the message
712 FND_MSG_PUB.Count_And_Get (
713 p_encoded => FND_API.G_FALSE,
714 p_count => x_msg_count,
715 p_data => x_msg_data
716 );
717 End Create_Score;
718
719
720 PROCEDURE Update_Score(
721 p_api_version IN NUMBER,
722 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
723 p_commit IN VARCHAR2 := FND_API.G_FALSE,
724 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
725 x_return_status OUT NOCOPY VARCHAR2,
726 x_msg_count OUT NOCOPY NUMBER,
727 x_msg_data OUT NOCOPY VARCHAR2,
728 p_score_rec IN Score_Rec_Type,
729 x_object_version_number OUT NOCOPY NUMBER
730 )
731 IS
732 l_api_name CONSTANT VARCHAR2(30) := 'Update_Score';
733 l_api_version_number CONSTANT NUMBER := 1.0;
734 L_SCORE_STATUS_SCHEDULED CONSTANT VARCHAR2(30) := 'SCHEDULED';
735
736 -- Local Variables
737 l_object_version_number NUMBER;
738 l_tar_score_rec AMS_DM_SCORE_PVT.Score_Rec_Type := p_score_rec;
739 l_rowid ROWID;
740 l_user_timezone_name VARCHAR2(80);
741 l_user_status_id NUMBER;
742 l_monitor_url VARCHAR2(4000);
743 l_run_started VARCHAR2(1);
744 l_selections_changed_flag VARCHAR2(1);
745 l_return_status VARCHAR2(1);
746 l_model_status VARCHAR2(30);
747 l_data_exists_flag VARCHAR2(1);
748 l_schedule_date DATE;
749 l_scheduled_timezone_id NUMBER;
750 l_is_enabled BOOLEAN;
751 l_target_id NUMBER;
752
753 CURSOR c_reference (p_score_id IN NUMBER) IS
754 SELECT *
755 FROM ams_dm_scores_vl
756 WHERE score_id = p_score_id;
757 l_reference_rec c_reference%ROWTYPE;
758
759 CURSOR c_status (p_id IN NUMBER) IS
760 SELECT system_status_code
761 FROM ams_user_statuses_vl
762 WHERE user_status_id = p_id;
763
764 CURSOR c_target_id (p_score_id IN NUMBER) IS
765 SELECT m.target_id from ams_dm_models_all_b m,ams_dm_scores_all_b s
766 WHERE m.model_id = s.model_id
767 AND s.score_id = p_score_id
768 ;
769 BEGIN
770 -- Standard Start of API savepoint
771 SAVEPOINT UPDATE_SCORE_PVT;
772
773 -- Standard call to check for call compatibility.
774 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
775 p_api_version,
776 l_api_name,
777 G_PKG_NAME)
778 THEN
779 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
780 END IF;
781
782 -- Initialize message list if p_init_msg_list is set to TRUE.
783 IF FND_API.to_Boolean( p_init_msg_list ) THEN
784 FND_MSG_PUB.initialize;
785 END IF;
786
787 -- Debug Message
788 IF (AMS_DEBUG_HIGH_ON) THEN
789 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' start');
790 END IF;
791
792
793 -- Initialize API return status to SUCCESS
794 x_return_status := FND_API.G_RET_STS_SUCCESS;
795
796 -- Initialize run process flag to 'N'
797 l_run_started := 'N';
798
799 -- Initialize data selections changed flag to 'N'
800 l_selections_changed_flag := 'N';
801
802 --
803 -- Initialize and default local variables
804 Complete_Score_Rec(
805 p_score_rec => p_score_rec,
806 x_complete_rec => l_tar_score_rec
807 );
808
809 OPEN c_reference (l_tar_score_rec.score_id);
810 FETCH c_reference INTO l_reference_rec;
811 IF (c_reference%NOTFOUND) THEN
812 CLOSE c_reference;
813 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
814 FND_MESSAGE.Set_Name('AMS', 'API_MISSING_UPDATE_TARGET');
815 FND_MESSAGE.Set_Token ('INFO', 'dm_score', FALSE);
816 FND_MSG_PUB.Add;
817 END IF;
818 RAISE FND_API.G_EXC_ERROR;
819 END IF;
820
821 -- Debug Message
822 IF (AMS_DEBUG_HIGH_ON) THEN
823 AMS_UTILITY_PVT.debug_message('Private API: - Close Cursor');
824 END IF;
825 CLOSE c_reference;
826
827 OPEN c_target_id (l_tar_score_rec.score_id);
828 FETCH c_target_id INTO l_target_id;
829 CLOSE c_target_id;
830
831 -- 24-Mar-2003 nyostos Fixed bug 2863861.
832 -- Check if the user is resubmitting the update request (via browser refresh button
833 -- or by pressing the "update" button again) before re-loading the record.
834 IF (p_score_rec.object_version_number <> l_reference_rec.object_version_number) THEN
835 AMS_Utility_PVT.error_message ('AMS_API_RECORD_NOT_FOUND');
836 RAISE FND_API.G_EXC_ERROR;
837 END IF;
838
839 l_tar_score_rec.country_id := FND_PROFILE.value ('AMS_SRCGEN_USER_CITY');
840
841 IF (l_reference_rec.status_code = G_DEFAULT_STATUS OR -- DRAFT
842 l_reference_rec.status_code = G_STATUS_INVALID OR
843 l_reference_rec.status_code = G_STATUS_FAILED) AND p_score_rec.scheduled_date <> FND_API.G_MISS_DATE THEN
844
845 -- First check that Model is still AVAILABLE
846 wf_checkModelStatus ( p_score_id => l_tar_score_rec.score_id,
847 x_return_status => l_return_status,
848 x_model_status => l_model_status
849 );
850
851 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
852 -- First check that the target is enabled
853 AMS_DM_TARGET_PVT.is_target_enabled(
854 p_target_id => l_target_id,
855 x_is_enabled => l_is_enabled
856 );
857 IF l_is_enabled = FALSE THEN
858 IF (AMS_DEBUG_HIGH_ON) THEN
859 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' Target is disabled, cannot score');
860 END IF;
861 -- Set x_return_status to 'T' (for target disabled error). This will results in a different message
862 -- displayed to the user
863 x_return_status := 'T';
864 RETURN;
865 END IF;
866 -- Also check that there is data selections for the Scoring Run
867 -- We should not schedule the Run if there are no data selections
868 dataToPreview (l_tar_score_rec.score_id, l_data_exists_flag);
869 IF l_data_exists_flag = 'N' THEN
870 IF (AMS_DEBUG_HIGH_ON) THEN
871 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' No Data Selections to Score');
872 END IF;
873 -- Set x_return_status to 'D' (for data error). This will results in a different message
874 -- displayed to the user
875 x_return_status := 'D';
876 RETURN;
877 END IF;
878
879 /* choang - 13-dec-2002 - added for nocopy */
880 l_schedule_date := l_tar_score_rec.scheduled_date;
881 l_scheduled_timezone_id := l_tar_score_rec.scheduled_timezone_id;
882 wf_startprocess (
883 p_score_id => l_reference_rec.score_id,
884 p_scheduled_date => l_schedule_date,
885 p_scheduled_timezone_id => l_scheduled_timezone_id,
886 p_orig_status_id => l_reference_rec.user_status_id,
887 x_tar_score_rec => l_tar_score_rec,
888 x_return_status => x_return_status
889 );
890 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
891 x_return_status := 'W';
892 RETURN;
893 END IF;
894 -- Construct the URL that could be used to monitor the WF process. This will be returned to the caller
895 -- to be displayed on a custom confirmation message.
896 l_run_started := 'Y';
897 l_monitor_url := wf_monitor.geturl(wf_core.TRANSLATE('WF_WEB_AGENT'), AMS_WFMOD_PVT.G_DEFAULT_ITEMTYPE, l_tar_score_rec.wf_itemkey, 'NO');
898 IF (AMS_DEBUG_HIGH_ON) THEN
899 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' l_monitor_url = ' || l_monitor_url );
900 END IF;
901 ELSE
902 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_MODEL_NOT_AVAILABLE',
903 p_token_name => 'STATUS',
904 p_token_value => l_model_status) ;
905 RAISE FND_API.G_EXC_ERROR;
906 END IF;
907
908 ELSIF l_reference_rec.status_code = G_STATUS_SCHEDULED AND l_reference_rec.scheduled_date <> l_tar_score_rec.scheduled_date THEN
909
910 -- First check that Model is still AVAILABLE
911 wf_checkModelStatus ( p_score_id => l_tar_score_rec.score_id,
912 x_return_status => l_return_status,
913 x_model_status => l_model_status
914 );
915
916 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
917 -- First check that the target is enabled
918 AMS_DM_TARGET_PVT.is_target_enabled(
919 p_target_id => l_target_id,
920 x_is_enabled => l_is_enabled
921 );
922 IF l_is_enabled = FALSE THEN
923 IF (AMS_DEBUG_HIGH_ON) THEN
924 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' Target is disabled, cannot score');
925 END IF;
926 -- Set x_return_status to 'T' (for target disabled error). This will results in a different message
927 -- displayed to the user
928 x_return_status := 'T';
929 RETURN;
930 END IF;
931 AMS_WFMod_PVT.change_schedule (
932 p_itemkey => l_tar_score_rec.wf_itemkey,
933 p_scheduled_date => l_tar_score_rec.scheduled_date,
934 p_scheduled_timezone_id => l_tar_score_rec.scheduled_timezone_id,
935 x_new_itemkey => l_tar_score_rec.wf_itemkey,
936 x_return_status => x_return_status
937 );
938 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
939 -- if we cannot change the schedule, may be the process has been purged,
940 -- then we go ahead and submit a new process and get a new wf_itemkey
941 IF (AMS_DEBUG_HIGH_ON) THEN
942 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' Change schedule failed' );
943 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' Going to start new build process' );
944 END IF;
945
946 -- Check that there is data selections for the Scoring Run
947 -- We should not schedule the Run if there are no data selections
948 dataToPreview (l_tar_score_rec.score_id, l_data_exists_flag);
949 IF l_data_exists_flag = 'N' THEN
950 IF (AMS_DEBUG_HIGH_ON) THEN
951 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' No Data Selections to Score');
952 END IF;
953 -- Set x_return_status to 'D' (for data error). This will results in a different message
954 -- displayed to the user
955 x_return_status := 'D';
956 RETURN;
957 END IF;
958
959 -- Set reference model status to DRAFT
960 OPEN c_user_status_id (G_STATUS_TYPE_SCORE, G_DEFAULT_STATUS);
961 FETCH c_user_status_id INTO l_user_status_id;
962 CLOSE c_user_status_id;
963
964 l_reference_rec.status_code := G_DEFAULT_STATUS;
965 l_reference_rec.user_status_id := l_user_status_id;
966
967 /* choang - 13-dec-2002 - added for nocopy */
968 l_schedule_date := l_tar_score_rec.scheduled_date;
969 l_scheduled_timezone_id := l_tar_score_rec.scheduled_timezone_id;
970 -- Submit a Build request
971 wf_startprocess (
972 p_score_id => l_reference_rec.score_id,
973 p_scheduled_date => l_schedule_date,
974 p_scheduled_timezone_id => l_scheduled_timezone_id,
975 p_orig_status_id => l_reference_rec.user_status_id,
976 x_tar_score_rec => l_tar_score_rec,
977 x_return_status => x_return_status
978 );
979 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
980 x_return_status := 'W';
981 RETURN;
982 END IF;
983 IF (AMS_DEBUG_HIGH_ON) THEN
984 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' New Item Key ' || l_tar_score_rec.wf_itemkey);
985 END IF;
986
987 END IF;
988
989 -- Construct the URL that could be used to monitor the WF process. This will be returned to the caller
990 -- to be displayed on a custom confirmation message.
991 l_run_started := 'Y';
992 l_monitor_url := wf_monitor.geturl(wf_core.TRANSLATE('WF_WEB_AGENT'), AMS_WFMOD_PVT.G_DEFAULT_ITEMTYPE, l_tar_score_rec.wf_itemkey, 'NO');
993 IF (AMS_DEBUG_HIGH_ON) THEN
994 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' l_monitor_url = ' || l_monitor_url );
995 END IF;
996 ELSE
997 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_MODEL_NOT_AVAILABLE',
998 p_token_name => 'STATUS',
999 p_token_value => l_model_status) ;
1000 RAISE FND_API.G_EXC_ERROR;
1001 END IF;
1002 ELSIF l_reference_rec.status_code = G_STATUS_COMPLETED AND l_reference_rec.scheduled_date <> l_tar_score_rec.scheduled_date THEN
1003 -- First check that Model is still AVAILABLE
1004 wf_checkModelStatus ( p_score_id => l_tar_score_rec.score_id,
1005 x_return_status => l_return_status,
1006 x_model_status => l_model_status
1007 );
1008
1009 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1010 -- First check that the target is enabled
1011 AMS_DM_TARGET_PVT.is_target_enabled(
1012 p_target_id => l_target_id,
1013 x_is_enabled => l_is_enabled
1014 );
1015 IF l_is_enabled = FALSE THEN
1016 IF (AMS_DEBUG_HIGH_ON) THEN
1017 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' Target is disabled, cannot score');
1018 END IF;
1019 -- Set x_return_status to 'T' (for target disabled error). This will results in a different message
1020 -- displayed to the user
1021 x_return_status := 'T';
1022 RETURN;
1023 END IF;
1024 -- Check that there is data selections for the Scoring Run
1025 -- We should not schedule the Run if there are no data selections
1026 dataToPreview (l_tar_score_rec.score_id, l_data_exists_flag);
1027 IF l_data_exists_flag = 'N' THEN
1028 IF (AMS_DEBUG_HIGH_ON) THEN
1029
1030 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' No Data Selections to Score');
1031 END IF;
1032 -- Set x_return_status to 'D' (for data error). This will results in a different message
1033 -- displayed to the user
1034 x_return_status := 'D';
1035 RETURN;
1036 END IF;
1037
1038 /* choang - 13-dec-2002 - added for nocopy */
1039 l_schedule_date := l_tar_score_rec.scheduled_date;
1040 l_scheduled_timezone_id := l_tar_score_rec.scheduled_timezone_id;
1041 wf_startprocess (
1042 p_score_id => l_reference_rec.score_id,
1043 p_scheduled_date => l_schedule_date,
1044 p_scheduled_timezone_id => l_scheduled_timezone_id,
1045 p_orig_status_id => l_reference_rec.user_status_id,
1046 x_tar_score_rec => l_tar_score_rec,
1047 x_return_status => x_return_status
1048 );
1049 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1050 x_return_status := 'W';
1051 RETURN;
1052 END IF;
1053
1054 -- Construct the URL that could be used to monitor the WF process. This will be returned to the caller
1055 -- to be displayed on a custom confirmation message.
1056 l_run_started := 'Y';
1057 l_monitor_url := wf_monitor.geturl(wf_core.TRANSLATE('WF_WEB_AGENT'), AMS_WFMOD_PVT.G_DEFAULT_ITEMTYPE, l_tar_score_rec.wf_itemkey, 'NO');
1058 IF (AMS_DEBUG_HIGH_ON) THEN
1059 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' l_monitor_url = ' || l_monitor_url );
1060 END IF;
1061 ELSE
1062 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_MODEL_NOT_AVAILABLE',
1063 p_token_name => 'STATUS',
1064 p_token_value => l_model_status) ;
1065 RAISE FND_API.G_EXC_ERROR;
1066 END IF;
1067
1068 ELSIF l_reference_rec.status_code = G_STATUS_PREVIEWING AND l_reference_rec.scheduled_date <> FND_API.G_MISS_DATE THEN
1069
1070 -- if the Scoring Run is PREVIEWING, then cancel the preview process first and set the Scoring Run status to DRAFT
1071 IF (AMS_DEBUG_HIGH_ON) THEN
1072 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' Scoring Run is currently previewing');
1073 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' Going to cancel Preview Process. Item Key ' || l_tar_score_rec.wf_itemkey);
1074 END IF;
1075
1076 -- First check that Model is still AVAILABLE
1077 wf_checkModelStatus ( p_score_id => l_tar_score_rec.score_id,
1078 x_return_status => l_return_status,
1079 x_model_status => l_model_status
1080 );
1081
1082 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1083
1084 -- First check that the target is enabled
1085 AMS_DM_TARGET_PVT.is_target_enabled(
1086 p_target_id => l_target_id,
1087 x_is_enabled => l_is_enabled
1088 );
1089 IF l_is_enabled = FALSE THEN
1090 IF (AMS_DEBUG_HIGH_ON) THEN
1091 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' Target is disabled, cannot score');
1092 END IF;
1093 -- Set x_return_status to 'T' (for target disabled error). This will results in a different message
1094 -- displayed to the user
1095 x_return_status := 'T';
1096 RETURN;
1097 END IF;
1098 -- Check that there is data selections for the Scoring Run
1099 -- We should not schedule the Run if there are no data selections
1100 dataToPreview (l_tar_score_rec.score_id, l_data_exists_flag);
1101 IF l_data_exists_flag = 'N' THEN
1102 IF (AMS_DEBUG_HIGH_ON) THEN
1103
1104 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' No Data Selections to Build');
1105 END IF;
1106 -- Set x_return_status to 'D' (for data error). This will results in a different message
1107 -- displayed to the user
1108 x_return_status := 'D';
1109 RETURN;
1110 END IF;
1111
1112 AMS_WFMod_PVT.cancel_process (
1113 p_itemkey => l_tar_score_rec.wf_itemkey,
1114 x_return_status => x_return_status
1115 );
1116
1117 -- Set reference Scoring Run status to DRAFT
1118 OPEN c_user_status_id (G_STATUS_TYPE_SCORE, G_DEFAULT_STATUS);
1119 FETCH c_user_status_id INTO l_user_status_id;
1120 CLOSE c_user_status_id;
1121
1122 l_reference_rec.status_code := G_DEFAULT_STATUS;
1123 l_reference_rec.user_status_id := l_user_status_id;
1124 l_tar_score_rec.wf_itemkey := NULL;
1125
1126 -- Submit a Score Run request
1127 /* choang - 13-dec-2002 - added for nocopy */
1128 l_schedule_date := l_tar_score_rec.scheduled_date;
1129 l_scheduled_timezone_id := l_tar_score_rec.scheduled_timezone_id;
1130 wf_startprocess (
1131 p_score_id => l_reference_rec.score_id,
1132 p_scheduled_date => l_schedule_date,
1133 p_scheduled_timezone_id => l_scheduled_timezone_id,
1134 p_orig_status_id => l_user_status_id,
1135 x_tar_score_rec => l_tar_score_rec,
1136 x_return_status => x_return_status
1137 );
1138 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1139 x_return_status := 'W';
1140 RETURN;
1141 END IF;
1142 IF (AMS_DEBUG_HIGH_ON) THEN
1143 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' New Item Key ' || l_tar_score_rec.wf_itemkey);
1144 END IF;
1145
1146 -- Construct the URL that could be used to monitor the WF process. This will be returned to the caller
1147 -- to be displayed on a custom confirmation message.
1148 l_run_started := 'Y';
1149 l_monitor_url := wf_monitor.geturl(wf_core.TRANSLATE('WF_WEB_AGENT'), AMS_WFMOD_PVT.G_DEFAULT_ITEMTYPE, l_tar_score_rec.wf_itemkey, 'NO');
1150 IF (AMS_DEBUG_HIGH_ON) THEN
1151
1152 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' l_monitor_url = ' || l_monitor_url );
1153 END IF;
1154 ELSE
1155 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_MODEL_NOT_AVAILABLE',
1156 p_token_name => 'STATUS',
1157 p_token_value => l_model_status) ;
1158 RAISE FND_API.G_EXC_ERROR;
1159 END IF;
1160
1161 END IF; -- flipped to scheduled
1162
1163 -- Validate if data selections changed for a COMPLETED Scoring Run, and set the
1164 -- Scoring Run status to INVALID. Make sure that a Run has not been started as
1165 -- this will mess up the statuses
1166 IF ( l_reference_rec.status_code = G_STATUS_COMPLETED AND l_run_started = 'N') THEN
1167
1168 check_data_size_changes (l_tar_score_rec,
1169 l_selections_changed_flag);
1170
1171 IF l_selections_changed_flag = 'Y' THEN
1172 l_tar_score_rec.status_code := G_STATUS_INVALID;
1173
1174 OPEN c_user_status_id (G_STATUS_TYPE_SCORE, l_tar_score_rec.status_code);
1175 FETCH c_user_status_id INTO l_tar_score_rec.user_status_id;
1176 CLOSE c_user_status_id;
1177 END IF;
1178
1179 END IF;
1180
1181 -- Validate next staus
1182 IF l_reference_rec.user_status_id <> l_tar_score_rec.user_status_id THEN
1183 OPEN c_status (l_tar_score_rec.user_status_id);
1184 FETCH c_status INTO l_tar_score_rec.status_code;
1185 CLOSE c_status;
1186 l_tar_score_rec.status_date := SYSDATE;
1187
1188 -- ************************************************
1189 -- BGEORGE - added status order rule driven validation
1190 -- ************************************************
1191 AMS_DM_MODEL_PVT.Validate_next_status (
1192 p_curr_status => l_reference_rec.status_code,
1193 p_next_status => l_tar_score_rec.status_code,
1194 p_system_status_type => G_STATUS_TYPE_SCORE,
1195 x_return_status => x_return_status,
1196 x_msg_count => x_msg_count,
1197 x_msg_data => x_msg_data
1198 );
1199 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1200 RAISE FND_API.G_EXC_ERROR;
1201 END IF;
1202 END IF;
1203
1204 IF ( p_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
1205 -- Debug message
1206 IF (AMS_DEBUG_HIGH_ON) THEN
1207
1208 AMS_UTILITY_PVT.debug_message('Private API: Validate_Score');
1209 END IF;
1210
1211 -- Invoke validation procedures
1212 Validate_Score (
1213 p_api_version => 1.0,
1214 p_init_msg_list => FND_API.G_FALSE,
1215 p_validation_level => p_validation_level,
1216 p_validation_mode => JTF_PLSQL_API.g_update,
1217 p_score_rec => l_tar_score_rec,
1218 x_return_status => x_return_status,
1219 x_msg_count => x_msg_count,
1220 x_msg_data => x_msg_data
1221 );
1222 END IF;
1223
1224 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1225 RAISE FND_API.G_EXC_ERROR;
1226 END IF;
1227
1228 -- Debug Message
1229 IF (AMS_DEBUG_LOW_ON) THEN
1230
1231 AMS_UTILITY_PVT.debug_message('Private API: Calling update table handler',FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1232 END IF;
1233
1234 BEGIN
1235 -- Invoke table handler(AMS_DM_scoreS_B_PKG.Update_Row)
1236 AMS_DM_scoreS_B_PKG.Update_Row(
1237 p_score_ID => l_tar_score_rec.score_id,
1238 p_LAST_UPDATE_DATE => SYSDATE,
1239 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1240 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
1241 p_OBJECT_VERSION_NUMBER => l_tar_score_rec.OBJECT_VERSION_NUMBER,
1242 p_MODEL_ID => l_tar_score_rec.MODEL_ID,
1243 p_USER_STATUS_ID => l_tar_score_rec.USER_STATUS_ID,
1244 p_STATUS_CODE => l_tar_score_rec.status_code,
1245 p_STATUS_DATE => l_tar_score_rec.status_date,
1246 p_OWNER_USER_ID => l_tar_score_rec.OWNER_USER_ID,
1247 p_RESULTS_FLAG => l_tar_score_rec.RESULTS_FLAG,
1248 p_logs_flag => l_tar_score_rec.logs_flag,
1249 p_SCHEDULED_DATE => l_tar_score_rec.SCHEDULED_DATE,
1250 p_SCHEDULED_TIMEZONE_ID => l_tar_score_rec.SCHEDULED_TIMEZONE_ID,
1251 p_SCORE_DATE => l_tar_score_rec.SCORE_DATE,
1252 p_total_records => l_tar_score_rec.total_records,
1253 p_total_positives => l_tar_score_rec.total_positives,
1254 p_EXPIRATION_DATE => l_tar_score_rec.EXPIRATION_DATE,
1255 p_min_records => l_tar_score_rec.min_records,
1256 p_max_records => l_tar_score_rec.max_records,
1257 p_row_selection_type => l_tar_score_rec.row_selection_type,
1258 p_every_nth_row => l_tar_score_rec.every_nth_row,
1259 p_pct_random => l_tar_score_rec.pct_random,
1260 p_custom_setup_id => l_tar_score_rec.custom_setup_id,
1261 p_country_id => l_tar_score_rec.country_id,
1262 p_wf_itemkey => l_tar_score_rec.wf_itemkey,
1263 P_score_NAME => l_tar_score_rec.score_NAME,
1264 p_DESCRIPTION => l_tar_score_rec.DESCRIPTION,
1265 p_ATTRIBUTE_CATEGORY => l_tar_score_rec.ATTRIBUTE_CATEGORY,
1266 p_ATTRIBUTE1 => l_tar_score_rec.ATTRIBUTE1,
1267 p_ATTRIBUTE2 => l_tar_score_rec.ATTRIBUTE2,
1268 p_ATTRIBUTE3 => l_tar_score_rec.ATTRIBUTE3,
1269 p_ATTRIBUTE4 => l_tar_score_rec.ATTRIBUTE4,
1270 p_ATTRIBUTE5 => l_tar_score_rec.ATTRIBUTE5,
1271 p_ATTRIBUTE6 => l_tar_score_rec.ATTRIBUTE6,
1272 p_ATTRIBUTE7 => l_tar_score_rec.ATTRIBUTE7,
1273 p_ATTRIBUTE8 => l_tar_score_rec.ATTRIBUTE8,
1274 p_ATTRIBUTE9 => l_tar_score_rec.ATTRIBUTE9,
1275 p_ATTRIBUTE10 => l_tar_score_rec.ATTRIBUTE10,
1276 p_ATTRIBUTE11 => l_tar_score_rec.ATTRIBUTE11,
1277 p_ATTRIBUTE12 => l_tar_score_rec.ATTRIBUTE12,
1278 p_ATTRIBUTE13 => l_tar_score_rec.ATTRIBUTE13,
1279 p_ATTRIBUTE14 => l_tar_score_rec.ATTRIBUTE14,
1280 p_ATTRIBUTE15 => l_tar_score_rec.ATTRIBUTE15
1281 );
1282 EXCEPTION
1283 WHEN NO_DATA_FOUND THEN
1284 AMS_Utility_PVT.error_message ('AMS_API_RECORD_NOT_FOUND');
1285 RAISE FND_API.G_EXC_ERROR;
1286 END;
1287
1288 x_object_version_number := p_score_rec.object_version_number + 1;
1289
1290 --
1291 -- End of API body.
1292 --
1293
1294 -- Standard check for p_commit
1295 IF FND_API.to_Boolean( p_commit ) THEN
1296 COMMIT WORK;
1297 END IF;
1298
1299
1300 -- Debug Message
1301 IF (AMS_DEBUG_HIGH_ON) THEN
1302
1303 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1304 END IF;
1305
1306 -- Standard call to get message count and if count is 1, get message info.
1307 FND_MSG_PUB.Count_And_Get
1308 (p_count => x_msg_count,
1309 p_data => x_msg_data
1310 );
1311
1312 -- If a Run process has been scheduled, then return the monitor_url in x_msg_data
1313 IF l_run_started = 'Y' THEN
1314 x_msg_data := l_monitor_url;
1315 END IF;
1316
1317 EXCEPTION
1318
1319 WHEN AMS_Utility_PVT.resource_locked THEN
1320 x_return_status := FND_API.g_ret_sts_error;
1321 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1322 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
1323 FND_MSG_PUB.add;
1324 END IF;
1325
1326 WHEN FND_API.G_EXC_ERROR THEN
1327 ROLLBACK TO UPDATE_Score_PVT;
1328 x_return_status := FND_API.G_RET_STS_ERROR;
1329 -- Standard call to get message count and if count=1, get the message
1330 FND_MSG_PUB.Count_And_Get (
1331 p_encoded => FND_API.G_FALSE,
1332 p_count => x_msg_count,
1333 p_data => x_msg_data
1334 );
1335
1336 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1337 ROLLBACK TO UPDATE_Score_PVT;
1338 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1339 -- Standard call to get message count and if count=1, get the message
1340 FND_MSG_PUB.Count_And_Get (
1341 p_encoded => FND_API.G_FALSE,
1342 p_count => x_msg_count,
1343 p_data => x_msg_data
1344 );
1345
1346 WHEN OTHERS THEN
1347 ROLLBACK TO UPDATE_Score_PVT;
1348 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1349 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1350 THEN
1351 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1352 END IF;
1353 -- Standard call to get message count and if count=1, get the message
1354 FND_MSG_PUB.Count_And_Get (
1355 p_encoded => FND_API.G_FALSE,
1356 p_count => x_msg_count,
1357 p_data => x_msg_data
1358 );
1359 End Update_Score;
1360
1361
1362 PROCEDURE Delete_Score(
1363 p_api_version IN NUMBER,
1364 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1365 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1366 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1367 x_return_status OUT NOCOPY VARCHAR2,
1368 x_msg_count OUT NOCOPY NUMBER,
1369 x_msg_data OUT NOCOPY VARCHAR2,
1370 p_score_id IN NUMBER,
1371 p_object_version_number IN NUMBER
1372 )
1373
1374 IS
1375
1376 CURSOR c_obj_version(c_id NUMBER) IS
1377 SELECT object_version_number
1378 FROM ams_dm_scores_all_b
1379 WHERE score_id = c_id;
1380
1381 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Score';
1382 l_api_version_number CONSTANT NUMBER := 1.0;
1383 l_object_version_number NUMBER;
1384
1385 BEGIN
1386 -- Standard Start of API savepoint
1387 SAVEPOINT DELETE_Score_PVT;
1388
1389 -- Standard call to check for call compatibility.
1390 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1391 p_api_version,
1392 l_api_name,
1393 G_PKG_NAME)
1394 THEN
1395 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1396 END IF;
1397
1398 -- Initialize message list if p_init_msg_list is set to TRUE.
1399 IF FND_API.to_Boolean( p_init_msg_list )
1400 THEN
1401 FND_MSG_PUB.initialize;
1402 END IF;
1403
1404 -- Debug Message
1405 IF (AMS_DEBUG_HIGH_ON) THEN
1406
1407 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' start');
1408 END IF;
1409
1410
1411 -- Initialize API return status to SUCCESS
1412 x_return_status := FND_API.G_RET_STS_SUCCESS;
1413
1414 IF (AMS_DEBUG_HIGH_ON) THEN
1415
1416
1417
1418 AMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
1419
1420 END IF;
1421
1422 Open c_obj_version(P_score_ID);
1423
1424 Fetch c_obj_version into l_object_version_number;
1425
1426 If ( c_obj_version%NOTFOUND) Then
1427 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1428 THEN
1429 FND_MESSAGE.Set_Name('AMS', 'API_MISSING_DELETE_TARGET');
1430 FND_MESSAGE.Set_Token ('INFO', 'dm_scores', FALSE);
1431 FND_MSG_PUB.Add;
1432 END IF;
1433 raise FND_API.G_EXC_ERROR;
1434 END IF;
1435 -- Debug Message
1436 IF (AMS_DEBUG_HIGH_ON) THEN
1437
1438 AMS_UTILITY_PVT.debug_message('Private API: - Close Cursor');
1439 END IF;
1440 Close c_obj_version;
1441
1442 --
1443 -- Api body
1444 --
1445 IF P_Object_Version_Number <> l_object_version_number THEN
1446 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1447 FND_MESSAGE.Set_Name('AMS', 'API_RECORD_CHANGED');
1448 FND_MESSAGE.Set_Token('INFO', 'dm_score', FALSE);
1449 FND_MSG_PUB.ADD;
1450 END IF;
1451 raise FND_API.G_EXC_ERROR;
1452 END IF;
1453
1454 IF (AMS_DEBUG_HIGH_ON) THEN
1455
1456
1457
1458 AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
1459
1460 END IF;
1461 -- Debug Message
1462 -- Invoke table handler(AMS_DM_scoreS_B_PKG.Delete_Row)
1463 AMS_DM_scoreS_B_PKG.Delete_Row(
1464 p_score_ID => p_score_id);
1465 --
1466 -- End of API body
1467 --
1468
1469 -- Standard check for p_commit
1470 IF FND_API.to_Boolean( p_commit )
1471 THEN
1472 COMMIT WORK;
1473 END IF;
1474
1475
1476 -- Debug Message
1477 IF (AMS_DEBUG_HIGH_ON) THEN
1478
1479 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' end');
1480 END IF;
1481
1482 -- Standard call to get message count and if count is 1, get message info.
1483 FND_MSG_PUB.Count_And_Get
1484 (p_count => x_msg_count,
1485 p_data => x_msg_data
1486 );
1487
1488 EXCEPTION
1489
1490 WHEN AMS_Utility_PVT.resource_locked THEN
1491 x_return_status := FND_API.g_ret_sts_error;
1492 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1493 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
1494 FND_MSG_PUB.add;
1495 END IF;
1496
1497 WHEN FND_API.G_EXC_ERROR THEN
1498 ROLLBACK TO DELETE_Score_PVT;
1499 x_return_status := FND_API.G_RET_STS_ERROR;
1500 -- Standard call to get message count and if count=1, get the message
1501 FND_MSG_PUB.Count_And_Get (
1502 p_encoded => FND_API.G_FALSE,
1503 p_count => x_msg_count,
1504 p_data => x_msg_data
1505 );
1506
1507 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1508 ROLLBACK TO DELETE_Score_PVT;
1509 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1510 -- Standard call to get message count and if count=1, get the message
1511 FND_MSG_PUB.Count_And_Get (
1512 p_encoded => FND_API.G_FALSE,
1513 p_count => x_msg_count,
1514 p_data => x_msg_data
1515 );
1516
1517 WHEN OTHERS THEN
1518 ROLLBACK TO DELETE_Score_PVT;
1519 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1520 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1521 THEN
1522 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1523 END IF;
1524 -- Standard call to get message count and if count=1, get the message
1525 FND_MSG_PUB.Count_And_Get (
1526 p_encoded => FND_API.G_FALSE,
1527 p_count => x_msg_count,
1528 p_data => x_msg_data
1529 );
1530 End Delete_Score;
1531
1532
1533 PROCEDURE check_score_uk_items(
1534 p_score_rec IN Score_Rec_Type,
1535 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1536 x_return_status OUT NOCOPY VARCHAR2)
1537 IS
1538
1539 l_valid_flag VARCHAR2(1);
1540
1541 CURSOR c_score_name
1542 IS SELECT 1 from dual
1543 WHERE EXISTS ( SELECT * from ams_dm_scores_vl
1544 WHERE UPPER(score_name) = UPPER(p_score_rec.score_name)) ;
1545 CURSOR c_score_name_updt
1546 IS SELECT 1 from dual
1547 WHERE EXISTS ( SELECT * from ams_dm_scores_vl
1548 WHERE UPPER(score_name) = UPPER(p_score_rec.score_name)
1549 AND score_id <> p_score_rec.score_id );
1550
1551 l_dummy NUMBER ;
1552
1553
1554
1555 BEGIN
1556 IF (AMS_DEBUG_HIGH_ON) THEN
1557
1558 AMS_UTILITY_PVT.debug_message('Private API:check_Score_uk_items');
1559 END IF;
1560
1561 x_return_status := FND_API.g_ret_sts_success;
1562 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1563 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
1564 'AMS_DM_SCORES_ALL_B',
1565 'SCORE_ID = ''' || p_score_rec.score_id ||''''
1566 );
1567 ELSE
1568 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
1569 'AMS_DM_scoreS_ALL_B',
1570 'score_ID = ''' || p_score_rec.score_id ||
1571 ''' AND score_ID <> ' || p_score_rec.score_id
1572 );
1573 END IF;
1574
1575 IF l_valid_flag = FND_API.g_false THEN
1576 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1577 THEN
1578 FND_MESSAGE.set_name('AMS', 'AMS_DM_SCORE_DUP_ID');
1579 FND_MSG_PUB.add;
1580 END IF;
1581 x_return_status := FND_API.g_ret_sts_error;
1582 RETURN;
1583 END IF;
1584
1585 --Validate unique score_name
1586 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1587 OPEN c_score_name ;
1588 FETCH c_score_name INTO l_dummy;
1589 CLOSE c_score_name ;
1590 ELSE
1591 OPEN c_score_name_updt ;
1592 FETCH c_score_name_updt INTO l_dummy;
1593 CLOSE c_score_name_updt ;
1594 END IF;
1595
1596 IF l_dummy IS NOT NULL THEN
1597 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1598 FND_MESSAGE.set_name('AMS', 'AMS_SCORE_DUPLICATE_NAME');
1599 FND_MSG_PUB.add;
1600 END IF;
1601 x_return_status := FND_API.g_ret_sts_error;
1602 RETURN;
1603 END IF;
1604
1605 END check_Score_uk_items;
1606
1607
1608 PROCEDURE check_Score_req_items(
1609 p_score_rec IN Score_Rec_Type,
1610 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1611 x_return_status OUT NOCOPY VARCHAR2
1612 )
1613 IS
1614 BEGIN
1615 IF (AMS_DEBUG_HIGH_ON) THEN
1616
1617 AMS_UTILITY_PVT.debug_message('Private API:check_Score_req_items');
1618 END IF;
1619 x_return_status := FND_API.g_ret_sts_success;
1620 IF (AMS_DEBUG_HIGH_ON) THEN
1621
1622 AMS_UTILITY_PVT.debug_message('Private API:mode ' || p_validation_mode);
1623 END IF;
1624
1625 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1626 IF (AMS_DEBUG_HIGH_ON) THEN
1627
1628 AMS_UTILITY_PVT.debug_message('Private API: id ' || p_score_rec.score_id);
1629 END IF;
1630 IF p_score_rec.model_id = FND_API.g_miss_num
1631 OR p_score_rec.model_id IS NULL THEN
1632 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1633 THEN
1634 FND_MESSAGE.set_name('AMS', 'AMS_DM_SCORE_NO_MOD_ID');
1635 FND_MSG_PUB.add;
1636 END IF;
1637 x_return_status := FND_API.g_ret_sts_error;
1638 END IF;
1639
1640
1641 IF p_score_rec.user_status_id = FND_API.g_miss_num
1642 OR p_score_rec.user_status_id IS NULL THEN
1643 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1644 THEN
1645 FND_MESSAGE.set_name('AMS', 'AMS_DM_SCORE_NO_USR_STAT_ID');
1646 FND_MSG_PUB.add;
1647 END IF;
1648 x_return_status := FND_API.g_ret_sts_error;
1649 END IF;
1650
1651 IF p_score_rec.owner_user_id = FND_API.g_miss_num
1652 OR p_score_rec.owner_user_id IS NULL THEN
1653 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1654 THEN
1655 FND_MESSAGE.set_name('AMS', 'AMS_DM_SCORE_NO_OWNER_USR_ID');
1656 FND_MSG_PUB.add;
1657 END IF;
1658 x_return_status := FND_API.g_ret_sts_error;
1659 END IF;
1660
1661 IF p_score_rec.score_name = FND_API.g_miss_char
1662 OR p_score_rec.score_name IS NULL THEN
1663 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1664 THEN
1665 FND_MESSAGE.set_name('AMS', 'AMS_DM_SCORE_NO_NAME');
1666 FND_MSG_PUB.add;
1667 END IF;
1668 x_return_status := FND_API.g_ret_sts_error;
1669 END IF;
1670
1671 ELSE -- update operation
1672 IF p_score_rec.score_id IS NULL THEN
1673 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1674 THEN
1675 FND_MESSAGE.set_name('AMS', 'AMS_DM_SCORE_NO_ID');
1676 FND_MSG_PUB.add;
1677 END IF;
1678 x_return_status := FND_API.g_ret_sts_error;
1679 END IF;
1680
1681 IF p_score_rec.object_version_number IS NULL OR p_score_rec.object_version_number = FND_API.G_MISS_NUM THEN
1682 AMS_Utility_PVT.error_message ('AMS_SCORE_NO_OBJECT_VERSION');
1683 x_return_status := FND_API.g_ret_sts_error;
1684 END IF;
1685
1686 IF p_score_rec.model_id IS NULL THEN
1687 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1688 THEN
1689 FND_MESSAGE.set_name('AMS', 'AMS_DM_SCORE_NO_MOD_ID');
1690 FND_MSG_PUB.add;
1691 END IF;
1692 x_return_status := FND_API.g_ret_sts_error;
1693 END IF;
1694
1695
1696 IF p_score_rec.user_status_id IS NULL THEN
1697 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1698 THEN
1699 FND_MESSAGE.set_name('AMS', 'AMS_DM_SCORE_NO_USR_STAT_ID');
1700 FND_MSG_PUB.add;
1701 END IF;
1702 x_return_status := FND_API.g_ret_sts_error;
1703 END IF;
1704
1705 IF p_score_rec.owner_user_id IS NULL THEN
1706 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1707 THEN
1708 FND_MESSAGE.set_name('AMS', 'AMS_DM_SCORE_NO_OWNER_USR_ID');
1709 FND_MSG_PUB.add;
1710 END IF;
1711 x_return_status := FND_API.g_ret_sts_error;
1712 END IF;
1713 END IF;
1714 END check_Score_req_items;
1715
1716 PROCEDURE check_score_fk_items(
1717 p_score_rec IN Score_Rec_Type,
1718 x_return_status OUT NOCOPY VARCHAR2
1719 )
1720 IS
1721 BEGIN
1722 IF (AMS_DEBUG_HIGH_ON) THEN
1723
1724 AMS_UTILITY_PVT.debug_message('Private API:check_Score_fk_items');
1725 END IF;
1726 x_return_status := FND_API.g_ret_sts_success;
1727
1728 --------------------model_id---------------------------
1729 IF p_score_rec.model_id <> FND_API.g_miss_num THEN
1730 IF AMS_Utility_PVT.check_fk_exists(
1731 'AMS_DM_MODELS_ALL_B',
1732 'MODEL_ID',
1733 p_score_rec.model_id
1734 ) = FND_API.g_false
1735 THEN
1736 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1737 THEN
1738 FND_MESSAGE.set_name('AMS', 'AMS_DM_SCORE_BAD_MOD_ID');
1739 FND_MSG_PUB.add;
1740 END IF;
1741 x_return_status := FND_API.g_ret_sts_error;
1742 END IF;
1743 END IF;
1744
1745 --------------------- user_status_id ------------------------
1746 IF p_score_rec.user_status_id <> FND_API.g_miss_num THEN
1747 IF AMS_Utility_PVT.check_fk_exists(
1748 'AMS_USER_STATUSES_B',
1749 'USER_STATUS_ID',
1750 p_score_rec.user_status_id
1751 ) = FND_API.g_false
1752 THEN
1753 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1754 THEN
1755 FND_MESSAGE.set_name('AMS', 'AMS_DM_SCORE_BAD_USR_STAT_ID');
1756 FND_MSG_PUB.add;
1757 END IF;
1758 x_return_status := FND_API.g_ret_sts_error;
1759 END IF;
1760 END IF;
1761 END check_Score_FK_items;
1762
1763 PROCEDURE check_dm_mdl_scr_lookup_items(
1764 p_score_rec IN Score_Rec_Type,
1765 x_return_status OUT NOCOPY VARCHAR2
1766 )
1767 IS
1768 BEGIN
1769 IF (AMS_DEBUG_HIGH_ON) THEN
1770
1771 AMS_UTILITY_PVT.debug_message('Private API:check_Score_lookup_items');
1772 END IF;
1773 x_return_status := FND_API.g_ret_sts_success;
1774
1775 ------------------ status_code ----------------------------------
1776 IF p_score_rec.status_code <> FND_API.g_miss_char THEN
1777 IF AMS_Utility_PVT.check_lookup_exists(
1778 p_lookup_type => G_STATUS_TYPE_SCORE,
1779 p_lookup_code => p_score_rec.status_code
1780 ) = FND_API.g_false
1781 THEN
1782 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1783 THEN
1784 FND_MESSAGE.set_name('AMS', 'AMS_DM_SCORE_BAD_STAT_CODE');
1785 FND_MSG_PUB.add;
1786 END IF;
1787
1788 x_return_status := FND_API.g_ret_sts_error;
1789 END IF;
1790 END IF;
1791
1792 END check_dm_mdl_scr_Lookup_items;
1793
1794 PROCEDURE Check_Score_Items (
1795 p_score_rec IN Score_Rec_Type,
1796 p_validation_mode IN VARCHAR2,
1797 x_return_status OUT NOCOPY VARCHAR2
1798 )
1799 IS
1800 BEGIN
1801
1802 -- Check Items Uniqueness API calls
1803
1804 check_Score_uk_items(
1805 p_score_rec => p_score_rec,
1806 p_validation_mode => p_validation_mode,
1807 x_return_status => x_return_status);
1808 IF x_return_status <> FND_API.g_ret_sts_success THEN
1809 RETURN;
1810 END IF;
1811
1812 -- Check Items Required/NOT NULL API calls
1813
1814 check_Score_req_items(
1815 p_score_rec => p_score_rec,
1816 p_validation_mode => p_validation_mode,
1817 x_return_status => x_return_status);
1818 IF x_return_status <> FND_API.g_ret_sts_success THEN
1819 RETURN;
1820 END IF;
1821 -- Check Items Foreign Keys API calls
1822
1823 check_Score_FK_items(
1824 p_score_rec => p_score_rec,
1825 x_return_status => x_return_status);
1826 IF x_return_status <> FND_API.g_ret_sts_success THEN
1827 RETURN;
1828 END IF;
1829
1830 -- Check Items Lookups
1831
1832 check_dm_mdl_scr_Lookup_items(
1833 p_score_rec => p_score_rec,
1834 x_return_status => x_return_status);
1835 IF x_return_status <> FND_API.g_ret_sts_success THEN
1836 RETURN;
1837 END IF;
1838
1839 -- Start add rosharma 28-Nov-2002
1840 -- Min records should be number and more than 0, if entered
1841 IF p_score_rec.min_records IS NOT NULL AND
1842 p_score_rec.min_records <> FND_API.g_miss_num THEN
1843 DECLARE
1844 l_min_rec NUMBER;
1845 BEGIN
1846 l_min_rec := TO_NUMBER (p_score_rec.min_records);
1847 IF l_min_rec < 0 THEN
1848 AMS_Utility_PVT.error_message ('AMS_DM_NEGATIVE_NUMBER' , 'FIELD' , 'MIN_RECORDS');
1849 x_return_status := FND_API.G_RET_STS_ERROR;
1850 RETURN;
1851 END IF;
1852 EXCEPTION
1853 WHEN VALUE_ERROR THEN
1854 AMS_Utility_PVT.error_message ('AMS_DM_NOT_NUMBER' , 'FIELD' , 'MIN_RECORDS');
1855 x_return_status := FND_API.G_RET_STS_ERROR;
1856 RETURN;
1857 END;
1858 END IF;
1859
1860 -- Max records should be number and more than 0, if entered
1861 IF p_score_rec.max_records IS NOT NULL AND
1862 p_score_rec.max_records <> FND_API.g_miss_num THEN
1863 DECLARE
1864 l_max_rec NUMBER;
1865 BEGIN
1866 l_max_rec := TO_NUMBER (p_score_rec.max_records);
1867 IF l_max_rec < 0 THEN
1868 AMS_Utility_PVT.error_message ('AMS_DM_NEGATIVE_NUMBER' , 'FIELD' , 'MAX_RECORDS');
1869 x_return_status := FND_API.G_RET_STS_ERROR;
1870 RETURN;
1871 END IF;
1872 EXCEPTION
1873 WHEN VALUE_ERROR THEN
1874 AMS_Utility_PVT.error_message ('AMS_DM_NOT_NUMBER' , 'FIELD' , 'MAX_RECORDS');
1875 x_return_status := FND_API.G_RET_STS_ERROR;
1876 RETURN;
1877 END;
1878 END IF;
1879 -- End add rosharma 28-Nov-2002
1880
1881 END Check_Score_Items;
1882
1883
1884 PROCEDURE Complete_Score_Rec (
1885 p_score_rec IN Score_Rec_Type,
1886 x_complete_rec OUT NOCOPY Score_Rec_Type
1887 )
1888 IS
1889 l_return_status VARCHAR2(1);
1890
1891 CURSOR c_complete IS
1892 SELECT *
1893 FROM ams_dm_scores_vl
1894 WHERE score_id = p_score_rec.score_id;
1895 l_score_rec c_complete%ROWTYPE;
1896 BEGIN
1897 x_complete_rec := p_score_rec;
1898
1899
1900 OPEN c_complete;
1901 FETCH c_complete INTO l_score_rec;
1902 CLOSE c_complete;
1903
1904 -- score_id
1905 IF p_score_rec.score_id = FND_API.g_miss_num THEN
1906 x_complete_rec.score_id := l_score_rec.score_id;
1907 END IF;
1908
1909 -- org_id
1910 IF p_score_rec.org_id = FND_API.g_miss_num THEN
1911 x_complete_rec.org_id := l_score_rec.org_id;
1912 END IF;
1913
1914 -- model_id
1915 IF p_score_rec.model_id = FND_API.g_miss_num THEN
1916 x_complete_rec.model_id := l_score_rec.model_id;
1917 END IF;
1918
1919 -- user_status_id
1920 IF p_score_rec.user_status_id = FND_API.g_miss_num THEN
1921 x_complete_rec.user_status_id := l_score_rec.user_status_id;
1922 END IF;
1923
1924 -- status_code
1925 IF p_score_rec.status_code = FND_API.g_miss_char THEN
1926 x_complete_rec.status_code := l_score_rec.status_code;
1927 END IF;
1928
1929 -- status_date
1930 IF p_score_rec.status_date = FND_API.g_miss_date THEN
1931 x_complete_rec.status_date := l_score_rec.status_date;
1932 END IF;
1933
1934 -- owner_user_id
1935 IF p_score_rec.owner_user_id = FND_API.g_miss_num THEN
1936 x_complete_rec.owner_user_id := l_score_rec.owner_user_id;
1937 END IF;
1938
1939 -- results_flag
1940 IF p_score_rec.results_flag = FND_API.g_miss_char THEN
1941 x_complete_rec.results_flag := l_score_rec.results_flag;
1942 END IF;
1943
1944 -- logs_flag
1945 IF p_score_rec.logs_flag = FND_API.g_miss_char THEN
1946 x_complete_rec.logs_flag := l_score_rec.logs_flag;
1947 END IF;
1948
1949 -- scheduled_date
1950 IF p_score_rec.scheduled_date = FND_API.g_miss_date THEN
1951 x_complete_rec.scheduled_date := l_score_rec.scheduled_date;
1952 END IF;
1953
1954 -- scheduled_timezone_id
1955 IF p_score_rec.scheduled_timezone_id = FND_API.g_miss_num THEN
1956 x_complete_rec.scheduled_timezone_id := l_score_rec.scheduled_timezone_id;
1957 END IF;
1958
1959 -- score_date
1960 IF p_score_rec.score_date = FND_API.g_miss_date THEN
1961 x_complete_rec.score_date := l_score_rec.score_date;
1962 END IF;
1963
1964 -- expiration_date
1965 IF p_score_rec.expiration_date = FND_API.g_miss_date THEN
1966 x_complete_rec.expiration_date := l_score_rec.expiration_date;
1967 END IF;
1968
1969 -- total_records
1970 IF p_score_rec.total_records = FND_API.g_miss_num THEN
1971 x_complete_rec.total_records := l_score_rec.total_records;
1972 END IF;
1973
1974 -- total_positives
1975 IF p_score_rec.total_positives = FND_API.g_miss_num THEN
1976 x_complete_rec.total_positives := l_score_rec.total_positives;
1977 END IF;
1978
1979 -- min_records
1980 IF p_score_rec.min_records = FND_API.g_miss_num THEN
1981 x_complete_rec.min_records := l_score_rec.min_records;
1982 END IF;
1983
1984 -- max_records
1985 IF p_score_rec.max_records = FND_API.g_miss_num THEN
1986 x_complete_rec.max_records := l_score_rec.max_records;
1987 END IF;
1988
1989 -- row_selection_type
1990 IF p_score_rec.row_selection_type = FND_API.g_miss_char THEN
1991 x_complete_rec.row_selection_type := l_score_rec.row_selection_type;
1992 END IF;
1993
1994 -- every_nth_row
1995 IF p_score_rec.every_nth_row = FND_API.g_miss_num THEN
1996 x_complete_rec.every_nth_row := l_score_rec.every_nth_row;
1997 END IF;
1998
1999 -- pct_random
2000 IF p_score_rec.pct_random = FND_API.g_miss_num THEN
2001 x_complete_rec.pct_random := l_score_rec.pct_random;
2002 END IF;
2003
2004 -- custom_setup_id
2005 IF p_score_rec.custom_setup_id = FND_API.g_miss_num THEN
2006 x_complete_rec.custom_setup_id := l_score_rec.custom_setup_id;
2007 END IF;
2008
2009 -- country_id
2010 IF p_score_rec.country_id = FND_API.g_miss_num THEN
2011 x_complete_rec.country_id := l_score_rec.country_id;
2012 END IF;
2013
2014 -- attribute_category
2015 IF p_score_rec.attribute_category = FND_API.g_miss_char THEN
2016 x_complete_rec.attribute_category := l_score_rec.attribute_category;
2017 END IF;
2018
2019 -- score_name
2020 IF p_score_rec.score_name = FND_API.g_miss_char THEN
2021 x_complete_rec.score_name := l_score_rec.score_name;
2022 END IF;
2023
2024 -- description
2025 IF p_score_rec.description = FND_API.g_miss_char THEN
2026 x_complete_rec.description := l_score_rec.description;
2027 END IF;
2028
2029 -- attribute1
2030 IF p_score_rec.attribute1 = FND_API.g_miss_char THEN
2031 x_complete_rec.attribute1 := l_score_rec.attribute1;
2032 END IF;
2033
2034 -- attribute2
2035 IF p_score_rec.attribute2 = FND_API.g_miss_char THEN
2036 x_complete_rec.attribute2 := l_score_rec.attribute2;
2037 END IF;
2038
2039 -- attribute3
2040 IF p_score_rec.attribute3 = FND_API.g_miss_char THEN
2041 x_complete_rec.attribute3 := l_score_rec.attribute3;
2042 END IF;
2043
2044 -- attribute4
2045 IF p_score_rec.attribute4 = FND_API.g_miss_char THEN
2046 x_complete_rec.attribute4 := l_score_rec.attribute4;
2047 END IF;
2048
2049 -- attribute5
2050 IF p_score_rec.attribute5 = FND_API.g_miss_char THEN
2051 x_complete_rec.attribute5 := l_score_rec.attribute5;
2052 END IF;
2053
2054 -- attribute6
2055 IF p_score_rec.attribute6 = FND_API.g_miss_char THEN
2056 x_complete_rec.attribute6 := l_score_rec.attribute6;
2057 END IF;
2058
2059 -- attribute7
2060 IF p_score_rec.attribute7 = FND_API.g_miss_char THEN
2061 x_complete_rec.attribute7 := l_score_rec.attribute7;
2062 END IF;
2063
2064 -- attribute8
2065 IF p_score_rec.attribute8 = FND_API.g_miss_char THEN
2066 x_complete_rec.attribute8 := l_score_rec.attribute8;
2067 END IF;
2068
2069 -- attribute9
2070 IF p_score_rec.attribute9 = FND_API.g_miss_char THEN
2071 x_complete_rec.attribute9 := l_score_rec.attribute9;
2072 END IF;
2073
2074 -- attribute10
2075 IF p_score_rec.attribute10 = FND_API.g_miss_char THEN
2076 x_complete_rec.attribute10 := l_score_rec.attribute10;
2077 END IF;
2078
2079 -- attribute11
2080 IF p_score_rec.attribute11 = FND_API.g_miss_char THEN
2081 x_complete_rec.attribute11 := l_score_rec.attribute11;
2082 END IF;
2083
2084 -- attribute12
2085 IF p_score_rec.attribute12 = FND_API.g_miss_char THEN
2086 x_complete_rec.attribute12 := l_score_rec.attribute12;
2087 END IF;
2088
2089 -- attribute13
2090 IF p_score_rec.attribute13 = FND_API.g_miss_char THEN
2091 x_complete_rec.attribute13 := l_score_rec.attribute13;
2092 END IF;
2093
2094 -- attribute14
2095 IF p_score_rec.attribute14 = FND_API.g_miss_char THEN
2096 x_complete_rec.attribute14 := l_score_rec.attribute14;
2097 END IF;
2098
2099 -- attribute15
2100 IF p_score_rec.attribute15 = FND_API.g_miss_char THEN
2101 x_complete_rec.attribute15 := l_score_rec.attribute15;
2102 END IF;
2103
2104 -- wf_itemkey
2105 IF p_score_rec.wf_itemkey = FND_API.g_miss_char THEN
2106 x_complete_rec.wf_itemkey := l_score_rec.wf_itemkey;
2107 END IF;
2108 END Complete_Score_Rec;
2109
2110 PROCEDURE Validate_Score (
2111 p_api_version IN NUMBER,
2112 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2113 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2114 p_validation_mode IN VARCHAR2,
2115 p_score_rec IN Score_Rec_Type,
2116 x_return_status OUT NOCOPY VARCHAR2,
2117 x_msg_count OUT NOCOPY NUMBER,
2118 x_msg_data OUT NOCOPY VARCHAR2
2119 )
2120 IS
2121 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Score';
2122 l_api_version_number CONSTANT NUMBER := 1.0;
2123 BEGIN
2124 -- Standard Start of API savepoint
2125 SAVEPOINT VALIDATE_Score_;
2126
2127 -- Debug Message
2128 IF (AMS_DEBUG_HIGH_ON) THEN
2129
2130 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' start');
2131 END IF;
2132
2133 -- Standard call to check for call compatibility.
2134 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2135 p_api_version,
2136 l_api_name,
2137 G_PKG_NAME)
2138 THEN
2139 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2140 END IF;
2141
2142 -- Initialize message list if p_init_msg_list is set to TRUE.
2143 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2144 FND_MSG_PUB.initialize;
2145 END IF;
2146
2147 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
2148 Check_Score_Items(
2149 p_score_rec => p_score_rec,
2150 p_validation_mode => p_validation_mode,
2151 x_return_status => x_return_status
2152 );
2153 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2154 RAISE FND_API.G_EXC_ERROR;
2155 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2156 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2157 END IF;
2158 END IF;
2159
2160 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
2161 Validate_Score_Rec(
2162 p_api_version => 1.0,
2163 p_init_msg_list => FND_API.G_FALSE,
2164 p_validation_mode => p_validation_mode,
2165 x_return_status => x_return_status,
2166 x_msg_count => x_msg_count,
2167 x_msg_data => x_msg_data,
2168 p_score_rec => p_score_rec
2169 );
2170 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2171 RAISE FND_API.G_EXC_ERROR;
2172 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2173 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2174 END IF;
2175 END IF;
2176
2177 -- Initialize API return status to SUCCESS
2178 x_return_status := FND_API.G_RET_STS_SUCCESS;
2179
2180
2181 -- Debug Message
2182 IF (AMS_DEBUG_HIGH_ON) THEN
2183
2184 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' end');
2185 END IF;
2186
2187 -- Standard call to get message count and if count is 1, get message info.
2188 FND_MSG_PUB.Count_And_Get
2189 (p_count => x_msg_count,
2190 p_data => x_msg_data
2191 );
2192 EXCEPTION
2193
2194 WHEN AMS_Utility_PVT.resource_locked THEN
2195 x_return_status := FND_API.g_ret_sts_error;
2196 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2197 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
2198 FND_MSG_PUB.add;
2199 END IF;
2200
2201 WHEN FND_API.G_EXC_ERROR THEN
2202 ROLLBACK TO VALIDATE_Score_;
2203 x_return_status := FND_API.G_RET_STS_ERROR;
2204 -- Standard call to get message count and if count=1, get the message
2205 FND_MSG_PUB.Count_And_Get (
2206 p_encoded => FND_API.G_FALSE,
2207 p_count => x_msg_count,
2208 p_data => x_msg_data
2209 );
2210
2211 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2212 ROLLBACK TO VALIDATE_Score_;
2213 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2214 -- Standard call to get message count and if count=1, get the message
2215 FND_MSG_PUB.Count_And_Get (
2216 p_encoded => FND_API.G_FALSE,
2217 p_count => x_msg_count,
2218 p_data => x_msg_data
2219 );
2220
2221 WHEN OTHERS THEN
2222 ROLLBACK TO VALIDATE_Score_;
2223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2224 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2225 THEN
2226 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2227 END IF;
2228 -- Standard call to get message count and if count=1, get the message
2229 FND_MSG_PUB.Count_And_Get (
2230 p_encoded => FND_API.G_FALSE,
2231 p_count => x_msg_count,
2232 p_data => x_msg_data
2233 );
2234 End Validate_Score;
2235
2236
2237 PROCEDURE Validate_Score_rec (
2238 p_api_version IN NUMBER,
2239 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2240 p_validation_mode IN VARCHAR2,
2241 x_return_status OUT NOCOPY VARCHAR2,
2242 x_msg_count OUT NOCOPY NUMBER,
2243 x_msg_data OUT NOCOPY VARCHAR2,
2244 p_Score_rec IN Score_Rec_Type
2245 )
2246 IS
2247 l_context_resource_id NUMBER;
2248 l_is_owner VARCHAR2(1);
2249
2250 -- add to select list as needed
2251 CURSOR c_reference (p_score_id IN NUMBER) IS
2252 SELECT owner_user_id
2253 FROM ams_dm_scores_all_b
2254 WHERE score_id = p_score_id;
2255 l_reference_rec c_reference%ROWTYPE;
2256 BEGIN
2257 -- Initialize message list if p_init_msg_list is set to TRUE.
2258 IF FND_API.to_Boolean( p_init_msg_list )
2259 THEN
2260 FND_MSG_PUB.initialize;
2261 END IF;
2262
2263 -- Initialize API return status to SUCCESS
2264 x_return_status := FND_API.G_RET_STS_SUCCESS;
2265
2266 -- Debug Message
2267 IF (AMS_DEBUG_HIGH_ON) THEN
2268
2269 AMS_UTILITY_PVT.debug_message('Private API: Validate_dm_model_rec');
2270 END IF;
2271
2272 IF p_validation_mode = JTF_PLSQL_API.g_update THEN
2273 l_context_resource_id := AMS_Utility_PVT.get_resource_id(FND_GLOBAL.user_id);
2274
2275 OPEN c_reference(p_score_rec.score_id);
2276 FETCH c_reference INTO l_reference_rec;
2277 CLOSE c_reference;
2278
2279 check_access (
2280 p_score_rec => p_score_rec,
2281 x_return_status => x_return_status
2282 );
2283
2284 -- the owner in the context needs to be the
2285 -- same as the owner of the record in order
2286 -- for owner to be changed
2287 IF l_reference_rec.owner_user_id <> p_score_rec.owner_user_id THEN
2288 l_is_owner := AMS_Access_PVT.check_owner (
2289 p_object_type => G_OBJECT_TYPE_SCORE,
2290 p_object_id => p_score_rec.score_id,
2291 p_user_or_role_type => 'USER',
2292 p_user_or_role_id => l_context_resource_id
2293 );
2294
2295 IF l_is_owner = 'N' AND NOT AMS_Access_PVT.Check_Admin_Access(l_context_resource_id) THEN
2296 AMS_Utility_PVT.error_message ('AMS_PRIC_UPDT_OWNER_PERM');
2297 x_return_status := FND_API.G_RET_STS_ERROR;
2298 END IF;
2299 END IF;
2300 END IF;
2301
2302 -- Start add rosharma 28-Nov-2002
2303 -- Max records must be more than Min records, if both are entered
2304 IF p_score_rec.min_records IS NOT NULL AND
2305 p_score_rec.min_records <> FND_API.g_miss_num AND
2306 p_score_rec.max_records IS NOT NULL AND
2307 p_score_rec.max_records <> FND_API.g_miss_num THEN
2308 DECLARE
2309 l_min_rec NUMBER;
2310 l_max_rec NUMBER;
2311 BEGIN
2312 l_min_rec := TO_NUMBER (p_score_rec.min_records);
2313 l_max_rec := TO_NUMBER (p_score_rec.max_records);
2314 IF l_max_rec < l_min_rec THEN
2315 AMS_Utility_PVT.error_message ('AMS_DM_MIN_MORE_THAN_MAX');
2316 x_return_status := FND_API.G_RET_STS_ERROR;
2317 RETURN;
2318 END IF;
2319 END;
2320 END IF;
2321
2322 -- If selection is every Nth row, there must be a value in number of rows
2323 -- and it must be greater than 0
2324 IF p_score_rec.row_selection_type = 'NTH_RECORD' THEN
2325 IF p_score_rec.every_nth_row IS NULL OR
2326 p_score_rec.every_nth_row = FND_API.g_miss_num THEN
2327 AMS_Utility_PVT.error_message ('AMS_DM_NO_NTH_RECORD');
2328 x_return_status := FND_API.G_RET_STS_ERROR;
2329 RETURN;
2330 END IF;
2331 --check for valid number
2332 DECLARE
2333 l_nth_row NUMBER;
2334 BEGIN
2335 l_nth_row := ROUND(TO_NUMBER (p_score_rec.every_nth_row));
2336 IF l_nth_row <= 0 THEN
2337 AMS_Utility_PVT.error_message ('AMS_DM_INVALID_NTH_ROW');
2338 x_return_status := FND_API.G_RET_STS_ERROR;
2339 RETURN;
2340 END IF;
2341 EXCEPTION
2342 WHEN VALUE_ERROR THEN
2343 AMS_Utility_PVT.error_message ('AMS_DM_NOT_NUMBER' , 'FIELD' , 'EVERY_NTH_ROW');
2344 x_return_status := FND_API.G_RET_STS_ERROR;
2345 RETURN;
2346 END;
2347 END IF;
2348
2349 -- If selection is random, there must be a value in random percentage
2350 -- and it must be between 0 and 100
2351 IF p_score_rec.row_selection_type = 'RANDOM' THEN
2352 IF p_score_rec.pct_random IS NULL OR
2353 p_score_rec.pct_random = FND_API.g_miss_num THEN
2354 AMS_Utility_PVT.error_message ('AMS_DM_NO_PCT_RANDOM');
2355 x_return_status := FND_API.G_RET_STS_ERROR;
2356 RETURN;
2357 END IF;
2358 --check for valid number
2359 DECLARE
2360 l_pct_random NUMBER;
2361 BEGIN
2362 l_pct_random := TO_NUMBER (p_score_rec.pct_random);
2363 IF l_pct_random <= 0 OR l_pct_random > 100 THEN
2364 AMS_Utility_PVT.error_message ('AMS_DM_INVALID_PCT_RANDOM');
2365 x_return_status := FND_API.G_RET_STS_ERROR;
2366 RETURN;
2367 END IF;
2368 EXCEPTION
2369 WHEN VALUE_ERROR THEN
2370 AMS_Utility_PVT.error_message ('AMS_DM_NOT_NUMBER' , 'FIELD' , 'PCT_RANDOM');
2371 x_return_status := FND_API.G_RET_STS_ERROR;
2372 RETURN;
2373 END;
2374 END IF;
2375 -- End add rosharma 28-Nov-2002
2376
2377 -- Standard call to get message count and if count is 1, get message info.
2378 FND_MSG_PUB.Count_And_Get
2379 (p_count => x_msg_count,
2380 p_data => x_msg_data
2381 );
2382 END Validate_Score_Rec;
2383
2384 PROCEDURE check_access (
2385 p_score_rec IN score_rec_type,
2386 x_return_status OUT NOCOPY VARCHAR2
2387 )
2388 IS
2389 L_ACCESS_TYPE_USER CONSTANT VARCHAR2(30) := 'USER';
2390 BEGIN
2391 -- Initialize API return status to SUCCESS
2392 x_return_status := FND_API.G_RET_STS_SUCCESS;
2393
2394 IF (AMS_DEBUG_HIGH_ON) THEN
2395
2396
2397
2398 ams_utility_pvt.debug_message ('score id: ' || p_score_rec.score_id || ' owner: ' || fnd_global.user_id);
2399
2400 END IF;
2401 -- validate access privileges
2402 IF AMS_Access_PVT.check_update_access (
2403 p_object_id => p_score_rec.score_id,
2404 p_object_type => G_OBJECT_TYPE_SCORE,
2405 p_user_or_role_id => AMS_Utility_PVT.get_resource_id (FND_GLOBAL.user_id),
2406 p_user_or_role_type => L_ACCESS_TYPE_USER) = 'N' THEN
2407 AMS_Utility_PVT.error_message ('AMS_SCOR_NO_UPDATE_ACCESS');
2408 x_return_status := FND_API.G_RET_STS_ERROR;
2409 END IF;
2410 END check_access;
2411
2412
2413 PROCEDURE wf_revert (
2414 p_score_id IN NUMBER,
2415 p_status_code IN VARCHAR2,
2416 x_return_status OUT NOCOPY VARCHAR2
2417 )
2418 IS
2419 l_user_status_id NUMBER;
2420 l_model_status_id NUMBER;
2421
2422 CURSOR c_score (p_score_id IN NUMBER) IS
2423 SELECT *
2424 FROM ams_dm_scores_all_b
2425 WHERE score_id = p_score_id
2426 ;
2427 l_score_rec c_score%ROWTYPE;
2428 BEGIN
2429 -- Initialize API return status to SUCCESS
2430 x_return_status := FND_API.G_RET_STS_SUCCESS;
2431
2432 OPEN c_score (p_score_id);
2433 FETCH c_score INTO l_score_rec;
2434 CLOSE c_score;
2435
2436 -- set the model that it used for scoring
2437 -- back to AVAILABLE -- the model was changed
2438 -- to scoring when scoring began
2439 --
2440 -- first check that the model isn't used by other
2441 -- scoring instances for scoring.
2442 IF model_used_for_scoring (l_score_rec.model_id, p_score_id) = FND_API.G_FALSE THEN
2443 OPEN c_user_status_id (G_STATUS_TYPE_MODEL, G_STATUS_AVAILABLE);
2444 FETCH c_user_status_id INTO l_model_status_id;
2445 CLOSE c_user_status_id;
2446
2447 UPDATE ams_dm_models_all_b
2448 SET last_update_date = SYSDATE
2449 , last_updated_by = FND_GLOBAL.user_id
2450 , last_update_login = FND_GLOBAL.conc_login_id
2451 , object_version_number = object_version_number + 1
2452 , status_code = G_STATUS_AVAILABLE
2453 , user_status_id = l_model_status_id
2454 , status_date = SYSDATE
2455 WHERE model_id = l_score_rec.model_id;
2456 END IF;
2457
2458 -- set the scoring run status to p_status_code (Available or Failed)
2459 OPEN c_user_status_id (G_STATUS_TYPE_SCORE, p_status_code);
2460 FETCH c_user_status_id INTO l_user_status_id;
2461 CLOSE c_user_status_id;
2462
2463 UPDATE ams_dm_scores_all_b
2464 SET last_update_date = SYSDATE
2465 , last_updated_by = FND_GLOBAL.user_id
2466 , last_update_login = FND_GLOBAL.conc_login_id
2467 , object_version_number = object_version_number + 1
2468 , status_code = p_status_code
2469 , user_status_id = l_user_status_id
2470 , status_date = SYSDATE
2471 WHERE score_id = p_score_id;
2472 END wf_revert;
2473
2474
2475 PROCEDURE process_score_success (
2476 p_score_id IN NUMBER,
2477 p_status_code IN VARCHAR2,
2478 x_return_status OUT NOCOPY VARCHAR2
2479 )
2480 IS
2481 l_user_status_id NUMBER;
2482 l_model_status_id NUMBER;
2483
2484 CURSOR c_score (p_score_id IN NUMBER) IS
2485 SELECT *
2486 FROM ams_dm_scores_all_b
2487 WHERE score_id = p_score_id
2488 ;
2489 l_score_rec c_score%ROWTYPE;
2490 BEGIN
2491 -- Initialize API return status to SUCCESS
2492 x_return_status := FND_API.G_RET_STS_SUCCESS;
2493
2494 OPEN c_score (p_score_id);
2495 FETCH c_score INTO l_score_rec;
2496 CLOSE c_score;
2497
2498 -- set the model that it used for scoring
2499 -- back to AVAILABLE -- the model was changed
2500 -- to scoring when scoring began
2501 --
2502 -- first check that the model isn't used by other
2503 -- scoring instances for scoring.
2504 IF model_used_for_scoring (l_score_rec.model_id, p_score_id) = FND_API.G_FALSE THEN
2505 OPEN c_user_status_id (G_STATUS_TYPE_MODEL, G_STATUS_AVAILABLE);
2506 FETCH c_user_status_id INTO l_model_status_id;
2507 CLOSE c_user_status_id;
2508
2509 UPDATE ams_dm_models_all_b
2510 SET last_update_date = SYSDATE
2511 , last_updated_by = FND_GLOBAL.user_id
2512 , last_update_login = FND_GLOBAL.conc_login_id
2513 , object_version_number = object_version_number + 1
2514 , status_code = G_STATUS_AVAILABLE
2515 , user_status_id = l_model_status_id
2516 , status_date = SYSDATE
2517 WHERE model_id = l_score_rec.model_id;
2518 END IF;
2519
2520 -- make the scoring run AVAILABLE for list creation.
2521 OPEN c_user_status_id (G_STATUS_TYPE_SCORE, p_status_code);
2522 FETCH c_user_status_id INTO l_user_status_id;
2523 CLOSE c_user_status_id;
2524
2525 UPDATE ams_dm_scores_all_b
2526 SET object_version_number = object_version_number + 1
2527 , last_update_date = SYSDATE
2528 , last_updated_by = FND_GLOBAL.user_id
2529 , status_date = SYSDATE
2530 , status_code = p_status_code
2531 , user_status_id = l_user_status_id
2532 , score_date = SYSDATE
2533 , results_flag = 'Y'
2534 WHERE score_id = p_score_id;
2535 END process_score_success;
2536
2537
2538 -- History
2539 -- 04-Oct-2002 nyostos Created.
2540 -- Overloaded procedure. New implementation in 11.5.9 to start
2541 -- the Build/Score/Preview Workflow process to handle Preview instead of
2542 -- starting the AMS_DM_PREVIEW concurrent program.
2543
2544 PROCEDURE handle_preview_request (
2545 p_score_id IN NUMBER,
2546 x_monitor_url OUT NOCOPY VARCHAR2,
2547 x_return_status OUT NOCOPY VARCHAR2
2548 )
2549 IS
2550 L_SCORE_QUALIFIER CONSTANT VARCHAR2(30) := 'SCOR';
2551 L_ACCESS_TYPE_USER CONSTANT VARCHAR2(30) := 'USER';
2552 l_owner_user_id NUMBER := AMS_Utility_PVT.get_resource_id (FND_GLOBAL.user_id);
2553
2554 l_proceedWithPreviewFlag VARCHAR2(1);
2555 l_data_exists_flag VARCHAR2(1);
2556 l_target_id NUMBER;
2557 l_is_enabled BOOLEAN;
2558
2559 CURSOR c_ref_score (p_score_id IN NUMBER) IS
2560 SELECT *
2561 FROM ams_dm_scores_vl
2562 WHERE score_id = p_score_id
2563 ;
2564
2565 CURSOR c_target (p_score_id IN NUMBER) IS
2566 SELECT m.target_id from ams_dm_models_all_b m,ams_dm_scores_all_b s
2567 WHERE m.model_id = s.model_id
2568 AND s.score_id = p_score_id
2569 ;
2570
2571 l_ref_score_rec c_ref_score%ROWTYPE;
2572
2573 l_tar_score_rec ams_dm_score_pvt.score_rec_type;
2574
2575 L_API_NAME CONSTANT VARCHAR2(30) := 'handle_preview_request';
2576
2577 BEGIN
2578 -- Debug Message
2579 IF (AMS_DEBUG_HIGH_ON) THEN
2580
2581 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' Start');
2582 END IF;
2583
2584 -- Initialize API return status to SUCCESS
2585 x_return_status := FND_API.G_RET_STS_SUCCESS;
2586
2587 -- Load the scoring run record to get the original status
2588 OPEN c_ref_score (p_score_id);
2589 FETCH c_ref_score INTO l_ref_score_rec;
2590 CLOSE c_ref_score;
2591
2592 --First check if the user has access to preview operation
2593 IF AMS_Access_PVT.check_update_access (
2594 p_object_id => p_score_id,
2595 p_object_type => L_SCORE_QUALIFIER,
2596 p_user_or_role_id => l_owner_user_id,
2597 p_user_or_role_type => L_ACCESS_TYPE_USER) = 'N' THEN
2598 x_return_status := 'A';
2599 return;
2600 END IF;
2601
2602 -- Check if the target is enabled
2603 OPEN c_target(p_score_id);
2604 FETCH c_target INTO l_target_id;
2605 CLOSE c_target;
2606
2607 AMS_DM_TARGET_PVT.is_target_enabled(
2608 p_target_id => l_target_id ,
2609 x_is_enabled => l_is_enabled
2610 );
2611 IF l_is_enabled = FALSE THEN
2612 IF (AMS_DEBUG_HIGH_ON) THEN
2613 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' Target is disabled, cannot preview');
2614 END IF;
2615 -- Set x_return_status to 'T' (for target disabled error). This will results in a different message
2616 -- displayed to the user
2617 x_return_status := 'T';
2618 RETURN;
2619 END IF;
2620
2621 -- Check if the Preview operation can be started
2622 proceedWithPreview (p_score_id, l_proceedWithPreviewFlag);
2623 IF l_proceedWithPreviewFlag = 'N' THEN
2624 IF (AMS_DEBUG_HIGH_ON) THEN
2625
2626 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' Cannot Proceed with Preview');
2627 END IF;
2628 -- Set x_return_status to expected error. This will results in a different message
2629 -- displayed to the user
2630 x_return_status := FND_API.G_RET_STS_ERROR;
2631 RETURN;
2632 END IF;
2633
2634 dataToPreview (p_score_id, l_data_exists_flag);
2635 IF l_data_exists_flag = 'N' THEN
2636 IF (AMS_DEBUG_HIGH_ON) THEN
2637
2638 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' No Data Selections to Preview');
2639 END IF;
2640 -- Set x_return_status to 'D' (for data error). This will results in a different message
2641 -- displayed to the user
2642 x_return_status := 'D';
2643 RETURN;
2644 END IF;
2645
2646 wf_startPreviewProcess (
2647 p_score_id => p_score_id,
2648 p_orig_status_id => l_ref_score_rec.user_status_id,
2649 x_tar_score_rec => l_tar_score_rec,
2650 x_return_status => x_return_status
2651 );
2652
2653 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2654 IF (AMS_DEBUG_HIGH_ON) THEN
2655
2656 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' Error from wf_startPreviewProcess');
2657 END IF;
2658 -- Set x_return_status to unexpected error. This will results in a different message
2659 -- displayed to the user
2660 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2661 RAISE FND_API.G_EXC_ERROR;
2662 END IF;
2663
2664 -- Construct the URL that could be used to monitor the WF process. This will be returned to the caller
2665 -- to be displayed on a custom confirmation message.
2666 x_monitor_url := wf_monitor.geturl(wf_core.TRANSLATE('WF_WEB_AGENT'), AMS_WFMOD_PVT.G_DEFAULT_ITEMTYPE, l_tar_score_rec.wf_itemkey, 'NO');
2667
2668 -- Update the Scoring Run record with the new status code (PREVIEWING) and Id
2669 -- and also with the WF Item Key
2670 UPDATE ams_dm_scores_all_b
2671 SET logs_flag = 'Y',
2672 object_version_number = object_version_number + 1,
2673 last_update_date = SYSDATE,
2674 last_updated_by = FND_GLOBAL.user_id,
2675 status_date = SYSDATE,
2676 status_code = l_tar_score_rec.status_code,
2677 user_status_id = l_tar_score_rec.user_status_id,
2678 wf_itemkey = l_tar_score_rec.wf_itemkey
2679 WHERE score_id = p_score_id;
2680
2681 -- Debug Message
2682 IF (AMS_DEBUG_HIGH_ON) THEN
2683
2684 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' End');
2685 END IF;
2686
2687 END handle_preview_request;
2688
2689
2690
2691
2692 --
2693 -- History
2694 -- 11-Mar-2001 choang Created.
2695 -- 04-Oct-2002 nyostos This procedure has been deprecated in 11.5.9. It
2696 -- is left for backward compatibility. It calls the
2697 -- overloaded handle_preview_request which starts
2698 -- the build/score workflow process instead of starting
2699 -- the AMS_DM_PREVIEW Concurrent Program.
2700 PROCEDURE handle_preview_request (
2701 p_score_id IN NUMBER,
2702 x_request_id OUT NOCOPY NUMBER,
2703 x_return_status OUT NOCOPY VARCHAR2
2704 )
2705 IS
2706 l_monitor_url VARCHAR2(1);
2707
2708 BEGIN
2709
2710 handle_preview_request ( p_score_id,
2711 l_monitor_url,
2712 x_return_status);
2713
2714 x_request_id := 0;
2715 END handle_preview_request;
2716
2717
2718 --
2719 -- Note
2720 -- Copy is broken into 4 sections:
2721 -- - copy all required fields of the object
2722 -- - copy all fields passed in thru the UI, but
2723 -- use the value of the base object if the field
2724 -- isn't passed through the UI
2725 -- - copy all fields passed in thru the UI, but
2726 -- leave the field as null if it isn't passed in
2727 -- - copy all attributes passed in from the UI
2728 --
2729 -- History
2730 -- 07-Apr-2001 choang Created.
2731 -- 26-Apr-2001 sveerave Changed column names to be of application column names from db column names
2732 -- in get_column_value call-out.
2733
2734 PROCEDURE copy_score (
2735 p_api_version IN NUMBER,
2736 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2737 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2738 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2739 x_return_status OUT NOCOPY VARCHAR2,
2740 x_msg_count OUT NOCOPY NUMBER,
2741 x_msg_data OUT NOCOPY VARCHAR2,
2742 p_source_object_id IN NUMBER,
2743 p_attributes_table IN AMS_CpyUtility_PVT.copy_attributes_table_type,
2744 p_copy_columns_table IN AMS_CpyUtility_PVT.copy_columns_table_type,
2745 x_new_object_id OUT NOCOPY NUMBER,
2746 x_custom_setup_id OUT NOCOPY NUMBER
2747 )
2748 IS
2749 L_API_NAME CONSTANT VARCHAR2(30) := 'copy_score';
2750 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
2751
2752 l_new_score_id NUMBER;
2753 l_score_rec score_rec_type;
2754
2755 -- for non-standard out params in copy_act_access
2756 l_errnum NUMBER;
2757 l_errcode VARCHAR2(30);
2758 l_errmsg VARCHAR2(4000);
2759
2760 CURSOR c_score (p_score_id IN NUMBER) IS
2761 SELECT *
2762 FROM ams_dm_scores_vl
2763 WHERE score_id = p_score_id
2764 ;
2765 --start changes rosharma 20-aug-2003 bug 3104201
2766 CURSOR c_data_source (p_model_id IN NUMBER) IS
2767 SELECT t.DATA_SOURCE_ID , t.target_id
2768 FROM ams_dm_models_all_b m,ams_dm_targets_b t
2769 WHERE m.model_id = p_model_id
2770 AND m.target_id = t.target_id
2771 ;
2772
2773 l_ds_id NUMBER;
2774 --end changes rosharma 20-aug-2003 bug 3104201
2775 l_target_id NUMBER;
2776 l_reference_rec c_score%ROWTYPE;
2777 l_new_score_rec c_score%ROWTYPE;
2778 BEGIN
2779 -- Standard Start of API savepoint
2780 SAVEPOINT ams_score_pvt_copy_score;
2781
2782 -- Standard call to check for call compatibility.
2783 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2784 p_api_version,
2785 l_api_name,
2786 G_PKG_NAME)
2787 THEN
2788 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2789 END IF;
2790
2791 -- Initialize message list if p_init_msg_list is set to TRUE.
2792 IF FND_API.to_Boolean( p_init_msg_list )THEN
2793 FND_MSG_PUB.initialize;
2794 END IF;
2795
2796 IF (AMS_DEBUG_HIGH_ON) THEN
2797
2798
2799
2800 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' start');
2801
2802 END IF;
2803
2804 -- Initialize API return status to SUCCESS
2805 x_return_status := FND_API.G_RET_STS_SUCCESS;
2806
2807 --
2808 -- Start of API body.
2809 --
2810 -- Initialize the new score record
2811 -- use ams_cpyutility_pvt.get_column_value to fetch a value
2812 -- to replace the reference column value with a new value
2813 -- passed in from the UI through p_copy_columns_table.
2814 OPEN c_score (p_source_object_id);
2815 FETCH c_score INTO l_reference_rec;
2816 CLOSE c_score;
2817
2818 -- copy all required fields
2819 l_score_rec.status_code := 'DRAFT';
2820 OPEN c_user_status_id (G_STATUS_TYPE_SCORE, l_score_rec.status_code);
2821 FETCH c_user_status_id INTO l_score_rec.user_status_id;
2822 CLOSE c_user_status_id;
2823 l_score_rec.row_selection_type := l_reference_rec.row_selection_type;
2824
2825 --added rosharma 10-Feb-2003 for copying pct_random and nth_row correctly
2826 IF l_score_rec.row_selection_type = 'NTH_RECORD' THEN
2827 l_score_rec.every_nth_row := l_reference_rec.every_nth_row;
2828 END IF;
2829
2830 IF l_score_rec.row_selection_type = 'RANDOM' THEN
2831 l_score_rec.pct_random := l_reference_rec.pct_random;
2832 END IF;
2833 --end add rosharma 10-Feb-2003 for copying pct_random and nth_row correctly
2834 --added rosharma 04-sep-2003 bug # 3127555
2835 l_score_rec.min_records := l_reference_rec.min_records;
2836 l_score_rec.max_records := l_reference_rec.max_records;
2837 --end add rosharma 04-sep-2003 bug # 3127555
2838
2839 -- copy flex field data. fix for 4220828
2840 l_score_rec.attribute_category := l_reference_rec.attribute_category;
2841 l_score_rec.attribute1 := l_reference_rec.attribute1;
2842 l_score_rec.attribute2 := l_reference_rec.attribute2;
2843 l_score_rec.attribute3 := l_reference_rec.attribute3;
2844 l_score_rec.attribute4 := l_reference_rec.attribute4;
2845 l_score_rec.attribute5 := l_reference_rec.attribute5;
2846 l_score_rec.attribute6 := l_reference_rec.attribute6;
2847 l_score_rec.attribute7 := l_reference_rec.attribute7;
2848 l_score_rec.attribute8 := l_reference_rec.attribute8;
2849 l_score_rec.attribute9 := l_reference_rec.attribute9;
2850 l_score_rec.attribute10 := l_reference_rec.attribute10;
2851 l_score_rec.attribute11 := l_reference_rec.attribute11;
2852 l_score_rec.attribute12 := l_reference_rec.attribute12;
2853 l_score_rec.attribute13 := l_reference_rec.attribute13;
2854 l_score_rec.attribute14 := l_reference_rec.attribute14;
2855 l_score_rec.attribute15 := l_reference_rec.attribute15;
2856
2857 -- if field is not passed in from copy_columns_table
2858 -- copy from the base object
2859 -- owner_user_id
2860 AMS_CpyUtility_PVT.get_column_value ('owner_user_id', p_copy_columns_table, l_score_rec.owner_user_id);
2861 l_score_rec.owner_user_id := NVL (l_score_rec.owner_user_id, l_reference_rec.owner_user_id);
2862 -- model_id
2863 AMS_CpyUtility_PVT.get_column_value ('model_id', p_copy_columns_table, l_score_rec.model_id);
2864 l_score_rec.model_id := NVL (l_score_rec.model_id, l_reference_rec.model_id);
2865
2866 -- if field is not passed in from copy_columns_table
2867 -- don't copy
2868 AMS_CpyUtility_PVT.get_column_value ('newObjName', p_copy_columns_table, l_score_rec.score_name);
2869 AMS_CpyUtility_PVT.get_column_value ('description', p_copy_columns_table, l_score_rec.description);
2870 --AMS_CpyUtility_PVT.get_column_value ('mainRandNthRowSel', p_copy_columns_table, l_score_rec.every_nth_row);
2871 --AMS_CpyUtility_PVT.get_column_value ('mainRandPctRowSel', p_copy_columns_table, l_score_rec.pct_random);
2872 --commented rosharma 04-sep-2003 bug # 3127555
2873 --AMS_CpyUtility_PVT.get_column_value ('minRequested', p_copy_columns_table, l_score_rec.min_records);
2874 --AMS_CpyUtility_PVT.get_column_value ('maxRequested', p_copy_columns_table, l_score_rec.max_records);
2875 --end comment rosharma 04-sep-2003 bug # 3127555
2876
2877 AMS_DM_Score_PVT.create_score (
2878 p_api_version => 1.0,
2879 p_init_msg_list => FND_API.G_FALSE,
2880 p_commit => FND_API.G_FALSE,
2881 p_validation_level => p_validation_level,
2882 x_return_status => x_return_status,
2883 x_msg_count => x_msg_count,
2884 x_msg_data => x_msg_data,
2885 p_score_rec => l_score_rec,
2886 x_custom_setup_id => x_custom_setup_id,
2887 x_score_id => l_new_score_id
2888 );
2889 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2890 RAISE FND_API.G_EXC_ERROR;
2891 END IF;
2892
2893 -- copy data selections
2894
2895 -- 01-May-2003 nyostos Fixed copying of Data Selections Bug 2934000
2896 -- Used G_ATTRIBUTE_SELC instead of G_ATTRIBUTE_TRNG
2897 IF AMS_CpyUtility_PVT.is_copy_attribute (AMS_CopyElements_PVT.G_ATTRIBUTE_SELC, p_attributes_table) = FND_API.G_TRUE THEN
2898 --start changes rosharma 20-aug-2003 bug 3104201
2899 OPEN c_data_source (l_reference_rec.model_id);
2900 FETCH c_data_source INTO l_ds_id , l_target_id;
2901 CLOSE c_data_source;
2902 -- Fix for bug 4357993. Workbook has to be copied for Custom score also.
2903 AMS_CopyElements_PVT.copy_list_select_actions (
2904 p_api_version => 1.0,
2905 p_init_msg_list => FND_API.G_FALSE,
2906 p_commit => FND_API.G_FALSE,
2907 x_return_status => x_return_status,
2908 x_msg_count => x_msg_count,
2909 x_msg_data => x_msg_data,
2910 p_object_type => G_OBJECT_TYPE_SCORE,
2911 p_src_object_id => p_source_object_id,
2912 p_tar_object_id => l_new_score_id
2913 );
2914
2915 IF l_target_id >= G_SEEDED_ID_THRESHOLD THEN
2916 AMS_Adv_Filter_PVT.copy_filter_data (
2917 p_api_version => 1.0,
2918 p_init_msg_list => FND_API.G_FALSE,
2919 p_commit => FND_API.G_FALSE,
2920 p_validation_level => p_validation_level,
2921 p_objType => G_OBJECT_TYPE_SCORE,
2922 p_old_objectId => p_source_object_id,
2923 p_new_objectId => l_new_score_id,
2924 p_dataSourceId => l_ds_id,
2925 x_return_status => x_return_status,
2926 x_msg_count => x_msg_count,
2927 x_msg_data => x_msg_data
2928 );
2929 --end changes rosharma 20-aug-2003 bug 3104201
2930 END IF;
2931
2932 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2933 RAISE FND_API.G_EXC_ERROR;
2934 END IF;
2935 END IF;
2936
2937 -- copy team
2938 IF AMS_CpyUtility_PVT.is_copy_attribute (AMS_CopyElements_PVT.G_ATTRIBUTE_TEAM, p_attributes_table) = FND_API.G_TRUE THEN
2939 AMS_CopyElements_PVT.copy_act_access (
2940 p_src_act_type => G_OBJECT_TYPE_SCORE,
2941 p_new_act_type => G_OBJECT_TYPE_SCORE,
2942 p_src_act_id => p_source_object_id,
2943 p_new_act_id => l_new_score_id,
2944 p_errnum => l_errnum,
2945 p_errcode => l_errcode,
2946 p_errmsg => l_errmsg
2947 );
2948 END IF;
2949
2950 -- currently, only needed to fetch custom_setup_id
2951 -- but can be used to return other values later.
2952 OPEN c_score (l_new_score_id);
2953 FETCH c_score INTO l_new_score_rec;
2954 CLOSE c_score;
2955
2956 x_new_object_id := l_new_score_id;
2957 -- x_custom_setup_id := l_new_score_rec.custom_setup_id;
2958 --
2959 -- End of API body.
2960 --
2961
2962 -- Standard check for p_commit
2963 IF FND_API.to_Boolean( p_commit ) THEN
2964 COMMIT WORK;
2965 END IF;
2966
2967
2968 -- Debug Message
2969 IF (AMS_DEBUG_HIGH_ON) THEN
2970
2971 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' end');
2972 END IF;
2973
2974 -- Standard call to get message count and if count is 1, get message info.
2975 FND_MSG_PUB.Count_And_Get (
2976 p_count => x_msg_count,
2977 p_data => x_msg_data
2978 );
2979 EXCEPTION
2980 WHEN FND_API.G_EXC_ERROR THEN
2981 ROLLBACK TO ams_score_pvt_copy_score;
2982 x_return_status := FND_API.G_RET_STS_ERROR;
2983 -- Standard call to get message count and if count=1, get the message
2984 FND_MSG_PUB.Count_And_Get (
2985 p_encoded => FND_API.G_FALSE,
2986 p_count => x_msg_count,
2987 p_data => x_msg_data
2988 );
2989 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2990 ROLLBACK TO ams_score_pvt_copy_score;
2991 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2992 -- Standard call to get message count and if count=1, get the message
2993 FND_MSG_PUB.Count_And_Get (
2994 p_encoded => FND_API.G_FALSE,
2995 p_count => x_msg_count,
2996 p_data => x_msg_data
2997 );
2998 WHEN OTHERS THEN
2999 ROLLBACK TO ams_score_pvt_copy_score;
3000 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3001 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3002 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3003 END IF;
3004 -- Standard call to get message count and if count=1, get the message
3005 FND_MSG_PUB.Count_And_Get (
3006 p_encoded => FND_API.G_FALSE,
3007 p_count => x_msg_count,
3008 p_data => x_msg_data
3009 );
3010 END copy_score;
3011
3012
3013 PROCEDURE wf_score (
3014 p_score_id IN NUMBER,
3015 x_status_code OUT NOCOPY VARCHAR2,
3016 x_return_status OUT NOCOPY VARCHAR2
3017 )
3018 IS
3019 l_user_status_id NUMBER;
3020 l_model_status_id NUMBER;
3021
3022 CURSOR c_model (p_model_id IN NUMBER) IS
3023 SELECT *
3024 FROM ams_dm_models_all_b
3025 WHERE model_id = p_model_id;
3026 l_model_rec c_model%ROWTYPE;
3027
3028 CURSOR c_score (p_score_id IN NUMBER) IS
3029 SELECT *
3030 FROM ams_dm_scores_all_b
3031 WHERE score_id = p_score_id;
3032 l_score_rec c_score%ROWTYPE;
3033 BEGIN
3034 -- Initialize API return status to SUCCESS
3035 x_return_status := FND_API.G_RET_STS_SUCCESS;
3036
3037 OPEN c_score (p_score_id);
3038 FETCH c_score INTO l_score_rec;
3039 CLOSE c_score;
3040
3041 OPEN c_model (l_score_rec.model_id);
3042 FETCH c_model INTO l_model_rec;
3043 CLOSE c_model;
3044
3045 -- if the model is not AVAILABLE or being used in SCORING, then
3046 -- it cannot be used for scoring.
3047 IF l_model_rec.status_code NOT IN (G_STATUS_AVAILABLE, G_STATUS_SCORING) THEN
3048 x_status_code := G_DEFAULT_STATUS; -- draft
3049 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3050 RETURN;
3051 END IF;
3052 x_status_code := G_STATUS_SCORING;
3053
3054 -- When the Scoring Run status is SCORING, then we cleanup previous
3055 -- Scoring Run results.
3056 cleanupPreviousScoreData(p_score_id);
3057
3058 -- update the model to indicate that it is
3059 -- being used to score a data set
3060 OPEN c_user_status_id (G_STATUS_TYPE_MODEL, G_STATUS_SCORING);
3061 FETCH c_user_status_id INTO l_model_status_id;
3062 CLOSE c_user_status_id;
3063
3064 UPDATE ams_dm_models_all_b
3065 SET last_update_date = SYSDATE
3066 , last_updated_by = FND_GLOBAL.user_id
3067 , last_update_login = FND_GLOBAL.conc_login_id
3068 , object_version_number = object_version_number + 1
3069 , status_code = G_STATUS_SCORING
3070 , user_status_id = l_model_status_id
3071 , status_date = SYSDATE
3072 WHERE model_id = l_model_rec.model_id;
3073
3074 -- set the scoring run status to scoring
3075 OPEN c_user_status_id (G_STATUS_TYPE_SCORE, G_STATUS_SCORING);
3076 FETCH c_user_status_id INTO l_user_status_id;
3077 CLOSE c_user_status_id;
3078
3079 UPDATE ams_dm_scores_all_b
3080 SET last_update_date = SYSDATE
3081 , last_updated_by = FND_GLOBAL.user_id
3082 , last_update_login = FND_GLOBAL.conc_login_id
3083 , object_version_number = object_version_number + 1
3084 , status_code = G_STATUS_SCORING
3085 , user_status_id = l_user_status_id
3086 , status_date = SYSDATE
3087 WHERE score_id = p_score_id;
3088 END wf_score;
3089
3090 --
3091 -- Purpose
3092 -- Indicates whether a model is being used for
3093 -- scoring.
3094 -- History
3095 -- 12-Apr-2001 choang Created.
3096 FUNCTION model_used_for_scoring (
3097 p_model_id IN NUMBER,
3098 p_current_score_id IN NUMBER
3099 ) RETURN VARCHAR2
3100 IS
3101 l_dummy NUMBER;
3102
3103 CURSOR c_scoring_model IS
3104 SELECT 1
3105 FROM dual
3106 WHERE EXISTS (SELECT 1
3107 FROM ams_dm_scores_all_b
3108 WHERE model_id = p_model_id
3109 AND status_code = G_STATUS_SCORING
3110 AND score_id <> p_current_score_id)
3111 ;
3112 BEGIN
3113 OPEN c_scoring_model;
3114 FETCH c_scoring_model INTO l_dummy;
3115 CLOSE c_scoring_model;
3116
3117 IF l_dummy = 1 THEN
3118 RETURN FND_API.G_TRUE;
3119 END IF;
3120
3121 RETURN FND_API.G_FALSE;
3122 END model_used_for_scoring;
3123
3124
3125 PROCEDURE wf_startprocess (
3126 p_score_id IN NUMBER,
3127 p_scheduled_date IN DATE,
3128 p_scheduled_timezone_id IN NUMBER,
3129 p_orig_status_id IN NUMBER,
3130 x_tar_score_rec IN OUT NOCOPY score_rec_type,
3131 x_return_status OUT NOCOPY VARCHAR2
3132 )
3133 IS
3134 -- used with get_user_timezone api
3135 l_user_timezone_name VARCHAR2(80);
3136 BEGIN
3137 IF p_scheduled_timezone_id IS NULL THEN
3138 AMS_Utility_PVT.get_user_timezone (
3139 x_return_status => x_return_status,
3140 x_user_time_id => x_tar_score_rec.scheduled_timezone_id,
3141 x_user_time_name => l_user_timezone_name
3142 );
3143 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3144 RAISE FND_API.G_EXC_ERROR;
3145 END IF;
3146 ELSE
3147 x_tar_score_rec.scheduled_timezone_id := p_scheduled_timezone_id;
3148 END IF;
3149
3150 x_tar_score_rec.status_code := G_STATUS_SCHEDULED;
3151 OPEN c_user_status_id (G_STATUS_TYPE_SCORE, x_tar_score_rec.status_code);
3152 FETCH c_user_status_id INTO x_tar_score_rec.user_status_id;
3153 CLOSE c_user_status_id;
3154
3155 -- Initiate Workflow process and grab the itemkey
3156 AMS_WFMOD_PVT.StartProcess(
3157 p_object_id => p_score_id,
3158 p_object_type => G_OBJECT_TYPE_SCORE,
3159 p_user_status_id => p_orig_status_id,
3160 p_scheduled_timezone_id => x_tar_score_rec.scheduled_timezone_id,
3161 p_scheduled_date => p_scheduled_date,
3162 p_request_type => NULL,
3163 p_select_list => NULL,
3164 x_itemkey => x_tar_score_rec.wf_itemkey
3165 );
3166
3167 IF x_tar_score_rec.wf_itemkey IS NULL THEN
3168 x_return_status := FND_API.g_ret_sts_error;
3169 END IF;
3170
3171 END wf_startprocess;
3172
3173
3174 -- History
3175 -- 04-Oct-2002 nyostos Created.
3176 -- Cancels the Score workflow process. If the Scoring Run is in SCHEDULED.
3177 -- state (i.e. the first step in the WF process has not started yet),
3178 -- the Scoring Run status will be reverted to its previous status.
3179 -- If the Scoring status is SCORING (i.e. the Workflow process is in progress),
3180 -- then the Scoring Run status will be set to DRAFT.
3181
3182 PROCEDURE cancel_run_request (
3183 p_score_id IN NUMBER,
3184 x_return_status OUT NOCOPY VARCHAR2
3185 )
3186 IS
3187
3188 CURSOR c_ref_score (p_score_id IN NUMBER) IS
3189 SELECT *
3190 FROM ams_dm_scores_vl
3191 WHERE score_id = p_score_id
3192 ;
3193 l_ref_score_rec c_ref_score%ROWTYPE;
3194
3195 L_API_NAME CONSTANT VARCHAR2(30) := 'cancel_run_request';
3196
3197 l_original_status_id VARCHAR2(30);
3198 l_status_code VARCHAR2(30);
3199 l_user_status_id NUMBER;
3200 l_model_status_id NUMBER;
3201 l_return_status VARCHAR2(1);
3202
3203 BEGIN
3204 -- Debug Message
3205 IF (AMS_DEBUG_HIGH_ON) THEN
3206
3207 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' Start');
3208 END IF;
3209
3210 AMS_Utility_PVT.create_log (
3211 x_return_status => l_return_status,
3212 p_arc_log_used_by => G_OBJECT_TYPE_SCORE,
3213 p_log_used_by_id => p_score_id,
3214 p_msg_data => L_API_NAME || ': Begin'
3215 );
3216
3217 -- Initialize API return status to SUCCESS
3218 x_return_status := FND_API.G_RET_STS_SUCCESS;
3219
3220 -- Load the score record to get the original status and wf_itemkey
3221 OPEN c_ref_score (p_score_id);
3222 FETCH c_ref_score INTO l_ref_score_rec;
3223 CLOSE c_ref_score;
3224
3225 IF l_ref_score_rec.wf_itemkey IS NOT NULL THEN
3226
3227 IF l_ref_score_rec.status_code = G_STATUS_SCHEDULED OR
3228 l_ref_score_rec.status_code = G_STATUS_QUEUED OR
3229 l_ref_score_rec.status_code = G_STATUS_SCORING THEN
3230
3231 -- Get the original status of the Model when then the WF process was scheduled
3232 AMS_WFMOD_PVT.get_original_status(
3233 p_itemkey => l_ref_score_rec.wf_itemkey,
3234 x_orig_status_id => l_original_status_id,
3235 x_return_status => x_return_status
3236 );
3237
3238 AMS_WFMod_PVT.cancel_process (
3239 p_itemkey => l_ref_score_rec.wf_itemkey,
3240 x_return_status => x_return_status
3241 );
3242
3243 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
3244 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3245 -- Report that an error occurred, but ifgnore it and proceed with re-setting
3246 -- the Scoring Run status.
3247 AMS_Utility_PVT.create_log (
3248 x_return_status => l_return_status,
3249 p_arc_log_used_by => G_OBJECT_TYPE_SCORE,
3250 p_log_used_by_id => p_score_id,
3251 p_msg_data => L_API_NAME || ': Error while canceling Scoring Run process'
3252 );
3253 --RAISE FND_API.G_EXC_ERROR;
3254 ELSE
3255 AMS_Utility_PVT.create_log (
3256 x_return_status => l_return_status,
3257 p_arc_log_used_by => G_OBJECT_TYPE_SCORE,
3258 p_log_used_by_id => p_score_id,
3259 p_msg_data => L_API_NAME || ': Successfully canceled Scoring Run Process'
3260 );
3261 END IF;
3262
3263 -- Set wf_itemkey to null
3264 l_ref_score_rec.wf_itemkey := NULL;
3265
3266 IF l_ref_score_rec.status_code = G_STATUS_SCORING THEN
3267
3268 -- Scoring Run was in SCORING status, then set its status to DRAFT
3269 l_ref_score_rec.status_code := G_DEFAULT_STATUS;
3270
3271
3272 -- Set results_flag to 'N', since all score results have been cleaned
3273 l_ref_score_rec.results_flag := 'N';
3274
3275 -- Set the user_status_id associated with the new status code
3276 OPEN c_user_status_id (G_STATUS_TYPE_SCORE, l_ref_score_rec.status_code);
3277 FETCH c_user_status_id INTO l_ref_score_rec.user_status_id;
3278 CLOSE c_user_status_id;
3279
3280 ELSE
3281 -- Scoring Run was in SCHEDULED/QUEUED status, set its status to its original status
3282 -- when the WF process was started
3283
3284 l_ref_score_rec.user_status_id := l_original_status_id;
3285
3286 -- Set the system_status_code associated with the original status id
3287 OPEN c_user_status_code (G_STATUS_TYPE_SCORE, l_ref_score_rec.user_status_id);
3288 FETCH c_user_status_code INTO l_ref_score_rec.status_code;
3289 CLOSE c_user_status_code;
3290
3291 END IF;
3292
3293
3294 -- update the Scoring Run record with new status code and id and with NULL wf_itemkey
3295 UPDATE ams_dm_scores_all_b
3296 SET object_version_number = object_version_number + 1,
3297 last_update_date = SYSDATE,
3298 last_updated_by = FND_GLOBAL.user_id,
3299 status_date = SYSDATE,
3300 status_code = l_ref_score_rec.status_code,
3301 user_status_id = l_ref_score_rec.user_status_id,
3302 wf_itemkey = l_ref_score_rec.wf_itemkey,
3303 results_flag = l_ref_score_rec.results_flag
3304 WHERE score_id = p_score_id;
3305
3306
3307 -- Update the Model back to AVAILABLE
3308 OPEN c_user_status_id (G_STATUS_TYPE_MODEL, G_STATUS_AVAILABLE);
3309 FETCH c_user_status_id INTO l_model_status_id;
3310 CLOSE c_user_status_id;
3311
3312 UPDATE ams_dm_models_all_b
3313 SET last_update_date = SYSDATE
3314 , last_updated_by = FND_GLOBAL.user_id
3315 , last_update_login = FND_GLOBAL.conc_login_id
3316 , object_version_number = object_version_number + 1
3317 , status_code = G_STATUS_AVAILABLE
3318 , user_status_id = l_model_status_id
3319 , status_date = SYSDATE
3320 WHERE model_id = l_ref_score_rec.model_id;
3321 ELSE
3322 -- No Run Request/Process to cancel
3323 -- Set x_return_status to expected error. This will results in a different message
3324 -- displayed to the user
3325 x_return_status := FND_API.G_RET_STS_ERROR;
3326
3327 AMS_Utility_PVT.create_log (
3328 x_return_status => l_return_status,
3329 p_arc_log_used_by => G_OBJECT_TYPE_SCORE,
3330 p_log_used_by_id => p_score_id,
3331 p_msg_data => L_API_NAME || ': No Scoring Run process to cancel'
3332 );
3333 END IF;
3334 ELSE
3335 -- No Run Request/Process to cancel
3336 -- Set x_return_status to expected error. This will results in a different message
3337 -- displayed to the user
3338 x_return_status := FND_API.G_RET_STS_ERROR;
3339
3340 AMS_Utility_PVT.create_log (
3341 x_return_status => l_return_status,
3342 p_arc_log_used_by => G_OBJECT_TYPE_SCORE,
3343 p_log_used_by_id => p_score_id,
3344 p_msg_data => L_API_NAME || ': No Scoring Run process to cancel'
3345 );
3346 END IF;
3347
3348 AMS_Utility_PVT.create_log (
3349 x_return_status => l_return_status,
3350 p_arc_log_used_by => G_OBJECT_TYPE_SCORE,
3351 p_log_used_by_id => p_score_id,
3352 p_msg_data => L_API_NAME || ': End'
3353 );
3354
3355 -- Debug Message
3356 IF (AMS_DEBUG_HIGH_ON) THEN
3357
3358 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' End');
3359 END IF;
3360
3361 END cancel_run_request;
3362
3363
3364 PROCEDURE wf_startPreviewProcess (
3365 p_score_id IN NUMBER,
3366 p_orig_status_id IN NUMBER,
3367 x_tar_score_rec IN OUT NOCOPY score_rec_type,
3368 x_return_status OUT NOCOPY VARCHAR2
3369 )
3370 IS
3371 -- used with get_user_timezone api
3372 l_user_timezone_name VARCHAR2(80);
3373 L_API_NAME CONSTANT VARCHAR2(30) := 'wf_startPreviewProcess';
3374
3375 BEGIN
3376
3377 -- Debug Message
3378 IF (AMS_DEBUG_HIGH_ON) THEN
3379
3380 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' Start');
3381 END IF;
3382
3383 -- fix for bug 4961279. changed get_user_timezone to get_system_timezone
3384 AMS_Utility_PVT.get_system_timezone (
3385 x_return_status => x_return_status,
3386 x_sys_time_id => x_tar_score_rec.scheduled_timezone_id,
3387 x_sys_time_name => l_user_timezone_name
3388 );
3389 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3390 RAISE FND_API.G_EXC_ERROR;
3391 END IF;
3392
3393 -- When we submit a Preview request, we clean up all the previous
3394 -- Scoring Run results since we assume that the data selections may have
3395 -- changed which means that the previous results do not match the
3396 -- data selections.
3397 cleanupPreviousScoreData(p_score_id);
3398
3399 -- Set the Scoring Run status to PREVIEWING
3400 x_tar_score_rec.status_code := G_STATUS_PREVIEWING;
3401 OPEN c_user_status_id (G_STATUS_TYPE_SCORE, x_tar_score_rec.status_code);
3402 FETCH c_user_status_id INTO x_tar_score_rec.user_status_id;
3403 CLOSE c_user_status_id;
3404
3405 -- Initiate Workflow process and grab the itemkey
3406 AMS_WFMOD_PVT.StartProcess(
3407 p_object_id => p_score_id,
3408 p_object_type => G_OBJECT_TYPE_SCORE,
3409 p_user_status_id => p_orig_status_id,
3410 p_scheduled_timezone_id => x_tar_score_rec.scheduled_timezone_id,
3411 p_scheduled_date => SYSDATE,
3412 p_request_type => 'PREVIEW',
3413 p_select_list => NULL,
3414 x_itemkey => x_tar_score_rec.wf_itemkey
3415 );
3416
3417 -- Debug Message
3418 IF (AMS_DEBUG_HIGH_ON) THEN
3419 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' End');
3420 END IF;
3421
3422 END wf_startPreviewProcess;
3423
3424 --
3425 -- History
3426 -- 04-Oct-2002 nyostos Created.
3427 PROCEDURE cleanupPreviousScoreData(
3428 p_score_ID NUMBER)
3429 IS
3430
3431 L_API_NAME CONSTANT VARCHAR2(30) := 'cleanupPreviousScoreData';
3432
3433 BEGIN
3434
3435 -- Debug Message
3436 IF (AMS_DEBUG_HIGH_ON) THEN
3437 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' Start');
3438 END IF;
3439
3440 -- Remove DM Source records
3441 delete /*+ index(AMS_DM_SOURCE AMS_DM_SOURCE_U2) */ from ams_dm_source
3442 where arc_used_for_object = G_OBJECT_TYPE_SCORE
3443 and used_for_object_id = p_score_ID;
3444
3445 -- ams_dm_score_results
3446 delete from ams_dm_score_results
3447 where score_id = p_score_id;
3448
3449 -- ams_dm_score_pct_results
3450 delete from ams_dm_score_pct_results
3451 where score_id = p_score_id;
3452
3453 -- Set the results_flag to 'N'
3454 UPDATE ams_dm_scores_all_b
3455 SET results_flag = 'N'
3456 WHERE score_id = p_score_id;
3457
3458 -- Debug Message
3459 IF (AMS_DEBUG_HIGH_ON) THEN
3460 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' End.');
3461 END IF;
3462
3463 END cleanupPreviousScoreData;
3464
3465
3466 --
3467 -- Purpose
3468 -- To check if there is data selected to be Previewed. We cannot Preview
3469 -- data selections for a Scoring Run if it has no list, segment, workbook,... selected.
3470 --
3471 -- History
3472 -- 04-Oct-2002 nyostos Created.
3473 PROCEDURE dataToPreview(
3474 p_score_id IN NUMBER,
3475 x_data_exists_flag OUT NOCOPY VARCHAR2
3476 )
3477 IS
3478
3479 l_model_id NUMBER;
3480 l_seeded_ds_flag VARCHAR2(1);
3481 l_data_selections_count NUMBER;
3482 L_API_NAME CONSTANT VARCHAR2(30) := 'dataToPreview';
3483
3484 CURSOR l_dataSelectionsExist (p_score_id IN NUMBER) IS
3485 SELECT count(*)
3486 FROM ams_list_select_actions
3487 WHERE arc_action_used_by = 'SCOR'
3488 AND action_used_by_id = p_score_id;
3489
3490 CURSOR l_modelId (p_score_id IN NUMBER) IS
3491 SELECT model_id
3492 FROM ams_dm_scores_all_b
3493 WHERE score_id = p_score_id;
3494
3495 BEGIN
3496
3497 -- Debug Message
3498 IF (AMS_DEBUG_HIGH_ON) THEN
3499
3500 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' Start');
3501 END IF;
3502
3503 -- Initialize x_data_exists_flag to 'Y'
3504 x_data_exists_flag := 'Y';
3505
3506 -- Initialize l_seeded_ds_flag to 'N'
3507 l_seeded_ds_flag := 'N';
3508
3509 -- Fetch model id for this scoring run
3510 OPEN l_modelId (p_score_id);
3511 FETCH l_modelId INTO l_model_id;
3512 CLOSE l_modelId;
3513
3514 -- Check if Model has a seeded data source
3515 AMS_DM_MODEL_PVT.seededDataSource (l_model_id, l_seeded_ds_flag);
3516
3517 -- Debug Message
3518 IF (AMS_DEBUG_HIGH_ON) THEN
3519
3520 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' l_seeded_ds_flag = ' || l_seeded_ds_flag);
3521 END IF;
3522
3523 IF l_seeded_ds_flag = 'Y' THEN
3524
3525 l_data_selections_count := 0;
3526
3527 -- Check if the SCoring Run has any data selections
3528 OPEN l_dataSelectionsExist (p_score_id);
3529 FETCH l_dataSelectionsExist INTO l_data_selections_count;
3530 CLOSE l_dataSelectionsExist;
3531
3532 -- If no data selections exist, then set the flag to N
3533 IF l_data_selections_count IS NULL or l_data_selections_count = 0 THEN
3534 x_data_exists_flag := 'N';
3535 END IF;
3536 END IF;
3537
3538 -- Debug Message
3539 IF (AMS_DEBUG_HIGH_ON) THEN
3540
3541 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' End. x_data_exists_flag = ' || x_data_exists_flag);
3542 END IF;
3543
3544 END dataToPreview;
3545
3546 --
3547 -- Purpose
3548 -- To check if a Preview request can be started. We cannot Preview
3549 -- data selections for a Scoring Run if it has any of the following statuses:
3550 -- SCHEDULED, SCORING, PREVIEWING, ARCHIVED, QUEUED.
3551 --
3552 -- History
3553 -- 04-Oct-2002 nyostos Created.
3554 PROCEDURE proceedWithPreview(
3555 p_score_id IN NUMBER,
3556 x_proceed_flag OUT NOCOPY VARCHAR2
3557 )
3558 IS
3559
3560 l_status_code VARCHAR2(30);
3561 l_user_status_id NUMBER;
3562 L_API_NAME CONSTANT VARCHAR2(30) := 'proceedWithPreview';
3563
3564 BEGIN
3565
3566 -- Debug Message
3567 IF (AMS_DEBUG_HIGH_ON) THEN
3568
3569 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' Start');
3570 END IF;
3571
3572 -- Initialize proceed flag to 'Y'
3573 x_proceed_flag := 'Y';
3574
3575 -- Check Scoring Run Status Code
3576 getScoreStatus( p_score_id, l_status_code , l_user_status_id);
3577
3578 -- Debug Message
3579 IF (AMS_DEBUG_HIGH_ON) THEN
3580
3581 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' Status Code = ' || l_status_code);
3582 END IF;
3583
3584 IF l_status_code = G_STATUS_SCHEDULED OR
3585 l_status_code = G_STATUS_SCORING OR
3586 l_status_code = G_STATUS_QUEUED OR
3587 l_status_code = G_STATUS_PREVIEWING OR
3588 l_status_code = G_STATUS_ARCHIVED
3589 THEN
3590 x_proceed_flag := 'N';
3591 END IF;
3592
3593 -- Debug Message
3594 IF (AMS_DEBUG_HIGH_ON) THEN
3595
3596 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' End. Proceed Flag = ' || x_proceed_flag);
3597 END IF;
3598
3599 END proceedWithPreview;
3600
3601
3602 --
3603 -- Purpose
3604 -- Returns Scoring Run Status_Code and User_Status_Id for a Scoring Run
3605 --
3606 -- History
3607 -- 04-Oct-2002 nyostos Created.
3608 PROCEDURE getScoreStatus(
3609 p_score_id IN NUMBER,
3610 x_status_code OUT NOCOPY VARCHAR2,
3611 x_user_status_id OUT NOCOPY NUMBER
3612 )
3613 IS
3614
3615 L_API_NAME CONSTANT VARCHAR2(30) := 'getScoreStatus';
3616
3617 CURSOR c_score_status (p_score_id IN NUMBER) IS
3618 SELECT status_code, user_status_id
3619 FROM ams_dm_scores_vl
3620 WHERE score_id = p_score_id
3621 ;
3622
3623 BEGIN
3624
3625 -- Debug Message
3626 IF (AMS_DEBUG_HIGH_ON) THEN
3627
3628 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' Start');
3629 END IF;
3630
3631 -- Get the model status
3632 OPEN c_score_status (p_score_id);
3633 FETCH c_score_status INTO x_status_code, x_user_status_id;
3634 CLOSE c_score_status;
3635
3636 -- Debug Message
3637 IF (AMS_DEBUG_HIGH_ON) THEN
3638
3639 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' End. Model Status Code = ' || x_status_code);
3640 END IF;
3641
3642 END getScoreStatus;
3643
3644
3645 --
3646 -- Purpose
3647 -- To check if Scoring Run data selection sizing options and selection
3648 -- method have changed. This would INVALIDate a COMPLETED Scornig Run.
3649 --
3650 -- History
3651 -- 07-Oct-2002 nyostos Created.
3652 PROCEDURE check_data_size_changes(
3653 p_input_score_rec IN score_rec_type,
3654 x_selections_changed_flag OUT NOCOPY VARCHAR2
3655 )
3656 IS
3657 L_API_NAME CONSTANT VARCHAR2(30) := 'check_data_size_changes';
3658
3659 CURSOR c_ref_score (p_score_id IN NUMBER) IS
3660 SELECT *
3661 FROM ams_dm_scores_vl
3662 WHERE score_id = p_score_id
3663 ;
3664 l_ref_score_rec c_ref_score%ROWTYPE;
3665
3666 BEGIN
3667 -- Debug Message
3668 IF (AMS_DEBUG_HIGH_ON) THEN
3669
3670 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' Begin.');
3671 END IF;
3672
3673 x_selections_changed_flag := 'N';
3674
3675 -- get the reference Scoring Run, which contains
3676 -- data before the update operation.
3677 OPEN c_ref_score (p_input_score_rec.score_id);
3678 FETCH c_ref_score INTO l_ref_score_rec;
3679 CLOSE c_ref_score;
3680
3681 -- min records
3682 IF (l_ref_score_rec.MIN_RECORDS IS NULL AND p_input_score_rec.MIN_RECORDS IS NOT NULL) OR
3683 (l_ref_score_rec.MIN_RECORDS <> p_input_score_rec.MIN_RECORDS) THEN
3684 x_selections_changed_flag := 'Y';
3685 RETURN;
3686 END IF;
3687
3688 -- max records
3689 IF (l_ref_score_rec.MAX_RECORDS IS NULL AND p_input_score_rec.MAX_RECORDS IS NOT NULL) OR
3690 (l_ref_score_rec.MAX_RECORDS <> p_input_score_rec.MAX_RECORDS) THEN
3691 x_selections_changed_flag := 'Y';
3692 RETURN;
3693 END IF;
3694
3695 -- row_selection_type
3696 IF (l_ref_score_rec.row_selection_type IS NULL AND p_input_score_rec.row_selection_type IS NOT NULL) OR
3697 (l_ref_score_rec.row_selection_type <> p_input_score_rec.row_selection_type) THEN
3698 x_selections_changed_flag := 'Y';
3699 RETURN;
3700 END IF;
3701
3702 -- every_nth_row
3703 IF (l_ref_score_rec.EVERY_NTH_ROW IS NULL AND p_input_score_rec.EVERY_NTH_ROW IS NOT NULL) OR
3704 (l_ref_score_rec.EVERY_NTH_ROW <> p_input_score_rec.EVERY_NTH_ROW) THEN
3705 x_selections_changed_flag := 'Y';
3706 RETURN;
3707 END IF;
3708
3709 -- pct_random
3710 IF (l_ref_score_rec.PCT_RANDOM IS NULL AND p_input_score_rec.PCT_RANDOM IS NOT NULL) OR
3711 (l_ref_score_rec.PCT_RANDOM <> p_input_score_rec.PCT_RANDOM) THEN
3712 x_selections_changed_flag := 'Y';
3713 RETURN;
3714 END IF;
3715
3716 -- Debug Message
3717 IF (AMS_DEBUG_HIGH_ON) THEN
3718
3719 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' End. Selections Changed Flag = ' || x_selections_changed_flag);
3720 END IF;
3721
3722 END check_data_size_changes;
3723
3724 --
3725 -- Purpose
3726 -- Checks whether a model is still AVAILABLE for scoring
3727 -- History
3728 -- 09-Oct-2002 nyostos Created.
3729 PROCEDURE wf_checkModelStatus (
3730 p_score_id IN NUMBER,
3731 x_return_status OUT NOCOPY VARCHAR2,
3732 x_model_status OUT NOCOPY VARCHAR2
3733 )
3734 IS
3735 l_user_status_id NUMBER;
3736 l_model_status_id NUMBER;
3737
3738 CURSOR c_model (p_model_id IN NUMBER) IS
3739 SELECT *
3740 FROM ams_dm_models_all_b
3741 WHERE model_id = p_model_id;
3742 l_model_rec c_model%ROWTYPE;
3743
3744 CURSOR c_score (p_score_id IN NUMBER) IS
3745 SELECT *
3746 FROM ams_dm_scores_all_b
3747 WHERE score_id = p_score_id;
3748 l_score_rec c_score%ROWTYPE;
3749 BEGIN
3750 -- Initialize API return status to SUCCESS
3751 x_return_status := FND_API.G_RET_STS_SUCCESS;
3752
3753 OPEN c_score (p_score_id);
3754 FETCH c_score INTO l_score_rec;
3755 CLOSE c_score;
3756
3757 OPEN c_model (l_score_rec.model_id);
3758 FETCH c_model INTO l_model_rec;
3759 CLOSE c_model;
3760
3761 -- if the model is not AVAILABLE or being used in SCORING, then
3762 -- it cannot be used for scoring.
3763 x_model_status := l_model_rec.status_code;
3764 IF l_model_rec.status_code NOT IN (G_STATUS_AVAILABLE, G_STATUS_SCORING) THEN
3765 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3766 END IF;
3767
3768 END wf_checkModelStatus;
3769
3770 --
3771 -- Purpose
3772 -- Procedure to handle data selection changes
3773 -- This would INVALIDate a COMPLETED Scoring Run.
3774 --
3775 -- History
3776 -- 14-Oct-2002 nyostos Created.
3777 PROCEDURE handle_data_selection_changes(
3778 p_score_id IN NUMBER
3779 )
3780 IS
3781 L_API_NAME CONSTANT VARCHAR2(30) := 'handle_data_selection_changes';
3782
3783 CURSOR c_ref_score (p_score_id IN NUMBER) IS
3784 SELECT *
3785 FROM ams_dm_scores_vl
3786 WHERE score_id = p_score_id
3787 ;
3788 l_ref_score_rec c_ref_score%ROWTYPE;
3789
3790 l_status_id NUMBER;
3791
3792 BEGIN
3793 -- Debug Message
3794 IF (AMS_DEBUG_HIGH_ON) THEN
3795
3796 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' Begin.');
3797 END IF;
3798
3799 -- Load the score record to get the original status
3800 OPEN c_ref_score (p_score_id);
3801 FETCH c_ref_score INTO l_ref_score_rec;
3802 CLOSE c_ref_score;
3803
3804 -- If the Scoring Run is COMPLETED, then change its status to INVALID
3805 IF l_ref_score_rec.status_code = G_STATUS_COMPLETED THEN
3806
3807 -- Get the status id for INVALID status code.
3808 OPEN c_user_status_id (G_STATUS_TYPE_SCORE, G_STATUS_INVALID);
3809 FETCH c_user_status_id INTO l_status_id;
3810 CLOSE c_user_status_id;
3811
3812 -- update the Scoring Run record with new status code and id and with NULL wf_itemkey
3813 -- UPDATE ams_dm_scores_all_b
3814 -- SET object_version_number = object_version_number + 1,
3815 -- last_update_date = SYSDATE,
3816 -- last_updated_by = FND_GLOBAL.user_id,
3817 -- status_date = SYSDATE,
3818 -- status_code = G_STATUS_INVALID,
3819 -- user_status_id = l_status_id
3820 -- WHERE score_id = p_score_id;
3821
3822 UPDATE ams_dm_scores_all_b
3823 SET last_update_date = SYSDATE,
3824 last_updated_by = FND_GLOBAL.user_id,
3825 status_date = SYSDATE,
3826 status_code = G_STATUS_INVALID,
3827 user_status_id = l_status_id
3828 WHERE score_id = p_score_id;
3829
3830 IF (AMS_DEBUG_HIGH_ON) THEN
3831
3832
3833
3834 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' Invalidated Scoring Run.');
3835
3836 END IF;
3837
3838 END IF;
3839
3840 -- Debug Message
3841 IF (AMS_DEBUG_HIGH_ON) THEN
3842
3843 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' End.');
3844 END IF;
3845
3846 END handle_data_selection_changes;
3847
3848 END ams_dm_score_pvt;
[Home] [Help]