[Home] [Help]
PACKAGE BODY: APPS.AMS_METRIC_FORMULA_PVT
Source
1 PACKAGE BODY AMS_METRIC_FORMULA_PVT AS
2 /* $Header: amsvmtfb.pls 115.10 2004/06/30 08:00:09 sunkumar noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_metric_formula_PVT
7 -- Purpose
8 --
9 -- History
10 -- ??-???-2003 dmvincen Created.
11 -- 12-Nov-2003 choang Modified transform_formula
12 -- 01-Dec-2003 dmvincen Return error for invalid formula.
13 -- 22-Dec-2003 dmvincen BUG3325199: check sequence in validate.
14 -- 09-Jan-2004 dmvincen BUG3354319: Default sequence value.
15 -- 30-Jun-2004 sunkumar bug#3687362: FORMULA METRIC VALIDATION ERROR MESSAGE IS UNCLEAR
16 --
17 -- NOTE
18 --
19 -- End of Comments
20 -- ===============================================================
21
22
23 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_metric_formula_PVT';
24 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvmtdb.pls';
25
26 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
27 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
28 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
29
30 G_SEQUENCE_INCREMENT CONSTANT NUMBER := 10;
31
32 TYPE stack_rec_type IS RECORD
33 (
34 source_name VARCHAR2(150),
35 token VARCHAR2(30),
36 sequence NUMBER,
37 object_version_number NUMBER,
38 metric_formula_id NUMBER,
39 notation_type VARCHAR2(30),
40 source_type VARCHAR2(30) := 'EMPTY',
41 use_sub_id_flag VARCHAR2(1),
42 source_value NUMBER,
43 source_id NUMBER,
44 source_sub_id NUMBER
45 );
46
47 TYPE stack_tbl_type IS TABLE OF stack_rec_type INDEX BY BINARY_INTEGER;
48 TYPE varchar30_tbl_type is table of varchar2(30) index by binary_integer;
49 type number_tbl_type is table of number index by binary_integer;
50 g_stack_tbl stack_tbl_type;
51 g_infix_tbl stack_tbl_type;
52 g_postfix_tbl stack_tbl_type;
53 g_infix_index number := 0;
54 g_infix_formula varchar2(4000);
55 g_valid_formula boolean;
56 g_error_sequence number := null;
57 g_current_sequence number := null;
58
59 g_source_type_tbl varchar30_tbl_type;
60 g_source_id_tbl number_tbl_type;
61 g_source_sub_id_tbl number_tbl_type;
62 g_source_value_tbl number_tbl_type;
63 g_token_tbl varchar30_tbl_type;
64 g_use_sub_id_flag_tbl varchar30_tbl_type;
65 g_index_tbl number_tbl_type;
66
67 PROCEDURE Complete_metric_formula_Rec (
68 p_ref_metric_formula_rec IN met_formula_rec_type,
69 x_tar_metric_formula_rec IN OUT NOCOPY met_formula_rec_type);
70
71 -- Hint: Primary key needs to be returned.
72 PROCEDURE Create_Metric_Formula(
73 p_api_version_number IN NUMBER,
74 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
75 p_commit IN VARCHAR2 := FND_API.G_FALSE,
76 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
77
78 x_return_status OUT nocopy VARCHAR2,
79 x_msg_count OUT nocopy NUMBER,
80 x_msg_data OUT nocopy VARCHAR2,
81
82 p_met_formula_rec IN met_formula_rec_type := g_miss_met_formula_rec,
83 x_metric_formula_id OUT nocopy NUMBER
84 )
85
86 IS
87 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_metric_formula';
88 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
89 l_return_status_full VARCHAR2(1);
90 l_object_version_number NUMBER := 1;
91 l_METRIC_FORMULA_ID NUMBER;
92 l_dummy NUMBER;
93 l_rowid ROWID;
94
95 CURSOR c_id IS
96 SELECT AMS_METRIC_FORMULAS_s.NEXTVAL
97 FROM dual;
98
99 CURSOR c_id_exists (l_id IN NUMBER) IS
100 SELECT 1
101 FROM AMS_METRIC_FORMULAS
102 WHERE METRIC_FORMULA_ID = l_id;
103
104 CURSOR c_get_max_sequence (l_metric_id IN NUMBER) IS
105 select nvl(max(sequence),0)
106 from ams_metric_formulas
107 where metric_id = l_metric_id;
108
109 l_metric_formula_rec met_formula_rec_type := p_met_formula_rec;
110 l_max_sequence number := null;
111
112 BEGIN
113 -- Standard Start of API savepoint
114 SAVEPOINT CREATE_metric_formula_SP;
115
116 -- Standard call to check for call compatibility.
117 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
118 p_api_version_number,
119 l_api_name,
120 G_PKG_NAME)
121 THEN
122 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
123 END IF;
124
125 -- Initialize message list if p_init_msg_list is set to TRUE.
126 IF FND_API.to_Boolean( p_init_msg_list )
127 THEN
128 FND_MSG_PUB.initialize;
129 END IF;
130
131 -- Debug Message
132 IF (AMS_DEBUG_HIGH_ON) THEN
133
134 Ams_Utility_Pvt.debug_message('Private API: ' || l_api_name || ': start');
135 END IF;
136
137 --sunkumar 30/01/2003
138 --check if the template is a seeded one
139 /* IF p_metric_formula_rec.metric_tpl_header_id < 10000 THEN
140 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
141 THEN
142 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_METR_TPL_SEEDED');
143 END IF;
144
145 RAISE FND_API.G_EXC_ERROR;
146 END IF; */
147
148
149 -- Initialize API return status to SUCCESS
150 x_return_status := FND_API.G_RET_STS_SUCCESS;
151
152 -- Local variable initialization
153
154 IF l_metric_formula_rec.METRIC_FORMULA_ID IS NULL OR
155 l_metric_formula_rec.METRIC_FORMULA_ID = FND_API.g_miss_num THEN
156 LOOP
157 l_dummy := NULL;
158 OPEN c_id;
159 FETCH c_id INTO l_METRIC_FORMULA_ID;
160 CLOSE c_id;
161
162 OPEN c_id_exists(l_METRIC_FORMULA_ID);
163 FETCH c_id_exists INTO l_dummy;
164 CLOSE c_id_exists;
165 EXIT WHEN l_dummy IS NULL;
166 END LOOP;
167 l_metric_formula_rec.METRIC_FORMULA_ID := l_METRIC_FORMULA_ID;
168 -- Debug message
169 IF (AMS_DEBUG_HIGH_ON) THEN
170
171 Ams_Utility_Pvt.debug_message('Private API: New formula id='||l_metric_formula_id);
172 END IF;
173 END IF;
174
175 -- =========================================================================
176 -- Validate Environment
177 -- =========================================================================
178
179 IF FND_GLOBAL.User_Id IS NULL
180 THEN
181 Ams_Utility_Pvt.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
182 RAISE FND_API.G_EXC_ERROR;
183 END IF;
184
185 if l_metric_formula_rec.sequence is null then
186 open c_get_max_sequence(l_metric_formula_rec.metric_id);
187 fetch c_get_max_sequence into l_max_sequence;
188 close c_get_max_sequence;
189 l_metric_formula_rec.sequence :=
190 nvl(l_max_sequence,0) + G_SEQUENCE_INCREMENT;
191 end if;
192
193 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
194 THEN
195 -- Debug message
196 IF (AMS_DEBUG_HIGH_ON) THEN
197
198 Ams_Utility_Pvt.debug_message('Private API: Validate_metric_formula');
199 END IF;
200
201 -- Invoke validation procedures
202 Validate_metric_formula(
203 p_api_version_number => 1.0,
204 p_init_msg_list => FND_API.G_FALSE,
205 p_validation_level => p_validation_level,
206 p_validation_mode => JTF_PLSQL_API.g_create,
207 p_metric_formula_rec => l_metric_formula_rec,
208 x_return_status => x_return_status,
209 x_msg_count => x_msg_count,
210 x_msg_data => x_msg_data);
211 END IF;
212
213 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
214 RAISE FND_API.G_EXC_ERROR;
215 END IF;
216
217
218 -- Debug Message
219 IF (AMS_DEBUG_HIGH_ON) THEN
220 Ams_Utility_Pvt.debug_message( 'Private API: Calling Ams_Metric_Formulas_Pkg.Insert_Row');
221 END IF;
222
223 -- Invoke table handler(AMS_MET_TPL_DETAILS_PKG.Insert_Row)
224 Ams_Metric_Formulas_Pkg.Insert_Row(
225 X_ROWID => l_rowid,
226 X_METRIC_FORMULA_ID => l_metric_formula_id,
227 X_METRIC_ID => l_metric_formula_rec.metric_id,
228 X_SOURCE_TYPE => l_metric_formula_rec.source_type,
229 X_SOURCE_ID => l_metric_formula_rec.source_id,
230 X_SOURCE_SUB_ID => l_metric_formula_rec.source_sub_id,
231 X_USE_SUB_ID_FLAG => l_metric_formula_rec.use_sub_id_flag,
232 X_SOURCE_VALUE => l_metric_formula_rec.source_value,
233 X_TOKEN => l_metric_formula_rec.token,
234 X_SEQUENCE => l_metric_formula_rec.sequence,
235 X_NOTATION_TYPE => l_metric_formula_rec.notation_type,
236 X_OBJECT_VERSION_NUMBER => 1,
237 X_CREATION_DATE => SYSDATE,
238 X_CREATED_BY => FND_GLOBAL.USER_ID,
239 X_LAST_UPDATE_DATE => SYSDATE,
240 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
241 X_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
242 );
243
244 -- Debug Message
245 IF (AMS_DEBUG_HIGH_ON) THEN
246 Ams_Utility_Pvt.debug_message( 'Private API: return_status='||x_return_status||', x_metric_formula_id='||x_metric_formula_id);
247 END IF;
248
249 x_METRIC_FORMULA_ID := l_METRIC_FORMULA_ID;
250 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
251 RAISE FND_API.G_EXC_ERROR;
252 END IF;
253 --
254 -- End of API body
255 --
256
257 -- Standard check for p_commit
258 IF FND_API.to_Boolean( p_commit )
259 THEN
260 COMMIT WORK;
261 END IF;
262
263
264 -- Debug Message
265 IF (AMS_DEBUG_HIGH_ON) THEN
266
267 Ams_Utility_Pvt.debug_message('Private API: ' || l_api_name || ': end');
268 END IF;
269
270 -- Standard call to get message count and if count is 1, get message info.
271 FND_MSG_PUB.Count_And_Get
272 (p_count => x_msg_count,
273 p_data => x_msg_data
274 );
275 EXCEPTION
276
280
277 WHEN Ams_Utility_Pvt.resource_locked THEN
278 x_return_status := FND_API.g_ret_sts_error;
279 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
281 WHEN FND_API.G_EXC_ERROR THEN
282 ROLLBACK TO CREATE_metric_formula_SP;
283 x_return_status := FND_API.G_RET_STS_ERROR;
284 -- Standard call to get message count and if count=1, get the message
285 FND_MSG_PUB.Count_And_Get (
286 p_encoded => FND_API.G_FALSE,
287 p_count => x_msg_count,
288 p_data => x_msg_data
289 );
290
291 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
292 ROLLBACK TO CREATE_metric_formula_SP;
293 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
294 -- Standard call to get message count and if count=1, get the message
295 FND_MSG_PUB.Count_And_Get (
296 p_encoded => FND_API.G_FALSE,
297 p_count => x_msg_count,
298 p_data => x_msg_data
299 );
300
301 WHEN OTHERS THEN
302 ROLLBACK TO CREATE_metric_formula_SP;
303 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
304 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
305 THEN
306 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
307 END IF;
308 -- Standard call to get message count and if count=1, get the message
309 FND_MSG_PUB.Count_And_Get (
310 p_encoded => FND_API.G_FALSE,
311 p_count => x_msg_count,
312 p_data => x_msg_data
313 );
314 END Create_metric_formula;
315
316
317 PROCEDURE Update_Metric_Formula(
318 p_api_version_number IN NUMBER,
319 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
320 p_commit IN VARCHAR2 := FND_API.G_FALSE,
321 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
322
323 x_return_status OUT nocopy VARCHAR2,
324 x_msg_count OUT nocopy NUMBER,
325 x_msg_data OUT nocopy VARCHAR2,
326
327 p_met_formula_rec IN met_formula_rec_type,
328 x_object_version_number OUT nocopy NUMBER
329 )
330 IS
331 CURSOR c_get_metric_formula(l_METRIC_FORMULA_ID NUMBER)
332 return met_formula_rec_type IS
333 SELECT
334 metric_formula_id,
335 last_update_date,
336 last_updated_by,
337 creation_date,
338 created_by,
339 last_update_login,
340 object_version_number,
341 metric_id,
342 source_type,
343 source_id,
344 source_sub_id,
345 source_value,
346 token,
347 notation_type,
348 use_sub_id_flag,
349 sequence
350 FROM AMS_METRIC_FORMULAS
351 WHERE METRIC_FORMULA_ID = l_METRIC_FORMULA_ID;
352
353 CURSOR c_get_max_sequence(l_metric_id NUMBER) is
354 select nvl(max(sequence),G_SEQUENCE_INCREMENT) from ams_metric_formulas
355 where metric_id = l_metric_id;
356
357 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_metric_formula';
358 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
359 -- Local Variables
360 l_object_version_number NUMBER;
361 l_METRIC_FORMULA_ID NUMBER;
362 l_ref_metric_formula_rec met_formula_rec_type ;
363 l_tar_metric_formula_rec met_formula_rec_type := p_met_formula_rec;
364 l_rowid ROWID;
365 l_max_sequence NUMBER := null;
366
367 BEGIN
368 -- Standard Start of API savepoint
369 SAVEPOINT UPDATE_metric_formula_sp;
370
371 -- Standard call to check for call compatibility.
372 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
373 p_api_version_number,
374 l_api_name,
375 G_PKG_NAME)
376 THEN
377 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
378 END IF;
379
380 -- Initialize message list if p_init_msg_list is set to TRUE.
381 IF FND_API.to_Boolean( p_init_msg_list )
382 THEN
383 FND_MSG_PUB.initialize;
384 END IF;
385
386 -- Debug Message
387 IF (AMS_DEBUG_HIGH_ON) THEN
388
389 Ams_Utility_Pvt.debug_message('Private API: ' || l_api_name || ': start');
390 END IF;
391
392 -- Initialize API return status to SUCCESS
393 x_return_status := FND_API.G_RET_STS_SUCCESS;
394
395 -- Debug Message
396 IF (AMS_DEBUG_HIGH_ON) THEN
397 Ams_Utility_Pvt.debug_message('Private API: - Open Cursor to Select');
398 END IF;
399
400 OPEN c_get_metric_formula( l_tar_metric_formula_rec.METRIC_FORMULA_ID);
401
402 FETCH c_get_metric_formula INTO l_ref_metric_formula_rec;
403
404 IF ( c_get_metric_formula%NOTFOUND) THEN
405 CLOSE c_get_metric_formula;
406 Ams_Utility_Pvt.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
407 p_token_name => 'INFO',
408 p_token_value => 'metric_formula_id='||l_tar_metric_formula_rec.METRIC_FORMULA_ID);
409 RAISE FND_API.G_EXC_ERROR;
410 END IF;
411
412 -- Debug Message
413 IF (AMS_DEBUG_HIGH_ON) THEN
414 Ams_Utility_Pvt.debug_message('Private API: - Close Cursor');
415 END IF;
419
416 CLOSE c_get_metric_formula;
417
418 Complete_metric_formula_rec(l_ref_metric_formula_rec, l_tar_metric_formula_rec);
420 IF (l_tar_metric_formula_rec.object_version_number IS NULL OR
421 l_tar_metric_formula_rec.object_version_number = FND_API.G_MISS_NUM ) THEN
422 Ams_Utility_Pvt.Error_Message(p_message_name => 'API_VERSION_MISSING',
423 p_token_name => 'COLUMN',
424 p_token_value => 'OBJECT_VERSION_NUMBER');
425 RAISE FND_API.G_EXC_ERROR;
426 END IF;
427
428 -- Check Whether record has been changed by someone else
429 IF (l_tar_metric_formula_rec.object_version_number <> l_ref_metric_formula_rec.object_version_number) THEN
430 Ams_Utility_Pvt.Error_Message(p_message_name => 'API_RECORD_CHANGED',
431 p_token_name => 'INFO',
432 p_token_value => 'metric_formula_id='||l_tar_metric_formula_rec.METRIC_FORMULA_ID);
433 RAISE FND_API.G_EXC_ERROR;
434 END IF;
435
436 --check if we are trying to update a seeded metric formula
437 IF l_tar_metric_formula_rec.metric_id < 10000 THEN
438 IF ( (l_tar_metric_formula_rec.METRIC_FORMULA_ID <>l_ref_metric_formula_rec.METRIC_FORMULA_ID )
439 OR (l_tar_metric_formula_rec.metric_id <>l_ref_metric_formula_rec.metric_id)
440 ) THEN
441 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
442 THEN
443 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_METR_SEEDED_METR');
444 END IF;
445 RAISE FND_API.G_EXC_ERROR;
446 END IF;
447 END IF;
448
449 if l_tar_metric_formula_rec.sequence is null then
450 if l_ref_metric_formula_rec.sequence is not null then
451 l_tar_metric_formula_rec.sequence := l_ref_metric_formula_rec.sequence;
452 else
453 open c_get_max_sequence(l_tar_metric_formula_rec.metric_id);
454 fetch c_get_max_sequence into l_max_sequence;
455 close c_get_max_sequence;
456 l_tar_metric_formula_rec.sequence := nvl(l_max_sequence,G_SEQUENCE_INCREMENT);
457 end if;
458 end if;
459
460 IF ( p_validation_level >= FND_API.G_VALID_LEVEL_FULL)
461 THEN
462 -- Debug message
463 IF (AMS_DEBUG_HIGH_ON) THEN
464 Ams_Utility_Pvt.debug_message('Private API: Validate_metric_formula');
465 END IF;
466
467 -- Invoke validation procedures
468 Validate_metric_formula(
469 p_api_version_number => 1.0,
470 p_init_msg_list => FND_API.G_FALSE,
471 p_validation_level => p_validation_level,
472 p_validation_mode => JTF_PLSQL_API.g_update,
473 p_metric_formula_rec => l_tar_metric_formula_rec,
474 x_return_status => x_return_status,
475 x_msg_count => x_msg_count,
476 x_msg_data => x_msg_data);
477 -- Debug message
478 IF (AMS_DEBUG_HIGH_ON) THEN
479 Ams_Utility_Pvt.debug_message('Private API: Validate_metric_formula: return status='||x_return_status);
480 END IF;
481 END IF;
482
483 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
484 RAISE FND_API.G_EXC_ERROR;
485 END IF;
486
487 l_object_version_number :=
488 l_ref_metric_formula_rec.object_version_number + 1;
489
490 -- Debug Message
491 IF (AMS_DEBUG_HIGH_ON) THEN
492 Ams_Utility_Pvt.debug_message('Private API: Calling Ams_Metric_Formulas_Pkg.Update_Row, sequence='||l_tar_metric_formula_rec.sequence);
493 END IF;
494
495 -- Invoke table handler(Ams_Metric_Formulas_Pkg.Update_Row)
496 Ams_Metric_Formulas_Pkg.Update_Row(
497 X_METRIC_FORMULA_ID => l_tar_metric_formula_rec.METRIC_FORMULA_ID,
498 X_METRIC_ID => l_tar_metric_formula_rec.metric_id,
499 X_SOURCE_TYPE => l_tar_metric_formula_rec.source_type,
500 X_SOURCE_ID => l_tar_metric_formula_rec.source_id,
501 X_SOURCE_SUB_ID => l_tar_metric_formula_rec.source_sub_id,
502 X_USE_SUB_ID_FLAG => l_tar_metric_formula_rec.use_sub_id_flag,
503 X_SOURCE_VALUE => l_tar_metric_formula_rec.source_value,
504 X_TOKEN => l_tar_metric_formula_rec.token,
505 X_SEQUENCE => l_tar_metric_formula_rec.sequence,
506 X_NOTATION_TYPE => l_tar_metric_formula_rec.notation_type,
507 X_OBJECT_VERSION_NUMBER => l_object_version_number,
508 X_LAST_UPDATE_DATE => sysdate,
509 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
510 X_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID);
511
512 --
513 -- End of API body.
514 --
515
516 -- Standard check for p_commit
517 IF FND_API.to_Boolean( p_commit )
518 THEN
519 COMMIT WORK;
520 END IF;
521
522
523 -- Debug Message
524 IF (AMS_DEBUG_HIGH_ON) THEN
525
526 Ams_Utility_Pvt.debug_message('Private API: ' || l_api_name || ': end');
527 END IF;
528
529 -- Standard call to get message count and if count is 1, get message info.
530 FND_MSG_PUB.Count_And_Get
531 (p_count => x_msg_count,
532 p_data => x_msg_data
533 );
534 EXCEPTION
535
536 WHEN Ams_Utility_Pvt.resource_locked THEN
537 x_return_status := FND_API.g_ret_sts_error;
538 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
539
540 WHEN FND_API.G_EXC_ERROR THEN
541 ROLLBACK TO UPDATE_metric_formula_sp;
542 x_return_status := FND_API.G_RET_STS_ERROR;
543 -- Standard call to get message count and if count=1, get the message
544 FND_MSG_PUB.Count_And_Get (
545 p_encoded => FND_API.G_FALSE,
549
546 p_count => x_msg_count,
547 p_data => x_msg_data
548 );
550 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
551 ROLLBACK TO UPDATE_metric_formula_sp;
552 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
553 -- Standard call to get message count and if count=1, get the message
554 FND_MSG_PUB.Count_And_Get (
555 p_encoded => FND_API.G_FALSE,
556 p_count => x_msg_count,
557 p_data => x_msg_data
558 );
559
560 WHEN OTHERS THEN
561 ROLLBACK TO UPDATE_metric_formula_sp;
562 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
563 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
564 THEN
565 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
566 END IF;
567 -- Standard call to get message count and if count=1, get the message
568 FND_MSG_PUB.Count_And_Get (
569 p_encoded => FND_API.G_FALSE,
570 p_count => x_msg_count,
571 p_data => x_msg_data
572 );
573 END Update_metric_formula;
574
575
576 PROCEDURE Delete_Metric_Formula(
577 p_api_version_number IN NUMBER,
578 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
579 p_commit IN VARCHAR2 := FND_API.G_FALSE,
580 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
581 x_return_status OUT nocopy VARCHAR2,
582 x_msg_count OUT nocopy NUMBER,
583 x_msg_data OUT nocopy VARCHAR2,
584 p_metric_formula_id IN NUMBER,
585 p_object_version_number IN NUMBER
586 )
587
588 IS
589 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_metric_formula';
590 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
591 l_object_version_number NUMBER;
592
593 BEGIN
594 -- Standard Start of API savepoint
595 SAVEPOINT DELETE_metric_formula_SP;
596
597 -- Standard call to check for call compatibility.
598 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
599 p_api_version_number,
600 l_api_name,
601 G_PKG_NAME)
602 THEN
603 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
604 END IF;
605
606 -- Initialize message list if p_init_msg_list is set to TRUE.
607 IF FND_API.to_Boolean( p_init_msg_list )
608 THEN
609 FND_MSG_PUB.initialize;
610 END IF;
611
612 -- Debug Message
613 IF (AMS_DEBUG_HIGH_ON) THEN
614
615 Ams_Utility_Pvt.debug_message('Private API: ' || l_api_name || ': start');
616 END IF;
617
618 --check if the formula is a seeded one
619 IF p_METRIC_FORMULA_ID < 10000 THEN
620 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
624
621 THEN
622 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_METR_SEEDED_METR');
623 END IF;
625 RAISE FND_API.G_EXC_ERROR;
626 END IF;
627
628 -- Initialize API return status to SUCCESS
629 x_return_status := FND_API.G_RET_STS_SUCCESS;
630
631 --
632 -- Api body
633 --
634 -- Debug Message
635 IF (AMS_DEBUG_HIGH_ON) THEN
636
637 Ams_Utility_Pvt.debug_message( 'Private API: Calling delete table handler');
638 END IF;
639
640 -- Invoke table handler(AMS_MET_TPL_DETAILS_PKG.Delete_Row)
641 AMS_METRIC_FORMULAS_PKG.Delete_Row(
642 X_METRIC_FORMULA_ID => p_METRIC_FORMULA_ID);
643 --
644 -- End of API body
645 --
646
647 -- Standard check for p_commit
648 IF FND_API.to_Boolean( p_commit )
649 THEN
650 COMMIT WORK;
651 END IF;
652
653
654 -- Debug Message
655 IF (AMS_DEBUG_HIGH_ON) THEN
656
657 Ams_Utility_Pvt.debug_message('Private API: ' || l_api_name || ': end');
658 END IF;
659
660 -- Standard call to get message count and if count is 1, get message info.
661 FND_MSG_PUB.Count_And_Get
662 (p_count => x_msg_count,
663 p_data => x_msg_data
664 );
665 EXCEPTION
666
667 WHEN Ams_Utility_Pvt.resource_locked THEN
668 x_return_status := FND_API.g_ret_sts_error;
669 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
670
671 WHEN FND_API.G_EXC_ERROR THEN
672 ROLLBACK TO DELETE_metric_formula_SP;
673 x_return_status := FND_API.G_RET_STS_ERROR;
674 -- Standard call to get message count and if count=1, get the message
675 FND_MSG_PUB.Count_And_Get (
676 p_encoded => FND_API.G_FALSE,
677 p_count => x_msg_count,
678 p_data => x_msg_data
679 );
680
681 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
682 ROLLBACK TO DELETE_metric_formula_SP;
683 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
684 -- Standard call to get message count and if count=1, get the message
685 FND_MSG_PUB.Count_And_Get (
686 p_encoded => FND_API.G_FALSE,
687 p_count => x_msg_count,
688 p_data => x_msg_data
689 );
690
691 WHEN OTHERS THEN
692 ROLLBACK TO DELETE_metric_formula_SP;
693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
694 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
695 THEN
696 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
697 END IF;
698 -- Standard call to get message count and if count=1, get the message
699 FND_MSG_PUB.Count_And_Get (
700 p_encoded => FND_API.G_FALSE,
701 p_count => x_msg_count,
702 p_data => x_msg_data
703 );
704 END Delete_metric_formula;
705
706
707
708 -- Hint: Primary key needs to be returned.
709 PROCEDURE Lock_Metric_Formula(
710 p_api_version_number IN NUMBER,
711 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
712
713 x_return_status OUT nocopy VARCHAR2,
714 x_msg_count OUT nocopy NUMBER,
715 x_msg_data OUT nocopy VARCHAR2,
716
717 p_metric_formula_id IN NUMBER,
718 p_object_version IN NUMBER
719 )
720
721 IS
722 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_metric_formula';
723 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
724 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
725 l_METRIC_FORMULA_ID NUMBER;
726
727 CURSOR c_metric_formula IS
728 SELECT METRIC_FORMULA_ID
729 FROM AMS_METRIC_FORMULAS
730 WHERE METRIC_FORMULA_ID = p_METRIC_FORMULA_ID
731 AND object_version_number = p_object_version
732 FOR UPDATE NOWAIT;
733
734 BEGIN
735
736 savepoint LOCK_metric_formula_SP;
737 -- Debug Message
738 IF (AMS_DEBUG_HIGH_ON) THEN
739
740 Ams_Utility_Pvt.debug_message('Private API: ' || l_api_name || ': start');
741 END IF;
742
743 -- Initialize message list if p_init_msg_list is set to TRUE.
747 END IF;
744 IF FND_API.to_Boolean( p_init_msg_list )
745 THEN
746 FND_MSG_PUB.initialize;
748
749 -- Standard call to check for call compatibility.
750 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
751 p_api_version_number,
752 l_api_name,
753 G_PKG_NAME)
754 THEN
755 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
756 END IF;
757
758
759 -- Initialize API return status to SUCCESS
760 x_return_status := FND_API.G_RET_STS_SUCCESS;
761
762 ------------------------ lock -------------------------
763
764 IF (AMS_DEBUG_HIGH_ON) THEN
765 Ams_Utility_Pvt.debug_message(l_full_name||': start');
766 END IF;
767
768 OPEN c_metric_formula;
769
770 FETCH c_metric_formula INTO l_METRIC_FORMULA_ID;
771
772 IF (c_metric_formula%NOTFOUND) THEN
773 CLOSE c_metric_formula;
774 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
775 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
776 FND_MSG_PUB.ADD;
777 END IF;
778 RAISE FND_API.g_exc_error;
779 END IF;
780
781 CLOSE c_metric_formula;
782
783 -------------------- finish --------------------------
784 FND_MSG_PUB.count_and_get(
785 p_encoded => FND_API.g_false,
786 p_count => x_msg_count,
787 p_data => x_msg_data);
788 IF (AMS_DEBUG_HIGH_ON) THEN
789 Ams_Utility_Pvt.debug_message(l_full_name ||': end');
790 END IF;
791 EXCEPTION
792
793 WHEN Ams_Utility_Pvt.resource_locked THEN
794 x_return_status := FND_API.g_ret_sts_error;
795 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
796
797 WHEN FND_API.G_EXC_ERROR THEN
798 ROLLBACK TO LOCK_metric_formula_SP;
799 x_return_status := FND_API.G_RET_STS_ERROR;
800 -- Standard call to get message count and if count=1, get the message
801 FND_MSG_PUB.Count_And_Get (
802 p_encoded => FND_API.G_FALSE,
803 p_count => x_msg_count,
804 p_data => x_msg_data
805 );
806
807 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
808 ROLLBACK TO LOCK_metric_formula_SP;
809 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
810 -- Standard call to get message count and if count=1, get the message
811 FND_MSG_PUB.Count_And_Get (
812 p_encoded => FND_API.G_FALSE,
813 p_count => x_msg_count,
814 p_data => x_msg_data
815 );
816
817 WHEN OTHERS THEN
818 ROLLBACK TO LOCK_metric_formula_SP;
819 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
820 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
821 THEN
822 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
823 END IF;
824 -- Standard call to get message count and if count=1, get the message
825 FND_MSG_PUB.Count_And_Get (
826 p_encoded => FND_API.G_FALSE,
827 p_count => x_msg_count,
828 p_data => x_msg_data
829 );
830 END Lock_metric_formula;
831
832
833 PROCEDURE check_metric_formula_uk_items(
834 p_metric_formula_rec IN met_formula_rec_type,
835 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
836 x_return_status OUT NOCOPY VARCHAR2)
837 IS
838 l_valid_flag VARCHAR2(1);
839 /*
840 l_dummy NUMBER;
841
842 cursor c_check_sequence_update(p_metric_formula_id number, p_metric_id number,
843 p_sequence NUMBER) IS
844 select COUNT(1) from ams_metric_formulas
845 where metric_id = p_metric_id
846 and metric_formula_id <> p_metric_formula_id
847 and sequence = p_sequence;
848
849 cursor c_check_sequence_new(p_metric_id number,
850 p_sequence NUMBER) IS
851 select COUNT(1) from ams_metric_formulas
852 where metric_id = p_metric_id
853 and sequence = p_sequence;
854 */
855 BEGIN
856 -- Debug Message
857 IF (AMS_DEBUG_HIGH_ON) THEN
858 Ams_Utility_Pvt.debug_message('PRIVATE API: check_metric_formula_uk_items : START');
859 END IF;
860 x_return_status := FND_API.g_ret_sts_success;
861 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
862 l_valid_flag := Ams_Utility_Pvt.check_uniqueness(
863 'AMS_METRIC_FORMULAS',
864 'METRIC_FORMULA_ID = ' || p_metric_formula_rec.METRIC_FORMULA_ID
865 );
866 END IF;
867
868 IF l_valid_flag = FND_API.g_false THEN
869 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_METR_FORMULA_ID_DUP');
870 x_return_status := FND_API.g_ret_sts_error;
871 RETURN;
872 END IF;
873 /*
874 l_dummy := 0;
875 IF p_validation_mode = JTF_PLSQL_API.G_CREATE THEN
876 open c_check_sequence_new(p_metric_formula_rec.metric_id,
877 p_metric_formula_rec.sequence);
878 fetch c_check_sequence_new INTO l_dummy;
879 close c_check_sequence_new;
883 fetch c_check_sequence_update INTO l_dummy;
880 elsif p_validation_mode = JTF_PLSQL_API.G_UPDATE THEN
881 open c_check_sequence_update(p_metric_formula_rec.metric_formula_id,
882 p_metric_formula_rec.metric_id, p_metric_formula_rec.sequence);
884 close c_check_sequence_update;
885 END IF;
886
887 IF l_dummy > 0 THEN
888 AMS_UTILITY_PVT.ERROR_MESSAGE(p_message_name => 'AMS_METR_INVALID_SEQUENCE');
889 x_return_status := FND_API.g_ret_sts_error;
890 RETURN;
891 END IF;
892 */
893 -- Debug Message
894 IF (AMS_DEBUG_HIGH_ON) THEN
895 Ams_Utility_Pvt.debug_message('PRIVATE API: check_metric_formula_uk_items : END');
896 END IF;
897 END check_metric_formula_uk_items;
898
899 PROCEDURE check_metric_formula_req_items(
900 p_metric_formula_rec IN met_formula_rec_type,
901 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
902 x_return_status OUT NOCOPY VARCHAR2
903 )
904 IS
905 BEGIN
906 -- Debug Message
907 IF (AMS_DEBUG_HIGH_ON) THEN
908 Ams_Utility_Pvt.debug_message('PRIVATE API: check_metric_formula_req_items : START');
909 END IF;
910 x_return_status := FND_API.g_ret_sts_success;
911
912 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
913
914 -- Debug Message
915 IF (AMS_DEBUG_HIGH_ON) THEN
916 Ams_Utility_Pvt.debug_message('PRIVATE API: check_metric_formula_req_items : metric_id='||p_metric_formula_rec.metric_id);
917 END IF;
918 IF p_metric_formula_rec.metric_id = FND_API.g_miss_num OR p_metric_formula_rec.metric_id IS NULL THEN
919 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
920 FND_MESSAGE.set_token('MISS_FIELD','METRIC_ID');
921 x_return_status := FND_API.g_ret_sts_error;
922 RETURN;
923 END IF;
924
925 -- Debug Message
926 IF (AMS_DEBUG_HIGH_ON) THEN
927 Ams_Utility_Pvt.debug_message('PRIVATE API: check_metric_formula_req_items : source_type='||p_metric_formula_rec.source_type);
928 END IF;
929 IF p_metric_formula_rec.source_type = FND_API.G_MISS_CHAR OR p_metric_formula_rec.source_type IS NULL THEN
930 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
931 FND_MESSAGE.set_token('MISS_FIELD','SOURCE_TYPE');
932 x_return_status := FND_API.g_ret_sts_error;
933 RETURN;
934 END IF;
935
936 IF p_metric_formula_rec.source_type IN ('METRIC','CATEGORY') THEN
937 -- Debug Message
938 IF (AMS_DEBUG_HIGH_ON) THEN
939 Ams_Utility_Pvt.debug_message('PRIVATE API: check_metric_formula_req_items : source_id='||p_metric_formula_rec.source_id);
940 END IF;
941 IF p_metric_formula_rec.source_id IS NULL OR p_metric_formula_rec.source_id = FND_API.G_MISS_NUM THEN
942 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
943 FND_MESSAGE.set_token('MISS_FIELD','SOURCE_ID');
944 x_return_status := FND_API.g_ret_sts_error;
945 RETURN;
946 END IF;
947 -- Debug Message
948 IF (AMS_DEBUG_HIGH_ON) THEN
949 Ams_Utility_Pvt.debug_message('PRIVATE API: check_metric_formula_req_items : use_sub_id_flag='||p_metric_formula_rec.use_sub_id_flag);
950 END IF;
951 IF p_metric_formula_rec.source_type = 'CATEGORY' AND
952 (p_metric_formula_rec.use_sub_id_flag IS NULL OR p_metric_formula_rec.use_sub_id_flag = FND_API.G_MISS_CHAR) THEN
953 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
954 FND_MESSAGE.set_token('MISS_FIELD','USE_SUB_ID_FLAG');
955 x_return_status := FND_API.g_ret_sts_error;
956 RETURN;
957 END IF;
958 ELSIF p_metric_formula_rec.source_type = 'OPERAND' and
959 (p_metric_formula_rec.TOKEN IS NULL OR p_metric_formula_rec.TOKEN = FND_API.G_MISS_CHAR) THEN
960 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
961 FND_MESSAGE.set_token('MISS_FIELD','TOKEN');
962 x_return_status := FND_API.g_ret_sts_error;
963 RETURN;
964 ELSIF p_metric_formula_rec.source_type = 'NUMBER' and
965 (p_metric_formula_rec.source_value IS NULL OR p_metric_formula_rec.source_value = FND_API.G_MISS_NUM) THEN
966 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
967 FND_MESSAGE.set_token('MISS_FIELD','SOURCE_VALUE');
968 x_return_status := FND_API.g_ret_sts_error;
969 RETURN;
970 END IF;
971
972 -- Debug Message
973 IF (AMS_DEBUG_HIGH_ON) THEN
974 Ams_Utility_Pvt.debug_message('PRIVATE API: check_metric_formula_req_items : notation_type='||p_metric_formula_rec.notation_type);
975 END IF;
976 IF p_metric_formula_rec.notation_type = FND_API.g_miss_char OR p_metric_formula_rec.notation_type IS NULL THEN
977 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
978 FND_MESSAGE.set_token('MISS_FIELD','NOTATION_TYPE');
979 x_return_status := FND_API.g_ret_sts_error;
980 RETURN;
981 END IF;
982
983 ELSE -- Update
984
985 IF p_metric_formula_rec.METRIC_FORMULA_ID = FND_API.g_miss_num OR p_metric_formula_rec.METRIC_FORMULA_ID IS NULL THEN
986 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
987 FND_MESSAGE.set_token('MISS_FIELD','METRIC_FORMULA_ID');
988 x_return_status := FND_API.g_ret_sts_error;
989 RETURN;
990 END IF;
991
992 IF p_metric_formula_rec.sequence = FND_API.g_miss_num OR p_metric_formula_rec.sequence IS NULL THEN
993 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
994 FND_MESSAGE.set_token('MISS_FIELD','SEQUENCE');
995 x_return_status := FND_API.g_ret_sts_error;
996 RETURN;
997 END IF;
998
999 END IF;
1000
1001 -- Debug Message
1002 IF (AMS_DEBUG_HIGH_ON) THEN
1003 Ams_Utility_Pvt.debug_message('PRIVATE API: check_metric_formula_req_items : END');
1004 END IF;
1005 END check_metric_formula_req_items;
1006
1007 PROCEDURE check_metric_formula_FK_items(
1008 p_metric_formula_rec IN met_formula_rec_type,
1009 x_return_status OUT NOCOPY VARCHAR2
1010 )
1011 IS
1012 CURSOR c_check_metric_parent(p_metric_id NUMBER) IS
1013 SELECT arc_metric_used_for_object, metric_calculation_type
1014 FROM ams_metrics_all_b
1015 WHERE metric_id = p_metric_id;
1016
1017 CURSOR c_check_category(p_CATEGORY_ID NUMBER) IS
1018 SELECT count(1) FROM AMS_CATEGORIES_VL
1019 WHERE CATEGORY_ID = p_CATEGORY_ID
1020 and arc_category_created_for = 'METR';
1021
1022 CURSOR c_check_sub_category(p_CATEGORY_ID NUMBER, p_SUB_CATEGORY_ID NUMBER) IS
1023 SELECT count(1) FROM AMS_CATEGORIES_VL
1024 WHERE CATEGORY_ID = p_SUB_CATEGORY_ID
1025 and parent_category_id = p_category_id
1026 and arc_category_created_for = 'METR';
1027
1028 CURSOR c_check_metric_source(p_metric_id NUMBER) IS
1029 SELECT arc_metric_used_for_object FROM ams_metrics_all_b
1030 WHERE metric_id = p_metric_id
1031 and metric_calculation_type <> 'FORMULA';
1032
1033 l_dummy NUMBER;
1034 l_object_type VARCHAR2(30);
1035 l_calculation_type VARCHAR2(30);
1036 l_source_object_type VARCHAR2(30);
1037 BEGIN
1038 x_return_status := FND_API.g_ret_sts_success;
1039
1043 OPEN c_check_metric_parent(p_metric_formula_rec.metric_id);
1040 -- Enter custom code here
1041
1042 -- Validate metric_id exists.
1044 FETCH c_check_metric_parent INTO l_object_type, l_calculation_type;
1045 IF c_check_metric_parent%NOTFOUND THEN
1046 Ams_Utility_Pvt.error_message(p_message_name => 'AMS_METR_INVALID_METRIC_ID',
1047 p_token_name => 'METRIC_ID',
1048 p_token_value => p_metric_formula_rec.metric_id);
1049 x_return_status := FND_API.g_ret_sts_error;
1050 elsif l_calculation_type <> 'FORMULA' THEN
1051 Ams_Utility_pvt.error_message(p_message_name => 'AMS_METR_INVALID_FORMULA_TYPE');
1052 x_return_status := FND_API.g_ret_sts_error;
1053 END IF;
1054 CLOSE c_check_metric_parent;
1055
1056 -- Validate the metric_id exists
1057 IF p_metric_formula_rec.source_type = 'METRIC' THEN
1058 OPEN c_check_metric_source(p_metric_formula_rec.SOURCE_id);
1059 FETCH c_check_metric_source INTO l_source_object_type;
1060 IF c_check_metric_source%NOTFOUND THEN
1061 Ams_Utility_Pvt.error_message(p_message_name => 'AMS_METR_INVALID_METRIC_SOURCE');
1062 x_return_status := FND_API.g_ret_sts_error;
1063 ELSIF l_object_type = 'ANY' and l_source_object_type <> 'ANY' then
1064 Ams_Utility_Pvt.error_message(p_message_name => 'AMS_METR_INVALID_METRIC_SOURCE');
1065 x_return_status := FND_API.g_ret_sts_error;
1066 ELSIF l_object_type <> 'ANY' and l_source_object_type NOT in ('ANY', l_object_type) THEN
1067 Ams_Utility_Pvt.error_message(p_message_name => 'AMS_METR_INVALID_METRIC_SOURCE');
1068 x_return_status := FND_API.g_ret_sts_error;
1069 END IF;
1070 CLOSE c_check_metric_source;
1071 ELSIF p_metric_formula_rec.source_type = 'CATEGORY' THEN
1072 OPEN c_check_category(p_metric_formula_rec.source_id);
1073 FETCH c_check_category INTO l_dummy;
1074 CLOSE c_check_category;
1075 IF l_dummy <> 1 then
1076 Ams_Utility_Pvt.error_message(p_message_name => 'AMS_METR_INVALID_CATEGORY_SRC');
1077 x_return_status := FND_API.g_ret_sts_error;
1078 ELSE
1079 IF p_metric_formula_rec.use_sub_id_flag = 'Y' AND
1080 p_metric_formula_rec.source_sub_id is not null THEN
1081 open c_check_sub_category(p_metric_formula_rec.source_id, p_metric_formula_rec.source_sub_id);
1082 fetch c_check_sub_category into l_dummy;
1083 close c_check_sub_category;
1084 IF l_dummy <> 1 then
1085 Ams_Utility_Pvt.error_message(p_message_name => 'AMS_METR_INVALID_CATEGORY_SRC');
1086 x_return_status := FND_API.g_ret_sts_error;
1087 END IF;
1088 end if;
1089 END IF;
1090 END IF;
1091
1092 END check_metric_formula_FK_items;
1093
1094 PROCEDURE check_metric_formula_lookups(
1095 p_metric_formula_rec IN met_formula_rec_type,
1096 x_return_status OUT NOCOPY VARCHAR2
1097 )
1098 IS
1099 BEGIN
1100 x_return_status := FND_API.g_ret_sts_success;
1101
1102 -- Enter custom code here
1103 IF p_metric_formula_rec.use_sub_id_flag is not null and
1104 Ams_Utility_Pvt.is_y_or_n(p_metric_formula_rec.use_sub_id_flag) = FND_API.G_FALSE THEN
1105 Ams_Utility_Pvt.error_message(p_message_name=>'AMS_METR_INVALID_USE_SUB_ID',
1106 p_token_name => 'USE_SUB_ID_FLAG',
1107 p_token_value=>p_metric_formula_rec.use_sub_id_flag);
1108 x_return_status := FND_API.g_ret_sts_error;
1109 END IF;
1110
1111 IF ams_utility_pvt.check_lookup_exists('AMS_LOOKUPS','AMS_METRIC_SOURCE_TYPE',p_metric_formula_rec.source_type) = FND_API.G_FALSE THEN
1112 Ams_Utility_Pvt.error_message(p_message_name => 'AMS_METR_INVALID_SOURCE_TYPE',
1113 p_token_name => 'SOURCE_TYPE',
1114 p_token_value => p_metric_formula_rec.source_type);
1115 x_return_status := FND_API.g_ret_sts_error;
1116 END IF;
1117
1118 IF ams_utility_pvt.check_lookup_exists('AMS_LOOKUPS','AMS_METRIC_NOTATION_TYPE',p_metric_formula_rec.notation_type) = FND_API.G_FALSE THEN
1119 Ams_Utility_Pvt.error_message(p_message_name => 'AMS_METR_INVALID_NOTATION_TYPE',
1120 p_token_name => 'NOTATION_TYPE',
1121 p_token_value => p_metric_formula_rec.notation_type);
1122 x_return_status := FND_API.g_ret_sts_error;
1123 END IF;
1124
1125 IF p_metric_formula_rec.source_type = 'OPERATOR' and p_metric_formula_rec.TOKEN is not null and
1126 ams_utility_pvt.check_lookup_exists('AMS_LOOKUPS','AMS_METRIC_OPERAND_TYPE',p_metric_formula_rec.token) = FND_API.G_FALSE THEN
1127 Ams_Utility_Pvt.error_message(p_message_name => 'AMS_METR_INVALID_OPERATOR',
1128 p_token_name => 'OPERATOR',
1129 p_token_value => p_metric_formula_rec.token);
1130 x_return_status := FND_API.g_ret_sts_error;
1131 END IF;
1132
1133 END check_metric_formula_lookups;
1134
1135 PROCEDURE Check_metric_formula_Items (
1136 p_metric_formula_rec IN met_formula_rec_type,
1137 p_validation_mode IN VARCHAR2,
1138 x_return_status OUT NOCOPY VARCHAR2
1139 )
1140 IS
1141 BEGIN
1142
1143 -- Debug Message
1144 IF (AMS_DEBUG_HIGH_ON) THEN
1145 Ams_Utility_Pvt.debug_message('PRIVATE API: check_metric_formula_items : START');
1149 check_metric_formula_uk_items(
1146 END IF;
1147 -- Check Items Uniqueness API calls
1148
1150 p_metric_formula_rec => p_metric_formula_rec,
1151 p_validation_mode => p_validation_mode,
1152 x_return_status => x_return_status);
1153 IF x_return_status <> FND_API.g_ret_sts_success THEN
1154 RETURN;
1155 END IF;
1156
1157 -- Check Items Required/NOT NULL API calls
1158
1159 check_metric_formula_req_items(
1160 p_metric_formula_rec => p_metric_formula_rec,
1161 p_validation_mode => p_validation_mode,
1162 x_return_status => x_return_status);
1163 IF x_return_status <> FND_API.g_ret_sts_success THEN
1164 RETURN;
1165 END IF;
1166 -- Check Items Foreign Keys API calls
1167
1168 check_metric_formula_FK_items(
1169 p_metric_formula_rec => p_metric_formula_rec,
1170 x_return_status => x_return_status);
1171 IF x_return_status <> FND_API.g_ret_sts_success THEN
1172 RETURN;
1173 END IF;
1174 -- Check Items Lookups
1175
1176 check_metric_formula_lookups(
1177 p_metric_formula_rec => p_metric_formula_rec,
1178 x_return_status => x_return_status);
1179 IF x_return_status <> FND_API.g_ret_sts_success THEN
1180 RETURN;
1181 END IF;
1182
1183 -- Debug Message
1184 IF (AMS_DEBUG_HIGH_ON) THEN
1185 Ams_Utility_Pvt.debug_message('PRIVATE API: check_metric_formula_items : END');
1186 END IF;
1187 END Check_metric_formula_Items;
1188
1189 PROCEDURE Complete_metric_formula_Rec (
1190 p_ref_metric_formula_rec IN met_formula_rec_type,
1191 x_tar_metric_formula_rec IN OUT NOCOPY met_formula_rec_type)
1192 IS
1193 -- l_return_status VARCHAR2(1);
1194
1195 -- CURSOR c_complete IS
1196 -- SELECT *
1197 -- FROM ams_met_tpl_details
1198 -- WHERE METRIC_FORMULA_ID = p_metric_formula_rec.METRIC_FORMULA_ID;
1199 -- l_metric_formula_rec c_complete%ROWTYPE;
1200 BEGIN
1201 -- metric_id
1202 IF x_tar_metric_formula_rec.metric_id = FND_API.g_miss_num THEN
1203 x_tar_metric_formula_rec.metric_id := p_ref_metric_formula_rec.metric_id;
1204 END IF;
1205
1206 -- source_type
1207 IF x_tar_metric_formula_rec.source_type = FND_API.G_MISS_CHAR THEN
1208 x_tar_metric_formula_rec.source_type := p_ref_metric_formula_rec.source_type;
1209 END IF;
1210
1211 -- source_id
1212 IF x_tar_metric_formula_rec.source_id = FND_API.g_miss_num THEN
1213 x_tar_metric_formula_rec.source_id := p_ref_metric_formula_rec.source_id;
1214 END IF;
1215
1216 -- source_sub_id
1217 IF x_tar_metric_formula_rec.source_sub_id = FND_API.g_miss_num THEN
1218 x_tar_metric_formula_rec.source_sub_id := p_ref_metric_formula_rec.source_sub_id;
1219 END IF;
1220
1221 -- source_value
1222 IF x_tar_metric_formula_rec.source_value = FND_API.g_miss_num THEN
1223 x_tar_metric_formula_rec.source_value := p_ref_metric_formula_rec.source_value;
1224 END IF;
1225
1226 -- use_sub_id_flag
1227 IF x_tar_metric_formula_rec.use_sub_id_flag = FND_API.g_miss_char THEN
1228 x_tar_metric_formula_rec.use_sub_id_flag := p_ref_metric_formula_rec.use_sub_id_flag;
1229 END IF;
1230
1231 -- token
1232 IF x_tar_metric_formula_rec.token = FND_API.g_miss_char THEN
1233 x_tar_metric_formula_rec.token := p_ref_metric_formula_rec.token;
1234 END IF;
1235
1236 -- sequence
1237 IF x_tar_metric_formula_rec.sequence = FND_API.g_miss_num THEN
1238 x_tar_metric_formula_rec.sequence := p_ref_metric_formula_rec.sequence;
1239 END IF;
1240
1241 -- notation_type
1242 IF x_tar_metric_formula_rec.notation_type = FND_API.g_miss_char THEN
1243 x_tar_metric_formula_rec.notation_type := p_ref_metric_formula_rec.notation_type;
1244 END IF;
1245
1246 -- Note: Developers need to modify the procedure
1247 -- to handle any business specific requirements.
1248 END Complete_metric_formula_Rec;
1249
1250
1251 PROCEDURE Validate_metric_formula(
1252 p_api_version_number IN NUMBER,
1253 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1254 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1255 p_metric_formula_rec IN met_formula_rec_type,
1256 p_validation_mode IN VARCHAR2,
1257 x_return_status OUT NOCOPY VARCHAR2,
1258 x_msg_count OUT NOCOPY NUMBER,
1259 x_msg_data OUT NOCOPY VARCHAR2
1260 )
1261 IS
1262 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_metric_formula';
1263 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1264 l_object_version_number NUMBER;
1265 l_metric_formula_rec met_formula_rec_type;
1266
1267 BEGIN
1268 -- Standard Start of API savepoint
1269 -- SAVEPOINT VALIDATE_metric_formula_SP;
1270
1271 -- Standard call to check for call compatibility.
1272 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1273 p_api_version_number,
1274 l_api_name,
1275 G_PKG_NAME)
1276 THEN
1277 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1278 END IF;
1279
1280 -- Initialize message list if p_init_msg_list is set to TRUE.
1284 END IF;
1281 IF FND_API.to_Boolean( p_init_msg_list )
1282 THEN
1283 FND_MSG_PUB.initialize;
1285
1286 -- Debug Message
1287 IF (AMS_DEBUG_HIGH_ON) THEN
1288 Ams_Utility_Pvt.debug_message('PRIVATE API: ' || l_api_name || ': START');
1289 END IF;
1290
1291
1292 -- Initialize API return status to SUCCESS
1293 x_return_status := FND_API.G_RET_STS_SUCCESS;
1294
1295
1296 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1297 Check_metric_formula_Items(
1298 p_metric_formula_rec => p_metric_formula_rec,
1299 p_validation_mode => p_validation_mode,
1300 x_return_status => x_return_status
1301 );
1302
1303 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1304 RAISE FND_API.G_EXC_ERROR;
1305 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1306 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1307 END IF;
1308 END IF;
1309
1310 -- Complete_metric_formula_Rec(
1311 -- p_metric_formula_rec => p_metric_formula_rec,
1312 -- x_complete_rec => l_metric_formula_rec
1313 -- );
1314
1315 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1316 Validate_metric_formula_Rec(
1317 p_api_version_number => 1.0,
1318 p_init_msg_list => FND_API.G_FALSE,
1319 x_return_status => x_return_status,
1320 x_msg_count => x_msg_count,
1321 x_msg_data => x_msg_data,
1322 p_metric_formula_rec => l_metric_formula_rec);
1323
1324 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1325 RAISE FND_API.G_EXC_ERROR;
1326 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1327 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1328 END IF;
1329 END IF;
1330
1331
1332 -- Debug Message
1333 IF (AMS_DEBUG_HIGH_ON) THEN
1334 Ams_Utility_Pvt.debug_message('PRIVATE API: ' || l_api_name || ': END');
1335 END IF;
1336
1337 -- Standard call to get message count and if count is 1, get message info.
1338 FND_MSG_PUB.Count_And_Get
1339 (p_count => x_msg_count,
1340 p_data => x_msg_data
1341 );
1342 EXCEPTION
1343
1344 WHEN Ams_Utility_Pvt.resource_locked THEN
1345 x_return_status := FND_API.g_ret_sts_error;
1346 Ams_Utility_Pvt.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1347
1348 WHEN FND_API.G_EXC_ERROR THEN
1349 -- ROLLBACK TO VALIDATE_metric_formula_sp;
1350 x_return_status := FND_API.G_RET_STS_ERROR;
1351 -- Standard call to get message count and if count=1, get the message
1352 FND_MSG_PUB.Count_And_Get (
1353 p_encoded => FND_API.G_FALSE,
1354 p_count => x_msg_count,
1355 p_data => x_msg_data
1356 );
1357
1358 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1359 -- ROLLBACK TO VALIDATE_metric_formula_sp;
1360 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1361 -- Standard call to get message count and if count=1, get the message
1362 FND_MSG_PUB.Count_And_Get (
1363 p_encoded => FND_API.G_FALSE,
1364 p_count => x_msg_count,
1365 p_data => x_msg_data
1366 );
1367
1368 WHEN OTHERS THEN
1369 -- ROLLBACK TO VALIDATE_metric_formula_sp;
1370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1371 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1372 THEN
1373 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1374 END IF;
1375 -- Standard call to get message count and if count=1, get the message
1376 FND_MSG_PUB.Count_And_Get (
1377 p_encoded => FND_API.G_FALSE,
1378 p_count => x_msg_count,
1379 p_data => x_msg_data
1380 );
1381 END Validate_metric_formula;
1382
1383
1384 PROCEDURE Validate_metric_formula_rec(
1385 p_api_version_number IN NUMBER,
1386 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1387 x_return_status OUT NOCOPY VARCHAR2,
1388 x_msg_count OUT NOCOPY NUMBER,
1389 x_msg_data OUT NOCOPY VARCHAR2,
1390 p_metric_formula_rec IN met_formula_rec_type
1391 )
1392 IS
1393 BEGIN
1394 -- Initialize message list if p_init_msg_list is set to TRUE.
1395 IF FND_API.to_Boolean( p_init_msg_list )
1396 THEN
1397 FND_MSG_PUB.initialize;
1398 END IF;
1399
1400 -- Initialize API return status to SUCCESS
1401 x_return_status := FND_API.G_RET_STS_SUCCESS;
1402
1403 -- Hint: Validate data
1404 -- If data not valid
1405 -- THEN
1406 -- x_return_status := FND_API.G_RET_STS_ERROR;
1407
1408 -- Debug Message
1409 IF (AMS_DEBUG_HIGH_ON) THEN
1413 FND_MSG_PUB.Count_And_Get
1410 Ams_Utility_Pvt.debug_message('PRIVATE API: Validate_metric_formula_rec');
1411 END IF;
1412 -- Standard call to get message count and if count is 1, get message info.
1414 (p_count => x_msg_count,
1415 p_data => x_msg_data
1416 );
1417 END Validate_metric_formula_Rec;
1418
1419 procedure push(p_stack_rec IN stack_rec_type)
1420 is
1421 l_index number;
1422 begin
1423 l_index := g_stack_tbl.count + 1;
1424 g_stack_tbl(l_index) := p_stack_rec;
1425 end push;
1426
1427 procedure pop(x_stack_rec OUT nocopy stack_rec_type)
1428 is
1429 l_index number;
1430 begin
1431 if g_stack_tbl.count = 0 then
1432 x_stack_rec.source_type := 'EMPTY';
1433 else
1434 l_index := g_stack_tbl.count;
1435 x_stack_rec := g_stack_tbl(l_index);
1436 g_stack_tbl.delete(l_index);
1437 end if;
1438 end pop;
1439
1440 procedure peek(x_stack_rec out nocopy stack_rec_type)
1441 is
1442 l_index number;
1443 begin
1444 if g_stack_tbl.count = 0 then
1445 x_stack_rec.source_type := 'EMPTY';
1446 else
1447 l_index := g_stack_tbl.last;
1448 x_stack_rec := g_stack_tbl(l_index);
1449 end if;
1450 end peek;
1451
1452 function has_more_infix
1453 return boolean
1454 is
1455 begin
1456 return g_infix_index <= g_infix_tbl.last;
1457 end has_more_infix;
1458
1459 procedure error_infix
1460 is
1461 l_error_msg VARCHAR2(150);
1462 begin
1463 l_error_msg := ams_utility_pvt.get_lookup_meaning('AMS_METRIC_STRINGS','ERROR');
1464 g_infix_formula := g_infix_formula || ' ' || l_error_msg;
1465 g_valid_formula := false;
1466 if g_error_sequence is null then
1467 g_error_sequence := g_current_sequence;
1468 end if;
1469 end error_infix;
1470
1471 procedure next_infix(x_formula_rec out nocopy stack_rec_type)
1472 is
1473 begin
1474 x_formula_rec := g_infix_tbl(g_infix_index);
1475 if g_current_sequence is not null AND
1476 g_current_sequence = x_formula_rec.sequence THEN
1477 AMS_UTILITY_PVT.ERROR_MESSAGE(p_message_name => 'AMS_METR_INVALID_SEQUENCE');
1478 error_infix;
1479 end if;
1480 g_current_sequence := x_formula_rec.sequence;
1481 g_infix_index := g_infix_tbl.next(g_infix_index);
1482 if length(g_infix_formula) > 0 then
1483 g_infix_formula := g_infix_formula || ' ';
1484 end if;
1485 g_infix_formula := g_infix_formula || x_formula_rec.source_name;
1486 end next_infix;
1487
1488 procedure add_postfix(p_formula_rec in stack_rec_type)
1489 is
1490 l_index number;
1491 begin
1492 if p_formula_rec.source_type <> 'EMPTY' then
1493 l_index := g_source_type_tbl.count + 1;
1494
1495 g_source_type_tbl(l_index) := p_formula_rec.source_type;
1496 g_source_id_tbl(l_index) := p_formula_rec.source_id;
1497 g_source_sub_id_tbl(l_index) := p_formula_rec.source_sub_id;
1498 g_source_value_tbl(l_index) := p_formula_rec.source_value;
1499 g_token_tbl(l_index) := p_formula_rec.token;
1500 g_use_sub_id_flag_tbl(l_index) := p_formula_rec.use_sub_id_flag;
1501 g_index_tbl(l_index) := l_index;
1502 end if;
1503 end add_postfix;
1504
1505 procedure reset_postfix
1506 is
1507 begin
1508 g_source_type_tbl.delete;
1509 g_source_id_tbl.delete;
1510 g_source_sub_id_tbl.delete;
1511 g_source_value_tbl.delete;
1512 g_token_tbl.delete;
1513 g_use_sub_id_flag_tbl.delete;
1514 g_index_tbl.delete;
1515 g_stack_tbl.delete;
1516 g_error_sequence := null;
1517 end reset_postfix;
1518
1519 --
1520 -- Purpose
1521 -- < we need to add a descriptive purpose of this procedure >
1522 --
1523 -- Change History
1524 -- ??-???-2003 dmvincen Created
1525 -- 12-Nov-2003 choang Added validation for first and last tokens in the formula.
1526 --
1527 procedure transform_formula
1528 is
1529 l_formula_rec stack_rec_type;
1530 l_lefthand_rec stack_rec_type;
1531 l_righthand_rec stack_rec_type;
1532 l_operator_rec stack_rec_type;
1533 l_paren_count NUMBER := 0;
1534 begin
1535 while has_more_infix loop
1536 next_infix(l_formula_rec);
1537
1538 IF (l_lefthand_rec.source_type = 'EMPTY') AND
1539 (l_formula_rec.source_type = 'OPERATOR' AND l_formula_rec.token <> 'LEFTPAREN') THEN
1540 -- choang - 12-nov-2003 - this validates that the first token of the formula
1541 -- cannot be an operator unless it is a left paren.
1542 error_infix;
1543
1544 -- Process formula indiscriminately
1545 push(l_formula_rec);
1546 IF l_formula_rec.token = 'RIGHTPAREN' THEN
1547 l_paren_count := l_paren_count - 1;
1548 END IF;
1549 elsif (l_formula_rec.source_type = 'OPERATOR' and
1550 l_formula_rec.token = 'LEFTPAREN') THEN
1551 if l_lefthand_rec.source_type in ('NUMBER','CATEGORY','METRIC') then
1552 error_infix;
1553 end if;
1557 l_formula_rec.token = 'RIGHTPAREN') THEN
1554 push(l_formula_rec);
1555 l_paren_count := l_paren_count + 1;
1556 elsif (l_formula_rec.source_type = 'OPERATOR' and
1558 if l_lefthand_rec.source_type in ('OPERATOR') and
1559 l_lefthand_rec.token <> 'RIGHTPAREN' then
1560 error_infix;
1561 end if;
1562 if l_paren_count <= 0 then
1563 error_infix;
1564 end if;
1565 l_paren_count := l_paren_count - 1;
1566 pop(l_operator_rec);
1567 add_postfix(l_operator_rec);
1568 pop(l_operator_rec); -- should be left paren.
1569 if l_operator_rec.token <> 'LEFTPAREN' then
1570 error_infix;
1571 end if;
1572 elsif (l_formula_rec.source_type in ('NUMBER','CATEGORY','METRIC')) THEN
1573 if l_lefthand_rec.source_type in ('NUMBER','CATEGORY','METRIC') then
1574 error_infix;
1575 end if;
1576 add_postfix(l_formula_rec);
1577 elsif (l_formula_rec.source_type = 'OPERATOR' and
1578 l_formula_rec.token IN ('TIMES','DIVIDE')) THEN
1579 if l_lefthand_rec.source_type in ('OPERATOR') and
1580 l_lefthand_rec.token in ('TIMES','DIVIDE','PLUS','MINUS') then
1581 error_infix;
1582 end if;
1583 peek(l_operator_rec);
1584 if l_operator_rec.source_type = 'EMPTY' then
1585 push(l_formula_rec);
1586 elsif l_operator_rec.source_type = 'OPERATOR' and
1587 l_operator_rec.token = 'LEFTPAREN' then
1588 push(l_formula_rec);
1589 elsif l_operator_rec.token in ('TIMES','DIVIDE') then
1590 add_postfix(l_operator_rec);
1591 pop(l_operator_rec);
1592 push(l_formula_rec);
1593 elsif l_operator_rec.token in ('PLUS','MINUS') then
1594 push(l_formula_rec);
1595 end if;
1596 elsif (l_formula_rec.source_type = 'OPERATOR' and
1597 l_formula_rec.token in ('PLUS','MINUS')) THEN
1598 if l_lefthand_rec.source_type in ('OPERATOR') and
1599 l_lefthand_rec.token in ('TIMES','DIVIDE','PLUS','MINUS') then
1600 error_infix;
1601 end if;
1602 peek(l_operator_rec);
1603 if l_operator_rec.source_type = 'EMPTY' then
1604 push(l_formula_rec);
1605 elsif l_operator_rec.source_type = 'OPERATOR' and
1606 l_operator_rec.token = 'LEFTPAREN' then
1607 push(l_formula_rec);
1608 elsif l_operator_rec.source_type = 'OPERATOR' and
1609 l_operator_rec.token in ('TIMES','DIVIDE') then
1610 add_postfix(l_operator_rec);
1611 pop(l_operator_rec);
1612 peek(l_operator_rec);
1613 if l_operator_rec.source_type = 'EMPTY' then
1614 push(l_formula_rec);
1615 elsif l_operator_rec.source_type = 'OPERATOR' and
1616 l_operator_rec.token = 'LEFTPAREN' then
1617 push(l_formula_rec);
1618 elsif l_operator_rec.token in ('PLUS','MINUS') then
1619 add_postfix(l_operator_rec);
1620 pop(l_operator_rec);
1621 push(l_formula_rec);
1622 end if;
1623 end if;
1624 END IF;
1625 l_lefthand_rec := l_formula_rec;
1626 end loop;
1627
1628 -- check the last token here
1629 --
1630 if l_lefthand_rec.source_type = 'OPERATOR' AND l_lefthand_rec.token <> 'RIGHTPAREN' THEN
1631 error_infix;
1632 end if;
1633
1634 loop
1635 pop(l_operator_rec);
1636 exit when l_operator_rec.source_type = 'EMPTY';
1637 if l_operator_rec.source_type = 'OPERATOR' and
1638 l_operator_rec.token = 'LEFTPAREN' then
1639 error_infix;
1640 else
1641 add_postfix(l_operator_rec);
1642 end if;
1643 end loop;
1644 if l_paren_count <> 0 then
1645 error_infix;
1646 end if;
1647 end transform_formula;
1648
1649 PROCEDURE VALIDATE_FORMULA(
1650 p_api_version_number IN NUMBER,
1651 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1652 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1653 x_return_status OUT NOCOPY VARCHAR2,
1654 x_msg_count OUT NOCOPY NUMBER,
1655 x_msg_data OUT NOCOPY VARCHAR2,
1656 p_metric_id IN NUMBER,
1657 p_object_version_number IN NUMBER
1658 )
1659 IS
1660 cursor c_get_formula(l_metric_id number)
1661 return stack_rec_type is
1662 SELECT
1663 decode(source_type, 'METRIC', m.metrics_name,
1664 'CATEGORY', c.category_name||delim.meaning||
1665 decode(source_sub_id, null,
1666 decode(use_sub_id_flag,'Y',excsubcat.meaning,incsubcat.meaning),
1667 sc.category_name), 'OPERATOR', opers.meaning,
1668 to_char(source_value)) source_name,
1669 f.TOKEN, f.sequence ,
1670 f.object_version_number ,
1671 f.metric_formula_id,
1672 f.notation_type ,
1673 f.source_type ,
1674 f.use_sub_id_flag ,
1675 f.source_value ,
1676 f.source_id,
1677 f.source_sub_id
1678 FROM ams_metric_formulas f, ams_metrics_vl m, ams_lookups opers,
1679 ams_categories_vl c, ams_categories_vl sc, ams_lookups delim,
1680 ams_lookups incsubcat, ams_lookups excsubcat
1681 WHERE f.metric_id = l_metric_id
1682 AND f.source_id = m.metric_id(+)
1683 AND f.source_id = c.category_id(+)
1684 AND c.arc_category_created_for(+) = 'METR'
1685 AND f.source_sub_id = sc.category_id(+)
1686 AND sc.arc_category_created_for(+) = 'METR'
1687 AND f.token = opers.lookup_code(+)
1691 AND delim.lookup_code = 'COLON_COLON'
1688 AND opers.lookup_type(+) = 'AMS_METRIC_OPERAND_TYPE'
1689 AND f.notation_type = 'INFIX'
1690 AND delim.lookup_type = 'AMS_METRIC_STRINGS'
1692 AND incsubcat.lookup_type = 'AMS_METRIC_STRINGS'
1693 AND incsubcat.lookup_code = 'INC_SUB_CAT'
1694 AND excsubcat.lookup_type = 'AMS_METRIC_STRINGS'
1695 AND excsubcat.lookup_code = 'EXC_SUB_CAT'
1696 ORDER BY f.sequence ASC ;
1697
1698 l_formula_str ams_metrics_all_b.formula%type;
1699 l_return_status varchar2(1);
1700 l_msg_count number;
1701 l_msg_data varchar2(4000);
1702
1703 l_parentheses_count number := 0;
1704 l_infix_rec stack_rec_type;
1705 l_valid_msg varchar2(200);
1706 BEGIN
1707 -- Debug Message
1708 IF (AMS_DEBUG_HIGH_ON) THEN
1709 Ams_Utility_Pvt.debug_message('PRIVATE API: VALIDATE_FORMULA');
1710 END IF;
1711 -- Initialize message list if p_init_msg_list is set to TRUE.
1712 IF FND_API.to_Boolean( p_init_msg_list )
1713 THEN
1714 FND_MSG_PUB.initialize;
1715 END IF;
1716
1717 -- Initialize API return status to SUCCESS
1718 x_return_status := FND_API.G_RET_STS_SUCCESS;
1719
1720 g_infix_tbl.delete;
1721 g_current_sequence := null;
1722 open c_get_formula(p_metric_id);
1723 LOOP
1724 fetch c_get_formula into l_infix_rec;
1725 exit when c_get_formula%NOTFOUND;
1726 g_infix_tbl(g_infix_tbl.count+1) := l_infix_rec;
1727 end loop;
1728 close c_get_formula;
1729 g_infix_formula := '';
1730 if g_infix_tbl.count > 0 then
1731 g_valid_formula := true;
1732 g_infix_index := g_infix_tbl.first;
1733 reset_postfix;
1734 transform_formula;
1735 end if;
1736
1737 if not g_valid_formula then
1738
1739 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1740 FND_MESSAGE.set_name('AMS', 'AMS_METR_INVALID_FORMULA_DEF');
1741 FND_MESSAGE.set_token('FORMULA', g_infix_formula);
1742 FND_MESSAGE.set_token('SEQUENCE', g_error_sequence);
1743 FND_MSG_PUB.add;
1744 END IF;
1745
1746 x_msg_data := g_infix_formula;
1747 x_return_status := FND_API.g_ret_sts_error;
1748
1749
1750 RAISE FND_API.G_EXC_ERROR;
1751 else
1752
1753 ams_metric_pvt_w.update_metric(
1754 p_api_version => 1,
1755 p_init_msg_list => FND_API.G_FALSE,
1756 p_commit => FND_API.G_FALSE,
1757 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1758 x_return_status => l_return_status,
1759 x_msg_count => l_msg_count,
1760 x_msg_data => l_msg_data,
1761 p7_a0 => p_metric_id,
1762 p7_a6 => p_object_version_number,
1763 p7_a25 => g_infix_formula
1764 );
1765
1766
1767
1768 delete from ams_metric_formulas
1769 where metric_id = p_metric_id
1770 and notation_type = 'POSTFIX';
1771
1772 if g_source_type_tbl.count > 0 then
1773 forall l_index in g_source_type_tbl.first..g_source_type_tbl.last
1774 insert into ams_metric_formulas
1775 (METRIC_FORMULA_ID ,
1776 LAST_UPDATE_DATE ,
1777 LAST_UPDATED_BY ,
1778 CREATION_DATE ,
1779 CREATED_BY ,
1780 LAST_UPDATE_LOGIN ,
1781 OBJECT_VERSION_NUMBER ,
1782 METRIC_ID ,
1783 SOURCE_TYPE ,
1784 SOURCE_ID ,
1785 TOKEN ,
1786 SEQUENCE ,
1787 NOTATION_TYPE ,
1788 SOURCE_VALUE ,
1789 SOURCE_SUB_ID ,
1790 USE_SUB_ID_FLAG )
1791 values
1792 (AMS_METRIC_FORMULAS_S.nextval,
1793 sysdate,
1794 0,
1795 sysdate,
1796 0,
1797 0,
1798 1,
1799 p_metric_id,
1800 g_source_type_tbl(l_index),
1801 g_source_id_tbl(l_index),
1802 g_token_tbl(l_index) ,
1803 g_index_tbl(l_index) ,
1804 'POSTFIX',
1805 g_source_value_tbl(l_index) ,
1806 g_source_sub_id_tbl(l_index) ,
1807 g_use_sub_id_flag_tbl(l_index) );
1808 end if;
1809
1810 end if;
1811
1812 -- Standard check for p_commit
1813 IF FND_API.to_Boolean( p_commit )
1814 THEN
1815 COMMIT WORK;
1816 END IF;
1817
1818 -- Debug Message
1819 IF (AMS_DEBUG_HIGH_ON) THEN
1820 Ams_Utility_Pvt.debug_message('PRIVATE API: VALIDATE_FORMULA');
1821 END IF;
1822
1823
1824 -- Standard call to get message count and if count is 1, get message info.
1825 FND_MSG_PUB.Count_And_Get
1826 (p_count => x_msg_count,
1827 p_data => x_msg_data
1828 );
1829
1830 EXCEPTION
1831
1832
1833 WHEN FND_API.G_EXC_ERROR THEN
1834 -- ROLLBACK TO VALIDATE_metric_formula_sp;
1835 x_return_status := FND_API.G_RET_STS_ERROR;
1836 -- Standard call to get message count and if count=1, get the message
1837 FND_MSG_PUB.Count_And_Get (
1838 p_encoded => FND_API.G_FALSE,
1839 p_count => x_msg_count,
1840 p_data => x_msg_data
1841 );
1842 END VALIDATE_FORMULA;
1843
1844 END Ams_metric_formula_Pvt;