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