[Home] [Help]
PACKAGE BODY: APPS.OZF_PROMOTIONAL_OFFERS_PVT
Source
1 PACKAGE BODY OZF_Promotional_Offers_PVT as
2 /* $Header: ozfvopob.pls 120.7.12020000.2 2013/01/30 08:23:47 apyadav ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- OZF_Promotional_Offers_PVT
7 -- Purpose
8 --
9 -- History
10 -- MAY-17-2002 julou modified. See bug 2380113
11 -- 1. changed G_USER_ID to FND_GLOBAL.user_id
12 -- 2. changed G_LOGIN_ID to FND_GLOBAL.conc_login_id
13 -- 3. removed created_by and creation_date from update api
14 -- 17-Oct-2002 RSSHARMA added last_recal_date and buyer_name
15 -- last_recal_date is inserted as sysdate (when created)
16 -- 18-Oct-2002 RSSHARMA Fixed issue where the complete rec was called
17 -- later than check items.Also the completed rec was not
18 -- for validate
19 -- 24-Oct-2002 RSSHARMA Added date_qualifier_profile_value.This value is sent using
20 -- fnd_profile function and there is no place holder for it in the record
21 -- 24-OCT-2002 julou 1. defaulting last_recal_date to offer start date
22 -- 2. add activity to required check
23 -- 07-JAN-2003 julou modified to handle no object_version_number
24 -- -- Fully Accrued Budget Offers
25 -- Wed Apr 05 2006:2/27 PM RSSHARMA Fixed bug # 5142859. Pass fund_request_curr_code to table handler.
26 -- Mon May 22 2006:2/18 PM RSSHARMA Fixed bug # 5131158. In update send g_miss value for date_qualifier_profile_value
27 -- Thu Mar 03 2011 nirprasa ER # 11806714. Added percent_discount
28 -- since it is not supposed to be updated
29 -- 30-JAN-2013 apyadav Bug 14138664 - GSIAV: COPY OFFER DO NOT HAVE FLEXIBILITY TO CHOOSE THE CURRENCY
30 -- NOTE
31 --
32 -- End of Comments
33 -- ===============================================================
34
35
36 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_Promotional_Offers_PVT';
37 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvopob.pls';
38
39 -- ==============================================================
40 -- Procedure
41 -- handle_status
42 -- History
43 -- 20-APR-2001 MUSMAN created
44 --
45 -- ==============================================================
46 PROCEDURE handle_status(
47 p_user_status_id IN NUMBER,
48 x_status_code OUT NOCOPY VARCHAR2,
49 x_return_status OUT NOCOPY VARCHAR2
50 );
51 --=============================================================
52 -- Procedure
53 -- handle_status
54 -- History
55 -- 20-APR-2001 MUSMAN created
56 --
57 -- ==============================================================
58 PROCEDURE handle_status(
59 x_status_id OUT NOCOPY NUMBER,
60 x_return_status OUT NOCOPY VARCHAR2
61 );
62
63
64
65
66 -- Hint: Primary key needs to be returned.
67 PROCEDURE Create_Offers(
68 p_api_version_number IN NUMBER,
69 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
70 p_commit IN VARCHAR2 := FND_API.G_FALSE,
71 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
72
73 x_return_status OUT NOCOPY VARCHAR2,
74 x_msg_count OUT NOCOPY NUMBER,
75 x_msg_data OUT NOCOPY VARCHAR2,
76
77 p_offers_rec IN offers_rec_type := g_miss_offers_rec,
78 x_offer_id OUT NOCOPY NUMBER
79 )
80
81 IS
82 l_api_name CONSTANT VARCHAR2(30) := 'Create_Offers';
83 l_api_version_number CONSTANT NUMBER := 1.0;
84 l_object_version_number NUMBER := 1;
85 l_org_id NUMBER := FND_API.G_MISS_NUM;
86 l_offer_id NUMBER;
87 l_dummy NUMBER;
88
89 CURSOR c_id IS
90 SELECT OZF_OFFERS_s.NEXTVAL
91 FROM dual;
92
93 CURSOR c_id_exists (l_id IN NUMBER) IS
94 SELECT 1
95 FROM OZF_OFFERS
96 WHERE OFFER_ID = l_id;
97
98 l_status_code VARCHAR2(30);
99 l_status_id NUMBER;
100
101 l_offers_Rec offers_rec_type := p_offers_rec;
102 l_access_rec AMS_access_PVT.access_rec_type;
103 l_access_id NUMBER;
104
105 CURSOR c_get_start_end_date(l_list_header_id NUMBER) IS
106 SELECT start_date_active, end_date_active
107 FROM qp_list_headers_b
108 WHERE list_header_id = l_list_header_id;
109
110 CURSOR c_get_start_date IS
111 SELECT start_date_active
112 FROM qp_list_headers_b
113 WHERE list_header_id = p_offers_rec.qp_list_header_id;
114
115 l_start_date DATE;
116 l_end_date DATE;
117
118 BEGIN
119 -- Standard Start of API savepoint
120 SAVEPOINT CREATE_Offers_PVT;
121
122 -- Standard call to check for call compatibility.
123 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
124 p_api_version_number,
125 l_api_name,
126 G_PKG_NAME)
127 THEN
128 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
129 END IF;
130
131 -- Initialize message list if p_init_msg_list is set to TRUE.
132 IF FND_API.to_Boolean( p_init_msg_list )
133 THEN
134 FND_MSG_PUB.initialize;
135 END IF;
136
137 -- Initialize API return status to SUCCESS
138 x_return_status := FND_API.G_RET_STS_SUCCESS;
139
140 -- Local variable initialization
141
142 IF p_offers_rec.OFFER_ID IS NULL OR p_offers_rec.OFFER_ID = FND_API.g_miss_num THEN
143 LOOP
144 l_dummy := NULL;
145 OPEN c_id;
146 FETCH c_id INTO l_OFFER_ID;
147 CLOSE c_id;
148
149 OPEN c_id_exists(l_OFFER_ID);
150 FETCH c_id_exists INTO l_dummy;
151 CLOSE c_id_exists;
152 EXIT WHEN l_dummy IS NULL;
153 END LOOP;
154 l_offers_rec.offer_id := l_offer_id;
155 END IF;
156
157
158 IF p_offers_rec.user_status_id = FND_API.G_MISS_NUM
159 OR p_offers_rec.user_status_id IS NULL
160 THEN
161 handle_status(
162 x_status_id => l_status_id
163 ,x_return_status => x_return_status
164 );
165 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
166 RAISE FND_API.G_EXC_ERROR;
167 ELSIF x_return_status =FND_API.G_RET_STS_SUCCESS THEN
168 l_offers_rec.user_status_id := l_status_id;
169 END IF;
170 END IF;
171
172
173 -- get the status_code for the the user_status_id
174 IF l_offers_rec.user_status_id <> FND_API.G_MISS_NUM
175 AND l_offers_rec.user_status_id IS NOT NULL
176 THEN
177 handle_status(
178 p_user_status_id => l_offers_rec.user_status_id
179 ,x_status_code => l_status_code
180 ,x_return_status => x_return_status
181 );
182 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
183 RAISE FND_API.G_EXC_ERROR;
184 ELSIF x_return_status =FND_API.G_RET_STS_SUCCESS THEN
185 l_offers_rec.status_code := l_status_code;
186 END IF;
187 END IF;
188
189
190 --getting the source code for the offers
191 IF p_offers_rec.offer_code IS NULL
192 OR p_offers_rec.offer_code = FND_API.g_miss_CHAR
193 THEN
194 l_offers_rec.offer_code := AMS_SourceCode_PVT.get_new_source_code (
195 p_object_type => 'OFFR',
196 p_custsetup_id => p_offers_rec.custom_setup_id,
197 p_global_flag => FND_API.g_false
198 );
199 END IF;
200
201 -- =========================================================================
202 -- Validate Environment
203 -- =========================================================================
204
205 IF FND_GLOBAL.User_Id IS NULL
206 THEN
207 OZF_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
208 RAISE FND_API.G_EXC_ERROR;
209 END IF;
210
211 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
212 THEN
213 -- Invoke validation procedures
214 Validate_offers(
215 p_api_version_number => 1.0,
216 p_init_msg_list => FND_API.G_FALSE,
217 p_validation_level => p_validation_level,
218 p_offers_rec => l_offers_rec,
219 x_return_status => x_return_status,
220 x_msg_count => x_msg_count,
221 x_msg_data => x_msg_data);
222 END IF;
223 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
224 RAISE FND_API.G_EXC_ERROR;
225 END IF;
226
227 -- julou defaulting last_recal_date to offer start date
228 OPEN c_get_start_date;
229 FETCH c_get_start_date INTO l_offers_rec.last_recal_date;
230 CLOSE c_get_start_date;
231 -- end julou
232 --l_offers_rec.last_recal_date := sysdate;
233
234 -- Invoke table handler(OZF_Promotional_Offers_PKG.Insert_Row)
235 OZF_Promotional_Offers_PKG.Insert_Row(
236 px_offer_id => l_offer_id,
237 p_qp_list_header_id => l_offers_rec.qp_list_header_id,
238 p_offer_type => l_offers_rec.offer_type,
239 p_offer_code => l_offers_rec.offer_code,
240 p_activity_media_id => l_offers_rec.activity_media_id,
241 p_reusable => l_offers_rec.reusable,
242 p_user_status_id => l_offers_rec.user_status_id,
243 p_owner_id => l_offers_rec.owner_id,
244 p_wf_item_key => l_offers_rec.wf_item_key,
245 p_customer_reference => l_offers_rec.customer_reference,
246 p_buying_group_contact_id => l_offers_rec.buying_group_contact_id,
247 p_last_update_date => SYSDATE,
248 p_last_updated_by => FND_GLOBAL.user_id,
249 p_creation_date => SYSDATE,
250 p_created_by => FND_GLOBAL.user_id,
251 p_last_update_login => FND_GLOBAL.conc_login_id,
252 px_object_version_number => l_object_version_number,
253 p_perf_date_from => l_offers_rec.perf_date_from,
254 p_perf_date_to => l_offers_rec.perf_date_to,
255 p_status_code => l_offers_rec.status_code,
256 p_status_date => l_offers_rec.status_date,
257 p_modifier_level_code => l_offers_rec.modifier_level_code,
258 p_order_value_discount_type => l_offers_rec.order_value_discount_type,
259 p_offer_amount => l_offers_rec.offer_amount,
260 p_lumpsum_amount => l_offers_rec.lumpsum_amount,
261 p_lumpsum_payment_type => l_offers_rec.lumpsum_payment_type,
262 p_custom_setup_id => l_offers_rec.custom_setup_id,
263 p_security_group_id => l_offers_rec.security_group_id,
264 p_budget_amount_tc => l_offers_rec.budget_amount_tc,
265 p_budget_amount_fc => l_offers_rec.budget_amount_fc,
266 p_transaction_currency_Code => l_offers_rec.transaction_currency_Code ,
267 p_functional_currency_code => l_offers_rec.functional_currency_code,
268 p_distribution_type => l_offers_rec.distribution_type,
269 p_qualifier_id => l_offers_rec.qualifier_id,
270 p_qualifier_type => l_offers_rec.qualifier_type,
271 p_account_closed_flag => l_offers_rec.account_closed_flag,
272 p_budget_offer_yn => l_offers_rec.budget_offer_yn,
273 p_break_type => l_offers_rec.break_type,
274 p_retroactive => l_offers_rec.retroactive,
275 p_volume_offer_type => l_offers_rec.volume_offer_type,
276 p_confidential_flag => l_offers_rec.confidential_flag,
277 p_budget_source_type => l_offers_rec.budget_source_type,
278 p_budget_source_id => l_offers_rec.budget_source_id,
279 p_source_from_parent => l_offers_rec.source_from_parent,
280 p_buyer_name => l_offers_rec.buyer_name,
281 p_last_recal_date => l_offers_rec.last_recal_date,
282 p_date_qualifier => FND_PROFILE.value('OZF_STORE_DATE_IN_QUALIFIERS'),
283 p_autopay_flag => l_offers_rec.autopay_flag,
284 p_autopay_days => l_offers_rec.autopay_days,
285 p_autopay_method => l_offers_rec.autopay_method,
286 p_autopay_party_attr => l_offers_rec.autopay_party_attr,
287 p_autopay_party_id => l_offers_rec.autopay_party_id,
288 p_tier_level => l_offers_rec.tier_level,
289 p_na_rule_header_id => l_offers_rec.na_rule_header_id,
290 p_beneficiary_account_id => l_offers_rec.beneficiary_account_id,
291 p_sales_method_flag => l_offers_rec.sales_method_flag,
292 p_org_id => l_offers_rec.org_id,
293 p_fund_request_curr_code => nvl(l_offers_rec.transaction_currency_Code,FND_PROFILE.VALUE('JTF_PROFILE_DEFAULT_CURRENCY')),
294 /*ER11806714 added percent_discount column */
295 p_percent_discount => l_offers_rec.percent_discount
296 );
297 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
298 RAISE FND_API.G_EXC_ERROR;
299 END IF;
300
301 x_offer_id := l_offer_id;
302 -- added by julou 07/29/2002 offer security. create an entry in ams_act_access
303 OPEN c_get_start_end_date(l_offers_rec.qp_list_header_id);
304 FETCH c_get_start_end_date INTO l_start_date, l_end_date;
305 CLOSE c_get_start_end_date;
306
307 l_access_rec.act_access_to_object_id := l_offers_rec.qp_list_header_id;
308 l_access_rec.arc_act_access_to_object := 'OFFR';
309 l_access_rec.user_or_role_id := l_offers_rec.owner_id;
310 l_access_rec.arc_user_or_role_type := 'USER';
311 l_access_rec.active_from_date := l_start_date;
312 l_access_rec.active_to_date := l_end_date;
313 l_access_rec.admin_flag := 'Y';
314 l_access_rec.owner_flag := 'Y';
315
316 -- create access for the owner of the offer
317 AMS_access_PVT.create_access(
318 p_api_version => l_api_version_number,
319 p_init_msg_list => FND_API.g_false,
320 p_commit => FND_API.g_false,
321 p_validation_level => FND_API.g_valid_level_full,
322 x_return_status => x_return_status,
323 x_msg_count => x_msg_count,
324 x_msg_data => x_msg_data,
325 p_access_rec => l_access_rec,
326 x_access_id => l_access_id
327 );
328
329 -- create access for default team of the offer
330 l_access_rec.user_or_role_id := FND_PROFILE.value('OZF_DEFAULT_OFFER_TEAM');
331 IF l_access_rec.user_or_role_id IS NOT NULL THEN
332 l_access_rec.owner_flag := 'N';
333 l_access_rec.arc_user_or_role_type := 'GROUP';
334 AMS_access_PVT.create_access(
335 p_api_version => l_api_version_number,
336 p_init_msg_list => FND_API.g_false,
337 p_commit => FND_API.g_false,
338 p_validation_level => FND_API.g_valid_level_full,
339 x_return_status => x_return_status,
340 x_msg_count => x_msg_count,
341 x_msg_data => x_msg_data,
342 p_access_rec => l_access_rec,
343 x_access_id => l_access_id
344 );
345 END IF;
346
347 -- end of creating access
348
349 --
350 -- End of API body
351 --
352
353 -- Standard check for p_commit
354 IF FND_API.to_Boolean( p_commit )
355 THEN
356 COMMIT WORK;
357 END IF;
358
359 -- Standard call to get message count and if count is 1, get message info.
360 FND_MSG_PUB.Count_And_Get
361 (p_count => x_msg_count,
362 p_data => x_msg_data
363 );
364 EXCEPTION
365
366 WHEN OZF_Utility_PVT.resource_locked THEN
367 x_return_status := FND_API.g_ret_sts_error;
368 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
369
370 WHEN FND_API.G_EXC_ERROR THEN
371 ROLLBACK TO CREATE_Offers_PVT;
372 x_return_status := FND_API.G_RET_STS_ERROR;
373 -- Standard call to get message count and if count=1, get the message
374 FND_MSG_PUB.Count_And_Get (
375 p_encoded => FND_API.G_FALSE,
376 p_count => x_msg_count,
377 p_data => x_msg_data
378 );
379
380 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
381 ROLLBACK TO CREATE_Offers_PVT;
382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
383 -- Standard call to get message count and if count=1, get the message
384 FND_MSG_PUB.Count_And_Get (
385 p_encoded => FND_API.G_FALSE,
386 p_count => x_msg_count,
387 p_data => x_msg_data
388 );
389
390 WHEN OTHERS THEN
391 ROLLBACK TO CREATE_Offers_PVT;
392 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
393 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
394 THEN
395 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
396 END IF;
397 -- Standard call to get message count and if count=1, get the message
398 FND_MSG_PUB.Count_And_Get (
399 p_encoded => FND_API.G_FALSE,
400 p_count => x_msg_count,
401 p_data => x_msg_data
402 );
403 End Create_Offers;
404
405 PROCEDURE Complete_offers_Rec (
406 p_offers_rec IN offers_rec_type,
407 x_complete_rec OUT NOCOPY offers_rec_type)
408 IS
409
410 CURSOR c_complete IS
411 SELECT *
412 FROM ozf_offers
413 WHERE qp_list_header_id = p_offers_rec.qp_list_header_id;
414 l_offers_rec c_complete%ROWTYPE;
415 BEGIN
416 x_complete_rec := p_offers_rec;
417
418 OPEN c_complete;
419 FETCH c_complete INTO l_offers_rec;
420 CLOSE c_complete;
421
422 -- offer_id
423 IF p_offers_rec.offer_id = FND_API.g_miss_num THEN
424 x_complete_rec.offer_id := l_offers_rec.offer_id;
425 END IF;
426
427 -- qp_list_header_id
428 IF p_offers_rec.qp_list_header_id = FND_API.g_miss_num THEN
429 x_complete_rec.qp_list_header_id := l_offers_rec.qp_list_header_id;
430 END IF;
431
432 -- offer_type
433 IF p_offers_rec.offer_type = FND_API.g_miss_char THEN
434 x_complete_rec.offer_type := l_offers_rec.offer_type;
435 END IF;
436
437 -- offer_code
438 IF p_offers_rec.offer_code = FND_API.g_miss_char THEN
439 x_complete_rec.offer_code := l_offers_rec.offer_code;
440 END IF;
441
442 -- activity_media_id
443 IF p_offers_rec.activity_media_id = FND_API.g_miss_num THEN
444 x_complete_rec.activity_media_id := l_offers_rec.activity_media_id;
445 END IF;
446
447 -- reusable
448 IF p_offers_rec.reusable = FND_API.g_miss_char THEN
449 x_complete_rec.reusable := l_offers_rec.reusable;
450 END IF;
451
452 -- user_status_id
453 IF p_offers_rec.user_status_id = FND_API.g_miss_num THEN
454 x_complete_rec.user_status_id := l_offers_rec.user_status_id;
455 END IF;
456 -- owner_id
457 IF p_offers_rec.owner_id = FND_API.g_miss_num THEN
458 x_complete_rec.owner_id := l_offers_rec.owner_id;
459 END IF;
460 -- wf_item_key
461 IF p_offers_rec.wf_item_key = FND_API.g_miss_char THEN
462 x_complete_rec.wf_item_key := l_offers_rec.wf_item_key;
463 END IF;
464
465 -- customer_reference
466 IF p_offers_rec.customer_reference = FND_API.g_miss_char THEN
467 x_complete_rec.customer_reference := l_offers_rec.customer_reference;
468 END IF;
469
470 -- buying_group_contact_id
471 IF p_offers_rec.buying_group_contact_id = FND_API.g_miss_num THEN
472 x_complete_rec.buying_group_contact_id := l_offers_rec.buying_group_contact_id;
473 END IF;
474
475 -- last_update_date
476 IF p_offers_rec.last_update_date = FND_API.g_miss_date THEN
477 x_complete_rec.last_update_date := l_offers_rec.last_update_date;
478 END IF;
479
480 -- last_updated_by
481 IF p_offers_rec.last_updated_by = FND_API.g_miss_num THEN
482 x_complete_rec.last_updated_by := l_offers_rec.last_updated_by;
483 END IF;
484
485 -- creation_date
486 IF p_offers_rec.creation_date = FND_API.g_miss_date THEN
487 x_complete_rec.creation_date := l_offers_rec.creation_date;
488 END IF;
489
490 -- created_by
491 IF p_offers_rec.created_by = FND_API.g_miss_num THEN
492 x_complete_rec.created_by := l_offers_rec.created_by;
493 END IF;
494
495 -- last_update_login
496 IF p_offers_rec.last_update_login = FND_API.g_miss_num THEN
497 x_complete_rec.last_update_login := l_offers_rec.last_update_login;
498 END IF;
499
500 -- object_version_number
501 IF p_offers_rec.object_version_number = FND_API.g_miss_num THEN
502 x_complete_rec.object_version_number := l_offers_rec.object_version_number;
503 END IF;
504
505
506 -- perf_date_from
507 IF p_offers_rec.perf_date_from = FND_API.g_miss_date THEN
508 x_complete_rec.perf_date_from := l_offers_rec.perf_date_from;
509 END IF;
510
511 -- perf_date_to
512 IF p_offers_rec.perf_date_to = FND_API.g_miss_date THEN
513 x_complete_rec.perf_date_to := l_offers_rec.perf_date_to;
514 END IF;
515
516 -- status_code
517 IF p_offers_rec.status_code = FND_API.g_miss_char THEN
518 x_complete_rec.status_code := l_offers_rec.status_code;
519 END IF;
520
521 -- status_date
522 IF p_offers_rec.status_date = FND_API.g_miss_date THEN
523 x_complete_rec.status_date := l_offers_rec.status_date;
524 END IF;
525
526 -- modifier_level_code
527 IF p_offers_rec.modifier_level_code = FND_API.g_miss_char THEN
528 x_complete_rec.modifier_level_code := l_offers_rec.modifier_level_code;
529 END IF;
530
531 -- order_value_discount_type
532 IF p_offers_rec.order_value_discount_type = FND_API.g_miss_char THEN
533 x_complete_rec.order_value_discount_type := l_offers_rec.order_value_discount_type;
534 END IF;
535
536 -- offer_amount
537 IF p_offers_rec.offer_amount = FND_API.g_miss_num THEN
538 x_complete_rec.offer_amount := l_offers_rec.offer_amount;
539 END IF;
540
541 -- lumpsum_amount
542 IF p_offers_rec.lumpsum_amount = FND_API.g_miss_num THEN
543 x_complete_rec.lumpsum_amount := l_offers_rec.lumpsum_amount;
544 END IF;
545
546 -- lumpsum_payment_type
547 IF p_offers_rec.lumpsum_payment_type = FND_API.g_miss_char THEN
548 x_complete_rec.lumpsum_payment_type := l_offers_rec.lumpsum_payment_type;
549 END IF;
550
551 -- custom_setup_id
552 IF p_offers_rec.custom_setup_id = FND_API.g_miss_num THEN
553 x_complete_rec.custom_setup_id := l_offers_rec.custom_setup_id;
554 END IF;
555
556 -- security_group_id
557 IF p_offers_rec.security_group_id = FND_API.g_miss_num THEN
558 x_complete_rec.security_group_id := l_offers_rec.security_group_id;
559 END IF;
560
561 -- budget_amount_tc
562 IF p_offers_rec.budget_amount_tc = FND_API.g_miss_num THEN
563 x_complete_rec.budget_amount_tc := l_offers_rec.budget_amount_tc;
564 END IF;
565
566 -- security_group_id
567 IF p_offers_rec.budget_amount_fc = FND_API.g_miss_num THEN
568 x_complete_rec.budget_amount_fc := l_offers_rec.budget_amount_fc;
569 END IF;
570
571 -- transaction_currency_code
572 IF p_offers_rec.transaction_currency_code = FND_API.g_miss_char THEN
573 x_complete_rec.transaction_currency_code := l_offers_rec.transaction_currency_code;
574 END IF;
575
576 -- functional_currency_Code
577 IF p_offers_rec.functional_currency_Code = FND_API.g_miss_char THEN
578 x_complete_rec.functional_currency_Code := l_offers_rec.functional_currency_Code;
579 END IF;
580
581 -- qualifier_type
582 IF p_offers_rec.qualifier_type = FND_API.g_miss_char THEN
583 x_complete_rec.qualifier_type := l_offers_rec.qualifier_type;
584 END IF;
585
586 -- qualifier_id
587 IF p_offers_rec.qualifier_id = FND_API.g_miss_num THEN
588 x_complete_rec.qualifier_id := l_offers_rec.qualifier_id;
589 END IF;
590
591 -- distribution_type
592 IF p_offers_rec.distribution_type = FND_API.g_miss_char THEN
593 x_complete_rec.distribution_type := l_offers_rec.distribution_type;
594 END IF;
595
596 -- account_closed_flag
597 IF p_offers_rec.account_closed_flag = FND_API.g_miss_char THEN
598 x_complete_rec.account_closed_flag := l_offers_rec.account_closed_flag;
599 END IF;
600
601 -- budget_offer_yn
602 IF p_offers_rec.budget_offer_yn = FND_API.g_miss_char THEN
603 x_complete_rec.budget_offer_yn := l_offers_rec.budget_offer_yn;
604 END IF;
605
606 -- break_type
607 IF p_offers_rec.break_type = FND_API.g_miss_char THEN
608 x_complete_rec.break_type := l_offers_rec.break_type;
609 END IF;
610
611 -- budget_source_type
612 IF p_offers_rec.budget_source_type = FND_API.g_miss_char THEN
613 x_complete_rec.budget_source_type := l_offers_rec.budget_source_type;
614 END IF;
615
616 -- budget_source_id
617 IF p_offers_rec.budget_source_id = FND_API.g_miss_num THEN
618 x_complete_rec.budget_source_id := l_offers_rec.budget_source_id;
619 END IF;
620
621 -- confidential_flag
622 IF p_offers_rec.confidential_flag = FND_API.g_miss_char THEN
623 x_complete_rec.confidential_flag := l_offers_rec.confidential_flag;
624 END IF;
625
626 IF p_offers_rec.source_from_parent = FND_API.g_miss_char THEN
627 x_complete_rec.source_from_parent := l_offers_rec.source_from_parent;
628 END IF;
629
630 IF p_offers_rec.buyer_name = FND_API.g_miss_char THEN
631 x_complete_rec.buyer_name := l_offers_rec.buyer_name;
632 END IF;
633
634 IF p_offers_rec.last_recal_date = FND_API.g_miss_date THEN
635 x_complete_rec.last_recal_date := l_offers_rec.last_recal_date;
636 END IF;
637
638 -- autopay_flag
639 IF p_offers_rec.autopay_flag = FND_API.g_miss_char THEN
640 x_complete_rec.autopay_flag := l_offers_rec.autopay_flag;
641 END IF;
642
643 -- autopay_days
644 IF p_offers_rec.autopay_days = FND_API.g_miss_num THEN
645 x_complete_rec.autopay_days := l_offers_rec.autopay_days;
646 END IF;
647
648 -- autopay_method
649 IF p_offers_rec.autopay_method = FND_API.g_miss_char THEN
650 x_complete_rec.autopay_method := l_offers_rec.autopay_method;
651 END IF;
652
653 -- autopay_party_attr
654 IF p_offers_rec.autopay_party_attr = FND_API.g_miss_char THEN
655 x_complete_rec.autopay_party_attr := l_offers_rec.autopay_party_attr;
656 END IF;
657
658 -- autopay_party_id
659 IF p_offers_rec.autopay_party_id = FND_API.g_miss_num THEN
660 x_complete_rec.autopay_party_id := l_offers_rec.autopay_party_id;
661 END IF;
662
663 IF p_offers_rec.tier_level = FND_API.g_miss_char THEN
664 -- x_complete_rec.tier_level := l_offers_rec.tier_level;
665 x_complete_rec.tier_level := l_offers_rec.tier_level;
666 END IF;
667
668
669 IF p_offers_rec.na_rule_header_id = FND_API.g_miss_num THEN
670 x_complete_rec.na_rule_header_id := l_offers_rec.na_rule_header_id;
671 END IF;
672
673 IF p_offers_rec.beneficiary_account_id = FND_API.g_miss_num THEN
674 x_complete_rec.beneficiary_account_id := l_offers_rec.beneficiary_account_id;
675 END IF;
676
677 IF p_offers_rec.sales_method_flag = FND_API.G_MISS_CHAR THEN
678 x_complete_rec.sales_method_flag := l_offers_rec.sales_method_flag;
679 END IF;
680
681 IF p_offers_rec.org_id = FND_API.g_miss_num THEN
682 x_complete_rec.org_id := l_offers_rec.org_id;
683 END IF;
684
685 /*ER 11806714 added percent_discount*/
686 IF p_offers_rec.percent_discount = FND_API.g_miss_num THEN
687 x_complete_rec.percent_discount := l_offers_rec.percent_discount;
688 END IF;
689
690 -- Note: Developers need to modify the procedure
691
692 -- to handle any business specific requirements.
693 END Complete_offers_Rec;
694
695 PROCEDURE Update_Offers(
696 p_api_version_number IN NUMBER,
697 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
698 p_commit IN VARCHAR2 := FND_API.G_FALSE,
699 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
700
701 x_return_status OUT NOCOPY VARCHAR2,
702 x_msg_count OUT NOCOPY NUMBER,
703 x_msg_data OUT NOCOPY VARCHAR2,
704
705 p_offers_rec IN offers_rec_type,
706 x_object_version_number OUT NOCOPY NUMBER
707 )
708
709 IS
710
711 CURSOR c_get_offers(p_offer_id NUMBER) IS
712 SELECT object_version_number
713 FROM OZF_OFFERS
714 WHERE qp_list_header_id = p_offer_id;
715 -- Hint: Developer need to provide Where clause
716
717 CURSOR c_get_old_owner(l_list_header_id NUMBER) IS
718 SELECT owner_id
719 FROM ozf_offers
720 WHERE qp_list_header_id = l_list_header_id;
721
722 CURSOR c_get_start_date IS
723 SELECT q.start_date_active, o.start_date
724 FROM qp_list_headers_b q, ozf_offers o
725 WHERE o.qp_list_header_id = q.list_header_id
726 AND q.list_header_id = p_offers_rec.qp_list_header_id;
727
728 l_api_name CONSTANT VARCHAR2(30) := 'Update_Offers';
729 l_api_version_number CONSTANT NUMBER := 1.0;
730 -- Local Variables
731 l_object_version_number NUMBER;
732 l_OFFER_ID NUMBER;
733
734 l_tar_offers_rec OZF_Promotional_Offers_PVT.offers_rec_type := P_offers_rec;
735 l_rowid ROWID;
736 l_is_owner VARCHAR2(1);
737 l_is_admin BOOLEAN;
738 l_old_owner_id NUMBER;
739 l_offers_rec OZF_Promotional_Offers_PVT.offers_rec_type;
740 l_last_recal_date DATE;
741 l_start_date_o DATE;
742 l_start_date DATE;
743
744 BEGIN
745 -- Standard Start of API savepoint
746 SAVEPOINT UPDATE_Offers_PVT;
747
748 -- Standard call to check for call compatibility.
749 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
750 p_api_version_number,
751 l_api_name,
752 G_PKG_NAME)
753 THEN
754 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
755 END IF;
756
757 -- Initialize message list if p_init_msg_list is set to TRUE.
758 IF FND_API.to_Boolean( p_init_msg_list )
759 THEN
760 FND_MSG_PUB.initialize;
761 END IF;
762
763 -- Initialize API return status to SUCCESS
764 x_return_status := FND_API.G_RET_STS_SUCCESS;
765
766 OPEN c_get_Offers( l_tar_offers_rec.qp_list_header_id);
767 FETCH c_get_Offers INTO l_object_version_number;
768
769 If ( c_get_Offers%NOTFOUND) THEN
770 OZF_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
771 p_token_name => 'INFO',
772 p_token_value => 'Offers') ;
773 RAISE FND_API.G_EXC_ERROR;
774 END IF;
775 CLOSE c_get_Offers;
776
777 -- Check Whether record has been changed by someone else
778 If l_tar_offers_rec.object_version_number IS NOT NULL
779 AND l_tar_offers_rec.object_version_number <> FND_API.G_MISS_NUM
780 AND l_tar_offers_rec.object_version_number <> l_object_version_number Then
781 OZF_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED',
782 p_token_name => 'INFO',
783 p_token_value => 'Offers') ;
784 raise FND_API.G_EXC_ERROR;
785 End if;
786 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
787 THEN
788 -- Invoke validation procedures
789 Validate_offers(
790 p_api_version_number => 1.0,
791 p_init_msg_list => FND_API.G_FALSE,
792 p_validation_level => p_validation_level,
793 p_offers_rec => p_offers_rec,
794 x_return_status => x_return_status,
795 x_msg_count => x_msg_count,
796 x_msg_data => x_msg_data);
797 END IF;
798
799 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
800 RAISE FND_API.G_EXC_ERROR;
801 END IF;
802
803 -- added by julou 07/29/2002 check if the owner is changed. if so, update the owner inof in ams_act_access
804 -- only the owner and super user can change the owner
805 -- check if login user is the owner.
806 OPEN c_get_old_owner(p_offers_rec.qp_list_header_id);
807 FETCH c_get_old_owner INTO l_old_owner_id;
808 CLOSE c_get_old_owner;
809
810 Complete_offers_Rec(
811 p_offers_rec => p_offers_rec,
812 x_complete_rec => l_offers_rec
813 );
814
815 IF l_offers_rec.owner_id <> l_old_owner_id THEN
816 l_is_owner := AMS_access_PVT.check_owner(p_object_id => l_offers_rec.qp_list_header_id
817 ,p_object_type => 'OFFR'
818 ,p_user_or_role_id => ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id)
819 ,p_user_or_role_type => 'USER');
820 -- check if login user is super user
821 l_is_admin := AMS_Access_PVT.Check_Admin_Access(ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id));
822
823 IF l_is_owner = 'Y' OR l_is_admin THEN -- curent user is owner/admin, changing owner is allowed
824 AMS_access_PVT.update_object_owner(
825 p_api_version => l_api_version_number,
826 p_init_msg_list => FND_API.g_false,
827 p_commit => FND_API.g_false,
828 p_validation_level => FND_API.g_valid_level_full,
829 x_return_status => x_return_status,
830 x_msg_count => x_msg_count,
831 x_msg_data => x_msg_data,
832 p_object_type => 'OFFR',
833 p_object_id => l_offers_rec.qp_list_header_id,
834 p_resource_id => l_offers_rec.owner_id,
835 p_old_resource_id => l_old_owner_id);
836
837 IF x_return_status = fnd_api.g_ret_sts_error THEN
838 RAISE FND_API.g_exc_error;
839 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
840 RAISE FND_API.g_exc_unexpected_error;
841 END IF;
842 ELSE -- not owner nor super user
843 OZF_Utility_PVT.error_message('OZF_OFFR_UPDT_OWNER_PERM');
844 RAISE FND_API.g_exc_error;
845 END IF;
846 END IF;
847 -- end of offer security change
848 -- julou defaulting last_recal_date to offer start date
849 OPEN c_get_start_date;
850 FETCH c_get_start_date INTO l_last_recal_date, l_start_date_o;
851 CLOSE c_get_start_date;
852
853 IF l_offers_rec.status_code = 'ACTIVE' THEN
854 IF l_start_date_o IS NULL THEN
855 l_start_date := GREATEST(NVL(l_last_recal_date, SYSDATE), SYSDATE);
856 ELSE
857 l_start_date := l_start_date_o;
858 END IF;
859 ELSE
860 l_start_date := l_start_date_o;
861 END IF;
862 -- end julou
863
864 -- Invoke table handler(OZF_Promotional_Offers_PKG.Update_Row)
865 OZF_Promotional_Offers_PKG.Update_Row(
866 p_offer_id => l_offers_rec.offer_id,
867 p_qp_list_header_id => l_offers_rec.qp_list_header_id,
868 p_offer_type => l_offers_rec.offer_type,
869 p_offer_code => l_offers_rec.offer_code,
870 p_activity_media_id => l_offers_rec.activity_media_id,
871 p_reusable => l_offers_rec.reusable,
872 p_user_status_id => l_offers_rec.user_status_id,
873 p_owner_id => l_offers_rec.owner_id,
874 p_wf_item_key => l_offers_rec.wf_item_key,
875 p_customer_reference => l_offers_rec.customer_reference,
876 p_buying_group_contact_id => l_offers_rec.buying_group_contact_id,
877 p_last_update_date => SYSDATE,
878 p_last_updated_by => FND_GLOBAL.user_id,
879 p_last_update_login => FND_GLOBAL.conc_login_id,
880 p_object_version_number => l_offers_rec.object_version_number,
881 p_perf_date_from => l_offers_rec.perf_date_from,
882 p_perf_date_to => l_offers_rec.perf_date_to,
883 p_status_code => l_offers_rec.status_code,
884 p_status_date => l_offers_rec.status_date,
885 p_modifier_level_code => l_offers_rec.modifier_level_code,
886 p_order_value_discount_type => l_offers_rec.order_value_discount_type,
887 p_offer_amount => l_offers_rec.offer_amount,
888 p_lumpsum_amount => l_offers_rec.lumpsum_amount,
889 p_lumpsum_payment_type => l_offers_rec.lumpsum_payment_type,
890 p_custom_setup_id => l_offers_rec.custom_setup_id,
891 p_security_group_id => l_offers_rec.security_group_id,
892 p_budget_amount_tc => l_offers_rec.budget_amount_tc,
893 p_budget_amount_fc => l_offers_rec.budget_amount_fc,
894 p_transaction_currency_Code => l_offers_rec.transaction_currency_Code ,
895 p_functional_currency_code => l_offers_rec.functional_currency_code,
896 p_distribution_type => l_offers_rec.distribution_type,
897 p_qualifier_id => l_offers_rec.qualifier_id,
898 p_qualifier_type => l_offers_rec.qualifier_type,
899 p_account_closed_flag => l_offers_rec.account_closed_flag,
900 p_budget_offer_yn => l_offers_rec.budget_offer_yn,
901 p_break_type => l_offers_rec.break_type,
902 p_retroactive => l_offers_rec.retroactive,
903 p_volume_offer_type => l_offers_rec.volume_offer_type,
904 p_confidential_flag => l_offers_rec.confidential_flag,
905 p_budget_source_type => l_offers_rec.budget_source_type,
906 p_budget_source_id => l_offers_rec.budget_source_id,
907 p_source_from_parent => l_offers_rec.source_from_parent,
908 p_buyer_name => l_offers_rec.buyer_name,
909 p_last_recal_date => l_last_recal_date,
910 p_date_qualifier => FND_API.G_MISS_CHAR,
911 p_autopay_flag => l_offers_rec.autopay_flag,
912 p_autopay_days => l_offers_rec.autopay_days,
913 p_autopay_method => l_offers_rec.autopay_method,
914 p_autopay_party_attr => l_offers_rec.autopay_party_attr,
915 p_autopay_party_id => l_offers_rec.autopay_party_id,
916 p_tier_level => l_offers_rec.tier_level,
917 p_na_rule_header_id => l_offers_rec.na_rule_header_id,
918 p_beneficiary_account_id => l_offers_rec.beneficiary_account_id,
919 p_sales_method_flag => l_offers_rec.sales_method_flag,
920 p_org_id => l_offers_rec.org_id,
921 p_start_date => l_start_date,
922 /*ER11806714 added percent_discount column */
923 p_percent_discount => l_offers_rec.percent_discount,
924 p_fund_request_curr_code => nvl(l_offers_rec.transaction_currency_Code,FND_PROFILE.VALUE('JTF_PROFILE_DEFAULT_CURRENCY')) -- Bug 14138664
925 );
926 --
927 -- End of API body.
928 --
929
930 -- Standard check for p_commit
931 IF FND_API.to_Boolean( p_commit )
932 THEN
933 COMMIT WORK;
934 END IF;
935
936 -- Standard call to get message count and if count is 1, get message info.
937 FND_MSG_PUB.Count_And_Get
938 (p_count => x_msg_count,
939 p_data => x_msg_data,
940 p_encoded => FND_API.G_FALSE
941 );
942 EXCEPTION
943
944 WHEN OZF_Utility_PVT.resource_locked THEN
945 x_return_status := FND_API.g_ret_sts_error;
946 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
947
948 WHEN FND_API.G_EXC_ERROR THEN
949 ROLLBACK TO UPDATE_Offers_PVT;
950 x_return_status := FND_API.G_RET_STS_ERROR;
951 -- Standard call to get message count and if count=1, get the message
952 FND_MSG_PUB.Count_And_Get (
953 p_encoded => FND_API.G_FALSE,
954 p_count => x_msg_count,
955 p_data => x_msg_data
956 );
957
958 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
959 ROLLBACK TO UPDATE_Offers_PVT;
960 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
961 -- Standard call to get message count and if count=1, get the message
962 FND_MSG_PUB.Count_And_Get (
963 p_encoded => FND_API.G_FALSE,
964 p_count => x_msg_count,
965 p_data => x_msg_data
966 );
967 /*
968 WHEN OTHERS THEN
969 ROLLBACK TO UPDATE_Offers_PVT;
970 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
971 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
972 THEN
973 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
974 END IF;
975 -- Standard call to get message count and if count=1, get the message
976 FND_MSG_PUB.Count_And_Get (
977 p_encoded => FND_API.G_FALSE,
978 p_count => x_msg_count,
979 p_data => x_msg_data
980 );
981 */
982 End Update_Offers;
983
984
985 PROCEDURE Delete_Offers(
986 p_api_version_number IN NUMBER,
987 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
988 p_commit IN VARCHAR2 := FND_API.G_FALSE,
989 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
990 x_return_status OUT NOCOPY VARCHAR2,
991 x_msg_count OUT NOCOPY NUMBER,
992 x_msg_data OUT NOCOPY VARCHAR2,
993 p_offer_id IN NUMBER,
994 p_object_version_number IN NUMBER
995 )
996
997 IS
998 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Offers';
999 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1000 l_object_version_number NUMBER;
1001
1002 BEGIN
1003 -- Standard Start of API savepoint
1004 SAVEPOINT DELETE_Offers_PVT;
1005
1006 -- Standard call to check for call compatibility.
1007 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1008 p_api_version_number,
1009 l_api_name,
1010 G_PKG_NAME)
1011 THEN
1012 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1013 END IF;
1014
1015 -- Initialize message list if p_init_msg_list is set to TRUE.
1016 IF FND_API.to_Boolean( p_init_msg_list )
1017 THEN
1018 FND_MSG_PUB.initialize;
1019 END IF;
1020
1021 -- Initialize API return status to SUCCESS
1022 x_return_status := FND_API.G_RET_STS_SUCCESS;
1023
1024 --
1025 -- Api body
1026 --
1027
1028 -- Invoke table handler(OZF_Promotional_Offers_PKG.Delete_Row)
1029 OZF_Promotional_Offers_PKG.Delete_Row(
1030 p_OFFER_ID => p_OFFER_ID);
1031 --
1032 -- End of API body
1033 --
1034
1035 -- Standard check for p_commit
1036 IF FND_API.to_Boolean( p_commit )
1037 THEN
1038 COMMIT WORK;
1039 END IF;
1040
1041 -- Standard call to get message count and if count is 1, get message info.
1042 FND_MSG_PUB.Count_And_Get
1043 (p_count => x_msg_count,
1044 p_data => x_msg_data
1045 );
1046 EXCEPTION
1047
1048 WHEN OZF_Utility_PVT.resource_locked THEN
1049 x_return_status := FND_API.g_ret_sts_error;
1050 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
1051
1052 WHEN FND_API.G_EXC_ERROR THEN
1053 ROLLBACK TO DELETE_Offers_PVT;
1054 x_return_status := FND_API.G_RET_STS_ERROR;
1055 -- Standard call to get message count and if count=1, get the message
1056 FND_MSG_PUB.Count_And_Get (
1057 p_encoded => FND_API.G_FALSE,
1058 p_count => x_msg_count,
1059 p_data => x_msg_data
1060 );
1061
1062 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1063 ROLLBACK TO DELETE_Offers_PVT;
1064 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1065 -- Standard call to get message count and if count=1, get the message
1066 FND_MSG_PUB.Count_And_Get (
1067 p_encoded => FND_API.G_FALSE,
1068 p_count => x_msg_count,
1069 p_data => x_msg_data
1070 );
1071
1072 WHEN OTHERS THEN
1073 ROLLBACK TO DELETE_Offers_PVT;
1074 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1075 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1076 THEN
1077 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1078 END IF;
1079 -- Standard call to get message count and if count=1, get the message
1080 FND_MSG_PUB.Count_And_Get (
1081 p_encoded => FND_API.G_FALSE,
1082 p_count => x_msg_count,
1083 p_data => x_msg_data
1084 );
1085 End Delete_Offers;
1086
1087
1088
1089 -- Hint: Primary key needs to be returned.
1090 PROCEDURE Lock_Offers(
1091 p_api_version_number IN NUMBER,
1092 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1093
1094 x_return_status OUT NOCOPY VARCHAR2,
1095 x_msg_count OUT NOCOPY NUMBER,
1096 x_msg_data OUT NOCOPY VARCHAR2,
1097
1098 p_offer_id IN NUMBER,
1099 p_object_version IN NUMBER
1100 )
1101
1102 IS
1103 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Offers';
1104 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1105 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1106 l_OFFER_ID NUMBER;
1107
1108 CURSOR c_Offers IS
1109 SELECT OFFER_ID
1110 FROM OZF_OFFERS
1111 WHERE OFFER_ID = p_OFFER_ID
1112 AND object_version_number = p_object_version
1113 FOR UPDATE NOWAIT;
1114
1115 BEGIN
1116
1117 -- Initialize message list if p_init_msg_list is set to TRUE.
1118 IF FND_API.to_Boolean( p_init_msg_list )
1119 THEN
1120 FND_MSG_PUB.initialize;
1121 END IF;
1122
1123 -- Standard call to check for call compatibility.
1124 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1125 p_api_version_number,
1126 l_api_name,
1127 G_PKG_NAME)
1128 THEN
1129 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1130 END IF;
1131
1132
1133 -- Initialize API return status to SUCCESS
1134 x_return_status := FND_API.G_RET_STS_SUCCESS;
1135
1136
1137 ------------------------ lock -------------------------
1138
1139 OPEN c_Offers;
1140
1141 FETCH c_Offers INTO l_OFFER_ID;
1142
1143 IF (c_Offers%NOTFOUND) THEN
1144 CLOSE c_Offers;
1145 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1146 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
1147 FND_MSG_PUB.add;
1148 END IF;
1149 RAISE FND_API.g_exc_error;
1150 END IF;
1151
1152 CLOSE c_Offers;
1153
1154 -------------------- finish --------------------------
1155 FND_MSG_PUB.count_and_get(
1156 p_encoded => FND_API.g_false,
1157 p_count => x_msg_count,
1158 p_data => x_msg_data);
1159
1160 EXCEPTION
1161
1162 WHEN OZF_Utility_PVT.resource_locked THEN
1163 x_return_status := FND_API.g_ret_sts_error;
1164 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
1165
1166 WHEN FND_API.G_EXC_ERROR THEN
1167 ROLLBACK TO LOCK_Offers_PVT;
1168 x_return_status := FND_API.G_RET_STS_ERROR;
1169 -- Standard call to get message count and if count=1, get the message
1170 FND_MSG_PUB.Count_And_Get (
1171 p_encoded => FND_API.G_FALSE,
1172 p_count => x_msg_count,
1173 p_data => x_msg_data
1174 );
1175
1176 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1177 ROLLBACK TO LOCK_Offers_PVT;
1178 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1179 -- Standard call to get message count and if count=1, get the message
1180 FND_MSG_PUB.Count_And_Get (
1181 p_encoded => FND_API.G_FALSE,
1182 p_count => x_msg_count,
1183 p_data => x_msg_data
1184 );
1185
1186 WHEN OTHERS THEN
1187 ROLLBACK TO LOCK_Offers_PVT;
1188 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1189 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1190 THEN
1191 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1192 END IF;
1193 -- Standard call to get message count and if count=1, get the message
1194 FND_MSG_PUB.Count_And_Get (
1195 p_encoded => FND_API.G_FALSE,
1196 p_count => x_msg_count,
1197 p_data => x_msg_data
1198 );
1199 End Lock_Offers;
1200
1201
1202 PROCEDURE check_offers_uk_items(
1203 p_offers_rec IN offers_rec_type,
1204 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1205 x_return_status OUT NOCOPY VARCHAR2)
1206 IS
1207 l_valid_flag VARCHAR2(1);
1208
1209 BEGIN
1210 x_return_status := FND_API.g_ret_sts_success;
1211 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1212
1213 l_valid_flag := OZF_Utility_PVT.check_uniqueness(
1214 'OZF_OFFERS',
1215 'OFFER_ID = '|| p_offers_rec.OFFER_ID
1216 );
1217
1218 END IF;
1219
1220 IF l_valid_flag = FND_API.g_false THEN
1221 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFER_ID_DUPLICATE');
1222 x_return_status := FND_API.g_ret_sts_error;
1223 RETURN;
1224 END IF;
1225
1226 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1227
1228 l_valid_flag := OZF_Utility_PVT.check_uniqueness(
1229 'OZF_OFFERS',
1230 'qp_list_header_id = '|| p_offers_rec.qp_list_header_id
1231 );
1232
1233 END IF;
1234
1235 IF l_valid_flag = FND_API.g_false THEN
1236 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFER_QP_ID_DUPLICATE');
1237 x_return_status := FND_API.g_ret_sts_error;
1238 RETURN;
1239 END IF;
1240
1241
1242 END check_offers_uk_items;
1243
1244 PROCEDURE check_offers_req_items(
1245 p_offers_rec IN offers_rec_type,
1246 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1247 x_return_status OUT NOCOPY VARCHAR2
1248 )
1249 IS
1250
1251 CURSOR C_OFFER_END_DATE IS
1252 SELECT end_date_active
1253 FROM qp_list_headers_b
1254 WHERE list_header_id = p_offers_rec.qp_list_header_id;
1255
1256 l_offer_end_date DATE;
1257
1258 BEGIN
1259 x_return_status := FND_API.g_ret_sts_success;
1260
1261 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1262
1263
1264 IF p_offers_rec.offer_id = FND_API.g_miss_num OR p_offers_rec.offer_id IS NULL THEN
1265 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFERS_NO_OFFER_ID');
1266 x_return_status := FND_API.g_ret_sts_error;
1267 RETURN;
1268 END IF;
1269
1270
1271 IF p_offers_rec.qp_list_header_id = FND_API.g_miss_num OR p_offers_rec.qp_list_header_id IS NULL THEN
1272 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFERS_NO_LIST_HEADER_ID');
1273 x_return_status := FND_API.g_ret_sts_error;
1274 RETURN;
1275 END IF;
1276
1277
1278 IF p_offers_rec.offer_type = FND_API.g_miss_char OR p_offers_rec.offer_type IS NULL THEN
1279 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFERS_NO_OFFER_TYPE');
1280 x_return_status := FND_API.g_ret_sts_error;
1281 RETURN;
1282 END IF;
1283
1284
1285 IF p_offers_rec.offer_code = FND_API.g_miss_char OR p_offers_rec.offer_code IS NULL THEN
1286 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFERS_NO_OFFER_CODE');
1287 x_return_status := FND_API.g_ret_sts_error;
1288 RETURN;
1289 END IF;
1290
1291 IF p_offers_rec.user_status_id = FND_API.g_miss_num OR p_offers_rec.user_status_id IS NULL THEN
1292 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFERS_NO_USER_STATUS_ID');
1293 x_return_status := FND_API.g_ret_sts_error;
1294 RETURN;
1295 END IF;
1296
1297 IF p_offers_rec.offer_type = 'SCAN_DATA' THEN
1298 IF p_offers_rec.activity_media_id = FND_API.g_miss_num OR p_offers_rec.activity_media_id IS NULL THEN
1299 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFERS_NO_ACTIVITY');
1300 x_return_status := FND_API.g_ret_sts_error;
1301 RETURN;
1302 END IF;
1303 END IF;
1304
1305 IF p_offers_rec.autopay_flag = 'Y' THEN
1306 IF p_offers_rec.autopay_days IS NULL THEN
1307 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFR_NO_AUTOPAY_DAYS');
1308 x_return_status := FND_API.g_ret_sts_error;
1309 RETURN;
1310 END IF;
1311
1312 IF p_offers_rec.autopay_method IS NULL THEN
1313 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFR_NO_AUTOPAY_METHOD');
1314 x_return_status := FND_API.g_ret_sts_error;
1315 RETURN;
1316 END IF;
1317
1318 IF p_offers_rec.autopay_party_id IS NULL THEN
1319 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFR_NO_AUTOPAY_PARTY_ID');
1320 x_return_status := FND_API.g_ret_sts_error;
1321 RETURN;
1322 END IF;
1323
1324 OPEN c_offer_end_date;
1325 FETCH c_offer_end_date INTO l_offer_end_date;
1326 CLOSE c_offer_end_date;
1327 IF l_offer_end_date IS NULL THEN
1328 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFR_NO_END_DATE');
1329 x_return_status := FND_API.g_ret_sts_error;
1330 RETURN;
1331 END IF;
1332 END IF;
1333
1334 IF p_offers_rec.offer_type = 'NET_ACCRUAL' THEN
1335 IF p_offers_rec.tier_level = FND_API.g_miss_char OR p_offers_rec.tier_level IS NULL THEN
1336 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFR_INVALID_TIER_LVL');
1337 x_return_status := FND_API.g_ret_sts_error;
1338 RETURN;
1339 END IF;
1340
1341 IF p_offers_rec.custom_setup_id <> 105 THEN -- customer not required for PV offer
1342 IF p_offers_rec.qualifier_id IS NULL OR p_offers_rec.qualifier_id = FND_API.g_miss_num THEN
1343 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFR_NO_CUSTOMER');
1344 x_return_status := FND_API.g_ret_sts_error;
1345 RETURN;
1346 END IF;
1347 END IF;
1348 END IF;
1349 ELSE
1350
1351
1352 IF p_offers_rec.offer_id IS NULL THEN
1353 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFERS_NO_OFFER_ID');
1354 x_return_status := FND_API.g_ret_sts_error;
1355 RETURN;
1356 END IF;
1357
1358
1359 IF p_offers_rec.qp_list_header_id IS NULL THEN
1360 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFERS_NO_LIST_HEADER_ID');
1361 x_return_status := FND_API.g_ret_sts_error;
1362 RETURN;
1363 END IF;
1364
1365
1366 IF p_offers_rec.offer_type IS NULL THEN
1367 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFERS_NO_OFFER_TYPE');
1368 x_return_status := FND_API.g_ret_sts_error;
1369 RETURN;
1370 END IF;
1371
1372
1373 IF p_offers_rec.offer_code IS NULL THEN
1374 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFERS_NO_OFFER_CODE');
1375 x_return_status := FND_API.g_ret_sts_error;
1376 RETURN;
1377 END IF;
1378
1379 IF p_offers_rec.user_status_id IS NULL THEN
1380 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFERS_NO_USER_STATUS_ID');
1381 x_return_status := FND_API.g_ret_sts_error;
1382 RETURN;
1383 END IF;
1384
1385 IF p_offers_rec.offer_type = 'SCAN_DATA' THEN
1386 IF p_offers_rec.activity_media_id IS NULL OR p_offers_rec.activity_media_id = FND_API.g_miss_num THEN
1387 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFERS_NO_ACTIVITY');
1388 x_return_status := FND_API.g_ret_sts_error;
1389 RETURN;
1390 END IF;
1391 END IF;
1392
1393 IF p_offers_rec.offer_type = 'NET_ACCRUAL' THEN
1394 IF p_offers_rec.tier_level = FND_API.g_miss_char OR p_offers_rec.tier_level IS NULL THEN
1395 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFR_INVALID_TIER_LVL');
1396 x_return_status := FND_API.g_ret_sts_error;
1397 RETURN;
1398 END IF;
1399
1400 IF p_offers_rec.custom_setup_id <> 105 THEN -- customer not required for PV offer
1401 IF p_offers_rec.qualifier_id IS NULL OR p_offers_rec.qualifier_id = FND_API.g_miss_num THEN
1402 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFR_NO_CUSTOMER');
1403 x_return_status := FND_API.g_ret_sts_error;
1404 RETURN;
1405 END IF;
1406 END IF;
1407 END IF;
1408 END IF;
1409
1410
1411 END check_offers_req_items;
1412
1413 PROCEDURE check_offers_FK_items(
1414 p_offers_rec IN offers_rec_type,
1415 x_return_status OUT NOCOPY VARCHAR2
1416 )
1417 IS
1418
1419 CURSOR c_media_id(l_id NUMBER) IS
1420 SELECT 1
1421 FROM ams_media_vl
1422 WHERE media_type_code = 'DEAL'
1423 AND media_id = l_id;
1424
1425 l_dummy NUMBER;
1426
1427 BEGIN
1428 x_return_status := FND_API.g_ret_sts_success;
1429
1430 --- checking the owner_id
1431 IF p_offers_rec.owner_id <> FND_API.g_miss_num THEN
1432 IF OZF_Utility_PVT.check_fk_exists(
1433 'jtf_rs_resource_extns',
1434 'resource_id',
1435 p_offers_rec.owner_id ) = FND_API.g_false
1436 THEN
1437 OZF_Utility_PVT.Error_Message('OZF_OFR_BAD_USER_ID');
1438 x_return_status := FND_API.g_ret_sts_error;
1439 RETURN;
1440 END IF;
1441 END IF;
1442
1443 --- checking the qp_list_header_id
1444 IF p_offers_rec.qp_list_header_id <> FND_API.G_MISS_NUM THEN
1445 IF OZF_Utility_PVT.check_fk_exists(
1446 'qp_list_headers_b'
1447 ,'list_header_id '
1448 ,p_offers_rec.qp_list_header_id) = FND_API.g_false
1449 THEN
1450 OZF_Utility_PVT.Error_Message('OZF_OFFR_BAD_QP_LIST_HEADER_ID');
1451 x_return_status := FND_API.g_ret_sts_error;
1452 RETURN;
1453 END IF;
1454 END IF;
1455
1456 -- checking the custom_setup_id
1457 IF p_offers_rec.custom_setup_id <> FND_API.g_miss_num
1458 AND p_offers_rec.custom_setup_id IS NOT NULL
1459 THEN
1460 IF OZF_Utility_PVT.check_fk_exists(
1461 'ams_custom_setups_vl'
1462 ,'custom_setup_id '
1463 ,p_offers_rec.custom_setup_id) = FND_API.g_false
1464 THEN
1465 OZF_Utility_PVT.Error_Message('OZF_OFFR_BAD_CUSTOM_SETUP_ID');
1466 x_return_status := FND_API.g_ret_sts_error;
1467 RETURN;
1468 END IF;
1469 END IF;
1470
1471 -- checking the user_status_id
1472 IF p_offers_rec.user_status_id <> FND_API.G_MISS_NUM
1473 AND p_offers_rec.user_status_id IS NOT NULL
1474 THEN
1475 IF OZF_Utility_PVT.check_fk_exists(
1476 'ams_user_statuses_vl'
1477 ,'user_status_id '
1478 ,p_offers_rec.user_status_id) = FND_API.g_false
1479 THEN
1480 OZF_Utility_PVT.Error_Message('OZF_OFFR_BAD_USER_STATUS_ID');
1481 x_return_status := FND_API.g_ret_sts_error;
1482 RETURN;
1483 END IF;
1484 END IF;
1485
1486
1487 -- checking the activity_media_id
1488 IF p_offers_rec.activity_media_id <> FND_API.G_MISS_NUM
1489 AND p_offers_rec.activity_media_id IS NOT NULL
1490 THEN
1491 OPEN c_media_id(p_offers_rec.activity_media_id);
1492 FETCH c_media_id INTO l_dummy;
1493 CLOSE c_media_id;
1494
1495 IF l_dummy IS NULL
1496 THEN
1497 OZF_Utility_PVT.Error_Message('OZF_OFFR_BAD_MEDIA_ID');
1498 x_return_status := FND_API.g_ret_sts_error;
1499 RETURN;
1500 END IF;
1501 END IF;
1502
1503 IF p_offers_rec.offer_type = 'NET_ACCRUAL' THEN
1504 IF p_offers_rec.na_rule_header_id IS NOT NULL AND p_offers_rec.na_rule_header_id <> FND_API.G_MISS_NUM THEN
1505 IF ozf_utility_pvt.check_fk_exists('ozf_na_rule_headers_b'
1506 ,'NA_RULE_HEADER_ID'
1507 ,p_offers_rec.na_rule_header_id) = FND_API.G_FALSE
1508 THEN
1509 OZF_Utility_PVT.Error_Message('OZF_OFFR_BAD_NA_RULE_HEADER_ID');
1510 x_return_status := FND_API.g_ret_sts_error;
1511 RETURN;
1512 END IF;
1513 END IF;
1514 END IF;
1515 END check_offers_FK_items;
1516
1517 PROCEDURE check_offers_Lookup_items(
1518 p_offers_rec IN offers_rec_type,
1519 x_return_status OUT NOCOPY VARCHAR2
1520 )
1521 IS
1522 BEGIN
1523 x_return_status := FND_API.g_ret_sts_success;
1524
1525 -- modifier_level_code
1526 IF p_offers_rec.modifier_level_code <> FND_API.g_miss_char
1527 AND p_offers_rec.modifier_level_code IS NOT NULL
1528 THEN
1529 IF OZF_Utility_PVT.check_lookup_exists(
1530 p_lookup_table_name => 'qp_lookups'
1531 ,p_lookup_type => 'MODIFIER_LEVEL_CODE'
1532 ,p_lookup_code => p_offers_rec.modifier_level_code
1533 ) = FND_API.g_false
1534 THEN
1535 --OZF_Utility_PVT.Error_Message('OZF_OFR_BAD_MODIFIER_LEVEL_CODE') ;
1536 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1537 THEN
1538 FND_MESSAGE.set_name('OZF', 'OZF_OFR_BAD_MODIFIER_LEVEL_COD');
1539 FND_MSG_PUB.add;
1540 END IF;
1541 x_return_status := FND_API.g_ret_sts_error;
1542 RETURN;
1543 END IF;
1544 END IF;
1545
1546 --- order_value_discount_type
1547 --- reminder : This lookup has to be created . -musman 04/20
1548 IF p_offers_rec.order_value_discount_type <> FND_API.g_miss_char
1549 AND p_offers_rec.order_value_discount_type IS NOT NULL
1550 THEN
1551 IF OZF_Utility_PVT.check_lookup_exists(
1552 p_lookup_type => 'OZF_OFFER_OV_DISCOUNT_TYPE',
1553 p_lookup_code => p_offers_rec.order_value_discount_type
1554 ) = FND_API.g_false
1555 THEN
1556 OZF_Utility_PVT.Error_Message('OZF_OFR_BAD_DISCOUNT_TYPE') ;
1557 x_return_status := FND_API.g_ret_sts_error;
1558 RETURN;
1559 END IF;
1560 END IF;
1561
1562 IF p_offers_rec.lumpsum_payment_type <> FND_API.g_miss_char
1563 AND p_offers_rec.lumpsum_payment_type IS NOT NULL
1564 THEN
1565 IF OZF_Utility_PVT.check_lookup_exists(
1566 p_lookup_type => 'OZF_OFFER_LUMPSUM_PAYMENT',
1567 p_lookup_code => p_offers_rec.lumpsum_payment_type
1568 ) = FND_API.g_false
1569 THEN
1570 OZF_Utility_PVT.Error_Message('OZF_OFR_BAD_DISCOUNT_TYPE') ;
1571 x_return_status := FND_API.g_ret_sts_error;
1572 RETURN;
1573 END IF;
1574 END IF;
1575
1576 -- status code
1577 IF p_offers_rec.status_code <> FND_API.g_miss_char
1578 AND p_offers_rec.status_code IS NOT NULL
1579 THEN
1580 IF OZF_Utility_PVT.check_lookup_exists(
1581 p_lookup_type => 'OZF_OFFER_STATUS',
1582 p_lookup_code => p_offers_rec.status_code
1583 ) = FND_API.g_false
1584 THEN
1585 OZF_Utility_PVT.Error_Message('OZF_OFR_BAD_STATUS_CODE') ;
1586 x_return_status := FND_API.g_ret_sts_error;
1587 RETURN;
1588 END IF;
1589 END IF;
1590
1591 -- offer_type
1592 IF p_offers_rec.offer_type <> FND_API.g_miss_char
1593 AND p_offers_rec.offer_type IS NOT NULL
1594 THEN
1595 IF OZF_Utility_PVT.check_lookup_exists(
1596 p_lookup_type => 'OZF_OFFER_TYPE',
1597 p_lookup_code => p_offers_rec.offer_type
1598 ) = FND_API.g_false
1599 THEN
1600 OZF_Utility_PVT.Error_Message('OZF_OFR_BAD_OFFER_TYPE') ;
1601 x_return_status := FND_API.g_ret_sts_error;
1602 RETURN;
1603 END IF;
1604 END IF;
1605
1606 -- break_type
1607 IF p_offers_rec.offer_type = 'OID' THEN
1608 IF OZF_Utility_PVT.check_lookup_exists(
1609 p_lookup_type => 'OZF_OFFER_BREAK_TYPE',
1610 p_lookup_code => p_offers_rec.break_type
1611 ) = FND_API.g_false
1612 THEN
1613 OZF_Utility_PVT.Error_Message('OZF_OFR_BAD_BREAK_TYPE') ;
1614 x_return_status := FND_API.g_ret_sts_error;
1615 RETURN;
1616 END IF;
1617 END IF;
1618 /*
1619 IF p_offers_rec.autopay_method <> FND_API.g_miss_char
1620 AND p_offers_rec.autopay_method IS NOT NULL
1621 THEN
1622 IF OZF_Utility_PVT.check_lookup_exists(
1623 p_lookup_table_name => 'OZF_lookups'
1624 ,p_lookup_type => 'OZF_OFFER_AUTOPAY_METHOD'
1625 ,p_lookup_code => p_offers_rec.autopay_method
1626 ) = FND_API.g_false
1627 THEN
1628 --OZF_Utility_PVT.Error_Message('OZF_OFR_BAD_MODIFIER_LEVEL_CODE') ;
1629 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1630 THEN
1631 FND_MESSAGE.set_name('OZF', 'OZF_OFFR_BAD_PAYMENT_METHOD');
1632 FND_MSG_PUB.add;
1633 END IF;
1634 x_return_status := FND_API.g_ret_sts_error;
1635 RETURN;
1636 END IF;
1637 END IF;
1638
1639 IF p_offers_rec.autopay_party_attr <> FND_API.g_miss_char
1640 AND p_offers_rec.autopay_party_attr IS NOT NULL
1641 THEN
1642 IF OZF_Utility_PVT.check_lookup_exists(
1643 p_lookup_table_name => 'ozf_lookups'
1644 ,p_lookup_type => 'OZF_AUTOPAY_CUST_TYPES'
1645 ,p_lookup_code => p_offers_rec.autopay_party_attr
1646 ) = FND_API.g_false
1647 THEN
1648 --OZF_Utility_PVT.Error_Message('OZF_OFR_BAD_MODIFIER_LEVEL_CODE') ;
1649 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1650 THEN
1651 FND_MESSAGE.set_name('OZF', 'OZF_OFFR_BAD_AUTOPAY_CUSTTYPE');
1652 FND_MSG_PUB.add;
1653 END IF;
1654 x_return_status := FND_API.g_ret_sts_error;
1655 RETURN;
1656 END IF;
1657 END IF;
1658 */
1659 END check_offers_Lookup_items;
1660
1661 PROCEDURE check_offers_flag_items(
1662 p_offers_rec IN offers_rec_type,
1663 x_return_status OUT NOCOPY VARCHAR2
1664 )
1665 IS
1666 BEGIN
1667
1668 x_return_status := FND_API.g_ret_sts_success;
1669
1670 ----------------------- active_flag ------------------------
1671 IF p_offers_rec.reusable <> FND_API.g_miss_char
1672 AND p_offers_rec.reusable IS NOT NULL
1673 THEN
1674 IF OZF_Utility_PVT.is_Y_or_N(p_offers_rec.reusable) = FND_API.g_false
1675 THEN
1676 OZF_Utility_PVT.Error_Message('OZF_OFR_BAD_REUSABLE_FLAG');
1677 x_return_status := FND_API.g_ret_sts_error;
1678 RETURN;
1679 END IF;
1680 END IF;
1681
1682 END check_offers_flag_items;
1683
1684
1685 PROCEDURE check_offers_inter_entity(
1686 p_offers_rec IN offers_rec_type
1687 ,x_return_status OUT NOCOPY VARCHAR2
1688 )
1689 IS
1690
1691 l_start_date DATE ;
1692 l_end_date DATE;
1693
1694 BEGIN
1695
1696 x_return_status := FND_API.g_ret_sts_success;
1697
1698 --checking the perf date from and to
1699
1700 IF p_offers_rec.perf_date_from IS NOT NULL
1701 AND p_offers_rec.perf_date_from <> FND_API.G_MISS_DATE
1702 AND p_offers_rec.perf_date_to IS NOT NULL
1703 AND p_offers_rec.perf_date_to <> FND_API.G_MISS_DATE
1704 THEN
1705 l_start_date := p_offers_rec.perf_date_from;
1706 l_end_date := p_offers_rec.perf_date_to;
1707 IF l_start_date > l_end_date THEN
1708 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1709 THEN
1710 FND_MESSAGE.set_name('OZF', 'OZF_OFR_SHIP_START_AFTER_END');
1711 FND_MSG_PUB.add;
1712 END IF;
1713 x_return_status := FND_API.g_ret_sts_error;
1714 END IF;
1715 END IF;
1716
1717 END check_offers_inter_entity;
1718
1719
1720
1721 PROCEDURE Check_offers_Items (
1722 P_offers_rec IN offers_rec_type,
1723 p_validation_mode IN VARCHAR2,
1724 x_return_status OUT NOCOPY VARCHAR2
1725 )
1726 IS
1727 BEGIN
1728
1729 -- Check Items Uniqueness API calls
1730 check_offers_uk_items(
1731 p_offers_rec => p_offers_rec,
1732 p_validation_mode => p_validation_mode,
1733 x_return_status => x_return_status);
1734 IF x_return_status <> FND_API.g_ret_sts_success THEN
1735 RETURN;
1736 END IF;
1737
1738 -- Check Items Required/NOT NULL API calls
1739 check_offers_req_items(
1740 p_offers_rec => p_offers_rec,
1741 p_validation_mode => p_validation_mode,
1742 x_return_status => x_return_status);
1743 IF x_return_status <> FND_API.g_ret_sts_success THEN
1744 RETURN;
1745 END IF;
1746
1747 -- Check Items Foreign Keys API calls
1748
1749 check_offers_FK_items(
1750 p_offers_rec => p_offers_rec,
1751 x_return_status => x_return_status);
1752 IF x_return_status <> FND_API.g_ret_sts_success THEN
1753 RETURN;
1754 END IF;
1755
1756 -- Check Items Lookups
1757
1758 check_offers_Lookup_items(
1759 p_offers_rec => p_offers_rec,
1760 x_return_status => x_return_status);
1761 IF x_return_status <> FND_API.g_ret_sts_success THEN
1762 RETURN;
1763 END IF;
1764
1765 -- check the flags
1766
1767 check_offers_flag_items(
1768 p_offers_rec => p_offers_rec
1769 ,x_return_status => x_return_status);
1770 IF x_return_status <> FND_API.g_ret_sts_success THEN
1771 RETURN;
1772 END IF;
1773
1774 --check the offer inter entity
1775 check_offers_inter_entity(
1776 p_offers_rec => p_offers_rec
1777 ,x_return_status => x_return_status);
1778 IF x_return_status <> FND_API.g_ret_sts_success THEN
1779 RETURN;
1780 END IF;
1781
1782 END Check_offers_Items;
1783
1784
1785
1786 PROCEDURE Validate_offers(
1787 p_api_version_number IN NUMBER,
1788 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1789 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1790 p_offers_rec IN offers_rec_type,
1791 x_return_status OUT NOCOPY VARCHAR2,
1792 x_msg_count OUT NOCOPY NUMBER,
1793 x_msg_data OUT NOCOPY VARCHAR2
1794 )
1795 IS
1796 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Offers';
1797 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1798 l_object_version_number NUMBER;
1799 l_offers_rec OZF_Promotional_Offers_PVT.offers_rec_type;
1800
1801
1802 BEGIN
1803 -- Standard Start of API savepoint
1804 SAVEPOINT VALIDATE_Offers_;
1805
1806 -- Standard call to check for call compatibility.
1807 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1808 p_api_version_number,
1809 l_api_name,
1810 G_PKG_NAME)
1811 THEN
1812 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1813 END IF;
1814
1815 -- Initialize message list if p_init_msg_list is set to TRUE.
1816 IF FND_API.to_Boolean( p_init_msg_list )
1817 THEN
1818 FND_MSG_PUB.initialize;
1819 END IF;
1820
1821 x_return_status := FND_API.g_ret_sts_success;
1822
1823 Complete_offers_Rec(
1824 p_offers_rec => p_offers_rec,
1825 x_complete_rec => l_offers_rec
1826 );
1827
1828 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1829 Check_offers_Items(
1830 p_offers_rec => l_offers_rec,
1831 p_validation_mode => JTF_PLSQL_API.g_update,
1832 x_return_status => x_return_status
1833 );
1834
1835 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1836 RAISE FND_API.G_EXC_ERROR;
1837 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1838 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1839 END IF;
1840 END IF;
1841
1842 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1843 Validate_offers_Rec(
1844 p_api_version_number => 1.0,
1845 p_init_msg_list => FND_API.G_FALSE,
1846 x_return_status => x_return_status,
1847 x_msg_count => x_msg_count,
1848 x_msg_data => x_msg_data,
1849 p_offers_rec => l_offers_rec);
1850
1851 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1852 RAISE FND_API.G_EXC_ERROR;
1853 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1854 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1855 END IF;
1856 END IF;
1857
1858 -- Initialize API return status to SUCCESS
1859 x_return_status := FND_API.G_RET_STS_SUCCESS;
1860
1861 -- Standard call to get message count and if count is 1, get message info.
1862 FND_MSG_PUB.Count_And_Get
1863 (p_count => x_msg_count,
1864 p_data => x_msg_data
1865 );
1866
1867 EXCEPTION
1868
1869 WHEN OZF_Utility_PVT.resource_locked THEN
1870 x_return_status := FND_API.g_ret_sts_error;
1871 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
1872
1873 WHEN FND_API.G_EXC_ERROR THEN
1874 ROLLBACK TO VALIDATE_Offers_;
1875 x_return_status := FND_API.G_RET_STS_ERROR;
1876 -- Standard call to get message count and if count=1, get the message
1877 FND_MSG_PUB.Count_And_Get (
1878 p_encoded => FND_API.G_FALSE,
1879 p_count => x_msg_count,
1880 p_data => x_msg_data
1881 );
1882
1883 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1884 ROLLBACK TO VALIDATE_Offers_;
1885 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1886 -- Standard call to get message count and if count=1, get the message
1887 FND_MSG_PUB.Count_And_Get (
1888 p_encoded => FND_API.G_FALSE,
1889 p_count => x_msg_count,
1890 p_data => x_msg_data
1891 );
1892
1893 WHEN OTHERS THEN
1894 ROLLBACK TO VALIDATE_Offers_;
1895 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1896 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1897 THEN
1898 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1899 END IF;
1900 -- Standard call to get message count and if count=1, get the message
1901 FND_MSG_PUB.Count_And_Get (
1902 p_encoded => FND_API.G_FALSE,
1903 p_count => x_msg_count,
1904 p_data => x_msg_data
1905 );
1906 End Validate_Offers;
1907
1908
1909 PROCEDURE Validate_offers_rec(
1910 p_api_version_number IN NUMBER,
1911 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1912 x_return_status OUT NOCOPY VARCHAR2,
1913 x_msg_count OUT NOCOPY NUMBER,
1914 x_msg_data OUT NOCOPY VARCHAR2,
1915 p_offers_rec IN offers_rec_type
1916 )
1917 IS
1918
1919 l_api_name varchar2(20) := 'Validate_offers_rec';
1920 BEGIN
1921 -- Initialize message list if p_init_msg_list is set to TRUE.
1922 IF FND_API.to_Boolean( p_init_msg_list )
1923 THEN
1924 FND_MSG_PUB.initialize;
1925 END IF;
1926
1927 -- Initialize API return status to SUCCESS
1928 x_return_status := FND_API.G_RET_STS_SUCCESS;
1929
1930 -- Hint: Validate data
1931 -- If data not valid
1932 -- THEN
1933 -- x_return_status := FND_API.G_RET_STS_ERROR;
1934
1935 -- Standard call to get message count and if count is 1, get message info.
1936 FND_MSG_PUB.Count_And_Get
1937 (p_count => x_msg_count,
1938 p_data => x_msg_data
1939 );
1940 END Validate_offers_Rec;
1941
1942 PROCEDURE handle_status(
1943 p_user_status_id IN NUMBER,
1944 x_status_code OUT NOCOPY VARCHAR2,
1945 x_return_status OUT NOCOPY VARCHAR2
1946 )
1947 IS
1948
1949 l_status_code VARCHAR2(30);
1950
1951 CURSOR c_status_code IS
1952 SELECT system_status_code
1953 FROM ams_user_statuses_vl
1954 WHERE user_status_id = p_user_status_id
1955 AND system_status_type = 'OZF_OFFER_STATUS'
1956 AND enabled_flag = 'Y';
1957
1958 BEGIN
1959
1960 x_return_status := FND_API.g_ret_sts_success;
1961
1962 OPEN c_status_code;
1963 FETCH c_status_code INTO l_status_code;
1964 CLOSE c_status_code;
1965
1966 IF l_status_code IS NULL THEN
1967 x_return_status := FND_API.g_ret_sts_error;
1968 OZF_Utility_PVT.error_message('OZF_OFFR_BAD_USER_STATUS_ID');
1969 END IF;
1970
1971 x_status_code := l_status_code;
1972
1973 END handle_status;
1974
1975 PROCEDURE handle_status(
1976 x_status_id OUT NOCOPY NUMBER,
1977 x_return_status OUT NOCOPY VARCHAR2
1978 )
1979 IS
1980
1981 l_status_id NUMBER;
1982
1983 CURSOR c_status_id IS
1984 SELECT user_status_id
1985 FROM ams_user_statuses_vl
1986 WHERE system_status_type = 'OZF_OFFER_STATUS'
1987 AND system_status_code = 'DRAFT'
1988 AND default_flag = 'Y'
1989 AND enabled_flag = 'Y';
1990
1991 BEGIN
1992
1993 x_return_status := FND_API.g_ret_sts_success;
1994
1995 OPEN c_status_id;
1996 FETCH c_status_id INTO l_status_id;
1997 CLOSE c_status_id;
1998
1999 IF l_status_id IS NULL THEN
2000 x_return_status := FND_API.g_ret_sts_error;
2001 OZF_Utility_PVT.error_message('OZF_OFFR_BAD_USER_STATUS_ID');
2002 END IF;
2003
2004 x_status_id := l_status_id;
2005
2006 END handle_status;
2007
2008 END OZF_Promotional_Offers_PVT;