[Home] [Help]
PACKAGE BODY: APPS.OZF_ACTMETRICFACT_PVT
Source
1 PACKAGE BODY Ozf_Actmetricfact_Pvt AS
2 /* $Header: ozfvamfb.pls 120.1.12010000.2 2008/08/13 06:20:58 kdass ship $ */
3
4 ---------------------------------------------------------------------------------------------------
5 --
6 -- NAME
7 -- Ozf_Actmetricfact_Pvt
8 --
9 -- HISTORY
10 -- 20-Jun-1999 tdonohoe Created package.
11 -- 28-Jun 2000 tdonohoe Modified Check_ActMetricFact_Items to allow the same node to appear on a
12 -- hierarchy combined with a unique formula_id.
13 -- 31-Jul-2000 tdonohoe comment out code to fix bug 1362107.
14 -- 03-Apr-2001 yzhao add validate_fund_facts
15 -- 08-Aug-2005 mkothari added 4 new columns for forecasting based on 3rd party baseline sales
16 --------------------------------------------------------------------------------------------------
17
18 --
19 -- Global variables and constants.
20
21 G_PKG_NAME CONSTANT VARCHAR2(30) := 'Ozf_Actmetricfact_Pvt'; -- Name of the current package.
22 G_DEBUG_FLAG VARCHAR2(1) := 'N';
23 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
24
25
26 -- Start of comments
27 -- NAME
28 -- Default_ActMetricFact
29 --
30 --
31 -- PURPOSE
32 -- Defaults the Activty Metric Fact .
33 --
34 -- NOTES
35 --
36 -- HISTORY
37 -- 24-Apr-2000 tdonohoe Created.
38 --
39 -- End of comments
40
41 OZF_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
42 OZF_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
43 OZF_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
44
45 PROCEDURE Default_ActMetricFact(
46 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
47 p_act_metric_fact_rec IN act_metric_fact_rec_type,
48 p_validation_mode IN VARCHAR2 ,
49 x_complete_rec OUT NOCOPY act_metric_fact_rec_type,
50 x_return_status OUT NOCOPY VARCHAR2,
51 x_msg_count OUT NOCOPY NUMBER,
52 x_msg_data OUT NOCOPY VARCHAR2
53 )
54 IS
55
56 BEGIN
57 --
58 -- Initialize message list if p_init_msg_list is set to TRUE.
59 --
60 IF FND_API.To_Boolean (p_init_msg_list) THEN
61 FND_MSG_PUB.Initialize;
62 END IF;
63
64 --
65 -- Initialize API return status to success.
66 --
67 x_return_status := FND_API.G_RET_STS_SUCCESS;
68
69 x_complete_rec := p_act_metric_fact_rec;
70
71 -- Insert Mode
72 IF ((p_validation_mode = JTF_PLSQL_API.g_create) OR (p_validation_mode = JTF_PLSQL_API.g_update)) THEN
73
74 IF p_act_metric_fact_rec.trans_forecasted_value IS NULL THEN
75 x_complete_rec.trans_forecasted_value := 0;
76 END IF;
77
78 IF p_act_metric_fact_rec.base_quantity IS NULL THEN
79 x_complete_rec.base_quantity := 0;
80 END IF;
81
82 IF p_act_metric_fact_rec.functional_currency_code IS NULL THEN
83 x_complete_rec.functional_currency_code := 'NONE';
84 END IF;
85
86 IF p_act_metric_fact_rec.func_forecasted_value IS NULL THEN
87 x_complete_rec.func_forecasted_value := 0;
88 END IF;
89
90 IF p_act_metric_fact_rec.de_metric_id IS NULL THEN
91 x_complete_rec.de_metric_id := 0;
92 END IF;
93
94 IF p_act_metric_fact_rec.time_id1 IS NULL THEN
95 x_complete_rec.time_id1 := 0;
96 END IF;
97
98 IF p_act_metric_fact_rec.value_type IS NULL THEN
99 x_complete_rec.value_type := 'NUMERIC';
100 END IF;
101
102 END IF;
103
104 END Default_ActMetricFact ;
105
106
107 -- Start of comments
108 -- API Name Init_ActMetricFact_Rec
109 -- Type Private
110 -- Function This Process initialize Activity Metric Fact record
111 -- Parameters
112 -- OUT NOCOPY x_fact_rec OUT NOCOPY act_metric_rec_fact_type
113 -- History
114 -- 05/30/2002 created by Ying Zhao
115 -- End of comments
116
117 PROCEDURE Init_ActMetricFact_Rec(
118 x_fact_rec OUT NOCOPY act_metric_fact_rec_type
119 )
120 IS
121 BEGIN
122 RETURN;
123 END Init_ActMetricFact_Rec;
124
125
126 -- Start of comments
127 -- NAME
128 -- Create_ActMetricFact
129 --
130 --
131 -- PURPOSE
132 -- Creates a result entry for the Activity Metric.
133
134 --
135 -- NOTES
136 --
137 -- HISTORY
138 -- 18-Apr-2000 tdonohoe@us Created.
139 --
140 -- End of comments
141
142 PROCEDURE Create_ActMetricFact (
143 p_api_version IN NUMBER,
144 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
145 p_commit IN VARCHAR2 := FND_API.G_FALSE,
146 p_validation_level IN NUMBER := FND_API.G_Valid_Level_Full,
147
148 x_return_status OUT NOCOPY VARCHAR2,
149 x_msg_count OUT NOCOPY NUMBER,
150 x_msg_data OUT NOCOPY VARCHAR2,
151
152 p_act_metric_fact_rec IN act_metric_fact_rec_type,
153 x_activity_metric_fact_id OUT NOCOPY NUMBER
154 )
155 IS
156 --
157 -- Standard API information constants.
158 --
159 L_API_VERSION CONSTANT NUMBER := 1.0;
160 L_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_ACTMETRICFACT';
161 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
162
163
164 l_return_status VARCHAR2(1); -- Return value from procedures.
165 l_act_metric_fact_rec act_metric_fact_rec_type := p_act_metric_fact_rec;
166 l_act_metric_fact_count NUMBER ;
167
168 l_sql_err_msg varchar2(4000);
169
170 CURSOR c_act_metric_fact_count(l_act_metric_fact_id IN NUMBER) IS
171 SELECT count(1)
172 FROM ozf_act_metric_facts_all
173 WHERE activity_metric_fact_id = l_act_metric_fact_id;
174
175 CURSOR c_act_metric_fact_id IS
176 SELECT ozf_act_metric_facts_all_s.NEXTVAL
177 FROM dual;
178
179 BEGIN
180 --
181 -- Initialize savepoint.
182 --
183
184 SAVEPOINT Create_ActMetricFact_Pvt;
185
186 IF (OZF_DEBUG_HIGH_ON) THEN
187
188
189
190 OZF_Utility_PVT.Debug_Message(l_full_name||': start');
191
192 END IF;
193
194 --
195 -- Initialize message list if p_init_msg_list is set to TRUE.
196 --
197 IF FND_API.To_Boolean (p_init_msg_list) THEN
198 FND_MSG_PUB.Initialize;
199 END IF;
200
201 --
202 -- Standard check for API version compatibility.
203 --
204 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
205 p_api_version,
206 L_API_NAME,
207 G_PKG_NAME)
208 THEN
209 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
210 END IF;
211
212 --
213 -- Initialize API return status to success.
214 --
215 x_return_status := FND_API.G_RET_STS_SUCCESS;
216
217 --
218 -- Begin API Body.
219 --
220
221
222
223 Default_ActMetricFact
224 ( p_init_msg_list => p_init_msg_list,
225 p_act_metric_fact_rec => p_act_metric_fact_rec,
226 p_validation_mode => JTF_PLSQL_API.g_create,
227 x_complete_rec => l_act_metric_fact_rec,
228 x_return_status => l_return_status,
229 x_msg_count => x_msg_count,
230 x_msg_data => x_msg_data ) ;
231
232 -- If any errors happen abort API.
233 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
234 RAISE FND_API.G_EXC_ERROR;
235 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
236 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
237 END IF;
238
239
240
241
242 --
243 -- Validate the record before inserting.
244 --
245
246
247 IF l_act_metric_fact_rec.activity_metric_fact_id IS NULL THEN
248 LOOP
249 --
250 -- Set the value for the PK.
251 OPEN c_act_metric_fact_id;
252 FETCH c_act_metric_fact_id INTO l_act_metric_fact_rec.activity_metric_fact_id;
253 CLOSE c_act_metric_fact_id;
254
255 OPEN c_act_metric_fact_count(l_act_metric_fact_rec.activity_metric_fact_id);
256 FETCH c_act_metric_fact_count INTO l_act_metric_fact_count ;
257 CLOSE c_act_metric_fact_count ;
258
259 EXIT WHEN l_act_metric_fact_count = 0 ;
260 END LOOP ;
261 END IF;
262
263
264
265
266 Validate_ActMetFact (
267 p_api_version => l_api_version,
268 p_init_msg_list => p_init_msg_list,
269 p_validation_level => p_validation_level,
270 x_msg_count => x_msg_count,
271 x_msg_data => x_msg_data,
272 x_return_status => l_return_status,
273 p_act_metric_fact_rec => l_act_metric_fact_rec
274 );
275
276 -- If any errors happen abort API.
277 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
278 RAISE FND_API.G_EXC_ERROR;
279 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
280 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
281 END IF;
282
283
284
285 --
286 -- Debug message.
287 --
288 IF (OZF_DEBUG_HIGH_ON) THEN
289
290 OZF_Utility_PVT.debug_message(l_full_name ||': insert');
291 END IF;
292
293
294
295 --
296 -- Insert into the base table.
297 --
298
299
300 Insert into ozf_act_metric_facts_all (
301 activity_metric_fact_id,
302 last_update_date,
303 last_updated_by,
304 creation_date,
305 created_by,
306 last_update_login,
307 object_version_number,
308 act_metric_used_by_id,
309 arc_act_metric_used_by,
310 value_type ,
311 activity_metric_id ,
312 activity_geo_area_id ,
313 activity_product_id ,
314 transaction_currency_code,
315 trans_forecasted_value ,
316 base_quantity ,
317 functional_currency_code ,
318 func_forecasted_value ,
319 org_id ,
320 de_metric_id ,
321 de_geographic_area_id ,
322 de_geographic_area_type ,
323 de_inventory_item_id ,
324 de_inventory_item_org_id ,
325 time_id1 ,
326 time_id2 ,
327 time_id3 ,
328 time_id4 ,
329 time_id5 ,
330 time_id6 ,
331 time_id7 ,
332 time_id8 ,
333 time_id9 ,
334 time_id10 ,
335 time_id11 ,
336 time_id12 ,
337 time_id13 ,
338 time_id14 ,
339 time_id15 ,
340 time_id16 ,
341 time_id17 ,
342 time_id18 ,
343 time_id19 ,
344 time_id20 ,
345 time_id21 ,
346 time_id22 ,
347 time_id23 ,
348 time_id24 ,
349 time_id25 ,
350 time_id26 ,
351 time_id27 ,
352 time_id28 ,
353 time_id29 ,
354 time_id30 ,
355 time_id31 ,
356 time_id32 ,
357 time_id33 ,
358 time_id34 ,
359 time_id35 ,
360 time_id36 ,
361 time_id37 ,
362 time_id38 ,
363 time_id39 ,
364 time_id40 ,
365 time_id41 ,
366 time_id42 ,
367 time_id43 ,
371 time_id47 ,
368 time_id44 ,
369 time_id45 ,
370 time_id46 ,
372 time_id48 ,
373 time_id49 ,
374 time_id50 ,
375 time_id51 ,
376 time_id52 ,
377 time_id53 ,
378 hierarchy_id ,
379 node_id ,
380 level_depth ,
381 formula_id ,
382 from_date ,
383 to_date ,
384 fact_value ,
385 fact_percent ,
386 root_fact_id ,
387 previous_fact_id ,
388 fact_type ,
389 fact_reference ,
390 forward_buy_quantity ,
391 /* 05/21/2002 yzhao: add 11 new columns for top-down bottom-up budgeting */
392 status_code ,
393 hierarchy_type ,
394 approval_date ,
395 recommend_total_amount ,
396 recommend_hb_amount ,
397 request_total_amount ,
398 request_hb_amount ,
399 actual_total_amount ,
400 actual_hb_amount ,
401 base_total_pct ,
402 base_hb_pct ,
403 /* 05/21/2002 yzhao: add ends */
404 /* 08/12/2005 mkothari: added 4 new columns for forecasting with 3rd party baseline sales */
405 baseline_sales ,
406 tpr_percent ,
407 lift_factor ,
408 incremental_sales
409 /* 08/12/2005 mkothari: add ends */
410 )
411 VALUES ( l_act_metric_fact_rec.activity_metric_fact_id,
412 SYSDATE,
413 FND_GLOBAL.User_ID,
414 SYSDATE,
415 FND_GLOBAL.User_ID,
416 FND_GLOBAL.Conc_Login_ID,
417 1, --OBJECT_VERSION_NUMBER
418 l_act_metric_fact_rec.act_metric_used_by_id,
419 l_act_metric_fact_rec.arc_act_metric_used_by,
420 l_act_metric_fact_rec.value_type ,
421 l_act_metric_fact_rec.activity_metric_id ,
422 l_act_metric_fact_rec.activity_geo_area_id ,
423 l_act_metric_fact_rec.activity_product_id ,
424 l_act_metric_fact_rec.transaction_currency_code,
425 l_act_metric_fact_rec.trans_forecasted_value ,
426 l_act_metric_fact_rec.base_quantity ,
427 l_act_metric_fact_rec.functional_currency_code ,
428 l_act_metric_fact_rec.func_forecasted_value ,
429 MO_UTILS.get_default_org_id , -- org_id
430 l_act_metric_fact_rec.de_metric_id ,
431 l_act_metric_fact_rec.de_geographic_area_id ,
432 l_act_metric_fact_rec.de_geographic_area_type ,
433 l_act_metric_fact_rec.de_inventory_item_id ,
434 l_act_metric_fact_rec.de_inventory_item_org_id ,
435 l_act_metric_fact_rec.time_id1 ,
436 l_act_metric_fact_rec.time_id2 ,
437 l_act_metric_fact_rec.time_id3 ,
438 l_act_metric_fact_rec.time_id4 ,
439 l_act_metric_fact_rec.time_id5 ,
440 l_act_metric_fact_rec.time_id6 ,
441 l_act_metric_fact_rec.time_id7 ,
442 l_act_metric_fact_rec.time_id8 ,
443 l_act_metric_fact_rec.time_id9 ,
444 l_act_metric_fact_rec.time_id10 ,
445 l_act_metric_fact_rec.time_id11 ,
446 l_act_metric_fact_rec.time_id12 ,
447 l_act_metric_fact_rec.time_id13 ,
448 l_act_metric_fact_rec.time_id14 ,
449 l_act_metric_fact_rec.time_id15 ,
450 l_act_metric_fact_rec.time_id16 ,
451 l_act_metric_fact_rec.time_id17 ,
452 l_act_metric_fact_rec.time_id18 ,
453 l_act_metric_fact_rec.time_id19 ,
454 l_act_metric_fact_rec.time_id20 ,
455 l_act_metric_fact_rec.time_id21 ,
456 l_act_metric_fact_rec.time_id22 ,
457 l_act_metric_fact_rec.time_id23 ,
458 l_act_metric_fact_rec.time_id24 ,
459 l_act_metric_fact_rec.time_id25 ,
460 l_act_metric_fact_rec.time_id26 ,
461 l_act_metric_fact_rec.time_id27 ,
462 l_act_metric_fact_rec.time_id28 ,
466 l_act_metric_fact_rec.time_id32 ,
463 l_act_metric_fact_rec.time_id29 ,
464 l_act_metric_fact_rec.time_id30 ,
465 l_act_metric_fact_rec.time_id31 ,
467 l_act_metric_fact_rec.time_id33 ,
468 l_act_metric_fact_rec.time_id34 ,
469 l_act_metric_fact_rec.time_id35 ,
470 l_act_metric_fact_rec.time_id36 ,
471 l_act_metric_fact_rec.time_id37 ,
472 l_act_metric_fact_rec.time_id38 ,
473 l_act_metric_fact_rec.time_id39 ,
474 l_act_metric_fact_rec.time_id40 ,
475 l_act_metric_fact_rec.time_id41 ,
476 l_act_metric_fact_rec.time_id42 ,
477 l_act_metric_fact_rec.time_id43 ,
478 l_act_metric_fact_rec.time_id44 ,
479 l_act_metric_fact_rec.time_id45 ,
480 l_act_metric_fact_rec.time_id46 ,
481 l_act_metric_fact_rec.time_id47 ,
482 l_act_metric_fact_rec.time_id48 ,
483 l_act_metric_fact_rec.time_id49 ,
484 l_act_metric_fact_rec.time_id50 ,
485 l_act_metric_fact_rec.time_id51 ,
486 l_act_metric_fact_rec.time_id52 ,
487 l_act_metric_fact_rec.time_id53 ,
488 l_act_metric_fact_rec.hierarchy_id ,
489 l_act_metric_fact_rec.node_id ,
490 l_act_metric_fact_rec.level_depth ,
491 l_act_metric_fact_rec.formula_id ,
492 l_act_metric_fact_rec.from_date ,
493 l_act_metric_fact_rec.to_date ,
494 l_act_metric_fact_rec.fact_value ,
495 l_act_metric_fact_rec.fact_percent ,
496 l_act_metric_fact_rec.root_fact_id ,
497 l_act_metric_fact_rec.previous_fact_id ,
498 l_act_metric_fact_rec.fact_type ,
499 l_act_metric_fact_rec.fact_reference ,
500 l_act_metric_fact_rec.forward_buy_quantity ,
501 /* 05/21/2002 yzhao: add 11 new columns for top-down bottom-up budgeting */
502 l_act_metric_fact_rec.status_code ,
503 l_act_metric_fact_rec.hierarchy_type ,
504 l_act_metric_fact_rec.approval_date ,
505 l_act_metric_fact_rec.recommend_total_amount ,
506 l_act_metric_fact_rec.recommend_hb_amount ,
507 l_act_metric_fact_rec.request_total_amount ,
508 l_act_metric_fact_rec.request_hb_amount ,
509 l_act_metric_fact_rec.actual_total_amount ,
510 l_act_metric_fact_rec.actual_hb_amount ,
511 l_act_metric_fact_rec.base_total_pct ,
512 l_act_metric_fact_rec.base_hb_pct ,
513 /* 05/21/2002 yzhao: add ends */
514 /* 08/12/2005 mkothari: added 4 new columns for forecasting with 3rd party baseline sales */
515 l_act_metric_fact_rec.baseline_sales ,
516 l_act_metric_fact_rec.tpr_percent ,
517 l_act_metric_fact_rec.lift_factor ,
518 l_act_metric_fact_rec.incremental_sales
519 /* 08/12/2005 mkothari: add ends */
520 );
521
522 -- If any errors happen abort API.
523 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
524 RAISE FND_API.G_EXC_ERROR;
525 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
526 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
527 END IF;
528
529
530
531
532 -- finish
533
534 --
535 -- Set OUT NOCOPY value.
536 --
537 x_activity_metric_fact_id := l_act_metric_fact_rec.activity_metric_fact_id;
538
539 --
540 -- End API Body.
541 --
542
543 --
544 -- Standard check for commit request.
545 --
546 IF FND_API.To_Boolean (p_commit) THEN
547 COMMIT WORK;
548 END IF;
549
550 --
551 -- Standard API to get message count, and if 1,
552 -- set the message data OUT NOCOPY variable.
553 --
554 FND_MSG_PUB.Count_And_Get (
555 p_count => x_msg_count,
556 p_data => x_msg_data,
557 p_encoded => FND_API.G_FALSE
558 );
559
560 --
561 -- Add success message to message list.
562 --
563 IF (OZF_DEBUG_HIGH_ON) THEN
564
565 OZF_Utility_PVT.debug_message(l_full_name ||': end Success');
566 END IF;
567
568
569
570
571 EXCEPTION
572 WHEN FND_API.G_EXC_ERROR THEN
573
574
575 ROLLBACK TO Create_ActMetricFact_Pvt;
576 x_return_status := FND_API.G_RET_STS_ERROR;
577 FND_MSG_PUB.Count_And_Get (
581 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
578 p_count => x_msg_count,
579 p_data => x_msg_data
580 );
582
583
584
585 ROLLBACK TO Create_ActMetricFact_Pvt;
586 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
587 FND_MSG_PUB.Count_And_Get (
588 p_count => x_msg_count,
589 p_data => x_msg_data
590 );
591 WHEN OTHERS THEN
592
593
594 ROLLBACK TO Create_ActMetricFact_Pvt;
595 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
596 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
597 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
598 END IF;
599 FND_MSG_PUB.Count_And_Get (
600 p_count => x_msg_count,
601 p_data => x_msg_data
602 );
603 END Create_ActMetricFact;
604
605
606 -- Start of comments
607 -- NAME
608 -- Update_ActMetricFact
609 --
610 -- PURPOSE
611 -- Updates an entry in the ozf_act_metric_facts_all table for
612 -- a given activity_metric record.
613 --
614 -- NOTES
615 --
616 -- HISTORY
617 -- 18-Apr-2000 tdonohoe Created.
618 --
619 -- End of comments
620
621 PROCEDURE Update_ActMetricFact (
622 p_api_version IN NUMBER,
623 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
624 p_commit IN VARCHAR2 := FND_API.G_FALSE,
625 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
626
627 x_return_status OUT NOCOPY VARCHAR2,
628 x_msg_count OUT NOCOPY NUMBER,
629 x_msg_data OUT NOCOPY VARCHAR2,
630
631 p_act_metric_fact_rec IN act_metric_fact_rec_type
632 )
633 IS
634 L_API_VERSION CONSTANT NUMBER := 1.0;
635 L_API_NAME CONSTANT VARCHAR2(30) := 'UPDATE_ACTMETRICFACT';
636 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
637
638
639 l_return_status VARCHAR2(1);
640 l_act_metric_fact_rec act_metric_fact_rec_type := p_act_metric_fact_rec;
641 l_temp_act_metric_fact_rec act_metric_fact_rec_type ;
642 BEGIN
643
644 --
645 -- Initialize savepoint.
646 --
647 SAVEPOINT Update_ActMetricFact_Pvt;
648
649 --
650 -- Output debug message.
651 --
652 IF (OZF_DEBUG_HIGH_ON) THEN
653
654 OZF_Utility_PVT.debug_message(l_full_name||': start');
655 END IF;
656
657 --
658 -- Initialize message list if p_init_msg_list is set to TRUE.
659 --
660 IF FND_API.To_Boolean (p_init_msg_list) THEN
661 FND_MSG_PUB.Initialize;
662 END IF;
663
664 --
665 -- Standard check for API version compatibility.
666 --
667 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
668 p_api_version,
669 L_API_NAME,
670 G_PKG_NAME)
671 THEN
672 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
673 END IF;
674
675 --
676 -- Initialize API return status to success.
677 --
678 x_return_status := FND_API.G_RET_STS_SUCCESS;
679
680 --
681 -- Begin API Body
682 --
683 -- Debug Message
684
685
686 Default_ActMetricFact
687 ( p_init_msg_list => p_init_msg_list,
688 p_act_metric_fact_rec => p_act_metric_fact_rec,
689 p_validation_mode => JTF_PLSQL_API.G_UPDATE,
690 x_complete_rec => l_act_metric_fact_rec,
691 x_return_status => l_return_status,
692 x_msg_count => x_msg_count,
693 x_msg_data => x_msg_data ) ;
694 -- dbms_output.put_line(l_full_name || ' default_actmetricfact returns ' || l_return_status);
695 -- If any errors happen abort API.
696 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
697 RAISE FND_API.G_EXC_ERROR;
698 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
699 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
700 END IF;
701
702
703 IF (OZF_DEBUG_HIGH_ON) THEN
704
705
706
707
708
709 OZF_Utility_PVT.debug_message(l_full_name ||': validate');
710
711
712 END IF;
713
714 -- yzhao: 06/11/2002 complete record before validation so missed values can be filled in
715 -- replace g_miss_char/num/date with current column values
716
717 -- mgudivak: November Fifteenth.
718 -- Added NOCOPY for the out variable. Hence in and out cannot have the same name.
719
720 l_temp_act_metric_fact_rec := l_act_metric_fact_rec;
721
722 Complete_ActMetFact_Rec(p_act_metric_fact_rec => l_temp_act_metric_fact_rec,
723 x_complete_fact_rec => l_act_metric_fact_rec);
724
725
726 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
727 Validate_ActMetFact_Items(
728 p_act_metric_fact_rec => l_act_metric_fact_rec,
732 -- dbms_output.put_line(l_full_name || ' validate_items returns ' || l_return_status);
729 p_validation_mode => JTF_PLSQL_API.g_update,
730 x_return_status => l_return_status
731 );
733 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
734 RAISE FND_API.g_exc_unexpected_error;
735 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
736 RAISE FND_API.g_exc_error;
737 END IF;
738 END IF;
739
740
741 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
742 Validate_ActMetFact_Rec(
743 p_act_metric_fact_rec => p_act_metric_fact_rec,
744 p_complete_fact_rec => l_act_metric_fact_rec,
745 x_return_status => l_return_status
746 );
747 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
748 RAISE FND_API.g_exc_unexpected_error;
749 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
750 RAISE FND_API.g_exc_error;
751 END IF;
752
753 END IF;
754
755
756 IF (OZF_DEBUG_HIGH_ON) THEN
757
758
759
760
761
762 OZF_Utility_PVT.debug_message(l_full_name ||': Update Activity Metric Facts Table');
763
764
765 END IF;
766
767
768
769 Update ozf_act_metric_facts_all Set
770 object_version_number = object_version_number + 1,
771 last_update_date = SYSDATE,
772 last_updated_by = NVL(fnd_global.user_id, -1),
773 last_update_login = NVL(fnd_global.conc_login_id, -1),
774 act_metric_used_by_id = l_act_metric_fact_rec.act_metric_used_by_id,
775 arc_act_metric_used_by = l_act_metric_fact_rec.arc_act_metric_used_by,
776 value_type = l_act_metric_fact_rec.value_type,
777 activity_metric_id = l_act_metric_fact_rec.activity_metric_id,
778 activity_geo_area_id = l_act_metric_fact_rec.activity_geo_area_id,
779 activity_product_id = l_act_metric_fact_rec.activity_product_id,
780 transaction_currency_code = l_act_metric_fact_rec.transaction_currency_code,
781 trans_forecasted_value = l_act_metric_fact_rec.trans_forecasted_value,
782 base_quantity = l_act_metric_fact_rec.base_quantity,
783 functional_currency_code = l_act_metric_fact_rec.functional_currency_code,
784 func_forecasted_value = l_act_metric_fact_rec.func_forecasted_value,
785 org_id = l_act_metric_fact_rec.org_id,
786 de_metric_id = l_act_metric_fact_rec.de_metric_id,
787 de_geographic_area_id = l_act_metric_fact_rec.de_geographic_area_id,
788 de_geographic_area_type = l_act_metric_fact_rec.de_geographic_area_type,
789 de_inventory_item_id = l_act_metric_fact_rec.de_inventory_item_id,
790 de_inventory_item_org_id = l_act_metric_fact_rec.de_inventory_item_org_id,
791 time_id1 = l_act_metric_fact_rec.time_id1,
792 time_id2 = l_act_metric_fact_rec.time_id2,
793 time_id3 = l_act_metric_fact_rec.time_id3,
794 time_id4 = l_act_metric_fact_rec.time_id4,
795 time_id5 = l_act_metric_fact_rec.time_id5,
796 time_id6 = l_act_metric_fact_rec.time_id6,
797 time_id7 = l_act_metric_fact_rec.time_id7,
798 time_id8 = l_act_metric_fact_rec.time_id8,
799 time_id9 = l_act_metric_fact_rec.time_id9,
800 time_id10 = l_act_metric_fact_rec.time_id10,
801 time_id11 = l_act_metric_fact_rec.time_id11,
802 time_id12 = l_act_metric_fact_rec.time_id12,
803 time_id13 = l_act_metric_fact_rec.time_id13,
804 time_id14 = l_act_metric_fact_rec.time_id14,
805 time_id15 = l_act_metric_fact_rec.time_id15,
806 time_id16 = l_act_metric_fact_rec.time_id16,
807 time_id17 = l_act_metric_fact_rec.time_id17,
808 time_id18 = l_act_metric_fact_rec.time_id18,
809 time_id19 = l_act_metric_fact_rec.time_id19,
810 time_id20 = l_act_metric_fact_rec.time_id20,
811 time_id21 = l_act_metric_fact_rec.time_id21,
812 time_id22 = l_act_metric_fact_rec.time_id22,
813 time_id23 = l_act_metric_fact_rec.time_id23,
814 time_id24 = l_act_metric_fact_rec.time_id24,
815 time_id25 = l_act_metric_fact_rec.time_id25,
816 time_id26 = l_act_metric_fact_rec.time_id26,
817 time_id27 = l_act_metric_fact_rec.time_id27,
818 time_id28 = l_act_metric_fact_rec.time_id28,
822 time_id32 = l_act_metric_fact_rec.time_id32,
819 time_id29 = l_act_metric_fact_rec.time_id29,
820 time_id30 = l_act_metric_fact_rec.time_id30,
821 time_id31 = l_act_metric_fact_rec.time_id31,
823 time_id33 = l_act_metric_fact_rec.time_id33,
824 time_id34 = l_act_metric_fact_rec.time_id34,
825 time_id35 = l_act_metric_fact_rec.time_id35,
826 time_id36 = l_act_metric_fact_rec.time_id36,
827 time_id37 = l_act_metric_fact_rec.time_id37,
828 time_id38 = l_act_metric_fact_rec.time_id38,
829 time_id39 = l_act_metric_fact_rec.time_id39,
830 time_id40 = l_act_metric_fact_rec.time_id40,
831 time_id41 = l_act_metric_fact_rec.time_id41,
832 time_id42 = l_act_metric_fact_rec.time_id42,
833 time_id43 = l_act_metric_fact_rec.time_id43,
834 time_id44 = l_act_metric_fact_rec.time_id44,
835 time_id45 = l_act_metric_fact_rec.time_id45,
836 time_id46 = l_act_metric_fact_rec.time_id46,
837 time_id47 = l_act_metric_fact_rec.time_id47,
838 time_id48 = l_act_metric_fact_rec.time_id48,
839 time_id49 = l_act_metric_fact_rec.time_id49,
840 time_id50 = l_act_metric_fact_rec.time_id50,
841 time_id51 = l_act_metric_fact_rec.time_id51,
842 time_id52 = l_act_metric_fact_rec.time_id52,
843 time_id53 = l_act_metric_fact_rec.time_id53,
844 hierarchy_id = l_act_metric_fact_rec.hierarchy_id,
845 node_id = l_act_metric_fact_rec.node_id,
846 level_depth = l_act_metric_fact_rec.level_depth,
847 formula_id = l_act_metric_fact_rec.formula_id,
848 from_date = l_act_metric_fact_rec.from_date,
849 to_date = l_act_metric_fact_rec.to_date,
850 fact_value = l_act_metric_fact_rec.fact_value,
851 fact_percent = l_act_metric_fact_rec.fact_percent,
852 root_fact_id = l_act_metric_fact_rec.root_fact_id,
853 previous_fact_id = l_act_metric_fact_rec.previous_fact_id,
854 fact_type = l_act_metric_fact_rec.fact_type,
855 fact_reference = l_act_metric_fact_rec.fact_reference,
856 forward_buy_quantity = l_act_metric_fact_rec.forward_buy_quantity,
857 /* 05/21/2002 yzhao: add 11 new columns for top-down bottom-up budgeting */
858 status_code = l_act_metric_fact_rec.status_code,
859 hierarchy_type = l_act_metric_fact_rec.hierarchy_type,
860 approval_date = l_act_metric_fact_rec.approval_date,
861 recommend_total_amount = l_act_metric_fact_rec.recommend_total_amount,
862 recommend_hb_amount = l_act_metric_fact_rec.recommend_hb_amount,
863 request_total_amount = l_act_metric_fact_rec.request_total_amount,
864 request_hb_amount = l_act_metric_fact_rec.request_hb_amount,
865 actual_total_amount = l_act_metric_fact_rec.actual_total_amount,
866 actual_hb_amount = l_act_metric_fact_rec.actual_hb_amount,
867 base_total_pct = l_act_metric_fact_rec.base_total_pct,
868 base_hb_pct = l_act_metric_fact_rec.base_hb_pct ,
869 /* 05/21/2002 yzhao: add ends */
870 /* 08/12/2005 mkothari: added 4 new columns for forecasting with 3rd party baseline sales */
871 baseline_sales = l_act_metric_fact_rec.baseline_sales,
872 tpr_percent = l_act_metric_fact_rec.tpr_percent,
873 lift_factor = l_act_metric_fact_rec.lift_factor,
874 incremental_sales = l_act_metric_fact_rec.incremental_sales
875 /* 08/12/2005 mkothari: add ends */
876 Where activity_metric_fact_id = l_act_metric_fact_rec.activity_metric_fact_id;
877
878 IF (SQL%NOTFOUND)
879 THEN
880 --
881 -- Add error message to API message list.
882 --
883 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
884 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
885 FND_MSG_PUB.add;
886 END IF;
887 RAISE FND_API.g_exc_error;
888 END IF;
889
890
891 --
892 -- End API Body
893 --
894
895 IF FND_API.to_boolean(p_commit) THEN
896 COMMIT;
897 END IF;
898
899 --
900 -- Standard API to get message count, and if 1,
901 -- set the message data OUT NOCOPY variable.
902 --
903 FND_MSG_PUB.Count_And_Get (
904 p_count => x_msg_count,
908
905 p_data => x_msg_data,
906 p_encoded => FND_API.G_FALSE
907 );
909 --
910 -- Debug message.
911 --
912 IF (OZF_DEBUG_HIGH_ON) THEN
913
914 OZF_Utility_PVT.debug_message(l_full_name ||': end');
915 END IF;
916
917
918 EXCEPTION
919 WHEN FND_API.G_EXC_ERROR THEN
920
921
922
923 ROLLBACK TO Update_ActMetricFact_pvt;
924 x_return_status := FND_API.G_RET_STS_ERROR;
925 FND_MSG_PUB.Count_And_Get (
926 p_count => x_msg_count,
927 p_data => x_msg_data
928 );
929 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
930
931
932
933 ROLLBACK TO Update_ActMetricFact_pvt;
934 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
935 FND_MSG_PUB.Count_And_Get (
936 p_count => x_msg_count,
937 p_data => x_msg_data
938 );
939 WHEN OTHERS THEN
940
941
942
943 ROLLBACK TO Update_ActMetricFact_pvt;
944 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
945 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
946 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
947 END IF;
948 FND_MSG_PUB.Count_And_Get (
949 p_count => x_msg_count,
950 p_data => x_msg_data
951 );
952 END Update_ActMetricFact;
953
954
955 --
956 -- NAME
957 -- Complete_MetricFact_Rec
958 --
959 -- PURPOSE
960 -- Returns the Initialized Activity Metric Fact Record
961 --
962 -- NOTES
963 --
964 -- HISTORY
965 -- 21-Apr-2000 tdonohoe Created.
966 --
967 PROCEDURE Complete_ActMetFact_Rec(
968 p_act_metric_fact_rec IN act_metric_fact_rec_type,
969 x_complete_fact_rec OUT NOCOPY act_metric_fact_rec_type
970 )
971 IS
972 CURSOR c_act_metric_fact IS
973 SELECT *
974 FROM ozf_act_metric_facts_all
975 WHERE activity_metric_fact_id = p_act_metric_fact_rec.activity_metric_fact_id;
976
977 l_act_metric_fact_rec c_act_metric_fact%ROWTYPE;
978 BEGIN
979
980 x_complete_fact_rec := p_act_metric_fact_rec;
981
982 OPEN c_act_metric_fact;
983 FETCH c_act_metric_fact INTO l_act_metric_fact_rec;
984 IF c_act_metric_fact%NOTFOUND THEN
985 CLOSE c_act_metric_fact;
986 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
987 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
988 FND_MSG_PUB.add;
989 END IF;
990 RAISE FND_API.g_exc_error;
991 END IF;
992 CLOSE c_act_metric_fact;
993
994
995 IF p_act_metric_fact_rec.activity_metric_fact_id = FND_API.G_MISS_NUM THEN
996 x_complete_fact_rec.activity_metric_fact_id := NULL;
997 END IF;
998 IF p_act_metric_fact_rec.activity_metric_fact_id IS NULL THEN
999 x_complete_fact_rec.activity_metric_fact_id := l_act_metric_fact_rec.activity_metric_fact_id;
1000 END IF;
1001
1002 IF p_act_metric_fact_rec.act_metric_used_by_id = FND_API.G_MISS_NUM THEN
1003 x_complete_fact_rec.act_metric_used_by_id := NULL;
1004 END IF;
1005 IF p_act_metric_fact_rec.act_metric_used_by_id IS NULL THEN
1006 x_complete_fact_rec.act_metric_used_by_id := l_act_metric_fact_rec.act_metric_used_by_id;
1007 END IF;
1008
1009 IF p_act_metric_fact_rec.arc_act_metric_used_by = FND_API.G_MISS_CHAR THEN
1010 x_complete_fact_rec.arc_act_metric_used_by := NULL;
1011 END IF;
1012 IF p_act_metric_fact_rec.arc_act_metric_used_by IS NULL THEN
1013 x_complete_fact_rec.arc_act_metric_used_by := l_act_metric_fact_rec.arc_act_metric_used_by;
1014 END IF;
1015
1016 IF p_act_metric_fact_rec.value_type = FND_API.G_MISS_CHAR THEN
1017 x_complete_fact_rec.value_type := NULL;
1018 END IF;
1019 IF p_act_metric_fact_rec.value_type IS NULL THEN
1020 x_complete_fact_rec.value_type := l_act_metric_fact_rec.value_type;
1021 END IF;
1022
1023 IF p_act_metric_fact_rec.activity_metric_id = FND_API.G_MISS_NUM THEN
1024 x_complete_fact_rec.activity_metric_id := NULL;
1025 END IF;
1026 IF p_act_metric_fact_rec.activity_metric_id IS NULL THEN
1027 x_complete_fact_rec.activity_metric_id := l_act_metric_fact_rec.activity_metric_id;
1028 END IF;
1029
1030 IF p_act_metric_fact_rec.activity_geo_area_id = FND_API.G_MISS_NUM THEN
1031 x_complete_fact_rec.activity_geo_area_id := NULL;
1032 END IF;
1033 IF p_act_metric_fact_rec.activity_geo_area_id IS NULL THEN
1034 x_complete_fact_rec.activity_geo_area_id := l_act_metric_fact_rec.activity_geo_area_id;
1035 END IF;
1036
1037 IF p_act_metric_fact_rec.activity_product_id = FND_API.G_MISS_NUM THEN
1038 x_complete_fact_rec.activity_product_id := NULL;
1039 END IF;
1040 IF p_act_metric_fact_rec.activity_product_id IS NULL THEN
1041 x_complete_fact_rec.activity_product_id := l_act_metric_fact_rec.activity_product_id;
1042 END IF;
1043
1044 IF p_act_metric_fact_rec.transaction_currency_code = FND_API.G_MISS_CHAR THEN
1048 x_complete_fact_rec.transaction_currency_code := l_act_metric_fact_rec.transaction_currency_code;
1045 x_complete_fact_rec.transaction_currency_code := NULL;
1046 END IF;
1047 IF p_act_metric_fact_rec.transaction_currency_code IS NULL THEN
1049 END IF;
1050
1051 IF p_act_metric_fact_rec.trans_forecasted_value = FND_API.G_MISS_NUM THEN
1052 x_complete_fact_rec.trans_forecasted_value := NULL;
1053 END IF;
1054 IF p_act_metric_fact_rec.trans_forecasted_value IS NULL THEN
1055 x_complete_fact_rec.trans_forecasted_value := l_act_metric_fact_rec.trans_forecasted_value;
1056 END IF;
1057
1058 IF p_act_metric_fact_rec.base_quantity = FND_API.G_MISS_NUM THEN
1059 x_complete_fact_rec.base_quantity := NULL;
1060 END IF;
1061 IF p_act_metric_fact_rec.base_quantity IS NULL THEN
1062 x_complete_fact_rec.base_quantity := l_act_metric_fact_rec.base_quantity;
1063 END IF;
1064
1065 IF p_act_metric_fact_rec.functional_currency_code = FND_API.G_MISS_CHAR THEN
1066 x_complete_fact_rec.functional_currency_code := NULL;
1067 END IF;
1068 IF p_act_metric_fact_rec.functional_currency_code IS NULL THEN
1069 x_complete_fact_rec.functional_currency_code := l_act_metric_fact_rec.functional_currency_code;
1070 END IF;
1071
1072 IF p_act_metric_fact_rec.func_forecasted_value = FND_API.G_MISS_NUM THEN
1073 x_complete_fact_rec.func_forecasted_value := NULL;
1074 END IF;
1075 IF p_act_metric_fact_rec.func_forecasted_value IS NULL THEN
1076 x_complete_fact_rec.func_forecasted_value := l_act_metric_fact_rec.func_forecasted_value;
1077 END IF;
1078
1079 IF p_act_metric_fact_rec.org_id = FND_API.G_MISS_NUM THEN
1080 x_complete_fact_rec.org_id := NULL;
1081 END IF;
1082 IF p_act_metric_fact_rec.org_id IS NULL THEN
1083 x_complete_fact_rec.org_id := l_act_metric_fact_rec.org_id;
1084 END IF;
1085
1086 IF p_act_metric_fact_rec.de_metric_id = FND_API.G_MISS_NUM THEN
1087 x_complete_fact_rec.de_metric_id := NULL;
1088 END IF;
1089 IF p_act_metric_fact_rec.de_metric_id IS NULL THEN
1090 x_complete_fact_rec.de_metric_id := l_act_metric_fact_rec.de_metric_id;
1091 END IF;
1092
1093 IF p_act_metric_fact_rec.de_geographic_area_id = FND_API.G_MISS_NUM THEN
1094 x_complete_fact_rec.de_geographic_area_id := NULL;
1095 END IF;
1096 IF p_act_metric_fact_rec.de_geographic_area_id IS NULL THEN
1097 x_complete_fact_rec.de_geographic_area_id := l_act_metric_fact_rec.de_geographic_area_id;
1098 END IF;
1099
1100 IF p_act_metric_fact_rec.de_geographic_area_type = FND_API.G_MISS_CHAR THEN
1101 x_complete_fact_rec.de_geographic_area_type := NULL;
1102 END IF;
1103 IF p_act_metric_fact_rec.de_geographic_area_type IS NULL THEN
1104 x_complete_fact_rec.de_geographic_area_type := l_act_metric_fact_rec.de_geographic_area_type;
1105 END IF;
1106
1107 IF p_act_metric_fact_rec.de_inventory_item_id = FND_API.G_MISS_NUM THEN
1108 x_complete_fact_rec.de_inventory_item_id := NULL;
1109 END IF;
1110 IF p_act_metric_fact_rec.de_inventory_item_id IS NULL THEN
1111 x_complete_fact_rec.de_inventory_item_id := l_act_metric_fact_rec.de_inventory_item_id;
1112 END IF;
1113
1114 IF p_act_metric_fact_rec.de_inventory_item_org_id = FND_API.G_MISS_NUM THEN
1115 x_complete_fact_rec.de_inventory_item_org_id := NULL;
1116 END IF;
1117 IF p_act_metric_fact_rec.de_inventory_item_org_id IS NULL THEN
1118 x_complete_fact_rec.de_inventory_item_org_id := l_act_metric_fact_rec.de_inventory_item_org_id;
1119 END IF;
1120
1121 IF p_act_metric_fact_rec.time_id1 = FND_API.G_MISS_NUM THEN
1122 x_complete_fact_rec.time_id1 := NULL;
1123 END IF;
1124 IF p_act_metric_fact_rec.time_id1 IS NULL THEN
1125 x_complete_fact_rec.time_id1 := l_act_metric_fact_rec.time_id1;
1126 END IF;
1127
1128 IF p_act_metric_fact_rec.time_id2 = FND_API.G_MISS_NUM THEN
1129 x_complete_fact_rec.time_id2 := NULL;
1130 END IF;
1131 IF p_act_metric_fact_rec.time_id2 IS NULL THEN
1132 x_complete_fact_rec.time_id2 := l_act_metric_fact_rec.time_id2;
1133 END IF;
1134
1135 IF p_act_metric_fact_rec.time_id3 = FND_API.G_MISS_NUM THEN
1136 x_complete_fact_rec.time_id3 := NULL;
1137 END IF;
1138 IF p_act_metric_fact_rec.time_id3 IS NULL THEN
1139 x_complete_fact_rec.time_id3 := l_act_metric_fact_rec.time_id3;
1140 END IF;
1141
1142 IF p_act_metric_fact_rec.time_id4 = FND_API.G_MISS_NUM THEN
1143 x_complete_fact_rec.time_id4 := NULL;
1144 END IF;
1145 IF p_act_metric_fact_rec.time_id4 IS NULL THEN
1146 x_complete_fact_rec.time_id4 := l_act_metric_fact_rec.time_id4;
1147 END IF;
1148
1149 IF p_act_metric_fact_rec.time_id5 = FND_API.G_MISS_NUM THEN
1150 x_complete_fact_rec.time_id5 := NULL;
1151 END IF;
1152 IF p_act_metric_fact_rec.time_id5 IS NULL THEN
1153 x_complete_fact_rec.time_id5 := l_act_metric_fact_rec.time_id5;
1154 END IF;
1155
1156 IF p_act_metric_fact_rec.time_id6 = FND_API.G_MISS_NUM THEN
1157 x_complete_fact_rec.time_id6 := NULL;
1158 END IF;
1159 IF p_act_metric_fact_rec.time_id6 IS NULL THEN
1163 IF p_act_metric_fact_rec.time_id7 = FND_API.G_MISS_NUM THEN
1160 x_complete_fact_rec.time_id6 := l_act_metric_fact_rec.time_id6;
1161 END IF;
1162
1164 x_complete_fact_rec.time_id7 := NULL;
1165 END IF;
1166 IF p_act_metric_fact_rec.time_id7 IS NULL THEN
1167 x_complete_fact_rec.time_id7 := l_act_metric_fact_rec.time_id7;
1168 END IF;
1169
1170 IF p_act_metric_fact_rec.time_id8 = FND_API.G_MISS_NUM THEN
1171 x_complete_fact_rec.time_id8 := NULL;
1172 END IF;
1173 IF p_act_metric_fact_rec.time_id8 IS NULL THEN
1174 x_complete_fact_rec.time_id8 := l_act_metric_fact_rec.time_id8;
1175 END IF;
1176
1177 IF p_act_metric_fact_rec.time_id9 = FND_API.G_MISS_NUM THEN
1178 x_complete_fact_rec.time_id9 := NULL;
1179 END IF;
1180 IF p_act_metric_fact_rec.time_id9 IS NULL THEN
1181 x_complete_fact_rec.time_id9 := l_act_metric_fact_rec.time_id9;
1182 END IF;
1183
1184 IF p_act_metric_fact_rec.time_id10 = FND_API.G_MISS_NUM THEN
1185 x_complete_fact_rec.time_id10 := NULL;
1186 END IF;
1187 IF p_act_metric_fact_rec.time_id10 IS NULL THEN
1188 x_complete_fact_rec.time_id10 := l_act_metric_fact_rec.time_id10;
1189 END IF;
1190
1191 IF p_act_metric_fact_rec.time_id11 = FND_API.G_MISS_NUM THEN
1192 x_complete_fact_rec.time_id11 := NULL;
1193 END IF;
1194 IF p_act_metric_fact_rec.time_id11 IS NULL THEN
1195 x_complete_fact_rec.time_id11 := l_act_metric_fact_rec.time_id11;
1196 END IF;
1197
1198 IF p_act_metric_fact_rec.time_id12 = FND_API.G_MISS_NUM THEN
1199 x_complete_fact_rec.time_id12 := NULL;
1200 END IF;
1201 IF p_act_metric_fact_rec.time_id12 IS NULL THEN
1202 x_complete_fact_rec.time_id12 := l_act_metric_fact_rec.time_id12;
1203 END IF;
1204
1205 IF p_act_metric_fact_rec.time_id13 = FND_API.G_MISS_NUM THEN
1206 x_complete_fact_rec.time_id13 := NULL;
1207 END IF;
1208 IF p_act_metric_fact_rec.time_id13 IS NULL THEN
1209 x_complete_fact_rec.time_id13 := l_act_metric_fact_rec.time_id13;
1210 END IF;
1211
1212 IF p_act_metric_fact_rec.time_id14 = FND_API.G_MISS_NUM THEN
1213 x_complete_fact_rec.time_id14 := NULL;
1214 END IF;
1215 IF p_act_metric_fact_rec.time_id14 IS NULL THEN
1216 x_complete_fact_rec.time_id14 := l_act_metric_fact_rec.time_id14;
1217 END IF;
1218
1219 IF p_act_metric_fact_rec.time_id15 = FND_API.G_MISS_NUM THEN
1220 x_complete_fact_rec.time_id15 := NULL;
1221 END IF;
1222 IF p_act_metric_fact_rec.time_id15 IS NULL THEN
1223 x_complete_fact_rec.time_id15 := l_act_metric_fact_rec.time_id15;
1224 END IF;
1225
1226 IF p_act_metric_fact_rec.time_id16 = FND_API.G_MISS_NUM THEN
1227 x_complete_fact_rec.time_id16 := NULL;
1228 END IF;
1229 IF p_act_metric_fact_rec.time_id16 IS NULL THEN
1230 x_complete_fact_rec.time_id16 := l_act_metric_fact_rec.time_id16;
1231 END IF;
1232
1233 IF p_act_metric_fact_rec.time_id17 = FND_API.G_MISS_NUM THEN
1234 x_complete_fact_rec.time_id17 := NULL;
1235 END IF;
1236 IF p_act_metric_fact_rec.time_id17 IS NULL THEN
1237 x_complete_fact_rec.time_id17 := l_act_metric_fact_rec.time_id17;
1238 END IF;
1239
1240 IF p_act_metric_fact_rec.time_id18 = FND_API.G_MISS_NUM THEN
1241 x_complete_fact_rec.time_id18 := NULL;
1242 END IF;
1243 IF p_act_metric_fact_rec.time_id18 IS NULL THEN
1244 x_complete_fact_rec.time_id18 := l_act_metric_fact_rec.time_id18;
1245 END IF;
1246
1247 IF p_act_metric_fact_rec.time_id19 = FND_API.G_MISS_NUM THEN
1248 x_complete_fact_rec.time_id19 := NULL;
1249 END IF;
1250 IF p_act_metric_fact_rec.time_id19 IS NULL THEN
1251 x_complete_fact_rec.time_id19 := l_act_metric_fact_rec.time_id19;
1252 END IF;
1253
1254 IF p_act_metric_fact_rec.time_id20 = FND_API.G_MISS_NUM THEN
1255 x_complete_fact_rec.time_id20 := NULL;
1256 END IF;
1257 IF p_act_metric_fact_rec.time_id20 IS NULL THEN
1258 x_complete_fact_rec.time_id20 := l_act_metric_fact_rec.time_id20;
1259 END IF;
1260
1261 IF p_act_metric_fact_rec.time_id21 = FND_API.G_MISS_NUM THEN
1262 x_complete_fact_rec.time_id21 := NULL;
1263 END IF;
1264 IF p_act_metric_fact_rec.time_id21 IS NULL THEN
1265 x_complete_fact_rec.time_id21 := l_act_metric_fact_rec.time_id21;
1266 END IF;
1267
1268 IF p_act_metric_fact_rec.time_id22 = FND_API.G_MISS_NUM THEN
1269 x_complete_fact_rec.time_id22 := NULL;
1270 END IF;
1271 IF p_act_metric_fact_rec.time_id22 IS NULL THEN
1272 x_complete_fact_rec.time_id22 := l_act_metric_fact_rec.time_id22;
1273 END IF;
1274
1275 IF p_act_metric_fact_rec.time_id23 = FND_API.G_MISS_NUM THEN
1276 x_complete_fact_rec.time_id23 := NULL;
1277 END IF;
1278 IF p_act_metric_fact_rec.time_id23 IS NULL THEN
1279 x_complete_fact_rec.time_id23 := l_act_metric_fact_rec.time_id23;
1280 END IF;
1281
1282 IF p_act_metric_fact_rec.time_id24 = FND_API.G_MISS_NUM THEN
1283 x_complete_fact_rec.time_id24 := NULL;
1284 END IF;
1288
1285 IF p_act_metric_fact_rec.time_id24 IS NULL THEN
1286 x_complete_fact_rec.time_id24 := l_act_metric_fact_rec.time_id24;
1287 END IF;
1289 IF p_act_metric_fact_rec.time_id25 = FND_API.G_MISS_NUM THEN
1290 x_complete_fact_rec.time_id25 := NULL;
1291 END IF;
1292 IF p_act_metric_fact_rec.time_id25 IS NULL THEN
1293 x_complete_fact_rec.time_id25 := l_act_metric_fact_rec.time_id25;
1294 END IF;
1295
1296 IF p_act_metric_fact_rec.time_id26 = FND_API.G_MISS_NUM THEN
1297 x_complete_fact_rec.time_id26 := NULL;
1298 END IF;
1299 IF p_act_metric_fact_rec.time_id26 IS NULL THEN
1300 x_complete_fact_rec.time_id26 := l_act_metric_fact_rec.time_id26;
1301 END IF;
1302
1303 IF p_act_metric_fact_rec.time_id27 = FND_API.G_MISS_NUM THEN
1304 x_complete_fact_rec.time_id27 := NULL;
1305 END IF;
1306 IF p_act_metric_fact_rec.time_id27 IS NULL THEN
1307 x_complete_fact_rec.time_id27 := l_act_metric_fact_rec.time_id27;
1308 END IF;
1309
1310 IF p_act_metric_fact_rec.time_id28 = FND_API.G_MISS_NUM THEN
1311 x_complete_fact_rec.time_id28 := NULL;
1312 END IF;
1313 IF p_act_metric_fact_rec.time_id28 IS NULL THEN
1314 x_complete_fact_rec.time_id28 := l_act_metric_fact_rec.time_id28;
1315 END IF;
1316
1317 IF p_act_metric_fact_rec.time_id29 = FND_API.G_MISS_NUM THEN
1318 x_complete_fact_rec.time_id29 := NULL;
1319 END IF;
1320 IF p_act_metric_fact_rec.time_id29 IS NULL THEN
1321 x_complete_fact_rec.time_id29 := l_act_metric_fact_rec.time_id29;
1322 END IF;
1323
1324 IF p_act_metric_fact_rec.time_id30 = FND_API.G_MISS_NUM THEN
1325 x_complete_fact_rec.time_id30 := NULL;
1326 END IF;
1327 IF p_act_metric_fact_rec.time_id30 IS NULL THEN
1328 x_complete_fact_rec.time_id30 := l_act_metric_fact_rec.time_id30;
1329 END IF;
1330
1331 IF p_act_metric_fact_rec.time_id31 = FND_API.G_MISS_NUM THEN
1332 x_complete_fact_rec.time_id31 := NULL;
1333 END IF;
1334 IF p_act_metric_fact_rec.time_id31 IS NULL THEN
1335 x_complete_fact_rec.time_id31 := l_act_metric_fact_rec.time_id31;
1336 END IF;
1337
1338 IF p_act_metric_fact_rec.time_id32 = FND_API.G_MISS_NUM THEN
1339 x_complete_fact_rec.time_id32 := NULL;
1340 END IF;
1341 IF p_act_metric_fact_rec.time_id32 IS NULL THEN
1342 x_complete_fact_rec.time_id32 := l_act_metric_fact_rec.time_id32;
1343 END IF;
1344
1345 IF p_act_metric_fact_rec.time_id33 = FND_API.G_MISS_NUM THEN
1346 x_complete_fact_rec.time_id33 := NULL;
1347 END IF;
1348 IF p_act_metric_fact_rec.time_id33 IS NULL THEN
1349 x_complete_fact_rec.time_id33 := l_act_metric_fact_rec.time_id33;
1350 END IF;
1351
1352 IF p_act_metric_fact_rec.time_id34 = FND_API.G_MISS_NUM THEN
1353 x_complete_fact_rec.time_id34 := NULL;
1354 END IF;
1355 IF p_act_metric_fact_rec.time_id34 IS NULL THEN
1356 x_complete_fact_rec.time_id34 := l_act_metric_fact_rec.time_id34;
1357 END IF;
1358
1359 IF p_act_metric_fact_rec.time_id35 = FND_API.G_MISS_NUM THEN
1360 x_complete_fact_rec.time_id35 := NULL;
1361 END IF;
1362 IF p_act_metric_fact_rec.time_id35 IS NULL THEN
1363 x_complete_fact_rec.time_id35 := l_act_metric_fact_rec.time_id35;
1364 END IF;
1365
1366 IF p_act_metric_fact_rec.time_id36 = FND_API.G_MISS_NUM THEN
1367 x_complete_fact_rec.time_id36 := NULL;
1368 END IF;
1369 IF p_act_metric_fact_rec.time_id36 IS NULL THEN
1370 x_complete_fact_rec.time_id36 := l_act_metric_fact_rec.time_id36;
1371 END IF;
1372
1373 IF p_act_metric_fact_rec.time_id37 = FND_API.G_MISS_NUM THEN
1374 x_complete_fact_rec.time_id37 := NULL;
1375 END IF;
1376 IF p_act_metric_fact_rec.time_id37 IS NULL THEN
1377 x_complete_fact_rec.time_id37 := l_act_metric_fact_rec.time_id37;
1378 END IF;
1379
1380 IF p_act_metric_fact_rec.time_id38 = FND_API.G_MISS_NUM THEN
1381 x_complete_fact_rec.time_id38 := NULL;
1382 END IF;
1383 IF p_act_metric_fact_rec.time_id38 IS NULL THEN
1384 x_complete_fact_rec.time_id38 := l_act_metric_fact_rec.time_id38;
1385 END IF;
1386
1387 IF p_act_metric_fact_rec.time_id39 = FND_API.G_MISS_NUM THEN
1388 x_complete_fact_rec.time_id39 := NULL;
1389 END IF;
1390 IF p_act_metric_fact_rec.time_id39 IS NULL THEN
1391 x_complete_fact_rec.time_id39 := l_act_metric_fact_rec.time_id39;
1392 END IF;
1393
1394 IF p_act_metric_fact_rec.time_id40 = FND_API.G_MISS_NUM THEN
1395 x_complete_fact_rec.time_id40 := NULL;
1396 END IF;
1397 IF p_act_metric_fact_rec.time_id40 IS NULL THEN
1398 x_complete_fact_rec.time_id40 := l_act_metric_fact_rec.time_id40;
1399 END IF;
1400
1401 IF p_act_metric_fact_rec.time_id41 = FND_API.G_MISS_NUM THEN
1402 x_complete_fact_rec.time_id41 := NULL;
1403 END IF;
1404 IF p_act_metric_fact_rec.time_id41 IS NULL THEN
1405 x_complete_fact_rec.time_id41 := l_act_metric_fact_rec.time_id41;
1406 END IF;
1407
1408 IF p_act_metric_fact_rec.time_id42 = FND_API.G_MISS_NUM THEN
1409 x_complete_fact_rec.time_id42 := NULL;
1410 END IF;
1411 IF p_act_metric_fact_rec.time_id42 IS NULL THEN
1415 IF p_act_metric_fact_rec.time_id43 = FND_API.G_MISS_NUM THEN
1412 x_complete_fact_rec.time_id42 := l_act_metric_fact_rec.time_id42;
1413 END IF;
1414
1416 x_complete_fact_rec.time_id43 := NULL;
1417 END IF;
1418 IF p_act_metric_fact_rec.time_id43 IS NULL THEN
1419 x_complete_fact_rec.time_id43 := l_act_metric_fact_rec.time_id43;
1420 END IF;
1421
1422 IF p_act_metric_fact_rec.time_id44 = FND_API.G_MISS_NUM THEN
1423 x_complete_fact_rec.time_id44 := NULL;
1424 END IF;
1425 IF p_act_metric_fact_rec.time_id44 IS NULL THEN
1426 x_complete_fact_rec.time_id44 := l_act_metric_fact_rec.time_id44;
1427 END IF;
1428
1429 IF p_act_metric_fact_rec.time_id45 = FND_API.G_MISS_NUM THEN
1430 x_complete_fact_rec.time_id45 := NULL;
1431 END IF;
1432 IF p_act_metric_fact_rec.time_id45 IS NULL THEN
1433 x_complete_fact_rec.time_id45 := l_act_metric_fact_rec.time_id45;
1434 END IF;
1435
1436 IF p_act_metric_fact_rec.time_id46 = FND_API.G_MISS_NUM THEN
1437 x_complete_fact_rec.time_id46 := NULL;
1438 END IF;
1439 IF p_act_metric_fact_rec.time_id46 IS NULL THEN
1440 x_complete_fact_rec.time_id46 := l_act_metric_fact_rec.time_id46;
1441 END IF;
1442
1443 IF p_act_metric_fact_rec.time_id47 = FND_API.G_MISS_NUM THEN
1444 x_complete_fact_rec.time_id47 := NULL;
1445 END IF;
1446 IF p_act_metric_fact_rec.time_id47 IS NULL THEN
1447 x_complete_fact_rec.time_id47 := l_act_metric_fact_rec.time_id47;
1448 END IF;
1449
1450 IF p_act_metric_fact_rec.time_id48 = FND_API.G_MISS_NUM THEN
1451 x_complete_fact_rec.time_id48 := NULL;
1452 END IF;
1453 IF p_act_metric_fact_rec.time_id48 IS NULL THEN
1454 x_complete_fact_rec.time_id48 := l_act_metric_fact_rec.time_id48;
1455 END IF;
1456
1457 IF p_act_metric_fact_rec.time_id49 = FND_API.G_MISS_NUM THEN
1458 x_complete_fact_rec.time_id49 := NULL;
1459 END IF;
1460 IF p_act_metric_fact_rec.time_id49 IS NULL THEN
1461 x_complete_fact_rec.time_id49 := l_act_metric_fact_rec.time_id49;
1462 END IF;
1463
1464 IF p_act_metric_fact_rec.time_id50 = FND_API.G_MISS_NUM THEN
1465 x_complete_fact_rec.time_id50 := NULL;
1466 END IF;
1467 IF p_act_metric_fact_rec.time_id50 IS NULL THEN
1468 x_complete_fact_rec.time_id50 := l_act_metric_fact_rec.time_id50;
1469 END IF;
1470
1471 IF p_act_metric_fact_rec.time_id51 = FND_API.G_MISS_NUM THEN
1472 x_complete_fact_rec.time_id51 := NULL;
1473 END IF;
1474 IF p_act_metric_fact_rec.time_id51 IS NULL THEN
1475 x_complete_fact_rec.time_id51 := l_act_metric_fact_rec.time_id51;
1476 END IF;
1477
1478 IF p_act_metric_fact_rec.time_id52 = FND_API.G_MISS_NUM THEN
1479 x_complete_fact_rec.time_id52 := NULL;
1480 END IF;
1481 IF p_act_metric_fact_rec.time_id52 IS NULL THEN
1482 x_complete_fact_rec.time_id52 := l_act_metric_fact_rec.time_id52;
1483 END IF;
1484
1485 IF p_act_metric_fact_rec.time_id53 = FND_API.G_MISS_NUM THEN
1486 x_complete_fact_rec.time_id53 := NULL;
1487 END IF;
1488 IF p_act_metric_fact_rec.time_id53 IS NULL THEN
1489 x_complete_fact_rec.time_id53 := l_act_metric_fact_rec.time_id53;
1490 END IF;
1491
1492 IF p_act_metric_fact_rec.hierarchy_id = FND_API.G_MISS_NUM THEN
1493 x_complete_fact_rec.hierarchy_id := NULL;
1494 END IF;
1495 IF p_act_metric_fact_rec.hierarchy_id IS NULL THEN
1496 x_complete_fact_rec.hierarchy_id := l_act_metric_fact_rec.hierarchy_id;
1497 END IF;
1498
1499 IF p_act_metric_fact_rec.node_id = FND_API.G_MISS_NUM THEN
1500 x_complete_fact_rec.node_id := NULL;
1501 END IF;
1502 IF p_act_metric_fact_rec.node_id IS NULL THEN
1503 x_complete_fact_rec.node_id := l_act_metric_fact_rec.node_id;
1504 END IF;
1505
1506 IF p_act_metric_fact_rec.level_depth = FND_API.G_MISS_NUM THEN
1507 x_complete_fact_rec.level_depth := NULL;
1508 END IF;
1509 IF p_act_metric_fact_rec.level_depth IS NULL THEN
1510 x_complete_fact_rec.level_depth := l_act_metric_fact_rec.level_depth;
1511 END IF;
1512
1513 IF p_act_metric_fact_rec.formula_id = FND_API.G_MISS_NUM THEN
1514 x_complete_fact_rec.formula_id := NULL;
1515 END IF;
1516 IF p_act_metric_fact_rec.formula_id IS NULL THEN
1517 x_complete_fact_rec.formula_id := l_act_metric_fact_rec.formula_id;
1518 END IF;
1519
1520 IF p_act_metric_fact_rec.from_date = FND_API.G_MISS_DATE THEN
1521 x_complete_fact_rec.from_date := NULL;
1522 END IF;
1523 IF p_act_metric_fact_rec.from_date IS NULL THEN
1524 x_complete_fact_rec.from_date := l_act_metric_fact_rec.from_date;
1525 END IF;
1526
1527 IF p_act_metric_fact_rec.to_date = FND_API.G_MISS_DATE THEN
1528 x_complete_fact_rec.to_date := NULL;
1529 END IF;
1530 IF p_act_metric_fact_rec.to_date IS NULL THEN
1531 x_complete_fact_rec.to_date := l_act_metric_fact_rec.to_date;
1532 END IF;
1533
1534 IF p_act_metric_fact_rec.fact_value = FND_API.G_MISS_NUM THEN
1535 x_complete_fact_rec.fact_value := NULL;
1536 END IF;
1537 IF p_act_metric_fact_rec.fact_value IS NULL THEN
1538 x_complete_fact_rec.fact_value := l_act_metric_fact_rec.fact_value;
1539 END IF;
1540
1544 IF p_act_metric_fact_rec.fact_percent IS NULL THEN
1541 IF p_act_metric_fact_rec.fact_percent = FND_API.G_MISS_NUM THEN
1542 x_complete_fact_rec.fact_percent := NULL;
1543 END IF;
1545 x_complete_fact_rec.fact_percent := l_act_metric_fact_rec.fact_percent;
1546 END IF;
1547
1548 IF p_act_metric_fact_rec.root_fact_id = FND_API.G_MISS_NUM THEN
1549 x_complete_fact_rec.root_fact_id := NULL;
1550 END IF;
1551 IF p_act_metric_fact_rec.root_fact_id IS NULL THEN
1552 x_complete_fact_rec.root_fact_id := l_act_metric_fact_rec.root_fact_id;
1553 END IF;
1554
1555 IF p_act_metric_fact_rec.previous_fact_id = FND_API.G_MISS_NUM THEN
1556 x_complete_fact_rec.previous_fact_id := NULL;
1557 END IF;
1558 IF p_act_metric_fact_rec.previous_fact_id IS NULL THEN
1559 x_complete_fact_rec.previous_fact_id := l_act_metric_fact_rec.previous_fact_id;
1560 END IF;
1561
1562 IF p_act_metric_fact_rec.fact_type = FND_API.G_MISS_CHAR THEN
1563 x_complete_fact_rec.fact_type := NULL;
1564 END IF;
1565 IF p_act_metric_fact_rec.fact_type IS NULL THEN
1566 x_complete_fact_rec.fact_type := l_act_metric_fact_rec.fact_type;
1567 END IF;
1568
1569 IF p_act_metric_fact_rec.fact_reference = FND_API.G_MISS_CHAR THEN
1570 x_complete_fact_rec.fact_reference := NULL;
1571 END IF;
1572 IF p_act_metric_fact_rec.fact_reference IS NULL THEN
1573 x_complete_fact_rec.fact_reference := l_act_metric_fact_rec.fact_reference;
1574 END IF;
1575
1576 IF p_act_metric_fact_rec.forward_buy_quantity = FND_API.G_MISS_NUM THEN
1577 x_complete_fact_rec.forward_buy_quantity := NULL;
1578 END IF;
1579 IF p_act_metric_fact_rec.forward_buy_quantity IS NULL THEN
1580 x_complete_fact_rec.forward_buy_quantity := l_act_metric_fact_rec.forward_buy_quantity;
1581 END IF;
1582
1583 /* 05/21/2002 yzhao: add 11 new columns for top-down bottom-up budgeting */
1584 IF p_act_metric_fact_rec.status_code = FND_API.G_MISS_CHAR THEN
1585 x_complete_fact_rec.status_code := NULL;
1586 END IF;
1587 IF p_act_metric_fact_rec.status_code IS NULL THEN
1588 x_complete_fact_rec.status_code := l_act_metric_fact_rec.status_code;
1589 END IF;
1590
1591 IF p_act_metric_fact_rec.hierarchy_type = FND_API.G_MISS_CHAR THEN
1592 x_complete_fact_rec.hierarchy_type := NULL;
1593 END IF;
1594 IF p_act_metric_fact_rec.hierarchy_type IS NULL THEN
1595 x_complete_fact_rec.hierarchy_type := l_act_metric_fact_rec.hierarchy_type;
1596 END IF;
1597
1598 IF p_act_metric_fact_rec.approval_date = FND_API.G_MISS_DATE THEN
1599 x_complete_fact_rec.approval_date := NULL;
1600 END IF;
1601 IF p_act_metric_fact_rec.approval_date IS NULL THEN
1602 x_complete_fact_rec.approval_date := l_act_metric_fact_rec.approval_date;
1603 END IF;
1604
1605 IF p_act_metric_fact_rec.recommend_total_amount = FND_API.G_MISS_NUM THEN
1606 x_complete_fact_rec.recommend_total_amount := NULL;
1607 END IF;
1608 IF p_act_metric_fact_rec.recommend_total_amount IS NULL THEN
1609 x_complete_fact_rec.recommend_total_amount := l_act_metric_fact_rec.recommend_total_amount;
1610 END IF;
1611
1612 IF p_act_metric_fact_rec.recommend_hb_amount = FND_API.G_MISS_NUM THEN
1613 x_complete_fact_rec.recommend_hb_amount := NULL;
1614 END IF;
1615 IF p_act_metric_fact_rec.recommend_hb_amount IS NULL THEN
1616 x_complete_fact_rec.recommend_hb_amount := l_act_metric_fact_rec.recommend_hb_amount;
1617 END IF;
1618
1619 IF p_act_metric_fact_rec.request_total_amount = FND_API.G_MISS_NUM THEN
1620 x_complete_fact_rec.request_total_amount := NULL;
1621 END IF;
1622 IF p_act_metric_fact_rec.request_total_amount IS NULL THEN
1623 x_complete_fact_rec.request_total_amount := l_act_metric_fact_rec.request_total_amount;
1624 END IF;
1625
1626 IF p_act_metric_fact_rec.request_hb_amount = FND_API.G_MISS_NUM THEN
1627 x_complete_fact_rec.request_hb_amount := NULL;
1628 END IF;
1629 IF p_act_metric_fact_rec.request_hb_amount IS NULL THEN
1630 x_complete_fact_rec.request_hb_amount := l_act_metric_fact_rec.request_hb_amount;
1631 END IF;
1632
1633 IF p_act_metric_fact_rec.actual_total_amount = FND_API.G_MISS_NUM THEN
1634 x_complete_fact_rec.actual_total_amount := NULL;
1635 END IF;
1636 IF p_act_metric_fact_rec.actual_total_amount IS NULL THEN
1637 x_complete_fact_rec.actual_total_amount := l_act_metric_fact_rec.actual_total_amount;
1638 END IF;
1639
1640 IF p_act_metric_fact_rec.actual_hb_amount = FND_API.G_MISS_NUM THEN
1641 x_complete_fact_rec.actual_hb_amount := NULL;
1642 END IF;
1643 IF p_act_metric_fact_rec.actual_hb_amount IS NULL THEN
1644 x_complete_fact_rec.actual_hb_amount := l_act_metric_fact_rec.actual_hb_amount;
1645 END IF;
1646
1647 IF p_act_metric_fact_rec.base_total_pct = FND_API.G_MISS_NUM THEN
1648 x_complete_fact_rec.base_total_pct := NULL;
1649 END IF;
1650 IF p_act_metric_fact_rec.base_total_pct IS NULL THEN
1651 x_complete_fact_rec.base_total_pct := l_act_metric_fact_rec.base_total_pct;
1652 END IF;
1653
1654 IF p_act_metric_fact_rec.base_hb_pct = FND_API.G_MISS_NUM THEN
1658 x_complete_fact_rec.base_hb_pct := l_act_metric_fact_rec.base_hb_pct;
1655 x_complete_fact_rec.base_hb_pct := NULL;
1656 END IF;
1657 IF p_act_metric_fact_rec.base_hb_pct IS NULL THEN
1659 END IF;
1660 /* 05/21/2002 yzhao: add ends */
1661
1662 /* 08/12/2005 mkothari: added 4 new columns for forecasting with 3rd party baseline sales */
1663 IF p_act_metric_fact_rec.baseline_sales = FND_API.G_MISS_NUM THEN
1664 x_complete_fact_rec.baseline_sales := NULL;
1665 END IF;
1666 IF p_act_metric_fact_rec.baseline_sales IS NULL THEN
1667 x_complete_fact_rec.baseline_sales := l_act_metric_fact_rec.baseline_sales;
1668 END IF;
1669
1670 IF p_act_metric_fact_rec.tpr_percent = FND_API.G_MISS_NUM THEN
1671 x_complete_fact_rec.tpr_percent := NULL;
1672 END IF;
1673 IF p_act_metric_fact_rec.tpr_percent IS NULL THEN
1674 x_complete_fact_rec.tpr_percent := l_act_metric_fact_rec.tpr_percent;
1675 END IF;
1676
1677 IF p_act_metric_fact_rec.lift_factor = FND_API.G_MISS_NUM THEN
1678 x_complete_fact_rec.lift_factor := NULL;
1679 END IF;
1680 IF p_act_metric_fact_rec.lift_factor IS NULL THEN
1681 x_complete_fact_rec.lift_factor := l_act_metric_fact_rec.lift_factor;
1682 END IF;
1683
1684 IF p_act_metric_fact_rec.incremental_sales = FND_API.G_MISS_NUM THEN
1685 x_complete_fact_rec.incremental_sales := NULL;
1686 END IF;
1687 IF p_act_metric_fact_rec.incremental_sales IS NULL THEN
1688 x_complete_fact_rec.incremental_sales := l_act_metric_fact_rec.incremental_sales;
1689 END IF;
1690 /* 08/12/2005 mkothari: add ends */
1691
1692 END Complete_ActMetFact_Rec ;
1693
1694
1695 -- Start of comments
1696 -- NAME
1697 -- Validate_ActMetFact
1698 --
1699 -- PURPOSE
1700 -- Validation API for Activity metric facts table.
1701 --
1702
1703 -- NOTES
1704 --
1705 -- HISTORY
1706 -- 24-Apr-2000 tdonohoe Created.
1707
1708 --
1709 -- End of comments
1710
1711 PROCEDURE Validate_ActMetFact (
1712 p_api_version IN NUMBER,
1713 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1714 p_validation_level IN NUMBER := FND_API.G_Valid_Level_Full,
1715
1716 x_return_status OUT NOCOPY VARCHAR2,
1717 x_msg_count OUT NOCOPY NUMBER,
1718 x_msg_data OUT NOCOPY VARCHAR2,
1719
1720 p_act_metric_fact_rec IN act_metric_fact_rec_type
1721 )
1722 IS
1723 L_API_VERSION CONSTANT NUMBER := 1.0;
1724 L_API_NAME CONSTANT VARCHAR2(30) := 'VALIDATE_ACTMETRICFACT';
1725 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1726
1727
1728 l_return_status VARCHAR2(1);
1729
1730 BEGIN
1731 --
1732 -- Output debug message.
1733 --
1734 IF (OZF_DEBUG_HIGH_ON) THEN
1735
1736 OZF_Utility_PVT.debug_message(l_full_name||': start');
1737 END IF;
1738
1739 --
1740 -- Initialize message list if p_init_msg_list is set to TRUE.
1741 --
1742 IF FND_API.To_Boolean (p_init_msg_list) THEN
1743 FND_MSG_PUB.Initialize;
1744 END IF;
1745
1746 --
1747 -- Standard check for API version compatibility.
1748 --
1749 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
1750 p_api_version,
1751 L_API_NAME,
1752 G_PKG_NAME)
1753 THEN
1754 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1755 END IF;
1756
1757 --
1758 -- Initialize API return status to success.
1759 --
1760 x_return_status := FND_API.G_RET_STS_SUCCESS;
1761
1762 --
1763 -- Begin API Body.
1764 --
1765
1766 IF (OZF_DEBUG_HIGH_ON) THEN
1767
1768
1769
1770 OZF_Utility_PVT.debug_message(l_full_name||': Validate items');
1771
1772 END IF;
1773
1774
1775
1776 -- Validate required items in the record.
1777 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1778
1779 Validate_ActMetFact_Items(
1780 p_act_metric_fact_rec => p_act_metric_fact_rec,
1781 p_validation_mode => JTF_PLSQL_API.g_create,
1782 x_return_status => l_return_status
1783 );
1784
1785 -- If any errors happen abort API.
1786 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1787 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1788 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1789 RAISE FND_API.G_EXC_ERROR;
1790 END IF;
1791 END IF;
1792
1793 IF (OZF_DEBUG_HIGH_ON) THEN
1794
1795
1796
1797 OZF_Utility_PVT.debug_message(l_full_name||': check record');
1798
1799 END IF;
1800
1801
1802
1803
1804 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
1805 Validate_ActMetFact_Rec(
1806 p_act_metric_fact_rec => p_act_metric_fact_rec,
1807 p_complete_fact_rec => NULL,
1811 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1808 x_return_status => l_return_status
1809 );
1810
1812 RAISE FND_API.g_exc_unexpected_error;
1813 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1814 RAISE FND_API.g_exc_error;
1815 END IF;
1816 END IF;
1817
1818
1819
1820 --
1821 -- End API Body.
1822 --
1823
1824 --
1825 -- Standard API to get message count, and if 1,
1826 -- set the message data OUT NOCOPY variable.
1827 --
1828 FND_MSG_PUB.Count_And_Get (
1829 p_count => x_msg_count,
1830 p_data => x_msg_data,
1831 p_encoded => FND_API.G_FALSE
1832 );
1833
1834
1835
1836 IF (OZF_DEBUG_HIGH_ON) THEN
1837
1838
1839
1840
1841
1842
1843
1844 OZF_Utility_PVT.debug_message(l_full_name ||': end');
1845
1846
1847
1848 END IF;
1849
1850
1851
1852 EXCEPTION
1853 WHEN FND_API.G_EXC_ERROR THEN
1854 x_return_status := FND_API.G_RET_STS_ERROR;
1855 FND_MSG_PUB.Count_And_Get (
1856 p_count => x_msg_count,
1857 p_data => x_msg_data
1858 );
1859 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1860 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1861 FND_MSG_PUB.Count_And_Get (
1862 p_count => x_msg_count,
1863 p_data => x_msg_data
1864 );
1865 WHEN OTHERS THEN
1866 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1867 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1868 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
1869 END IF;
1870 FND_MSG_PUB.Count_And_Get (
1871 p_count => x_msg_count,
1872 p_data => x_msg_data
1873 );
1874 END Validate_ActMetFact;
1875
1876
1877 -- Start of comments.
1878 --
1879 -- NAME
1880 -- Check_Req_ActMetricFact_Items
1881 --
1882 -- PURPOSE
1883 -- Validate required metric fact items.
1884 --
1885 -- NOTES
1886 --
1887 -- HISTORY
1888 -- 24-Apr-2000 tdonohoe Created.
1889 --
1890 -- End of comments.
1891
1892 PROCEDURE Check_Req_ActMetricFact_Items (
1893 p_act_metric_fact_rec IN act_metric_fact_rec_type,
1894 x_return_status OUT NOCOPY VARCHAR2
1895 )
1896 IS
1897 BEGIN
1898 -- Initialize return status to success.
1899 x_return_status := FND_API.G_RET_STS_SUCCESS;
1900
1901 --ACT_METRIC_USED_BY_ID
1902
1903
1904
1905
1906 IF p_act_metric_fact_rec.act_metric_used_by_id IS NULL
1907 THEN
1908 -- missing required fields
1909 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1910 THEN -- MMSG
1911 FND_MESSAGE.Set_Name('OZF', 'OZF_METR_MISSING_ARC_USED_FOR');
1912 FND_MSG_PUB.Add;
1913 END IF;
1914
1915 x_return_status := FND_API.G_RET_STS_ERROR;
1916
1917 -- If any error happens abort API.
1918 RETURN;
1919 END IF;
1920
1921 --ARC_ACT_METRIC_USED_BY
1922
1923
1924
1925 IF p_act_metric_fact_rec.arc_act_metric_used_by IS NULL
1926 THEN
1927 -- missing required fields
1928 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1929 THEN -- MMSG
1930 FND_MESSAGE.Set_Name('OZF', 'OZF_METR_MISSING_ARC_USED_FOR');
1931 FND_MSG_PUB.Add;
1932 END IF;
1933
1934 x_return_status := FND_API.G_RET_STS_ERROR;
1935
1936 -- If any error happens abort API.
1937 RETURN;
1938 END IF;
1939
1940 --VALUE_TYPE
1941
1942
1943
1944 IF p_act_metric_fact_rec.value_type IS NULL
1945 THEN
1946 -- missing required fields
1947 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1948 THEN -- MMSG
1949 FND_MESSAGE.Set_Name('OZF', 'OZF_METR_MISSING_VAL_TYPE');
1950 FND_MSG_PUB.Add;
1951 END IF;
1952
1953 x_return_status := FND_API.G_RET_STS_ERROR;
1954
1955 -- If any error happens abort API.
1956 RETURN;
1957 END IF;
1958
1959 --ACTIVITY_METRIC_ID
1960
1961
1962
1963
1964 IF p_act_metric_fact_rec.activity_metric_id IS NULL
1965 THEN
1966 -- missing required fields
1967 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1968 THEN -- MMSG
1969 FND_MESSAGE.Set_Name('OZF', 'OZF_METR_MISSING_ACT_METRIC_ID');
1970 FND_MSG_PUB.Add;
1971 END IF;
1972
1973 x_return_status := FND_API.G_RET_STS_ERROR;
1974
1975 -- If any error happens abort API.
1976 RETURN;
1977 END IF;
1978
1979 --TRANS_FORECASTED_VALUE
1980
1981
1982
1983 IF p_act_metric_fact_rec.trans_forecasted_value IS NULL
1984 THEN
1985 -- missing required fields
1989 FND_MSG_PUB.Add;
1986 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1987 THEN -- MMSG
1988 FND_MESSAGE.Set_Name('OZF', 'OZF_METR_MISSING_TRAN_FCST_VAL');
1990 END IF;
1991
1992 x_return_status := FND_API.G_RET_STS_ERROR;
1993
1994 -- If any error happens abort API.
1995 RETURN;
1996 END IF;
1997
1998
1999
2000
2001 --FUNCTIONAL_CURRENCY_CODE
2002 IF p_act_metric_fact_rec.functional_currency_code IS NULL
2003 THEN
2004 -- missing required fields
2005 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2006 THEN -- MMSG
2007 FND_MESSAGE.Set_Name('OZF', 'OZF_METR_MISSING_FUNC_CUR_CODE');
2008 FND_MSG_PUB.Add;
2009 END IF;
2010
2011 x_return_status := FND_API.G_RET_STS_ERROR;
2012
2013 -- If any error happens abort API.
2014 RETURN;
2015 END IF;
2016
2017 --FUNC_FORECASTED_VALUE
2018
2019
2020
2021 IF p_act_metric_fact_rec.func_forecasted_value IS NULL
2022 THEN
2023 -- missing required fields
2024 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2025 THEN -- MMSG
2026 FND_MESSAGE.Set_Name('OZF', 'OZF_METR_MISSING_FUNC_FCST_VAL');
2027 FND_MSG_PUB.Add;
2028 END IF;
2029
2030 x_return_status := FND_API.G_RET_STS_ERROR;
2031
2032 -- If any error happens abort API.
2033 RETURN;
2034 END IF;
2035
2036 --DE_METRIC_ID
2037
2038
2039
2040
2041 IF p_act_metric_fact_rec.de_metric_id IS NULL
2042 THEN
2043 -- missing required fields
2044 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2045 THEN -- MMSG
2046 FND_MESSAGE.Set_Name('OZF', 'OZF_METR_MISSING_METRIC_ID');
2047 FND_MSG_PUB.Add;
2048 END IF;
2049
2050 x_return_status := FND_API.G_RET_STS_ERROR;
2051
2052 -- If any error happens abort API.
2053 RETURN;
2054 END IF;
2055
2056 --TIME_ID1
2057
2058
2059
2060
2061 IF p_act_metric_fact_rec.time_id1 IS NULL
2062 THEN
2063 -- missing required fields
2064 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2065 THEN -- MMSG
2066 FND_MESSAGE.Set_Name('OZF', 'OZF_METR_MISSING_TIME_ID1');
2067 FND_MSG_PUB.Add;
2068 END IF;
2069
2070 x_return_status := FND_API.G_RET_STS_ERROR;
2071
2072 -- If any error happens abort API.
2073 RETURN;
2074 END IF;
2075
2076
2077
2078 -------------------------------------------------------------------------------------
2079 --When a Hierarchy Id is present then the node_id and fact_value fields are mandatory
2080 -------------------------------------------------------------------------------------
2081 IF p_act_metric_fact_rec.hierarchy_id IS NOT NULL AND p_act_metric_fact_rec.hierarchy_id <> FND_API.G_MISS_NUM
2082 THEN
2083
2084
2085
2086
2087
2088
2089 IF p_act_metric_fact_rec.node_id IS NULL
2090 THEN
2091
2092
2093
2094 -- missing required fields
2095 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2096 THEN -- MMSG
2097 FND_MESSAGE.Set_Name('OZF', 'OZF_METR_MISSING_NODE_ID');
2098 FND_MSG_PUB.Add;
2099 END IF;
2100
2101 x_return_status := FND_API.G_RET_STS_ERROR;
2102
2103 -- If any error happens abort API.
2104 RETURN;
2105 END IF;
2106
2107
2108
2109
2110 IF p_act_metric_fact_rec.fact_value IS NULL
2111 THEN
2112 -- missing required fields
2113 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2114 THEN -- MMSG
2115 FND_MESSAGE.Set_Name('OZF', 'OZF_METR_MISSING_FACT_VAL');
2116 FND_MSG_PUB.Add;
2117 END IF;
2118
2119 x_return_status := FND_API.G_RET_STS_ERROR;
2120
2121 -- If any error happens abort API.
2122 RETURN;
2123 END IF;
2124
2125 END IF;
2126
2127 EXCEPTION
2128 WHEN OTHERS THEN
2129 RAISE;
2130 END Check_Req_ActMetricFact_Items;
2131
2132
2133 --
2134 -- Start of comments.
2135 --
2136 -- NAME
2137 -- Check_ActMetricFact_UK_Items
2138 --
2139 -- PURPOSE
2140 -- Perform Uniqueness check for Activity metric facts.
2141 --
2142 -- NOTES
2143 --
2144 -- HISTORY
2145 -- 24-Apr-2000 tdonohoe Created.
2146 -- End of comments.
2147
2148
2149 PROCEDURE Check_ActMetricFact_UK_Items(
2150 p_act_metric_fact_rec IN act_metric_fact_rec_type,
2151 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
2152 x_return_status OUT NOCOPY VARCHAR2
2153 )
2154 IS
2155 l_where_clause VARCHAR2(2000); -- Used By Check_Uniqueness
2156 BEGIN
2157
2158 x_return_status := FND_API.g_ret_sts_success;
2159
2160 -- For Create_ActMetricFact, when activity_metric_fact_id is passed in, we need to
2164 THEN
2161 -- check if this activity_metric_id is unique.
2162 IF p_validation_mode = JTF_PLSQL_API.g_create
2163 AND p_act_metric_fact_rec.activity_metric_fact_id IS NOT NULL
2165
2166 l_where_clause := ' activity_metric_fact_id = '||p_act_metric_fact_rec.activity_metric_fact_id ;
2167
2168 IF OZF_Utility_PVT.Check_Uniqueness(
2169 p_table_name => 'ozf_act_metric_facts_all',
2170 p_where_clause => l_where_clause
2171 ) = FND_API.g_false
2172 THEN
2173 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2174 THEN
2175 FND_MESSAGE.set_name('OZF', 'OZF_METR_FACT_DUP_ID');
2176 FND_MSG_PUB.add;
2177 END IF;
2178 x_return_status := FND_API.g_ret_sts_error;
2179 RETURN;
2180 END IF;
2181 END IF;
2182
2183
2184 END Check_ActMetricFact_Uk_Items;
2185
2186
2187 --
2188 -- Start of comments.
2189 --
2190 -- NAME
2191 -- Check_ActMetricFact_Items
2192 --
2193 -- PURPOSE
2194 -- Perform item level validation for Activity metric facts.
2195 --
2196 -- NOTES
2197 --
2198 -- HISTORY
2199 -- 24-Apr-2000 tdonohoe Created.
2200 -- 28-Jun 2000 tdonohoe Modified Check_ActMetricFact_Items to allow the same node to appear on a
2201 -- hierarchy combined with a unique formula_id.
2202 -- End of comments.
2203
2204 PROCEDURE Check_ActMetricFact_Items (
2205 p_act_metric_fact_rec IN act_metric_fact_rec_type,
2206 x_return_status OUT NOCOPY VARCHAR2
2207 )
2208 IS
2209 l_item_name VARCHAR2(30); -- Used to standardize error messages.
2210 l_act_metric_fact_rec act_metric_fact_rec_type := p_act_metric_fact_rec;
2211 l_return_status VARCHAR2(1);
2212
2213
2214 l_table_name VARCHAR2(30);
2215 l_pk_name VARCHAR2(30);
2216 l_pk_value VARCHAR2(30);
2217 l_pk_data_type VARCHAR2(30);
2218 l_additional_where_clause VARCHAR2(4000); -- Used by Check_FK_Exists.
2219 l_lookup_type VARCHAR2(30);
2220
2221 CURSOR c_hierarchy_node_check(p_hierarchy_id number,p_node_id number,p_act_metric_fact_id number,p_act_metric_id number,p_formula_id number) IS
2222 SELECT 1 from ozf_act_metric_facts_all
2223 WHERE hierarchy_id = p_hierarchy_id
2224 AND node_id = p_node_id
2225 AND formula_id = p_formula_id
2226 AND activity_metric_fact_id <> p_act_metric_fact_id
2227 AND activity_metric_id = p_act_metric_id;
2228
2229 l_fact_exists number;
2230
2231
2232 BEGIN
2233 -- Initialize return status to success.
2234 x_return_status := FND_API.G_RET_STS_SUCCESS;
2235
2236
2237
2238 -- ACTIVITY_METRIC_ID
2239 -- Do not validate FK if NULL
2240
2241 IF l_act_metric_fact_rec.activity_metric_id <> FND_API.G_MISS_NUM THEN
2242 l_table_name := 'OZF_ACT_METRICS_ALL';
2243 l_pk_name := 'ACTIVITY_METRIC_ID';
2244 l_pk_value := l_act_metric_fact_rec.activity_metric_id;
2245 l_pk_data_type := OZF_Utility_PVT.G_NUMBER;
2246 l_additional_where_clause := NULL ;
2247
2248 IF OZF_Utility_PVT.Check_FK_Exists (
2249 p_table_name => l_table_name
2250 ,p_pk_name => l_pk_name
2251 ,p_pk_value => l_pk_value
2252 ,p_pk_data_type => l_pk_data_type
2253 ,p_additional_where_clause => l_additional_where_clause
2254 ) = FND_API.G_FALSE
2255 THEN
2256 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2257 THEN
2258 FND_MESSAGE.Set_Name('OZF', 'OZF_METR_INVALID_MET');
2259 FND_MSG_PUB.Add;
2260 END IF;
2261
2262 x_return_status := FND_API.G_RET_STS_ERROR;
2263 RETURN;
2264 END IF; -- Check_FK_Exists
2265
2266 END IF;
2267
2268
2269
2270 -- ARC_ACT_METRIC_USED_BY
2271 IF l_act_metric_fact_rec.arc_act_metric_used_by <> FND_API.G_MISS_CHAR THEN
2272 IF l_act_metric_fact_rec.arc_act_metric_used_by NOT IN
2273 ('CAMP','CSCH','EVEH','EVEO','DELV','FUND','FCST')
2274 THEN
2275 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2276 FND_MESSAGE.Set_Name ('OZF', 'OZF_METR_INVALID_USED_BY');
2277 FND_MSG_PUB.Add;
2278 END IF;
2279
2280 x_return_status := FND_API.G_RET_STS_ERROR;
2281 RETURN;
2282 END IF;
2283 END IF;
2284
2285 -----------------------------------------------------------------------
2286 --End of Comments --
2287 -----------------------------------------------------------------------
2288 ----------------------------------------------------------------------------------
2289 --When a Hierarchy and Node are specified then a check must be done to verify
2290 --that the node is unique in the set of result entries for this activity metric.
2291 ----------------------------------------------------------------------------------
2295 l_act_metric_fact_rec.node_id,
2292 IF l_act_metric_fact_rec.hierarchy_id <> FND_API.G_MISS_NUM THEN
2293
2294 OPEN c_hierarchy_node_check(l_act_metric_fact_rec.hierarchy_id,
2296 l_act_metric_fact_rec.activity_metric_fact_id,
2297 l_act_metric_fact_rec.activity_metric_id,
2298 l_act_metric_fact_rec.formula_id);
2299
2300 FETCH c_hierarchy_node_check INTO l_fact_exists;
2301
2302
2303
2304
2305
2306 IF c_hierarchy_node_check%FOUND THEN
2307 CLOSE c_hierarchy_node_check;
2308 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2309 FND_MESSAGE.set_name('OZF', 'OZF_METR_FACT_DUP_NODE_ID');
2310 FND_MSG_PUB.add;
2311 RAISE FND_API.g_exc_error;
2312 END IF;
2313 ELSE
2314 CLOSE c_hierarchy_node_check;
2315 END IF;
2316 END IF;
2317
2318 EXCEPTION
2319 WHEN OTHERS THEN
2320 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2321 END Check_ActMetricFact_Items;
2322
2323
2324 --
2325 -- Start of comments.
2326 --
2327 -- NAME
2328 -- Validate_ActMetFact_Rec
2329 --
2330 -- PURPOSE
2331 -- Perform Record Level and Other business validations for metric facts.
2332 --
2333 -- NOTES
2334 --
2335 -- HISTORY
2336 -- 24-Apr-2000 tdonohoe Created.
2337 --
2338 -- End of comments.
2339
2340 PROCEDURE Validate_ActMetFact_Rec(
2341 p_act_metric_fact_rec IN act_metric_fact_rec_type,
2342 p_complete_fact_rec IN act_metric_fact_rec_type,
2343 x_return_status OUT NOCOPY VARCHAR2
2344 )
2345 IS
2346
2347 l_act_metric_fact_rec act_metric_fact_rec_type := p_act_metric_fact_rec;
2348
2349 l_table_name VARCHAR2(30);
2350 l_pk_name VARCHAR2(30);
2351 l_pk_value VARCHAR2(30);
2352 l_pk_data_type VARCHAR2(30);
2353 l_additional_where_clause VARCHAR2(4000); -- Used by Check_FK_Exists.
2354
2355 l_return_status VARCHAR2(1);
2356
2357
2358 BEGIN
2359
2360 x_return_status := FND_API.g_ret_sts_success;
2361
2362 IF l_act_metric_fact_rec.arc_act_metric_used_by <> FND_API.G_MISS_CHAR THEN
2363
2364 IF l_act_metric_fact_rec.act_metric_used_by_id = FND_API.G_MISS_NUM THEN
2365 l_act_metric_fact_rec.act_metric_used_by_id := p_complete_fact_rec.act_metric_used_by_id ;
2366 END IF;
2367
2368 IF l_act_metric_fact_rec.activity_metric_id = FND_API.G_MISS_NUM THEN
2369 l_act_metric_fact_rec.activity_metric_id := p_complete_fact_rec.activity_metric_id ;
2370 END IF;
2371
2372 -- first Check whether the Metric is attached to same usage or not
2373 l_table_name := 'OZF_ACT_METRICS_ALL';
2374 l_pk_name := 'ACTIVITY_METRIC_ID';
2375 l_pk_value := l_act_metric_fact_rec.activity_metric_id;
2376 l_pk_data_type := OZF_Utility_PVT.G_NUMBER;
2377 l_additional_where_clause := ' arc_act_metric_used_by = '||''''||
2378 l_act_metric_fact_rec.arc_act_metric_used_by||'''' ;
2379
2380
2381
2382 IF OZF_Utility_PVT.Check_FK_Exists (
2383 p_table_name => l_table_name
2384 ,p_pk_name => l_pk_name
2385 ,p_pk_value => l_pk_value
2386 ,p_pk_data_type => l_pk_data_type
2387 ,p_additional_where_clause => l_additional_where_clause
2388 ) = FND_API.G_FALSE
2389 THEN
2390
2391 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2392 THEN
2393 FND_MESSAGE.Set_Name('OZF', 'OZF_METR_INVALID_ACT_USAGE');
2394 FND_MSG_PUB.Add;
2395 END IF;
2396
2397 x_return_status := FND_API.G_RET_STS_ERROR;
2398
2399 RETURN;
2400
2401 END IF; -- Check_FK_Exists
2402
2403
2404 /*
2405
2406 -- Get table_name and pk_name for the ARC qualifier.
2407 OZF_Utility_PVT.Get_Qual_Table_Name_And_PK (
2408 p_sys_qual => l_act_metric_fact_rec.arc_act_metric_used_by,
2409 x_return_status => l_return_status,
2410 x_table_name => l_table_name,
2411 x_pk_name => l_pk_name
2412 );
2413
2414
2415
2416 l_pk_value := l_act_metric_fact_rec.act_metric_used_by_id;
2417 l_pk_data_type := OZF_Utility_PVT.G_NUMBER;
2418 l_additional_where_clause := NULL;
2419
2420 IF OZF_Utility_PVT.Check_FK_Exists (
2421 p_table_name => l_table_name
2422 ,p_pk_name => l_pk_name
2423 ,p_pk_value => l_pk_value
2424 ,p_pk_data_type => l_pk_data_type
2425 ,p_additional_where_clause => NULL
2426 ) = FND_API.G_FALSE
2427 THEN
2428 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2429 FND_MESSAGE.Set_Name ('OZF', 'OZF_METR_INVALID_USED_BY');
2430 FND_MSG_PUB.Add;
2434 RETURN;
2431 END IF;
2432
2433 x_return_status := FND_API.G_RET_STS_ERROR;
2435 END IF;
2436 */
2437
2438 END IF;
2439
2440 EXCEPTION
2441 WHEN OTHERS THEN
2442 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2443
2444
2445 END Validate_ActMetFact_Rec;
2446
2447
2448 --
2449 -- Start of comments.
2450 --
2451 -- NAME
2452 -- Validate_ActMetFact_Items
2453 --
2454 -- PURPOSE
2455 -- Perform All Item level validation for Activity metric facts.
2456 --
2457 -- NOTES
2458 --
2459 -- HISTORY
2460 -- 24-Apr-2000 tdonohoe Created.
2461 --
2462 -- End of comments.
2463
2464 PROCEDURE Validate_ActMetFact_Items (
2465 p_act_metric_fact_rec IN act_metric_fact_rec_type,
2466 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
2467 x_return_status OUT NOCOPY VARCHAR2
2468 )
2469 IS
2470 BEGIN
2471
2472
2473
2474 Check_Req_ActMetricFact_Items(
2475 p_act_metric_fact_rec => p_act_metric_fact_rec,
2476 x_return_status => x_return_status
2477 );
2478 IF x_return_status <> FND_API.g_ret_sts_success THEN
2479 RETURN;
2480 END IF;
2481
2482
2483
2484 Check_ActMetricFact_Uk_Items(
2485 p_act_metric_fact_rec => p_act_metric_fact_rec,
2486 p_validation_mode => p_validation_mode,
2487 x_return_status => x_return_status
2488 );
2489
2490 IF x_return_status <> FND_API.g_ret_sts_success THEN
2491 RETURN;
2492 END IF;
2493
2494
2495
2496 Check_ActMetricFact_Items(
2497 p_act_metric_fact_rec => p_act_metric_fact_rec,
2498 x_return_status => x_return_status
2499 );
2500
2501 IF x_return_status <> FND_API.g_ret_sts_success THEN
2502 RETURN;
2503 END IF;
2504
2505 END Validate_ActMetFact_Items;
2506
2507 -- Start of comments
2508 -- NAME
2509 -- Default_Formula
2510 --
2511 --
2512 -- PURPOSE
2513 -- Defaults the Activity Metric Formula.
2514 --
2515 -- NOTES
2516 --
2517 -- HISTORY
2518 -- 31-May-2000 tdonohoe Created.
2519 --
2520 -- End of comments
2521
2522 PROCEDURE Default_Formula(
2523 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2524 p_formula_rec IN ozf_formula_rec_type,
2525 p_validation_mode IN VARCHAR2 ,
2526 x_complete_rec OUT NOCOPY ozf_formula_rec_type,
2527 x_return_status OUT NOCOPY VARCHAR2,
2528 x_msg_count OUT NOCOPY NUMBER,
2529 x_msg_data OUT NOCOPY VARCHAR2
2530 )
2531 IS
2532
2533 BEGIN
2534 --
2535 -- Initialize message list if p_init_msg_list is set to TRUE.
2536 --
2537 IF FND_API.To_Boolean (p_init_msg_list) THEN
2538 FND_MSG_PUB.Initialize;
2539 END IF;
2540
2541 --
2542 -- Initialize API return status to success.
2543 --
2544 x_return_status := FND_API.G_RET_STS_SUCCESS;
2545
2546 x_complete_rec := p_formula_rec;
2547
2548 -- Insert Mode
2549 IF ((p_validation_mode = JTF_PLSQL_API.g_create) OR (p_validation_mode = JTF_PLSQL_API.g_update)) THEN
2550 NULL;
2551 END IF;
2552
2553 END Default_Formula ;
2554
2555
2556 -- Start of comments
2557 -- NAME
2558 -- Default_Formula_Entry
2559 --
2560 --
2561 -- PURPOSE
2562 -- Defaults the Activity Metric Formula Entry.
2563 --
2564 -- NOTES
2565 --
2566 -- HISTORY
2567 -- 01-Jun-2000 tdonohoe Created.
2568 --
2569 -- End of comments
2570
2571 PROCEDURE Default_Formula_Entry(
2572 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2573 p_formula_entry_rec IN ozf_formula_entry_rec_type,
2574 p_validation_mode IN VARCHAR2 ,
2575 x_complete_entry_rec OUT NOCOPY ozf_formula_entry_rec_type,
2576 x_return_status OUT NOCOPY VARCHAR2,
2577 x_msg_count OUT NOCOPY NUMBER,
2578 x_msg_data OUT NOCOPY VARCHAR2
2579 )
2580 IS
2581
2582 BEGIN
2583 --
2584 -- Initialize message list if p_init_msg_list is set to TRUE.
2585 --
2586 IF FND_API.To_Boolean (p_init_msg_list) THEN
2587 FND_MSG_PUB.Initialize;
2588 END IF;
2589
2590 --
2591 -- Initialize API return status to success.
2592 --
2593 x_return_status := FND_API.G_RET_STS_SUCCESS;
2594
2595 x_complete_entry_rec := p_formula_entry_rec;
2596
2597 -- Insert Mode
2598 IF ((p_validation_mode = JTF_PLSQL_API.g_create) OR (p_validation_mode = JTF_PLSQL_API.g_update)) THEN
2599
2600 NULL;
2601 END IF;
2602
2603 END Default_Formula_Entry ;
2604
2605
2606
2607 -- Start of comments.
2608 --
2609 -- NAME
2610 -- Check_Req_Formula_Items
2611 --
2612 -- PURPOSE
2613 -- Validate required activity metric formula items.
2614 --
2615 -- NOTES
2616 --
2617 -- HISTORY
2618 -- 31-May-2000 tdonohoe Created.
2619 --
2620 -- End of comments.
2621
2625 )
2622 PROCEDURE Check_Req_Formula_Items (
2623 p_formula_rec IN ozf_formula_rec_type,
2624 x_return_status OUT NOCOPY VARCHAR2
2626 IS
2627 BEGIN
2628 -- Initialize return status to success.
2629 x_return_status := FND_API.G_RET_STS_SUCCESS;
2630
2631
2632 --ACTIVITY_METRIC_ID
2633
2634 IF p_formula_rec.activity_metric_id IS NULL
2635 THEN
2636 -- missing required fields
2637 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2638 THEN -- MMSG
2639 FND_MESSAGE.Set_Name('OZF', 'OZF_FML_MISSING_ACT_METRIC_ID');
2640 FND_MSG_PUB.Add;
2641 END IF;
2642
2643 x_return_status := FND_API.G_RET_STS_ERROR;
2644
2645 -- If any error happens abort API.
2646 RETURN;
2647 END IF;
2648
2649 --LEVEL_DEPTH
2650
2651 IF p_formula_rec.level_depth IS NULL
2652 THEN
2653 -- missing required fields
2654 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2655 THEN -- MMSG
2656 FND_MESSAGE.Set_Name('OZF', 'OZF_FML_MISSING_LEVEL_DEPTH');
2657 FND_MSG_PUB.Add;
2658 END IF;
2659
2660 x_return_status := FND_API.G_RET_STS_ERROR;
2661
2662 -- If any error happens abort API.
2663 RETURN;
2664 END IF;
2665
2666
2667 EXCEPTION
2668 WHEN OTHERS THEN
2669 RAISE;
2670 END Check_Req_Formula_Items;
2671
2672
2673 -- Start of comments.
2674 --
2675 -- NAME
2676 -- Check_Req_Formula_Entry_Items
2677 --
2678 -- PURPOSE
2679 -- Validate required activity metric formula entry items.
2680 --
2681 -- NOTES
2682 --
2683 -- HISTORY
2684 -- 01-Jun-2000 tdonohoe Created.
2685 --
2686 -- End of comments.
2687
2688 PROCEDURE Check_Req_Formula_Entry_Items (
2689 p_formula_entry_rec IN ozf_formula_entry_rec_type,
2690 x_return_status OUT NOCOPY VARCHAR2
2691 )
2692 IS
2693 BEGIN
2694 -- Initialize return status to success.
2695 x_return_status := FND_API.G_RET_STS_SUCCESS;
2696
2697
2698
2699 --FORMULA_ID
2700
2701 IF p_formula_entry_rec.formula_id IS NULL
2702 THEN
2703 -- missing required fields
2704 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2705 THEN -- MMSG
2706 FND_MESSAGE.Set_Name('OZF', 'OZF_FML_MISSING_FORMULA_ID');
2707 FND_MSG_PUB.Add;
2708 END IF;
2709
2710 x_return_status := FND_API.G_RET_STS_ERROR;
2711
2712 -- If any error happens abort API.
2713 RETURN;
2714 END IF;
2715
2716 --ORDER_NUMBER
2717 IF p_formula_entry_rec.order_number IS NULL
2718 THEN
2719 -- missing required fields
2720 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2721 THEN -- MMSG
2722 FND_MESSAGE.Set_Name('OZF', 'OZF_FML_MISSING_ORDER_NUM');
2723 FND_MSG_PUB.Add;
2724 END IF;
2725
2726 x_return_status := FND_API.G_RET_STS_ERROR;
2727
2728 -- If any error happens abort API.
2729 RETURN;
2730 END IF;
2731
2732 -- FORMULA_ENTRY_TYPE
2733 IF p_formula_entry_rec.formula_entry_type IS NULL
2734 THEN
2735 -- missing required fields
2736 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2737 THEN -- MMSG
2738 FND_MESSAGE.Set_Name('OZF', 'OZF_FML_MISSING_ENT_TYPE');
2739 FND_MSG_PUB.Add;
2740 END IF;
2741
2742 x_return_status := FND_API.G_RET_STS_ERROR;
2743
2744 -- If any error happens abort API.
2745 RETURN;
2746 END IF;
2747
2748 -- OBJECT_VERSION_NUMBER
2749 IF p_formula_entry_rec.object_version_number IS NULL
2750 THEN
2751 -- missing required fields
2752 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2753 THEN -- MMSG
2754 FND_MESSAGE.Set_Name('OZF', 'OZF_FML_MISSING_OBJ_NUM');
2755 FND_MSG_PUB.Add;
2756 END IF;
2757
2758 x_return_status := FND_API.G_RET_STS_ERROR;
2759
2760 -- If any error happens abort API.
2761 RETURN;
2762 END IF;
2763
2764 EXCEPTION
2765 WHEN OTHERS THEN
2766 RAISE;
2767 END Check_Req_Formula_Entry_Items;
2768
2769
2770
2771 --
2772 -- Start of comments.
2773 --
2774 -- NAME
2775 -- Check_Formula_UK_Items
2776 --
2777 -- PURPOSE
2778 -- Perform Uniqueness check for Activity metric formulas.
2779 --
2780 -- NOTES
2781 --
2782 -- HISTORY
2783 -- 31-May-2000 tdonohoe Created.
2784 --
2785 -- End of comments.
2786
2787
2788 PROCEDURE Check_Formula_UK_Items(
2789 p_formula_rec IN ozf_formula_rec_type,
2790 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
2791 x_return_status OUT NOCOPY VARCHAR2
2792 )
2793 IS
2794
2795 l_formula_count number;
2796
2797 CURSOR c_formula_type IS
2798 SELECT COUNT(*)
2799 FROM ozf_act_metric_formulas
2800 WHERE formula_type = p_formula_rec.formula_type
2801 AND activity_metric_id = p_formula_rec.activity_metric_id
2805
2802 AND level_depth = p_formula_rec.level_depth
2803 AND formula_id <> p_formula_rec.formula_id;
2804
2806 BEGIN
2807
2808 x_return_status := FND_API.g_ret_sts_success;
2809
2810 OPEN c_formula_type;
2811 FETCH c_formula_type INTO l_formula_count;
2812 CLOSE c_formula_type;
2813
2814 IF (l_formula_count > 0) THEN
2815 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2816
2817 FND_MESSAGE.set_name('OZF', 'OZF_FML_MAX_LEVEL');
2818 FND_MSG_PUB.add;
2819 END IF;
2820 x_return_status := FND_API.g_ret_sts_error;
2821 RETURN;
2822 END IF;
2823
2824 END Check_Formula_Uk_Items;
2825
2826 --
2827 -- Start of comments.
2828 --
2829 -- NAME
2830 -- Check_Formula_Entry_UK_Items
2831 --
2832 -- PURPOSE
2833 -- Perform Uniqueness check for Activity metric formula entries.
2834 --
2835 -- NOTES
2836 --
2837 -- HISTORY
2838 -- 01-Jun-2000 tdonohoe Created.
2839 --
2840 -- End of comments.
2841 PROCEDURE Check_Formula_Entry_UK_Items(
2842 p_formula_entry_rec IN ozf_formula_entry_rec_type,
2843 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
2844 x_return_status OUT NOCOPY VARCHAR2
2845 )
2846 IS
2847
2848 l_formula_entry_count number;
2849
2850 CURSOR c_formula_entry_type IS
2851 SELECT COUNT(*)
2852 FROM ozf_act_metric_form_ent
2853 WHERE formula_id = p_formula_entry_rec.formula_id
2854 AND order_number = p_formula_entry_rec.order_number
2855 AND formula_entry_id <> p_formula_entry_rec.formula_entry_id;
2856
2857
2858 BEGIN
2859
2860 x_return_status := FND_API.g_ret_sts_success;
2861
2862 /*
2863 OPEN c_formula_entry_type;
2864 FETCH c_formula_entry_type INTO l_formula_entry_count;
2865 CLOSE c_formula_entry_type;
2866
2867 IF (l_formula_entry_count > 0) THEN
2868 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2869
2870 FND_MESSAGE.set_name('OZF', 'OZF_FML_ENT_DUP_ORDNUM');
2871 FND_MSG_PUB.add;
2872 END IF;
2873 x_return_status := FND_API.g_ret_sts_error;
2874 RETURN;
2875 END IF;
2876 */
2877 END Check_Formula_Entry_Uk_Items;
2878
2879
2880
2881
2882 --
2883 -- Start of comments.
2884 --
2885 -- NAME
2886 -- Check_Formula_Items
2887 --
2888 -- PURPOSE
2889 -- Perform item level validation for activity metric formulas.
2890 --
2891 -- NOTES
2892 --
2893 -- HISTORY
2894 -- 31-May-2000 tdonohoe Created.
2895 --
2896 -- End of comments.
2897
2898 PROCEDURE Check_Formula_Items (
2899 p_formula_rec IN ozf_formula_rec_type,
2900 x_return_status OUT NOCOPY VARCHAR2
2901 )
2902 IS
2903 l_item_name VARCHAR2(30); -- Used to standardize error messages.
2904 l_formula_rec ozf_formula_rec_type := p_formula_rec;
2905 l_return_status VARCHAR2(1);
2906
2907 BEGIN
2908 -- Initialize return status to success.
2909 x_return_status := FND_API.G_RET_STS_SUCCESS;
2910
2911 --FORMULA_TYPE
2912
2913 IF l_formula_rec.formula_type <> FND_API.G_MISS_CHAR THEN
2914
2915
2916 IF ozf_utility_pvt.check_lookup_exists(p_lookup_type => 'OZF_FORMULA_TYPE',
2917 p_lookup_code => l_formula_rec.formula_type) = FND_API.g_false THEN
2918
2919 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2920 FND_MESSAGE.set_name('OZF', 'OZF_FML_INVALID_TYPE');
2921 FND_MSG_PUB.add;
2922 END IF;
2923
2924 x_return_status := FND_API.g_ret_sts_error;
2925 RETURN;
2926 END IF;
2927 END IF;
2928
2929 EXCEPTION
2930 WHEN OTHERS THEN
2931 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2932 END Check_Formula_Items;
2933
2934
2935 --
2936 -- Start of comments.
2937 --
2938 -- NAME
2939 -- Check_Formula_Entry_Items
2940 --
2941 -- PURPOSE
2942 -- Perform item level validation for activity metric formula entries.
2943 --
2944 -- NOTES
2945 --
2946 -- HISTORY
2947 -- 01-Jun-2000 tdonohoe Created.
2948 --
2949 -- End of comments.
2950
2951 PROCEDURE Check_Formula_Entry_Items (
2952 p_formula_entry_rec IN ozf_formula_entry_rec_type,
2953 x_return_status OUT NOCOPY VARCHAR2
2954 )
2955 IS
2956 l_item_name VARCHAR2(30); -- Used to standardize error messages.
2957 l_formula_entry_rec ozf_formula_entry_rec_type := p_formula_entry_rec;
2958 l_return_status VARCHAR2(1);
2959
2960
2961 BEGIN
2962
2963 -- Initialize return status to success.
2964 x_return_status := FND_API.G_RET_STS_SUCCESS;
2965
2966 --FORMULA_ENTRY_TYPE
2967
2968 IF l_formula_entry_rec.formula_entry_type <> FND_API.G_MISS_CHAR THEN
2969
2970
2971 IF ozf_utility_pvt.check_lookup_exists(p_lookup_type => 'OZF_FORMULA_ENT_TYPE',
2975 FND_MESSAGE.set_name('OZF', 'OZF_FML_ENT_INVALID_TYPE');
2972 p_lookup_code => l_formula_entry_rec.formula_entry_type) = FND_API.g_false THEN
2973
2974 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2976 FND_MSG_PUB.add;
2977 END IF;
2978
2979 x_return_status := FND_API.g_ret_sts_error;
2980 RETURN;
2981 END IF;
2982 END IF;
2983
2984 --OZF_FORMULA_OPERATORS
2985
2986 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
2987
2988
2989 IF ozf_utility_pvt.check_lookup_exists(p_lookup_type => 'OZF_FORMULA_OPERATOR',
2990 p_lookup_code => l_formula_entry_rec.formula_entry_operator) = FND_API.g_false THEN
2991
2992 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2993 FND_MESSAGE.set_name('OZF', 'OZF_FML_ENT_INVALID_OP');
2994 FND_MSG_PUB.add;
2995 END IF;
2996
2997 x_return_status := FND_API.g_ret_sts_error;
2998 RETURN;
2999 END IF;
3000 END IF;
3001
3002 EXCEPTION
3003 WHEN OTHERS THEN
3004 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3005 END Check_Formula_Entry_Items;
3006
3007
3008
3009 --
3010 -- Start of comments.
3011 --
3012 -- NAME
3013 -- Validate_Formula_Rec
3014 --
3015 -- PURPOSE
3016 -- Perform Record Level and Other business validations for activity metric formula table.
3017 --
3018 -- NOTES
3019 --
3020 -- HISTORY
3021 -- 31-May-2000 tdonohoe Created.
3022 --
3023 -- End of comments.
3024
3025 PROCEDURE Validate_Formula_rec(
3026 p_formula_rec IN ozf_formula_rec_type,
3027 p_complete_formula_rec IN ozf_formula_rec_type,
3028 x_return_status OUT NOCOPY VARCHAR2
3029 )
3030 IS
3031
3032 l_formula_rec ozf_formula_rec_type := p_formula_rec;
3033 l_return_status VARCHAR2(1);
3034
3035
3036 BEGIN
3037
3038 x_return_status := FND_API.g_ret_sts_success;
3039
3040 IF (l_formula_rec.activity_metric_id <> FND_API.G_MISS_NUM) THEN
3041
3042 IF ozf_utility_pvt.Check_FK_Exists (
3043 p_table_name => 'OZF_ACT_METRICS_ALL'
3044 ,p_pk_name => 'ACTIVITY_METRIC_ID'
3045 ,p_pk_value => l_formula_rec.activity_metric_id
3046 ,p_pk_data_type => NULL
3047 ,p_additional_where_clause => NULL
3048 ) = FND_API.G_FALSE
3049 THEN
3050 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3051 FND_MESSAGE.Set_Name ('OZF', 'OZF_FML_INVALID_ACT_METRIC');
3052 FND_MSG_PUB.Add;
3053 END IF;
3054
3055 x_return_status := FND_API.G_RET_STS_ERROR;
3056 RETURN;
3057 END IF;
3058 END IF;
3059
3060 EXCEPTION
3061 WHEN OTHERS THEN
3062 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3063
3064 END Validate_Formula_rec;
3065
3066
3067 --
3068 -- Start of comments.
3069 --
3070 -- NAME
3071 -- Validate_Form_ent_rec
3072 --
3073 -- PURPOSE
3074 -- Perform Record Level and Other business validations for activity metric formula table.
3075 --
3076 -- NOTES
3077 --
3078 -- HISTORY
3079 -- 01-Jun-2000 tdonohoe Created.
3080 --
3081 -- End of comments.
3082
3083 PROCEDURE Validate_Form_ent_rec(
3084 p_formula_entry_rec IN ozf_formula_entry_rec_type,
3085 p_complete_formula_entry_rec IN ozf_formula_entry_rec_type,
3086 x_return_status OUT NOCOPY VARCHAR2
3087 )
3088 IS
3089
3090 l_formula_entry_rec ozf_formula_entry_rec_type := p_formula_entry_rec;
3091 l_return_status VARCHAR2(1);
3092
3093
3094 BEGIN
3095
3096 x_return_status := FND_API.g_ret_sts_success;
3097
3098 IF (l_formula_entry_rec.formula_id <> FND_API.G_MISS_NUM) THEN
3099
3100 IF ozf_utility_pvt.Check_FK_Exists (
3101 p_table_name => 'OZF_ACT_METRIC_FORMULAS'
3102 ,p_pk_name => 'FORMULA_ID'
3103 ,p_pk_value => l_formula_entry_rec.formula_id
3104 ,p_pk_data_type => NULL
3105 ,p_additional_where_clause => NULL
3106 ) = FND_API.G_FALSE
3107 THEN
3108 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3109 FND_MESSAGE.Set_Name ('OZF', 'OZF_FML_INVALID_FORMULA_ID');
3110 FND_MSG_PUB.Add;
3111 END IF;
3112
3113 x_return_status := FND_API.G_RET_STS_ERROR;
3114 RETURN;
3115 END IF;
3116 END IF;
3117
3118 EXCEPTION
3119 WHEN OTHERS THEN
3120 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3121
3122 END Validate_Form_ent_rec;
3123
3124
3125 --
3126 -- Start of comments.
3127 --
3128 -- NAME
3129 -- Validate_Formula_Items
3130 --
3131 -- PURPOSE
3132 -- Perform All Item level validation for Activity metric formulas.
3133 --
3134 -- NOTES
3135 --
3136 -- HISTORY
3140
3137 -- 31-May-2000 tdonohoe Created.
3138 --
3139 -- End of comments.
3141 PROCEDURE Validate_Formula_Items (
3142 p_formula_rec IN ozf_formula_rec_type,
3143 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
3144 x_return_status OUT NOCOPY VARCHAR2
3145 )
3146 IS
3147 BEGIN
3148
3149
3150
3151 Check_Req_Formula_Items(
3152 p_formula_rec => p_formula_rec,
3153 x_return_status => x_return_status
3154 );
3155 IF x_return_status <> FND_API.g_ret_sts_success THEN
3156 RETURN;
3157 END IF;
3158
3159
3160 Check_Formula_Uk_Items(
3161 p_formula_rec => p_formula_rec,
3162 p_validation_mode => p_validation_mode,
3163 x_return_status => x_return_status
3164 );
3165
3166 IF x_return_status <> FND_API.g_ret_sts_success THEN
3167 RETURN;
3168 END IF;
3169
3170
3171 Check_Formula_Items(
3172 p_formula_rec => p_formula_rec,
3173 x_return_status => x_return_status
3174 );
3175
3176 IF x_return_status <> FND_API.g_ret_sts_success THEN
3177 RETURN;
3178 END IF;
3179
3180 END Validate_Formula_Items;
3181
3182
3183 --
3184 -- Start of comments.
3185 --
3186 -- NAME
3187 -- Validate_Form_Ent_Items
3188 --
3189 -- PURPOSE
3190 -- Perform All Item level validation for Activity metric formula entries.
3191 --
3192 -- NOTES
3193 --
3194 -- HISTORY
3195 -- 01-Jun-2000 tdonohoe Created.
3196 --
3197 -- End of comments.
3198
3199 PROCEDURE Validate_Form_Ent_Items (
3200 p_formula_entry_rec IN ozf_formula_entry_rec_type,
3201 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
3202 x_return_status OUT NOCOPY VARCHAR2
3203 )
3204 IS
3205 BEGIN
3206
3207
3208
3209 Check_Req_Formula_Entry_Items(
3210 p_formula_entry_rec => p_formula_entry_rec,
3211 x_return_status => x_return_status
3212 );
3213 IF x_return_status <> FND_API.g_ret_sts_success THEN
3214 RETURN;
3215 END IF;
3216
3217
3218 Check_Formula_Entry_Uk_Items(
3219 p_formula_entry_rec => p_formula_entry_rec,
3220 p_validation_mode => p_validation_mode,
3221 x_return_status => x_return_status
3222 );
3223
3224 IF x_return_status <> FND_API.g_ret_sts_success THEN
3225 RETURN;
3226 END IF;
3227
3228
3229 Check_Formula_Entry_Items(
3230 p_formula_entry_rec => p_formula_entry_rec,
3231 x_return_status => x_return_status
3232 );
3233
3234 IF x_return_status <> FND_API.g_ret_sts_success THEN
3235 RETURN;
3236 END IF;
3237
3238 END Validate_Form_Ent_Items;
3239
3240
3241 -- Start of comments
3242 -- NAME
3243 -- Validate_Formula
3244 --
3245 -- PURPOSE
3246 -- Validation API for Activity metric formula table.
3247 --
3248
3249 -- NOTES
3250 --
3251 -- HISTORY
3252 -- 31-May-2000 tdonohoe Created.
3253
3254 --
3255 -- End of comments
3256 PROCEDURE Validate_Formula (
3257 p_api_version IN NUMBER,
3258 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3259 p_validation_level IN NUMBER := FND_API.G_Valid_Level_Full,
3260
3261 x_return_status OUT NOCOPY VARCHAR2,
3262 x_msg_count OUT NOCOPY NUMBER,
3263 x_msg_data OUT NOCOPY VARCHAR2,
3264
3265 p_formula_rec IN ozf_formula_rec_type
3266 )
3267 IS
3268 L_API_VERSION CONSTANT NUMBER := 1.0;
3269 L_API_NAME CONSTANT VARCHAR2(30) := 'VALIDATE_FORMULA';
3270 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
3271
3272
3273 l_return_status VARCHAR2(1);
3274
3275 BEGIN
3276 --
3277 -- Output debug message.
3278 --
3279 IF G_DEBUG THEN
3280 ozf_utility_pvt.debug_message(l_full_name||': start');
3281 END IF;
3282
3283 --
3284 -- Initialize message list if p_init_msg_list is set to TRUE.
3285 --
3286 IF FND_API.To_Boolean (p_init_msg_list) THEN
3287 FND_MSG_PUB.Initialize;
3288 END IF;
3289
3290 --
3291 -- Standard check for API version compatibility.
3292 --
3293 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
3294 p_api_version,
3295 L_API_NAME,
3296 G_PKG_NAME)
3297 THEN
3298 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3299 END IF;
3300
3301 --
3302 -- Initialize API return status to success.
3303 --
3304 x_return_status := FND_API.G_RET_STS_SUCCESS;
3305
3306 --
3307 -- Begin API Body.
3308 --
3309
3310 IF G_DEBUG THEN
3311 ozf_utility_pvt.debug_message(l_full_name||': Validate items');
3312 END IF;
3313
3314 -- Validate required items in the record.
3315 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
3316
3317 Validate_Formula_Items(
3318 p_formula_rec => p_formula_rec,
3322
3319 p_validation_mode => JTF_PLSQL_API.g_create,
3320 x_return_status => l_return_status
3321 );
3323 -- If any errors happen abort API.
3324 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3325 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3326 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3327 RAISE FND_API.G_EXC_ERROR;
3328 END IF;
3329 END IF;
3330
3331 IF G_DEBUG THEN
3332 ozf_utility_pvt.debug_message(l_full_name||': check record');
3333 END IF;
3334
3335 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
3336 Validate_Formula_Rec(
3337 p_formula_rec => p_formula_rec,
3338 p_complete_formula_rec => NULL,
3339 x_return_status => l_return_status
3340 );
3341
3342 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3343 IF G_DEBUG THEN
3344 ozf_utility_pvt.debug_message(l_full_name||': error in check record');
3345 END IF;
3346 RAISE FND_API.g_exc_unexpected_error;
3347 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
3348 IF G_DEBUG THEN
3349 ozf_utility_pvt.debug_message(l_full_name||': error in check record');
3350 END IF;
3351 RAISE FND_API.g_exc_error;
3352 END IF;
3353 END IF;
3354
3355 IF G_DEBUG THEN
3356 ozf_utility_pvt.debug_message(l_full_name||': after check record');
3357 END IF;
3358
3359
3360 --
3361 -- End API Body.
3362 --
3363
3364 --
3365 -- Standard API to get message count, and if 1,
3366 -- set the message data OUT variable.
3367 --
3368 FND_MSG_PUB.Count_And_Get (
3369 p_count => x_msg_count,
3370 p_data => x_msg_data,
3371 p_encoded => FND_API.G_FALSE
3372 );
3373
3374 IF G_DEBUG THEN
3375 ozf_utility_pvt.debug_message(l_full_name ||': end');
3376 END IF;
3377
3378
3379 EXCEPTION
3380 WHEN FND_API.G_EXC_ERROR THEN
3381 x_return_status := FND_API.G_RET_STS_ERROR;
3382 FND_MSG_PUB.Count_And_Get (
3383 p_count => x_msg_count,
3384 p_data => x_msg_data
3385 );
3386 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3387 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3388 FND_MSG_PUB.Count_And_Get (
3389 p_count => x_msg_count,
3390 p_data => x_msg_data
3391 );
3392 WHEN OTHERS THEN
3393 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3394 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3395 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
3396 END IF;
3397 FND_MSG_PUB.Count_And_Get (
3398 p_count => x_msg_count,
3399 p_data => x_msg_data
3400 );
3401 END Validate_Formula;
3402
3403 -- Start of comments
3404 -- NAME
3405 -- Validate_Formula_Entry
3406 --
3407 -- PURPOSE
3408 -- Validation API for Activity metric formula entry table.
3409 --
3410
3411 -- NOTES
3412 --
3413 -- HISTORY
3414 -- 01-Jun-2000 tdonohoe Created.
3415
3416 --
3417 -- End of comments
3418 PROCEDURE Validate_Formula_Entry (
3419 p_api_version IN NUMBER,
3420 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3421 p_validation_level IN NUMBER := FND_API.G_Valid_Level_Full,
3422
3423 x_return_status OUT NOCOPY VARCHAR2,
3424 x_msg_count OUT NOCOPY NUMBER,
3425 x_msg_data OUT NOCOPY VARCHAR2,
3426
3427 p_formula_entry_rec IN ozf_formula_entry_rec_type
3428 )
3429 IS
3430 L_API_VERSION CONSTANT NUMBER := 1.0;
3431 L_API_NAME CONSTANT VARCHAR2(30) := 'VALIDATE_FORMULA_ENTRY';
3432 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
3433
3434
3435 l_return_status VARCHAR2(1);
3436
3437 BEGIN
3438 --
3439 -- Output debug message.
3440 --
3441 IF G_DEBUG THEN
3442 ozf_utility_pvt.debug_message(l_full_name||': start');
3443 END IF;
3444
3445 --
3446 -- Initialize message list if p_init_msg_list is set to TRUE.
3447 --
3448 IF FND_API.To_Boolean (p_init_msg_list) THEN
3449 FND_MSG_PUB.Initialize;
3450 END IF;
3451
3452 --
3453 -- Standard check for API version compatibility.
3454 --
3455 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
3456 p_api_version,
3457 L_API_NAME,
3458 G_PKG_NAME)
3459 THEN
3460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3461 END IF;
3462
3463 --
3464 -- Initialize API return status to success.
3465 --
3466 x_return_status := FND_API.G_RET_STS_SUCCESS;
3467
3468 --
3469 -- Begin API Body.
3470 --
3471
3472 IF G_DEBUG THEN
3473 ozf_utility_pvt.debug_message(l_full_name||': Validate items');
3474 END IF;
3475
3476 -- Validate required items in the record.
3480 p_formula_entry_rec => p_formula_entry_rec,
3477 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
3478
3479 Validate_Form_Ent_Items(
3481 p_validation_mode => JTF_PLSQL_API.g_create,
3482 x_return_status => l_return_status
3483 );
3484
3485 -- If any errors happen abort API.
3486 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3487 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3488 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3489 RAISE FND_API.G_EXC_ERROR;
3490 END IF;
3491 END IF;
3492
3493 IF G_DEBUG THEN
3494 ozf_utility_pvt.debug_message(l_full_name||': check record');
3495 END IF;
3496
3497 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
3498 Validate_Form_Ent_Rec(
3499 p_formula_entry_rec => p_formula_entry_rec,
3500 p_complete_formula_entry_rec => NULL,
3501 x_return_status => l_return_status
3502 );
3503
3504 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3505 IF G_DEBUG THEN
3506 ozf_utility_pvt.debug_message(l_full_name||': error in check record');
3507 END IF;
3508 RAISE FND_API.g_exc_unexpected_error;
3509 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
3510 IF G_DEBUG THEN
3511 ozf_utility_pvt.debug_message(l_full_name||': error in check record');
3512 END IF;
3513 RAISE FND_API.g_exc_error;
3514 END IF;
3515 END IF;
3516
3517 IF G_DEBUG THEN
3518 ozf_utility_pvt.debug_message(l_full_name||': after check record');
3519 END IF;
3520
3521
3522 --
3523 -- End API Body.
3524 --
3525
3526 --
3527 -- Standard API to get message count, and if 1,
3528 -- set the message data OUT variable.
3529 --
3530 FND_MSG_PUB.Count_And_Get (
3531 p_count => x_msg_count,
3532 p_data => x_msg_data,
3533 p_encoded => FND_API.G_FALSE
3534 );
3535
3536 IF G_DEBUG THEN
3537 ozf_utility_pvt.debug_message(l_full_name ||': end');
3538 END IF;
3539
3540
3541 EXCEPTION
3542 WHEN FND_API.G_EXC_ERROR THEN
3543 x_return_status := FND_API.G_RET_STS_ERROR;
3544 FND_MSG_PUB.Count_And_Get (
3545 p_count => x_msg_count,
3546 p_data => x_msg_data
3547 );
3548 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3549 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3550 FND_MSG_PUB.Count_And_Get (
3551 p_count => x_msg_count,
3552 p_data => x_msg_data
3553 );
3554 WHEN OTHERS THEN
3555 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3556 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3557 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
3558 END IF;
3559 FND_MSG_PUB.Count_And_Get (
3560 p_count => x_msg_count,
3561 p_data => x_msg_data
3562 );
3563 END Validate_Formula_Entry;
3564
3565
3566
3567 -------------------------------------------------------------------------------
3568 -- Start of comments
3569 -- NAME
3570 -- Create_Formula
3571 --
3572 --
3573 -- PURPOSE
3574 -- Creates an Activity Metric Formula.
3575
3576 --
3577 -- NOTES
3578 --
3579 -- HISTORY
3580 -- 31-May-2000 tdonohoe@us Created.
3581 --
3582 -- End of comments
3583 -------------------------------------------------------------------------------
3584 PROCEDURE Create_Formula (
3585 p_api_version IN NUMBER,
3586 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3587 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3588 p_validation_level IN NUMBER := FND_API.G_Valid_Level_Full,
3589
3590 x_return_status OUT NOCOPY VARCHAR2,
3591 x_msg_count OUT NOCOPY NUMBER,
3592 x_msg_data OUT NOCOPY VARCHAR2,
3593
3594 p_formula_rec IN ozf_formula_rec_type,
3595 x_formula_id OUT NOCOPY NUMBER
3596 ) IS
3597
3598 --
3599 -- Standard API information constants.
3600 --
3601 L_API_VERSION CONSTANT NUMBER := 1.0;
3602 L_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_FORMULA';
3603 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
3604
3605
3606 l_return_status VARCHAR2(1); -- Return value from procedures.
3607 l_formula_rec ozf_formula_rec_type := p_formula_rec;
3608 l_formula_count NUMBER ;
3609
3610 CURSOR c_formula_count(l_formula_id IN NUMBER) IS
3611 SELECT count(*)
3612 FROM ozf_act_metric_formulas
3613 WHERE formula_id = l_formula_id;
3614
3615 CURSOR c_formula_id IS
3616 SELECT ozf_act_metric_formulas_s.NEXTVAL
3617 FROM dual;
3618
3619 BEGIN
3620
3621 --
3622 -- Initialize savepoint.
3623 --
3624
3625 SAVEPOINT Create_Formula_Pvt;
3626
3627 IF G_DEBUG THEN
3628 ozf_utility_pvt.Debug_Message(l_full_name||': start');
3629 END IF;
3630
3631 --
3635 FND_MSG_PUB.Initialize;
3632 -- Initialize message list if p_init_msg_list is set to TRUE.
3633 --
3634 IF FND_API.To_Boolean (p_init_msg_list) THEN
3636 END IF;
3637
3638 --
3639 -- Standard check for API version compatibility.
3640 --
3641 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
3642 p_api_version,
3643 L_API_NAME,
3644 G_PKG_NAME)
3645 THEN
3646 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3647 END IF;
3648
3649 --
3650 -- Initialize API return status to success.
3651 --
3652 x_return_status := FND_API.G_RET_STS_SUCCESS;
3653
3654 --
3655 -- Begin API Body.
3656 --
3657
3658 Default_Formula
3659 ( p_init_msg_list => p_init_msg_list,
3660 p_formula_rec => p_formula_rec,
3661 p_validation_mode => JTF_PLSQL_API.g_create,
3662 x_complete_rec => l_formula_rec,
3663 x_return_status => l_return_status,
3664 x_msg_count => x_msg_count,
3665 x_msg_data => x_msg_data ) ;
3666
3667
3668
3669 -- If any errors happen abort API.
3670 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3671 RAISE FND_API.G_EXC_ERROR;
3672 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3673 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3674 END IF;
3675
3676
3677
3678 --
3679 -- Validate the record before inserting.
3680 --
3681
3682
3683 IF l_formula_rec.formula_id IS NULL THEN
3684 LOOP
3685 --
3686 -- Set the value for the PK.
3687 OPEN c_formula_id;
3688 FETCH c_formula_id INTO l_formula_rec.formula_id;
3689 CLOSE c_formula_id;
3690
3691 OPEN c_formula_count(l_formula_rec.formula_id);
3692 FETCH c_formula_count INTO l_formula_count ;
3693 CLOSE c_formula_count ;
3694
3695 EXIT WHEN l_formula_count = 0 ;
3696 END LOOP ;
3697 END IF;
3698
3699
3700
3701 Validate_Formula (
3702 p_api_version => l_api_version,
3703 p_init_msg_list => p_init_msg_list,
3704 p_validation_level => p_validation_level,
3705 x_msg_count => x_msg_count,
3706 x_msg_data => x_msg_data,
3707 x_return_status => l_return_status,
3708 p_formula_rec => l_formula_rec
3709 );
3710
3711 -- If any errors happen abort API.
3712 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3713 RAISE FND_API.G_EXC_ERROR;
3714 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3715 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3716 END IF;
3717
3718
3719 --
3720 -- Debug message.
3721 --
3722 IF G_DEBUG THEN
3723 ozf_utility_pvt.debug_message(l_full_name ||': insert');
3724 END IF;
3725
3726
3727
3728 --
3729 -- Insert into the base table.
3730 --
3731 INSERT INTO OZF_ACT_METRIC_FORMULAS
3732 ( formula_id
3733 ,activity_metric_id
3734 ,level_depth
3735 ,parent_formula_id
3736 ,last_update_date
3737 ,last_updated_by
3738 ,creation_date
3739 ,created_by
3740 ,last_update_login
3741 ,object_version_number
3742 ,formula_type
3743 )
3744 VALUES
3745 (l_formula_rec.formula_id
3746 ,l_formula_rec.activity_metric_id
3747 ,l_formula_rec.level_depth
3748 ,l_formula_rec.parent_formula_id
3749 ,SYSDATE
3750 ,FND_GLOBAL.User_ID
3751 ,SYSDATE
3752 ,FND_GLOBAL.User_ID
3753 ,FND_GLOBAL.Conc_Login_ID
3754 ,1--object version number
3755 ,l_formula_rec.formula_type
3756 );
3757
3758
3759 -- If any errors happen abort API.
3760 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3761 RAISE FND_API.G_EXC_ERROR;
3762 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3763 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3764 END IF;
3765
3766
3767 -- finish
3768
3769 --
3770 -- Set OUT value.
3771 --
3772 x_formula_id := l_formula_rec.formula_id;
3773
3774 --
3775 -- End API Body.
3776 --
3777
3778 --
3779 -- Standard check for commit request.
3780 --
3781 IF FND_API.To_Boolean (p_commit) THEN
3782 COMMIT WORK;
3783 END IF;
3784
3785 --
3786 -- Standard API to get message count, and if 1,
3787 -- set the message data OUT variable.
3788 --
3789 FND_MSG_PUB.Count_And_Get (
3790 p_count => x_msg_count,
3791 p_data => x_msg_data,
3792 p_encoded => FND_API.G_FALSE
3793 );
3794
3795 --
3796 -- Add success message to message list.
3797 --
3798
3799 IF G_DEBUG THEN
3800 ozf_utility_pvt.debug_message(l_full_name ||': end Success');
3801 END IF;
3802
3803
3804
3805
3806 EXCEPTION
3807 WHEN FND_API.G_EXC_ERROR THEN
3808
3809
3813 p_count => x_msg_count,
3810 ROLLBACK TO Create_Formula_Pvt;
3811 x_return_status := FND_API.G_RET_STS_ERROR;
3812 FND_MSG_PUB.Count_And_Get (
3814 p_data => x_msg_data
3815 );
3816 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3817
3818
3819
3820 ROLLBACK TO Create_Formula_Pvt;
3821 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3822 FND_MSG_PUB.Count_And_Get (
3823 p_count => x_msg_count,
3824 p_data => x_msg_data
3825 );
3826 WHEN OTHERS THEN
3827
3828
3829 ROLLBACK TO Create_Formula_Pvt;
3830 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3831 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3832 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
3833 END IF;
3834 FND_MSG_PUB.Count_And_Get (
3835 p_count => x_msg_count,
3836 p_data => x_msg_data
3837 );
3838
3839
3840 END Create_Formula;
3841
3842
3843 -------------------------------------------------------------------------------
3844 -- Start of comments
3845 -- NAME
3846 -- Create_Formula_Entry
3847 --
3848 --
3849 -- PURPOSE
3850 -- Creates an Activity Metric Formula Entry.
3851
3852 --
3853 -- NOTES
3854 --
3855 -- HISTORY
3856 -- 31-May-2000 tdonohoe@us Created.
3857 --
3858 -- End of comments
3859 -------------------------------------------------------------------------------
3860 PROCEDURE Create_Formula_Entry (
3861 p_api_version IN NUMBER,
3862 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3863 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3864 p_validation_level IN NUMBER := FND_API.G_Valid_Level_Full,
3865
3866 x_return_status OUT NOCOPY VARCHAR2,
3867 x_msg_count OUT NOCOPY NUMBER,
3868 x_msg_data OUT NOCOPY VARCHAR2,
3869
3870 p_formula_entry_rec IN ozf_formula_entry_rec_type,
3871 x_formula_entry_id OUT NOCOPY NUMBER
3872 ) IS
3873
3874 --
3875 -- Standard API information constants.
3876 --
3877 L_API_VERSION CONSTANT NUMBER := 1.0;
3878 L_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_FORMULA_ENTRY';
3879 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
3880
3881
3882 l_return_status VARCHAR2(1); -- Return value from procedures.
3883 l_formula_entry_rec ozf_formula_entry_rec_type := p_formula_entry_rec;
3884 l_formula_entry_count NUMBER ;
3885
3886 CURSOR c_formula_entry_count(l_formula_entry_id IN NUMBER) IS
3887 SELECT count(*)
3888 FROM ozf_act_metric_form_ent
3889 WHERE formula_entry_id = l_formula_entry_id;
3890
3891 CURSOR c_formula_entry_id IS
3892 SELECT ozf_act_metric_formula_ent_s.NEXTVAL
3893 FROM dual;
3894
3895 BEGIN
3896
3897 --
3898 -- Initialize savepoint.
3899 --
3900
3901 SAVEPOINT Create_Formula_Entry_Pvt;
3902
3903 IF G_DEBUG THEN
3904 ozf_utility_pvt.Debug_Message(l_full_name||': start');
3905 END IF;
3906
3907 --
3908 -- Initialize message list if p_init_msg_list is set to TRUE.
3909 --
3910 IF FND_API.To_Boolean (p_init_msg_list) THEN
3911 FND_MSG_PUB.Initialize;
3912 END IF;
3913
3914 --
3915 -- Standard check for API version compatibility.
3916 --
3917 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
3918 p_api_version,
3919 L_API_NAME,
3920 G_PKG_NAME)
3921 THEN
3922 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3923 END IF;
3924
3925 --
3926 -- Initialize API return status to success.
3927 --
3928 x_return_status := FND_API.G_RET_STS_SUCCESS;
3929
3930 --
3931 -- Begin API Body.
3932 --
3933
3934 Default_Formula_Entry
3935 ( p_init_msg_list => p_init_msg_list,
3936 p_formula_entry_rec => p_formula_entry_rec,
3937 p_validation_mode => JTF_PLSQL_API.g_create,
3938 x_complete_entry_rec => l_formula_entry_rec,
3939 x_return_status => l_return_status,
3940 x_msg_count => x_msg_count,
3941 x_msg_data => x_msg_data ) ;
3942
3943
3944
3945 -- If any errors happen abort API.
3946 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3947 RAISE FND_API.G_EXC_ERROR;
3948 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3949 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3950 END IF;
3951
3952
3953
3954 --
3955 -- Validate the record before inserting.
3956 --
3957
3958
3959 IF l_formula_entry_rec.formula_entry_id IS NULL THEN
3960 LOOP
3961 --
3962 -- Set the value for the PK.
3963 OPEN c_formula_entry_id;
3964 FETCH c_formula_entry_id INTO l_formula_entry_rec.formula_entry_id;
3965 CLOSE c_formula_entry_id;
3966
3967 OPEN c_formula_entry_count(l_formula_entry_rec.formula_entry_id);
3968 FETCH c_formula_entry_count INTO l_formula_entry_count ;
3969 CLOSE c_formula_entry_count ;
3973 END IF;
3970
3971 EXIT WHEN l_formula_entry_count = 0 ;
3972 END LOOP ;
3974
3975
3976
3977 Validate_Formula_Entry (
3978 p_api_version => l_api_version,
3979 p_init_msg_list => p_init_msg_list,
3980 p_validation_level => p_validation_level,
3981 x_msg_count => x_msg_count,
3982 x_msg_data => x_msg_data,
3983 x_return_status => l_return_status,
3984 p_formula_entry_rec => l_formula_entry_rec
3985 );
3986
3987 -- If any errors happen abort API.
3988 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3989 RAISE FND_API.G_EXC_ERROR;
3990 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3991 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3992 END IF;
3993
3994
3995 --
3996 -- Debug message.
3997 --
3998 IF G_DEBUG THEN
3999 ozf_utility_pvt.debug_message(l_full_name ||': insert');
4000 END IF;
4001
4002
4003
4004 --
4005 -- Insert into the base table.
4006 --
4007 INSERT INTO OZF_ACT_METRIC_FORM_ENT
4008 ( formula_entry_id
4009 ,formula_id
4010 ,order_number
4011 ,formula_entry_type
4012 ,formula_entry_value
4013 ,metric_column_value
4014 ,formula_entry_operator
4015 ,last_update_date
4016 ,last_updated_by
4017 ,creation_date
4018 ,created_by
4019 ,last_update_login
4020 ,object_version_number
4021 )
4022 VALUES
4023 ( l_formula_entry_rec.formula_entry_id
4024 ,l_formula_entry_rec.formula_id
4025 ,l_formula_entry_rec.order_number
4026 ,l_formula_entry_rec.formula_entry_type
4027 ,l_formula_entry_rec.formula_entry_value
4028 ,l_formula_entry_rec.metric_column_value
4029 ,l_formula_entry_rec.formula_entry_operator
4030 ,SYSDATE
4031 ,FND_GLOBAL.User_ID
4032 ,SYSDATE
4033 ,FND_GLOBAL.User_ID
4034 ,FND_GLOBAL.User_ID
4035 ,1--OBJECT_VERSION_NUMBER
4036 );
4037
4038
4039 -- If any errors happen abort API.
4040 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
4041 RAISE FND_API.G_EXC_ERROR;
4042 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4043 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4044 END IF;
4045
4046
4047 -- finish
4048
4049 --
4050 -- Set OUT value.
4051 --
4052 x_formula_entry_id := l_formula_entry_rec.formula_entry_id;
4053
4054 --
4055 -- End API Body.
4056 --
4057
4058 --
4059 -- Standard check for commit request.
4060 --
4061 IF FND_API.To_Boolean (p_commit) THEN
4062 COMMIT WORK;
4063 END IF;
4064
4065 --
4066 -- Standard API to get message count, and if 1,
4067 -- set the message data OUT variable.
4068 --
4069 FND_MSG_PUB.Count_And_Get (
4070 p_count => x_msg_count,
4071 p_data => x_msg_data,
4072 p_encoded => FND_API.G_FALSE
4073 );
4074
4075 --
4076 -- Add success message to message list.
4077 --
4078
4079 IF G_DEBUG THEN
4080 ozf_utility_pvt.debug_message(l_full_name ||': end Success');
4081 END IF;
4082
4083
4084
4085
4086 EXCEPTION
4087 WHEN FND_API.G_EXC_ERROR THEN
4088
4089
4090 ROLLBACK TO Create_Formula_Entry_Pvt;
4091 x_return_status := FND_API.G_RET_STS_ERROR;
4092 FND_MSG_PUB.Count_And_Get (
4093 p_count => x_msg_count,
4094 p_data => x_msg_data
4095 );
4096 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4097
4098
4099
4100 ROLLBACK TO Create_Formula_Entry_Pvt;
4101 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4102 FND_MSG_PUB.Count_And_Get (
4103 p_count => x_msg_count,
4104 p_data => x_msg_data
4105 );
4106 WHEN OTHERS THEN
4107
4108
4109 ROLLBACK TO Create_Formula_Entry_Pvt;
4110 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4111 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4112 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
4113 END IF;
4114 FND_MSG_PUB.Count_And_Get (
4115 p_count => x_msg_count,
4116 p_data => x_msg_data
4117 );
4118
4119
4120 END Create_Formula_Entry;
4121
4122
4123 -- Start of comments
4124 -- NAME
4125 -- Delete_Formula
4126 --
4127 -- PURPOSE
4128 -- Deletes an entry in the ozf_act_metrics_formulas table.
4129 --
4130 -- NOTES
4131 --
4132 -- HISTORY
4133 -- 24-Apr-2000 tdonohoe Created.
4134 --
4135 -- End of comments
4136
4137 PROCEDURE Delete_Formula (
4138 p_api_version IN NUMBER,
4139 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4140 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4141
4142 x_return_status OUT NOCOPY VARCHAR2,
4143 x_msg_count OUT NOCOPY NUMBER,
4144 x_msg_data OUT NOCOPY VARCHAR2,
4145
4146 p_formula_id IN NUMBER,
4147 p_object_version_number IN NUMBER
4148 )
4149 IS
4150 L_API_VERSION CONSTANT NUMBER := 1.0;
4151 L_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_FORMULA';
4152 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
4153
4154 l_return_status VARCHAR2(1);
4155
4156 BEGIN
4157 --
4158 -- Initialize savepoint.
4159 --
4160 SAVEPOINT Delete_Formula_pvt;
4161
4162 --
4163 -- Output debug message.
4164 --
4165 IF G_DEBUG THEN
4166 ozf_utility_pvt.debug_message(l_full_name||': start');
4167 END IF;
4168
4169 --
4170 -- Initialize message list if p_init_msg_list is set to TRUE.
4171 --
4172 IF FND_API.To_Boolean (p_init_msg_list) THEN
4173 FND_MSG_PUB.Initialize;
4174 END IF;
4175
4176 --
4177 -- Standard check for API version compatibility.
4178 --
4179 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
4180 p_api_version,
4181 L_API_NAME,
4182 G_PKG_NAME)
4183 THEN
4184 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4185 END IF;
4186
4187 --
4188 -- Initialize API return status to success.
4189 --
4190 x_return_status := FND_API.G_RET_STS_SUCCESS;
4191
4192 --
4193 -- Begin API Body.
4194 --
4195
4196 -- Debug message.
4197 IF G_DEBUG THEN
4198 ozf_utility_pvt.debug_message(l_full_name ||': delete with Validation');
4199
4200 ozf_utility_pvt.debug_message('formula id '||to_char(p_formula_id));
4201
4202 ozf_utility_pvt.debug_message('object version number '||to_char(p_object_version_number));
4203 END IF;
4204
4205 DELETE
4206 FROM ozf_act_metric_formulas
4207 WHERE formula_id = p_formula_id
4208 AND object_version_number = p_object_version_number;
4209
4210 IF (SQL%NOTFOUND) THEN
4211 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
4212 THEN
4213
4214 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
4215 FND_MSG_PUB.add;
4216 RAISE FND_API.g_exc_error;
4217 END IF;
4218 END IF;
4219
4220 DELETE
4221 FROM ozf_act_metric_form_ent
4222 WHERE formula_id = p_formula_id;
4223
4224 --
4225 -- End API Body.
4226 --
4227
4228 IF FND_API.To_Boolean (p_commit) THEN
4229 COMMIT WORK;
4230 END IF;
4231
4232 --
4233 -- Debug message.
4234 --
4235 IF G_DEBUG THEN
4236 ozf_utility_pvt.debug_message(l_full_name ||': End');
4237 END IF;
4238
4239
4240 --
4241 -- Standard API to get message count, and if 1,
4242 -- set the message data OUT variable.
4243 --
4244 FND_MSG_PUB.Count_And_Get (
4245 p_count => x_msg_count,
4246 p_data => x_msg_data,
4247 p_encoded => FND_API.G_FALSE
4248 );
4249
4250 EXCEPTION
4251 WHEN FND_API.G_EXC_ERROR THEN
4252 ROLLBACK TO Delete_Formula_pvt;
4253 x_return_status := FND_API.G_RET_STS_ERROR;
4254 FND_MSG_PUB.Count_And_Get (
4255 p_count => x_msg_count,
4256 p_data => x_msg_data
4257 );
4258 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4259 ROLLBACK TO Delete_Formula_pvt;
4260 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4261 FND_MSG_PUB.Count_And_Get (
4262 p_count => x_msg_count,
4263 p_data => x_msg_data
4264 );
4265 WHEN OTHERS THEN
4266 ROLLBACK TO Delete_Formula_pvt;
4267 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4268 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4269 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
4270 END IF;
4271 FND_MSG_PUB.Count_And_Get (
4272 p_count => x_msg_count,
4273 p_data => x_msg_data
4274 );
4275 END Delete_Formula;
4276
4277 END Ozf_Actmetricfact_Pvt;