[Home] [Help]
PACKAGE BODY: APPS.OZF_THRESHOLD_PVT
Source
1 PACKAGE BODY Ozf_Threshold_Pvt AS
2 /* $Header: ozfvtreb.pls 120.4 2006/07/21 09:03:41 kpatro noship $*/
3
4 -- ===============================================================
5 -- Start of Comments
6 -- Package name
7 -- ozf_threshold_pvt
8 -- Purpose
9 --
10 -- History
11 -- Created By - Siddharha Dutta
12 -- 29/04/2001 Feliu updated
13 -- 29/11/2001 Feliu Changed signature for validate_threshold.
14 -- 03/11/2002 Feliu Added start_process, call notification directly.
15 -- Remove package ams_threshold_notify.
16 -- 05/08/2002 Feliu Added re-calculated committed.
17 -- 30/04/2004 Ribha Added Earned Amount.
18 -- 10-May-2004 feliu add business event for notification.
19 -- 08/24/2004 Ribha 3842318 fixed
20 -- 06/08/2005 kdass Bug 4415878 SQL Repository Fix
21 -- 12-May-2006 asylvia Bug 5199719 - SQL Repository fixes
22 -- 21-Jul-2006 kpatro Bug 5390527 - fix for 'Validate Budget and Quota Thresholds' conc program
23 -- NOTE
24 -- Will prcess the thresholds and creates
25 -- - notification information in ams_act_logs
26 -- - table. Will make a call to notification
27 -- - package.
28 -- End of Comments
29 -- ===============================================================
30
31 G_PKG_NAME CONSTANT VARCHAR2(20) :='OZF_THRESHOLD_PVT';
32 G_FILE_NAME CONSTANT VARCHAR2(20) :='ozfvtreb.pls';
33 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
34
35
36 PROCEDURE start_process(
37 p_api_version_number IN NUMBER
38 ,x_msg_count OUT NOCOPY NUMBER
39 ,x_msg_data OUT NOCOPY VARCHAR2
40 ,x_return_status OUT NOCOPY VARCHAR2
41 ,p_owner_id IN NUMBER
42 ,p_parent_owner_id IN NUMBER
43 ,p_message_text IN VARCHAR2
44 ,p_activity_log_id IN NUMBER
45 )
46 IS
47 l_api_name CONSTANT VARCHAR2(30) := 'Start_Process';
48 l_return_status VARCHAR2(1);
49 l_strSubject VARCHAR2(30);
50 l_strChildSubject VARCHAR2(30);
51 l_notification_id NUMBER;
52 l_strBody VARCHAR2(2000);
53
54 BEGIN
55 IF G_DEBUG THEN
56 OZF_UTILITY_PVT.debug_message('Entering ams_threshold_notify.Start_process : ');
57 END IF;
58
59
60 -- Initialize API return status to SUCCESS
61 x_return_status := FND_API.G_RET_STS_SUCCESS;
62
63 fnd_message.set_name('OZF', 'OZF_THRESHOLD_SUBJECT');
64 l_strSubject := fnd_message.get;
65 fnd_message.set_name('OZF', 'OZF_THRESHOLD_CHILDSUBJ');
66 l_strChildSubject := fnd_message.get;
67
68 -- fnd_message.set_name('OZF', 'OZF_NOTIFY_HEADERLINE');
69 --l_strBody := fnd_message.get ||fnd_global.local_chr(10)||fnd_global.local_chr(10)||p_message_text;
70 l_strBody := p_message_text;
71 fnd_message.set_name('OZF', 'OZF_NOTIFY_FOOTER');
72 --l_strBody := l_strBody || fnd_global.local_chr(10) || fnd_global.local_chr(10) ||fnd_message.get ;
73 l_strBody := l_strBody ||fnd_message.get ;
74 ozf_utility_pvt.send_wf_standalone_message(
75 p_subject => l_strSubject
76 ,p_body => l_strBody
77 ,p_send_to_res_id => p_owner_id
78 ,x_notif_id => l_notification_id
79 ,x_return_status => l_return_status
80 );
81
82 IF l_return_status <> fnd_api.g_ret_sts_success THEN
83 RAISE fnd_api.g_exc_error;
84 END IF;
85
86
87 IF p_parent_owner_id <>0 THEN
88 ozf_utility_pvt.send_wf_standalone_message(
89 p_subject => l_strChildSubject
90 ,p_body => l_strBody
91 ,p_send_to_res_id => p_parent_owner_id
92 ,x_notif_id => l_notification_id
93 ,x_return_status => l_return_status
94 );
95 END IF;
96
97 IF l_return_status <> fnd_api.g_ret_sts_success THEN
98 RAISE fnd_api.g_exc_error;
99 END IF;
100
101 EXCEPTION
102
103 WHEN FND_API.G_EXC_ERROR THEN
104 x_return_status := FND_API.G_RET_STS_ERROR;
105 -- Standard call to get message count and if count=1, get the message
106 FND_MSG_PUB.Count_And_Get (
107 p_encoded => FND_API.G_FALSE,
108 p_count => x_msg_count,
109 p_data => x_msg_data
110 );
111
112 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
113 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
114 -- Standard call to get message count and if count=1, get the message
115 FND_MSG_PUB.Count_And_Get (
116 p_encoded => FND_API.G_FALSE,
117 p_count => x_msg_count,
118 p_data => x_msg_data
119 );
120
121 WHEN OTHERS THEN
122 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
123 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
124 THEN
125 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
126 END IF;
127 -- Standard call to get message count and if count=1, get the message
128 FND_MSG_PUB.Count_And_Get (
129 p_encoded => FND_API.G_FALSE,
130 p_count => x_msg_count,
131 p_data => x_msg_data
132 );
133 END start_process; /* START_PROCESS */
134
135
136 -----------------------------------------------------------------------
137 -- PROCEDURE
138 -- raise_business_event
139 --
140 -- HISTORY
141 -- 05/08/2004 feliu Created.
142 -----------------------------------------------------------------------
143
144
145 PROCEDURE raise_business_event(p_object_id IN NUMBER)
146 IS
147 l_item_key varchar2(30);
148 l_parameter_list wf_parameter_list_t;
149 BEGIN
150 l_item_key := p_object_id ||'_'|| TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
151 l_parameter_list := WF_PARAMETER_LIST_T();
152
153
154 IF G_DEBUG THEN
155 ozf_utility_pvt.debug_message(' threshold Id is :'||p_object_id );
156 END IF;
157
158 wf_event.AddParameterToList(p_name => 'P_THRESH_ID',
159 p_value => p_object_id,
160 p_parameterlist => l_parameter_list);
161
162 IF G_DEBUG THEN
163 ozf_utility_pvt.debug_message('Item Key is :'||l_item_key);
164 END IF;
165
166 wf_event.raise( p_event_name =>'oracle.apps.ozf.fund.threshold.reach',
167 p_event_key => l_item_key,
168 p_parameters => l_parameter_list);
169
170
171 EXCEPTION
172 WHEN OTHERS THEN
173 RAISE Fnd_Api.g_exc_error;
174 ozf_utility_pvt.debug_message('Exception in raising business event');
175 END;
176
177
178 -----------------------------------------------------------------------
179 -- PROCEDURE
180 -- value_limit
181 --
182 -- HISTORY
183
184 -----------------------------------------------------------------------
185 PROCEDURE value_limit
186 ( p_api_version_number IN NUMBER,
187 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
188 X_Msg_Count OUT NOCOPY NUMBER,
189 X_Msg_Data OUT NOCOPY VARCHAR2,
190 x_return_status OUT NOCOPY VARCHAR2,
191 p_budget_id IN NUMBER,
192 p_value_limit_type IN VARCHAR2,
193 p_off_on_line IN VARCHAR2,
194 x_result OUT NOCOPY NUMBER)
195 IS
196 l_budget_amount_tc NUMBER := 0;
197 l_committed_amt NUMBER := 0;
198 l_commit_amt NUMBER := 0;
199 l_api_version_number CONSTANT NUMBER := 1.0;
200 l_api_name CONSTANT VARCHAR2(30) := 'value_limit';
201
202 CURSOR c_committed_amt
203 IS
204 SELECT committed_amt
205 FROM ozf_fund_details_v
206 WHERE fund_id = p_budget_id;
207
208 CURSOR c_planned_amt
209 IS
210 SELECT planned_amt
211 FROM ozf_fund_details_v
212 WHERE fund_id = p_budget_id;
213
214
215 CURSOR c_utilized_amt
216 IS
217 SELECT earned_amt
218 FROM ozf_fund_details_v
219 WHERE fund_id = p_budget_id;
220
221 CURSOR c_earned_amt
222 IS
223 SELECT earned_amt
224 FROM ozf_fund_details_v
225 WHERE fund_id = p_budget_id;
226
227 CURSOR c_paid_amt
228 IS
229 SELECT paid_amt
230 FROM ozf_fund_details_v
231 WHERE fund_id = p_budget_id;
232
233 --asylvia 12-May-2006 bug 5199719 - SQL ID 17780673
234 CURSOR c_balance_amt
235 IS
236 select (NVL(original_budget, 0) + (NVL(transfered_in_amt, 0) - NVL(transfered_out_amt, 0))) ,committed_amt
237 FROM ozf_funds_all_b
238 WHERE fund_id = p_budget_id;
239
240 CURSOR c_re_committed_amt
241 IS
242 SELECT recal_committed
243 FROM ozf_fund_details_v
244 WHERE fund_id = p_budget_id;
245
246 BEGIN
247 -- Standard call to check for call compatibility.
248 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
249 p_api_version_number,
250 l_api_name,
251 G_PKG_NAME)
252 THEN
253 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
254 END IF;
255
256 -- Initialize message list if p_init_msg_list is set to TRUE.
257 -- IF FND_API.to_Boolean( p_init_msg_list )
258 -- THEN
259 -- FND_MSG_PUB.initialize;
260 -- END IF;
261
262 -- Debug Message
263 --OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
264
265
266 -- Initialize API return status to SUCCESS
267 x_return_status := FND_API.G_RET_STS_SUCCESS;
268
269 -- Derive the value_limit amount based on value_limit_type
270
271 IF p_value_limit_type = 'COMMITTED' THEN
272
273 OPEN c_committed_amt;
274 FETCH c_committed_amt INTO l_commit_amt;
275 CLOSE c_committed_amt;
276
277 IF l_commit_amt is NULL THEN
278 l_commit_amt := 0;
279 END IF;
280
281 ELSIF p_value_limit_type = 'RECOMMITTED' THEN
282
283 OPEN c_re_committed_amt;
284 FETCH c_re_committed_amt INTO l_commit_amt;
285 CLOSE c_re_committed_amt;
286
287 IF l_commit_amt is NULL THEN
288 l_commit_amt := 0;
289 END IF;
290
291 ELSIF p_value_limit_type = 'PLANNED' THEN
292
293 OPEN c_planned_amt;
294 FETCH c_planned_amt INTO l_commit_amt;
295 CLOSE c_planned_amt;
296
297 IF l_commit_amt is NULL THEN
298 l_commit_amt := 0;
299 END IF;
300
301 ELSIF p_value_limit_type = 'UTILIZED' THEN
302
303 OPEN c_utilized_amt;
304 FETCH c_utilized_amt INTO l_commit_amt;
305 CLOSE c_utilized_amt;
306
307 IF l_commit_amt is NULL THEN
308 l_commit_amt := 0;
309 END IF;
310
311 ELSIF p_value_limit_type = 'EARNED' THEN
312
313 OPEN c_earned_amt;
314 FETCH c_earned_amt INTO l_commit_amt;
315 CLOSE c_earned_amt;
316
317 IF l_commit_amt is NULL THEN
318 l_commit_amt := 0;
319 END IF;
320
321 ELSIF p_value_limit_type = 'PAID' THEN
322
323 OPEN c_paid_amt;
324 FETCH c_paid_amt INTO l_commit_amt;
325 CLOSE c_paid_amt;
326
327 IF l_commit_amt is NULL THEN
328 l_commit_amt := 0;
329 END IF;
330
331 ELSIF p_value_limit_type = 'BALANCE' THEN
332
333 OPEN c_balance_amt;
334 FETCH c_balance_amt INTO l_budget_amount_tc,l_committed_amt;
335 CLOSE c_balance_amt;
336
337 IF l_commit_amt is NULL THEN
338 l_commit_amt := 0;
339 END IF;
340
341 l_commit_amt := l_budget_amount_tc - l_committed_amt;
342
343 END IF; --for main IF/elsifs
344
345 x_result := l_commit_amt;
346
347 --OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
348
349 -- Standard call to get message count and if count is 1, get message info.
350 FND_MSG_PUB.Count_And_Get
351 (p_count => x_msg_count,
352 p_data => x_msg_data
353 );
354 EXCEPTION
355
356 WHEN FND_API.G_EXC_ERROR THEN
357 x_return_status := FND_API.G_RET_STS_ERROR;
358 -- Standard call to get message count and if count=1, get the message
359 FND_MSG_PUB.Count_And_Get (
360 -- p_encoded => FND_API.G_FALSE,
361 p_count => x_msg_count,
362 p_data => x_msg_data
363 );
364
365 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
366 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
367 -- Standard call to get message count and if count=1, get the message
368 FND_MSG_PUB.Count_And_Get (
369 --p_encoded => FND_API.G_FALSE,
370 p_count => x_msg_count,
371 p_data => x_msg_data
372 );
373
374 WHEN OTHERS THEN
375 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
376 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
377 THEN
378 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
379 END IF;
380 -- Standard call to get message count and if count=1, get the message
381 FND_MSG_PUB.Count_And_Get (
382 -- p_encoded => FND_API.G_FALSE,
383 p_count => x_msg_count,
384 p_data => x_msg_data
385 );
386
387 END value_limit;
388 -----------------------------------------------------------------------
389 -- PROCEDURE
390 -- base_line_amt
391 --
392 -- HISTORY
393
394 -----------------------------------------------------------------------
395 PROCEDURE base_line_amt(
396 p_api_version_number IN NUMBER,
397 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
398 X_Msg_Count OUT NOCOPY NUMBER,
399 X_Msg_Data OUT NOCOPY VARCHAR2,
400 x_return_status OUT NOCOPY VARCHAR2,
401 p_budget_id IN NUMBER,
402 p_percent IN NUMBER,
403 p_base_line_type IN VARCHAR2,
404 x_result OUT NOCOPY NUMBER)
405
406 IS
407 l_api_version_number CONSTANT NUMBER := 1.0;
408 l_api_name CONSTANT VARCHAR2(30) := 'base_line_amt';
409 l_budget_amt NUMBER := 0;
410
411 --asylvia 12-May-2006 bug 5199719 - SQL ID 17780673
412 CURSOR c_budgeted_amt
413 IS
414 select (NVL(original_budget, 0) + (NVL(transfered_in_amt, 0) - NVL(transfered_out_amt, 0)))
415 FROM ozf_funds_all_b
416 WHERE fund_id = p_budget_id;
417
418
419 CURSOR c_committed_amt
420 IS
421 SELECT committed_amt
422 FROM ozf_fund_details_v
423 WHERE fund_id = p_budget_id;
424
425 CURSOR c_planned_amt
426 IS
427 SELECT planned_amt
428 FROM ozf_fund_details_v
429 WHERE fund_id = p_budget_id;
430
431
432 CURSOR c_utilized_amt
433 IS
434 SELECT earned_amt
435 FROM ozf_fund_details_v
436 WHERE fund_id = p_budget_id;
437
438 CURSOR c_earned_amt
439 IS
440 SELECT earned_amt
441 FROM ozf_fund_details_v
442 WHERE fund_id = p_budget_id;
443
444 CURSOR c_paid_amt
445 IS
446 SELECT paid_amt
450 CURSOR c_re_committed_amt
447 FROM ozf_fund_details_v
448 WHERE fund_id = p_budget_id;
449
451 IS
452 SELECT recal_committed
453 FROM ozf_fund_details_v
454 WHERE fund_id = p_budget_id;
455
456 BEGIN
457 -- Standard call to check for call compatibility.
458 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
459 p_api_version_number,
460 l_api_name,
461 G_PKG_NAME)
462 THEN
463 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
464 END IF;
465
466 -- Initialize message list if p_init_msg_list is set to TRUE.
467 IF FND_API.to_Boolean( p_init_msg_list )
468 THEN
469 FND_MSG_PUB.initialize;
470 END IF;
471
472 -- Debug Message
473 --OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
474
475
476 -- Initialize API return status to SUCCESS
477 x_return_status := FND_API.G_RET_STS_SUCCESS;
478
479 IF p_base_line_type = 'BUDGET' THEN
480 -- Derive the percentage value on the base amount
481 OPEN c_budgeted_amt;
482 FETCH c_budgeted_amt INTO l_budget_amt;
483 CLOSE c_budgeted_amt;
484
485 IF l_budget_amt is NULL THEN
486 l_budget_amt := 0;
487 END IF;
488
489 ELSIF p_base_line_type = 'COMMITTED' THEN
490
491 OPEN c_committed_amt;
492 FETCH c_committed_amt INTO l_budget_amt;
493 CLOSE c_committed_amt;
494
495 IF l_budget_amt is NULL THEN
496 l_budget_amt := 0;
497 END IF;
498
499 ELSIF p_base_line_type = 'RECOMMITTED' THEN
500
501 OPEN c_re_committed_amt;
502 FETCH c_re_committed_amt INTO l_budget_amt;
503 CLOSE c_re_committed_amt;
504
505 IF l_budget_amt is NULL THEN
506 l_budget_amt := 0;
507 END IF;
508
509 ELSIF p_base_line_type = 'PLANNED' THEN
510 OPEN c_planned_amt;
511 FETCH c_planned_amt INTO l_budget_amt;
512 CLOSE c_planned_amt;
513
514 IF l_budget_amt is NULL THEN
515 l_budget_amt := 0;
516 END IF;
517
518 ELSIF p_base_line_type = 'UTILIZED' THEN
519 OPEN c_utilized_amt;
520 FETCH c_utilized_amt INTO l_budget_amt;
521 CLOSE c_utilized_amt;
522
523 IF l_budget_amt is NULL THEN
524 l_budget_amt := 0;
525 END IF;
526
527 ELSIF p_base_line_type = 'EARNED' THEN
528 OPEN c_earned_amt;
529 FETCH c_earned_amt INTO l_budget_amt;
530 CLOSE c_earned_amt;
531
532 IF l_budget_amt is NULL THEN
533 l_budget_amt := 0;
534 END IF;
535
536 ELSIF p_base_line_type = 'PAID' THEN
537 OPEN c_paid_amt;
538 FETCH c_paid_amt INTO l_budget_amt;
539 CLOSE c_paid_amt;
540
541 IF l_budget_amt is NULL THEN
542 l_budget_amt := 0;
543 END IF;
544
545 END IF;
546
547 IF l_budget_amt <> 0 THEN
548 x_result := (l_budget_amt*p_percent/100);
549 ELSE
550 x_result := 0;
551 END IF;
552
553 --OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
554
555 -- Standard call to get message count and if count is 1, get message info.
556 FND_MSG_PUB.Count_And_Get
557 (p_count => x_msg_count,
558 p_data => x_msg_data
559 );
560 EXCEPTION
561
562 WHEN FND_API.G_EXC_ERROR THEN
563 x_return_status := FND_API.G_RET_STS_ERROR;
564 -- Standard call to get message count and if count=1, get the message
565 FND_MSG_PUB.Count_And_Get (
566 p_encoded => FND_API.G_FALSE,
567 p_count => x_msg_count,
568 p_data => x_msg_data
569 );
570
571 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
572 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
573 -- Standard call to get message count and if count=1, get the message
574 FND_MSG_PUB.Count_And_Get (
575 p_encoded => FND_API.G_FALSE,
576 p_count => x_msg_count,
577 p_data => x_msg_data
578 );
579
580 WHEN OTHERS THEN
581 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
582 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
583 THEN
584 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
585 END IF;
586 -- Standard call to get message count and if count=1, get the message
587 FND_MSG_PUB.Count_And_Get (
588 p_encoded => FND_API.G_FALSE,
589 p_count => x_msg_count,
590 p_data => x_msg_data
591 );
592 END base_line_amt;
593 -----------------------------------------------------------------------
594 -- PROCEDURE
595 -- operation_result
596 --
597 -- HISTORY
598
602 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
599 -----------------------------------------------------------------------
600 PROCEDURE operation_result(
601 p_api_version_number IN NUMBER,
603 X_Msg_Count OUT NOCOPY NUMBER,
604 X_Msg_Data OUT NOCOPY VARCHAR2,
605 x_return_status OUT NOCOPY VARCHAR2,
606 p_lhs IN NUMBER,
607 p_rhs IN NUMBER,
608 p_operator_code IN VARCHAR2,
609 x_result OUT NOCOPY VARCHAR2)
610 IS
611 l_api_version_number CONSTANT NUMBER := 1.0;
612 l_api_name CONSTANT VARCHAR2(30) := 'operation_result';
613
614
615 BEGIN
616 -- Standard call to check for call compatibility.
617 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
618 p_api_version_number,
619 l_api_name,
620 G_PKG_NAME)
621 THEN
622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
623 END IF;
624
625 -- Initialize message list if p_init_msg_list is set to TRUE.
626 IF FND_API.to_Boolean( p_init_msg_list )
627 THEN
628 FND_MSG_PUB.initialize;
629 END IF;
630
631 -- Debug Message
632 --OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
633
634
635 -- Initialize API return status to SUCCESS
636 x_return_status := FND_API.G_RET_STS_SUCCESS;
637
638 -- Derive the result based on operator_code
639
640 IF p_operator_code = '2' THEN
641 IF p_lhs > p_rhs THEN
642 x_result := 'VIOLATED';
643 ELSE
644 x_result := 'NOT VIOLATED';
645 END IF;
646
647
648 ELSIF p_operator_code = '0' THEN
649 IF p_lhs < p_rhs THEN
650 x_result := 'VIOLATED';
651 ELSE
652 x_result := 'NOT VIOLATED';
653 END IF;
654
655 ELSIF p_operator_code = '1' THEN
656 IF p_lhs = p_rhs THEN
657 x_result := 'VIOLATED';
658 ELSE
659 x_result := 'NOT VIOLATED';
660 END IF;
661
662 END IF; -- for main IF/ELSIF
663
664 --OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
665
666 -- Standard call to get message count and if count is 1, get message info.
667 FND_MSG_PUB.Count_And_Get
668 (p_count => x_msg_count,
669 p_data => x_msg_data
670 );
671
672 EXCEPTION
673
674 WHEN FND_API.G_EXC_ERROR THEN
675 x_return_status := FND_API.G_RET_STS_ERROR;
676 -- Standard call to get message count and if count=1, get the message
677 FND_MSG_PUB.Count_And_Get (
678 p_encoded => FND_API.G_FALSE,
679 p_count => x_msg_count,
680 p_data => x_msg_data
681 );
682
683 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
684 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
685 -- Standard call to get message count and if count=1, get the message
686 FND_MSG_PUB.Count_And_Get (
687 p_encoded => FND_API.G_FALSE,
688 p_count => x_msg_count,
689 p_data => x_msg_data
690 );
691
692 WHEN OTHERS THEN
693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
694 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
695 THEN
696 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
697 END IF;
698 -- Standard call to get message count and if count=1, get the message
699 FND_MSG_PUB.Count_And_Get (
700 p_encoded => FND_API.G_FALSE,
701 p_count => x_msg_count,
702 p_data => x_msg_data
703 );
704 END operation_result;
705 -----------------------------------------------------------------------
706 -- PROCEDURE
707 -- verify_notification
708 -- In Parozf
709 -- p_api_version_number IN NUMBER
710 -- p_init_msg_list IN VARCHAR2 := fnd_api.g_false
711 -- p_budget_id IN NUMBER -- budget to which the threshold applies
712 -- p_threshold_id IN NUMBER -- threshold_id
713 -- p_threshold_rule_id IN NUMBER -- threhold_rule_id
714 -- p_frequency_period IN VARCHAR2 -- MONTHLY or DAILY
715 -- p_repeat_frequency IN NUMBER
716 -- It is a number . It signifies the frequency of resending the notifications
717 -- p_rule_start_date IN DATE
718 -- Standard Out params
719 -- x_msg_count OUT NUMBER
720 -- x_msg_data OUT VARCHAR2
721 -- x_return_status OUT VARCHAR2
722 -- x_result OUT VARCHAR2 -- NOTIFY OR NO_NOTIFY
723
724 -- Checks if there already is a notification sent to the budget owner or not
725 -- for a threshold rule violation
726
727 -----------------------------------------------------------------------
728 PROCEDURE verify_notification(
729 p_api_version_number IN NUMBER,
730 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
734 p_budget_id IN NUMBER,
731 X_Msg_Count OUT NOCOPY NUMBER,
732 X_Msg_Data OUT NOCOPY VARCHAR2,
733 x_return_status OUT NOCOPY VARCHAR2,
735 p_threshold_id IN NUMBER,
736 p_threshold_rule_id IN NUMBER,
737 p_frequency_period IN VARCHAR2,
738 p_repeat_frequency IN NUMBER,
739 p_rule_start_date IN DATE,
740 x_result OUT NOCOPY VARCHAR2)
741 IS
742 l_api_version_number CONSTANT NUMBER := 1.0;
743 l_api_name CONSTANT VARCHAR2(30) := 'verify_notification';
744 l_count NUMBER := 0;
745 l_notify_freq_days NUMBER := 0;
746 l_notified_date DATE := SYSDATE;
747
748 CURSOR c_notification_exist(x_threshold_id NUMBER,
749 x_threshold_rule_id NUMBER,
750 x_budget_id NUMBER) IS
751 SELECT Max(notification_creation_date)
752 FROM AMS_ACT_LOGS
753 WHERE arc_act_log_used_by = 'FTHO'
754 AND act_log_used_by_id = x_threshold_rule_id
755 AND budget_id = x_budget_id
756 AND threshold_id = x_threshold_id;
757
758 BEGIN
759 -- Standard call to check for call compatibility.
760 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
761 p_api_version_number,
762 l_api_name,
763 G_PKG_NAME)
764 THEN
765 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
766 END IF;
767
768 -- Initialize message list if p_init_msg_list is set to TRUE.
769 IF FND_API.to_Boolean( p_init_msg_list )
770 THEN
771 FND_MSG_PUB.initialize;
772 END IF;
773
774 -- Debug Message
775 --OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
776
777
778 -- Initialize API return status to SUCCESS
779 x_return_status := FND_API.G_RET_STS_SUCCESS;
780
781 IF p_frequency_period = 'DAILY' THEN
782 l_notify_freq_days := p_repeat_frequency;
783 END IF;
784
785 IF p_frequency_period ='WEEKLY' THEN
786 l_notify_freq_days := p_repeat_frequency*7;
787
788 END IF;
789
790 IF p_frequency_period ='MONTHLY' THEN
791 l_notify_freq_days := p_repeat_frequency * 30;
792 END IF;
793
794 IF p_frequency_period = 'QUARTERLY' THEN
795 l_notify_freq_days := p_repeat_frequency * 30 * 3;
796 END IF;
797
798 IF p_frequency_period = 'YEARLY' THEN
799 l_notify_freq_days := p_repeat_frequency * 365;
800 END IF;
801
802 -- checks entry in the ams_act_logs table for notification_purposes
803 OPEN c_notification_exist(p_threshold_id,
804 p_threshold_rule_id,
805 p_budget_id);
806 FETCH c_notification_exist INTO l_notified_date;
807 CLOSE c_notification_exist;
808
809 -- In case of no notification recorder.
810 IF l_notified_date is NULL THEN
811 l_notified_date := p_rule_start_date;
812 END IF;
813
814
815 IF SYSDATE - l_notified_date >= l_notify_freq_days THEN
816 x_result := ('NOTIFY');
817 ELSE
818 x_result := ('NOT NOTIFY');
819 END IF;
820
821 IF G_DEBUG THEN
822 OZF_UTILITY_PVT.debug_message('Private API: Notified day' || l_notify_freq_days || ' end ' ||x_result );
823 END IF;
824
825 -- Standard call to get message count and if count is 1, get message info.
826 FND_MSG_PUB.Count_And_Get
827 (p_count => x_msg_count,
828 p_data => x_msg_data
829 );
830 EXCEPTION
831
832 WHEN FND_API.G_EXC_ERROR THEN
833 x_return_status := FND_API.G_RET_STS_ERROR;
834 -- Standard call to get message count and if count=1, get the message
835 FND_MSG_PUB.Count_And_Get (
836 p_encoded => FND_API.G_FALSE,
837 p_count => x_msg_count,
838 p_data => x_msg_data
839 );
840
841 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
842 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
843 -- Standard call to get message count and if count=1, get the message
844 FND_MSG_PUB.Count_And_Get (
845 p_encoded => FND_API.G_FALSE,
846 p_count => x_msg_count,
847 p_data => x_msg_data
848 );
849
850 WHEN OTHERS THEN
851 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
852 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
853 THEN
854 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
855 END IF;
856 -- Standard call to get message count and if count=1, get the message
857 FND_MSG_PUB.Count_And_Get (
858 p_encoded => FND_API.G_FALSE,
859 p_count => x_msg_count,
860 p_data => x_msg_data
861 );
862 END verify_notification;
863 -----------------------------------------------------------------------
867 -- HISTORY
864 -- PROCEDURE
865 -- check_threshold_calendar
866 --
868
869 -----------------------------------------------------------------------
870 PROCEDURE validate_threshold
871 ( /*p_api_version_number IN NUMBER,
872
873 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
874 p_commit IN VARCHAR2 := FND_API.G_FALSE,
875 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
876 x_msg_count OUT NOCOPY NUMBER,
877 x_msg_data OUT NOCOPY VARCHAR2,
878 x_msg_buffer OUT NOCOPY VARCHAR2,
879 x_return_status OUT NOCOPY VARCHAR2
880 */
881 x_errbuf OUT NOCOPY VARCHAR2
882 ,x_retcode OUT NOCOPY NUMBER
883 )
884 IS
885 l_api_name CONSTANT VARCHAR2(30) := 'validate_threshold';
886 l_api_version_number CONSTANT NUMBER := 1.0;
887 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
888 l_count NUMBER := 0;
889 l_value_limit NUMBER := 0;
890 l_base_line_amt NUMBER := 0;
891 l_value_limit_type VARCHAR2(15);
892 l_operation_result VARCHAR2(25);
893 l_notification_result VARCHAR2(25);
894 l_return_status VARCHAR2(2);
895 l_operator_meaning VARCHAR2(25);
896 l_budget_name VARCHAR2(240); -- fix for 3842318
897 l_parent_fund_id NUMBER;
898 l_trans_id NUMBER;
899 l_log_id NUMBER;
900 l_owner_id NUMBER;
901 l_parent_owner_id NUMBER;
902 l_message VARCHAR2(5000);
903 l_period_meaning VARCHAR2(25);
904 l_msg_data VARCHAR2 (2000);
905 l_msg_count NUMBER;
906 l_errbuf VARCHAR2(2000);
907 l_retcode NUMBER := 0;
908
909 -- This cursor gets the threshold rules which are in active status
910
911 CURSOR c_threshold_rules_cur IS
912 SELECT r.threshold_rule_id,
913 r.threshold_id
914 FROM ozf_threshold_rules_all r, ozf_thresholds_all_b t
915 WHERE r.threshold_id = t.threshold_id
916 AND t.threshold_type = 'BUDGET'
917 AND r.enabled_flag = 'Y'
918 AND r.start_date <= SYSDATE
919 AND r.end_date >= SYSDATE;
920 --kdass 08-Jun-2005 Bug 4415878 SQL Repository Fix - removed the order by clause
921 --ORDER BY r.threshold_rule_id;
922
923
924 --This cursor will get all the enabled budgets which are tied with the Thresholds
925
926 CURSOR c_threshold_funds(p_threshold_rule_id NUMBER)
927 IS
928 SELECT a.fund_id budget_id,
929 a.fund_number budget_number,
930 a.parent_fund_id parent_budget_id,
931 a.planned_amt planned_amt,
932 a.committed_amt committed_amt,
933 a.paid_amt paid_amt,
934 a.available_amount available_amt,
935 a.budget_amount_tc budget_amount_tc,
936 a.start_date_active budget_start_date,
937 a.end_date_active budget_end_date,
938 a.earned_amt utilized_Amt,
939 c.value_limit value_limit,
940 c.start_period_name start_period_name,
941 c.end_period_name end_period_name,
942 c.operator_code operator_code,
943 c.start_date rule_start_date,
944 c.end_date rule_end_date,
945 c.period_type period_type,
946 c.threshold_id threshold_id,
947 c.threshold_rule_id threshold_rule_id,
948 c.percent_amount percent_amt,
949 c.base_line base_line,
950 c.frequency_period frequency_period,
951 c.converted_days conv_frequency_period, --Not used in current version.
952 c.repeat_frequency repeat_frequency
953 FROM ozf_funds_all_b a,
954 ozf_thresholds_all_b b,
955 ozf_threshold_rules_all c
956 WHERE a.threshold_id = b.threshold_id
957 AND a.status_code = 'ACTIVE'
958 AND b.enable_flag = 'Y'
959 AND b.threshold_id = c.threshold_id
960 AND c.threshold_rule_id = p_threshold_rule_id
961 AND c.end_date >= SYSDATE;
962
963 CURSOR c_log_seq IS
964 SELECT ams_act_logs_s.NEXTVAL
965 FROM DUAL;
966
967 CURSOR c_trans_seq IS
968 SELECT ams_act_logs_transaction_id_s.NEXTVAL
969 FROM DUAL;
970
971
972 CURSOR c_log_message (p_trans_id NUMBER)
973 IS
974 SELECT budget_id, log_message_text
975 FROM ams_act_logs
976 WHERE log_transaction_id = p_trans_id;
977
978 CURSOR c_owner(p_budget_id NUMBER)
979 IS
980 SELECT owner,parent_fund_id
981 FROM ozf_Funds_All_b
982 WHERE fund_id = p_budget_id;
983
984 CURSOR c_parent_owner(p_budget_id NUMBER)
985 IS
986 SELECT owner
987 FROM ozf_Funds_All_b
988 WHERE fund_id = p_budget_id;
989
990 CURSOR c_budget_name(p_budget_id NUMBER)
991 IS
992 SELECT short_name
993 FROM ozf_fund_details_v
994 WHERE fund_id = p_budget_id;
995
996 CURSOR c_valuelimit_name(p_lkup_code VARCHAR2)
997 IS
998 SELECT meaning
999 FROM ozf_lookups
1000 WHERE lookup_type = 'OZF_VALUE_LIMIT'
1001 AND lookup_code = p_lkup_code;
1002
1003 TYPE owner_record_type IS RECORD
1004 (owner NUMBER,
1008
1005 parent_owner NUMBER,
1006 message_text VARCHAR2(5000),
1007 remove_flag VARCHAR2(1));
1009 l_owner_record owner_record_type;
1010
1011 TYPE owner_table_type IS TABLE OF owner_record_type
1012 INDEX BY BINARY_INTEGER;
1013 l_owner_table owner_table_type;
1014 l_notify_table owner_table_type;
1015 l_valuelimit_name VARCHAR2(60);
1016 l_baseline_name VARCHAR2(60);
1017 l_today_date VARCHAR2(20);
1018 l_counter NUMBER;
1019
1020
1021 BEGIN
1022
1023 -- Standard Start of API savepoint
1024 SAVEPOINT VALIDATE_THRESHOLD_RULE_PVT;
1025
1026 -- Debug Message
1027 IF G_DEBUG THEN
1028 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1029 END IF;
1030
1031 -- Initialize API return status to SUCCESS
1032 -- x_return_status := FND_API.G_RET_STS_SUCCESS;
1033
1034 OPEN c_trans_seq;
1035 FETCH c_trans_seq INTO l_trans_id;
1036 CLOSE c_trans_seq;
1037 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start of Budget Threshold ........ ');
1038 FOR rule IN c_threshold_rules_cur
1039 LOOP
1040 BEGIN
1041 FOR budget IN c_threshold_funds(rule.threshold_rule_id)
1042 LOOP
1043 BEGIN
1044 value_limit(l_api_version_number,
1045 FND_API.G_FALSE,
1046 l_Msg_Count,
1047 l_Msg_Data,
1048 l_return_status,
1049 budget.budget_id,
1050 budget.value_limit,
1051 'OFFLINE',
1052 l_value_limit);
1053 IF G_DEBUG THEN
1054 OZF_UTILITY_PVT.debug_message('Value limit: ' || l_value_limit);
1055 END IF;
1056
1057 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1058 RAISE FND_API.G_EXC_ERROR;
1059 END IF;
1060
1061 --l_value_limit is lhs for operation_result input.
1062
1063 base_line_amt(l_api_version_number,
1064 FND_API.G_FALSE,
1065 l_Msg_Count,
1066 l_Msg_Data,
1067 l_return_status,
1068 budget.budget_id,
1069 budget.percent_amt,
1070 budget.base_line,
1071 l_base_line_amt);
1072 IF G_DEBUG THEN
1073 OZF_UTILITY_PVT.debug_message('Base limit: ' || l_base_line_amt);
1074 END IF;
1075
1076 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1077 RAISE FND_API.G_EXC_ERROR;
1078 END IF;
1079 --l_base_line_amt is rhs for operation_result imput.
1080
1081 operation_result(l_api_version_number,
1082 FND_API.G_FALSE,
1083 l_Msg_Count,
1084 l_Msg_Data,
1085 l_return_status,
1086 l_value_limit,
1087 l_base_line_amt,
1088 budget.operator_code,
1089 l_operation_result);
1090 IF G_DEBUG THEN
1091 OZF_UTILITY_PVT.debug_message('Operator: ' || l_operation_result);
1092 END IF;
1093 --Get operator meaning.
1094 IF budget.operator_code = '0' THEN
1095 fnd_message.set_name ('OZF', 'OZF_THRESHOLD_LESS');
1096 l_operator_meaning := fnd_message.get;
1097 ELSIF budget.operator_code = '1' THEN
1098 fnd_message.set_name ('OZF', 'OZF_THRESHOLD_EQUAL');
1099 l_operator_meaning := fnd_message.get;
1100 ELSE
1101 fnd_message.set_name ('OZF', 'OZF_THRESHOLD_LARGER');
1102 l_operator_meaning := fnd_message.get;
1103 END IF;
1104
1105 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1106 RAISE FND_API.G_EXC_ERROR;
1107 END IF;
1108 /* l_operation_result is deciding factor in calling verify_notification.
1109 if l_operation_result is 'VIOLATED' then we will call verify_notification
1110 else if the l_opearation_result is 'NOT VIOLATED' then we will not call verify_notification*/
1111
1112 IF l_operation_result = 'VIOLATED' THEN
1113 verify_notification( l_api_version_number,
1114 FND_API.G_FALSE,
1115 l_Msg_Count,
1116 l_Msg_Data,
1117 l_return_status,
1118 budget.budget_id,
1122 budget.repeat_frequency,
1119 budget.threshold_id,
1120 budget.threshold_rule_id,
1121 budget.frequency_period,
1123 budget.rule_start_date,
1124 l_notification_result);
1125
1126 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1127 RAISE FND_API.G_EXC_ERROR;
1128 END IF;
1129 --l_notification_result will drive write_to_log
1130 IF G_DEBUG THEN
1131 OZF_UTILITY_PVT.debug_message('Notify result: ' || l_notification_result );
1132 END IF;
1133
1134 --Get lookup meaning
1135
1136 l_period_meaning := ozf_utility_pvt.get_lookup_meaning('AMS_TRIGGER_FREQUENCY_TYPE'
1137 ,budget.frequency_period);
1138
1139 IF l_notification_result = 'NOTIFY' THEN
1140 -- raise business event.
1141 raise_business_event(p_object_id => budget.threshold_rule_id );
1142
1143 OPEN c_budget_name(budget.budget_id);
1144 FETCH c_budget_name INTO l_budget_name;
1145 CLOSE c_budget_name;
1146 OPEN c_valuelimit_name(budget.value_limit);
1147 FETCH c_valuelimit_name INTO l_valuelimit_name;
1148 CLOSE c_valuelimit_name;
1149
1150 select to_char(sysdate, 'dd-Mon-yyyy' ) into l_today_date from dual;
1151
1152 fnd_message.set_name ('OZF', 'OZF_WF_NTF_THRESHOLD_FYI');
1153 fnd_message.set_token ('BUDGET_NAME', l_budget_name, FALSE);
1154 fnd_message.set_token ('VALUE_LIMIT', l_valuelimit_name, FALSE);
1155 fnd_message.set_token ('OPERATOR', l_operator_meaning, FALSE);
1156 fnd_message.set_token ('PERCENT_AMOUNT', budget.percent_amt, FALSE);
1157 fnd_message.set_token ('BASE_LINE', budget.base_line, FALSE);
1158 fnd_message.set_token ('DATE', l_today_date, FALSE);
1159
1160 l_message := fnd_message.get;
1161
1162 OZF_Utility_PVT.create_log(l_return_status,
1163 'FTHO',
1164 budget.threshold_rule_id,
1165 l_message,
1166 1,
1167 'GENERAL',
1168 'NOTIFY',
1169 budget.budget_id,
1170 budget.threshold_id,
1171 l_trans_id,
1172 SYSDATE
1173 );
1174
1175 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1176 RAISE FND_API.G_EXC_ERROR;
1177 END IF;
1178 END IF;
1179 END IF;
1180
1181 l_value_limit := 0;
1182 l_base_line_amt := 0;
1183 l_value_limit_type := '';
1184 l_operation_result := '';
1185 l_notification_result := '';
1186
1187 --######
1188 END;
1189 END LOOP;
1190 END;
1191 END LOOP;
1192 l_owner_table.delete;
1193
1194 --Create owner_message table.
1195 FOR logs IN c_log_message(l_trans_id) LOOP
1196 OPEN c_owner(logs.budget_id);
1197 FETCH c_owner INTO l_owner_id,l_parent_fund_id;
1198 CLOSE c_owner;
1199
1200 OPEN c_parent_owner(l_parent_fund_id);
1201 FETCH c_parent_owner INTO l_parent_owner_id;
1202 CLOSE c_parent_owner;
1203
1204 l_owner_table(l_count).owner := l_owner_id;
1205 l_owner_table(l_count).parent_owner := NVL(l_parent_owner_id,0);
1206 l_owner_table(l_count).message_text := logs.log_message_text;
1207 l_owner_table(l_count).remove_flag := 'F';
1208
1209 l_count := l_count +1;
1210 END LOOP;
1211 --Combine message for same owner and parent owner and create notify_tabel.
1212 l_count := 1;
1213 IF l_owner_table.FIRST IS NOT NULL AND l_owner_table.LAST IS NOT NULL THEN
1214 FOR i IN NVL(l_owner_table.FIRST, 1) .. NVL(l_owner_table.LAST, 0) LOOP
1215 l_counter := 1;
1216
1217 IF l_owner_table(i).remove_flag = 'F' THEN
1218 --l_message := l_owner_table(i).message_text;
1219 l_message := l_owner_table(i).message_text|| fnd_global.local_chr(10);
1220 l_notify_table(l_count).owner := l_owner_table(i).owner;
1221 l_notify_table(l_count).parent_owner :=l_owner_table(i).parent_owner;
1222 l_parent_owner_id := l_owner_table(i).parent_owner;
1223 l_owner_table(i).remove_flag := 'T';
1224
1225 FOR j IN NVL(l_owner_table.FIRST, 1) .. NVL(l_owner_table.LAST, 0) LOOP
1226 IF j <> i AND l_owner_table(j).remove_flag = 'F' AND l_parent_owner_id = l_owner_table(j).parent_owner THEN
1227 --l_message := l_message || fnd_global.local_chr(10)|| l_owner_table(j).message_text || '. ' || fnd_global.local_chr(10);
1228 l_message := l_message || l_owner_table(j).message_text || fnd_global.local_chr(10);
1229 l_owner_table(j).remove_flag := 'T';
1230
1231 --restricting 15 messages to notification -bug 5390527
1232 l_counter := l_counter+1;
1233 IF l_counter = 15 THEN
1234 EXIT;
1235 END IF;
1236 END IF;
1237 END LOOP;
1238 l_notify_table(l_count).message_text := l_message;
1239 l_count := l_count + 1;
1240 END IF;
1241 EXIT WHEN l_owner_table.COUNT = 0;
1242 END LOOP;
1243 END IF;
1244
1245 IF l_notify_table.FIRST IS NOT NULL AND l_notify_table.LAST IS NOT NULL THEN
1246 --MAKE A CALL TO NOTIFICATION PROGRAM WHEN READY
1247 FOR i IN NVL(l_notify_table.FIRST, 0)..NVL(l_notify_table.LAST, 0) LOOP
1248
1249 OPEN c_log_seq;
1250 FETCH c_log_seq INTO l_log_id;
1251 CLOSE c_log_seq;
1252
1253 /* No need to store all the combined messages in log table -bug 5390527
1254 OZF_Utility_PVT.create_log(x_return_status =>l_return_status,
1255 p_arc_log_used_by =>'FTHO',
1256 p_log_used_by_id => l_notify_table(i).owner,
1257 p_msg_data =>l_notify_table(i).message_text,
1258 p_msg_level =>1,
1259 p_msg_type => 'COMBINED',
1260 p_desc =>'NOTIFY',
1261 --p_budget_id =>null,
1262 --p_threshold_id => null,
1263 --p_transaction_id => null,
1264 p_notification_creat_date => SYSDATE,
1265 p_activity_log_id => l_log_id
1266 );*/
1267 IF G_DEBUG THEN
1268 OZF_UTILITY_PVT.debug_message('Call workflow: ' || l_return_status );
1269 END IF;
1270
1271 start_process(l_api_version_number,
1272 l_Msg_Count,
1273 l_Msg_Data,
1274 l_return_status,
1275 l_notify_table(i).owner,
1276 l_notify_table(i).parent_owner,
1277 l_notify_table(i).message_text,
1278 l_log_id
1279 );
1280
1281 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1282 RAISE FND_API.G_EXC_ERROR;
1283 END IF;
1284 END LOOP;
1285 END IF;
1286
1287
1288 IF G_DEBUG THEN
1289 OZF_UTILITY_PVT.debug_message('PUBLIC API: ' || l_api_name || 'END');
1290 END IF;
1291 x_retcode := 0;
1292
1293 ozf_utility_pvt.write_conc_log(l_msg_data);
1294 OZF_UTILITY_PVT.debug_message( 'End of Budget Threshold ........ ');
1295 OZF_UTILITY_PVT.debug_message( 'Start of Quota Threshold ........ ');
1296
1297 FND_FILE.PUT_LINE(FND_FILE.LOG, 'End of Budget Threshold ........ ');
1298 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start of Quota Threshold ........ ');
1299 OZF_QUOTA_THRESHOLD_PVT.validate_quota_threshold(
1300 x_errbuf => l_errbuf,
1301 x_retcode => l_retcode);
1302 IF l_retcode <> 0 THEN
1303 x_errbuf := l_errbuf;
1304 x_retcode := l_retcode;
1305 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN Quota Threshold ........ l_errbuf :' || l_errbuf);
1306 RAISE FND_API.G_EXC_ERROR;
1307 END IF;
1308 COMMIT;
1309 FND_FILE.PUT_LINE(FND_FILE.LOG, 'End of Quota Threshold ........ ');
1310 OZF_UTILITY_PVT.debug_message( 'End of Quota Threshold ........ ');
1311 EXCEPTION
1312
1313 WHEN FND_API.G_EXC_ERROR THEN
1314 ROLLBACK TO VALIDATE_THRESHOLD_RULE_PVT;
1315 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception G_EXC_ERROR '||l_api_name);
1316 x_retcode := 1;
1317 x_errbuf := l_msg_data;
1318 ozf_utility_pvt.write_conc_log(x_errbuf);
1319
1320 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1321 ROLLBACK TO VALIDATE_THRESHOLD_RULE_PVT;
1322 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception G_EXC_UNEXPECTED_ERROR '||l_api_name);
1323 x_retcode := 1;
1324 x_errbuf := l_msg_data;
1325 ozf_utility_pvt.write_conc_log(x_errbuf);
1326
1327 WHEN OTHERS THEN
1328 ROLLBACK TO VALIDATE_THRESHOLD_RULE_PVT;
1329 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception OTHERS '||l_api_name);
1330 x_retcode := 1;
1331 x_errbuf := l_msg_data;
1332 ozf_utility_pvt.write_conc_log(x_errbuf);
1333 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error : ' || SQLCODE||SQLERRM);
1334 END validate_threshold;
1335
1336 END Ozf_Threshold_Pvt;
1337