[Home] [Help]
PACKAGE BODY: APPS.AMS_METRICS_ALL_PKG
Source
1 package body AMS_METRICS_ALL_PKG as
2 /* $Header: amslmtcb.pls 120.2 2011/06/07 07:44:44 annsrini ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_METRICS_ALL_PKG
7 -- Purpose
8 --
9 -- History
10 -- 03/06/2003 dmvincen BUG2819067: Do not update if customized.
11 -- 08/20/2003 dmvincen Added Display Type.
12 --
13 -- NOTE
14 --
15 -- End of Comments
16 -- ===============================================================
17 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
18 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
19 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
20
21 procedure INSERT_ROW (
22 X_ROWID in VARCHAR2,
23 X_METRIC_ID in NUMBER,
24 X_OBJECT_VERSION_NUMBER in NUMBER,
25 X_ARC_METRIC_USED_FOR_OBJECT in VARCHAR2,
26 X_METRIC_CALCULATION_TYPE in VARCHAR2,
27 X_APPLICATION_ID in NUMBER,
28 X_METRIC_CATEGORY in NUMBER,
29 X_ACCRUAL_TYPE in VARCHAR2,
30 X_VALUE_TYPE in VARCHAR2,
31 X_SENSITIVE_DATA_FLAG in VARCHAR2,
32 X_ENABLED_FLAG in VARCHAR2,
33 X_METRIC_SUB_CATEGORY in NUMBER,
34 X_FUNCTION_NAME in VARCHAR2,
35 X_FUNCTION_TYPE in VARCHAR2,
36 X_METRIC_PARENT_ID in NUMBER,
37 X_SUMMARY_METRIC_ID in NUMBER,
38 X_COMPUTE_USING_FUNCTION in VARCHAR2,
39 X_DEFAULT_UOM_CODE in VARCHAR2,
40 X_UOM_TYPE in VARCHAR2,
41 X_FORMULA in VARCHAR2,
42 X_DISPLAY_TYPE in VARCHAR2,
43 X_METRICS_NAME in VARCHAR2,
44 X_DESCRIPTION in VARCHAR2,
45 X_FORMULA_DISPLAY in VARCHAR2,
46 X_TARGET_TYPE in VARCHAR2,
47 X_DENORM_CODE in VARCHAR2,
48 X_CREATION_DATE in DATE,
49 X_CREATED_BY in NUMBER,
50 X_LAST_UPDATE_DATE in DATE,
51 X_LAST_UPDATED_BY in NUMBER,
52 X_LAST_UPDATE_LOGIN in NUMBER
53 ) is
54 cursor C is select ROWID from AMS_METRICS_ALL_B
55 where METRIC_ID = X_METRIC_ID
56 ;
57 l_rowid VARCHAR2(1000);
58 begin
59 insert into AMS_METRICS_ALL_B (
60 METRIC_ID,
61 OBJECT_VERSION_NUMBER,
62 ARC_METRIC_USED_FOR_OBJECT,
63 METRIC_CALCULATION_TYPE,
64 APPLICATION_ID,
65 METRIC_CATEGORY,
66 ACCRUAL_TYPE,
67 VALUE_TYPE,
68 SENSITIVE_DATA_FLAG,
69 ENABLED_FLAG,
70 METRIC_SUB_CATEGORY,
71 FUNCTION_NAME,
72 FUNCTION_TYPE,
73 METRIC_PARENT_ID,
74 SUMMARY_METRIC_ID,
75 COMPUTE_USING_FUNCTION,
76 DEFAULT_UOM_CODE,
77 UOM_TYPE,
78 FORMULA,
79 DISPLAY_TYPE,
80 TARGET_TYPE,
81 DENORM_CODE,
82 CREATION_DATE,
83 CREATED_BY,
84 LAST_UPDATE_DATE,
85 LAST_UPDATED_BY,
86 LAST_UPDATE_LOGIN
87 ) values (
88 X_METRIC_ID,
89 X_OBJECT_VERSION_NUMBER,
90 X_ARC_METRIC_USED_FOR_OBJECT,
91 X_METRIC_CALCULATION_TYPE,
92 X_APPLICATION_ID,
93 X_METRIC_CATEGORY,
94 X_ACCRUAL_TYPE,
95 X_VALUE_TYPE,
96 X_SENSITIVE_DATA_FLAG,
97 X_ENABLED_FLAG,
98 X_METRIC_SUB_CATEGORY,
99 X_FUNCTION_NAME,
100 X_FUNCTION_TYPE,
101 X_METRIC_PARENT_ID,
102 X_SUMMARY_METRIC_ID,
103 X_COMPUTE_USING_FUNCTION,
104 X_DEFAULT_UOM_CODE,
105 X_UOM_TYPE,
106 X_FORMULA,
107 X_DISPLAY_TYPE,
108 X_TARGET_TYPE,
109 X_DENORM_CODE,
110 X_CREATION_DATE,
111 X_CREATED_BY,
112 X_LAST_UPDATE_DATE,
113 X_LAST_UPDATED_BY,
114 X_LAST_UPDATE_LOGIN
115 );
116
117 insert into AMS_METRICS_ALL_TL (
118 METRICS_NAME,
119 DESCRIPTION,
120 FORMULA_DISPLAY,
121 METRIC_ID,
122 LAST_UPDATE_DATE,
123 LAST_UPDATED_BY,
124 CREATION_DATE,
125 CREATED_BY,
126 LAST_UPDATE_LOGIN,
127 LANGUAGE,
128 SOURCE_LANG
129 ) select
130 X_METRICS_NAME,
131 X_DESCRIPTION,
132 X_FORMULA_DISPLAY,
133 X_METRIC_ID,
134 X_LAST_UPDATE_DATE,
135 X_LAST_UPDATED_BY,
136 X_CREATION_DATE,
137 X_CREATED_BY,
138 X_LAST_UPDATE_LOGIN,
139 L.LANGUAGE_CODE,
140 userenv('LANG')
141 from FND_LANGUAGES L
142 where L.INSTALLED_FLAG in ('I', 'B')
143 and not exists
144 (select NULL
145 from AMS_METRICS_ALL_TL T
146 where T.METRIC_ID = X_METRIC_ID
147 and T.LANGUAGE = L.LANGUAGE_CODE);
148
149 open c;
150 fetch c into l_ROWID;
151 if (c%notfound) then
152 close c;
153 raise no_data_found;
154 end if;
155 close c;
156
157 end INSERT_ROW;
158
159 procedure LOCK_ROW (
160 X_METRIC_ID in NUMBER,
161 X_OBJECT_VERSION_NUMBER in NUMBER,
162 X_ARC_METRIC_USED_FOR_OBJECT in VARCHAR2,
163 X_METRIC_CALCULATION_TYPE in VARCHAR2,
164 X_APPLICATION_ID in NUMBER,
165 X_METRIC_CATEGORY in NUMBER,
166 X_ACCRUAL_TYPE in VARCHAR2,
167 X_VALUE_TYPE in VARCHAR2,
168 X_SENSITIVE_DATA_FLAG in VARCHAR2,
169 X_ENABLED_FLAG in VARCHAR2,
170 X_METRIC_SUB_CATEGORY in NUMBER,
171 X_FUNCTION_NAME in VARCHAR2,
172 X_FUNCTION_TYPE in VARCHAR2,
173 X_METRIC_PARENT_ID in NUMBER,
174 X_SUMMARY_METRIC_ID in NUMBER,
175 X_COMPUTE_USING_FUNCTION in VARCHAR2,
176 X_DEFAULT_UOM_CODE in VARCHAR2,
177 X_UOM_TYPE in VARCHAR2,
178 X_FORMULA in VARCHAR2,
179 X_DISPLAY_TYPE in VARCHAR2,
180 X_METRICS_NAME in VARCHAR2,
181 X_DESCRIPTION in VARCHAR2,
182 X_FORMULA_DISPLAY in VARCHAR2,
183 X_TARGET_TYPE in VARCHAR2,
184 X_DENORM_CODE in VARCHAR2
185 ) is
186 cursor c is select
187 OBJECT_VERSION_NUMBER,
188 ARC_METRIC_USED_FOR_OBJECT,
189 METRIC_CALCULATION_TYPE,
190 APPLICATION_ID,
191 METRIC_CATEGORY,
192 ACCRUAL_TYPE,
193 VALUE_TYPE,
194 SENSITIVE_DATA_FLAG,
195 ENABLED_FLAG,
196 METRIC_SUB_CATEGORY,
197 FUNCTION_NAME,
198 FUNCTION_TYPE,
199 METRIC_PARENT_ID,
200 SUMMARY_METRIC_ID,
201 COMPUTE_USING_FUNCTION,
202 DEFAULT_UOM_CODE,
203 UOM_TYPE,
204 FORMULA,
205 DISPLAY_TYPE,
206 TARGET_TYPE,
207 DENORM_CODE
208 from AMS_METRICS_ALL_B
209 where METRIC_ID = X_METRIC_ID
210 for update of METRIC_ID nowait;
211 recinfo c%rowtype;
212
213 cursor c1 is select
214 METRICS_NAME,
215 DESCRIPTION,
216 FORMULA_DISPLAY,
217 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
218 from AMS_METRICS_ALL_TL
219 where METRIC_ID = X_METRIC_ID
220 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
221 for update of METRIC_ID nowait;
222 begin
223 open c;
224 fetch c into recinfo;
225 if (c%notfound) then
226 close c;
227 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
228 app_exception.raise_exception;
229 end if;
230 close c;
231 if ( ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
232 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND
233 (X_OBJECT_VERSION_NUMBER is null)))
234 AND (recinfo.ARC_METRIC_USED_FOR_OBJECT = X_ARC_METRIC_USED_FOR_OBJECT)
235 AND (recinfo.METRIC_CALCULATION_TYPE = X_METRIC_CALCULATION_TYPE)
236 AND (recinfo.APPLICATION_ID = X_APPLICATION_ID)
237 AND (recinfo.METRIC_CATEGORY = X_METRIC_CATEGORY)
238 AND (recinfo.ACCRUAL_TYPE = X_ACCRUAL_TYPE)
239 AND (recinfo.VALUE_TYPE = X_VALUE_TYPE)
240 AND (recinfo.SENSITIVE_DATA_FLAG = X_SENSITIVE_DATA_FLAG)
241 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
242 AND ((recinfo.METRIC_SUB_CATEGORY = X_METRIC_SUB_CATEGORY)
243 OR ((recinfo.METRIC_SUB_CATEGORY is null) AND
244 (X_METRIC_SUB_CATEGORY is null)))
245 AND ((recinfo.FUNCTION_NAME = X_FUNCTION_NAME)
246 OR ((recinfo.FUNCTION_NAME is null) AND
247 (X_FUNCTION_NAME is null)))
248 AND ((recinfo.FUNCTION_TYPE = X_FUNCTION_TYPE)
249 OR ((recinfo.FUNCTION_TYPE is null) AND
250 (X_FUNCTION_TYPE is null)))
251 AND ((recinfo.METRIC_PARENT_ID = X_METRIC_PARENT_ID)
252 OR ((recinfo.METRIC_PARENT_ID is null) AND
253 (X_METRIC_PARENT_ID is null)))
254 AND ((recinfo.SUMMARY_METRIC_ID = X_SUMMARY_METRIC_ID)
255 OR ((recinfo.SUMMARY_METRIC_ID is null) AND
256 (X_SUMMARY_METRIC_ID is null)))
257 AND ((recinfo.COMPUTE_USING_FUNCTION = X_COMPUTE_USING_FUNCTION)
258 OR ((recinfo.COMPUTE_USING_FUNCTION is null) AND
259 (X_COMPUTE_USING_FUNCTION is null)))
260 AND ((recinfo.DEFAULT_UOM_CODE = X_DEFAULT_UOM_CODE)
261 OR ((recinfo.DEFAULT_UOM_CODE is null) AND
262 (X_DEFAULT_UOM_CODE is null)))
263 AND ((recinfo.UOM_TYPE = X_UOM_TYPE)
264 OR ((recinfo.UOM_TYPE is null) AND (X_UOM_TYPE is null)))
265 AND ((recinfo.FORMULA = X_FORMULA)
266 OR ((recinfo.FORMULA is null) AND (X_FORMULA is null)))
267 AND ((recinfo.DISPLAY_TYPE = X_DISPLAY_TYPE)
268 OR ((recinfo.DISPLAY_TYPE is null) AND (X_DISPLAY_TYPE is null)))
269 AND ((recinfo.TARGET_TYPE = X_TARGET_TYPE)
270 OR ((recinfo.TARGET_TYPE is null) AND (X_TARGET_TYPE is null)))
271 AND ((recinfo.DENORM_CODE = X_DENORM_CODE)
272 OR ((recinfo.DENORM_CODE is null) AND (X_DENORM_CODE is null)))
273 ) then
274 null;
275 else
276 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
277 app_exception.raise_exception;
278 end if;
279
280 for tlinfo in c1 loop
281 if (tlinfo.BASELANG = 'Y') then
282 if ( (tlinfo.METRICS_NAME = X_METRICS_NAME)
283 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
284 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
285 AND ((tlinfo.FORMULA_DISPLAY = X_FORMULA_DISPLAY)
286 OR ((tlinfo.FORMULA_DISPLAY is null)
290 else
287 AND (X_FORMULA_DISPLAY is null)))
288 ) then
289 null;
291 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
292 app_exception.raise_exception;
293 end if;
294 end if;
295 end loop;
296 return;
297 end LOCK_ROW;
298
299 procedure UPDATE_ROW (
300 X_METRIC_ID in NUMBER,
301 X_OBJECT_VERSION_NUMBER in NUMBER,
302 X_ARC_METRIC_USED_FOR_OBJECT in VARCHAR2,
303 X_METRIC_CALCULATION_TYPE in VARCHAR2,
304 X_APPLICATION_ID in NUMBER,
305 X_METRIC_CATEGORY in NUMBER,
306 X_ACCRUAL_TYPE in VARCHAR2,
307 X_VALUE_TYPE in VARCHAR2,
308 X_SENSITIVE_DATA_FLAG in VARCHAR2,
309 X_ENABLED_FLAG in VARCHAR2,
310 X_METRIC_SUB_CATEGORY in NUMBER,
311 X_FUNCTION_NAME in VARCHAR2,
312 X_FUNCTION_TYPE in VARCHAR2,
313 X_METRIC_PARENT_ID in NUMBER,
314 X_SUMMARY_METRIC_ID in NUMBER,
315 X_COMPUTE_USING_FUNCTION in VARCHAR2,
316 X_DEFAULT_UOM_CODE in VARCHAR2,
317 X_UOM_TYPE in VARCHAR2,
318 X_FORMULA in VARCHAR2,
319 X_DISPLAY_TYPE in VARCHAR2,
320 X_METRICS_NAME in VARCHAR2,
321 X_DESCRIPTION in VARCHAR2,
322 X_FORMULA_DISPLAY in VARCHAR2,
323 X_TARGET_TYPE in VARCHAR2,
324 X_DENORM_CODE in VARCHAR2,
325 X_LAST_UPDATE_DATE in DATE,
326 X_LAST_UPDATED_BY in NUMBER,
327 X_LAST_UPDATE_LOGIN in NUMBER
328 ) is
329 begin
330 update AMS_METRICS_ALL_B set
331 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
332 ARC_METRIC_USED_FOR_OBJECT = X_ARC_METRIC_USED_FOR_OBJECT,
333 METRIC_CALCULATION_TYPE = X_METRIC_CALCULATION_TYPE,
334 APPLICATION_ID = X_APPLICATION_ID,
335 METRIC_CATEGORY = X_METRIC_CATEGORY,
336 ACCRUAL_TYPE = X_ACCRUAL_TYPE,
337 VALUE_TYPE = X_VALUE_TYPE,
338 SENSITIVE_DATA_FLAG = X_SENSITIVE_DATA_FLAG,
339 ENABLED_FLAG = X_ENABLED_FLAG,
340 METRIC_SUB_CATEGORY = X_METRIC_SUB_CATEGORY,
341 FUNCTION_NAME = X_FUNCTION_NAME,
342 FUNCTION_TYPE = X_FUNCTION_TYPE,
343 METRIC_PARENT_ID = X_METRIC_PARENT_ID,
344 SUMMARY_METRIC_ID = X_SUMMARY_METRIC_ID,
345 COMPUTE_USING_FUNCTION = X_COMPUTE_USING_FUNCTION,
346 DEFAULT_UOM_CODE = X_DEFAULT_UOM_CODE,
347 UOM_TYPE = X_UOM_TYPE,
348 FORMULA = X_FORMULA,
349 DISPLAY_TYPE = X_DISPLAY_TYPE,
350 TARGET_TYPE = X_TARGET_TYPE,
351 DENORM_CODE = X_DENORM_CODE,
352 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
353 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
354 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
355 where METRIC_ID = X_METRIC_ID;
356
357 if (sql%notfound) then
358 raise no_data_found;
359 end if;
360
361 update AMS_METRICS_ALL_TL set
362 METRICS_NAME = X_METRICS_NAME,
363 DESCRIPTION = X_DESCRIPTION,
364 FORMULA_DISPLAY = X_FORMULA_DISPLAY,
365 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
366 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
367 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
368 SOURCE_LANG = userenv('LANG')
369 where METRIC_ID = X_METRIC_ID
370 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
371
372 if (sql%notfound) then
373 raise no_data_found;
374 end if;
375 end UPDATE_ROW;
376
377 procedure DELETE_ROW (
378 X_METRIC_ID in NUMBER
379 ) is
380 begin
381 delete from AMS_METRICS_ALL_TL
385 raise no_data_found;
382 where METRIC_ID = X_METRIC_ID;
383
384 if (sql%notfound) then
386 end if;
387
388 delete from AMS_METRICS_ALL_B
389 where METRIC_ID = X_METRIC_ID;
390
391 if (sql%notfound) then
392 raise no_data_found;
393 end if;
394 end DELETE_ROW;
395
396 procedure ADD_LANGUAGE
397 is
398 begin
399 delete from AMS_METRICS_ALL_TL T
400 where not exists
401 (select NULL
402 from AMS_METRICS_ALL_B B
403 where B.METRIC_ID = T.METRIC_ID
404 );
405
406 update AMS_METRICS_ALL_TL T set (
407 METRICS_NAME,
408 DESCRIPTION,
409 FORMULA_DISPLAY
410 ) = (select
411 B.METRICS_NAME,
412 B.DESCRIPTION,
413 B.FORMULA_DISPLAY
414 from AMS_METRICS_ALL_TL B
415 where B.METRIC_ID = T.METRIC_ID
416 and B.LANGUAGE = T.SOURCE_LANG)
417 where (
418 T.METRIC_ID,
419 T.LANGUAGE
420 ) in (select
421 SUBT.METRIC_ID,
422 SUBT.LANGUAGE
423 from AMS_METRICS_ALL_TL SUBB, AMS_METRICS_ALL_TL SUBT
424 where SUBB.METRIC_ID = SUBT.METRIC_ID
425 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
426 and (SUBB.METRICS_NAME <> SUBT.METRICS_NAME
427 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
431 or (SUBB.FORMULA_DISPLAY is null and SUBT.FORMULA_DISPLAY is not null)
428 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
429 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
430 or SUBB.FORMULA_DISPLAY <> SUBT.FORMULA_DISPLAY
432 or (SUBB.FORMULA_DISPLAY is not null and SUBT.FORMULA_DISPLAY is null)
433 ));
434
435 insert into AMS_METRICS_ALL_TL (
436 METRICS_NAME,
437 DESCRIPTION,
438 FORMULA_DISPLAY,
439 METRIC_ID,
440 LAST_UPDATE_DATE,
441 LAST_UPDATED_BY,
442 CREATION_DATE,
443 CREATED_BY,
444 LAST_UPDATE_LOGIN,
445 LANGUAGE,
446 SOURCE_LANG
447 ) select
448 B.METRICS_NAME,
449 B.DESCRIPTION,
450 B.FORMULA_DISPLAY,
451 B.METRIC_ID,
452 B.LAST_UPDATE_DATE,
453 B.LAST_UPDATED_BY,
454 B.CREATION_DATE,
455 B.CREATED_BY,
456 B.LAST_UPDATE_LOGIN,
457 L.LANGUAGE_CODE,
458 B.SOURCE_LANG
459 from AMS_METRICS_ALL_TL B, FND_LANGUAGES L
460 where L.INSTALLED_FLAG in ('I', 'B')
461 and B.LANGUAGE = userenv('LANG')
462 and not exists
463 (select NULL
464 from AMS_METRICS_ALL_TL T
465 where T.METRIC_ID = B.METRIC_ID
466 and T.LANGUAGE = L.LANGUAGE_CODE);
467 end ADD_LANGUAGE;
468
469 procedure TRANSLATE_ROW(
470 x_metric_id in NUMBER
471 , x_metrics_name in VARCHAR2
472 , x_description in VARCHAR2
473 , x_formula_display in VARCHAR2
474 , x_owner in VARCHAR2
475 ) is
476 begin
477 update AMS_METRICS_ALL_TL set
478 metrics_name = nvl(x_metrics_name, metrics_name),
479 description = nvl(x_description, description),
480 formula_display = nvl(x_formula_display, formula_display),
481 source_lang = userenv('LANG'),
482 last_update_date = sysdate,
483 last_updated_by = decode(x_owner, 'SEED', 1, 0),
484 last_update_login = 0
485 where metric_id = x_metric_id
486 and userenv('LANG') in (language, source_lang);
487 end TRANSLATE_ROW;
488
489 procedure LOAD_ROW(
490 X_METRIC_ID in NUMBER,
491 X_ARC_METRIC_USED_FOR_OBJECT in VARCHAR2,
492 X_METRIC_CALCULATION_TYPE in VARCHAR2,
493 X_APPLICATION_ID in NUMBER,
494 X_METRIC_CATEGORY in NUMBER,
495 X_ACCRUAL_TYPE in VARCHAR2,
496 X_VALUE_TYPE in VARCHAR2,
497 X_SENSITIVE_DATA_FLAG in VARCHAR2,
498 X_ENABLED_FLAG in VARCHAR2,
499 X_METRIC_SUB_CATEGORY in NUMBER,
500 X_FUNCTION_NAME in VARCHAR2,
501 X_FUNCTION_TYPE in VARCHAR2,
502 X_METRIC_PARENT_ID in NUMBER,
503 X_SUMMARY_METRIC_ID in NUMBER,
504 X_COMPUTE_USING_FUNCTION in VARCHAR2,
505 X_DEFAULT_UOM_CODE in VARCHAR2,
506 X_UOM_TYPE in VARCHAR2,
507 X_FORMULA in VARCHAR2,
508 X_DISPLAY_TYPE in VARCHAR2,
509 X_METRICS_NAME in VARCHAR2,
510 X_DESCRIPTION in VARCHAR2,
511 X_FORMULA_DISPLAY in VARCHAR2,
512 X_TARGET_TYPE in VARCHAR2,
513 X_DENORM_CODE in VARCHAR2,
514 X_Owner IN VARCHAR2,
515 X_CUSTOM_MODE IN VARCHAR2
516 ) is
517
518 l_user_id number := 0;
519 l_obj_verno number;
520 l_dummy_char varchar2(1);
521 l_row_id varchar2(100);
522 l_metric_id number;
523 l_db_luby_id NUMBER;
524
525 cursor c_db_data_details is
526 select last_updated_by, object_version_number
527 from AMS_METRICS_ALL_B
528 where metric_id = X_METRIC_ID;
529
530 cursor c_chk_mtc_exists is
531 select 'x'
532 from AMS_METRICS_ALL_B
533 where metric_id = X_METRIC_ID;
534
535 cursor c_get_mtcid is
536 select AMS_METRICS_ALL_B_S.nextval
537 from dual;
538
539 BEGIN
540
541 -- set the last_updated_by to be used while updating the data in customer data.
542 if X_OWNER = 'SEED' then
543 l_user_id := 1;
544 elsif X_OWNER = 'ORACLE' THEN
545 l_user_id := 2;
546 elsif X_OWNER = 'SYSADMIN' THEN
547 l_user_id := 0;
548 end if ;
549
550 open c_chk_mtc_exists;
551 fetch c_chk_mtc_exists into l_dummy_char;
552 if c_chk_mtc_exists%notfound
553 then
554 close c_chk_mtc_exists;
555
556 if x_metric_id is null then
557 open c_get_mtcid;
558 fetch c_get_mtcid into l_metric_id;
559 close c_get_mtcid;
560 else
561 l_metric_id := x_metric_id ;
562 end if ;
563
564 l_obj_verno := 1;
565
566 AMS_METRICS_ALL_PKG.INSERT_ROW (
567 X_ROWID => l_row_id ,
568 X_METRIC_ID => l_metric_id,
569 X_OBJECT_VERSION_NUMBER => l_obj_verno,
570 X_ARC_METRIC_USED_FOR_OBJECT => X_ARC_METRIC_USED_FOR_OBJECT,
571 X_METRIC_CALCULATION_TYPE => X_METRIC_CALCULATION_TYPE,
572 X_APPLICATION_ID => X_APPLICATION_ID,
573 X_METRIC_CATEGORY => X_METRIC_CATEGORY,
574 X_ACCRUAL_TYPE => X_ACCRUAL_TYPE,
575 X_VALUE_TYPE => X_VALUE_TYPE,
576 X_SENSITIVE_DATA_FLAG => X_SENSITIVE_DATA_FLAG,
577 X_ENABLED_FLAG => X_ENABLED_FLAG,
578 X_METRIC_SUB_CATEGORY => X_METRIC_SUB_CATEGORY,
579 X_FUNCTION_NAME => X_FUNCTION_NAME,
580 X_FUNCTION_TYPE => X_FUNCTION_TYPE,
581 X_METRIC_PARENT_ID => X_METRIC_PARENT_ID,
585 X_UOM_TYPE => X_UOM_TYPE,
582 X_SUMMARY_METRIC_ID => X_SUMMARY_METRIC_ID,
583 X_COMPUTE_USING_FUNCTION => X_COMPUTE_USING_FUNCTION,
584 X_DEFAULT_UOM_CODE => X_DEFAULT_UOM_CODE,
586 X_FORMULA => X_FORMULA,
587 X_DISPLAY_TYPE => X_DISPLAY_TYPE,
588 X_METRICS_NAME => X_METRICS_NAME,
589 X_DESCRIPTION => X_DESCRIPTION,
590 X_FORMULA_DISPLAY => X_FORMULA_DISPLAY,
591 X_TARGET_TYPE => X_TARGET_TYPE,
592 X_DENORM_CODE => X_DENORM_CODE,
593 X_CREATION_DATE => SYSDATE,
594 X_CREATED_BY => l_user_id,
595 X_LAST_UPDATE_DATE => SYSDATE,
596 X_LAST_UPDATED_BY => l_user_id,
597 X_LAST_UPDATE_LOGIN => 0
598 ) ;
599
600 else
601 close c_chk_mtc_exists;
602 open c_db_data_details;
603 fetch c_db_data_details into l_db_luby_id, l_obj_verno;
604 close c_db_data_details;
605 if ( l_db_luby_id IN (1, 2, 0)
606 OR NVL(x_custom_mode,'PRESERVE') = 'FORCE') THEN
607 AMS_METRICS_ALL_PKG.UPDATE_ROW(
608 X_METRIC_ID => X_METRIC_ID,
609 X_OBJECT_VERSION_NUMBER => l_obj_verno + 1,
610 X_ARC_METRIC_USED_FOR_OBJECT => X_ARC_METRIC_USED_FOR_OBJECT,
611 X_METRIC_CALCULATION_TYPE => X_METRIC_CALCULATION_TYPE,
612 X_APPLICATION_ID => X_APPLICATION_ID,
613 X_METRIC_CATEGORY => X_METRIC_CATEGORY,
614 X_ACCRUAL_TYPE => X_ACCRUAL_TYPE,
615 X_VALUE_TYPE => X_VALUE_TYPE,
616 X_SENSITIVE_DATA_FLAG => X_SENSITIVE_DATA_FLAG,
617 X_ENABLED_FLAG => X_ENABLED_FLAG,
618 X_METRIC_SUB_CATEGORY => X_METRIC_SUB_CATEGORY,
619 X_FUNCTION_NAME => X_FUNCTION_NAME,
620 X_FUNCTION_TYPE => X_FUNCTION_TYPE,
621 X_METRIC_PARENT_ID => X_METRIC_PARENT_ID,
622 X_SUMMARY_METRIC_ID => X_SUMMARY_METRIC_ID,
623 X_COMPUTE_USING_FUNCTION => X_COMPUTE_USING_FUNCTION,
624 X_DEFAULT_UOM_CODE => X_DEFAULT_UOM_CODE,
625 X_UOM_TYPE => X_UOM_TYPE,
626 X_FORMULA => X_FORMULA,
627 X_DISPLAY_TYPE => X_DISPLAY_TYPE,
628 X_METRICS_NAME => X_METRICS_NAME,
629 X_DESCRIPTION => X_DESCRIPTION,
630 X_FORMULA_DISPLAY => X_FORMULA_DISPLAY,
631 X_TARGET_TYPE => X_TARGET_TYPE,
632 X_DENORM_CODE => X_DENORM_CODE,
633 X_LAST_UPDATE_DATE => SYSDATE,
634 X_LAST_UPDATED_BY => l_user_id,
635 X_LAST_UPDATE_LOGIN => 0
636 );
637 end if;
638 end if;
639 END LOAD_ROW;
640
641
642 end AMS_METRICS_ALL_PKG;