[Home] [Help]
PACKAGE BODY: APPS.AMS_DM_MODELS_B_PKG
Source
1 PACKAGE BODY ams_dm_models_b_PKG as
2 /* $Header: amstdmmb.pls 115.18 2002/12/09 11:07:06 choang noship $ */
3 -- Start of Comments
4 -- Package name : ams_dm_models_b_PKG
5 -- Purpose : PACKAGE BODY FOR TABLE HANDLER
6 -- History : 11/10/00 JIE LI CREATED
7 -- 26-Jan-2001 choang Removed increment of object ver num in update.
8 -- 02-Feb-2001 choang Update was not taking object ver num from param.
9 -- 08-Feb-2001 choang Changed all IN/OUT params to IN.
10 -- 16-Feb-2001 choang Replaced top_down_flag with row_selection_type.
11 -- 23-Feb-2001 choang Defaulted row_selection_type to STANDARD.
12 -- 26-Feb-2001 choang Added custom_setup_id, country_id, best_subtree
13 -- 08-Mar-2001 choang Added wf_itemkey
14 -- 18-Mar-2001 choang Added add_language, load_row, translate_row; changed
15 -- obj ver logic in update.
16 -- 01-Feb-2002 choang Removed created by in update api
17 -- 18-Mar-2002 choang Added checkfile to dbdrv
18 -- 23-Apr-2002 choang Added target_id
19 -- NOTE :
20 -- End of Comments
21
22
23 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ams_dm_models_b_PKG';
24 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstmmsb.pls';
25
26 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
27 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
28 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
29
30 PROCEDURE Insert_Row(
31 p_model_id IN NUMBER,
32 p_last_update_date DATE,
33 p_last_updated_by NUMBER,
34 p_creation_date DATE,
35 p_created_by NUMBER,
36 p_last_update_login NUMBER,
37 p_object_version_number IN NUMBER,
38 p_model_type VARCHAR2,
39 p_user_status_id NUMBER,
40 p_status_code IN VARCHAR2,
41 p_status_date DATE,
42 p_last_build_date DATE,
43 p_owner_user_id NUMBER,
44 p_performance NUMBER,
45 p_target_group_type VARCHAR2,
46 p_darwin_model_ref VARCHAR2,
47 p_model_name VARCHAR2,
48 p_description VARCHAR2,
49 p_scheduled_date DATE,
50 p_scheduled_timezone_id NUMBER,
51 p_expiration_date DATE,
52 p_results_flag VARCHAR2,
53 p_logs_flag VARCHAR2,
54 p_target_field VARCHAR2,
55 p_target_type VARCHAR2,
56 p_target_positive_value VARCHAR2,
57 p_total_records NUMBER,
58 p_total_positives NUMBER,
59 p_min_records NUMBER,
60 p_max_records NUMBER,
61 p_row_selection_type VARCHAR2,
62 p_every_nth_row NUMBER,
63 p_pct_random NUMBER,
64 p_best_subtree NUMBER,
65 p_custom_setup_id NUMBER,
66 p_country_id NUMBER,
67 p_wf_itemkey VARCHAR2,
68 p_target_id NUMBER,
69 p_attribute_category VARCHAR2,
70 p_attribute1 VARCHAR2,
71 p_attribute2 VARCHAR2,
72 p_attribute3 VARCHAR2,
73 p_attribute4 VARCHAR2,
74 p_attribute5 VARCHAR2,
75 p_attribute6 VARCHAR2,
76 p_attribute7 VARCHAR2,
77 p_attribute8 VARCHAR2,
78 p_attribute9 VARCHAR2,
79 p_attribute10 VARCHAR2,
80 p_attribute11 VARCHAR2,
81 p_attribute12 VARCHAR2,
82 p_attribute13 VARCHAR2,
83 p_attribute14 VARCHAR2,
84 p_attribute15 VARCHAR2
85 )
86 IS
87 L_DEFAULT_SELECTION_TYPE CONSTANT VARCHAR2(30) := 'STANDARD';
88
89 BEGIN
90 INSERT INTO ams_dm_models_all_b(
91 model_id,
92 last_update_date,
93 last_updated_by,
94 creation_date,
95 created_by,
96 last_update_login,
97 object_version_number,
98 model_type,
99 user_status_id,
100 status_code,
101 status_date,
102 last_build_date,
103 owner_user_id,
104 performance,
105 target_group_type,
106 darwin_model_ref,
107 scheduled_date,
108 scheduled_timezone_id,
109 expiration_date,
110 results_flag,
111 logs_flag,
112 target_field,
113 target_type,
114 target_positive_value,
115 total_records,
116 total_positives,
117 min_records,
118 max_records,
119 row_selection_type,
120 every_nth_row,
121 pct_random,
122 best_subtree,
123 custom_setup_id,
124 country_id,
125 wf_itemkey,
126 target_id,
127 attribute_category,
128 attribute1 ,
129 attribute2 ,
130 attribute3 ,
131 attribute4 ,
132 attribute5 ,
133 attribute6 ,
134 attribute7 ,
135 attribute8 ,
136 attribute9 ,
137 attribute10,
138 attribute11,
139 attribute12,
140 attribute13 ,
141 attribute14 ,
142 attribute15
143 ) VALUES (
144 DECODE( p_MODEL_ID, FND_API.G_MISS_NUM, NULL, p_MODEL_ID),
145 DECODE( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, p_LAST_UPDATE_DATE),
146 DECODE( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
147 DECODE( p_CREATION_DATE, FND_API.G_MISS_DATE, NULL, p_CREATION_DATE),
148 DECODE( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
149 DECODE( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
150 DECODE( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, 1, p_OBJECT_VERSION_NUMBER),
151 DECODE( p_MODEL_TYPE, FND_API.G_MISS_CHAR, NULL, p_MODEL_TYPE),
152 DECODE( p_USER_STATUS_ID, FND_API.G_MISS_NUM, NULL, p_USER_STATUS_ID),
153 DECODE( p_STATUS_CODE, FND_API.G_MISS_CHAR, NULL, p_STATUS_CODE),
154 DECODE( p_STATUS_DATE, FND_API.G_MISS_CHAR, NULL, p_STATUS_DATE),
155 DECODE( p_LAST_BUILD_DATE, FND_API.G_MISS_DATE, NULL, p_LAST_BUILD_DATE),
156 DECODE( p_OWNER_USER_ID, FND_API.G_MISS_NUM, NULL, p_OWNER_USER_ID),
157 DECODE( p_PERFORMANCE, FND_API.G_MISS_NUM, NULL, p_PERFORMANCE),
158 DECODE( p_TARGET_GROUP_TYPE, FND_API.G_MISS_CHAR, NULL, p_TARGET_GROUP_TYPE),
159 DECODE( p_DARWIN_MODEL_REF, FND_API.G_MISS_CHAR, NULL, p_DARWIN_MODEL_REF),
160 DECODE( p_SCHEDULED_DATE, FND_API.G_MISS_DATE,NULL, p_SCHEDULED_DATE),
161 DECODE( p_SCHEDULED_TIMEZONE_ID, FND_API.G_MISS_NUM, NULL, p_SCHEDULED_TIMEZONE_ID),
162 DECODE( p_EXPIRATION_DATE, FND_API.G_MISS_DATE,NULL, p_EXPIRATION_DATE),
163 DECODE( p_RESULTS_FLAG, FND_API.G_MISS_CHAR, 'N', p_RESULTS_FLAG),
164 DECODE( p_LOGS_FLAG, FND_API.g_miss_char, 'N', p_LOGS_FLAG),
165 DECODE( p_TARGET_FIELD, FND_API.g_miss_char, NULL, p_TARGET_FIELD),
166 DECODE( p_TARGET_TYPE, FND_API.g_miss_char, NULL, p_TARGET_TYPE),
167 DECODE( p_TARGET_POSITIVE_VALUE, FND_API.g_miss_char, NULL, p_TARGET_POSITIVE_VALUE),
168 DECODE( p_TOTAL_RECORDS, FND_API.g_miss_num, NULL, p_TOTAL_RECORDS),
169 DECODE( p_TOTAL_POSITIVES, FND_API.g_miss_num, NULL, p_TOTAL_POSITIVES),
170 DECODE( p_MIN_RECORDS, FND_API.g_miss_num, NULL, p_MIN_RECORDS),
171 DECODE( p_MAX_RECORDS, FND_API.g_miss_num, NULL, p_MAX_RECORDS),
172 DECODE( p_row_selection_type, FND_API.g_miss_char, L_DEFAULT_SELECTION_TYPE, p_row_selection_type),
173 DECODE( p_EVERY_NTH_ROW, FND_API.g_miss_num, NULL, p_EVERY_NTH_ROW),
174 DECODE( p_PCT_RANDOM, FND_API.g_miss_num, NULL, p_PCT_RANDOM),
175 DECODE( p_best_subtree, FND_API.g_miss_num, NULL, p_best_subtree),
176 DECODE( p_custom_setup_id, FND_API.g_miss_num, NULL, p_custom_setup_id),
177 DECODE( p_country_id, FND_API.g_miss_num, NULL, p_country_id),
178 DECODE( p_wf_itemkey, FND_API.G_MISS_CHAR, NULL, p_wf_itemkey),
179 DECODE( p_target_id, FND_API.G_MISS_CHAR, NULL, p_target_id),
180 DECODE( p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE_CATEGORY),
181 DECODE( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1),
182 DECODE( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2),
183 DECODE( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3),
184 DECODE( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4),
185 DECODE( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5),
186 DECODE( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6),
187 DECODE( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7),
188 DECODE( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8),
189 DECODE( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9),
190 DECODE( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10),
191 DECODE( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11),
192 DECODE( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12),
193 DECODE( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13),
194 DECODE( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14),
195 DECODE( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15)
196 );
197
198 INSERT INTO ams_dm_models_all_tl(
199 model_id,
200 language,
201 last_update_date,
202 last_updated_by,
203 creation_date,
204 created_by,
205 last_update_login,
206 source_lang,
207 model_name,
208 description
209 )
210 SELECT
211 decode( p_MODEL_ID, FND_API.G_MISS_NUM, NULL, p_MODEL_ID),
212 l.language_code,
213 SYSDATE,
214 FND_GLOBAL.user_id,
215 SYSDATE,
216 FND_GLOBAL.user_id,
217 FND_GLOBAL.conc_login_id,
218 USERENV('LANG'),
219 decode( p_MODEL_NAME, FND_API.G_MISS_CHAR, NULL, p_MODEL_NAME),
220 decode( p_DESCRIPTION, FND_API.G_MISS_CHAR, NULL, p_DESCRIPTION)
221 FROM fnd_languages l
222 WHERE l.installed_flag in ('I', 'B')
223 AND NOT EXISTS(
224 SELECT NULL
225 FROM ams_dm_models_all_tl t
226 WHERE t.model_id = decode( p_MODEL_ID, FND_API.G_MISS_NUM, NULL, p_MODEL_ID)
227 AND t.language = l.language_code );
228
229 End Insert_Row;
230
231 PROCEDURE Update_Row(
232 p_model_id NUMBER,
233 p_last_update_date DATE,
234 p_last_updated_by NUMBER,
235 p_last_update_login NUMBER,
236 p_object_version_number NUMBER,
237 p_model_type VARCHAR2,
238 p_user_status_id NUMBER,
239 p_status_code VARCHAR2,
240 p_status_date DATE,
241 p_last_build_date DATE,
242 p_owner_user_id NUMBER,
243 p_performance NUMBER,
244 p_target_group_type VARCHAR2,
245 p_darwin_model_ref VARCHAR2,
246 p_model_name VARCHAR2,
247 p_description VARCHAR2,
248 p_scheduled_date DATE,
249 p_scheduled_timezone_id NUMBER,
250 p_expiration_date DATE,
251 p_results_flag VARCHAR2,
252 p_logs_flag VARCHAR2,
253 p_target_field VARCHAR2,
254 p_target_type VARCHAR2,
255 p_target_positive_value VARCHAR2,
256 p_total_records NUMBER,
257 p_total_positives NUMBER,
258 p_min_records NUMBER,
259 p_max_records NUMBER,
260 p_row_selection_type VARCHAR2,
261 p_every_nth_row NUMBER,
262 p_pct_random NUMBER,
263 p_best_subtree NUMBER,
264 p_custom_setup_id NUMBER,
265 p_country_id NUMBER,
266 p_wf_itemkey VARCHAR2,
267 p_target_id NUMBER,
268 p_attribute_category VARCHAR2,
269 p_attribute1 VARCHAR2,
270 p_attribute2 VARCHAR2,
271 p_attribute3 VARCHAR2,
272 p_attribute4 VARCHAR2,
273 p_attribute5 VARCHAR2,
274 p_attribute6 VARCHAR2,
275 p_attribute7 VARCHAR2,
276 p_attribute8 VARCHAR2,
277 p_attribute9 VARCHAR2,
278 p_attribute10 VARCHAR2,
279 p_attribute11 VARCHAR2,
280 p_attribute12 VARCHAR2,
281 p_attribute13 VARCHAR2,
282 p_attribute14 VARCHAR2,
283 p_attribute15 VARCHAR2
284 )
285 IS
286 BEGIN
287 Update ams_dm_models_all_b
288 SET
289 MODEL_ID = decode( p_MODEL_ID, FND_API.G_MISS_NUM, MODEL_ID, p_MODEL_ID),
290 LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
291 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
292 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
293 object_version_number = object_version_number + 1,
294 MODEL_TYPE = decode( p_MODEL_TYPE, FND_API.G_MISS_CHAR, MODEL_TYPE, p_MODEL_TYPE),
295 USER_STATUS_ID = decode( p_USER_STATUS_ID, FND_API.G_MISS_NUM, USER_STATUS_ID, p_USER_STATUS_ID),
296 STATUS_CODE = decode( p_STATUS_CODE, FND_API.G_MISS_CHAR, STATUS_CODE, p_STATUS_CODE),
297 STATUS_DATE = decode( p_STATUS_DATE, FND_API.G_MISS_CHAR, STATUS_DATE, p_STATUS_DATE),
298 LAST_BUILD_DATE = decode( p_LAST_BUILD_DATE, FND_API.G_MISS_DATE, LAST_BUILD_DATE, p_LAST_BUILD_DATE),
299 OWNER_USER_ID = decode( p_OWNER_USER_ID, FND_API.G_MISS_NUM, OWNER_USER_ID, p_OWNER_USER_ID),
303 SCHEDULED_DATE = decode( p_SCHEDULED_DATE, FND_API.G_MISS_DATE,SCHEDULED_DATE, p_SCHEDULED_DATE),
300 PERFORMANCE = decode( p_PERFORMANCE, FND_API.G_MISS_NUM, PERFORMANCE, p_PERFORMANCE),
301 TARGET_GROUP_TYPE = decode( p_TARGET_GROUP_TYPE, FND_API.G_MISS_CHAR, TARGET_GROUP_TYPE, p_TARGET_GROUP_TYPE),
302 DARWIN_MODEL_REF = decode( p_DARWIN_MODEL_REF, FND_API.G_MISS_CHAR, DARWIN_MODEL_REF, p_DARWIN_MODEL_REF),
304 SCHEDULED_TIMEZONE_ID = decode( p_SCHEDULED_TIMEZONE_ID, FND_API.G_MISS_NUM, SCHEDULED_TIMEZONE_ID, p_SCHEDULED_TIMEZONE_ID),
305 EXPIRATION_DATE = decode( p_EXPIRATION_DATE, FND_API.G_MISS_DATE,EXPIRATION_DATE, p_EXPIRATION_DATE),
306 RESULTS_FLAG = decode( p_RESULTS_FLAG, FND_API.G_MISS_CHAR,RESULTS_FLAG, p_RESULTS_FLAG),
307 LOGS_FLAG = decode( p_LOGS_FLAG, FND_API.g_miss_char, LOGS_FLAG, p_LOGS_FLAG),
308 TARGET_FIELD = decode( p_TARGET_FIELD, FND_API.g_miss_char, TARGET_FIELD, p_TARGET_FIELD),
309 TARGET_TYPE = decode( p_TARGET_TYPE, FND_API.g_miss_char, TARGET_TYPE, p_TARGET_TYPE),
310 TARGET_POSITIVE_VALUE = decode( p_TARGET_POSITIVE_VALUE, FND_API.g_miss_char, TARGET_POSITIVE_VALUE, p_TARGET_POSITIVE_VALUE),
311 TOTAL_RECORDS = decode( p_TOTAL_RECORDS, FND_API.g_miss_num, TOTAL_RECORDS, p_TOTAL_RECORDS),
312 TOTAL_POSITIVES = decode( p_TOTAL_POSITIVES, FND_API.g_miss_num, TOTAL_POSITIVES, p_TOTAL_POSITIVES),
313 MIN_RECORDS = decode( p_MIN_RECORDS, FND_API.g_miss_num, MIN_RECORDS, p_MIN_RECORDS),
314 MAX_RECORDS = decode( p_MAX_RECORDS, FND_API.g_miss_num, MAX_RECORDS, p_MAX_RECORDS),
315 row_selection_type = decode( p_row_selection_type, FND_API.g_miss_char, row_selection_type, p_row_selection_type),
316 EVERY_NTH_ROW = decode( p_EVERY_NTH_ROW, FND_API.g_miss_num, EVERY_NTH_ROW, p_EVERY_NTH_ROW),
317 PCT_RANDOM = decode( p_PCT_RANDOM, FND_API.g_miss_num, PCT_RANDOM, p_PCT_RANDOM),
318 best_subtree = DECODE( p_best_subtree, FND_API.g_miss_num, best_subtree, p_best_subtree),
319 custom_setup_id = DECODE( p_custom_setup_id, FND_API.g_miss_num, custom_setup_id, p_custom_setup_id),
320 country_id = DECODE( p_country_id, FND_API.g_miss_num, country_id, p_country_id),
321 wf_itemkey = decode( p_wf_itemkey, FND_API.g_miss_char, wf_itemkey, p_wf_itemkey),
322 target_id = decode( p_target_id, FND_API.g_miss_char, target_id, p_target_id),
323 ATTRIBUTE_CATEGORY = decode( p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR,ATTRIBUTE_CATEGORY, p_ATTRIBUTE_CATEGORY),
324 ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR,ATTRIBUTE1, p_ATTRIBUTE1),
325 ATTRIBUTE2 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR,ATTRIBUTE2, p_ATTRIBUTE2),
326 ATTRIBUTE3 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR,ATTRIBUTE3, p_ATTRIBUTE3),
327 ATTRIBUTE4 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR,ATTRIBUTE4, p_ATTRIBUTE4),
328 ATTRIBUTE5 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR,ATTRIBUTE5, p_ATTRIBUTE5),
329 ATTRIBUTE6 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR,ATTRIBUTE6, p_ATTRIBUTE6),
330 ATTRIBUTE7 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR,ATTRIBUTE7, p_ATTRIBUTE7),
331 ATTRIBUTE8 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR,ATTRIBUTE8, p_ATTRIBUTE8),
332 ATTRIBUTE9 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR,ATTRIBUTE9, p_ATTRIBUTE9),
333 ATTRIBUTE10 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR,ATTRIBUTE10, p_ATTRIBUTE10),
334 ATTRIBUTE11 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR,ATTRIBUTE11, p_ATTRIBUTE11),
335 ATTRIBUTE12 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR,ATTRIBUTE12, p_ATTRIBUTE12),
336 ATTRIBUTE13 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR,ATTRIBUTE13, p_ATTRIBUTE13),
337 ATTRIBUTE14 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR,ATTRIBUTE14, p_ATTRIBUTE14),
338 ATTRIBUTE15 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR,ATTRIBUTE15, p_ATTRIBUTE15)
339 WHERE MODEL_ID = p_MODEL_ID
340 AND object_version_number = p_object_version_number;
341 IF SQL%NOTFOUND THEN
342 -- the calling program should catch no_data_found
343 -- and treat it as a mismatch in object version
344 -- number.
345 RAISE NO_DATA_FOUND;
346 END IF;
347
348 update ams_dm_models_all_tl set
349 model_name = decode( p_MODEL_NAME, FND_API.G_MISS_CHAR, MODEL_NAME, p_MODEL_NAME),
350 description = decode( p_DESCRIPTION, FND_API.G_MISS_CHAR, DESCRIPTION, p_DESCRIPTION),
351 last_update_date = SYSDATE,
352 last_updated_by = FND_GLOBAL.user_id,
353 last_update_login = FND_GLOBAL.conc_login_id,
354 source_lang = USERENV('LANG')
355 WHERE model_id = p_MODEL_ID
356 AND USERENV('LANG') IN (language, source_lang);
357
358 END Update_Row;
359
360 PROCEDURE Delete_Row(
361 p_MODEL_ID NUMBER)
362 IS
363 BEGIN
364 DELETE FROM ams_dm_models_all_b
365 WHERE MODEL_ID = p_MODEL_ID;
366 If (SQL%NOTFOUND) then
367 RAISE NO_DATA_FOUND;
368 End If;
369
370 DELETE FROM ams_dm_models_all_tl
371 WHERE MODEL_ID = p_MODEL_ID;
372 If (SQL%NOTFOUND) then
373 RAISE NO_DATA_FOUND;
374 End If;
375
376 END Delete_Row;
377
378 PROCEDURE Lock_Row(
379 p_MODEL_ID NUMBER,
380 p_LAST_UPDATE_DATE DATE,
381 p_LAST_UPDATED_BY NUMBER,
382 p_CREATION_DATE DATE,
383 p_CREATED_BY NUMBER,
384 p_LAST_UPDATE_LOGIN NUMBER,
385 p_OBJECT_VERSION_NUMBER NUMBER,
386 p_MODEL_TYPE VARCHAR2,
387 p_USER_STATUS_ID NUMBER,
388 p_STATUS_CODE VARCHAR2,
389 p_STATUS_DATE DATE,
390 p_LAST_BUILD_DATE DATE,
391 p_OWNER_USER_ID NUMBER,
392 p_PERFORMANCE NUMBER,
393 p_TARGET_GROUP_TYPE VARCHAR2,
394 p_DARWIN_MODEL_REF VARCHAR2,
395 p_SCHEDULED_DATE DATE,
396 p_SCHEDULED_TIMEZONE_ID NUMBER,
397 p_EXPIRATION_DATE DATE,
398 p_RESULTS_FLAG VARCHAR2,
399 p_ATTRIBUTE_CATEGORY VARCHAR2,
400 p_ATTRIBUTE1 VARCHAR2,
401 p_ATTRIBUTE2 VARCHAR2,
402 p_ATTRIBUTE3 VARCHAR2,
403 p_ATTRIBUTE4 VARCHAR2,
407 p_ATTRIBUTE8 VARCHAR2,
404 p_ATTRIBUTE5 VARCHAR2,
405 p_ATTRIBUTE6 VARCHAR2,
406 p_ATTRIBUTE7 VARCHAR2,
408 p_ATTRIBUTE9 VARCHAR2,
409 p_ATTRIBUTE10 VARCHAR2,
410 p_ATTRIBUTE11 VARCHAR2,
411 p_ATTRIBUTE12 VARCHAR2,
412 p_ATTRIBUTE13 VARCHAR2,
413 p_ATTRIBUTE14 VARCHAR2,
414 p_ATTRIBUTE15 VARCHAR2
415 )
416
417 IS
418 CURSOR C IS
419 SELECT *
420 FROM ams_dm_models_all_b
421 WHERE MODEL_ID = p_MODEL_ID
422 FOR UPDATE of MODEL_ID NOWAIT;
423 Recinfo C%ROWTYPE;
424 BEGIN
425 OPEN C;
426 FETCH C INTO Recinfo;
427 If (C%NOTFOUND) then
428 CLOSE C;
429 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
430 APP_EXCEPTION.RAISE_EXCEPTION;
431 End If;
432 CLOSE C;
433 if (
434 ( Recinfo.MODEL_ID = p_MODEL_ID)
435 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
436 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
437 AND ( p_LAST_UPDATE_DATE IS NULL )))
438 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
439 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
440 AND ( p_LAST_UPDATED_BY IS NULL )))
441 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
442 OR ( ( Recinfo.CREATION_DATE IS NULL )
443 AND ( p_CREATION_DATE IS NULL )))
444 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
445 OR ( ( Recinfo.CREATED_BY IS NULL )
446 AND ( p_CREATED_BY IS NULL )))
447 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
448 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
449 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
450 AND ( ( Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
451 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
452 AND ( p_OBJECT_VERSION_NUMBER IS NULL )))
453 AND ( ( Recinfo.MODEL_TYPE = p_MODEL_TYPE)
454 OR ( ( Recinfo.MODEL_TYPE IS NULL )
455 AND ( p_MODEL_TYPE IS NULL )))
456 AND ( ( Recinfo.USER_STATUS_ID = p_USER_STATUS_ID)
457 OR ( ( Recinfo.USER_STATUS_ID IS NULL )
458 AND ( p_USER_STATUS_ID IS NULL )))
459 AND ( ( Recinfo.STATUS_CODE = p_STATUS_CODE)
460 OR ( ( Recinfo.STATUS_CODE IS NULL )
461 AND ( p_STATUS_CODE IS NULL )))
462 AND ( ( Recinfo.STATUS_DATE = p_STATUS_DATE)
463 OR ( ( Recinfo.STATUS_DATE IS NULL )
464 AND ( p_STATUS_DATE IS NULL )))
465 AND ( ( Recinfo.LAST_BUILD_DATE = p_LAST_BUILD_DATE)
466 OR ( ( Recinfo.LAST_BUILD_DATE IS NULL )
467 AND ( p_LAST_BUILD_DATE IS NULL )))
468 AND ( ( Recinfo.OWNER_USER_ID = p_OWNER_USER_ID)
469 OR ( ( Recinfo.OWNER_USER_ID IS NULL )
470 AND ( p_OWNER_USER_ID IS NULL )))
471 AND ( ( Recinfo.PERFORMANCE = p_PERFORMANCE)
472 OR ( ( Recinfo.PERFORMANCE IS NULL )
473 AND ( p_PERFORMANCE IS NULL )))
474 AND ( ( Recinfo.TARGET_GROUP_TYPE = p_TARGET_GROUP_TYPE)
475 OR ( ( Recinfo.TARGET_GROUP_TYPE IS NULL )
476 AND ( p_TARGET_GROUP_TYPE IS NULL )))
477 AND ( ( Recinfo.DARWIN_MODEL_REF = p_DARWIN_MODEL_REF)
478 OR ( ( Recinfo.DARWIN_MODEL_REF IS NULL )
479 AND ( p_DARWIN_MODEL_REF IS NULL )))
480 ) then
481 return;
482 else
483 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
484 APP_EXCEPTION.RAISE_EXCEPTION;
485 End If;
486 END Lock_Row;
487
488
489 PROCEDURE add_language
490 IS
491 BEGIN
492 delete from AMS_DM_MODELS_ALL_TL T
493 where not exists
494 (select NULL
495 from AMS_DM_MODELS_ALL_B B
496 where B.MODEL_ID = T.MODEL_ID
497 );
498
499 update AMS_DM_MODELS_ALL_TL T set (
500 MODEL_NAME,
501 DESCRIPTION
502 ) = (select
503 B.MODEL_NAME,
504 B.DESCRIPTION
505 from AMS_DM_MODELS_ALL_TL B
506 where B.MODEL_ID = T.MODEL_ID
507 and B.LANGUAGE = T.SOURCE_LANG)
508 where (
509 T.MODEL_ID,
510 T.LANGUAGE
511 ) in (select
512 SUBT.MODEL_ID,
513 SUBT.LANGUAGE
514 from AMS_DM_MODELS_ALL_TL SUBB, AMS_DM_MODELS_ALL_TL SUBT
515 where SUBB.MODEL_ID = SUBT.MODEL_ID
516 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
517 and (SUBB.MODEL_NAME <> SUBT.MODEL_NAME
518 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
519 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
520 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
521 ));
522
523 insert into AMS_DM_MODELS_ALL_TL (
524 MODEL_ID,
525 LAST_UPDATE_DATE,
526 LAST_UPDATED_BY,
527 CREATION_DATE,
528 CREATED_BY,
529 LAST_UPDATE_LOGIN,
530 MODEL_NAME,
531 DESCRIPTION,
532 LANGUAGE,
533 SOURCE_LANG
534 ) select /*+ ORDERED */
535 B.MODEL_ID,
536 B.LAST_UPDATE_DATE,
537 B.LAST_UPDATED_BY,
538 B.CREATION_DATE,
539 B.CREATED_BY,
540 B.LAST_UPDATE_LOGIN,
541 B.MODEL_NAME,
542 B.DESCRIPTION,
543 L.LANGUAGE_CODE,
544 B.SOURCE_LANG
545 from AMS_DM_MODELS_ALL_TL B, FND_LANGUAGES L
546 where L.INSTALLED_FLAG in ('I', 'B')
547 and B.LANGUAGE = userenv('LANG')
548 and not exists
549 (select NULL
550 from AMS_DM_MODELS_ALL_TL T
551 where T.MODEL_ID = B.MODEL_ID
555 PROCEDURE translate_row (
552 and T.LANGUAGE = L.LANGUAGE_CODE);
553 END add_language;
554
556 x_model_id IN NUMBER,
557 x_model_name IN VARCHAR2,
558 x_description IN VARCHAR2,
559 x_owner IN VARCHAR2
560 )
561 IS
562 BEGIN
563 update ams_dm_models_all_tl set
564 model_name = nvl(x_model_name, model_name),
565 description = nvl(x_description, description),
566 source_lang = userenv('LANG'),
567 last_update_date = sysdate,
568 last_updated_by = decode(x_owner, 'SEED', 1, 0),
569 last_update_login = 0
570 where model_id = x_model_id
571 and userenv('LANG') in (language, source_lang);
572 end TRANSLATE_ROW;
573
574
575 PROCEDURE load_row (
576 x_model_id IN NUMBER,
577 x_model_type VARCHAR2,
578 x_user_status_id NUMBER,
579 x_status_code IN VARCHAR2,
580 x_status_date DATE,
581 x_last_build_date DATE,
582 x_owner_user_id NUMBER,
583 x_performance NUMBER,
584 x_target_group_type VARCHAR2,
585 x_darwin_model_ref VARCHAR2,
586 x_model_name VARCHAR2,
587 x_description VARCHAR2,
588 x_scheduled_date DATE,
589 x_scheduled_timezone_id NUMBER,
590 x_expiration_date DATE,
591 x_results_flag VARCHAR2,
592 x_logs_flag VARCHAR2,
593 x_target_field VARCHAR2,
594 x_target_type VARCHAR2,
595 x_target_positive_value VARCHAR2,
596 x_total_records NUMBER,
597 x_total_positives NUMBER,
598 x_min_records NUMBER,
599 x_max_records NUMBER,
600 x_row_selection_type VARCHAR2,
601 x_every_nth_row NUMBER,
602 x_pct_random NUMBER,
603 x_best_subtree NUMBER,
604 x_custom_setup_id NUMBER,
605 x_country_id NUMBER,
606 x_wf_itemkey VARCHAR2,
607 x_target_id NUMBER,
608 x_attribute_category VARCHAR2,
609 x_attribute1 VARCHAR2,
610 x_attribute2 VARCHAR2,
611 x_attribute3 VARCHAR2,
612 x_attribute4 VARCHAR2,
613 x_attribute5 VARCHAR2,
614 x_attribute6 VARCHAR2,
615 x_attribute7 VARCHAR2,
616 x_attribute8 VARCHAR2,
617 x_attribute9 VARCHAR2,
618 x_attribute10 VARCHAR2,
619 x_attribute11 VARCHAR2,
620 x_attribute12 VARCHAR2,
621 x_attribute13 VARCHAR2,
622 x_attribute14 VARCHAR2,
623 x_attribute15 VARCHAR2,
624 x_owner VARCHAR2
625 )
626 IS
627 l_user_id number := 0;
628 l_obj_verno number;
629 l_dummy_char varchar2(1);
630 l_row_id varchar2(100);
631 l_model_id number;
632
633 cursor c_obj_verno is
634 select object_version_number
635 from ams_dm_models_all_b
636 where model_id = x_model_id;
637
638 cursor c_chk_model_exists is
639 select 'x'
640 from ams_dm_models_all_b
641 where model_id = x_model_id;
642
643 cursor c_get_model_id is
644 select ams_dm_models_all_b_s.nextval
645 from dual;
646 BEGIN
647 if X_OWNER = 'SEED' then
648 l_user_id := 1;
649 end if;
650
651 open c_chk_model_exists;
652 fetch c_chk_model_exists into l_dummy_char;
653 if c_chk_model_exists%notfound THEN
654 if x_model_id is null then
655 open c_get_model_id;
656 fetch c_get_model_id into l_model_id;
657 close c_get_model_id;
658 else
659 l_model_id := x_model_id;
660 end if;
661 l_obj_verno := 1;
662 ams_dm_models_b_pkg.INSERT_ROW (
663 p_model_id => l_model_id,
664 p_last_update_date => SYSDATE,
665 p_last_updated_by => l_user_id,
666 p_creation_date => SYSDATE,
667 p_created_by => l_user_id,
668 p_last_update_login => 0,
669 p_object_version_number => l_obj_verno,
670 p_model_type => x_model_type,
671 p_user_status_id => x_user_status_id,
672 p_status_code => x_status_code,
673 p_status_date => x_status_date,
674 p_last_build_date => x_last_build_date,
675 p_owner_user_id => x_owner_user_id,
676 p_scheduled_date => x_scheduled_date,
677 p_scheduled_timezone_id => x_scheduled_timezone_id,
678 p_expiration_date => x_expiration_date,
679 p_custom_setup_id => x_custom_setup_id,
680 p_country_id => x_country_id,
681 p_results_flag => x_results_flag,
682 p_logs_flag => x_logs_flag,
683 p_total_records => x_total_records,
684 p_total_positives => x_total_positives,
685 p_target_field => x_target_field,
686 p_target_type => x_target_type,
687 p_target_positive_value => x_target_positive_value,
688 p_min_records => x_min_records,
689 p_max_records => x_max_records,
690 p_row_selection_type => x_row_selection_type,
691 p_every_nth_row => x_every_nth_row,
692 p_pct_random => x_pct_random,
693 p_performance => x_performance,
694 p_target_group_type => x_target_group_type,
695 p_best_subtree => x_best_subtree,
696 p_wf_itemkey => x_wf_itemkey,
697 p_target_id => x_target_id,
698 p_darwin_model_ref => x_darwin_model_ref,
699 p_attribute_category => x_attribute_category,
700 p_attribute1 => x_attribute1,
701 p_attribute2 => x_attribute2,
705 p_attribute6 => x_attribute6,
702 p_attribute3 => x_attribute3,
703 p_attribute4 => x_attribute4,
704 p_attribute5 => x_attribute5,
706 p_attribute7 => x_attribute7,
707 p_attribute8 => x_attribute8,
708 p_attribute9 => x_attribute9,
709 p_attribute10 => x_attribute10,
710 p_attribute11 => x_attribute11,
711 p_attribute12 => x_attribute12,
712 p_attribute13 => x_attribute13,
713 p_attribute14 => x_attribute14,
714 p_attribute15 => x_attribute15,
715 p_model_name => x_model_name,
716 p_description => x_description
717 );
718 else
719 open c_obj_verno;
720 fetch c_obj_verno into l_obj_verno;
721 close c_obj_verno;
722 ams_dm_models_b_pkg.UPDATE_ROW (
723 p_model_id => x_model_id,
724 p_last_update_date => SYSDATE,
725 p_last_updated_by => l_user_id,
726 p_last_update_login => 0,
727 p_object_version_number => l_obj_verno,
728 p_model_type => x_model_type,
729 p_user_status_id => x_user_status_id,
730 p_status_code => x_status_code,
731 p_status_date => x_status_date,
732 p_last_build_date => x_last_build_date,
733 p_owner_user_id => x_owner_user_id,
734 p_scheduled_date => x_scheduled_date,
735 p_scheduled_timezone_id => x_scheduled_timezone_id,
736 p_expiration_date => x_expiration_date,
737 p_custom_setup_id => x_custom_setup_id,
738 p_country_id => x_country_id,
739 p_results_flag => x_results_flag,
740 p_logs_flag => x_logs_flag,
741 p_total_records => x_total_records,
742 p_total_positives => x_total_positives,
743 p_target_field => x_target_field,
744 p_target_type => x_target_type,
745 p_target_positive_value => x_target_positive_value,
746 p_min_records => x_min_records,
747 p_max_records => x_max_records,
748 p_row_selection_type => x_row_selection_type,
749 p_every_nth_row => x_every_nth_row,
750 p_pct_random => x_pct_random,
751 p_performance => x_performance,
752 p_target_group_type => x_target_group_type,
753 p_best_subtree => x_best_subtree,
754 p_wf_itemkey => x_wf_itemkey,
755 p_target_id => x_target_id,
756 p_darwin_model_ref => x_darwin_model_ref,
757 p_attribute_category => x_attribute_category,
758 p_attribute1 => x_attribute1,
759 p_attribute2 => x_attribute2,
760 p_attribute3 => x_attribute3,
761 p_attribute4 => x_attribute4,
762 p_attribute5 => x_attribute5,
763 p_attribute6 => x_attribute6,
764 p_attribute7 => x_attribute7,
765 p_attribute8 => x_attribute8,
766 p_attribute9 => x_attribute9,
767 p_attribute10 => x_attribute10,
768 p_attribute11 => x_attribute11,
769 p_attribute12 => x_attribute12,
770 p_attribute13 => x_attribute13,
771 p_attribute14 => x_attribute14,
772 p_attribute15 => x_attribute15,
773 p_model_name => x_model_name,
774 p_description => x_description
775 );
776 end if;
777 close c_chk_model_exists;
778 END load_row;
779
780
781 End ams_dm_models_b_pkg;