1 PACKAGE ams_dm_score_pvt AUTHID CURRENT_USER as
2 /* $Header: amsvdmss.pls 115.18 2003/08/20 08:30:40 kbasavar ship $ */
3 -- Start of Comments
4 -- Package name : AMS_DM_SCORE_PVT
5 -- Purpose : PACKAGE SPECIFICATION FOR PRIVATE API
6 -- History : 11/20 julou created
7 -- 23-Jan-2001 choang Added org_id to rec type
8 -- 12-Feb-2001 choang 1) Changed model_score to score. 2) added new columns.
9 -- 19-Feb-2001 choang Replaced top_down_flag with row_selection_type.
10 -- 26-Feb-2001 choang Added custom_setup_id and country_id.
11 -- 10-Mar-2001 choang Added wf_itemkey, wf_revert(), process_score_status()
12 -- 11-Mar-2001 choang Added handle_preview_request
13 -- 07-Apr-2001 choang Added copy_score
14 -- 11-Apr-2001 choang 1) changed spec of wf_revert 2) added wf_score
15 -- 17-Aug-2001 choang Added custom_setup_id in out param of create api.
16 -- 07-Jan-2002 choang Removed security group id
17 -- 04-Oct-2002 choang Added cancel_run_request
18 -- 20-Aug-2003 kbasavar Incresed the description size to 4000
19 --
20 --
21 --
22 -- NOTE :
23 -- End of Comments
24
25 -- Default number of records fetch per call
26 G_DEFAULT_NUM_REC_FETCH NUMBER := 30;
27 -- *******************************************************
28 -- Start of Comments
29 -- -------------------------------------------------------
30 -- Record name: Score_Rec_Type
31 -- -------------------------------------------------------
32 -- Parameters:
33 -- score_ID
34 -- LAST_UPDATE_DATE
35 -- LAST_UPDATED_BY
36 -- CREATION_DATE
37 -- CREATED_BY
38 -- LAST_UPDATE_LOGIN
39 -- OBJECT_VERSION_NUMBER
40 -- MODEL_ID
41 -- USER_STATUS_ID
42 -- STATUS_CODE
43 -- STATUS_DATE
44 -- OWNER_USER_ID
45 -- RESULTS_FLAG
46 -- logs_flag
47 -- scheduled_date
48 -- scheduled_timezone_id
49 -- score_date
50 -- expiration_date
51 -- total_records
52 -- total_positives
53 -- min_records
54 -- max_records
55 -- row_selection_type
56 -- every_nth_row
57 -- pct_random
58 -- custom_setup_id
59 -- country_id
60 -- wf_itemkey
61 -- score_name
62 -- description
63 -- ATTRIBUTE_CATEGORY
64 -- ATTRIBUTE1
65 -- ATTRIBUTE2
66 -- ATTRIBUTE3
67 -- ATTRIBUTE4
68 -- ATTRIBUTE5
69 -- ATTRIBUTE6
70 -- ATTRIBUTE7
71 -- ATTRIBUTE8
72 -- ATTRIBUTE9
73 -- ATTRIBUTE10
74 -- ATTRIBUTE11
75 -- ATTRIBUTE12
76 -- ATTRIBUTE13
77 -- ATTRIBUTE14
78 -- ATTRIBUTE15
79 --
80 -- Required:
81 -- Defaults:
82 -- Note: This is automatic generated record definition, it includes all columns
83 -- defined in the table, developer must manually add or delete some of the attributes.
84 --
85 -- End of Comments
86
87 TYPE Score_Rec_Type IS RECORD
88 (
89 score_id NUMBER := FND_API.G_MISS_NUM,
90 last_update_date DATE := FND_API.G_MISS_DATE,
91 last_updated_by NUMBER := FND_API.G_MISS_NUM,
92 creation_date DATE := FND_API.G_MISS_DATE,
93 created_by NUMBER := FND_API.G_MISS_NUM,
94 last_update_login NUMBER := FND_API.G_MISS_NUM,
95 object_version_number NUMBER := FND_API.G_MISS_NUM,
96 org_id NUMBER := FND_API.G_MISS_NUM,
97 model_id NUMBER := FND_API.G_MISS_NUM,
98 user_status_id NUMBER := FND_API.G_MISS_NUM,
99 status_code VARCHAR2(30) := FND_API.G_MISS_CHAR,
100 status_date DATE := FND_API.G_MISS_DATE,
101 owner_user_id NUMBER := FND_API.G_MISS_NUM,
102 results_flag VARCHAR2(1) := FND_API.G_MISS_CHAR,
103 logs_flag VARCHAR2(1) := FND_API.G_MISS_CHAR,
104 scheduled_date DATE := FND_API.G_MISS_DATE,
105 scheduled_timezone_id NUMBER := FND_API.G_MISS_NUM,
106 score_date DATE := FND_API.G_MISS_DATE,
107 expiration_date DATE := FND_API.G_MISS_DATE,
108 total_records NUMBER := FND_API.G_MISS_NUM,
109 total_positives NUMBER := FND_API.G_MISS_NUM,
110 min_records NUMBER := FND_API.G_MISS_NUM,
111 max_records NUMBER := FND_API.G_MISS_NUM,
112 row_selection_type VARCHAR2(30) := FND_API.G_MISS_CHAR,
113 every_nth_row NUMBER := FND_API.G_MISS_NUM,
114 pct_random NUMBER := FND_API.G_MISS_NUM,
115 custom_setup_id NUMBER := FND_API.G_MISS_NUM,
116 country_id NUMBER := FND_API.G_MISS_NUM,
117 wf_itemkey VARCHAR2(240) := FND_API.G_MISS_CHAR,
118 score_name VARCHAR2(120) := FND_API.G_MISS_CHAR,
119 description VARCHAR2(4000) := FND_API.G_MISS_CHAR,
120 attribute_category VARCHAR2(30) := FND_API.G_MISS_CHAR,
121 attribute1 VARCHAR2(150) := FND_API.G_MISS_CHAR,
122 attribute2 VARCHAR2(150) := FND_API.G_MISS_CHAR,
123 attribute3 VARCHAR2(150) := FND_API.G_MISS_CHAR,
124 attribute4 VARCHAR2(150) := FND_API.G_MISS_CHAR,
125 attribute5 VARCHAR2(150) := FND_API.G_MISS_CHAR,
126 attribute6 VARCHAR2(150) := FND_API.G_MISS_CHAR,
127 attribute7 VARCHAR2(150) := FND_API.G_MISS_CHAR,
128 attribute8 VARCHAR2(150) := FND_API.G_MISS_CHAR,
129 attribute9 VARCHAR2(150) := FND_API.G_MISS_CHAR,
130 attribute10 VARCHAR2(150) := FND_API.G_MISS_CHAR,
131 attribute11 VARCHAR2(150) := FND_API.G_MISS_CHAR,
132 attribute12 VARCHAR2(150) := FND_API.G_MISS_CHAR,
133 attribute13 VARCHAR2(150) := FND_API.G_MISS_CHAR,
134 attribute14 VARCHAR2(150) := FND_API.G_MISS_CHAR,
135 attribute15 VARCHAR2(150) := FND_API.G_MISS_CHAR
136 );
137
138 G_MISS_Score_REC Score_Rec_Type;
139 TYPE DM_score_Tbl_Type IS TABLE OF Score_Rec_Type INDEX BY BINARY_INTEGER;
140 G_MISS_Score_TBL DM_score_Tbl_Type;
141
142
143 -- Start of Comments
144 --
145 -- validation procedures
146 --
147 -- p_validation_mode is a constant defined in AMS_UTILITY_PVT package
148 -- For create: G_CREATE, for update: G_UPDATE
149 -- Note: 1. This is automated generated item level validation procedure.
150 -- The actual validation detail is needed to be added.
151 -- 2. Validate the unique keys, lookups here
152 -- End of Comments
153
154 PROCEDURE Check_Score_Items (
155 p_Score_rec IN Score_Rec_Type,
156 p_validation_mode IN VARCHAR2,
157 x_return_status OUT NOCOPY VARCHAR2
158 );
159 -- *******************************************************
160 -- Start of Comments
161 -- *******************************************************
162 -- API Name: Lock_Score
163 -- Type : Private
164 -- Pre-Req :
165 -- Parameters:
166 -- IN
167 -- p_api_version IN NUMBER Required
168 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
169 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
170 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
171 -- P_Score_Rec IN Score_Rec_Type Required
172 --
173 -- OUT:
174 -- x_return_status OUT VARCHAR2
175 -- x_msg_count OUT NUMBER
176 -- x_msg_data OUT VARCHAR2
177 -- Version : Current version 1.0
178 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
179 -- and basic operation, developer must manually add parameters and business logic as necessary.
180 --
181 -- End of Comments
182 --
183 PROCEDURE Lock_Score(
184 p_api_version IN NUMBER,
185 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
186 x_return_status OUT NOCOPY VARCHAR2,
187 x_msg_count OUT NOCOPY NUMBER,
188 x_msg_data OUT NOCOPY VARCHAR2,
189 p_score_id IN NUMBER,
190 p_object_version IN NUMBER
191 );
192
193 -- *******************************************************
194 -- Start of Comments
195 -- *******************************************************
196 -- API Name: Create_Score
197 -- Type : Private
198 -- Pre-Req :
199 -- Parameters:
200 -- IN
201 -- p_api_version IN NUMBER Required
202 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
203 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
204 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
205 -- P_Score_Rec IN Score_Rec_Type Required
206 --
207 -- OUT:
208 -- x_return_status OUT VARCHAR2
209 -- x_msg_count OUT NUMBER
210 -- x_msg_data OUT VARCHAR2
211 -- Version : Current version 1.0
212 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
213 -- and basic operation, developer must manually add parameters and business logic as necessary.
214 --
215 -- End of Comments
216 --
217 PROCEDURE Create_Score(
218 p_api_version IN NUMBER,
219 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
220 p_commit IN VARCHAR2 := FND_API.G_FALSE,
221 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
222 x_return_status OUT NOCOPY VARCHAR2,
223 x_msg_count OUT NOCOPY NUMBER,
224 x_msg_data OUT NOCOPY VARCHAR2,
225 p_Score_rec IN Score_Rec_Type := G_MISS_Score_REC,
226 x_custom_setup_id OUT NOCOPY NUMBER,
227 x_score_id OUT NOCOPY NUMBER
228 );
229
230 -- *******************************************************
231 -- Start of Comments
232 -- *******************************************************
233 -- API Name: Update_Score
234 -- Type : Private
235 -- Pre-Req :
236 -- Parameters:
237 -- IN
238 -- p_api_version IN NUMBER Required
239 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
240 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
241 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
242 -- P_Score_Rec IN Score_Rec_Type Required
243 --
244 -- OUT:
245 -- x_return_status OUT VARCHAR2
246 -- x_msg_count OUT NUMBER
247 -- x_msg_data OUT VARCHAR2
248 -- Version : Current version 1.0
249 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
250 -- and basic operation, developer must manually add parameters and business logic as necessary.
251 --
252 -- End of Comments
253 --
254 PROCEDURE Update_Score(
255 p_api_version IN NUMBER,
256 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
257 p_commit IN VARCHAR2 := FND_API.G_FALSE,
258 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
259 x_return_status OUT NOCOPY VARCHAR2,
260 x_msg_count OUT NOCOPY NUMBER,
261 x_msg_data OUT NOCOPY VARCHAR2,
262 p_Score_rec IN Score_Rec_Type,
263 x_object_version_number OUT NOCOPY NUMBER
264 );
265
266 -- *******************************************************
267 -- Start of Comments
268 -- *******************************************************
269 -- API Name: Delete_Score
270 -- Type : Private
271 -- Pre-Req :
272 -- Parameters:
273 -- IN
274 -- p_api_version IN NUMBER Required
275 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
276 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
277 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
278 -- p_score_ID IN NUMBER
279 -- p_object_version_number IN NUMBER Optional Default = NULL
280 --
281 -- OUT:
282 -- x_return_status OUT VARCHAR2
283 -- x_msg_count OUT NUMBER
284 -- x_msg_data OUT VARCHAR2
285 -- Version : Current version 1.0
286 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
287 -- and basic operation, developer must manually add parameters and business logic as necessary.
288 --
289 -- End of Comments
290 --
291 PROCEDURE Delete_Score(
292 p_api_version IN NUMBER,
293 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
294 p_commit IN VARCHAR2 := FND_API.G_FALSE,
295 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
296 x_return_status OUT NOCOPY VARCHAR2,
297 x_msg_count OUT NOCOPY NUMBER,
298 x_msg_data OUT NOCOPY VARCHAR2,
299 p_score_id IN NUMBER,
300 p_object_version_number IN NUMBER
301 );
302
303
304 -- Start of Comments
305 --
306 -- Record level validation procedures
307 --
308 -- p_validation_mode is a constant defined in AMS_UTILITY_PVT package
309 -- For create: G_CREATE, for update: G_UPDATE
310 -- Note: 1. This is automated generated item level validation procedure.
311 -- The actual validation detail is needed to be added.
312 -- 2. Developer can manually added inter-field level validation.
313 -- End of Comments
314
315 PROCEDURE Validate_Score_rec(
316 p_api_version IN NUMBER,
317 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
318 p_validation_mode IN VARCHAR2,
319 x_return_status OUT NOCOPY VARCHAR2,
320 x_msg_count OUT NOCOPY NUMBER,
321 x_msg_data OUT NOCOPY VARCHAR2,
322 p_Score_rec IN Score_Rec_Type
323 );
324
325 -- Start of Comments
326 --
327 -- validation procedures
328 --
329 -- p_validation_mode is a constant defined in AMS_UTILITY_PVT package
330 -- For create: G_CREATE, for update: G_UPDATE
331 -- Note: 1. This is automated generated item level validation procedure.
332 -- The actual validation detail is needed to be added.
333 -- 2. We can also validate table instead of record. There will be an option for user to choose.
334 -- End of Comments
335
336 PROCEDURE Validate_Score(
337 p_api_version IN NUMBER,
338 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
339 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
340 p_validation_mode IN VARCHAR2,
341 p_Score_rec IN Score_Rec_Type,
342 x_return_status OUT NOCOPY VARCHAR2,
343 x_msg_count OUT NOCOPY NUMBER,
344 x_msg_data OUT NOCOPY VARCHAR2
345 );
346
347
348 --
349 -- Purpose
350 -- Allow Workflow to revert the scoring run's status
351 -- to the original status with which the score started.
352 --
353 PROCEDURE wf_revert (
354 p_score_id IN NUMBER,
355 p_status_code IN VARCHAR2,
356 x_return_status OUT NOCOPY VARCHAR2
357 );
358
359
360 PROCEDURE process_score_success (
361 p_score_id IN NUMBER,
362 p_status_code IN VARCHAR2,
363 x_return_status OUT NOCOPY VARCHAR2
364 );
365
366
367 --
368 -- Purpose
369 -- Submit a concurrent request to initiate the preview process
370 --
371 -- Parameters
372 -- p_model_id - ID of the model to preview
373 -- x_request_id - ID of the concurrent request
374 -- x_return_status - standard return status
375 --
376 PROCEDURE handle_preview_request (
377 p_score_id IN NUMBER,
378 x_request_id OUT NOCOPY NUMBER,
379 x_return_status OUT NOCOPY VARCHAR2
380 );
381
382 -- History
383 -- 04-Oct-2002 nyostos Created.
384 -- Overloaded procedure. New implementation in 11.5.9 to start
385 -- the Build/Score/Preview Workflow process to handle Preview instead of
386 -- starting the AMS_DM_PREVIEW concurrent program.
387
388 PROCEDURE handle_preview_request (
389 p_score_id IN NUMBER,
390 x_monitor_url OUT NOCOPY VARCHAR2,
391 x_return_status OUT NOCOPY VARCHAR2
392 );
393
394 --
395 -- Purpose
396 -- Copy parameters of a scoring run.
397 -- Parameters
398 -- p_source_object_id - ID of the copy source.
399 -- p_attributes_table - table of attributes to indicate the different
400 -- attributes of the source object to copy.
401 -- p_copy_columns_table - table of column name and value pairs to
402 -- indicate the values to replace the respective columns.
403 -- x_new_object_id - ID for the newly created object.
404 -- x_custom_setup_id - ID of the custom setup which is associated to
405 -- the new object. The ID is used by the UI to render the detail
406 -- page which follows a copy operation.
407 PROCEDURE copy_score (
408 p_api_version IN NUMBER,
409 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
410 p_commit IN VARCHAR2 := FND_API.G_FALSE,
411 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
412 x_return_status OUT NOCOPY VARCHAR2,
413 x_msg_count OUT NOCOPY NUMBER,
414 x_msg_data OUT NOCOPY VARCHAR2,
415 p_source_object_id IN NUMBER,
416 p_attributes_table IN AMS_CpyUtility_PVT.copy_attributes_table_type,
417 p_copy_columns_table IN AMS_CpyUtility_PVT.copy_columns_table_type,
418 x_new_object_id OUT NOCOPY NUMBER,
419 x_custom_setup_id OUT NOCOPY NUMBER
420 );
421
422
423 --
424 -- Purpose
425 -- Validate model status and update scoring run status before
426 -- the actual scoring is executed.
427 -- Parameters
428 -- p_score_id - ID of the scoring run instance.
429 -- x_status_code - the status set for the scoring run. If the scoring run
430 -- cannot be started, then the status is set to DRAFT, set the status
431 -- to SCORING, otherwise.
432 PROCEDURE wf_score (
433 p_score_id IN NUMBER,
434 x_status_code OUT NOCOPY VARCHAR2,
435 x_return_status OUT NOCOPY VARCHAR2
436 );
437
438 -- History
439 -- 04-Oct-2002 nyostos Created.
440 -- Cancels the Score workflow process. If the Scoring Run is in SCHEDULED.
441 -- state (i.e. the first step in the WF process has not started yet),
442 -- the Scoring Run status will be reverted to its previous status.
443 -- If the Scoring status is SCORING (i.e. the Workflow process is in progress),
444 -- then the Scoring Run status will be set to DRAFT.
445
446 PROCEDURE cancel_run_request (
447 p_score_id IN NUMBER,
448 x_return_status OUT NOCOPY VARCHAR2
449 );
450
451 --
452 -- Purpose
453 -- Checks whether a model is still AVAILABLE for scoring
454 -- History
455 -- 09-Oct-2002 nyostos Created.
456 PROCEDURE wf_checkModelStatus (
457 p_score_id IN NUMBER,
458 x_return_status OUT NOCOPY VARCHAR2,
459 x_model_status OUT NOCOPY VARCHAR2
460 );
461
462 --
463 -- Purpose
464 -- Procedure to handle data selection changes
465 -- This would INVALIDate a COMPLETED Scoring Run.
466 --
467 -- History
468 -- 14-Oct-2002 nyostos Created.
469 PROCEDURE handle_data_selection_changes(
470 p_score_id IN NUMBER
471 );
472
473 End ams_dm_score_pvt;