[Home] [Help]
PACKAGE BODY: APPS.AMS_CAMPAIGNRULES_PVT
Source
1 PACKAGE BODY AMS_CampaignRules_PVT AS
2 /* $Header: amsvcbrb.pls 120.6 2006/04/12 03:19:24 mayjain noship $ */
3
4
5 g_pkg_name CONSTANT VARCHAR2(30):='AMS_CampaignRules_PVT';
6
7 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
8 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
9 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
10
11 PROCEDURE Archive_Schedules(
12 p_campaign_id IN NUMBER
13 );
14
15 PROCEDURE Archive_Campaigns(
16 p_program_id IN NUMBER
17 );
18
19 PROCEDURE Activate_Campaigns(
20 p_program_id IN NUMBER
21 ) ;
22
23 PROCEDURE Hold_Campaigns(
24 p_program_id IN NUMBER,
25 p_system_status_code IN VARCHAR2
26 );
27
28
29 PROCEDURE Update_Related_Source_Code(
30 p_source_code IN VARCHAR2,
31 p_source_code_for_id IN NUMBER,
32 p_source_code_for IN VARCHAR2,
33 p_related_source_code IN VARCHAR2,
34 p_related_source_code_for_id IN NUMBER,
35 p_related_source_code_for IN VARCHAR2,
36 x_return_status OUT NOCOPY VARCHAR2
37 ) ;
38
39 PROCEDURE Cancel_Schedule(p_campaign_id IN NUMBER) ;
40 PROCEDURE Cancel_Program(p_program_id IN NUMBER) ;
41 PROCEDURE Complete_Schedule(p_campaign_id IN NUMBER) ;
42 PROCEDURE Complete_Program(p_program_id IN NUMBER) ;
43 PROCEDURE Check_Close_Campaign(p_campaign_id IN NUMBER) ;
44 -----------------------------------------------------------------------
45 -- PROCEDURE
46 -- handle_camp_status
47 --
48 -- HISTORY
49 -- 11/01/99 holiu Created.
50 -- 07-May-2001 ptendulk Commented check for system status type as
51 -- Programs will also use same api.
52 -----------------------------------------------------------------------
53 PROCEDURE handle_camp_status(
54 p_user_status_id IN NUMBER,
55 x_status_code OUT NOCOPY VARCHAR2,
56 x_return_status OUT NOCOPY VARCHAR2
57 )
58 IS
59
60 l_status_code VARCHAR2(30);
61
62 CURSOR c_status_code IS
63 SELECT system_status_code
64 FROM ams_user_statuses_b
65 WHERE user_status_id = p_user_status_id
66 -- Commented by ptendulk on 07-May-2001 as Program and campaign use the same api.
67 -- AND system_status_type = 'AMS_CAMPAIGN_STATUS'
68 AND enabled_flag = 'Y';
69
70 BEGIN
71
72 x_return_status := FND_API.g_ret_sts_success;
73
74 OPEN c_status_code;
75 FETCH c_status_code INTO l_status_code ;
76 CLOSE c_status_code;
77
78 IF l_status_code IS NULL THEN
79 x_return_status := FND_API.g_ret_sts_error;
80 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_USER_STATUS');
81 END IF;
82
83 x_status_code := l_status_code;
84
85 END handle_camp_status;
86
87
88 -----------------------------------------------------------------------
89 -- PROCEDURE
90 -- handle_camp_inherit_flag
91 --
92 -- HISTORY
93 -- 11/01/99 holiu Created.
94 -----------------------------------------------------------------------
95 PROCEDURE handle_camp_inherit_flag(
96 p_parent_id IN NUMBER,
97 p_rollup_type IN VARCHAR2,
98 x_inherit_flag OUT NOCOPY VARCHAR2,
99 x_return_status OUT NOCOPY VARCHAR2
100 )
101 IS
102
103 l_rollup_type VARCHAR2(30);
104
105 CURSOR c_parent IS
106 SELECT rollup_type
107 FROM ams_campaigns_vl
108 WHERE campaign_id = p_parent_id;
109
110 BEGIN
111
112 x_inherit_flag := 'N';
113 x_return_status := FND_API.g_ret_sts_success;
114
115 IF p_parent_id IS NOT NULL THEN
116 OPEN c_parent;
117 FETCH c_parent INTO l_rollup_type;
118 CLOSE c_parent;
119
120 IF l_rollup_type IS NULL THEN
121 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_PARENT_ID');
122 x_return_status := FND_API.g_ret_sts_error;
123 ELSIF l_rollup_type = 'ECAM' THEN
124 AMS_Utility_PVT.error_message('AMS_CAMP_PARENT_IS_EC');
125 x_return_status := FND_API.g_ret_sts_error;
126 ELSIF l_rollup_type = 'MCAM' THEN
127 IF p_rollup_type = 'ECAM' THEN
128 x_inherit_flag := 'Y';
129 ELSE
130 AMS_Utility_PVT.error_message('AMS_CAMP_PARENT_IS_MC');
131 x_return_status := FND_API.g_ret_sts_error;
132 END IF;
133 END IF;
134 END IF;
135
136 END handle_camp_inherit_flag;
137
138
139 -----------------------------------------------------------------------
140 -- PROCEDURE
141 -- create_camp_association
142 --
143 -- HISTORY
144 -- 07/15/2000 ptendulk Created.
145 -----------------------------------------------------------------------
146 PROCEDURE create_camp_association(
147 p_campaign_id IN NUMBER,
148 p_event_id IN NUMBER,
149 p_event_type IN VARCHAR2,
150 x_return_status OUT NOCOPY VARCHAR2
151 )
152 IS
153
154 l_assc_rec AMS_Associations_PVT.association_rec_type;
155 l_event_type VARCHAR2(30);
156 l_event_id NUMBER;
157 l_obj_ver NUMBER;
158 l_obj_id NUMBER;
159
160 l_msg_count NUMBER;
161 l_msg_data VARCHAR2(2000);
162
163 CURSOR c_event_det IS
164 SELECT object_association_id,
165 object_version_number,
166 using_object_id,
167 using_object_type
168 FROM ams_object_associations
169 WHERE master_object_type = 'CAMP'
170 AND master_object_id = p_campaign_id
171 AND using_object_type in ('EVEH', 'EVEO');
172
173 -- CURSOR c_event_used IS
174 -- SELECT 1
175 -- FROM DUAL
176 -- WHERE EXISTS(
177 -- SELECT 1
178 -- FROM ams_object_associations
179 -- WHERE master_object_type = 'CAMP'
180 -- AND using_object_type = p_event_type
181 -- AND using_object_id = p_event_id);
182
183 --
184 -- Following Cursor is rewritten by ptendulk on 14Aug2000
185 -- Ref. Bug :1378977
186 -- Check that the event is not associated to any other campaign
187 --
188 CURSOR c_event_used IS
189 SELECT master_object_id
190 FROM ams_object_associations
191 WHERE master_object_type = 'CAMP'
192 AND using_object_type = p_event_type
193 AND using_object_id = p_event_id;
194 l_master_id NUMBER ;
195
196 BEGIN
197
198 x_return_status := FND_API.g_ret_sts_success;
199
200 -- find out if there is any event already associated to the campaign
201 OPEN c_event_det;
202 FETCH c_event_det INTO l_obj_id, l_obj_ver, l_event_id, l_event_type;
203 CLOSE c_event_det ;
204
205 -- delete it if no longer associated
206 IF l_obj_id IS NOT NULL
207 AND (l_event_id <> p_event_id OR l_event_type <> p_event_type
208 OR p_event_id IS NULL)
209 THEN
210 l_assc_rec.object_version_number := l_obj_ver;
211 l_assc_rec.object_association_id := l_obj_id ;
212
213 AMS_Associations_PVT.delete_association(
214 p_api_version => 1.0,
215 p_init_msg_list => FND_API.g_false,
216 p_commit => FND_API.g_false,
217 p_validation_level => FND_API.g_valid_level_full,
218
219 x_return_status => x_return_status,
220 x_msg_count => l_msg_count,
221 x_msg_data => l_msg_data,
222
223 p_object_association_id => l_obj_id,
224 p_object_version => l_obj_ver
225 );
226 END IF;
227
228 IF x_return_status = FND_API.g_ret_sts_success
229 AND p_event_id IS NOT NULL
230 THEN
231 -- check if the given event is associated to any campaign
232 l_obj_id := 0 ;
233 OPEN c_event_used ;
234 FETCH c_event_used INTO l_master_id ;
235 CLOSE c_event_used ;
236
237 --
238 -- Following code is modified by ptendulk on 14Aug2000
239 -- Check if the event is associated , if yes check if it is
240 -- associated to any other campaign if yes give error message
241 -- if not associated to any campaign, create association
242 --
243 IF l_master_id IS NOT NULL AND
244 l_master_id <> p_campaign_id
245 THEN
246 x_return_status := FND_API.g_ret_sts_error;
247 AMS_Utility_PVT.error_message('AMS_CAMP_EVE_EXIST');
248 ELSIF l_master_id IS NULL THEN
249 -- initialize the association rec
250 l_assc_rec.master_object_type := 'CAMP' ;
251 l_assc_rec.using_object_type := p_event_type ;
252 l_assc_rec.master_object_id := p_campaign_id ;
253 l_assc_rec.using_object_id := p_event_id ;
254 l_assc_rec.primary_flag := 'Y' ;
255 l_assc_rec.usage_type := 'CREATED' ;
256
257 AMS_Associations_PVT.create_association(
258 p_api_version => 1.0,
259 p_init_msg_list => FND_API.g_false,
260 p_commit => FND_API.g_false,
261 p_validation_level => FND_API.g_valid_level_full,
262
263 x_return_status => x_return_status,
264 x_msg_count => l_msg_count,
265 x_msg_data => l_msg_data,
266
267 p_association_rec => l_assc_rec,
268 x_object_association_id => l_obj_id
269 );
270 END IF;
271 END IF;
272
273 END create_camp_association;
274
275
276 -----------------------------------------------------------------------
277 -- PROCEDURE
278 -- Udpate_Camp_Source_Code
279 --
280 -- HISTORY
281 -- 06/26/00 holiu Created.
282 -- 07-Feb-2001 ptendulk Changed the logic for cascade source_code
283 -- flag as it is moved to schedules tables now.
284 -- 12-Jun-2001 ptendulk Refer bug #1825922
285 -- 16-aug-2002 soagrawa Fixed bug# 2511783 in update_camp_source_code. This is related to
286 -- updating global flag
287 --
288 -----------------------------------------------------------------------
289 PROCEDURE update_camp_source_code(
290 p_campaign_id IN NUMBER,
291 p_source_code IN VARCHAR2,
292 p_global_flag IN VARCHAR2,
293 x_source_code OUT NOCOPY VARCHAR2,
294 p_related_source_object IN VARCHAR2 := NULL,
295 p_related_source_id IN NUMBER := NULL,
296 x_return_status OUT NOCOPY VARCHAR2
297 )
298 IS
299
300 l_msg_data VARCHAR2(2000);
301 l_msg_count NUMBER;
302
303 l_source_code VARCHAR2(30);
304 l_global_flag VARCHAR2(1);
305 l_cascade_flag VARCHAR2(1);
306 l_custom_setup_id NUMBER;
307 l_csch_exist NUMBER;
308 l_source_code_id NUMBER;
309 l_status VARCHAR2(30) ;
310 l_rollup_type VARCHAR2(30) ;
311
312 CURSOR c_old_info IS
313 SELECT global_flag, source_code, custom_setup_id, status_code, rollup_type,
314 related_event_id
315 FROM ams_campaigns_all_b
316 WHERE campaign_id = p_campaign_id;
317
318 CURSOR c_csch_exist IS
319 SELECT 1
320 FROM DUAL
321 WHERE EXISTS(
322 SELECT 1
323 FROM ams_campaign_schedules_b
324 WHERE campaign_id = p_campaign_id
325 AND active_flag = 'Y'
326 AND use_parent_code_flag = 'Y' );
327
328
329 CURSOR c_source_code IS
330 SELECT source_code_id
331 FROM ams_source_codes
332 WHERE source_code = x_source_code
333 AND active_flag = 'Y';
334
335 l_rollup VARCHAR2(30) ;
336 l_related_event_id NUMBER ;
337
338 l_related_source_code VARCHAR2(30);
339 l_related_source_object VARCHAR2(30) := p_related_source_object ;
340 l_related_source_id NUMBER := p_related_source_id ;
341
342 BEGIN
343
344 x_source_code := p_source_code;
345 x_return_status := FND_API.g_ret_sts_success;
346
347
348 OPEN c_old_info;
349 FETCH c_old_info INTO l_global_flag, l_source_code, l_custom_setup_id, l_status, l_rollup, l_related_event_id;
350 CLOSE c_old_info;
351
352 l_related_source_code := Get_Event_Source_Code(p_related_source_object,p_related_source_id);
353 IF l_related_source_code IS NULL THEN
354 l_related_source_id := NULL ;
355 l_related_source_object := NULL ;
356 END IF ;
357
358
359 IF p_source_code = l_source_code
360 -- following line of code is added by ptendulk on 12-Jun-2001
361 -- Refer bug #1825922
362 AND p_global_flag = l_global_flag THEN
363 IF (AMS_DEBUG_HIGH_ON) THEN
364
365 AMS_Utility_PVT.Debug_Message('Source code is Same') ;
366 END IF;
367 IF (p_related_source_id IS NULL AND l_related_event_id IS NOT NULL)
368 OR (l_related_event_id IS NULL AND p_related_source_id IS NOT NULL)
369 THEN
370 Update_Related_Source_Code(
371 p_source_code => p_source_code,
372 p_source_code_for_id => p_campaign_id ,
373 p_source_code_for => 'CAMP',
374 p_related_source_code => l_related_source_code,
375 p_related_source_code_for_id => l_related_source_id,
376 p_related_source_code_for => l_related_source_object,
377 x_return_status => x_return_status
378 );
379 ELSIF p_related_source_id <> l_related_event_id THEN
380 Update_Related_Source_Code(
381 p_source_code => p_source_code,
382 p_source_code_for_id => p_campaign_id ,
383 p_source_code_for => 'CAMP',
384 p_related_source_code => l_related_source_code,
385 p_related_source_code_for_id => l_related_source_id,
386 p_related_source_code_for => l_related_source_object,
387 x_return_status => x_return_status
388 );
389 END IF ;
390 RETURN ;
391 END IF ;
392
393 IF l_rollup = 'RCAM' THEN
394 IF p_source_code IS NULL THEN
395 AMS_Utility_PVT.Error_Message('AMS_CAMP_NO_PROG_CODE');
396 x_return_status := FND_API.g_ret_sts_error;
397 RETURN;
398 ELSE
399 --aranka added 07/27/02
400 IF AMS_Utility_PVT.check_uniqueness(
401 'ams_campaigns_all_b',
402 'source_code = ''' || p_source_code || ''''
403 || ' AND campaign_id <> '||p_campaign_id
404 -- || ''' AND rollup_type = ''RCAM'' AND campaign_id <> '||p_campaign_id
405 ) = FND_API.g_false
406 THEN
407 AMS_Utility_PVT.Error_Message('AMS_CAMP_BAD_PROG_CODE');
408 x_return_status := FND_API.g_ret_sts_error;
409 RETURN;
410 END IF;
411 END IF;
412 ELSE
413
414 -- Can not update source code if the Status is not new
415 IF l_status <> 'NEW' THEN
416 AMS_Utility_PVT.error_message('AMS_CAMP_UPDATE_SRC_STAT');
417 x_return_status := FND_API.g_ret_sts_error;
418 RETURN;
419 END IF ;
420
421 -- all this code added by aranka was removed by SOAGRAWA on 16-AUG-2002
422 -- refer to bug# 2511783
423 --aranka added 07/27/02
424 --sam added start
425 -- IF p_source_code IS NOT NULL THEN
426 -- IF AMS_Utility_PVT.check_uniqueness(
427 -- 'ams_source_codes',
428 -- 'source_code = ''' || p_source_code ||
429 -- ''' AND active_flag = ''Y'''
430 -- ) = FND_API.g_false
431 -- THEN
432 -- AMS_Utility_PVT.Error_Message('AMS_CAMP_DUPLICATE_CODE');
433 -- x_return_status := FND_API.g_ret_sts_error;
434 -- RETURN;
435 -- END IF;
436 -- IF AMS_Utility_PVT.check_uniqueness(
437 -- 'ams_campaigns_all_b',
438 -- 'source_code = ''' || p_source_code || ''''
439 -- || ' AND campaign_id <> '||p_campaign_id
440 -- ) = FND_API.g_false
441 -- THEN
442 -- AMS_Utility_PVT.Error_Message('AMS_CAMP_DUPLICATE_CODE');
443 -- x_return_status := FND_API.g_ret_sts_error;
444 -- RETURN;
445 -- END IF;
446 -- END IF;
447 --sam added end
448
449 OPEN c_csch_exist;
450 FETCH c_csch_exist INTO l_csch_exist;
451 CLOSE c_csch_exist;
452
453 -- source_code cannot be changed if cascade and schedule exists
454 IF l_csch_exist IS NOT NULL THEN
455 AMS_Utility_PVT.error_message('AMS_CAMP_UPDATE_SOURCE_CODE');
456 x_return_status := FND_API.g_ret_sts_error;
457 RETURN;
458 END IF;
459 IF (AMS_DEBUG_HIGH_ON) THEN
460
461 AMS_Utility_PVT.Debug_message('Global Flag : ' ||l_global_flag );
462 END IF;
463 -- generate a new source code if global flag is updated and
464 -- source code is not cascaded to schedules
465 IF p_global_flag <> l_global_flag
466 THEN
467 x_source_code := AMS_SourceCode_PVT.get_new_source_code(
468 p_object_type => 'CAMP',
469 p_custsetup_id => l_custom_setup_id,
470 p_global_flag => p_global_flag
471 );
472 END IF;
473 IF (AMS_DEBUG_HIGH_ON) THEN
474
475 AMS_Utility_PVT.Debug_message('Source Code : ' ||x_source_code );
476 END IF;
477
478 IF x_source_code = l_source_code THEN
479 RETURN;
480 END IF;
481
482 IF x_source_code IS NULL THEN
483 AMS_Utility_PVT.error_message('AMS_CAMP_NO_SOURCE_CODE');
484 x_return_status := FND_API.g_ret_sts_error;
485 RETURN;
486 END IF;
487
488 -- check if the new source code is unique
489 OPEN c_source_code;
490 FETCH c_source_code INTO l_source_code_id;
491 CLOSE c_source_code;
492
493 IF l_source_code_id IS NOT NULL THEN
494 AMS_Utility_PVT.error_message('AMS_CAMP_DUPLICATE_CODE');
495 x_return_status := FND_API.g_ret_sts_error;
496 RETURN;
497 END IF;
498
499 -- this code added here by soagrawa on 16-aug-2002 for bug# 2511783
500 IF x_source_code IS NOT NULL THEN
501 IF AMS_Utility_PVT.check_uniqueness(
502 'ams_campaigns_all_b',
503 'source_code = ''' || x_source_code || ''''
504 || ' AND campaign_id <> '||p_campaign_id
505 ) = FND_API.g_false
506 THEN
507 AMS_Utility_PVT.Error_Message('AMS_CAMP_DUPLICATE_CODE');
508 x_return_status := FND_API.g_ret_sts_error;
509 RETURN;
510 END IF;
511 END IF;
512 -- end soagrawa
513
514 -- otherwise revoke the old one and add the new one to ams_source_codes
515 AMS_SourceCode_PVT.revoke_sourcecode(
516 p_api_version => 1.0,
517 p_init_msg_list => FND_API.g_false,
518 p_commit => FND_API.g_false,
519 p_validation_level => FND_API.g_valid_level_full,
520
521 x_return_status => x_return_status,
522 x_msg_count => l_msg_count,
523 x_msg_data => l_msg_data,
524
525 p_sourcecode => l_source_code
526 );
527
528 IF x_return_status <> FND_API.g_ret_sts_success THEN
529 RAISE FND_API.g_exc_error;
530 END IF;
531
532 AMS_SourceCode_PVT.create_sourcecode(
533 p_api_version => 1.0,
534 p_init_msg_list => FND_API.g_false,
535 p_commit => FND_API.g_false,
536 p_validation_level => FND_API.g_valid_level_full,
537
538 x_return_status => x_return_status,
539 x_msg_count => l_msg_count,
540 x_msg_data => l_msg_data,
541
542 p_sourcecode => x_source_code,
543 p_sourcecode_for => 'CAMP',
544 p_sourcecode_for_id => p_campaign_id,
545 p_related_sourcecode => l_related_source_code,
546 p_releated_sourceobj => l_related_source_object,
547 p_related_sourceid => l_related_source_id,
548 x_sourcecode_id => l_source_code_id
549 );
550
551 IF x_return_status <> FND_API.g_ret_sts_success THEN
552 RAISE FND_API.g_exc_error;
553 END IF;
554 END IF ;
555 END update_camp_source_code;
556
557
558 ---------------------------------------------------------------------
559 -- PROCEDURE
560 -- check_camp_update
561 --
562 -- HISTORY
563 -- 11/01/99 holiu Created.
564 -- 06/26/00 holiu Move out source code logic.
565 -- 07/15/00 holiu Requirement changes for going live.
566 ---------------------------------------------------------------------
567 PROCEDURE check_camp_update(
568 p_camp_rec IN AMS_Campaign_PVT.camp_rec_type,
569 p_complete_rec IN AMS_Campaign_PVT.camp_rec_type,
570 x_return_status OUT NOCOPY VARCHAR2
571 )
572 IS
573
574 CURSOR c_resource IS
575 SELECT resource_id
576 FROM ams_jtf_rs_emp_v
577 WHERE user_id = FND_GLOBAL.user_id ;
578
579 CURSOR c_child IS
580 SELECT 1
581 FROM DUAL
582 WHERE EXISTS(
583 SELECT campaign_id
584 FROM ams_campaigns_vl
585 WHERE parent_campaign_id = p_camp_rec.campaign_id);
586
587 CURSOR c_camp IS
588 SELECT *
589 FROM ams_campaigns_vl
590 WHERE campaign_id = p_camp_rec.campaign_id;
591
592 l_camp_rec c_camp%ROWTYPE;
593 l_dummy NUMBER;
594
595 l_resource NUMBER ;
596 l_access VARCHAR2(1);
597 l_admin_user BOOLEAN;
598 l_rollup_type VARCHAR2(30);
599 l_owner NUMBER ;
600 BEGIN
601
602 x_return_status := FND_API.g_ret_sts_success;
603
604 OPEN c_resource ;
605 FETCH c_resource INTO l_resource;
606 CLOSE c_resource ;
607
608 IF p_complete_rec.rollup_type = 'RCAM' THEN
609 l_rollup_type := 'RCAM' ;
610 ELSE
611 l_rollup_type := 'CAMP' ;
612 END IF ;
613
614 IF (AMS_DEBUG_HIGH_ON) THEN
615
616
617
618 AMS_Utility_PVT.debug_message('Obj : '||l_rollup_type||p_camp_rec.campaign_id||' User : '||l_resource);
619
620 END IF;
621
622 l_access := AMS_Access_PVT.Check_Update_Access(p_object_id => p_camp_rec.campaign_id ,
623 p_object_type => l_rollup_type,
624 p_user_or_role_id => l_resource,
625 p_user_or_role_type => 'USER');
626
627 IF l_access = 'N' THEN
628 AMS_Utility_PVT.error_message('AMS_CAMP_NO_ACCESS');
629 x_return_status := FND_API.g_ret_sts_error;
630 RETURN;
631 END IF ;
632
633
634
635 OPEN c_camp;
636 FETCH c_camp INTO l_camp_rec;
637 IF c_camp%NOTFOUND THEN
638 CLOSE c_camp;
639 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
640 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
641 FND_MSG_PUB.add;
642 END IF;
643 RAISE FND_API.g_exc_error;
644 END IF;
645 CLOSE c_camp;
646
647 --aranka removed comment 01/18/02
648 l_admin_user := AMS_Access_PVT.Check_Admin_Access(l_resource);
649 --aranka removed comment 01/18/02
650
651 IF p_camp_rec.owner_user_id = FND_API.g_miss_num THEN
652 l_owner := p_complete_rec.owner_user_id ;
653 ELSE
654 l_owner := p_camp_rec.owner_user_id ;
655 END IF;
656
657 -- aranka added 12/17/01 bug #2148325 start
658 -- l_admin_user := AMS_Access_PVT.Check_Admin_Access(l_owner);
659 -- aranka added 12/17/01 bug #2148325 end
660
661 IF (AMS_DEBUG_HIGH_ON) THEN
662
663
664
665 AMS_Utility_PVT.Debug_message('p_camp_rec.owner_user_id : '|| p_camp_rec.owner_user_id) ;
666
667 END IF;
668 IF (AMS_DEBUG_HIGH_ON) THEN
669
670 AMS_Utility_PVT.Debug_message('l_camp_rec.owner_user_id : '|| l_camp_rec.owner_user_id) ;
671 END IF;
672 IF (AMS_DEBUG_HIGH_ON) THEN
673
674 AMS_Utility_PVT.Debug_message('Resource id : '||l_resource ) ;
675 END IF;
676
677 -- Only owner/ Super Admin can change the owner.
678 IF p_camp_rec.owner_user_id <> FND_API.g_miss_num
679 AND p_camp_rec.owner_user_id <> l_camp_rec.owner_user_id
680 AND l_admin_user = FALSE
681 --aranka added comment 01/18/02
682 -- AND l_owner <> l_resource
683 AND l_camp_rec.owner_user_id <> l_resource
684 --aranka added comment 01/18/02
685 THEN
686 AMS_Utility_PVT.error_message('AMS_CAMP_UPDT_OWNER_PERM');
687 x_return_status := FND_API.g_ret_sts_error;
688 END IF;
689
690
691 IF (AMS_DEBUG_HIGH_ON) THEN
692
693
694
695
696
697 AMS_Utility_PVT.Debug_message('Resource id : '||l_resource ||' Owner : '||p_camp_rec.owner_user_id) ;
698
699
700 END IF;
701 -- Only owner/ Super Admin can change the Business Unit
702 IF p_camp_rec.business_unit_id <> FND_API.g_miss_num
703 AND p_camp_rec.business_unit_id <> l_camp_rec.business_unit_id
704 AND l_admin_user = FALSE
705 AND l_owner <> l_resource
706 THEN
707 AMS_Utility_PVT.error_message('AMS_CAMP_UPDT_BUS_UNIT_PERM');
708 x_return_status := FND_API.g_ret_sts_error;
709 END IF;
710
711 -- cannot update template_flag if child campaigns exist
712 IF p_camp_rec.template_flag <> FND_API.g_miss_char
713 AND p_camp_rec.template_flag <> l_camp_rec.template_flag
714 THEN
715 OPEN c_child;
716 FETCH c_child INTO l_dummy;
717 IF c_child%FOUND THEN
718 AMS_Utility_PVT.error_message('AMS_CAMP_UPDT_TMPL_FLAG');
719 x_return_status := FND_API.g_ret_sts_error;
720 END IF;
721 CLOSE c_child;
722 END IF;
723
724 -- 07/15/00 holiu:
725 -- remove as template campaigns will have status changes
726 -- template campaigns won't have any status changes
727 --IF p_camp_rec.user_status_id <> FND_API.g_miss_num
728 -- AND p_camp_rec.user_status_id <> l_camp_rec.user_status_id
729 -- AND l_camp_rec.template_flag = 'Y'
730 --THEN
731 -- AMS_Utility_PVT.error_message('AMS_CAMP_UPDATE_TEMP_STATUS');
732 -- x_return_status := FND_API.g_ret_sts_error;
733 -- RETURN;
734 --END IF;
735
736 -- aranka added 05/10/02
737 -- the following will be locked after available
738 -- IF l_camp_rec.status_code <> 'NEW'
739 -- THEN
740 -- IF p_camp_rec.campaign_name <> FND_API.g_miss_char
741 -- AND p_camp_rec.campaign_name <> l_camp_rec.campaign_name
742 -- THEN
743 -- AMS_Utility_PVT.error_message('AMS_CAMP_UPDATE_CAMPAIGN_NAME');
744 -- x_return_status := FND_API.g_ret_sts_error;
745 -- END IF;
746
747 --IF p_camp_rec.channel_id <> FND_API.g_miss_num
748 -- AND p_camp_rec.channel_id <> l_camp_rec.channel_id
749 --THEN
750 -- AMS_Utility_PVT.error_message('AMS_CAMP_UPDATE_CHANNEL');
751 -- x_return_status := FND_API.g_ret_sts_error;
752 --END IF;
753
754 -- IF p_camp_rec.actual_exec_start_date <> FND_API.g_miss_date
755 -- AND p_camp_rec.actual_exec_start_date <> l_camp_rec.actual_exec_start_date
756 -- AND (p_camp_rec.actual_exec_start_date IS NOT NULL
757 -- OR l_camp_rec.actual_exec_start_date IS NOT NULL)
758 -- THEN
759 -- AMS_Utility_PVT.error_message('AMS_CAMP_UPDATE_START_DATE');
760 -- x_return_status := FND_API.g_ret_sts_error;
761 -- END IF;
762
763 --IF p_camp_rec.actual_exec_end_date <> FND_API.g_miss_date
764 -- AND p_camp_rec.actual_exec_end_date <> l_camp_rec.actual_exec_end_date
765 -- AND (p_camp_rec.actual_exec_end_date IS NOT NULL
766 -- OR l_camp_rec.actual_exec_end_date IS NOT NULL)
767 --THEN
768 -- AMS_Utility_PVT.error_message('AMS_CAMP_UPDATE_END_DATE');
769 -- x_return_status := FND_API.g_ret_sts_error;
770 --END IF;
771 -- END IF;
772
773 END Check_Camp_Update;
774
775
776 --======================================================================
777 -- PROCEDURE
778 -- check_camp_template_flag
779 --
780 -- PURPOSE
781 -- 1. Created to check the template flag for campaigns
782 -- 2. Check if the marketing medium is assigned to the campaign
783 -- before it goes active.
784 --
785 -- HISTORY
786 -- 07/15/00 holiu Created.
787 --======================================================================
788 PROCEDURE Check_Camp_Template_Flag(
789 p_parent_id IN NUMBER,
790 p_channel_id IN NUMBER,
791 p_template_flag IN VARCHAR2,
792 p_status_code IN VARCHAR2,
793 p_rollup_type IN VARCHAR2,
794 p_media_type IN VARCHAR2,
795 x_return_status OUT NOCOPY VARCHAR2
796 )
797 IS
798
799 l_template_flag VARCHAR2(1);
800
801 CURSOR c_parent IS
802 SELECT template_flag
803 FROM ams_campaigns_vl
804 WHERE campaign_id = p_parent_id;
805
806 BEGIN
807
808 x_return_status := FND_API.g_ret_sts_success;
809
810 -- Commented by ptendulk as Channels will be attached at the schedule level
811 -- channel is required before submitted for non-template campaigns
812 --IF p_template_flag = 'N'
813 -- AND p_rollup_type = 'ECAM'
814 -- AND p_channel_id IS NULL
815 -- AND p_status_code IN ('SUBMITTED_TA', 'PLANNING', 'SUBMITTED_BA', 'AVAILABLE', 'ACTIVE')
816 --THEN
817 -- IF p_media_type = 'EVENTS' THEN
818 -- AMS_Utility_PVT.error_message('AMS_CAMP_EVENT_REQUIRED');
819 -- ELSE
820 -- AMS_Utility_PVT.error_message('AMS_CAMP_CHANNEL_REQUIRED');
821 -- END IF;
822 -- x_return_status := FND_API.g_ret_sts_error;
823 -- RETURN;
824 --END IF;
825
826 -- check parent campaign
827 IF p_parent_id IS NOT NULL THEN
828 OPEN c_parent;
829 FETCH c_parent INTO l_template_flag;
830 CLOSE c_parent;
831
832 IF l_template_flag <> p_template_flag THEN
833 AMS_Utility_PVT.error_message('AMS_CAMP_ASSOC_TEMPLATE');
834 x_return_status := FND_API.g_ret_sts_error;
835 RETURN;
836 END IF;
837 END IF;
838
839 END check_camp_template_flag;
840
841
842 -----------------------------------------------------------------------
843 -- PROCEDURE
844 -- check_camp_media_type
845 --
846 -- HISTORY
847 -- 11/01/99 holiu Created.
848 -- 02/07/00 holiu Disable share media type checking.
849 -- 07/14/00 holiu Both EVEH and EVEO can be channels.
850 -- 07/15/00 holiu Channel is no longer required for ECAM.
851 --
852 -----------------------------------------------------------------------
853 PROCEDURE check_camp_media_type(
854 p_campaign_id IN NUMBER,
855 p_parent_id IN NUMBER,
856 p_rollup_type IN VARCHAR2,
857 p_media_type IN VARCHAR2,
858 p_media_id IN NUMBER,
859 p_channel_id IN NUMBER,
860 p_event_type IN VARCHAR2,
861 p_arc_channel_from IN VARCHAR2,
862 x_return_status OUT NOCOPY VARCHAR2
863 )
864 IS
865
866 l_type VARCHAR2(30);
867 l_dummy NUMBER;
868
869 CURSOR c_media IS
870 SELECT media_type_code
871 FROM ams_media_vl
872 WHERE media_id = p_media_id
873 AND enabled_flag = 'Y';
874
875 CURSOR c_channel_media IS
876 SELECT 1
877 FROM ams_media_channels
878 WHERE channel_id = p_channel_id
879 AND media_id = p_media_id;
880
881 CURSOR c_eveh IS
882 SELECT event_type_code
883 FROM ams_event_headers_vl
884 WHERE event_header_id = p_channel_id;
885
886 CURSOR c_eveo IS
887 SELECT event_type_code
888 FROM ams_event_offers_vl
889 WHERE event_offer_id = p_channel_id;
890
891 CURSOR c_camp_event IS
892 SELECT 1
893 FROM DUAL
894 WHERE EXISTS(
895 SELECT campaign_id
896 FROM ams_campaigns_vl
897 WHERE media_type_code = 'EVENTS'
898 AND arc_channel_from = p_arc_channel_from
899 AND channel_id = p_channel_id
900 AND (campaign_id <> p_campaign_id OR p_campaign_id IS NULL));
901
902 -- Following line(Was the last line of the above cursor) is commented by ptendulk
903 -- on 14 Aug 2000 Ref Bug : 1378977
904 --
905 -- AND (campaign_id = p_campaign_id OR p_campaign_id IS NULL));
906
907 BEGIN
908
909 x_return_status := FND_API.g_ret_sts_success;
910
911 -- for execution campaigns, media_type and media are required
912 IF p_rollup_type = 'ECAM' THEN
913 IF p_media_type IS NULL THEN
914 AMS_Utility_PVT.error_message('AMS_CAMP_EC_NO_MEDIA_TYPE');
915 x_return_status := FND_API.g_ret_sts_error;
916 RETURN;
917 END IF;
918
919 IF p_media_type <> 'EVENTS' AND p_media_id IS NULL THEN
920 AMS_Utility_PVT.error_message('AMS_CAMP_EC_NO_MEDIA');
921 x_return_status := FND_API.g_ret_sts_error;
922 RETURN;
923 END IF;
924
925 IF p_media_type = 'EVENTS' AND p_event_type IS NULL THEN
926 AMS_Utility_PVT.error_message('AMS_CAMP_EC_NO_EVENT_TYPE');
927 x_return_status := FND_API.g_ret_sts_error;
928 RETURN;
929 END IF;
930
931 -- 07/15/00 holiu: remove as channel is no longer required
932 --IF p_channel_id IS NULL THEN
933 -- AMS_Utility_PVT.error_message('AMS_CAMP_EC_NO_CHANNEL');
934 -- x_return_status := FND_API.g_ret_sts_error;
935 -- RETURN;
936 --END IF;
937 END IF;
938
939 ---- all children under the same rollup campaign share the same media type
940 --l_type := get_parent_media_type(p_parent_id);
941 --IF p_media_type <> l_type THEN
942 -- x_return_status := FND_API.g_ret_sts_error;
943 -- AMS_Utility_PVT.error_message('AMS_CAMP_SHARE_MEDIA_TYPE');
944 -- RETURN;
945 --END IF;
946
947 -- validate media_id
948 IF p_media_id IS NOT NULL THEN
949 OPEN c_media;
950 FETCH c_media INTO l_type;
951 CLOSE c_media;
952
953 IF l_type <> p_media_type THEN
954 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_MEDIA_ID');
955 x_return_status := FND_API.g_ret_sts_error;
956 RETURN;
957 END IF;
958 END IF;
959
960 -- validate media channel id
961 IF p_media_type <> 'EVENTS'AND p_channel_id IS NOT NULL THEN
962 OPEN c_channel_media;
963 FETCH c_channel_media INTO l_dummy;
964 CLOSE c_channel_media;
965
966 IF l_dummy IS NULL OR p_media_id IS NULL THEN
967 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_CHANNEL');
968 x_return_status := FND_API.g_ret_sts_error;
969 RETURN;
970 END IF;
971 END IF;
972
973 -- validate event channel id
974 IF p_media_type = 'EVENTS' AND p_channel_id IS NOT NULL THEN
975 IF p_arc_channel_from = 'EVEO' THEN
976 OPEN c_eveo;
977 FETCH c_eveo INTO l_type;
978 IF c_eveo%NOTFOUND OR l_type <> p_event_type THEN
979 x_return_status := FND_API.g_ret_sts_error;
980 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_CHANNEL');
981 END IF;
982 CLOSE c_eveo;
983 ELSIF p_arc_channel_from = 'EVEH' THEN
984 OPEN c_eveh;
985 FETCH c_eveh INTO l_type;
986 IF c_eveh%NOTFOUND OR l_type <> p_event_type THEN
987 x_return_status := FND_API.g_ret_sts_error;
988 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_CHANNEL');
989 END IF;
990 CLOSE c_eveh;
991 ELSE
992 x_return_status := FND_API.g_ret_sts_error;
993 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_ARC_CHANNEL');
994 END IF;
995
996 -- event associated to a campaign cannot be associated to other campaigns
997 OPEN c_camp_event;
998 FETCH c_camp_event INTO l_dummy;
999 IF c_camp_event%FOUND THEN
1000 x_return_status := FND_API.g_ret_sts_error;
1001 AMS_Utility_PVT.error_message('AMS_CAMP_EVENT_IN_USE');
1002 END IF;
1003 CLOSE c_camp_event;
1004 END IF;
1005
1006 END check_camp_media_type;
1007
1008
1009 ---------------------------------------------------------------------
1010 -- PROCEDURE
1011 -- check_camp_fund_source
1012 --
1013 -- HISTORY
1014 -- 11/01/99 holiu Created.
1015 ---------------------------------------------------------------------
1016 PROCEDURE check_camp_fund_source(
1017 p_fund_source_type IN VARCHAR2,
1018 p_fund_source_id IN NUMBER,
1019 x_return_status OUT NOCOPY VARCHAR2
1020 )
1021 IS
1022
1023 l_dummy NUMBER;
1024
1025 CURSOR c_camp IS
1026 SELECT 1
1027 FROM ams_campaigns_vl
1028 WHERE campaign_id = p_fund_source_id;
1029
1030 CURSOR c_eveh IS
1031 SELECT 1
1032 FROM ams_event_headers_vl
1033 WHERE event_header_id = p_fund_source_id;
1034
1035 CURSOR c_eveo IS
1036 SELECT 1
1037 FROM ams_event_offers_vl
1038 WHERE event_offer_id = p_fund_source_id;
1039
1040 BEGIN
1041
1042 x_return_status := FND_API.g_ret_sts_success;
1043 IF p_fund_source_type IS NULL AND p_fund_source_id IS NULL THEN
1044 RETURN;
1045 ELSIF p_fund_source_type IS NULL AND p_fund_source_id IS NOT NULL THEN
1046 x_return_status := FND_API.g_ret_sts_error;
1047 AMS_Utility_PVT.error_message('AMS_CAMP_NO_FUND_SOURCE_TYPE');
1048 RETURN;
1049 END IF;
1050
1051 IF p_fund_source_type = 'FUND' THEN
1052 NULL;
1053 ELSIF p_fund_source_type = 'CAMP' THEN
1054 IF p_fund_source_id IS NOT NULL THEN
1055 OPEN c_camp;
1056 FETCH c_camp INTO l_dummy;
1057 IF c_camp%NOTFOUND THEN
1058 x_return_status := FND_API.g_ret_sts_error;
1059 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_FUND_SOURCE_ID');
1060 END IF;
1061 CLOSE c_camp;
1062 END IF;
1063 ELSIF p_fund_source_type = 'EVEH' THEN
1064 IF p_fund_source_id IS NOT NULL THEN
1065 OPEN c_eveh;
1066 FETCH c_eveh INTO l_dummy;
1067 IF c_eveh%NOTFOUND THEN
1068 x_return_status := FND_API.g_ret_sts_error;
1069 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_FUND_SOURCE_ID');
1070 END IF;
1071 CLOSE c_eveh;
1072 END IF;
1073 ELSIF p_fund_source_type = 'EVEO' THEN
1074 IF p_fund_source_id IS NOT NULL THEN
1075 OPEN c_eveo;
1076 FETCH c_eveo INTO l_dummy;
1077 IF c_eveo%NOTFOUND THEN
1078 x_return_status := FND_API.g_ret_sts_error;
1079 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_FUND_SOURCE_ID');
1080 END IF;
1081 CLOSE c_eveo;
1082 END IF;
1083 ELSE
1084 x_return_status := FND_API.g_ret_sts_error;
1085 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_FUND_SOURCE_TYPE');
1086 END IF;
1087
1088 END check_camp_fund_source;
1089
1090
1091 -----------------------------------------------------------------------
1092 -- PROCEDURE
1093 -- check_camp_calendar
1094 --
1095 -- HISTORY
1096 -- 06/21/00 holiu Created.
1097 -----------------------------------------------------------------------
1098 PROCEDURE check_camp_calendar(
1099 p_campaign_calendar IN VARCHAR2,
1100 p_start_period_name IN VARCHAR2,
1101 p_end_period_name IN VARCHAR2,
1102 p_start_date IN DATE,
1103 p_end_date IN DATE,
1104 x_return_status OUT NOCOPY VARCHAR2
1105 )
1106 IS
1107
1108 l_start_start DATE;
1109 l_start_end DATE;
1110 l_end_start DATE;
1111 l_end_end DATE;
1112 l_dummy NUMBER;
1113
1114 CURSOR c_campaign_calendar IS
1115 SELECT 1
1116 FROM DUAL
1117 WHERE EXISTS(
1118 SELECT 1
1119 FROM gl_periods_v
1120 WHERE period_set_name = p_campaign_calendar
1121 );
1122
1123 CURSOR c_start_period IS
1124 SELECT start_date, end_date
1125 FROM gl_periods_v
1126 WHERE period_set_name = p_campaign_calendar
1127 AND period_name = p_start_period_name;
1128
1129 CURSOR c_end_period IS
1130 SELECT start_date, end_date
1131 FROM gl_periods_v
1132 WHERE period_set_name = p_campaign_calendar
1133 AND period_name = p_end_period_name;
1134
1135 BEGIN
1136
1137 x_return_status := FND_API.g_ret_sts_success;
1138
1139 -- check if p_campaign_calendar is null
1140 IF p_campaign_calendar IS NULL
1141 AND p_start_period_name IS NULL
1142 AND p_end_period_name IS NULL
1143 THEN
1144 RETURN;
1145 ELSIF p_campaign_calendar IS NULL THEN
1146 x_return_status := FND_API.g_ret_sts_error;
1147 AMS_Utility_PVT.error_message('AMS_CAMP_NO_CAMPAIGN_CALENDAR');
1148 RETURN;
1149 END IF;
1150
1151 IF p_start_date > p_end_date THEN
1152 x_return_status := FND_API.g_ret_sts_error;
1153 AMS_Utility_PVT.error_message('AMS_CAMP_INVALID_DATE');
1154 RETURN;
1155 END IF ;
1156
1157
1158 -- check if p_campaign_calendar is valid
1159 OPEN c_campaign_calendar;
1160 FETCH c_campaign_calendar INTO l_dummy;
1161 CLOSE c_campaign_calendar;
1162
1163 IF l_dummy IS NULL THEN
1164 x_return_status := FND_API.g_ret_sts_error;
1165 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_CAMPAIGN_CALENDAR');
1166 RETURN;
1167 END IF;
1168
1169 -- check p_start_period_name
1170 IF p_start_period_name IS NOT NULL THEN
1171 OPEN c_start_period;
1172 FETCH c_start_period INTO l_start_start, l_start_end;
1173 CLOSE c_start_period;
1174
1175 IF l_start_start IS NULL THEN
1176 x_return_status := FND_API.g_ret_sts_error;
1177 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_START_PERIOD');
1178 RETURN;
1179 ELSIF p_start_date < l_start_start OR p_start_date > l_start_end THEN
1180 x_return_status := FND_API.g_ret_sts_error;
1181 AMS_Utility_PVT.error_message('AMS_CAMP_OUT_START_PERIOD');
1182 RETURN;
1183 END IF;
1184 END IF;
1185
1186 -- check p_end_period_name
1187 IF p_end_period_name IS NOT NULL THEN
1188 OPEN c_end_period;
1189 FETCH c_end_period INTO l_end_start, l_end_end;
1190 CLOSE c_end_period;
1191
1192 IF l_end_end IS NULL THEN
1193 x_return_status := FND_API.g_ret_sts_error;
1194 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_END_PERIOD');
1195 RETURN;
1196 ELSIF p_end_date < l_end_start OR p_end_date > l_end_end THEN
1197 x_return_status := FND_API.g_ret_sts_error;
1198 AMS_Utility_PVT.error_message('AMS_CAMP_OUT_END_PERIOD');
1199 RETURN;
1200 END IF;
1201 END IF;
1202
1203 -- compare the start date and the end date
1204 IF l_start_start > l_end_end THEN
1205 x_return_status := FND_API.g_ret_sts_error;
1206 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_PERIODS');
1207 END IF;
1208
1209 END check_camp_calendar;
1210
1211
1212 -----------------------------------------------------------------------
1213 -- PROCEDURE
1214 -- check_camp_version
1215 --
1216 -- HISTORY
1217 -- 06/22/00 holiu Created.
1218 -- 20-Jun-2001 ptendulk Modified the c_displayed cursor, as There
1219 -- will be only one campaign by the name and
1220 -- version. You can plan on new campaign with same
1221 -- name on later date but you can not create
1222 -- new campaign of same name as another campaign
1223 -- which is not active or cancelled or archived.
1224 -- 30-Jul-2001 ptendulk Removed the city check as the campaign
1225 -- name will be unique with version.
1226 -----------------------------------------------------------------------
1227 PROCEDURE check_camp_version(
1228 p_campaign_id IN NUMBER,
1229 p_campaign_name IN VARCHAR2,
1230 p_status_code IN VARCHAR2,
1231 p_start_date IN DATE,
1232 p_city_id IN NUMBER,
1233 p_version_no IN NUMBER,
1234 x_return_status OUT NOCOPY VARCHAR2
1235 )
1236 IS
1237
1238 l_active_end_date DATE;
1239 l_displayed NUMBER;
1240 l_duplicate NUMBER;
1241
1242 CURSOR c_active_end_date IS
1243 SELECT actual_exec_end_date
1244 FROM ams_campaigns_vl
1245 WHERE campaign_name = p_campaign_name
1246 -- AND (city_id = p_city_id OR city_id IS NULL AND p_city_id IS NULL)
1247 AND status_code = 'ACTIVE'
1248 --AND (campaign_id <> p_campaign_id OR p_campaign_id IS NULL);
1249 AND NVL(p_campaign_id,-20) <> campaign_id ;
1250
1251
1252 CURSOR c_displayed IS
1253 SELECT 1
1254 FROM DUAL
1255 WHERE EXISTS(
1256 SELECT 1
1257 FROM ams_campaigns_vl
1258 WHERE campaign_name = p_campaign_name
1259 -- AND (city_id = p_city_id OR city_id IS NULL AND p_city_id IS NULL)
1260 AND show_campaign_flag = 'Y'
1261 --AND actual_exec_end_date < SYSDATE
1262 AND (p_status_code <> 'CANCELLED' AND p_status_code <> 'ARCHIVED')
1263 AND NVL(p_campaign_id,-20) <> campaign_id
1264 --AND (campaign_id <> p_campaign_id OR p_campaign_id IS NULL)
1265 );
1266
1267 CURSOR c_duplicate IS
1268 SELECT 1
1269 FROM DUAL
1270 WHERE EXISTS(
1271 SELECT 1
1272 FROM ams_campaigns_vl
1273 WHERE campaign_name = p_campaign_name
1274 AND (city_id = p_city_id OR city_id IS NULL AND p_city_id IS NULL)
1275 -- 25-Aug-2005 mayjain version is no longer supported from R12
1276 --AND version_no = p_version_no
1277 AND (campaign_id <> p_campaign_id OR p_campaign_id IS NULL)
1278 );
1279
1280 BEGIN
1281
1282 x_return_status := FND_API.g_ret_sts_success;
1283
1284 OPEN c_active_end_date;
1285 FETCH c_active_end_date INTO l_active_end_date;
1286 CLOSE c_active_end_date;
1287
1288 IF l_active_end_date IS NULL THEN -- could be planning old one
1289 OPEN c_displayed;
1290 FETCH c_displayed INTO l_displayed;
1291 CLOSE c_displayed;
1292
1293 IF l_displayed IS NOT NULL THEN --still planning old one
1294 OPEN c_duplicate;
1295 FETCH c_duplicate INTO l_duplicate;
1296 CLOSE c_duplicate;
1297
1298 IF l_duplicate IS NOT NULL THEN --duplicate version
1299 x_return_status := FND_API.g_ret_sts_error;
1300 AMS_Utility_PVT.error_message('AMS_CAMP_DUPLICATE_VERSION');
1301 END IF;
1302 END IF;
1303 ELSE --plan new one
1304 IF p_status_code = 'ACTIVE' THEN
1305 x_return_status := FND_API.g_ret_sts_error;
1306 AMS_Utility_PVT.error_message('AMS_CAMP_PREV_STILL_ACTIVE');
1307 ELSIF p_start_date < l_active_end_date THEN
1308 x_return_status := FND_API.g_ret_sts_error;
1309 AMS_Utility_PVT.error_message('AMS_CAMP_START_BEF_PREV_END');
1310 END IF;
1311 END IF;
1312
1313 END check_camp_version;
1314
1315
1316
1317 ---------------------------------------------------------------------
1318 -- PROCEDURE
1319 -- check_camp_status_vs_parent
1320 --
1321 -- HISTORY
1322 -- 04/17/2002 aranka Created.
1323 ---------------------------------------------------------------------
1324 PROCEDURE check_camp_status_vs_parent(
1325 p_parent_id IN NUMBER,
1326 p_status_code IN VARCHAR2,
1327 x_return_status OUT NOCOPY VARCHAR2
1328 )
1329 IS
1330
1331 l_api_name CONSTANT VARCHAR2(30) := 'check_camp_status_vs_parent';
1332 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1333 l_parent_status_code VARCHAR2(30);
1334 l_old_status_id NUMBER;
1335
1336 /* Cursor to get the user status id of program */
1337 CURSOR c_PROGRAM_status IS
1338 SELECT user_status_id
1339 FROM ams_campaigns_all_b
1340 WHERE campaign_id = p_parent_id;
1341
1342 BEGIN
1343 IF p_parent_id IS NOT NULL then
1344 OPEN c_PROGRAM_status;
1345 FETCH c_PROGRAM_status INTO l_old_status_id;
1346 CLOSE c_PROGRAM_status;
1347 END IF;
1348
1349 l_parent_status_code := AMS_Utility_PVT.get_system_status_code(l_old_status_id);
1350
1351 If p_status_code = 'ACTIVE' and l_parent_status_code <> 'ACTIVE' THEN
1352 FND_MESSAGE.set_name('AMS', 'AMS_PROGRAM_NOT_ACTIVE');
1353 FND_MSG_PUB.add;
1354 RAISE FND_API.g_exc_error;
1355 END IF;
1356
1357 END check_camp_status_vs_parent;
1358
1359
1360 ---------------------------------------------------------------------
1361 -- PROCEDURE
1362 -- check_camp_dates_vs_parent
1363 --
1364 -- HISTORY
1365 -- 11/01/99 holiu Created.
1366 -- 23-May-2001 ptendulk Check for the Business unit of the parent if it is same.
1367 ---------------------------------------------------------------------
1368 PROCEDURE check_camp_dates_vs_parent(
1369 p_parent_id IN NUMBER,
1370 p_rollup_type IN VARCHAR2,
1371 p_start_date IN DATE,
1372 p_end_date IN DATE,
1373 x_return_status OUT NOCOPY VARCHAR2
1374 )
1375 IS
1376
1377 l_api_name CONSTANT VARCHAR2(30) := 'check_camp_dates_vs_parent';
1378 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1379
1380 CURSOR c_parent_camp IS
1381 SELECT actual_exec_start_date,
1382 actual_exec_end_date
1383 FROM ams_campaigns_vl
1384 WHERE campaign_id = p_parent_id;
1385
1386 l_parent_start_date DATE;
1387 l_parent_end_date DATE;
1388
1389 -- 09-Aug-2002 aranka
1390 l_msg_name VARCHAR2(40);
1391
1392 BEGIN
1393
1394 x_return_status := FND_API.g_ret_sts_success;
1395 IF p_parent_id IS NULL THEN
1396 RETURN;
1397 END IF;
1398
1399 OPEN c_parent_camp;
1400 FETCH c_parent_camp INTO l_parent_start_date, l_parent_end_date;
1401 IF c_parent_camp%NOTFOUND THEN
1402 CLOSE c_parent_camp;
1403 x_return_status := FND_API.g_ret_sts_error;
1404 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_PARENT_ID');
1405 RAISE FND_API.g_exc_error;
1406 END IF;
1407 CLOSE c_parent_camp;
1408
1409 -- aranka added 12/13/01 bug# 2146013 start
1410 ---------------------- start date ----------------------------
1411 IF p_start_date IS NOT NULL THEN
1412 IF l_parent_start_date IS NULL THEN
1413 x_return_status := FND_API.g_ret_sts_error;
1414 AMS_Utility_PVT.error_message('AMS_CAMP_PAR_START_IS_NULL');
1415 ELSIF p_start_date < l_parent_start_date THEN
1416 x_return_status := FND_API.g_ret_sts_error;
1417 -- AMS_Utility_PVT.error_message('AMS_CAMP_START_BEF_PAR_START');
1418 -- 09-Aug-2002 aranka
1419 l_msg_name := 'AMS_CAMP_START_BEF_PAR_START';
1420 IF ( p_rollup_type = 'RCAM') THEN
1421 l_msg_name := 'AMS_RCAM_START_BEF_PAR_START';
1422 END IF;
1423
1424 AMS_Utility_PVT.error_message(
1425 l_msg_name,
1426 'CAMP_START_DATE_AFTR',
1427 FND_DATE.date_to_chardate(l_parent_start_date)
1428 );
1429
1430 ELSIF (l_parent_end_date IS NOT NULL AND p_start_date > l_parent_end_date) THEN
1431 x_return_status := FND_API.g_ret_sts_error;
1432 -- AMS_Utility_PVT.error_message('AMS_CAMP_START_AFT_PAR_END');
1433
1434 l_msg_name := 'AMS_CAMP_START_AFT_PAR_END';
1435 IF ( p_rollup_type = 'RCAM') THEN
1436 l_msg_name := 'AMS_RCAM_START_AFT_PAR_END';
1437 END IF;
1438
1439 AMS_Utility_PVT.error_message(
1440 l_msg_name,
1441 'CAMP_START_DATE_BFR',
1442 -- aranka added 04/01/02
1443 -- FND_DATE.date_to_chardate(l_parent_start_date)
1444 FND_DATE.date_to_chardate(l_parent_end_date)
1445 );
1446
1447 END IF;
1448 END IF;
1449
1450 ---------------------- end date ------------------------------
1451 IF p_end_date IS NOT NULL THEN
1452 IF l_parent_end_date IS NULL THEN
1453 RETURN ; -- As Program End date can be null
1454 -- x_return_status := FND_API.g_ret_sts_error;
1455 -- AMS_Utility_PVT.error_message('AMS_CAMP_PAR_END_IS_NULL');
1456 ELSIF p_end_date > l_parent_end_date THEN
1457 x_return_status := FND_API.g_ret_sts_error;
1458 -- AMS_Utility_PVT.error_message('AMS_CAMP_END_AFT_PAR_END');
1459 -- 09-Aug-2002 aranka
1460 l_msg_name := 'AMS_CAMP_END_AFT_PAR_END';
1461 IF ( p_rollup_type = 'RCAM') THEN
1462 l_msg_name := 'AMS_RCAM_END_AFT_PAR_END';
1463 END IF;
1464
1465 AMS_Utility_PVT.error_message(
1466 l_msg_name,
1467 'CAMP_END_DATE_BFR',
1468 -- aranka added 04/01/02
1469 -- FND_DATE.date_to_chardate(l_parent_start_date)
1470 FND_DATE.date_to_chardate(l_parent_end_date)
1471 );
1472 ELSIF p_end_date < l_parent_start_date THEN
1473 x_return_status := FND_API.g_ret_sts_error;
1474 -- AMS_Utility_PVT.error_message('AMS_CAMP_END_BEF_PAR_START');
1475
1476 l_msg_name := 'AMS_CAMP_END_BEF_PAR_START';
1477 IF ( p_rollup_type = 'RCAM') THEN
1478 l_msg_name := 'AMS_RCAM_END_BEF_PAR_START';
1479 END IF;
1480
1481
1482 AMS_Utility_PVT.error_message(
1483 l_msg_name,
1484 'CAMP_END_DATE_AFTR',
1485 FND_DATE.date_to_chardate(l_parent_start_date)
1486 );
1487 END IF;
1488 END IF;
1489
1490 -- aranka added 12/13/01 bug# 2146013 end
1491
1492 END check_camp_dates_vs_parent;
1493
1494
1495 ---------------------------------------------------------------------
1496 -- PROCEDURE
1497 -- check_camp_dates_vs_child
1498 --
1499 -- HISTORY
1500 -- 11/01/99 holiu Created.
1501 ---------------------------------------------------------------------
1502 PROCEDURE check_camp_dates_vs_child(
1503 p_camp_id IN NUMBER,
1504 p_start_date IN DATE,
1505 p_end_date IN DATE,
1506 x_return_status OUT NOCOPY VARCHAR2
1507 )
1508 IS
1509
1510 l_api_name CONSTANT VARCHAR2(30) := 'check_camp_dates_vs_child';
1511 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1512
1513 CURSOR c_sub_camp IS
1514 SELECT campaign_name AS campaign_name,
1515 actual_exec_start_date AS start_date,
1516 actual_exec_end_date AS end_date
1517 FROM ams_campaigns_vl
1518 WHERE parent_campaign_id = p_camp_id;
1519
1520 BEGIN
1521
1522 x_return_status := FND_API.g_ret_sts_success;
1523 IF p_camp_id IS NULL THEN
1524 RETURN;
1525 END IF;
1526
1527 FOR l_sub_rec IN c_sub_camp LOOP
1528 IF p_start_date IS NULL AND l_sub_rec.start_date IS NOT NULL THEN
1529 x_return_status := FND_API.g_ret_sts_error;
1530 AMS_Utility_PVT.error_message(
1531 'AMS_CAMP_SUB_START_NOT_NULL',
1532 'CAMPAIGN_NAME',
1533 l_sub_rec.campaign_name
1534 );
1535 ELSIF p_start_date > l_sub_rec.start_date THEN
1536 x_return_status := FND_API.g_ret_sts_error;
1537 AMS_Utility_PVT.error_message(
1538 'AMS_CAMP_START_AFT_SUB_START',
1539 'CAMPAIGN_NAME',
1540 l_sub_rec.campaign_name
1541 );
1542 ELSIF (l_sub_rec.end_date IS NOT NULL AND p_start_date > l_sub_rec.end_date) THEN
1543 x_return_status := FND_API.g_ret_sts_error;
1544 AMS_Utility_PVT.error_message(
1545 'AMS_CAMP_START_AFT_SUB_END',
1546 'CAMPAIGN_NAME',
1547 l_sub_rec.campaign_name
1548 );
1549 END IF;
1550
1551 IF p_end_date IS NOT NULL AND l_sub_rec.end_date IS NULL THEN
1552 x_return_status := FND_API.g_ret_sts_error;
1553 AMS_Utility_PVT.error_message(
1554 'AMS_CAMP_SUB_END_NOT_NULL',
1555 'CAMPAIGN_NAME',
1556 l_sub_rec.campaign_name
1557 );
1558 ELSIF p_end_date < l_sub_rec.end_date THEN
1559 x_return_status := FND_API.g_ret_sts_error;
1560 AMS_Utility_PVT.error_message(
1561 'AMS_CAMP_END_BEF_SUB_END',
1562 'CAMPAIGN_NAME',
1563 l_sub_rec.campaign_name
1564 );
1565 ELSIF p_end_date < l_sub_rec.start_date THEN
1566 x_return_status := FND_API.g_ret_sts_error;
1567 AMS_Utility_PVT.error_message(
1568 'AMS_CAMP_END_BEF_SUB_START',
1569 'CAMPAIGN_NAME',
1570 l_sub_rec.campaign_name
1571 );
1572 END IF;
1573 END LOOP;
1574
1575 END check_camp_dates_vs_child;
1576
1577
1578
1579 --=====================================================================
1580 -- PROCEDURE
1581 -- Check_BU_Vs_Child
1582 --
1583 -- PURPOSE
1584 -- Check if the Business unit of children is same as that of parent
1585 --
1586 -- HISTORY
1587 -- 23-May-2001 ptendulk Created.
1588 --=====================================================================
1589 PROCEDURE Check_BU_Vs_Child(
1590 p_camp_id IN NUMBER,
1591 p_business_unit_id IN NUMBER,
1592 x_return_status OUT NOCOPY VARCHAR2
1593 )
1594 IS
1595
1596 l_api_name CONSTANT VARCHAR2(30) := 'Check_BU_Vs_Child';
1597 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1598
1599 CURSOR c_sub_camp IS
1600 SELECT campaign_name AS campaign_name,
1601 business_unit_id
1602 FROM ams_campaigns_vl
1603 WHERE parent_campaign_id = p_camp_id;
1604
1605 BEGIN
1606
1607 x_return_status := FND_API.g_ret_sts_success;
1608 IF p_camp_id IS NULL OR
1609 p_business_unit_id IS NULL THEN
1610 RETURN;
1611 END IF;
1612
1613 FOR l_sub_rec IN c_sub_camp LOOP
1614 IF l_sub_rec.business_unit_id IS NOT NULL
1615 AND p_business_unit_id <> l_sub_rec.business_unit_id
1616 THEN
1617 x_return_status := FND_API.g_ret_sts_error;
1618 AMS_Utility_PVT.error_message(
1619 'AMS_NOMATCH_CHILD_BU',
1620 'CAMPAIGN_NAME',
1621 l_sub_rec.campaign_name
1622 );
1623 END IF ;
1624 END LOOP ;
1625
1626
1627 END Check_BU_Vs_Child;
1628
1629 --==============================================================================
1630 -- PROCEDURE
1631 -- Check_BU_Vs_Parent
1632 --
1633 -- PURPOSE
1634 -- Check if the Business unit of campaign/program is same as that of parent
1635 --
1636 -- HISTORY
1637 -- 23-May-2001 ptendulk Created.
1638 --===============================================================================
1639 PROCEDURE Check_BU_Vs_Parent(
1640 p_program_id IN NUMBER,
1641 p_business_unit_id IN NUMBER,
1642 x_return_status OUT NOCOPY VARCHAR2
1643 )
1644 IS
1645
1646 l_api_name CONSTANT VARCHAR2(30) := 'Check_BU_Vs_Parent';
1647 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1648
1649 CURSOR c_parent_camp IS
1650 SELECT business_unit_id
1651 FROM ams_campaigns_all_b
1652 WHERE campaign_id = p_program_id;
1653
1654 l_business_unit_id NUMBER ;
1655 BEGIN
1656
1657 x_return_status := FND_API.g_ret_sts_success;
1658 IF p_program_id IS NULL OR
1659 p_business_unit_id IS NULL THEN
1660 RETURN;
1661 END IF;
1662
1663 OPEN c_parent_camp ;
1664 FETCH c_parent_camp INTO l_business_unit_id ;
1665 CLOSE c_parent_camp;
1666
1667 IF l_business_unit_id IS NOT NULL
1668 AND l_business_unit_id <> p_business_unit_id
1669 THEN
1670 AMS_Utility_PVT.Error_Message('AMS_NOMATCH_PARENT_BU');
1671 x_return_status := FND_API.g_ret_sts_error ;
1672 END IF ;
1673
1674 END Check_BU_Vs_Parent;
1675
1676 --=====================================================================
1677 -- PROCEDURE
1678 -- Check_Prog_Dates_Vs_Eveh
1679 --
1680 -- PURPOSE
1681 -- The api is created to check the dates of program vs dates of
1682 -- events. Events dates has to be between program dates.
1683 --
1684 -- HISTORY
1685 -- 07-Feb-2001 ptendulk Created.
1686 -- 26-Dec-2002 ptendulk Fixed bug 2685244, there was no validation before
1687 --=====================================================================
1688 PROCEDURE Check_Prog_Dates_Vs_Eveh(
1689 p_camp_id IN NUMBER,
1690 p_start_date IN DATE,
1691 p_end_date IN DATE,
1692 x_return_status OUT NOCOPY VARCHAR2
1693 )
1694 IS
1695
1696 l_api_name CONSTANT VARCHAR2(30) := 'Check_Prog_Dates_Vs_Eveh';
1697 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1698
1699 CURSOR c_sub_eveh IS
1700 SELECT event_header_name AS event_name,
1701 active_from_date AS start_date,
1702 active_to_date AS end_date
1703 FROM ams_event_headers_vl
1704 WHERE program_id = p_camp_id;
1705
1706 CURSOR c_sub_eone IS
1707 SELECT event_offer_name AS event_name,
1708 event_start_date AS start_date,
1709 event_end_date AS end_date
1710 FROM ams_event_offers_vl
1711 WHERE parent_type = 'RCAM'
1712 AND parent_id = p_camp_id
1713 AND event_standalone_flag = 'Y' ;
1714
1715
1716 BEGIN
1717
1718 x_return_status := FND_API.g_ret_sts_success;
1719
1720 IF p_camp_id IS NULL THEN
1721 RETURN;
1722 END IF;
1723
1724 FOR l_sub_rec IN c_sub_eveh LOOP
1725 IF p_start_date IS NULL AND l_sub_rec.start_date IS NOT NULL THEN
1726 x_return_status := FND_API.g_ret_sts_error;
1727 AMS_Utility_PVT.error_message(
1728 'AMS_EVEH_SUB_START_NOT_NULL',
1729 'EVENT_NAME',
1730 l_sub_rec.event_name
1731 );
1732 ELSIF p_start_date > l_sub_rec.start_date THEN
1733 x_return_status := FND_API.g_ret_sts_error;
1734 AMS_Utility_PVT.error_message(
1735 'AMS_EVEH_START_AFT_SUB_START',
1736 'EVENT_NAME',
1737 l_sub_rec.event_name
1738 );
1739 ELSIF (l_sub_rec.end_date IS NOT NULL AND p_start_date > l_sub_rec.end_date) THEN
1740 x_return_status := FND_API.g_ret_sts_error;
1741 AMS_Utility_PVT.error_message(
1742 'AMS_EVEH_START_AFT_SUB_END',
1743 'EVENT_NAME',
1744 l_sub_rec.event_name
1745 );
1746 END IF;
1747
1748 IF p_end_date IS NOT NULL AND l_sub_rec.end_date IS NULL THEN
1749 x_return_status := FND_API.g_ret_sts_error;
1750 AMS_Utility_PVT.error_message(
1751 'AMS_EVEH_SUB_END_NOT_NULL',
1752 'EVENT_NAME',
1753 l_sub_rec.event_name
1754 );
1755 ELSIF p_end_date < l_sub_rec.end_date THEN
1756 x_return_status := FND_API.g_ret_sts_error;
1757 AMS_Utility_PVT.error_message(
1758 'AMS_EVEH_END_BEF_SUB_END',
1759 'EVENT_NAME',
1760 l_sub_rec.event_name
1761 );
1762 ELSIF p_end_date < l_sub_rec.start_date THEN
1763 x_return_status := FND_API.g_ret_sts_error;
1764 AMS_Utility_PVT.error_message(
1765 'AMS_EVEH_END_BEF_SUB_START',
1766 'EVENT_NAME',
1767 l_sub_rec.event_name
1768 );
1769 END IF;
1770 END LOOP;
1771
1772 FOR l_sub_eone_rec IN c_sub_eone LOOP
1773 IF p_start_date IS NULL AND l_sub_eone_rec.start_date IS NOT NULL THEN
1774 x_return_status := FND_API.g_ret_sts_error;
1775 AMS_Utility_PVT.error_message(
1776 'AMS_EONE_SUB_START_NOT_NULL',
1777 'EVENT_NAME',
1778 l_sub_eone_rec.event_name
1779 );
1780 ELSIF p_start_date > l_sub_eone_rec.start_date THEN
1781 x_return_status := FND_API.g_ret_sts_error;
1782 AMS_Utility_PVT.error_message(
1783 'AMS_EONE_START_AFT_SUB_START',
1784 'EVENT_NAME',
1785 l_sub_eone_rec.event_name
1786 );
1787 ELSIF (l_sub_eone_rec.end_date IS NOT NULL AND p_start_date > l_sub_eone_rec.end_date) THEN
1788 x_return_status := FND_API.g_ret_sts_error;
1789 AMS_Utility_PVT.error_message(
1790 'AMS_EONE_START_AFT_SUB_END',
1791 'EVENT_NAME',
1792 l_sub_eone_rec.event_name
1793 );
1794 END IF;
1795
1796 IF p_end_date IS NOT NULL AND l_sub_eone_rec.end_date IS NULL THEN
1797 x_return_status := FND_API.g_ret_sts_error;
1798 AMS_Utility_PVT.error_message(
1799 'AMS_EONE_SUB_END_NOT_NULL',
1800 'EVENT_NAME',
1801 l_sub_eone_rec.event_name
1802 );
1803 ELSIF p_end_date < l_sub_eone_rec.end_date THEN
1804 x_return_status := FND_API.g_ret_sts_error;
1805 AMS_Utility_PVT.error_message(
1806 'AMS_EONE_END_BEF_SUB_END',
1807 'EVENT_NAME',
1808 l_sub_eone_rec.event_name
1809 );
1810 ELSIF p_end_date < l_sub_eone_rec.start_date THEN
1811 x_return_status := FND_API.g_ret_sts_error;
1812 AMS_Utility_PVT.error_message(
1813 'AMS_EONE_END_BEF_SUB_START',
1814 'EVENT_NAME',
1815 l_sub_eone_rec.event_name
1816 );
1817 END IF;
1818 END LOOP;
1819
1820
1821 END Check_Prog_Dates_Vs_Eveh;
1822
1823 ---------------------------------------------------------------------
1824 -- PROCEDURE
1825 -- check_camp_dates_vs_csch
1826 --
1827 -- HISTORY
1828 -- 11/01/99 holiu Created.
1829 -- 25-May-2001 ptendulk Check only dates of Active schedules.
1830 ---------------------------------------------------------------------
1831 PROCEDURE check_camp_dates_vs_csch(
1832 p_camp_id IN NUMBER,
1833 p_start_date IN DATE,
1834 p_end_date IN DATE,
1835 x_return_status OUT NOCOPY VARCHAR2
1836 )
1837 IS
1838
1839 l_api_name CONSTANT VARCHAR2(30) := 'check_camp_dates_vs_csch';
1840 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1841
1842 CURSOR c_csch IS
1843 SELECT start_date_time AS start_date,
1844 end_date_time AS end_date
1845 FROM ams_campaign_schedules_b
1846 WHERE campaign_id = p_camp_id
1847 -- Following line of code is added by ptendulk on 25-May-2001
1848 AND active_flag = 'Y' ;
1849
1850 BEGIN
1851
1852 x_return_status := FND_API.g_ret_sts_success;
1853 IF p_camp_id IS NULL THEN
1854 RETURN;
1855 END IF;
1856
1857 FOR l_csch_rec IN c_csch LOOP
1858 IF p_start_date IS NULL AND l_csch_rec.start_date IS NOT NULL THEN
1859 x_return_status := FND_API.g_ret_sts_error;
1860 AMS_Utility_PVT.error_message('AMS_CAMP_CSCH_START_NOT_NULL');
1861 ELSIF p_start_date > l_csch_rec.start_date THEN
1862 x_return_status := FND_API.g_ret_sts_error;
1863 AMS_Utility_PVT.error_message(
1864 'AMS_CAMP_START_AFT_CSCH_START',
1865 'SCHEDULE_DATE',
1866 FND_DATE.date_to_chardate(l_csch_rec.start_date)
1867 );
1868 ELSIF l_csch_rec.end_date IS NOT NULL
1869 AND p_start_date > l_csch_rec.end_date THEN
1870 x_return_status := FND_API.g_ret_sts_error;
1871 AMS_Utility_PVT.error_message(
1872 'AMS_CAMP_START_AFT_CSCH_END',
1873 'SCHEDULE_DATE',
1874 FND_DATE.date_to_chardate(l_csch_rec.end_date)
1875 );
1876 END IF;
1877
1878 IF p_end_date IS NULL AND l_csch_rec.end_date IS NOT NULL THEN
1879 x_return_status := FND_API.g_ret_sts_error;
1880 AMS_Utility_PVT.error_message('AMS_CAMP_CSCH_END_NOT_NULL');
1881 ELSIF l_csch_rec.end_date IS NOT NULL
1882 AND p_end_date < l_csch_rec.end_date THEN
1883 x_return_status := FND_API.g_ret_sts_error;
1884 AMS_Utility_PVT.error_message(
1885 'AMS_CAMP_END_BEF_CSCH_END',
1886 'SCHEDULE_DATE',
1887 FND_DATE.date_to_chardate(l_csch_rec.end_date)
1888 );
1889 ELSIF p_end_date < l_csch_rec.start_date THEN
1890 x_return_status := FND_API.g_ret_sts_error;
1891 AMS_Utility_PVT.error_message(
1892 'AMS_CAMP_END_BEF_CSCH_START',
1893 'SCHEDULE_DATE',
1894 FND_DATE.date_to_chardate(l_csch_rec.start_date)
1895 );
1896 END IF;
1897 END LOOP;
1898
1899 END check_camp_dates_vs_csch;
1900
1901
1902 ---------------------------------------------------------------------
1903 -- PROCEDURE
1904 -- handle_csch_source_code
1905 --
1906 -- HISTORY
1907 -- 11/01/99 holiu Created.
1908 ---------------------------------------------------------------------
1909 PROCEDURE handle_csch_source_code(
1910 p_source_code IN VARCHAR2,
1911 p_camp_id IN NUMBER,
1912 x_cascade_flag OUT NOCOPY VARCHAR2,
1913 x_source_code OUT NOCOPY VARCHAR2,
1914 x_return_status OUT NOCOPY VARCHAR2
1915 )
1916 IS
1917
1918 CURSOR c_camp IS
1919 SELECT cascade_source_code_flag,
1920 source_code,
1921 custom_setup_id,
1922 global_flag
1923 FROM ams_campaigns_vl
1924 WHERE campaign_id = p_camp_id;
1925
1926 l_cascade_flag VARCHAR2(1);
1927 l_source_code VARCHAR2(30);
1928 l_setup_id NUMBER;
1929 l_global_flag VARCHAR2(1);
1930
1931 BEGIN
1932
1933 x_source_code := p_source_code;
1934 x_return_status := FND_API.g_ret_sts_success;
1935
1936 OPEN c_camp;
1937 FETCH c_camp INTO l_cascade_flag, l_source_code, l_setup_id, l_global_flag;
1938 IF c_camp%NOTFOUND THEN -- campaign_id is invalid
1939 CLOSE c_camp;
1940 x_return_status := FND_API.g_ret_sts_error;
1941 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_ID');
1942 RAISE FND_API.g_exc_error;
1943 END IF;
1944 CLOSE c_camp;
1945
1946 x_cascade_flag := l_cascade_flag;
1947
1948 IF l_cascade_flag = 'Y' THEN
1949 IF p_source_code IS NULL THEN
1950 x_source_code := l_source_code;
1951 ELSIF p_source_code <> l_source_code THEN
1952 x_return_status := FND_API.g_ret_sts_error;
1953 AMS_Utility_PVT.error_message('AMS_CSCH_CODE_NOT_CASCADE');
1954 END IF;
1955 ELSE
1956 IF p_source_code IS NULL THEN
1957 x_source_code := AMS_SourceCode_PVT.get_new_source_code(
1958 'CSCH', l_setup_id, l_global_flag);
1959 ELSIF AMS_SourceCode_PVT.is_source_code_unique(p_source_code) = FND_API.g_false
1960 THEN
1961 x_return_status := FND_API.g_ret_sts_error;
1962 AMS_Utility_PVT.error_message('AMS_CAMP_DUPLICATE_CODE');
1963 END IF;
1964 END IF;
1965
1966 END handle_csch_source_code;
1967
1968 -- 10/02/2002
1969 -- Commented this proc because this method is not being used any where and it refers to
1970 -- old AMS_CampaignSchedule_PVT which is no more there. Please refer Bug# 2605184
1971 ---------------------------------------------------------------------
1972 -- PROCEDURE
1973 -- check_csch_update
1974 --
1975 -- HISTORY
1976 -- 11/01/99 holiu Created.
1977 ---------------------------------------------------------------------
1978 -- PROCEDURE check_csch_update(
1979 -- p_csch_rec IN AMS_CampaignSchedule_PVT.csch_rec_type,
1980 -- x_return_status OUT VARCHAR2
1981 -- )
1982 -- IS
1983
1984 -- l_cascade_flag VARCHAR2(1);
1985 -- l_source_code VARCHAR2(30);
1986 -- l_camp_id NUMBER;
1987 -- l_dummy NUMBER;
1988 -- l_msg_count NUMBER;
1989 -- l_msg_data VARCHAR2(2000);
1990
1991 -- CURSOR c_source_code IS
1992 -- SELECT 1
1993 -- FROM ams_source_codes
1994 -- WHERE source_code = p_csch_rec.source_code
1995 -- AND active_flag = 'Y';
1996
1997 -- CURSOR c_csch IS
1998 -- SELECT campaign_id, source_code
1999 -- FROM ams_campaign_schedules
2000 -- WHERE campaign_schedule_id = p_csch_rec.campaign_schedule_id;
2001
2002 -- CURSOR c_camp IS
2003 -- SELECT cascade_source_code_flag
2004 -- FROM ams_campaigns_vl
2005 -- WHERE campaign_id = l_camp_id;
2006
2007 -- CURSOR c_list_header IS
2008 -- SELECT 1
2009 -- FROM ams_list_headers_all
2010 -- WHERE arc_list_used_by = 'CSCH'
2011 -- AND list_used_by_id = p_csch_rec.campaign_schedule_id
2012 -- AND status_code <> 'NEW';
2013
2014 -- BEGIN
2015
2016 -- x_return_status := FND_API.g_ret_sts_success;
2017
2018 -- -- cannot update to null
2019 -- IF p_csch_rec.source_code IS NULL THEN
2020 -- FND_MESSAGE.set_name('AMS', 'AMS_CAMP_NO_SOURCE_CODE');
2021 -- FND_MSG_PUB.add;
2022 -- END IF;
2023 --
2024 -- -- query the campaign_id and the old source_code
2025 -- OPEN c_csch;
2026 -- FETCH c_csch INTO l_camp_id, l_source_code;
2027 -- IF c_csch%NOTFOUND THEN
2028 -- CLOSE c_csch;
2029 -- IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2030 -- FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
2031 -- FND_MSG_PUB.add;
2032 -- END IF;
2033 -- RAISE FND_API.g_exc_error;
2034 -- END IF;
2035 -- CLOSE c_csch;
2036
2037 -- -- if source_code is not changed, return
2038 -- IF p_csch_rec.source_code = FND_API.g_miss_char
2039 -- OR p_csch_rec.source_code = l_source_code
2040 -- THEN
2041 -- RETURN;
2042 -- END IF;
2043
2044 -- -- check if source code is cascaded from campaign
2045 -- OPEN c_camp;
2046 -- FETCH c_camp INTO l_cascade_flag;
2047 -- CLOSE c_camp;
2048 -- IF l_cascade_flag = 'Y' THEN
2049 -- x_return_status := FND_API.g_ret_sts_error;
2050 -- AMS_Utility_PVT.error_message('AMS_CSCH_CODE_NOT_CASCADE');
2051 -- RETURN;
2052 -- END IF;
2053
2054 -- -- check if the new source code is unique
2055 -- OPEN c_source_code;
2056 -- FETCH c_source_code INTO l_dummy;
2057 -- CLOSE c_source_code;
2058 -- IF l_dummy IS NOT NULL THEN
2059 -- AMS_Utility_PVT.error_message('AMS_CAMP_DUPLICATE_CODE');
2060 -- x_return_status := FND_API.g_ret_sts_error;
2061 -- RETURN;
2062 -- END IF;
2063
2064 -- -- cannot update source code if schedule has "old" list headers
2065 -- OPEN c_list_header;
2066 -- FETCH c_list_header INTO l_dummy;
2067 -- CLOSE c_list_header;
2068 -- IF l_dummy IS NOT NULL THEN
2069 -- AMS_Utility_PVT.error_message('AMS_CSCH_UPDATE_SOURCE_CODE');
2070 -- x_return_status := FND_API.g_ret_sts_error;
2071 -- RETURN;
2072 -- END IF;
2073
2074 -- AMS_SourceCode_PVT.revoke_sourcecode(
2075 -- p_api_version => 1.0,
2076 -- p_init_msg_list => FND_API.g_false,
2077 -- p_commit => FND_API.g_false,
2078 -- p_validation_level => FND_API.g_valid_level_full,
2079
2080 -- x_return_status => x_return_status,
2081 -- x_msg_count => l_msg_count,
2082 -- x_msg_data => l_msg_data,
2083
2084 -- p_sourcecode => l_source_code
2085 -- );
2086
2087 -- IF x_return_status <> FND_API.g_ret_sts_success THEN
2088 -- RAISE FND_API.g_exc_error;
2089 -- END IF;
2090
2091 -- AMS_SourceCode_PVT.create_sourcecode(
2092 -- p_api_version => 1.0,
2093 -- p_init_msg_list => FND_API.g_false,
2094 -- p_commit => FND_API.g_false,
2095 -- p_validation_level => FND_API.g_valid_level_full,
2096
2097 -- x_return_status => x_return_status,
2098 -- x_msg_count => l_msg_count,
2099 -- x_msg_data => l_msg_data,
2100
2101 -- p_sourcecode => p_csch_rec.source_code,
2102 -- p_sourcecode_for => 'CSCH',
2103 -- p_sourcecode_for_id => p_csch_rec.campaign_schedule_id,
2104 -- x_sourcecode_id => l_dummy
2105 -- );
2106
2107 -- IF x_return_status <> FND_API.g_ret_sts_success THEN
2108 -- RAISE FND_API.g_exc_error;
2109 -- END IF;
2110
2111 -- END check_csch_update;
2112
2113
2114 ---------------------------------------------------------------------
2115 -- PROCEDURE
2116 -- check_csch_camp_id
2117 --
2118 -- HISTORY
2119 -- 11/01/99 holiu Created.
2120 ---------------------------------------------------------------------
2121 PROCEDURE check_csch_camp_id(
2122 p_camp_id IN NUMBER,
2123 x_return_status OUT NOCOPY VARCHAR2
2124 )
2125 IS
2126
2127 CURSOR c_camp IS
2128 SELECT rollup_type, media_type_code
2129 FROM ams_campaigns_vl
2130 WHERE campaign_id = p_camp_id
2131 AND active_flag = 'Y';
2132
2133 l_rollup_type VARCHAR2(30);
2134 l_media_type VARCHAR2(30);
2135
2136 BEGIN
2137
2138 x_return_status := FND_API.g_ret_sts_success;
2139 OPEN c_camp;
2140 FETCH c_camp INTO l_rollup_type, l_media_type;
2141 IF c_camp%NOTFOUND THEN
2142 x_return_status := FND_API.g_ret_sts_error;
2143 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_ID');
2144 ELSIF l_rollup_type <> 'ECAM' THEN
2145 x_return_status := FND_API.g_ret_sts_error;
2146 AMS_Utility_PVT.error_message('AMS_CSCH_NOT_EXEC_CAMP');
2147 ELSIF l_media_type = 'EVENTS' THEN
2148 x_return_status := FND_API.g_ret_sts_error;
2149 AMS_Utility_PVT.error_message('AMS_CSCH_MEDIA_IS_EVENT');
2150 END IF;
2151 CLOSE c_camp;
2152
2153 END check_csch_camp_id;
2154
2155
2156 ---------------------------------------------------------------------
2157 -- PROCEDURE
2158 -- check_csch_deliv_id
2159 --
2160 -- HISTORY
2161 -- 11/01/99 holiu Created.
2162 ---------------------------------------------------------------------
2163 PROCEDURE check_csch_deliv_id(
2164 p_deliv_id IN NUMBER,
2165 p_camp_id IN NUMBER,
2166 x_return_status OUT NOCOPY VARCHAR2
2167 )
2168 IS
2169
2170 l_dummy NUMBER;
2171
2172 CURSOR c_camp_deliv IS
2173 SELECT 1
2174 FROM ams_object_associations
2175 WHERE master_object_type = 'CAMP'
2176 AND master_object_id = p_camp_id
2177 AND using_object_type = 'DELV'
2178 AND using_object_id = p_deliv_id;
2179
2180 BEGIN
2181
2182 x_return_status := FND_API.g_ret_sts_success;
2183 IF p_deliv_id IS NULL OR p_deliv_id = FND_API.g_miss_num THEN
2184 RETURN;
2185 END IF;
2186
2187 OPEN c_camp_deliv;
2188 FETCH c_camp_deliv INTO l_dummy;
2189 IF c_camp_deliv%NOTFOUND THEN
2190 x_return_status := FND_API.g_ret_sts_error;
2191 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_DELIV_ID');
2192 END IF;
2193 CLOSE c_camp_deliv;
2194
2195 END check_csch_deliv_id;
2196
2197
2198 ---------------------------------------------------------------------
2199 -- PROCEDURE
2200 -- check_csch_offer_id
2201 --
2202 -- HISTORY
2203 -- 11/01/99 holiu Created.
2204 ---------------------------------------------------------------------
2205 PROCEDURE check_csch_offer_id(
2206 p_offer_id IN NUMBER,
2207 p_camp_id IN NUMBER,
2208 x_return_status OUT NOCOPY VARCHAR2
2209 )
2210 IS
2211
2212 l_dummy NUMBER;
2213
2214 CURSOR c_camp_offer IS
2215 SELECT 1
2216 FROM ams_act_offers
2217 WHERE activity_offer_id = p_offer_id
2218 AND arc_act_offer_used_by = 'CAMP'
2219 AND act_offer_used_by_id = p_camp_id;
2220
2221 BEGIN
2222
2223 x_return_status := FND_API.g_ret_sts_success;
2224 IF p_offer_id IS NULL OR p_offer_id = FND_API.g_miss_num THEN
2225 RETURN;
2226 END IF;
2227
2228 OPEN c_camp_offer;
2229 FETCH c_camp_offer INTO l_dummy;
2230 IF c_camp_offer%NOTFOUND THEN
2231 x_return_status := FND_API.g_ret_sts_error;
2232 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_OFFER_ID');
2233 END IF;
2234 CLOSE c_camp_offer;
2235
2236 END check_csch_offer_id;
2237
2238
2239 ---------------------------------------------------------------------
2240 -- PROCEDURE
2241 -- check_csch_dates_vs_camp
2242 --
2243 -- HISTORY
2244 -- 11/01/99 holiu Created.
2245 ---------------------------------------------------------------------
2246 PROCEDURE check_csch_dates_vs_camp(
2247 p_camp_id IN NUMBER,
2248 p_start_date IN DATE,
2249 p_end_date IN DATE,
2250 x_return_status OUT NOCOPY VARCHAR2
2251 )
2252 IS
2253
2254 l_api_name CONSTANT VARCHAR2(30) := 'check_csch_dates_vs_camp';
2255 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2256
2257 CURSOR c_camp IS
2258 SELECT actual_exec_start_date,
2259 actual_exec_end_date
2260 FROM ams_campaigns_vl
2261 WHERE campaign_id = p_camp_id;
2262
2263 l_camp_start_date DATE;
2264 l_camp_end_date DATE;
2265
2266 BEGIN
2267
2268 x_return_status := FND_API.g_ret_sts_success;
2269
2270 OPEN c_camp;
2271 FETCH c_camp INTO l_camp_start_date, l_camp_end_date;
2272 IF c_camp%NOTFOUND THEN
2273 CLOSE c_camp;
2274 x_return_status := FND_API.g_ret_sts_error;
2275 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_ID');
2276 RAISE FND_API.g_exc_error;
2277 END IF;
2278 CLOSE c_camp;
2279
2280 IF p_start_date IS NOT NULL THEN
2281 IF l_camp_start_date IS NULL THEN
2282 x_return_status := FND_API.g_ret_sts_error;
2283 AMS_Utility_PVT.error_message('AMS_CAMP_PAR_START_IS_NULL');
2284 ELSIF p_start_date < l_camp_start_date THEN
2285 x_return_status := FND_API.g_ret_sts_error;
2286 AMS_Utility_PVT.error_message('AMS_CSCH_START_BEF_CAMP_START');
2287 ELSIF p_start_date > l_camp_end_date THEN
2288 x_return_status := FND_API.g_ret_sts_error;
2289 AMS_Utility_PVT.error_message('AMS_CSCH_START_AFT_CAMP_END');
2290 END IF;
2291 END IF;
2292
2293 IF p_end_date IS NOT NULL THEN
2294 IF l_camp_end_date IS NULL THEN
2295 x_return_status := FND_API.g_ret_sts_error;
2296 AMS_Utility_PVT.error_message('AMS_CAMP_PAR_END_IS_NULL');
2297 ELSIF p_end_date > l_camp_end_date THEN
2298 x_return_status := FND_API.g_ret_sts_error;
2299 AMS_Utility_PVT.error_message('AMS_CSCH_END_AFT_CAMP_END');
2300 ELSIF p_end_date < l_camp_start_date THEN
2301 x_return_status := FND_API.g_ret_sts_error;
2302 AMS_Utility_PVT.error_message('AMS_CSCH_END_BEF_CAMP_START');
2303 END IF;
2304 END IF;
2305
2306 END check_csch_dates_vs_camp;
2307
2308
2309 ---------------------------------------------------------------------
2310 -- PROCEDURE
2311 -- activate_campaign
2312 --
2313 -- PURPOSE
2314 -- Perform the following tasks when campaigns become active:
2315 -- 1. Change the show_campaign_flag of all other versions to 'N'.
2316 --
2317 -- History
2318 -- 19-Jun-2001 ptendulk Added Where clause to Deactivate only same
2319 -- rollup type, ie. If the campaign get active,
2320 -- de activate only campaigns not program
2321 -- 27-Jun-2001 ptendulk Added code to deactivate the rollup if the
2322 -- campaign is getting inactive.
2323 -- 30-Jul-2001 ptendulk Commented city_id check
2324 -- 29-jan-2003 soagrawa Fixed bug# 2764007
2325 ---------------------------------------------------------------------
2326 PROCEDURE activate_campaign(
2327 p_campaign_id IN NUMBER
2328 )
2329 IS
2330 CURSOR c_camp_det IS
2331 SELECT A.campaign_id,DECODE(a.rollup_type,'RCAM','RCAM','CAMP')
2332 FROM ams_campaigns_vl A, ams_campaigns_vl B
2333 WHERE B.campaign_id = p_campaign_id
2334 AND A.campaign_name = B.campaign_name
2335 -- AND (A.city_id = B.city_id OR A.city_id IS NULL AND B.city_id IS NULL)
2336 AND A.show_campaign_flag = 'Y'
2337 AND A.campaign_id <> p_campaign_id
2338 -- Following line is added by ptendulk on 19-Jun-2001
2339 AND A.rollup_type = B.rollup_type
2340 AND a.parent_campaign_id IS NOT NULL ;
2341
2342 l_camp_id NUMBER ;
2343 l_rollup_type VARCHAR2(30);
2344 l_return_status VARCHAR2(30) ;
2345 l_msg_count NUMBER ;
2346 l_msg_data VARCHAR2(30) ;
2347 BEGIN
2348
2349 OPEN c_camp_det ;
2350 LOOP
2351 FETCH c_camp_det INTO l_camp_id, l_rollup_type ;
2352 EXIT WHEN c_camp_det%NOTFOUND ;
2353 -- Call the api to deactivate the parent
2354
2355 AMS_ACTMETRIC_PUB.Invalidate_Rollup(
2356 p_api_version => 1.0,
2357
2358 x_return_status => l_return_status,
2359 x_msg_count => l_msg_count,
2360 x_msg_data => l_msg_data,
2361
2362 -- p_used_by_type => 'CAMP',
2363 p_used_by_type => l_rollup_type,
2364 p_used_by_id => l_camp_id
2365 );
2366 END LOOP ;
2367 CLOSE c_camp_det ;
2368
2369 -- soagrawa 29-jan-2003 bug# 2764007
2370 IF l_rollup_type <> 'RCAM'
2371 THEN
2372 UPDATE ams_campaigns_all_b
2373 SET show_campaign_flag = 'N'
2374 WHERE campaign_id IN(
2375 SELECT A.campaign_id
2376 FROM ams_campaigns_vl A, ams_campaigns_vl B
2377 WHERE B.campaign_id = p_campaign_id
2378 AND A.campaign_name = B.campaign_name
2379 AND (A.city_id = B.city_id OR A.city_id IS NULL AND B.city_id IS NULL)
2380 AND A.show_campaign_flag = 'Y'
2381 AND A.campaign_id <> p_campaign_id
2382 -- Following line is added by ptendulk on 19-Jun-2001
2383 AND A.rollup_type = B.rollup_type
2384 );
2385 END IF;
2386
2387 END activate_campaign;
2388
2389
2390 -----------------------------------------------------------------------
2391 -- PROCEDURE
2392 -- udpate_camp_status
2393 --
2394 -- HISTORY
2395 -- 06/26/00 holiu Created.
2396 -- 05-Apr-2001 ptendulk Modified business Rules.
2397 -- 20-May-2001 ptendulk Pass RCAM to check_status_change proc for Programs
2398 -- Refer bug#1784156
2399 -- 16-Jun-2001 ptendulk Added call to new api check_new_status_change for
2400 -- approvals
2401 -- 24-Sep-2001 ptendulk Added the code to make private campaign public
2402 -- when the campaign goes active.
2403 -- 30-Oct-2001 ptendulk Modified after request from gjoby for 0
2404 -- budget approvals
2405 -- 25-Oct-2002 soagrawa Added code for automatic budget line approval enh# 2445453
2406 -----------------------------------------------------------------------
2407 PROCEDURE update_camp_status(
2408 p_campaign_id IN NUMBER,
2409 p_user_status_id IN NUMBER,
2410 p_budget_amount IN NUMBER,
2411 p_parent_id IN NUMBER
2412 )
2413 IS
2414
2415 l_budget_exist NUMBER;
2416 l_old_status_id NUMBER;
2417 l_new_status_id NUMBER;
2418 l_deny_status_id NUMBER;
2419 l_object_version NUMBER;
2420 l_approval_type VARCHAR2(30);
2421 l_return_status VARCHAR2(1);
2422 l_rollup_type VARCHAR2(30);
2423
2424 CURSOR c_old_status IS
2425 SELECT user_status_id, object_version_number,DECODE(rollup_type,'RCAM','RCAM','CAMP') rollup_type,
2426 status_code,custom_setup_id
2427 FROM ams_campaigns_all_b
2428 WHERE campaign_id = p_campaign_id;
2429
2430 CURSOR c_budget_exist IS
2431 SELECT 1
2432 FROM DUAL
2433 WHERE EXISTS(
2434 SELECT 1
2435 FROM ozf_act_budgets
2436 WHERE arc_act_budget_used_by = 'CAMP'
2437 AND act_budget_used_by_id = p_campaign_id);
2438
2439 CURSOR c_parent IS
2440 SELECT status_code
2441 FROM ams_campaigns_all_b
2442 WHERE campaign_id = p_parent_id ;
2443 l_status_code VARCHAR2(30);
2444
2445 CURSOR c_child IS
2446 SELECT 1
2447 FROM ams_campaigns_all_b
2448 WHERE parent_campaign_id = p_campaign_id
2449 AND status_code = 'ACTIVE' ;
2450 l_act_child_exist NUMBER;
2451
2452 l_system_status_code VARCHAR2(30) := AMS_Utility_PVT.get_system_status_code(p_user_status_id) ;
2453 l_old_status_code VARCHAR2(30) ;
2454 l_custom_setup_id NUMBER ;
2455 l_msg_count NUMBER ;
2456 l_msg_data VARCHAR2(2000);
2457 l_start_wf_process VARCHAR2(1);
2458
2459 BEGIN
2460
2461 IF (AMS_DEBUG_HIGH_ON) THEN
2462
2463
2464
2465 AMS_Utility_PVT.debug_message('SONALI x');
2466
2467 END IF;
2468
2469 OPEN c_old_status;
2470 FETCH c_old_status INTO l_old_status_id, l_object_version, l_rollup_type, l_old_status_code,l_custom_setup_id ;
2471 CLOSE c_old_status;
2472
2473 IF (AMS_DEBUG_HIGH_ON) THEN
2474
2475
2476
2477 AMS_Utility_PVT.debug_message('SONALI new '||l_system_status_code);
2478
2479 END IF;
2480 IF (AMS_DEBUG_HIGH_ON) THEN
2481
2482 AMS_Utility_PVT.debug_message('SONALI old '||l_old_status_code);
2483 END IF;
2484
2485 IF l_old_status_id = p_user_status_id THEN
2486 RETURN;
2487 END IF;
2488
2489 -- Follwing code is modified by ptendulk on 16-Jun-2001
2490 -- The old procedure is replaced by new to check the type
2491 -- of the approval required as ams_object_attribute table is
2492 -- obsoleted now.
2493 AMS_Utility_PVT.check_new_status_change(
2494 -- p_object_type => 'CAMP',
2495 p_object_type => l_rollup_type,
2496 p_object_id => p_campaign_id,
2497 p_old_status_id => l_old_status_id,
2498 p_new_status_id => p_user_status_id,
2499 p_custom_setup_id => l_custom_setup_id,
2500 x_approval_type => l_approval_type,
2501 x_return_status => l_return_status
2502 );
2503
2504 --AMS_Utility_PVT.check_status_change(
2505 -- p_object_type => 'CAMP',
2506 -- p_object_type => l_rollup_type,
2507 -- p_object_id => p_campaign_id,
2508 -- p_old_status_id => l_old_status_id,
2509 -- p_new_status_id => p_user_status_id,
2510 -- x_approval_type => l_approval_type,
2511 -- x_return_status => l_return_status
2512 --);
2513
2514 IF l_return_status <> FND_API.g_ret_sts_success THEN
2515 RAISE FND_API.g_exc_error;
2516 END IF;
2517
2518 -- Following lines of code is modified by ptendulk on 22-May-2001
2519 -- Check system status code instead of user status id.
2520 -- Campaign can not go active unless the program is active
2521 -- program - campaign 3
2522 IF p_parent_id IS NOT NULL
2523 -- AND p_user_status_id = 105 THEN
2524 AND l_system_status_code = 'ACTIVE' THEN
2525 OPEN c_parent ;
2526 FETCH c_parent INTO l_status_code ;
2527 CLOSE c_parent;
2528
2529 IF l_status_code <> 'ACTIVE' THEN
2530 AMS_Utility_PVT.Error_Message('AMS_CAMP_PROG_ACTIVE_STAT');
2531 RAISE FND_API.g_exc_error;
2532 END IF ;
2533 END IF ;
2534
2535 -- Can not cancell /Complete Program if the child campaign is Active
2536 -- Program Campaign Rules 4.
2537 -- IF p_user_status_id = 106 OR
2538 -- p_user_status_id = 111
2539 IF l_system_status_code = 'CANCELLED'
2540 THEN
2541 IF l_rollup_type = 'RCAM' THEN
2542 Cancel_Program(p_campaign_id);
2543 -- Cancel All the children associated to the program.
2544 IF (AMS_DEBUG_HIGH_ON) THEN
2545
2546 AMS_Utility_PVT.Debug_Message('Cancel All the events');
2547 END IF;
2548 -- Call to cancel events modified by soagrawa on 15-feb-2002
2549 -- after gmadana modified event rules APIs for bug# 2218013
2550 --AMS_EvhRules_PVT.Cancel_All_Event(p_prog_id => p_campaign_id );
2551 IF FND_API.g_false = AMS_EvhRules_PVT.Cancel_All_Event(p_prog_id => p_campaign_id)
2552 THEN
2553 AMS_Utility_PVT.Error_Message('AMS_COMP_CANNOT_CANCEL');
2554 RAISE FND_API.g_exc_error;
2555 END IF;
2556 IF (AMS_DEBUG_HIGH_ON) THEN
2557
2558 AMS_Utility_PVT.Debug_Message('After Cancel All the events');
2559 END IF;
2560 ELSE
2561 Cancel_Schedule(p_campaign_id);
2562 END IF ;
2563
2564 --OPEN c_child;
2565 --FETCH c_child INTO l_act_child_exist;
2566 --CLOSE c_child;
2567
2568 --IF l_act_child_exist IS NOT NULL THEN
2569 -- AMS_Utility_PVT.Error_Message('AMS_CAMP_CHILD_ACTIVE');
2570 -- RAISE FND_API.g_exc_error;
2571 --END IF ;
2572 ELSIF l_system_status_code = 'COMPLETED' THEN
2573 IF l_rollup_type = 'RCAM' THEN
2574 Complete_Program(p_campaign_id);
2575 AMS_EvhRules_PVT.Complete_All_Event(p_prog_id => p_campaign_id );
2576 ELSE
2577 Complete_Schedule(p_campaign_id);
2578 END IF ;
2579
2580 ELSIF l_system_status_code = 'CLOSED' THEN
2581 IF l_rollup_type <> 'RCAM' THEN
2582 Check_Close_Campaign(p_campaign_id);
2583 END IF;
2584
2585 ELSIF l_system_status_code = 'ARCHIVED' THEN
2586 IF l_rollup_type = 'RCAM' THEN
2587 Archive_Campaigns(p_campaign_id) ;
2588 ELSE
2589 Archive_Schedules(p_campaign_id) ;
2590 END IF ;
2591 ELSIF l_system_status_code = 'ACTIVE' THEN
2592 IF l_rollup_type = 'RCAM' THEN
2593 IF l_old_status_code = 'NEW' THEN
2594 Activate_Campaigns(p_campaign_id) ;
2595 ELSIF l_old_status_code = 'ON_HOLD' THEN
2596 Hold_Campaigns(p_campaign_id,'ACTIVE') ;
2597 END IF;
2598 END IF ;
2599 ELSIF l_system_status_code = 'ON_HOLD' THEN
2600 IF l_rollup_type = 'RCAM' THEN
2601 Hold_Campaigns(p_campaign_id,'ON_HOLD') ;
2602 END IF ;
2603 END IF ;
2604
2605
2606
2607 -- Budget Approval
2608 IF l_approval_type = 'BUDGET' THEN
2609 /* Following code is commented by ptendulk on 30-Oct-2001
2610 for 0 budget approvals
2611 -- check if budget amount is specified
2612 IF p_budget_amount IS NULL THEN
2613 AMS_Utility_PVT.error_message('AMS_EVE_NO_BGT_AMT');
2614 RAISE FND_API.g_exc_error;
2615 END IF;
2616
2617 -- check if there is any budget line
2618 OPEN c_budget_exist;
2619 FETCH c_budget_exist INTO l_budget_exist;
2620 CLOSE c_budget_exist;
2621
2622 IF l_budget_exist IS NULL THEN
2623 AMS_Utility_PVT.error_message('AMS_EVE_NO_BGT_SRC');
2624 RAISE FND_API.g_exc_error;
2625 END IF;
2626 End of code commented by ptendulk
2627 */
2628 /* mayjain 22-Sep-2005 */
2629 AMS_Approval_PVT.Must_Preview(
2630 p_activity_id => p_campaign_id,
2631 p_activity_type => 'CAMP',
2632 p_approval_type => 'BUDGET',
2633 p_act_budget_id => null,
2634 p_requestor_id => AMS_Utility_PVT.get_resource_id(FND_GLOBAL.user_id),
2635 x_must_preview => l_start_wf_process,
2636 x_return_status => l_return_status);
2637
2638 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2639 RAISE FND_API.G_EXC_ERROR;
2640 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2641 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2642 END IF;
2643 /* mayjain 22-Sep-2005 */
2644
2645 IF (l_start_wf_process = 'Y') -- If the user is not the approver
2646 THEN
2647 -- start budget approval process
2648 l_new_status_id := AMS_Utility_PVT.get_default_user_status(
2649 'AMS_CAMPAIGN_STATUS',
2650 'SUBMITTED_BA'
2651 );
2652 l_deny_status_id := AMS_Utility_PVT.get_default_user_status(
2653 'AMS_CAMPAIGN_STATUS',
2654 'DENIED_BA'
2655 );
2656 AMS_Approval_PVT.StartProcess(
2657 p_activity_type => 'CAMP',
2658 p_activity_id => p_campaign_id,
2659 p_approval_type => l_approval_type,
2660 p_object_version_number => l_object_version,
2661 p_orig_stat_id => l_old_status_id,
2662 p_new_stat_id => p_user_status_id,
2663 p_reject_stat_id => l_deny_status_id,
2664 p_requester_userid => AMS_Utility_PVT.get_resource_id(FND_GLOBAL.user_id),
2665 p_workflowprocess => 'AMS_APPROVAL',
2666 p_item_type => 'AMSAPRV'
2667 );
2668 ELSE -- If user equals approver
2669 IF (AMS_DEBUG_HIGH_ON) THEN
2670 AMS_Utility_PVT.Debug_Message('No need to start Workflow Process for Approval, Status Code ' || l_system_status_code );
2671 END IF;
2672
2673 IF l_system_status_code = 'ACTIVE' AND l_rollup_type <> 'RCAM'
2674 THEN
2675 OZF_BudgetApproval_PVT.budget_request_approval(
2676 p_init_msg_list => FND_API.G_FALSE
2677 , p_api_version => 1.0
2678 , p_commit => FND_API.G_False
2679 , x_return_status => l_return_status
2680 , x_msg_count => l_msg_count
2681 , x_msg_data => l_msg_data
2682 , p_object_type => 'CAMP'
2683 , p_object_id => p_campaign_id
2684 --, x_status_code =>
2685 );
2686 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2687 RAISE FND_API.G_EXC_ERROR;
2688 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2689 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2690 END IF;
2691 END IF;
2692
2693 l_new_status_id := p_user_status_id;
2694
2695 END IF; -- IF (l_start_wf_process = 'Y')
2696 -- Concept Approval
2697 ELSIF l_approval_type = 'THEME' THEN
2698
2699 /* mayjain 22-Sep-2005 */
2700 AMS_Approval_PVT.Must_Preview(
2701 p_activity_id => p_campaign_id,
2702 p_activity_type => 'CAMP',
2703 p_approval_type => 'CONCEPT',
2704 p_act_budget_id => null,
2705 p_requestor_id => AMS_Utility_PVT.get_resource_id(FND_GLOBAL.user_id),
2706 x_must_preview => l_start_wf_process,
2707 x_return_status => l_return_status);
2708
2709 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2710 RAISE FND_API.G_EXC_ERROR;
2711 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2712 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2713 END IF;
2714 /* mayjain 22-Sep-2005 */
2715
2716 IF (l_start_wf_process = 'Y') -- If the user is not the approver
2717 THEN
2718 l_new_status_id := AMS_Utility_PVT.get_default_user_status(
2719 'AMS_CAMPAIGN_STATUS',
2720 'SUBMITTED_TA'
2721 );
2722 l_deny_status_id := AMS_Utility_PVT.get_default_user_status(
2723 'AMS_CAMPAIGN_STATUS',
2724 'DENIED_TA'
2725 );
2726 AMS_Approval_PVT.StartProcess(
2727 p_activity_type => 'CAMP',
2728 p_activity_id => p_campaign_id,
2729 p_approval_type => 'CONCEPT',
2730 p_object_version_number => l_object_version,
2731 p_orig_stat_id => l_old_status_id,
2732 p_new_stat_id => p_user_status_id,
2733 p_reject_stat_id => l_deny_status_id,
2734 p_requester_userid => AMS_Utility_PVT.get_resource_id(FND_GLOBAL.user_id),
2735 p_workflowprocess => 'AMS_CONCEPT_APPROVAL',
2736 p_item_type => 'AMSAPRV'
2737 );
2738
2739 ELSE -- If user equals approver
2740 l_new_status_id := p_user_status_id;
2741 END IF; -- IF (l_start_wf_process = 'Y')
2742
2743 ELSE
2744 -- Following budget line api call added by soagrawa on 25-oct-2002
2745 -- for enhancement # 2445453
2746
2747 IF l_system_status_code = 'ACTIVE' AND l_rollup_type <> 'RCAM'
2748 THEN
2749 OZF_BudgetApproval_PVT.budget_request_approval(
2750 p_init_msg_list => FND_API.G_FALSE
2751 , p_api_version => 1.0
2752 , p_commit => FND_API.G_False
2753 , x_return_status => l_return_status
2754 , x_msg_count => l_msg_count
2755 , x_msg_data => l_msg_data
2756 , p_object_type => 'CAMP'
2757 , p_object_id => p_campaign_id
2758 --, x_status_code =>
2759 );
2760 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2761 RAISE FND_API.G_EXC_ERROR;
2762 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2763 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2764 END IF;
2765 END IF;
2766
2767 l_new_status_id := p_user_status_id;
2768 END IF;
2769
2770 update_status(p_campaign_id => p_campaign_id,
2771 p_new_status_id => l_new_status_id,
2772 p_new_status_code => AMS_Utility_PVT.get_system_status_code(l_new_status_id)
2773 ) ;
2774
2775
2776 /* Following code is commented by ptendulk on 08-Oct-2001
2777 Use the common update api to update the campaign
2778 UPDATE ams_campaigns_all_b
2779 SET user_status_id = l_new_status_id,
2780 status_code = AMS_Utility_PVT.get_system_status_code(l_new_status_id),
2781 status_date = SYSDATE
2782 WHERE campaign_id = p_campaign_id;
2783
2784 IF l_system_status_code = 'ACTIVE' AND
2785 l_new_status_id = p_user_status_id
2786 THEN
2787 -- Following code is added by ptendulk on 24-Sep-2001
2788 -- Make the campaign Non confidential when it goes live.
2789 UPDATE ams_campaigns_all_b
2790 SET private_flag = 'N'
2791 WHERE campaign_id = p_campaign_id ;
2792
2793 Activate_Campaign(p_campaign_id => p_campaign_id );
2794 END IF ;
2795 */
2796
2797
2798 END update_camp_status;
2799
2800
2801 ---------------------------------------------------------------------
2802 -- PROCEDURE
2803 -- push_source_code
2804 --
2805 -- HISTORY
2806 -- 11/01/99 holiu Created.
2807 ---------------------------------------------------------------------
2808 PROCEDURE push_source_code(
2809 p_source_code IN VARCHAR2,
2810 p_arc_object IN VARCHAR2,
2811 p_object_id IN NUMBER,
2812 p_related_source_code IN VARCHAR2 := NULL,
2813 p_related_source_object IN VARCHAR2 := NULL,
2814 p_related_source_id IN NUMBER := NULL
2815 )
2816 IS
2817
2818 l_sourcecode_id NUMBER;
2819 l_return_status VARCHAR2(1);
2820 l_msg_count NUMBER;
2821 l_msg_data VARCHAR2(2000);
2822
2823 BEGIN
2824
2825 AMS_SourceCode_PVT.create_sourcecode(
2826 p_api_version => 1.0,
2827 p_init_msg_list => FND_API.g_false,
2828 p_commit => FND_API.g_false,
2829 p_validation_level => FND_API.g_valid_level_full,
2830
2831 x_return_status => l_return_status,
2832 x_msg_count => l_msg_count,
2833 x_msg_data => l_msg_data,
2834
2835 p_sourcecode => p_source_code,
2836 p_sourcecode_for => p_arc_object,
2837 p_sourcecode_for_id => p_object_id,
2838 p_related_sourcecode => p_related_source_code,
2839 p_releated_sourceobj => p_related_source_object,
2840 p_related_sourceid => p_related_source_id,
2841 x_sourcecode_id => l_sourcecode_id
2842 );
2843
2844 IF l_return_status <> FND_API.g_ret_sts_success THEN
2845 RAISE FND_API.g_exc_error;
2846 END IF;
2847
2848 END push_source_code;
2849
2850
2851 -----------------------------------------------------------------------
2852 -- FUNCTION
2853 -- get_parent_media_type
2854 --
2855 -- HISTORY
2856 -- 11/01/99 holiu Created.
2857 -----------------------------------------------------------------------
2858 FUNCTION get_parent_media_type(
2859 p_parent_id IN NUMBER
2860 )
2861 RETURN VARCHAR2
2862 IS
2863
2864 l_parent_id NUMBER;
2865 l_media_type VARCHAR2(30);
2866
2867 CURSOR c_parent IS
2868 SELECT parent_campaign_id,
2869 media_type_code
2870 FROM ams_campaigns_vl
2871 WHERE campaign_id = l_parent_id;
2872
2873 BEGIN
2874
2875 l_parent_id := p_parent_id;
2876 OPEN c_parent;
2877 FETCH c_parent INTO l_parent_id, l_media_type;
2878 CLOSE c_parent;
2879
2880 IF l_media_type IS NOT NULL THEN
2881 RETURN l_media_type;
2882 ELSIF l_parent_id IS NULL THEN
2883 RETURN NULL;
2884 ELSE
2885 RETURN get_parent_media_type(l_parent_id);
2886 END IF;
2887
2888 END get_parent_media_type;
2889
2890
2891 -----------------------------------------------------------------------
2892 -- FUNCTION
2893 -- check_camp_parent
2894 --
2895 -- PURPOSE
2896 -- Check if a campaign can be the parent of another campaign.
2897 -----------------------------------------------------------------------
2898 FUNCTION check_camp_parent(
2899 p_camp_id IN NUMBER,
2900 p_parent_id IN NUMBER
2901 )
2902 RETURN VARCHAR2
2903 IS
2904
2905 l_camp_id NUMBER;
2906
2907 CURSOR c_parent IS
2908 SELECT parent_campaign_id
2909 FROM ams_campaigns_vl
2910 WHERE campaign_id = l_camp_id;
2911
2912 BEGIN
2913
2914 l_camp_id := p_parent_id;
2915
2916 WHILE l_camp_id IS NOT NULL LOOP
2917 IF l_camp_id = p_camp_id THEN
2918 RETURN FND_API.g_false;
2919 END IF;
2920
2921 OPEN c_parent;
2922 FETCH c_parent INTO l_camp_id;
2923 IF c_parent%NOTFOUND THEN
2924 CLOSE c_parent;
2925 RETURN FND_API.g_false;
2926 END IF;
2927 CLOSE c_parent;
2928 END LOOP;
2929
2930 RETURN FND_API.g_true;
2931
2932 END check_camp_parent;
2933
2934
2935 -----------------------------------------------------------------------
2936 -- FUNCTION
2937 -- check_camp_attribute
2938 --
2939 -- HISTORY
2940 -- 11/01/99 holiu Create.
2941 -- 09/14/99 holiu Rewrite.
2942 -----------------------------------------------------------------------
2943 FUNCTION check_camp_attribute(
2944 p_camp_id IN NUMBER,
2945 p_attribute IN VARCHAR2
2946 )
2947 RETURN VARCHAR2
2948 IS
2949
2950 l_dummy NUMBER;
2951
2952 CURSOR c_object_attr IS
2953 SELECT 1
2954 FROM ams_object_attributes
2955 WHERE object_type = 'CAMP'
2956 AND object_id = p_camp_id
2957 AND object_attribute = p_attribute;
2958
2959 BEGIN
2960
2961 OPEN c_object_attr;
2962 FETCH c_object_attr INTO l_dummy;
2963 CLOSE c_object_attr;
2964
2965 IF l_dummy IS NULL THEN
2966 RETURN FND_API.g_false;
2967 ELSE
2968 RETURN FND_API.g_true;
2969 END IF;
2970
2971 END check_camp_attribute;
2972
2973 --=======================================================================
2974 -- PROCEDURE
2975 -- Convert_Camp_Currency
2976 -- NOTES
2977 -- This procedure is created to convert the transaction currency into
2978 -- functional currency.
2979 -- HISTORY
2980 -- 09/27/2000 PTENDULK Created.
2981 --=======================================================================
2982 PROCEDURE Convert_Camp_Currency(
2983 p_tc_curr IN VARCHAR2,
2984 p_tc_amt IN NUMBER,
2985 x_fc_curr OUT NOCOPY VARCHAR2,
2986 x_fc_amt OUT NOCOPY NUMBER
2987 )
2988 IS
2989 L_FUNC_CURR_PROF CONSTANT VARCHAR2(30) := 'AMS_DEFAULT_CURR_CODE';
2990 l_curr_code VARCHAR2(240) ;
2991 l_return_status VARCHAR2(30);
2992 BEGIN
2993 l_curr_code := FND_PROFILE.Value(L_FUNC_CURR_PROF);
2994 IF l_curr_code IS NULL THEN
2995 l_curr_code := 'USD' ;
2996 END IF ;
2997
2998 AMS_Utility_PVT.Convert_Currency(
2999 x_return_status => l_return_status ,
3000 p_from_currency => p_tc_curr,
3001 p_to_currency => l_curr_code,
3002 p_from_amount => p_tc_amt,
3003 x_to_amount => x_fc_amt
3004 );
3005
3006 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3007 RAISE FND_API.g_exc_error;
3008 END IF;
3009
3010 x_fc_curr := l_curr_code ;
3011
3012 END Convert_Camp_Currency;
3013
3014 --=======================================================================
3015 -- PROCEDURE
3016 -- Get_Camp_Child_Count
3017 -- NOTES
3018 -- This function is created to return the child count given a campaign
3019 -- id . It is used to tune Campaign Hierarchy tree.
3020 --
3021 -- HISTORY
3022 -- 04-Feb-2001 PTENDULK Created.
3023 --=======================================================================
3024 FUNCTION Get_Camp_Child_Count( p_campaign_id IN VARCHAR2 )
3025 RETURN NUMBER
3026 IS
3027 l_count NUMBER ;
3028
3029 CURSOR c_child IS
3030 SELECT COUNT(campaign_id)
3031 FROM ams_campaigns_vl
3032 WHERE parent_campaign_id = p_campaign_id
3033 AND active_flag = 'Y'
3034 AND private_flag = 'N'
3035 AND show_campaign_flag = 'Y' ;
3036
3037 BEGIN
3038 OPEN c_child ;
3039 FETCH c_child INTO l_count ;
3040 CLOSE c_child ;
3041
3042 RETURN l_count ;
3043
3044 END Get_Camp_Child_Count ;
3045
3046
3047 --=====================================================================
3048 -- PROCEDURE
3049 -- Update_Owner
3050 --
3051 -- PURPOSE
3052 -- The api is created to update the owner of the campaign from the
3053 -- access table if the owner is changed in update.
3054 --
3055 -- Algorithm for CSCH access list manipulation
3056 -- I. For each CSCH of the CAMP do the following:
3057 -- 1. Is old campaign owner the same as the schedule owner?
3058 -- Yes:
3059 -- Is new campaign owner in the access list of the schedule
3060 -- Yes: do nothing
3061 -- No: Add new campaign owner to access list of schedule
3062 -- No:
3063 -- Is old campaign owner in the access list of the schedule
3064 -- Yes: Delete access from schedule access list for old campaign owner
3065 -- No: Do nothing
3066 -- Is new campaign owner in the access list of the schedule
3067 -- Yes: Do nothing
3068 -- No: Add new campaign owner to access list of schedule
3069 --
3070 --
3071 -- HISTORY
3072 -- 04-Mar-2001 ptendulk Created.
3073 -- 07-Jun-2002 soagrawa Modified code. Now manipulating CSCH access list
3074 -- if campaign owner changes. Refer to algorithm above.
3075 -- This is for bug# 2406677
3076 --=====================================================================
3077 PROCEDURE Update_Owner(
3078 p_api_version IN NUMBER,
3079 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
3080 p_commit IN VARCHAR2 := FND_API.g_false,
3081 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
3082 x_return_status OUT NOCOPY VARCHAR2,
3083 x_msg_count OUT NOCOPY NUMBER,
3084 x_msg_data OUT NOCOPY VARCHAR2,
3085 p_object_type IN VARCHAR2 := NULL ,
3086 p_campaign_id IN NUMBER,
3087 p_owner_id IN NUMBER )
3088 IS
3089 CURSOR c_owner IS
3090 SELECT owner_user_id
3091 FROM ams_campaigns_all_b
3092 WHERE campaign_id = p_campaign_id ;
3093
3094 CURSOR c_schedules IS
3095 SELECT *
3096 FROM ams_campaign_schedules_vl
3097 WHERE campaign_id = p_campaign_id;
3098
3099 CURSOR c_access_csch_det(p_schedule_id NUMBER, p_owner NUMBER) IS
3100 SELECT *
3101 FROM ams_act_access
3102 WHERE arc_act_access_to_object = 'CSCH'
3103 AND user_or_role_id = p_owner
3104 AND arc_user_or_role_type = 'USER'
3105 AND act_access_to_object_id = p_schedule_id;
3106
3107 c_schedule_rec c_schedules%ROWTYPE;
3108 c_schedule_access_rec c_access_csch_det%ROWTYPE;
3109
3110 l_access_rec AMS_Access_Pvt.access_rec_type ;
3111 l_dummy_id NUMBER ;
3112
3113 l_old_owner NUMBER ;
3114
3115 BEGIN
3116 OPEN c_owner ;
3117 FETCH c_owner INTO l_old_owner ;
3118 IF c_owner%NOTFOUND THEN
3119 CLOSE c_owner;
3120 AMS_Utility_Pvt.Error_Message('AMS_API_RECORD_NOT_FOUND');
3121 RAISE FND_API.g_exc_error;
3122 END IF;
3123 CLOSE c_owner ;
3124
3125 IF p_owner_id <> l_old_owner THEN
3126 AMS_Access_PVT.update_object_owner(
3127 p_api_version => p_api_version,
3128 p_init_msg_list => p_init_msg_list,
3129 p_commit => p_commit,
3130 p_validation_level => p_validation_level,
3131 x_return_status => x_return_status,
3132 x_msg_count => x_msg_count,
3133 x_msg_data => x_msg_data,
3134 p_object_type => nvl(p_object_type,'CAMP'),
3135 p_object_id => p_campaign_id,
3136 p_resource_id => p_owner_id,
3137 p_old_resource_id => l_old_owner
3138 );
3139
3140 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3141 RAISE FND_API.G_EXC_ERROR;
3142 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3143 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3144 END IF;
3145
3146 -- Fetch all the schedules for the campaign
3147 OPEN c_schedules;
3148 LOOP
3149 FETCH c_schedules INTO c_schedule_rec;
3150 EXIT WHEN c_schedules%NOTFOUND ;
3151
3152 -- 1. Is old campaign owner the same as the schedule owner?
3153 IF l_old_owner = c_schedule_rec.owner_user_id
3154 THEN
3155 -- Yes:
3156
3157 -- Is new campaign owner in the access list of the schedule
3158 OPEN c_access_csch_det(c_schedule_rec.schedule_id, p_owner_id);
3159 FETCH c_access_csch_det INTO c_schedule_access_rec;
3160 -- Yes: do nothing
3161 IF c_access_csch_det%NOTFOUND THEN
3162 -- No: Add new campaign owner to access list of schedule
3163
3164 -- Create Access
3165 l_access_rec.act_access_to_object_id := c_schedule_rec.schedule_id ;
3166 l_access_rec.arc_act_access_to_object := 'CSCH' ;
3167 l_access_rec.owner_flag := 'N' ;
3168 l_access_rec.user_or_role_id := p_owner_id;
3169 l_access_rec.arc_user_or_role_type := 'USER' ;
3170 l_access_rec.delete_flag := 'N';
3171 l_access_rec.admin_flag := 'Y';
3172
3173
3174 AMS_Access_Pvt.Create_Access(
3175 p_api_version => p_api_version,
3176 p_init_msg_list => p_init_msg_list,
3177 p_commit => p_commit,
3178 p_validation_level => p_validation_level,
3179
3180 x_return_status => x_return_status,
3181 x_msg_count => x_msg_count,
3182 x_msg_data => x_msg_data,
3183
3184 p_access_rec => l_access_rec,
3185 x_access_id => l_dummy_id
3186 );
3187 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3188 CLOSE c_access_csch_det;
3189 CLOSE c_schedules;
3190 RAISE FND_API.G_EXC_ERROR;
3191 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3192 CLOSE c_access_csch_det;
3193 CLOSE c_schedules;
3194 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3195 END IF;
3196
3197 END IF;
3198 CLOSE c_access_csch_det ;
3199
3200 ELSE
3201 -- No:
3202
3203 -- Is old campaign owner in the access list of the schedule
3204 OPEN c_access_csch_det(c_schedule_rec.schedule_id, l_old_owner);
3205 FETCH c_access_csch_det INTO c_schedule_access_rec;
3206 IF c_access_csch_det%NOTFOUND THEN
3207 -- No: Do nothing
3208 NULL;
3209 ELSE
3210 -- Yes: Delete access from schedule access list for old campaign owner
3211
3212 Ams_Access_pvt.delete_access(
3213 p_api_version => p_api_version,
3214 p_init_msg_list => p_init_msg_list,
3215 p_commit => p_commit,
3216 p_validation_level => p_validation_level,
3217
3218 x_return_status => x_return_status,
3219 x_msg_count => x_msg_count,
3220 x_msg_data => x_msg_data,
3221
3222 p_access_id => c_schedule_access_rec.activity_access_id,
3223 p_object_version => c_schedule_access_rec.object_version_number
3224 );
3225 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3226 CLOSE c_access_csch_det;
3227 CLOSE c_schedules;
3228 RAISE FND_API.G_EXC_ERROR;
3229 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3230 CLOSE c_access_csch_det;
3231 CLOSE c_schedules;
3232 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3233 END IF;
3234
3235 END IF;
3236 CLOSE c_access_csch_det;
3237
3238
3239
3240 -- Is new campaign owner in the access list of the schedule
3241 OPEN c_access_csch_det(c_schedule_rec.schedule_id, p_owner_id);
3242 FETCH c_access_csch_det INTO c_schedule_access_rec;
3243 -- Yes: do nothing
3244 IF c_access_csch_det%NOTFOUND THEN
3245 -- No: Add new campaign owner to access list of schedule
3246
3247 -- Create Access
3248 l_access_rec.act_access_to_object_id := c_schedule_rec.schedule_id ;
3249 l_access_rec.arc_act_access_to_object := 'CSCH' ;
3250 l_access_rec.owner_flag := 'N' ;
3251 l_access_rec.user_or_role_id := p_owner_id;
3252 l_access_rec.arc_user_or_role_type := 'USER' ;
3253 l_access_rec.delete_flag := 'N';
3254 l_access_rec.admin_flag := 'Y';
3255
3256
3257 AMS_Access_Pvt.Create_Access(
3258 p_api_version => p_api_version,
3259 p_init_msg_list => p_init_msg_list,
3260 p_commit => p_commit,
3261 p_validation_level => p_validation_level,
3262
3263 x_return_status => x_return_status,
3264 x_msg_count => x_msg_count,
3265 x_msg_data => x_msg_data,
3266
3267 p_access_rec => l_access_rec,
3268 x_access_id => l_dummy_id
3269 );
3270 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3271 CLOSE c_access_csch_det;
3272 CLOSE c_schedules;
3273 RAISE FND_API.G_EXC_ERROR;
3274 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3275 CLOSE c_access_csch_det;
3276 CLOSE c_schedules;
3277 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3278 END IF;
3279
3280 END IF;
3281 CLOSE c_access_csch_det ;
3282
3283
3284 END IF;
3285 END LOOP;
3286 CLOSE c_schedules;
3287 END IF;
3288
3289 END Update_Owner ;
3290
3291 -----------------------------------------------------------------------
3292 -- PROCEDURE
3293 -- validate_event
3294 --
3295 -- PURPOSE
3296 -- Validate the realted event. Check the foreign key against the
3297 -- event tables depending on the event_type passed
3298 --
3299 -- NOTES
3300 -- HISTORY
3301 -- 12-Apr-2001 rrajesh Created.
3302 -----------------------------------------------------------------------
3303 PROCEDURE validate_realted_event(
3304 p_related_event_id IN NUMBER,
3305 p_related_event_type IN VARCHAR2,
3306 x_return_status OUT NOCOPY VARCHAR2
3307 ) IS
3308
3309 CURSOR c_event IS
3310 SELECT event_header_id
3311 FROM ams_event_headers_all_b
3312 WHERE event_header_id = p_related_event_id ;
3313
3314 CURSOR c_schedule_event IS
3315 SELECT event_offer_id
3316 FROM ams_event_offers_all_b
3317 WHERE event_offer_id = p_related_event_id
3318 --AND event_standalone_flag = 'N';
3319 AND event_object_type = 'EVEO';
3320
3321 CURSOR c_one_off_event IS
3322 SELECT event_offer_id
3323 FROM ams_event_offers_all_b
3324 WHERE event_offer_id = p_related_event_id
3325 --AND event_standalone_flag = 'Y';
3326 AND event_object_type = 'EONE';
3327
3328 l_tmpEvent NUMBER;
3329
3330 BEGIN
3331
3332 IF p_related_event_type = 'EVEH' THEN
3333 OPEN c_event;
3334 FETCH c_event INTO l_tmpEvent;
3335 IF c_event%NOTFOUND THEN
3336 CLOSE c_event;
3337 x_return_status := FND_API.g_ret_sts_error;
3338 RETURN;
3339 END IF;
3340 CLOSE c_event;
3341 ELSIF p_related_event_type = 'EVEO' THEN
3342 OPEN c_schedule_event;
3343 FETCH c_schedule_event INTO l_tmpEvent;
3344 IF c_schedule_event%NOTFOUND THEN
3345 CLOSE c_schedule_event ;
3346 x_return_status := FND_API.g_ret_sts_error;
3347 RETURN;
3348 END IF;
3349 CLOSE c_schedule_event ;
3350 ELSIF p_related_event_type = 'EONE' THEN
3351 OPEN c_one_off_event;
3352 FETCH c_one_off_event INTO l_tmpEvent;
3353 IF c_one_off_event%NOTFOUND THEN
3354 CLOSE c_one_off_event;
3355 x_return_status := FND_API.g_ret_sts_error;
3356 RETURN;
3357 END IF;
3358 CLOSE c_one_off_event;
3359 END IF;
3360 END validate_realted_event;
3361
3362 -----------------------------------------------------------------------
3363 -- PROCEDURE
3364 -- Update_Related_Source_Code
3365 --
3366 -- PURPOSE
3367 -- Update the source code of realted event.
3368 --
3369 -- NOTES
3370 -- HISTORY
3371 -- 12-Apr-2001 rrajesh Created.
3372 -----------------------------------------------------------------------
3373
3374 PROCEDURE Update_Related_Source_Code(
3375 p_source_code IN VARCHAR2,
3376 p_source_code_for_id IN NUMBER,
3377 p_source_code_for IN VARCHAR2,
3378 p_related_source_code IN VARCHAR2,
3379 p_related_source_code_for_id IN NUMBER,
3380 p_related_source_code_for IN VARCHAR2,
3381 x_return_status OUT NOCOPY VARCHAR2
3382 ) IS
3383
3384 CURSOR c_sc_from_source_codes IS
3385 SELECT source_code_id
3386 FROM ams_source_codes
3387 WHERE source_code = p_source_code
3388 AND source_code_for_id = p_source_code_for_id
3389 AND arc_source_code_for = p_source_code_for;
3390
3391 l_return_status VARCHAR2(1);
3392 l_sourcecode_id NUMBER;
3393
3394 BEGIN
3395 OPEN c_sc_from_source_codes;
3396 FETCH c_sc_from_source_codes INTO l_sourcecode_id;
3397 IF c_sc_from_source_codes%NOTFOUND THEN
3398 x_return_status := FND_API.g_ret_sts_error;
3399 RETURN;
3400 END IF;
3401
3402 AMS_SourceCode_PVT.modify_sourcecode(
3403 p_source_code => p_source_code,
3404 p_object_type => p_source_code_for,
3405 p_object_id => p_source_code_for_id,
3406 p_sourcecode_id => l_sourcecode_id,
3407 p_related_sourcecode => p_related_source_code,
3408 p_releated_sourceobj => p_related_source_code_for,
3409 p_related_sourceid => p_related_source_code_for_id,
3410 x_return_status => l_return_status
3411 );
3412
3413 END Update_Related_Source_Code;
3414
3415 -- PROCEDURE
3416 -- Archive_Schedules
3417 --
3418 -- PURPOSE
3419 -- Archive all the schedules associated to the campaign.
3420 --
3421 -- NOTES
3422 -- HISTORY
3423 -- 22-May-2001 ptendulk Created.
3424 -----------------------------------------------------------------------
3425
3426 PROCEDURE Archive_Schedules(
3427 p_campaign_id IN NUMBER
3428 ) IS
3429
3430 CURSOR c_schedule IS
3431 SELECT schedule_id , object_version_number
3432 FROM ams_campaign_schedules_b
3433 WHERE campaign_id = p_campaign_id
3434 AND (status_code = 'COMPLETED' OR status_code = 'CANCELLED') ;
3435
3436 l_schedule_id NUMBER ;
3437 l_obj_version NUMBER ;
3438 l_user_status_id NUMBER := AMS_Utility_PVT.get_default_user_status('AMS_CAMPAIGN_SCHEDULE_STATUS','ARCHIVED');
3439
3440 BEGIN
3441
3442 OPEN c_schedule ;
3443 LOOP
3444 FETCH c_schedule INTO l_schedule_id, l_obj_version;
3445 EXIT WHEN c_schedule%NOTFOUND ;
3446
3447 UPDATE ams_campaign_schedules_b
3448 SET status_code = 'ARCHIVED',
3449 user_status_id = l_user_status_id ,
3450 status_date = SYSDATE ,
3451 object_version_number = l_obj_version + 1
3452 WHERE schedule_id = l_schedule_id
3453 AND object_version_number = l_obj_version ;
3454
3455 IF (SQL%NOTFOUND) THEN
3456 CLOSE c_schedule ;
3457 AMS_Utility_PVT.Error_Message('AMS_API_RECORD_NOT_FOUND');
3458 RAISE FND_API.g_exc_error;
3459 END IF;
3460
3461 END LOOP ;
3462 CLOSE c_schedule ;
3463
3464
3465 END Archive_Schedules;
3466
3467 -- PROCEDURE
3468 -- Archive_Campaigns
3469 --
3470 -- PURPOSE
3471 -- Archive all the Programs/Campaigns associated to the Program
3472 --
3473 -- NOTES
3474 -- HISTORY
3475 -- 22-May-2001 ptendulk Created.
3476 -----------------------------------------------------------------------
3477
3478 PROCEDURE Archive_Campaigns(
3479 p_program_id IN NUMBER
3480 ) IS
3481
3482 CURSOR c_campaign IS
3483 SELECT campaign_id , object_version_number,rollup_type
3484 FROM ams_campaigns_all_b
3485 WHERE parent_campaign_id = p_program_id
3486 AND (status_code = 'COMPLETED' OR status_code = 'CANCELLED') ;
3487
3488 l_campaign_id NUMBER ;
3489 l_obj_version NUMBER ;
3490 l_rollup_type VARCHAR2(30) ;
3491 l_program_status_id NUMBER := AMS_Utility_PVT.get_default_user_status('AMS_PROGRAM_STATUS','ARCHIVED');
3492 l_campaign_status_id NUMBER := AMS_Utility_PVT.get_default_user_status('AMS_CAMPAIGN_STATUS','ARCHIVED');
3493
3494 BEGIN
3495
3496 OPEN c_campaign ;
3497 LOOP
3498 FETCH c_campaign INTO l_campaign_id, l_obj_version, l_rollup_type ;
3499 EXIT WHEN c_campaign%NOTFOUND ;
3500
3501 IF l_rollup_type = 'RCAM' THEN
3502 Archive_Campaigns(l_campaign_id) ;
3503 UPDATE ams_campaigns_all_b
3504 SET status_code = 'ARCHIVED',
3505 user_status_id = l_program_status_id ,
3506 status_date = SYSDATE ,
3507 object_version_number = l_obj_version + 1
3508 WHERE campaign_id = l_campaign_id
3509 AND object_version_number = l_obj_version ;
3510 ELSE
3511 Archive_Schedules(l_campaign_id) ;
3512
3513 UPDATE ams_campaigns_all_b
3514 SET status_code = 'ARCHIVED',
3515 user_status_id = l_campaign_status_id ,
3516 status_date = SYSDATE ,
3517 object_version_number = l_obj_version + 1
3518 WHERE campaign_id = l_campaign_id
3519 AND object_version_number = l_obj_version ;
3520
3521 IF (SQL%NOTFOUND) THEN
3522 CLOSE c_campaign ;
3523 AMS_Utility_PVT.Error_Message('AMS_API_RECORD_NOT_FOUND');
3524 RAISE FND_API.g_exc_error;
3525 END IF;
3526 END IF;
3527 END LOOP ;
3528 CLOSE c_campaign ;
3529
3530
3531 END Archive_Campaigns;
3532
3533 -- PROCEDURE
3534 -- Activate_Campaigns
3535 --
3536 -- PURPOSE
3537 -- Activate all the Campaigns associated to the Program
3538 --
3539 -- NOTES
3540 -- HISTORY
3541 -- 22-May-2001 ptendulk Created.
3542 -----------------------------------------------------------------------
3543
3544 PROCEDURE Activate_Campaigns(
3545 p_program_id IN NUMBER
3546 ) IS
3547
3548 CURSOR c_campaign IS
3549 SELECT campaign_id , object_version_number,rollup_type
3550 FROM ams_campaigns_all_b
3551 WHERE parent_campaign_id = p_program_id
3552 AND status_code = DECODE(rollup_type,'RCAM','NEW','AVAILABLE') ;
3553
3554 l_campaign_id NUMBER ;
3555 l_obj_version NUMBER ;
3556 l_rollup_type VARCHAR2(30) ;
3557 l_program_status_id NUMBER := AMS_Utility_PVT.get_default_user_status('AMS_PROGRAM_STATUS','ACTIVE');
3558 l_campaign_status_id NUMBER := AMS_Utility_PVT.get_default_user_status('AMS_CAMPAIGN_STATUS','ACTIVE');
3559
3560 BEGIN
3561
3562 OPEN c_campaign ;
3563 LOOP
3564 FETCH c_campaign INTO l_campaign_id, l_obj_version, l_rollup_type ;
3565 EXIT WHEN c_campaign%NOTFOUND ;
3566
3567 IF l_rollup_type = 'RCAM' THEN
3568 Activate_Campaigns(l_campaign_id) ;
3569 UPDATE ams_campaigns_all_b
3570 SET status_code = 'ACTIVE',
3571 user_status_id = l_program_status_id ,
3572 status_date = SYSDATE ,
3573 object_version_number = l_obj_version + 1
3574 WHERE campaign_id = l_campaign_id
3575 AND object_version_number = l_obj_version ;
3576 ELSE
3577 UPDATE ams_campaigns_all_b
3578 SET status_code = 'ACTIVE',
3579 user_status_id = l_campaign_status_id ,
3580 status_date = SYSDATE ,
3581 object_version_number = l_obj_version + 1
3582 WHERE campaign_id = l_campaign_id
3583 AND object_version_number = l_obj_version ;
3584
3585 IF (SQL%NOTFOUND) THEN
3586 CLOSE c_campaign ;
3587 AMS_Utility_PVT.Error_Message('AMS_API_RECORD_NOT_FOUND');
3588 RAISE FND_API.g_exc_error;
3589 END IF;
3590 END IF;
3591 END LOOP ;
3592 CLOSE c_campaign ;
3593
3594
3595 END Activate_Campaigns;
3596
3597 --==========================================================================
3598 -- PROCEDURE
3599 -- Hold_Campaigns
3600 --
3601 -- PURPOSE
3602 -- Keep all the Campaigns/programs associated to the Program
3603 -- on hold.
3604 --
3605 -- NOTES
3606 -- HISTORY
3607 -- 23-May-2001 ptendulk Created.
3608 --==========================================================================
3609
3610 PROCEDURE Hold_Campaigns(
3611 p_program_id IN NUMBER,
3612 p_system_status_code IN VARCHAR2
3613 ) IS
3614
3615 CURSOR c_campaign IS
3616 SELECT campaign_id, object_version_number, rollup_type
3617 FROM ams_campaigns_all_b
3618 WHERE parent_campaign_id = p_program_id
3619 AND status_code = DECODE(p_system_status_code,'ACTIVE','ON_HOLD','ACTIVE') ;
3620
3621 l_campaign_id NUMBER ;
3622 l_obj_version NUMBER ;
3623 l_rollup_type VARCHAR2(30) ;
3624 l_program_status_id NUMBER := AMS_Utility_PVT.get_default_user_status('AMS_PROGRAM_STATUS',p_system_status_code);
3625 l_campaign_status_id NUMBER := AMS_Utility_PVT.get_default_user_status('AMS_CAMPAIGN_STATUS',p_system_status_code);
3626
3627 BEGIN
3628
3629 OPEN c_campaign ;
3630 LOOP
3631 FETCH c_campaign INTO l_campaign_id, l_obj_version, l_rollup_type ;
3632 EXIT WHEN c_campaign%NOTFOUND ;
3633
3634 IF l_rollup_type = 'RCAM' THEN
3635 Hold_Campaigns(l_campaign_id,p_system_status_code) ;
3636 UPDATE ams_campaigns_all_b
3637 SET status_code = p_system_status_code,
3638 user_status_id = l_program_status_id ,
3639 status_date = SYSDATE ,
3640 object_version_number = l_obj_version + 1
3641 WHERE campaign_id = l_campaign_id
3642 AND object_version_number = l_obj_version ;
3643 ELSE
3644 UPDATE ams_campaigns_all_b
3645 SET status_code = p_system_status_code,
3646 user_status_id = l_campaign_status_id ,
3647 status_date = SYSDATE ,
3648 object_version_number = l_obj_version + 1
3649 WHERE campaign_id = l_campaign_id
3650 AND object_version_number = l_obj_version ;
3651
3652 IF (SQL%NOTFOUND) THEN
3653 CLOSE c_campaign ;
3654 AMS_Utility_PVT.Error_Message('AMS_API_RECORD_NOT_FOUND');
3655 RAISE FND_API.g_exc_error;
3656 END IF;
3657 END IF;
3658 END LOOP ;
3659 CLOSE c_campaign ;
3660
3661
3662 END Hold_Campaigns;
3663
3664 --==========================================================================
3665 -- PROCEDURE
3666 -- Cancel_Schedule
3667 --
3668 -- PURPOSE
3669 -- Cancels all the schedules associated to the campaign. If the status
3670 -- order rules does not permit it, it will error out.
3671 --
3672 -- NOTES
3673 -- HISTORY
3674 -- 09-Jul-2001 ptendulk Created.
3675 -- 15-feb-2002 soagrawa Logic modified by soagrawa to fix bug# 2218013
3676 -- Before: Cancel a program => cancel all components
3677 -- Cancel a campaign => cancel all schedules
3678 -- Now: Cancel only if children are cancelled/archived
3679 --==========================================================================
3680
3681 /*
3682 PROCEDURE Cancel_Schedule(p_campaign_id IN NUMBER) IS
3683
3684 CURSOR c_schedule IS
3685 SELECT schedule_id,object_version_number,status_code
3686 FROM ams_campaign_schedules_b
3687 WHERE campaign_id = p_campaign_id
3688 AND status_code <> 'CANCELLED' ;
3689
3690 l_schedule_id NUMBER ;
3691 l_obj_version NUMBER ;
3692 l_status_code VARCHAR2(30) ;
3693 l_status_id NUMBER := AMS_Utility_PVT.get_default_user_status('AMS_CAMPAIGN_SCHEDULE_STATUS','CANCELLED');
3694
3695 BEGIN
3696
3697 OPEN c_schedule ;
3698 LOOP
3699 FETCH c_schedule INTO l_schedule_id,l_obj_version,l_status_code ;
3700 EXIT WHEN c_schedule%NOTFOUND ;
3701 IF FND_API.G_TRUE = AMS_Utility_PVT.Check_Status_Change('AMS_CAMPAIGN_SCHEDULE_STATUS',l_status_code,'CANCELLED') THEN
3702 -- Can cancel the schedule
3703 UPDATE ams_campaign_schedules_b
3704 SET status_code = 'CANCELLED',
3705 status_date = SYSDATE,
3706 user_status_id = l_status_id,
3707 object_version_number = object_version_number + 1
3708 WHERE schedule_id = l_schedule_id
3709 AND object_version_number = l_obj_version ;
3710
3711 IF (SQL%NOTFOUND) THEN
3712 CLOSE c_schedule ;
3713 AMS_Utility_PVT.Error_Message('AMS_API_RECORD_NOT_FOUND');
3714 RAISE FND_API.g_exc_error;
3715 END IF;
3716 ELSE -- Can not cancel the schedule as the status is can not go to cancel from current status
3717 CLOSE c_schedule;
3718 AMS_Utility_PVT.Error_Message('AMS_CSCH_CANNOT_CANCEL');
3719 RAISE FND_API.g_exc_error;
3720 END IF ;
3721
3722 END LOOP;
3723 CLOSE c_schedule;
3724
3725
3726 END Cancel_Schedule;
3727 */
3728
3729
3730 PROCEDURE Cancel_Schedule(p_campaign_id IN NUMBER) IS
3731
3732 CURSOR c_schedule IS
3733 SELECT count(*)
3734 FROM ams_campaign_schedules_b
3735 WHERE campaign_id = p_campaign_id
3736 AND status_code <> 'CANCELLED'
3737 AND status_code <> 'ARCHIVED';
3738
3739 l_schedule_count NUMBER;
3740 -- l_schedule_id NUMBER ;
3741 -- l_obj_version NUMBER ;
3742 -- l_status_code VARCHAR2(30) ;
3743 -- l_status_id NUMBER := AMS_Utility_PVT.get_default_user_status('AMS_CAMPAIGN_SCHEDULE_STATUS','CANCELLED');
3744
3745 BEGIN
3746
3747 OPEN c_schedule ;
3748 FETCH c_schedule INTO l_schedule_count;
3749 CLOSE c_schedule ;
3750
3751 IF (AMS_DEBUG_HIGH_ON) THEN
3752
3753
3754
3755 AMS_Utility_PVT.debug_message('SONALI: l_schedule_count is *' || l_schedule_count ||'*');
3756
3757 END IF;
3758 IF l_schedule_count > 0
3759 THEN
3760 -- cannot cancel
3761 AMS_Utility_PVT.Error_Message('AMS_CSCH_CANNOT_CANCEL');
3762 RAISE FND_API.g_exc_error;
3763 /*
3764 ELSE
3765 -- ok cancel
3766 UPDATE ams_campaigns_all_b
3767 SET status_code = 'CANCELLED',
3768 user_status_id = l_program_status_id ,
3769 status_date = SYSDATE ,
3770 -- object_version_number = l_obj_version + 1
3771 WHERE campaign_id = l_campaign_id ;
3772 --AND object_version_number = l_obj_version ;
3773 IF (SQL%NOTFOUND) THEN
3774 CLOSE c_campaign ;
3775 AMS_Utility_PVT.Error_Message('AMS_API_RECORD_NOT_FOUND');
3776 RAISE FND_API.g_exc_error;
3777 END IF;
3778 */
3779
3780 END IF;
3781
3782 END Cancel_Schedule;
3783
3784 --==========================================================================
3785 -- PROCEDURE
3786 -- Cancel_Program
3787 --
3788 -- PURPOSE
3789 -- Cancel All the associated campaigns. If the campaign can not be
3790 -- canceled, error out .If the campaign can be cancelled, cancel all
3791 -- the schedules too.
3792 --
3793 -- NOTES
3794 -- HISTORY
3795 -- 23-May-2001 ptendulk Created.
3796 -- 15-feb-2002 soagrawa Logic modified by soagrawa to fix bug# 2218013
3797 -- Before: Cancel a program => cancel all components
3798 -- Cancel a campaign => cancel all schedules
3799 -- Now: Cancel only if children are cancelled/archived
3800 --==========================================================================
3801
3802 /*
3803 PROCEDURE Cancel_Program(
3804 p_program_id IN NUMBER
3805 ) IS
3806
3807 CURSOR c_campaign IS
3808 SELECT campaign_id, object_version_number, rollup_type, status_code
3809 FROM ams_campaigns_all_b
3810 WHERE parent_campaign_id = p_program_id
3811 AND status_code <> 'CANCELLED' ;
3812
3813 l_campaign_id NUMBER ;
3814 l_obj_version NUMBER ;
3815 l_rollup_type VARCHAR2(30) ;
3816 l_status_code VARCHAR2(30) ;
3817 l_program_status_id NUMBER := AMS_Utility_PVT.get_default_user_status('AMS_PROGRAM_STATUS','CANCELLED');
3818 l_campaign_status_id NUMBER := AMS_Utility_PVT.get_default_user_status('AMS_CAMPAIGN_STATUS','CANCELLED');
3819
3820 BEGIN
3821
3822 OPEN c_campaign ;
3823 LOOP
3824 FETCH c_campaign INTO l_campaign_id, l_obj_version, l_rollup_type,l_status_code ;
3825 EXIT WHEN c_campaign%NOTFOUND ;
3826
3827 IF l_rollup_type = 'RCAM' THEN
3828 Cancel_Program(l_campaign_id) ;
3829 IF FND_API.G_TRUE = AMS_Utility_PVT.Check_Status_Change('AMS_PROGRAM_STATUS',l_status_code,'CANCELLED') THEN
3830 UPDATE ams_campaigns_all_b
3831 SET status_code = 'CANCELLED',
3832 user_status_id = l_program_status_id ,
3833 status_date = SYSDATE ,
3834 object_version_number = l_obj_version + 1
3835 WHERE campaign_id = l_campaign_id
3836 AND object_version_number = l_obj_version ;
3837 IF (SQL%NOTFOUND) THEN
3838 CLOSE c_campaign ;
3839 AMS_Utility_PVT.Error_Message('AMS_API_RECORD_NOT_FOUND');
3840 RAISE FND_API.g_exc_error;
3841 END IF;
3842 ELSE
3843 CLOSE c_campaign;
3844 AMS_Utility_PVT.Error_Message('AMS_PROG_CANNOT_CANCEL');
3845 RAISE FND_API.g_exc_error;
3846 END IF ;
3847 ELSE
3848 IF FND_API.G_TRUE = AMS_Utility_PVT.Check_Status_Change('AMS_CAMPAIGN_STATUS',l_status_code,'CANCELLED') THEN
3849 Cancel_schedule(l_campaign_id);
3850 UPDATE ams_campaigns_all_b
3851 SET status_code = 'CANCELLED',
3852 user_status_id = l_campaign_status_id ,
3853 status_date = SYSDATE ,
3854 object_version_number = l_obj_version + 1
3855 WHERE campaign_id = l_campaign_id
3856 AND object_version_number = l_obj_version ;
3857
3858 IF (SQL%NOTFOUND) THEN
3859 CLOSE c_campaign ;
3860 AMS_Utility_PVT.Error_Message('AMS_API_RECORD_NOT_FOUND');
3861 RAISE FND_API.g_exc_error;
3862 END IF;
3863 ELSE
3864 CLOSE c_campaign;
3865 AMS_Utility_PVT.Error_Message('AMS_CAMP_CANNOT_CANCEL');
3866 RAISE FND_API.g_exc_error;
3867 END IF ;
3868 END IF;
3869 END LOOP ;
3870 CLOSE c_campaign ;
3871
3872
3873 END Cancel_Program;
3874 */
3875
3876
3877 PROCEDURE Cancel_Program(
3878 p_program_id IN NUMBER
3879 ) IS
3880
3881 -- cursor sees if for given program there are any components that are not cancelled / archived
3882 CURSOR c_campaign IS
3883 SELECT count(*)
3884 FROM ams_campaigns_all_b
3885 WHERE parent_campaign_id = p_program_id
3886 AND status_code <> 'CANCELLED'
3887 AND status_code <> 'ARCHIVED';
3888
3889 l_camp_count NUMBER;
3890 -- l_program_status_id NUMBER := AMS_Utility_PVT.get_default_user_status('AMS_PROGRAM_STATUS','CANCELLED');
3891 -- l_campaign_status_id NUMBER := AMS_Utility_PVT.get_default_user_status('AMS_CAMPAIGN_STATUS','CANCELLED');
3892
3893
3894 -- l_campaign_id NUMBER ;
3895 -- l_obj_version NUMBER ;
3896 -- l_rollup_type VARCHAR2(30) ;
3897 -- l_status_code VARCHAR2(30) ;
3898
3899 BEGIN
3900
3901 OPEN c_campaign ;
3902 FETCH c_campaign INTO l_camp_count;
3903 CLOSE c_campaign ;
3904
3905 IF (AMS_DEBUG_HIGH_ON) THEN
3906
3907
3908
3909 AMS_Utility_PVT.debug_message('SONALI: l_camp_count is *' || l_camp_count ||'*');
3910
3911 END IF;
3912 IF l_camp_count > 0
3913 THEN
3914 -- cannot cancel
3915 AMS_Utility_PVT.Error_Message('AMS_COMP_CANNOT_CANCEL');
3916 RAISE FND_API.g_exc_error;
3917 /*
3918 ELSE
3919 -- ok cancel
3920 UPDATE ams_campaigns_all_b
3921 SET status_code = 'CANCELLED',
3922 user_status_id = l_program_status_id ,
3923 status_date = SYSDATE ,
3924 -- object_version_number = l_obj_version + 1
3925 WHERE campaign_id = l_campaign_id ;
3926 --AND object_version_number = l_obj_version ;
3927 IF (SQL%NOTFOUND) THEN
3928 CLOSE c_campaign ;
3929 AMS_Utility_PVT.Error_Message('AMS_API_RECORD_NOT_FOUND');
3930 RAISE FND_API.g_exc_error;
3931 END IF;
3932 */
3933
3934 END IF;
3935
3936 END Cancel_Program;
3937
3938
3939
3940 --==========================================================================
3941 -- PROCEDURE
3942 -- Complete_Schedule
3943 --
3944 -- PURPOSE
3945 -- Completes all the schedules associated to the campaign. If the status
3946 -- order rules does not permit it, it will error out. This api is similar
3947 -- to the Cancel_Schedule api , only reason to write it seperately is to
3948 -- keep the logic of the complete and cancel status seperate, So that if
3949 -- there is any change , the apis can be modified seperately.
3950 --
3951 -- NOTES
3952 -- HISTORY
3953 -- 09-Jul-2001 ptendulk Created.
3954 -- 15-may-2003 soagrawa Modified code to fix bug# 2962164
3955 --=======================================================================
3956
3957 PROCEDURE Complete_Schedule(p_campaign_id IN NUMBER) IS
3958
3959 CURSOR c_schedule IS
3960 -- soagrawa added columns to this cursor on 15-may-2003 for bug# 2962164
3961 SELECT schedule_id,object_version_number,status_code, activity_type_code, related_event_id, source_code
3962 FROM ams_campaign_schedules_b
3963 WHERE campaign_id = p_campaign_id
3964 -- asaha added more status check for bug 3142886
3965 AND status_code NOT IN ('COMPLETED','CANCELLED','CLOSED','ARCHIVED') ;
3966
3967 -- new cursor created by asaha for bug 3132886
3968 CURSOR c_completed_schedule IS
3969 SELECT count(*)
3970 FROM ams_campaign_schedules_b
3971 WHERE campaign_id = p_campaign_id
3972 AND status_code IN ('COMPLETED','CLOSED') ;
3973
3974 -- mayjain 11-Oct-2005 Bug 4401237
3975 CURSOR c_no_of_schedules IS
3976 SELECT count(1)
3977 FROM ams_campaign_schedules_b
3978 WHERE campaign_id = p_campaign_id ;
3979
3980 l_schedule_id NUMBER ;
3981 l_obj_version NUMBER ;
3982 l_status_code VARCHAR2(30) ;
3983 l_status_id NUMBER := AMS_Utility_PVT.get_default_user_status('AMS_CAMPAIGN_SCHEDULE_STATUS','COMPLETED');
3984 -- soagrawa added the following on 15-may-2003 for bug# 2962164
3985 l_activity_type_code VARCHAR2(30);
3986 l_related_event_id NUMBER;
3987 l_source_code VARCHAR2(30);
3988 l_no_complete_scheds NUMBER;
3989 l_no_of_scheds NUMBER ;
3990
3991
3992 BEGIN
3993
3994 OPEN c_schedule ;
3995 LOOP
3996 FETCH c_schedule
3997 INTO l_schedule_id,l_obj_version,l_status_code, l_activity_type_code, l_related_event_id, l_source_code ;
3998 EXIT WHEN c_schedule%NOTFOUND ;
3999 IF FND_API.G_TRUE = AMS_Utility_PVT.Check_Status_Change('AMS_CAMPAIGN_SCHEDULE_STATUS',l_status_code,'COMPLETED') THEN
4000 IF (AMS_DEBUG_HIGH_ON) THEN
4001 AMS_Utility_PVT.debug_message('MAYANK: l_schedule_id is *' || l_schedule_id ||'*');
4002 AMS_Utility_PVT.debug_message('MAYANK: l_obj_version is *' || l_obj_version ||'*');
4003 END IF;
4004
4005 -- Can complete the schedule
4006 UPDATE ams_campaign_schedules_b
4007 SET status_code = 'COMPLETED',
4008 status_date = SYSDATE,
4009 user_status_id = l_status_id,
4010 object_version_number = object_version_number + 1
4011 WHERE schedule_id = l_schedule_id
4012 AND object_version_number = l_obj_version ;
4013
4014 IF (SQL%NOTFOUND) THEN
4015 CLOSE c_schedule ;
4016 AMS_Utility_PVT.Error_Message('AMS_API_RECORD_NOT_FOUND');
4017 RAISE FND_API.g_exc_error;
4018 END IF;
4019
4020 -- soagrawa added the following on 15-may-2003 for bug# 2962164
4021 IF l_activity_type_code = 'EVENTS'
4022 THEN
4023 AMS_EvhRules_PVT.process_leads(p_event_id => l_related_event_id
4024 , p_obj_type => 'CSCH'
4025 , p_obj_srccd => l_source_code);
4026 END IF;
4027
4028
4029 ELSE -- Can not complete the schedule as the status is can not go to complete from current status
4030 CLOSE c_schedule;
4031 AMS_Utility_PVT.Error_Message('AMS_CSCH_CANNOT_COMPLETE');
4032 RAISE FND_API.g_exc_error;
4033 END IF ;
4034
4035 END LOOP;
4036 CLOSE c_schedule;
4037
4038 -- check added by asaha for bug 3132886
4039 OPEN c_completed_schedule;
4040 FETCH c_completed_schedule INTO l_no_complete_scheds;
4041 CLOSE c_completed_schedule;
4042
4043 -- mayjain 11-Oct-2005 Bug 4401237
4044 OPEN c_no_of_schedules;
4045 FETCH c_no_of_schedules INTO l_no_of_scheds;
4046 CLOSE c_no_of_schedules;
4047
4048 -- mayjain 11-Oct-2005 Bug 4401237
4049 IF (l_no_of_scheds > 0) THEN -- There should be atleast one schedule to make the next check.
4050 IF(l_no_complete_scheds = 0) THEN
4051 -- at least 1 completed Schedule is required for the Campaign to be complete
4052 CLOSE c_completed_schedule;
4053 AMS_Utility_PVT.Error_Message('AMS_CSCH_CANNOT_COMPLETE');
4054 RAISE FND_API.g_exc_error;
4055 END IF;
4056 END IF;
4057
4058
4059 END Complete_Schedule;
4060
4061 --==========================================================================
4062 -- PROCEDURE
4063 -- Complete_Program
4064 --
4065 -- PURPOSE
4066 -- Completes All the associated campaigns. If the campaign can not be
4067 -- completed, error out .If the campaign can be completed, complete all
4068 -- the schedules too.This api is similar
4069 -- to the Cancel_Program api , only reason to write it seperately is to
4070 -- keep the logic of the complete and cancel status seperate, So that if
4071 -- there is any change , the apis can be modified seperately.
4072 --
4073 -- NOTES
4074 -- HISTORY
4075 -- 23-May-2001 ptendulk Created.
4076 -- 15-may-2003 soagrawa Modified code to fix bug# 2962702
4077 --==========================================================================
4078
4079 PROCEDURE Complete_Program(
4080 p_program_id IN NUMBER
4081 ) IS
4082
4083 CURSOR c_campaign IS
4084 SELECT campaign_id, object_version_number, rollup_type, status_code
4085 FROM ams_campaigns_all_b
4086 WHERE parent_campaign_id = p_program_id
4087 AND status_code <> 'COMPLETED' ;
4088
4089 l_campaign_id NUMBER ;
4090 l_obj_version NUMBER ;
4091 l_rollup_type VARCHAR2(30) ;
4092 l_status_code VARCHAR2(30) ;
4093 l_program_status_id NUMBER := AMS_Utility_PVT.get_default_user_status('AMS_PROGRAM_STATUS','COMPLETED');
4094 l_campaign_status_id NUMBER := AMS_Utility_PVT.get_default_user_status('AMS_CAMPAIGN_STATUS','COMPLETED');
4095
4096 BEGIN
4097
4098 OPEN c_campaign ;
4099 LOOP
4100 FETCH c_campaign INTO l_campaign_id, l_obj_version, l_rollup_type,l_status_code ;
4101 EXIT WHEN c_campaign%NOTFOUND ;
4102
4103 IF l_rollup_type = 'RCAM' THEN
4104 -- soagrawa 15-may-2003 modified for bug# 2962702
4105 -- Cancel_Program(l_campaign_id) ;
4106 Complete_Program(l_campaign_id) ;
4107 IF FND_API.G_TRUE = AMS_Utility_PVT.Check_Status_Change('AMS_PROGRAM_STATUS',l_status_code,'COMPLETED') THEN
4108 UPDATE ams_campaigns_all_b
4109 SET status_code = 'COMPLETED',
4110 user_status_id = l_program_status_id ,
4111 status_date = SYSDATE ,
4112 object_version_number = l_obj_version + 1
4113 WHERE campaign_id = l_campaign_id
4114 AND object_version_number = l_obj_version ;
4115 IF (SQL%NOTFOUND) THEN
4116 CLOSE c_campaign ;
4117 AMS_Utility_PVT.Error_Message('AMS_API_RECORD_NOT_FOUND');
4118 RAISE FND_API.g_exc_error;
4119 END IF;
4120 ELSE
4121 CLOSE c_campaign;
4122 AMS_Utility_PVT.Error_Message('AMS_PROG_CANNOT_COMPLETE');
4123 RAISE FND_API.g_exc_error;
4124 END IF ;
4125 ELSE
4126 IF FND_API.G_TRUE = AMS_Utility_PVT.Check_Status_Change('AMS_CAMPAIGN_STATUS',l_status_code,'COMPLETED') THEN
4127 -- soagrawa 15-may-2003 modified for bug# 2962702
4128 -- Cancel_schedule(l_campaign_id) ;
4129 Complete_Schedule(l_campaign_id) ;
4130
4131 UPDATE ams_campaigns_all_b
4132 SET status_code = 'COMPLETED',
4133 user_status_id = l_campaign_status_id ,
4134 status_date = SYSDATE ,
4135 object_version_number = l_obj_version + 1
4136 WHERE campaign_id = l_campaign_id
4137 AND object_version_number = l_obj_version ;
4138
4139 IF (SQL%NOTFOUND) THEN
4140 CLOSE c_campaign ;
4141 AMS_Utility_PVT.Error_Message('AMS_API_RECORD_NOT_FOUND');
4142 RAISE FND_API.g_exc_error;
4143 END IF;
4144 ELSE
4145 CLOSE c_campaign;
4146 AMS_Utility_PVT.Error_Message('AMS_CAMP_CANNOT_COMPLETE');
4147 RAISE FND_API.g_exc_error;
4148 END IF ;
4149 END IF;
4150 END LOOP ;
4151 CLOSE c_campaign ;
4152
4153
4154 END Complete_Program;
4155
4156 -- PROCEDURE
4157 -- Get_Event_Source_Code
4158 --
4159 -- PURPOSE
4160 -- Get the source code for the related event associated to the campaign.
4161 --
4162 -- NOTES
4163 -- HISTORY
4164 -- 22-May-2001 ptendulk Created.
4165 -- 08-Oct-2001 ptendulk Modified cursor queries for event offers and one off.
4166 -----------------------------------------------------------------------
4167
4168 FUNCTION Get_Event_Source_Code(
4169 p_event_type VARCHAR2,
4170 p_event_id NUMBER
4171 ) RETURN VARCHAR2
4172 IS
4173 --Added by rrajesh on 04/13/01 - to update realted_event fields
4174 CURSOR c_fetch_sourcecode_for_eveh IS
4175 SELECT source_code
4176 FROM ams_event_headers_all_b
4177 WHERE event_header_id = p_event_id ;
4178
4179 CURSOR c_fetch_sourcecode_for_eveo IS
4180 SELECT source_code
4181 FROM ams_event_offers_all_b
4182 WHERE event_offer_id = p_event_id
4183 --AND event_standalone_flag = 'N';
4184 AND event_object_type = 'EVEO';
4185
4186 CURSOR c_fetch_sourcecode_for_eone IS
4187 SELECT source_code
4188 FROM ams_event_offers_all_b
4189 WHERE event_offer_id = p_event_id
4190 --AND event_standalone_flag = 'Y';
4191 AND event_object_type = 'EONE';
4192
4193 l_source_code VARCHAR2(30) ;
4194
4195 BEGIN
4196
4197 IF p_event_type = 'EVEH' THEN
4198 OPEN c_fetch_sourcecode_for_eveh;
4199 FETCH c_fetch_sourcecode_for_eveh INTO l_source_code;
4200 CLOSE c_fetch_sourcecode_for_eveh;
4201 ELSIF p_event_type = 'EVEO' THEN
4202 OPEN c_fetch_sourcecode_for_eveo;
4203 FETCH c_fetch_sourcecode_for_eveo INTO l_source_code;
4204 CLOSE c_fetch_sourcecode_for_eveo;
4205 ELSIF p_event_type = 'EONE' THEN
4206 OPEN c_fetch_sourcecode_for_eone;
4207 FETCH c_fetch_sourcecode_for_eone INTO l_source_code;
4208 CLOSE c_fetch_sourcecode_for_eone;
4209 ELSE
4210 l_source_code := NULL ;
4211 END IF;
4212 RETURN l_source_code ;
4213 END Get_Event_Source_Code ;
4214
4215 --=====================================================================
4216 -- PROCEDURE
4217 -- Update_Rollup
4218 --
4219 -- PURPOSE
4220 -- The api is created to update the rollup for the metrics if the
4221 -- parent of the campaign is changed
4222 --
4223 -- HISTORY
4224 -- 31-May-2001 ptendulk Created.
4225 --=====================================================================
4226 PROCEDURE Update_Rollup(
4227 p_api_version IN NUMBER,
4228 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
4229 p_commit IN VARCHAR2 := FND_API.g_false,
4230 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
4231 x_return_status OUT NOCOPY VARCHAR2,
4232 x_msg_count OUT NOCOPY NUMBER,
4233 x_msg_data OUT NOCOPY VARCHAR2,
4234 p_campaign_id IN NUMBER,
4235 p_parent_id IN NUMBER )
4236 IS
4237 CURSOR c_parent IS
4238 SELECT parent_campaign_id, DECODE(rollup_type,'RCAM','RCAM','CAMP')
4239 FROM ams_campaigns_all_b
4240 WHERE campaign_id = p_campaign_id ;
4241 l_old_parent NUMBER ;
4242 l_rollup_type VARCHAR2(30) ;
4243
4244 BEGIN
4245 IF (AMS_DEBUG_HIGH_ON) THEN
4246
4247 AMS_Utility_PVT.Debug_message('Start Update rollup ');
4248 END IF;
4249 OPEN c_parent ;
4250 FETCH c_parent INTO l_old_parent,l_rollup_type ;
4251 IF c_parent%NOTFOUND THEN
4252 CLOSE c_parent;
4253 AMS_Utility_Pvt.Error_Message('AMS_API_RECORD_NOT_FOUND');
4254 RAISE FND_API.g_exc_error;
4255 END IF;
4256 CLOSE c_parent ;
4257
4258 IF l_old_parent IS NOT NULL THEN
4259 IF p_parent_id IS NULL OR
4260 p_parent_id <> l_old_parent
4261 THEN
4262 IF (AMS_DEBUG_HIGH_ON) THEN
4263
4264 AMS_Utility_PVT.Debug_message('Invalidate the rollup ');
4265 END IF;
4266 -- Change p_used_by_type to l_rollup_type when gliu resolve the
4267 -- issue for rollup type of seed metric for the program /campaign
4268 -- as of Jun01-2001
4269 AMS_ACTMETRIC_PUB.Invalidate_Rollup(
4270 p_api_version => p_api_version ,
4271 p_init_msg_list => p_init_msg_list,
4272 p_commit => p_commit,
4273
4274 x_return_status => x_return_status,
4275 x_msg_count => x_msg_count,
4276 x_msg_data => x_msg_data,
4277
4278 -- Following line is commented
4279 --p_used_by_type => 'CAMP',
4280 p_used_by_type => l_rollup_type,
4281 p_used_by_id => p_campaign_id
4282 );
4283 END IF ;
4284 END IF;
4285
4286 END Update_Rollup ;
4287
4288 --========================================================================
4289 -- PROCEDURE
4290 -- Update_Status
4291 --
4292 -- PURPOSE
4293 -- This api is called in Update campaign api (and in approvals' api)
4294 --
4295 -- NOTE
4296 --
4297 -- HISTORY
4298 -- 26-Sep-2001 soagrawa Created.
4299 -- 07-SEP-2003 asaha Disabled Update of private_flag to N when
4300 -- Campaign goes active
4301 --========================================================================
4302 PROCEDURE update_status( p_campaign_id IN NUMBER,
4303 p_new_status_id IN NUMBER,
4304 p_new_status_code IN VARCHAR2
4305 )
4306 IS
4307
4308 BEGIN
4309 UPDATE ams_campaigns_all_b
4310 SET user_status_id = p_new_status_id,
4311 status_code = p_new_status_code, -- AMS_Utility_PVT.get_system_status_code(p_new_status_id),
4312 status_date = SYSDATE
4313 -- private_flag = DECODE(p_new_status_code,'ACTIVE','N',private_flag)
4314 WHERE campaign_id = p_campaign_id;
4315
4316 IF p_new_status_code = 'ACTIVE' THEN
4317 activate_campaign(p_campaign_id => p_campaign_id);
4318 END IF ;
4319
4320 END update_status;
4321
4322 --========================================================================
4323 -- PROCEDURE
4324 -- Check_Children_Tree
4325 --
4326 -- PURPOSE
4327 -- This api is to check if the hierarchy for the parent child camp is
4328 -- valid. It validates that parent campaign is not one of the
4329 -- childrens of the campaign.
4330 --
4331 -- NOTE
4332 --
4333 -- HISTORY
4334 -- 25-Oct-2001 ptendulk Created.
4335 --
4336 --========================================================================
4337 PROCEDURE Check_Children_Tree(p_campaign_id IN NUMBER,
4338 p_parent_campaign_id IN NUMBER
4339 )
4340 IS
4341 CURSOR c_child_tree IS
4342 SELECT campaign_id
4343 FROM ams_Campaigns_all_B
4344 WHERE active_flag = 'Y'
4345 START WITH campaign_id = p_campaign_id
4346 CONNECT BY PRIOR campaign_id = parent_campaign_id ;
4347 l_camp_id NUMBER ;
4348
4349 BEGIN
4350
4351 OPEN c_child_tree ;
4352 LOOP
4353 FETCH c_child_tree INTO l_camp_id ;
4354 EXIT WHEN c_child_tree%NOTFOUND ;
4355 IF l_camp_id = p_parent_campaign_id THEN
4356 CLOSE c_child_tree;
4357 AMS_Utility_PVT.Error_Message('AMS_CAMP_PARENT_IS_CHILD');
4358 RAISE FND_API.g_exc_error;
4359 END IF ;
4360
4361 END LOOP;
4362 CLOSE c_child_tree;
4363
4364 END Check_Children_Tree;
4365
4366 --==========================================================================
4367 -- PROCEDURE
4368 -- Check_Close_Campaign
4369 --
4370 -- PURPOSE
4371 -- This procedure is used to check whether the campaign can be closed.
4372 -- All the schedules under this campaign are checked.
4373 -- The campaign will be closed only if all the schedules under the campaign is closed.
4374 --
4375 -- NOTES
4376 -- HISTORY
4377 -- Created by Prageorg on 4/10/2006 to fix Bug 4263210
4378 --=======================================================================
4379
4380 PROCEDURE Check_Close_Campaign(p_campaign_id IN NUMBER) IS
4381
4382 CURSOR c_no_of_open_schedules IS
4383 SELECT count(1)
4384 FROM ams_campaign_schedules_b
4385 WHERE campaign_id = p_campaign_id
4386 AND status_code IN ('ACTIVE','AVAILABLE');
4387
4388 l_no_open_scheds NUMBER;
4389
4390
4391 BEGIN
4392
4393 OPEN c_no_of_open_schedules;
4394 FETCH c_no_of_open_schedules INTO l_no_open_scheds;
4395 CLOSE c_no_of_open_schedules;
4396
4397 IF (l_no_open_scheds > 0) THEN
4398 AMS_Utility_PVT.Error_Message('AMS_CSCH_CANNOT_CLOSE');
4399 RAISE FND_API.g_exc_error;
4400 END IF;
4401
4402
4403 END Check_Close_Campaign;
4404
4405 END AMS_CampaignRules_PVT;