[Home] [Help]
PACKAGE BODY: APPS.AMS_WFMOD_PVT
Source
1 PACKAGE BODY AMS_WFMOD_PVT AS
2 /* $Header: amsvwmdb.pls 120.0 2005/05/31 19:57:04 appldev noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMS_WFMOD_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvwmdb.pls';
6 G_STATUS_DRAFT CONSTANT VARCHAR2(30) := 'DRAFT';
7 G_STATUS_BUILDING CONSTANT VARCHAR2(30) := 'BUILDING';
8 G_STATUS_SCORING CONSTANT VARCHAR2(30) := 'SCORING';
9 G_STATUS_AVAILABLE CONSTANT VARCHAR2(30) := 'AVAILABLE';
10 G_STATUS_COMPLETED CONSTANT VARCHAR2(30) := 'COMPLETED';
11 G_STATUS_QUEUED CONSTANT VARCHAR2(30) := 'QUEUED';
12 G_OBJECT_TYPE_MODEL CONSTANT VARCHAR2(30) := 'MODL';
13 G_OBJECT_TYPE_SCORE CONSTANT VARCHAR2(30) := 'SCOR';
14
15 G_STATUS_PREVIEWING CONSTANT VARCHAR2(30) := 'PREVIEWING';
16 G_STATUS_INVALID CONSTANT VARCHAR2(30) := 'INVALID';
17 G_STATUS_FAILED CONSTANT VARCHAR2(30) := 'FAILED';
18 G_PREVIEW_REQUEST CONSTANT VARCHAR2(30) := 'PREVIEW';
19 G_PREVIEW_STARTED CONSTANT VARCHAR2(30) := 'PREVIEW_STARTED';
20 G_PREVIEW_COMPLETE CONSTANT VARCHAR2(30) := 'PREVIEW_COMPLETE';
21
22 -- Start of Comments
23 --
24 -- NAME
25 -- AMS_WFMOD_PVT
26 --
27 -- PURPOSE
28 -- This package contains the workflow procedures for
29 -- Model Building/Scoring in Oracle Marketing
30 --
31 -- HISTORY
32 -- 11/30/2000 sveerave@us CREATED
33 -- 07-Mar-2001 choang 1) Changed object type from MINING to MODL. 2)
34 -- replaced itemkey in call to create_log with object_id
35 -- 08-Mar-2001 choang Added user_status_id in startprocess.
36 -- 20-Mar-2001 choang Modified all messages.
37 -- 30-Mar-2001 choang Added cancel_process and change_schedule
38 -- 10-Apr-2001 choang Added log entry for response messages from dequeue.
39 -- 11-Apr-2001 choang handle updates to base objects for different ending statuses.
40 -- 19-Apr-2001 choang - Fixed problem in update_obj_status to use l_object_status
41 -- instead of l_status_code
42 -- - update logs_flag in model/score table when writing log entry
43 -- 03-Jul-2001 choang Replaced callouts to AQ with submit request to concurrent
44 -- manager.
45 -- 12-Jul-2001 choang - added calls to bin_probability and process_scores
46 -- collect_results.
47 -- - added validate_concurrency.
48 -- 27-Jul-2001 choang added callout to generate_odm_input_views.
49 -- 20-Aug-2001 choang fixed result in validate_concurrency.
50 -- 04-Sep-2001 choang Added wait for conc request to complete.
51 -- 11-Nov-2001 choang Added start/end messages to main wf functions.
52 -- 16-Nov-2001 choang Added commits to change_schedule and cancel_process.
53 -- 26-Nov-2001 choang Changed scoring run completion status to COMPLETED
54 -- from AVAILABLE.
55 -- 07-Dec-2001 choang Modified logic for change_schedule
56 -- 30-May-2002 choang Fixed select order of model and scoring run
57 -- in validate_data details.
58 -- 03-Jun-2002 choang Replaced target_group_type with data_source_id and use
59 -- new version of generate_odm_input_views; do not
60 -- perform party data extraction for alternative data
61 -- sources.
62 -- 29-Aug-2002 nyostos Added Reset_Status procedure to reset model/score status
63 -- to DRAFT.
64 -- 17-Sep-2002 nyostos Changes to support new Model and Scoring Run States
65 -- Three new states are added: INVALID, FAILED and PREVIEWING.
66 -- - Reset_Status - sets the status to FAILED
67 -- 22-Oct-2002 nyostos Fixed a problem in validate_concurrency where multiple preview
68 -- requests deadlocked.
69 -- 30-Jan-2003 nyostos Moved the check for owner role from Validate_data() to startProcess().
70 -- The WF process will not be started if the Model/Score owner does not have
71 -- a valid WF approver role.
72 -- 23-Jul-2003 rosharma Passed score ID to AMS_JCP_MODEL_APPLY.
73 -- 16-Sep-2003 nyostos Changes related to allowing parallel mining operations.
74 -- 20-Sep-2003 rosharma Changes related to Audience data source uptake.
75 -- 22-Sep-2003 nyostos Fixed TRANSFORM to exit if generate_odm_input_views errors out and not
76 -- proceed with ExtractMain.
77 -- 12-Feb-2004 rosharma Bug # 3436093.
78 -- 06-Apr-2004 rosharma Bug # 3557739.
79 -- 22-Apr-2005 srivikri Bug # 4305459.
80 --
81
82 AMS_DEBUG_HIGH_ON CONSTANT BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
83 AMS_DEBUG_LOW_ON CONSTANT BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
84 AMS_DEBUG_MEDIUM_ON CONSTANT BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
85
86 PROCEDURE Get_User_Role
87 ( p_user_id IN NUMBER,
88 x_role_name OUT NOCOPY VARCHAR2,
89 x_role_display_name OUT NOCOPY VARCHAR2 ,
90 x_return_status OUT NOCOPY VARCHAR2
91 );
92
93 PROCEDURE write_buffer_to_log (
94 p_object_type IN VARCHAR2,
95 p_object_id IN NUMBER
96 );
97
98
99 /*************************** PRIVATE ROUTINES *******************************/
100
101 -- Start of Comments
102 --
103 -- NAME
104 -- Intialize_Var
105 --
106 -- PURPOSE
107 -- This Procedure will initialize all the variables
108 --
109 -- Called By
110 -- Start_Process
111 --
112 -- NOTES
113 -- When the process is started , all the variables are extracted
114 -- from database using object id, request type passed to the Start Process
115 --
116 -- HISTORY
117 -- 11/30/2000 sveerave@us created
118 -- End of Comments
119
120 PROCEDURE Initialize_Var ( p_object_id IN NUMBER
121 , p_object_type IN VARCHAR2
122 , p_user_status_id IN NUMBER
123 , p_scheduled_timezone_id IN NUMBER
124 , p_scheduled_date IN DATE
125 , p_request_type IN VARCHAR2
126 , p_select_list IN VARCHAR2
127 , p_enqueue_message IN VARCHAR2
128 , p_itemtype IN VARCHAR2
129 , p_itemkey IN VARCHAR2
130 ) IS
131 l_return_status VARCHAR2(1);
132 l_msg_count NUMBER;
133 l_msg_data VARCHAR2(4000);
134
135 l_system_scheduled_date DATE;
136
137 BEGIN
138 WF_ENGINE.SetItemAttrText(
139 itemtype => p_itemtype
140 , itemkey => p_itemkey
141 , aname => 'MONITOR_URL'
142 , avalue => wf_monitor.geturl(wf_core.TRANSLATE('WF_WEB_AGENT'), p_itemtype, p_itemkey, 'NO')
143 );
144
145 IF (AMS_DEBUG_HIGH_ON) THEN
146 AMS_Utility_PVT.debug_message ('URL: ' || wf_monitor.geturl(wf_core.TRANSLATE('WF_WEB_AGENT'), p_itemtype, p_itemkey, 'NO'));
147 END IF;
148
149 WF_ENGINE.SetItemAttrNumber(
150 itemtype => p_itemtype
151 , itemkey => p_itemkey
152 , aname => 'OBJECT_ID'
153 , avalue => p_object_id
154 );
155
156 WF_ENGINE.SetItemAttrText(
157 itemtype => p_itemtype
158 , itemkey => p_itemkey
159 , aname => 'OBJECT_TYPE'
160 , avalue => p_object_type
161 );
162
163 WF_ENGINE.SetItemAttrNumber(
164 itemtype => p_itemtype
165 , itemkey => p_itemkey
166 , aname => 'ORIG_USER_STATUS_ID'
167 , avalue => p_user_status_id
168 );
169
170 -- if needed, convert the time into system time from schedule time zone ??
171 -- for time being, assuming that shedule_date and workflow system date are in
172 -- in same time zone ??
173 AMS_Utility_PVT.convert_timezone (
174 p_init_msg_list => FND_API.G_FALSE,
175 x_return_status => l_return_status,
176 x_msg_count => l_msg_count,
177 x_msg_data => l_msg_data,
178 p_user_tz_id => p_scheduled_timezone_id,
179 p_in_time => p_scheduled_date,
180 p_convert_type => 'SYS',
181 x_out_time => l_system_scheduled_date
182 );
183 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
184 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
185 END IF;
186
187 IF (AMS_DEBUG_HIGH_ON) THEN
188 AMS_Utility_PVT.debug_message ('SYSTEM SCHEDULED - WAIT: ' || TO_CHAR (l_system_scheduled_date, 'DD-MON-RRRR HH24:MI:SS'));
189 END IF;
190
191 WF_ENGINE.SetItemAttrDate(
192 itemtype => p_itemtype
193 , itemkey => p_itemkey
194 , aname => 'SCHEDULE_DATE'
195 , avalue => l_system_scheduled_date
196 );
197
198 IF p_request_type IS NULL THEN
199 WF_ENGINE.SetItemAttrText(
200 itemtype => p_itemtype
201 , itemkey => p_itemkey
202 , aname => 'REQUEST_TYPE'
203 , avalue => ' '
204 );
205 ELSE
206 WF_ENGINE.SetItemAttrText(
207 itemtype => p_itemtype
208 , itemkey => p_itemkey
209 , aname => 'REQUEST_TYPE'
210 , avalue => p_request_type
211 );
212 END IF;
213
214
215 WF_ENGINE.SetItemAttrText(
216 itemtype => p_itemtype
217 , itemkey => p_itemkey
218 , aname => 'SELECT_LIST'
219 , avalue => p_select_list
220 );
221
222 /* If PREVIEW request, then set the STATUS_CODE variable to PREVIEWING */
223 IF p_request_type = G_PREVIEW_REQUEST THEN
224 WF_ENGINE.SetItemAttrText(
225 itemtype => p_itemtype
226 , itemkey => p_itemkey
227 , aname => 'STATUS_CODE'
228 , avalue => G_STATUS_PREVIEWING
229 );
230 ELSE
231 /* Set status to BUILDING or SCORING as per object_type */
232 IF p_object_type = G_OBJECT_TYPE_MODEL THEN
233 WF_ENGINE.SetItemAttrText(
234 itemtype => p_itemtype
235 , itemkey => p_itemkey
236 , aname => 'STATUS_CODE'
237 , avalue => G_STATUS_BUILDING
238 );
239 ELSIF p_object_type = G_OBJECT_TYPE_SCORE THEN
240 WF_ENGINE.SetItemAttrText(
241 itemtype => p_itemtype
242 , itemkey => p_itemkey
243 , aname => 'STATUS_CODE'
244 , avalue => G_STATUS_SCORING
245 );
246 END IF;
247 END IF;
248
249 IF (AMS_DEBUG_HIGH_ON) THEN
250 AMS_Utility_PVT.debug_message ('REQUEST_TYPE: ' || p_request_type);
251 AMS_Utility_PVT.debug_message ('ORIG_USER_STATUS_ID: ' || p_user_status_id);
252 AMS_Utility_PVT.debug_message ('STATUS_CODE: ' || WF_ENGINE.GetItemAttrText(itemtype => p_itemtype, itemkey => p_itemkey, aname => 'STATUS_CODE'));
253 END IF;
254
255 END Initialize_Var ;
256
257 -- Start of Comments
258 --
259 -- NAME
260 -- StartProcess
261 --
262 -- PURPOSE
263 -- This Procedure will Start the flow
264 --
265 -- IN
266 -- p_object_id Object ID - Model or Score ID
267 -- p_object_type Object Type - MODL or SCOR
268 -- p_usr_status_id The original status of the object before WF
269 -- p_request_type Request Type for data mining engine, Darwin
270 -- p_select_list Select List for data mining engine, Darwin
271 -- processowner Owner Of the Process
272 -- workflowprocess Work Flow Process Name (MODEL_BUILD_SCORE)
273 -- itemtype Item type DEFAULT NULL(AMSDMMOD)
274
275 --
276 -- OUT
277 --
278 -- Used By Activities
279 --
280 -- NOTES
281 --
282 --
283 -- HISTORY
284 -- 11/30/2000 sveerave@us created
285 -- 07-mar-2001 choang Added x_itemkey
286 -- End of Comments
287
288
289 PROCEDURE StartProcess( p_object_id IN NUMBER
290 , p_object_type IN VARCHAR2
291 , p_user_status_id IN NUMBER
292 , p_scheduled_timezone_id IN NUMBER
293 , p_scheduled_date IN DATE
294 , p_request_type IN VARCHAR2 DEFAULT NULL
295 , p_select_list IN VARCHAR2 DEFAULT NULL
296 , p_enqueue_message IN VARCHAR2 DEFAULT NULL
297 , processowner IN VARCHAR2 DEFAULT NULL
298 , workflowprocess IN VARCHAR2 DEFAULT NULL
299 , itemtype IN VARCHAR2 DEFAULT G_DEFAULT_ITEMTYPE
300 , x_itemkey OUT NOCOPY VARCHAR2
301 ) IS
302 L_API_NAME CONSTANT VARCHAR2(30) := 'STARTPROCESS';
303
304
305 CURSOR c_get_model_owner(p_obj_id NUMBER) IS
306 SELECT owner_user_id
307 FROM ams_dm_models_vl model
308 WHERE model_id = p_obj_id
309 ;
310
311 CURSOR c_get_score_owner(p_obj_id NUMBER) IS
312 SELECT score.owner_user_id
313 FROM ams_dm_scores_vl score
314 WHERE score_id = p_obj_id
315 ;
316
317 l_owner_user_id NUMBER;
318 l_owner_role VARCHAR2(80);
319 l_owner_role_name VARCHAR2(120);
320
321 l_itemtype VARCHAR2(30) := nvl(itemtype, G_DEFAULT_ITEMTYPE);
322 itemkey VARCHAR2(30) := p_object_id||p_object_type||TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
323 itemuserkey VARCHAR2(80) := p_object_id||'-'||p_object_type;
324 l_return_status VARCHAR2(1);
325
326 BEGIN
327 -- clear the message buffer
328 FND_MSG_PUB.initialize;
329
330 AMS_Utility_PVT.create_log (
331 x_return_status => l_return_status,
332 p_arc_log_used_by => p_object_type,
333 p_log_used_by_id => p_object_id,
334 p_msg_data => L_API_NAME || ': begin ' || itemkey
335 );
336
337 -- Get Model/Score Owner
338 IF (p_object_type = G_OBJECT_TYPE_MODEL ) THEN
339
340 OPEN c_get_model_owner(p_object_id);
341 FETCH c_get_model_owner INTO l_owner_user_id;
342
343 IF c_get_model_owner%NOTFOUND THEN
344 IF (AMS_DEBUG_HIGH_ON) THEN
345 AMS_Utility_PVT.debug_message (L_API_NAME || ' - c_get_model_owner%NOTFOUND');
346 END IF;
347
348 x_itemkey := NULL;
349
350 RAISE FND_API.G_EXC_ERROR;
351 END IF;
352 CLOSE c_get_model_owner;
353
354 ELSIF p_object_type = G_OBJECT_TYPE_SCORE THEN
355
356 OPEN c_get_score_owner(p_object_id);
357 FETCH c_get_score_owner INTO l_owner_user_id;
358
359 IF c_get_score_owner%NOTFOUND THEN
360 IF (AMS_DEBUG_HIGH_ON) THEN
361 AMS_Utility_PVT.debug_message (L_API_NAME || ' - c_get_score_owner%NOTFOUND');
362 END IF;
363
364 x_itemkey := NULL;
365
366 RAISE FND_API.G_EXC_ERROR;
367 END IF;
368 CLOSE c_get_score_owner;
369
370 END IF;
371
372 -- Check the Model/Score owner has the right role
373 Get_User_Role(p_user_id => l_owner_user_id ,
374 x_role_name => l_owner_role,
375 x_role_display_name => l_owner_role_name,
376 x_return_status => l_return_status);
377
378 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
379 IF (AMS_DEBUG_HIGH_ON) THEN
380 AMS_Utility_PVT.debug_message (L_API_NAME || ' - Get_User_Role Failed. ');
381 END IF;
382
383 x_itemkey := NULL;
384 ELSE
385
386 IF (AMS_DEBUG_HIGH_ON) THEN
387 Ams_Utility_pvt.debug_message('ITEMTYPE: ' || l_itemtype || ' - ITEMKEY: ' || itemkey);
388 END IF;
389
390 -- dbms_output.put_line('Start');
391 WF_ENGINE.CreateProcess ( itemtype => l_itemtype
392 , itemkey => itemkey
393 , process => 'MODEL_BUILD_SCORE'
394 );
395
396 -- Call a Proc to Initialize the Variables
397 Initialize_Var( p_object_id => p_object_id
398 , p_object_type => p_object_type
399 , p_user_status_id => p_user_status_id
400 , p_scheduled_timezone_id => p_scheduled_timezone_id
401 , p_scheduled_date => p_scheduled_date
402 , p_request_type => p_request_type
403 , p_select_list => p_select_list
404 , p_enqueue_message => p_enqueue_message
405 , p_itemtype => l_itemtype
406 , p_itemkey => itemkey
407 );
408
409 -- srivikri - bug4305459 04/22/05- setting item owner
410 Wf_Engine.SetItemOwner(itemtype => itemtype,
411 itemkey => itemkey,
412 owner => l_owner_role);
413
414 WF_ENGINE.StartProcess ( itemtype => l_itemtype
415 , itemkey => itemkey
416 );
417
418 -- return to calling procedure
419 -- note: originally created for schedule cancellation in
420 -- model and score screens.
421 x_itemkey := itemkey;
422 END IF;
423
424 write_buffer_to_log (p_object_type, p_object_id);
425
426 AMS_Utility_PVT.create_log (
427 x_return_status => l_return_status,
428 p_arc_log_used_by => p_object_type,
429 p_log_used_by_id => p_object_id,
430 p_msg_data => L_API_NAME || ': end'
431 );
432
433 EXCEPTION
434 -- The line below records this function call in the error system
435 -- in the case of an exception.
436 WHEN OTHERS THEN
437 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
438 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
439 END IF;
440
441 write_buffer_to_log (p_object_type, p_object_id);
442
443 wf_core.context (G_PKG_NAME, 'StartProcess',p_object_id,itemuserkey,workflowprocess);
444
445 raise;
446 END StartProcess;
447
448 -- Start of Comments
449 --
450 -- NAME
451 -- Selector
452 --
453 -- PURPOSE
454 -- This Procedure will determine which process to run
455 --
456 -- IN
457 -- itemtype - A Valid item type from (WF_ITEM_TYPES Table).
458 -- itemkey - A string generated from application object's primary key.
459 -- actid - The function Activity
460 -- funcmode - Run / Cancel
461 --
462 -- OUT
463 -- resultout - Name of workflow process to run
464 --
465 -- Used By Activities
466 --
467 -- NOTES
468 --
469 --
470 -- HISTORY
471 -- 11/30/2000 sveerave@us created
472 -- End of Comments
473
474
475 PROCEDURE selector ( itemtype IN VARCHAR2
476 , itemkey IN VARCHAR2
477 , actid IN NUMBER
478 , funcmode IN VARCHAR2
479 , resultout OUT NOCOPY VARCHAR2
480 ) IS
481 L_API_NAME CONSTANT VARCHAR2(30) := 'SELECTOR';
482
483 l_return_status VARCHAR2(1);
484 l_object_id NUMBER;
485 l_object_type VARCHAR2(30);
486 BEGIN
487 l_object_id := WF_ENGINE.GetItemAttrNumber(
488 itemtype => itemtype
489 , itemkey => itemkey
490 , aname => 'OBJECT_ID'
491 );
492
493 l_object_type := WF_ENGINE.GetItemAttrText (
494 itemtype => itemtype
495 , itemkey => itemkey
496 , aname => 'OBJECT_TYPE'
497 );
498
499 -- dbms_output.put_line('In Selector Function');
500 --
501 -- RUN mode - normal process execution
502 --
503 IF (AMS_DEBUG_HIGH_ON) THEN
504 AMS_Utility_PVT.debug_message (L_API_NAME || ' - FUNCMODE: ' || funcmode);
505 END IF;
506
507 IF (funcmode = 'RUN') THEN
508 --
509 -- Return process to run
510 --
511 resultout := 'MODEL_BUILD_SCORE';
512 --
513 -- CANCEL mode -
514 --
515 ELSIF (funcmode = 'CANCEL') THEN
516 --
517 -- Return process to run
518 --
519 resultout := 'MODEL_BUILD_SCORE';
520 --
521 -- TIMEOUT mode
522 --
523 ELSIF (funcmode = 'TIMEOUT') THEN
524 resultout := 'MODEL_BUILD_SCORE';
525 END IF;
526
527 write_buffer_to_log (l_object_type, l_object_id);
528 EXCEPTION
529 WHEN OTHERS THEN
530 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
531 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
532 END IF;
533
534 write_buffer_to_log (l_object_type, l_object_id);
535
536 -- The line below records this function call in the error system
537 -- in the case of an exception.
538 wf_core.context (G_PKG_NAME, 'Selector', itemtype, itemkey, actid, funcmode);
539
540 RAISE;
541 END Selector;
542
543 -- Start of Comments
544 --
545 -- NAME
546 -- Validate
547 --
548 -- PURPOSE
549 -- This Procedure will aggregate sources based on user selections, and will return
550 -- Success or Failure
551 --
552 -- IN
553 -- Itemtype - AMSDMMOD
554 -- Itemkey - ObjectID+Time
555 -- Accid - Activity ID
556 -- Funmode - Run/Cancel/Timeout
557 --
558 -- OUT
559 -- Result - 'COMPLETE:T' If the validation is successfully completed
560 -- - 'COMPLETE:F' If there is an error in validation
561 --
562 -- Used By Activities
563 -- Item Type - AMSDMMOD
564 -- Activity - VALIDATE
565 --
566 -- NOTES
567 --
568 --
569 -- HISTORY
570 -- 02/27/2001 sveerave@us created
571 -- 30-Mar-2001 choang Added convert_timezone
572 -- 05-Jul-2001 choang Removed best_subtree
573 -- 03-Jun-2002 choang replaced target_group_type with data_source_id
574 -- End of Comments
575
576 PROCEDURE validate_data ( itemtype IN VARCHAR2
577 , itemkey IN VARCHAR2
578 , actid IN NUMBER
579 , funcmode IN VARCHAR2
580 , result OUT NOCOPY VARCHAR2
581 ) IS
582 L_API_NAME CONSTANT VARCHAR2(30) := 'VALIDATE_DATA';
583
584 CURSOR c_get_model_details(p_obj_id NUMBER) IS
585 SELECT model.model_type
586 , target.data_source_id
587 , target_positive_value
588 , model_name
589 , status_code
590 , owner_user_id
591 , target.active_flag
592 FROM ams_dm_models_vl model, ams_dm_targets_b target
593 WHERE model_id = p_obj_id
594 AND target.target_id = model.target_id
595 ;
596
597 CURSOR c_get_score_details(p_obj_id NUMBER) IS
598 SELECT model.model_type
599 , target.data_source_id
600 , model.target_positive_value
601 , score.score_name
602 , score.status_code
603 , score.owner_user_id
604 , target.active_flag
605 FROM ams_dm_models_all_b model, ams_dm_scores_vl score, ams_dm_targets_b target
606 WHERE score_id = p_obj_id
607 AND score.model_id = model.model_id
608 AND target.target_id = model.target_id
609 ;
610
611 l_model_type VARCHAR2(30);
612 l_data_source_id NUMBER;
613 l_target_positive_value NUMBER;
614 l_name VARCHAR2(120);
615 l_status_code VARCHAR2(30);
616 l_object_id NUMBER;
617 l_object_type VARCHAR2(30);
618 l_result_flag VARCHAR2(1) := 'T';
619
620 l_return_status VARCHAR2(1);
621 l_msg_count NUMBER;
622 l_msg_data VARCHAR2(4000);
623
624 l_owner_user_id NUMBER;
625 l_owner_role VARCHAR2(80);
626 l_owner_role_name VARCHAR2(120);
627 l_target_active_flag VARCHAR2(1);
628 BEGIN
629 l_object_id := WF_ENGINE.GetItemAttrNumber(
630 itemtype => itemtype
631 , itemkey => itemkey
632 , aname => 'OBJECT_ID'
633 );
634
635 l_object_type := WF_ENGINE.GetItemAttrText (
636 itemtype => itemtype
637 , itemkey => itemkey
638 , aname => 'OBJECT_TYPE'
639 );
640
641 IF (AMS_DEBUG_HIGH_ON) THEN
642 AMS_Utility_PVT.debug_message (L_API_NAME || ' - FUNCMODE: ' || funcmode);
643 END IF;
644
645 -- RUN mode - Normal Process Execution
646 IF (funcmode = 'RUN') THEN
647 IF (l_object_type = G_OBJECT_TYPE_MODEL ) THEN
648 OPEN c_get_model_details(l_object_id);
649 FETCH c_get_model_details INTO l_model_type
650 , l_data_source_id
651 , l_target_positive_value
652 , l_name
653 , l_status_code
654 , l_owner_user_id
655 , l_target_active_flag;
656
657 IF c_get_model_details%NOTFOUND THEN
658 IF (AMS_DEBUG_HIGH_ON) THEN
659 AMS_Utility_PVT.debug_message (L_API_NAME || ' - c_get_model_details%NOTFOUND');
660 END IF;
661 l_result_flag := 'F';
662 END IF;
663 CLOSE c_get_model_details;
664 ELSIF l_object_type = G_OBJECT_TYPE_SCORE THEN
665 OPEN c_get_score_details(l_object_id);
666 FETCH c_get_score_details INTO l_model_type
667 , l_data_source_id
668 , l_target_positive_value
669 , l_name
670 , l_status_code
671 , l_owner_user_id
672 , l_target_active_flag;
673
674 IF c_get_score_details%NOTFOUND THEN
675 IF (AMS_DEBUG_HIGH_ON) THEN
676 AMS_Utility_PVT.debug_message (L_API_NAME || ' - c_get_score_details%NOTFOUND');
677 END IF;
678 l_result_flag := 'F';
679 END IF;
680 CLOSE c_get_score_details;
681 ELSE
682
683 IF (AMS_DEBUG_HIGH_ON) THEN
684 AMS_Utility_PVT.debug_message (L_API_NAME || ' - OBJECT_TYPE: ' || l_object_type);
685 END IF;
686
687 l_result_flag := 'F';
688 END IF;
689
690 IF l_target_active_flag = 'N' THEN
691 IF (AMS_DEBUG_HIGH_ON) THEN
692 AMS_Utility_PVT.debug_message (L_API_NAME || ' - Cannot proceed, the target is inactive...');
693 END IF;
694 AMS_Utility_PVT.error_message ('AMS_MODEL_TARGET_DISABLED');
695 l_result_flag := 'F';
696 END IF;
697
698 IF (AMS_DEBUG_HIGH_ON) THEN
699 AMS_Utility_PVT.debug_message (L_API_NAME || ' - RESULT_FLAG: ' || l_result_flag);
700 END IF;
701
702 -- Set all derived attirbutes, True status if result flag is true otherwise set False status
703 IF (l_result_flag = 'F') THEN
704 result := 'COMPLETE:F';
705 ELSE
706
707 -- Setting up the role
708 Get_User_Role(p_user_id => l_owner_user_id ,
709 x_role_name => l_owner_role,
710 x_role_display_name => l_owner_role_name,
711 x_return_status => l_return_status);
712
713 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
714
715 result := 'COMPLETE:F';
716
717 IF (AMS_DEBUG_HIGH_ON) THEN
718 AMS_Utility_PVT.debug_message (L_API_NAME || ' - Get_User_Role Failed. Result= ' || result);
719 END IF;
720 ELSE
721
722 IF (AMS_DEBUG_HIGH_ON) THEN
723 AMS_Utility_PVT.debug_message (L_API_NAME || ' - Get_User_Role Succeeded. ' );
724 END IF;
725
726 WF_ENGINE.SetItemAttrText (
727 itemtype => itemtype
728 , itemkey => itemkey
729 , aname => 'OWNER_USERNAME'
730 , avalue => l_owner_role );
731
732
733 WF_ENGINE.SetItemAttrText(
734 itemtype => itemtype
735 , itemkey => itemkey
736 , aname => 'MODEL_TYPE'
737 , avalue => l_model_type
738 );
739 WF_ENGINE.SetItemAttrNumber(
740 itemtype => itemtype
741 , itemkey => itemkey
742 , aname => 'DATA_SOURCE_ID'
743 , avalue => l_data_source_id
744 );
745 WF_ENGINE.SetItemAttrNumber(
746 itemtype => itemtype
747 , itemkey => itemkey
748 , aname => 'TARGET_POSITIVE_VALUE'
749 , avalue => l_target_positive_value
750 );
751
752 WF_ENGINE.SetItemAttrText(
753 itemtype => itemtype
754 , itemkey => itemkey
755 , aname => 'NAME'
756 , avalue => l_name
757 );
758 /* we do not need to set the status as this is already
759 set in Initialize_var to BUILDING or SCORING as per OBJ_TYPE */
760 result := 'COMPLETE:T';
761 END IF;
762 END IF; -- IF (l_result_flag = 'F')
763 ELSIF (funcmode = 'CANCEL') THEN
764 result := 'COMPLETE' ;
765 -- TIMEOUT mode - Normal Process Execution
766 ELSIF (funcmode = 'TIMEOUT') THEN
767 result := 'COMPLETE' ;
768 --
769 -- Other execution modes may be created in the future. The following
770 -- activity will indicate that it does not implement a mode
771 -- by returning null
772 --
773 ELSE
774 result := '';
775 END IF;
776
777 IF (AMS_DEBUG_HIGH_ON) THEN
778 AMS_Utility_PVT.debug_message (L_API_NAME || ' - RESULT: ' || result);
779 END IF;
780
781 write_buffer_to_log (l_object_type, l_object_id);
782
783 EXCEPTION
784 -- The line below records this function call in the error system
785 -- in the case of an exception.
786 WHEN OTHERS THEN
787 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
788 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
789 END IF;
790
791 write_buffer_to_log (l_object_type, l_object_id);
792
793 wf_core.context(G_PKG_NAME,'Validate', itemtype,itemkey,to_char(actid),funcmode);
794
795 /* populate status to DRAFT */
796 WF_ENGINE.SetItemAttrText(
797 itemtype => itemtype
798 , itemkey => itemkey
799 , aname => 'STATUS_CODE'
800 , avalue => G_STATUS_DRAFT
801 );
802 result := 'COMPLETE:FAILURE' ;
803 END Validate_Data;
804
805
806 -- Start of Comments
807 --
808 -- NAME
809 -- Aggregate_sources
810 --
811 -- PURPOSE
812 -- This Procedure will aggregate sources based on user selections, and will return
813 -- Success or Failure
814 --
815 -- IN
816 -- Itemtype - AMSDMMOD
817 -- Itemkey - ObjectID+Time
818 -- Accid - Activity ID
819 -- Funmode - Run/Cancel/Timeout
820 --
821 -- OUT
822 -- Result - 'COMPLETE:SUCCESS' If the aggregation is successfully completed
823 -- - 'COMPLETE:FAILURE' If there is an error in aggregation
824 --
825 -- Used By Activities
826 -- Item Type - AMSDMMOD
827 -- Activity - AGGREGATE_SOURCES
828 --
829 -- NOTES
830 --
831 --
832 -- HISTORY
833 -- 11/30/2000 sveerave@us created
834 -- End of Comments
835
836 PROCEDURE Aggregate_Sources( itemtype IN VARCHAR2
837 , itemkey IN VARCHAR2
838 , actid IN NUMBER
839 , funcmode IN VARCHAR2
840 , result OUT NOCOPY VARCHAR2
841 ) IS
842 L_API_NAME CONSTANT VARCHAR2(30) := 'AGGREGATE_SOURCES';
843
844 l_return_status VARCHAR2(1);
845
846 l_msg_count NUMBER;
847 l_msg_data VARCHAR2(2000);
848 l_message VARCHAR2(4000);
849 l_object_id NUMBER;
850 l_object_type VARCHAR2(30);
851 BEGIN
852 l_object_id := WF_ENGINE.GetItemAttrNumber(
853 itemtype => itemtype
854 , itemkey => itemkey
855 , aname => 'OBJECT_ID'
856 );
857
858 l_object_type := WF_ENGINE.GetItemAttrText (
859 itemtype => itemtype
860 , itemkey => itemkey
861 , aname => 'OBJECT_TYPE'
862 );
863
864 AMS_Utility_PVT.create_log (
865 x_return_status => l_return_status,
866 p_arc_log_used_by => l_object_type,
867 p_log_used_by_id => l_object_id,
868 p_msg_data => L_API_NAME || ': begin'
869 );
870
871 AMS_Utility_PVT.create_log (
872 x_return_status => l_return_status,
873 p_arc_log_used_by => l_object_type,
874 p_log_used_by_id => l_object_id,
875 p_msg_data => L_API_NAME || ' - FUNCMODE: ' || funcmode
876 );
877
878
879 -- RUN mode - Normal Process Execution
880 IF (funcmode = 'RUN') THEN
881 AMS_DMSelection_PVT.Aggregate_Selections (
882 p_arc_object => l_object_type
883 , p_object_id => l_object_id
884 , x_return_status => l_return_status
885 );
886 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
887 result := 'COMPLETE:SUCCESS' ;
888 ELSE
889 /* populate error message */
890 WF_ENGINE.SetItemAttrText(
891 itemtype => itemtype
892 , itemkey => itemkey
893 , aname => 'AGGR_ERR_MESSAGE'
894 , avalue => l_message
895 );
896 /* populate status to DRAFT */
897 WF_ENGINE.SetItemAttrText(
898 itemtype => itemtype
899 , itemkey => itemkey
900 , aname => 'STATUS_CODE'
901 , avalue => G_STATUS_DRAFT
902 );
903 result := 'COMPLETE:FAILURE' ;
904 END IF;
905 -- CANCEL mode - Normal Process Execution
906 ELSIF (funcmode = 'CANCEL') THEN
907 result := 'COMPLETE' ;
908 -- TIMEOUT mode - Normal Process Execution
909 ELSIF (funcmode = 'TIMEOUT') THEN
910 result := 'COMPLETE' ;
911 --
912 -- Other execution modes may be created in the future. The following
913 -- activity will indicate that it does not implement a mode
914 -- by returning null
915 --
916 ELSE
917 result := '';
918 END IF;
919
920 IF (AMS_DEBUG_HIGH_ON) THEN
921 AMS_Utility_PVT.debug_message (L_API_NAME || ' - RESULT: ' || result);
922 END IF;
923
924 write_buffer_to_log (l_object_type, l_object_id);
925
926 AMS_Utility_PVT.create_log (
927 x_return_status => l_return_status,
928 p_arc_log_used_by => l_object_type,
929 p_log_used_by_id => l_object_id,
930 p_msg_data => L_API_NAME || ': end'
931 );
932 EXCEPTION
933 WHEN OTHERS THEN
934 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
935 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
936 END IF;
937
938 write_buffer_to_log (l_object_type, l_object_id);
939
940 AMS_Utility_PVT.create_log (
941 x_return_status => l_return_status,
942 p_arc_log_used_by => l_object_type,
943 p_log_used_by_id => l_object_id,
944 p_msg_data => L_API_NAME || ': EXCEPTION BUFFER WRITTEN'
945 );
946 /* populate status to DRAFT */
947 WF_ENGINE.SetItemAttrText(
948 itemtype => itemtype
949 , itemkey => itemkey
950 , aname => 'STATUS_CODE'
951 , avalue => G_STATUS_DRAFT
952 );
953 result := 'COMPLETE:FAILURE' ;
954
955 -- The line below records this function call in the error system
956 -- in the case of an exception.
957 -- wf_core.context(G_PKG_NAME,'Aggregate_Sources',itemtype,itemkey,to_char(actid),funcmode);
958 END Aggregate_Sources ;
959
960 -- Start of Comments
961 --
962 -- NAME
963 -- Transform
964 --
965 -- PURPOSE
966 -- This Procedure will transform the untransformed data, and will return
967 -- Success or Failure
968 --
969 -- IN
970 -- Itemtype - AMSDMMOD
971 -- Itemkey - ObjectID+Time
972 -- Accid - Activity ID
973 -- Funmode - Run/Cancel/Timeout
974 --
975 -- OUT
976 -- Result - 'COMPLETE:SUCCESS' If the transformation is successfully completed
977 -- - 'COMPLETE:FAILURE' If there is an error in transformation
978 --
979 -- Used By Activities
980 -- Item Type - AMSDMMOD
981 -- Activity - TRANSFORM
982 --
983 -- NOTES
984 --
985 --
986 -- HISTORY
987 -- 30-Nov-2000 sveerave@us created
988 -- 03-Jun-2002 choang changed call to generate_odm_input_views to use data_source_id
989 --
990 -- End of Comments
991
992 PROCEDURE Transform( itemtype IN VARCHAR2
993 , itemkey IN VARCHAR2
994 , actid IN NUMBER
995 , funcmode IN VARCHAR2
996 , result OUT NOCOPY VARCHAR2
997 ) IS
998 L_API_NAME CONSTANT VARCHAR2(30) := 'TRANSFORM';
999 L_SEEDED_ID_THRESHOLD CONSTANT NUMBER := 10000;
1000
1001 l_return_status VARCHAR2(1);
1002 l_log_return_status VARCHAR2(1);
1003
1004 l_object_id NUMBER;
1005 l_object_type VARCHAR2(30);
1006 l_data_source_id NUMBER;
1007 l_api_version NUMBER := 1.0;
1008 l_init_msg_list VARCHAR2(1) := FND_API.g_false;
1009 l_commit VARCHAR2(1) := FND_API.g_true;
1010 l_validation_level NUMBER := 100;
1011 l_msg_count NUMBER;
1012 l_msg_data VARCHAR2(2000);
1013 l_message VARCHAR2(4000);
1014
1015 l_seeded_data_source BOOLEAN := FALSE;
1016
1017 CURSOR c_target_id_model (p_model_id IN NUMBER) IS
1018 SELECT target_id
1019 FROM ams_dm_models_v
1020 WHERE model_id = p_model_id
1021 ;
1022
1023 CURSOR c_target_id_score (p_score_id IN NUMBER) IS
1024 SELECT model.target_id
1025 FROM ams_dm_models_v model , ams_dm_scores_v score
1026 WHERE score.score_id = p_score_id
1027 AND score.model_id = model.model_id
1028 ;
1029
1030 l_target_id NUMBER;
1031
1032 BEGIN
1033 l_object_id := WF_ENGINE.GetItemAttrNumber(
1034 itemtype => itemtype
1035 , itemkey => itemkey
1036 , aname => 'OBJECT_ID'
1037 );
1038 l_object_type := WF_ENGINE.GetItemAttrText (
1039 itemtype => itemtype
1040 , itemkey => itemkey
1041 , aname => 'OBJECT_TYPE'
1042 );
1043
1044 AMS_Utility_PVT.create_log (
1045 x_return_status => l_log_return_status,
1046 p_arc_log_used_by => l_object_type,
1047 p_log_used_by_id => l_object_id,
1048 p_msg_data => L_API_NAME || ': begin'
1049 );
1050
1051 AMS_Utility_PVT.create_log (
1052 x_return_status => l_log_return_status,
1053 p_arc_log_used_by => l_object_type,
1054 p_log_used_by_id => l_object_id,
1055 p_msg_data => L_API_NAME || ' - FUNCMODE: ' || funcmode
1056 );
1057
1058 l_data_source_id := WF_ENGINE.GetItemAttrNumber (
1059 itemtype => itemtype
1060 , itemkey => itemkey
1061 , aname => 'DATA_SOURCE_ID'
1062 );
1063
1064 -- start changes rosharma for audience data sources uptake
1065 /*IF l_data_source_id < L_SEEDED_ID_THRESHOLD THEN
1066 l_seeded_data_source := TRUE;
1067 END IF;*/
1068
1069 IF l_object_type = 'MODL' THEN
1070 OPEN c_target_id_model(l_object_id);
1071 FETCH c_target_id_model INTO l_target_id;
1072 CLOSE c_target_id_model;
1073 ELSE
1074 OPEN c_target_id_score(l_object_id);
1075 FETCH c_target_id_score INTO l_target_id;
1076 CLOSE c_target_id_score;
1077 END IF;
1078
1079 IF l_target_id < L_SEEDED_ID_THRESHOLD THEN
1080 l_seeded_data_source := TRUE;
1081 END IF;
1082 -- end changes rosharma for audience data sources uptake
1083
1084 -- RUN mode - Normal Process Execution
1085 IF (funcmode = 'RUN') THEN
1086
1087 AMS_DMSource_PVT.generate_odm_input_views (
1088 p_api_version => 2.0
1089 , p_init_msg_list => l_init_msg_list
1090 , p_object_type => l_object_type
1091 , p_object_id => l_object_id
1092 , p_data_source_id => l_data_source_id
1093 , x_return_status => l_return_status
1094 , x_msg_count => l_msg_count
1095 , x_msg_data => l_msg_data
1096 );
1097
1098 AMS_Utility_PVT.create_log (
1099 x_return_status => l_log_return_status,
1100 p_arc_log_used_by => l_object_type,
1101 p_log_used_by_id => l_object_id,
1102 p_msg_data => ' After generate_odm_input_views status ' || l_return_status
1103 );
1104
1105 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1106 result := 'COMPLETE:FAILURE' ;
1107
1108 /* populate error message */
1109 WF_ENGINE.SetItemAttrText(
1110 itemtype => itemtype
1111 , itemkey => itemkey
1112 , aname => 'TRAN_ERR_MESSAGE'
1113 , avalue => l_message
1114 );
1115 /* populate status to DRAFT */
1116 WF_ENGINE.SetItemAttrText(
1117 itemtype => itemtype
1118 , itemkey => itemkey
1119 , aname => 'STATUS_CODE'
1120 , avalue => G_STATUS_DRAFT
1121 );
1122 ELSE
1123 result := 'COMPLETE:SUCCESS' ;
1124 IF l_seeded_data_source THEN
1125
1126 -- only need to perform data extraction for seeded data sources
1127 AMS_DMExtract_pvt.ExtractMain (
1128 p_api_version => l_api_version
1129 , p_init_msg_list => l_init_msg_list
1130 , p_commit => l_commit
1131 , x_return_status => l_return_status
1132 , x_msg_count => l_msg_count
1133 , x_msg_data => l_msg_data
1134 , p_mode => 'I'
1135 , p_model_id => l_object_id
1136 , p_model_type => l_object_type
1137 );
1138 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1139 result := 'COMPLETE:SUCCESS' ;
1140 ELSE
1141 /* populate error message */
1142 WF_ENGINE.SetItemAttrText(
1143 itemtype => itemtype
1144 , itemkey => itemkey
1145 , aname => 'TRAN_ERR_MESSAGE'
1146 , avalue => l_msg_data
1147 );
1148 /* populate status to DRAFT */
1149 WF_ENGINE.SetItemAttrText(
1150 itemtype => itemtype
1151 , itemkey => itemkey
1152 , aname => 'STATUS_CODE'
1153 , avalue => G_STATUS_DRAFT
1154 );
1155 result := 'COMPLETE:FAILURE' ;
1156 END IF;
1157 END IF; -- IF l_seeded_data_source THEN
1158 END IF; -- IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1159 -- CANCEL mode - Normal Process Execution
1160 ELSIF (funcmode = 'CANCEL') THEN
1161 result := 'COMPLETE' ;
1162 -- TIMEOUT mode - Normal Process Execution
1163 ELSIF (funcmode = 'TIMEOUT') THEN
1164 result := 'COMPLETE' ;
1165 --
1166 -- Other execution modes may be created in the future. The following
1167 -- activity will indicate that it does not implement a mode
1168 -- by returning null
1169 --
1170 ELSE
1171 result := '';
1172 END IF;
1173
1174 write_buffer_to_log (l_object_type, l_object_id);
1175
1176 AMS_Utility_PVT.create_log (
1177 x_return_status => l_log_return_status,
1178 p_arc_log_used_by => l_object_type,
1179 p_log_used_by_id => l_object_id,
1180 p_msg_data => L_API_NAME || ' - RESULT: ' || result
1181 );
1182
1183 AMS_Utility_PVT.create_log (
1184 x_return_status => l_log_return_status,
1185 p_arc_log_used_by => l_object_type,
1186 p_log_used_by_id => l_object_id,
1187 p_msg_data => L_API_NAME || ': end'
1188 );
1189 EXCEPTION
1190 -- The line below records this function call in the error system
1191 -- in the case of an exception.
1192 WHEN OTHERS THEN
1193 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1194 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1195 END IF;
1196
1197 write_buffer_to_log (l_object_type, l_object_id);
1198
1199 wf_core.context(G_PKG_NAME,'Transform',itemtype,itemkey,to_char(actid),funcmode);
1200
1201 /* populate status to DRAFT */
1202 WF_ENGINE.SetItemAttrText(
1203 itemtype => itemtype
1204 , itemkey => itemkey
1205 , aname => 'STATUS_CODE'
1206 , avalue => G_STATUS_DRAFT
1207 );
1208 result := 'COMPLETE:FAILURE' ;
1209 END Transform ;
1210
1211 -- Start of Comments
1212 --
1213 -- NAME
1214 -- Command
1215 --
1216 -- PURPOSE
1217 -- This Procedure will request for data mining by posting a request to AQ, and will return
1218 -- Success or Failure
1219 --
1220 -- IN
1221 -- Itemtype - AMSDMMOD
1222 -- Itemkey - ObjectID+Time
1223 -- Accid - Activity ID
1224 -- Funmode - Run/Cancel/Timeout
1225 -- OUT
1226 -- Result - 'COMPLETE:SUCCESS' If sumbmitting aq request is successfully completed
1227 -- - 'COMPLETE:FAILURE' If there is an error in submitting the aq request
1228 --
1229 -- Used By Activities
1230 -- Item Type - AMSDMMOD
1231 -- Activity - COMMAND
1232 --
1233 -- NOTES
1234 --
1235 --
1236 -- HISTORY
1237 -- 11/30/2000 sveerave@us created
1238 -- End of Comments
1239
1240 PROCEDURE Command( itemtype IN VARCHAR2
1241 , itemkey IN VARCHAR2
1242 , actid IN NUMBER
1243 , funcmode IN VARCHAR2
1244 , result OUT NOCOPY VARCHAR2
1245 ) IS
1246 L_API_NAME CONSTANT VARCHAR2(30) := 'COMMAND';
1247 L_APPLICATION_NAME CONSTANT VARCHAR2(30) := 'AMS';
1248 L_SCORE_PROGRAM CONSTANT VARCHAR2(30) := 'AMS_JCP_MODEL_APPLY';
1249 L_BUILD_PROGRAM CONSTANT VARCHAR2(30) := 'AMS_JCP_MODEL_BUILD';
1250
1251 l_object_id NUMBER;
1252 l_object_type VARCHAR2(30);
1253 l_model_type VARCHAR2(30);
1254 l_model_id NUMBER;
1255 l_target_group VARCHAR2(30);
1256 l_request_type VARCHAR2(30);
1257 l_select_list VARCHAR2(2000);
1258
1259 -- concurrent processing variables
1260 l_call_status BOOLEAN;
1261 l_request_id NUMBER;
1262 l_phase VARCHAR2(80);
1263 l_status VARCHAR2(80);
1264 l_dev_phase VARCHAR2(30);
1265 l_dev_status VARCHAR2(30);
1266 l_message VARCHAR2(2000);
1267
1268 l_return_status VARCHAR2(1);
1269 l_msg_data VARCHAR2 (2000);
1270
1271 l_sysdate DATE := SYSDATE;
1272 l_target_positive_value NUMBER;
1273
1274 CURSOR c_model (p_score_id IN NUMBER) IS
1275 SELECT model_id
1276 FROM ams_dm_scores_all_b
1277 WHERE score_id = p_score_id;
1278 BEGIN
1279 l_object_id := WF_ENGINE.GetItemAttrNumber(
1280 itemtype => itemtype
1281 , itemkey => itemkey
1282 , aname => 'OBJECT_ID'
1283 );
1284 l_object_type := WF_ENGINE.GetItemAttrText (
1285 itemtype => itemtype
1286 , itemkey => itemkey
1287 , aname => 'OBJECT_TYPE'
1288 );
1289
1290 AMS_Utility_PVT.create_log (
1291 x_return_status => l_return_status,
1292 p_arc_log_used_by => l_object_type,
1293 p_log_used_by_id => l_object_id,
1294 p_msg_data => L_API_NAME || ': begin'
1295 );
1296
1297 IF (AMS_DEBUG_HIGH_ON) THEN
1298 AMS_Utility_PVT.debug_message (L_API_NAME || ' - FUNCMODE: ' || funcmode);
1299 END IF;
1300
1301 -- RUN mode - Normal Process Execution
1302 IF (funcmode = 'RUN') THEN
1303 l_model_type := WF_ENGINE.GetItemAttrText(
1304 itemtype => itemtype
1305 , itemkey => itemkey
1306 , aname => 'MODEL_TYPE'
1307 );
1308 l_target_group := WF_ENGINE.GetItemAttrNumber(
1309 itemtype => itemtype
1310 , itemkey => itemkey
1311 , aname => 'DATA_SOURCE_ID'
1312 );
1313 l_request_type := WF_ENGINE.GetItemAttrText (
1314 itemtype => itemtype
1315 , itemkey => itemkey
1316 , aname => 'REQUEST_TYPE'
1317 );
1318 l_select_list := WF_ENGINE.GetItemAttrText(
1319 itemtype => itemtype
1320 , itemkey => itemkey
1321 , aname => 'SELECT_LIST'
1322 );
1323
1324 l_target_positive_value := WF_ENGINE.GetItemAttrNumber(
1325 itemtype => itemtype
1326 , itemkey => itemkey
1327 , aname => 'TARGET_POSITIVE_VALUE'
1328 );
1329
1330 IF l_object_type = G_OBJECT_TYPE_MODEL THEN
1331 l_request_id := fnd_request.submit_request (
1332 application => L_APPLICATION_NAME,
1333 program => L_BUILD_PROGRAM,
1334 start_time => l_sysdate,
1335 argument1 => l_object_id,
1336 argument2 => l_target_positive_value,
1337 argument3 => l_target_group
1338 );
1339 ELSE
1340 OPEN c_model (l_object_id);
1341 FETCH c_model INTO l_model_id;
1342 CLOSE c_model;
1343
1344 l_request_id := fnd_request.submit_request (
1345 application => L_APPLICATION_NAME,
1346 program => L_SCORE_PROGRAM,
1347 start_time => l_sysdate,
1348 argument1 => l_model_id,
1349 argument2 => l_object_id,
1350 argument3 => l_target_group
1351 );
1352 END IF;
1353
1354 IF (AMS_DEBUG_HIGH_ON) THEN
1355 AMS_Utility_PVT.debug_message (L_API_NAME || ' - REQUEST_ID: ' || l_request_id);
1356 END IF;
1357
1358 IF l_request_id <> 0 THEN
1359 WF_ENGINE.SetItemAttrNumber(
1360 itemtype => itemtype
1361 , itemkey => itemkey
1362 , aname => 'REQUEST_ID'
1363 , avalue => l_request_id
1364 );
1365
1366 -- need to do a commit after submit_request
1367 -- so concurrent manager will see the request,
1368 -- otherwise, the process will hang.
1369 COMMIT;
1370
1371 AMS_Utility_PVT.create_log (
1372 x_return_status => l_return_status,
1373 p_arc_log_used_by => l_object_type,
1374 p_log_used_by_id => l_object_id,
1375 p_msg_data => L_API_NAME || ': REQUEST_ID - ' || l_request_id
1376 );
1377
1378 -- wait for the request to complete
1379 l_call_status := fnd_concurrent.wait_for_request (
1380 request_id => l_request_id,
1381 phase => l_phase,
1382 status => l_status,
1383 dev_phase => l_dev_phase,
1384 dev_status => l_dev_status,
1385 message => l_message
1386 );
1387
1388 IF l_dev_status = 'NORMAL' THEN
1389 result := 'COMPLETE:SUCCESS' ;
1390 ELSE
1391 AMS_Utility_PVT.create_log (
1392 x_return_status => l_return_status,
1393 p_arc_log_used_by => l_object_type,
1394 p_log_used_by_id => l_object_id,
1395 p_msg_data => L_API_NAME || ' - CONCURRENT REQUEST ERROR: ' || l_message
1396 );
1397 -- AMS_Utility_PVT.error_message ( L_API_NAME || '- CONCURRENT REQUEST ERROR: ' || l_message);
1398
1399 /* populate status to DRAFT */
1400 WF_ENGINE.SetItemAttrText(
1401 itemtype => itemtype
1402 , itemkey => itemkey
1403 , aname => 'STATUS_CODE'
1404 , avalue => G_STATUS_DRAFT
1405 );
1406 result := 'COMPLETE:FAILURE' ;
1407 END IF;
1408 ELSE
1409 AMS_Utility_PVT.create_log (
1410 x_return_status => l_return_status,
1411 p_arc_log_used_by => l_object_type,
1412 p_log_used_by_id => l_object_id,
1413 p_msg_data => L_API_NAME || '- NO CONCURRENT REQUEST '
1414 );
1415 -- AMS_Utility_PVT.error_message ( L_API_NAME || ' - NO CONCURRENT REQUEST');
1416
1417 /* populate status to DRAFT */
1418 WF_ENGINE.SetItemAttrText(
1419 itemtype => itemtype
1420 , itemkey => itemkey
1421 , aname => 'STATUS_CODE'
1422 , avalue => G_STATUS_DRAFT
1423 );
1424 result := 'COMPLETE:FAILURE' ;
1425 END IF; -- IF NVL(l_return_status,'N') = 'Y'
1426 -- CANCEL mode - Normal Process Execution
1427 ELSIF (funcmode = 'CANCEL') THEN
1428 result := 'COMPLETE' ;
1429 -- TIMEOUT mode - Normal Process Execution
1430 ELSIF (funcmode = 'TIMEOUT') THEN
1431 result := 'COMPLETE' ;
1432 --
1433 -- Other execution modes may be created in the future. The following
1434 -- activity will indicate that it does not implement a mode
1435 -- by returning null
1436 --
1437 ELSE
1438 result := '';
1439 END IF;
1440
1441 IF (AMS_DEBUG_HIGH_ON) THEN
1442 AMS_Utility_PVT.debug_message (L_API_NAME || ' - RESULT: ' || result);
1443 END IF;
1444
1445 write_buffer_to_log (l_object_type, l_object_id);
1446
1447 AMS_Utility_PVT.create_log (
1448 x_return_status => l_return_status,
1449 p_arc_log_used_by => l_object_type,
1450 p_log_used_by_id => l_object_id,
1451 p_msg_data => L_API_NAME || ': end'
1452 );
1453 EXCEPTION
1454 -- The line below records this function call in the error system
1455 -- in the case of an exception.
1456 WHEN OTHERS THEN
1457 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1458 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1459 END IF;
1460
1461 write_buffer_to_log (l_object_type, l_object_id);
1462
1463 wf_core.context(G_PKG_NAME,'Command',itemtype,itemkey,to_char(actid),funcmode);
1464
1465 /* populate status to DRAFT */
1466 WF_ENGINE.SetItemAttrText(
1467 itemtype => itemtype
1468 , itemkey => itemkey
1469 , aname => 'STATUS_CODE'
1470 , avalue => G_STATUS_DRAFT
1471 );
1472 result := 'COMPLETE:FAILURE' ;
1473 END Command ;
1474
1475 -- Start of Comments
1476 --
1477 -- NAME
1478 -- Check_response
1479 --
1480 -- PURPOSE
1481 -- This Procedure will poll AQ to check whether there is any message
1482 -- awaiting from Darwin after model is built/scored,
1483 -- and will return:
1484
1485 -- ERROR (Successfully polled AQ with a message waiting, but the message is an Error message from mining application to build/score a model)
1486 -- FAILURE (Failed to poll AQ for a message due to AQ system failure)
1487 -- NO (Sucessfully polled AQ, but there are no messages waiting in the queue.)
1488 -- YES (Sucessfully polled AQ with a message waiting, and also there are no errors from mining application (Darwin))
1489
1490 -- IN
1491 -- Itemtype - AMSDMMOD
1492 -- Itemkey - ObjectID+Time
1493 -- Accid - Activity ID
1494 -- Funmode - Run/Cancel/Timeout
1495 --
1496 -- OUT
1497 -- Result - 'COMPLETE:YES' If there is message awaiting from Darwin about model building/scoring.
1498 -- 'COMPLETE:NO' If there is no message awaiting from Darwin about model building/scoring.
1499 -- 'COMPLETE:ERROR' If there is error message from Darwin'
1500 -- 'COMPLETE:FAILURE' If it could not poll aq due to aq system failure
1501
1502 --
1503 -- Used By Activities
1504 -- Item Type - AMSDMMOD
1505 -- Activity - CHECK_RESPONSE
1506 --
1507 -- NOTES
1508 --
1509 --
1510 -- HISTORY
1511 -- 11/30/2000 sveerave@us created
1512 -- 03-Jul-2001 choang Remove AQ dependency.
1513 -- End of Comments
1514
1515 PROCEDURE Check_Response( itemtype IN VARCHAR2
1516 , itemkey IN VARCHAR2
1517 , actid IN NUMBER
1518 , funcmode IN VARCHAR2
1519 , result OUT NOCOPY VARCHAR2
1520 ) IS
1521 L_API_NAME CONSTANT VARCHAR2(30) := 'CHECK_RESPONSE';
1522
1523 l_object_id NUMBER;
1524 l_object_type VARCHAR2(30);
1525 l_request_type VARCHAR2(30);
1526 l_status VARCHAR2(30);
1527 l_message VARCHAR2(2000);
1528 l_model_type VARCHAR2(30);
1529
1530 -- Use l_return_status_log to get status
1531 -- for calls to create_log because the
1532 -- status codes that are used for workflow
1533 -- processing are different from those
1534 -- returned by the create_log api.
1535 l_return_status_log VARCHAR2(1);
1536 l_return_status VARCHAR2(1);
1537
1538 l_msg_count NUMBER;
1539 l_msg_data VARCHAR2 (2000);
1540
1541 BEGIN
1542 /***
1543 l_object_id := WF_ENGINE.GetItemAttrNumber(
1544 itemtype => itemtype
1545 , itemkey => itemkey
1546 , aname => 'OBJECT_ID'
1547 );
1548 l_object_type := WF_ENGINE.GetItemAttrText (
1549 itemtype => itemtype
1550 , itemkey => itemkey
1551 , aname => 'OBJECT_TYPE'
1552 );
1553
1554 IF (AMS_DEBUG_HIGH_ON) THEN
1555
1556
1557
1558 AMS_Utility_PVT.debug_message (L_API_NAME || ' - FUNCMODE: ' || funcmode);
1559
1560 END IF;
1561 -- RUN mode - Normal Process Execution
1562 IF (funcmode = 'RUN') THEN
1563 AMS_DM_AQ_PVT.dequeue_dm_response(
1564 p_msg_for_object_id => l_object_id
1565 , p_msg_for_object_type => l_object_type
1566 , x_object_id => l_object_id
1567 , x_object_type => l_object_type
1568 , x_request_type => l_request_type
1569 , x_status => l_status
1570 , x_message => l_message
1571 , x_return_status => l_return_status
1572 , x_msg_data => l_msg_data
1573 );
1574 IF (AMS_DEBUG_HIGH_ON) THEN
1575
1576 AMS_Utility_PVT.debug_message (L_API_NAME || ' - RETURN_STATUS: ' || l_return_status);
1577 END IF;
1578 IF NVL(l_return_status,'E') = 'Y' THEN
1579 result := 'COMPLETE:YES' ;
1580 IF (AMS_DEBUG_HIGH_ON) THEN
1581
1582 AMS_Utility_PVT.debug_message (L_API_NAME || ' - AQ MESG: ' || l_message);
1583 END IF;
1584 ELSIF NVL(l_return_status,'E') = 'N' THEN
1585 result := 'COMPLETE:NO' ;
1586 WF_ENGINE.SetItemAttrText(
1587 itemtype => itemtype
1588 , itemkey => itemkey
1589 , aname => 'AQ_ERR_MESSAGE'
1590 , avalue => l_msg_data
1591 );
1592 WF_ENGINE.SetItemAttrText(
1593 itemtype => itemtype
1594 , itemkey => itemkey
1595 , aname => 'AQ_OPERATION'
1596 , avalue => 'Polling from Response'
1597 );
1598 ELSIF NVL(l_return_status,'E') = 'F' THEN
1599 result := 'COMPLETE:FAILURE' ;
1600 WF_ENGINE.SetItemAttrText(
1601 itemtype => itemtype
1602 , itemkey => itemkey
1603 , aname => 'RESP_ERR_MESSAGE'
1604 , avalue => l_msg_data
1605 );
1606 ELSIF NVL(l_return_status,'E') = 'E' THEN
1607 result := 'COMPLETE:ERROR' ;
1608 WF_ENGINE.SetItemAttrText(
1609 itemtype => itemtype
1610 , itemkey => itemkey
1611 , aname => 'RESP_ERR_MESSAGE'
1612 , avalue => l_message
1613 );
1614 WF_ENGINE.SetItemAttrText(
1615 itemtype => itemtype
1616 , itemkey => itemkey
1617 , aname => 'STATUS_CODE'
1618 , avalue => G_STATUS_DRAFT
1619 );
1620 AMS_Utility_PVT.error_message ('AMS_DM_DDML_ERROR', 'ERROR_MESSAGE', l_message);
1621 END IF; --IF NVL(l_return_status,'E') = 'Y' THEN
1622 -- CANCEL mode - Normal Process Execution
1623 ELSIF (funcmode = 'CANCEL') THEN
1624 result := 'COMPLETE' ;
1625 -- TIMEOUT mode - Normal Process Execution
1626 ELSIF (funcmode = 'TIMEOUT') THEN
1627 result := 'COMPLETE' ;
1628 --
1629 -- Other execution modes may be created in the future. The following
1630 -- activity will indicate that it does not implement a mode
1631 -- by returning null
1632 --
1633 ELSE
1634 result := '';
1635 END IF;
1636
1637 IF (AMS_DEBUG_HIGH_ON) THEN
1638
1639
1640
1641 AMS_Utility_PVT.debug_message (L_API_NAME || ' - RESULT: ' || result);
1642
1643 END IF;
1644
1645 write_buffer_to_log (l_object_type, l_object_id);
1646 ***/
1647 null;
1648 EXCEPTION
1649 -- The line below records this function call in the error system
1650 -- in the case of an exception.
1651 WHEN OTHERS THEN
1652 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1653 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1654 END IF;
1655
1656 write_buffer_to_log (l_object_type, l_object_id);
1657
1658 wf_core.context(G_PKG_NAME,'Check_Response',itemtype,itemkey,to_char(actid),funcmode);
1659 raise ;
1660 END Check_Response ;
1661
1662 -- Start of Comments
1663 --
1664 -- NAME
1665 -- Collect_Results
1666 --
1667 -- PURPOSE
1668 -- This Procedure will collect results once the model is built or scored by Darwin, and will return
1669 -- Success or Failure
1670 --
1671 -- IN
1672 -- Itemtype - AMSDMMOD
1673 -- Itemkey - ObjectID+Time
1674 -- Accid - Activity ID
1675 -- Funmode - Run/Cancel/Timeout
1676 --
1677 -- OUT
1678 -- Result - 'COMPLETE:SUCCESS' If the collect results is successfully completed
1679 -- - 'COMPLETE:FAILURE' If there is an error in collect results
1680 --
1681 -- Used By Activities
1682 -- Item Type - AMSDMMOD
1683 -- Activity - COLLECT_RESULTS
1684 --
1685 -- NOTES
1686 --
1687 --
1688 -- HISTORY
1689 -- 11/30/2000 sveerave@us created
1690 -- End of Comments
1691
1692 PROCEDURE Collect_Results( itemtype IN VARCHAR2
1693 , itemkey IN VARCHAR2
1694 , actid IN NUMBER
1695 , funcmode IN VARCHAR2
1696 , result OUT NOCOPY VARCHAR2
1697 ) IS
1698 L_API_NAME CONSTANT VARCHAR2(30) := 'COLLECT_RESULTS';
1699
1700 l_return_status VARCHAR2(1);
1701
1702 l_object_id NUMBER;
1703 l_object_type VARCHAR2(30);
1704 l_api_version NUMBER := 1.0;
1705 l_init_msg_list VARCHAR2(1) := FND_API.g_true;
1706 l_commit VARCHAR2(1) := FND_API.g_true;
1707 l_validation_level NUMBER := 100;
1708 l_msg_count NUMBER;
1709 l_msg_data VARCHAR2(2000);
1710 l_message VARCHAR2(4000);
1711
1712
1713 BEGIN
1714 l_object_id := WF_ENGINE.GetItemAttrNumber(
1715 itemtype => itemtype
1716 , itemkey => itemkey
1717 , aname => 'OBJECT_ID'
1718 );
1719 l_object_type := WF_ENGINE.GetItemAttrText (
1720 itemtype => itemtype
1721 , itemkey => itemkey
1722 , aname => 'OBJECT_TYPE'
1723 );
1724
1725 AMS_Utility_PVT.create_log (
1726 x_return_status => l_return_status,
1727 p_arc_log_used_by => l_object_type,
1728 p_log_used_by_id => l_object_id,
1729 p_msg_data => L_API_NAME || ': begin'
1730 );
1731
1732 IF (AMS_DEBUG_HIGH_ON) THEN
1733 AMS_Utility_PVT.debug_message (L_API_NAME || ' - FUNCMODE: ' || funcmode);
1734 END IF;
1735
1736 -- RUN mode - Normal Process Execution
1737 IF (funcmode = 'RUN') THEN
1738
1739 -- Call cleanup_odm_input_views to drop the input data Views
1740 -- and the synonyms created for them in the ODM schema
1741 AMS_DMSource_PVT.cleanup_odm_input_views(
1742 p_object_type => l_object_type,
1743 p_object_id => l_object_id
1744 );
1745
1746 IF l_object_type = G_OBJECT_TYPE_MODEL THEN
1747 result := 'COMPLETE:SUCCESS' ;
1748 /* populate status to AVAILABLE */
1749 WF_ENGINE.SetItemAttrText(
1750 itemtype => itemtype
1751 , itemkey => itemkey
1752 , aname => 'STATUS_CODE'
1753 , avalue => G_STATUS_AVAILABLE
1754 );
1755 ELSIF l_object_type = G_OBJECT_TYPE_SCORE THEN
1756 AMS_DMSource_PVT.process_scores (
1757 p_api_version => l_api_version
1758 , p_init_msg_list => l_init_msg_list
1759 , p_commit => l_commit
1760 , x_return_status => l_return_status
1761 , x_msg_count => l_msg_count
1762 , x_msg_data => l_msg_data
1763 , p_score_id => l_object_id
1764 );
1765 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1766 result := 'COMPLETE:FAILURE' ;
1767
1768 AMS_Utility_PVT.create_log (
1769 x_return_status => l_return_status,
1770 p_arc_log_used_by => l_object_type,
1771 p_log_used_by_id => l_object_id,
1772 p_msg_data => 'L_API_NAME: AMS_DMSource_PVT.process_scores Failed'
1773 );
1774
1775
1776 /* populate error message */
1777 WF_ENGINE.SetItemAttrText(
1778 itemtype => itemtype
1779 , itemkey => itemkey
1780 , aname => 'COLL_ERR_MESSAGE'
1781 , avalue => l_msg_data
1782 );
1783 /* populate status to DRAFT */
1784 WF_ENGINE.SetItemAttrText(
1785 itemtype => itemtype
1786 , itemkey => itemkey
1787 , aname => 'STATUS_CODE'
1788 , avalue => G_STATUS_DRAFT
1789 );
1790
1791 write_buffer_to_log (l_object_type, l_object_id);
1792 RETURN;
1793 END IF;
1794
1795
1796 AMS_DMSource_PVT.bin_probability (
1797 p_api_version => l_api_version
1798 , p_init_msg_list => l_init_msg_list
1799 , p_commit => l_commit
1800 , x_return_status => l_return_status
1801 , x_msg_count => l_msg_count
1802 , x_msg_data => l_msg_data
1803 , p_score_id => l_object_id
1804 );
1805 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1806 result := 'COMPLETE:FAILURE' ;
1807
1808 /* populate error message */
1809 WF_ENGINE.SetItemAttrText(
1810 itemtype => itemtype
1811 , itemkey => itemkey
1812 , aname => 'COLL_ERR_MESSAGE'
1813 , avalue => l_msg_data
1814 );
1815 /* populate status to DRAFT */
1816 WF_ENGINE.SetItemAttrText(
1817 itemtype => itemtype
1818 , itemkey => itemkey
1819 , aname => 'STATUS_CODE'
1820 , avalue => G_STATUS_DRAFT
1821 );
1822
1823 write_buffer_to_log (l_object_type, l_object_id);
1824 RETURN;
1825 END IF;
1826
1827 AMS_Scoreresult_PVT.summarize_results (
1828 p_api_version => l_api_version
1829 , p_init_msg_list => l_init_msg_list
1830 , p_commit => l_commit
1831 , x_return_status => l_return_status
1832 , x_msg_count => l_msg_count
1833 , x_msg_data => l_msg_data
1834 , p_score_id => l_object_id
1835 );
1836 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1837 result := 'COMPLETE:SUCCESS' ;
1838 /* populate status to COMPLETED */
1839 WF_ENGINE.SetItemAttrText(
1840 itemtype => itemtype
1841 , itemkey => itemkey
1842 , aname => 'STATUS_CODE'
1843 , avalue => G_STATUS_COMPLETED
1844 );
1845 ELSE
1846 result := 'COMPLETE:FAILURE' ;
1847
1848 /* populate error message */
1849 WF_ENGINE.SetItemAttrText(
1850 itemtype => itemtype
1851 , itemkey => itemkey
1852 , aname => 'COLL_ERR_MESSAGE'
1853 , avalue => l_msg_data
1854 );
1855 /* populate status to DRAFT */
1856 WF_ENGINE.SetItemAttrText(
1857 itemtype => itemtype
1858 , itemkey => itemkey
1859 , aname => 'STATUS_CODE'
1860 , avalue => G_STATUS_DRAFT
1861 );
1862 END IF; --IF (l_return_status = FND_API.G_RET_STS_SUCCESS)
1863
1864 COMMIT;
1865
1866 END IF; --IF l_object_type = G_OBJECT_TYPE_SCORE
1867 -- CANCEL mode - Normal Process Execution
1868 ELSIF (funcmode = 'CANCEL') THEN
1869 result := 'COMPLETE' ;
1870 -- TIMEOUT mode - Normal Process Execution
1871 ELSIF (funcmode = 'TIMEOUT') THEN
1872 result := 'COMPLETE' ;
1873 --
1874 -- Other execution modes may be created in the future. The following
1875 -- activity will indicate that it does not implement a mode
1876 -- by returning null
1877 --
1878 ELSE
1879 result := '';
1880 END IF;
1881
1882 IF (AMS_DEBUG_HIGH_ON) THEN
1883 AMS_Utility_PVT.debug_message (L_API_NAME || ' - RESULT: ' || result);
1884 END IF;
1885
1886 write_buffer_to_log (l_object_type, l_object_id);
1887
1888 AMS_Utility_PVT.create_log (
1889 x_return_status => l_return_status,
1890 p_arc_log_used_by => l_object_type,
1891 p_log_used_by_id => l_object_id,
1892 p_msg_data => L_API_NAME || ': end'
1893 );
1894 EXCEPTION
1895 -- The line below records this function call in the error system
1896 -- in the case of an exception.
1897 WHEN OTHERS THEN
1898 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1899 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1900 END IF;
1901
1902 write_buffer_to_log (l_object_type, l_object_id);
1903
1904 wf_core.context(G_PKG_NAME,'Collect_Results',itemtype,itemkey,to_char(actid),funcmode);
1905
1906 /* populate status to DRAFT */
1907 WF_ENGINE.SetItemAttrText(
1908 itemtype => itemtype
1909 , itemkey => itemkey
1910 , aname => 'STATUS_CODE'
1911 , avalue => G_STATUS_DRAFT
1912 );
1913 result := 'COMPLETE:FAILURE' ;
1914 END Collect_Results ;
1915
1916 -- Start of Comments
1917 --
1918 -- NAME
1919 -- Update_Obj_Status
1920 --
1921 -- PURPOSE
1922 -- This Procedure will update object status to BUILDING or SCORING as per the object type
1923 -- at the beginning. When error happens, it flips status to DRAFT, and succeeds, it flips status to
1924 -- AVAILABLE
1925
1926 -- IN
1927 -- Itemtype - AMSDMMOD
1928 -- Itemkey - ObjectID+Time
1929 -- Accid - Activity ID
1930 -- Funmode - Run/Cancel/Timeout
1931 --
1932 -- OUT
1933 -- Result - No Result
1934
1935 --
1936 -- Used By Activities
1937 -- Item Type - AMSDMMOD
1938 -- Activity - UPDATE_OBJ_STATUS
1939 --
1940 -- NOTES
1941 --
1942 --
1943 -- HISTORY
1944 -- 02/28/2001 sveerave@uscreated
1945 -- End of Comments
1946
1947 PROCEDURE Update_Obj_Status( itemtype IN VARCHAR2
1948 , itemkey IN VARCHAR2
1949 , actid IN NUMBER
1950 , funcmode IN VARCHAR2
1951 , result OUT NOCOPY VARCHAR2
1952 ) IS
1953 L_API_NAME CONSTANT VARCHAR2(30) := 'UPDATE_OBJ_STATUS';
1954
1955 l_object_id NUMBER;
1956 l_object_type VARCHAR2(30);
1957 l_object_status VARCHAR2(30);
1958 l_message VARCHAR2(2000);
1959 l_model_type VARCHAR2(30);
1960 l_return_status VARCHAR2(1);
1961 l_msg_data VARCHAR2 (2000);
1962
1963 l_request_type VARCHAR2(30);
1964
1965 BEGIN
1966 l_object_id := WF_ENGINE.GetItemAttrNumber(
1967 itemtype => itemtype
1968 , itemkey => itemkey
1969 , aname => 'OBJECT_ID'
1970 );
1971 l_object_type := WF_ENGINE.GetItemAttrText (
1972 itemtype => itemtype
1973 , itemkey => itemkey
1974 , aname => 'OBJECT_TYPE'
1975 );
1976 l_request_type := WF_ENGINE.GetItemAttrText (
1977 itemtype => itemtype
1978 , itemkey => itemkey
1979 , aname => 'REQUEST_TYPE'
1980 );
1981
1982 AMS_Utility_PVT.create_log (
1983 x_return_status => l_return_status,
1984 p_arc_log_used_by => l_object_type,
1985 p_log_used_by_id => l_object_id,
1986 p_msg_data => L_API_NAME || ': begin'
1987 );
1988
1989 IF (AMS_DEBUG_HIGH_ON) THEN
1990 AMS_Utility_PVT.debug_message (L_API_NAME || ' - FUNCMODE: ' || funcmode);
1991 END IF;
1992
1993 -- RUN mode - Normal Process Execution
1994 IF (funcmode = 'RUN') THEN
1995 l_object_status := WF_ENGINE.GetItemAttrText (
1996 itemtype => itemtype
1997 , itemkey => itemkey
1998 , aname => 'STATUS_CODE'
1999 );
2000
2001
2002 AMS_Utility_PVT.create_log (
2003 x_return_status => l_return_status,
2004 p_arc_log_used_by => l_object_type,
2005 p_log_used_by_id => l_object_id,
2006 p_msg_data => L_API_NAME || ': ' || l_object_status
2007 );
2008
2009 IF (AMS_DEBUG_HIGH_ON) THEN
2010 AMS_Utility_PVT.debug_message (L_API_NAME || ' - OBJECT_STATUS: ' || l_object_status);
2011 END IF;
2012
2013 -- l_object_status is set to either DRAFT when preview process
2014 -- finishes or AVAILABLE when the build process is completed successfully
2015 -- or COMPLETED when scoring run completes successfully.
2016 IF l_object_status = G_STATUS_DRAFT OR l_object_status = G_STATUS_QUEUED THEN
2017 IF l_object_type = G_OBJECT_TYPE_MODEL THEN
2018 AMS_DM_Model_PVT.wf_revert (
2019 p_model_id => l_object_id,
2020 p_status_code => l_object_status,
2021 x_return_status => l_return_status
2022 );
2023 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2024 RAISE FND_API.G_EXC_ERROR;
2025 END IF;
2026 ELSE
2027 AMS_DM_Score_PVT.wf_revert (
2028 p_score_id => l_object_id,
2029 p_status_code => l_object_status,
2030 x_return_status => l_return_status
2031 );
2032 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2033 RAISE FND_API.G_EXC_ERROR;
2034 END IF;
2035 END IF;
2036 ELSIF l_object_status = G_STATUS_AVAILABLE THEN
2037 AMS_DM_Model_PVT.process_build_success (
2038 p_model_id => l_object_id,
2039 p_status_code => l_object_status,
2040 x_return_status => l_return_status
2041 );
2042 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2043 RAISE FND_API.G_EXC_ERROR;
2044 END IF;
2045 ELSIF l_object_status = G_STATUS_COMPLETED THEN
2046 AMS_DM_Score_PVT.process_score_success (
2047 p_score_id => l_object_id,
2048 p_status_code => l_object_status,
2049 x_return_status => l_return_status
2050 );
2051 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2052 RAISE FND_API.G_EXC_ERROR;
2053 END IF;
2054 ELSIF l_object_status = G_STATUS_SCORING THEN
2055 AMS_DM_Score_PVT.wf_score (
2056 p_score_id => l_object_id,
2057 x_status_code => l_object_status,
2058 x_return_status => l_return_status
2059 );
2060 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2061 RAISE FND_API.G_EXC_ERROR;
2062 END IF;
2063 -- handle if l_status_code comes back as DRAFT
2064 ELSIF l_object_status = G_STATUS_BUILDING THEN
2065 AMS_DM_Model_PVT.wf_build (
2066 p_model_id => l_object_id,
2067 x_return_status => l_return_status
2068 );
2069 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2070 RAISE FND_API.G_EXC_ERROR;
2071 END IF;
2072 END IF;
2073
2074 result := 'COMPLETE' ;
2075 -- CANCEL mode - Normal Process Execution
2076 ELSIF (funcmode = 'CANCEL') THEN
2077 result := 'COMPLETE' ;
2078 -- TIMEOUT mode - Normal Process Execution
2079 ELSIF (funcmode = 'TIMEOUT') THEN
2080 result := 'COMPLETE' ;
2081 --
2082 -- Other execution modes may be created in the future. The following
2083 -- activity will indicate that it does not implement a mode
2084 -- by returning null
2085 --
2086 ELSE
2087 result := '';
2088 END IF;
2089
2090 IF (AMS_DEBUG_HIGH_ON) THEN
2091 AMS_Utility_PVT.debug_message (L_API_NAME || ' - RESULT: ' || result);
2092 END IF;
2093
2094 write_buffer_to_log (l_object_type, l_object_id);
2095
2096 AMS_Utility_PVT.create_log (
2097 x_return_status => l_return_status,
2098 p_arc_log_used_by => l_object_type,
2099 p_log_used_by_id => l_object_id,
2100 p_msg_data => L_API_NAME || ': end'
2101 );
2102 EXCEPTION
2103 -- The line below records this function call in the error system
2104 -- in the case of an exception.
2105 WHEN OTHERS THEN
2106 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2107 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2108 END IF;
2109
2110 write_buffer_to_log (l_object_type, l_object_id);
2111
2112 wf_core.context(G_PKG_NAME,'Update_Obj_Status',itemtype,itemkey,to_char(actid),funcmode);
2113
2114 /* populate status to DRAFT */
2115 WF_ENGINE.SetItemAttrText(
2116 itemtype => itemtype
2117 , itemkey => itemkey
2118 , aname => 'STATUS_CODE'
2119 , avalue => G_STATUS_DRAFT
2120 );
2121 result := 'COMPLETE:FAILURE' ;
2122 END Update_Obj_Status ;
2123
2124
2125 PROCEDURE Get_User_Role
2126 ( p_user_id IN NUMBER,
2127 x_role_name OUT NOCOPY VARCHAR2,
2128 x_role_display_name OUT NOCOPY VARCHAR2 ,
2129 x_return_status OUT NOCOPY VARCHAR2)
2130 IS
2131 CURSOR c_resource IS
2132 SELECT employee_id source_id
2133 FROM ams_jtf_rs_emp_v
2134 WHERE resource_id = p_user_id ;
2135 l_person_id number;
2136 BEGIN
2137 x_return_status := FND_API.G_RET_STS_SUCCESS;
2138 OPEN c_resource ;
2139 FETCH c_resource INTO l_person_id ;
2140 IF c_resource%NOTFOUND THEN
2141 x_return_status := FND_API.G_RET_STS_ERROR;
2142 FND_MESSAGE.Set_Name('AMS','AMS_APPR_INVALID_RESOURCE_ID');
2143 FND_MSG_PUB.Add;
2144 END IF;
2145 CLOSE c_resource ;
2146 -- Pass the Employee ID to get the Role
2147 WF_DIRECTORY.getrolename
2148 ( p_orig_system => 'PER',
2149 p_orig_system_id => l_person_id ,
2150 p_name => x_role_name,
2151 p_display_name => x_role_display_name );
2152 IF x_role_name is null then
2153 x_return_status := FND_API.G_RET_STS_ERROR;
2154 FND_MESSAGE.Set_Name('AMS','AMS_APPR_INVALID_ROLE');
2155 FND_MSG_PUB.Add;
2156 END IF;
2157 END Get_User_Role;
2158
2159
2160 PROCEDURE write_buffer_to_log (
2161 p_object_type IN VARCHAR2,
2162 p_object_id IN NUMBER
2163 )
2164 IS
2165 l_return_status_log VARCHAR2(1);
2166 l_msg_count NUMBER;
2167 l_msg_data VARCHAR2(4000);
2168 BEGIN
2169 -- update the logs_flag in model/score table
2170 -- to Y. don't increment version because this
2171 -- could happen often
2172 IF p_object_type = G_OBJECT_TYPE_MODEL THEN
2173 UPDATE ams_dm_models_all_b
2174 SET logs_flag = 'Y',
2175 last_update_date = SYSDATE,
2176 last_updated_by = FND_GLOBAL.user_id,
2177 last_update_login = FND_GLOBAL.conc_login_id
2178 WHERE model_id = p_object_id
2179 AND logs_flag = 'N';
2180 ELSE
2181 UPDATE ams_dm_scores_all_b
2182 SET logs_flag = 'Y',
2183 last_update_date = SYSDATE,
2184 last_updated_by = FND_GLOBAL.user_id,
2185 last_update_login = FND_GLOBAL.conc_login_id
2186 WHERE score_id = p_object_id
2187 AND logs_flag = 'N';
2188 END IF;
2189
2190 l_msg_count := FND_MSG_PUB.count_msg;
2191 FOR i IN 1..FND_MSG_PUB.count_msg LOOP
2192 l_msg_data := FND_MSG_PUB.get(i, FND_API.G_FALSE);
2193 Ams_Utility_PVT.Create_Log (
2194 x_return_status => l_return_status_log
2195 , p_arc_log_used_by => p_object_type
2196 , p_log_used_by_id => p_object_id
2197 , p_msg_data => l_msg_data
2198 );
2199 END LOOP;
2200
2201 -- buffer has been written to log, clear
2202 -- buffer.
2203 FND_MSG_PUB.initialize;
2204 END write_buffer_to_log;
2205
2206
2207 --
2208 -- Note
2209 --
2210 -- History
2211 -- 30-Mar-2001 choang Created.
2212 --
2213 PROCEDURE cancel_process (
2214 p_itemkey VARCHAR2,
2215 x_return_status OUT NOCOPY VARCHAR2
2216 )
2217 IS
2218 L_API_NAME CONSTANT VARCHAR2(30) := 'cancel_process';
2219
2220 l_wf_result VARCHAR2(30);
2221 BEGIN
2222 x_return_status := FND_API.G_RET_STS_SUCCESS;
2223
2224 WF_Engine.AbortProcess (
2225 itemtype => G_DEFAULT_ITEMTYPE,
2226 itemkey => p_itemkey,
2227 result => l_wf_result
2228 );
2229 -- no matter what the result returns
2230 -- the process should still get aborted
2231 -- so we can ignore the result.
2232 --COMMIT;
2233 EXCEPTION
2234 WHEN OTHERS THEN
2235 -- Change by nyostos on Jan 6, 2003
2236 -- Commented the following line to ignore any errors when aborting the WF process
2237 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2238 --COMMIT;
2239 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2240 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name);
2241 END IF;
2242 END cancel_process;
2243
2244
2245 --
2246 -- Note
2247 --
2248 -- History
2249 -- 30-Mar-2001 choang Created.
2250 --
2251 PROCEDURE change_schedule (
2252 p_itemkey IN VARCHAR2,
2253 p_scheduled_date IN DATE,
2254 p_scheduled_timezone_id IN NUMBER,
2255 x_new_itemkey OUT NOCOPY VARCHAR2,
2256 x_return_status OUT NOCOPY VARCHAR2
2257 )
2258 IS
2259 L_API_NAME CONSTANT VARCHAR2(30) := 'change_schedule';
2260
2261 l_system_scheduled_date DATE;
2262 l_object_id NUMBER;
2263 l_object_type VARCHAR2(30);
2264 l_user_status_id NUMBER;
2265 l_wf_result VARCHAR2(30);
2266
2267 l_msg_count NUMBER;
2268 l_msg_data VARCHAR2(4000);
2269 BEGIN
2270 x_return_status := FND_API.G_RET_STS_SUCCESS;
2271
2272 -- after copying all the process attributes,
2273 -- cancel the current process and create a
2274 -- new one.
2275 l_object_id := WF_ENGINE.GetItemAttrNumber(
2276 itemtype => G_DEFAULT_ITEMTYPE
2277 , itemkey => p_itemkey
2278 , aname => 'OBJECT_ID'
2279 );
2280 l_object_type := WF_ENGINE.GetItemAttrText (
2281 itemtype => G_DEFAULT_ITEMTYPE
2282 , itemkey => p_itemkey
2283 , aname => 'OBJECT_TYPE'
2284 );
2285 l_user_status_id := WF_ENGINE.GetItemAttrNumber(
2286 itemtype => G_DEFAULT_ITEMTYPE
2287 , itemkey => p_itemkey
2288 , aname => 'ORIG_USER_STATUS_ID'
2289 );
2290
2291 AMS_Utility_PVT.convert_timezone (
2292 p_init_msg_list => FND_API.G_FALSE,
2293 x_return_status => x_return_status,
2294 x_msg_count => l_msg_count,
2295 x_msg_data => l_msg_data,
2296 p_user_tz_id => p_scheduled_timezone_id,
2297 p_in_time => p_scheduled_date,
2298 p_convert_type => 'SYS',
2299 x_out_time => l_system_scheduled_date
2300 );
2301 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2302 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2303 END IF;
2304
2305 -- abort the current process
2306 WF_Engine.AbortProcess (
2307 itemtype => G_DEFAULT_ITEMTYPE,
2308 itemkey => p_itemkey,
2309 result => l_wf_result
2310 );
2311
2312 -- create a new process with new schedule date
2313 AMS_WFMOD_PVT.StartProcess(
2314 p_object_id => l_object_id,
2315 p_object_type => l_object_type,
2316 p_user_status_id => l_user_status_id,
2317 p_scheduled_timezone_id => p_scheduled_timezone_id,
2318 p_scheduled_date => l_system_scheduled_date,
2319 p_request_type => NULL,
2320 p_select_list => NULL,
2321 x_itemkey => x_new_itemkey
2322 );
2323
2324 --COMMIT; -- otherwise, WF engine does not get this
2325 EXCEPTION
2326 WHEN OTHERS THEN
2327 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2328 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2329 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name);
2330 END IF;
2331 END change_schedule;
2332
2333
2334 --
2335 -- Note
2336 -- The current data mining configuration can only handle either
2337 -- one model building process and one scoring process at a given
2338 -- time. If a model build is requested and there is already a
2339 -- model building process running, then block the request and
2340 -- queue it up. Same thing with scoring run requests.
2341 -- 16-Sep-2003 nyostos: multiple -non-conflicting- mining operations
2342 -- can now proceed at the same time.
2343 --
2344 -- History
2345 -- 12-Jul-2001 choang Created.
2346 -- 27-Sep-2002 nyostos Changed logic to handle PREVIEWING state.
2347 -- 16-Sep-2003 nyostos Allow Parallel Mining Operations.
2348 PROCEDURE validate_concurrency (
2349 p_itemtype IN VARCHAR2,
2350 p_itemkey IN VARCHAR2,
2351 p_actid IN NUMBER,
2352 p_funcmode IN VARCHAR2,
2353 x_result OUT NOCOPY VARCHAR2
2354 )
2355 IS
2356 L_API_NAME CONSTANT VARCHAR2(30) := 'validate_concurrency';
2357 L_MINUTE CONSTANT NUMBER := 1/(24*60);
2358
2359 l_object_type VARCHAR2(30);
2360 l_object_id NUMBER;
2361 l_model_wf_itemkey VARCHAR2(30);
2362 l_score_wf_itemkey VARCHAR2(30);
2363 l_temp_wf_itemkey VARCHAR2(30);
2364 l_next_date DATE := SYSDATE + L_MINUTE;
2365
2366 l_request_type VARCHAR2(30);
2367 l_preview_status VARCHAR2(30);
2368 l_return_status VARCHAR2(1);
2369
2370 -- Cursor to get the wf_itemkey for a Model that is Building, Scoring or Previewing,
2371 -- excluding the current wf_itemkey
2372 CURSOR c_modelProcessing IS
2373 SELECT wf_itemkey
2374 FROM ams_dm_models_all_b
2375 WHERE status_code = G_STATUS_BUILDING
2376 OR status_code = G_STATUS_SCORING
2377 OR status_code = G_STATUS_PREVIEWING
2378 AND wf_itemkey <> p_itemkey;
2379
2380 -- Cursor to get the wf_itemkey for a Scoring Run that is Building, Scoring or Previewing
2381 -- excluding the current wf_itemkey
2382 CURSOR c_scoreProcessing IS
2383 SELECT wf_itemkey
2384 FROM ams_dm_scores_all_b
2385 WHERE status_code = G_STATUS_SCORING
2386 OR status_code = G_STATUS_PREVIEWING
2387 AND wf_itemkey <> p_itemkey;
2388
2389 BEGIN
2390 -- initialize the result to allow the model/score
2391 -- request to pass.
2392 x_result := 'COMPLETE:T';
2393
2394 l_object_id := WF_ENGINE.GetItemAttrNumber(
2395 itemtype => p_itemtype
2396 , itemkey => p_itemkey
2397 , aname => 'OBJECT_ID'
2398 );
2399 l_object_type := WF_ENGINE.GetItemAttrText (
2400 itemtype => p_itemtype
2401 , itemkey => p_itemkey
2402 , aname => 'OBJECT_TYPE'
2403 );
2404
2405 -- REQUEST_TYPE will be set to PREVIEW if the WF Process is handling
2406 -- a Preview request. It will be blank if a Build/Score is requested.
2407 -- REQUEST_TYPE will be set to PREVIEW_STARTED if the Preview request
2408 -- is going to execute the aggregate_soruces step.
2409 l_request_type := WF_ENGINE.GetItemAttrText (
2410 itemtype => p_itemtype
2411 , itemkey => p_itemkey
2412 , aname => 'REQUEST_TYPE'
2413 );
2414
2415 IF (AMS_DEBUG_HIGH_ON) THEN
2416 AMS_Utility_PVT.debug_message (L_API_NAME || ' - FUNCMODE: ' || p_funcmode);
2417 AMS_Utility_PVT.debug_message (L_API_NAME || ' - REQUEST_TYPE: ' || l_request_type);
2418 AMS_Utility_PVT.debug_message (L_API_NAME || ' - itemkey: ' || p_itemkey);
2419 END IF;
2420
2421 IF (p_funcmode = 'RUN') THEN
2422
2423 -- Get the wf_itemkey for the Model that is currently Building/Scoring/Previewing, if any.
2424 --OPEN c_modelProcessing;
2425 --FETCH c_modelProcessing INTO l_model_wf_itemkey;
2426 --CLOSE c_modelProcessing;
2427
2428 -- Get the wf_itemkey for the Scoring Run that is currently Scoring/Previewing, if any.
2429 --OPEN c_scoreProcessing;
2430 --FETCH c_scoreProcessing INTO l_score_wf_itemkey;
2431 --CLOSE c_scoreProcessing;
2432
2433 -- If there is a Model or Scoring Run that is in progress.
2434 --IF (l_model_wf_itemkey IS NOT NULL) OR (l_score_wf_itemkey IS NOT NULL) THEN
2435
2436 -- IF (AMS_DEBUG_HIGH_ON) THEN
2437 -- AMS_Utility_PVT.debug_message (L_API_NAME || ': ' || ' Other Model/Score in Progress');
2438 -- END IF;
2439
2440 -- IF l_model_wf_itemkey IS NOT NULL THEN
2441 -- l_temp_wf_itemkey := l_model_wf_itemkey;
2442 -- ELSE
2443 -- l_temp_wf_itemkey := l_score_wf_itemkey;
2444 -- END IF;
2445
2446 -- IF (AMS_DEBUG_HIGH_ON) THEN
2447 -- AMS_Utility_PVT.debug_message (L_API_NAME || ': ' || ' Other Model/Score wf_itemkey: ' || l_temp_wf_itemkey);
2448 -- END IF;
2449
2450 -- If we are handling a Preview request, then check if the other Model/Scoring Run
2451 -- in progress has finished the aggregate_sources step.
2452 -- IF l_request_type = G_PREVIEW_REQUEST THEN
2453 -- l_preview_status := WF_ENGINE.GetItemAttrText (
2454 -- itemtype => p_itemtype
2455 -- , itemkey => l_temp_wf_itemkey
2456 -- , aname => 'REQUEST_TYPE'
2457 -- );
2458 -- IF (AMS_DEBUG_HIGH_ON) THEN
2459 -- AMS_Utility_PVT.debug_message(L_API_NAME || ': ' || ' l_preview_status (' || l_preview_status || ')');
2460 -- END IF;
2461
2462 --IF l_preview_status = G_PREVIEW_STARTED OR l_preview_status = '' OR l_preview_status IS NULL THEN
2463 -- The other model/scoring run has not yet finished the aggregate_sources step,
2464 -- so return false and keep the status unchanged at previewing.
2465 --x_result := 'COMPLETE:F';
2466 --END IF;
2467 --ELSE
2468 -- For model build/score request, return false and set the status to QUEUED
2469 --x_result := 'COMPLETE:F';
2470
2471 --WF_ENGINE.SetItemAttrText (
2472 -- itemtype => p_itemtype
2473 -- , itemkey => p_itemkey
2474 -- , aname => 'STATUS_CODE'
2475 -- , avalue => G_STATUS_QUEUED
2476 --);
2477 -- END IF;
2478
2479 -- WF_ENGINE.SetItemAttrDate (
2480 -- itemtype => p_itemtype
2481 -- , itemkey => p_itemkey
2482 -- , aname => 'NEXT_QUEUE_CHECK'
2483 -- , avalue => l_next_date
2484 -- );
2485
2486 -- IF (AMS_DEBUG_HIGH_ON) THEN
2487 -- AMS_Utility_PVT.debug_message (L_API_NAME || ': ' || ' l_next_date ' || l_next_date);
2488 -- END IF;
2489
2490 --ELSE
2491 -- No Model/Scoring Run in progress.
2492 -- For Model, set status to BUILDING if we are not performing a Preview operation
2493 IF l_request_type <> G_PREVIEW_REQUEST THEN
2494 IF l_object_type = G_OBJECT_TYPE_MODEL THEN
2495 WF_ENGINE.SetItemAttrText (
2496 itemtype => p_itemtype
2497 , itemkey => p_itemkey
2498 , aname => 'STATUS_CODE'
2499 , avalue => G_STATUS_BUILDING
2500 );
2501 -- For Scoring Run, set status to SCORING if we are not performing a Preview operation
2502 ELSIF l_object_type = G_OBJECT_TYPE_SCORE THEN
2503 WF_ENGINE.SetItemAttrText (
2504 itemtype => p_itemtype
2505 , itemkey => p_itemkey
2506 , aname => 'STATUS_CODE'
2507 , avalue => G_STATUS_SCORING
2508 );
2509 END IF;
2510 END IF; -- If not previewing (i.e if building/scoring)
2511 --END IF;
2512 ELSIF (p_funcmode = 'CANCEL') THEN
2513 x_result := 'COMPLETE' ;
2514 -- TIMEOUT mode - Normal Process Execution
2515 ELSIF (p_funcmode = 'TIMEOUT') THEN
2516 x_result := 'COMPLETE' ;
2517 --
2518 -- Other execution modes may be created in the future. The following
2519 -- activity will indicate that it does not implement a mode
2520 -- by returning null
2521 --
2522 ELSE
2523 x_result := '';
2524 END IF; -- funcmode
2525
2526 IF (AMS_DEBUG_HIGH_ON) THEN
2527 AMS_Utility_PVT.debug_message (L_API_NAME || ' - RESULT: ' || x_result);
2528 END IF;
2529
2530 COMMIT; -- otherwise, the changes don't reflect in UI
2531
2532 write_buffer_to_log (l_object_type, l_object_id);
2533 EXCEPTION
2534 -- The line below records this function call in the error system
2535 -- in the case of an exception.
2536 WHEN OTHERS THEN
2537 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2538 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2539 END IF;
2540
2541 write_buffer_to_log (l_object_type, l_object_id);
2542
2543 wf_core.context(G_PKG_NAME, 'validate_concurrency', p_itemtype, p_itemkey, TO_CHAR(p_actid), p_funcmode);
2544 raise ;
2545 END validate_concurrency;
2546
2547
2548 -- Start of Comments
2549 --
2550 -- NAME
2551 -- Reset_Status
2552 --
2553 -- PURPOSE
2554 -- This Procedure will reset the object status back to DRAFT regardless
2555 -- of what status it is currently in.
2556
2557 -- IN
2558 -- Itemtype - AMSDMMOD
2559 -- Itemkey - ObjectID+Time
2560 -- Accid - Activity ID
2561 -- Funmode - Run/Cancel/Timeout
2562 --
2563 -- OUT
2564 -- Result - No Result
2565
2566 --
2567 -- Used By Activities
2568 -- Item Type - AMSDMMOD
2569 -- Activity - RESET_STATUS
2570 --
2571 -- NOTES
2572 --
2573 --
2574 -- HISTORY
2575 -- 08/28/2002 nyostos created
2576 -- End of Comments
2577
2578 PROCEDURE Reset_Status( p_itemtype IN VARCHAR2
2579 , p_itemkey IN VARCHAR2
2580 , p_actid IN NUMBER
2581 , p_funcmode IN VARCHAR2
2582 , x_result OUT NOCOPY VARCHAR2
2583 ) IS
2584 L_API_NAME CONSTANT VARCHAR2(30) := 'RESET_STATUS';
2585
2586 l_object_id NUMBER;
2587 l_object_type VARCHAR2(30);
2588 l_return_status VARCHAR2(1);
2589
2590 BEGIN
2591 l_object_id := WF_ENGINE.GetItemAttrNumber(
2592 itemtype => p_itemtype
2593 , itemkey => p_itemkey
2594 , aname => 'OBJECT_ID'
2595 );
2596 l_object_type := WF_ENGINE.GetItemAttrText (
2597 itemtype => p_itemtype
2598 , itemkey => p_itemkey
2599 , aname => 'OBJECT_TYPE'
2600 );
2601
2602
2603 IF (AMS_DEBUG_HIGH_ON) THEN
2604 AMS_Utility_PVT.debug_message (L_API_NAME || ' BEGIN - FUNCMODE: ' || p_funcmode);
2605 END IF;
2606
2607 -- RUN mode - Normal Process Execution
2608 IF (p_funcmode = 'RUN') THEN
2609
2610 -- nyostos 09/17/2002 - Reset status to FAILED instead of DRAFT
2611 -- Set status to FAILED for the model or scoring run
2612 IF l_object_type = G_OBJECT_TYPE_MODEL THEN
2613 AMS_DM_Model_PVT.wf_revert (
2614 p_model_id => l_object_id,
2615 p_status_code => G_STATUS_FAILED,
2616 x_return_status => l_return_status
2617 );
2618 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2619 RAISE FND_API.G_EXC_ERROR;
2620 END IF;
2621 ELSE
2622 AMS_DM_Score_PVT.wf_revert (
2623 p_score_id => l_object_id,
2624 p_status_code => G_STATUS_FAILED,
2625 x_return_status => l_return_status
2626 );
2627 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2628 RAISE FND_API.G_EXC_ERROR;
2629 END IF;
2630 END IF;
2631
2632 -- nyostos 09/17/2002 - Set STATUS_CODE Workflow global variable to FAILED instead of DRAFT
2633 -- Set STATUS_CODE Workflow global variable to FAILED
2634 WF_ENGINE.SetItemAttrText(
2635 itemtype => p_itemtype
2636 , itemkey => p_itemkey
2637 , aname => 'STATUS_CODE'
2638 , avalue => G_STATUS_FAILED
2639 );
2640
2641 x_result := 'COMPLETE' ;
2642 -- CANCEL mode - Normal Process Execution
2643 ELSIF (p_funcmode = 'CANCEL') THEN
2644 x_result := 'COMPLETE' ;
2645 -- TIMEOUT mode - Normal Process Execution
2646 ELSIF (p_funcmode = 'TIMEOUT') THEN
2647 x_result := 'COMPLETE' ;
2648 --
2649 -- Other execution modes may be created in the future. The following
2650 -- activity will indicate that it does not implement a mode
2651 -- by returning null
2652 --
2653 ELSE
2654 x_result := '';
2655 END IF;
2656
2657 IF (AMS_DEBUG_HIGH_ON) THEN
2658 AMS_Utility_PVT.debug_message (L_API_NAME || ' END - RESULT: ' || x_result);
2659 END IF;
2660
2661 write_buffer_to_log (l_object_type, l_object_id);
2662
2663 EXCEPTION
2664 -- The line below records this function call in the error system
2665 -- in the case of an exception.
2666 WHEN OTHERS THEN
2667 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2668 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2669 END IF;
2670
2671 write_buffer_to_log (l_object_type, l_object_id);
2672
2673 wf_core.context(G_PKG_NAME, 'Reset_Status', p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
2674
2675 -- nyostos 09/17/2002 - Set STATUS_CODE Workflow global variable to FAILED instead of DRAFT
2676 -- Set STATUS_CODE Workflow global variable to FAILED
2677 WF_ENGINE.SetItemAttrText(
2678 itemtype => p_itemtype
2679 , itemkey => p_itemkey
2680 , aname => 'STATUS_CODE'
2681 , avalue => G_STATUS_FAILED
2682 );
2683 x_result := 'COMPLETE:FAILURE' ;
2684 END Reset_Status ;
2685
2686
2687
2688 -- Start of Comments
2689 --
2690 -- NAME
2691 -- Is_Previewing
2692 --
2693 -- PURPOSE
2694 -- This Procedure will be called after the aggregate sources is done. If the WF process has been
2695 -- started to Preview data selections, the Model/Scoring Run status will be set to DRAFT and this
2696 -- procedure will return True so that the WF Process ends. If the WF Process was started to perform a Build
2697 -- or Score then the procedure will return F, so that the next step in the process proceeds.
2698 --
2699 -- IN
2700 -- Itemtype - AMSDMMOD
2701 -- Itemkey - ObjectID+Time
2702 -- Accid - Activity ID
2703 -- Funmode - Run/Cancel/Timeout
2704 --
2705 -- OUT
2706 -- Result - 'COMPLETE:T' If Previewing
2707 -- - 'COMPLETE:F' Otherwise
2708 --
2709 --
2710 -- NOTES
2711 --
2712 --
2713 -- HISTORY
2714 -- 20-Sep-2002 nyostos Created.
2715 -- End of Comments
2716
2717 PROCEDURE Is_Previewing ( p_itemtype IN VARCHAR2
2718 , p_itemkey IN VARCHAR2
2719 , p_actid IN NUMBER
2720 , p_funcmode IN VARCHAR2
2721 , x_result OUT NOCOPY VARCHAR2
2722 ) IS
2723 L_API_NAME CONSTANT VARCHAR2(30) := 'IS_PREVIEWING';
2724
2725 l_request_type VARCHAR2(30);
2726 l_object_id NUMBER;
2727 l_object_type VARCHAR2(30);
2728 l_return_status VARCHAR2(1);
2729
2730 BEGIN
2731 -- initialize the result
2732 x_result := 'COMPLETE:T';
2733
2734 l_request_type := WF_ENGINE.GetItemAttrText(
2735 itemtype => p_itemtype
2736 , itemkey => p_itemkey
2737 , aname => 'REQUEST_TYPE'
2738 );
2739 l_object_id := WF_ENGINE.GetItemAttrNumber(
2740 itemtype => p_itemtype
2741 , itemkey => p_itemkey
2742 , aname => 'OBJECT_ID'
2743 );
2744 l_object_type := WF_ENGINE.GetItemAttrText (
2745 itemtype => p_itemtype
2746 , itemkey => p_itemkey
2747 , aname => 'OBJECT_TYPE'
2748 );
2749
2750
2751
2752 IF (AMS_DEBUG_HIGH_ON) THEN
2753 AMS_Utility_PVT.debug_message (L_API_NAME || ' BEGIN - FUNCMODE: ' || p_funcmode);
2754 END IF;
2755
2756 -- RUN mode - Normal Process Execution
2757 IF (p_funcmode = 'RUN') THEN
2758
2759 -- If the WF Process was started to Preview Data Selections, then set the
2760 -- STATUS_CODE attribute to DRAFT as the Preview Data Selections has
2761 -- finished successfully.
2762 IF l_request_type = G_PREVIEW_REQUEST OR l_request_type = G_PREVIEW_STARTED THEN
2763
2764 -- Set STATUS_CODE attribute to DRAFT
2765 WF_ENGINE.SetItemAttrText(
2766 itemtype => p_itemtype
2767 , itemkey => p_itemkey
2768 , aname => 'STATUS_CODE'
2769 , avalue => G_STATUS_DRAFT
2770 );
2771 ELSE
2772 -- WF Process was started to Build/Score so return False
2773 x_result := 'COMPLETE:F' ;
2774
2775 -- Set REQUEST_TYPE attribute to PREVIEW_COMPLETE
2776 WF_ENGINE.SetItemAttrText(
2777 itemtype => p_itemtype
2778 , itemkey => p_itemkey
2779 , aname => 'REQUEST_TYPE'
2780 , avalue => G_PREVIEW_COMPLETE
2781 );
2782 END IF;
2783 -- CANCEL mode - Normal Process Execution
2784 ELSIF (p_funcmode = 'CANCEL') THEN
2785 x_result := 'COMPLETE' ;
2786 -- TIMEOUT mode - Normal Process Execution
2787 ELSIF (p_funcmode = 'TIMEOUT') THEN
2788 x_result := 'COMPLETE' ;
2789 --
2790 -- Other execution modes may be created in the future. The following
2791 -- activity will indicate that it does not implement a mode
2792 -- by returning null
2793 --
2794 ELSE
2795 x_result := '';
2796 END IF;
2797
2798 IF (AMS_DEBUG_HIGH_ON) THEN
2799 AMS_Utility_PVT.debug_message (L_API_NAME || ' END - RESULT: ' || x_result);
2800 END IF;
2801
2802 write_buffer_to_log (l_object_type, l_object_id);
2803
2804 EXCEPTION
2805 -- The line below records this function call in the error system
2806 -- in the case of an exception.
2807 WHEN OTHERS THEN
2808 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2809 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2810 END IF;
2811
2812
2813 write_buffer_to_log (l_object_type, l_object_id);
2814
2815 wf_core.context(G_PKG_NAME,'Is_Previewing', p_itemtype, p_itemkey,to_char(p_actid), p_funcmode);
2816
2817 -- Set STATUS_CODE attribute to DRAFT
2818 WF_ENGINE.SetItemAttrText(
2819 itemtype => p_itemtype
2820 , itemkey => p_itemkey
2821 , aname => 'STATUS_CODE'
2822 , avalue => G_STATUS_DRAFT
2823 );
2824
2825 x_result := 'COMPLETE:FAILURE' ;
2826
2827
2828 END Is_Previewing ;
2829
2830
2831 --
2832 -- Purpose
2833 -- Returns the value of a the Model/Scoring Run original status for
2834 -- a specific workflow process identified by p_itemkey
2835 --
2836 -- Parameters
2837 -- p_itemkey - the WF itemkey identifying the instance of the process.
2838 -- x_orig_status_id - original status id of the Model/Scoring Run
2839 -- x_return_status - standard output indicating the completion status
2840 --
2841 PROCEDURE get_original_status (
2842 p_itemkey VARCHAR2,
2843 x_orig_status_id OUT NOCOPY NUMBER,
2844 x_return_status OUT NOCOPY VARCHAR2
2845 )
2846 IS
2847 l_orig_status_id NUMBER;
2848
2849
2850 BEGIN
2851
2852
2853 l_orig_status_id := WF_ENGINE.GetItemAttrNumber(
2854 itemtype => G_DEFAULT_ITEMTYPE
2855 , itemkey => p_itemkey
2856 , aname => 'ORIG_USER_STATUS_ID'
2857 );
2858
2859 x_orig_status_id := l_orig_status_id;
2860
2861 END get_original_status;
2862
2863 -- Start of Comments
2864 --
2865 -- NAME
2866 -- ok_to_proceed
2867 --
2868 -- PURPOSE
2869 -- This Procedure will make final errors checks before the Build, Score or Preivew proceeds.
2870 -- For Scoring Run, we check that the Model has not become INVALID.
2871 --
2872 -- IN
2873 -- Itemtype - AMSDMMOD
2874 -- Itemkey - ObjectID+Time
2875 -- Accid - Activity ID
2876 -- Funmode - Run/Cancel/Timeout
2877 --
2878 -- OUT
2879 -- Result - 'COMPLETE:T' If ok to proceed
2880 -- - 'COMPLETE:F' Otherwise
2881 --
2882 --
2883 -- NOTES
2884 --
2885 --
2886 -- HISTORY
2887 -- 08-Oct-2002 nyostos Created.
2888 -- End of Comments
2889
2890 PROCEDURE ok_to_proceed ( p_itemtype IN VARCHAR2
2891 , p_itemkey IN VARCHAR2
2892 , p_actid IN NUMBER
2893 , p_funcmode IN VARCHAR2
2894 , x_result OUT NOCOPY VARCHAR2
2895 ) IS
2896 L_API_NAME CONSTANT VARCHAR2(30) := 'OK_TO_PROCEED';
2897
2898 l_request_type VARCHAR2(30);
2899 l_object_id NUMBER;
2900 l_object_type VARCHAR2(30);
2901 l_return_status VARCHAR2(1);
2902 l_model_status VARCHAR2(30);
2903 l_target_id NUMBER;
2904 l_is_enabled BOOLEAN;
2905
2906 CURSOR c_target_id_model (p_model_id IN NUMBER) IS
2907 SELECT target_id
2908 FROM ams_dm_models_all_b
2909 WHERE model_id = p_model_id
2910 ;
2911
2912 CURSOR c_target_id_score (p_score_id IN NUMBER) IS
2913 SELECT m.target_id
2914 FROM ams_dm_models_all_b m , ams_dm_scores_all_b s
2915 WHERE m.model_id = s.model_id
2916 AND s.score_id = p_score_id
2917 ;
2918
2919 BEGIN
2920 -- initialize the result
2921 x_result := 'COMPLETE:T';
2922
2923 l_request_type := WF_ENGINE.GetItemAttrText(
2924 itemtype => p_itemtype
2925 , itemkey => p_itemkey
2926 , aname => 'REQUEST_TYPE'
2927 );
2928 l_object_id := WF_ENGINE.GetItemAttrNumber(
2929 itemtype => p_itemtype
2930 , itemkey => p_itemkey
2931 , aname => 'OBJECT_ID'
2932 );
2933 l_object_type := WF_ENGINE.GetItemAttrText (
2934 itemtype => p_itemtype
2935 , itemkey => p_itemkey
2936 , aname => 'OBJECT_TYPE'
2937 );
2938
2939
2940
2941 IF (AMS_DEBUG_HIGH_ON) THEN
2942 AMS_Utility_PVT.debug_message (L_API_NAME || ' BEGIN - FUNCMODE: ' || p_funcmode);
2943 END IF;
2944
2945 -- RUN mode - Normal Process Execution
2946 IF (p_funcmode = 'RUN') THEN
2947
2948 -- First check if the target has not been disabled
2949 IF l_object_type = G_OBJECT_TYPE_SCORE THEN
2950 OPEN c_target_id_score(l_object_id);
2951 FETCH c_target_id_score INTO l_target_id;
2952 CLOSE c_target_id_score;
2953 ELSE
2954 OPEN c_target_id_model(l_object_id);
2955 FETCH c_target_id_model INTO l_target_id;
2956 CLOSE c_target_id_model;
2957 END IF;
2958
2959 AMS_DM_TARGET_PVT.is_target_enabled(
2960 p_target_id => l_target_id ,
2961 x_is_enabled => l_is_enabled
2962 );
2963 IF l_is_enabled = FALSE THEN
2964 IF (AMS_DEBUG_HIGH_ON) THEN
2965 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' ERROR: Target is disabled, cannot preview/build/score');
2966 END IF;
2967 AMS_Utility_PVT.create_log (
2968 x_return_status => l_return_status,
2969 p_arc_log_used_by => l_object_type,
2970 p_log_used_by_id => l_object_id,
2971 p_msg_data => L_API_NAME || ' ERROR: Target is disabled, cannot preview/build/score '
2972 );
2973 x_result := 'COMPLETE:F' ;
2974 END IF;
2975
2976
2977 -- If the WF Process was started to Score, then check if the Model has
2978 -- not become INVALID
2979 IF x_result <> 'COMPLETE:F' AND l_request_type <> G_PREVIEW_REQUEST AND l_object_type = G_OBJECT_TYPE_SCORE THEN
2980 AMS_DM_Score_PVT.wf_checkModelStatus (
2981 p_score_id => l_object_id,
2982 x_return_status => l_return_status,
2983 x_model_status => l_model_status
2984 );
2985 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2986 AMS_Utility_PVT.create_log (
2987 x_return_status => l_return_status,
2988 p_arc_log_used_by => 'SCOR',
2989 p_log_used_by_id => l_object_id,
2990 p_msg_data => L_API_NAME || ' ERROR: Model Status is ' || l_model_status
2991 );
2992 x_result := 'COMPLETE:F' ;
2993 END IF;
2994 ELSIF x_result <> 'COMPLETE:F' AND l_request_type = G_PREVIEW_REQUEST THEN
2995 -- Set the Request type to PREVIEW_STARTED. This will be checked by other
2996 -- Preview requests when validating concurrency to resolve any potential deadlock
2997 WF_ENGINE.SetItemAttrText (
2998 itemtype => p_itemtype
2999 , itemkey => p_itemkey
3000 , aname => 'REQUEST_TYPE'
3001 , avalue => G_PREVIEW_STARTED
3002 );
3003 END IF;
3004 -- CANCEL mode - Normal Process Execution
3005 ELSIF (p_funcmode = 'CANCEL') THEN
3006 x_result := 'COMPLETE' ;
3007 -- TIMEOUT mode - Normal Process Execution
3008 ELSIF (p_funcmode = 'TIMEOUT') THEN
3009 x_result := 'COMPLETE' ;
3010 --
3011 -- Other execution modes may be created in the future. The following
3012 -- activity will indicate that it does not implement a mode
3013 -- by returning null
3014 --
3015 ELSE
3016 x_result := '';
3017 END IF;
3018
3019 IF (AMS_DEBUG_HIGH_ON) THEN
3020 AMS_Utility_PVT.debug_message (L_API_NAME || ' END - RESULT: ' || x_result);
3021 END IF;
3022
3023 write_buffer_to_log (l_object_type, l_object_id);
3024
3025 EXCEPTION
3026 -- The line below records this function call in the error system
3027 -- in the case of an exception.
3028 WHEN OTHERS THEN
3029 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3030 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3031 END IF;
3032
3033 write_buffer_to_log (l_object_type, l_object_id);
3034
3035 wf_core.context(G_PKG_NAME,'ok_to_proceed', p_itemtype, p_itemkey,to_char(p_actid), p_funcmode);
3036
3037 -- Set STATUS_CODE attribute to DRAFT
3038 WF_ENGINE.SetItemAttrText(
3039 itemtype => p_itemtype
3040 , itemkey => p_itemkey
3041 , aname => 'STATUS_CODE'
3042 , avalue => G_STATUS_DRAFT
3043 );
3044
3045 x_result := 'COMPLETE:FAILURE' ;
3046 END ok_to_proceed;
3047
3048
3049 END AMS_WFMOD_PVT;