[Home] [Help]
PACKAGE BODY: APPS.OZF_QUAL_MARKET_OPTION_PVT
Source
1 PACKAGE BODY OZF_QUAL_MARKET_OPTION_PVT AS
2 /* $Header: ozfvqmob.pls 120.4 2005/08/24 06:52:04 rssharma noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_offer_Market_Options_PVT';
4 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvodlb.pls';
5
6
7
8
9 PROCEDURE check_qual_mo_uk_items(
10 p_qual_mo_rec IN qual_mo_rec_type
11 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.G_CREATE
12 , x_return_status OUT NOCOPY VARCHAR2
13 )
14 IS
15 BEGIN
16 x_return_status := FND_API.G_RET_STS_SUCCESS;
17 END check_qual_mo_uk_items;
18
19 PROCEDURE check_qual_mo_req_items(
20 p_qual_mo_rec IN qual_mo_rec_type
21 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
22 , x_return_status OUT NOCOPY VARCHAR2
23 )
24 IS
25 BEGIN
26 x_return_status := FND_API.G_RET_STS_SUCCESS;
27
28 ozf_utility_pvt.debug_message('Mode is : '||p_validation_mode || ' : '||JTF_PLSQL_API.g_create);
29 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
30 IF p_qual_mo_rec.offer_market_option_id IS NULL OR p_qual_mo_rec.offer_market_option_id = FND_API.G_MISS_NUM THEN
31 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','offer_market_option_id');
32 x_return_status := FND_API.g_ret_sts_error;
33 END IF;
34
35 IF p_qual_mo_rec.qp_qualifier_id IS NULL OR p_qual_mo_rec.qp_qualifier_id = FND_API.G_MISS_NUM THEN
36 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','qp_qualifier_id');
37 x_return_status := FND_API.g_ret_sts_error;
38 END IF;
39
40 ELSIF p_validation_mode = JTF_PLSQL_API.g_update THEN
41
42 IF p_qual_mo_rec.qualifier_market_option_id = FND_API.G_MISS_NUM THEN
43 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','qualifier_market_option_id');
44 x_return_status := FND_API.g_ret_sts_error;
45 END IF;
46
47 IF p_qual_mo_rec.offer_market_option_id = FND_API.G_MISS_NUM THEN
48 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','offer_market_option_id');
49 x_return_status := FND_API.g_ret_sts_error;
50 END IF;
51
52 IF p_qual_mo_rec.object_version_number = FND_API.G_MISS_NUM THEN
53 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','object_version_number');
54 x_return_status := FND_API.g_ret_sts_error;
55 END IF;
56
57 IF p_qual_mo_rec.qp_qualifier_id = FND_API.G_MISS_NUM THEN
58 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','qp_qualifier_id');
59 x_return_status := FND_API.g_ret_sts_error;
60 END IF;
61 END IF;
62
63 END check_qual_mo_req_items;
64
65 PROCEDURE check_qual_mo_fk_items(
66 p_qual_mo_rec IN qual_mo_rec_type
67 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
68 , x_return_status OUT NOCOPY VARCHAR2
69 )
70 IS
71
72
73 BEGIN
74 x_return_status := FND_API.G_RET_STS_SUCCESS;
75 IF p_qual_mo_rec.offer_market_option_id IS NOT NULL AND p_qual_mo_rec.offer_market_option_id <> FND_API.G_MISS_NUM THEN
76 IF p_qual_mo_rec.offer_market_option_id <> -1 THEN
77 IF ozf_utility_pvt.check_fk_exists('ozf_offr_market_options','offer_market_option_id',to_char(p_qual_mo_rec.offer_market_option_id)) = FND_API.g_false
78 THEN
79 OZF_Utility_PVT.Error_Message('OZF_OFFR_MOID_INV');
80 x_return_status := FND_API.g_ret_sts_error;
81 END IF;
82 END IF;
83 END IF;
84
85 IF p_qual_mo_rec.qp_qualifier_id IS NOT NULL AND p_qual_mo_rec.qp_qualifier_id <> FND_API.G_MISS_NUM THEN
86 IF ozf_utility_pvt.check_fk_exists('qp_qualifiers','qualifier_id',to_char(p_qual_mo_rec.qp_qualifier_id)) = FND_API.g_false
87 THEN
88 OZF_Utility_PVT.Error_Message('OZF_OFFR_INV_QP_QUAL');
89 x_return_status := FND_API.g_ret_sts_error;
90 END IF;
91 END IF;
92
93 END check_qual_mo_fk_items;
94
95 PROCEDURE check_qual_mo_lkup_items(
96 p_qual_mo_rec IN qual_mo_rec_type
97 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
98 , x_return_status OUT NOCOPY VARCHAR2
99 )
100 IS
101 BEGIN
102 x_return_status := FND_API.G_RET_STS_SUCCESS;
103 END check_qual_mo_lkup_items;
104
105 PROCEDURE check_qual_mo_attr(
106 p_qual_mo_rec IN qual_mo_rec_type
107 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.G_CREATE
108 , x_return_status OUT NOCOPY VARCHAR2
109 )
110 IS
111 BEGIN
112 x_return_status := FND_API.G_RET_STS_SUCCESS;
113 END check_qual_mo_attr;
114
115 PROCEDURE check_qual_mo_inter_attr(
116 p_qual_mo_rec IN qual_mo_rec_type
117 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.G_CREATE
118 , x_return_status OUT NOCOPY VARCHAR2
119 )
120 IS
121 BEGIN
122 x_return_status := FND_API.G_RET_STS_SUCCESS;
123 END check_qual_mo_inter_attr;
124
125 PROCEDURE check_qual_mo_entity(
126 p_qual_mo_rec IN qual_mo_rec_type
127 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.G_CREATE
128 , x_return_status OUT NOCOPY VARCHAR2
129 )
130 IS
131 BEGIN
132 x_return_status := FND_API.G_RET_STS_SUCCESS;
133 END check_qual_mo_entity;
134
135
136
137
138
139 PROCEDURE Check_qual_mo_Items(
140 p_qual_mo_rec IN qual_mo_rec_type
141 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
142 , x_return_status OUT NOCOPY VARCHAR2
143 )
144 IS
145 BEGIN
146 x_return_status := FND_API.G_RET_STS_SUCCESS;
147
148 check_qual_mo_req_items(
149 p_qual_mo_rec => p_qual_mo_rec
150 , p_validation_mode => p_validation_mode
151 , x_return_status => x_return_status
152 );
153 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
154 RAISE FND_API.G_EXC_ERROR;
155 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
156 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
157 END IF;
158
159 check_qual_mo_uk_items(
160 p_qual_mo_rec => p_qual_mo_rec,
161 p_validation_mode => p_validation_mode,
162 x_return_status => x_return_status
163 );
164 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
165 RAISE FND_API.G_EXC_ERROR;
166 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
167 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
168 END IF;
169
170
171 check_qual_mo_fk_items(
172 p_qual_mo_rec => p_qual_mo_rec
173 , p_validation_mode => p_validation_mode
174 , x_return_status => x_return_status
175 );
176 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
177 RAISE FND_API.G_EXC_ERROR;
178 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
179 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
180 END IF;
181 check_qual_mo_lkup_items(
182 p_qual_mo_rec => p_qual_mo_rec
183 , p_validation_mode => p_validation_mode
184 , x_return_status => x_return_status
185 );
186 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
187 RAISE FND_API.G_EXC_ERROR;
188 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
189 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
190 END IF;
191 check_qual_mo_attr(
192 p_qual_mo_rec => p_qual_mo_rec
193 , p_validation_mode => p_validation_mode
194 , x_return_status => x_return_status
195 );
196 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
197 RAISE FND_API.G_EXC_ERROR;
198 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
199 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
200 END IF;
201
202 check_qual_mo_inter_attr(
203 p_qual_mo_rec => p_qual_mo_rec
204 , p_validation_mode => p_validation_mode
205 , x_return_status => x_return_status
206 );
207 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
208 RAISE FND_API.G_EXC_ERROR;
209 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
210 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
211 END IF;
212 check_qual_mo_entity(
213 p_qual_mo_rec => p_qual_mo_rec
214 , p_validation_mode => p_validation_mode
215 , x_return_status => x_return_status
216 );
217 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
218 RAISE FND_API.G_EXC_ERROR;
219 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
220 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
221 END IF;
222
223 END Check_qual_mo_Items;
224
225
226
227 PROCEDURE validate_qual_market_options
228 (
229 p_api_version_number NUMBER
230 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
231 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
232 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
233 , x_return_status OUT NOCOPY VARCHAR2
234 , x_msg_count OUT NOCOPY VARCHAR2
235 , x_msg_data OUT NOCOPY VARCHAR2
236 , p_qual_mo_rec IN qual_mo_rec_type
237 )
238 IS
239 l_api_name CONSTANT VARCHAR2(30) := 'validate_qual_market_options';
240 l_api_version_number CONSTANT NUMBER := 1.0;
241 l_object_version_number NUMBER;
242 l_qual_mo_rec qual_mo_rec_type;
243 BEGIN
244 -- Standard Start of API savepoint
245 SAVEPOINT validate_qual_mkt_options_pvt;
246
247 -- Standard call to check for call compatibility.
248 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
249 p_api_version_number,
250 l_api_name,
251 G_PKG_NAME)
252 THEN
253 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
254 END IF;
255 -- Initialize message list if p_init_msg_list is set to TRUE.
256 IF FND_API.to_Boolean( p_init_msg_list )
257 THEN
258 FND_MSG_PUB.initialize;
259 END IF;
260
261 -- Debug Message
262 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
263
264 -- Initialize API return status to SUCCESS
265 x_return_status := FND_API.G_RET_STS_SUCCESS;
266 -- check
267 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
268 Check_qual_mo_Items(
269 p_qual_mo_rec => p_qual_mo_rec,
270 p_validation_mode => p_validation_mode,
271 x_return_status => x_return_status
272 );
273 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
274 RAISE FND_API.G_EXC_ERROR;
275 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
276 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
277 END IF;
278 END IF;
279 IF p_validation_mode = JTF_PLSQL_API.g_update THEN
280 /* Complete_mo_Rec(
281 p_vo_disc_rec => l_vo_disc_rec,
282 x_complete_rec => l_vo_disc_rec
283 );
284 */
285 -- END IF;
286 /* IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
287 Validate_vo_discounts_rec(
288 p_api_version_number => 1.0,
289 p_init_msg_list => FND_API.G_FALSE,
290 x_return_status => x_return_status,
291 x_msg_count => x_msg_count,
292 x_msg_data => x_msg_data,
293 p_vo_disc_rec => l_vo_disc_rec);
294 */
295 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
296 RAISE FND_API.G_EXC_ERROR;
297 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
298 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
299 END IF;
300 END IF;
301 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'Return status is : '|| x_return_status);
302
303 -- Debug Message
304 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
305
306 -- collect message
307
308 -- Standard call to get message count and if count is 1, get message info.
309 FND_MSG_PUB.Count_And_Get
310 (p_count => x_msg_count,
311 p_data => x_msg_data
312 );
313 -- exception
314
315 EXCEPTION
316
317 WHEN OZF_Utility_PVT.resource_locked THEN
318 x_return_status := FND_API.g_ret_sts_error;
319 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
320
321 WHEN FND_API.G_EXC_ERROR THEN
322 ROLLBACK TO validate_qual_mkt_options_pvt;
323 x_return_status := FND_API.G_RET_STS_ERROR;
324 -- Standard call to get message count and if count=1, get the message
325 FND_MSG_PUB.Count_And_Get (
326 p_encoded => FND_API.G_FALSE,
327 p_count => x_msg_count,
328 p_data => x_msg_data
329 );
330
331 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
332 ROLLBACK TO validate_qual_mkt_options_pvt;
333 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
334 -- Standard call to get message count and if count=1, get the message
335 FND_MSG_PUB.Count_And_Get (
336 p_encoded => FND_API.G_FALSE,
337 p_count => x_msg_count,
338 p_data => x_msg_data
339 );
340
341 WHEN OTHERS THEN
342 ROLLBACK TO validate_qual_mkt_options_pvt;
343 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
344 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
345 THEN
346 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
347 END IF;
348 -- Standard call to get message count and if count=1, get the message
349 FND_MSG_PUB.Count_And_Get (
350 p_encoded => FND_API.G_FALSE,
351 p_count => x_msg_count,
352 p_data => x_msg_data
353 );
354
355 END validate_qual_market_options;
356
357
358 -- ==============================================================================
359 -- Start of Comments
360 -- ==============================================================================
361 -- API Name
362 -- Create_qual_market_options
363 -- Type
364 -- Private
365 -- Pre-Req
366 -- Parameters
367 --
368 -- IN
369 -- p_api_version_number IN NUMBER Required
370 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
371 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
372 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
373 -- p_qual_mo_rec IN qual_mo_rec_type
374 -- OUT NOCOPY
375 -- x_return_status OUT NOCOPY VARCHAR2
376 -- x_msg_count OUT NOCOPY NUMBER
377 -- x_msg_data OUT NOCOPY VARCHAR2
378 -- x_vo_market_option_id OUT NOCOPY NUMBER. Market Option id of the market option just created
379 -- Version : Current version 1.0
380 --
381 -- History
382 -- Mon Jun 20 2005:3/33 PM RSSHARMA Created
383 --
384 -- Description
385 -- : Method to Create New Market Options.
386 -- End of Comments
387 -- ==============================================================================
388
389 PROCEDURE Create_qual_market_options(
390 p_api_version_number IN NUMBER,
391 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
392 p_commit IN VARCHAR2 := FND_API.G_FALSE,
393 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
394
395 x_return_status OUT NOCOPY VARCHAR2,
396 x_msg_count OUT NOCOPY NUMBER,
397 x_msg_data OUT NOCOPY VARCHAR2,
398
399 p_qual_mo_rec IN qual_mo_rec_type ,
400 x_qual_market_option_id OUT NOCOPY NUMBER
401 )
402 IS
403 l_qual_mo_rec qual_mo_rec_type;
404 l_api_version_number CONSTANT NUMBER := 1.0;
405 l_api_name CONSTANT VARCHAR2(30) := 'Create_qual_market_options';
406 l_qual_market_option_id NUMBER;
407 l_dummy NUMBER;
408 CURSOR c_id IS
409 SELECT ozf_qualifier_market_option_s.NEXTVAL
410 FROM dual;
411 CURSOR c_id_exists (l_id IN NUMBER) IS
412 SELECT 1
413 FROM ozf_qualifier_market_option
414 WHERE qualifier_market_option_id = l_id;
415 l_object_version_number NUMBER;
416 BEGIN
417 --initialize
418 -- Standard Start of API savepoint
419 SAVEPOINT Create_qual_market_options_pvt;
420
421 -- Standard call to check for call compatibility.
422 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
423 p_api_version_number,
424 l_api_name,
425 G_PKG_NAME)
426 THEN
427 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
428 END IF;
429
430 -- Initialize message list if p_init_msg_list is set to TRUE.
431 IF FND_API.to_Boolean( p_init_msg_list )
432 THEN
433 FND_MSG_PUB.initialize;
434 END IF;
435
436 -- Debug Message
437 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
438
439 -- Initialize API return status to SUCCESS
440 x_return_status := FND_API.G_RET_STS_SUCCESS;
441
442 -- =========================================================================
443 -- Validate Environment
444 -- =========================================================================
445
446 IF FND_GLOBAL.USER_ID IS NULL
447 THEN
448 OZF_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
449 RAISE FND_API.G_EXC_ERROR;
450 END IF;
451
452 l_qual_mo_rec := p_qual_mo_rec;
453
454
455 IF p_qual_mo_rec.qualifier_market_option_id IS NULL OR p_qual_mo_rec.qualifier_market_option_id = FND_API.g_miss_num THEN
456 LOOP
457 l_dummy := NULL;
458 OPEN c_id;
459 FETCH c_id INTO l_qual_market_option_id;
460 CLOSE c_id;
461
462 OPEN c_id_exists(l_qual_market_option_id);
463 FETCH c_id_exists INTO l_dummy;
464 CLOSE c_id_exists;
465 EXIT WHEN l_dummy IS NULL;
466 END LOOP;
467 ELSE
468 l_qual_market_option_id := p_qual_mo_rec.qualifier_market_option_id;
469 END IF;
470 -- validate
471
472 validate_qual_market_options
473 (
474 p_api_version_number => p_api_version_number
475 , p_init_msg_list => p_init_msg_list
476 , p_validation_level => p_validation_level
477 , p_validation_mode => JTF_PLSQL_API.g_create
478 , x_return_status => x_return_status
479 , x_msg_count => x_msg_count
480 , x_msg_data => x_msg_data
481 , p_qual_mo_rec => l_qual_mo_rec
482 );
483
484 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
485 RAISE FND_API.G_EXC_ERROR;
486 END IF;
487 -- insert
488 OZF_QUAL_MARKET_OPTION_PKG.Insert_Row(
489 px_qualifier_market_option_id => l_qual_market_option_id
490 , p_offer_market_option_id => l_qual_mo_rec.offer_market_option_id
491 , p_qp_qualifier_id => l_qual_mo_rec.qp_qualifier_id
492 , px_object_version_number => l_object_version_number
493 , p_creation_date => SYSDATE
494 , p_created_by => FND_GLOBAL.USER_ID
495 , p_last_updated_by => FND_GLOBAL.USER_ID
496 , p_last_update_date => SYSDATE
497 , p_last_update_login => FND_GLOBAL.conc_login_id
498 );
499 -- commit
500 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
501 RAISE FND_API.G_EXC_ERROR;
502 END IF;
503
504 x_qual_market_option_id := l_qual_market_option_id;
505
506 IF FND_API.to_Boolean( p_commit )
507 THEN
508 COMMIT WORK;
509 END IF;
510 -- Debug Message
511 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'Return status is : '|| x_return_status);
512 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
513 -- Standard call to get message count and if count is 1, get message info.
514
515 FND_MSG_PUB.Count_And_Get
516 (p_count => x_msg_count,
517 p_data => x_msg_data
518 );
519
520 -- exception
521 EXCEPTION
522 WHEN FND_API.G_EXC_ERROR THEN
523 ROLLBACK TO Create_qual_market_options_pvt;
524 x_return_status := FND_API.G_RET_STS_ERROR;
525 -- Standard call to get message count and if count=1, get the message
526 FND_MSG_PUB.Count_And_Get (
527 p_encoded => FND_API.G_FALSE,
528 p_count => x_msg_count,
529 p_data => x_msg_data
530 );
531
532 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
533 ROLLBACK TO Create_qual_market_options_pvt;
534 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
535 -- Standard call to get message count and if count=1, get the message
536 FND_MSG_PUB.Count_And_Get (
537 p_encoded => FND_API.G_FALSE,
538 p_count => x_msg_count,
539 p_data => x_msg_data
540 );
541
542 WHEN OTHERS THEN
543 ROLLBACK TO Create_qual_market_options_pvt;
544 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
545 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
546 THEN
547 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
548 END IF;
549 -- Standard call to get message count and if count=1, get the message
550 FND_MSG_PUB.Count_And_Get (
551 p_encoded => FND_API.G_FALSE,
552 p_count => x_msg_count,
553 p_data => x_msg_data
554 );
555
556 END Create_qual_market_options;
557
558 PROCEDURE update_qual_market_options(
559 p_api_version_number IN NUMBER
560 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
561 , p_commit IN VARCHAR2 := FND_API.G_FALSE
562 , p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL
563
564 , x_return_status OUT NOCOPY VARCHAR2
565 , x_msg_count OUT NOCOPY VARCHAR2
566 , x_msg_data OUT NOCOPY VARCHAR2
567
568 , p_qual_mo_rec IN qual_mo_rec_type
569 )
570 IS
571 CURSOR c_get_qual_mo(p_qualifier_market_option_id NUMBER, p_object_version_number NUMBER) IS
572 SELECT *
573 FROM ozf_qualifier_market_option
574 WHERE qualifier_market_option_id = p_qualifier_market_option_id
575 AND object_version_number = p_object_version_number;
576 -- Hint: Developer need to provide Where clause
577
578 l_api_name CONSTANT VARCHAR2(30) := 'update_qual_market_options';
579 l_api_version_number CONSTANT NUMBER := 1.0;
580 -- Local Variables
581 l_object_version_number NUMBER;
582 l_qual_market_option_id NUMBER;
583 l_ref_qual_mo_rec c_get_qual_mo%ROWTYPE ;
584 l_tar_qual_mo_rec qual_mo_rec_type := p_qual_mo_rec ;
585 l_rowid ROWID;
586 BEGIN
587 --initialize
588 -- Standard Start of API savepoint
589 SAVEPOINT update_qual_market_options_pvt;
590 -- Standard call to check for call compatibility.
591 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
592 p_api_version_number,
593 l_api_name,
594 G_PKG_NAME)
595 THEN
596 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
597 END IF;
598 -- Initialize message list if p_init_msg_list is set to TRUE.
599 IF FND_API.to_Boolean( p_init_msg_list )
600 THEN
601 FND_MSG_PUB.initialize;
602 END IF;
603 -- Debug Message
604 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
605
606 -- Initialize API return status to SUCCESS
607 x_return_status := FND_API.G_RET_STS_SUCCESS;
608
609 OPEN c_get_qual_mo( l_tar_qual_mo_rec.qualifier_market_option_id,l_tar_qual_mo_rec.object_version_number);
610 FETCH c_get_qual_mo INTO l_ref_qual_mo_rec ;
611 If ( c_get_qual_mo%NOTFOUND) THEN
612 OZF_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET'
613 , p_token_name => 'INFO'
614 , p_token_value => 'OZF_QUAL_MARKET_OPTIONS') ;
615 RAISE FND_API.G_EXC_ERROR;
616 END IF;
617 CLOSE c_get_qual_mo;
618
619 If (l_tar_qual_mo_rec.object_version_number is NULL or
620 l_tar_qual_mo_rec.object_version_number = FND_API.G_MISS_NUM ) Then
621 OZF_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING'
622 , p_token_name => 'COLUMN'
623 , p_token_value => 'Last_Update_Date') ;
624 RAISE FND_API.G_EXC_ERROR;
625 End if;
626 -- Check Whether record has been changed by someone else
627 If (l_tar_qual_mo_rec.object_version_number <> l_ref_qual_mo_rec.object_version_number) Then
628 OZF_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED'
629 , p_token_name => 'INFO'
630 , p_token_value => 'Ozf_Market_Options') ;
631 RAISE FND_API.G_EXC_ERROR;
632 End if;
633 -- validate
634 validate_qual_market_options
635 (
636 p_api_version_number => p_api_version_number
637 , p_init_msg_list => p_init_msg_list
638 , p_validation_level => p_validation_level
639 , p_validation_mode => JTF_PLSQL_API.g_update
640 , x_return_status => x_return_status
641 , x_msg_count => x_msg_count
642 , x_msg_data => x_msg_data
643 , p_qual_mo_rec => p_qual_mo_rec
644 );
645 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
646 RAISE FND_API.G_EXC_ERROR;
647 END IF;
648 -- update
649 OZF_QUAL_MARKET_OPTION_PKG.Update_Row(
650 p_qualifier_market_option_id => p_qual_mo_rec.qualifier_market_option_id
651 , p_offer_market_option_id => p_qual_mo_rec.offer_market_option_id
652 , p_qp_qualifier_id => p_qual_mo_rec.qp_qualifier_id
653 , p_object_version_number => p_qual_mo_rec.object_version_number
654 , p_last_updated_by => FND_GLOBAL.USER_ID
655 , p_last_update_date => SYSDATE
656 , p_last_update_login => FND_GLOBAL.conc_login_id
657 );
658 -- commit;
659
660 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
661 RAISE FND_API.G_EXC_ERROR;
662 END IF;
663
664
665 IF FND_API.to_Boolean( p_commit )
666 THEN
667 COMMIT WORK;
668 END IF;
669 -- Debug Message
670 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'Return status is : '|| x_return_status);
671 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
672 -- Standard call to get message count and if count is 1, get message info.
673
674 FND_MSG_PUB.Count_And_Get
675 (p_count => x_msg_count,
676 p_data => x_msg_data
677 );
678 --exception
679
680 EXCEPTION
681 WHEN FND_API.G_EXC_ERROR THEN
682 ROLLBACK TO update_qual_market_options_pvt;
683 x_return_status := FND_API.G_RET_STS_ERROR;
684 -- Standard call to get message count and if count=1, get the message
685 FND_MSG_PUB.Count_And_Get (
686 p_encoded => FND_API.G_FALSE,
687 p_count => x_msg_count,
688 p_data => x_msg_data
689 );
690
691 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
692 ROLLBACK TO update_qual_market_options_pvt;
693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
694 -- Standard call to get message count and if count=1, get the message
695 FND_MSG_PUB.Count_And_Get (
696 p_encoded => FND_API.G_FALSE,
697 p_count => x_msg_count,
698 p_data => x_msg_data
699 );
700
701 WHEN OTHERS THEN
702 ROLLBACK TO update_qual_market_options_pvt;
703 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
704 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
705 THEN
706 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
707 END IF;
708 -- Standard call to get message count and if count=1, get the message
709 FND_MSG_PUB.Count_And_Get (
710 p_encoded => FND_API.G_FALSE,
711 p_count => x_msg_count,
712 p_data => x_msg_data
713 );
714
715 END update_qual_market_options;
716
717
718 PROCEDURE Delete_qual_market_options(
719 p_api_version_number IN NUMBER
720 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
721 , p_commit IN VARCHAR2 := FND_API.G_FALSE
722 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
723 , x_return_status OUT NOCOPY VARCHAR2
724 , x_msg_count OUT NOCOPY VARCHAR2
725 , x_msg_data OUT NOCOPY VARCHAR2
726 , p_qualifier_market_option_id IN NUMBER
727 , p_object_version_number IN NUMBER
728 )
729 IS
730 l_api_name CONSTANT VARCHAR2(30) := 'Delete_qual_market_options';
731 l_api_version_number CONSTANT NUMBER := 1.0;
732 l_object_version_number NUMBER;
733 BEGIN
734 --initialize
735 -- Standard Start of API savepoint
736 SAVEPOINT Delete_qual_market_options_PVT;
737
738 -- Standard call to check for call compatibility.
739 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
740 p_api_version_number,
741 l_api_name,
742 G_PKG_NAME)
743 THEN
744 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
745 END IF;
746
747 -- Initialize message list if p_init_msg_list is set to TRUE.
748 IF FND_API.to_Boolean( p_init_msg_list )
749 THEN
750 FND_MSG_PUB.initialize;
751 END IF;
752
753 -- Initialize API return status to SUCCESS
754 x_return_status := FND_API.G_RET_STS_SUCCESS;
755
756 -- delete
757 OZF_QUAL_MARKET_OPTION_PKG.Delete_Row(
758 p_qualifier_market_option_id => p_qualifier_market_option_id
759 , p_object_version_number => p_object_version_number
760 );
761 -- commit
762 IF FND_API.to_Boolean( p_commit )
763 THEN
764 COMMIT WORK;
765 END IF;
766
767 -- Standard call to get message count and if count is 1, get message info.
768 FND_MSG_PUB.Count_And_Get
769 (p_count => x_msg_count,
770 p_data => x_msg_data
771 );
772 -- exception
773
774 EXCEPTION
775
776 WHEN OZF_Utility_PVT.resource_locked THEN
777 x_return_status := FND_API.g_ret_sts_error;
778 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
779
780 WHEN FND_API.G_EXC_ERROR THEN
781 ROLLBACK TO Delete_qual_market_options_PVT;
782 x_return_status := FND_API.G_RET_STS_ERROR;
783 -- Standard call to get message count and if count=1, get the message
784 FND_MSG_PUB.Count_And_Get (
785 p_encoded => FND_API.G_FALSE,
786 p_count => x_msg_count,
787 p_data => x_msg_data
788 );
789
790 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
791 ROLLBACK TO Delete_qual_market_options_PVT;
792 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
793 -- Standard call to get message count and if count=1, get the message
794 FND_MSG_PUB.Count_And_Get (
795 p_encoded => FND_API.G_FALSE,
796 p_count => x_msg_count,
797 p_data => x_msg_data
798 );
799
800 WHEN OTHERS THEN
801 ROLLBACK TO Delete_qual_market_options_PVT;
802 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
803 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
804 THEN
805 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
806 END IF;
807 -- Standard call to get message count and if count=1, get the message
808 FND_MSG_PUB.Count_And_Get (
809 p_encoded => FND_API.G_FALSE,
810 p_count => x_msg_count,
811 p_data => x_msg_data
812 );
813
814 END Delete_qual_market_options;
815
816 END OZF_QUAL_MARKET_OPTION_PVT;
817