[Home] [Help]
PACKAGE BODY: APPS.AMS_ACT_MARKET_SEGMENTS_PVT
Source
1 PACKAGE BODY AMS_Act_Market_Segments_PVT AS
2 /* $Header: amsvmksb.pls 120.1 2005/06/16 06:13:16 appldev $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'AMS_Act_Market_Segments_PVT';
5
6 /*****************************************************************************/
7 -- Procedure: create_market_segments
8 --
9 -- History
10 -- 10/28/1999 julou created
11 -- 14/02/2000 ptendulk Modified
12 -------------------------------------------------------------------------------
13 PROCEDURE create_market_segments
14 (
15 p_api_version IN NUMBER,
16 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
17 p_commit IN VARCHAR2 := FND_API.g_false,
18 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
19
20 x_return_status OUT NOCOPY VARCHAR2,
21 x_msg_count OUT NOCOPY NUMBER,
22 x_msg_data OUT NOCOPY VARCHAR2,
23
24 p_mks_rec IN mks_rec_type,
25 x_act_mks_id OUT NOCOPY NUMBER
26 )
27 IS
28
29 l_api_version CONSTANT NUMBER := 1.0;
30 l_api_name CONSTANT VARCHAR2(30) := 'create_market_segments';
31 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
32 l_return_status VARCHAR2(1);
33 l_mks_rec mks_rec_type := p_mks_rec;
34 l_mks_count NUMBER;
35
36 CURSOR c_mks_seq IS
37 SELECT AMS_ACT_MARKET_SEGMENTS_S.NEXTVAL
38 FROM DUAL;
39
40 CURSOR c_mks_count(mks_id IN NUMBER) IS
41 SELECT COUNT(*)
42 FROM AMS_ACT_MARKET_SEGMENTS
43 WHERE activity_market_segment_id = mks_id;
44
45 BEGIN
46 -- initialize
47 SAVEPOINT create_market_segments;
48
49 AMS_Utility_PVT.debug_message(l_full_name || ': start');
50
51 IF FND_API.to_boolean(p_init_msg_list) THEN
52 FND_MSG_PUB.initialize;
53 END IF;
54
55 IF NOT FND_API.compatible_api_call
56 (
57 l_api_version,
58 p_api_version,
59 l_api_name,
60 g_pkg_name
61 )
62 THEN
63 RAISE FND_API.g_exc_unexpected_error;
64 END IF;
65
66 x_return_status := FND_API.g_ret_sts_success;
67
68 -- validate
69 -- Following code is Modified by ptendulk as the Validation level Check
70 -- is done in Validate API
71
72 -- IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
73 AMS_Utility_PVT.debug_message(l_full_name || ': validate');
74
75 validate_market_segments
76 (
77 p_api_version => l_api_version,
78 p_init_msg_list => p_init_msg_list,
79 p_validation_level => p_validation_level,
80 x_return_status => l_return_status,
81 x_msg_count => x_msg_count,
82 x_msg_data => x_msg_data,
83 p_mks_rec => l_mks_rec
84 );
85
86
87
88 IF l_return_status = FND_API.g_ret_sts_error THEN
89 RAISE FND_API.g_exc_error;
90 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
91 RAISE FND_API.g_exc_unexpected_error;
92 END IF;
93 -- END IF;
94
95 -- insert
96 AMS_Utility_PVT.debug_message(l_full_name || ': insert');
97
98 IF l_mks_rec.activity_market_segment_id IS NULL THEN
99 LOOP
100 OPEN c_mks_seq;
101 FETCH c_mks_seq INTO l_mks_rec.activity_market_segment_id;
102 CLOSE c_mks_seq;
103
104 OPEN c_mks_count(l_mks_rec.activity_market_segment_id);
105 FETCH c_mks_count INTO l_mks_count;
106 CLOSE c_mks_count;
107
108 EXIT WHEN l_mks_count = 0;
109 END LOOP;
110 END IF;
111
112 INSERT INTO AMS_ACT_MARKET_SEGMENTS
113 (
114 activity_market_segment_id,
115 last_update_date,
116 last_updated_by,
117 creation_date,
118 created_by,
119 market_segment_id,
120 act_market_segment_used_by_id,
121 arc_act_market_segment_used_by,
122 segment_type,
123 last_update_login,
124 object_version_number,
125 attribute_category,
126 attribute1,
127 attribute2,
128 attribute3,
129 attribute4,
130 attribute5,
131 attribute6,
132 attribute7,
133 attribute8,
134 attribute9,
135 attribute10,
136 attribute11,
137 attribute12,
138 attribute13,
139 attribute14,
140 attribute15,
141 group_code,
142 exclude_flag
143 )
144 VALUES
145 (
146 l_mks_rec.activity_market_segment_id,
147 SYSDATE,
148 FND_GLOBAL.user_id,
149 SYSDATE,
150 FND_GLOBAL.user_id,
151 l_mks_rec.market_segment_id,
152 l_mks_rec.act_market_segment_used_by_id,
153 l_mks_rec.arc_act_market_segment_used_by,
154 l_mks_rec.segment_type,
155 FND_GLOBAL.conc_login_id,
156 1,
157 l_mks_rec.attribute_category,
158 l_mks_rec.attribute1,
159 l_mks_rec.attribute2,
160 l_mks_rec.attribute3,
161 l_mks_rec.attribute4,
162 l_mks_rec.attribute5,
163 l_mks_rec.attribute6,
164 l_mks_rec.attribute7,
165 l_mks_rec.attribute8,
166 l_mks_rec.attribute9,
167 l_mks_rec.attribute10,
168 l_mks_rec.attribute11,
169 l_mks_rec.attribute12,
170 l_mks_rec.attribute13,
171 l_mks_rec.attribute14,
172 l_mks_rec.attribute15,
173 l_mks_rec.group_code,
174 l_mks_rec.exclude_flag
175 );
176
177 /*-- Following code has been added by ptendulk on 14Feb2000
178 -- It will update the attribute in ams_object_attribites
179 -- as soon as segment is created for an activity
180
181 -- indicate schedule has been defined for the campaign
182 AMS_ObjectAttribute_PVT.modify_object_attribute(
183 p_api_version => l_api_version,
184 p_init_msg_list => FND_API.g_false,
185 p_commit => FND_API.g_false,
186 p_validation_level => FND_API.g_valid_level_full,
187
188 x_return_status => l_return_status,
189 x_msg_count => x_msg_count,
190 x_msg_data => x_msg_data,
191
192 p_object_type => l_mks_rec.arc_act_market_segment_used_by,
193 p_object_id => l_mks_rec.act_market_segment_used_by_id,
194 p_attr => 'CELL',
195 p_attr_defined_flag => 'Y'
196 );
197 IF l_return_status = FND_API.g_ret_sts_error THEN
198 RAISE FND_API.g_exc_error;
199 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
200 RAISE FND_API.g_exc_unexpected_error;
201 END IF; */
202
203 -- finish
204 x_act_mks_id := l_mks_rec.activity_market_segment_id;
205
206 IF FND_API.to_boolean(p_commit) THEN
207 COMMIT;
208 END IF;
209
210 FND_MSG_PUB.count_and_get
211 (
212 p_encoded => FND_API.g_false,
213 p_count => x_msg_count,
214 p_data => x_msg_data
215 );
216
217 AMS_Utility_PVT.debug_message(l_full_name || ': end');
218
219 EXCEPTION
220
221 WHEN FND_API.g_exc_error THEN
222 ROLLBACK TO create_market_segments;
223 x_return_status := FND_API.g_ret_sts_error;
224 FND_MSG_PUB.count_and_get
225 (
226 p_encoded => FND_API.g_false,
227 p_count => x_msg_count,
228 p_data => x_msg_data
229 );
230
231 WHEN FND_API.g_exc_unexpected_error THEN
232 ROLLBACK TO create_market_segments;
233 x_return_status := FND_API.g_ret_sts_unexp_error;
234 FND_MSG_PUB.count_and_get
235 (
236 p_encoded => FND_API.g_false,
237 p_count => x_msg_count,
238 p_data => x_msg_data
239 );
240
241 WHEN OTHERS THEN
242 ROLLBACK TO create_market_segments;
243 x_return_status :=FND_API.g_ret_sts_unexp_error;
244 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
245 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
246 END IF;
247 FND_MSG_PUB.count_and_get
248 (
249 p_encoded => FND_API.g_false,
250 p_count => x_msg_count,
251 p_data => x_msg_data
252 );
253
254 END create_market_segments;
255
256
257 /*****************************************************************************/
258 -- Procedure: update_market_segments
259 --
260 -- History
261 -- 10/28/1999 julou created
262 -------------------------------------------------------------------------------
263 PROCEDURE update_market_segments
264 (
265 p_api_version IN NUMBER,
266 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
267 p_commit IN VARCHAR2 := FND_API.g_false,
268 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
269 x_return_status OUT NOCOPY VARCHAR2,
270 x_msg_count OUT NOCOPY NUMBER,
271 x_msg_data OUT NOCOPY VARCHAR2,
272
273 p_mks_rec IN mks_rec_type
274 )
275 IS
276
277 l_api_version CONSTANT NUMBER := 1.0;
278 l_api_name CONSTANT VARCHAR2(30) := 'update_market_segments';
279 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
280 l_return_status VARCHAR2(1);
281 l_mks_rec mks_rec_type := p_mks_rec;
282
283 BEGIN
284
285 -- initialize
286 SAVEPOINT update_market_segments;
287
288 AMS_Utility_PVT.debug_message(l_full_name || ': start');
289
290 IF FND_API.to_boolean(p_init_msg_list) THEN
291 FND_MSG_PUB.initialize;
292 END IF;
293
294 IF NOT FND_API.compatible_api_call
295 (
296 l_api_version,
297 p_api_version,
298 l_api_name,
299 g_pkg_name
300 )
301 THEN
302 RAISE FND_API.g_exc_unexpected_error;
303 END IF;
304
305 x_return_status := FND_API.g_ret_sts_success;
306
307 -- validate
308 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
309 AMS_Utility_PVT.debug_message(l_full_name || ': validate');
310
311 check_mks_items(
312 p_mks_rec => p_mks_rec,
313 p_validation_mode => JTF_PLSQL_API.g_update,
314 x_return_status => l_return_status
315 );
316
317 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
318 RAISE FND_API.g_exc_unexpected_error;
319 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
320 RAISE FND_API.g_exc_error;
321 END IF;
322 END IF;
323
324 complete_mks_rec (
325 p_mks_rec,
326 l_mks_rec
327 );
328 /* Start Of Code added by ptendulk */
329
330
331 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
332 Validate_cross_ent_Rec(
333 p_mks_rec => p_mks_rec,
334 p_complete_rec => l_mks_rec,
335 p_validation_mode => JTF_PLSQL_API.g_update,
336 x_return_status => l_return_status
337 );
338
339 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
340 RAISE FND_API.g_exc_unexpected_error;
341 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
342 RAISE FND_API.g_exc_error;
343 END IF;
344 END IF;
345
346
347 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
348 check_mks_record(
349 p_mks_rec => p_mks_rec,
350 p_complete_rec => l_mks_rec,
351 x_return_status => l_return_status
352 );
353
354 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
355 RAISE FND_API.g_exc_unexpected_error;
356 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
357 RAISE FND_API.g_exc_error;
358 END IF;
359 END IF;
360
361 /* End Of Code added by ptendulk */
362
363
364 -- update
365 AMS_Utility_PVT.debug_message(l_full_name||': update');
366
367 UPDATE AMS_ACT_MARKET_SEGMENTS SET
368 last_update_date = SYSDATE,
369 last_updated_by = FND_GLOBAL.user_id,
370 market_segment_id = l_mks_rec.market_segment_id,
371 act_market_segment_used_by_id = l_mks_rec.act_market_segment_used_by_id,
372 arc_act_market_segment_used_by = l_mks_rec.arc_act_market_segment_used_by,
373 segment_type = l_mks_rec.segment_type,
374 last_update_login = FND_GLOBAL.conc_login_id,
375 object_version_number = l_mks_rec.object_version_number + 1,
376 attribute_category = l_mks_rec.attribute_category,
377 attribute1 = l_mks_rec.attribute1,
378 attribute2 = l_mks_rec.attribute2,
379 attribute3 = l_mks_rec.attribute3,
380 attribute4 = l_mks_rec.attribute4,
381 attribute5 = l_mks_rec.attribute5,
382 attribute6 = l_mks_rec.attribute6,
383 attribute7 = l_mks_rec.attribute7,
384 attribute8 = l_mks_rec.attribute8,
385 attribute9 = l_mks_rec.attribute9,
386 attribute10 = l_mks_rec.attribute10,
387 attribute11 = l_mks_rec.attribute11,
388 attribute12 = l_mks_rec.attribute12,
389 attribute13 = l_mks_rec.attribute13,
390 attribute14 = l_mks_rec.attribute14,
391 attribute15 = l_mks_rec.attribute15,
392 group_code = l_mks_rec.group_code,
393 exclude_flag = l_mks_rec.exclude_flag
394 WHERE activity_market_segment_id = l_mks_rec.activity_market_segment_id
395 AND object_version_number = l_mks_rec.object_version_number;
396
397 IF (SQL%NOTFOUND) THEN
398 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
399 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
400 FND_MSG_PUB.add;
401 END IF;
402 RAISE FND_API.g_exc_error;
403 END IF;
404
405 -- finish
406 IF FND_API.to_boolean(p_commit) THEN
407 COMMIT;
408 END IF;
409
410 FND_MSG_PUB.count_and_get
411 (
412 P_ENCODED => FND_API.g_false,
413 p_count => x_msg_count,
414 p_data => x_msg_data
415 );
416
417 AMS_Utility_PVT.debug_message(l_full_name || ': end');
418
419 EXCEPTION
420
421 WHEN FND_API.g_exc_error THEN
422 ROLLBACK TO update_market_segments;
423 x_return_status := FND_API.g_ret_sts_error;
424 FND_MSG_PUB.count_and_get
425 (
426 p_encoded => FND_API.g_false,
427 p_count => x_msg_count,
428 p_data => x_msg_data
429 );
430
431 WHEN FND_API.g_exc_unexpected_error THEN
432 ROLLBACK TO update_market_segments;
433 x_return_status := FND_API.g_ret_sts_unexp_error;
434 FND_MSG_PUB.count_and_get
435 (
436 p_encoded => FND_API.g_false,
437 p_count => x_msg_count,
438 p_data => x_msg_data
439 );
440
441 WHEN OTHERS THEN
442 ROLLBACK TO update_market_segments;
443 x_return_status :=FND_API.g_ret_sts_unexp_error;
444 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
445 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
446 END IF;
447 FND_MSG_PUB.count_and_get
448 (
449 p_encoded => FND_API.g_false,
450 p_count => x_msg_count,
451 p_data => x_msg_data
452 );
453
454 END update_market_segments;
455
456
457 /*****************************************************************************/
458 -- Procedure: delete_market_segments
459 --
463 PROCEDURE delete_market_segments
460 -- History
461 -- 10/28/1999 julou created
462 -------------------------------------------------------------------------------
464 (
465 p_api_version IN NUMBER,
466 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
467 p_commit IN VARCHAR2 := FND_API.g_false,
468
469 x_return_status OUT NOCOPY VARCHAR2,
470 x_msg_count OUT NOCOPY NUMBER,
471 x_msg_data OUT NOCOPY VARCHAR2,
472
473 p_act_mks_id IN NUMBER,
474 p_object_version IN NUMBER
475 )
476 IS
477
478 l_api_version CONSTANT NUMBER := 1.0;
479 l_api_name CONSTANT VARCHAR2(30) := 'delete_market_segments';
480 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
481
482 CURSOR c_arc_det IS
483 SELECT act_market_segment_used_by_id ,
484 arc_act_market_segment_used_by
485 FROM ams_act_market_segments
486 WHERE activity_market_segment_id = p_act_mks_id;
487
488 l_act_id NUMBER;
489 l_arc_act VARCHAR2(30);
490 l_dummy NUMBER;
491 CURSOR c_mks IS
492 SELECT 1
493 FROM ams_act_market_segments
494 WHERE act_market_segment_used_by_id = l_act_id
495 AND arc_act_market_segment_used_by = l_arc_act ;
496
497 BEGIN
498 -- initialize
499 SAVEPOINT delete_market_segments;
500
501 AMS_Utility_PVT.debug_message(l_full_name || ': start');
502
503 IF FND_API.to_boolean(p_init_msg_list) THEN
504 FND_MSG_PUB.initialize;
505 END IF;
506
507 IF NOT FND_API.compatible_api_call
508 (
509 l_api_version,
510 p_api_version,
511 l_api_name,
512 g_pkg_name
513 )
514 THEN
515 RAISE FND_API.g_exc_unexpected_error;
516 END IF;
517
518 x_return_status := FND_API.g_ret_sts_success;
519
520 -- delete
521 AMS_Utility_PVT.debug_message(l_full_name || ': delete');
522
523 -- Following code is added by ptendulk on 14th Feb 2000
524 -- to update Attribute after deletion
525 -- indicate if there is any other schedule for this campaign
526 OPEN c_arc_det;
527 FETCH c_arc_det INTO l_act_id,l_arc_act;
528 CLOSE c_arc_det;
529
530
531 DELETE FROM AMS_ACT_MARKET_SEGMENTS
532 WHERE activity_market_segment_id = p_act_mks_id
533 AND object_version_number = p_object_version;
534
535 IF (SQL%NOTFOUND) THEN
536 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
537 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
538 FND_MSG_PUB.add;
539 END IF;
540 RAISE FND_API.g_exc_error;
541 END IF;
542
543 /* -- Following code is added by ptendulk on 14th Feb 2000
544 -- to update Attribute after deletion
545 -- indicate if there is any other schedule for this campaign
546
547 OPEN c_mks;
548 FETCH c_mks INTO l_dummy;
549 CLOSE c_mks;
550
551
552 IF l_dummy IS NULL THEN
553 AMS_ObjectAttribute_PVT.modify_object_attribute(
554 p_api_version => l_api_version,
555 p_init_msg_list => FND_API.g_false,
556 p_commit => FND_API.g_false,
557 p_validation_level => FND_API.g_valid_level_full,
558
559 x_return_status => x_return_status,
560 x_msg_count => x_msg_count,
561 x_msg_data => x_msg_data,
562
563 p_object_type => l_arc_act,
564 p_object_id => l_act_id,
565 p_attr => 'CELL',
566 p_attr_defined_flag => 'N'
567 );
568 IF x_return_status = FND_API.g_ret_sts_error THEN
569 RAISE FND_API.g_exc_error;
570 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
571 RAISE FND_API.g_exc_unexpected_error;
572 END IF;
573 END IF; */
574
575
576 -- finish
577 IF FND_API.to_boolean(p_commit) THEN
578 COMMIT;
579 END IF;
580
581 FND_MSG_PUB.count_and_get
582 (
583 P_ENCODED => FND_API.g_false,
584 p_count => x_msg_count,
585 p_data => x_msg_data
586 );
587
588 AMS_Utility_PVT.debug_message(l_full_name || ': end');
589
590 EXCEPTION
591
592 WHEN FND_API.g_exc_error THEN
593 ROLLBACK TO delete_market_segments;
594 x_return_status := FND_API.g_ret_sts_error;
595 FND_MSG_PUB.count_and_get
596 (
597 p_encoded => FND_API.g_false,
598 p_count => x_msg_count,
599 p_data => x_msg_data
600 );
601
602 WHEN FND_API.g_exc_unexpected_error THEN
603 ROLLBACK TO delete_market_segments;
604 x_return_status := FND_API.g_ret_sts_unexp_error;
605 FND_MSG_PUB.count_and_get
606 (
607 p_encoded => FND_API.g_false,
608 p_count => x_msg_count,
609 p_data => x_msg_data
610 );
611
612 WHEN OTHERS THEN
613 ROLLBACK TO delete_market_segments;
614 x_return_status :=FND_API.g_ret_sts_unexp_error;
618 FND_MSG_PUB.count_and_get
615 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
616 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
617 END IF;
619 (
620 p_encoded => FND_API.g_false,
621 p_count => x_msg_count,
622 p_data => x_msg_data
623 );
624
625 END delete_market_segments;
626
627
628 /*****************************************************************************/
629 -- Procedure: lock_market_segments
630 --
631 -- History
632 -- 10/28/1999 julou created
633 -------------------------------------------------------------------------------
634 PROCEDURE lock_market_segments
635 (
636 p_api_version IN NUMBER,
637 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
638
639 x_return_status OUT NOCOPY VARCHAR2,
640 x_msg_count OUT NOCOPY NUMBER,
641 x_msg_data OUT NOCOPY VARCHAR2,
642
643 p_act_mks_id IN NUMBER,
644 p_object_version IN NUMBER
645 )
646 IS
647
648 l_api_version CONSTANT NUMBER := 1.0;
649 l_api_name CONSTANT VARCHAR2(30) := 'delete_market_segments';
650 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
651 l_mks_id NUMBER;
652
653 CURSOR c_obj IS
654 SELECT activity_market_segment_id
655 FROM AMS_ACT_MARKET_SEGMENTS
656 WHERE activity_market_segment_id = p_act_mks_id
657 AND object_version_number = p_object_version
658 FOR UPDATE NOWAIT;
659
660 BEGIN
661 -- initialize
662 AMS_Utility_PVT.debug_message(l_full_name || ': start');
663
664 IF FND_API.to_boolean(p_init_msg_list) THEN
665 FND_MSG_PUB.initialize;
666 END IF;
667
668 IF NOT FND_API.compatible_api_call
669 (
670 l_api_version,
671 p_api_version,
672 l_api_name,
673 g_pkg_name
674 )
675 THEN
676 RAISE FND_API.g_exc_unexpected_error;
677 END IF;
678
679 x_return_status := FND_API.g_ret_sts_success;
680
681 -- lock
682 AMS_Utility_PVT.debug_message(l_full_name || ': lock');
683
684 OPEN c_obj;
685 FETCH c_obj INTO l_mks_id;
686 IF (c_obj%NOTFOUND) THEN
687 CLOSE c_obj;
688 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
689 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
690 FND_MSG_PUB.add;
691 END IF;
692 RAISE FND_API.g_exc_error;
693 END IF;
694 CLOSE c_obj;
695
696 -- finish
697 FND_MSG_PUB.count_and_get
698 (
699 p_encoded => FND_API.g_false,
700 p_count => x_msg_count,
701 p_data => x_msg_data
702 );
703
704 AMS_Utility_PVT.debug_message(l_full_name || ': end');
705
706 EXCEPTION
707
708 WHEN AMS_Utility_PVT.resource_locked THEN
709 x_return_status := FND_API.g_ret_sts_error;
710 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
711 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
712 FND_MSG_PUB.add;
713 END IF;
714 FND_MSG_PUB.count_and_get
715 (
716 p_encoded => FND_API.g_false,
717 p_count => x_msg_count,
718 p_data => x_msg_data
719 );
720
721 WHEN FND_API.g_exc_error THEN
722 x_return_status := FND_API.g_ret_sts_error;
723 FND_MSG_PUB.count_and_get
724 (
725 p_encoded => FND_API.g_false,
726 p_count => x_msg_count,
727 p_data => x_msg_data
728 );
729
730 WHEN FND_API.g_exc_unexpected_error THEN
731 x_return_status := FND_API.g_ret_sts_unexp_error;
732 FND_MSG_PUB.count_and_get
733 (
734 p_encoded => FND_API.g_false,
735 p_count => x_msg_count,
736 p_data => x_msg_data
737 );
738
739 WHEN OTHERS THEN
740 x_return_status :=FND_API.g_ret_sts_unexp_error;
741 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
742 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
743 END IF;
744 FND_MSG_PUB.count_and_get
745 (
746 p_encoded => FND_API.g_false,
747 p_count => x_msg_count,
748 p_data => x_msg_data
749 );
750
751 END LOCK_MARKET_SEGMENTS;
752
753
754 /*****************************************************************************/
755 -- Procedure: validate_market_segments
756 --
757 -- History
758 -- 10/28/1999 julou created
759 -- 12/16/1999 ptendulk Modified as we have to chack the validation level
760 -- before validting also added Cross Entity Validation
761 -------------------------------------------------------------------------------
762 PROCEDURE validate_market_segments
763 (
764 p_api_version IN NUMBER,
765 P_init_msg_list IN VARCHAR2 := FND_API.g_false,
766 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
767
768 x_return_status OUT NOCOPY VARCHAR2,
772 p_mks_rec IN mks_rec_type
769 x_msg_count OUT NOCOPY NUMBER,
770 x_msg_data OUT NOCOPY VARCHAR2,
771
773 )
774 IS
775
776 l_api_version CONSTANT NUMBER := 1.0;
777 l_api_name CONSTANT VARCHAR2(30) := 'validate_market_segments';
778 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
779
780 l_return_status VARCHAR2(1);
781
782 BEGIN
783 -- initialize
784 AMS_Utility_PVT.debug_message(l_full_name || ': start');
785
786 IF FND_API.to_boolean(p_init_msg_list) THEN
787 FND_MSG_PUB.initialize;
788 END IF;
789
790 IF NOT FND_API.compatible_api_call
791 (
792 l_api_version,
793 p_api_version,
794 l_api_name,
795 g_pkg_name
796 )
797 THEN
798 RAISE FND_API.g_exc_unexpected_error;
799 END IF;
800
801 x_return_status := FND_API.g_ret_sts_success;
802
803 ---------------------- validate Segment Items ------------------------
804 AMS_Utility_PVT.debug_message(l_full_name || ': check required items');
805
806 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
807 Check_Mks_items(
808 p_mks_rec => p_mks_rec,
809 p_validation_mode => JTF_PLSQL_API.g_create,
810 x_return_status => l_return_status
811 ) ;
812
813 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
814 RAISE FND_API.g_exc_unexpected_error;
815 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
816 RAISE FND_API.g_exc_error;
817 END IF;
818 END IF;
819
820
821 ---------------------- validate Segment Cross Entity Records ------------------------
822 --
823 -- Debug Message
824 --
825 AMS_Utility_PVT.debug_message(l_full_name||': check cross Entity');
826
827 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
828
829 Validate_cross_ent_Rec(
830 p_mks_rec => p_mks_rec,
831 p_complete_rec => NULL,
832 p_validation_mode => JTF_PLSQL_API.g_create,
833 x_return_status => l_return_status
834 );
835
836 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
837 RAISE FND_API.g_exc_unexpected_error;
838 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
839 RAISE FND_API.g_exc_error;
840 END IF;
841 END IF;
842
843 ---------------------- validate Segment Records ------------------------
844 --
845 -- Debug Message
846 --
847
848 AMS_Utility_PVT.debug_message(l_full_name||': check record');
849
850 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
851 Check_Mks_Record(
852 p_mks_rec => p_mks_rec,
853 p_complete_rec => NULL,
854 x_return_status => l_return_status
855 );
856
857 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
858 RAISE FND_API.g_exc_unexpected_error;
859 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
860 RAISE FND_API.g_exc_error;
861 END IF;
862 END IF;
863
864 -------------------- finish --------------------------
865 FND_MSG_PUB.count_and_get(
866 p_encoded => FND_API.g_false,
867 p_count => x_msg_count,
868 p_data => x_msg_data
869 );
870
871 AMS_Utility_PVT.debug_message(l_full_name ||': end');
872
873 EXCEPTION
874
875 WHEN FND_API.g_exc_error THEN
876 x_return_status := FND_API.g_ret_sts_error;
877 FND_MSG_PUB.count_and_get(
878 p_encoded => FND_API.g_false,
879 p_count => x_msg_count,
880 p_data => x_msg_data
881 );
882
883 WHEN FND_API.g_exc_unexpected_error THEN
884 x_return_status := FND_API.g_ret_sts_unexp_error ;
885 FND_MSG_PUB.count_and_get(
886 p_encoded => FND_API.g_false,
887 p_count => x_msg_count,
888 p_data => x_msg_data
889 );
890
891 WHEN OTHERS THEN
892 x_return_status := FND_API.g_ret_sts_unexp_error;
893 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
894 THEN
895 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
896 END IF;
897
898 FND_MSG_PUB.count_and_get(
899 p_encoded => FND_API.g_false,
900 p_count => x_msg_count,
901 p_data => x_msg_data
902 );
903
904 END validate_market_segments;
905
906
907
908
909 /*****************************************************************************/
910 -- Procedure: check_mks_req_items
911 --
912 -- History
913 -- 10/28/1999 julou created
914 -------------------------------------------------------------------------------
915 PROCEDURE check_mks_req_items
916 ( p_mks_rec IN mks_rec_type,
917 x_return_status OUT NOCOPY VARCHAR2
918 )
919 IS
920
921 BEGIN
922
926 -- check activity_market_segment_id
923 x_return_status := FND_API.g_ret_sts_success;
924
925 /* Start of code commented by ptendulk */
927 -- IF p_mks_rec.activity_market_segment_id IS NULL
928 -- AND p_validation_mode = JTF_PLSQL_API.g_update
929 -- THEN
930 -- IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
931 -- FND_MESSAGE.set_name('AMS', 'AMS_MKS_NO_ACT_MKS_ID');
932 -- FND_MSG_PUB.add;
933 -- END IF;
934 --
935 -- x_return_status := FND_API.g_ret_sts_error;
936 -- RETURN;
937 -- END IF;
938
939
940 -- check object_version_number
941 -- IF p_mks_rec.object_version_number IS NULL
942 -- AND p_validation_mode = JTF_PLSQL_API.g_update
943 -- THEN
944 -- IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
945 -- FND_MESSAGE.set_name('AMS', 'AMS_MKS_NO_OBJ_VER_NUM');
946 -- FND_MSG_PUB.add;
947 -- END IF;
948 --
949 -- x_return_status := FND_API.g_ret_sts_error;
950 -- RETURN;
951 -- END IF;
952 /* Start of code commented by ptendulk */
953
954 -- check market_segment_id
955 /* comment by julou 29-MAY-2001
956 IF p_mks_rec.market_segment_id IS NULL THEN
957 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
958 FND_MESSAGE.set_name('AMS', 'AMS_MKS_NO_MKS_ID');
959 FND_MSG_PUB.add;
960 END IF;
961
962 x_return_status := FND_API.g_ret_sts_error;
963 RETURN;
964 END IF;
965 */
966 -- check act_market_segment_used_by_id
967 IF p_mks_rec.act_market_segment_used_by_id IS NULL THEN
968 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
969 FND_MESSAGE.set_name('AMS', 'AMS_MKS_NO_ACT_MKS_USED_BY_ID');
970 FND_MSG_PUB.add;
971 END IF;
972
973 x_return_status := FND_API.g_ret_sts_error;
974 RETURN;
975 END IF;
976
977 -- check arc_act_market_segment_used_by
978 IF p_mks_rec.arc_act_market_segment_used_by IS NULL THEN
979 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
980 FND_MESSAGE.set_name('AMS', 'AMS_MKS_NO_ARC_ACT_MKS_USED_BY');
981 FND_MSG_PUB.add;
982 END IF;
983
984 x_return_status := FND_API.g_ret_sts_error;
985 RETURN;
986 END IF;
987
988 -- Following Code is added by ptendulk on 16th Dec as segment type is not null
989 -- check segment_type
990 -- Commented by skarumur as there is no segment type any more
991
992 /* IF p_mks_rec.segment_type IS NULL THEN
993 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
994 FND_MESSAGE.set_name('AMS', 'AMS_MKS_MISSING_MKS_TYPE');
995 FND_MSG_PUB.add;
996 END IF;
997
998 x_return_status := FND_API.g_ret_sts_error;
999 RETURN;
1000 END IF; */
1001
1002 END check_mks_req_items;
1003
1004
1005 /*****************************************************************************/
1006 -- Procedure: check_mks_fk_items
1007 --
1008 -- History
1009 -- 10/28/1999 julou created
1010 -- 06/14/2000 ptendulk Added Offer as used by activity
1011 -------------------------------------------------------------------------------
1012 PROCEDURE check_mks_fk_items
1013 (
1014 p_mks_rec IN mks_rec_type,
1015 x_return_status OUT NOCOPY VARCHAR2
1016 )
1017 IS
1018
1019 l_fk_flag VARCHAR2(1);
1020
1021 BEGIN
1022
1023 x_return_status := FND_API.g_ret_sts_success;
1024 /*
1025 IF UPPER(p_mks_rec.arc_act_market_segment_used_by)
1026 NOT IN ('CAMP', 'EVEH', 'EVEO', 'CELL') THEN
1027 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1028 FND_MESSAGE.set_name('AMS', 'AMS_MKS_BAD_ARC_ACT_MKS_USEDBY');
1029 FND_MSG_PUB.add;
1030 END IF;
1031
1032 x_return_status := FND_API.g_ret_sts_error;
1033 RETURN;
1034 END IF;
1035 */
1036
1037 /* Start of Comments by ptendulk on 16-dec-1999 */
1038 -- Following code is modified by ptendulk as
1039 -- we need to account for G_MISS_NUM /CHAR for arc_act_market_segment_used_by
1040 -- Checking of act_market_segment_used_by_id will be done is Validate record
1041 --
1042 -- IF UPPER(p_mks_rec.arc_act_market_segment_used_by) = 'CAMP' THEN
1043 -- l_fk_flag := AMS_Utility_PVT.check_fk_exists
1044 -- (
1045 -- 'AMS_CAMPAIGNS_VL',
1046 -- 'campaign_id',
1047 -- p_mks_rec.act_market_segment_used_by_id
1048 -- );
1049 -- ELSIF UPPER(p_mks_rec.arc_act_market_segment_used_by) = 'EVEH' THEN
1050 -- l_fk_flag := AMS_Utility_PVT.check_fk_exists
1051 -- (
1052 -- 'AMS_EVENT_HEADERS_VL',
1053 -- 'event_header_id',
1054 -- p_mks_rec.act_market_segment_used_by_id
1055 -- );
1056 -- ELSIF UPPER(p_mks_rec.arc_act_market_segment_used_by) = 'EVEO' THEN
1057 -- l_fk_flag := AMS_Utility_PVT.check_fk_exists
1058 -- (
1059 -- 'AMS_EVENT_OFFERS_VL',
1060 -- 'event_offer_id',
1061 -- p_mks_rec.act_market_segment_used_by_id
1062 -- );
1063 -- ELSE
1067 -- END IF;
1064 -- IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1065 -- FND_MESSAGE.set_name('AMS', 'AMS_MKS_BAD_ARC_ACT_MKS_USEDBY');
1066 -- FND_MSG_PUB.add;
1068 /* End of Comments by ptendulk on 16-dec-1999 */
1069
1070 --=============================================================================
1071 -- Following code is modified by ptendulk on 14th Jun 2000
1072 -- Add offer code as segments will be used by offers also.
1073 --=============================================================================
1074 -- Check arc_act_market_segment_used_by
1075
1076 --=============================================================================
1077 -- Following code is modified by ptendulk on 23th Aug 2000
1078 -- The validation is commented as there are lot of activities going to use
1079 -- Market Segment, it is easier to control the validation modifying
1080 -- Get_Qual_Table_Name_And_PK than changing this package every time the
1081 -- new activity start creating it.
1082 --=============================================================================
1083
1084 -- IF p_mks_rec.arc_act_market_segment_used_by <> FND_API.G_MISS_CHAR THEN
1085 -- IF p_mks_rec.arc_act_market_segment_used_by <> 'CAMP' AND
1086 -- p_mks_rec.arc_act_market_segment_used_by <> 'EVEH' AND
1087 -- p_mks_rec.arc_act_market_segment_used_by <> 'EVEO' AND
1088 -- p_mks_rec.arc_act_market_segment_used_by <> 'OFFR'
1089 -- THEN
1090 -- -- invalid item
1091 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1092 -- THEN -- MMSG
1093 ---- DBMS_OUTPUT.Put_Line('Foreign Key Does not Exist');
1094 -- FND_MESSAGE.Set_Name('AMS', 'AMS_MKS_BAD_ARC_ACT_MKS_USEDBY');
1095 -- FND_MSG_PUB.Add;
1096 -- END IF;
1097 -- x_return_status := FND_API.G_RET_STS_ERROR;
1098 -- -- If any errors happen abort API/Procedure.
1099 -- RETURN;
1100 -- END IF;
1101 -- END IF;
1102
1103 END check_mks_fk_items;
1104
1105
1106 -- Start of Comments
1107 --
1108 -- NAME
1109 -- Validate_Mks_UK_Items
1110 --
1111 -- PURPOSE
1112 -- This procedure is to validate ams_act_market_segments
1113 -- for Unique ness
1114 -- NOTES
1115 --
1116 --
1117 -- HISTORY
1118 -- 12/16/1999 ptendulk created
1119 -- End of Comments
1120
1121 PROCEDURE Check_Mks_Uk_Items(
1122 p_mks_rec IN mks_rec_type,
1123 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1124 x_return_status OUT NOCOPY VARCHAR2
1125 )
1126 IS
1127 l_valid_flag VARCHAR2(1);
1128 l_where_clause VARCHAR2(2000);
1129 BEGIN
1130
1131 x_return_status := FND_API.g_ret_sts_success;
1132
1133 -- For create_market Segments, when ACTIVITY_MARKET_SEGMENT_ID is passed in, we need to
1134 -- check if this ACTIVITY_MARKET_SEGMENT_ID is unique.
1135 IF p_validation_mode = JTF_PLSQL_API.g_create
1136 AND p_mks_rec.activity_market_segment_id IS NOT NULL
1137 THEN
1138 IF AMS_Utility_PVT.check_uniqueness(
1139 'ams_act_market_segments',
1140 'activity_market_segment_id = ' || p_mks_rec.activity_market_segment_id
1141 ) = FND_API.g_false
1142 THEN
1143 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1144 THEN
1145 FND_MESSAGE.set_name('AMS', 'AMS_MKS_DUP_ACt_ID');
1146 FND_MSG_PUB.add;
1147 END IF;
1148 x_return_status := FND_API.g_ret_sts_error;
1149 RETURN;
1150 END IF;
1151 END IF;
1152
1153 END Check_Mks_Uk_Items;
1154
1155 /*****************************************************************************/
1156 -- Procedure: check_mks_lookup_items
1157 --
1158 -- History
1159 -- 10/28/1999 julou created
1160 -------------------------------------------------------------------------------
1161 PROCEDURE check_mks_lookup_items
1162 (
1163 p_mks_rec IN mks_rec_type,
1164 x_return_status OUT NOCOPY VARCHAR2
1165 )
1166 IS
1167
1168 l_lookup_flag VARCHAR2(1);
1169
1170 BEGIN
1171
1172 x_return_status := FND_API.g_ret_sts_success;
1173 /* removed by julou. there is no segment type any more
1174 IF p_mks_rec.segment_type IS NOT NULL
1175 AND p_mks_rec.segment_type <> FND_API.g_miss_char THEN
1176 l_lookup_flag := AMS_Utility_PVT.check_lookup_exists
1177 (
1178 p_lookup_type => 'AMS_MKT_SEGMENT_TYPE',
1179 p_lookup_code => p_mks_rec.segment_type
1180 );
1181 END IF;
1182
1183 IF l_lookup_flag = FND_API.g_false THEN
1184 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1185 FND_MESSAGE.set_name('AMS', 'AMS_MKS_BAD_SEGMENT_TYPE');
1186 FND_MSG_PUB.add;
1187 END IF;
1188
1189 x_return_status := FND_API.g_ret_sts_error;
1190 RETURN;
1191 END IF;
1192 */
1193 /*IF p_mks_rec.exclude_flag IS NOT NULL
1194 AND p_mks_rec.exclude_flag <> FND_API.g_miss_char THEN
1195 l_lookup_flag := AMS_Utility_PVT.check_lookup_exists
1196 (
1197 p_lookup_type => 'AMS_SEGMENT_CONDITIONS',
1198 p_lookup_code => p_mks_rec.exclude_flag
1202 IF l_lookup_flag = FND_API.g_false THEN
1199 );
1200 END IF;
1201
1203 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1204 FND_MESSAGE.set_name('AMS', 'AMS_MKS_BAD_CONDITION_TYPE');
1205 FND_MSG_PUB.add;
1206 END IF;
1207
1208 x_return_status := FND_API.g_ret_sts_error;
1209 RETURN;
1210 END IF;
1211 */
1212 END check_mks_lookup_items;
1213
1214 -- Start of Comments
1215 --
1216 -- NAME
1217 -- Check_Mks_Items
1218 --
1219 -- PURPOSE
1220 -- This procedure is to validate ams_act_market_segtments
1221 -- NOTES
1222 --
1223 -- HISTORY
1224 -- 12/16/1999 ptendulk created
1225 -- End of Comments
1226
1227 PROCEDURE check_Mks_items(
1228 p_mks_rec IN mks_rec_type,
1229 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1230 x_return_status OUT NOCOPY VARCHAR2
1231 )
1232 IS
1233 BEGIN
1234
1235 check_mks_req_items(
1236 p_mks_rec => p_mks_rec,
1237 x_return_status => x_return_status
1238 );
1239 IF x_return_status <> FND_API.g_ret_sts_success THEN
1240 RETURN;
1241 END IF;
1242
1243
1244 Check_Mks_Uk_Items(
1245 p_mks_rec => p_mks_rec,
1246 p_validation_mode => p_validation_mode,
1247 x_return_status => x_return_status
1248 );
1249
1250 IF x_return_status <> FND_API.g_ret_sts_success THEN
1251 RETURN;
1252 END IF;
1253
1254 Check_Mks_Fk_Items(
1255 p_mks_rec => p_mks_rec,
1256 x_return_status => x_return_status
1257 );
1258
1259 IF x_return_status <> FND_API.g_ret_sts_success THEN
1260 RETURN;
1261 END IF;
1262
1263 Check_Mks_Lookup_Items(
1264 p_mks_rec => p_mks_rec,
1265 x_return_status => x_return_status
1266 );
1267
1268 IF x_return_status <> FND_API.g_ret_sts_success THEN
1269 RETURN;
1270 END IF;
1271
1272 END Check_Mks_Items;
1273
1274 -- Start of Comments
1275 --
1276 -- NAME
1277 -- Validate_cross_ent_Rec
1278 --
1279 -- PURPOSE
1280 -- This procedure is to validate Unique Marketsegment across
1281 -- Activities
1282 -- NOTES
1283 --
1284 --
1285 -- HISTORY
1286 -- 12/16/1999 ptendulk created
1287 -- End of Comments
1288 PROCEDURE Validate_cross_ent_Rec(
1289 p_mks_rec IN mks_rec_type,
1290 p_complete_rec IN mks_rec_type,
1291 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1292 x_return_status OUT NOCOPY VARCHAR2
1293 )
1294 IS
1295 l_mks_used_by_id NUMBER ;
1296 l_mks_used_by VARCHAR2(30) ;
1297 l_mks_id NUMBER ;
1298 l_where_clause VARCHAR2(2000);
1299 BEGIN
1300 x_return_status := FND_API.g_ret_sts_success;
1301
1302
1303 IF p_mks_rec.arc_act_market_segment_used_by <> FND_API.G_MISS_CHAR
1304 OR p_mks_rec.act_market_segment_used_by_id <> FND_API.G_MISS_NUM
1305 OR p_mks_rec.market_segment_id <> FND_API.G_MISS_NUM
1306 THEN
1307 IF p_mks_rec.act_market_segment_used_by_id = FND_API.G_MISS_NUM THEN
1308 l_mks_used_by_id := p_complete_rec.act_market_segment_used_by_id ;
1309 ELSE
1310 l_mks_used_by_id := p_mks_rec.act_market_segment_used_by_id ;
1311 END IF;
1312
1313 IF p_mks_rec.arc_act_market_segment_used_by = FND_API.G_MISS_CHAR THEN
1314 l_mks_used_by := p_complete_rec.arc_act_market_segment_used_by ;
1315 ELSE
1316 l_mks_used_by := p_mks_rec.arc_act_market_segment_used_by ;
1317 END IF;
1318
1319 IF p_mks_rec.market_segment_id = FND_API.G_MISS_NUM THEN
1320 l_mks_id := p_complete_rec.market_segment_id ;
1321 ELSE
1322 l_mks_id := p_mks_rec.market_segment_id ;
1323 END IF;
1324
1325 -- Check if Trigger_name is unique. Need to handle create and
1326 -- update differently.
1327
1328 -- Unique TRIGGER_NAME and TRIGGER_CREATED_FOR
1329 l_where_clause := ' market_segment_id = '|| l_mks_id||
1330 ' and act_market_segment_used_by_id = '||l_mks_used_by_id ||
1331 ' and arc_act_market_segment_used_by = '||''''||l_mks_used_by||'''' ;
1332
1333
1334 -- For Updates, must also check that uniqueness is not checked against the same record.
1335 IF p_validation_mode <> JTF_PLSQL_API.g_create THEN
1336 l_where_clause := l_where_clause || ' AND activity_market_segment_id <> ' || p_mks_rec.activity_market_segment_id;
1337 END IF;
1338
1339 IF AMS_Utility_PVT.Check_Uniqueness(
1340 p_table_name => 'ams_act_market_segments',
1341 p_where_clause => l_where_clause
1342 ) = FND_API.g_false
1343 THEN
1344 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1345 THEN
1346 FND_MESSAGE.set_name('AMS', 'AMS_MKS_DUP_SEGMENT');
1347 FND_MSG_PUB.add;
1348 END IF;
1349 x_return_status := FND_API.g_ret_sts_error;
1350 RETURN;
1351 END IF;
1352 END IF;
1353 END Validate_cross_ent_Rec ;
1354
1355 -- Start of Comments
1356 --
1357 -- NAME
1361 -- This procedure is to validate ams_act_market_segments table
1358 -- Validate_Mks_Record
1359 --
1360 -- PURPOSE
1362 -- NOTES
1363 --
1364 --
1365 -- HISTORY
1366 -- 12/16/1999 ptendulk created
1367 -- End of Comments
1368 PROCEDURE Check_Mks_Record(
1369 p_mks_rec IN mks_rec_type,
1370 p_complete_rec IN mks_rec_type,
1371 x_return_status OUT NOCOPY VARCHAR2
1372 )
1373 IS
1374 CURSOR c_mkt_seg_id(l_id NUMBER) IS
1375 SELECT COUNT(1)
1376 FROM qp_qualifiers
1377 WHERE qualifier_id = l_id;
1378
1379 l_mks_used_by_id NUMBER ;
1380 l_mks_used_by VARCHAR2(30) ;
1381 l_market_segment_id NUMBER ;
1382 l_segment_type VARCHAR2(30) ;
1383 l_seg_flag VARCHAR2(1) ;
1384
1385 l_table_name VARCHAR2(30);
1386 l_pk_name VARCHAR2(30);
1387 l_pk_value VARCHAR2(30);
1388 l_pk_data_type VARCHAR2(30);
1389 l_additional_where_clause VARCHAR2(4000); -- Used by Check_FK_Exists.
1390 l_dummy NUMBER;
1391
1392 BEGIN
1393 --
1394 -- Initialize the Out Variable
1395 --
1396 x_return_status := FND_API.g_ret_sts_success;
1397
1398 -- ELSIF UPPER(p_mks_rec.arc_act_market_segment_used_by) = 'EVEO' THEN
1399 -- l_fk_flag := AMS_Utility_PVT.check_fk_exists
1400 -- (
1401 -- 'AMS_EVENT_OFFERS_VL',
1402 -- 'event_offer_id',
1403 -- p_mks_rec.act_market_segment_used_by_id
1404 -- );
1405
1406 IF p_mks_rec.arc_act_market_segment_used_by <> FND_API.G_MISS_CHAR
1407 OR p_mks_rec.act_market_segment_used_by_id <> FND_API.G_MISS_NUM THEN
1408
1409 IF p_mks_rec.act_market_segment_used_by_id = FND_API.G_MISS_NUM THEN
1410 l_mks_used_by_id := p_complete_rec.act_market_segment_used_by_id ;
1411 ELSE
1412 l_mks_used_by_id := p_mks_rec.act_market_segment_used_by_id ;
1413 END IF;
1414
1415 IF p_mks_rec.arc_act_market_segment_used_by = FND_API.G_MISS_CHAR THEN
1416 l_mks_used_by := p_complete_rec.arc_act_market_segment_used_by ;
1417 ELSE
1418 l_mks_used_by := p_mks_rec.arc_act_market_segment_used_by ;
1419 END IF;
1420
1421
1422 -- Get table_name and pk_name for the ARC qualifier.
1423 AMS_Utility_PVT.Get_Qual_Table_Name_And_PK (
1424 p_sys_qual => l_mks_used_by,
1425 x_return_status => x_return_status,
1426 x_table_name => l_table_name,
1427 x_pk_name => l_pk_name
1428 );
1429
1430 l_pk_value := l_mks_used_by_id;
1431 l_pk_data_type := AMS_Utility_PVT.G_NUMBER;
1432 l_additional_where_clause := NULL;
1433
1434
1435 IF AMS_Utility_PVT.Check_FK_Exists (
1436 p_table_name => l_table_name
1437 ,p_pk_name => l_pk_name
1438 ,p_pk_value => l_pk_value
1439 ,p_pk_data_type => l_pk_data_type
1440 ,p_additional_where_clause => l_additional_where_clause
1441 ) = FND_API.G_FALSE
1442 THEN
1443 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1444 FND_MESSAGE.Set_Name ('AMS', 'AMS_MKS_INVALID_USED_BY');
1445 FND_MSG_PUB.Add;
1446 END IF;
1447
1448 x_return_status := FND_API.G_RET_STS_ERROR;
1449 RETURN;
1450 END IF;
1451 END IF;
1452
1453 -- Check MARKET_SEGMENT_ID
1454
1455 IF p_mks_rec.segment_type <> FND_API.G_MISS_CHAR
1456 OR p_mks_rec.market_segment_id <> FND_API.G_MISS_NUM THEN
1457
1458 IF p_mks_rec.market_segment_id = FND_API.G_MISS_NUM THEN
1459 l_market_segment_id := p_complete_rec.market_segment_id ;
1460 ELSE
1461 l_market_segment_id := p_mks_rec.market_segment_id ;
1462 END IF;
1463
1464 IF p_mks_rec.segment_type = FND_API.G_MISS_CHAR THEN
1465 l_segment_type := p_complete_rec.segment_type ;
1466 ELSE
1467 l_segment_type := p_mks_rec.segment_type ;
1468 END IF;
1469
1470 IF l_segment_type = 'MARKET_SEGMENT' THEN
1471 l_seg_flag := 'Y' ;
1472 ELSIF l_segment_type = 'CELL' THEN
1473 l_seg_flag := 'N' ;
1474 END IF;
1475
1476 l_table_name := 'AMS_CELLS_VL';
1477 l_pk_name := 'CELL_ID';
1478 l_pk_value := l_market_segment_id;
1479 l_pk_data_type := AMS_Utility_PVT.G_NUMBER;
1480 /*
1481 IF AMS_Utility_PVT.Check_FK_Exists (
1482 p_table_name => l_table_name
1483 ,p_pk_name => l_pk_name
1484 ,p_pk_value => l_pk_value
1485 ,p_pk_data_type => l_pk_data_type
1486 ) = FND_API.G_FALSE
1487 THEN
1488 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1489 THEN
1490 FND_MESSAGE.Set_Name('AMS', 'AMS_MKS_INVALID_MKS_ID');
1491 FND_MSG_PUB.Add;
1492 END IF;
1493
1494 x_return_status := FND_API.G_RET_STS_ERROR;
1495 RETURN;
1496 END IF; -- Check_FK_Exists
1500 -- if segment type is 'QUALIFIER', market_segment_id must be qualifier_id
1497 */END IF;
1498
1499 -- added by julou on MAY-01-2001
1501 -- from qp_qualifiers
1502 /* removed by julou. no segment type any more.
1503 IF p_mks_rec.segment_type = 'QUALIFIER' THEN
1504 OPEN c_mkt_seg_id(p_mks_rec.market_segment_id);
1505 FETCH c_mkt_seg_id INTO l_dummy;
1506 CLOSE c_mkt_seg_id;
1507
1508 IF l_dummy = 0 THEN
1509 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1510 FND_MESSAGE.Set_Name('AMS', 'AMS_MKS_INVALID_MKS_ID');
1511 FND_MSG_PUB.Add;
1512 END IF;
1513 END IF;
1514 END IF;*/
1515 -- end of comments
1516 END Check_Mks_Record;
1517
1518
1519 /*****************************************************************************/
1520 -- Procedure: complete_mks_rec
1521 --
1522 -- History
1523 -- 10/28/1999 julou created
1524 -- 05/08/2000 ptendulk Modified the record type declaration
1525 -------------------------------------------------------------------------------
1526 PROCEDURE complete_mks_rec
1527 (
1528 p_mks_rec IN mks_rec_type,
1529 x_complete_rec OUT NOCOPY mks_rec_type
1530 )
1531 IS
1532
1533 CURSOR c_obj IS
1534 SELECT * FROM AMS_ACT_MARKET_SEGMENTS
1535 WHERE activity_market_segment_id = p_mks_rec.activity_market_segment_id;
1536
1537 -- ==============================================================================
1538 -- Following code is Modified by ptendulk on 05/08/2000
1539 -- Changed the record type declaration
1540 -- ==============================================================================
1541 l_mks_rec c_obj%ROWTYPE;
1542
1543 -- l_mks_rec mks_rec_type;
1544
1545 BEGIN
1546
1547 x_complete_rec := p_mks_rec;
1548
1549 OPEN c_obj;
1550 FETCH c_obj INTO l_mks_rec;
1551 IF (c_obj%NOTFOUND) THEN
1552 CLOSE c_obj;
1553 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1554 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1555 FND_MSG_PUB.add;
1556 END IF;
1557 RAISE FND_API.g_exc_error;
1558 END IF;
1559 CLOSE c_obj;
1560
1561 IF p_mks_rec.market_segment_id = FND_API.g_miss_num
1562 -- Following Line is Commented by ptendulk on 16 dec
1563 -- OR p_mks_rec.market_segment_id IS NULL
1564 THEN
1565 x_complete_rec.market_segment_id := l_mks_rec.market_segment_id;
1566 END IF;
1567
1568 IF p_mks_rec.act_market_segment_used_by_id = FND_API.g_miss_num
1569 -- OR p_mks_rec.act_market_segment_used_by_id IS NULL
1570 -- Following Line is Commented by ptendulk on 16 dec
1571 THEN
1572 x_complete_rec.act_market_segment_used_by_id := l_mks_rec.act_market_segment_used_by_id;
1573 END IF;
1574
1575 IF p_mks_rec.arc_act_market_segment_used_by = FND_API.g_miss_char
1576 -- OR p_mks_rec.arc_act_market_segment_used_by IS NULL
1577 -- Following Line is Commented by ptendulk on 16 dec
1578 THEN
1579 x_complete_rec.arc_act_market_segment_used_by := l_mks_rec.arc_act_market_segment_used_by;
1580 END IF;
1581
1582 IF p_mks_rec.segment_type = FND_API.g_miss_char THEN
1583 x_complete_rec.segment_type := l_mks_rec.segment_type;
1584 END IF;
1585
1586 IF p_mks_rec.attribute_category = FND_API.g_miss_char THEN
1587 x_complete_rec.attribute_category := l_mks_rec.attribute_category;
1588 END IF;
1589
1590 IF p_mks_rec.attribute1 = FND_API.g_miss_char THEN
1591 x_complete_rec.attribute1 := l_mks_rec.attribute1;
1592 END IF;
1593
1594 IF p_mks_rec.attribute2 = FND_API.g_miss_char THEN
1595 x_complete_rec.attribute2 := l_mks_rec.attribute2;
1596 END IF;
1597
1598 IF p_mks_rec.attribute3 = FND_API.g_miss_char THEN
1599 x_complete_rec.attribute3 := l_mks_rec.attribute3;
1600 END IF;
1601
1602 IF p_mks_rec.attribute4 = FND_API.g_miss_char THEN
1603 x_complete_rec.attribute4 := l_mks_rec.attribute4;
1604 END IF;
1605
1606 IF p_mks_rec.attribute5 = FND_API.g_miss_char THEN
1607 x_complete_rec.attribute5 := l_mks_rec.attribute5;
1608 END IF;
1609
1610 IF p_mks_rec.attribute6 = FND_API.g_miss_char THEN
1611 x_complete_rec.attribute6 := l_mks_rec.attribute6;
1612 END IF;
1613
1614 IF p_mks_rec.attribute7 = FND_API.g_miss_char THEN
1615 x_complete_rec.attribute7 := l_mks_rec.attribute7;
1616 END IF;
1617
1618 IF p_mks_rec.attribute8 = FND_API.g_miss_char THEN
1619 x_complete_rec.attribute8 := l_mks_rec.attribute8;
1620 END IF;
1621
1622 IF p_mks_rec.attribute9 = FND_API.g_miss_char THEN
1623 x_complete_rec.attribute9 := l_mks_rec.attribute9;
1624 END IF;
1625
1626 IF p_mks_rec.attribute10 = FND_API.g_miss_char THEN
1627 x_complete_rec.attribute10 := l_mks_rec.attribute10;
1628 END IF;
1629
1630 IF p_mks_rec.attribute11 = FND_API.g_miss_char THEN
1631 x_complete_rec.attribute11 := l_mks_rec.attribute11;
1632 END IF;
1633
1634 IF p_mks_rec.attribute12 = FND_API.g_miss_char THEN
1635 x_complete_rec.attribute12 := l_mks_rec.attribute12;
1636 END IF;
1637
1638 IF p_mks_rec.attribute13 = FND_API.g_miss_char THEN
1642 IF p_mks_rec.attribute14 = FND_API.g_miss_char THEN
1639 x_complete_rec.attribute13 := l_mks_rec.attribute13;
1640 END IF;
1641
1643 x_complete_rec.attribute14 := l_mks_rec.attribute14;
1644 END IF;
1645
1646 IF p_mks_rec.attribute15 = FND_API.g_miss_char THEN
1647 x_complete_rec.attribute15 := l_mks_rec.attribute15;
1648 END IF;
1649 IF p_mks_rec.group_code = FND_API.g_miss_char THEN
1650 x_complete_rec.group_code := l_mks_rec.group_code;
1651 END IF;
1652 IF p_mks_rec.exclude_flag = FND_API.g_miss_char THEN
1653 x_complete_rec.exclude_flag := l_mks_rec.exclude_flag;
1654 END IF;
1655
1656 END complete_mks_rec;
1657
1658 -- Start of Comments
1659 --
1660 -- NAME
1661 -- Init_Mks_Rec
1662 --
1663 -- PURPOSE
1664 -- This procedure is to Initialize the Record type before Updation.
1665 --
1666 -- NOTES
1667 --
1668 --
1669 -- HISTORY
1670 -- 12/16/1999 ptendulk created
1671 -- End of Comments
1672 PROCEDURE Init_Mks_Rec(
1673 x_mks_rec OUT NOCOPY mks_rec_type
1674 )
1675 IS
1676 BEGIN
1677 x_mks_rec.activity_market_segment_id := FND_API.G_MISS_NUM ;
1678 x_mks_rec.last_update_date := FND_API.G_MISS_DATE ;
1679 x_mks_rec.last_updated_by := FND_API.G_MISS_NUM ;
1680 x_mks_rec.creation_date := FND_API.G_MISS_DATE ;
1681 x_mks_rec.created_by := FND_API.G_MISS_NUM ;
1682 x_mks_rec.last_update_login := FND_API.G_MISS_NUM ;
1683 x_mks_rec.market_segment_id := FND_API.G_MISS_NUM ;
1684 x_mks_rec.act_market_segment_used_by_id := FND_API.G_MISS_NUM ;
1685 x_mks_rec.arc_act_market_segment_used_by := FND_API.G_MISS_CHAR ;
1686 x_mks_rec.object_version_number := FND_API.G_MISS_NUM ;
1687 x_mks_rec.attribute_category := FND_API.G_MISS_CHAR ;
1688 x_mks_rec.attribute1 := FND_API.G_MISS_CHAR ;
1689 x_mks_rec.attribute2 := FND_API.G_MISS_CHAR ;
1690 x_mks_rec.attribute3 := FND_API.G_MISS_CHAR ;
1691 x_mks_rec.attribute4 := FND_API.G_MISS_CHAR ;
1692 x_mks_rec.attribute5 := FND_API.G_MISS_CHAR ;
1693 x_mks_rec.attribute6 := FND_API.G_MISS_CHAR ;
1694 x_mks_rec.attribute7 := FND_API.G_MISS_CHAR ;
1695 x_mks_rec.attribute8 := FND_API.G_MISS_CHAR ;
1696 x_mks_rec.attribute9 := FND_API.G_MISS_CHAR ;
1697 x_mks_rec.attribute10 := FND_API.G_MISS_CHAR ;
1698 x_mks_rec.attribute11 := FND_API.G_MISS_CHAR ;
1699 x_mks_rec.attribute12 := FND_API.G_MISS_CHAR ;
1700 x_mks_rec.attribute13 := FND_API.G_MISS_CHAR ;
1701 x_mks_rec.attribute14 := FND_API.G_MISS_CHAR ;
1702 x_mks_rec.attribute15 := FND_API.G_MISS_CHAR ;
1703 x_mks_rec.segment_type := FND_API.G_MISS_CHAR ;
1704 x_mks_rec.group_code := FND_API.G_MISS_CHAR ;
1705 x_mks_rec.exclude_flag := FND_API.G_MISS_CHAR ;
1706
1707
1708 END Init_Mks_Rec ;
1709
1710
1711 END AMS_Act_Market_Segments_PVT;