[Home] [Help]
PACKAGE BODY: APPS.OZF_FUNDRULES_PVT
Source
1 PACKAGE BODY ozf_fundrules_pvt AS
2 /* $Header: ozfvfrub.pls 120.5 2006/05/11 22:11:29 asylvia ship $ */
3 g_pkg_name CONSTANT VARCHAR2(30) := 'Ozf_FundRules_PVT';
4 g_file_name CONSTANT VARCHAR2(30) := 'ozfvfrub.pls';
5 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
6
7
8 ---------------------------------------------------------------------
9 -- PROCEDURE
10 -- check_product_eligibility_exists
11 --
12 -- PURPOSE
13 -- check_product_eligibility_exists
14 -- HISTORY
15 -- 01/15/2001 Mumu Pande Create.
16 -- 11/11/2002 Srinivasa Rudravarapu Modified Process_Offers Method.
17 -- NOTES
18 ---------------------------------------------------------------------
19
20 PROCEDURE check_product_elig_exists(
21 p_fund_id IN NUMBER,
22 x_return_status OUT NOCOPY VARCHAR2) IS
23 l_dummy VARCHAR2(3);
24
25 -- CURSOR for product eligibility
26 CURSOR c_product_elig IS
27 SELECT 1
28 FROM DUAL
29 WHERE EXISTS( SELECT 1
30 FROM ams_act_products
31 WHERE act_product_used_by_id = p_fund_id AND arc_act_product_used_by = 'FUND');
32 BEGIN
33 x_return_status := fnd_api.g_ret_sts_success;
34 -- Fetch the cursor
35 OPEN c_product_elig;
36 FETCH c_product_elig INTO l_dummy;
37 CLOSE c_product_elig;
38
39 IF l_dummy IS NULL THEN
40 ozf_utility_pvt.error_message('OZF_ACCRUAL_NO_PROD');
41 x_return_status := fnd_api.g_ret_sts_error;
42 END IF;
43 END check_product_elig_exists;
44
45
46 ---------------------------------------------------------------------
47 -- PROCEDURE
48 -- check_fund_amount_vs_parent
49 --
50 -- PURPOSE
51 -- Check fund amount against its parent.
52 -- HISTORY
53 -- 01/15/2001 Mumu Pande Create.
54 -- 09/04/2001 Mumu Pande Updated for different currency child
55 -- NOTES
56 ---------------------------------------------------------------------
57 PROCEDURE check_fund_amount_vs_parent(
58 p_parent_id IN NUMBER,
59 p_child_curr IN VARCHAR2,
60 p_original_budget IN NUMBER,
61 x_return_status OUT NOCOPY VARCHAR2) IS
62 -- CURSOR for parent budget amounts
63 CURSOR c_parent_amount IS
64 SELECT original_budget,
65 transfered_in_amt,
66 transfered_out_amt,
67 currency_code_tc
68 FROM ozf_funds_all_vl
69 WHERE fund_id = p_parent_id;
70
71 CURSOR c_parent_type IS
72 SELECT fund_type
73 FROM ozf_funds_all_vl
74 WHERE fund_id = p_parent_id;
75
76 l_parent_fund_type VARCHAR2(30);
77 l_par_original_budget NUMBER;
78 l_par_trans_in_budget NUMBER;
79 l_par_trans_out_budget NUMBER;
80 l_par_curr_code VARCHAR2(30);
81 l_coverted_orig_budget NUMBER;
82 l_rate NUMBER;
83 BEGIN
84 x_return_status := fnd_api.g_ret_sts_success;
85
86 IF p_parent_id IS NULL THEN
87 RETURN;
88 END IF;
89
90 -- Fetch the parent fund amounts
91 OPEN c_parent_amount;
92 FETCH c_parent_amount INTO l_par_original_budget,
93 l_par_trans_in_budget,
94 l_par_trans_out_budget,
95 l_par_curr_code;
96 CLOSE c_parent_amount;
97
98 OPEN c_parent_type;
99 FETCH c_parent_type INTO l_parent_fund_type;
100 CLOSE c_parent_type;
101
102 IF l_par_curr_code = p_child_curr THEN
103 l_coverted_orig_budget := p_original_budget;
104 ELSE
105 ozf_utility_pvt.convert_currency(
106 x_return_status=> x_return_status,
107 p_from_currency=> p_child_curr,
108 p_to_currency=> l_par_curr_code,
109 p_from_amount=> p_original_budget,
110 x_to_amount=> l_coverted_orig_budget,
111 x_rate=> l_rate);
112 END IF;
113
114 IF NVL(l_coverted_orig_budget, 0) >
115 (NVL(l_par_original_budget, 0) +
116 NVL(l_par_trans_in_budget, 0) -
117 NVL(l_par_trans_out_budget, 0)) THEN
118 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
119 IF l_parent_fund_type = 'QUOTA' THEN
120 fnd_message.set_name('OZF', 'OZF_CHILD_EXCESS_QUOTA');
121 ELSE
122 fnd_message.set_name('OZF', 'OZF_CHILD_EXCESS_BUDGET');
123 END IF;
124 fnd_msg_pub.ADD;
125 END IF;
126
127 x_return_status := fnd_api.g_ret_sts_error;
128 END IF;
129 END check_fund_amount_vs_parent;
130
131
132
133 ---------------------------------------------------------------------
134 -- PROCEDURE
135 -- check_fund_type_vs_parent
136 --
137 -- PURPOSE
138 -- Check fund type against its parent.
139 -- HISTORY
140 -- 01/15/2001 Mumu Pande Create.
141 --
142 -- NOTES
143 ---------------------------------------------------------------------
144 PROCEDURE check_fund_type_vs_parent(
145 p_parent_id IN NUMBER,
146 p_fund_type IN VARCHAR2,
147 x_return_status OUT NOCOPY VARCHAR2) IS
148 -- CURSOR for parent fund type
149 CURSOR c_parent_type IS
150 SELECT fund_type
151 FROM ozf_funds_all_vl
152 WHERE fund_id = p_parent_id;
153
154 l_parent_fund_type VARCHAR2(30);
155 BEGIN
156 x_return_status := fnd_api.g_ret_sts_success;
157
158 IF p_parent_id IS NULL THEN
159 RETURN;
160 END IF;
161
162 -- Fetch the parent fund amounts
163 OPEN c_parent_type;
164 FETCH c_parent_type INTO l_parent_fund_type;
165 CLOSE c_parent_type;
166
167 IF p_fund_type <> l_parent_fund_type THEN
168 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
169 IF p_fund_type = 'QUOTA' THEN
170 fnd_message.set_name('OZF', 'OZF_TP_CHILD_WRONG_QUOTA_TYPE');
171 ELSE
172 fnd_message.set_name('OZF', 'OZF_CHILD_WRONG_FUND_TYPE');
173 END IF;
174 fnd_msg_pub.ADD;
175 END IF;
176 x_return_status := fnd_api.g_ret_sts_error;
177 END IF;
178 END check_fund_type_vs_parent;
179
180
181 ---------------------------------------------------------------------
182 -- PROCEDURE
183 -- check_fund_status_vs_parent
184 --
185 -- PURPOSE
186 -- Check fund status(active,draft) against its parent.
187 -- HISTORY
188 -- 01/15/2001 Mumu Pande Create.
189 --
190 -- NOTES
191 ---------------------------------------------------------------------
192 PROCEDURE check_fund_status_vs_parent(
193 p_parent_id IN NUMBER,
194 p_status_code IN VARCHAR2,
195 x_return_status OUT NOCOPY VARCHAR2) IS
196 -- CURSOR for parent status
197 CURSOR c_parent_status IS
198 SELECT status_code, fund_type
199 FROM ozf_funds_all_vl
200 WHERE fund_id = p_parent_id;
201
202 l_parent_fund_status VARCHAR2(30);
203 l_parent_fund_type VARCHAR2(30);
204
205 BEGIN
206 x_return_status := fnd_api.g_ret_sts_success;
207
208
209 IF p_parent_id IS NULL THEN
210 RETURN;
211 END IF;
212
213 -- Fetch the parent fund status
214 OPEN c_parent_status;
215 FETCH c_parent_status INTO l_parent_fund_status, l_parent_fund_type;
216 CLOSE c_parent_status;
217
218 IF p_status_code = 'ACTIVE' THEN
219 -- Check parent fund status
220 IF l_parent_fund_status <> 'ACTIVE' THEN
221 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
222 IF l_parent_fund_type = 'QUOTA' THEN
223 fnd_message.set_name('OZF', 'OZF_TP_ACTIVATE_QUOTA_PARENT');
224 ELSE
225 fnd_message.set_name('OZF', 'OZF_ACTIVATE_FUND_PARENT');
226 END IF;
227 fnd_msg_pub.ADD;
228 END IF;
229
230 x_return_status := fnd_api.g_ret_sts_error;
231 END IF;
232 ELSIF p_status_code = 'ON_HOLD' THEN
233 -- Check parent fund status
234 IF l_parent_fund_status NOT IN ('ON_HOLD', 'ACTIVE') THEN
235 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
236 IF l_parent_fund_type = 'QUOTA' THEN
237 fnd_message.set_name('OZF', 'OZF_TP_ACTIVATE_QUOTA_PARENT');
238 ELSE
239 fnd_message.set_name('OZF', 'OZF_ACTIVATE_FUND_PARENT');
240 END IF;
241 fnd_msg_pub.ADD;
242 END IF;
243
244 x_return_status := fnd_api.g_ret_sts_error;
245 END IF; --status_code
246 END IF;
247 END check_fund_status_vs_parent;
248
249
250 ---------------------------------------------------------------------
251 -- PROCEDURE
252 -- check_fund_dates_vs_parent
253 --
254 -- PURPOSE
255 -- Check fund dates against its parent.
256 -- HISTORY
257 -- 01/15/2001 Mumu Pande Create.
258 --
259 -- NOTES
260 ---------------------------------------------------------------------
261 PROCEDURE check_fund_dates_vs_parent(
262 p_parent_id IN NUMBER,
263 p_start_date_active IN DATE,
264 p_end_date_active IN DATE,
265 x_return_status OUT NOCOPY VARCHAR2) IS
266 -- CURSOR for parent dates
267 CURSOR c_parent_dates IS
268 SELECT start_date_active,
269 end_date_active
270 FROM ozf_funds_all_vl
271 WHERE fund_id = p_parent_id;
272
273 l_parent_start_date DATE;
274 l_parent_end_date DATE;
275 BEGIN
276 x_return_status := fnd_api.g_ret_sts_success;
277
278 IF p_parent_id IS NULL THEN
279 RETURN;
280 END IF;
281
282 -- Fetch the parent fund status
283 OPEN c_parent_dates;
284 FETCH c_parent_dates INTO l_parent_start_date, l_parent_end_date;
285 CLOSE c_parent_dates;
286
287 --Check validity of child fund's effectivity dates w.r.t. the parent fund
288 IF p_start_date_active < l_parent_start_date
289 OR p_end_date_active > l_parent_end_date THEN
290 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
291 fnd_message.set_name('OZF', 'OZF_CHILD_ILLEGAL_DATE');
292 fnd_msg_pub.ADD;
293 END IF;
294
295 x_return_status := fnd_api.g_ret_sts_error;
296 END IF;
297 END check_fund_dates_vs_parent;
298
299
300 ---------------------------------------------------------------------
301 -- PROCEDURE
302 -- check_fund_curr_vs_parent
303 --
304 -- PURPOSE
305 -- Check fund curr against its parent.
306 -- HISTORY
307 -- 01/15/2001 Mumu Pande Create.
308 --
309 -- NOTES
310 ---------------------------------------------------------------------
311 PROCEDURE check_fund_curr_vs_parent(
312 p_parent_id IN NUMBER,
313 p_fund_curr IN VARCHAR2,
314 x_return_status OUT NOCOPY VARCHAR2) IS
315 -- CURSOR for parent fund type
316 CURSOR c_parent_curr IS
317 SELECT currency_code_tc
318 FROM ozf_funds_all_vl
319 WHERE fund_id = p_parent_id;
320
321 l_parent_fund_curr VARCHAR2(30);
322 BEGIN
323 x_return_status := fnd_api.g_ret_sts_success;
324
325 IF p_parent_id IS NULL THEN
326 RETURN;
327 END IF;
328
329 -- Fetch the parent fund amounts
330 OPEN c_parent_curr;
331 FETCH c_parent_curr INTO l_parent_fund_curr;
332 CLOSE c_parent_curr;
333
334 IF p_fund_curr <> l_parent_fund_curr THEN
335 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
336 fnd_message.set_name('OZF', 'OZF_CHILD_ILLEGAL_CURRENCY');
337 fnd_msg_pub.ADD;
338 END IF;
339
340 x_return_status := fnd_api.g_ret_sts_error;
341 END IF;
342 END check_fund_curr_vs_parent;
343
344
345 ---------------------------------------------------------------------
346 -- PROCEDURE
347 -- check_fund_amount_vs_child
348 --
349 -- HISTORY
350 -- 07/28/2000 mpande Created.
351 -- 09/04/2001 Mumu Pande Updated for different currency child
352 ---------------------------------------------------------------------
353 PROCEDURE check_fund_amount_vs_child(
354 p_fund_id IN NUMBER,
355 p_fund_org_amount IN NUMBER,
356 p_fund_tran_in_amount IN NUMBER,
357 p_fund_tran_out_amount IN NUMBER,
358 p_parent_currency IN VARCHAR2,
359 x_return_status OUT NOCOPY VARCHAR2) IS
360 l_api_name CONSTANT VARCHAR2(30) := 'check_amount_type_vs_child';
361 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||
362 '.' ||
363 l_api_name;
364 l_org_budget NUMBER;
365 l_par_total_budget NUMBER;
366 l_coverted_orig_budget NUMBER;
367 l_rate NUMBER;
368
369 CURSOR c_sub_fund IS
370 SELECT short_name AS short_name,
371 original_budget AS original_budget,
372 currency_code_tc AS currency_code
373 FROM ozf_funds_all_vl
374 WHERE parent_fund_id = p_fund_id;
375
376 CURSOR c_parent_type IS
377 SELECT fund_type
378 FROM ozf_funds_all_vl
379 WHERE fund_id = p_fund_id;
380
381 l_parent_fund_type VARCHAR2(30);
382 BEGIN
383 x_return_status := fnd_api.g_ret_sts_success;
384
385 IF p_fund_id IS NULL THEN
386 RETURN;
387 END IF;
388
389 OPEN c_parent_type;
390 FETCH c_parent_type INTO l_parent_fund_type;
391 CLOSE c_parent_type;
392
393 l_org_budget := 0;
394 l_par_total_budget := NVL(p_fund_org_amount, 0) +
395 NVL(p_fund_tran_in_amount, 0) -
396 NVL(p_fund_tran_out_amount, 0);
397 FOR l_sub_rec IN c_sub_fund
398 LOOP
399 IF l_sub_rec.currency_code = p_parent_currency THEN
400 l_coverted_orig_budget := l_sub_rec.original_budget;
401 ELSE
402 ozf_utility_pvt.convert_currency(
403 x_return_status=> x_return_status,
404 p_from_currency=> l_sub_rec.currency_code,
405 p_to_currency=> p_parent_currency,
406 p_from_amount=> l_sub_rec.original_budget,
407 x_to_amount=> l_coverted_orig_budget,
408 x_rate=> l_rate);
409 END IF;
410
411 --- fund amount of the parent cannot be less than the planned added child funds
412 l_org_budget := NVL(l_coverted_orig_budget, 0) +
413 NVL(l_org_budget, 0);
414
415 IF (l_par_total_budget < NVL(l_org_budget, 0)) THEN
416 IF l_parent_fund_type = 'QUOTA' THEN
417 ozf_utility_pvt.error_message(
418 'OZF_PAR_QUOTA_LESS_THAN_CHILD',
419 'FUND_NAME',
420 l_sub_rec.short_name);
421 ELSE
422 ozf_utility_pvt.error_message(
423 'OZF_PAR_BUDGET_LESS_THAN_CHILD',
424 'FUND_NAME',
425 l_sub_rec.short_name);
426 END IF;
427
428 x_return_status := fnd_api.g_ret_sts_error;
429 END IF;
430 END LOOP;
431 END check_fund_amount_vs_child;
432
433 ---------------------------------------------------------------------
434 -- PROCEDURE
435 -- check_fund_dates_vs_child
436 --
437 -- HISTORY
438 -- 07/28/2000 mpande Created.
439 ---------------------------------------------------------------------
440 PROCEDURE check_fund_dates_vs_child(
441 p_fund_id IN NUMBER,
442 p_start_date IN DATE,
443 p_end_date IN DATE,
444 x_return_status OUT NOCOPY VARCHAR2) IS
445 l_api_name CONSTANT VARCHAR2(30) := 'check_fund_dates_vs_child';
446 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||
447 '.' ||
448 l_api_name;
449
450 CURSOR c_sub_fund IS
451 SELECT short_name AS short_name,
452 start_date_active AS start_date,
453 end_date_active AS end_date,
454 fund_type AS fund_type
455 FROM ozf_funds_all_vl
456 WHERE parent_fund_id = p_fund_id;
457 BEGIN
458 x_return_status := fnd_api.g_ret_sts_success;
459
460 IF p_fund_id IS NULL THEN
461 RETURN;
462 END IF;
463
464 FOR l_sub_rec IN c_sub_fund
465 LOOP
466 IF p_start_date > l_sub_rec.start_date THEN
467 x_return_status := fnd_api.g_ret_sts_error;
468 IF l_sub_rec.fund_type = 'QUOTA' THEN
469 ozf_utility_pvt.error_message('OZF_TP_START_AFT_SUB_START', 'FUND_NAME', l_sub_rec.short_name);
470 ELSE
471 ozf_utility_pvt.error_message('OZF_FUND_START_AFT_SUB_START', 'FUND_NAME', l_sub_rec.short_name);
472 END IF;
473 ELSIF p_start_date > l_sub_rec.end_date THEN
474 x_return_status := fnd_api.g_ret_sts_error;
475 IF l_sub_rec.fund_type = 'QUOTA' THEN
476 ozf_utility_pvt.error_message('OZF_TP_START_AFT_SUB_END', 'FUND_NAME', l_sub_rec.short_name);
477 ELSE
478 ozf_utility_pvt.error_message('OZF_FUND_START_AFT_SUB_END', 'FUND_NAME', l_sub_rec.short_name);
479 END IF;
480 END IF;
481
482 IF p_end_date < l_sub_rec.end_date THEN
483 x_return_status := fnd_api.g_ret_sts_error;
484 IF l_sub_rec.fund_type = 'QUOTA' THEN
485 ozf_utility_pvt.error_message('OZF_TP_QUOTA_END_BEF_SUB_END', 'FUND_NAME', l_sub_rec.short_name);
486 ELSE
487 ozf_utility_pvt.error_message('OZF_FUND_END_BEF_SUB_END', 'FUND_NAME', l_sub_rec.short_name);
488 END IF;
489 ELSIF p_end_date < l_sub_rec.start_date THEN
490 x_return_status := fnd_api.g_ret_sts_error;
491 IF l_sub_rec.fund_type = 'QUOTA' THEN
492 ozf_utility_pvt.error_message('OZF_TP_END_BEF_SUB_START', 'FUND_NAME', l_sub_rec.short_name);
493 ELSE
494 ozf_utility_pvt.error_message('OZF_FUND_END_BEF_SUB_START', 'FUND_NAME', l_sub_rec.short_name);
495 END IF;
496 END IF;
497 END LOOP;
498 END check_fund_dates_vs_child;
499
500
501 ---------------------------------------------------------------------
502 -- PROCEDURE
503 -- check_fund_types_vs_child
504 --
505 -- HISTORY
506 -- 07/28/2000 mpande Created.
507 ---------------------------------------------------------------------
508 PROCEDURE check_fund_type_vs_child(
509 p_fund_id IN NUMBER,
510 p_fund_type IN VARCHAR2,
511 x_return_status OUT NOCOPY VARCHAR2) IS
512 l_api_name CONSTANT VARCHAR2(30) := 'check_fund_type_vs_child';
513 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||
514 '.' ||
515 l_api_name;
516
517 CURSOR c_sub_fund IS
518 SELECT short_name AS short_name,
519 fund_type AS fund_type
520 FROM ozf_funds_all_vl
521 WHERE parent_fund_id = p_fund_id;
522 BEGIN
523 x_return_status := fnd_api.g_ret_sts_success;
524
525 IF p_fund_id IS NULL THEN
526 RETURN;
527 END IF;
528
529 FOR l_sub_rec IN c_sub_fund
530 LOOP
531 --- fund type of the parent and child fund should be the same
532 IF p_fund_type <> l_sub_rec.fund_type THEN
533 IF l_sub_rec.fund_type = 'QUOTA' THEN
534 ozf_utility_pvt.error_message('OZF_TP_PAR_MISMATCH_CHILD_TYPE', 'FUND_NAME', l_sub_rec.short_name);
535 ELSE
536 ozf_utility_pvt.error_message('OZF_PAR_MISMATCH_CHILD_TYPE', 'FUND_NAME', l_sub_rec.short_name);
537 END IF;
538 x_return_status := fnd_api.g_ret_sts_error;
539 END IF;
540 END LOOP;
541 END check_fund_type_vs_child;
542
543
544 ---------------------------------------------------------------------
545 -- PROCEDURE
546 -- check_fund_curr_vs_child
547 --
548 -- HISTORY
549 -- 07/28/2000 mpande Created.
550 ---------------------------------------------------------------------
551 PROCEDURE check_fund_curr_vs_child(
552 p_fund_id IN NUMBER,
553 p_fund_curr IN VARCHAR2,
554 x_return_status OUT NOCOPY VARCHAR2) IS
555 l_api_name CONSTANT VARCHAR2(30) := 'check_fund_curr_vs_child';
556 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||
557 '.' ||
558 l_api_name;
559
560 CURSOR c_sub_fund IS
561 SELECT short_name AS short_name,
562 currency_code_tc AS fund_curr,
563 fund_type AS fund_type
564 FROM ozf_funds_all_vl
565 WHERE parent_fund_id = p_fund_id;
566 BEGIN
567 x_return_status := fnd_api.g_ret_sts_success;
568
569 IF p_fund_id IS NULL THEN
570 RETURN;
571 END IF;
572
573 FOR l_sub_rec IN c_sub_fund
574 LOOP
575 --- fund curr of the parent and child fund should be the same
576 IF p_fund_curr <> l_sub_rec.fund_curr THEN
577 IF l_sub_rec.fund_type = 'QUOTA' THEN
578 ozf_utility_pvt.error_message('OZF_TP_PAR_MISMATCH_CHILD_CURR', 'FUND_NAME', l_sub_rec.short_name);
579 ELSE
580 ozf_utility_pvt.error_message('OZF_PAR_MISMATCH_CHILD_CURR', 'FUND_NAME', l_sub_rec.short_name);
581 END IF;
582 x_return_status := fnd_api.g_ret_sts_error;
583 END IF;
584 END LOOP;
585 END check_fund_curr_vs_child;
586
587
588 -----------------------------------------------------------------------
589 -- PROCEDURE
590 -- check_fund_calendar
591 --
592 -- HISTORY
593 -- 01/28/2001 mpande Created.
594 -----------------------------------------------------------------------
595 PROCEDURE check_fund_calendar(
596 p_fund_calendar IN VARCHAR2,
597 p_start_period_name IN VARCHAR2,
598 p_end_period_name IN VARCHAR2,
599 p_start_date IN DATE,
600 p_end_date IN DATE,
601 p_fund_type IN VARCHAR2,
602 x_return_status OUT NOCOPY VARCHAR2) IS
603 l_start_start DATE;
604 l_start_end DATE;
605 l_end_start DATE;
606 l_end_end DATE;
607 l_local NUMBER;
608
609 CURSOR c_fund_calendar IS
610 SELECT 1
611 FROM DUAL
612 WHERE EXISTS( SELECT 1
613 FROM gl_periods_v
614 WHERE period_set_name = p_fund_calendar);
615
616 CURSOR c_start_period IS
617 SELECT start_date,
618 end_date
619 FROM gl_periods_v
620 WHERE period_set_name = p_fund_calendar AND period_name = p_start_period_name;
621
622 CURSOR c_end_period IS
623 SELECT start_date,
624 end_date
625 FROM gl_periods_v
626 WHERE period_set_name = p_fund_calendar AND period_name = p_end_period_name;
627
628 CURSOR c_start_period_quota IS
629 SELECT START_DATE, END_DATE FROM OZF_TIME_ENT_PERIOD
630 WHERE NAME = p_start_period_name
631 UNION ALL
632 SELECT START_DATE, END_DATE FROM OZF_TIME_ENT_QTR
633 WHERE NAME = p_start_period_name
634 UNION ALL
635 SELECT START_DATE, END_DATE FROM OZF_TIME_ENT_YEAR
636 WHERE NAME = p_start_period_name;
637
638 CURSOR c_end_period_quota IS
639 SELECT START_DATE, END_DATE FROM OZF_TIME_ENT_PERIOD
640 WHERE NAME = p_end_period_name
641 UNION ALL
642 SELECT START_DATE, END_DATE FROM OZF_TIME_ENT_QTR
643 WHERE NAME = p_end_period_name
644 UNION ALL
645 SELECT START_DATE, END_DATE FROM OZF_TIME_ENT_YEAR
646 WHERE NAME = p_end_period_name;
647
648
649 /*
650 CURSOR c_start_period_quota IS
651 SELECT NAME, START_DATE, END_DATE
652 FROM
653 (SELECT NAME, START_DATE, END_DATE FROM OZF_TIME_ENT_PERIOD
654 UNION ALL
655 SELECT NAME, START_DATE, END_DATE FROM OZF_TIME_ENT_QTR
656 UNION ALL
657 SELECT NAME, START_DATE, END_DATE FROM OZF_TIME_ENT_YEAR
658 )
659 WHERE NAME = p_start_period_name;
660
661 CURSOR c_end_period_quota IS
662 SELECT NAME, START_DATE, END_DATE
663 FROM
664 (SELECT NAME, START_DATE, END_DATE FROM OZF_TIME_ENT_PERIOD
665 UNION ALL
666 SELECT NAME, START_DATE, END_DATE FROM OZF_TIME_ENT_QTR
667 UNION ALL
668 SELECT NAME, START_DATE, END_DATE FROM OZF_TIME_ENT_YEAR
669 )
670 WHERE NAME = p_end_period_name;
671 */
672
673 BEGIN
674 x_return_status := fnd_api.g_ret_sts_success;
675
676 -- check if p_fund_calendar is null
677 IF p_fund_calendar IS NULL AND p_start_period_name IS NULL AND p_end_period_name IS NULL THEN
678 RETURN;
679 ELSIF p_fund_calendar IS NULL THEN
680 x_return_status := fnd_api.g_ret_sts_error;
681 ozf_utility_pvt.error_message('OZF_FUND_NO_CALENDAR');
682 RETURN;
683 END IF;
684
685 -- check if p_fund_calendar is valid
686 OPEN c_fund_calendar;
687 FETCH c_fund_calendar INTO l_local;
688 CLOSE c_fund_calendar;
689
690 IF l_local IS NULL THEN
691 x_return_status := fnd_api.g_ret_sts_error;
692 ozf_utility_pvt.error_message('OZF_FUND_BAD_CALENDAR');
693 RETURN;
694 END IF;
695
696 -- check p_start_period_name
697 IF p_start_period_name IS NOT NULL THEN
698
699 IF p_fund_type = 'QUOTA' THEN
700 OPEN c_start_period_quota;
701 FETCH c_start_period_quota INTO l_start_start, l_start_end;
702 CLOSE c_start_period_quota;
703 ELSE
704 OPEN c_start_period;
705 FETCH c_start_period INTO l_start_start, l_start_end;
706 CLOSE c_start_period;
707 END IF;
708
709 IF l_start_start IS NULL THEN
710 x_return_status := fnd_api.g_ret_sts_error;
711 ozf_utility_pvt.error_message('OZF_FUND_BAD_START_PERIOD');
712 RETURN;
713 ELSIF p_start_date < l_start_start
714 OR p_start_date > l_start_end THEN
715 x_return_status := fnd_api.g_ret_sts_error;
716 IF p_fund_type = 'QUOTA' THEN
717 ozf_utility_pvt.error_message('OZF_TP_QUOTA_OUT_START_PERIOD');
718 ELSE
719 ozf_utility_pvt.error_message('OZF_FUND_OUT_START_PERIOD');
720 END IF;
721 RETURN;
722 END IF;
723 END IF;
724
725 -- check p_end_period_name
726 IF p_end_period_name IS NOT NULL THEN
727 IF p_fund_type = 'QUOTA' THEN
728 OPEN c_end_period_quota;
729 FETCH c_end_period_quota INTO l_end_start, l_end_end;
730 CLOSE c_end_period_quota;
731 ELSE
732 OPEN c_end_period;
733 FETCH c_end_period INTO l_end_start, l_end_end;
734 CLOSE c_end_period;
735 END IF;
736
737 IF l_end_end IS NULL THEN
738 x_return_status := fnd_api.g_ret_sts_error;
739 ozf_utility_pvt.error_message('OZF_FUND_BAD_END_PERIOD');
740 RETURN;
741 ELSIF TRUNC(p_end_date) < TRUNC(l_end_start)
742 OR TRUNC(p_end_date) > TRUNC(l_end_end) THEN
743 x_return_status := fnd_api.g_ret_sts_error;
744 IF p_fund_type = 'QUOTA' THEN
745 ozf_utility_pvt.error_message('OZF_TP_QUOTA_OUT_END_PERIOD');
746 ELSE
747 ozf_utility_pvt.error_message('OZF_FUND_OUT_END_PERIOD');
748 END IF;
749 RETURN;
750 END IF;
751 END IF;
752
753 -- compare the start date and the end date
754 IF TRUNC(l_start_start) > TRUNC(l_end_end) THEN
755 x_return_status := fnd_api.g_ret_sts_error;
756 ozf_utility_pvt.error_message('OZF_FUND_BAD_PERIODS');
757 END IF;
758 END check_fund_calendar;
759
760
761 ---------------------------------------------------------------------
762 -- PROCEDURE
763 -- process_offers
764 --
765 -- PURPOSE
766 -- This API does the following transactions
767 -- 1) It creates a offer for a accrual fund and pushes all th eligibility
768 -- information of the fund to QP
769 -- 2) It also create =a record in the ozf_Act_budgets APi for the offer and the fund
770 --Parameters
771 -- (p_fund_rec IN OZF_FUNDS_PVT.fund_rec_type ,
772 -- p_csch_id IN NUMBER := NULL, If a schedule id is passed then the offer
773 -- would be associated to the schedule else to the fund
774 -- p_api_version IN NUMBER,
775 -- x_msg_count OUT NUMBER,
776 -- x_msg_data OUT VARCHAR2,
777 -- x_return_status OUT VARCHAR2 )
778 -- HISTORY
779 -- 01/15/2001 Mumu Pande Create.
780 -- 05/30/2001 Mumu Pande Updated for new offer requirements
781 -- 10/22/2001 Mumu Pande Updated for accrual offer updation
782 -- 01/12/2001 Mumu Pande Updated for sales accrual offers
783 -- 10/11/2002 Narasimha Ramu
784 -- Srinivasa Rudaravarapu Updated process offer for 11.5.9
785 -- NOTES
786 ---------------------------------------------------------------------
787 PROCEDURE process_offers(
788 p_fund_rec IN ozf_funds_pvt.fund_rec_type,
789 p_csch_id IN NUMBER := NULL -- this is not used anymore
790 ,
791 p_api_version IN NUMBER,
792 p_mode IN VARCHAR2 := jtf_plsql_api.g_create,
793 p_old_fund_status IN VARCHAR2 := NULL,
794 x_msg_count OUT NOCOPY NUMBER,
795 x_msg_data OUT NOCOPY VARCHAR2,
796 x_return_status OUT NOCOPY VARCHAR2) IS
797 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
798 l_msg_count NUMBER;
799 l_msg_data VARCHAR2(2000);
800 l_api_name CONSTANT VARCHAR2(30) := 'process_offers';
801 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||
802 '.' ||
803 l_api_name;
804 l_offer_hdr_rec ozf_offer_pvt.modifier_list_rec_type;
805
806 -- l_upd_offer_hdr_rec ozf_offer_pvt.modifier_list_rec_type;
807 l_offer_line_tbl ozf_offer_pvt.modifier_line_tbl_type;
808
809 -- l_offer_qlfr_tbl ozf_offer_pvt.qualifiers_tbl_type;
810 -- l_offer_pricing_tbl ozf_offer_pvt.pricing_attr_tbl_type;
811 -- l_segments_rec ams_act_market_segments_pvt.mks_rec_type;
812 -- l_act_segment_id NUMBER;
813 l_error_location NUMBER;
814 --l_error_entity VARCHAR2(200);
815 --l_error_source VARCHAR2(10);
816 l_api_version CONSTANT NUMBER := 1.0;
817 l_act_budgets_rec_type ozf_actbudgets_pvt.act_budgets_rec_type;
818 l_act_budget_id NUMBER;
819 i NUMBER := 1;
820 j NUMBER := 1;
821 l_status_type VARCHAR(30) := 'OZF_OFFER_STATUS';
822 l_qp_list_header_id NUMBER;
823 l_ozf_offer_id NUMBER;
824 l_rec_idx NUMBER := 1;
825 l_offer_advd_opt_rec ozf_offer_pvt.advanced_option_rec_type;
826 l_offer_obj_ver_num NUMBER;
827 l_offer_pending_flag VARCHAR2(1);
828 l_old_fund_status VARCHAR(30) := p_old_fund_status;
829
830 CURSOR c_offer_id(
831 p_list_header_id IN NUMBER) IS
832 SELECT offer_id,
833 object_version_number
834 FROM ozf_offers
835 WHERE qp_list_header_id = p_list_header_id;
836
837 CURSOR c_old_fund(
838 cv_fund_id IN NUMBER)
839 IS
840 SELECT status_code
841 FROM ozf_funds_all_b
842 WHERE fund_id = cv_fund_id;
843
844 BEGIN
845 SAVEPOINT process_offers;
846 IF G_DEBUG THEN
847 ozf_utility_pvt.debug_message(l_full_name ||
848 ': create offers');
849 END IF;
850 x_return_status := fnd_api.g_ret_sts_success;
851
852 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
853 RAISE fnd_api.g_exc_unexpected_error;
854 END IF;
855
856 IF p_old_fund_status IS NULL THEN
857 OPEN c_old_fund(p_fund_rec.fund_id);
858 FETCH c_old_fund INTO l_old_fund_status;
859 CLOSE c_old_fund;
860 END IF;
861
862 IF p_mode = 'CREATE' THEN
863 IF G_DEBUG THEN
864 ozf_utility_pvt.debug_message('Create');
865 END IF;
866 -- first process_offerss in QP
867 l_offer_hdr_rec.qp_list_header_id := fnd_api.g_miss_num;
868 l_offer_hdr_rec.activity_media_id := p_fund_rec.task_id;
869 l_offer_hdr_rec.offer_id := fnd_api.g_miss_num;
870
871 --kdass 18-MAY-2004 fix for bug 3628608
872 l_offer_hdr_rec.confidential_flag := 'Y';
873 -- commented for 11.5.9
874 /*
875 IF p_fund_rec.liability_flag = 'Y' THEN
876 l_offer_hdr_rec.offer_operation := 'CREATE';
877 ELSE
878 l_offer_hdr_rec.offer_operation := fnd_api.g_miss_char;
879 END IF;
880 */
881 l_offer_hdr_rec.offer_operation := 'CREATE';
882 l_offer_hdr_rec.modifier_operation := 'CREATE';
883 ELSIF p_mode In ( 'UPDATE' ,'ACTIVE') THEN
884
885 /* yzhao: 07/23/2001 create actbudget after updating offer modifiers
886 so product eligibility check succeeds */ -- 10/11/2002
887 /* fix bug 3464511 - duplicate REQUEST records in ozf_act_budgets whenever updating active accrual budget
888 IF p_fund_rec.status_code = 'ACTIVE' THEN
889 */
890 IF p_fund_rec.status_code in ('ACTIVE','ON_HOLD') AND p_mode ='ACTIVE' THEN
891 IF G_DEBUG THEN
892 ozf_utility_pvt.debug_message(l_full_name ||
893 ': begin create act budgets ');
894 END IF;
895 -- Create_act_budgets for the created offers
896 l_act_budgets_rec_type.act_budget_used_by_id := p_fund_rec.plan_id;
897 l_act_budgets_rec_type.arc_act_budget_used_by := 'OFFR';
898 l_act_budgets_rec_type.budget_source_type := 'FUND';
899 l_act_budgets_rec_type.budget_source_id := p_fund_rec.fund_id;
900 l_act_budgets_rec_type.transaction_type := 'CREDIT';
901 /* yzhao: 12/17/2001 create a REQUEST rathen than TRANSFER so it shows in offer screen
902 l_act_budgets_rec_type.transfer_type := 'TRANSFER';
903 */
904 l_act_budgets_rec_type.transfer_type := 'REQUEST';
905 l_act_budgets_rec_type.request_amount := NVL(p_fund_rec.accrual_cap, 0);
906 l_act_budgets_rec_type.request_currency := p_fund_rec.currency_code_tc;
907 l_act_budgets_rec_type.request_date := SYSDATE;
908 l_act_budgets_rec_type.user_status_id := 5001;
909 l_act_budgets_rec_type.status_code := 'APPROVED';
910 l_act_budgets_rec_type.user_status_id := ozf_utility_pvt.get_default_user_status(
911 'OZF_BUDGETSOURCE_STATUS',
912 'APPROVED');
913 l_act_budgets_rec_type.approved_amount := NVL(p_fund_rec.accrual_cap, 0);
914 l_act_budgets_rec_type.approved_original_amount := NVL(p_fund_rec.accrual_cap, 0);
915 l_act_budgets_rec_type.approved_in_currency := p_fund_rec.currency_code_tc;
916 l_act_budgets_rec_type.approval_date := SYSDATE;
917 l_act_budgets_rec_type.approver_id := p_fund_rec.owner;
918 l_act_budgets_rec_type.requester_id := p_fund_rec.owner;
919 ozf_actbudgets_pvt.create_act_budgets(
920 p_api_version=> l_api_version,
921 x_return_status=> l_return_status,
922 x_msg_count=> x_msg_count,
923 x_msg_data=> x_msg_data,
924 p_act_budgets_rec=> l_act_budgets_rec_type,
925 p_act_util_rec=> ozf_actbudgets_pvt.g_miss_act_util_rec,
926 x_act_budget_id=> l_act_budget_id,
927 p_approval_flag=> fnd_api.g_true);
928
929 -- dbms_output.put_line('fundrules: create_act_budget returns ' || l_return_status);
930 -- dbms_output.put_line(' actbudget_used_by_id=' || l_act_budgets_rec_type.act_budget_used_by_id);
931 IF l_return_status = fnd_api.g_ret_sts_error THEN
932 RAISE fnd_api.g_exc_error;
933 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
934 RAISE fnd_api.g_exc_unexpected_error;
935 END IF;
936
937 IF G_DEBUG THEN
938 ozf_utility_pvt.debug_message(l_full_name ||
939 ': end create budgets ');
940 END IF;
941 END IF; -- end if for active
942 /* -- 10/11/2002
943 ELSIF p_mode = 'UPDATE' THEN
944 -- 10/16/2001 mpande for updation of accrual offer
945 OPEN c_actbudget_id( l_upd_offer_hdr_rec.qp_list_header_id, p_fund_rec.fund_id) ;
946 FETCH c_actbudget_id INTO l_act_budget_id ;
947 CLOSE c_actbudget_id;
948 -- 10/11/2002 increment the object verison number
949 UPDATE ozf_act_budgets
950 SET approved_amount = NVL(p_fund_rec.accrual_cap,0),
951 approved_original_amount = NVL(p_fund_rec.accrual_cap,0)
952 WHERE activity_budget_Id = l_Act_budget_id;
953 END IF;
954 */
955
956 -- dbms_OUTPUT.put_line('fund_status ' ||
957 -- p_fund_rec.status_code);
958 IF G_DEBUG THEN
959 ozf_utility_pvt.debug_message('debug6');
960 END IF;
961 l_qp_list_header_id := p_fund_rec.plan_id;
962 IF G_DEBUG THEN
963 ozf_utility_pvt.debug_message('l_qp_list_header_id =>' ||
964 l_qp_list_header_id);
965 END IF;
966 OPEN c_offer_id(l_qp_list_header_id);
967 FETCH c_offer_id INTO l_ozf_offer_id, l_offer_obj_ver_num;
968 CLOSE c_offer_id;
969 l_offer_hdr_rec.qp_list_header_id := p_fund_rec.plan_id;
970 l_offer_hdr_rec.activity_media_id := p_fund_rec.task_id;
971 l_offer_hdr_rec.offer_id := l_ozf_offer_id;
972 l_offer_hdr_rec.object_version_number := l_offer_obj_ver_num;
973 /*
974 IF p_fund_rec.liability_flag = 'Y' THEN
975 l_offer_hdr_rec.offer_operation := 'UPDATE';
976 ELSE
977 l_offer_hdr_rec.offer_operation := fnd_api.g_miss_char;
978 END IF;
979 */
980 l_offer_hdr_rec.offer_operation := 'UPDATE';
981 l_offer_hdr_rec.modifier_operation := 'UPDATE';
982 END IF; -- end if for p_mode
983
984 IF p_fund_rec.status_code = 'CLOSED' THEN
985 l_offer_hdr_rec.user_status_id := ozf_utility_pvt.get_default_user_status(
986 l_status_type,
987 'COMPLETED');
988 l_offer_hdr_rec.status_code := 'COMPLETED';
989 ELSIF p_fund_rec.status_code = 'CANCELLED' THEN
990 l_offer_hdr_rec.user_status_id := ozf_utility_pvt.get_default_user_status(
991 l_status_type,
992 'TERMINATED');
993 l_offer_hdr_rec.status_code := 'TERMINATED';
994 ELSIF p_fund_rec.status_code = 'DRAFT' THEN
995 l_offer_hdr_rec.status_code := 'DRAFT';
996 l_offer_hdr_rec.user_status_id := ozf_utility_pvt.get_default_user_status(
997 l_status_type,
998 'DRAFT');
999 ELSIF p_fund_rec.status_code = 'PENDING' THEN
1000 IF l_old_fund_status = 'REJECTED' THEN
1001 l_offer_hdr_rec.status_code := 'REJECTED';
1002 l_offer_hdr_rec.user_status_id := ozf_utility_pvt.get_default_user_status(
1003 l_status_type,
1004 'REJECTED');
1005 ELSE
1006 l_offer_hdr_rec.status_code := 'DRAFT';
1007 l_offer_hdr_rec.user_status_id := ozf_utility_pvt.get_default_user_status(
1008 l_status_type,
1009 'DRAFT');
1010 END IF;
1011 l_offer_pending_flag := 'T';
1012 IF G_DEBUG THEN
1013 ozf_utility_pvt.debug_message('debug5');
1014 END IF;
1015 ELSIF p_fund_rec.status_code = 'ON_HOLD' THEN
1016 l_offer_hdr_rec.status_code := 'ONHOLD';
1017 l_offer_hdr_rec.user_status_id := ams_utility_pvt.get_default_user_status(
1018 l_status_type,
1019 'ONHOLD');
1020 ELSIF p_fund_rec.status_code = 'REJECTED' THEN
1021 l_offer_hdr_rec.status_code := 'REJECTED';
1022 l_offer_hdr_rec.user_status_id := ams_utility_pvt.get_default_user_status(
1023 l_status_type,
1024 'REJECTED');
1025 ELSIF p_fund_rec.status_code = 'ACTIVE' THEN
1026 l_offer_hdr_rec.user_status_id := ozf_utility_pvt.get_default_user_status(
1027 l_status_type,
1028 'ACTIVE');
1029 l_offer_hdr_rec.status_code := 'ACTIVE';
1030 END IF;
1031
1032 -- -- 10/11/2002 mpande
1033 -- all these parameters are same in create and update mode
1034 IF p_fund_rec.accrual_phase = 'VOLUME' THEN
1035 l_offer_hdr_rec.offer_type := 'VOLUME_OFFER';
1036 -- customer and customer_type
1037 l_offer_hdr_rec.retroactive := p_fund_rec.retroactive_flag;
1038 l_offer_hdr_rec.custom_setup_id := 108;
1039 l_offer_hdr_rec.volume_offer_type := 'ACCRUAL';
1040 -- here pass the seeded custom setup id for volume offers 10/11/2002
1041 -- 10/11/2002 pass the volume offer customer informaiton
1042 ELSE
1043 l_offer_hdr_rec.offer_type := 'ACCRUAL';
1044 l_offer_hdr_rec.custom_setup_id := 101;
1045 END IF;
1046 l_offer_hdr_rec.NAME := p_fund_rec.short_name ||
1047 '-' ||
1048 p_fund_rec.fund_id;
1049 l_offer_hdr_rec.description := p_fund_rec.short_name;
1050 l_offer_hdr_rec.offer_amount := NVL(p_fund_rec.accrual_cap, 0);
1051 l_offer_hdr_rec.budget_amount_tc := NVL(p_fund_rec.accrual_cap, 0);
1052 --l_offer_hdr_rec.BUDGET_AMOUNT_FC NUMBER := Fnd_Api.g_miss_num
1053 IF l_qp_list_header_id IS NULL THEN
1054 l_offer_hdr_rec.offer_code := p_fund_rec.fund_number;
1055 END IF;
1056 l_offer_hdr_rec.ql_qualifier_type := p_fund_rec.apply_accrual_on;
1057 l_offer_hdr_rec.ql_qualifier_id := p_fund_rec.qualifier_id;
1058 l_offer_hdr_rec.owner_id := p_fund_rec.owner;
1059 l_offer_hdr_rec.perf_date_from := p_fund_rec.start_date_active;
1060 l_offer_hdr_rec.perf_date_to := p_fund_rec.end_date_active;
1061 l_offer_hdr_rec.status_date := p_fund_rec.start_date_active;
1062 l_offer_hdr_rec.source_from_parent := 'N';
1063 l_offer_hdr_rec.transaction_currency_code := p_fund_rec.currency_code_tc;
1064 l_offer_hdr_rec.currency_code := p_fund_rec.currency_code_tc;
1065 l_offer_hdr_rec.start_date_active := p_fund_rec.start_date_active;
1066 l_offer_hdr_rec.end_date_active := p_fund_rec.end_date_active;
1067 l_offer_hdr_rec.reusable := 'N';
1068 l_offer_hdr_rec.budget_offer_yn := 'Y';
1069 l_offer_hdr_rec.modifier_level_code := p_fund_rec.accrual_discount_level;
1070 -- dbms_OUTPUT.put_line('modifier_leve ' ||
1071 -- p_fund_rec.accrual_discount_level);
1072 -- bug fix 3088198.
1073 -- IF p_fund_rec.status_code = 'ACTIVE' THEN
1074 /* yzhao: 03/03/2004 fix bug 3464511 - duplicate REQUEST records in ozf_funds_utilized_all whenever updating active accrual budget
1075 IF l_old_fund_status in('PENDING') AND p_fund_rec.status_code in('ACTIVE','ON_HOLD','REJECTED') THEN
1076 */
1077 IF (p_mode = 'ACTIVE' OR l_old_fund_status in('PENDING'))
1078 AND p_fund_rec.status_code in('ACTIVE','ON_HOLD','REJECTED', 'DRAFT') THEN
1079 /* yzhao: 08/09/2005 for fully accrual budget PENDING => DRAFT, call update_offer_status not process_modifier
1080 otherwise fully accrual budget can not be reverted to DRAFT from PENDING
1081 since process_modifer checks ams_status_order_rules, and PENDING => DRAFT not allowed for offer
1082 AND p_fund_rec.status_code in('ACTIVE','ON_HOLD','REJECTED') THEN
1083 */
1084 ozf_offer_pvt.update_offer_status
1085 (
1086 p_commit => fnd_api.g_false,
1087 x_return_status=> l_return_status,
1088 x_msg_count=> x_msg_count,
1089 x_msg_data=> x_msg_data,
1090 p_modifier_list_rec => l_offer_hdr_rec
1091 );
1092
1093 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1094 IF G_DEBUG THEN
1095 ozf_utility_pvt.debug_message('OZF_OFFR_UPDATE_SATAUS FAIL');
1096 END IF;
1097
1098 IF l_return_status = fnd_api.g_ret_sts_error THEN
1099 RAISE fnd_api.g_exc_error;
1100 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1101 RAISE fnd_api.g_exc_unexpected_error;
1102 END IF;
1103 END IF;
1104
1105 ELSE
1106 IF G_DEBUG THEN
1107 ozf_utility_pvt.debug_message('process modifiers');
1108 END IF;
1109 ozf_offer_pvt.process_modifiers(
1110 p_init_msg_list=> fnd_api.g_false,
1111 p_api_version=> 1.0,
1112 p_commit=> fnd_api.g_false,
1113 x_return_status=> l_return_status,
1114 x_msg_count=> x_msg_count,
1115 x_msg_data=> x_msg_data,
1116 p_modifier_list_rec=> l_offer_hdr_rec,
1117 p_modifier_line_tbl=> l_offer_line_tbl,
1118 p_offer_type=> l_offer_hdr_rec.offer_type,
1119 x_qp_list_header_id=> l_qp_list_header_id,
1120 x_error_location=> l_error_location);
1121 IF G_DEBUG THEN
1122 ozf_utility_pvt.debug_message(
1123 'l_return_status' ||
1124 l_return_status ||
1125 '-' ||
1126 l_error_location ||
1127 x_msg_data);
1128 END IF;
1129 END IF;
1130
1131 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1132 IF G_DEBUG THEN
1133 ozf_utility_pvt.debug_message('OZF_OFFR_QP_FAILURE' ||
1134 l_error_location ||
1135 x_msg_data);
1136 END IF;
1137
1138 -- ozf_utility_pvt.error_message('OZF_OFFR_QP_FAILURE'||l_error_location||x_msg_data);
1139 IF l_return_status = fnd_api.g_ret_sts_error THEN
1140 RAISE fnd_api.g_exc_error;
1141 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1142 RAISE fnd_api.g_exc_unexpected_error;
1143 END IF;
1144 END IF;
1145
1146 -- Update ozf_offers with status code 'PENDING' by incrementing object_version_number.
1147 IF l_offer_pending_flag = 'T' THEN
1148 l_offer_hdr_rec.user_status_id := ozf_utility_pvt.get_default_user_status(l_status_type, 'PENDING');
1149
1150 -- 10/11/2002 mpande pass the default user status and check what is the default status code
1151 UPDATE ozf_offers
1152 SET status_code = 'PENDING',
1153 user_status_id = l_offer_hdr_rec.user_status_id,
1154 object_version_number = object_version_number + 1
1155 WHERE offer_id = l_ozf_offer_id;
1156
1157 IF G_DEBUG THEN
1158 ozf_utility_pvt.debug_message('In update 1');
1159 END IF;
1160 END IF;
1161
1162 IF p_mode = 'CREATE' THEN
1163 UPDATE ozf_funds_all_b
1164 SET plan_id = l_qp_list_header_id,
1165 plan_type = 'OFFR'
1166 WHERE fund_id = p_fund_rec.fund_id;
1167 ELSIF p_mode = 'UPDATE' THEN
1168 l_qp_list_header_id := p_fund_rec.plan_id;
1169 IF G_DEBUG THEN
1170 ozf_utility_pvt.debug_message('qp_list_header_id =>' ||
1171 l_qp_list_header_id);
1172 END IF;
1173 END IF; -- end of p_mode = CREATE/UPDATE
1174
1175 IF G_DEBUG THEN
1176 ozf_utility_pvt.debug_message(l_full_name ||
1177 ': end update offer advanced option ');
1178 END IF;
1179 -- dbms_output.put_line('fundrules: process_modifiers to UPDATE returns ' || l_return_status || ' x_qp_list_header_id=' || l_qp_list_header_id);
1180 IF G_DEBUG THEN
1181 ozf_utility_pvt.debug_message(l_full_name ||
1182 ': begin exclusion ');
1183 END IF;
1184
1185 --kdass 11-MAR-2004 fixed bug 3465281 - advanced options is handled by offers, budgets need to handle this
1186 /*
1187 -- for Fund_status = 'DRAFT' or p_mode = 'CREATE'.
1188 IF ( p_mode = 'CREATE'
1189 OR p_fund_rec.status_code = 'DRAFT') THEN
1190 IF G_DEBUG THEN
1191 ozf_utility_pvt.debug_message(l_full_name ||
1192 ': begin update offer advanced option ');
1193 END IF;
1194 --- 08/20/2001 mpande addded for discount level and bucket to process advanced options
1195 l_offer_advd_opt_rec.list_header_id := l_qp_list_header_id;
1196 l_offer_advd_opt_rec.offer_type := l_offer_hdr_rec.offer_type;
1197 l_offer_advd_opt_rec.modifier_level_code := p_fund_rec.accrual_discount_level;
1198
1199 -- order level does not need a bucket
1200 IF p_fund_rec.accrual_discount_level <> 'ORDER' THEN
1201 l_offer_advd_opt_rec.pricing_group_sequence := p_fund_rec.accrual_method;
1202 END IF;
1203
1204 --l_offer_advd_opt_rec.PRINT_ON_INVOICE_FLAG VARCHAR2(1) := Fnd_Api.g_miss_char
1205 ozf_offer_pvt.process_adv_options(
1206 p_init_msg_list=> fnd_api.g_false,
1207 p_api_version=> 1.0,
1208 p_commit=> fnd_api.g_false,
1209 x_return_status=> l_return_status,
1210 x_msg_count=> l_msg_count,
1211 x_msg_data=> l_msg_data,
1212 p_advanced_options_rec=> l_offer_advd_opt_rec);
1213 IF G_DEBUG THEN
1214 ozf_utility_pvt.debug_message('ret status for process adv options =>' ||
1215 l_return_status);
1216 END IF;
1217
1218 IF l_return_status = fnd_api.g_ret_sts_error THEN
1219 RAISE fnd_api.g_exc_error;
1220 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1221 RAISE fnd_api.g_exc_unexpected_error;
1222 END IF;
1223 END IF;
1224 */
1225 EXCEPTION
1226 WHEN fnd_api.g_exc_error THEN
1227 ROLLBACK TO process_offers;
1228 x_return_status := fnd_api.g_ret_sts_error;
1229 fnd_msg_pub.count_and_get(
1230 p_encoded=> fnd_api.g_false,
1231 p_count=> x_msg_count,
1232 p_data=> x_msg_data);
1233 WHEN fnd_api.g_exc_unexpected_error THEN
1234 ROLLBACK TO process_offers;
1235 x_return_status := fnd_api.g_ret_sts_unexp_error;
1236 fnd_msg_pub.count_and_get(
1237 p_encoded=> fnd_api.g_false,
1238 p_count=> x_msg_count,
1239 p_data=> x_msg_data);
1240 WHEN OTHERS THEN
1241 ROLLBACK TO process_offers;
1242 x_return_status := fnd_api.g_ret_sts_unexp_error;
1243
1244 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1245 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1246 END IF;
1247
1248 fnd_msg_pub.count_and_get(
1249 p_encoded=> fnd_api.g_false,
1250 p_count=> x_msg_count,
1251 p_data=> x_msg_data);
1252 END process_offers;
1253
1254 ---------------------------------------------------------------------
1255 -- PROCEDURE
1256 -- process_approval
1257 --
1258 -- PURPOSE
1259 -- This API is called when fund is approved from a workflow.
1260 -- This API does the following transactions for a Active fund.
1261 -- 1) Record for holdback amount
1262 -- 2) Handle transactions for a Accrual type fund
1263 -- HISTORY
1264 -- 01/15/2001 Mumu Pande Create.
1265 -- NOTES
1266 ---------------------------------------------------------------------
1267 PROCEDURE process_approval(
1268 p_fund_rec IN ozf_funds_pvt.fund_rec_type,
1269 p_mode IN VARCHAR2,
1270 p_old_fund_status IN VARCHAR2 := NULL,
1271 x_return_status OUT NOCOPY VARCHAR2,
1272 x_msg_count OUT NOCOPY NUMBER,
1273 x_msg_data OUT NOCOPY VARCHAR2,
1274 p_api_version IN NUMBER) IS
1275 l_api_version CONSTANT NUMBER := 1.0;
1276 l_api_name CONSTANT VARCHAR2(30) := 'process_approval';
1277 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||
1278 '.' ||
1279 l_api_name;
1280 l_return_status VARCHAR2(1);
1281 l_msg_count NUMBER;
1282 l_msg_data VARCHAR2(2000);
1283 l_act_budget_rec ozf_actbudgets_pvt.act_budgets_rec_type;
1284 l_act_budget_id NUMBER;
1285 BEGIN
1286 -- If the fund_status is changing from 'DRAFT to 'ACTIVE', we need to create a record in the
1287 -- FUND_REQUESTS table for the holdback amount.
1288 SAVEPOINT process_approval;
1289 x_return_status := fnd_api.g_ret_sts_success;
1290
1291 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1292 RAISE fnd_api.g_exc_unexpected_error;
1293 END IF;
1294
1295 IF p_mode = 'ACTIVE' THEN
1296 IF (NVL(p_fund_rec.holdback_amt, 0) <> 0) THEN
1297 l_act_budget_rec.status_code := 'APPROVED';
1298 l_act_budget_rec.arc_act_budget_used_by := 'FUND'; -- hardcoded to fund
1299 l_act_budget_rec.act_budget_used_by_id := p_fund_rec.fund_id;
1300 l_act_budget_rec.requester_id := p_fund_rec.owner;
1301 l_act_budget_rec.approver_id := p_fund_rec.owner;
1302 l_act_budget_rec.request_amount := p_fund_rec.holdback_amt; --- in transferring to fund currency
1303 l_act_budget_rec.approved_amount := p_fund_rec.holdback_amt; --- in transferring to fund currency
1304 l_act_budget_rec.approved_original_amount := p_fund_rec.holdback_amt; --- in transferring to fund currency
1305 l_act_budget_rec.budget_source_type := 'FUND';
1306 l_act_budget_rec.budget_source_id := p_fund_rec.fund_id;
1307 l_act_budget_rec.transfer_type := 'RESERVE';
1308 l_act_budget_rec.transaction_type := 'CREDIT';
1309 l_act_budget_rec.approved_in_currency := p_fund_rec.currency_code_tc;
1310 l_act_budget_rec.adjusted_flag := 'N';
1311 --l_act_budget_rec.date_required_by := p_needbydate;
1312 -- Create_transfer record
1313 ozf_actbudgets_pvt.create_act_budgets(
1314 p_api_version=> l_api_version,
1315 x_return_status=> l_return_status,
1316 x_msg_count=> x_msg_count,
1317 x_msg_data=> x_msg_data,
1318 p_act_budgets_rec=> l_act_budget_rec,
1319 x_act_budget_id=> l_act_budget_id);
1320
1321 IF l_return_status = fnd_api.g_ret_sts_error THEN
1322 RAISE fnd_api.g_exc_error;
1323 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1324 RAISE fnd_api.g_exc_unexpected_error;
1325 END IF;
1326 END IF; -- end for holdback mat
1327 END IF;
1328
1329 IF p_fund_rec.fund_type = 'FULLY_ACCRUED' THEN
1330 -- 10/14/2002 mpande for 11.5.9
1331 ozf_fundrules_pvt.process_accrual(
1332 p_fund_rec=> p_fund_rec,
1333 p_api_version=> l_api_version,
1334 p_mode=> p_mode,
1335 p_old_fund_status => p_old_fund_status,
1336 x_return_status=> l_return_status,
1337 x_msg_count=> x_msg_count,
1338 x_msg_data=> x_msg_data);
1339
1340 IF l_return_status = fnd_api.g_ret_sts_error THEN
1341 RAISE fnd_api.g_exc_error;
1342 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1343 RAISE fnd_api.g_exc_unexpected_error;
1344 END IF;
1345 END IF;
1346
1347 IF G_DEBUG THEN
1348 ozf_utility_pvt.debug_message(l_full_name ||
1349 ': end');
1350 END IF;
1351 EXCEPTION
1352 WHEN fnd_api.g_exc_error THEN
1353 ROLLBACK TO process_approval;
1354 x_return_status := fnd_api.g_ret_sts_error;
1355 fnd_msg_pub.count_and_get(
1356 p_encoded=> fnd_api.g_false,
1357 p_count=> x_msg_count,
1358 p_data=> x_msg_data);
1359 WHEN fnd_api.g_exc_unexpected_error THEN
1360 ROLLBACK TO process_approval;
1361 x_return_status := fnd_api.g_ret_sts_unexp_error;
1362 fnd_msg_pub.count_and_get(
1363 p_encoded=> fnd_api.g_false,
1364 p_count=> x_msg_count,
1365 p_data=> x_msg_data);
1366 WHEN OTHERS THEN
1367 ROLLBACK TO process_approval;
1368 x_return_status := fnd_api.g_ret_sts_unexp_error;
1369
1370 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1371 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1372 END IF;
1373
1374 fnd_msg_pub.count_and_get(
1375 p_encoded=> fnd_api.g_false,
1376 p_count=> x_msg_count,
1377 p_data=> x_msg_data);
1378 END process_approval;
1379
1380
1381 -----------------------------------------------------------------------
1382 -- PROCEDURE
1383 -- check_fund_before_close
1384 --
1385 -- PURPOSE
1386 -- Check fund amount before close/cancel an ACTIVE/ON_HOLD budget
1387 -- fix bug 2532491 TST 1158.7 MASTER FUNC : CAN CANCEL AND ARCHIEVE BUDGETS WITH AVAILABLE FUNDS
1388 -- HISTORY
1389 -- 01/20/2003 yzhao Create
1390 -- parameters
1391 -- p_fund_id IN NUMBER
1392 -- x_return_status OUT VARCAHR2
1393 -----------------------------------------------------------------------
1394 PROCEDURE check_fund_before_close(
1395 p_fund_id IN NUMBER
1396 ,x_return_status OUT NOCOPY VARCHAR2
1397 ,x_msg_count OUT NOCOPY NUMBER
1398 ,x_msg_data OUT NOCOPY VARCHAR2)
1399 IS
1400 --12/08/2005 rimehrot - sql repository fix SQL ID 14893182 - query the base table directly
1401 --asylvia 11-May-2006 bug 5199719 - SQL ID 17779489
1402 CURSOR c_get_fund_amount IS
1403 SELECT (NVL(original_budget, 0) + NVL(transfered_in_amt,0) - NVL(transfered_out_amt, 0))
1404 , NVL(recal_committed, 0),
1405 NVL(utilized_amt, 0), NVL(earned_amt, 0), NVL(paid_amt, 0), fund_type -- yzhao: 11.5.10 added utilized_amt
1406 FROM ozf_funds_all_b
1407 WHERE fund_id = p_fund_id;
1408
1409 l_recal_flag VARCHAR2(1);
1410 l_total_budget NUMBER;
1411 l_committed_amt NUMBER;
1412 l_utilized_amt NUMBER;
1413 l_earned_amt NUMBER;
1414 l_paid_amt NUMBER;
1415 l_fund_type VARCHAR2(20);
1416 BEGIN
1417 x_return_status := fnd_api.g_ret_sts_success;
1418
1419 OPEN c_get_fund_amount;
1420 FETCH c_get_fund_amount INTO l_total_budget, l_committed_amt, l_utilized_amt, l_earned_amt, l_paid_amt, l_fund_type;
1421 CLOSE c_get_fund_amount;
1422
1423 IF l_total_budget > l_committed_amt THEN
1424 -- total > committed >= utilized >= earned >= paid
1425 -- don't close budget because there's available money
1426 IF l_fund_type = 'QUOTA' THEN
1427 ozf_utility_pvt.error_message('OZF_TP_QUOTA_BAN_CLOSE');
1428 ELSE
1429 ozf_utility_pvt.error_message('OZF_FUND_BAN_CLOSE');
1430 END IF;
1431 x_return_status := fnd_api.g_ret_sts_error;
1432 ELSIF l_total_budget < l_committed_amt THEN
1433 -- total < re-calculated committed This only happens when profile 'OZF_BUDGET_ADJ_ALLOW_RECAL' is 'Y'
1434 -- don't close budget because re-calculated committed has committed funds for over the pool of money originally available,
1435 -- need a budget transfer into the budget
1436 IF l_fund_type = 'QUOTA' THEN
1437 ozf_utility_pvt.error_message('OZF_TP_QUOTA_BAN_CLOSE');
1438 ELSE
1439 ozf_utility_pvt.error_message('OZF_FUND_BAN_CLOSE_COMM_MORE');
1440 END IF;
1441 x_return_status := fnd_api.g_ret_sts_error;
1442 ELSE
1443 -- total = committed
1444 IF l_committed_amt > l_utilized_amt THEN
1445 -- total = calculated committed > utilized
1446 -- don't close budget because there is committed fund but not yet utilized
1447 IF l_fund_type = 'QUOTA' THEN
1448 ozf_utility_pvt.error_message('OZF_TP_QUOTA_BAN_CLOSE');
1449 ELSE
1450 ozf_utility_pvt.error_message('OZF_FUND_BAN_CLOSE_UTIL_LESS');
1451 END IF;
1452 x_return_status := fnd_api.g_ret_sts_error;
1453 ELSIF l_committed_amt = l_utilized_amt THEN
1454 -- total = committed = utilized
1455 IF l_utilized_amt > l_earned_amt THEN
1456 -- total = calculated committed = utilized > earned
1457 -- don't close budget because there is utilized fund but not yet posted to GL
1458 IF l_fund_type = 'QUOTA' THEN
1459 ozf_utility_pvt.error_message('OZF_TP_QUOTA_BAN_CLOSE');
1460 ELSE
1461 ozf_utility_pvt.error_message('OZF_FUND_BAN_CLOSE_EARN_LESS');
1462 END IF;
1463 x_return_status := fnd_api.g_ret_sts_error;
1464 ELSIF l_utilized_amt = l_earned_amt THEN
1465 -- total = calculated committed = utilized = earned
1466 IF l_earned_amt > l_paid_amt THEN
1467 -- total = re-calculated committed = utilized = earned > paid
1468 -- don't close budget because there's un-paid fund (trade management is implemented,
1469 -- accrual earnings not paid out by claim or deduction yet).
1470 IF l_fund_type = 'QUOTA' THEN
1471 ozf_utility_pvt.error_message('OZF_TP_QUOTA_BAN_CLOSE');
1472 ELSE
1473 ozf_utility_pvt.error_message('OZF_FUND_BAN_CLOSE_PAID_LESS');
1474 END IF;
1475 x_return_status := fnd_api.g_ret_sts_error;
1476 ELSIF l_earned_amt = l_paid_amt THEN
1477 -- total = re-calculated committed = utilized = earned = paid
1478 x_return_status := fnd_api.g_ret_sts_success;
1479 END IF;
1480 END IF;
1481 END IF;
1482 END IF;
1483 EXCEPTION
1484 WHEN OTHERS THEN
1485 x_return_status := fnd_api.g_ret_sts_unexp_error;
1486 fnd_msg_pub.count_and_get(
1487 p_encoded => fnd_api.g_false
1488 ,p_count => x_msg_count
1489 ,p_data => x_msg_data);
1490 END check_fund_before_close;
1491
1492 -----------------------------------------------------------------------
1493 -- PROCEDURE
1494 -- update_fund_status
1495 --
1496 -- PURPOSE
1497 -- Update fund status .This procedure is called by the update fund API
1498 -- It takes care of all the status changes that take place in funds in the
1499 -- update mode
1500 -- HISTORY
1501 -- 01/15/2001 Mumu Pande Create.
1502 -- parameters p_fund_rec IN fund_rec_type,
1503 -- x_new_status_code OUT VARCHAR2 the new fund status code
1504 -- x_new_status_id OUT NUMBER
1505 -- x_return_status OUT VARCAHR2
1506 -----------------------------------------------------------------------
1507 PROCEDURE update_fund_status(
1508 p_fund_rec IN ozf_funds_pvt.fund_rec_type,
1509 x_new_status_code OUT NOCOPY VARCHAR2,
1510 x_new_status_id OUT NOCOPY NUMBER,
1511 x_submit_budget_approval OUT NOCOPY VARCHAR2,
1512 x_submit_child_approval OUT NOCOPY VARCHAR2,
1513 x_return_status OUT NOCOPY VARCHAR2,
1514 x_msg_count OUT NOCOPY NUMBER,
1515 x_msg_data OUT NOCOPY VARCHAR2,
1516 p_api_version IN NUMBER) IS
1517 l_api_version CONSTANT NUMBER := 1.0;
1518 l_api_name CONSTANT VARCHAR2(30) := 'Update_fund_status';
1519 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||
1520 '.' ||
1521 l_api_name;
1522 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1523 l_msg_count NUMBER;
1524 l_msg_data VARCHAR2(4000);
1525 l_old_status_code VARCHAR2(30);
1526 l_old_user_status_id NUMBER;
1527 l_new_status_code VARCHAR2(30);
1528 l_request_id NUMBER;
1529 l_is_requestor_owner VARCHAR2(1);
1530 l_approver_id NUMBER;
1531 l_status_type VARCHAR2(30) := 'OZF_FUND_STATUS';
1532 l_workflow_process VARCHAR2(30) := 'AMSGAPP';
1533 l_item_type VARCHAR2(30) := 'AMSGAPP';
1534 l_reject_status_id NUMBER;
1535 l_old_owner_id NUMBER;
1536 l_resource_id NUMBER;
1537 l_fund_type VARCHAR2(30);
1538 l_list_line NUMBER;
1539 l_plan_id NUMBER;
1540
1541 CURSOR l_old_status IS
1542 SELECT status_code,
1543 user_status_id,
1544 owner,
1545 fund_type,
1546 plan_id
1547 FROM ozf_funds_all_b
1548 WHERE fund_id = p_fund_rec.fund_id;
1549
1550 -- Cursor to find the owner of the parent fund
1551 CURSOR c_parent_fund_owner(
1552 p_parent_fund_id NUMBER) IS
1553 SELECT owner
1554 FROM ozf_funds_all_b
1555 WHERE fund_id = p_parent_fund_id;
1556
1557 BEGIN
1558 SAVEPOINT update_fund_status;
1559 IF G_DEBUG THEN
1560 ozf_utility_pvt.debug_message(l_full_name ||
1561 '- enter');
1562 END IF;
1563 x_return_status := fnd_api.g_ret_sts_success;
1564 -- initiallize the out params 08/14/2001 mpande added
1565 x_submit_budget_approval := fnd_api.g_false;
1566 x_submit_child_approval := fnd_api.g_false;
1567 --Get old_status
1568 OPEN l_old_status;
1569 FETCH l_old_status INTO l_old_status_code, l_old_user_status_id, l_old_owner_id, l_fund_type, l_plan_id;
1570 CLOSE l_old_status;
1571 l_reject_status_id := ozf_utility_pvt.get_default_user_status(l_status_type, 'REJECTED');
1572 l_new_status_code := p_fund_rec.status_code;
1573
1574 IF l_old_status_code <> l_new_status_code THEN
1575 IF l_old_status_code IN ('CLOSED', 'ARCHIVED', 'CANCELLED') THEN
1576 IF l_new_status_code NOT IN ('ARCHIVED') THEN
1577 IF G_DEBUG THEN
1578 ozf_utility_pvt.debug_message(l_full_name ||
1579 'fund closed');
1580 END IF;
1581 IF l_fund_type = 'QUOTA' THEN
1582 ozf_utility_pvt.error_message('OZF_TP_QUOTA_BAN_UPDATE');
1583 ELSE
1584 ozf_utility_pvt.error_message('OZF_FUND_BAN_UPDATE');
1585 END IF;
1586 --return the old status
1587 x_new_status_id := l_old_user_status_id;
1588 x_new_status_code := l_old_status_code;
1589 x_return_status := fnd_api.g_ret_sts_error;
1590 ELSE
1591 x_new_status_code := p_fund_rec.status_code;
1592 x_new_status_id := p_fund_rec.user_status_id;
1593 END IF;
1594 -- Cases of valid approval:
1595 -- 1) WF approval process responds to request to APPROVE, in which case, old status
1596 -- equals PENDING and new status equals ONHOLD/ACTIVE.
1597 -- 2) WF approval is always started. If the owner does not find anybody to approve above him
1598 -- the workflow will autoamtically approve it.--
1599 -- The following case happens only from workflow
1600 ELSIF l_old_status_code = 'PENDING' THEN
1601 IF l_new_status_code IN ('ACTIVE', 'ON_HOLD') THEN
1602 --The following subroutine will take care of all the processing that take place for a active fund.
1603 IF G_DEBUG THEN
1604 ozf_utility_pvt.debug_message(l_full_name ||
1605 'fund aproved');
1606 END IF;
1607 x_new_status_id := ozf_utility_pvt.get_default_user_status(l_status_type, l_new_status_code);
1608 x_new_status_code := l_new_status_code;
1609 --END IF;
1610 ELSIF l_new_status_code = 'REJECTED' THEN
1611 -- Update the fund with status REJECTED
1612 IF G_DEBUG THEN
1613 ozf_utility_pvt.debug_message(l_full_name ||
1614 'fund rejected');
1615 END IF;
1616 x_new_status_id := ozf_utility_pvt.get_default_user_status(l_status_type, 'REJECTED');
1617 x_new_status_code := 'REJECTED';
1618 ELSIF l_new_status_code = 'DRAFT' THEN
1619 --An error occurred during the approval process, revert back to 'DRAFT
1620 --Update the fund with status 'DRAFT'
1621 IF G_DEBUG THEN
1622 ozf_utility_pvt.debug_message(l_full_name ||
1623 'error in aproval');
1624 END IF;
1625 x_new_status_id := ozf_utility_pvt.get_default_user_status(l_status_type, 'DRAFT');
1626 x_new_status_code := 'DRAFT';
1627 END IF; -- end pending
1628 ELSIF l_old_status_code IN ('DRAFT', 'REJECTED') THEN
1629 IF l_new_status_code IN ('ACTIVE', 'ON_HOLD') THEN
1630 -- niprakas added
1631 IF (p_fund_rec.fund_usage = 'MTRAN') THEN
1632 ozf_utility_pvt.write_conc_log(' The fund_usage is Mass Transfer');
1633 x_new_status_id := ozf_utility_pvt.get_default_user_status(
1634 l_status_type,
1635 l_new_status_code);
1636 x_new_status_code := l_new_status_code;
1637 IF p_fund_rec.parent_fund_id IS NOT NULL THEN
1638 x_submit_child_approval := fnd_api.g_true;
1639 END IF;
1640 END IF;
1641 -- niprakas ends
1642
1643 IF (p_fund_rec.fund_usage = 'ALLOC') THEN
1644 -- yzhao: 02/26/2003 fix bug bug 2823606 - when called from budget allocation activation, approval is not needed
1645 x_new_status_id := ozf_utility_pvt.get_default_user_status(
1646 l_status_type,
1647 l_new_status_code);
1648 x_new_status_code := l_new_status_code;
1649 IF p_fund_rec.parent_fund_id IS NOT NULL THEN
1650 x_submit_child_approval := fnd_api.g_true;
1651 END IF;
1652 ELSE
1653 /* yzhao: 01/29/2003 fix bug 2775762 MKTF1R9:1159.0127:FUNC: ACCRUAL BUDGET CANNOT GO ACTIVE
1654 for accrual budget, check if discount rule already defined
1655 SELECT 1
1656 FROM qp_list_lines
1657 WHERE list_header_id = (SELECT plan_id FROM ozf_funds_all_b WHERE fund_id = p_fund_rec.fund_id);
1658 */
1659 IF (l_fund_type = 'FULLY_ACCRUED') THEN
1660 l_list_line := ozf_offer_pvt.discount_lines_exist(p_list_header_id => l_plan_id);
1661 IF l_list_line <> 0 THEN
1662 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1663 FND_MESSAGE.set_name('OZF', 'OZF_OFFR_NO_DISC_LINES');
1664 FND_MSG_PUB.add;
1665 END IF;
1666 RAISE FND_API.g_exc_error;
1667 END IF;
1668 END IF;
1669 -- yzhao: 01/29/2003 fix bug 2775762 ends
1670
1671 IF p_fund_rec.parent_fund_id IS NOT NULL THEN
1672 -- changing status from 'DRAFT or 'REJECTED' to 'ACTIVE or ON_HOLD is
1673 -- equivalent to submitting for approval.
1674 -- Approval submission child fund
1675 IF G_DEBUG THEN
1676 ozf_utility_pvt.debug_message(l_full_name ||
1677 'owner' ||
1678 p_fund_rec.owner);
1679 END IF;
1680 x_submit_child_approval := fnd_api.g_true;
1681 OPEN c_parent_fund_owner(p_fund_rec.parent_fund_id);
1682 FETCH c_parent_fund_owner INTO l_approver_id;
1683
1684 IF (c_parent_fund_owner%NOTFOUND) THEN
1685 CLOSE c_parent_fund_owner;
1686
1687 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1688 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
1689 fnd_msg_pub.ADD;
1690 END IF;
1691
1692 RAISE fnd_api.g_exc_error;
1693 END IF;
1694
1695 CLOSE c_parent_fund_owner;
1696
1697 -- Check if requester is also the owner of the parent fund
1698 IF l_approver_id = p_fund_rec.owner THEN
1699 l_is_requestor_owner := 'Y';
1700 ELSE
1701 l_is_requestor_owner := 'N';
1702 END IF;
1703
1704 IF l_is_requestor_owner = 'N' THEN
1705 x_new_status_id := ozf_utility_pvt.get_default_user_status(l_status_type, 'PENDING');
1706 x_new_status_code := 'PENDING';
1707 ELSE
1708 x_new_status_id := ozf_utility_pvt.get_default_user_status(
1709 l_status_type,
1710 l_new_status_code);
1711 x_new_status_code := l_new_status_code;
1712 END IF;
1713 ELSE
1714 -- Here Approval submission would be done for parent less fund
1715 -- call the approval API**********************************
1716 -- the approval API would updatethe fund status to pending
1717 IF G_DEBUG THEN
1718 ozf_utility_pvt.debug_message('Approval');
1719 END IF;
1720 x_submit_budget_approval := fnd_api.g_true;
1721 x_new_status_id := ozf_utility_pvt.get_default_user_status(l_status_type, 'PENDING');
1722 x_new_status_code := 'PENDING';
1723 END IF; -- end of parent fund id check
1724 END IF; -- IF (p_fund_rec.fund_usage == 'ALLOC')
1725 -- 07/03/2001 bUG#1540719 -- Cancelled spellingwas incorrect
1726 ELSIF l_new_status_code NOT IN ('CANCELLED') THEN
1727 ozf_utility_pvt.error_message('OZF_FUND_WRONG_STATUS');
1728 --return the old status
1729 x_new_status_id := l_old_user_status_id;
1730 x_new_status_code := l_old_status_code;
1731 x_return_status := fnd_api.g_ret_sts_error;
1732 ELSE
1733 x_new_status_code := p_fund_rec.status_code;
1734 x_new_status_id := p_fund_rec.user_status_id;
1735 END IF; -- end for old draft
1736 -- 01/20/2003 yzhao: fix bug 2532491 TST 1158.7 MASTER FUNC : CAN CANCEL AND ARCHIEVE BUDGETS WITH AVAILABLE FUNDS
1737 ELSIF l_old_status_code IN('ACTIVE', 'ON_HOLD') THEN
1738 IF l_new_status_code IN('CANCELLED', 'CLOSED') THEN
1739 -- 06/14/2004 yzhao: for quota, do not check remaining amount before closing
1740 IF l_fund_type = 'QUOTA' THEN
1741 l_return_status := fnd_api.g_ret_sts_success;
1742 ELSE
1743 check_fund_before_close( p_fund_id => p_fund_rec.fund_id
1744 , x_return_status => l_return_status
1745 , x_msg_count => l_msg_count
1746 , x_msg_data => l_msg_data);
1747 END IF;
1748 IF l_return_status = fnd_api.g_ret_sts_success THEN
1749 x_new_status_code := p_fund_rec.status_code;
1750 x_new_status_id := p_fund_rec.user_status_id;
1751 ELSE
1752 -- can not close budget, return the old status
1753 x_new_status_id := l_old_user_status_id;
1754 x_new_status_code := l_old_status_code;
1755 x_return_status := fnd_api.g_ret_sts_error;
1756 END IF;
1757
1758 -- 05/11/2003 niprakas added the else loop for the bug#2950428
1759 ELSIF l_new_status_code IN('ACTIVE') THEN
1760 x_new_status_code := p_fund_rec.status_code;
1761 x_new_status_id := p_fund_rec.user_status_id;
1762 -- 05/11/2003 niprakas else loop ends here for the bug#2950428
1763
1764 ELSE
1765 -- Invalid status change, should not get here.
1766 ozf_utility_pvt.error_message('OZF_FUND_WRONG_STATUS');
1767 -- return the old status
1768 x_new_status_id := l_old_user_status_id;
1769 x_new_status_code := l_old_status_code;
1770 x_return_status := fnd_api.g_ret_sts_error;
1771 END IF;
1772 -- 01/20/2003 yzhao bug 2532491 ends
1773
1774 ELSE
1775 x_new_status_code := p_fund_rec.status_code;
1776 x_new_status_id := p_fund_rec.user_status_id;
1777 END IF;
1778 /* 12/18/2001 yzhao: locking rule locks owner for 'CANCELLED','CLOSED','ARCHIVED'
1779 ELSIF l_old_status_code IN ('ACTIVE','CANCELLED','CLOSED','ARCHIVED','ON_HOLD') THEN
1780 */
1781 ELSIF l_old_status_code IN ('ACTIVE', 'ON_HOLD') THEN
1782 IF G_DEBUG THEN
1783 ozf_utility_pvt.debug_message(l_full_name ||
1784 'fund no update except owner');
1785 END IF;
1786 --06/04/2001 added validations for accrual type fund
1787 -- all locking rules are to be implemented by locking rules so I am not raising a error here
1788 -- Accrual Type Offer could be modified
1789 l_resource_id := ozf_utility_pvt.get_resource_id(p_user_id => fnd_global.user_id);
1790
1791 -- the owner could be changed in a active status by the current owner
1792 -- 12/18/2001 yzhao: owner could be changed by super admin too
1793 IF p_fund_rec.owner <> l_old_owner_id THEN
1794 IF l_resource_id = l_old_owner_id
1795 OR ams_access_pvt.check_admin_access(l_resource_id) THEN
1796 x_new_status_id := l_old_user_status_id;
1797 x_new_status_code := l_old_status_code;
1798 x_return_status := fnd_api.g_ret_sts_success;
1799 ELSE
1800 --ozf_utility_pvt.error_message('OZF_FUND_BAN_UPDATE');
1801 IF l_fund_type = 'QUOTA' THEN
1802 ozf_utility_pvt.error_message('OZF_TP_QUOTA_UPDT_OWNER_PERM');
1803 ELSE
1804 ozf_utility_pvt.error_message('OZF_FUND_UPDT_OWNER_PERM');
1805 END IF;
1806 --return the old status
1807 x_new_status_id := l_old_user_status_id;
1808 x_new_status_code := l_old_status_code;
1809 x_return_status := fnd_api.g_ret_sts_error;
1810 END IF;
1811 /*
1812 ELSIF p_fund_rec.fund_type = 'FULLY_ACCRUED' THEN
1813 x_new_status_id := l_old_user_status_id;
1814 x_new_status_code := l_old_status_code;
1815 x_return_status := fnd_api.g_ret_sts_success;
1816 */
1817 ELSE
1818 x_new_status_code := p_fund_rec.status_code;
1819 x_new_status_id := p_fund_rec.user_status_id;
1820 x_return_status := fnd_api.g_ret_sts_success;
1821 END IF;
1822 ELSE
1823 x_new_status_code := p_fund_rec.status_code;
1824 x_new_status_id := p_fund_rec.user_status_id;
1825 END IF; --check for old and new diff
1826
1827 IF G_DEBUG THEN
1828 ozf_utility_pvt.debug_message(l_full_name ||
1829 ': end');
1830 END IF;
1831 EXCEPTION
1832 WHEN fnd_api.g_exc_error THEN
1833 ROLLBACK TO update_fund_status;
1834 x_return_status := fnd_api.g_ret_sts_error;
1835 fnd_msg_pub.count_and_get(
1836 p_encoded=> fnd_api.g_false,
1837 p_count=> x_msg_count,
1838 p_data=> x_msg_data);
1839 WHEN fnd_api.g_exc_unexpected_error THEN
1840 ROLLBACK TO update_fund_status;
1841 x_return_status := fnd_api.g_ret_sts_unexp_error;
1842 fnd_msg_pub.count_and_get(
1843 p_encoded=> fnd_api.g_false,
1844 p_count=> x_msg_count,
1845 p_data=> x_msg_data);
1846 WHEN OTHERS THEN
1847 ROLLBACK TO update_fund_status;
1848 x_return_status := fnd_api.g_ret_sts_unexp_error;
1849
1850 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1851 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1852 END IF;
1853
1854 fnd_msg_pub.count_and_get(
1855 p_encoded=> fnd_api.g_false,
1856 p_count=> x_msg_count,
1857 p_data=> x_msg_data);
1858 END update_fund_status;
1859
1860
1861 ----------------------------------------------------------------------
1862 -- PROCEDURE
1863 -- process_accrual
1864 --
1865 -- PURPOSE
1866 -- Based on accrual basis the offer accrues to customer if Accrual_Basis = 'Customer
1867 -- If accrual_Basis = 'SALES' it accrues to Sales force.
1868 --
1869 --
1870 -- HISTORY
1871 -- 10/7/2001 Srinivasa Rudravarapu Create.
1872 --
1873 -- parameters
1874 -- p_fund_rec IN fund_rec_type,
1875 -- p_mode IN VARCHAR2 Whether 'INSERT' or 'UPDATE'
1876 -- x_msg_count OUT NUMBER
1877 -- x_return_status OUT VARCAHR2
1878 -- x_msg_data OUT VARCHAR2
1879 -----------------------------------------------------------------------
1880 PROCEDURE process_accrual(
1881 p_fund_rec IN ozf_funds_pvt.fund_rec_type,
1882 p_api_version IN NUMBER,
1883 p_mode IN VARCHAR2,
1884 p_old_fund_status IN VARCHAR2 := NULL,
1885 x_return_status OUT NOCOPY VARCHAR2,
1886 x_msg_count OUT NOCOPY NUMBER,
1887 x_msg_data OUT NOCOPY VARCHAR2) IS
1888 l_api_name CONSTANT VARCHAR2(30) := 'process_accrual';
1889 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||
1890 '.' ||
1891 l_api_name;
1892 l_api_version CONSTANT NUMBER := 1.0;
1893 l_return_status VARCHAR2(1);
1894 l_msg_count NUMBER;
1895 l_msg_data VARCHAR2(4000);
1896 l_fund_rec ozf_funds_pvt.fund_rec_type := p_fund_rec;
1897 l_fund_id NUMBER;
1898 l_fund_status VARCHAR2(30);
1899 l_accrual_basis VARCHAR2(30);
1900 BEGIN
1901 IF G_DEBUG THEN
1902 ozf_utility_pvt.debug_message(l_full_name ||
1903 ': begin');
1904 END IF;
1905 SAVEPOINT process_accrual;
1906 x_return_status := fnd_api.g_ret_sts_success;
1907
1908 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1909 RAISE fnd_api.g_exc_unexpected_error;
1910 END IF;
1911
1912 -- dbms_OUTPUT.put_line('FUND ID ' ||
1913 -- p_fund_rec.fund_id ||
1914 -- 'fundtype' ||
1915 -- p_fund_rec.fund_type);
1916
1917 IF p_fund_rec.fund_id IS NULL
1918 OR p_fund_rec.fund_type <> 'FULLY_ACCRUED' THEN
1919 RETURN;
1920 END IF;
1921
1922 process_offers(
1923 p_fund_rec=> l_fund_rec,
1924 p_api_version=> l_api_version,
1925 p_mode=> p_mode,
1926 p_old_fund_status => p_old_fund_status,
1927 x_msg_count=> l_msg_count,
1928 x_msg_data=> l_msg_data,
1929 x_return_status=> l_return_status);
1930
1931 IF l_return_status = fnd_api.g_ret_sts_error THEN
1932 RAISE fnd_api.g_exc_error;
1933 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1934 RAISE fnd_api.g_exc_unexpected_error;
1935 END IF;
1936
1937 EXCEPTION
1938 WHEN fnd_api.g_exc_error THEN
1939 ROLLBACK TO process_accrual;
1940 x_return_status := fnd_api.g_ret_sts_error;
1941 fnd_msg_pub.count_and_get(
1942 p_encoded=> fnd_api.g_false,
1943 p_count=> x_msg_count,
1944 p_data=> x_msg_data);
1945 WHEN fnd_api.g_exc_unexpected_error THEN
1946 ROLLBACK TO process_accrual;
1947 x_return_status := fnd_api.g_ret_sts_unexp_error;
1948 fnd_msg_pub.count_and_get(
1949 p_encoded=> fnd_api.g_false,
1950 p_count=> x_msg_count,
1951 p_data=> x_msg_data);
1952 WHEN OTHERS THEN
1953 ROLLBACK TO process_accrual;
1954 x_return_status := fnd_api.g_ret_sts_unexp_error;
1955
1956 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1957 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1958 END IF;
1959
1960 fnd_msg_pub.count_and_get(
1961 p_encoded=> fnd_api.g_false,
1962 p_count=> x_msg_count,
1963 p_data=> x_msg_data);
1964 END process_accrual;
1965 END ozf_fundrules_pvt;