[Home] [Help]
PACKAGE BODY: APPS.AMS_FORMULA_PVT
Source
1 PACKAGE BODY AMS_FORMULA_PVT AS
2 /* $Header: amsvfmlb.pls 115.8 2002/12/05 19:53:43 feliu ship $*/
3 -- Start of Comments
4 --
5 -- NAME
6 -- AMS_FORMULA_PVT
7 --
8 -- PURPOSE
9 -- This Package provides procedures to allow Insertion, Deletion,
10 -- Update and Locking of Marketing On-Line formulas and formula entries.
11 --
12 -- This Package also stores the seeded Functions which can be executed as
13 -- part of a formula entry.
14 --
15 -- This Package also provides functions to execute a formula.
16 --
17 -- Procedures:
18 --
19 -- Create_Formula.
20 -- Update_Formula.
21 -- Delete_Formula.
22 -- Lock_Formula.
23 -- Default_Formula.
24 -- Check_Req_Formula_Items.
25 -- Execute_Formula.
26 -- Perform_Computation.
27
28 -- Create_Formula_Entry.
29 -- Update_Formula_Entry.
30 -- Delete_Formula_Entry.
31 -- Lock_Formula_Entry.
32
33 -- NOTES
34 --
35 --
36 -- HISTORY
37 -- 31-May-2000 tdonohoe created.
38 -- 21-Jun-2000 tdonohoe update perform_computation to put a message on the stack when
39 -- an invalid operator is specified.
40 --
41 -- End of Comments
42 --
43 -- Global variables and constants.
44
45 -- Name of the current package.
46 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMS_FORMULA_PVT';
47
48 G_DEBUG_FLAG VARCHAR2(1) := 'N';
49
50 -- Start of comments
51 -- NAME
52 -- Perform_Computation
53 --
54 --
55 -- PURPOSE
56 -- This Function will take two values and an operator and perform a compuation.
57 -- The result of the compuation is returned.
58 --
59 -- NOTES
60 -- This Function supports PLUS,MINUS,DIVIDE,MULTIPY,PERCENT.
61
62 -- HISTORY
63 -- 15/Jun/2000 tdonohoe Created.
64 --
65 -- End of comments
66
67 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
68
69 PROCEDURE Perform_Computation(p_left_value IN NUMBER,
70 p_right_value IN NUMBER,
71 p_operator IN VARCHAR2,
72 x_return_status OUT NOCOPY VARCHAR2,
73 x_result OUT NOCOPY NUMBER)
74 IS
75
76 L_API_VERSION CONSTANT NUMBER := 1.0;
77 L_API_NAME CONSTANT VARCHAR2(30) := 'PERFORM_COMPUTATION';
78 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
79
80 l_result NUMBER := 0;
81
82 BEGIN
83
84 --
85 -- Initialize API return status to success.
86 --
87 x_return_status := FND_API.G_RET_STS_SUCCESS;
88
89
90 IF(p_operator = 'PLUS')THEN
91 l_result := (p_left_value + p_right_value);
92
93 ELSIF(p_operator = 'MINUS')THEN
94 l_result := (p_left_value - p_right_value);
95
96
97 ELSIF(p_operator = 'MULTIPLY')THEN
98 l_result := (p_left_value * p_right_value);
99
100
101 ELSIF(p_operator = 'DIVIDE')THEN
102 l_result := (p_left_value / p_right_value);
103
104
105 ELSIF(p_operator = 'PERCENT')THEN
106 l_result := ((p_left_value /100)* p_right_value);
107
108 ELSE
109 FND_MESSAGE.set_name('AMS', 'AMS_FML_ENT_INVALID_OP');
110 FND_MSG_PUB.add;
111 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
112 END IF;
113
114 -------------------------------------------
115 --Assigning return value to OUT NOCOPY variable.--
116 -------------------------------------------
117 x_result := l_result;
118
119 EXCEPTION
120 WHEN FND_API.G_EXC_ERROR THEN
121 x_return_status := FND_API.G_RET_STS_ERROR;
122 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
123 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
124 END IF;
125
126 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
127 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
128 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
129 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
130 END IF;
131
132 WHEN OTHERS THEN
133 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
134 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
135 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
136 END IF;
137
138
139 END Perform_Computation;
140
141 -- Start of comments
142 -- NAME
143 -- Execute_Formula
144 --
145 --
146 -- PURPOSE
147 -- This Procedure will Execute a Formula by processing all its formula entries
148 -- and returning the result in the X_RESULT variable.
149
150 --
151 -- NOTES
152 -- A Formula can have 1..N Formula Entries.
153
154 -- A Formula can have three sources which is stored in FORMULA_ENTRY_TYPE and taken from
155 -- the lookup AMS_FORMULA_ENT_TYPE.
156
157 -- 1. CONSTANT -> This value is User entered.
158 -- 2. CALCULATION -> The name of a PL\SQL function to be executed.
159 -- 3. METRIC_VALUE -> The name of a column in the AMS_ACT_METRICS_ALL table,.
160 -- the value in this column is used.
161
162 -- The result of each formula entry is grouped together by the value of ORDER_NUMBER
163 -- and calculated on this basis.
164
165 -- EXAMPLE.
166 --
167 -- FORMULA A B C D E F G
168 -- ORDER_NUMBER 1 1 1 2 3 3 4 -> (A+B+C)*(C)\(E+F)-(G)
169 -- OPERATOR + + * \ + (-) ->
170 --
171 -- HISTORY
172 -- 12-Jun-2000 tdonohoe Created.
173 --
174 -- End of comments
175
176 PROCEDURE Execute_Formula (
177 p_api_version IN NUMBER,
178 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
179 p_commit IN VARCHAR2 := FND_API.G_FALSE,
180 p_validation_level IN NUMBER := FND_API.G_Valid_Level_Full,
181
182 x_return_status OUT NOCOPY VARCHAR2,
183 x_msg_count OUT NOCOPY NUMBER,
184 x_msg_data OUT NOCOPY VARCHAR2,
185 x_result OUT NOCOPY NUMBER,
186
187 p_formula_id IN NUMBER,
188 p_hierarchy_id IN NUMBER,
189 p_parent_node_id IN NUMBER,
190 p_node_id IN NUMBER
191 )
192 IS
193
194 --
195 -- Standard API information constants.
196 --
197 L_API_VERSION CONSTANT NUMBER := 1.0;
198 L_API_NAME CONSTANT VARCHAR2(30) := 'EXECUTE_FORMULA';
199 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
200
201 ---------------------------------------------------------------------------------------
202 --This variable indicates the success or failure of the perform_computation call --
203 ---------------------------------------------------------------------------------------
204 l_return_status VARCHAR2(1);
205
206 ---------------------------------------------------------------------------------------
207 --This record type is used the result for a formula entry --
208 ---------------------------------------------------------------------------------------
209 Type Formula_Entry_Result is record
210 (formula_entry_id NUMBER,
211 result NUMBER,
212 formula_entry_operator VARCHAR2(30),
213 order_number NUMBER);
214
215 ---------------------------------------------------------------------------------------
216 --This table stores the set of formula entries for a formula --
217 ---------------------------------------------------------------------------------------
218 TYPE Formula_Entry_Results IS TABLE OF Formula_Entry_Result INDEX BY binary_integer;
219
220 ---------------------------------------------------------------------------------------
221 --This program variable stores the set of formula entries for a formula --
222 ---------------------------------------------------------------------------------------
223 l_formula_entry_results Formula_Entry_Results;
224
225 ---------------------------------------------------------------------------------------
226 --This variable stores the number of records in the Formula_Entry_Results table --
227 ---------------------------------------------------------------------------------------
228 l_rec_counter binary_integer :=1;
229
230 ---------------------------------------------------------------------------------------
231 --These variables store the result and the level of the entries completely processed --
232 ---------------------------------------------------------------------------------------
233 l_processed_value NUMBER;
234 l_processed_level NUMBER;
235
236 ---------------------------------------------------------------------------------------
237 --These variables store the result and the level of the current level --
238 ---------------------------------------------------------------------------------------
239 l_current_value NUMBER;
240 l_current_level NUMBER;
241 l_current_flag BOOLEAN := FALSE;
242 l_current_operator VARCHAR2(30);
243
244
245 ---------------------------------------------------------------------------------------
246 --This variable stores the result of the most recent call to perform computation --
247 ---------------------------------------------------------------------------------------
248 l_computation_result NUMBER := 0;
249
250 ---------------------------------------------------------------------------------------
251 --This Cursor queries the activity_metric_id field from the ams_act_metric_formulas --
252 --table, this is used to query the required value from the ams_act_metrics_all table --
253 ---------------------------------------------------------------------------------------
254 CURSOR C_Formula_Dets(p_formula_id IN NUMBER) IS
255 SELECT activity_metric_id,parent_formula_id
256 FROM ams_act_metric_formulas
257 WHERE formula_id = p_formula_id;
258
259 ---------------------------------------------------------------------------------------
260 --This variable stores the value of the cursor c_formula_dets. --
261 ---------------------------------------------------------------------------------------
262 l_activity_metric_id NUMBER;
263
264 ---------------------------------------------------------------------------------------
265 --This variable stores the value of the parent_formula_id. --
266 ---------------------------------------------------------------------------------------
267 l_parent_formula_id NUMBER;
268
269 ---------------------------------------------------------------------------------------
270 --This Cursor queries all formula entries for a specified FORMULA_ID --
271 --The Entries are processed in Ascending ORDER_NUMBER and FORMULA_ENTRY_ID. --
272 ---------------------------------------------------------------------------------------
273 CURSOR C_Formula_Entry_Dets IS
274 SELECT *
275 FROM ams_act_metric_form_ent
276 WHERE formula_id = p_formula_id
277 ORDER BY formula_entry_id,order_number;
278
279
280 ---------------------------------------------------------------------------------------
281 --This Variable stores the result of the cursor C_Formula_Entry_Dets. --
282 ---------------------------------------------------------------------------------------
283 l_formula_entry_dets C_Formula_Entry_Dets%ROWTYPE;
284
285
286 ---------------------------------------------------------------------------------------
287 --This Variable stores the SQL string to be executed natively. --
288 ---------------------------------------------------------------------------------------
289 l_sql_stmt VARCHAR2(4000);
290
291 BEGIN
292 -- Initialize savepoint.
293 --
294
295 SAVEPOINT Execute_Formula_Pvt;
296
297 IF G_DEBUG THEN
298 AMS_Utility_PVT.Debug_Message(l_full_name||': start');
299 END IF;
300
301 --
302 -- Initialize message list if p_init_msg_list is set to TRUE.
303 --
304 IF FND_API.To_Boolean (p_init_msg_list) THEN
305 FND_MSG_PUB.Initialize;
306 END IF;
307
308 --
309 -- Standard check for API version compatibility.
310 --
311 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
312 p_api_version,
313 L_API_NAME,
314 G_PKG_NAME)
315 THEN
316 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
317 END IF;
318
319
320
321 --
322 -- Initialize API return status to success.
323 --
324 x_return_status := FND_API.G_RET_STS_SUCCESS;
325
326 --
327 -- Begin API Body.
328 --
329
330
331 OPEN C_Formula_dets(p_formula_id);
332 FETCH C_Formula_dets INTO l_activity_metric_id,l_parent_formula_id;
333
334 IF(C_FORMULA_DETS%NOTFOUND)THEN
335
336 CLOSE C_Formula_dets;
337 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
338 ELSE
339 CLOSE C_Formula_dets;
340 END IF;
341
342 --------------------------------------------------------------------
343 --Reading in the set of formula entries and performing dynamic --
344 --SQl if necessary to calculate the value for each entry --
345 --The results are stored into the l_formula_entry_results variable--
346 --------------------------------------------------------------------
347 OPEN C_Formula_Entry_Dets;
348
349 LOOP
350
351 FETCH C_Formula_Entry_Dets INTO l_formula_entry_dets;
352
353 EXIT WHEN C_Formula_Entry_Dets%NOTFOUND;
354
355 IF (l_formula_entry_dets.formula_entry_type = 'CALCULATION') THEN
356
357 l_sql_stmt := 'SELECT '||l_formula_entry_dets.formula_entry_value||'(:p1,:p2,:p3,:p4,:p5) FROM DUAL';
358
359
360 EXECUTE IMMEDIATE l_sql_stmt INTO l_formula_entry_results(l_rec_counter).result USING p_hierarchy_id,p_parent_node_id,p_node_id,l_activity_metric_id,l_parent_formula_id;
361 l_sql_stmt := NULL;
362
363 ELSIF(l_formula_entry_dets.formula_entry_type = 'METRIC') THEN
364
365
366 l_sql_stmt := 'SELECT '||l_formula_entry_dets.formula_entry_value||' FROM AMS_ACT_METRICS_ALL WHERE ACTIVITY_METRIC_ID = :P1';
367
368 EXECUTE IMMEDIATE l_sql_stmt INTO l_formula_entry_results(l_rec_counter).result USING l_activity_metric_id;
369
370 l_sql_stmt := NULL;
371
372 ELSIF(l_formula_entry_dets.formula_entry_type = 'CONSTANT') THEN
373
374 l_formula_entry_results(l_rec_counter).result := l_formula_entry_dets.formula_entry_value;
375 ELSE
376
377 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
378 END IF;
379
380 l_formula_entry_results(l_rec_counter).formula_entry_id := l_formula_entry_dets.formula_entry_id;
381 l_formula_entry_results(l_rec_counter).formula_entry_operator := l_formula_entry_dets.formula_entry_operator;
382 l_formula_entry_results(l_rec_counter).order_number := l_formula_entry_dets.order_number;
383
384 -----------------------------------------------------------------
385 --Initializing the record variable for the next loop iteration.--
386 -----------------------------------------------------------------
387 l_formula_entry_dets := NULL;
388
389 ------------------------------------
390 --Incrementing the record counter.--
391 ------------------------------------
392 l_rec_counter := l_rec_counter + 1;
393
394 END LOOP;
395
396 CLOSE C_Formula_Entry_Dets;
397
398 --------------------------------------------------------------------------------------
399 --End of Calculating Formula Entry values --
400 --------------------------------------------------------------------------------------
401
402 --------------------------------------------------------------------------------------
403 --Traversing Formula Entry values and performing the necessary OPERATOR computations--
404 --------------------------------------------------------------------------------------
405 FOR i IN l_formula_entry_results.FIRST .. l_formula_entry_results.LAST LOOP
406
407 ----------------------------------------------------------------------------------------------
408 --If this is the first entry result then always save this to the l_processed_value variable.--
412
409 --Updating the l_processed_level variable. --
410 ----------------------------------------------------------------------------------------------
411 IF(i = l_formula_entry_results.FIRST) THEN
413 l_processed_value := l_formula_entry_results(i).result;
414 l_processed_level := l_formula_entry_results(i).order_number;
415 l_current_flag := FALSE;
416
417
418 -----------------------------------------------------------------------------------
419 --The Current record has the same level as the processed result. --
420 -----------------------------------------------------------------------------------
421 ELSIF(l_formula_entry_results(i).order_number = l_processed_level)THEN
422 perform_computation(p_left_value => l_processed_value,
423 p_right_value => l_formula_entry_results(i).result,
424 p_operator => l_formula_entry_results(i).formula_entry_operator,
425 x_return_status => l_return_status,
426 x_result => l_computation_result);
427
428 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)THEN
429 l_processed_value := l_computation_result;
430 ELSE
431 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
432 END IF;
433 -----------------------------------------------------------------------------------
434 --The Current record has a different level to the processed result. --
435 -----------------------------------------------------------------------------------
436 ELSE
437 --------------------------------------------------------------------------------
438 --The First Node of a new level has been detected. --
439 --------------------------------------------------------------------------------
440 IF(NOT(l_current_flag))THEN
441 l_current_flag := TRUE;
442 l_current_operator := l_formula_entry_results(i).formula_entry_operator;
443 l_current_value := l_formula_entry_results(i).result;
444 l_current_level := l_formula_entry_results(i).order_number;
445 --------------------------------------------------------------------------------
446 --The Current record has the same level as the current_level_value --
447 --------------------------------------------------------------------------------
448 ELSIF(l_formula_entry_results(i).order_number = l_current_level)THEN
449
450 perform_computation(p_left_value => l_current_value,
451 p_right_value => l_formula_entry_results(i).result,
452 p_operator => l_formula_entry_results(i).formula_entry_operator,
453 x_return_status => l_return_status,
454 x_result => l_computation_result);
455
456 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)THEN
457 l_current_value := l_computation_result;
458 ELSE
459 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
460 END IF;
461 -------------------------------------------------------------------------------------
462 --The Current record has a different level as the current_level_value. --
463 --Perform the computation between the processed_level_value and current_level_value--
464 --Reset the Current Level program variables to the current record. --
465 -------------------------------------------------------------------------------------
466 ELSE
467 perform_computation(p_left_value => l_processed_value,
468 p_right_value => l_current_value,
469 p_operator => l_current_operator,
470 x_return_status => l_return_status,
471 x_result => l_computation_result);
472
473 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)THEN
474 l_processed_value := l_computation_result;
475 l_processed_level := l_current_level;
476 ELSE
477 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
478 END IF;
479
480 l_current_flag := TRUE;
481 l_current_operator := l_formula_entry_results(i).formula_entry_operator;
482 l_current_value := l_formula_entry_results(i).result;
483 l_current_level := l_formula_entry_results(i).order_number;
484
485 END IF;
486 END IF;
487
488
489 END LOOP;
490
491
492 IF(l_current_flag)THEN
493 perform_computation(p_left_value => l_processed_value,
494 p_right_value => l_current_value,
495 p_operator => l_current_operator,
496 x_return_status => l_return_status,
497 x_result => l_computation_result);
498
499 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)THEN
500 l_processed_value := l_computation_result;
501 l_processed_level := l_current_level;
502 ELSE
503 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
504 END IF;
505 END IF;
506
507 -------------------------------------
508 --Assigning result to OUT NOCOPY variable.--
509 -------------------------------------
510 x_result := l_processed_value;
511
512
513 EXCEPTION
514 WHEN FND_API.G_EXC_ERROR THEN
515
516
517 ROLLBACK TO Execute_Formula_Pvt;
518 x_return_status := FND_API.G_RET_STS_ERROR;
519 FND_MSG_PUB.Count_And_Get (
520 p_count => x_msg_count,
521 p_data => x_msg_data
522 );
523 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
524
525
526
527 ROLLBACK TO Execute_Formula_Pvt;
528 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
529 FND_MSG_PUB.Count_And_Get (
530 p_count => x_msg_count,
534
531 p_data => x_msg_data
532 );
533 WHEN OTHERS THEN
535
536 ROLLBACK TO Execute_Formula_Pvt;
537 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
538 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
539 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
540 END IF;
541 FND_MSG_PUB.Count_And_Get (
542 p_count => x_msg_count,
543 p_data => x_msg_data
544 );
545
546 END Execute_Formula ;
547
548
549
550
551 -- Start of comments
552 -- NAME
553 -- Default_Formula
554 --
555 --
556 -- PURPOSE
557 -- Defaults the Activity Metric Formula.
558 --
559 -- NOTES
560 --
561 -- HISTORY
562 -- 31-May-2000 tdonohoe Created.
563 --
564 -- End of comments
565
566 PROCEDURE Default_Formula(
567 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
568 p_formula_rec IN ams_formula_rec_type,
569 p_validation_mode IN VARCHAR2 ,
570 x_complete_rec OUT NOCOPY ams_formula_rec_type,
571 x_return_status OUT NOCOPY VARCHAR2,
572 x_msg_count OUT NOCOPY NUMBER,
573 x_msg_data OUT NOCOPY VARCHAR2
574 )
575 IS
576
577 BEGIN
578 --
579 -- Initialize message list if p_init_msg_list is set to TRUE.
580 --
581 IF FND_API.To_Boolean (p_init_msg_list) THEN
582 FND_MSG_PUB.Initialize;
583 END IF;
584
585 --
586 -- Initialize API return status to success.
587 --
588 x_return_status := FND_API.G_RET_STS_SUCCESS;
589
590 x_complete_rec := p_formula_rec;
591
592 -- Insert Mode
593 IF ((p_validation_mode = JTF_PLSQL_API.g_create) OR (p_validation_mode = JTF_PLSQL_API.g_update)) THEN
594 NULL;
595 END IF;
596
597 END Default_Formula ;
598
599
600 -- Start of comments
601 -- NAME
602 -- Default_Formula_Entry
603 --
604 --
605 -- PURPOSE
606 -- Defaults the Activity Metric Formula Entry.
607 --
608 -- NOTES
609 --
610 -- HISTORY
611 -- 01-Jun-2000 tdonohoe Created.
612 --
613 -- End of comments
614
615 PROCEDURE Default_Formula_Entry(
616 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
617 p_formula_entry_rec IN ams_formula_entry_rec_type,
618 p_validation_mode IN VARCHAR2 ,
619 x_complete_entry_rec OUT NOCOPY ams_formula_entry_rec_type,
620 x_return_status OUT NOCOPY VARCHAR2,
621 x_msg_count OUT NOCOPY NUMBER,
622 x_msg_data OUT NOCOPY VARCHAR2
623 )
624 IS
625
626 BEGIN
627 --
628 -- Initialize message list if p_init_msg_list is set to TRUE.
629 --
630 IF FND_API.To_Boolean (p_init_msg_list) THEN
631 FND_MSG_PUB.Initialize;
632 END IF;
633
634 --
635 -- Initialize API return status to success.
636 --
637 x_return_status := FND_API.G_RET_STS_SUCCESS;
638
639 x_complete_entry_rec := p_formula_entry_rec;
640
641 -- Insert Mode
642 IF ((p_validation_mode = JTF_PLSQL_API.g_create) OR (p_validation_mode = JTF_PLSQL_API.g_update)) THEN
643
644 NULL;
645 END IF;
646
647 END Default_Formula_Entry ;
648
649
650
651 -- Start of comments.
652 --
653 -- NAME
654 -- Check_Req_Formula_Items
655 --
656 -- PURPOSE
657 -- Validate required activity metric formula items.
658 --
659 -- NOTES
660 --
661 -- HISTORY
662 -- 31-May-2000 tdonohoe Created.
663 --
664 -- End of comments.
665
666 PROCEDURE Check_Req_Formula_Items (
667 p_formula_rec IN ams_formula_rec_type,
668 x_return_status OUT NOCOPY VARCHAR2
669 )
670 IS
671 BEGIN
672 -- Initialize return status to success.
673 x_return_status := FND_API.G_RET_STS_SUCCESS;
674
675
676 --ACTIVITY_METRIC_ID
677
678 IF p_formula_rec.activity_metric_id IS NULL
679 THEN
680 -- missing required fields
681 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
682 THEN -- MMSG
683 FND_MESSAGE.Set_Name('AMS', 'AMS_FML_MISSING_ACT_METRIC_ID');
684 FND_MSG_PUB.Add;
685 END IF;
686
687 x_return_status := FND_API.G_RET_STS_ERROR;
688
689 -- If any error happens abort API.
690 RETURN;
691 END IF;
692
693 --LEVEL_DEPTH
694
695 IF p_formula_rec.level_depth IS NULL
696 THEN
697 -- missing required fields
698 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
699 THEN -- MMSG
700 FND_MESSAGE.Set_Name('AMS', 'AMS_FML_MISSING_LEVEL_DEPTH');
701 FND_MSG_PUB.Add;
702 END IF;
703
704 x_return_status := FND_API.G_RET_STS_ERROR;
705
706 -- If any error happens abort API.
707 RETURN;
708 END IF;
709
710
711 EXCEPTION
712 WHEN OTHERS THEN
713 RAISE;
714 END Check_Req_Formula_Items;
715
716
717 -- Start of comments.
718 --
719 -- NAME
720 -- Check_Req_Formula_Entry_Items
721 --
722 -- PURPOSE
723 -- Validate required activity metric formula entry items.
724 --
725 -- NOTES
726 --
727 -- HISTORY
728 -- 01-Jun-2000 tdonohoe Created.
729 --
733 p_formula_entry_rec IN ams_formula_entry_rec_type,
730 -- End of comments.
731
732 PROCEDURE Check_Req_Formula_Entry_Items (
734 x_return_status OUT NOCOPY VARCHAR2
735 )
736 IS
737 BEGIN
738 -- Initialize return status to success.
739 x_return_status := FND_API.G_RET_STS_SUCCESS;
740
741
742
743 --FORMULA_ID
744
745 IF p_formula_entry_rec.formula_id IS NULL
746 THEN
747 -- missing required fields
748 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
749 THEN -- MMSG
750 FND_MESSAGE.Set_Name('AMS', 'AMS_FML_MISSING_FORMULA_ID');
751 FND_MSG_PUB.Add;
752 END IF;
753
754 x_return_status := FND_API.G_RET_STS_ERROR;
755
756 -- If any error happens abort API.
757 RETURN;
758 END IF;
759
760 --ORDER_NUMBER
761 IF p_formula_entry_rec.order_number IS NULL
762 THEN
763 -- missing required fields
764 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
765 THEN -- MMSG
766 FND_MESSAGE.Set_Name('AMS', 'AMS_FML_MISSING_ORDER_NUM');
767 FND_MSG_PUB.Add;
768 END IF;
769
770 x_return_status := FND_API.G_RET_STS_ERROR;
771
772 -- If any error happens abort API.
773 RETURN;
774 END IF;
775
776 -- FORMULA_ENTRY_TYPE
777 IF p_formula_entry_rec.formula_entry_type IS NULL
778 THEN
779 -- missing required fields
780 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
781 THEN -- MMSG
782 FND_MESSAGE.Set_Name('AMS', 'AMS_FML_MISSING_ENT_TYPE');
783 FND_MSG_PUB.Add;
784 END IF;
785
786 x_return_status := FND_API.G_RET_STS_ERROR;
787
788 -- If any error happens abort API.
789 RETURN;
790 END IF;
791
792 -- OBJECT_VERSION_NUMBER
793 IF p_formula_entry_rec.object_version_number IS NULL
794 THEN
795 -- missing required fields
796 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
797 THEN -- MMSG
798 FND_MESSAGE.Set_Name('AMS', 'AMS_FML_MISSING_OBJ_NUM');
799 FND_MSG_PUB.Add;
800 END IF;
801
802 x_return_status := FND_API.G_RET_STS_ERROR;
803
804 -- If any error happens abort API.
805 RETURN;
806 END IF;
807
808 EXCEPTION
809 WHEN OTHERS THEN
810 RAISE;
811 END Check_Req_Formula_Entry_Items;
812
813
814
815 --
816 -- Start of comments.
817 --
818 -- NAME
819 -- Check_Formula_UK_Items
820 --
821 -- PURPOSE
822 -- Perform Uniqueness check for Activity metric formulas.
823 --
824 -- NOTES
825 --
826 -- HISTORY
827 -- 31-May-2000 tdonohoe Created.
828 --
829 -- End of comments.
830
831
832 PROCEDURE Check_Formula_UK_Items(
833 p_formula_rec IN ams_formula_rec_type,
834 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
835 x_return_status OUT NOCOPY VARCHAR2
836 )
837 IS
838
839 l_formula_count number;
840
841 CURSOR c_formula_type IS
842 SELECT COUNT(*)
843 FROM ams_act_metric_formulas
844 WHERE formula_type = p_formula_rec.formula_type
845 AND activity_metric_id = p_formula_rec.activity_metric_id
846 AND level_depth = p_formula_rec.level_depth
847 AND formula_id <> p_formula_rec.formula_id;
848
849
850 BEGIN
851
852 x_return_status := FND_API.g_ret_sts_success;
853
854 OPEN c_formula_type;
855 FETCH c_formula_type INTO l_formula_count;
856 CLOSE c_formula_type;
857
858 IF (l_formula_count > 0) THEN
859 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
860
861 FND_MESSAGE.set_name('AMS', 'AMS_FML_MAX_LEVEL');
862 FND_MSG_PUB.add;
863 END IF;
864 x_return_status := FND_API.g_ret_sts_error;
865 RETURN;
866 END IF;
867
868 END Check_Formula_Uk_Items;
869
870 --
871 -- Start of comments.
872 --
873 -- NAME
874 -- Check_Formula_Entry_UK_Items
875 --
876 -- PURPOSE
877 -- Perform Uniqueness check for Activity metric formula entries.
878 --
879 -- NOTES
880 --
881 -- HISTORY
882 -- 01-Jun-2000 tdonohoe Created.
883 --
884 -- End of comments.
885 PROCEDURE Check_Formula_Entry_UK_Items(
886 p_formula_entry_rec IN ams_formula_entry_rec_type,
887 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
888 x_return_status OUT NOCOPY VARCHAR2
889 )
890 IS
891
892 l_formula_entry_count number;
893
894 CURSOR c_formula_entry_type IS
895 SELECT COUNT(*)
896 FROM ams_act_metric_form_ent
897 WHERE formula_id = p_formula_entry_rec.formula_id
898 AND order_number = p_formula_entry_rec.order_number
899 AND formula_entry_id <> p_formula_entry_rec.formula_entry_id;
900
901
902 BEGIN
903
904 x_return_status := FND_API.g_ret_sts_success;
905
906 /*
907 OPEN c_formula_entry_type;
908 FETCH c_formula_entry_type INTO l_formula_entry_count;
909 CLOSE c_formula_entry_type;
910
911 IF (l_formula_entry_count > 0) THEN
912 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
913
914 FND_MESSAGE.set_name('AMS', 'AMS_FML_ENT_DUP_ORDNUM');
918 RETURN;
915 FND_MSG_PUB.add;
916 END IF;
917 x_return_status := FND_API.g_ret_sts_error;
919 END IF;
920 */
921 END Check_Formula_Entry_Uk_Items;
922
923
924
925
926 --
927 -- Start of comments.
928 --
929 -- NAME
930 -- Check_Formula_Items
931 --
932 -- PURPOSE
933 -- Perform item level validation for activity metric formulas.
934 --
935 -- NOTES
936 --
937 -- HISTORY
938 -- 31-May-2000 tdonohoe Created.
939 --
940 -- End of comments.
941
942 PROCEDURE Check_Formula_Items (
943 p_formula_rec IN ams_formula_rec_type,
944 x_return_status OUT NOCOPY VARCHAR2
945 )
946 IS
947 l_item_name VARCHAR2(30); -- Used to standardize error messages.
948 l_formula_rec ams_formula_rec_type := p_formula_rec;
949 l_return_status VARCHAR2(1);
950
951 BEGIN
952 -- Initialize return status to success.
953 x_return_status := FND_API.G_RET_STS_SUCCESS;
954
955 --FORMULA_TYPE
956
957 IF l_formula_rec.formula_type <> FND_API.G_MISS_CHAR THEN
958
959
960 IF AMS_Utility_PVT.check_lookup_exists(p_lookup_type => 'AMS_FORMULA_TYPE',
961 p_lookup_code => l_formula_rec.formula_type) = FND_API.g_false THEN
962
963 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
964 FND_MESSAGE.set_name('AMS', 'AMS_FML_INVALID_TYPE');
965 FND_MSG_PUB.add;
966 END IF;
967
968 x_return_status := FND_API.g_ret_sts_error;
969 RETURN;
970 END IF;
971 END IF;
972
973 EXCEPTION
974 WHEN OTHERS THEN
975 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
976 END Check_Formula_Items;
977
978
979 --
980 -- Start of comments.
981 --
982 -- NAME
983 -- Check_Formula_Entry_Items
984 --
985 -- PURPOSE
986 -- Perform item level validation for activity metric formula entries.
987 --
988 -- NOTES
989 --
990 -- HISTORY
991 -- 01-Jun-2000 tdonohoe Created.
992 --
993 -- End of comments.
994
995 PROCEDURE Check_Formula_Entry_Items (
996 p_formula_entry_rec IN ams_formula_entry_rec_type,
997 x_return_status OUT NOCOPY VARCHAR2
998 )
999 IS
1000 l_item_name VARCHAR2(30); -- Used to standardize error messages.
1001 l_formula_entry_rec ams_formula_entry_rec_type := p_formula_entry_rec;
1002 l_return_status VARCHAR2(1);
1003
1004
1005 BEGIN
1006
1007 -- Initialize return status to success.
1008 x_return_status := FND_API.G_RET_STS_SUCCESS;
1009
1010 --FORMULA_ENTRY_TYPE
1011
1012 IF l_formula_entry_rec.formula_entry_type <> FND_API.G_MISS_CHAR THEN
1013
1014
1015 IF AMS_Utility_PVT.check_lookup_exists(p_lookup_type => 'AMS_FORMULA_ENT_TYPE',
1016 p_lookup_code => l_formula_entry_rec.formula_entry_type) = FND_API.g_false THEN
1017
1018 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1019 FND_MESSAGE.set_name('AMS', 'AMS_FML_ENT_INVALID_TYPE');
1020 FND_MSG_PUB.add;
1021 END IF;
1022
1023 x_return_status := FND_API.g_ret_sts_error;
1024 RETURN;
1025 END IF;
1026 END IF;
1027
1028 --AMS_FORMULA_OPERATORS
1029
1030 IF l_formula_entry_rec.formula_entry_operator IS NOT NULL AND l_formula_entry_rec.formula_entry_operator <> FND_API.G_MISS_CHAR THEN
1031
1032
1033 IF AMS_Utility_PVT.check_lookup_exists(p_lookup_type => 'AMS_FORMULA_OPERATOR',
1034 p_lookup_code => l_formula_entry_rec.formula_entry_operator) = FND_API.g_false THEN
1035
1036 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1037 FND_MESSAGE.set_name('AMS', 'AMS_FML_ENT_INVALID_OP');
1038 FND_MSG_PUB.add;
1039 END IF;
1040
1041 x_return_status := FND_API.g_ret_sts_error;
1042 RETURN;
1043 END IF;
1044 END IF;
1045
1046 EXCEPTION
1047 WHEN OTHERS THEN
1048 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1049 END Check_Formula_Entry_Items;
1050
1051
1052
1053 --
1054 -- Start of comments.
1055 --
1056 -- NAME
1057 -- Validate_Formula_Rec
1058 --
1059 -- PURPOSE
1060 -- Perform Record Level and Other business validations for activity metric formula table.
1061 --
1062 -- NOTES
1063 --
1064 -- HISTORY
1065 -- 31-May-2000 tdonohoe Created.
1066 --
1067 -- End of comments.
1068
1069 PROCEDURE Validate_Formula_rec(
1070 p_formula_rec IN ams_formula_rec_type,
1071 p_complete_formula_rec IN ams_formula_rec_type,
1072 x_return_status OUT NOCOPY VARCHAR2
1073 )
1074 IS
1075
1076 l_formula_rec ams_formula_rec_type := p_formula_rec;
1077 l_return_status VARCHAR2(1);
1078
1079
1080 BEGIN
1081
1082 x_return_status := FND_API.g_ret_sts_success;
1083
1084 IF (l_formula_rec.activity_metric_id <> FND_API.G_MISS_NUM) THEN
1085
1086 IF AMS_Utility_PVT.Check_FK_Exists (
1087 p_table_name => 'AMS_ACT_METRICS_ALL'
1088 ,p_pk_name => 'ACTIVITY_METRIC_ID'
1089 ,p_pk_value => l_formula_rec.activity_metric_id
1090 ,p_pk_data_type => NULL
1094 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1091 ,p_additional_where_clause => NULL
1092 ) = FND_API.G_FALSE
1093 THEN
1095 FND_MESSAGE.Set_Name ('AMS', 'AMS_FML_INVALID_ACT_METRIC');
1096 FND_MSG_PUB.Add;
1097 END IF;
1098
1099 x_return_status := FND_API.G_RET_STS_ERROR;
1100 RETURN;
1101 END IF;
1102 END IF;
1103
1104 EXCEPTION
1105 WHEN OTHERS THEN
1106 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1107
1108 END Validate_Formula_rec;
1109
1110
1111 --
1112 -- Start of comments.
1113 --
1114 -- NAME
1115 -- Validate_Form_ent_rec
1116 --
1117 -- PURPOSE
1118 -- Perform Record Level and Other business validations for activity metric formula table.
1119 --
1120 -- NOTES
1121 --
1122 -- HISTORY
1123 -- 01-Jun-2000 tdonohoe Created.
1124 --
1125 -- End of comments.
1126
1127 PROCEDURE Validate_Form_ent_rec(
1128 p_formula_entry_rec IN ams_formula_entry_rec_type,
1129 p_complete_formula_entry_rec IN ams_formula_entry_rec_type,
1130 x_return_status OUT NOCOPY VARCHAR2
1131 )
1132 IS
1133
1134 l_formula_entry_rec ams_formula_entry_rec_type := p_formula_entry_rec;
1135 l_return_status VARCHAR2(1);
1136
1137
1138 BEGIN
1139
1140 x_return_status := FND_API.g_ret_sts_success;
1141
1142 IF (l_formula_entry_rec.formula_id <> FND_API.G_MISS_NUM) THEN
1143
1144 IF AMS_Utility_PVT.Check_FK_Exists (
1145 p_table_name => 'AMS_ACT_METRIC_FORMULAS'
1146 ,p_pk_name => 'FORMULA_ID'
1147 ,p_pk_value => l_formula_entry_rec.formula_id
1148 ,p_pk_data_type => NULL
1149 ,p_additional_where_clause => NULL
1150 ) = FND_API.G_FALSE
1151 THEN
1152 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1153 FND_MESSAGE.Set_Name ('AMS', 'AMS_FML_INVALID_FORMULA_ID');
1154 FND_MSG_PUB.Add;
1155 END IF;
1156
1157 x_return_status := FND_API.G_RET_STS_ERROR;
1158 RETURN;
1159 END IF;
1160 END IF;
1161
1162 EXCEPTION
1163 WHEN OTHERS THEN
1164 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1165
1166 END Validate_Form_ent_rec;
1167
1168
1169 --
1170 -- Start of comments.
1171 --
1172 -- NAME
1173 -- Validate_Formula_Items
1174 --
1175 -- PURPOSE
1176 -- Perform All Item level validation for Activity metric formulas.
1177 --
1178 -- NOTES
1179 --
1180 -- HISTORY
1181 -- 31-May-2000 tdonohoe Created.
1182 --
1183 -- End of comments.
1184
1185 PROCEDURE Validate_Formula_Items (
1186 p_formula_rec IN ams_formula_rec_type,
1187 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1188 x_return_status OUT NOCOPY VARCHAR2
1189 )
1190 IS
1191 BEGIN
1192
1193
1194
1195 Check_Req_Formula_Items(
1196 p_formula_rec => p_formula_rec,
1197 x_return_status => x_return_status
1198 );
1199 IF x_return_status <> FND_API.g_ret_sts_success THEN
1200 RETURN;
1201 END IF;
1202
1203
1204 Check_Formula_Uk_Items(
1205 p_formula_rec => p_formula_rec,
1206 p_validation_mode => p_validation_mode,
1207 x_return_status => x_return_status
1208 );
1209
1210 IF x_return_status <> FND_API.g_ret_sts_success THEN
1211 RETURN;
1212 END IF;
1213
1214
1215 Check_Formula_Items(
1216 p_formula_rec => p_formula_rec,
1217 x_return_status => x_return_status
1218 );
1219
1220 IF x_return_status <> FND_API.g_ret_sts_success THEN
1221 RETURN;
1222 END IF;
1223
1224 END Validate_Formula_Items;
1225
1226
1227 --
1228 -- Start of comments.
1229 --
1230 -- NAME
1231 -- Validate_Form_Ent_Items
1232 --
1233 -- PURPOSE
1234 -- Perform All Item level validation for Activity metric formula entries.
1235 --
1236 -- NOTES
1237 --
1238 -- HISTORY
1239 -- 01-Jun-2000 tdonohoe Created.
1240 --
1241 -- End of comments.
1242
1243 PROCEDURE Validate_Form_Ent_Items (
1244 p_formula_entry_rec IN ams_formula_entry_rec_type,
1245 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1246 x_return_status OUT NOCOPY VARCHAR2
1247 )
1248 IS
1249 BEGIN
1250
1251
1252
1253 Check_Req_Formula_Entry_Items(
1254 p_formula_entry_rec => p_formula_entry_rec,
1255 x_return_status => x_return_status
1256 );
1257 IF x_return_status <> FND_API.g_ret_sts_success THEN
1258 RETURN;
1259 END IF;
1260
1261
1262 Check_Formula_Entry_Uk_Items(
1263 p_formula_entry_rec => p_formula_entry_rec,
1264 p_validation_mode => p_validation_mode,
1265 x_return_status => x_return_status
1266 );
1267
1268 IF x_return_status <> FND_API.g_ret_sts_success THEN
1269 RETURN;
1270 END IF;
1271
1272
1273 Check_Formula_Entry_Items(
1274 p_formula_entry_rec => p_formula_entry_rec,
1275 x_return_status => x_return_status
1276 );
1277
1278 IF x_return_status <> FND_API.g_ret_sts_success THEN
1279 RETURN;
1280 END IF;
1281
1282 END Validate_Form_Ent_Items;
1286 -- NAME
1283
1284
1285 -- Start of comments
1287 -- Validate_Formula
1288 --
1289 -- PURPOSE
1290 -- Validation API for Activity metric formula table.
1291 --
1292
1293 -- NOTES
1294 --
1295 -- HISTORY
1296 -- 31-May-2000 tdonohoe Created.
1297
1298 --
1299 -- End of comments
1300 PROCEDURE Validate_Formula (
1301 p_api_version IN NUMBER,
1302 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1303 p_validation_level IN NUMBER := FND_API.G_Valid_Level_Full,
1304
1305 x_return_status OUT NOCOPY VARCHAR2,
1306 x_msg_count OUT NOCOPY NUMBER,
1307 x_msg_data OUT NOCOPY VARCHAR2,
1308
1309 p_formula_rec IN ams_formula_rec_type
1310 )
1311 IS
1312 L_API_VERSION CONSTANT NUMBER := 1.0;
1313 L_API_NAME CONSTANT VARCHAR2(30) := 'VALIDATE_FORMULA';
1314 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1315
1316
1317 l_return_status VARCHAR2(1);
1318
1319 BEGIN
1320 --
1321 -- Output debug message.
1322 --
1323 IF G_DEBUG THEN
1324 AMS_Utility_PVT.debug_message(l_full_name||': start');
1325 END IF;
1326
1327 --
1328 -- Initialize message list if p_init_msg_list is set to TRUE.
1329 --
1330 IF FND_API.To_Boolean (p_init_msg_list) THEN
1331 FND_MSG_PUB.Initialize;
1332 END IF;
1333
1334 --
1335 -- Standard check for API version compatibility.
1336 --
1337 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
1338 p_api_version,
1339 L_API_NAME,
1340 G_PKG_NAME)
1341 THEN
1342 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1343 END IF;
1344
1345 --
1346 -- Initialize API return status to success.
1347 --
1348 x_return_status := FND_API.G_RET_STS_SUCCESS;
1349
1350 --
1351 -- Begin API Body.
1352 --
1353
1354 IF G_DEBUG THEN
1355 AMS_Utility_PVT.debug_message(l_full_name||': Validate items');
1356 END IF;
1357
1358 -- Validate required items in the record.
1359 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1360
1361 Validate_Formula_Items(
1362 p_formula_rec => p_formula_rec,
1363 p_validation_mode => JTF_PLSQL_API.g_create,
1364 x_return_status => l_return_status
1365 );
1366
1367 -- If any errors happen abort API.
1368 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1369 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1370 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1371 RAISE FND_API.G_EXC_ERROR;
1372 END IF;
1373 END IF;
1374
1375 IF G_DEBUG THEN
1376 AMS_Utility_PVT.debug_message(l_full_name||': check record');
1377 END IF;
1378
1379 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
1380 Validate_Formula_Rec(
1381 p_formula_rec => p_formula_rec,
1382 p_complete_formula_rec => NULL,
1383 x_return_status => l_return_status
1384 );
1385
1386 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1387 IF G_DEBUG THEN
1388 AMS_Utility_PVT.debug_message(l_full_name||': error in check record');
1389 END IF;
1390 RAISE FND_API.g_exc_unexpected_error;
1391 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1392 IF G_DEBUG THEN
1393 AMS_Utility_PVT.debug_message(l_full_name||': error in check record');
1394 END IF;
1395 RAISE FND_API.g_exc_error;
1396 END IF;
1397 END IF;
1398
1399 IF G_DEBUG THEN
1400 AMS_Utility_PVT.debug_message(l_full_name||': after check record');
1401 END IF;
1402
1403
1404 --
1405 -- End API Body.
1406 --
1407
1408 --
1409 -- Standard API to get message count, and if 1,
1410 -- set the message data OUT variable.
1411 --
1412 FND_MSG_PUB.Count_And_Get (
1413 p_count => x_msg_count,
1414 p_data => x_msg_data,
1415 p_encoded => FND_API.G_FALSE
1416 );
1417
1418 IF G_DEBUG THEN
1419 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1420 END IF;
1421
1422
1423 EXCEPTION
1424 WHEN FND_API.G_EXC_ERROR THEN
1425 x_return_status := FND_API.G_RET_STS_ERROR;
1426 FND_MSG_PUB.Count_And_Get (
1427 p_count => x_msg_count,
1428 p_data => x_msg_data
1429 );
1430 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1431 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1432 FND_MSG_PUB.Count_And_Get (
1433 p_count => x_msg_count,
1434 p_data => x_msg_data
1435 );
1436 WHEN OTHERS THEN
1437 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1438 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1439 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
1440 END IF;
1441 FND_MSG_PUB.Count_And_Get (
1442 p_count => x_msg_count,
1443 p_data => x_msg_data
1444 );
1445 END Validate_Formula;
1446
1447 -- Start of comments
1448 -- NAME
1449 -- Validate_Formula_Entry
1450 --
1451 -- PURPOSE
1452 -- Validation API for Activity metric formula entry table.
1453 --
1454
1455 -- NOTES
1456 --
1460 --
1457 -- HISTORY
1458 -- 01-Jun-2000 tdonohoe Created.
1459
1461 -- End of comments
1462 PROCEDURE Validate_Formula_Entry (
1463 p_api_version IN NUMBER,
1464 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1465 p_validation_level IN NUMBER := FND_API.G_Valid_Level_Full,
1466
1467 x_return_status OUT NOCOPY VARCHAR2,
1468 x_msg_count OUT NOCOPY NUMBER,
1469 x_msg_data OUT NOCOPY VARCHAR2,
1470
1471 p_formula_entry_rec IN ams_formula_entry_rec_type
1472 )
1473 IS
1474 L_API_VERSION CONSTANT NUMBER := 1.0;
1475 L_API_NAME CONSTANT VARCHAR2(30) := 'VALIDATE_FORMULA_ENTRY';
1476 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1477
1478
1479 l_return_status VARCHAR2(1);
1480
1481 BEGIN
1482 --
1483 -- Output debug message.
1484 --
1485 IF G_DEBUG THEN
1486 AMS_Utility_PVT.debug_message(l_full_name||': start');
1487 END IF;
1488
1489 --
1490 -- Initialize message list if p_init_msg_list is set to TRUE.
1491 --
1492 IF FND_API.To_Boolean (p_init_msg_list) THEN
1493 FND_MSG_PUB.Initialize;
1494 END IF;
1495
1496 --
1497 -- Standard check for API version compatibility.
1498 --
1499 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
1500 p_api_version,
1501 L_API_NAME,
1502 G_PKG_NAME)
1503 THEN
1504 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1505 END IF;
1506
1507 --
1508 -- Initialize API return status to success.
1509 --
1510 x_return_status := FND_API.G_RET_STS_SUCCESS;
1511
1512 --
1513 -- Begin API Body.
1514 --
1515
1516 IF G_DEBUG THEN
1517 AMS_Utility_PVT.debug_message(l_full_name||': Validate items');
1518 END IF;
1519
1520 -- Validate required items in the record.
1521 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1522
1523 Validate_Form_Ent_Items(
1524 p_formula_entry_rec => p_formula_entry_rec,
1525 p_validation_mode => JTF_PLSQL_API.g_create,
1526 x_return_status => l_return_status
1527 );
1528
1529 -- If any errors happen abort API.
1530 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1531 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1532 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1533 RAISE FND_API.G_EXC_ERROR;
1534 END IF;
1535 END IF;
1536
1537 IF G_DEBUG THEN
1538 AMS_Utility_PVT.debug_message(l_full_name||': check record');
1539 END IF;
1540
1541 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
1542 Validate_Form_Ent_Rec(
1543 p_formula_entry_rec => p_formula_entry_rec,
1544 p_complete_formula_entry_rec => NULL,
1545 x_return_status => l_return_status
1546 );
1547
1548 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1549 IF G_DEBUG THEN
1550 AMS_Utility_PVT.debug_message(l_full_name||': error in check record');
1551 END IF;
1552 RAISE FND_API.g_exc_unexpected_error;
1553 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1554 IF G_DEBUG THEN
1555 AMS_Utility_PVT.debug_message(l_full_name||': error in check record');
1556 END IF;
1557 RAISE FND_API.g_exc_error;
1558 END IF;
1559 END IF;
1560
1561 IF G_DEBUG THEN
1562 AMS_Utility_PVT.debug_message(l_full_name||': after check record');
1563 END IF;
1564
1565
1566 --
1567 -- End API Body.
1568 --
1569
1570 --
1571 -- Standard API to get message count, and if 1,
1572 -- set the message data OUT variable.
1573 --
1574 FND_MSG_PUB.Count_And_Get (
1575 p_count => x_msg_count,
1576 p_data => x_msg_data,
1577 p_encoded => FND_API.G_FALSE
1578 );
1579
1580 IF G_DEBUG THEN
1581 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1582 END IF;
1583
1584
1585 EXCEPTION
1586 WHEN FND_API.G_EXC_ERROR THEN
1587 x_return_status := FND_API.G_RET_STS_ERROR;
1588 FND_MSG_PUB.Count_And_Get (
1589 p_count => x_msg_count,
1590 p_data => x_msg_data
1591 );
1592 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1593 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1594 FND_MSG_PUB.Count_And_Get (
1595 p_count => x_msg_count,
1596 p_data => x_msg_data
1597 );
1598 WHEN OTHERS THEN
1599 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1600 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1601 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
1602 END IF;
1603 FND_MSG_PUB.Count_And_Get (
1604 p_count => x_msg_count,
1605 p_data => x_msg_data
1606 );
1607 END Validate_Formula_Entry;
1608
1609
1610
1611 -------------------------------------------------------------------------------
1612 -- Start of comments
1613 -- NAME
1614 -- Create_Formula
1615 --
1616 --
1617 -- PURPOSE
1618 -- Creates an Activity Metric Formula.
1619
1620 --
1621 -- NOTES
1622 --
1623 -- HISTORY
1624 -- 31-May-2000 tdonohoe@us Created.
1625 --
1626 -- End of comments
1630 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1627 -------------------------------------------------------------------------------
1628 PROCEDURE Create_Formula (
1629 p_api_version IN NUMBER,
1631 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1632 p_validation_level IN NUMBER := FND_API.G_Valid_Level_Full,
1633
1634 x_return_status OUT NOCOPY VARCHAR2,
1635 x_msg_count OUT NOCOPY NUMBER,
1636 x_msg_data OUT NOCOPY VARCHAR2,
1637
1638 p_formula_rec IN ams_formula_rec_type,
1639 x_formula_id OUT NOCOPY NUMBER
1640 ) IS
1641
1642 --
1643 -- Standard API information constants.
1644 --
1645 L_API_VERSION CONSTANT NUMBER := 1.0;
1646 L_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_FORMULA';
1647 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
1648
1649
1650 l_return_status VARCHAR2(1); -- Return value from procedures.
1651 l_formula_rec ams_formula_rec_type := p_formula_rec;
1652 l_formula_count NUMBER ;
1653
1654 CURSOR c_formula_count(l_formula_id IN NUMBER) IS
1655 SELECT count(*)
1656 FROM ams_act_metric_formulas
1657 WHERE formula_id = l_formula_id;
1658
1659 CURSOR c_formula_id IS
1660 SELECT ams_act_metric_formulas_s.NEXTVAL
1661 FROM dual;
1662
1663 BEGIN
1664
1665 --
1666 -- Initialize savepoint.
1667 --
1668
1669 SAVEPOINT Create_Formula_Pvt;
1670
1671 IF G_DEBUG THEN
1672 AMS_Utility_PVT.Debug_Message(l_full_name||': start');
1673 END IF;
1674
1675 --
1676 -- Initialize message list if p_init_msg_list is set to TRUE.
1677 --
1678 IF FND_API.To_Boolean (p_init_msg_list) THEN
1679 FND_MSG_PUB.Initialize;
1680 END IF;
1681
1682 --
1683 -- Standard check for API version compatibility.
1684 --
1685 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
1686 p_api_version,
1687 L_API_NAME,
1688 G_PKG_NAME)
1689 THEN
1690 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1691 END IF;
1692
1693 --
1694 -- Initialize API return status to success.
1695 --
1696 x_return_status := FND_API.G_RET_STS_SUCCESS;
1697
1698 --
1699 -- Begin API Body.
1700 --
1701
1702 Default_Formula
1703 ( p_init_msg_list => p_init_msg_list,
1704 p_formula_rec => p_formula_rec,
1705 p_validation_mode => JTF_PLSQL_API.g_create,
1706 x_complete_rec => l_formula_rec,
1707 x_return_status => l_return_status,
1708 x_msg_count => x_msg_count,
1709 x_msg_data => x_msg_data ) ;
1710
1711
1712
1713 -- If any errors happen abort API.
1714 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1715 RAISE FND_API.G_EXC_ERROR;
1716 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1717 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1718 END IF;
1719
1720
1721
1722 --
1723 -- Validate the record before inserting.
1724 --
1725
1726
1727 IF l_formula_rec.formula_id IS NULL THEN
1728 LOOP
1729 --
1730 -- Set the value for the PK.
1731 OPEN c_formula_id;
1732 FETCH c_formula_id INTO l_formula_rec.formula_id;
1733 CLOSE c_formula_id;
1734
1735 OPEN c_formula_count(l_formula_rec.formula_id);
1736 FETCH c_formula_count INTO l_formula_count ;
1737 CLOSE c_formula_count ;
1738
1739 EXIT WHEN l_formula_count = 0 ;
1740 END LOOP ;
1741 END IF;
1742
1743
1744
1745 Validate_Formula (
1746 p_api_version => l_api_version,
1747 p_init_msg_list => p_init_msg_list,
1748 p_validation_level => p_validation_level,
1749 x_msg_count => x_msg_count,
1750 x_msg_data => x_msg_data,
1751 x_return_status => l_return_status,
1752 p_formula_rec => l_formula_rec
1753 );
1754
1755 -- If any errors happen abort API.
1756 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1757 RAISE FND_API.G_EXC_ERROR;
1758 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1759 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1760 END IF;
1761
1762
1763 --
1764 -- Debug message.
1765 --
1766 IF G_DEBUG THEN
1767 AMS_Utility_PVT.debug_message(l_full_name ||': insert');
1768 END IF;
1769
1770
1771
1772 --
1773 -- Insert into the base table.
1774 --
1775 INSERT INTO AMS_ACT_METRIC_FORMULAS
1776 ( formula_id
1777 ,activity_metric_id
1778 ,level_depth
1779 ,parent_formula_id
1780 ,last_update_date
1781 ,last_updated_by
1782 ,creation_date
1783 ,created_by
1784 ,last_update_login
1785 ,object_version_number
1786 ,formula_type
1787 )
1788 VALUES
1789 (l_formula_rec.formula_id
1790 ,l_formula_rec.activity_metric_id
1791 ,l_formula_rec.level_depth
1792 ,l_formula_rec.parent_formula_id
1793 ,SYSDATE
1794 ,FND_GLOBAL.User_ID
1795 ,SYSDATE
1796 ,FND_GLOBAL.User_ID
1797 ,FND_GLOBAL.Conc_Login_ID
1798 ,1--object version number
1799 ,l_formula_rec.formula_type
1800 );
1801
1802
1806 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1803 -- If any errors happen abort API.
1804 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1805 RAISE FND_API.G_EXC_ERROR;
1807 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1808 END IF;
1809
1810
1811 -- finish
1812
1813 --
1814 -- Set OUT value.
1815 --
1816 x_formula_id := l_formula_rec.formula_id;
1817
1818 --
1819 -- End API Body.
1820 --
1821
1822 --
1823 -- Standard check for commit request.
1824 --
1825 IF FND_API.To_Boolean (p_commit) THEN
1826 COMMIT WORK;
1827 END IF;
1828
1829 --
1830 -- Standard API to get message count, and if 1,
1831 -- set the message data OUT variable.
1832 --
1833 FND_MSG_PUB.Count_And_Get (
1834 p_count => x_msg_count,
1835 p_data => x_msg_data,
1836 p_encoded => FND_API.G_FALSE
1837 );
1838
1839 --
1840 -- Add success message to message list.
1841 --
1842
1843 IF G_DEBUG THEN
1844 AMS_Utility_PVT.debug_message(l_full_name ||': end Success');
1845 END IF;
1846
1847
1848
1849
1850 EXCEPTION
1851 WHEN FND_API.G_EXC_ERROR THEN
1852
1853
1854 ROLLBACK TO Create_Formula_Pvt;
1855 x_return_status := FND_API.G_RET_STS_ERROR;
1856 FND_MSG_PUB.Count_And_Get (
1857 p_count => x_msg_count,
1858 p_data => x_msg_data
1859 );
1860 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1861
1862
1863
1864 ROLLBACK TO Create_Formula_Pvt;
1865 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1866 FND_MSG_PUB.Count_And_Get (
1867 p_count => x_msg_count,
1868 p_data => x_msg_data
1869 );
1870 WHEN OTHERS THEN
1871
1872
1873 ROLLBACK TO Create_Formula_Pvt;
1874 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1875 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1876 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
1877 END IF;
1878 FND_MSG_PUB.Count_And_Get (
1879 p_count => x_msg_count,
1880 p_data => x_msg_data
1881 );
1882
1883
1884 END Create_Formula;
1885
1886
1887 -------------------------------------------------------------------------------
1888 -- Start of comments
1889 -- NAME
1890 -- Create_Formula_Entry
1891 --
1892 --
1893 -- PURPOSE
1894 -- Creates an Activity Metric Formula Entry.
1895
1896 --
1897 -- NOTES
1898 --
1899 -- HISTORY
1900 -- 31-May-2000 tdonohoe@us Created.
1901 --
1902 -- End of comments
1903 -------------------------------------------------------------------------------
1904 PROCEDURE Create_Formula_Entry (
1905 p_api_version IN NUMBER,
1906 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1907 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1908 p_validation_level IN NUMBER := FND_API.G_Valid_Level_Full,
1909
1910 x_return_status OUT NOCOPY VARCHAR2,
1911 x_msg_count OUT NOCOPY NUMBER,
1912 x_msg_data OUT NOCOPY VARCHAR2,
1913
1914 p_formula_entry_rec IN ams_formula_entry_rec_type,
1915 x_formula_entry_id OUT NOCOPY NUMBER
1916 ) IS
1917
1918 --
1919 -- Standard API information constants.
1920 --
1921 L_API_VERSION CONSTANT NUMBER := 1.0;
1922 L_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_FORMULA_ENTRY';
1923 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
1924
1925
1926 l_return_status VARCHAR2(1); -- Return value from procedures.
1927 l_formula_entry_rec ams_formula_entry_rec_type := p_formula_entry_rec;
1928 l_formula_entry_count NUMBER ;
1929
1930 CURSOR c_formula_entry_count(l_formula_entry_id IN NUMBER) IS
1931 SELECT count(*)
1932 FROM ams_act_metric_form_ent
1933 WHERE formula_entry_id = l_formula_entry_id;
1934
1935 CURSOR c_formula_entry_id IS
1936 SELECT ams_act_metric_formula_ent_s.NEXTVAL
1937 FROM dual;
1938
1939 BEGIN
1940
1941 --
1942 -- Initialize savepoint.
1943 --
1944
1945 SAVEPOINT Create_Formula_Entry_Pvt;
1946
1947 IF G_DEBUG THEN
1948 AMS_Utility_PVT.Debug_Message(l_full_name||': start');
1949 END IF;
1950
1951 --
1952 -- Initialize message list if p_init_msg_list is set to TRUE.
1953 --
1954 IF FND_API.To_Boolean (p_init_msg_list) THEN
1955 FND_MSG_PUB.Initialize;
1956 END IF;
1957
1958 --
1959 -- Standard check for API version compatibility.
1960 --
1961 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
1962 p_api_version,
1963 L_API_NAME,
1964 G_PKG_NAME)
1965 THEN
1966 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1967 END IF;
1968
1969 --
1970 -- Initialize API return status to success.
1971 --
1972 x_return_status := FND_API.G_RET_STS_SUCCESS;
1973
1974 --
1975 -- Begin API Body.
1976 --
1977
1978 Default_Formula_Entry
1979 ( p_init_msg_list => p_init_msg_list,
1980 p_formula_entry_rec => p_formula_entry_rec,
1981 p_validation_mode => JTF_PLSQL_API.g_create,
1985 x_msg_data => x_msg_data ) ;
1982 x_complete_entry_rec => l_formula_entry_rec,
1983 x_return_status => l_return_status,
1984 x_msg_count => x_msg_count,
1986
1987
1988
1989 -- If any errors happen abort API.
1990 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1991 RAISE FND_API.G_EXC_ERROR;
1992 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1993 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1994 END IF;
1995
1996
1997
1998 --
1999 -- Validate the record before inserting.
2000 --
2001
2002
2003 IF l_formula_entry_rec.formula_entry_id IS NULL THEN
2004 LOOP
2005 --
2006 -- Set the value for the PK.
2007 OPEN c_formula_entry_id;
2008 FETCH c_formula_entry_id INTO l_formula_entry_rec.formula_entry_id;
2009 CLOSE c_formula_entry_id;
2010
2011 OPEN c_formula_entry_count(l_formula_entry_rec.formula_entry_id);
2012 FETCH c_formula_entry_count INTO l_formula_entry_count ;
2013 CLOSE c_formula_entry_count ;
2014
2015 EXIT WHEN l_formula_entry_count = 0 ;
2016 END LOOP ;
2017 END IF;
2018
2019
2020
2021 Validate_Formula_Entry (
2022 p_api_version => l_api_version,
2023 p_init_msg_list => p_init_msg_list,
2024 p_validation_level => p_validation_level,
2025 x_msg_count => x_msg_count,
2026 x_msg_data => x_msg_data,
2027 x_return_status => l_return_status,
2028 p_formula_entry_rec => l_formula_entry_rec
2029 );
2030
2031 -- If any errors happen abort API.
2032 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2033 RAISE FND_API.G_EXC_ERROR;
2034 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2035 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2036 END IF;
2037
2038
2039 --
2040 -- Debug message.
2041 --
2042 IF G_DEBUG THEN
2043 AMS_Utility_PVT.debug_message(l_full_name ||': insert');
2044 END IF;
2045
2046
2047
2048 --
2049 -- Insert into the base table.
2050 --
2051 INSERT INTO AMS_ACT_METRIC_FORM_ENT
2052 ( formula_entry_id
2053 ,formula_id
2054 ,order_number
2055 ,formula_entry_type
2056 ,formula_entry_value
2057 ,metric_column_value
2058 ,formula_entry_operator
2059 ,last_update_date
2060 ,last_updated_by
2061 ,creation_date
2062 ,created_by
2063 ,last_update_login
2064 ,object_version_number
2065 )
2066 VALUES
2067 ( l_formula_entry_rec.formula_entry_id
2068 ,l_formula_entry_rec.formula_id
2069 ,l_formula_entry_rec.order_number
2070 ,l_formula_entry_rec.formula_entry_type
2071 ,l_formula_entry_rec.formula_entry_value
2072 ,l_formula_entry_rec.metric_column_value
2073 ,l_formula_entry_rec.formula_entry_operator
2074 ,SYSDATE
2075 ,FND_GLOBAL.User_ID
2076 ,SYSDATE
2077 ,FND_GLOBAL.User_ID
2078 ,FND_GLOBAL.User_ID
2079 ,1--OBJECT_VERSION_NUMBER
2080 );
2081
2082
2083 -- If any errors happen abort API.
2084 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2085 RAISE FND_API.G_EXC_ERROR;
2086 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2087 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2088 END IF;
2089
2090
2091 -- finish
2092
2093 --
2094 -- Set OUT value.
2095 --
2096 x_formula_entry_id := l_formula_entry_rec.formula_entry_id;
2097
2098 --
2099 -- End API Body.
2100 --
2101
2102 --
2103 -- Standard check for commit request.
2104 --
2105 IF FND_API.To_Boolean (p_commit) THEN
2106 COMMIT WORK;
2107 END IF;
2108
2109 --
2110 -- Standard API to get message count, and if 1,
2111 -- set the message data OUT variable.
2112 --
2113 FND_MSG_PUB.Count_And_Get (
2114 p_count => x_msg_count,
2115 p_data => x_msg_data,
2116 p_encoded => FND_API.G_FALSE
2117 );
2118
2119 --
2120 -- Add success message to message list.
2121 --
2122
2123 IF G_DEBUG THEN
2124 AMS_Utility_PVT.debug_message(l_full_name ||': end Success');
2125 END IF;
2126
2127
2128
2129
2130 EXCEPTION
2131 WHEN FND_API.G_EXC_ERROR THEN
2132
2133
2134 ROLLBACK TO Create_Formula_Entry_Pvt;
2135 x_return_status := FND_API.G_RET_STS_ERROR;
2136 FND_MSG_PUB.Count_And_Get (
2137 p_count => x_msg_count,
2138 p_data => x_msg_data
2139 );
2140 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2141
2142
2143
2144 ROLLBACK TO Create_Formula_Entry_Pvt;
2145 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2146 FND_MSG_PUB.Count_And_Get (
2147 p_count => x_msg_count,
2148 p_data => x_msg_data
2149 );
2150 WHEN OTHERS THEN
2151
2152
2153 ROLLBACK TO Create_Formula_Entry_Pvt;
2154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2155 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2156 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
2157 END IF;
2158 FND_MSG_PUB.Count_And_Get (
2159 p_count => x_msg_count,
2160 p_data => x_msg_data
2161 );
2162
2163
2164 END Create_Formula_Entry;
2165
2166
2170 --
2167 -- Start of comments
2168 -- NAME
2169 -- Update_Formula
2171 -- PURPOSE
2172 -- Updates an entry in the AMS_ACT_METRIC_FORMULAS table
2173 --
2174 -- NOTES
2175 --
2176 -- HISTORY
2177 -- 31-May-2000 tdonohoe Created.
2178 --
2179 -- End of comments
2180
2181 PROCEDURE Update_Formula (
2182 p_api_version IN NUMBER,
2183 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2184 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2185 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2186
2187 x_return_status OUT NOCOPY VARCHAR2,
2188 x_msg_count OUT NOCOPY NUMBER,
2189 x_msg_data OUT NOCOPY VARCHAR2,
2190
2191 p_formula_rec IN ams_formula_rec_type
2192 )
2193 IS
2194 L_API_VERSION CONSTANT NUMBER := 1.0;
2195 L_API_NAME CONSTANT VARCHAR2(30) := 'UPDATE_FORMULA';
2196 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2197
2198
2199 l_return_status VARCHAR2(1);
2200 l_formula_rec ams_formula_rec_type := p_formula_rec;
2201
2202 BEGIN
2203
2204 --
2205 -- Initialize savepoint.
2206 --
2207 SAVEPOINT Update_Formula_Pvt;
2208
2209 --
2210 -- Output debug message.
2211 --
2212 IF G_DEBUG THEN
2213 AMS_Utility_PVT.debug_message(l_full_name||': start');
2214 END IF;
2215
2216 --
2217 -- Initialize message list if p_init_msg_list is set to TRUE.
2218 --
2219 IF FND_API.To_Boolean (p_init_msg_list) THEN
2220 FND_MSG_PUB.Initialize;
2221 END IF;
2222
2223 --
2224 -- Standard check for API version compatibility.
2225 --
2226 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
2227 p_api_version,
2228 L_API_NAME,
2229 G_PKG_NAME)
2230 THEN
2231 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2232 END IF;
2233
2234 --
2235 -- Initialize API return status to success.
2236 --
2237 x_return_status := FND_API.G_RET_STS_SUCCESS;
2238
2239 --
2240 -- Begin API Body
2241 --
2242 -- Debug Message
2243
2244
2245 Default_Formula
2246 ( p_init_msg_list => p_init_msg_list,
2247 p_formula_rec => p_formula_rec,
2248 p_validation_mode => JTF_PLSQL_API.G_UPDATE,
2249 x_complete_rec => l_formula_rec,
2250 x_return_status => l_return_status,
2251 x_msg_count => x_msg_count,
2252 x_msg_data => x_msg_data ) ;
2253
2254 -- If any errors happen abort API.
2255 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2256 RAISE FND_API.G_EXC_ERROR;
2257 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2258 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2259 END IF;
2260
2261
2262 IF G_DEBUG THEN
2263 AMS_Utility_PVT.debug_message(l_full_name ||': validate');
2264 END IF;
2265
2266
2267
2268 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
2269 Validate_Formula_Items(
2270 p_formula_rec => l_formula_rec,
2271 p_validation_mode => JTF_PLSQL_API.g_update,
2272 x_return_status => l_return_status
2273 );
2274 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2275 RAISE FND_API.g_exc_unexpected_error;
2276 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
2277 RAISE FND_API.g_exc_error;
2278 END IF;
2279 END IF;
2280
2281
2282
2283 -- replace g_miss_char/num/date with current column values
2284 Complete_Formula_Rec(l_formula_rec,l_formula_rec);
2285
2286
2287 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
2288
2289 Validate_Formula_Rec(
2290 p_formula_rec => p_formula_rec,
2291 p_complete_formula_rec => l_formula_rec,
2292 x_return_status => l_return_status
2293 );
2294
2295 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2296 RAISE FND_API.g_exc_unexpected_error;
2297 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
2298 RAISE FND_API.g_exc_error;
2299 END IF;
2300 END IF;
2301
2302
2303 IF G_DEBUG THEN
2304 AMS_Utility_PVT.debug_message(l_full_name ||': Update Activity Metric Formulas Table');
2305 END IF;
2306
2307 UPDATE ams_act_metric_formulas SET
2308 activity_metric_id = l_formula_rec.activity_metric_id,
2309 level_depth = l_formula_rec.level_depth,
2310 parent_formula_id = l_formula_rec.parent_formula_id,
2311 last_update_date = SYSDATE,
2312 last_updated_by = FND_GLOBAL.User_Id,
2313 last_update_login = FND_GLOBAL.Conc_Login_Id,
2314 object_version_number = l_formula_rec.object_version_number + 1,
2315 formula_type = l_formula_rec.formula_type
2316 WHERE formula_id = l_formula_rec.formula_id;
2317
2318
2319 IF (SQL%NOTFOUND)
2320 THEN
2321 --
2322 -- Add error message to API message list.
2323 --
2324 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2325 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
2326 FND_MSG_PUB.add;
2327 END IF;
2328 RAISE FND_API.g_exc_error;
2329 END IF;
2330
2331
2332 --
2336 IF FND_API.to_boolean(p_commit) THEN
2333 -- End API Body
2334 --
2335
2337 COMMIT;
2338 END IF;
2339
2340 --
2341 -- Standard API to get message count, and if 1,
2342 -- set the message data OUT variable.
2343 --
2344 FND_MSG_PUB.Count_And_Get (
2345 p_count => x_msg_count,
2346 p_data => x_msg_data,
2347 p_encoded => FND_API.G_FALSE
2348 );
2349
2350 --
2351 -- Debug message.
2352 --
2353 IF G_DEBUG THEN
2354 AMS_Utility_PVT.debug_message(l_full_name ||': end');
2355 END IF;
2356
2357
2358 EXCEPTION
2359 WHEN FND_API.G_EXC_ERROR THEN
2360
2361 ROLLBACK TO Update_Formula_pvt;
2362 x_return_status := FND_API.G_RET_STS_ERROR;
2363 FND_MSG_PUB.Count_And_Get (
2364 p_count => x_msg_count,
2365 p_data => x_msg_data);
2366
2367 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2368
2369 ROLLBACK TO Update_Formula_pvt;
2370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2371 FND_MSG_PUB.Count_And_Get (
2372 p_count => x_msg_count,
2373 p_data => x_msg_data
2374 );
2375
2376 WHEN OTHERS THEN
2377
2378 ROLLBACK TO Update_Formula_pvt;
2379 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2380 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2381 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
2382 END IF;
2383 FND_MSG_PUB.Count_And_Get (
2384 p_count => x_msg_count,
2385 p_data => x_msg_data
2386 );
2387 END Update_Formula;
2388
2389
2390
2391
2392 -- Start of comments
2393 -- NAME
2394 -- Update_Formula_Entry
2395 --
2396 -- PURPOSE
2397 -- Updates an entry in the AMS_ACT_METRIC_FORM_ENT table
2398 --
2399 -- NOTES
2400 --
2401 -- HISTORY
2402 -- 09-Jun-2000 tdonohoe Created.
2403 --
2404 -- End of comments
2405
2406 PROCEDURE Update_Formula_Entry (
2407 p_api_version IN NUMBER,
2408 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2409 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2410 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2411
2412 x_return_status OUT NOCOPY VARCHAR2,
2413 x_msg_count OUT NOCOPY NUMBER,
2414 x_msg_data OUT NOCOPY VARCHAR2,
2415
2416 p_formula_entry_rec IN ams_formula_entry_rec_type
2417 )
2418 IS
2419 L_API_VERSION CONSTANT NUMBER := 1.0;
2420 L_API_NAME CONSTANT VARCHAR2(30) := 'UPDATE_FORMULA_ENTRY';
2421 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2422
2423
2424 l_return_status VARCHAR2(1);
2425 l_formula_entry_rec ams_formula_entry_rec_type := p_formula_entry_rec;
2426
2427 BEGIN
2428
2429 --
2430 -- Initialize savepoint.
2431 --
2432 SAVEPOINT Update_Formula_Entry_Pvt;
2433
2434 --
2435 -- Output debug message.
2436 --
2437 IF G_DEBUG THEN
2438 AMS_Utility_PVT.debug_message(l_full_name||': start');
2439 END IF;
2440
2441 --
2442 -- Initialize message list if p_init_msg_list is set to TRUE.
2443 --
2444 IF FND_API.To_Boolean (p_init_msg_list) THEN
2445 FND_MSG_PUB.Initialize;
2446 END IF;
2447
2448 --
2449 -- Standard check for API version compatibility.
2450 --
2451 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
2452 p_api_version,
2453 L_API_NAME,
2454 G_PKG_NAME)
2455 THEN
2456 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2457 END IF;
2458
2459 --
2460 -- Initialize API return status to success.
2461 --
2462 x_return_status := FND_API.G_RET_STS_SUCCESS;
2463
2464 --
2465 -- Begin API Body
2466 --
2467 -- Debug Message
2468
2469
2470 Default_Formula_Entry
2471 ( p_init_msg_list => p_init_msg_list,
2472 p_formula_entry_rec => p_formula_entry_rec,
2473 p_validation_mode => JTF_PLSQL_API.G_UPDATE,
2474 x_complete_entry_rec => l_formula_entry_rec,
2475 x_return_status => l_return_status,
2476 x_msg_count => x_msg_count,
2477 x_msg_data => x_msg_data ) ;
2478
2479 -- If any errors happen abort API.
2480 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2481 RAISE FND_API.G_EXC_ERROR;
2482 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2483 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2484 END IF;
2485
2486
2487 IF G_DEBUG THEN
2488 AMS_Utility_PVT.debug_message(l_full_name ||': validate');
2489 END IF;
2490
2491
2492
2493 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
2494 Validate_Form_Ent_Items(
2495 p_formula_entry_rec => l_formula_entry_rec,
2496 p_validation_mode => JTF_PLSQL_API.g_update,
2497 x_return_status => l_return_status
2498 );
2499
2500 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2501 RAISE FND_API.g_exc_unexpected_error;
2502 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
2503 RAISE FND_API.g_exc_error;
2504 END IF;
2505 END IF;
2506
2510
2507 -- replace g_miss_char/num/date with current column values
2508 Complete_Form_Ent_Rec(l_formula_entry_rec,l_formula_entry_rec);
2509
2511 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
2512
2513 Validate_Form_Ent_Rec(
2514 p_formula_entry_rec => p_formula_entry_rec,
2515 p_complete_formula_entry_rec => l_formula_entry_rec,
2516 x_return_status => l_return_status
2517 );
2518
2519 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2520 RAISE FND_API.g_exc_unexpected_error;
2521 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
2522 RAISE FND_API.g_exc_error;
2523 END IF;
2524 END IF;
2525
2526
2527 IF G_DEBUG THEN
2528 AMS_Utility_PVT.debug_message(l_full_name ||': Update Activity Metric Formula Entry Table');
2529 END IF;
2530
2531 UPDATE ams_act_metric_form_ent SET
2532 formula_id = l_formula_entry_rec.formula_id
2533 ,order_number = l_formula_entry_rec.order_number
2534 ,formula_entry_type = l_formula_entry_rec.formula_entry_type
2535 ,formula_entry_value = l_formula_entry_rec.formula_entry_value
2536 ,metric_column_value = l_formula_entry_rec.metric_column_value
2537 ,formula_entry_operator = l_formula_entry_rec.formula_entry_operator
2538 ,object_version_number = l_formula_entry_rec.object_version_number + 1
2539 ,last_update_date = SYSDATE
2540 ,last_updated_by = FND_GLOBAL.User_ID
2541 ,last_update_login = FND_GLOBAL.User_ID
2542 WHERE formula_entry_id = l_formula_entry_rec.formula_entry_id;
2543
2544
2545
2546 IF (SQL%NOTFOUND)
2547 THEN
2548
2549 --
2550 -- Add error message to API message list.
2551 --
2552 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2553 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
2554 FND_MSG_PUB.add;
2555 END IF;
2556 RAISE FND_API.g_exc_error;
2557 END IF;
2558
2559
2560 --
2561 -- End API Body
2562 --
2563
2564 IF FND_API.to_boolean(p_commit) THEN
2565 COMMIT;
2566 END IF;
2567
2568 --
2569 -- Standard API to get message count, and if 1,
2570 -- set the message data OUT variable.
2571 --
2572 FND_MSG_PUB.Count_And_Get (
2573 p_count => x_msg_count,
2574 p_data => x_msg_data,
2575 p_encoded => FND_API.G_FALSE
2576 );
2577
2578 --
2579 -- Debug message.
2580 --
2581 IF G_DEBUG THEN
2582 AMS_Utility_PVT.debug_message(l_full_name ||': end');
2583 END IF;
2584
2585
2586 EXCEPTION
2587 WHEN FND_API.G_EXC_ERROR THEN
2588 ROLLBACK TO Update_Formula_Entry_Pvt;
2589 x_return_status := FND_API.G_RET_STS_ERROR;
2590 FND_MSG_PUB.Count_And_Get (
2591 p_count => x_msg_count,
2592 p_data => x_msg_data);
2593
2594 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2595 ROLLBACK TO Update_Formula_Entry_Pvt;
2596 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2597 FND_MSG_PUB.Count_And_Get (
2598 p_count => x_msg_count,
2599 p_data => x_msg_data
2600 );
2601
2602 WHEN OTHERS THEN
2603 ROLLBACK TO Update_Formula_Entry_Pvt;
2604 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2605 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2606 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
2607 END IF;
2608 FND_MSG_PUB.Count_And_Get (
2609 p_count => x_msg_count,
2610 p_data => x_msg_data
2611 );
2612 END Update_Formula_Entry;
2613
2614
2615 -- Start of comments
2616 -- NAME
2617 -- Delete_Formula
2618 --
2619 -- PURPOSE
2620 -- Deletes an entry in the ams_act_metrics_formulas table.
2621 --
2622 -- NOTES
2623 --
2624 -- HISTORY
2625 -- 24-Apr-2000 tdonohoe Created.
2626 --
2627 -- End of comments
2628
2629 PROCEDURE Delete_Formula (
2630 p_api_version IN NUMBER,
2631 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2632 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2633
2634 x_return_status OUT NOCOPY VARCHAR2,
2635 x_msg_count OUT NOCOPY NUMBER,
2636 x_msg_data OUT NOCOPY VARCHAR2,
2637
2638 p_formula_id IN NUMBER,
2639 p_object_version_number IN NUMBER
2640 )
2641 IS
2642 L_API_VERSION CONSTANT NUMBER := 1.0;
2643 L_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_FORMULA';
2644 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2645
2646 l_return_status VARCHAR2(1);
2647
2648 BEGIN
2649 --
2650 -- Initialize savepoint.
2651 --
2652 SAVEPOINT Delete_Formula_pvt;
2653
2654 --
2655 -- Output debug message.
2656 --
2657 IF G_DEBUG THEN
2658 AMS_Utility_PVT.debug_message(l_full_name||': start');
2659 END IF;
2660
2661 --
2662 -- Initialize message list if p_init_msg_list is set to TRUE.
2663 --
2664 IF FND_API.To_Boolean (p_init_msg_list) THEN
2665 FND_MSG_PUB.Initialize;
2666 END IF;
2667
2668 --
2669 -- Standard check for API version compatibility.
2670 --
2671 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
2672 p_api_version,
2673 L_API_NAME,
2677 END IF;
2674 G_PKG_NAME)
2675 THEN
2676 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2678
2679 --
2680 -- Initialize API return status to success.
2681 --
2682 x_return_status := FND_API.G_RET_STS_SUCCESS;
2683
2684 --
2685 -- Begin API Body.
2686 --
2687
2688 -- Debug message.
2689 IF G_DEBUG THEN
2690 AMS_Utility_PVT.debug_message(l_full_name ||': delete with Validation');
2691
2692 AMS_Utility_PVT.debug_message('formula id '||to_char(p_formula_id));
2693
2694 AMS_Utility_PVT.debug_message('object version number '||to_char(p_object_version_number));
2695 END IF;
2696
2697 DELETE
2698 FROM ams_act_metric_formulas
2699 WHERE formula_id = p_formula_id
2700 AND object_version_number = p_object_version_number;
2701
2702 IF (SQL%NOTFOUND) THEN
2703 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2704 THEN
2705
2706 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
2707 FND_MSG_PUB.add;
2708 RAISE FND_API.g_exc_error;
2709 END IF;
2710 END IF;
2711
2712 DELETE
2713 FROM ams_act_metric_form_ent
2714 WHERE formula_id = p_formula_id;
2715
2716 --
2717 -- End API Body.
2718 --
2719
2720 IF FND_API.To_Boolean (p_commit) THEN
2721 COMMIT WORK;
2722 END IF;
2723
2724 --
2725 -- Debug message.
2726 --
2727 IF G_DEBUG THEN
2728 AMS_Utility_PVT.debug_message(l_full_name ||': End');
2729 END IF;
2730
2731
2732 --
2733 -- Standard API to get message count, and if 1,
2734 -- set the message data OUT variable.
2735 --
2736 FND_MSG_PUB.Count_And_Get (
2737 p_count => x_msg_count,
2738 p_data => x_msg_data,
2739 p_encoded => FND_API.G_FALSE
2740 );
2741
2742 EXCEPTION
2743 WHEN FND_API.G_EXC_ERROR THEN
2744 ROLLBACK TO Delete_Formula_pvt;
2745 x_return_status := FND_API.G_RET_STS_ERROR;
2746 FND_MSG_PUB.Count_And_Get (
2747 p_count => x_msg_count,
2748 p_data => x_msg_data
2749 );
2750 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2751 ROLLBACK TO Delete_Formula_pvt;
2752 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2753 FND_MSG_PUB.Count_And_Get (
2754 p_count => x_msg_count,
2755 p_data => x_msg_data
2756 );
2757 WHEN OTHERS THEN
2758 ROLLBACK TO Delete_Formula_pvt;
2759 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2760 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2761 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
2762 END IF;
2763 FND_MSG_PUB.Count_And_Get (
2764 p_count => x_msg_count,
2765 p_data => x_msg_data
2766 );
2767 END Delete_Formula;
2768
2769
2770 -- Start of comments
2771 -- NAME
2772 -- Delete_Formula_Entry
2773 --
2774 -- PURPOSE
2775 -- Deletes an entry in the ams_act_metrics_form_ent table.
2776 --
2777 -- NOTES
2778 --
2779 -- HISTORY
2780 -- 09-Jun-2000 tdonohoe Created.
2781 --
2782 -- End of comments
2783
2784 PROCEDURE Delete_Formula_Entry (
2785 p_api_version IN NUMBER,
2786 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2787 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2788
2789 x_return_status OUT NOCOPY VARCHAR2,
2790 x_msg_count OUT NOCOPY NUMBER,
2791 x_msg_data OUT NOCOPY VARCHAR2,
2792
2793 p_formula_entry_id IN NUMBER,
2794 p_object_version_number IN NUMBER
2795 )
2796 IS
2797 L_API_VERSION CONSTANT NUMBER := 1.0;
2798 L_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_FORMULA_ENTRY';
2799 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2800
2801 l_return_status VARCHAR2(1);
2802
2803 BEGIN
2804 --
2805 -- Initialize savepoint.
2806 --
2807 SAVEPOINT Delete_Formula_Entry_Pvt;
2808
2809 --
2810 -- Output debug message.
2811 --
2812 IF G_DEBUG THEN
2813 AMS_Utility_PVT.debug_message(l_full_name||': start');
2814 END IF;
2815
2816 --
2817 -- Initialize message list if p_init_msg_list is set to TRUE.
2818 --
2819 IF FND_API.To_Boolean (p_init_msg_list) THEN
2820 FND_MSG_PUB.Initialize;
2821 END IF;
2822
2823 --
2824 -- Standard check for API version compatibility.
2825 --
2826 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
2827 p_api_version,
2828 L_API_NAME,
2829 G_PKG_NAME)
2830 THEN
2831 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2832 END IF;
2833
2834 --
2835 -- Initialize API return status to success.
2836 --
2837 x_return_status := FND_API.G_RET_STS_SUCCESS;
2838
2839 --
2840 -- Begin API Body.
2841 --
2842
2843 -- Debug message.
2844 IF G_DEBUG THEN
2845 AMS_Utility_PVT.debug_message(l_full_name ||': delete with Validation');
2846
2847 AMS_Utility_PVT.debug_message('formula id '||to_char(p_formula_entry_id));
2848
2849 AMS_Utility_PVT.debug_message('object version number '||to_char(p_object_version_number));
2850 END IF;
2854 WHERE formula_entry_id = p_formula_entry_id
2851
2852 DELETE
2853 FROM ams_act_metric_form_ent
2855 AND object_version_number = p_object_version_number;
2856
2857 IF (SQL%NOTFOUND) THEN
2858 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2859 THEN
2860
2861 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
2862 FND_MSG_PUB.add;
2863 RAISE FND_API.g_exc_error;
2864 END IF;
2865 END IF;
2866
2867 --
2868 -- End API Body.
2869 --
2870
2871 IF FND_API.To_Boolean (p_commit) THEN
2872 COMMIT WORK;
2873 END IF;
2874
2875 --
2876 -- Debug message.
2877 --
2878 IF G_DEBUG THEN
2879 AMS_Utility_PVT.debug_message(l_full_name ||': End');
2880 END IF;
2881
2882
2883 --
2884 -- Standard API to get message count, and if 1,
2885 -- set the message data OUT variable.
2886 --
2887 FND_MSG_PUB.Count_And_Get (
2888 p_count => x_msg_count,
2889 p_data => x_msg_data,
2890 p_encoded => FND_API.G_FALSE
2891 );
2892
2893 EXCEPTION
2894 WHEN FND_API.G_EXC_ERROR THEN
2895 ROLLBACK TO Delete_Formula_Entry_Pvt;
2896 x_return_status := FND_API.G_RET_STS_ERROR;
2897 FND_MSG_PUB.Count_And_Get (
2898 p_count => x_msg_count,
2899 p_data => x_msg_data
2900 );
2901 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2902 ROLLBACK TO Delete_Formula_Entry_Pvt;
2903 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2904 FND_MSG_PUB.Count_And_Get (
2905 p_count => x_msg_count,
2906 p_data => x_msg_data
2907 );
2908 WHEN OTHERS THEN
2909 ROLLBACK TO Delete_Formula_Entry_Pvt;
2910 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2911 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2912 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
2913 END IF;
2914 FND_MSG_PUB.Count_And_Get (
2915 p_count => x_msg_count,
2916 p_data => x_msg_data
2917 );
2918 END Delete_Formula_Entry;
2919
2920
2921 -- Start of comments
2922 -- NAME
2923 -- Lock_Formula
2924 --
2925 -- PURPOSE
2926 -- Lock the given row in AMS_ACT_METRICS_FORMULAS table.
2927 --
2928 -- NOTES
2929 --
2930 -- HISTORY
2931 -- 31-May-2000 tdonohoe Created.
2932 --
2933 -- End of comments
2934
2935 PROCEDURE Lock_Formula (
2936 p_api_version IN NUMBER,
2937 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2938
2939 x_return_status OUT NOCOPY VARCHAR2,
2940 x_msg_count OUT NOCOPY NUMBER,
2941 x_msg_data OUT NOCOPY VARCHAR2,
2942
2943 p_formula_id IN NUMBER,
2944 p_object_version_number IN NUMBER
2945 )
2946 IS
2947 L_API_VERSION CONSTANT NUMBER := 1.0;
2948 L_API_NAME CONSTANT VARCHAR2(30) := 'LOCK_FORMULA';
2949 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2950
2951 l_formula_id NUMBER;
2952
2953 CURSOR c_formula_info IS
2954 SELECT formula_id
2955 FROM ams_act_metric_formulas
2956 WHERE formula_id = p_formula_id
2957 AND object_version_number = p_object_version_number
2958 FOR UPDATE OF formula_id NOWAIT;
2959
2960 BEGIN
2961 --
2962 -- Output debug message.
2963 --
2964 IF G_DEBUG THEN
2965 AMS_Utility_PVT.debug_message(l_full_name||': start');
2966 END IF;
2967
2968 --
2969 -- Initialize message list if p_init_msg_list is set to TRUE.
2970 --
2971 IF FND_API.To_Boolean (p_init_msg_list) THEN
2972 FND_MSG_PUB.Initialize;
2973 END IF;
2974
2975 --
2976 -- Standard check for API version compatibility.
2977 --
2978 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
2979 p_api_version,
2980 L_API_NAME,
2981 G_PKG_NAME)
2982 THEN
2983 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2984 END IF;
2985
2986 --
2987 -- Initialize API return status to success.
2988 --
2989 x_return_status := FND_API.G_RET_STS_SUCCESS;
2990
2991 --
2992 -- Begin API Body
2993 --
2994 IF G_DEBUG THEN
2995 AMS_Utility_PVT.debug_message(l_full_name||': lock');
2996 END IF;
2997
2998 OPEN c_formula_info;
2999 FETCH c_formula_info INTO l_formula_id;
3000 IF (c_formula_info%NOTFOUND)
3001 THEN
3002 CLOSE c_formula_info;
3003 -- Error, check the msg level and added an error message to the
3004 -- API message list
3005 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3006 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
3007 FND_MSG_PUB.add;
3008 END IF;
3009 RAISE FND_API.g_exc_error;
3010 END IF;
3011 CLOSE c_formula_info;
3012
3013
3014 --
3015 -- Standard API to get message count, and if 1,
3016 -- set the message data OUT variable.
3017 --
3018 FND_MSG_PUB.Count_And_Get (
3019 p_count => x_msg_count,
3020 p_data => x_msg_data,
3021 p_encoded => FND_API.G_FALSE
3022 );
3023
3024 --
3025 -- Debug message.
3026 --
3027 IF G_DEBUG THEN
3031
3028 AMS_Utility_PVT.debug_message(l_full_name ||': end');
3029 END IF;
3030
3032 EXCEPTION
3033 WHEN FND_API.G_EXC_ERROR THEN
3034 x_return_status := FND_API.G_RET_STS_ERROR;
3035 FND_MSG_PUB.Count_And_Get (
3036 p_count => x_msg_count,
3037 p_data => x_msg_data
3038 );
3039 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3040 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3041 FND_MSG_PUB.Count_And_Get (
3042 p_count => x_msg_count,
3043 p_data => x_msg_data
3044 );
3045 WHEN AMS_Utility_PVT.RESOURCE_LOCKED THEN
3046 x_return_status := FND_API.G_RET_STS_ERROR ;
3047
3048 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3049 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
3050 FND_MSG_PUB.add;
3051 END IF;
3052
3053 FND_MSG_PUB.Count_And_Get (
3054 p_count => x_msg_count,
3055 p_data => x_msg_data,
3056 p_encoded => FND_API.G_FALSE
3057 );
3058 WHEN OTHERS THEN
3059 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3060 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3061 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
3062 END IF;
3063 FND_MSG_PUB.Count_And_Get (
3064 p_count => x_msg_count,
3065 p_data => x_msg_data,
3066 p_encoded => FND_API.G_FALSE
3067 );
3068 END Lock_Formula;
3069
3070
3071 -- Start of comments
3072 -- NAME
3073 -- Lock_Formula_Entry
3074 --
3075 -- PURPOSE
3076 -- Lock the given row in AMS_ACT_METRIC_FORM_ENT table.
3077 --
3078 -- NOTES
3079 --
3080 -- HISTORY
3081 -- 31-May-2000 tdonohoe Created.
3082 --
3083 -- End of comments
3084
3085 PROCEDURE Lock_Formula_Entry (
3086 p_api_version IN NUMBER,
3087 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3088
3089 x_return_status OUT NOCOPY VARCHAR2,
3090 x_msg_count OUT NOCOPY NUMBER,
3091 x_msg_data OUT NOCOPY VARCHAR2,
3092
3093 p_formula_entry_id IN NUMBER,
3094 p_object_version_number IN NUMBER
3095 )
3096 IS
3097 L_API_VERSION CONSTANT NUMBER := 1.0;
3098 L_API_NAME CONSTANT VARCHAR2(30) := 'LOCK_FORMULA_ENTRY';
3099 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
3100
3101 l_formula_entry_id NUMBER;
3102
3103 CURSOR c_formula_entry_info IS
3104 SELECT formula_entry_id
3105 FROM ams_act_metric_form_ent
3106 WHERE formula_entry_id = p_formula_entry_id
3107 AND object_version_number = p_object_version_number
3108 FOR UPDATE OF formula_entry_id NOWAIT;
3109
3110 BEGIN
3111 --
3112 -- Output debug message.
3113 --
3114 IF G_DEBUG THEN
3115 AMS_Utility_PVT.debug_message(l_full_name||': start');
3116 END IF;
3117
3118 --
3119 -- Initialize message list if p_init_msg_list is set to TRUE.
3120 --
3121 IF FND_API.To_Boolean (p_init_msg_list) THEN
3122 FND_MSG_PUB.Initialize;
3123 END IF;
3124
3125 --
3126 -- Standard check for API version compatibility.
3127 --
3128 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
3129 p_api_version,
3130 L_API_NAME,
3131 G_PKG_NAME)
3132 THEN
3133 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3134 END IF;
3135
3136 --
3137 -- Initialize API return status to success.
3138 --
3139 x_return_status := FND_API.G_RET_STS_SUCCESS;
3140
3141 --
3142 -- Begin API Body
3143 --
3144 IF G_DEBUG THEN
3145 AMS_Utility_PVT.debug_message(l_full_name||': lock');
3146 END IF;
3147
3148 OPEN c_formula_entry_info;
3149 FETCH c_formula_entry_info INTO l_formula_entry_id;
3150 IF (c_formula_entry_info%NOTFOUND)
3151 THEN
3152 CLOSE c_formula_entry_info;
3153 -- Error, check the msg level and added an error message to the
3154 -- API message list
3155 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3156 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
3157 FND_MSG_PUB.add;
3158 END IF;
3159 RAISE FND_API.g_exc_error;
3160 END IF;
3161 CLOSE c_formula_entry_info;
3162
3163
3164 --
3165 -- Standard API to get message count, and if 1,
3166 -- set the message data OUT variable.
3167 --
3168 FND_MSG_PUB.Count_And_Get (
3169 p_count => x_msg_count,
3170 p_data => x_msg_data,
3171 p_encoded => FND_API.G_FALSE
3172 );
3173
3174 --
3175 -- Debug message.
3176 --
3177 IF G_DEBUG THEN
3178 AMS_Utility_PVT.debug_message(l_full_name ||': end');
3179 END IF;
3180
3181
3182 EXCEPTION
3183 WHEN FND_API.G_EXC_ERROR THEN
3184 x_return_status := FND_API.G_RET_STS_ERROR;
3185 FND_MSG_PUB.Count_And_Get (
3186 p_count => x_msg_count,
3187 p_data => x_msg_data
3188 );
3189 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3190 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3191 FND_MSG_PUB.Count_And_Get (
3192 p_count => x_msg_count,
3193 p_data => x_msg_data
3194 );
3195 WHEN AMS_Utility_PVT.RESOURCE_LOCKED THEN
3199 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
3196 x_return_status := FND_API.G_RET_STS_ERROR ;
3197
3198 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3200 FND_MSG_PUB.add;
3201 END IF;
3202
3203 FND_MSG_PUB.Count_And_Get (
3204 p_count => x_msg_count,
3205 p_data => x_msg_data,
3206 p_encoded => FND_API.G_FALSE
3207 );
3208 WHEN OTHERS THEN
3209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3210 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3211 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
3212 END IF;
3213 FND_MSG_PUB.Count_And_Get (
3214 p_count => x_msg_count,
3215 p_data => x_msg_data,
3216 p_encoded => FND_API.G_FALSE
3217 );
3218 END Lock_Formula_Entry;
3219
3220 -------------------------------------------------------------------------------
3221 -- Start of comments
3222 --
3223 -- NAME
3224 -- Complete_Formula_Rec
3225 --
3226 -- PURPOSE
3227 -- Returns the Initialized Activity Metric Formula Record
3228 --
3229 -- NOTES
3230 --
3231 -- HISTORY
3232 -- 31-May-2000 tdonohoe Created.
3233 --
3234 PROCEDURE Complete_Formula_Rec(
3235 p_formula_rec IN ams_formula_rec_type,
3236 x_complete_formula_rec OUT NOCOPY ams_formula_rec_type
3237 )
3238 IS
3239 CURSOR c_formula IS
3240 SELECT *
3241 FROM ams_act_metric_formulas
3242 WHERE formula_id = p_formula_rec.formula_id;
3243
3244 l_formula_rec c_formula%ROWTYPE;
3245
3246 BEGIN
3247
3248 x_complete_formula_rec := p_formula_rec;
3249
3250 OPEN c_formula;
3251 FETCH c_formula INTO l_formula_rec;
3252
3253 IF c_formula%NOTFOUND THEN
3254 CLOSE c_formula;
3255 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3256 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
3257 FND_MSG_PUB.add;
3258 END IF;
3259 RAISE FND_API.g_exc_error;
3260 END IF;
3261 CLOSE c_formula;
3262
3263
3264
3265 IF p_formula_rec.formula_id = FND_API.G_MISS_NUM THEN
3266 x_complete_formula_rec.formula_id := l_formula_rec.formula_id;
3267 END IF;
3268
3269 IF p_formula_rec.activity_metric_id = FND_API.G_MISS_NUM THEN
3270 x_complete_formula_rec.activity_metric_id := l_formula_rec.activity_metric_id;
3271 END IF;
3272
3273 IF p_formula_rec.level_depth = FND_API.G_MISS_NUM THEN
3274 x_complete_formula_rec.level_depth := l_formula_rec.level_depth;
3275 END IF;
3276
3277 IF p_formula_rec.parent_formula_id = FND_API.G_MISS_NUM THEN
3278 x_complete_formula_rec.parent_formula_id := l_formula_rec.parent_formula_id;
3279 END IF;
3280
3281 IF p_formula_rec.last_update_date = FND_API.G_MISS_DATE THEN
3282 x_complete_formula_rec.last_update_date := l_formula_rec.last_update_date;
3283 END IF;
3284
3285 IF p_formula_rec.last_updated_by = FND_API.G_MISS_NUM THEN
3286 x_complete_formula_rec.last_updated_by := l_formula_rec.last_updated_by ;
3287 END IF;
3288
3289 IF p_formula_rec.creation_date = FND_API.G_MISS_DATE THEN
3290 x_complete_formula_rec.creation_date := l_formula_rec.creation_date;
3291 END IF;
3292
3293 IF p_formula_rec.created_by = FND_API.G_MISS_NUM THEN
3294 x_complete_formula_rec.last_updated_by := l_formula_rec.last_updated_by ;
3295 END IF;
3296
3297 IF p_formula_rec.last_update_login = FND_API.G_MISS_NUM THEN
3298 x_complete_formula_rec.last_updated_by := l_formula_rec.last_updated_by ;
3299 END IF;
3300
3301 IF p_formula_rec.formula_type = FND_API.G_MISS_CHAR THEN
3302 x_complete_formula_rec.formula_type := l_formula_rec.formula_type;
3303 END IF;
3304
3305 END Complete_Formula_Rec ;
3306
3307
3308 -------------------------------------------------------------------------------
3309 -- Start of comments
3310 --
3311 -- NAME
3312 -- Complete_Form_Ent_Rec
3313 --
3314 -- PURPOSE
3315 -- Returns the Initialized Activity Metric Formula Entry Record
3316 --
3317 -- NOTES
3318 --
3319 -- HISTORY
3320 -- 01-Jun-2000 tdonohoe Created.
3321 --
3322 PROCEDURE Complete_Form_Ent_Rec(
3323 p_formula_entry_rec IN ams_formula_entry_rec_type,
3324 x_complete_formula_entry_rec OUT NOCOPY ams_formula_entry_rec_type
3325 )
3326 IS
3327 CURSOR c_formula_entry IS
3328 SELECT *
3329 FROM ams_act_metric_form_ent
3330 WHERE formula_entry_id = p_formula_entry_rec.formula_entry_id;
3331
3332 l_formula_entry_rec c_formula_entry%ROWTYPE;
3333
3334 BEGIN
3335
3336 x_complete_formula_entry_rec := p_formula_entry_rec ;
3337
3338 OPEN c_formula_entry;
3339 FETCH c_formula_entry INTO l_formula_entry_rec;
3340
3341 IF c_formula_entry%NOTFOUND THEN
3342 CLOSE c_formula_entry;
3343 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3344 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
3345 FND_MSG_PUB.add;
3346 END IF;
3347 RAISE FND_API.g_exc_error;
3348 END IF;
3349 CLOSE c_formula_entry;
3350
3351
3352 IF p_formula_entry_rec.formula_entry_id = FND_API.G_MISS_NUM THEN
3353 x_complete_formula_entry_rec.formula_entry_id := l_formula_entry_rec.formula_entry_id;
3354 END IF;
3355
3356 IF p_formula_entry_rec.formula_id = FND_API.G_MISS_NUM THEN
3357 x_complete_formula_entry_rec.formula_id := l_formula_entry_rec.formula_id;
3358 END IF;
3359
3363
3360 IF p_formula_entry_rec.order_number = FND_API.G_MISS_NUM THEN
3361 x_complete_formula_entry_rec.order_number := l_formula_entry_rec.order_number;
3362 END IF;
3364 IF p_formula_entry_rec.formula_entry_type = FND_API.G_MISS_CHAR THEN
3365 x_complete_formula_entry_rec.formula_entry_type := l_formula_entry_rec.formula_entry_type;
3366 END IF;
3367
3368 IF p_formula_entry_rec.formula_entry_value = FND_API.G_MISS_CHAR THEN
3369 x_complete_formula_entry_rec.formula_entry_value := l_formula_entry_rec.formula_entry_value;
3370 END IF;
3371
3372 IF p_formula_entry_rec.metric_column_value = FND_API.G_MISS_CHAR THEN
3373 x_complete_formula_entry_rec.metric_column_value := l_formula_entry_rec.metric_column_value;
3374 END IF;
3375
3376 IF p_formula_entry_rec.formula_entry_operator = FND_API.G_MISS_CHAR THEN
3377 x_complete_formula_entry_rec.formula_entry_operator := l_formula_entry_rec.formula_entry_operator;
3378 END IF;
3379
3380 IF p_formula_entry_rec.formula_entry_operator = FND_API.G_MISS_CHAR THEN
3381 x_complete_formula_entry_rec.formula_entry_operator := l_formula_entry_rec.formula_entry_operator;
3382 END IF;
3383
3384 IF p_formula_entry_rec.last_update_date = FND_API.G_MISS_DATE THEN
3385 x_complete_formula_entry_rec.last_update_date := l_formula_entry_rec.last_update_date;
3386 END IF;
3387
3388 IF p_formula_entry_rec.last_updated_by = FND_API.G_MISS_NUM THEN
3389 x_complete_formula_entry_rec.last_updated_by := l_formula_entry_rec.last_updated_by;
3390 END IF;
3391
3392 IF p_formula_entry_rec.creation_date = FND_API.G_MISS_DATE THEN
3393 x_complete_formula_entry_rec.creation_date := l_formula_entry_rec.creation_date;
3394 END IF;
3395
3396 IF p_formula_entry_rec.created_by = FND_API.G_MISS_NUM THEN
3397 x_complete_formula_entry_rec.created_by := l_formula_entry_rec.created_by;
3398 END IF;
3399
3400 IF p_formula_entry_rec.last_update_login = FND_API.G_MISS_NUM THEN
3401 x_complete_formula_entry_rec.last_update_login := l_formula_entry_rec.last_update_login;
3402 END IF;
3403
3404 IF p_formula_entry_rec.object_version_number = FND_API.G_MISS_NUM THEN
3405 x_complete_formula_entry_rec.object_version_number := l_formula_entry_rec.object_version_number;
3406 END IF;
3407
3408 END Complete_Form_Ent_Rec ;
3409
3410 END AMS_Formula_PVT;