[Home] [Help]
PACKAGE BODY: APPS.AMS_DM_TARGET_VALUES_B_PKG
Source
1 PACKAGE BODY AMS_DM_TARGET_VALUES_B_PKG as
2 /* $Header: amstdtvb.pls 115.5 2003/03/07 03:54:23 choang noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_DM_TARGET_VALUES_B_PKG
7 -- Purpose
8 --
9 -- History
10 -- 08-Oct-2002 nyostos Added value_condition column
11 -- 16-Oct-2002 choang Added target_operator and range_value, replacing value_condition
12 -- 06-Mar-2003 choang Added x_custom_mode to load_row for bug 2819067.
13 --
14 -- NOTE
15 --
16 -- End of Comments
17 -- ===============================================================
18
19
20 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMS_DM_TARGET_VALUES_B_PKG';
21 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstdtvb.pls';
22
23
24 ----------------------------------------------------------
25 ---- Data Mining Target Values ----
26 ----------------------------------------------------------
27
28 -- ========================================================
29 --
30 -- NAME
31 -- createInsertBody
32 --
33 -- PURPOSE
34 --
35 -- NOTES
36 --
37 -- HISTORY
38 --
39 -- ========================================================
40 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
41 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
42 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
43
44 PROCEDURE Insert_Row(
45 px_target_value_id IN OUT NOCOPY NUMBER,
46 p_last_update_date DATE,
47 p_last_updated_by NUMBER,
48 p_creation_date DATE,
49 p_created_by NUMBER,
50 p_last_update_login NUMBER,
51 px_object_version_number IN OUT NOCOPY NUMBER,
52 p_target_id NUMBER,
53 p_target_value VARCHAR2,
54 p_target_operator IN VARCHAR2,
55 p_range_value IN VARCHAR2,
56 p_description VARCHAR2)
57 IS
58 x_rowid VARCHAR2(30);
59 BEGIN
60 px_object_version_number := 1;
61
62
63 INSERT INTO AMS_DM_TARGET_VALUES_B(
64 target_value_id,
65 last_update_date,
66 last_updated_by,
67 creation_date,
68 created_by,
69 last_update_login,
70 object_version_number,
71 target_id,
72 target_value,
73 target_operator,
74 range_value
75 ) VALUES (
76 DECODE( px_target_value_id, FND_API.g_miss_num, NULL, px_target_value_id),
77 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
78 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
79 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
80 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
81 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
82 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
83 DECODE( p_target_id, FND_API.g_miss_num, NULL, p_target_id),
84 DECODE( p_target_value, FND_API.g_miss_char, NULL, p_target_value),
85 DECODE( p_target_operator, FND_API.g_miss_char, NULL, p_target_operator),
86 DECODE( p_range_value, FND_API.g_miss_char, NULL, p_range_value)
87 );
88
89 -- Insert target value description into TL table
90 INSERT INTO ams_dm_target_values_tl(
91 target_value_id,
92 language,
93 last_update_date,
94 last_updated_by,
95 creation_date,
96 created_by,
97 last_update_login,
98 source_lang,
99 description
100 )
101 SELECT
102 decode( px_target_value_id, FND_API.G_MISS_NUM, NULL, px_target_value_id),
103 l.language_code,
104 SYSDATE,
105 FND_GLOBAL.user_id,
106 SYSDATE,
107 FND_GLOBAL.user_id,
108 FND_GLOBAL.conc_login_id,
109 USERENV('LANG'),
110 decode( p_description, FND_API.G_MISS_CHAR, NULL, p_description)
111 FROM fnd_languages l
112 WHERE l.installed_flag in ('I', 'B')
113 AND NOT EXISTS(
114 SELECT NULL
115 FROM ams_dm_target_values_tl t
116 WHERE t.target_value_id = decode( px_target_value_id, FND_API.G_MISS_NUM, NULL, px_target_value_id)
117 AND t.language = l.language_code );
118 END Insert_Row;
119
120
121 -- ========================================================
122 --
123 -- NAME
124 -- createUpdateBody
125 --
126 -- PURPOSE
127 --
128 -- NOTES
129 --
130 -- HISTORY
131 --
132 -- ========================================================
133 PROCEDURE Update_Row(
134 p_target_value_id NUMBER,
135 p_last_update_date DATE,
136 p_last_updated_by NUMBER,
137 p_last_update_login NUMBER,
138 p_object_version_number NUMBER,
139 p_target_id NUMBER,
140 p_target_value VARCHAR2,
141 p_target_operator IN VARCHAR2,
142 p_range_value IN VARCHAR2,
143 p_description VARCHAR2)
144 IS
145 BEGIN
146 Update AMS_DM_TARGET_VALUES_B
147 SET
148 target_value_id = DECODE( p_target_value_id, FND_API.g_miss_num, target_value_id, p_target_value_id),
149 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
150 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
151 last_update_login = DECODE( p_last_update_login,FND_API.g_miss_num, last_update_login, p_last_update_login),
152 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number),
153 target_id = DECODE( p_target_id, FND_API.g_miss_num, target_id, p_target_id),
154 target_value = DECODE( p_target_value, FND_API.g_miss_char, target_value, p_target_value),
155 target_operator = DECODE( p_target_operator, FND_API.g_miss_char, target_operator, p_target_operator),
156 range_value = DECODE( p_range_value, FND_API.g_miss_char, range_value, p_range_value)
157 WHERE TARGET_VALUE_ID = p_TARGET_VALUE_ID
158 ;
159
160 IF (SQL%NOTFOUND) THEN
161 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
162 END IF;
163
164 -- update target value description in TL table
165 update ams_dm_target_values_tl set
166 description = decode( p_description, FND_API.G_MISS_CHAR, DESCRIPTION, p_description),
167 last_update_date = SYSDATE,
168 last_updated_by = FND_GLOBAL.user_id,
169 last_update_login = FND_GLOBAL.conc_login_id,
170 source_lang = USERENV('LANG')
171 WHERE target_value_id = p_target_value_id
172 AND USERENV('LANG') IN (language, source_lang);
173
174
175 END Update_Row;
176
177
178 -- ========================================================
179 --
180 -- NAME
181 -- createDeleteBody
182 --
183 -- PURPOSE
184 --
185 -- NOTES
186 --
187 -- HISTORY
188 --
189 -- ========================================================
190 PROCEDURE Delete_Row(
191 p_TARGET_VALUE_ID NUMBER)
192 IS
193 BEGIN
194 DELETE FROM AMS_DM_TARGET_VALUES_B
195 WHERE TARGET_VALUE_ID = p_TARGET_VALUE_ID;
196 If (SQL%NOTFOUND) then
197 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
198 End If;
199
200
201 DELETE FROM ams_dm_target_values_tl
202 WHERE TARGET_VALUE_ID = p_TARGET_VALUE_ID;
203 If (SQL%NOTFOUND) then
204 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
205 End If;
206
207 END Delete_Row ;
208
209
210
211 -- ========================================================
212 --
213 -- NAME
214 -- createLockBody
215 --
216 -- PURPOSE
217 --
218 -- NOTES
219 --
220 -- HISTORY
221 --
222 -- ========================================================
223 PROCEDURE Lock_Row(
224 p_target_value_id NUMBER,
225 p_last_update_date DATE,
226 p_last_updated_by NUMBER,
227 p_creation_date DATE,
228 p_created_by NUMBER,
229 p_last_update_login NUMBER,
230 p_object_version_number NUMBER,
231 p_target_id NUMBER,
232 p_target_value VARCHAR2)
233 IS
234 CURSOR C IS
235 SELECT *
236 FROM AMS_DM_TARGET_VALUES_B
237 WHERE TARGET_VALUE_ID = p_TARGET_VALUE_ID
238 FOR UPDATE of TARGET_VALUE_ID NOWAIT;
239 Recinfo C%ROWTYPE;
240 BEGIN
241 OPEN c;
242 FETCH c INTO Recinfo;
243 If (c%NOTFOUND) then
244 CLOSE c;
245 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
246 APP_EXCEPTION.RAISE_EXCEPTION;
247 END IF;
248 CLOSE C;
249 IF (
250 ( Recinfo.target_value_id = p_target_value_id)
251 AND ( ( Recinfo.object_version_number = p_object_version_number)
252 OR ( ( Recinfo.object_version_number IS NULL )
253 AND ( p_object_version_number IS NULL )))
254 ) THEN
255 RETURN;
256 ELSE
257 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
258 APP_EXCEPTION.RAISE_EXCEPTION;
259 END IF;
260 END Lock_Row;
261
262
263 PROCEDURE add_language
264 IS
265 BEGIN
266
267 delete from AMS_DM_TARGET_VALUES_TL T
268 where not exists
269 (select NULL
270 from AMS_DM_TARGET_VALUES_B B
271 where B.TARGET_VALUE_ID = T.TARGET_VALUE_ID
272 );
273
274 update AMS_DM_TARGET_VALUES_TL T set (
275 DESCRIPTION
276 ) = (select
277 B.DESCRIPTION
278 from AMS_DM_TARGET_VALUES_TL B
279 where B.TARGET_VALUE_ID = T.TARGET_VALUE_ID
280 and B.LANGUAGE = T.SOURCE_LANG)
281 where (
282 T.TARGET_VALUE_ID,
283 T.LANGUAGE
284 ) in (select
285 SUBT.TARGET_VALUE_ID,
286 SUBT.LANGUAGE
287 from AMS_DM_TARGET_VALUES_TL SUBB, AMS_DM_TARGET_VALUES_TL SUBT
288 where SUBB.TARGET_VALUE_ID = SUBT.TARGET_VALUE_ID
289 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
290 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
291 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
292 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
293 ));
294
295 insert into AMS_DM_TARGET_VALUES_TL (
296 TARGET_VALUE_ID,
297 LAST_UPDATE_DATE,
298 LAST_UPDATED_BY,
299 CREATION_DATE,
300 CREATED_BY,
301 LAST_UPDATE_LOGIN,
302 DESCRIPTION,
303 LANGUAGE,
304 SOURCE_LANG
305 ) select /*+ ORDERED */
306 B.TARGET_VALUE_ID,
307 B.LAST_UPDATE_DATE,
308 B.LAST_UPDATED_BY,
309 B.CREATION_DATE,
310 B.CREATED_BY,
311 B.LAST_UPDATE_LOGIN,
312 B.DESCRIPTION,
313 L.LANGUAGE_CODE,
314 B.SOURCE_LANG
315 from AMS_DM_TARGET_VALUES_TL B, FND_LANGUAGES L
316 where L.INSTALLED_FLAG in ('I', 'B')
317 and B.LANGUAGE = userenv('LANG')
318 and not exists
319 (select NULL
320 from AMS_DM_TARGET_VALUES_TL T
321 where T.TARGET_VALUE_ID = B.TARGET_VALUE_ID
322 and T.LANGUAGE = L.LANGUAGE_CODE);
323 END add_language;
324
325
326 PROCEDURE translate_row (
327 x_target_value_id IN NUMBER,
328 x_description IN VARCHAR2,
329 x_owner IN VARCHAR2
330 )
331 IS
332 BEGIN
333 update ams_dm_target_values_tl set
334 description = nvl(x_description, description),
335 source_lang = userenv('LANG'),
336 last_update_date = sysdate,
337 last_updated_by = decode(x_owner, 'SEED', 1, 0),
338 last_update_login = 0
339 where target_value_id = x_target_value_id
340 and userenv('LANG') in (language, source_lang);
341 end TRANSLATE_ROW;
342
343
344 PROCEDURE load_row (
345 x_target_value_id IN NUMBER,
346 x_target_id IN NUMBER,
347 x_target_value VARCHAR2,
348 x_target_operator IN VARCHAR2,
349 x_range_value IN VARCHAR2,
350 x_description VARCHAR2,
351 x_owner IN VARCHAR2,
352 x_custom_mode IN VARCHAR2
353 )
354 IS
355 l_user_id number := 0;
356 l_obj_verno number;
357 l_db_luby_id number;
358 l_row_id varchar2(100);
359 l_target_value_id number;
360
361 cursor c_chk_target_value_exists is
362 select last_updated_by, nvl(object_version_number, 1)
363 from ams_dm_target_values_b
364 where target_value_id = x_target_value_id;
365
366 cursor c_get_target_value_id is
367 select ams_dm_target_values_b_s.nextval
368 from dual;
369
370 BEGIN
371
372 if x_owner = 'SEED' then
373 l_user_id := 1;
374 end if;
375
376 open c_chk_target_value_exists;
377 fetch c_chk_target_value_exists into l_db_luby_id, l_obj_verno;
378 if c_chk_target_value_exists%notfound THEN
379 if x_target_value_id is null then
380 open c_get_target_value_id;
381 fetch c_get_target_value_id into l_target_value_id;
382 close c_get_target_value_id;
383 else
384 l_target_value_id := x_target_value_id;
385 end if;
386
387 l_obj_verno := 1;
388
389 AMS_DM_TARGET_VALUES_B_PKG.INSERT_ROW (
390 px_target_value_id => l_target_value_id,
391 p_last_update_date => SYSDATE,
392 p_last_updated_by => l_user_id,
393 p_creation_date => SYSDATE,
394 p_created_by => l_user_id,
395 p_last_update_login => 0,
396 px_object_version_number => l_obj_verno,
397 p_target_id => x_target_id,
398 p_target_value => x_target_value,
399 p_target_operator => x_target_operator,
400 p_range_value => x_range_value,
401 p_description => x_description
402 );
403 else
404 if ( l_db_luby_id IN (1, 2, 0) OR NVL(x_custom_mode,'PRESERVE') = 'FORCE') THEN
405 AMS_DM_TARGET_VALUES_B_PKG.UPDATE_ROW (
406 p_target_value_id => x_target_value_id,
407 p_last_update_date => SYSDATE,
408 p_last_updated_by => l_user_id,
409 p_last_update_login => 0,
410 p_object_version_number => l_obj_verno,
411 p_target_id => x_target_id,
412 p_target_value => x_target_value,
413 p_target_operator => x_target_operator,
414 p_range_value => x_range_value,
415 p_description => x_description
416 );
417 end if; -- last updated by and force update
418 end if;
419 close c_chk_target_value_exists;
420 END load_row;
421
422 END AMS_DM_TARGET_VALUES_B_PKG;