1 PACKAGE BODY OZF_ACTBUDGETRULES_PVT AS
2 /*$Header: ozfvarub.pls 120.10 2011/01/12 06:54:31 muthsubr ship $*/
3 -- NAME
4 -- OZF_ACTBUDGETRULES_PVT
5 --
6 -- HISTORY
7 -- 04/16/2000 feliu created by separated from ozf_actbudgets_pvt.
8 -- 5/10/2002 mpande Updated can_plan_more_budget function
9 -- 11/23/2005 kdass fixed bug 4658021
10 -- 12/08/2005 kdass Bug 4870218 - sql repository fix SQL ID 14892411
11 -- 14/04/2008 psomyaju Bug 6654242 - FP:11510-R12 6495406: ORACLE ERROR -01400 WHEN
12 -- ATTEMPTING TO RECONCILE ACCRUAL O
13 -- 01/15/2009 nirprasa fixed bug 7697861.
14 -- 05/01/2011 muthsubr bug#8867381.
15
16 /*****************************************************************************************/
17 -- Start of Comments
18 --
19 -- NAME
20 -- check_cat_activity_match
21 --
22 -- PURPOSE
23 -- This procedure is to validate budget record
24 --
25 -- NOTES
26 -- HISTORY
27 -- 04/10/2001 mpande Category and activity should match for the budget and the campaign or schedule
28 -- 06/08/2005 kdass Bug 4415878 SQL Repository Fix
29 -- 2/17/2010 nepanda Bug 9131648 : multi currency changes
30 /*****************************************************************************************/
31 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
32
33 PROCEDURE check_cat_activity_match (
34 p_used_by_id IN NUMBER
35 ,p_used_by_type IN VARCHAR2
36 ,p_budget_source_id IN NUMBER
37 ,x_return_status OUT NOCOPY VARCHAR2
38 ) IS
39 CURSOR c_get_activity_id (p_used_by_id IN NUMBER) IS
40 SELECT activity_id
41 FROM ams_campaign_schedules_b
42 WHERE schedule_id = p_used_by_id;
43 -- 03/28/2002 added for Offer
44 CURSOR c_get_off_activity_id (p_used_by_id IN NUMBER) IS
45 SELECT activity_media_id
46 FROM ozf_offers
47 WHERE qp_list_header_id = p_used_by_id;
48
49 CURSOR c_get_cat_activity (p_category_id IN NUMBER, p_activity_id IN NUMBER) IS
50 SELECT 'X'
51 FROM ams_cat_activities
52 WHERE category_id = p_category_id
53 AND activity_id = p_activity_id;
54
55 CURSOR c_cat_activity_count (p_category_id IN NUMBER) IS
56 SELECT COUNT(cat_activity_id) count
57 FROM ams_cat_activities
58 WHERE category_id = p_category_id;
59
60 CURSOR c_get_category_id (p_budget_source_id IN NUMBER) IS
61 SELECT category_id
62 FROM ozf_funds_all_b
63 WHERE fund_id = p_budget_source_id;
64
65 l_activity_id NUMBER;
66 l_category_id NUMBER;
67 l_dummy VARCHAR2 (3);
68 l_cat_act_count NUMBER;
69
70 BEGIN
71 x_return_status := fnd_api.g_ret_sts_success;
72
73 OPEN c_get_category_id (p_budget_source_id);
74 FETCH c_get_category_id INTO l_category_id;
75 CLOSE c_get_category_id;
76 -- check if category has a record in ams_cat_activities table
77 OPEN c_cat_activity_count (l_category_id);
78 FETCH c_cat_activity_count INTO l_cat_act_count;
79 CLOSE c_cat_activity_count;
80 -- if cat activity association is there then match for the activity id
81 IF l_cat_act_count <> 0 THEN
82 IF p_used_by_type = 'CSCH' THEN
83 OPEN c_get_activity_id (p_used_by_id);
84 FETCH c_get_activity_id INTO l_activity_id;
85 CLOSE c_get_activity_id;
86 ELSIF p_used_by_type = 'OFFR' THEN
87 OPEN c_get_off_activity_id (p_used_by_id);
88 FETCH c_get_off_activity_id INTO l_activity_id;
89 CLOSE c_get_off_activity_id;
90 END IF;
91
92 OPEN c_get_cat_activity (l_category_id, l_activity_id);
93 FETCH c_get_cat_activity INTO l_dummy;
94 CLOSE c_get_cat_activity;
95 IF l_dummy IS NULL THEN
96 ozf_utility_pvt.error_message ( 'OZF_CAT_ACTIVITY_MISMATCH');
97 x_return_status := fnd_api.g_ret_sts_error;
98 END IF;
99 END IF;
100 END check_cat_activity_match;
101
102
103 /*****************************************************************************************/
104 -- Start of Comments
105 --
106 -- NAME
107 -- check_transfer_amount_exists
108 -- PARAMETERS
109 -- p_object_id IN NUMBER -- in case of transfer it is the budget_source_id
110 -- ,p_object_type IN VARCHAR2
111 -- ,p_budget_source_id IN NUMBER
112 -- ,p_budget_source_type IN VARCHAR2
113
114 -- PURPOSE
115 -- This procedure is to validate budget record
116 --
117 -- NOTES
118 -- HISTORY
119 -- 04/10/2001 mpande Cannot tranfer to a budget if he does not have it from that particular budget
120 -- 08/05/2005 feliu modified for R12.
121 /*****************************************************************************************/
122 PROCEDURE check_transfer_amount_exists (
123 p_object_id IN NUMBER
124 ,p_object_type IN VARCHAR2
125 ,p_budget_source_id IN NUMBER
126 ,p_budget_source_type IN VARCHAR2
127 ,p_transfer_amt IN NUMBER
128 ,p_transfer_type IN VARCHAR2
129 ,x_return_status OUT NOCOPY VARCHAR2
130 ) IS
131 -- for TRANSFER type, check for individual budget.
132 CURSOR c_transfer_allowed IS
133 /*
134 SELECT (NVL(plan_curr_committed_amt,0)-NVL(plan_curr_utilized_amt,0)) total_amount
135 FROM ozf_object_fund_summary
136 WHERE object_id =p_object_id
137 AND object_type = p_object_type
138 AND fund_id = p_budget_source_id;
139 */
140 SELECT SUM (amount) total_amount
141 FROM(
142 SELECT --- request amount
143 NVL (SUM (a1.approved_amount), 0) amount
144 FROM ozf_act_budgets a1
145 WHERE a1.act_budget_used_by_id = p_object_id
146 AND a1.arc_act_budget_used_by = p_object_type
147 AND a1.budget_source_type = 'FUND'
148 AND a1.budget_source_id = p_budget_source_id
149 AND a1.status_code = 'APPROVED'
150 AND a1.transfer_type ='REQUEST'
151 UNION
152 SELECT -NVL (SUM (a2.approved_original_amount), 0) amount
153 FROM ozf_act_budgets a2
154 WHERE a2.budget_source_id = p_object_id
155 AND a2.budget_source_type = p_object_type
156 AND a2.act_budget_used_by_id = p_budget_source_id
157 AND a2.arc_act_budget_used_by = 'FUND'
158 AND a2.status_code = 'APPROVED'
159 AND a2.transfer_type = 'TRANSFER'
160 UNION
161 SELECT SUM(fund_request_amount) amount --nirprasa,12.2 ER 8399134 replace plan_curr_amount
162 FROM ozf_funds_utilized_all_b
163 where plan_type = p_object_type
164 and plan_id = p_object_id
165 and fund_id = p_budget_source_id);
166
167
168 -- for UTILIZED type, check for total committed amount.
169 CURSOR c_transfer_allowed_util IS
170 SELECT SUM(NVL(plan_curr_committed_amt,0)-NVL(plan_curr_utilized_amt,0)) total_amount
171 FROM ozf_object_fund_summary
172 WHERE object_id =p_object_id
173 AND object_type = p_object_type;
174
175 /*
176 SELECT parent_source, parent_curr, SUM (amount) total_amount
177 FROM (SELECT a1.fund_id parent_source, a1.currency_code parent_curr
178 ,NVL (SUM (a1.amount), 0) amount
179 FROM ozf_funds_utilized_all_b a1
180 WHERE a1.component_id = p_budget_source_id
181 AND a1.component_type = p_budget_source_type
182 -- AND a1.status_code = 'APPROVED' -- only approved record are present here
183 AND a1.utilization_type IN ('TRANSFER', 'REQUEST')
184 AND a1.fund_id = DECODE (p_object_type,'FUND',p_object_id , a1.fund_id )
185 -- AND a1.budget_source_type = DECODE (p_object_type, 'FUND', p_object_type, a1.budget_source_type )
186 GROUP BY a1.fund_id, a1.currency_code
187 UNION
188 SELECT a2.fund_id parent_source, a2.currency_code parent_curr
189 ,-NVL (SUM (a2.amount), 0) amount
190 FROM ozf_funds_utilized_all_b a2
191 WHERE a2.plan_id = p_budget_source_id
192 AND a2.plan_type = p_budget_source_type
193 -- yzhao: 12/02/2003 11.5.10 added CHARGEBACK
194 AND a2.utilization_type IN ('TRANSFER', 'REQUEST', 'UTILIZED','ADJUSTMENT','ACCRUAL','SALES_ACCRUAL', 'CHARGEBACK')
195 AND a2.fund_id = DECODE (p_object_type,'FUND',p_object_id , a2.fund_id )
196 -- AND a2.arc_act_budget_used_by = DECODE (p_object_type, 'FUND', p_object_type, a2.arc_act_budget_used_by )
197 -- AND a2.status_code = 'APPROVED' -- -- only approved record are present here
198 GROUP BY a2.fund_id, a2.currency_code)
199 GROUP BY parent_source, parent_curr
200 ORDER BY parent_source;
201
202 l_parent_src_rec c_transfer_allowed%ROWTYPE;
203 */
204 l_existing_amt NUMBER := 0;
205 --l_curr VARCHAR2 (30) := p_transfer_currency;
206 --l_converted_amount NUMBER := 0;
207 BEGIN
208 x_return_status := fnd_api.g_ret_sts_success;
209
210 IF p_transfer_type ='TRANSFER' THEN
211 OPEN c_transfer_allowed;
212 FETCH c_transfer_allowed INTO l_existing_amt;
213 CLOSE c_transfer_allowed;
214 ELSE
215 OPEN c_transfer_allowed_util;
216 FETCH c_transfer_allowed_util INTO l_existing_amt;
217 CLOSE c_transfer_allowed_util;
218 END IF;
219
220 /*
221 LOOP
222 FETCH c_transfer_allowed INTO l_parent_src_rec;
223 EXIT WHEN c_transfer_allowed%NOTFOUND;
224
225 IF l_curr <> l_parent_src_rec.parent_curr THEN
226 ozf_utility_pvt.convert_currency (
227 x_return_status=> x_return_status
228 ,p_from_currency=> l_parent_src_rec.parent_curr
229 ,p_to_currency=> l_curr
230 ,p_from_amount=> l_parent_src_rec.total_amount
231 ,x_to_amount=> l_converted_amount
232 );
233
234 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
235 RAISE fnd_api.g_exc_unexpected_error;
236 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
237 RAISE fnd_api.g_exc_error;
238 END IF;
239 ELSE
240 l_converted_amount := l_parent_src_rec.total_amount;
241 END IF;
242
243 l_existing_amt := l_existing_amt
244 + l_converted_amount;
245 END LOOP;
246 */
247
248 -- IF p_object_type NOT IN ('FUND','PTNR','PRIC','WKST') THEN
249 IF NVL (p_transfer_amt, 0) > NVL (l_existing_amt, 0) THEN
250 IF G_DEBUG THEN
251 ozf_utility_pvt.debug_message ('p_transfer_amt: ' || p_transfer_amt);
252 ozf_utility_pvt.debug_message ('p_transfer_type ' || p_transfer_type);
253 ozf_utility_pvt.debug_message ('l_existing_amt: ' || l_existing_amt);
254 ozf_utility_pvt.debug_message ('p_object_id: ' || p_object_id);
255 ozf_utility_pvt.debug_message ('p_budget_source_id: ' || p_budget_source_id);
256 END IF;
257
258 ozf_utility_pvt.error_message ('OZF_TRANSFER_NOT_ALLOWED');
259 x_return_status := fnd_api.g_ret_sts_error;
260 END IF;
261 -- END IF;
262
263 END check_transfer_amount_exists;
264
265
266 /*****************************************************************************************/
267 -- Start of Comments
268 --
269 -- NAME
270 -- check_market_elig_match
271 --
272 -- PURPOSE
273 -- This procedure is to validate budget record
274 --
275 -- NOTES
276 -- HISTORY
277 -- 04/10/2001 mpande MArket Eligibility should match for the budget and the campaign or schedule or offer
278 -- 8/7/2002 mpande commetend
279 /*****************************************************************************************
280 PROCEDURE check_market_elig_match (
281 p_used_by_id IN NUMBER
282 ,p_used_by_type IN VARCHAR2
283 ,p_budget_source_id IN NUMBER
284 ,x_return_status OUT NOCOPY VARCHAR2
285 ) IS
286 CURSOR c_get_market_elig (
287 p_used_by_id IN NUMBER
288 ,p_used_by_type IN VARCHAR2
289 ,p_budget_source_id IN NUMBER
290 ) IS
291 SELECT 'X'
292 FROM ams_act_market_segments mkt1
293 WHERE mkt1.arc_act_market_segment_used_by = p_used_by_type
294 AND mkt1.act_market_segment_used_by_id = p_used_by_id
295 AND mkt1.exclude_flag = 'N'
296 AND EXISTS ( SELECT mkt1.market_segment_id
297 FROM ams_act_market_segments mkt2
298 WHERE mkt2.arc_act_market_segment_used_by = 'FUND'
299 AND mkt2.act_market_segment_used_by_id = p_budget_source_id
300 AND mkt2.exclude_flag = 'N');
301
302 /* yzhao: 07/17/2001 check qp_modifiers for offer
303 CURSOR c_get_offer_market_elig (
304 p_used_by_id IN NUMBER
305 ,p_budget_source_id IN NUMBER
306 ) IS
307 SELECT 'X'
308 FROM qp_qualifiers qp
309 WHERE list_header_id = p_used_by_id
310 AND EXISTS ( SELECT 1
311 FROM ams_act_market_segments
312 WHERE arc_act_market_segment_used_by = 'FUND'
313 AND act_market_segment_used_by_id = p_budget_source_id
314 AND exclude_flag = qp.excluder_flag);
315
316 CURSOR c_market_elig_exists (p_budget_source_id IN NUMBER) IS
317 SELECT 'X'
318 FROM ams_act_market_segments mkt2
319 WHERE mkt2.arc_act_market_segment_used_by = 'FUND'
320 AND mkt2.act_market_segment_used_by_id = p_budget_source_id;
321
322 l_dummy1 VARCHAR2 (3);
323 l_dummy VARCHAR2 (3);
324 BEGIN
325 x_return_status := fnd_api.g_ret_sts_success;
326 OPEN c_market_elig_exists (p_budget_source_id);
327 FETCH c_market_elig_exists INTO l_dummy1;
328 CLOSE c_market_elig_exists;
329 --dbms_output.put_line('yzhao: market eligibility l_dummy1=' || l_dummy1 || ' used_by_type=' || p_used_by_type);
330
331 IF l_dummy1 IS NOT NULL THEN
332 /* yzhao: 07/17/2001 for offer check qp_modifiers, for others check ams_act_market_segments
333 IF p_used_by_type = 'OFFR' THEN
334 OPEN c_get_offer_market_elig (p_used_by_id, p_budget_source_id);
335 FETCH c_get_offer_market_elig INTO l_dummy;
336 CLOSE c_get_offer_market_elig;
337 ELSE
338 OPEN c_get_market_elig (p_used_by_id, p_used_by_type, p_budget_source_id);
339 FETCH c_get_market_elig INTO l_dummy;
340 CLOSE c_get_market_elig;
341 END IF;
342
343 --dbms_output.put_line('yzhao: market/offer eligibility l_dummy=' || l_dummy);
344 IF l_dummy IS NULL THEN
345 ozf_utility_pvt.error_message ( 'OZF_MARKET_ELIG_MISMATCH');
346 x_return_status := fnd_api.g_ret_sts_error;
347 END IF;
348 END IF;
349 END check_market_elig_match;
350
351
352 /*****************************************************************************************/
353 -- Start of Comments
354 --
355 -- NAME
356 -- check_product_elig_match
357 --
358 -- PURPOSE
359 -- This procedure is to validate budget record
360 --
361 -- NOTES
362 -- HISTORY
363 -- 04/10/2001 mpande Product Eiligibility should match for the budget and the campaign or schedule
364 -- 8/7/2002 mpande Commented
365 /*****************************************************************************************
366
367 PROCEDURE check_prod_elig_match (
368 p_used_by_id IN NUMBER
369 ,p_used_by_type IN VARCHAR2
370 ,p_budget_source_id IN NUMBER
371 ,x_return_status OUT NOCOPY VARCHAR2
372 ) IS
373 CURSOR c_get_product_elig (
374 p_used_by_id IN NUMBER
375 ,p_used_by_type IN VARCHAR2
376 ,p_budget_source_id IN NUMBER
377 ) IS
378 SELECT 'X'
379 FROM ams_act_products pdt1
380 WHERE pdt1.arc_act_product_used_by = p_used_by_type
381 AND pdt1.act_product_used_by_id = p_used_by_id
382 AND pdt1.excluded_flag = 'N'
383 AND EXISTS ( SELECT 1
384 FROM ams_act_products pdt2
385 WHERE pdt2.arc_act_product_used_by = 'FUND'
386 AND pdt2.act_product_used_by_id = p_budget_source_id
387 AND pdt2.excluded_flag = 'N');
388
389 CURSOR c_get_offer_product_elig (
390 p_used_by_id IN NUMBER
391 ,p_budget_source_id IN NUMBER
392 ) IS
393 SELECT 'X'
394 FROM qp_modifier_summary_v qp
395 WHERE list_header_id = p_used_by_id
396 AND EXISTS ( SELECT 1
397 FROM ams_act_products
398 WHERE arc_act_product_used_by = 'FUND'
399 AND act_product_used_by_id = p_budget_source_id
400 AND excluded_flag = qp.excluder_flag);
401
402 CURSOR c_product_elig_exists (p_budget_source_id IN NUMBER) IS
403 SELECT 'X'
404 FROM ams_act_products pdt2
405 WHERE pdt2.arc_act_product_used_by = 'FUND'
406 AND pdt2.act_product_used_by_id = p_budget_source_id;
407
408 l_dummy1 VARCHAR2 (3);
409 l_dummy VARCHAR2 (3);
410 BEGIN
411 x_return_status := fnd_api.g_ret_sts_success;
412 OPEN c_product_elig_exists (p_budget_source_id);
413 FETCH c_product_elig_exists INTO l_dummy1;
414 CLOSE c_product_elig_exists;
415
416 IF l_dummy1 IS NOT NULL THEN
417 IF p_used_by_type <> 'OFFR' THEN
418 OPEN c_get_product_elig (p_used_by_id, p_used_by_type, p_budget_source_id);
419 FETCH c_get_product_elig INTO l_dummy;
420 CLOSE c_get_product_elig;
421 ELSE
422 /* yzhao: 07/17/2001 for offer check qp_list_lines, for others check ams_act_products
423 OPEN c_get_offer_product_elig (p_used_by_id, p_budget_source_id);
424 FETCH c_get_offer_product_elig INTO l_dummy;
425 CLOSE c_get_offer_product_elig;
426 END IF;
427
428 IF l_dummy IS NULL THEN
429 ozf_utility_pvt.error_message ('OZF_PRODUCT_ELIG_MISMATCH');
430 x_return_status := fnd_api.g_ret_sts_error;
431 END IF;
432 END IF;
433 END check_prod_elig_match;
434 */
435
436 /*****************************************************************************************/
437 -- Start of Comments
438
439 -- NAME
440 -- source_has_enough_money
441 -- PURPOSE
442 -- Return Y if the budget source has enough
443 -- money to fund the approved amount for a
444 -- budget request; return N, otherwise.
445 -- HISTORY
446 -- 20-Aug-2000 choang Created.
447 -- 08/05/2005 feliu changed for R12 by using ozf_object_fund_summary.
448 /*****************************************************************************************/
449 FUNCTION source_has_enough_money (
450 p_source_type IN VARCHAR2
451 ,p_source_id IN NUMBER
452 ,p_approved_amount IN NUMBER
453 )
454 RETURN VARCHAR2 IS
455 l_approved_amount NUMBER;
456 /*
457 CURSOR c_approved_amount IS
458 SELECT NVL (SUM (approved_amount), 0)
459 FROM ams_act_budgets
460 WHERE arc_act_budget_used_by = p_source_type
461 AND act_budget_used_by_id = p_source_id;
462 */
463 CURSOR c_approved_amount IS
464 SELECT SUM(NVL(committed_amt,0)-NVL(utilized_amt,0)) total_amount
465 FROM ozf_object_fund_summary
466 WHERE object_id =p_source_id
467 AND object_type = p_source_type;
468
469 -- change by feliu on 03/26/04
470 /*
471 SELECT SUM (amount) total_amount
472 FROM (SELECT NVL (SUM (a1.amount), 0) amount
473 FROM ozf_funds_utilized_all_b a1
474 WHERE a1.component_id = p_source_id
475 AND a1.component_type = p_source_type
476 AND a1.utilization_type NOT IN
477 ('ADJUSTMENT', 'UTILIZED')
478 GROUP BY a1.fund_id, a1.currency_code
479 UNION
480 SELECT -NVL (SUM (a2.amount), 0) amount
481 FROM ozf_funds_utilized_all_b a2
482 WHERE a2.plan_id = p_source_id
483 AND a2.plan_type = p_source_type
484 GROUP BY a2.fund_id, a2.currency_code);
485 */
486
487 BEGIN
488 OPEN c_approved_amount;
489 FETCH c_approved_amount INTO l_approved_amount;
490 CLOSE c_approved_amount;
491
492 IF l_approved_amount >= p_approved_amount THEN
493 RETURN fnd_api.g_true;
494 ELSE
495 RETURN fnd_api.g_false;
496 END IF;
497 END source_has_enough_money;
498
499
500 /*****************************************************************************************/
501 -- Start of Comments
502 --
503 -- NAME
504 -- check_approval_required
505 -- PURPOSE
506 -- Return T if the budget approval required
507 -- HISTORY
508 -- 20-Feb-2001 mpande Created.
509 /*****************************************************************************************/
510 FUNCTION check_approval_required (
511 p_object IN VARCHAR2
512 ,p_object_id IN NUMBER
513 ,p_source_type IN VARCHAR2
514 ,p_source_id IN NUMBER
515 ,p_transfer_type IN VARCHAR2
516 )
517 RETURN VARCHAR2 IS
518 CURSOR c_campaign (p_object_id IN NUMBER) IS
519 SELECT custom_setup_id, owner_user_id
520 FROM ams_campaigns_vl
521 WHERE campaign_id = p_object_id;
522
523 CURSOR c_campaign_schl (p_object_id IN NUMBER) IS
524 SELECT custom_setup_id, owner_user_id
525 FROM ams_campaign_schedules_vl
526 WHERE schedule_id = p_object_id;
527
528 CURSOR c_eheader (p_object_id IN NUMBER) IS
529 SELECT setup_type_id, owner_user_id
530 FROM ams_event_headers_vl
531 WHERE event_header_id = p_object_id;
532
533 CURSOR c_eoffer (p_object_id IN NUMBER) IS
534 SELECT setup_type_id, owner_user_id
535 FROM ams_event_offers_vl
536 WHERE event_offer_id = p_object_id;
537
538 CURSOR c_deliverable (p_object_id IN NUMBER) IS
539 SELECT custom_setup_id, owner_user_id
540 FROM ams_deliverables_vl
541 WHERE deliverable_id = p_object_id;
542
543 CURSOR c_fund (p_object_id IN NUMBER) IS
544 SELECT custom_setup_id, owner
545 FROM ozf_funds_all_vl
546 WHERE fund_id = p_object_id;
547
548 CURSOR c_offer (p_object_id IN NUMBER) IS
549 SELECT custom_setup_id, owner_id
550 FROM ozf_offers
551 WHERE qp_list_header_id = p_object_id;
552
553 l_custom_setup_id NUMBER;
554 l_flag VARCHAR2 (1);
555 l_object_owner_id NUMBER;
556 l_source_owner_id NUMBER;
557 l_src_custom_setup_id NUMBER;
558 l_return_status VARCHAR2(1);
559 l_must_preview VARCHAR2(1) := 'Y';
560
561 --- the flag is null then no approval required
562 CURSOR c_appvl_reqd_flag (p_custom_setup_id IN NUMBER) IS
563 SELECT NVL (attr_available_flag, 'N')
564 FROM ams_custom_setup_attr
565 WHERE custom_setup_id = p_custom_setup_id
566 AND object_attribute = 'BAPL';
567 BEGIN
568 -- approval is required only for budget request
569 IF p_transfer_type = 'REQUEST' THEN
570 -- Campaign
571 IF p_object = 'CAMP' THEN
572 OPEN c_campaign (p_object_id);
573 FETCH c_campaign INTO l_custom_setup_id, l_object_owner_id;
574 CLOSE c_campaign;
575 -- Campaign Schdules
576 ELSIF p_object = 'CSCH' THEN
577 OPEN c_campaign_schl (p_object_id);
578 FETCH c_campaign_schl INTO l_custom_setup_id, l_object_owner_id;
579 CLOSE c_campaign_schl;
580 -- Event Header/Rollup Event
581 ELSIF p_object = 'EVEH' THEN
582 OPEN c_eheader (p_object_id);
583 FETCH c_eheader INTO l_custom_setup_id, l_object_owner_id;
584 CLOSE c_eheader;
585 -- Event Offer/Execution Event
586 ELSIF p_object IN ('EONE','EVEO') THEN
587 OPEN c_eoffer (p_object_id);
588 FETCH c_eoffer INTO l_custom_setup_id, l_object_owner_id;
589 CLOSE c_eoffer;
590 -- Deliverable
591 ELSIF p_object = 'DELV' THEN
592 OPEN c_deliverable (p_object_id);
593 FETCH c_deliverable INTO l_custom_setup_id, l_object_owner_id;
594 CLOSE c_deliverable;
595 ELSIF p_object = 'FUND' THEN
596 OPEN c_fund (p_object_id);
597 FETCH c_fund INTO l_custom_setup_id, l_object_owner_id;
598 CLOSE c_fund;
599 ELSIF p_object = 'OFFR' THEN
600 OPEN c_offer (p_object_id);
601 FETCH c_offer INTO l_custom_setup_id, l_object_owner_id;
602 CLOSE c_offer;
603 END IF;
604
605 --- checking for source type
606 -- Campaign
607 IF p_source_type = 'CAMP' THEN
608 OPEN c_campaign (p_source_id);
609 FETCH c_campaign INTO l_src_custom_setup_id, l_source_owner_id;
610 CLOSE c_campaign;
611 -- Campaign Schdules
612 ELSIF p_source_type = 'CSCH' THEN
613 OPEN c_campaign_schl (p_source_id);
614 FETCH c_campaign_schl INTO l_src_custom_setup_id, l_source_owner_id;
615 CLOSE c_campaign_schl;
616 -- Event Header/Rollup Event
617 ELSIF p_source_type = 'EVEH' THEN
618 OPEN c_eheader (p_source_id);
619 FETCH c_eheader INTO l_src_custom_setup_id, l_source_owner_id;
620 CLOSE c_eheader;
621 -- Event Offer/Execution Event
622 ELSIF p_source_type IN ('EONE','EVEO') THEN
623 OPEN c_eoffer (p_source_id);
624 FETCH c_eoffer INTO l_src_custom_setup_id, l_source_owner_id;
625 CLOSE c_eoffer;
626 -- Deliverable
627 ELSIF p_source_type = 'DELV' THEN
628 OPEN c_deliverable (p_source_id);
629 FETCH c_deliverable INTO l_src_custom_setup_id, l_source_owner_id;
630 CLOSE c_deliverable;
631 ELSIF p_source_type = 'FUND' THEN
632 OPEN c_fund (p_source_id);
633 FETCH c_fund INTO l_src_custom_setup_id, l_source_owner_id;
634 CLOSE c_fund;
635 ELSIF p_source_type = 'OFFR' THEN
636 OPEN c_offer (p_source_id);
637 FETCH c_offer INTO l_src_custom_setup_id, l_source_owner_id;
638 CLOSE c_offer;
639 END IF;
640
641 --
642 OPEN c_appvl_reqd_flag (l_custom_setup_id);
643 FETCH c_appvl_reqd_flag INTO l_flag;
644 CLOSE c_appvl_reqd_flag;
645
646 --kdass 23-NOV-2005 bug 4658021 - when the approver for a campaign or campaign schedule is the same
647 --person as the requestor, budget request approval is not required
648 IF l_flag = 'Y' AND p_object IN ('CAMP', 'CSCH') AND p_source_type = 'FUND' THEN
649
650 ams_approval_pvt.must_preview(p_activity_id => p_object_id
651 ,p_activity_type => p_object
652 ,p_approval_type => 'BUDGET'
653 ,p_act_budget_id => null
654 ,p_requestor_id => AMS_Utility_PVT.get_resource_id(FND_GLOBAL.user_id)
655 ,x_must_preview => l_must_preview
656 ,x_return_status => l_return_status
657 );
658 IF l_must_preview = 'N' THEN
659 l_flag := NULL;
660 END IF;
661 END IF;
662
663 -- if owner is different then check for approval flag
664 --10/30/2001 commented owner code for later release, we ahve to change workflow approvals API also
665 -- for owner approval logic
666 -- IF l_source_owner_id <> l_object_owner_id THEN
667 IF l_flag IS NULL THEN
668 RETURN fnd_api.g_false; --change to false by feliu from 11.5.10
669 ELSIF l_flag = 'Y' THEN
670 RETURN fnd_api.g_true;
671 ELSIF l_flag = 'N' THEN
672 RETURN fnd_api.g_false;
673 ELSE
674 RETURN fnd_api.g_false;
675 END IF;
676 /*
677 -- if owner is the same then donot submit for approval
678 ELSE
679 -- IMP mpande 08/10/2001 made it to true for a workaround in approvals workflow
680 RETURN fnd_api.g_true;
681 END IF;
682 */
683 ELSE -- else for transfer type 'TRANSFER'
684 RETURN fnd_api.g_false;
685 END IF;
686 EXCEPTION
687 WHEN OTHERS THEN
688 IF c_campaign%ISOPEN THEN
689 CLOSE c_campaign;
690 END IF;
691
692 IF c_campaign_schl%ISOPEN THEN
693 CLOSE c_campaign_schl;
694 END IF;
695
696 IF c_eheader%ISOPEN THEN
697 CLOSE c_eheader;
698 END IF;
699
700 IF c_eoffer%ISOPEN THEN
701 CLOSE c_eoffer;
702 END IF;
703
704 IF c_deliverable%ISOPEN THEN
705 CLOSE c_deliverable;
706 END IF;
707
708 IF c_offer%ISOPEN THEN
709 CLOSE c_offer;
710 END IF;
711
712 IF c_fund%ISOPEN THEN
713 CLOSE c_fund;
714 END IF;
715
716 RAISE;
717 END check_approval_required;
718
719 /*****************************************************************************************/
720 -- Start of Comments
721 --
722 -- NAME
723 -- can_plan_more_budget
724 -- PURPOSE
725 -- Return T if the object(CAMP, EVEH) total request amount is greater than the planned amount
726 -- in the active state only
727 -- HISTORY
728 -- 05/01/2001 mpande Created.
729 /*****************************************************************************************/
730 FUNCTION can_plan_more_budget (
731 p_object_type IN VARCHAR2
732 ,p_object_id IN NUMBER
733 ,p_request_amount IN NUMBER
734 ,p_act_budget_id IN NUMBER
735 )
736 RETURN VARCHAR2 IS
737 CURSOR c_campaign IS
738 SELECT budget_amount_tc, status_code
739 FROM ams_campaigns_vl
740 WHERE campaign_id = p_object_id;
741
742 CURSOR c_campaign_schl IS
743 SELECT budget_amount_tc, status_code
744 FROM ams_campaign_schedules_vl
745 WHERE schedule_id = p_object_id;
746
747 CURSOR c_eheader IS
748 SELECT fund_amount_tc, system_status_code
749 FROM ams_event_headers_vl
750 WHERE event_header_id = p_object_id;
751
752 CURSOR c_eoffer IS
753 SELECT fund_amount_tc, system_status_code
754 FROM ams_event_offers_vl
755 WHERE event_offer_id = p_object_id;
756
757 CURSOR c_deliverable IS
758 SELECT budget_amount_tc, status_code
759 FROM ams_deliverables_vl
760 WHERE deliverable_id = p_object_id;
761
762 -- this amount column would change
763 CURSOR c_offer IS
764 SELECT budget_amount_tc, status_code
765 FROM ozf_offers
766 WHERE qp_list_header_id = p_object_id;
767
768 l_amount NUMBER;
769 l_existing_amount NUMBER;
770 l_status_code VARCHAR2 (30);
771
772 --kdass 08-Jun-2005 Bug 4415878 SQL Repository Fix - changed the cursor query
773 CURSOR c_obj_amount IS
774 SELECT SUM (amount) amount FROM (
775 SELECT DECODE(a1.status_code ,'NEW',a1.request_amount,'APPROVED', a1.approved_amount,0) amount
776 FROM ozf_act_budgets a1
777 WHERE a1.act_budget_used_by_id = p_object_id
778 AND a1.arc_act_budget_used_by = p_object_type
779 AND a1.transfer_type = 'REQUEST'
780 AND a1.activity_budget_id <> NVL (p_act_budget_id, 0)
781 AND status_code <> 'REJECTED'
782 UNION ALL
783 SELECT DECODE(a1.status_code ,'NEW',a1.src_curr_request_amt,'APPROVED', -a1.approved_original_amount) amount
784 FROM ozf_act_budgets a1
785 WHERE a1.budget_source_id = p_object_id
786 AND a1.budget_source_type = p_object_type
787 AND a1.transfer_type = 'TRANSFER'
788 AND a1.activity_budget_id <> NVL (p_act_budget_id, 0)
789 AND status_code <> 'REJECTED');
790
791 /*
792 CURSOR c_obj_amount IS
793 SELECT SUM( NVL(
794 DECODE(a1.transfer_type ,
795 'REQUEST',
796 DECODE(a1.status_code ,'NEW',a1.request_amount,'APPROVED', a1.approved_amount,0),
797 'TRANSFER' ,
798 DECODE(a1.status_code ,'NEW',a1.src_curr_request_amt,'APPROVED', -a1.approved_original_amount))
799 ,0)
800 ) amount
801 FROM ozf_act_budgets a1
802 WHERE DECODE(a1.transfer_type , 'REQUEST', a1.act_budget_used_by_id, 'TRANSFER' , a1.budget_source_id ) = p_object_id
803 AND DECODE(a1.transfer_type , 'REQUEST', a1.arc_act_budget_used_by, 'TRANSFER' , a1.budget_source_type ) = p_object_type
804 AND a1.transfer_type <> 'UTILIZED'
805 AND a1.activity_budget_id <> NVL (p_act_budget_id, 0)
806 AND status_code <> 'REJECTED' ;
807 */
808 -- 5/10/2002 mpande commented the code
809 /*
810 CURSOR c_obj_amount IS
811 SELECT NVL (SUM (NVL (a1.approved_amount, a1.request_amount)), 0) amount
812 FROM ozf_act_budgets a1
813 WHERE a1.act_budget_used_by_id = p_object_id
814 AND a1.arc_act_budget_used_by = p_object_type
815 AND a1.transfer_type <> 'UTILIZED'
816 AND a1.activity_budget_id <> NVL (p_act_budget_id, 0)
817 AND status_code <> 'REJECTED';
818 */
819 BEGIN
820 -- Campaign
821 IF p_object_type = 'CAMP' THEN
822 OPEN c_campaign;
823 FETCH c_campaign INTO l_amount, l_status_code;
824 CLOSE c_campaign;
825 -- Campaign Schdules
826 ELSIF p_object_type = 'CSCH' THEN
827 OPEN c_campaign_schl;
828 FETCH c_campaign_schl INTO l_amount, l_status_code;
829 CLOSE c_campaign_schl;
830 -- Event Header/Rollup Event
831 ELSIF p_object_type = 'EVEH' THEN
832 OPEN c_eheader;
833 FETCH c_eheader INTO l_amount, l_status_code;
834 CLOSE c_eheader;
835 -- Event Offer/Execution Event
836 ELSIF p_object_type IN ('EONE','EVEO') THEN
837 OPEN c_eoffer;
838 FETCH c_eoffer INTO l_amount, l_status_code;
839 CLOSE c_eoffer;
840 -- Deliverable
841 ELSIF p_object_type = 'DELV' THEN
842 OPEN c_deliverable;
843 FETCH c_deliverable INTO l_amount, l_status_code;
844 CLOSE c_deliverable;
845
846 -- making the tem variable status_code = ACTIVE to make a cleaner code
847 IF l_status_code = 'AVAILABLE' THEN
848 l_status_code := 'ACTIVE';
849 END IF;
850 -- we do not need to check this for fund
851 ELSIF p_object_type = 'OFFR' THEN
852 OPEN c_offer;
853 FETCH c_offer INTO l_amount, l_status_code;
854 CLOSE c_offer;
855 -- have to add for EONE
856 END IF;
857
858 OPEN c_obj_amount;
859 FETCH c_obj_amount INTO l_existing_amount;
860 CLOSE c_obj_amount;
861
862 IF p_object_type <> 'FUND'
863 AND l_status_code <> 'ACTIVE' THEN
864 IF NVL (l_existing_amount, 0)
865 + NVL (p_request_amount, 0) > NVL (l_amount, 0) THEN
866 RETURN fnd_api.g_false;
867 ELSE
868 RETURN fnd_api.g_true;
869 END IF;
870 ELSE
871 RETURN fnd_api.g_true;
872 END IF;
873 EXCEPTION
874 WHEN OTHERS THEN
875 IF c_campaign%ISOPEN THEN
876 CLOSE c_campaign;
877 END IF;
878
879 IF c_campaign_schl%ISOPEN THEN
880 CLOSE c_campaign_schl;
881 END IF;
882
883 IF c_eheader%ISOPEN THEN
884 CLOSE c_eheader;
885 END IF;
886
887 IF c_eoffer%ISOPEN THEN
888 CLOSE c_eoffer;
889 END IF;
890
891 IF c_deliverable%ISOPEN THEN
892 CLOSE c_deliverable;
893 END IF;
894
895 IF c_offer%ISOPEN THEN
896 CLOSE c_offer;
897 END IF;
898
899 RAISE;
900 END can_plan_more_budget;
901
902 /*****************************************************************************************/
903 -- Start of Comments
904 --
905 -- NAME
906 -- budget_has_enough_money
907 -- PURPOSE
908 -- Return Y if the budget source has enough
909 -- money to fund the approved amount for a
910 -- budget request; return N, otherwise.
911 -- HISTORY
912 -- 20-Feb-2001 mpande Created.
913 -- 12/17/2001 mpande UPdated put = clause
914 /*****************************************************************************************/
915
916 FUNCTION budget_has_enough_money (p_source_id IN NUMBER, p_approved_amount IN NUMBER)
917 RETURN VARCHAR2 IS
918 l_approved_amount NUMBER;
919
920 --12/08/2005 kdass - sql repository fix SQL ID 14892411 - query the base table directly
921 CURSOR c_approved_amount IS
922 SELECT (NVL(original_budget, 0) - NVL(holdback_amt, 0)
923 + NVL(transfered_in_amt, 0) - NVL(transfered_out_amt, 0))
924 - NVL (committed_amt, 0)
925 FROM ozf_funds_all_b
926 WHERE fund_id = p_source_id;
927 /*
928 CURSOR c_approved_amount IS
929 SELECT NVL (available_budget, 0)
930 - NVL (committed_amt, 0)
931 FROM ozf_fund_details_v
932 WHERE fund_id = p_source_id;
933 */
934 BEGIN
935 OPEN c_approved_amount;
936 FETCH c_approved_amount INTO l_approved_amount;
937 CLOSE c_approved_amount;
938
939 IF l_approved_amount >= p_approved_amount THEN
940 RETURN fnd_api.g_true;
941 ELSE
942 RETURN fnd_api.g_false;
943 END IF;
944 END budget_has_enough_money;
945
946
947 /*****************************************************************************************/
948 -- Start of Comments
949 -- NAME
950 -- Create Note
951 -- PURPOSE
952 -- Create Note fro justification and comments
953 -- HISTORY
954 -- 02/23/2001 mpande CREATED
955 /*****************************************************************************************/
956 PROCEDURE create_note (
957 p_activity_type IN VARCHAR2
958 ,p_activity_id IN NUMBER
959 ,p_note IN VARCHAR2
960 ,p_note_type IN VARCHAR2
961 ,p_user IN NUMBER
962 ,x_msg_count OUT NOCOPY NUMBER
963 ,x_msg_data OUT NOCOPY VARCHAR2
964 ,x_return_status OUT NOCOPY VARCHAR2
965 ) IS
966 l_id NUMBER;
967 l_user NUMBER;
968 l_note_id NUMBER;
969
970 CURSOR c_resource IS
971 SELECT user_id user_id
972 FROM ams_jtf_rs_emp_v
973 WHERE resource_id = p_user;
974
975 CURSOR c_note (p_activity_type IN VARCHAR2, p_activity_id IN NUMBER, p_note_type IN VARCHAR2) IS
976 SELECT jtf_note_id
977 FROM jtf_notes_b
978 WHERE source_object_code = 'AMS_'
979 || p_activity_type
980 AND source_object_id = p_activity_id
981 AND note_type = p_note_type;
982 BEGIN
983 x_return_status := fnd_api.g_ret_sts_success;
984 OPEN c_resource;
985 FETCH c_resource INTO l_user;
986
987 IF c_resource%NOTFOUND THEN
988 fnd_message.set_name ('OZF', 'OZF_API_DEBUG_MESSAGE');
989 fnd_message.set_token ('ROW', SQLERRM);
990 fnd_msg_pub.ADD;
991 END IF;
992
993 CLOSE c_resource;
994 OPEN c_note (p_activity_type, p_activity_id, p_note_type);
995 FETCH c_note INTO l_note_id;
996 CLOSE c_note;
997
998 --Bugfix:6654242 - Added l_user check
999 IF l_user IS NULL THEN
1000 l_user := NVL(fnd_global.user_id, -1);
1001 END IF;
1002
1003
1004 IF l_note_id IS NOT NULL THEN
1005
1006 IF G_DEBUG THEN
1007 ozf_utility_pvt.debug_message('jtf_notes_pub.update_note');
1008 END IF;
1009
1010 -- For Bug#8867381 to avoid locking of the record in JTF_NOTES Table
1011 IF p_note <> 'CHARGEBACK' THEN
1012
1013 jtf_notes_pub.update_note (
1014 p_api_version=> 1.0
1015 ,x_return_status=> x_return_status
1016 ,x_msg_count=> x_msg_count
1017 ,x_msg_data=> x_msg_data
1018 ,p_jtf_note_id=> l_note_id
1019 ,p_entered_by=> l_user
1020 ,p_last_updated_by=> l_user
1021 ,p_notes=> p_note
1022 ,p_note_type=> p_note_type
1023 );
1024
1025 END IF;
1026
1027 ELSE
1028
1029 IF G_DEBUG THEN
1030 ozf_utility_pvt.debug_message('jtf_notes_pub.create_note');
1031 END IF;
1032
1033 jtf_notes_pub.create_note (
1034 p_api_version=> 1.0
1035 ,x_return_status=> x_return_status
1036 ,x_msg_count=> x_msg_count
1037 ,x_msg_data=> x_msg_data
1038 ,p_source_object_id=> p_activity_id
1039 ,p_source_object_code=> 'AMS_'
1040 || p_activity_type
1041 ,p_notes=> p_note
1042 ,p_note_status=> NULL
1043 ,p_entered_by=> l_user
1044 ,p_entered_date=> SYSDATE
1045 ,p_last_updated_by=> l_user
1046 ,x_jtf_note_id=> l_id
1047 ,p_note_type=> p_note_type
1048 ,p_last_update_date=> SYSDATE
1049 ,p_creation_date=> SYSDATE
1050 );
1051 END IF;
1052
1053 IF G_DEBUG THEN
1054 ozf_utility_pvt.debug_message('jtf_notes_pub returns: ' || x_return_status);
1055 END IF;
1056
1057 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1058 fnd_message.set_name ('OZF', 'OZF_API_DEBUG_MESSAGE');
1059 fnd_message.set_token ('ROW', SQLERRM);
1060 fnd_msg_pub.ADD;
1061 END IF;
1062 END create_note;
1063
1064 END OZF_ACTBUDGETRULES_PVT;
[Home] [Help]