[Home] [Help]
PACKAGE BODY: APPS.AMS_ACTMETRICFACT_PVT
Source
1 PACKAGE BODY Ams_Actmetricfact_Pvt AS
2 /* $Header: amsvamfb.pls 115.25 2002/11/16 01:09:36 mgudivak ship $ */
3
4 ---------------------------------------------------------------------------------------------------
5 --
6 -- NAME
7 -- Ams_Actmetricfact_Pvt
8 --
9 -- HISTORY
10 -- 20-Jun-1999 tdonohoe Created package.
11 -- 28-Jun 2000 tdonohoe Modified Check_ActMetricFact_Items to allow the same node to appear on a
12 -- hierarchy combined with a unique formula_id.
13 -- 31-Jul-2000 tdonohoe comment out code to fix bug 1362107.
14 -- 03-Apr-2001 yzhao add validate_fund_facts
15 --------------------------------------------------------------------------------------------------
16
17 --
18 -- Global variables and constants.
19
20 G_PKG_NAME CONSTANT VARCHAR2(30) := 'Ams_Actmetricfact_Pvt'; -- Name of the current package.
21 G_DEBUG_FLAG VARCHAR2(1) := 'N';
22
23
24 -- Start of comments
25 -- NAME
26 -- Default_ActMetricFact
27 --
28 --
29 -- PURPOSE
30 -- Defaults the Activty Metric Fact .
31 --
32 -- NOTES
33 --
34 -- HISTORY
35 -- 24-Apr-2000 tdonohoe Created.
36 --
37 -- End of comments
38
39 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
40 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
41 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
42
43 PROCEDURE Default_ActMetricFact(
44 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
45 p_act_metric_fact_rec IN act_metric_fact_rec_type,
46 p_validation_mode IN VARCHAR2 ,
47 x_complete_rec OUT NOCOPY act_metric_fact_rec_type,
48 x_return_status OUT NOCOPY VARCHAR2,
49 x_msg_count OUT NOCOPY NUMBER,
50 x_msg_data OUT NOCOPY VARCHAR2
51 )
52 IS
53
54 BEGIN
55 --
56 -- Initialize message list if p_init_msg_list is set to TRUE.
57 --
58 IF FND_API.To_Boolean (p_init_msg_list) THEN
59 FND_MSG_PUB.Initialize;
60 END IF;
61
62 --
63 -- Initialize API return status to success.
64 --
65 x_return_status := FND_API.G_RET_STS_SUCCESS;
66
67 x_complete_rec := p_act_metric_fact_rec;
68
69 -- Insert Mode
70 IF ((p_validation_mode = JTF_PLSQL_API.g_create) OR (p_validation_mode = JTF_PLSQL_API.g_update)) THEN
71
72 IF p_act_metric_fact_rec.trans_forecasted_value IS NULL THEN
73 x_complete_rec.trans_forecasted_value := 0;
74 END IF;
75
76 IF p_act_metric_fact_rec.base_quantity IS NULL THEN
77 x_complete_rec.base_quantity := 0;
78 END IF;
79
80 IF p_act_metric_fact_rec.functional_currency_code IS NULL THEN
81 x_complete_rec.functional_currency_code := 'NONE';
82 END IF;
83
84 IF p_act_metric_fact_rec.func_forecasted_value IS NULL THEN
85 x_complete_rec.func_forecasted_value := 0;
86 END IF;
87
88 IF p_act_metric_fact_rec.de_metric_id IS NULL THEN
89 x_complete_rec.de_metric_id := 0;
90 END IF;
91
92 IF p_act_metric_fact_rec.time_id1 IS NULL THEN
93 x_complete_rec.time_id1 := 0;
94 END IF;
95
96 IF p_act_metric_fact_rec.value_type IS NULL THEN
97 x_complete_rec.value_type := 'NUMERIC';
98 END IF;
99
100 END IF;
101
102 END Default_ActMetricFact ;
103
104
105 -- Start of comments
106 -- API Name Init_ActMetricFact_Rec
107 -- Type Private
108 -- Function This Process initialize Activity Metric Fact record
109 -- Parameters
110 -- OUT NOCOPY x_fact_rec OUT NOCOPY act_metric_rec_fact_type
111 -- History
112 -- 05/30/2002 created by Ying Zhao
113 -- End of comments
114
115 PROCEDURE Init_ActMetricFact_Rec(
116 x_fact_rec OUT NOCOPY act_metric_fact_rec_type
117 )
118 IS
119 BEGIN
120 x_fact_rec.activity_metric_fact_id := fnd_api.g_miss_num;
121 x_fact_rec.last_update_date := fnd_api.g_miss_date;
122 x_fact_rec.last_updated_by := fnd_api.g_miss_num;
123 x_fact_rec.creation_date := fnd_api.g_miss_date;
124 x_fact_rec.created_by := fnd_api.g_miss_num;
125 x_fact_rec.last_update_login := fnd_api.g_miss_num;
126 x_fact_rec.object_version_number := fnd_api.g_miss_num;
127 x_fact_rec.act_metric_used_by_id := fnd_api.g_miss_num;
128 x_fact_rec.arc_act_metric_used_by := fnd_api.g_miss_char;
129 x_fact_rec.value_type := fnd_api.g_miss_char;
130 x_fact_rec.activity_metric_id := fnd_api.g_miss_num;
131 x_fact_rec.activity_geo_area_id := fnd_api.g_miss_num;
132 x_fact_rec.activity_product_id := fnd_api.g_miss_num;
133 x_fact_rec.transaction_currency_code := fnd_api.g_miss_char;
134 x_fact_rec.trans_forecasted_value := fnd_api.g_miss_num;
135 x_fact_rec.base_quantity := fnd_api.g_miss_num;
136 x_fact_rec.functional_currency_code := fnd_api.g_miss_char;
137 x_fact_rec.func_forecasted_value := fnd_api.g_miss_num;
138 x_fact_rec.org_id := fnd_api.g_miss_num;
139 x_fact_rec.de_metric_id := fnd_api.g_miss_num;
140 x_fact_rec.de_geographic_area_id := fnd_api.g_miss_num;
141 x_fact_rec.de_geographic_area_type := fnd_api.g_miss_char;
142 x_fact_rec.de_inventory_item_id := fnd_api.g_miss_num;
143 x_fact_rec.de_inventory_item_org_id := fnd_api.g_miss_num;
144 x_fact_rec.time_id1 := fnd_api.g_miss_num;
145 x_fact_rec.time_id2 := fnd_api.g_miss_num;
146 x_fact_rec.time_id3 := fnd_api.g_miss_num;
147 x_fact_rec.time_id4 := fnd_api.g_miss_num;
148 x_fact_rec.time_id5 := fnd_api.g_miss_num;
149 x_fact_rec.time_id6 := fnd_api.g_miss_num;
150 x_fact_rec.time_id7 := fnd_api.g_miss_num;
151 x_fact_rec.time_id8 := fnd_api.g_miss_num;
152 x_fact_rec.time_id9 := fnd_api.g_miss_num;
153 x_fact_rec.time_id10 := fnd_api.g_miss_num;
154 x_fact_rec.time_id11 := fnd_api.g_miss_num;
155 x_fact_rec.time_id12 := fnd_api.g_miss_num;
156 x_fact_rec.time_id13 := fnd_api.g_miss_num;
157 x_fact_rec.time_id14 := fnd_api.g_miss_num;
158 x_fact_rec.time_id15 := fnd_api.g_miss_num;
159 x_fact_rec.time_id16 := fnd_api.g_miss_num;
160 x_fact_rec.time_id17 := fnd_api.g_miss_num;
161 x_fact_rec.time_id18 := fnd_api.g_miss_num;
162 x_fact_rec.time_id19 := fnd_api.g_miss_num;
163 x_fact_rec.time_id20 := fnd_api.g_miss_num;
164 x_fact_rec.time_id21 := fnd_api.g_miss_num;
165 x_fact_rec.time_id22 := fnd_api.g_miss_num;
166 x_fact_rec.time_id23 := fnd_api.g_miss_num;
167 x_fact_rec.time_id24 := fnd_api.g_miss_num;
168 x_fact_rec.time_id25 := fnd_api.g_miss_num;
169 x_fact_rec.time_id26 := fnd_api.g_miss_num;
170 x_fact_rec.time_id27 := fnd_api.g_miss_num;
171 x_fact_rec.time_id28 := fnd_api.g_miss_num;
172 x_fact_rec.time_id29 := fnd_api.g_miss_num;
173 x_fact_rec.time_id30 := fnd_api.g_miss_num;
174 x_fact_rec.time_id31 := fnd_api.g_miss_num;
175 x_fact_rec.time_id32 := fnd_api.g_miss_num;
176 x_fact_rec.time_id33 := fnd_api.g_miss_num;
177 x_fact_rec.time_id34 := fnd_api.g_miss_num;
178 x_fact_rec.time_id35 := fnd_api.g_miss_num;
179 x_fact_rec.time_id36 := fnd_api.g_miss_num;
180 x_fact_rec.time_id37 := fnd_api.g_miss_num;
181 x_fact_rec.time_id38 := fnd_api.g_miss_num;
182 x_fact_rec.time_id39 := fnd_api.g_miss_num;
183 x_fact_rec.time_id40 := fnd_api.g_miss_num;
184 x_fact_rec.time_id41 := fnd_api.g_miss_num;
185 x_fact_rec.time_id42 := fnd_api.g_miss_num;
186 x_fact_rec.time_id43 := fnd_api.g_miss_num;
187 x_fact_rec.time_id44 := fnd_api.g_miss_num;
188 x_fact_rec.time_id45 := fnd_api.g_miss_num;
189 x_fact_rec.time_id46 := fnd_api.g_miss_num;
190 x_fact_rec.time_id47 := fnd_api.g_miss_num;
191 x_fact_rec.time_id48 := fnd_api.g_miss_num;
192 x_fact_rec.time_id49 := fnd_api.g_miss_num;
193 x_fact_rec.time_id50 := fnd_api.g_miss_num;
194 x_fact_rec.time_id51 := fnd_api.g_miss_num;
195 x_fact_rec.time_id52 := fnd_api.g_miss_num;
196 x_fact_rec.time_id53 := fnd_api.g_miss_num;
197 x_fact_rec.hierarchy_id := fnd_api.g_miss_num;
198 x_fact_rec.node_id := fnd_api.g_miss_num;
199 x_fact_rec.level_depth := fnd_api.g_miss_num;
200 x_fact_rec.formula_id := fnd_api.g_miss_num;
201 x_fact_rec.from_date := fnd_api.g_miss_date;
202 x_fact_rec.to_date := fnd_api.g_miss_date;
203 x_fact_rec.fact_value := fnd_api.g_miss_num;
204 x_fact_rec.fact_percent := fnd_api.g_miss_num;
205 x_fact_rec.root_fact_id := fnd_api.g_miss_num;
206 x_fact_rec.previous_fact_id := fnd_api.g_miss_num;
207 x_fact_rec.fact_type := fnd_api.g_miss_char;
208 x_fact_rec.fact_reference := fnd_api.g_miss_char;
209 x_fact_rec.forward_buy_quantity := fnd_api.g_miss_num;
210 x_fact_rec.status_code := fnd_api.g_miss_char;
211 x_fact_rec.hierarchy_type := fnd_api.g_miss_char;
212 x_fact_rec.approval_date := fnd_api.g_miss_date;
213 x_fact_rec.recommend_total_amount := fnd_api.g_miss_num;
214 x_fact_rec.recommend_hb_amount := fnd_api.g_miss_num;
215 x_fact_rec.request_total_amount := fnd_api.g_miss_num;
216 x_fact_rec.request_hb_amount := fnd_api.g_miss_num;
217 x_fact_rec.actual_total_amount := fnd_api.g_miss_num;
218 x_fact_rec.actual_hb_amount := fnd_api.g_miss_num;
219 x_fact_rec.base_total_pct := fnd_api.g_miss_num;
220 x_fact_rec.base_hb_pct := fnd_api.g_miss_num;
221 END Init_ActMetricFact_Rec;
222
223
224 -- Start of comments
225 -- NAME
226 -- Validate_FUND_Facts
227 --
228 -- PURPOSE
229 -- Validate Activity Metric Fact for budget allocation.
230 -- For each node in the hierarchy:
231 -- Sum(child allocation amount) <= this node's allocation amount - holdback amount
232 -- since I'm traversing the hierarchy, it's not necessary to do the following check any more:
233 -- Sum(this node and its sibling's allocation amount) <= parent allocation amount - holdback amount
234
235 -- can not use ozf_fund_alloc_tree_v since the view has no data for create mode
236 --
237 -- NOTES
238 --
239 -- HISTORY
240 -- 26-Mar-2001 yzhao Created.
241 --
242 -- End of comments--
243 PROCEDURE Validate_FUND_Facts(
244 p_api_version IN NUMBER,
245 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
246 p_validation_level IN NUMBER := FND_API.G_Valid_Level_Full,
247 p_act_metric_id IN NUMBER,
248 x_return_status OUT NOCOPY VARCHAR2,
249 x_msg_count OUT NOCOPY NUMBER,
250 x_msg_data OUT NOCOPY VARCHAR2
251 )
252 IS
253 l_avail_amount NUMBER := 0;
254 l_allocation_amount NUMBER :=0;
255 l_holdback_amount NUMBER :=0;
256 l_child_sum_amount NUMBER := 0;
257 l_node_value VARCHAR2(2000) := null;
258 l_hierarchy_id NUMBER := 0;
259 l_node_id NUMBER := 0;
260 l_level_depth NUMBER := 0;
261 l_formula_id NUMBER := 0;
262 l_top_level boolean := true;
263
264 CURSOR c_fundfact_getfacts IS
265 SELECT fact.hierarchy_id, fact.node_id, fact.level_depth, fact.fact_value
266 ,formula.formula_id, formula.formula_type
267 FROM ams_act_metric_facts_all fact, ams_act_metric_formulas formula
268 WHERE fact.activity_metric_id = p_act_metric_id
269 AND fact.formula_id = formula.formula_id
270 ORDER BY fact.level_depth, fact.node_id, formula.formula_type asc;
271
272 -- get the fund's allocation amount
273 CURSOR c_fundfact_getfundamt IS
274 SELECT func_actual_value
278 -- get a node's node_value, parent node id
275 FROM ams_act_metrics_all
276 WHERE activity_metric_id = p_act_metric_id;
277
279 CURSOR c_fundfact_getnodeinfo(p_hierarchy_id NUMBER, p_node_id NUMBER) IS
280 SELECT node_value
281 FROM ams_terr_v
282 WHERE hierarchy_id = p_hierarchy_id
283 AND node_id = p_node_id;
284
285 -- get allocation amount summary of this node's children
286 CURSOR c_fundfact_getchildsum(p_hierarchy_id NUMBER, p_node_id NUMBER,
287 p_level_depth NUMBER, p_formula_id NUMBER) IS
288 SELECT NVL(SUM(fact_value), 0)
289 FROM ams_act_metric_facts_all fact
290 WHERE activity_metric_id = p_act_metric_id
291 AND hierarchy_id = p_hierarchy_id
292 AND EXISTS
293 (SELECT 1 FROM ams_act_metric_formulas formula
294 WHERE formula.formula_id = fact.formula_id
295 AND formula.formula_type = 'ALLOCATION'
296 AND formula.level_depth= p_level_depth + 1
297 AND formula.parent_formula_id = p_formula_id
298 )
299 AND EXISTS
300 (SELECT 1 FROM ams_terr_v terr
301 WHERE terr.hierarchy_id = p_hierarchy_id
302 AND terr.parent_id = p_node_id
303 AND terr.node_id = fact.node_id);
304
305 BEGIN
306 SAVEPOINT Validate_FUND_Facts;
307 IF (AMS_DEBUG_HIGH_ON) THEN
308
309 AMS_Utility_PVT.debug_message('Validate_FundFact_Fund: start');
310 END IF;
311
312 IF FND_API.To_Boolean (p_init_msg_list) THEN
313 FND_MSG_PUB.Initialize;
314 END IF;
315
316 x_return_status := FND_API.G_RET_STS_SUCCESS;
317 l_top_level := true;
318
319 FOR factrec IN c_fundfact_getfacts LOOP
320 IF factrec.formula_type = 'ALLOCATION' THEN
321 l_allocation_amount := factrec.fact_value;
322 l_hierarchy_id := factrec.hierarchy_id;
323 l_node_id := factrec.node_id;
324 l_level_depth := factrec.level_depth;
325 l_formula_id := factrec.formula_id;
326 IF l_top_level = true THEN
327 -- top level: check against root budget
328 OPEN c_fundfact_getfundamt;
329 FETCH c_fundfact_getfundamt INTO l_avail_amount;
330 CLOSE c_fundfact_getfundamt;
331 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_Utility_PVT.debug_message(' Top level budget available amount: ' || l_avail_amount); END IF;
332 IF l_allocation_amount > l_avail_amount THEN
333 -- top level allocation amount can not exceed fund's available amount
334 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
335 FND_MESSAGE.Set_Name ('AMS', 'AMS_FUND_INV_CHILD_AMT');
336 FND_MESSAGE.Set_Token('SUMAMT', l_allocation_amount);
337 FND_MESSAGE.Set_Token('NODEVALUE', 'FUND');
338 FND_MESSAGE.Set_Token('PAMT', l_avail_amount);
339 FND_MSG_PUB.Add;
340 END IF;
341 x_return_status := FND_API.G_RET_STS_ERROR;
342 RAISE FND_API.G_EXC_ERROR;
343 END IF;
344 l_top_level := false;
345 END IF; -- IF l_top_level = true
346 ELSE -- formula is 'HOLDBACK'
347 l_avail_amount := l_allocation_amount - factrec.fact_value;
348
349 -- check the node's children sum. Check here so both allocation amt and holdback amt are available
350 OPEN c_fundfact_getchildsum(l_hierarchy_id, l_node_id, l_level_depth, l_formula_id);
351 FETCH c_fundfact_getchildsum INTO l_child_sum_amount;
352 CLOSE c_fundfact_getchildsum;
353
354 IF l_child_sum_amount > l_avail_amount THEN
355 -- sum of this node's children's allocation amount can not exceed this node's available amount
356 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
357 OPEN c_fundfact_getnodeinfo(factrec.hierarchy_id, factrec.node_id);
358 FETCH c_fundfact_getnodeinfo INTO l_node_value;
359 CLOSE c_fundfact_getnodeinfo;
360 FND_MESSAGE.Set_Name ('AMS', 'AMS_FUND_INV_CHILD_AMT');
361 FND_MESSAGE.Set_Token('SUMAMT', l_child_sum_amount);
362 FND_MESSAGE.Set_Token('NODEVALUE', l_node_value);
363 FND_MESSAGE.Set_Token('PAMT', l_avail_amount);
364 FND_MSG_PUB.Add;
365 END IF;
366 x_return_status := FND_API.G_RET_STS_ERROR;
367 RAISE FND_API.G_EXC_ERROR;
368 END IF;
369 END IF;
370 END LOOP;
371
372 IF (AMS_DEBUG_HIGH_ON) THEN
373
374
375
376 AMS_Utility_PVT.debug_message('Validate_FundFact_FUND: end');
377
378 END IF;
379
380 EXCEPTION
381 WHEN FND_API.G_EXC_ERROR THEN
382 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_Utility_PVT.debug_message('Validate_FUND_Facts: ' || substr(sqlerrm, 1, 100)); END IF;
383 ROLLBACK TO Validate_FUND_Facts;
384 x_return_status := FND_API.G_RET_STS_ERROR;
385 FND_MSG_PUB.Count_And_Get (
386 p_encoded => fnd_api.g_false,
387 p_count => x_msg_count,
388 p_data => x_msg_data
389 );
390 WHEN OTHERS THEN
391 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_Utility_PVT.debug_message('Validate_FUND_Facts: ' || substr(sqlerrm, 1, 100)); END IF;
392 ROLLBACK TO Validate_FUND_Facts;
393 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
394 FND_MSG_PUB.Count_And_Get (
395 p_encoded => fnd_api.g_false,
396 p_count => x_msg_count,
397 p_data => x_msg_data
401
398 );
399 END Validate_FUND_Facts;
400
402 -- Start of comments
403 -- NAME
404 -- Create_ActMetricFact
405 --
406 --
407 -- PURPOSE
408 -- Creates a result entry for the Activity Metric.
409
410 --
411 -- NOTES
412 --
413 -- HISTORY
414 -- 18-Apr-2000 tdonohoe@us Created.
415 --
416 -- End of comments
417
418 PROCEDURE Create_ActMetricFact (
419 p_api_version IN NUMBER,
420 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
421 p_commit IN VARCHAR2 := FND_API.G_FALSE,
422 p_validation_level IN NUMBER := FND_API.G_Valid_Level_Full,
423
424 x_return_status OUT NOCOPY VARCHAR2,
425 x_msg_count OUT NOCOPY NUMBER,
426 x_msg_data OUT NOCOPY VARCHAR2,
427
428 p_act_metric_fact_rec IN act_metric_fact_rec_type,
429 x_activity_metric_fact_id OUT NOCOPY NUMBER
430 )
431 IS
432 --
433 -- Standard API information constants.
434 --
435 L_API_VERSION CONSTANT NUMBER := 1.0;
436 L_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_ACTMETRICFACT';
437 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
438
439
440 l_return_status VARCHAR2(1); -- Return value from procedures.
441 l_act_metric_fact_rec act_metric_fact_rec_type := p_act_metric_fact_rec;
442 l_act_metric_fact_count NUMBER ;
443
444 l_sql_err_msg varchar2(4000);
445
446 CURSOR c_act_metric_fact_count(l_act_metric_fact_id IN NUMBER) IS
447 SELECT count(1)
448 FROM ams_act_metric_facts_all
449 WHERE activity_metric_fact_id = l_act_metric_fact_id;
450
451 CURSOR c_act_metric_fact_id IS
452 SELECT ams_act_metric_facts_all_s.NEXTVAL
453 FROM dual;
454
455 BEGIN
456 --
457 -- Initialize savepoint.
458 --
459
460 SAVEPOINT Create_ActMetricFact_Pvt;
461
462 IF (AMS_DEBUG_HIGH_ON) THEN
463
464
465
466 AMS_Utility_PVT.Debug_Message(l_full_name||': start');
467
468 END IF;
469
470 --
471 -- Initialize message list if p_init_msg_list is set to TRUE.
472 --
473 IF FND_API.To_Boolean (p_init_msg_list) THEN
474 FND_MSG_PUB.Initialize;
475 END IF;
476
477 --
478 -- Standard check for API version compatibility.
479 --
480 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
481 p_api_version,
482 L_API_NAME,
483 G_PKG_NAME)
484 THEN
485 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
486 END IF;
487
488 --
489 -- Initialize API return status to success.
490 --
491 x_return_status := FND_API.G_RET_STS_SUCCESS;
492
493 --
494 -- Begin API Body.
495 --
496
497
498
499 Default_ActMetricFact
500 ( p_init_msg_list => p_init_msg_list,
501 p_act_metric_fact_rec => p_act_metric_fact_rec,
502 p_validation_mode => JTF_PLSQL_API.g_create,
503 x_complete_rec => l_act_metric_fact_rec,
504 x_return_status => l_return_status,
505 x_msg_count => x_msg_count,
506 x_msg_data => x_msg_data ) ;
507
508 -- If any errors happen abort API.
509 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
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;
514
515
516
517
518 --
519 -- Validate the record before inserting.
520 --
521
522
523 IF l_act_metric_fact_rec.activity_metric_fact_id IS NULL THEN
524 LOOP
525 --
526 -- Set the value for the PK.
527 OPEN c_act_metric_fact_id;
528 FETCH c_act_metric_fact_id INTO l_act_metric_fact_rec.activity_metric_fact_id;
529 CLOSE c_act_metric_fact_id;
530
531 OPEN c_act_metric_fact_count(l_act_metric_fact_rec.activity_metric_fact_id);
532 FETCH c_act_metric_fact_count INTO l_act_metric_fact_count ;
533 CLOSE c_act_metric_fact_count ;
534
535 EXIT WHEN l_act_metric_fact_count = 0 ;
536 END LOOP ;
537 END IF;
538
539
540
541
542 Validate_ActMetFact (
543 p_api_version => l_api_version,
544 p_init_msg_list => p_init_msg_list,
545 p_validation_level => p_validation_level,
546 x_msg_count => x_msg_count,
547 x_msg_data => x_msg_data,
548 x_return_status => l_return_status,
549 p_act_metric_fact_rec => l_act_metric_fact_rec
550 );
551
552 -- If any errors happen abort API.
553 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
554 RAISE FND_API.G_EXC_ERROR;
555 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
556 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
557 END IF;
558
559
560
561 --
562 -- Debug message.
563 --
564 IF (AMS_DEBUG_HIGH_ON) THEN
565
566 AMS_Utility_PVT.debug_message(l_full_name ||': insert');
567 END IF;
568
569
570
571 --
572 -- Insert into the base table.
573 --
574
575
579 last_updated_by,
576 Insert into ams_act_metric_facts_all (
577 activity_metric_fact_id,
578 last_update_date,
580 creation_date,
581 created_by,
582 last_update_login,
583 object_version_number,
584 act_metric_used_by_id,
585 arc_act_metric_used_by,
586 value_type ,
587 activity_metric_id ,
588 activity_geo_area_id ,
589 activity_product_id ,
590 transaction_currency_code,
591 trans_forecasted_value ,
592 base_quantity ,
593 functional_currency_code ,
594 func_forecasted_value ,
595 org_id ,
596 de_metric_id ,
597 de_geographic_area_id ,
598 de_geographic_area_type ,
599 de_inventory_item_id ,
600 de_inventory_item_org_id ,
601 time_id1 ,
602 time_id2 ,
603 time_id3 ,
604 time_id4 ,
605 time_id5 ,
606 time_id6 ,
607 time_id7 ,
608 time_id8 ,
609 time_id9 ,
610 time_id10 ,
611 time_id11 ,
612 time_id12 ,
613 time_id13 ,
614 time_id14 ,
615 time_id15 ,
616 time_id16 ,
617 time_id17 ,
618 time_id18 ,
619 time_id19 ,
620 time_id20 ,
621 time_id21 ,
622 time_id22 ,
623 time_id23 ,
624 time_id24 ,
625 time_id25 ,
626 time_id26 ,
627 time_id27 ,
628 time_id28 ,
629 time_id29 ,
630 time_id30 ,
631 time_id31 ,
632 time_id32 ,
633 time_id33 ,
634 time_id34 ,
635 time_id35 ,
636 time_id36 ,
637 time_id37 ,
638 time_id38 ,
639 time_id39 ,
640 time_id40 ,
641 time_id41 ,
642 time_id42 ,
643 time_id43 ,
644 time_id44 ,
645 time_id45 ,
646 time_id46 ,
647 time_id47 ,
648 time_id48 ,
649 time_id49 ,
650 time_id50 ,
651 time_id51 ,
652 time_id52 ,
653 time_id53 ,
654 hierarchy_id ,
655 node_id ,
656 level_depth ,
657 formula_id ,
658 from_date ,
659 to_date ,
660 fact_value ,
661 fact_percent ,
662 root_fact_id ,
663 previous_fact_id ,
664 fact_type ,
665 fact_reference ,
666 forward_buy_quantity ,
667 /* 05/21/2002 yzhao: add 11 new columns for top-down bottom-up budgeting */
668 status_code ,
669 hierarchy_type ,
670 approval_date ,
671 recommend_total_amount ,
672 recommend_hb_amount ,
673 request_total_amount ,
674 request_hb_amount ,
675 actual_total_amount ,
676 actual_hb_amount ,
677 base_total_pct ,
678 base_hb_pct
679 /* 05/21/2002 yzhao: add ends */
680 )
681 VALUES ( l_act_metric_fact_rec.activity_metric_fact_id,
682 SYSDATE,
683 FND_GLOBAL.User_ID,
684 SYSDATE,
685 FND_GLOBAL.User_ID,
686 FND_GLOBAL.Conc_Login_ID,
687 1, --OBJECT_VERSION_NUMBER
688 l_act_metric_fact_rec.act_metric_used_by_id,
689 l_act_metric_fact_rec.arc_act_metric_used_by,
690 l_act_metric_fact_rec.value_type ,
691 l_act_metric_fact_rec.activity_metric_id ,
692 l_act_metric_fact_rec.activity_geo_area_id ,
693 l_act_metric_fact_rec.activity_product_id ,
694 l_act_metric_fact_rec.transaction_currency_code,
695 l_act_metric_fact_rec.trans_forecasted_value ,
696 l_act_metric_fact_rec.base_quantity ,
697 l_act_metric_fact_rec.functional_currency_code ,
698 l_act_metric_fact_rec.func_forecasted_value ,
699 TO_NUMBER (SUBSTRB (USERENV ('CLIENT_INFO'), 1, 10)) , -- org_id
700 l_act_metric_fact_rec.de_metric_id ,
704 l_act_metric_fact_rec.de_inventory_item_org_id ,
701 l_act_metric_fact_rec.de_geographic_area_id ,
702 l_act_metric_fact_rec.de_geographic_area_type ,
703 l_act_metric_fact_rec.de_inventory_item_id ,
705 l_act_metric_fact_rec.time_id1 ,
706 l_act_metric_fact_rec.time_id2 ,
707 l_act_metric_fact_rec.time_id3 ,
708 l_act_metric_fact_rec.time_id4 ,
709 l_act_metric_fact_rec.time_id5 ,
710 l_act_metric_fact_rec.time_id6 ,
711 l_act_metric_fact_rec.time_id7 ,
712 l_act_metric_fact_rec.time_id8 ,
713 l_act_metric_fact_rec.time_id9 ,
714 l_act_metric_fact_rec.time_id10 ,
715 l_act_metric_fact_rec.time_id11 ,
716 l_act_metric_fact_rec.time_id12 ,
717 l_act_metric_fact_rec.time_id13 ,
718 l_act_metric_fact_rec.time_id14 ,
719 l_act_metric_fact_rec.time_id15 ,
720 l_act_metric_fact_rec.time_id16 ,
721 l_act_metric_fact_rec.time_id17 ,
722 l_act_metric_fact_rec.time_id18 ,
723 l_act_metric_fact_rec.time_id19 ,
724 l_act_metric_fact_rec.time_id20 ,
725 l_act_metric_fact_rec.time_id21 ,
726 l_act_metric_fact_rec.time_id22 ,
727 l_act_metric_fact_rec.time_id23 ,
728 l_act_metric_fact_rec.time_id24 ,
729 l_act_metric_fact_rec.time_id25 ,
730 l_act_metric_fact_rec.time_id26 ,
731 l_act_metric_fact_rec.time_id27 ,
732 l_act_metric_fact_rec.time_id28 ,
733 l_act_metric_fact_rec.time_id29 ,
734 l_act_metric_fact_rec.time_id30 ,
735 l_act_metric_fact_rec.time_id31 ,
736 l_act_metric_fact_rec.time_id32 ,
737 l_act_metric_fact_rec.time_id33 ,
738 l_act_metric_fact_rec.time_id34 ,
739 l_act_metric_fact_rec.time_id35 ,
740 l_act_metric_fact_rec.time_id36 ,
741 l_act_metric_fact_rec.time_id37 ,
742 l_act_metric_fact_rec.time_id38 ,
743 l_act_metric_fact_rec.time_id39 ,
744 l_act_metric_fact_rec.time_id40 ,
745 l_act_metric_fact_rec.time_id41 ,
746 l_act_metric_fact_rec.time_id42 ,
747 l_act_metric_fact_rec.time_id43 ,
748 l_act_metric_fact_rec.time_id44 ,
749 l_act_metric_fact_rec.time_id45 ,
750 l_act_metric_fact_rec.time_id46 ,
751 l_act_metric_fact_rec.time_id47 ,
752 l_act_metric_fact_rec.time_id48 ,
753 l_act_metric_fact_rec.time_id49 ,
754 l_act_metric_fact_rec.time_id50 ,
755 l_act_metric_fact_rec.time_id51 ,
756 l_act_metric_fact_rec.time_id52 ,
757 l_act_metric_fact_rec.time_id53 ,
758 l_act_metric_fact_rec.hierarchy_id ,
759 l_act_metric_fact_rec.node_id ,
760 l_act_metric_fact_rec.level_depth ,
761 l_act_metric_fact_rec.formula_id ,
762 l_act_metric_fact_rec.from_date ,
763 l_act_metric_fact_rec.to_date ,
764 l_act_metric_fact_rec.fact_value ,
765 l_act_metric_fact_rec.fact_percent ,
766 l_act_metric_fact_rec.root_fact_id ,
767 l_act_metric_fact_rec.previous_fact_id ,
768 l_act_metric_fact_rec.fact_type ,
769 l_act_metric_fact_rec.fact_reference ,
770 l_act_metric_fact_rec.forward_buy_quantity ,
771 /* 05/21/2002 yzhao: add 11 new columns for top-down bottom-up budgeting */
772 l_act_metric_fact_rec.status_code ,
773 l_act_metric_fact_rec.hierarchy_type ,
774 l_act_metric_fact_rec.approval_date ,
775 l_act_metric_fact_rec.recommend_total_amount ,
776 l_act_metric_fact_rec.recommend_hb_amount ,
777 l_act_metric_fact_rec.request_total_amount ,
778 l_act_metric_fact_rec.request_hb_amount ,
779 l_act_metric_fact_rec.actual_total_amount ,
780 l_act_metric_fact_rec.actual_hb_amount ,
781 l_act_metric_fact_rec.base_total_pct ,
782 l_act_metric_fact_rec.base_hb_pct
783 /* 05/21/2002 yzhao: add ends */
784 );
785
786 -- If any errors happen abort API.
787 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
788 RAISE FND_API.G_EXC_ERROR;
789 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
790 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
791 END IF;
792
793
794
795
796 -- finish
797
798 --
799 -- Set OUT NOCOPY value.
800 --
801 x_activity_metric_fact_id := l_act_metric_fact_rec.activity_metric_fact_id;
802
803 --
804 -- End API Body.
808 -- Standard check for commit request.
805 --
806
807 --
809 --
810 IF FND_API.To_Boolean (p_commit) THEN
811 COMMIT WORK;
812 END IF;
813
814 --
815 -- Standard API to get message count, and if 1,
816 -- set the message data OUT NOCOPY variable.
817 --
818 FND_MSG_PUB.Count_And_Get (
819 p_count => x_msg_count,
820 p_data => x_msg_data,
821 p_encoded => FND_API.G_FALSE
822 );
823
824 --
825 -- Add success message to message list.
826 --
827 IF (AMS_DEBUG_HIGH_ON) THEN
828
829 AMS_Utility_PVT.debug_message(l_full_name ||': end Success');
830 END IF;
831
832
833
834
835 EXCEPTION
836 WHEN FND_API.G_EXC_ERROR THEN
837
838
839 ROLLBACK TO Create_ActMetricFact_Pvt;
840 x_return_status := FND_API.G_RET_STS_ERROR;
841 FND_MSG_PUB.Count_And_Get (
842 p_count => x_msg_count,
843 p_data => x_msg_data
844 );
845 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
846
847
848
849 ROLLBACK TO Create_ActMetricFact_Pvt;
850 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
851 FND_MSG_PUB.Count_And_Get (
852 p_count => x_msg_count,
853 p_data => x_msg_data
854 );
855 WHEN OTHERS THEN
856
857
858 ROLLBACK TO Create_ActMetricFact_Pvt;
859 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
860 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
861 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
862 END IF;
863 FND_MSG_PUB.Count_And_Get (
864 p_count => x_msg_count,
865 p_data => x_msg_data
866 );
867 END Create_ActMetricFact;
868
869
870 -- Start of comments
871 -- NAME
872 -- Update_ActMetricFact
873 --
874 -- PURPOSE
875 -- Updates an entry in the AMS_ACT_METRIC_FACTS_ALL table for
876 -- a given activity_metric record.
877 --
878 -- NOTES
879 --
880 -- HISTORY
881 -- 18-Apr-2000 tdonohoe Created.
882 --
883 -- End of comments
884
885 PROCEDURE Update_ActMetricFact (
886 p_api_version IN NUMBER,
887 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
888 p_commit IN VARCHAR2 := FND_API.G_FALSE,
889 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
890
891 x_return_status OUT NOCOPY VARCHAR2,
892 x_msg_count OUT NOCOPY NUMBER,
893 x_msg_data OUT NOCOPY VARCHAR2,
894
895 p_act_metric_fact_rec IN act_metric_fact_rec_type
896 )
897 IS
898 L_API_VERSION CONSTANT NUMBER := 1.0;
899 L_API_NAME CONSTANT VARCHAR2(30) := 'UPDATE_ACTMETRICFACT';
900 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
901
902
903 l_return_status VARCHAR2(1);
904 l_act_metric_fact_rec act_metric_fact_rec_type := p_act_metric_fact_rec;
905 l_temp_act_metric_fact_rec act_metric_fact_rec_type ;
906 BEGIN
907
908 --
909 -- Initialize savepoint.
910 --
911 SAVEPOINT Update_ActMetricFact_Pvt;
912
913 --
914 -- Output debug message.
915 --
916 IF (AMS_DEBUG_HIGH_ON) THEN
917
918 AMS_Utility_PVT.debug_message(l_full_name||': start');
919 END IF;
920
921 --
922 -- Initialize message list if p_init_msg_list is set to TRUE.
923 --
924 IF FND_API.To_Boolean (p_init_msg_list) THEN
925 FND_MSG_PUB.Initialize;
926 END IF;
927
928 --
929 -- Standard check for API version compatibility.
930 --
931 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
932 p_api_version,
933 L_API_NAME,
934 G_PKG_NAME)
935 THEN
936 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
937 END IF;
938
939 --
940 -- Initialize API return status to success.
941 --
942 x_return_status := FND_API.G_RET_STS_SUCCESS;
943
944 --
945 -- Begin API Body
946 --
947 -- Debug Message
948
949
950 Default_ActMetricFact
951 ( p_init_msg_list => p_init_msg_list,
952 p_act_metric_fact_rec => p_act_metric_fact_rec,
953 p_validation_mode => JTF_PLSQL_API.G_UPDATE,
954 x_complete_rec => l_act_metric_fact_rec,
955 x_return_status => l_return_status,
956 x_msg_count => x_msg_count,
957 x_msg_data => x_msg_data ) ;
958 -- dbms_output.put_line(l_full_name || ' default_actmetricfact returns ' || l_return_status);
959 -- If any errors happen abort API.
960 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
961 RAISE FND_API.G_EXC_ERROR;
962 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
963 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
964 END IF;
965
966
967 IF (AMS_DEBUG_HIGH_ON) THEN
968
969
970
971
972
973 AMS_Utility_PVT.debug_message(l_full_name ||': validate');
974
975
976 END IF;
977
978 -- yzhao: 06/11/2002 complete record before validation so missed values can be filled in
979 -- replace g_miss_char/num/date with current column values
980
984 l_temp_act_metric_fact_rec := l_act_metric_fact_rec;
981 -- mgudivak: November Fifteenth.
982 -- Added NOCOPY for the out variable. Hence in and out cannot have the same name.
983
985
986 Complete_ActMetFact_Rec(p_act_metric_fact_rec => l_temp_act_metric_fact_rec,
987 x_complete_fact_rec => l_act_metric_fact_rec);
988
989
990 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
991 Validate_ActMetFact_Items(
992 p_act_metric_fact_rec => l_act_metric_fact_rec,
993 p_validation_mode => JTF_PLSQL_API.g_update,
994 x_return_status => l_return_status
995 );
996 -- dbms_output.put_line(l_full_name || ' validate_items returns ' || l_return_status);
997 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
998 RAISE FND_API.g_exc_unexpected_error;
999 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1000 RAISE FND_API.g_exc_error;
1001 END IF;
1002 END IF;
1003
1004
1005 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
1006 Validate_ActMetFact_Rec(
1007 p_act_metric_fact_rec => p_act_metric_fact_rec,
1008 p_complete_fact_rec => l_act_metric_fact_rec,
1009 x_return_status => l_return_status
1010 );
1011 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1012 RAISE FND_API.g_exc_unexpected_error;
1013 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1014 RAISE FND_API.g_exc_error;
1015 END IF;
1016
1017 END IF;
1018
1019
1020 IF (AMS_DEBUG_HIGH_ON) THEN
1021
1022
1023
1024
1025
1026 AMS_Utility_PVT.debug_message(l_full_name ||': Update Activity Metric Facts Table');
1027
1028
1029 END IF;
1030
1031
1032
1033 Update ams_act_metric_facts_all Set
1034 object_version_number = object_version_number + 1,
1035 last_update_date = SYSDATE,
1036 last_updated_by = NVL(fnd_global.user_id, -1),
1037 last_update_login = NVL(fnd_global.conc_login_id, -1),
1038 act_metric_used_by_id = l_act_metric_fact_rec.act_metric_used_by_id,
1039 arc_act_metric_used_by = l_act_metric_fact_rec.arc_act_metric_used_by,
1040 value_type = l_act_metric_fact_rec.value_type,
1041 activity_metric_id = l_act_metric_fact_rec.activity_metric_id,
1042 activity_geo_area_id = l_act_metric_fact_rec.activity_geo_area_id,
1043 activity_product_id = l_act_metric_fact_rec.activity_product_id,
1044 transaction_currency_code = l_act_metric_fact_rec.transaction_currency_code,
1045 trans_forecasted_value = l_act_metric_fact_rec.trans_forecasted_value,
1046 base_quantity = l_act_metric_fact_rec.base_quantity,
1047 functional_currency_code = l_act_metric_fact_rec.functional_currency_code,
1048 func_forecasted_value = l_act_metric_fact_rec.func_forecasted_value,
1049 org_id = l_act_metric_fact_rec.org_id,
1050 de_metric_id = l_act_metric_fact_rec.de_metric_id,
1051 de_geographic_area_id = l_act_metric_fact_rec.de_geographic_area_id,
1052 de_geographic_area_type = l_act_metric_fact_rec.de_geographic_area_type,
1053 de_inventory_item_id = l_act_metric_fact_rec.de_inventory_item_id,
1054 de_inventory_item_org_id = l_act_metric_fact_rec.de_inventory_item_org_id,
1055 time_id1 = l_act_metric_fact_rec.time_id1,
1056 time_id2 = l_act_metric_fact_rec.time_id2,
1057 time_id3 = l_act_metric_fact_rec.time_id3,
1058 time_id4 = l_act_metric_fact_rec.time_id4,
1059 time_id5 = l_act_metric_fact_rec.time_id5,
1060 time_id6 = l_act_metric_fact_rec.time_id6,
1061 time_id7 = l_act_metric_fact_rec.time_id7,
1062 time_id8 = l_act_metric_fact_rec.time_id8,
1063 time_id9 = l_act_metric_fact_rec.time_id9,
1064 time_id10 = l_act_metric_fact_rec.time_id10,
1065 time_id11 = l_act_metric_fact_rec.time_id11,
1066 time_id12 = l_act_metric_fact_rec.time_id12,
1067 time_id13 = l_act_metric_fact_rec.time_id13,
1068 time_id14 = l_act_metric_fact_rec.time_id14,
1069 time_id15 = l_act_metric_fact_rec.time_id15,
1070 time_id16 = l_act_metric_fact_rec.time_id16,
1071 time_id17 = l_act_metric_fact_rec.time_id17,
1072 time_id18 = l_act_metric_fact_rec.time_id18,
1073 time_id19 = l_act_metric_fact_rec.time_id19,
1074 time_id20 = l_act_metric_fact_rec.time_id20,
1075 time_id21 = l_act_metric_fact_rec.time_id21,
1076 time_id22 = l_act_metric_fact_rec.time_id22,
1077 time_id23 = l_act_metric_fact_rec.time_id23,
1078 time_id24 = l_act_metric_fact_rec.time_id24,
1079 time_id25 = l_act_metric_fact_rec.time_id25,
1080 time_id26 = l_act_metric_fact_rec.time_id26,
1081 time_id27 = l_act_metric_fact_rec.time_id27,
1082 time_id28 = l_act_metric_fact_rec.time_id28,
1083 time_id29 = l_act_metric_fact_rec.time_id29,
1084 time_id30 = l_act_metric_fact_rec.time_id30,
1088 time_id34 = l_act_metric_fact_rec.time_id34,
1085 time_id31 = l_act_metric_fact_rec.time_id31,
1086 time_id32 = l_act_metric_fact_rec.time_id32,
1087 time_id33 = l_act_metric_fact_rec.time_id33,
1089 time_id35 = l_act_metric_fact_rec.time_id35,
1090 time_id36 = l_act_metric_fact_rec.time_id36,
1091 time_id37 = l_act_metric_fact_rec.time_id37,
1092 time_id38 = l_act_metric_fact_rec.time_id38,
1093 time_id39 = l_act_metric_fact_rec.time_id39,
1094 time_id40 = l_act_metric_fact_rec.time_id40,
1095 time_id41 = l_act_metric_fact_rec.time_id41,
1096 time_id42 = l_act_metric_fact_rec.time_id42,
1097 time_id43 = l_act_metric_fact_rec.time_id43,
1098 time_id44 = l_act_metric_fact_rec.time_id44,
1099 time_id45 = l_act_metric_fact_rec.time_id45,
1100 time_id46 = l_act_metric_fact_rec.time_id46,
1101 time_id47 = l_act_metric_fact_rec.time_id47,
1102 time_id48 = l_act_metric_fact_rec.time_id48,
1103 time_id49 = l_act_metric_fact_rec.time_id49,
1104 time_id50 = l_act_metric_fact_rec.time_id50,
1105 time_id51 = l_act_metric_fact_rec.time_id51,
1106 time_id52 = l_act_metric_fact_rec.time_id52,
1107 time_id53 = l_act_metric_fact_rec.time_id53,
1108 hierarchy_id = l_act_metric_fact_rec.hierarchy_id,
1109 node_id = l_act_metric_fact_rec.node_id,
1110 level_depth = l_act_metric_fact_rec.level_depth,
1111 formula_id = l_act_metric_fact_rec.formula_id,
1112 from_date = l_act_metric_fact_rec.from_date,
1113 to_date = l_act_metric_fact_rec.to_date,
1114 fact_value = l_act_metric_fact_rec.fact_value,
1115 fact_percent = l_act_metric_fact_rec.fact_percent,
1116 root_fact_id = l_act_metric_fact_rec.root_fact_id,
1117 previous_fact_id = l_act_metric_fact_rec.previous_fact_id,
1118 fact_type = l_act_metric_fact_rec.fact_type,
1119 fact_reference = l_act_metric_fact_rec.fact_reference,
1120 forward_buy_quantity = l_act_metric_fact_rec.forward_buy_quantity,
1121 /* 05/21/2002 yzhao: add 11 new columns for top-down bottom-up budgeting */
1122 status_code = l_act_metric_fact_rec.status_code,
1123 hierarchy_type = l_act_metric_fact_rec.hierarchy_type,
1124 approval_date = l_act_metric_fact_rec.approval_date,
1125 recommend_total_amount = l_act_metric_fact_rec.recommend_total_amount,
1126 recommend_hb_amount = l_act_metric_fact_rec.recommend_hb_amount,
1127 request_total_amount = l_act_metric_fact_rec.request_total_amount,
1128 request_hb_amount = l_act_metric_fact_rec.request_hb_amount,
1129 actual_total_amount = l_act_metric_fact_rec.actual_total_amount,
1130 actual_hb_amount = l_act_metric_fact_rec.actual_hb_amount,
1131 base_total_pct = l_act_metric_fact_rec.base_total_pct,
1132 base_hb_pct = l_act_metric_fact_rec.base_hb_pct
1133 /* 05/21/2002 yzhao: add ends */
1134 Where activity_metric_fact_id = l_act_metric_fact_rec.activity_metric_fact_id;
1135
1136 IF (SQL%NOTFOUND)
1137 THEN
1138 --
1139 -- Add error message to API message list.
1140 --
1141 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1142 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1143 FND_MSG_PUB.add;
1144 END IF;
1145 RAISE FND_API.g_exc_error;
1146 END IF;
1147
1148
1149 --
1150 -- End API Body
1151 --
1152
1153 IF FND_API.to_boolean(p_commit) THEN
1154 COMMIT;
1155 END IF;
1156
1157 --
1158 -- Standard API to get message count, and if 1,
1159 -- set the message data OUT NOCOPY variable.
1160 --
1161 FND_MSG_PUB.Count_And_Get (
1162 p_count => x_msg_count,
1163 p_data => x_msg_data,
1164 p_encoded => FND_API.G_FALSE
1165 );
1166
1167 --
1168 -- Debug message.
1169 --
1170 IF (AMS_DEBUG_HIGH_ON) THEN
1171
1172 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1173 END IF;
1174
1175
1176 EXCEPTION
1177 WHEN FND_API.G_EXC_ERROR THEN
1178
1179
1180
1181 ROLLBACK TO Update_ActMetricFact_pvt;
1182 x_return_status := FND_API.G_RET_STS_ERROR;
1183 FND_MSG_PUB.Count_And_Get (
1184 p_count => x_msg_count,
1185 p_data => x_msg_data
1186 );
1187 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1188
1189
1190
1191 ROLLBACK TO Update_ActMetricFact_pvt;
1192 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1193 FND_MSG_PUB.Count_And_Get (
1194 p_count => x_msg_count,
1195 p_data => x_msg_data
1196 );
1197 WHEN OTHERS THEN
1198
1199
1200
1201 ROLLBACK TO Update_ActMetricFact_pvt;
1202 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1206 FND_MSG_PUB.Count_And_Get (
1203 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1204 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
1205 END IF;
1207 p_count => x_msg_count,
1208 p_data => x_msg_data
1209 );
1210 END Update_ActMetricFact;
1211
1212
1213 -- Start of comments
1214 -- NAME
1215 -- Lock_ActMetricFact
1216 --
1217 -- PURPOSE
1218 -- Lock the given row in AMS_ACT_METRIC_FACTS table.
1219 --
1220 -- NOTES
1221 --
1222 -- HISTORY
1223 -- 19-Apr-2000 tdonohoe Created.
1224 --
1225 -- End of comments
1226
1227 PROCEDURE Lock_ActMetricFact (
1228 p_api_version IN NUMBER,
1229 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1230
1231 x_return_status OUT NOCOPY VARCHAR2,
1232 x_msg_count OUT NOCOPY NUMBER,
1233 x_msg_data OUT NOCOPY VARCHAR2,
1234
1235 p_activity_metric_fact_id IN NUMBER,
1236 p_object_version_number IN NUMBER
1237 )
1238 IS
1239 L_API_VERSION CONSTANT NUMBER := 1.0;
1240 L_API_NAME CONSTANT VARCHAR2(30) := 'LOCK_ACTMETRICFACT';
1241 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1242
1243 l_activity_metric_fact_id NUMBER;
1244
1245 CURSOR c_act_metric_fact_info IS
1246 SELECT activity_metric_fact_id
1247 FROM ams_act_metric_facts_all
1248 WHERE activity_metric_fact_id = p_activity_metric_fact_id
1249 AND object_version_number = p_object_version_number
1250 FOR UPDATE OF activity_metric_fact_id NOWAIT;
1251
1252 BEGIN
1253 --
1254 -- Output debug message.
1255 --
1256 IF (AMS_DEBUG_HIGH_ON) THEN
1257
1258 AMS_Utility_PVT.debug_message(l_full_name||': start');
1259 END IF;
1260
1261 --
1262 -- Initialize message list if p_init_msg_list is set to TRUE.
1263 --
1264 IF FND_API.To_Boolean (p_init_msg_list) THEN
1265 FND_MSG_PUB.Initialize;
1266 END IF;
1267
1268 --
1269 -- Standard check for API version compatibility.
1270 --
1271 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
1272 p_api_version,
1273 L_API_NAME,
1274 G_PKG_NAME)
1275 THEN
1276 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1277 END IF;
1278
1279 --
1280 -- Initialize API return status to success.
1281 --
1282 x_return_status := FND_API.G_RET_STS_SUCCESS;
1283
1284 --
1285 -- Begin API Body
1286 --
1287 IF (AMS_DEBUG_HIGH_ON) THEN
1288
1289 AMS_Utility_PVT.debug_message(l_full_name||': lock');
1290 END IF;
1291
1292 OPEN c_act_metric_fact_info;
1293 FETCH c_act_metric_fact_info INTO l_activity_metric_fact_id;
1294 IF (c_act_metric_fact_info%NOTFOUND)
1295 THEN
1296 CLOSE c_act_metric_fact_info;
1297 -- Error, check the msg level and added an error message to the
1298 -- API message list
1299 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1300 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1301 FND_MSG_PUB.add;
1302 END IF;
1303 RAISE FND_API.g_exc_error;
1304 END IF;
1305 CLOSE c_act_metric_fact_info;
1306
1307
1308 --
1309 -- Standard API to get message count, and if 1,
1310 -- set the message data OUT NOCOPY variable.
1311 --
1312 FND_MSG_PUB.Count_And_Get (
1313 p_count => x_msg_count,
1314 p_data => x_msg_data,
1315 p_encoded => FND_API.G_FALSE
1316 );
1317
1318 --
1319 -- Debug message.
1320 --
1321 IF (AMS_DEBUG_HIGH_ON) THEN
1322
1323 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1324 END IF;
1325
1326
1327 EXCEPTION
1328 WHEN FND_API.G_EXC_ERROR THEN
1329 x_return_status := FND_API.G_RET_STS_ERROR;
1330 FND_MSG_PUB.Count_And_Get (
1331 p_count => x_msg_count,
1332 p_data => x_msg_data
1333 );
1334 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1335 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1336 FND_MSG_PUB.Count_And_Get (
1337 p_count => x_msg_count,
1338 p_data => x_msg_data
1339 );
1340 WHEN AMS_Utility_PVT.RESOURCE_LOCKED THEN
1341 x_return_status := FND_API.G_RET_STS_ERROR ;
1342
1343 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1344 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
1345 FND_MSG_PUB.add;
1346 END IF;
1347
1348 FND_MSG_PUB.Count_And_Get (
1349 p_count => x_msg_count,
1350 p_data => x_msg_data,
1351 p_encoded => FND_API.G_FALSE
1352 );
1353 WHEN OTHERS THEN
1354 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1355 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1356 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
1357 END IF;
1358 FND_MSG_PUB.Count_And_Get (
1359 p_count => x_msg_count,
1360 p_data => x_msg_data,
1361 p_encoded => FND_API.G_FALSE
1362 );
1363 END Lock_ActMetricFact;
1364
1365
1366 --
1367 -- NAME
1368 -- Complete_MetricFact_Rec
1369 --
1370 -- PURPOSE
1371 -- Returns the Initialized Activity Metric Fact Record
1372 --
1376 -- 21-Apr-2000 tdonohoe Created.
1373 -- NOTES
1374 --
1375 -- HISTORY
1377 --
1378 PROCEDURE Complete_ActMetFact_Rec(
1379 p_act_metric_fact_rec IN act_metric_fact_rec_type,
1380 x_complete_fact_rec OUT NOCOPY act_metric_fact_rec_type
1381 )
1382 IS
1383 CURSOR c_act_metric_fact IS
1384 SELECT *
1385 FROM ams_act_metric_facts_all
1386 WHERE activity_metric_fact_id = p_act_metric_fact_rec.activity_metric_fact_id;
1387
1388 l_act_metric_fact_rec c_act_metric_fact%ROWTYPE;
1389 BEGIN
1390
1391 x_complete_fact_rec := p_act_metric_fact_rec;
1392
1393 OPEN c_act_metric_fact;
1394 FETCH c_act_metric_fact INTO l_act_metric_fact_rec;
1395 IF c_act_metric_fact%NOTFOUND THEN
1396 CLOSE c_act_metric_fact;
1397 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1398 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1399 FND_MSG_PUB.add;
1400 END IF;
1401 RAISE FND_API.g_exc_error;
1402 END IF;
1403 CLOSE c_act_metric_fact;
1404
1405
1406 IF p_act_metric_fact_rec.activity_metric_fact_id = FND_API.G_MISS_NUM THEN
1407 x_complete_fact_rec.activity_metric_fact_id := l_act_metric_fact_rec.activity_metric_fact_id;
1408 END IF;
1409
1410 IF p_act_metric_fact_rec.act_metric_used_by_id = FND_API.G_MISS_NUM THEN
1411 x_complete_fact_rec.act_metric_used_by_id := l_act_metric_fact_rec.act_metric_used_by_id;
1412 END IF;
1413
1414 IF p_act_metric_fact_rec.arc_act_metric_used_by = FND_API.G_MISS_CHAR THEN
1415 x_complete_fact_rec.arc_act_metric_used_by := l_act_metric_fact_rec.arc_act_metric_used_by;
1416 END IF;
1417
1418 IF p_act_metric_fact_rec.value_type = FND_API.G_MISS_CHAR THEN
1419 x_complete_fact_rec.value_type := l_act_metric_fact_rec.value_type;
1420 END IF;
1421
1422 IF p_act_metric_fact_rec.activity_metric_id = FND_API.G_MISS_NUM THEN
1423 x_complete_fact_rec.activity_metric_id := l_act_metric_fact_rec.activity_metric_id;
1424 END IF;
1425
1426 IF p_act_metric_fact_rec.activity_geo_area_id = FND_API.G_MISS_NUM THEN
1427 x_complete_fact_rec.activity_geo_area_id := l_act_metric_fact_rec.activity_geo_area_id;
1428 END IF;
1429
1430 IF p_act_metric_fact_rec.activity_product_id = FND_API.G_MISS_NUM THEN
1431 x_complete_fact_rec.activity_product_id := l_act_metric_fact_rec.activity_product_id;
1432 END IF;
1433
1434 IF p_act_metric_fact_rec.transaction_currency_code = FND_API.G_MISS_CHAR THEN
1435 x_complete_fact_rec.transaction_currency_code := l_act_metric_fact_rec.transaction_currency_code;
1436 END IF;
1437
1438 IF p_act_metric_fact_rec.trans_forecasted_value = FND_API.G_MISS_NUM THEN
1439 x_complete_fact_rec.trans_forecasted_value := l_act_metric_fact_rec.trans_forecasted_value;
1440 END IF;
1441
1442 IF p_act_metric_fact_rec.base_quantity = FND_API.G_MISS_NUM THEN
1443 x_complete_fact_rec.base_quantity := l_act_metric_fact_rec.base_quantity;
1444 END IF;
1445
1446 IF p_act_metric_fact_rec.functional_currency_code = FND_API.G_MISS_CHAR THEN
1447 x_complete_fact_rec.functional_currency_code := l_act_metric_fact_rec.functional_currency_code;
1448 END IF;
1449
1450 IF p_act_metric_fact_rec.func_forecasted_value = FND_API.G_MISS_NUM THEN
1451 x_complete_fact_rec.func_forecasted_value := l_act_metric_fact_rec.func_forecasted_value;
1452 END IF;
1453
1454 IF p_act_metric_fact_rec.org_id = FND_API.G_MISS_NUM THEN
1455 x_complete_fact_rec.org_id := l_act_metric_fact_rec.org_id;
1456 END IF;
1457
1458 IF p_act_metric_fact_rec.de_metric_id = FND_API.G_MISS_NUM THEN
1459 x_complete_fact_rec.de_metric_id := l_act_metric_fact_rec.de_metric_id;
1460 END IF;
1461
1462 IF p_act_metric_fact_rec.de_geographic_area_id = FND_API.G_MISS_NUM THEN
1463 x_complete_fact_rec.de_geographic_area_id := l_act_metric_fact_rec.de_geographic_area_id;
1464 END IF;
1465
1466 IF p_act_metric_fact_rec.de_geographic_area_type = FND_API.G_MISS_CHAR THEN
1467 x_complete_fact_rec.de_geographic_area_type := l_act_metric_fact_rec.de_geographic_area_type;
1468 END IF;
1469
1470 IF p_act_metric_fact_rec.de_inventory_item_id = FND_API.G_MISS_NUM THEN
1471 x_complete_fact_rec.de_inventory_item_id := l_act_metric_fact_rec.de_inventory_item_id;
1472 END IF;
1473
1474 IF p_act_metric_fact_rec.de_inventory_item_org_id = FND_API.G_MISS_NUM THEN
1475 x_complete_fact_rec.de_inventory_item_org_id := l_act_metric_fact_rec.de_inventory_item_org_id;
1476 END IF;
1477
1478 IF p_act_metric_fact_rec.time_id1 = FND_API.G_MISS_NUM THEN
1479 x_complete_fact_rec.time_id1 := l_act_metric_fact_rec.time_id1;
1480 END IF;
1481
1482 IF p_act_metric_fact_rec.time_id2 = FND_API.G_MISS_NUM THEN
1483 x_complete_fact_rec.time_id2 := l_act_metric_fact_rec.time_id2;
1484 END IF;
1485
1486 IF p_act_metric_fact_rec.time_id3 = FND_API.G_MISS_NUM THEN
1487 x_complete_fact_rec.time_id3 := l_act_metric_fact_rec.time_id3;
1488 END IF;
1489
1490
1491 IF p_act_metric_fact_rec.time_id4 = FND_API.G_MISS_NUM THEN
1492 x_complete_fact_rec.time_id4 := l_act_metric_fact_rec.time_id4;
1493 END IF;
1494
1495 IF p_act_metric_fact_rec.time_id5 = FND_API.G_MISS_NUM THEN
1496 x_complete_fact_rec.time_id5 := l_act_metric_fact_rec.time_id5;
1497 END IF;
1498
1499 IF p_act_metric_fact_rec.time_id6 = FND_API.G_MISS_NUM THEN
1500 x_complete_fact_rec.time_id6 := l_act_metric_fact_rec.time_id6;
1501 END IF;
1502
1503 IF p_act_metric_fact_rec.time_id7 = FND_API.G_MISS_NUM THEN
1504 x_complete_fact_rec.time_id7 := l_act_metric_fact_rec.time_id7;
1505 END IF;
1506
1507 IF p_act_metric_fact_rec.time_id8 = FND_API.G_MISS_NUM THEN
1511 IF p_act_metric_fact_rec.time_id9 = FND_API.G_MISS_NUM THEN
1508 x_complete_fact_rec.time_id8 := l_act_metric_fact_rec.time_id8;
1509 END IF;
1510
1512 x_complete_fact_rec.time_id9 := l_act_metric_fact_rec.time_id9;
1513 END IF;
1514
1515 IF p_act_metric_fact_rec.time_id10 = FND_API.G_MISS_NUM THEN
1516 x_complete_fact_rec.time_id10 := l_act_metric_fact_rec.time_id10;
1517 END IF;
1518
1519 IF p_act_metric_fact_rec.time_id11 = FND_API.G_MISS_NUM THEN
1520 x_complete_fact_rec.time_id11 := l_act_metric_fact_rec.time_id11;
1521 END IF;
1522
1523 IF p_act_metric_fact_rec.time_id12 = FND_API.G_MISS_NUM THEN
1524 x_complete_fact_rec.time_id12 := l_act_metric_fact_rec.time_id12;
1525 END IF;
1526
1527 IF p_act_metric_fact_rec.time_id13 = FND_API.G_MISS_NUM THEN
1528 x_complete_fact_rec.time_id13 := l_act_metric_fact_rec.time_id13;
1529 END IF;
1530
1531 IF p_act_metric_fact_rec.time_id14 = FND_API.G_MISS_NUM THEN
1532 x_complete_fact_rec.time_id14 := l_act_metric_fact_rec.time_id14;
1533 END IF;
1534
1535 IF p_act_metric_fact_rec.time_id15 = FND_API.G_MISS_NUM THEN
1536 x_complete_fact_rec.time_id15 := l_act_metric_fact_rec.time_id15;
1537 END IF;
1538
1539 IF p_act_metric_fact_rec.time_id16 = FND_API.G_MISS_NUM THEN
1540 x_complete_fact_rec.time_id16 := l_act_metric_fact_rec.time_id16;
1541 END IF;
1542
1543 IF p_act_metric_fact_rec.time_id17 = FND_API.G_MISS_NUM THEN
1544 x_complete_fact_rec.time_id17 := l_act_metric_fact_rec.time_id17;
1545 END IF;
1546
1547 IF p_act_metric_fact_rec.time_id18 = FND_API.G_MISS_NUM THEN
1548 x_complete_fact_rec.time_id18 := l_act_metric_fact_rec.time_id18;
1549 END IF;
1550
1551 IF p_act_metric_fact_rec.time_id19 = FND_API.G_MISS_NUM THEN
1552 x_complete_fact_rec.time_id19 := l_act_metric_fact_rec.time_id19;
1553 END IF;
1554
1555 IF p_act_metric_fact_rec.time_id20 = FND_API.G_MISS_NUM THEN
1556 x_complete_fact_rec.time_id20 := l_act_metric_fact_rec.time_id20;
1557 END IF;
1558
1559 IF p_act_metric_fact_rec.time_id21 = FND_API.G_MISS_NUM THEN
1560 x_complete_fact_rec.time_id21 := l_act_metric_fact_rec.time_id21;
1561 END IF;
1562
1563 IF p_act_metric_fact_rec.time_id22 = FND_API.G_MISS_NUM THEN
1564 x_complete_fact_rec.time_id22 := l_act_metric_fact_rec.time_id22;
1565 END IF;
1566
1567 IF p_act_metric_fact_rec.time_id23 = FND_API.G_MISS_NUM THEN
1568 x_complete_fact_rec.time_id23 := l_act_metric_fact_rec.time_id23;
1569 END IF;
1570
1571 IF p_act_metric_fact_rec.time_id24 = FND_API.G_MISS_NUM THEN
1572 x_complete_fact_rec.time_id24 := l_act_metric_fact_rec.time_id24;
1573 END IF;
1574
1575 IF p_act_metric_fact_rec.time_id25 = FND_API.G_MISS_NUM THEN
1576 x_complete_fact_rec.time_id25 := l_act_metric_fact_rec.time_id25;
1577 END IF;
1578
1579 IF p_act_metric_fact_rec.time_id26 = FND_API.G_MISS_NUM THEN
1580 x_complete_fact_rec.time_id26 := l_act_metric_fact_rec.time_id26;
1581 END IF;
1582
1583 IF p_act_metric_fact_rec.time_id27 = FND_API.G_MISS_NUM THEN
1584 x_complete_fact_rec.time_id27 := l_act_metric_fact_rec.time_id27;
1585 END IF;
1586
1587 IF p_act_metric_fact_rec.time_id28 = FND_API.G_MISS_NUM THEN
1588 x_complete_fact_rec.time_id28 := l_act_metric_fact_rec.time_id28;
1589 END IF;
1590
1591 IF p_act_metric_fact_rec.time_id29 = FND_API.G_MISS_NUM THEN
1592 x_complete_fact_rec.time_id29 := l_act_metric_fact_rec.time_id29;
1593 END IF;
1594
1595 IF p_act_metric_fact_rec.time_id30 = FND_API.G_MISS_NUM THEN
1596 x_complete_fact_rec.time_id30 := l_act_metric_fact_rec.time_id30;
1597 END IF;
1598
1599 IF p_act_metric_fact_rec.time_id31 = FND_API.G_MISS_NUM THEN
1600 x_complete_fact_rec.time_id31 := l_act_metric_fact_rec.time_id31;
1601 END IF;
1602
1603 IF p_act_metric_fact_rec.time_id32 = FND_API.G_MISS_NUM THEN
1604 x_complete_fact_rec.time_id32 := l_act_metric_fact_rec.time_id32;
1605 END IF;
1606
1607 IF p_act_metric_fact_rec.time_id33 = FND_API.G_MISS_NUM THEN
1608 x_complete_fact_rec.time_id33 := l_act_metric_fact_rec.time_id33;
1609 END IF;
1610
1611 IF p_act_metric_fact_rec.time_id34 = FND_API.G_MISS_NUM THEN
1612 x_complete_fact_rec.time_id34 := l_act_metric_fact_rec.time_id34;
1613 END IF;
1614
1615 IF p_act_metric_fact_rec.time_id35 = FND_API.G_MISS_NUM THEN
1616 x_complete_fact_rec.time_id35 := l_act_metric_fact_rec.time_id35;
1617 END IF;
1618
1619 IF p_act_metric_fact_rec.time_id36 = FND_API.G_MISS_NUM THEN
1620 x_complete_fact_rec.time_id36 := l_act_metric_fact_rec.time_id36;
1621 END IF;
1622
1623 IF p_act_metric_fact_rec.time_id37 = FND_API.G_MISS_NUM THEN
1624 x_complete_fact_rec.time_id37 := l_act_metric_fact_rec.time_id37;
1625 END IF;
1626
1627 IF p_act_metric_fact_rec.time_id38 = FND_API.G_MISS_NUM THEN
1628 x_complete_fact_rec.time_id38 := l_act_metric_fact_rec.time_id38;
1629 END IF;
1630
1631 IF p_act_metric_fact_rec.time_id39 = FND_API.G_MISS_NUM THEN
1632 x_complete_fact_rec.time_id39 := l_act_metric_fact_rec.time_id39;
1633 END IF;
1634
1635 IF p_act_metric_fact_rec.time_id40 = FND_API.G_MISS_NUM THEN
1636 x_complete_fact_rec.time_id40 := l_act_metric_fact_rec.time_id40;
1637 END IF;
1638
1639 IF p_act_metric_fact_rec.time_id41 = FND_API.G_MISS_NUM THEN
1640 x_complete_fact_rec.time_id41 := l_act_metric_fact_rec.time_id41;
1641 END IF;
1642
1643 IF p_act_metric_fact_rec.time_id42 = FND_API.G_MISS_NUM THEN
1644 x_complete_fact_rec.time_id42 := l_act_metric_fact_rec.time_id42;
1645 END IF;
1646
1650
1647 IF p_act_metric_fact_rec.time_id43 = FND_API.G_MISS_NUM THEN
1648 x_complete_fact_rec.time_id43 := l_act_metric_fact_rec.time_id43;
1649 END IF;
1651 IF p_act_metric_fact_rec.time_id44 = FND_API.G_MISS_NUM THEN
1652 x_complete_fact_rec.time_id44 := l_act_metric_fact_rec.time_id44;
1653 END IF;
1654
1655 IF p_act_metric_fact_rec.time_id45 = FND_API.G_MISS_NUM THEN
1656 x_complete_fact_rec.time_id45 := l_act_metric_fact_rec.time_id45;
1657 END IF;
1658
1659 IF p_act_metric_fact_rec.time_id46 = FND_API.G_MISS_NUM THEN
1660 x_complete_fact_rec.time_id46 := l_act_metric_fact_rec.time_id46;
1661 END IF;
1662
1663 IF p_act_metric_fact_rec.time_id47 = FND_API.G_MISS_NUM THEN
1664 x_complete_fact_rec.time_id47 := l_act_metric_fact_rec.time_id47;
1665 END IF;
1666
1667 IF p_act_metric_fact_rec.time_id48 = FND_API.G_MISS_NUM THEN
1668 x_complete_fact_rec.time_id48 := l_act_metric_fact_rec.time_id48;
1669 END IF;
1670
1671 IF p_act_metric_fact_rec.time_id49 = FND_API.G_MISS_NUM THEN
1672 x_complete_fact_rec.time_id49 := l_act_metric_fact_rec.time_id49;
1673 END IF;
1674
1675 IF p_act_metric_fact_rec.time_id50 = FND_API.G_MISS_NUM THEN
1676 x_complete_fact_rec.time_id50 := l_act_metric_fact_rec.time_id50;
1677 END IF;
1678
1679 IF p_act_metric_fact_rec.time_id51 = FND_API.G_MISS_NUM THEN
1680 x_complete_fact_rec.time_id51 := l_act_metric_fact_rec.time_id51;
1681 END IF;
1682
1683 IF p_act_metric_fact_rec.time_id52 = FND_API.G_MISS_NUM THEN
1684 x_complete_fact_rec.time_id52 := l_act_metric_fact_rec.time_id52;
1685 END IF;
1686
1687 IF p_act_metric_fact_rec.time_id53 = FND_API.G_MISS_NUM THEN
1688 x_complete_fact_rec.time_id53 := l_act_metric_fact_rec.time_id53;
1689 END IF;
1690
1691 IF p_act_metric_fact_rec.hierarchy_id = FND_API.G_MISS_NUM THEN
1692 x_complete_fact_rec.hierarchy_id := l_act_metric_fact_rec.hierarchy_id;
1693 END IF;
1694
1695 IF p_act_metric_fact_rec.node_id = FND_API.G_MISS_NUM THEN
1696 x_complete_fact_rec.node_id := l_act_metric_fact_rec.node_id;
1697 END IF;
1698
1699 IF p_act_metric_fact_rec.level_depth = FND_API.G_MISS_NUM THEN
1700 x_complete_fact_rec.level_depth := l_act_metric_fact_rec.level_depth;
1701 END IF;
1702
1703 IF p_act_metric_fact_rec.formula_id = FND_API.G_MISS_NUM THEN
1704 x_complete_fact_rec.formula_id := l_act_metric_fact_rec.formula_id;
1705 END IF;
1706
1707 IF p_act_metric_fact_rec.from_date = FND_API.G_MISS_DATE THEN
1708 x_complete_fact_rec.from_date := l_act_metric_fact_rec.from_date;
1709 END IF;
1710
1711 IF p_act_metric_fact_rec.to_date = FND_API.G_MISS_DATE THEN
1712 x_complete_fact_rec.to_date := l_act_metric_fact_rec.to_date;
1713 END IF;
1714
1715 IF p_act_metric_fact_rec.fact_value = FND_API.G_MISS_NUM THEN
1716 x_complete_fact_rec.fact_value := l_act_metric_fact_rec.fact_value;
1717 END IF;
1718
1719 IF p_act_metric_fact_rec.fact_percent = FND_API.G_MISS_NUM THEN
1720 x_complete_fact_rec.fact_percent := l_act_metric_fact_rec.fact_percent;
1721 END IF;
1722
1723 IF p_act_metric_fact_rec.root_fact_id = FND_API.G_MISS_NUM THEN
1724 x_complete_fact_rec.root_fact_id := l_act_metric_fact_rec.root_fact_id;
1725 END IF;
1726
1727 IF p_act_metric_fact_rec.previous_fact_id = FND_API.G_MISS_NUM THEN
1728 x_complete_fact_rec.previous_fact_id := l_act_metric_fact_rec.previous_fact_id;
1729 END IF;
1730
1731 IF p_act_metric_fact_rec.fact_type = FND_API.G_MISS_CHAR THEN
1732 x_complete_fact_rec.fact_type := l_act_metric_fact_rec.fact_type;
1733 END IF;
1734
1735 IF p_act_metric_fact_rec.fact_reference = FND_API.G_MISS_CHAR THEN
1736 x_complete_fact_rec.fact_reference := l_act_metric_fact_rec.fact_reference;
1737 END IF;
1738
1739 IF p_act_metric_fact_rec.forward_buy_quantity = FND_API.G_MISS_NUM THEN
1740 x_complete_fact_rec.forward_buy_quantity := l_act_metric_fact_rec.forward_buy_quantity;
1741 END IF;
1742
1743 /* 05/21/2002 yzhao: add 11 new columns for top-down bottom-up budgeting */
1744 IF p_act_metric_fact_rec.status_code = FND_API.G_MISS_CHAR THEN
1745 x_complete_fact_rec.status_code := l_act_metric_fact_rec.status_code;
1746 END IF;
1747
1748 IF p_act_metric_fact_rec.hierarchy_type = FND_API.G_MISS_CHAR THEN
1749 x_complete_fact_rec.hierarchy_type := l_act_metric_fact_rec.hierarchy_type;
1750 END IF;
1751
1752 IF p_act_metric_fact_rec.approval_date = FND_API.G_MISS_DATE THEN
1753 x_complete_fact_rec.approval_date := l_act_metric_fact_rec.approval_date;
1754 END IF;
1755
1756 IF p_act_metric_fact_rec.recommend_total_amount = FND_API.G_MISS_NUM THEN
1757 x_complete_fact_rec.recommend_total_amount := l_act_metric_fact_rec.recommend_total_amount;
1758 END IF;
1759
1760 IF p_act_metric_fact_rec.recommend_hb_amount = FND_API.G_MISS_NUM THEN
1761 x_complete_fact_rec.recommend_hb_amount := l_act_metric_fact_rec.recommend_hb_amount;
1762 END IF;
1763
1764 IF p_act_metric_fact_rec.request_total_amount = FND_API.G_MISS_NUM THEN
1765 x_complete_fact_rec.request_total_amount := l_act_metric_fact_rec.request_total_amount;
1766 END IF;
1767
1768 IF p_act_metric_fact_rec.request_hb_amount = FND_API.G_MISS_NUM THEN
1769 x_complete_fact_rec.request_hb_amount := l_act_metric_fact_rec.request_hb_amount;
1770 END IF;
1771
1772 IF p_act_metric_fact_rec.actual_total_amount = FND_API.G_MISS_NUM THEN
1773 x_complete_fact_rec.actual_total_amount := l_act_metric_fact_rec.actual_total_amount;
1774 END IF;
1775
1776 IF p_act_metric_fact_rec.actual_hb_amount = FND_API.G_MISS_NUM THEN
1780 IF p_act_metric_fact_rec.base_total_pct = FND_API.G_MISS_NUM THEN
1777 x_complete_fact_rec.actual_hb_amount := l_act_metric_fact_rec.actual_hb_amount;
1778 END IF;
1779
1781 x_complete_fact_rec.base_total_pct := l_act_metric_fact_rec.base_total_pct;
1782 END IF;
1783
1784 IF p_act_metric_fact_rec.base_hb_pct = FND_API.G_MISS_NUM THEN
1785 x_complete_fact_rec.base_hb_pct := l_act_metric_fact_rec.base_hb_pct;
1786 END IF;
1787 /* 05/21/2002 yzhao: add ends */
1788
1789 END Complete_ActMetFact_Rec ;
1790
1791
1792 -- Start of comments
1793 -- NAME
1794 -- Delete_ActMetricFact
1795 --
1796 -- PURPOSE
1797 -- Deletes an entry in the ams_act_metric_facts_all table.
1798 --
1799 -- NOTES
1800 --
1801 -- HISTORY
1802 -- 24-Apr-2000 tdonohoe Created.
1803 -- 25-Apr-2000 tdonohoe Modified, if the p_activity_metric_id is specified then
1804 -- all entries for this parameter are deleted.
1805 --
1806 -- End of comments
1807
1808 PROCEDURE Delete_ActMetricFact (
1809 p_api_version IN NUMBER,
1810 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1811 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1812
1813 x_return_status OUT NOCOPY VARCHAR2,
1814 x_msg_count OUT NOCOPY NUMBER,
1815 x_msg_data OUT NOCOPY VARCHAR2,
1816
1817 p_activity_metric_fact_id IN NUMBER,
1818 p_activity_metric_id IN NUMBER,
1819 p_object_version_number IN NUMBER
1820 )
1821 IS
1822 L_API_VERSION CONSTANT NUMBER := 1.0;
1823 L_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_ACTMETRICFACT';
1824 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1825
1826 l_return_status VARCHAR2(1);
1827
1828 BEGIN
1829 --
1830 -- Initialize savepoint.
1831 --
1832 SAVEPOINT Delete_ActMetricFact_pvt;
1833
1834 --
1835 -- Output debug message.
1836 --
1837 IF (AMS_DEBUG_HIGH_ON) THEN
1838
1839 AMS_Utility_PVT.debug_message(l_full_name||': start');
1840 END IF;
1841
1842 --
1843 -- Initialize message list if p_init_msg_list is set to TRUE.
1844 --
1845 IF FND_API.To_Boolean (p_init_msg_list) THEN
1846 FND_MSG_PUB.Initialize;
1847 END IF;
1848
1849 --
1850 -- Standard check for API version compatibility.
1851 --
1852 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
1853 p_api_version,
1854 L_API_NAME,
1855 G_PKG_NAME)
1856 THEN
1857 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1858 END IF;
1859
1860 --
1861 -- Initialize API return status to success.
1862 --
1863 x_return_status := FND_API.G_RET_STS_SUCCESS;
1864
1865 --
1866 -- Begin API Body.
1867 --
1868
1869 -- Debug message.
1870 IF (AMS_DEBUG_HIGH_ON) THEN
1871
1872 AMS_Utility_PVT.debug_message(l_full_name ||': delete with Validation');
1873 END IF;
1874
1875 IF(p_activity_metric_id IS NOT NULL) THEN
1876
1877 DELETE FROM ams_act_metric_facts_all
1878 WHERE activity_metric_id = p_activity_metric_id;
1879
1880 ELSIF (p_activity_metric_fact_id IS NOT NULL) THEN
1881
1882 DELETE FROM ams_act_metric_facts_all
1883 WHERE activity_metric_fact_id = p_activity_metric_fact_id
1884 AND object_version_number = p_object_version_number;
1885
1886 END IF;
1887
1888
1889 IF (SQL%NOTFOUND) THEN
1890 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1891 THEN
1892 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1893 FND_MSG_PUB.add;
1894 END IF;
1895 RAISE FND_API.g_exc_error;
1896 END IF;
1897
1898
1899 --
1900 -- End API Body.
1901 --
1902
1903 IF FND_API.To_Boolean (p_commit) THEN
1904 COMMIT WORK;
1905 END IF;
1906
1907 --
1908 -- Debug message.
1909 --
1910 IF (AMS_DEBUG_HIGH_ON) THEN
1911
1912 AMS_Utility_PVT.debug_message(l_full_name ||': End');
1913 END IF;
1914
1915
1916 --
1917 -- Standard API to get message count, and if 1,
1918 -- set the message data OUT NOCOPY variable.
1919 --
1920 FND_MSG_PUB.Count_And_Get (
1921 p_count => x_msg_count,
1922 p_data => x_msg_data,
1923 p_encoded => FND_API.G_FALSE
1924 );
1925
1926 EXCEPTION
1927 WHEN FND_API.G_EXC_ERROR THEN
1928 ROLLBACK TO Delete_ActMetricFact_pvt;
1929 x_return_status := FND_API.G_RET_STS_ERROR;
1930 FND_MSG_PUB.Count_And_Get (
1931 p_count => x_msg_count,
1932 p_data => x_msg_data
1933 );
1934 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1935 ROLLBACK TO Delete_ActMetricFact_pvt;
1936 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1937 FND_MSG_PUB.Count_And_Get (
1938 p_count => x_msg_count,
1939 p_data => x_msg_data
1940 );
1941 WHEN OTHERS THEN
1942 ROLLBACK TO Delete_ActMetricFact_pvt;
1943 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1944 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1945 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
1946 END IF;
1947 FND_MSG_PUB.Count_And_Get (
1951 END Delete_ActMetricFact;
1948 p_count => x_msg_count,
1949 p_data => x_msg_data
1950 );
1952
1953 -- Start of comments
1954 -- NAME
1955 -- Validate_ActMetFact
1956 --
1957 -- PURPOSE
1958 -- Validation API for Activity metric facts table.
1959 --
1960
1961 -- NOTES
1962 --
1963 -- HISTORY
1964 -- 24-Apr-2000 tdonohoe Created.
1965
1966 --
1967 -- End of comments
1968
1969 PROCEDURE Validate_ActMetFact (
1970 p_api_version IN NUMBER,
1971 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1972 p_validation_level IN NUMBER := FND_API.G_Valid_Level_Full,
1973
1974 x_return_status OUT NOCOPY VARCHAR2,
1975 x_msg_count OUT NOCOPY NUMBER,
1976 x_msg_data OUT NOCOPY VARCHAR2,
1977
1978 p_act_metric_fact_rec IN act_metric_fact_rec_type
1979 )
1980 IS
1981 L_API_VERSION CONSTANT NUMBER := 1.0;
1982 L_API_NAME CONSTANT VARCHAR2(30) := 'VALIDATE_ACTMETRICFACT';
1983 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1984
1985
1986 l_return_status VARCHAR2(1);
1987
1988 BEGIN
1989 --
1990 -- Output debug message.
1991 --
1992 IF (AMS_DEBUG_HIGH_ON) THEN
1993
1994 AMS_Utility_PVT.debug_message(l_full_name||': start');
1995 END IF;
1996
1997 --
1998 -- Initialize message list if p_init_msg_list is set to TRUE.
1999 --
2000 IF FND_API.To_Boolean (p_init_msg_list) THEN
2001 FND_MSG_PUB.Initialize;
2002 END IF;
2003
2004 --
2005 -- Standard check for API version compatibility.
2006 --
2007 IF NOT FND_API.Compatible_API_Call (L_API_VERSION,
2008 p_api_version,
2009 L_API_NAME,
2010 G_PKG_NAME)
2011 THEN
2012 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2013 END IF;
2014
2015 --
2016 -- Initialize API return status to success.
2017 --
2018 x_return_status := FND_API.G_RET_STS_SUCCESS;
2019
2020 --
2021 -- Begin API Body.
2022 --
2023
2024 IF (AMS_DEBUG_HIGH_ON) THEN
2025
2026
2027
2028 AMS_Utility_PVT.debug_message(l_full_name||': Validate items');
2029
2030 END IF;
2031
2032
2033
2034 -- Validate required items in the record.
2035 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
2036
2037 Validate_ActMetFact_Items(
2038 p_act_metric_fact_rec => p_act_metric_fact_rec,
2039 p_validation_mode => JTF_PLSQL_API.g_create,
2040 x_return_status => l_return_status
2041 );
2042
2043 -- If any errors happen abort API.
2044 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2045 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2046 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2047 RAISE FND_API.G_EXC_ERROR;
2048 END IF;
2049 END IF;
2050
2051 IF (AMS_DEBUG_HIGH_ON) THEN
2052
2053
2054
2055 AMS_Utility_PVT.debug_message(l_full_name||': check record');
2056
2057 END IF;
2058
2059
2060
2061
2062 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
2063 Validate_ActMetFact_Rec(
2064 p_act_metric_fact_rec => p_act_metric_fact_rec,
2065 p_complete_fact_rec => NULL,
2066 x_return_status => l_return_status
2067 );
2068
2069 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2070 RAISE FND_API.g_exc_unexpected_error;
2071 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
2072 RAISE FND_API.g_exc_error;
2073 END IF;
2074 END IF;
2075
2076
2077
2078 --
2079 -- End API Body.
2080 --
2081
2082 --
2083 -- Standard API to get message count, and if 1,
2084 -- set the message data OUT NOCOPY variable.
2085 --
2086 FND_MSG_PUB.Count_And_Get (
2087 p_count => x_msg_count,
2088 p_data => x_msg_data,
2089 p_encoded => FND_API.G_FALSE
2090 );
2091
2092
2093
2094 IF (AMS_DEBUG_HIGH_ON) THEN
2095
2096
2097
2098
2099
2100
2101
2102 AMS_Utility_PVT.debug_message(l_full_name ||': end');
2103
2104
2105
2106 END IF;
2107
2108
2109
2110 EXCEPTION
2111 WHEN FND_API.G_EXC_ERROR THEN
2112 x_return_status := FND_API.G_RET_STS_ERROR;
2113 FND_MSG_PUB.Count_And_Get (
2114 p_count => x_msg_count,
2115 p_data => x_msg_data
2116 );
2117 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2118 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2119 FND_MSG_PUB.Count_And_Get (
2120 p_count => x_msg_count,
2121 p_data => x_msg_data
2122 );
2123 WHEN OTHERS THEN
2124 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2125 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2126 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, L_API_NAME);
2127 END IF;
2128 FND_MSG_PUB.Count_And_Get (
2129 p_count => x_msg_count,
2130 p_data => x_msg_data
2131 );
2132 END Validate_ActMetFact;
2133
2134
2135 -- Start of comments.
2139 --
2136 --
2137 -- NAME
2138 -- Check_Req_ActMetricFact_Items
2140 -- PURPOSE
2141 -- Validate required metric fact items.
2142 --
2143 -- NOTES
2144 --
2145 -- HISTORY
2146 -- 24-Apr-2000 tdonohoe Created.
2147 --
2148 -- End of comments.
2149
2150 PROCEDURE Check_Req_ActMetricFact_Items (
2151 p_act_metric_fact_rec IN act_metric_fact_rec_type,
2152 x_return_status OUT NOCOPY VARCHAR2
2153 )
2154 IS
2155 BEGIN
2156 -- Initialize return status to success.
2157 x_return_status := FND_API.G_RET_STS_SUCCESS;
2158
2159 --ACT_METRIC_USED_BY_ID
2160
2161
2162
2163
2164 IF p_act_metric_fact_rec.act_metric_used_by_id IS NULL
2165 THEN
2166 -- missing required fields
2167 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2168 THEN -- MMSG
2169 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_MISSING_ARC_USED_FOR');
2170 FND_MSG_PUB.Add;
2171 END IF;
2172
2173 x_return_status := FND_API.G_RET_STS_ERROR;
2174
2175 -- If any error happens abort API.
2176 RETURN;
2177 END IF;
2178
2179 --ARC_ACT_METRIC_USED_BY
2180
2181
2182
2183 IF p_act_metric_fact_rec.arc_act_metric_used_by IS NULL
2184 THEN
2185 -- missing required fields
2186 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2187 THEN -- MMSG
2188 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_MISSING_ARC_USED_FOR');
2189 FND_MSG_PUB.Add;
2190 END IF;
2191
2192 x_return_status := FND_API.G_RET_STS_ERROR;
2193
2194 -- If any error happens abort API.
2195 RETURN;
2196 END IF;
2197
2198 --VALUE_TYPE
2199
2200
2201
2202 IF p_act_metric_fact_rec.value_type IS NULL
2203 THEN
2204 -- missing required fields
2205 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2206 THEN -- MMSG
2207 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_MISSING_VAL_TYPE');
2208 FND_MSG_PUB.Add;
2209 END IF;
2210
2211 x_return_status := FND_API.G_RET_STS_ERROR;
2212
2213 -- If any error happens abort API.
2214 RETURN;
2215 END IF;
2216
2217 --ACTIVITY_METRIC_ID
2218
2219
2220
2221
2222 IF p_act_metric_fact_rec.activity_metric_id IS NULL
2223 THEN
2224 -- missing required fields
2225 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2226 THEN -- MMSG
2227 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_MISSING_ACT_METRIC_ID');
2228 FND_MSG_PUB.Add;
2229 END IF;
2230
2231 x_return_status := FND_API.G_RET_STS_ERROR;
2232
2233 -- If any error happens abort API.
2234 RETURN;
2235 END IF;
2236
2237 --TRANS_FORECASTED_VALUE
2238
2239
2240
2241 IF p_act_metric_fact_rec.trans_forecasted_value IS NULL
2242 THEN
2243 -- missing required fields
2244 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2245 THEN -- MMSG
2246 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_MISSING_TRAN_FCST_VAL');
2247 FND_MSG_PUB.Add;
2248 END IF;
2249
2250 x_return_status := FND_API.G_RET_STS_ERROR;
2251
2252 -- If any error happens abort API.
2253 RETURN;
2254 END IF;
2255
2256
2257
2258
2259 --FUNCTIONAL_CURRENCY_CODE
2260 IF p_act_metric_fact_rec.functional_currency_code IS NULL
2261 THEN
2262 -- missing required fields
2263 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2264 THEN -- MMSG
2265 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_MISSING_FUNC_CUR_CODE');
2266 FND_MSG_PUB.Add;
2267 END IF;
2268
2269 x_return_status := FND_API.G_RET_STS_ERROR;
2270
2271 -- If any error happens abort API.
2272 RETURN;
2273 END IF;
2274
2275 --FUNC_FORECASTED_VALUE
2276
2277
2278
2279 IF p_act_metric_fact_rec.func_forecasted_value IS NULL
2280 THEN
2281 -- missing required fields
2282 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2283 THEN -- MMSG
2284 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_MISSING_FUNC_FCST_VAL');
2285 FND_MSG_PUB.Add;
2286 END IF;
2287
2288 x_return_status := FND_API.G_RET_STS_ERROR;
2289
2290 -- If any error happens abort API.
2291 RETURN;
2292 END IF;
2293
2294 --DE_METRIC_ID
2295
2296
2297
2298
2299 IF p_act_metric_fact_rec.de_metric_id IS NULL
2300 THEN
2301 -- missing required fields
2302 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2303 THEN -- MMSG
2304 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_MISSING_METRIC_ID');
2305 FND_MSG_PUB.Add;
2306 END IF;
2307
2308 x_return_status := FND_API.G_RET_STS_ERROR;
2309
2310 -- If any error happens abort API.
2311 RETURN;
2312 END IF;
2313
2314 --TIME_ID1
2315
2316
2317
2318
2319 IF p_act_metric_fact_rec.time_id1 IS NULL
2320 THEN
2321 -- missing required fields
2322 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2323 THEN -- MMSG
2324 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_MISSING_TIME_ID1');
2325 FND_MSG_PUB.Add;
2326 END IF;
2327
2328 x_return_status := FND_API.G_RET_STS_ERROR;
2329
2333
2330 -- If any error happens abort API.
2331 RETURN;
2332 END IF;
2334
2335
2336 -------------------------------------------------------------------------------------
2337 --When a Hierarchy Id is present then the node_id and fact_value fields are mandatory
2338 -------------------------------------------------------------------------------------
2339 IF p_act_metric_fact_rec.hierarchy_id IS NOT NULL AND p_act_metric_fact_rec.hierarchy_id <> FND_API.G_MISS_NUM
2340 THEN
2341
2342
2343
2344
2345
2346
2347 IF p_act_metric_fact_rec.node_id IS NULL
2348 THEN
2349
2350
2351
2352 -- missing required fields
2353 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2354 THEN -- MMSG
2355 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_MISSING_NODE_ID');
2356 FND_MSG_PUB.Add;
2357 END IF;
2358
2359 x_return_status := FND_API.G_RET_STS_ERROR;
2360
2361 -- If any error happens abort API.
2362 RETURN;
2363 END IF;
2364
2365
2366
2367
2368 IF p_act_metric_fact_rec.fact_value IS NULL
2369 THEN
2370 -- missing required fields
2371 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2372 THEN -- MMSG
2373 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_MISSING_FACT_VAL');
2374 FND_MSG_PUB.Add;
2375 END IF;
2376
2377 x_return_status := FND_API.G_RET_STS_ERROR;
2378
2379 -- If any error happens abort API.
2380 RETURN;
2381 END IF;
2382
2383 END IF;
2384
2385 EXCEPTION
2386 WHEN OTHERS THEN
2387 RAISE;
2388 END Check_Req_ActMetricFact_Items;
2389
2390
2391 --
2392 -- Start of comments.
2393 --
2394 -- NAME
2395 -- Check_ActMetricFact_UK_Items
2396 --
2397 -- PURPOSE
2398 -- Perform Uniqueness check for Activity metric facts.
2399 --
2400 -- NOTES
2401 --
2402 -- HISTORY
2403 -- 24-Apr-2000 tdonohoe Created.
2404 -- End of comments.
2405
2406
2407 PROCEDURE Check_ActMetricFact_UK_Items(
2408 p_act_metric_fact_rec IN act_metric_fact_rec_type,
2409 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
2410 x_return_status OUT NOCOPY VARCHAR2
2411 )
2412 IS
2413 l_where_clause VARCHAR2(2000); -- Used By Check_Uniqueness
2414 BEGIN
2415
2416 x_return_status := FND_API.g_ret_sts_success;
2417
2418 -- For Create_ActMetricFact, when activity_metric_fact_id is passed in, we need to
2419 -- check if this activity_metric_id is unique.
2420 IF p_validation_mode = JTF_PLSQL_API.g_create
2421 AND p_act_metric_fact_rec.activity_metric_fact_id IS NOT NULL
2422 THEN
2423
2424 l_where_clause := ' activity_metric_fact_id = '||p_act_metric_fact_rec.activity_metric_fact_id ;
2425
2426 IF AMS_Utility_PVT.Check_Uniqueness(
2427 p_table_name => 'ams_act_metric_facts_all',
2428 p_where_clause => l_where_clause
2429 ) = FND_API.g_false
2430 THEN
2431 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2432 THEN
2433 FND_MESSAGE.set_name('AMS', 'AMS_METR_FACT_DUP_ID');
2434 FND_MSG_PUB.add;
2435 END IF;
2436 x_return_status := FND_API.g_ret_sts_error;
2437 RETURN;
2438 END IF;
2439 END IF;
2440
2441
2442 END Check_ActMetricFact_Uk_Items;
2443
2444
2445 --
2446 -- Start of comments.
2447 --
2448 -- NAME
2449 -- Check_ActMetricFact_Items
2450 --
2451 -- PURPOSE
2452 -- Perform item level validation for Activity metric facts.
2453 --
2454 -- NOTES
2455 --
2456 -- HISTORY
2457 -- 24-Apr-2000 tdonohoe Created.
2458 -- 28-Jun 2000 tdonohoe Modified Check_ActMetricFact_Items to allow the same node to appear on a
2459 -- hierarchy combined with a unique formula_id.
2460 -- End of comments.
2461
2462 PROCEDURE Check_ActMetricFact_Items (
2463 p_act_metric_fact_rec IN act_metric_fact_rec_type,
2464 x_return_status OUT NOCOPY VARCHAR2
2465 )
2466 IS
2467 l_item_name VARCHAR2(30); -- Used to standardize error messages.
2468 l_act_metric_fact_rec act_metric_fact_rec_type := p_act_metric_fact_rec;
2469 l_return_status VARCHAR2(1);
2470
2471
2472 l_table_name VARCHAR2(30);
2473 l_pk_name VARCHAR2(30);
2474 l_pk_value VARCHAR2(30);
2475 l_pk_data_type VARCHAR2(30);
2476 l_additional_where_clause VARCHAR2(4000); -- Used by Check_FK_Exists.
2477 l_lookup_type VARCHAR2(30);
2478
2479 CURSOR c_hierarchy_node_check(p_hierarchy_id number,p_node_id number,p_act_metric_fact_id number,p_act_metric_id number,p_formula_id number) IS
2480 SELECT 1 from ams_act_metric_facts_all
2481 WHERE hierarchy_id = p_hierarchy_id
2482 AND node_id = p_node_id
2483 AND formula_id = p_formula_id
2484 AND activity_metric_fact_id <> p_act_metric_fact_id
2485 AND activity_metric_id = p_act_metric_id;
2486
2487 l_fact_exists number;
2488
2489
2490 BEGIN
2491 -- Initialize return status to success.
2492 x_return_status := FND_API.G_RET_STS_SUCCESS;
2493
2494
2495
2496 -- ACTIVITY_METRIC_ID
2497 -- Do not validate FK if NULL
2498
2499 IF l_act_metric_fact_rec.activity_metric_id <> FND_API.G_MISS_NUM THEN
2503 l_pk_data_type := AMS_Utility_PVT.G_NUMBER;
2500 l_table_name := 'AMS_ACT_METRICS_ALL';
2501 l_pk_name := 'ACTIVITY_METRIC_ID';
2502 l_pk_value := l_act_metric_fact_rec.activity_metric_id;
2504 l_additional_where_clause := NULL ;
2505
2506 IF AMS_Utility_PVT.Check_FK_Exists (
2507 p_table_name => l_table_name
2508 ,p_pk_name => l_pk_name
2509 ,p_pk_value => l_pk_value
2510 ,p_pk_data_type => l_pk_data_type
2511 ,p_additional_where_clause => l_additional_where_clause
2512 ) = FND_API.G_FALSE
2513 THEN
2514 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2515 THEN
2516 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_INVALID_MET');
2517 FND_MSG_PUB.Add;
2518 END IF;
2519
2520 x_return_status := FND_API.G_RET_STS_ERROR;
2521 RETURN;
2522 END IF; -- Check_FK_Exists
2523
2524 END IF;
2525
2526
2527
2528 -- ARC_ACT_METRIC_USED_BY
2529 IF l_act_metric_fact_rec.arc_act_metric_used_by <> FND_API.G_MISS_CHAR THEN
2530 IF l_act_metric_fact_rec.arc_act_metric_used_by NOT IN
2531 ('CAMP','CSCH','EVEH','EVEO','DELV','FUND','FCST')
2532 THEN
2533 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2534 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_USED_BY');
2535 FND_MSG_PUB.Add;
2536 END IF;
2537
2538 x_return_status := FND_API.G_RET_STS_ERROR;
2539 RETURN;
2540 END IF;
2541 END IF;
2542
2543 -----------------------------------------------------------------------
2544 --07-31-2000 tdonohoe , commented out to fix bug 1362107 --
2545 -----------------------------------------------------------------------
2546 /*
2547 IF l_act_metric_fact_rec.hierarchy_id <> FND_API.G_MISS_NUM THEN
2548
2549 l_table_name := 'AMS_HIERARCHIES_ALL_B';
2550 l_pk_name := 'HIERARCHY_ID';
2551 l_pk_value := l_act_metric_fact_rec.hierarchy_id;
2552 l_pk_data_type := AMS_Utility_PVT.G_NUMBER;
2553 l_additional_where_clause := NULL ;
2554
2555 IF AMS_Utility_PVT.Check_FK_Exists (
2556 p_table_name => l_table_name
2557 ,p_pk_name => l_pk_name
2558 ,p_pk_value => l_pk_value
2559 ,p_pk_data_type => l_pk_data_type
2560 ,p_additional_where_clause => l_additional_where_clause
2561 ) = FND_API.G_FALSE
2562 THEN
2563 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2564 THEN
2565 FND_MESSAGE.Set_Name('AMS', 'AMS_HIER_INVALID');
2566 FND_MSG_PUB.Add;
2567 END IF;
2568
2569 x_return_status := FND_API.G_RET_STS_ERROR;
2570 RETURN;
2571 END IF; -- Check_FK_Exists
2572
2573 END IF;
2574
2575 */
2576 -----------------------------------------------------------------------
2577 --End of Comments --
2578 -----------------------------------------------------------------------
2579 ----------------------------------------------------------------------------------
2580 --When a Hierarchy and Node are specified then a check must be done to verify
2581 --that the node is unique in the set of result entries for this activity metric.
2582 ----------------------------------------------------------------------------------
2583 IF l_act_metric_fact_rec.hierarchy_id <> FND_API.G_MISS_NUM THEN
2584
2585 OPEN c_hierarchy_node_check(l_act_metric_fact_rec.hierarchy_id,
2586 l_act_metric_fact_rec.node_id,
2587 l_act_metric_fact_rec.activity_metric_fact_id,
2588 l_act_metric_fact_rec.activity_metric_id,
2589 l_act_metric_fact_rec.formula_id);
2590
2591 FETCH c_hierarchy_node_check INTO l_fact_exists;
2592
2593
2594
2595
2596
2597 IF c_hierarchy_node_check%FOUND THEN
2598 CLOSE c_hierarchy_node_check;
2599 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2600 FND_MESSAGE.set_name('AMS', 'AMS_METR_FACT_DUP_NODE_ID');
2601 FND_MSG_PUB.add;
2602 RAISE FND_API.g_exc_error;
2603 END IF;
2604 ELSE
2605 CLOSE c_hierarchy_node_check;
2606 END IF;
2607 END IF;
2608
2609 EXCEPTION
2610 WHEN OTHERS THEN
2611 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2612 END Check_ActMetricFact_Items;
2613
2614
2615 --
2616 -- Start of comments.
2617 --
2618 -- NAME
2619 -- Validate_ActMetFact_Rec
2620 --
2621 -- PURPOSE
2622 -- Perform Record Level and Other business validations for metric facts.
2623 --
2624 -- NOTES
2625 --
2626 -- HISTORY
2627 -- 24-Apr-2000 tdonohoe Created.
2628 --
2629 -- End of comments.
2630
2631 PROCEDURE Validate_ActMetFact_Rec(
2632 p_act_metric_fact_rec IN act_metric_fact_rec_type,
2633 p_complete_fact_rec IN act_metric_fact_rec_type,
2634 x_return_status OUT NOCOPY VARCHAR2
2635 )
2636 IS
2637
2638 l_act_metric_fact_rec act_metric_fact_rec_type := p_act_metric_fact_rec;
2639
2640 l_table_name VARCHAR2(30);
2641 l_pk_name VARCHAR2(30);
2642 l_pk_value VARCHAR2(30);
2643 l_pk_data_type VARCHAR2(30);
2644 l_additional_where_clause VARCHAR2(4000); -- Used by Check_FK_Exists.
2645
2649 BEGIN
2646 l_return_status VARCHAR2(1);
2647
2648
2650
2651 x_return_status := FND_API.g_ret_sts_success;
2652
2653 IF l_act_metric_fact_rec.arc_act_metric_used_by <> FND_API.G_MISS_CHAR THEN
2654
2655 IF l_act_metric_fact_rec.act_metric_used_by_id = FND_API.G_MISS_NUM THEN
2656 l_act_metric_fact_rec.act_metric_used_by_id := p_complete_fact_rec.act_metric_used_by_id ;
2657 END IF;
2658
2659 IF l_act_metric_fact_rec.activity_metric_id = FND_API.G_MISS_NUM THEN
2660 l_act_metric_fact_rec.activity_metric_id := p_complete_fact_rec.activity_metric_id ;
2661 END IF;
2662
2663 -- first Check whether the Metric is attached to same usage or not
2664 l_table_name := 'AMS_ACT_METRICS_ALL';
2665 l_pk_name := 'ACTIVITY_METRIC_ID';
2666 l_pk_value := l_act_metric_fact_rec.activity_metric_id;
2667 l_pk_data_type := AMS_Utility_PVT.G_NUMBER;
2668 l_additional_where_clause := ' arc_act_metric_used_by = '||''''||
2669 l_act_metric_fact_rec.arc_act_metric_used_by||'''' ;
2670
2671
2672
2673 IF AMS_Utility_PVT.Check_FK_Exists (
2674 p_table_name => l_table_name
2675 ,p_pk_name => l_pk_name
2676 ,p_pk_value => l_pk_value
2677 ,p_pk_data_type => l_pk_data_type
2678 ,p_additional_where_clause => l_additional_where_clause
2679 ) = FND_API.G_FALSE
2680 THEN
2681
2682 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2683 THEN
2684 FND_MESSAGE.Set_Name('AMS', 'AMS_METR_INVALID_ACT_USAGE');
2685 FND_MSG_PUB.Add;
2686 END IF;
2687
2688 x_return_status := FND_API.G_RET_STS_ERROR;
2689
2690 RETURN;
2691
2692 END IF; -- Check_FK_Exists
2693
2694
2695 /*
2696
2697 -- Get table_name and pk_name for the ARC qualifier.
2698 AMS_Utility_PVT.Get_Qual_Table_Name_And_PK (
2699 p_sys_qual => l_act_metric_fact_rec.arc_act_metric_used_by,
2700 x_return_status => l_return_status,
2701 x_table_name => l_table_name,
2702 x_pk_name => l_pk_name
2703 );
2704
2705
2706
2707 l_pk_value := l_act_metric_fact_rec.act_metric_used_by_id;
2708 l_pk_data_type := AMS_Utility_PVT.G_NUMBER;
2709 l_additional_where_clause := NULL;
2710
2711 IF AMS_Utility_PVT.Check_FK_Exists (
2712 p_table_name => l_table_name
2713 ,p_pk_name => l_pk_name
2714 ,p_pk_value => l_pk_value
2715 ,p_pk_data_type => l_pk_data_type
2716 ,p_additional_where_clause => NULL
2717 ) = FND_API.G_FALSE
2718 THEN
2719 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2720 FND_MESSAGE.Set_Name ('AMS', 'AMS_METR_INVALID_USED_BY');
2721 FND_MSG_PUB.Add;
2722 END IF;
2723
2724 x_return_status := FND_API.G_RET_STS_ERROR;
2725 RETURN;
2726 END IF;
2727 */
2728
2729 END IF;
2730
2731 EXCEPTION
2732 WHEN OTHERS THEN
2733 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2734
2735
2736 END Validate_ActMetFact_Rec;
2737
2738
2739 --
2740 -- Start of comments.
2741 --
2742 -- NAME
2743 -- Validate_ActMetFact_Items
2744 --
2745 -- PURPOSE
2746 -- Perform All Item level validation for Activity metric facts.
2747 --
2748 -- NOTES
2749 --
2750 -- HISTORY
2751 -- 24-Apr-2000 tdonohoe Created.
2752 --
2753 -- End of comments.
2754
2755 PROCEDURE Validate_ActMetFact_Items (
2756 p_act_metric_fact_rec IN act_metric_fact_rec_type,
2757 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
2758 x_return_status OUT NOCOPY VARCHAR2
2759 )
2760 IS
2761 BEGIN
2762
2763
2764
2765 Check_Req_ActMetricFact_Items(
2766 p_act_metric_fact_rec => p_act_metric_fact_rec,
2767 x_return_status => x_return_status
2768 );
2769 IF x_return_status <> FND_API.g_ret_sts_success THEN
2770 RETURN;
2771 END IF;
2772
2773
2774
2775 Check_ActMetricFact_Uk_Items(
2776 p_act_metric_fact_rec => p_act_metric_fact_rec,
2777 p_validation_mode => p_validation_mode,
2778 x_return_status => x_return_status
2779 );
2780
2781 IF x_return_status <> FND_API.g_ret_sts_success THEN
2782 RETURN;
2783 END IF;
2784
2785
2786
2787 Check_ActMetricFact_Items(
2788 p_act_metric_fact_rec => p_act_metric_fact_rec,
2789 x_return_status => x_return_status
2790 );
2791
2792 IF x_return_status <> FND_API.g_ret_sts_success THEN
2793 RETURN;
2794 END IF;
2795
2796
2797
2798 END Validate_ActMetFact_Items;
2799
2800
2801 END Ams_Actmetricfact_Pvt;