[Home] [Help]
PACKAGE BODY: APPS.OZF_VOLUME_OFFER_QUAL_PVT
Source
1 PACKAGE BODY OZF_Volume_Offer_Qual_PVT AS
2 /* $Header: ozfvvoqb.pls 120.7 2005/10/11 13:55:53 rssharma noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_Volume_Offer_Qual_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvvoqb.pls';
6
7 OZF_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
8 OZF_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
9 OZF_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
10
11
12 PROCEDURE debug_message(p_message IN VARCHAR2)
13 IS
14 BEGIN
15 IF (OZF_DEBUG_HIGH_ON) THEN
16 ozf_utility_pvt.debug_message(p_message);
17 END IF;
18 END debug_message;
19
20
21 PROCEDURE populate_mkt_option_qual_intf(
22 p_mo_rec IN OZF_offer_Market_Options_PVT.vo_mo_rec_type
23 , p_qualifiers_rec qp_qualifier_rules_pub.qualifiers_rec_type
24 , p_qual_mo_rec IN OUT NOCOPY OZF_QUAL_MARKET_OPTION_PVT.qual_mo_rec_type
25 , x_return_status OUT NOCOPY VARCHAR2
26 , x_msg_count OUT NOCOPY NUMBER
27 , x_msg_data OUT NOCOPY VARCHAR2
28 )
29 IS
30 BEGIN
31 x_return_status := FND_API.G_RET_STS_SUCCESS;
32 IF create_mo_for_group(p_qualifiers_rec.qualifier_grouping_no, p_qualifiers_rec.list_header_id) = 'N' THEN
33 IF get_market_option_id(p_qualifiers_rec.qualifier_grouping_no, p_qualifiers_rec.list_header_id) = -1 THEN
34 OZF_Utility_PVT.Error_Message('OZF_OFFR_INV_MO');
35 x_return_status := FND_API.g_ret_sts_error;
36 END IF;
37 p_qual_mo_rec.offer_market_option_id := get_market_option_id(p_qualifiers_rec.qualifier_grouping_no, p_qualifiers_rec.list_header_id);
38 ELSE
39 p_qual_mo_rec.offer_market_option_id := p_mo_rec.offer_market_option_id;
40 END IF;
41 p_qual_mo_rec.qp_qualifier_id := p_qualifiers_rec.qualifier_id;
42 END populate_mkt_option_qual_intf;
43
44 PROCEDURE populate_deflt_mkt_options(
45 p_mo_rec IN OUT NOCOPY OZF_offer_Market_Options_PVT.vo_mo_rec_type
46 , p_qualifiers_rec qp_qualifier_rules_pub.qualifiers_rec_type
47 , x_return_status OUT NOCOPY VARCHAR2
48 , x_msg_count OUT NOCOPY NUMBER
49 , x_msg_data OUT NOCOPY VARCHAR2
50 )
51 IS
52 CURSOR c_offer_id(p_qp_list_header_id NUMBER) IS
53 SELECT offer_id FROM ozf_offers
54 WHERE qp_list_header_id = p_qp_list_header_id
55 AND offer_type = 'VOLUME_OFFER';
56
57 l_offer_id NUMBER;
58
59 BEGIN
60 x_return_status := FND_API.G_RET_STS_SUCCESS;
61 OPEN c_offer_id(p_qualifiers_rec.list_header_id);
62 FETCH c_Offer_id INTO l_offer_id;
63 IF (c_offer_id%NOTFOUND) THEN
64 OZF_UTILITY_PVT.Error_message('OZF_OFFR_INV_OFFER_ID');
65 x_return_status := FND_API.G_RET_STS_ERROR;
66 END IF;
67 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
68 RAISE FND_API.G_EXC_ERROR;
69 END IF;
70 p_mo_rec.offer_id := l_offer_id;
71 p_mo_rec.qp_list_header_id := p_qualifiers_rec.list_header_id;
72 p_mo_rec.group_number := p_qualifiers_rec.qualifier_grouping_no;
73 p_mo_rec.retroactive_flag := FND_PROFILE.VALUE('OZF_VO_RETROACTIVE');
74 -- l_mo_rec.beneficiary_party_id := null;;
75 p_mo_rec.combine_schedule_flag := FND_PROFILE.VALUE('OZF_VO_COMBINE_DISCOUNTS_FLAG');
76 p_mo_rec.volume_tracking_level_code := FND_PROFILE.VALUE('OZF_VO_VOLUME_TRACKING_LEVEL');
77 p_mo_rec.accrue_to_code := FND_PROFILE.VALUE('OZF_VO_ACCRUE_TO');
78 p_mo_rec.precedence := p_qualifiers_rec.qualifier_grouping_no;
79
80 debug_message('Offer Id is : '||p_mo_rec.offer_id);
81 END populate_deflt_mkt_options;
82
83
84
85 FUNCTION create_mo_for_group(
86 p_group_number NUMBER
87 , p_qp_list_header_id NUMBER
88 ) RETURN VARCHAR2
89 IS
90 l_return VARCHAR2(1) := 'Y';
91 CURSOR c_create_mo(p_group_number NUMBER, p_qp_list_header_id NUMBER) IS
92 SELECT 'N' FROM dual WHERE EXISTS(SELECT 'X' FROM ozf_offr_market_options WHERE group_number = p_group_number and qp_list_header_id = p_qp_list_header_id);
93 BEGIN
94 OPEN c_create_mo(p_group_number,p_qp_list_header_id);
95 FETCH c_create_mo INTO l_return;
96 IF(c_create_mo%NOTFOUND) THEN
97 l_return := 'Y';
98 END IF;
99 CLOSE c_create_mo;
100 RETURN l_return;
101 END create_mo_for_group;
102
103 FUNCTION get_market_option_id(p_group_number NUMBER,p_qp_list_header_id NUMBER)
104 RETURN NUMBER
105 IS
106 CURSOR c_mkt_option_id(p_group_number NUMBER,p_qp_list_header_id NUMBER)IS
107 SELECT offer_market_option_id FROM ozf_offr_market_options WHERE qp_list_header_id = p_qp_list_header_id AND group_number = p_group_number;
108 l_market_option_id NUMBER := -1;
109 BEGIN
110 OPEN c_mkt_option_id(p_group_number ,p_qp_list_header_id);
111 FETCH c_mkt_option_id INTO l_market_option_id;
112 IF (c_mkt_option_id%NOTFOUND) THEN
113 l_market_option_id := -1;
114 END IF;
115 CLOSE c_mkt_option_id;
116 RETURN l_market_option_id;
117 END get_market_option_id;
118
119 PROCEDURE create_vo_qualifier
120 (
121 p_api_version_number IN NUMBER
122 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
123 , p_commit IN VARCHAR2 := FND_API.G_FALSE
124 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
125
126 , x_return_status OUT NOCOPY VARCHAR2
127 , x_msg_count OUT NOCOPY NUMBER
128 , x_msg_data OUT NOCOPY VARCHAR2
129
130 , p_qualifiers_rec IN OZF_OFFER_PVT.qualifiers_Rec_Type
131 )
132 IS
133 x_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
134 l_api_version_number CONSTANT NUMBER := 1.0;
135 l_api_name CONSTANT VARCHAR2(30) := 'create_vo_qualifier';
136 l_qualifiers_tbl OZF_OFFER_PVT.QUALIFIERS_TBL_TYPE;
137 l_qualifiers_rec OZF_OFFER_PVT.QUALIFIERS_REC_TYPE;
138 x_error_location NUMBER;
139 l_mo_id NUMBER;
140 l_Mo_rec OZF_offer_Market_Options_PVT.vo_mo_rec_type;
141
142 l_qual_mo_rec OZF_QUAL_MARKET_OPTION_PVT.qual_mo_rec_type;
143 l_qual_market_option_id NUMBER;
144 BEGIN
145 --initialize
146 -- Standard Start of API savepoint
147 SAVEPOINT create_volume_offer_qual_pvt;
148
149 -- Standard call to check for call compatibility.
150 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
151 p_api_version_number,
152 l_api_name,
153 G_PKG_NAME)
154 THEN
155 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
156 END IF;
157
158 -- Initialize message list if p_init_msg_list is set to TRUE.
159 IF FND_API.to_Boolean( p_init_msg_list )
160 THEN
161 FND_MSG_PUB.initialize;
162 END IF;
163
164 -- Debug Message
165 debug_message('Private API: ' || l_api_name || 'start');
166
167 -- Initialize API return status to SUCCESS
168 x_return_status := FND_API.G_RET_STS_SUCCESS;
169
170 -- =========================================================================
171 -- Validate Environment
172 -- =========================================================================
173
174 IF FND_GLOBAL.USER_ID IS NULL
175 THEN
176 OZF_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
177 RAISE FND_API.G_EXC_ERROR;
178 END IF;
179 -- create market eligibility
180 l_qualifiers_tbl(1) := p_qualifiers_rec;
181
182
183 -- check if offer is volume offer
184
185 -- logic for creating qualifier
186 -- 1. Create a market eligibility.
187 -- 2. For the newly created market Eligibility , check if market option exists ie. does market option exist for the group
188 -- the qualifier belongs to
189 -- if market option already exists , get the market option id and create a qualifier market option relation.
190 -- if market optoin does not exist for the group, create the market option , get the market option id and then
191 -- create a market option-qualifier relation
192
193 l_qualifiers_tbl(1).operation := 'CREATE';
194 OZF_OFFER_PVT.process_market_qualifiers
195 (
196 p_init_msg_list => p_init_msg_list
197 ,p_api_version => p_api_version_number
198 ,p_commit => p_commit
199 ,x_return_status => x_return_status
200 ,x_msg_count => x_msg_count
201 ,x_msg_data => x_msg_data
202 ,p_qualifiers_tbl => l_qualifiers_tbl
203 ,x_error_location => x_error_location
204 ,x_qualifiers_tbl => x_qualifiers_tbl
205 );
206
207 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
208 RAISE FND_API.G_EXC_ERROR;
209 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
210 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
211 END IF;
212
213 debug_message('Return count is : '||x_qualifiers_tbl.count);
214
215 FOR i in 1 .. x_qualifiers_tbl.count
216 LOOP
217 debug_message('QUalifierId is : '||x_qualifiers_tbl(i).qualifier_id);
218 debug_message('List Header Id is : '||x_qualifiers_tbl(i).list_header_id);
219
220
221
222 IF x_qualifiers_tbl(i).qualifier_context IN ('CUSTOMER','CUSTOMER_GROUP','TERRITORY','SOLD_BY') THEN
223 IF create_mo_for_group(p_group_number => x_qualifiers_tbl(i).qualifier_grouping_no,p_qp_list_header_id => x_qualifiers_tbl(i).list_header_id) = 'Y'
224 THEN
225 populate_deflt_mkt_options(
226 p_mo_rec => l_mo_rec
227 , p_qualifiers_rec => x_qualifiers_tbl(i)
228 , x_return_status => x_return_status
229 , x_msg_count => x_msg_count
230 , x_msg_data => x_msg_data
231 );
232
233 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
234 RAISE FND_API.G_EXC_ERROR;
235 END IF;
236
237 OZF_offer_Market_Options_PVT.Create_market_options
238 (
239 p_api_version_number => p_api_version_number
240 , p_init_msg_list => p_init_msg_list
241 , p_commit => p_commit
242 , p_validation_level => p_validation_level
243
244 , x_return_status => x_return_status
245 , x_msg_count => x_msg_count
246 , x_msg_data => x_msg_data
247
248 , p_mo_rec => l_mo_rec
249 , x_vo_market_option_id => l_mo_id
250 );
251
252 debug_message('Market option id is : '|| l_mo_id);
253 l_mo_rec.offer_market_option_id := l_mo_id;
254
255 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
256 RAISE FND_API.G_EXC_ERROR;
257 END IF;
258
259 END IF;
260
261 populate_mkt_option_qual_intf(
262 p_mo_rec => l_mo_rec
263 , p_qualifiers_rec => x_qualifiers_tbl(i)
264 , p_qual_mo_rec => l_qual_mo_rec
265 , x_return_status => x_return_status
266 , x_msg_count => x_msg_count
267 , x_msg_data => x_msg_data
268 );
269
270 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
271 RAISE FND_API.G_EXC_ERROR;
272 END IF;
273
274 OZF_QUAL_MARKET_OPTION_PVT.Create_qual_market_options(
275 p_api_version_number => p_api_version_number
276 , p_init_msg_list => p_init_msg_list
277 , p_commit => p_commit
278 , p_validation_level => p_validation_level
279
280 , x_return_status => x_return_status
281 , x_msg_count => x_msg_count
282 , x_msg_data => x_msg_data
283
284 , p_qual_mo_rec => l_qual_mo_rec
285 , x_qual_market_option_id => l_qual_market_option_id
286 );
287
288 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
289 RAISE FND_API.G_EXC_ERROR;
290 END IF;
291 END IF;
292
293 END LOOP;
294
295 -- commit
296 Fnd_Msg_Pub.Count_AND_Get
297 ( p_count => x_msg_count,
298 p_data => x_msg_data,
299 p_encoded => Fnd_Api.G_FALSE
300 );
301 IF p_commit = Fnd_Api.g_true THEN
302 COMMIT WORK;
303 END IF;
304
305 --exception
306 EXCEPTION
307 WHEN Fnd_Api.G_EXC_ERROR THEN
308 x_return_status := Fnd_Api.g_ret_sts_error ;
309 ROLLBACK TO create_volume_offer_qual_pvt;
310 Fnd_Msg_Pub.Count_AND_Get
311 ( p_count => x_msg_count,
312 p_data => x_msg_data,
313 p_encoded => Fnd_Api.G_FALSE
314 );
315 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
316 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
317 ROLLBACK TO create_volume_offer_qual_pvt;
318 Fnd_Msg_Pub.Count_AND_Get
319 ( p_count => x_msg_count,
320 p_data => x_msg_data,
321 p_encoded => Fnd_Api.G_FALSE
322 );
323 WHEN OTHERS THEN
324 x_return_status := Fnd_Api.g_ret_sts_unexp_erroR ;
325 ROLLBACK TO create_volume_offer_qual_pvt;
326 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
327 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
328 END IF;
329 Fnd_Msg_Pub.Count_AND_Get
330 ( p_count => x_msg_count,
331 p_data => x_msg_data,
332 p_encoded => Fnd_Api.G_FALSE
333 );
334
335 END create_vo_qualifier;
336
337 FUNCTION get_group_members(p_qp_list_header_id NUMBER, p_group_number NUMBER)
338 RETURN VARCHAR2
339 IS
340 --l_operation VARCHAR2(30);
341 CURSOR c_group_members_exist(p_qp_list_header_id NUMBER,p_group_number NUMBER) IS
342 SELECT 'Y' FROM dual WHERE EXISTS (SELECT 'X' FROM qp_qualifiers WHERE list_header_id = p_qp_list_header_id AND qualifier_grouping_no = p_group_number);
343 l_grp_mem_exist VARCHAR2(1) := 'A';
344 BEGIN
345 OPEN c_group_members_exist(p_qp_list_header_id,p_group_number);
346 FETCH c_group_members_exist INTO l_grp_mem_exist;
347 IF (c_group_members_exist%NOTFOUND) THEN
348 l_grp_mem_exist := 'N';
349 END IF;
350 CLOSE c_group_members_exist;
351
352 RETURN l_grp_mem_exist;
353
354 END get_group_members;
355
356
357 /*
358 Logic followed is
359 1. BEFORE update get the old group number of the qualifier
360 check if members exist in the new group of the qualifier
361 2. Update the qualifier
362 3. check if members exist in the old group of the
363 4. If members did not exist in new group before creation of the qualifier
364 create a new market option
365 5. If members no longer exist in the old group delete the market option associated with the old group
366 6.Update the Market OPtion - QUalifier interface table to point to the new market_option
367 */
368 PROCEDURE update_vo_qualifier
369 (
370 p_api_version_number IN NUMBER
371 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
372 , p_commit IN VARCHAR2 := FND_API.G_FALSE
373 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
374
375 , x_return_status OUT NOCOPY VARCHAR2
376 , x_msg_count OUT NOCOPY NUMBER
377 , x_msg_data OUT NOCOPY VARCHAR2
378
379 , p_qualifiers_rec IN OZF_OFFER_PVT.qualifiers_Rec_Type
380 )
381 IS
382 l_api_name CONSTANT VARCHAR2(30) := 'update_vo_qualifier';
383 l_api_version_number CONSTANT NUMBER := 1.0;
384 l_qualifiers_tbl OZF_OFFER_PVT.qualifiers_tbl_Type;
385 x_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
386 x_error_location NUMBER;
387 l_group_number NUMBER := -5000;
388 l_operation VARCHAR2(30);
389 l_old_group_members_exist VARCHAR2(1);
390 l_new_group_members_exist VARCHAR2(1);
391
392 CURSOR c_group_number(p_qp_qualifier_id NUMBER) IS
393 SELECT qualifier_grouping_no FROM qp_qualifiers WHERE qualifier_id = p_qp_qualifier_id;
394
395 CURSOR c_mkt_opt_dtail(p_qp_qualifier_id NUMBER)
396 IS
397 SELECT offer_market_option_id, object_version_number FROM ozf_offr_market_options
398 WHERE offer_market_option_id = (SELECT offer_market_option_id FROM ozf_qualifier_market_option WHERE qp_qualifier_id = p_qp_qualifier_id);
399
400 l_mkt_opt_dtail c_mkt_opt_dtail%ROWTYPE;
401 l_mkt_option_found VARCHAR2(1) := 'N';
402 l_mo_id NUMBER;
403 l_mo_rec OZF_offer_Market_Options_PVT.vo_mo_rec_type;
404 l_qual_mo_rec OZF_QUAL_MARKET_OPTION_PVT.qual_mo_rec_type;
405
406 CURSOR c_qual_mo(p_qualifier_id NUMBER) IS
407 SELECT * FROM ozf_qualifier_market_option
408 WHERE qp_qualifier_id = p_qualifier_id;
409
410 l_qual_mo c_qual_mo%ROWTYPE;
411
412 CURSOR c_market_option_id(p_qp_list_header_id NUMBER, p_group_number NUMBER) IS
413 SELECT offer_market_option_id FROM ozf_offr_market_options WHERE qp_list_header_id = p_qp_list_header_id
414 AND group_number = p_group_number;
415
416 l_market_option_id NUMBER;
417
418 BEGIN
419 -- initialize
420 SAVEPOINT update_volume_offer_qual_pvt;
421 -- Standard call to check for call compatibility.
422 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
423 p_api_version_number,
424 l_api_name,
425 G_PKG_NAME)
426 THEN
427 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
428 END IF;
429 -- Initialize message list if p_init_msg_list is set to TRUE.
430 IF FND_API.to_Boolean( p_init_msg_list )
431 THEN
432 FND_MSG_PUB.initialize;
433 END IF;
434 -- Debug Message
435 debug_message('Private API: ' || l_api_name || 'start');
436
437 -- Initialize API return status to SUCCESS
438 x_return_status := FND_API.G_RET_STS_SUCCESS;
439 -- update
440 l_qualifiers_tbl(1) := p_qualifiers_rec;
441
442 -- get old group number
443 OPEN c_group_number(l_qualifiers_tbl(1).qualifier_id);
444 FETCH c_group_number INTO l_group_number;
445 IF c_group_number%NOTFOUND THEN
446 l_group_number := -1000;
447 END IF;
448 CLOSE c_group_number;
449
450 -- check if members exist in the new group
451 l_new_group_members_exist := get_group_members(l_qualifiers_tbl(1).list_header_id,l_qualifiers_tbl(1).qualifier_grouping_no );
452
453 l_qualifiers_tbl(1).operation := 'UPDATE';
454 -- update the qualifier
455 debug_message('Calling update');
456 OZF_OFFER_PVT.process_market_qualifiers
457 (
458 p_init_msg_list => p_init_msg_list
459 ,p_api_version => p_api_version_number
460 ,p_commit => p_commit
461 ,x_return_status => x_return_status
462 ,x_msg_count => x_msg_count
463 ,x_msg_data => x_msg_data
464 ,p_qualifiers_tbl => l_qualifiers_tbl
465 ,x_error_location => x_error_location
466 ,x_qualifiers_tbl => x_qualifiers_tbl
467 );
468
469
470 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
471 RAISE FND_API.G_EXC_ERROR;
472 END IF;
473
474 -- check if members still exist in the old group
475 l_old_group_members_exist := get_group_members(l_qualifiers_tbl(1).list_header_id,l_group_number );
476
477 FOR i in 1 .. x_qualifiers_tbl.count
478 LOOP
479 debug_message('QUalifier group nos are : old'||l_group_number || ' : NEW : '||x_qualifiers_tbl(i).qualifier_grouping_no);
480
481 -- get market option details of the old group using data in the interface table
482
483 -- do all processing to sync market options and interface table only id the group no. is updated
484 IF x_qualifiers_tbl(i).qualifier_context IN ('CUSTOMER','CUSTOMER_GROUP','TERRITORY','SOLD_BY') THEN
485
486 IF l_group_number <> p_qualifiers_rec.qualifier_grouping_no THEN
487
488 OPEN c_mkt_opt_dtail(x_qualifiers_tbl(i).qualifier_id);
489 FETCH c_mkt_opt_dtail INTO l_mkt_opt_dtail;
490
491 IF (c_mkt_opt_dtail%NOTFOUND) THEN
492 l_mkt_option_found := 'N';
493 ELSE
494 l_mkt_option_found := 'Y';
495 END IF;
496 CLOSE c_mkt_opt_dtail;
497
498 debug_message('Market OPtion Found is : '||l_mkt_option_found);
499 debug_message('Old Group members exist : '||l_old_group_members_exist);
500 debug_message('New Group members exist : '||l_new_group_members_exist);
501
502
503 IF l_mkt_option_found <> 'N' THEN
504 -- if members do not exist in the old group delete the market option associated with the old group
505 IF l_old_group_members_exist = 'N' THEN
506 debug_message('Deleting old market option');
507 OZF_offer_Market_Options_PVT.Delete_market_options(
508 p_api_version_number => p_api_version_number
509 , p_init_msg_list => p_init_msg_list
510 , p_commit => p_commit
511 , p_validation_level => p_validation_level
512 , x_return_status => x_return_status
513 , x_msg_count => x_msg_count
514 , x_msg_data => x_msg_data
515 , p_offer_market_option_id => l_mkt_opt_dtail.offer_market_option_id
516 , p_object_version_number => l_mkt_opt_dtail.object_version_number
517 );
518 END IF;
519 END IF;
520 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
521 RAISE FND_API.G_EXC_ERROR;
522 END IF;
523
524 -- if members did not exist in the new group before the creation of the offer create new market option
525 IF l_new_group_members_exist = 'N' THEN
526 debug_message('Creating new market options');
527 populate_deflt_mkt_options(
528 p_mo_rec => l_mo_rec
529 , p_qualifiers_rec => x_qualifiers_tbl(i)
530 , x_return_status => x_return_status
531 , x_msg_count => x_msg_count
532 , x_msg_data => x_msg_data
533 );
534
535 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
536 RAISE FND_API.G_EXC_ERROR;
537 END IF;
538
539 OZF_offer_Market_Options_PVT.Create_market_options
540 (
541 p_api_version_number => p_api_version_number
542 , p_init_msg_list => p_init_msg_list
543 , p_commit => p_commit
544 , p_validation_level => p_validation_level
545
546 , x_return_status => x_return_status
547 , x_msg_count => x_msg_count
548 , x_msg_data => x_msg_data
549
550 , p_mo_rec => l_mo_rec
551 , x_vo_market_option_id => l_mo_id
552 );
553
554 debug_message('Market option id is : '|| l_mo_id);
555 l_mo_rec.offer_market_option_id := l_mo_id;
556
557 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
558 RAISE FND_API.G_EXC_ERROR;
559 END IF;
560
561 END IF; -- END IF l_new_group_members_exist = 'N'
562
563 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
564 RAISE FND_API.G_EXC_ERROR;
565 END IF;
566
567 -- get the interface details for the qualifier id
568 OPEN c_qual_mo(p_qualifiers_rec.qualifier_id);
569 FETCH c_qual_mo INTO l_qual_mo;
570 l_qual_mo_rec.qp_qualifier_id := l_qual_mo.qp_qualifier_id;
571 l_qual_mo_rec.qualifier_market_option_id := l_qual_mo.qualifier_market_option_id;
572 l_qual_mo_rec.object_version_number := l_qual_mo.object_version_number;
573 CLOSE c_qual_mo;
574
575 -- get the market option id for the group no
576 -- -1 indicates that the group no for the QUalifier is -1 and qualifier-mo-interface exists for the qualifier
577 -- the interface record is to be updated to -1 as the qualifier id
578 -- -2 indicates that the group no for the qualifier is not -1 and no market option record exists for the qualifier
579 -- this is a anomaly and raises error
580 OPEN c_market_option_id(p_qualifiers_rec.list_header_id , p_qualifiers_rec.qualifier_grouping_no);
581 FETCH c_market_option_id INTO l_market_option_id;
582 IF (c_market_option_id%NOTFOUND) THEN
583 IF p_qualifiers_rec.qualifier_grouping_no = -1 THEN
584 l_market_option_id := -1;
585 ELSE
586 l_market_option_id := -2;
587 END IF;
588 END IF;
589 CLOSE c_market_option_id;
590 -- if market option exists for the group update the reference to the market option in the interface table
591 IF l_market_option_id <> -2 THEN
592 l_qual_mo_rec.offer_market_option_id := l_market_option_id;
593 debug_message('uupdating interface');
594
595 OZF_QUAL_MARKET_OPTION_PVT.update_qual_market_options(
596 p_api_version_number => p_api_version_number
597 , p_init_msg_list => p_init_msg_list
598 , p_commit => p_commit
599 , p_validation_level => p_validation_level
600
601 , x_return_status => x_return_status
602 , x_msg_count => x_msg_count
603 , x_msg_data => x_msg_data
604
605 , p_qual_mo_rec => l_qual_mo_rec
606 );
607
608 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
609 RAISE FND_API.G_EXC_ERROR;
610 END IF;
611
612 ELSE
613 OZF_Utility_PVT.Error_Message('OZF_CANT_UPDATE_INT');
614 x_return_status := FND_API.g_ret_sts_error;
615 END IF; -- END IF l_market_option_id <> -1
616
617 END IF; -- end if group nos are different
618 END IF; -- if qualifiers are in contexts CUSTOMER, CUSTOMER_GROUP, TERRITORY, SOLD_BY
619
620 END LOOP;
621
622 -- commit;
623 Fnd_Msg_Pub.Count_AND_Get
624 ( p_count => x_msg_count,
625 p_data => x_msg_data,
626 p_encoded => Fnd_Api.G_FALSE
627 );
628 IF p_commit = Fnd_Api.g_true THEN
629 COMMIT WORK;
630 END IF;
631
632 --exception
633 EXCEPTION
634 WHEN Fnd_Api.G_EXC_ERROR THEN
635 x_return_status := Fnd_Api.g_ret_sts_error ;
636 ROLLBACK TO update_volume_offer_qual_pvt;
637 Fnd_Msg_Pub.Count_AND_Get
638 ( p_count => x_msg_count,
639 p_data => x_msg_data,
640 p_encoded => Fnd_Api.G_FALSE
641 );
642 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
643 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
644 ROLLBACK TO update_volume_offer_qual_pvt;
645 Fnd_Msg_Pub.Count_AND_Get
646 ( p_count => x_msg_count,
647 p_data => x_msg_data,
648 p_encoded => Fnd_Api.G_FALSE
649 );
650 WHEN OTHERS THEN
651 x_return_status := Fnd_Api.g_ret_sts_unexp_erroR ;
652 ROLLBACK TO update_volume_offer_qual_pvt;
653 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
654 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
655 END IF;
656 Fnd_Msg_Pub.Count_AND_Get
657 ( p_count => x_msg_count,
658 p_data => x_msg_data,
659 p_encoded => Fnd_Api.G_FALSE
660 );
661
662 END update_vo_qualifier;
663
664
665
666 PROCEDURE Delete_vo_qualifier(
667 p_api_version_number IN NUMBER
668 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
669 , p_commit IN VARCHAR2 := FND_API.G_FALSE
670 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
671 , x_return_status OUT NOCOPY VARCHAR2
672 , x_msg_count OUT NOCOPY NUMBER
673 , x_msg_data OUT NOCOPY VARCHAR2
674 , p_qualifier_id IN NUMBER
675 )
676 IS
677 l_api_version_number CONSTANT NUMBER := 1.0;
678 l_api_name CONSTANT VARCHAR2(30) := 'Delete_volume_offer_qualifier';
679
680
681 CURSOR c_group_no(p_qualifier_id NUMBER)IS
682 SELECT qualifier_grouping_no,list_header_id FROM qp_qualifiers
683 WHERE qualifier_id = p_qualifier_id;
684 l_group_no c_group_no%ROWTYPE;
685 group_no NUMBER := 1000;
686
687 l_qualifiers_rec OZF_OFFER_PVT.qualifiers_Rec_Type;
688 l_qualifiers_tbl OZF_OFFER_PVT.qualifiers_TBL_Type;
689 x_error_location NUMBER;
690 x_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
691
692
693 CURSOR c_mkt_opt_dtail(p_qp_qualifier_id NUMBER)
694 IS
695 SELECT offer_market_option_id, object_version_number FROM ozf_offr_market_options
696 WHERE offer_market_option_id = (SELECT offer_market_option_id FROM ozf_qualifier_market_option WHERE qp_qualifier_id = p_qp_qualifier_id);
697
698 l_mkt_opt_dtail c_mkt_opt_dtail%ROWTYPE;
699 l_mkt_option_found VARCHAR2(1);
700
701 CURSOR c_qual_mo(p_qualifier_id NUMBER)
702 IS
703 SELECT qualifier_market_option_id, object_version_number FROM ozf_qualifier_market_option
704 WHERE qp_qualifier_id = p_qualifier_id;
705 l_qual_mo c_qual_mo%ROWTYPE;
706
707 CURSOR c_qualifierContext(cp_qualifierId NUMBER) IS
708 SELECT qualifier_context FROM qp_qualifiers
709 WHERE qualifier_id = cp_qualifierId;
710 l_qualifierContext VARCHAR2(30);
711 BEGIN
712 SAVEPOINT Delete_volume_offer_qual_pvt;
713 -- Standard call to check for call compatibility.
714 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
715 p_api_version_number,
716 l_api_name,
717 G_PKG_NAME)
718 THEN
719 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
720 END IF;
721 -- Initialize message list if p_init_msg_list is set to TRUE.
722 IF FND_API.to_Boolean( p_init_msg_list )
723 THEN
724 FND_MSG_PUB.initialize;
725 END IF;
726 -- Debug Message
727 debug_message('Private API: ' || l_api_name || 'start');
728
729 -- Initialize API return status to SUCCESS
730 x_return_status := FND_API.G_RET_STS_SUCCESS;
731
732
733 -- get group number
734 OPEN c_group_no(p_qualifier_id);
735 FETCH c_group_no INTO l_group_no;
736 IF (c_group_no%NOTFOUND) THEN
737 group_no := -1000;
738 END IF;
739 CLOSE c_group_no;
740 -- delete the market eligibility
741 l_qualifiers_rec.qualifier_id := p_qualifier_id;
742 l_qualifiers_rec.operation := 'DELETE';
743 l_qualifiers_tbl(1) := l_qualifiers_rec;
744
745 OZF_OFFER_PVT.process_market_qualifiers
746 (
747 p_init_msg_list => p_init_msg_list
748 ,p_api_version => p_api_version_number
749 ,p_commit => p_commit
750 ,x_return_status => x_return_status
751 ,x_msg_count => x_msg_count
752 ,x_msg_data => x_msg_data
753 ,p_qualifiers_tbl => l_qualifiers_tbl
754 ,x_error_location => x_error_location
755 ,x_qualifiers_tbl => x_qualifiers_tbl
756 );
757
758 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
759 RAISE FND_API.G_EXC_ERROR;
760 END IF;
761
762 OPEN c_qualifierContext( cp_qualifierId => p_qualifier_id);
763 FETCH c_qualifierContext INTO l_qualifierContext;
764 CLOSE c_qualifierContext;
765
766 -- Market Option is created only for Customer Qualifiers. So deleting market options and interface records is only
767 -- required if the qualifier is a Customer Qualifier
768 IF l_qualifierContext IN ('CUSTOMER','CUSTOMER_GROUP','TERRITORY','SOLD_BY') THEN
769 -- check if members still exist in the group
770 -- if no delete the market option
771 OPEN c_mkt_opt_dtail(p_qualifier_id);
772 FETCH c_mkt_opt_dtail INTO l_mkt_opt_dtail;
773
774 IF (c_mkt_opt_dtail%NOTFOUND) THEN
775 l_mkt_option_found := 'N';
776 ELSE
777 l_mkt_option_found := 'Y';
778 END IF;
779 CLOSE c_mkt_opt_dtail;
780
781 IF l_mkt_option_found = 'Y' THEN
782 debug_message('Group no is : '||group_no);
783 IF group_no <> -1000 THEN
784 IF get_group_members(l_group_no.list_header_id,l_group_no.qualifier_grouping_no ) = 'N' THEN
785 OZF_offer_Market_Options_PVT.Delete_market_options(
786 p_api_version_number => p_api_version_number
787 , p_init_msg_list => p_init_msg_list
788 , p_commit => p_commit
789 , p_validation_level => p_validation_level
790 , x_return_status => x_return_status
791 , x_msg_count => x_msg_count
792 , x_msg_data => x_msg_data
793 , p_offer_market_option_id => l_mkt_opt_dtail.offer_market_option_id
794 , p_object_version_number => l_mkt_opt_dtail.object_version_number
795 );
796
797 END IF;
798 ELSE
799 OZF_Utility_PVT.Error_Message('OZF_OFFR_MO_CANT_DELETE_MO');
800 x_return_status := FND_API.g_ret_sts_error;
801 END IF;
802 END IF;
803 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
804 RAISE FND_API.G_EXC_ERROR;
805 END IF;
806
807 -- delete the interface reference
808 OPEN c_qual_mo(p_qualifier_id);
809 FETCH c_qual_mo INTO l_qual_mo;
810 IF (c_qual_mo%NOTFOUND) THEN
811 OZF_Utility_PVT.Error_Message('OZF_OFFR_MO_INTF_REC_NOT_FOUND');
812 x_return_status := FND_API.g_ret_sts_error;
813 ELSE
814 OZF_QUAL_MARKET_OPTION_PVT.Delete_qual_market_options(
815 p_api_version_number => p_api_version_number
816 , p_init_msg_list => p_init_msg_list
817 , p_commit => p_commit
818 , p_validation_level => p_validation_level
819 , x_return_status => x_return_status
820 , x_msg_count => x_msg_count
821 , x_msg_data => x_msg_data
822 , p_qualifier_market_option_id => l_qual_mo.qualifier_market_option_id
823 , p_object_version_number => l_qual_mo.object_version_number
824 );
825
826 END IF;
827
828 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
829 RAISE FND_API.G_EXC_ERROR;
830 END IF;
831
832 END IF;
833 Fnd_Msg_Pub.Count_AND_Get
834 ( p_count => x_msg_count,
835 p_data => x_msg_data,
836 p_encoded => Fnd_Api.G_FALSE
837 );
838 IF p_commit = Fnd_Api.g_true THEN
839 COMMIT WORK;
840 END IF;
841
842 --exception
843 EXCEPTION
844 WHEN Fnd_Api.G_EXC_ERROR THEN
845 x_return_status := Fnd_Api.g_ret_sts_error ;
846 ROLLBACK TO Delete_volume_offer_qual_pvt;
847 Fnd_Msg_Pub.Count_AND_Get
848 ( p_count => x_msg_count,
849 p_data => x_msg_data,
850 p_encoded => Fnd_Api.G_FALSE
851 );
852 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
853 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
854 ROLLBACK TO Delete_volume_offer_qual_pvt;
855 Fnd_Msg_Pub.Count_AND_Get
856 ( p_count => x_msg_count,
857 p_data => x_msg_data,
858 p_encoded => Fnd_Api.G_FALSE
859 );
860 WHEN OTHERS THEN
861 x_return_status := Fnd_Api.g_ret_sts_unexp_erroR ;
862 ROLLBACK TO Delete_volume_offer_qual_pvt;
863 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
864 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
865 END IF;
866 Fnd_Msg_Pub.Count_AND_Get
867 ( p_count => x_msg_count,
868 p_data => x_msg_data,
869 p_encoded => Fnd_Api.G_FALSE
870 );
871
872
873 END Delete_vo_qualifier;
874
875 END OZF_Volume_Offer_Qual_PVT;