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