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