[Home] [Help]
PACKAGE BODY: APPS.OZF_THRESHOLD_RULE_PVT
Source
1 PACKAGE BODY OZF_Threshold_Rule_PVT as
2 /* $Header: ozfvtrub.pls 120.2 2005/12/19 10:10:36 mkothari ship $ */
3 -- ===============================================================
4
5 -- Start of Comments
6 -- Package name
7 -- OZF_Threshold_Rule_PVT
8 -- Purpose
9 --
10 -- History
11 --
12 -- NOTE
13 --
14 -- End of Comments
15 -- ===============================================================
16
17
18 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_Threshold_Rule_PVT';
19 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvtrub.pls';
20 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
21
22 -----------------------------------------------------------------------
23 -- PROCEDURE
24 -- calculate_converted_day
25 --
26 -- HISTORY
27 -----------------------------------------------------------------------
28
29
30 PROCEDURE calculate_converted_day(
31 p_repeat_frequency IN NUMBER
32 ,p_frequency_period IN VARCHAR2
33 ,p_start_date IN DATE
34 ,p_end_date IN DATE
35 ,x_converted_days OUT NOCOPY NUMBER
36 ,x_return_status OUT NOCOPY VARCHAR2)
37 IS
38
39 BEGIN
40 x_return_status := fnd_api.g_ret_sts_success;
41
42 IF p_repeat_frequency IS NOT NULL
43 AND p_frequency_period IS NOT NULL THEN
44
45 IF p_frequency_period = 'WEEKLY' THEN
46 x_converted_days := 7 * p_repeat_frequency;
47 ELSIF p_frequency_period = 'MONTHLY' THEN
48 x_converted_days := 30 * p_repeat_frequency;
49 ELSIF p_frequency_period = 'QUARTERLY' THEN
50 x_converted_days := 90 * p_repeat_frequency;
51 ELSIF p_frequency_period = 'YEARLY' THEN
52 x_converted_days := 365 * p_repeat_frequency;
53 ELSE
54 x_converted_days := p_repeat_frequency;
55 END IF;
56
57 END IF;
58
59 END calculate_converted_day;
60 -----------------------------------------------------------------------
61 -- PROCEDURE
62 -- check_threshold_calendar
63 --
64 -- HISTORY
65
66 -----------------------------------------------------------------------
67 PROCEDURE check_threshold_calendar(
68 p_threshold_calendar IN VARCHAR2
69 ,p_start_period_name IN VARCHAR2
70 ,p_end_period_name IN VARCHAR2
71 ,p_start_date IN DATE
72 ,p_end_date IN DATE
73 ,p_threshold_id IN NUMBER
74 ,x_return_status OUT NOCOPY VARCHAR2)
75 IS
76 l_start_start DATE;
77 l_start_end DATE;
78 l_end_start DATE;
79 l_end_end DATE;
80 l_threshold_start_date DATE;
81 l_threshold_end_date DATE;
82 l_local NUMBER;
83
84
85 CURSOR c_threshold_date
86 IS
87 SELECT start_date_active, end_date_active
88 FROM ozf_thresholds_vl
89 WHERE threshold_id = p_threshold_id;
90
91 BEGIN
92 x_return_status := fnd_api.g_ret_sts_success;
93 -- compare the start date and the end date
94 IF p_start_date > p_end_date THEN
95 x_return_status := fnd_api.g_ret_sts_error;
96 ozf_utility_pvt.error_message('OZF_STARTDATE_OUT_ENDDATE');
97 RETURN;
98 END IF;
99
100 --compare the start date and the end date of both threshold and threshold_rule
101 IF p_threshold_id IS NOT NULL THEN
102 OPEN c_threshold_date;
103 FETCH c_threshold_date INTO l_threshold_start_date,l_threshold_end_date;
104 CLOSE c_threshold_date;
105
106 IF p_start_date < l_threshold_start_date
107 OR p_end_date > l_threshold_end_date THEN
108 x_return_status := fnd_api.g_ret_sts_error;
109 ozf_utility_pvt.error_message('OZF_TRSH_RULE_OUT_THRESHOLD');
110 RETURN;
111 END IF;
112
113 END IF;
114
115
116 END check_threshold_calendar;
117
118 ---------------------------------------------------------------------
119
120
121 -- Hint: Primary key needs to be returned.
122 PROCEDURE Create_Threshold_Rule(
123 p_api_version_number IN NUMBER,
124 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
125 p_commit IN VARCHAR2 := FND_API.G_FALSE,
126 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
127
128 x_return_status OUT NOCOPY VARCHAR2,
129 x_msg_count OUT NOCOPY NUMBER,
130 x_msg_data OUT NOCOPY VARCHAR2,
131
132 p_threshold_rule_rec IN threshold_rule_rec_type := g_miss_threshold_rule_rec,
133 x_threshold_rule_id OUT NOCOPY NUMBER
134 )
135
136 IS
137 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Threshold_Rule';
138 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
139 l_return_status_full VARCHAR2(1);
140 l_object_version_number NUMBER := 1;
141 l_org_id NUMBER ;
142 l_THRESHOLD_RULE_ID NUMBER;
143 l_dummy NUMBER;
144 l_threshold_calendar VARCHAR2(30);
145 l_converted_days NUMBER;
146 l_return_status VARCHAR2(30);
147
148 CURSOR c_id IS
149 SELECT OZF_THRESHOLD_RULES_s.NEXTVAL
150 FROM dual;
151
152 CURSOR c_id_exists (l_id IN NUMBER) IS
153 SELECT 1 FROM dual
154 WHERE EXISTS (SELECT 1 FROM OZF_THRESHOLD_RULES_ALL
155 WHERE THRESHOLD_RULE_ID = l_id);
156
157 BEGIN
158 -- Standard Start of API savepoint
159 SAVEPOINT CREATE_Threshold_Rule_PVT;
160
161 -- Standard call to check for call compatibility.
162 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
163 p_api_version_number,
164 l_api_name,
165 G_PKG_NAME)
166 THEN
167 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
168 END IF;
169
170 -- Initialize message list if p_init_msg_list is set to TRUE.
171 IF FND_API.to_Boolean( p_init_msg_list )
172 THEN
173 FND_MSG_PUB.initialize;
174 END IF;
175
176 -- Debug Message
177 IF G_DEBUG THEN
178 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
179 END IF;
180
181
182 -- Initialize API return status to SUCCESS
183 x_return_status := FND_API.G_RET_STS_SUCCESS;
184
185 -- Local variable initialization
186
187 IF p_threshold_rule_rec.THRESHOLD_RULE_ID IS NULL OR p_threshold_rule_rec.THRESHOLD_RULE_ID = FND_API.g_miss_num THEN
188 LOOP
189 l_dummy := NULL;
190 OPEN c_id;
191 FETCH c_id INTO l_THRESHOLD_RULE_ID;
192 CLOSE c_id;
193
194 OPEN c_id_exists(l_THRESHOLD_RULE_ID);
195 FETCH c_id_exists INTO l_dummy;
196 CLOSE c_id_exists;
197 EXIT WHEN l_dummy IS NULL;
198 END LOOP;
199 END IF;
200
201 -- default threshold calendar
202 IF p_threshold_rule_rec.threshold_calendar IS NULL
203 AND ( p_threshold_rule_rec.start_period_name IS NOT NULL
204 OR p_threshold_rule_rec.end_period_name IS NOT NULL) THEN
205 l_threshold_calendar := fnd_profile.VALUE('AMS_CAMPAIGN_DEFAULT_CALENDER');
206 END IF;
207
208 ------------------- check calendar ----------------------
209 IF p_threshold_rule_rec.threshold_calendar <> fnd_api.g_miss_char
210 OR p_threshold_rule_rec.start_period_name <> fnd_api.g_miss_char
211 OR p_threshold_rule_rec.end_period_name <> fnd_api.g_miss_char
212 OR p_threshold_rule_rec.start_date <> fnd_api.g_miss_date
213 OR p_threshold_rule_rec.end_date <> fnd_api.g_miss_date THEN
214 check_threshold_calendar(
215 l_threshold_calendar
216 ,p_threshold_rule_rec.start_period_name
217 ,p_threshold_rule_rec.end_period_name
218 ,p_threshold_rule_rec.start_date
219 ,p_threshold_rule_rec.end_date
220 ,p_threshold_rule_rec.threshold_id
221 ,l_return_status);
222
223 IF l_return_status <> fnd_api.g_ret_sts_success THEN
224 RAISE FND_API.G_EXC_ERROR;
225
226 --x_return_status := l_return_status;
227 END IF;
228 END IF;
229
230 -- =========================================================================
231 -- Validate Environment
232 -- =========================================================================
233
234 IF FND_GLOBAL.User_Id IS NULL
235 THEN
236 OZF_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
237 RAISE FND_API.G_EXC_ERROR;
238 END IF;
239
240 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
241 THEN
242 -- Debug message
243 IF G_DEBUG THEN
244 OZF_UTILITY_PVT.debug_message('Private API: Validate_Threshold_Rule');
245 END IF;
246
247 -- Invoke validation procedures
248 Validate_threshold_rule(
249 p_api_version_number => 1.0,
250 p_init_msg_list => FND_API.G_FALSE,
251 p_validation_level => p_validation_level,
252 p_threshold_rule_rec => p_threshold_rule_rec,
253 x_return_status => x_return_status,
254 x_msg_count => x_msg_count,
255 x_msg_data => x_msg_data);
256 END IF;
257
258 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
259 RAISE FND_API.G_EXC_ERROR;
260 END IF;
261
262 -- Calculate converted days.
263 IF p_threshold_rule_rec.repeat_frequency IS NOT NULL
264 AND p_threshold_rule_rec.frequency_period IS NOT NULL THEN
265 Calculate_converted_day(
266 p_threshold_rule_rec.repeat_frequency
267 ,p_threshold_rule_rec.frequency_period
268 ,p_threshold_rule_rec.start_date
269 ,p_threshold_rule_rec.end_date
270 ,l_converted_days
271 ,l_return_status);
272
273 IF l_return_status <> fnd_api.g_ret_sts_success THEN
274 x_return_status := l_return_status;
275 END IF;
276
277 END IF;
278
279 -- Debug Message
280 IF G_DEBUG THEN
281 OZF_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
282 END IF;
283
284 -- Invoke table handler(OZF_THRESHOLD_RULES_PKG.Insert_Row)
285 OZF_THRESHOLD_RULES_PKG.Insert_Row(
286 px_threshold_rule_id => l_threshold_rule_id,
287 p_last_update_date => SYSDATE,
288 p_last_updated_by => NVL(fnd_global.user_id, -1),
289 p_last_update_login => NVL(fnd_global.conc_login_id, -1),
290 p_creation_date => SYSDATE,
291 p_created_by => NVL(fnd_global.user_id, -1),
292 p_created_from => NULL,
293 p_request_id => fnd_global.conc_request_id,
294 p_program_application_id => fnd_global.prog_appl_id,
295 p_program_id => fnd_global.conc_program_id,
296 p_program_update_date => SYSDATE,
297 p_period_type => p_threshold_rule_rec.period_type,
298 p_enabled_flag => p_threshold_rule_rec.enabled_flag,
299 p_threshold_calendar =>l_threshold_calendar,
300 p_start_period_name => p_threshold_rule_rec.start_period_name,
301 p_end_period_name => p_threshold_rule_rec.end_period_name,
302 p_threshold_id => p_threshold_rule_rec.threshold_id,
303 p_start_date => p_threshold_rule_rec.start_date,
304 p_end_date => p_threshold_rule_rec.end_date,
305 p_value_limit => p_threshold_rule_rec.value_limit,
306 p_operator_code => p_threshold_rule_rec.operator_code,
307 p_percent_amount => p_threshold_rule_rec.percent_amount,
308 p_base_line => p_threshold_rule_rec.base_line,
309 p_error_mode => p_threshold_rule_rec.error_mode,
310 p_repeat_frequency => p_threshold_rule_rec.repeat_frequency,
311 p_frequency_period => p_threshold_rule_rec.frequency_period,
312 p_attribute_category => p_threshold_rule_rec.attribute_category,
313 p_attribute1 => p_threshold_rule_rec.attribute1,
314 p_attribute2 => p_threshold_rule_rec.attribute2,
315 p_attribute3 => p_threshold_rule_rec.attribute3,
316 p_attribute4 => p_threshold_rule_rec.attribute4,
317 p_attribute5 => p_threshold_rule_rec.attribute5,
318 p_attribute6 => p_threshold_rule_rec.attribute6,
319 p_attribute7 => p_threshold_rule_rec.attribute7,
320 p_attribute8 => p_threshold_rule_rec.attribute8,
321 p_attribute9 => p_threshold_rule_rec.attribute9,
322 p_attribute10 => p_threshold_rule_rec.attribute10,
323 p_attribute11 => p_threshold_rule_rec.attribute11,
324 p_attribute12 => p_threshold_rule_rec.attribute12,
325 p_attribute13 => p_threshold_rule_rec.attribute13,
326 p_attribute14 => p_threshold_rule_rec.attribute14,
327 p_attribute15 => p_threshold_rule_rec.attribute15,
328 p_org_id => TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10)),
329 p_security_group_id => p_threshold_rule_rec.security_group_id,
330 p_converted_days => l_converted_days,
331 px_object_version_number => l_object_version_number,
332 p_comparison_type => p_threshold_rule_rec.comparison_type,
333 p_alert_type => p_threshold_rule_rec.alert_type
334 );
335 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
336 RAISE FND_API.G_EXC_ERROR;
337 END IF;
338 --
339 -- End of API body
340 --
341
342 -- Standard check for p_commit
343 IF FND_API.to_Boolean( p_commit )
344 THEN
345 COMMIT WORK;
346 END IF;
347
348 x_threshold_rule_id := l_threshold_rule_id;
349
350 -- Debug Message
351 IF G_DEBUG THEN
352 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
353 END IF;
354
355 -- Standard call to get message count and if count is 1, get message info.
356 FND_MSG_PUB.Count_And_Get
357 (p_count => x_msg_count,
358 p_data => x_msg_data
359 );
360 EXCEPTION
361
362 WHEN OZF_Utility_PVT.resource_locked THEN
363 x_return_status := FND_API.g_ret_sts_error;
364 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
365
366 WHEN FND_API.G_EXC_ERROR THEN
367 ROLLBACK TO CREATE_Threshold_Rule_PVT;
368 x_return_status := FND_API.G_RET_STS_ERROR;
369 -- Standard call to get message count and if count=1, get the message
370 FND_MSG_PUB.Count_And_Get (
371 p_encoded => FND_API.G_FALSE,
372 p_count => x_msg_count,
373 p_data => x_msg_data
374 );
375
376 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
377 ROLLBACK TO CREATE_Threshold_Rule_PVT;
378 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
379 -- Standard call to get message count and if count=1, get the message
380 FND_MSG_PUB.Count_And_Get (
381 p_encoded => FND_API.G_FALSE,
382 p_count => x_msg_count,
383 p_data => x_msg_data
384 );
385
386 WHEN OTHERS THEN
387 ROLLBACK TO CREATE_Threshold_Rule_PVT;
388 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
389 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
390 THEN
391 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
392 END IF;
393 -- Standard call to get message count and if count=1, get the message
394 FND_MSG_PUB.Count_And_Get (
395 p_encoded => FND_API.G_FALSE,
396 p_count => x_msg_count,
397 p_data => x_msg_data
398 );
399 End Create_Threshold_Rule;
400
401
402 PROCEDURE Update_Threshold_Rule(
403 p_api_version_number IN NUMBER,
404 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
405 p_commit IN VARCHAR2 := FND_API.G_FALSE,
406 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
407
408 x_return_status OUT NOCOPY VARCHAR2,
409 x_msg_count OUT NOCOPY NUMBER,
410 x_msg_data OUT NOCOPY VARCHAR2,
411
412 p_threshold_rule_rec IN threshold_rule_rec_type,
413 x_object_version_number OUT NOCOPY NUMBER
414 )
415
416 IS
417
418 CURSOR c_get_threshold_rule(threshold_rule_id NUMBER) IS
419 SELECT *
420 FROM OZF_THRESHOLD_RULES_ALL
421 WHERE THRESHOLD_RULE_ID = threshold_rule_id
422 AND OBJECT_VERSION_NUMBER =p_threshold_rule_rec.object_version_number ;
423 -- Hint: Developer need to provide Where clause
424
425 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Threshold_Rule';
426 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
427 -- Local Variables
428 l_object_version_number NUMBER := p_threshold_rule_rec.object_version_number;
429 l_THRESHOLD_RULE_ID NUMBER ;
430 l_ref_threshold_rule_rec c_get_Threshold_Rule%ROWTYPE ;
431 l_tar_threshold_rule_rec OZF_Threshold_Rule_PVT.threshold_rule_rec_type := P_threshold_rule_rec;
432 l_threshold_rule_rec OZF_Threshold_Rule_PVT.threshold_rule_rec_type;
433 l_rowid ROWID;
434 l_converted_days NUMBER;
435 l_return_status VARCHAR2(30);
436
437 BEGIN
438 -- Standard Start of API savepoint
439 SAVEPOINT UPDATE_Threshold_Rule_PVT;
440
441 -- Standard call to check for call compatibility.
442 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
443 p_api_version_number,
444 l_api_name,
445 G_PKG_NAME)
446 THEN
447 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
448 END IF;
449
450 -- Initialize message list if p_init_msg_list is set to TRUE.
451 IF FND_API.to_Boolean( p_init_msg_list )
452 THEN
453 FND_MSG_PUB.initialize;
454 END IF;
455
456 -- Debug Message
457 IF G_DEBUG THEN
458 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
459 END IF;
460
461
462 -- Initialize API return status to SUCCESS
463 x_return_status := FND_API.G_RET_STS_SUCCESS;
464
465 -- Debug Message
466 IF G_DEBUG THEN
467 OZF_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select ' || l_tar_threshold_rule_rec.threshold_rule_id);
468 END IF;
469
470
471 OPEN c_get_Threshold_Rule( l_tar_threshold_rule_rec.threshold_rule_id);
472
473 FETCH c_get_Threshold_Rule INTO l_ref_threshold_rule_rec ;
474
475 If ( c_get_Threshold_Rule%NOTFOUND) THEN
476 OZF_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
477 p_token_name => 'INFO',
478 p_token_value => 'Threshold_Rule') ;
479 RAISE FND_API.G_EXC_ERROR;
480 END IF;
481 -- Debug Message
482 IF G_DEBUG THEN
483 OZF_UTILITY_PVT.debug_message('Private API: - Close Cursor');
484 END IF;
485 CLOSE c_get_Threshold_Rule;
486
487
488 If (l_tar_threshold_rule_rec.object_version_number is NULL or
489 l_tar_threshold_rule_rec.object_version_number = FND_API.G_MISS_NUM ) Then
490 OZF_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING',
491 p_token_name => 'COLUMN',
492 p_token_value => 'Last_Update_Date') ;
493 raise FND_API.G_EXC_ERROR;
494 End if;
495 -- Check Whether record has been changed by someone else
496 If (l_tar_threshold_rule_rec.object_version_number <> l_ref_threshold_rule_rec.object_version_number) Then
497 OZF_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED',
498 p_token_name => 'INFO',
499 p_token_value => 'Threshold_Rule') ;
500 raise FND_API.G_EXC_ERROR;
501 End if;
502
503
504 Complete_threshold_rule_Rec(
505 p_threshold_rule_rec => p_threshold_rule_rec,
506 x_complete_rec => l_threshold_rule_rec
507 );
508
509
510 -- Calculate converted days.
511 IF l_threshold_rule_rec.repeat_frequency IS NOT NULL
512 AND l_threshold_rule_rec.frequency_period IS NOT NULL THEN
513 Calculate_converted_day(
514 l_threshold_rule_rec.repeat_frequency
515 ,l_threshold_rule_rec.frequency_period
516 ,l_threshold_rule_rec.start_date
517 ,l_threshold_rule_rec.end_date
518 ,l_converted_days
519 ,l_return_status);
520
521 IF l_return_status <> fnd_api.g_ret_sts_success THEN
522 x_return_status := l_return_status;
523 END IF;
524
525 END IF;
526
527
528 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
529 Check_threshold_rule_Items(
530 p_threshold_rule_rec => l_threshold_rule_rec,
531 p_validation_mode => JTF_PLSQL_API.g_update,
532 x_return_status => x_return_status
533 );
534
535 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
536 RAISE FND_API.G_EXC_ERROR;
537 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
538 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
539 END IF;
540 END IF;
541
542 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
543 Validate_threshold_rule_Rec(
544 p_api_version_number => 1.0,
545 p_init_msg_list => FND_API.G_FALSE,
546 x_return_status => x_return_status,
547 x_msg_count => x_msg_count,
548 x_msg_data => x_msg_data,
549 p_threshold_rule_rec => l_threshold_rule_rec);
550
551 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
552 RAISE FND_API.G_EXC_ERROR;
553 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
554 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
555 END IF;
556 END IF;
557
558 -- Debug Message
559 IF G_DEBUG THEN
560 OZF_UTILITY_PVT.debug_message('Private API: Calling update table handler');
561 END IF;
562
563 -- Invoke table handler(OZF_THRESHOLD_RULES_PKG.Update_Row)
564 OZF_THRESHOLD_RULES_PKG.Update_Row(
565 p_threshold_rule_id => l_threshold_rule_rec.threshold_rule_id,
566 p_last_update_date => SYSDATE,
567 p_last_updated_by => NVL(fnd_global.user_id, -1),
568 p_last_update_login => NVL(fnd_global.conc_login_id, -1),
569 p_created_from => l_threshold_rule_rec.created_from,
570 p_request_id => fnd_global.conc_request_id,
571 p_program_application_id => fnd_global.prog_appl_id,
572 p_program_id => fnd_global.conc_program_id,
573 p_program_update_date => SYSDATE,
574 p_period_type => l_threshold_rule_rec.period_type,
575 p_enabled_flag => l_threshold_rule_rec.enabled_flag,
576 p_start_period_name => l_threshold_rule_rec.start_period_name,
577 p_threshold_calendar =>l_threshold_rule_rec.threshold_calendar,
578 p_end_period_name => l_threshold_rule_rec.end_period_name,
579 p_threshold_id => l_threshold_rule_rec.threshold_id,
580 p_start_date => l_threshold_rule_rec.start_date,
581 p_end_date => l_threshold_rule_rec.end_date,
582 p_value_limit => l_threshold_rule_rec.value_limit,
583 p_operator_code => l_threshold_rule_rec.operator_code,
584 p_percent_amount => l_threshold_rule_rec.percent_amount,
585 p_base_line => l_threshold_rule_rec.base_line,
586 p_error_mode => l_threshold_rule_rec.error_mode,
587 p_repeat_frequency => l_threshold_rule_rec.repeat_frequency,
588 p_frequency_period => l_threshold_rule_rec.frequency_period,
589 p_attribute_category => l_threshold_rule_rec.attribute_category,
590 p_attribute1 => l_threshold_rule_rec.attribute1,
591 p_attribute2 => l_threshold_rule_rec.attribute2,
592 p_attribute3 => l_threshold_rule_rec.attribute3,
593 p_attribute4 => l_threshold_rule_rec.attribute4,
594 p_attribute5 => l_threshold_rule_rec.attribute5,
595 p_attribute6 => l_threshold_rule_rec.attribute6,
596 p_attribute7 => l_threshold_rule_rec.attribute7,
597 p_attribute8 => l_threshold_rule_rec.attribute8,
598 p_attribute9 => l_threshold_rule_rec.attribute9,
599 p_attribute10 => l_threshold_rule_rec.attribute10,
600 p_attribute11 => l_threshold_rule_rec.attribute11,
601 p_attribute12 => l_threshold_rule_rec.attribute12,
602 p_attribute13 => l_threshold_rule_rec.attribute13,
603 p_attribute14 => l_threshold_rule_rec.attribute14,
604 p_attribute15 => l_threshold_rule_rec.attribute15,
605 p_org_id => l_threshold_rule_rec.org_id,
606 p_security_group_id => l_threshold_rule_rec.security_group_id,
607 p_converted_days => l_converted_days,
608 px_object_version_number => l_object_version_number,
609 p_comparison_type => l_threshold_rule_rec.comparison_type,
610 p_alert_type => l_threshold_rule_rec.alert_type
611 );
612 --
613 -- End of API body.
614 --
615
616 -- Standard check for p_commit
617 IF FND_API.to_Boolean( p_commit )
618 THEN
619 COMMIT WORK;
620 END IF;
621
622 x_object_version_number := l_object_version_number;
623
624 -- Debug Message
625 IF G_DEBUG THEN
626 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
627 END IF;
628
629 -- Standard call to get message count and if count is 1, get message info.
630 FND_MSG_PUB.Count_And_Get
631 (p_count => x_msg_count,
632 p_data => x_msg_data
633 );
634 EXCEPTION
635
636 WHEN OZF_Utility_PVT.resource_locked THEN
637 x_return_status := FND_API.g_ret_sts_error;
638 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
639
640 WHEN FND_API.G_EXC_ERROR THEN
641 ROLLBACK TO UPDATE_Threshold_Rule_PVT;
642 x_return_status := FND_API.G_RET_STS_ERROR;
643 -- Standard call to get message count and if count=1, get the message
644 FND_MSG_PUB.Count_And_Get (
645 p_encoded => FND_API.G_FALSE,
646 p_count => x_msg_count,
647 p_data => x_msg_data
648 );
649
650 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
651 ROLLBACK TO UPDATE_Threshold_Rule_PVT;
652 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
653 -- Standard call to get message count and if count=1, get the message
654 FND_MSG_PUB.Count_And_Get (
655 p_encoded => FND_API.G_FALSE,
656 p_count => x_msg_count,
657 p_data => x_msg_data
658 );
659
660 WHEN OTHERS THEN
661 ROLLBACK TO UPDATE_Threshold_Rule_PVT;
662 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
663 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
664 THEN
665 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
666 END IF;
667 -- Standard call to get message count and if count=1, get the message
668 FND_MSG_PUB.Count_And_Get (
669 p_encoded => FND_API.G_FALSE,
670 p_count => x_msg_count,
671 p_data => x_msg_data
672 );
673 End Update_Threshold_Rule;
674
675
676 PROCEDURE Delete_Threshold_Rule(
677 p_api_version_number IN NUMBER,
678 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
679 p_commit IN VARCHAR2 := FND_API.G_FALSE,
680 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
681 x_return_status OUT NOCOPY VARCHAR2,
682 x_msg_count OUT NOCOPY NUMBER,
683 x_msg_data OUT NOCOPY VARCHAR2,
684 p_threshold_rule_id IN NUMBER,
685 p_object_version_number IN NUMBER
686 )
687
688 IS
689 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Threshold_Rule';
690 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
691 l_object_version_number NUMBER;
692
693 BEGIN
694 -- Standard Start of API savepoint
695 SAVEPOINT DELETE_Threshold_Rule_PVT;
696
697 -- Standard call to check for call compatibility.
698 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
699 p_api_version_number,
700 l_api_name,
701 G_PKG_NAME)
702 THEN
703 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
704 END IF;
705
706 -- Initialize message list if p_init_msg_list is set to TRUE.
707 IF FND_API.to_Boolean( p_init_msg_list )
708 THEN
709 FND_MSG_PUB.initialize;
710 END IF;
711
712 -- Debug Message
713 IF G_DEBUG THEN
714 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
715 END IF;
716
717
718 -- Initialize API return status to SUCCESS
719 x_return_status := FND_API.G_RET_STS_SUCCESS;
720
721 --
722 -- Api body
723 --
724 -- Debug Message
725 IF G_DEBUG THEN
726 OZF_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
727 END IF;
728
729 -- Invoke table handler(OZF_THRESHOLD_RULES_PKG.Delete_Row)
730 OZF_THRESHOLD_RULES_PKG.Delete_Row(
731 p_THRESHOLD_RULE_ID => p_THRESHOLD_RULE_ID);
732 --
733 -- End of API body
734 --
735
736 -- Standard check for p_commit
737 IF FND_API.to_Boolean( p_commit )
738 THEN
739 COMMIT WORK;
740 END IF;
741
742
743 -- Debug Message
744 IF G_DEBUG THEN
745 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
746 END IF;
747
748 -- Standard call to get message count and if count is 1, get message info.
749 FND_MSG_PUB.Count_And_Get
750 (p_count => x_msg_count,
751 p_data => x_msg_data
752 );
753 EXCEPTION
754
755 WHEN OZF_Utility_PVT.resource_locked THEN
756 x_return_status := FND_API.g_ret_sts_error;
757 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
758
759 WHEN FND_API.G_EXC_ERROR THEN
760 ROLLBACK TO DELETE_Threshold_Rule_PVT;
761 x_return_status := FND_API.G_RET_STS_ERROR;
762 -- Standard call to get message count and if count=1, get the message
763 FND_MSG_PUB.Count_And_Get (
764 p_encoded => FND_API.G_FALSE,
765 p_count => x_msg_count,
766 p_data => x_msg_data
767 );
768
769 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
770 ROLLBACK TO DELETE_Threshold_Rule_PVT;
771 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
772 -- Standard call to get message count and if count=1, get the message
773 FND_MSG_PUB.Count_And_Get (
774 p_encoded => FND_API.G_FALSE,
775 p_count => x_msg_count,
776 p_data => x_msg_data
777 );
778
779 WHEN OTHERS THEN
780 ROLLBACK TO DELETE_Threshold_Rule_PVT;
781 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
782 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
783 THEN
784 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
785 END IF;
786 -- Standard call to get message count and if count=1, get the message
787 FND_MSG_PUB.Count_And_Get (
788 p_encoded => FND_API.G_FALSE,
789 p_count => x_msg_count,
790 p_data => x_msg_data
791 );
792 End Delete_Threshold_Rule;
793
794
795
796 -- Hint: Primary key needs to be returned.
797 PROCEDURE Lock_Threshold_Rule(
798 p_api_version_number IN NUMBER,
799 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
800
801 x_return_status OUT NOCOPY VARCHAR2,
802 x_msg_count OUT NOCOPY NUMBER,
803 x_msg_data OUT NOCOPY VARCHAR2,
804
805 p_threshold_rule_id IN NUMBER,
806 p_object_version IN NUMBER
807 )
808
809 IS
810 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Threshold_Rule';
811 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
812 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
813 l_THRESHOLD_RULE_ID NUMBER;
814
815 CURSOR c_Threshold_Rule IS
816 SELECT THRESHOLD_RULE_ID
817 FROM OZF_THRESHOLD_RULES_ALL
818 WHERE THRESHOLD_RULE_ID = p_THRESHOLD_RULE_ID
819 AND object_version_number = p_object_version
820 FOR UPDATE NOWAIT;
821
822 BEGIN
823
824 -- Debug Message
825 IF G_DEBUG THEN
826 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
827 END IF;
828
829 -- Initialize message list if p_init_msg_list is set to TRUE.
830 IF FND_API.to_Boolean( p_init_msg_list )
831 THEN
832 FND_MSG_PUB.initialize;
833 END IF;
834
835 -- Standard call to check for call compatibility.
836 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
837 p_api_version_number,
838 l_api_name,
839 G_PKG_NAME)
840 THEN
841 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
842 END IF;
843
844
845 -- Initialize API return status to SUCCESS
846 x_return_status := FND_API.G_RET_STS_SUCCESS;
847
848
849 ------------------------ lock -------------------------
850
851 IF G_DEBUG THEN
852 OZF_Utility_PVT.debug_message(l_full_name||': start');
853 END IF;
854 OPEN c_Threshold_Rule;
855
856 FETCH c_Threshold_Rule INTO l_THRESHOLD_RULE_ID;
857
858 IF (c_Threshold_Rule%NOTFOUND) THEN
859 CLOSE c_Threshold_Rule;
860 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
861 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
862 FND_MSG_PUB.add;
863 END IF;
864 RAISE FND_API.g_exc_error;
865 END IF;
866
867 CLOSE c_Threshold_Rule;
868
869 -------------------- finish --------------------------
870 FND_MSG_PUB.count_and_get(
871 p_encoded => FND_API.g_false,
872 p_count => x_msg_count,
873 p_data => x_msg_data);
874 IF G_DEBUG THEN
875 OZF_Utility_PVT.debug_message(l_full_name ||': end');
876 END IF;
877 EXCEPTION
878
879 WHEN OZF_Utility_PVT.resource_locked THEN
880 x_return_status := FND_API.g_ret_sts_error;
881 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
882
883 WHEN FND_API.G_EXC_ERROR THEN
884 ROLLBACK TO LOCK_Threshold_Rule_PVT;
885 x_return_status := FND_API.G_RET_STS_ERROR;
886 -- Standard call to get message count and if count=1, get the message
887 FND_MSG_PUB.Count_And_Get (
888 p_encoded => FND_API.G_FALSE,
889 p_count => x_msg_count,
890 p_data => x_msg_data
891 );
892
893 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
894 ROLLBACK TO LOCK_Threshold_Rule_PVT;
895 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
896 -- Standard call to get message count and if count=1, get the message
897 FND_MSG_PUB.Count_And_Get (
898 p_encoded => FND_API.G_FALSE,
899 p_count => x_msg_count,
900 p_data => x_msg_data
901 );
902
903 WHEN OTHERS THEN
904 ROLLBACK TO LOCK_Threshold_Rule_PVT;
905 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
906 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
907 THEN
908 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
909 END IF;
910 -- Standard call to get message count and if count=1, get the message
911 FND_MSG_PUB.Count_And_Get (
912 p_encoded => FND_API.G_FALSE,
913 p_count => x_msg_count,
914 p_data => x_msg_data
915 );
916 End Lock_Threshold_Rule;
917
918
919 PROCEDURE check_threshold_rule_uk_items(
920 p_threshold_rule_rec IN threshold_rule_rec_type,
921 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
922 x_return_status OUT NOCOPY VARCHAR2)
923 IS
924 l_valid_flag VARCHAR2(1);
925
926 BEGIN
927 x_return_status := FND_API.g_ret_sts_success;
928 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
929 l_valid_flag := OZF_Utility_PVT.check_uniqueness(
930 'OZF_THRESHOLD_RULES_ALL',
931 'THRESHOLD_RULE_ID = ''' || p_threshold_rule_rec.THRESHOLD_RULE_ID ||''''
932 );
933 ELSE
934 l_valid_flag := OZF_Utility_PVT.check_uniqueness(
935 'OZF_THRESHOLD_RULES_ALL',
936 'THRESHOLD_RULE_ID = ''' || p_threshold_rule_rec.THRESHOLD_RULE_ID ||
937 ''' AND THRESHOLD_RULE_ID <> ' || p_threshold_rule_rec.THRESHOLD_RULE_ID
938 );
939 END IF;
940
941 IF l_valid_flag = FND_API.g_false THEN
942 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_THRESHD_RULE_ID_DUPLICATE');
943 x_return_status := FND_API.g_ret_sts_error;
944 RETURN;
945 END IF;
946
947 END check_threshold_rule_uk_items;
948
949 PROCEDURE check_threshold_rule_req_items(
950 p_threshold_rule_rec IN threshold_rule_rec_type,
951 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
952 x_return_status OUT NOCOPY VARCHAR2
953 )
954 IS
955 CURSOR c_threshold_type (p_thres_id NUMBER) IS
956 SELECT threshold_type
957 FROM ozf_thresholds_all_b
958 WHERE threshold_id = p_thres_id;
959
960 l_threshold_type VARCHAR2(30);
961
962 BEGIN
963 x_return_status := FND_API.g_ret_sts_success;
964
965 IF p_threshold_rule_rec.start_date IS NULL THEN
966 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
967 FND_MESSAGE.set_name('OZF', 'OZF_FUND_MISSING_COLUMN');
968 FND_MESSAGE.set_token('COLUMN', 'Start Date');
969 FND_MSG_PUB.add;
970 END IF;
971 x_return_status := FND_API.g_ret_sts_error;
972 RETURN;
973 END IF;
974
975 IF p_threshold_rule_rec.end_date IS NULL THEN
976 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
977 FND_MESSAGE.set_name('OZF', 'OZF_FUND_MISSING_COLUMN');
978 FND_MESSAGE.set_token('COLUMN', 'End Date');
979 FND_MSG_PUB.add;
980 END IF;
981 x_return_status := FND_API.g_ret_sts_error;
982 RETURN;
983 END IF;
984
985 IF p_threshold_rule_rec.value_limit IS NULL THEN
986 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
987 FND_MESSAGE.set_name('OZF', 'OZF_FUND_MISSING_COLUMN');
988 FND_MESSAGE.set_token('COLUMN', 'Value Limit');
989 FND_MSG_PUB.add;
990 END IF;
991 x_return_status := FND_API.g_ret_sts_error;
992 RETURN;
993 END IF;
994
995 IF p_threshold_rule_rec.operator_code IS NULL THEN
996 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
997 FND_MESSAGE.set_name('OZF', 'OZF_FUND_MISSING_COLUMN');
998 FND_MESSAGE.set_token('COLUMN', 'Operator');
999 FND_MSG_PUB.add;
1000 END IF;
1001 x_return_status := FND_API.g_ret_sts_error;
1002 RETURN;
1003 END IF;
1004
1005 IF p_threshold_rule_rec.threshold_id IS NOT NULL THEN
1006 OPEN c_threshold_type(p_threshold_rule_rec.threshold_id);
1007 FETCH c_threshold_type INTO l_threshold_type;
1008 CLOSE c_threshold_type;
1009
1010 IF l_threshold_type IS NOT NULL THEN
1011 IF l_threshold_type = 'QUOTA' THEN
1012 IF p_threshold_rule_rec.comparison_type IS NULL THEN
1013 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1014 FND_MESSAGE.set_name('OZF', 'OZF_FUND_MISSING_COLUMN');
1015 FND_MESSAGE.set_token('COLUMN', 'Comparision Type');
1016 FND_MSG_PUB.add;
1017 END IF;
1018 x_return_status := FND_API.g_ret_sts_error;
1019 RETURN;
1020 END IF;
1021 IF p_threshold_rule_rec.percent_amount IS NULL THEN
1022 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1023 FND_MESSAGE.set_name('OZF', 'OZF_FUND_MISSING_COLUMN');
1024 FND_MESSAGE.set_token('COLUMN', 'Comparision Value');
1025 FND_MSG_PUB.add;
1026 END IF;
1027 x_return_status := FND_API.g_ret_sts_error;
1028 RETURN;
1029 END IF;
1030 IF p_threshold_rule_rec.comparison_type = 'PERCENT' THEN
1031 IF p_threshold_rule_rec.base_line IS NULL THEN
1032 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1033 FND_MESSAGE.set_name('OZF', 'OZF_FUND_MISSING_COLUMN');
1034 FND_MESSAGE.set_token('COLUMN', 'OfBaseline');
1035 FND_MSG_PUB.add;
1036 END IF;
1037 x_return_status := FND_API.g_ret_sts_error;
1038 RETURN;
1039 END IF;
1040 END IF;
1041 IF p_threshold_rule_rec.repeat_frequency IS NULL THEN
1042 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1043 FND_MESSAGE.set_name('OZF', 'OZF_FUND_MISSING_COLUMN');
1044 FND_MESSAGE.set_token('COLUMN', 'Frequency');
1045 FND_MSG_PUB.add;
1046 END IF;
1047 x_return_status := FND_API.g_ret_sts_error;
1048 RETURN;
1049 END IF;
1050 IF p_threshold_rule_rec.frequency_period IS NULL THEN
1051 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1052 FND_MESSAGE.set_name('OZF', 'OZF_FUND_MISSING_COLUMN');
1053 FND_MESSAGE.set_token('COLUMN', 'Period Type');
1054 FND_MSG_PUB.add;
1055 END IF;
1056 x_return_status := FND_API.g_ret_sts_error;
1057 RETURN;
1058 END IF;
1059 IF p_threshold_rule_rec.alert_type IS NULL THEN
1060 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1061 FND_MESSAGE.set_name('OZF', 'OZF_FUND_MISSING_COLUMN');
1062 FND_MESSAGE.set_token('COLUMN', 'Alert Type');
1063 FND_MSG_PUB.add;
1064 END IF;
1065 x_return_status := FND_API.g_ret_sts_error;
1066 RETURN;
1067 END IF;
1068 ELSIF l_threshold_type = 'BUDGET' THEN
1069 IF p_threshold_rule_rec.percent_amount IS NULL THEN
1070 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1071 FND_MESSAGE.set_name('OZF', 'OZF_FUND_MISSING_COLUMN');
1072 FND_MESSAGE.set_token('COLUMN', 'Percent');
1073 FND_MSG_PUB.add;
1074 END IF;
1075 x_return_status := FND_API.g_ret_sts_error;
1076 RETURN;
1077 END IF;
1078 IF p_threshold_rule_rec.base_line IS NULL THEN
1079 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1080 FND_MESSAGE.set_name('OZF', 'OZF_FUND_MISSING_COLUMN');
1081 FND_MESSAGE.set_token('COLUMN', 'OfBaseline');
1082 FND_MSG_PUB.add;
1083 END IF;
1084 x_return_status := FND_API.g_ret_sts_error;
1085 RETURN;
1086 END IF;
1087 IF p_threshold_rule_rec.repeat_frequency IS NULL THEN
1088 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1089 FND_MESSAGE.set_name('OZF', 'OZF_FUND_MISSING_COLUMN');
1090 FND_MESSAGE.set_token('COLUMN', 'Frequency');
1091 FND_MSG_PUB.add;
1092 END IF;
1093 x_return_status := FND_API.g_ret_sts_error;
1094 RETURN;
1095 END IF;
1096 IF p_threshold_rule_rec.frequency_period IS NULL THEN
1097 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1098 FND_MESSAGE.set_name('OZF', 'OZF_FUND_MISSING_COLUMN');
1099 FND_MESSAGE.set_token('COLUMN', 'Period Type');
1100 FND_MSG_PUB.add;
1101 END IF;
1102 x_return_status := FND_API.g_ret_sts_error;
1103 RETURN;
1104 END IF;
1105 END IF;
1106 END IF;
1107 END IF;
1108
1109 /*
1110 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1111
1112
1113 IF p_threshold_rule_rec.threshold_rule_id = FND_API.g_miss_num OR p_threshold_rule_rec.threshold_rule_id IS NULL THEN
1114 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_threshold_rule_NO_threshold_rule_id');
1115 x_return_status := FND_API.g_ret_sts_error;
1116 RETURN;
1117 END IF;
1118
1119
1120 IF p_threshold_rule_rec.last_update_date = FND_API.g_miss_date OR p_threshold_rule_rec.last_update_date IS NULL THEN
1121 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_threshold_rule_NO_last_update_date');
1122 x_return_status := FND_API.g_ret_sts_error;
1123 RETURN;
1124 END IF;
1125
1126
1127 IF p_threshold_rule_rec.last_updated_by = FND_API.g_miss_num OR p_threshold_rule_rec.last_updated_by IS NULL THEN
1128 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_threshold_rule_NO_last_updated_by');
1129 x_return_status := FND_API.g_ret_sts_error;
1130 RETURN;
1131 END IF;
1132
1133
1134 IF p_threshold_rule_rec.creation_date = FND_API.g_miss_date OR p_threshold_rule_rec.creation_date IS NULL THEN
1135 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_threshold_rule_NO_creation_date');
1136 x_return_status := FND_API.g_ret_sts_error;
1137 RETURN;
1138 END IF;
1139
1140
1141 IF p_threshold_rule_rec.created_by = FND_API.g_miss_num OR p_threshold_rule_rec.created_by IS NULL THEN
1142 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_threshold_rule_NO_created_by');
1143 x_return_status := FND_API.g_ret_sts_error;
1144 RETURN;
1145 END IF;
1146
1147
1148 IF p_threshold_rule_rec.threshold_id = FND_API.g_miss_num OR p_threshold_rule_rec.threshold_id IS NULL THEN
1149 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_threshold_rule_NO_threshold_id');
1150 x_return_status := FND_API.g_ret_sts_error;
1151 RETURN;
1152 END IF;
1153 ELSE
1154
1155
1156 IF p_threshold_rule_rec.threshold_rule_id IS NULL THEN
1157 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_threshold_rule_NO_threshold_rule_id');
1158 x_return_status := FND_API.g_ret_sts_error;
1159 RETURN;
1160 END IF;
1161
1162
1163 IF p_threshold_rule_rec.last_update_date IS NULL THEN
1164 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_threshold_rule_NO_last_update_date');
1165 x_return_status := FND_API.g_ret_sts_error;
1166 RETURN;
1167 END IF;
1168
1169
1170 IF p_threshold_rule_rec.last_updated_by IS NULL THEN
1171 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_threshold_rule_NO_last_updated_by');
1172 x_return_status := FND_API.g_ret_sts_error;
1173 RETURN;
1174 END IF;
1175
1176
1177 IF p_threshold_rule_rec.creation_date IS NULL THEN
1178 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_threshold_rule_NO_creation_date');
1179 x_return_status := FND_API.g_ret_sts_error;
1180 RETURN;
1181 END IF;
1182
1183
1184 IF p_threshold_rule_rec.created_by IS NULL THEN
1185 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_threshold_rule_NO_created_by');
1186 x_return_status := FND_API.g_ret_sts_error;
1187 RETURN;
1188 END IF;
1189
1190
1191 IF p_threshold_rule_rec.threshold_id IS NULL THEN
1192 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_threshold_rule_NO_threshold_id');
1193 x_return_status := FND_API.g_ret_sts_error;
1194 RETURN;
1195 END IF;
1196 END IF;
1197 */
1198 END check_threshold_rule_req_items;
1199
1200 PROCEDURE check_threshold_rule_FK_items(
1201 p_threshold_rule_rec IN threshold_rule_rec_type,
1202 x_return_status OUT NOCOPY VARCHAR2
1203 )
1204 IS
1205 BEGIN
1206 x_return_status := FND_API.g_ret_sts_success;
1207
1208 -- Enter custom code here
1209
1210 END check_threshold_rule_FK_items;
1211
1212 PROCEDURE check_Lookup_items(
1213 p_threshold_rule_rec IN threshold_rule_rec_type,
1214 x_return_status OUT NOCOPY VARCHAR2
1215 )
1216 IS
1217 BEGIN
1218 x_return_status := FND_API.g_ret_sts_success;
1219
1220 -- Enter custom code here
1221
1222 END check_Lookup_items;
1223
1224 PROCEDURE Check_threshold_rule_Items (
1225 P_threshold_rule_rec IN threshold_rule_rec_type,
1226 p_validation_mode IN VARCHAR2,
1227 x_return_status OUT NOCOPY VARCHAR2
1228 )
1229 IS
1230 BEGIN
1231
1232 -- Check Items Uniqueness API calls
1233
1234 check_threshold_rule_uk_items(
1235 p_threshold_rule_rec => p_threshold_rule_rec,
1236 p_validation_mode => p_validation_mode,
1237 x_return_status => x_return_status);
1238 IF x_return_status <> FND_API.g_ret_sts_success THEN
1239 RETURN;
1240 END IF;
1241
1242 -- Check Items Required/NOT NULL API calls
1243
1244 check_threshold_rule_req_items(
1245 p_threshold_rule_rec => p_threshold_rule_rec,
1246 p_validation_mode => p_validation_mode,
1247 x_return_status => x_return_status);
1248 IF x_return_status <> FND_API.g_ret_sts_success THEN
1249 RETURN;
1250 END IF;
1251 -- Check Items Foreign Keys API calls
1252
1253 check_threshold_rule_FK_items(
1254 p_threshold_rule_rec => p_threshold_rule_rec,
1255 x_return_status => x_return_status);
1256 IF x_return_status <> FND_API.g_ret_sts_success THEN
1257 RETURN;
1258 END IF;
1259 -- Check Items Lookups
1260
1261 check_Lookup_items(
1262 p_threshold_rule_rec => p_threshold_rule_rec,
1263 x_return_status => x_return_status);
1264 IF x_return_status <> FND_API.g_ret_sts_success THEN
1265 RETURN;
1266 END IF;
1267
1268 END Check_threshold_rule_Items;
1269
1270 PROCEDURE Complete_threshold_rule_Rec (
1271 p_threshold_rule_rec IN threshold_rule_rec_type,
1272 x_complete_rec OUT NOCOPY threshold_rule_rec_type)
1273 IS
1274 l_return_status VARCHAR2(1);
1275
1276 CURSOR c_complete IS
1277 SELECT *
1278 FROM ozf_threshold_rules_all
1279 WHERE threshold_rule_id = p_threshold_rule_rec.threshold_rule_id;
1280 l_threshold_rule_rec c_complete%ROWTYPE;
1281 BEGIN
1282 x_complete_rec := p_threshold_rule_rec;
1283
1284
1285 OPEN c_complete;
1286 FETCH c_complete INTO l_threshold_rule_rec;
1287 CLOSE c_complete;
1288
1289 -- threshold_rule_id
1290 IF p_threshold_rule_rec.threshold_rule_id = FND_API.g_miss_num THEN
1291 x_complete_rec.threshold_rule_id := NULL;
1292 END IF;
1293 IF p_threshold_rule_rec.threshold_rule_id IS NULL THEN
1294 x_complete_rec.threshold_rule_id := l_threshold_rule_rec.threshold_rule_id;
1295 END IF;
1296
1297 -- last_update_date
1298 IF p_threshold_rule_rec.last_update_date = FND_API.g_miss_date THEN
1299 x_complete_rec.last_update_date := NULL;
1300 END IF;
1301 IF p_threshold_rule_rec.last_update_date IS NULL THEN
1302 x_complete_rec.last_update_date := l_threshold_rule_rec.last_update_date;
1303 END IF;
1304
1305 -- last_updated_by
1306 IF p_threshold_rule_rec.last_updated_by = FND_API.g_miss_num THEN
1307 x_complete_rec.last_updated_by := NULL;
1308 END IF;
1309 IF p_threshold_rule_rec.last_updated_by IS NULL THEN
1310 x_complete_rec.last_updated_by := l_threshold_rule_rec.last_updated_by;
1311 END IF;
1312
1313 -- last_update_login
1314 IF p_threshold_rule_rec.last_update_login = FND_API.g_miss_num THEN
1315 x_complete_rec.last_update_login := NULL;
1316 END IF;
1317 IF p_threshold_rule_rec.last_update_login IS NULL THEN
1318 x_complete_rec.last_update_login := l_threshold_rule_rec.last_update_login;
1319 END IF;
1320
1321 -- creation_date
1322 IF p_threshold_rule_rec.creation_date = FND_API.g_miss_date THEN
1323 x_complete_rec.creation_date := NULL;
1324 END IF;
1325 IF p_threshold_rule_rec.creation_date IS NULL THEN
1326 x_complete_rec.creation_date := l_threshold_rule_rec.creation_date;
1327 END IF;
1328
1329 -- created_by
1330 IF p_threshold_rule_rec.created_by = FND_API.g_miss_num THEN
1331 x_complete_rec.created_by := NULL;
1332 END IF;
1333 IF p_threshold_rule_rec.created_by IS NULL THEN
1334 x_complete_rec.created_by := l_threshold_rule_rec.created_by;
1335 END IF;
1336
1337 -- created_from
1338 IF p_threshold_rule_rec.created_from = FND_API.g_miss_char THEN
1339 x_complete_rec.created_from := NULL;
1340 END IF;
1341 IF p_threshold_rule_rec.created_from IS NULL THEN
1342 x_complete_rec.created_from := l_threshold_rule_rec.created_from;
1343 END IF;
1344
1345 -- request_id
1346 IF p_threshold_rule_rec.request_id = FND_API.g_miss_num THEN
1347 x_complete_rec.request_id := NULL;
1348 END IF;
1349 IF p_threshold_rule_rec.request_id IS NULL THEN
1350 x_complete_rec.request_id := l_threshold_rule_rec.request_id;
1351 END IF;
1352
1353 -- program_application_id
1354 IF p_threshold_rule_rec.program_application_id = FND_API.g_miss_num THEN
1355 x_complete_rec.program_application_id := NULL;
1356 END IF;
1357 IF p_threshold_rule_rec.program_application_id IS NULL THEN
1358 x_complete_rec.program_application_id := l_threshold_rule_rec.program_application_id;
1359 END IF;
1360
1361 -- program_id
1362 IF p_threshold_rule_rec.program_id = FND_API.g_miss_num THEN
1363 x_complete_rec.program_id := NULL;
1364 END IF;
1365 IF p_threshold_rule_rec.program_id IS NULL THEN
1366 x_complete_rec.program_id := l_threshold_rule_rec.program_id;
1367 END IF;
1368
1369 -- program_update_date
1370 IF p_threshold_rule_rec.program_update_date = FND_API.g_miss_date THEN
1371 x_complete_rec.program_update_date := NULL;
1372 END IF;
1373 IF p_threshold_rule_rec.program_update_date IS NULL THEN
1374 x_complete_rec.program_update_date := l_threshold_rule_rec.program_update_date;
1375 END IF;
1376
1377 -- period_type
1378 IF p_threshold_rule_rec.period_type = FND_API.g_miss_char THEN
1379 x_complete_rec.period_type := NULL;
1380 END IF;
1381 IF p_threshold_rule_rec.period_type IS NULL THEN
1382 x_complete_rec.period_type := l_threshold_rule_rec.period_type;
1383 END IF;
1384
1385 -- enabled_flag
1386 IF p_threshold_rule_rec.enabled_flag = FND_API.g_miss_char THEN
1387 x_complete_rec.enabled_flag := NULL;
1388 END IF;
1389 IF p_threshold_rule_rec.enabled_flag IS NULL THEN
1390 x_complete_rec.enabled_flag := l_threshold_rule_rec.enabled_flag;
1391 END IF;
1392
1393 -- threshold_calendar
1394 IF p_threshold_rule_rec.threshold_calendar = FND_API.g_miss_char THEN
1395 x_complete_rec.threshold_calendar := NULL;
1396 END IF;
1397 IF p_threshold_rule_rec.threshold_calendar IS NULL THEN
1398 x_complete_rec.threshold_calendar := l_threshold_rule_rec.threshold_calendar;
1399 END IF;
1400
1401 -- start_period_name
1402 IF p_threshold_rule_rec.start_period_name = FND_API.g_miss_char THEN
1403 x_complete_rec.start_period_name := NULL;
1404 END IF;
1405 IF p_threshold_rule_rec.start_period_name IS NULL THEN
1406 x_complete_rec.start_period_name := l_threshold_rule_rec.start_period_name;
1407 END IF;
1408
1409 -- end_period_name
1410 IF p_threshold_rule_rec.end_period_name = FND_API.g_miss_char THEN
1411 x_complete_rec.end_period_name := NULL;
1412 END IF;
1413 IF p_threshold_rule_rec.end_period_name IS NULL THEN
1414 x_complete_rec.end_period_name := l_threshold_rule_rec.end_period_name;
1415 END IF;
1416
1417 -- threshold_id
1418 IF p_threshold_rule_rec.threshold_id = FND_API.g_miss_num THEN
1419 x_complete_rec.threshold_id := NULL;
1420 END IF;
1421 IF p_threshold_rule_rec.threshold_id IS NULL THEN
1422 x_complete_rec.threshold_id := l_threshold_rule_rec.threshold_id;
1423 END IF;
1424
1425 -- start_date
1426 IF p_threshold_rule_rec.start_date = FND_API.g_miss_date THEN
1427 x_complete_rec.start_date := NULL;
1428 END IF;
1429 IF p_threshold_rule_rec.start_date IS NULL THEN
1430 x_complete_rec.start_date := l_threshold_rule_rec.start_date;
1431 END IF;
1432
1433 -- end_date
1434 IF p_threshold_rule_rec.end_date = FND_API.g_miss_date THEN
1435 x_complete_rec.end_date := NULL;
1436 END IF;
1437 IF p_threshold_rule_rec.end_date IS NULL THEN
1438 x_complete_rec.end_date := l_threshold_rule_rec.end_date;
1439 END IF;
1440
1441 -- value_limit
1442 IF p_threshold_rule_rec.value_limit = FND_API.g_miss_char THEN
1443 x_complete_rec.value_limit := NULL;
1444 END IF;
1445 IF p_threshold_rule_rec.value_limit IS NULL THEN
1446 x_complete_rec.value_limit := l_threshold_rule_rec.value_limit;
1447 END IF;
1448
1449 -- operator_code
1450 IF p_threshold_rule_rec.operator_code = FND_API.g_miss_char THEN
1451 x_complete_rec.operator_code := NULL;
1452 END IF;
1453 IF p_threshold_rule_rec.operator_code IS NULL THEN
1454 x_complete_rec.operator_code := l_threshold_rule_rec.operator_code;
1455 END IF;
1456
1457 -- percent_amount
1458 IF p_threshold_rule_rec.percent_amount = FND_API.g_miss_num THEN
1459 x_complete_rec.percent_amount := NULL;
1460 END IF;
1461 IF p_threshold_rule_rec.percent_amount IS NULL THEN
1462 x_complete_rec.percent_amount := l_threshold_rule_rec.percent_amount;
1463 END IF;
1464
1465 -- base_line
1466 IF p_threshold_rule_rec.base_line = FND_API.g_miss_char THEN
1467 x_complete_rec.base_line := NULL;
1468 END IF;
1469 IF p_threshold_rule_rec.base_line IS NULL THEN
1470 x_complete_rec.base_line := l_threshold_rule_rec.base_line;
1471 END IF;
1472
1473 -- error_mode
1474 IF p_threshold_rule_rec.error_mode = FND_API.g_miss_char THEN
1475 x_complete_rec.error_mode := NULL;
1476 END IF;
1477 IF p_threshold_rule_rec.error_mode IS NULL THEN
1478 x_complete_rec.error_mode := l_threshold_rule_rec.error_mode;
1479 END IF;
1480
1481 -- repeat_frequency
1482 IF p_threshold_rule_rec.repeat_frequency = FND_API.g_miss_num THEN
1483 x_complete_rec.repeat_frequency := NULL;
1484 END IF;
1485 IF p_threshold_rule_rec.repeat_frequency IS NULL THEN
1486 x_complete_rec.repeat_frequency := l_threshold_rule_rec.repeat_frequency;
1487 END IF;
1488
1489 -- frequency_period
1490 IF p_threshold_rule_rec.frequency_period = FND_API.g_miss_char THEN
1491 x_complete_rec.frequency_period := NULL;
1492 END IF;
1493 IF p_threshold_rule_rec.frequency_period IS NULL THEN
1494 x_complete_rec.frequency_period := l_threshold_rule_rec.frequency_period;
1495 END IF;
1496
1497 -- attribute_category
1498 IF p_threshold_rule_rec.attribute_category = FND_API.g_miss_char THEN
1499 x_complete_rec.attribute_category := NULL;
1500 END IF;
1501 IF p_threshold_rule_rec.attribute_category IS NULL THEN
1502 x_complete_rec.attribute_category := l_threshold_rule_rec.attribute_category;
1503 END IF;
1504
1505 -- attribute1
1506 IF p_threshold_rule_rec.attribute1 = FND_API.g_miss_char THEN
1507 x_complete_rec.attribute1 := NULL;
1508 END IF;
1509 IF p_threshold_rule_rec.attribute1 IS NULL THEN
1510 x_complete_rec.attribute1 := l_threshold_rule_rec.attribute1;
1511 END IF;
1512
1513 -- attribute2
1514 IF p_threshold_rule_rec.attribute2 = FND_API.g_miss_char THEN
1515 x_complete_rec.attribute2 := NULL;
1516 END IF;
1517 IF p_threshold_rule_rec.attribute2 IS NULL THEN
1518 x_complete_rec.attribute2 := l_threshold_rule_rec.attribute2;
1519 END IF;
1520
1521 -- attribute3
1522 IF p_threshold_rule_rec.attribute3 = FND_API.g_miss_char THEN
1523 x_complete_rec.attribute3 := NULL;
1524 END IF;
1525 IF p_threshold_rule_rec.attribute3 IS NULL THEN
1526 x_complete_rec.attribute3 := l_threshold_rule_rec.attribute3;
1527 END IF;
1528
1529 -- attribute4
1530 IF p_threshold_rule_rec.attribute4 = FND_API.g_miss_char THEN
1531 x_complete_rec.attribute4 := NULL;
1532 END IF;
1533 IF p_threshold_rule_rec.attribute4 IS NULL THEN
1534 x_complete_rec.attribute4 := l_threshold_rule_rec.attribute4;
1535 END IF;
1536
1537 -- attribute5
1538 IF p_threshold_rule_rec.attribute5 = FND_API.g_miss_char THEN
1539 x_complete_rec.attribute5 := NULL;
1540 END IF;
1541 IF p_threshold_rule_rec.attribute5 IS NULL THEN
1542 x_complete_rec.attribute5 := l_threshold_rule_rec.attribute5;
1543 END IF;
1544
1545 -- attribute6
1546 IF p_threshold_rule_rec.attribute6 = FND_API.g_miss_char THEN
1547 x_complete_rec.attribute6 := NULL;
1548 END IF;
1549 IF p_threshold_rule_rec.attribute6 IS NULL THEN
1550 x_complete_rec.attribute6 := l_threshold_rule_rec.attribute6;
1551 END IF;
1552
1553 -- attribute7
1554 IF p_threshold_rule_rec.attribute7 = FND_API.g_miss_char THEN
1555 x_complete_rec.attribute7 := NULL;
1556 END IF;
1557 IF p_threshold_rule_rec.attribute7 IS NULL THEN
1558 x_complete_rec.attribute7 := l_threshold_rule_rec.attribute7;
1559 END IF;
1560
1561 -- attribute8
1562 IF p_threshold_rule_rec.attribute8 = FND_API.g_miss_char THEN
1563 x_complete_rec.attribute8 := NULL;
1564 END IF;
1565 IF p_threshold_rule_rec.attribute8 IS NULL THEN
1566 x_complete_rec.attribute8 := l_threshold_rule_rec.attribute8;
1567 END IF;
1568
1569 -- attribute9
1570 IF p_threshold_rule_rec.attribute9 = FND_API.g_miss_char THEN
1571 x_complete_rec.attribute9 := NULL;
1572 END IF;
1573 IF p_threshold_rule_rec.attribute9 IS NULL THEN
1574 x_complete_rec.attribute9 := l_threshold_rule_rec.attribute9;
1575 END IF;
1576
1577 -- attribute10
1578 IF p_threshold_rule_rec.attribute10 = FND_API.g_miss_char THEN
1579 x_complete_rec.attribute10 := NULL;
1580 END IF;
1581 IF p_threshold_rule_rec.attribute10 IS NULL THEN
1582 x_complete_rec.attribute10 := l_threshold_rule_rec.attribute10;
1583 END IF;
1584
1585 -- attribute11
1586 IF p_threshold_rule_rec.attribute11 = FND_API.g_miss_char THEN
1587 x_complete_rec.attribute11 := NULL;
1588 END IF;
1589 IF p_threshold_rule_rec.attribute11 IS NULL THEN
1590 x_complete_rec.attribute11 := l_threshold_rule_rec.attribute11;
1591 END IF;
1592
1593 -- attribute12
1594 IF p_threshold_rule_rec.attribute12 = FND_API.g_miss_char THEN
1595 x_complete_rec.attribute12 := NULL;
1596 END IF;
1597 IF p_threshold_rule_rec.attribute12 IS NULL THEN
1598 x_complete_rec.attribute12 := l_threshold_rule_rec.attribute12;
1599 END IF;
1600
1601 -- attribute13
1602 IF p_threshold_rule_rec.attribute13 = FND_API.g_miss_char THEN
1603 x_complete_rec.attribute13 := NULL;
1604 END IF;
1605 IF p_threshold_rule_rec.attribute13 IS NULL THEN
1606 x_complete_rec.attribute13 := l_threshold_rule_rec.attribute13;
1607 END IF;
1608
1609 -- attribute14
1610 IF p_threshold_rule_rec.attribute14 = FND_API.g_miss_char THEN
1611 x_complete_rec.attribute14 := NULL;
1612 END IF;
1613 IF p_threshold_rule_rec.attribute14 IS NULL THEN
1614 x_complete_rec.attribute14 := l_threshold_rule_rec.attribute14;
1615 END IF;
1616
1617 -- attribute15
1618 IF p_threshold_rule_rec.attribute15 = FND_API.g_miss_char THEN
1619 x_complete_rec.attribute15 := NULL;
1620 END IF;
1621 IF p_threshold_rule_rec.attribute15 IS NULL THEN
1622 x_complete_rec.attribute15 := l_threshold_rule_rec.attribute15;
1623 END IF;
1624
1625 -- org_id
1626 IF p_threshold_rule_rec.org_id = FND_API.g_miss_num THEN
1627 x_complete_rec.org_id := NULL;
1628 END IF;
1629 IF p_threshold_rule_rec.org_id IS NULL THEN
1630 x_complete_rec.org_id := l_threshold_rule_rec.org_id;
1631 END IF;
1632
1633 -- security_group_id
1634 IF p_threshold_rule_rec.security_group_id = FND_API.g_miss_num THEN
1635 x_complete_rec.security_group_id := NULL;
1636 END IF;
1637 IF p_threshold_rule_rec.security_group_id IS NULL THEN
1638 x_complete_rec.security_group_id := l_threshold_rule_rec.security_group_id;
1639 END IF;
1640
1641 -- converted_days
1642 IF p_threshold_rule_rec.converted_days = FND_API.g_miss_num THEN
1643 x_complete_rec.converted_days := NULL;
1644 END IF;
1645 IF p_threshold_rule_rec.converted_days IS NULL THEN
1646 x_complete_rec.converted_days := l_threshold_rule_rec.converted_days;
1647 END IF;
1648
1649 -- object_version_number
1650 IF p_threshold_rule_rec.object_version_number = FND_API.g_miss_num THEN
1651 x_complete_rec.object_version_number := NULL;
1652 END IF;
1653 IF p_threshold_rule_rec.object_version_number IS NULL THEN
1654 x_complete_rec.object_version_number := l_threshold_rule_rec.object_version_number;
1655 END IF;
1656
1657 -- comparison_type
1658 IF p_threshold_rule_rec.comparison_type = FND_API.g_miss_char THEN
1659 x_complete_rec.comparison_type := NULL;
1660 END IF;
1661 IF p_threshold_rule_rec.comparison_type IS NULL THEN
1662 x_complete_rec.comparison_type := l_threshold_rule_rec.comparison_type;
1663 END IF;
1664
1665 -- alert_type
1666 IF p_threshold_rule_rec.alert_type = FND_API.g_miss_char THEN
1667 x_complete_rec.alert_type := NULL;
1668 END IF;
1669 IF p_threshold_rule_rec.alert_type IS NULL THEN
1670 x_complete_rec.alert_type := l_threshold_rule_rec.alert_type;
1671 END IF;
1672
1673 --This condition is needed for QUOTA type threshold rules.
1674 IF p_threshold_rule_rec.comparison_type = 'CONSTANT' THEN
1675 x_complete_rec.base_line := NULL;
1676 END IF;
1677 -- Note: Developers need to modify the procedure
1678 -- to handle any business specific requirements.
1679 END Complete_threshold_rule_Rec;
1680
1681 PROCEDURE Validate_threshold_rule(
1682 p_api_version_number IN NUMBER,
1683 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1684 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1685 p_threshold_rule_rec IN threshold_rule_rec_type,
1686 x_return_status OUT NOCOPY VARCHAR2,
1687 x_msg_count OUT NOCOPY NUMBER,
1688 x_msg_data OUT NOCOPY VARCHAR2
1689 )
1690 IS
1691 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Threshold_Rule';
1692 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1693 l_object_version_number NUMBER;
1694 l_threshold_rule_rec OZF_Threshold_Rule_PVT.threshold_rule_rec_type;
1695
1696 BEGIN
1697 -- Standard Start of API savepoint
1698 SAVEPOINT VALIDATE_Threshold_Rule_;
1699
1700 -- Standard call to check for call compatibility.
1701 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1702 p_api_version_number,
1703 l_api_name,
1704 G_PKG_NAME)
1705 THEN
1706 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1707 END IF;
1708
1709 -- Initialize message list if p_init_msg_list is set to TRUE.
1710 IF FND_API.to_Boolean( p_init_msg_list )
1711 THEN
1712 FND_MSG_PUB.initialize;
1713 END IF;
1714
1715 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1716 Check_threshold_rule_Items(
1717 p_threshold_rule_rec => p_threshold_rule_rec,
1718 p_validation_mode => JTF_PLSQL_API.g_create,
1719 x_return_status => x_return_status
1720 );
1721
1722 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1723 RAISE FND_API.G_EXC_ERROR;
1724 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1725 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1726 END IF;
1727 END IF;
1728
1729 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1730 Validate_threshold_rule_Rec(
1731 p_api_version_number => 1.0,
1732 p_init_msg_list => FND_API.G_FALSE,
1733 x_return_status => x_return_status,
1734 x_msg_count => x_msg_count,
1735 x_msg_data => x_msg_data,
1736 p_threshold_rule_rec => l_threshold_rule_rec);
1737
1738 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1739 RAISE FND_API.G_EXC_ERROR;
1740 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1741 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1742 END IF;
1743 END IF;
1744
1745 -- Debug Message
1746 IF G_DEBUG THEN
1747 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1748 END IF;
1749
1750 -- Standard call to get message count and if count is 1, get message info.
1751 FND_MSG_PUB.Count_And_Get
1752 (p_count => x_msg_count,
1753 p_data => x_msg_data
1754 );
1755 EXCEPTION
1756
1757 WHEN OZF_Utility_PVT.resource_locked THEN
1758 x_return_status := FND_API.g_ret_sts_error;
1759 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
1760
1761 WHEN FND_API.G_EXC_ERROR THEN
1762 ROLLBACK TO VALIDATE_Threshold_Rule_;
1763 x_return_status := FND_API.G_RET_STS_ERROR;
1764 -- Standard call to get message count and if count=1, get the message
1765 FND_MSG_PUB.Count_And_Get (
1766 p_encoded => FND_API.G_FALSE,
1767 p_count => x_msg_count,
1768 p_data => x_msg_data
1769 );
1770
1771 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1772 ROLLBACK TO VALIDATE_Threshold_Rule_;
1773 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1774 -- Standard call to get message count and if count=1, get the message
1775 FND_MSG_PUB.Count_And_Get (
1776 p_encoded => FND_API.G_FALSE,
1777 p_count => x_msg_count,
1778 p_data => x_msg_data
1779 );
1780
1781 WHEN OTHERS THEN
1782 ROLLBACK TO VALIDATE_Threshold_Rule_;
1783 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1784 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1785 THEN
1786 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1787 END IF;
1788 -- Standard call to get message count and if count=1, get the message
1789 FND_MSG_PUB.Count_And_Get (
1790 p_encoded => FND_API.G_FALSE,
1791 p_count => x_msg_count,
1792 p_data => x_msg_data
1793 );
1794 End Validate_Threshold_Rule;
1795
1796
1797 PROCEDURE Validate_threshold_rule_rec(
1798 p_api_version_number IN NUMBER,
1799 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1800 x_return_status OUT NOCOPY VARCHAR2,
1801 x_msg_count OUT NOCOPY NUMBER,
1802 x_msg_data OUT NOCOPY VARCHAR2,
1803 p_threshold_rule_rec IN threshold_rule_rec_type
1804 )
1805 IS
1806 l_return_status VARCHAR2(30);
1807
1808 BEGIN
1809 -- Initialize message list if p_init_msg_list is set to TRUE.
1810 IF FND_API.to_Boolean( p_init_msg_list )
1811 THEN
1812 FND_MSG_PUB.initialize;
1813 END IF;
1814
1815 -- Initialize API return status to SUCCESS
1816 x_return_status := FND_API.G_RET_STS_SUCCESS;
1817
1818 ------------------- check calendar ----------------------
1819 IF p_threshold_rule_rec.threshold_calendar <> fnd_api.g_miss_char
1820 OR p_threshold_rule_rec.start_period_name <> fnd_api.g_miss_char
1821 OR p_threshold_rule_rec.end_period_name <> fnd_api.g_miss_char
1822 OR p_threshold_rule_rec.start_date <> fnd_api.g_miss_date
1823 OR p_threshold_rule_rec.end_date <> fnd_api.g_miss_date THEN
1824 check_threshold_calendar(
1825 p_threshold_rule_rec.threshold_calendar
1826 ,p_threshold_rule_rec.start_period_name
1827 ,p_threshold_rule_rec.end_period_name
1828 ,p_threshold_rule_rec.start_date
1829 ,p_threshold_rule_rec.end_date
1830 ,p_threshold_rule_rec.threshold_id
1831 ,l_return_status);
1832
1833 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1834 x_return_status := l_return_status;
1835 END IF;
1836 END IF;
1837
1838
1839 -- Debug Message
1840 IF G_DEBUG THEN
1841 OZF_UTILITY_PVT.debug_message('Private API: Validate_dm_model_rec');
1842 END IF;
1843 -- Standard call to get message count and if count is 1, get message info.
1844 FND_MSG_PUB.Count_And_Get
1845 (p_count => x_msg_count,
1846 p_data => x_msg_data
1847 );
1848 END Validate_threshold_rule_Rec;
1849
1850 END OZF_Threshold_Rule_PVT;