[Home] [Help]
PACKAGE BODY: APPS.OZF_ACTMETRIC_PVT
Source
1 PACKAGE BODY OZF_ACTMETRIC_PVT AS
2 /* $Header: ozfvamtb.pls 120.1.12010000.2 2008/08/05 09:08:22 kdass ship $ */
3
4 ------------------------------------------------------------------------------
5 --
6 -- NAME
7 -- OZF_ActMetric_PVT 11.5.10
8 --
9 -- HISTORY
10 -- 05/07/2003 KDASS migrate to ozf from ams_actmetric_pvt for budget allocation, quota allocation and forecast
11 -- Fri Nov 21 2003:5/42 PM RSSHARMA Changed reference to ams_terr_v to ozf_terr_v
12 -- kvattiku April 23, 04 Update extra paramters in Quota
13 -- rimehrot May 7, 04 Added error message for Quota and changed all error messages to OZF from AMS.
14 -----------------------------------------------------------------------------
15
16 --
17 -- Global variables and constants.
18
19 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_ACTMETRIC_PVT'; -- Name of the current package.
20 G_DEBUG_FLAG VARCHAR2(1) := 'N';
21 G_CREATE VARCHAR2(30) := 'CREATE';
22 G_UPDATE VARCHAR2(30) := 'UPDATE';
23 G_DELETE VARCHAR2(30) := 'DELETE';
24 G_CATEGORY_COSTS CONSTANT NUMBER := 901;
25 G_CATEGORY_REVENUES CONSTANT NUMBER := 902;
26 TYPE date_bucket_type IS TABLE OF DATE;
27 TYPE number_table IS TABLE OF NUMBER;
28
29 -- Forward Declarations Begin
30 OZF_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
31 OZF_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
32 OZF_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
33
34 PROCEDURE Create_ActMetric2 (
35 p_api_version IN NUMBER,
36 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
37 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
38 p_validation_level IN NUMBER := Fnd_Api.G_Valid_Level_Full,
39
40 x_return_status OUT NOCOPY VARCHAR2,
41 x_msg_count OUT NOCOPY NUMBER,
42 x_msg_data OUT NOCOPY VARCHAR2,
43
44 p_act_metric_rec IN act_metric_rec_type,
45 x_activity_metric_id OUT NOCOPY NUMBER
46 );
47
48
49 PROCEDURE Validate_ActMetric_Record (
50 p_act_metric_rec IN act_metric_rec_type,
51 p_complete_rec IN act_metric_rec_type,
52 p_operation_mode IN VARCHAR2,
53 x_return_status OUT NOCOPY VARCHAR2
54 );
55 -- Forward Declarations End
56
57
58 PROCEDURE Create_ActMetric (
59 p_api_version IN NUMBER,
60 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
61 --p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
62 p_commit IN VARCHAR2 := Fnd_Api.G_TRUE,
63 p_validation_level IN NUMBER := Fnd_Api.G_Valid_Level_Full,
64
65 x_return_status OUT NOCOPY VARCHAR2,
66 x_msg_count OUT NOCOPY NUMBER,
67 x_msg_data OUT NOCOPY VARCHAR2,
68
69 p_act_metric_rec IN act_metric_rec_type,
70 x_activity_metric_id OUT NOCOPY NUMBER
71 ) IS
72
73 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_ActMetric';
74 BEGIN
75
76 SAVEPOINT sp_create_actmetric;
77
78 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
79 x_activity_metric_id := NULL;
80
81 LOCK TABLE OZF_ACT_METRICS_ALL IN EXCLUSIVE MODE;
82
83 Create_ActMetric2 (
84 p_api_version => p_api_version,
85 p_init_msg_list => p_init_msg_list,
86 p_commit => p_commit,
87 p_validation_level => p_validation_level,
88 x_return_status => x_return_status,
89 x_msg_count => x_msg_count,
90 x_msg_data => x_msg_data,
91 p_act_metric_rec => p_act_metric_rec,
92 x_activity_metric_id => x_activity_metric_id);
93
94 -- If any errors happen abort API.
95 IF x_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
96 RAISE Fnd_Api.G_EXC_ERROR;
97 END IF;
98
99 --COMMIT WORK;
100
101 IF Fnd_Api.to_boolean(p_commit) THEN
102 COMMIT;
103 END IF;
104
105 --
106 -- Standard API to get message count, and if 1,
107 -- set the message data OUT NOCOPY variable.
108 --
109 Fnd_Msg_Pub.Count_And_Get (
110 p_count => x_msg_count,
111 p_data => x_msg_data,
112 p_encoded => Fnd_Api.G_FALSE
113 );
114
115 EXCEPTION
116 WHEN Fnd_Api.G_EXC_ERROR THEN
117 ROLLBACK TO sp_create_actmetric;
118 x_return_status := Fnd_Api.G_RET_STS_ERROR;
119 Fnd_Msg_Pub.Count_And_Get (
120 p_count => x_msg_count,
121 p_data => x_msg_data,
122 p_encoded => FND_API.G_FALSE
123 );
124 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
125 ROLLBACK TO sp_create_actmetric;
126 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
127 Fnd_Msg_Pub.Count_And_Get (
128 p_count => x_msg_count,
129 p_data => x_msg_data,
130 p_encoded => FND_API.G_FALSE
131 );
132 WHEN OTHERS THEN
133 ROLLBACK TO sp_create_actmetric;
134 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
135 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
136 Fnd_Msg_Pub.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
137 END IF;
138 Fnd_Msg_Pub.Count_And_Get (
139 p_count => x_msg_count,
140 p_data => x_msg_data,
141 p_encoded => FND_API.G_FALSE
142 );
143 END Create_ActMetric;
144
145 -- Start of comments
146 -- NAME
147 -- Init_ActMetric_Rec
148 --
149 -- PURPOSE
150 -- This Procedure will initialize the Record for Activity Metric.
151 -- It will be called before call to Update Activity Metric
152 --
153 -- NOTES
154 --
155 -- HISTORY
156 -- 10/11/2000 SVEERAVE Created.
157 -- 05/07/2001 HuiLi Added the "depend_act_metric" field
158 --
159 -- End of comments
160
161 PROCEDURE Init_ActMetric_Rec(
162 x_act_metric_rec IN OUT NOCOPY Ozf_Actmetric_Pvt.Act_metric_rec_type
163 ) IS
164 BEGIN
165 RETURN;
166 END Init_ActMetric_Rec;
167
168
169
170
171
172
173 -- Start of comments
174 -- NAME
175 -- Default_ActMetric
176 --
177 --
178 -- PURPOSE
179 -- Defaults the Activty Metric . also does Currency Conversion to
180 -- keep Transaction and currency Conversion in Sync.
181 --
182 -- NOTES
183 --
184 -- HISTORY
185 -- End of comments
186
187 PROCEDURE Default_ActMetric(
188 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
189 --p_act_metric_rec IN act_metric_rec_type,
190 p_validation_mode IN VARCHAR2 ,
191 x_complete_rec IN OUT NOCOPY act_metric_rec_type,
192 x_return_status OUT NOCOPY VARCHAR2,
193 x_msg_count OUT NOCOPY NUMBER,
194 x_msg_data OUT NOCOPY VARCHAR2
195 )
196 IS
197 l_obj_trans_curr VARCHAR2(15);
198 l_return_status VARCHAR2(1);
199 l_curr_return_status VARCHAR2(1) := Fnd_Api.G_RET_STS_SUCCESS;
200 l_current_date DATE := SYSDATE;
201 l_trans_actual_value number;
202 l_trans_forecasted_value number;
203
204 BEGIN
205 --
206 -- Initialize message list if p_init_msg_list is set to TRUE.
207 --
208 IF Fnd_Api.To_Boolean (p_init_msg_list) THEN
209 Fnd_Msg_Pub.Initialize;
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 -- 05/22/2002 yzhao: 11.5.9 default setting for budget allocation
218 IF (x_complete_rec.arc_act_metric_used_by='FUND') THEN
219 IF (x_complete_rec.hierarchy_type IN ('BUDGET_HIER', 'BUDGET_CATEGORY', 'HR_ORG') ) THEN
220 -- set start_level, end_level for budget allocation
221 IF (x_complete_rec.from_level IS NULL OR
222 x_complete_rec.from_level = Fnd_Api.G_MISS_NUM) THEN
223 x_complete_rec.from_level := 1;
224 END IF;
225 IF (x_complete_rec.to_level IS NULL OR
226 x_complete_rec.to_level = Fnd_Api.G_MISS_NUM) THEN
227 x_complete_rec.to_level := OZF_Fund_allocations_Pvt.g_max_end_level;
228 END IF;
229
230 -- set 'Ex-Start-Node' if 'ADD ONTO EXISTING BUDGET' and start node is the same as the budget
231 IF (x_complete_rec.action_code = 'TRANSFER_TO_BUDGET' AND
232 x_complete_rec.start_node = x_complete_rec.act_metric_used_by_id) THEN
233 x_complete_rec.ex_start_node := 'Y';
234 ELSE
235 x_complete_rec.ex_start_node := 'N';
236 END IF;
237 END IF;
238
239 END IF;
240 -- 05/22/2002 yzhao: add ends
241
242 /*EXCEPTION
243 WHEN GL_CURRENCY_API.NO_RATE THEN
244 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
245 -- No rate exist for for given conversion date and type between
246 -- transaction currency and functional currency
247 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_NO_RATE');
248 FND_MSG_PUB.Add;
249 END IF;
250 x_return_status := FND_API.G_RET_STS_ERROR;
251
252 -- If any error happens abort API.
253 RETURN;
254 WHEN GL_CURRENCY_API.INVALID_CURRENCY THEN
255 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
256 -- Atleast One of the two Currencies specified is invalid
257 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_INVALID_CURR');
258 FND_MSG_PUB.Add;
259 END IF;
260 x_return_status := FND_API.G_RET_STS_ERROR;
261
262 -- If any error happens abort API.
263 RETURN;
264 */
265 END Default_ActMetric ;
266
267
268
269 -- Start of comments
270 -- NAME
271 -- Create_ActMetric2
272 --
273 --
274 -- PURPOSE
275 -- Creates an association of a metric to a business
276 -- object by creating a record in OZF_ACT_METRICS_ALL.
277 --
278 -- NOTES
279 --
280 -- HISTORY
281 -- 05/26/1999 choang Created.
282 -- 10/9/1999 ptendulk Modified According to new Standards
283 -- 14/Apr-2000 tdonohoe@us Added new columns for 11.5.2 into insert statement.
284 -- 06-28-2000 rchahal@us Modified to allow metric creation for Fund.
285 -- 07/11/2000 svatsa@us Updated the API to allow for creating Summary ActMetric
286 -- for a given metric_id.
287 -- End of comments
288
289 PROCEDURE Create_ActMetric2 (
290 p_api_version IN NUMBER,
291 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
292 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
293 p_validation_level IN NUMBER := Fnd_Api.G_Valid_Level_Full,
294
295 x_return_status OUT NOCOPY VARCHAR2,
296 x_msg_count OUT NOCOPY NUMBER,
297 x_msg_data OUT NOCOPY VARCHAR2,
298
299 p_act_metric_rec IN act_metric_rec_type,
300 x_activity_metric_id OUT NOCOPY NUMBER
301 )
302
303 IS
304 --
305 -- Standard API information constants.
306 --
307 L_API_VERSION CONSTANT NUMBER := 1.0;
308 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_ActMetric2';
309 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
310
311
312 l_return_status VARCHAR2(1); -- Return value from procedures.
313 l_act_metrics_rec act_metric_rec_type := p_act_metric_rec;
314
315 l_act_metr_count NUMBER ;
316
317
318 CURSOR c_act_metr_count(l_act_metric_id IN NUMBER) IS
319 SELECT COUNT(1)
320 FROM ozf_act_metrics_all
321 WHERE activity_metric_id = l_act_metric_id;
322
323 CURSOR c_act_met_id IS
324 SELECT ozf_act_metrics_all_s.NEXTVAL
325 FROM dual;
326
327
328 BEGIN
329 --
330 -- Initialize savepoint.
331 --
332 --SAVEPOINT Create_ActMetric2_pvt;
333
334 IF (OZF_DEBUG_HIGH_ON) THEN
335 ozf_utility_pvt.Debug_Message(l_full_name||': start');
336 END IF;
337
338 --
339 -- Initialize message list if p_init_msg_list is set to TRUE.
340 --
341 IF Fnd_Api.To_Boolean (p_init_msg_list) THEN
342 Fnd_Msg_Pub.Initialize;
343 END IF;
344
345 --
346 -- Standard check for API version compatibility.
347 --
348 IF NOT Fnd_Api.Compatible_API_Call (L_API_VERSION,
349 p_api_version,
350 L_API_NAME,
351 G_PKG_NAME)
352 THEN
353 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
354 END IF;
355
356 --
357 -- Initialize API return status to success.
358 --
359 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
360 x_activity_metric_id := NULL;
361 --
362 -- Begin API Body.
363 --
364
365 IF l_act_metrics_rec.hierarchy_type = FND_API.G_MISS_CHAR then
366 l_act_metrics_rec.hierarchy_type := NULL;
367 END IF;
368 IF l_act_metrics_rec.status_code = FND_API.G_MISS_CHAR then
369 l_act_metrics_rec.status_code := NULL;
370 END IF;
371 IF l_act_metrics_rec.method_code = FND_API.G_MISS_CHAR then
372 l_act_metrics_rec.method_code := NULL;
373 END IF;
374 IF l_act_metrics_rec.action_code = FND_API.G_MISS_CHAR then
375 l_act_metrics_rec.action_code := NULL;
376 END IF;
377 IF l_act_metrics_rec.basis_year = FND_API.G_MISS_NUM then
378 l_act_metrics_rec.basis_year := NULL;
379 END IF;
380 IF l_act_metrics_rec.ex_start_node = FND_API.G_MISS_CHAR then
381 l_act_metrics_rec.ex_start_node := NULL;
382 END IF;
383
384
385 -- Default Sensitive data Flag, UOM code if not sent to the API
386 -- Do Currency Conversion after defaulting functional currency code
387 Default_ActMetric(
388 p_init_msg_list => p_init_msg_list,
389 --p_act_metric_rec => l_act_metrics_rec,
390 p_validation_mode => Jtf_Plsql_Api.g_create,
391 x_complete_rec => l_act_metrics_rec,
392 x_return_status => l_return_status,
393 x_msg_count => x_msg_count,
394 x_msg_data => x_msg_data
395 );
396
397 -- If any errors happen abort API.
398 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
399 RAISE Fnd_Api.G_EXC_ERROR;
400 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
401 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
402 END IF;
403
404 --
405 -- Validate the record before inserting.
406 --
407 Validate_ActMetric (
408 p_api_version => l_api_version,
409 p_init_msg_list => p_init_msg_list,
410 p_validation_level => p_validation_level,
411 x_msg_count => x_msg_count,
412 x_msg_data => x_msg_data,
413 x_return_status => l_return_status,
414 p_act_metric_rec => l_act_metrics_rec
415 );
416
417 -- If any errors happen abort API.
418 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
419 RAISE Fnd_Api.G_EXC_ERROR;
420 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
421 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
422 END IF;
423
424 --
425 -- Debug message.
426 --
427 IF (OZF_DEBUG_HIGH_ON) THEN
428
429 ozf_utility_pvt.debug_message(l_full_name ||': insert');
430 END IF;
431
432 IF l_act_metrics_rec.activity_metric_id IS NULL THEN
433 LOOP
434 --
435 -- Set the value for the PK.
436 OPEN c_act_met_id;
437 FETCH c_act_met_id INTO l_act_metrics_rec.activity_metric_id;
438 CLOSE c_act_met_id;
439
440 OPEN c_act_metr_count(l_act_metrics_rec.activity_metric_id);
441 FETCH c_act_metr_count INTO l_act_metr_count ;
442 CLOSE c_act_metr_count ;
443
444 EXIT WHEN l_act_metr_count = 0 ;
445 END LOOP ;
446 END IF;
447
448 --dbms_output.put_line('Stat Before Insert : '||l_return_status);
449
450 --
451 -- Insert into the base table.
452 --
453 INSERT INTO ozf_act_metrics_all (
454 activity_metric_id,
455 creation_date,
456 created_by,
457 last_update_date,
458 last_updated_by,
459 last_update_login,
460 object_version_number,
461 act_metric_used_by_id,
462 arc_act_metric_used_by,
463 purchase_req_raised_flag,
464 application_id,
465 sensitive_data_flag,
466 budget_id,
467 metric_id,
468 transaction_currency_code,
469 trans_forecasted_value,
470 trans_committed_value,
471 trans_actual_value,
472 functional_currency_code,
473 func_forecasted_value,
474 dirty_flag,
475 func_committed_value,
476 func_actual_value,
477 last_calculated_date,
478 variable_value,
479 computed_using_function_value,
480 metric_uom_code,
481 org_id,
482 attribute_category,
483 difference_since_last_calc,
484 activity_metric_origin_id,
485 arc_activity_metric_origin,
486 days_since_last_refresh,
487 scenario_id,
488 SUMMARIZE_TO_METRIC,
489 hierarchy_id,
490 start_node,
491 from_level,
492 to_level,
493 from_date,
494 TO_DATE,
495 amount1,
496 amount2,
497 amount3,
498 percent1,
499 percent2,
500 percent3,
501 published_flag,
502 pre_function_name,
503 post_function_name,
504 attribute1,
505 attribute2,
506 attribute3,
507 attribute4,
508 attribute5,
509 attribute6,
510 attribute7,
511 attribute8,
512 attribute9,
513 attribute10,
514 attribute11,
515 attribute12,
516 attribute13,
517 attribute14,
518 attribute15,
519 description,
520 act_metric_date,
521 depend_act_metric,
522 function_used_by_id,
523 arc_function_used_by,
524 /* 05/15/2002 yzhao: 11.5.9 add 6 new columns for top-down bottom-up budgeting */
525 hierarchy_type,
526 status_code,
527 method_code,
528 action_code,
529 basis_year,
530 ex_start_node,
531 /* 05/15/2002 yzhao: add ends */
532 product_spread_time_id,
533 start_period_name,
534 end_period_name
535 )
536 VALUES (
537 l_act_metrics_rec.activity_metric_id,
538 sysdate,
539 Fnd_Global.User_ID,
540 sysdate,
541 Fnd_Global.User_ID,
542 Fnd_Global.Conc_Login_ID,
543 1, --Object Version Number
544 l_act_metrics_rec.act_metric_used_by_id,
545 l_act_metrics_rec.arc_act_metric_used_by,
546 NVL(l_act_metrics_rec.purchase_req_raised_flag,'N'),
547 l_act_metrics_rec.application_id,
548 l_act_metrics_rec.sensitive_data_flag,
549 l_act_metrics_rec.budget_id,
550 l_act_metrics_rec.metric_id,
551 l_act_metrics_rec.transaction_currency_code,
552 l_act_metrics_rec.trans_forecasted_value,
553 l_act_metrics_rec.trans_committed_value,
554 l_act_metrics_rec.trans_actual_value,
555 l_act_metrics_rec.functional_currency_code,
556 l_act_metrics_rec.func_forecasted_value,
557 NVL(l_act_metrics_rec.dirty_flag,'Y'),
558 l_act_metrics_rec.func_committed_value,
559 l_act_metrics_rec.func_actual_value,
560 l_act_metrics_rec.last_calculated_date,
561 l_act_metrics_rec.variable_value,
562 l_act_metrics_rec.computed_using_function_value,
563 l_act_metrics_rec.metric_uom_code,
564 MO_UTILS.get_default_org_id , -- org_id
565 l_act_metrics_rec.attribute_category,
566 l_act_metrics_rec.difference_since_last_calc,
567 l_act_metrics_rec.activity_metric_origin_id,
568 l_act_metrics_rec.arc_activity_metric_origin,
569 l_act_metrics_rec.days_since_last_refresh,
570 l_act_metrics_rec.scenario_id,
571 l_act_metrics_rec.SUMMARIZE_TO_METRIC,
572 l_act_metrics_rec.hierarchy_id,
573 l_act_metrics_rec.start_node,
574 l_act_metrics_rec.from_level,
575 l_act_metrics_rec.to_level,
576 l_act_metrics_rec.from_date,
577 l_act_metrics_rec.TO_DATE,
578 l_act_metrics_rec.amount1,
579 l_act_metrics_rec.amount2,
580 l_act_metrics_rec.amount3,
581 l_act_metrics_rec.percent1,
582 l_act_metrics_rec.percent2,
583 l_act_metrics_rec.percent3,
584 l_act_metrics_rec.published_flag,
585 l_act_metrics_rec.pre_function_name,
586 l_act_metrics_rec.post_function_name,
587 l_act_metrics_rec.attribute1,
588 l_act_metrics_rec.attribute2,
589 l_act_metrics_rec.attribute3,
590 l_act_metrics_rec.attribute4,
591 l_act_metrics_rec.attribute5,
592 l_act_metrics_rec.attribute6,
593 l_act_metrics_rec.attribute7,
594 l_act_metrics_rec.attribute8,
595 l_act_metrics_rec.attribute9,
596 l_act_metrics_rec.attribute10,
597 l_act_metrics_rec.attribute11,
598 l_act_metrics_rec.attribute12,
599 l_act_metrics_rec.attribute13,
600 l_act_metrics_rec.attribute14,
601 l_act_metrics_rec.attribute15,
602 l_act_metrics_rec.description,
603 l_act_metrics_rec.act_metric_date,
604 l_act_metrics_rec.depend_act_metric,
605 l_act_metrics_rec.function_used_by_id,
606 l_act_metrics_rec.arc_function_used_by,
607 /* 05/15/2002 yzhao: 11.5.9 add 6 new columns for top-down bottom-up budgeting */
608 l_act_metrics_rec.hierarchy_type,
609 l_act_metrics_rec.status_code,
610 l_act_metrics_rec.method_code,
611 l_act_metrics_rec.action_code,
612 l_act_metrics_rec.basis_year,
613 l_act_metrics_rec.ex_start_node,
614 /* 05/15/2002 yzhao: add ends */
615 l_act_metrics_rec.product_spread_time_id,
616 l_act_metrics_rec.start_period_name,
617 l_act_metrics_rec.end_period_name
618 );
619
620
621
622
623 --
624 -- Set OUT NOCOPY value.
625 --
626 x_activity_metric_id := l_act_metrics_rec.activity_metric_id;
627
628 --
629 -- End API Body.
630 --
631
632 --
633 -- Standard check for commit request.
634 --
635 --IF Fnd_Api.To_Boolean (p_commit) THEN
636 -- COMMIT WORK;
637 --END IF;
638
639 --
640 -- Standard API to get message count, and if 1,
641 -- set the message data OUT NOCOPY variable.
642 --
643 --Fnd_Msg_Pub.Count_And_Get (
644 -- p_count => x_msg_count,
645 -- p_data => x_msg_data,
646 -- p_encoded => Fnd_Api.G_FALSE
647 --);
648
649 --
650 -- Add success message to message list.
651 --
652 IF (OZF_DEBUG_HIGH_ON) THEN
653 ozf_utility_pvt.debug_message(l_full_name ||': end Success');
654 END IF;
655
656 /*
657 EXCEPTION
658 WHEN Fnd_Api.G_EXC_ERROR THEN
659 ROLLBACK TO Create_ActMetric2_pvt;
660 --ROLLBACK;
661 x_return_status := Fnd_Api.G_RET_STS_ERROR;
662 Fnd_Msg_Pub.Count_And_Get (
663 p_count => x_msg_count,
664 p_data => x_msg_data,
665 p_encoded => FND_API.G_FALSE
666 );
667 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
668 ROLLBACK TO Create_ActMetric2_pvt;
669 --ROLLBACK;
670 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
671 Fnd_Msg_Pub.Count_And_Get (
672 p_count => x_msg_count,
673 p_data => x_msg_data,
674 p_encoded => FND_API.G_FALSE
675 );
676 WHEN OTHERS THEN
677 ROLLBACK TO Create_ActMetric2_pvt;
678 --ROLLBACK;
679 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
680 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
681 Fnd_Msg_Pub.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
682 END IF;
683 Fnd_Msg_Pub.Count_And_Get (
684 p_count => x_msg_count,
685 p_data => x_msg_data,
686 p_encoded => FND_API.G_FALSE
687 );
688 */
689 END Create_ActMetric2;
690
691 -- Start of comments
692 -- NAME
693 -- Update_ActMetric
694 --
695 -- PURPOSE
696 -- Updates a metric in OZF_ACT_METRICS_ALL given the
697 -- record for the metrics.
698 --
699 -- NOTES
700 --
701 -- HISTORY
702 -- 05/26/1999 choang Created.
703 -- 10/9/1999 ptendulk Modified According to new Standards
704 -- 17-Apr-2000 tdonohoe Added new columns to Update statement to
705 -- support 11.5.2 release.
706 -- 05/07/2001 huili@ Added invalidating corresponding variable metrics
707 -- End of comments
708
709
710
711
712 PROCEDURE Update_ActMetric (
713 p_api_version IN NUMBER,
714 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
715 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
716 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
717
718 x_return_status OUT NOCOPY VARCHAR2,
719 x_msg_count OUT NOCOPY NUMBER,
720 x_msg_data OUT NOCOPY VARCHAR2,
721 p_act_metric_rec IN act_metric_rec_type
722 )
723 IS
724 L_API_VERSION CONSTANT NUMBER := 1.0;
725 L_API_NAME CONSTANT VARCHAR2(30) := 'UPDATE_ACTMETRIC';
726 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
727
728 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
729 l_actmet_rec act_metric_rec_type := p_act_metric_rec;
730
731 BEGIN
732
733
734
735 IF (OZF_DEBUG_HIGH_ON) THEN
736
737 ozf_utility_pvt.debug_message('Now updating act met id: '||p_act_metric_rec.activity_metric_id);
738
739 END IF;
740 --
741 -- Initialize savepoint.
742 --
743 SAVEPOINT Update_ActMetric_pvt;
744 --
745 -- Output debug message.
746 --
747 IF (OZF_DEBUG_HIGH_ON) THEN
748 ozf_utility_pvt.debug_message(l_full_name||': start');
749 END IF;
750 --
751 -- Initialize message list if p_init_msg_list is set to TRUE.
752 --
753 IF Fnd_Api.To_Boolean (p_init_msg_list) THEN
754 Fnd_Msg_Pub.Initialize;
755 END IF;
756
757 --
758 -- Standard check for API version compatibility.
759 --
760 IF NOT Fnd_Api.Compatible_API_Call (L_API_VERSION,
761 p_api_version,
762 L_API_NAME,
763 G_PKG_NAME)
764 THEN
765 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
766 END IF;
767
768 --
769 -- Initialize API return status to success.
770 --
771 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
772
773 --
774 -- Begin API Body
775 --
776 -- Debug Message
777
778 IF (OZF_DEBUG_HIGH_ON) THEN
779 ozf_utility_pvt.debug_message(l_full_name ||': validate');
780 END IF;
781
782 -- replace g_miss_char/num/date with current column values
783 Complete_ActMetric_Rec(p_act_metric_rec, l_actmet_rec);
784
785
786 -- Do Currency Conversion
787 Default_ActMetric(
788 p_init_msg_list => p_init_msg_list,
789 -- p_act_metric_rec => l_actmet_rec,
790 p_validation_mode => Jtf_Plsql_Api.G_UPDATE,
791 x_complete_rec => l_actmet_rec,
792 x_return_status => l_return_status,
793 x_msg_count => x_msg_count,
794 x_msg_data => x_msg_data ) ;
795 -- If any errors happen abort API.
796 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
797 RAISE Fnd_Api.G_EXC_ERROR;
798 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
799 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
800 END IF;
801
802 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_item THEN
803 Validate_ActMetric_items(
804 p_act_metric_rec => l_actmet_rec,
805 p_validation_mode => Jtf_Plsql_Api.g_update,
806 x_return_status => l_return_status
807 );
808 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
809 RAISE Fnd_Api.g_exc_unexpected_error;
810 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
811 RAISE Fnd_Api.g_exc_error;
812 END IF;
813 END IF;
814
815
816 -- replace g_miss_char/num/date with current column values
817 --Complete_ActMetric_Rec(l_actmet_rec, l_actmet_rec);
818
819 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_record THEN
820
821 Validate_ActMetric_Record(
822 p_act_metric_rec => l_actmet_rec,
823 p_complete_rec => l_actmet_rec,
824 p_operation_mode => G_UPDATE,
825 x_return_status => l_return_status
826 );
827 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
828 RAISE Fnd_Api.g_exc_unexpected_error;
829 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
830 RAISE Fnd_Api.g_exc_error;
831 END IF;
832 END IF;
833
834
835 IF (OZF_DEBUG_HIGH_ON) THEN
836 ozf_utility_pvt.debug_message(l_full_name ||': update Activity Metrics Table');
837 END IF;
838
839 -- Update OZF_ACT_METRICS_ALL
840 UPDATE ozf_act_metrics_all
841 SET object_version_number= object_version_number + 1,
842 act_metric_used_by_id = l_actmet_rec.act_metric_used_by_id,
843 arc_act_metric_used_by = l_actmet_rec.arc_act_metric_used_by,
844 purchase_req_raised_flag = l_actmet_rec.purchase_req_raised_flag,
845 application_id = l_actmet_rec.application_id,
846 sensitive_data_flag = l_actmet_rec.sensitive_data_flag,
847 budget_id = l_actmet_rec.budget_id ,
848 metric_id = l_actmet_rec.metric_id,
849 transaction_currency_code= l_actmet_rec.transaction_currency_code,
850 trans_forecasted_value = l_actmet_rec.trans_forecasted_value,
851 trans_committed_value = l_actmet_rec.trans_committed_value,
852 trans_actual_value = l_actmet_rec.trans_actual_value,
853 functional_currency_code = l_actmet_rec.functional_currency_code,
854 func_forecasted_value = l_actmet_rec.func_forecasted_value,
855 func_committed_value = l_actmet_rec.func_committed_value,
856 func_actual_value = l_actmet_rec.func_actual_value,
857 dirty_flag = l_actmet_rec.dirty_flag,
858 last_calculated_date = l_actmet_rec.last_calculated_date,
859 variable_value = l_actmet_rec.variable_value,
860 computed_using_function_value =
861 l_actmet_rec.computed_using_function_value,
862 metric_uom_code = l_actmet_rec.metric_uom_code,
863 difference_since_last_calc = l_actmet_rec.difference_since_last_calc,
864 activity_metric_origin_id= l_actmet_rec.activity_metric_origin_id,
865 arc_activity_metric_origin = l_actmet_rec.arc_activity_metric_origin,
866 hierarchy_id = l_actmet_rec.hierarchy_id,
867 start_node = l_actmet_rec.start_node,
868 from_level = l_actmet_rec.from_level,
869 to_level = l_actmet_rec.to_level,
870 from_date = l_actmet_rec.from_date,
871 TO_DATE = l_actmet_rec.TO_DATE,
872 amount1 = l_actmet_rec.amount1,
873 amount2 = l_actmet_rec.amount2,
874 amount3 = l_actmet_rec.amount3,
875 percent1 = l_actmet_rec.percent1,
876 percent2 = l_actmet_rec.percent2,
877 percent3 = l_actmet_rec.percent3,
878 published_flag = l_actmet_rec.published_flag,
879 pre_function_name = l_actmet_rec.pre_function_name,
880 post_function_name = l_actmet_rec.post_function_name,
881 last_update_date = SYSDATE,
882 last_updated_by = Fnd_Global.User_ID,
883 last_update_login = Fnd_Global.Conc_Login_ID,
884 attribute_category = l_actmet_rec.attribute_category,
885 attribute1 = l_actmet_rec.attribute1,
886 attribute2 = l_actmet_rec.attribute2,
887 attribute3 = l_actmet_rec.attribute3,
888 attribute4 = l_actmet_rec.attribute4,
889 attribute5 = l_actmet_rec.attribute5,
890 attribute6 = l_actmet_rec.attribute6,
891 attribute7 = l_actmet_rec.attribute7,
892 attribute8 = l_actmet_rec.attribute8,
893 attribute9 = l_actmet_rec.attribute9,
894 attribute10 = l_actmet_rec.attribute10,
895 attribute11 = l_actmet_rec.attribute11,
896 attribute12 = l_actmet_rec.attribute12,
897 attribute13 = l_actmet_rec.attribute13,
898 attribute14 = l_actmet_rec.attribute14,
899 attribute15 = l_actmet_rec.attribute15,
900 description = l_actmet_rec.description,
901 act_metric_date = l_actmet_rec.act_metric_date,
902 depend_act_metric = l_actmet_rec.depend_act_metric,
903 function_used_by_id = l_actmet_rec.function_used_by_id,
904 arc_function_used_by = l_actmet_rec.arc_function_used_by,
905 /* 05/15/2002 yzhao: 11.5.9 add 6 new columns for top-down bottom-up budgeting */
906 hierarchy_type = l_actmet_rec.hierarchy_type,
907 status_code = l_actmet_rec.status_code,
908 method_code = l_actmet_rec.method_code,
909 action_code = l_actmet_rec.action_code,
910 basis_year = l_actmet_rec.basis_year,
911 ex_start_node = l_actmet_rec.ex_start_node,
912 /* 05/15/2002 yzhao: add ends */
913
914 /* kvattiku April 23, 04 Update extra paramters in Quota */
915 product_spread_time_id = l_actmet_rec.product_spread_time_id,
916 start_period_name = l_actmet_rec.start_period_name,
917 end_period_name = l_actmet_rec.end_period_name
918 WHERE activity_metric_id = l_actmet_rec.activity_metric_id;
919
920 IF (SQL%NOTFOUND)
921 THEN
922 --
923 -- Add error message to API message list.
924 --
925 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
926 Fnd_Message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
927 Fnd_Msg_Pub.ADD;
928 END IF;
929 RAISE Fnd_Api.g_exc_error;
930 END IF;
931
932 IF Fnd_Api.to_boolean(p_commit) THEN
933 COMMIT;
934 END IF;
935
936 --
937 -- Standard API to get message count, and if 1,
938 -- set the message data OUT variable.
939 --
940 Fnd_Msg_Pub.Count_And_Get (
941 p_count => x_msg_count,
942 p_data => x_msg_data,
943 p_encoded => Fnd_Api.G_FALSE
944 );
945
946 --
947 -- Debug message.
948 --
949 IF (OZF_DEBUG_HIGH_ON) THEN
950 ozf_utility_pvt.debug_message(l_full_name ||': end');
951 END IF;
952
953
954 EXCEPTION
955 WHEN Fnd_Api.G_EXC_ERROR THEN
956 ROLLBACK TO Update_ActMetric_pvt;
957 x_return_status := Fnd_Api.G_RET_STS_ERROR;
958 Fnd_Msg_Pub.Count_And_Get (
959 p_count => x_msg_count,
960 p_data => x_msg_data,
961 p_encoded => FND_API.G_FALSE
962 );
963 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
964 ROLLBACK TO Update_ActMetric_pvt;
965 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
966 Fnd_Msg_Pub.Count_And_Get (
967 p_count => x_msg_count,
968 p_data => x_msg_data,
969 p_encoded => FND_API.G_FALSE
970 );
971 WHEN OTHERS THEN
972 ROLLBACK TO Update_ActMetric_pvt;
973 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
974 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
975 Fnd_Msg_Pub.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
976 END IF;
977 Fnd_Msg_Pub.Count_And_Get (
978 p_count => x_msg_count,
979 p_data => x_msg_data,
980 p_encoded => FND_API.G_FALSE
981 );
982 END Update_ActMetric;
983
984
985 -- Start of comments
986 -- NAME
987 -- Validate_ActMetric
988 --
989 -- PURPOSE
990 -- Validation API for Activity metrics.
991 --
992
993 -- NOTES
994 --
995 -- HISTORY
996 -- 05/26/1999 choang Created.
997 -- 10/9/1999 ptendulk Modified according to new standards
998 --
999 -- End of comments
1000
1001 PROCEDURE Validate_ActMetric (
1002 p_api_version IN NUMBER,
1003 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
1004 p_validation_level IN NUMBER := Fnd_Api.G_Valid_Level_Full,
1005
1006 x_return_status OUT NOCOPY VARCHAR2,
1007 x_msg_count OUT NOCOPY NUMBER,
1008 x_msg_data OUT NOCOPY VARCHAR2,
1009
1010 p_act_metric_rec IN act_metric_rec_type
1011 )
1012 IS
1013 L_API_VERSION CONSTANT NUMBER := 1.0;
1014 L_API_NAME CONSTANT VARCHAR2(30) := 'VALIDATE_ACTMETRIC';
1015 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1016
1017 l_return_status VARCHAR2(1);
1018
1019 BEGIN
1020 --
1021 -- Output debug message.
1022 --
1023 IF (OZF_DEBUG_HIGH_ON) THEN
1024 ozf_utility_pvt.debug_message(l_full_name||': start');
1025 END IF;
1026
1027 --
1028 -- Initialize message list if p_init_msg_list is set to TRUE.
1029 --
1030 IF Fnd_Api.To_Boolean (p_init_msg_list) THEN
1031 Fnd_Msg_Pub.Initialize;
1032 END IF;
1033
1034 --
1035 -- Standard check for API version compatibility.
1036 --
1037 IF NOT Fnd_Api.Compatible_API_Call (L_API_VERSION,
1038 p_api_version,
1039 L_API_NAME,
1040 G_PKG_NAME)
1041 THEN
1042 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1043 END IF;
1044
1045 --
1046 -- Initialize API return status to success.
1047 --
1048 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1049
1050 --
1051 -- Begin API Body.
1052 --
1053
1054 IF (OZF_DEBUG_HIGH_ON) THEN
1055 ozf_utility_pvt.debug_message(l_full_name||': Validate items');
1056 END IF;
1057
1058 -- Validate required items in the record.
1059 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_item THEN
1060
1061 Validate_ActMetric_items(
1062 p_act_metric_rec => p_act_metric_rec,
1063 p_validation_mode => Jtf_Plsql_Api.g_create,
1064 x_return_status => l_return_status
1065 );
1066
1067 -- If any errors happen abort API.
1068 IF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
1069 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1070 ELSIF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
1071 RAISE Fnd_Api.G_EXC_ERROR;
1072 END IF;
1073 END IF;
1074
1075 IF (OZF_DEBUG_HIGH_ON) THEN
1076 ozf_utility_pvt.debug_message(l_full_name||': check record');
1077 END IF;
1078
1079 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_record THEN
1080 Validate_ActMetric_record(
1081 p_act_metric_rec => p_act_metric_rec,
1082 p_complete_rec => NULL,
1083 x_return_status => l_return_status
1084 );
1085
1086 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1087 RAISE Fnd_Api.g_exc_unexpected_error;
1088 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
1089 RAISE Fnd_Api.g_exc_error;
1090 END IF;
1091 END IF;
1092
1093 --
1094 -- End API Body.
1095 --
1096
1097 --
1098 -- Standard API to get message count, and if 1,
1099 -- set the message data OUT NOCOPY variable.
1100 --
1101 Fnd_Msg_Pub.Count_And_Get (
1102 p_count => x_msg_count,
1103 p_data => x_msg_data,
1104 p_encoded => Fnd_Api.G_FALSE
1105 );
1106
1107 IF (OZF_DEBUG_HIGH_ON) THEN
1108 ozf_utility_pvt.debug_message(l_full_name ||': end');
1109 END IF;
1110
1111 EXCEPTION
1112 WHEN Fnd_Api.G_EXC_ERROR THEN
1113 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1114 Fnd_Msg_Pub.Count_And_Get (
1115 p_count => x_msg_count,
1116 p_data => x_msg_data,
1117 p_encoded => FND_API.G_FALSE
1118 );
1119 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1120 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1121 Fnd_Msg_Pub.Count_And_Get (
1122 p_count => x_msg_count,
1123 p_data => x_msg_data,
1124 p_encoded => FND_API.G_FALSE
1125 );
1126 WHEN OTHERS THEN
1127 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1128 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
1129 Fnd_Msg_Pub.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
1130 END IF;
1131 Fnd_Msg_Pub.Count_And_Get (
1132 p_count => x_msg_count,
1133 p_data => x_msg_data,
1134 p_encoded => FND_API.G_FALSE
1135 );
1136 END Validate_ActMetric;
1137
1138
1139 -- Start of comments.
1140 --
1141 -- NAME
1142 -- Check_Req_ActMetrics_Items
1143 --
1144 -- PURPOSE
1145 -- Validate required items metrics associated with business
1146 -- objects.
1147 --
1148 -- NOTES
1149 --
1150 -- HISTORY
1151 -- 05/26/1999 choang Created.
1152 -- 10/9/1999 ptendulk Modified According to new standards.
1153 --
1154 -- End of comments.
1155
1156 PROCEDURE Check_Req_ActMetrics_Items (
1157 p_act_metric_rec IN act_metric_rec_type,
1158 x_return_status OUT NOCOPY VARCHAR2
1159 )
1160 IS
1161 BEGIN
1162 -- Initialize return status to success.
1163 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1164
1165 -- APPLICATION_ID
1166
1167 IF p_act_metric_rec.application_id IS NULL
1168 THEN
1169 -- missing required fields
1170 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
1171 THEN -- MMSG
1172 Fnd_Message.Set_Name('OZF', 'OZF_METR_MISSING_APP_ID');
1173 Fnd_Msg_Pub.ADD;
1174 END IF;
1175
1176 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1177
1178 -- If any error happens abort API.
1179 RETURN;
1180 END IF;
1181
1182 -- ARC_METRIC_USED_FOR_OBJECT
1183
1184 IF p_act_metric_rec.arc_act_metric_used_by IS NULL
1185 THEN
1186 -- missing required fields
1187 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
1188 THEN -- MMSG
1189 Fnd_Message.Set_Name('OZF', 'OZF_METR_MISSING_ARC_USED_FOR');
1190 Fnd_Msg_Pub.ADD;
1191 END IF;
1192
1193 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1194
1195 -- If any error happens abort API.
1196 RETURN;
1197 END IF;
1198
1199
1200 -- ACT_METRIC_USED_BY_ID
1201
1202 IF p_act_metric_rec.act_metric_used_by_id IS NULL
1203 THEN
1204 -- missing required fields
1205 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
1206 THEN -- MMSG
1207 Fnd_Message.Set_Name('OZF', 'OZF_METR_MISSING_ARC_USED_FOR');
1208 Fnd_Msg_Pub.ADD;
1209 END IF;
1210
1211 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1212
1213 -- If any error happens abort API.
1214 RETURN;
1215 END IF;
1216
1217 -- METRIC_ID
1218
1219 IF p_act_metric_rec.metric_id IS NULL
1220 THEN
1221 -- missing required fields
1222 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
1223 THEN -- MMSG
1224 Fnd_Message.Set_Name('OZF', 'OZF_METR_MISSING_METRIC_ID');
1225 Fnd_Msg_Pub.ADD;
1226 END IF;
1227
1228 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1229
1230 -- If any error happens abort API.
1231 RETURN;
1232 END IF;
1233
1234 /*----------------------------------------------------------------
1235 -- commented by bgeorge om 01/18/2000, removed UOM as a req item
1236 -- METRIC_UOM_CODE
1237
1238 IF p_act_metric_rec.metric_uom_code IS NULL
1239 THEN
1240 -- missing required fields
1241 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1242 THEN -- MMSG
1243 FND_MESSAGE.Set_Name('OZF', 'OZF_METR_MISSING_UOM');
1244 FND_MSG_PUB.Add;
1245 END IF;
1246
1247 x_return_status := FND_API.G_RET_STS_ERROR;
1248
1249 -- If any error happens abort API.
1250 RETURN;
1251 END IF;
1252 -- end of comment 01/18/2000
1253 ---------------------------------------------------------------*/
1254
1255
1256 -- Sensitive Data flag
1257
1258 IF p_act_metric_rec.sensitive_data_flag IS NULL
1259 THEN
1260 -- missing required fields
1261 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
1262 THEN -- MMSG
1263 Fnd_Message.Set_Name('OZF', 'OZF_METR_MISSING_SENSITIVE');
1264 Fnd_Msg_Pub.ADD;
1265 END IF;
1266
1267 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1268
1269 -- If any error happens abort API.
1270 RETURN;
1271 END IF;
1272
1273
1274 EXCEPTION
1275 WHEN OTHERS THEN
1276 RAISE;
1277 END Check_Req_ActMetrics_Items;
1278
1279
1280 --
1281 -- Start of comments.
1282 --
1283 -- NAME
1284 -- Check_ActMetric_UK_Items
1285 --
1286 -- PURPOSE
1287 -- Perform Uniqueness check for Activity metrics.
1288 --
1289 -- NOTES
1290 --
1291 -- HISTORY
1292 -- 10/9/1999 ptendulk Created.
1293 --
1294 -- End of comments.
1295
1296
1297 PROCEDURE Check_ActMetric_UK_Items(
1298 p_act_metric_rec IN act_metric_rec_type,
1299 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
1300 x_return_status OUT NOCOPY VARCHAR2
1301 )
1302 IS
1303 l_where_clause VARCHAR2(2000); -- Used By Check_Uniqueness
1304 l_test VARCHAR2(1) := NULL;
1305 CURSOR c_check_function(l_metric_id NUMBER,
1306 l_arc_act_metric_used_by VARCHAR2,
1307 l_act_metric_used_by_id NUMBER,
1308 l_arc_function_used_by VARCHAR2,
1309 l_function_used_by_id NUMBER) IS
1310 SELECT 'x'
1311 FROM ams_metrics_all_b b
1312 WHERE metric_id = l_metric_id
1313 AND ((metric_calculation_type = 'FUNCTION'
1314 AND NOT EXISTS (SELECT 'x' FROM ozf_act_metrics_all a
1315 WHERE a.metric_id = b.metric_id
1316 AND a.arc_act_metric_used_by = l_arc_act_metric_used_by
1317 AND a.act_metric_used_by_id = l_act_metric_used_by_id
1318 AND NVL(a.arc_function_used_by,'') = NVL(l_arc_function_used_by,'')
1319 AND NVL(a.function_used_by_id,-1) = NVL(l_function_used_by_id,-1)
1320 ))
1321 OR metric_calculation_type <> 'FUNCTION');
1322
1323 BEGIN
1324
1325 x_return_status := Fnd_Api.g_ret_sts_success;
1326
1327 -- For Create_ActMetric2, when activity_metric_id is passed in, we need to
1328 -- check if this activity_metric_id is unique.
1329 IF p_validation_mode = Jtf_Plsql_Api.g_create
1330 AND p_act_metric_rec.activity_metric_id IS NOT NULL
1331 THEN
1332 l_where_clause := ' activity_metric_id = '||p_act_metric_rec.activity_metric_id ;
1333
1334 IF ozf_utility_pvt.Check_Uniqueness(
1335 p_table_name => 'ozf_act_metrics_all',
1336 p_where_clause => l_where_clause
1337 ) = Fnd_Api.g_false
1338 THEN
1339 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error)
1340 THEN
1341 Fnd_Message.set_name('OZF', 'OZF_METR_ACT_DUP_ID');
1342 Fnd_Msg_Pub.ADD;
1343 END IF;
1344 x_return_status := Fnd_Api.g_ret_sts_error;
1345 RETURN;
1346 END IF;
1347 END IF;
1348
1349 -- check other unique items
1350
1351 -- Function metrics may only be added once.
1352 IF p_validation_mode = Jtf_Plsql_Api.g_create THEN
1353 l_test := NULL;
1354 OPEN c_check_function(p_act_metric_rec.metric_id,
1355 p_act_metric_rec.arc_act_metric_used_by,
1356 p_act_metric_rec.act_metric_used_by_id,
1357 p_act_metric_rec.arc_function_used_by,
1358 p_act_metric_rec.function_used_by_id);
1359 FETCH c_check_function INTO l_test;
1360 CLOSE c_check_function;
1361
1362 IF l_test IS NULL THEN
1363 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
1364 Fnd_Message.set_name('OZF', 'OZF_ACT_MET_DUP_FUNCTION');
1365 Fnd_Msg_Pub.ADD;
1366 END IF;
1367 x_return_status := Fnd_Api.g_ret_sts_error;
1368 END IF;
1369
1370 END IF;
1371
1372 END Check_ActMetric_Uk_Items;
1373
1374
1375 --
1376 -- Start of comments.
1377 --
1378 -- NAME
1379 -- Check_ActMetric_Items
1380 --
1381 -- PURPOSE
1382 -- Perform item level validation for Activity metrics.
1383 --
1384 -- NOTES
1385 --
1386 -- HISTORY
1387 -- 05/26/1999 choang Created.
1388 -- 10/9/1999 ptendulk Modified According to new Standards
1389 -- 05/08/2000 tdonohoe Modified, do not perform Metric_Id Check if the Activity Metric
1390 -- is associated with a Forecast.
1391 -- 06-28-2000 rchahal@us Modified to allow metric creation for Fund.
1392 --
1393 -- End of comments.
1394
1395 PROCEDURE Check_ActMetric_Items (
1396 p_act_metric_rec IN act_metric_rec_type,
1397 x_return_status OUT NOCOPY VARCHAR2
1398 )
1399 IS
1400 l_item_name VARCHAR2(30); -- Used to standardize error messages.
1401 l_act_metrics_rec act_metric_rec_type := p_act_metric_rec;
1402 l_return_status VARCHAR2(1);
1403
1404 l_table_name VARCHAR2(30);
1405 l_pk_name VARCHAR2(30);
1406 l_pk_value VARCHAR2(30);
1407 l_pk_data_type VARCHAR2(30);
1408 l_additional_where_clause VARCHAR2(4000); -- Used by Check_FK_Exists.
1409 l_lookup_type VARCHAR2(30);
1410
1411 BEGIN
1412 -- Initialize return status to success.
1413 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1414
1415 --
1416
1417 --
1418 -- Begin Validate Referential
1419 --
1420
1421 -- METRIC_ID
1422 -- Do not validate FK if NULL
1423 -- Do not validate if Activity Metric is associated with a Forecast.
1424
1425
1426 IF l_act_metrics_rec.arc_act_metric_used_by <> Fnd_Api.G_MISS_CHAR AND
1427 ( l_act_metrics_rec.arc_act_metric_used_by NOT IN ('FCST', 'FUND') )
1428 THEN --added 05-08-2000 tdonohoe
1429 --added 06-28-2000 rchahal
1430
1431 IF l_act_metrics_rec.metric_id <> Fnd_Api.G_MISS_NUM THEN
1432 l_table_name := 'AMS_METRICS_VL';
1433 l_pk_name := 'METRIC_ID';
1434 l_pk_value := l_act_metrics_rec.metric_id;
1435 l_pk_data_type := ozf_utility_pvt.G_NUMBER;
1436 l_additional_where_clause := NULL ;
1437
1438 IF ozf_utility_pvt.Check_FK_Exists (
1439 p_table_name => l_table_name
1440 ,p_pk_name => l_pk_name
1441 ,p_pk_value => l_pk_value
1442 ,p_pk_data_type => l_pk_data_type
1443 ,p_additional_where_clause => l_additional_where_clause
1444 ) = Fnd_Api.G_FALSE
1445 THEN
1446 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
1447 THEN
1448 Fnd_Message.Set_Name('OZF', 'OZF_METR_INVALID_MET');
1449 Fnd_Msg_Pub.ADD;
1450 END IF;
1451
1452 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1453 RETURN;
1454 END IF; -- Check_FK_Exists
1455
1456 END IF;
1457 END IF;--added 05-08-2000 tdonohoe
1458
1459 -- TRANSACTION_CURRENCY_CODE
1460 -- Do not validate FK if NULL
1461 IF l_act_metrics_rec.transaction_currency_code <> Fnd_Api.G_MISS_CHAR THEN
1462 l_table_name := 'FND_CURRENCIES';
1463 l_pk_name := 'CURRENCY_CODE';
1464 l_pk_value := l_act_metrics_rec.transaction_currency_code;
1465 l_pk_data_type := ozf_utility_pvt.G_VARCHAR2;
1466 l_additional_where_clause := ' enabled_flag = ''Y''';
1467 IF ozf_utility_pvt.Check_FK_Exists (
1468 p_table_name => l_table_name
1469 ,p_pk_name => l_pk_name
1470 ,p_pk_value => l_pk_value
1471 ,p_pk_data_type => l_pk_data_type
1472 ,p_additional_where_clause => l_additional_where_clause
1473 ) = Fnd_Api.G_FALSE
1474 THEN
1475 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
1476 THEN
1477 Fnd_Message.Set_Name('OZF', 'OZF_METR_INVALID_TRANS_CUR');
1478 Fnd_Msg_Pub.ADD;
1479 END IF;
1480
1481 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1482 RETURN;
1483 END IF; -- Check_FK_Exists
1484 END IF;
1485
1486 -- FUNCTIONAL_CURRENCY_CODE
1487 -- Do not validate FK if NULL
1488 IF l_act_metrics_rec.functional_currency_code <> Fnd_Api.G_MISS_CHAR THEN
1489 l_table_name := 'FND_CURRENCIES';
1490 l_pk_name := 'CURRENCY_CODE';
1491 l_pk_value := l_act_metrics_rec.functional_currency_code;
1492 l_pk_data_type := ozf_utility_pvt.G_VARCHAR2;
1493 l_additional_where_clause := ' enabled_flag = ''Y''';
1494
1495 IF ozf_utility_pvt.Check_FK_Exists (
1496 p_table_name => l_table_name
1497 ,p_pk_name => l_pk_name
1498 ,p_pk_value => l_pk_value
1499 ,p_pk_data_type => l_pk_data_type
1500 ,p_additional_where_clause => l_additional_where_clause
1501 ) = Fnd_Api.G_FALSE
1502 THEN
1503 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
1504 THEN
1505 Fnd_Message.Set_Name('OZF', 'OZF_METR_INVALID_FUNC_CUR');
1506 Fnd_Msg_Pub.ADD;
1507 END IF;
1508
1509 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1510 RETURN;
1511 END IF; -- Check_FK_Exists
1512
1513 END IF;
1514
1515 --
1516 -- End Validate Referential
1517 --
1518
1519 --
1520 -- Begin Validate Flags
1521 --
1522
1523 -- SENSITIVE_DATA_FLAG
1524 IF l_act_metrics_rec.sensitive_data_flag <> Fnd_Api.G_MISS_CHAR THEN
1525 IF ozf_utility_pvt.Is_Y_Or_N (l_act_metrics_rec.sensitive_data_flag)
1526 = Fnd_Api.G_FALSE
1527 THEN
1528 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
1529 THEN
1530 Fnd_Message.Set_Name('OZF', 'OZF_METR_INVALID_SENS_FLAG');
1531 Fnd_Msg_Pub.ADD;
1532 END IF;
1533
1534 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1535 RETURN;
1536 END IF; -- Check_FK_Exists
1537 END IF;
1538
1539 --
1540 -- End Validate Flags
1541 --
1542
1543 --
1544 -- Begin Validate LOOKUPS
1545 --
1546
1547 --
1548 -- End Validate LOOKUPS
1549 --
1550
1551
1552 -- ARC_METRIC_USED_FOR_OBJECT
1553 -- DMVINCEN 03/11/2002: Added Dialog Components.
1554 -- DMVINCEN 03/11/2003: Removed Dialogue Components.
1555 IF l_act_metrics_rec.arc_act_metric_used_by <> Fnd_Api.G_MISS_CHAR THEN
1556 IF l_act_metrics_rec.arc_act_metric_used_by NOT IN
1557 ('CAMP','CSCH','EVEH','EVEO','DELV','FUND','FCST','RCAM','EONE')
1558 --'DILG','AMS_COMP_START','AMS_COMP_SHOW_WEB_PAGE','AMS_COMP_END')
1559 THEN
1560 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1561 Fnd_Message.Set_Name ('OZF', 'OZF_METR_INVALID_USED_BY');
1562 Fnd_Msg_Pub.ADD;
1563 END IF;
1564
1565 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1566 RETURN;
1567 END IF;
1568 END IF;
1569
1570 -- ARC_ACTIVITY_METRIC_ORIGIN
1571 -- DMVINCEN 03/11/2002: Added Dialog Components.
1572 -- DMVINCEN 03/11/2003: Removed Dialogue Components.
1573 IF l_act_metrics_rec.arc_activity_metric_origin <> Fnd_Api.G_MISS_CHAR THEN
1574 IF l_act_metrics_rec.arc_activity_metric_origin NOT IN
1575 ('CAMP','CSCH','EVEH','EVEO','DELV','FUND','FCST','RCAM','EONE')
1576 --'DILG','AMS_COMP_START','AMS_COMP_SHOW_WEB_PAGE','AMS_COMP_END')
1577 THEN
1578 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1579 Fnd_Message.Set_Name ('OZF', 'OZF_METR_INVALID_ORIGIN');
1580 Fnd_Msg_Pub.ADD;
1581 END IF;
1582
1583 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1584 RETURN;
1585 END IF;
1586 END IF;
1587
1588 --
1589 -- End Other Business Rule Validations
1590 --
1591
1592 EXCEPTION
1593 WHEN OTHERS THEN
1594 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1595 END Check_ActMetric_Items;
1596
1597
1598 -- Start of comments
1599 -- NAME
1600 -- Validate_Alloc_Record
1601 --
1602 -- PURPOSE
1603 -- Validate budget allocation
1604 -- allocation amount can not exceed avail amt
1605 -- start level <= end level
1606 -- start date <= end date
1607 -- start date >= budget start date
1608 -- end date <= budget end date
1609 -- start node falls in the start level
1610 -- can not set 'ex-start-node' if start level = end level
1611 --
1612 -- NOTES
1613 --
1614 -- HISTORY
1615 -- 08/03/2001 YZHAO Created
1616 -- 05/15/2002 YZHAO Updated for 11.5.9 Top-down Bottom-up Budgeting
1617 -- 02/20/2003 YZHAO 11.5.9: can not set 'ex-start-node' if start level = end level
1618 -- End of comments
1619
1620 PROCEDURE Validate_Alloc_Record (
1621 p_act_metric_rec IN act_metric_rec_type,
1622 x_return_status OUT NOCOPY VARCHAR2
1623 )
1624 IS
1625 l_start_node NUMBER;
1626 l_available_budget NUMBER;
1627 l_fund_type VARCHAR(30);
1628 l_budget_start_date DATE;
1629 l_budget_end_date DATE;
1630 l_alloc_start_date DATE;
1631 l_alloc_end_date DATE;
1632 l_default_start_date DATE := TO_DATE('01/01/1900', 'DD/MM/YYYY');
1633 l_default_end_date DATE := TO_DATE('31/12/2900', 'DD/MM/YYYY');
1634
1635 -- rimehrot, fixed sql repository violation 14892133
1636 CURSOR c_get_budget_info IS
1637 SELECT (NVL(original_budget, 0) - NVL(holdback_amt, 0) + NVL(transfered_in_amt,0) - NVL(transfered_out_amt, 0))
1638 , NVL(start_date_active, l_default_start_date)
1639 , NVL(end_date_active, l_default_end_date)
1640 , fund_type
1641 FROM ozf_funds_all_b
1642 WHERE fund_id = p_act_metric_rec.act_metric_used_by_id;
1643
1644 CURSOR c_check_start_node_terr IS
1645 SELECT 1
1646 FROM ozf_terr_v
1647 WHERE hierarchy_id = p_act_metric_rec.hierarchy_id
1648 AND level_depth = p_act_metric_rec.from_level
1649 AND node_id = p_act_metric_rec.start_node;
1650
1651 BEGIN
1652
1653 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1654 IF NOT (Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)) THEN
1655 RETURN;
1656 END IF;
1657
1658 /* yzhao: 11.5.9 need to add check required items for top-down bottom-up budgeting
1659 action_code, hierarchy_type, hierarch_id, from_level, start_node, end_level(TERR or GEOGRAPHY only)
1660 method_code, fact_value, status_code
1661 */
1662
1663 OPEN c_get_budget_info;
1664 FETCH c_get_budget_info INTO l_available_budget, l_budget_start_date, l_budget_end_date, l_fund_type;
1665 CLOSE c_get_budget_info;
1666
1667 /* Can not allocate if available budget amount is 0 */
1668 IF (l_available_budget = 0) THEN
1669 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1670 IF l_fund_type = 'QUOTA' THEN
1671 Fnd_Message.set_name('OZF', 'OZF_TP_ALLOCNOAVAIL_ERROR');
1672 ELSE
1673 Fnd_Message.set_name('OZF', 'OZF_FUND_ALLOCNOAVAIL_ERROR');
1674 END IF;
1675 Fnd_Msg_Pub.ADD;
1676 END IF;
1677
1678 /* allocation amount can not exceed available amount */
1679 IF (p_act_metric_rec.func_actual_value > l_available_budget) THEN
1680 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1681 IF l_fund_type = 'QUOTA' THEN
1682 Fnd_Message.set_name('OZF', 'OZF_TP_ALLOCAMOUNT_ERROR');
1683 ELSE
1684 Fnd_Message.set_name('OZF', 'OZF_FUND_ALLOCAMOUNT_ERROR');
1685 END IF;
1686 Fnd_Message.set_token('ALLOCAMT', p_act_metric_rec.func_actual_value);
1687 Fnd_Message.set_token('BUDAMT', l_available_budget);
1688 Fnd_Msg_Pub.ADD;
1689 END IF;
1690
1691 /* check start level <= end level */
1692 IF (p_act_metric_rec.from_level <> Fnd_Api.g_miss_num AND
1693 p_act_metric_rec.to_level <> Fnd_Api.g_miss_num) THEN
1694 IF (NVL(p_act_metric_rec.from_level, 0) > NVL(p_act_metric_rec.to_level, 1000)) THEN
1695 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1696 Fnd_Message.set_name('OZF', 'OZF_FUND_ALLOCLEVEL_ERROR');
1697 Fnd_Msg_Pub.ADD;
1698 END IF;
1699
1700 /* 11.5.9: can not set 'ex-start-node' if start level = end level */
1701 IF (p_act_metric_rec.ex_start_node = 'Y' AND
1702 p_act_metric_rec.from_level = p_act_metric_rec.to_level) THEN
1703 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1704 Fnd_Message.set_name('OZF', 'OZF_FUND_ALLOCEXSTNODE_ERROR');
1705 Fnd_Msg_Pub.ADD;
1706 END IF;
1707 END IF;
1708
1709 IF (p_act_metric_rec.from_date <> Fnd_Api.g_miss_date) THEN
1710 l_alloc_start_date := NVL(p_act_metric_rec.from_date, l_default_start_date);
1711 END IF;
1712
1713 IF (p_act_metric_rec.TO_DATE <> Fnd_Api.g_miss_date) THEN
1714 l_alloc_end_date := NVL(p_act_metric_rec.TO_DATE, l_default_end_date);
1715 END IF;
1716
1717 /* check start date >= budget start date */
1718 IF (l_alloc_start_date < l_budget_start_date) THEN
1719 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1720 IF l_fund_type = 'QUOTA' THEN
1721 Fnd_Message.set_name('OZF', 'OZF_TP_ALLOCSTARTDATE_ERROR');
1722 ELSE
1723 Fnd_Message.set_name('OZF', 'OZF_FUND_ALLOCSTARTDATE_ERROR');
1724 END IF;
1725 Fnd_Msg_Pub.ADD;
1726 END IF;
1727
1728 /* check end date <= budget end date */
1729 IF (l_alloc_end_date > l_budget_end_date) THEN
1730 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1731 IF l_fund_type = 'QUOTA' THEN
1732 Fnd_Message.set_name('OZF', 'OZF_TP_ALLOCENDDATE_ERROR');
1733 ELSE
1734 Fnd_Message.set_name('OZF', 'OZF_FUND_ALLOCENDDATE_ERROR');
1735 END IF;
1736 Fnd_Msg_Pub.ADD;
1737 END IF;
1738
1739 /* check start date <= end date */
1740 IF (l_alloc_start_date > l_alloc_end_date) THEN
1741 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1742 Fnd_Message.set_name('OZF', 'OZF_FUND_ALLOCDATE_ERROR');
1743 Fnd_Msg_Pub.ADD;
1744 END IF;
1745
1746 /* check start node falls in the start level */
1747 IF (p_act_metric_rec.HIERARCHY_TYPE = 'TERRITORY') THEN
1748 OPEN c_check_start_node_terr;
1749 FETCH c_check_start_node_terr INTO l_start_node;
1750 IF c_check_start_node_terr%NOTFOUND THEN
1751 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1752 Fnd_Message.set_name('OZF', 'OZF_FUND_ALLOCSTARTNODE_ERROR');
1753 Fnd_Msg_Pub.ADD;
1754 END IF;
1755 CLOSE c_check_start_node_terr;
1756 /* for future release
1757 ELSIF (p_act_metric_rec.HIERARCHY_TYPE = 'GEOGRAPHY') THEN
1758 */
1759 END IF;
1760
1761 /* 11.5.9: method 'PRIOR_YEARS_SALE' can only be used by 'TERRITORY' hierarchy and must have year set */
1762 IF (p_act_metric_rec.method_code = 'PRIOR_SALES_TOTAL') THEN
1763 IF (p_act_metric_rec.hierarchy_type <> 'TERRITORY') THEN
1764 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1765 Fnd_Message.set_name('OZF', 'OZF_FUND_ALLOCPRISALE_ERROR');
1766 Fnd_Msg_Pub.ADD;
1767 END IF;
1768 IF (p_act_metric_rec.basis_year is null) THEN
1769 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1770 Fnd_Message.set_name('OZF', 'OZF_FUND_ALLOCBASISYEAR_ERROR');
1771 Fnd_Msg_Pub.ADD;
1772 END IF;
1773 END IF;
1774
1775 EXCEPTION
1776 WHEN OTHERS THEN
1777 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1778
1779 END Validate_Alloc_Record;
1780
1781
1782 --
1783 -- Start of comments.
1784 --
1785 -- NAME
1786 -- Validate_ActMetric_Record
1787 --
1788 -- PURPOSE
1789 -- Perform Record Level and Other business validations for metrics.
1790 --
1791 -- NOTES
1792 --
1793 -- HISTORY
1794 -- 10/11/1999 ptendulk Created.
1795 -- 05/08/2000 tdonohoe Modified, do not perform FK check on Metric_Id
1796 -- if Activity Metric is associated with a Forecast.
1797 -- 06/28/2000 rchahal Modified, do not perform FK check on Metric_Id
1798 -- if Activity Metric is associated with a Fund.
1799 -- 05/01/2003 choang bug 2931351 - restrict update of costs and revenues
1800 -- End of comments.
1801
1802 PROCEDURE Validate_ActMetric_record(
1803 p_act_metric_rec IN act_metric_rec_type,
1804 p_complete_rec IN act_metric_rec_type,
1805 x_return_status OUT NOCOPY VARCHAR2
1806 )
1807 IS
1808 BEGIN
1809 Validate_ActMetric_Record (
1810 p_act_metric_rec => p_act_metric_rec,
1811 p_complete_rec => p_complete_rec,
1812 p_operation_mode => G_CREATE,
1813 x_return_status => x_return_status
1814 );
1815 END;
1816
1817
1818 --
1819 -- Start of comments.
1820 --
1821 -- NAME
1822 -- Validate_ActMetric_Record
1823 --
1824 -- PURPOSE
1825 -- Perform Record Level and Other business validations for metrics. Allow for
1826 -- different types of validation based on the type of database operation.
1827 --
1828 -- NOTES
1829 --
1830 -- HISTORY
1831 -- 06-May-2003 choang bug 2931351 - restrict update of costs and revenues
1832 -- End of comments.
1833
1834 PROCEDURE Validate_ActMetric_record(
1835 p_act_metric_rec IN act_metric_rec_type,
1836 p_complete_rec IN act_metric_rec_type,
1837 p_operation_mode IN VARCHAR2,
1838 x_return_status OUT NOCOPY VARCHAR2
1839 )
1840 IS
1841 L_ALLOW_ACTUAL_UPDATE_METR CONSTANT VARCHAR2(30) := 'AMS_ALLOW_ACTUAL_UPDATE';
1842
1843 l_act_metrics_rec act_metric_rec_type := p_act_metric_rec ;
1844
1845 l_table_name VARCHAR2(30);
1846 l_pk_name VARCHAR2(30);
1847 l_pk_value VARCHAR2(30);
1848 l_pk_data_type VARCHAR2(30);
1849 l_additional_where_clause VARCHAR2(4000); -- Used by Check_FK_Exists.
1850
1851 l_allow_actual_update VARCHAR2(1);
1852
1853 l_return_status VARCHAR2(1);
1854
1855 l_object_name AMS_LOOKUPS.MEANING%TYPE;
1856
1857 CURSOR c_ref_metric (p_act_metric_id NUMBER) IS
1858 SELECT func_actual_value,
1859 trans_forecasted_value
1860 FROM ozf_act_metrics_all
1861 WHERE activity_metric_id = p_act_metric_id;
1862 l_ref_metric_rec c_ref_metric%ROWTYPE;
1863 BEGIN
1864
1865 x_return_status := Fnd_Api.g_ret_sts_success;
1866
1867 OPEN c_ref_metric (l_act_metrics_rec.activity_metric_id);
1868 FETCH c_ref_metric INTO l_ref_metric_rec;
1869 CLOSE c_ref_metric;
1870
1871
1872 -- Validate All Modes --
1873 IF l_act_metrics_rec.arc_act_metric_used_by <> Fnd_Api.G_MISS_CHAR THEN
1874
1875 IF l_act_metrics_rec.act_metric_used_by_id = Fnd_Api.G_MISS_NUM THEN
1876 l_act_metrics_rec.act_metric_used_by_id :=
1877 p_complete_rec.act_metric_used_by_id;
1878 END IF;
1879
1880 IF l_act_metrics_rec.metric_id = Fnd_Api.G_MISS_NUM THEN
1881 l_act_metrics_rec.metric_id := p_complete_rec.metric_id;
1882 END IF;
1883
1884
1885 -- Get table_name and pk_name for the ARC qualifier.
1886 ozf_utility_pvt.Get_Qual_Table_Name_And_PK (
1887 p_sys_qual => l_act_metrics_rec.arc_act_metric_used_by,
1888 x_return_status => l_return_status,
1889 x_table_name => l_table_name,
1890 x_pk_name => l_pk_name
1891 );
1892
1893 l_pk_value := l_act_metrics_rec.act_metric_used_by_id;
1894 l_pk_data_type := ozf_utility_pvt.G_NUMBER;
1895 l_additional_where_clause := NULL;
1896
1897 IF ozf_utility_pvt.Check_FK_Exists (
1898 p_table_name => l_table_name
1899 ,p_pk_name => l_pk_name
1900 ,p_pk_value => l_pk_value
1901 ,p_pk_data_type => l_pk_data_type
1902 ,p_additional_where_clause => l_additional_where_clause
1903 ) = Fnd_Api.G_FALSE
1904 THEN
1905 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1906 l_object_name := ozf_utility_pvt.get_lookup_meaning(
1907 'AMS_SYS_ARC_QUALIFIER',l_act_metrics_rec.arc_act_metric_used_by);
1908 Fnd_Message.Set_Name ('OZF', 'OZF_METR_INVALID_OBJECT');
1909 Fnd_Message.Set_Token('OBJTYPE',l_object_name);
1910 Fnd_Message.Set_Token('OBJID',l_pk_value);
1911 Fnd_Msg_Pub.ADD;
1912 END IF;
1913
1914 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1915 END IF;
1916
1917 -- 08/06/2001 yzhao: validation for budget allocation
1918 IF l_act_metrics_rec.arc_act_metric_used_by = 'FUND' THEN
1919 Validate_Alloc_Record (
1920 p_act_metric_rec => l_act_metrics_rec,
1921 x_return_status => l_return_status
1922 );
1923 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
1924 x_return_status := l_return_status;
1925 END IF;
1926 END IF;
1927
1928 END IF;
1929
1930 -- METRIC_UOM_CODE
1931 IF l_act_metrics_rec.metric_uom_code <> Fnd_Api.G_MISS_CHAR THEN
1932 IF l_act_metrics_rec.metric_id = Fnd_Api.G_MISS_NUM THEN
1933 l_act_metrics_rec.metric_id := p_complete_rec.metric_id ;
1934 END IF;
1935
1936 /* yzhao: is METRIC_UOM_CODE used in our code? should it be removed? */
1937 l_table_name := 'MTL_UNITS_OF_MEASURE';
1938 l_pk_name := 'UOM_CODE';
1939 l_pk_value := l_act_metrics_rec.metric_uom_code;
1940 l_pk_data_type := ozf_utility_pvt.G_VARCHAR2;
1941 -- l_additional_where_clause := ' uom_class = ''' || l_metric_details_rec.uom_type || '''' ;
1942
1943 IF ozf_utility_pvt.Check_FK_Exists (
1944 p_table_name => l_table_name
1945 ,p_pk_name => l_pk_name
1946 ,p_pk_value => l_pk_value
1947 ,p_pk_data_type => l_pk_data_type
1948 ,p_additional_where_clause => l_additional_where_clause
1949 ) = Fnd_Api.G_FALSE
1950 THEN
1951 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
1952 THEN
1953 Fnd_Message.Set_Name('OZF', 'OZF_METR_INVALID_UOM');
1954 Fnd_Msg_Pub.ADD;
1955 END IF;
1956
1957 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1958 END IF; -- Check_FK_Exists
1959 END IF;
1960
1961
1962 IF l_act_metrics_rec.arc_activity_metric_origin <> Fnd_Api.G_MISS_CHAR THEN
1963 IF l_act_metrics_rec.activity_metric_origin_id = Fnd_Api.G_MISS_NUM THEN
1964 l_act_metrics_rec.activity_metric_origin_id :=
1965 p_complete_rec.activity_metric_origin_id;
1966 END IF;
1967
1968 -- Get table_name and pk_name for the ARC qualifier.
1969 ozf_utility_pvt.Get_Qual_Table_Name_And_PK (
1970 p_sys_qual => l_act_metrics_rec.arc_activity_metric_origin,
1971 x_return_status => l_return_status,
1972 x_table_name => l_table_name,
1973 x_pk_name => l_pk_name
1974 );
1975
1976 l_pk_value := l_act_metrics_rec.activity_metric_origin_id;
1977 l_pk_data_type := ozf_utility_pvt.G_NUMBER;
1978 l_additional_where_clause := NULL;
1979
1980 IF ozf_utility_pvt.Check_FK_Exists (
1981 p_table_name => l_table_name
1982 ,p_pk_name => l_pk_name
1983 ,p_pk_value => l_pk_value
1984 ,p_pk_data_type => l_pk_data_type
1985 ,p_additional_where_clause => l_additional_where_clause
1986 ) = Fnd_Api.G_FALSE
1987 THEN
1988 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1989 Fnd_Message.Set_Name ('OZF', 'OZF_METR_INVALID_ORIGIN');
1990 Fnd_Msg_Pub.ADD;
1991 END IF;
1992
1993 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1994 END IF;
1995 END IF;
1996
1997 --
1998 -- Other Business Rule Validations
1999 --
2000 /*
2001 EXCEPTION
2002 WHEN OTHERS THEN
2003 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2004 */
2005 END Validate_ActMetric_record;
2006
2007
2008
2009 --
2010 -- Start of comments.
2011 --
2012 -- NAME
2013 -- Validate_ActMetric_Items
2014 --
2015 -- PURPOSE
2016 -- Perform All Item level validation for Activity metrics.
2017 --
2018 -- NOTES
2019 --
2020 -- HISTORY
2021 -- 10/11/1999 ptendulk Created.
2022 --
2023 -- End of comments.
2024
2025 PROCEDURE Validate_ActMetric_items(
2026 p_act_metric_rec IN act_metric_rec_type,
2027 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
2028 x_return_status OUT NOCOPY VARCHAR2
2029 )
2030 IS
2031 BEGIN
2032
2033 Check_Req_ActMetrics_Items(
2034 p_act_metric_rec => p_act_metric_rec,
2035 x_return_status => x_return_status
2036 );
2037 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
2038 RETURN;
2039 END IF;
2040
2041 Check_ActMetric_Uk_Items(
2042 p_act_metric_rec => p_act_metric_rec,
2043 p_validation_mode => p_validation_mode,
2044 x_return_status => x_return_status
2045 );
2046
2047 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
2048 RETURN;
2049 END IF;
2050
2051 Check_ActMetric_Items(
2052 p_act_metric_rec => p_act_metric_rec,
2053 x_return_status => x_return_status
2054 );
2055
2056 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
2057 RETURN;
2058 END IF;
2059
2060
2061
2062 END Validate_ActMetric_items;
2063
2064 --
2065 -- Begin of section added by ptendulk - 10/11/1999
2066 --
2067 -- NAME
2068 -- Complete_Metric_Rec
2069 --
2070 -- PURPOSE
2071 -- Returns the Initialized Activity Metric Record
2072 --
2073 -- NOTES
2074 --
2075 -- HISTORY
2076 -- 07/19/1999 choang Created.
2077 --
2078 PROCEDURE Complete_ActMetric_Rec(
2079 p_act_metric_rec IN act_metric_rec_type,
2080 x_complete_rec IN OUT NOCOPY act_metric_rec_type
2081 )
2082 IS
2083 CURSOR c_act_metric IS
2084 SELECT *
2085 FROM ozf_act_metrics_all
2086 WHERE activity_metric_id = p_act_metric_rec.activity_metric_id;
2087
2088 l_act_metric_rec c_act_metric%ROWTYPE;
2089 BEGIN
2090
2091 x_complete_rec := p_act_metric_rec;
2092
2093 OPEN c_act_metric;
2094 FETCH c_act_metric INTO l_act_metric_rec;
2095 IF c_act_metric%NOTFOUND THEN
2096 CLOSE c_act_metric;
2097 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
2098 Fnd_Message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
2099 Fnd_Msg_Pub.ADD;
2100 END IF;
2101 RAISE Fnd_Api.g_exc_error;
2102 END IF;
2103 CLOSE c_act_metric;
2104
2105
2106 IF p_act_metric_rec.act_metric_used_by_id = Fnd_Api.G_MISS_NUM THEN
2107 x_complete_rec.act_metric_used_by_id := NULL;
2108 END IF;
2109 IF p_act_metric_rec.act_metric_used_by_id IS NULL THEN
2110 x_complete_rec.act_metric_used_by_id := l_act_metric_rec.act_metric_used_by_id;
2111 END IF;
2112
2113 IF p_act_metric_rec.arc_act_metric_used_by = Fnd_Api.G_MISS_CHAR THEN
2114 x_complete_rec.arc_act_metric_used_by := NULL;
2115 END IF;
2116 IF p_act_metric_rec.arc_act_metric_used_by IS NULL THEN
2117 x_complete_rec.arc_act_metric_used_by := l_act_metric_rec.arc_act_metric_used_by;
2118 END IF;
2119
2120 IF p_act_metric_rec.purchase_req_raised_flag = Fnd_Api.G_MISS_CHAR THEN
2121 x_complete_rec.purchase_req_raised_flag := NULL;
2122 END IF;
2123 IF p_act_metric_rec.purchase_req_raised_flag IS NULL THEN
2124 x_complete_rec.purchase_req_raised_flag := l_act_metric_rec.purchase_req_raised_flag;
2125 END IF;
2126
2127 IF p_act_metric_rec.application_id = Fnd_Api.G_MISS_NUM THEN
2128 x_complete_rec.application_id := NULL;
2129 END IF;
2130 IF p_act_metric_rec.application_id IS NULL THEN
2131 x_complete_rec.application_id := l_act_metric_rec.application_id;
2132 END IF;
2133
2134 IF p_act_metric_rec.sensitive_data_flag = Fnd_Api.G_MISS_CHAR THEN
2135 x_complete_rec.sensitive_data_flag := NULL;
2136 END IF;
2137 IF p_act_metric_rec.sensitive_data_flag IS NULL THEN
2138 x_complete_rec.sensitive_data_flag := l_act_metric_rec.sensitive_data_flag;
2139 END IF;
2140
2141 IF p_act_metric_rec.budget_id = Fnd_Api.G_MISS_NUM THEN
2142 x_complete_rec.budget_id := NULL;
2143 END IF;
2144 IF p_act_metric_rec.budget_id IS NULL THEN
2145 x_complete_rec.budget_id := l_act_metric_rec.budget_id;
2146 END IF;
2147
2148 IF p_act_metric_rec.metric_id = Fnd_Api.G_MISS_NUM THEN
2149 x_complete_rec.metric_id := NULL;
2150 END IF;
2151 IF p_act_metric_rec.metric_id IS NULL THEN
2152 x_complete_rec.metric_id := l_act_metric_rec.metric_id;
2153 END IF;
2154
2155 IF p_act_metric_rec.transaction_currency_code = Fnd_Api.G_MISS_CHAR THEN
2156 x_complete_rec.transaction_currency_code := NULL;
2157 END IF;
2158 IF p_act_metric_rec.transaction_currency_code IS NULL THEN
2159 x_complete_rec.transaction_currency_code := l_act_metric_rec.transaction_currency_code;
2160 END IF;
2161
2162 IF NVL(p_act_metric_rec.trans_forecasted_value,-1) = Fnd_Api.G_MISS_NUM THEN
2163 x_complete_rec.trans_forecasted_value := NULL;
2164 END IF;
2165 IF NVL(p_act_metric_rec.trans_forecasted_value,-1) IS NULL THEN
2166 x_complete_rec.trans_forecasted_value := l_act_metric_rec.trans_forecasted_value;
2167 END IF;
2168
2169 IF p_act_metric_rec.trans_committed_value = Fnd_Api.G_MISS_NUM THEN
2170 x_complete_rec.trans_committed_value := NULL;
2171 END IF;
2172 IF p_act_metric_rec.trans_committed_value IS NULL THEN
2173 x_complete_rec.trans_committed_value := l_act_metric_rec.trans_committed_value;
2174 END IF;
2175
2176 IF p_act_metric_rec.trans_actual_value = Fnd_Api.G_MISS_NUM THEN
2177 x_complete_rec.trans_actual_value := NULL;
2178 END IF;
2179 IF p_act_metric_rec.trans_actual_value IS NULL THEN
2180 x_complete_rec.trans_actual_value := l_act_metric_rec.trans_actual_value;
2181 END IF;
2182
2183 IF p_act_metric_rec.functional_currency_code = Fnd_Api.G_MISS_CHAR THEN
2184 x_complete_rec.functional_currency_code := NULL;
2185 END IF;
2186 IF p_act_metric_rec.functional_currency_code IS NULL THEN
2187 x_complete_rec.functional_currency_code := l_act_metric_rec.functional_currency_code;
2188 END IF;
2189
2190 IF p_act_metric_rec.func_forecasted_value = Fnd_Api.G_MISS_NUM THEN
2191 x_complete_rec.func_forecasted_value := NULL;
2192 END IF;
2193 IF p_act_metric_rec.func_forecasted_value IS NULL THEN
2194 x_complete_rec.func_forecasted_value := l_act_metric_rec.func_forecasted_value;
2195 END IF;
2196
2197 IF p_act_metric_rec.func_committed_value = Fnd_Api.G_MISS_NUM THEN
2198 x_complete_rec.func_committed_value := NULL;
2199 END IF;
2200 IF p_act_metric_rec.func_committed_value IS NULL THEN
2201 x_complete_rec.func_committed_value := l_act_metric_rec.func_committed_value;
2202 END IF;
2203
2204 IF p_act_metric_rec.func_actual_value = Fnd_Api.G_MISS_NUM THEN
2205 x_complete_rec.func_actual_value := NULL;
2206 END IF;
2207 IF p_act_metric_rec.func_actual_value IS NULL THEN
2208 x_complete_rec.func_actual_value := l_act_metric_rec.func_actual_value;
2209 END IF;
2210
2211 IF p_act_metric_rec.dirty_flag = Fnd_Api.G_MISS_CHAR THEN
2212 x_complete_rec.dirty_flag := NULL;
2213 END IF;
2214 IF p_act_metric_rec.dirty_flag IS NULL THEN
2215 IF (l_act_metric_rec.trans_actual_value <>
2216 x_complete_rec.trans_actual_value) OR
2217 (l_act_metric_rec.transaction_currency_code <>
2218 x_complete_rec.transaction_currency_code) OR
2219 (l_act_metric_rec.trans_forecasted_value <>
2220 x_complete_rec.trans_forecasted_value) OR
2221 (l_act_metric_rec.variable_value <>
2222 x_complete_rec.variable_value) THEN
2223 --SVEERAVE, 10/16/00 to default dirty_flag to Y incase of changes in
2224 -- actual/forecasted values.
2225 x_complete_rec.dirty_flag := 'Y';
2226 ELSE
2227 x_complete_rec.dirty_flag := NVL(l_act_metric_rec.dirty_flag,'Y');
2228 END IF;
2229 END IF;
2230
2231 IF p_act_metric_rec.last_calculated_date = Fnd_Api.G_MISS_DATE THEN
2232 x_complete_rec.last_calculated_date := NULL;
2233 END IF;
2234 IF p_act_metric_rec.last_calculated_date IS NULL THEN
2235 x_complete_rec.last_calculated_date := l_act_metric_rec.last_calculated_date;
2236 END IF;
2237
2238 IF p_act_metric_rec.variable_value = Fnd_Api.G_MISS_NUM THEN
2239 x_complete_rec.variable_value := NULL;
2240 END IF;
2241 IF p_act_metric_rec.variable_value IS NULL THEN
2242 x_complete_rec.variable_value := l_act_metric_rec.variable_value;
2243 END IF;
2244
2245 IF p_act_metric_rec.computed_using_function_value = Fnd_Api.G_MISS_NUM THEN
2246 x_complete_rec.computed_using_function_value := NULL;
2247 END IF;
2248 IF p_act_metric_rec.computed_using_function_value IS NULL THEN
2249 x_complete_rec.computed_using_function_value := l_act_metric_rec.computed_using_function_value;
2250 END IF;
2251
2252 IF p_act_metric_rec.metric_uom_code = Fnd_Api.G_MISS_CHAR THEN
2253 x_complete_rec.metric_uom_code := NULL;
2254 END IF;
2255 IF p_act_metric_rec.metric_uom_code IS NULL THEN
2256 x_complete_rec.metric_uom_code := l_act_metric_rec.metric_uom_code;
2257 END IF;
2258
2259 IF p_act_metric_rec.attribute_category = Fnd_Api.G_MISS_CHAR THEN
2260 x_complete_rec.attribute_category := NULL;
2261 END IF;
2262 IF p_act_metric_rec.attribute_category IS NULL THEN
2263 x_complete_rec.attribute_category := l_act_metric_rec.attribute_category;
2264 END IF;
2265
2266 IF p_act_metric_rec.difference_since_last_calc = Fnd_Api.G_MISS_NUM THEN
2267 x_complete_rec.difference_since_last_calc := NULL;
2268 END IF;
2269 IF p_act_metric_rec.difference_since_last_calc IS NULL THEN
2270 x_complete_rec.difference_since_last_calc := l_act_metric_rec.difference_since_last_calc;
2271 END IF;
2272
2273 IF p_act_metric_rec.activity_metric_origin_id = Fnd_Api.G_MISS_NUM THEN
2274 x_complete_rec.activity_metric_origin_id := NULL;
2275 END IF;
2276 IF p_act_metric_rec.activity_metric_origin_id IS NULL THEN
2277 x_complete_rec.activity_metric_origin_id := l_act_metric_rec.activity_metric_origin_id;
2278 END IF;
2279
2280 IF p_act_metric_rec.arc_activity_metric_origin = Fnd_Api.G_MISS_CHAR THEN
2281 x_complete_rec.arc_activity_metric_origin := NULL;
2282 END IF;
2283 IF p_act_metric_rec.arc_activity_metric_origin IS NULL THEN
2284 x_complete_rec.arc_activity_metric_origin := l_act_metric_rec.arc_activity_metric_origin;
2285 END IF;
2286
2287 IF p_act_metric_rec.days_since_last_refresh = Fnd_Api.G_MISS_NUM THEN
2288 x_complete_rec.days_since_last_refresh := NULL;
2289 END IF;
2290 IF p_act_metric_rec.days_since_last_refresh IS NULL THEN
2291 x_complete_rec.days_since_last_refresh := l_act_metric_rec.days_since_last_refresh;
2292 END IF;
2293
2294 IF p_act_metric_rec.scenario_id = Fnd_Api.G_MISS_NUM THEN
2295 x_complete_rec.scenario_id := NULL;
2296 END IF;
2297 IF p_act_metric_rec.scenario_id IS NULL THEN
2298 x_complete_rec.scenario_id := l_act_metric_rec.scenario_id;
2299 END IF;
2300
2301 /***************************************************************/
2302 /*added 17-Apr-2000 tdonohoe@us support 11.5.2 columns */
2303 /***************************************************************/
2304
2305 IF p_act_metric_rec.hierarchy_id = Fnd_Api.G_MISS_NUM THEN
2306 x_complete_rec.hierarchy_id := NULL;
2307 END IF;
2308 IF p_act_metric_rec.hierarchy_id IS NULL THEN
2309 x_complete_rec.hierarchy_id := l_act_metric_rec.hierarchy_id;
2310 END IF;
2311
2312 IF p_act_metric_rec.start_node = Fnd_Api.G_MISS_NUM THEN
2313 x_complete_rec.start_node := NULL;
2314 END IF;
2315 IF p_act_metric_rec.start_node IS NULL THEN
2316 x_complete_rec.start_node := l_act_metric_rec.start_node;
2317 END IF;
2318
2319 IF p_act_metric_rec.from_level = Fnd_Api.G_MISS_NUM THEN
2320 x_complete_rec.from_level := NULL;
2321 END IF;
2322 IF p_act_metric_rec.from_level IS NULL THEN
2323 x_complete_rec.from_level := l_act_metric_rec.from_level;
2324 END IF;
2325
2326 IF p_act_metric_rec.to_level = Fnd_Api.G_MISS_NUM THEN
2327 x_complete_rec.to_level := NULL;
2328 END IF;
2329 IF p_act_metric_rec.to_level IS NULL THEN
2330 x_complete_rec.to_level := l_act_metric_rec.to_level;
2331 END IF;
2332
2333 IF p_act_metric_rec.from_date = Fnd_Api.G_MISS_DATE THEN
2334 x_complete_rec.from_date := NULL;
2335 END IF;
2336 IF p_act_metric_rec.from_date IS NULL THEN
2337 x_complete_rec.from_date := l_act_metric_rec.from_date;
2338 END IF;
2339
2340 IF p_act_metric_rec.TO_DATE = Fnd_Api.G_MISS_DATE THEN
2341 x_complete_rec.TO_DATE := NULL;
2342 END IF;
2343 IF p_act_metric_rec.TO_DATE IS NULL THEN
2344 x_complete_rec.TO_DATE := l_act_metric_rec.TO_DATE;
2345 END IF;
2346
2347 IF p_act_metric_rec.amount1 = Fnd_Api.G_MISS_NUM THEN
2348 x_complete_rec.amount1 := NULL;
2349 END IF;
2350 IF p_act_metric_rec.amount1 IS NULL THEN
2351 x_complete_rec.amount1 := l_act_metric_rec.amount1;
2352 END IF;
2353
2354 IF p_act_metric_rec.amount2 = Fnd_Api.G_MISS_NUM THEN
2355 x_complete_rec.amount2 := NULL;
2356 END IF;
2357 IF p_act_metric_rec.amount2 IS NULL THEN
2358 x_complete_rec.amount2 := l_act_metric_rec.amount2;
2359 END IF;
2360
2361 IF p_act_metric_rec.amount3 = Fnd_Api.G_MISS_NUM THEN
2362 x_complete_rec.amount3 := NULL;
2363 END IF;
2364 IF p_act_metric_rec.amount3 IS NULL THEN
2365 x_complete_rec.amount3 := l_act_metric_rec.amount3;
2366 END IF;
2367
2368 IF p_act_metric_rec.percent1 = Fnd_Api.G_MISS_NUM THEN
2369 x_complete_rec.percent1 := NULL;
2370 END IF;
2371 IF p_act_metric_rec.percent1 IS NULL THEN
2372 x_complete_rec.percent1 := l_act_metric_rec.percent1;
2373 END IF;
2374
2375 IF p_act_metric_rec.percent2 = Fnd_Api.G_MISS_NUM THEN
2376 x_complete_rec.percent2 := NULL;
2377 END IF;
2378 IF p_act_metric_rec.percent2 IS NULL THEN
2379 x_complete_rec.percent2 := l_act_metric_rec.percent2;
2380 END IF;
2381
2382 IF p_act_metric_rec.percent3 = Fnd_Api.G_MISS_NUM THEN
2383 x_complete_rec.percent3 := NULL;
2384 END IF;
2385 IF p_act_metric_rec.percent3 IS NULL THEN
2386 x_complete_rec.percent3 := l_act_metric_rec.percent3;
2387 END IF;
2388
2389 IF p_act_metric_rec.published_flag = Fnd_Api.G_MISS_CHAR THEN
2390 x_complete_rec.published_flag := NULL;
2391 END IF;
2392 IF p_act_metric_rec.published_flag IS NULL THEN
2393 x_complete_rec.published_flag := l_act_metric_rec.published_flag;
2394 END IF;
2395
2396 IF p_act_metric_rec.pre_function_name = Fnd_Api.G_MISS_CHAR THEN
2397 x_complete_rec.pre_function_name := NULL;
2398 END IF;
2399 IF p_act_metric_rec.pre_function_name IS NULL THEN
2400 x_complete_rec.pre_function_name := l_act_metric_rec.pre_function_name;
2401 END IF;
2402
2403 IF p_act_metric_rec.post_function_name = Fnd_Api.G_MISS_CHAR THEN
2404 x_complete_rec.post_function_name := NULL;
2405 END IF;
2406 IF p_act_metric_rec.post_function_name IS NULL THEN
2407 x_complete_rec.post_function_name := l_act_metric_rec.post_function_name;
2408 END IF;
2409
2410 IF p_act_metric_rec.attribute1 = Fnd_Api.G_MISS_CHAR THEN
2411 x_complete_rec.attribute1 := NULL;
2412 END IF;
2413 IF p_act_metric_rec.attribute1 IS NULL THEN
2414 x_complete_rec.attribute1 := l_act_metric_rec.attribute1;
2415 END IF;
2416
2417 IF p_act_metric_rec.attribute2 = Fnd_Api.G_MISS_CHAR THEN
2418 x_complete_rec.attribute2 := NULL;
2419 END IF;
2420 IF p_act_metric_rec.attribute2 IS NULL THEN
2421 x_complete_rec.attribute2 := l_act_metric_rec.attribute2;
2422 END IF;
2423
2424 IF p_act_metric_rec.attribute3 = Fnd_Api.G_MISS_CHAR THEN
2425 x_complete_rec.attribute3 := NULL;
2426 END IF;
2427 IF p_act_metric_rec.attribute3 IS NULL THEN
2428 x_complete_rec.attribute3 := l_act_metric_rec.attribute3;
2429 END IF;
2430
2431 IF p_act_metric_rec.attribute4 = Fnd_Api.G_MISS_CHAR THEN
2432 x_complete_rec.attribute4 := NULL;
2433 END IF;
2434 IF p_act_metric_rec.attribute4 IS NULL THEN
2435 x_complete_rec.attribute4 := l_act_metric_rec.attribute4;
2436 END IF;
2437
2438 IF p_act_metric_rec.attribute5 = Fnd_Api.G_MISS_CHAR THEN
2439 x_complete_rec.attribute5 := NULL;
2440 END IF;
2441 IF p_act_metric_rec.attribute5 IS NULL THEN
2442 x_complete_rec.attribute5 := l_act_metric_rec.attribute5;
2443 END IF;
2444
2445 IF p_act_metric_rec.attribute6 = Fnd_Api.G_MISS_CHAR THEN
2446 x_complete_rec.attribute6 := NULL;
2447 END IF;
2448 IF p_act_metric_rec.attribute6 IS NULL THEN
2449 x_complete_rec.attribute6 := l_act_metric_rec.attribute6;
2450 END IF;
2451
2452 IF p_act_metric_rec.attribute7 = Fnd_Api.G_MISS_CHAR THEN
2453 x_complete_rec.attribute7 := NULL;
2454 END IF;
2455 IF p_act_metric_rec.attribute7 IS NULL THEN
2456 x_complete_rec.attribute7 := l_act_metric_rec.attribute7;
2457 END IF;
2458
2459 IF p_act_metric_rec.attribute8 = Fnd_Api.G_MISS_CHAR THEN
2460 x_complete_rec.attribute8 := NULL;
2461 END IF;
2462 IF p_act_metric_rec.attribute8 IS NULL THEN
2463 x_complete_rec.attribute8 := l_act_metric_rec.attribute8;
2464 END IF;
2465
2466 IF p_act_metric_rec.attribute9 = Fnd_Api.G_MISS_CHAR THEN
2467 x_complete_rec.attribute9 := NULL;
2468 END IF;
2469 IF p_act_metric_rec.attribute9 IS NULL THEN
2470 x_complete_rec.attribute9 := l_act_metric_rec.attribute9;
2471 END IF;
2472
2473 IF p_act_metric_rec.attribute10 = Fnd_Api.G_MISS_CHAR THEN
2474 x_complete_rec.attribute10 := NULL;
2475 END IF;
2476 IF p_act_metric_rec.attribute10 IS NULL THEN
2477 x_complete_rec.attribute10 := l_act_metric_rec.attribute10;
2478 END IF;
2479
2480 IF p_act_metric_rec.attribute11 = Fnd_Api.G_MISS_CHAR THEN
2481 x_complete_rec.attribute11 := NULL;
2482 END IF;
2483 IF p_act_metric_rec.attribute11 IS NULL THEN
2484 x_complete_rec.attribute11 := l_act_metric_rec.attribute11;
2485 END IF;
2486
2487 IF p_act_metric_rec.attribute12 = Fnd_Api.G_MISS_CHAR THEN
2488 x_complete_rec.attribute12 := NULL;
2489 END IF;
2490 IF p_act_metric_rec.attribute12 IS NULL THEN
2491 x_complete_rec.attribute12 := l_act_metric_rec.attribute12;
2492 END IF;
2493
2494 IF p_act_metric_rec.attribute13 = Fnd_Api.G_MISS_CHAR THEN
2495 x_complete_rec.attribute13 := NULL;
2496 END IF;
2497 IF p_act_metric_rec.attribute13 IS NULL THEN
2498 x_complete_rec.attribute13 := l_act_metric_rec.attribute13;
2499 END IF;
2500
2501 IF p_act_metric_rec.attribute14 = Fnd_Api.G_MISS_CHAR THEN
2502 x_complete_rec.attribute14 := NULL;
2503 END IF;
2504 IF p_act_metric_rec.attribute14 IS NULL THEN
2505 x_complete_rec.attribute14 := l_act_metric_rec.attribute14;
2506 END IF;
2507
2508 IF p_act_metric_rec.attribute15 = Fnd_Api.G_MISS_CHAR THEN
2509 x_complete_rec.attribute15 := NULL;
2510 END IF;
2511 IF p_act_metric_rec.attribute15 IS NULL THEN
2512 x_complete_rec.attribute15 := l_act_metric_rec.attribute15;
2513 END IF;
2514
2515 -- DMVINCEN 05/01/2001: New columns.
2516 IF p_act_metric_rec.act_metric_date = Fnd_Api.G_MISS_DATE THEN
2517 x_complete_rec.act_metric_date := NULL;
2518 END IF;
2519 IF p_act_metric_rec.act_metric_date IS NULL THEN
2520 x_complete_rec.act_metric_date := l_act_metric_rec.act_metric_date;
2521 END IF;
2522
2523 IF p_act_metric_rec.description = Fnd_Api.G_MISS_CHAR THEN
2524 x_complete_rec.description := NULL;
2525 END IF;
2526 IF p_act_metric_rec.description IS NULL THEN
2527 x_complete_rec.description := l_act_metric_rec.description;
2528 END IF;
2529
2530 -- DMVINCEN 05/01/2001: End new columns.
2531
2532 IF p_act_metric_rec.depend_act_metric = Fnd_Api.G_MISS_NUM THEN
2533 x_complete_rec.depend_act_metric := NULL;
2534 END IF;
2535 IF p_act_metric_rec.depend_act_metric IS NULL THEN
2536 x_complete_rec.depend_act_metric := l_act_metric_rec.depend_act_metric;
2537 END IF;
2538
2539 -- DMVINCEN 03/08/2002:
2540
2541 IF p_act_metric_rec.function_used_by_id = Fnd_Api.G_MISS_NUM THEN
2542 x_complete_rec.function_used_by_id := NULL;
2543 END IF;
2544 IF p_act_metric_rec.function_used_by_id IS NULL THEN
2545 x_complete_rec.function_used_by_id := l_act_metric_rec.function_used_by_id;
2546 END IF;
2547
2548 IF p_act_metric_rec.arc_function_used_by = Fnd_Api.G_MISS_CHAR THEN
2549 x_complete_rec.arc_function_used_by := NULL;
2550 END IF;
2551 IF p_act_metric_rec.arc_function_used_by IS NULL THEN
2552 x_complete_rec.arc_function_used_by := l_act_metric_rec.arc_function_used_by;
2553 END IF;
2554
2555 /* 05/15/2002 yzhao: add 6 new columns for top-down bottom-up budgeting */
2556 IF p_act_metric_rec.hierarchy_type = Fnd_Api.G_MISS_CHAR THEN
2557 x_complete_rec.hierarchy_type := NULL;
2558 END IF;
2559 IF p_act_metric_rec.hierarchy_type IS NULL THEN
2560 x_complete_rec.hierarchy_type := l_act_metric_rec.hierarchy_type;
2561 END IF;
2562
2563 IF p_act_metric_rec.status_code = Fnd_Api.G_MISS_CHAR THEN
2564 x_complete_rec.status_code := NULL;
2565 END IF;
2566 IF p_act_metric_rec.status_code IS NULL THEN
2567 x_complete_rec.status_code := l_act_metric_rec.status_code;
2568 END IF;
2569
2570 IF p_act_metric_rec.method_code = Fnd_Api.G_MISS_CHAR THEN
2571 x_complete_rec.method_code := NULL;
2572 END IF;
2573 IF p_act_metric_rec.method_code IS NULL THEN
2574 x_complete_rec.method_code := l_act_metric_rec.method_code;
2575 END IF;
2576
2577 IF p_act_metric_rec.action_code = Fnd_Api.G_MISS_CHAR THEN
2578 x_complete_rec.action_code := NULL;
2579 END IF;
2580 IF p_act_metric_rec.action_code IS NULL THEN
2581 x_complete_rec.action_code := l_act_metric_rec.action_code;
2582 END IF;
2583
2584 IF p_act_metric_rec.basis_year = Fnd_Api.G_MISS_NUM THEN
2585 x_complete_rec.basis_year := NULL;
2586 END IF;
2587 IF p_act_metric_rec.basis_year IS NULL THEN
2588 x_complete_rec.basis_year := l_act_metric_rec.basis_year;
2589 END IF;
2590
2591 IF p_act_metric_rec.ex_start_node = Fnd_Api.G_MISS_CHAR THEN
2592 x_complete_rec.ex_start_node := NULL;
2593 END IF;
2594 IF p_act_metric_rec.ex_start_node IS NULL THEN
2595 x_complete_rec.ex_start_node := l_act_metric_rec.ex_start_node;
2596 END IF;
2597 /* 05/15/2002 yzhao: add ends */
2598
2599 IF p_act_metric_rec.product_spread_time_id = Fnd_Api.G_MISS_NUM THEN
2600 x_complete_rec.product_spread_time_id := NULL;
2601 END IF;
2602 IF p_act_metric_rec.product_spread_time_id IS NULL THEN
2603 x_complete_rec.product_spread_time_id := l_act_metric_rec.product_spread_time_id;
2604 END IF;
2605
2606 IF p_act_metric_rec.start_period_name = Fnd_Api.G_MISS_CHAR THEN
2607 x_complete_rec.start_period_name := NULL;
2608 END IF;
2609 IF p_act_metric_rec.start_period_name IS NULL THEN
2610 x_complete_rec.start_period_name := l_act_metric_rec.start_period_name;
2611 END IF;
2612
2613 IF p_act_metric_rec.end_period_name = Fnd_Api.G_MISS_CHAR THEN
2614 x_complete_rec.end_period_name := NULL;
2615 END IF;
2616 IF p_act_metric_rec.end_period_name IS NULL THEN
2617 x_complete_rec.end_period_name := l_act_metric_rec.end_period_name;
2618 END IF;
2619
2620 END Complete_ActMetric_Rec ;
2621
2622
2623 END Ozf_Actmetric_Pvt;