[Home] [Help]
PACKAGE BODY: APPS.OZF_OFFER_MARKET_OPTIONS_PVT
Source
1 PACKAGE BODY OZF_offer_Market_Options_PVT AS
2 /* $Header: ozfvomob.pls 120.6 2005/10/11 17:52:33 rssharma noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_offer_Market_Options_PVT';
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
18 WHERE offer_id = p_offer_id
19 AND tier_type = 'PBH';
20 l_uom_code_cnt NUMBER := 0;
21
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
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;
153 END IF;
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 IF p_mo_rec.qp_list_header_id IS NOT NULL AND p_mo_rec.qp_list_header_id <> FND_API.G_MISS_NUM
244 THEN
245 IF ozf_utility_pvt.check_fk_exists('QP_LIST_HEADERS_B','list_header_id',to_char(p_mo_rec.qp_list_header_id)) = FND_API.g_false THEN
246 OZF_Utility_PVT.Error_Message('OZF_INVALID_QP_LIST_HEADER' );
247 x_return_status := FND_API.g_ret_sts_error;
248 END IF;
249 END IF;
250
251 IF p_mo_rec.beneficiary_party_id IS NOT NULL AND p_mo_rec.beneficiary_party_id <> FND_API.G_MISS_NUM THEN
252 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
253 OZF_Utility_PVT.Error_Message('OZF_INVALID_BENEFICIARY' );
254 x_return_status := FND_API.g_ret_sts_error;
255 END IF;
256 END IF;
257
258 /* IF (p_mo_rec.qp_list_header_id IS NOT NULL AND p_mo_rec.qp_list_header_id <> FND_API.G_MISS_NUM)
259 AND (p_mo_rec.group_number IS NOT NULL AND p_mo_rec.group_number <> FND_API.G_MISS_NUM)
260 THEN
261 OPEN c_mo_grp(p_mo_rec.qp_list_header_id,p_mo_rec.group_number);
262 FETCH c_mo_grp INTO l_dummy;
263 IF (c_mo_grp%NOTFOUND) THEN
264 OZF_Utility_PVT.Error_Message('OZF_OFFR_INV_LH_GRP' );
265 x_return_status := FND_API.g_ret_sts_error;
266 END IF;
267 END IF;
268 */
269 END check_mo_fk_items;
270
271 PROCEDURE check_mo_lkup_items(
272 p_mo_rec IN vo_mo_rec_type
273 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
274 , x_return_status OUT NOCOPY VARCHAR2
275 )
276 IS
277 BEGIN
278 x_return_status := FND_API.G_RET_STS_SUCCESS;
279 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
280 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
281 OZF_Utility_PVT.Error_Message('OZF_OFFR_MO_INV_VOL_TRK' );
282 x_return_status := FND_API.g_ret_sts_error;
283 END IF;
284 END IF;
285
286 IF p_mo_rec.accrue_to_code <> FND_API.G_MISS_CHAR AND p_mo_rec.accrue_to_code IS NOT NULL THEN
287 IF OZF_UTILITY_PVT.check_lookup_exists('OZF_LOOKUPS', 'OZF_VO_ACCRUE_TO', p_mo_rec.accrue_to_code) = FND_API.g_false THEN
288 OZF_Utility_PVT.Error_Message('OZF_OFFR_MO_INV_ACCR_TO' );
289 x_return_status := FND_API.g_ret_sts_error;
290 END IF;
291 END IF;
295 PROCEDURE check_mo_attr(
292 END check_mo_lkup_items;
293
294
296 p_mo_rec IN vo_mo_rec_type
297 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
298 , x_return_status OUT NOCOPY VARCHAR2
299 )
300 IS
301 BEGIN
302 x_return_status := FND_API.G_RET_STS_SUCCESS;
303 IF p_mo_rec.retroactive_flag IS NOT NULL AND p_mo_rec.retroactive_flag <> FND_API.G_MISS_CHAR THEN
304 IF upper(p_mo_rec.retroactive_flag) <> 'Y' AND upper(p_mo_rec.retroactive_flag) <> 'N' THEN
305 OZF_Utility_PVT.Error_Message('OZF_OFFR_MO_INV_RETROACT_FLAG' );
306 x_return_status := FND_API.g_ret_sts_error;
307 END IF;
308 END IF;
309
310 IF p_mo_rec.combine_schedule_flag IS NOT NULL AND p_mo_rec.combine_schedule_flag <> FND_API.G_MISS_CHAR THEN
311 IF upper(p_mo_rec.combine_schedule_flag) <> 'Y' AND upper(p_mo_rec.combine_schedule_flag) <> 'N' THEN
312 OZF_Utility_PVT.Error_Message('OZF_OFFR_MO_INV_COMB_TIERS' );
313 x_return_status := FND_API.g_ret_sts_error;
314 END IF;
315 END IF;
316
317 END check_mo_attr;
318
319
320 PROCEDURE check_mo_inter_attr(
321 p_mo_rec IN vo_mo_rec_type
322 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
323 , x_return_status OUT NOCOPY VARCHAR2
324 )
325 IS
326 BEGIN
327 x_return_status := FND_API.G_RET_STS_SUCCESS;
328 END check_mo_inter_attr;
329
330 PROCEDURE check_mo_entity(
331 p_mo_rec IN vo_mo_rec_type
332 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
333 , x_return_status OUT NOCOPY VARCHAR2
334 )
335 IS
336 BEGIN
337 x_return_status := FND_API.G_RET_STS_SUCCESS;
338 END check_mo_entity;
339
340
341
342 PROCEDURE Check_mo_Items(
343 p_mo_rec IN vo_mo_rec_type
344 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
345 , x_return_status OUT NOCOPY VARCHAR2
346 )
347 IS
348 BEGIN
349 -- initialize
350 x_return_status := FND_API.G_RET_STS_SUCCESS;
351 -- check unique items
352 check_mo_uk_items(
353 p_mo_rec => p_mo_rec
354 , p_validation_mode => p_validation_mode
355 , x_return_status => x_return_status
356 );
357 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
358 RAISE FND_API.G_EXC_ERROR;
359 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
360 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
361 END IF;
362
363 -- check required items
364 check_mo_req_items(
365 p_mo_rec => p_mo_rec
366 , p_validation_mode => p_validation_mode
367 , x_return_status => x_return_status
368 );
369 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
370 RAISE FND_API.G_EXC_ERROR;
371 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
372 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
373 END IF;
374
375 -- check Foreign key items
376 check_mo_fk_items(
377 p_mo_rec => p_mo_rec
378 , p_validation_mode => p_validation_mode
379 , x_return_status => x_return_status
380 );
381
382 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
383 RAISE FND_API.G_EXC_ERROR;
384 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
385 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
386 END IF;
387
388 -- check lookup items
389 check_mo_lkup_items(
390 p_mo_rec => p_mo_rec
391 , p_validation_mode => p_validation_mode
392 , x_return_status => x_return_status
393 );
394
395 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
396 RAISE FND_API.G_EXC_ERROR;
397 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
398 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
399 END IF;
400
401 -- check mo attributes
402 check_mo_attr(
403 p_mo_rec => p_mo_rec
404 , p_validation_mode => p_validation_mode
405 , x_return_status => x_return_status
406 );
407
408 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
409 RAISE FND_API.G_EXC_ERROR;
410 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
411 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
412 END IF;
413
414 -- check mo inter attributes
415 check_mo_inter_attr(
416 p_mo_rec => p_mo_rec
417 , p_validation_mode => p_validation_mode
418 , x_return_status => x_return_status
419 );
420
421 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
422 RAISE FND_API.G_EXC_ERROR;
423 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
424 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
425 END IF;
426
427 -- check mo entity
428 check_mo_entity(
429 p_mo_rec => p_mo_rec
430 , p_validation_mode => p_validation_mode
431 , x_return_status => x_return_status
432 );
433
437 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
434 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
435 RAISE FND_API.G_EXC_ERROR;
436 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
438 END IF;
439
440 END Check_mo_Items;
441
442
443
444 PROCEDURE validate_market_options
445 (
446 p_api_version_number IN NUMBER
447 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
448 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
449 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
450 , x_return_status OUT NOCOPY VARCHAR2
451 , x_msg_count OUT NOCOPY NUMBER
452 , x_msg_data OUT NOCOPY VARCHAR2
453 , p_mo_rec IN vo_mo_rec_type
454 )
455 IS
456 l_api_name CONSTANT VARCHAR2(30) := 'validate_market_options';
457 l_api_version_number CONSTANT NUMBER := 1.0;
458 l_object_version_number NUMBER;
459 l_vo_mo_rec vo_mo_rec_type;
460 BEGIN
461 -- Standard Start of API savepoint
462 SAVEPOINT validate_market_options_pvt;
463
464 -- Standard call to check for call compatibility.
465 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
466 p_api_version_number,
467 l_api_name,
468 G_PKG_NAME)
469 THEN
470 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
471 END IF;
472 -- Initialize message list if p_init_msg_list is set to TRUE.
473 IF FND_API.to_Boolean( p_init_msg_list )
474 THEN
475 FND_MSG_PUB.initialize;
476 END IF;
477
478 -- Debug Message
479 OZF_Volume_Offer_disc_PVT.debug_message('Private API: ' || l_api_name || 'start');
480
481 -- Initialize API return status to SUCCESS
482 x_return_status := FND_API.G_RET_STS_SUCCESS;
483
484 -- check items
485 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
486 Check_mo_Items(
487 p_mo_rec => p_mo_rec,
488 p_validation_mode => p_validation_mode,
489 x_return_status => x_return_status
490 );
491 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
492 RAISE FND_API.G_EXC_ERROR;
493 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
494 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
495 END IF;
496 END IF;
497 IF p_validation_mode = JTF_PLSQL_API.g_update THEN
498 /* Complete_mo_Rec(
499 p_vo_disc_rec => l_vo_disc_rec,
500 x_complete_rec => l_vo_disc_rec
501 );
502 */
503 -- END IF;
504 /* IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
505 Validate_vo_discounts_rec(
506 p_api_version_number => 1.0,
507 p_init_msg_list => FND_API.G_FALSE,
508 x_return_status => x_return_status,
509 x_msg_count => x_msg_count,
510 x_msg_data => x_msg_data,
511 p_vo_disc_rec => l_vo_disc_rec);
512 */
513 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
514 RAISE FND_API.G_EXC_ERROR;
515 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
516 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
517 END IF;
518 END IF;
519
520 -- exception
521 OZF_Volume_Offer_disc_PVT.debug_message('Private API: ' || l_api_name || 'Return status is : '|| x_return_status);
522
523 -- Debug Message
524 OZF_Volume_Offer_disc_PVT.debug_message('Private API: ' || l_api_name || 'end');
525
526
527 -- Standard call to get message count and if count is 1, get message info.
528 FND_MSG_PUB.Count_And_Get
529 (p_count => x_msg_count,
530 p_data => x_msg_data
531 );
532 EXCEPTION
533
534 WHEN OZF_Utility_PVT.resource_locked THEN
535 x_return_status := FND_API.g_ret_sts_error;
536 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
537
538 WHEN FND_API.G_EXC_ERROR THEN
539 ROLLBACK TO validate_market_options_pvt;
540 x_return_status := FND_API.G_RET_STS_ERROR;
541 -- Standard call to get message count and if count=1, get the message
542 FND_MSG_PUB.Count_And_Get (
543 p_encoded => FND_API.G_FALSE,
544 p_count => x_msg_count,
545 p_data => x_msg_data
546 );
547
548 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
549 ROLLBACK TO validate_market_options_pvt;
550 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
551 -- Standard call to get message count and if count=1, get the message
552 FND_MSG_PUB.Count_And_Get (
553 p_encoded => FND_API.G_FALSE,
554 p_count => x_msg_count,
555 p_data => x_msg_data
556 );
557
558 WHEN OTHERS THEN
559 ROLLBACK TO validate_market_options_pvt;
560 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
561 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
562 THEN
563 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
564 END IF;
565 -- Standard call to get message count and if count=1, get the message
566 FND_MSG_PUB.Count_And_Get (
567 p_encoded => FND_API.G_FALSE,
568 p_count => x_msg_count,
569 p_data => x_msg_data
570 );
574 -- Start of Comments
571 END validate_market_options;
572
573 -- ==============================================================================
575 -- ==============================================================================
576 -- API Name
577 -- Create_market_options
578 -- Type
579 -- Private
580 -- Pre-Req
581 -- Parameters
582 --
583 -- IN
584 -- p_api_version_number IN NUMBER
585 -- p_init_msg_list IN VARCHAR2
586 -- p_validation_level IN NUMBER
587 -- p_mo_rec IN vo_mo_rec_type
588 -- p_validation_mode IN VARCHAR2
589 --
590 -- OUT
591 -- x_return_status OUT NOCOPY VARCHAR2
592 -- x_msg_count OUT NOCOPY NUMBER
593 -- x_msg_data OUT NOCOPY VARCHAR2
594
595 -- Version : Current version 1.0
596 --
597 -- History
598 -- Mon Jun 20 2005:7/57 PM RSSHARMA Created
599 --
600 -- Description
601 -- End of Comments
602 -- ==============================================================================
603
604 PROCEDURE Create_market_options(
605 p_api_version_number IN NUMBER,
606 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
607 p_commit IN VARCHAR2 := FND_API.G_FALSE,
608 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
609
610 x_return_status OUT NOCOPY VARCHAR2,
611 x_msg_count OUT NOCOPY NUMBER,
612 x_msg_data OUT NOCOPY VARCHAR2,
613
614 p_mo_rec IN vo_mo_rec_type ,
615 x_vo_market_option_id OUT NOCOPY NUMBER
616 )
617 IS
618 l_mo_rec vo_mo_rec_type;
619 l_api_version_number CONSTANT NUMBER := 1.0;
620 l_api_name CONSTANT VARCHAR2(30) := 'Create_market_options';
621 l_market_option_id NUMBER;
622 l_object_version_number NUMBER;
623 l_dummy NUMBER;
624 CURSOR c_id IS
625 SELECT ozf_offr_market_options_s.NEXTVAL
626 FROM dual;
627 CURSOR c_id_exists (l_id IN NUMBER) IS
628 SELECT 1
629 FROM ozf_offr_market_options
630 WHERE offer_market_option_id = l_id;
631
632 BEGIN
633 -- initialize
634 --initialize
635 -- Standard Start of API savepoint
636 SAVEPOINT Create_market_options_pvt;
637
638 -- Standard call to check for call compatibility.
639 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
640 p_api_version_number,
641 l_api_name,
642 G_PKG_NAME)
643 THEN
644 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
645 END IF;
646
647 -- Initialize message list if p_init_msg_list is set to TRUE.
648 IF FND_API.to_Boolean( p_init_msg_list )
649 THEN
650 FND_MSG_PUB.initialize;
651 END IF;
652
653 -- Debug Message
654 OZF_Volume_Offer_disc_PVT.debug_message('Private API: ' || l_api_name || 'start');
655
656 -- Initialize API return status to SUCCESS
657 x_return_status := FND_API.G_RET_STS_SUCCESS;
658
659 -- =========================================================================
660 -- Validate Environment
661 -- =========================================================================
662
663 IF FND_GLOBAL.USER_ID IS NULL
664 THEN
665 OZF_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
666 RAISE FND_API.G_EXC_ERROR;
667 END IF;
668
669 l_mo_rec := p_mo_rec;
670
671
672 IF p_mo_rec.offer_market_option_id IS NULL OR p_mo_rec.OFFER_MARKET_OPTION_ID = FND_API.g_miss_num THEN
673 LOOP
674 l_dummy := NULL;
675 OPEN c_id;
676 FETCH c_id INTO l_market_option_id;
677 CLOSE c_id;
678
679 OPEN c_id_exists(l_market_option_id);
680 FETCH c_id_exists INTO l_dummy;
681 CLOSE c_id_exists;
682 EXIT WHEN l_dummy IS NULL;
683 END LOOP;
684 ELSE
685 l_market_option_id := p_mo_rec.offer_market_option_id;
686 END IF;
687
688 -- 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
689 IF l_mo_rec.group_number IS NOT NULL AND l_mo_rec.group_number <> FND_API.G_MISS_NUM THEN
690 IF l_mo_rec.group_number <> -1 THEN
691 -- validate
692 validate_market_options
693 (
694 p_api_version_number => p_api_version_number
695 , p_init_msg_list => p_init_msg_list
696 , p_validation_level => p_validation_level
697 , p_validation_mode => JTF_PLSQL_API.g_create
698 , x_return_status => x_return_status
699 , x_msg_count => x_msg_count
700 , x_msg_data => x_msg_data
701 , p_mo_rec => l_mo_rec
702 );
703
704 -- insert
705 OZF_OFFR_MARKET_OPTION_PKG.Insert_Row(
706 px_offer_market_option_id => l_market_option_id
707 , p_offer_id => l_mo_rec.offer_id
708 , p_qp_list_header_id => l_mo_rec.qp_list_header_id
709 , p_group_number => l_mo_rec.group_number
710 , p_retroactive_flag => l_mo_rec.retroactive_flag
711 , p_beneficiary_party_id => l_mo_rec.beneficiary_party_id
712 , p_combine_schedule_flag => l_mo_rec.combine_schedule_flag
713 , p_volume_tracking_level_code => l_mo_rec.volume_tracking_level_code
714 , p_accrue_to_code => l_mo_rec.accrue_to_code
718 , p_created_by => FND_GLOBAL.USER_ID
715 , p_precedence => l_mo_rec.precedence
716 , px_object_version_number => l_object_version_number
717 , p_creation_date => SYSDATE
719 , p_last_updated_by => FND_GLOBAL.USER_ID
720 , p_last_update_date => SYSDATE
721 , p_last_update_login => FND_GLOBAL.conc_login_id
722 );
723
724 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
725 RAISE FND_API.G_EXC_ERROR;
726 END IF;
727
728 x_vo_market_option_id := l_market_option_id;
729 ELSE
730 x_vo_market_option_id := -1;
731 END IF;
732 END IF;
733
734 -- commit;
735 IF FND_API.to_Boolean( p_commit )
736 THEN
737 COMMIT WORK;
738 END IF;
739 -- Debug Message
740 OZF_Volume_Offer_disc_PVT.debug_message('Private API: ' || l_api_name || 'Return status is : '|| x_return_status);
741 OZF_Volume_Offer_disc_PVT.debug_message('Private API: ' || l_api_name || 'end');
742 -- Standard call to get message count and if count is 1, get message info.
743
744 FND_MSG_PUB.Count_And_Get
745 (p_count => x_msg_count,
746 p_data => x_msg_data
747 );
748
749 -- exception
750 EXCEPTION
751 WHEN FND_API.G_EXC_ERROR THEN
752 ROLLBACK TO Create_market_options_pvt;
753 x_return_status := FND_API.G_RET_STS_ERROR;
754 -- Standard call to get message count and if count=1, get the message
755 FND_MSG_PUB.Count_And_Get (
756 p_encoded => FND_API.G_FALSE,
757 p_count => x_msg_count,
758 p_data => x_msg_data
759 );
760
761 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
762 ROLLBACK TO Create_market_options_pvt;
763 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
764 -- Standard call to get message count and if count=1, get the message
765 FND_MSG_PUB.Count_And_Get (
766 p_encoded => FND_API.G_FALSE,
767 p_count => x_msg_count,
768 p_data => x_msg_data
769 );
770
771 WHEN OTHERS THEN
772 ROLLBACK TO Create_market_options_pvt;
773 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
774 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
775 THEN
776 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
777 END IF;
778 -- Standard call to get message count and if count=1, get the message
779 FND_MSG_PUB.Count_And_Get (
780 p_encoded => FND_API.G_FALSE,
781 p_count => x_msg_count,
782 p_data => x_msg_data
783 );
784
785 END Create_market_options;
786
787
788
789 -- ==============================================================================
790 -- Start of Comments
791 -- ==============================================================================
792 -- API Name
793 -- Update_market_options
794 -- Type
795 -- Private
796 -- Pre-Req
797 -- validate_market_options
798 -- Parameters
799 --
800 -- IN
801 -- p_api_version_number IN NUMBER Required
802 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
803 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
804 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
805 -- p_mo_rec IN vo_mo_rec_type Required Record Containing Market options Data
806 -- x_return_status OUT NOCOPY VARCHAR2
807 -- x_msg_count OUT NOCOPY NUMBER
808 -- x_msg_data OUT NOCOPY VARCHAR2
809 -- Version : Current version 1.0
810 --
811 -- History
812 -- Mon Jun 20 2005:7/56 PM Created
813 --
814 -- Description
815 -- : Method to Update Discount Lines.
816 -- End of Comments
817 -- ==============================================================================
818
819 PROCEDURE Update_market_options(
820 p_api_version_number IN NUMBER,
821 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
822 p_commit IN VARCHAR2 := FND_API.G_FALSE,
823 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
824
825 x_return_status OUT NOCOPY VARCHAR2,
826 x_msg_count OUT NOCOPY NUMBER,
827 x_msg_data OUT NOCOPY VARCHAR2,
828
829 p_mo_rec IN vo_mo_rec_type
830 )
831 IS
832 CURSOR c_get_mo(p_market_option_id NUMBER, p_object_version_number NUMBER) IS
833 SELECT *
834 FROM ozf_offr_market_options
835 WHERE offer_market_option_id = p_market_option_id
836 AND object_version_number = p_object_version_number;
837 -- Hint: Developer need to provide Where clause
838
839 l_api_name CONSTANT VARCHAR2(30) := 'Update_market_options';
840 l_api_version_number CONSTANT NUMBER := 1.0;
841 -- Local Variables
842 l_object_version_number NUMBER;
843 l_market_option_id NUMBER;
844 l_ref_mo_rec c_get_mo%ROWTYPE ;
845 l_tar_mo_rec vo_mo_rec_type := p_mo_rec ;
846 l_rowid ROWID;
847 BEGIN
848 --initialize
849 -- Standard Start of API savepoint
850 SAVEPOINT Update_market_options_pvt;
851 -- Standard call to check for call compatibility.
852 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
853 p_api_version_number,
854 l_api_name,
858 END IF;
855 G_PKG_NAME)
856 THEN
857 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
859 -- Initialize message list if p_init_msg_list is set to TRUE.
860 IF FND_API.to_Boolean( p_init_msg_list )
861 THEN
862 FND_MSG_PUB.initialize;
863 END IF;
864 -- Debug Message
865 OZF_Volume_Offer_disc_PVT.debug_message('Private API: ' || l_api_name || 'start');
866
867 -- Initialize API return status to SUCCESS
868 x_return_status := FND_API.G_RET_STS_SUCCESS;
869
870 OPEN c_get_mo( l_tar_mo_rec.offer_market_option_id,l_tar_mo_rec.object_version_number);
871 FETCH c_get_mo INTO l_ref_mo_rec ;
872 If ( c_get_mo%NOTFOUND) THEN
873 OZF_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET'
874 , p_token_name => 'INFO'
875 , p_token_value => 'OZF_MARKET_OPTIONS') ;
876 RAISE FND_API.G_EXC_ERROR;
877 END IF;
878 CLOSE c_get_mo;
879
880 If (l_tar_mo_rec.object_version_number is NULL or
881 l_tar_mo_rec.object_version_number = FND_API.G_MISS_NUM ) Then
882 OZF_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING'
883 , p_token_name => 'COLUMN'
884 , p_token_value => 'Last_Update_Date') ;
885 RAISE FND_API.G_EXC_ERROR;
886 End if;
887 -- Check Whether record has been changed by someone else
888 If (l_tar_mo_rec.object_version_number <> l_ref_mo_rec.object_version_number) Then
889 OZF_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED'
890 , p_token_name => 'INFO'
891 , p_token_value => 'Ozf_Market_Options') ;
892 RAISE FND_API.G_EXC_ERROR;
893 End if;
894 -- validate
895 validate_market_options
896 (
897 p_api_version_number => p_api_version_number
898 , p_init_msg_list => p_init_msg_list
899 , p_validation_level => p_validation_level
900 , p_validation_mode => JTF_PLSQL_API.g_update
901 , x_return_status => x_return_status
902 , x_msg_count => x_msg_count
903 , x_msg_data => x_msg_data
904 , p_mo_rec => l_tar_mo_rec
905 );
906 -- update
907 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
908 RAISE FND_API.G_EXC_ERROR;
909 END IF;
910
911 OZF_OFFR_MARKET_OPTION_PKG.Update_Row(
912 p_offer_market_option_id => l_tar_mo_rec.offer_market_option_id
913 , p_offer_id => l_tar_mo_rec.offer_id
914 , p_qp_list_header_id => l_tar_mo_rec.qp_list_header_id
915 , p_group_number => l_tar_mo_rec.group_number
916 , p_retroactive_flag => l_tar_mo_rec.retroactive_flag
917 , p_beneficiary_party_id => l_tar_mo_rec.beneficiary_party_id
918 , p_combine_schedule_flag => l_tar_mo_rec.combine_schedule_flag
919 , p_volume_tracking_level_code => l_tar_mo_rec.volume_tracking_level_code
920 , p_accrue_to_code => l_tar_mo_rec.accrue_to_code
921 , p_precedence => l_tar_mo_rec.precedence
922 , p_object_version_number => l_tar_mo_rec.object_version_number
923 , p_creation_date => SYSDATE
924 , p_created_by => FND_GLOBAL.USER_ID
925 , p_last_updated_by => FND_GLOBAL.USER_ID
926 , p_last_update_date => SYSDATE
927 , p_last_update_login => FND_GLOBAL.conc_login_id
928 );
929
930 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
931 RAISE FND_API.G_EXC_ERROR;
932 END IF;
933
934 IF FND_API.to_Boolean( p_commit )
935 THEN
936 COMMIT WORK;
937 END IF;
938 -- Debug Message
939 OZF_Volume_Offer_disc_PVT.debug_message('Private API: ' || l_api_name || 'end');
940 -- Standard call to get message count and if count is 1, get message info.
941 FND_MSG_PUB.Count_And_Get
942 (p_count => x_msg_count,
943 p_data => x_msg_data
944 );
945
946 -- exception
947 EXCEPTION
948
949 WHEN OZF_Utility_PVT.resource_locked THEN
950 x_return_status := FND_API.g_ret_sts_error;
951 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
952
953 WHEN FND_API.G_EXC_ERROR THEN
954 ROLLBACK TO Update_market_options_pvt;
955 x_return_status := FND_API.G_RET_STS_ERROR;
956 -- Standard call to get message count and if count=1, get the message
957 FND_MSG_PUB.Count_And_Get (
958 p_encoded => FND_API.G_FALSE,
959 p_count => x_msg_count,
960 p_data => x_msg_data
961 );
962
963 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
964 ROLLBACK TO Update_market_options_pvt;
965 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
966 -- Standard call to get message count and if count=1, get the message
967 FND_MSG_PUB.Count_And_Get (
968 p_encoded => FND_API.G_FALSE,
969 p_count => x_msg_count,
970 p_data => x_msg_data
971 );
972
973 WHEN OTHERS THEN
974 ROLLBACK TO Update_market_options_pvt;
975 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
976 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
977 THEN
978 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
979 END IF;
980 -- Standard call to get message count and if count=1, get the message
981 FND_MSG_PUB.Count_And_Get (
985 );
982 p_encoded => FND_API.G_FALSE,
983 p_count => x_msg_count,
984 p_data => x_msg_data
986
987 END Update_market_options;
988
989
990 -- ==============================================================================
991 -- Start of Comments
992 -- ==============================================================================
993 -- API Name
994 -- Delete_market_options
995 -- Type
996 -- Private
997 -- Pre-Req
998 -- Parameters
999 --
1000 -- IN
1001 -- p_api_version_number IN NUMBER
1002 -- p_init_msg_list IN VARCHAR2
1003 -- p_commit IN VARCHAR2
1004 -- p_validation_level IN NUMBER
1005 -- p_offer_market_option_id IN NUMBER
1006 -- p_object_version_number IN NUMBER
1007
1008 --
1009 -- OUT
1010 -- x_return_status OUT NOCOPY VARCHAR2
1011 -- x_msg_count OUT NOCOPY NUMBER
1012 -- x_msg_data OUT NOCOPY VARCHAR2
1013
1014 -- Version : Current version 1.0
1015 --
1016 -- History
1017 -- Mon Jun 20 2005:7/55 PM Created
1018 --
1019 -- Description
1020 -- End of Comments
1021 -- ==============================================================================
1022 PROCEDURE Delete_market_options(
1023 p_api_version_number IN NUMBER,
1024 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1025 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1026 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1027 x_return_status OUT NOCOPY VARCHAR2,
1028 x_msg_count OUT NOCOPY NUMBER,
1029 x_msg_data OUT NOCOPY VARCHAR2,
1030 p_offer_market_option_id IN NUMBER,
1031 p_object_version_number IN NUMBER
1032 )
1033 IS
1034 l_api_name CONSTANT VARCHAR2(30) := 'Delete_market_options';
1035 l_api_version_number CONSTANT NUMBER := 1.0;
1036 l_object_version_number NUMBER;
1037 BEGIN
1038 -- Standard Start of API savepoint
1039 SAVEPOINT Delete_market_options_PVT;
1040
1041 -- Standard call to check for call compatibility.
1042 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1043 p_api_version_number,
1044 l_api_name,
1045 G_PKG_NAME)
1046 THEN
1047 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1048 END IF;
1049
1050 -- Initialize message list if p_init_msg_list is set to TRUE.
1051 IF FND_API.to_Boolean( p_init_msg_list )
1052 THEN
1053 FND_MSG_PUB.initialize;
1054 END IF;
1055
1056 -- Initialize API return status to SUCCESS
1057 x_return_status := FND_API.G_RET_STS_SUCCESS;
1058
1059 --
1060 -- Api body
1061 --
1062
1063 -- Invoke table handler(OZF_Promotional_Offers_PKG.Delete_Row)
1064 OZF_OFFR_MARKET_OPTION_PKG.Delete_row(
1065 p_offer_market_option_id => p_offer_market_option_id
1066 , p_object_version_number => p_object_version_number
1067 );
1068
1069 --
1070 -- End of API body
1071 --
1072
1073 -- Standard check for p_commit
1074 IF FND_API.to_Boolean( p_commit )
1075 THEN
1076 COMMIT WORK;
1077 END IF;
1078
1079 -- Standard call to get message count and if count is 1, get message info.
1080 FND_MSG_PUB.Count_And_Get
1081 (p_count => x_msg_count,
1082 p_data => x_msg_data
1083 );
1084 EXCEPTION
1085
1086 WHEN OZF_Utility_PVT.resource_locked THEN
1087 x_return_status := FND_API.g_ret_sts_error;
1088 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
1089
1090 WHEN FND_API.G_EXC_ERROR THEN
1091 ROLLBACK TO Delete_market_options_PVT;
1092 x_return_status := FND_API.G_RET_STS_ERROR;
1093 -- Standard call to get message count and if count=1, get the message
1094 FND_MSG_PUB.Count_And_Get (
1095 p_encoded => FND_API.G_FALSE,
1096 p_count => x_msg_count,
1097 p_data => x_msg_data
1098 );
1099
1100 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1101 ROLLBACK TO Delete_market_options_PVT;
1102 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1103 -- Standard call to get message count and if count=1, get the message
1104 FND_MSG_PUB.Count_And_Get (
1105 p_encoded => FND_API.G_FALSE,
1106 p_count => x_msg_count,
1107 p_data => x_msg_data
1108 );
1109
1110 WHEN OTHERS THEN
1111 ROLLBACK TO Delete_market_options_PVT;
1112 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1113 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1114 THEN
1115 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1116 END IF;
1117 -- Standard call to get message count and if count=1, get the message
1118 FND_MSG_PUB.Count_And_Get (
1119 p_encoded => FND_API.G_FALSE,
1120 p_count => x_msg_count,
1121 p_data => x_msg_data
1122 );
1123
1124 END Delete_market_options;
1125
1126
1127 END OZF_offer_Market_Options_PVT;
1128