[Home] [Help]
PACKAGE BODY: APPS.AMS_METRIC_PVT
Source
1 PACKAGE BODY AMS_METRIC_PVT AS
2 /* $Header: amsvmtcb.pls 120.1 2005/08/24 23:08:56 dmvincen noship $ */
3
4 ------------------------------------------------------------------------------
5 --
6 -- NAME
7 -- AMS_Metrics_PVT
8 --
9 -- HISTORY
10 -- 25-may-1999 choang@us Created package.
11 -- 26-may-1999 choang@us Completed create API. Created templates and
12 -- definitions for all other API's.
13 -- 27-may-1999 choang@us Added check_req_metrics_rec.
14 -- 28-may-1999 choang@us Completed update, delete and lock API's. Added
15 -- validate_metric_items. Began procedure for
16 -- validating child entities. Completed
17 -- validate_metric API and added ORG_ID to create and
18 -- update. Began creating template
19 -- API's for activity metrics.
20 -- 31-may-1999 choang@us Updated package qualifier for utility functions to
21 -- use AMS_Utility_PVT instead of AMS_Global_PVT.
22 -- 01-jun-1999 choang@us Added insert and update API for activity metrics
23 -- -- untested.
24 -- 02-jun-1999 choang@us Began on lock API.
25 -- 07-jun-1999 choang@us Added validate rec types extracted from global
26 -- package. Removed references to global package for
27 -- validate rec types.
28 -- 08-jun-1999 choang@us Added dummy procedure for defaulting values.
29 -- Completed lock and delete API's for activity
30 -- metrics. Completed procedure for item level
31 -- validation for activity metrics. Changed order of
32 -- parameter list for API standards.
33 -- 10-jun-1999 choang@us Corrected validate API for metrics and activity
34 -- metrics to return the rec as an OUT variable --
35 -- this allows default values to be set properly.
36 -- Modified case of packages, procedures and functions
37 -- to conform to standards.
38 -- 14-jun-1999 choang@us Updated all case standards for procedure
39 -- references.
40 -- 22-jun-1999 choang@us Moved activity metrics to common objects package.
41 -- 17-jul-1999 choang@us Added validation for delete and update of seed
42 -- data. Added 'get' API's for metric values and
43 -- metric category values. Added refresh and refresh
44 -- all API's. Added API for update of committed value.
45 -- 30-jul-1999 choang@us Completed calculation engine for summary -- needs
46 -- testing, but no data yet. Added addition item
47 -- level validation to implement business rules for
48 -- hierarchy rollup and summarization.
49 -- 04-aug-1999 choang@us Consolidated refresh and refresh all into one
50 -- procedure by adding flag to refresh API to
51 -- indicate whether to refresh one metric or
52 -- all associated metrics.
53 -- 15-aug-1999 ptendulk@us Removed references to G_MISS_NUM and G_MISS_CHAR;
54 -- fixed child entity validation logic.
55 -- 01-sep-1999 choang@us Made the following specs public:
56 -- Validate_Metric_Items, IsSeeded,
57 -- Validate_Metric_Child per request of ptendulk.
58 -- 04-Oct-1999 ptendulk@us Added Changed Metric Refresh Engine(UOM and
59 -- Currency Conversion)
60 -- 09-Oct-1999 ptendulk@us Seperated Metric package with Refresh Engine
61 -- Package and made changes according to new
62 -- standards.
63 -- 01/18/2000 bgeorge Reviewed code, made UOM non-required, removed
64 -- function ISSEEDED from the specs.
65 -- 17-Apr-2000 tdonohoe@us Added columns to metric_rec_type to support
66 -- 11.5.2 release.
67 -- 07/17/2000 khung@us bug 1356700 fix. modify check_uniqueness() where
68 -- clause
69 -- 11/15/2000 sveerave@us bug 1490374 fix. removed reference to
70 -- check_uniqueness and added new logic.
71 -- 11/28/2000 sveerave@us bug 1499845 fix.
72 -- 04/27/2001 dmvincen@us Added SUMMARY metric calculation type. #1753241
73 -- 05/04/2001 dmvincen@us Allow name and enable to update even if assigned.
74 -- 05/15/2001 dmvincen@us Allow SUMMARY even if not seeded. For 11.5.4.11.
75 -- 06/07/2001 huili@ Alow rollup metric to summarize to metrics of
76 -- different business types for 11.5.5
77 -- 06/14/2001 huili Comment out validation for "VARIABLE" metrics.
78 -- 06/19/2001 dmvincen Added RCAM and EONE object types.
79 -- 06/29/2001 huili Bug fix #1831746.
80 -- 07/09/2001 huili Bug fix #1865864.
81 -- 09/07/2001 huili Added the "Validate_Metric_Program" function.
82 -- 10/04/2001 dmvincen Added used with ANY for rollup and summary metrics.
83 -- 10/08/2001 huili Remove the message initialization in the
84 -- "Get_Function_Type".
85 -- 10/09/2001 huili Remove the schema checking for seeded function
86 -- metrics.
87 -- 10/12/2001 huili Pass the "FND_API.G_FALSE" to the "p_encoded"
88 -- parameter of the "FND_MSG_PUB.Count_And_Get"
89 -- module.
90 -- 10/29/2001 huili Add the " Inter_Metric_Validation" module and link
91 -- it to the "update_metric" module.
92 -- 12/26/2001 dmvincen Metrics can rollup to any type of object.
93 -- 12/27/2001 dmvincen Seeded metrics can update enabled flag.
94 -- 03/13/2002 dmvincen Added dialog components.
95 -- 03/13/2002 dmvincen Rollup/summary object type is always 'ANY'.
96 -- No validation required.
97 -- 04/03/2002 dmvincen Summary and Rollups have 'ANY' used with.
98 -- 06/14/2002 dmvincen BUG2411660: Test for dependent metric corrected.
99 -- 07/09/2002 dmvincen BUG2450504,2448534,2448518: Set encoding to false.
100 -- 11/18/2002 dmvincen Added EONE.
101 -- 01/08/2003 dmvincen BUG2741868: Disable summary metrics.
102 -- 03/04/2003 dmvincen BUG2830166: Update metric name.
103 -- 03/11/2003 dmvincen BUG2845365: Removed Dialogue components.
104 -- 08/27/2003 sunkumar BUG3116703: Modified Validate_Metric_Program
105 -- 08/29/2003 dmvincen Adding display type.
106 -- 02/19/2004 sunkumar bug#3453994
107 -- 02/24/2004 dmvincen BUG3465714: Record validation on create.
108 -- 04/20/2004 sunkumar removed reference to ams_utility_pvt.checkcheck_fk_exists
109 -- 06/17/2004 sunkumar BUG#3697901: Function Type not setting up
110 -- 06/18/2004 sunkumar removed reference to get_function_type instead
111 -- setting up the function/procedure flag in
112 -- Validate_Metric_Program, Made
113 -- Validate_Metric_Program a procedure, earlier it
114 -- was a function.
115 -- 11/10/2004 dmvincen BUG3792709: Fixed program validation.
116 -- 06-Jan-2006 choang Bug 4107480: fixed update api to calc func_type all the
117 -- time and removed obsoleted procedure get_function_type
118 -------------------------------------------------------------------------------
119
120 --
121 -- Global variables and constants.
122
123 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMS_METRIC_PVT'; -- Name of the current package.
124 G_DEBUG_FLAG VARCHAR2(1) := 'Y';
125
126 G_ROLLUP CONSTANT VARCHAR2(30) := 'ROLLUP';
127 G_SUMMARY CONSTANT VARCHAR2(30) := 'SUMMARY';
128 G_MANUAL CONSTANT VARCHAR2(30) := 'MANUAL';
129 G_FUNCTION CONSTANT VARCHAR2(30) := 'FUNCTION';
130 G_FORMULA CONSTANT VARCHAR2(30) := 'FORMULA';
131
132 G_FIXED CONSTANT VARCHAR2(30) := 'FIXED';
133 G_VARIABLE CONSTANT VARCHAR2(30) := 'VARIABLE';
134
135 G_COST_ID NUMBER := 901;
136 G_REVENUE_ID NUMBER :=902;
137
138 -- Start of comments
139 -- API Name IsSeeded
140 -- Type Private
141 -- Pre-reqs None.
142 -- Function Returns whether the given ID is that of a seeded record.
143 -- Parameters
144 -- IN p_id IN ams_metrics_all_vl.metric_id%TYPE Required
145 -- OUT Boolean (True/FALSE)
146 -- Version Current version: 1.0
147 -- Previous version: 1.0
148 -- Initial version: 1.0
149 -- End of comments
150 FUNCTION IsSeeded (
151 p_id IN NUMBER
152 ) RETURN BOOLEAN ;
153
154 PROCEDURE Complete_Metric_Rec(
155 p_metric_rec IN metric_rec_type,
156 x_complete_rec IN OUT NOCOPY metric_rec_type,
157 x_old_metric_rec IN OUT NOCOPY metric_rec_type,
158 x_seeded_ok IN OUT NOCOPY BOOLEAN
159 );
160
161 --
162 -- Start of comments.
163 --
164 -- NAME
165 -- Inter_Metric_Validation
166 --
167 -- PURPOSE
168 -- Validation for all metrics (rollup, summary parents and children, variable metrics)
169 -- which have relationship with this one.
170 --
171 -- NOTES
172 --
173 -- HISTORY
174 -- 10/26/2001 huili Created.
175 --
176 -- End of comments.
177 PROCEDURE Inter_Metric_Validation (
178 p_metric_rec IN metric_rec_type,
179 x_return_status OUT NOCOPY VARCHAR2
180 )
181 IS
182 CURSOR c_metric IS
183 SELECT metric_id, metric_calculation_type, metric_category, accrual_type
184 FROM ams_metrics_all_b
185 WHERE metric_id = p_metric_rec.metric_id;
186
187 l_metric_rec c_metric%ROWTYPE;
188
189 CURSOR c_check_rollup_children (p_met_id NUMBER) IS
190 SELECT 1
191 FROM ams_metrics_all_b
192 WHERE metric_parent_id = p_met_id;
193
194 CURSOR c_check_summary_children (p_met_id NUMBER) IS
195 SELECT metric_id
196 FROM ams_metrics_all_b
197 WHERE summary_metric_id = p_met_id;
198
199 l_check_children NUMBER;
200
201 CURSOR c_check_variable_met (p_met_id NUMBER) IS
202 SELECT metric_id
203 FROM ams_metrics_all_b
204 WHERE to_number(compute_using_function) = p_met_id;
205
206 BEGIN
207 x_return_status := FND_API.G_RET_STS_SUCCESS;
208
209 OPEN c_metric;
210 FETCH c_metric INTO l_metric_rec;
211 CLOSE c_metric;
212
213 l_check_children := NULL;
214
215 --
216 -- can not update if rollup children exist and category or used with mismatch
217 --
218 IF l_metric_rec.metric_calculation_type = G_ROLLUP THEN
219 OPEN c_check_rollup_children (l_metric_rec.metric_id);
220 FETCH c_check_rollup_children INTO l_check_children;
221 CLOSE c_check_rollup_children;
222 IF l_check_children IS NOT NULL
223 AND ( p_metric_rec.metric_calculation_type <>
224 l_metric_rec.metric_calculation_type
225 OR p_metric_rec.metric_category <> l_metric_rec.metric_category )
226 THEN
227 x_return_status := FND_API.G_RET_STS_ERROR;
228 RETURN;
229 END IF;
230
231 --
232 -- Can not update if summary children exist and category or
233 -- used with mismatch
234 --
235 ELSIF l_metric_rec.metric_calculation_type = G_SUMMARY THEN
236 OPEN c_check_summary_children (l_metric_rec.metric_id);
237 FETCH c_check_summary_children INTO l_check_children;
238 CLOSE c_check_summary_children;
239 IF l_check_children IS NOT NULL THEN
240 x_return_status := FND_API.G_RET_STS_ERROR;
241 RETURN;
242 END IF;
243
244 --
245 -- check variable metric: If a variable metric depends on this.
246 --
247 ELSIF l_metric_rec.metric_calculation_type IN (G_MANUAL, G_FUNCTION)
248 AND l_metric_rec.accrual_type = G_FIXED THEN
249 OPEN c_check_variable_met (l_metric_rec.metric_id);
250 FETCH c_check_variable_met INTO l_check_children;
251 CLOSE c_check_variable_met;
252 -- BUG2411660: Test for dependent metric corrected.
253 -- IF l_check_children IS NULL THEN
254 IF l_check_children IS NOT NULL THEN
255 x_return_status := FND_API.G_RET_STS_ERROR;
256 RETURN;
257 END IF;
258 END IF;
259 END;
260
261 PROCEDURE Validate_Metric_Program (
262 p_func_name IN VARCHAR2,
263 x_func_type OUT NOCOPY VARCHAR2,
264 x_return_status OUT NOCOPY VARCHAR2
265 );
266
267
268 FUNCTION Is_Valid_Metric_Program (
269 p_exec_string IN VARCHAR2
270 ) RETURN BOOLEAN;
271
272
273
274 -- Start of comments
275 -- NAME
276 -- Create_Metric
277 --
278 -- PURPOSE
279 -- Creates a metric in AMS_METRICS_ALL_B given the
280 -- record for the metrics.
281 --
282 -- NOTES
283 --
284 -- HISTORY
285 -- 05/26/1999 choang Created.
286 -- 10/9/1999 ptendulk Modified According to new Standards
287 -- 17-Apr-2000 tdonohoe Added columns to support 11.5.2 release.
288 --
289 -- End of comments
290
291 PROCEDURE Create_Metric (
292 p_api_version IN NUMBER,
293 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
294 p_commit IN VARCHAR2 := FND_API.G_FALSE,
295 p_validation_level IN NUMBER := FND_API.G_Valid_Level_Full,
296
297 x_return_status OUT NOCOPY VARCHAR2,
298 x_msg_count OUT NOCOPY NUMBER,
299 x_msg_data OUT NOCOPY VARCHAR2,
300
301 p_metric_rec IN metric_rec_type,
302 x_metric_id OUT NOCOPY NUMBER
303 )
304 IS
305 --
306 -- Standard API information constants.
307 --
308 L_API_VERSION CONSTANT NUMBER := 1.0;
309 L_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_METRIC';
310 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
311
312
313 l_return_status VARCHAR2(1); -- Return value from procedures.
314 l_metrics_rec metric_rec_type := p_metric_rec;
315 l_metr_count NUMBER ;
316
317 l_func_type VARCHAR2(1) := NULL;
318
319 CURSOR c_metr_count(l_metric_id IN NUMBER) IS
320 SELECT COUNT(1)
321 FROM ams_metrics_all_b
322 WHERE metric_id = l_metric_id;
323
324 CURSOR c_metric_id IS
325 SELECT ams_metrics_all_b_s.NEXTVAL
326 FROM dual;
327 BEGIN
328 --
329 -- Initialize savepoint.
330 --
331 SAVEPOINT Create_Metric_pvt;
332
333 Ams_Utility_Pvt.Debug_Message(l_full_name||': start');
334
335 --
336 -- Initialize message list if p_init_msg_list is set to TRUE.
337 --
338 IF FND_API.To_Boolean (p_init_msg_list) THEN
339 FND_MSG_PUB.Initialize;
340 END IF;
341
342 --
343 -- Standard check for API version compatibility.
344 --
348 G_PKG_NAME)
345 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
346 p_api_version,
347 L_API_NAME,
349 THEN
350 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
351 END IF;
352
353
354 --
355 -- Initialize API return status to success.
356 --
357 x_return_status := FND_API.G_RET_STS_SUCCESS;
358
359 --
360 -- Begin API Body.
361 --
362 IF g_debug_flag = 'Y' THEN
363 NULL;
364 --DBMS_OUTPUT.put_line(l_full_name||': Validate');
365 END IF;
366
367 --
368 -- Validate the record before inserting.
369 --
370 Validate_Metric (
371 p_api_version => l_api_version,
372 p_init_msg_list => p_init_msg_list,
373 p_validation_level => p_validation_level,
374 x_msg_count => x_msg_count,
375 x_msg_data => x_msg_data,
376 x_return_status => l_return_status,
377 p_metric_rec => l_metrics_rec
378 );
379
380 -- If any errors happen abort API.
381 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
382 RAISE FND_API.G_EXC_ERROR;
383 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
384 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
385 END IF;
386
387 --
388 -- Debug message.
389 --
390 Ams_Utility_Pvt.debug_message(l_full_name ||': insert');
391
392 IF l_metrics_rec.metric_id IS NULL THEN
393 LOOP
394 --
395 -- Set the value for the PK.
396 OPEN c_metric_id;
397 FETCH c_metric_id INTO l_metrics_rec.metric_id;
398 CLOSE c_metric_id;
399
400 OPEN c_metr_count(l_metrics_rec.metric_id);
401 FETCH c_metr_count INTO l_metr_count ;
402 CLOSE c_metr_count ;
403
404 EXIT WHEN l_metr_count = 0 ;
405 END LOOP ;
406 END IF;
407
408 --function metric
409 IF UPPER(l_metrics_rec.metric_calculation_type) = G_FUNCTION THEN
410 IF l_metrics_rec.function_name IS NULL
411 OR l_metrics_rec.function_name = FND_API.G_MISS_CHAR THEN
412 FND_MESSAGE.Set_Name ('AMS', 'AMS_MET_FUNC_BLANK');
413 FND_MSG_PUB.ADD;
414 RAISE FND_API.G_EXC_ERROR;
415 END IF;
416 l_metrics_rec.function_name := UPPER (l_metrics_rec.function_name);
417
418 Validate_Metric_Program (p_func_name => l_metrics_rec.function_name,
419 x_func_type => l_func_type,
420 x_return_status => l_return_status);
421
422 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
423 RAISE FND_API.G_EXC_ERROR;
424 END IF;
425
426 -- elsif UPPER(l_metrics_rec.metric_calculation_type) = G_FORMULA THEN
427 -- l_metrics_rec.metric_category := null;
428 -- l_metrics_rec.metric_sub_category := null;
429 END IF;
430
431 --
432 -- Insert into the base table.
433 --
434 INSERT INTO ams_metrics_all_b (
435 metric_id,
436
437 creation_date,
438 created_by,
439 last_update_date,
440 last_updated_by,
441 last_update_login,
442
443 object_version_number,
444 application_id,
445 arc_metric_used_for_object,
446 metric_calculation_type,
447 metric_category,
448 accrual_type,
449 value_type,
450 sensitive_data_flag,
451 enabled_flag,
452 metric_sub_category,
453 function_name,
454 metric_parent_id,
455 summary_metric_id,
456 compute_using_function,
457 default_uom_code,
458 uom_type,
459 formula,
460 org_id,
461 hierarchy_id,
462 set_function_name,
463 function_type,
464 display_type,
465 target_type,
466 denorm_code
467 )
468 VALUES (
469 l_metrics_rec.metric_id,
470
471 SYSDATE,
472 FND_GLOBAL.User_ID,
473 SYSDATE,
474 FND_GLOBAL.User_ID,
475 FND_GLOBAL.Conc_Login_ID,
476 1, --Object Version Number
477 l_metrics_rec.application_id,
478 l_metrics_rec.arc_metric_used_for_object,
479 l_metrics_rec.metric_calculation_type,
480 l_metrics_rec.metric_category,
481 l_metrics_rec.accrual_type,
482 l_metrics_rec.value_type,
483 l_metrics_rec.sensitive_data_flag,
484 l_metrics_rec.enabled_flag,
485 l_metrics_rec.metric_sub_category,
486 l_metrics_rec.function_name,
487 l_metrics_rec.metric_parent_id,
488 l_metrics_rec.summary_metric_id,
489 l_metrics_rec.compute_using_function,
490 l_metrics_rec.default_uom_code,
491 l_metrics_rec.uom_type,
492 l_metrics_rec.formula,
493 TO_NUMBER (SUBSTRB (USERENV ('CLIENT_INFO'), 1, 10)), -- org_id
494 l_metrics_rec.hierarchy_id,
498 l_metrics_rec.target_type,
495 l_metrics_rec.set_function_name,
496 l_func_type,
497 l_metrics_rec.display_type,
499 l_metrics_rec.denorm_code
500 );
501
502 -- Debug message.
503 --
504 Ams_Utility_Pvt.debug_message(l_full_name ||': insert TL ');
505
506 --
507 -- Insert into the translation table.
508 --
509 INSERT INTO ams_metrics_all_tl (
510 metric_id,
511 last_update_date,
512 last_updated_by,
513 creation_date,
514 created_by,
515 last_update_login,
516 source_lang,
517 metrics_name,
518 description,
519 formula_display,
520 LANGUAGE
521 )
522 SELECT l_metrics_rec.metric_id,
523 SYSDATE,
524 FND_GLOBAL.User_ID,
525 SYSDATE,
526 FND_GLOBAL.User_ID,
527 FND_GLOBAL.Conc_Login_ID,
528 USERENV ('LANG'),
529 l_metrics_rec.metrics_name,
530 l_metrics_rec.description,
531 l_metrics_rec.formula_display,
532 l.language_code
533 FROM fnd_languages l
534 WHERE l.installed_flag IN ('I', 'B')
535 AND NOT EXISTS ( SELECT NULL
536 FROM ams_metrics_all_tl t
537 WHERE t.metric_id = l_metrics_rec.metric_id
538 AND t.LANGUAGE = l.language_code);
539
540
541 --
542 -- Set OUT value.
543 --
544 x_metric_id := l_metrics_rec.metric_id;
545
546 --
547 -- End API Body.
548 --
549
550 --
551 -- Standard check for commit request.
552 --
553 IF FND_API.To_Boolean (p_commit) THEN
554 COMMIT WORK;
555 END IF;
556
557 --
558 -- Standard API to get message count, and if 1,
559 -- set the message data OUT variable.
560 --
561 FND_MSG_PUB.Count_And_Get (
562 p_count => x_msg_count,
563 p_data => x_msg_data,
564 p_encoded => FND_API.G_FALSE
565 );
566
567 --
568 -- Add success message to message list.
569 --
570 Ams_Utility_Pvt.debug_message(l_full_name ||': end Success');
571
572
573 EXCEPTION
574 WHEN FND_API.G_EXC_ERROR THEN
575 ROLLBACK TO Create_Metric_pvt;
576 x_return_status := FND_API.G_RET_STS_ERROR;
577 FND_MSG_PUB.Count_And_Get (
578 p_encoded => FND_API.g_false,
579 p_count => x_msg_count,
580 p_data => x_msg_data
581 );
582 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
583 ROLLBACK TO Create_Metric_pvt;
584 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
585 FND_MSG_PUB.Count_And_Get (
586 p_encoded => FND_API.g_false,
587 p_count => x_msg_count,
588 p_data => x_msg_data
589 );
590 WHEN OTHERS THEN
591 ROLLBACK TO Create_Metric_pvt;
592 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
593 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
594 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
595 END IF;
596 FND_MSG_PUB.Count_And_Get (
597 p_encoded => FND_API.g_false,
598 p_count => x_msg_count,
599 p_data => x_msg_data
600 );
601 END Create_Metric;
602
603 -- Start of comments
604 -- NAME
605 -- Update_Metric
606 --
607 -- PURPOSE
608 -- Updates a metric in AMS_METRICS_ALL_B given the
609 -- record for the metrics.
610 --
611 -- NOTES
612 --
613 -- HISTORY
614 -- 05/26/1999 choang Created.
615 -- 10/9/1999 ptendulk Modified According to new Standards
616 -- 17-Apr-2000 tdonohoe Added columns to support 11.5.2 release.
617 --
618 -- End of comments
619
620 PROCEDURE Update_Metric (
621 p_api_version IN NUMBER,
622 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
623 p_commit IN VARCHAR2 := FND_API.G_FALSE,
624 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
625
626 x_return_status OUT NOCOPY VARCHAR2,
627 x_msg_count OUT NOCOPY NUMBER,
628 x_msg_data OUT NOCOPY VARCHAR2,
629
630 p_metric_rec IN metric_rec_type
631 )
632 IS
633 L_API_VERSION CONSTANT NUMBER := 1.0;
634 L_API_NAME CONSTANT VARCHAR2(30) := 'UPDATE_METRIC';
635 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
636
637
638 l_return_status VARCHAR2(1);
639 l_metrics_rec metric_rec_type;-- := p_metric_rec;
640 l_old_metrics_rec metric_rec_type;
641 l_func_type VARCHAR2(1) := NULL;
642 l_seeded_ok BOOLEAN;
643
644 BEGIN
645 --
646 -- Initialize savepoint.
647 --
648 SAVEPOINT Update_Metric_pvt;
649
650 --
651 -- Output debug message.
652 --
653 Ams_Utility_Pvt.debug_message(l_full_name||': start');
654
655 --
659 FND_MSG_PUB.Initialize;
656 -- Initialize message list if p_init_msg_list is set to TRUE.
657 --
658 IF FND_API.To_Boolean (p_init_msg_list) THEN
660 END IF;
661
662 --
663 -- Standard check for API version compatibility.
664 --
665 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
666 p_api_version,
667 L_API_NAME,
668 G_PKG_NAME)
669 THEN
670 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
671 END IF;
672
673 --
674 -- Initialize API return status to success.
675 --
676 x_return_status := FND_API.G_RET_STS_SUCCESS;
677
678 -- replace g_miss_char/num/date with current column values
679 Complete_Metric_Rec(p_metric_rec, l_metrics_rec, l_old_metrics_rec, l_seeded_ok);
680
681 --
682 -- Begin API Body
683 --
684 Inter_Metric_Validation (
685 p_metric_rec => l_metrics_rec,
686 x_return_status => l_return_status
687 );
688
689 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
690 Validate_Metric_Child (
691 p_metric_id => l_metrics_rec.metric_id,
692 x_return_status => l_return_status
693 );
694 END IF;
695
696 IF l_return_status = FND_API.g_ret_sts_error THEN
697 IF l_metrics_rec.metric_calculation_type <>
698 l_old_metrics_rec.metric_calculation_type
699 OR l_metrics_rec.metric_category <> l_old_metrics_rec.metric_category
700 OR l_metrics_rec.accrual_type <> l_old_metrics_rec.accrual_type
701 OR l_metrics_rec.value_type <> l_old_metrics_rec.value_type
702 OR l_metrics_rec.arc_metric_used_for_object <>
703 l_old_metrics_rec.arc_metric_used_for_object
704 OR l_metrics_rec.display_type <> l_old_metrics_rec.display_type
705 OR l_metrics_rec.target_type <> l_old_metrics_rec.target_type
706 THEN
707 -- Add error message to API message list.
708 --
709 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
710 FND_MESSAGE.set_name('AMS', 'AMS_METR_INVALID_UPDT_CHLD');
711 FND_MSG_PUB.ADD;
712 END IF;
713 RAISE FND_API.g_exc_error;
714 ELSE
715 l_return_status := FND_API.G_RET_STS_SUCCESS;
716 END IF;
717 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
718 RAISE FND_API.g_exc_unexpected_error;
719 END IF;
720
721 -- Ams_Utility_Pvt.debug_message(l_full_name ||': validate');
722
723 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
724 Validate_Metric_items(
725 p_metric_rec => l_metrics_rec,
726 p_validation_mode => JTF_PLSQL_API.g_update,
727 x_return_status => l_return_status
728 );
729
730 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
731 RAISE FND_API.g_exc_unexpected_error;
732 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
733 RAISE FND_API.g_exc_error;
734 END IF;
735 END IF;
736
737 -- If seeded then only update of enable flag is permitted.
738 IF (NOT l_seeded_ok) AND IsSeeded (l_metrics_rec.metric_id) THEN
739 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
740 THEN
741 FND_MESSAGE.set_name('AMS', 'AMS_METR_SEEDED_METR2');
742 FND_MSG_PUB.ADD;
743 END IF;
744
745 RAISE FND_API.G_EXC_ERROR;
746 END IF;
747
748 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
749 Validate_Metric_Record(
750 p_metric_rec => p_metric_rec,
751 p_complete_rec => l_metrics_rec,
752 x_return_status => l_return_status
753 );
754
755
756 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
757 RAISE FND_API.g_exc_unexpected_error;
758 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
759 RAISE FND_API.g_exc_error;
760 END IF;
761 END IF;
762
763 -- Ams_Utility_Pvt.debug_message(l_full_name ||': update Metrics Base Table');
764
765 --function metric
766 IF UPPER(l_metrics_rec.metric_calculation_type) = G_FUNCTION THEN
767 IF l_metrics_rec.function_name IS NULL
768 OR l_metrics_rec.function_name = FND_API.G_MISS_CHAR THEN
769 FND_MESSAGE.Set_Name ('AMS', 'AMS_MET_FUNC_BLANK');
770 FND_MSG_PUB.ADD;
771 RAISE FND_API.G_EXC_ERROR;
772 END IF;
773 l_metrics_rec.function_name := UPPER (l_metrics_rec.function_name);
774
775 -- choang - 06-jan-2005 - bug 4107480
776 -- Removed the restriction that func_type only be calculated for
777 -- non-seeded metrics; now func_type is calculated for all.
778 Validate_Metric_Program (p_func_name => l_metrics_rec.function_name,
779 x_func_type => l_func_type,
780 x_return_status => l_return_status);
781
782 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
783 RAISE FND_API.G_EXC_ERROR;
784 END IF;
785 END IF;
786
787 -- Update AMS_METRICS_ALL_B
788 UPDATE ams_metrics_all_b
789 SET object_version_number = l_metrics_rec.object_version_number + 1,
793 metric_category = l_metrics_rec.metric_category,
790 application_id = l_metrics_rec.application_id,
791 arc_metric_used_for_object =l_metrics_rec.arc_metric_used_for_object,
792 metric_calculation_type = l_metrics_rec.metric_calculation_type,
794 accrual_type = l_metrics_rec.accrual_type,
795 value_type = l_metrics_rec.value_type,
796 sensitive_data_flag = l_metrics_rec.sensitive_data_flag,
797 enabled_flag = l_metrics_rec.enabled_flag,
798 metric_sub_category = l_metrics_rec.metric_sub_category,
799 function_name = l_metrics_rec.function_name,
800 metric_parent_id = l_metrics_rec.metric_parent_id,
801 summary_metric_id = l_metrics_rec.summary_metric_id,
802 compute_using_function = l_metrics_rec.compute_using_function,
803 default_uom_code = l_metrics_rec.default_uom_code,
804 uom_type = l_metrics_rec.uom_type,
805 formula = l_metrics_rec.formula,
806 last_update_date = SYSDATE,
807 last_updated_by = FND_GLOBAL.User_ID,
808 last_update_login = FND_GLOBAL.Conc_Login_ID,
809 hierarchy_id = l_metrics_rec.hierarchy_id,
810 set_function_name = l_metrics_rec.set_function_name,
811 function_type = l_func_type,
812 display_type = l_metrics_rec.display_type,
813 target_type = l_metrics_rec.target_type,
814 denorm_code = l_metrics_rec.denorm_code
815 WHERE metric_id = l_metrics_rec.metric_id;
816
817 IF (SQL%NOTFOUND)
818 THEN
819 --
820 -- Add error message to API message list.
821 --
822 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
823 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
824 FND_MSG_PUB.ADD;
825 END IF;
826 RAISE FND_API.g_exc_error;
827 END IF;
828
829 -- Debug message.
830 Ams_Utility_Pvt.debug_message(l_full_name ||': updateMetrics TL Table');
831
832 -- Update AMS_METRICS_ALL_TL
833 UPDATE ams_metrics_all_tl
834 SET metrics_name = l_metrics_rec.metrics_name,
835 description = l_metrics_rec.description,
836 formula_display = l_metrics_rec.formula_display,
837 last_update_date = SYSDATE,
838 last_updated_by = FND_GLOBAL.User_ID,
839 last_update_login = FND_GLOBAL.Conc_Login_ID,
840 source_lang = USERENV ('LANG')
841 WHERE metric_id = l_metrics_rec.metric_id
842 AND USERENV ('LANG') IN (LANGUAGE, source_lang);
843
844 IF (SQL%NOTFOUND)
845 THEN
846 --
847 -- Add error message to API message list.
848 --
849 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
850 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
851 FND_MSG_PUB.ADD;
852 END IF;
853 RAISE FND_API.g_exc_error;
854
855 END IF;
856
857 --
858 -- End API Body
859 --
860
861 IF FND_API.to_boolean(p_commit) THEN
862 COMMIT;
863 END IF;
864
865 --
866 -- Standard API to get message count, and if 1,
867 -- set the message data OUT variable.
868 --
869 FND_MSG_PUB.Count_And_Get (
870 p_count => x_msg_count,
871 p_data => x_msg_data,
872 p_encoded => FND_API.G_FALSE
873 );
874
875 --
876 -- Debug message.
877 --
878 Ams_Utility_Pvt.debug_message(l_full_name ||': end');
879
880
881 EXCEPTION
882 WHEN FND_API.G_EXC_ERROR THEN
883 ROLLBACK TO Update_Metric_pvt;
884 x_return_status := FND_API.G_RET_STS_ERROR;
885 FND_MSG_PUB.Count_And_Get (
886 p_encoded => FND_API.g_false,
887 p_count => x_msg_count,
888 p_data => x_msg_data
889 );
890 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
891 ROLLBACK TO Update_Metric_pvt;
892 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
893 FND_MSG_PUB.Count_And_Get (
894 p_encoded => FND_API.g_false,
895 p_count => x_msg_count,
896 p_data => x_msg_data
897 );
898 WHEN OTHERS THEN
899 ROLLBACK TO Update_Metric_pvt;
900 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
901 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
902 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
903 END IF;
904 FND_MSG_PUB.Count_And_Get (
905 p_encoded => FND_API.g_false,
906 p_count => x_msg_count,
907 p_data => x_msg_data
908 );
909 END Update_Metric;
910
911 -- Start of comments
912 -- NAME
913 -- Delete_Metric
914 --
915 -- PURPOSE
916 -- Deletes a metric in AMS_METRICS_ALL_B given the
917 -- key identifier for the metric.
918 --
919 -- NOTES
920 --
921 -- HISTORY
925 -- End of comments
922 -- 05/26/1999 choang Created.
923 -- 10/9/1999 ptendulk Modified according to new standards
924 --
926
927 PROCEDURE Delete_Metric (
928 p_api_version IN NUMBER,
929 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
930 p_commit IN VARCHAR2 := FND_API.G_FALSE,
931
932 x_return_status OUT NOCOPY VARCHAR2,
933 x_msg_count OUT NOCOPY NUMBER,
934 x_msg_data OUT NOCOPY VARCHAR2,
935
936 p_metric_id IN NUMBER,
937 p_object_version_number IN NUMBER
938 )
939 IS
940 L_API_VERSION CONSTANT NUMBER := 1.0;
941 L_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_METRIC';
942 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
943
944 l_return_status VARCHAR2(1);
945
946 CURSOR c_child_met_id (l_met_id NUMBER) IS
947 SELECT metric_id
948 FROM ams_metrics_all_b
949 WHERE metric_parent_id = l_met_id;
950
951 --huili added on 08/14/2001
952 CURSOR c_check_depend_met (l_met_id NUMBER) IS
953 SELECT metric_id
954 FROM ams_metrics_all_b
955 WHERE COMPUTE_USING_FUNCTION = TO_CHAR(l_met_id);
956
957 CURSOR c_sum_met_id (l_met_id NUMBER) IS
958 SELECT metric_id
959 FROM ams_metrics_all_b
960 WHERE SUMMARY_METRIC_ID = l_met_id;
961
962 l_sum_met_id NUMBER := NULL;
963 l_dep_met_id NUMBER := NULL;
964 l_child_met_id NUMBER := NULL;
965 BEGIN
966 --
967 -- Initialize savepoint.
968 --
969 SAVEPOINT Delete_Metric_pvt;
970
971 --
972 -- Output debug message.
973 --
974 Ams_Utility_Pvt.debug_message(l_full_name||': start');
975
976 --
977 -- Initialize message list if p_init_msg_list is set to TRUE.
978 --
979 IF FND_API.To_Boolean (p_init_msg_list) THEN
980 FND_MSG_PUB.Initialize;
981 END IF;
982
983 --
984 -- Standard check for API version compatibility.
985 --
986 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
987 p_api_version,
988 L_API_NAME,
989 G_PKG_NAME)
990 THEN
991 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
992 END IF;
993
994 --
995 -- Check if record is seeded.
996 IF IsSeeded (p_metric_id) THEN
997 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
998 THEN
999 FND_MESSAGE.set_name('AMS', 'AMS_METR_SEEDED_METR3');
1000 FND_MSG_PUB.ADD;
1001 END IF;
1002
1003 RAISE FND_API.G_EXC_ERROR;
1004 END IF;
1005
1006 --
1007 -- Initialize API return status to success.
1008 --
1009 x_return_status := FND_API.G_RET_STS_SUCCESS;
1010
1011 --
1012 -- Begin API Body.
1013 --
1014
1015 Validate_Metric_Child (
1016 p_metric_id => p_metric_id,
1017 x_return_status => l_return_status
1018 );
1019
1020 -- If any errors happen abort API.
1021
1022 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1023 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1024 FND_MESSAGE.set_name('AMS', 'AMS_METR_CHILD_EXIST');
1025 FND_MSG_PUB.ADD;
1026 END IF;
1027 RAISE FND_API.G_EXC_ERROR;
1028 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1029 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1030 END IF;
1031
1032 --huili added on 07/09/2001 for bug fix #1865864
1033 l_child_met_id := NULL;
1034 OPEN c_child_met_id (p_metric_id);
1035 FETCH c_child_met_id INTO l_child_met_id;
1036 CLOSE c_child_met_id;
1037
1038 IF l_child_met_id IS NOT NULL THEN
1039 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1040 FND_MSG_PUB.Initialize;
1041 FND_MESSAGE.set_name('AMS', 'AMS_MET_ROLL_CHILD_EXISTS');
1042 FND_MSG_PUB.ADD;
1043 END IF;
1044 RAISE FND_API.G_EXC_ERROR;
1045 END IF;
1046 -- end addition
1047
1048 --huili added on 08/14/2001 for checking for dependent metric
1049 l_dep_met_id := NULL;
1050 OPEN c_check_depend_met (p_metric_id);
1051 FETCH c_check_depend_met INTO l_dep_met_id;
1052 CLOSE c_check_depend_met;
1053
1054 IF l_dep_met_id IS NOT NULL THEN
1055 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1056 FND_MSG_PUB.Initialize;
1057 FND_MESSAGE.set_name('AMS', 'AMS_MET_DEP_EXISTS');
1058 FND_MSG_PUB.ADD;
1059 END IF;
1060 RAISE FND_API.G_EXC_ERROR;
1061 END IF;
1062
1063 l_sum_met_id := NULL;
1064 OPEN c_sum_met_id (p_metric_id);
1065 FETCH c_sum_met_id INTO l_sum_met_id;
1066 CLOSE c_sum_met_id;
1067
1068 IF l_sum_met_id IS NOT NULL THEN
1069 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1070 FND_MSG_PUB.Initialize;
1074 RAISE FND_API.G_EXC_ERROR;
1071 FND_MESSAGE.set_name('AMS', 'AMS_MET_SUM_CHILD_EXISTS');
1072 FND_MSG_PUB.ADD;
1073 END IF;
1075 END IF;
1076 -- end addition
1077
1078 -- Debug message.
1079 Ams_Utility_Pvt.debug_message(l_full_name ||': delete with Validation');
1080
1081 DELETE FROM ams_metrics_all_b
1082 WHERE metric_id = p_metric_id
1083 AND object_version_number = p_object_version_number;
1084
1085
1086 IF (SQL%NOTFOUND) THEN
1087 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1088 THEN
1089 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1090 FND_MSG_PUB.ADD;
1091 END IF;
1092 RAISE FND_API.g_exc_error;
1093 END IF;
1094
1095
1096 DELETE FROM ams_metrics_all_tl
1097 WHERE metric_id = p_metric_id;
1098
1099 IF (SQL%NOTFOUND) THEN
1100 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1101 THEN
1102 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1103 FND_MSG_PUB.ADD;
1104 END IF;
1105 RAISE FND_API.g_exc_error;
1106 END IF;
1107
1108 -- 02-SEP-2003: dmvincen - formula metrics support.
1109 delete from ams_metric_formulas
1110 where metric_id = p_metric_id;
1111 -- No need to check if not found.
1112
1113 --
1114 -- End API Body.
1115 --
1116
1117 IF FND_API.To_Boolean (p_commit) THEN
1118 COMMIT WORK;
1119 END IF;
1120
1121 --
1122 -- Debug message.
1123 --
1124 Ams_Utility_Pvt.debug_message(l_full_name ||': End');
1125
1126
1127 --
1128 -- Standard API to get message count, and if 1,
1129 -- set the message data OUT variable.
1130 --
1131 FND_MSG_PUB.Count_And_Get (
1132 p_count => x_msg_count,
1133 p_data => x_msg_data,
1134 p_encoded => FND_API.G_FALSE
1135 );
1136
1137 EXCEPTION
1138 WHEN FND_API.G_EXC_ERROR THEN
1139 ROLLBACK TO Delete_Metric_pvt;
1140 x_return_status := FND_API.G_RET_STS_ERROR;
1141 FND_MSG_PUB.Count_And_Get (
1142 p_encoded => FND_API.g_false,
1143 p_count => x_msg_count,
1144 p_data => x_msg_data
1145 );
1146 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1147 ROLLBACK TO Delete_Metric_pvt;
1148 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1149 FND_MSG_PUB.Count_And_Get (
1150 p_encoded => FND_API.g_false,
1151 p_count => x_msg_count,
1152 p_data => x_msg_data
1153 );
1154 WHEN OTHERS THEN
1155 ROLLBACK TO Delete_Metric_pvt;
1156 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1157 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1158 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
1159 END IF;
1160 FND_MSG_PUB.Count_And_Get (
1161 p_encoded => FND_API.g_false,
1162 p_count => x_msg_count,
1163 p_data => x_msg_data
1164 );
1165 END Delete_Metric;
1166
1167 -- Start of comments
1168 -- NAME
1169 -- Lock_Metric
1170 --
1171 -- PURPOSE
1172 -- Perform a row lock of the metrics identified in the
1173 -- given row.
1174 --
1175 -- NOTES
1176 --
1177 -- HISTORY
1178 -- 05/26/1999 choang Created.
1179 -- 10/9/1999 ptendulk Modified according to new standards
1180 --
1181 -- End of comments
1182
1183 PROCEDURE Lock_Metric (
1184 p_api_version IN NUMBER,
1185 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1186
1187 x_return_status OUT NOCOPY VARCHAR2,
1188 x_msg_count OUT NOCOPY NUMBER,
1189 x_msg_data OUT NOCOPY VARCHAR2,
1190
1191 p_metric_id IN NUMBER,
1192 p_object_version_number IN NUMBER
1193 )
1194 IS
1195 L_API_VERSION CONSTANT NUMBER := 1.0;
1196 L_API_NAME CONSTANT VARCHAR2(30) := 'LOCK_METRIC';
1197 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1198
1199 l_metric_id NUMBER;
1200
1201 CURSOR c_metrics_info IS
1202 SELECT metric_id
1203 FROM ams_metrics_all_b
1204 WHERE metric_id = p_metric_id
1205 AND object_version_number = p_object_version_number
1206 FOR UPDATE OF metric_id NOWAIT;
1207
1208 CURSOR c_language IS
1209 SELECT metric_id
1210 FROM ams_metrics_all_tl
1211 WHERE metric_id = p_metric_id
1212 AND USERENV('LANG') IN (LANGUAGE, source_lang)
1213 FOR UPDATE OF metric_id NOWAIT;
1214 BEGIN
1215 --
1216 -- Output debug message.
1217 --
1218 Ams_Utility_Pvt.debug_message(l_full_name||': start');
1219
1220 --
1221 -- Initialize message list if p_init_msg_list is set to TRUE.
1222 --
1223 IF FND_API.To_Boolean (p_init_msg_list) THEN
1224 FND_MSG_PUB.Initialize;
1225 END IF;
1226
1227 --
1231 p_api_version,
1228 -- Standard check for API version compatibility.
1229 --
1230 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
1232 L_API_NAME,
1233 G_PKG_NAME)
1234 THEN
1235 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1236 END IF;
1237
1238 --
1239 -- Initialize API return status to success.
1240 --
1241 x_return_status := FND_API.G_RET_STS_SUCCESS;
1242
1243 --
1244 -- Begin API Body
1245 --
1246 Ams_Utility_Pvt.debug_message(l_full_name||': lock');
1247
1248
1249 OPEN c_metrics_info;
1250 FETCH c_metrics_info INTO l_metric_id;
1251 IF (c_metrics_info%NOTFOUND)
1252 THEN
1253 CLOSE c_metrics_info;
1254 -- Error, check the msg level and added an error message to the
1255 -- API message list
1256 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1257 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1258 FND_MSG_PUB.ADD;
1259 END IF;
1260 RAISE FND_API.g_exc_error;
1261 END IF;
1262 CLOSE c_metrics_info;
1263
1264 OPEN c_language;
1265 CLOSE c_language;
1266
1267 --
1268 -- Standard API to get message count, and if 1,
1269 -- set the message data OUT variable.
1270 --
1271 FND_MSG_PUB.Count_And_Get (
1272 p_count => x_msg_count,
1273 p_data => x_msg_data,
1274 p_encoded => FND_API.G_FALSE
1275 );
1276
1277 --
1278 -- Debug message.
1279 --
1280 Ams_Utility_Pvt.debug_message(l_full_name ||': end');
1281
1282
1283 EXCEPTION
1284 WHEN FND_API.G_EXC_ERROR THEN
1285 x_return_status := FND_API.G_RET_STS_ERROR;
1286 FND_MSG_PUB.Count_And_Get (
1287 p_count => x_msg_count,
1288 p_data => x_msg_data
1289 );
1290 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1291 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1292 FND_MSG_PUB.Count_And_Get (
1293 p_encoded => FND_API.g_false,
1294 p_count => x_msg_count,
1295 p_data => x_msg_data
1296 );
1297 WHEN Ams_Utility_Pvt.RESOURCE_LOCKED THEN
1298 x_return_status := FND_API.G_RET_STS_ERROR ;
1299
1300 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1301 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
1302 FND_MSG_PUB.ADD;
1303 END IF;
1304
1305 FND_MSG_PUB.Count_And_Get (
1306 p_count => x_msg_count,
1307 p_data => x_msg_data,
1308 p_encoded => FND_API.G_FALSE
1309 );
1310 WHEN OTHERS THEN
1311 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1312 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1313 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
1314 END IF;
1315 FND_MSG_PUB.Count_And_Get (
1316 p_count => x_msg_count,
1317 p_data => x_msg_data,
1318 p_encoded => FND_API.G_FALSE
1319 );
1320 END Lock_Metric;
1321
1322 -- Start of comments
1323 -- NAME
1324 -- Validate_Metric
1325 --
1326 -- PURPOSE
1327 -- Validation API for metrics.
1328 --
1329
1330 -- NOTES
1331 --
1332 -- HISTORY
1333 -- 05/26/1999 choang Created.
1334 -- 10/9/1999 ptendulk Modified according to new standards
1335 --
1336 -- End of comments
1337
1338 PROCEDURE Validate_Metric (
1339 p_api_version IN NUMBER,
1340 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1341 p_validation_level IN NUMBER := FND_API.G_Valid_Level_Full,
1342
1343 x_return_status OUT NOCOPY VARCHAR2,
1344 x_msg_count OUT NOCOPY NUMBER,
1345 x_msg_data OUT NOCOPY VARCHAR2,
1346
1347 p_metric_rec IN metric_rec_type
1348 )
1349 IS
1350 L_API_VERSION CONSTANT NUMBER := 1.0;
1351 L_API_NAME CONSTANT VARCHAR2(30) := 'VALIDATE_METRIC';
1352 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1353
1354
1355 l_return_status VARCHAR2(1);
1356
1357 BEGIN
1358 --
1359 -- Output debug message.
1360 --
1361 Ams_Utility_Pvt.debug_message(l_full_name||': start');
1362
1363 --
1364 -- Initialize message list if p_init_msg_list is set to TRUE.
1365 --
1366 IF FND_API.To_Boolean (p_init_msg_list) THEN
1367 FND_MSG_PUB.Initialize;
1368 END IF;
1369
1370 --
1371 -- Standard check for API version compatibility.
1372 --
1373 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
1374 p_api_version,
1375 L_API_NAME,
1376 G_PKG_NAME)
1377 THEN
1378 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1382 -- Initialize API return status to success.
1379 END IF;
1380
1381 --
1383 --
1384 x_return_status := FND_API.G_RET_STS_SUCCESS;
1385
1386 --
1387 -- Begin API Body.
1388 --
1389
1390 Ams_Utility_Pvt.debug_message(l_full_name||': Validate items');
1391
1392 -- Validate required items in the record.
1393 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1394
1395 Validate_Metric_items(
1396 p_metric_rec => p_metric_rec,
1397 p_validation_mode => JTF_PLSQL_API.g_create,
1398 x_return_status => l_return_status
1399 );
1400
1401
1402 -- If any errors happen abort API.
1403 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1404 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1405 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1406 RAISE FND_API.G_EXC_ERROR;
1407 END IF;
1408 END IF;
1409
1410 Ams_Utility_Pvt.debug_message(l_full_name||': check record');
1411
1412 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
1413 -- dmvincen 02/24/2004: set p_complete_rec to p_metric_rec from null.
1414 Validate_Metric_record(
1415 p_metric_rec => p_metric_rec,
1416 p_complete_rec => p_metric_rec,
1417 x_return_status => l_return_status
1418 );
1419
1420 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1421 RAISE FND_API.g_exc_unexpected_error;
1422 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1423 RAISE FND_API.g_exc_error;
1424 END IF;
1425 END IF;
1426
1427 --
1428 -- End API Body.
1429 --
1430
1431 --
1432 -- Standard API to get message count, and if 1,
1433 -- set the message data OUT variable.
1434 --
1435 FND_MSG_PUB.Count_And_Get (
1436 p_count => x_msg_count,
1437 p_data => x_msg_data,
1438 p_encoded => FND_API.G_FALSE
1439 );
1440
1441
1442
1443 Ams_Utility_Pvt.debug_message(l_full_name ||': end');
1444
1445
1446
1447 EXCEPTION
1448 WHEN FND_API.G_EXC_ERROR THEN
1449 x_return_status := FND_API.G_RET_STS_ERROR;
1450 FND_MSG_PUB.Count_And_Get (
1451 p_encoded => FND_API.g_false,
1452 p_count => x_msg_count,
1453 p_data => x_msg_data
1454 );
1455 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1457 FND_MSG_PUB.Count_And_Get (
1458 p_encoded => FND_API.g_false,
1459 p_count => x_msg_count,
1460 p_data => x_msg_data
1461 );
1462 WHEN OTHERS THEN
1463 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1464 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1465 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
1466 END IF;
1467 FND_MSG_PUB.Count_And_Get (
1468 p_encoded => FND_API.g_false,
1469 p_count => x_msg_count,
1470 p_data => x_msg_data
1471 );
1472 END Validate_Metric;
1473
1474
1475 -- Start of comments.
1476 --
1477 -- NAME
1478 -- Check_Req_Metrics_Items
1479 --
1480 -- PURPOSE
1481 -- Check for all required fields in ASM_METRICS_ALL_VL has
1482 -- a value; if value is NULL, then Add an error message to
1483 -- the API message list.
1484 --
1485 -- NOTES
1486 --
1487 -- HISTORY
1488 -- 05/26/1999 choang Created.
1489 -- 10/9/1999 ptendulk Modified According to new standards
1490 --
1491 -- End of comments.
1492
1493 PROCEDURE Check_Req_Metrics_Items (
1494 p_metric_rec IN metric_rec_type,
1495 x_return_status OUT NOCOPY VARCHAR2
1496 )
1497 IS
1498 BEGIN
1499 -- Initialize return status to success.
1500 x_return_status := FND_API.G_RET_STS_SUCCESS;
1501
1502 -- VIEW_APPLICATION_ID
1503
1504 IF p_metric_rec.application_id IS NULL
1505 THEN
1506 -- missing required fields
1507 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1508 THEN -- MMSG
1509 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_MISSING_APP_ID');
1510 FND_MSG_PUB.ADD;
1511 END IF;
1512
1513 x_return_status := FND_API.G_RET_STS_ERROR;
1514
1515 -- If any error happens abort API.
1516 RETURN;
1517 END IF;
1518
1519 -- ARC_METRIC_USED_FOR_OBJECT
1520
1521 IF p_metric_rec.arc_metric_used_for_object IS NULL
1522 THEN
1523 -- missing required fields
1524 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1525 THEN -- MMSG
1526 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_MISSING_ARC_USED_FOR');
1527 FND_MSG_PUB.ADD;
1528 END IF;
1529
1530 x_return_status := FND_API.G_RET_STS_ERROR;
1531
1532 -- If any error happens abort API.
1533 RETURN;
1534 END IF;
1535
1536 -- METRIC_CALCULATION_TYPE
1537
1541 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1538 IF p_metric_rec.metric_calculation_type IS NULL
1539 THEN
1540 -- missing required fields
1542 THEN -- MMSG
1543 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_MISSING_CALC_TYPE');
1544 FND_MSG_PUB.ADD;
1545 END IF;
1546
1547 x_return_status := FND_API.G_RET_STS_ERROR;
1548
1549 -- If any error happens abort API.
1550 RETURN;
1551 END IF;
1552
1553 -- METRIC_CATEGORY
1554
1555 IF p_metric_rec.metric_calculation_type <> G_FORMULA AND
1556 p_metric_rec.metric_category IS NULL
1557 THEN
1558 -- missing required fields
1559 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1560 THEN -- MMSG
1561 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_MISSING_CATEGORY');
1562 FND_MSG_PUB.ADD;
1563 END IF;
1564
1565 x_return_status := FND_API.G_RET_STS_ERROR;
1566
1567 -- If any error happens abort API.
1568 RETURN;
1569 END IF;
1570
1571 -- ACCRUAL_TYPE
1572
1573 IF p_metric_rec.accrual_type IS NULL
1574 THEN
1575 -- missing required fields
1576 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1577 THEN -- MMSG
1578 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_MISSING_ACCRUAL_TYPE');
1579 FND_MSG_PUB.ADD;
1580 END IF;
1581
1582 x_return_status := FND_API.G_RET_STS_ERROR;
1583
1584 -- If any error happens abort API.
1585 RETURN;
1586 END IF;
1587
1588 -- VALUE_TYPE
1589
1590 IF p_metric_rec.value_type IS NULL
1591 THEN
1592 -- missing required fields
1593 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1594 THEN -- MMSG
1595 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_MISSING_VAL_TYPE');
1596 FND_MSG_PUB.ADD;
1597 END IF;
1598
1599 x_return_status := FND_API.G_RET_STS_ERROR;
1600
1601 -- If any error happens abort API.
1602 RETURN;
1603 END IF;
1604
1605 -- SENSITIVE_DATA_FLAG
1606
1607 IF p_metric_rec.sensitive_data_flag IS NULL
1608 THEN
1609 -- missing required fields
1610 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1611 THEN -- MMSG
1612 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_MISSING_SENSITIVE');
1613 FND_MSG_PUB.ADD;
1614 END IF;
1615
1616 x_return_status := FND_API.G_RET_STS_ERROR;
1617
1618 -- If any error happens abort API.
1619 RETURN;
1620 END IF;
1621
1622 -- ENABLED_FLAG
1623
1624 IF p_metric_rec.enabled_flag IS NULL
1625 THEN
1626 -- missing required fields
1627 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1628 THEN -- MMSG
1629 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_MISSING_ENABLED_FLAG');
1630 FND_MSG_PUB.ADD;
1631 END IF;
1632
1633 x_return_status := FND_API.G_RET_STS_ERROR;
1634
1635 -- If any error happens abort API.
1636 RETURN;
1637 END IF;
1638
1639 -- Uom Type
1640 /*------------------------------------------------------------
1641 --commented by Bgeorge on 01/18/00
1642 --removed the functional requirement for the
1643 --below two columns uom_type + default_uom_code
1644
1645 IF p_metric_rec.uom_type IS NULL
1646 THEN
1647 -- missing required fields
1648 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1649 THEN -- MMSG
1650 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_MISSING_UOM_TYPE');
1651 FND_MSG_PUB.Add;
1652 END IF;
1653
1654 x_return_status := FND_API.G_RET_STS_ERROR;
1655
1656 -- If any error happens abort API.
1657 RETURN;
1658 END IF;
1659
1660 -- Default UOM Code
1661
1662 IF p_metric_rec.default_uom_code IS NULL
1663 THEN
1664 -- missing required fields
1665 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1666 THEN -- MMSG
1667 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_MISSING_DEF_UOM');
1668 FND_MSG_PUB.Add;
1669 END IF;
1670
1671 x_return_status := FND_API.G_RET_STS_ERROR;
1672
1673 -- If any error happens abort API.
1674 RETURN;
1675 END IF;
1676
1677 --end comment 01/18/00
1678 ---------------------------------------------------------------------*/
1679
1680 -- METRICS_NAME
1681
1682 IF p_metric_rec.metrics_name IS NULL
1683 THEN
1684 -- missing required fields
1685 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1686 THEN -- MMSG
1687 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_MISSING_NAME');
1688 FND_MSG_PUB.ADD;
1689 END IF;
1690
1691 x_return_status := FND_API.G_RET_STS_ERROR;
1692
1693 -- If any error happens abort API.
1694 RETURN;
1695 END IF;
1696
1697 -- DISPLAY_TYPE
1698
1699 IF p_metric_rec.DISPLAY_TYPE IS NULL
1700 THEN
1701 -- missing required fields
1702 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1703 THEN -- MMSG
1707
1704 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_MISSING_DISPLAY_TYPE');
1705 FND_MSG_PUB.ADD;
1706 END IF;
1708 x_return_status := FND_API.G_RET_STS_ERROR;
1709
1710 -- If any error happens abort API.
1711 RETURN;
1712 END IF;
1713
1714 EXCEPTION
1715 WHEN OTHERS THEN
1716 RAISE;
1717 END Check_Req_Metrics_Items;
1718
1719
1720 --
1721 -- Start of comments.
1722 --
1723 -- NAME
1724 -- Check_Metric_UK_Items
1725 --
1726 -- PURPOSE
1727 -- Perform Uniqueness check for metrics.
1728 --
1729 -- NOTES
1730 --
1731 -- HISTORY
1732 -- 10/9/1999 ptendulk Created.
1733 --
1734 -- End of comments.
1735
1736
1737 PROCEDURE Check_Metric_UK_Items(
1738 p_metric_rec IN metric_rec_type,
1739 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1740 x_return_status OUT NOCOPY VARCHAR2
1741 )
1742 IS
1743 l_where_clause VARCHAR2(2000); -- Used By Check_Uniqueness
1744
1745 CURSOR c_crt_get_dup_names(p_metrics_name VARCHAR2,
1746 p_arc_metric_used_for_object VARCHAR2) IS
1747 SELECT 1
1748 FROM ams_metrics_vl
1749 WHERE UPPER(METRICS_NAME) = UPPER(p_metrics_name)
1750 AND arc_metric_used_for_object = p_arc_metric_used_for_object;
1751
1752 CURSOR c_upd_get_dup_names(p_metrics_name VARCHAR2,
1753 p_arc_metric_used_for_object VARCHAR2, p_metric_id NUMBER) IS
1754 SELECT 1
1755 FROM ams_metrics_vl
1756 WHERE UPPER(METRICS_NAME) = UPPER(p_metrics_name)
1757 AND arc_metric_used_for_object = p_arc_metric_used_for_object
1758 AND metric_id <> p_metric_id ;
1759 l_dummy NUMBER;
1760
1761 BEGIN
1762
1763 x_return_status := FND_API.g_ret_sts_success;
1764
1765 -- For create_metric, when metric_id is passed in, we need to
1766 -- check if this metric_id is unique.
1767 IF p_validation_mode = JTF_PLSQL_API.g_create
1768 AND p_metric_rec.metric_id IS NOT NULL
1769 THEN
1770 l_where_clause := ' metric_id = '||p_metric_rec.metric_id ;
1771
1772 IF Ams_Utility_Pvt.Check_Uniqueness(
1773 p_table_name => 'ams_metrics_vl',
1774 p_where_clause => l_where_clause
1775 ) = FND_API.g_false
1776 THEN
1777 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1778 THEN
1779 FND_MESSAGE.set_name('AMS', 'AMS_METR_DUP_ID');
1780 FND_MSG_PUB.ADD;
1781 END IF;
1782 x_return_status := FND_API.g_ret_sts_error;
1783 RETURN;
1784 END IF;
1785 END IF;
1786
1787 -- Check if Metric_name is unique. Need to handle create and
1788 -- update differently.
1789
1790 -- Following code is commented and added new logic below this in order to fix bug # 1490374
1791
1792 /* -- Unique METRICS_NAME and usage level
1793 l_where_clause := ' UPPER(METRICS_NAME) = ''' ||
1794 UPPER(p_metric_rec.metrics_name) ||
1795 ''' AND arc_metric_used_for_object = ''' ||
1796 p_metric_rec.arc_metric_used_for_object || '''';
1797
1798 -- For Updates, must also check that uniqueness is not checked against the
1799 -- same record.
1800 IF p_validation_mode <> JTF_PLSQL_API.g_create THEN
1801 l_where_clause := l_where_clause || ' AND metric_id <> ' ||
1802 p_metric_rec.metric_id;
1803
1804 END IF;
1805
1806 IF AMS_Utility_PVT.Check_Uniqueness(
1807 p_table_name => 'ams_metrics_vl',
1808 p_where_clause => l_where_clause
1809 ) = FND_API.g_false
1810 THEN
1811 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1812 THEN
1813 FND_MESSAGE.set_name('AMS', 'AMS_METR_DUP_NAME');
1814 FND_MSG_PUB.add;
1815 END IF;
1816 x_return_status := FND_API.g_ret_sts_error;
1817 RETURN;
1818 END IF;
1819 */
1820 -- For Updates, must also check that uniqueness is not checked against the same record.
1821 IF p_validation_mode <> JTF_PLSQL_API.g_create THEN
1822 OPEN c_upd_get_dup_names(p_metric_rec.metrics_name,
1823 p_metric_rec.arc_metric_used_for_object,p_metric_rec.metric_id);
1824 FETCH c_upd_get_dup_names INTO l_dummy;
1825 IF c_upd_get_dup_names%FOUND THEN
1826 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1827 FND_MESSAGE.set_name('AMS', 'AMS_METR_DUP_NAME');
1828 FND_MSG_PUB.ADD;
1829 END IF;
1830 x_return_status := FND_API.g_ret_sts_error;
1831 END IF;
1832 CLOSE c_upd_get_dup_names;
1833 RETURN;
1834 ELSE
1835 OPEN c_crt_get_dup_names(p_metric_rec.metrics_name,
1836 p_metric_rec.arc_metric_used_for_object);
1837 FETCH c_crt_get_dup_names INTO l_dummy;
1838 IF c_crt_get_dup_names%FOUND THEN
1839 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1843 x_return_status := FND_API.g_ret_sts_error;
1840 FND_MESSAGE.set_name('AMS', 'AMS_METR_DUP_NAME');
1841 FND_MSG_PUB.ADD;
1842 END IF;
1844 END IF;
1845 CLOSE c_crt_get_dup_names;
1846 RETURN;
1847 END IF;
1848
1849 -- check other unique items
1850
1851 END Check_Metric_Uk_Items;
1852
1853
1854 --
1855 -- Start of comments.
1856 --
1857 -- NAME
1858 -- Check_Metric_Items
1859 --
1860 -- PURPOSE
1861 -- Perform item level validation for metrics.
1862 --
1863 -- NOTES
1864 --
1865 -- HISTORY
1866 -- 05/26/1999 choang Created.
1867 -- 10/9/1999 ptendulk Modified According to new Standards
1868 --
1869 -- End of comments.
1870
1871 PROCEDURE Check_Metric_Items (
1872 p_metric_rec IN metric_rec_type,
1873 x_return_status OUT NOCOPY VARCHAR2
1874 )
1875 IS
1876 l_item_name VARCHAR2(30); -- Used to standardize error messages.
1877 l_metrics_rec metric_rec_type := p_metric_rec;
1878 l_return_status VARCHAR2(1);
1879
1880 l_table_name VARCHAR2(30);
1881 l_pk_name VARCHAR2(30);
1882 l_pk_value VARCHAR2(30);
1883 l_pk_data_type VARCHAR2(30);
1884 l_additional_where_clause VARCHAR2(4000); -- Used by Check_FK_Exists.
1885 l_lookup_type VARCHAR2(30);
1886
1887 Cursor c_check_object_type(l_arc_metric_used_for_object VARCHAR2) is
1888 select count(1) from ams_lookups
1889 where lookup_type in ('AMS_METRIC_ROLLUP_TYPE', 'AMS_METRIC_OBJECT_TYPE', 'AMS_METRIC_ALLOCATION_TYPE')
1890 and lookup_code = l_arc_metric_used_for_object;
1891
1892 Cursor c_check_special_type(l_arc_metric_used_for_object VARCHAR2) is
1893 select count(1) from ams_lookups
1894 where lookup_type in ('AMS_METRIC_SPECIAL_TYPE')
1895 and lookup_code = l_arc_metric_used_for_object;
1896
1897 Cursor c_check_all_type(l_arc_metric_used_for_object VARCHAR2) is
1898 select count(1) from ams_lookups
1899 where lookup_type in ('AMS_METRIC_SPECIAL_TYPE', 'AMS_METRIC_ROLLUP_TYPE',
1900 'AMS_METRIC_OBJECT_TYPE', 'AMS_METRIC_ALLOCATION_TYPE')
1901 and lookup_code = l_arc_metric_used_for_object;
1902
1903
1904 /*sunkumar 20-april-2004 removed reference to check_fk_exists of utility package*/
1905
1906 CURSOR c_check_metric_id(p_metric_id number, p_metric_calculation varchar2) IS
1907 SELECT 1 from ams_metrics_vl
1908 WHERE METRIC_ID = p_metric_id
1909 AND metric_calculation_type = p_metric_calculation;
1910
1911
1912 CURSOR c_check_uom(p_uom_type varchar2) IS
1913 SELECT 1 from MTL_UOM_CLASSES
1914 WHERE UOM_CLASS = p_uom_type;
1915
1916
1917 CURSOR c_check_category(p_category_id number) IS
1918 SELECT 1 from AMS_CATEGORIES_VL
1919 WHERE CATEGORY_ID = p_category_id
1920 AND enabled_flag = 'Y'
1921 AND arc_category_created_for = 'METR';
1922
1923 /*End changes sunkumar*/
1924
1925 l_count number;
1926
1927 BEGIN
1928 -- Initialize return status to success.
1929 x_return_status := FND_API.G_RET_STS_SUCCESS;
1930
1931 --
1932
1933 --
1934 -- Begin Validate Referential
1935 --
1936
1937 -- METRIC_PARENT_ID
1938 -- Do not validate FK if NULL
1939
1940 IF (l_metrics_rec.metric_parent_id <> FND_API.G_MISS_NUM
1941 AND l_metrics_rec.metric_parent_id IS NOT NULL) THEN
1942
1943 OPEN c_check_metric_id(l_metrics_rec.metric_parent_id,G_ROLLUP);
1944 IF c_check_metric_id%NOTFOUND
1945 THEN
1946 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1947 THEN
1948 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_INVALID_PARENT_MET');
1949 FND_MSG_PUB.ADD;
1950 END IF;
1951
1952 x_return_status := FND_API.G_RET_STS_ERROR;
1953 CLOSE c_check_metric_id;
1954 RETURN;
1955 END IF;
1956 CLOSE c_check_metric_id;
1957
1958 /* end changes sunkumar */
1959
1960 END IF;
1961
1962 /*commented by sunkumar 20-april-2004 */
1963 /* l_table_name := 'AMS_METRICS_VL';
1964 l_pk_name := 'METRIC_ID';
1965 l_pk_value := l_metrics_rec.metric_parent_id;
1966 l_pk_data_type := Ams_Utility_Pvt.G_NUMBER;
1967 l_additional_where_clause := ' metric_calculation_type = '''||G_ROLLUP||'''';
1968
1969 IF Ams_Utility_Pvt.Check_FK_Exists (
1970 p_table_name => l_table_name
1971 ,p_pk_name => l_pk_name
1972 ,p_pk_value => l_pk_value
1973 ,p_pk_data_type => l_pk_data_type
1974 ,p_additional_where_clause => l_additional_where_clause
1975 ) = FND_API.G_FALSE
1976 THEN
1977 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1978 THEN
1979 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_INVALID_PARENT_MET');
1983 x_return_status := FND_API.G_RET_STS_ERROR;
1980 FND_MSG_PUB.ADD;
1981 END IF;
1982
1984 RETURN;*/
1985
1986 /*added by sunkumar for alternate to the code commented above 20-apr-2004*/
1987
1988
1989
1990
1991 -- SUMMARY_METRIC_ID
1992 IF l_metrics_rec.summary_metric_id <> FND_API.G_MISS_NUM AND
1993 l_metrics_rec.summary_metric_id IS NOT NULL THEN
1994
1995
1996 OPEN c_check_metric_id(l_metrics_rec.metric_parent_id,G_SUMMARY);
1997 IF c_check_metric_id%NOTFOUND
1998 THEN
1999 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2000 THEN
2001 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_INVALID_SUMMARY_MET');
2002 FND_MSG_PUB.ADD;
2003 END IF;
2004
2005 x_return_status := FND_API.G_RET_STS_ERROR;
2006 CLOSE c_check_metric_id;
2007 RETURN;
2008 END IF;
2009 CLOSE c_check_metric_id;
2010
2011
2012
2013 END IF;
2014
2015
2016 /* l_table_name := 'AMS_METRICS_VL';
2017 l_pk_name := 'METRIC_ID';
2018 l_pk_value := l_metrics_rec.summary_metric_id;
2019 l_pk_data_type := Ams_Utility_Pvt.G_NUMBER;
2020 l_additional_where_clause := ' metric_calculation_type = '''||G_SUMMARY||'''';
2021
2022 IF Ams_Utility_Pvt.Check_FK_Exists (
2023 p_table_name => l_table_name
2024 ,p_pk_name => l_pk_name
2025 ,p_pk_value => l_pk_value
2026 ,p_pk_data_type => l_pk_data_type
2027 ,p_additional_where_clause => l_additional_where_clause
2028 ) = FND_API.G_FALSE
2029 THEN
2030 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2031 THEN
2032 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_INVALID_SUMMARY_MET');
2033 FND_MSG_PUB.ADD;
2034 END IF;
2035
2036 x_return_status := FND_API.G_RET_STS_ERROR;
2037 RETURN;
2038 END IF; -- Check_FK_Exists*/
2039
2040
2041
2042
2043 -- UOM_CLASS
2044 IF l_metrics_rec.uom_type <> FND_API.G_MISS_CHAR THEN
2045
2046 OPEN c_check_uom(l_metrics_rec.uom_type);
2047 IF c_check_uom%NOTFOUND
2048 THEN
2049 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2050 THEN
2051 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_INVALID_UOM_TYPE');
2052 FND_MSG_PUB.ADD;
2053 END IF;
2054
2055 x_return_status := FND_API.G_RET_STS_ERROR;
2056 CLOSE c_check_uom;
2057 RETURN;
2058 END IF;
2059 CLOSE c_check_uom;
2060
2061 END IF;
2062
2063
2064 /* l_table_name := 'MTL_UOM_CLASSES';
2065 l_pk_name := 'UOM_CLASS';
2066 l_pk_value := l_metrics_rec.uom_type;
2067 l_pk_data_type := Ams_Utility_Pvt.G_VARCHAR2;
2068 l_additional_where_clause := NULL;
2069
2070 IF Ams_Utility_Pvt.Check_FK_Exists (
2071 p_table_name => l_table_name
2072 ,p_pk_name => l_pk_name
2073 ,p_pk_value => l_pk_value
2074 ,p_pk_data_type => l_pk_data_type
2075 ,p_additional_where_clause => l_additional_where_clause
2076 ) = FND_API.G_FALSE
2077 THEN
2078 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2079 THEN
2080 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_INVALID_UOM_TYPE');
2081 FND_MSG_PUB.ADD;
2082 END IF;
2083
2084 x_return_status := FND_API.G_RET_STS_ERROR;
2085 RETURN;
2086 END IF; -- Check_FK_Exists*/
2087
2088
2089
2090 -- Metric_category
2091 IF l_metrics_rec.metric_category <> FND_API.G_MISS_NUM THEN
2092
2093
2094 OPEN c_check_category(l_metrics_rec.metric_category);
2095 IF c_check_category%NOTFOUND
2096 THEN
2097 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2098 THEN
2099 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_INVALID_CATEGORY');
2100 FND_MSG_PUB.ADD;
2101 END IF;
2102
2103 x_return_status := FND_API.G_RET_STS_ERROR;
2104 CLOSE c_check_category;
2105 RETURN;
2106 END IF;
2107 CLOSE c_check_category;
2108
2109 END IF;
2110
2111
2112 /*l_table_name := 'AMS_CATEGORIES_VL';
2113 l_pk_name := 'CATEGORY_ID';
2114 l_pk_value := l_metrics_rec.metric_category;
2115 l_pk_data_type := Ams_Utility_Pvt.G_NUMBER;
2116 l_additional_where_clause := ' enabled_flag = ''Y'''||
2117 ' and arc_category_created_for = ''METR''';
2118
2119 IF Ams_Utility_Pvt.Check_FK_Exists (
2120 p_table_name => l_table_name
2124 ,p_additional_where_clause => l_additional_where_clause
2121 ,p_pk_name => l_pk_name
2122 ,p_pk_value => l_pk_value
2123 ,p_pk_data_type => l_pk_data_type
2125 ) = FND_API.G_FALSE
2126 THEN
2127 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2128 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_CATEGORY');
2129 FND_MSG_PUB.ADD;
2130 END IF;
2131
2132 x_return_status := FND_API.G_RET_STS_ERROR;
2133 RETURN;
2134 END IF;*/
2135
2136
2137
2138
2139 --
2140 -- End Validate Referential
2141 --
2142
2143 --
2144 -- Begin Validate Flags
2145 --
2146
2147 -- SENSITIVE_DATA_FLAG
2148 IF l_metrics_rec.sensitive_data_flag <> FND_API.G_MISS_CHAR THEN
2149 IF Ams_Utility_Pvt.Is_Y_Or_N(l_metrics_rec.sensitive_data_flag)
2150 = FND_API.G_FALSE
2151 THEN
2152 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2153 THEN
2154 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_INVALID_SENS_FLAG');
2155 FND_MSG_PUB.ADD;
2156 END IF;
2157
2158 x_return_status := FND_API.G_RET_STS_ERROR;
2159 RETURN;
2160 END IF; -- Is_Y_Or_N
2161 END IF;
2162
2163 -- ENABLED_FLAG
2164 IF l_metrics_rec.enabled_flag <> FND_API.G_MISS_CHAR THEN
2165 IF Ams_Utility_Pvt.Is_Y_Or_N(l_metrics_rec.enabled_flag)
2166 = FND_API.G_FALSE
2167 THEN
2168 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2169 THEN
2170 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_INVALID_ENABLED_FLAG');
2171 FND_MSG_PUB.ADD;
2172 END IF;
2173
2174 x_return_status := FND_API.G_RET_STS_ERROR;
2175 RETURN;
2176 END IF; -- Is_Y_Or_N
2177 END IF;
2178
2179 --
2180 -- End Validate Flags
2181 --
2182
2183 --
2184 -- Begin Validate LOOKUPS
2185 --
2186
2187 -- ACCRUAL_TYPE
2188 IF l_metrics_rec.accrual_type <> FND_API.G_MISS_CHAR THEN
2189 l_lookup_type := 'AMS_METRIC_ACCRUAL_TYPE';
2190 IF Ams_Utility_Pvt.Check_Lookup_Exists (
2191 p_lookup_table_name => 'AMS_LOOKUPS'
2192 ,p_lookup_type => l_lookup_type
2193 ,p_lookup_code => l_metrics_rec.accrual_type
2194 ) = FND_API.G_FALSE
2195 THEN
2196 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2197 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_ACCRUAL_TYPE');
2198 FND_MSG_PUB.ADD;
2199 END IF;
2200
2201 x_return_status := FND_API.G_RET_STS_ERROR;
2202 RETURN;
2203 END IF;
2204 END IF;
2205
2206 -- METRIC_CALCULATION_TYPE
2207 -- DMVINCEN 05/15/2001: Allow SUMMARY for 11.5.4.11 (change in 11.5.5).
2208 IF l_metrics_rec.metric_calculation_type <> FND_API.G_MISS_CHAR THEN
2209 l_lookup_type := 'AMS_METRIC_CALCULATION_TYPE';
2210 IF -- l_metrics_rec.metric_calculation_type <> G_SUMMARY AND
2211 Ams_Utility_Pvt.Check_Lookup_Exists (
2212 p_lookup_table_name => 'AMS_LOOKUPS'
2213 ,p_lookup_type => l_lookup_type
2214 ,p_lookup_code => l_metrics_rec.metric_calculation_type
2215 ) = FND_API.G_FALSE
2216 THEN
2217 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2218 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_CALC_TYPE');
2219 FND_MSG_PUB.ADD;
2220 END IF;
2221
2222 x_return_status := FND_API.G_RET_STS_ERROR;
2223 RETURN;
2224 END IF;
2225 END IF;
2226
2227
2228 -- VALUE_TYPE
2229 IF l_metrics_rec.DISPLAY_TYPE <> FND_API.G_MISS_CHAR THEN
2230 l_lookup_type := 'AMS_METRIC_DISPLAY_TYPE';
2231 IF Ams_Utility_Pvt.Check_Lookup_Exists (
2232 p_lookup_table_name => 'AMS_LOOKUPS'
2233 ,p_lookup_type => l_lookup_type
2234 ,p_lookup_code => l_metrics_rec.DISPLAY_TYPE
2235 ) = FND_API.G_FALSE
2236 THEN
2237 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2238 FND_MESSAGE.Set_Name ('AMS', 'API_INCOMPLETE_INFO');
2239 FND_MESSAGE.Set_Token ('PARAM', l_lookup_type, FALSE);
2240 FND_MSG_PUB.ADD;
2241 END IF;
2242
2243 x_return_status := FND_API.G_RET_STS_ERROR;
2244 RETURN;
2245 END IF;
2246 END IF;
2247
2248 -- VALUE_TYPE
2249 IF l_metrics_rec.value_type <> FND_API.G_MISS_CHAR THEN
2250 l_lookup_type := 'AMS_METRIC_VALUE_TYPE';
2251 IF Ams_Utility_Pvt.Check_Lookup_Exists (
2252 p_lookup_table_name => 'AMS_LOOKUPS'
2253 ,p_lookup_type => l_lookup_type
2254 ,p_lookup_code => l_metrics_rec.value_type
2255 ) = FND_API.G_FALSE
2256 THEN
2257 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2258 FND_MESSAGE.Set_Name ('AMS', 'API_INCOMPLETE_INFO');
2262
2259 FND_MESSAGE.Set_Token ('PARAM', l_lookup_type, FALSE);
2260 FND_MSG_PUB.ADD;
2261 END IF;
2263 x_return_status := FND_API.G_RET_STS_ERROR;
2264 RETURN;
2265 END IF;
2266 END IF;
2267
2268 --
2269 -- End Validate LOOKUPS
2270 --
2271
2272 -- 17-Apr-2000 tdonohoe@us modified, added FUND and FCST qualifiers.
2273 -- 11-Mar-2002 DMVINCEN Added components.
2274 -- 11-Mar-2003 BUG2845365: Removed dialgue components.
2275 -- ARC_METRIC_USED_FOR_OBJECT
2276 IF l_metrics_rec.arc_metric_used_for_object <> FND_API.G_MISS_CHAR THEN
2277 l_count := 0;
2278 IF l_metrics_rec.metric_calculation_type in (G_FUNCTION, G_MANUAL) THEN
2279 OPEN c_check_object_type(l_metrics_rec.arc_metric_used_for_object);
2280 fetch c_check_object_type into l_count;
2281 close c_check_object_type;
2282 ELSIF l_metrics_rec.metric_calculation_type in (G_ROLLUP, G_SUMMARY) THEN
2283 OPEN c_check_special_type(l_metrics_rec.arc_metric_used_for_object);
2284 fetch c_check_special_type into l_count;
2285 close c_check_special_type;
2286 ELSIF l_metrics_rec.metric_calculation_type in (G_FORMULA) THEN
2287 OPEN c_check_all_type(l_metrics_rec.arc_metric_used_for_object);
2288 fetch c_check_all_type into l_count;
2289 close c_check_all_type;
2290 END IF;
2291 if l_count = 0 then
2292 /***
2293 IF (l_metrics_rec.arc_metric_used_for_object NOT IN
2294 ('CAMP','CSCH','EVEH','EVEO','DELV','FUND','FCST', 'EONE')
2295 --'DILG', 'AMS_COMP_START','AMS_COMP_SHOW_WEB_PAGE','AMS_COMP_END')
2296 AND l_metrics_rec.metric_calculation_type in (G_FUNCTION, G_MANUAL))
2297 OR (l_metrics_rec.arc_metric_used_for_object <> 'ANY'
2298 AND l_metrics_rec.metric_calculation_type in (G_ROLLUP, G_SUMMARY))
2299
2300 -- l_lookup_type := 'AMS_SYS_ARC_QUALIFIER';
2301 -- IF AMS_Utility_PVT.Check_Lookup_Exists (
2302 -- p_lookup_table_name => 'AMS_LOOKUPS'
2303 -- ,p_lookup_type => l_lookup_type
2304 -- ,p_lookup_code => l_metrics_rec.arc_metric_used_for_object
2305 -- ) = FND_API.G_FALSE
2306 THEN
2307 ***/
2308 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2309 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_USED_BY');
2310 FND_MSG_PUB.ADD;
2311 END IF;
2312
2313 x_return_status := FND_API.G_RET_STS_ERROR;
2314 RETURN;
2315 END IF;
2316 END IF;
2317
2318
2319
2320 -- Validate FUNCTION_NAME
2321 -- Validate that the Function is created in database
2322
2323
2324 --
2325 -- End Other Business Rule Validations
2326 --
2327
2328 EXCEPTION
2329 WHEN OTHERS THEN
2330
2331 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2332 RETURN;
2333 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2334 END Check_Metric_Items;
2335
2336 /**** OBSOLETE: DMVINCEN 03/04/2003
2337 --
2338 -- Start of comments.
2339 --
2340 -- NAME
2341 -- Check_Valid_Parent
2342 --
2343 -- PURPOSE
2344 -- Check the Validity of the Metric Parent For e.g. The metric Connected to
2345 -- Campaign Schedule can be rolled up into metric Connected to Campaigns only.
2346 --
2347 -- NOTES
2348 --
2349 -- HISTORY
2350 -- 10/11/1999 ptendulk Created.
2351 -- 06/10/2001 huili Changed to apply the new hierarchy for
2352 -- the new revision 11.5.5
2353 -- 06/19/2001 dmvincen Change of hierarchy for 11.5.6
2354 --
2355 -- End of comments.
2356 PROCEDURE Check_Valid_Parent(p_metric_used_by IN VARCHAR2,
2357 p_parent_metric_used_by IN VARCHAR2,
2358 x_return_status OUT NOCOPY VARCHAR2 )
2359 IS
2360 BEGIN
2361 x_return_status := FND_API.g_ret_sts_success;
2362
2363 -- If Child is Attached to Program then Parent must be attached to Program
2364 IF p_metric_used_by = 'RCAM' AND
2365 p_parent_metric_used_by <> 'RCAM'
2366 THEN
2367 x_return_status := FND_API.G_RET_STS_ERROR;
2368 RETURN;
2369 END IF;
2370
2371 -- If Child is Attached to Campaign then Parent must be attached to Program
2372 IF p_metric_used_by = 'CAMP' AND
2373 p_parent_metric_used_by <> 'RCAM'
2374 THEN
2375 x_return_status := FND_API.G_RET_STS_ERROR;
2376 RETURN;
2377 END IF;
2378
2379 -- If Child is Attached to Campaign Schedule then Parent must be attached to Campaign
2380 IF p_metric_used_by = 'CSCH' AND
2381 p_parent_metric_used_by <> 'CAMP'
2382 THEN
2383 x_return_status := FND_API.G_RET_STS_ERROR;
2384 RETURN;
2385 END IF;
2386
2387 -- Deliverable Child can not be rolled up into any other entity
2388 IF p_metric_used_by = 'DELV'
2389 THEN
2390 x_return_status := FND_API.G_RET_STS_ERROR;
2391 RETURN;
2392 END IF;
2393
2394 --06/10/2001 huili changed logic
2395 -- If Child is Attached to Event Header then Parent must be attached to Program
2396 IF p_metric_used_by = 'EVEH' AND
2400 RETURN;
2397 p_parent_metric_used_by <> 'RCAM'
2398 THEN
2399 x_return_status := FND_API.G_RET_STS_ERROR;
2401 END IF;
2402
2403 -- If Child is Attached to Event Offer then Parent must be attached to
2404 -- Event Header or Event Offer
2405 IF p_metric_used_by = 'EVEO' AND
2406 p_parent_metric_used_by <> 'EVEH'
2407 THEN
2408 x_return_status := FND_API.G_RET_STS_ERROR;
2409 RETURN;
2410 END IF;
2411
2412 -- If Child is Attached to One Off Event then Parent must be attached to Program
2413 --06/25/2001 huili for debug
2414 --FND_MESSAGE.set_name('AMS', p_parent_metric_used_by);
2415 --FND_MSG_PUB.add;
2416 IF p_metric_used_by = 'EONE' AND
2417 p_parent_metric_used_by <> 'RCAM'
2418 THEN
2419 x_return_status := FND_API.G_RET_STS_ERROR;
2420 RETURN;
2421 END IF;
2422
2423 END Check_Valid_Parent;
2424 ***** OBSOLETE: dmvincen 03/04/2003 ****/
2425
2426 --
2427 -- Start of comments.
2428 --
2429 -- NAME
2430 -- Validate_Metric_Record
2431 --
2432 -- PURPOSE
2433 -- Perform Record Level and Other business validations for metrics.
2434 --
2435 -- NOTES
2436 --
2437 -- HISTORY
2438 -- 10/11/1999 ptendulk Created.
2439 -- 12/26/2001 dmvincen Any parent type if valid.
2440 --
2441 -- End of comments.
2442
2443 PROCEDURE Validate_Metric_record(
2444 p_metric_rec IN metric_rec_type,
2445 p_complete_rec IN metric_rec_type,
2446 x_return_status OUT NOCOPY VARCHAR2
2447 )
2448 IS
2449
2450 -- l_metrics_rec metric_rec_type := p_metric_rec ;
2451
2452 CURSOR c_summary_metric(l_metric_id number) IS
2453 SELECT met.*
2454 FROM ams_metrics_vl met
2455 WHERE met.metric_id = l_metric_id
2456 ;
2457 l_summary_metric_rec c_summary_metric%ROWTYPE;
2458
2459 CURSOR c_rollup_metric(l_metric_id number) IS
2460 SELECT met.*
2461 FROM ams_metrics_vl met
2462 WHERE met.metric_id = l_metric_id
2463 ;
2464
2465 l_rollup_metric_rec c_rollup_metric%ROWTYPE;
2466
2467 -- Following cursors are defined to check that Metric can either be Summary
2468 -- Metric or Rollup Metric but not both.
2469
2470 CURSOR c_rollup_count(l_metric_id number) IS
2471 SELECT COUNT(1)
2472 FROM ams_metrics_vl
2473 WHERE metric_parent_id = l_metric_id ;
2474
2475 CURSOR c_summary_count(l_metric_id number) IS
2476 SELECT COUNT(1)
2477 FROM ams_metrics_vl
2478 WHERE summary_metric_id = l_metric_id ;
2479
2480 CURSOR c_check_multiplier (l_metric_id number) IS
2481 select metric_category, ARC_METRIC_USED_FOR_OBJECT, metric_calculation_type
2482 from ams_metrics_all_b
2483 where metric_id = l_metric_id;
2484
2485 CURSOR c_get_category_name (l_category_id number) is
2486 SELECT category_name
2487 FROM ams_categories_vl
2488 where category_id = l_category_id;
2489
2490
2491 /*sunkumar 20 april 2004*/
2492 CURSOR c_check_subcategory(p_category_id number,p_parent_category_id number ) IS
2493 SELECT 1 from AMS_CATEGORIES_VL
2494 WHERE CATEGORY_ID = p_category_id
2495 AND enabled_flag = 'Y'
2496 AND arc_category_created_for = 'METR'
2497 AND parent_category_id = p_parent_category_id;
2498
2499
2500
2501 CURSOR c_check_uom(p_uom_code varchar2,p_uom_class varchar2 ) IS
2502 SELECT 1 from MTL_UNITS_OF_MEASURE
2503 WHERE UOM_CODE = p_uom_code
2504 AND uom_class = p_uom_class;
2505
2506 /*sunkumar 20 april 2004*/
2507
2508 l_category_id number;
2509 l_object_type varchar2(30);
2510 l_calculation_type varchar2(30);
2511 l_name ams_lookups.meaning%TYPE;
2512
2513 l_count NUMBER := 0;
2514 l_valid_chld_flag VARCHAR2(1);
2515
2516 l_table_name VARCHAR2(30);
2517 l_pk_name VARCHAR2(30);
2518 l_pk_value VARCHAR2(30);
2519 l_pk_data_type VARCHAR2(30);
2520 l_additional_where_clause VARCHAR2(4000); -- Used by Check_FK_Exists.
2521
2522 BEGIN
2523
2524 x_return_status := FND_API.g_ret_sts_success;
2525
2526 IF --l_metrics_rec.metric_sub_category <> FND_API.G_MISS_NUM AND
2527 p_complete_rec.metric_sub_category IS NOT NULL THEN
2528 -- IF l_metrics_rec.metric_category = FND_API.G_MISS_NUM THEN
2529 -- l_metrics_rec.metric_category := p_complete_rec.metric_category ;
2530 -- END IF;
2531
2532 /*sunkumar 20 april 2004*/
2533 OPEN c_check_subcategory(p_complete_rec.metric_sub_category,p_complete_rec.metric_category);
2534 IF c_check_subcategory%NOTFOUND
2535 THEN
2536 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2537 THEN
2538 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_INVALID_SUB_CATEGORY');
2539 FND_MSG_PUB.ADD;
2540 END IF;
2541
2542 x_return_status := FND_API.G_RET_STS_ERROR;
2543 CLOSE c_check_subcategory;
2544 RETURN;
2548 END IF;
2545 END IF;
2546 CLOSE c_check_subcategory;
2547
2549
2550
2551 IF p_complete_rec.default_uom_code IS NOT NULL AND
2552 p_complete_rec.uom_type IS NOT NULL THEN
2553
2554 OPEN c_check_uom(p_complete_rec.default_uom_code,p_complete_rec.uom_type);
2555 IF c_check_uom%NOTFOUND
2556 THEN
2557 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2558 THEN
2559 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_INVALID_UOM');
2560 FND_MSG_PUB.ADD;
2561 END IF;
2562
2563 x_return_status := FND_API.G_RET_STS_ERROR;
2564 CLOSE c_check_uom;
2565 RETURN;
2566 END IF;
2567 CLOSE c_check_uom;
2568
2569
2570
2571
2572 END IF; -- default_uom_code is not null
2573
2574
2575 /*l_table_name := 'AMS_CATEGORIES_VL';
2576 l_pk_name := 'CATEGORY_ID';
2577 l_pk_value := p_complete_rec.metric_sub_category;
2578 l_pk_data_type := Ams_Utility_Pvt.G_NUMBER;
2579 l_additional_where_clause := ' enabled_flag = ''Y'''||
2580 ' and arc_category_created_for = ''METR'''||
2581 ' and parent_category_id = '||
2582 p_complete_rec.metric_category;
2583
2584 IF Ams_Utility_Pvt.Check_FK_Exists (
2585 p_table_name => l_table_name
2586 ,p_pk_name => l_pk_name
2587 ,p_pk_value => l_pk_value
2588 ,p_pk_data_type => l_pk_data_type
2589 ,p_additional_where_clause => l_additional_where_clause
2590 ) = FND_API.G_FALSE
2591 THEN
2592 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2593 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_SUB_CATEGORY');
2594 FND_MSG_PUB.ADD;
2595 END IF;
2596
2597 x_return_status := FND_API.G_RET_STS_ERROR;
2598 RETURN;
2599 END IF; */
2600
2601
2602 -- DEFAULT_UOM_CODE
2603 -- IF l_metrics_rec.default_uom_code <> FND_API.G_MISS_CHAR THEN
2604
2605
2606
2607 -- IF l_metrics_rec.uom_type = FND_API.G_MISS_CHAR THEN
2608 -- l_metrics_rec.uom_type := p_complete_rec.uom_type ;
2609 -- END IF;
2610 /* l_table_name := 'MTL_UNITS_OF_MEASURE';
2611 l_pk_name := 'UOM_CODE';
2612 l_pk_value := p_complete_rec.default_uom_code;
2613 l_pk_data_type := Ams_Utility_Pvt.G_VARCHAR2;
2614 l_additional_where_clause := ' uom_class = '''||p_complete_rec.uom_type||'''';
2615
2616 IF Ams_Utility_Pvt.Check_FK_Exists (
2617 p_table_name => l_table_name
2618 ,p_pk_name => l_pk_name
2619 ,p_pk_value => l_pk_value
2620 ,p_pk_data_type => l_pk_data_type
2621 ,p_additional_where_clause => l_additional_where_clause
2622 ) = FND_API.G_FALSE
2623 THEN
2624 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2625 THEN
2626 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_INVALID_UOM');
2627 FND_MSG_PUB.ADD;
2628 END IF;
2629
2630 x_return_status := FND_API.G_RET_STS_ERROR;
2631 RETURN;
2632 END IF; -- Check_FK_Exists*/
2633
2634
2635
2636 /*****
2637 IF (l_metrics_rec.metric_calculation_type <> FND_API.G_MISS_CHAR
2638 OR l_metrics_rec.function_name <> FND_API.G_MISS_CHAR
2639 OR l_metrics_rec.compute_using_function <> FND_API.G_MISS_CHAR
2640 OR l_metrics_rec.accrual_type <> FND_API.G_MISS_CHAR)
2641 THEN
2642 IF l_metrics_rec.metric_calculation_type = FND_API.G_MISS_CHAR THEN
2643 l_metrics_rec.metric_calculation_type :=
2644 p_complete_rec.metric_calculation_type ;
2645 END IF;
2646
2647 IF l_metrics_rec.function_name = FND_API.G_MISS_CHAR THEN
2648 l_metrics_rec.function_name := p_complete_rec.function_name ;
2649 END IF;
2650
2651 IF l_metrics_rec.compute_using_function = FND_API.G_MISS_CHAR THEN
2652 l_metrics_rec.compute_using_function :=
2653 p_complete_rec.compute_using_function ;
2654 END IF;
2655
2656 IF l_metrics_rec.accrual_type = FND_API.G_MISS_CHAR THEN
2657 l_metrics_rec.accrual_type := p_complete_rec.accrual_type ;
2658 END IF;
2659 *****/
2660 -- Has to change when routine for Validate Function is done
2661 IF p_complete_rec.accrual_type = G_FIXED THEN
2662 IF p_complete_rec.compute_using_function IS NOT NULL THEN
2663 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2664 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_ACCR_VAR_FUN');
2665 FND_MSG_PUB.ADD;
2666 END IF;
2667 x_return_status := FND_API.G_RET_STS_ERROR;
2668 RETURN;
2669 ELSE -- compute_using_function is null
2670 IF p_complete_rec.metric_calculation_type = G_FUNCTION THEN
2671 IF p_complete_rec.function_name IS NULL THEN
2675 FND_MSG_PUB.ADD;
2672 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2673 THEN
2674 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_CALC_FUNC');
2676 END IF;
2677 x_return_status := FND_API.G_RET_STS_ERROR;
2678 RETURN;
2679 END IF; -- function_name is null
2680 ELSE -- metric_calculation_type <> G_FUNCTION
2681 IF p_complete_rec.function_name IS NOT NULL THEN
2682 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2683 THEN
2684 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_CALC_FUNC');
2685 FND_MSG_PUB.ADD;
2686 END IF;
2687 x_return_status := FND_API.G_RET_STS_ERROR;
2688 RETURN;
2689 END IF; -- function_name is not null
2690 END IF; -- metric_calculation_type = 'FUNCTION'
2691 END IF; -- compute_using_function is not null
2692 ELSIF p_complete_rec.accrual_type = G_VARIABLE THEN
2693 /** NOT TRUE ANY MORE
2694 IF p_complete_rec.function_name IS NOT NULL THEN
2695 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2696 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_ACCR_FUN');
2697 FND_MSG_PUB.ADD;
2698 END IF;
2699 x_return_status := FND_API.G_RET_STS_ERROR;
2700 RETURN;
2701 END IF; -- function_name is not null
2702 **/
2703 IF p_complete_rec.compute_using_function IS NULL THEN
2704 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2705 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_COMP_FUNC');
2706 FND_MSG_PUB.ADD;
2707 END IF;
2708 x_return_status := FND_API.G_RET_STS_ERROR;
2709 RETURN;
2710 ELSE
2711 OPEN c_check_multiplier(
2712 to_number(p_complete_rec.compute_using_function));
2713 l_category_id := null;
2714 l_object_type := null;
2715 l_calculation_type := null;
2716 FETCH c_check_multiplier
2717 INTO l_category_id, l_object_type, l_calculation_type;
2718 IF c_check_multiplier%NOTFOUND THEN
2719 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2720 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_MULTI_METR');
2721 FND_MSG_PUB.ADD;
2722 END IF;
2723 x_return_status := FND_API.G_RET_STS_ERROR;
2724 END IF;
2725 CLOSE c_check_multiplier;
2726 IF l_category_id IN (G_COST_ID,G_REVENUE_ID) THEN
2727 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2728 OPEN c_get_category_name(l_category_id);
2729 FETCH c_get_category_name INTO l_name;
2730 CLOSE c_get_category_name;
2731 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_MULTI_CAT');
2732 FND_MESSAGE.set_token('CATEGORY',
2733 NVL(l_name,to_char(l_category_id)), FALSE);
2734 FND_MSG_PUB.ADD;
2735 END IF;
2736 x_return_status := FND_API.G_RET_STS_ERROR;
2737 END IF;
2738 IF l_object_type <> p_complete_rec.arc_metric_used_for_object THEN
2739 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2740 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_MULTI_OBJ');
2741 l_name := AMS_UTILITY_PVT.get_lookup_meaning(
2742 'AMS_METRIC_OBJECT_TYPE',l_object_type);
2743 FND_MESSAGE.set_token('OBJECT',
2744 NVL(l_name,l_object_type), FALSE);
2745 FND_MSG_PUB.ADD;
2746 END IF;
2747 x_return_status := FND_API.G_RET_STS_ERROR;
2748 END IF;
2749 IF l_calculation_type NOT IN (G_MANUAL, G_FUNCTION) THEN
2750 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2751 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_MULTI_CALC');
2752 l_name := AMS_UTILITY_PVT.get_lookup_meaning(
2753 'AMS_METRIC_CALCULATION_TYPE',l_calculation_type);
2754 FND_MESSAGE.set_token('CALCULATION',
2755 NVL(l_name,l_calculation_type), FALSE);
2756 FND_MSG_PUB.ADD;
2757 END IF;
2758 x_return_status := FND_API.G_RET_STS_ERROR;
2759 END IF;
2760 END IF; -- compute_using_function is null
2761
2762 END IF; -- accrual_type = 'FIXED'
2763 -- END IF; -- metric_calculation_type <> G_MISS_CHAR
2764
2765 --
2766 -- Begin Other Business Rule Validations
2767 --
2768 /*****
2769 IF (l_metrics_rec.metric_parent_id <> FND_API.G_MISS_NUM OR
2770 l_metrics_rec.summary_metric_id <> FND_API.G_MISS_NUM ) AND
2771 (l_metrics_rec.metric_parent_id IS NOT NULL OR
2772 l_metrics_rec.summary_metric_id IS NOT NULL )
2773 THEN
2774 IF l_metrics_rec.metric_parent_id = FND_API.G_MISS_NUM THEN
2775 l_metrics_rec.metric_parent_id := p_complete_rec.metric_parent_id ;
2776 END IF;
2777
2781 /* **** DMVINCEN 04/27/2001 - Allow both same level and parent level.
2778 IF l_metrics_rec.summary_metric_id = FND_API.G_MISS_NUM THEN
2779 l_metrics_rec.summary_metric_id := p_complete_rec.summary_metric_id ;
2780 END IF;
2782 IF (l_metrics_rec.summary_metric_id <> FND_API.G_MISS_NUM AND
2783 l_metrics_rec.metric_parent_id <> FND_API.G_MISS_NUM ) AND
2784 (l_metrics_rec.summary_metric_id IS NOT NULL AND
2785 l_metrics_rec.metric_parent_id IS NOT NULL )
2786 THEN
2787 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2788 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_ROLL_SUMM');
2789 FND_MSG_PUB.Add;
2790 END IF;
2791 x_return_status := FND_API.G_RET_STS_ERROR;
2792 RETURN;
2793 END IF;
2794 ***** * /
2795 END IF;
2796 ****/
2797 -- DMVINCEN 04/27/2001 - Summary metrics may not rollup.
2798 IF p_complete_rec.metric_calculation_type = G_SUMMARY AND
2799 p_complete_rec.metric_parent_id IS NOT NULL --AND
2800 --l_metrics_rec.metric_parent_id <> FND_API.G_MISS_NUM
2801 THEN
2802 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2803 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_SUMM_NOT_ROLL');
2804 FND_MSG_PUB.ADD;
2805 END IF;
2806 x_return_status := FND_API.G_RET_STS_ERROR;
2807 RETURN;
2808 END IF;
2809
2810 -- Validate ROLLUP_METRIC_ID
2811
2812 IF --l_metrics_rec.metric_parent_id <> FND_API.G_MISS_NUM AND
2813 p_complete_rec.metric_parent_id IS NOT NULL THEN
2814
2815 -- Check if this Parent Metric is Summary Metric of any other Metric
2816 OPEN c_summary_count(p_complete_rec.metric_id);
2817 FETCH c_summary_count INTO l_count;
2818 CLOSE c_summary_count;
2819
2820 IF l_count > 0 THEN
2821 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2822 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_ROLL_SUMM');
2823 FND_MSG_PUB.ADD;
2824 END IF;
2825
2826 x_return_status := FND_API.G_RET_STS_ERROR;
2827 RETURN;
2828 END IF;
2829
2830 OPEN c_rollup_metric(p_complete_rec.metric_parent_id);
2831 FETCH c_rollup_metric INTO l_rollup_metric_rec;
2832 --
2833 -- Don't have to verify that the metric exists
2834 -- because we already did the referential integrity
2835 -- check earlier.
2836 --
2837 CLOSE c_rollup_metric;
2838
2839 -- Check whether the child metric is attached to a activity
2840 -- which is child of the activity attached to Parent metric
2841 -- (For e.g. If Metric M1 is Attached to Campaign C1, Metric M1a rolls up
2842 -- into M1 and is Attached to schedule Csh1 THEN csh1 must be child of
2843 -- Campaign C1
2844
2845 -- 12/16/2001 dmvincen : Any parent type is valid.
2846 -- Check_Valid_Parent(
2847 -- p_metric_used_by => l_metrics_rec.arc_metric_used_for_object,
2848 -- p_parent_metric_used_by => l_rollup_metric_rec.arc_metric_used_for_object,
2849 -- x_return_status => x_return_status );
2850
2851
2852 -- IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2853 --
2854 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2855 -- FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_PARENT');
2856 -- FND_MSG_PUB.ADD;
2857 -- END IF;
2858 -- RETURN;
2859 -- END IF;
2860
2861 -- METRIC_CATEGORY
2862 -- IF l_metrics_rec.metric_category = FND_API.G_MISS_NUM THEN
2863 -- l_metrics_rec.metric_category := p_complete_rec.metric_category ;
2864 -- END IF;
2865 -- The parent rollup metric category must be the same as the child's.
2866 IF p_complete_rec.metric_category <> l_rollup_metric_rec.metric_category
2867 THEN
2868 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2869 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_ROLL_CAT');
2870 FND_MSG_PUB.ADD;
2871 END IF;
2872
2873 x_return_status := FND_API.G_RET_STS_ERROR;
2874 RETURN;
2875 END IF;
2876
2877 -- VALUE_TYPE
2878 -- The parent rollup metric return type must be the same as the child's.
2879 -- i.e. Numeric Metric can not be rolled up into Ratio Metric
2880 -- IF l_metrics_rec.value_type = FND_API.G_MISS_CHAR THEN
2881 -- l_metrics_rec.value_type := p_complete_rec.value_type ;
2882 -- END IF;
2883
2884 IF p_complete_rec.value_type <> l_rollup_metric_rec.value_type THEN
2885 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2886 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_ROLL_VAL');
2887 FND_MSG_PUB.ADD;
2888 END IF;
2889
2890 x_return_status := FND_API.G_RET_STS_ERROR;
2891 RETURN;
2892 END IF;
2893
2894 -- UOM_TYPE
2895 -- The parent rollup metric unit of measure must be the same as the child's.
2896 -- IF l_metrics_rec.uom_type = FND_API.G_MISS_CHAR THEN
2897 -- l_metrics_rec.uom_type := p_complete_rec.uom_type ;
2898 -- END IF;
2899
2900 IF p_complete_rec.uom_type <> l_rollup_metric_rec.uom_type THEN
2904 END IF;
2901 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2902 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_ROLL_UOM');
2903 FND_MSG_PUB.ADD;
2905
2906 x_return_status := FND_API.G_RET_STS_ERROR;
2907 RETURN;
2908 END IF;
2909
2910
2911 -- Following Code is Commented by ptendulk as Metric can never be
2912 -- Rolledup into Metric of same Usage type
2913
2914 -- ARC_METRIC_USED_FOR_OBJECT
2915 -- The return value type of the parent metric must be the same as
2916 -- that of the child's.
2917 -- IF l_metrics_rec.arc_metric_used_for_object <>
2918 -- l_rollup_metric_rec.arc_metric_used_for_object THEN
2919 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2920 -- FND_MESSAGE.Set_Name ('AMS', 'AMS_ARC_QUALIFIER_NOT_SAME');
2921 -- FND_MESSAGE.Set_Token ('PARAM', l_lookup_type, FALSE);
2922 -- FND_MSG_PUB.Add;
2923 -- END IF;
2924 --
2925 -- x_return_status := FND_API.G_RET_STS_ERROR;
2926 -- END IF;
2927
2928 END IF;
2929
2930 -- Validate SUMMARY_METRIC_ID
2931 IF --l_metrics_rec.summary_metric_id <> FND_API.G_MISS_NUM AND
2932 p_complete_rec.summary_metric_id IS NOT NULL THEN
2933 -- Check if this Parent Metric is Rollup Metric of any other Metric
2934 /*****
2935 OPEN c_rollup_count(p_complete_rec.metric_id);
2936 FETCH c_rollup_count INTO l_count;
2937 CLOSE c_rollup_count;
2938
2939 IF l_count > 0 THEN
2940 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2941 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_ROLL_SUMM');
2942 FND_MSG_PUB.ADD;
2943 END IF;
2944
2945 x_return_status := FND_API.G_RET_STS_ERROR;
2946 RETURN;
2947 END IF;
2948 ****/
2949 OPEN c_summary_metric(p_complete_rec.summary_metric_id);
2950 FETCH c_summary_metric INTO l_summary_metric_rec;
2951 --
2952 -- Don't have to verify that the metric exists
2953 -- because we already did the referential integrity
2954 -- check earlier.
2955 --
2956 CLOSE c_summary_metric;
2957
2958 -- METRIC_CATEGORY
2959 -- IF l_metrics_rec.metric_category = FND_API.G_MISS_NUM THEN
2960 -- l_metrics_rec.metric_category := p_complete_rec.metric_category ;
2961 -- END IF;
2962 -- The parent rollup metric category must be the same as the child's.
2963 IF p_complete_rec.metric_category <> l_summary_metric_rec.metric_category
2964 THEN
2965 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2966 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_SUMM_CAT');
2967 FND_MSG_PUB.ADD;
2968 END IF;
2969
2970 x_return_status := FND_API.G_RET_STS_ERROR;
2971 RETURN;
2972 END IF;
2973
2974 -- UOM_TYPE
2975 -- IF l_metrics_rec.uom_type = FND_API.G_MISS_CHAR THEN
2976 -- l_metrics_rec.uom_type := p_complete_rec.uom_type ;
2977 -- END IF;
2978 -- The parent rollup metric unit of measure must be the same as the child's.
2979 IF p_complete_rec.uom_type <> l_summary_metric_rec.uom_type THEN
2980 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2981 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_SUMM_UOM');
2982 FND_MSG_PUB.ADD;
2983 END IF;
2984
2985 x_return_status := FND_API.G_RET_STS_ERROR;
2986 RETURN;
2987 END IF;
2988
2989 -- VALUE_TYPE
2990 -- IF l_metrics_rec.value_type = FND_API.G_MISS_CHAR THEN
2991 -- l_metrics_rec.value_type := p_complete_rec.value_type ;
2992 -- END IF;
2993 -- The return value type of the parent metric must be the same
2994 -- as that of the child's.
2995 IF p_complete_rec.value_type <> l_summary_metric_rec.value_type THEN
2996 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2997 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_SUMM_VAL');
2998 FND_MSG_PUB.ADD;
2999 END IF;
3000
3001 x_return_status := FND_API.G_RET_STS_ERROR;
3002 RETURN;
3003 END IF;
3004
3005 -- ARC_METRIC_USED_FOR_OBJECT
3006 --IF l_metrics_rec.arc_metric_used_for_object = FND_API.G_MISS_CHAR THEN
3007 -- l_metrics_rec.arc_metric_used_for_object :=
3008 -- p_complete_rec.arc_metric_used_for_object ;
3009 --END IF;
3010 -- The return value type of the parent metric must be the same as that
3011 -- of the child's.
3012 --06/07/2001 huili allow rollup metric to summarize to metrics of
3013 --different business types
3014 -- 03/13/2002 dmvincen: Summary and Rollup have object type of ANY.
3015 -- This code is not applicable.
3016 -- IF l_metrics_rec.arc_metric_used_for_object <>
3017 -- l_summary_metric_rec.arc_metric_used_for_object
3018 -- AND l_metrics_rec.metric_calculation_type <> G_ROLLUP THEN
3019 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3020 -- FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_SUMM_OBJ');
3021 -- FND_MSG_PUB.ADD;
3022 -- END IF;
3023
3027 END IF;
3024 -- x_return_status := FND_API.G_RET_STS_ERROR;
3025 -- RETURN;
3026 -- END IF;
3028
3029 --06/22/2001 huili recovered
3030 --06/14/2001 huili comment out
3031 --huili added on 05/10/2001
3032 IF p_complete_rec.accrual_type = 'VARIABLE'
3033 AND p_complete_rec.metric_category <> 901
3034 AND p_complete_rec.metric_category <> 902 THEN
3035 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3036 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_CATEGORY');
3037 FND_MSG_PUB.ADD;
3038 END IF;
3039 x_return_status := FND_API.G_RET_STS_ERROR;
3040 RETURN;
3041 END IF;
3042
3043 -- 04-OCT-2001 DMVINCEN New object type for summary and rollup.
3044 IF p_complete_rec.arc_metric_used_for_object <> 'ANY' AND
3045 p_complete_rec.metric_calculation_type IN (G_ROLLUP, G_SUMMARY)
3046 THEN
3047 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3048 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_USED_CALC');
3049 FND_MSG_PUB.ADD;
3050 END IF;
3051
3052 x_return_status := FND_API.G_RET_STS_ERROR;
3053 RETURN;
3054 END IF;
3055
3056 -- 04-OCT-2001 DMVINCEN New object type for summary and rollup.
3057 IF p_complete_rec.arc_metric_used_for_object = 'ANY' AND
3058 p_complete_rec.metric_calculation_type NOT IN (G_ROLLUP, G_SUMMARY, G_FORMULA)
3059 THEN
3060 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3061 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_CALC_USED');
3062 FND_MSG_PUB.ADD;
3063 END IF;
3064
3065 x_return_status := FND_API.G_RET_STS_ERROR;
3066 RETURN;
3067 END IF;
3068
3069 EXCEPTION
3070 WHEN OTHERS THEN
3071 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3072 RETURN ;
3073
3074 END Validate_Metric_record;
3075
3076 --
3077 -- Start of comments.
3078 --
3079 -- NAME
3080 -- Validate_Metric_Items
3081 --
3082 -- PURPOSE
3083 -- Perform All Item level validation for metrics.
3084 --
3085 -- NOTES
3086 --
3087 -- HISTORY
3088 -- 10/11/1999 ptendulk Created.
3089 --
3090 -- End of comments.
3091
3092 PROCEDURE Validate_Metric_items(
3093 p_metric_rec IN metric_rec_type,
3094 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
3095 x_return_status OUT NOCOPY VARCHAR2
3096 )
3097 IS
3098 BEGIN
3099 Check_Req_Metrics_Items(
3100 p_metric_rec => p_metric_rec,
3101 x_return_status => x_return_status
3102 );
3103
3104 IF x_return_status <> FND_API.g_ret_sts_success THEN
3105 RETURN;
3106 END IF;
3107
3108 Check_Metric_Uk_Items(
3109 p_metric_rec => p_metric_rec,
3110 p_validation_mode => p_validation_mode,
3111 x_return_status => x_return_status
3112 );
3113
3114 IF x_return_status <> FND_API.g_ret_sts_success THEN
3115 RETURN;
3116 END IF;
3117
3118 Check_Metric_Items(
3119 p_metric_rec => p_metric_rec,
3120 x_return_status => x_return_status
3121 );
3122
3123 IF x_return_status <> FND_API.g_ret_sts_success THEN
3124 RETURN;
3125 END IF;
3126
3127
3128
3129 END Validate_Metric_items;
3130
3131
3132 --
3133 -- Start of comments.
3134 --
3135 -- NAME
3136 -- Validate_Metric_Child
3137 --
3138 -- PURPOSE
3139 -- Perform child entity validation for metrics.
3140 --
3141 -- NOTES
3142 --
3143 -- HISTORY
3144 -- 05/26/1999 choang Created.
3145 --
3146 -- End of comments.
3147
3148 PROCEDURE Validate_Metric_Child (
3149 p_metric_id IN NUMBER,
3150 x_return_status OUT NOCOPY VARCHAR2
3151 )
3152 IS
3153 -- l_item_name VARCHAR2(30); -- Used to standardize error messages.
3154 l_metric_id NUMBER := p_metric_id;
3155 l_return_status VARCHAR2(1);
3156
3157 CURSOR c_check_metric_id(p_metric_id number) IS
3158 SELECT 1 from AMS_ACT_METRICS_ALL
3159 WHERE METRIC_ID = p_metric_id ;
3160
3161
3162 BEGIN
3163 -- Initialize return status to success.
3164 x_return_status := FND_API.G_RET_STS_SUCCESS;
3165
3166 OPEN c_check_metric_id(l_metric_id);
3167 IF c_check_metric_id%NOTFOUND
3168 THEN
3169 x_return_status := FND_API.G_RET_STS_ERROR;
3170 RETURN;
3171 END IF;
3172 CLOSE c_check_metric_id;
3173
3174
3175
3176 -- AMS_ACT_METRICS_ALL
3177 /*IF Ams_Utility_Pvt.Check_FK_Exists (
3178 p_table_name => 'AMS_ACT_METRICS_ALL',
3179 p_pk_name => 'METRIC_ID',
3180 p_pk_value => l_metric_id,
3181 p_pk_data_type => Ams_Utility_Pvt.G_NUMBER
3182 ) = FND_API.G_TRUE
3183 THEN
3184 x_return_status := FND_API.G_RET_STS_ERROR;
3185 RETURN;
3186 END IF;
3187 */
3188 -- Do not validate FK if NULL
3192 -- IF l_metrics_validate_fk_rec.metric_parent_id = FND_API.G_TRUE THEN
3189 -- IF l_metrics_rec.metric_parent_id IS NULL THEN
3190 -- l_metrics_validate_fk_rec.metric_parent_id := FND_API.G_FALSE;
3191 -- END IF;
3193 -- AMS_METRICS_VL
3194 -- Start of the changes made by PTENDULK on 08/19/1999
3195 -- The check is modified to check whether this metric id is parent metric
3196 -- id of any other id
3197
3198 -- Original Code
3199
3200 -- IF AMS_Utility_PVT.Check_FK_Exists (
3201 -- p_table_name => 'AMS_METRICS_VL',
3202 -- p_pk_name => 'METRIC_ID',
3203 -- p_pk_value => l_metrics_rec.metric_parent_id
3204 -- ) = FND_API.G_TRUE
3205 -- THEN
3206 -- Modified Code
3207
3208 --
3209 -- Check that the metric is not used by another metric as
3210 -- it's parent.
3211
3212 -- comment out the following for bug 1356700 fix
3213 -- 07/17/2000 khung
3214 -- IF AMS_Utility_PVT.Check_FK_Exists (
3215 -- p_table_name => 'AMS_METRICS_VL',
3216 -- p_pk_name => 'METRIC_PARENT_ID',
3217 -- p_pk_value => l_metric_id,
3218 -- p_pk_data_type => AMS_Utility_PVT.G_NUMBER
3219 -- ) = FND_API.G_TRUE
3220 -- End of the changes made by PTENDULK on 08/19/1999
3221 -- THEN
3222 -- IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3223 -- THEN
3224 -- FND_MESSAGE.Set_Name('AMS', 'AMS_METR_CHILD_EXIST');
3225 -- FND_MSG_PUB.Add;
3226 -- END IF;
3227 -- x_return_status := FND_API.G_RET_STS_ERROR;
3228 -- RETURN;
3229 -- END IF;
3230 -- end of change 07/17/2000 khung
3231
3232 -- IF l_metrics_rec.summary_metric_id IS NULL THEN
3233 -- l_metrics_validate_fk_rec.summary_metric_id := FND_API.G_FALSE;
3234 -- END IF;
3235 -- IF l_metrics_validate_fk_rec.summary_metric_id = FND_API.G_TRUE THEN
3236 -- AMS_METRICS_VL
3237 -- Start of the changes made by PTENDULK on 08/19/1999
3238 -- The check is modified to check whether this metric id is Summary metric
3239 -- id of any other metric id
3240 -- Original Code
3241 -- IF AMS_Utility_PVT.Check_FK_Exists (
3242 -- p_table_name => 'AMS_METRICS_VL',
3243 -- p_pk_name => 'METRIC_ID',
3244 -- p_pk_value => l_metrics_rec.summary_metric_id
3245 -- ) = FND_API.G_TRUE
3246
3247 -- Modified Code
3248 --
3249 -- Check that the metric is not used by another metric as
3250 -- it's summary rollup.
3251
3252 -- comment out the following for bug 1356700 fix
3253 -- 07/17/2000 khung
3254 -- IF AMS_Utility_PVT.Check_FK_Exists (
3255 -- p_table_name => 'AMS_METRICS_VL',
3256 -- p_pk_name => 'SUMMARY_METRIC_ID',
3257 -- p_pk_value => l_metric_id,
3258 -- p_pk_data_type => AMS_Utility_PVT.G_NUMBER
3259 -- ) = FND_API.G_TRUE
3260 -- End of the changes made by PTENDULK on 08/19/1999
3261 -- THEN
3262
3263 -- IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3264 -- THEN
3265 -- FND_MESSAGE.Set_Name('AMS', 'AMS_METR_CHILD_EXIST');
3266 -- FND_MSG_PUB.Add;
3267 -- END IF;
3268 -- x_return_status := FND_API.G_RET_STS_ERROR;
3269 -- RETURN;
3270 -- END IF;
3271 -- end of change 07/17/2000 khung
3272 EXCEPTION
3273 WHEN OTHERS THEN
3274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3275 RETURN ;
3276 END Validate_Metric_Child;
3277
3278 --
3279 -- Begin of section added by ptendulk - 10/11/1999
3280 --
3281 -- NAME
3282 -- Complete_Metric_Rec
3283 --
3284 -- PURPOSE
3285 -- Return the functional forecasted value, committed value, actual
3286 -- value, and the functional currency code for a given metric.
3287 --
3288 -- NOTES
3289 --
3290 -- HISTORY
3291 -- 07/19/1999 choang Created.
3292 -- 17-Apr-2000 tdonohoe Added columns to support 11.5.2 release.
3293 --
3294 PROCEDURE Complete_Metric_Rec(
3295 p_metric_rec IN metric_rec_type,
3296 x_complete_rec IN OUT NOCOPY metric_rec_type,
3297 x_old_metric_rec IN OUT NOCOPY metric_rec_type,
3298 x_seeded_ok IN OUT NOCOPY BOOLEAN
3299 )
3300 IS
3301 CURSOR c_metric(p_metric_id number) return metric_rec_type IS
3302 SELECT metric_id
3303 ,last_update_date
3304 ,last_updated_by
3305 ,creation_date
3306 ,created_by
3307 ,last_update_login
3308 ,object_version_number
3309 ,application_id
3310 ,arc_metric_used_for_object
3311 ,metric_calculation_type
3312 ,metric_category
3313 ,accrual_type
3314 ,value_type
3315 ,sensitive_data_flag
3316 ,enabled_flag
3317 ,metric_sub_category
3318 ,function_name
3319 ,metric_parent_id
3320 ,summary_metric_id
3321 ,compute_using_function
3322 ,default_uom_code
3323 ,uom_type
3324 ,formula
3325 ,metrics_name
3326 ,description
3327 ,formula_display
3328 ,hierarchy_id
3329 ,set_function_name
3333 FROM ams_metrics_vl
3330 ,display_type
3331 ,target_type
3332 ,denorm_code
3334 WHERE metric_id = p_metric_rec.metric_id;
3335
3336 --l_metric_rec c_metric%ROWTYPE;
3337 BEGIN
3338
3339 x_complete_rec := p_metric_rec;
3340
3341 OPEN c_metric(p_metric_rec.metric_id);
3342 FETCH c_metric INTO x_old_metric_rec;
3343 IF c_metric%NOTFOUND THEN
3344 CLOSE c_metric;
3345 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3346 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
3347 FND_MSG_PUB.ADD;
3348 END IF;
3349 RAISE FND_API.g_exc_error;
3350 END IF;
3351 CLOSE c_metric;
3352
3353 IF p_metric_rec.application_id = FND_API.G_MISS_NUM THEN
3354 x_complete_rec.application_id := x_old_metric_rec.application_id;
3355 END IF;
3356
3357 IF p_metric_rec.arc_metric_used_for_object = FND_API.G_MISS_CHAR THEN
3358 x_complete_rec.arc_metric_used_for_object := x_old_metric_rec.arc_metric_used_for_object;
3359 END IF;
3360
3361 IF p_metric_rec.metric_calculation_type = FND_API.G_MISS_CHAR THEN
3362 x_complete_rec.metric_calculation_type := x_old_metric_rec.metric_calculation_type ;
3363 END IF;
3364
3365 IF p_metric_rec.metric_category = FND_API.G_MISS_NUM THEN
3366 x_complete_rec.metric_category := x_old_metric_rec.metric_category ;
3367 END IF;
3368
3369 IF p_metric_rec.accrual_type = FND_API.G_MISS_CHAR THEN
3370 x_complete_rec.accrual_type := x_old_metric_rec.accrual_type ;
3371 END IF;
3372
3373 IF p_metric_rec.value_type = FND_API.G_MISS_CHAR THEN
3374 x_complete_rec.value_type := x_old_metric_rec.value_type ;
3375 END IF;
3376
3377 IF p_metric_rec.sensitive_data_flag = FND_API.G_MISS_CHAR THEN
3378 x_complete_rec.sensitive_data_flag := x_old_metric_rec.sensitive_data_flag;
3379 END IF;
3380
3381 IF p_metric_rec.enabled_flag = FND_API.G_MISS_CHAR THEN
3382 x_complete_rec.enabled_flag := x_old_metric_rec.enabled_flag ;
3383 END IF;
3384
3385 IF p_metric_rec.metric_sub_category = FND_API.G_MISS_NUM THEN
3386 x_complete_rec.metric_sub_category := x_old_metric_rec.metric_sub_category ;
3387 END IF;
3388
3389 IF p_metric_rec.function_name = FND_API.G_MISS_CHAR THEN
3390 x_complete_rec.function_name := x_old_metric_rec.function_name ;
3391 END IF;
3392
3393 IF p_metric_rec.metric_parent_id = FND_API.G_MISS_NUM THEN
3394 x_complete_rec.metric_parent_id := x_old_metric_rec.metric_parent_id ;
3395 END IF;
3396
3397 IF p_metric_rec.enabled_flag = FND_API.G_MISS_CHAR THEN
3398 x_complete_rec.enabled_flag := x_old_metric_rec.enabled_flag ;
3399 END IF;
3400
3401 IF p_metric_rec.summary_metric_id = FND_API.G_MISS_NUM THEN
3402 x_complete_rec.summary_metric_id := x_old_metric_rec.summary_metric_id ;
3403 END IF;
3404
3405 IF p_metric_rec.compute_using_function = FND_API.G_MISS_CHAR THEN
3406 x_complete_rec.compute_using_function := x_old_metric_rec.compute_using_function ;
3407 END IF;
3408
3409 IF p_metric_rec.default_uom_code = FND_API.G_MISS_CHAR THEN
3410 x_complete_rec.default_uom_code := x_old_metric_rec.default_uom_code ;
3411 END IF;
3412
3413 IF p_metric_rec.uom_type = FND_API.G_MISS_CHAR THEN
3414 x_complete_rec.uom_type := x_old_metric_rec.uom_type ;
3415 END IF;
3416
3417 IF p_metric_rec.formula = FND_API.G_MISS_CHAR THEN
3418 x_complete_rec.formula := x_old_metric_rec.formula ;
3419 END IF;
3420
3421 IF p_metric_rec.metrics_name = FND_API.G_MISS_CHAR THEN
3422 x_complete_rec.metrics_name := x_old_metric_rec.metrics_name ;
3423 END IF;
3424
3425 IF p_metric_rec.description = FND_API.G_MISS_CHAR THEN
3426 x_complete_rec.description := x_old_metric_rec.description;
3427 END IF;
3428
3429 IF p_metric_rec.formula_display = FND_API.G_MISS_CHAR THEN
3430 x_complete_rec.formula_display := x_old_metric_rec.formula_display;
3431 END IF;
3432
3433 -- 17-Apr-2000 tdonohoe@us added.
3434 IF p_metric_rec.hierarchy_id = FND_API.G_MISS_NUM THEN
3435 x_complete_rec.hierarchy_id := x_old_metric_rec.hierarchy_id;
3436 END IF;
3437
3438 -- 17-Apr-2000 tdonohoe@us added.
3439 IF p_metric_rec.set_function_name = FND_API.G_MISS_CHAR THEN
3440 x_complete_rec.set_function_name := x_old_metric_rec.set_function_name;
3441 END IF;
3442
3443 if p_metric_rec.display_type = FND_API.G_MISS_CHAR then
3444 x_complete_rec.display_type := x_old_metric_rec.display_type;
3445 end if;
3446
3447 if p_metric_rec.target_type = FND_API.G_MISS_CHAR then
3448 x_complete_rec.target_type := x_old_metric_rec.target_type;
3449 end if;
3450
3451 if p_metric_rec.denorm_code = FND_API.G_MISS_CHAR then
3452 x_complete_rec.denorm_code := x_old_metric_rec.denorm_code;
3453 end if;
3454
3455 x_seeded_ok := TRUE;
3456
3457 IF x_old_metric_rec.metrics_name <> x_complete_rec.metrics_name OR
3458 x_old_metric_rec.application_id <> x_complete_rec.application_id OR
3459 x_old_metric_rec.arc_metric_used_for_object <> x_complete_rec.arc_metric_used_for_object OR
3460 x_old_metric_rec.metric_calculation_type <> x_complete_rec.metric_calculation_type OR
3461 x_old_metric_rec.metric_category <> x_complete_rec.metric_category OR
3462 x_old_metric_rec.accrual_type <> x_complete_rec.accrual_type OR
3463 x_old_metric_rec.value_type <> x_complete_rec.value_type OR
3464 x_old_metric_rec.sensitive_data_flag <> x_complete_rec.sensitive_data_flag OR
3465 NVL(x_old_metric_rec.metric_sub_category,FND_API.G_MISS_NUM) <>
3466 NVL(x_complete_rec.metric_sub_category,FND_API.G_MISS_NUM) OR
3467 NVL(x_old_metric_rec.function_name,FND_API.G_MISS_CHAR) <>
3468 NVL(x_complete_rec.function_name,FND_API.G_MISS_CHAR) OR
3469 NVL(x_old_metric_rec.metric_parent_id,FND_API.G_MISS_NUM) <>
3470 NVL(x_complete_rec.metric_parent_id,FND_API.G_MISS_NUM) OR
3471 NVL(x_old_metric_rec.summary_metric_id,FND_API.G_MISS_NUM) <>
3472 NVL(x_complete_rec.summary_metric_id,FND_API.G_MISS_NUM) OR
3473 NVL(x_old_metric_rec.compute_using_function,FND_API.G_MISS_CHAR) <>
3474 NVL(x_complete_rec.compute_using_function,FND_API.G_MISS_CHAR) OR
3475 NVL(x_old_metric_rec.default_uom_code,FND_API.G_MISS_CHAR) <>
3476 NVL(x_complete_rec.default_uom_code,FND_API.G_MISS_CHAR) OR
3477 NVL(x_old_metric_rec.uom_type,FND_API.G_MISS_CHAR) <>
3478 NVL(x_complete_rec.uom_type,FND_API.G_MISS_CHAR) OR
3479 NVL(x_old_metric_rec.formula,FND_API.G_MISS_CHAR) <>
3480 NVL(x_complete_rec.formula,FND_API.G_MISS_CHAR) OR
3481 NVL(x_old_metric_rec.hierarchy_id,FND_API.G_MISS_NUM) <>
3482 NVL(x_complete_rec.hierarchy_id,FND_API.G_MISS_NUM) OR
3483 NVL(x_old_metric_rec.set_function_name,FND_API.G_MISS_CHAR) <>
3484 NVL(x_complete_rec.set_function_name,FND_API.G_MISS_CHAR) THEN
3485 x_seeded_ok := FALSE;
3486 END IF;
3487 END Complete_Metric_Rec ;
3488
3489 --
3490 -- End of section added by choang.
3491 --
3492
3493 --
3494 -- NAME
3495 -- IsSeeded
3496 --
3497 -- PURPOSE
3498 -- Returns whether the given ID is that of a seeded record.
3499 --
3500 -- NOTES
3501 -- As of creation of the function, a seeded record has an ID
3502 -- less than 10,000.
3503 --
3504 -- HISTORY
3505 -- 07/19/1999 choang Created.
3506 -- 01/16/00 bgeorge Modified to check for ID <10000
3507 --
3508 FUNCTION IsSeeded (
3509 p_id IN NUMBER
3510 )
3511 RETURN BOOLEAN
3512 IS
3513 BEGIN
3514 IF p_id < 10000 THEN
3515 RETURN TRUE;
3516 END IF;
3517
3518 RETURN FALSE;
3519 END IsSeeded;
3520
3521
3522 -- NAME
3523 -- Validate_Metric_Program
3524 --
3525 -- PURPOSE
3526 -- Validate the metric program and determine whether it is
3527 -- a function or procedure.
3528 --
3529 -- Logic of validation:
3530 --
3531 -- Validate the custom code for a function:
3532 --
3533 -- If Yes: Set the function Type to 'Y' and return success.
3534 --
3535 -- If No: Validate the custom code for a procedure, check is made
3536 -- for the validation to go thru for both UI level and refresh
3537 -- and system level refresh.
3538 --
3539 -- If Yes: Set the function type to 'N' and return success.
3540 --
3541 -- If NO: Set the function type to null and return error with
3542 -- the error message.
3543 --
3544 -- NOTES
3545 --
3546 -- HISTORY
3547 -- 06/18/2004 sunkumar Created.
3548 -- 08/09/2004 sunkumar Validation ammendment.
3549 -- 11/10/2004 dmvincen BUG 3792709: Fixed validation logic.
3550
3551 PROCEDURE Validate_Metric_Program (
3552 p_func_name IN VARCHAR2,
3553 x_func_type OUT NOCOPY VARCHAR2,
3554 x_return_status OUT NOCOPY VARCHAR2
3555 )
3556 IS
3557
3558 l_parse_string varchar2(4000);
3559 l_return_status VARCHAR2(1); -- Return value from procedures.
3560 l_func_type VARCHAR2(1) := NULL;
3561
3562 BEGIN
3563
3564 x_func_type := NULL;
3565 x_return_status := FND_API.G_RET_STS_SUCCESS;
3566
3567 l_parse_string := 'DECLARE l_num_value NUMBER; Begin l_num_value := '||
3568 p_func_name || '(10000); end;';
3569
3570 IF Is_Valid_Metric_Program (p_exec_string => l_parse_string) THEN
3571
3572 x_func_type := 'Y';
3573
3574 ELSE
3575
3576 l_parse_string := 'begin '|| p_func_name ||'; end;';
3577
3578 IF Is_Valid_Metric_Program (p_exec_string => l_parse_string) THEN
3579
3580 l_parse_string := 'begin '|| p_func_name ||'(''CSCH'', 10000); end;';
3581
3582 IF Is_Valid_Metric_Program (p_exec_string => l_parse_string) THEN
3583
3584 x_func_type := 'N';
3585
3586 END IF;
3587
3588 END IF;
3589
3590 END IF;
3591
3592 IF x_func_type is null THEN
3593
3594 FND_MESSAGE.Set_Name ('AMS', 'AMS_MET_FUNC_INVALID_DETAILS');
3595 FND_MSG_PUB.ADD;
3596 x_return_status := FND_API.G_RET_STS_ERROR;
3597
3598 END IF;
3599
3600 END Validate_Metric_Program;
3601
3602
3603
3604
3605 --
3606 -- Start of comments.
3607 --
3608 -- NAME
3609 -- Is_Valid_Metric_Program
3610 --
3611 -- PURPOSE
3612 -- Checks wether the custom procedure/function is a valid one
3613 --
3614 -- NOTES
3615 --
3616 -- HISTORY
3617 -- 06/18/2004 sunkumar Created.
3618 -- 08/09/2004 sunkumar indentation
3619 --
3620 -- End of comments.
3621
3622 FUNCTION Is_Valid_Metric_Program (
3623 p_exec_string IN VARCHAR2
3624 ) RETURN BOOLEAN
3625 IS
3626 cursor_num integer;
3627 BEGIN
3628
3629 cursor_num := dBMS_sql.open_cursor;
3630 dbms_sql.parse(cursor_num, p_exec_string, dbms_sql.native);
3631 dbms_sql.close_cursor(cursor_num);
3632
3633 RETURN TRUE;
3634 EXCEPTION
3635 WHEN OTHERS THEN
3636 dbms_sql.close_cursor(cursor_num);
3637 RETURN FALSE;
3638 END Is_Valid_Metric_Program;
3639
3640 END Ams_Metric_Pvt;