[Home] [Help]
PACKAGE BODY: APPS.OZF_MO_PRESET_TIERS_PVT
Source
1 PACKAGE BODY OZF_MO_PRESET_TIERS_PVT AS
2 /* $Header: ozfvmoptb.pls 120.4 2005/08/25 04:34:57 rssharma noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- OZF_MO_PRESET_TIERS_PKG
7 -- Purpose
8 --
9 -- History
10 -- Mon Jul 11 2005:6/29 PM RSSHARMA Created
11 -- NOTE
12 --
13 -- This Api is generated with Latest version of
14 -- Rosetta, where g_miss indicates NULL and
15 -- NULL indicates missing value. Rosetta Version 1.55
16 -- End of Comments
17 -- ===============================================================
18
19 G_PKG_NAME VARCHAR2(30) := 'OZF_MO_PRESET_TIERS_PVT';
20 G_FILE_NAME VARCHAR2(15) := 'ozfvmoptb.pls';
21
22 PROCEDURE check_preset_tiers_req_items
23 (
24 p_preset_tier_rec IN mo_preset_rec_type
25 , p_validation_mode IN VARCHAR2
26 , x_return_status OUT NOCOPY VARCHAR2
27 )
28 IS
29 BEGIN
30 x_return_status := FND_API.G_RET_STS_SUCCESS;
31 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
32 IF p_preset_tier_rec.offer_market_option_id IS NULL OR p_preset_tier_rec.offer_market_option_id = FND_API.G_MISS_NUM THEN
33 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','offer_market_option_id');
34 x_return_status := FND_API.g_ret_sts_error;
35 return;
36 END IF;
37 IF p_preset_tier_rec.pbh_offer_discount_id IS NULL OR p_preset_tier_rec.pbh_offer_discount_id = FND_API.G_MISS_NUM THEN
38 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','pbh_offer_discount_id');
39 x_return_status := FND_API.g_ret_sts_error;
40 return;
41 END IF;
42 IF p_preset_tier_rec.dis_offer_discount_id IS NULL OR p_preset_tier_rec.dis_offer_discount_id = FND_API.G_MISS_NUM THEN
43 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','dis_offer_discount_id');
44 x_return_status := FND_API.g_ret_sts_error;
45 return;
46 END IF;
47 ELSE
48 IF p_preset_tier_rec.market_preset_tier_id = FND_API.G_MISS_NUM THEN
49 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','market_preset_tier_id');
50 x_return_status := FND_API.g_ret_sts_error;
51 return;
52 END IF;
53 IF p_preset_tier_rec.offer_market_option_id = FND_API.G_MISS_NUM THEN
54 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','offer_market_option_id');
55 x_return_status := FND_API.g_ret_sts_error;
56 return;
57 END IF;
58 IF p_preset_tier_rec.pbh_offer_discount_id = FND_API.G_MISS_NUM THEN
59 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','pbh_offer_discount_id');
60 x_return_status := FND_API.g_ret_sts_error;
61 return;
62 END IF;
63 IF p_preset_tier_rec.dis_offer_discount_id = FND_API.G_MISS_NUM THEN
64 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','dis_offer_discount_id');
65 x_return_status := FND_API.g_ret_sts_error;
66 return;
67 END IF;
68 IF p_preset_tier_rec.object_version_number = FND_API.G_MISS_NUM THEN
69 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','object_version_number');
70 x_return_status := FND_API.g_ret_sts_error;
71 return;
72 END IF;
73 END IF;
74
75 END check_preset_tiers_req_items;
76
77 PROCEDURE check_preset_tiers_uk_items
78 (
79 p_preset_tier_rec IN mo_preset_rec_type
80 , p_validation_mode IN VARCHAR2
81 , x_return_status OUT NOCOPY VARCHAR2
82 )
83 IS
84 BEGIN
85 x_return_status := FND_API.G_RET_STS_SUCCESS;
86 IF p_validation_mode = JTF_PLSQL_API.G_CREATE THEN
87 IF p_preset_tier_rec.market_preset_tier_id IS NOT NULL AND p_preset_tier_rec.market_preset_tier_id <> FND_API.G_MISS_NUM THEN
88 IF ozf_utility_pvt.check_uniqueness('ozf_market_preset_tiers','market_preset_tier_id = '||p_preset_tier_rec.market_preset_tier_id) = FND_API.g_false THEN
89 OZF_Utility_PVT.Error_Message('OZF_MO_PRESET_ID_DUP');
90 x_return_status := FND_API.g_ret_sts_error;
91 return;
92 END IF;
93 END IF;
94 END IF;
95
96 IF p_validation_mode = JTF_PLSQL_API.G_UPDATE THEN
97 IF
98 (
99 p_preset_tier_rec.offer_market_option_id IS NOT NULL AND p_preset_tier_rec.offer_market_option_id <> FND_API.G_MISS_NUM
100 )
101 AND
102 (
103 p_preset_tier_rec.pbh_offer_discount_id IS NOT NULL AND p_preset_tier_rec.pbh_offer_discount_id <> FND_API.G_MISS_NUM
104 )
105 AND
106 (
107 p_preset_tier_rec.dis_offer_discount_id IS NOT NULL AND p_preset_tier_rec.dis_offer_discount_id <> FND_API.G_MISS_NUM
108 )
109 AND
110 (
111 p_preset_tier_rec.market_preset_tier_id IS NOT NULL AND p_preset_tier_rec.market_preset_tier_id <> FND_API.G_MISS_NUM
112 )
113 THEN
114 IF ozf_utility_pvt.check_uniqueness('ozf_market_preset_tiers'
115 ,'offer_market_option_id = '||p_preset_tier_rec.offer_market_option_id
116 ||' AND pbh_offer_discount_id = '|| p_preset_tier_rec.pbh_offer_discount_id
117 || ' AND dis_offer_discount_id = '||p_preset_tier_rec.dis_offer_discount_id
118 || ' AND market_preset_tier_id <> ' ||p_preset_tier_rec.market_preset_tier_id
119 )
120 = FND_API.G_FALSE
121 THEN
122 OZF_Utility_PVT.Error_Message('OZF_MO_PRESTE_TIER_DUP');
123 x_return_status := FND_API.g_ret_sts_error;
124 return;
125 END IF;
126 END IF;
127 ELSE
128 IF
129 (
130 p_preset_tier_rec.offer_market_option_id IS NOT NULL AND p_preset_tier_rec.offer_market_option_id <> FND_API.G_MISS_NUM
131 )
132 AND
133 (
134 p_preset_tier_rec.pbh_offer_discount_id IS NOT NULL AND p_preset_tier_rec.pbh_offer_discount_id <> FND_API.G_MISS_NUM
135 )
136 AND
137 (
138 p_preset_tier_rec.dis_offer_discount_id IS NOT NULL AND p_preset_tier_rec.dis_offer_discount_id <> FND_API.G_MISS_NUM
139 )
140 THEN
141 IF ozf_utility_pvt.check_uniqueness('ozf_market_preset_tiers'
142 ,'offer_market_option_id = '||p_preset_tier_rec.offer_market_option_id ||' AND pbh_offer_discount_id = '|| p_preset_tier_rec.pbh_offer_discount_id || ' AND dis_offer_discount_id = '||p_preset_tier_rec.dis_offer_discount_id ) = FND_API.G_FALSE
143 THEN
144 OZF_Utility_PVT.Error_Message('OZF_MO_PRESTE_TIER_DUP');
145 x_return_status := FND_API.g_ret_sts_error;
146 return;
147 END IF;
148 END IF;
149
150 END IF;
151 END check_preset_tiers_uk_items;
152
153 PROCEDURE check_preset_tiers_fk_items
154 (
155 p_preset_tier_rec IN mo_preset_rec_type
156 , p_validation_mode IN VARCHAR2
157 , x_return_status OUT NOCOPY VARCHAR2
158 )
159 IS
160 CURSOR c_pbh(p_pbh_discount_id NUMBER)
161 IS
162 SELECT 1 FROM DUAL
163 WHERE EXISTS( SELECT 'X' FROM ozf_offer_discount_lines WHERE offer_discount_line_id = p_pbh_discount_id AND tier_type = 'PBH');
164
165 CURSOR c_dis(p_dis_discount_id NUMBER)
166 IS
167 SELECT 1 FROM dual WHERE EXISTS (SELECT 'X' FROM ozf_offer_discount_lines WHERE offer_discount_line_id = p_dis_discount_id AND tier_type = 'DIS');
168
169 l_dummy NUMBER;
170 BEGIN
171 x_return_status := FND_API.G_RET_STS_SUCCESS;
172 IF p_preset_tier_rec.offer_market_option_id IS NOT NULL AND p_preset_tier_rec.offer_market_option_id <> FND_API.G_MISS_NUM THEN
173 IF ozf_utility_pvt.check_fk_exists('ozf_offr_market_options','offer_market_option_id',to_char(p_preset_tier_rec.offer_market_option_id)) = FND_API.G_FALSE THEN
174 OZF_Utility_PVT.Error_Message('OZF_MO_PRESET_TIER_INV_MO_ID');
175 x_return_status := FND_API.g_ret_sts_error;
176 return;
177 END IF;
178 END IF;
179
180 IF p_preset_tier_rec.pbh_offer_discount_id IS NOT NULL AND p_preset_tier_rec.pbh_offer_discount_id <> FND_API.G_MISS_NUM THEN
181 IF ozf_utility_pvt.check_fk_exists('OZF_OFFER_DISCOUNT_LINES','offer_discount_line_id',to_char(p_preset_tier_rec.pbh_offer_discount_id)) =FND_API.G_FALSE THEN
182 OZF_Utility_PVT.Error_Message('OZF_MO_PRESET_TIER_INV_PBH_ID');
183 x_return_status := FND_API.g_ret_sts_error;
184 return;
185 END IF;
186 IF p_preset_tier_rec.dis_offer_discount_id IS NOT NULL AND p_preset_tier_rec.dis_offer_discount_id <> FND_API.G_MISS_NUM THEN
187 IF ozf_utility_pvt.check_fk_exists('OZF_OFFER_DISCOUNT_LINES', 'OFFER_DISCOUNT_LINE_ID', to_char(p_preset_tier_rec.dis_offer_discount_id)) = FND_API.G_FALSE THEN
188 OZF_Utility_PVT.Error_Message('OZF_MO_PRESET_TIERS_INV_DIS_ID');
189 x_return_status := FND_API.g_ret_sts_error;
190 return;
191 END IF;
192 END IF;
193 END IF;
194
195
196 END check_preset_tiers_fk_items;
197
198 PROCEDURE check_preset_tiers_attr
199 (
200 p_preset_tier_rec IN mo_preset_rec_type
201 , p_validation_mode IN VARCHAR2
202 , x_return_status OUT NOCOPY VARCHAR2
203 )
204 IS
205 BEGIN
206 x_return_status := FND_API.G_RET_STS_SUCCESS;
207 END check_preset_tiers_attr;
208
209 PROCEDURE check_preset_tiers_inter_attr
210 (
211 p_preset_tier_rec IN mo_preset_rec_type
212 , p_validation_mode IN VARCHAR2
213 , x_return_status OUT NOCOPY VARCHAR2
214 )
215 IS
216 CURSOR c_valid(p_dis_discount_id NUMBER, p_pbh_discount_id NUMBER) IS
217 SELECT 1 FROM dual
218 WHERE EXISTS(
219 SELECT 'x' FROM ozf_offer_discount_lines
220 WHERE offer_discount_line_id = p_dis_discount_id
221 AND parent_discount_line_id = p_pbh_discount_id);
222 l_dummy NUMBER;
223 BEGIN
224 x_return_status := FND_API.G_RET_STS_SUCCESS;
225 IF
226 (p_preset_tier_rec.pbh_offer_discount_id IS NOT NULL AND p_preset_tier_rec.pbh_offer_discount_id <> FND_API.G_MISS_NUM )
227 AND
228 (p_preset_tier_rec.dis_offer_discount_id IS NOT NULL AND p_preset_tier_rec.dis_offer_discount_id <> FND_API.G_MISS_NUM )
229 THEN
230 OPEN c_valid(p_preset_tier_rec.dis_offer_discount_id,p_preset_tier_rec.pbh_offer_discount_id);
231 FETCH c_valid INTO l_dummy;
232 IF (c_valid%NOTFOUND) THEN
233 CLOSE c_valid;
234 OZF_Utility_PVT.Error_Message('OZF_MO_PRESET_TIER_INV_DIS_PBH');
235 x_return_status := FND_API.g_ret_sts_error;
236 return;
237 END IF;
238 CLOSE c_valid;
239 END IF;
240 END check_preset_tiers_inter_attr;
241
242
243 PROCEDURE check_preset_tier_items
244 (
245 p_preset_tier_rec IN mo_preset_rec_type
246 , p_validation_mode IN VARCHAR2
247 , x_return_status OUT NOCOPY VARCHAR2
248 )
249 IS
250 BEGIN
251 x_return_status := FND_API.G_RET_STS_SUCCESS;
252 check_preset_tiers_req_items
253 (
254 p_preset_tier_rec => p_preset_tier_rec
255 , p_validation_mode => p_validation_mode
256 , x_return_status => x_return_status
257 );
258 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
259 RAISE FND_API.G_EXC_ERROR;
260 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
261 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
262 END IF;
263 check_preset_tiers_uk_items
264 (
265 p_preset_tier_rec => p_preset_tier_rec
266 , p_validation_mode => p_validation_mode
267 , x_return_status => x_return_status
268 );
269 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
270 RAISE FND_API.G_EXC_ERROR;
271 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
272 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
273 END IF;
274 check_preset_tiers_fk_items
275 (
276 p_preset_tier_rec => p_preset_tier_rec
277 , p_validation_mode => p_validation_mode
278 , x_return_status => x_return_status
279 );
280 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
281 RAISE FND_API.G_EXC_ERROR;
282 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
283 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
284 END IF;
285 check_preset_tiers_attr
286 (
287 p_preset_tier_rec => p_preset_tier_rec
288 , p_validation_mode => p_validation_mode
289 , x_return_status => x_return_status
290 );
291 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
292 RAISE FND_API.G_EXC_ERROR;
293 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
294 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
295 END IF;
296 check_preset_tiers_inter_attr
297 (
298 p_preset_tier_rec => p_preset_tier_rec
299 , p_validation_mode => p_validation_mode
300 , x_return_status => x_return_status
301 );
302 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
303 RAISE FND_API.G_EXC_ERROR;
304 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
305 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
306 END IF;
307 END check_preset_tier_items;
308
309 PROCEDURE Validate_mo_preset_tiers
310 (
311 p_api_version_number IN NUMBER,
312 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
313 p_commit IN VARCHAR2 := FND_API.G_FALSE,
314 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
315 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.G_CREATE,
316 x_return_status OUT NOCOPY VARCHAR2,
317 x_msg_count OUT NOCOPY NUMBER,
318 x_msg_data OUT NOCOPY VARCHAR2,
319
320 p_preset_tier_rec IN mo_preset_rec_type
321 )
322 IS
323 l_api_name CONSTANT VARCHAR2(30) := 'Validate_mo_preset_tiers';
324 BEGIN
325 x_return_status := FND_API.G_RET_STS_SUCCESS;
326
327 ozf_utility_pvt.debug_message('Private API: '|| l_api_name||' Start');
328
329 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
330 check_preset_tier_items
331 (
332 p_preset_tier_rec => p_preset_tier_rec
333 , p_validation_mode => p_validation_mode
334 , x_return_status => x_return_status
335 );
336 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
337 RAISE FND_API.G_EXC_ERROR;
338 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
339 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
340 END IF;
341 END IF;
342 ozf_utility_pvt.debug_message('Private API: '||l_api_name ||' End');
343
344 END Validate_mo_preset_tiers;
345
346 PROCEDURE Create_mo_preset_tiers(
347 p_api_version_number IN NUMBER,
348 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
349 p_commit IN VARCHAR2 := FND_API.G_FALSE,
350 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
351
352 x_return_status OUT NOCOPY VARCHAR2,
353 x_msg_count OUT NOCOPY NUMBER,
354 x_msg_data OUT NOCOPY VARCHAR2,
355
356 p_preset_tier_rec IN mo_preset_rec_type ,
357 x_market_preset_tier_id OUT NOCOPY NUMBER
358 )
359 IS
360 l_api_version_number CONSTANT NUMBER := 1.0;
361 l_api_name CONSTANT VARCHAR2(30) := 'Create_mo_preset_tiers';
362 l_market_preset_tier_id NUMBER;
366 l_preset_tier_rec mo_preset_rec_type;
363 l_dummy NUMBER;
364 l_object_version_number NUMBER;
365
367 CURSOR c_id IS
368 SELECT ozf_market_preset_tiers_s.nextval
369 FROM DUAL;
370
371 CURSOR c_id_exists(l_market_preset_tier_id NUMBER)
372 is
373 SELECT 1 FROM dual WHERE exists(SELECT 'X' FROM ozf_market_preset_tiers WHERE market_preset_tier_id = l_market_preset_tier_id);
374 BEGIN
375 -- initialize
376 -- savepoint
377 SAVEPOINT Create_mo_preset_tiers;
378 -- api compatibility
379 IF NOT FND_API.Compatible_api_call(
380 l_api_version_number
381 , p_api_version_number
382 , g_pkg_name
383 , l_api_name
384 ) THEN
385 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
386 END IF;
387 -- initialize messages
388 IF FND_API.to_boolean(p_init_msg_list) THEN
389 FND_MSG_PUB.initialize;
390 END IF;
391
392 ozf_utility_pvt.debug_message('Private API: '|| l_api_name || ' Start');
393 x_return_status := FND_API.G_RET_STS_SUCCESS;
394
395 IF FND_GLOBAL.USER_ID IS NULL
396 THEN
397 OZF_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
398 RAISE FND_API.G_EXC_ERROR;
399 END IF;
400
401 l_preset_tier_rec := p_preset_tier_rec;
402
403 IF p_preset_tier_rec.market_preset_tier_id IS NULL OR p_preset_tier_rec.market_preset_tier_id = FND_API.G_MISS_NUM THEN
404 LOOP
405 l_dummy := null;
406 OPEN c_id;
407 FETCH c_id INTO l_market_preset_tier_id;
408 CLOSE c_id;
409 OPEN c_id_exists(l_market_preset_tier_id);
410 FETCH c_id_exists INTO l_dummy;
411 CLOSE c_id_exists;
412 EXIT WHEN l_dummy IS NULL;
413 END LOOP;
414 ELSE
415 l_market_preset_tier_id := p_preset_tier_rec.market_preset_tier_id;
416 END IF;
417 -- validate
418 Validate_mo_preset_tiers
419 (
420 p_api_version_number => p_api_version_number
421 , p_init_msg_list => p_init_msg_list
422 , p_commit => p_commit
423 , p_validation_level => p_validation_level
424 , p_validation_mode => JTF_PLSQL_API.g_create
425 , x_return_status => x_return_status
426 , x_msg_count => x_msg_count
427 , x_msg_data => x_msg_data
428 , p_preset_tier_rec => l_preset_tier_rec
429 );
430
431 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
432 RAISE FND_API.G_EXC_ERROR;
433 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
434 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
435 END IF;
436
437 -- insert
438 OZF_MO_PRESET_TIERS_PKG.Insert_row
439 (
440 px_market_preset_tier_id => l_market_preset_tier_id
441 , p_offer_market_option_id => l_preset_tier_rec.offer_market_option_id
442 , p_pbh_offer_discount_id => l_preset_tier_rec.pbh_offer_discount_id
443 , p_dis_offer_discount_id => l_preset_tier_rec.dis_offer_discount_id
444 , px_object_version_number => l_object_version_number
445 , p_creation_date => sysdate
446 , p_created_by => FND_GLOBAL.user_id
447 , p_last_update_date => sysdate
448 , p_last_updated_by => FND_GLOBAL.user_id
449 , p_last_update_login => FND_GLOBAL.conc_login_id
450 );
451
452 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
453 RAISE FND_API.G_EXC_ERROR;
454 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
455 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
456 END IF;
457
458 x_market_preset_tier_id := l_market_preset_tier_id;
459
460 ozf_utility_pvt.debug_message('Private API: '|| l_api_name || ' Start');
461
462 IF FND_API.to_boolean(p_commit) THEN
463 COMMIT WORK;
464 END IF;
465 -- commit
466 EXCEPTION
467 WHEN FND_API.G_EXC_ERROR THEN
468 ROLLBACK TO Create_mo_preset_tiers;
469 x_return_status := FND_API.G_RET_STS_ERROR;
470 FND_MSG_PUB.count_and_get(
471 p_encoded => FND_API.g_false
472 , p_count => x_msg_count
473 , p_data => x_msg_data
474 );
475 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
476 ROLLBACK TO Create_mo_preset_tiers;
477 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
478 FND_MSG_PUB.count_and_get(
479 p_encoded => FND_API.g_false
480 , p_count => x_msg_count
481 , p_data => x_msg_data
482 );
483 WHEN OTHERS THEN
484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
485 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
486 THEN
487 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
488 END IF;
489 -- Standard call to get message count and if count=1, get the message
490 FND_MSG_PUB.Count_And_Get (
491 p_encoded => FND_API.G_FALSE,
492 p_count => x_msg_count,
493 p_data => x_msg_data
494 );
495 -- exception
496 NULL;
497 END Create_mo_preset_tiers;
498
499 PROCEDURE Update_mo_preset_tiers(
500 p_api_version_number IN NUMBER,
501 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
502 p_commit IN VARCHAR2 := FND_API.G_FALSE,
503 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
504
505 x_return_status OUT NOCOPY VARCHAR2,
506 x_msg_count OUT NOCOPY NUMBER,
507 x_msg_data OUT NOCOPY VARCHAR2,
508
512 l_api_name CONSTANT VARCHAR2(30) := 'Update_mo_preset_tiers';
509 p_preset_tier_rec IN mo_preset_rec_type
510 )
511 IS
513 l_api_version_number CONSTANT NUMBER := 1.0;
514 l_tar_preset_rec mo_preset_rec_type := p_preset_tier_rec;
515
516 CURSOR c_get_preset_tiers(p_market_preset_tier_id NUMBER, p_object_version_number NUMBER)
517 IS
518 SELECT * FROM ozf_market_preset_tiers
519 WHERE market_preset_tier_id = p_market_preset_tier_id
520 AND object_version_number = p_object_version_number;
521 l_ref_preset_tiers c_get_preset_tiers%ROWTYPE;
522
523 BEGIN
524 --initialize
525 SAVEPOINT Update_mo_preset_tiers;
526 IF NOT FND_API.Compatible_api_call
527 (
528 l_api_version_number
529 , p_api_version_number
530 , G_PKG_NAME
531 , l_api_name
532 )
533 THEN
534 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
535 END IF;
536
537 IF FND_API.to_boolean(p_init_msg_list) THEN
538 FND_MSG_PUB.initialize;
539 END IF;
540
541 ozf_utility_pvt.debug_message('Private API: '|| l_api_name || ' Start');
542 x_return_status := FND_API.G_RET_STS_SUCCESS;
543
544 open c_get_preset_tiers(l_tar_preset_rec.market_preset_tier_id, l_tar_preset_rec.object_version_number);
545 FETCH c_get_preset_tiers INTO l_ref_preset_tiers;
546 IF (c_get_preset_tiers%NOTFOUND) THEN
547 OZF_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET'
548 , p_token_name => 'INFO'
549 , p_token_value => 'OZF_MO_PRESET_TIERS') ;
550 RAISE FND_API.G_EXC_ERROR;
551 END IF;
552 IF l_tar_preset_rec.object_version_number = FND_API.G_MISS_NUM OR l_tar_preset_rec.object_version_number IS NULL THEN
553 OZF_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING'
554 , p_token_name => 'COLUMN'
555 , p_token_value => 'Last_Update_Date') ;
556 RAISE FND_API.G_EXC_ERROR;
557 END IF;
558 IF l_tar_preset_rec.object_version_number <> l_ref_preset_tiers.object_version_number THEN
559 OZF_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED'
560 , p_token_name => 'INFO'
561 , p_token_value => 'Ozf_Market_Options') ;
562 RAISE FND_API.G_EXC_ERROR;
563 END IF;
564 -- validate
565 -- update
566 -- commit
567 -- exception
568 Validate_mo_preset_tiers
569 (
570 p_api_version_number => p_api_version_number
571 , p_init_msg_list => p_init_msg_list
572 , p_commit => p_commit
573 , p_validation_level => p_validation_level
574 , p_validation_mode => JTF_PLSQL_API.g_update
575 , x_return_status => x_return_status
576 , x_msg_count => x_msg_count
577 , x_msg_data => x_msg_data
578 , p_preset_tier_rec => p_preset_tier_rec
579 );
580
581 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
582 RAISE FND_API.G_EXC_ERROR;
583 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
584 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
585 END IF;
586
587 OZF_MO_PRESET_TIERS_PKG.Update_row
588 (
589 p_market_preset_tier_id => l_tar_preset_rec.market_preset_tier_id
590 , p_offer_market_option_id => l_tar_preset_rec.offer_market_option_id
591 , p_pbh_offer_discount_id => l_tar_preset_rec.pbh_offer_discount_id
592 , p_dis_offer_discount_id => l_tar_preset_rec.dis_offer_discount_id
593 , p_object_version_number => l_tar_preset_rec.object_version_number
594 , p_last_update_date => sysdate
595 , p_last_updated_by => FND_GLOBAL.user_id
596 , p_last_update_login => FND_GLOBAL.conc_login_id
597 );
598 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
599 RAISE FND_API.G_EXC_ERROR;
600 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
601 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
602 END IF;
603
604 ozf_utility_pvt.debug_message('Private API: '|| l_api_name ||' End');
605
606 IF FND_API.to_boolean(p_commit) THEN
607 COMMIT WORK;
608 END IF;
609 EXCEPTION
610 WHEN FND_API.G_EXC_ERROR THEN
611 ROLLBACK TO UPDATE_mo_PRESET_TIERS;
612 x_return_status := FND_API.G_RET_STS_ERROR;
613 FND_MSG_PUB.count_and_get(
614 p_encoded => FND_API.g_false
615 , p_count => x_msg_count
616 , p_data => x_msg_data
617 );
618 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
619 ROLLBACK TO UPDATE_mo_PRESET_TIERS;
620 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
621 FND_MSG_PUB.count_and_get(
622 p_encoded => FND_API.g_false
623 , p_count => x_msg_count
624 , p_data => x_msg_data
625 );
626 WHEN OTHERS THEN
627 ROLLBACK TO UPDATE_mo_PRESET_TIERS;
628 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
629 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
630 THEN
631 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
632 END IF;
633 -- Standard call to get message count and if count=1, get the message
634 FND_MSG_PUB.Count_And_Get (
635 p_encoded => FND_API.G_FALSE,
636 p_count => x_msg_count,
637 p_data => x_msg_data
638 );
639
640 END UPDATE_mo_PRESET_TIERS;
641
642 PROCEDURE Delete_mo_preset_tiers(
643 p_api_version_number IN NUMBER,
644 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
645 p_commit IN VARCHAR2 := FND_API.G_FALSE,
646 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
650 p_market_preset_tier_id IN NUMBER,
647 x_return_status OUT NOCOPY VARCHAR2,
648 x_msg_count OUT NOCOPY NUMBER,
649 x_msg_data OUT NOCOPY VARCHAR2,
651 p_object_version_number IN NUMBER
652 )
653 IS
654 l_api_version_number CONSTANT NUMBER := 1.0;
655 l_api_name CONSTANT VARCHAR2(30) := 'Delete_mo_preset_tiers';
656 BEGIN
657 --INITIALIZE
658 SAVEPOINT Delete_mo_preset_tiers;
659 IF NOT FND_API.Compatible_api_call
660 (
661 l_api_version_number
662 , p_api_version_number
663 , G_PKG_NAME
664 , l_api_name
665 )
666 THEN
667 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
668 END IF;
669
670 IF FND_API.to_boolean(p_init_msg_list) THEN
671 FND_MSG_PUB.initialize;
672 END IF;
673
674 x_return_status := FND_API.G_RET_STS_SUCCESS;
675 ozf_utility_pvt.debug_message('Private API: '|| l_api_name || ' Start');
676
677 OZF_MO_PRESET_TIERS_PKG.Delete_row(p_market_preset_tier_id => p_market_preset_tier_id, p_object_version_number => p_object_version_number);
678
679 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
680 RAISE FND_API.G_EXC_ERROR;
681 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
682 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
683 END IF;
684
685 ozf_utility_pvt.debug_message('Private API: '|| l_api_name || ' End');
686 IF FND_API.to_boolean(p_commit) THEN
687 COMMIT WORK;
688 END IF;
689
690 EXCEPTION
691 WHEN FND_API.G_EXC_ERROR THEN
692 ROLLBACK TO Delete_mo_preset_tiers;
693 x_return_status := FND_API.G_RET_STS_ERROR;
694 FND_MSG_PUB.count_and_get(
695 p_encoded => FND_API.g_false
696 , p_count => x_msg_count
697 , p_data => x_msg_data
698 );
699 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
700 ROLLBACK TO Delete_mo_preset_tiers;
701 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
702 FND_MSG_PUB.count_and_get(
703 p_encoded => FND_API.g_false
704 , p_count => x_msg_count
705 , p_data => x_msg_data
706 );
707 WHEN OTHERS THEN
708 ROLLBACK TO Delete_mo_preset_tiers;
709 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
710 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
711 FND_MSG_PUB.Add_exc_msg(G_PKG_NAME,l_api_name);
712 END IF;
713 FND_MSG_PUB.count_and_get(
714 p_encoded => FND_API.g_false
715 , p_count => x_msg_count
716 , p_data => x_msg_data
717 );
718
719 -- exception
720 END Delete_mo_preset_tiers;
721
722
723 END OZF_MO_PRESET_TIERS_PVT;