[Home] [Help]
PACKAGE BODY: APPS.OZF_QUOTA_THRESHOLD_PVT
Source
1 PACKAGE BODY OZF_QUOTA_THRESHOLD_PVT AS
2 /* $Header: ozfvqtrb.pls 120.1 2006/05/11 03:43:03 inanaiah noship $*/
3
4 -- ===============================================================
5 -- Start of Comments
6 -- Package name
7 -- OZF_QUOTA_THRESHOLD_PVT
8 -- Purpose
9 --
10 -- History
11 -- Created By - Padmavathi Karthikeyan
12 -- Modified by kvattiku July 15, 04 Took care of the binding variable issue
13 -- inanaiah May 11, 06 Bug 5185832 fix - closed c_quota loop after
14 -- l_operation_result IF stmt
15
16 -- NOTE
17 -- Will prcess the quota thresholds and creates
18 -- notification information in ams_act_logs
19 -- table. Will make a call to notification
20 -- package. Will insert alert types for dashboard use.
21 -- End of Comments
22 -- ===============================================================
23
24 G_PKG_NAME CONSTANT VARCHAR2(30) :='OZF_QUOTA_THRESHOLD_PVT';
25 G_FILE_NAME CONSTANT VARCHAR2(20) :='ozfvqtrb.pls';
26 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
27 --G_DEBUG BOOLEAN := TRUE;
28 TYPE NUMBER_TBL_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
29
30 -----------------------------------------------------------------------
31 -- PROCEDURE
32 -- start_process
33 --
34 -- Sends notification to budget owner by calling the procedure
35 -- ozf_utility_pvt.send_wf_standalone_message
36
37 -----------------------------------------------------------------------
38 PROCEDURE start_process(
39 p_api_version_number IN NUMBER
40 ,x_msg_count OUT NOCOPY NUMBER
41 ,x_msg_data OUT NOCOPY VARCHAR2
42 ,x_return_status OUT NOCOPY VARCHAR2
43 ,p_owner_id IN NUMBER
44 ,p_parent_owner_id IN NUMBER
45 ,p_message_text IN VARCHAR2
46 ,p_activity_log_id IN NUMBER
47 )
48 IS
49 l_api_name CONSTANT VARCHAR2(30) := 'Start_Process';
50 l_return_status VARCHAR2(1);
51 l_strSubject VARCHAR2(30);
52 l_strChildSubject VARCHAR2(30);
53 l_notification_id NUMBER;
54 l_strBody VARCHAR2(2000);
55
56 BEGIN
57 IF G_DEBUG THEN
58 OZF_UTILITY_PVT.debug_message('Entering ams_threshold_notify.Start_process : ');
59 END IF;
60
61 -- Initialize API return status to SUCCESS
62 x_return_status := FND_API.G_RET_STS_SUCCESS;
63
64 fnd_message.set_name('OZF', 'OZF_THRESHOLD_SUBJECT');
65 l_strSubject := fnd_message.get;
66 fnd_message.set_name('OZF', 'OZF_THRESHOLD_CHILDSUBJ');
67 l_strChildSubject := fnd_message.get;
68
69 -- fnd_message.set_name('OZF', 'OZF_NOTIFY_HEADERLINE');
70 --l_strBody := fnd_message.get ||fnd_global.local_chr(10)||fnd_global.local_chr(10)||p_message_text;
71 l_strBody := p_message_text;
72 fnd_message.set_name('OZF', 'OZF_NOTIFY_FOOTER');
73 l_strBody := l_strBody || fnd_global.local_chr(10) || fnd_global.local_chr(10) ||fnd_message.get ;
74
75 ozf_utility_pvt.send_wf_standalone_message(
76 p_subject => l_strSubject
77 ,p_body => l_strBody
78 ,p_send_to_res_id => p_owner_id
79 ,x_notif_id => l_notification_id
80 ,x_return_status => l_return_status
81 );
82
83 IF l_return_status <> fnd_api.g_ret_sts_success THEN
84 RAISE fnd_api.g_exc_error;
85 END IF;
86
87
88 IF p_parent_owner_id <>0 THEN
89 ozf_utility_pvt.send_wf_standalone_message(
90 p_subject => l_strChildSubject
91 ,p_body => l_strBody
92 ,p_send_to_res_id => p_parent_owner_id
93 ,x_notif_id => l_notification_id
94 ,x_return_status => l_return_status
95 );
96 END IF;
97
98 IF l_return_status <> fnd_api.g_ret_sts_success THEN
99 RAISE fnd_api.g_exc_error;
100 END IF;
101
102 EXCEPTION
103
104 WHEN FND_API.G_EXC_ERROR THEN
105 x_return_status := FND_API.G_RET_STS_ERROR;
106 -- Standard call to get message count and if count=1, get the message
107 FND_MSG_PUB.Count_And_Get (
108 p_encoded => FND_API.G_FALSE,
109 p_count => x_msg_count,
110 p_data => x_msg_data
111 );
112
113 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
114 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
115 -- Standard call to get message count and if count=1, get the message
116 FND_MSG_PUB.Count_And_Get (
117 p_encoded => FND_API.G_FALSE,
118 p_count => x_msg_count,
119 p_data => x_msg_data
120 );
121
122 WHEN OTHERS THEN
123 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
124 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
125 THEN
126 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
127 END IF;
128 -- Standard call to get message count and if count=1, get the message
129 FND_MSG_PUB.Count_And_Get (
130 p_encoded => FND_API.G_FALSE,
131 p_count => x_msg_count,
132 p_data => x_msg_data
133 );
134 END start_process; /* START_PROCESS */
135
136 -----------------------------------------------------------------------
137 -- PROCEDURE
138 -- validate_quota_threshold
139 --
140 -- This is the main procedure called while executing concurrent program.
141 -- For all enabled quota threshold rules, it will check for violation
142 -- and sent notification accordingly.
143 -- It also set alert flages for dashboard use.
144
145 -----------------------------------------------------------------------
146 PROCEDURE validate_quota_threshold
147 (
148 x_errbuf OUT NOCOPY VARCHAR2,
149 x_retcode OUT NOCOPY NUMBER
150 )
151 IS
152 l_api_name CONSTANT VARCHAR2(30) := 'validate_quota_threshold';
153 l_api_version_number CONSTANT NUMBER := 1.0;
154 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
155 l_count NUMBER := 0;
156 l_value_limit NUMBER := 0;
157 l_base_line_amt NUMBER := 0;
158 l_value_limit_type VARCHAR2(15);
159 l_operation_result VARCHAR2(25);
160 l_operation_result_notify VARCHAR2(25);
161 l_notification_result VARCHAR2(25);
162 l_return_status VARCHAR2(2);
163 l_operator_meaning VARCHAR2(25);
164 l_budget_name VARCHAR2(240);
165 l_parent_fund_id NUMBER;
166 l_trans_id NUMBER;
167 l_log_id NUMBER;
168 l_owner_id NUMBER;
169 l_parent_owner_id NUMBER;
170 l_message VARCHAR2(5000);
171 l_period_meaning VARCHAR2(25);
172 l_msg_data VARCHAR2 (5000);
173 l_msg_count NUMBER;
174 l_valuelimit_name VARCHAR2(60);
175 l_baseline_name VARCHAR2(60);
176 l_today_date VARCHAR2(20);
177 l_quota NUMBER;
178 l_resource_list NUMBER_TBL_TYPE;
179
180 -- This cursor gets the threshold rules which are in active status
181
182 CURSOR c_threshold_rules_cur IS
183 SELECT r.threshold_rule_id,
184 r.threshold_id
185 FROM ozf_threshold_rules_all r, ozf_thresholds_all_b t
186 WHERE r.threshold_id = t.threshold_id
187 AND t.threshold_type = 'QUOTA'
188 AND r.enabled_flag = 'Y'
189 AND r.start_date <= SYSDATE
190 AND r.end_date >= SYSDATE ;
191
192
193 --This cursor will get all the enabled budgets which are tied with the Thresholds
194 CURSOR c_threshold_funds(p_threshold_rule_id NUMBER)
195 IS
196 SELECT a.fund_id budget_id,
197 a.parent_fund_id parent_budget_id,
198 a.owner owner,
199 c.value_limit value_limit,
200 c.operator_code operator_code,
201 c.start_date rule_start_date,
202 c.end_date rule_end_date,
203 c.threshold_id threshold_id,
204 c.threshold_rule_id threshold_rule_id,
205 c.percent_amount percent_amt,
206 c.base_line base_line,
207 c.frequency_period frequency_period,
208 c.repeat_frequency repeat_frequency,
209 c.comparison_type,
210 c.alert_type
211 FROM ozf_funds_all_b a,
212 ozf_thresholds_all_b b,
213 ozf_threshold_rules_all c
214 WHERE a.threshold_id = b.threshold_id
215 AND a.status_code = 'ACTIVE'
216 AND b.enable_flag = 'Y'
217 AND b.threshold_id = c.threshold_id
218 AND c.threshold_rule_id = p_threshold_rule_id
219 AND c.end_date >= SYSDATE;
220
221 --This cursor is to get all the resources for the enabled quota thresholds
222 CURSOR c_all_resources
223 IS
224 SELECT DISTINCT a.owner owner
225 FROM ozf_funds_all_b a,
226 ozf_thresholds_all_b b,
227 ozf_threshold_rules_all c
228 WHERE a.threshold_id = b.threshold_id
229 AND a.status_code = 'ACTIVE'
230 AND b.enable_flag = 'Y'
231 AND b.threshold_id = c.threshold_id
232 AND b.threshold_type = 'QUOTA'
233 AND c.enabled_flag = 'Y'
234 AND c.start_date <= SYSDATE
235 AND c.end_date >= SYSDATE;
236
237 --This cursor will get all product related infromation for the given budget
238 CURSOR c_product_facts (p_budget_id NUMBER)
239 IS
240 SELECT
241 p.item_id item_id,
242 p.item_type item_type,
243 sum(c.ptd_sales) mtd_sales,
244 sum(c.qtd_sales) qtd_sales,
245 sum(c.ytd_sales) ytd_sales,
246 sum(c.lysp_sales) lysp_sales,
247 sum(c.past_due_order_qty) outst_order,
248 sum(c.current_period_order_qty) current_order,
249 sum(c.backordered_qty) back_order,
250 sum(c.booked_for_future_qty) future_order,
251 sum(c.current_year_target) yearly_quota,
252 sum(c.current_period_target) monthly_quota,
253 sum(c.current_qtr_target) quarterly_quota,
254 sum(c.lysq_sales) lysq_sales,
255 sum(c.ly_sales) ly_sales
256 FROM ozf_product_allocations p, ozf_cust_daily_facts c
257 WHERE
258 p.item_type <> 'OTHERS' and
259 p.fund_id = p_budget_id and
260 p.item_id = c.product_attr_value and
261 p.item_type = c.product_attribute and
262 c.report_date = trunc(SYSDATE)
263 group by p.item_id, p.item_type;
264
265
266 --This cursor will get all customer related infromation for the given budget
267 CURSOR c_customer_facts (p_budget_id NUMBER)
268 IS
269 SELECT
270 c.cust_account_id cust_account_id,
271 c.ship_to_site_use_id ship_to_site_use_id,
272 sum(c.ptd_sales) mtd_sales,
273 sum(c.qtd_sales) qtd_sales,
274 sum(c.ytd_sales) ytd_sales,
275 sum(c.lysp_sales) lysp_sales,
276 sum(c.past_due_order_qty) outst_order,
277 sum(c.current_period_order_qty) current_order,
278 sum(c.backordered_qty) back_order,
279 sum(c.booked_for_future_qty) future_order,
280 sum(c.current_year_target) yearly_quota,
281 sum(c.current_period_target) monthly_quota,
282 sum(c.current_qtr_target) quarterly_quota,
283 sum(c.lysq_sales) lysq_sales,
284 sum(c.ly_sales) ly_sales
285 FROM ozf_account_allocations a, ozf_cust_daily_facts c
286 WHERE
287 a.allocation_for = 'FUND' and
288 a.allocation_for_id = p_budget_id and
289 a.site_use_code = 'SHIP_TO' and
290 a.cust_account_id = c.cust_account_id and
291 a.site_use_id = c.ship_to_site_use_id and
292 c.report_date = trunc(SYSDATE)
293 group by c.cust_account_id, c.ship_to_site_use_id;
294
295 --This cursor will get quota related information for the given resource id
296 CURSOR c_quota (p_resource_id NUMBER)
297 IS
298 SELECT resource_id, sequence_number, kpi_name, kpi_value
299 FROM ozf_dashb_daily_kpi
300 WHERE resource_id = p_resource_id
301 AND report_date = trunc(SYSDATE)
302 ORDER BY sequence_number;
303
304 CURSOR c_log_seq IS
305 SELECT ams_act_logs_s.NEXTVAL
306 FROM DUAL;
307
308 CURSOR c_trans_seq IS
309 SELECT ams_act_logs_transaction_id_s.NEXTVAL
310 FROM DUAL;
311
312
313 CURSOR c_log_message (p_trans_id NUMBER)
314 IS
315 SELECT budget_id, log_message_text
316 FROM ams_act_logs
317 WHERE log_transaction_id = p_trans_id;
318
319 CURSOR c_owner(p_budget_id NUMBER)
320 IS
321 SELECT owner,parent_fund_id
322 FROM ozf_Funds_All_b
323 WHERE fund_id = p_budget_id;
324
325 CURSOR c_parent_owner(p_budget_id NUMBER)
326 IS
327 SELECT owner
328 FROM ozf_Funds_All_b
329 WHERE fund_id = p_budget_id;
330
331 CURSOR c_budget_name(p_budget_id NUMBER)
332 IS
333 SELECT short_name
334 FROM ozf_fund_details_v
335 WHERE fund_id = p_budget_id;
336
337 CURSOR c_valuelimit_name(p_lkup_code VARCHAR2)
338 IS
339 SELECT meaning
340 FROM ozf_lookups
341 WHERE lookup_type = 'OZF_QUOTA_VALUE_LIMIT'
342 AND lookup_code = p_lkup_code;
343
344 CURSOR c_baseline_name(p_lkup_code VARCHAR2)
345 IS
346 SELECT meaning
347 FROM ozf_lookups
348 WHERE lookup_type = 'OZF_QUOTA_BASE_LINE'
349 AND lookup_code = p_lkup_code;
350
351 TYPE owner_record_type IS RECORD
352 (owner NUMBER,
353 parent_owner NUMBER,
354 message_text VARCHAR2(5000),
355 remove_flag VARCHAR2(1));
356
357 l_owner_record owner_record_type;
358
359 TYPE owner_table_type IS TABLE OF owner_record_type
360 INDEX BY BINARY_INTEGER;
361 l_owner_table owner_table_type;
362 l_notify_table owner_table_type;
363 l_alert_str VARCHAR2(100);
364 l_alert_no SMALLINT;
365 l_quota_lysp_sales NUMBER;
366 l_quota_lysq_sales NUMBER;
367 l_quota_ly_sales NUMBER;
368
369
370 BEGIN
371
372 -- Standard Start of API savepoint
373 --SAVEPOINT validate_quota_threshold;
374
375 -- Debug Message
376 IF G_DEBUG THEN
377 OZF_UTILITY_PVT.debug_message('Private QUOTA API: ' || l_api_name || ' start');
378 END IF;
379 FND_FILE.PUT_LINE(FND_FILE.LOG,'Inside quota api ....... ' );
380 OPEN c_trans_seq;
381 FETCH c_trans_seq INTO l_trans_id;
382 CLOSE c_trans_seq;
383 -- Initialize API return status to SUCCESS
384 -- x_return_status := FND_API.G_RET_STS_SUCCESS;
385
386 OPEN c_all_resources;
387 FETCH c_all_resources BULK COLLECT INTO l_resource_list;
388 CLOSE c_all_resources;
389
390 IF l_resource_list.FIRST IS NOT NULL AND l_resource_list.LAST IS NOT NULL THEN
391
392 FORALL i in l_resource_list.FIRST .. l_resource_list.LAST
393 UPDATE OZF_QUOTA_ALERTS SET mtd_alert = NULL, qtd_alert = NULL,
394 ytd_alert = NULL, back_order_alert = NULL, outstand_order_alert = NULL
395 WHERE report_date = trunc(sysdate) and resource_id = l_resource_list(i);
396
397 FORALL i in l_resource_list.FIRST .. l_resource_list.LAST
398 UPDATE OZF_DASHB_DAILY_KPI SET alert_type = NULL
399 WHERE report_date = trunc(sysdate) and resource_id = l_resource_list(i);
400 END IF;
401
402 FOR rule IN c_threshold_rules_cur
403 LOOP
404 FND_FILE.PUT_LINE(FND_FILE.LOG,'Inside rules loop rule id : ' || rule.threshold_rule_id);
405 FOR budget IN c_threshold_funds(rule.threshold_rule_id)
406 LOOP
407 FND_FILE.PUT_LINE(FND_FILE.LOG,'Inside budget loop budget id :' || budget.budget_id);
408 l_operation_result_notify := '';
409 l_operator_meaning := '';
410 l_notification_result := '';
411 l_alert_no := 0;
412
413 l_quota_lysp_sales := 0;
414 l_quota_lysq_sales := 0;
415 l_quota_ly_sales := 0;
416
417 FOR product IN c_product_facts(budget.budget_id)
418 LOOP
419 FND_FILE.PUT_LINE(FND_FILE.LOG,'Inside Product loop :: Product :' || product.item_id );
420 l_operation_result := '';
421 l_alert_str := '';
422 IF budget.comparison_type = 'PERCENT' then
423 IF budget.base_line = 'MONTHLY_QUOTA' THEN
424 l_base_line_amt := (product.monthly_quota * budget.percent_amt / 100);
425 ELSIF budget.base_line = 'QUARTERLY_QUOTA' THEN
426 l_base_line_amt := (product.quarterly_quota* budget.percent_amt / 100);
427 ELSIF budget.base_line = 'YEARLY_QUOTA' THEN
428 l_base_line_amt := (product.yearly_quota* budget.percent_amt / 100);
429 END IF;
430 ELSE
431 l_base_line_amt := budget.percent_amt;
432 END IF;
433
434 IF budget.value_limit = 'MTD' THEN
435 l_value_limit := product.mtd_sales;
436 l_alert_str := 'mtd_alert';
437 IF budget.comparison_type = 'PERCENT' then
438 IF budget.base_line = 'LYSP_SALES' THEN
439 l_base_line_amt := (product.lysp_sales* budget.percent_amt / 100);
440 END IF;
441 END IF;
442 ELSIF budget.value_limit = 'QTD' THEN
443 l_value_limit := product.qtd_sales;
444 l_alert_str := 'qtd_alert';
445 IF budget.comparison_type = 'PERCENT' then
446 IF budget.base_line = 'LYSP_SALES' THEN
447 l_base_line_amt := (product.lysq_sales* budget.percent_amt / 100);
448 END IF;
449 END IF;
450 ELSIF budget.value_limit = 'YTD' THEN
451 l_value_limit := product.ytd_sales;
452 l_alert_str := 'ytd_alert';
453 IF budget.comparison_type = 'PERCENT' then
454 IF budget.base_line = 'LYSP_SALES' THEN
455 l_base_line_amt := (product.ly_sales* budget.percent_amt / 100);
456 END IF;
457 END IF;
458 /*ELSIF budget.value_limit = 'CURRENT_ORDERS' THEN
459 l_value_limit := product.current_order;
460 l_alert_str := 'current_order_alert';
461 ELSIF budget.value_limit = 'FUTURE_ORDERS' THEN
462 l_value_limit := product.future_order;
463 l_alert_str := 'future_order_alertt'; */
464 ELSIF budget.value_limit = 'BACK_ORDERS' THEN
465 l_value_limit := product.back_order;
466 l_alert_str := 'back_order_alert';
467 ELSIF budget.value_limit = 'OUTSTANDING_ORDERS' THEN
468 l_value_limit := product.outst_order;
469 l_alert_str := 'outstand_order_alert';
470 /*ELSIF budget.value_limit = 'TOTAL_SHIPMENTS' THEN
471 l_value_limit := product.mtd_sales + product.outst_order + product.back_order + product.current_order;
472 l_alert_str := 'total_ship_alert'; */
473 END IF;
474
475 FND_FILE.PUT_LINE(FND_FILE.LOG,'Inside Product loop - l_value_limit :' || l_value_limit );
476 FND_FILE.PUT_LINE(FND_FILE.LOG,'Inside Product loop - l_base_line_amt :' || l_base_line_amt );
477 --l_base_line_amt is rhs for operation_result imput.
478 operation_result(l_api_version_number,
479 FND_API.G_FALSE,
480 l_Msg_Count,
481 l_Msg_Data,
482 l_return_status,
483 l_value_limit,
484 l_base_line_amt,
485 budget.operator_code,
486 l_operation_result);
487 IF G_DEBUG THEN
488 OZF_UTILITY_PVT.debug_message('l_operation_result: ' || l_operation_result);
489 END IF;
490 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_operation_result :' || l_operation_result );
491
492 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
493 RAISE FND_API.G_EXC_ERROR;
494 END IF;
495
496 IF l_operation_result = 'VIOLATED' THEN
497 update_alerts(l_api_version_number,
498 FND_API.G_FALSE,
499 l_Msg_Count,
500 l_Msg_Data,
501 l_return_status,
502 budget.owner,
503 'PROD',
504 product.item_type,
505 product.item_id,
506 budget.alert_type,
507 l_alert_str,
508 0);
509 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
510 RAISE FND_API.G_EXC_ERROR;
511 END IF;
512 END IF;
513
514 END LOOP;
515
516 l_quota_lysp_sales := 0;
517 l_quota_lysq_sales := 0;
518 l_quota_ly_sales := 0;
519 FOR customer IN c_customer_facts(budget.budget_id)
520 LOOP
521 l_quota_lysp_sales := l_quota_lysp_sales + customer.lysp_sales;
522 l_quota_lysq_sales := l_quota_lysq_sales + customer.lysq_sales;
523 l_quota_ly_sales := l_quota_ly_sales + customer.ly_sales;
524 FND_FILE.PUT_LINE(FND_FILE.LOG,'Inside customer loop :: Customer :' || customer.cust_account_id );
525 l_operation_result := '';
526 l_alert_str := '';
527 IF budget.comparison_type = 'PERCENT' then
528 IF budget.base_line = 'MONTHLY_QUOTA' THEN
529 l_base_line_amt := (customer.monthly_quota * budget.percent_amt / 100);
530 ELSIF budget.base_line = 'QUARTERLY_QUOTA' THEN
531 l_base_line_amt := (customer.quarterly_quota* budget.percent_amt / 100);
532 ELSIF budget.base_line = 'YEARLY_QUOTA' THEN
533 l_base_line_amt := (customer.yearly_quota* budget.percent_amt / 100);
534 END IF;
535 ELSE
536 l_base_line_amt := budget.percent_amt;
537 END IF;
538
539 IF budget.value_limit = 'MTD' THEN
540 l_value_limit := customer.mtd_sales;
541 l_alert_str := 'mtd_alert';
542 IF budget.comparison_type = 'PERCENT' then
543 IF budget.base_line = 'LYSP_SALES' THEN
544 l_base_line_amt := (customer.lysp_sales* budget.percent_amt / 100);
545 END IF;
546 END IF;
547 ELSIF budget.value_limit = 'QTD' THEN
548 l_value_limit := customer.qtd_sales;
549 l_alert_str := 'qtd_alert';
550 IF budget.comparison_type = 'PERCENT' then
551 IF budget.base_line = 'LYSP_SALES' THEN
552 l_base_line_amt := (customer.lysq_sales* budget.percent_amt / 100);
553 END IF;
554 END IF;
555 ELSIF budget.value_limit = 'YTD' THEN
556 l_value_limit := customer.ytd_sales;
557 l_alert_str := 'ytd_alert';
558 IF budget.comparison_type = 'PERCENT' then
559 IF budget.base_line = 'LYSP_SALES' THEN
560 l_base_line_amt := (customer.ly_sales* budget.percent_amt / 100);
561 END IF;
562 END IF;
563 /*ELSIF budget.value_limit = 'CURRENT_ORDERS' THEN
564 l_value_limit := customer.current_order;
565 l_alert_str := 'current_order_alert';
566 ELSIF budget.value_limit = 'FUTURE_ORDERS' THEN
567 l_value_limit := customer.future_order;
568 l_alert_str := 'future_order_alert'; */
569 ELSIF budget.value_limit = 'BACK_ORDERS' THEN
570 l_value_limit := customer.back_order;
571 l_alert_str := 'back_order_alert';
572 ELSIF budget.value_limit = 'OUTSTANDING_ORDERS' THEN
573 l_value_limit := customer.outst_order;
574 l_alert_str := 'outstand_order_alert';
575 /*ELSIF budget.value_limit = 'TOTAL_SHIPMENTS' THEN
576 l_value_limit := customer.mtd_sales + customer.outst_order + customer.back_order + customer.current_order;
577 l_alert_str := 'total_ship_alert'; */
578 END IF;
579 FND_FILE.PUT_LINE(FND_FILE.LOG,'Customer - l_value_limit :' || l_value_limit );
580 FND_FILE.PUT_LINE(FND_FILE.LOG,'Customer - l_base_line_amt :' || l_base_line_amt );
581
582 --l_base_line_amt is rhs for operation_result imput.
583 operation_result(l_api_version_number,
584 FND_API.G_FALSE,
585 l_Msg_Count,
586 l_Msg_Data,
587 l_return_status,
588 l_value_limit,
589 l_base_line_amt,
590 budget.operator_code,
591 l_operation_result);
592 IF G_DEBUG THEN
593 OZF_UTILITY_PVT.debug_message('Operator: ' || l_operation_result);
594 END IF;
595 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_operation_result :' || l_operation_result );
596
597 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
598 RAISE FND_API.G_EXC_ERROR;
599 END IF;
600
601 IF l_operation_result = 'VIOLATED' THEN
602 update_alerts(l_api_version_number,
603 FND_API.G_FALSE,
604 l_Msg_Count,
605 l_Msg_Data,
606 l_return_status,
607 budget.owner,
608 'CUST',
609 NULL,
610 customer.ship_to_site_use_id,
611 budget.alert_type,
612 l_alert_str,
613 customer.cust_account_id);
614 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
615 RAISE FND_API.G_EXC_ERROR;
616 END IF;
617 END IF;
618
619 END LOOP;
620 FND_FILE.PUT_LINE(FND_FILE.LOG,' l_quota_lysp_sales :' || l_quota_lysp_sales );
621 FND_FILE.PUT_LINE(FND_FILE.LOG,' l_quota_lysq_sales :' || l_quota_lysq_sales );
622 FND_FILE.PUT_LINE(FND_FILE.LOG,' l_quota_ly_sales :' || l_quota_ly_sales );
623
624 IF budget.value_limit = 'MTD' OR budget.value_limit = 'QTD'
625 OR budget.value_limit = 'YTD' THEN
626 l_quota := 0;
627 FOR quota IN c_quota(budget.owner)
628 LOOP
629 FND_FILE.PUT_LINE(FND_FILE.LOG,'Inside quota loop ' );
630 l_operation_result := '';
631 IF budget.comparison_type = 'PERCENT' then
632 IF budget.base_line = 'MONTHLY_QUOTA' THEN
633 IF quota.sequence_number = 1 THEN
634 l_quota := quota.kpi_value;
635 l_base_line_amt := (l_quota * budget.percent_amt) / 100;
636 END IF;
637 /*IF quota.sequence_number = 2 THEN
638 --l_base_line_amt := (quota.kpi_value * 100 / l_quota)*(budget.percent_amt/100);
639 l_base_line_amt := (quota.kpi_value / l_quota)*budget.percent_amt;
640 END IF;*/
641 ELSIF budget.base_line = 'QUARTERLY_QUOTA' THEN
642 IF quota.sequence_number = 4 THEN
643 l_quota := quota.kpi_value;
644 l_base_line_amt := (l_quota * budget.percent_amt) / 100;
645 END IF;
646 /*IF quota.sequence_number = 5 THEN
647 l_base_line_amt := (quota.kpi_value / l_quota)*budget.percent_amt;
648 END IF;*/
649 ELSIF budget.base_line = 'YEARLY_QUOTA' THEN
650 IF quota.sequence_number = 7 THEN
651 l_quota := quota.kpi_value;
652 l_base_line_amt := (l_quota * budget.percent_amt) / 100;
653 END IF;
654 /*IF quota.sequence_number = 8 THEN
655 l_base_line_amt := (quota.kpi_value / l_quota)*budget.percent_amt;
656 END IF;*/
657 END IF;
658 ELSE
659 l_base_line_amt := budget.percent_amt;
660 END IF;
661
662 IF budget.value_limit = 'MTD' THEN
663 l_alert_no := 3;
664 IF quota.sequence_number = 2 THEN
665 l_value_limit := quota.kpi_value;
666 END IF;
667 IF budget.comparison_type = 'PERCENT' then
668 IF budget.base_line = 'LYSP_SALES' THEN
669 l_base_line_amt := (l_quota_lysp_sales* budget.percent_amt / 100);
670 END IF;
671 END IF;
672 ELSIF budget.value_limit = 'QTD' THEN
673 l_alert_no := 6;
674 IF quota.sequence_number = 5 THEN
675 l_value_limit := quota.kpi_value;
676 END IF;
677
678 IF budget.comparison_type = 'PERCENT' then
679 IF budget.base_line = 'LYSP_SALES' THEN
680 l_base_line_amt := (l_quota_lysq_sales* budget.percent_amt / 100);
681 END IF;
682 END IF;
683
684 ELSIF budget.value_limit = 'YTD' THEN
685 l_alert_no := 9;
686 IF quota.sequence_number = 8 THEN
687 l_value_limit := quota.kpi_value;
688 END IF;
689
690 IF budget.comparison_type = 'PERCENT' then
691 IF budget.base_line = 'LYSP_SALES' THEN
692 l_base_line_amt := (l_quota_ly_sales * budget.percent_amt / 100);
693 END IF;
694 END IF;
695
696 END IF;
697
698
699 FND_FILE.PUT_LINE(FND_FILE.LOG,'Quota - l_value_limit :' || l_value_limit );
700 FND_FILE.PUT_LINE(FND_FILE.LOG,'Quota - l_base_line_amt :' || l_base_line_amt );
701
702 --l_base_line_amt is rhs for operation_result imput.
703 operation_result(l_api_version_number,
704 FND_API.G_FALSE,
705 l_Msg_Count,
706 l_Msg_Data,
707 l_return_status,
708 l_value_limit,
709 l_base_line_amt,
710 budget.operator_code,
711 l_operation_result);
712 IF G_DEBUG THEN
713 OZF_UTILITY_PVT.debug_message('Operator: ' || l_operation_result);
714 END IF;
715 FND_FILE.PUT_LINE(FND_FILE.LOG,'budget.operator_code :' || budget.operator_code );
716 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_operation_result :' || l_operation_result );
717
718 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
719 RAISE FND_API.G_EXC_ERROR;
720 END IF;
721
722 IF l_operation_result = 'VIOLATED' THEN
723 update_alerts(l_api_version_number,
724 FND_API.G_FALSE,
725 l_Msg_Count,
726 l_Msg_Data,
727 l_return_status,
728 budget.owner,
729 'QUOTA',
730 NULL,
731 l_alert_no,
732 budget.alert_type,
733 NULL,
734 0);
735 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
736 RAISE FND_API.G_EXC_ERROR;
737 END IF;
738 l_operation_result_notify := 'VIOLATED';
739 OZF_UTILITY_PVT.debug_message(' budget.alert_type:' || budget.alert_type);
740 OZF_UTILITY_PVT.debug_message(' l_alert_no:' ||l_alert_no );
741 FND_FILE.PUT_LINE(FND_FILE.LOG,' budget.alert_type:' || budget.alert_type);
742 FND_FILE.PUT_LINE(FND_FILE.LOG,' l_alert_no:' ||l_alert_no );
743 END IF;
744 END LOOP;
745 END IF;
746
747 /* l_opeartion_result is deciding factor in calling verify_notification.
748 if l_opearation_result is 'VIOLATED' then we will call verify_notification
749 else if the l_opearation_result is 'NOT VIOLATED' then we will not call verify_notification*/
750 FND_FILE.PUT_LINE(FND_FILE.LOG,' l_operation_result_notify : ' || l_operation_result_notify);
751 IF l_operation_result_notify = 'VIOLATED' THEN
752 verify_notification( l_api_version_number,
753 FND_API.G_FALSE,
754 l_Msg_Count,
755 l_Msg_Data,
756 l_return_status,
757 budget.budget_id,
758 budget.threshold_id,
759 budget.threshold_rule_id,
760 budget.frequency_period,
761 budget.repeat_frequency,
762 budget.rule_start_date,
763 l_notification_result);
764
765 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
766 RAISE FND_API.G_EXC_ERROR;
767 END IF;
768 FND_FILE.PUT_LINE(FND_FILE.LOG,' l_notification_result : ' || l_notification_result);
769 --l_notification_result will drive write_to_log
770 IF G_DEBUG THEN
771 OZF_UTILITY_PVT.debug_message('Notify result: ' || l_notification_result );
772 END IF;
773
774 --Get lookup meaning
775
776 l_period_meaning := ozf_utility_pvt.get_lookup_meaning('AMS_TRIGGER_FREQUENCY_TYPE'
777 ,budget.frequency_period);
778
779 IF l_notification_result = 'NOTIFY' THEN
780
781 --Get operator meaning.
782 IF budget.operator_code = '0' THEN
783 fnd_message.set_name ('OZF', 'OZF_THRESHOLD_LESS');
784 l_operator_meaning := fnd_message.get;
785 ELSIF budget.operator_code = '1' THEN
786 fnd_message.set_name ('OZF', 'OZF_THRESHOLD_EQUAL');
787 l_operator_meaning := fnd_message.get;
788 ELSE
789 fnd_message.set_name ('OZF', 'OZF_THRESHOLD_LARGER');
790 l_operator_meaning := fnd_message.get;
791 END IF;
792
793 OPEN c_budget_name(budget.budget_id);
794 FETCH c_budget_name INTO l_budget_name;
795 CLOSE c_budget_name;
796
797 OPEN c_valuelimit_name(budget.value_limit);
798 FETCH c_valuelimit_name INTO l_valuelimit_name;
799 CLOSE c_valuelimit_name;
800
801 select to_char(sysdate, 'dd-Mon-yyyy' ) into l_today_date from dual;
802
803 IF budget.comparison_type = 'PERCENT' then
804 OPEN c_baseline_name(budget.base_line);
805 FETCH c_baseline_name INTO l_baseline_name;
806 CLOSE c_baseline_name;
807 fnd_message.set_name ('OZF', 'OZF_WF_NTF_QUOTA_THRESHOLD_FYI');
808 fnd_message.set_token ('BUDGET_NAME', l_budget_name, FALSE);
809 fnd_message.set_token ('VALUE_LIMIT', l_valuelimit_name, FALSE);
810 fnd_message.set_token ('OPERATOR', l_operator_meaning, FALSE);
811 fnd_message.set_token ('PERCENT_AMOUNT', budget.percent_amt, FALSE);
812 fnd_message.set_token ('BASE_LINE', l_baseline_name, FALSE);
813 fnd_message.set_token ('DATE', l_today_date, FALSE);
814 l_message := fnd_message.get;
815 ELSE
816 fnd_message.set_name ('OZF', 'OZF_WF_NTF_QUOTA_THRS_CONS_FYI');
817 fnd_message.set_token ('BUDGET_NAME', l_budget_name, FALSE);
818 fnd_message.set_token ('VALUE_LIMIT', l_valuelimit_name, FALSE);
819 fnd_message.set_token ('OPERATOR', l_operator_meaning, FALSE);
820 fnd_message.set_token ('PERCENT_AMOUNT', budget.percent_amt, FALSE);
821 fnd_message.set_token ('DATE', l_today_date, FALSE);
822 l_message := fnd_message.get;
823 END IF;
824
825
826 OZF_Utility_PVT.create_log(l_return_status,
827 'FTHO',
828 budget.threshold_rule_id,
829 l_message,
830 1,
831 'GENERAL',
832 'NOTIFY',
833 budget.budget_id,
834 budget.threshold_id,
835 l_trans_id,
836 SYSDATE
837 );
838
839 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
840 RAISE FND_API.G_EXC_ERROR;
841 END IF;
842 END IF; -- Notification result end if
843 END IF; -- operation result end if
844
845 l_value_limit := 0;
846 l_base_line_amt := 0;
847 l_value_limit_type := '';
848 l_operation_result := '';
849 l_notification_result := '';
850 END LOOP;
851 END LOOP;
852 l_owner_table.delete;
853
854 --Create owner_message table.
855 FOR logs IN c_log_message(l_trans_id) LOOP
856 OPEN c_owner(logs.budget_id);
857 FETCH c_owner INTO l_owner_id,l_parent_fund_id;
858 CLOSE c_owner;
859
860 OPEN c_parent_owner(l_parent_fund_id);
861 FETCH c_parent_owner INTO l_parent_owner_id;
862 CLOSE c_parent_owner;
863
864 l_owner_table(l_count).owner := l_owner_id;
865 l_owner_table(l_count).parent_owner := NVL(l_parent_owner_id,0);
866 l_owner_table(l_count).message_text := logs.log_message_text;
867 l_owner_table(l_count).remove_flag := 'F';
868
869 l_count := l_count +1;
870 END LOOP;
871
872 --Combine message for same owner and parent owner and create notify_tabel.
873 l_count := 1;
874 IF l_owner_table.FIRST IS NOT NULL AND l_owner_table.LAST IS NOT NULL THEN
875 FOR i IN NVL(l_owner_table.FIRST, 1) .. NVL(l_owner_table.LAST, 0) LOOP
876 IF l_owner_table(i).remove_flag = 'F' THEN
877 l_message := l_owner_table(i).message_text;
878 l_notify_table(l_count).owner := l_owner_table(i).owner;
879 l_notify_table(l_count).parent_owner :=l_owner_table(i).parent_owner;
880 l_parent_owner_id := l_owner_table(i).parent_owner;
881 FOR j IN NVL(l_owner_table.FIRST, 1) .. NVL(l_owner_table.LAST, 0) LOOP
882 IF j <> i AND l_owner_table(j).remove_flag = 'F' AND l_parent_owner_id = l_owner_table(j).parent_owner THEN
883 l_message := l_message || fnd_global.local_chr(10)|| l_owner_table(j).message_text || '. ' || fnd_global.local_chr(10);
884 l_owner_table(j).remove_flag := 'T';
885 END IF;
886 END LOOP;
887 l_notify_table(l_count).message_text := l_message;
888 l_count := l_count + 1;
889 END IF;
890 EXIT WHEN l_owner_table.COUNT = 0;
891 END LOOP;
892 END IF;
893
894 IF l_notify_table.FIRST IS NOT NULL AND l_notify_table.LAST IS NOT NULL THEN
895 --MAKE A CALL TO NOTIFICATION PROGRAM WHEN READY
896 FOR i IN NVL(l_notify_table.FIRST, 0)..NVL(l_notify_table.LAST, 0) LOOP
897
898 OPEN c_log_seq;
899 FETCH c_log_seq INTO l_log_id;
900 CLOSE c_log_seq;
901
902 OZF_Utility_PVT.create_log(x_return_status =>l_return_status,
903 p_arc_log_used_by =>'FTHO',
904 p_log_used_by_id => l_notify_table(i).owner,
905 p_msg_data =>l_notify_table(i).message_text,
906 p_msg_level =>1,
907 p_msg_type => 'COMBINED',
908 p_desc =>'NOTIFY',
909 --p_budget_id =>null,
910 --p_threshold_id => null,
911 --p_transaction_id => null,
912 p_notification_creat_date => SYSDATE,
913 p_activity_log_id => l_log_id
914 );
915 IF G_DEBUG THEN
916 OZF_UTILITY_PVT.debug_message('Call workflow: ' || l_return_status );
917 END IF;
918
919 start_process(l_api_version_number,
920 l_Msg_Count,
921 l_Msg_Data,
922 l_return_status,
923 l_notify_table(i).owner,
924 l_notify_table(i).parent_owner,
925 l_notify_table(i).message_text,
926 l_log_id
927 );
928
929 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
930 RAISE FND_API.G_EXC_ERROR;
931 END IF;
932
933 END LOOP;
934 END IF;
935
936 COMMIT;
937
938 IF G_DEBUG THEN
939 OZF_UTILITY_PVT.debug_message('PUBLIC QUOTA API: ' || l_api_name || 'END');
940 END IF;
941 x_retcode := 0;
942
943 ozf_utility_pvt.write_conc_log(l_msg_data);
944
945 FND_FILE.PUT_LINE(FND_FILE.LOG,'End of quota validation : ' );
946 -- RAISE FND_API.G_EXC_ERROR;
947 EXCEPTION
948
949 WHEN FND_API.G_EXC_ERROR THEN
950 --ROLLBACK TO validate_quota_threshold;
951 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception G_EXC_ERROR '||l_api_name);
952 x_retcode := 1;
953 x_errbuf := substr(l_msg_data,1,1000);
954 ozf_utility_pvt.write_conc_log(l_msg_data);
955
956 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
957 --ROLLBACK TO validate_quota_threshold;
958 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception G_EXC_UNEXPECTED_ERROR '||l_api_name);
959 x_retcode := 1;
960 x_errbuf := substr(l_msg_data,1,1000);
961 ozf_utility_pvt.write_conc_log(l_msg_data);
962
963 WHEN OTHERS THEN
964 --ROLLBACK TO validate_quota_threshold;
965 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception OTHERS '||l_api_name);
966 x_retcode := 1;
967 x_errbuf := substr(l_msg_data,1,1000);
968 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error : ' || SQLCODE||SQLERRM);
969 ozf_utility_pvt.write_conc_log(l_msg_data);
970
971 END validate_quota_threshold;
972
973 -----------------------------------------------------------------------
974 -- PROCEDURE
975 -- operation_result
976 --
977 -- It compares value limit amout and base line limit amount
978 -- to decide validate status.
979
980 -----------------------------------------------------------------------
981 PROCEDURE operation_result(
982 p_api_version_number IN NUMBER,
983 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
984 X_Msg_Count OUT NOCOPY NUMBER,
985 X_Msg_Data OUT NOCOPY VARCHAR2,
986 x_return_status OUT NOCOPY VARCHAR2,
987 p_lhs IN NUMBER,
988 p_rhs IN NUMBER,
989 p_operator_code IN VARCHAR2,
990 x_result OUT NOCOPY VARCHAR2)
991 IS
992 l_api_version_number CONSTANT NUMBER := 1.0;
993 l_api_name CONSTANT VARCHAR2(30) := 'operation_result';
994
995
996 BEGIN
997 -- Standard call to check for call compatibility.
998 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
999 p_api_version_number,
1000 l_api_name,
1001 G_PKG_NAME)
1002 THEN
1003 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1004 END IF;
1005
1006 -- Initialize message list if p_init_msg_list is set to TRUE.
1007 IF FND_API.to_Boolean( p_init_msg_list )
1008 THEN
1009 FND_MSG_PUB.initialize;
1010 END IF;
1011
1012 -- Debug Message
1013 --OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1014
1015 -- Initialize API return status to SUCCESS
1016 x_return_status := FND_API.G_RET_STS_SUCCESS;
1017
1018 -- Derive the result based on operator_code
1019
1020 IF p_operator_code = '2' THEN
1021 IF p_lhs >= p_rhs THEN
1022 x_result := 'VIOLATED';
1023 ELSE
1024 x_result := 'NOT VIOLATED';
1025 END IF;
1026
1027
1028 ELSIF p_operator_code = '0' THEN
1029 IF p_lhs <= p_rhs THEN
1030 x_result := 'VIOLATED';
1031 ELSE
1032 x_result := 'NOT VIOLATED';
1033 END IF;
1034
1035 ELSIF p_operator_code = '1' THEN
1036 IF p_lhs = p_rhs THEN
1037 x_result := 'VIOLATED';
1038 ELSE
1039 x_result := 'NOT VIOLATED';
1040 END IF;
1041
1042 END IF; -- for main IF/ELSIF
1043
1044 --OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1045
1046 -- Standard call to get message count and if count is 1, get message info.
1047 FND_MSG_PUB.Count_And_Get
1048 (p_count => x_msg_count,
1049 p_data => x_msg_data
1050 );
1051
1052 EXCEPTION
1053
1054 WHEN FND_API.G_EXC_ERROR THEN
1055 x_return_status := FND_API.G_RET_STS_ERROR;
1056 -- Standard call to get message count and if count=1, get the message
1057 FND_MSG_PUB.Count_And_Get (
1058 p_encoded => FND_API.G_FALSE,
1059 p_count => x_msg_count,
1060 p_data => x_msg_data
1061 );
1062
1063 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1064 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1065 -- Standard call to get message count and if count=1, get the message
1066 FND_MSG_PUB.Count_And_Get (
1067 p_encoded => FND_API.G_FALSE,
1068 p_count => x_msg_count,
1069 p_data => x_msg_data
1070 );
1071
1072 WHEN OTHERS THEN
1073 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1074 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1075 THEN
1076 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1077 END IF;
1078 -- Standard call to get message count and if count=1, get the message
1079 FND_MSG_PUB.Count_And_Get (
1080 p_encoded => FND_API.G_FALSE,
1081 p_count => x_msg_count,
1082 p_data => x_msg_data
1083 );
1084 END operation_result;
1085 -----------------------------------------------------------------------
1086 -- PROCEDURE
1087 -- verify_notification
1088 -- In Parozf
1089 -- p_api_version_number IN NUMBER
1090 -- p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1091 -- p_budget_id IN NUMBER -- budget to which the threshold applies
1092 -- p_threshold_id IN NUMBER -- threshold_id
1093 -- p_threshold_rule_id IN NUMBER -- threhold_rule_id
1094 -- p_frequency_period IN VARCHAR2 -- MONTHLY or DAILY
1095 -- p_repeat_frequency IN NUMBER
1096 -- It is a number . It signifies the frequency of resending the notifications
1097 -- p_rule_start_date IN DATE
1098 -- Standard Out params
1099 -- x_msg_count OUT NUMBER
1100 -- x_msg_data OUT VARCHAR2
1101 -- x_return_status OUT VARCHAR2
1102 -- x_result OUT VARCHAR2 -- NOTIFY OR NO_NOTIFY
1103
1104 -- Checks ams_act_logs table if there already is a notification sent to
1105 -- the budget owner or not for a threshold rule violation
1106
1107 -----------------------------------------------------------------------
1108 PROCEDURE verify_notification(
1109 p_api_version_number IN NUMBER,
1110 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1111 X_Msg_Count OUT NOCOPY NUMBER,
1112 X_Msg_Data OUT NOCOPY VARCHAR2,
1113 x_return_status OUT NOCOPY VARCHAR2,
1114 p_budget_id IN NUMBER,
1115 p_threshold_id IN NUMBER,
1116 p_threshold_rule_id IN NUMBER,
1117 p_frequency_period IN VARCHAR2,
1118 p_repeat_frequency IN NUMBER,
1119 p_rule_start_date IN DATE,
1120 x_result OUT NOCOPY VARCHAR2)
1121 IS
1122 l_api_version_number CONSTANT NUMBER := 1.0;
1123 l_api_name CONSTANT VARCHAR2(30) := 'verify_notification';
1124 l_count NUMBER := 0;
1125 l_notify_freq_days NUMBER := 0;
1126 l_notified_date DATE := SYSDATE;
1127
1128 CURSOR c_notification_exist(x_threshold_id NUMBER,
1129 x_threshold_rule_id NUMBER,
1130 x_budget_id NUMBER) IS
1131 SELECT Max(notification_creation_date)
1132 FROM AMS_ACT_LOGS
1133 WHERE arc_act_log_used_by = 'FTHO'
1134 AND act_log_used_by_id = x_threshold_rule_id
1135 AND budget_id = x_budget_id
1136 AND threshold_id = x_threshold_id;
1137
1138 BEGIN
1139 -- Standard call to check for call compatibility.
1140 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1141 p_api_version_number,
1142 l_api_name,
1143 G_PKG_NAME)
1144 THEN
1145 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1146 END IF;
1147
1148 -- Initialize message list if p_init_msg_list is set to TRUE.
1149 IF FND_API.to_Boolean( p_init_msg_list )
1150 THEN
1151 FND_MSG_PUB.initialize;
1152 END IF;
1153
1154 -- Debug Message
1155 --OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1156
1157
1158 -- Initialize API return status to SUCCESS
1159 x_return_status := FND_API.G_RET_STS_SUCCESS;
1160
1161 IF p_frequency_period = 'DAILY' THEN
1162 l_notify_freq_days := p_repeat_frequency;
1163 END IF;
1164
1165 IF p_frequency_period ='WEEKLY' THEN
1166 l_notify_freq_days := p_repeat_frequency*7;
1167
1168 END IF;
1169
1170 IF p_frequency_period ='MONTHLY' THEN
1171 l_notify_freq_days := p_repeat_frequency * 30;
1172 END IF;
1173
1174 IF p_frequency_period = 'QUARTERLY' THEN
1175 l_notify_freq_days := p_repeat_frequency * 30 * 3;
1176 END IF;
1177
1178 IF p_frequency_period = 'YEARLY' THEN
1179 l_notify_freq_days := p_repeat_frequency * 365;
1180 END IF;
1181
1182 -- checks entry in the ams_act_logs table for notification_purposes
1183 OPEN c_notification_exist(p_threshold_id,
1184 p_threshold_rule_id,
1185 p_budget_id);
1186 FETCH c_notification_exist INTO l_notified_date;
1187 CLOSE c_notification_exist;
1188
1189 -- In case of no notification recorder.
1190 IF l_notified_date is NULL THEN
1191 l_notified_date := p_rule_start_date;
1192 END IF;
1193
1194 IF SYSDATE - l_notified_date >= l_notify_freq_days THEN
1195 x_result := ('NOTIFY');
1196 ELSE
1197 x_result := ('NOT NOTIFY');
1198 END IF;
1199
1200 IF G_DEBUG THEN
1201 OZF_UTILITY_PVT.debug_message('Private API: Notified day' || l_notify_freq_days || ' end ' ||x_result );
1202 END IF;
1203
1204 -- Standard call to get message count and if count is 1, get message info.
1205 FND_MSG_PUB.Count_And_Get
1206 (p_count => x_msg_count,
1207 p_data => x_msg_data
1208 );
1209 EXCEPTION
1210
1211 WHEN FND_API.G_EXC_ERROR THEN
1212 x_return_status := FND_API.G_RET_STS_ERROR;
1213 -- Standard call to get message count and if count=1, get the message
1214 FND_MSG_PUB.Count_And_Get (
1215 p_encoded => FND_API.G_FALSE,
1216 p_count => x_msg_count,
1217 p_data => x_msg_data
1218 );
1219
1220 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1221 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1222 -- Standard call to get message count and if count=1, get the message
1223 FND_MSG_PUB.Count_And_Get (
1224 p_encoded => FND_API.G_FALSE,
1225 p_count => x_msg_count,
1226 p_data => x_msg_data
1227 );
1228
1229 WHEN OTHERS THEN
1230 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1231 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1232 THEN
1233 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1234 END IF;
1235 -- Standard call to get message count and if count=1, get the message
1236 FND_MSG_PUB.Count_And_Get (
1237 p_encoded => FND_API.G_FALSE,
1238 p_count => x_msg_count,
1239 p_data => x_msg_data
1240 );
1241 END verify_notification;
1242
1243 PROCEDURE update_alerts(
1244 p_api_version_number IN NUMBER,
1245 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1246 x_Msg_Count OUT NOCOPY NUMBER,
1247 x_Msg_Data OUT NOCOPY VARCHAR2,
1248 x_return_status OUT NOCOPY VARCHAR2,
1249 p_resource_id IN NUMBER,
1250 p_alert_for IN VARCHAR2,
1251 p_product_attribute IN VARCHAR2,
1252 p_attribute2 IN NUMBER, -- product_attr_value/ship_to_site_use_id/sequence_number
1253 p_alert_type IN VARCHAR2,
1254 p_select_attribute IN VARCHAR2,
1255 p_cust_account_id IN NUMBER
1256 )
1257 IS
1258 l_api_version_number CONSTANT NUMBER := 1.0;
1259 l_api_name CONSTANT VARCHAR2(30) := 'update_alerts';
1260 l_sql_stmt VARCHAR2(2000);
1261 l_sql_str VARCHAR2(2000);
1262 l_alert_type VARCHAR2(30);
1263 l_new_alert_type VARCHAR2(30);
1264 l_ins_csr NUMBER;
1265 l_ignore NUMBER;
1266 l_stmt VARCHAR2(2000) := NULL;
1267 l_quota_alert_id NUMBER;
1268 l_update BOOLEAN;
1269
1270 CURSOR c_quota_alert_seq IS
1271 SELECT ozf_quota_alerts_s.NEXTVAL
1272 FROM DUAL;
1273
1274 BEGIN
1275 -- Standard call to check for call compatibility.
1276 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1277 p_api_version_number,
1278 l_api_name,
1279 G_PKG_NAME)
1280 THEN
1281 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1282 END IF;
1283
1284 -- Initialize message list if p_init_msg_list is set to TRUE.
1285 IF FND_API.to_Boolean( p_init_msg_list )
1286 THEN
1287 FND_MSG_PUB.initialize;
1288 END IF;
1289
1290 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' start');
1291
1292 FND_FILE.PUT_LINE(FND_FILE.LOG,' p_resource_id: '|| p_resource_id);
1293 FND_FILE.PUT_LINE(FND_FILE.LOG,' p_alert_for: '|| p_alert_for);
1294 FND_FILE.PUT_LINE(FND_FILE.LOG,' p_product_attribute: '|| p_product_attribute);
1295 FND_FILE.PUT_LINE(FND_FILE.LOG,' p_attribute2: '|| p_attribute2);
1296 FND_FILE.PUT_LINE(FND_FILE.LOG,' p_alert_type: '|| p_alert_type);
1297 FND_FILE.PUT_LINE(FND_FILE.LOG,' p_select_attribute: '|| p_select_attribute);
1298 FND_FILE.PUT_LINE(FND_FILE.LOG,' p_cust_account_id: '|| p_cust_account_id);
1299
1300 -- Initialize API return status to SUCCESS
1301 x_return_status := FND_API.G_RET_STS_SUCCESS;
1302 l_sql_stmt := '';
1303 l_sql_str := '';
1304 l_ins_csr := 0;
1305 l_ignore := 0;
1306 l_stmt := '';
1307 l_new_alert_type := '';
1308 l_update := FALSE;
1309
1310 IF p_alert_for = 'PROD' THEN
1311 BEGIN
1312 /*l_sql_stmt := 'SELECT ' || p_select_attribute || ' FROM OZF_QUOTA_ALERTS '
1313 || 'WHERE report_date = trunc(SYSDATE) AND resource_id = ' || p_resource_id
1314 || 'AND alert_for = ''PROD'' AND product_attribute = ''' || p_product_attribute
1315 || ''' AND product_attr_value = ' || p_attribute2 */
1316 l_sql_stmt := 'SELECT ' || p_select_attribute || ' FROM OZF_QUOTA_ALERTS '
1317 || 'WHERE report_date = trunc(SYSDATE) AND resource_id = :1 '
1318 || 'AND alert_for = ''PROD'' AND product_attribute = :2 '
1319 || 'AND product_attr_value = :3';
1320 EXECUTE IMMEDIATE l_sql_stmt
1321 INTO l_alert_type
1322 USING p_resource_id, p_product_attribute, p_attribute2;
1323 IF ( l_alert_type IS NULL OR l_alert_type = FND_API.G_MISS_CHAR OR l_alert_type = '') THEN
1324 l_new_alert_type := p_alert_type;
1325 l_update := TRUE;
1326 ELSIF l_alert_type = 'ACCEPTABLE' THEN
1327 IF (p_alert_type = 'WARNING' OR p_alert_type = 'UNACCEPTABLE' )THEN
1328 l_new_alert_type := p_alert_type;
1329 l_update := TRUE;
1330 END IF;
1331 ELSIF l_alert_type = 'WARNING' THEN
1332 IF (p_alert_type = 'UNACCEPTABLE' )THEN
1333 l_new_alert_type := p_alert_type;
1334 l_update := TRUE;
1335 END IF;
1336 END IF;
1337
1338 IF l_update THEN
1339
1340 /*
1341 l_sql_str := 'UPDATE OZF_QUOTA_ALERTS SET ' || p_select_attribute;
1342 l_sql_str := l_sql_str || ' = ''' || l_new_alert_type ;
1343 l_sql_str := l_sql_str || ''' WHERE report_date = trunc(SYSDATE) AND resource_id = ' || p_resource_id;
1344 l_sql_str := l_sql_str || ' AND alert_for = ''PROD'' AND product_attribute = ''' || p_product_attribute;
1345 l_sql_str := l_sql_str || ''' AND product_attr_value = ' || p_attribute2;
1346
1347 FND_DSQL.init;
1348 FND_DSQL.add_text(l_sql_str);
1349 l_ins_csr := DBMS_SQL.open_cursor;
1350 FND_DSQL.set_cursor(l_ins_csr);
1351 l_stmt := FND_DSQL.get_text(FALSE);
1352
1353 FND_FILE.PUT_LINE(FND_FILE.LOG,'product UPD query: '|| l_stmt);
1354 DBMS_SQL.parse(l_ins_csr, l_stmt, DBMS_SQL.native);
1355 FND_DSQL.do_binds;
1356 l_ignore := DBMS_SQL.execute(l_ins_csr);
1357 */
1358
1359 --kvattiku updated
1360 l_sql_str := 'UPDATE OZF_QUOTA_ALERTS SET ' || p_select_attribute;
1361 l_sql_str := l_sql_str || ' = :1 ';
1362 l_sql_str := l_sql_str || ' WHERE report_date = trunc(SYSDATE) AND resource_id = :2';
1363 l_sql_str := l_sql_str || ' AND alert_for = ''PROD'' AND product_attribute = :3';
1364 l_sql_str := l_sql_str || ' AND product_attr_value = :4';
1365
1366 EXECUTE IMMEDIATE l_sql_str
1367 USING l_new_alert_type, p_resource_id, p_product_attribute, p_attribute2;
1368
1369 END IF;
1370 EXCEPTION
1371 WHEN NO_DATA_FOUND THEN
1372 OPEN c_quota_alert_seq;
1373 FETCH c_quota_alert_seq INTO l_quota_alert_id;
1374 CLOSE c_quota_alert_seq;
1375
1376 l_sql_str := 'INSERT INTO OZF_QUOTA_ALERTS ';
1377 l_sql_str := l_sql_str || '(quota_alert_id, report_date, resource_id, alert_for, ';
1378 l_sql_str := l_sql_str || 'product_attribute, product_attr_value, ' || p_select_attribute;
1379 l_sql_str := l_sql_str || ') VALUES (' || l_quota_alert_id || ',''' || TRUNC(SYSDATE) || ''',';
1380 l_sql_str := l_sql_str || p_resource_id || ','''||'PROD'',''' || p_product_attribute || ''',';
1381 l_sql_str := l_sql_str || p_attribute2 || ',''' || p_alert_type || ''')';
1382
1383 --FND_FILE.PUT_LINE(FND_FILE.LOG,'l_sql_str ::::::::::' || l_sql_str );
1384 FND_DSQL.init;
1385 FND_DSQL.add_text(l_sql_str);
1386 l_ins_csr := DBMS_SQL.open_cursor;
1387 FND_DSQL.set_cursor(l_ins_csr);
1388 l_stmt := FND_DSQL.get_text(FALSE);
1389
1390 FND_FILE.PUT_LINE(FND_FILE.LOG,'product INS query: '|| l_stmt);
1391 DBMS_SQL.parse(l_ins_csr, l_stmt, DBMS_SQL.native);
1392 FND_DSQL.do_binds;
1393 l_ignore := DBMS_SQL.execute(l_ins_csr);
1394
1395 WHEN OTHERS THEN
1396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1397 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1398 THEN
1399 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1400 END IF;
1401 -- Standard call to get message count and if count=1, get the message
1402 FND_MSG_PUB.Count_And_Get (
1403 p_encoded => FND_API.G_FALSE,
1404 p_count => x_msg_count,
1405 p_data => x_msg_data
1406 );
1407 FND_FILE.PUT_LINE(FND_FILE.LOG,'PROD EXCEPTION : '|| SUBSTR(SQLERRM, 1, 2000));
1408 END;
1409
1410 ELSIF p_alert_for = 'CUST' THEN
1411 BEGIN
1412 l_sql_stmt := 'SELECT ' || p_select_attribute || ' FROM OZF_QUOTA_ALERTS '
1413 || 'WHERE report_date = trunc(SYSDATE) AND resource_id = :1 '
1414 || 'AND alert_for = ''CUST'' AND cust_account_id = :2 '
1415 || 'AND ship_to_site_use_id = :3';
1416 EXECUTE IMMEDIATE l_sql_stmt
1417 INTO l_alert_type
1418 USING p_resource_id, p_cust_account_id, p_attribute2;
1419 IF ( l_alert_type IS NULL OR l_alert_type = FND_API.G_MISS_CHAR OR l_alert_type = '') THEN
1420 l_new_alert_type := p_alert_type;
1421 l_update := TRUE;
1422 ELSIF l_alert_type = 'ACCEPTABLE' THEN
1423 IF (p_alert_type = 'WARNING' OR p_alert_type = 'UNACCEPTABLE' )THEN
1424 l_new_alert_type := p_alert_type;
1425 l_update := TRUE;
1426 END IF;
1427 ELSIF l_alert_type = 'WARNING' THEN
1428 IF (p_alert_type = 'UNACCEPTABLE' )THEN
1429 l_new_alert_type := p_alert_type;
1430 l_update := TRUE;
1431 END IF;
1432 END IF;
1433
1434 IF l_update THEN
1435
1436 /* commented by kvattiku
1437 l_sql_str := 'UPDATE OZF_QUOTA_ALERTS SET ' || p_select_attribute;
1438 l_sql_str := l_sql_str || ' = ''' || l_new_alert_type ;
1439 l_sql_str := l_sql_str || ''' WHERE report_date = trunc(SYSDATE) AND resource_id = ' || p_resource_id;
1440 l_sql_str := l_sql_str || ' AND alert_for = ''CUST'' AND cust_account_id = ''' || p_cust_account_id;
1441 l_sql_str := l_sql_str || ''' AND ship_to_site_use_id = ' || p_attribute2;
1442
1443 FND_DSQL.init;
1444 FND_DSQL.add_text(l_sql_str);
1445 l_ins_csr := DBMS_SQL.open_cursor;
1446 FND_DSQL.set_cursor(l_ins_csr);
1447 l_stmt := FND_DSQL.get_text(FALSE);
1448
1449 FND_FILE.PUT_LINE(FND_FILE.LOG,'CUSTOMER UPD query: '|| l_stmt);
1450 DBMS_SQL.parse(l_ins_csr, l_stmt, DBMS_SQL.native);
1451 FND_DSQL.do_binds;
1452 l_ignore := DBMS_SQL.execute(l_ins_csr);
1453 */
1454
1455 --added by kvattiku
1456 l_sql_str := 'UPDATE OZF_QUOTA_ALERTS SET ' || p_select_attribute;
1457 l_sql_str := l_sql_str || ' = :1';
1458 l_sql_str := l_sql_str || ' WHERE report_date = trunc(SYSDATE) AND resource_id = :2';
1459 l_sql_str := l_sql_str || ' AND alert_for = ''CUST'' AND cust_account_id = :3';
1460 l_sql_str := l_sql_str || ' AND ship_to_site_use_id = :4';
1461
1462 EXECUTE IMMEDIATE l_sql_str
1463 USING l_new_alert_type, p_resource_id, p_cust_account_id, p_attribute2;
1464
1465
1466 END IF;
1467 EXCEPTION
1468 WHEN NO_DATA_FOUND THEN
1469 OPEN c_quota_alert_seq;
1470 FETCH c_quota_alert_seq INTO l_quota_alert_id;
1471 CLOSE c_quota_alert_seq;
1472
1473 l_sql_str := 'INSERT INTO OZF_QUOTA_ALERTS ';
1474 l_sql_str := l_sql_str || '(quota_alert_id, report_date, resource_id, alert_for, ';
1475 l_sql_str := l_sql_str || 'cust_account_id, ship_to_site_use_id, ' || p_select_attribute;
1476 l_sql_str := l_sql_str || ') VALUES (' || l_quota_alert_id || ',''' || TRUNC(SYSDATE) || ''',';
1477 l_sql_str := l_sql_str || p_resource_id || ','''||'CUST'',''' || p_cust_account_id || ''',';
1478 l_sql_str := l_sql_str || p_attribute2 || ',''' || p_alert_type || ''')';
1479
1480 --FND_FILE.PUT_LINE(FND_FILE.LOG,'l_sql_str ::::::::::' || l_sql_str );
1481 FND_DSQL.init;
1482 FND_DSQL.add_text(l_sql_str);
1483 l_ins_csr := DBMS_SQL.open_cursor;
1484 FND_DSQL.set_cursor(l_ins_csr);
1485 l_stmt := FND_DSQL.get_text(FALSE);
1486
1487 FND_FILE.PUT_LINE(FND_FILE.LOG,'CUSTOMER INS query: '|| l_stmt);
1488 DBMS_SQL.parse(l_ins_csr, l_stmt, DBMS_SQL.native);
1489 FND_DSQL.do_binds;
1490 l_ignore := DBMS_SQL.execute(l_ins_csr);
1491
1492 WHEN OTHERS THEN
1493 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1494 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1495 THEN
1496 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1497 END IF;
1498 -- Standard call to get message count and if count=1, get the message
1499 FND_MSG_PUB.Count_And_Get (
1500 p_encoded => FND_API.G_FALSE,
1501 p_count => x_msg_count,
1502 p_data => x_msg_data
1503 );
1504 FND_FILE.PUT_LINE(FND_FILE.LOG,'CUST EXCEPTION : '|| SUBSTR(SQLERRM, 1, 2000));
1505 END;
1506
1507 ELSIF p_alert_for = 'QUOTA' THEN
1508 BEGIN
1509 l_sql_stmt := 'SELECT alert_type FROM OZF_DASHB_DAILY_KPI '
1510 || 'WHERE report_date = trunc(SYSDATE) AND resource_id = :1 '
1511 || 'AND sequence_number = :2';
1512
1513 EXECUTE IMMEDIATE l_sql_stmt
1514 INTO l_alert_type
1515 USING p_resource_id, p_attribute2;
1516 IF ( l_alert_type IS NULL OR l_alert_type = FND_API.G_MISS_CHAR OR l_alert_type = '') THEN
1517 l_new_alert_type := p_alert_type;
1518 l_update := TRUE;
1519 ELSIF l_alert_type = 'ACCEPTABLE' THEN
1520 IF (p_alert_type = 'WARNING' OR p_alert_type = 'UNACCEPTABLE' )THEN
1521 l_new_alert_type := p_alert_type;
1522 l_update := TRUE;
1523 END IF;
1524 ELSIF l_alert_type = 'WARNING' THEN
1525 IF (p_alert_type = 'UNACCEPTABLE' )THEN
1526 l_new_alert_type := p_alert_type;
1527 l_update := TRUE;
1528 END IF;
1529 END IF;
1530
1531 IF l_update THEN
1532
1533 /* commented by kvattiku
1534 l_sql_str := 'UPDATE OZF_DASHB_DAILY_KPI SET alert_type = ''' || l_new_alert_type;
1535 l_sql_str := l_sql_str || ''' WHERE report_date = trunc(SYSDATE) AND resource_id = ' || p_resource_id;
1536 l_sql_str := l_sql_str || ' AND sequence_number = ' || p_attribute2;
1537
1538 FND_DSQL.init;
1539 FND_DSQL.add_text(l_sql_str);
1540 l_ins_csr := DBMS_SQL.open_cursor;
1541 FND_DSQL.set_cursor(l_ins_csr);
1542 l_stmt := FND_DSQL.get_text(FALSE);
1543
1544 FND_FILE.PUT_LINE(FND_FILE.LOG,'CUSTOMER UPD query: '|| l_stmt);
1545 DBMS_SQL.parse(l_ins_csr, l_stmt, DBMS_SQL.native);
1546 FND_DSQL.do_binds;
1547 l_ignore := DBMS_SQL.execute(l_ins_csr);
1548 */
1549
1550 --added by kvattiku
1551 l_sql_str := 'UPDATE OZF_DASHB_DAILY_KPI SET alert_type = :1';
1552 l_sql_str := l_sql_str || ' WHERE report_date = trunc(SYSDATE) AND resource_id = :2';
1553 l_sql_str := l_sql_str || ' AND sequence_number = :3';
1554
1555 EXECUTE IMMEDIATE l_sql_str
1556 USING l_new_alert_type, p_resource_id, p_attribute2;
1557
1558 END IF;
1559 EXCEPTION
1560 WHEN NO_DATA_FOUND THEN
1561 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1562 x_msg_count := 1;
1563 x_msg_data := SUBSTR(SQLERRM, 1, 2000);
1564 FND_FILE.PUT_LINE(FND_FILE.LOG,'QUOTA EXCEPTION : '|| SUBSTR(SQLERRM, 1, 2000));
1565
1566 WHEN OTHERS THEN
1567 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1568 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1569 THEN
1570 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1571 END IF;
1572 -- Standard call to get message count and if count=1, get the message
1573 FND_MSG_PUB.Count_And_Get (
1574 p_encoded => FND_API.G_FALSE,
1575 p_count => x_msg_count,
1576 p_data => x_msg_data
1577 );
1578 FND_FILE.PUT_LINE(FND_FILE.LOG,'QUOTA EXCEPTION : '|| SUBSTR(SQLERRM, 1, 2000));
1579 END;
1580 END IF;
1581
1582
1583 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1584
1585 -- Standard call to get message count and if count is 1, get message info.
1586 FND_MSG_PUB.Count_And_Get
1587 (p_count => x_msg_count,
1588 p_data => x_msg_data
1589 );
1590 EXCEPTION
1591 WHEN OTHERS THEN
1592 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1593 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1594 THEN
1595 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1596 END IF;
1597 -- Standard call to get message count and if count=1, get the message
1598 FND_MSG_PUB.Count_And_Get (
1599 p_encoded => FND_API.G_FALSE,
1600 p_count => x_msg_count,
1601 p_data => x_msg_data
1602 );
1603 FND_FILE.PUT_LINE(FND_FILE.LOG,'update_alerts EXCEPTION : '|| SUBSTR(SQLERRM, 1, 2000));
1604 END update_alerts;
1605
1606 END OZF_QUOTA_THRESHOLD_PVT;