[Home] [Help]
PACKAGE BODY: APPS.OZF_OFFER_MARKET_OPTIONS_PVT
Source
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_offer_Market_Options_PVT';
1 PACKAGE BODY OZF_offer_Market_Options_PVT AS
2 /* $Header: ozfvomob.pls 120.6.12020000.2 2012/10/17 10:14:05 nepanda ship $ */
3
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvodlb.pls';
6
7 FUNCTION get_combine_discounts(p_offer_id IN NUMBER)
8 RETURN VARCHAR2
9 IS
10 CURSOR c_volume_type_cnt (p_offer_id NUMBER) IS
11 SELECT count(distinct(volume_type)) FROM ozf_offer_discount_lines
12 WHERE offer_id = p_offer_id
13 AND tier_type = 'PBH';
14 l_vol_type_cnt NUMBER:=0;
15
16 CURSOR c_uom_code_cnt(p_offer_id NUMBER) IS
17 SELECT count(distinct(uom_code)) FROM ozf_offer_discount_lines
21
18 WHERE offer_id = p_offer_id
19 AND tier_type = 'PBH';
20 l_uom_code_cnt NUMBER := 0;
22 BEGIN
23 OPEN c_volume_type_cnt(p_offer_id);
24 FETCH c_volume_type_cnt INTO l_vol_type_cnt;
25 IF c_volume_type_cnt%NOTFOUND THEN
26 l_vol_type_cnt :=0;
27 END IF;
28 CLOSE c_volume_type_cnt ;
29
30 IF l_vol_type_cnt > 1 THEN
31 RETURN 'N';
32 ELSE
33 OPEN c_uom_code_cnt(p_offer_id);
34 FETCH c_uom_code_cnt INTO l_uom_code_cnt;
35 IF (c_uom_code_cnt%NOTFOUND) THEN
36 l_uom_code_cnt := 0;
37 END IF;
38 CLOSE c_uom_code_cnt;
39 IF l_uom_code_cnt > 1 THEN
40 return 'N';
41 ELSE
42 return 'Y';
43 END IF;
44 END IF;
45 END get_combine_discounts;
46
47 FUNCTION get_mo_name(p_qp_list_header_id IN NUMBER, p_qualifier_grouping_no IN NUMBER)
48 RETURN VARCHAR2
49 IS
50
51 CURSOR c_market_option_name(p_qp_list_header_id NUMBER, p_qualifier_grouping_no NUMBER) IS
52 SELECT QP_QP_Form_Pricing_Attr.Get_Attribute_Value('QP_ATTR_DEFNS_QUALIFIER',qpl.qualifier_context, qpl.qualifier_attribute, qpl.qualifier_attr_value)
53 FROM qp_qualifiers qpl
54 WHERE list_header_id = p_qp_list_header_id
55 AND qualifier_grouping_no = p_qualifier_grouping_no
56 AND qualifier_context IN ('CUSTOMER', 'CUSTOMER_GROUP','TERRITORY','SOLD_BY')
57 and qualifier_id = (select min(qualifier_id) from qp_qualifiers WHERE list_header_id = qpl.list_header_id and qualifier_grouping_no = qpl.qualifier_grouping_no);
58
59 l_market_option_name VARCHAR2(240);
60
61 BEGIN
62
63 OPEN c_market_option_name(p_qp_list_header_id , p_qualifier_grouping_no);
64 FETCH c_market_option_name INTO l_market_option_name;
65 IF c_market_option_name%NOTFOUND THEN
66 l_market_option_name := 'NONAME';
67 END IF;
68 CLOSE c_market_option_name;
69
70 RETURN l_market_option_name||'...';
71
72 END get_mo_name;
73
74
75 PROCEDURE check_mo_uk_items(
76 p_mo_rec IN vo_mo_rec_type
77 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
78 , x_return_status OUT NOCOPY VARCHAR2
79 )
80 IS
81 l_dummy NUMBER:= -1;
82 CURSOR c_mo_uk (p_offer_id NUMBER, p_group_number NUMBER) IS
83 SELECT 1 FROM dual
84 WHERE EXISTS
85 (SELECT 'X'
86 FROM ozf_offr_market_options
87 WHERE offer_id = p_offer_id --p_mo_rec.offer_id
88 AND group_number = p_group_number); --p_mo_rec.group_number);
89
90 BEGIN
91 x_return_status := FND_API.G_RET_STS_SUCCESS;
92 OZF_Volume_Offer_disc_PVT.debug_message('Market optionId is : '|| p_mo_rec.offer_market_option_id);
93 IF p_validation_mode = JTF_PLSQL_API.G_CREATE THEN
94 IF p_mo_rec.offer_market_option_id IS NOT NULL AND p_mo_rec.offer_market_option_id <> FND_API.G_MISS_NUM THEN
95 OZF_Volume_Offer_disc_PVT.debug_message('Checking qunqieness for moid');
96 IF OZF_Utility_PVT.check_uniqueness('ozf_offr_market_options','offer_market_option_id = ''' || p_mo_rec.offer_market_option_id ||'''') = FND_API.g_false THEN
97 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFF_MO_ID_DUP');
98 x_return_status := FND_API.g_ret_sts_error;
99 END IF;
100 END IF;
101 END IF;
102 OZF_Volume_Offer_disc_PVT.debug_message('Val mode is : '|| p_validation_mode);
103 /*
104 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
105 OZF_Volume_Offer_disc_PVT.debug_message('Val mode is1 : '|| p_validation_mode);
106 OZF_Volume_Offer_disc_PVT.debug_message('Market OPtion Id is1 '|| p_mo_rec.offer_market_option_id);
107 IF (p_mo_rec.offer_id IS NOT NULL AND p_mo_rec.offer_id <> FND_API.G_MISS_NUM)
108 AND (p_mo_rec.group_number IS NOT NULL AND p_mo_rec.group_number <> FND_API.G_MISS_NUM)
109 THEN
110 OZF_Volume_Offer_disc_PVT.debug_message('Market OPtion Id is1 '|| p_mo_rec.offer_market_option_id);
111 OPEN c_mo_uk(p_mo_rec.offer_id,p_mo_rec.group_number);
112 FETCH c_mo_uk INTO l_dummy;
113 IF ( c_mo_uk%NOTFOUND) THEN
114 NULL;
115 ELSE
116 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_MO_DUP');
117 x_return_status := FND_API.g_ret_sts_error;
118 END IF;
119 END IF;
120
121 END IF;
122 */
123
124 IF l_dummy = 1 THEN
125 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFR_MO_DUP');
126 x_return_status := FND_API.g_ret_sts_error;
127 END IF;
128
129 END check_mo_uk_items;
130
131 PROCEDURE check_mo_req_items(
132 p_mo_rec IN vo_mo_rec_type
133 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
134 , x_return_status OUT NOCOPY VARCHAR2
135 )
136 IS
137 l_api_name CONSTANT VARCHAR2(30) := 'check_mo_req_items';
138 BEGIN
139 OZF_Volume_Offer_disc_PVT.debug_message('Private API: ' || l_api_name || 'start');
140 OZF_Volume_Offer_disc_PVT.debug_message('Validation Mode is : ' || p_validation_mode || ' '|| JTF_PLSQL_API.g_create);
141
142 x_return_status := FND_API.g_ret_sts_success;
143
144 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
145 IF p_mo_rec.offer_id = FND_API.G_MISS_NUM OR p_mo_rec.offer_id IS NULL THEN
146 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'OFFER_ID' );
147 x_return_status := FND_API.g_ret_sts_error;
148 END IF;
149
153 END IF;
150 /*IF p_mo_rec.group_number = FND_API.G_MISS_NUM OR p_mo_rec.group_number IS NULL THEN
151 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'GROUP_NUMBER' );
152 x_return_status := FND_API.g_ret_sts_error;
154 */
155 IF p_mo_rec.retroactive_flag = FND_API.G_MISS_CHAR OR p_mo_rec.retroactive_flag IS NULL THEN
156 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'RETROACTIVE_FLAG' );
157 x_return_status := FND_API.g_ret_sts_error;
158 END IF;
159
160 IF p_mo_rec.combine_schedule_flag = FND_API.G_MISS_CHAR OR p_mo_rec.combine_schedule_flag IS NULL THEN
161 OZF_UTILITY_PVT.Error_message('OZF_API_MISSING_FIELD','MISS_FIELD','COMBINE_SCHEDULE_FLAG');
162 x_return_status := FND_API.g_ret_sts_error;
163 END IF;
164 IF p_mo_rec.volume_tracking_level_code = FND_API.G_MISS_CHAR OR p_mo_rec.volume_tracking_level_code IS NULL THEN
165 OZF_UTILITY_PVT.Error_message('OZF_API_MISSING_FIELD','MISS_FIELD','VOLUME_TRACKING_LEVEL_CODE');
166 x_return_status := FND_API.g_ret_sts_error;
167 END IF;
168 IF p_mo_rec.accrue_to_code = FND_API.G_MISS_CHAR OR p_mo_rec.accrue_to_code IS NULL THEN
169 OZF_UTILITY_PVT.Error_message('OZF_API_MISSING_FIELD','MISS_FIELD','ACCRUE_TO_CODE');
170 x_return_status := FND_API.g_ret_sts_error;
171 END IF;
172 IF p_mo_rec.precedence = FND_API.G_MISS_NUM OR p_mo_rec.precedence IS NULL THEN
173 OZF_UTILITY_PVT.Error_message('OZF_API_MISSING_FIELD','MISS_FIELD','PRECEDENCE');
174 END IF;
175
176
177 ELSE
178 IF p_mo_rec.offer_market_option_id = FND_API.G_MISS_NUM THEN
179 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'offer_market_option_id' );
180 x_return_status := FND_API.g_ret_sts_error;
181 END IF;
182
183 IF p_mo_rec.offer_id = FND_API.G_MISS_NUM THEN
184 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'OFFER_ID' );
185 x_return_status := FND_API.g_ret_sts_error;
186 END IF;
187
188 /* IF p_mo_rec.group_number = FND_API.G_MISS_NUM THEN
189 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'GROUP_NUMBER' );
190 x_return_status := FND_API.g_ret_sts_error;
191 END IF;
192 */
193 IF p_mo_rec.retroactive_flag = FND_API.G_MISS_CHAR THEN
194 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'RETROACTIVE_FLAG' );
195 x_return_status := FND_API.g_ret_sts_error;
196 END IF;
197
198 IF p_mo_rec.combine_schedule_flag = FND_API.G_MISS_CHAR THEN
199 OZF_UTILITY_PVT.Error_message('OZF_API_MISSING_FIELD','MISS_FIELD','COMBINE_SCHEDULE_FLAG');
200 x_return_status := FND_API.g_ret_sts_error;
201 END IF;
202 IF p_mo_rec.volume_tracking_level_code = FND_API.G_MISS_CHAR THEN
203 OZF_UTILITY_PVT.Error_message('OZF_API_MISSING_FIELD','MISS_FIELD','VOLUME_TRACKING_LEVEL_CODE');
204 x_return_status := FND_API.g_ret_sts_error;
205 END IF;
206 IF p_mo_rec.accrue_to_code = FND_API.G_MISS_CHAR THEN
207 OZF_UTILITY_PVT.Error_message('OZF_API_MISSING_FIELD','MISS_FIELD','ACCRUE_TO_CODE');
208 x_return_status := FND_API.g_ret_sts_error;
209 END IF;
210 IF p_mo_rec.precedence = FND_API.G_MISS_NUM THEN
211 OZF_UTILITY_PVT.Error_message('OZF_API_MISSING_FIELD','MISS_FIELD','PRECEDENCE');
212 x_return_status := FND_API.g_ret_sts_error;
213 END IF;
214 IF p_mo_rec.object_version_number = FND_API.G_MISS_NUM OR p_mo_rec.object_version_number IS NULL THEN
215 OZF_UTILITY_PVT.Error_message('OZF_API_MISSING_FIELD','MISS_FIELD','OBJECT_VERSION_NUMBER');
216 x_return_status := FND_API.g_ret_sts_error;
217 END IF;
218
219 END IF;
220
221 END check_mo_req_items;
222
223 PROCEDURE check_mo_fk_items(
224 p_mo_rec IN vo_mo_rec_type
225 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
226 , x_return_status OUT NOCOPY VARCHAR2
227 )
228 IS
229 l_dummy number := -1;
230 CURSOR c_mo_grp(p_list_header_id NUMBER, p_group_number NUMBER) IS
231 SELECT 1 FROM dual WHERE EXISTS( SELECT 'X' FROM qp_qualifiers WHERE list_header_id = p_list_header_id AND qualifier_grouping_no = p_group_number);
232
233 BEGIN
234 x_return_status := FND_API.G_RET_STS_SUCCESS;
235 IF p_mo_rec.offer_id IS NOT NULL AND p_mo_rec.offer_id <> FND_API.G_MISS_NUM
236 THEN
237 IF ozf_utility_pvt.check_fk_exists('OZF_OFFERS','OFFER_ID',to_char(p_mo_rec.offer_id)) = FND_API.g_false THEN
238 OZF_Utility_PVT.Error_Message('OZF_INVALID_OFFER_ID' );
239 x_return_status := FND_API.g_ret_sts_error;
240 END IF;
241 END IF;
242
243 -- Fix for bug # 14724414
244 IF p_mo_rec.qp_list_header_id IS NOT NULL AND p_mo_rec.qp_list_header_id <> FND_API.G_MISS_NUM
245 THEN
246 IF ozf_utility_pvt.check_fk_exists('QP_LIST_HEADERS_ALL_B','list_header_id',to_char(p_mo_rec.qp_list_header_id)) = FND_API.g_false THEN
247 OZF_Utility_PVT.Error_Message('OZF_INVALID_QP_LIST_HEADER' );
248 x_return_status := FND_API.g_ret_sts_error;
249 END IF;
250 END IF;
251
252 IF p_mo_rec.beneficiary_party_id IS NOT NULL AND p_mo_rec.beneficiary_party_id <> FND_API.G_MISS_NUM THEN
253 IF ozf_utility_pvt.check_fk_exists('QP_CUSTOMERS_V','CUSTOMER_ID',to_char(p_mo_rec.beneficiary_party_id)) = FND_API.g_false THEN
254 OZF_Utility_PVT.Error_Message('OZF_INVALID_BENEFICIARY' );
258
255 x_return_status := FND_API.g_ret_sts_error;
256 END IF;
257 END IF;
259 /* IF (p_mo_rec.qp_list_header_id IS NOT NULL AND p_mo_rec.qp_list_header_id <> FND_API.G_MISS_NUM)
260 AND (p_mo_rec.group_number IS NOT NULL AND p_mo_rec.group_number <> FND_API.G_MISS_NUM)
261 THEN
262 OPEN c_mo_grp(p_mo_rec.qp_list_header_id,p_mo_rec.group_number);
263 FETCH c_mo_grp INTO l_dummy;
264 IF (c_mo_grp%NOTFOUND) THEN
265 OZF_Utility_PVT.Error_Message('OZF_OFFR_INV_LH_GRP' );
266 x_return_status := FND_API.g_ret_sts_error;
267 END IF;
268 END IF;
269 */
270 END check_mo_fk_items;
271
272 PROCEDURE check_mo_lkup_items(
273 p_mo_rec IN vo_mo_rec_type
274 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
275 , x_return_status OUT NOCOPY VARCHAR2
276 )
277 IS
278 BEGIN
279 x_return_status := FND_API.G_RET_STS_SUCCESS;
280 IF p_mo_rec.volume_tracking_level_code <> FND_API.G_MISS_CHAR AND p_mo_rec.volume_tracking_level_code IS NOT NULL THEN
281 IF OZF_UTILITY_PVT.check_lookup_exists('OZF_LOOKUPS', 'OZF_VO_TRACKING_LEVEL', p_mo_rec.volume_tracking_level_code) = FND_API.g_false THEN
282 OZF_Utility_PVT.Error_Message('OZF_OFFR_MO_INV_VOL_TRK' );
283 x_return_status := FND_API.g_ret_sts_error;
284 END IF;
285 END IF;
286
287 IF p_mo_rec.accrue_to_code <> FND_API.G_MISS_CHAR AND p_mo_rec.accrue_to_code IS NOT NULL THEN
288 IF OZF_UTILITY_PVT.check_lookup_exists('OZF_LOOKUPS', 'OZF_VO_ACCRUE_TO', p_mo_rec.accrue_to_code) = FND_API.g_false THEN
289 OZF_Utility_PVT.Error_Message('OZF_OFFR_MO_INV_ACCR_TO' );
290 x_return_status := FND_API.g_ret_sts_error;
291 END IF;
292 END IF;
293 END check_mo_lkup_items;
294
295
296 PROCEDURE check_mo_attr(
297 p_mo_rec IN vo_mo_rec_type
298 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
299 , x_return_status OUT NOCOPY VARCHAR2
300 )
301 IS
302 BEGIN
303 x_return_status := FND_API.G_RET_STS_SUCCESS;
304 IF p_mo_rec.retroactive_flag IS NOT NULL AND p_mo_rec.retroactive_flag <> FND_API.G_MISS_CHAR THEN
305 IF upper(p_mo_rec.retroactive_flag) <> 'Y' AND upper(p_mo_rec.retroactive_flag) <> 'N' THEN
306 OZF_Utility_PVT.Error_Message('OZF_OFFR_MO_INV_RETROACT_FLAG' );
307 x_return_status := FND_API.g_ret_sts_error;
308 END IF;
309 END IF;
310
311 IF p_mo_rec.combine_schedule_flag IS NOT NULL AND p_mo_rec.combine_schedule_flag <> FND_API.G_MISS_CHAR THEN
312 IF upper(p_mo_rec.combine_schedule_flag) <> 'Y' AND upper(p_mo_rec.combine_schedule_flag) <> 'N' THEN
313 OZF_Utility_PVT.Error_Message('OZF_OFFR_MO_INV_COMB_TIERS' );
314 x_return_status := FND_API.g_ret_sts_error;
315 END IF;
316 END IF;
317
318 END check_mo_attr;
319
320
321 PROCEDURE check_mo_inter_attr(
322 p_mo_rec IN vo_mo_rec_type
323 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
324 , x_return_status OUT NOCOPY VARCHAR2
325 )
326 IS
327 BEGIN
328 x_return_status := FND_API.G_RET_STS_SUCCESS;
329 END check_mo_inter_attr;
330
331 PROCEDURE check_mo_entity(
332 p_mo_rec IN vo_mo_rec_type
333 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
334 , x_return_status OUT NOCOPY VARCHAR2
335 )
336 IS
337 BEGIN
338 x_return_status := FND_API.G_RET_STS_SUCCESS;
339 END check_mo_entity;
340
341
342
343 PROCEDURE Check_mo_Items(
344 p_mo_rec IN vo_mo_rec_type
345 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
346 , x_return_status OUT NOCOPY VARCHAR2
347 )
348 IS
349 BEGIN
350 -- initialize
351 x_return_status := FND_API.G_RET_STS_SUCCESS;
352 -- check unique items
353 check_mo_uk_items(
354 p_mo_rec => p_mo_rec
355 , p_validation_mode => p_validation_mode
356 , x_return_status => x_return_status
357 );
358 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
359 RAISE FND_API.G_EXC_ERROR;
360 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
361 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
362 END IF;
363
364 -- check required items
365 check_mo_req_items(
366 p_mo_rec => p_mo_rec
367 , p_validation_mode => p_validation_mode
368 , x_return_status => x_return_status
369 );
370 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
371 RAISE FND_API.G_EXC_ERROR;
372 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
373 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
374 END IF;
375
376 -- check Foreign key items
377 check_mo_fk_items(
378 p_mo_rec => p_mo_rec
379 , p_validation_mode => p_validation_mode
380 , x_return_status => x_return_status
381 );
382
383 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
384 RAISE FND_API.G_EXC_ERROR;
388
385 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
386 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
387 END IF;
389 -- check lookup items
390 check_mo_lkup_items(
391 p_mo_rec => p_mo_rec
392 , p_validation_mode => p_validation_mode
393 , x_return_status => x_return_status
394 );
395
396 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
397 RAISE FND_API.G_EXC_ERROR;
398 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
399 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
400 END IF;
401
402 -- check mo attributes
403 check_mo_attr(
404 p_mo_rec => p_mo_rec
405 , p_validation_mode => p_validation_mode
406 , x_return_status => x_return_status
407 );
408
409 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
410 RAISE FND_API.G_EXC_ERROR;
411 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
412 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
413 END IF;
414
415 -- check mo inter attributes
416 check_mo_inter_attr(
417 p_mo_rec => p_mo_rec
418 , p_validation_mode => p_validation_mode
419 , x_return_status => x_return_status
420 );
421
422 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
423 RAISE FND_API.G_EXC_ERROR;
424 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
425 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
426 END IF;
427
428 -- check mo entity
429 check_mo_entity(
430 p_mo_rec => p_mo_rec
431 , p_validation_mode => p_validation_mode
432 , x_return_status => x_return_status
433 );
434
435 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
436 RAISE FND_API.G_EXC_ERROR;
440
437 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
438 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
439 END IF;
441 END Check_mo_Items;
442
443
444
445 PROCEDURE validate_market_options
446 (
447 p_api_version_number IN NUMBER
448 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
449 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
450 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
451 , x_return_status OUT NOCOPY VARCHAR2
452 , x_msg_count OUT NOCOPY NUMBER
453 , x_msg_data OUT NOCOPY VARCHAR2
454 , p_mo_rec IN vo_mo_rec_type
455 )
456 IS
457 l_api_name CONSTANT VARCHAR2(30) := 'validate_market_options';
458 l_api_version_number CONSTANT NUMBER := 1.0;
459 l_object_version_number NUMBER;
460 l_vo_mo_rec vo_mo_rec_type;
461 BEGIN
462 -- Standard Start of API savepoint
463 SAVEPOINT validate_market_options_pvt;
464
465 -- Standard call to check for call compatibility.
466 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
467 p_api_version_number,
468 l_api_name,
469 G_PKG_NAME)
470 THEN
471 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
472 END IF;
473 -- Initialize message list if p_init_msg_list is set to TRUE.
474 IF FND_API.to_Boolean( p_init_msg_list )
475 THEN
476 FND_MSG_PUB.initialize;
477 END IF;
478
479 -- Debug Message
480 OZF_Volume_Offer_disc_PVT.debug_message('Private API: ' || l_api_name || 'start');
481
482 -- Initialize API return status to SUCCESS
483 x_return_status := FND_API.G_RET_STS_SUCCESS;
484
485 -- check items
486 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
487 Check_mo_Items(
488 p_mo_rec => p_mo_rec,
489 p_validation_mode => p_validation_mode,
490 x_return_status => x_return_status
491 );
492 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
493 RAISE FND_API.G_EXC_ERROR;
494 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
495 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
496 END IF;
497 END IF;
501 x_complete_rec => l_vo_disc_rec
498 IF p_validation_mode = JTF_PLSQL_API.g_update THEN
499 /* Complete_mo_Rec(
500 p_vo_disc_rec => l_vo_disc_rec,
502 );
503 */
504 -- END IF;
505 /* IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
506 Validate_vo_discounts_rec(
507 p_api_version_number => 1.0,
508 p_init_msg_list => FND_API.G_FALSE,
509 x_return_status => x_return_status,
510 x_msg_count => x_msg_count,
511 x_msg_data => x_msg_data,
512 p_vo_disc_rec => l_vo_disc_rec);
513 */
514 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
515 RAISE FND_API.G_EXC_ERROR;
516 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
517 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
518 END IF;
519 END IF;
520
521 -- exception
522 OZF_Volume_Offer_disc_PVT.debug_message('Private API: ' || l_api_name || 'Return status is : '|| x_return_status);
523
524 -- Debug Message
525 OZF_Volume_Offer_disc_PVT.debug_message('Private API: ' || l_api_name || 'end');
526
527
528 -- Standard call to get message count and if count is 1, get message info.
529 FND_MSG_PUB.Count_And_Get
530 (p_count => x_msg_count,
531 p_data => x_msg_data
532 );
533 EXCEPTION
534
535 WHEN OZF_Utility_PVT.resource_locked THEN
536 x_return_status := FND_API.g_ret_sts_error;
537 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
538
539 WHEN FND_API.G_EXC_ERROR THEN
540 ROLLBACK TO validate_market_options_pvt;
541 x_return_status := FND_API.G_RET_STS_ERROR;
542 -- Standard call to get message count and if count=1, get the message
543 FND_MSG_PUB.Count_And_Get (
544 p_encoded => FND_API.G_FALSE,
545 p_count => x_msg_count,
546 p_data => x_msg_data
547 );
548
549 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
550 ROLLBACK TO validate_market_options_pvt;
551 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
552 -- Standard call to get message count and if count=1, get the message
553 FND_MSG_PUB.Count_And_Get (
554 p_encoded => FND_API.G_FALSE,
555 p_count => x_msg_count,
556 p_data => x_msg_data
557 );
558
559 WHEN OTHERS THEN
560 ROLLBACK TO validate_market_options_pvt;
561 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
562 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
563 THEN
564 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
565 END IF;
566 -- Standard call to get message count and if count=1, get the message
567 FND_MSG_PUB.Count_And_Get (
568 p_encoded => FND_API.G_FALSE,
569 p_count => x_msg_count,
570 p_data => x_msg_data
571 );
572 END validate_market_options;
573
574 -- ==============================================================================
575 -- Start of Comments
576 -- ==============================================================================
577 -- API Name
578 -- Create_market_options
579 -- Type
580 -- Private
581 -- Pre-Req
582 -- Parameters
583 --
584 -- IN
585 -- p_api_version_number IN NUMBER
586 -- p_init_msg_list IN VARCHAR2
587 -- p_validation_level IN NUMBER
588 -- p_mo_rec IN vo_mo_rec_type
589 -- p_validation_mode IN VARCHAR2
590 --
591 -- OUT
592 -- x_return_status OUT NOCOPY VARCHAR2
593 -- x_msg_count OUT NOCOPY NUMBER
594 -- x_msg_data OUT NOCOPY VARCHAR2
595
596 -- Version : Current version 1.0
597 --
598 -- History
599 -- Mon Jun 20 2005:7/57 PM RSSHARMA Created
600 --
601 -- Description
602 -- End of Comments
603 -- ==============================================================================
604
605 PROCEDURE Create_market_options(
606 p_api_version_number IN NUMBER,
607 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
608 p_commit IN VARCHAR2 := FND_API.G_FALSE,
609 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
610
611 x_return_status OUT NOCOPY VARCHAR2,
612 x_msg_count OUT NOCOPY NUMBER,
613 x_msg_data OUT NOCOPY VARCHAR2,
614
615 p_mo_rec IN vo_mo_rec_type ,
616 x_vo_market_option_id OUT NOCOPY NUMBER
617 )
618 IS
619 l_mo_rec vo_mo_rec_type;
620 l_api_version_number CONSTANT NUMBER := 1.0;
621 l_api_name CONSTANT VARCHAR2(30) := 'Create_market_options';
622 l_market_option_id NUMBER;
623 l_object_version_number NUMBER;
624 l_dummy NUMBER;
625 CURSOR c_id IS
626 SELECT ozf_offr_market_options_s.NEXTVAL
627 FROM dual;
628 CURSOR c_id_exists (l_id IN NUMBER) IS
629 SELECT 1
630 FROM ozf_offr_market_options
631 WHERE offer_market_option_id = l_id;
632
633 BEGIN
634 -- initialize
635 --initialize
636 -- Standard Start of API savepoint
637 SAVEPOINT Create_market_options_pvt;
638
639 -- Standard call to check for call compatibility.
643 G_PKG_NAME)
640 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
641 p_api_version_number,
642 l_api_name,
644 THEN
645 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
646 END IF;
647
648 -- Initialize message list if p_init_msg_list is set to TRUE.
649 IF FND_API.to_Boolean( p_init_msg_list )
650 THEN
651 FND_MSG_PUB.initialize;
652 END IF;
653
654 -- Debug Message
655 OZF_Volume_Offer_disc_PVT.debug_message('Private API: ' || l_api_name || 'start');
656
657 -- Initialize API return status to SUCCESS
658 x_return_status := FND_API.G_RET_STS_SUCCESS;
659
660 -- =========================================================================
661 -- Validate Environment
662 -- =========================================================================
663
664 IF FND_GLOBAL.USER_ID IS NULL
665 THEN
666 OZF_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
667 RAISE FND_API.G_EXC_ERROR;
668 END IF;
669
670 l_mo_rec := p_mo_rec;
671
672
673 IF p_mo_rec.offer_market_option_id IS NULL OR p_mo_rec.OFFER_MARKET_OPTION_ID = FND_API.g_miss_num THEN
674 LOOP
675 l_dummy := NULL;
676 OPEN c_id;
677 FETCH c_id INTO l_market_option_id;
678 CLOSE c_id;
679
680 OPEN c_id_exists(l_market_option_id);
681 FETCH c_id_exists INTO l_dummy;
682 CLOSE c_id_exists;
683 EXIT WHEN l_dummy IS NULL;
684 END LOOP;
685 ELSE
686 l_market_option_id := p_mo_rec.offer_market_option_id;
687 END IF;
688
689 -- if group_number is -1 then dont create a market option. But since the market option id may be required , set the returned market option id to -1
690 IF l_mo_rec.group_number IS NOT NULL AND l_mo_rec.group_number <> FND_API.G_MISS_NUM THEN
691 IF l_mo_rec.group_number <> -1 THEN
692 -- validate
693 validate_market_options
694 (
695 p_api_version_number => p_api_version_number
696 , p_init_msg_list => p_init_msg_list
697 , p_validation_level => p_validation_level
698 , p_validation_mode => JTF_PLSQL_API.g_create
699 , x_return_status => x_return_status
700 , x_msg_count => x_msg_count
701 , x_msg_data => x_msg_data
702 , p_mo_rec => l_mo_rec
703 );
704
705 -- insert
706 OZF_OFFR_MARKET_OPTION_PKG.Insert_Row(
707 px_offer_market_option_id => l_market_option_id
708 , p_offer_id => l_mo_rec.offer_id
709 , p_qp_list_header_id => l_mo_rec.qp_list_header_id
710 , p_group_number => l_mo_rec.group_number
711 , p_retroactive_flag => l_mo_rec.retroactive_flag
712 , p_beneficiary_party_id => l_mo_rec.beneficiary_party_id
713 , p_combine_schedule_flag => l_mo_rec.combine_schedule_flag
714 , p_volume_tracking_level_code => l_mo_rec.volume_tracking_level_code
715 , p_accrue_to_code => l_mo_rec.accrue_to_code
716 , p_precedence => l_mo_rec.precedence
717 , px_object_version_number => l_object_version_number
718 , p_creation_date => SYSDATE
719 , p_created_by => FND_GLOBAL.USER_ID
720 , p_last_updated_by => FND_GLOBAL.USER_ID
721 , p_last_update_date => SYSDATE
722 , p_last_update_login => FND_GLOBAL.conc_login_id
723 );
724
725 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
726 RAISE FND_API.G_EXC_ERROR;
727 END IF;
728
729 x_vo_market_option_id := l_market_option_id;
730 ELSE
731 x_vo_market_option_id := -1;
732 END IF;
733 END IF;
734
735 -- commit;
736 IF FND_API.to_Boolean( p_commit )
737 THEN
738 COMMIT WORK;
739 END IF;
740 -- Debug Message
741 OZF_Volume_Offer_disc_PVT.debug_message('Private API: ' || l_api_name || 'Return status is : '|| x_return_status);
742 OZF_Volume_Offer_disc_PVT.debug_message('Private API: ' || l_api_name || 'end');
743 -- Standard call to get message count and if count is 1, get message info.
744
745 FND_MSG_PUB.Count_And_Get
746 (p_count => x_msg_count,
747 p_data => x_msg_data
748 );
749
750 -- exception
751 EXCEPTION
752 WHEN FND_API.G_EXC_ERROR THEN
753 ROLLBACK TO Create_market_options_pvt;
754 x_return_status := FND_API.G_RET_STS_ERROR;
755 -- Standard call to get message count and if count=1, get the message
756 FND_MSG_PUB.Count_And_Get (
757 p_encoded => FND_API.G_FALSE,
758 p_count => x_msg_count,
759 p_data => x_msg_data
760 );
764 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
761
762 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
763 ROLLBACK TO Create_market_options_pvt;
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 OTHERS THEN
773 ROLLBACK TO Create_market_options_pvt;
774 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
775 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
776 THEN
777 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
778 END IF;
779 -- Standard call to get message count and if count=1, get the message
780 FND_MSG_PUB.Count_And_Get (
781 p_encoded => FND_API.G_FALSE,
782 p_count => x_msg_count,
783 p_data => x_msg_data
784 );
785
786 END Create_market_options;
787
788
789
790 -- ==============================================================================
791 -- Start of Comments
792 -- ==============================================================================
793 -- API Name
794 -- Update_market_options
795 -- Type
796 -- Private
797 -- Pre-Req
798 -- validate_market_options
799 -- Parameters
800 --
801 -- IN
802 -- p_api_version_number IN NUMBER Required
803 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
804 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
805 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
806 -- p_mo_rec IN vo_mo_rec_type Required Record Containing Market options Data
807 -- x_return_status OUT NOCOPY VARCHAR2
808 -- x_msg_count OUT NOCOPY NUMBER
809 -- x_msg_data OUT NOCOPY VARCHAR2
810 -- Version : Current version 1.0
811 --
812 -- History
813 -- Mon Jun 20 2005:7/56 PM Created
814 --
815 -- Description
816 -- : Method to Update Discount Lines.
817 -- End of Comments
818 -- ==============================================================================
819
820 PROCEDURE Update_market_options(
821 p_api_version_number IN NUMBER,
822 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
823 p_commit IN VARCHAR2 := FND_API.G_FALSE,
824 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
825
826 x_return_status OUT NOCOPY VARCHAR2,
827 x_msg_count OUT NOCOPY NUMBER,
828 x_msg_data OUT NOCOPY VARCHAR2,
829
830 p_mo_rec IN vo_mo_rec_type
831 )
832 IS
833 CURSOR c_get_mo(p_market_option_id NUMBER, p_object_version_number NUMBER) IS
834 SELECT *
835 FROM ozf_offr_market_options
836 WHERE offer_market_option_id = p_market_option_id
837 AND object_version_number = p_object_version_number;
838 -- Hint: Developer need to provide Where clause
839
840 l_api_name CONSTANT VARCHAR2(30) := 'Update_market_options';
841 l_api_version_number CONSTANT NUMBER := 1.0;
842 -- Local Variables
843 l_object_version_number NUMBER;
844 l_market_option_id NUMBER;
845 l_ref_mo_rec c_get_mo%ROWTYPE ;
846 l_tar_mo_rec vo_mo_rec_type := p_mo_rec ;
847 l_rowid ROWID;
848 BEGIN
849 --initialize
850 -- Standard Start of API savepoint
851 SAVEPOINT Update_market_options_pvt;
852 -- Standard call to check for call compatibility.
853 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
854 p_api_version_number,
855 l_api_name,
856 G_PKG_NAME)
857 THEN
858 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
859 END IF;
860 -- Initialize message list if p_init_msg_list is set to TRUE.
861 IF FND_API.to_Boolean( p_init_msg_list )
862 THEN
863 FND_MSG_PUB.initialize;
864 END IF;
865 -- Debug Message
866 OZF_Volume_Offer_disc_PVT.debug_message('Private API: ' || l_api_name || 'start');
867
868 -- Initialize API return status to SUCCESS
869 x_return_status := FND_API.G_RET_STS_SUCCESS;
870
871 OPEN c_get_mo( l_tar_mo_rec.offer_market_option_id,l_tar_mo_rec.object_version_number);
872 FETCH c_get_mo INTO l_ref_mo_rec ;
873 If ( c_get_mo%NOTFOUND) THEN
874 OZF_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET'
875 , p_token_name => 'INFO'
876 , p_token_value => 'OZF_MARKET_OPTIONS') ;
877 RAISE FND_API.G_EXC_ERROR;
878 END IF;
879 CLOSE c_get_mo;
880
881 If (l_tar_mo_rec.object_version_number is NULL or
882 l_tar_mo_rec.object_version_number = FND_API.G_MISS_NUM ) Then
883 OZF_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING'
884 , p_token_name => 'COLUMN'
885 , p_token_value => 'Last_Update_Date') ;
889 If (l_tar_mo_rec.object_version_number <> l_ref_mo_rec.object_version_number) Then
886 RAISE FND_API.G_EXC_ERROR;
887 End if;
888 -- Check Whether record has been changed by someone else
890 OZF_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED'
891 , p_token_name => 'INFO'
892 , p_token_value => 'Ozf_Market_Options') ;
893 RAISE FND_API.G_EXC_ERROR;
894 End if;
895 -- validate
896 validate_market_options
897 (
898 p_api_version_number => p_api_version_number
899 , p_init_msg_list => p_init_msg_list
900 , p_validation_level => p_validation_level
901 , p_validation_mode => JTF_PLSQL_API.g_update
902 , x_return_status => x_return_status
903 , x_msg_count => x_msg_count
904 , x_msg_data => x_msg_data
905 , p_mo_rec => l_tar_mo_rec
906 );
907 -- update
908 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
909 RAISE FND_API.G_EXC_ERROR;
910 END IF;
911
915 , p_qp_list_header_id => l_tar_mo_rec.qp_list_header_id
912 OZF_OFFR_MARKET_OPTION_PKG.Update_Row(
913 p_offer_market_option_id => l_tar_mo_rec.offer_market_option_id
914 , p_offer_id => l_tar_mo_rec.offer_id
916 , p_group_number => l_tar_mo_rec.group_number
917 , p_retroactive_flag => l_tar_mo_rec.retroactive_flag
918 , p_beneficiary_party_id => l_tar_mo_rec.beneficiary_party_id
919 , p_combine_schedule_flag => l_tar_mo_rec.combine_schedule_flag
920 , p_volume_tracking_level_code => l_tar_mo_rec.volume_tracking_level_code
921 , p_accrue_to_code => l_tar_mo_rec.accrue_to_code
922 , p_precedence => l_tar_mo_rec.precedence
923 , p_object_version_number => l_tar_mo_rec.object_version_number
924 , p_creation_date => SYSDATE
925 , p_created_by => FND_GLOBAL.USER_ID
926 , p_last_updated_by => FND_GLOBAL.USER_ID
927 , p_last_update_date => SYSDATE
928 , p_last_update_login => FND_GLOBAL.conc_login_id
929 );
930
931 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
932 RAISE FND_API.G_EXC_ERROR;
933 END IF;
934
935 IF FND_API.to_Boolean( p_commit )
936 THEN
937 COMMIT WORK;
938 END IF;
939 -- Debug Message
940 OZF_Volume_Offer_disc_PVT.debug_message('Private API: ' || l_api_name || 'end');
941 -- Standard call to get message count and if count is 1, get message info.
942 FND_MSG_PUB.Count_And_Get
943 (p_count => x_msg_count,
944 p_data => x_msg_data
945 );
946
947 -- exception
948 EXCEPTION
949
950 WHEN OZF_Utility_PVT.resource_locked THEN
951 x_return_status := FND_API.g_ret_sts_error;
952 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
953
954 WHEN FND_API.G_EXC_ERROR THEN
955 ROLLBACK TO Update_market_options_pvt;
956 x_return_status := FND_API.G_RET_STS_ERROR;
957 -- Standard call to get message count and if count=1, get the message
958 FND_MSG_PUB.Count_And_Get (
959 p_encoded => FND_API.G_FALSE,
960 p_count => x_msg_count,
961 p_data => x_msg_data
962 );
963
964 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
965 ROLLBACK TO Update_market_options_pvt;
966 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
967 -- Standard call to get message count and if count=1, get the message
968 FND_MSG_PUB.Count_And_Get (
969 p_encoded => FND_API.G_FALSE,
970 p_count => x_msg_count,
971 p_data => x_msg_data
972 );
973
974 WHEN OTHERS THEN
975 ROLLBACK TO Update_market_options_pvt;
976 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
977 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
978 THEN
979 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
980 END IF;
981 -- Standard call to get message count and if count=1, get the message
982 FND_MSG_PUB.Count_And_Get (
983 p_encoded => FND_API.G_FALSE,
984 p_count => x_msg_count,
985 p_data => x_msg_data
986 );
987
988 END Update_market_options;
989
990
991 -- ==============================================================================
992 -- Start of Comments
993 -- ==============================================================================
994 -- API Name
995 -- Delete_market_options
999 -- Parameters
996 -- Type
997 -- Private
998 -- Pre-Req
1000 --
1001 -- IN
1002 -- p_api_version_number IN NUMBER
1003 -- p_init_msg_list IN VARCHAR2
1004 -- p_commit IN VARCHAR2
1005 -- p_validation_level IN NUMBER
1006 -- p_offer_market_option_id IN NUMBER
1007 -- p_object_version_number IN NUMBER
1008
1009 --
1010 -- OUT
1011 -- x_return_status OUT NOCOPY VARCHAR2
1012 -- x_msg_count OUT NOCOPY NUMBER
1013 -- x_msg_data OUT NOCOPY VARCHAR2
1014
1015 -- Version : Current version 1.0
1016 --
1017 -- History
1018 -- Mon Jun 20 2005:7/55 PM Created
1019 --
1020 -- Description
1021 -- End of Comments
1022 -- ==============================================================================
1023 PROCEDURE Delete_market_options(
1024 p_api_version_number IN NUMBER,
1025 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1026 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1027 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1028 x_return_status OUT NOCOPY VARCHAR2,
1029 x_msg_count OUT NOCOPY NUMBER,
1030 x_msg_data OUT NOCOPY VARCHAR2,
1031 p_offer_market_option_id IN NUMBER,
1032 p_object_version_number IN NUMBER
1033 )
1034 IS
1035 l_api_name CONSTANT VARCHAR2(30) := 'Delete_market_options';
1036 l_api_version_number CONSTANT NUMBER := 1.0;
1037 l_object_version_number NUMBER;
1038 BEGIN
1039 -- Standard Start of API savepoint
1040 SAVEPOINT Delete_market_options_PVT;
1041
1042 -- Standard call to check for call compatibility.
1043 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1044 p_api_version_number,
1045 l_api_name,
1046 G_PKG_NAME)
1047 THEN
1048 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1049 END IF;
1050
1051 -- Initialize message list if p_init_msg_list is set to TRUE.
1052 IF FND_API.to_Boolean( p_init_msg_list )
1053 THEN
1054 FND_MSG_PUB.initialize;
1055 END IF;
1056
1057 -- Initialize API return status to SUCCESS
1058 x_return_status := FND_API.G_RET_STS_SUCCESS;
1059
1060 --
1061 -- Api body
1062 --
1063
1064 -- Invoke table handler(OZF_Promotional_Offers_PKG.Delete_Row)
1065 OZF_OFFR_MARKET_OPTION_PKG.Delete_row(
1066 p_offer_market_option_id => p_offer_market_option_id
1067 , p_object_version_number => p_object_version_number
1068 );
1069
1070 --
1074 -- Standard check for p_commit
1071 -- End of API body
1072 --
1073
1075 IF FND_API.to_Boolean( p_commit )
1076 THEN
1077 COMMIT WORK;
1078 END IF;
1079
1080 -- Standard call to get message count and if count is 1, get message info.
1081 FND_MSG_PUB.Count_And_Get
1082 (p_count => x_msg_count,
1083 p_data => x_msg_data
1084 );
1085 EXCEPTION
1086
1087 WHEN OZF_Utility_PVT.resource_locked THEN
1088 x_return_status := FND_API.g_ret_sts_error;
1089 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
1090
1091 WHEN FND_API.G_EXC_ERROR THEN
1092 ROLLBACK TO Delete_market_options_PVT;
1093 x_return_status := FND_API.G_RET_STS_ERROR;
1094 -- Standard call to get message count and if count=1, get the message
1095 FND_MSG_PUB.Count_And_Get (
1096 p_encoded => FND_API.G_FALSE,
1097 p_count => x_msg_count,
1098 p_data => x_msg_data
1099 );
1100
1101 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1102 ROLLBACK TO Delete_market_options_PVT;
1103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1104 -- Standard call to get message count and if count=1, get the message
1105 FND_MSG_PUB.Count_And_Get (
1106 p_encoded => FND_API.G_FALSE,
1107 p_count => x_msg_count,
1108 p_data => x_msg_data
1109 );
1110
1111 WHEN OTHERS THEN
1112 ROLLBACK TO Delete_market_options_PVT;
1113 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1114 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1115 THEN
1116 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1117 END IF;
1118 -- Standard call to get message count and if count=1, get the message
1119 FND_MSG_PUB.Count_And_Get (
1120 p_encoded => FND_API.G_FALSE,
1121 p_count => x_msg_count,
1122 p_data => x_msg_data
1123 );
1124
1125 END Delete_market_options;
1126
1127
1128 END OZF_offer_Market_Options_PVT;
1129