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