[Home] [Help]
PACKAGE BODY: APPS.OZF_ACT_OFFERS_PVT
Source
1 PACKAGE BODY OZF_Act_Offers_PVT AS
2 /* $Header: ozfvoffb.pls 120.0 2005/06/01 01:54:23 appldev noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'OZF_Act_Offers_PVT';
5
6
7 /*****************************************************************************/
8 -- Procedure: create_act_offer
9 --
10 -- History
11 -- 01/12/2000 julou created
12 -- 04/11/2000 holiu add new columns
13 -- 16-May-2000 choang Replaced call to get_source_code with get_new_source_code
14 -- to implement new source code generation algorithm.
15 -- 06-Jun-2000 ptendulk Revert back to old source code api
16 -------------------------------------------------------------------------------
17 PROCEDURE Create_Act_Offer
18 (
19 p_api_version IN NUMBER,
20 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
21 p_commit IN VARCHAR2 := FND_API.g_false,
22 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
23
24 x_return_status OUT NOCOPY VARCHAR2,
25 x_msg_count OUT NOCOPY NUMBER,
26 x_msg_data OUT NOCOPY VARCHAR2,
27
28 p_act_offer_rec IN act_offer_rec_type,
29 x_act_offer_id OUT NOCOPY NUMBER
30 )
31 IS
32
33 CURSOR c_offer_code(l_id NUMBER) IS
34 SELECT offer_code
35 FROM ozf_offers
36 WHERE qp_list_header_id = l_id;
37
38 l_api_version CONSTANT NUMBER := 1.0;
39 l_api_name CONSTANT VARCHAR2(30) := 'create_act_offer';
40 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
41 l_msg_data VARCHAR2(2000);
42 l_msg_count NUMBER;
43 l_return_status VARCHAR2(1);
44 l_act_offer_rec act_offer_rec_type := p_act_offer_rec;
45 l_act_offer_count NUMBER;
46 l_sourcecode_id NUMBER;
47 l_custom_setup_id NUMBER;
48 l_offer_code VARCHAR2(30);
49 /*
50 CURSOR c_custom_setup IS
51 SELECT custom_setup_id
52 FROM ams_custom_setups_vl
53 WHERE object_type = 'OFFR'
54 AND activity_type_code = p_act_offer_rec.offer_type;
55 */
56 CURSOR c_act_offer_seq IS
57 SELECT ozf_act_offers_s.NEXTVAL
58 FROM DUAL;
59
60 CURSOR c_act_offer_count(act_offer_id IN NUMBER) IS
61 SELECT COUNT(*)
62 FROM ozf_act_offers
63 WHERE activity_offer_id = act_offer_id;
64
65 BEGIN
66
67 -- initialize
68 SAVEPOINT create_act_offer;
69
70 IF FND_API.to_boolean(p_init_msg_list) THEN
71 FND_MSG_PUB.initialize;
72 END IF;
73
74 IF NOT FND_API.compatible_api_call
75 (
76 l_api_version,
77 p_api_version,
78 l_api_name,
79 g_pkg_name
80 )
81 THEN
82 RAISE FND_API.g_exc_unexpected_error;
83 END IF;
84
85 x_return_status := FND_API.g_ret_sts_success;
86 /*
87 OPEN c_custom_setup;
88 FETCH c_custom_setup INTO l_custom_setup_id;
89 CLOSE c_custom_setup;
90 */
91 -- validate
92 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
93 validate_act_offer
94 (
95 p_api_version => l_api_version,
96 p_init_msg_list => p_init_msg_list,
97 p_validation_level => p_validation_level,
98 x_return_status => l_return_status,
99 x_msg_count => x_msg_count,
100 x_msg_data => x_msg_data,
101 p_act_offer_rec => l_act_offer_rec
102 );
103
104 IF l_return_status = FND_API.g_ret_sts_error THEN
105 RAISE FND_API.g_exc_error;
106 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
107 RAISE FND_API.g_exc_unexpected_error;
108 END IF;
109 END IF;
110
111 -- insert
112 --MY_Utility_PVT.debug_message(l_full_name || ': insert');
113
114 IF l_act_offer_rec.activity_offer_id IS NULL THEN
115 LOOP
116 OPEN c_act_offer_seq;
117 FETCH c_act_offer_seq INTO l_act_offer_rec.activity_offer_id;
118 CLOSE c_act_offer_seq;
119
120 OPEN c_act_offer_count(l_act_offer_rec.activity_offer_id);
121 FETCH c_act_offer_count INTO l_act_offer_count;
122 CLOSE c_act_offer_count;
123
124 EXIT WHEN l_act_offer_count = 0;
125 END LOOP;
126 END IF;
127 /*
128 -- default offer_code from AMS_SOURCE_CODES
129 IF l_act_offer_rec.offer_code IS NULL THEN -- need a new offer_code
130 --========================================================================
131 -- Following source generation code is revert back to old source code
132 -- generation api by ptendulk on 06-Jun-2000 As the new api will only
133 -- be available with R2
134 --========================================================================
135 --
136 -- choang - 16-May-2000
137 -- Modified to use new source code generation
138 -- function for internal rollout requirement #20.
139 -- NOTE: Need to implement global flag.]
140 -- l_act_offer_rec.offer_code := AMS_SourceCode_PVT.get_new_source_code (
141 -- p_object_type => 'OFFR',
142 -- p_custsetup_id => l_custom_setup_id,
143 -- p_global_flag => FND_API.g_false
144 -- );
145 l_act_offer_rec.offer_code := AMS_SourceCode_PVT.get_source_code
146 (
147 'OFFR',
148 l_act_offer_rec.offer_type
149 );
150 END IF;
151 */
152 -- set primary_offer_flag to default value
153 IF l_act_offer_rec.primary_offer_flag IS NULL
154 OR l_act_offer_rec.primary_offer_flag = FND_API.g_miss_char
155 THEN
156 l_act_offer_rec.primary_offer_flag := 'N';
157 END IF;
158
159 OPEN c_offer_code(l_act_offer_rec.qp_list_header_id);
160 FETCH c_offer_code INTO l_offer_code;
161 CLOSE c_offer_code;
162
163 INSERT INTO OZF_ACT_OFFERS
164 (
165 activity_offer_id,
166 last_update_date,
167 last_updated_by,
168 creation_date,
169 created_by,
170 last_update_login,
171 object_version_number,
172 act_offer_used_by_id,
173 arc_act_offer_used_by,
174 primary_offer_flag,
175 -- offer_type,
176 offer_code,
177 active_period_set,
178 active_period,
179 -- start_date,
180 -- end_date,
181 -- order_date_from,
182 -- order_date_to,
183 -- ship_date_from,
184 -- ship_date_to,
185 -- perf_date_from,
186 -- perf_date_to,
187 -- status_code,
188 -- status_date,
189 -- offer_amount,
190 -- lumpsum_payment_type,
191 qp_list_header_id
192 )
193 VALUES
194 (
195 l_act_offer_rec.activity_offer_id,
196 SYSDATE,
197 FND_GLOBAL.user_id,
198 SYSDATE,
199 FND_GLOBAL.user_id,
200 FND_GLOBAL.conc_login_id,
201 1,
202 l_act_offer_rec.act_offer_used_by_id,
203 l_act_offer_rec.arc_act_offer_used_by,
204 l_act_offer_rec.primary_offer_flag,
205 -- l_act_offer_rec.offer_type,
206 l_offer_code,
207 l_act_offer_rec.active_period_set,
208 l_act_offer_rec.active_period,
209 -- l_act_offer_rec.start_date,
210 -- l_act_offer_rec.end_date,
211 -- l_act_offer_rec.order_date_from,
212 -- l_act_offer_rec.order_date_to,
213 -- l_act_offer_rec.ship_date_from,
214 -- l_act_offer_rec.ship_date_to,
215 -- l_act_offer_rec.perf_date_from,
216 -- l_act_offer_rec.perf_date_to,
217 -- l_act_offer_rec.status_code,
218 -- l_act_offer_rec.status_date,
219 -- l_act_offer_rec.offer_amount,
220 -- l_act_offer_rec.lumpsum_payment_type,
221 l_act_offer_rec.qp_list_header_id
222 );
223
224 -- insert offer_code into AMS_SOURCE_CODES
225 -- commented by julou 05/03/2001. offer_code is gone
226 /* AMS_SourceCode_PVT.create_sourcecode(
227 p_api_version => 1.0,
228 p_init_msg_list => FND_API.g_false,
229 p_commit => FND_API.g_false,
230 p_validation_level => FND_API.g_valid_level_full,
231
232 x_return_status => l_return_status,
233 x_msg_count => l_msg_count,
234 x_msg_data => l_msg_data,
235
236 p_sourcecode => l_act_offer_rec.offer_code,
237 p_sourcecode_for => 'OFFR',
238 p_sourcecode_for_id => l_act_offer_rec.activity_offer_id,
239 x_sourcecode_id => l_sourcecode_id
240 );
241 */
242 -- end of comment
243
244 -- added by julou on 03/08/2000
245 -- indicate offer has been defined for the campaign
246 /* AMS_ObjectAttribute_PVT.modify_object_attribute(
247 p_api_version => l_api_version,
248 p_init_msg_list => FND_API.g_false,
249 p_commit => FND_API.g_false,
250 p_validation_level => FND_API.g_valid_level_full,
251
252 x_return_status => l_return_status,
253 x_msg_count => x_msg_count,
254 x_msg_data => x_msg_data,
255
256 p_object_type => l_act_offer_rec.arc_act_offer_used_by,
257 p_object_id => l_act_offer_rec.act_offer_used_by_id,
258 p_attr => 'OFFR',
259 p_attr_defined_flag => 'Y'
260 );
261
262 IF l_return_status = FND_API.g_ret_sts_error THEN
263 RAISE FND_API.g_exc_error;
264 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
265 RAISE FND_API.g_exc_unexpected_error;
266 END IF; */
267
268 -- create attributes for this offer
269 -- commented by julou 05/03/2001. custom_setup_id is gone
270 /*
271 IF l_custom_setup_id IS NOT NULL THEN
272 AMS_ObjectAttribute_PVT.create_object_attributes(
273 p_api_version => 1.0,
274 p_init_msg_list => FND_API.g_false,
275 p_commit => FND_API.g_false,
276 p_validation_level => FND_API.g_valid_level_full,
277 x_return_status => l_return_status,
278 x_msg_count => x_msg_count,
279 x_msg_data => x_msg_data,
280 p_object_type => 'OFFR',
281 p_object_id => l_act_offer_rec.activity_offer_id,
282 p_setup_id => l_custom_setup_id
283 );
284 IF l_return_status = FND_API.g_ret_sts_error THEN
285 RAISE FND_API.g_exc_error;
286 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
287 RAISE FND_API.g_exc_unexpected_error;
288 END IF;
289 END IF;
290 */
291 -- end of comment
292
293 -- finish
294 x_act_offer_id := l_act_offer_rec.activity_offer_id;
295
296 IF FND_API.to_boolean(p_commit) THEN
297 COMMIT;
298 END IF;
299
300 FND_MSG_PUB.count_and_get
301 (
302 p_encoded => FND_API.g_false,
303 p_count => x_msg_count,
304 p_data => x_msg_data
305 );
306
307 EXCEPTION
308 WHEN FND_API.g_exc_error THEN
309 ROLLBACK TO create_act_offer;
310 x_return_status := FND_API.g_ret_sts_error;
311 FND_MSG_PUB.count_and_get
312 (
313 p_encoded => FND_API.g_false,
314 p_count => x_msg_count,
315 p_data => x_msg_data
316 );
317
318 WHEN FND_API.g_exc_unexpected_error THEN
319 ROLLBACK TO create_act_offer;
320 x_return_status := FND_API.g_ret_sts_unexp_error;
321 FND_MSG_PUB.count_and_get
322 (
323 p_encoded => FND_API.g_false,
324 p_count => x_msg_count,
325 p_data => x_msg_data
326 );
327
328 WHEN OTHERS THEN
329 ROLLBACK TO create_act_offer;
330 x_return_status :=FND_API.g_ret_sts_unexp_error;
331 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
332 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
333 END IF;
334 FND_MSG_PUB.count_and_get
335 (
336 p_encoded => FND_API.g_false,
337 p_count => x_msg_count,
338 p_data => x_msg_data
339 );
340 END Create_Act_Offer;
341
342
343 /*****************************************************************************/
344 -- Procedure: update_act_offer
345 --
346 -- History
347 -- 01/12/2000 julou created
348 -- 01/14/2000 ptendulk modified
349 -- 04/11/2000 holiu add new columns
350 -------------------------------------------------------------------------------
351 PROCEDURE Update_Act_Offer
352 (
353 p_api_version IN NUMBER,
354 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
355 p_commit IN VARCHAR2 := FND_API.g_false,
356 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
357
358 x_return_status OUT NOCOPY VARCHAR2,
359 x_msg_count OUT NOCOPY NUMBER,
360 x_msg_data OUT NOCOPY VARCHAR2,
361
362 p_act_offer_rec IN act_offer_rec_type
363 )
364 IS
365
366 l_api_version CONSTANT NUMBER := 1.0;
367 l_api_name CONSTANT VARCHAR2(30) := 'update_act_offer';
368 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
369 l_msg_data VARCHAR2(2000);
370 l_msg_count NUMBER;
371 l_return_status VARCHAR2(1);
372 l_act_offer_rec act_offer_rec_type := p_act_offer_rec;
373 l_sourcecode_id NUMBER;
374
375 BEGIN
376
377 -- initialize
378 SAVEPOINT update_act_offer;
379
380 IF FND_API.to_boolean(p_init_msg_list) THEN
381 FND_MSG_PUB.initialize;
382 END IF;
383
384 IF NOT FND_API.compatible_api_call
385 (
386 l_api_version,
387 p_api_version,
388 l_api_name,
389 g_pkg_name
390 )
391 THEN
392 RAISE FND_API.g_exc_unexpected_error;
393 END IF;
394
395 x_return_status := FND_API.g_ret_sts_success;
396
397 -- validate
398 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
399 check_items
400 (
401 p_validation_mode => JTF_PLSQL_API.g_update,
402 x_return_status => l_return_status,
403 p_act_offer_rec => l_act_offer_rec
404 );
405
406 IF l_return_status = FND_API.g_ret_sts_error THEN
407 RAISE FND_API.g_exc_error;
408 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
409 RAISE FND_API.g_exc_unexpected_error;
410 END IF;
411 END IF;
412
413 complete_rec
414 (
415 p_act_offer_rec,
416 l_act_offer_rec
417 );
418
419 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
420 check_record
421 (
422 p_act_offer_rec => p_act_offer_rec,
423 p_complete_rec => l_act_offer_rec,
424 x_return_status => l_return_status
425 );
426
427 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
428 RAISE FND_API.g_exc_unexpected_error;
429 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
430 RAISE FND_API.g_exc_error;
431 END IF;
432 END IF;
433
434 -- The second line below is modified by ptendulk on Jan 14th:
435 -- if the flag is not sent for update, don't update it.
436
437 -- set primary_offer_flag to default value
438 IF l_act_offer_rec.primary_offer_flag IS NULL
439 -- OR l_act_offer_rec.primary_offer_flag = FND_API.g_miss_char
440 THEN
441 l_act_offer_rec.primary_offer_flag := 'N';
442 END IF;
443
444 -- update
445 UPDATE ozf_act_offers SET
446 last_update_date = SYSDATE,
447 last_updated_by = FND_GLOBAL.user_id,
448 object_version_number = l_act_offer_rec.object_version_number + 1,
449 last_update_login = FND_GLOBAL.conc_login_id,
450 act_offer_used_by_id = l_act_offer_rec.act_offer_used_by_id,
451 arc_act_offer_used_by = l_act_offer_rec.arc_act_offer_used_by,
452 primary_offer_flag = l_act_offer_rec.primary_offer_flag,
453 -- offer_type = l_act_offer_rec.offer_type,
454 -- offer_code = l_act_offer_rec.offer_code,
455 active_period_set = l_act_offer_rec.active_period_set,
456 active_period = l_act_offer_rec.active_period,
457 -- start_date = l_act_offer_rec.start_date,
458 -- end_date = l_act_offer_rec.end_date,
459 -- order_date_from = l_act_offer_rec.order_date_from,
460 -- order_date_to = l_act_offer_rec.order_date_to,
461 -- ship_date_from = l_act_offer_rec.ship_date_from,
462 -- ship_date_to = l_act_offer_rec.ship_date_to,
463 -- perf_date_from = l_act_offer_rec.perf_date_from,
464 -- perf_date_to = l_act_offer_rec.perf_date_to,
465 -- status_code = l_act_offer_rec.status_code,
466 -- status_date = l_act_offer_rec.status_date,
467 -- offer_amount = l_act_offer_rec.offer_amount,
468 -- lumpsum_payment_type = l_act_offer_rec.lumpsum_payment_type,
469 qp_list_header_id = l_act_offer_rec.qp_list_header_id
470 WHERE activity_offer_id = l_act_offer_rec.activity_offer_id
471 AND object_version_number = l_act_offer_rec.object_version_number;
472
473 IF (SQL%NOTFOUND) THEN
474 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
475 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
476 FND_MSG_PUB.add;
477 END IF;
478 RAISE FND_API.g_exc_error;
479 END IF;
480
481 -- finish
482 IF FND_API.to_boolean(p_commit) THEN
483 COMMIT;
484 END IF;
485
486 FND_MSG_PUB.count_and_get
487 (
488 P_ENCODED => FND_API.g_false,
489 p_count => x_msg_count,
490 p_data => x_msg_data
491 );
492
493 EXCEPTION
494 WHEN FND_API.g_exc_error THEN
495 ROLLBACK TO update_act_offer;
496 x_return_status := FND_API.g_ret_sts_error;
497 FND_MSG_PUB.count_and_get
498 (
499 p_encoded => FND_API.g_false,
500 p_count => x_msg_count,
501 p_data => x_msg_data
502 );
503
504 WHEN FND_API.g_exc_unexpected_error THEN
505 ROLLBACK TO update_act_offer;
506 x_return_status := FND_API.g_ret_sts_unexp_error;
507 FND_MSG_PUB.count_and_get
508 (
509 p_encoded => FND_API.g_false,
510 p_count => x_msg_count,
511 p_data => x_msg_data
512 );
513
514 WHEN OTHERS THEN
515 ROLLBACK TO update_act_offer;
516 x_return_status :=FND_API.g_ret_sts_unexp_error;
517 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
518 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
519 END IF;
520 FND_MSG_PUB.count_and_get
521 (
522 p_encoded => FND_API.g_false,
523 p_count => x_msg_count,
524 p_data => x_msg_data
525 );
526 END Update_Act_Offer;
527
528
529 /*****************************************************************************/
530 -- Procedure: delete_act_offer
531 --
532 -- History
533 -- 11/22/1999 julou created
534 -------------------------------------------------------------------------------
535 PROCEDURE Delete_Act_Offer
536 (
537 p_api_version IN NUMBER,
538 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
539 p_commit IN VARCHAR2 := FND_API.g_false,
540
541 x_return_status OUT NOCOPY VARCHAR2,
542 x_msg_count OUT NOCOPY NUMBER,
543 x_msg_data OUT NOCOPY VARCHAR2,
544
545 p_act_offer_id IN NUMBER,
546 p_object_version IN NUMBER
547 )
548 IS
549
550 l_api_version CONSTANT NUMBER := 1.0;
551 l_api_name CONSTANT VARCHAR2(30) := 'delete_act_offer';
552 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
553 l_used_by_id NUMBER;
554 l_used_by VARCHAR2(30);
555 l_dummy NUMBER;
556
557 CURSOR c_used_by IS
558 SELECT act_offer_used_by_id, arc_act_offer_used_by
559 FROM ozf_act_offers
560 WHERE activity_offer_id = p_act_offer_id;
561
562 CURSOR c_offer IS
563 SELECT 1
564 FROM ozf_act_offers
565 WHERE act_offer_used_by_id = l_used_by_id
566 AND arc_act_offer_used_by = l_used_by;
567
568 BEGIN
569
570 -- initialize
571 SAVEPOINT delete_act_offer;
572
573 IF FND_API.to_boolean(p_init_msg_list) THEN
574 FND_MSG_PUB.initialize;
575 END IF;
576
577 IF NOT FND_API.compatible_api_call
578 (
579 l_api_version,
580 p_api_version,
581 l_api_name,
582 g_pkg_name
583 )
584 THEN
585 RAISE FND_API.g_exc_unexpected_error;
586 END IF;
587
588 x_return_status := FND_API.g_ret_sts_success;
589
590 OPEN c_used_by;
591 FETCH c_used_by INTO l_used_by_id, l_used_by;
592 CLOSE c_used_by;
593
594 -- delete
595 DELETE FROM OZF_ACT_OFFERS
596 WHERE activity_offer_id = p_act_offer_id
597 AND object_version_number = p_object_version;
598
599 IF (SQL%NOTFOUND) THEN
600 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
601 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
602 FND_MSG_PUB.add;
603 END IF;
604 RAISE FND_API.g_exc_error;
605 END IF;
606
607 /* -- added by julou on 03/07/2000
608 -- indicate if there is any other offers for this campaign
609 OPEN c_offer;
610 FETCH c_offer INTO l_dummy;
611 CLOSE c_offer;
612
613 IF l_dummy IS NULL THEN
614 AMS_ObjectAttribute_PVT.modify_object_attribute(
615 p_api_version => l_api_version,
616 p_init_msg_list => FND_API.g_false,
617 p_commit => FND_API.g_false,
618 p_validation_level => FND_API.g_valid_level_full,
619
620 x_return_status => x_return_status,
621 x_msg_count => x_msg_count,
622 x_msg_data => x_msg_data,
623
624 p_object_type => l_used_by,
625 p_object_id => l_used_by_id,
626 p_attr => 'OFFR',
627 p_attr_defined_flag => 'N'
628 );
629
630 IF x_return_status = FND_API.g_ret_sts_error THEN
631 RAISE FND_API.g_exc_error;
632 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
633 RAISE FND_API.g_exc_unexpected_error;
634 END IF;
635 END IF; */
636
637 -- finish
638 IF FND_API.to_boolean(p_commit) THEN
639 COMMIT;
640 END IF;
641
642 FND_MSG_PUB.count_and_get
643 (
644 P_ENCODED => FND_API.g_false,
645 p_count => x_msg_count,
646 p_data => x_msg_data
647 );
648
649 EXCEPTION
650 WHEN FND_API.g_exc_error THEN
651 ROLLBACK TO delete_act_offer;
652 x_return_status := FND_API.g_ret_sts_error;
653 FND_MSG_PUB.count_and_get
654 (
655 p_encoded => FND_API.g_false,
656 p_count => x_msg_count,
657 p_data => x_msg_data
658 );
659
660 WHEN FND_API.g_exc_unexpected_error THEN
661 ROLLBACK TO delete_act_offer;
662 x_return_status := FND_API.g_ret_sts_unexp_error;
663 FND_MSG_PUB.count_and_get
664 (
665 p_encoded => FND_API.g_false,
666 p_count => x_msg_count,
667 p_data => x_msg_data
668 );
669
670 WHEN OTHERS THEN
671 ROLLBACK TO delete_act_offer;
672 x_return_status :=FND_API.g_ret_sts_unexp_error;
673 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
674 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
675 END IF;
676 FND_MSG_PUB.count_and_get
677 (
678 p_encoded => FND_API.g_false,
679 p_count => x_msg_count,
680 p_data => x_msg_data
681 );
682 END Delete_Act_Offer;
683
684
685 /*****************************************************************************/
686 -- Procedure: lock_act_offer
687 --
688 -- History
689 -- 11/22/1999 julou created
690 -------------------------------------------------------------------------------
691 PROCEDURE Lock_Act_Offer
692 (
693 p_api_version IN NUMBER,
694 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
695
696 x_return_status OUT NOCOPY VARCHAR2,
697 x_msg_count OUT NOCOPY NUMBER,
698 x_msg_data OUT NOCOPY VARCHAR2,
699
700 p_act_offer_id IN NUMBER,
701 p_object_version IN NUMBER
702 )
703 IS
704
705 l_api_version CONSTANT NUMBER := 1.0;
706 l_api_name CONSTANT VARCHAR2(30) := 'lock_act_offer';
707 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
708 l_act_offer_id NUMBER;
709
710 CURSOR c_act_offer_b IS
711 SELECT activity_offer_id
712 FROM OZF_ACT_OFFERS
713 WHERE activity_offer_id = p_act_offer_id
714 AND object_version_number = p_object_version
715 FOR UPDATE OF activity_offer_id NOWAIT;
716
717 BEGIN
718
719 -- initialize
720 IF FND_API.to_boolean(p_init_msg_list) THEN
721 FND_MSG_PUB.initialize;
722 END IF;
723
724 IF NOT FND_API.compatible_api_call
725 (
726 l_api_version,
727 p_api_version,
728 l_api_name,
729 g_pkg_name
730 )
731 THEN
732 RAISE FND_API.g_exc_unexpected_error;
733 END IF;
734
735 x_return_status := FND_API.g_ret_sts_success;
736
737 -- lock
738 OPEN c_act_offer_b;
739 FETCH c_act_offer_b INTO l_act_offer_id;
740 IF (c_act_offer_b%NOTFOUND) THEN
741 CLOSE c_act_offer_b;
742 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
743 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
744 FND_MSG_PUB.add;
745 END IF;
746 RAISE FND_API.g_exc_error;
747 END IF;
748 CLOSE c_act_offer_b;
749
750 -- finish
751 FND_MSG_PUB.count_and_get
752 (
753 p_encoded => FND_API.g_false,
754 p_count => x_msg_count,
755 p_data => x_msg_data
756 );
757
758 EXCEPTION
759 WHEN OZF_Utility_PVT.resource_locked THEN
760 x_return_status := FND_API.g_ret_sts_error;
761 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
762 FND_MESSAGE.set_name('OZF', 'OZF_API_RESOURCE_LOCKED');
763 FND_MSG_PUB.add;
764 END IF;
765 FND_MSG_PUB.count_and_get
766 (
767 p_encoded => FND_API.g_false,
768 p_count => x_msg_count,
769 p_data => x_msg_data
770 );
771
772 WHEN FND_API.g_exc_error THEN
773 x_return_status := FND_API.g_ret_sts_error;
774 FND_MSG_PUB.count_and_get
775 (
776 p_encoded => FND_API.g_false,
777 p_count => x_msg_count,
778 p_data => x_msg_data
779 );
780
781 WHEN FND_API.g_exc_unexpected_error THEN
782 x_return_status := FND_API.g_ret_sts_unexp_error;
783 FND_MSG_PUB.count_and_get
784 (
785 p_encoded => FND_API.g_false,
786 p_count => x_msg_count,
787 p_data => x_msg_data
788 );
789
790 WHEN OTHERS THEN
791 x_return_status :=FND_API.g_ret_sts_unexp_error;
792 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
793 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
794 END IF;
795 FND_MSG_PUB.count_and_get
796 (
797 p_encoded => FND_API.g_false,
798 p_count => x_msg_count,
799 p_data => x_msg_data
800 );
801 END Lock_Act_Offer;
802
803
804 /*****************************************************************************/
805 -- Procedure: validate_act_offer
806 --
807 -- History
808 -- 11/29/99 julou Created.
809 -------------------------------------------------------------------------------
810 PROCEDURE Validate_Act_Offer
811 (
812 p_api_version IN NUMBER,
813 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
814 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
815
816 x_return_status OUT NOCOPY VARCHAR2,
817 x_msg_count OUT NOCOPY NUMBER,
818 x_msg_data OUT NOCOPY VARCHAR2,
819
820 p_act_offer_rec IN act_offer_rec_type
821 )
822 IS
823
824 l_api_version CONSTANT NUMBER := 1.0;
825 l_api_name CONSTANT VARCHAR2(30) := 'validate_act_offer';
826 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
827
828 l_return_status VARCHAR2(1);
829
830 BEGIN
831
832 ----------------------- initialize --------------------
833 IF NOT FND_API.compatible_api_call
834 (
835 l_api_version,
836 p_api_version,
837 l_api_name,
838 g_pkg_name
839 )
840 THEN
841 RAISE FND_API.g_exc_unexpected_error;
842 END IF;
843
844 x_return_status := FND_API.g_ret_sts_success;
845
846 ---------------------- validate ------------------------
847 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
848 check_items
849 (
850 p_validation_mode => JTF_PLSQL_API.g_create,
851 x_return_status => l_return_status,
852 p_act_offer_rec => p_act_offer_rec
853 );
854
855 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
856 RAISE FND_API.g_exc_unexpected_error;
857 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
858 RAISE FND_API.g_exc_error;
859 END IF;
860 END IF;
861
862 -- record level
863 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
864 check_record
865 (
866 p_act_offer_rec => p_act_offer_rec,
867 p_complete_rec => p_act_offer_rec,
868 x_return_status => l_return_status
869 );
870
871 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
872 RAISE FND_API.g_exc_unexpected_error;
873 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
874 RAISE FND_API.g_exc_error;
875 END IF;
876 END IF;
877
878 -------------------- finish --------------------------
879 FND_MSG_PUB.count_and_get
880 (
881 p_encoded => FND_API.g_false,
882 p_count => x_msg_count,
883 p_data => x_msg_data
884 );
885
886 EXCEPTION
887 WHEN FND_API.g_exc_error THEN
888 x_return_status := FND_API.g_ret_sts_error;
889 FND_MSG_PUB.count_and_get
890 (
891 p_encoded => FND_API.g_false,
892 p_count => x_msg_count,
893 p_data => x_msg_data
894 );
895
896 WHEN FND_API.g_exc_unexpected_error THEN
897 x_return_status := FND_API.g_ret_sts_unexp_error ;
898 FND_MSG_PUB.count_and_get
899 (
900 p_encoded => FND_API.g_false,
901 p_count => x_msg_count,
902 p_data => x_msg_data
903 );
904
905 WHEN OTHERS THEN
906 x_return_status := FND_API.g_ret_sts_unexp_error;
907 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
908 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
909 END IF;
910 FND_MSG_PUB.count_and_get
911 (
912 p_encoded => FND_API.g_false,
913 p_count => x_msg_count,
914 p_data => x_msg_data
915 );
916 END Validate_Act_Offer;
917
918
919 /*****************************************************************************/
920 -- Procedure: check_req_items
921 --
922 -- History
923 -- 11/22/1999 julou created
924 -------------------------------------------------------------------------------
925 PROCEDURE Check_Req_Items
926 (
927 p_validation_mode IN VARCHAR2,
928 p_act_offer_rec IN act_offer_rec_type,
929 x_return_status OUT NOCOPY VARCHAR2
930 )
931 IS
932
933 BEGIN
934
935 x_return_status := FND_API.g_ret_sts_success;
936
937 -- Following Code is Commented by PTENDULK as Activity Offer ID
938 -- can be null Also No need to send Object version Number
939 -- Date : 14Jan2000
940
941 -- check activity_offer_id
942 IF p_act_offer_rec.activity_offer_id IS NULL
943 AND p_validation_mode = JTF_PLSQL_API.g_update
944 THEN
945 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
946 FND_MESSAGE.set_name('OZF', 'OZF_ACT_OFFER_NO_ACT_OFFER_ID');
947 FND_MSG_PUB.add;
948 END IF;
949
950 x_return_status := FND_API.g_ret_sts_error;
951 RETURN;
952 END IF;
953
954 -- check object_version_number
955 IF p_act_offer_rec.object_version_number IS NULL
956 AND p_validation_mode = JTF_PLSQL_API.g_update
957 THEN
958 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
959 FND_MESSAGE.set_name('OZF', 'OZF_API_NO_OBJ_VER_NUM');
960 FND_MSG_PUB.add;
961 END IF;
962
963 x_return_status := FND_API.g_ret_sts_error;
964 RETURN;
965 END IF;
966
967 -- check act_offer_used_by_id
968 IF p_act_offer_rec.act_offer_used_by_id IS NULL THEN
969 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
970 FND_MESSAGE.set_name('OZF', 'OZF_ACT_OFFER_NO_USED_BY_ID');
971 FND_MSG_PUB.add;
972 END IF;
973
974 x_return_status := FND_API.g_ret_sts_error;
975 RETURN;
976 END IF;
977
978 -- check arc_act_offer_used_by
979 IF p_act_offer_rec.arc_act_offer_used_by IS NULL THEN
980 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
981 FND_MESSAGE.set_name('OZF', 'OZF_ACT_OFFER_NO_USED_BY');
982 FND_MSG_PUB.add;
983 END IF;
984
985 x_return_status := FND_API.g_ret_sts_error;
986 RETURN;
987 END IF;
988
989 -- check qp_list_header_id
990 IF p_act_offer_rec.qp_list_header_id IS NULL THEN
991 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
992 FND_MESSAGE.set_name('OZF', 'OZF_ACT_OFFER_NO_LIST_HEAD_ID');
993 FND_MSG_PUB.add;
994 END IF;
995
996 x_return_status := FND_API.g_ret_sts_error;
997 RETURN;
998 END IF;
999
1000 END Check_Req_Items;
1001
1002
1003 /*****************************************************************************/
1004 -- Procedure: check_uk_items
1005 --
1006 -- History
1007 -- 11/22/1999 julou created
1008 -------------------------------------------------------------------------------
1009 PROCEDURE Check_Uk_Items
1010 (
1011 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1012 p_act_offer_rec IN act_offer_rec_type,
1013 x_return_status OUT NOCOPY VARCHAR2
1014 )
1015 IS
1016
1017 l_uk_flag VARCHAR2(1);
1018
1019 BEGIN
1020
1021 x_return_status := FND_API.g_ret_sts_success;
1022 -- rssharma fixed bug # 2747282
1023 -- don't allow adding duplicate offers to same campaign
1024 -- check PK, if activity_offer_id is passed in, must check if it is duplicate
1025 IF p_validation_mode = JTF_PLSQL_API.g_create
1026 -- AND p_act_offer_rec.activity_offer_id IS NOT NULL
1027 THEN
1028 l_uk_flag := OZF_Utility_PVT.check_uniqueness
1029 (
1030 'OZF_ACT_OFFERS',
1031 ' qp_list_header_id = ' || p_act_offer_rec.qp_list_header_id ||
1032 ' AND act_offer_used_by_id = ' || p_act_offer_rec.act_offer_used_by_id ||
1033 ' AND arc_act_offer_used_by = ' ||p_act_offer_rec.arc_act_offer_used_by
1034 );
1035 ELSIF p_validation_mode = JTF_PLSQL_API.g_update
1036 AND p_act_offer_rec.activity_offer_id IS NOT NULL
1037 THEN
1038 l_uk_flag := OZF_Utility_PVT.check_uniqueness
1039 (
1040 'OZF_ACT_OFFERS',
1041 ' activity_offer_id <> '|| p_act_offer_rec.activity_offer_id ||
1042 ' AND qp_list_header_id = ' || p_act_offer_rec.qp_list_header_id ||
1043 ' AND act_offer_used_by_id = ' || p_act_offer_rec.act_offer_used_by_id ||
1044 ' AND arc_act_offer_used_by = ' ||p_act_offer_rec.arc_act_offer_used_by
1045 );
1046
1047 END IF;
1048
1049 IF l_uk_flag = FND_API.g_false THEN
1050 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1051 FND_MESSAGE.set_name('OZF', 'OZF_ACT_OFFER_DUP_OFFER_ID');
1052 FND_MSG_PUB.add;
1053 END IF;
1054
1055 x_return_status := FND_API.g_ret_sts_error;
1056 RETURN;
1057 END IF;
1058 -- commented by julou 05/03/2001. offer_code is gone.
1059 /*
1060 -- check offer_code
1061 IF p_act_offer_rec.offer_code IS NOT NULL THEN
1062 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1063 l_uk_flag := OZF_Utility_PVT.check_uniqueness
1064 (
1065 'AMS_SOURCE_CODES',
1066 'source_code = ''' || p_act_offer_rec.offer_code || ''''
1067 );
1068 ELSE
1069 l_uk_flag := OZF_Utility_PVT.check_uniqueness
1070 (
1071 'AMS_SOURCE_CODES',
1072 'source_code_for_id <> ' || p_act_offer_rec.activity_offer_id || ' AND '
1073 || 'source_code = ''' || p_act_offer_rec.offer_code || ''''
1074 );
1075 END IF;
1076 END IF;
1077
1078 IF l_uk_flag = FND_API.g_false THEN
1079 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1080 FND_MESSAGE.set_name('OZF', 'OZF_ACT_OFFER_DUP_OFFER_CODE');
1081 FND_MSG_PUB.add;
1082 END IF;
1083
1084 x_return_status := FND_API.g_ret_sts_error;
1085 RETURN;
1086 END IF;
1087 */
1088 -- end of comment
1089
1090 END Check_Uk_Items;
1091
1092
1093 /*****************************************************************************/
1094 -- Procedure: check_fk_items
1095 --
1096 -- History
1097 -- 11/22/1999 julou created
1098 -- 01/14/2000 ptendulk Modified
1099 -- 05/29/2001 julou modified. CSCH is allowable now.
1100 -------------------------------------------------------------------------------
1101 PROCEDURE Check_Fk_Items
1102 (
1103 p_act_offer_rec IN act_offer_rec_type,
1104 x_return_status OUT NOCOPY VARCHAR2
1105 )
1106 IS
1107
1108 l_fk_flag VARCHAR2(1);
1109
1110 BEGIN
1111
1112 x_return_status := FND_API.g_ret_sts_success;
1113
1114 -- Following Code Has been Modified by ptendulk
1115 -- Validate p_act_offer_rec.act_offer_used_by_id against View
1116 -- check act_offer_used_by_id
1117 IF p_act_offer_rec.act_offer_used_by_id <> FND_API.g_miss_num
1118 AND
1119 p_act_offer_rec.act_offer_used_by_id IS NOT NULL
1120 THEN
1121 IF p_act_offer_rec.arc_act_offer_used_by = 'CAMP' THEN
1122 l_fk_flag := OZF_Utility_PVT.check_fk_exists
1123 (
1124 'AMS_CAMPAIGNS_VL',
1125 'campaign_id',
1126 p_act_offer_rec.act_offer_used_by_id
1127 );
1128 ELSE
1129 IF p_act_offer_rec.arc_act_offer_used_by = 'CSCH' THEN
1130 l_fk_flag := OZF_Utility_PVT.check_fk_exists
1131 (
1132 'AMS_CAMPAIGN_SCHEDULES_VL',
1133 'schedule_id',
1134 p_act_offer_rec.act_offer_used_by_id
1135 );
1136 END IF;
1137 END IF;
1138
1139 IF l_fk_flag = FND_API.g_false THEN
1140 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1141 FND_MESSAGE.set_name('OZF', 'OZF_ACT_OFFER_NO_USED_BY_ID');
1142 FND_MSG_PUB.add;
1143 END IF;
1144
1145 x_return_status := FND_API.g_ret_sts_error;
1146 RETURN;
1147 END IF;
1148 END IF;
1149
1150 -- Following Code has been modified by ptendulk
1151 -- Validate against QP_LIST_HEADERS_VL
1152 -- check qp_list_header_id
1153 IF p_act_offer_rec.qp_list_header_id <> FND_API.g_miss_num THEN
1154 l_fk_flag := OZF_Utility_PVT.check_fk_exists
1155 (
1156 'QP_LIST_HEADERS_VL',
1157 'list_header_id',
1158 p_act_offer_rec.qp_list_header_id
1159 );
1160
1161 IF l_fk_flag = FND_API.g_false THEN
1162 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1163 FND_MESSAGE.set_name('OZF', 'OZF_ACT_OFFER_NO_LIST_HEAD_ID');
1164 FND_MSG_PUB.add;
1165 END IF;
1166
1167 x_return_status := FND_API.g_ret_sts_error;
1168 RETURN;
1169 END IF;
1170 END IF;
1171
1172 END Check_Fk_Items;
1173
1174 /*****************************************************************************/
1175 -- Procedure: check_lookup_items
1176 --
1177 -- History
1178 -- 11/22/1999 julou created
1179 -- 01/14/2000 ptendulk Modified
1180 -------------------------------------------------------------------------------
1181 PROCEDURE Check_Lookup_Items
1182 (
1183 p_act_offer_rec IN act_offer_rec_type,
1184 x_return_status OUT NOCOPY VARCHAR2
1185 )
1186 IS
1187
1188 BEGIN
1189
1190 x_return_status := FND_API.g_ret_sts_success;
1191
1192 -- Following Code has been Changed by ptendulk
1193 -- as Only Campaign can create Offers
1194
1195 -- check arc_act_offer_used_by
1196 -- IF p_act_offer_rec.arc_act_offer_used_by <> FND_API.g_miss_char
1197 -- AND p_act_offer_rec.arc_act_offer_used_by NOT IN ('ECAM', 'MCAM', 'RCAM', 'CAMP')
1198 -- THEN
1199 -- IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1200 -- FND_MESSAGE.set_name('OZF', 'OZF_ACT_OFFER_NO_USED_BY');
1201 -- FND_MSG_PUB.add;
1202 -- END IF;
1203 --OZF_UTILITY_PVT.debug_message(g_pkg_name||': check used_by');
1204 --OZF_UTILITY_PVT.debug_message('used by: ' || p_act_offer_rec.arc_act_offer_used_by);
1205 IF p_act_offer_rec.arc_act_offer_used_by <> FND_API.g_miss_char
1206 AND (p_act_offer_rec.arc_act_offer_used_by <> 'CAMP'
1207 AND p_act_offer_rec.arc_act_offer_used_by <> 'CSCH')
1208 THEN
1209 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1210 FND_MESSAGE.set_name('OZF', 'OZF_ACT_OFFER_NO_USED_BY');
1211 FND_MSG_PUB.add;
1212 END IF;
1213
1214 x_return_status := FND_API.g_ret_sts_error;
1215 RETURN;
1216 END IF;
1217 -- commented by julou 05/03/2001. offer_type, status_code, lumpsum_payment_type are gone
1218 /*
1219 -- check offer_type
1220 IF p_act_offer_rec.offer_type <> FND_API.g_miss_char
1221 AND p_act_offer_rec.offer_type IS NOT NULL
1222 THEN
1223 IF OZF_Utility_PVT.check_lookup_exists(
1224 p_lookup_type => 'OZF_OFFER_TYPE',
1225 p_lookup_code => p_act_offer_rec.offer_type
1226 ) = FND_API.g_false
1227 THEN
1228 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1229 THEN
1230 FND_MESSAGE.set_name('OZF', 'OZF_ACT_OFFER_BAD_OFFER_TYPE');
1231 FND_MSG_PUB.add;
1232 END IF;
1233 x_return_status := FND_API.g_ret_sts_error;
1234 RETURN;
1235 END IF;
1236 END IF;
1237
1238 -- check status_code
1239 IF p_act_offer_rec.status_code <> FND_API.g_miss_char
1240 AND p_act_offer_rec.status_code IS NOT NULL
1241 THEN
1242 IF OZF_Utility_PVT.check_lookup_exists(
1243 p_lookup_type => 'OZF_OFFER_STATUS',
1244 p_lookup_code => p_act_offer_rec.status_code
1245 ) = FND_API.g_false
1246 THEN
1247 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1248 THEN
1249 FND_MESSAGE.set_name('OZF', 'OZF_ACT_OFFER_BAD_STATUS');
1250 FND_MSG_PUB.add;
1251 END IF;
1252 x_return_status := FND_API.g_ret_sts_error;
1253 RETURN;
1254 END IF;
1255 END IF;
1256
1257 -- check lumpsum_payment_type
1258 IF p_act_offer_rec.lumpsum_payment_type <> FND_API.g_miss_char
1259 AND p_act_offer_rec.lumpsum_payment_type IS NOT NULL
1260 THEN
1261 IF OZF_Utility_PVT.check_lookup_exists(
1262 p_lookup_type => 'OZF_OFFER_LUMPSUM_PAYMENT',
1263 p_lookup_code => p_act_offer_rec.lumpsum_payment_type
1264 ) = FND_API.g_false
1265 THEN
1266 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1267 THEN
1268 FND_MESSAGE.set_name('OZF', 'OZF_ACT_OFFER_BAD_PAYMENT');
1269 FND_MSG_PUB.add;
1270 END IF;
1271 x_return_status := FND_API.g_ret_sts_error;
1272 RETURN;
1273 END IF;
1274 END IF;
1275 */
1276 -- end of comment
1277
1278 END Check_Lookup_Items;
1279
1280
1281 /*****************************************************************************/
1282 -- Procedure: check_items
1283 --
1284 -- History
1285 -- 11/22/1999 julou created
1286 -------------------------------------------------------------------------------
1287 PROCEDURE Check_Items
1288 (
1289 p_validation_mode IN VARCHAR2,
1290 x_return_status OUT NOCOPY VARCHAR2,
1291 p_act_offer_rec IN act_offer_rec_type
1292 )
1293 IS
1294
1295 l_api_version CONSTANT NUMBER := 1.0;
1296 l_api_name CONSTANT VARCHAR2(30) := 'check_items';
1297 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
1298
1299 BEGIN
1300
1301 x_return_status := FND_API.g_ret_sts_success;
1302
1303 -- check required items
1304 check_req_items
1305 (
1306 p_validation_mode => p_validation_mode,
1307 p_act_offer_rec => p_act_offer_rec,
1308 x_return_status => x_return_status
1309 );
1310
1311 IF x_return_status <> FND_API.g_ret_sts_success THEN
1312 RETURN;
1313 END IF;
1314
1315 -- check unique key items
1316 check_uk_items
1317 (
1318 p_validation_mode => p_validation_mode,
1319 p_act_offer_rec => p_act_offer_rec,
1320 x_return_status => x_return_status
1321 );
1322
1323 IF x_return_status <> FND_API.g_ret_sts_success THEN
1324 RETURN;
1325 END IF;
1326
1327 -- check foreign key items
1328 check_fk_items
1329 (
1330 p_act_offer_rec => p_act_offer_rec,
1331 x_return_status => x_return_status
1332 );
1333
1334 IF x_return_status <> FND_API.g_ret_sts_success THEN
1335 RETURN;
1336 END IF;
1337
1338 -- check lookup items
1339 check_lookup_items
1340 (
1341 p_act_offer_rec => p_act_offer_rec,
1342 x_return_status => x_return_status
1343 );
1344
1345 IF x_return_status <> FND_API.g_ret_sts_success THEN
1346 RETURN;
1347 END IF;
1348
1349 END Check_Items;
1350
1351
1352 /*****************************************************************************/
1353 -- Procedure: check_record
1354 --
1355 -- History
1356 -- 01/15/1999 julou created
1357 -- 01/17/1999 julou check if the campaign is active
1358 -- 01/26/2001 rssharma fixed bug on active campaigns
1359
1360 -------------------------------------------------------------------------------
1361 PROCEDURE Check_Record
1362 (
1363 p_act_offer_rec IN act_offer_rec_type,
1364 p_complete_rec IN act_offer_rec_type,
1365 x_return_status OUT NOCOPY VARCHAR2
1366 )
1367 IS
1368
1369 CURSOR c_active(used_by_id IN NUMBER) IS
1370 SELECT count(*) FROM AMS_CAMPAIGNS_VL
1371 WHERE campaign_id = used_by_id
1372 AND (actual_exec_end_date IS NULL
1373 -- changed by rssharma for bug fixing on 01/26/2001
1374 OR actual_exec_end_date >= trunc(SYSDATE) );
1375
1376
1377 CURSOR c_primary_offer_count1(used_by_id IN NUMBER, used_by IN VARCHAR2) IS
1378 SELECT COUNT(*) FROM OZF_ACT_OFFERS
1379 WHERE act_offer_used_by_id = used_by_id
1380 AND arc_act_offer_used_by = used_by
1381 AND primary_offer_flag = 'Y';
1382
1383 CURSOR c_primary_offer_count2(act_offer_id IN NUMBER, used_by_id IN NUMBER, used_by IN VARCHAR2) IS
1384 SELECT COUNT(*) FROM OZF_ACT_OFFERS
1385 WHERE act_offer_used_by_id = used_by_id
1386 AND arc_act_offer_used_by = used_by
1387 AND activity_offer_id <> act_offer_id
1388 AND primary_offer_flag = 'Y';
1389
1390 l_primary_offer_count NUMBER;
1391 l_active NUMBER;
1392
1393 BEGIN
1394
1395 x_return_status := FND_API.g_ret_sts_success;
1396
1397 -- commented by julou 05/03/2001. These dates are gone
1398 /*
1399 -- check offer dates
1400 IF p_complete_rec.start_date > p_complete_rec.end_date THEN
1401 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1402 THEN
1403 FND_MESSAGE.set_name('OZF', 'OZF_OFFR_START_AFTER_END');
1404 FND_MSG_PUB.add;
1405 END IF;
1406 x_return_status := FND_API.g_ret_sts_error;
1407 END IF;
1408
1409 IF p_complete_rec.order_date_from > p_complete_rec.order_date_to THEN
1410 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1411 THEN
1412 FND_MESSAGE.set_name('OZF', 'OZF_OFFR_BAD_ORDER_DATES');
1413 FND_MSG_PUB.add;
1414 END IF;
1415 x_return_status := FND_API.g_ret_sts_error;
1416 END IF;
1417
1418 IF p_complete_rec.ship_date_from > p_complete_rec.ship_date_to THEN
1419 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1420 THEN
1421 FND_MESSAGE.set_name('OZF', 'OZF_OFFR_BAD_SHIP_DATES');
1422 FND_MSG_PUB.add;
1423 END IF;
1424 x_return_status := FND_API.g_ret_sts_error;
1425 END IF;
1426
1427 IF p_complete_rec.perf_date_from > p_complete_rec.perf_date_to THEN
1428 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1429 THEN
1430 FND_MESSAGE.set_name('OZF', 'OZF_OFFR_BAD_PERF_DATES');
1431 FND_MSG_PUB.add;
1432 END IF;
1433 x_return_status := FND_API.g_ret_sts_error;
1434 END IF;
1435 */
1436 -- check if the campaign is active
1437 IF p_complete_rec.arc_act_offer_used_by = 'CAMP' THEN
1438 OPEN c_active(p_complete_rec.act_offer_used_by_id);
1439 FETCH c_active INTO l_active;
1440 CLOSE c_active;
1441
1442 IF l_active = 0 THEN
1443 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1444 FND_MESSAGE.set_name('OZF', 'OZF_ACT_OFFER_CAMP_EXPIRED');
1445 FND_MSG_PUB.add;
1446 END IF;
1447 x_return_status := FND_API.g_ret_sts_error;
1448 RETURN;
1449 END IF;
1450 END IF;
1451
1452 -- check if the primary offer is unique
1453 IF p_complete_rec.primary_offer_flag = 'Y' THEN
1454 IF p_complete_rec.activity_offer_id IS NULL THEN
1455 OPEN c_primary_offer_count1(p_complete_rec.act_offer_used_by_id, p_complete_rec.arc_act_offer_used_by);
1456 FETCH c_primary_offer_count1 INTO l_primary_offer_count;
1457 IF l_primary_offer_count <> 0 THEN
1458 CLOSE c_primary_offer_count1;
1459 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1460 FND_MESSAGE.set_name('OZF', 'OZF_ACT_OFFER_PRI_OFFER_EXISTS');
1461 FND_MSG_PUB.add;
1462 END IF;
1463
1464 x_return_status := FND_API.g_ret_sts_error;
1465 RETURN;
1466 END IF;
1467 CLOSE c_primary_offer_count1;
1468 ELSE
1469 OPEN c_primary_offer_count2(p_complete_rec.activity_offer_id, p_complete_rec.act_offer_used_by_id, p_complete_rec.arc_act_offer_used_by);
1470 FETCH c_primary_offer_count2 INTO l_primary_offer_count;
1471 IF l_primary_offer_count <> 0 THEN
1472 CLOSE c_primary_offer_count2;
1473 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1474 FND_MESSAGE.set_name('OZF', 'OZF_ACT_OFFER_PRI_OFFER_EXISTS');
1475 FND_MSG_PUB.add;
1476 END IF;
1477
1478 x_return_status := FND_API.g_ret_sts_error;
1479 RETURN;
1480 END IF;
1481 CLOSE c_primary_offer_count2;
1482 END IF;
1483 END IF;
1484
1485 END Check_Record;
1486
1487 /*****************************************************************************/
1488 -- Procedure: complete_rec
1489 --
1490 -- History
1491 -- 12/19/1999 julou Created.
1492 -- 04/11/2000 holiu Added new columns.
1493 -- 06/08/2000 ptendulk Added condition for column primary offer flag
1494 -------------------------------------------------------------------------------
1495 PROCEDURE Complete_Rec
1496 (
1497 p_act_offer_rec IN act_offer_rec_type,
1498 x_complete_rec OUT NOCOPY act_offer_rec_type
1499 )
1500 IS
1501
1502 CURSOR c_act_offer IS
1503 SELECT *
1504 FROM ozf_act_offers
1505 WHERE activity_offer_id = p_act_offer_rec.activity_offer_id;
1506
1507 l_act_offer_rec c_act_offer%ROWTYPE;
1508
1509 BEGIN
1510
1511 x_complete_rec := p_act_offer_rec;
1512
1513 OPEN c_act_offer;
1514 FETCH c_act_offer INTO l_act_offer_rec;
1515 IF (c_act_offer%NOTFOUND) THEN
1516 CLOSE c_act_offer;
1517 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1518 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
1519 FND_MSG_PUB.add;
1520 END IF;
1521 RAISE FND_API.g_exc_error;
1522 END IF;
1523 CLOSE c_act_offer;
1524
1525 IF p_act_offer_rec.act_offer_used_by_id = FND_API.g_miss_num THEN
1526 x_complete_rec.act_offer_used_by_id := l_act_offer_rec.act_offer_used_by_id;
1527 END IF;
1528
1529 IF p_act_offer_rec.arc_act_offer_used_by = FND_API.g_miss_char THEN
1530 x_complete_rec.arc_act_offer_used_by := l_act_offer_rec.arc_act_offer_used_by;
1531 END IF;
1532 -- commented by julou 05/03/2001. offer_type and offer_code are gone.
1533 /*
1534 IF p_act_offer_rec.offer_type = FND_API.g_miss_char THEN
1535 x_complete_rec.offer_type := l_act_offer_rec.offer_type;
1536 END IF;
1537
1538 IF p_act_offer_rec.offer_code = FND_API.g_miss_char THEN
1539 x_complete_rec.offer_code := l_act_offer_rec.offer_code;
1540 END IF;
1541 */
1542 -- ==================================================================
1543 -- Following line of code is added by ptendulk on 08Jun2000
1544 -- Check for the primary offer Flag
1545 -- =================================================================
1546 IF p_act_offer_rec.primary_offer_flag = FND_API.g_miss_char THEN
1547 x_complete_rec.primary_offer_flag := l_act_offer_rec.primary_offer_flag ;
1548 END IF;
1549
1550 IF p_act_offer_rec.active_period_set = FND_API.g_miss_char THEN
1551 x_complete_rec.active_period_set := l_act_offer_rec.active_period_set;
1552 END IF;
1553
1554 IF p_act_offer_rec.active_period = FND_API.g_miss_char THEN
1555 x_complete_rec.active_period := l_act_offer_rec.active_period;
1556 END IF;
1557 -- commented by julou 05/03/2001. These dates are gone.
1558 /*
1559 IF p_act_offer_rec.start_date = FND_API.g_miss_date THEN
1560 x_complete_rec.start_date := l_act_offer_rec.start_date;
1561 END IF;
1562
1563 IF p_act_offer_rec.end_date = FND_API.g_miss_date THEN
1564 x_complete_rec.end_date := l_act_offer_rec.end_date;
1565 END IF;
1566
1567 IF p_act_offer_rec.order_date_from = FND_API.g_miss_date THEN
1568 x_complete_rec.order_date_from := l_act_offer_rec.order_date_from;
1569 END IF;
1570
1571 IF p_act_offer_rec.order_date_to = FND_API.g_miss_date THEN
1572 x_complete_rec.order_date_to := l_act_offer_rec.order_date_to;
1573 END IF;
1574
1575 IF p_act_offer_rec.ship_date_from = FND_API.g_miss_date THEN
1576 x_complete_rec.ship_date_from := l_act_offer_rec.ship_date_from;
1577 END IF;
1578
1579 IF p_act_offer_rec.ship_date_to = FND_API.g_miss_date THEN
1580 x_complete_rec.ship_date_to := l_act_offer_rec.ship_date_to;
1581 END IF;
1582
1583 IF p_act_offer_rec.perf_date_from = FND_API.g_miss_date THEN
1584 x_complete_rec.perf_date_from := l_act_offer_rec.perf_date_from;
1585 END IF;
1586
1587 IF p_act_offer_rec.perf_date_to = FND_API.g_miss_date THEN
1588 x_complete_rec.perf_date_to := l_act_offer_rec.perf_date_to;
1589 END IF;
1590
1591 IF p_act_offer_rec.status_code = FND_API.g_miss_char THEN
1592 x_complete_rec.status_code := l_act_offer_rec.status_code;
1593 END IF;
1594
1595 IF p_act_offer_rec.status_date = FND_API.g_miss_date
1596 OR p_act_offer_rec.status_date IS NULL
1597 THEN
1598 IF x_complete_rec.status_code = l_act_offer_rec.status_code THEN
1599 -- no status change, set it to be the original value
1600 x_complete_rec.status_date := l_act_offer_rec.status_date;
1601 ELSE
1602 -- status changed, set it to be SYSDATE
1603 x_complete_rec.status_date := SYSDATE;
1604 END IF;
1605 END IF;
1606
1607 IF p_act_offer_rec.offer_amount = FND_API.g_miss_num THEN
1608 x_complete_rec.offer_amount := l_act_offer_rec.offer_amount;
1609 END IF;
1610
1611 IF p_act_offer_rec.lumpsum_payment_type = FND_API.g_miss_char THEN
1612 x_complete_rec.lumpsum_payment_type := l_act_offer_rec.lumpsum_payment_type;
1613 END IF;
1614 */
1615 -- end of comment
1616 IF p_act_offer_rec.qp_list_header_id = FND_API.g_miss_num THEN
1617 x_complete_rec.qp_list_header_id := l_act_offer_rec.qp_list_header_id;
1618 END IF;
1619
1620 END Complete_Rec;
1621
1622
1623 /****************************************************************************/
1624 -- Procedure: init_rec
1625 --
1626 -- History
1627 -- 12/19/1999 julou Created.
1628 -- 04/11/2000 holiu Added new columns.
1629 ------------------------------------------------------------------------------
1630 PROCEDURE Init_Rec
1631 (
1632 x_act_offer_rec OUT NOCOPY act_offer_rec_type
1633 )
1634 IS
1635
1636 BEGIN
1637
1638 x_act_offer_rec.activity_offer_id := FND_API.g_miss_num;
1639 x_act_offer_rec.last_update_date := FND_API.g_miss_date;
1640 x_act_offer_rec.last_updated_by := FND_API.g_miss_num;
1641 x_act_offer_rec.creation_date := FND_API.g_miss_date;
1642 x_act_offer_rec.created_by := FND_API.g_miss_num;
1643 x_act_offer_rec.last_update_login := FND_API.g_miss_num;
1644 x_act_offer_rec.object_version_number := FND_API.g_miss_num;
1645 x_act_offer_rec.act_offer_used_by_id := FND_API.g_miss_num;
1646 x_act_offer_rec.arc_act_offer_used_by := FND_API.g_miss_char;
1647 x_act_offer_rec.primary_offer_flag := FND_API.g_miss_char;
1648 x_act_offer_rec.active_period_set := FND_API.g_miss_char;
1649 x_act_offer_rec.active_period := FND_API.g_miss_char;
1650 -- commented by julou 05/03/2001. These columns are gone.
1651 /*
1652 x_act_offer_rec.offer_type := FND_API.g_miss_char;
1653 x_act_offer_rec.offer_code := FND_API.g_miss_char;
1654 x_act_offer_rec.start_date := FND_API.g_miss_date;
1655 x_act_offer_rec.end_date := FND_API.g_miss_date;
1656 x_act_offer_rec.order_date_from := FND_API.g_miss_date;
1657 x_act_offer_rec.order_date_to := FND_API.g_miss_date;
1658 x_act_offer_rec.ship_date_from := FND_API.g_miss_date;
1659 x_act_offer_rec.ship_date_to := FND_API.g_miss_date;
1660 x_act_offer_rec.perf_date_from := FND_API.g_miss_date;
1661 x_act_offer_rec.perf_date_to := FND_API.g_miss_date;
1662 x_act_offer_rec.status_code := FND_API.g_miss_char;
1663 x_act_offer_rec.status_date := FND_API.g_miss_date;
1664 x_act_offer_rec.offer_amount := FND_API.g_miss_num;
1665 x_act_offer_rec.lumpsum_payment_type := FND_API.g_miss_char;
1666 */
1667 -- end of comment
1668 x_act_offer_rec.qp_list_header_id := FND_API.g_miss_num;
1669
1670 END Init_Rec;
1671
1672 --==================================================================================
1673 -- Following lines of code is commented by ptendulk on may31-2000 the Wrapper part
1674 -- is added in the Offer api which calls the modifier pub by skarumar
1675 --
1676 --==================================================================================
1677
1678 /*
1679 -- Start of Comments
1680 --
1681 -- NAME
1682 -- Create_Offer
1683 --
1684 -- PURPOSE
1685 -- This procedure is a Wrapper which will be used to create the offers in
1686 -- Oracle Marketing. It will internally call OzfOfferPvt.processListHeader
1687 -- and Then Create_Act_Offer . It will commit the changes if both are
1688 -- successful else it will rollback both.
1689 --
1690 -- NOTES
1691 -- OzfOfferPvt.Process_List_Header will write the messages in OE PUB
1692 -- So will have to read error messages from there , if any.
1693 -- the out parameter x_message_type will return value FND / OE
1694 -- It will return 'FND' if the Messages are stored in FND_PUB
1695 -- It will return 'OE' if the Messages are stored in OE_PUB
1696 --
1697 -- HISTORY
1698 -- 05/12/2000 ptendulk created
1699 -- End of Comments
1700 PROCEDURE Create_Offer
1701 (
1702 p_api_version IN NUMBER,
1703 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1704 p_commit IN VARCHAR2 := FND_API.g_false,
1705 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1706
1707 x_return_status OUT NOCOPY VARCHAR2,
1708 x_msg_count OUT NOCOPY NUMBER,
1709 x_msg_data OUT NOCOPY VARCHAR2,
1710
1711 p_act_offer_rec IN act_offer_rec_type,
1712 p_list_header_id IN NUMBER := FND_API.g_miss_num,
1713 p_offer_name IN VARCHAR2 := FND_API.g_miss_char,
1714 p_currency_code IN VARCHAR2 := FND_API.g_miss_char,
1715 p_start_date IN DATE := FND_API.g_miss_date,
1716 p_end_date IN DATE := FND_API.g_miss_date,
1717 p_active_flag IN VARCHAR2 := FND_API.g_miss_char,
1718 p_automatic_flag IN VARCHAR2 := 'Y',
1719 p_invoice_flag IN VARCHAR2 := 'Y',
1720
1721 x_list_header_id OUT NOCOPY NUMBER,
1722 x_act_offer_id OUT NOCOPY NUMBER,
1723 x_message_type OUT NOCOPY VARCHAR2 -- OE / FND
1724 )
1725 IS
1726 l_return_status VARCHAR2(1) ;
1727 l_api_name CONSTANT VARCHAR2(30) := 'Create_Offer';
1728 l_api_version CONSTANT NUMBER := 1.0;
1729 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1730 l_line_id NUMBER ;
1731 l_list_header_id NUMBER ;
1732 l_act_offer_rec act_offer_rec_type := p_act_offer_rec ;
1733 BEGIN
1734
1735 -- initialize
1736 SAVEPOINT Create_Offer;
1737 x_message_type := 'FND' ;
1738
1739 IF FND_API.to_boolean(p_init_msg_list) THEN
1740 FND_MSG_PUB.initialize;
1741 END IF;
1742
1743 IF NOT FND_API.compatible_api_call
1744 (
1745 l_api_version,
1746 p_api_version,
1747 l_api_name,
1748 g_pkg_name
1749 )
1750 THEN
1751 RAISE FND_API.g_exc_unexpected_error;
1752 END IF;
1753
1754 x_return_status := FND_API.g_ret_sts_success;
1755
1756
1757 --
1758 -- Create the List Header for the offer
1759 --
1760 OZF_Offer_PVT.process_list_header(
1761 p_init_msg_list => p_init_msg_list ,
1762 p_commit => p_commit ,
1763
1764 x_return_status => l_return_status ,
1765 x_msg_count => x_msg_count ,
1766 x_msg_data => x_msg_data ,
1767
1768 p_list_header_id => p_list_header_id,
1769 p_offer_name => p_offer_name,
1770 p_currency_code => p_currency_code,
1771 p_start_date => p_start_date,
1772 p_end_date => p_end_date,
1773 p_active_flag => p_active_flag,
1774 p_automatic_flag => p_automatic_flag,
1775 p_mode => 'CREATE',
1776
1777 x_list_header_id => l_list_header_id
1778 );
1779
1780 IF l_return_status = FND_API.g_ret_sts_error THEN
1781 x_message_type := 'OE' ;
1782 RAISE FND_API.g_exc_error;
1783 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1784 x_message_type := 'OE' ;
1785 RAISE FND_API.g_exc_unexpected_error;
1786 END IF;
1787
1788 OZF_Utility_Pvt.Debug_Message('Created List Header for the offers');
1789
1790 --===================================================================
1791 -- Following line of code is commented by ptendulk on 16th May
1792 -- Create the Offer line in detail page
1793 --===================================================================
1794 --
1795 -- Create the List line If the offer type is Tiered Discount
1796 --
1797 -- IF p_act_offer_rec.offer_type = 'TIERED' THEN
1798 -- OZF_Offer_PVT.Process_List_Line(
1799 -- p_init_msg_list => p_init_msg_list,
1800 -- p_commit => p_commit,
1801 --
1802 -- x_return_status => x_return_status,
1803 -- x_msg_count => x_msg_count,
1804 -- x_msg_data => x_msg_data,
1805 --
1806 -- p_m_list_header_id => l_list_header_id,
1807 -- p_m_automatic_flag => 'Y',
1808 -- p_m_invoice_flag => p_invoice_flag,
1809 -- p_m_list_line_type_code => 'PBH',
1810 -- p_m_modifier_level_code => 'LINE',
1811 -- p_m_mode => 'CREATE',
1812 --
1813 -- p_p_mode => 'NONE',
1814 --
1815 -- x_list_line_id => l_line_id
1816 -- );
1817 --
1818 -- IF x_return_status = FND_API.g_ret_sts_error THEN
1819 -- x_message_type := 'OE' ;
1820 -- RAISE FND_API.g_exc_error;
1821 -- ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1822 -- x_message_type := 'OE' ;
1823 -- RAISE FND_API.g_exc_unexpected_error;
1824 -- END IF;
1825 -- END IF;
1826 --
1827 -- Create the record in Activity Offers
1828 --
1829 OZF_Utility_Pvt.Debug_Message('Create Activity Offer');
1830 l_act_offer_rec.qp_list_header_id := l_list_header_id ;
1831
1832 Create_Act_Offer
1833 (
1834 p_api_version => p_api_version,
1835 p_init_msg_list => p_init_msg_list,
1836 p_commit => p_commit,
1837 p_validation_level => p_validation_level,
1838
1839 x_return_status => l_return_status ,
1840 x_msg_count => x_msg_count,
1841 x_msg_data => x_msg_data,
1842
1843 p_act_offer_rec => l_act_offer_rec,
1844 x_act_offer_id => x_act_offer_id
1845 ) ;
1846
1847 IF l_return_status = FND_API.g_ret_sts_error THEN
1848 x_message_type := 'FND' ;
1849 RAISE FND_API.g_exc_error;
1850 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1851 x_message_type := 'FND' ;
1852 RAISE FND_API.g_exc_unexpected_error;
1853 END IF;
1854
1855 --
1856 -- END of API body.
1857 --
1858 l_list_header_id := x_list_header_id ;
1859
1860 -- Standard check of p_commit.
1861 IF FND_API.To_Boolean ( p_commit )
1862 THEN
1863 COMMIT WORK;
1864 END IF;
1865
1866 --
1867 -- Standard call to get message count AND IF count is 1, get message info.
1868 --
1869 FND_MSG_PUB.Count_AND_Get
1870 ( p_count => x_msg_count,
1871 p_data => x_msg_data,
1872 p_encoded => FND_API.G_FALSE
1873 );
1874
1875 OE_MSG_PUB.Count_AND_Get
1876 ( p_count => x_msg_count,
1877 p_data => x_msg_data,
1878 p_encoded => FND_API.G_FALSE
1879 );
1880
1881 OZF_Utility_PVT.debug_message(l_full_name ||': end');
1882
1883 EXCEPTION
1884
1885 WHEN FND_API.G_EXC_ERROR THEN
1886
1887 ROLLBACK TO Create_Offer;
1888 x_return_status := FND_API.G_RET_STS_ERROR ;
1889
1890 IF x_message_type = 'FND' THEN
1891 FND_MSG_PUB.Count_AND_Get
1892 ( p_count => x_msg_count,
1893 p_data => x_msg_data,
1894 p_encoded => FND_API.G_FALSE
1895 );
1896 ELSIF x_message_type = 'OE' THEN
1897 OE_MSG_PUB.Count_AND_Get
1898 ( p_count => x_msg_count,
1899 p_data => x_msg_data,
1900 p_encoded => FND_API.G_FALSE
1901 );
1902 END IF ;
1903
1904 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1905
1906 ROLLBACK TO Create_Offer;
1907 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1908
1909 IF x_message_type = 'FND' THEN
1910 FND_MSG_PUB.Count_AND_Get
1911 ( p_count => x_msg_count,
1912 p_data => x_msg_data,
1913 p_encoded => FND_API.G_FALSE
1914 );
1915 ELSIF x_message_type = 'OE' THEN
1916 OE_MSG_PUB.Count_AND_Get
1917 ( p_count => x_msg_count,
1918 p_data => x_msg_data,
1919 p_encoded => FND_API.G_FALSE
1920 );
1921 END IF ;
1922
1923
1924 WHEN OTHERS THEN
1925
1926 ROLLBACK TO Create_Offer;
1927 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1928
1929 IF x_message_type = 'FND' THEN
1930 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1931 THEN
1932 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1933 END IF;
1934
1935 FND_MSG_PUB.Count_AND_Get
1936 ( p_count => x_msg_count,
1937 p_data => x_msg_data,
1938 p_encoded => FND_API.G_FALSE
1939 );
1940 ELSIF x_message_type = 'OE' THEN
1941 IF OE_MSG_PUB.Check_Msg_Level ( OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1942 THEN
1943 OE_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1944 END IF;
1945 OE_MSG_PUB.Count_AND_Get
1946 ( p_count => x_msg_count,
1947 p_data => x_msg_data,
1948 p_encoded => FND_API.G_FALSE
1949 );
1950 END IF ;
1951
1952 END Create_Offer ;
1953
1954
1955 -- Start of Comments
1956 --
1957 -- NAME
1958 -- Update_Offer
1959 --
1960 -- PURPOSE
1961 -- This procedure is a Wrapper which will be used to Update the offers in
1962 -- Oracle Marketing. It will internally call OzfOfferPvt.processListHeader
1963 -- and Then Update_Act_Offer . It will commit the changes if both are
1964 -- successful else it will rollback both.
1965 --
1966 -- NOTES
1967 -- OzfOfferPvt.Process_List_Header will write the messages in OE PUB
1968 -- So will have to read error messages from there , if any.
1969 -- the out parameter x_message_type will return value FND / OE
1970 -- It will return 'FND' if the Messages are stored in FND_PUB
1971 -- It will return 'OE' if the Messages are stored in OE_PUB
1972 --
1973 -- HISTORY
1974 -- 05/12/2000 ptendulk created
1975 -- End of Comments
1976 PROCEDURE Update_Offer
1977 (
1978 p_api_version IN NUMBER,
1979 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1980 p_commit IN VARCHAR2 := FND_API.g_false,
1981 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1982
1983 x_return_status OUT NOCOPY VARCHAR2,
1984 x_msg_count OUT NOCOPY NUMBER,
1985 x_msg_data OUT NOCOPY VARCHAR2,
1986
1987 p_act_offer_rec IN act_offer_rec_type,
1988 p_list_header_id IN NUMBER := FND_API.g_miss_num,
1989 p_offer_name IN VARCHAR2 := FND_API.g_miss_char,
1990 p_currency_code IN VARCHAR2 := FND_API.g_miss_char,
1991 p_start_date IN DATE := FND_API.g_miss_date,
1992 p_end_date IN DATE := FND_API.g_miss_date,
1993 p_active_flag IN VARCHAR2 := FND_API.g_miss_char,
1994 p_automatic_flag IN VARCHAR2 := FND_API.g_miss_char,
1995
1996 x_message_type OUT NOCOPY VARCHAR2 -- OE / FND
1997 )
1998 IS
1999
2000 l_api_name CONSTANT VARCHAR2(30) := 'Update_Offer';
2001 l_api_version CONSTANT NUMBER := 1.0;
2002 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2003 l_return_status VARCHAR2(1) ;
2004 l_list_header_id NUMBER ;
2005 BEGIN
2006
2007 -- initialize
2008 SAVEPOINT Update_Offer;
2009
2010 x_message_type := 'FND' ;
2011
2012 IF FND_API.to_boolean(p_init_msg_list) THEN
2013 FND_MSG_PUB.initialize;
2014 END IF;
2015
2016 IF NOT FND_API.compatible_api_call
2017 (
2018 l_api_version,
2019 p_api_version,
2020 l_api_name,
2021 g_pkg_name
2022 )
2023 THEN
2024 RAISE FND_API.g_exc_unexpected_error;
2025 END IF;
2026
2027 x_return_status := FND_API.g_ret_sts_success;
2028
2029
2030 --
2031 -- Create the List Header for the offer
2032 --
2033 OZF_Offer_PVT.process_list_header(
2034 p_init_msg_list => p_init_msg_list ,
2035 p_commit => p_commit ,
2036
2037 x_return_status => l_return_status ,
2038 x_msg_count => x_msg_count ,
2039 x_msg_data => x_msg_data ,
2040
2041 p_list_header_id => p_list_header_id,
2042 p_offer_name => p_offer_name,
2043 p_currency_code => p_currency_code,
2044 p_start_date => p_start_date,
2045 p_end_date => p_end_date,
2046 p_active_flag => p_active_flag,
2047 p_automatic_flag => p_automatic_flag,
2048 p_mode => 'UPDATE',
2049
2050 x_list_header_id => l_list_header_id
2051 );
2052
2053 IF l_return_status = FND_API.g_ret_sts_error THEN
2054 x_message_type := 'OE' ;
2055 RAISE FND_API.g_exc_error;
2056 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2057 x_message_type := 'OE' ;
2058 RAISE FND_API.g_exc_unexpected_error;
2059 END IF;
2060
2061 --
2062 -- Create the record in Activity Offers
2063 --
2064 OZF_Utility_Pvt.Debug_Message('Update Activity Offer');
2065 Update_Act_Offer
2066 (
2067 p_api_version => p_api_version,
2068 p_init_msg_list => p_init_msg_list,
2069 p_commit => p_commit,
2070 p_validation_level => p_validation_level,
2071
2072 x_return_status => l_return_status ,
2073 x_msg_count => x_msg_count,
2074 x_msg_data => x_msg_data,
2075
2076 p_act_offer_rec => p_act_offer_rec
2077 ) ;
2078
2079 IF l_return_status = FND_API.g_ret_sts_error THEN
2080 x_message_type := 'FND' ;
2081 RAISE FND_API.g_exc_error;
2082 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2083 x_message_type := 'FND' ;
2084 RAISE FND_API.g_exc_unexpected_error;
2085 END IF;
2086
2087 --
2088 -- END of API body.
2089 --
2090
2091 -- Standard check of p_commit.
2092 IF FND_API.To_Boolean ( p_commit )
2093 THEN
2094 COMMIT WORK;
2095 END IF;
2096
2097 --
2098 -- Standard call to get message count AND IF count is 1, get message info.
2099 --
2100 FND_MSG_PUB.Count_AND_Get
2101 ( p_count => x_msg_count,
2102 p_data => x_msg_data,
2103 p_encoded => FND_API.G_FALSE
2104 );
2105
2106 OE_MSG_PUB.Count_AND_Get
2107 ( p_count => x_msg_count,
2108 p_data => x_msg_data,
2109 p_encoded => FND_API.G_FALSE
2110 );
2111
2112 OZF_Utility_PVT.debug_message(l_full_name ||': end');
2113
2114 EXCEPTION
2115
2116 WHEN FND_API.G_EXC_ERROR THEN
2117
2118 ROLLBACK TO Update_Offer;
2119 x_return_status := FND_API.G_RET_STS_ERROR ;
2120
2121 IF x_message_type = 'FND' THEN
2122 FND_MSG_PUB.Count_AND_Get
2123 ( p_count => x_msg_count,
2124 p_data => x_msg_data,
2125 p_encoded => FND_API.G_FALSE
2126 );
2127 ELSIF x_message_type = 'OE' THEN
2128 OE_MSG_PUB.Count_AND_Get
2129 ( p_count => x_msg_count,
2130 p_data => x_msg_data,
2131 p_encoded => FND_API.G_FALSE
2132 );
2133 END IF ;
2134
2135 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2136
2137 ROLLBACK TO Update_Offer;
2138 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2139
2140 IF x_message_type = 'FND' THEN
2141 FND_MSG_PUB.Count_AND_Get
2142 ( p_count => x_msg_count,
2143 p_data => x_msg_data,
2144 p_encoded => FND_API.G_FALSE
2145 );
2146 ELSIF x_message_type = 'OE' THEN
2147 OE_MSG_PUB.Count_AND_Get
2148 ( p_count => x_msg_count,
2149 p_data => x_msg_data,
2150 p_encoded => FND_API.G_FALSE
2151 );
2152 END IF ;
2153
2154
2155 WHEN OTHERS THEN
2156
2157 ROLLBACK TO Update_Offer;
2158 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2159
2160 IF x_message_type = 'FND' THEN
2161 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2162 THEN
2163 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2164 END IF;
2165
2166 FND_MSG_PUB.Count_AND_Get
2167 ( p_count => x_msg_count,
2168 p_data => x_msg_data,
2169 p_encoded => FND_API.G_FALSE
2170 );
2171 ELSIF x_message_type = 'OE' THEN
2172 IF OE_MSG_PUB.Check_Msg_Level ( OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2173 THEN
2174 OE_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2175 END IF;
2176 OE_MSG_PUB.Count_AND_Get
2177 ( p_count => x_msg_count,
2178 p_data => x_msg_data,
2179 p_encoded => FND_API.G_FALSE
2180 );
2181 END IF ;
2182
2183
2184 END Update_Offer;
2185
2186
2187 -- Start of Comments
2188 --
2189 -- NAME
2190 -- Delete_Offer
2191 --
2192 -- PURPOSE
2193 -- This procedure is a Wrapper which will be used to Delete the offers in
2194 -- Oracle Marketing. It will internally call OzfOfferPvt.processListHeader
2195 -- and then Delete_Act_Offer . It will commit the changes if both are
2196 -- successful else it will rollback both.
2197 --
2198 -- NOTES
2199 -- OzfOfferPvt.Process_List_Header will write the messages in OE PUB
2200 -- So will have to read error messages from there , if any.
2201 -- the out parameter x_message_type will return value FND / OE
2202 -- It will return 'FND' if the Messages are stored in FND_PUB
2203 -- It will return 'OE' if the Messages are stored in OE_PUB
2204 --
2205 -- HISTORY
2206 -- 05/12/2000 ptendulk created
2207 -- End of Comments
2208 PROCEDURE Delete_Offer
2209 (
2210 p_api_version IN NUMBER,
2211 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2212 p_commit IN VARCHAR2 := FND_API.g_false,
2213
2214 x_return_status OUT NOCOPY VARCHAR2,
2215 x_msg_count OUT NOCOPY NUMBER,
2216 x_msg_data OUT NOCOPY VARCHAR2,
2217
2218 p_act_offer_id IN NUMBER,
2219 p_object_version IN NUMBER,
2220 p_list_header_id IN NUMBER := FND_API.g_miss_num,
2221 p_offer_name IN VARCHAR2 := FND_API.g_miss_char,
2222 p_currency_code IN VARCHAR2 := FND_API.g_miss_char,
2223 p_start_date IN DATE := FND_API.g_miss_date,
2224 p_end_date IN DATE := FND_API.g_miss_date,
2225 p_active_flag IN VARCHAR2 := FND_API.g_miss_char,
2226 p_automatic_flag IN VARCHAR2 := FND_API.g_miss_char,
2227
2228 x_message_type OUT NOCOPY VARCHAR2 -- OE / FND
2229 )
2230 IS
2231
2232 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Offer';
2233 l_api_version CONSTANT NUMBER := 1.0;
2234 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2235 l_list_header_id NUMBER ;
2236 l_return_status VARCHAR2(1) ;
2237 BEGIN
2238
2239 -- initialize
2240 SAVEPOINT Delete_Offer;
2241 x_message_type := 'FND' ;
2242
2243 IF FND_API.to_boolean(p_init_msg_list) THEN
2244 FND_MSG_PUB.initialize;
2245 END IF;
2246
2247 IF NOT FND_API.compatible_api_call
2248 (
2249 l_api_version,
2250 p_api_version,
2251 l_api_name,
2252 g_pkg_name
2253 )
2254 THEN
2255 RAISE FND_API.g_exc_unexpected_error;
2256 END IF;
2257
2258 x_return_status := FND_API.g_ret_sts_success;
2259
2260
2261 --
2262 -- Delete the List Header for the offer
2263 --
2264 OZF_Offer_PVT.process_list_header(
2265 p_init_msg_list => p_init_msg_list ,
2266 p_commit => p_commit ,
2267
2268 x_return_status => l_return_status ,
2269 x_msg_count => x_msg_count ,
2270 x_msg_data => x_msg_data ,
2271
2272 p_list_header_id => p_list_header_id,
2273 p_offer_name => p_offer_name,
2274 p_currency_code => p_currency_code,
2275 p_start_date => p_start_date,
2276 p_end_date => p_end_date,
2277 p_active_flag => p_active_flag,
2278 p_automatic_flag => p_automatic_flag,
2279 p_mode => 'DELETE',
2280
2281 x_list_header_id => l_list_header_id
2282 );
2283
2284 IF l_return_status = FND_API.g_ret_sts_error THEN
2285 x_message_type := 'OE' ;
2286 RAISE FND_API.g_exc_error;
2287 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2288 x_message_type := 'OE' ;
2289 RAISE FND_API.g_exc_unexpected_error;
2290 END IF;
2291
2292 --
2293 -- Delete the record in Activity Offers
2294 --
2295 OZF_Utility_Pvt.Debug_Message('Delete Activity Offer');
2296 Delete_Act_Offer
2297 (
2298 p_api_version => p_api_version,
2299 p_init_msg_list => p_init_msg_list,
2300 p_commit => p_commit,
2301
2302 x_return_status => l_return_status ,
2303 x_msg_count => x_msg_count,
2304 x_msg_data => x_msg_data,
2305
2306 p_act_offer_id => p_act_offer_id,
2307 p_object_version => p_object_version
2308 ) ;
2309
2310 IF l_return_status = FND_API.g_ret_sts_error THEN
2311 x_message_type := 'FND' ;
2312 RAISE FND_API.g_exc_error;
2313 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2314 x_message_type := 'FND' ;
2315 RAISE FND_API.g_exc_unexpected_error;
2316 END IF;
2317
2318 --
2319 -- END of API body.
2320 --
2321
2322 -- Standard check of p_commit.
2323 IF FND_API.To_Boolean ( p_commit )
2324 THEN
2325 COMMIT WORK;
2326 END IF;
2327
2328 --
2329 -- Standard call to get message count AND IF count is 1, get message info.
2330 --
2331 FND_MSG_PUB.Count_AND_Get
2332 ( p_count => x_msg_count,
2333 p_data => x_msg_data,
2334 p_encoded => FND_API.G_FALSE
2335 );
2336
2337 OE_MSG_PUB.Count_AND_Get
2338 ( p_count => x_msg_count,
2339 p_data => x_msg_data,
2340 p_encoded => FND_API.G_FALSE
2341 );
2342
2343 OZF_Utility_PVT.debug_message(l_full_name ||': end');
2344
2345 EXCEPTION
2346
2347 WHEN FND_API.G_EXC_ERROR THEN
2348
2349 ROLLBACK TO Delete_Offer;
2350 x_return_status := FND_API.G_RET_STS_ERROR ;
2351
2352 IF x_message_type = 'FND' THEN
2353 FND_MSG_PUB.Count_AND_Get
2354 ( p_count => x_msg_count,
2355 p_data => x_msg_data,
2356 p_encoded => FND_API.G_FALSE
2357 );
2358 ELSIF x_message_type = 'OE' THEN
2359 OE_MSG_PUB.Count_AND_Get
2360 ( p_count => x_msg_count,
2361 p_data => x_msg_data,
2362 p_encoded => FND_API.G_FALSE
2363 );
2364 END IF ;
2365
2366 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2367
2368 ROLLBACK TO Delete_Offer;
2369 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2370
2371 IF x_message_type = 'FND' THEN
2372 FND_MSG_PUB.Count_AND_Get
2373 ( p_count => x_msg_count,
2374 p_data => x_msg_data,
2375 p_encoded => FND_API.G_FALSE
2376 );
2377 ELSIF x_message_type = 'OE' THEN
2378 OE_MSG_PUB.Count_AND_Get
2379 ( p_count => x_msg_count,
2380 p_data => x_msg_data,
2381 p_encoded => FND_API.G_FALSE
2382 );
2383 END IF ;
2384
2385
2386 WHEN OTHERS THEN
2387
2388 ROLLBACK TO Delete_Offer;
2389 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2390
2391 IF x_message_type = 'FND' THEN
2392 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2393 THEN
2394 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2395 END IF;
2396
2397 FND_MSG_PUB.Count_AND_Get
2398 ( p_count => x_msg_count,
2399 p_data => x_msg_data,
2400 p_encoded => FND_API.G_FALSE
2401 );
2402 ELSIF x_message_type = 'OE' THEN
2403 IF OE_MSG_PUB.Check_Msg_Level ( OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2404 THEN
2405 OE_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2406 END IF;
2407 OE_MSG_PUB.Count_AND_Get
2408 ( p_count => x_msg_count,
2409 p_data => x_msg_data,
2410 p_encoded => FND_API.G_FALSE
2411 );
2412 END IF ;
2413
2414
2415 END Delete_Offer;
2416 */
2417
2418 END OZF_Act_Offers_PVT;