[Home] [Help]
PACKAGE BODY: APPS.AMS_MET_TPL_HEADERS_B_PKG
Source
1 PACKAGE BODY AMS_MET_TPL_HEADERS_B_PKG AS
2 /* $Header: amslmthb.pls 115.14 2003/10/16 11:26:04 sunkumar ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_MET_TPL_HEADERS_B_PKG
7 -- Purpose
8 --
9 -- History
10 -- 03/05/2002 dmvincen Created.
11 -- 03/07/2002 dmvincen Added LOAD_ROW.
12 -- 08/19/2002 dmvincen Added add_language for MLS compliance. BUG2501425.
13 -- 03/06/2003 dmvincen BUG2819067: Do not update if customized.
14 -- 08-Sep-2003 Sunkumar Bug#3130095 Metric Template UI Enh. 11510
15 --
16 -- NOTE
17 --
18 -- End of Comments
19 -- ===============================================================
20
21
22 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_MET_TPL_HEADERS_B_PKG';
23 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amslmthb.pls';
24
25
26 ----------------------------------------------------------
27 ---- MEDIA ----
28 ----------------------------------------------------------
29
30 -- ========================================================
31 --
32 -- NAME
33 -- createInsertBody
34 --
35 -- PURPOSE
36 --
37 -- NOTES
38 --
39 -- HISTORY
40 --
41 -- ========================================================
42 PROCEDURE Insert_Row(
43 px_metric_tpl_header_id NUMBER,
44 p_last_update_date DATE,
45 p_last_updated_by NUMBER,
46 p_creation_date DATE,
47 p_created_by NUMBER,
48 p_last_update_login NUMBER,
49 px_object_version_number NUMBER,
50 p_enabled_flag VARCHAR2,
51 p_application_id NUMBER,
52 p_metric_tpl_header_name VARCHAR2,
53 p_description VARCHAR2,
54 p_object_type VARCHAR2,
55 p_association_type VARCHAR2,
56 p_used_by_id NUMBER,
57 p_used_by_code VARCHAR2)
58
59 IS
60 x_rowid VARCHAR2(30);
61
62
63 BEGIN
64
65 -- px_object_version_number := 1;
66
67 INSERT INTO AMS_MET_TPL_HEADERS_B(
68 metric_tpl_header_id,
69 last_update_date,
70 last_updated_by,
71 creation_date,
72 created_by,
73 last_update_login,
74 object_version_number,
75 enabled_flag,
76 application_id,
77 object_type,
78 association_type,
79 used_by_id,
80 used_by_code
81 ) VALUES (
82 DECODE( px_metric_tpl_header_id, FND_API.g_miss_num, NULL, px_metric_tpl_header_id),
83 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
84 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
85 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
86 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
87 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
88 1, --DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
89 DECODE( p_enabled_flag, FND_API.g_miss_char, NULL, p_enabled_flag),
90 DECODE( p_application_id, FND_API.g_miss_num, NULL, p_application_id),
91 DECODE( p_object_type, FND_API.g_miss_char, NULL, p_object_type),
92 DECODE( p_association_type, FND_API.g_miss_char, NULL, p_association_type),
93 DECODE( p_used_by_id, FND_API.g_miss_num, NULL, p_used_by_id),
94 DECODE( p_used_by_code, FND_API.g_miss_char, NULL, p_used_by_code));
95
96
97 INSERT INTO AMS_MET_TPL_HEADERS_TL (
98 METRIC_TPL_HEADER_ID ,
99 LAST_UPDATE_DATE ,
100 LAST_UPDATED_BY ,
101 CREATION_DATE ,
102 CREATED_BY ,
103 LAST_UPDATE_LOGIN ,
104 LANGUAGE ,
105 SOURCE_LANG ,
106 METRIC_TPL_HEADER_NAME ,
107 DESCRIPTION
108 ) SELECT
109 DECODE( px_metric_tpl_header_id, FND_API.g_miss_num, NULL, px_metric_tpl_header_id),
110 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
111 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
112 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
113 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
114 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
115 L.LANGUAGE_CODE,
116 USERENV('LANG'),
117 DECODE( p_metric_tpl_header_name, FND_API.g_miss_char, NULL, p_metric_tpl_header_name),
118 DECODE( p_description, FND_API.g_miss_char, NULL, p_description)
119 FROM FND_LANGUAGES L
120 WHERE L.INSTALLED_FLAG IN ('I', 'B')
121 AND NOT EXISTS
122 (SELECT NULL
123 FROM AMS_MET_TPL_HEADERS_TL T
124 WHERE T.METRIC_TPL_HEADER_ID = px_metric_tpl_header_id
125 AND T.LANGUAGE = L.LANGUAGE_CODE);
126 END Insert_Row;
127
128
129 ----------------------------------------------------------
130 ---- MEDIA ----
131 ----------------------------------------------------------
132
133 -- ========================================================
134 --
135 -- NAME
136 -- createUpdateBody
137 --
138 -- PURPOSE
139 --
140 -- NOTES
141 --
142 -- HISTORY
143 --
144 -- ========================================================
145 PROCEDURE Update_Row(
146 p_metric_tpl_header_id NUMBER,
147 p_last_update_date DATE,
148 p_last_updated_by NUMBER,
149 p_last_update_login NUMBER,
150 p_object_version_number NUMBER,
151 p_enabled_flag VARCHAR2,
152 p_application_id NUMBER,
153 p_metric_tpl_header_name VARCHAR2,
154 p_description VARCHAR2,
155 p_object_type VARCHAR2,
156 p_association_type VARCHAR2,
157 p_used_by_id NUMBER,
158 p_used_by_code VARCHAR2)
159
160 IS
161 BEGIN
162 IF p_metric_tpl_header_id IS NULL OR
163 p_metric_tpl_header_id = FND_API.g_miss_num OR
164 p_object_version_number IS NULL OR
165 p_object_version_number = FND_API.g_miss_num THEN
166 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
167 END IF;
168
169 UPDATE AMS_MET_TPL_HEADERS_B
170 SET
171 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
172 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
173 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
174 enabled_flag = DECODE( p_enabled_flag, FND_API.g_miss_char, enabled_flag, p_enabled_flag),
175 application_id = DECODE( p_application_id, FND_API.g_miss_num, application_id, p_application_id),
176 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number),
177 object_type = DECODE( p_object_type, FND_API.g_miss_char, object_type, p_object_type),
178 association_type = DECODE( p_association_type, FND_API.g_miss_char, association_type, p_association_type),
179 used_by_id = DECODE( p_used_by_id, FND_API.g_miss_num, used_by_id, p_used_by_id),
180 used_by_code = DECODE( p_used_by_code, FND_API.g_miss_char, used_by_code, p_used_by_code)
181 WHERE METRIC_TPL_HEADER_ID = p_METRIC_TPL_HEADER_ID;
182 -- AND object_version_number = p_object_version_number;
183
184 IF (SQL%NOTFOUND) THEN
185 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
186 END IF;
187
188 UPDATE AMS_MET_TPL_HEADERS_TL SET
189 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
190 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
191 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
192 SOURCE_LANG = USERENV('LANG'),
193 metric_tpl_header_name = DECODE( p_metric_tpl_header_name, FND_API.g_miss_char, metric_tpl_header_name, p_metric_tpl_header_name),
194 description = DECODE( p_description, FND_API.g_miss_char, description, p_description)
195 WHERE METRIC_TPL_HEADER_ID = p_METRIC_TPL_HEADER_ID
196 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
197
198 IF (SQL%NOTFOUND) THEN
199 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
200 END IF;
201
202 END Update_Row;
203
204
205 ----------------------------------------------------------
206 ---- MEDIA ----
207 ----------------------------------------------------------
208
209 -- ========================================================
210 --
211 -- NAME
212 -- createDeleteBody
213 --
214 -- PURPOSE
215 --
216 -- NOTES
217 --
218 -- HISTORY
219 --
220 -- ========================================================
221 PROCEDURE Delete_Row(
222 p_METRIC_TPL_HEADER_ID NUMBER)
223 IS
224 BEGIN
225 DELETE FROM AMS_MET_TPL_HEADERS_B
226 WHERE METRIC_TPL_HEADER_ID = p_METRIC_TPL_HEADER_ID;
227 IF (SQL%NOTFOUND) THEN
228 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
229 END IF;
230 DELETE FROM AMS_MET_TPL_HEADERS_TL
231 WHERE METRIC_TPL_HEADER_ID = p_METRIC_TPL_HEADER_ID;
232 IF (SQL%NOTFOUND) THEN
233 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
234 END IF;
235 END Delete_Row ;
236
237
238
239 ----------------------------------------------------------
240 ---- MEDIA ----
241 ----------------------------------------------------------
242
243 -- ========================================================
244 --
245 -- NAME
246 -- createLockBody
247 --
248 -- PURPOSE
249 --
250 -- NOTES
251 --
252 -- HISTORY
253 --
254 -- ========================================================
255 PROCEDURE Lock_Row(
256 p_metric_tpl_header_id NUMBER,
257 p_last_update_date DATE,
258 p_last_updated_by NUMBER,
259 p_creation_date DATE,
260 p_created_by NUMBER,
261 p_last_update_login NUMBER,
262 p_object_version_number NUMBER,
263 p_enabled_flag VARCHAR2,
264 p_application_id NUMBER)
265
266 IS
267 CURSOR C IS
268 SELECT *
269 FROM AMS_MET_TPL_HEADERS_B
270 WHERE METRIC_TPL_HEADER_ID = p_METRIC_TPL_HEADER_ID
271 FOR UPDATE OF METRIC_TPL_HEADER_ID NOWAIT;
272 Recinfo C%ROWTYPE;
273 BEGIN
274 OPEN c;
275 FETCH c INTO Recinfo;
276 IF (c%NOTFOUND) THEN
277 CLOSE c;
278 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
279 APP_EXCEPTION.RAISE_EXCEPTION;
280 END IF;
281 CLOSE C;
282 IF (
283 ( Recinfo.metric_tpl_header_id = p_metric_tpl_header_id)
284 AND ( ( Recinfo.last_update_date = p_last_update_date)
285 OR ( ( Recinfo.last_update_date IS NULL )
286 AND ( p_last_update_date IS NULL )))
287 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
288 OR ( ( Recinfo.last_updated_by IS NULL )
289 AND ( p_last_updated_by IS NULL )))
290 AND ( ( Recinfo.creation_date = p_creation_date)
291 OR ( ( Recinfo.creation_date IS NULL )
292 AND ( p_creation_date IS NULL )))
293 AND ( ( Recinfo.created_by = p_created_by)
294 OR ( ( Recinfo.created_by IS NULL )
295 AND ( p_created_by IS NULL )))
296 AND ( ( Recinfo.last_update_login = p_last_update_login)
297 OR ( ( Recinfo.last_update_login IS NULL )
298 AND ( p_last_update_login IS NULL )))
299 AND ( ( Recinfo.object_version_number = p_object_version_number)
300 OR ( ( Recinfo.object_version_number IS NULL )
301 AND ( p_object_version_number IS NULL )))
302 AND ( ( Recinfo.enabled_flag = p_enabled_flag)
303 OR ( ( Recinfo.enabled_flag IS NULL )
304 AND ( p_enabled_flag IS NULL )))
305 AND ( ( Recinfo.application_id = p_application_id)
306 OR ( ( Recinfo.application_id IS NULL )
307 AND ( p_application_id IS NULL )))
308 ) THEN
309 RETURN;
310 ELSE
311 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
312 APP_EXCEPTION.RAISE_EXCEPTION;
313 END IF;
314 END Lock_Row;
315
316
317 PROCEDURE LOAD_ROW (
318 X_METRIC_TPL_HEADER_ID IN NUMBER,
319 X_OBJECT_VERSION_NUMBER IN NUMBER,
320 X_METRIC_TPL_HEADER_NAME IN VARCHAR2,
321 X_DESCRIPTION IN VARCHAR2,
322 X_ENABLED_FLAG IN VARCHAR2,
323 X_APPLICATION_ID IN NUMBER,
324 X_Owner IN VARCHAR2,
325 X_CUSTOM_MODE IN VARCHAR2,
326 X_OBJECT_TYPE IN VARCHAR2,
327 X_ASSOCIATION_TYPE IN VARCHAR2,
328 X_USED_BY_ID IN NUMBER,
329 X_USED_BY_CODE IN VARCHAR2
330 )
331 IS
332 l_user_id NUMBER := 0;
333 l_obj_verno NUMBER;
334 l_row_id VARCHAR2(100);
335 l_metric_tpl_header_id NUMBER;
336 l_db_luby_id NUMBER;
337
338 CURSOR c_db_data_details IS
339 SELECT last_updated_by, object_version_number
340 FROM AMS_MET_TPL_HEADERS_B
341 WHERE METRIC_TPL_HEADER_ID = X_METRIC_TPL_HEADER_ID;
342
343 CURSOR c_get_mthid IS
344 SELECT AMS_MET_TPL_HEADERS_ALL_S.NEXTVAL
345 FROM dual;
346
347 BEGIN
348
349 -- set the last_updated_by to be used while updating the data in customer data.
350 if X_OWNER = 'SEED' then
351 l_user_id := 1;
352 elsif X_OWNER = 'ORACLE' THEN
353 l_user_id := 2;
354 elsif X_OWNER = 'SYSADMIN' THEN
355 l_user_id := 0;
356 end if ;
357
361 THEN
358 OPEN c_db_data_details;
359 FETCH c_db_data_details INTO l_db_luby_id, l_obj_verno;
360 IF c_db_data_details%NOTFOUND
362 CLOSE c_db_data_details;
363
364 IF x_metric_tpl_header_id IS NULL THEN
365 OPEN c_get_mthid;
366 FETCH c_get_mthid INTO l_metric_tpl_header_id;
367 CLOSE c_get_mthid;
368 ELSE
369 l_metric_tpl_header_id := x_metric_tpl_header_id;
370 END IF ;
371
372 l_obj_verno := 1;
373
374
375 Insert_Row(
376 px_metric_tpl_header_id => L_METRIC_TPL_HEADER_ID,
377 p_last_update_date => SYSDATE,
378 p_last_updated_by => l_user_id,
379 p_creation_date => SYSDATE,
380 p_created_by => l_user_id,
381 p_last_update_login => 0,
382 px_object_version_number => l_obj_verno,
383 p_enabled_flag => X_ENABLED_FLAG,
384 p_application_id => X_APPLICATION_ID,
385 p_metric_tpl_header_name => X_METRIC_TPL_HEADER_NAME,
386 p_description => X_DESCRIPTION,
387 p_object_type => X_OBJECT_TYPE,
388 p_association_type => X_ASSOCIATION_TYPE,
389 p_used_by_id => X_USED_BY_ID,
390 p_used_by_code => X_USED_BY_CODE);
391
392 ELSE
393 CLOSE c_db_data_details;
394 if ( l_db_luby_id IN (1, 2, 0)
395 OR NVL(x_custom_mode,'PRESERVE') = 'FORCE') THEN
396 Update_Row(
397 p_metric_tpl_header_id => X_METRIC_TPL_HEADER_ID,
398 p_last_update_date => SYSDATE,
399 p_last_updated_by => l_user_id,
400 p_last_update_login => 0,
401 p_object_version_number => l_obj_verno + 1,
402 p_enabled_flag => X_ENABLED_FLAG,
403 p_application_id => X_APPLICATION_ID,
404 p_metric_tpl_header_name => X_METRIC_TPL_HEADER_NAME,
405 p_description => X_DESCRIPTION,
406 p_object_type => X_OBJECT_TYPE,
407 p_association_type => X_ASSOCIATION_TYPE,
408 p_used_by_id => X_USED_BY_ID,
409 p_used_by_code => X_USED_BY_CODE);
410
411 END IF;
412 END IF;
413 END LOAD_ROW;
414
415 -- MLS compatibility.
416 procedure ADD_LANGUAGE
417 is
418 begin
419 delete from AMS_MET_TPL_HEADERS_TL T
420 where not exists
421 (select NULL
422 from AMS_MET_TPL_HEADERS_B B
423 where B.METRIC_TPL_HEADER_ID = T.METRIC_TPL_HEADER_ID
424 );
425
426 update AMS_MET_TPL_HEADERS_TL T set (
427 METRIC_TPL_HEADER_NAME,
428 DESCRIPTION
429 ) = (select
430 B.METRIC_TPL_HEADER_NAME,
431 B.description
432 from AMS_MET_TPL_HEADERS_TL B
433 where B.METRIC_TPL_HEADER_ID = T.METRIC_TPL_HEADER_ID
434 and B.LANGUAGE = T.SOURCE_LANG)
435 where (
436 T.METRIC_TPL_HEADER_ID,
437 T.LANGUAGE
438 ) in (select
439 SUBT.METRIC_TPL_HEADER_ID,
440 SUBT.LANGUAGE
441 from AMS_MET_TPL_HEADERS_TL SUBB, AMS_MET_TPL_HEADERS_TL SUBT
442 where SUBB.METRIC_TPL_HEADER_ID = SUBT.METRIC_TPL_HEADER_ID
443 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
444 and (SUBB.METRIC_TPL_HEADER_NAME <> SUBT.METRIC_TPL_HEADER_NAME
445 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
446 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
447 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
448 ));
449
450 insert into AMS_MET_TPL_HEADERS_TL (
451 LAST_UPDATE_DATE,
452 CREATION_DATE,
453 LAST_UPDATED_BY,
454 CREATED_BY,
455 METRIC_TPL_HEADER_ID,
456 METRIC_TPL_HEADER_NAME,
457 DESCRIPTION,
458 LAST_UPDATE_LOGIN,
459 LANGUAGE,
460 SOURCE_LANG
461 ) select
462 B.LAST_UPDATE_DATE,
463 B.CREATION_DATE,
464 B.LAST_UPDATED_BY,
465 B.CREATED_BY,
466 B.METRIC_TPL_HEADER_ID,
467 B.METRIC_TPL_HEADER_NAME,
468 B.DESCRIPTION,
469 B.LAST_UPDATE_LOGIN,
470 L.LANGUAGE_CODE,
471 B.SOURCE_LANG
472 from AMS_MET_TPL_HEADERS_TL B, FND_LANGUAGES L
473 where L.INSTALLED_FLAG in ('I', 'B')
474 and B.LANGUAGE = userenv('LANG')
475 and not exists
476 (select NULL
477 from AMS_MET_TPL_HEADERS_TL T
478 where T.METRIC_TPL_HEADER_ID = B.METRIC_TPL_HEADER_ID
479 and T.LANGUAGE = L.LANGUAGE_CODE);
480 end ADD_LANGUAGE;
481
482 procedure TRANSLATE_ROW(
483 X_METRIC_TPL_HEADER_ID in NUMBER
484 , X_METRIC_TPL_HEADER_NAME in VARCHAR2
485 , X_DESCRIPTION in VARCHAR2
486 , x_owner in VARCHAR2
487 ) is
488 begin
489 update AMS_MET_TPL_HEADERS_TL set
490 METRIC_TPL_HEADER_NAME = nvl(X_METRIC_TPL_HEADER_NAME, METRIC_TPL_HEADER_NAME),
491 description = nvl(x_description, description),
492 source_lang = userenv('LANG'),
496 where METRIC_TPL_HEADER_ID = x_METRIC_TPL_HEADER_ID
493 last_update_date = sysdate,
494 last_updated_by = decode(x_owner, 'SEED', 1, 0),
495 last_update_login = 0
497 and userenv('LANG') in (language, source_lang);
498 end TRANSLATE_ROW;
499
500 END AMS_MET_TPL_HEADERS_B_PKG;