[Home] [Help]
PACKAGE BODY: APPS.OZF_VOL_OFFR_PVT
Source
1 PACKAGE BODY OZF_Vol_Offr_PVT as
2 /* $Header: ozfvvob.pls 120.0 2005/05/31 23:29:02 appldev noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- OZF_Vol_Offr_PVT
7 -- Purpose
8 --
9 -- History
10 --
11 -- Mon Jun 14 2004:6/12 PM RSSHARMA Fixed bug # 3564470. Log debug messages depending on the Debug level
12 -- Added new method to look at the debug level and then add debug messages
13 -- NOTE
14 --
15 -- This Api is generated with Latest version of
16 -- Rosetta, where g_miss indicates NULL and
17 -- NULL indicates missing value. Rosetta Version 1.55
18 -- End of Comments
19 -- ===============================================================
20
21
22 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_Vol_Offr_PVT';
23 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvvob.pls';
24
25 OZF_DEBUG_HIGH_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
26 OZF_DEBUG_LOW_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
27
28
29 -- G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
30 -- G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
31 --
32 -- Foreward Procedure Declarations
33 --
34
35 PROCEDURE Default_Vol_Offr_Tier_Items (
36 p_vol_offr_tier_rec IN vol_offr_tier_rec_type ,
37 x_vol_offr_tier_rec OUT NOCOPY vol_offr_tier_rec_type
38 ) ;
39
40 PROCEDURE debug_message(
41 p_message_text IN VARCHAR2
42 )
43 IS
44 BEGIN
45 IF OZF_DEBUG_HIGH_ON THEN
46 OZF_UTILITY_PVT.debug_message(p_message_text);
47 END IF;
48 END debug_message;
49
50
51 -- Hint: Primary key needs to be returned.
52 -- ==============================================================================
53 -- Start of Comments
54 -- ==============================================================================
55 -- API Name
56 -- Create_Vol_Offr
57 -- Type
58 -- Private
59 -- Pre-Req
60 --
61 -- Parameters
62 --
63 -- IN
64 -- p_api_version_number IN NUMBER Required
65 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
66 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
67 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
68 -- p_vol_offr_tier_rec IN vol_offr_tier_rec_type Required
69 --
70 -- OUT
71 -- x_return_status OUT VARCHAR2
72 -- x_msg_count OUT NUMBER
73 -- x_msg_data OUT VARCHAR2
74 -- Version : Current version 1.0
75 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
76 -- and basic operation, developer must manually add parameters and business logic as necessary.
77 --
78 -- History
79 --
80 -- NOTE
81 --
82 -- End of Comments
83 -- ==============================================================================
84
85 PROCEDURE Create_Vol_Offr(
86 p_api_version_number IN NUMBER,
87 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
88 p_commit IN VARCHAR2 := FND_API.G_FALSE,
89 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
90
91 x_return_status OUT NOCOPY VARCHAR2,
92 x_msg_count OUT NOCOPY NUMBER,
93 x_msg_data OUT NOCOPY VARCHAR2,
94
95 p_vol_offr_tier_rec IN vol_offr_tier_rec_type := g_miss_vol_offr_tier_rec,
96 x_volume_offer_tiers_id OUT NOCOPY NUMBER
97 )
98
99 IS
100 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Vol_Offr';
101 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
102 l_return_status_full VARCHAR2(1);
103 l_object_version_number NUMBER := 1;
104 l_org_id NUMBER := FND_API.G_MISS_NUM;
105 l_volume_offer_tiers_id NUMBER;
106 l_dummy NUMBER;
107 CURSOR c_id IS
108 SELECT OZF_volume_offer_tiers_s.NEXTVAL
109 FROM dual;
110
111 CURSOR c_id_exists (l_id IN NUMBER) IS
112 SELECT 1
113 FROM OZF_VOLUME_OFFER_TIERS
114 WHERE volume_offer_tiers_id = l_id;
115 BEGIN
116 -- Standard Start of API savepoint
117 SAVEPOINT create_vol_offr_pvt;
118
119 -- Standard call to check for call compatibility.
120 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
121 p_api_version_number,
122 l_api_name,
123 G_PKG_NAME)
124 THEN
125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
126 END IF;
127
128
129 -- Initialize message list if p_init_msg_list is set to TRUE.
130 IF FND_API.to_Boolean( p_init_msg_list )
131 THEN
132 FND_MSG_PUB.initialize;
133 END IF;
134
135
136
137 -- Debug Message
138 debug_message('Private API: ' || l_api_name || 'start1');
139
140
141
142 -- Initialize API return status to SUCCESS
143 x_return_status := FND_API.G_RET_STS_SUCCESS;
144
145 -- Local variable initialization
146
147 IF p_vol_offr_tier_rec.volume_offer_tiers_id IS NULL OR p_vol_offr_tier_rec.volume_offer_tiers_id = FND_API.g_miss_num THEN
148 LOOP
149 l_dummy := NULL;
150 OPEN c_id;
151 FETCH c_id INTO l_volume_offer_tiers_id;
152 CLOSE c_id;
153
154 OPEN c_id_exists(l_volume_offer_tiers_id);
155 FETCH c_id_exists INTO l_dummy;
156 CLOSE c_id_exists;
157 EXIT WHEN l_dummy IS NULL;
158 END LOOP;
159 ELSE
160 l_volume_offer_tiers_id := p_vol_offr_tier_rec.volume_offer_tiers_id;
161 END IF;
162 -- =========================================================================
163 -- Validate Environment
164 -- =========================================================================
165
166 IF FND_GLOBAL.USER_ID IS NULL
167 THEN
168 OZF_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
169 RAISE FND_API.G_EXC_ERROR;
170 END IF;
171
172
173
174 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
175 THEN
176 -- Debug message
177 debug_message('Private API: Validate_Vol_Offr');
178
179 -- Invoke validation procedures
180 Validate_vol_offr(
181 p_api_version_number => 1.0,
182 p_init_msg_list => FND_API.G_FALSE,
183 p_validation_level => p_validation_level,
184 p_validation_mode => JTF_PLSQL_API.g_create,
185 p_vol_offr_tier_rec => p_vol_offr_tier_rec,
186 x_return_status => x_return_status,
187 x_msg_count => x_msg_count,
188 x_msg_data => x_msg_data);
189 END IF;
190
191 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
192 RAISE FND_API.G_EXC_ERROR;
193 END IF;
194
195
196 -- Debug Message
197 debug_message( 'Private API: Calling create table handler');
198
199 -- Invoke table handler(Ozf_Vol_Offr_Pkg.Insert_Row)
200 Ozf_Vol_Offr_Pkg.Insert_Row(
201 px_volume_offer_tiers_id => l_volume_offer_tiers_id,
202 p_qp_list_header_id => p_vol_offr_tier_rec.qp_list_header_id,
203 p_discount_type_code => p_vol_offr_tier_rec.discount_type_code,
204 p_discount => p_vol_offr_tier_rec.discount,
205 p_break_type_code => p_vol_offr_tier_rec.break_type_code,
206 p_tier_value_from => p_vol_offr_tier_rec.tier_value_from,
207 p_tier_value_to => p_vol_offr_tier_rec.tier_value_to,
208 p_volume_type => p_vol_offr_tier_rec.volume_type,
209 p_active => p_vol_offr_tier_rec.active,
210 p_uom_code => p_vol_offr_tier_rec.uom_code,
211 px_object_version_number => l_object_version_number
212 );
213
214 x_volume_offer_tiers_id := l_volume_offer_tiers_id;
215 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
216 RAISE FND_API.G_EXC_ERROR;
217 END IF;
218 --
219 -- End of API body
220 --
221
222 -- Standard check for p_commit
223 IF FND_API.to_Boolean( p_commit )
224 THEN
225 COMMIT WORK;
226 END IF;
227
228
229 -- Debug Message
230 debug_message('Private API: ' || l_api_name || 'end');
231
232
233 -- Standard call to get message count and if count is 1, get message info.
234 FND_MSG_PUB.Count_And_Get
235 (p_count => x_msg_count,
236 p_data => x_msg_data
237 );
238 EXCEPTION
239
240 WHEN OZF_Utility_PVT.resource_locked THEN
241 x_return_status := FND_API.g_ret_sts_error;
242 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
243
244 WHEN FND_API.G_EXC_ERROR THEN
245 ROLLBACK TO CREATE_Vol_Offr_PVT;
246 x_return_status := FND_API.G_RET_STS_ERROR;
247 -- Standard call to get message count and if count=1, get the message
248 FND_MSG_PUB.Count_And_Get (
249 p_encoded => FND_API.G_FALSE,
250 p_count => x_msg_count,
251 p_data => x_msg_data
252 );
253
254 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
255 ROLLBACK TO CREATE_Vol_Offr_PVT;
256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
257 -- Standard call to get message count and if count=1, get the message
258 FND_MSG_PUB.Count_And_Get (
259 p_encoded => FND_API.G_FALSE,
260 p_count => x_msg_count,
261 p_data => x_msg_data
262 );
263
264 WHEN OTHERS THEN
265 ROLLBACK TO CREATE_Vol_Offr_PVT;
266 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
267 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
268 THEN
269 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
270 END IF;
271 -- Standard call to get message count and if count=1, get the message
272 FND_MSG_PUB.Count_And_Get (
273 p_encoded => FND_API.G_FALSE,
274 p_count => x_msg_count,
275 p_data => x_msg_data
276 );
277 End Create_Vol_Offr;
278
279
280 -- ==============================================================================
281 -- Start of Comments
282 -- ==============================================================================
283 -- API Name
284 -- Update_Vol_Offr
285 -- Type
286 -- Private
287 -- Pre-Req
288 --
289 -- Parameters
290 --
291 -- IN
292 -- p_api_version_number IN NUMBER Required
293 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
294 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
295 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
296 -- p_vol_offr_tier_rec IN vol_offr_tier_rec_type Required
297 --
298 -- OUT
299 -- x_return_status OUT VARCHAR2
300 -- x_msg_count OUT NUMBER
301 -- x_msg_data OUT VARCHAR2
302 -- Version : Current version 1.0
303 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
304 -- and basic operation, developer must manually add parameters and business logic as necessary.
305 --
306 -- History
307 --
308 -- NOTE
309 --
310 -- End of Comments
311 -- ==============================================================================
312
313 PROCEDURE Update_Vol_Offr(
314 p_api_version_number IN NUMBER,
315 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
316 p_commit IN VARCHAR2 := FND_API.G_FALSE,
317 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
318
319 x_return_status OUT NOCOPY VARCHAR2,
320 x_msg_count OUT NOCOPY NUMBER,
321 x_msg_data OUT NOCOPY VARCHAR2,
322
323 p_vol_offr_tier_rec IN vol_offr_tier_rec_type,
324 x_object_version_number OUT NOCOPY NUMBER
325 )
326
327 IS
328
329
330 CURSOR c_get_vol_offr(volume_offer_tiers_id NUMBER) IS
331 SELECT *
332 FROM OZF_VOLUME_OFFER_TIERS
333 WHERE volume_offer_tiers_id = p_vol_offr_tier_rec.volume_offer_tiers_id;
334 -- Hint: Developer need to provide Where clause
335
336
337 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Vol_Offr';
338 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
339 -- Local Variables
340 l_object_version_number NUMBER := p_vol_offr_tier_rec.object_version_number;
341 l_volume_offer_tiers_id NUMBER;
342 l_ref_vol_offr_tier_rec c_get_Vol_Offr%ROWTYPE ;
343 l_tar_vol_offr_tier_rec vol_offr_tier_rec_type := P_vol_offr_tier_rec;
344 l_rowid ROWID;
345
346 BEGIN
347 -- Standard Start of API savepoint
348 SAVEPOINT update_vol_offr_pvt;
349
350 -- Standard call to check for call compatibility.
351 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
352 p_api_version_number,
353 l_api_name,
354 G_PKG_NAME)
355 THEN
356 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
357 END IF;
358
359
360 -- Initialize message list if p_init_msg_list is set to TRUE.
361 IF FND_API.to_Boolean( p_init_msg_list )
362 THEN
363 FND_MSG_PUB.initialize;
364 END IF;
368 -- Debug Message
365
366
367
369 debug_message('Private API: ' || l_api_name || 'start');
370
371
372
373 -- Initialize API return status to SUCCESS
374 x_return_status := FND_API.G_RET_STS_SUCCESS;
375
376 -- Debug Message
377 debug_message('Private API: - Open Cursor to Select');
378
379 OPEN c_get_Vol_Offr( l_tar_vol_offr_tier_rec.volume_offer_tiers_id);
380
381 FETCH c_get_Vol_Offr INTO l_ref_vol_offr_tier_rec ;
382
383 If ( c_get_Vol_Offr%NOTFOUND) THEN
384 OZF_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
385 p_token_name => 'INFO',
386 p_token_value => 'Vol_Offr') ;
387 RAISE FND_API.G_EXC_ERROR;
388 END IF;
389 -- Debug Message
390 debug_message('Private API: - Close Cursor');
391 CLOSE c_get_Vol_Offr;
392
393
394 If (l_tar_vol_offr_tier_rec.object_version_number is NULL or
395 l_tar_vol_offr_tier_rec.object_version_number = FND_API.G_MISS_NUM ) Then
396 OZF_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING',
397 p_token_name => 'COLUMN',
398 p_token_value => 'Last_Update_Date') ;
399 raise FND_API.G_EXC_ERROR;
400 End if;
401 -- Check Whether record has been changed by someone else
402 If (l_tar_vol_offr_tier_rec.object_version_number <> l_ref_vol_offr_tier_rec.object_version_number) Then
403 OZF_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED',
404 p_token_name => 'INFO',
405 p_token_value => 'Vol_Offr') ;
406 raise FND_API.G_EXC_ERROR;
407 End if;
408
409
410 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
411 THEN
412 -- Debug message
413 debug_message('Private API: Validate_Vol_Offr');
414
415 -- Invoke validation procedures
416 Validate_vol_offr(
417 p_api_version_number => 1.0,
418 p_init_msg_list => FND_API.G_FALSE,
419 p_validation_level => p_validation_level,
420 p_validation_mode => JTF_PLSQL_API.g_update,
421 p_vol_offr_tier_rec => p_vol_offr_tier_rec,
422 x_return_status => x_return_status,
423 x_msg_count => x_msg_count,
424 x_msg_data => x_msg_data);
425 END IF;
426 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
427 RAISE FND_API.G_EXC_ERROR;
428 END IF;
429 debug_message('id: '||p_vol_offr_tier_rec.volume_offer_tiers_id);
430 debug_message('ver: '||l_object_version_number);
431 -- Debug Message
432 --debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling update table handler');
433 -- Invoke table handler(Ozf_Vol_Offr_Pkg.Update_Row)
434 Ozf_Vol_Offr_Pkg.Update_Row(
435 p_volume_offer_tiers_id => p_vol_offr_tier_rec.volume_offer_tiers_id,
436 p_qp_list_header_id => p_vol_offr_tier_rec.qp_list_header_id,
437 p_discount_type_code => p_vol_offr_tier_rec.discount_type_code,
438 p_discount => p_vol_offr_tier_rec.discount,
439 p_break_type_code => p_vol_offr_tier_rec.break_type_code,
440 p_tier_value_from => p_vol_offr_tier_rec.tier_value_from,
441 p_tier_value_to => p_vol_offr_tier_rec.tier_value_to,
442 p_volume_type => p_vol_offr_tier_rec.volume_type,
443 p_active => p_vol_offr_tier_rec.active,
444 p_uom_code => p_vol_offr_tier_rec.uom_code,
445 px_object_version_number => l_object_version_number
446 );
447 debug_message('after calling tabel handler');
448 x_object_version_number := l_object_version_number;
449 --
450 -- End of API body.
451 --
452
453 -- Standard check for p_commit
454 IF FND_API.to_Boolean( p_commit )
455 THEN
456 COMMIT WORK;
457 END IF;
458
459
460 -- Debug Message
461 debug_message('Private API: ' || l_api_name || 'end');
462
463
464 -- Standard call to get message count and if count is 1, get message info.
465 FND_MSG_PUB.Count_And_Get
466 (p_count => x_msg_count,
467 p_data => x_msg_data
468 );
469 EXCEPTION
470
471 WHEN OZF_Utility_PVT.resource_locked THEN
472 x_return_status := FND_API.g_ret_sts_error;
473 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
474
475 WHEN FND_API.G_EXC_ERROR THEN
476 ROLLBACK TO UPDATE_Vol_Offr_PVT;
477 x_return_status := FND_API.G_RET_STS_ERROR;
478 -- Standard call to get message count and if count=1, get the message
479 FND_MSG_PUB.Count_And_Get (
480 p_encoded => FND_API.G_FALSE,
481 p_count => x_msg_count,
482 p_data => x_msg_data
483 );
484
485 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
486 ROLLBACK TO UPDATE_Vol_Offr_PVT;
487 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
488 -- Standard call to get message count and if count=1, get the message
489 FND_MSG_PUB.Count_And_Get (
490 p_encoded => FND_API.G_FALSE,
491 p_count => x_msg_count,
492 p_data => x_msg_data
493 );
494
495 WHEN OTHERS THEN
496 ROLLBACK TO UPDATE_Vol_Offr_PVT;
497 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
498 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
499 THEN
500 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
501 END IF;
502 -- Standard call to get message count and if count=1, get the message
503 FND_MSG_PUB.Count_And_Get (
504 p_encoded => FND_API.G_FALSE,
505 p_count => x_msg_count,
506 p_data => x_msg_data
507 );
508 End Update_Vol_Offr;
509
510
511 -- ==============================================================================
512 -- Start of Comments
513 -- ==============================================================================
514 -- API Name
515 -- Delete_Vol_Offr
516 -- Type
517 -- Private
518 -- Pre-Req
519 --
520 -- Parameters
521 --
522 -- IN
523 -- p_api_version_number IN NUMBER Required
524 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
525 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
526 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
527 -- p_volume_offer_tiers_id IN NUMBER
528 -- p_object_version_number IN NUMBER Optional Default = NULL
529 --
530 -- OUT
531 -- x_return_status OUT VARCHAR2
532 -- x_msg_count OUT NUMBER
533 -- x_msg_data OUT VARCHAR2
534 -- Version : Current version 1.0
535 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
536 -- and basic operation, developer must manually add parameters and business logic as necessary.
537 --
538 -- History
539 --
540 -- NOTE
541 --
542 -- End of Comments
543 -- ==============================================================================
544
545 PROCEDURE Delete_Vol_Offr(
546 p_api_version_number IN NUMBER,
547 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
548 p_commit IN VARCHAR2 := FND_API.G_FALSE,
549 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
550 x_return_status OUT NOCOPY VARCHAR2,
551 x_msg_count OUT NOCOPY NUMBER,
552 x_msg_data OUT NOCOPY VARCHAR2,
553 p_volume_offer_tiers_id IN NUMBER,
554 p_object_version_number IN NUMBER
555 )
556
557 IS
558 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Vol_Offr';
559 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
560 l_object_version_number NUMBER;
561
562 BEGIN
563 -- Standard Start of API savepoint
564 SAVEPOINT delete_vol_offr_pvt;
565
566 -- Standard call to check for call compatibility.
567 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
568 p_api_version_number,
569 l_api_name,
570 G_PKG_NAME)
571 THEN
572 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
573 END IF;
574
575
576 -- Initialize message list if p_init_msg_list is set to TRUE.
577 IF FND_API.to_Boolean( p_init_msg_list )
578 THEN
579 FND_MSG_PUB.initialize;
580 END IF;
581
582
583
584 -- Debug Message
585 debug_message('Private API: ' || l_api_name || 'start');
586
587
588
589 -- Initialize API return status to SUCCESS
590 x_return_status := FND_API.G_RET_STS_SUCCESS;
591
592 --
593 -- Api body
594 --
595 -- Debug Message
596 debug_message( 'Private API: Calling delete table handler');
597
598 -- Invoke table handler(Ozf_Vol_Offr_Pkg.Delete_Row)
599 Ozf_Vol_Offr_Pkg.Delete_Row(
600 p_volume_offer_tiers_id => p_volume_offer_tiers_id,
601 p_object_version_number => p_object_version_number );
602 --
603 -- End of API body
604 --
605
606 -- Standard check for p_commit
607 IF FND_API.to_Boolean( p_commit )
608 THEN
609 COMMIT WORK;
610 END IF;
611
612
613 -- Debug Message
614 debug_message('Private API: ' || l_api_name || 'end');
615
616
617 -- Standard call to get message count and if count is 1, get message info.
618 FND_MSG_PUB.Count_And_Get
619 (p_count => x_msg_count,
620 p_data => x_msg_data
621 );
622 EXCEPTION
623
624 WHEN OZF_Utility_PVT.resource_locked THEN
625 x_return_status := FND_API.g_ret_sts_error;
626 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
627
628 WHEN FND_API.G_EXC_ERROR THEN
629 ROLLBACK TO DELETE_Vol_Offr_PVT;
630 x_return_status := FND_API.G_RET_STS_ERROR;
634 p_count => x_msg_count,
631 -- Standard call to get message count and if count=1, get the message
632 FND_MSG_PUB.Count_And_Get (
633 p_encoded => FND_API.G_FALSE,
635 p_data => x_msg_data
636 );
637
638 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
639 ROLLBACK TO DELETE_Vol_Offr_PVT;
640 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
641 -- Standard call to get message count and if count=1, get the message
642 FND_MSG_PUB.Count_And_Get (
643 p_encoded => FND_API.G_FALSE,
644 p_count => x_msg_count,
645 p_data => x_msg_data
646 );
647
648 WHEN OTHERS THEN
649 ROLLBACK TO DELETE_Vol_Offr_PVT;
650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
651 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
652 THEN
653 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
654 END IF;
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 End Delete_Vol_Offr;
662
663
664
665 -- Hint: Primary key needs to be returned.
666 -- ==============================================================================
667 -- Start of Comments
668 -- ==============================================================================
669 -- API Name
670 -- Lock_Vol_Offr
671 -- Type
672 -- Private
673 -- Pre-Req
674 --
675 -- Parameters
676 --
677 -- IN
678 -- p_api_version_number IN NUMBER Required
679 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
680 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
681 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
682 -- p_vol_offr_tier_rec IN vol_offr_tier_rec_type Required
683 --
684 -- OUT
685 -- x_return_status OUT VARCHAR2
686 -- x_msg_count OUT NUMBER
687 -- x_msg_data OUT VARCHAR2
688 -- Version : Current version 1.0
689 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
690 -- and basic operation, developer must manually add parameters and business logic as necessary.
691 --
692 -- History
693 --
694 -- NOTE
695 --
696 -- End of Comments
697 -- ==============================================================================
698
699 PROCEDURE Lock_Vol_Offr(
700 p_api_version_number IN NUMBER,
701 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
702
703 x_return_status OUT NOCOPY VARCHAR2,
704 x_msg_count OUT NOCOPY NUMBER,
705 x_msg_data OUT NOCOPY VARCHAR2,
706
707 p_volume_offer_tiers_id IN NUMBER,
708 p_object_version IN NUMBER
709 )
710
711 IS
712 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Vol_Offr';
713 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
714 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
715 l_volume_offer_tiers_id NUMBER;
716
717 BEGIN
718
719 -- Debug Message
720 debug_message('Private API: ' || l_api_name || 'start');
721
722
723 -- Initialize message list if p_init_msg_list is set to TRUE.
724 IF FND_API.to_Boolean( p_init_msg_list )
725 THEN
726 FND_MSG_PUB.initialize;
727 END IF;
728
729
730
731 -- Standard call to check for call compatibility.
732 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
733 p_api_version_number,
734 l_api_name,
738 END IF;
735 G_PKG_NAME)
736 THEN
737 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
739
740
741
742 -- Initialize API return status to SUCCESS
743 x_return_status := FND_API.G_RET_STS_SUCCESS;
744
745
746 ------------------------ lock -------------------------
747 OZF_Vol_Offr_PKG.Lock_Row(l_volume_offer_tiers_id,p_object_version);
748
749
750 -------------------- finish --------------------------
751 FND_MSG_PUB.count_and_get(
752 p_encoded => FND_API.g_false,
753 p_count => x_msg_count,
754 p_data => x_msg_data);
755 debug_message(l_full_name ||': end');
756 EXCEPTION
757
758 WHEN OZF_Utility_PVT.resource_locked THEN
759 x_return_status := FND_API.g_ret_sts_error;
760 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
761
762 WHEN FND_API.G_EXC_ERROR THEN
763 ROLLBACK TO LOCK_Vol_Offr_PVT;
764 x_return_status := FND_API.G_RET_STS_ERROR;
765 -- Standard call to get message count and if count=1, get the message
766 FND_MSG_PUB.Count_And_Get (
767 p_encoded => FND_API.G_FALSE,
768 p_count => x_msg_count,
769 p_data => x_msg_data
770 );
771
772 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
773 ROLLBACK TO LOCK_Vol_Offr_PVT;
774 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
775 -- Standard call to get message count and if count=1, get the message
776 FND_MSG_PUB.Count_And_Get (
777 p_encoded => FND_API.G_FALSE,
778 p_count => x_msg_count,
779 p_data => x_msg_data
780 );
781
782 WHEN OTHERS THEN
783 ROLLBACK TO LOCK_Vol_Offr_PVT;
784 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
785 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
786 THEN
787 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
788 END IF;
789 -- Standard call to get message count and if count=1, get the message
790 FND_MSG_PUB.Count_And_Get (
791 p_encoded => FND_API.G_FALSE,
792 p_count => x_msg_count,
793 p_data => x_msg_data
794 );
795 End Lock_Vol_Offr;
796
797
798
799
800 PROCEDURE check_Vol_Offr_Tier_Uk_Items(
801 p_vol_offr_tier_rec IN vol_offr_tier_rec_type,
802 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
803 x_return_status OUT NOCOPY VARCHAR2)
804 IS
805 l_valid_flag VARCHAR2(1);
806
807 BEGIN
808 x_return_status := FND_API.g_ret_sts_success;
809 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
810 l_valid_flag := OZF_Utility_PVT.check_uniqueness(
811 'ozf_volume_offer_tiers',
812 'volume_offer_tiers_id = ''' || p_vol_offr_tier_rec.volume_offer_tiers_id ||''''
813 );
814 ELSE
815 l_valid_flag := OZF_Utility_PVT.check_uniqueness(
816 'ozf_volume_offer_tiers',
817 'volume_offer_tiers_id = ''' || p_vol_offr_tier_rec.volume_offer_tiers_id ||
821
818 ''' AND volume_offer_tiers_id <> ' || p_vol_offr_tier_rec.volume_offer_tiers_id
819 );
820 END IF;
822 IF l_valid_flag = FND_API.g_false THEN
823 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_VOLUME_OFFER_TIERS_ID_DUP');
824 x_return_status := FND_API.g_ret_sts_error;
825 END IF;
826
827 END check_Vol_Offr_Tier_Uk_Items;
828
829
830
831 PROCEDURE check_Vol_Offr_Tier_Req_Items(
832 p_vol_offr_tier_rec IN vol_offr_tier_rec_type,
833 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
834 x_return_status OUT NOCOPY VARCHAR2
835 )
836 IS
837 BEGIN
838 x_return_status := FND_API.g_ret_sts_success;
839 -- check qp_list_header_id
840 IF p_validation_mode = JTF_PLSQL_API.g_update THEN
841 IF p_vol_offr_tier_rec.qp_list_header_id = FND_API.g_miss_num
842 OR p_vol_offr_tier_rec.qp_list_header_id IS NULL
843 THEN
844 Fnd_Message.SET_NAME('OZF', 'OZF_VOL_OFF_NO_LST_HDR_ID');
845 Fnd_Msg_Pub.ADD;
846 x_return_status := FND_API.g_ret_sts_error;
847 END IF;
848 END IF;
849 -- check discount_type_code
850 IF p_vol_offr_tier_rec.discount_type_code = FND_API.g_miss_char
851 OR p_vol_offr_tier_rec.discount_type_code IS NULL
852 THEN
853 Fnd_Message.SET_NAME('OZF', 'OZF_VOL_OFF_NO_DISOUNT_TYPE');
854 Fnd_Msg_Pub.ADD;
855 x_return_status := FND_API.g_ret_sts_error;
856 END IF;
857 -- check discount
858 IF p_vol_offr_tier_rec.discount = FND_API.g_miss_num
859 OR p_vol_offr_tier_rec.discount IS NULL
860 THEN
861 Fnd_Message.SET_NAME('OZF', 'OZF_VOL_OFF_NO_DISOUNT');
862 Fnd_Msg_Pub.ADD;
863 x_return_status := FND_API.g_ret_sts_error;
864 END IF;
865 -- check break_type_code
866 IF p_vol_offr_tier_rec.break_type_code = FND_API.g_miss_char
867 OR p_vol_offr_tier_rec.break_type_code IS NULL
868 THEN
869 Fnd_Message.SET_NAME('OZF', 'OZF_VOL_OFF_NO_BREAK_TYPE');
870 Fnd_Msg_Pub.ADD;
871 x_return_status := FND_API.g_ret_sts_error;
872 END IF;
873 -- check tier_value_from
874 IF p_vol_offr_tier_rec.tier_value_from = FND_API.g_miss_num
875 OR p_vol_offr_tier_rec.tier_value_from IS NULL
876 THEN
877 Fnd_Message.SET_NAME('OZF', 'OZF_VOL_OFF_NO_VALUE_FROM');
878 Fnd_Msg_Pub.ADD;
879 x_return_status := FND_API.g_ret_sts_error;
880 END IF;
881 -- check tier_value_to
882 IF p_vol_offr_tier_rec.tier_value_to = FND_API.g_miss_num
883 OR p_vol_offr_tier_rec.tier_value_to IS NULL
884 THEN
885 Fnd_Message.SET_NAME('OZF', 'OZF_VOL_OFF_NO_VALUE_TO');
886 Fnd_Msg_Pub.ADD;
887 x_return_status := FND_API.g_ret_sts_error;
888 END IF;
889 -- check volume_type
890 IF p_vol_offr_tier_rec.volume_type = FND_API.g_miss_char
891 OR p_vol_offr_tier_rec.volume_type IS NULL
892 THEN
893 Fnd_Message.SET_NAME('OZF', 'OZF_VOL_OFF_NO_VOLUME_TYPE');
894 Fnd_Msg_Pub.ADD;
895 x_return_status := FND_API.g_ret_sts_error;
896 END IF;
897 -- check uom_code
898 IF p_vol_offr_tier_rec.volume_type = 'PRICING_ATTRIBUTE10' THEN
899 IF p_vol_offr_tier_rec.uom_code IS NULL
900 OR p_vol_offr_tier_rec.uom_code = FND_API.g_miss_char
901 THEN
902 Fnd_Message.SET_NAME('OZF', 'OZF_VOL_OFF_NO_UOM_CODE');
903 Fnd_Msg_Pub.ADD;
904 x_return_status := FND_API.g_ret_sts_error;
905 END IF;
906 RETURN;
907 END IF;
908 -- check object_version_number
909 IF p_vol_offr_tier_rec.object_version_number = FND_API.g_miss_num
910 OR p_vol_offr_tier_rec.object_version_number IS NULL
911 THEN
912 Fnd_Message.SET_NAME('OZF', 'OZF_VOL_OFF_NO_OBJECT_VERSION');
913 Fnd_Msg_Pub.ADD;
914 x_return_status := FND_API.g_ret_sts_error;
915 END IF;
916
917 END check_Vol_Offr_Tier_Req_Items;
918
919
920
921 PROCEDURE check_Vol_Offr_Tier_Fk_Items(
922 p_vol_offr_tier_rec IN vol_offr_tier_rec_type,
923 x_return_status OUT NOCOPY VARCHAR2
924 )
925 IS
926 BEGIN
927 x_return_status := FND_API.g_ret_sts_success;
928
929 IF p_vol_offr_tier_rec.qp_list_header_id <> FND_API.g_miss_num
930 AND p_vol_offr_tier_rec.qp_list_header_id IS NOT NULL THEN
931 IF
932 OZF_Utility_PVT.check_fk_exists(
933 p_table_name => 'OZF_OFFERS'
937 , p_additional_where_clause => NULL
934 , p_pk_name => 'qp_list_header_id'
935 , p_pk_value => p_vol_offr_tier_rec.qp_list_header_id
936 , p_pk_data_type => OZF_Utility_PVT.g_number
938 ) = FND_API.g_false
939 THEN
940 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_VO_BAD_LIST_HEADER_ID');
941 x_return_status := FND_API.g_ret_sts_error;
942 RETURN ;
943 END IF;
944 END IF;
945 -- Enter custom code here
946 END check_Vol_Offr_Tier_Fk_Items;
947
948
949
950 PROCEDURE check_Vol_Offr_Tier_Lkp_Items(
951 p_vol_offr_tier_rec IN vol_offr_tier_rec_type,
952 x_return_status OUT NOCOPY VARCHAR2
953 )
954 IS
955 BEGIN
956 x_return_status := FND_API.g_ret_sts_success;
957
958 IF p_vol_offr_tier_rec.discount_type_code <> FND_API.G_MISS_CHAR AND p_vol_offr_tier_rec.discount_type_code IS NOT NULL
959 THEN
960 debug_message(' LookUp type lookup code = '''|| p_vol_offr_tier_rec.discount_type_code);
961 IF OZF_Utility_PVT.Check_Lookup_Exists
962 ( p_lookup_table_name => 'OZF_LOOKUPS'
963 ,p_lookup_type => 'OZF_QP_ARITHMETIC_OPERATOR'
964 ,p_lookup_code => p_vol_offr_tier_rec.discount_type_code
965 ) = FND_API.G_FALSE then
966 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_INVALID_DISCOUNT_TYPE');
967 x_return_status := FND_API.G_RET_STS_ERROR;
968 -- If any errors happen abort API/Procedure.
969 RETURN;
970 END IF;
971 END IF;
972 IF p_vol_offr_tier_rec.break_type_code <> FND_API.G_MISS_CHAR AND p_vol_offr_tier_rec.break_type_code IS NOT NULL
973 THEN
974 debug_message(' LookUp type lookup code = '''|| p_vol_offr_tier_rec.break_type_code);
975 IF OZF_Utility_PVT.Check_Lookup_Exists
976 ( p_lookup_table_name => 'QP_LOOKUPS'
977 ,p_lookup_type => 'PRICE_BREAK_TYPE_CODE'
978 ,p_lookup_code => p_vol_offr_tier_rec.break_type_code
979 ) = FND_API.G_FALSE then
980 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_INVALID_BREAK_TYPE_TYPE');
981 x_return_status := FND_API.G_RET_STS_ERROR;
982 -- If any errors happen abort API/Procedure.
983 RETURN;
984 END IF;
985 END IF;
986 IF p_vol_offr_tier_rec.volume_type <> FND_API.G_MISS_CHAR AND p_vol_offr_tier_rec.volume_type IS NOT NULL
987 THEN
988 debug_message(' LookUp type lookup code = '''|| p_vol_offr_tier_rec.volume_type);
989 IF OZF_Utility_PVT.Check_Lookup_Exists
990 ( p_lookup_table_name => 'OZF_LOOKUPS'
991 ,p_lookup_type => 'OZF_QP_VOLUME_TYPE'
992 ,p_lookup_code => p_vol_offr_tier_rec.volume_type
993 ) = FND_API.G_FALSE then
994 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_INVALID_VOLUME_TYPE');
995 x_return_status := FND_API.G_RET_STS_ERROR;
996 -- If any errors happen abort API/Procedure.
997 RETURN;
998 END IF;
999 END IF;
1000
1001 --OZF_QP_ARITHMETIC_OPERATOR, OZF_QP_VOLUME_TYPE , QP_LOOKUPS","PRICE_BREAK_TYPE_CODE
1002 -- Enter custom code here
1003 END check_Vol_Offr_Tier_Lkp_Items;
1004
1005
1006
1007 PROCEDURE Check_Vol_Offr_Tier_Items (
1008 P_vol_offr_tier_rec IN vol_offr_tier_rec_type,
1009 p_validation_mode IN VARCHAR2,
1010 x_return_status OUT NOCOPY VARCHAR2
1011 )
1012 IS
1013 l_return_status VARCHAR2(1);
1014 BEGIN
1015
1016 l_return_status := FND_API.g_ret_sts_success;
1017 -- Check Items Uniqueness API calls
1018
1019 check_Vol_offr_tier_Uk_Items(
1020 p_vol_offr_tier_rec => p_vol_offr_tier_rec,
1021 p_validation_mode => p_validation_mode,
1022 x_return_status => x_return_status);
1023 IF x_return_status <> FND_API.g_ret_sts_success THEN
1024 l_return_status := FND_API.g_ret_sts_error;
1025 END IF;
1026
1027 -- Check Items Required/NOT NULL API calls
1028
1029 check_vol_offr_tier_req_items(
1030 p_vol_offr_tier_rec => p_vol_offr_tier_rec,
1031 p_validation_mode => p_validation_mode,
1032 x_return_status => x_return_status);
1033 IF x_return_status <> FND_API.g_ret_sts_success THEN
1034 l_return_status := FND_API.g_ret_sts_error;
1035 END IF;
1036 -- Check Items Foreign Keys API calls
1037
1038 check_vol_offr_tier_FK_items(
1039 p_vol_offr_tier_rec => p_vol_offr_tier_rec,
1040 x_return_status => x_return_status);
1041 IF x_return_status <> FND_API.g_ret_sts_success THEN
1042 l_return_status := FND_API.g_ret_sts_error;
1043 END IF;
1044 -- Check Items Lookups
1045
1046 check_Vol_Offr_Tier_Lkp_Items(
1047 p_vol_offr_tier_rec => p_vol_offr_tier_rec,
1048 x_return_status => x_return_status);
1049 IF x_return_status <> FND_API.g_ret_sts_success THEN
1050 l_return_status := FND_API.g_ret_sts_error;
1051 END IF;
1052
1053 x_return_status := l_return_status;
1054
1055 END Check_vol_offr_tier_Items;
1059
1056
1057
1058
1060
1061 PROCEDURE Complete_Vol_Offr_Tier_Rec (
1062 p_vol_offr_tier_rec IN vol_offr_tier_rec_type,
1063 x_complete_rec OUT NOCOPY vol_offr_tier_rec_type)
1064 IS
1065 l_return_status VARCHAR2(1);
1066
1067 CURSOR c_complete IS
1068 SELECT *
1069 FROM OZF_volume_offer_tiers
1070 WHERE volume_offer_tiers_id = p_vol_offr_tier_rec.volume_offer_tiers_id;
1071 l_vol_offr_tier_rec c_complete%ROWTYPE;
1072 BEGIN
1073 x_complete_rec := p_vol_offr_tier_rec;
1074
1075
1076 OPEN c_complete;
1077 FETCH c_complete INTO l_vol_offr_tier_rec;
1078 CLOSE c_complete;
1079
1080 -- volume_offer_tiers_id
1081 IF p_vol_offr_tier_rec.volume_offer_tiers_id IS NULL THEN
1082 x_complete_rec.volume_offer_tiers_id := l_vol_offr_tier_rec.volume_offer_tiers_id;
1083 END IF;
1084
1085 -- qp_list_header_id
1086 IF p_vol_offr_tier_rec.qp_list_header_id IS NULL THEN
1087 x_complete_rec.qp_list_header_id := l_vol_offr_tier_rec.qp_list_header_id;
1088 END IF;
1089
1090 -- discount_type_code
1091 IF p_vol_offr_tier_rec.discount_type_code IS NULL THEN
1092 x_complete_rec.discount_type_code := l_vol_offr_tier_rec.discount_type_code;
1093 END IF;
1094
1095 -- discount
1096 IF p_vol_offr_tier_rec.discount IS NULL THEN
1097 x_complete_rec.discount := l_vol_offr_tier_rec.discount;
1098 END IF;
1099
1100 -- break_type_code
1101 IF p_vol_offr_tier_rec.break_type_code IS NULL THEN
1102 x_complete_rec.break_type_code := l_vol_offr_tier_rec.break_type_code;
1103 END IF;
1104
1105 -- tier_value_from
1106 IF p_vol_offr_tier_rec.tier_value_from IS NULL THEN
1107 x_complete_rec.tier_value_from := l_vol_offr_tier_rec.tier_value_from;
1108 END IF;
1109
1110 -- tier_value_to
1111 IF p_vol_offr_tier_rec.tier_value_to IS NULL THEN
1112 x_complete_rec.tier_value_to := l_vol_offr_tier_rec.tier_value_to;
1113 END IF;
1114
1115 -- volume_type
1116 IF p_vol_offr_tier_rec.volume_type IS NULL THEN
1117 x_complete_rec.volume_type := l_vol_offr_tier_rec.volume_type;
1118 END IF;
1119
1120 -- active
1121 IF p_vol_offr_tier_rec.active IS NULL THEN
1122 x_complete_rec.active := l_vol_offr_tier_rec.active;
1123 END IF;
1124
1125 -- uom_code
1126 IF p_vol_offr_tier_rec.uom_code IS NULL THEN
1127 x_complete_rec.uom_code := l_vol_offr_tier_rec.uom_code;
1128 END IF;
1129
1130 -- object_version_number
1131 IF p_vol_offr_tier_rec.object_version_number IS NULL THEN
1132 x_complete_rec.object_version_number := l_vol_offr_tier_rec.object_version_number;
1133 END IF;
1134 -- Note: Developers need to modify the procedure
1135 -- to handle any business specific requirements.
1136 END Complete_Vol_Offr_Tier_Rec;
1137
1138
1139
1140
1141 PROCEDURE Default_Vol_Offr_Tier_Items ( p_vol_offr_tier_rec IN vol_offr_tier_rec_type ,
1142 x_vol_offr_tier_rec OUT NOCOPY vol_offr_tier_rec_type )
1143 IS
1144 l_vol_offr_tier_rec vol_offr_tier_rec_type := p_vol_offr_tier_rec;
1145 BEGIN
1146 -- Developers should put their code to default the record type
1147 -- e.g. IF p_campaign_rec.status_code IS NULL
1148 -- OR p_campaign_rec.status_code = FND_API.G_MISS_CHAR THEN
1149 -- l_campaign_rec.status_code := 'NEW' ;
1150 -- END IF ;
1151 --
1152 NULL ;
1153 END;
1154
1155
1156
1157
1158 PROCEDURE Validate_Vol_Offr(
1159 p_api_version_number IN NUMBER,
1160 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1161 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1162 p_vol_offr_tier_rec IN vol_offr_tier_rec_type,
1163 p_validation_mode IN VARCHAR2,
1164 x_return_status OUT NOCOPY VARCHAR2,
1165 x_msg_count OUT NOCOPY NUMBER,
1166 x_msg_data OUT NOCOPY VARCHAR2
1167 )
1168 IS
1169 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Vol_Offr';
1170 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1171 l_object_version_number NUMBER;
1172 l_vol_offr_tier_rec vol_offr_tier_rec_type;
1173
1174 BEGIN
1175 -- Standard Start of API savepoint
1176 SAVEPOINT validate_vol_offr_;
1177
1178 -- Standard call to check for call compatibility.
1179 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1180 p_api_version_number,
1181 l_api_name,
1182 G_PKG_NAME)
1183 THEN
1184 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1185 END IF;
1186
1187
1188 -- Initialize message list if p_init_msg_list is set to TRUE.
1189 IF FND_API.to_Boolean( p_init_msg_list )
1190 THEN
1191 FND_MSG_PUB.initialize;
1192 END IF;
1193
1194
1195 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1196 Check_vol_offr_tier_Items(
1197 p_vol_offr_tier_rec => p_vol_offr_tier_rec,
1198 p_validation_mode => p_validation_mode,
1199 x_return_status => x_return_status
1200 );
1201
1202 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1203 RAISE FND_API.G_EXC_ERROR;
1204 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1205 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1206 END IF;
1207 END IF;
1208
1209 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1210 Default_Vol_Offr_Tier_Items (p_vol_offr_tier_rec => p_vol_offr_tier_rec ,
1211 x_vol_offr_tier_rec => l_vol_offr_tier_rec) ;
1212 END IF ;
1213
1214
1215 Complete_vol_offr_tier_Rec(
1216 p_vol_offr_tier_rec => l_vol_offr_tier_rec,
1217 x_complete_rec => l_vol_offr_tier_rec
1218 );
1219
1220 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1221 Validate_vol_offr_tier_Rec(
1222 p_api_version_number => 1.0,
1223 p_init_msg_list => FND_API.G_FALSE,
1224 x_return_status => x_return_status,
1225 x_msg_count => x_msg_count,
1226 x_msg_data => x_msg_data,
1227 p_vol_offr_tier_rec => l_vol_offr_tier_rec);
1228
1229 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1230 RAISE FND_API.G_EXC_ERROR;
1231 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1232 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1233 END IF;
1234 END IF;
1235
1236
1237 -- Debug Message
1238 debug_message('Private API: ' || l_api_name || ' start');
1239
1240
1241
1242 -- Initialize API return status to SUCCESS
1243 x_return_status := FND_API.G_RET_STS_SUCCESS;
1244
1245
1246 -- Debug Message
1247 debug_message('Private API: ' || l_api_name || ' end');
1248
1249
1250 -- Standard call to get message count and if count is 1, get message info.
1251 FND_MSG_PUB.Count_And_Get
1252 (p_count => x_msg_count,
1253 p_data => x_msg_data
1254 );
1255 EXCEPTION
1256
1257 WHEN OZF_Utility_PVT.resource_locked THEN
1258 x_return_status := FND_API.g_ret_sts_error;
1259 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
1260
1261 WHEN FND_API.G_EXC_ERROR THEN
1262 ROLLBACK TO VALIDATE_Vol_Offr_;
1263 x_return_status := FND_API.G_RET_STS_ERROR;
1264 -- Standard call to get message count and if count=1, get the message
1265 FND_MSG_PUB.Count_And_Get (
1266 p_encoded => FND_API.G_FALSE,
1267 p_count => x_msg_count,
1268 p_data => x_msg_data
1269 );
1270
1271 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1275 FND_MSG_PUB.Count_And_Get (
1272 ROLLBACK TO VALIDATE_Vol_Offr_;
1273 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1274 -- Standard call to get message count and if count=1, get the message
1276 p_encoded => FND_API.G_FALSE,
1277 p_count => x_msg_count,
1278 p_data => x_msg_data
1279 );
1280
1281 WHEN OTHERS THEN
1282 ROLLBACK TO VALIDATE_Vol_Offr_;
1283 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1284 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1285 THEN
1286 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1287 END IF;
1288 -- Standard call to get message count and if count=1, get the message
1289 FND_MSG_PUB.Count_And_Get (
1290 p_encoded => FND_API.G_FALSE,
1291 p_count => x_msg_count,
1292 p_data => x_msg_data
1293 );
1294 End Validate_Vol_Offr;
1295
1296
1297 PROCEDURE Validate_Vol_Offr_Tier_Rec (
1298 p_api_version_number IN NUMBER,
1299 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1300 x_return_status OUT NOCOPY VARCHAR2,
1301 x_msg_count OUT NOCOPY NUMBER,
1302 x_msg_data OUT NOCOPY VARCHAR2,
1303 p_vol_offr_tier_rec IN vol_offr_tier_rec_type
1304 )
1305 IS
1306 CURSOR c_range IS
1307 SELECT TIER_VALUE_FROM, TIER_VALUE_TO
1308 FROM OZF_volume_offer_tiers
1309 WHERE QP_LIST_HEADER_ID = p_vol_offr_tier_rec.qp_list_header_id;
1310
1311 BEGIN
1312 -- Initialize message list if p_init_msg_list is set to TRUE.
1313 IF FND_API.to_Boolean( p_init_msg_list )
1314 THEN
1315 FND_MSG_PUB.initialize;
1316 END IF;
1317
1318
1319
1320 -- Initialize API return status to SUCCESS
1321 x_return_status := FND_API.G_RET_STS_SUCCESS;
1322
1323 -- Hint: Validate data
1324 -- If data not valid
1325 -- THEN
1326 -- x_return_status := FND_API.G_RET_STS_ERROR;
1327 FOR l_range IN c_range LOOP
1328 IF c_range%NOTFOUND THEN
1329 RETURN;
1330 END IF;
1331
1332 IF p_vol_offr_tier_rec.TIER_VALUE_FROM BETWEEN l_range.TIER_VALUE_FROM AND l_range.TIER_VALUE_TO
1333 OR p_vol_offr_tier_rec.TIER_VALUE_TO BETWEEN l_range.TIER_VALUE_FROM AND l_range.TIER_VALUE_TO
1334 THEN
1335 x_return_status := FND_API.G_RET_STS_ERROR;
1336 RETURN;
1337 END IF;
1338 END LOOP;
1339 -- Debug Message
1340 debug_message('Private API: Validate_dm_model_rec');
1341 -- Standard call to get message count and if count is 1, get message info.
1342 FND_MSG_PUB.Count_And_Get
1343 (p_count => x_msg_count,
1344 p_data => x_msg_data
1345 );
1346 END Validate_vol_offr_tier_Rec;
1347
1348 END OZF_Vol_Offr_PVT;