[Home] [Help]
PACKAGE BODY: APPS.AMS_DM_TARGETS_B_PKG
Source
1 PACKAGE BODY AMS_DM_TARGETS_B_PKG as
2 /* $Header: amstdtgb.pls 115.4 2003/09/15 12:44:47 rosharma noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_DM_TARGETS_B_PKG
7 -- Purpose
8 --
9 -- History
10 -- 10-Apr-2002 nyostos Created.
11 -- 06-Mar-2003 choang Added x_custom_mode to load_row for bug 2819067.
12 --
13 -- NOTE
14 --
15 -- End of Comments
16 -- ===============================================================
17
18
19 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_DM_TARGETS_B_PKG';
20 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstdtgb.pls';
21
22
23 ----------------------------------------------------------
24 ---- Data Mining Targets ----
25 ----------------------------------------------------------
26
27 -- ========================================================
28 --
29 -- NAME
30 -- Insert_Row
31 --
32 -- PURPOSE
33 --
34 -- NOTES
35 --
36 -- HISTORY
37 --
38 -- ========================================================
39 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
40 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
41 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
42
43 PROCEDURE Insert_Row(
44 px_target_id IN OUT NOCOPY NUMBER,
45 p_last_update_date DATE,
46 p_last_updated_by NUMBER,
47 p_creation_date DATE,
48 p_created_by NUMBER,
49 p_last_update_login NUMBER,
50 px_object_version_number IN OUT NOCOPY NUMBER,
51 p_active_flag VARCHAR2,
52 p_model_type VARCHAR2,
53 p_data_source_id NUMBER,
54 p_source_field_id NUMBER,
55 p_target_name VARCHAR2,
56 p_description VARCHAR2,
57 p_target_source_id NUMBER )
58
59 IS
60 x_rowid VARCHAR2(30);
61
62
63 BEGIN
64
65
66 px_object_version_number := 1;
67
68
69 INSERT INTO AMS_DM_TARGETS_B(
70 target_id,
71 last_update_date,
72 last_updated_by,
73 creation_date,
74 created_by,
75 last_update_login,
76 object_version_number,
77 active_flag,
78 model_type,
79 data_source_id,
80 source_field_id,
81 target_source_id
82 ) VALUES (
83 DECODE( px_target_id, FND_API.g_miss_num, NULL, px_target_id),
84 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
85 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
86 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
87 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
88 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
89 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
90 DECODE( p_active_flag, FND_API.g_miss_char, NULL, p_active_flag),
91 DECODE( p_model_type, FND_API.g_miss_char, NULL, p_model_type),
92 DECODE( p_data_source_id, FND_API.g_miss_num, NULL, p_data_source_id),
93 DECODE( p_source_field_id, FND_API.g_miss_num, NULL, p_source_field_id),
94 DECODE( p_target_source_id, FND_API.g_miss_num, NULL, p_target_source_id));
95
96 -- Insert target_name and description into TL table
97 INSERT INTO ams_dm_targets_tl(
98 target_id,
99 language,
100 last_update_date,
101 last_updated_by,
102 creation_date,
103 created_by,
104 last_update_login,
105 source_lang,
106 target_name,
107 description
108 )
109 SELECT
110 decode( px_target_id, FND_API.G_MISS_NUM, NULL, px_target_id),
111 l.language_code,
112 SYSDATE,
113 FND_GLOBAL.user_id,
114 SYSDATE,
115 FND_GLOBAL.user_id,
116 FND_GLOBAL.conc_login_id,
117 USERENV('LANG'),
118 decode( p_target_name, FND_API.G_MISS_CHAR, NULL, p_target_name),
119 decode( p_description, FND_API.G_MISS_CHAR, NULL, p_description)
120 FROM fnd_languages l
121 WHERE l.installed_flag in ('I', 'B')
122 AND NOT EXISTS(
123 SELECT NULL
124 FROM ams_dm_targets_tl t
125 WHERE t.target_id = decode( px_target_id, FND_API.G_MISS_NUM, NULL, px_target_id)
126 AND t.language = l.language_code );
127
128
129 END Insert_Row;
130
131
132 -- ========================================================
133 --
134 -- NAME
135 -- createUpdateBody
136 --
137 -- PURPOSE
138 --
139 -- NOTES
140 --
141 -- HISTORY
142 --
143 -- ========================================================
144 PROCEDURE Update_Row(
145 p_target_id NUMBER,
146 p_last_update_date DATE,
147 p_last_updated_by NUMBER,
148 p_creation_date DATE,
149 p_created_by NUMBER,
150 p_last_update_login NUMBER,
151 p_object_version_number NUMBER,
152 p_active_flag VARCHAR2,
153 p_model_type VARCHAR2,
154 p_data_source_id NUMBER,
155 p_source_field_id NUMBER,
156 p_target_name VARCHAR2,
157 p_description VARCHAR2,
158 p_target_source_id NUMBER )
159
160 IS
161 BEGIN
162 IF (AMS_DEBUG_HIGH_ON) THEN
163
164 AMS_UTILITY_PVT.debug_message('Table Handler Update going to Update AMS_DM_TARGETS_B' );
165 END IF;
166 IF (AMS_DEBUG_HIGH_ON) THEN
167
168 AMS_UTILITY_PVT.debug_message(p_target_id || ' ' || p_last_update_date || ' ' || p_last_updated_by);
169 END IF;
170 IF (AMS_DEBUG_HIGH_ON) THEN
171
172 AMS_UTILITY_PVT.debug_message(p_creation_date || ' ' || p_created_by || ' ' || p_last_update_login);
173 END IF;
174 IF (AMS_DEBUG_HIGH_ON) THEN
175
176 AMS_UTILITY_PVT.debug_message(p_object_version_number || ' ' || p_active_flag || ' ' || p_model_type);
177 END IF;
178 IF (AMS_DEBUG_HIGH_ON) THEN
179
180 AMS_UTILITY_PVT.debug_message(p_data_source_id || ' ' || p_source_field_id);
181 END IF;
182 Update AMS_DM_TARGETS_B
183 SET
184 target_id = DECODE( p_target_id, FND_API.g_miss_num, target_id, p_target_id),
185 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
186 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
187 creation_date = DECODE( p_creation_date, FND_API.g_miss_date, creation_date, p_creation_date),
188 created_by = DECODE( p_created_by, FND_API.g_miss_num, created_by, p_created_by),
189 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
190 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number),
191 active_flag = DECODE( p_active_flag, FND_API.g_miss_char, active_flag, p_active_flag),
192 model_type = DECODE( p_model_type, FND_API.g_miss_char, model_type, p_model_type),
193 data_source_id = DECODE( p_data_source_id, FND_API.g_miss_num, data_source_id, p_data_source_id),
194 source_field_id = DECODE( p_source_field_id, FND_API.g_miss_num, source_field_id, p_source_field_id),
195 target_source_id = DECODE( p_target_source_id, FND_API.g_miss_num, target_source_id, p_target_source_id)
196 WHERE TARGET_ID = p_TARGET_ID
197 AND object_version_number = p_object_version_number;
198
199 IF (SQL%NOTFOUND) THEN
200 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
201 END IF;
202
203 IF (AMS_DEBUG_HIGH_ON) THEN
204
205
206
207 AMS_UTILITY_PVT.debug_message('Table Handler Update going to Update ams_dm_targets_tl' );
208
209 END IF;
210
211 -- update target name and description in TL table
212 update ams_dm_targets_tl set
213 target_name = decode( p_target_name, FND_API.G_MISS_CHAR, TARGET_NAME, p_target_name),
214 description = decode( p_description, FND_API.G_MISS_CHAR, DESCRIPTION, p_description),
215 last_update_date = SYSDATE,
216 last_updated_by = p_last_updated_by,
217 last_update_login = p_last_update_login,
218 source_lang = USERENV('LANG')
219 WHERE target_id = p_target_id
220 AND USERENV('LANG') IN (language, source_lang);
221
222
223 END Update_Row;
224
225
226 -- ========================================================
227 --
228 -- NAME
229 -- createDeleteBody
230 --
231 -- PURPOSE
232 --
233 -- NOTES
234 --
235 -- HISTORY
236 --
237 -- ========================================================
238 PROCEDURE Delete_Row(
239 p_TARGET_ID NUMBER)
240 IS
241 BEGIN
242
243 DELETE FROM AMS_DM_TARGETS_B
244 WHERE TARGET_ID = p_TARGET_ID;
245 If (SQL%NOTFOUND) then
246 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
247 End If;
248
249 DELETE FROM ams_dm_targets_tl
250 WHERE TARGET_ID = p_TARGET_ID;
251 If (SQL%NOTFOUND) then
252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
253 End If;
254
255 END Delete_Row ;
256
257
258 -- ========================================================
259 --
260 -- NAME
261 -- createLockBody
262 --
263 -- PURPOSE
264 --
265 -- NOTES
266 --
267 -- HISTORY
268 --
269 -- ========================================================
270 PROCEDURE Lock_Row(
271 p_target_id NUMBER,
272 p_last_update_date DATE,
273 p_last_updated_by NUMBER,
274 p_creation_date DATE,
275 p_created_by NUMBER,
276 p_last_update_login NUMBER,
277 p_object_version_number NUMBER,
278 p_active_flag VARCHAR2,
279 p_model_type VARCHAR2,
280 p_data_source_id NUMBER,
281 p_source_field_id NUMBER,
282 p_target_source_id NUMBER )
283
284 IS
285 CURSOR C IS
286 SELECT *
287 FROM AMS_DM_TARGETS_B
288 WHERE TARGET_ID = p_TARGET_ID
289 FOR UPDATE of TARGET_ID NOWAIT;
290 Recinfo C%ROWTYPE;
291 BEGIN
292 OPEN c;
293 FETCH c INTO Recinfo;
294 If (c%NOTFOUND) then
295 CLOSE c;
296 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
297 APP_EXCEPTION.RAISE_EXCEPTION;
298 END IF;
299 CLOSE C;
300 IF (
301 ( Recinfo.target_id = p_target_id)
302 AND ( ( Recinfo.last_update_date = p_last_update_date)
303 OR ( ( Recinfo.last_update_date IS NULL )
304 AND ( p_last_update_date IS NULL )))
305 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
306 OR ( ( Recinfo.last_updated_by IS NULL )
307 AND ( p_last_updated_by IS NULL )))
308 AND ( ( Recinfo.creation_date = p_creation_date)
309 OR ( ( Recinfo.creation_date IS NULL )
310 AND ( p_creation_date IS NULL )))
311 AND ( ( Recinfo.created_by = p_created_by)
312 OR ( ( Recinfo.created_by IS NULL )
313 AND ( p_created_by IS NULL )))
314 AND ( ( Recinfo.last_update_login = p_last_update_login)
315 OR ( ( Recinfo.last_update_login IS NULL )
316 AND ( p_last_update_login IS NULL )))
317 AND ( ( Recinfo.object_version_number = p_object_version_number)
318 OR ( ( Recinfo.object_version_number IS NULL )
319 AND ( p_object_version_number IS NULL )))
320 AND ( ( Recinfo.active_flag = p_active_flag)
321 OR ( ( Recinfo.active_flag IS NULL )
322 AND ( p_active_flag IS NULL )))
323 AND ( ( Recinfo.model_type = p_model_type)
324 OR ( ( Recinfo.model_type IS NULL )
325 AND ( p_model_type IS NULL )))
326 AND ( ( Recinfo.data_source_id = p_data_source_id)
327 OR ( ( Recinfo.data_source_id IS NULL )
328 AND ( p_data_source_id IS NULL )))
329 AND ( ( Recinfo.source_field_id = p_source_field_id)
330 OR ( ( Recinfo.source_field_id IS NULL )
331 AND ( p_source_field_id IS NULL )))
332 AND ( ( Recinfo.target_source_id = p_target_source_id)
333 OR ( ( Recinfo.target_source_id IS NULL )
334 AND ( p_target_source_id IS NULL )))
335 ) THEN
336 RETURN;
337 ELSE
338 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
339 APP_EXCEPTION.RAISE_EXCEPTION;
340 END IF;
341 END Lock_Row;
342
343
344 PROCEDURE add_language
345 IS
346 BEGIN
347 delete from AMS_DM_TARGETS_TL T
348 where not exists
349 (select NULL
350 from AMS_DM_TARGETS_B B
351 where B.TARGET_ID = T.TARGET_ID
352 );
353
354 update AMS_DM_TARGETS_TL T set (
355 TARGET_NAME,
356 DESCRIPTION
357 ) = (select
358 B.TARGET_NAME,
359 B.DESCRIPTION
360 from AMS_DM_TARGETS_TL B
361 where B.TARGET_ID = T.TARGET_ID
362 and B.LANGUAGE = T.SOURCE_LANG)
363 where (
364 T.TARGET_ID,
365 T.LANGUAGE
366 ) in (select
367 SUBT.TARGET_ID,
368 SUBT.LANGUAGE
369 from AMS_DM_TARGETS_TL SUBB, AMS_DM_TARGETS_TL SUBT
370 where SUBB.TARGET_ID = SUBT.TARGET_ID
371 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
372 and (SUBB.TARGET_NAME <> SUBT.TARGET_NAME
373 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
374 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
375 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
376 ));
377
378 insert into AMS_DM_TARGETS_TL (
379 TARGET_ID,
380 LAST_UPDATE_DATE,
381 LAST_UPDATED_BY,
382 CREATION_DATE,
383 CREATED_BY,
384 LAST_UPDATE_LOGIN,
385 TARGET_NAME,
386 DESCRIPTION,
390 B.TARGET_ID,
387 LANGUAGE,
388 SOURCE_LANG
389 ) select /*+ ORDERED */
391 B.LAST_UPDATE_DATE,
392 B.LAST_UPDATED_BY,
393 B.CREATION_DATE,
394 B.CREATED_BY,
395 B.LAST_UPDATE_LOGIN,
396 B.TARGET_NAME,
397 B.DESCRIPTION,
398 L.LANGUAGE_CODE,
399 B.SOURCE_LANG
400 from AMS_DM_TARGETS_TL B, FND_LANGUAGES L
401 where L.INSTALLED_FLAG in ('I', 'B')
402 and B.LANGUAGE = userenv('LANG')
403 and not exists
404 (select NULL
405 from AMS_DM_TARGETS_TL T
406 where T.TARGET_ID = B.TARGET_ID
407 and T.LANGUAGE = L.LANGUAGE_CODE);
408 END add_language;
409
410 PROCEDURE translate_row (
411 x_target_id IN NUMBER,
412 x_target_name IN VARCHAR2,
413 x_description IN VARCHAR2,
414 x_owner IN VARCHAR2
415 )
416 IS
417 BEGIN
418 update ams_dm_targets_tl set
419 target_name = nvl(x_target_name, target_name),
420 description = nvl(x_description, description),
421 source_lang = userenv('LANG'),
422 last_update_date = sysdate,
423 last_updated_by = decode(x_owner, 'SEED', 1, 0),
424 last_update_login = 0
425 where target_id = x_target_id
426 and userenv('LANG') in (language, source_lang);
427 end TRANSLATE_ROW;
428
429
430 PROCEDURE load_row (
431 x_target_id IN NUMBER,
432 x_active_flag VARCHAR2,
433 x_model_type VARCHAR2,
434 x_data_source_id NUMBER,
435 x_source_field_id NUMBER,
436 x_target_name VARCHAR2,
437 x_description VARCHAR2,
438 x_target_source_id NUMBER,
439 x_owner IN VARCHAR2,
440 x_custom_mode IN VARCHAR2
441 )
442 IS
443 l_user_id number := 0;
444 l_obj_verno number;
445 l_db_luby_id number;
446 l_row_id varchar2(100);
447 l_target_id number;
448
449 cursor c_chk_target_exists is
450 select last_updated_by, nvl(object_version_number, 1)
451 from ams_dm_targets_b
452 where target_id = x_target_id;
453
454 cursor c_get_target_id is
455 select ams_dm_targets_b_s.nextval
456 from dual;
457 BEGIN
458
459 if x_owner = 'SEED' then
460 l_user_id := 1;
461 end if;
462
463 open c_chk_target_exists;
464 fetch c_chk_target_exists into l_db_luby_id, l_obj_verno;
465 if c_chk_target_exists%notfound THEN
466 if x_target_id is null then
467 open c_get_target_id;
468 fetch c_get_target_id into l_target_id;
469 close c_get_target_id;
470 else
471 l_target_id := x_target_id;
472 end if;
473 l_obj_verno := 1;
474
475 AMS_DM_TARGETS_B_PKG.INSERT_ROW (
476 px_target_id => l_target_id,
477 p_last_update_date => SYSDATE,
478 p_last_updated_by => l_user_id,
479 p_creation_date => SYSDATE,
480 p_created_by => l_user_id,
481 p_last_update_login => 0,
482 px_object_version_number => l_obj_verno,
483 p_active_flag => x_active_flag,
484 p_model_type => x_model_type,
485 p_data_source_id => x_data_source_id,
486 p_source_field_id => x_source_field_id,
487 p_target_name => x_target_name,
488 p_description => x_description,
489 p_target_source_id => x_target_source_id
490 );
491 else
492 if ( l_db_luby_id IN (1, 2, 0) OR NVL(x_custom_mode,'PRESERVE') = 'FORCE') THEN
493 AMS_DM_TARGETS_B_PKG.UPDATE_ROW (
494 p_target_id => x_target_id,
495 p_last_update_date => SYSDATE,
496 p_last_updated_by => l_user_id,
497 p_creation_date => SYSDATE,
498 p_created_by => l_user_id,
499 p_last_update_login => 0,
500 p_object_version_number => l_obj_verno,
501 p_active_flag => x_active_flag,
502 p_model_type => x_model_type,
503 p_data_source_id => x_data_source_id,
504 p_source_field_id => x_source_field_id,
505 p_target_name => x_target_name,
506 p_description => x_description,
507 p_target_source_id => x_target_source_id
508 );
509 end if; -- last updated by and force update
510 end if;
511 close c_chk_target_exists;
512 END load_row;
513
514
515
516 END AMS_DM_TARGETS_B_PKG;