1 PACKAGE BODY AMS_REFRESHMETRIC_PVT AS
2 /* $Header: amsvmrsb.pls 120.5 2006/02/09 17:29:23 rmajumda ship $ */
3 --------------------------------------------------------------------------------
4 --
5 -- NAME
6 -- AMS_refreshMetric_PVT for 11.5.6
7 --
8 -- HISTORY
9 -- 19-Oct-1999 ptendulk@us Created
10 -- 27-FEB-2000 bgeorge Modified to call the trigger API for metrics
11 -- refresh in the copy_seeded_metrics
12 -- 05/08/2000 BGEORGE a. added proc to be called from CC
13 -- b. commented call to create triggers
14 -- c. added call to check object attribute
15 -- 07/17/2000 khung bug 1356700 fix. add check category_id in
16 -- copy_seeded_metric
17 -- 08/17/2000 sveerave Included procedure to associate metrics for the
18 -- dependent objects in the parent object
19 -- 10/11/2000 SVEERAVE Removed Init_ActMetric_Rec from here and placed
20 -- in AMS_ACTMETRICS_PVT
21 -- 02/12/2001 dmvincen BUG# 1603925
22 -- Fixed create_refresh_parent_level to only
23 -- invalidate the parent on refresh. And fixed
24 -- some code hierarchies to match levels for
25 -- easier reading.
26 -- 04/04/2001 dmvincen Call new metrics engine for rollup.
27 -- 04/30/2001 dmvincen Rollup Changes #1753241
28 -- 06/14/2001 huili Set values for "VARIABLE" metrics to 0 since it not
29 -- supported by 11.5.5
30 -- 06/19/2001 huili Comment out the "create_refresh_assoc_metrics"
31 -- call in the "Refresh_Metric" module.
32 -- 06/27/2001 huili Added calculation for forecasted value.
33 -- 06/29/2001 dmvincen Generate Rollups even if no dirty flags set.
34 -- 06/29/2001 dmvincen Rollup and Summarize only using activity metric ids
35 -- 07/02/2001 huili Adjust the "arc_act_metric_used_by" field while
36 -- creating parent activity metrics.
37 -- 08/07/2001 huili Remove rollup from a "Deliverable" to other business object.
38 -- 08/16/2001 dmvincen BUG# 1868868: Only rollup cancel objects if actual
39 -- values have been accrued.
40 -- 08/16/2001 dmvincen Ensure only same currencies are being added up.
41 -- Set the functional currencies to the default.
42 -- 08/31/2001 huili Added the "Exec_Procedure" function.
43 -- 10/10/2001 huili Added fix for checking the function_type.
44 -- 10/24/2001 dmvincen Fixed logic for checking cancelled objects.
45 -- 12/24/2001 dmvincen Uncommented create_refresh_assoc_metric, and fixed
46 -- cursor leaks. (Merged from 11.5.4.07E)
47 -- 15-Jan-2002 huili Added the "p_update_history" to the
48 -- "Refresh_Act_metrics" module.
49 -- 02/26/2002 dmvincen New Feature: Modify the Copy_Seeded_metrics to
50 -- utilize new metric template structures.
51 -- 03/27/2002 dmvincen Added support for dialogs and components to
52 -- Copy_Seeded_Metrics.
53 -- 03/28/2002 dmvincen Dialog refresh must refresh all components.
54 -- 03/28/2002 dmvincen Call procedures for dialog NOT components.
55 -- 06/13/2002 dmvincen BUG2385692: Fixed function metric update.
56 -- 17-Sep-2003 sunkumar Object level locking introduced
57 -- 09-Feb-2004 dmvincen Copy_seeded_metrics includes 'ANY' object type.
58 -- 21-Nov-2005 dmvincen BUG4742384: Copy by fixed then variable.
59 --
60 ------------------------------------------------------------------------------
61 --
62 -- Global variables and constants.
63 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMS_REFRESHMETRIC_PVT'; -- Name of the current package.
64 G_DEBUG_FLAG VARCHAR2(1) := 'N';
65
66 -- Forward Declarations Begin
67 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
68 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
69 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
70
71 -- Forward Declaration for Calculate_Metric as it is removed from specs, SVEERAVE, 10/19/00
72 PROCEDURE Calculate_Metric (
73 p_api_version IN NUMBER,
74 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
75 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
76 x_return_status OUT NOCOPY VARCHAR2,
77 x_msg_count OUT NOCOPY NUMBER,
78 x_msg_data OUT NOCOPY VARCHAR2,
79 p_activity_metric_id IN NUMBER,
80 -- p_act_metric_used_by_id IN NUMBER,
81 -- p_arc_act_metric_used_by IN VARCHAR2,
82 -- p_act_metric_uom_code IN VARCHAR2,
83 x_actual_value OUT NOCOPY NUMBER,
84 x_forecasted_value OUT NOCOPY NUMBER,
85 p_refresh_function IN VARCHAR2 := Fnd_Api.G_TRUE,
86 p_func_currency_code IN VARCHAR2);
87
88 PROCEDURE Exec_Procedure (
89 p_arc_act_metric_used_by IN VARCHAR2,
90 p_act_metric_used_by_id IN NUMBER,
91 p_function_name IN VARCHAR2
92 );
93
94 PROCEDURE Run_Object_Procedures (
95 p_arc_act_metric_used_by IN VARCHAR2,
96 p_act_metric_used_by_id IN NUMBER);
97
98 /**
99 Commenting out dialog related packages since Dialog Objects
100 are obsolete.
101
102 PROCEDURE Refresh_Components(
103 p_api_version IN NUMBER,
104 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_TRUE,
105 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
106 x_return_status OUT NOCOPY VARCHAR2,
107 x_msg_count OUT NOCOPY NUMBER,
108 x_msg_data OUT NOCOPY VARCHAR2,
109 p_arc_act_metric_used_by IN VARCHAR2,
110 p_act_metric_used_by_id IN NUMBER,
111 p_refresh_function IN VARCHAR2 := Fnd_Api.G_TRUE
112 );
113 **/
114 -- NAME
115 -- create_refresh_parent_level
116 --
117 -- PURPOSE
118 -- procedure below checks for the existence of the
119 -- rollup metrics at the parent Marketing entity level
120 -- if this is not existing a new row is created in the
121 -- ams_act_metrics_all and also calls refresh for the
122 -- parent entity..
123 --
124 -- NOTES
125 --
126 -- HISTORY
127 -- 08/14/2000 bgeorge Created.
128 -- 02/12/2001 dmvincen This function now only invalidates the parent.
129 --
130 PROCEDURE create_refresh_parent_level (
131 p_activity_metric_id IN NUMBER,
132 p_metric_id IN NUMBER,
133 p_act_metric_used_by_id IN NUMBER,
134 p_arc_act_metric_used_by IN VARCHAR2,
135 p_dirty IN VARCHAR2
136 -- p_func_actual_value IN NUMBER,
137 -- p_func_forecasted_value IN NUMBER
138 )
139 IS
140
141 --Check if the metric has a rollup parent defined.
142 CURSOR c_check_parent(l_metric_id NUMBER) IS
143 SELECT metric_parent_id
144 FROM ams_metrics_all_b
145 WHERE metric_id = l_metric_id;
146 l_metric_parent_id NUMBER;
147
148 --check for the parent campaign
149 CURSOR c_check_parent_campaign(l_act_metric_used_by_id NUMBER) IS
150 SELECT parent_campaign_id
151 FROM ams_campaigns_all_b
152 WHERE campaign_id = l_act_metric_used_by_id;
153
154 --check for the parent event
155 CURSOR c_check_parent_event(l_act_metric_used_by_id NUMBER) IS
156 SELECT event_header_id
157 FROM ams_event_offers_all_b
158 WHERE event_offer_id = l_act_metric_used_by_id;
159 l_parent_obj NUMBER;
160
161 -- 06/24/2001 huili added
162 --check for the parent event
163 CURSOR c_check_parent_event_o(l_act_metric_used_by_id NUMBER) IS
164 SELECT event_header_id
165 FROM ams_event_offers_all_b
166 WHERE event_offer_id = l_act_metric_used_by_id;
167
168 --check for the parent event
169 CURSOR c_check_parent_event_h(l_act_metric_used_by_id NUMBER) IS
170 SELECT parent_event_header_id
171 FROM ams_event_headers_all_b
172 WHERE event_header_id = l_act_metric_used_by_id;
173
174 -- 06/23/2001 huili changed the "c_check_event_camp"
175 -- 06/07/2001 huili added two cursors
176 --check another type of parent event: execution campaign
177 CURSOR c_check_event_camp (l_act_metric_used_by_id NUMBER) IS
178 SELECT program_id
179 FROM ams_event_headers_all_b
180 WHERE event_header_id = l_act_metric_used_by_id;
181
182 CURSOR c_check_camp_type (l_camp_id NUMBER) IS
183 SELECT rollup_type
184 FROM ams_campaigns_all_b
185 WHERE campaign_id = l_camp_id;
186
187 -- 06/23/2001 huili added
188 --check the parent campaign for schedule
189 CURSOR c_check_parent_schedule (l_act_metric_used_by_id NUMBER) IS
190 SELECT campaign_id
191 FROM ams_campaign_schedules_b
192 WHERE schedule_id = l_act_metric_used_by_id;
193
194 CURSOR c_check_parent_one_event(l_act_metric_used_by_id NUMBER) IS
195 SELECT parent_id
196 FROM ams_event_offers_all_b
197 WHERE event_offer_id = l_act_metric_used_by_id
198 AND parent_type = 'RCAM';
199 -- END
200
201 -- check if act_metrics exists
202 CURSOR c_verify_act_metric(l_metric_parent_id NUMBER,
203 l_obj_id NUMBER,
204 l_obj_code VARCHAR2) IS
205 SELECT activity_metric_id
206 FROM ams_act_metrics_all
207 WHERE metric_id = l_metric_parent_id
208 AND act_metric_used_by_id = l_obj_id
209 AND arc_act_metric_used_by = l_obj_code;
210
211 l_parent_obj_code VARCHAR2(30) := p_arc_act_metric_used_by;
212 l_act_flag NUMBER :=0;
213 l_act_metrics_rec Ams_Actmetric_Pvt.act_metric_rec_type;
214 l_return_status VARCHAR2(1);
215 l_msg_count NUMBER;
216 l_msg_data VARCHAR2(2000);
217 l_act_met_id NUMBER;
218 l_is_locked varchar2(1);
219
220 BEGIN
221
222 SAVEPOINT create_refresh_parent_sp;
223
224 OPEN c_check_parent(p_metric_id);
225 FETCH c_check_parent INTO l_metric_parent_id;
226 CLOSE c_check_parent;
227
228 l_parent_obj := NULL;
229 l_parent_obj_code := NULL;
230
231 -- 06/24/2001 huili changed to add new hierarchy
232 IF l_metric_parent_id IS NOT NULL THEN
233 -- campaign/program to program
234 IF p_arc_act_metric_used_by IN ('CAMP', 'RCAM' ) THEN
235 OPEN c_check_parent_campaign(p_act_metric_used_by_id);
236 FETCH c_check_parent_campaign INTO l_parent_obj;
237 CLOSE c_check_parent_campaign;
238 l_parent_obj_code := 'RCAM';
239 -- schedule to campaign
240 ELSIF p_arc_act_metric_used_by = 'CSCH' THEN
241 OPEN c_check_parent_schedule (p_act_metric_used_by_id);
242 FETCH c_check_parent_schedule INTO l_parent_obj;
243 CLOSE c_check_parent_schedule;
244 l_parent_obj_code := 'CAMP';
245 -- Event Schedule to Event
246 ELSIF p_arc_act_metric_used_by = 'EVEO' THEN
247 OPEN c_check_parent_event_o (p_act_metric_used_by_id);
248 FETCH c_check_parent_event_o INTO l_parent_obj;
249 CLOSE c_check_parent_event_o;
250 l_parent_obj_code := 'EVEH';
251 -- Event to Program
252 ELSIF p_arc_act_metric_used_by = 'EVEH' THEN
253 OPEN c_check_event_camp (p_act_metric_used_by_id);
254 FETCH c_check_event_camp INTO l_parent_obj;
255 CLOSE c_check_event_camp;
256 IF l_parent_obj IS NOT NULL THEN
257 l_parent_obj_code := 'RCAM';
258 END IF;
259 -- One Off Event to Program
260 ELSIF p_arc_act_metric_used_by = 'EONE' THEN
261 OPEN c_check_parent_one_event (p_act_metric_used_by_id);
262 FETCH c_check_parent_one_event INTO l_parent_obj;
263 CLOSE c_check_parent_one_event;
264 l_parent_obj_code := 'RCAM';
265 ELSE
266 RETURN;
267 END IF;
268
269 IF l_parent_obj IS NOT NULL THEN
270 l_is_locked := ams_actmetric_pvt.lock_object(
271 p_api_version => 1 ,
272 p_init_msg_list => Fnd_Api.G_FALSE,
273 p_arc_act_metric_used_by => l_parent_obj_code,
274 p_act_metric_used_by_id => l_parent_obj,
275 x_return_status => l_return_status,
276 x_msg_count => l_msg_count,
277 x_msg_data => l_msg_data);
278
279 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
280 RAISE Fnd_Api.G_EXC_ERROR;
281 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
282 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
283 elsif l_is_locked = FND_API.G_FALSE THEN
284 -- the object needs to be lock by this process.
285 RAISE ams_utility_pvt.resource_locked;
286 END IF;
287
288
289 OPEN c_verify_act_metric(l_metric_parent_id,
290 l_parent_obj, l_parent_obj_code);
291 FETCH c_verify_act_metric INTO l_act_met_id;
292
293 IF c_verify_act_metric%NOTFOUND
294 -- AND (l_parent_obj IS NOT NULL)
295 THEN
296 l_act_metrics_rec.act_metric_used_by_id := l_parent_obj;
297 l_act_metrics_rec.arc_act_metric_used_by := l_parent_obj_code;
298 l_act_metrics_rec.metric_id := l_metric_parent_id;
299 l_act_metrics_rec.func_actual_value := NULL; --p_func_actual_value;
300 l_act_metrics_rec.func_forecasted_value := NULL; --p_func_forecasted_value;
301 l_act_metrics_rec.application_id := 530;
302 Ams_Actmetric_Pvt.Create_ActMetric (
303 p_api_version => 1.0,
304 p_act_metric_rec => l_act_metrics_rec,
305 x_return_status => l_return_status,
306 x_msg_count => l_msg_count,
307 x_msg_data => l_msg_data,
308 x_activity_metric_id => l_act_met_id);
309
310 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
311 RAISE Fnd_Api.G_EXC_ERROR;
312 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
313 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
314 END IF;
315
316 -- DMVINCEN: After selecting the primary key this is not null.
317 -- ELSIF l_act_met_id is NOT NULL
318 -- THEN -- IF c_verify_act_metric%NOTFOUND THEN
319 ELSE
320 IF p_dirty = 'Y' THEN
321 -- 02/12/2001 dmvincen - make the parent metric dirty
322 -- but do not refresh the data.
323 Ams_Actmetric_Pvt.Make_actmetric_dirty(l_act_met_id);
324 END IF;
325
326 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
327 RAISE Fnd_Api.G_EXC_ERROR;
328 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
329 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
330 END IF;
331
332 END IF; -- IF c_verify_act_metric%NOTFOUND THEN
333 CLOSE c_verify_act_metric;
334
335 -- DMVINCEN 04/30/2001: update the rollup to field.
336 UPDATE ams_act_metrics_all
337 SET rollup_to_metric = l_act_met_id
338 WHERE activity_metric_id = p_activity_metric_id;
339
340 END IF; -- parent_obj not null
341 END IF; --IF l_metric_parent_id <> 0 THEN
342
343 exception
344 when others then
345 rollback to create_refresh_parent_sp;
346 raise;
347
348 END create_refresh_parent_level;
349
350 -- NAME
351 -- refresh_assoc_metrics
352 --
353 -- PURPOSE
354 -- The procedure will refresh(create or update) act metrics for the master
355 -- object depending upon association objects.
356 -- If there are any dependent objects for a master object, and these
357 -- dependent objects have any top level metrics, this procedure creates or
358 -- refreshes the association metric for the master object.
359 --
360 -- NOTES
361 -- Tables used: ams_object_associations,ams_metric_accruals,ams_act_metrics_all
362 -- This algorithm is driven on ams_metric_accruals table.
363 -- HISTORY
364 -- 10/04/2000 sveerave Created.
365 -- 08/16/2001 dmvincen Not all cursors were being closed.
366
367 PROCEDURE refresh_assoc_metrics ( p_accr_met_acc_id NUMBER,
368 p_master_obj_id NUMBER,
369 p_master_obj_type VARCHAR2
370 )
371 IS
372
373 -- Get accrual metric details
374 CURSOR c_get_accr_met_details(l_accr_met_acc_id NUMBER) IS
375 SELECT using_object, used_object, metric_id, metric_type_id
376 FROM ams_metric_accruals
377 WHERE ams_metric_acc_id = l_accr_met_acc_id;
378 l_accr_met_rec c_get_accr_met_details%ROWTYPE;
379 l_accr_found VARCHAR2(1) := Fnd_Api.G_FALSE;
380
381 --Get top level metrics details for the child object.
382 CURSOR c_get_child_summary_metrics(l_master_obj_type VARCHAR2,
383 l_master_obj_id NUMBER,
384 l_metric_type_id NUMBER)IS
385 SELECT actmet.activity_metric_id,
386 actmet.metric_id,
387 actmet.func_actual_value,
388 actmet.func_forecasted_value,
389 actmet.functional_currency_code,
390 objassoc.object_association_id
391 FROM ams_act_metrics_all actmet,
392 ams_metrics_all_b met,
393 ams_object_associations objassoc
394 WHERE actmet.metric_id = met.metric_id
395 AND objassoc.master_object_type = l_master_obj_type
396 AND objassoc.master_object_id = l_master_obj_id
397 AND actmet.act_metric_used_by_id = objassoc.using_object_id
398 AND actmet.arc_act_metric_used_by = objassoc.using_object_type
399 AND met.metric_category = l_metric_type_id
400 AND met.summary_metric_id IS NULL;
401
402 l_child_summary_actmet_rec c_get_child_summary_metrics%ROWTYPE;
403 --Check whether there is already an instance is existing for the
404 --accrual metric in the parent object metrics.
405 CURSOR c_get_master_accrual_actmet(l_master_object_type VARCHAR2,
406 l_master_object_id NUMBER,
407 l_metric_id NUMBER) IS
408 SELECT activity_metric_id
409 FROM ams_act_metrics_all
410 WHERE arc_act_metric_used_by = l_master_object_type
411 AND act_metric_used_by_id = l_master_object_id
412 AND metric_id = l_metric_id;
413 l_master_found VARCHAR2(1) := Fnd_Api.G_FALSE;
414 l_sum_forecasted_value NUMBER;
415 l_sum_actual_value NUMBER;
416 l_refresh VARCHAR2(1) := 'N';
417 l_default_currency VARCHAR2(15) := Ams_Actmetric_Pvt.Default_Func_Currency;
418 l_act_metric_id NUMBER;
419 l_act_metric_rec Ams_Actmetric_Pvt.act_metric_rec_type;
420 l_return_status VARCHAR2(1);
421 l_msg_count NUMBER;
422 l_msg_data VARCHAR2(2000);
423 l_current_date DATE := SYSDATE;
424
425 BEGIN
426
427
428 -- get accrual metric details
429 OPEN c_get_accr_met_details(p_accr_met_acc_id);
430 FETCH c_get_accr_met_details INTO l_accr_met_rec;
431 IF c_get_accr_met_details%FOUND THEN
432 l_accr_found := Fnd_Api.G_TRUE;
433 END IF;
434 CLOSE c_get_accr_met_details;
435 IF l_accr_found = Fnd_Api.G_TRUE THEN
436 -- initialize the sum values;
437 l_sum_actual_value := 0;
438 l_sum_forecasted_value := 0;
439 -- get toplevel metric for one or more child objects
440 OPEN c_get_child_summary_metrics(p_master_obj_type, p_master_obj_id,
441 l_accr_met_rec.metric_type_id);
442 LOOP
443 FETCH c_get_child_summary_metrics INTO l_child_summary_actmet_rec;
444 EXIT WHEN c_get_child_summary_metrics%NOTFOUND;
445 l_refresh := 'Y';
446 -- do currency conversions if any
447 IF l_child_summary_actmet_rec.functional_currency_code IS NOT NULL AND
448 l_child_summary_actmet_rec.functional_currency_code <>
449 l_default_currency THEN
450 -- Convert currency into default currency
451 IF NVL(l_child_summary_actmet_rec.func_actual_value,0) <> 0 OR
452 NVL(l_child_summary_actmet_rec.func_forecasted_value,0) <> 0 THEN
453 Ams_Actmetric_Pvt.CONVERT_CURRENCY2 (
454 x_return_status => l_return_status,
455 p_from_currency => l_child_summary_actmet_rec.functional_currency_code,
456 p_to_currency => l_default_currency,
457 p_conv_date => l_current_date,
458 p_from_amount => l_child_summary_actmet_rec.func_actual_value,
459 x_to_amount => l_child_summary_actmet_rec.func_actual_value,
460 p_from_amount2 => l_child_summary_actmet_rec.func_forecasted_value,
461 x_to_amount2 => l_child_summary_actmet_rec.func_forecasted_value,
462 p_round => Fnd_Api.G_FALSE);
463 END IF;
464 END IF; -- IF (l_child_summary_actmet_rec.transaction_currency_code IS NOT NULL) AND
465 l_sum_actual_value := l_sum_actual_value +
466 l_child_summary_actmet_rec.func_actual_value;
467 l_sum_forecasted_value := l_sum_forecasted_value +
468 l_child_summary_actmet_rec.func_forecasted_value;
469 END LOOP;
470 CLOSE c_get_child_summary_metrics;
471
472 IF l_refresh = 'Y' THEN
473 OPEN c_get_master_accrual_actmet( p_master_obj_type, p_master_obj_id,
474 l_accr_met_rec.metric_id);
475 FETCH c_get_master_accrual_actmet INTO l_act_metric_id;
476 IF c_get_master_accrual_actmet%FOUND THEN
477 l_master_found := Fnd_Api.G_TRUE;
478 END IF;
479 CLOSE c_get_master_accrual_actmet;
480 IF l_master_found = Fnd_Api.G_TRUE THEN
481 -- Update the accrual metrics's VALUES FROM child object's summary level metric
482 -- Initialize the record type for Activity Metric update
483 Ams_Actmetric_Pvt.Init_ActMetric_Rec(x_act_metric_rec => l_act_metric_rec );
484 l_act_metric_rec.activity_metric_id := l_act_metric_id;
485 l_act_metric_rec.func_actual_value := l_sum_actual_value;
486 l_act_metric_rec.func_forecasted_value := l_sum_forecasted_value;
487 l_act_metric_rec.trans_actual_value := NULL;
488 l_act_metric_rec.trans_forecasted_value := NULL;
489 IF l_accr_met_rec.metric_type_id IN (901,902) THEN
490 l_act_metric_rec.functional_currency_code := l_default_currency;
491 ELSE
492 l_act_metric_rec.functional_currency_code := NULL;
493 END IF;
494 Ams_Actmetric_Pvt.update_actmetric (
495 p_api_version => 1.0,
496 p_commit => Fnd_Api.G_FALSE,
497 p_validation_level => Fnd_Api.g_valid_level_full,
498 p_act_metric_rec => l_act_metric_rec,
499 x_return_status => l_return_status,
500 x_msg_count => l_msg_count,
501 x_msg_data => l_msg_data);
502
503 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
504 IF(l_msg_count > 0) THEN
505 FOR i IN 1 .. l_msg_count
506 LOOP
507 l_msg_data := Fnd_Msg_Pub.get(i, Fnd_Api.g_false);
508 END LOOP;
509 END IF;
510 RAISE Fnd_Api.G_EXC_ERROR;
511 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
512 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
513 END IF; -- for IF l_return_status = FND_API.G_RET_STS_ERROR
514 ELSE -- for c_get_master_accrual_actmet%FOUND
515
516 --Create the instance of accrual metric in the master object metrics.
517 l_act_metric_rec.act_metric_used_by_id := p_master_obj_id;
518 l_act_metric_rec.arc_act_metric_used_by := p_master_obj_type;
519 l_act_metric_rec.metric_id := l_accr_met_rec.metric_id;
520 l_act_metric_rec.func_actual_value := l_sum_actual_value;
521 l_act_metric_rec.func_forecasted_value := l_sum_forecasted_value;
522 l_act_metric_rec.sensitive_data_flag := 'N';
523 l_act_metric_rec.application_id:=530;
524 l_act_metric_rec.activity_metric_origin_id :=
525 l_child_summary_actmet_rec.object_association_id;
526 IF l_accr_met_rec.metric_type_id IN (901,902) THEN
527 l_act_metric_rec.functional_currency_code := l_default_currency;
528 ELSE
529 l_act_metric_rec.functional_currency_code := NULL;
530 END IF;
531 Ams_Actmetric_Pvt.Create_ActMetric (
532 p_api_version => 1.0,
533 p_act_metric_rec => l_act_metric_rec,
534 x_return_status => l_return_status,
535 x_msg_count => l_msg_count,
536 x_msg_data => l_msg_data,
537 x_activity_metric_id => l_act_metric_id);
538 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
539 IF l_msg_count > 0 THEN
540 FOR i IN 1 .. l_msg_count
541 LOOP
542 l_msg_data := Fnd_Msg_Pub.get(i, Fnd_Api.g_false);
543 END LOOP;
544 END IF;
545 RAISE Fnd_Api.G_EXC_ERROR;
546 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
547 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
548 END IF; -- for IF l_return_status = FND_API.G_RET_STS_ERROR
549 END IF; -- for c_get_master_accrual_actmet%FOUND
550 END IF; -- for IF l_refresh = 'Y'
551 END IF; --for IF c_get_accr_met_details%FOUND
552
553 END refresh_assoc_metrics;
554
555 -- NAME
556 -- create_refresh_assoc_metrics
557 --
558 -- PURPOSE
559 -- The procedure will handle metrics for association objects.
560 -- If there are any dependent objects for a master object, and these
561 -- dependent objects have any top level metrics, this procedure creates
562 -- or refreshes the association metric for the master object.
563 --
564 -- NOTES
565 -- Tables used: ams_object_associations,ams_metric_accruals,ams_metrics_all_b,ams_act_metrics_all
566 -- This procedure is called in Refresh_metrics for each distinct combination
567 -- of parent_object_id, parent_object_type
568 -- This method only creates act metric accruals and calls
569 -- refresh_assoc_metrics for creation/update of activity metrics.
570 -- HISTORY
571 -- 08/15/2000 sveerave Created.
572 -- 10/03/2000 SVEERAVE Modified to refresh correctly if there are multiple
573 -- child objects for master object.
574 -- 10/04/2000 SVEERAVE separated refresh algorithm into refresh_assoc_metrics.
575 PROCEDURE create_refresh_assoc_metrics(
576 p_master_object_type IN VARCHAR2,
577 p_master_object_id IN NUMBER)
578 IS
579 --Get associated child objects for the parent object.
580 CURSOR c_get_child_objects (l_master_object_type VARCHAR2,
581 l_master_object_id NUMBER) IS
582 SELECT object_association_id,
583 using_object_type child_object_type,
584 using_object_id child_object_id
585 FROM ams_object_associations
586 WHERE master_object_type = l_master_object_type
587 AND master_object_id = l_master_object_id;
588
589 l_child_obj_rec c_get_child_objects%ROWTYPE;
590
591 --Get top level metrics details for the child object.
592 CURSOR c_get_child_summary_metrics( l_child_object_type VARCHAR2,
593 l_child_object_id NUMBER)IS
594 SELECT actmet.activity_metric_id child_actmet_id,
595 met.metric_category child_metric_type_id ,
596 actmet.metric_id child_metric_id,
597 met.value_type child_met_value_type
598 FROM ams_act_metrics_all actmet, ams_metrics_all_b met
599 WHERE actmet.arc_act_metric_used_by = l_child_object_type
600 AND actmet.act_metric_used_by_id = l_child_object_id
601 AND met.summary_metric_id IS NULL
602 AND actmet.metric_id = met.metric_id;
603
604 l_child_summary_actmet_rec c_get_child_summary_metrics%ROWTYPE;
605
606 --Get top level metrics details for the parent object.
607 CURSOR c_get_parent_summary_metrics( l_master_object_type VARCHAR2,
608 l_master_object_id NUMBER,
609 l_metric_type_id NUMBER) IS
610 SELECT activity_metric_id parent_summary_actmet_id,
611 met.metric_id parent_summary_met_id
612 FROM ams_act_metrics_all actmet, ams_metrics_all_b met
613 WHERE actmet.metric_id = met.metric_id
614 AND arc_act_metric_used_by = l_master_object_type
615 AND act_metric_used_by_id = l_master_object_id
616 AND metric_category = l_metric_type_id
617 AND metric_calculation_type = 'SUMMARY'
618 AND summarize_to_metric IS NULL;
619
620 l_parent_summary_actmet_rec c_get_parent_summary_metrics%ROWTYPE;
621
622 --Check whether any metric is registered with the master_object type,
623 -- child_object_type, metric_type, and get its metric_id
624 CURSOR c_get_metric_accrual( l_master_object_type VARCHAR2,
625 l_child_object_type VARCHAR2,
626 l_metric_type_id NUMBER) IS
627 SELECT accr.ams_metric_acc_id
628 FROM ams_metric_accruals accr, ams_metrics_all_b met
629 WHERE accr.using_object = l_master_object_type
630 AND accr.used_object = l_child_object_type
631 AND accr.metric_type_id = l_metric_type_id
632 AND accr.metric_id = met.metric_id;
633 -- AND met.enabled_flag = 'Y';
634 l_metric_accr_found VARCHAR2(1) := Fnd_Api.G_FALSE;
635
636 -- Get category name when id is passed
637 CURSOR c_get_category_name(l_category_id NUMBER) IS
638 SELECT category_name
639 FROM ams_categories_vl
640 WHERE category_id = l_category_id;
641
642 CURSOR c_next_metric_accrual_id IS
643 SELECT ams_metric_accruals_s.NEXTVAL
644 FROM dual;
645
646 l_act_metric_rec Ams_Actmetric_Pvt.act_metric_rec_type;
647 l_metric_rec Ams_Metric_Pvt.metric_rec_type;
648 l_accrual_metric_id NUMBER;
649 l_category_name VARCHAR2(120);
650 l_object_type_meaning VARCHAR2(80);
651 l_return_status VARCHAR2(1);
652 l_msg_count NUMBER;
653 l_msg_data VARCHAR2(2000);
654 l_act_metric_id NUMBER;
655 l_flag NUMBER := 0;
656 l_metric_id NUMBER;
657
658 BEGIN
659
660 -- Get all the objects associated with the master object that was sent
661 -- in the call-out
662 OPEN c_get_child_objects(p_master_object_type, p_master_object_id);
663 LOOP
664
665 FETCH c_get_child_objects INTO l_child_obj_rec;
666
667 EXIT WHEN c_get_child_objects%NOTFOUND;
668 -- Get top level metric type for the child object.
669 OPEN c_get_child_summary_metrics(l_child_obj_rec.child_object_type,
670 l_child_obj_rec.child_object_id);
671 LOOP
672
673 FETCH c_get_child_summary_metrics INTO l_child_summary_actmet_rec;
674
675 EXIT WHEN c_get_child_summary_metrics%NOTFOUND;
676 OPEN c_get_metric_accrual(p_master_object_type,
677 l_child_obj_rec.child_object_type,
678 l_child_summary_actmet_rec.child_metric_type_id);
679 FETCH c_get_metric_accrual INTO l_accrual_metric_id;
680
681 IF c_get_metric_accrual%FOUND THEN
682 l_metric_accr_found := Fnd_Api.G_TRUE;
683 END IF;
684 CLOSE c_get_metric_accrual;
685 IF l_metric_accr_found = Fnd_Api.G_FALSE THEN
686 -- Create new accrual metric in metrics table and register it in
687 -- ams_metric_accruals populate accrual metric name as child
688 -- object name + - + metrics type get metric type name from
689 -- the category id
690 OPEN c_get_category_name(
691 l_child_summary_actmet_rec.child_metric_type_id);
692 FETCH c_get_category_name INTO l_category_name;
693 CLOSE c_get_category_name;
694 l_category_name := SUBSTR(l_category_name,1,85);
695 --Get meaning for the child object type
696 l_object_type_meaning :=
697 Ams_Utility_Pvt.get_lookup_meaning('AMS_SYS_ARC_QUALIFIER',
698 l_child_obj_rec.child_object_type);
699 l_object_type_meaning := SUBSTR(l_object_type_meaning,1,30);
700 -- Assign values to the accrual metric that is being created
701 l_metric_rec.metrics_name :=
702 l_object_type_meaning||'-'||l_category_name;
703 l_metric_rec.description :=
704 l_object_type_meaning||'-'||l_category_name;
705 l_metric_rec.arc_metric_used_for_object := p_master_object_type;
706 l_metric_rec.metric_calculation_type := 'ROLLUP';
707 l_metric_rec.metric_category :=
708 l_child_summary_actmet_rec.child_metric_type_id;
709 l_metric_rec.accrual_type := 'FIXED';
710 l_metric_rec.application_id := 530;
711 l_metric_rec.sensitive_data_flag := 'N';
712 l_metric_rec.enabled_flag := 'Y';
713 -- assign value_type as the child object's metric
714 l_metric_rec.value_type :=
715 l_child_summary_actmet_rec.child_met_value_type;
716 --summary_metric_id to be that of the master object's metric_id
717 OPEN c_get_parent_summary_metrics(p_master_object_type,
718 p_master_object_id,
719 l_child_summary_actmet_rec.child_metric_type_id);
720 FETCH c_get_parent_summary_metrics
721 INTO l_parent_summary_actmet_rec;
722 IF c_get_parent_summary_metrics%FOUND THEN
723 l_metric_rec.summary_metric_id :=
724 l_parent_summary_actmet_rec.parent_summary_met_id;
725 ELSE
726 l_metric_rec.summary_metric_id := NULL;
727 END IF;
728 CLOSE c_get_parent_summary_metrics;
729
730 Ams_Metric_Pvt.Create_Metric(
731 p_api_version => 1.0,
732 p_init_msg_list => Fnd_Api.G_TRUE,
733 x_return_status => l_return_status,
734 x_msg_count => l_msg_count,
735 x_msg_data => l_msg_data,
736 p_metric_rec => l_metric_rec,
737 x_metric_id => l_metric_id);
738
739 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
740 IF l_msg_count > 0 THEN
741 FOR i IN 1 .. l_msg_count
742 LOOP
743 l_msg_data := Fnd_Msg_Pub.get(i, Fnd_Api.g_false);
744 END LOOP;
745 END IF;
746 --CLOSE c_get_metric_accrual;
747 CLOSE c_get_child_summary_metrics;
748 CLOSE c_get_child_objects;
749 RAISE Fnd_Api.G_EXC_ERROR;
750 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
751 --CLOSE c_get_metric_accrual;
752 CLOSE c_get_child_summary_metrics;
753 CLOSE c_get_child_objects;
754 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
755 END IF;
756
757 OPEN c_next_metric_accrual_id;
758 FETCH c_next_metric_accrual_id INTO l_accrual_metric_id;
759 CLOSE c_next_metric_accrual_id;
760
761 -- Register the metric in ams_metric_accruals by creating a row.
762 INSERT INTO ams_metric_accruals(
763 AMS_METRIC_ACC_ID,
764 LAST_UPDATE_DATE,
765 LAST_UPDATED_BY,
766 CREATION_DATE,
767 CREATED_BY,
768 LAST_UPDATE_LOGIN,
769 OBJECT_VERSION_NUMBER,
770 USING_OBJECT,
771 USED_OBJECT,
772 METRIC_ID,
773 METRIC_TYPE_ID)
774 VALUES(
775 l_accrual_metric_id,
776 SYSDATE,
777 Fnd_Global.User_ID,
778 SYSDATE,
779 Fnd_Global.User_ID,
780 Fnd_Global.Conc_Login_ID,
781 1,
782 p_master_object_type,
783 l_child_obj_rec.child_object_type,
784 l_metric_id,
785 l_child_summary_actmet_rec.child_metric_type_id);
786
787 END IF; -- for c_get_metric_accrual%NOTFOUND
788 refresh_assoc_metrics(l_accrual_metric_id,
789 p_master_object_id,p_master_object_type);
790 --CLOSE c_get_metric_accrual;
791 END LOOP;
792 -- inside c_get_child_summary_metrics
793 CLOSE c_get_child_summary_metrics;
794 END LOOP; -- inside c_get_child_objects
795 CLOSE c_get_child_objects;
796 END create_refresh_assoc_metrics;
797
798 -- NAME
799 -- GetMetricCatVal
800 --
801 -- PURPOSE
802 -- Return the functional forecasted value, committed value, actual
803 -- value depending on Return type for a given metric.
804 --
805 -- NOTES
806 --
807 -- HISTORY
808 -- 10/13/1999 ptendulk Created.
809 --
810 PROCEDURE GetMetCatVal (
811 x_return_status OUT NOCOPY VARCHAR2,
812 p_arc_act_metric_used_by IN VARCHAR2,
813 p_act_metric_used_by_id IN NUMBER,
814 p_metric_category IN VARCHAR2,
815 p_return_type IN VARCHAR2,
816 x_value OUT NOCOPY NUMBER
817 )
818 IS
819 CURSOR c_sum_metrics(l_metric_id IN NUMBER,
820 l_arc_act_metric_used_by VARCHAR2) IS
821 SELECT metric_id
822 FROM ams_metrics_all_b
823 WHERE arc_metric_used_for_object = l_arc_act_metric_used_by
824 START WITH metric_id = l_metric_id
825 CONNECT BY PRIOR summary_metric_id = metric_id
826 ORDER BY LEVEL DESC ;
827
828 CURSOR c_cat_metrics IS
829 SELECT act.activity_metric_id activity_metric_id,
830 met.metric_id metric_id
831 FROM ams_act_metrics_all act,ams_metrics_vl met
832 WHERE met.metric_id = act.metric_id
833 AND act.arc_act_metric_used_by = p_arc_act_metric_used_by
834 AND act.act_metric_used_by_id = p_act_metric_used_by_id
835 AND met.metric_category = p_metric_category ;
836 CURSOR c_amount(l_met_id IN NUMBER) IS
837 SELECT NVL(func_actual_value,0) func_actual_value,
838 NVL(func_forecasted_value,0) func_forecasted_value,
839 NVL(func_committed_value,0) func_committed_value
840 FROM ams_act_metrics_all
841 WHERE metric_id = l_met_id
842 AND arc_act_metric_used_by = p_arc_act_metric_used_by
843 AND act_metric_used_by_id = p_act_metric_used_by_id ;
844 l_cat_met_rec c_cat_metrics%ROWTYPE ;
845 l_sum_met_rec c_sum_metrics%ROWTYPE ;
846 l_amount_rec c_amount%ROWTYPE ;
847 BEGIN
848 --
849 -- Initialize API return status to success.
850 --
851 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
852 --
853 -- Begin API Body.
854 --
855 -- Take the First Metric ID of all the Metrics attached to this Usage and of
856 -- this class
857 OPEN c_cat_metrics;
858 FETCH c_cat_metrics INTO l_cat_met_rec;
859 CLOSE c_cat_metrics ;
860 -- This Cursor will Find out Summary Metric of all the metrics attached to
861 -- this usage type (for e.g. Total Cost )
862 OPEN c_sum_metrics(l_cat_met_rec.metric_id, p_arc_act_metric_used_by) ;
863 FETCH c_sum_metrics INTO l_sum_met_rec;
864 IF c_sum_metrics%NOTFOUND THEN
865 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
866 RETURN;
867 END IF ;
868 CLOSE c_sum_metrics;
869 -- Following Cursor will Find out the value for this summary metric and
870 -- for this Usage
871 -- ASSUMPTIONS : There will be only one Summary Metric(e.g. Total Cost)
872 -- attached to one Usage(For e.g. Camp C1)
873 OPEN c_amount(l_sum_met_rec.metric_id) ;
874 FETCH c_amount INTO l_amount_rec ;
875 CLOSE c_amount ;
876 --
877 -- Set OUT values.
878 --
879 -- This amount is in Functional Currency Code
880 IF p_return_type = 'ACTUAL' THEN
881 x_value := l_amount_rec.func_actual_value ;
882 ELSIF p_return_type = 'FORECASTED' THEN
883 x_value := l_amount_rec.func_forecasted_value ;
884 ELSIF p_return_type = 'COMMITTED' THEN
885 x_value := l_amount_rec.func_committed_value ;
886 END IF;
887 --
888 -- End API Body.
889 --
890 EXCEPTION
891 WHEN OTHERS THEN
892 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
893 RETURN;
894 END GetMetCatVal;
895 -- NAME
896 -- Get_Met_Apport_Val
897 --
898 -- PURPOSE
899 -- Returns the Value of the Approtioned Metric
900 --
901 -- NOTES
902 --
903 -- HISTORY
904 -- 10/20/1999 ptendulk Created.
905 --
906 PROCEDURE Get_Met_Apport_Val(
907 p_obj_association_id IN NUMBER,
908 x_return_status OUT NOCOPY VARCHAR2,
909 x_apportioned_value OUT NOCOPY NUMBER
910 )
911 IS
912 l_return_status VARCHAR2(1);
913 CURSOR c_obj_det IS
914 SELECT master_object_type,
915 master_object_id,
916 using_object_type,
917 using_object_id ,
918 TO_NUMBER(DECODE(usage_type,'CREATED',100,'USED_BY',
919 NVL(pct_of_cost_to_charge_used_by,0))) pct_of_cost_to_charge_used_by,
920 cost_frozen_flag
921 FROM ams_object_associations
922 WHERE object_association_id = p_obj_association_id ;
923 CURSOR c_amt_met IS
924 SELECT func_actual_value
925 FROM ams_act_metrics_all
926 WHERE activity_metric_origin_id = p_obj_association_id ;
927 l_obj_det_rec c_obj_det%ROWTYPE ;
928 l_amount NUMBER ;
929 l_apport_value NUMBER ;
930 BEGIN
931 --
932 -- Initialize API return status to success.
933 --
934 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
935 --
936 -- Begin API Body
937 --
938 OPEN c_obj_det ;
939 FETCH c_obj_det INTO l_obj_det_rec ;
940 CLOSE c_obj_det ;
941 -- If Cost frozen flag is 'Y' don't apportion Metric
942 IF l_obj_det_rec.cost_frozen_flag = 'N' THEN
943 -- Call Proc to get the Value of the
944 GetMetCatVal (
945 x_return_status => l_return_status,
946 p_arc_act_metric_used_by => l_obj_det_rec.using_object_type,
947 p_act_metric_used_by_id => l_obj_det_rec.using_object_id,
948 p_metric_category => 'COST', --Apportioning only for Cost
949 p_return_type => 'ACTUAL', -- Return the Actual Value
950 x_value => l_amount
951 );
952 IF l_return_status = Fnd_Api.G_RET_STS_SUCCESS THEN
953 l_apport_value :=
954 l_amount * l_obj_det_rec.pct_of_cost_to_charge_used_by/100 ;
955 ELSE
956 x_return_status := l_return_status ;
957 RETURN;
958 END IF;
959 ELSE
960 OPEN c_amt_met;
961 FETCH c_amt_met INTO l_apport_value;
962 CLOSE c_amt_met;
963 END IF;
964 --
965 -- Set Output Variables.
966 --
967 x_apportioned_value := l_apport_value;
968 --
969 -- End API Body.
970 --
971 EXCEPTION
972 WHEN OTHERS THEN
973 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
974 END Get_Met_Apport_Val;
975 --
976 -- NAME
977 -- Convert_Uom
978 --
979 -- PURPOSE
980 -- This Procedure will call the Inventory API to convert Uom
981 -- It will return the calculated quantity (in UOM of to_uom_code )
982 -- All the Calculations to calculate Value of the metrics
983 -- are done in Base Uom defined for that Metric. So the First step before
984 -- calculation starts is to convert the UOM into Base UOM.
985 -- Once the Value is calculated it's converted back to the Original UOM
986 -- Activity Metric table will be updated with this UOM
987 -- NOTES
988 --
989 -- HISTORY
990 -- 09/30/1999 ptendulk Created.
991 --
992 FUNCTION Convert_Uom(
993 p_from_uom_code IN VARCHAR2,
994 p_to_uom_code IN VARCHAR2,
995 p_from_quantity IN NUMBER,
996 p_precision IN NUMBER DEFAULT NULL,
997 p_from_uom_name IN VARCHAR2 DEFAULT NULL,
998 p_to_uom_name IN VARCHAR2 DEFAULT NULL
999 )
1000 RETURN NUMBER
1001 IS
1002 l_to_quantity NUMBER ;
1003 BEGIN
1004 -- Call UOM Conversion API. Pass Item ID as 0 as the UOM is not attached to Item
1005 l_to_quantity := Inv_Convert.Inv_Um_Convert (
1006 item_id => 0 , -- As This is Standard Conversion
1007 PRECISION => p_precision,
1008 from_quantity=> p_from_quantity,
1009 from_unit => p_from_uom_code,
1010 to_unit => p_to_uom_code,
1011 from_name => p_from_uom_name,
1012 to_name => p_to_uom_name ) ;
1013 RETURN l_to_quantity ;
1014 EXCEPTION
1015 WHEN OTHERS THEN
1016 l_to_quantity := -1 ;
1017 RETURN l_to_quantity ;
1018 END Convert_Uom;
1019
1020 --
1021 -- NAME
1022 -- Fetch_Manual
1023 --
1024 -- PURPOSE
1025 -- For the Metric of Type Manual this Function will get
1026 -- value of the Metrices from Activity_metric table and
1027 -- will return the values
1028 --
1029 -- NOTES
1030 --
1031 -- HISTORY
1032 -- 09/29/1999 ptendulk Created
1033 ---08/28/2000 SVEERAVE Modified from function to procedure, and also included
1034 -- OUT parameter of func_forecasted_value
1035 -- 08/16/2001 dmvincen Syncronize functional currency.
1036 PROCEDURE Fetch_Manual (
1037 p_activity_metric_id IN NUMBER,
1038 x_func_actual_value OUT NOCOPY NUMBER,
1039 x_func_forecasted_value OUT NOCOPY NUMBER,
1040 p_func_currency_code IN VARCHAR2
1041 )
1042 IS
1043 CURSOR c_act_metric(l_activity_metric_id NUMBER) IS
1044 SELECT trans_actual_value,
1045 trans_forecasted_value,
1046 transaction_currency_code,
1047 NVL(func_actual_value,0) actual,
1048 NVL(func_forecasted_value, 0) forecasted,
1049 functional_currency_code
1050 FROM ams_act_metrics_all
1051 WHERE activity_metric_id = l_activity_metric_id;
1052 l_trans_actual_value NUMBER;
1053 l_trans_forecasted_value NUMBER;
1054 l_trans_currency_code VARCHAR2(15);
1055 l_func_currency_code VARCHAR2(15);
1056 l_return_status VARCHAR2(1);
1057
1058 BEGIN
1059 OPEN c_act_metric(p_activity_metric_id);
1060 FETCH c_act_metric
1061 INTO l_trans_actual_value, l_trans_forecasted_value, l_trans_currency_code,
1062 x_func_actual_value, x_func_forecasted_value, l_func_currency_code;
1063 CLOSE c_act_metric;
1064 IF l_trans_currency_code IS NOT NULL AND l_func_currency_code IS NOT NULL
1065 AND l_func_currency_code <> p_func_currency_code THEN
1066 Ams_Actmetric_Pvt.CONVERT_CURRENCY2(
1067 x_return_status => l_return_status,
1068 p_from_currency => l_trans_currency_code,
1069 p_to_currency => p_func_currency_code,
1070 -- p_conv_date => SYSDATE,
1071 p_from_amount => l_trans_actual_value,
1072 x_to_amount => x_func_actual_value,
1073 p_from_amount2 => l_trans_forecasted_value,
1074 x_to_amount2 => x_func_forecasted_value,
1075 p_round => Fnd_Api.G_FALSE
1076 );
1077 END IF;
1078
1079 END Fetch_Manual;
1080
1081 --
1082 -- NAME
1083 -- Exec_Function
1084 --
1085 -- PURPOSE
1086 -- Executes the given function using values derived from the
1087 -- activity_metric_id.
1088 -- Return the value derived from the given function.
1089 --
1090 -- NOTES
1091 -- Use Native Dynamic SQL (8i feature) for executing the function.
1092 -- If a currency value is returned it must be in the default currency.
1093 --
1094 -- HISTORY
1095 -- 07/05/1999 choang Created.
1096 --
1097 FUNCTION Exec_Function (
1098 p_activity_metric_id IN NUMBER,
1099 p_function_name IN VARCHAR2
1100 )
1101 RETURN NUMBER
1102 IS
1103 l_return_value NUMBER;
1104 l_sql_str VARCHAR2(4000);
1105 BEGIN
1106 l_sql_str := 'BEGIN :return_value := ' || p_function_name || '( :activity_metric_id ); END;';
1107 EXECUTE IMMEDIATE l_sql_str
1108 USING OUT l_return_value, IN p_activity_metric_id;
1109 RETURN l_return_value;
1110 END Exec_Function;
1111
1112 -------------------------------------------------------------------------------
1113 -- NAME
1114 -- Make_Functions_Dirty
1115 -- PURPOSE
1116 -- For all activity metrics of type FUNCTION, if the function's evaluated
1117 -- value is different from it's original value, update the activity metric
1118 -- to reflect the change and make it dirty, which also makes all activity
1119 -- metrics above it in the hierarchy dirty.
1120 -- HISTORY
1121 -- 13-Oct-2000 choang Created.
1122 -- 08/16/2001 dmvincen Syncronize the functional currency to the default.
1123 -------------------------------------------------------------------------------
1124 /*** OBSOLETE BUG4924982 ***
1125 PROCEDURE Make_Functions_Dirty
1126 IS
1127 l_new_func_value NUMBER;
1128 l_act_metric_rec Ams_Actmetric_Pvt.Act_Metric_Rec_Type;
1129
1130 l_return_status VARCHAR2(1);
1131 l_msg_count NUMBER;
1132 l_msg_data VARCHAR2(4000);
1133 cntr NUMBER := 1;
1134 CURSOR c_functions IS
1135 SELECT /*+ first_rows * / activity_metric_id,
1136 function_name,
1137 func_actual_value,
1138 last_calculated_date,
1139 functional_currency_code
1140 FROM ams_act_metrics_all actmet, ams_metrics_all_b met
1141 WHERE actmet.metric_id = met.metric_id
1142 AND arc_act_metric_used_by IN
1143 ('RCAM', 'CAMP', 'CSCH', 'DELV', 'EVEH', 'EVEO', 'EONE')
1144 --BUG2845365: Remove dialogue components.
1145 --'DILG', 'AMS_COMP_START', 'AMS_COMP_SHOW_WEB_PAGE', 'AMS_COMP_END')
1146 AND metric_calculation_type = 'FUNCTION';
1147
1148 l_function_rec c_functions%ROWTYPE;
1149 l_current_date DATE := SYSDATE;
1150 l_default_currency VARCHAR2(15) := Ams_Actmetric_Pvt.DEFAULT_FUNC_CURRENCY;
1151 BEGIN
1152
1153 OPEN c_functions;
1154 LOOP
1155 FETCH c_functions INTO l_function_rec;
1156 EXIT WHEN c_functions%NOTFOUND;
1157 l_new_func_value := Exec_Function (l_function_rec.activity_metric_id,
1158 l_function_rec.function_name);
1159
1160 IF l_new_func_value <> l_function_rec.func_actual_value THEN
1161 Ams_Actmetric_Pvt.Init_ActMetric_Rec(x_act_metric_rec => l_act_metric_rec );
1162 l_act_metric_rec.activity_metric_id :=
1163 l_function_rec.activity_metric_id;
1164 l_act_metric_rec.func_actual_value := l_new_func_value;
1165 -- BUG2385692: Set transaction values to recalculate.
1166 l_act_metric_rec.trans_actual_value := NULL;
1167 IF l_function_rec.functional_currency_code IS NOT NULL THEN
1168 l_act_metric_rec.functional_currency_code := l_default_currency;
1169 END IF;
1170 l_act_metric_rec.difference_since_last_calc :=
1171 l_new_func_value - l_function_rec.func_actual_value;
1172 l_act_metric_rec.days_since_last_refresh :=
1173 l_current_date - l_function_rec.last_calculated_date;
1174 l_act_metric_rec.last_calculated_date := l_current_date;
1175 l_act_metric_rec.dirty_flag := 'Y';
1176
1177 Ams_Actmetric_Pvt.Update_ActMetric (
1178 p_api_version => 1.0,
1179 p_init_msg_list => Fnd_Api.g_false,
1180 p_commit => Fnd_Api.G_FALSE,
1181 p_validation_level => Fnd_Api.g_valid_level_full,
1182 p_act_metric_rec => l_act_metric_rec,
1183 x_return_status => l_return_status,
1184 x_msg_count => l_msg_count,
1185 x_msg_data => l_msg_data);
1186 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
1187 RAISE Fnd_Api.G_EXC_ERROR;
1188 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
1189 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1190 END IF;
1191 END IF;
1192 END LOOP;
1193 CLOSE c_functions;
1194 END Make_Functions_Dirty;
1195 *** OBSOLETE BUG4924982 ***/
1196
1197 --
1198 -- NAME
1199 -- check_object_status
1200 --
1201 -- PURPOSE
1202 -- Check if the business object has been canceled.
1203 --
1204 -- NOTES
1205 --
1206 -- HISTORY
1207 -- 08/16/2001 dmvincen Created
1208 --
1209 PROCEDURE check_object_status(
1210 p_arc_act_metric_used_by IN VARCHAR2,
1211 p_act_metric_used_by_id IN NUMBER,
1212 x_is_canceled OUT NOCOPY VARCHAR2
1213 )
1214 IS
1215 CURSOR c_camp(id NUMBER) IS
1216 SELECT Fnd_Api.G_TRUE
1217 FROM ams_campaigns_all_b
1218 WHERE campaign_id = id
1219 AND status_code = 'CANCELLED';
1220
1221 CURSOR c_csch(id NUMBER) IS
1222 SELECT Fnd_Api.G_TRUE
1223 FROM ams_campaign_schedules_b
1224 WHERE schedule_id = id
1225 AND status_code = 'CANCELLED';
1226
1227 CURSOR c_delv(id NUMBER) IS
1228 SELECT Fnd_Api.G_TRUE
1229 FROM ams_deliverables_all_b
1230 WHERE deliverable_id = id
1231 AND status_code = 'CANCELLED';
1232
1233 CURSOR c_eveh(id NUMBER) IS
1234 SELECT Fnd_Api.G_TRUE
1235 FROM ams_event_headers_all_b
1236 WHERE event_header_id = id
1237 AND system_status_code = 'CANCELLED';
1238
1239 CURSOR c_eveo(id NUMBER) IS
1240 SELECT Fnd_Api.G_TRUE
1241 FROM ams_event_offers_all_b
1242 WHERE event_offer_id = id
1243 AND system_status_code IN ('CANCELLED');
1244
1245 l_is_canceled VARCHAR2(1) := Fnd_Api.G_FALSE;
1246
1247 BEGIN
1248 IF p_arc_act_metric_used_by IN ('RCAM', 'CAMP') THEN
1249 OPEN c_camp(p_act_metric_used_by_id);
1250 FETCH c_camp INTO l_is_canceled;
1251 CLOSE c_camp;
1252
1253 ELSIF (p_arc_act_metric_used_by = 'CSCH') THEN
1254 OPEN c_csch(p_act_metric_used_by_id);
1255 FETCH c_csch INTO l_is_canceled;
1256 CLOSE c_csch;
1257
1258 ELSIF (p_arc_act_metric_used_by = 'DELV') THEN
1259 OPEN c_delv(p_act_metric_used_by_id);
1260 FETCH c_delv INTO l_is_canceled;
1261 CLOSE c_delv;
1262
1263 ELSIF (p_arc_act_metric_used_by = 'EVEH') THEN
1264 OPEN c_eveh(p_act_metric_used_by_id);
1265 FETCH c_eveh INTO l_is_canceled;
1266 CLOSE c_eveh;
1267
1268 ELSIF p_arc_act_metric_used_by IN ('EONE', 'EVEO') THEN
1269 OPEN c_eveo(p_act_metric_used_by_id);
1270 FETCH c_eveo INTO l_is_canceled;
1271 CLOSE c_eveo;
1272
1273 END IF;
1274
1275 x_is_canceled := l_is_canceled;
1276
1277 END check_object_status;
1278
1279 --
1280 -- Begin of section added by ptendulk- 09/29/1999
1281 --
1282 -- NAME
1283 -- Exec_Rollup
1284 --
1285 -- PURPOSE
1286 -- For the Metric of Type ROLLUP this Procedure will calculate
1287 -- value of the Metrices at lower level and aggregates/averages them
1288 -- to Upper Level
1289 --
1290 -- NOTES
1291 --
1292 -- HISTORY
1293 -- 09/29/1999 ptendulk Created
1294 -- 10/19/2000 SVEERAVE Removed savepoints and percolated exceptions above.
1295 -- Added p_refresh_function parameter.
1296 -- 08/16/2001 dmvincen Make sure the all the currency values are added up
1297 -- as the same currency code.
1298 -- 08/16/2001 dmvincen Check if the child object is canceled. No not rollup
1299 -- values if no actual value has accrued.
1300 PROCEDURE Exec_Rollup (
1301 p_api_version IN NUMBER,
1302 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_False,
1303 p_commit IN VARCHAR2 := Fnd_Api.G_False,
1304 x_return_status OUT NOCOPY VARCHAR2,
1305 x_msg_count OUT NOCOPY NUMBER,
1306 x_msg_data OUT NOCOPY VARCHAR2,
1307 p_act_metric_id IN NUMBER,
1308 p_metric_value_type IN VARCHAR2,
1309 x_actual_value OUT NOCOPY NUMBER,
1310 x_forecasted_value OUT NOCOPY NUMBER,
1311 p_func_currency_code IN VARCHAR2
1312 )
1313 IS
1314 L_API_VERSION CONSTANT NUMBER := 1.0;
1315 L_API_NAME VARCHAR2(30) := 'Exec_Rollup';
1316 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1317 l_function_name VARCHAR2(30);
1318
1319 -- Order by is used to prevent redundant queries of the same object.
1320 CURSOR c_met_child_rollup(l_activity_metric_id NUMBER)
1321 IS
1322 SELECT activity_metric_id,
1323 arc_act_metric_used_by,
1324 act_metric_used_by_id,
1325 --metric_uom_code,
1326 NVL(func_actual_value, 0) func_actual_value,
1327 NVL(func_forecasted_value, 0) func_forecasted_value,
1328 functional_currency_code,
1329 NVL(trans_actual_value, 0) trans_actual_value,
1330 NVL(trans_forecasted_value, 0) trans_forecasted_value,
1331 transaction_currency_code
1332 FROM ams_act_metrics_all
1333 WHERE rollup_to_metric = l_activity_metric_id
1334 ORDER BY arc_act_metric_used_by, act_metric_used_by_id;
1335 l_met_child_rec c_met_child_rollup%ROWTYPE;
1336
1337 -- Variables to store calculated Values
1338 l_actual_value NUMBER ;
1339 l_forecasted_value NUMBER;
1340 l_cnt_chld NUMBER := 0; -- Count No of childs This will be used to calculate average
1341 -- Variables to store final calculated Values (These will be passed Along
1342 -- with Activity Metric to Update Activity Metric API
1343 l_final_actual_value NUMBER := 0 ;
1344 l_final_forecasted_value NUMBER := 0;
1345 l_valid_chld_flg VARCHAR2(1):= Fnd_Api.G_False;
1346 l_arc_act_metric_used_by VARCHAR2(80);
1347 l_act_metric_used_by_id NUMBER;
1348 l_is_canceled VARCHAR2(1);
1349 l_return_status VARCHAR2(1);
1350 BEGIN
1351 --
1352 -- Output debug message.
1353 --
1354 IF AMS_DEBUG_HIGH_ON THEN
1355 Ams_Utility_Pvt.debug_message(l_full_name||': START');
1356 END IF;
1357 --
1358 -- Initialize message list if p_init_msg_list is set to TRUE.
1359 --
1360 IF Fnd_Api.To_Boolean (p_init_msg_list) THEN
1361 Fnd_Msg_Pub.Initialize;
1362 END IF;
1363 -- Standard check for API version compatibility.
1364 --
1365 IF NOT Fnd_Api.Compatible_API_Call (L_API_VERSION,
1366 p_api_version,
1367 L_API_NAME,
1368 G_PKG_NAME)
1369 THEN
1370 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1371 END IF;
1372 --
1373 -- Initialize API return status to success.
1374 --
1375 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1376 --
1377 -- Begin API Body.
1378 --
1379 OPEN c_met_child_rollup(p_act_metric_id);
1380 LOOP
1381 FETCH c_met_child_rollup INTO l_met_child_rec;
1382 EXIT WHEN c_met_child_rollup%NOTFOUND;
1383 -- check for canceled object only if we have not previously checked.
1384 IF l_arc_act_metric_used_by IS NULL OR
1385 (l_arc_act_metric_used_by <> l_met_child_rec.arc_act_metric_used_by OR
1386 l_act_metric_used_by_id <> l_met_child_rec.act_metric_used_by_id)
1387 THEN
1388 l_arc_act_metric_used_by := l_met_child_rec.arc_act_metric_used_by;
1389 l_act_metric_used_by_id := l_met_child_rec.act_metric_used_by_id;
1390 l_is_canceled := Fnd_Api.G_FALSE;
1391 check_object_status( l_arc_act_metric_used_by,
1392 l_act_metric_used_by_id,
1393 l_is_canceled);
1394 END IF;
1395 -- canceled must only rollup if actual costs were accrued.
1396 IF l_is_canceled = Fnd_Api.G_FALSE OR
1397 l_met_child_rec.func_actual_value <> 0
1398 THEN
1399 IF l_met_child_rec.functional_currency_code IS NOT NULL AND
1400 l_met_child_rec.functional_currency_code <> p_func_currency_code
1401 THEN
1402 -- Syncronize the currency code with the default.
1403 Ams_Actmetric_Pvt.CONVERT_CURRENCY2(
1404 x_return_status => l_return_status,
1405 p_from_currency => l_met_child_rec.transaction_currency_code,
1406 p_to_currency => p_func_currency_code,
1407 -- p_conv_date => SYSDATE,
1408 p_from_amount => l_met_child_rec.trans_actual_value,
1409 x_to_amount => l_actual_value,
1410 p_from_amount2 => l_met_child_rec.trans_forecasted_value,
1411 x_to_amount2 => l_forecasted_value,
1412 p_round => Fnd_Api.G_FALSE
1413 );
1414 ELSE
1415 l_actual_value := l_met_child_rec.func_actual_value;
1416 l_forecasted_value := l_met_child_rec.func_forecasted_value;
1417 END IF;
1418 -- Child Exist
1419 -- Count the Number of child entities
1420 l_cnt_chld := l_cnt_chld + 1 ;
1421 l_final_actual_value := l_final_actual_value +
1422 l_actual_value;
1423 l_final_forecasted_value := l_final_forecasted_value +
1424 l_forecasted_value;
1425 END IF;
1426 END LOOP;
1427 CLOSE c_met_child_rollup;
1428 -- Now If the Value type is 'R' (It's Ratio Metric) , Devide the SUM
1429 -- by number of childs , it will give you average
1430 IF p_metric_value_type = 'R' AND l_cnt_chld > 0 THEN
1431 l_final_actual_value := l_final_actual_value / l_cnt_chld;
1432 l_final_forecasted_value := l_final_forecasted_value / l_cnt_chld;
1433 END IF;
1434 x_actual_value := l_final_actual_value ;
1435 x_forecasted_value := l_final_forecasted_value;
1436
1437 --
1438 -- End API Body.
1439 --
1440 --
1441 -- Standard check for commit request.
1442 --
1443 IF Fnd_Api.To_Boolean(p_commit) THEN
1444 COMMIT WORK;
1445 END IF;
1446 --
1447 -- Standard API to get message count, and if 1,
1448 -- set the message data OUT variable.
1449 --
1450 Fnd_Msg_Pub.Count_And_Get (
1451 p_count => x_msg_count,
1452 p_data => x_msg_data,
1453 p_encoded => Fnd_Api.G_FALSE
1454 );
1455 --Ams_Utility_Pvt.debug_message(l_full_name ||': end');
1456 EXCEPTION
1457 WHEN Fnd_Api.G_EXC_ERROR THEN
1458 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1459 Fnd_Msg_Pub.Count_And_Get (
1460 p_count => x_msg_count,
1461 p_data => x_msg_data,
1462 p_encoded => FND_API.G_FALSE
1463 );
1464 RAISE Fnd_Api.G_EXC_ERROR;
1465
1466 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1467 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1468 Fnd_Msg_Pub.Count_And_Get (
1469 p_count => x_msg_count,
1470 p_data => x_msg_data,
1471 p_encoded => FND_API.G_FALSE
1472 );
1473 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR ;
1474 WHEN OTHERS THEN
1475 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1476 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
1477 Fnd_Msg_Pub.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
1478 END IF;
1479 Fnd_Msg_Pub.Count_And_Get (
1480 p_count => x_msg_count,
1481 p_data => x_msg_data,
1482 p_encoded => FND_API.G_FALSE
1483 );
1484 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR ;
1485 END Exec_Rollup;
1486
1487 --
1488 -- Begin of section added by ptendulk- 09/29/1999
1489 --
1490 -- NAME
1491 -- Exec_Summary
1492 --
1493 -- PURPOSE
1494 -- For the Metric of Type SUMMARY this Procedure will calculate
1495 -- value of the Metrices at lower level and aggregates/averages them
1496 -- to Upper Level
1497 --
1498 -- NOTES
1499 --
1500 -- HISTORY
1501 -- 09/29/1999 ptendulk Created
1502 -- 10/19/2000 SVEERAVE Removed savepoints and percolated exceptions above.
1503 -- Added p_refresh_function parameter.
1504 -- 08/16/2001 dmvincen Add up all the child values as the same currency.
1505 PROCEDURE Exec_Summary (
1506 p_api_version IN NUMBER,
1507 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_False,
1508 p_commit IN VARCHAR2 := Fnd_Api.G_False,
1509 x_return_status OUT NOCOPY VARCHAR2,
1510 x_msg_count OUT NOCOPY NUMBER,
1511 x_msg_data OUT NOCOPY VARCHAR2,
1512 p_act_metric_id IN NUMBER,
1513 p_metric_value_type IN VARCHAR2,
1514 x_actual_value OUT NOCOPY NUMBER,
1515 x_forecasted_value OUT NOCOPY NUMBER,
1516 p_refresh_function IN VARCHAR2 := Fnd_Api.G_TRUE,
1517 p_func_currency_code IN VARCHAR2
1518 )
1519 IS
1520 L_API_VERSION CONSTANT NUMBER := 1.0;
1521 L_API_NAME VARCHAR2(30) := 'Exec_Summary';
1522 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1523 l_function_name VARCHAR2(30);
1524
1525 -- This Cursor will Select the Details for the Activity Metric ID
1526 -- For the Child Summary Metric The Only Difference from the cursor above
1527 -- is It checks the children are attached to the same Usage level as parent
1528 CURSOR c_met_child_sum(l_activity_metric_id NUMBER)
1529 IS
1530 SELECT activity_metric_id,
1531 arc_act_metric_used_by,
1532 act_metric_used_by_id,
1533 metric_uom_code
1534 FROM ams_act_metrics_all
1535 WHERE summarize_to_metric = l_activity_metric_id;
1536 l_met_child_sum_rec c_met_child_sum%ROWTYPE;
1537
1538 -- Variables to store calculated Values
1539 l_actual_value NUMBER ;
1540 l_forecasted_value NUMBER;
1541 l_cnt_chld NUMBER := 0; -- Count No of childs This will be used to calculate average
1542 -- Variables to store final calculated Values (These will be passed Along
1543 -- with Activity Metric to Update Activity Metric API
1544 l_final_actual_value NUMBER := 0 ;
1545 l_final_forecasted_value NUMBER := 0;
1546 l_valid_chld_flg VARCHAR2(1):= Fnd_Api.G_False ;
1547 BEGIN
1548 --
1549 -- Output debug message.
1550 --
1551 IF AMS_DEBUG_HIGH_ON THEN
1552 Ams_Utility_Pvt.debug_message(l_full_name||': start');
1553 END IF;
1554 --
1555 -- Initialize message list if p_init_msg_list is set to TRUE.
1556 --
1557 IF Fnd_Api.To_Boolean (p_init_msg_list) THEN
1558 Fnd_Msg_Pub.Initialize;
1559 END IF;
1560 -- Standard check for API version compatibility.
1561 --
1562 IF NOT Fnd_Api.Compatible_API_Call (L_API_VERSION,
1563 p_api_version,
1564 L_API_NAME,
1565 G_PKG_NAME)
1566 THEN
1567 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1568 END IF;
1569 --
1570 -- Initialize API return status to success.
1571 --
1572 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1573 --
1574 -- Begin API Body.
1575 --
1576 OPEN c_met_child_sum(p_act_metric_id);
1577 LOOP
1578 FETCH c_met_child_sum INTO l_met_child_sum_rec;
1579 EXIT WHEN c_met_child_sum%NOTFOUND;
1580 l_cnt_chld := l_cnt_chld + 1 ;
1581 -- Give Recursive call to Calculate Metric
1582 -- to find out the value of the Child Metric
1583 Calculate_Metric (
1584 p_api_version => l_api_version,
1585 p_init_msg_list => p_init_msg_list,
1586 p_commit => p_commit ,
1587 x_return_status => x_return_status,
1588 x_msg_count => x_msg_count,
1589 x_msg_data => x_msg_data,
1590 p_activity_metric_id =>
1591 l_met_child_sum_rec.activity_metric_id,
1592 x_actual_value => l_actual_value,
1593 x_forecasted_value => l_forecasted_value,
1594 p_refresh_function => p_refresh_function,
1595 p_func_currency_code => p_func_currency_code
1596 );
1597 -- If any errors happen abort API.
1598 IF x_return_status = Fnd_Api.G_RET_STS_ERROR THEN
1599 CLOSE c_met_child_sum ;
1600 RAISE Fnd_Api.G_EXC_ERROR;
1601 ELSIF x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
1602 CLOSE c_met_child_sum ;
1603 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1604 END IF;
1605
1606 l_final_actual_value :=
1607 l_final_actual_value + NVL(l_actual_value,0);
1608 l_final_forecasted_value :=
1609 l_final_forecasted_value + NVL(l_forecasted_value,0);
1610
1611 END LOOP;
1612 CLOSE c_met_child_sum;
1613 -- Now If the Value type is 'R' (It's Ratio Metric) , Devide the Sum
1614 -- by number of childs , it will give you average
1615 IF p_metric_value_type = 'R' AND l_cnt_chld > 0 THEN
1616 l_final_actual_value := l_final_actual_value / l_cnt_chld;
1617 l_final_forecasted_value := l_final_forecasted_value / l_cnt_chld;
1618 END IF;
1619 x_actual_value := l_final_actual_value ;
1620 x_forecasted_value := l_final_forecasted_value;
1621
1622 --
1623 -- End API Body.
1624 --
1625 --
1626 -- Standard check for commit request.
1627 --
1628 IF Fnd_Api.To_Boolean(p_commit) THEN
1629 COMMIT WORK;
1630 END IF;
1631 --
1632 -- Standard API to get message count, and if 1,
1633 -- set the message data OUT variable.
1634 --
1635 Fnd_Msg_Pub.Count_And_Get (
1636 p_count => x_msg_count,
1637 p_data => x_msg_data,
1638 p_encoded => Fnd_Api.G_FALSE
1639 );
1640 IF AMS_DEBUG_HIGH_ON THEN
1641 Ams_Utility_Pvt.debug_message(l_full_name ||': end');
1642 END IF;
1643 EXCEPTION
1644 WHEN Fnd_Api.G_EXC_ERROR THEN
1645 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1646 Fnd_Msg_Pub.Count_And_Get (
1647 p_count => x_msg_count,
1648 p_data => x_msg_data,
1649 p_encoded => FND_API.G_FALSE
1650 );
1651 RAISE Fnd_Api.G_EXC_ERROR;
1652
1653 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1654 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1655 Fnd_Msg_Pub.Count_And_Get (
1656 p_count => x_msg_count,
1657 p_data => x_msg_data,
1658 p_encoded => FND_API.G_FALSE
1659 );
1660 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR ;
1661 WHEN OTHERS THEN
1662 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1663 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
1664 Fnd_Msg_Pub.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
1665 END IF;
1666 Fnd_Msg_Pub.Count_And_Get (
1667 p_count => x_msg_count,
1668 p_data => x_msg_data,
1669 p_encoded => FND_API.G_FALSE
1670 );
1671 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR ;
1672 END Exec_Summary;
1673
1674 --
1675 -- Begin of section added by ptendulk - 09/28/1999
1676 --
1677 -- NAME
1678 -- Calculate_Metric
1679 --
1680 -- PURPOSE
1681 -- This Procedure checks the Calculation type of the Metrics
1682 -- in question and calls appropriate function
1683 -- (For e.g Function Fetch_Manual is called for Manual type of Matrics)
1684 -- This Process calls the Update Activity metric API at Last stage
1685 -- which updates the Activity Metric table with the calculated values
1686 -- as part of Metric Refresh. This one also includes the Calls to
1687 -- the appropreate apis to Update Usages with refreshed metric values.
1688 -- For e.g Update_campaign for updating Campaign Cost
1689 --
1690 -- NOTES
1691 --
1692 --
1693 -- HISTORY
1694 -- 09/28/1999 ptendulk Created
1695 -- 08/28/2000 SVEERAVE Modified to include forecasted value in the rollup.
1696 -- 10/19/2000 SVEERAVE Removed savepoints and percolated exceptions above, and removed it from specs.
1697 -- Added p_refresh_function parameter.
1698 -- 08/16/2001 dmvincen Add up the currency as the same currency code.
1699 -- 08/16/2001 dmvincen Syncronize the functional currencies to default.
1700 -- 08/16/2001 dmvincen Removed redundant parameters.
1701 -- 08/29/2001 huili Added function_type checking for function metrics.
1702
1703
1704 PROCEDURE Calculate_Metric (
1705 p_api_version IN NUMBER,
1706 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
1707 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
1708 x_return_status OUT NOCOPY VARCHAR2,
1709 x_msg_count OUT NOCOPY NUMBER,
1710 x_msg_data OUT NOCOPY VARCHAR2,
1711 -- p_metric_id IN NUMBER,
1712 p_activity_metric_id IN NUMBER,
1713 -- p_act_metric_used_by_id IN NUMBER,
1714 -- p_arc_act_metric_used_by IN VARCHAR2,
1715 -- p_act_metric_uom_code IN VARCHAR2,
1716 x_actual_value OUT NOCOPY NUMBER,
1717 x_forecasted_value OUT NOCOPY NUMBER,
1718 p_refresh_function IN VARCHAR2 := Fnd_Api.G_TRUE,
1719 p_func_currency_code IN VARCHAR2)
1720 IS
1721 L_API_VERSION CONSTANT NUMBER := 1.0;
1722 L_API_NAME VARCHAR2(30) := 'Calculate_Metric';
1723 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1724 l_function_name VARCHAR2(30);
1725 -------------------------------------------------------------
1726 -- Define Record type , this will be passed to activity
1727 -- metric API for Updation
1728 -------------------------------------------------------------
1729 l_act_metrics_rec Ams_Actmetric_Pvt.act_metric_rec_type;
1730 CURSOR c_met_cal_type(l_metric_id NUMBER) IS
1731 SELECT value_type, -- Ratio / Numeric
1732 metric_calculation_type, -- Manual/Function/...
1733 accrual_type, -- Fixed/Variable
1734 function_name , -- will be used only if it is Function Metric
1735 compute_using_function,--Will be used only if it is Variable Metric
1736 default_uom_code, -- Will be Used for UOM Conversion
1737 function_type
1738
1739 --FROM ams_metrics_vl
1740 FROM ams_metrics_all_b
1741 WHERE metric_id = l_metric_id;
1742 l_cal_type_rec c_met_cal_type%ROWTYPE;
1743 l_metric_found VARCHAR2(1) := Fnd_Api.G_FALSE;
1744 -------------------------------------------
1745 -- Variables to store calculated Values
1746 -------------------------------------------
1747 l_actual_value NUMBER ;
1748 l_forecasted_value NUMBER;
1749 l_func_currency_code VARCHAR2(15);
1750 --------------------------------------------------------------------
1751 -- Count No of childs This will be used to calculate average
1752 --------------------------------------------------------------------
1753 l_cnt_chld NUMBER := 0;
1754 ----------------------------------------------------------------------------
1755 -- Variables to store final calculated Values (These will be passed Along
1756 -- with Activity Metric ID to Update Activity Metric API
1757 ----------------------------------------------------------------------------
1758 --functional actual, and forecasted values in metric's DEFAULT uom
1759 l_final_actual_value NUMBER := 0 ;
1760 l_final_forecasted_value NUMBER := 0 ;
1761 --functional actual, and forecasted values in act metric's uom
1762 l_conv_uom_forecasted_value NUMBER := 0;
1763 l_conv_uom_actual_value NUMBER := 0 ;
1764 --transaction actual, and forecasted currency values in act metric's uom
1765 l_conv_curr_value NUMBER := 0 ;
1766 l_trans_curr_forecasted_value NUMBER := 0 ;
1767 l_compute_using_function_value NUMBER ;
1768
1769 -------------------------------------------------------
1770 -- Cursor to select Origin ID of the Activity Metric
1771 -- It will be used only in case of Apportioned Metric.
1772 -------------------------------------------------------
1773 CURSOR c_origin(l_activity_metric_id NUMBER) IS
1774 SELECT activity_metric_origin_id,
1775 NVL(last_calculated_date,SYSDATE) last_calculated_date,
1776 NVL(func_actual_value,0) func_actual_value,
1777 NVL(func_forecasted_value,0) func_forecasted_value,
1778 metric_id, variable_value, depend_act_metric, dirty_flag,
1779 act_metric_used_by_id, arc_act_metric_used_by,
1780 metric_uom_code,
1781 transaction_currency_code,
1782 functional_currency_code,
1783 NVL(trans_actual_value,0) trans_actual_value,
1784 NVL(trans_forecasted_value,0) trans_forecasted_value
1785 FROM ams_act_metrics_all
1786 WHERE activity_metric_id = l_activity_metric_id
1787 for update of trans_actual_value, trans_forecasted_value,
1788 func_actual_value, func_forecasted_value,
1789 functional_currency_code, computed_using_function_value,
1790 difference_since_last_calc, days_since_last_refresh,
1791 last_calculated_date, dirty_flag,
1792 last_updated_by, last_update_login,
1793 object_version_number
1794 nowait ;
1795 l_origin_rec c_origin%ROWTYPE ;
1796 -------------------------------------------------------
1797 -- Cursor to identify that it is an association activity metric.
1798 -------------------------------------------------------
1799 CURSOR c_check_assoc_metric(l_metric_id NUMBER) IS
1800 SELECT 1
1801 FROM ams_metric_accruals
1802 WHERE metric_id = l_metric_id;
1803 l_flag NUMBER;
1804 l_current_date DATE := SYSDATE;
1805
1806 -- huili on 06/22/2001 changed the calculation method for variable metrics
1807 CURSOR c_var_met_info (l_act_metric_id NUMBER) IS
1808 SELECT func_actual_value, func_forecasted_value
1809 FROM ams_act_metrics_all
1810 WHERE activity_metric_id = l_act_metric_id;
1811
1812 l_return_status VARCHAR2(1);
1813
1814 BEGIN
1815 IF AMS_DEBUG_HIGH_ON THEN
1816 Ams_Utility_Pvt.debug_message('Now Ref/Calculating act met id: '||p_activity_metric_id);
1817 END IF;
1818
1819 --
1820 -- Initialize savepoint.
1821 --
1822 -- SAVEPOINT CALCULATE_METRIC_SAVEPOINT;
1823 --
1824 -- Output debug message.
1825 --
1826 IF AMS_DEBUG_HIGH_ON THEN
1827 Ams_Utility_Pvt.debug_message(l_full_name||': start');
1828 END IF;
1829 --
1830 -- Initialize message list if p_init_msg_list is set to TRUE.
1831 --
1832 IF Fnd_Api.To_Boolean (p_init_msg_list) THEN
1833 Fnd_Msg_Pub.Initialize;
1834 END IF;
1835 --
1836 -- Standard check for API version compatibility.
1837 --
1838 IF NOT Fnd_Api.Compatible_API_Call (L_API_VERSION,
1839 p_api_version,
1840 L_API_NAME,
1841 G_PKG_NAME)
1842 THEN
1843 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1844 END IF;
1845 --
1846 -- Initialize API return status to success.
1847 --
1848 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1849 --
1850 -- Begin API Body.
1851 --
1852 -- Store the Original values of the Metric
1853 OPEN c_origin(p_activity_metric_id);
1854 FETCH c_origin INTO l_origin_rec ;
1855 CLOSE c_origin ;
1856 -------------------------------------------------------------
1857 -- procedure below checks for the existence of the
1858 -- rollup metrics at the parent Marketing entity level
1859 -- if this is not existing a new row is created in the
1860 -- ams_act_metrics_all and also calls refresh for the
1861 -- parent entity.
1862 -------------------------------------------------------------
1863
1864 create_refresh_parent_level(
1865 p_activity_metric_id => p_activity_metric_id,
1866 p_metric_id => l_origin_rec.metric_id,
1867 p_act_metric_used_by_id => l_origin_rec.act_metric_used_by_id,
1868 p_arc_act_metric_used_by => l_origin_rec.arc_act_metric_used_by,
1869 p_dirty => l_origin_rec.dirty_flag
1870 );
1871
1872 OPEN c_met_cal_type(l_origin_rec.metric_id);
1873 FETCH c_met_cal_type INTO l_cal_type_rec;
1874 IF c_met_cal_type%FOUND THEN
1875 l_metric_found := Fnd_Api.G_TRUE;
1876 END IF;
1877 CLOSE c_met_cal_type;
1878
1879 ------------------------------------------
1880 -- If details exist in ams_metrics_all_b
1881 ------------------------------------------
1882 IF l_metric_found = Fnd_Api.G_TRUE THEN
1883 -----------------------------------------------------
1884 -- If this metrics is of FIXED calculation type
1885 -----------------------------------------------------
1886 IF l_cal_type_rec.accrual_type = 'FIXED' THEN
1887 ----------------------------------------------------
1888 -- for FIXED metrics, if this of MANUAL entry type
1889 -- then just grab the values and update
1890 -----------------------------------------------------
1891 IF l_cal_type_rec.metric_calculation_type = 'MANUAL' THEN
1892 Fetch_Manual( p_activity_metric_id => p_activity_metric_id,
1893 x_func_actual_value => l_actual_value,
1894 x_func_forecasted_value => l_forecasted_value,
1895 p_func_currency_code => p_func_currency_code);
1896 IF (l_cal_type_rec.default_uom_code IS NOT NULL) AND
1897 (l_origin_rec.metric_uom_code IS NOT NULL) THEN
1898 --------------------------------------------------------------
1899 -- Convert the Actual value calculated into Base UOM amount
1900 -- While Updating Activity Metric Table with refreshed metric
1901 -- value, This will be converted back to UOM of this Activity
1902 -- Metric
1903 -----------------------------------------------------------------
1904 l_final_actual_value := Convert_Uom(
1905 p_from_uom_code => l_origin_rec.metric_uom_code,
1906 p_to_uom_code => l_cal_type_rec.default_uom_code,
1907 p_from_quantity => l_actual_value);
1908 l_final_forecasted_value := Convert_Uom(
1909 p_from_uom_code => l_origin_rec.metric_uom_code,
1910 p_to_uom_code => l_cal_type_rec.default_uom_code,
1911 p_from_quantity => l_forecasted_value);
1912 IF (l_final_actual_value < 0) OR (l_final_forecasted_value < 0)
1913 THEN
1914 l_final_actual_value := 0 ;
1915 l_final_forecasted_value := 0 ;
1916 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1917 END IF; --check final value
1918 ELSE
1919 l_final_actual_value := l_actual_value;
1920 l_final_forecasted_value := l_forecasted_value;
1921 END IF; --if there is UOM
1922 ----------------------------------------------------
1923 -- for FIXED metrics, if this of FUNCTION type
1924 -- then execute the function and update
1925 -- no need of forecasted values for FUNCTION types as it is
1926 -- basically of MANUAL or ROLLUP
1927 -----------------------------------------------------
1928 ELSIF l_cal_type_rec.metric_calculation_type = 'FUNCTION' THEN
1929 --AND l_cal_type_rec.function_type = 'Y' THEN
1930 -- Calculate the value only when refresh_function is TRUE,
1931 -- SVEERAVE, 10/16/00
1932 IF p_refresh_function = Fnd_Api.G_TRUE
1933 AND l_cal_type_rec.function_type = 'Y' THEN
1934 l_actual_value := Exec_Function(
1935 p_activity_metric_id => p_activity_metric_id,
1936 p_function_name => l_cal_type_rec.function_name);
1937 -- refresh the forecasted value as function execution might
1938 -- change forecasted values. -- 12/14/00 SVEERAVE
1939 -- OPEN c_origin(p_activity_metric_id);
1940 -- FETCH c_origin INTO l_origin_rec;
1941 -- CLOSE c_origin;
1942 l_forecasted_value := l_origin_rec.func_forecasted_value;
1943 ELSE
1944 l_actual_value := l_origin_rec.func_actual_value;
1945 l_forecasted_value := l_origin_rec.func_forecasted_value;
1946 END IF;
1947
1948 IF (l_cal_type_rec.default_uom_code IS NOT NULL) AND
1949 (l_origin_rec.metric_uom_code IS NOT NULL) THEN
1950 --------------------------------------------------------------------
1951 -- Convert the Actual value calculated into Base UOM amount
1952 -- While Updating Activity Metric Table with refreshed metric value,
1953 -- This will be converted back to UOM of this Activity Metric
1954 --------------------------------------------------------------------
1955 l_final_actual_value := Convert_Uom(
1956 p_from_uom_code => l_origin_rec.metric_uom_code,
1957 p_to_uom_code => l_cal_type_rec.default_uom_code,
1958 p_from_quantity => NVL(l_actual_value,0));
1959 l_final_forecasted_value := Convert_Uom(
1960 p_from_uom_code => l_origin_rec.metric_uom_code,
1961 p_to_uom_code => l_cal_type_rec.default_uom_code,
1962 p_from_quantity => NVL(l_forecasted_value,0));
1963 IF l_final_actual_value < 0 THEN
1964 l_final_actual_value := 0 ;
1965 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1966 END IF; --check final value
1967 ELSE
1968 IF l_origin_rec.transaction_currency_code IS NOT NULL AND
1969 l_origin_rec.functional_currency_code IS NOT NULL AND
1970 l_origin_rec.functional_currency_code <> p_func_currency_code
1971 THEN
1972 l_func_currency_code := p_func_currency_code;
1973 Ams_Actmetric_Pvt.CONVERT_CURRENCY2(
1974 x_return_status => l_return_status,
1975 p_from_currency => l_origin_rec.transaction_currency_code,
1976 p_to_currency => l_func_currency_code,
1977 -- p_conv_date => SYSDATE,
1978 p_from_amount => l_origin_rec.trans_actual_value,
1979 x_to_amount => l_actual_value,
1980 p_from_amount2 => l_origin_rec.trans_forecasted_value,
1981 x_to_amount2 => l_forecasted_value,
1982 p_round => Fnd_Api.G_FALSE
1983 );
1984 ELSE
1985 l_func_currency_code := l_origin_rec.functional_currency_code;
1986 END IF;
1987 l_final_actual_value := l_actual_value;
1988 l_final_forecasted_value := l_forecasted_value;
1989 END IF; --if there is UOM
1990 ----------------------------------------------------
1991 -- for FIXED metrics, if this of ROLLUP type
1992 -- then calculate metrics from lower level and update
1993 -----------------------------------------------------
1994 ELSIF l_cal_type_rec.metric_calculation_type IN ('ROLLUP', 'SUMMARY')
1995 THEN
1996 ------------------------------------------------------
1997 -- First Check if it is Apportioned Metric
1998 ------------------------------------------------------
1999 IF l_origin_rec.activity_metric_origin_id IS NOT NULL THEN
2000
2001 -------------------------------------------
2002 -- Check whether it is association activity metric
2003 -------------------------------------------
2004 OPEN c_check_assoc_metric(l_origin_rec.metric_id);
2005 FETCH c_check_assoc_metric INTO l_flag;
2006 IF c_check_assoc_metric%FOUND THEN
2007 -- do nothing in case of association activity metric.
2008 l_final_actual_value := l_origin_rec.func_actual_value;
2009 l_final_forecasted_value :=l_origin_rec.func_forecasted_value;
2010
2011 ELSE
2012 -------------------------------------------
2013 -- Get the Value of the Apportioned Metric
2014 -- no need to compute forecasted values in case of
2015 -- apportioned metrics
2016 -------------------------------------------
2017 Get_Met_Apport_Val(
2018 p_obj_association_id =>
2019 l_origin_rec.activity_metric_origin_id,
2020 x_return_status => x_return_status,
2021 x_apportioned_value => l_actual_value);
2022 IF (l_cal_type_rec.default_uom_code IS NOT NULL) AND
2023 (l_origin_rec.metric_uom_code IS NOT NULL) THEN
2024 ----------------------------------------------------------
2025 -- Convert the Actual value calculated into Base UOM amount
2026 -- While Updating Activity Metric Table with refreshed
2027 -- metric value, this will be converted back to UOM of
2028 -- this Activity Metric
2029 ----------------------------------------------------------
2030 l_final_actual_value := Convert_Uom(
2031 p_from_uom_code => l_origin_rec.metric_uom_code,
2032 p_to_uom_code => l_cal_type_rec.default_uom_code,
2033 p_from_quantity => l_actual_value);
2034 IF l_final_actual_value < 0 THEN
2035 l_final_actual_value := 0 ;
2036 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2037 END IF; --check final value
2038 ELSE
2039 l_final_actual_value := l_actual_value;
2040 END IF; --if there is UOM
2041 END IF; --IF c_check_assoc_metric%FOUND
2042 CLOSE c_check_assoc_metric;
2043 ------------------------------------------------------
2044 -- If this is not apportioned metrics
2045 ------------------------------------------------------
2046 ELSE
2047 ---------------------------------------------------
2048 -- Check if this a Rollup Metrics Or Summary Metric
2049 ---------------------------------------------------
2050 IF l_cal_type_rec.metric_calculation_type = 'ROLLUP' THEN
2051 -----------------------------------
2052 -- This is for vertical Rollup Metric
2053 ----------------------------------
2054 ----------------------------------------------------------
2055 -- Call Exec Rollup to go to Lower Level and Calculate
2056 -- Metric Value. This Proc is called in the Loop so will
2057 -- be executed for each child of the Rollup Metric
2058 -- The Parameter p_metric_type will tell this Proc whether
2059 -- the metric is Rollup or Summary The Only difference
2060 -- between calculation of Summary and Rollup Metric is
2061 -- For Rollup Metric we have to validate the Child entity
2062 ----------------------------------------------------------
2063 Exec_Rollup(
2064 p_api_version => l_api_version,
2065 p_init_msg_list => p_init_msg_list,
2066 p_commit => p_commit,
2067 x_return_status => x_return_status,
2068 x_msg_count => x_msg_count,
2069 x_msg_data => x_msg_data,
2070 p_act_metric_id => p_activity_metric_id,
2071 x_actual_value => l_actual_value,
2072 x_forecasted_value => l_forecasted_value,
2073 p_metric_value_type => l_cal_type_rec.value_type,
2074 p_func_currency_code => p_func_currency_code);
2075 ------------------------------------
2076 -- If any errors happen abort API.
2077 ------------------------------------
2078 IF x_return_status = Fnd_Api.G_RET_STS_ERROR THEN
2079 RAISE Fnd_Api.G_EXC_ERROR;
2080 ELSIF x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
2081 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2082 END IF;
2083 ---------------------------------------
2084 -- Sum-up the values returned for the
2085 -- child metrics
2086 ---------------------------------------
2087 l_final_actual_value := NVL(l_actual_value,0) ;
2088 l_final_forecasted_value:= NVL(l_forecasted_value,0);
2089 --------------------------------------------------
2090 -- Now If the Value type is 'R' (It's Ratio Metric,
2091 -- Divide the Sum by number of childs , it will
2092 -- give you average
2093 --------------------------------------------------
2094 -- IF l_cal_type_rec.value_type = 'R' THEN
2095 -- l_final_actual_value :=
2096 -- l_final_actual_value / l_cnt_chld;
2097 -- l_final_forecasted_value:=
2098 -- l_final_forecasted_value / l_cnt_chld;
2099 -- END IF;
2100 -------------------------------------------------------
2101 -- if the count of rollup metrics is less than or
2102 -- equal to 0, this may a be a summarization
2103 -------------------------------------------------------
2104 ELSE
2105 ------------------------------------------
2106 -- check for horizontal summary metrics
2107 ------------------------------------------
2108 IF l_cal_type_rec.metric_calculation_type = 'SUMMARY' THEN
2109 ----------------------------------
2110 -- It is hori Summary Metric
2111 ----------------------------------
2112 ----------------------------------------------------------
2113 -- Call Exec Rollup to go to Lower Level and Calculate
2114 -- Metric Value. This Proc is called in the Loop so will
2115 -- be executed for each child of the Rollup Metric
2116 -- The Parameter p_metric_type will tell this Proc whether
2117 -- the metric is Rollup or Summary The Only difference
2118 -- between calculation of Summary and Rollup Metric is
2119 -- For Rollup Metric we have to validate the Child entity
2120 ----------------------------------------------------------
2121 Exec_Summary(p_api_version => l_api_version,
2122 p_init_msg_list => p_init_msg_list,
2123 p_commit => p_commit ,
2124 x_return_status => x_return_status,
2125 x_msg_count => x_msg_count,
2126 x_msg_data => x_msg_data,
2127 p_act_metric_id => p_activity_metric_id,
2128 x_actual_value => l_actual_value ,
2129 x_forecasted_value=> l_forecasted_value ,
2130 p_metric_value_type=> l_cal_type_rec.value_type,
2131 p_refresh_function => p_refresh_function,
2132 p_func_currency_code => p_func_currency_code);
2133 ----------------------------------
2134 -- If any errors happen abort API.
2135 ----------------------------------
2136 IF x_return_status = Fnd_Api.G_RET_STS_ERROR THEN
2137 RAISE Fnd_Api.G_EXC_ERROR;
2138 ELSIF x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
2139 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2140 END IF;
2141 ---------------------------------------
2142 -- Sum-up the values returned for the
2143 -- child metrics
2144 ---------------------------------------
2145 l_final_actual_value := NVL(l_actual_value,0);
2146 l_final_forecasted_value := NVL(l_forecasted_value,0);
2147 --------------------------------------------------
2148 -- Now If the Value type is 'R' (It's Ratio Metric,
2149 -- Divide the Sum by number of childs , it will
2150 -- give you average
2151 --------------------------------------------------
2152 -- IF l_cal_type_rec.value_type = 'R' THEN
2153 -- l_final_actual_value := l_final_actual_value / l_cnt_chld;
2154 -- l_final_forecasted_value := l_final_forecasted_value / l_cnt_chld;
2155 -- END IF;
2156 -----------------------------------------------------
2157 -- if no rows are found for rollup or summary then
2158 -----------------------------------------------------
2159 END IF ; -- For Summary
2160 END IF; -- For Rollup
2161 IF l_origin_rec.functional_currency_code IS NOT NULL THEN
2162 l_func_currency_code := p_func_currency_code;
2163 ELSE
2164 l_func_currency_code := NULL;
2165 END IF;
2166 END IF; -- For Apport
2167 END IF ; -- For Cal_type
2168 -------------------------------------------------------------------
2169 -- if this is not a FIXED type metrics, then it is VARIABLE type
2170 -- this requires that another function be executed to calculate
2171 -- the metrics. No need to compute forecasted in VARIABLE type.
2172 -------------------------------------------------------------------
2173 ELSIF l_cal_type_rec.accrual_type = 'VARIABLE' THEN
2174
2175 ----------------------------------------------------------
2176 -- execute the function
2177 ----------------------------------------------------------
2178 -- 06/22/2001 huili change
2179 -- 06/14/2001 huili commented out since it is not supported by 11.5.5
2180 --l_compute_using_function_value := Exec_Function(
2181 -- p_activity_metric_id => p_activity_metric_id,
2182 -- p_function_name => l_cal_type_rec.compute_using_function);
2183 --l_compute_using_function_value := 0;
2184
2185 l_actual_value := NULL;
2186 l_forecasted_value := NULL;
2187
2188 OPEN c_var_met_info (l_origin_rec.depend_act_metric);
2189 FETCH c_var_met_info INTO l_actual_value, l_forecasted_value;
2190 CLOSE c_var_met_info;
2191
2192 ----------------------------------------------------------
2193 -- now actual value is multiple of the value from the
2194 -- function and that read for variable value from the
2195 -- table.
2196 ----------------------------------------------------------
2197 l_actual_value := NVL(l_actual_value,0)
2198 * NVL(l_origin_rec.variable_value,0);
2199 l_forecasted_value := NVL(l_forecasted_value,0);
2200 -- end of change
2201
2202 IF (l_cal_type_rec.default_uom_code IS NOT NULL) AND
2203 (l_origin_rec.metric_uom_code IS NOT NULL) THEN
2204 -------------------------------------------------------------------
2205 -- Convert the Actual value calculated into Base UOM amount
2206 -- While Updating Activity Metric Table with refreshed metric value,
2207 -- This will be converted back to UOM of this Activity Metric
2208 -------------------------------------------------------------------
2209 l_final_actual_value := Convert_Uom(
2210 p_from_uom_code => l_origin_rec.metric_uom_code,
2211 p_to_uom_code => l_cal_type_rec.default_uom_code,
2212 p_from_quantity => l_actual_value);
2213
2214 --06/28/2001 huili
2215 l_final_forecasted_value := Convert_Uom(
2216 p_from_uom_code => l_origin_rec.metric_uom_code,
2217 p_to_uom_code => l_cal_type_rec.default_uom_code,
2218 p_from_quantity => l_forecasted_value);
2219
2220 IF l_final_actual_value < 0 THEN
2221 l_final_actual_value := 0 ;
2222 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2223 END IF; --check final value
2224 ELSE
2225 IF l_origin_rec.functional_currency_code IS NOT NULL THEN
2226 l_func_currency_code := p_func_currency_code;
2227 ELSE
2228 l_func_currency_code := NULL;
2229 END IF;
2230 l_final_actual_value := l_actual_value;
2231 l_final_forecasted_value := l_forecasted_value;
2232 END IF; --if there is UOM
2233 END IF; -- For Fixed/Var
2234 END IF ; -- For %FOUND
2235 ---------------------------------------------------------------
2236 -- Added by bgeorge to make sure the transac actual value
2237 -- reflects the func actual value if there is no UOM or
2238 -- Currency conversion
2239 ---------------------------------------------------------------
2240
2241 l_conv_uom_actual_value := l_final_actual_value;
2242 l_conv_uom_forecasted_value := l_final_forecasted_value;
2243
2244 IF (l_cal_type_rec.default_uom_code IS NOT NULL) AND
2245 (l_origin_rec.metric_uom_code IS NOT NULL) THEN
2246 ---------------------------------------------------------------
2247 -- Convert the UOM Back to the Original UOM (As the Value
2248 -- Calculated is in Default UOM of this Metric )
2249 ---------------------------------------------------------------
2250 l_conv_uom_actual_value := Convert_Uom(
2251 p_from_uom_code => l_cal_type_rec.default_uom_code,
2252 p_to_uom_code => l_origin_rec.metric_uom_code,
2253 p_from_quantity => l_final_actual_value);
2254 l_conv_uom_forecasted_value := Convert_Uom(
2255 p_from_uom_code => l_cal_type_rec.default_uom_code,
2256 p_to_uom_code => l_origin_rec.metric_uom_code,
2257 p_from_quantity => l_final_forecasted_value);
2258 IF (l_conv_uom_actual_value < 0) OR (l_conv_uom_forecasted_value < 0) THEN
2259 l_final_actual_value := 0 ;
2260 l_final_forecasted_value := 0;
2261 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2262 END IF;
2263 END IF;
2264 ----------------------------------------------
2265 -- set return value for this procedure
2266 ----------------------------------------------
2267 x_actual_value := l_final_actual_value ;
2268 x_forecasted_value := l_final_forecasted_value;
2269
2270 -------------------------------------------------------------
2271 -- Initialize the record type for Activity Metric update
2272 -------------------------------------------------------------
2273 Ams_Actmetric_Pvt.Init_ActMetric_Rec(x_act_metric_rec => l_act_metrics_rec );
2274 -- BUG2385692: Set transaction values to recalculate.
2275 l_act_metrics_rec.trans_actual_value := NULL;
2276 l_act_metrics_rec.trans_forecasted_value := NULL;
2277 l_act_metrics_rec.func_actual_value := l_conv_uom_actual_value;
2278 l_act_metrics_rec.func_forecasted_value := l_conv_uom_forecasted_value;
2279 l_act_metrics_rec.functional_currency_code := l_func_currency_code;
2280 l_act_metrics_rec.activity_metric_id := p_activity_metric_id;
2281 l_act_metrics_rec.computed_using_function_value :=
2282 l_compute_using_function_value;
2283 l_act_metrics_rec.difference_since_last_calc :=
2284 l_act_metrics_rec.func_actual_value - l_origin_rec.func_actual_value;
2285 l_act_metrics_rec.days_since_last_refresh :=
2286 l_current_date - l_origin_rec.last_calculated_date;
2287 l_act_metrics_rec.last_calculated_date := l_current_date;
2288 l_act_metrics_rec.dirty_flag := 'N';
2289
2290 Ams_Actmetric_Pvt.update_actmetric (
2291 p_api_version => l_api_version,
2292 p_init_msg_list => p_init_msg_list,
2293 p_commit => Fnd_Api.G_FALSE,
2294 p_validation_level => Fnd_Api.g_valid_level_full,
2295 p_act_metric_rec => l_act_metrics_rec,
2296 x_return_status => x_return_status,
2297 x_msg_count => x_msg_count,
2298 x_msg_data => x_msg_data);
2299
2300 IF x_return_status = Fnd_Api.G_RET_STS_ERROR THEN
2301 RAISE Fnd_Api.G_EXC_ERROR;
2302 ELSIF x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
2303 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2304 END IF;
2305
2306 -- Call Campaign API /Deliverable API/Events API to update the values
2307 -- Chect the x_return_status after the Update
2308 -- Handle the Error
2309 --
2310 -- End API Body.
2311 --
2312 --
2313 -- Standard check for commit request.
2314 --
2315 IF Fnd_Api.To_Boolean (p_commit) THEN
2316 COMMIT WORK;
2317 END IF;
2318 --
2319 -- Standard API to get message count, and if 1,
2320 -- set the message data OUT variable.
2321 --
2322 Fnd_Msg_Pub.Count_And_Get (
2323 p_count => x_msg_count,
2324 p_data => x_msg_data,
2325 p_encoded => Fnd_Api.G_FALSE
2326 );
2327 --
2328 -- Add success message to message list.
2329 --
2330 IF AMS_DEBUG_HIGH_ON THEN
2331 Ams_Utility_Pvt.debug_message(l_full_name ||': end');
2332 END IF;
2333 EXCEPTION
2334 WHEN Fnd_Api.G_EXC_ERROR THEN
2335 -- ROLLBACK TO CALCULATE_METRIC_SAVEPOINT;
2336 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2337 Fnd_Msg_Pub.Count_And_Get (
2338 p_count => x_msg_count,
2339 p_data => x_msg_data,
2340 p_encoded => FND_API.G_FALSE
2341 );
2342 RAISE Fnd_Api.G_EXC_ERROR;
2343 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
2344 -- ROLLBACK TO CALCULATE_METRIC_SAVEPOINT;
2345 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2346 Fnd_Msg_Pub.Count_And_Get (
2347 p_count => x_msg_count,
2348 p_data => x_msg_data,
2349 p_encoded => FND_API.G_FALSE
2350 );
2351 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR ;
2352 when ams_utility_pvt.resource_locked then
2353 Fnd_Message.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
2354 Fnd_Msg_Pub.ADD;
2355 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR ;
2356 WHEN OTHERS THEN
2357 -- ROLLBACK TO CALCULATE_METRIC_SAVEPOINT;
2358
2359 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2360 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
2361 Fnd_Msg_Pub.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
2362 END IF;
2363 Fnd_Msg_Pub.Count_And_Get (
2364 p_count => x_msg_count,
2365 p_data => x_msg_data,
2366 p_encoded => FND_API.G_FALSE
2367 );
2368 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR ;
2369
2370 END Calculate_Metric;
2371
2372 -- Start of comments
2373 -- NAME
2374 -- Copy_seeded_Metric
2375 --
2376 -- PURPOSE
2377 -- This Procedure is called when a new Usage(Campaign/Event/Del.) is Created
2378 -- This will check the templates defined for the given usage ,usage type
2379 -- and will copy the metrics associated with this Template to the Activity
2380 -- Metric.
2381 -- For e.g. when Campaign c1 is created with type type1 , this process will
2382 -- check the template defined for campaigns (or if avilable Template for this
2383 -- Campaign for this campaign type ) .If Metric M1,M2,M3 are attached to
2384 -- Campaigns then the rows are inserted into Activity Metric table for
2385 -- M1,M2,M3 attached to C1.
2386 --
2387 -- NOTES
2388 --
2389 -- HISTORY
2390 -- 10/06/1999 ptendulk Created.
2391 -- 27-FEB-2000 bgeorge Modified to call the trigger API for metrics
2392 -- refresh in the copy_seeded_metrics
2393 -- 05/08/2000 bgeorge Commented out the call to create triggers
2394 -- 07/17/2000 khung bug 1356700 fix. add check category_id in copy_seeded_metric
2395 -- 02/26/2002 dmvincen New Feature: Metric Templates extended features.
2396 -- 25-dec-2002 choang added enabled_flag to cursor c_tpl_all_metrics
2397 -- 27-oct-2003 choang Enh 3130095: optimized metric cursor and removed assocs
2398 -- 02-nov-2003 choang Enh 3199867: added support for Tracking custom setup
2399 -- 21-Nov-2005 dmvincen BUG4742384: Copy fixed then variable.
2400 -- End of comments
2401
2402 PROCEDURE Copy_Seeded_Metric (
2403 p_api_version IN NUMBER,
2404 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
2405 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
2406 x_return_status OUT NOCOPY VARCHAR2,
2407 x_msg_count OUT NOCOPY NUMBER,
2408 x_msg_data OUT NOCOPY VARCHAR2,
2409 p_arc_act_metric_used_by IN VARCHAR2 ,
2410 p_act_metric_used_by_id IN NUMBER ,
2411 p_act_metric_used_by_type IN VARCHAR2
2412 )
2413 IS
2414 L_API_VERSION CONSTANT NUMBER := 1.0;
2415 L_API_NAME CONSTANT VARCHAR2(30) := 'Copy_Seeded_Metric';
2416 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2417
2418 -- choang - 27-oct-2003
2419 -- enh 3130095: tuned query and use new schema for picking
2420 -- up the correct metrics to copy.
2421 -- when custom setup is involved, admins have the option
2422 -- to include or exclude any of the cue-cards:
2423 -- costs and revenues map to cateogry 901 and 902
2424 -- all other metrics go into the other cue-card
2425 --Bug# 3335711 - 23-dec-2003 - sunkumar - added check for object type
2426 --BUG4742384: Copy fixed metrics first then variable.
2427 CURSOR c_tpl_all_metrics (l_object_type IN VARCHAR2,
2428 l_setup_id IN NUMBER,
2429 p_cost_flag IN NUMBER,
2430 p_amet_flag IN NUMBER,
2431 p_track_flag IN NUMBER) IS
2432 SELECT DISTINCT met.metric_id, met.metric_category, met.accrual_type
2433 FROM ams_metrics_all_b met, ams_met_tpl_details det, ams_met_tpl_headers_b tpl
2434 WHERE
2435 met.arc_metric_used_for_object IN ( l_object_type, 'ANY')
2436 AND met.enabled_flag = 'Y'
2437 AND met.metric_id = det.metric_id
2438 AND tpl.metric_tpl_header_id = det.metric_tpl_header_id
2439 AND tpl.enabled_flag = 'Y'
2440 AND det.enabled_flag = 'Y'
2441 AND ((1 = p_track_flag) OR
2442 (EXISTS (SELECT 1
2443 FROM ams_categories_b cat
2444 WHERE cat.category_id = met.metric_category
2445 AND ((1 = p_cost_flag AND category_id IN (901, 902)) OR
2446 (1 = p_amet_flag AND category_id NOT IN (901, 902))
2447 AND enabled_flag = 'Y'))))
2448 AND ((tpl.association_type = 'OBJECT_TYPE'
2449 AND tpl.used_by_code = l_object_type)
2450 OR (tpl.association_type = 'CUSTOM_SETUP'
2451 AND tpl.used_by_id = l_setup_id))
2452 order by met.accrual_type
2453 ;
2454
2455 /***
2456 -- choang - 27-oct-2003
2457 -- enh 3130095: obsoleted query; no longer using met_tpl_assocs
2458 -- and the query had three full table scans in explain plan on
2459 -- dev instance mktd2r10 and mktu2r10.
2460 CURSOR c_tpl_all_metrics(l_object_type VARCHAR2, l_setup_id NUMBER) IS
2461 SELECT metric_id, metric_category
2462 FROM ams_metrics_all_b met
2463 WHERE met.arc_metric_used_for_object = l_object_type
2464 AND met.metric_calculation_type IN ('MANUAL', 'FUNCTION')
2465 AND met.enabled_flag = 'Y'
2466 AND metric_id IN
2467 (SELECT metric_id
2468 FROM ams_met_tpl_details dtl
2469 WHERE enabled_flag = 'Y'
2470 AND metric_tpl_header_id IN
2471 (SELECT metric_tpl_header_id FROM ams_met_tpl_headers_vl
2472 WHERE enabled_flag = 'Y'
2473 AND metric_tpl_header_id IN
2474 (SELECT metric_tpl_header_id FROM ams_met_tpl_assocs
2475 WHERE enabled_flag = 'Y'
2476 AND ASSOCIATION_TYPE = 'OBJECT_TYPE'
2477 AND used_by_code = l_object_type
2478 UNION ALL
2479 SELECT metric_tpl_header_id FROM ams_met_tpl_assocs
2480 WHERE enabled_flag = 'Y'
2481 AND ASSOCIATION_TYPE = 'CUSTOM_SETUP'
2482 AND used_by_id = l_setup_id
2483 )
2484 )
2485 )
2486 ;
2487 ***/
2488
2489 CURSOR c_setup_attrs (p_custom_setup_id NUMBER) IS
2490 SELECT MAX (DECODE (object_attribute, 'COST', 1, 0)) cost_flag
2491 , MAX (DECODE (object_attribute, 'AMET', 1, 0)) amet_flag
2492 , MAX (DECODE (object_attribute, 'TRACK', 1, 0)) track_flag
2493 FROM ams_custom_setup_attr
2494 WHERE attr_available_flag = 'Y'
2495 AND custom_setup_id = p_custom_setup_id
2496 AND object_attribute IN ('COST', 'AMET', 'TRACK')
2497 ;
2498
2499 /*** cursor does one pass
2500 CURSOR c_setup_attrs(p_custom_setup_id NUMBER) IS
2501 SELECT object_attribute
2502 FROM ams_custom_setup_attr
2503 WHERE attr_available_flag = 'Y'
2504 AND custom_setup_id = p_custom_setup_id
2505 AND object_attribute IN ('COST', 'AMET');
2506 ***/
2507
2508 l_setup_rec c_setup_attrs%ROWTYPE;
2509 l_object_attribute ams_custom_setup_attr.OBJECT_ATTRIBUTE%TYPE;
2510 l_metric_id NUMBER;
2511 l_metric_category NUMBER;
2512 l_custom_setup_id NUMBER := NULL;
2513 l_act_metrics_rec Ams_Actmetric_Pvt.act_metric_rec_type;
2514 l_activity_metric_id NUMBER;
2515 l_accrual_type VARCHAR2(2000);
2516
2517 CURSOR c_get_camp_setup(p_campaign_id NUMBER) IS
2518 SELECT custom_setup_id
2519 FROM ams_campaigns_all_b
2520 WHERE campaign_id = p_campaign_id;
2521
2522 CURSOR c_get_csch_setup(p_schedule_id NUMBER) IS
2523 SELECT custom_setup_id
2524 FROM AMS_CAMPAIGN_SCHEDULES_B
2525 WHERE schedule_id = p_schedule_id;
2526
2527 CURSOR c_get_eveh_setup(p_event_id NUMBER) IS
2528 SELECT setup_type_id
2529 FROM AMS_EVENT_HEADERS_ALL_B
2530 WHERE EVENT_HEADER_ID = p_event_id;
2531
2532 CURSOR c_get_eveo_setup(p_event_offer_id NUMBER) IS
2533 SELECT setup_type_id
2534 FROM AMS_EVENT_OFFERS_ALL_B
2535 WHERE EVENT_OFFER_ID = p_event_offer_id;
2536
2537 CURSOR c_get_delv_setup(p_deliverable_id NUMBER) IS
2538 SELECT custom_setup_id
2539 FROM AMS_DELIVERABLES_ALL_B
2540 WHERE DELIVERABLE_ID = p_deliverable_id;
2541
2542 /*** choang - 27-oct-2003 - removed old code see previous version for commented code ***/
2543 BEGIN
2544 --
2545 -- Initialize savepoint.
2546 --
2547 SAVEPOINT Copy_Seeded_Metric_pvt;
2548 --
2549 -- Output debug message.
2550 --
2551 IF AMS_DEBUG_HIGH_ON THEN
2552 Ams_Utility_Pvt.debug_message(l_full_name||': START');
2553 END IF;
2554 --
2555 -- Initialize message list if p_init_msg_list is set to TRUE.
2556 --
2557 IF Fnd_Api.To_Boolean (p_init_msg_list) THEN
2558 Fnd_Msg_Pub.Initialize;
2559 END IF;
2560 --
2561 -- Standard check for API version compatibility.
2562 --
2563 IF NOT Fnd_Api.Compatible_API_Call (L_API_VERSION,
2564 p_api_version,
2565 L_API_NAME,
2566 G_PKG_NAME)
2567 THEN
2568 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2569 END IF;
2570 --
2571 -- Initialize API return status to success.
2572 --
2573 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2574 --
2575 -- Begin API Body
2576 --
2577 IF p_arc_act_metric_used_by IN ('RCAM', 'CAMP') THEN
2578 OPEN c_get_camp_setup(p_act_metric_used_by_id);
2579 FETCH c_get_camp_setup INTO l_custom_setup_id;
2580 CLOSE c_get_camp_setup;
2581 ELSIF p_arc_act_metric_used_by IN ('CSCH') THEN
2582 OPEN c_get_csch_setup(p_act_metric_used_by_id);
2583 FETCH c_get_csch_setup INTO l_custom_setup_id;
2584 CLOSE c_get_csch_setup;
2585 ELSIF p_arc_act_metric_used_by IN ('EVEH') THEN
2586 OPEN c_get_eveh_setup(p_act_metric_used_by_id);
2587 FETCH c_get_eveh_setup INTO l_custom_setup_id;
2588 CLOSE c_get_eveh_setup;
2589 ELSIF p_arc_act_metric_used_by IN ('EVEO', 'EONE') THEN
2590 OPEN c_get_eveo_setup(p_act_metric_used_by_id);
2591 FETCH c_get_eveo_setup INTO l_custom_setup_id;
2592 CLOSE c_get_eveo_setup;
2593 ELSIF p_arc_act_metric_used_by IN ('DELV') THEN
2594 OPEN c_get_delv_setup(p_act_metric_used_by_id);
2595 FETCH c_get_delv_setup INTO l_custom_setup_id;
2596 CLOSE c_get_delv_setup;
2597 /***** BUG2845365: Remove dialogue Components. *****
2598 ELSIF p_arc_act_metric_used_by IN ('DILG') THEN
2599 OPEN c_get_dilg_setup(p_act_metric_used_by_id);
2600 FETCH c_get_dilg_setup INTO l_custom_setup_id;
2601 CLOSE c_get_dilg_setup;
2602 ELSIF p_arc_act_metric_used_by in
2603 ('AMS_COMP_START', 'AMS_COMP_SHOW_WEB_PAGE', 'AMS_COMP_END') THEN
2604 l_has_amet := fnd_api.g_true;
2605 ****** BUG2845365 ******/
2606 END IF;
2607
2608 -- choang - 27-oct-2003 - flag consists of 1 or 0
2609 l_setup_rec.cost_flag := 1;
2610 l_setup_rec.amet_flag := 1;
2611 l_setup_rec.track_flag := 1;
2612
2613 IF l_custom_setup_id IS NULL THEN
2614 l_custom_setup_id := -1; -- undefined setup.
2615 ELSE
2616 OPEN c_setup_attrs(l_custom_setup_id);
2617 FETCH c_setup_attrs INTO l_setup_rec;
2618 CLOSE c_setup_attrs;
2619
2620 END IF;
2621
2622 OPEN c_tpl_all_metrics(p_arc_act_metric_used_by,
2623 l_custom_setup_id,
2624 l_setup_rec.cost_flag,
2625 l_setup_rec.amet_flag,
2626 l_setup_rec.track_flag);
2627 LOOP
2628 FETCH c_tpl_all_metrics INTO l_metric_id, l_metric_category,
2629 l_accrual_type;
2630 EXIT WHEN c_tpl_all_metrics%NOTFOUND;
2631 IF AMS_DEBUG_HIGH_ON THEN
2632 Ams_Utility_Pvt.debug_message(l_full_name||': Metric_Id='||l_metric_id);
2633 END IF;
2634 /*** choang - 27-oct-2003 - optimized to filter in the metric query
2635 IF (l_has_cost = FND_API.G_TRUE AND l_metric_category IN (901,902)) OR
2636 (l_has_amet = FND_API.G_TRUE AND l_metric_category NOT IN (901,902)) THEN
2637 ***/
2638 l_act_metrics_rec.metric_id := l_metric_id ;
2639 l_act_metrics_rec.arc_act_metric_used_by := p_arc_act_metric_used_by;
2640 l_act_metrics_rec.act_metric_used_by_id := p_act_metric_used_by_id;
2641 l_act_metrics_rec.application_id := 530 ; -- Oracle Marketing
2642 Ams_Actmetric_Pub.Create_ActMetric (
2643 p_api_version => p_api_version,
2644 p_init_msg_list => Fnd_Api.G_FALSE,
2645 p_commit => Fnd_Api.G_FALSE,
2646 p_validation_level => Fnd_Api.g_valid_level_full,
2647 p_act_metric_rec => l_act_metrics_rec,
2648 x_return_status => x_return_status,
2649 x_msg_count => x_msg_count,
2650 x_msg_data => x_msg_data,
2651 x_activity_metric_id => l_activity_metric_id
2652 );
2653 -- If any errors happen abort API.
2654 IF x_return_status = Fnd_Api.G_RET_STS_ERROR THEN
2655 RAISE Fnd_Api.G_EXC_ERROR;
2656 ELSIF x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
2657 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2658 END IF;
2659 /*** END IF; ***/
2660 END LOOP;
2661 CLOSE c_tpl_all_metrics;
2662 /*** choang - 27-oct-2003 - removed old code from 2000
2663 -- Bug fix 1265154
2664 -- 05/08/2000 BGEORGE
2665 ***/
2666 -- End API Body
2667 --
2668 --
2669 -- Standard API to get message count, and if 1,
2670 -- set the message data OUT variable.
2671 --
2672 Fnd_Msg_Pub.Count_And_Get (
2673 p_count => x_msg_count,
2674 p_data => x_msg_data,
2675 p_encoded => Fnd_Api.G_FALSE
2676 );
2677 --
2678 -- Debug message.
2679 --
2680 IF AMS_DEBUG_HIGH_ON THEN
2681 Ams_Utility_Pvt.debug_message(l_full_name ||': END');
2682 END IF;
2683 EXCEPTION
2684 WHEN Fnd_Api.G_EXC_ERROR THEN
2685 ROLLBACK TO Copy_Seeded_Metric_pvt;
2686 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2687 Fnd_Msg_Pub.Count_And_Get (
2688 p_count => x_msg_count,
2689 p_data => x_msg_data,
2690 p_encoded => FND_API.G_FALSE
2691 );
2692 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
2693 ROLLBACK TO Copy_Seeded_Metric_pvt;
2694 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2695 Fnd_Msg_Pub.Count_And_Get (
2696 p_count => x_msg_count,
2697 p_data => x_msg_data,
2698 p_encoded => FND_API.G_FALSE
2699 );
2700 WHEN OTHERS THEN
2701 ROLLBACK TO Copy_Seeded_Metric_pvt;
2702 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2703 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
2704 Fnd_Msg_Pub.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
2705 END IF;
2706 Fnd_Msg_Pub.Count_And_Get (
2707 p_count => x_msg_count,
2708 p_data => x_msg_data,
2709 p_encoded => FND_API.G_FALSE
2710 );
2711 END Copy_seeded_Metric;
2712 --
2713 -- NAME
2714 -- Get_Apport_Met_ID
2715 --
2716 -- PURPOSE
2717 -- Returns the Metric ID for the apportioned Metric.
2718 --
2719 -- NOTES
2720 -- This function is not complete as of 10/19/1999 , Pending Issue
2721 --
2722 -- HISTORY
2723 -- 10/22/1999 ptendulk Created.
2724 --
2725 FUNCTION Get_Apport_Met_ID(p_master_object_type IN VARCHAR2)
2726 RETURN NUMBER
2727 IS
2728 BEGIN
2729 -- Will have to be changed
2730 RETURN 10000;
2731 END Get_Apport_Met_ID;
2732 -- Start of comments
2733 -- NAME
2734 -- Create_Apport_Metric
2735 --
2736 -- PURPOSE
2737 -- This Procedure is called when a new Object association is created.
2738 -- This will create Activity Metric in AMS_ACT_METRICS_ALL with the details
2739 -- of the association.
2740 --
2741 -- NOTES
2742 --
2743 -- HISTORY
2744 -- 10/06/1999 ptendulk Created.
2745 --
2746 -- End of comments
2747 PROCEDURE Create_Apport_Metric(
2748 p_api_version IN NUMBER,
2749 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
2750 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
2751 x_return_status OUT NOCOPY VARCHAR2,
2752 x_msg_count OUT NOCOPY NUMBER,
2753 x_msg_data OUT NOCOPY VARCHAR2,
2754 p_obj_association_id IN NUMBER
2755 )
2756 IS
2757 L_API_VERSION CONSTANT NUMBER := 1.0;
2758 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Apportioned_Metric';
2759 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
2760 CURSOR c_obj_det IS
2761 SELECT master_object_type,
2762 master_object_id
2763 FROM ams_object_associations
2764 WHERE object_association_id = p_obj_association_id ;
2765 CURSOR c_act_met IS
2766 SELECT COUNT(1)
2767 FROM ams_act_metrics_all
2768 WHERE activity_metric_origin_id = p_obj_association_id ;
2769 l_obj_det_rec c_obj_det%ROWTYPE ;
2770 -- Initialize Activity Metric Record type for Insertion/Updation of Act. Metric
2771 l_act_metrics_rec Ams_Actmetric_Pvt.act_metric_rec_type ;
2772 l_amount NUMBER;
2773 l_count NUMBER;
2774 l_activity_metric_id NUMBER ;
2775 l_return_status VARCHAR2(1) ;
2776 BEGIN
2777 --
2778 -- Initialize savepoint.
2779 --
2780 SAVEPOINT Create_App_Metric_pvt;
2781 --
2782 -- Output debug message.
2783 --
2784 IF AMS_DEBUG_HIGH_ON THEN
2785 Ams_Utility_Pvt.Debug_Message(l_full_name||': START');
2786 END IF;
2787 --
2788 -- Initialize message list if p_init_msg_list is set to TRUE.
2789 --
2790 IF Fnd_Api.To_Boolean (p_init_msg_list) THEN
2791 Fnd_Msg_Pub.Initialize;
2792 END IF;
2793 --
2794 -- Standard check for API version compatibility.
2795 --
2796 IF NOT Fnd_Api.Compatible_API_Call (L_API_VERSION,
2797 p_api_version,
2798 L_API_NAME,
2799 G_PKG_NAME)
2800 THEN
2801 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2802 END IF;
2803 --
2804 -- Initialize API return status to success.
2805 --
2806 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2807 --
2808 -- Begin API Body
2809 --
2810 Get_Met_Apport_Val(
2811 p_obj_association_id => p_obj_association_id,
2812 x_return_status => x_return_status,
2813 x_apportioned_value => l_amount
2814 ) ;
2815 OPEN c_act_met ;
2816 FETCH c_act_met INTO l_count ;
2817 CLOSE c_act_met ;
2818 IF l_count > 0 THEN
2819 -- Update Activity Metric
2820 -- Initialize the record type for Activity Metric updation
2821 Ams_Actmetric_Pvt.Init_ActMetric_Rec(
2822 x_act_metric_rec => l_act_metrics_rec );
2823 l_act_metrics_rec.activity_metric_origin_id := p_obj_association_id ;
2824 l_act_metrics_rec.func_actual_value := l_amount ;
2825 l_act_metrics_rec.trans_actual_value :=
2826 l_act_metrics_rec.func_actual_value ;
2827 Ams_Actmetric_Pvt.Update_ActMetric (
2828 p_api_version => l_api_version,
2829 p_init_msg_list => p_init_msg_list,
2830 p_commit => Fnd_Api.G_FALSE,
2831 p_validation_level => Fnd_Api.g_valid_level_full,
2832 p_act_metric_rec => l_act_metrics_rec,
2833 x_return_status => l_return_status,
2834 x_msg_count => x_msg_count,
2835 x_msg_data => x_msg_data
2836 );
2837 ELSE
2838 -- Insert Activity Metric
2839 OPEN c_obj_det ;
2840 FETCH c_obj_det INTO l_obj_det_rec ;
2841 CLOSE c_obj_det ;
2842 l_act_metrics_rec.activity_metric_origin_id := p_obj_association_id ;
2843 l_act_metrics_rec.func_actual_value := l_amount ;
2844 l_act_metrics_rec.trans_actual_value := l_amount ;
2845 -- Give call to routine which will decide the Apportioned Metric ID for
2846 -- this Activity Metric
2847 l_act_metrics_rec.metric_id :=
2848 Get_Apport_Met_ID(l_obj_det_rec.master_object_type) ;
2849 l_act_metrics_rec.act_metric_used_by_id :=
2850 l_obj_det_rec.master_object_id;
2851 l_act_metrics_rec.arc_act_metric_used_by :=
2852 l_obj_det_rec.master_object_type;
2853 l_act_metrics_rec.application_id := 530 ;
2854 -- Give Call to Default_Func_Currency to decide default Currency code
2855 l_act_metrics_rec.functional_currency_code :=
2856 Ams_Actmetric_Pvt.default_func_currency ;
2857 l_act_metrics_rec.transaction_currency_code :=
2858 l_act_metrics_rec.functional_currency_code ;
2859 Ams_Actmetric_Pub.Create_ActMetric (
2860 p_api_version => l_api_version,
2861 p_init_msg_list => p_init_msg_list,
2862 p_commit => Fnd_Api.G_FALSE,
2863 p_validation_level => Fnd_Api.g_valid_level_full,
2864 p_act_metric_rec => l_act_metrics_rec,
2865 x_return_status => l_return_status,
2866 x_msg_count => x_msg_count,
2867 x_msg_data => x_msg_data,
2868 x_activity_metric_id => l_activity_metric_id
2869 );
2870 END IF;
2871 -- If any errors happen abort API.
2872 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
2873 RAISE Fnd_Api.G_EXC_ERROR;
2874 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
2875 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2876 END IF;
2877 --
2878 -- Standard API to get message count, and if 1,
2879 -- set the message data OUT variable.
2880 --
2881 Fnd_Msg_Pub.Count_And_Get (
2882 p_count => x_msg_count,
2883 p_data => x_msg_data,
2884 p_encoded => Fnd_Api.G_FALSE
2885 );
2886 --
2887 -- Add success message to message list.
2888 --
2889 IF AMS_DEBUG_HIGH_ON THEN
2890 Ams_Utility_Pvt.debug_message(l_full_name ||': END Success');
2891 END IF;
2892 EXCEPTION
2893 WHEN Fnd_Api.G_EXC_ERROR THEN
2894 ROLLBACK TO Create_App_Metric_pvt;
2895 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2896 Fnd_Msg_Pub.Count_And_Get (
2897 p_count => x_msg_count,
2898 p_data => x_msg_data,
2899 p_encoded => FND_API.G_FALSE
2900 );
2901 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
2902 ROLLBACK TO Create_App_Metric_pvt;
2903 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2904 Fnd_Msg_Pub.Count_And_Get (
2905 p_count => x_msg_count,
2906 p_data => x_msg_data,
2907 p_encoded => FND_API.G_FALSE
2908 );
2909 WHEN OTHERS THEN
2910 ROLLBACK TO Create_App_Metric_pvt;
2911 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2912 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
2913 Fnd_Msg_Pub.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
2914 END IF;
2915 Fnd_Msg_Pub.Count_And_Get (
2916 p_count => x_msg_count,
2917 p_data => x_msg_data,
2918 p_encoded => FND_API.G_FALSE
2919 );
2920 END Create_Apport_Metric;
2921
2922 -- Start of Comments
2923 --
2924 -- NAME
2925 -- Refresh_Act_metrics
2926 --
2927 -- PURPOSE
2928 -- This procedure wraps around Refresh_Metric and is called
2929 -- from concurrent program
2930 --
2931 -- NOTES
2932 --
2933 --
2934 -- HISTORY
2935 -- 05/02/1999 bgeorge created
2936 -- End of Comments
2937
2938
2939 PROCEDURE Refresh_Act_metrics (
2940 errbuf OUT NOCOPY VARCHAR2,
2941 retcode OUT NOCOPY NUMBER,
2942 p_update_history IN VARCHAR2 := Fnd_Api.G_FALSE
2943 )
2944 IS
2945 BEGIN
2946 -- DMVINCEN: 04-APR-2001: New routine.
2947 Ams_Actmetrics_Engine_Pvt.Refresh_Act_Metrics_Engine(
2948 x_errbuf => errbuf,
2949 x_retcode => retcode,
2950 p_update_history => p_update_history);
2951 END Refresh_Act_Metrics;
2952
2953 -- Start of comments
2954 -- NAME
2955 -- Refresh_Metric
2956 --
2957 -- PURPOSE
2958 -- Re-calculate the value for a given activity metric.
2959 --
2960 -- NOTES
2961 --
2962 -- HISTORY
2963 -- 07/05/1999 choang Created.
2964 -- 09/28/1999 ptendulk Modified
2965 -- 08/16/2001 dmvincen Pass the default currency to syncronize.
2966 -- 08/16/2001 dmvincen Removed unused parameters from calculate_metric
2967 -- 08/30/2001 huili Added call to the procedure "Run_Object_Procedures".
2968 PROCEDURE Refresh_Metric (
2969 p_api_version IN NUMBER,
2970 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_TRUE,
2971 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
2972 x_return_status OUT NOCOPY VARCHAR2,
2973 x_msg_count OUT NOCOPY NUMBER,
2974 x_msg_data OUT NOCOPY VARCHAR2,
2975 p_activity_metric_id IN NUMBER,
2976 p_refresh_type IN VARCHAR2,
2977 p_refresh_function IN VARCHAR2 := Fnd_Api.G_TRUE
2978 )
2979 IS
2980 L_API_VERSION CONSTANT NUMBER := 1.0;
2981 L_API_NAME VARCHAR2(30) := 'Refresh_Metric';
2982 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2983 l_function_name VARCHAR2(30);
2984 l_actual_value NUMBER;
2985 l_forecasted_value NUMBER;
2986
2987 -- This Cursor will Select the Metric ID and
2988 -- Usage details for the Activity Metrics
2989 CURSOR c_act_metric(l_act_metric_id NUMBER) IS
2990 SELECT metric_id,
2991 arc_act_metric_used_by,
2992 act_metric_used_by_id,
2993 metric_uom_code
2994 FROM ams_act_metrics_all
2995 WHERE activity_metric_id = l_act_metric_id;
2996 l_act_metric_rec c_act_metric%ROWTYPE;
2997 --
2998 -- Retrieve all associated activities for the given
2999 -- business entity, identified by the combination of
3000 -- ARC_ACT_METRIC_USED_BY and ACT_METRIC_USED_BY_ID.
3001 --
3002 CURSOR c_all_metrics(l_arc_act_metric_used_by VARCHAR2,
3003 l_act_metric_used_by_id NUMBER) IS
3004 SELECT activity_metric_id,actmet.metric_id,actmet.dirty_flag,
3005 met.metric_calculation_type
3006 FROM ams_act_metrics_all actmet, ams_metrics_all_b met
3007 WHERE actmet.metric_id = met.metric_id
3008 AND actmet.arc_act_metric_used_by = l_arc_act_metric_used_by
3009 AND actmet.act_metric_used_by_id = l_act_metric_used_by_id
3010 AND actmet.summarize_to_metric IS NULL;
3011 --AND (dirty_flag = 'Y' OR metric_calculation_type = 'FUNCTION');
3012
3013 -- select only the dirty records or FUNCTION type records,
3014 -- added SVEERAVE - 10/13/00,12/14/00
3015 l_all_metrics_rec c_all_metrics%ROWTYPE;
3016 BEGIN
3017 --
3018 -- Initialize savepoint.
3019 --
3020 SAVEPOINT Refresh_Metric_SavePoint;
3021 --
3022 -- Output debug message.
3023 --
3024 IF AMS_DEBUG_HIGH_ON THEN
3025 Ams_Utility_Pvt.debug_message(l_full_name||': START');
3026 END IF;
3027 --
3028 -- Initialize message list if p_init_msg_list is set to TRUE.
3029 --
3030 IF Fnd_Api.To_Boolean (p_init_msg_list) THEN
3031 Fnd_Msg_Pub.Initialize;
3032 END IF;
3033 --
3034 -- Standard check for API version compatibility.
3035 --
3036 IF NOT Fnd_Api.Compatible_API_Call (L_API_VERSION,
3037 p_api_version,
3038 L_API_NAME,
3039 G_PKG_NAME)
3040 THEN
3041 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3042 END IF;
3043 --
3044 -- Initialize API return status to success.
3045 --
3046 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3047 --
3048 -- Begin API Body.
3049 --
3050
3051
3052
3053 OPEN c_act_metric(p_activity_metric_id);
3054 FETCH c_act_metric INTO l_act_metric_rec;
3055 IF c_act_metric%NOTFOUND THEN
3056 -- activity metric not found
3057 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
3058 THEN -- MMSG
3059 Fnd_Message.Set_Name('AMS', 'AMS_METR_INVALID_ACT_METR');
3060 Fnd_Msg_Pub.ADD;
3061 END IF;
3062 CLOSE c_act_metric;
3063 RAISE Fnd_Api.G_EXC_ERROR;
3064 END IF;
3065 CLOSE c_act_metric;
3066
3067 Run_Object_Procedures (
3068 p_arc_act_metric_used_by => l_act_metric_rec.arc_act_metric_used_by,
3069 p_act_metric_used_by_id => l_act_metric_rec.act_metric_used_by_id);
3070
3071 -- Refresh or Create the association metric between dependent
3072 -- and master objects.
3073
3074 -- 06/19/2001 huili commented out since it is out of date
3075 --create_refresh_assoc_metrics(l_act_metric_rec.arc_act_metric_used_by,
3076 -- l_act_metric_rec.act_metric_used_by_id);
3077
3078 IF p_refresh_type = 'ONE'
3079 THEN
3080 -- Only Current Activity Metric Has to be refreshed
3081 Calculate_Metric (
3082 p_api_version => l_api_version ,
3083 p_init_msg_list => p_init_msg_list,
3084 p_commit => Fnd_Api.G_FALSE,
3085 x_return_status => x_return_status,
3086 x_msg_count => x_msg_count,
3087 x_msg_data => x_msg_data,
3088 p_activity_metric_id => p_activity_metric_id,
3089 -- p_act_metric_used_by_id => l_act_metric_rec.act_metric_used_by_id,
3090 -- p_arc_act_metric_used_by => l_act_metric_rec.arc_act_metric_used_by,
3091 -- p_act_metric_uom_code => l_act_metric_rec.metric_uom_code,
3092 x_actual_value => l_actual_value,
3093 x_forecasted_value => l_forecasted_value,
3094 p_refresh_function => p_refresh_function,
3095 p_func_currency_code => Ams_Actmetric_Pvt.DEFAULT_FUNC_CURRENCY);
3096 -- If any errors happen abort API.
3097 IF x_return_status = Fnd_Api.G_RET_STS_ERROR THEN
3098 RAISE Fnd_Api.G_EXC_ERROR;
3099 ELSIF x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
3100 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3101 END IF;
3102
3103 ELSIF p_refresh_type = 'ALL'
3104 THEN
3105 -- All the Activity Metrics attached to the current usage has to be refreshed
3106 OPEN c_all_metrics(l_act_metric_rec.arc_act_metric_used_by,
3107 l_act_metric_rec.act_metric_used_by_id);
3108 LOOP
3109 FETCH c_all_metrics INTO l_all_metrics_rec;
3110 EXIT WHEN c_all_metrics%NOTFOUND;
3111 IF AMS_DEBUG_MEDIUM_ON THEN
3112 Ams_Utility_Pvt.debug_message('Now Calling Calc Met FOR: '||
3113 l_act_metric_rec.act_metric_used_by_id||'-'||
3114 l_act_metric_rec.arc_act_metric_used_by);
3115 END IF;
3116 Calculate_Metric (
3117 p_api_version => l_api_version ,
3118 p_init_msg_list => p_init_msg_list,
3119 p_commit => Fnd_Api.G_FALSE,
3120 x_return_status => x_return_status,
3121 x_msg_count => x_msg_count,
3122 x_msg_data => x_msg_data,
3123 p_activity_metric_id => l_all_metrics_rec.activity_metric_id,
3124 -- p_act_metric_used_by_id => l_act_metric_rec.act_metric_used_by_id,
3125 -- p_arc_act_metric_used_by => l_act_metric_rec.arc_act_metric_used_by,
3126 -- p_act_metric_uom_code => l_act_metric_rec.metric_uom_code,
3127 x_actual_value => l_actual_value,
3128 x_forecasted_value => l_forecasted_value,
3129 p_refresh_function => p_refresh_function,
3130 p_func_currency_code => Ams_Actmetric_Pvt.DEFAULT_FUNC_CURRENCY);
3131
3132 -- If any errors happen abort API.
3133 IF x_return_status = Fnd_Api.G_RET_STS_ERROR THEN
3134 RAISE Fnd_Api.G_EXC_ERROR;
3135 ELSIF x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
3136 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3137 END IF;
3138 END LOOP;
3139 END IF;
3140 --
3141 -- End API Body.
3142 --
3143 --
3144 -- Standard check for commit request.
3145 --
3146 IF Fnd_Api.To_Boolean (p_commit) THEN
3147 COMMIT WORK;
3148 END IF;
3149 --
3150 -- Standard API to get message count, and if 1,
3151 -- set the message data OUT variable.
3152 --
3153 Fnd_Msg_Pub.Count_And_Get (
3154 p_count => x_msg_count,
3155 p_data => x_msg_data,
3156 p_encoded => Fnd_Api.G_FALSE
3157 );
3158 --
3159 -- Add success message to message list.
3160 --
3161 IF AMS_DEBUG_HIGH_ON THEN
3162 Ams_Utility_Pvt.debug_message(l_full_name ||': END');
3163 END IF;
3164 EXCEPTION
3165 WHEN Fnd_Api.G_EXC_ERROR THEN
3166 ROLLBACK TO Refresh_Metric_SavePoint;
3167 x_return_status := Fnd_Api.G_RET_STS_ERROR;
3168 Fnd_Msg_Pub.Count_And_Get (
3169 p_count => x_msg_count,
3170 p_data => x_msg_data,
3171 p_encoded => FND_API.G_FALSE
3172 );
3173 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
3174 ROLLBACK TO Refresh_Metric_SavePoint;
3175 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3176 Fnd_Msg_Pub.Count_And_Get (
3177 p_count => x_msg_count,
3178 p_data => x_msg_data,
3179 p_encoded => FND_API.G_FALSE
3180 );
3181 WHEN OTHERS THEN
3182 ROLLBACK TO Refresh_Metric_SavePoint;
3183 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3184 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
3185 Fnd_Msg_Pub.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
3186 END IF;
3187 Fnd_Msg_Pub.Count_And_Get (
3188 p_count => x_msg_count,
3189 p_data => x_msg_data,
3190 p_encoded => FND_API.G_FALSE
3191 );
3192 END Refresh_Metric;
3193
3194 -- Start of comments
3195 -- NAME
3196 -- Refresh_Metric
3197 --
3198 -- PURPOSE
3199 -- Re-calculate the value for a given activity metric.
3200 -- This accepts the object information to do the refresh.
3201 --
3202 -- NOTES
3203 --
3204 -- HISTORY
3205 -- 08/16/2001 dmvincen Created
3206 --
3207 PROCEDURE Refresh_Metric (
3208 p_api_version IN NUMBER,
3209 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_TRUE,
3210 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
3211 x_return_status OUT NOCOPY VARCHAR2,
3212 x_msg_count OUT NOCOPY NUMBER,
3213 x_msg_data OUT NOCOPY VARCHAR2,
3214 p_arc_act_metric_used_by IN VARCHAR2,
3215 p_act_metric_used_by_id IN NUMBER,
3216 p_refresh_function IN VARCHAR2 := Fnd_Api.G_TRUE
3217 )
3218 IS
3219 L_API_VERSION CONSTANT NUMBER := 1.0;
3220 L_API_NAME VARCHAR2(30) := 'Refresh_Metric';
3221 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
3222 l_function_name VARCHAR2(30);
3223 l_actual_value NUMBER;
3224 l_forecasted_value NUMBER;
3225
3226 -- This Cursor will Select the Metric ID and
3227 -- Usage details for the Activity Metrics
3228 -- CURSOR c_act_metric(l_act_metric_id NUMBER) IS
3229 -- SELECT metric_id,
3230 -- arc_act_metric_used_by,
3231 -- act_metric_used_by_id,
3232 -- metric_uom_code
3233 -- FROM ams_act_metrics_all
3234 -- WHERE activity_metric_id = l_act_metric_id;
3235 -- l_act_metric_rec c_act_metric%ROWTYPE;
3236 --
3237 -- Retrieve all associated activities for the given
3238 -- business entity, identified by the combination of
3239 -- ARC_ACT_METRIC_USED_BY and ACT_METRIC_USED_BY_ID.
3240 --
3241
3242 CURSOR c_all_metrics(l_arc_act_metric_used_by VARCHAR2,
3243 l_act_metric_used_by_id NUMBER) IS
3244 SELECT activity_metric_id,actmet.metric_id,actmet.dirty_flag,
3245 met.metric_calculation_type,actmet.metric_uom_code
3246 FROM ams_act_metrics_all actmet, ams_metrics_all_b met
3247 WHERE actmet.metric_id = met.metric_id
3248 AND actmet.arc_act_metric_used_by = l_arc_act_metric_used_by
3249 AND actmet.act_metric_used_by_id = l_act_metric_used_by_id
3250 AND actmet.summarize_to_metric IS NULL;
3251 --AND (dirty_flag = 'Y' OR metric_calculation_type = 'FUNCTION');
3252
3253 -- select only the dirty records or FUNCTION type records,
3254 -- added SVEERAVE - 10/13/00,12/14/00
3255 l_all_metrics_rec c_all_metrics%ROWTYPE;
3256 l_is_locked VARCHAR2(1);
3257 BEGIN
3258 --
3259 -- Initialize savepoint.
3260 --
3261 SAVEPOINT Refresh_Metric_SavePoint2;
3262 --
3263 -- Output debug message.
3264 --
3265 IF AMS_DEBUG_HIGH_ON THEN
3266 Ams_Utility_Pvt.debug_message(l_full_name||': START');
3267 END IF;
3268 --
3269 -- Initialize message list if p_init_msg_list is set to TRUE.
3270 --
3271 IF Fnd_Api.To_Boolean (p_init_msg_list) THEN
3272 Fnd_Msg_Pub.Initialize;
3273 END IF;
3274 --
3275 -- Standard check for API version compatibility.
3276 --
3277 IF NOT Fnd_Api.Compatible_API_Call (L_API_VERSION,
3278 p_api_version,
3279 L_API_NAME,
3280 G_PKG_NAME)
3281 THEN
3282 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3283 END IF;
3284 --
3285 -- Initialize API return status to success.
3286 --
3287 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3288 --
3289 -- Begin API Body.
3290
3291 l_is_locked := ams_actmetric_pvt.lock_object(
3292 p_api_version => l_api_version ,
3293 p_init_msg_list => Fnd_Api.G_FALSE,
3294 p_arc_act_metric_used_by => p_arc_act_metric_used_by,
3295 p_act_metric_used_by_id => p_act_metric_used_by_id,
3296 x_return_status => x_return_status,
3297 x_msg_count => x_msg_count,
3298 x_msg_data => x_msg_data);
3299
3300 IF x_return_status = Fnd_Api.G_RET_STS_ERROR THEN
3301 RAISE Fnd_Api.G_EXC_ERROR;
3302 ELSIF x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
3303 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3304 elsif l_is_locked = FND_API.G_FALSE THEN
3305 -- the object needs to be lock by this process.
3306 RAISE ams_utility_pvt.resource_locked;
3307 END IF;
3308
3309 --
3310 -- Replace the body: 11/19/2002
3311 ams_actmetrics_engine_PVT.Refresh_Act_Metrics_Engine
3312 (p_api_version => p_api_version,
3313 p_init_msg_list => p_init_msg_list,
3314 p_arc_act_metric_used_by => p_arc_act_metric_used_by,
3315 p_act_metric_used_by_id => p_act_metric_used_by_id,
3316 x_return_status => x_return_status,
3317 x_msg_count => x_msg_count,
3318 x_msg_data => x_msg_data,
3319 p_commit => FND_API.G_FALSE,
3320 p_run_functions => FND_API.G_TRUE--,
3321 --p_update_history => fnd_api.g_false
3322 );
3323 /****** OBSOLETE 11/19/2002: Using engine.
3324 Run_Object_Procedures (
3325 p_arc_act_metric_used_by => p_arc_act_metric_used_by,
3326 p_act_metric_used_by_id => p_act_metric_used_by_id);
3327
3328 -- All the Activity Metrics attached to the current usage has to be refreshed
3329 OPEN c_all_metrics(p_arc_act_metric_used_by,
3330 p_act_metric_used_by_id);
3331 LOOP
3332 FETCH c_all_metrics INTO l_all_metrics_rec;
3333 EXIT WHEN c_all_metrics%NOTFOUND;
3334 -- Ams_Utility_Pvt.debug_message('Now Calling Calc Met FOR: '||
3335 -- p_arc_act_metric_used_by||'-'||
3336 -- p_act_metric_used_by_id);
3337 Calculate_Metric (
3338 p_api_version => l_api_version ,
3339 p_init_msg_list => p_init_msg_list,
3340 p_commit => Fnd_Api.G_FALSE,
3341 x_return_status => x_return_status,
3342 x_msg_count => x_msg_count,
3343 x_msg_data => x_msg_data,
3344 p_activity_metric_id => l_all_metrics_rec.activity_metric_id,
3345 -- p_act_metric_used_by_id => l_act_metric_rec.act_metric_used_by_id,
3346 -- p_arc_act_metric_used_by => l_act_metric_rec.arc_act_metric_used_by,
3347 -- p_act_metric_uom_code => l_act_metric_rec.metric_uom_code,
3348 x_actual_value => l_actual_value,
3349 x_forecasted_value => l_forecasted_value,
3350 p_refresh_function => p_refresh_function,
3351 p_func_currency_code => Ams_Actmetric_Pvt.DEFAULT_FUNC_CURRENCY);
3352
3353 -- If any errors happen abort API.
3354 IF x_return_status = Fnd_Api.G_RET_STS_ERROR THEN
3355 CLOSE c_all_metrics;
3356 RAISE Fnd_Api.G_EXC_ERROR;
3357 ELSIF x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
3358 CLOSE c_all_metrics;
3359 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3360 END IF;
3361 END LOOP;
3362 CLOSE c_all_metrics;
3363
3364 -- DMVINCEN 03/28/2002: Iterate through all components for the dialog.
3365 -- BUG2845365: Removed dialogue support.
3366 IF p_arc_act_metric_used_by = 'DILG' THEN
3367 Refresh_Components(
3368 p_api_version => p_api_version,
3369 p_init_msg_list => Fnd_Api.G_FALSE,
3370 p_commit => p_commit,
3371 x_return_status => x_return_status,
3372 x_msg_count => x_msg_count,
3373 x_msg_data => x_msg_data,
3374 p_arc_act_metric_used_by => p_arc_act_metric_used_by,
3375 p_act_metric_used_by_id => p_act_metric_used_by_id,
3376 p_refresh_function => p_refresh_function
3377 );
3378 END IF;
3379 ***** OBSOLETE 11/19/2002: Using engine. */
3380 --
3381 -- End API Body.
3382 --
3383 --
3384 -- Standard check for commit request.
3385 --
3386 IF Fnd_Api.To_Boolean (p_commit) THEN
3387 COMMIT WORK;
3388 END IF;
3389 --
3390 -- Standard API to get message count, and if 1,
3391 -- set the message data OUT variable.
3392 --
3393 Fnd_Msg_Pub.Count_And_Get (
3394 p_count => x_msg_count,
3395 p_data => x_msg_data,
3396 p_encoded => Fnd_Api.G_FALSE
3397 );
3398 --
3399 -- Add success message to message list.
3400 --
3401 IF AMS_DEBUG_HIGH_ON THEN
3402 Ams_Utility_Pvt.debug_message(l_full_name ||': END');
3403 END IF;
3404 EXCEPTION
3405 when ams_utility_pvt.resource_locked then
3406 ROLLBACK TO Refresh_Metric_SavePoint2;
3407 Fnd_Message.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
3408 Fnd_Msg_Pub.ADD;
3409 x_return_status := Fnd_Api.G_RET_STS_ERROR;
3410 Fnd_Msg_Pub.Count_And_Get (
3411 p_count => x_msg_count,
3412 p_data => x_msg_data,
3413 p_encoded => FND_API.G_FALSE
3414 );
3415 /*
3416 EXCEPTION
3417 WHEN Fnd_Api.G_EXC_ERROR THEN
3418 --ROLLBACK TO Refresh_Metric_SavePoint;
3419 x_return_status := Fnd_Api.G_RET_STS_ERROR;
3420 Fnd_Msg_Pub.Count_And_Get (
3421 p_count => x_msg_count,
3422 p_data => x_msg_data,
3423 p_encoded => FND_API.G_FALSE
3424 );
3425 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
3426 --ROLLBACK TO Refresh_Metric_SavePoint;
3427 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3428 Fnd_Msg_Pub.Count_And_Get (
3429 p_count => x_msg_count,
3430 p_data => x_msg_data,
3431 p_encoded => FND_API.G_FALSE
3432 );
3433 WHEN OTHERS THEN
3434 --ROLLBACK TO Refresh_Metric_SavePoint;
3435 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3436 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
3437 Fnd_Msg_Pub.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
3438 END IF;
3439 Fnd_Msg_Pub.Count_And_Get (
3440 p_count => x_msg_count,
3441 p_data => x_msg_data,
3442 p_encoded => FND_API.G_FALSE
3443 );
3444 */
3445 END Refresh_Metric;
3446
3447 --
3448 -- NAME
3449 -- Exec_Procedure
3450 --
3451 -- PURPOSE
3452 -- Executes the given function stored procedure using object type and object
3453 -- id as parameters.
3454 --
3455 -- NOTES
3456 -- Use Native Dynamic SQL (8i feature) for executing the function.
3457 --
3458 -- HISTORY
3459 -- 08/29/2001 huili Created.
3460 --
3461 PROCEDURE Exec_Procedure (
3462 p_arc_act_metric_used_by IN VARCHAR2,
3463 p_act_metric_used_by_id IN NUMBER,
3464 p_function_name IN VARCHAR2)
3465 IS
3466 BEGIN
3467 IF p_arc_act_metric_used_by IS NOT NULL
3468 AND p_arc_act_metric_used_by IN
3469 ('CAMP', 'CSCH', 'DELV', 'EVEO', 'EVEH', 'RCAM', 'EONE')
3470 -- BUG2845365: Remove dialogue components.
3471 --'DILG')
3472 --, 'AMS_COMP_START', 'AMS_COMP_SHOW_WEB_PAGE', 'AMS_COMP_END')
3473 AND p_act_metric_used_by_id IS NOT NULL
3474 AND p_function_name IS NOT NULL THEN
3475 EXECUTE IMMEDIATE 'BEGIN ' || p_function_name ||
3476 '( :p_arc_act_metric_used_by, :p_act_metric_used_by_id ); END;'
3477 USING IN p_arc_act_metric_used_by, p_act_metric_used_by_id;
3478 END IF;
3479 EXCEPTION
3480 WHEN OTHERS THEN
3481 RETURN;
3482 END Exec_Procedure;
3483
3484 --
3485 -- NAME
3486 -- Run_Object_Procedures
3487 --
3488 -- PURPOSE
3489 -- Executes all stored procedures accociated with a business object.
3490 --
3491 -- NOTES
3492 -- Use Native Dynamic SQL (8i feature) for executing the function.
3493 --
3494 -- HISTORY
3495 -- 08/29/2001 huili Created.
3496 --
3497 PROCEDURE Run_Object_Procedures (
3498 p_arc_act_metric_used_by IN VARCHAR2,
3499 p_act_metric_used_by_id IN NUMBER)
3500 IS
3501 TYPE t_procedures IS TABLE OF ams.ams_metrics_all_b.function_name%TYPE;
3502
3503 l_function_list t_procedures;
3504 l_function_name ams.ams_metrics_all_b.function_name%TYPE;
3505
3506 CURSOR c_all_procedures (p_arc_act_metric_used_by VARCHAR2,
3507 p_act_metric_used_by_id NUMBER)
3508 IS
3509 SELECT DISTINCT function_name
3510 FROM ams_metrics_all_b
3511 WHERE metric_id IN (SELECT metric_id
3512 FROM ams_act_metrics_all
3513 WHERE arc_act_metric_used_by = p_arc_act_metric_used_by
3514 AND act_metric_used_by_id = p_act_metric_used_by_id)
3515 AND function_type = 'N';
3516 BEGIN
3517
3518 OPEN c_all_procedures (p_arc_act_metric_used_by, p_act_metric_used_by_id);
3519 FETCH c_all_procedures BULK COLLECT INTO l_function_list;
3520 CLOSE c_all_procedures;
3521
3522 IF l_function_list.COUNT > 0 THEN
3523 FOR l_index IN l_function_list.FIRST..l_function_list.LAST
3524 LOOP
3525 l_function_name := l_function_list(l_index);
3526
3527 Exec_Procedure (
3528 p_arc_act_metric_used_by => p_arc_act_metric_used_by,
3529 p_act_metric_used_by_id => p_act_metric_used_by_id,
3530 p_function_name => l_function_name);
3531 END LOOP;
3532 END IF;
3533
3534 EXCEPTION
3535 WHEN OTHERS THEN
3536 RETURN;
3537 END Run_Object_Procedures;
3538
3539
3540 /** Commenting Out the package since Dialog Objects are obsolete.
3541 Bug#5029304
3542 **/
3543 /**
3544 -- Start of comments
3545 -- NAME
3546 -- Refresh_Metric
3547 --
3548 -- PURPOSE
3549 -- Re-calculate metrics for the components with in a dialog.
3550 --
3551 -- NOTES
3552 --
3553 -- HISTORY
3554 -- 03/28/2002 dmvincen Created
3555 --
3556 PROCEDURE Refresh_Components(
3557 p_api_version IN NUMBER,
3558 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_TRUE,
3559 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
3560 x_return_status OUT NOCOPY VARCHAR2,
3561 x_msg_count OUT NOCOPY NUMBER,
3562 x_msg_data OUT NOCOPY VARCHAR2,
3563 p_arc_act_metric_used_by IN VARCHAR2,
3564 p_act_metric_used_by_id IN NUMBER,
3565 p_refresh_function IN VARCHAR2 := Fnd_Api.G_TRUE
3566 )
3567 IS
3568 L_API_VERSION CONSTANT NUMBER := 1.0;
3569 L_API_NAME VARCHAR2(30) := 'Refresh_Component';
3570 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
3571
3572 CURSOR c_get_components(p_dialog_id NUMBER) IS
3573 SELECT flow_component_id obj_id,component_type_code obj_type
3574 FROM ams_dlg_flow_comps_b
3575 WHERE dialog_id= p_dialog_id;
3576
3577 l_object_id NUMBER;
3578 l_object_type VARCHAR2(30);
3579 BEGIN
3580
3581 --
3582 -- Output debug message.
3583 --
3584 IF AMS_DEBUG_HIGH_ON THEN
3585 Ams_Utility_Pvt.debug_message(l_full_name||': START');
3586 END IF;
3587 --
3588 -- Initialize message list if p_init_msg_list is set to TRUE.
3589 --
3590 IF Fnd_Api.To_Boolean (p_init_msg_list) THEN
3591 Fnd_Msg_Pub.Initialize;
3592 END IF;
3593 --
3594 -- Standard check for API version compatibility.
3595 --
3596 -- IF NOT Fnd_Api.Compatible_API_Call (L_API_VERSION,
3597 -- p_api_version,
3598 -- L_API_NAME,
3599 -- G_PKG_NAME)
3600 -- THEN
3601 -- RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3602 -- END IF;
3603 --
3604 -- Initialize API return status to success.
3605 --
3606 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3607 OPEN c_get_components(p_act_metric_used_by_id);
3608 LOOP
3609 FETCH c_get_components INTO l_object_id, l_object_type;
3610 EXIT WHEN c_get_components%NOTFOUND;
3611 IF AMS_DEBUG_MEDIUM_ON THEN
3612 Ams_Utility_Pvt.debug_message(l_full_name||': refresh component:'||
3613 l_object_type||'/'||l_object_id);
3614 END IF;
3615 Refresh_Metric(
3616 p_api_version => p_api_version,
3617 p_init_msg_list => Fnd_Api.G_FALSE,
3618 p_commit => Fnd_Api.G_FALSE,
3619 x_return_status => x_return_status,
3620 x_msg_count => x_msg_count,
3621 x_msg_data => x_msg_data,
3622 p_arc_act_metric_used_by => l_object_type,
3623 p_act_metric_used_by_id => l_object_id,
3624 p_refresh_function => Fnd_Api.G_FALSE);
3625 IF x_return_status = Fnd_Api.G_RET_STS_ERROR THEN
3626 CLOSE c_get_components;
3627 RAISE Fnd_Api.G_EXC_ERROR;
3628 ELSIF x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
3629 CLOSE c_get_components;
3630 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3631 END IF;
3632 END LOOP;
3633 CLOSE c_get_components;
3634
3635 --
3636 -- Standard check for commit request.
3637 --
3638 IF Fnd_Api.To_Boolean (p_commit) THEN
3639 COMMIT WORK;
3640 END IF;
3641 --
3642 -- Standard API to get message count, and if 1,
3643 -- set the message data OUT variable.
3644 --
3645 Fnd_Msg_Pub.Count_And_Get (
3646 p_count => x_msg_count,
3647 p_data => x_msg_data,
3648 p_encoded => Fnd_Api.G_FALSE
3649 );
3650 --
3651 -- Add success message to message list.
3652 --
3653 IF AMS_DEBUG_HIGH_ON THEN
3654 Ams_Utility_Pvt.debug_message(l_full_name ||': END');
3655 END IF;
3656 EXCEPTION
3657 WHEN Fnd_Api.G_EXC_ERROR THEN
3658 ROLLBACK TO Refresh_Metric_SavePoint;
3659 x_return_status := Fnd_Api.G_RET_STS_ERROR;
3660 Fnd_Msg_Pub.Count_And_Get (
3661 p_count => x_msg_count,
3662 p_data => x_msg_data,
3663 p_encoded => FND_API.G_FALSE
3664 );
3665 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
3666 ROLLBACK TO Refresh_Metric_SavePoint;
3667 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3668 Fnd_Msg_Pub.Count_And_Get (
3669 p_count => x_msg_count,
3670 p_data => x_msg_data,
3671 p_encoded => FND_API.G_FALSE
3672 );
3673 WHEN OTHERS THEN
3674 ROLLBACK TO Refresh_Metric_SavePoint;
3675 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3676 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
3677 Fnd_Msg_Pub.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
3678 END IF;
3679 Fnd_Msg_Pub.Count_And_Get (
3680 p_count => x_msg_count,
3681 p_data => x_msg_data,
3682 p_encoded => FND_API.G_FALSE
3683 );
3684 END Refresh_Components;
3685
3686 **/
3687
3688
3689 END Ams_Refreshmetric_Pvt ;
[Home] [Help]