1 PACKAGE AMS_WFMOD_PVT AUTHID CURRENT_USER AS
2 /* $Header: amsvwmds.pls 115.12 2002/12/09 11:32:56 choang noship $*/
3
4 -- Start of Comments
5 --
6 -- NAME
7 -- AMS_WFMOD_PVT
8 --
9 -- PURPOSE
10 -- This package contains the workflow procedures for
11 -- Model Building/Scoring in Oracle Marketing
12 --
13 -- HISTORY
14 -- 11/30/2000 sveerave@us CREATED
15 -- 30-Mar-2001 choang Added cancel_process and change_schedule
16 -- 12-Jul-2001 choang Added validate_concurrency.
17 -- 07-Dec-2001 choang Modified change_schedule spec.
18 -- 17-Jul-2002 choang Added default itemtype to spec for bug
19 -- 2410322.
20 -- 29-Aug-2002 nyostos Added Reset_Status procedure to reset model/
21 -- score status to DRAFT.
22 --
23 /************* GLOBAL VARIABLES *******************/
24 G_DEFAULT_ITEMTYPE CONSTANT VARCHAR2(30) := 'AMSDMMOD';
25
26 /*************************** PRIVATE ROUTINES *******************************/
27 -- Start of Comments
28 --
29 -- NAME
30 -- StartProcess
31 --
32 -- PURPOSE
33 -- This Procedure will Start the flow
34 --
35 -- IN
36 -- p_object_id Object ID - Model or Score ID
37 -- p_object_type Object Type - MODL or SCOR
38 -- p_request_type Request Type for data mining engine, Darwin
39 -- p_select_list Select List for data mining engine, Darwin
40 -- processowner Owner Of the Process
41 -- workflowprocess Work Flow Process Name (MODEL_BUILD_SCORE)
42 -- itemtype Item type DEFAULT NULL(AMSDMMOD)
43
44
45 --
46 -- OUT
47 --
48 -- Used By Activities
49 --
50 -- NOTES
51 --
52 --
53 -- HISTORY
54 -- 11/30/1999 sveerave@us created
55 -- 04-Apr-2001 choang Added scheduled_timezone_id and scheduled_date to params.
56 -- End of Comments
57
58 PROCEDURE StartProcess( p_object_id IN NUMBER
59 , p_object_type IN VARCHAR2
60 , p_user_status_id IN NUMBER
61 , p_scheduled_timezone_id IN NUMBER
62 , p_scheduled_date IN DATE
63 , p_request_type IN VARCHAR2 DEFAULT NULL
64 , p_select_list IN VARCHAR2 DEFAULT NULL
65 , p_enqueue_message IN VARCHAR2 DEFAULT NULL
66 , processowner IN VARCHAR2 DEFAULT NULL
67 , workflowprocess IN VARCHAR2 DEFAULT NULL
68 , itemtype IN VARCHAR2 DEFAULT G_DEFAULT_ITEMTYPE
69 , x_itemkey OUT NOCOPY VARCHAR2
70 );
71
72 -- Start of Comments
73 --
74 -- NAME
75 -- Selector
76 --
77 -- PURPOSE
78 -- This Procedure will determine which process to run
79 --
80 -- IN
81 -- itemtype - A Valid item type from (WF_ITEM_TYPES Table).
82 -- itemkey - A string generated from application object's primary key.
83 -- actid - The function Activity
84 -- funcmode - Run / Cancel
85 --
86 -- OUT
87 -- resultout - Name of workflow process to run
88 --
89 -- Used By Activities
90 --
91 -- NOTES
92 --
93 --
94 -- HISTORY
95 -- 11/30/1999 sveerave@us created
96 -- End of Comments
97
98
99 PROCEDURE selector ( itemtype IN VARCHAR2
100 , itemkey IN VARCHAR2
101 , actid IN NUMBER
102 , funcmode IN VARCHAR2
103 , resultout OUT NOCOPY VARCHAR2
104 ) ;
105
106 -- Start of Comments
107 --
108 -- NAME
109 -- Validate
110 --
111 -- PURPOSE
112 -- This Procedure will aggregate sources based on user selections, and will return
113 -- Success or Failure
114 --
115 -- IN
116 -- Itemtype - AMSDMMOD
117 -- Itemkey - ObjectID+Time
118 -- Accid - Activity ID
119 -- Funmode - Run/Cancel/Timeout
120 --
121 -- OUT
122 -- Result - 'COMPLETE:T' If the validation is successfully completed
123 -- - 'COMPLETE:F' If there is an error in validation
124 --
125 -- Used By Activities
126 -- Item Type - AMSDMMOD
127 -- Activity - VALIDATE
128 --
129 -- NOTES
130 --
131 --
132 -- HISTORY
133 -- 02/27/2001 sveerave@us created
134 -- End of Comments
135
136 PROCEDURE validate_data ( itemtype IN VARCHAR2
137 , itemkey IN VARCHAR2
138 , actid IN NUMBER
139 , funcmode IN VARCHAR2
140 , result OUT NOCOPY VARCHAR2
141 );
142
143 -- Start of Comments
144 --
145 -- NAME
146 -- Aggregate_sources
147 --
148 -- PURPOSE
149 -- This Procedure will aggregate sources based on user selections, and will return
150 -- Success or Failure
151 --
152 -- IN
153 -- Itemtype - AMSDMMOD
154 -- Itemkey - ObjectID+Time
155 -- Accid - Activity ID
156 -- Funmode - Run/Cancel/Timeout
157 --
158 -- OUT
159 -- Result - 'COMPLETE:SUCCESS' If the aggregation is successfully completed
160 -- - 'COMPLETE:FAILURE' If there is an error in aggregation
161 --
162 -- Used By Activities
163 -- Item Type - AMSDMMOD
164 -- Activity - AGGREGATE_SOURCES
165 --
166 -- NOTES
167 --
168 --
169 -- HISTORY
170 -- 11/30/2000 sveerave@us created
171 -- End of Comments
172
173 PROCEDURE Aggregate_Sources( itemtype IN VARCHAR2
174 , itemkey IN VARCHAR2
175 , actid IN NUMBER
176 , funcmode IN VARCHAR2
177 , result OUT NOCOPY VARCHAR2
178 );
179
180 -- Start of Comments
181 --
182 -- NAME
183 -- Transform
184 --
185 -- PURPOSE
186 -- This Procedure will transform the untransformed data, and will return
187 -- Success or Failure
188 --
189 -- IN
190 -- Itemtype - AMSDMMOD
191 -- Itemkey - ObjectID+Time
192 -- Accid - Activity ID
193 -- Funmode - Run/Cancel/Timeout
194 --
195 -- OUT
196 -- Result - 'COMPLETE:SUCCESS' If the transformation is successfully completed
197 -- - 'COMPLETE:FAILURE' If there is an error in transformation
198 --
199 -- Used By Activities
200 -- Item Type - AMSDMMOD
201 -- Activity - TRANSFORM
202 --
203 -- NOTES
204 --
205 --
206 -- HISTORY
207 -- 11/30/2000 sveerave@us created
208 -- End of Comments
209
210 PROCEDURE Transform( itemtype IN VARCHAR2
211 , itemkey IN VARCHAR2
212 , actid IN NUMBER
213 , funcmode IN VARCHAR2
214 , result OUT NOCOPY VARCHAR2
215 ) ;
216
217 -- Start of Comments
218 --
219 -- NAME
220 -- Command
221 --
222 -- PURPOSE
223 -- This Procedure will request for data mining by posting a request to AQ, and will return
224 -- Success or Failure
225 --
226 -- IN
227 -- Itemtype - AMSDMMOD
228 -- Itemkey - ObjectID+Time
229 -- Accid - Activity ID
230 -- Funmode - Run/Cancel/Timeout
231 -- OUT
232 -- Result - 'COMPLETE:SUCCESS' If sumbmitting aq request is successfully completed
233 -- - 'COMPLETE:FAILURE' If there is an error in submitting the aq request
234 --
235 -- Used By Activities
236 -- Item Type - AMSDMMOD
237 -- Activity - COMMAND
238 --
239 -- NOTES
240 --
241 --
242 -- HISTORY
243 -- 11/30/2000 sveerave@us created
244 -- End of Comments
245
246 PROCEDURE Command( itemtype IN VARCHAR2
247 , itemkey IN VARCHAR2
248 , actid IN NUMBER
249 , funcmode IN VARCHAR2
250 , result OUT NOCOPY VARCHAR2
251 ) ;
252
253 -- Start of Comments
254 --
255 -- NAME
256 -- Check_response
257 --
258 -- PURPOSE
259 -- This Procedure will poll AQ to check whether there is any message
260 -- awaiting from Darwin after model is built/scored,
261 -- and will return:
262
263 -- ERROR (Successfully polled AQ with a message waiting, but the message is an Error message from mining application to build/score a model)
264 -- FAILURE (Failed to poll AQ for a message due to AQ system failure)
265 -- NO (Sucessfully polled AQ, but there are no messages waiting in the queue.)
266 -- YES (Sucessfully polled AQ with a message waiting, and also there are no errors from mining application (Darwin))
267
268 -- IN
269 -- Itemtype - AMSDMMOD
270 -- Itemkey - ObjectID+Time
271 -- Accid - Activity ID
272 -- Funmode - Run/Cancel/Timeout
273 --
274 -- OUT
275 -- Result - 'COMPLETE:YES' If there is message awaiting from Darwin about model building/scoring.
276 -- 'COMPLETE:NO' If there is no message awaiting from Darwin about model building/scoring.
277 -- 'COMPLETE:ERROR' If there is error message from Darwin'
278 -- 'COMPLETE:FAILURE' If it could not poll aq due to aq system failure
279
280 --
281 -- Used By Activities
282 -- Item Type - AMSDMMOD
283 -- Activity - CHECK_RESPONSE
284 --
285 -- NOTES
286 --
287 --
288 -- HISTORY
289 -- 11/30/2000 sveerave@uscreated
290 -- End of Comments
291
292 PROCEDURE Check_Response( itemtype IN VARCHAR2
293 , itemkey IN VARCHAR2
294 , actid IN NUMBER
295 , funcmode IN VARCHAR2
296 , result OUT NOCOPY VARCHAR2
297 ) ;
298
299 -- Start of Comments
300 --
301 -- NAME
302 -- Collect_Results
303 --
304 -- PURPOSE
305 -- This Procedure will collect results once the model is built or scored by Darwin, and will return
306 -- Success or Failure
307 --
308 -- IN
309 -- Itemtype - AMSDMMOD
310 -- Itemkey - ObjectID+Time
311 -- Accid - Activity ID
312 -- Funmode - Run/Cancel/Timeout
313 --
314 -- OUT
315 -- Result - 'COMPLETE:SUCCESS' If the collect results is successfully completed
316 -- - 'COMPLETE:FAILURE' If there is an error in collect results
317 --
318 -- Used By Activities
319 -- Item Type - AMSDMMOD
320 -- Activity - COLLECT_RESULTS
321 --
322 -- NOTES
323 --
324 --
325 -- HISTORY
326 -- 11/30/2000 sveerave@us created
327 -- End of Comments
328
329 PROCEDURE Collect_Results( itemtype IN VARCHAR2
330 , itemkey IN VARCHAR2
331 , actid IN NUMBER
332 , funcmode IN VARCHAR2
333 , result OUT NOCOPY VARCHAR2
334 ) ;
335
336 -- Start of Comments
337 --
338 -- NAME
339 -- Reset_Status
340 --
341 -- PURPOSE
342 -- This Procedure will reset the object status back to DRAFT regardless
343 -- of what status it is currently in.
344
345 -- IN
346 -- Itemtype - AMSDMMOD
347 -- Itemkey - ObjectID+Time
348 -- Accid - Activity ID
349 -- Funmode - Run/Cancel/Timeout
350 --
351 -- OUT
352 -- Result - No Result
353
354 --
355 -- Used By Activities
356 -- Item Type - AMSDMMOD
357 -- Activity - RESET_STATUS
358 --
359 -- NOTES
360 --
361 --
362 -- HISTORY
363 -- 08/28/2002 nyostos created
364 -- End of Comments
365
366 PROCEDURE Reset_Status( p_itemtype IN VARCHAR2
367 , p_itemkey IN VARCHAR2
368 , p_actid IN NUMBER
369 , p_funcmode IN VARCHAR2
370 , x_result OUT NOCOPY VARCHAR2
371 );
372
373
374
375 -- Start of Comments
376 --
377 -- NAME
378 -- Update_Obj_Status
379 --
380 -- PURPOSE
381 -- This Procedure will update object status to BUILDING or SCORING as per the object type
382 -- at the beginning. When error happens, it flips status to DRAFT, and succeeds, it flips status to
383 -- AVAILABLE
384
385 -- IN
386 -- Itemtype - AMSDMMOD
387 -- Itemkey - ObjectID+Time
388 -- Accid - Activity ID
389 -- Funmode - Run/Cancel/Timeout
390 --
391 -- OUT
392 -- Result - No Result
393
394 --
395 -- Used By Activities
396 -- Item Type - AMSDMMOD
397 -- Activity - UPDATE_OBJ_STATUS
398 --
399 -- NOTES
400 --
401 --
402 -- HISTORY
403 -- 02/28/2001 sveerave@uscreated
404 -- End of Comments
405
406 PROCEDURE Update_Obj_Status( itemtype IN VARCHAR2
407 , itemkey IN VARCHAR2
408 , actid IN NUMBER
409 , funcmode IN VARCHAR2
410 , result OUT NOCOPY VARCHAR2
411 );
412
413
414 --
415 -- Purpose
416 -- Cancel the specified instance of a WF model building
417 -- or scoring process.
418 --
422 --
419 -- Parameters
420 -- p_itemkey - the WF itemkey identifying the instance of the process.
421 -- x_return_status - standard output indicating the completion status
423 PROCEDURE cancel_process (
424 p_itemkey VARCHAR2,
425 x_return_status OUT NOCOPY VARCHAR2
426 );
427
428
429 --
430 -- Purpose
431 -- Update the scheduled date of the model building or scoring process.
432 --
433 -- Parameters
434 -- p_itemkey - the WF itemkey identifying the instance of the process.
435 -- x_return_status - standard output indicating the completion status
436 --
437 PROCEDURE change_schedule (
438 p_itemkey IN VARCHAR2,
439 p_scheduled_date IN DATE,
440 p_scheduled_timezone_id IN NUMBER,
441 x_new_itemkey OUT NOCOPY VARCHAR2,
442 x_return_status OUT NOCOPY VARCHAR2
443 );
444
445
446 --
447 -- Purpose
448 --
449 -- Parameters
450 -- p_itemtype - AMSDMMOD
451 -- p_itemkey - unique identifier of the workflow process instance.
452 -- p_actid - activity id
453 -- p_funcmode - Run/Cancel/Timeout
454 -- x_result - output result: TRUE, FALSE
455 PROCEDURE validate_concurrency (
456 p_itemtype IN VARCHAR2,
457 p_itemkey IN VARCHAR2,
458 p_actid IN NUMBER,
459 p_funcmode IN VARCHAR2,
460 x_result OUT NOCOPY VARCHAR2
461 );
462
463
464 -- Start of Comments
465 --
466 -- NAME
467 -- Is_Previewing
468 --
469 -- PURPOSE
470 -- This Procedure will be called after the aggregate sources is done. If the WF process has been
471 -- started to Preview data selections, the Model/Scoring Run status will be set to DRAFT and this
472 -- procedure will return True so that the WF Process ends. If the WF Process was started to perform a Build
476 -- Itemtype - AMSDMMOD
473 -- or Score then the procedure will return F, so that the next step in the process proceeds.
474 --
475 -- IN
477 -- Itemkey - ObjectID+Time
478 -- Accid - Activity ID
479 -- Funmode - Run/Cancel/Timeout
480 --
481 -- OUT
482 -- Result - 'COMPLETE:T' If Previewing
483 -- - 'COMPLETE:F' Otherwise
484 --
485 --
486 -- NOTES
487 --
488 --
489 -- HISTORY
490 -- 20-Sep-2002 nyostos Created.
491 -- End of Comments
492
493 PROCEDURE Is_Previewing ( p_itemtype IN VARCHAR2
494 , p_itemkey IN VARCHAR2
495 , p_actid IN NUMBER
496 , p_funcmode IN VARCHAR2
497 , x_result OUT NOCOPY VARCHAR2
498 );
499
500
501 --
502 -- Purpose
503 -- Returns the value of a the Model/Scoring Run original status for
504 -- a specific workflow process identified by p_itemkey
505 --
506 -- Parameters
507 -- p_itemkey - the WF itemkey identifying the instance of the process.
508 -- x_orig_status_id - original status id of the Model/Scoring Run
509 -- x_return_status - standard output indicating the completion status
510 --
511 PROCEDURE get_original_status (
512 p_itemkey VARCHAR2,
513 x_orig_status_id OUT NOCOPY NUMBER,
514 x_return_status OUT NOCOPY VARCHAR2
515 );
516
517 -- Start of Comments
518 --
519 -- NAME
520 -- ok_to_proceed
521 --
522 -- PURPOSE
523 -- This Procedure will make final errors checks before the Build, Score or Preivew proceeds.
524 -- For Scoring Run, we check that the Model has not become INVALID.
525 --
526 -- IN
527 -- Itemtype - AMSDMMOD
528 -- Itemkey - ObjectID+Time
529 -- Accid - Activity ID
530 -- Funmode - Run/Cancel/Timeout
531 --
532 -- OUT
533 -- Result - 'COMPLETE:T' If ok to proceed
534 -- - 'COMPLETE:F' Otherwise
535 --
536 --
537 -- NOTES
538 --
539 --
540 -- HISTORY
541 -- 08-Oct-2002 nyostos Created.
542 -- End of Comments
543
544 PROCEDURE ok_to_proceed ( p_itemtype IN VARCHAR2
545 , p_itemkey IN VARCHAR2
546 , p_actid IN NUMBER
547 , p_funcmode IN VARCHAR2
548 , x_result OUT NOCOPY VARCHAR2
549 );
550
551 END AMS_WFMOD_PVT;