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