[Home] [Help]
PACKAGE BODY: APPS.OZF_QUOTA_ALLOCATIONS_PVT
Source
1 PACKAGE BODY OZF_Quota_allocations_Pvt AS
2 /* $Header: ozfvqalb.pls 115.15 2004/06/11 19:55:29 kvattiku noship $*/
3 g_pkg_name CONSTANT VARCHAR2(30) := 'OZF_Quota_allocations_Pvt';
4
5 ---------------------------------------------------------------------
6 -- FUNCTION
7 --- is_root_or_leaf
8 --
9 -- PURPOSE
10 -- This function returns is the quota a leaf/Root quota or otherwise.
11 -- used to render link to Account allocation page
12 --
13 -- HISTORY
14 -- Tue Dec 02 2003:4/56 PM RSSHARMA Created.
15 -- Tue Dec 16 2003:8/36 PM RSSHARMA Changed function is_root_or_leaf and now the functionality is_leaf only
16
17 -- PARAMETERS
18 -- p_quota_id NUMBER
19 ---------------------------------------------------------------------
20 FUNCTION is_root_or_leaf(p_quota_id IN NUMBER)
21 RETURN VARCHAR2
22 IS
23
24 CURSOR c_leaf(p_quota_id NUMBER) IS
25 SELECT 'Y' from dual
26 WHERE exists(SELECT 1 FROM ozf_funds_all_b WHERE parent_fund_id IS NOT NULL AND fund_id = p_quota_id)
27 AND NOT EXISTS(select 1 FROM ozf_funds_all_b WHERE parent_fund_id = p_quota_id);
28
29 CURSOR c_acct_Spread_OR_Selection(p_quota_start_date DATE) IS
30 SELECT decode(greatest(p_quota_start_date, sysdate), sysdate,'SPR','SEL')
31 FROM dual;
32
33 CURSOR c_quota_start_date(p_quota_id NUMBER) IS
34 SELECT start_date_active
35 FROM ozf_funds_all_b
36 WHERE fund_id = p_quota_id;
37
38 l_return VARCHAR2(1) := 'N';
39 l_quota_start_date DATE;
40 l_acct_icon VARCHAR2(3) := 'N';
41
42 BEGIN
43 open c_leaf(p_quota_id);
44 fetch c_leaf into l_return;
45 close c_leaf;
46
47 --kvattiku Feb 23, 04 modified code to ensure proper icons are displayed on the Quota Overview page columns
48 IF l_return = 'Y' THEN
49
50 open c_quota_start_date(p_quota_id);
51 fetch c_quota_start_date into l_quota_start_date;
52 close c_quota_start_date;
53
54 open c_acct_Spread_OR_Selection(l_quota_start_date);
55 fetch c_acct_Spread_OR_Selection into l_acct_icon;
56 close c_acct_Spread_OR_Selection;
57
58 END IF;
59
60
61 return l_acct_icon;
62 END;
63
64
65
66
67 FUNCTION get_unallocated_amount(p_quota_id IN NUMBER)
68 RETURN NUMBER
69 IS
70
71 CURSOR c_leaf(p_quota_id NUMBER) IS
72 SELECT 'Y' from dual
73 WHERE exists(SELECT 1 FROM ozf_funds_all_b WHERE parent_fund_id IS NOT NULL AND fund_id = p_quota_id)
74 AND NOT EXISTS(select 1 FROM ozf_funds_all_b WHERE parent_fund_id = p_quota_id);
75
76 CURSOR c_unallocated_amt_leaf(p_quota_id NUMBER) IS
77 SELECT TARGET Unallocated_Fund
78 FROM OZF_ACCOUNT_ALLOCATIONS
79 WHERE ALLOCATION_FOR = 'FUND'
80 AND ALLOCATION_FOR_ID = p_quota_id
81 AND PARENT_PARTY_ID = -9999;
82
83 CURSOR c_unallocated_amt_non_leaf(p_quota_id NUMBER) IS
84 SELECT ((NVL(FF.ORIGINAL_BUDGET,0) + NVL(FF.TRANSFERED_IN_AMT,0)) - NVL(FF.TRANSFERED_OUT_AMT,0)) UNALLOCATED_AMT
85 FROM OZF_FUNDS_ALL_VL FF
86 WHERE FF.FUND_ID = p_quota_id;
87
88 l_return VARCHAR2(1) := 'N';
89 l_quota_start_date DATE;
90 l_quota_unallocated_amt NUMBER;
91
92 BEGIN
93 open c_leaf(p_quota_id);
94 fetch c_leaf into l_return;
95 close c_leaf;
96
97 IF l_return = 'Y' THEN
98 open c_unallocated_amt_leaf(p_quota_id);
99 fetch c_unallocated_amt_leaf into l_quota_unallocated_amt;
100 close c_unallocated_amt_leaf;
101 ELSE
102 open c_unallocated_amt_non_leaf(p_quota_id);
103 fetch c_unallocated_amt_non_leaf into l_quota_unallocated_amt;
104 close c_unallocated_amt_non_leaf;
105 END IF;
106
107 return l_quota_unallocated_amt;
108 END;
109
110
111 FUNCTION get_threshold_name(p_threshold_id IN NUMBER )RETURN VARCHAR2 IS
112 CURSOR c_threshold_name (p_id NUMBER) IS
113 SELECT NAME FROM ozf_thresholds_all_vl WHERE threshold_id = p_id;
114 l_threshold_name ozf_thresholds_all_vl.NAME%TYPE;
115 BEGIN
116 OPEN c_threshold_name(p_threshold_id);
117 FETCH c_threshold_name INTO l_threshold_name;
118 CLOSE c_threshold_name;
119
120 return l_threshold_name;
121
122 END;
123
124
125 PROCEDURE generate_product_spread(
126 p_api_version IN NUMBER
127 , p_init_msg_list IN VARCHAR2
128 , p_commit IN VARCHAR2
129 , p_alloc_id IN NUMBER
130 , p_mode IN VARCHAR2
131 , p_context IN VARCHAR2
132 , x_return_status OUT NOCOPY VARCHAR2
133 , x_msg_count OUT NOCOPY NUMBER
134 , x_msg_data OUT NOCOPY VARCHAR2
135 )
136 IS
137 l_api_version CONSTANT NUMBER := 1.0;
138 l_api_name CONSTANT VARCHAR2(30) := 'generate_product_spread';
139 --l_fact_id ozf_act_metric_facts_all.activity_metric_fact_id%type;
140
141 CURSOR c_fact_id (p_alloc_id NUMBER)IS
142 SELECT activity_metric_fact_id , activity_metric_id FROM ozf_act_metric_facts_all
143 WHERE activity_metric_id = p_alloc_id;
144
145 l_fact_id c_fact_id%rowtype;
146 BEGIN
147
148 SAVEPOINT generate_product_spread_sp;
149 x_return_status := FND_API.G_RET_STS_SUCCESS;
150
151 for l_fact_id in c_fact_id(p_alloc_id)
152 loop
153 ozf_allocation_engine_pvt.setup_product_spread
154 (
155 p_api_version =>1.0,
156 p_init_msg_list => FND_API.G_FALSE,
157 p_commit => FND_API.G_FALSE,
158 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
159 x_return_status => x_return_status,
160 x_error_number => x_msg_count,
161 x_error_message => x_msg_data,
162 p_mode => p_mode,
163 p_obj_id => l_fact_id.activity_metric_fact_id,
164 p_context => p_context
165 );
166 end loop;
167
168 IF x_return_status = fnd_api.g_ret_sts_error THEN
169 RAISE fnd_api.g_exc_error;
170 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
171 RAISE fnd_api.g_exc_unexpected_error;
172 END IF;
173
174 EXCEPTION
175 WHEN Fnd_Api.G_EXC_ERROR THEN
176 x_return_status := Fnd_Api.g_ret_sts_error ;
177 ROLLBACK TO generate_product_spread_sp;
178 Fnd_Msg_Pub.Count_AND_Get
179 ( p_count => x_msg_count,
180 p_data => x_msg_data,
181 p_encoded => Fnd_Api.G_FALSE
182 );
183 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
184 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
185 ROLLBACK TO generate_product_spread_sp;
186 Fnd_Msg_Pub.Count_AND_Get
187 ( p_count => x_msg_count,
188 p_data => x_msg_data,
189 p_encoded => Fnd_Api.G_FALSE
190 );
191 WHEN OTHERS THEN
192 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
193 ROLLBACK TO generate_product_spread_sp;
194 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
195 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
196 END IF;
197 Fnd_Msg_Pub.Count_AND_Get
198 ( p_count => x_msg_count,
199 p_data => x_msg_data,
200 p_encoded => Fnd_Api.G_FALSE
201 );
202
203 END generate_product_spread;
204 ---------------------------------------------------------------------
205 -- PROCEDURE
206 --- create_quota_alloc_hierarchy
207 --
208 -- PURPOSE
209 -- Create allocation worksheet hierarchy for Trade Planning Quota.
210 --
211 -- HISTORY
212 -- Wed Nov 12 2003:6/26 PM RSSHARMA Created.
213 --
214 -- PARAMETERS
215 ---------------------------------------------------------------------
216 PROCEDURE create_quota_alloc_hierarchy(
217 p_api_version IN NUMBER
218 , p_init_msg_list IN VARCHAR2
219 , p_commit IN VARCHAR2
220 , p_alloc_id IN NUMBER
221 , x_return_status OUT NOCOPY VARCHAR2
222 , x_msg_count OUT NOCOPY NUMBER
223 , x_msg_data OUT NOCOPY VARCHAR2
224 ) IS
225 l_api_version CONSTANT NUMBER := 1.0;
226 l_api_name CONSTANT VARCHAR2(30) := 'generate_product_spread';
227 BEGIN
228 SAVEPOINT create_quota_alloc_hier_sp;
229 x_return_status := FND_API.G_RET_STS_SUCCESS;
230
231 OZF_Fund_allocations_Pvt.create_alloc_hierarchy(
232 p_api_version => p_api_version
233 , p_init_msg_list => p_init_msg_list
234 , p_commit => p_commit
235 , p_alloc_id => p_alloc_id
236 , x_return_status => x_return_status
237 , x_msg_count => x_msg_count
238 , x_msg_data => x_msg_data
239 );
240
241 IF x_return_status = fnd_api.g_ret_sts_error THEN
242 RAISE fnd_api.g_exc_error;
243 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
244 RAISE fnd_api.g_exc_unexpected_error;
245 END IF;
246
247 generate_product_spread(
248 p_api_version => p_api_version
249 , p_init_msg_list => p_init_msg_list
250 , p_commit => p_commit
251 , p_alloc_id => p_alloc_id
252 , p_mode => 'CREATE'
253 , p_context => 'FACT'
254 , x_return_status => x_return_status
255 , x_msg_count => x_msg_count
256 , x_msg_data => x_msg_count
257 );
258
259 IF x_return_status = fnd_api.g_ret_sts_error THEN
260 RAISE fnd_api.g_exc_error;
261 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
262 RAISE fnd_api.g_exc_unexpected_error;
263 END IF;
264
265 EXCEPTION
266 WHEN Fnd_Api.G_EXC_ERROR THEN
267 x_return_status := Fnd_Api.g_ret_sts_error ;
268 ROLLBACK TO create_quota_alloc_hier_sp;
269 Fnd_Msg_Pub.Count_AND_Get
270 ( p_count => x_msg_count,
271 p_data => x_msg_data,
272 p_encoded => Fnd_Api.G_FALSE
273 );
274 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
275 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
276 ROLLBACK TO create_quota_alloc_hier_sp;
277 Fnd_Msg_Pub.Count_AND_Get
278 ( p_count => x_msg_count,
279 p_data => x_msg_data,
280 p_encoded => Fnd_Api.G_FALSE
281 );
282 WHEN OTHERS THEN
283 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
284 ROLLBACK TO create_quota_alloc_hier_sp;
285 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
286 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
287 END IF;
288 Fnd_Msg_Pub.Count_AND_Get
289 ( p_count => x_msg_count,
290 p_data => x_msg_data,
291 p_encoded => Fnd_Api.G_FALSE
292 );
293
294 END create_quota_alloc_hierarchy;
295
296
297 PROCEDURE update_fund_with_extra_fields(
298 p_api_version IN NUMBER DEFAULT 1.0
299 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
300 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
301 , p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full
302 , p_alloc_id IN NUMBER
303 , x_return_status OUT NOCOPY VARCHAR2
304 , x_msg_count OUT NOCOPY NUMBER
305 , x_msg_data OUT NOCOPY VARCHAR2
306 )
307 IS
308 l_api_version CONSTANT NUMBER := 1.0;
309 l_api_name CONSTANT VARCHAR2(30) := 'allocate_target';
310 CURSOR c_fact_id (p_alloc_id NUMBER)IS
311 SELECT activity_metric_fact_id , act_metric_used_by_id FROM ozf_act_metric_facts_all
312 WHERE activity_metric_id = p_alloc_id;
313 l_fact_id c_fact_id%rowtype;
314
315 CURSOR C_extra_fields (p_alloc_id NUMBER)
316 IS
317 SELECT start_period_name, end_period_name, product_spread_time_id FROM ozf_act_metrics_all
318 WHERE activity_metric_id = p_alloc_id;
319
320 l_start_period_name VARCHAR2(30);
321 l_end_period_name VARCHAR2(30);
322 l_spread_time_id NUMBER;
323 BEGIN
324
325 SAVEPOINT update_fund_sp;
326 x_return_status := FND_API.G_RET_STS_SUCCESS;
327
328 OPEN C_extra_fields(p_alloc_id);
329 FETCH C_extra_fields into l_start_period_name, l_end_period_name, l_spread_time_id;
330 CLOSE C_extra_fields;
331
332 for l_fact_id in c_fact_id(p_alloc_id)
333 loop
334 UPDATE ozf_funds_all_b
335 SET start_period_name = l_start_period_name,
336 end_period_name = l_end_period_name,
337 product_spread_time_id = l_spread_time_id
338 WHERE fund_id = l_fact_id.act_metric_used_by_id;
339 end loop;
340
341 EXCEPTION
342 WHEN OTHERS THEN
343 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
344 ROLLBACK TO update_fund_sp;
345 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
346 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
347 END IF;
348 Fnd_Msg_Pub.Count_AND_Get
349 ( p_count => x_msg_count,
350 p_data => x_msg_data,
351 p_encoded => Fnd_Api.G_FALSE
352 );
353 END update_fund_with_extra_fields;
354
355 PROCEDURE allocate_target (
356 p_api_version IN NUMBER DEFAULT 1.0
357 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
358 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
359 , p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full
360 , p_mode IN VARCHAR2
361 , p_alloc_id IN NUMBER
362 , x_return_status OUT NOCOPY VARCHAR2
363 , x_msg_count OUT NOCOPY NUMBER
364 , x_msg_data OUT NOCOPY VARCHAR2
365 )
366 IS
367 l_api_version CONSTANT NUMBER := 1.0;
368 l_api_name CONSTANT VARCHAR2(30) := 'allocate_target';
369
370 CURSOR c_fact_id (p_alloc_id NUMBER)IS
371 SELECT activity_metric_fact_id , act_metric_used_by_id FROM ozf_act_metric_facts_all
372 WHERE activity_metric_id = p_alloc_id;
373
374 --Added by kvattiku Mar 15, 04
375 --Will be used to ensure that the ozf_allocation_engine_pvt.allocate_target is called only for leaf quota nodes
376
377 CURSOR c_leaf(p_quota_id NUMBER) IS
378 SELECT 'Y' from dual
379 WHERE exists(
380 SELECT 1 FROM ozf_funds_all_b
381 WHERE parent_fund_id IS NOT NULL
382 AND fund_id = p_quota_id)
383 AND NOT EXISTS(
384 select 1 FROM ozf_funds_all_b
385 WHERE parent_fund_id = p_quota_id);
386
387 l_return VARCHAR2(1) := 'N';
388
389 l_fact_id c_fact_id%rowtype;
390
391 BEGIN
392 SAVEPOINT allocate_target_sp;
393 x_return_status := FND_API.G_RET_STS_SUCCESS;
394
395 for l_fact_id in c_fact_id(p_alloc_id)
396 loop
397 --Added by kvattiku Mar 15, 04
398 open c_leaf(l_fact_id.act_metric_used_by_id);
399 fetch c_leaf into l_return;
400 close c_leaf;
401
402 --call the allocate_target api for leaf quota nodes only
403 IF l_return = 'Y' THEN
404 ozf_allocation_engine_pvt.allocate_target
405 (
406 p_api_version => p_api_version
407 , p_init_msg_list => p_init_msg_list
408 , p_commit => p_commit
409 , p_validation_level => p_validation_level
410 , x_return_status =>x_return_status
411 , x_error_number => x_msg_count
412 , x_error_message => x_msg_data
413 , p_mode => p_mode--'FIRSTTIME',
414 , p_fund_id => l_fact_id.act_metric_used_by_id
415 , p_old_start_date => null
416 , p_new_end_date => null
417 , p_addon_fact_id => null
418 , p_addon_amount => null
419 );
420 END IF;
421
422 end loop;
423
424 IF x_return_status = fnd_api.g_ret_sts_error THEN
425 RAISE fnd_api.g_exc_error;
426 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
427 RAISE fnd_api.g_exc_unexpected_error;
428 END IF;
429
430 EXCEPTION
431 WHEN Fnd_Api.G_EXC_ERROR THEN
432 x_return_status := Fnd_Api.g_ret_sts_error ;
433 ROLLBACK TO allocate_target_sp;
434 Fnd_Msg_Pub.Count_AND_Get
435 ( p_count => x_msg_count,
436 p_data => x_msg_data,
437 p_encoded => Fnd_Api.G_FALSE
438 );
439 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
440 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
441 ROLLBACK TO allocate_target_sp;
442 Fnd_Msg_Pub.Count_AND_Get
443 ( p_count => x_msg_count,
444 p_data => x_msg_data,
445 p_encoded => Fnd_Api.G_FALSE
446 );
447 WHEN OTHERS THEN
448 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
449 ROLLBACK TO allocate_target_sp;
450 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
451 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
452 END IF;
453 Fnd_Msg_Pub.Count_AND_Get
454 ( p_count => x_msg_count,
455 p_data => x_msg_data,
456 p_encoded => Fnd_Api.G_FALSE
457 );
458 END allocate_target;
459
460 PROCEDURE publish_allocation( p_api_version IN NUMBER DEFAULT 1.0
461 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
462 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
463 , p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full
464 , p_alloc_id IN NUMBER
465 , x_return_status OUT NOCOPY VARCHAR2
466 , x_msg_count OUT NOCOPY NUMBER
467 , x_msg_data OUT NOCOPY VARCHAR2
468 )
469 IS
470
471 l_api_version CONSTANT NUMBER := 1.0;
472 l_api_name CONSTANT VARCHAR2(30) := 'publish_allocation';
473 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
474
475 CURSOR c_facts (p_alloc_id NUMBER) IS
476 SELECT activity_metric_fact_id , activity_metric_id FROM ozf_act_metric_facts_all
477 WHERE activity_metric_id = p_alloc_id;
478
479 CURSOR c_alloc_dtls(p_alloc_id NUMBER) IS
480 SELECT status_code,object_version_number FROM ozf_act_metrics_all
481 WHERE activity_metric_id = p_alloc_id;
482
483 l_alloc_dtls c_alloc_dtls%rowtype;
484 l_facts c_facts%rowtype;
485 BEGIN
486
487 SAVEPOINT publish_allocation_sp;
488
489 IF Fnd_Api.to_boolean(p_init_msg_list) THEN
490 Fnd_Msg_Pub.initialize;
491 END IF;
492
493 /* IF NOT Fnd_Api.compatible_api_call
494 (
495 l_api_version,
496 p_api_version,
497 l_api_name,
498 g_pkg_name
499 )
500 THEN
501 RAISE Fnd_Api.g_exc_unexpected_error;
502 END IF;
503 */
504 x_return_status := Fnd_Api.g_ret_sts_success;
505
506
507 open c_alloc_dtls(p_alloc_id);
508 fetch c_alloc_dtls into l_alloc_dtls;
509 close c_alloc_dtls;
510 -- Publish funds
511 /* commented out by kvattiku
512 ozf_utility_pvt.debug_message('@Calling Publish Allocation');
513
514 OZF_FUND_ALLOCATIONS_PVT.publish_allocation(p_api_version => p_api_version
515 , p_init_msg_list => p_init_msg_list
516 , p_commit => p_commit
517 , p_validation_level => p_validation_level
518 , p_alloc_id => p_alloc_id
519 , p_alloc_status => l_alloc_dtls.status_code
520 , p_alloc_obj_ver => l_alloc_dtls.object_version_number
521 , x_return_status => x_return_status
522 , x_msg_count => x_msg_count
523 , x_msg_data => x_msg_data
524 );
525 IF x_return_status = fnd_api.g_ret_sts_error THEN
526 RAISE fnd_api.g_exc_error;
527 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
528 RAISE fnd_api.g_exc_unexpected_error;
529 END IF;
530 ozf_utility_pvt.debug_message('@Done Calling Publish Allocation: Return :'||x_return_status);
531 -- update generated funds with the time spread of the allocation
532 */
533 ozf_utility_pvt.debug_message('@Calling Update Fund With Time Spread');
534
535 update_fund_with_extra_fields(
536 p_api_version => p_api_version
537 , p_init_msg_list => p_init_msg_list
538 , p_commit => p_commit
539 , p_validation_level => p_validation_level
540 , p_alloc_id => p_alloc_id
541 , x_return_status => x_return_status
542 , x_msg_count => x_msg_count
543 , x_msg_data => x_msg_data
544 ); -- dont update directly instead call budget api
545 -- generate product spread for the newly created funds
546 IF x_return_status = fnd_api.g_ret_sts_error THEN
547 RAISE fnd_api.g_exc_error;
548 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
549 RAISE fnd_api.g_exc_unexpected_error;
550 END IF;
551 ozf_utility_pvt.debug_message('@Done Calling Update Fund With Time Spread: Return :'||x_return_status);
552 ozf_utility_pvt.debug_message('@Calling generate product spread');
553
554 generate_product_spread(
555 p_api_version => p_api_version
556 , p_init_msg_list => p_init_msg_list
557 , p_commit => p_commit
558 , p_alloc_id => p_alloc_id
559 , p_mode => 'PUBLISH'
560 , p_context => 'FACT'
561 , x_return_status => x_return_status
562 , x_msg_count => x_msg_count
563 , x_msg_data => x_msg_count
564 );
565
566 -- generate time allocations for the newly generated funds
567 IF x_return_status = fnd_api.g_ret_sts_error THEN
568 RAISE fnd_api.g_exc_error;
569 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
570 RAISE fnd_api.g_exc_unexpected_error;
571 END IF;
572
573 ozf_utility_pvt.debug_message('@Done Calling generate product spread: Return:'||x_return_status);
574 ozf_utility_pvt.debug_message('@Calling allocate target');
575
576 allocate_target(
577 p_api_version => p_api_version
578 , p_init_msg_list => p_init_msg_list
579 , p_commit => p_commit
580 , p_validation_level => p_validation_level
581 , p_mode => 'FIRSTTIME'
582 , p_alloc_id => p_alloc_id
583 , x_return_status => x_return_status
584 , x_msg_count => x_msg_count
585 , x_msg_data => x_msg_data
586 );
587
588 IF x_return_status = fnd_api.g_ret_sts_error THEN
589 RAISE fnd_api.g_exc_error;
590 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
591 RAISE fnd_api.g_exc_unexpected_error;
592 END IF;
593
594 ozf_utility_pvt.debug_message('@Done Calling allocate target : return :'||x_return_status);
595
596 EXCEPTION
597 WHEN Fnd_Api.G_EXC_ERROR THEN
598 x_return_status := Fnd_Api.g_ret_sts_error ;
599 ROLLBACK TO publish_allocation_sp;
600 Fnd_Msg_Pub.Count_AND_Get
601 ( p_count => x_msg_count,
602 p_data => x_msg_data,
603 p_encoded => Fnd_Api.G_FALSE
604 );
605 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
606 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
607 ROLLBACK TO publish_allocation_sp;
608 Fnd_Msg_Pub.Count_AND_Get
609 ( p_count => x_msg_count,
610 p_data => x_msg_data,
611 p_encoded => Fnd_Api.G_FALSE
612 );
613 WHEN OTHERS THEN
614 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
615 ROLLBACK TO publish_allocation_sp;
616 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
617 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
618 END IF;
619 Fnd_Msg_Pub.Count_AND_Get
620 ( p_count => x_msg_count,
621 p_data => x_msg_data,
622 p_encoded => Fnd_Api.G_FALSE
623 );
624
625 END;
626
627
628
629 PROCEDURE cancel_alloc_hfq(
630 p_api_version IN NUMBER
631 , p_init_msg_list IN VARCHAR2
632 , p_commit IN VARCHAR2
633 , p_quota_id IN NUMBER
634 , x_return_status OUT NOCOPY VARCHAR2
635 , x_msg_count OUT NOCOPY NUMBER
636 , x_msg_data OUT NOCOPY VARCHAR2
637 )
638 IS
639 l_api_version CONSTANT NUMBER := 1.0;
640 l_api_name CONSTANT VARCHAR2(30) := 'cancel_alloc_hfq';
641
642 CURSOR c_alloc_header_ids(p_quota_id NUMBER) IS
643 select activity_metric_id
644 from ozf_act_metrics_all
645 where arc_act_metric_used_by = 'FUND'
646 and act_metric_used_by_id = p_quota_id;
647
648 CURSOR c_alloc_metric_used_by_id(p_alloc_id NUMBER) IS
649 select act_metric_used_by_id
650 from ozf_act_metric_facts_all
651 where arc_act_metric_used_by = 'FUND'
652 and activity_metric_id = p_alloc_id;
653
654 CURSOR c_user_status_id IS
655 SELECT user_status_id
656 FROM ams_user_statuses_vl
657 WHERE system_status_type = 'OZF_FUND_STATUS'
658 AND system_status_code = 'CANCELLED';
659
660 l_alloc_metric_used_by_id NUMBER;
661 l_user_status_id NUMBER;
662
663 BEGIN
664
665 SAVEPOINT cancel_alloc_hfq_sp;
666 x_return_status := FND_API.G_RET_STS_SUCCESS;
667
668 --Get all the allocation header records for the fund_id and update the status_code to CANCELLED
669 update ozf_act_metrics_all
670 set status_code = 'CANCELLED'
671 where arc_act_metric_used_by = 'FUND'
672 and act_metric_used_by_id = p_quota_id;
673
674 open c_user_status_id;
675 fetch c_user_status_id into l_user_status_id;
676 close c_user_status_id;
677
678 for l_alloc_id in c_alloc_header_ids(p_quota_id)
679 loop
680
681 --Get all the allocation fact records for a given allocation id and update their status_code to cancelled
682 update ozf_act_metric_facts_all
683 set status_code = 'CANCELLED'
684 where arc_act_metric_used_by = 'FUND'
685 and activity_metric_id = l_alloc_id.activity_metric_id;
686
687 open c_alloc_metric_used_by_id(l_alloc_id.activity_metric_id);
688 fetch c_alloc_metric_used_by_id into l_alloc_metric_used_by_id;
689 close c_alloc_metric_used_by_id;
690
691 IF(l_alloc_metric_used_by_id <> p_quota_id) THEN
692
693 --Change the status of child quotas to cancelled (happens if the allocation is published)
694 update ozf_funds_all_b
695 set status_code = 'CANCELLED',
696 user_status_id = l_user_status_id
697 where fund_id in
698 (
699 select act_metric_used_by_id from ozf_act_metric_facts_all
700 where arc_act_metric_used_by = 'FUND'
701 and activity_metric_id = l_alloc_id.activity_metric_id
702 );
703
704 END IF;
705
706
707 end loop;
708
709 EXCEPTION
710 WHEN OTHERS THEN
711 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
712 ROLLBACK TO cancel_alloc_hfq_sp;
713 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
714 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
715 END IF;
716 Fnd_Msg_Pub.Count_AND_Get
717 ( p_count => x_msg_count,
718 p_data => x_msg_data,
719 p_encoded => Fnd_Api.G_FALSE
720 );
721 END cancel_alloc_hfq;
722
723
724
725
726
727
728
729
730 END OZF_Quota_allocations_Pvt;
731