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