[Home] [Help]
PACKAGE BODY: APPS.OZF_FUNDRULES_PVT
Source
1 PACKAGE BODY ozf_fundrules_pvt AS
2 /* $Header: ozfvfrub.pls 120.7.12020000.2 2013/02/22 13:49:35 bchaturv 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;
91 OPEN c_parent_amount;
88 END IF;
89
90 -- Fetch the parent fund amounts
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
239 fnd_message.set_name('OZF', 'OZF_ACTIVATE_FUND_PARENT');
236 IF l_parent_fund_type = 'QUOTA' THEN
237 fnd_message.set_name('OZF', 'OZF_TP_ACTIVATE_QUOTA_PARENT');
238 ELSE
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
384
381 l_parent_fund_type VARCHAR2(30);
382 BEGIN
383 x_return_status := fnd_api.g_ret_sts_success;
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(
512 l_api_name CONSTANT VARCHAR2(30) := '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
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
646 WHERE NAME = p_end_period_name;
643 WHERE NAME = p_end_period_name
644 UNION ALL
645 SELECT START_DATE, END_DATE FROM OZF_TIME_ENT_YEAR
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
785 -- NOTES
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
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';
901 /* yzhao: 12/17/2001 create a REQUEST rathen than TRANSFER so it shows in offer screen
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';
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(
1006 l_offer_hdr_rec.status_code := 'DRAFT';
1003 l_status_type,
1004 'REJECTED');
1005 ELSE
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
1071 --//12764004 TPM Upgrade ER
1072 -- Fix for Bug # 14500493
1073 -- p_fund_rec.org_id has the default operating unit value.
1074 -- In case of Update, for any Update in Accrual Budget, the Offer OU was changing to Default OU.
1075 -- Changed the code to not pass any value for offer's org id .
1076
1077 -- Fix for Bug #14736414
1078 -- Global flag for an offer is checkedd against the profile global flag
1079
1080 IF p_mode = 'CREATE' THEN
1081 IF NVL(fnd_profile.value('QP_SECURITY_CONTROL'), 'OFF') <> 'OFF' THEN
1082 -- QP_SECURITY_PROFILE IS ON.
1083 IF(p_fund_rec.org_id IS NULL OR p_fund_rec.org_id = FND_API.G_MISS_NUM) THEN
1084 -- DEFAULT OU IS NULL
1085 IF (NVL(fnd_profile.value('OZF_GLOBAL_FLAG'), 'N') = 'N') THEN
1086 OZF_Utility_PVT.Error_Message('OZF_ORG_ID_OR_GFLAG_REQD');
1087 RAISE FND_API.G_EXC_ERROR;
1088 ELSE
1089 l_offer_hdr_rec.global_flag := 'Y';
1090 END IF;
1091 ELSE
1092 l_offer_hdr_rec.orig_org_id := p_fund_rec.org_id;
1093 l_offer_hdr_rec.global_flag := 'N';
1094
1095 END IF;
1096 ELSE
1097 --QP_SECURITY_PROFILE IS OFF.
1098 IF (NVL(fnd_profile.value('OZF_GLOBAL_FLAG'), 'N') = 'N') THEN
1099 -- OZF_GLOBAL_FLAG IS NO.
1100 OZF_Utility_PVT.Error_Message('QP_SEC_PROF_AND_GFLAG_NOT_COMP');
1101 RAISE FND_API.G_EXC_ERROR;
1102 ELSE
1103 l_offer_hdr_rec.global_flag := 'Y';
1104 END IF;
1105 END IF;
1106
1107 ELSIF p_mode = 'UPDATE' THEN
1108
1109 OZF_Utility_PVT.debug_message(FND_LOG.LEVEL_STATEMENT,'ozf_fundrules_pvt','Update mode = p_fund_rec.org_id'|| p_fund_rec.org_id);
1110
1111 IF p_fund_rec.org_id IS NOT NULL AND p_fund_rec.org_id <> FND_API.G_MISS_NUM AND NVL(fnd_profile.value('QP_SECURITY_CONTROL'), 'OFF') <> 'OFF' THEN
1112 l_offer_hdr_rec.global_flag := 'N';
1116 END IF;
1113 l_offer_hdr_rec.orig_org_id := p_fund_rec.org_id; /*12_11_2012 pass it to offer API process_modifier*/
1114 ELSE
1115 l_offer_hdr_rec.global_flag := 'Y';
1117 END IF;
1118
1119 -- dbms_OUTPUT.put_line('modifier_leve ' ||
1120 -- p_fund_rec.accrual_discount_level);
1121 -- bug fix 3088198.
1122 -- IF p_fund_rec.status_code = 'ACTIVE' THEN
1123 /* yzhao: 03/03/2004 fix bug 3464511 - duplicate REQUEST records in ozf_funds_utilized_all whenever updating active accrual budget
1124 IF l_old_fund_status in('PENDING') AND p_fund_rec.status_code in('ACTIVE','ON_HOLD','REJECTED') THEN
1125 */
1126 IF (p_mode = 'ACTIVE' OR l_old_fund_status in('PENDING'))
1127 AND p_fund_rec.status_code in('ACTIVE','ON_HOLD','REJECTED', 'DRAFT') THEN
1128 /* yzhao: 08/09/2005 for fully accrual budget PENDING => DRAFT, call update_offer_status not process_modifier
1129 otherwise fully accrual budget can not be reverted to DRAFT from PENDING
1130 since process_modifer checks ams_status_order_rules, and PENDING => DRAFT not allowed for offer
1131 AND p_fund_rec.status_code in('ACTIVE','ON_HOLD','REJECTED') THEN
1132 */
1133 ozf_offer_pvt.update_offer_status
1134 (
1135 p_commit => fnd_api.g_false,
1136 x_return_status=> l_return_status,
1137 x_msg_count=> x_msg_count,
1138 x_msg_data=> x_msg_data,
1139 p_modifier_list_rec => l_offer_hdr_rec
1140 );
1141
1142 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1143 IF G_DEBUG THEN
1144 ozf_utility_pvt.debug_message('OZF_OFFR_UPDATE_SATAUS FAIL');
1145 END IF;
1146
1147 IF l_return_status = fnd_api.g_ret_sts_error THEN
1148 RAISE fnd_api.g_exc_error;
1149 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1150 RAISE fnd_api.g_exc_unexpected_error;
1151 END IF;
1152 END IF;
1153
1154 ELSE
1155 IF G_DEBUG THEN
1156 ozf_utility_pvt.debug_message('process modifiers');
1157 END IF;
1158 ozf_offer_pvt.process_modifiers(
1159 p_init_msg_list=> fnd_api.g_false,
1160 p_api_version=> 1.0,
1161 p_commit=> fnd_api.g_false,
1162 x_return_status=> l_return_status,
1163 x_msg_count=> x_msg_count,
1164 x_msg_data=> x_msg_data,
1165 p_modifier_list_rec=> l_offer_hdr_rec,
1166 p_modifier_line_tbl=> l_offer_line_tbl,
1167 p_offer_type=> l_offer_hdr_rec.offer_type,
1168 x_qp_list_header_id=> l_qp_list_header_id,
1169 x_error_location=> l_error_location);
1170 IF G_DEBUG THEN
1171 ozf_utility_pvt.debug_message(
1172 'l_return_status' ||
1173 l_return_status ||
1174 '-' ||
1175 l_error_location ||
1176 x_msg_data);
1177 END IF;
1178 END IF;
1179
1180 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1181 IF G_DEBUG THEN
1182 ozf_utility_pvt.debug_message('OZF_OFFR_QP_FAILURE' ||
1183 l_error_location ||
1184 x_msg_data);
1185 END IF;
1186
1187 -- ozf_utility_pvt.error_message('OZF_OFFR_QP_FAILURE'||l_error_location||x_msg_data);
1188 IF l_return_status = fnd_api.g_ret_sts_error THEN
1189 RAISE fnd_api.g_exc_error;
1190 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1191 RAISE fnd_api.g_exc_unexpected_error;
1192 END IF;
1193 END IF;
1194
1195 -- Update ozf_offers with status code 'PENDING' by incrementing object_version_number.
1196 IF l_offer_pending_flag = 'T' THEN
1197 l_offer_hdr_rec.user_status_id := ozf_utility_pvt.get_default_user_status(l_status_type, 'PENDING');
1198
1199 -- 10/11/2002 mpande pass the default user status and check what is the default status code
1200 UPDATE ozf_offers
1201 SET status_code = 'PENDING',
1202 user_status_id = l_offer_hdr_rec.user_status_id,
1203 object_version_number = object_version_number + 1
1204 WHERE offer_id = l_ozf_offer_id;
1205
1206 IF G_DEBUG THEN
1207 ozf_utility_pvt.debug_message('In update 1');
1208 END IF;
1209 END IF;
1210
1211 IF p_mode = 'CREATE' THEN
1212 UPDATE ozf_funds_all_b
1213 SET plan_id = l_qp_list_header_id,
1214 plan_type = 'OFFR'
1215 WHERE fund_id = p_fund_rec.fund_id;
1216 ELSIF p_mode = 'UPDATE' THEN
1217 l_qp_list_header_id := p_fund_rec.plan_id;
1218 IF G_DEBUG THEN
1219 ozf_utility_pvt.debug_message('qp_list_header_id =>' ||
1220 l_qp_list_header_id);
1221 END IF;
1222 END IF; -- end of p_mode = CREATE/UPDATE
1223
1224 IF G_DEBUG THEN
1225 ozf_utility_pvt.debug_message(l_full_name ||
1226 ': end update offer advanced option ');
1227 END IF;
1228 -- dbms_output.put_line('fundrules: process_modifiers to UPDATE returns ' || l_return_status || ' x_qp_list_header_id=' || l_qp_list_header_id);
1229 IF G_DEBUG THEN
1230 ozf_utility_pvt.debug_message(l_full_name ||
1231 ': begin exclusion ');
1232 END IF;
1233
1237 IF ( p_mode = 'CREATE'
1234 --kdass 11-MAR-2004 fixed bug 3465281 - advanced options is handled by offers, budgets need to handle this
1235 /*
1236 -- for Fund_status = 'DRAFT' or p_mode = 'CREATE'.
1238 OR p_fund_rec.status_code = 'DRAFT') THEN
1239 IF G_DEBUG THEN
1240 ozf_utility_pvt.debug_message(l_full_name ||
1241 ': begin update offer advanced option ');
1242 END IF;
1243 --- 08/20/2001 mpande addded for discount level and bucket to process advanced options
1244 l_offer_advd_opt_rec.list_header_id := l_qp_list_header_id;
1245 l_offer_advd_opt_rec.offer_type := l_offer_hdr_rec.offer_type;
1246 l_offer_advd_opt_rec.modifier_level_code := p_fund_rec.accrual_discount_level;
1247
1248 -- order level does not need a bucket
1249 IF p_fund_rec.accrual_discount_level <> 'ORDER' THEN
1250 l_offer_advd_opt_rec.pricing_group_sequence := p_fund_rec.accrual_method;
1251 END IF;
1252
1253 --l_offer_advd_opt_rec.PRINT_ON_INVOICE_FLAG VARCHAR2(1) := Fnd_Api.g_miss_char
1254 ozf_offer_pvt.process_adv_options(
1255 p_init_msg_list=> fnd_api.g_false,
1256 p_api_version=> 1.0,
1257 p_commit=> fnd_api.g_false,
1258 x_return_status=> l_return_status,
1259 x_msg_count=> l_msg_count,
1260 x_msg_data=> l_msg_data,
1261 p_advanced_options_rec=> l_offer_advd_opt_rec);
1262 IF G_DEBUG THEN
1263 ozf_utility_pvt.debug_message('ret status for process adv options =>' ||
1264 l_return_status);
1265 END IF;
1266
1267 IF l_return_status = fnd_api.g_ret_sts_error THEN
1268 RAISE fnd_api.g_exc_error;
1269 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1270 RAISE fnd_api.g_exc_unexpected_error;
1271 END IF;
1272 END IF;
1273 */
1274 EXCEPTION
1275 WHEN fnd_api.g_exc_error THEN
1276 ROLLBACK TO process_offers;
1277 x_return_status := fnd_api.g_ret_sts_error;
1278 fnd_msg_pub.count_and_get(
1279 p_encoded=> fnd_api.g_false,
1280 p_count=> x_msg_count,
1281 p_data=> x_msg_data);
1282 WHEN fnd_api.g_exc_unexpected_error THEN
1283 ROLLBACK TO process_offers;
1284 x_return_status := fnd_api.g_ret_sts_unexp_error;
1285 fnd_msg_pub.count_and_get(
1286 p_encoded=> fnd_api.g_false,
1287 p_count=> x_msg_count,
1288 p_data=> x_msg_data);
1289 WHEN OTHERS THEN
1290 ROLLBACK TO process_offers;
1291 x_return_status := fnd_api.g_ret_sts_unexp_error;
1292
1293 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1294 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1295 END IF;
1296
1297 fnd_msg_pub.count_and_get(
1298 p_encoded=> fnd_api.g_false,
1299 p_count=> x_msg_count,
1300 p_data=> x_msg_data);
1301 END process_offers;
1302
1303 ---------------------------------------------------------------------
1304 -- PROCEDURE
1305 -- process_approval
1306 --
1307 -- PURPOSE
1308 -- This API is called when fund is approved from a workflow.
1309 -- This API does the following transactions for a Active fund.
1310 -- 1) Record for holdback amount
1311 -- 2) Handle transactions for a Accrual type fund
1312 -- HISTORY
1313 -- 01/15/2001 Mumu Pande Create.
1314 -- NOTES
1315 ---------------------------------------------------------------------
1316 PROCEDURE process_approval(
1317 p_fund_rec IN ozf_funds_pvt.fund_rec_type,
1318 p_mode IN VARCHAR2,
1319 p_old_fund_status IN VARCHAR2 := NULL,
1320 x_return_status OUT NOCOPY VARCHAR2,
1321 x_msg_count OUT NOCOPY NUMBER,
1322 x_msg_data OUT NOCOPY VARCHAR2,
1323 p_api_version IN NUMBER) IS
1324 l_api_version CONSTANT NUMBER := 1.0;
1325 l_api_name CONSTANT VARCHAR2(30) := 'process_approval';
1326 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||
1327 '.' ||
1328 l_api_name;
1329 l_return_status VARCHAR2(1);
1330 l_msg_count NUMBER;
1331 l_msg_data VARCHAR2(2000);
1332 l_act_budget_rec ozf_actbudgets_pvt.act_budgets_rec_type;
1333 l_act_budget_id NUMBER;
1334 BEGIN
1335 -- If the fund_status is changing from 'DRAFT to 'ACTIVE', we need to create a record in the
1336 -- FUND_REQUESTS table for the holdback amount.
1337 SAVEPOINT process_approval;
1338 x_return_status := fnd_api.g_ret_sts_success;
1339
1340 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1341 RAISE fnd_api.g_exc_unexpected_error;
1342 END IF;
1343
1344 IF p_mode = 'ACTIVE' THEN
1345 IF (NVL(p_fund_rec.holdback_amt, 0) <> 0) THEN
1346 l_act_budget_rec.status_code := 'APPROVED';
1347 l_act_budget_rec.arc_act_budget_used_by := 'FUND'; -- hardcoded to fund
1348 l_act_budget_rec.act_budget_used_by_id := p_fund_rec.fund_id;
1352 l_act_budget_rec.approved_amount := p_fund_rec.holdback_amt; --- in transferring to fund currency
1349 l_act_budget_rec.requester_id := p_fund_rec.owner;
1350 l_act_budget_rec.approver_id := p_fund_rec.owner;
1351 l_act_budget_rec.request_amount := p_fund_rec.holdback_amt; --- in transferring to fund currency
1353 l_act_budget_rec.approved_original_amount := p_fund_rec.holdback_amt; --- in transferring to fund currency
1354 l_act_budget_rec.budget_source_type := 'FUND';
1355 l_act_budget_rec.budget_source_id := p_fund_rec.fund_id;
1356 l_act_budget_rec.transfer_type := 'RESERVE';
1357 l_act_budget_rec.transaction_type := 'CREDIT';
1358 l_act_budget_rec.approved_in_currency := p_fund_rec.currency_code_tc;
1359 l_act_budget_rec.adjusted_flag := 'N';
1360 --l_act_budget_rec.date_required_by := p_needbydate;
1361 -- Create_transfer record
1362 ozf_actbudgets_pvt.create_act_budgets(
1363 p_api_version=> l_api_version,
1364 x_return_status=> l_return_status,
1365 x_msg_count=> x_msg_count,
1366 x_msg_data=> x_msg_data,
1367 p_act_budgets_rec=> l_act_budget_rec,
1368 x_act_budget_id=> l_act_budget_id);
1369
1370 IF l_return_status = fnd_api.g_ret_sts_error THEN
1371 RAISE fnd_api.g_exc_error;
1372 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1373 RAISE fnd_api.g_exc_unexpected_error;
1374 END IF;
1375 END IF; -- end for holdback mat
1376 END IF;
1377
1378 IF p_fund_rec.fund_type = 'FULLY_ACCRUED' THEN
1379 -- 10/14/2002 mpande for 11.5.9
1380 ozf_fundrules_pvt.process_accrual(
1381 p_fund_rec=> p_fund_rec,
1382 p_api_version=> l_api_version,
1383 p_mode=> p_mode,
1384 p_old_fund_status => p_old_fund_status,
1385 x_return_status=> l_return_status,
1386 x_msg_count=> x_msg_count,
1387 x_msg_data=> x_msg_data);
1388
1389 IF l_return_status = fnd_api.g_ret_sts_error THEN
1390 RAISE fnd_api.g_exc_error;
1391 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1392 RAISE fnd_api.g_exc_unexpected_error;
1393 END IF;
1394 END IF;
1395
1396 IF G_DEBUG THEN
1397 ozf_utility_pvt.debug_message(l_full_name ||
1398 ': end');
1399 END IF;
1400 EXCEPTION
1401 WHEN fnd_api.g_exc_error THEN
1402 ROLLBACK TO process_approval;
1403 x_return_status := fnd_api.g_ret_sts_error;
1404 fnd_msg_pub.count_and_get(
1405 p_encoded=> fnd_api.g_false,
1406 p_count=> x_msg_count,
1407 p_data=> x_msg_data);
1408 WHEN fnd_api.g_exc_unexpected_error THEN
1409 ROLLBACK TO process_approval;
1410 x_return_status := fnd_api.g_ret_sts_unexp_error;
1411 fnd_msg_pub.count_and_get(
1412 p_encoded=> fnd_api.g_false,
1413 p_count=> x_msg_count,
1414 p_data=> x_msg_data);
1415 WHEN OTHERS THEN
1416 ROLLBACK TO process_approval;
1417 x_return_status := fnd_api.g_ret_sts_unexp_error;
1418
1419 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1420 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1421 END IF;
1422
1423 fnd_msg_pub.count_and_get(
1424 p_encoded=> fnd_api.g_false,
1425 p_count=> x_msg_count,
1426 p_data=> x_msg_data);
1427 END process_approval;
1428
1429
1430 -----------------------------------------------------------------------
1431 -- PROCEDURE
1432 -- check_fund_before_close
1433 --
1434 -- PURPOSE
1435 -- Check fund amount before close/cancel an ACTIVE/ON_HOLD budget
1436 -- fix bug 2532491 TST 1158.7 MASTER FUNC : CAN CANCEL AND ARCHIEVE BUDGETS WITH AVAILABLE FUNDS
1437 -- HISTORY
1438 -- 01/20/2003 yzhao Create
1439 -- parameters
1440 -- p_fund_id IN NUMBER
1441 -- x_return_status OUT VARCAHR2
1442 -----------------------------------------------------------------------
1443 PROCEDURE check_fund_before_close(
1444 p_fund_id IN NUMBER
1445 ,x_return_status OUT NOCOPY VARCHAR2
1446 ,x_msg_count OUT NOCOPY NUMBER
1447 ,x_msg_data OUT NOCOPY VARCHAR2)
1448 IS
1449 --12/08/2005 rimehrot - sql repository fix SQL ID 14893182 - query the base table directly
1450 --asylvia 11-May-2006 bug 5199719 - SQL ID 17779489
1451 CURSOR c_get_fund_amount IS
1452 SELECT (NVL(original_budget, 0) + NVL(transfered_in_amt,0) - NVL(transfered_out_amt, 0))
1453 , NVL(recal_committed, 0),
1454 NVL(utilized_amt, 0), NVL(earned_amt, 0), NVL(paid_amt, 0), fund_type -- yzhao: 11.5.10 added utilized_amt
1455 FROM ozf_funds_all_b
1456 WHERE fund_id = p_fund_id;
1457
1458 l_recal_flag VARCHAR2(1);
1459 l_total_budget NUMBER;
1460 l_committed_amt NUMBER;
1461 l_utilized_amt NUMBER;
1462 l_earned_amt NUMBER;
1463 l_paid_amt NUMBER;
1464 l_fund_type VARCHAR2(20);
1465 BEGIN
1466 x_return_status := fnd_api.g_ret_sts_success;
1467
1468 OPEN c_get_fund_amount;
1469 FETCH c_get_fund_amount INTO l_total_budget, l_committed_amt, l_utilized_amt, l_earned_amt, l_paid_amt, l_fund_type;
1470 CLOSE c_get_fund_amount;
1471
1472 IF l_total_budget > l_committed_amt THEN
1473 -- total > committed >= utilized >= earned >= paid
1474 -- don't close budget because there's available money
1478 ozf_utility_pvt.error_message('OZF_FUND_BAN_CLOSE');
1475 IF l_fund_type = 'QUOTA' THEN
1476 ozf_utility_pvt.error_message('OZF_TP_QUOTA_BAN_CLOSE');
1477 ELSE
1479 END IF;
1480 x_return_status := fnd_api.g_ret_sts_error;
1481 ELSIF l_total_budget < l_committed_amt THEN
1482 -- total < re-calculated committed This only happens when profile 'OZF_BUDGET_ADJ_ALLOW_RECAL' is 'Y'
1483 -- don't close budget because re-calculated committed has committed funds for over the pool of money originally available,
1484 -- need a budget transfer into the budget
1485 IF l_fund_type = 'QUOTA' THEN
1486 ozf_utility_pvt.error_message('OZF_TP_QUOTA_BAN_CLOSE');
1487 ELSE
1488 ozf_utility_pvt.error_message('OZF_FUND_BAN_CLOSE_COMM_MORE');
1489 END IF;
1490 x_return_status := fnd_api.g_ret_sts_error;
1491 ELSE
1492 -- total = committed
1493 IF l_committed_amt > l_utilized_amt THEN
1494 -- total = calculated committed > utilized
1495 -- don't close budget because there is committed fund but not yet utilized
1496 IF l_fund_type = 'QUOTA' THEN
1497 ozf_utility_pvt.error_message('OZF_TP_QUOTA_BAN_CLOSE');
1498 ELSE
1499 ozf_utility_pvt.error_message('OZF_FUND_BAN_CLOSE_UTIL_LESS');
1500 END IF;
1501 x_return_status := fnd_api.g_ret_sts_error;
1502 ELSIF l_committed_amt = l_utilized_amt THEN
1503 -- total = committed = utilized
1504 IF l_utilized_amt > l_earned_amt THEN
1505 -- total = calculated committed = utilized > earned
1506 -- don't close budget because there is utilized fund but not yet posted to GL
1507 IF l_fund_type = 'QUOTA' THEN
1508 ozf_utility_pvt.error_message('OZF_TP_QUOTA_BAN_CLOSE');
1509 ELSE
1510 ozf_utility_pvt.error_message('OZF_FUND_BAN_CLOSE_EARN_LESS');
1511 END IF;
1512 x_return_status := fnd_api.g_ret_sts_error;
1513 ELSIF l_utilized_amt = l_earned_amt THEN
1514 -- total = calculated committed = utilized = earned
1515 IF l_earned_amt > l_paid_amt THEN
1516 -- total = re-calculated committed = utilized = earned > paid
1517 -- don't close budget because there's un-paid fund (trade management is implemented,
1518 -- accrual earnings not paid out by claim or deduction yet).
1519 IF l_fund_type = 'QUOTA' THEN
1520 ozf_utility_pvt.error_message('OZF_TP_QUOTA_BAN_CLOSE');
1521 ELSE
1522 ozf_utility_pvt.error_message('OZF_FUND_BAN_CLOSE_PAID_LESS');
1523 END IF;
1524 x_return_status := fnd_api.g_ret_sts_error;
1525 ELSIF l_earned_amt = l_paid_amt THEN
1526 -- total = re-calculated committed = utilized = earned = paid
1527 x_return_status := fnd_api.g_ret_sts_success;
1528 END IF;
1529 END IF;
1530 END IF;
1531 END IF;
1532 EXCEPTION
1533 WHEN OTHERS THEN
1534 x_return_status := fnd_api.g_ret_sts_unexp_error;
1535 fnd_msg_pub.count_and_get(
1536 p_encoded => fnd_api.g_false
1537 ,p_count => x_msg_count
1538 ,p_data => x_msg_data);
1539 END check_fund_before_close;
1540
1541 -----------------------------------------------------------------------
1542 -- PROCEDURE
1543 -- update_fund_status
1544 --
1545 -- PURPOSE
1546 -- Update fund status .This procedure is called by the update fund API
1547 -- It takes care of all the status changes that take place in funds in the
1548 -- update mode
1549 -- HISTORY
1550 -- 01/15/2001 Mumu Pande Create.
1551 -- parameters p_fund_rec IN fund_rec_type,
1552 -- x_new_status_code OUT VARCHAR2 the new fund status code
1553 -- x_new_status_id OUT NUMBER
1554 -- x_return_status OUT VARCAHR2
1555 -----------------------------------------------------------------------
1556 PROCEDURE update_fund_status(
1557 p_fund_rec IN ozf_funds_pvt.fund_rec_type,
1558 x_new_status_code OUT NOCOPY VARCHAR2,
1559 x_new_status_id OUT NOCOPY NUMBER,
1560 x_submit_budget_approval OUT NOCOPY VARCHAR2,
1561 x_submit_child_approval OUT NOCOPY VARCHAR2,
1562 x_return_status OUT NOCOPY VARCHAR2,
1563 x_msg_count OUT NOCOPY NUMBER,
1564 x_msg_data OUT NOCOPY VARCHAR2,
1565 p_api_version IN NUMBER) IS
1566 l_api_version CONSTANT NUMBER := 1.0;
1567 l_api_name CONSTANT VARCHAR2(30) := 'Update_fund_status';
1568 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||
1569 '.' ||
1570 l_api_name;
1571 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1572 l_msg_count NUMBER;
1573 l_msg_data VARCHAR2(4000);
1574 l_old_status_code VARCHAR2(30);
1575 l_old_user_status_id NUMBER;
1576 l_new_status_code VARCHAR2(30);
1577 l_request_id NUMBER;
1578 l_is_requestor_owner VARCHAR2(1);
1579 l_approver_id NUMBER;
1580 l_status_type VARCHAR2(30) := 'OZF_FUND_STATUS';
1581 l_workflow_process VARCHAR2(30) := 'AMSGAPP';
1582 l_item_type VARCHAR2(30) := 'AMSGAPP';
1583 l_reject_status_id NUMBER;
1584 l_old_owner_id NUMBER;
1585 l_resource_id NUMBER;
1586 l_fund_type VARCHAR2(30);
1587 l_list_line NUMBER;
1588 l_plan_id NUMBER;
1589
1593 owner,
1590 CURSOR l_old_status IS
1591 SELECT status_code,
1592 user_status_id,
1594 fund_type,
1595 plan_id
1596 FROM ozf_funds_all_b
1597 WHERE fund_id = p_fund_rec.fund_id;
1598
1599 -- Cursor to find the owner of the parent fund
1600 CURSOR c_parent_fund_owner(
1601 p_parent_fund_id NUMBER) IS
1602 SELECT owner
1603 FROM ozf_funds_all_b
1604 WHERE fund_id = p_parent_fund_id;
1605
1606 BEGIN
1607 SAVEPOINT update_fund_status;
1608 IF G_DEBUG THEN
1609 ozf_utility_pvt.debug_message(l_full_name ||
1610 '- enter');
1611 END IF;
1612 x_return_status := fnd_api.g_ret_sts_success;
1613 -- initiallize the out params 08/14/2001 mpande added
1614 x_submit_budget_approval := fnd_api.g_false;
1615 x_submit_child_approval := fnd_api.g_false;
1616 --Get old_status
1617 OPEN l_old_status;
1618 FETCH l_old_status INTO l_old_status_code, l_old_user_status_id, l_old_owner_id, l_fund_type, l_plan_id;
1619 CLOSE l_old_status;
1620 l_reject_status_id := ozf_utility_pvt.get_default_user_status(l_status_type, 'REJECTED');
1621 l_new_status_code := p_fund_rec.status_code;
1622
1623 IF l_old_status_code <> l_new_status_code THEN
1624 IF l_old_status_code IN ('CLOSED', 'ARCHIVED', 'CANCELLED') THEN
1625 IF l_new_status_code NOT IN ('ARCHIVED') THEN
1626 IF G_DEBUG THEN
1627 ozf_utility_pvt.debug_message(l_full_name ||
1628 'fund closed');
1629 END IF;
1630 IF l_fund_type = 'QUOTA' THEN
1631 ozf_utility_pvt.error_message('OZF_TP_QUOTA_BAN_UPDATE');
1632 ELSE
1633 ozf_utility_pvt.error_message('OZF_FUND_BAN_UPDATE');
1634 END IF;
1635 --return the old status
1636 x_new_status_id := l_old_user_status_id;
1637 x_new_status_code := l_old_status_code;
1638 x_return_status := fnd_api.g_ret_sts_error;
1639 ELSE
1640 x_new_status_code := p_fund_rec.status_code;
1641 x_new_status_id := p_fund_rec.user_status_id;
1642 END IF;
1643 -- Cases of valid approval:
1644 -- 1) WF approval process responds to request to APPROVE, in which case, old status
1645 -- equals PENDING and new status equals ONHOLD/ACTIVE.
1646 -- 2) WF approval is always started. If the owner does not find anybody to approve above him
1647 -- the workflow will autoamtically approve it.--
1648 -- The following case happens only from workflow
1649 ELSIF l_old_status_code = 'PENDING' THEN
1650 IF l_new_status_code IN ('ACTIVE', 'ON_HOLD') THEN
1651 --The following subroutine will take care of all the processing that take place for a active fund.
1652 IF G_DEBUG THEN
1653 ozf_utility_pvt.debug_message(l_full_name ||
1654 'fund aproved');
1655 END IF;
1656 x_new_status_id := ozf_utility_pvt.get_default_user_status(l_status_type, l_new_status_code);
1657 x_new_status_code := l_new_status_code;
1658 --END IF;
1659 ELSIF l_new_status_code = 'REJECTED' THEN
1660 -- Update the fund with status REJECTED
1661 IF G_DEBUG THEN
1662 ozf_utility_pvt.debug_message(l_full_name ||
1663 'fund rejected');
1664 END IF;
1665 x_new_status_id := ozf_utility_pvt.get_default_user_status(l_status_type, 'REJECTED');
1666 x_new_status_code := 'REJECTED';
1667 ELSIF l_new_status_code = 'DRAFT' THEN
1668 --An error occurred during the approval process, revert back to 'DRAFT
1669 --Update the fund with status 'DRAFT'
1670 IF G_DEBUG THEN
1671 ozf_utility_pvt.debug_message(l_full_name ||
1672 'error in aproval');
1673 END IF;
1674 x_new_status_id := ozf_utility_pvt.get_default_user_status(l_status_type, 'DRAFT');
1675 x_new_status_code := 'DRAFT';
1676 END IF; -- end pending
1677 ELSIF l_old_status_code IN ('DRAFT', 'REJECTED') THEN
1678 IF l_new_status_code IN ('ACTIVE', 'ON_HOLD') THEN
1679 -- niprakas added
1680 IF (p_fund_rec.fund_usage = 'MTRAN') THEN
1681 ozf_utility_pvt.write_conc_log(' The fund_usage is Mass Transfer');
1682 x_new_status_id := ozf_utility_pvt.get_default_user_status(
1683 l_status_type,
1684 l_new_status_code);
1685 x_new_status_code := l_new_status_code;
1686 IF p_fund_rec.parent_fund_id IS NOT NULL THEN
1687 x_submit_child_approval := fnd_api.g_true;
1688 END IF;
1689 END IF;
1690 -- niprakas ends
1691
1692 IF (p_fund_rec.fund_usage = 'ALLOC') THEN
1693 -- yzhao: 02/26/2003 fix bug bug 2823606 - when called from budget allocation activation, approval is not needed
1694 x_new_status_id := ozf_utility_pvt.get_default_user_status(
1695 l_status_type,
1696 l_new_status_code);
1697 x_new_status_code := l_new_status_code;
1698 IF p_fund_rec.parent_fund_id IS NOT NULL THEN
1699 x_submit_child_approval := fnd_api.g_true;
1703 for accrual budget, check if discount rule already defined
1700 END IF;
1701 ELSE
1702 /* yzhao: 01/29/2003 fix bug 2775762 MKTF1R9:1159.0127:FUNC: ACCRUAL BUDGET CANNOT GO ACTIVE
1704 SELECT 1
1705 FROM qp_list_lines
1706 WHERE list_header_id = (SELECT plan_id FROM ozf_funds_all_b WHERE fund_id = p_fund_rec.fund_id);
1707 */
1708 IF (l_fund_type = 'FULLY_ACCRUED') THEN
1709 l_list_line := ozf_offer_pvt.discount_lines_exist(p_list_header_id => l_plan_id);
1710 IF l_list_line <> 0 THEN
1711 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1712 FND_MESSAGE.set_name('OZF', 'OZF_OFFR_NO_DISC_LINES');
1713 FND_MSG_PUB.add;
1714 END IF;
1715 RAISE FND_API.g_exc_error;
1716 END IF;
1717 END IF;
1718 -- yzhao: 01/29/2003 fix bug 2775762 ends
1719
1720 IF p_fund_rec.parent_fund_id IS NOT NULL THEN
1721 -- changing status from 'DRAFT or 'REJECTED' to 'ACTIVE or ON_HOLD is
1722 -- equivalent to submitting for approval.
1723 -- Approval submission child fund
1724 IF G_DEBUG THEN
1725 ozf_utility_pvt.debug_message(l_full_name ||
1726 'owner' ||
1727 p_fund_rec.owner);
1728 END IF;
1729 x_submit_child_approval := fnd_api.g_true;
1730 OPEN c_parent_fund_owner(p_fund_rec.parent_fund_id);
1731 FETCH c_parent_fund_owner INTO l_approver_id;
1732
1733 IF (c_parent_fund_owner%NOTFOUND) THEN
1734 CLOSE c_parent_fund_owner;
1735
1736 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1737 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
1738 fnd_msg_pub.ADD;
1739 END IF;
1740
1741 RAISE fnd_api.g_exc_error;
1742 END IF;
1743
1744 CLOSE c_parent_fund_owner;
1745
1746 -- Check if requester is also the owner of the parent fund
1747 IF l_approver_id = p_fund_rec.owner THEN
1748 l_is_requestor_owner := 'Y';
1749 ELSE
1750 l_is_requestor_owner := 'N';
1751 END IF;
1752
1753 IF l_is_requestor_owner = 'N' THEN
1754 x_new_status_id := ozf_utility_pvt.get_default_user_status(l_status_type, 'PENDING');
1755 x_new_status_code := 'PENDING';
1756 ELSE
1757 x_new_status_id := ozf_utility_pvt.get_default_user_status(
1758 l_status_type,
1759 l_new_status_code);
1760 x_new_status_code := l_new_status_code;
1761 END IF;
1762 ELSE
1763 -- Here Approval submission would be done for parent less fund
1764 -- call the approval API**********************************
1765 -- the approval API would updatethe fund status to pending
1766 IF G_DEBUG THEN
1767 ozf_utility_pvt.debug_message('Approval');
1768 END IF;
1769 x_submit_budget_approval := fnd_api.g_true;
1770 x_new_status_id := ozf_utility_pvt.get_default_user_status(l_status_type, 'PENDING');
1771 x_new_status_code := 'PENDING';
1772 END IF; -- end of parent fund id check
1773 END IF; -- IF (p_fund_rec.fund_usage == 'ALLOC')
1774 -- 07/03/2001 bUG#1540719 -- Cancelled spellingwas incorrect
1775 ELSIF l_new_status_code NOT IN ('CANCELLED') THEN
1776 ozf_utility_pvt.error_message('OZF_FUND_WRONG_STATUS');
1777 --return the old status
1778 x_new_status_id := l_old_user_status_id;
1779 x_new_status_code := l_old_status_code;
1780 x_return_status := fnd_api.g_ret_sts_error;
1781 ELSE
1782 x_new_status_code := p_fund_rec.status_code;
1783 x_new_status_id := p_fund_rec.user_status_id;
1784 END IF; -- end for old draft
1785 -- 01/20/2003 yzhao: fix bug 2532491 TST 1158.7 MASTER FUNC : CAN CANCEL AND ARCHIEVE BUDGETS WITH AVAILABLE FUNDS
1786 ELSIF l_old_status_code IN('ACTIVE', 'ON_HOLD') THEN
1787 IF l_new_status_code IN('CANCELLED', 'CLOSED') THEN
1788 -- 06/14/2004 yzhao: for quota, do not check remaining amount before closing
1789 IF l_fund_type = 'QUOTA' THEN
1790 l_return_status := fnd_api.g_ret_sts_success;
1791 ELSE
1792 check_fund_before_close( p_fund_id => p_fund_rec.fund_id
1793 , x_return_status => l_return_status
1794 , x_msg_count => l_msg_count
1795 , x_msg_data => l_msg_data);
1796 END IF;
1797 IF l_return_status = fnd_api.g_ret_sts_success THEN
1798 x_new_status_code := p_fund_rec.status_code;
1799 x_new_status_id := p_fund_rec.user_status_id;
1800 ELSE
1804 x_return_status := fnd_api.g_ret_sts_error;
1801 -- can not close budget, return the old status
1802 x_new_status_id := l_old_user_status_id;
1803 x_new_status_code := l_old_status_code;
1805 END IF;
1806
1807 -- 05/11/2003 niprakas added the else loop for the bug#2950428
1808 ELSIF l_new_status_code IN('ACTIVE') THEN
1809 x_new_status_code := p_fund_rec.status_code;
1810 x_new_status_id := p_fund_rec.user_status_id;
1811 -- 05/11/2003 niprakas else loop ends here for the bug#2950428
1812
1813 ELSE
1814 -- Invalid status change, should not get here.
1815 ozf_utility_pvt.error_message('OZF_FUND_WRONG_STATUS');
1816 -- return the old status
1817 x_new_status_id := l_old_user_status_id;
1818 x_new_status_code := l_old_status_code;
1819 x_return_status := fnd_api.g_ret_sts_error;
1820 END IF;
1821 -- 01/20/2003 yzhao bug 2532491 ends
1822
1823 ELSE
1824 x_new_status_code := p_fund_rec.status_code;
1825 x_new_status_id := p_fund_rec.user_status_id;
1826 END IF;
1827 /* 12/18/2001 yzhao: locking rule locks owner for 'CANCELLED','CLOSED','ARCHIVED'
1828 ELSIF l_old_status_code IN ('ACTIVE','CANCELLED','CLOSED','ARCHIVED','ON_HOLD') THEN
1829 */
1830 ELSIF l_old_status_code IN ('ACTIVE', 'ON_HOLD') THEN
1831 IF G_DEBUG THEN
1832 ozf_utility_pvt.debug_message(l_full_name ||
1833 'fund no update except owner');
1834 END IF;
1835 --06/04/2001 added validations for accrual type fund
1836 -- all locking rules are to be implemented by locking rules so I am not raising a error here
1837 -- Accrual Type Offer could be modified
1838 l_resource_id := ozf_utility_pvt.get_resource_id(p_user_id => fnd_global.user_id);
1839
1840 -- the owner could be changed in a active status by the current owner
1841 -- 12/18/2001 yzhao: owner could be changed by super admin too
1842 IF p_fund_rec.owner <> l_old_owner_id THEN
1843 IF l_resource_id = l_old_owner_id
1844 OR ams_access_pvt.check_admin_access(l_resource_id) THEN
1845 x_new_status_id := l_old_user_status_id;
1846 x_new_status_code := l_old_status_code;
1847 x_return_status := fnd_api.g_ret_sts_success;
1848 ELSE
1849 --ozf_utility_pvt.error_message('OZF_FUND_BAN_UPDATE');
1850 IF l_fund_type = 'QUOTA' THEN
1851 ozf_utility_pvt.error_message('OZF_TP_QUOTA_UPDT_OWNER_PERM');
1852 ELSE
1853 ozf_utility_pvt.error_message('OZF_FUND_UPDT_OWNER_PERM');
1854 END IF;
1855 --return the old status
1856 x_new_status_id := l_old_user_status_id;
1857 x_new_status_code := l_old_status_code;
1858 x_return_status := fnd_api.g_ret_sts_error;
1859 END IF;
1860 /*
1861 ELSIF p_fund_rec.fund_type = 'FULLY_ACCRUED' THEN
1862 x_new_status_id := l_old_user_status_id;
1863 x_new_status_code := l_old_status_code;
1864 x_return_status := fnd_api.g_ret_sts_success;
1865 */
1866 ELSE
1867 x_new_status_code := p_fund_rec.status_code;
1868 x_new_status_id := p_fund_rec.user_status_id;
1869 x_return_status := fnd_api.g_ret_sts_success;
1870 END IF;
1871 ELSE
1872 x_new_status_code := p_fund_rec.status_code;
1873 x_new_status_id := p_fund_rec.user_status_id;
1874 END IF; --check for old and new diff
1875
1876 IF G_DEBUG THEN
1877 ozf_utility_pvt.debug_message(l_full_name ||
1878 ': end');
1879 END IF;
1880 EXCEPTION
1881 WHEN fnd_api.g_exc_error THEN
1882 ROLLBACK TO update_fund_status;
1883 x_return_status := fnd_api.g_ret_sts_error;
1884 fnd_msg_pub.count_and_get(
1885 p_encoded=> fnd_api.g_false,
1886 p_count=> x_msg_count,
1887 p_data=> x_msg_data);
1888 WHEN fnd_api.g_exc_unexpected_error THEN
1889 ROLLBACK TO update_fund_status;
1890 x_return_status := fnd_api.g_ret_sts_unexp_error;
1891 fnd_msg_pub.count_and_get(
1892 p_encoded=> fnd_api.g_false,
1893 p_count=> x_msg_count,
1894 p_data=> x_msg_data);
1895 WHEN OTHERS THEN
1896 ROLLBACK TO update_fund_status;
1897 x_return_status := fnd_api.g_ret_sts_unexp_error;
1898
1899 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1900 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1901 END IF;
1902
1903 fnd_msg_pub.count_and_get(
1904 p_encoded=> fnd_api.g_false,
1905 p_count=> x_msg_count,
1906 p_data=> x_msg_data);
1907 END update_fund_status;
1908
1909
1910 ----------------------------------------------------------------------
1911 -- PROCEDURE
1912 -- process_accrual
1913 --
1914 -- PURPOSE
1915 -- Based on accrual basis the offer accrues to customer if Accrual_Basis = 'Customer
1916 -- If accrual_Basis = 'SALES' it accrues to Sales force.
1917 --
1918 --
1919 -- HISTORY
1920 -- 10/7/2001 Srinivasa Rudravarapu Create.
1921 --
1922 -- parameters
1923 -- p_fund_rec IN fund_rec_type,
1924 -- p_mode IN VARCHAR2 Whether 'INSERT' or 'UPDATE'
1925 -- x_msg_count OUT NUMBER
1926 -- x_return_status OUT VARCAHR2
1927 -- x_msg_data OUT VARCHAR2
1928 -----------------------------------------------------------------------
1929 PROCEDURE process_accrual(
1930 p_fund_rec IN ozf_funds_pvt.fund_rec_type,
1931 p_api_version IN NUMBER,
1932 p_mode IN VARCHAR2,
1933 p_old_fund_status IN VARCHAR2 := NULL,
1934 x_return_status OUT NOCOPY VARCHAR2,
1935 x_msg_count OUT NOCOPY NUMBER,
1936 x_msg_data OUT NOCOPY VARCHAR2) IS
1937 l_api_name CONSTANT VARCHAR2(30) := 'process_accrual';
1938 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||
1939 '.' ||
1940 l_api_name;
1941 l_api_version CONSTANT NUMBER := 1.0;
1942 l_return_status VARCHAR2(1);
1943 l_msg_count NUMBER;
1944 l_msg_data VARCHAR2(4000);
1945 l_fund_rec ozf_funds_pvt.fund_rec_type := p_fund_rec;
1946 l_fund_id NUMBER;
1947 l_fund_status VARCHAR2(30);
1948 l_accrual_basis VARCHAR2(30);
1949 BEGIN
1950 IF G_DEBUG THEN
1951 ozf_utility_pvt.debug_message(l_full_name ||
1952 ': begin');
1953 END IF;
1954 SAVEPOINT process_accrual;
1955 x_return_status := fnd_api.g_ret_sts_success;
1956
1957 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1958 RAISE fnd_api.g_exc_unexpected_error;
1959 END IF;
1960
1961 -- dbms_OUTPUT.put_line('FUND ID ' ||
1962 -- p_fund_rec.fund_id ||
1963 -- 'fundtype' ||
1964 -- p_fund_rec.fund_type);
1965
1966 IF p_fund_rec.fund_id IS NULL
1967 OR p_fund_rec.fund_type <> 'FULLY_ACCRUED' THEN
1968 RETURN;
1969 END IF;
1970
1971 process_offers(
1972 p_fund_rec=> l_fund_rec,
1973 p_api_version=> l_api_version,
1974 p_mode=> p_mode,
1975 p_old_fund_status => p_old_fund_status,
1976 x_msg_count=> l_msg_count,
1977 x_msg_data=> l_msg_data,
1978 x_return_status=> l_return_status);
1979
1980 IF l_return_status = fnd_api.g_ret_sts_error THEN
1981 RAISE fnd_api.g_exc_error;
1982 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1983 RAISE fnd_api.g_exc_unexpected_error;
1984 END IF;
1985
1986 EXCEPTION
1987 WHEN fnd_api.g_exc_error THEN
1988 ROLLBACK TO process_accrual;
1989 x_return_status := fnd_api.g_ret_sts_error;
1990 fnd_msg_pub.count_and_get(
1991 p_encoded=> fnd_api.g_false,
1992 p_count=> x_msg_count,
1993 p_data=> x_msg_data);
1994 WHEN fnd_api.g_exc_unexpected_error THEN
1995 ROLLBACK TO process_accrual;
1996 x_return_status := fnd_api.g_ret_sts_unexp_error;
1997 fnd_msg_pub.count_and_get(
1998 p_encoded=> fnd_api.g_false,
1999 p_count=> x_msg_count,
2000 p_data=> x_msg_data);
2001 WHEN OTHERS THEN
2002 ROLLBACK TO process_accrual;
2003 x_return_status := fnd_api.g_ret_sts_unexp_error;
2004
2005 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2006 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2007 END IF;
2008
2009 fnd_msg_pub.count_and_get(
2010 p_encoded=> fnd_api.g_false,
2011 p_count=> x_msg_count,
2012 p_data=> x_msg_data);
2013 END process_accrual;
2014 END ozf_fundrules_pvt;