[Home] [Help]
PACKAGE BODY: APPS.OZF_FUND_ALLOCATIONS_PVT
Source
1 PACKAGE BODY OZF_Fund_allocations_Pvt AS
2 /* $Header: ozfvalcb.pls 120.6.12010000.2 2008/08/08 08:53:37 ateotia ship $*/
3 g_pkg_name CONSTANT VARCHAR2(30) := 'OZF_Fund_allocations_Pvt';
4
5 -- yzhao 04/08/2003 fix bug 2897460 - RESOURCE GROUPS IN TERRITORY ADMIN NOT WORKING IN OMO
6 -- corresponds to JTF_TERR_RSC_ALL.RESOURCE_TYPE
7 G_RS_EMPLOYEE_TYPE CONSTANT VARCHAR2(30) := 'RS_EMPLOYEE';
8 G_RS_GROUP_TYPE CONSTANT VARCHAR2(30) := 'RS_GROUP';
9
10 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
11
12 TYPE node_info_type IS RECORD (
13 node_name VARCHAR2(240)
14 , owner NUMBER
15 , parent_node_id NUMBER
16 );
17 TYPE worksheet_record_type IS RECORD (
18 node_id NUMBER
19 , parent_node_id NUMBER
20 , level_depth NUMBER
21 , total_amount NUMBER
22 , hb_amount NUMBER
23 , total_pct NUMBER
24 , hb_pct NUMBER
25 );
26 TYPE node_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
27 TYPE worksheet_table_type IS TABLE OF worksheet_record_type INDEX BY BINARY_INTEGER;
28 TYPE fundIdRec IS RECORD (
29 fact_id NUMBER,
30 fund_id NUMBER,
31 owner NUMBER);
32 TYPE fundIdTableType IS TABLE OF fundIdRec INDEX BY BINARY_INTEGER;
33 TYPE factLevelRec IS RECORD (
34 fact_id NUMBER
35 , level_depth NUMBER
36 );
37 TYPE factLevelTableType IS TABLE OF factLevelRec INDEX BY BINARY_INTEGER;
38
39 ---------------------------------------------------------------------
40 -- FUNCTION
41 -- get_max_end_level
42 --
43 -- PURPOSE
44 -- returns g_max_end_level
45 -- called by BudgetTopbotAdmEO.java
46 -- HISTORY
47 -- 01/28/04 kdass Created.
48 -- PARAMETERS
49 --
50 ---------------------------------------------------------------------
51 FUNCTION get_max_end_level RETURN NUMBER IS
52 BEGIN
53 RETURN g_max_end_level;
54 END;
55
56 ---------------------------------------------------------------------
57 -- PROCEDURE
58 -- get_node_info
59 --
60 -- PURPOSE
61 -- private api to get node's detail information etc.
62 -- HISTORY
63 -- 05/20/02 yzhao Created.
64 -- PARAMETERS
65 --
66 ---------------------------------------------------------------------
67
68 PROCEDURE get_node_info(
69 p_hierarchy_id IN NUMBER
70 , p_hierarchy_type IN VARCHAR2
71 , p_node_id IN NUMBER
72 , x_node_info OUT NOCOPY node_info_type
73 , x_return_status OUT NOCOPY VARCHAR2
74 , x_msg_count OUT NOCOPY NUMBER
75 , x_msg_data OUT NOCOPY VARCHAR2
76 ) IS
77
78 l_node_info node_info_type := NULL;
79
80 -- cursors for territory
81 CURSOR c_get_terr_name IS
82 SELECT node_value, parent_id
83 FROM ozf_terr_v
84 WHERE hierarchy_id = p_hierarchy_id
85 AND node_id = p_node_id;
86
87 --R12 - Modified for Primary Contact - bug 4643041
88 CURSOR c_get_terr_owner IS
89 SELECT resource_id
90 FROM jtf_terr_rsc_all jtra,
91 jtf_terr_rsc_access_all jtraa
92 WHERE jtraa.terr_rsc_id = jtra.terr_rsc_id
93 AND jtraa.access_type = 'OFFER'
94 AND jtraa.trans_access_code = 'PRIMARY_CONTACT'
95 -- WHERE primary_contact_flag = 'Y'
96 AND jtra.resource_type = G_RS_EMPLOYEE_TYPE -- yzhao: 04/09/2003 resource can be employee or group. Only employee can be used as budget owner
97 AND jtra.terr_id = p_node_id;
98
99 -- cursors for budget hierarchy
100 CURSOR c_get_budget_name IS
101 SELECT short_name, owner, parent_fund_id
102 FROM ozf_funds_all_vl
103 WHERE fund_id = p_node_id;
104
105 BEGIN
106 x_return_status := FND_API.G_RET_STS_SUCCESS;
107
108 -- dbms_output.put_line('get_node_info: hier_type=' || p_hierarchy_type || ' node=' || p_node_id || ' hier_id=' || p_hierarchy_id);
109 IF (p_hierarchy_type = 'TERRITORY') THEN
110 -- territory
111 OPEN c_get_terr_name;
112 FETCH c_get_terr_name INTO l_node_info.node_name, l_node_info.parent_node_id;
113 CLOSE c_get_terr_name;
114
115 OPEN c_get_terr_owner;
116 FETCH c_get_terr_owner INTO l_node_info.owner;
117 CLOSE c_get_terr_owner;
118 ELSIF (p_hierarchy_type = 'BUDGET_HIER') THEN
119 -- budget
120 OPEN c_get_budget_name;
121 FETCH c_get_budget_name INTO l_node_info.node_name, l_node_info.owner, l_node_info.parent_node_id;
122 CLOSE c_get_budget_name;
123
124 /* for future release
125 ELSIF (p_hierarchy_type = 'BUDGET_CATEGORY') THEN
126 ELSIF (p_hierarchy_type = 'GEOGRAPHY') THEN
127 ELSIF (p_hierarchy_type = 'HR_ORG') THEN
128 */
129 END IF;
130
131 x_node_info := l_node_info;
132
133 EXCEPTION
134 WHEN OTHERS THEN
135 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
136 -- dbms_output.put_line('get_node_info: UNEXP exception ' || substr(sqlerrm, 1, 150));
137 fnd_msg_pub.count_and_get(
138 p_encoded => fnd_api.g_false
139 , p_count => x_msg_count
140 , p_data => x_msg_data);
141 END get_node_info;
142
143
144 ---------------------------------------------------------------------
145 -- PROCEDURE
146 -- get_node_children
147 --
148 -- PURPOSE
149 -- private api to get node's children etc.
150 -- HISTORY
151 -- 05/20/02 yzhao Created.
152 -- PARAMETERS
153 -- p_fund_rec: the fund record.
154 ---------------------------------------------------------------------
155
156 PROCEDURE get_node_children(
157 p_hierarchy_id IN NUMBER
158 , p_hierarchy_type IN VARCHAR2
159 , p_node_id IN NUMBER
160 , x_child_node_tbl OUT NOCOPY node_table_type
161 , x_return_status OUT NOCOPY VARCHAR2
162 , x_msg_count OUT NOCOPY NUMBER
163 , x_msg_data OUT NOCOPY VARCHAR2
164 ) IS
165
166 l_index NUMBER;
167 l_child_node_tbl node_table_type;
168
169 -- cursors for territory
170 CURSOR c_get_terr_child IS
171 SELECT node_id
172 FROM ozf_terr_v
173 WHERE hierarchy_id = p_hierarchy_id
174 AND parent_id = p_node_id
175 -- Bug # 5723438 fixed by ateotia (+)
176 AND decode(end_date_active,'',sysdate,end_date_active) > = sysdate;
177 -- Bug # 5723438 fixed by ateotia (-)
178
179 -- cursors for budget hierarchy
180 CURSOR c_get_budget_child IS
181 SELECT fund_id
182 FROM ozf_funds_all_b
183 WHERE parent_fund_id = p_node_id
184 AND status_code = 'ACTIVE';
185
186 BEGIN
187 x_return_status := FND_API.G_RET_STS_SUCCESS;
188 l_index := 1;
189
190 -- dbms_output.put_line('get_node_children: hier_type=' || p_hierarchy_type || ' node=' || p_node_id || ' hier_id=' || p_hierarchy_id);
191 IF (p_hierarchy_type = 'TERRITORY') THEN
192 -- territory
193 FOR child_rec IN c_get_terr_child LOOP
194 l_child_node_tbl(l_index) := child_rec.node_id;
195 l_index := l_index + 1;
196 END LOOP;
197
198 ELSIF (p_hierarchy_type = 'BUDGET_HIER') THEN
199 FOR child_rec IN c_get_budget_child LOOP
200 l_child_node_tbl(l_index) := child_rec.fund_id;
201 l_index := l_index + 1;
202 END LOOP;
203
204 /* for future release
205 ELSIF (p_hierarchy_type = 'BUDGET_CATEGORY') THEN
206 ELSIF (p_hierarchy_type = 'GEOGRAPHY') THEN
207 ELSIF (p_hierarchy_type = 'HR_ORG') THEN
208 */
209 END IF;
210
211 x_child_node_tbl := l_child_node_tbl;
212
213 EXCEPTION
214 WHEN OTHERS THEN
215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
216 -- dbms_output.put_line('get_node_children: UNEXP exception ' || substr(sqlerrm, 1, 150));
217 fnd_msg_pub.count_and_get(
218 p_encoded => fnd_api.g_false
219 , p_count => x_msg_count
220 , p_data => x_msg_data);
221 END get_node_children;
222
223
224 ---------------------------------------------------------------------
225 -- PROCEDURE
226 -- get_prior_year_sales
227 --
228 -- PURPOSE
229 -- public api to get prior year's total sales amount for one territory node
230 -- called by compute_worksheet and UI worksheet page
231 --
232 -- HISTORY
233 -- 10/16/02 yzhao Created.
234 -- 14/07/03 nkumar Modified.
235 --
236 -- PARAMETERS
237 ---------------------------------------------------------------------
238
239 PROCEDURE get_prior_year_sales(
240 p_hierarchy_id IN NUMBER
241 , p_node_id IN NUMBER
242 , p_basis_year IN NUMBER
243 , p_alloc_id IN NUMBER
244 , x_self_amount OUT NOCOPY NUMBER
245 , x_rollup_amount OUT NOCOPY NUMBER
246 , x_return_status OUT NOCOPY VARCHAR2
247 , x_msg_count OUT NOCOPY NUMBER
248 , x_msg_data OUT NOCOPY VARCHAR2
249 ) IS
250 l_start_date DATE := NULL;
251 l_end_date DATE := NULL;
252 l_temp_start_date DATE := NULL;
253 l_temp_end_date DATE := NULL;
254 l_date DATE DEFAULT SYSDATE;
255 l_curr_code VARCHAR2(30);
256 l_return_status VARCHAR2(30);
257 l_rate NUMBER;
258 l_r_amount NUMBER;
259 l_s_amount NUMBER;
260
261
262 /* Prior year date is derived as follows
263 - If Allocation has a start date (for eg: 15-MAR-2004)
264 Then
265
266 Start date is 15-MAR-<<Basis Year>>
267
268 Else
269
270 Check If Budget has a start date.
271
272 If Yes (for eg: 01-MAR-2004)
273 Then
274
275 Start date is 01-MAR-<<Basis Year>>
276
277 Else
278
279 Start Date is '01-JAN-<<Basis year>>
280
281 -- Same Logic for End Date
282 */
283
284 CURSOR c_prior_year_parameters(p_node_id NUMBER)
285 IS
286 SELECT f.currency_code_tc,
287 NVL(m.from_date, f.start_date_active),
288 NVL(m.to_date, f.end_date_active)
289 /*
290 TO_DATE(
291 TO_CHAR( NVL(
292 NVL(m.from_date,f.start_date_active)
293 ,TO_DATE('01-01-2004','DD-MM-YYYY')
294 )
295 ,'DD-MM') || '-' || m.basis_year
296 , 'DD-MM-YYYY'
297 ) derived_start_date,
298 TO_DATE(
299 TO_CHAR( NVL(
300 NVL(m.to_date,f.end_date_active)
301 ,TO_DATE('31-12-2004','DD-MM-YYYY')
302 )
303 ,'DD-MM') || '-' || m.basis_year
304 , 'DD-MM-YYYY'
305 ) derived_end_date
306 */
307 FROM ozf_funds_all_b f,
308 ozf_act_metrics_all m
309 WHERE m.activity_metric_id = p_alloc_id
310 AND m.arc_act_metric_used_by = 'FUND'
311 AND m.act_metric_used_by_id = f.fund_id ;
312
313 CURSOR c_get_terr_self_accts (p_node_id IN NUMBER)
314 IS
315 SELECT DISTINCT cust_account_id
316 FROM ams_party_market_segments
317 WHERE market_qualifier_type = 'TERRITORY'
318 AND market_qualifier_reference = p_node_id;
319
320 CURSOR c_get_terr_rollup_accts (p_node_id IN NUMBER)
321 IS
322 SELECT DISTINCT pms.cust_account_id
323 FROM ams_party_market_segments pms,
324 ozf_terr_v terr
325 WHERE pms.market_qualifier_type = 'TERRITORY'
326 AND pms.market_qualifier_reference = terr.node_id
327 AND terr.parent_id = ( SELECT terr1.parent_id
328 FROM ozf_terr_v terr1
329 WHERE terr1.node_id = p_node_id);
330
331 CURSOR c_get_year_to_date_sales( p_as_of_date IN DATE ,
332 p_cust_account_id IN NUMBER)
333 IS
334 SELECT NVL(SUM(b.sales_amt),0) tot_sales
335 FROM ozf_time_rpt_struct a,
336 ozf_order_sales_sumry_mv b
337 WHERE a.report_date = p_as_of_date
338 AND BITAND(a.record_type_id, 119) = a.record_type_id
339 AND a.time_id = b.time_id
340 AND b.sold_to_cust_account_id = p_cust_account_id ;
341
342 l_sales_as_of_end_date NUMBER := 0;
343 l_sales_as_of_start_date NUMBER := 0;
344 l_sales_for_cust NUMBER := 0;
345 l_total_terr_self_amount NUMBER := 0;
346 l_total_terr_rollup_amount NUMBER := 0;
347
348 -- All data in the ozf_order_sales_sumry_mv is already converted
349 -- to this currency code;
350 l_common_currency_code VARCHAR2(30) := FND_PROFILE.VALUE('OZF_TP_COMMON_CURRENCY');
351
352 l_st VARCHAR2(10);
353 l_end VARCHAR2(10);
354
355 BEGIN
356 x_self_amount := 0;
357 x_rollup_amount := 0;
358 x_return_status := FND_API.G_RET_STS_SUCCESS;
359
360 -- Fetch currency_code, start_date and end_date
361 OPEN c_prior_year_parameters(p_node_id);
362 FETCH c_prior_year_parameters INTO l_curr_code, l_temp_start_date, l_temp_end_date;
363 CLOSE c_prior_year_parameters;
364
365 l_st := NVL(TO_CHAR(l_temp_start_date, 'mm-dd'), '01-01');
366 l_end := NVL(TO_CHAR(l_temp_end_date, 'mm-dd'), '12-31') ;
367
368 l_start_date := to_date(p_basis_year || l_st, 'yyyy-mm-dd');
369 l_end_date := to_date(p_basis_year || l_end, 'yyyy-mm-dd');
370
371 --l_start_date := to_date(p_basis_year || '01-01', 'yyyy-mm-dd');
372 --l_end_date := to_date(p_basis_year || '12-31', 'yyyy-mm-dd');
373
374 -- For Self amount
375 FOR cust IN c_get_terr_self_accts(p_node_id)
376 LOOP
377
378 -- Get the Sales for the customer as of end date
379 OPEN c_get_year_to_date_sales ( l_end_date, cust.cust_account_id);
380 FETCH c_get_year_to_date_sales INTO l_sales_as_of_end_date ;
381 CLOSE c_get_year_to_date_sales;
382
383 -- Get the Sales for the customer as of start date
384 OPEN c_get_year_to_date_sales ( l_start_date, cust.cust_account_id);
385 FETCH c_get_year_to_date_sales INTO l_sales_as_of_start_date ;
386 CLOSE c_get_year_to_date_sales;
387
388 -- Actual sales between the Start and End Dates is....
389 l_sales_for_cust := l_sales_as_of_end_date - l_sales_as_of_start_date ;
390
391 l_total_terr_self_amount := l_total_terr_self_amount + l_sales_for_cust;
392
393 END LOOP;
394
395 -- For Rollup amount
396
397 l_sales_as_of_end_date := 0;
398 l_sales_as_of_start_date := 0;
399 l_sales_for_cust := 0;
400
401 FOR cust IN c_get_terr_rollup_accts(p_node_id)
402 LOOP
403
404 -- Get the Sales for the customer as of end date
405 OPEN c_get_year_to_date_sales ( l_end_date, cust.cust_account_id);
406 FETCH c_get_year_to_date_sales INTO l_sales_as_of_end_date ;
407 CLOSE c_get_year_to_date_sales;
408
409 -- Get the Sales for the customer as of start date
410 OPEN c_get_year_to_date_sales ( l_start_date, cust.cust_account_id);
411 FETCH c_get_year_to_date_sales INTO l_sales_as_of_start_date ;
412 CLOSE c_get_year_to_date_sales ;
413
414 -- Actual sales between the Start and End Dates is....
415 l_sales_for_cust := l_sales_as_of_end_date - l_sales_as_of_start_date ;
416
417 l_total_terr_rollup_amount := l_total_terr_self_amount + l_sales_for_cust;
418
419 END LOOP;
420
421 --x_self_amount := l_total_terr_self_amount;
422 --x_rollup_amount := l_total_terr_rollup_amount ;
423 -- Convert the self amount
424
425 Ozf_utility_pvt.convert_currency
426 (x_return_status => l_return_status
427 ,p_from_currency => l_common_currency_code
428 ,p_to_currency => l_curr_code
429 ,p_from_amount => l_total_terr_self_amount
430 ,x_to_amount => x_self_amount
431 ,x_rate => l_rate);
432
433 IF l_return_status = fnd_api.g_ret_sts_error
434 THEN
435 RAISE fnd_api.g_exc_error;
436 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
437 THEN
438 RAISE fnd_api.g_exc_unexpected_error;
439 END IF;
440
441 -- Convert the rollup amount
442
443 Ozf_utility_pvt.convert_currency
444 (x_return_status => l_return_status
445 ,p_from_currency => l_common_currency_code
446 ,p_to_currency => l_curr_code
447 ,p_from_amount => l_total_terr_rollup_amount
448 ,x_to_amount => x_rollup_amount
449 ,x_rate => l_rate);
450
451 IF l_return_status = fnd_api.g_ret_sts_error
452 THEN
453 RAISE fnd_api.g_exc_error;
454 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
455 THEN
456 RAISE fnd_api.g_exc_unexpected_error;
457 END IF;
458
459 EXCEPTION
460 WHEN OTHERS THEN
461 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
462 -- dbms_output.put_line('get_prior_year_sales: UNEXP exception ' || substr(sqlerrm, 1, 150));
463 fnd_msg_pub.count_and_get(
464 p_encoded => fnd_api.g_false
465 , p_count => x_msg_count
466 , p_data => x_msg_data);
467 END get_prior_year_sales;
468
469
470 ---------------------------------------------------------------------
471 -- PROCEDURE
472 --- compute_worksheet
473 --
474 -- PURPOSE
475 -- Compute worksheet using allocation method.
476 -- Traverse hierarchy from current node down till end level
477 -- Specifically, for each level, to avoid computing round up error like bug 2413219,
478 -- the last node alloc amount = parent alloc down amount - sum(sibling alloc amount)
479 --
480 -- HISTORY
481 -- 05/20/02 yzhao Created.
482 --
483 -- PARAMETERS
484 -- p_alloc_down_amount: parent's allocation down amount
485 -- p_alloc_amount: if it is not null, use this amount as node's allocation amount, do not compute
486 -- p_alloc_pct: if it is not null, use this amount as node's allocation percentage, do not compute
487 ---------------------------------------------------------------------
488 PROCEDURE compute_worksheet(
489 p_api_version IN NUMBER := 1.0
490 , p_alloc_id IN NUMBER
491 , p_alloc_down_amount IN NUMBER
492 , p_alloc_amount IN NUMBER := NULL
493 , p_alloc_pct IN NUMBER := NULL
494 , p_parent_node_id IN NUMBER := NULL
495 , p_sibling_count IN NUMBER := NULL
496 , p_hierarchy_id IN NUMBER
497 , p_hierarchy_type IN VARCHAR2
498 , p_node_id IN NUMBER
499 , p_curr_level IN NUMBER
500 , p_end_level IN NUMBER := g_max_end_level
501 , p_method_code IN VARCHAR2
502 , p_basis_year IN NUMBER
503 , x_worksheet_tbl OUT NOCOPY worksheet_table_type
504 , x_return_status OUT NOCOPY VARCHAR2
505 , x_msg_count OUT NOCOPY NUMBER
506 , x_msg_data OUT NOCOPY VARCHAR2
507 ) IS
508 CURSOR c_get_holdback_info IS
509 SELECT fent.formula_entry_operator, fent.formula_entry_value
510 FROM ozf_act_metric_formulas form, ozf_act_metric_form_ent fent
511 WHERE form.activity_metric_id=p_alloc_id
512 AND form.formula_type = 'HOLDBACK'
513 AND form.level_depth = p_curr_level
514 AND form.formula_id = fent.formula_id
515 AND fent.formula_entry_type = 'CONSTANT';
516
517 l_worksheet_rec worksheet_record_type;
518 l_worksheet_tbl worksheet_table_type;
519 l_child_worksheet_tbl worksheet_table_type;
520 l_child_node_tbl node_table_type;
521 l_return_status VARCHAR2(2);
522 l_hb_type VARCHAR2(30);
523 l_hb_value NUMBER;
524 l_fact_id NUMBER;
525 l_sum_amount NUMBER;
526 l_tmp_amount NUMBER;
527 l_sum_pct NUMBER;
528 l_tmp_pct NUMBER;
529 l_index NUMBER := 1;
530
531 BEGIN
532 x_return_status := FND_API.G_RET_STS_SUCCESS;
533
534 l_worksheet_rec.node_id := p_node_id;
535 l_worksheet_rec.parent_node_id := p_parent_node_id;
536 l_worksheet_rec.level_depth := p_curr_level;
537
538 -- compute allocation amount and percentage
539 IF (p_sibling_count IS NULL) THEN
540 -- top level node
541 l_worksheet_rec.total_amount := p_alloc_down_amount;
542 l_worksheet_rec.total_pct := 100;
543 ELSIF p_alloc_amount IS NOT NULL THEN
544 -- last node: alloc amount = parent alloc down amount - sum(sibling alloc amount)
545 l_worksheet_rec.total_amount := p_alloc_amount;
546 l_worksheet_rec.total_pct := p_alloc_pct;
547 ELSE
548 -- non top level, non last node
549 IF (p_method_code = 'MANUAL') THEN
550 l_worksheet_rec.total_amount := NULL;
551 l_worksheet_rec.total_pct := NULL;
552 l_worksheet_rec.hb_amount := NULL;
553 l_worksheet_rec.hb_pct := NULL;
554 ELSIF (p_method_code = 'EVEN') THEN
555 l_worksheet_rec.total_amount := p_alloc_down_amount / p_sibling_count;
556 l_worksheet_rec.total_pct := 100/p_sibling_count;
557 ELSIF (p_method_code = 'PRIOR_SALES_TOTAL') THEN
558 get_prior_year_sales(p_hierarchy_id => p_hierarchy_id
559 , p_node_id => p_node_id
560 , p_basis_year => p_basis_year
561 , p_alloc_id => p_alloc_id
562 , x_self_amount => l_tmp_amount
563 , x_rollup_amount => l_sum_amount
564 , x_return_status => l_return_status
565 , x_msg_count => x_msg_count
566 , x_msg_data => x_msg_data);
567 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
568 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
569 END IF;
570 -- dbms_output.put_line('compute_worksheet: get_prior_year_sales returns' || x_return_status);
571
572 IF (l_sum_amount = 0) THEN
573 l_worksheet_rec.total_pct := 0;
574 ELSE
575 l_worksheet_rec.total_pct := l_tmp_amount / l_sum_amount * 100;
576 END IF;
577
578 l_worksheet_rec.total_amount := ROUND((p_alloc_down_amount)*(l_worksheet_rec.total_pct)/100);
579 /* for future release
580 ELSIF (p_method_code = 'PRIOR_BUDGET' THEN)
581 ELSIF (p_method_code = 'PRIOR_BUDGET_SPENT') THEN
582 */
583 END IF;
584 END IF;
585 -- dbms_output.put_line('compute_worksheet: total_amount=' || l_worksheet_rec.total_amount || ' total_pct=' || l_worksheet_rec.total_pct);
586
587 IF (p_method_code <> 'MANUAL') THEN
588 -- compute holdback amount and percentage, for territory and geography only
589 l_worksheet_rec.hb_amount := 0;
590 l_worksheet_rec.hb_pct := 0;
591 IF (p_hierarchy_type = 'TERRITORY' OR p_hierarchy_type = 'GEOGRAPHY') THEN
592 OPEN c_get_holdback_info;
593 FETCH c_get_holdback_info INTO l_hb_type, l_hb_value;
594 CLOSE c_get_holdback_info;
595
596 IF (l_hb_value IS NOT NULL) THEN
597 -- holdback information is defined for this level
598 IF (l_hb_type = 'PERCENT') THEN
599 -- holdback percentage is defined for this level
600 l_worksheet_rec.hb_amount := l_worksheet_rec.total_amount * l_hb_value / 100;
601 l_worksheet_rec.hb_pct := l_hb_value;
602 ELSE
603 -- holdback amount is defined for this level
604 l_worksheet_rec.hb_amount := l_hb_value;
605 IF (l_worksheet_rec.total_amount = 0) THEN
606 l_worksheet_rec.hb_pct := 0;
607 ELSE
608 l_worksheet_rec.hb_pct := l_hb_value/l_worksheet_rec.total_amount * 100;
609 END IF;
610 END IF;
611 END IF;
612 END IF;
613 END IF;
614 -- dbms_output.put_line('compute_worksheet: hb_value=' || l_worksheet_rec.hb_amount || ' hb_total_pct=' || l_worksheet_rec.hb_pct);
615
616 l_worksheet_tbl(l_index) := l_worksheet_rec;
617 --dbms_output.put_line('compute_woksheet node_id=' || p_node_id || ' level=' || p_curr_level || ' before child table.index=' || l_index || ' COUNT=' || l_worksheet_tbl.COUNT);
618 IF (p_curr_level IS NULL OR p_curr_level < p_end_level) THEN
619 get_node_children(p_hierarchy_id => p_hierarchy_id
620 , p_hierarchy_type => p_hierarchy_type
621 , p_node_id => p_node_id
622 , x_child_node_tbl => l_child_node_tbl
623 , x_return_status => l_return_status
624 , x_msg_count => x_msg_count
625 , x_msg_data => x_msg_data
626 );
627 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
628 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
629 END IF;
630 --dbms_output.put_line('compute_worksheet: child count=' || l_child_node_tbl.COUNT);
631
632 -- compute worksheet for children. recursive call
633 IF (l_child_node_tbl IS NOT NULL AND l_child_node_tbl.COUNT > 0) THEN
634 l_sum_amount := 0;
635 l_sum_pct := 0;
636 FOR I IN l_child_node_tbl.FIRST .. l_child_node_tbl.LAST LOOP
637 IF I = l_child_node_tbl.LAST THEN
638 l_tmp_amount := l_worksheet_rec.total_amount - l_worksheet_rec.hb_amount - l_sum_amount;
639 l_tmp_pct := 100 - l_sum_pct;
640 ELSE
641 l_tmp_amount := NULL;
642 l_tmp_pct := NULL;
643 END IF;
644
645 compute_worksheet(p_api_version => p_api_version
646 , p_alloc_id => p_alloc_id
647 , p_alloc_down_amount => (l_worksheet_rec.total_amount - l_worksheet_rec.hb_amount)
648 , p_alloc_amount => l_tmp_amount
649 , p_alloc_pct => l_tmp_pct
650 , p_parent_node_id => p_node_id
651 , p_sibling_count => l_child_node_tbl.COUNT
652 , p_hierarchy_id => p_hierarchy_id
653 , p_hierarchy_type => p_hierarchy_type
654 , p_node_id => l_child_node_tbl(I)
655 , p_curr_level => (p_curr_level + 1)
656 , p_end_level => p_end_level
657 , p_method_code => p_method_code
658 , p_basis_year => p_basis_year
659 , x_worksheet_tbl => l_child_worksheet_tbl
660 , x_return_status => x_return_status
661 , x_msg_count => x_msg_count
662 , x_msg_data => x_msg_data
663 );
664 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
665 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
666 END IF;
667
668 IF (l_child_worksheet_tbl.COUNT IS NOT NULL AND l_child_worksheet_tbl.COUNT > 0) THEN
669 l_sum_amount := l_sum_amount + l_child_worksheet_tbl(1).total_amount;
670 l_sum_pct := l_sum_pct + l_child_worksheet_tbl(1).total_pct;
671 FOR I IN l_child_worksheet_tbl.FIRST .. l_child_worksheet_tbl.LAST LOOP
672 l_index := l_index + 1;
673 l_worksheet_tbl(l_index) := l_child_worksheet_tbl(I);
674 -- dbms_output.put_line('compute_worksheet node_id=' || p_node_id || ' level=' || p_curr_level || ' append child table.index=' || l_index || ' COUNT=' || l_worksheet_tbl.COUNT);
675 END LOOP;
676 END IF;
677 END LOOP; -- end for child loop
678 END IF; -- end IF having child node(s)
679 END IF;
680
681 x_worksheet_tbl := l_worksheet_tbl;
682
683 EXCEPTION
684 WHEN OTHERS THEN
685 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
686 -- dbms_output.put_line('compute_worksheet(node_id=' || p_node_id || '): exception - ' || substr(sqlerrm, 1, 200));
687 fnd_msg_pub.count_and_get(
688 p_encoded => fnd_api.g_false
689 , p_count => x_msg_count
690 , p_data => x_msg_data);
691 END compute_worksheet;
692
693 ---------------------------------------------------------------------
694 -- PROCEDURE
695 --- create_alloc_hierarchy
696 --
697 -- PURPOSE
698 -- Create allocation worksheet hierarchy.
699 --
700 -- HISTORY
701 -- 05/20/02 yzhao Created.
702 --
703 -- PARAMETERS
704 ---------------------------------------------------------------------
705 PROCEDURE create_alloc_hierarchy(
706 p_api_version IN NUMBER
707 , p_init_msg_list IN VARCHAR2
708 , p_commit IN VARCHAR2
709 , p_alloc_id IN NUMBER
710 , x_return_status OUT NOCOPY VARCHAR2
711 , x_msg_count OUT NOCOPY NUMBER
712 , x_msg_data OUT NOCOPY VARCHAR2
713 ) IS
714 l_api_version CONSTANT NUMBER := 1.0;
715 l_api_name CONSTANT VARCHAR2(30) := 'create_alloc_hierarchy';
716 l_return_status VARCHAR2(2);
717 l_fact_rec Ozf_Actmetricfact_Pvt.act_metric_fact_rec_type;
718 l_fund_id ozf_act_metrics_all.act_metric_used_by_id%TYPE;
719 l_alloc_amount ozf_act_metrics_all.func_actual_value%TYPE;
720 l_hierarchy_id ozf_act_metrics_all.hierarchy_id%TYPE;
721 l_hierarchy_type ozf_act_metrics_all.hierarchy_type%TYPE;
722 l_start_node ozf_act_metrics_all.start_node%TYPE;
723 l_from_level ozf_act_metrics_all.from_level%TYPE;
724 l_to_level ozf_act_metrics_all.to_level%TYPE;
725 l_from_date ozf_act_metrics_all.from_date%TYPE;
726 l_to_date ozf_act_metrics_all.to_date%TYPE;
727 l_status_code ozf_act_metrics_all.status_code%TYPE;
728 l_method_code ozf_act_metrics_all.method_code%TYPE;
729 l_basis_year ozf_act_metrics_all.basis_year%TYPE;
730 l_ex_start_node ozf_act_metrics_all.ex_start_node%TYPE;
731 l_fact_id ozf_act_metric_facts_all.activity_metric_fact_id%TYPE;
732 l_worksheet_tbl worksheet_table_type;
733
734 CURSOR c_get_alloc_details IS
735 SELECT act_metric_used_by_id, func_actual_value,
736 hierarchy_id, hierarchy_type, start_node, from_level, NVL(to_level, g_max_end_level),
737 from_date, to_date, status_code, method_code, basis_year, ex_start_node
738 FROM ozf_act_metrics_all
739 WHERE activity_metric_id = p_alloc_id;
740
741 CURSOR c_get_parent_fact_id(p_node_id NUMBER, p_level_depth NUMBER) IS
742 SELECT activity_metric_fact_id
743 FROM ozf_act_metric_facts_all
744 WHERE activity_metric_id = p_alloc_id
745 AND node_id = p_node_id
746 AND level_depth = p_level_depth;
747
748 BEGIN
749 SAVEPOINT create_alloc_hierarchy_sp;
750 x_return_status := FND_API.G_RET_STS_SUCCESS;
751
752 IF (fnd_api.to_boolean(p_init_msg_list)) THEN
753 fnd_msg_pub.initialize;
754 END IF;
755
756 IF (NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name)) THEN
757 RAISE fnd_api.g_exc_unexpected_error;
758 END IF;
759
760 IF G_DEBUG THEN
761 Ozf_Utility_Pvt.Debug_Message('create_alloc_hierarchy() start ');
762 END IF;
763
764 OPEN c_get_alloc_details;
765 FETCH c_get_alloc_details
766 INTO l_fund_id, l_alloc_amount,
767 l_hierarchy_id, l_hierarchy_type, l_start_node, l_from_level, l_to_level,
768 l_from_date, l_to_date, l_status_code, l_method_code, l_basis_year, l_ex_start_node;
769 IF c_get_alloc_details%NOTFOUND THEN
770 CLOSE c_get_alloc_details;
771 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
772 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
773 fnd_msg_pub.add;
774 END IF;
775 RAISE fnd_api.g_exc_error;
776 END IF;
777 CLOSE c_get_alloc_details;
778
779 compute_worksheet(p_alloc_id => p_alloc_id
780 , p_alloc_down_amount => l_alloc_amount
781 , p_alloc_amount => NULL
782 , p_alloc_pct => NULL
783 , p_parent_node_id => NULL
784 , p_sibling_count => NULL
785 , p_hierarchy_id => l_hierarchy_id
786 , p_hierarchy_type => l_hierarchy_type
787 , p_node_id => l_start_node
788 , p_curr_level => l_from_level
789 , p_end_level => l_to_level
790 , p_method_code => l_method_code
791 , p_basis_year => l_basis_year
792 , x_worksheet_tbl => l_worksheet_tbl
793 , x_return_status => l_return_status
794 , x_msg_count => x_msg_count
795 , x_msg_data => x_msg_data
796 );
797 -- dbms_output.put_line('create_alloc_hierarchy: compute_worksheet returns ' || l_return_status || ' fact count=' || l_worksheet_tbl.COUNT);
798 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
799 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
800 END IF;
801
802 IF (l_worksheet_tbl.COUNT IS NOT NULL AND l_worksheet_tbl.COUNT > 0) THEN
803 FOR I IN l_worksheet_tbl.FIRST .. l_worksheet_tbl.LAST LOOP
804 l_fact_rec := NULL;
805 l_fact_rec.object_version_number := 1;
806 l_fact_rec.act_metric_used_by_id := l_fund_id;
807 l_fact_rec.arc_act_metric_used_by := 'FUND';
808 l_fact_rec.activity_metric_id := p_alloc_id;
809 l_fact_rec.status_code := 'NEW';
810 l_fact_rec.hierarchy_type := l_hierarchy_type;
811 l_fact_rec.hierarchy_id := l_hierarchy_id;
812 l_fact_rec.node_id := l_worksheet_tbl(I).node_id;
813 l_fact_rec.level_depth := l_worksheet_tbl(I).level_depth;
814 l_fact_rec.recommend_total_amount := l_worksheet_tbl(I).total_amount;
815 l_fact_rec.base_total_pct := l_worksheet_tbl(I).total_pct;
816 l_fact_rec.recommend_hb_amount := l_worksheet_tbl(I).hb_amount;
817 l_fact_rec.base_hb_pct := l_worksheet_tbl(I).hb_pct;
818 l_fact_rec.fact_value := 0; -- I'm not using fact_value, but it is a required field for fact table
819 l_fact_rec.previous_fact_id := NULL;
820 OPEN c_get_parent_fact_id(l_worksheet_tbl(I).parent_node_id, l_worksheet_tbl(I).level_depth-1);
821 FETCH c_get_parent_fact_id INTO l_fact_rec.previous_fact_id;
822 CLOSE c_get_parent_fact_id;
823
824 -- yzhao: 02/18/2003 set fact type as 'EXCLUDE' if start node is excluded
825 IF l_fact_rec.previous_fact_id IS NULL AND l_ex_start_node = 'Y' THEN
826 l_fact_rec.fact_type := 'EXCLUDE';
827 END IF;
828
829 -- write fact record to table
830 OZF_ActMetricFact_PVT.Create_ActMetricFact
831 ( p_api_version => p_api_version
832 , x_return_status => l_return_status
833 , x_msg_count => x_msg_count
834 , x_msg_data => x_msg_data
835 , p_act_metric_fact_rec => l_fact_rec
836 , x_activity_metric_fact_id => l_fact_id
837 );
838 -- dbms_output.put_line('create_alloc_herarchy: create_actmetricfact returns ' || l_return_status || ' fact_id=' || l_fact_id);
839 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
840 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
841 END IF;
842 END LOOP;
843 END IF;
844
845 EXCEPTION
846 WHEN OTHERS THEN
847 ROLLBACK TO create_alloc_hierarchy_sp;
848 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
849 -- dbms_output.put_line('create_alloc_hierarchy: exception - ' || substr(sqlerrm, 1, 200));
850 fnd_msg_pub.count_and_get(
851 p_encoded => fnd_api.g_false
852 , p_count => x_msg_count
853 , p_data => x_msg_data);
854 END create_alloc_hierarchy;
855
856
857 ---------------------------------------------------------------------
858 -- PROCEDURE
859 --- create_budget_for_one_node
860 --
861 -- PURPOSE
862 -- Create budget for one node,
863 -- copy root budget's market and product eligibility
864 -- grant allocator access to the budget
865 -- private api called by publish_allocation() only
866 --
867 -- HISTORY
868 -- 05/30/02 yzhao Created.
869 --
870 -- PARAMETERS
871 ---------------------------------------------------------------------
872 PROCEDURE create_budget_for_one_node(
873 p_api_version IN NUMBER := 1.0
874 , p_fund_id IN NUMBER
875 , p_resource_id IN NUMBER := NULL
876 , p_fund_rec IN ozf_funds_pvt.fund_rec_type
877 , x_fund_id OUT NOCOPY NUMBER
878 , x_return_status OUT NOCOPY VARCHAR2
879 , x_msg_count OUT NOCOPY NUMBER
880 , x_msg_data OUT NOCOPY VARCHAR2
881 ) IS
882 l_new_fund_id NUMBER;
883 l_return_id NUMBER;
884 l_return_status VARCHAR2(2);
885 -- for non-standard out params in copy_act_access
886 l_errnum NUMBER;
887 l_errcode VARCHAR2(30);
888 l_errmsg VARCHAR2(4000);
889 l_access_exists NUMBER;
890 l_access_rec ams_access_pvt.access_rec_type;
891 l_mktelig_rec ams_act_market_segments_pvt.mks_rec_type;
892 l_segments_rec ams_act_market_segments_pvt.mks_rec_type;
893 l_terr_resource_id NUMBER;
894 l_terr_resource_type jtf_terr_rsc_all.resource_type%TYPE;
895 l_access_type ams_act_access.arc_user_or_role_type%TYPE;
896
897 CURSOR c_check_access_exists(p_fund_id NUMBER, p_resource_id NUMBER, p_resource_type VARCHAR2) IS
898 SELECT 1
899 FROM ams_act_access
900 WHERE act_access_to_object_id = p_fund_id
901 AND arc_act_access_to_object = 'FUND'
902 AND user_or_role_id = p_resource_id
903 AND arc_user_or_role_type = p_resource_type
904 AND delete_flag = 'N';
905
906 --R12 - Modified for Primary Contact - bug 4643041
907 CURSOR c_get_territory_resource(p_terr_id NUMBER, p_primary_flag VARCHAR2) IS
908 SELECT jtra.resource_id, jtra.resource_type
909 FROM jtf_terr_rsc_all jtra,
910 jtf_terr_rsc_access_all jtraa
911 -- WHERE primary_contact_flag = p_primary_flag
912 WHERE jtraa.terr_rsc_id = jtra.terr_rsc_id
913 AND jtraa.access_type = 'OFFER'
914 AND DECODE(jtraa.trans_access_code,'PRIMARY_CONTACT','Y','NONE','N','DEFAULT','N','N')=p_primary_flag
915 AND jtra.terr_id = p_terr_id;
916
917 CURSOR c_check_marketelig_unique(p_market_id NUMBER, p_fund_id NUMBER) IS
918 SELECT 1
919 FROM ams_act_market_segments
920 WHERE arc_act_market_segment_used_by = 'FUND'
921 AND act_market_segment_used_by_id = p_fund_id
922 AND market_segment_id = p_market_id
923 AND segment_type = 'TERRITORY';
924
925 CURSOR c_segments_cur(p_fund_id NUMBER, p_hierarchy_id NUMBER) IS
926 SELECT *
927 FROM ams_act_market_segments seg
928 WHERE arc_act_market_segment_used_by = 'FUND'
929 AND act_market_segment_used_by_id = p_fund_id
930 AND (segment_type <> 'TERRITORY'
931 OR (segment_type = 'TERRITORY'
932 AND NOT EXISTS (SELECT 1 FROM ozf_terr_v WHERE hierarchy_id = p_hierarchy_id AND node_id = seg.market_segment_id))
933 );
934
935 BEGIN
936 SAVEPOINT create_budget_for_one_node_sp;
937 x_return_status := FND_API.G_RET_STS_SUCCESS;
938
939 -- create child fund for the node
940 ozf_funds_pvt.create_fund( p_api_version => p_api_version
941 , p_init_msg_list => fnd_api.g_false
942 , p_commit => fnd_api.g_false
943 , p_validation_level => fnd_api.g_valid_level_full
944 , x_return_status => l_return_status
945 , x_msg_count => x_msg_count
946 , x_msg_data => x_msg_data
947 , p_fund_rec => p_fund_rec
948 , x_fund_id => l_new_fund_id
949 );
950 -- dbms_output.put_line('create_budget_for_one_node(): create fund for node returns ' || l_return_status);
951
952 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
953 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
954 END IF;
955
956 IF p_resource_id IS NOT NULL THEN
957 l_access_exists := NULL;
958 OPEN c_check_access_exists(l_new_fund_id, p_resource_id, 'USER');
959 FETCH c_check_access_exists INTO l_access_exists;
960 CLOSE c_check_access_exists;
961
962 IF l_access_exists IS NULL THEN
963 -- add allocator to fund access list if s/he is not owner
964 l_access_rec.act_access_to_object_id := l_new_fund_id;
965 l_access_rec.arc_act_access_to_object := 'FUND';
966 l_access_rec.user_or_role_id := p_resource_id;
967 l_access_rec.arc_user_or_role_type := 'USER';
968 l_access_rec.admin_flag := 'Y';
969 l_access_rec.owner_flag := 'N';
970 ams_access_pvt.create_access(
971 p_api_version => p_api_version
972 , p_init_msg_list => fnd_api.g_true
973 , p_validation_level => fnd_api.g_valid_level_full
974 , p_commit => fnd_api.g_false
975 , p_access_rec => l_access_rec
976 , x_return_status => l_return_status
977 , x_msg_count => x_msg_count
978 , x_msg_data => x_msg_data
979 , x_access_id => l_return_id
980 );
981 IF l_return_status = fnd_api.g_ret_sts_error THEN
982 RAISE fnd_api.g_exc_error;
983 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
984 RAISE fnd_api.g_exc_unexpected_error;
985 END IF;
986 END IF;
987 END IF;
988
989 l_terr_resource_type := NULL;
990 OPEN c_get_territory_resource(p_fund_rec.node_id, 'Y');
991 FETCH c_get_territory_resource INTO l_terr_resource_id, l_terr_resource_type;
992 CLOSE c_get_territory_resource;
993
994 /* yzhao: 04/08/2003 fix bug 2897460 - if territory node's primary contact is group, grant access to the group */
995 IF l_terr_resource_type IS NOT NULL AND l_terr_resource_type = G_RS_GROUP_TYPE THEN
996 l_access_exists := NULL;
997 OPEN c_check_access_exists(l_new_fund_id, l_terr_resource_id, 'GROUP');
998 FETCH c_check_access_exists INTO l_access_exists;
999 CLOSE c_check_access_exists;
1000 IF l_access_exists IS NULL THEN
1001 l_access_rec.act_access_to_object_id := l_new_fund_id;
1002 l_access_rec.arc_act_access_to_object := 'FUND';
1003 l_access_rec.user_or_role_id := l_terr_resource_id;
1004 l_access_rec.arc_user_or_role_type := 'GROUP';
1005 l_access_rec.admin_flag := 'N';
1006 l_access_rec.owner_flag := 'N';
1007 ams_access_pvt.create_access(
1008 p_api_version => p_api_version
1009 , p_init_msg_list => fnd_api.g_true
1010 , p_validation_level => fnd_api.g_valid_level_full
1011 , p_commit => fnd_api.g_false
1012 , x_return_status => l_return_status
1013 , x_msg_count => x_msg_count
1014 , x_msg_data => x_msg_data
1015 , p_access_rec => l_access_rec
1016 , x_access_id => l_return_id
1017 );
1018 IF l_return_status = fnd_api.g_ret_sts_error THEN
1019 RAISE fnd_api.g_exc_error;
1020 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1021 RAISE fnd_api.g_exc_unexpected_error;
1022 END IF;
1023 END IF;
1024 END IF;
1025
1026 /* yzhao: 12/27/2002 grant fund access to territory node's non-primary contacts */
1027 FOR resource_rec IN c_get_territory_resource(p_fund_rec.node_id, 'N') LOOP
1028 l_access_exists := NULL;
1029 IF resource_rec.resource_type = G_RS_EMPLOYEE_TYPE THEN
1030 l_access_type := 'USER';
1031 ELSE
1032 l_access_type := 'GROUP';
1033 END IF;
1034 OPEN c_check_access_exists(l_new_fund_id, resource_rec.resource_id, l_access_type);
1035 FETCH c_check_access_exists INTO l_access_exists;
1036 CLOSE c_check_access_exists;
1037 /* yzhao: 12/27/2002 fix bug 2728235 create access for sales representative only if he has no access to the budget.
1038 * if s/he is one of its ancestor budget owner, create_access() returns error. So check beforehand to avoid that
1039 */
1040 IF l_access_exists IS NULL THEN
1041 l_access_rec.act_access_to_object_id := l_new_fund_id;
1042 l_access_rec.arc_act_access_to_object := 'FUND';
1043 l_access_rec.user_or_role_id := resource_rec.resource_id;
1044 l_access_rec.arc_user_or_role_type := l_access_type;
1045 l_access_rec.admin_flag := 'N';
1046 l_access_rec.owner_flag := 'N';
1047 ams_access_pvt.create_access(
1048 p_api_version => p_api_version
1049 , p_init_msg_list => fnd_api.g_true
1050 , p_validation_level => fnd_api.g_valid_level_full
1051 , p_commit => fnd_api.g_false
1052 , x_return_status => l_return_status
1053 , x_msg_count => x_msg_count
1054 , x_msg_data => x_msg_data
1055 , p_access_rec => l_access_rec
1056 , x_access_id => l_return_id
1057 );
1058 IF l_return_status = fnd_api.g_ret_sts_error THEN
1059 RAISE fnd_api.g_exc_error;
1060 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1061 RAISE fnd_api.g_exc_unexpected_error;
1062 END IF;
1063 END IF;
1064 END LOOP;
1065
1066 /* yzhao: child budget inherits its parent's market eligibility and product eligibility
1067 fix bug 3384488, however if root budget has the territory root this allocation is created for, the child should not inherit it
1068 */
1069 -- copy root budget's market eligibility:
1070 /* l_errnum := 0;
1071 l_errcode := NULL;
1072 l_errmsg := NULL;
1073 ams_copyelements_pvt.copy_act_market_segments (
1074 p_src_act_type => 'FUND',
1075 p_new_act_type => 'FUND',
1076 p_src_act_id => p_fund_id,
1077 p_new_act_id => l_new_fund_id,
1078 p_errnum => l_errnum,
1079 p_errcode => l_errcode,
1080 p_errmsg => l_errmsg
1081 );
1082 -- dbms_output.put_line(l_full_name || 'create market eligibility for new fund(' || l_child_fund_id || ') returns ' || l_errnum || ' errcode=' || l_errcode || ' errmsg=' || l_errmsg);
1083 IF (l_errnum <> 0) THEN
1084 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1085 END IF;
1086 */
1087
1088 FOR segments_rec IN c_segments_cur(p_fund_id, p_fund_rec.hierarchy_id) LOOP
1089 l_segments_rec.object_version_number := 1;
1090 l_segments_rec.act_market_segment_used_by_id := l_new_fund_id;
1091 l_segments_rec.arc_act_market_segment_used_by := 'FUND';
1092 l_segments_rec.market_segment_id := segments_rec.market_segment_id;
1093 l_segments_rec.attribute_category := segments_rec.attribute_category;
1094 l_segments_rec.attribute1 := segments_rec.attribute1;
1095 l_segments_rec.attribute2 := segments_rec.attribute2;
1096 l_segments_rec.attribute3 := segments_rec.attribute3;
1097 l_segments_rec.attribute4 := segments_rec.attribute4;
1098 l_segments_rec.attribute5 := segments_rec.attribute5;
1099 l_segments_rec.attribute6 := segments_rec.attribute6;
1100 l_segments_rec.attribute7 := segments_rec.attribute7;
1101 l_segments_rec.attribute8 := segments_rec.attribute8;
1102 l_segments_rec.attribute9 := segments_rec.attribute9;
1103 l_segments_rec.attribute10 := segments_rec.attribute10;
1104 l_segments_rec.attribute11 := segments_rec.attribute11;
1105 l_segments_rec.attribute12 := segments_rec.attribute12;
1106 l_segments_rec.attribute13 := segments_rec.attribute13;
1107 l_segments_rec.attribute14 := segments_rec.attribute14;
1108 l_segments_rec.attribute15 := segments_rec.attribute15;
1109 l_segments_rec.segment_type := segments_rec.segment_type;
1110 l_segments_rec.exclude_flag := segments_rec.exclude_flag;
1111 l_segments_rec.group_code := segments_rec.group_code;
1112 ams_act_market_segments_pvt.create_market_segments (
1113 p_api_version => p_api_version
1114 , p_init_msg_list => fnd_api.g_true
1115 , p_commit => fnd_api.g_false
1116 , p_validation_level => fnd_api.g_valid_level_full
1117 , x_return_status => l_return_status
1118 , x_msg_count => x_msg_count
1119 , x_msg_data => x_msg_data
1120 , p_mks_rec => l_segments_rec
1121 , x_act_mks_id => l_return_id
1122 );
1123
1124 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1126 END IF;
1127 END LOOP;
1128
1129
1130 -- copy root budget's product eligibility
1131 l_errnum := 0;
1132 l_errcode := NULL;
1133 l_errmsg := NULL;
1134 ams_copyelements_pvt.copy_act_prod (
1135 p_src_act_type => 'FUND',
1136 p_new_act_type => 'FUND',
1137 p_src_act_id => p_fund_id,
1138 p_new_act_id => l_new_fund_id,
1139 p_errnum => l_errnum,
1140 p_errcode => l_errcode,
1141 p_errmsg => l_errmsg
1142 );
1143 -- dbms_output.put_line(l_full_name || 'create product eligibility for new fund(' || l_child_fund_id || ') returns ' || l_errnum || ' errcode=' || l_errcode || ' errmsg=' || l_errmsg);
1144 IF (l_errnum <> 0) THEN
1145 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1146 END IF;
1147
1148 /* yzhao: 04/09/2002 fix bug 2653078: 1158.9FP:MAPREL68: ALLOCATED BUDGET DOES NOT SHOW TERRITORY UNDER MARKET ELIGIBI */
1149 l_errnum := NULL;
1150 OPEN c_check_marketelig_unique(p_fund_rec.node_id, l_new_fund_id);
1151 FETCH c_check_marketelig_unique INTO l_errnum;
1152 CLOSE c_check_marketelig_unique;
1153 -- add territory market eligibility to fund if it's not there
1154 IF l_errnum IS NULL THEN
1155 l_mktelig_rec.market_segment_id := p_fund_rec.node_id;
1156 l_mktelig_rec.arc_act_market_segment_used_by := 'FUND';
1157 l_mktelig_rec.act_market_segment_used_by_id := l_new_fund_id;
1158 l_mktelig_rec.segment_type := 'TERRITORY';
1159 l_mktelig_rec.exclude_flag := 'N';
1160 l_mktelig_rec.group_code := 'TERRITORY' || p_fund_rec.node_id;
1161 ams_act_market_segments_pvt.create_market_segments(
1162 p_api_version => p_api_version
1163 , p_init_msg_list => fnd_api.g_true
1164 , p_commit => fnd_api.g_false
1165 , p_validation_level => fnd_api.g_valid_level_full
1166 , x_return_status => l_return_status
1167 , x_msg_count => x_msg_count
1168 , x_msg_data => x_msg_data
1169 , p_mks_rec => l_mktelig_rec
1170 , x_act_mks_id => l_errnum
1171 );
1172 -- dbms_output.put_line('create_market_segments returns ' || l_return_status);
1173 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1174 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1175 END IF;
1176 END IF;
1177 /* yzhao: 11/01/2002 fix bug 2653078 ends */
1178
1179 x_fund_id := l_new_fund_id;
1180
1181 EXCEPTION
1182 WHEN OTHERS THEN
1183 ROLLBACK TO create_budget_for_one_node_sp;
1184 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1185 -- dbms_output.put_line('create_budget_for_one_node(): exception - ' || substr(sqlerrm, 1, 200));
1186 fnd_msg_pub.count_and_get(
1187 p_encoded => fnd_api.g_false
1188 , p_count => x_msg_count
1189 , p_data => x_msg_data);
1190 END create_budget_for_one_node;
1191
1192
1193 ----------------------------------------------------------------------------------------
1194 -- This Procedure will create approved budget transfer from parent to child --
1195 -- Private api called by activate_one_node() and publish_allocation() --
1196 -- for allocation action code 'TRANSFER_TO_BUDGET' only --
1197 -- Action: create an approved actbudget transfer from parent to child --
1198 ----------------------------------------------------------------------------------------
1199 ---------------------------------PARAMETERS---------------------------------------------
1200 -- p_child_fund_id the child fund id --
1201 -- p_approved_total approved total amount --
1202 ----------------------------------------------------------------------------------------
1203 Procedure transfer_approved_budget(p_api_version IN NUMBER := 1.0,
1204 p_child_fund_id IN NUMBER,
1205 p_approved_total IN NUMBER,
1206 x_return_status OUT NOCOPY VARCHAR2,
1207 x_msg_count OUT NOCOPY NUMBER,
1208 x_msg_data OUT NOCOPY VARCHAR2)
1209 IS
1210 l_return_status VARCHAR2(2);
1211 l_parent_fund_id NUMBER;
1212 l_requestor_id NUMBER;
1213 l_act_budget_id NUMBER;
1214 l_act_budget_rec ozf_actbudgets_pvt.act_budgets_rec_type;
1215
1216 CURSOR c_get_fund_owner(p_fund_id NUMBER) IS
1217 SELECT owner, parent_fund_id
1218 FROM ozf_funds_all_b
1219 WHERE fund_id = p_fund_id;
1220
1221 BEGIN
1222 OPEN c_get_fund_owner(p_child_fund_id);
1223 FETCH c_get_fund_owner INTO l_requestor_id, l_parent_fund_id;
1224 CLOSE c_get_fund_owner;
1225
1226 -- first create a NEW actbudget transfer record
1227 l_act_budget_rec.status_code := 'NEW';
1228 l_act_budget_rec.user_status_id :=
1229 ozf_utility_pvt.get_default_user_status('OZF_BUDGETSOURCE_STATUS', l_act_budget_rec.status_code);
1230 l_act_budget_rec.arc_act_budget_used_by := 'FUND';
1231 l_act_budget_rec.act_budget_used_by_id := p_child_fund_id;
1232 l_act_budget_rec.requester_id := l_requestor_id;
1233 l_act_budget_rec.request_amount := p_approved_total; --- in transferring to fund currency
1234 l_act_budget_rec.budget_source_type := 'FUND';
1235 l_act_budget_rec.budget_source_id := l_parent_fund_id;
1236 l_act_budget_rec.justification := null;
1237 l_act_budget_rec.transfer_type := 'TRANSFER';
1238 l_act_budget_rec.transaction_type := null;
1239 ozf_actbudgets_pvt.create_act_budgets(
1240 p_api_version => p_api_version
1241 , p_act_budgets_rec => l_act_budget_rec
1242 , x_return_status => l_return_status
1243 , x_msg_count => x_msg_count
1244 , x_msg_data => x_msg_data
1245 , x_act_budget_id => l_act_budget_id);
1246 -- dbms_output.put_line('transfer_approved_budget: create_act_Budget returns ' || l_return_status || ' act_budget_id=' || l_act_budget_id);
1247 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1248 RAISE fnd_api.g_exc_unexpected_error;
1249 END IF;
1250
1251 -- update actbudget record as APPROVED
1252 ozf_actbudgets_pvt.init_act_budgets_rec(l_act_budget_rec);
1253 l_act_budget_rec.activity_budget_id := l_act_budget_id;
1254 l_act_budget_rec.object_version_number := 1;
1255 l_act_budget_rec.status_code := 'APPROVED';
1256 l_act_budget_rec.approved_amount := p_approved_total;
1257 l_act_budget_rec.user_status_id :=
1258 ozf_utility_pvt.get_default_user_status('OZF_BUDGETSOURCE_STATUS', l_act_budget_rec.status_code);
1259 ozf_actbudgets_pvt.update_act_budgets(
1260 p_api_version => p_api_version
1261 , p_init_msg_list => fnd_api.g_true
1262 , p_commit => fnd_api.g_false
1263 , p_act_budgets_rec => l_act_budget_rec
1264 , p_child_approval_flag => fnd_api.g_false -- false since child budget already active
1265 , p_requestor_owner_flag => 'Y' -- set it to bypass approval
1266 , x_return_status => l_return_status
1267 , x_msg_count => x_msg_count
1268 , x_msg_data => x_msg_data);
1269 -- dbms_output.put_line('transfer_approved_budget: update_act_Budget approved returns ' || l_return_status || ' act_budget_id=' || l_act_budget_id);
1270 IF l_return_status = fnd_api.g_ret_sts_error THEN
1271 RAISE fnd_api.g_exc_error;
1272 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1273 RAISE fnd_api.g_exc_unexpected_error;
1274 END IF;
1275
1276 x_return_status := fnd_api.g_ret_sts_success;
1277
1278
1279 EXCEPTION
1280 WHEN OTHERS THEN
1281 -- dbms_output.put_line('transfer_approved_budget: exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
1282 x_return_status := fnd_api.g_ret_sts_unexp_error;
1283 fnd_msg_pub.count_and_get(
1284 p_encoded => fnd_api.g_false
1285 , p_count => x_msg_count
1286 , p_data => x_msg_data);
1287 END transfer_approved_budget;
1288
1289
1290 ----------------------------------------------------------------------------------------
1291 -- This Procedure will publish allocation worksheet --
1292 -- create draft or active child funds for the allocation --
1293 -- child funds inherit parent funds's market and product eligibity --
1294 -- OR transfer budget to existing budget --
1295 -- update node status as 'ACTIVE' or 'PLANNED' --
1296 -- send notification to child budget owner --
1297 ----------------------------------------------------------------------------------------
1298 ---------------------------------PARAMETERS---------------------------------------------
1299 -- p_alloc_id allocation id in ozf_act_metrics_all table --
1300 -- p_alloc_status new allocation status, either 'PLANNED' or 'ACTIVE' --
1301 ----------------------------------------------------------------------------------------
1302 Procedure publish_allocation(p_api_version IN NUMBER,
1303 p_init_msg_list IN VARCHAR2,
1304 p_commit IN VARCHAR2,
1305 p_validation_level IN NUMBER,
1306 p_alloc_id IN NUMBER,
1307 p_alloc_status IN VARCHAR2,
1308 p_alloc_obj_ver IN NUMBER,
1309 x_return_status OUT NOCOPY VARCHAR2,
1310 x_msg_count OUT NOCOPY NUMBER,
1311 x_msg_data OUT NOCOPY VARCHAR2)
1312 IS
1313 l_index NUMBER;
1314 l_return_id NUMBER;
1315 l_full_name VARCHAR2(120) := g_pkg_name || ': publish_allocation() ';
1316 l_return_status VARCHAR2(2);
1317 l_resource_id NUMBER;
1318 l_alloc_status VARCHAR2(30);
1319 l_alloc_from_date DATE := NULL;
1320 l_alloc_to_date DATE := NULL;
1321 l_alloc_action_code VARCHAR2(30);
1322 l_alloc_fund_status VARCHAR2(30);
1323 l_fund_id NUMBER;
1324 l_fund_owner NUMBER;
1325 l_fund_start_date DATE := NULL;
1326 l_fund_currency VARCHAR2(15);
1327 l_fund_type VARCHAR2(30);
1328 l_fund_org_id NUMBER;
1329 l_fund_ledger_id NUMBER;
1330 l_fund_dept_id NUMBER;
1331 l_fund_short_name ozf_funds_all_tl.short_name%TYPE;
1332 l_fund_status_code ozf_funds_all_b.status_code%TYPE;
1333 l_fund_number ozf_funds_all_b.fund_number%TYPE;
1334 l_fund_category_id NUMBER;
1335 l_fund_cust_setup_id NUMBER;
1336 l_parent_fund_id NUMBER;
1337 l_parent_fund_owner NUMBER;
1338 l_child_fund_id NUMBER;
1339 l_child_fund_owner NUMBER;
1340 l_child_fund_name VARCHAR2(240);
1341 l_tmp_id NUMBER;
1342 l_tmp_char VARCHAR2(1);
1343 l_notif_subject VARCHAR2(400);
1344 l_notif_body VARCHAR2(4000);
1345 l_node_info node_info_type;
1346 l_fund_rec ozf_funds_pvt.fund_rec_type;
1347 l_metric_rec ozf_actmetric_pvt.act_metric_rec_type;
1348 l_metric_fact_rec ozf_actmetricfact_Pvt.act_metric_fact_rec_type;
1349 l_fund_id_table fundIdTableType;
1350 l_temp_status VARCHAR2(1);
1351 l_fund_meaning VARCHAR2(240) := NULL;
1352
1353 CURSOR c_get_metric_info IS
1354 SELECT act_metric_used_by_id, from_date, to_date, action_code
1355 FROM ozf_act_metrics_all
1356 WHERE activity_metric_id = p_alloc_id;
1357
1358 CURSOR c_get_fund_info(p_fund_id NUMBER) IS
1359 SELECT fu.owner, fu.start_date_active, fu.currency_code_tc, fu.fund_type,
1360 fu.org_id, fu.ledger_id, fu.department_id, fu.short_name, fu.status_code,
1361 fu.fund_number, fu.category_id, fu.custom_setup_id
1362 FROM ozf_funds_all_vl fu
1363 where fund_id = p_fund_id;
1364
1365 CURSOR c_get_fund_owner(p_fund_id NUMBER) IS
1366 SELECT owner, short_name
1367 FROM ozf_funds_all_vl
1368 WHERE fund_id = p_fund_id;
1369
1370 CURSOR c_get_res_id IS
1371 SELECT resource_id
1372 FROM ams_jtf_rs_emp_v
1373 WHERE user_id = FND_GLOBAL.User_Id;
1374
1375 CURSOR c_get_worksheet_info(p_fund_id NUMBER) IS
1376 SELECT activity_metric_fact_id, object_version_number,
1377 hierarchy_id, hierarchy_type, level_depth, node_id, previous_fact_id,
1378 recommend_total_amount, recommend_hb_amount, fact_type
1379 FROM ozf_act_metric_facts_all
1380 WHERE act_metric_used_by_id = p_fund_id
1381 AND arc_act_metric_used_by = 'FUND'
1382 AND activity_metric_id = p_alloc_id
1383 AND status_code = 'NEW'
1384 ORDER BY level_depth, node_id;
1385
1386 CURSOR c_get_user_status_id(p_status_code VARCHAR2) IS
1387 SELECT user_status_id
1388 FROM ams_user_statuses_vl
1389 WHERE UPPER(system_status_code) = UPPER(p_status_code)
1390 AND system_status_type = 'OZF_FUND_STATUS'
1391 AND enabled_flag = 'Y';
1392
1393 BEGIN
1394
1395 SAVEPOINT publish_allocation_sp;
1396 IF G_DEBUG THEN
1397 ozf_utility_pvt.debug_message(l_full_name || ': start');
1398 END IF;
1399 x_return_status := fnd_api.g_ret_sts_success;
1400
1401 IF fnd_api.to_boolean(p_init_msg_list) THEN
1402 fnd_msg_pub.initialize;
1403 END IF;
1404
1405 OPEN c_get_metric_info;
1406 FETCH c_get_metric_info INTO l_fund_id, l_alloc_from_date, l_alloc_to_date, l_alloc_action_code;
1407 CLOSE c_get_metric_info;
1408
1409 OPEN c_get_fund_info(l_fund_id);
1410 FETCH c_get_fund_info INTO l_fund_owner, l_fund_start_date, l_fund_currency, l_fund_type,
1411 l_fund_org_id, l_fund_ledger_id, l_fund_dept_id, l_fund_short_name, l_fund_status_code,
1412 l_fund_number, l_fund_category_id, l_fund_cust_setup_id;
1413 CLOSE c_get_fund_info;
1414
1415 /* TO DO: add 'DRAFT_ALLOC' to lookup OZF_FUND_STATUS and add use_status_id */
1416 IF (p_alloc_status = 'ACTIVE') THEN
1417 IF l_fund_status_code <> 'ACTIVE' THEN
1418 -- can not make allocaiton active if root budget is not active
1419 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1420 IF (l_fund_type = 'QUOTA') THEN
1421 FND_MESSAGE.Set_Name ('OZF', 'OZF_TP_ALLOCACTIVE_ERROR');
1422 ELSE
1423 FND_MESSAGE.Set_Name ('OZF', 'OZF_FUND_ALLOCACTIVE_ERROR');
1424 END IF;
1425 FND_MSG_PUB.Add;
1426 END IF;
1427 x_return_status := FND_API.G_RET_STS_ERROR;
1428 RAISE FND_API.G_EXC_ERROR;
1429 END IF;
1430 l_alloc_fund_status := p_alloc_status;
1431 l_alloc_status := p_alloc_status;
1432 ELSE
1433 l_alloc_fund_status := 'DRAFT'; -- remember to change to 'DRAFT_ALLOC'
1434 l_alloc_status := 'PLANNED';
1435 END IF;
1436
1437 l_fund_short_name := l_fund_short_name || '-' || p_alloc_id;
1438 /* use allocation start date. If it is null, use budget's start date.
1439 always use allocation end date */
1440 IF l_alloc_from_date IS NULL THEN
1441 l_alloc_from_date := l_fund_start_date;
1442 END IF;
1443
1444 OPEN c_get_res_id;
1445 FETCH c_get_res_id INTO l_resource_id;
1446 CLOSE c_get_res_id;
1447
1448 l_index := 1;
1449 FOR l_worksheet_rec IN c_get_worksheet_info(l_fund_id) LOOP
1450 -- dbms_output.put_line(l_full_name || 'index=' || l_index);
1451 ozf_actmetricfact_pvt.init_actmetricfact_rec(l_metric_fact_rec);
1452 IF (l_alloc_action_code = 'TRANSFER_TO_BUDGET') THEN
1453 -- add onto existing budget
1454 l_child_fund_id := l_worksheet_rec.node_id;
1455 OPEN c_get_fund_owner(l_child_fund_id);
1456 FETCH c_get_fund_owner INTO l_child_fund_owner, l_child_fund_name;
1457 CLOSE c_get_fund_owner;
1458
1459 IF (l_alloc_status = 'ACTIVE') THEN
1460 -- create budget transfer record from parent to child if it is active
1461 -- however do not create transfer if it's top level and exclude start flag is ON
1462 IF l_worksheet_rec.fact_type IS NULL OR
1463 l_worksheet_rec.fact_type <> 'EXCLUDE' THEN
1464 transfer_approved_budget(p_api_version => p_api_version
1465 , p_child_fund_id => l_child_fund_id
1466 , p_approved_total => l_worksheet_rec.recommend_total_amount
1467 , x_return_status => l_return_status
1468 , x_msg_count => x_msg_count
1469 , x_msg_data => x_msg_data
1470 );
1471 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1472 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1473 END IF;
1474 END IF;
1475 END IF; -- IF (l_alloc_status = 'ACTIVE') THEN
1476 ELSE
1477 -- create new budget
1478 l_parent_fund_id := NULL;
1479 IF l_worksheet_rec.previous_fact_id IS NULL THEN
1480 -- top level node
1481 l_parent_fund_id := l_fund_id;
1482 l_parent_fund_owner := l_fund_owner;
1483 IF l_worksheet_rec.fact_type = 'EXCLUDE' THEN
1484 -- do not create budget for start node if it is excluded
1485 l_index := l_index + 1;
1486 l_fund_id_table(l_index).fact_id := l_worksheet_rec.activity_metric_fact_id;
1487 l_fund_id_table(l_index).fund_id := l_fund_id;
1488 l_fund_id_table(l_index).owner := l_fund_owner;
1489 l_child_fund_id := l_fund_id;
1490 l_child_fund_owner := l_fund_owner;
1491 GOTO LOOP_UPDATE_STATUS;
1492 END IF;
1493 ELSE
1494 /* non-top level: find parent fund id */
1495 FOR I IN l_fund_id_table.FIRST .. l_fund_id_table.LAST LOOP
1496 IF l_fund_id_table(I).fact_id = l_worksheet_rec.previous_fact_id THEN
1497 l_parent_fund_id := l_fund_id_table(I).fund_id;
1498 l_parent_fund_owner := l_fund_id_table(I).owner;
1499 EXIT;
1500 END IF;
1501 END LOOP;
1502 END IF;
1503
1504 get_node_info( p_hierarchy_id => l_worksheet_rec.hierarchy_id
1505 , p_hierarchy_type => l_worksheet_rec.hierarchy_type
1506 , p_node_id => l_worksheet_rec.node_id
1507 , x_node_info => l_node_info
1508 , x_return_status => l_return_status
1509 , x_msg_count => x_msg_count
1510 , x_msg_data => x_msg_data
1511 );
1512
1513 /* use parent fund owner if the node has no primary contact */
1514 IF l_node_info.owner IS NULL THEN
1515 l_node_info.owner := l_fund_owner;
1516 END IF;
1517 l_fund_rec.owner := l_node_info.owner;
1518 l_fund_rec.parent_node_id := l_node_info.parent_node_id;
1519 l_fund_rec.short_name := substr(l_fund_short_name || '-' || l_node_info.node_name, 1, 80);
1520 l_child_fund_name := l_fund_rec.short_name;
1521 l_fund_rec.parent_fund_id := l_parent_fund_id;
1522 --kpatro 26/09/2006 fix bug 5569140
1523 l_fund_rec.category_id := l_fund_category_id;
1524 l_fund_rec.fund_number := substr(ams_sourcecode_pvt.get_source_code( p_category_id => l_fund_rec.category_id
1525 , p_arc_object_for => 'FUND'), 1, 30);
1526 l_fund_rec.original_budget := l_worksheet_rec.recommend_total_amount;
1527 l_fund_rec.holdback_amt := l_worksheet_rec.recommend_hb_amount;
1528 l_fund_rec.hierarchy_id := l_worksheet_rec.hierarchy_id;
1529 l_fund_rec.node_id := l_worksheet_rec.node_id;
1530 l_fund_rec.hierarchy_level := l_worksheet_rec.level_depth;
1531 l_fund_rec.status_code := l_alloc_fund_status;
1532 OPEN c_get_user_status_id(l_fund_rec.status_code);
1533 FETCH c_get_user_status_id INTO l_fund_rec.user_status_id;
1534 CLOSE c_get_user_status_id;
1535 l_fund_rec.currency_code_tc := l_fund_currency;
1536 l_fund_rec.org_id := l_fund_org_id;
1537 l_fund_rec.ledger_id := l_fund_ledger_id;
1538 l_fund_rec.department_id := l_fund_dept_id;
1539 l_fund_rec.custom_setup_id := l_fund_cust_setup_id;
1540 l_fund_rec.fund_type := l_fund_type;
1541 l_fund_rec.start_date_active := l_alloc_from_date;
1542 l_fund_rec.end_date_active := l_alloc_to_date;
1543 l_fund_rec.fund_usage := 'ALLOC'; -- set special flag for budgets created by allocation
1544
1545 -- dbms_output.put_line(l_full_name || ' about to create fund for node ' || l_node_info.node_name || ' owner=' || l_fund_rec.owner );
1546 -- dbms_output.put_line(' parent_fund_id=' || l_fund_rec.parent_fund_id || ' status=' || l_fund_rec.status_code);
1547
1548 /* add the allocator to the access list if it's neither this budget owner nor the parent budget owner
1549 * ozf_funds_pvt.create_fund() takes care of parent budget owner's access
1550 */
1551 IF l_resource_id = l_fund_rec.owner OR
1552 l_resource_id = l_parent_fund_owner THEN
1553 l_resource_id := NULL;
1554 END IF;
1555
1556 -- create child fund for the node
1557 create_budget_for_one_node(p_api_version => p_api_version
1558 , p_fund_id => l_fund_id
1559 , p_resource_id => l_resource_id
1560 , p_fund_rec => l_fund_rec
1561 , x_fund_id => l_child_fund_id
1562 , x_return_status => l_return_status
1563 , x_msg_count => x_msg_count
1564 , x_msg_data => x_msg_data
1565 );
1566 -- dbms_output.put_line(l_full_name || ' create fund for node ' || l_node_info.node_name || ' returns ' || l_return_status || ' new fund id=' || l_child_fund_id);
1567
1568 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1569 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1570 END IF;
1571
1572 l_index := l_index + 1;
1573 l_fund_id_table(l_index).fact_id := l_worksheet_rec.activity_metric_fact_id;
1574 l_fund_id_table(l_index).fund_id := l_child_fund_id;
1575 l_fund_id_table(l_index).owner := l_fund_rec.owner;
1576 l_child_fund_owner := l_fund_rec.owner;
1577 END IF; -- IF p_alloc_action = 'TRANSFER_TO_BUDGET'
1578
1579 <<LOOP_UPDATE_STATUS>>
1580 /* update fact table used_by_id as the child budget id, set status as ACTIVE or PLANNED */
1581 l_metric_fact_rec.activity_metric_fact_id := l_worksheet_rec.activity_metric_fact_id;
1582 l_metric_fact_rec.object_version_number := l_worksheet_rec.object_version_number;
1583 l_metric_fact_rec.act_metric_used_by_id := l_child_fund_id;
1584 l_metric_fact_rec.status_code := l_alloc_status;
1585 IF (l_alloc_status = 'ACTIVE') THEN
1586 -- for active publish, set actual_total_amount, actual_hb_amount, approval_date
1587 l_metric_fact_rec.actual_total_amount := l_worksheet_rec.recommend_total_amount;
1588 l_metric_fact_rec.actual_hb_amount := NVL(l_worksheet_rec.recommend_hb_amount, 0);
1589 l_metric_fact_rec.approval_date := sysdate;
1590 END IF;
1591 ozf_actmetricfact_Pvt.Update_ActMetricFact( p_api_version => p_api_version
1592 , p_init_msg_list => FND_API.G_FALSE
1593 , p_commit => FND_API.G_FALSE
1594 , p_validation_level => p_validation_level
1595 , x_return_status => l_return_status
1596 , x_msg_count => x_msg_count
1597 , x_msg_data => x_msg_data
1598 , p_act_metric_fact_rec => l_metric_fact_rec
1599 );
1600 -- dbms_output.put_line(l_full_name || 'update metric fact status ' || l_return_status);
1601 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1602 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1603 END IF;
1604
1605 /* send notification to child budget owner */
1606 IF l_fund_type = 'QUOTA' THEN
1607 ozf_utility_pvt.get_lookup_meaning (p_lookup_type => 'OZF_FUND_NTF_TYPE',
1608 p_lookup_code => 'QUOTA',
1609 x_return_status => l_temp_status,
1610 x_meaning => l_fund_meaning);
1611 ELSIF l_fund_type IN ('FIXED', 'FULLY_ACCRUED') THEN
1612 ozf_utility_pvt.get_lookup_meaning (p_lookup_type => 'OZF_FUND_NTF_TYPE',
1613 p_lookup_code => 'BUDGET',
1614 x_return_status => l_temp_status,
1615 x_meaning => l_fund_meaning);
1616 END IF;
1617 fnd_message.set_name('OZF', 'OZF_NTF_ALLOC_PUBLISH_SUB');
1618 fnd_message.set_token('BUDGET_NAME', l_child_fund_name);
1619 fnd_message.set_token('ALLOC_ID', p_alloc_id);
1620 fnd_message.set_token ('FUND_TYPE', l_fund_meaning, FALSE);
1621 l_notif_subject := substrb(fnd_message.get, 1, 400);
1622 fnd_message.set_name('OZF', 'OZF_NTF_ALLOC_PUBLISH_BODY');
1623 fnd_message.set_token ('BUDGET_NAME', l_child_fund_name);
1624 fnd_message.set_token ('ALLOC_ID', p_alloc_id);
1625 fnd_message.set_token ('ALLOC_STATUS', l_alloc_status);
1626 fnd_message.set_token ('FUND_TYPE', l_fund_meaning, FALSE);
1627 l_notif_body := substrb(fnd_message.get, 1, 4000);
1628
1629 ozf_utility_pvt.send_wf_standalone_message(
1630 p_subject => l_notif_subject
1631 , p_body => l_notif_body
1632 , p_send_to_res_id => l_child_fund_owner
1633 , x_notif_id => l_return_id
1634 , x_return_status => l_return_status
1635 );
1636 -- dbms_output.put_line(l_full_name || 'send notificaiton returns ' || l_return_status);
1637 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1638 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1639 END IF;
1640
1641 END LOOP;
1642
1643 /* set allocation as 'PLANNED' or 'ACTIVE' */
1644 Ozf_Actmetric_Pvt.Init_ActMetric_Rec(l_metric_rec);
1645 l_metric_rec.activity_metric_id := p_alloc_id;
1646 l_metric_rec.status_code := l_alloc_status;
1647 l_metric_rec.object_version_number := p_alloc_obj_ver;
1648 IF l_alloc_status = 'ACTIVE' THEN
1649 -- set approval date.
1650 l_metric_rec.act_metric_date := sysdate;
1651 END IF;
1652
1653 /* it is important not to do some validation since allocation amount already deducted from fund,
1654 so validation will fail. should < jtf_plsql_api.G_VALID_LEVEL_RECORD CONSTANT NUMBER:= 80.
1655 set to 70 here.
1656 */
1657 Ozf_Actmetric_Pvt.update_Actmetric( p_api_version => p_api_version
1658 , p_init_msg_list => FND_API.G_FALSE
1659 , p_commit => FND_API.G_FALSE
1660 , p_validation_level => 70
1661 , x_return_status => l_return_status
1662 , x_msg_count => x_msg_count
1663 , x_msg_data => x_msg_data
1664 , p_act_metric_rec => l_metric_rec
1665 );
1666 -- dbms_output.put_line(l_full_name || 'set new alloc status returns ' || l_return_status);
1667 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1668 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1669 END IF;
1670
1671 IF p_commit = FND_API.G_TRUE THEN
1672 commit;
1673 END IF;
1674
1675 EXCEPTION
1676 WHEN OTHERS THEN
1677 -- dbms_output.put_line(l_full_name || 'exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
1678 ROLLBACK TO publish_allocation_sp;
1679 x_return_status := fnd_api.g_ret_sts_unexp_error;
1680 fnd_msg_pub.count_and_get(
1681 p_encoded => fnd_api.g_false
1682 ,p_count => x_msg_count
1683 ,p_data => x_msg_data);
1684
1685 END publish_allocation;
1686
1687
1688 ----------------------------------------------------------------------------------------
1689 -- This private Procedure will validate: --
1690 -- allocation amount <= budget available amount --
1691 -- holdback amount <= allocation amount --
1692 ----------------------------------------------------------------------------------------
1693 Procedure check_budget_available_amount( p_fund_id IN NUMBER
1694 , p_alloc_amount IN NUMBER
1695 , p_hb_amount IN NUMBER := NULL
1696 , x_return_status OUT NOCOPY VARCHAR2
1697 , x_msg_count OUT NOCOPY NUMBER
1698 , x_msg_data OUT NOCOPY VARCHAR2)
1699 IS
1700 l_avail_amount NUMBER;
1701
1702 --asylvia 11-May-2006 bug 5199719 - SQL ID 17778551
1703 CURSOR c_get_fund_amount IS
1704 SELECT ((NVL(original_budget,0)-NVL(holdback_amt,0))+(NVL(transfered_in_amt,0)-NVL(transfered_out_amt,0)))
1705 FROM ozf_funds_all_b
1706 WHERE FUND_id = p_fund_id ;
1707
1708 BEGIN
1709 x_return_status := fnd_api.g_ret_sts_success;
1710
1711 OPEN c_get_fund_amount;
1712 FETCH c_get_fund_amount INTO l_avail_amount;
1713 CLOSE c_get_fund_amount;
1714 -- dbms_output.put_line('top level: budget available amount=' || l_avail_amount || ' recommend total=' || p_alloc_amount);
1715 IF p_alloc_amount > l_avail_amount THEN
1716 -- top level allocation amount can not exceed fund's available amount
1717 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1718 FND_MESSAGE.Set_Name ('OZF', 'OZF_FUND_INV_CHILD_AMT');
1719 FND_MESSAGE.Set_Token('SUMAMT', p_alloc_amount);
1720 FND_MESSAGE.Set_Token('NODEVALUE', 'FUND');
1721 FND_MESSAGE.Set_Token('PAMT', l_avail_amount);
1722 FND_MSG_PUB.Add;
1723 END IF;
1724 x_return_status := FND_API.G_RET_STS_ERROR;
1725 RAISE FND_API.G_EXC_ERROR;
1726 END IF;
1727
1728 IF p_hb_amount > p_alloc_amount THEN
1729 -- holdback amount can not exceed allocation amount
1730 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1731 FND_MESSAGE.Set_Name ('OZF', 'OZF_FUND_HOLDBACKAMT_ERROR');
1732 FND_MESSAGE.Set_Token('HOLDBACK', p_hb_amount);
1733 FND_MESSAGE.Set_Token('ALLOCAMT', p_alloc_amount);
1734 FND_MSG_PUB.Add;
1735 END IF;
1736 x_return_status := FND_API.G_RET_STS_ERROR;
1737 RAISE FND_API.G_EXC_ERROR;
1738 END IF;
1739
1740 EXCEPTION
1741 WHEN OTHERS THEN
1742 x_return_status := fnd_api.g_ret_sts_unexp_error;
1743 fnd_msg_pub.count_and_get(
1744 p_encoded => fnd_api.g_false
1745 ,p_count => x_msg_count
1746 ,p_data => x_msg_data);
1747 END check_budget_available_amount;
1748
1749
1750 ----------------------------------------------------------------------------------------
1751 -- This private Procedure will update base_total_pct, base_hb_pct for the whole worksheet --
1752 -- according to the new recommendation amount --
1753 -- private api called by update_worksheet_amount() only --
1754 ----------------------------------------------------------------------------------------
1755 Procedure update_basepct_info( p_api_version IN NUMBER := 1.0,
1756 p_alloc_id IN NUMBER,
1757 x_return_status OUT NOCOPY VARCHAR2,
1758 x_msg_count OUT NOCOPY NUMBER,
1759 x_msg_data OUT NOCOPY VARCHAR2)
1760 IS
1761 CURSOR c_get_alloc_info IS
1762 SELECT from_level
1763 FROM ozf_act_metrics_all
1764 WHERE activity_metric_id = p_alloc_id;
1765
1766 CURSOR c_get_worksheetfacts IS
1767 SELECT fact.activity_metric_fact_id, fact.object_version_number
1768 , fact.level_depth, fact.previous_fact_id
1769 , fact.recommend_total_amount, fact.recommend_hb_amount
1770 , fact.base_total_pct, fact.base_hb_pct
1771 FROM ozf_act_metric_facts_all fact
1772 WHERE fact.activity_metric_id = p_alloc_id
1773 ORDER BY fact.level_depth asc;
1774
1775 CURSOR c_get_alloc_down(p_parent_fact_id NUMBER) IS
1776 SELECT recommend_total_amount - NVL(recommend_hb_amount, 0)
1777 FROM ozf_act_metric_facts_all
1778 WHERE activity_metric_fact_id = p_parent_fact_id;
1779
1780 l_alloc_from_level NUMBER;
1781 l_parent_alloc_down NUMBER;
1782 l_new_total_pct NUMBER;
1783 l_new_hb_pct NUMBER;
1784 l_return_status VARCHAR2(2);
1785 l_fact_rec ozf_actmetricfact_Pvt.act_metric_fact_rec_type;
1786
1787 BEGIN
1788 x_return_status := fnd_api.g_ret_sts_success;
1789
1790 OPEN c_get_alloc_info;
1791 FETCH c_get_alloc_info INTO l_alloc_from_level;
1792 CLOSE c_get_alloc_info;
1793
1794 FOR worksheet_rec IN c_get_worksheetfacts LOOP
1795 IF worksheet_rec.level_depth = l_alloc_from_level THEN
1796 -- For start node, percentage should always be 100.
1797 l_new_total_pct := 100;
1798 ELSE
1799 -- compute non-start node total percentage.
1800 OPEN c_get_alloc_down(worksheet_rec.previous_fact_id);
1801 FETCH c_get_alloc_down INTO l_parent_alloc_down;
1802 CLOSE c_get_alloc_down;
1803 --Fix for Bug 3603302
1804 IF (l_parent_alloc_down IS NOT NULL AND l_parent_alloc_down <> 0) THEN
1805 l_new_total_pct := worksheet_rec.recommend_total_amount / l_parent_alloc_down * 100;
1806 END IF;
1807 END IF;
1808
1809 IF (worksheet_rec.recommend_total_amount IS NOT NULL AND worksheet_rec.recommend_total_amount <> 0) THEN
1810 l_new_hb_pct := worksheet_rec.recommend_hb_amount / worksheet_rec.recommend_total_amount * 100;
1811 END IF;
1812
1813 IF (l_new_total_pct IS NOT NULL AND worksheet_rec.base_total_pct IS NULL) OR
1814 (l_new_total_pct IS NULL AND worksheet_rec.base_total_pct IS NOT NULL) OR
1815 l_new_total_pct <> worksheet_rec.base_total_pct OR
1816 (l_new_hb_pct IS NOT NULL AND worksheet_rec.base_hb_pct IS NULL) OR
1817 (l_new_hb_pct IS NULL AND worksheet_rec.base_hb_pct IS NOT NULL) OR
1818 l_new_hb_pct <> worksheet_rec.base_hb_pct THEN
1819 Ozf_Actmetricfact_Pvt.Init_ActMetricFact_Rec(x_fact_rec => l_fact_rec);
1820 l_fact_rec.activity_metric_fact_id := worksheet_rec.activity_metric_fact_id;
1821 l_fact_rec.object_version_number := worksheet_rec.object_version_number;
1822 l_fact_rec.base_total_pct := l_new_total_pct;
1823 l_fact_rec.base_hb_pct := l_new_hb_pct;
1824 -- update table ozf_act_metric_facts
1825 ozf_actmetricfact_Pvt.update_actmetricfact(
1826 p_api_version => p_api_version,
1827 p_init_msg_list => fnd_api.g_false,
1828 p_commit => fnd_api.g_false,
1829 p_validation_level => fnd_api.g_valid_level_full,
1830 x_return_status => l_return_status,
1831 x_msg_count => x_msg_count,
1832 x_msg_data => x_msg_data,
1833 p_act_metric_fact_rec => l_fact_rec
1834 );
1835 -- dbms_output.put_line('update fact percentage ' || l_fact_rec.activity_metric_fact_id || ' returns ' || l_return_status);
1836 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1837 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1838 END IF;
1839 END IF;
1840 END LOOP;
1841
1842 EXCEPTION
1843 WHEN OTHERS THEN
1844 x_return_status := fnd_api.g_ret_sts_unexp_error;
1845 fnd_msg_pub.count_and_get(
1846 p_encoded => fnd_api.g_false
1847 , p_count => x_msg_count
1848 , p_data => x_msg_data);
1849 END update_basepct_info;
1850
1851
1852 ----------------------------------------------------------------------------------------
1853 -- This Procedure will sort fact changes by level ascendantly --
1854 -- private api called by update_worksheet_amount() only --
1855 ---------------------------------PARAMETERS---------------------------------------------
1856 -- p_fact_table amount changes by user --
1857 ----------------------------------------------------------------------------------------
1858 Procedure sort_changes_by_level(p_fact_table IN fact_table_type,
1859 x_sorted_fact_ids OUT NOCOPY factLevelTableType,
1860 x_return_status OUT NOCOPY VARCHAR2,
1861 x_msg_count OUT NOCOPY NUMBER,
1862 x_msg_data OUT NOCOPY VARCHAR2)
1863 IS
1864 l_index NUMBER;
1865 l_sorted_fact_ids factLevelTableType;
1866 l_tmp_fact_ids factLevelTableType;
1867 BEGIN
1868 x_return_status := fnd_api.g_ret_sts_success;
1869
1870 IF p_fact_table.FIRST IS NULL THEN
1871 RETURN;
1872 END IF;
1873
1874 l_index := p_fact_table.FIRST;
1875 l_tmp_fact_ids(l_index).fact_id := p_fact_table(l_index).activity_metric_fact_id;
1876 l_tmp_fact_ids(l_index).level_depth := p_fact_table(l_index).level_depth;
1877
1878 IF (p_fact_table.NEXT(l_index) IS NULL) THEN
1879 l_sorted_fact_ids := l_tmp_fact_ids;
1880 ELSE
1881 FOR I IN p_fact_table.NEXT(l_index) .. p_fact_table.LAST LOOP
1882 l_index := l_tmp_fact_ids.FIRST;
1883 WHILE (l_index <= l_tmp_fact_ids.LAST AND
1884 p_fact_table(I).level_depth >= l_tmp_fact_ids(l_index).level_depth) LOOP
1885 l_sorted_fact_ids(l_index).fact_id := l_tmp_fact_ids(l_index).fact_id;
1886 l_sorted_fact_ids(l_index).level_depth := l_tmp_fact_ids(l_index).level_depth;
1887 l_index := l_tmp_fact_ids.NEXT(l_index);
1888 END LOOP;
1889 IF (l_index IS NULL) THEN
1890 -- end of the tmp list, append here
1891 l_index := l_tmp_fact_ids.LAST + 1;
1892 l_sorted_fact_ids(l_index).fact_id := p_fact_table(I).activity_metric_fact_id;
1893 l_sorted_fact_ids(l_index).level_depth := p_fact_table(I).level_depth;
1894 ELSE
1895 -- insert the node in the middle
1896 l_sorted_fact_ids(l_index).fact_id := p_fact_table(I).activity_metric_fact_id;
1897 l_sorted_fact_ids(l_index).level_depth := p_fact_table(I).level_depth;
1898 FOR K IN l_index .. l_tmp_fact_ids.LAST LOOP
1899 l_sorted_fact_ids(K+1).fact_id := l_tmp_fact_ids(K).fact_id;
1900 l_sorted_fact_ids(K+1).level_depth := l_tmp_fact_ids(K).level_depth;
1901 END LOOP;
1902 END IF;
1903 l_tmp_fact_ids := l_sorted_fact_ids;
1904 END LOOP;
1905 END IF;
1906
1907 x_sorted_fact_ids := l_sorted_fact_ids;
1908
1909 EXCEPTION
1910 WHEN OTHERS THEN
1911 x_return_status := fnd_api.g_ret_sts_unexp_error;
1912 FND_MSG_PUB.Count_And_Get (
1913 p_encoded => fnd_api.g_false,
1914 p_count => x_msg_count,
1915 p_data => x_msg_data
1916 );
1917 END sort_changes_by_level;
1918
1919
1920 ----------------------------------------------------------------------------------------
1921 -- This Procedure will cascade recommend amount changes down the sub-tree --
1922 -- private api called by update_worksheet_amount() only --
1923 ---------------------------------PARAMETERS---------------------------------------------
1924 -- p_start_fact_id fact id for the sub-tree's root, not necessarily the allocation start node --
1925 -- p_alloc_status_code allocation status code --
1926 ----------------------------------------------------------------------------------------
1927 Procedure cascade_down_subtree(p_api_version IN NUMBER := 1.0,
1928 p_alloc_id IN NUMBER,
1929 p_alloc_status_code IN VARCHAR2,
1930 p_alloc_action_code IN VARCHAR2,
1931 p_start_fact_id IN NUMBER,
1932 x_visited_node_set OUT NOCOPY node_table_type,
1933 x_return_status OUT NOCOPY VARCHAR2,
1934 x_msg_count OUT NOCOPY NUMBER,
1935 x_msg_data OUT NOCOPY VARCHAR2)
1936 IS
1937 l_return_status VARCHAR2(2);
1938 l_parent_fact_id NUMBER;
1939 l_recommend_total NUMBER;
1940 l_recommend_hb NUMBER;
1941 l_sibling_sum_amount NUMBER;
1942 l_parent_amount NUMBER;
1943 l_fund_id NUMBER;
1944 l_hierarchy_id NUMBER;
1945 l_hierarchy_type VARCHAR2(30);
1946 l_node_id NUMBER;
1947 l_index NUMBER;
1948 l_fact_rec ozf_actmetricfact_Pvt.act_metric_fact_rec_type;
1949 l_fund_rec ozf_funds_pvt.fund_rec_type;
1950 l_node_info node_info_type;
1951 l_visited_node_set node_table_type;
1952
1953 CURSOR c_get_subtree_root IS
1954 SELECT previous_fact_id, recommend_total_amount, recommend_hb_amount,
1955 act_metric_used_by_id
1956 FROM ozf_act_metric_facts_all
1957 WHERE activity_metric_fact_id = p_start_fact_id;
1958
1959 -- get allocation amount summary of this node's children
1960 CURSOR c_get_child_sum(p_parent_fact_id NUMBER) IS
1961 --01/19/2004 kdass fixed bug 3371034
1962 --SELECT SUM(NVL(recommend_total_amount, 0))
1963 SELECT TRUNC(SUM(NVL(recommend_total_amount, 0)))
1964 FROM ozf_act_metric_facts_all fact
1965 WHERE activity_metric_id = p_alloc_id
1966 AND previous_fact_id = p_parent_fact_id;
1967
1968 CURSOR c_get_node_info(p_fact_id NUMBER) IS
1969 SELECT hierarchy_id, hierarchy_type, node_id
1970 FROM ozf_act_metric_facts_all fact
1971 WHERE activity_metric_fact_id = p_fact_id;
1972
1973 CURSOR c_get_subtree_worksheet IS
1974 SELECT activity_metric_fact_id, object_version_number,
1975 previous_fact_id, recommend_total_amount, recommend_hb_amount,
1976 base_total_pct, base_hb_pct
1977 FROM ozf_act_metric_facts_all
1978 CONNECT BY prior activity_metric_fact_id = previous_fact_id
1979 START WITH previous_fact_id = p_start_fact_id
1980 ORDER BY level_depth;
1981
1982 CURSOR c_get_allocdown_amount(p_fact_id NUMBER) IS
1983 SELECT recommend_total_amount - NVL(recommend_hb_amount, 0)
1984 FROM ozf_act_metric_facts_all
1985 WHERE activity_metric_fact_id = p_fact_id;
1986
1987 CURSOR c_get_fund_info(l_fact_id IN NUMBER) IS
1988 SELECT fund_id, object_version_number
1989 FROM ozf_funds_all_b
1990 WHERE fund_id = (SELECT act_metric_used_by_id
1991 FROM ozf_act_metric_facts_all
1992 WHERE activity_metric_fact_id = l_fact_id);
1993
1994 BEGIN
1995 x_return_status := fnd_api.g_ret_sts_success;
1996
1997 -- validate the new recommended amount
1998 OPEN c_get_subtree_root;
1999 FETCH c_get_subtree_root INTO l_parent_fact_id, l_recommend_total, l_recommend_hb, l_fund_id;
2000 CLOSE c_get_subtree_root;
2001
2002 IF l_parent_fact_id IS NOT NULL THEN
2003 -- we're not checking top level node against root budget as update_worksheet_amount() already does that before calling cascade
2004 -- holdback amount can not exceed allocation amount
2005 IF l_recommend_hb > l_recommend_total THEN
2006 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2007 FND_MESSAGE.Set_Name ('OZF', 'OZF_FUND_HOLDBACKAMT_ERROR');
2008 FND_MESSAGE.Set_Token('HOLDBACK', l_recommend_hb);
2009 FND_MESSAGE.Set_Token('ALLOCAMT', l_recommend_total);
2010 FND_MSG_PUB.Add;
2011 END IF;
2012 x_return_status := FND_API.G_RET_STS_ERROR;
2013 RAISE FND_API.G_EXC_ERROR;
2014 END IF;
2015
2016 -- sum(sibling allocation amount) can not exceed parent allocation down amount
2017 OPEN c_get_child_sum(l_parent_fact_id);
2018 FETCH c_get_child_sum INTO l_sibling_sum_amount;
2019 CLOSE c_get_child_sum;
2020 OPEN c_get_allocdown_amount(l_parent_fact_id);
2021 FETCH c_get_allocdown_amount INTO l_parent_amount;
2022 CLOSE c_get_allocdown_amount;
2023 IF l_sibling_sum_amount > l_parent_amount THEN
2024 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2025 OPEN c_get_node_info(l_parent_fact_id);
2026 FETCH c_get_node_info INTO l_hierarchy_id, l_hierarchy_type, l_node_id;
2027 CLOSE c_get_node_info;
2028 get_node_info( p_hierarchy_id => l_hierarchy_id
2029 , p_hierarchy_type => l_hierarchy_type
2030 , p_node_id => l_node_id
2031 , x_node_info => l_node_info
2032 , x_return_status => l_return_status
2033 , x_msg_count => x_msg_count
2034 , x_msg_data => x_msg_data
2035 );
2036 FND_MESSAGE.Set_Name ('OZF', 'OZF_FUND_INV_CHILD_AMT');
2037 FND_MESSAGE.Set_Token('SUMAMT', l_sibling_sum_amount);
2038 FND_MESSAGE.Set_Token('NODEVALUE', l_node_info.node_name);
2039 FND_MESSAGE.Set_Token('PAMT', l_parent_amount);
2040 FND_MSG_PUB.Add;
2041 END IF;
2042 x_return_status := FND_API.G_RET_STS_ERROR;
2043 RAISE FND_API.G_EXC_ERROR;
2044 END IF;
2045 END IF; -- IF l_parent_fact_id IS NOT NULL THEN
2046
2047 -- cascade changes
2048 l_index := 0;
2049 FOR worksheet_rec IN c_get_subtree_worksheet LOOP
2050 Ozf_Actmetricfact_Pvt.Init_ActMetricFact_Rec(x_fact_rec => l_fact_rec);
2051 l_fact_rec.activity_metric_fact_id := worksheet_rec.activity_metric_fact_id;
2052 l_fact_rec.object_version_number := worksheet_rec.object_version_number;
2053 OPEN c_get_allocdown_amount(worksheet_rec.previous_fact_id);
2054 FETCH c_get_allocdown_amount INTO l_parent_amount;
2055 CLOSE c_get_allocdown_amount;
2056 l_fact_rec.recommend_total_amount := l_parent_amount * worksheet_rec.base_total_pct / 100;
2057 l_fact_rec.recommend_hb_amount := l_fact_rec.recommend_total_amount * worksheet_rec.base_hb_pct / 100;
2058 ozf_actmetricfact_Pvt.update_actmetricfact(
2059 p_api_version => p_api_version,
2060 p_init_msg_list => fnd_api.g_false,
2061 p_commit => fnd_api.g_false,
2062 p_validation_level => fnd_api.g_valid_level_full,
2063 x_return_status => l_return_status,
2064 x_msg_count => x_msg_count,
2065 x_msg_data => x_msg_data,
2066 p_act_metric_fact_rec => l_fact_rec
2067 );
2068 -- dbms_output.put_line('cascade_down_subtree() update fact ' || l_fact_rec.activity_metric_fact_id || ' total=' || l_fact_rec.recommend_total_amount || ' holdback=' || l_fact_rec.recommend_hb_amount || ' returns ' || l_return_status);
2069 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2070 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2071 END IF;
2072
2073 l_index := l_index + 1;
2074 l_visited_node_set(l_index) := worksheet_rec.activity_metric_fact_id;
2075
2076 IF p_alloc_status_code = 'PLANNED' AND
2077 p_alloc_action_code = 'CREATE_NEW_BUDGET' THEN
2078 -- update the corresponding child budget's total and holdback amount for published allocation
2079 -- yzhao: 02/04/2003 update only when 'create new budget', do not update if it is 'adding onto existing budget'
2080 ozf_funds_pvt.init_fund_rec (l_fund_rec);
2081 OPEN c_get_fund_info(l_fact_rec.activity_metric_fact_id);
2082 FETCH c_get_fund_info INTO l_fund_rec.fund_id, l_fund_rec.object_version_number;
2083 CLOSE c_get_fund_info;
2084 l_fund_rec.original_budget := l_fact_rec.recommend_total_amount;
2085 l_fund_rec.holdback_amt := l_fact_rec.recommend_hb_amount;
2086 ozf_funds_pvt.update_fund( p_api_version => p_api_version
2087 , p_init_msg_list => fnd_api.g_false
2088 , p_commit => fnd_api.g_false
2089 /* yzhao: 12/17/2002 disable validation here since for cascading, parent budget got updated
2090 before child budget, which may cause amount validation failure
2091 , p_validation_level => fnd_api.g_valid_level_full
2092 */
2093 , p_validation_level => fnd_api.g_valid_level_none
2094 , p_fund_rec => l_fund_rec
2095 , x_return_status => l_return_status
2096 , x_msg_count => x_msg_count
2097 , x_msg_data => x_msg_data
2098 );
2099 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2101 END IF;
2102 END IF; -- IF p_alloc_status_code = 'PLANNED' THEN
2103
2104 END LOOP;
2105
2106 x_visited_node_set := l_visited_node_set;
2107 -- dbms_output.put_line('cascade_down_subtree: visited_node_set.COUNT=' || x_visited_node_set.COUNT);
2108
2109 EXCEPTION
2110 WHEN OTHERS THEN
2111 x_return_status := fnd_api.g_ret_sts_unexp_error;
2112 FND_MSG_PUB.Count_And_Get (
2113 p_encoded => fnd_api.g_false,
2114 p_count => x_msg_count,
2115 p_data => x_msg_data
2116 );
2117 END cascade_down_subtree;
2118
2119
2120 ----------------------------------------------------------------------------------------
2121 -- This Procedure will validate an allocation worksheet recommended amount --
2122 -- For start level node: --
2123 -- recommend total amount <= budget available amount --
2124 -- For each node: --
2125 -- Sum(child recommend total amount) <= this node's recommend total amount - holdback amount
2126 -- since I'm traversing the hierarchy, it's not necessary to do the following check any more:
2127 -- Sum(this node and its sibling's allocation amount) <= --
2128 -- parent allocation amount - holdback amount --
2129 ---------------------------------PARAMETERS---------------------------------------------
2130 -- p_alloc_id allocation id in ozf_act_metrics_all table --
2131 ----------------------------------------------------------------------------------------
2132 Procedure validate_worksheet(p_api_version IN NUMBER,
2133 p_init_msg_list IN VARCHAR2,
2134 p_commit IN VARCHAR2,
2135 p_validation_level IN NUMBER,
2136 p_alloc_id IN NUMBER,
2137 x_return_status OUT NOCOPY VARCHAR2,
2138 x_msg_count OUT NOCOPY NUMBER,
2139 x_msg_data OUT NOCOPY VARCHAR2)
2140 IS
2141 l_fund_id NUMBER;
2142 l_avail_amount NUMBER;
2143 l_alloc_from_level NUMBER;
2144 l_child_sum_amount NUMBER;
2145 l_return_status VARCHAR2(2);
2146 l_node_info node_info_type;
2147 l_alloc_status_code VARCHAR2(30);
2148
2149 CURSOR c_get_alloc_info IS
2150 SELECT act_metric_used_by_id, from_level, status_code
2151 FROM ozf_act_metrics_all
2152 WHERE activity_metric_id = p_alloc_id;
2153
2154 CURSOR c_get_worksheetfacts IS
2155 SELECT fact.activity_metric_fact_id, fact.hierarchy_id, fact.hierarchy_type
2156 , fact.node_id, fact.level_depth
2157 , fact.recommend_total_amount, fact.recommend_hb_amount
2158 FROM ozf_act_metric_facts_all fact
2159 WHERE fact.activity_metric_id = p_alloc_id
2160 ORDER BY fact.level_depth, fact.node_id asc;
2161
2162 -- get allocation amount summary of this node's children
2163 CURSOR c_get_child_sum(p_parent_fact_id NUMBER) IS
2164 --01/19/2004 kdass fixed bug 3371034
2165 --SELECT SUM(NVL(recommend_total_amount, 0))
2166 SELECT TRUNC(SUM(NVL(recommend_total_amount, 0)))
2167 FROM ozf_act_metric_facts_all fact
2168 WHERE activity_metric_id = p_alloc_id
2169 AND previous_fact_id = p_parent_fact_id;
2170
2171 BEGIN
2172 IF G_DEBUG THEN
2173 OZF_Utility_PVT.debug_message('Validate_worksheet: start');
2174 END IF;
2175
2176 IF FND_API.To_Boolean (p_init_msg_list) THEN
2177 FND_MSG_PUB.Initialize;
2178 END IF;
2179 x_return_status := fnd_api.g_ret_sts_success;
2180
2181 OPEN c_get_alloc_info;
2182 FETCH c_get_alloc_info INTO l_fund_id, l_alloc_from_level, l_alloc_status_code;
2183 CLOSE c_get_alloc_info;
2184
2185 FOR worksheet_rec IN c_get_worksheetfacts LOOP
2186
2187 /* yzhao: 01/26/2004 fix bug 3362218 - CHILD ALLOCATIONS IN BUDGET ALLOCATION ERROR OUT
2188 do not validate against root budget if allocation is already active
2189 */
2190 IF worksheet_rec.level_depth = l_alloc_from_level AND
2191 l_alloc_status_code <> 'ACTIVE' THEN
2192 -- top level node: check against root budget
2193 check_budget_available_amount( p_fund_id => l_fund_id
2194 , p_alloc_amount => worksheet_rec.recommend_total_amount
2195 , p_hb_amount => worksheet_rec.recommend_hb_amount
2196 , x_return_status => l_return_status
2197 , x_msg_count => x_msg_count
2198 , x_msg_data => x_msg_data);
2199 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2200 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2201 END IF;
2202 ELSE
2203 -- for each node: holdback amount can not exceed allocation amount
2204 IF worksheet_rec.recommend_hb_amount > worksheet_rec.recommend_total_amount THEN
2205 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2206 FND_MESSAGE.Set_Name ('OZF', 'OZF_FUND_HOLDBACKAMT_ERROR');
2207 FND_MESSAGE.Set_Token('HOLDBACK', worksheet_rec.recommend_hb_amount);
2208 FND_MESSAGE.Set_Token('ALLOCAMT', worksheet_rec.recommend_total_amount);
2209 FND_MSG_PUB.Add;
2210 END IF;
2211 x_return_status := FND_API.G_RET_STS_ERROR;
2212 RAISE FND_API.G_EXC_ERROR;
2213 END IF;
2214 END IF; -- IF l_worksheet_rec.level_depth = l_alloc_from_level THEN
2215
2216 -- for each node: check its allocation down amount against its children's allocation sum
2217 OPEN c_get_child_sum(worksheet_rec.activity_metric_fact_id);
2218 FETCH c_get_child_sum INTO l_child_sum_amount;
2219 CLOSE c_get_child_sum;
2220 l_avail_amount := worksheet_rec.recommend_total_amount - NVL(worksheet_rec.recommend_hb_amount, 0);
2221 IF l_child_sum_amount > (l_avail_amount + g_max_ignorable_amount) THEN
2222 -- sum of this node's children's allocation amount can not exceed this node's available amount
2223 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2224 get_node_info( p_hierarchy_id => worksheet_rec.hierarchy_id
2225 , p_hierarchy_type => worksheet_rec.hierarchy_type
2226 , p_node_id => worksheet_rec.node_id
2227 , x_node_info => l_node_info
2228 , x_return_status => l_return_status
2229 , x_msg_count => x_msg_count
2230 , x_msg_data => x_msg_data
2231 );
2232 FND_MESSAGE.Set_Name ('OZF', 'OZF_FUND_INV_CHILD_AMT');
2233 FND_MESSAGE.Set_Token('SUMAMT', l_child_sum_amount);
2234 FND_MESSAGE.Set_Token('NODEVALUE', l_node_info.node_name);
2235 FND_MESSAGE.Set_Token('PAMT', l_avail_amount);
2236 FND_MSG_PUB.Add;
2237 END IF;
2238 x_return_status := FND_API.G_RET_STS_ERROR;
2239 RAISE FND_API.G_EXC_ERROR;
2240 END IF;
2241 END LOOP;
2242
2243 EXCEPTION
2244 WHEN OTHERS THEN
2245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2246 FND_MSG_PUB.Count_And_Get (
2247 p_encoded => fnd_api.g_false,
2248 p_count => x_msg_count,
2249 p_data => x_msg_data
2250 );
2251 END validate_worksheet;
2252
2253
2254 ----------------------------------------------------------------------------------------
2255 -- This Procedure will update an allocation worksheet --
2256 -- Public api called by worksheet update and publish button --
2257 -- Only called by allocation in 'NEW' OR 'PLANNED' status --
2258 -- It first updates fact amount according to the input --
2259 -- then if 'cascade' flag is set, cascade changes down the whole hierarchy --
2260 -- it also update the corresponding allocation budget's original and holdback amount--
2261 -- if base percentage is null in table, set base percentage
2262 -- cascade is allowed for recommended amount change only --
2263 ----------------------------------------------------------------------------------------
2264 ---------------------------------PARAMETERS---------------------------------------------
2265 -- p_alloc_id allocation id in ozf_act_metrics_all table --
2266 -- p_fact_table table of fact records to be changed --
2267 -- required fields are: activity_metric_fact_id, object_version_number, --
2268 -- recommend_total_amount, recommend_hb_amount, --
2269 -- node_id, level_depth --
2270 ----------------------------------------------------------------------------------------
2271 Procedure update_worksheet_amount(p_api_version IN NUMBER,
2272 p_init_msg_list IN VARCHAR2,
2273 p_commit IN VARCHAR2,
2274 p_validation_level IN NUMBER,
2275 p_alloc_id IN NUMBER,
2276 p_alloc_obj_ver IN NUMBER,
2277 p_cascade_flag IN VARCHAR2,
2278 p_fact_table IN fact_table_type,
2279 x_return_status OUT NOCOPY VARCHAR2,
2280 x_msg_count OUT NOCOPY NUMBER,
2281 x_msg_data OUT NOCOPY VARCHAR2)
2282 IS
2283 l_full_name VARCHAR2(120) := g_pkg_name || ': update_worksheet_amount() ';
2284 l_index NUMBER := 0;
2285 l_return_status VARCHAR2(2);
2286 l_fund_id NUMBER;
2287 l_alloc_start_node NUMBER;
2288 l_alloc_amount NUMBER;
2289 l_alloc_status_code VARCHAR2(30);
2290 l_alloc_action_code VARCHAR2(30);
2291 l_parent_amount NUMBER;
2292 l_not_visited_flag BOOLEAN := true;
2293 l_sorted_fact_ids factLevelTableType;
2294 l_fact_rec ozf_actmetricfact_Pvt.act_metric_fact_rec_type;
2295 l_metric_rec Ozf_Actmetric_Pvt.act_metric_rec_type;
2296 l_fund_rec ozf_funds_pvt.fund_rec_type;
2297 l_visited_node_set node_table_type;
2298 l_new_visited_node_set node_table_type;
2299
2300 CURSOR c_get_alloc_info IS
2301 SELECT status_code, start_node, func_actual_value, act_metric_used_by_id, action_code
2302 FROM ozf_act_metrics_all
2303 WHERE activity_metric_id = p_alloc_id;
2304
2305 CURSOR c_get_fund_info(l_fact_id IN NUMBER) IS
2306 SELECT fund_id, object_version_number
2307 FROM ozf_funds_all_b
2308 WHERE fund_id = (SELECT act_metric_used_by_id
2309 FROM ozf_act_metric_facts_all
2310 WHERE activity_metric_fact_id = l_fact_id);
2311 BEGIN
2312 SAVEPOINT update_worksheet_amount_sp;
2313
2314 IF G_DEBUG THEN
2315 ozf_utility_pvt.debug_message(l_full_name || ': start');
2316 END IF;
2317 x_return_status := fnd_api.g_ret_sts_success;
2318
2319 IF fnd_api.to_boolean(p_init_msg_list) THEN
2320 fnd_msg_pub.initialize;
2321 END IF;
2322
2323 OPEN c_get_alloc_info;
2324 FETCH c_get_alloc_info
2325 INTO l_alloc_status_code, l_alloc_start_node, l_alloc_amount, l_fund_id, l_alloc_action_code;
2326 CLOSE c_get_alloc_info;
2327
2328 IF p_fact_table.COUNT <= 0 OR l_alloc_status_code NOT IN ('NEW', 'PLANNED', 'ACTIVE') THEN
2329 RETURN;
2330 END IF;
2331
2332 FOR l_index IN p_fact_table.FIRST .. p_fact_table.LAST LOOP
2333
2334 l_fact_rec := p_fact_table(l_index);
2335
2336 -- kdass: 01/19/2004 fix bug 3379926 - the allocation worksheet should not accept negative amounts for allocation
2337 IF NVL(p_fact_table(l_index).recommend_total_amount,0) < 0 THEN -- check for recommended total amount
2338 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2339 fnd_message.set_name('OZF', 'OZF_FUND_ALLOCAMT_NEG_ERROR');
2340 fnd_msg_pub.add;
2341 END IF;
2342 x_return_status := fnd_api.g_ret_sts_error;
2343 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2344 END IF;
2345
2346 -- kdass: 01/23/2004 fix bug 3392738 - the allocation worksheet should not accept negative amounts for holdback amounts
2347 IF NVL(p_fact_table(l_index).recommend_hb_amount,0) < 0 THEN -- check for recommended holdback amount
2348 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2349 fnd_message.set_name('OZF', 'OZF_FUND_NO_HOLDBACK_BUDGET');
2350 fnd_msg_pub.add;
2351 END IF;
2352 x_return_status := fnd_api.g_ret_sts_error;
2353 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2354 END IF;
2355
2356 IF p_fact_table(l_index).node_id = l_alloc_start_node THEN
2357 -- start node
2358 -- validation: allocation amount <= budget available amount
2359 -- holdback amount <= allocation amount
2360 check_budget_available_amount( p_fund_id => l_fund_id
2361 , p_alloc_amount => p_fact_table(l_index).recommend_total_amount
2362 , p_hb_amount => p_fact_table(l_index).recommend_hb_amount
2363 , x_return_status => l_return_status
2364 , x_msg_count => x_msg_count
2365 , x_msg_data => x_msg_data);
2366 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2367 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2368 END IF;
2369
2370 IF l_alloc_amount <> p_fact_table(l_index).recommend_total_amount THEN
2371 -- top level's total amount is changed, need to update metric record
2372 Ozf_Actmetric_Pvt.Init_ActMetric_Rec(l_metric_rec);
2373 l_metric_rec.activity_metric_id := p_alloc_id;
2374 l_metric_rec.object_version_number := p_alloc_obj_ver;
2375 l_metric_rec.func_actual_value := p_fact_table(l_index).recommend_total_amount;
2376
2377 /* it is important not to do some validation since allocation amount already deducted from fund,
2378 so validation will fail. should < jtf_plsql_api.G_VALID_LEVEL_RECORD CONSTANT NUMBER:= 80.
2379 set to 70 here.
2380 */
2381 Ozf_Actmetric_Pvt.update_Actmetric( p_api_version => p_api_version
2382 , p_init_msg_list => FND_API.G_FALSE
2383 , p_commit => FND_API.G_FALSE
2384 , p_validation_level => 70
2385 , x_return_status => l_return_status
2386 , x_msg_count => x_msg_count
2387 , x_msg_data => x_msg_data
2388 , p_act_metric_rec => l_metric_rec
2389 );
2390 -- dbms_output.put_line(l_full_name || 'update allocation amount returns ' || l_return_status);
2391 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2392 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2393 END IF;
2394 END IF; -- IF l_alloc_amount <> p_fact_table(l_index).recommend_total_amount THEN
2395 END IF; -- IF p_fact_table(l_index).node_id = l_alloc_start_node THEN
2396
2397 -- update table ozf_act_metric_facts
2398 ozf_actmetricfact_Pvt.update_actmetricfact(
2399 p_api_version => p_api_version,
2400 p_init_msg_list => fnd_api.g_false,
2401 p_commit => fnd_api.g_false,
2402 p_validation_level => fnd_api.g_valid_level_full,
2403 x_return_status => l_return_status,
2404 x_msg_count => x_msg_count,
2405 x_msg_data => x_msg_data,
2406 p_act_metric_fact_rec => l_fact_rec
2407 );
2408 -- dbms_output.put_line(l_full_name || 'update fact ' || l_index || ' returns ' || l_return_status);
2409 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2410 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2411 END IF;
2412 END LOOP;
2413
2414 IF p_cascade_flag = 'N' THEN
2415 -- validate worksheet update if it's not cascade
2416 validate_worksheet(p_api_version => p_api_version,
2417 p_init_msg_list => fnd_api.g_false,
2418 p_commit => fnd_api.g_false,
2419 p_validation_level => fnd_api.g_valid_level_full,
2420 p_alloc_id => p_alloc_id,
2421 x_return_status => l_return_status,
2422 x_msg_count => x_msg_count,
2423 x_msg_data => x_msg_data);
2424 -- dbms_output.put_line(l_full_name || 'validate_worksheet returns ' || l_return_status);
2425 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2426 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2427 END IF;
2428
2429 IF l_alloc_status_code = 'PLANNED' AND
2430 l_alloc_action_code = 'CREATE_NEW_BUDGET' THEN
2431 -- update the corresponding child budget's total and holdback amount for published allocation
2432 -- yzhao: 02/04/2003 update only when 'create new budget', do not update if it is 'adding onto existing budget'
2433 FOR l_index IN p_fact_table.FIRST .. p_fact_table.LAST LOOP
2434 ozf_funds_pvt.init_fund_rec (l_fund_rec);
2435 OPEN c_get_fund_info(p_fact_table(l_index).activity_metric_fact_id);
2436 FETCH c_get_fund_info INTO l_fund_rec.fund_id, l_fund_rec.object_version_number;
2437 CLOSE c_get_fund_info;
2438 l_fund_rec.original_budget := p_fact_table(l_index).recommend_total_amount;
2439 l_fund_rec.holdback_amt := p_fact_table(l_index).recommend_hb_amount;
2440 ozf_funds_pvt.update_fund( p_api_version => p_api_version
2441 , p_init_msg_list => fnd_api.g_false
2442 , p_commit => fnd_api.g_false
2443 , p_validation_level => fnd_api.g_valid_level_full
2444 , p_fund_rec => l_fund_rec
2445 , x_return_status => l_return_status
2446 , x_msg_count => x_msg_count
2447 , x_msg_data => x_msg_data
2448 );
2449 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2450 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2451 END IF;
2452 END LOOP;
2453 END IF;
2454
2455 ELSE
2456 -- sort passed in fact records by level depepth ascendantly
2457 sort_changes_by_level(p_fact_table => p_fact_table,
2458 x_sorted_fact_ids => l_sorted_fact_ids,
2459 x_return_status => l_return_status,
2460 x_msg_count => x_msg_count,
2461 x_msg_data => x_msg_data);
2462 -- dbms_output.put_line(l_full_name || ' sort fact changes by level returns ' || l_return_status);
2463 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2464 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2465 END IF;
2466
2467 -- cascade recommend total and holdback amount changes.
2468 -- start from the toppest level. If update multiple levels under the same node, higher level change superceds lower level change
2469 FOR I IN NVL(l_sorted_fact_ids.FIRST, 1) .. NVL(l_sorted_fact_ids.LAST, -1) LOOP
2470 l_not_visited_flag := TRUE;
2471 FOR J IN NVL(l_visited_node_set.FIRST, 1) .. NVL(l_visited_node_set.LAST, -1) LOOP
2472 IF l_visited_node_set(J) = l_sorted_fact_ids(I).fact_id THEN
2473 -- this node has already been cascaded, so its ancester's change supercedes its
2474 l_not_visited_flag := FALSE;
2475 EXIT;
2476 END IF;
2477 END LOOP;
2478
2479 IF l_not_visited_flag THEN
2480 cascade_down_subtree(p_api_version => p_api_version,
2481 p_alloc_id => p_alloc_id,
2482 p_alloc_status_code => l_alloc_status_code,
2483 p_alloc_action_code => l_alloc_action_code,
2484 p_start_fact_id => l_sorted_fact_ids(I).fact_id,
2485 x_visited_node_set => l_new_visited_node_set,
2486 x_return_status => l_return_status,
2487 x_msg_count => x_msg_count,
2488 x_msg_data => x_msg_data);
2489 -- dbms_output.put_line(l_full_name || ' cascade down subtree returns ' || l_return_status || ' subtree node count=' || l_new_visited_node_set.COUNT);
2490 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2491 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2492 END IF;
2493
2494 l_index := NVL(l_visited_node_set.LAST, 0);
2495 FOR J IN NVL(l_new_visited_node_set.FIRST, 1) .. NVL(l_new_visited_node_set.LAST, -1) LOOP
2496 -- append new visited nodes
2497 l_index := l_index + 1;
2498 l_visited_node_set(l_index) := l_new_visited_node_set(J);
2499 END LOOP;
2500 END IF; -- IF NOT l_not_visited_flag THEN
2501 END LOOP;
2502
2503 END IF;
2504
2505 -- update base total and holdback percentage according to new recommendation amounts
2506 update_basepct_info( p_api_version => p_api_version,
2507 p_alloc_id => p_alloc_id,
2508 x_return_status => l_return_status,
2509 x_msg_count => x_msg_count,
2510 x_msg_data => x_msg_data);
2511 -- dbms_output.put_line(l_full_name || 'update worksheet percentage returns ' || l_return_status);
2512 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2513 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2514 END IF;
2515
2516 IF p_commit = FND_API.G_TRUE THEN
2517 commit;
2518 END IF;
2519
2520 EXCEPTION
2521 WHEN OTHERS THEN
2522 ROLLBACK TO update_worksheet_amount_sp;
2523 -- dbms_output.put_line(l_full_name || 'exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
2524 x_return_status := fnd_api.g_ret_sts_unexp_error;
2525 fnd_msg_pub.count_and_get(
2526 p_encoded => fnd_api.g_false
2527 , p_count => x_msg_count
2528 , p_data => x_msg_data);
2529 END update_worksheet_amount;
2530
2531
2532 ----------------------------------------------------------------------------------------
2533 -- This Procedure will activate one fact record of allocation --
2534 -- Private api called by activate_allocation() and approve_levels() --
2535 -- Validation: all validation should already be done by caller --
2536 -- Action: set the allocation fact and corresponding budget to active --
2537 -- send notification to child budget owner --
2538 ----------------------------------------------------------------------------------------
2539 ---------------------------------PARAMETERS---------------------------------------------
2540 -- p_child_fact_id the child fact to be approved --
2541 -- p_child_fact_obj_ver --
2542 -- p_approved_total approved total amount --
2543 -- p_approved_hb approved holdback amount --
2544 ----------------------------------------------------------------------------------------
2545 Procedure activate_one_node(p_api_version IN NUMBER := 1.0,
2546 p_alloc_id IN NUMBER,
2547 p_child_fact_id IN NUMBER,
2548 p_child_fact_obj_ver IN NUMBER,
2549 p_approved_total IN NUMBER,
2550 p_approved_hb IN NUMBER,
2551 p_alloc_action_code IN VARCHAR2,
2552 x_return_status OUT NOCOPY VARCHAR2,
2553 x_msg_count OUT NOCOPY NUMBER,
2554 x_msg_data OUT NOCOPY VARCHAR2)
2555 IS
2556 l_child_fund_id NUMBER;
2557 l_child_fund_obj_ver NUMBER;
2558 l_child_fund_owner NUMBER;
2559 l_child_fund_name ozf_funds_all_tl.short_name%TYPE;
2560 l_child_fund_currency ozf_funds_all_b.currency_code_tc%TYPE;
2561 l_child_requested_total NUMBER;
2562 l_notif_subject VARCHAR2(400);
2563 l_notif_body VARCHAR2(4000);
2564 l_return_id NUMBER;
2565 l_tmp_id NUMBER;
2566 l_tmp_char VARCHAR2(1);
2567 l_metric_fact_rec ozf_actmetricfact_Pvt.act_metric_fact_rec_type;
2568 l_fund_rec ozf_funds_pvt.fund_rec_type;
2569 l_fund_type VARCHAR2(30) := NULL;
2570 l_fund_meaning VARCHAR2(240) := NULL;
2571 l_temp_status VARCHAR2(1);
2572
2573 CURSOR c_get_child_info IS
2574 SELECT fund.fund_id, fund.object_version_number, fund.owner, fund.short_name, fund.currency_code_tc
2575 , fact.request_total_amount, fund.fund_type
2576 FROM ozf_funds_all_vl fund, ozf_act_metric_facts_all fact
2577 WHERE fund.fund_id = fact.act_metric_used_by_id
2578 AND fact.activity_metric_fact_id = p_child_fact_id
2579 AND fact.arc_act_metric_used_by = 'FUND';
2580
2581 BEGIN
2582 -- update child node status as ACTIVE
2583 ozf_actmetricfact_pvt.init_actmetricfact_rec(l_metric_fact_rec);
2584 l_metric_fact_rec.activity_metric_fact_id := p_child_fact_id;
2585 l_metric_fact_rec.object_version_number := p_child_fact_obj_ver;
2586 l_metric_fact_rec.status_code := 'ACTIVE';
2587 -- set actual_total_amount, actual_hb_amount, approval_date
2588 l_metric_fact_rec.actual_total_amount := p_approved_total;
2589 l_metric_fact_rec.actual_hb_amount := NVL(p_approved_hb, 0);
2590 l_metric_fact_rec.approval_date := sysdate;
2591 ozf_actmetricfact_Pvt.Update_ActMetricFact( p_api_version => p_api_version
2592 , p_init_msg_list => fnd_api.g_true
2593 , p_commit => fnd_api.g_false
2594 , p_validation_level => fnd_api.g_valid_level_full
2595 , p_act_metric_fact_rec => l_metric_fact_rec
2596 , x_return_status => x_return_status
2597 , x_msg_count => x_msg_count
2598 , x_msg_data => x_msg_data
2599 );
2600 -- dbms_output.put_line('activate_one_node: update child node fact id=' || p_child_fact_id || ' active returns ' || x_return_status);
2601 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2602 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2603 END IF;
2604
2605 OPEN c_get_child_info;
2606 FETCH c_get_child_info INTO l_child_fund_id, l_child_fund_obj_ver, l_child_fund_owner, l_child_fund_name
2607 , l_child_fund_currency, l_child_requested_total, l_fund_type;
2608 CLOSE c_get_child_info;
2609 IF (p_alloc_action_code = 'CREATE_NEW_BUDGET') THEN
2610 -- activate the child budget
2611 ozf_funds_pvt.init_fund_rec (l_fund_rec);
2612 l_fund_rec.fund_id := l_child_fund_id;
2613 l_fund_rec.object_version_number := l_child_fund_obj_ver;
2614 l_fund_rec.original_budget := p_approved_total;
2615 l_fund_rec.holdback_amt := p_approved_hb;
2616 l_fund_rec.status_code := 'ACTIVE';
2617 ozf_funds_pvt.update_fund( p_api_version => p_api_version
2618 , p_init_msg_list => fnd_api.g_true
2619 , p_commit => fnd_api.g_false
2620 , p_validation_level => fnd_api.g_valid_level_full
2621 , p_fund_rec => l_fund_rec
2622 , x_return_status => x_return_status
2623 , x_msg_count => x_msg_count
2624 , x_msg_data => x_msg_data
2625 );
2626 -- dbms_output.put_line('activate_one_node: update_fund to active returns ' || x_return_status || ' budget id=' || l_fund_rec.fund_id || ' request_amount=' || p_approved_total);
2627 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2628 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2629 END IF;
2630 ELSIF (p_alloc_action_code = 'TRANSFER_TO_BUDGET') THEN
2631 -- create an approved budget transfer from parent to child
2632 transfer_approved_budget(p_api_version => p_api_version
2633 , p_child_fund_id => l_child_fund_id
2634 , p_approved_total => p_approved_total
2635 , x_return_status => x_return_status
2636 , x_msg_count => x_msg_count
2637 , x_msg_data => x_msg_data
2638 );
2639 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2640 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2641 END IF;
2642 END IF;
2643
2644 /* send notification to child budget owner */
2645 IF l_fund_type = 'QUOTA' THEN
2646 ozf_utility_pvt.get_lookup_meaning (p_lookup_type => 'OZF_FUND_NTF_TYPE',
2647 p_lookup_code => 'QUOTA',
2648 x_return_status => l_temp_status,
2649 x_meaning => l_fund_meaning);
2650 ELSIF l_fund_type IN ('FIXED', 'FULLY_ACCRUED') THEN
2651 ozf_utility_pvt.get_lookup_meaning (p_lookup_type => 'OZF_FUND_NTF_TYPE',
2652 p_lookup_code => 'BUDGET',
2653 x_return_status => l_temp_status,
2654 x_meaning => l_fund_meaning);
2655 END IF;
2656 fnd_message.set_name('OZF', 'OZF_NTF_ALLOC_APPROVAL_SUB');
2657 fnd_message.set_token('BUDGET_NAME', l_child_fund_name);
2658 fnd_message.set_token('ALLOC_ID', p_alloc_id);
2659 fnd_message.set_token ('FUND_TYPE', l_fund_meaning, FALSE);
2660 l_notif_subject := substrb(fnd_message.get, 1, 400);
2661 fnd_message.set_name('OZF', 'OZF_NTF_ALLOC_APPROVAL_BODY');
2662 fnd_message.set_token ('BUDGET_NAME', l_child_fund_name);
2663 fnd_message.set_token ('ALLOC_ID', p_alloc_id);
2664 fnd_message.set_token ('ALLOC_STATUS', 'ACTIVE');
2665 fnd_message.set_token ('CURRENCY_CODE', l_child_fund_currency);
2666 fnd_message.set_token ('REQUESTED_TOTAL_AMOUNT', ams_utility_pvt.CurrRound(nvl(l_child_requested_total,0),l_child_fund_currency));
2667 fnd_message.set_token ('APPROVED_TOTAL_AMOUNT', ams_utility_pvt.CurrRound(nvl(p_approved_total,0),l_child_fund_currency));
2668 fnd_message.set_token ('FUND_TYPE', l_fund_meaning, FALSE);
2669 l_notif_body := substrb(fnd_message.get, 1, 4000);
2670
2671 ozf_utility_pvt.send_wf_standalone_message(
2672 p_subject => l_notif_subject
2673 , p_body => l_notif_body
2674 , p_send_to_res_id => l_child_fund_owner
2675 , x_notif_id => l_return_id
2676 , x_return_status => x_return_status
2677 );
2678 -- dbms_output.put_line('activate_one_node: send notificaiton returns ' || x_return_status);
2679 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2680 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2681 END IF;
2682
2683 EXCEPTION
2684 WHEN OTHERS THEN
2685 -- dbms_output.put_line('activate_one_node: exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
2686 x_return_status := fnd_api.g_ret_sts_unexp_error;
2687 fnd_msg_pub.count_and_get(
2688 p_encoded => fnd_api.g_false
2689 , p_count => x_msg_count
2690 , p_data => x_msg_data);
2691 END activate_one_node;
2692
2693
2694 ----------------------------------------------------------------------------------------
2695 -- This Procedure will activate an allocation. Called when allocation status changed to active --
2696 -- Private api called by update_alloc_status() only --
2697 -- Validation: only the top level user can do the activation --
2698 -- the budget must be active to activate an allocation --
2699 -- only PLANNED allcation can be made active. --
2700 -- Action: set the metric to active --
2701 -- set the top level allocation and budget active, --
2702 -- and create active budget transfer record from root to top level --
2703 -- send notification to child budget owner --
2704 ----------------------------------------------------------------------------------------
2705 ---------------------------------PARAMETERS---------------------------------------------
2706 -- p_alloc_id allocation id in ozf_act_metrics_all table --
2707 ----------------------------------------------------------------------------------------
2708 Procedure activate_allocation(p_api_version IN NUMBER := 1.0,
2709 p_alloc_id IN NUMBER,
2710 p_alloc_obj_ver IN NUMBER,
2711 x_return_status OUT NOCOPY VARCHAR2,
2712 x_msg_count OUT NOCOPY NUMBER,
2713 x_msg_data OUT NOCOPY VARCHAR2)
2714 IS
2715 l_full_name VARCHAR2(120) := g_pkg_name || ': activate_allocation() ';
2716 l_alloc_creator NUMBER;
2717 l_alloc_old_status VARCHAR2(30);
2718 l_alloc_action_code VARCHAR2(30);
2719 l_fund_id NUMBER;
2720 l_fund_status_code VARCHAR2(30);
2721 l_fund_type VARCHAR2(30);
2722 l_child_fund_id NUMBER;
2723 l_child_fund_name ozf_funds_all_tl.short_name%TYPE;
2724 l_fact_id NUMBER;
2725 l_fact_obj_ver NUMBER;
2726 l_recommend_total NUMBER;
2727 l_recommend_hb NUMBER;
2728 l_notif_subject VARCHAR2(400);
2729 l_notif_body VARCHAR2(4000);
2730 l_return_id NUMBER;
2731 l_tmp_id NUMBER;
2732 l_tmp_char VARCHAR2(1);
2733 l_ex_start_node ozf_act_metrics_all.ex_start_node%TYPE;
2734 l_metric_rec ozf_actmetric_pvt.act_metric_rec_type;
2735
2736 CURSOR c_get_metric_info IS
2737 SELECT act_metric_used_by_id, created_by, status_code, action_code, ex_start_node
2738 FROM ozf_act_metrics_all
2739 WHERE activity_metric_id = p_alloc_id;
2740
2741 CURSOR c_get_fund_info(p_fund_id NUMBER) IS
2742 SELECT fu.status_code, fu.fund_type
2743 FROM ozf_funds_all_b fu
2744 WHERE fund_id = p_fund_id;
2745
2746 CURSOR c_get_alloc_topnode IS
2747 SELECT m.activity_metric_fact_id, m.object_version_number
2748 , m.recommend_total_amount, m.recommend_hb_amount
2749 FROM ozf_act_metric_facts_all m
2750 WHERE activity_metric_id = p_alloc_id
2751 AND previous_fact_id IS NULL;
2752
2753 CURSOR c_get_alloc_toplevels(p_previous_fact_id IN NUMBER) IS
2754 SELECT m.activity_metric_fact_id, m.object_version_number
2755 , m.recommend_total_amount, m.recommend_hb_amount
2756 FROM ozf_act_metric_facts_all m
2757 WHERE activity_metric_id = p_alloc_id
2758 AND previous_fact_id = p_previous_fact_id;
2759
2760 BEGIN
2761 IF G_DEBUG THEN
2762 ozf_utility_pvt.debug_message(l_full_name || ': start');
2763 END IF;
2764 x_return_status := fnd_api.g_ret_sts_success;
2765
2766 OPEN c_get_metric_info;
2767 FETCH c_get_metric_info INTO l_fund_id, l_alloc_creator, l_alloc_old_status, l_alloc_action_code, l_ex_start_node;
2768 CLOSE c_get_metric_info;
2769
2770 OPEN c_get_fund_info(l_fund_id);
2771 FETCH c_get_fund_info INTO l_fund_status_code, l_fund_type;
2772 CLOSE c_get_fund_info;
2773
2774 -- only the allocation creator can activate allocation
2775 IF l_alloc_creator <> NVL(fnd_global.user_id, -1) THEN
2776 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2777 IF l_fund_type = 'QUOTA' THEN
2778 FND_MESSAGE.Set_Name ('OZF', 'OZF_TP_ALLOCNOTOWNER_ERROR');
2779 ELSE
2780 FND_MESSAGE.Set_Name ('OZF', 'OZF_FUND_ALLOCNOTOWNER_ERROR');
2781 END IF;
2782 FND_MSG_PUB.Add;
2783 END IF;
2784 x_return_status := FND_API.G_RET_STS_ERROR;
2785 RAISE FND_API.G_EXC_ERROR;
2786 END IF;
2787
2788 -- statuses other than PLANNED are not allowed changed to ACTIVE
2789 IF l_alloc_old_status <> 'PLANNED' THEN
2790 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2791 FND_MESSAGE.Set_Name ('OZF', 'OZF_FUND_ALLOCSTATUS_ERROR');
2792 FND_MSG_PUB.Add;
2793 END IF;
2794 x_return_status := FND_API.G_RET_STS_ERROR;
2795 RAISE FND_API.G_EXC_ERROR;
2796 END IF;
2797
2798
2799
2800 -- can not make allocaiton active if root budget is not active
2801 IF l_fund_status_code <> 'ACTIVE' THEN
2802 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2803 IF l_fund_type = 'QUOTA' THEN
2804 FND_MESSAGE.Set_Name ('OZF', 'OZF_TP_ALLOCACTIVE_ERROR');
2805 ELSE
2806 FND_MESSAGE.Set_Name ('OZF', 'OZF_FUND_ALLOCACTIVE_ERROR');
2807 END IF;
2808 FND_MSG_PUB.Add;
2809 END IF;
2810 x_return_status := FND_API.G_RET_STS_ERROR;
2811 RAISE FND_API.G_EXC_ERROR;
2812 END IF;
2813
2814 /* allocation status 'PLANNED' -> 'ACTIVE' */
2815 Ozf_Actmetric_Pvt.Init_ActMetric_Rec(l_metric_rec);
2816 l_metric_rec.activity_metric_id := p_alloc_id;
2817 l_metric_rec.status_code := 'ACTIVE';
2818 l_metric_rec.object_version_number := p_alloc_obj_ver;
2819 l_metric_rec.act_metric_date := sysdate;
2820 Ozf_Actmetric_Pvt.update_Actmetric( p_api_version => p_api_version
2821 , p_init_msg_list => fnd_api.g_false
2822 , p_commit => fnd_api.g_false
2823 , p_validation_level => fnd_api.g_valid_level_full
2824 , x_return_status => x_return_status
2825 , x_msg_count => x_msg_count
2826 , x_msg_data => x_msg_data
2827 , p_act_metric_rec => l_metric_rec
2828 );
2829 -- dbms_output.put_line(l_full_name || 'set alloc status ACTIVE returns ' || x_return_status);
2830 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2831 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2832 END IF;
2833
2834 l_fact_id := NULL;
2835 l_fact_obj_ver := NULL;
2836 OPEN c_get_alloc_topnode;
2837 FETCH c_get_alloc_topnode INTO l_fact_id, l_fact_obj_ver, l_recommend_total, l_recommend_hb;
2838 CLOSE c_get_alloc_topnode;
2839 IF (l_ex_start_node IS NULL OR l_ex_start_node = 'N') THEN
2840 -- start node is included, so activate the top node
2841 -- dbms_output.put_line(l_full_name || 'top node fact_id=' || l_fact_id || ' ver=' || l_fact_obj_ver);
2842 -- activate top node allocation and budget, create fund request between root budget and top node, send notification to top node owner
2843 activate_one_node(p_api_version => p_api_version,
2844 p_alloc_id => p_alloc_id,
2845 p_child_fact_id => l_fact_id,
2846 p_child_fact_obj_ver => l_fact_obj_ver,
2847 p_approved_total => l_recommend_total,
2848 p_approved_hb => l_recommend_hb,
2849 p_alloc_action_code => l_alloc_action_code,
2850 x_return_status => x_return_status,
2851 x_msg_count => x_msg_count,
2852 x_msg_data => x_msg_data);
2853 -- dbms_output.put_line(l_full_name || 'activate top level node returns ' || x_return_status);
2854 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2855 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2856 END IF;
2857 ELSE
2858 -- start node is excluded, so activate all nodes in the top level
2859 FOR fact_rec IN c_get_alloc_toplevels(l_fact_id) LOOP
2860 -- activate top level allocation and budget, create fund request between root budget and top level node, send notification to top level node owner
2861 activate_one_node(p_api_version => p_api_version,
2862 p_alloc_id => p_alloc_id,
2863 p_child_fact_id => fact_rec.activity_metric_fact_id,
2864 p_child_fact_obj_ver => fact_rec.object_version_number,
2865 p_approved_total => fact_rec.recommend_total_amount,
2866 p_approved_hb => fact_rec.recommend_hb_amount,
2867 p_alloc_action_code => l_alloc_action_code,
2868 x_return_status => x_return_status,
2869 x_msg_count => x_msg_count,
2870 x_msg_data => x_msg_data);
2871 -- dbms_output.put_line(l_full_name || 'activate top level node returns ' || x_return_status);
2872 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2873 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2874 END IF;
2875 END LOOP;
2876 END IF;
2877
2878 EXCEPTION
2879 WHEN OTHERS THEN
2880 -- dbms_output.put_line(l_full_name || 'exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
2881 x_return_status := fnd_api.g_ret_sts_unexp_error;
2882 fnd_msg_pub.count_and_get(
2883 p_encoded => fnd_api.g_false
2884 , p_count => x_msg_count
2885 , p_data => x_msg_data);
2886 END activate_allocation;
2887
2888
2889 ----------------------------------------------------------------------------------------
2890 -- This Procedure will cancel an allocation. Called when allocation status changed to CANCELLED --
2891 -- Private api called by update_alloc_status() only --
2892 -- Validation: only the top level user can do the cancellation --
2893 -- only NEW or PLANNED allcation can be cancelled --
2894 -- Action: set the metric to cancelled --
2895 -- set all facts(if any) status to canncelled --
2896 -- if already published, notify child budget owners of cancellation --
2897 -- if CREATE_NEW_BUDGET, mark all children budget as CANCELLED --
2898 ----------------------------------------------------------------------------------------
2899 ---------------------------------PARAMETERS---------------------------------------------
2900 -- p_alloc_id allocation id in ozf_act_metrics_all table --
2901 ----------------------------------------------------------------------------------------
2902 Procedure cancel_allocation(p_api_version IN NUMBER := 1.0,
2903 p_alloc_id IN NUMBER,
2904 p_alloc_obj_ver IN NUMBER,
2905 x_return_status OUT NOCOPY VARCHAR2,
2906 x_msg_count OUT NOCOPY NUMBER,
2907 x_msg_data OUT NOCOPY VARCHAR2)
2908 IS
2909 l_full_name VARCHAR2(120) := g_pkg_name || ': cancel_allocation() ';
2910 l_alloc_creator NUMBER;
2911 l_alloc_old_status VARCHAR2(30);
2912 l_alloc_action_code VARCHAR2(30);
2913 l_root_fund_id NUMBER;
2914 l_fund_obj_ver ozf_funds_all_b.object_version_number%TYPE;
2915 l_fund_owner ozf_funds_all_b.owner%TYPE;
2916 l_fund_name ozf_funds_all_tl.short_name%TYPE;
2917 l_notif_subject VARCHAR2(400);
2918 l_notif_body VARCHAR2(4000);
2919 l_return_id NUMBER;
2920 l_metric_rec ozf_actmetric_pvt.act_metric_rec_type;
2921 l_metric_fact_rec ozf_actmetricfact_Pvt.act_metric_fact_rec_type;
2922 l_fund_rec ozf_funds_pvt.fund_rec_type;
2923 l_fund_type VARCHAR2(30) := NULL;
2924 l_root_fund_type VARCHAR2(30) := NULL;
2925 l_fund_meaning VARCHAR2(240) := NULL;
2926 l_temp_status VARCHAR2(1);
2927
2928 CURSOR c_get_metric_info IS
2929 SELECT created_by, status_code, action_code, act_metric_used_by_id
2930 FROM ozf_act_metrics_all
2931 WHERE activity_metric_id = p_alloc_id;
2932
2933 CURSOR c_get_all_facts IS
2934 SELECT activity_metric_fact_id, object_version_number, act_metric_used_by_id
2935 FROM ozf_act_metric_facts_all
2936 WHERE activity_metric_id = p_alloc_id;
2937
2938 CURSOR c_get_fund_info(p_fund_id NUMBER) IS
2939 SELECT object_version_number, owner, short_name, fund_type
2940 FROM ozf_funds_all_vl
2941 WHERE fund_id = p_fund_id;
2942
2943 CURSOR c_get_root_fund_info(p_fund_id NUMBER) IS
2944 SELECT fund_type
2945 FROM ozf_funds_all_vl
2946 WHERE fund_id = p_fund_id;
2947
2948 BEGIN
2949 IF G_DEBUG THEN
2950 ozf_utility_pvt.debug_message(l_full_name || ': start');
2951 END IF;
2952 x_return_status := fnd_api.g_ret_sts_success;
2953
2954 OPEN c_get_metric_info;
2955 FETCH c_get_metric_info INTO l_alloc_creator, l_alloc_old_status, l_alloc_action_code, l_root_fund_id;
2956 CLOSE c_get_metric_info;
2957
2958 OPEN c_get_root_fund_info(l_root_fund_id);
2959 FETCH c_get_root_fund_info INTO l_root_fund_type;
2960 CLOSE c_get_root_fund_info;
2961
2962
2963 -- only the allocation creator can activate allocation
2964 IF l_alloc_creator <> NVL(fnd_global.user_id, -1) THEN
2965 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2966 IF l_root_fund_type = 'QUOTA' THEN
2967 FND_MESSAGE.Set_Name ('OZF', 'OZF_TP_ALLOCNOTOWNER_ERROR');
2968 ELSE
2969 FND_MESSAGE.Set_Name ('OZF', 'OZF_FUND_ALLOCNOTOWNER_ERROR');
2970 END IF;
2971 FND_MSG_PUB.Add;
2972 END IF;
2973 x_return_status := FND_API.G_RET_STS_ERROR;
2974 RAISE FND_API.G_EXC_ERROR;
2975 END IF;
2976
2977 -- statuses other than NEW or PLANNED are not allowed changed to CANCELLED
2978 IF l_alloc_old_status <> 'NEW' AND l_alloc_old_status <> 'PLANNED' THEN
2979 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2980 FND_MESSAGE.Set_Name ('OZF', 'OZF_FUND_ALLOCSTATUS_ERROR');
2981 FND_MSG_PUB.Add;
2982 END IF;
2983 x_return_status := FND_API.G_RET_STS_ERROR;
2984 RAISE FND_API.G_EXC_ERROR;
2985 END IF;
2986
2987 /* allocation status 'NEW' or 'PLANNED' -> 'CANCELLED' */
2988 Ozf_Actmetric_Pvt.Init_ActMetric_Rec(l_metric_rec);
2989 l_metric_rec.activity_metric_id := p_alloc_id;
2990 l_metric_rec.status_code := 'CANCELLED';
2991 l_metric_rec.object_version_number := p_alloc_obj_ver;
2992 l_metric_rec.act_metric_date := sysdate;
2993 Ozf_Actmetric_Pvt.update_Actmetric( p_api_version => p_api_version
2994 , p_init_msg_list => fnd_api.g_false
2995 , p_commit => fnd_api.g_false
2996 , p_validation_level => fnd_api.g_valid_level_full
2997 , x_return_status => x_return_status
2998 , x_msg_count => x_msg_count
2999 , x_msg_data => x_msg_data
3000 , p_act_metric_rec => l_metric_rec
3001 );
3002 -- dbms_output.put_line(l_full_name || 'set alloc status CANCELLED returns ' || x_return_status);
3003 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3004 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3005 END IF;
3006
3007 FOR fact_rec IN c_get_all_facts LOOP
3008 -- update child fact status as CANCELLED
3009 ozf_actmetricfact_pvt.init_actmetricfact_rec(l_metric_fact_rec);
3010 l_metric_fact_rec.activity_metric_fact_id := fact_rec.activity_metric_fact_id;
3011 l_metric_fact_rec.object_version_number := fact_rec.object_version_number;
3012 l_metric_fact_rec.status_code := 'CANCELLED';
3013 ozf_actmetricfact_Pvt.Update_ActMetricFact( p_api_version => p_api_version
3014 , p_init_msg_list => fnd_api.g_true
3015 , p_commit => fnd_api.g_false
3016 , p_validation_level => fnd_api.g_valid_level_full
3017 , p_act_metric_fact_rec => l_metric_fact_rec
3018 , x_return_status => x_return_status
3019 , x_msg_count => x_msg_count
3020 , x_msg_data => x_msg_data
3021 );
3022 -- dbms_output.put_line(l_full_name || ': update child node fact id=' || fact_rec.activity_metric_fact_id || ' cancelled returns ' || x_return_status);
3023 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3024 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3025 END IF;
3026
3027 -- if allocation already published, notify child budget owners
3028 -- if new budgets already created, update budgets as CANCELLED
3029 IF (l_alloc_old_status = 'PLANNED' AND fact_rec.act_metric_used_by_id <> l_root_fund_id) THEN
3030 -- however, do not cancel root budget. This is possible only when exclude_start_node is checked
3031 OPEN c_get_fund_info(fact_rec.act_metric_used_by_id);
3032 FETCH c_get_fund_info INTO l_fund_obj_ver, l_fund_owner, l_fund_name, l_fund_type;
3033 CLOSE c_get_fund_info;
3034 IF (l_alloc_action_code = 'CREATE_NEW_BUDGET') THEN
3035 -- cancell the child budget
3036 ozf_funds_pvt.init_fund_rec (l_fund_rec);
3037 l_fund_rec.fund_id := fact_rec.act_metric_used_by_id;
3038 l_fund_rec.object_version_number := l_fund_obj_ver;
3039 l_fund_rec.status_code := 'CANCELLED';
3040 ozf_funds_pvt.update_fund( p_api_version => p_api_version
3041 , p_init_msg_list => fnd_api.g_true
3042 , p_commit => fnd_api.g_false
3043 , p_validation_level => fnd_api.g_valid_level_full
3044 , p_fund_rec => l_fund_rec
3045 , x_return_status => x_return_status
3046 , x_msg_count => x_msg_count
3047 , x_msg_data => x_msg_data
3048 );
3049 -- dbms_output.put_line(l_full_name || ': update_fund to cancelled returns ' || x_return_status || ' budget id=' || l_fund_rec.fund_id);
3050 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3051 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3052 END IF;
3053 END IF;
3054
3055 /* send notification to child budget owner */
3056 IF l_fund_type = 'QUOTA' THEN
3057 ozf_utility_pvt.get_lookup_meaning (p_lookup_type => 'OZF_FUND_NTF_TYPE',
3058 p_lookup_code => 'QUOTA',
3059 x_return_status => l_temp_status,
3060 x_meaning => l_fund_meaning);
3061 ELSIF l_fund_type IN ('FIXED', 'FULLY_ACCRUED') THEN
3062 ozf_utility_pvt.get_lookup_meaning (p_lookup_type => 'OZF_FUND_NTF_TYPE',
3063 p_lookup_code => 'BUDGET',
3064 x_return_status => l_temp_status,
3065 x_meaning => l_fund_meaning);
3066 END IF;
3067 fnd_message.set_name('OZF', 'OZF_NTF_ALLOC_CANCEL_SUB');
3068 fnd_message.set_token('BUDGET_NAME', l_fund_name);
3069 fnd_message.set_token('ALLOC_ID', p_alloc_id);
3070 fnd_message.set_token ('FUND_TYPE', l_fund_meaning, FALSE);
3071 l_notif_subject := substrb(fnd_message.get, 1, 400);
3072 fnd_message.set_name('OZF', 'OZF_NTF_ALLOC_CANCEL_BODY');
3073 fnd_message.set_token ('BUDGET_NAME', l_fund_name);
3074 fnd_message.set_token ('ALLOC_ID', p_alloc_id);
3075 fnd_message.set_token ('ALLOC_STATUS', 'CANCELLED');
3076 fnd_message.set_token ('FUND_TYPE', l_fund_meaning, FALSE);
3077 l_notif_body := substrb(fnd_message.get, 1, 4000);
3078
3079 ozf_utility_pvt.send_wf_standalone_message(
3080 p_subject => l_notif_subject
3081 , p_body => l_notif_body
3082 , p_send_to_res_id => l_fund_owner
3083 , x_notif_id => l_return_id
3084 , x_return_status => x_return_status
3085 );
3086 -- dbms_output.put_line(l_full_name || ': send notificaiton returns ' || x_return_status);
3087 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3088 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3089 END IF;
3090 END IF; -- IF (l_alloc_old_status == 'PLANNED') THEN
3091 END LOOP;
3092
3093 EXCEPTION
3094 WHEN OTHERS THEN
3095 -- dbms_output.put_line(l_full_name || 'exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
3096 x_return_status := fnd_api.g_ret_sts_unexp_error;
3097 fnd_msg_pub.count_and_get(
3098 p_encoded => fnd_api.g_false
3099 , p_count => x_msg_count
3100 , p_data => x_msg_data);
3101 END cancel_allocation;
3102
3103
3104 ---------------------------------------------------------------------
3105 -- PROCEDURE
3106 --- update_alloc_status
3107 --
3108 -- PURPOSE
3109 -- Update allocation status
3110 -- public api called by worksheet page update button
3111 --
3112 -- HISTORY
3113 -- 09/23/02 yzhao Created.
3114 --
3115 -- PARAMETERS
3116 ---------------------------------------------------------------------
3117 PROCEDURE update_alloc_status(
3118 p_api_version IN NUMBER
3119 , p_init_msg_list IN VARCHAR2
3120 , p_commit IN VARCHAR2
3121 , p_validation_level IN NUMBER
3122 , p_alloc_id IN NUMBER
3123 , p_alloc_status IN VARCHAR2
3124 , p_alloc_obj_ver IN NUMBER
3125 , x_return_status OUT NOCOPY VARCHAR2
3126 , x_msg_count OUT NOCOPY NUMBER
3127 , x_msg_data OUT NOCOPY VARCHAR2
3128 )
3129 IS
3130 l_old_alloc_status VARCHAR2(30);
3131 l_valid_change BOOLEAN;
3132
3133 CURSOR c_get_alloc_status IS
3134 SELECT status_code
3135 FROM ozf_act_metrics_all
3136 WHERE activity_metric_id = p_alloc_id;
3137 BEGIN
3138 SAVEPOINT update_alloc_status_sp;
3139 x_return_status := FND_API.G_RET_STS_SUCCESS;
3140
3141 IF fnd_api.to_boolean(p_init_msg_list) THEN
3142 fnd_msg_pub.initialize;
3143 END IF;
3144
3145 OPEN c_get_alloc_status;
3146 FETCH c_get_alloc_status INTO l_old_alloc_status;
3147 CLOSE c_get_alloc_status;
3148
3149 -- dbms_output.put_line('zy: update_alloc_status(): old_status=' || l_old_alloc_status || ' new_status=' || p_alloc_status);
3150 IF (p_alloc_status = l_old_alloc_status) THEN
3151 -- no status change, does nothing.
3152 RETURN;
3153 END IF;
3154
3155 l_valid_change := FALSE;
3156 -- Following status are allowed: NEW -> CANCELLED; PLANNED -> ACTIVE/CANCELLED
3157 IF l_old_alloc_status = 'NEW' THEN
3158 IF p_alloc_status = 'ACTIVE' THEN
3159 -- workaround: NEW -> ACTIVE is not allowed. But not throw error, just do nothing.
3160 l_valid_change := TRUE;
3161 ELSIF p_alloc_status = 'CANCELLED' THEN
3162 l_valid_change := TRUE;
3163 cancel_allocation(p_api_version => p_api_version,
3164 p_alloc_id => p_alloc_id,
3165 p_alloc_obj_ver => p_alloc_obj_ver,
3166 x_return_status => x_return_status,
3167 x_msg_count => x_msg_count,
3168 x_msg_data => x_msg_data);
3169 END IF;
3170 ELSIF l_old_alloc_status = 'PLANNED' THEN
3171 IF p_alloc_status = 'ACTIVE' THEN
3172 l_valid_change := TRUE;
3173 activate_allocation(p_api_version => p_api_version,
3174 p_alloc_id => p_alloc_id,
3175 p_alloc_obj_ver => p_alloc_obj_ver,
3176 x_return_status => x_return_status,
3177 x_msg_count => x_msg_count,
3178 x_msg_data => x_msg_data);
3179 ELSIF p_alloc_status = 'CANCELLED' THEN
3180 l_valid_change := TRUE;
3181 cancel_allocation(p_api_version => p_api_version,
3182 p_alloc_id => p_alloc_id,
3183 p_alloc_obj_ver => p_alloc_obj_ver,
3184 x_return_status => x_return_status,
3185 x_msg_count => x_msg_count,
3186 x_msg_data => x_msg_data);
3187 END IF;
3188 END IF;
3189
3190 IF not l_valid_change THEN
3191 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3192 FND_MESSAGE.Set_Name ('OZF', 'OZF_FUND_ALLOCSTATUS_ERROR');
3193 FND_MSG_PUB.Add;
3194 END IF;
3195 x_return_status := FND_API.G_RET_STS_ERROR;
3196 RAISE FND_API.G_EXC_ERROR;
3197 END IF;
3198
3199 IF p_commit = FND_API.G_TRUE THEN
3200 commit;
3201 END IF;
3202
3203 EXCEPTION
3204 WHEN OTHERS THEN
3205 ROLLBACK TO update_alloc_status_sp;
3206 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3207 -- dbms_output.put_line('update_alloc_status: exception - ' || substr(sqlerrm, 1, 200));
3208 fnd_msg_pub.count_and_get(
3209 p_encoded => fnd_api.g_false
3210 , p_count => x_msg_count
3211 , p_data => x_msg_data);
3212 END update_alloc_status;
3213
3214
3215 ----------------------------------------------------------------------------------------
3216 -- This Procedure will approve a published allocation called by bottom-up budgeting --
3217 -- the approver's fact record must be active to approve its children --
3218 -- approve all levels below, or next level only --
3219 -- create budget transfer record --
3220 -- update child node status as 'ACTIVE' --
3221 -- send notification to child budget owner --
3222 ----------------------------------------------------------------------------------------
3223 ---------------------------------PARAMETERS---------------------------------------------
3224 -- p_approver_fact_id the approver's fact id. null means approver is the root budget --
3225 -- p_approve_all_flag Y - approve all levels below; N - approve the next level only --
3226 -- p_factid_table children fact ids to be approved --
3227 ----------------------------------------------------------------------------------------
3228 Procedure approve_levels(p_api_version IN NUMBER,
3229 p_init_msg_list IN VARCHAR2,
3230 p_commit IN VARCHAR2,
3231 p_validation_level IN NUMBER,
3232 p_approver_factid IN NUMBER,
3233 p_approve_all_flag IN VARCHAR2,
3234 p_factid_table IN factid_table_type,
3235 x_return_status OUT NOCOPY VARCHAR2,
3236 x_msg_count OUT NOCOPY NUMBER,
3237 x_msg_data OUT NOCOPY VARCHAR2
3238 )
3239 IS
3240 l_return_status VARCHAR2(2);
3241 l_alloc_action_code VARCHAR2(30);
3242 l_approver_status VARCHAR2(30) := null;
3243 l_approved_total NUMBER;
3244 l_approved_hb NUMBER;
3245 l_parent_fund_id NUMBER;
3246 l_parent_fact_id NUMBER;
3247 l_fund_id NUMBER;
3248 l_fact_id NUMBER;
3249 l_alloc_id NUMBER;
3250 l_fact_status VARCHAR2(30);
3251 l_recommend_total NUMBER;
3252 l_recommend_hb NUMBER;
3253 l_request_total NUMBER;
3254 l_request_hb NUMBER;
3255
3256 CURSOR c_get_alloc_status(p_fact_id NUMBER) IS
3257 SELECT status_code, action_code, activity_metric_id
3258 FROM ozf_act_metrics_all
3259 WHERE activity_metric_id = (SELECT activity_metric_id
3260 FROM ozf_act_metric_facts_all
3261 WHERE activity_metric_fact_id = p_fact_id);
3262
3263 CURSOR c_get_fact_status(p_fact_id NUMBER) IS
3264 SELECT status_code
3265 FROM ozf_act_metric_facts_all
3266 WHERE activity_metric_fact_id = p_fact_id;
3267
3268 CURSOR c_get_one_level(p_fact_id NUMBER) IS
3269 SELECT status_code, act_metric_used_by_id, previous_fact_id
3270 , recommend_total_amount, recommend_hb_amount
3271 , request_total_amount, request_hb_amount
3272 FROM ozf_act_metric_facts_all
3273 WHERE activity_metric_fact_id = p_fact_id
3274 AND previous_fact_id = p_approver_factid; -- to guarantee it approves next level only
3275
3276 CURSOR c_get_all_levels(p_fact_id NUMBER) IS
3277 SELECT activity_metric_fact_id, object_version_number
3278 , status_code, act_metric_used_by_id, previous_fact_id
3279 , recommend_total_amount, recommend_hb_amount
3280 , request_total_amount, request_hb_amount
3281 FROM ozf_act_metric_facts_all
3282 CONNECT BY prior activity_metric_fact_id = previous_fact_id
3283 START WITH previous_fact_id = p_fact_id
3284 ORDER BY level_depth;
3285
3286 BEGIN
3287 SAVEPOINT approve_levels_sp;
3288 IF G_DEBUG THEN
3289 ozf_utility_pvt.debug_message('approve_levels(): start');
3290 END IF;
3291 x_return_status := fnd_api.g_ret_sts_success;
3292
3293 IF p_factid_table.COUNT = 0 THEN
3294 RETURN;
3295 END IF;
3296
3297 IF fnd_api.to_boolean(p_init_msg_list) THEN
3298 fnd_msg_pub.initialize;
3299 END IF;
3300
3301 OPEN c_get_alloc_status(p_factid_table(p_factid_table.FIRST).fact_id);
3302 FETCH c_get_alloc_status INTO l_approver_status, l_alloc_action_code, l_alloc_id;
3303 CLOSE c_get_alloc_status;
3304
3305 IF (p_approver_factid IS NOT NULL) THEN
3306 -- root budget is not the approver, so get actual approver fact status
3307 OPEN c_get_fact_status(p_approver_factid);
3308 FETCH c_get_fact_status INTO l_approver_status;
3309 CLOSE c_get_fact_status;
3310 END IF;
3311
3312 -- approver's status must be active to approve allocation
3313 IF l_approver_status <> 'ACTIVE' THEN
3314 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3315 FND_MESSAGE.Set_Name ('OZF', 'OZF_FUND_ALLOCAPPROVE_ERROR');
3316 FND_MSG_PUB.Add;
3317 END IF;
3318 x_return_status := FND_API.G_RET_STS_ERROR;
3319 RAISE FND_API.G_EXC_ERROR;
3320 END IF;
3321
3322 FOR I IN p_factid_table.FIRST .. p_factid_table.LAST LOOP
3323 l_fact_status := NULL;
3324 OPEN c_get_one_level(p_factid_table(I).fact_id);
3325 FETCH c_get_one_level
3326 INTO l_fact_status, l_fund_id, l_parent_fact_id
3327 , l_recommend_total, l_recommend_hb
3328 , l_request_total, l_request_hb;
3329 CLOSE c_get_one_level;
3330
3331 IF (l_fact_status = 'PLANNED' OR l_fact_status = 'REJECTED' OR l_fact_status = 'SUBMITTED') THEN
3332 IF (p_factid_table(I).approve_recommend = 'Y') THEN
3333 l_approved_total := l_recommend_total;
3334 l_approved_hb := l_recommend_hb;
3335 ELSE
3336 l_approved_total := l_request_total;
3337 l_approved_hb := l_request_hb;
3338 END IF;
3339 -- activate child fact, create fund request between parent budget and child budget, send notification to child budget owner
3340 activate_one_node( p_api_version => p_api_version,
3341 p_alloc_id => l_alloc_id,
3342 p_child_fact_id => p_factid_table(I).fact_id,
3343 p_child_fact_obj_ver => p_factid_table(I).fact_obj_ver,
3344 p_approved_total => l_approved_total,
3345 p_approved_hb => l_approved_hb,
3346 p_alloc_action_code => l_alloc_action_code,
3347 x_return_status => x_return_status,
3348 x_msg_count => x_msg_count,
3349 x_msg_data => x_msg_data);
3350 -- dbms_output.put_line('approve_levels(): one node ' || p_factid_table(I).fact_id || ' returns ' || x_return_status);
3351 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3352 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3353 END IF;
3354
3355 l_fact_status := 'ACTIVE';
3356 END IF;
3357
3358 IF p_approve_all_flag = 'Y' AND l_fact_status = 'ACTIVE' THEN
3359 FOR fact_rec IN c_get_all_levels(p_factid_table(I).fact_id) LOOP
3360 IF (fact_rec.status_code = 'PLANNED' OR fact_rec.status_code = 'REJECTED' OR fact_rec.status_code = 'SUBMITTED') THEN
3361 IF (p_factid_table(I).approve_recommend = 'Y') THEN
3362 l_approved_total := fact_rec.recommend_total_amount;
3363 l_approved_hb := fact_rec.recommend_hb_amount;
3364 ELSE
3365 l_approved_total := fact_rec.request_total_amount;
3366 l_approved_hb := fact_rec.request_hb_amount;
3367 END IF;
3368 -- activate child fact, create fund request between parent budget and child budget, send notification to child budget owner
3369 activate_one_node(p_api_version => p_api_version,
3370 p_alloc_id => l_alloc_id,
3371 p_child_fact_id => fact_rec.activity_metric_fact_id,
3372 p_child_fact_obj_ver => fact_rec.object_version_number,
3373 p_approved_total => l_approved_total,
3374 p_approved_hb => l_approved_hb,
3375 p_alloc_action_code => l_alloc_action_code,
3376 x_return_status => x_return_status,
3377 x_msg_count => x_msg_count,
3378 x_msg_data => x_msg_data);
3379 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3381 END IF;
3382 END IF;
3383 END LOOP;
3384 END IF;
3385 END LOOP;
3386
3387 EXCEPTION
3388 WHEN OTHERS THEN
3389 -- dbms_output.put_line('approve_levels(): exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
3390 ROLLBACK TO approve_levels_sp;
3391 x_return_status := fnd_api.g_ret_sts_unexp_error;
3392 fnd_msg_pub.count_and_get(
3393 p_encoded => fnd_api.g_false
3394 , p_count => x_msg_count
3395 , p_data => x_msg_data);
3396 END approve_levels;
3397
3398
3399 ----------------------------------------------------------------------------------------
3400 -- This Procedure will submit user's requested total and holdback amount --
3401 -- only allocation in 'PLANNED' or 'REJECTED' status can user submit request --
3402 -- update this node allocation status as 'SUBMITTED' --
3403 -- send notification to parent budget owner --
3404 -- record justificaiton note if any --
3405 ----------------------------------------------------------------------------------------
3406 ---------------------------------PARAMETERS---------------------------------------------
3407 -- p_fact_id fact id --
3408 -- p_fact_obj_ver fact object version number --
3409 -- p_note justification note if any --
3410 ----------------------------------------------------------------------------------------
3411 Procedure submit_request(p_api_version IN NUMBER,
3412 p_init_msg_list IN VARCHAR2,
3413 p_commit IN VARCHAR2,
3414 p_validation_level IN NUMBER,
3415 p_fact_id IN NUMBER,
3416 p_fact_obj_ver IN NUMBER,
3417 p_note IN VARCHAR2,
3418 x_return_status OUT NOCOPY VARCHAR2,
3419 x_msg_count OUT NOCOPY NUMBER,
3420 x_msg_data OUT NOCOPY VARCHAR2)
3421 IS
3422 l_alloc_status VARCHAR2(30);
3423 l_alloc_id NUMBER;
3424 l_parent_fact_id NUMBER;
3425 l_parent_budget_owner NUMBER;
3426 l_fund_name ozf_funds_all_tl.short_name%TYPE;
3427 l_currency_code ozf_funds_all_b.currency_code_tc%TYPE;
3428 l_request_total NUMBER;
3429 l_request_hb NUMBER;
3430 l_return_status VARCHAR2(2);
3431 l_notif_subject VARCHAR2(400);
3432 l_notif_body VARCHAR2(4000);
3433 l_return_id NUMBER;
3434 l_fact_rec Ozf_Actmetricfact_Pvt.act_metric_fact_rec_type;
3435 l_fund_type VARCHAR2(30) := NULL;
3436 l_fund_meaning VARCHAR2(240) := NULL;
3437 l_temp_status VARCHAR2(1);
3438
3439 CURSOR c_get_fact_info IS
3440 SELECT fact.status_code, fact.previous_fact_id, fact.activity_metric_id
3441 , fact.request_total_amount, fact.request_hb_amount, fund.short_name, fund.fund_type
3442 FROM ozf_act_metric_facts_all fact, ozf_funds_all_vl fund
3443 WHERE fact.activity_metric_fact_id = p_fact_id
3444 AND fact.act_metric_used_by_id = fund.fund_id;
3445
3446 CURSOR c_get_fact_budget_owner(p_lfact_id NUMBER) IS
3447 SELECT owner, currency_code_tc
3448 FROM ozf_funds_all_b
3449 WHERE fund_id = (SELECT act_metric_used_by_id
3450 FROM ozf_act_metric_facts_all
3451 WHERE activity_metric_fact_id = p_lfact_id
3452 AND arc_act_metric_used_by = 'FUND');
3453
3454 CURSOR c_get_alloc_budget_owner(p_lalloc_id NUMBER) IS
3455 SELECT owner, currency_code_tc
3456 FROM ozf_funds_all_b
3457 WHERE fund_id = (SELECT act_metric_used_by_id
3458 FROM ozf_act_metrics_all
3459 WHERE activity_metric_id = p_lalloc_id
3460 AND arc_act_metric_used_by = 'FUND');
3461
3462 BEGIN
3463 SAVEPOINT submit_request_sp;
3464 IF G_DEBUG THEN
3465 ozf_utility_pvt.debug_message('submit_request(): start');
3466 END IF;
3467 x_return_status := fnd_api.g_ret_sts_success;
3468
3469 IF fnd_api.to_boolean(p_init_msg_list) THEN
3470 fnd_msg_pub.initialize;
3471 END IF;
3472
3473 OPEN c_get_fact_info;
3474 FETCH c_get_fact_info INTO l_alloc_status, l_parent_fact_id, l_alloc_id, l_request_total, l_request_hb, l_fund_name, l_fund_type;
3475 CLOSE c_get_fact_info;
3476
3477 -- only allocation in 'PLANNED' or 'REJECTED' status can user submit request
3478 -- not returning error message here since submit is not exposted to user from UI for this case
3479 IF (l_alloc_status <> 'PLANNED' AND l_alloc_status <> 'REJECTED') THEN
3480 IF G_DEBUG THEN
3481 ozf_utility_pvt.debug_message('submit_request(): only planned or rejected can submit. status=' || l_alloc_status);
3482 END IF;
3483 RAISE FND_API.G_EXC_ERROR;
3484 END IF;
3485
3486 -- update this node allocation status as 'SUBMITTED'
3487 Ozf_Actmetricfact_Pvt.Init_ActMetricFact_Rec(x_fact_rec => l_fact_rec);
3488 l_fact_rec.activity_metric_fact_id := p_fact_id;
3489 l_fact_rec.object_version_number := p_fact_obj_ver;
3490 l_fact_rec.status_code := 'SUBMITTED';
3491 -- update table ozf_act_metric_facts
3492 ozf_actmetricfact_Pvt.update_actmetricfact(
3493 p_api_version => p_api_version,
3494 p_init_msg_list => fnd_api.g_false,
3495 p_commit => fnd_api.g_false,
3496 p_validation_level => fnd_api.g_valid_level_full,
3497 x_return_status => l_return_status,
3498 x_msg_count => x_msg_count,
3499 x_msg_data => x_msg_data,
3500 p_act_metric_fact_rec => l_fact_rec
3501 );
3502 -- dbms_output.put_line('update fact percentage ' || l_fact_rec.activity_metric_fact_id || ' returns ' || l_return_status);
3503 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3504 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3505 END IF;
3506
3507 -- send notification to parent budget owner
3508 IF l_parent_fact_id IS NULL THEN
3509 OPEN c_get_alloc_budget_owner(l_alloc_id);
3510 FETCH c_get_alloc_budget_owner INTO l_parent_budget_owner, l_currency_code;
3511 CLOSE c_get_alloc_budget_owner;
3512 ELSE
3513 OPEN c_get_fact_budget_owner(l_parent_fact_id);
3514 FETCH c_get_fact_budget_owner INTO l_parent_budget_owner, l_currency_code;
3515 CLOSE c_get_fact_budget_owner;
3516 END IF;
3517
3518 -- create note
3519 IF p_note IS NOT NULL THEN
3520 jtf_notes_pub.create_note (
3521 p_api_version => 1.0
3522 ,p_source_object_id => p_fact_id
3523 ,p_source_object_code => 'AMS_ALCT'
3524 ,p_notes => p_note
3525 ,p_note_status => NULL
3526 ,p_entered_by => fnd_global.user_id
3527 ,p_entered_date => SYSDATE
3528 ,p_last_updated_by => fnd_global.user_id
3529 ,x_jtf_note_id => l_return_id
3530 ,p_note_type => 'AMS_JUSTIFICATION'
3531 ,p_last_update_date => SYSDATE
3532 ,p_creation_date => SYSDATE
3533 ,x_return_status => l_return_status
3534 ,x_msg_count => x_msg_count
3535 ,x_msg_data => x_msg_data
3536 );
3537
3538 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3539 RAISE fnd_api.g_exc_unexpected_error;
3540 END IF;
3541 END IF;
3542
3543 /* send request notice to parent budget owner */
3544 IF l_fund_type = 'QUOTA' THEN
3545 ozf_utility_pvt.get_lookup_meaning (p_lookup_type => 'OZF_FUND_NTF_TYPE',
3546 p_lookup_code => 'QUOTA',
3547 x_return_status => l_temp_status,
3548 x_meaning => l_fund_meaning);
3549 ELSIF l_fund_type IN ('FIXED', 'FULLY_ACCRUED') THEN
3550 ozf_utility_pvt.get_lookup_meaning (p_lookup_type => 'OZF_FUND_NTF_TYPE',
3551 p_lookup_code => 'BUDGET',
3552 x_return_status => l_temp_status,
3553 x_meaning => l_fund_meaning);
3554 END IF;
3555 l_notif_body := NULL;
3556 fnd_message.set_name('OZF', 'OZF_NTF_ALLOC_SUBMIT_SUB');
3557 fnd_message.set_token('BUDGET_NAME', l_fund_name);
3558 fnd_message.set_token('ALLOC_ID', l_alloc_id);
3559 fnd_message.set_token ('FUND_TYPE', l_fund_meaning, FALSE);
3560 l_notif_subject := substrb(fnd_message.get, 1, 400);
3561 fnd_message.set_name('OZF', 'OZF_NTF_ALLOC_SUBMIT_BODY');
3562 fnd_message.set_token ('BUDGET_NAME', l_fund_name);
3563 fnd_message.set_token('ALLOC_ID', l_alloc_id);
3564 fnd_message.set_token ('CURRENCY_CODE', l_currency_code);
3565 fnd_message.set_token ('TOTAL_AMOUNT', l_request_total);
3566 fnd_message.set_token ('REQUESTOR_NOTE', p_note);
3567 fnd_message.set_token ('FUND_TYPE', l_fund_meaning, FALSE);
3568 l_notif_body := substrb(fnd_message.get, 1, 4000);
3569
3570 ozf_utility_pvt.send_wf_standalone_message(
3571 p_subject => l_notif_subject
3572 , p_body => l_notif_body
3573 , p_send_to_res_id => l_parent_budget_owner
3574 , x_notif_id => l_return_id
3575 , x_return_status => l_return_status
3576 );
3577 -- dbms_output.put_line('submit_request(): send notificaiton returns ' || l_return_status);
3578 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3579 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3580 END IF;
3581
3582 EXCEPTION
3583 WHEN OTHERS THEN
3584 -- dbms_output.put_line('submit_request(): exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
3585 ROLLBACK TO submit_request_sp;
3586 x_return_status := fnd_api.g_ret_sts_unexp_error;
3587 fnd_msg_pub.count_and_get(
3588 p_encoded => fnd_api.g_false
3589 , p_count => x_msg_count
3590 , p_data => x_msg_data);
3591 END submit_request;
3592
3593
3594 ----------------------------------------------------------------------------------------
3595 -- This Procedure will reject user's requested total and holdback amount --
3596 -- only allocation in 'PLANNED' or 'ACTIVE' status can user reject request --
3597 -- called by top or bottom level user --
3598 -- update the child node allocation status as 'REJECTED' --
3599 -- send notification to child budget owners --
3600 ----------------------------------------------------------------------------------------
3601 ---------------------------------PARAMETERS---------------------------------------------
3602 -- p_rejector_fact_id rejector's fact id. If null, means the root budget --
3603 -- p_factid_table child fact ids to be rejected --
3604 ----------------------------------------------------------------------------------------
3605 Procedure reject_request(p_api_version IN NUMBER,
3606 p_init_msg_list IN VARCHAR2,
3607 p_commit IN VARCHAR2,
3608 p_validation_level IN NUMBER,
3609 p_rejector_factid IN NUMBER,
3610 p_factid_table IN factid_table_type,
3611 x_return_status OUT NOCOPY VARCHAR2,
3612 x_msg_count OUT NOCOPY NUMBER,
3613 x_msg_data OUT NOCOPY VARCHAR2)
3614 IS
3615 l_budget_owner NUMBER;
3616 l_budget_name ozf_funds_all_tl.short_name%TYPE;
3617 l_budget_currency ozf_funds_all_b.currency_code_tc%TYPE;
3618 l_alloc_id NUMBER;
3619 l_fact_old_status VARCHAR2(30);
3620 l_request_total NUMBER;
3621 l_request_hb NUMBER;
3622 l_return_status VARCHAR2(2);
3623 l_notif_subject VARCHAR2(400);
3624 l_notif_body VARCHAR2(4000);
3625 l_return_id NUMBER;
3626 l_fact_rec Ozf_Actmetricfact_Pvt.act_metric_fact_rec_type;
3627 l_fund_type VARCHAR2(30) := NULL;
3628 l_fund_meaning VARCHAR2(240) := NULL;
3629 l_temp_status VARCHAR2(1);
3630
3631 CURSOR c_get_fact_info(p_fact_id NUMBER) IS
3632 SELECT fund.owner, fund.short_name, fund.currency_code_tc
3633 , fact.status_code, fact.request_total_amount, fact.request_hb_amount
3634 , fact.activity_metric_id, fund.fund_type
3635 FROM ozf_act_metric_facts_all fact, ozf_funds_all_vl fund
3636 WHERE fact.activity_metric_fact_id = p_fact_id
3637 AND fact.arc_act_metric_used_by = 'FUND'
3638 AND fact.act_metric_used_by_id = fund.fund_id;
3639
3640 BEGIN
3641 SAVEPOINT reject_request_sp;
3642 IF G_DEBUG THEN
3643 ozf_utility_pvt.debug_message('reject_request(): start');
3644 END IF;
3645 x_return_status := fnd_api.g_ret_sts_success;
3646
3647 IF fnd_api.to_boolean(p_init_msg_list) THEN
3648 fnd_msg_pub.initialize;
3649 END IF;
3650
3651 /* only allocation in 'PLANNED' or 'ACTIVE' status can user reject request
3652 IF (l_alloc_status <> 'PLANNED' AND l_alloc_status <> 'APPROVED') THEN
3653 return;
3654 END IF;
3655 */
3656
3657 FOR i IN p_factid_table.FIRST .. p_factid_table.LAST LOOP
3658 -- update this node allocation status as 'REJECTED'
3659
3660 OPEN c_get_fact_info(p_factid_table(i).fact_id);
3661 FETCH c_get_fact_info INTO l_budget_owner, l_budget_name, l_budget_currency
3662 , l_fact_old_status, l_request_total, l_request_hb, l_alloc_id, l_fund_type;
3663 CLOSE c_get_fact_info;
3664
3665 -- do nothing if user rejects an already rejected/active node
3666 IF l_fact_old_status <> 'REJECTED' AND l_fact_old_status <> 'ACTIVE' THEN
3667 Ozf_Actmetricfact_Pvt.Init_ActMetricFact_Rec(x_fact_rec => l_fact_rec);
3668 l_fact_rec.activity_metric_fact_id := p_factid_table(i).fact_id;
3669 l_fact_rec.object_version_number := p_factid_table(i).fact_obj_ver;
3670 l_fact_rec.status_code := 'REJECTED';
3671 -- update table ozf_act_metric_facts
3672 ozf_actmetricfact_Pvt.update_actmetricfact(
3673 p_api_version => p_api_version,
3674 p_init_msg_list => fnd_api.g_false,
3675 p_commit => fnd_api.g_false,
3676 p_validation_level => fnd_api.g_valid_level_full,
3677 x_return_status => l_return_status,
3678 x_msg_count => x_msg_count,
3679 x_msg_data => x_msg_data,
3680 p_act_metric_fact_rec => l_fact_rec
3681 );
3682 -- dbms_output.put_line('update fact as rejected ' || l_fact_rec.activity_metric_fact_id || ' returns ' || l_return_status);
3683 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3684 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3685 END IF;
3686
3687 -- send notification to child budget owner of rejection
3688 IF l_fund_type = 'QUOTA' THEN
3689 ozf_utility_pvt.get_lookup_meaning (p_lookup_type => 'OZF_FUND_NTF_TYPE',
3690 p_lookup_code => 'QUOTA',
3691 x_return_status => l_temp_status,
3692 x_meaning => l_fund_meaning);
3693 ELSIF l_fund_type IN ('FIXED', 'FULLY_ACCRUED') THEN
3694 ozf_utility_pvt.get_lookup_meaning (p_lookup_type => 'OZF_FUND_NTF_TYPE',
3695 p_lookup_code => 'BUDGET',
3696 x_return_status => l_temp_status,
3697 x_meaning => l_fund_meaning);
3698 END IF;
3699 fnd_message.set_name('OZF', 'OZF_NTF_ALLOC_REJECT_SUB');
3700 fnd_message.set_token('BUDGET_NAME', l_budget_name);
3701 fnd_message.set_token('ALLOC_ID', l_alloc_id);
3702 fnd_message.set_token ('FUND_TYPE', l_fund_meaning, FALSE);
3703 l_notif_subject := substrb(fnd_message.get, 1, 400);
3704 fnd_message.set_name('OZF', 'OZF_NTF_ALLOC_REJECT_BODY');
3705 fnd_message.set_token ('BUDGET_NAME', l_budget_name);
3706 fnd_message.set_token('ALLOC_ID', l_alloc_id);
3707 fnd_message.set_token ('ALLOC_STATUS', 'REJECTED');
3708 fnd_message.set_token ('CURRENCY_CODE', l_budget_currency);
3709 fnd_message.set_token ('REQUESTED_TOTAL_AMOUNT', ams_utility_pvt.CurrRound(l_request_total,l_budget_currency));
3710 fnd_message.set_token ('FUND_TYPE', l_fund_meaning, FALSE);
3711 l_notif_body := substrb(fnd_message.get, 1, 4000);
3712
3713 ozf_utility_pvt.send_wf_standalone_message(
3714 p_subject => l_notif_subject
3715 , p_body => l_notif_body
3716 , p_send_to_res_id => l_budget_owner
3717 , x_notif_id => l_return_id
3718 , x_return_status => l_return_status
3719 );
3720 -- dbms_output.put_line('reject_request(): send notificaiton returns ' || l_return_status);
3721 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3722 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3723 END IF;
3724 END IF;
3725 END LOOP;
3726
3727 EXCEPTION
3728 WHEN OTHERS THEN
3729 -- dbms_output.put_line('reject_request(): exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
3730 ROLLBACK TO reject_request_sp;
3731 x_return_status := fnd_api.g_ret_sts_unexp_error;
3732 fnd_msg_pub.count_and_get(
3733 p_encoded => fnd_api.g_false
3734 , p_count => x_msg_count
3735 , p_data => x_msg_data);
3736 END reject_request;
3737
3738
3739 END OZF_Fund_allocations_Pvt;
3740