[Home] [Help]
PACKAGE BODY: APPS.AMS_SCHEDULERULES_PVT
Source
1 PACKAGE BODY AMS_ScheduleRules_PVT AS
2 /* $Header: amsvsbrb.pls 120.35 2011/09/06 05:22:25 annsrini ship $ */
3
4
5 g_pkg_name CONSTANT VARCHAR2(30):='AMS_ScheduleRules_PVT';
6 g_log_level CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
7 --========================================================================
8 -- FUNCTION
9 -- Target_Group_Exist
10 -- Purpose
11 -- Created to check if the target group exist or not.
12 -- HISTORY
13 -- 19-Jan-2000 ptendulk Created.
14 -- 31-jan-2002 soagrawa Modified signature to take used_by as well
15 -- so that events team could use it too
16 -- This is related to fix for bug# 2207286
17 --========================================================================
18 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
19 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
20 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
21
22
23
24 FUNCTION Target_Group_Exist (p_schedule_id IN NUMBER --)
25 , p_obj_type IN VARCHAR2 :='CSCH')
26 RETURN VARCHAR2
27 IS
28 CURSOR c_target_det
29 IS SELECT 1
30 FROM ams_act_lists la
31 WHERE list_act_type = 'TARGET'
32 AND list_used_by = p_obj_type --'CSCH'
33 AND list_used_by_id = p_schedule_id
34 AND EXISTS (SELECT *
35 FROM ams_list_entries le
36 WHERE le.list_header_id = la.list_header_id) ;
37 l_dummy NUMBER ;
38 BEGIN
39 OPEN c_target_det ;
40 FETCH c_target_det INTO l_dummy ;
41 CLOSE c_target_det ;
42
43 IF l_dummy IS NULL THEN
44 RETURN FND_API.g_false ;
45 ELSE
46 RETURN FND_API.g_true;
47 END IF;
48
49 END Target_Group_Exist ;
50
51 --========================================================================
52 -- PROCEDURE
53 -- Handle_Status
54 -- Purpose
55 -- Created to get the system status code for the user status id
56 -- HISTORY
57 -- 19-Jan-2000 ptendulk Created.
58 --
59 --========================================================================
60 PROCEDURE Handle_Status(
61 p_user_status_id IN NUMBER,
62 p_sys_status_code IN VARCHAR2,
63 x_status_code OUT NOCOPY VARCHAR2,
64 x_return_status OUT NOCOPY VARCHAR2
65 )
66 IS
67
68 l_status_code VARCHAR2(30);
69
70 CURSOR c_status_code IS
71 SELECT system_status_code
72 FROM ams_user_statuses_vl
73 WHERE user_status_id = p_user_status_id
74 AND system_status_type = p_sys_status_code
75 AND enabled_flag = 'Y';
76
77 BEGIN
78
79 x_return_status := FND_API.g_ret_sts_success;
80
81 OPEN c_status_code;
82 FETCH c_status_code INTO l_status_code;
83 CLOSE c_status_code;
84
85 IF l_status_code IS NULL THEN
86 x_return_status := FND_API.g_ret_sts_error;
87 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_USER_STATUS');
88 END IF;
89
90 x_status_code := l_status_code;
91
92 END Handle_Status;
93
94
95 --========================================================================
96 -- PROCEDURE
97 -- validate_activation_rules
98 -- Purpose
99 -- Created to validate the activation rules going forward in R12
100 -- HISTORY
101 -- 27-Jul-2005 anchaudh Created.
102 --
103 --========================================================================
104 PROCEDURE validate_activation_rules(
105 p_scheduleid IN NUMBER,
106 x_status_code OUT NOCOPY VARCHAR2
107 )
108 IS
109 l_status_code VARCHAR2(30);
110 l_return_status VARCHAR2(1);
111 l_msg_count NUMBER;
112 l_msg_data VARCHAR2(2000);
113
114 BEGIN
115
116 SAVEPOINT validate_activation_rules;
117
118 -- Initialize API return status to SUCCESS
119 x_status_code := FND_API.G_RET_STS_SUCCESS;
120
121 AMS_ScheduleRules_PVT.collateral_activation_rule(
122 p_scheduleid => p_scheduleid,
123 x_status_code => l_return_status,
124 x_msg_count => l_msg_count,
125 x_msg_data => l_msg_data) ;
126
127 IF l_return_status = fnd_api.g_ret_sts_error THEN
128 x_status_code := FND_API.g_ret_sts_error;
129 RAISE FND_API.g_exc_error;
130 END IF;
131
132 --similarly, call other validation apis also, as the api validate_activation_rules itself is just a placeholder
133 -- for other valiation rules api like collateral,collaboration validations etc.
134
135 /*AMS_Collab_assoc_PVT .IS_COLLAB_CONTENT_APPROVED (p_schedule_id => p_schedule_id,
136 x_return_status => l_return_status,
137 x_msg_count => l_msg_count,
138 x_msg_data => l_msg_data) ;
139
140 IF l_return_status = fnd_api.g_ret_sts_error THEN
141 x_status_code := FND_API.g_ret_sts_error;
142 RAISE FND_API.g_exc_error;
143 END IF;*/
144
145 AMS_WEBMARKETING_PVT.WEBMARKETING_CONTENT_STATUS (p_campaign_activity_id => p_scheduleid,
146 x_return_status => l_return_status,
147 x_msg_count => l_msg_count,
148 x_msg_data => l_msg_data);
149
150 IF l_return_status = fnd_api.g_ret_sts_error THEN
151 x_status_code := FND_API.g_ret_sts_error;
152 RAISE FND_API.g_exc_error;
153 END IF;
154
155 EXCEPTION
156
157 WHEN FND_API.G_EXC_ERROR THEN
158
159 ROLLBACK TO validate_activation_rules;
160
161 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
162
163 ROLLBACK TO validate_activation_rules;
164
165 WHEN OTHERS THEN
166
167 ROLLBACK TO validate_activation_rules;
168
169
170 END validate_activation_rules;
171
172
173 --=================================================================================
174 -- PROCEDURE
175 -- collateral_activation_rule
176 -- Purpose
177 -- Created to validate the collateral content status before activity activation
178 -- HISTORY
179 -- 27-Jul-2005 anchaudh Created.
180 --
181 --=================================================================================
182 PROCEDURE collateral_activation_rule(
183 p_scheduleid IN NUMBER,
184 x_status_code OUT NOCOPY VARCHAR2,
185 x_msg_count OUT NOCOPY NUMBER,
186 x_msg_data OUT NOCOPY VARCHAR2
187 )
188 IS
189 l_status_code VARCHAR2(30);
190 l_content_name_text VARCHAR2 (2000):= null;
191 l_content_name_text_dm VARCHAR2 (2000):= null;
192 l_content_item_id NUMBER;
193 l_content_item_exists_id NUMBER;
194 l_content_item_exists_ndm_id NUMBER;
195 l_content_item_id_dm NUMBER;
196 l_activity_id NUMBER;
197 l_activity_type_code VARCHAR2(30);
198 l_content_name VARCHAR2 (2000);
199 l_content_name_dm VARCHAR2 (2000);
200 l_content_name_exists VARCHAR2 (2000);
201 l_content_exists VARCHAR2(1) := 'Y';
202 l_content_exists_ndm VARCHAR2(1) := 'Y';
203
204
205 CURSOR c_sched_details IS
206 SELECT activity_id,activity_type_code
207 FROM ams_campaign_schedules_b
208 WHERE schedule_id = p_scheduleid;
209
210 CURSOR C_Content_CL( l_obj_id IN NUMBER) IS
211 SELECT ibcassn.content_item_id,citm.name
212 FROM IBC_ASSOCIATIONS IbcAssn,ibc_citems_v citm
213 WHERE IbcAssn.ASSOCIATED_OBJECT_VAL1 = to_char(l_obj_id )
214 AND IbcAssn.Content_item_id = citm.citem_id
215 AND citm.item_status <> 'APPROVED'
216 AND ibcassn.ASSOCIATION_TYPE_CODE = 'AMS_CSCH' ;
217
218 CURSOR C_Content_NDM( l_obj_id IN NUMBER) IS
219 SELECT ibcassn.content_item_id,citm.name
220 FROM IBC_ASSOCIATIONS IbcAssn,ibc_citems_v citm
221 WHERE IbcAssn.ASSOCIATED_OBJECT_VAL1 = to_char(l_obj_id )
222 AND IbcAssn.Content_item_id = citm.citem_id
223 AND citm.item_status <> 'APPROVED'
224 AND ibcassn.ASSOCIATION_TYPE_CODE = 'AMS_COLLAT'
225 AND citm.VERSION = 1 ;
226
227 CURSOR C_Content_Exists( l_obj_id IN NUMBER) IS
228 SELECT count(1)
229 FROM IBC_ASSOCIATIONS IbcAssn,ibc_citems_v citm
230 WHERE IbcAssn.ASSOCIATED_OBJECT_VAL1 = to_char(l_obj_id )
231 AND IbcAssn.Content_item_id = citm.citem_id
232 AND ibcassn.ASSOCIATION_TYPE_CODE in ('AMS_CSCH') ;
233
234 CURSOR C_Content_Exists_NDM( l_obj_id IN NUMBER) IS
235 SELECT count(1)
236 FROM IBC_ASSOCIATIONS IbcAssn,ibc_citems_v citm
237 WHERE IbcAssn.ASSOCIATED_OBJECT_VAL1 = to_char(l_obj_id )
238 AND IbcAssn.Content_item_id = citm.citem_id
239 AND ibcassn.ASSOCIATION_TYPE_CODE in ('AMS_COLLAT') ;
240
241
242 BEGIN
243
244 l_status_code := FND_API.g_ret_sts_success;
245
246 OPEN c_sched_details ;
247 FETCH c_sched_details INTO l_activity_id,l_activity_type_code;
248 CLOSE c_sched_details ;
249
250 OPEN C_Content_Exists(p_scheduleid);
251 LOOP
252 FETCH C_Content_Exists INTO l_content_item_exists_id;
253 if (l_content_item_exists_id = 0) then
254 l_content_exists := 'N';
255 exit;
256 else
257 l_content_exists := 'Y';
258 exit;
259 end if;
260
261 END LOOP;
262 CLOSE C_Content_Exists;
263
264 OPEN C_Content_Exists_NDM(p_scheduleid);
265 LOOP
266 FETCH C_Content_Exists_NDM INTO l_content_item_exists_ndm_id;
267 if (l_content_item_exists_ndm_id = 0) then
268 l_content_exists_ndm := 'N';
269 exit;
270 else
271 l_content_exists_ndm := 'Y';
272 exit;
273 end if;
274 END LOOP;
275 CLOSE C_Content_Exists_NDM;
276
277
278 if (l_activity_type_code = 'DIRECT_MARKETING') then
279
280 IF (l_content_exists = 'Y') THEN
281
282 --anchaudh : starts : cover letter related validation during activity activation.
283 if (l_activity_id = 10 OR l_activity_id = 20 OR l_activity_id = 480) then
284 OPEN C_Content_CL(p_scheduleid);
285 LOOP
286 FETCH C_Content_CL INTO l_content_item_id,l_content_name;
287 EXIT WHEN C_Content_CL%NOTFOUND;
288 if (C_Content_CL%found) then
289 l_status_code := fnd_api.g_ret_sts_error;
290 end if;
291 END LOOP;
292 CLOSE C_Content_CL;
293
294 If l_status_code = fnd_api.g_ret_sts_error THEN
295 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
296 THEN
297 FND_MESSAGE.set_name('AMS', 'AMS_COVER_LETTER_APPRV_MSG');
298 FND_MESSAGE.Set_Token('COVER_LETTER_NAME',l_content_name);
299 FND_MSG_PUB.add;
300 END IF;
301
302 FND_MSG_PUB.Count_AND_Get( p_count => x_msg_count,
303 p_data => x_msg_data,
304 p_encoded => FND_API.G_FALSE
305 );
306 END IF;
307 end if;
308 --anchaudh : ends : cover letter related validation during activity activation.
309
310 --anchaudh : starts : generic direct marketing collateral content's validation during activity activation.
311
312 IF ((l_activity_type_code = 'DIRECT_MARKETING') AND (l_activity_id <> 10 AND l_activity_id <> 20 AND l_activity_id <> 480)) THEN
313
314 OPEN C_Content_CL(p_scheduleid);
315 LOOP
316 FETCH C_Content_CL INTO l_content_item_id_dm,l_content_name_dm;
317 EXIT WHEN C_Content_CL%NOTFOUND;
318 if (C_Content_CL%found) then
319 l_status_code := fnd_api.g_ret_sts_error;
320
321 if(l_content_name_dm is not null) then
322 if (l_content_name_text_dm is null) then
323 l_content_name_text_dm := l_content_name_dm;
324 else
325 l_content_name_text_dm := l_content_name_text_dm || ',' || l_content_name_dm ;
326 end if;
327 end if;
328
329 end if;
330 END LOOP;
331 CLOSE C_Content_CL;
332
333 If l_status_code = fnd_api.g_ret_sts_error THEN
334 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
335 THEN
336 FND_MESSAGE.set_name('AMS', 'AMS_COLLATEARL_CONTENT_APRVMSG');
337 FND_MESSAGE.Set_Token('COLLATERAL_CONTENT_NAMES', l_content_name_text_dm);
338 FND_MSG_PUB.add;
339 END IF;
340
341 FND_MSG_PUB.Count_AND_Get( p_count => x_msg_count,
342 p_data => x_msg_data,
343 p_encoded => FND_API.G_FALSE
344 );
345 END IF;
346
347 END IF;
348
349 --anchaudh : ends : generic direct marketing collateral content's validation during activity activation.
350
351 ELSIF (l_activity_id <> 460 ) THEN --kbasavar skip the validation for telemarketing
352
353 l_status_code := fnd_api.g_ret_sts_error;
354
355 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
356 THEN
357 FND_MESSAGE.set_name('AMS', 'AMS_COLLAT_CONTENT_NOT_EXISTS');
358 FND_MSG_PUB.add;
359 END IF;
360
361 FND_MSG_PUB.Count_AND_Get( p_count => x_msg_count,
362 p_data => x_msg_data,
363 p_encoded => FND_API.G_FALSE);
364
365 END IF;--IF (l_content_exists = 'Y') THEN
366
367 end if;--if (l_activity_type_code = 'DIRECT_MARKETING')
368
369 if(l_content_exists_ndm = 'Y') then
370 --anchaudh : starts : collateral content in NMD activity related validation during the activity activation.
371 IF ((l_activity_type_code = 'BROADCAST') OR (l_activity_type_code = 'PUBLIC_RELATIONS') OR (l_activity_type_code = 'IN_STORE')) THEN
372 OPEN C_Content_NDM(p_scheduleid);
373 LOOP
374 FETCH C_Content_NDM INTO l_content_item_id,l_content_name;
375 EXIT WHEN C_Content_NDM%NOTFOUND;
376 if (C_Content_NDM%found) then
377 l_status_code := fnd_api.g_ret_sts_error;
378 if(l_content_name is not null) then
379 if (l_content_name_text is null) then
380 l_content_name_text := l_content_name;
381 else
382 l_content_name_text := l_content_name_text || ',' || l_content_name ;
383 end if;
384 end if;
385 end if;
386 END LOOP;
387 CLOSE C_Content_NDM;
388
389 If l_status_code = fnd_api.g_ret_sts_error THEN
390 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
391 THEN
392 FND_MESSAGE.set_name('AMS', 'AMS_COLLATEARL_CONTENT_APRVMSG');
393 FND_MESSAGE.Set_Token('COLLATERAL_CONTENT_NAMES', l_content_name_text);
394 FND_MSG_PUB.add;
395 END IF;
396
397 FND_MSG_PUB.Count_AND_Get( p_count => x_msg_count,
398 p_data => x_msg_data,
399 p_encoded => FND_API.G_FALSE
400 );
401 END IF;
402 END IF;
403 --anchaudh : ends : collateral content in NDM activity related validation during the activity activation.
404 end if;--if(l_content_exists_ndm = 'Y')
405
406
407 x_status_code := l_status_code;
408
409 END collateral_activation_rule;
410
411
412
413 --========================================================================
414 -- FUNCTION
415 -- Generate_Schedule_Code
416 -- Purpose
417 -- Created to generate source code for schedule.
418 --
419 -- Note
420 -- Schedule code is generated using combination
421 -- camp source code + custom setup suffix + unique number
422 --
423 -- HISTORY
424 -- 30-Jan-2000 ptendulk Created.
425 --
426 --========================================================================
427 FUNCTION Generate_Schedule_Code(p_campaign_source_code IN VARCHAR2,
428 p_setup_id IN NUMBER)
429 RETURN VARCHAR2
430 IS
431 PRAGMA AUTONOMOUS_TRANSACTION;
432 CURSOR c_sequence_value IS
433 SELECT gde.scode_number_element
434 FROM ams_generated_codes gde
435 WHERE gde.scode_char_element = p_campaign_source_code
436 FOR UPDATE ;
437
438 CURSOR c_setup_suffix IS
439 SELECT source_code_suffix
440 FROM ams_custom_setups_b
441 WHERE custom_setup_id = p_setup_id ;
442
443 l_suffix VARCHAR2(3);
444 l_seq NUMBER ;
445 l_source_code VARCHAR2(50); --anchaudh bug fix 3861594
446 BEGIN
447 OPEN c_setup_suffix ;
448 FETCH c_setup_suffix INTO l_suffix ;
449 CLOSE c_setup_suffix ;
450
451 IF l_suffix IS NULL
452 THEN
453 l_suffix := '' ;
454 END IF ;
455
456 OPEN c_sequence_value;
457 FETCH c_sequence_value INTO l_seq;
458 CLOSE c_sequence_value;
459
460 IF l_seq IS NULL THEN
461 l_seq := 0 ;
462
463 INSERT INTO ams_generated_codes (
464 gen_code_id,
465 last_update_date,
466 last_updated_by,
467 creation_date,
468 created_by,
469 last_update_login,
470 object_version_number,
471 scode_char_element,
472 scode_number_element,
473 arc_source_code_for
474 ) VALUES (
475 ams_source_codes_gen_s.NEXTVAL,
476 SYSDATE,
477 FND_GLOBAL.user_id,
478 SYSDATE,
479 FND_GLObAL.user_id,
480 FND_GLOBAL.conc_login_id,
481 1, -- object version number
482 p_campaign_source_code,
483 0,
484 'NONE' -- Not generated for any specific object
485 );
486
487 COMMIT;
488 l_source_code := p_campaign_source_code || l_suffix || TO_CHAR(l_seq) ;
489 ELSE
490 -- Update the generate code with the new
491 -- upper limit of the numeric sequence.
492 LOOP
493 l_source_code := p_campaign_source_code || l_suffix || TO_CHAR(l_seq + 1) ;
494 EXIT WHEN AMS_SourceCode_PVT.is_source_code_unique (l_source_code) = FND_API.g_true;
495 l_seq := l_seq + 1 ;
496 END LOOP;
497
498 UPDATE ams_generated_codes gde
499 SET gde.scode_number_element = l_seq + 1
500 WHERE gde.scode_char_element = p_campaign_source_code ;
501 COMMIT ;
502
503 END IF ;
504
505 RETURN l_source_code ;
506
507 END Generate_Schedule_Code;
508
509 --========================================================================
510 -- PROCEDURE
511 -- Handle_Schedule_Source_Code
512 -- Purpose
513 -- Created to get the source code for the schedules.
514 -- HISTORY
515 -- 30-Jan-2000 ptendulk Created.
516 --
517 --========================================================================
518 PROCEDURE Handle_Schedule_Source_Code(
519 p_source_code IN VARCHAR2,
520 p_camp_id IN NUMBER,
521 p_setup_id IN NUMBER,
522 p_cascade_flag IN VARCHAR2,
523 x_source_code OUT NOCOPY VARCHAR2,
524 x_return_status OUT NOCOPY VARCHAR2
525 )
526 IS
527
528 CURSOR c_camp IS
529 SELECT source_code,
530 global_flag
531 FROM ams_campaigns_vl
532 WHERE campaign_id = p_camp_id;
533
534 l_source_code VARCHAR2(30);
535 l_dummy_src_code VARCHAR2(50);
536 l_global_flag VARCHAR2(1);
537
538 BEGIN
539
540 x_source_code := p_source_code;
541 x_return_status := FND_API.g_ret_sts_success;
542
543 OPEN c_camp;
544 FETCH c_camp INTO l_source_code, l_global_flag;
545 IF c_camp%NOTFOUND THEN -- campaign_id is invalid
546 CLOSE c_camp;
547 x_return_status := FND_API.g_ret_sts_error;
548 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_ID');
549 RAISE FND_API.g_exc_error;
550 END IF;
551 CLOSE c_camp;
552
553 IF p_cascade_flag = 'Y' THEN
554 IF p_source_code IS NULL THEN
555 x_source_code := l_source_code;
556 ELSIF p_source_code <> l_source_code THEN
557 x_return_status := FND_API.g_ret_sts_error;
558 AMS_Utility_PVT.error_message('AMS_CSCH_CODE_NOT_CASCADE');
559 END IF;
560 ELSE
561 IF p_source_code IS NULL THEN
562 l_dummy_src_code := Generate_Schedule_Code(l_source_code,p_setup_id);
563 --x_source_code := AMS_SourceCode_PVT.get_new_source_code(
564 -- 'CSCH', p_setup_id, l_global_flag);
565 --anchaudh bug fix 3861594 starts
566 IF(length(l_dummy_src_code) > 30) THEN
567 x_return_status := FND_API.g_ret_sts_error;
568 AMS_Utility_PVT.error_message('AMS_CSCH_SRC_CODE_ERROR');
569 ELSE
570 x_source_code := l_dummy_src_code; --Generate_Schedule_Code(l_source_code,p_setup_id);
571 --x_source_code := AMS_SourceCode_PVT.get_new_source_code(
572 -- 'CSCH', p_setup_id, l_global_flag);
573 END IF;
574 --anchaudh bug fix 3861594 ends
575 ELSIF AMS_SourceCode_PVT.is_source_code_unique(p_source_code) = FND_API.g_false
576 THEN
577 x_return_status := FND_API.g_ret_sts_error;
578 AMS_Utility_PVT.error_message('AMS_CAMP_DUPLICATE_CODE');
579 END IF;
580 END IF;
581
582 END Handle_Schedule_Source_Code;
583
584 --========================================================================
585 -- PROCEDURE
586 -- Push_Source_Code
587 -- Purpose
588 -- Created to push the source code for the schedule
589 -- after the schedule is created.
590 -- HISTORY
591 -- 19-Jan-2000 ptendulk Created.
592 -- 16-May-2001 soagrawa
593 --
594 --========================================================================
595 PROCEDURE Push_Source_Code(
596 p_source_code IN VARCHAR2,
597 p_arc_object IN VARCHAR2,
598 p_object_id IN NUMBER,
599 p_related_source_code IN VARCHAR2 := NULL,
600 p_related_source_object IN VARCHAR2 := NULL,
601 p_related_source_id IN NUMBER := NULL
602 )
603 IS
604
605 l_sourcecode_id NUMBER;
606 l_return_status VARCHAR2(1);
607 l_msg_count NUMBER;
608 l_msg_data VARCHAR2(2000);
609
610 BEGIN
611
612 AMS_SourceCode_PVT.Create_SourceCode(
613 p_api_version => 1.0,
614 p_init_msg_list => FND_API.g_false,
615 p_commit => FND_API.g_false,
616 p_validation_level => FND_API.g_valid_level_full,
617
618 x_return_status => l_return_status,
619 x_msg_count => l_msg_count,
620 x_msg_data => l_msg_data,
621
622 p_sourcecode => p_source_code,
623 p_sourcecode_for => p_arc_object,
624 p_sourcecode_for_id => p_object_id,
625 p_related_sourcecode => p_related_source_code,
626 p_releated_sourceobj => p_related_source_object,
627 p_related_sourceid => p_related_source_id,
628 x_sourcecode_id => l_sourcecode_id
629 );
630
631 IF l_return_status <> FND_API.g_ret_sts_success THEN
632 RAISE FND_API.g_exc_error;
633 END IF;
634
635 END Push_Source_Code;
636
637
638
639 --========================================================================
640 -- PROCEDURE
641 -- Check_Source_Code
642 --
643 -- Purpose
644 -- Created to check the source code for the schedule before updation
645 --
646 -- HISTORY
647 -- 19-Jan-2000 ptendulk Created.
648 -- 12-DEC-2001 soagrawa Logic modified by soagrawa. Bug# 2133264:
649 -- entire procedure rewritten
650 -- 31-jan-2001 soagrawa Fixed code for bug# 2207286 (re: TGRP and source code)
651 --========================================================================
652
653 /*PROCEDURE Check_Source_Code(
654 p_schedule_rec IN AMS_Camp_Schedule_PVT.schedule_rec_type,
655 x_return_status OUT NOCOPY VARCHAR2
656 )
657 IS
658
659 l_cascade_flag VARCHAR2(1);
660 l_source_code VARCHAR2(30);
661 l_camp_id NUMBER;
662 l_dummy NUMBER;
663 l_msg_count NUMBER;
664 l_msg_data VARCHAR2(2000);
665
666 CURSOR c_source_code IS
667 SELECT 1
668 FROM ams_source_codes
669 WHERE source_code = p_schedule_rec.source_code
670 AND active_flag = 'Y';
671
672 CURSOR c_schedule IS
673 SELECT campaign_id, source_code, use_parent_code_flag
674 FROM ams_campaign_schedules_b
675 WHERE schedule_id = p_schedule_rec.schedule_id;
676
677 CURSOR c_list_header IS
678 SELECT 1
679 FROM ams_list_headers_all
680 WHERE arc_list_used_by = 'CSCH'
681 AND list_used_by_id = p_schedule_rec.schedule_id
682 AND status_code <> 'NEW';
683
684 BEGIN
685 IF (AMS_DEBUG_HIGH_ON) THEN
686
687 AMS_Utility_PVT.debug_message('Check Source Code ');
688 END IF;
689 x_return_status := FND_API.g_ret_sts_success;
690
691 -- cannot update to null
692 IF p_schedule_rec.source_code IS NULL THEN
693 AMS_Utility_PVT.Error_Message('AMS_CAMP_NO_SOURCE_CODE');
694 RAISE FND_API.g_exc_error;
695 END IF;
696
697 -- query the campaign_id and the old source_code
698 OPEN c_schedule;
699 FETCH c_schedule INTO l_camp_id, l_source_code, l_cascade_flag ;
700 IF c_schedule%NOTFOUND THEN
701 CLOSE c_schedule;
702 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
703 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
704 FND_MSG_PUB.add;
705 END IF;
706 RAISE FND_API.g_exc_error;
707 END IF;
708 CLOSE c_schedule;
709
710 -- if source_code is not changed, return
711 IF p_schedule_rec.source_code = FND_API.g_miss_char
712 OR p_schedule_rec.source_code = l_source_code
713 THEN
714 RETURN;
715 END IF;
716
717 -- check if source code is cascaded from campaign
718 IF l_cascade_flag = 'Y' THEN
719 x_return_status := FND_API.g_ret_sts_error;
720 AMS_Utility_PVT.error_message('AMS_CSCH_CODE_NOT_CASCADE');
721 RETURN;
722 END IF;
723 IF (AMS_DEBUG_HIGH_ON) THEN
724
725 AMS_Utility_PVT.debug_message('Check Source Code uniqueness ');
726 END IF;
727 -- check if the new source code is unique
728 OPEN c_source_code;
729 FETCH c_source_code INTO l_dummy;
730 CLOSE c_source_code;
731 IF (AMS_DEBUG_HIGH_ON) THEN
732
733 AMS_Utility_PVT.debug_message('Dup Code '||l_dummy);
734 END IF;
735 IF l_dummy IS NOT NULL THEN
736 AMS_Utility_PVT.error_message('AMS_CAMP_DUPLICATE_CODE');
737 x_return_status := FND_API.g_ret_sts_error;
738 RETURN;
739 END IF;
740
741 -- cannot update source code if schedule has "old" list headers
742 OPEN c_list_header;
743 FETCH c_list_header INTO l_dummy;
744 CLOSE c_list_header;
745 IF l_dummy IS NOT NULL THEN
746 AMS_Utility_PVT.error_message('AMS_CSCH_UPDATE_SOURCE_CODE');
747 x_return_status := FND_API.g_ret_sts_error;
748 RETURN;
749 END IF;
750
751 IF (AMS_DEBUG_HIGH_ON) THEN
752
753
754
755 AMS_Utility_PVT.debug_message('Revoke Source Code ');
756
757 END IF;
758 AMS_SourceCode_PVT.revoke_sourcecode(
759 p_api_version => 1.0,
760 p_init_msg_list => FND_API.g_false,
761 p_commit => FND_API.g_false,
762 p_validation_level => FND_API.g_valid_level_full,
763
764 x_return_status => x_return_status,
765 x_msg_count => l_msg_count,
766 x_msg_data => l_msg_data,
767
768 p_sourcecode => l_source_code
769 );
770
771 IF x_return_status <> FND_API.g_ret_sts_success THEN
772 RAISE FND_API.g_exc_error;
773 END IF;
774
775 IF (AMS_DEBUG_HIGH_ON) THEN
776
777
778
779 AMS_Utility_PVT.debug_message('Create Source Code ');
780
781 END IF;
782 AMS_SourceCode_PVT.create_sourcecode(
783 p_api_version => 1.0,
784 p_init_msg_list => FND_API.g_false,
785 p_commit => FND_API.g_false,
786 p_validation_level => FND_API.g_valid_level_full,
787
788 x_return_status => x_return_status,
789 x_msg_count => l_msg_count,
790 x_msg_data => l_msg_data,
791
792 p_sourcecode => p_schedule_rec.source_code,
793 p_sourcecode_for => 'CSCH',
794 p_sourcecode_for_id => p_schedule_rec.schedule_id,
795 p_related_sourcecode => p_schedule_rec.related_source_code,
796 p_releated_sourceobj => p_schedule_rec.related_source_object,
797 p_related_sourceid => p_schedule_rec.related_source_id,
798 x_sourcecode_id => l_dummy
799 );
800
801 IF x_return_status <> FND_API.g_ret_sts_success THEN
802 RAISE FND_API.g_exc_error;
803 END IF;
804
805 END Check_Source_Code;
806 */
807
808 PROCEDURE Check_Source_Code(
809 p_schedule_rec IN AMS_Camp_Schedule_PVT.schedule_rec_type,
810 x_return_status OUT NOCOPY VARCHAR2,
811 x_source_code OUT NOCOPY VARCHAR2
812 )
813 IS
814
815 l_cascade_flag VARCHAR2(1);
816 l_source_code VARCHAR2(30);
817 p_sch_source_code VARCHAR2(30);
818 l_camp_id NUMBER;
819 l_dummy NUMBER;
820 l_msg_count NUMBER;
821 l_msg_data VARCHAR2(2000);
822
823 l_camp_source_code VARCHAR2(30);
824 l_camp_global_flag VARCHAR2(1);
825
826 CURSOR c_source_code IS
827 SELECT 1
828 FROM ams_source_codes
829 WHERE source_code = p_schedule_rec.source_code
830 AND active_flag = 'Y';
831
832 CURSOR c_schedule IS
833 SELECT campaign_id, source_code, use_parent_code_flag
834 FROM ams_campaign_schedules_b
835 WHERE schedule_id = p_schedule_rec.schedule_id;
836
837 CURSOR c_list_header IS
838 SELECT 1
839 FROM ams_list_headers_all
840 WHERE arc_list_used_by = 'CSCH'
841 AND list_used_by_id = p_schedule_rec.schedule_id
842 AND status_code <> 'NEW';
843
844 CURSOR c_camp IS
845 SELECT source_code,
846 global_flag
847 FROM ams_campaigns_vl
848 WHERE campaign_id = p_schedule_rec.campaign_id;
849
850 BEGIN
851 IF (AMS_DEBUG_HIGH_ON) THEN
852
853 AMS_Utility_PVT.debug_message('Check Source Code ');
854 END IF;
855 x_return_status := FND_API.g_ret_sts_success;
856
857
858 -- query the campaign_id and the old source_code
859 OPEN c_schedule;
860 FETCH c_schedule INTO l_camp_id, l_source_code, l_cascade_flag;
861 IF c_schedule%NOTFOUND THEN
862 CLOSE c_schedule;
863 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
864 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
865 FND_MSG_PUB.add;
866 END IF;
867 RAISE FND_API.g_exc_error;
868 RETURN;
869 END IF;
870 CLOSE c_schedule;
871
872 x_source_code := l_source_code;
873 p_sch_source_code := l_source_code;
874
875 -- commented out by soagrawa on 31-jan-2002 as no longer valid
876 -- cannot update source code if schedule has "old" list headers
877 /*
878 OPEN c_list_header;
879 FETCH c_list_header INTO l_dummy;
880 CLOSE c_list_header;
881 IF l_dummy IS NOT NULL THEN
882 AMS_Utility_PVT.error_message('AMS_CSCH_UPDATE_SOURCE_CODE');
883 x_return_status := FND_API.g_ret_sts_error;
884 RETURN;
885 END IF;
886 */
887
888 -- if source_code is not changed, return
889 IF /*p_schedule_rec.source_code = FND_API.g_miss_char
890 OR */p_schedule_rec.source_code = l_source_code
891 THEN
892 RETURN;
893 END IF;
894
895 -- following code added by soagrawa on 31-jan-2002 for bug# 2207286
896 IF FND_API.G_TRUE = Target_Group_Exist(p_schedule_rec.schedule_id) THEN
897 AMS_Utility_PVT.Error_Message('AMS_CSCH_UPDATE_SOURCE_CODE');
898 RAISE FND_API.g_exc_error;
899 END IF ;
900
901 -- get campaign's source code
902 OPEN c_camp;
903 FETCH c_camp INTO l_camp_source_code, l_camp_global_flag;
904 IF c_camp%NOTFOUND THEN -- campaign_id is invalid
905 CLOSE c_camp;
906 x_return_status := FND_API.g_ret_sts_error;
907 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_ID');
908 RAISE FND_API.g_exc_error;
909 RETURN;
910 END IF;
911 CLOSE c_camp;
912
913
914 -- Logic for source code update:
915 -- if cascade flag is Y
916 -- if current source code == campaign source code
917 -- => return
918 -- else
919 -- => 1. revoke old source code
920 -- 2. take campaign's source code to populate schedule's
921
922 IF (AMS_DEBUG_HIGH_ON) THEN
923
924
925
926 AMS_Utility_PVT.debug_message('Check Source Code: use parent source code flag is '||p_schedule_rec.use_parent_code_flag);
927
928 END IF;
929 l_cascade_flag := p_schedule_rec.use_parent_code_flag;
930
931
932 -- check if source code is cascaded from campaign
933 IF l_cascade_flag = 'Y' THEN
934 IF (AMS_DEBUG_HIGH_ON) THEN
935
936 AMS_Utility_PVT.debug_message('Check Source Code: use parent source code flag is Y');
937 END IF;
938 IF l_source_code = l_camp_source_code
939 THEN
940 IF (AMS_DEBUG_HIGH_ON) THEN
941
942 AMS_Utility_PVT.debug_message('nothing to change');
943 END IF;
944 RETURN;
945 ELSE
946 -- revoke old source code
947 IF (AMS_DEBUG_HIGH_ON) THEN
948
949 AMS_Utility_PVT.debug_message('revoke previous, and put campaign source code');
950 END IF;
951 AMS_SourceCode_PVT.revoke_sourcecode(
952 p_api_version => 1.0,
953 p_init_msg_list => FND_API.g_false,
954 p_commit => FND_API.g_false,
955 p_validation_level => FND_API.g_valid_level_full,
956
957 x_return_status => x_return_status,
958 x_msg_count => l_msg_count,
959 x_msg_data => l_msg_data,
960
961 p_sourcecode => l_source_code
962 );
963 IF x_return_status <> FND_API.g_ret_sts_success THEN
964 RAISE FND_API.g_exc_error;
965 RETURN;
966 END IF;
967
968 -- populate camp's srccd into schedule
969 x_source_code := l_camp_source_code;
970 IF (AMS_DEBUG_HIGH_ON) THEN
971
972 AMS_Utility_PVT.debug_message('put campaign source code - all OK');
973 END IF;
974 END IF;
975
976
977 -- else (Cascade flag is N)
978 -- if source code is null
979 -- => 1. system generate it
980 -- 2. push it into the source code table
981 -- else (not null)
982 -- => 1. check for uniqueness
983 -- if unique => push it in source code table
984 -- else error out.
985
986
987 ELSE -- cascade flag is N
988 IF (AMS_DEBUG_HIGH_ON) THEN
989
990 AMS_Utility_PVT.debug_message('Check Source Code: use parent source code flag is N');
991 END IF;
992 IF p_schedule_rec.source_code IS NULL
993 OR p_schedule_rec.source_code = FND_API.g_miss_char
994 OR p_schedule_rec.source_code = ''
995 THEN
996 IF (AMS_DEBUG_HIGH_ON) THEN
997
998 AMS_Utility_PVT.debug_message('Gotta system generate it');
999 END IF;
1000 -- system generate it
1001 x_source_code := Generate_Schedule_Code(l_camp_source_code,p_schedule_rec.custom_setup_id);
1002 -- see if current in database is same as campaign's
1003 IF p_sch_source_code <> l_camp_source_code THEN
1004 -- revoke
1005 IF (AMS_DEBUG_HIGH_ON) THEN
1006
1007 AMS_Utility_PVT.debug_message('Revoke Source Code '||p_sch_source_code);
1008 END IF;
1009 AMS_SourceCode_PVT.revoke_sourcecode(
1010 p_api_version => 1.0,
1011 p_init_msg_list => FND_API.g_false,
1012 p_commit => FND_API.g_false,
1013 p_validation_level => FND_API.g_valid_level_full,
1014
1015 x_return_status => x_return_status,
1016 x_msg_count => l_msg_count,
1017 x_msg_data => l_msg_data,
1018
1019 p_sourcecode => p_sch_source_code
1020 );
1021 IF x_return_status <> FND_API.g_ret_sts_success THEN
1022 RAISE FND_API.g_exc_error;
1023 RETURN;
1024 END IF;
1025 END IF;
1026
1027 IF (AMS_DEBUG_HIGH_ON) THEN
1028
1029
1030
1031 AMS_Utility_PVT.debug_message('push it '||x_source_code);
1032
1033 END IF;
1034 -- push system generated one into source code table
1035
1036 -- soagrawa 22-oct-2002 for bug# 2594717
1037 IF P_schedule_rec.related_event_id IS NOT NULL
1038 THEN
1039 AMS_CampaignRules_PVT.push_source_code(
1040 x_source_code,
1041 'CSCH',
1042 p_schedule_rec.schedule_id,
1043 p_schedule_rec.related_source_code,
1044 p_schedule_rec.related_source_object,
1045 p_schedule_rec.related_source_id
1046 );
1047 ELSE
1048 AMS_CampaignRules_PVT.push_source_code(
1049 x_source_code,
1050 'CSCH',
1051 p_schedule_rec.schedule_id
1052 );
1053 END IF;
1054
1055 ELSE -- source code is not null
1056 IF (AMS_DEBUG_HIGH_ON) THEN
1057
1058 AMS_Utility_PVT.debug_message('is it unique? '||p_schedule_rec.source_code);
1059 END IF;
1060 IF AMS_SourceCode_PVT.is_source_code_unique(p_schedule_rec.source_code) = FND_API.g_false
1061 THEN
1062 -- if not unique
1063 x_return_status := FND_API.g_ret_sts_error;
1064 AMS_Utility_PVT.error_message('AMS_CAMP_DUPLICATE_CODE');
1065 RETURN;
1066 ELSE
1067 -- yes unique
1068 -- remove whatever was earlier
1069 IF p_sch_source_code <> l_camp_source_code THEN
1070 -- revoke
1071 IF (AMS_DEBUG_HIGH_ON) THEN
1072
1073 AMS_Utility_PVT.debug_message('Revoke Source Code '||p_sch_source_code);
1074 END IF;
1075 AMS_SourceCode_PVT.revoke_sourcecode(
1076 p_api_version => 1.0,
1077 p_init_msg_list => FND_API.g_false,
1078 p_commit => FND_API.g_false,
1079 p_validation_level => FND_API.g_valid_level_full,
1080
1081 x_return_status => x_return_status,
1082 x_msg_count => l_msg_count,
1083 x_msg_data => l_msg_data,
1084
1085 p_sourcecode => p_sch_source_code
1086 );
1087 IF x_return_status <> FND_API.g_ret_sts_success THEN
1088 RAISE FND_API.g_exc_error;
1089 RETURN;
1090 END IF;
1091 END IF;
1092
1093 -- push user's code into source code table
1094 x_source_code := p_schedule_rec.source_code;
1095
1096 -- soagrawa 22-oct-2002 for bug# 2594717
1097 IF P_schedule_rec.related_event_id IS NOT NULL
1098 THEN
1099 AMS_CampaignRules_PVT.push_source_code(
1100 x_source_code,
1101 'CSCH',
1102 p_schedule_rec.schedule_id,
1103 p_schedule_rec.related_source_code,
1104 p_schedule_rec.related_source_object,
1105 p_schedule_rec.related_source_id
1106 );
1107 ELSE
1108 AMS_CampaignRules_PVT.push_source_code(
1109 x_source_code,
1110 'CSCH',
1111 p_schedule_rec.schedule_id
1112 );
1113 END IF;
1114 END IF;
1115 END IF;
1116
1117 END IF;
1118
1119 IF (AMS_DEBUG_HIGH_ON) THEN
1120
1121
1122
1123 AMS_Utility_PVT.debug_message('Final source code is '||x_source_code);
1124
1125 END IF;
1126
1127 END Check_Source_Code;
1128
1129 --========================================================================
1130 -- PROCEDURE
1131 -- Check_Sched_Dates_Vs_Camp
1132 --
1133 -- Purpose
1134 -- Created to check if the schedules start and end date are within
1135 -- campaigns start date and end date.
1136 --
1137 -- HISTORY
1138 -- 02-Feb-2001 ptendulk Created.
1139
1140 --========================================================================
1141 PROCEDURE Check_Sched_Dates_Vs_Camp(
1142 p_campaign_id IN NUMBER,
1143 p_start_date IN DATE,
1144 p_end_date IN DATE,
1145 x_return_status OUT NOCOPY VARCHAR2
1146 )
1147 IS
1148
1149 CURSOR c_campaign IS
1150 SELECT actual_exec_start_date,
1151 actual_exec_end_date
1152 FROM ams_campaigns_all_b
1153 WHERE campaign_id = p_campaign_id;
1154
1155 l_parent_start_date DATE;
1156 l_parent_end_date DATE;
1157
1158 BEGIN
1159
1160 x_return_status := FND_API.g_ret_sts_success;
1161 IF p_campaign_id IS NULL THEN
1162 RETURN;
1163 END IF;
1164
1165 OPEN c_campaign;
1166 FETCH c_campaign INTO l_parent_start_date, l_parent_end_date;
1167 IF c_campaign%NOTFOUND THEN
1168 CLOSE c_campaign;
1169 x_return_status := FND_API.g_ret_sts_error;
1170 AMS_Utility_PVT.error_message('AMS_CSCH_NO_CAMP_ID');
1171 RAISE FND_API.g_exc_error;
1172 END IF;
1173 CLOSE c_campaign;
1174
1175 ---------------------- start date ----------------------------
1176 IF p_start_date IS NOT NULL THEN
1177 IF l_parent_start_date IS NULL THEN
1178 x_return_status := FND_API.g_ret_sts_error;
1179 AMS_Utility_PVT.error_message('AMS_CSCH_CAMP_START_NULL');
1180 ELSIF p_start_date < l_parent_start_date THEN
1181 x_return_status := FND_API.g_ret_sts_error;
1182 AMS_Utility_PVT.error_message('AMS_CSCH_START_BEF_CAMP_START');
1183 ELSIF p_start_date > l_parent_end_date THEN
1184 x_return_status := FND_API.g_ret_sts_error;
1185 AMS_Utility_PVT.error_message('AMS_CSCH_START_AFT_CAMP_END');
1186 END IF;
1187 END IF;
1188
1189 ---------------------- end date ------------------------------
1190 IF p_end_date IS NOT NULL THEN
1191 IF l_parent_end_date IS NULL THEN
1192 x_return_status := FND_API.g_ret_sts_error;
1193 AMS_Utility_PVT.error_message('AMS_CSCH_CAMP_END_NULL');
1194 ELSIF p_end_date > l_parent_end_date THEN
1195 x_return_status := FND_API.g_ret_sts_error;
1196 AMS_Utility_PVT.error_message('AMS_CSCH_END_AFT_CAMP_END');
1197 ELSIF p_end_date < l_parent_start_date THEN
1198 x_return_status := FND_API.g_ret_sts_error;
1199 AMS_Utility_PVT.error_message('AMS_CSCH_END_BEF_CAMP_START');
1200 END IF;
1201 END IF;
1202
1203 END Check_Sched_Dates_Vs_Camp;
1204
1205
1206 --========================================================================
1207 -- PROCEDURE
1208 -- Check_Schedule_Update
1209 --
1210 -- Purpose
1211 -- Created to check if the user can update the schedule details
1212 -- It also checks for the locked columns and if user tries to update
1213 -- API will be errored out.
1214 --
1215 -- Note
1216 -- 1. Can't update Currency if the budget line exist for the schedule.
1217 -- 2. Only user/sysadmin can change the owner field.
1218 --
1219 -- HISTORY
1220 -- 13-Feb-2001 ptendulk Created.
1221 -- 08-Jul-2002 soagrawa Fixed reopened bug# 2406677 in check_schedule_update
1222 -- 13-feb-2003 soagrawa Fixed CRMAP bug# 2795823
1223 -- checking for access against the schedule, and NOT against the parent campaign
1224 --========================================================================
1225 PROCEDURE Check_Schedule_Update(
1226 p_schedule_rec IN AMS_Camp_Schedule_PVT.schedule_rec_type,
1227 x_return_status OUT NOCOPY VARCHAR2
1228 )
1229 IS
1230 CURSOR c_resource IS
1231 SELECT resource_id
1232 FROM ams_jtf_rs_emp_v
1233 WHERE user_id = FND_GLOBAL.user_id ;
1234
1235 CURSOR c_schedule IS
1236 SELECT *
1237 FROM ams_campaign_schedules_vl
1238 WHERE schedule_id = p_schedule_rec.schedule_id;
1239
1240 CURSOR c_bud_line IS
1241 SELECT 1
1242 FROM DUAL
1243 WHERE EXISTS(
1244 SELECT activity_budget_id
1245 FROM ozf_act_budgets
1246 WHERE arc_act_budget_used_by = 'CSCH'
1247 AND act_budget_used_by_id = p_schedule_rec.schedule_id );
1248
1249
1250 l_bud_exist VARCHAR2(1);
1251 l_schedule_rec c_schedule%ROWTYPE;
1252
1253 l_resource NUMBER ;
1254 l_access VARCHAR2(1);
1255 l_admin_user BOOLEAN;
1256
1257
1258 BEGIN
1259 x_return_status := FND_API.g_ret_sts_success;
1260
1261 OPEN c_resource ;
1262 FETCH c_resource INTO l_resource;
1263 CLOSE c_resource ;
1264
1265 -- Modified by soagrawa on 13-feb-2003 to fix CRMAP bug# 2795823
1266 -- checking for access against the schedule, and NOT against the parent campaign.
1267 /*
1268 l_access := AMS_Access_PVT.Check_Update_Access(p_object_id => p_schedule_rec.campaign_id ,
1269 p_object_type => 'CAMP',
1270 p_user_or_role_id => l_resource,
1271 p_user_or_role_type => 'USER');
1272 */
1273
1274 l_access := AMS_Access_PVT.Check_Update_Access(p_object_id => p_schedule_rec.schedule_id ,
1275 p_object_type => 'CSCH',
1276 p_user_or_role_id => l_resource,
1277 p_user_or_role_type => 'USER');
1278
1279 IF l_access = 'N' THEN
1280 AMS_Utility_PVT.error_message('AMS_CAMP_NO_ACCESS');
1281 x_return_status := FND_API.g_ret_sts_error;
1282 RETURN;
1283 END IF ;
1284
1285 OPEN c_schedule;
1286 FETCH c_schedule INTO l_schedule_rec;
1287 IF c_schedule%NOTFOUND THEN
1288 CLOSE c_schedule;
1289 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1290 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1291 FND_MSG_PUB.add;
1292 END IF;
1293 RAISE FND_API.g_exc_error;
1294 END IF;
1295 CLOSE c_schedule;
1296
1297 l_admin_user := AMS_Access_PVT.Check_Admin_Access(l_resource);
1298
1299 -- Only owner/ Super Admin can change the owner.
1300 IF p_schedule_rec.owner_user_id <> FND_API.g_miss_num
1301 AND p_schedule_rec.owner_user_id <> l_schedule_rec.owner_user_id
1302 AND l_admin_user = FALSE
1303 -- following line modified by soagrawa on 08-jul-2002
1304 -- for fixing reopened bug# 2406677
1305 -- AND p_schedule_rec.owner_user_id <> l_resource
1306 AND l_schedule_rec.owner_user_id <> l_resource
1307 THEN
1308 AMS_Utility_PVT.error_message('AMS_CAMP_OWNER_ACCESS');
1309 x_return_status := FND_API.g_ret_sts_error;
1310 END IF;
1311
1312 OPEN c_bud_line ;
1313 FETCH c_bud_line INTO l_bud_exist;
1314 CLOSE c_bud_line ;
1315
1316 IF p_schedule_rec.transaction_currency_code <> FND_API.g_miss_char
1317 AND p_schedule_rec.transaction_currency_code <> l_schedule_rec.transaction_currency_code
1318 AND l_bud_exist IS NOT NULL
1319 THEN
1320 AMS_Utility_PVT.error_message('AMS_CSCH_BUD_PRESENT');
1321 x_return_status := FND_API.g_ret_sts_error;
1322 END IF ;
1323
1324
1325 END Check_Schedule_Update;
1326
1327 --========================================================================
1328 -- PROCEDURE
1329 -- Check_Schedule_Activity
1330 --
1331 -- PURPOSE
1332 -- This api is created to validate the activity type , activity
1333 -- and marketing medium attached to the schedule.
1334 --
1335 -- HISTORY
1336 -- 13-Feb-2001 ptendulk Created.
1337 --
1338 --========================================================================
1339 PROCEDURE Check_Schedule_Activity(
1340 p_schedule_id IN NUMBER,
1341 p_activity_type IN VARCHAR2,
1342 p_activity_id IN NUMBER,
1343 p_medium_id IN NUMBER,
1344 p_arc_channel_from IN VARCHAR2,
1345 p_status_code IN VARCHAR2,
1346 x_return_status OUT NOCOPY VARCHAR2
1347 )
1348 IS
1349
1350 l_type VARCHAR2(30);
1351 l_dummy NUMBER;
1352
1353 CURSOR c_media IS
1354 SELECT media_type_code
1355 FROM ams_media_b
1356 WHERE media_id = p_activity_id
1357 AND enabled_flag = 'Y';
1358
1359 CURSOR c_channel_media IS
1360 SELECT 1
1361 FROM ams_media_channels
1362 WHERE channel_id = p_medium_id
1363 AND media_id = p_activity_id;
1364
1365 -- CURSOR c_eveh IS
1366 -- SELECT event_type_code
1367 -- FROM ams_event_headers_all_b
1368 -- WHERE event_header_id = p_channel_id;
1369 --
1370 -- CURSOR c_eveo IS
1371 -- SELECT event_type_code
1372 -- FROM ams_event_offers_all_b
1373 -- WHERE event_offer_id = p_channel_id;
1374
1375 -- CURSOR c_camp_event IS
1376 -- SELECT 1
1377 -- FROM DUAL
1378 -- WHERE EXISTS(
1379 -- SELECT campaign_id
1380 -- FROM ams_campaigns_vl
1381 -- WHERE media_type_code = 'EVENTS'
1382 -- AND arc_channel_from = p_arc_channel_from
1383 -- AND channel_id = p_channel_id
1384 -- AND (campaign_id <> p_campaign_id OR p_campaign_id IS NULL));
1385
1386 -- Following line(Was the last line of the above cursor) is commented by ptendulk
1387 -- on 14 Aug 2000 Ref Bug : 1378977
1388 --
1389 -- AND (campaign_id = p_campaign_id OR p_campaign_id IS NULL));
1390
1391 BEGIN
1392
1393 x_return_status := FND_API.g_ret_sts_success;
1394
1395 -- for execution campaigns, media_type and media are required
1396 IF p_activity_type IS NULL THEN
1397 AMS_Utility_PVT.error_message('AMS_CSCH_NO_MEDIA_TYPE');
1398 x_return_status := FND_API.g_ret_sts_error;
1399 RETURN;
1400 END IF;
1401
1402 IF p_activity_type <> 'EVENTS' AND p_activity_id IS NULL THEN
1403 AMS_Utility_PVT.error_message('AMS_CSCH_NO_MEDIA');
1404 x_return_status := FND_API.g_ret_sts_error;
1405 RETURN;
1406 END IF;
1407
1408 IF p_activity_type = 'EVENTS' AND p_activity_type IS NULL THEN
1409 AMS_Utility_PVT.error_message('AMS_CAMP_EC_NO_EVENT_TYPE');
1410 x_return_status := FND_API.g_ret_sts_error;
1411 RETURN;
1412 END IF;
1413
1414 -- validate media_id
1415 IF p_activity_id IS NOT NULL THEN
1416 OPEN c_media;
1417 FETCH c_media INTO l_type;
1418 CLOSE c_media;
1419
1420 IF l_type <> p_activity_type THEN
1421 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_MEDIA_ID');
1422 x_return_status := FND_API.g_ret_sts_error;
1423 RETURN;
1424 END IF;
1425 END IF;
1426
1427 -- validate media channel id
1428 -- IF p_activity_type <> 'EVENTS'AND p_medium_id IS NOT NULL THEN
1429 IF p_activity_type <> 'EVENTS' AND p_activity_type <> 'DEAL' AND p_activity_type <> 'TRADE_PROMOTION' AND (p_medium_id IS NOT NULL AND p_medium_id <> FND_API.g_miss_num) THEN
1430 OPEN c_channel_media;
1431 FETCH c_channel_media INTO l_dummy;
1432 CLOSE c_channel_media;
1433
1434 IF l_dummy IS NULL OR p_activity_id IS NULL THEN
1435 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_CHANNEL');
1436 x_return_status := FND_API.g_ret_sts_error;
1437 RETURN;
1438 END IF;
1439 END IF;
1440
1441 IF p_activity_type <> 'TRADE_PROMOTION'
1442 AND p_activity_type <> 'DEAL'
1443 AND p_activity_type <> 'DIRECT_MARKETING'
1444 AND p_activity_type <> 'DIRECT_SALES'
1445 AND p_activity_type <> 'INTERNET'
1446 AND p_medium_id IS NULL --AND p_medium_id <> FND_API.g_miss_num
1447 AND p_status_code IN ('SUBMITTED_BA', 'AVAILABLE', 'ACTIVE')
1448 THEN
1449 IF p_activity_type = 'EVENTS' THEN
1450 -- dbiswas commented out the following error mesg for R12.
1451 -- AMS_Utility_PVT.error_message('AMS_CAMP_EVENT_REQUIRED');
1452 null;
1453 ELSE
1454 AMS_Utility_PVT.error_message('AMS_CAMP_CHANNEL_REQUIRED');
1455 x_return_status := FND_API.g_ret_sts_error;
1456 END IF;
1457 -- x_return_status := FND_API.g_ret_sts_error;
1458 RETURN;
1459 END IF;
1460
1461
1462
1463 -- validate event channel id
1464 -- IF p_media_type = 'EVENTS' AND p_channel_id IS NOT NULL THEN
1465 -- IF p_arc_channel_from = 'EVEO' THEN
1466 -- OPEN c_eveo;
1467 -- FETCH c_eveo INTO l_type;
1468 -- IF c_eveo%NOTFOUND OR l_type <> p_event_type THEN
1469 -- x_return_status := FND_API.g_ret_sts_error;
1470 -- AMS_Utility_PVT.error_message('AMS_CAMP_BAD_CHANNEL');
1471 -- END IF;
1472 -- CLOSE c_eveo;
1473 -- ELSIF p_arc_channel_from = 'EVEH' THEN
1474 -- OPEN c_eveh;
1475 -- FETCH c_eveh INTO l_type;
1476 -- IF c_eveh%NOTFOUND OR l_type <> p_event_type THEN
1477 -- x_return_status := FND_API.g_ret_sts_error;
1478 -- AMS_Utility_PVT.error_message('AMS_CAMP_BAD_CHANNEL');
1479 -- END IF;
1480 -- CLOSE c_eveh;
1481 -- ELSE
1482 -- x_return_status := FND_API.g_ret_sts_error;
1483 -- AMS_Utility_PVT.error_message('AMS_CAMP_BAD_ARC_CHANNEL');
1484 -- END IF;
1485
1486 -- event associated to a campaign cannot be associated to other campaigns
1487 -- OPEN c_camp_event;
1488 -- FETCH c_camp_event INTO l_dummy;
1489 -- IF c_camp_event%FOUND THEN
1490 -- x_return_status := FND_API.g_ret_sts_error;
1491 -- AMS_Utility_PVT.error_message('AMS_CAMP_EVENT_IN_USE');
1492 -- END IF;
1493 -- CLOSE c_camp_event;
1494 -- END IF;
1495
1496 END Check_Schedule_Activity;
1497
1498 -- Start of Comments
1499 --
1500 -- NAME
1501 -- Update_List_Sent_Out_Date
1502 --
1503 -- PURPOSE
1504 --
1505 --
1506 -- NOTES
1507 --
1508 --
1509 -- HISTORY
1510 -- 17-MAY-2001 soagrawa created
1511 -- End of Comments
1512 PROCEDURE Update_List_Sent_Out_Date
1513 (p_api_version IN NUMBER,
1514 p_init_msg_list IN VARCHAR2 := FND_API.G_False,
1515 p_commit IN VARCHAR2 := FND_API.G_False,
1516
1517 x_return_status OUT NOCOPY VARCHAR2,
1518 x_msg_count OUT NOCOPY NUMBER ,
1519 x_msg_data OUT NOCOPY VARCHAR2,
1520
1521 p_list_header_id IN NUMBER)
1522 -- p_schedule_id IN NUMBER,
1523 -- p_exec_flag IN VARCHAR2)
1524 IS
1525
1526 CURSOR c_list_details IS
1527 SELECT object_version_number
1528 FROM ams_list_headers_all
1529 WHERE list_header_id = p_list_header_id ;
1530
1531 -- g_pkg_name CONSTANT VARCHAR2(30) := 'NONE';
1532
1533 l_list_rec AMS_LISTHEADER_PVT.list_header_rec_type;
1534 l_api_name CONSTANT VARCHAR2(30) := 'AMS_EXEC_SCHEDULE';
1535 l_api_version CONSTANT NUMBER := 1.0;
1536 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1537
1538
1539 l_return_status VARCHAR2(1);
1540
1541 BEGIN
1542 --
1543 -- Standard Start of API savepoint
1544 --
1545 SAVEPOINT Update_List_Header;
1546
1547 --
1548 -- Debug Message
1549 --
1550 IF (AMS_DEBUG_HIGH_ON) THEN
1551
1552 AMS_Utility_PVT.debug_message(l_full_name||': start');
1553 END IF;
1554
1555 --
1556 -- Initialize message list IF p_init_msg_list is set to TRUE.
1557 --
1558 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1559 FND_MSG_PUB.initialize;
1560 END IF;
1561
1562 --
1563 -- Standard call to check for call compatibility.
1564 --
1565 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1566 p_api_version,
1567 l_api_name,
1568 G_PKG_NAME)
1569 THEN
1570 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1571 END IF;
1572
1573 --
1574 -- Initialize API return status to success
1575 --
1576 x_return_status := FND_API.G_RET_STS_SUCCESS;
1577
1578 -- Update the list header with the Schedule details.
1579 AMS_LISTHEADER_PVT.Init_ListHeader_rec(x_listheader_rec => l_list_rec);
1580 l_list_rec.list_header_id := p_list_header_id ;
1581
1582 -- get the obj version number
1583 OPEN c_list_details ;
1584 FETCH c_list_details INTO l_list_rec.object_version_number ;
1585 CLOSE c_list_details ;
1586
1587
1588 --
1589 -- Update the list sent out date with sysdate if success
1590 --
1591
1592 l_list_rec.sent_out_date := sysdate ;
1593
1594 --l_list_rec.arc_list_used_by := 'CSCH' ; -- Campaign Schedule
1595 --l_list_rec.list_used_by_id := p_schedule_id ; -- Campaign Schedule
1596
1597 AMS_LISTHEADER_PVT.Update_ListHeader
1598 ( p_api_version => p_api_version,
1599 p_init_msg_list => FND_API.G_FALSE,
1600 p_commit => FND_API.G_FALSE,
1601 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1602
1603 x_return_status => x_return_status,
1604 x_msg_count => x_msg_count,
1605 x_msg_data => x_msg_data ,
1606
1607 p_listheader_rec => l_list_rec
1608 );
1609
1610 -- If any errors happen abort API.
1611 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1612 RAISE FND_API.G_EXC_ERROR;
1613 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1614 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1615 END IF;
1616
1617 --
1618 -- Standard check of p_commit.
1619 --
1620 IF FND_API.To_Boolean ( p_commit )
1621 THEN
1622 COMMIT WORK;
1623 END IF;
1624
1625 --
1626 -- Standard call to get message count AND IF count is 1, get message info.
1627 --
1628 FND_MSG_PUB.Count_AND_Get
1629 ( p_count => x_msg_count,
1630 p_data => x_msg_data,
1631 p_encoded => FND_API.G_FALSE
1632 );
1633
1634 IF (AMS_DEBUG_HIGH_ON) THEN
1635
1636
1637
1638 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1639
1640 END IF;
1641
1642
1643
1644 EXCEPTION
1645 WHEN FND_API.G_EXC_ERROR THEN
1646
1647 ROLLBACK TO Update_List_Header;
1648 x_return_status := FND_API.G_RET_STS_ERROR ;
1649
1650 FND_MSG_PUB.Count_AND_Get
1651 ( p_count => x_msg_count,
1652 p_data => x_msg_data,
1653 p_encoded => FND_API.G_FALSE
1654 );
1655
1656 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1657
1658 ROLLBACK TO Update_List_Header;
1659 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1660
1661 FND_MSG_PUB.Count_AND_Get
1662 ( p_count => x_msg_count,
1663 p_data => x_msg_data,
1664 p_encoded => FND_API.G_FALSE
1665 );
1666
1667 WHEN OTHERS THEN
1668
1669 ROLLBACK TO Update_List_Header;
1670 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1671
1672 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1673 THEN
1674 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1675 END IF;
1676
1677 FND_MSG_PUB.Count_AND_Get
1678 ( p_count => x_msg_count,
1679 p_data => x_msg_data,
1680 p_encoded => FND_API.G_FALSE
1681 );
1682 END Update_List_Sent_Out_Date ;
1683
1684
1685
1686
1687
1688
1689
1690 --=====================================================================================================
1691 -- PROCEDURE
1692 -- Complete_Schedule
1693 --
1694 -- PURPOSE
1695 -- This api is created to complete active schedules.
1696 --
1697 --
1698 -- Note
1699 -- This procedure will be called by concurrent program to complete the
1700 -- schedule.
1701 --
1702 -- HISTORY
1703 -- 24-Aug-2003 ptendulk Created
1704 -- 17-Mar-2005 spendem call the API to raise business event on status change as per enh # 3805347
1705 --========================================================================================================
1706 PROCEDURE Complete_Schedule
1707 (
1708 p_api_version IN NUMBER,
1709 p_init_msg_list IN VARCHAR2 := FND_API.G_False,
1710 p_commit IN VARCHAR2 := FND_API.G_False,
1711 p_schedule_id IN NUMBER := NULL,
1712
1713 x_return_status OUT NOCOPY VARCHAR2,
1714 x_msg_count OUT NOCOPY NUMBER ,
1715 x_msg_data OUT NOCOPY VARCHAR2 )
1716 IS
1717 CURSOR c_completed_schedule IS
1718 SELECT schedule_id, object_version_number
1719 FROM ams_campaign_schedules_b
1720 WHERE status_code = 'ACTIVE'
1721 AND end_date_time <= SYSDATE ;
1722
1723 CURSOR c_status(l_status_code VARCHAR2) IS
1724 SELECT user_status_id
1725 FROM ams_user_statuses_b
1726 WHERE system_status_type = 'AMS_CAMPAIGN_SCHEDULE_STATUS'
1727 AND system_status_code = l_status_code
1728 AND default_flag = 'Y'
1729 AND enabled_flag = 'Y' ;
1730
1731 l_status_id NUMBER ;
1732 l_schedule_id NUMBER ;
1733 l_obj_version NUMBER ;
1734 l_api_version CONSTANT NUMBER := 1.0 ;
1735 l_api_name CONSTANT VARCHAR2(30) := 'Complete_Schedule';
1736
1737 BEGIN
1738 --
1739 -- Standard Start of API savepoint
1740 --
1741 SAVEPOINT AMS_COMPLETE_SCHEDULE;
1742
1743 --
1744 -- Debug Message
1745 --
1746 IF (AMS_DEBUG_HIGH_ON) THEN
1747
1748 AMS_Utility_PVT.debug_message(l_api_name || ': start');
1749 END IF;
1750
1751 --
1752 -- Initialize message list IF p_init_msg_list is set to TRUE.
1753 --
1754 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1755 FND_MSG_PUB.initialize;
1756 END IF;
1757
1758 --
1759 -- Standard call to check for call compatibility.
1760 --
1761 IF NOT FND_API.Compatible_API_Call ( 1.0,
1762 p_api_version,
1763 l_api_name,
1764 G_PKG_NAME)
1765 THEN
1766 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1767 END IF;
1768
1769 --
1770 -- Initialize API return status to success
1771 --
1772 x_return_status := FND_API.G_RET_STS_SUCCESS;
1773
1774 -- Chenge the status of all the schedules which are active to
1775 -- completed.
1776 OPEN c_status('COMPLETED') ;
1777 FETCH c_status INTO l_status_id ;
1778 IF c_status%NOTFOUND THEN
1779 CLOSE c_status;
1780 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_USER_STATUS');
1781 RETURN ;
1782 END IF ;
1783 CLOSE c_status ;
1784
1785 OPEN c_completed_schedule ;
1786 LOOP
1787 FETCH c_completed_schedule INTO l_schedule_id, l_obj_version ;
1788 EXIT WHEN c_completed_schedule%NOTFOUND ;
1789
1790 -- Update the status of the schedule to Active.
1791 UPDATE ams_campaign_schedules_b
1792 SET status_code = 'COMPLETED',
1793 status_date = SYSDATE ,
1794 user_status_id = l_status_id,
1795 object_version_number = l_obj_version + 1
1796 WHERE schedule_id = l_schedule_id ;
1797
1798 -- call to api to raise business event, as per enh # 3805347
1799 RAISE_BE_ON_STATUS_CHANGE(p_obj_id => l_schedule_id,
1800 p_obj_type => 'CSCH',
1801 p_old_status_code => 'ACTIVE',
1802 p_new_status_code => 'COMPLETED');
1803
1804 END LOOP;
1805 CLOSE c_completed_schedule;
1806 --
1807 -- Standard check of p_commit.
1808 --
1809 IF FND_API.To_Boolean ( p_commit )
1810 THEN
1811 COMMIT WORK;
1812 END IF;
1813
1814 --
1815 -- Standard call to get message count AND IF count is 1, get message info.
1816 --
1817 FND_MSG_PUB.Count_AND_Get
1818 ( p_count => x_msg_count,
1819 p_data => x_msg_data,
1820 p_encoded => FND_API.G_FALSE
1821 );
1822
1823 IF (AMS_DEBUG_HIGH_ON) THEN
1824
1825
1826
1827 AMS_Utility_PVT.debug_message(l_api_name ||' : end Status : ' || x_return_status);
1828
1829 END IF;
1830
1831
1832
1833 EXCEPTION
1834 WHEN FND_API.G_EXC_ERROR THEN
1835
1836 IF (c_completed_schedule%ISOPEN) THEN
1837 CLOSE c_completed_schedule ;
1838 END IF;
1839 ROLLBACK TO AMS_COMPLETE_SCHEDULE;
1840 x_return_status := FND_API.G_RET_STS_ERROR ;
1841
1842 FND_MSG_PUB.Count_AND_Get
1843 ( p_count => x_msg_count,
1844 p_data => x_msg_data,
1845 p_encoded => FND_API.G_FALSE
1846 );
1847
1848 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1849 IF (c_completed_schedule%ISOPEN) THEN
1850 CLOSE c_completed_schedule ;
1851 END IF;
1852 ROLLBACK TO AMS_COMPLETE_SCHEDULE;
1853 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1854
1855 FND_MSG_PUB.Count_AND_Get
1856 ( p_count => x_msg_count,
1857 p_data => x_msg_data,
1858 p_encoded => FND_API.G_FALSE
1859 );
1860
1861 WHEN OTHERS THEN
1862 IF (c_completed_schedule%ISOPEN) THEN
1863 CLOSE c_completed_schedule ;
1864 END IF;
1865 ROLLBACK TO AMS_COMPLETE_SCHEDULE;
1866 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1867
1868 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1869 THEN
1870 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1871 END IF;
1872
1873 FND_MSG_PUB.Count_AND_Get
1874 ( p_count => x_msg_count,
1875 p_data => x_msg_data,
1876 p_encoded => FND_API.G_FALSE
1877 );
1878
1879 END Complete_Schedule;
1880
1881
1882 --========================================================================
1883 -- PROCEDURE
1884 -- Activate_Schedule
1885 --
1886 -- PURPOSE
1887 -- This api is created to be used by concurrent program to activate
1888 -- schedules. It will internally call the Activate schedules api to
1889 -- activate the schedule.
1890
1891 --
1892 -- HISTORY
1893 -- 17-Mar-2001 ptendulk Created.
1894 --
1895 --========================================================================
1896 PROCEDURE Activate_Schedule
1897 (errbuf OUT NOCOPY VARCHAR2,
1898 retcode OUT NOCOPY NUMBER)
1899 IS
1900 l_return_status VARCHAR2(1) ;
1901 l_msg_count NUMBER ;
1902 l_msg_data VARCHAR2(2000);
1903 l_api_version NUMBER := 1.0 ;
1904 BEGIN
1905 FND_MSG_PUB.initialize;
1906
1907 Complete_Schedule(
1908 p_api_version => l_api_version ,
1909
1910 x_return_status => l_return_status,
1911 x_msg_count => l_msg_count,
1912 x_msg_data => l_msg_data
1913 ) ;
1914 -- Write_log ;
1915 Ams_Utility_Pvt.Write_Conc_log ;
1916
1917 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)THEN
1918 retcode :=0;
1919 ELSE
1920 retcode := 2;
1921 errbuf := l_msg_data ;
1922 END IF;
1923 END Activate_Schedule;
1924
1925
1926 --========================================================================
1927 -- PROCEDURE
1928 -- Update_Schedule_Status
1929 --
1930 -- PURPOSE
1931 -- This api is created to be used for schedule status changes.
1932 --
1933 -- HISTORY
1934 -- 28-Mar-2001 ptendulk Created.
1935 -- 24-May-2001 ptendulk Added check to validate marketing medium before
1936 -- schedule goes active.
1937 -- 12-Jun-2001 ptendulk Event type schedule can go active without marketing
1938 -- medium.
1939 -- 04-dec-2001 soagrawa Modified code for 0 budget approvals.
1940 -- 04-dec-2001 soagrawa Modified condition for checking for existence of target group
1941 -- Now looking for only direct marketing of type email/fax/telemarketing
1942 -- 25-Oct-2002 soagrawa Added code for automatic budget line approval enh# 2445453
1943 -- 30-sep-2003 soagrawa Modified code for cover letter id retrieval and validation
1944 --========================================================================
1945 PROCEDURE Update_Schedule_Status(
1946 p_schedule_id IN NUMBER,
1947 p_campaign_id IN NUMBER,
1948 p_user_status_id IN NUMBER,
1949 p_budget_amount IN NUMBER,
1950 p_asn_group_id IN VARCHAR2 DEFAULT NULL -- anchaudh added for leads bug.
1951 )
1952 IS
1953
1954 l_budget_exist NUMBER;
1955 l_old_status_id NUMBER;
1956 l_new_status_id NUMBER;
1957 l_deny_status_id NUMBER;
1958 l_object_version NUMBER;
1959 l_approval_type VARCHAR2(30);
1960 l_return_status VARCHAR2(1);
1961 l_start_time DATE;
1962 l_timezone NUMBER;
1963 l_start_wf_process VARCHAR2(1) := 'Y';
1964
1965 CURSOR c_old_status IS
1966 SELECT user_status_id, object_version_number,
1967 start_date_time, timezone_id,activity_type_code,
1968 activity_id,marketing_medium_id,custom_setup_id,
1969 cover_letter_id, printer_address
1970 FROM ams_campaign_schedules_b
1971 WHERE schedule_id = p_schedule_id;
1972
1973 CURSOR c_budget_exist IS
1974 SELECT 1
1975 FROM DUAL
1976 WHERE EXISTS(
1977 SELECT 1
1978 FROM ozf_act_budgets
1979 WHERE arc_act_budget_used_by = 'CSCH'
1980 AND act_budget_used_by_id = p_schedule_id);
1981
1982 CURSOR c_camp_status IS
1983 SELECT status_code
1984 FROM ams_campaigns_all_b
1985 WHERE campaign_id = p_campaign_id ;
1986
1987 -- soagrawa added the following cursor on 30-sep-2003 for stamping version in 11.5.10
1988 CURSOR c_cover_letter_det IS
1989 SELECT ci.live_citem_version_id
1990 FROM ibc_associations assoc, ibc_content_Items ci
1991 WHERE assoc.association_type_code = 'AMS_CSCH'
1992 AND assoc.associated_object_val1 = to_char(p_schedule_id) -- fix for bug # 4145845
1993 AND assoc.content_item_id = ci.content_Item_id;
1994
1995 -- dbiswas added the following cursor on 23-mar-2003 for content item approval in 11.5.10
1996 CURSOR c_attr_available (p_custom_setup_id IN NUMBER)IS
1997 SELECT attr_available_flag
1998 FROM ams_custom_Setup_attr atr,
1999 ams_Custom_setups_vl vl
2000 WHERE vl.object_type ='CSCH'
2001 AND vl.custom_Setup_id = atr.custom_Setup_id
2002 AND atr.object_attribute in ('COLLAB','MEDIA_PLANNER')
2003 AND vl.custom_setup_id = p_custom_setup_id;
2004
2005 -- dbiswas added the following cursor on 26-may-2005 for pretty URL uniqueness in 11.5.10.RUP4
2006 CURSOR c_system_url (p_schedule_id IN NUMBER)IS
2007 SELECT system_url, pretty_url_id, ctd_id
2008 FROM ams_system_pretty_url sysUrl,
2009 ams_pretty_url_assoc assoc
2010 WHERE assoc.used_by_obj_type ='CSCH'
2011 AND assoc.used_by_obj_id = p_schedule_id
2012 AND assoc.system_url_id = sysUrl.system_url_id;
2013
2014 -- dbiswas added the following 2 cursors on 30 Aug 06 for validating PU and CTD reqd fields
2015 CURSOR c_pretty_url (p_pretty_url_id IN NUMBER)IS
2016 SELECT *
2017 FROM ams_pretty_url
2018 WHERE pretty_url_id = p_pretty_url_id;
2019
2020 CURSOR c_ctd_items (p_ctd_id IN NUMBER)IS
2021 SELECT ctd_id,
2022 action_id,
2023 forward_url,
2024 track_url,
2025 activity_product_id,
2026 activity_offer_id,
2027 object_version_number,
2028 last_update_date,
2029 last_updated_by,
2030 creation_date,
2031 created_by,
2032 last_update_login,
2033 security_group_id
2034 FROM ams_ctds
2035 WHERE ctd_id = p_ctd_id;
2036
2037 l_status_code VARCHAR2(30);
2038 l_activity_type_code VARCHAR2(30);
2039 l_activity_id NUMBER ;
2040 l_marketing_med_id NUMBER ;
2041 l_schedule_status_code VARCHAR2(30) := AMS_Utility_PVT.get_system_status_code(p_user_status_id) ;
2042 l_custom_setup_id NUMBER;
2043 l_cover_letter_id NUMBER ;
2044
2045 l_msg_count NUMBER ;
2046 l_msg_data VARCHAR2(2000);
2047 l_cover_letter_ver_id NUMBER; -- soagrawa added 30-sep-2003 or 11.5.10
2048 l_printer_address VARCHAR2(255);
2049 l_fulfilment VARCHAR2(30);
2050 l_attr_available VARCHAR2(30);
2051 l_system_url VARCHAR2(4000); -- dbiswas added 26May05 for 11.5.10.RUP4
2052 l_pretty_url_id NUMBER; -- dbiswas added 30Aug06 for R12 bug 5477945
2053 l_ctd_id NUMBER;
2054 l_pretty_url_rec AMS_PRETTY_URL_PVT.pretty_url_rec_type;
2055 l_ctd_rec AMS_CTD_PVT.ctd_rec_type;
2056
2057 x_status_code VARCHAR2(30);
2058
2059 BEGIN
2060
2061 OPEN c_old_status;
2062 FETCH c_old_status INTO l_old_status_id, l_object_version, l_start_time,
2063 l_timezone, l_activity_type_code, l_activity_id, l_marketing_med_id,l_custom_setup_id,l_cover_letter_id, l_printer_address ;
2064 CLOSE c_old_status;
2065
2066 IF l_old_status_id = p_user_status_id THEN
2067 RETURN;
2068 END IF;
2069
2070 -- Follwing code is modified by ptendulk on 10-Jul-2001
2071 -- The old procedure is replaced by new to check the type
2072 -- of the approval required as ams_object_attribute table is
2073 -- obsoleted now.
2074 AMS_Utility_PVT.check_new_status_change(
2075 p_object_type => 'CSCH',
2076 p_object_id => p_schedule_id,
2077 p_old_status_id => l_old_status_id,
2078 p_new_status_id => p_user_status_id,
2079 p_custom_setup_id => l_custom_setup_id,
2080 x_approval_type => l_approval_type,
2081 x_return_status => l_return_status
2082 );
2083
2084
2085 IF l_return_status <> FND_API.g_ret_sts_success THEN
2086 RAISE FND_API.g_exc_error;
2087 END IF;
2088
2089 -- dbiswas added the following pretty url check for bug 4472099
2090 IF l_schedule_status_code = 'SUBMITTED_BA'
2091 THEN
2092 IF (AMS_DEBUG_HIGH_ON) THEN
2093 AMS_Utility_PVT.debug_message('Is Pretty URL supported for schedule '||p_schedule_id||' with Activity Type = '||l_activity_type_code ||' and activity id = '||l_activity_id);
2094 END IF;
2095 IF (((l_activity_type_code = 'DIRECT_MARKETING') AND ((l_activity_id <> 20) AND (l_activity_id <> 460 )))
2096 OR((l_activity_type_code = 'BROADCAST') OR (l_activity_type_code = 'PUBLIC_RELATIONS') OR (l_activity_type_code = 'IN_STORE')))
2097 THEN
2098 IF (AMS_DEBUG_HIGH_ON) THEN
2099 AMS_Utility_PVT.debug_message('Pretty URL IS supported for schedule '||p_schedule_id||' with Activity Type = '||l_activity_type_code ||' and activity id = '||l_activity_id);
2100 END IF;
2101 OPEN c_system_url(p_schedule_id);
2102 FETCH c_system_url INTO l_system_url, l_pretty_url_id, l_ctd_id ;
2103 CLOSE c_system_url;
2104 IF(l_system_url IS NOT NULL) THEN
2105 -- dbiswas added the following checks for bug 5477945. Mandatory fields check for PrettyUrl
2106 OPEN c_pretty_url(l_pretty_url_id);
2107 FETCH c_pretty_url INTO l_pretty_url_rec;
2108 CLOSE c_pretty_url;
2109 IF (l_pretty_url_rec.pretty_url_id IS NOT NULL) THEN
2110 AMS_PRETTY_URL_PVT.CHECK_PU_MANDATORY_FIELDS(
2111 p_pretty_url_rec => l_pretty_url_rec,
2112 x_return_status => l_return_status);
2113 IF l_return_status <> FND_API.g_ret_sts_success THEN
2114 RAISE FND_API.g_exc_error;
2115 END IF;
2116 ELSE --Pretty URL rec not found, but system url exists. ERROR
2117 RAISE FND_API.g_exc_error;
2118 END IF;
2119
2120 -- Mandatory fields check for CTD
2121 OPEN c_ctd_items(l_ctd_id);
2122 FETCH c_ctd_items INTO l_ctd_rec;
2123 CLOSE c_ctd_items;
2124 IF (l_ctd_rec.ctd_id IS NOT NULL) THEN
2125 AMS_CTD_PVT.CHECK_MANDATORY_FIELDS(
2126 p_ctd_rec => l_ctd_rec,
2127 x_return_status => l_return_status
2128 );
2129 IF l_return_status <> FND_API.g_ret_sts_success THEN
2130 RAISE FND_API.g_exc_error;
2131 END IF ;
2132 ELSE --CTD Referenced in System url but does not exist. ERROR
2133 RAISE FND_API.g_exc_error;
2134 END IF ;
2135
2136 AMS_PRETTY_URL_PVT.IS_SYSTEM_URL_UNIQ(p_sys_url => l_system_url ,
2137 p_current_used_by_id => p_schedule_id,
2138 p_current_used_by_type => 'CSCH',
2139 x_return_status => l_return_status);
2140 IF l_return_status <> FND_API.g_ret_sts_success THEN
2141 RAISE FND_API.g_exc_error;
2142 END IF ;
2143 --
2144 END IF;
2145 END IF;
2146 END IF; -- end bug fix # 4472099
2147
2148 -- Schedule Can not go active unless the campaign is Active
2149 -- Schedule Camapign Rule 2/5
2150 IF l_schedule_status_code = 'ACTIVE' OR
2151 -- Following line is added by ptendulk on 06-Oct-2001
2152 l_schedule_status_code = 'AVAILABLE'
2153 THEN
2154
2155 -- anchaudh : calling validate activation rules api from R12 onwards; for any activity validation rule, going forward.
2156 validate_activation_rules(p_scheduleid => p_schedule_id , x_status_code => x_status_code);
2157 IF x_status_code <> FND_API.g_ret_sts_success THEN
2158 RAISE FND_API.g_exc_error;
2159 END IF ;
2160
2161 -- Following line of code is added by ptendulk on 08-Jul-2001
2162 --Check if the schedule has target group attached and generated.
2163 -- Following line is modified by ptendulk on 06-Oct-2001 .
2164 -- IF l_activity_type_code IN ('DIRECT_MARKETING','INTERNET','DEAL','TRADE_PROMOTION') THEN
2165 -- SALES related stuff added by asaha on 18th Feb, 2004
2166 IF (l_activity_type_code = 'DIRECT_MARKETING' OR l_activity_type_code = 'DIRECT_SALES') THEN
2167 -- following line added by soagrawa on 04-dec-2001
2168 -- modified by soagrawa on 15-aug-2002 for bug# 2515493 - added direct mail 480
2169 IF (l_activity_id = 10 OR l_activity_id = 20 OR l_activity_id = 460 OR l_activity_id = 480 OR l_activity_id = 500) THEN
2170 IF FND_API.G_FALSE = Target_Group_Exist(p_schedule_id) THEN
2171 AMS_Utility_PVT.Error_Message('AMS_CSCH_NO_TARGET_GROUP');
2172 RAISE FND_API.g_exc_error;
2173 END IF ;
2174 END IF;
2175
2176 -- see if live cover letter version exists for email, fax, print
2177 -- soagrawa modified the way l_cover_letter_id is populated on 30-sep-2003 for 11.5.10
2178 OPEN c_cover_letter_det;
2179 FETCH c_cover_letter_det INTO l_cover_letter_ver_id;
2180 CLOSE c_cover_letter_det;
2181 -- soagrawa added 480 on 30-sep-2003 for 11.5.10
2182 IF (l_activity_id = 20 OR l_activity_id = 10 OR l_activity_id = 480)
2183 AND l_cover_letter_ver_id IS NULL THEN
2184 AMS_Utility_PVT.Error_Message('AMS_CSCH_NO_COVER_LETTER');
2185 RAISE FND_API.g_exc_error;
2186 END IF ;
2187
2188 -- soagrawa added printer validation on 18-nov-2003 for 11.5.10
2189 l_fulfilment := FND_PROFILE.Value('AMS_FULFILL_ENABLE_FLAG');
2190 IF l_activity_id = 480
2191 AND l_fulfilment <> 'N'
2192 AND l_printer_address IS NULL THEN
2193 AMS_Utility_PVT.Error_Message('AMS_CSCH_NO_PRINTER');
2194 RAISE FND_API.g_exc_error;
2195 END IF ;
2196
2197 END IF ;
2198
2199 --anchaudh : commenting out the call to AMS_ActProduct_PVT.IS_ALL_CONTENT_APPROVED for R12 .
2200
2201 --dbiswas added content validation for Collab midtab on 18-Mar-2004 for 11.5.10
2202 /*OPEN c_attr_available(l_custom_setup_id);
2203 FETCH c_attr_available INTO l_attr_available;
2204 CLOSE c_attr_available;
2205 IF (l_attr_available = 'Y') THEN
2206 AMS_ActProduct_PVT.IS_ALL_CONTENT_APPROVED (p_schedule_id => p_schedule_id,
2207 x_return_status => l_return_status);
2208 IF l_return_status <> 'Y'
2209 THEN AMS_Utility_PVT.Error_Message('AMS_CONTENT_NOT_APPROVED');
2210 RAISE FND_API.g_exc_error;
2211 END IF ;
2212 END IF;*/
2213
2214 --anchaudh: from R12 onwards, the above content valdation would be taken care of in the api : validate_activation_rules
2215
2216
2217 --dbiswas added pretty URL uniqueness check for pretty URL region on May 26, 2005 for 11.5.10.RUP4
2218 IF (AMS_DEBUG_HIGH_ON) THEN
2219 AMS_Utility_PVT.debug_message('Is Pretty URL supported for schedule '||p_schedule_id||' with Activity Type = '||l_activity_type_code ||' and activity id = '||l_activity_id);
2220 END IF;
2221 IF (((l_activity_type_code = 'DIRECT_MARKETING') AND ((l_activity_id <> 20) AND (l_activity_id <> 460 )))
2222 OR((l_activity_type_code = 'BROADCAST') OR (l_activity_type_code = 'PUBLIC_RELATIONS') OR (l_activity_type_code = 'IN_STORE')))
2223 THEN
2224 IF (AMS_DEBUG_HIGH_ON) THEN
2225 AMS_Utility_PVT.debug_message('Pretty URL IS supported for schedule '||p_schedule_id||' with Activity Type = '||l_activity_type_code ||' and activity id = '||l_activity_id);
2226 END IF;
2227 OPEN c_system_url(p_schedule_id);
2228 FETCH c_system_url INTO l_system_url, l_pretty_url_id, l_ctd_id;
2229 CLOSE c_system_url;
2230 IF(l_system_url IS NOT NULL) THEN
2231 -- dbiswas added the following checks for bug 5477945. Mandatory fields check for PrettyUrl
2232 OPEN c_pretty_url(l_pretty_url_id);
2233 FETCH c_pretty_url INTO l_pretty_url_rec;
2234 CLOSE c_pretty_url;
2235 IF (l_pretty_url_rec.pretty_url_id IS NOT NULL) THEN
2236 AMS_PRETTY_URL_PVT.CHECK_PU_MANDATORY_FIELDS(
2237 p_pretty_url_rec => l_pretty_url_rec,
2238 x_return_status => l_return_status);
2239 IF l_return_status <> FND_API.g_ret_sts_success THEN
2240 RAISE FND_API.g_exc_error;
2241 END IF;
2242 ELSE --Pretty URL rec not found, but system url exists. ERROR
2243 RAISE FND_API.g_exc_error;
2244 END IF;
2245
2246 -- Mandatory fields check for CTD
2247 OPEN c_ctd_items(l_ctd_id);
2248 FETCH c_ctd_items INTO l_ctd_rec;
2249 CLOSE c_ctd_items;
2250 IF (l_ctd_rec.ctd_id IS NOT NULL) THEN
2251 AMS_CTD_PVT.CHECK_MANDATORY_FIELDS(
2252 p_ctd_rec => l_ctd_rec,
2253 x_return_status => l_return_status
2254 );
2255 IF l_return_status <> FND_API.g_ret_sts_success THEN
2256 RAISE FND_API.g_exc_error;
2257 END IF ;
2258 ELSE --CTD Referenced in System url but does not exist. ERROR
2259 RAISE FND_API.g_exc_error;
2260 END IF ;
2261
2262 AMS_PRETTY_URL_PVT.IS_SYSTEM_URL_UNIQ(p_sys_url => l_system_url ,
2263 p_current_used_by_id => p_schedule_id,
2264 p_current_used_by_type => 'CSCH',
2265 x_return_status => l_return_status);
2266 IF l_return_status <> FND_API.g_ret_sts_success THEN
2267 RAISE FND_API.g_exc_error;
2268 END IF ;
2269 END IF;
2270 END IF;
2271
2272
2273 IF l_marketing_med_id IS NULL THEN
2274 IF l_activity_type_code <> 'DIRECT_MARKETING' AND
2275 l_activity_type_code <> 'INTERNET' AND
2276 -- Following line of code is added by ptendulk on 12-Jun-2001
2277 -- Mktg medium is not mandatory for event type schedules
2278 l_activity_type_code <> 'EVENTS' AND
2279 -- Following Line of code is added by ptendulk on 06-Oct-2001
2280 l_activity_type_code <> 'DEAL' AND
2281 l_activity_type_code <> 'TRADE_PROMOTION' AND
2282 -- Following Line of code is added by asaha on 09-Sep-2003 for Sales Channel
2283 l_activity_type_code <> 'DIRECT_SALES'
2284 THEN
2285 AMS_Utility_PVT.Error_Message('AMS_CAMP_CHANNEL_REQUIRED');
2286 RAISE FND_API.g_exc_error;
2287 END IF ;
2288 END IF ;
2289
2290 OPEN c_camp_status ;
2291 FETCH c_camp_status INTO l_status_code ;
2292 CLOSE c_camp_status;
2293
2294 IF l_status_code <> 'ACTIVE' THEN
2295 AMS_Utility_PVT.Error_Message('AMS_CSCH_CAMP_NO_ACTIVE');
2296 RAISE FND_API.g_exc_error;
2297 END IF ;
2298 END IF ; -- Active or Available
2299
2300
2301 IF l_approval_type = 'BUDGET' THEN
2302
2303 /* vmodur 19-Dec-2005 */
2304 AMS_Approval_PVT.Must_Preview(
2305 p_activity_id => p_schedule_id,
2306 p_activity_type => 'CSCH',
2307 p_approval_type => 'BUDGET',
2308 p_act_budget_id => null,
2309 p_requestor_id => AMS_Utility_PVT.get_resource_id(FND_GLOBAL.user_id),
2310 x_must_preview => l_start_wf_process,
2311 x_return_status => l_return_status);
2312
2313 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2314 RAISE FND_API.G_EXC_ERROR;
2315 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2316 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2317 END IF;
2318
2319 /* vmodur 19-Dec-2005 */
2320 IF (l_start_wf_process = 'Y') THEN -- If the user is not the approver and budget approval reqd
2321 -- start budget approval process
2322 l_new_status_id := AMS_Utility_PVT.get_default_user_status(
2323 'AMS_CAMPAIGN_SCHEDULE_STATUS',
2324 'SUBMITTED_BA'
2325 );
2326 l_deny_status_id := AMS_Utility_PVT.get_default_user_status(
2327 'AMS_CAMPAIGN_SCHEDULE_STATUS',
2328 'DENIED_BA'
2329 );
2330
2331 AMS_Approval_PVT.StartProcess(
2332 p_activity_type => 'CSCH',
2333 p_activity_id => p_schedule_id,
2334 p_approval_type => l_approval_type,
2335 p_object_version_number => l_object_version,
2336 p_orig_stat_id => l_old_status_id,
2337 p_new_stat_id => p_user_status_id,
2338 p_reject_stat_id => l_deny_status_id,
2339 p_requester_userid => AMS_Utility_PVT.get_resource_id(FND_GLOBAL.user_id),
2340 p_workflowprocess => 'AMS_APPROVAL',
2341 p_item_type => 'AMSAPRV'
2342 );
2343 ELSE -- If user equals approver and budget approval reqd
2344 IF (AMS_DEBUG_HIGH_ON) THEN
2345 AMS_Utility_PVT.Debug_Message('No need to start Workflow Process for Approval, Status Code ' || l_schedule_status_code );
2346 END IF;
2347 -- Following budget line api call added by soagrawa on 25-oct-2002
2348 -- for enhancement # 2445453
2349
2350 IF l_schedule_status_code = 'ACTIVE' THEN
2351 OZF_BudgetApproval_PVT.budget_request_approval(
2352 p_init_msg_list => FND_API.G_FALSE
2353 , p_api_version => 1.0
2354 , p_commit => FND_API.G_False
2355 , x_return_status => l_return_status
2356 , x_msg_count => l_msg_count
2357 , x_msg_data => l_msg_data
2358 , p_object_type => 'CSCH'
2359 , p_object_id => p_schedule_id
2360 --, x_status_code =>
2361 );
2362
2363 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2364 RAISE FND_API.G_EXC_ERROR;
2365 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2366 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2367 END IF;
2368 END IF; -- Active
2369 l_new_status_id := p_user_status_id;
2370
2371 END IF; -- IF budget approval reqd
2372
2373 ELSE -- No BUDGET Approval
2374
2375 IF (AMS_DEBUG_HIGH_ON) THEN
2376 AMS_Utility_PVT.Debug_Message('No Approval' || l_schedule_status_code );
2377 END IF;
2378 -- Following budget line api call added by soagrawa on 25-oct-2002
2379 -- for enhancement # 2445453
2380
2381 IF l_schedule_status_code = 'ACTIVE' THEN
2382 OZF_BudgetApproval_PVT.budget_request_approval(
2383 p_init_msg_list => FND_API.G_FALSE
2384 , p_api_version => 1.0
2385 , p_commit => FND_API.G_False
2386 , x_return_status => l_return_status
2387 , x_msg_count => l_msg_count
2388 , x_msg_data => l_msg_data
2389 , p_object_type => 'CSCH'
2390 , p_object_id => p_schedule_id
2391 --, x_status_code =>
2392 );
2393
2394 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2395 RAISE FND_API.G_EXC_ERROR;
2396 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2397 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2398 END IF;
2399 END IF;
2400 l_new_status_id := p_user_status_id;
2401
2402 END IF; -- If Budget
2403
2404 --insert_log_mesg('Anirban got value of asn_group_id in api Update_Schedule_Status in amsvsbrb.pls as :'||p_asn_group_id);
2405
2406 update_status(p_schedule_id => p_schedule_id,
2407 p_new_status_id => l_new_status_id,
2408 p_new_status_code => AMS_Utility_PVT.get_system_status_code(l_new_status_id),
2409 p_asn_group_id => p_asn_group_id -- anchaudh added for leads bug.
2410 );
2411
2412 END Update_Schedule_Status;
2413
2414
2415
2416 --========================================================================
2417 -- PROCEDURE
2418 -- Create_list
2419 --
2420 -- PURPOSE
2421 -- This api is called after the creation of the Direct marketing schedules
2422 -- to create the default target group for the schedule. User can go to the
2423 -- target group screen to modify the details.
2424 --
2425 -- NOTE
2426 -- The list of Type Target is created in list header and the association is
2427 -- created in the ams_act_lists table.
2428 --
2429 -- HISTORY
2430 -- 18-May-2001 ptendulk Created.
2431 -- 18-Aug-2001 ptendulk Modified the Target group name
2432 --
2433 --========================================================================
2434 PROCEDURE Create_list
2435 (p_schedule_id IN NUMBER,
2436 p_schedule_name IN VARCHAR2,
2437 p_owner_id IN NUMBER)
2438 IS
2439 l_return_status VARCHAR2(1) ;
2440 l_msg_count NUMBER ;
2441 l_msg_data VARCHAR2(2000);
2442 l_api_version NUMBER := 1.0 ;
2443
2444 l_list_header_rec AMS_ListHeader_Pvt.list_header_rec_type;
2445 l_act_list_rec AMS_Act_List_Pvt.act_list_rec_type;
2446 l_list_header_id NUMBER ;
2447 l_act_list_header_id NUMBER ;
2448
2449 l_tmp NUMBER ;
2450
2451 BEGIN
2452 NULL;
2453 /* Following code is modified by ptendulk on 25-Oct-2001
2454 As we don't have to create the target group for schedules at
2455 schedule creation.
2456 -- AMS_ListHeader_PVT.init_listheader_rec(l_list_header_rec);
2457 l_list_header_rec.list_name := p_schedule_name ||TO_CHAR(p_schedule_id)||' - '||AMS_Utility_PVT.get_lookup_meaning('AMS_SYS_ARC_QUALIFIER','TGRP');
2458 l_list_header_rec.list_type := 'TARGET';
2459 -- Have to be removed.
2460 l_list_header_rec.list_source_type := 'PERSON_LIST' ;
2461 l_list_header_rec.owner_user_id := p_owner_id;
2462 AMS_ListHeader_PVT.Create_Listheader
2463 ( p_api_version => 1.0,
2464 p_init_msg_list => FND_API.g_false,
2465 p_commit => FND_API.g_false,
2466 p_validation_level => FND_API.g_valid_level_full,
2467
2468 x_return_status => l_return_status,
2469 x_msg_count => l_msg_count,
2470 x_msg_data => l_msg_data,
2471 p_listheader_rec => l_list_header_rec,
2472 x_listheader_id => l_list_header_id
2473 );
2474
2475 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2476 RAISE FND_API.G_EXC_ERROR;
2477 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2478 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2479 END IF;
2480
2481 l_act_list_rec.list_header_id := l_list_header_id;
2482 l_act_list_rec.list_used_by := 'CSCH';
2483 l_act_list_rec.list_used_by_id := p_schedule_id ;
2484 l_act_list_rec.list_act_type := 'TARGET';
2485
2486 AMS_Act_List_PVT.Create_Act_List(
2487 p_api_version_number => 1.0,
2488 p_init_msg_list => FND_API.g_false,
2489 p_commit => FND_API.g_false,
2490 p_validation_level => FND_API.g_valid_level_full,
2491 x_return_status => l_return_status,
2492 x_msg_count => l_msg_count,
2493 x_msg_data => l_msg_data,
2494 p_act_list_rec => l_act_list_rec ,
2495 x_act_list_header_id => l_act_list_header_id
2496 ) ;
2497
2498 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2499 RAISE FND_API.G_EXC_ERROR;
2500 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2501 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2502 END IF;
2503
2504 */
2505 END Create_list;
2506
2507
2508
2509 --========================================================================
2510 -- PROCEDURE
2511 -- Create_Schedule_Access
2512 --
2513 -- PURPOSE
2514 -- This api is called in Create schedule api to give the access for
2515 -- schedule to the team members of the campaign.
2516 --
2517 -- NOTE
2518 --
2519 -- HISTORY
2520 -- 11-Sep-2001 ptendulk Created.
2521 --
2522 --========================================================================
2523 PROCEDURE Create_Schedule_Access(p_schedule_id IN NUMBER,
2524 p_campaign_id IN NUMBER,
2525 p_owner_id IN NUMBER,
2526 p_init_msg_list IN VARCHAR2,
2527 p_commit IN VARCHAR2,
2528 p_validation_level IN NUMBER,
2529
2530 x_return_status OUT NOCOPY VARCHAR2,
2531 x_msg_count OUT NOCOPY NUMBER,
2532 x_msg_data OUT NOCOPY VARCHAR2
2533 )
2534 IS
2535
2536 CURSOR c_access_det IS
2537 SELECT *
2538 FROM ams_act_access
2539 WHERE arc_act_access_to_object = 'CAMP'
2540 AND act_access_to_object_id = p_campaign_id ;
2541 l_access_det c_access_det%ROWTYPE;
2542
2543 l_access_rec AMS_Access_Pvt.access_rec_type ;
2544 l_dummy_id NUMBER ;
2545
2546 BEGIN
2547
2548 l_access_rec.act_access_to_object_id := p_schedule_id ;
2549 l_access_rec.arc_act_access_to_object := 'CSCH' ;
2550 l_access_rec.user_or_role_id := p_owner_id ;
2551 l_access_rec.arc_user_or_role_type := 'USER' ;
2552 l_access_rec.owner_flag := 'Y' ;
2553 l_access_rec.delete_flag := 'N' ;
2554 l_access_rec.admin_flag := 'Y' ;
2555
2556 AMS_Access_Pvt.Create_Access(
2557 p_api_version => 1,
2558 p_init_msg_list => p_init_msg_list,
2559 p_commit => p_commit,
2560 p_validation_level => p_validation_level,
2561
2562 x_return_status => x_return_status,
2563 x_msg_count => x_msg_count,
2564 x_msg_data => x_msg_data,
2565
2566 p_access_rec => l_access_rec,
2567 x_access_id => l_dummy_id
2568 );
2569 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2570 RAISE FND_API.G_EXC_ERROR;
2571 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2572 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2573 END IF;
2574
2575
2576 OPEN c_access_det ;
2577 LOOP
2578 FETCH c_access_det INTO l_access_det;
2579 EXIT WHEN c_access_det%NOTFOUND ;
2580
2581 IF l_access_det.arc_user_or_role_type = 'USER'
2582 AND l_access_det.user_or_role_id = p_owner_id
2583 THEN
2584 -- Entry of user is already gone is dont do anything
2585 NULL ;
2586 ELSE
2587 -- Create Access for the team /owner
2588 l_access_rec.owner_flag := 'N' ;
2589 l_access_rec.user_or_role_id := l_access_det.user_or_role_id ;
2590 l_access_rec.arc_user_or_role_type := l_access_det.arc_user_or_role_type ;
2591 l_access_rec.delete_flag := l_access_det.delete_flag ;
2592 --l_access_rec.admin_flag := l_access_rec.admin_flag ;
2593 l_access_rec.admin_flag := l_access_det.admin_flag ;--anchaudh: changed rec type to l_access_det.
2594
2595 AMS_Access_Pvt.Create_Access(
2596 p_api_version => 1,
2597 p_init_msg_list => p_init_msg_list,
2598 p_commit => p_commit,
2599 p_validation_level => p_validation_level,
2600
2601 x_return_status => x_return_status,
2602 x_msg_count => x_msg_count,
2603 x_msg_data => x_msg_data,
2604
2605 p_access_rec => l_access_rec,
2606 x_access_id => l_dummy_id
2607 );
2608 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2609 CLOSE c_access_det;
2610 RAISE FND_API.G_EXC_ERROR;
2611 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2612 CLOSE c_access_det;
2613 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2614 END IF;
2615
2616 END IF ;
2617
2618 END LOOP;
2619 CLOSE c_access_det ;
2620
2621 END Create_Schedule_Access ;
2622
2623
2624
2625
2626
2627 --========================================================================
2628 -- PROCEDURE
2629 -- get_user_id
2630 --
2631 -- PURPOSE
2632 -- This api will take a resource id and give the corresponding user_id
2633 --
2634 -- NOTE
2635 --
2636 -- HISTORY
2637 -- 19-mar-2002 soagrawa Created
2638 --========================================================================
2639
2640
2641 FUNCTION get_user_id (
2642 p_resource_id IN NUMBER
2643 )
2644 RETURN NUMBER
2645 IS
2646 l_user_id NUMBER;
2647
2648 CURSOR c_user IS
2649 SELECT user_id
2650 FROM ams_jtf_rs_emp_v
2651 WHERE resource_id = p_resource_id;
2652 BEGIN
2653 OPEN c_user;
2654 FETCH c_user INTO l_user_id;
2655 IF c_user%NOTFOUND THEN
2656 l_user_id := -1;
2657 -- Adding an error message will cause the function
2658 -- to violate the WNDS pragma, preventing it from
2659 -- being able to be called from a SQL statement.
2660 END IF;
2661 CLOSE c_user;
2662
2663 RETURN l_user_id;
2664 END get_user_id;
2665
2666
2667
2668 --========================================================================
2669 -- PROCEDURE
2670 -- write_interaction
2671 --
2672 -- PURPOSE
2673 -- This api is called in update_Status to write to interaction history
2674 -- if it was DIRECT_MARKETING Direct Mail
2675 --
2676 -- NOTE
2677 --
2678 -- HISTORY
2679 -- 19-mar-2002 soagrawa Created to log interactions for
2680 -- DIRECT_MARKETING MAIL
2681 -- 27-may-2003 soagrawa Fixed NI issue about result of interaction bug# 2978948
2682 --========================================================================
2683
2684 PROCEDURE write_interaction(
2685 p_schedule_id IN NUMBER
2686 )
2687
2688 IS
2689
2690 -- CURSOR:
2691 -- get the target grp for this CSCH
2692 -- get the list entries from that target group
2693 -- get the party_id for those list entries
2694
2695 CURSOR c_parties_det IS
2696 SELECT party_id
2697 FROM ams_list_entries
2698 WHERE list_header_id =
2699 (SELECT list_header_id
2700 FROM ams_act_lists
2701 WHERE list_used_by = 'CSCH'
2702 AND list_act_type = 'TARGET'
2703 AND list_used_by_id = p_schedule_id)
2704 AND enabled_flag = 'Y';
2705
2706
2707 CURSOR c_sch_det IS
2708 SELECT start_date_time, end_date_time, owner_user_id, source_code
2709 FROM ams_campaign_schedules_b
2710 WHERE schedule_id = p_schedule_id;
2711
2712 CURSOR c_media_item_id IS
2713 SELECT JTF_IH_MEDIA_ITEMS_S1.NEXTVAL
2714 FROM dual;
2715
2716 CURSOR c_interactions_id IS
2717 SELECT jtf_ih_interactions_s1.NEXTVAL
2718 FROM dual;
2719
2720 CURSOR c_activities_id IS
2721 SELECT JTF_IH_ACTIVITIES_S1.NEXTVAL
2722 FROM dual;
2723
2724 l_interaction_rec JTF_IH_PUB.interaction_rec_type;
2725 l_activities JTF_IH_PUB.activity_tbl_type;
2726 l_activity_rec JTF_IH_PUB.activity_rec_type;
2727 l_media_rec JTF_IH_PUB.media_rec_type;
2728 l_interaction_id NUMBER;
2729 l_media_id NUMBER;
2730 l_party_id NUMBER;
2731 l_schedule_start_time DATE;
2732 l_schedule_end_time DATE;
2733 l_schedule_owner_id NUMBER;
2734 l_schedule_source_code VARCHAR2(30);
2735
2736 l_return_status VARCHAR2(1);
2737 l_msg_count NUMBER;
2738 l_msg_data VARCHAR2(2000);
2739 l_user_id NUMBER;
2740
2741 BEGIN
2742
2743 OPEN c_sch_det;
2744 FETCH c_sch_det INTO l_schedule_start_time, l_schedule_end_time, l_schedule_owner_id, l_schedule_source_code;
2745 CLOSE c_sch_det;
2746
2747 l_user_id := get_user_id(p_resource_id => l_schedule_owner_id);
2748
2749 -- populate media_rec
2750 OPEN c_media_item_id;
2751 FETCH c_media_item_id INTO l_media_rec.media_id ;
2752 CLOSE c_media_item_id;
2753 -- l_media_rec.media_id := JTF_IH_MEDIA_ITEMS_S1.nextval;
2754 l_media_rec.end_date_time := l_schedule_end_time ;
2755 l_media_rec.start_date_time := l_schedule_start_time ;
2756 l_media_rec.media_item_type := 'MAIL' ;
2757
2758 -- create media_rec
2759 JTF_IH_PUB.Create_MediaItem
2760 (
2761 p_api_version => 1.0,
2762 p_init_msg_list => FND_API.g_false,
2763 p_commit => FND_API.g_false,
2764 -- p_resp_appl_id => l_resp_appl_id,
2765 -- p_resp_id => l_resp_id,
2766 p_user_id => l_user_id,
2767 -- p_login_id => l_login_id,
2768 x_return_status => l_return_status,
2769 x_msg_count => l_msg_count,
2770 x_msg_data => l_msg_data,
2771 p_media_rec => l_media_rec,
2772 x_media_id => l_media_id
2773 );
2774 IF l_return_status <> FND_API.g_ret_sts_success THEN
2775 RAISE FND_API.g_exc_error;
2776 RETURN;
2777 END IF;
2778
2779 IF (AMS_DEBUG_HIGH_ON) THEN
2780 AMS_Utility_PVT.debug_message('Write interaction: created media item ');
2781 END IF;
2782
2783 -- loop for each party id found
2784 OPEN c_parties_det;
2785 LOOP
2786 FETCH c_parties_det INTO l_party_id ;
2787 EXIT WHEN c_parties_det%NOTFOUND ;
2788
2789 IF (AMS_DEBUG_HIGH_ON) THEN
2790
2791
2792
2793 AMS_Utility_PVT.debug_message('Write interaction: looping for party id ');
2794
2795 END IF;
2796
2797 -- populate interaction record
2798 /*OPEN c_interactions_id;
2799 FETCH c_interactions_id INTO l_interaction_id ;
2800 CLOSE c_interactions_id;*/
2801 -- l_interaction_id := jtf_ih_interactions_s1.nextval ;
2802
2803 l_interaction_rec.interaction_id := l_interaction_id ;
2804 l_interaction_rec.end_date_time := l_schedule_end_time ;
2805 l_interaction_rec.start_date_time := l_schedule_start_time ;
2806 l_interaction_rec.handler_id := 530 ;
2807 l_interaction_rec.outcome_id := 10 ; -- request processed
2808 -- soagrawa added on 27-may-2003 for NI interaction issue bug# 2978948
2809 l_interaction_rec.result_id := 8 ; -- sent
2810 l_interaction_rec.resource_id := l_schedule_owner_id ;
2811 l_interaction_rec.party_id := l_party_id ; -- looping for all party ids in the list
2812 l_interaction_rec.object_id := p_schedule_id ;
2813 l_interaction_rec.object_type := 'CSCH';
2814 l_interaction_rec.source_code := l_schedule_source_code;
2815
2816 -- populate activity record
2817 /*OPEN c_activities_id;
2818 FETCH c_activities_id INTO l_activity_rec.activity_id ;
2819 CLOSE c_activities_id;*/
2820 -- l_activity_rec.activity_id := JTF_IH_ACTIVITIES_S1.nextval ;
2821 l_activity_rec.end_date_time := l_schedule_end_time ;
2822 l_activity_rec.start_date_time := l_schedule_start_time ;
2823 l_activity_rec.media_id := l_media_id ;
2824 l_activity_rec.action_item_id := 3 ; -- collateral
2825 --l_activity_rec.interaction_id := l_interaction_id ;
2826 l_activity_rec.outcome_id := 10 ; -- request processed
2827 l_activity_rec.result_id := 8 ; -- sent
2828 l_activity_rec.action_id := 5 ; -- sent
2829
2830 -- populate activity table with the activity record
2831 l_activities(1) := l_activity_rec;
2832
2833 -- create interaction
2834 JTF_IH_PUB.Create_Interaction
2835 (
2836 p_api_version => 1.0,
2837 p_init_msg_list => FND_API.g_false,
2838 p_commit => FND_API.g_false,
2839 -- p_resp_appl_id => l_resp_appl_id, -- 530
2840 -- p_resp_id => l_resp_id, -- fnd global
2841 p_user_id => l_user_id,
2842 -- p_login_id => l_login_id,
2843 x_return_status => l_return_status,
2844 x_msg_count => l_msg_count,
2845 x_msg_data => l_msg_data,
2846 p_interaction_rec => l_interaction_rec,
2847 p_activities => l_activities
2848 );
2849 IF l_return_status <> FND_API.g_ret_sts_success THEN
2850 RAISE FND_API.g_exc_error;
2851 RETURN;
2852 END IF;
2853
2854 END LOOP;
2855 CLOSE c_parties_det;
2856
2857
2858
2859
2860 END write_interaction;
2861
2862
2863
2864
2865
2866
2867
2868
2869 --========================================================================
2870 -- PROCEDURE
2871 -- Update_Status
2872 --
2873 -- PURPOSE
2874 -- This api is called in Update schedule api (and in approvals' api)
2875 --
2876 -- NOTE
2877 --
2878 -- HISTORY
2879 -- 26-Sep-2001 soagrawa Created.
2880 -- 05-dec-2001 soagrawa Added code for updating status of the related event
2881 -- for schedules of type event
2882 -- 08-mar-2002 soagrawa Added code to call an events api if changing event schedule
2883 -- status to closed :fix for bug# 2254382
2884 -- 19-mar-2002 soagrawa Added code to fix bug# 2263166 regding TGRP purging
2885 -- 14-may-2002 soagrawa Modified for status of new schedule eblast
2886 -- 08-jul-2002 soagrawa Fixed content related bug# 2442744
2887 -- 26-jul-2002 soagrawa Fixed order of template approval and call to submit_conc_request
2888 -- for bug# 2463596
2889 -- 24-sep-2002 soagrawa Fixed condition for call to process_leads, refer to bug# 2582436
2890 -- 26-may-2003 anchaudh Called list api Update_Prev_contacted_count
2891 -- 24-Aug-2003 ptendulk Modified to call business event on the schedule activation
2892 -- 06-Sep-2003 ptendulk Modified the workflow parameter name to SCHEDULE_ID from AMS_SCHEDULE_ID
2893 -- 26-sep-2003 soagrawa Modified to accommodate triggers and repeating schedules
2894 -- 17-Mar-2005 spendem call the API to raise business event on status change as per enh # 3805347
2895 --========================================================================================================
2896 PROCEDURE update_status( p_schedule_id IN NUMBER,
2897 p_new_status_id IN NUMBER,
2898 p_new_status_code IN VARCHAR2,
2899 p_asn_group_id IN VARCHAR2 DEFAULT NULL -- anchaudh added for leads bug.
2900 )
2901 IS
2902
2903 CURSOR c_sch_det IS
2904 SELECT start_date_time, timezone_id,
2905 activity_type_code, activity_id,
2906 related_event_id -- soagrawa 05-dec-2001 - now also retrieving related event id.
2907 -- so as to update the event's status
2908 , user_status_id, status_code -- soagrawa 19-mar-2002
2909 , source_code -- soagrawa 22-oct-2002 for bug# 2594717
2910 , NVL(triggerable_flag,'N') -- soagrawa 26-sep-2003 for trigger and repeating schedule code change
2911 , NVL(trig_repeat_flag,'N') -- soagrawa 26-sep-2003 for trigger and repeating schedule code change
2912 , orig_csch_id -- soagrawa 26-sep-2003 for trigger and repeating schedule code change
2913 , owner_user_id -- vmodur
2914 , campaign_id -- vmodur
2915 FROM ams_campaign_schedules_b
2916 WHERE schedule_id = p_schedule_id;
2917
2918 l_source_code VARCHAR2(30);
2919 l_new_status_id NUMBER;
2920 l_activity_type_code VARCHAR2(30);
2921 l_activity_id NUMBER ;
2922 l_start_time DATE;
2923 l_sys_start_time DATE;
2924 l_timezone NUMBER;
2925 l_related_event_id NUMBER;
2926 l_old_status_id NUMBER;
2927 l_old_status_code VARCHAR2(30);
2928 l_triggerable_flag VARCHAR2(1); -- soagrawa 26-sep-2003 for trigger and repeating schedule code change
2929 l_trig_repeat_flag VARCHAR2(1); -- soagrawa 26-sep-2003 for trigger and repeating schedule code change
2930 l_orig_csch_id NUMBER; -- soagrawa 26-sep-2003 for trigger and repeating schedule code change
2931
2932 /* REMOVED BY SOAGRAWA ON 26-SEP-2003 : NOT BEING USED ANY MORE
2933 -- the following cursor and vars added by soagrawa
2934 -- on 19-mar-2002 for bug# 2263166
2935
2936 CURSOR c_tgrp_det
2937 IS SELECT list_header_id
2938 FROM ams_act_lists la
2939 WHERE list_act_type = 'TARGET'
2940 AND list_used_by = 'CSCH'
2941 AND list_used_by_id = p_schedule_id
2942 AND EXISTS (SELECT *
2943 FROM ams_list_entries le
2944 WHERE le.list_header_id = la.list_header_id) ;
2945 */
2946
2947 l_return_status VARCHAR2(1);
2948 l_msg_count NUMBER;
2949 l_msg_data VARCHAR2(2000);
2950 l_tgrp_id NUMBER;
2951
2952 /* REMOVED BY SOAGRAWA ON 26-SEP-2003 : NOT BEING USED ANY MORE
2953 -- the following cursor and variables added by soagrawa on 14-may-2002
2954 -- for approving item
2955 CURSOR c_template_det (p_content_item_id NUMBER)
2956 IS SELECT ver.citem_version_id, ver.object_version_number, ci.content_item_status
2957 FROM ibc_citem_versions_vl ver
2958 , ibc_content_items ci
2959 WHERE ci.content_item_id = p_content_item_id
2960 AND ci.content_item_id = ver.content_item_id;
2961 */
2962
2963 l_citem_ver_id NUMBER;
2964 l_RESOURCE_id NUMBER;
2965 p_num_asn_group_id number;
2966 l_obj_ver_num NUMBER;
2967 l_content_item_status VARCHAR2(20);
2968 l_def_flag VARCHAR2(1);
2969
2970 l_parameter_list WF_PARAMETER_LIST_T;
2971 l_new_item_key VARCHAR2(30);
2972 l_owner_user_id NUMBER;
2973 l_campaign_id NUMBER;
2974
2975 l_user_id NUMBER;
2976 l_resp_id NUMBER;
2977 l_resp_appl_id NUMBER;
2978 l_evo_rec AMS_EVENTOFFER_PVT.evo_rec_type; -- vmodur
2979
2980 -- dbiswas added the following cursor for bug 2852078
2981 CURSOR c_is_default_flag_on (p_user_status_id NUMBER)
2982 IS
2983 SELECT default_flag
2984 FROM ams_user_statuses_b
2985 WHERE user_status_id = p_user_status_id;
2986
2987 BEGIN
2988
2989 l_user_id := FND_GLOBAL.USER_ID;
2990 l_resp_id := FND_GLOBAL.RESP_ID;
2991 l_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
2992
2993 -- soagrawa on 19-mar-2002
2994 -- moved the cursor data retrieval from after update to before update
2995 OPEN c_sch_det;
2996 FETCH c_sch_det INTO l_start_time, l_timezone, l_activity_type_code, l_activity_id, l_related_event_id
2997 , l_old_status_id, l_old_status_code, l_source_code
2998 , l_triggerable_flag, l_trig_repeat_flag, l_orig_csch_id, l_owner_user_id, l_campaign_id;
2999 CLOSE c_sch_det;
3000
3001 UPDATE ams_campaign_schedules_b
3002 SET user_status_id = p_new_status_id,
3003 status_code = p_new_status_code, -- AMS_Utility_PVT.get_system_status_code(p_new_status_id),
3004 status_date = SYSDATE,
3005 object_version_number = object_version_number + 1,
3006 last_update_date = SYSDATE
3007 WHERE schedule_id = p_schedule_id;
3008
3009 -- call to api to raise business event, as per enh # 3805347
3010 RAISE_BE_ON_STATUS_CHANGE(p_obj_id => p_schedule_id,
3011 p_obj_type => 'CSCH',
3012 p_old_status_code => l_old_status_code,
3013 p_new_status_code => p_new_status_code );
3014
3015
3016 OPEN c_is_default_flag_on(p_new_status_id);
3017 FETCH c_is_default_flag_on INTO l_def_flag;
3018 CLOSE c_is_default_flag_on;
3019
3020 IF (p_new_status_code = 'ACTIVE' OR p_new_status_code = 'AVAILABLE')
3021 THEN
3022 IF ((l_old_status_code <> 'ON_HOLD' AND l_old_status_code <> 'AVAILABLE')
3023 -- Don't submit process if the status is updated from avail as in avail status
3024 -- there will be process created already
3025 AND l_def_flag = 'Y')
3026 THEN
3027
3028 -- soagrawa 26-sep-2003 Modified logic and code for trigger and repeating schedule code change
3029
3030 -- Logic:
3031 -- If it is a triggerable schedule, do nothing.
3032 -- If it is a non-triggerable repeating schedule's parent instance, raise business event for repeating schedule oracle.apps.ams.campaign.RepeatScheduleEvent with start date of the schedule.
3033 -- Otherwise, raise business event for schedule execution with start date of the schedule.
3034
3035 IF l_triggerable_flag <> 'Y' -- not triggerable
3036 THEN
3037 IF l_trig_repeat_flag = 'Y' AND l_orig_csch_id IS NULL -- repeating csch parent instance
3038 THEN
3039 l_new_item_key := p_schedule_id || 'RPT' || TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
3040 l_parameter_list := WF_PARAMETER_LIST_T();
3041 wf_event.AddParameterToList(p_name => 'SCHEDULE_ID',
3042 p_value => p_schedule_id,
3043 p_parameterlist => l_parameter_list);
3044
3045 AMS_UTILITY_PVT.Convert_Timezone(
3046 p_init_msg_list => FND_API.G_TRUE,
3047 x_return_status => l_return_status,
3048 x_msg_count => l_msg_count,
3049 x_msg_data => l_msg_data,
3050
3051 p_user_tz_id => l_timezone,
3052 p_in_time => l_start_time,
3053 p_convert_type => 'SYS',
3054
3055 x_out_time => l_sys_start_time
3056 );
3057
3058 -- If any errors happen let start time be sysdate
3059 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3060 l_start_time := SYSDATE;
3061 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3062 l_start_time := SYSDATE;
3063 END IF;
3064
3065 AMS_Utility_PVT.debug_message('Raise Business event for Repeating Schedule');
3066 WF_EVENT.Raise
3067 ( p_event_name => 'oracle.apps.ams.campaign.RepeatScheduleEvent',
3068 p_event_key => l_new_item_key,
3069 p_parameters => l_parameter_list,
3070 p_send_date => l_sys_start_time);
3071
3072 ELSE -- not repeating csch parent instance
3073 l_new_item_key := p_schedule_id || TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
3074 l_parameter_list := WF_PARAMETER_LIST_T();
3075
3076 wf_event.AddParameterToList(p_name => 'SCHEDULE_ID',
3077 p_value => p_schedule_id,
3078 p_parameterlist => l_parameter_list);
3079 --ANCHAUDH starts modification for the leads bug.
3080 IF ((p_asn_group_id IS NOT NULL) AND (p_asn_group_id <> FND_API.g_miss_char)) THEN
3081 p_num_asn_group_id := to_number(p_asn_group_id);
3082 --insert_log_mesg('Anirban passing value of the param in WF, in amsvsbrb.pls as :'||p_num_asn_group_id);
3083
3084 wf_event.AddParameterToList(p_name => 'ASN_GROUP_ID',
3085 p_value => p_num_asn_group_id,
3086 p_parameterlist => l_parameter_list);
3087 ELSE
3088 p_num_asn_group_id := to_number('9999');
3089 wf_event.AddParameterToList(p_name => 'ASN_GROUP_ID',
3090 p_value => p_num_asn_group_id,
3091 p_parameterlist => l_parameter_list);
3092
3093 --insert_log_mesg('Anirban passing value of the param in WF, in amsvsbrb.pls as NULL for ASN_GROUP_ID :'||p_num_asn_group_id);
3094
3095 END IF;
3096
3097
3098 l_RESOURCE_id := AMS_Utility_PVT.get_resource_id(FND_GLOBAL.user_id);
3099 --insert_log_mesg('Anirban passing value of l_RESOURCE_id in WF, in amsvsbrb.pls as :'||l_RESOURCE_id);
3100 wf_event.AddParameterToList(p_name => 'ASN_RESOURCE_ID',
3101 p_value => l_RESOURCE_id,
3102 p_parameterlist => l_parameter_list);
3103
3104 --ANCHAUDH starts modification for the leads bug.
3105
3106 AMS_UTILITY_PVT.Convert_Timezone(
3107 p_init_msg_list => FND_API.G_TRUE,
3108 x_return_status => l_return_status,
3109 x_msg_count => l_msg_count,
3110 x_msg_data => l_msg_data,
3111
3112 p_user_tz_id => l_timezone,
3113 p_in_time => l_start_time,
3114 p_convert_type => 'SYS',
3115
3116 x_out_time => l_sys_start_time
3117 );
3118
3119 -- If any errors happen let start time be sysdate
3120 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3121 l_sys_start_time := SYSDATE;
3122 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3123 l_sys_start_time := SYSDATE;
3124 END IF;
3125
3126 AMS_Utility_PVT.Create_Log (
3127 x_return_status => l_return_status,
3128 p_arc_log_used_by => 'CSCH',
3129 p_log_used_by_id => p_schedule_id,
3130 p_msg_data => 'Before Raise : started with : '||TO_CHAR(l_user_id)||' '||TO_CHAR(l_resp_id)||' '||TO_CHAR(l_resp_appl_id),
3131 p_msg_type => 'DEBUG'
3132 );
3133
3134 AMS_Utility_PVT.debug_message('Raise Business event for schedule execution');
3135 WF_EVENT.Raise
3136 ( p_event_name => 'oracle.apps.ams.campaign.ExecuteSchedule',
3137 p_event_key => l_new_item_key,
3138 p_parameters => l_parameter_list,
3139 p_send_date => l_sys_start_time);
3140 END IF; -- repeating parent instance check
3141 END IF; -- not triggerable
3142
3143 UPDATE ams_campaign_schedules_b
3144 SET workflow_item_key = l_new_item_key
3145 WHERE schedule_id = p_schedule_id ;
3146
3147 END IF;
3148 ELSIF (p_new_status_code = 'COMPLETED' AND l_activity_type_code = 'EVENTS')
3149 THEN
3150 IF l_def_flag = 'Y' THEN
3151 AMS_EvhRules_PVT.process_leads(p_event_id => l_related_event_id,
3152 p_obj_type => 'CSCH',
3153 p_obj_srccd => l_source_code);
3154 END IF;
3155 END IF;
3156
3157 IF l_activity_type_code = 'EVENTS'
3158 THEN
3159 l_new_status_id := AMS_Utility_PVT.get_default_user_status('AMS_EVENT_STATUS',p_new_status_code);
3160
3161 --Added by ANSKUMAR for Fulfilment
3162
3163 IF p_new_status_code='ACTIVE' OR p_new_status_code='CANCELLED'
3164 THEN
3165 l_evo_rec.event_offer_id := l_related_event_id;
3166 l_evo_rec.event_object_type := 'EONE';
3167 l_evo_rec.user_status_id := l_new_status_id;
3168 l_evo_rec.system_status_code := p_new_status_code;
3169 --l_evo_rec.last_status_date := SYSDATE;
3170 --l_evo_rec.owner_user_id := l_owner_user_id;
3171 --l_evo_rec.application_id := 530;
3172 --l_evo_rec.event_level := 'MAIN';
3173 --l_evo_rec.parent_type := 'CAMP';
3174 --l_evo_rec.parent_id := l_campaign_id;
3175 --l_evo_rec.custom_setup_id := 3000;
3176
3177 AMS_EventOffer_PVT.fulfill_event_offer(p_evo_rec => l_evo_rec,
3178 x_return_status => l_return_status);
3179
3180 END IF;
3181 -- Not handling return_stauts here
3182
3183 UPDATE ams_event_offers_all_b
3184 SET user_status_id = l_new_status_id,
3185 system_status_code = p_new_status_code,
3186 last_status_date = SYSDATE
3187 WHERE event_offer_id = l_related_event_id;
3188
3189
3190 END IF;
3191
3192 END update_status;
3193
3194
3195
3196
3197 --=====================================================================
3198 -- PROCEDURE
3199 -- Update_Schedule_Owner
3200 --
3201 -- PURPOSE
3202 -- The api is created to update the owner of the schedule from the
3203 -- access table if the owner is changed in update.
3204 --
3205 -- Algorithm:
3206 -- 1. Call update_object_owner from access_pvt
3207 -- 2. Add access from campaign to schedules
3208 --
3209 -- HISTORY
3210 -- 06-Jun-2002 soagrawa Created. Refer to bug# 2406677
3211 -- 18-jun-2002 soagrawa Fixed bug# 2421601
3212 --=====================================================================
3213 PROCEDURE Update_Schedule_Owner(
3214 p_api_version IN NUMBER,
3215 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
3216 p_commit IN VARCHAR2 := FND_API.g_false,
3217 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
3218 x_return_status OUT NOCOPY VARCHAR2,
3219 x_msg_count OUT NOCOPY NUMBER,
3220 x_msg_data OUT NOCOPY VARCHAR2,
3221 p_object_type IN VARCHAR2 := NULL ,
3222 p_schedule_id IN NUMBER,
3223 p_owner_id IN NUMBER )
3224 IS
3225
3226 CURSOR c_owner IS
3227 SELECT owner_user_id , campaign_id
3228 FROM ams_campaign_schedules_vl
3229 WHERE schedule_id = p_schedule_id ;
3230
3231 CURSOR c_access_csch_det(p_owner NUMBER) IS
3232 SELECT *
3233 FROM ams_act_access
3234 WHERE arc_act_access_to_object = 'CSCH'
3235 AND user_or_role_id = p_owner
3236 AND arc_user_or_role_type = 'USER'
3237 AND act_access_to_object_id = p_schedule_id;
3238
3239 CURSOR c_access_camp_det(p_campaign_id NUMBER) IS
3240 SELECT *
3241 FROM ams_act_access
3242 WHERE arc_act_access_to_object = 'CAMP'
3243 -- AND user_or_role_id = p_owner_id
3244 AND arc_user_or_role_type = 'USER'
3245 AND act_access_to_object_id = p_campaign_id;
3246
3247
3248 l_access_csch_rec c_access_csch_det%ROWTYPE;
3249 l_access_camp_rec c_access_camp_det%ROWTYPE;
3250
3251 l_access_rec AMS_Access_Pvt.access_rec_type ;
3252
3253 l_old_owner NUMBER ;
3254 l_campaign_id NUMBER ;
3255
3256 l_dummy_id NUMBER ;
3257
3258
3259 BEGIN
3260 -- the following 2 lines added by soagrawa on 18-jun-2002
3261 -- for bug# 2421601
3262 IF (AMS_DEBUG_HIGH_ON) THEN
3263
3264 AMS_Utility_PVT.debug_message('Update schedule owner ');
3265 END IF;
3266 x_return_status := FND_API.g_ret_sts_success;
3267
3268 OPEN c_owner ;
3269 FETCH c_owner INTO l_old_owner, l_campaign_id ;
3270 IF c_owner%NOTFOUND THEN
3271 CLOSE c_owner;
3272 AMS_Utility_Pvt.Error_Message('AMS_API_RECORD_NOT_FOUND');
3273 RAISE FND_API.g_exc_error;
3274 END IF;
3275 CLOSE c_owner ;
3276
3277 IF p_owner_id <> l_old_owner THEN
3278
3279 -- call update_owner_object
3280 AMS_Access_PVT.update_object_owner(
3281 p_api_version => p_api_version,
3282 p_init_msg_list => p_init_msg_list,
3283 p_commit => p_commit,
3284 p_validation_level => p_validation_level,
3285 x_return_status => x_return_status,
3286 x_msg_count => x_msg_count,
3287 x_msg_data => x_msg_data,
3288 p_object_type => nvl(p_object_type,'CSCH'),
3289 p_object_id => p_schedule_id,
3290 p_resource_id => p_owner_id,
3291 p_old_resource_id => l_old_owner
3292 );
3293
3294 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3295 RAISE FND_API.G_EXC_ERROR;
3296 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3297 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3298 END IF;
3299
3300 -- get all the access list ppl of campaign
3301 -- check if they are not in the access list of the schedule
3302 -- if they are do nothing
3303 -- if they are not add them
3304
3305
3306 OPEN c_access_camp_det(l_campaign_id) ;
3307 LOOP
3308 FETCH c_access_camp_det INTO l_access_camp_rec;
3309 EXIT WHEN c_access_camp_det%NOTFOUND ;
3310
3311 OPEN c_access_csch_det(l_access_camp_rec.user_or_role_id);
3312 FETCH c_access_csch_det INTO l_access_csch_rec;
3313 IF c_access_csch_det%NOTFOUND THEN
3314
3315 -- Create Access
3316 l_access_rec.act_access_to_object_id := p_schedule_id ;
3317 l_access_rec.arc_act_access_to_object := 'CSCH' ;
3318 l_access_rec.owner_flag := 'N' ;
3319 l_access_rec.user_or_role_id := l_access_camp_rec.user_or_role_id ;
3320 l_access_rec.arc_user_or_role_type := l_access_camp_rec.arc_user_or_role_type ;
3321 l_access_rec.delete_flag := l_access_camp_rec.delete_flag ;
3322 l_access_rec.admin_flag := l_access_camp_rec.admin_flag ;
3323
3324 AMS_Access_Pvt.Create_Access(
3325 p_api_version => p_api_version,
3326 p_init_msg_list => p_init_msg_list,
3327 p_commit => p_commit,
3328 p_validation_level => p_validation_level,
3329
3330 x_return_status => x_return_status,
3331 x_msg_count => x_msg_count,
3332 x_msg_data => x_msg_data,
3333
3334 p_access_rec => l_access_rec,
3335 x_access_id => l_dummy_id
3336 );
3337 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3338 CLOSE c_access_csch_det;
3339 CLOSE c_access_camp_det;
3340 RAISE FND_API.G_EXC_ERROR;
3341 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3342 CLOSE c_access_csch_det;
3343 CLOSE c_access_camp_det;
3344 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3345 END IF;
3346
3347
3348 ELSE
3349 -- do nothing
3350 NULL;
3351 END IF;
3352 CLOSE c_access_csch_det;
3353
3354 END LOOP;
3355 CLOSE c_access_camp_det ;
3356
3357 END IF ;
3358
3359 END Update_Schedule_Owner ;
3360
3361
3362
3363
3364
3365 -- Start of Comments
3366 --
3367 -- NAME
3368 -- Handle_Error
3369 --
3370 -- PURPOSE
3371 -- This Procedure will Get all the Errors from the Message stack and
3372 -- Set the Workflow item attribut with the Error Messages
3373 --
3374 -- Used By Activities
3375 --
3376 --
3377 -- NOTES
3378 --
3379 -- HISTORY
3380 -- 03-Sep-2003 ptendulk created
3381 -- 14-Oct-2003 dbiswas added wf_attrib to signature
3382 -- End of Comments
3383 PROCEDURE Handle_Error
3384 (p_itemtype IN VARCHAR2 ,
3385 p_itemkey IN VARCHAR2 ,
3386 p_msg_count IN NUMBER , -- Number of error Messages
3387 p_msg_data IN VARCHAR2 ,
3388 p_wf_err_attrib IN VARCHAR2 := 'ERROR_MSG'
3389 )
3390 IS
3391 l_msg_count NUMBER ;
3392 l_msg_data VARCHAR2(2000);
3393 l_final_data VARCHAR2(4000);
3394 l_msg_index NUMBER ;
3395 l_cnt NUMBER := 0 ;
3396 l_return_status VARCHAR2(1);
3397 l_schedule_id NUMBER ;
3398 BEGIN
3399
3400 l_schedule_id := WF_ENGINE.GetItemAttrText(
3401 itemtype => p_itemtype,
3402 itemkey => p_itemkey ,
3403 aname => 'SCHEDULE_ID');
3404
3405 AMS_Utility_PVT.Create_Log (
3406 x_return_status => l_return_status,
3407 p_arc_log_used_by => 'CSCH',
3408 p_log_used_by_id => l_schedule_id,
3409 p_msg_data => 'Error Message handling',
3410 p_msg_type => 'DEBUG'
3411 );
3412
3413 WHILE l_cnt < p_msg_count
3414 LOOP
3415 FND_MSG_PUB.Get(p_msg_index => l_cnt + 1,
3416 p_encoded => FND_API.G_FALSE,
3417 p_data => l_msg_data,
3418 p_msg_index_out => l_msg_index ) ;
3419 l_final_data := l_final_data ||l_msg_index||': '||l_msg_data||fnd_global.local_chr(10);
3420 l_cnt := l_cnt + 1 ;
3421
3422 END LOOP ;
3423
3424 WF_ENGINE.SetItemAttrText(itemtype => p_itemtype,
3425 itemkey => p_itemkey ,
3426 aname => 'ERROR_MESSAGE',
3427 avalue => l_final_data );
3428
3429 END Handle_Error;
3430
3431
3432
3433 --=====================================================================
3434 -- PROCEDURE
3435 -- Init_Schedule_val
3436 --
3437 -- PURPOSE
3438 -- This api will be used by schedule execution workflow to initialize the schedule
3439 -- parameter values.
3440 --
3441 -- HISTORY
3442 -- 23-Aug-2003 ptendulk Created.
3443 -- 19-Sep-2003 dbiswas Update out to out nocopy
3444 -- 09-nov-2004 anchaudh Now setting item owner along with bug fix for bug# 3799053
3445 --=====================================================================
3446 PROCEDURE Init_Schedule_val(itemtype IN VARCHAR2,
3447 itemkey IN VARCHAR2,
3448 actid IN NUMBER,
3449 funcmode IN VARCHAR2,
3450 result OUT NOCOPY VARCHAR2) IS
3451 l_schedule_id NUMBER;
3452
3453 CURSOR c_schedule_det(l_csch_id NUMBER) IS
3454 SELECT schedule_name, status_code,owner_user_id,
3455 DECODE(activity_type_code,'DIRECT_SALES','SALES','DIRECT_MARKETING','DIRECT_MARKETING','OTHERS') activity_type,
3456 activity_id, start_date_time, end_date_time
3457 FROM ams_campaign_schedules_vl
3458 WHERE schedule_id = l_csch_id ;
3459 l_schedule_rec c_schedule_det%ROWTYPE;
3460
3461 CURSOR c_emp_dtl(l_res_id IN NUMBER) IS
3462 SELECT employee_id
3463 FROM ams_jtf_rs_emp_v
3464 WHERE resource_id = l_res_id ;
3465 l_emp_id NUMBER;
3466 l_user_name VARCHAR2(100);
3467 l_display_name VARCHAR2(100);
3468 l_return_status VARCHAR2(1);
3469 l_user_id NUMBER;
3470 l_resp_id NUMBER;
3471 l_resp_appl_id NUMBER;
3472
3473 BEGIN
3474 IF (funcmode = 'RUN')
3475 THEN
3476
3477 l_schedule_id := WF_ENGINE.GetItemAttrText(
3478 itemtype => itemtype,
3479 itemkey => itemkey ,
3480 aname => 'SCHEDULE_ID');
3481
3482 l_user_id := FND_GLOBAL.USER_ID;
3483 l_resp_id := FND_GLOBAL.RESP_ID;
3484 l_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
3485
3486 AMS_Utility_PVT.Create_Log (
3487 x_return_status => l_return_status,
3488 p_arc_log_used_by => 'CSCH',
3489 p_log_used_by_id => l_schedule_id,
3490 p_msg_data => 'Init_Schedule_val : started with '||TO_CHAR(l_user_id)||' '||TO_CHAR(l_resp_id)||' '||TO_CHAR(l_resp_appl_id),
3491 p_msg_type => 'DEBUG'
3492 );
3493
3494 OPEN c_schedule_det(l_schedule_id);
3495 FETCH c_schedule_det INTO l_schedule_rec ;
3496 CLOSE c_schedule_det;
3497
3498 OPEN c_emp_dtl(l_schedule_rec.owner_user_id);
3499 FETCH c_emp_dtl INTO l_emp_id;
3500 -- soagrawa setting item owner along with bug fix for bug# 3799053
3501 IF c_emp_dtl%FOUND
3502 THEN
3503 WF_DIRECTORY.getrolename
3504 ( p_orig_system => 'PER',
3505 p_orig_system_id => l_emp_id ,
3506 p_name => l_user_name,
3507 p_display_name => l_display_name );
3508
3509 IF l_user_name IS NOT NULL THEN
3510 Wf_Engine.SetItemOwner(itemtype => itemtype,
3511 itemkey => itemkey,
3512 owner => l_user_name);
3513 END IF;
3514 END IF;
3515 CLOSE c_emp_dtl;
3516
3517 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3518 itemkey => itemkey,
3519 aname => 'SCHEDULE_NAME',
3520 avalue => l_schedule_rec.schedule_name);
3521
3522 WF_ENGINE.SetItemUserkey(itemtype => itemtype,
3523 itemkey => itemkey ,
3524 userkey => l_schedule_rec.schedule_name);
3525
3526 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3527 itemkey => itemkey,
3528 aname => 'SCHEDULE_OWNER',
3529 avalue => l_user_name);
3530
3531 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3532 itemkey => itemkey,
3533 aname => 'WF_ADMINISTRATOR',
3534 avalue => l_user_name);
3535
3536 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3537 itemkey => itemkey,
3538 aname => 'SCHEDULE_STATUS',
3539 avalue => l_schedule_rec.status_code);
3540
3541 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3542 itemkey => itemkey,
3543 aname => 'SCHEDULE_CHANNEL',
3544 avalue => l_schedule_rec.activity_id );
3545
3546 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3547 itemkey => itemkey,
3548 aname => 'ACTIVITY_TYPE',
3549 avalue => l_schedule_rec.activity_type );
3550
3551 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3552 itemkey => itemkey,
3553 aname => 'ERROR_FLAG',
3554 avalue => 'N');
3555 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3556 itemkey => itemkey,
3557 aname => 'AMS_SCHEDULE_START_DATE',
3558 avalue => l_schedule_rec.start_date_time );
3559
3560 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3561 itemkey => itemkey,
3562 aname => 'AMS_SCHEDULE_END_DATE',
3563 avalue => l_schedule_rec.end_date_time );
3564
3565 END IF;
3566
3567 -- CANCEL mode - Normal Process Execution
3568 IF (funcmode = 'CANCEL')
3569 THEN
3570 RETURN;
3571 END IF;
3572
3573 -- TIMEOUT mode - Normal Process Execution
3574 IF (funcmode = 'TIMEOUT')
3575 THEN
3576 RETURN;
3577 END IF;
3578 -- dbms_output.put_line('End Check Trigger stat :'||result);
3579
3580 EXCEPTION
3581 WHEN OTHERS THEN
3582 wf_core.context(G_PKG_NAME,'Init_Schedule_val',itemtype,itemkey,actid,funcmode);
3583 RAISE ;
3584 END ;
3585
3586 /* Commented for sql rep 14423973. Bug 4956974
3587 PROCEDURE AMS_SELECTOR
3588 ( p_itemtype in varchar2
3589 , p_itemkey in varchar2
3590 , p_actid in number
3591 , p_funcmode in varchar2
3592 , p_result in out nocopy varchar2)
3593 IS
3594 l_user_id NUMBER;
3595 l_resp_id NUMBER;
3596 l_resp_appl_id NUMBER;
3597 l_return_status VARCHAR2(1);
3598 l_schedule_id NUMBER;
3599
3600 CURSOR c_schedule_creator_id (p_schedule_id IN NUMBER) IS
3601 select created_by
3602 from ams_campaign_schedules_b
3603 where schedule_id = p_schedule_id;
3604
3605 CURSOR c_user_resp_dtl(p_user_id IN NUMBER) IS
3606 SELECT responsibility_id
3607 FROM fnd_user_resp_groups
3608 WHERE responsibility_application_id = 530
3609 and user_id = p_user_id
3610 and rownum < 2;
3611
3612 BEGIN
3613 IF (p_funcmode = 'RUN') THEN
3614 -- Code that determines Start Process
3615 p_result := 'COMPLETE';
3616 ELSIF (p_funcmode = 'TEST_CTX') THEN
3617 -- Code that compares current session context
3618 -- with the work item context required to execute
3619 -- the workflow safely
3620 l_user_id := FND_GLOBAL.USER_ID;
3621 l_resp_id := FND_GLOBAL.RESP_ID;
3622 l_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
3623
3624 l_schedule_id := WF_ENGINE.GetItemAttrText(
3625 itemtype => p_itemtype,
3626 itemkey => p_itemkey ,
3627 aname => 'SCHEDULE_ID');
3628
3629 AMS_Utility_PVT.Create_Log (
3630 x_return_status => l_return_status,
3631 p_arc_log_used_by => 'CSCH',
3632 p_log_used_by_id => l_schedule_id,
3633 p_msg_data => 'Ams_Selector TEST_CTX : started with : '||TO_CHAR(l_user_id)||' '||TO_CHAR(l_resp_id)||' '||TO_CHAR(l_resp_appl_id),
3634 p_msg_type => 'DEBUG'
3635 );
3636
3637 if l_user_id < 0 then
3638 -- If the background engine is executing the
3639 -- Selector/Callback function, the workflow engine
3640 -- Will immediately run the Selector/Callback
3641 -- Function in SET_CTX mode
3642 OPEN c_schedule_creator_id(l_schedule_id);
3643 FETCH c_schedule_creator_id INTO l_user_id;
3644 CLOSE c_schedule_creator_id;
3645
3646 OPEN c_user_resp_dtl(l_user_id);
3647 FETCH c_user_resp_dtl INTO l_resp_id;
3648 CLOSE c_user_resp_dtl;
3649
3650 l_resp_appl_id := 530;
3651
3652 AMS_Utility_PVT.Create_Log (
3653 x_return_status => l_return_status,
3654 p_arc_log_used_by => 'CSCH',
3655 p_log_used_by_id => l_schedule_id,
3656 p_msg_data => 'Ams_Selector TEST_CTX : setting the apps ctx to : '||TO_CHAR(l_user_id)||' '||TO_CHAR(l_resp_id)||' '||TO_CHAR(l_resp_appl_id),
3657 p_msg_type => 'DEBUG'
3658 );
3659
3660 -- Set the database session context
3661 FND_GLOBAL.Apps_Initialize(l_user_id, l_resp_id, l_resp_appl_id);
3662 p_result := 'COMPLETE:FALSE';
3663 else
3664 p_result := 'COMPLETE:TRUE';
3665 end if;
3666 ELSIF(p_funcmode = 'SET_CTX') THEN
3667 -- Code that sets the current session context
3668 -- based on the work item context stored in item attributes
3669 -- get Item Attributes for user_id, responsibility_id and application_id
3670 -- this assumes that they were set as item attribute, probably through
3671 -- definition.
3672 l_schedule_id := WF_ENGINE.GetItemAttrText(
3673 itemtype => p_itemtype,
3674 itemkey => p_itemkey ,
3675 aname => 'SCHEDULE_ID');
3676
3677 OPEN c_schedule_creator_id(l_schedule_id);
3678 FETCH c_schedule_creator_id INTO l_user_id;
3679 CLOSE c_schedule_creator_id;
3680
3681 OPEN c_user_resp_dtl(l_user_id);
3682 FETCH c_user_resp_dtl INTO l_resp_id;
3683 CLOSE c_user_resp_dtl;
3684
3685 l_resp_appl_id := 530;
3686
3687 -- Set the database session context which also sets the org
3688 --FND_GLOBAL.Apps_Initialize(l_user_id, l_resp_id, l_resp_appl_id);
3689 AMS_Utility_PVT.Create_Log (
3690 x_return_status => l_return_status,
3691 p_arc_log_used_by => 'CSCH',
3692 p_log_used_by_id => l_schedule_id,
3693 p_msg_data => 'Ams_Selector SET_CTX : setting the apps ctx to : '||TO_CHAR(l_user_id)||' '||TO_CHAR(l_resp_id)||' '||TO_CHAR(l_resp_appl_id),
3694 p_msg_type => 'DEBUG'
3695 );
3696
3697 p_result := 'COMPLETE';
3698 ELSE
3699 p_result := 'COMPLETE';
3700 END IF;
3701 EXCEPTION
3702 WHEN OTHERS THEN NULL;
3703 WF_CORE.Context('PROD_STANDARD_WF', 'AMS_SELECTOR', p_itemtype, p_itemkey, p_actid, p_funcmode);
3704 RAISE;
3705 END AMS_SELECTOR;
3706 */
3707
3708 --=====================================================================
3709 -- PROCEDURE
3710 -- Check_Schedule_Status
3711 --
3712 -- PURPOSE
3713 -- This api will be used by schedule execution workflow to check schedule status
3714 -- The schedule can be in available or active status. if the schedule is available
3715 -- workflow will update the status to active.
3716 --
3717 -- HISTORY
3718 -- 23-Aug-2003 ptendulk Created.
3719 -- 19-Sep-2003 dbiswas Added nocopy
3720 --=====================================================================
3721 PROCEDURE Check_Schedule_Status(itemtype IN VARCHAR2,
3722 itemkey IN VARCHAR2,
3723 actid IN NUMBER,
3724 funcmode IN VARCHAR2,
3725 result OUT NOCOPY VARCHAR2) IS
3726 l_schedule_status VARCHAR2(30) ;
3727 l_return_status VARCHAR2(1);
3728 l_schedule_id NUMBER;
3729 BEGIN
3730 -- dbms_output.put_line('Process Check_Repeat');
3731 -- RUN mode - Normal Process Execution
3732 IF (funcmode = 'RUN')
3733 THEN
3734 l_schedule_id := WF_ENGINE.GetItemAttrText(
3735 itemtype => itemtype,
3736 itemkey => itemkey ,
3737 aname => 'SCHEDULE_ID' );
3738
3739 AMS_Utility_PVT.Create_Log (
3740 x_return_status => l_return_status,
3741 p_arc_log_used_by => 'CSCH',
3742 p_log_used_by_id => l_schedule_id,
3743 p_msg_data => 'Check_Schedule_Status : started',
3744 p_msg_type => 'DEBUG'
3745 );
3746
3747 l_schedule_status := WF_ENGINE.GetItemAttrText(
3748 itemtype => itemtype,
3749 itemkey => itemkey ,
3750 aname => 'SCHEDULE_STATUS' );
3751
3752
3753 -- make sure that last activation date is updated
3754 UPDATE ams_campaign_schedules_b
3755 SET last_activation_date = SYSDATE,
3756 object_version_number = object_version_number + 1,
3757 last_update_date = SYSDATE,
3758 last_updated_by = FND_GLOBAL.user_id
3759 WHERE schedule_id = l_schedule_id ;
3760
3761 IF l_schedule_status = 'ACTIVE' THEN
3762 result := 'COMPLETE:ACTIVE' ;
3763 ELSE
3764 result := 'COMPLETE:AVAILABLE' ;
3765 END IF ;
3766 END IF;
3767
3768 -- CANCEL mode - Normal Process Execution
3769 IF (funcmode = 'CANCEL')
3770 THEN
3771 result := 'COMPLETE:' ;
3772 RETURN;
3773 END IF;
3774
3775 -- TIMEOUT mode - Normal Process Execution
3776 IF (funcmode = 'TIMEOUT')
3777 THEN
3778 result := 'COMPLETE:' ;
3779 RETURN;
3780 END IF;
3781 EXCEPTION
3782 WHEN OTHERS THEN
3783 wf_core.context(G_PKG_NAME,'Check_Schedule_Status',itemtype,itemkey,actid,funcmode);
3784 raise ;
3785 END Check_Schedule_Status ;
3786
3787
3788 --=========================================================================================================
3789 -- PROCEDURE
3790 -- Update_Schedule_Status
3791 --
3792 -- PURPOSE
3793 -- This api will be used by schedule execution workflow to update schedule status
3794 -- It will update the schedule status to Active.
3795 --
3796 -- HISTORY
3797 -- 23-Aug-2003 ptendulk Created.
3798 -- 19-Sep-2003 dbiswas Added nocopy
3799 -- 17-Mar-2005 spendem call the API to raise business event on status change as per enh # 3805347
3800 --===========================================================================================================
3801 PROCEDURE Update_Schedule_Status(itemtype IN VARCHAR2,
3802 itemkey IN VARCHAR2,
3803 actid IN NUMBER,
3804 funcmode IN VARCHAR2,
3805 result OUT NOCOPY VARCHAR2) IS
3806
3807 -- declare cursor as per enh # 3805347
3808 -- dkailash related_event_id added for bug #11830352
3809 CURSOR c_csch_det(p_schedule_id IN NUMBER) IS
3810 SELECT status_code,related_event_id,activity_type_code
3811 FROM ams_campaign_schedules_b
3812 WHERE schedule_id = p_schedule_id;
3813
3814 l_schedule_id NUMBER;
3815 l_user_status_id NUMBER ;
3816 l_return_status VARCHAR2(1);
3817 l_old_status_code VARCHAR2(30); -- added as per enh # 3805347.
3818 l_related_event_id Number ; --dkailash l_related_event_id added for bug #11830352
3819 l_event_status_id NUMBER ;
3820 l_activity_type_code VARCHAR2(30);
3821 BEGIN
3822 -- dbms_output.put_line('Process Check_Repeat');
3823 -- RUN mode - Normal Process Execution
3824 IF (funcmode = 'RUN')
3825 THEN
3826 l_schedule_id := WF_ENGINE.GetItemAttrText(
3827 itemtype => itemtype,
3828 itemkey => itemkey ,
3829 aname => 'SCHEDULE_ID' );
3830
3831 AMS_Utility_PVT.Create_Log (
3832 x_return_status => l_return_status,
3833 p_arc_log_used_by => 'CSCH',
3834 p_log_used_by_id => l_schedule_id,
3835 p_msg_data => 'Update_Schedule_Status : started',
3836 p_msg_type => 'DEBUG'
3837 );
3838
3839
3840 -- open cursor here for enh # 3805347
3841 --dkailash l_related_event_id added for bug #11830352
3842 OPEN c_csch_det(l_schedule_id);
3843 FETCH c_csch_det INTO l_old_status_code,l_related_event_id,l_activity_type_code;
3844 CLOSE c_csch_det;
3845
3846 l_user_status_id := AMS_Utility_PVT.get_default_user_status('AMS_CAMPAIGN_SCHEDULE_STATUS','ACTIVE') ;
3847
3848 UPDATE ams_campaign_schedules_b
3849 SET status_code = 'ACTIVE',
3850 user_status_id = l_user_status_id,
3851 status_date = SYSDATE,
3852 last_activation_date = SYSDATE,
3853 object_version_number = object_version_number + 1,
3854 last_update_date = SYSDATE,
3855 last_updated_by = FND_GLOBAL.user_id
3856 WHERE schedule_id = l_schedule_id ;
3857 --dkailash update AMS_EVENT_OFFERS_ALL_B table for l_activity_type_code ='EVENTS' added for bug #11830352
3858 IF (l_related_event_id IS NOT NULL and l_activity_type_code ='EVENTS')
3859 THEN
3860 l_event_status_id := AMS_Utility_PVT.get_default_user_status('AMS_EVENT_STATUS','ACTIVE');
3861
3862 UPDATE AMS_EVENT_OFFERS_ALL_B
3863 SET SYSTEM_STATUS_CODE = 'ACTIVE',
3864 USER_STATUS_ID=l_event_status_id,
3865 LAST_STATUS_DATE=SYSDATE,
3866 OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1,
3867 LAST_UPDATE_DATE=SYSDATE,
3868 LAST_UPDATED_BY=FND_GLOBAL.user_id
3869 WHERE EVENT_OFFER_ID = l_related_event_id;
3870 END IF;
3871
3872 -- call to api to raise business event, as per enh # 3805347
3873 RAISE_BE_ON_STATUS_CHANGE(p_obj_id => l_schedule_id,
3874 p_obj_type => 'CSCH',
3875 p_old_status_code => l_old_status_code,
3876 p_new_status_code => 'ACTIVE' );
3877
3878 END IF;
3879
3880 -- CANCEL mode - Normal Process Execution
3881 IF (funcmode = 'CANCEL')
3882 THEN
3883 RETURN;
3884 END IF;
3885
3886 -- TIMEOUT mode - Normal Process Execution
3887 IF (funcmode = 'TIMEOUT')
3888 THEN
3889 RETURN;
3890 END IF;
3891 -- dbms_output.put_line('End Check Trigger stat :'||result);
3892 EXCEPTION
3893 WHEN OTHERS THEN
3894 wf_core.context(G_PKG_NAME,'Update_Schedule_Status',itemtype,itemkey,actid,funcmode);
3895 RAISE ;
3896 END Update_Schedule_Status ;
3897
3898
3899 --=====================================================================
3900 -- PROCEDURE
3901 -- Check_Schedule_Act_Type
3902 --
3903 -- PURPOSE
3904 -- This api will be used by schedule execution workflow to check schedule activity
3905 -- Based on the activity type different apis will be called.
3906 --
3907 -- HISTORY
3908 -- 23-Aug-2003 ptendulk Created.
3909 -- 19-Sep-2003 dbiswas Added nocopy
3910 --=====================================================================
3911 PROCEDURE Check_Schedule_Act_Type(itemtype IN VARCHAR2,
3912 itemkey IN VARCHAR2,
3913 actid IN NUMBER,
3914 funcmode IN VARCHAR2,
3915 result OUT NOCOPY VARCHAR2) IS
3916 l_schedule_activity VARCHAR2(30) ;
3917 l_return_status VARCHAR2(1);
3918 l_schedule_id NUMBER;
3919 BEGIN
3920 -- dbms_output.put_line('Process Check_Repeat');
3921 -- RUN mode - Normal Process Execution
3922 IF (funcmode = 'RUN')
3923 THEN
3924 l_schedule_id := WF_ENGINE.GetItemAttrText(
3925 itemtype => itemtype,
3926 itemkey => itemkey ,
3927 aname => 'SCHEDULE_ID' );
3928
3929 AMS_Utility_PVT.Create_Log (
3930 x_return_status => l_return_status,
3931 p_arc_log_used_by => 'CSCH',
3932 p_log_used_by_id => l_schedule_id,
3933 p_msg_data => 'Check_Schedule_Act_Type : started',
3934 p_msg_type => 'DEBUG'
3935 );
3936
3937 l_schedule_activity := WF_ENGINE.GetItemAttrText(
3938 itemtype => itemtype,
3939 itemkey => itemkey ,
3940 aname => 'ACTIVITY_TYPE' );
3941
3942
3943 result := 'COMPLETE:'||l_schedule_activity ;
3944
3945 END IF;
3946
3947 -- CANCEL mode - Normal Process Execution
3948 IF (funcmode = 'CANCEL')
3949 THEN
3950 result := 'COMPLETE:' ;
3951 RETURN;
3952 END IF;
3953
3954 -- TIMEOUT mode - Normal Process Execution
3955 IF (funcmode = 'TIMEOUT')
3956 THEN
3957 result := 'COMPLETE:' ;
3958 RETURN;
3959 END IF;
3960 EXCEPTION
3961 WHEN OTHERS THEN
3962 wf_core.context(G_PKG_NAME,'Check_Schedule_Act_Type',itemtype,itemkey,actid,funcmode);
3963 raise ;
3964 END Check_Schedule_Act_Type ;
3965
3966 --=====================================================================
3967 -- PROCEDURE
3968 -- Execute_Direct_Marketing
3969 --
3970 -- PURPOSE
3971 -- This api will be used by schedule execution workflow to execute schedule
3972 -- of type Direct Marketing
3973 --
3974 -- ALGORITHM
3975 -- 1. Does target group exist?
3976 -- Yes => 1.1 Increase usage
3977 -- 1.2 Is channel Email, Print, Fax
3978 -- Yes => 1.2.1 Increase contacted count
3979 -- 1.2.2 Stamp version in ibc_associations table
3980 -- 1.2.3 Send Fulfillment Request
3981 -- 1.2.4 Update list sent out date
3982 --
3983 -- Any error in any of the API callouts?
3984 -- => a) Set attribute ERROR_FLAG to Y
3985 -- b) Call Handle_err to set error msg values
3986 -- c) Return
3987 --
3988 -- OPEN ISSUES
3989 -- 1. Use Enable Fulfillment profile before fulilling?
3990 -- 2. If not enabled => write interaction or not?
3991 --
3992 -- HISTORY
3993 -- 23-Aug-2003 ptendulk Created.
3994 -- 19-Sep-2003 dbiswas Added nocopy
3995 -- 29-sep-2003 soagrawa Modified to clean up the code and removed interaction for direct mail channel
3996 -- 29-sep-2003 soagrawa Modified to clean up the code and removed interaction for direct mail channel
3997 -- 05-apr-2004 soagrawa added ELSE part for when TGRP does not exist
3998 -- this is needed for automated flows like Repeating Schedules / Triggers
3999 -- pls refer bug# 3553087
4000 -- 29-apr-2004 anchaudh fixed the reopened bug#3553087
4001 -- 09-nov-2004 anchaudh fixed bug# 3799053 about FFM requests being created with random user ids
4002 -- 28-jan-2005 spendem fix for bug # 4145845. Added to_char function to the schedule_id
4003 -- 14-mar-2005 spendem fix for bug # 4184571. Adding a filter for unwanted error.
4004 --=========================================================================================================
4005 PROCEDURE Execute_Direct_Marketing(itemtype IN VARCHAR2,
4006 itemkey IN VARCHAR2,
4007 actid IN NUMBER,
4008 funcmode IN VARCHAR2,
4009 result OUT NOCOPY VARCHAR2) IS
4010
4011 CURSOR c_tgrp_det(l_csch_id IN NUMBER) IS
4012 SELECT list_header_id
4013 FROM ams_act_lists la
4014 WHERE list_act_type = 'TARGET'
4015 AND list_used_by = 'CSCH'
4016 AND list_used_by_id = l_csch_id
4017 AND EXISTS (SELECT *
4018 FROM ams_list_entries le
4019 WHERE le.list_header_id = la.list_header_id) ;
4020
4021 -- soagrawa added the following cursor on 30-sep-2003 for stamping version
4022 CURSOR c_cover_letter_det (l_csch_id IN NUMBER) IS
4023 SELECT assoc.association_id, assoc.content_item_id, ci.live_citem_version_id
4024 FROM ibc_associations assoc, ibc_content_Items ci
4025 WHERE assoc.association_type_code = 'AMS_CSCH'
4026 AND assoc.associated_object_val1 = to_char(l_csch_id) -- fix for bug # 4145845
4027 AND assoc.content_item_id = ci.content_Item_id;
4028
4029 -- anchaudh added the following cursor on 01-nov-2004 for getting csch owner, bug# 3799053
4030 CURSOR c_csch_det (l_csch_id IN NUMBER) IS
4031 SELECT owner_user_id
4032 FROM ams_campaign_schedules_b
4033 WHERE schedule_id = l_csch_id ;
4034
4035 l_csch_owner_user_id NUMBER;
4036 l_schedule_id NUMBER;
4037 l_return_status VARCHAR2(1) := FND_API.g_ret_sts_success ;
4038 l_log_return_status VARCHAR2(1) := FND_API.g_ret_sts_success ;
4039 l_activity_id NUMBER;
4040 l_msg_count NUMBER;
4041 l_msg_data VARCHAR2(2000);
4042 l_list_id NUMBER;
4043 l_request_id NUMBER;
4044 l_association_id NUMBER;
4045 l_cover_letter_id NUMBER;
4046 l_cover_letter_ver_id NUMBER;
4047 l_error_msg VARCHAR2(4000);
4048
4049 BEGIN
4050
4051 -- RUN mode - Normal Process Execution
4052 IF (funcmode = 'RUN')
4053 THEN
4054 -- get schedule id
4055 l_schedule_id := WF_ENGINE.GetItemAttrText(
4056 itemtype => itemtype,
4057 itemkey => itemkey ,
4058 aname => 'SCHEDULE_ID' );
4059
4060 AMS_Utility_PVT.Create_Log (
4061 x_return_status => l_log_return_status,
4062 p_arc_log_used_by => 'CSCH',
4063 p_log_used_by_id => l_schedule_id,
4064 p_msg_data => 'Execute_Direct_Marketing : started for schedule id '||l_schedule_id,
4065 p_msg_type => 'DEBUG'
4066 );
4067
4068
4069 -- get schedule activity
4070 l_activity_id := WF_ENGINE.GetItemAttrText(
4071 itemtype => itemtype,
4072 itemkey => itemkey ,
4073 aname => 'SCHEDULE_CHANNEL' );
4074 --
4075 -- 1. Does target group exist?
4076 --
4077 OPEN c_tgrp_det (l_schedule_id) ;
4078 FETCH c_tgrp_det INTO l_list_id ;
4079 CLOSE c_tgrp_det ;
4080
4081
4082 IF FND_API.G_TRUE = Target_Group_Exist(l_schedule_id)
4083 THEN
4084 --
4085 -- Yes => 1.1 Increase usage
4086 --
4087 AMS_Utility_PVT.Create_Log (
4088 x_return_status => l_log_return_status,
4089 p_arc_log_used_by => 'CSCH',
4090 p_log_used_by_id => l_schedule_id,
4091 p_msg_data => 'Execute_Direct_Marketing : Increase usage',
4092 p_msg_type => 'DEBUG'
4093 );
4094
4095 AMS_List_Purge_PVT.Increase_Usage
4096 (
4097 p_api_version => 1.0,
4098 p_init_msg_list => FND_API.g_false,
4099 p_commit => FND_API.g_false,
4100 p_validation_level => FND_API.g_valid_level_full,
4101 x_return_status => l_return_status,
4102 x_msg_count => l_msg_count,
4103 x_msg_data => l_msg_data,
4104 p_list_header_id => l_list_id -- target group id
4105 );
4106
4107 IF l_return_status <> FND_API.g_ret_sts_success THEN
4108 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4109 itemkey => itemkey,
4110 aname => 'ERROR_FLAG',
4111 avalue => 'Y');
4112 Handle_Error(p_itemtype => itemtype,
4113 p_itemkey => itemkey,
4114 p_msg_count => l_msg_count,
4115 p_msg_data => l_msg_data);
4116 RETURN;
4117 END IF;
4118
4119 --
4120 -- 1.2 Is channel Email, Print, Fax
4121 --
4122 IF (l_activity_id = 10 OR l_activity_id = 20 OR l_activity_id = 480)
4123 THEN
4124 AMS_Utility_PVT.Create_Log (
4125 x_return_status => l_log_return_status,
4126 p_arc_log_used_by => 'CSCH',
4127 p_log_used_by_id => l_schedule_id,
4128 p_msg_data => 'Execute_Direct_Marketing : update previously contacted',
4129 p_msg_type => 'DEBUG'
4130 );
4131
4132 --
4133 -- Yes => 1.2.1 Increase contacted count
4134 --
4135 AMS_Listheader_PVT.Update_Prev_Contacted_Count(
4136 p_used_by_id => l_schedule_id,
4137 p_used_by => 'CSCH',
4138 p_last_contacted_date => sysdate,
4139 p_init_msg_list => FND_API.g_false,
4140 p_commit => FND_API.g_false,
4141 x_return_status => l_return_status,
4142 x_msg_count => l_msg_count,
4143 x_msg_data => l_msg_data
4144 );
4145
4146 IF l_return_status <> FND_API.g_ret_sts_success THEN
4147 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4148 itemkey => itemkey,
4149 aname => 'ERROR_FLAG',
4150 avalue => 'Y');
4151 Handle_Error(p_itemtype => itemtype,
4152 p_itemkey => itemkey,
4153 p_msg_count => l_msg_count,
4154 p_msg_data => l_msg_data);
4155 RETURN;
4156 END IF;
4157
4158 /*
4159 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4160 itemkey => itemkey,
4161 aname => 'ERROR_FLAG',
4162 avalue => 'Y');
4163 Handle_Error(p_itemtype => itemtype,
4164 p_itemkey => itemkey,
4165 p_msg_count => l_msg_count,
4166 p_msg_data => l_msg_data);*/
4167
4168 --
4169 -- 1.2.2 Stamp version in ibc_associations table
4170 -- anchaudh : from R12 onwards this stamping of cover letter version will take place in the new event subscription api.
4171 --
4172 /*AMS_Utility_PVT.Create_Log (
4173 x_return_status => l_log_return_status,
4174 p_arc_log_used_by => 'CSCH',
4175 p_log_used_by_id => l_schedule_id,
4176 p_msg_data => 'Execute_Direct_Marketing : Stamping version',
4177 p_msg_type => 'DEBUG'
4178 );
4179
4180 -- get associated cover letter and its live version
4181 OPEN c_cover_letter_det(l_schedule_id);
4182 FETCH c_cover_letter_det INTO l_association_id, l_cover_letter_id, l_cover_letter_ver_id;
4183 CLOSE c_cover_letter_det;
4184
4185 IF l_association_id IS NOT null
4186 AND l_cover_letter_id IS NOT null
4187 AND l_cover_letter_ver_id IS NOT NULl
4188 THEN
4189 Ibc_Associations_Pkg.UPDATE_ROW(
4190 p_association_id => l_association_id
4191 ,p_content_item_id => l_cover_letter_id
4192 ,p_citem_version_id => l_cover_letter_ver_id
4193 ,p_association_type_code => 'AMS_CSCH'
4194 ,p_associated_object_val1 => l_schedule_id );
4195 ELSE
4196 -- throw error because no live cover letter is associated with the schedule
4197 -- either no cover letter is associated OR the cover letter associated has no live ver
4198 NULL;
4199 END IF;*/
4200
4201 --
4202 -- 1.2.3 Send Fulfillment Request
4203 --
4204 AMS_Utility_PVT.Create_Log (
4205 x_return_status => l_log_return_status,
4206 p_arc_log_used_by => 'CSCH',
4207 p_log_used_by_id => l_schedule_id,
4208 p_msg_data => 'Execute_Direct_Marketing : Call to fulfillment',
4209 p_msg_type => 'DEBUG'
4210 );
4211
4212 -- user id added by anchaudh on 09-nov-2004 for bug# 3799053
4213 OPEN c_csch_det(l_schedule_id);
4214 FETCH c_csch_det INTO l_csch_owner_user_id;
4215 CLOSE c_csch_det;
4216
4217 AMS_Fulfill_PVT.Ams_Fulfill(
4218 p_api_version => 1.0,
4219 p_init_msg_list => FND_API.g_false,
4220 p_commit => FND_API.g_false,
4221 x_return_status => l_return_status,
4222 x_msg_count => l_msg_count,
4223 x_msg_data => l_msg_data,
4224 x_request_history_id => l_request_id,
4225 p_schedule_id => l_schedule_id,
4226 -- user id passing added by anchaudh on 09-nov-2004 for bug# 3799053
4227 p_user_id => Ams_Utility_pvt.get_user_id(l_csch_owner_user_id)
4228 ) ;
4229
4230 AMS_Utility_PVT.Create_Log (
4231 x_return_status => l_log_return_status,
4232 p_arc_log_used_by => 'CSCH',
4233 p_log_used_by_id => l_schedule_id,
4234 p_msg_data => 'Execute_Direct_Marketing : Call to fulfillment : Return status is '||l_return_status,
4235 p_msg_type => 'DEBUG'
4236 );
4237
4238 IF l_return_status <> FND_API.g_ret_sts_success THEN
4239 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4240 itemkey => itemkey,
4241 aname => 'ERROR_FLAG',
4242 avalue => 'Y');
4243 Handle_Error(p_itemtype => itemtype,
4244 p_itemkey => itemkey,
4245 p_msg_count => l_msg_count,
4246 p_msg_data => l_msg_data);
4247 RETURN;
4248 END IF;
4249
4250 --
4251 -- 1.2.4 Update list sent out date
4252 --
4253 AMS_Utility_PVT.Create_Log (
4254 x_return_status => l_log_return_status,
4255 p_arc_log_used_by => 'CSCH',
4256 p_log_used_by_id => l_schedule_id,
4257 p_msg_data => 'Execute_Direct_Marketing : calling update_list_send_out_date ',
4258 p_msg_type => 'DEBUG'
4259 );
4260
4261 Update_List_Sent_Out_Date(
4262 p_api_version => 1.0,
4263 p_init_msg_list => FND_API.g_false,
4264 p_commit => FND_API.g_false,
4265
4266 x_return_status => l_return_status,
4267 x_msg_count => l_msg_count,
4268 x_msg_data => l_msg_data,
4269
4270 p_list_header_id => l_list_id);
4271
4272 AMS_Utility_PVT.Create_Log (
4273 x_return_status => l_log_return_status,
4274 p_arc_log_used_by => 'CSCH',
4275 p_log_used_by_id => l_schedule_id,
4276 p_msg_data => 'Execute_Direct_Marketing : update_list_send_out_date : Return status is '||l_return_status,
4277 p_msg_type => 'DEBUG'
4278 );
4279
4280 IF l_return_status <> FND_API.g_ret_sts_success THEN
4281 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4282 itemkey => itemkey,
4283 aname => 'ERROR_FLAG',
4284 avalue => 'Y');
4285 Handle_Error(p_itemtype => itemtype,
4286 p_itemkey => itemkey,
4287 p_msg_count => l_msg_count,
4288 p_msg_data => l_msg_data);
4289 RETURN;
4290 END IF;
4291
4292 END IF; -- activity is email / print / fax
4293
4294 -- 05-apr-2004 soagrawa added ELSE part for when TGRP does not exist
4295 -- this is needed for automated flows like Repeating Schedules / Triggers
4296 -- pls refer bug# 3553087
4297
4298 ELSE
4299 -- if TGRP does not exist
4300 -- AMS_Utility_PVT.Error_Message('AMS_CSCH_NO_TARGET_GROUP');
4301
4302 -- Throw a valid error, if TG does not exist.. Filter should be on channel email.fax/print and Telemarketing.
4303 -- fix for bug # 4184571
4304 IF (l_activity_id = 10 OR l_activity_id = 20 OR l_activity_id = 460 OR l_activity_id = 480)
4305 THEN
4306
4307 AMS_Utility_PVT.Create_Log (
4308 x_return_status => l_log_return_status,
4309 p_arc_log_used_by => 'CSCH',
4310 p_log_used_by_id => l_schedule_id,
4311 p_msg_data => 'Execute_Direct_Marketing : Target Group is empty',
4312 p_msg_type => 'DEBUG'
4313 );
4314
4315 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4316 itemkey => itemkey,
4317 aname => 'ERROR_FLAG',
4318 avalue => 'Y');
4319
4320 /* Handle_Error(p_itemtype => itemtype, -- 29-apr-2004 anchaudh : fixed the reopened bug#3553087
4321 p_itemkey => itemkey,
4322 p_msg_count => l_msg_count,
4323 p_msg_data => l_msg_data);*/
4324
4325 l_error_msg := FND_MESSAGE.get_string('AMS','AMS_CSCH_NO_TARGET_GROUP');
4326
4327 WF_ENGINE.SetItemAttrText(itemtype => itemtype, -- 29-apr-2004 anchaudh : fixed the reopened bug#3553087
4328 itemkey => itemkey,
4329 aname => 'ERROR_MESSAGE',
4330 avalue => l_error_msg);
4331
4332
4333 END IF;
4334
4335 END IF; -- target group exists
4336
4337 END IF; -- func mode is RUN
4338
4339 -- CANCEL mode - Normal Process Execution
4340 IF (funcmode = 'CANCEL')
4341 THEN
4342 RETURN;
4343 END IF;
4344
4345 -- TIMEOUT mode - Normal Process Execution
4346 IF (funcmode = 'TIMEOUT')
4347 THEN
4348 RETURN;
4349 END IF;
4350
4351 EXCEPTION
4352 WHEN OTHERS THEN
4353 wf_core.context(G_PKG_NAME,'Execute_Direct_Marketing',itemtype,itemkey,actid,funcmode);
4354 RAISE ;
4355 END Execute_Direct_Marketing;
4356
4357
4358
4359 --=====================================================================
4360 -- PROCEDURE
4361 -- Execute_Sales
4362 --
4363 -- PURPOSE
4364 -- This api will be used by schedule execution workflow to execute schedule
4365 -- of type Sales
4366 --
4367 -- HISTORY
4368 -- 23-Aug-2003 ptendulk Created.
4369 -- 19-Sep-2003 dbiswas Added nocopy
4370 --=====================================================================
4371 PROCEDURE Execute_Sales(itemtype IN VARCHAR2,
4372 itemkey IN VARCHAR2,
4373 actid IN NUMBER,
4374 funcmode IN VARCHAR2,
4375 result OUT NOCOPY VARCHAR2) IS
4376 l_schedule_id NUMBER;
4377 l_return_status VARCHAR2(1) := FND_API.g_ret_sts_success ;
4378 l_msg_count NUMBER;
4379 l_msg_data VARCHAR2(2000);
4380 l_user_id NUMBER;
4381 l_resp_id NUMBER;
4382 l_resp_appl_id NUMBER;
4383 BEGIN
4384 -- RUN mode - Normal Process Execution
4385 IF (funcmode = 'RUN')
4386 THEN
4387 l_schedule_id := WF_ENGINE.GetItemAttrText(
4388 itemtype => itemtype,
4389 itemkey => itemkey ,
4390 aname => 'SCHEDULE_ID' );
4391
4392 l_user_id := FND_GLOBAL.USER_ID;
4393 l_resp_id := FND_GLOBAL.RESP_ID;
4394 l_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
4395
4396 AMS_Utility_PVT.Create_Log (
4397 x_return_status => l_return_status,
4398 p_arc_log_used_by => 'CSCH',
4399 p_log_used_by_id => l_schedule_id,
4400 p_msg_data => 'Execute_Sales : started with '||TO_CHAR(l_user_id)||' '||TO_CHAR(l_resp_id)||' '||TO_CHAR(l_resp_appl_id),
4401 p_msg_type => 'DEBUG'
4402 );
4403
4404 -- Call the api to execute the sales schedule , return the error flag in l_return_status
4405 --generate_leads(l_schedule_id,'CSCH',l_return_status);
4406 generate_leads(l_schedule_id,'CSCH',l_return_status,itemtype,itemkey);--anchaudh changed the signature of this api for the leads bug.
4407
4408 AMS_Utility_PVT.Create_Log (
4409 x_return_status => l_return_status,
4410 p_arc_log_used_by => 'CSCH',
4411 p_log_used_by_id => l_schedule_id,
4412 p_msg_data => 'Execute_Sales : done',
4413 p_msg_type => 'DEBUG'
4414 );
4415
4416 IF l_return_status <> FND_API.g_ret_sts_success THEN
4417 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4418 itemkey => itemkey,
4419 aname => 'ERROR_FLAG',
4420 avalue => 'Y');
4421 Handle_Error(p_itemtype => itemtype,
4422 p_itemkey => itemkey,
4423 p_msg_count => l_msg_count,
4424 p_msg_data => l_msg_data);
4425 END IF;
4426
4427 END IF;
4428 -- CANCEL mode - Normal Process Execution
4429 IF (funcmode = 'CANCEL')
4430 THEN
4431 RETURN;
4432 END IF;
4433
4434 -- TIMEOUT mode - Normal Process Execution
4435 IF (funcmode = 'TIMEOUT')
4436 THEN
4437 RETURN;
4438 END IF;
4439 -- dbms_output.put_line('End Check Trigger stat :'||result);
4440 EXCEPTION
4441 WHEN OTHERS THEN
4442 wf_core.context(G_PKG_NAME,'Execute_Sales',itemtype,itemkey,actid,funcmode);
4443 RAISE ;
4444 END Execute_Sales;
4445
4446
4447 --=====================================================================
4448 -- PROCEDURE
4449 -- generate_leads
4450 --
4451 -- PURPOSE
4452 -- This api will be used by schedule execution workflow generate leads.
4453 --
4454 -- HISTORY
4455 -- 08-Sep-2003 asaha Created.
4456 -- 09-dec-2005 soagrawa Added limited size batch processing for perf bug 4461415
4457 --=====================================================================
4458 PROCEDURE generate_leads(
4459 p_obj_id IN NUMBER,
4460 p_obj_type IN VARCHAR2,
4461 x_return_status OUT NOCOPY VARCHAR2,
4462 itemtype IN VARCHAR2,--anchaudh changed the signature of this api for the leads bug.
4463 itemkey IN VARCHAR2--anchaudh changed the signature of this api for the leads bug.
4464 ) IS
4465
4466 l_msg_count NUMBER;
4467 l_msg_data VARCHAR2(4000);
4468 l_triggerable_flag VARCHAR2(1);
4469 l_trig_repeat_flag VARCHAR2(1);
4470 l_orig_csch_id NUMBER;
4471 l_csch_offer_id NUMBER := null;
4472
4473 cursor c_party_relationships_csr(p_party_id NUMBER) is
4474 select subject_id, object_id
4475 from hz_relationships
4476 where party_id = p_party_id;
4477
4478 -- anchaudh 17th Mar'05 : modified the cursor to pull up only purchasable products for bug#3607972.
4479 cursor c_assoc_products_csr(p_schedule_id NUMBER) is
4480 select inventory_item_id, ams_act_products.category_id, organization_id
4481 from ams_act_products,ENI_PROD_DEN_HRCHY_PARENTS_V cat
4482 where arc_act_product_used_by = 'CSCH'
4483 and act_product_used_by_id = p_schedule_id
4484 and ams_act_products.category_id = cat.category_id(+)
4485 and nvl(cat.PURCHASE_INTEREST, 'Y') <> 'N';
4486
4487 cursor c_schedule_details_csr(p_schedule_id NUMBER) is
4488 select a.source_code, a.sales_methodology_id, b.source_code_id
4489 from ams_campaign_schedules_b a, ams_source_codes b
4490 where a.schedule_id = p_schedule_id
4491 and a.status_code = 'ACTIVE'
4492 and a.source_code = b.source_code;
4493
4494 CURSOR c_sch_det(p_schedule_id NUMBER) IS -- anchaudh added this new cursor for the leads bug.
4495 SELECT NVL(triggerable_flag,'N')
4496 ,NVL(trig_repeat_flag,'N')
4497 ,orig_csch_id
4498 FROM ams_campaign_schedules_b
4499 WHERE schedule_id = p_schedule_id;
4500
4501 CURSOR c_sch_det_offer(p_schedule_id NUMBER) IS -- anchaudh added this for bug#4957178.
4502 select offer_id
4503 from
4504 OZF_ACT_OFFERS ACT_OFFER,
4505 ozf_offers off
4506 where
4507 ACT_OFFER.ARC_ACT_OFFER_USED_BY = 'CSCH'
4508 AND ACT_OFFER.act_offer_used_by_id = p_schedule_id
4509 AND off.qp_list_header_id = ACT_OFFER.qp_list_header_id
4510 AND ACT_OFFER.PRIMARY_OFFER_FLAG = 'Y';
4511
4512 -- soagrawa 09-dec-2005 added this cursor for bug 4461415
4513 cursor c_parties(p_obj_id NUMBER) is
4514 select decode(pa.party_type,'PARTY_RELATIONSHIP','ORGANIZATION','PERSON') party_type,
4515 decode(pa.party_type,'PARTY_RELATIONSHIP',rel.subject_id,null) contact_party_id,
4516 decode(pa.party_type,'PARTY_RELATIONSHIP',TO_NUMBER(le.col147),le.party_id) main_party_id,
4517 decode(pa.party_type,'PARTY_RELATIONSHIP',le.party_id,null) rel_party_id
4518 from ams_act_lists la, ams_list_entries le, hz_parties pa, hz_relationships rel
4519 where la.list_header_id = le.list_header_id
4520 and la.list_act_type = 'TARGET'
4521 and la.list_used_by = 'CSCH'
4522 and la.list_used_by_id = p_obj_id
4523 and le.enabled_flag = 'Y'
4524 and le.party_id = pa.party_id
4525 and pa.party_id = rel.party_id(+)
4526 and rel.subject_type(+) = 'PERSON';
4527
4528 -- soagrawa 09-dec-2005 added this cursor for bug 4461415
4529 cursor c_lead_headers(srccd VARCHAR2) is
4530 SELECT IMPORT_INTERFACE_ID
4531 FROM as_import_interface
4532 where promotion_code = srccd;
4533
4534
4535 CURSOR c_batch_id IS
4536 SELECT as_sl_imp_batch_s.NEXTVAL
4537 FROM DUAL;
4538
4539 CURSOR c_lead_header_id_csr IS
4540 SELECT AS_IMPORT_INTERFACE_S.NEXTVAL
4541 FROM dual;
4542
4543 CURSOR c_lead_header_id_exists_csr (l_id IN NUMBER) IS
4544 SELECT 1 FROM dual
4545 WHERE EXISTS (SELECT 1 FROM as_import_interface
4546 WHERE import_interface_id = l_id);
4547
4548 CURSOR c_lead_line_id_csr IS
4549 SELECT AS_IMP_LINES_INTERFACE_S.NEXTVAL
4550 FROM dual;
4551
4552 CURSOR c_lead_line_id_exists_csr (l_id IN NUMBER) IS
4553 SELECT 1 FROM dual
4554 WHERE EXISTS (SELECT 1 FROM as_imp_lines_interface
4555 WHERE imp_lines_interface_id = l_id);
4556
4557 CURSOR c_loaded_rows_for_lead (batch_id_in IN NUMBER) IS
4558 SELECT COUNT(*)
4559 FROM as_import_interface
4560 WHERE batch_id = batch_id_in;
4561
4562 l_assoc_product_row c_assoc_products_csr%ROWTYPE;
4563 l_schedule_details c_schedule_details_csr%ROWTYPE;
4564 l_contact_party_details c_party_relationships_csr%ROWTYPE;
4565
4566 TYPE Lead_Header_Id_Table IS TABLE OF as_import_interface.IMPORT_INTERFACE_ID%TYPE;
4567 l_lead_header_ids Lead_Header_Id_Table; -- no need to initialize
4568
4569 TYPE Main_Party_Id_Table IS TABLE OF hz_parties.PARTY_ID%TYPE;
4570 l_main_party_ids Main_Party_Id_Table; -- no need to initialize
4571
4572 TYPE Contact_Point_Party_Id_Table IS TABLE OF hz_parties.PARTY_ID%TYPE;
4573 l_contact_point_party_ids Contact_Point_Party_Id_Table; -- no need to initialize
4574
4575 TYPE Rel_Party_Id_Table IS TABLE OF hz_parties.PARTY_ID%TYPE;
4576 l_rel_party_ids Rel_Party_Id_Table; -- no need to initialize
4577
4578 TYPE Party_Type_Table IS TABLE OF hz_parties.PARTY_TYPE%TYPE;
4579 l_party_types Party_Type_Table; -- no need to initialize
4580
4581 l_return_status VARCHAR2(1);
4582 l_party_id NUMBER;
4583 l_org_id VARCHAR2(500);
4584 l_asn_group_id VARCHAR2(500);
4585 l_num_asn_group_id NUMBER;
4586 l_num_asn_resource_id NUMBER;
4587 l_asn_resource_id NUMBER;
4588 l_contact_id NUMBER;
4589 l_rel_party_id NUMBER;
4590 l_party_type VARCHAR2(30);
4591 l_no_of_prods NUMBER := 0;
4592 l_batch_id NUMBER;
4593 l_loaded_rows NUMBER;
4594 l_no_of_tgrp_entries NUMBER := 0;
4595 l_method_id NUMBER;
4596 l_request_id NUMBER;
4597 j NUMBER;
4598
4599 l_lead_header_id NUMBER;
4600 l_lead_line_id NUMBER;
4601 l_dummy NUMBER;
4602
4603 -- soagrawa 09-dec-2005 added this variable for bug 4461415
4604 l_batch_size NUMBER := 1000;
4605
4606 BEGIN
4607 x_return_status := FND_API.g_ret_sts_success;
4608
4609 IF (AMS_DEBUG_HIGH_ON) THEN
4610 AMS_Utility_PVT.debug_message('generate_leads: Enter');
4611 END IF;
4612
4613 IF(p_obj_type <> 'CSCH') THEN
4614
4615 IF (AMS_DEBUG_HIGH_ON) THEN
4616 AMS_UTILITY_Pvt.debug_message('generate_leads: Unsupported object type : '||p_obj_type);
4617 END IF;
4618
4619 x_return_status := FND_API.g_ret_sts_error;
4620 return;
4621 END IF;
4622
4623 AMS_Utility_PVT.Create_Log (
4624 x_return_status => l_return_status,
4625 p_arc_log_used_by => 'CSCH',
4626 p_log_used_by_id => p_obj_id,
4627 p_msg_data => 'Starting lead generation process schedule id is ' || to_char(p_obj_id),
4628 p_msg_type => 'DEBUG'
4629 );
4630
4631 OPEN c_sch_det_offer(p_obj_id);
4632 FETCH c_sch_det_offer INTO l_csch_offer_id;
4633 CLOSE c_sch_det_offer;
4634
4635
4636 OPEN c_sch_det(p_obj_id); -- anchaudh added for the leads bug.
4637 FETCH c_sch_det INTO l_triggerable_flag,l_trig_repeat_flag,l_orig_csch_id;
4638 CLOSE c_sch_det;
4639
4640 if(l_triggerable_flag = 'N' and l_trig_repeat_flag = 'N' and l_orig_csch_id IS NULL) then -- anchaudh added for the leads bug.
4641
4642 l_asn_group_id := WF_ENGINE.GetItemAttrText(
4643 itemtype => itemtype,
4644 itemkey => itemkey ,
4645 aname => 'ASN_GROUP_ID');--anchaudh added for the leads bug.
4646
4647 --insert_log_mesg('Anirban inside generate_leads api, value of l_asn_group_id retrieved is :'||l_asn_group_id);
4648
4649 l_asn_resource_id := WF_ENGINE.GetItemAttrText(
4650 itemtype => itemtype,
4651 itemkey => itemkey ,
4652 aname => 'ASN_RESOURCE_ID');--anchaudh added for the leads bug.
4653 --insert_log_mesg('Anirban inside generate_leads api, value of l_asn_resource_id retrieved is :'||l_asn_resource_id);
4654
4655 end if;
4656
4657 OPEN c_schedule_details_csr(p_obj_id);
4658 FETCH c_schedule_details_csr INTO l_schedule_details;
4659 IF(c_schedule_details_csr%NOTFOUND) THEN
4660 IF (AMS_DEBUG_HIGH_ON) THEN
4661 AMS_UTILITY_Pvt.debug_message('generate_leads: No Schedule details found for '||TO_CHAR(p_obj_id));
4662 END IF;
4663 CLOSE c_schedule_details_csr;
4664 x_return_status := FND_API.g_ret_sts_error;
4665 return;
4666 END IF;
4667 CLOSE c_schedule_details_csr;
4668
4669 OPEN c_batch_id;
4670 FETCH c_batch_id INTO l_batch_id;
4671 CLOSE c_batch_id;
4672 IF (AMS_DEBUG_HIGH_ON) THEN
4673 AMS_UTILITY_Pvt.debug_message('generate_leads: generated batch id: '||TO_CHAR(l_batch_id));
4674 END IF;
4675
4676 -- bulk collect party related info
4677 -- soagrawa 09-dec-2005 added LIMIT on this bulk collect bug 4461415 and moved it to a cursor
4678
4679 open c_parties(p_obj_id);
4680
4681 loop
4682 fetch c_parties
4683 BULK COLLECT INTO l_party_types, l_contact_point_party_ids, l_main_party_ids, l_rel_party_ids
4684 LIMIT l_batch_size;
4685
4686 AMS_Utility_PVT.Create_Log (
4687 x_return_status => l_return_status,
4688 p_arc_log_used_by => 'CSCH',
4689 p_log_used_by_id => p_obj_id,
4690 p_msg_data => 'Lead interface processing ' || l_main_party_ids.count,
4691 p_msg_type => 'DEBUG'
4692 );
4693
4694 --now generate lead headers for all parties by bulk insert
4695 FORALL j IN l_main_party_ids.FIRST..l_main_party_ids.LAST
4696 -- insert in as_import_interface based on target group entry details
4697 INSERT INTO as_import_interface
4698 (
4699 IMPORT_INTERFACE_ID --NOT NULL NUMBER
4700 , LAST_UPDATE_DATE --NOT NULL DATE
4701 , LAST_UPDATED_BY --NOT NULL NUMBER
4702 , CREATION_DATE --NOT NULL DATE
4703 , CREATED_BY --NOT NULL NUMBER
4704 , LAST_UPDATE_LOGIN --NOT NULL NUMBER
4705 , LOAD_TYPE -- VARCHAR2(20)
4706 , LOAD_DATE --NOT NULL DATE
4707 , PROMOTION_CODE -- VARCHAR2(50)
4708 , STATUS_CODE -- VARCHAR2(30)
4709 , SOURCE_SYSTEM -- VARCHAR2(30)
4710 , PARTY_TYPE -- VARCHAR2(30)
4711 , BATCH_ID -- NUMBER(15)
4712 , PARTY_ID -- NUMBER(15)
4713 , PARTY_SITE_ID -- NUMBER(15)
4714 ,load_status -- VARCHAR2(20)
4715 ,contact_party_id -- NUMBER
4716 ,vehicle_response_code
4717 ,qualified_flag
4718 ,sales_methodology_id -- NUMBER
4719 ,rel_party_id
4720 ,offer_id --anchaudh added for bug#4957178
4721 )
4722 VALUES
4723 (
4724 AS_IMPORT_INTERFACE_S.NEXTVAL --IMPORT_INTERFACE_ID --NOT NULL NUMBER
4725 , SYSDATE --LAST_UPDATE_DATE --NOT NULL DATE
4726 , FND_GLOBAL.user_id --LAST_UPDATED_BY --NOT NULL NUMBER
4727 , SYSDATE --CREATION_DATE --NOT NULL DATE
4728 , FND_GLOBAL.user_id --CREATED_BY --NOT NULL NUMBER
4729 , FND_GLOBAL.conc_login_id --LAST_UPDATE_LOGIN --NOT NULL NUMBER
4730 , 'LEAD_LOAD' --LOAD_TYPE -- VARCHAR2(20)
4731 , SYSDATE --LOAD_DATE --NOT NULL DATE
4732 , l_schedule_details.source_code --PROMOTION_CODE -- VARCHAR2(50)
4733 , null --STATUS_CODE -- VARCHAR2(30)
4734 , 'SALES_CAMPAIGN' --SOURCE_SYSTEM -- VARCHAR2(30)
4735 , l_party_types(j) --PARTY_TYPE -- VARCHAR2(30)
4736 , l_batch_id --BATCH_ID -- NUMBER(15)
4737 , l_main_party_ids(j) --PARTY_ID -- NUMBER(15)
4738 , NULL --PARTY_SITE_ID -- NUMBER(15)
4739 ,'NEW' -- load_status -- VARCHAR2(20)
4740 , l_contact_point_party_ids(j) -- contact party id, subject id for relationship -- NUMBER
4741 , 'SALES'
4742 , 'Y'
4743 ,l_schedule_details.sales_methodology_id -- sales methodology id NUMBER
4744 ,l_rel_party_ids(j) -- relationship party id
4745 ,l_csch_offer_id -- primary offer id --anchaudh added for bug#4957178
4746 );
4747
4748 exit when c_parties%notfound;
4749
4750 end loop;
4751
4752 close c_parties;
4753
4754 IF (AMS_DEBUG_HIGH_ON) THEN
4755 AMS_Utility_PVT.debug_message('generate_leads: insertion done in lead interface tables');
4756 END IF;
4757
4758
4759
4760
4761
4762
4763 -- bulk collect lead header ids for lead lines
4764 -- soagrawa 09-dec-2005 added LIMIT on this bulk collect bug 4461415 and moved it to a cursor
4765
4766 OPEN c_lead_headers(l_schedule_details.source_code);
4767
4768 LOOP
4769 FETCH c_lead_headers
4770 BULK COLLECT INTO l_lead_header_ids
4771 LIMIT l_batch_size;
4772
4773 --open products cursor and collects associated product info
4774 l_no_of_prods := 0;
4775 OPEN c_assoc_products_csr(p_obj_id);
4776 LOOP
4777 FETCH c_assoc_products_csr INTO l_assoc_product_row;
4778 EXIT WHEN c_assoc_products_csr%NOTFOUND;
4779
4780 l_no_of_prods := l_no_of_prods+1;
4781
4782 FORALL j IN l_lead_header_ids.FIRST..l_lead_header_ids.LAST
4783 --bulk insert each Product/Product Category in as_imp_lines_interface table
4784 INSERT INTO as_imp_lines_interface
4785 (
4786 IMP_LINES_INTERFACE_ID --NOT NULL NUMBER
4787 , IMPORT_INTERFACE_ID --NOT NULL NUMBER
4788 , LAST_UPDATE_DATE --NOT NULL DATE
4789 , LAST_UPDATED_BY --NOT NULL NUMBER
4790 , CREATION_DATE --NOT NULL DATE
4791 , CREATED_BY --NOT NULL NUMBER
4792 , LAST_UPDATE_LOGIN --NOT NULL NUMBER
4793 , CATEGORY_ID --NOT NULL NUMBER
4794 , INVENTORY_ITEM_ID --NUMBER
4795 , ORGANIZATION_ID --NUMBER
4796 , SOURCE_PROMOTION_ID --NUMBER
4797 )
4798 VALUES
4799 (
4800 AS_IMP_LINES_INTERFACE_S.NEXTVAL --IMP_LINES_INTERFACE_ID --NOT NULL NUMBER
4801 , l_lead_header_ids(j) --IMPORT_INTERFACE_ID --NOT NULL NUMBER
4802 , SYSDATE --LAST_UPDATE_DATE --NOT NULL DATE
4803 , FND_GLOBAL.user_id --LAST_UPDATED_BY --NOT NULL NUMBER
4804 , SYSDATE --CREATION_DATE --NOT NULL DATE
4805 , FND_GLOBAL.user_id --CREATED_BY --NOT NULL NUMBER
4806 , FND_GLOBAL.conc_login_id --LAST_UPDATE_LOGIN --NOT NULL NUMBER
4807 ,l_assoc_product_row.category_id
4808 ,l_assoc_product_row.inventory_item_id
4809 ,l_assoc_product_row.organization_id
4810 ,l_schedule_details.source_code_id
4811 );
4812
4813 END LOOP; -- for products
4814 CLOSE c_assoc_products_csr;
4815
4816 exit when c_lead_headers%notfound;
4817
4818 end loop;
4819
4820 close c_lead_headers;
4821
4822
4823
4824 IF (AMS_DEBUG_HIGH_ON) THEN
4825 AMS_UTILITY_Pvt.debug_message('generate_leads: No. of Products/Categories : '||TO_CHAR(l_no_of_prods));
4826 END IF;
4827
4828 -- At this point we will have added all the records in as_import_interface table.
4829 -- Now we can call the concurrent program for lead process.
4830 OPEN c_loaded_rows_for_lead(l_batch_id);
4831 FETCH c_loaded_rows_for_lead INTO l_loaded_rows;
4832 CLOSE c_loaded_rows_for_lead;
4833
4834 IF (AMS_DEBUG_HIGH_ON) THEN
4835 AMS_Utility_PVT.debug_message('generate_leads: No of lead header rows created : '||TO_CHAR(l_loaded_rows));
4836 END IF;
4837
4838 AMS_Utility_PVT.Create_Log (
4839 x_return_status => l_return_status,
4840 p_arc_log_used_by => 'CSCH',
4841 p_log_used_by_id => p_obj_id,
4842 p_msg_data => 'No. of lead headers generated '||TO_CHAR(l_loaded_rows),
4843 p_msg_type => 'DEBUG'
4844 );
4845
4846 l_request_id := 0;
4847
4848 if(l_triggerable_flag = 'N' and l_trig_repeat_flag = 'N' and l_orig_csch_id IS NULL) then -- anchaudh added for the leads bug.
4849 l_num_asn_resource_id := to_number(l_asn_resource_id);
4850 if(l_asn_group_id = '9999') then
4851 l_num_asn_group_id := null;
4852 else
4853 l_num_asn_group_id := to_number(l_asn_group_id);
4854 end if;
4855 else
4856 l_num_asn_group_id := null;
4857 l_num_asn_resource_id := null;
4858 end if;
4859
4860 --insert_log_mesg('Anirban just before calling conc. program , values of :l_num_asn_resource_id and l_num_asn_group_id are :'||l_num_asn_resource_id || ' '||l_num_asn_group_id);
4861
4862 -- Call the concurrent program for leads.
4863 l_request_id := FND_REQUEST.SUBMIT_REQUEST (
4864 application => 'AS',
4865 program => 'ASXSLIMP',
4866 argument1 => 'SALES_CAMPAIGN',
4867 argument2 => 'N',
4868 --argument2 => NULL,
4869 argument3 => l_batch_id,
4870 argument4 => 'N',
4871 argument5 => null,
4872 argument6 => null,
4873 argument7 => l_num_asn_resource_id,--anchaudh added for the leads bug.
4874 argument8 => l_num_asn_group_id--anchaudh added for the leads bug.
4875 );
4876
4877 --insert_log_mesg('Anirban inside generate_leads api, value of l_batch_id and l_request_id after submitting Conc. request is :'||l_batch_id || ' '||l_request_id);
4878
4879 AMS_Utility_PVT.Create_Log (
4880 x_return_status => l_return_status,
4881 p_arc_log_used_by => 'CSCH',
4882 p_log_used_by_id => p_obj_id,
4883 p_msg_data => 'Starting LEAD program (ASXSLIMP) -- concurrent program_id is ' || to_char(l_request_id) ||' for batch id '||TO_CHAR(l_batch_id),
4884 p_msg_type => 'DEBUG'
4885 );
4886
4887 IF l_request_id = 0 THEN
4888 l_msg_data := fnd_message.get;
4889 AMS_Utility_PVT.Create_Log (
4890 x_return_status => l_return_status,
4891 p_arc_log_used_by => 'CSCH',
4892 p_log_used_by_id => p_obj_id,
4893 p_msg_data => l_msg_data,
4894 p_msg_type => 'DEBUG'
4895 );
4896 x_return_status := FND_API.g_ret_sts_error;
4897 --insert_log_mesg('Anirban inside generate_leads api,ERROR occured in the conc. program. ');
4898 return;
4899 END IF;
4900
4901 -- Import completed successfully
4902 IF (AMS_DEBUG_HIGH_ON) THEN
4903 AMS_Utility_PVT.debug_message('generate_leads: Submitted Lead import request with request id : '||TO_CHAR(l_request_id));
4904 AMS_Utility_PVT.debug_message('generate_leads: End');
4905 END IF;
4906
4907 END generate_leads;
4908
4909
4910
4911
4912
4913
4914 --=====================================================================
4915 -- PROCEDURE
4916 -- Check_WF_Error
4917 --
4918 -- PURPOSE
4919 -- This api will be used by schedule execution workflow to check error
4920 -- The api will check the error flag and based on the value, the error
4921 -- notifications will be sent to schedule owner.
4922 --
4923 -- HISTORY
4924 -- 23-Aug-2003 ptendulk Created.
4925 -- 19-Sep-2003 dbiswas Added nocopy
4926 --=====================================================================
4927 PROCEDURE Check_WF_Error(itemtype IN VARCHAR2,
4928 itemkey IN VARCHAR2,
4929 actid IN NUMBER,
4930 funcmode IN VARCHAR2,
4931 result OUT NOCOPY VARCHAR2) IS
4932 l_error_flag VARCHAR2(30) ;
4933 l_return_status VARCHAR2(1);
4934 l_schedule_id NUMBER;
4935 BEGIN
4936 -- dbms_output.put_line('Process Check_Repeat');
4937 -- RUN mode - Normal Process Execution
4938 IF (funcmode = 'RUN')
4939 THEN
4940 l_schedule_id := WF_ENGINE.GetItemAttrText(
4941 itemtype => itemtype,
4942 itemkey => itemkey ,
4943 aname => 'SCHEDULE_ID' );
4944
4945 AMS_Utility_PVT.Create_Log (
4946 x_return_status => l_return_status,
4947 p_arc_log_used_by => 'CSCH',
4948 p_log_used_by_id => l_schedule_id,
4949 p_msg_data => 'Check_WF_Error : started',
4950 p_msg_type => 'DEBUG'
4951 );
4952
4953 l_error_flag := WF_ENGINE.GetItemAttrText(
4954 itemtype => itemtype,
4955 itemkey => itemkey ,
4956 aname => 'ERROR_FLAG' );
4957
4958
4959 IF l_error_flag = 'N' THEN
4960 result := 'COMPLETE:N' ;
4961 ELSE
4962 result := 'COMPLETE:Y' ;
4963 END IF ;
4964 END IF;
4965
4966 -- CANCEL mode - Normal Process Execution
4967 IF (funcmode = 'CANCEL')
4968 THEN
4969 result := 'COMPLETE:Y' ;
4970 RETURN;
4971 END IF;
4972
4973 -- TIMEOUT mode - Normal Process Execution
4974 IF (funcmode = 'TIMEOUT')
4975 THEN
4976 result := 'COMPLETE:Y' ;
4977 RETURN;
4978 END IF;
4979 EXCEPTION
4980 WHEN OTHERS THEN
4981 wf_core.context(G_PKG_NAME,'Check_WF_Error',itemtype,itemkey,actid,funcmode);
4982 raise ;
4983 END Check_WF_Error ;
4984
4985 --========================================================================
4986 -- PROCEDURE
4987 -- WRITE_LOG
4988 -- Purpose
4989 -- This method will be used to write logs for this api
4990 -- HISTORY
4991 -- 10-Oct-2000 dbiswas Created.
4992 --
4993 --========================================================================
4994
4995 PROCEDURE WRITE_LOG ( p_api_name IN VARCHAR2 := NULL,
4996 p_log_message IN VARCHAR2 := NULL)
4997 IS
4998 l_api_name VARCHAR2(30);
4999 l_log_mesg VARCHAR2(2000);
5000 l_return_status VARCHAR2(1);
5001 BEGIN
5002 l_api_name := p_api_name;
5003 l_log_mesg := p_log_message;
5004 AMS_Utility_PVT.debug_message (
5005 p_log_level => g_log_level,
5006 p_module_name => 'ams.plsql.'||'.'|| g_pkg_name||'.'||l_api_name||'.'||l_log_mesg,
5007 p_text => p_log_message
5008 );
5009
5010 AMS_Utility_PVT.Create_Log (
5011 x_return_status => l_return_status,
5012 p_arc_log_used_by => 'CSCH',
5013 p_log_used_by_id => 1,
5014 p_msg_data => p_log_message,
5015 p_msg_type => 'DEBUG'
5016 );
5017
5018 END WRITE_LOG;
5019
5020 --=====================================================================
5021 -- Procedure
5022 -- WF_REPEAT_INIT_VAR
5023 --
5024 -- PURPOSE
5025 -- This api is used by scheduler workflow to initialize the attributes
5026 -- Returns the processId information in the schedules table
5027 --
5028 -- HISTORY
5029 -- 07-Oct-2003 dbiswas Created.
5030 -- 09-nov-2004 anchaudh Now setting item owner along with bug fix for bug# 3799053
5031 --=====================================================================
5032 PROCEDURE Wf_Repeat_Init_var(itemtype IN VARCHAR2,
5033 itemkey IN VARCHAR2,
5034 actid IN NUMBER,
5035 funcmode IN VARCHAR2,
5036 result OUT NOCOPY VARCHAR2) IS
5037
5038 CURSOR c_sched_dat (p_schedule_id IN NUMBER) IS
5039 SELECT csch.schedule_name,
5040 csch.start_date_time,
5041 csch.end_date_time,
5042 csch.status_code,
5043 csch.owner_user_id,
5044 csch.activity_id,
5045 csch.activity_type_code,
5046 nvl(csch.orig_csch_id, csch.schedule_id),
5047 scheduler.frequency,
5048 scheduler.frequency_type,
5049 camp.actual_exec_start_date,
5050 camp.actual_exec_end_date,
5051 parentCSCH.start_date_time,
5052 parentCSCH.end_date_time,
5053 parentCSCH.status_code
5054 FROM ams_campaign_schedules_vl csch,
5055 ams_scheduler scheduler,
5056 ams_campaigns_all_b camp,
5057 ams_campaign_Schedules_b parentCSCH
5058 WHERE csch.schedule_id = p_schedule_id
5059 AND scheduler.OBJECT_ID = nvl(csch.orig_csch_id, csch.schedule_id)
5060 AND scheduler.OBJECT_TYPE = 'CSCH'
5061 AND camp.campaign_id = csch.campaign_id
5062 and parentCSCH.schedule_id = nvl(csch.orig_Csch_id,csch.schedule_id);
5063
5064 CURSOR c_emp_dtl(l_res_id IN NUMBER) IS
5065 SELECT employee_id
5066 FROM ams_jtf_rs_emp_v
5067 WHERE resource_id = l_res_id ;
5068
5069 l_schedule_id NUMBER;
5070 l_schedule_name VARCHAR2(240);
5071 l_csch_st_date DATE;
5072 l_csch_en_date DATE;
5073 l_csch_status VARCHAR2(30);
5074 l_csch_owner NUMBER;
5075 l_csch_act_id NUMBER;
5076 l_csch_act_code VARCHAR2(30);
5077 l_csch_orig_id NUMBER;
5078 l_sched_freq NUMBER;
5079 l_sched_freq_type VARCHAR2(30);
5080 l_camp_st_date DATE;
5081 l_camp_en_date DATE;
5082 l_parent_st_date DATE;
5083 l_parent_en_date DATE;
5084 l_parent_status VARCHAR2(30);
5085 l_api_name VARCHAR2(30);
5086 l_return_status VARCHAR2(1);
5087 l_emp_id NUMBER;
5088 l_user_name VARCHAR2(100);
5089 l_display_name VARCHAR2(100);
5090
5091 l_temp_varaibale VARCHAR2(50);
5092 l_schedule_next_run_st_date DATE;
5093
5094
5095
5096 BEGIN
5097 l_api_name := 'Wf_Repeat_Init_var';
5098 IF (funcmode = 'RUN')
5099 THEN
5100
5101 l_schedule_id := WF_ENGINE.GetItemAttrText(
5102 itemtype => itemtype,
5103 itemkey => itemkey ,
5104 aname => 'SCHEDULE_ID');
5105
5106
5107
5108 l_temp_varaibale := WF_ENGINE.GetItemAttrText(itemtype => itemtype,
5109 itemkey => itemkey ,
5110 aname => 'AMS_PARENT_STATUS'
5111 );
5112
5113 l_schedule_next_run_st_date := to_date(l_temp_varaibale,'DD-MM-RRRR HH24:MI:SS');
5114
5115 WRITE_LOG (l_api_name, 'WF_REPEAT_INIT_VAR: SCHEDULE ID IS '||l_schedule_id
5116 || '|| SCHEDULED KICKOFF TIME: '||to_char(l_schedule_next_run_st_date,'DD-MON-RRRR HH24:MI:SS')
5117 || '|| CURRENT SYSTEM TIME: '||to_char(SYSDATE,'DD-MON-RRRR HH24:MI:SS'));
5118
5119 WRITE_LOG(l_api_name, 'WF_REPEAT_INIT_VAR: AMS_SCHEDULE_NEXT_RUN_ST_DATE DERIVED FROM PARAMETER LIST '
5120 ||'SCHEDULE ID RECEIVED IS:' || l_schedule_id
5121 ||to_char(l_schedule_next_run_st_date,'DD-MON-RRRR HH24:MI:SS')
5122 ||' ; '||'L_TEMP_VARAIBALE VALUE : '
5123 ||l_temp_varaibale);
5124
5125
5126
5127 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
5128 itemkey => itemkey,
5129 aname => 'AMS_SCHEDULE_ID',
5130 avalue => l_schedule_id);
5131
5132 UPDATE ams_campaign_schedules_b
5133 SET REPEAT_WORKFLOW_ITEM_KEY = itemkey
5134 WHERE schedule_id = l_schedule_id;
5135
5136 WRITE_LOG (l_api_name, 'Wf_Repeat_Init_var: Schedule id is '||l_schedule_id);
5137
5138 OPEN c_sched_dat(l_schedule_id);
5139 FETCH c_sched_dat INTO l_schedule_name,
5140 l_csch_st_date,
5141 l_csch_en_date,
5142 l_csch_status,
5143 l_csch_owner,
5144 l_csch_act_id,
5145 l_csch_act_code,
5146 l_csch_orig_id,
5147 l_sched_freq,
5148 l_sched_freq_type,
5149 l_camp_st_date,
5150 l_camp_en_date,
5151 l_parent_st_date,
5152 l_parent_en_date,
5153 l_parent_status
5154 ;
5155 CLOSE c_sched_dat;
5156
5157 OPEN c_emp_dtl(l_csch_owner);
5158 FETCH c_emp_dtl INTO l_emp_id;
5159 -- anchaudh setting item owner along with bug fix for bug# 3799053
5160 IF c_emp_dtl%FOUND
5161 THEN
5162 WF_DIRECTORY.getrolename
5163 ( p_orig_system => 'PER',
5164 p_orig_system_id => l_emp_id ,
5165 p_name => l_user_name,
5166 p_display_name => l_display_name );
5167
5168 IF l_user_name IS NOT NULL THEN
5169 Wf_Engine.SetItemOwner(itemtype => itemtype,
5170 itemkey => itemkey,
5171 owner => l_user_name);
5172 END IF;
5173 END IF;
5174 CLOSE c_emp_dtl;
5175
5176 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
5177 itemkey => itemkey,
5178 aname => 'SCHEDULE_NAME',
5179 avalue => l_schedule_name);
5180
5181 WF_ENGINE.SetItemUserkey(itemtype => itemtype,
5182 itemkey => itemkey ,
5183 userkey => l_schedule_name);
5184
5185 WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
5186 itemkey => itemkey,
5187 aname => 'AMS_SCHEDULE_START_DATE',
5188 avalue => to_date(l_csch_st_date,'DD-MM-RRRR HH24:MI:SS') );
5189
5190 WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
5191 itemkey => itemkey,
5192 aname => 'AMS_SCHEDULE_END_DATE',
5193 avalue => to_date(l_csch_en_date,'DD-MM-RRRR HH24:MI:SS') );
5194
5195 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
5196 itemkey => itemkey,
5197 aname => 'SCHEDULE_STATUS',
5198 avalue => l_csch_status);
5199
5200 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
5201 itemkey => itemkey,
5202 aname => 'SCHEDULE_OWNER',
5203 avalue => l_user_name);
5204
5205 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
5206 itemkey => itemkey,
5207 aname => 'SCHEDULE_CHANNEL',
5208 avalue => l_csch_act_id);
5209
5210 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
5211 itemkey => itemkey,
5212 aname => 'ACTIVITY_TYPE',
5213 avalue => l_csch_act_code);
5214
5215 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
5216 itemkey => itemkey,
5217 aname => 'AMS_ORIG_SCHEDULE_ID',
5218 avalue => l_csch_orig_id);
5219
5220 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
5221 itemkey => itemkey,
5222 aname => 'AMS_SCHEDULER_FREQUENCY',
5223 avalue => l_sched_freq);
5224
5225 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
5226 itemkey => itemkey,
5227 aname => 'AMS_SCHEDULER_FREQUENCY_TYPE',
5228 avalue => l_sched_freq_type);
5229
5230 WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
5231 itemkey => itemkey,
5232 aname => 'AMS_CAMPAIGN_START_DATE',
5233 avalue => to_date(l_camp_st_date,'DD-MM-RRRR HH24:MI:SS') );
5234
5235 WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
5236 itemkey => itemkey,
5237 aname => 'AMS_CAMPAIGN_END_DATE',
5238 avalue => to_date(l_camp_en_date,'DD-MM-RRRR HH24:MI:SS') );
5239
5240 WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
5241 itemkey => itemkey,
5242 aname => 'AMS_PARENT_START_DATE',
5243 avalue => to_date(l_parent_st_date,'DD-MM-RRRR HH24:MI:SS') );
5244
5245 WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
5246 itemkey => itemkey,
5247 aname => 'AMS_PARENT_END_DATE',
5248 avalue => to_date(l_parent_en_date,'DD-MM-RRRR HH24:MI:SS') );
5249
5250 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
5251 itemkey => itemkey,
5252 aname => 'AMS_PARENT_STATUS',
5253 avalue => l_parent_status);
5254
5255
5256 WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
5257 itemkey => itemkey ,
5258 aname => 'AMS_SCHEDULER_NEXT_RUN_ST_DATE',
5259 avalue => l_schedule_next_run_st_date);
5260
5261 END IF;
5262
5263 -- CANCEL mode - Normal Process Execution
5264 IF (funcmode = 'CANCEL')
5265 THEN
5266 RETURN;
5267 END IF;
5268
5269 -- TIMEOUT mode - Normal Process Execution
5270 IF (funcmode = 'TIMEOUT')
5271 THEN
5272 RETURN;
5273 END IF;
5274 -- dbms_output.put_line('End Check scheduler stat :'||result);
5275 EXCEPTION
5276 WHEN OTHERS THEN
5277 wf_core.context(G_PKG_NAME,'Wf_Repeat_Init_var',itemtype,itemkey,actid,funcmode);
5278 raise ;
5279 END Wf_Repeat_Init_var ;
5280
5281 --=====================================================================
5282 -- Procedure
5283 -- WF_REPEAT_CHECK_EXECUTE
5284 --
5285 -- PURPOSE
5286 -- This api is used by scheduler workflow to check if the schedule
5287 -- should execute or not based on status and dates
5288 --
5289 -- HISTORY
5290 -- 07-Oct-2003 dbiswas Created.
5291 --=====================================================================
5292 PROCEDURE Wf_Repeat_Check_Execute(itemtype IN VARCHAR2,
5293 itemkey IN VARCHAR2,
5294 actid IN NUMBER,
5295 funcmode IN VARCHAR2,
5296 result OUT NOCOPY VARCHAR2) IS
5297
5298 CURSOR c_sched_data (p_schedule_id IN NUMBER) IS
5299 SELECT nvl(csch.orig_csch_id, csch.schedule_id)
5300 FROM ams_campaign_schedules_vl csch
5301 WHERE csch.schedule_id = p_schedule_id;
5302
5303
5304 l_schedule_id NUMBER;
5305 l_csch_orig_id NUMBER;
5306 l_sched_end_date DATE;
5307 l_sched_status VARCHAR2(30);
5308 l_orig_csch_id NUMBER;
5309 l_camp_end_date DATE;
5310 l_orig_csch_end_date DATE;
5311 l_orig_csch_status VARCHAR2(30);
5312 l_api_name VARCHAR2(30);
5313 l_return_status VARCHAR2(1);
5314
5315 BEGIN
5316 l_api_name := 'WF_REPEAT_CHECK_EXECUTE';
5317
5318 l_schedule_id := WF_ENGINE.GetItemAttrText(
5319 itemtype => itemtype,
5320 itemkey => itemkey ,
5321 aname => 'SCHEDULE_ID');
5322
5323
5324 OPEN c_sched_data(l_schedule_id);
5325 FETCH c_sched_data INTO l_csch_orig_id ;
5326 CLOSE c_sched_data;
5327
5328
5329 IF (funcmode = 'RUN')
5330 THEN
5331 WRITE_LOG(l_api_name, 'Wf_Repeat_Check_Execute: Schedule id is '||l_schedule_id);
5332
5333 l_sched_status := WF_ENGINE.GetItemAttrText(
5334 itemtype => itemtype,
5335 itemkey => itemkey ,
5336 aname => 'SCHEDULE_STATUS');
5337
5338 l_sched_end_date := WF_ENGINE.GetItemAttrDate(
5339 itemtype => itemtype,
5340 itemkey => itemkey ,
5341 aname => 'AMS_SCHEDULE_END_DATE');
5342
5343 l_orig_csch_status := WF_ENGINE.GetItemAttrText(
5344 itemtype => itemtype,
5345 itemkey => itemkey ,
5346 aname => 'AMS_PARENT_STATUS');
5347
5348 l_orig_csch_end_date := WF_ENGINE.GetItemAttrDate(
5349 itemtype => itemtype,
5350 itemkey => itemkey ,
5351 aname => 'AMS_PARENT_END_DATE');
5352
5353
5354 l_camp_end_date := WF_ENGINE.GetItemAttrDate(
5355 itemtype => itemtype,
5356 itemkey => itemkey ,
5357 aname => 'AMS_CAMPAIGN_END_DATE');
5358
5359
5360
5361 WRITE_LOG(l_api_name, 'Wf_Repeat_Check_Execute: Schedule id is '||l_schedule_id||
5362 '|| SCHEDULE_STATUS is: '||l_sched_status||
5363 '|| AMS_SCHEDULE_END_DATE is: '||l_sched_end_date||
5364 '|| AMS_PARENT_STATUS is: '||l_orig_csch_status||
5365 '|| AMS_PARENT_END_DATE is: '||l_orig_csch_end_date||
5366 '|| AMS_CAMPAIGN_END_DATE is: '||l_camp_end_date);
5367
5368
5369 if(l_csch_orig_id <> l_schedule_id) then -- ensuring that for only the child activities, the end date check is performed , and not for the parent activity. Bug#4690754 : anchaudh: 3 Nov'05.
5370
5371 IF (l_sched_status = 'AVAILABLE' or l_sched_status = 'ACTIVE')
5372 THEN
5373 IF (l_orig_csch_status = 'AVAILABLE' or l_orig_csch_status = 'ACTIVE')
5374 THEN
5375 IF (nvl(l_orig_csch_end_date, l_camp_end_date) >=SYSDATE)
5376 THEN
5377 result := 'COMPLETE:Y' ;
5378 ELSE
5379 WRITE_LOG (l_api_name, 'Wf_Repeat_Check_Execute: returns out of bounds for exec date for schedule id '||l_schedule_id);
5380 result := 'COMPLETE:N';
5381 END IF;
5382 END IF;
5383 END IF;
5384 else -- ensuring that for only the child activities, the end date check is performed , and not for the parent activity. Bug#4690754 : anchaudh: 3 Nov'05.
5385 result := 'COMPLETE:Y';
5386 end if; -- ensuring that for only the child activities, the end date check is performed , and not for the parent activity. Bug#4690754 : anchaudh: 3 Nov'05.
5387
5388 END IF; --funcmode RUN
5389 -- CANCEL mode - Normal Process Execution
5390 IF (funcmode = 'CANCEL')
5391 THEN
5392 RETURN;
5393 END IF;
5394
5395 -- TIMEOUT mode - Normal Process Execution
5396 IF (funcmode = 'TIMEOUT')
5397 THEN
5398 RETURN;
5399 END IF;
5400 -- dbms_output.put_line('End Check scheduler stat :'||result);
5401 EXCEPTION
5402 WHEN OTHERS THEN
5403 wf_core.context(G_PKG_NAME,'Wf_Repeat_Check_Exec',itemtype,itemkey,actid,funcmode);
5404 raise ;
5405 END Wf_Repeat_Check_Execute;
5406
5407 --=====================================================================
5408 -- Procedure
5409 -- WF_REPEAT_SCHEDULER
5410 --
5411 -- PURPOSE
5412 -- This api is used by scheduler workflow to check when the next schedule run should be
5413 --
5414 -- HISTORY
5415 -- 07-Oct-2003 dbiswas Created.
5416 --=====================================================================
5417 PROCEDURE Wf_Repeat_Scheduler(itemtype IN VARCHAR2,
5418 itemkey IN VARCHAR2,
5419 actid IN NUMBER,
5420 funcmode IN VARCHAR2,
5421 result OUT NOCOPY VARCHAR2) IS
5422
5423 l_schedule_id NUMBER;
5424 l_scheduler_frequency NUMBER;
5425 l_scheduler_frequency_type VARCHAR2(30);
5426 l_scheduler_next_run_date DATE;
5427 l_api_name VARCHAR2(30);
5428 l_msg_count NUMBER;
5429 l_msg_data VARCHAR2(2000);
5430
5431 l_return_status VARCHAR2(1);
5432
5433 l_new_last_run_date DATE;
5434 l_orig_csch_id NUMBER;
5435
5436
5437 BEGIN
5438 l_api_name := 'WF_REPEAT_SCHEDULER';
5439 IF (funcmode = 'RUN')
5440 THEN
5441
5442 l_schedule_id := WF_ENGINE.GetItemAttrText(
5443 itemtype => itemtype,
5444 itemkey => itemkey ,
5445 aname => 'SCHEDULE_ID');
5446 WRITE_LOG(l_api_name, 'Wf_Repeat_Scheduler: Schedule id ' ||l_schedule_id);
5447
5448 l_scheduler_frequency := WF_ENGINE.GetItemAttrNumber(
5449 itemtype => itemtype,
5450 itemkey => itemkey ,
5451 aname => 'AMS_SCHEDULER_FREQUENCY');
5452
5453 l_scheduler_frequency_type := WF_ENGINE.GetItemAttrText(
5454 itemtype => itemtype,
5455 itemkey => itemkey ,
5456 aname => 'AMS_SCHEDULER_FREQUENCY_TYPE');
5457
5458 WRITE_LOG(l_api_name, 'Wf_Repeat_Scheduler: Calling AMS_SCHEDULER_PVT.Schedule_Repeat with freq type ' ||l_scheduler_frequency_type);
5459
5460
5461 l_new_last_run_date := WF_ENGINE.GetItemAttrDate(
5462 itemtype => itemtype,
5463 itemkey => itemkey ,
5464 aname => 'AMS_SCHEDULER_NEXT_RUN_ST_DATE');
5465
5466
5467
5468 SELECT nvl(orig_csch_id, schedule_id)
5469 INTO l_orig_csch_id
5470 FROM ams_campaign_schedules_b
5471 WHERE schedule_id = l_schedule_id;
5472
5473 IF l_new_last_run_date IS NULL then
5474
5475 l_new_last_run_date := SYSDATE;
5476 ELSIF l_new_last_run_date = '' then
5477 l_new_last_run_date := SYSDATE;
5478 END IF;
5479
5480
5481
5482 WRITE_LOG(l_api_name, 'Wf_Repeat_Scheduler: Schedule_Repeat returned last run date from the WF Engine : '||to_char(l_new_last_run_date,'DD-MON-RRRR HH24:MI:SS')||'; For Schedule Id: '||l_schedule_id);
5483
5484
5485 WRITE_LOG(l_api_name, 'Wf_Repeat_Scheduler: Calling AMS_SCHEDULER_PVT.Schedule_Repeat with freq type ' ||l_scheduler_frequency_type||'; For Schedule Id: '||l_schedule_id);
5486
5487
5488 AMS_SCHEDULER_PVT.Schedule_Repeat (
5489 p_last_run_date => l_new_last_run_date,
5490 p_frequency => l_scheduler_frequency,
5491 p_frequency_type => l_scheduler_frequency_type,
5492 x_next_run_date => l_scheduler_next_run_date,
5493 x_return_status => l_return_status,
5494 x_msg_count => l_msg_count,
5495 x_msg_data => l_msg_data);
5496
5497
5498 IF l_return_status = FND_API.G_RET_STS_SUCCESS
5499 THEN
5500 WRITE_LOG(l_api_name, 'Wf_Repeat_Scheduler: Schedule_Repeat returned success for next run date for schedule id: '||l_schedule_id);
5501 WRITE_LOG(l_api_name, 'Wf_Repeat_Scheduler: Schedule_Repeat returned next run date is : '||to_char(l_scheduler_next_run_date,'DD-MON-RRRR HH24:MI:SS')||' Schedule Id is : '||l_schedule_id);
5502
5503 WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
5504 itemkey => itemkey ,
5505 aname => 'AMS_SCHEDULER_NEXT_RUN_ST_DATE',
5506 avalue => l_scheduler_next_run_date);
5507
5508 result := 'COMPLETE:SUCCESS' ;
5509 ELSIF l_return_status <> FND_API.G_RET_STS_SUCCESS
5510 THEN
5511 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
5512 itemkey => itemkey,
5513 aname => 'ERROR_FLAG',
5514 avalue => 'Y');
5515 Handle_Error(p_itemtype => itemtype,
5516 p_itemkey => itemkey,
5517 p_msg_count => l_msg_count,
5518 p_msg_data => l_msg_data,
5519 p_wf_err_attrib => 'AMS_SCHEDULER_ERROR_MSG');
5520
5521 WRITE_LOG(l_api_name, 'Error in scheduling next run start date caught for schedule id: '||l_schedule_id);
5522 result := 'COMPLETE:ERROR' ;
5523
5524 END IF ;
5525 END IF;
5526
5527 IF (funcmode = 'CANCEL')
5528 THEN
5529 result := 'COMPLETE:' ;
5530 RETURN;
5531 END IF;
5532
5533 IF (funcmode = 'TIMEOUT')
5534 THEN
5535 result := 'COMPLETE:' ;
5536 RETURN;
5537 END IF;
5538 EXCEPTION
5539 WHEN OTHERS THEN
5540 wf_core.context(G_PKG_NAME,l_api_name,itemtype,itemkey,actid,funcmode);
5541 raise ;
5542
5543 END Wf_Repeat_Scheduler ;
5544
5545 --=====================================================================
5546 -- Procedure
5547 -- WF_REPEAT_CHECK_CREATE_CSCH
5548 --
5549 -- PURPOSE
5550 -- This api is used by scheduler workflow to check whether to create the next child schedule
5551 -- based on schedule date boundaries. (campaign end date in case parent's end date is null
5552 --
5553 -- HISTORY
5554 -- 07-Oct-2003 dbiswas Created.
5555 --=====================================================================
5556 PROCEDURE WF_REPEAT_CHECK_CREATE_CSCH(itemtype IN VARCHAR2,
5557 itemkey IN VARCHAR2,
5558 actid IN NUMBER,
5559 funcmode IN VARCHAR2,
5560 result OUT NOCOPY VARCHAR2) IS
5561
5562 l_schedule_id NUMBER;
5563 l_schedule_next_run_date DATE;
5564 l_parent_end_date DATE;
5565 l_campaign_end_date DATE;
5566 l_api_name VARCHAR2(30);
5567
5568 l_return_status VARCHAR2(1);
5569 BEGIN
5570 l_api_name := 'WF_REPEAT_CHECK_CREATE_CSCH';
5571 IF (funcmode = 'RUN')
5572 THEN
5573
5574 l_schedule_id := WF_ENGINE.GetItemAttrText(
5575 itemtype => itemtype,
5576 itemkey => itemkey ,
5577 aname => 'SCHEDULE_ID');
5578 WRITE_LOG(l_api_name, 'WF_REPEAT_CHECK_CREATE_CSCH: Started for schedule id ' ||l_schedule_id);
5579
5580 l_schedule_next_run_date := WF_ENGINE.GetItemAttrDate(
5581 itemtype => itemtype,
5582 itemkey => itemkey ,
5583 aname => 'AMS_SCHEDULER_NEXT_RUN_ST_DATE');
5584
5585 l_parent_end_date := WF_ENGINE.GetItemAttrDate(
5586 itemtype => itemtype,
5587 itemkey => itemkey ,
5588 aname => 'AMS_PARENT_END_DATE');
5589
5590 l_campaign_end_date := WF_ENGINE.GetItemAttrDate(
5591 itemtype => itemtype,
5592 itemkey => itemkey ,
5593 aname => 'AMS_CAMPAIGN_END_DATE');
5594
5595 IF (nvl(l_parent_end_date, l_campaign_end_date) > l_schedule_next_run_date)
5596 THEN
5597 result := 'COMPLETE:Y' ;
5598 ELSE
5599 result := 'COMPLETE:N' ;
5600 END IF ;
5601
5602 END IF ; -- end func mode
5603
5604 IF (funcmode = 'CANCEL')
5605 THEN
5606 result := 'COMPLETE:' ;
5607 RETURN;
5608 END IF;
5609
5610 IF (funcmode = 'TIMEOUT')
5611 THEN
5612 result := 'COMPLETE:' ;
5613 RETURN;
5614 END IF;
5615 EXCEPTION
5616 WHEN OTHERS THEN
5617 wf_core.context(G_PKG_NAME,l_api_name,itemtype,itemkey,actid,funcmode);
5618 raise ;
5619
5620 END WF_REPEAT_CHECK_CREATE_CSCH ;
5621
5622 --=====================================================================
5623 -- Procedure
5624 -- WF_REPEAT_CREATE_CSCH
5625 --
5626 -- PURPOSE
5627 -- This api is used by scheduler workflow to create the next child schedule
5628 --
5629 -- HISTORY
5630 -- 11-Oct-2003 dbiswas Created.
5631 --=====================================================================
5632 PROCEDURE WF_REPEAT_CREATE_CSCH(itemtype IN VARCHAR2,
5633 itemkey IN VARCHAR2,
5634 actid IN NUMBER,
5635 funcmode IN VARCHAR2,
5636 result OUT NOCOPY VARCHAR2) IS
5637
5638 l_schedule_id NUMBER;
5639 l_schedule_start_date DATE;
5640 l_schedule_end_date DATE;
5641 l_scheduler_frequency NUMBER;
5642 l_scheduler_frequency_type VARCHAR2(30);
5643 l_parent_sched_id NUMBER;
5644 l_parent_end_date DATE;
5645 l_campaign_end_date DATE;
5646 l_api_name VARCHAR2(30);
5647 l_msg_count NUMBER;
5648 l_msg_data VARCHAR2(2000);
5649 l_return_status VARCHAR2(1);
5650 l_usr_start_time DATE;
5651 l_start_time DATE;
5652 l_timezone NUMBER;
5653 l_child_sched_id NUMBER;
5654
5655 -- CURSOR c_sch_det (p_schedule_id NUMBER) IS
5656 -- SELECT start_date_time, timezone_id
5657 -- FROM ams_campaign_schedules_b
5658 -- WHERE schedule_id = p_schedule_id;
5659
5660 BEGIN
5661 l_api_name := 'WF_REPEAT_CREATE_CSCH';
5662 l_schedule_id := WF_ENGINE.GetItemAttrText(
5663 itemtype => itemtype,
5664 itemkey => itemkey ,
5665 aname => 'SCHEDULE_ID');
5666
5667 IF (funcmode = 'RUN')
5668 THEN
5669 WRITE_LOG(l_api_name, 'WF_REPEAT_CREATE_CSCH: Schedule id is '||l_schedule_id);
5670
5671 l_parent_sched_id := WF_ENGINE.GetItemAttrNumber(
5672 itemtype => itemtype,
5673 itemkey => itemkey ,
5674 aname => 'AMS_ORIG_SCHEDULE_ID');
5675
5676 l_campaign_end_date := WF_ENGINE.GetItemAttrDate(
5677 itemtype => itemtype,
5678 itemkey => itemkey ,
5679 aname => 'AMS_CAMPAIGN_END_DATE');
5680
5681 l_parent_end_date := WF_ENGINE.GetItemAttrDate(
5682 itemtype => itemtype,
5683 itemkey => itemkey ,
5684 aname => 'AMS_PARENT_END_DATE');
5685
5686 l_schedule_start_date := WF_ENGINE.GetItemAttrDate(
5687 itemtype => itemtype,
5688 itemkey => itemkey ,
5689 aname => 'AMS_SCHEDULER_NEXT_RUN_ST_DATE');
5690
5691 l_scheduler_frequency := WF_ENGINE.GetItemAttrNumber(
5692 itemtype => itemtype,
5693 itemkey => itemkey ,
5694 aname => 'AMS_SCHEDULER_FREQUENCY');
5695
5696 l_scheduler_frequency_type := WF_ENGINE.GetItemAttrText(
5697 itemtype => itemtype,
5698 itemkey => itemkey ,
5699 aname => 'AMS_SCHEDULER_FREQUENCY_TYPE');
5700
5701 --anchaudh: commented out on 11 Jun '05 to fix bug#4477717 .
5702 /*AMS_UTILITY_PVT.Convert_Timezone(
5703 p_init_msg_list => FND_API.G_TRUE,
5704 x_return_status => l_return_status,
5705 x_msg_count => l_msg_count,
5706 x_msg_data => l_msg_data,
5707 p_user_tz_id => l_timezone,
5708 p_in_time => l_schedule_start_date,
5709 p_convert_type => 'USER',
5710 x_out_time => l_usr_start_time
5711 );
5712
5713 AMS_SCHEDULER_PVT.Schedule_Repeat(
5714 p_last_run_date => l_usr_start_time,
5715 p_frequency => l_scheduler_frequency,
5716 p_frequency_type => l_scheduler_frequency_type,
5717 x_next_run_date => l_schedule_end_date,
5718 x_return_status => l_return_status,
5719 x_msg_count => l_msg_count,
5720 x_msg_data => l_msg_data);
5721
5722 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS)
5723 THEN
5724 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
5725 itemkey => itemkey,
5726 aname => 'ERROR_FLAG',
5727 avalue => 'Y');
5728 Handle_Error(p_itemtype => itemtype,
5729 p_itemkey => itemkey,
5730 p_msg_count => l_msg_count,
5731 p_msg_data => l_msg_data);
5732
5733 WRITE_LOG (l_api_name, 'Errored when creating child end date'||'.'||l_schedule_start_date);
5734 END IF;*/
5735
5736
5737 -- OPEN c_sch_det(l_schedule_id);
5738 -- FETCH c_sch_det INTO l_start_time, l_timezone;
5739 -- CLOSE c_sch_det;
5740
5741
5742
5743 -- If any errors happen let start time be sysdate
5744 /*IF l_return_status = FND_API.G_RET_STS_ERROR THEN
5745 l_usr_start_time := SYSDATE;
5746 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5747 l_usr_start_time := SYSDATE;
5748 END IF;*/
5749
5750 --IF l_schedule_end_date > nvl(l_parent_end_date, l_campaign_end_date)
5751 --THEN
5752 l_schedule_end_date := nvl(l_parent_end_date, l_campaign_end_date);
5753 --END IF;
5754
5755
5756 AMS_SCHEDULER_PVT.Create_Next_Schedule ( p_parent_sched_id => l_parent_sched_id,
5757 p_child_sched_st_date => l_schedule_start_date,--l_usr_start_time,
5758 p_child_sched_en_date => l_schedule_end_date,
5759 x_child_sched_id => l_child_sched_id,
5760 x_msg_count => l_msg_count,
5761 x_msg_data => l_msg_data,
5762 x_return_status => l_return_status
5763 );
5764
5765 IF l_return_status = FND_API.G_RET_STS_SUCCESS
5766 THEN
5767 WRITE_LOG(l_api_name, 'WF_REPEAT_CREATE_CSCH: Create next schedule returned Success ');
5768
5769 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
5770 itemkey => itemkey,
5771 aname => 'AMS_NEW_SCHEDULE_ID',
5772 avalue => l_child_sched_id);
5773
5774 result := 'COMPLETE:SUCCESS' ;
5775 ELSIF l_return_status <> FND_API.G_RET_STS_SUCCESS
5776 THEN
5777 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
5778 itemkey => itemkey,
5779 aname => 'ERROR_FLAG',
5780 avalue => 'Y');
5781 Handle_Error(p_itemtype => itemtype,
5782 p_itemkey => itemkey,
5783 p_msg_count => l_msg_count,
5784 p_msg_data =>l_msg_data,
5785 p_wf_err_attrib => 'AMS_CSCH_CREATE_ERROR'
5786 );
5787 result := 'COMPLETE:ERROR' ;
5788 END IF; -- success in create_next_schedule
5789 END IF; -- funcmode RUN
5790
5791 IF (funcmode = 'CANCEL')
5792 THEN
5793 result := 'COMPLETE:' ;
5794 RETURN;
5795 END IF;
5796
5797 IF (funcmode = 'TIMEOUT')
5798 THEN
5799 result := 'COMPLETE:' ;
5800 RETURN;
5801 END IF;
5802 EXCEPTION
5803 WHEN OTHERS THEN
5804 wf_core.context(G_PKG_NAME,l_api_name,itemtype,itemkey,actid,funcmode);
5805 raise ;
5806
5807 END WF_REPEAT_CREATE_CSCH;
5808
5809 --=====================================================================
5810 -- Procedure
5811 -- WF_REPEAT_RAISE_EVENT
5812 --
5813 -- PURPOSE
5814 -- This api is used by scheduler workflow to raise the event for the next sched run
5815 --
5816 -- HISTORY
5817 -- 11-Oct-2003 dbiswas Created.
5818 --=====================================================================
5819
5820 PROCEDURE WF_REPEAT_RAISE_EVENT(itemtype IN VARCHAR2,
5821 itemkey IN VARCHAR2,
5822 actid IN NUMBER,
5823 funcmode IN VARCHAR2,
5824 result OUT NOCOPY VARCHAR2) IS
5825
5826 l_schedule_id NUMBER;
5827 l_parameter_list WF_PARAMETER_LIST_T;
5828 l_schedule_next_run_st_date DATE;
5829 l_temp_variable varchar2(50);
5830
5831 l_sch_text VARCHAR2(100);
5832 l_new_item_key VARCHAR2(30);
5833 l_api_name VARCHAR2(30);
5834
5835 BEGIN
5836 l_api_name := 'WF_REPEAT_RAISE_EVENT';
5837 IF (funcmode = 'RUN')
5838 THEN
5839 l_schedule_id := WF_ENGINE.GetItemAttrText(
5840 itemtype => itemtype,
5841 itemkey => itemkey ,
5842 aname => 'AMS_NEW_SCHEDULE_ID');
5843
5844 l_parameter_list := WF_PARAMETER_LIST_T();
5845
5846 wf_event.AddParameterToList(p_name => 'SCHEDULE_ID',
5847 p_value => l_schedule_id,
5848 p_parameterlist => l_parameter_list);
5849
5850 l_schedule_next_run_st_date := WF_ENGINE.GetItemAttrDate(itemtype => itemtype,
5851 itemkey => itemkey ,
5852 aname => 'AMS_SCHEDULER_NEXT_RUN_ST_DATE'
5853 );
5854
5855 l_temp_variable := to_char(l_schedule_next_run_st_date,'DD-MON-RRRR HH24:MI:SS');
5856
5857 wf_event.AddParameterToList(p_name => 'AMS_PARENT_STATUS',
5858 p_value => l_temp_variable,
5859 p_parameterlist => l_parameter_list);
5860
5861 WRITE_LOG(l_api_name, 'WF_REPEAT_RAISE_EVENT: ADD AMS_SCHEDULER_NEXT_RUN_ST_DATE TO PARAMTER LIST : '||
5862 '|| NEW SCHEDULE ID PASSED: '||l_schedule_id||'; || '
5863 ||to_char(l_schedule_next_run_st_date,'DD-MON-RRRR HH24:MI:SS')||' ; '
5864 ||'|| L_TEMP_VARIABLE VALUE PASSED: '||l_temp_variable||'|| SYSDATE: '
5865 ||to_char(sysdate,'DD-MON-RRRR HH24:MI:SS'));
5866
5867
5868 l_new_item_key := l_schedule_id ||'RPT'|| TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
5869
5870 WRITE_LOG(l_api_name, 'WF_REPEAT_RAISE_EVENT: Before raising event ');
5871 WRITE_LOG(l_api_name, 'WF_REPEAT_RAISE_EVENT: Before raising event with key '||l_new_item_key);
5872
5873 Wf_Event.Raise
5874 ( p_event_name => 'oracle.apps.ams.campaign.RepeatScheduleEvent',
5875 p_event_key => l_new_item_key,
5876 p_parameters => l_parameter_list,
5877 p_send_date => l_schedule_next_run_st_date
5878 );
5879
5880 END IF;
5881
5882 -- CANCEL mode - Normal Process Execution
5883 IF (funcmode = 'CANCEL')
5884 THEN
5885 RETURN;
5886 END IF;
5887
5888 -- TIMEOUT mode - Normal Process Execution
5889 IF (funcmode = 'TIMEOUT')
5890 THEN
5891 RETURN;
5892 END IF;
5893 -- dbms_output.put_line('End Check Trigger stat :'||result);
5894 EXCEPTION
5895 WHEN OTHERS THEN
5896 wf_core.context(G_PKG_NAME,l_api_name,itemtype,itemkey,actid,funcmode);
5897 raise ;
5898 END WF_REPEAT_RAISE_EVENT ;
5899
5900
5901 --===============================================================================================
5902 -- Procedure
5903 -- Raise_BE_On_Status_change
5904 --
5905 -- PURPOSE
5906 -- This api is called to raise a Business event on a UserStatus change for CSCH, EVEO, EONE
5907 --
5908 -- ALGORITHM
5909 -- 1. Check for the Object Type (CSCH, EVEO and EONE )
5910 -- Yes => 1.1 Open the respective cursor to get the required values
5911 -- 1.2 if old_status_code not equal to new_status_code
5912 -- Yes => Raise Business event
5913 --
5914 -- Any error in any of the API callouts?
5915 -- => a) Set RETURN STATUS to E
5916 --
5917 -- OPEN ISSUES
5918 -- 1. Should we do a explicit exit on Object_type not found.
5919 --
5920 -- HISTORY
5921 -- 17-Mar-2005 spendem Created. Enhancement # 3805347
5922 --===============================================================================================
5923
5924 PROCEDURE RAISE_BE_ON_STATUS_CHANGE(p_obj_id IN NUMBER,
5925 p_obj_type IN VARCHAR2,
5926 p_old_status_code IN VARCHAR2,
5927 p_new_status_code IN VARCHAR2 ) IS
5928
5929
5930 CURSOR c_csch_det IS
5931 SELECT related_event_from
5932 , related_event_id
5933 FROM ams_campaign_schedules_b
5934 WHERE schedule_id = p_obj_id;
5935
5936
5937 l_api_version CONSTANT NUMBER := 1.0 ;
5938 l_api_name CONSTANT VARCHAR2(30) := 'RAISE_BE_ON_STATUS_CHANGE';
5939
5940 l_old_status_code VARCHAR2(30);
5941 l_related_event_from VARCHAR2(30);
5942 l_related_event_id NUMBER;
5943 l_schedule_type VARCHAR2(4);
5944 l_parameter_list WF_PARAMETER_LIST_T;
5945 l_new_item_key VARCHAR2(100);
5946
5947 BEGIN
5948
5949 -- input debug messages.
5950 IF (AMS_DEBUG_HIGH_ON) THEN
5951
5952 AMS_Utility_PVT.debug_message(l_api_name || ': start');
5953
5954 END IF;
5955
5956
5957 IF ( p_obj_type = 'CSCH' ) THEN
5958
5959 l_schedule_type := p_obj_type;
5960
5961 --open cursor for campaign schedules and fetch values
5962 OPEN c_csch_det;
5963 FETCH c_csch_det INTO l_related_event_from, l_related_event_id;
5964 CLOSE c_csch_det;
5965
5966 ELSIF ( p_obj_type = 'EVEO' OR p_obj_type = 'EONE' ) THEN
5967
5968 l_schedule_type := p_obj_type;
5969
5970 ELSE
5971
5972 RETURN;
5973
5974 END IF;
5975
5976 IF ( p_old_status_code <> p_new_status_code )
5977 THEN
5978
5979 l_parameter_list := WF_PARAMETER_LIST_T();
5980 l_new_item_key := p_obj_id || 'STATUS' || p_obj_type || TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
5981
5982
5983 wf_event.AddParameterToList(p_name => 'OBJECT_TYPE',
5984 p_value => l_schedule_type,
5985 p_parameterlist => l_parameter_list);
5986
5987
5988 wf_event.AddParameterToList(p_name => 'OBJECT_ID',
5989 p_value => p_obj_id,
5990 p_parameterlist => l_parameter_list);
5991
5992
5993 wf_event.AddParameterToList(p_name => 'OLD_STATUS',
5994 p_value => p_old_status_code,
5995 p_parameterlist => l_parameter_list);
5996
5997
5998 wf_event.AddParameterToList(p_name => 'NEW_STATUS',
5999 p_value => p_new_status_code,
6000 p_parameterlist => l_parameter_list);
6001
6002
6003 wf_event.AddParameterToList(p_name => 'RELATED_EVENT_OBJECT_TYPE',
6004 p_value => l_related_event_from,
6005 p_parameterlist => l_parameter_list);
6006
6007
6008 wf_event.AddParameterToList(p_name => 'RELATED_EVENT_OBJECT_ID',
6009 p_value => l_related_event_id,
6010 p_parameterlist => l_parameter_list);
6011
6012 IF (AMS_DEBUG_HIGH_ON) THEN
6013
6014 AMS_Utility_PVT.debug_message('Raise Business event for User Status Change' || ' ' || l_new_item_key);
6015
6016 END IF;
6017
6018
6019
6020 WF_EVENT.Raise
6021 ( p_event_name => 'oracle.apps.ams.common.ObjectStatusChanged',
6022 p_event_key => l_new_item_key,
6023 p_parameters => l_parameter_list);
6024
6025 END IF; -- end if for raise Business event.
6026
6027
6028 END RAISE_BE_ON_STATUS_CHANGE;
6029
6030
6031 -------------------------------------------------------------
6032 -- Start of Comments
6033 -- Name
6034 -- HANDLE_COLLATERAL
6035 --
6036 -- Purpose
6037 -- This function is called from Business Event
6038 -- anchaudh created for R12.
6039 -------------------------------------------------------------
6040 FUNCTION HANDLE_COLLATERAL(p_subscription_guid IN RAW,
6041 p_event IN OUT NOCOPY WF_EVENT_T
6042 ) RETURN VARCHAR2
6043 IS
6044 l_schedule_id NUMBER;
6045 l_association_id NUMBER;
6046 l_citem_id NUMBER;
6047 l_citem_ver_id NUMBER;
6048 l_Return_status varchar2(20);
6049 l_log_return_status VARCHAR2(1) := FND_API.g_ret_sts_success ;
6050
6051 CURSOR c_citem_assoc (l_csch_id IN NUMBER) IS
6052 SELECT assoc.association_id, assoc.content_item_id, ci.live_citem_version_id
6053 FROM ibc_associations assoc, ibc_content_Items ci
6054 WHERE assoc.association_type_code in ('AMS_CSCH','AMS_COLLAT')
6055 AND assoc.associated_object_val1 = to_char(l_csch_id)
6056 AND assoc.content_item_id = ci.content_Item_id;
6057
6058
6059 PROCEDURE_NAME CONSTANT VARCHAR2(30) := 'HANDLE_COLLATERAL';
6060
6061 BEGIN
6062
6063 -- Get the Value of SCHEDULE_ID
6064 l_schedule_id := p_event.getValueForParameter('SCHEDULE_ID');
6065
6066 OPEN c_citem_assoc(l_schedule_id);
6067 LOOP
6068 FETCH c_citem_assoc INTO l_association_id, l_citem_id, l_citem_ver_id;
6069 EXIT WHEN c_citem_assoc%NOTFOUND;
6070
6071 AMS_Utility_PVT.Create_Log (
6072 x_return_status => l_log_return_status,
6073 p_arc_log_used_by => 'CSCH',
6074 p_log_used_by_id => l_schedule_id,
6075 p_msg_data => 'HANDLE_COLLATERAL : Stamping collateral versions',
6076 p_msg_type => 'DEBUG'
6077 );
6078
6079 IF l_association_id IS NOT null
6080 AND l_citem_id IS NOT null
6081 AND l_citem_ver_id IS NOT NULl
6082 THEN
6083 Ibc_Associations_Pkg.UPDATE_ROW(
6084 p_association_id => l_association_id
6085 ,p_citem_version_id => l_citem_ver_id
6086 );
6087 END IF;
6088 END LOOP;
6089 CLOSE c_citem_assoc;
6090
6091
6092 return 'SUCCESS';
6093
6094 EXCEPTION
6095
6096 WHEN OTHERS THEN
6097 WF_CORE.CONTEXT('AMS_ScheduleRules_PVT','HANDLE_COLLATERAL',
6098 p_event.getEventName( ), p_subscription_guid);
6099 WF_EVENT.setErrorInfo(p_event, 'ERROR');
6100 RETURN 'ERROR';
6101 END HANDLE_COLLATERAL;
6102
6103 --===============================================================================================
6104 -- PROCEDURE
6105 -- CHECK_NOTIFICATION_PREFERENCE
6106 --
6107 -- PURPOSE
6108 -- This method will be used to check the notification preference for an activity
6109 --
6110 -- ALGORITHM
6111 -- 1. Check for the NOTIFY_ON_ACTIVATION_FLAG for the Schedule Id
6112 -- Y => RETURN True
6113 -- N => RETURN False
6114 --
6115 -- HISTORY
6116 -- 08-Aug-2005 srivikri Created.
6117 -- 01-sep-2005 soagrawa Cleaned up
6118 -- 30-sep-2005 srivikri Changes for Repeating Frequency Region display
6119 -- 07-Mar-2006 srivikri changes for bug 4690754
6120 --===============================================================================================
6121
6122 PROCEDURE CHECK_NOTIFICATION_PREFERENCE(itemtype IN VARCHAR2,
6123 itemkey IN VARCHAR2,
6124 actid IN NUMBER,
6125 funcmode IN VARCHAR2,
6126 result OUT NOCOPY VARCHAR2) IS
6127
6128 CURSOR l_sch_det (p_schedule_id NUMBER) IS
6129 SELECT
6130 NOTIFY_ON_ACTIVATION_FLAG,
6131 triggerable_flag,
6132 trig_repeat_flag,
6133 source_code,
6134 Med.media_name,
6135 lookup.MEANING,
6136 orig_csch_id,
6137 frequency,
6138 frequency_type,
6139 end_date_time,
6140 campaign_id
6141 FROM ams_campaign_schedules_b csch,
6142 ams_scheduler scheduler,
6143 AMS_MEDIA_VL Med,
6144 ams_lookups lookup
6145 WHERE csch.schedule_id = p_schedule_id
6146 AND scheduler.OBJECT_ID(+) = nvl(csch.orig_csch_id, csch.schedule_id)
6147 AND scheduler.OBJECT_TYPE(+) = 'CSCH'
6148 AND Med.media_id = csch.activity_id
6149 AND lookup.LOOKUP_TYPE(+) = 'AMS_TRIGGER_FREQUENCY_TYPE'
6150 AND lookup.LOOKUP_CODE(+) = scheduler.frequency_type;
6151
6152 CURSOR l_new_sch_det (p_new_schedule_id NUMBER) IS
6153 SELECT
6154 schedule_name
6155 FROM AMS_CAMPAIGN_SCHEDULES_VL
6156 WHERE SCHEDULE_ID = p_new_schedule_id;
6157
6158 CURSOR l_camp_det (p_campaign_id NUMBER) IS
6159 SELECT
6160 actual_exec_end_date
6161 FROM AMS_CAMPAIGNS_ALL_B
6162 WHERE CAMPAIGN_ID = p_campaign_id;
6163
6164
6165 l_api_version CONSTANT NUMBER := 1.0 ;
6166 l_api_name CONSTANT VARCHAR2(35) := 'CHECK_NOTIFICATION_PREFERENCE';
6167 l_flag VARCHAR2(1);
6168 l_triggerable_flag VARCHAR2(1);
6169 l_trig_repeat_flag VARCHAR2(1);
6170 l_schedule_id NUMBER;
6171 l_return_status VARCHAR2(1);
6172 --l_repeat_freq_type VARCHAR2(30);
6173 l_msg_data VARCHAR2(30);
6174 l_source_code VARCHAR2(30);
6175 l_new_schedule_id NUMBER;
6176 l_new_schedule_name VARCHAR2(240);
6177 l_scheduler_frequency NUMBER;
6178 l_media_name VARCHAR2(120);
6179 l_freq_meaning VARCHAR2(80);
6180 l_orig_csch_id NUMBER;
6181
6182 l_query_freq NUMBER;
6183 l_query_freq_type VARCHAR2(80);
6184 l_csch_end_date DATE;
6185 l_scheduler_next_run_date DATE;
6186 l_campaign_end_date DATE;
6187 l_campaign_id NUMBER;
6188 l_msg_count NUMBER;
6189
6190 BEGIN
6191
6192 -- RUN mode - Normal Process Execution
6193 IF (funcmode = 'RUN')
6194 THEN
6195 l_schedule_id := to_number(WF_ENGINE.GetItemAttrText(
6196 itemtype => itemtype,
6197 itemkey => itemkey ,
6198 aname => 'SCHEDULE_ID' ));
6199
6200 AMS_Utility_PVT.Create_Log (
6201 x_return_status => l_return_status,
6202 p_arc_log_used_by => 'CSCH',
6203 p_log_used_by_id => l_schedule_id,
6204 p_msg_data => 'CHECK_NOTIFICATION_PREFERENCE : started',
6205 p_msg_type => 'DEBUG'
6206 );
6207
6208 OPEN l_sch_det(l_schedule_id);
6209 FETCH l_sch_det INTO l_flag, l_triggerable_flag, l_trig_repeat_flag, l_source_code, l_media_name, l_freq_meaning, l_orig_csch_id,l_query_freq,l_query_freq_type,l_csch_end_date, l_campaign_id;
6210 CLOSE l_sch_det;
6211
6212 IF ( l_flag is not null and l_flag = 'Y' ) THEN
6213 result := 'COMPLETE:T' ;
6214 AMS_Utility_PVT.Create_Log (
6215 x_return_status => l_return_status,
6216 p_arc_log_used_by => 'CSCH',
6217 p_log_used_by_id => l_schedule_id,
6218 p_msg_data => 'CHECK_NOTIFICATION_PREFERENCE : NOTIFICATION PREFERENCE IS YES for schedule id '||l_schedule_id,
6219 p_msg_type => 'DEBUG'
6220 );
6221
6222 ELSE
6223 AMS_Utility_PVT.Create_Log (
6224 x_return_status => l_return_status,
6225 p_arc_log_used_by => 'CSCH',
6226 p_log_used_by_id => l_schedule_id,
6227 p_msg_data => 'CHECK_NOTIFICATION_PREFERENCE : NOTIFICATION PREFERENCE IS NO for schedule id '||l_schedule_id,
6228 p_msg_type => 'DEBUG'
6229 );
6230 result := 'COMPLETE:F';
6231 END IF;
6232
6233 IF ((l_triggerable_flag = 'N' AND l_trig_repeat_flag = 'Y') OR l_orig_csch_id IS NOT NULL) THEN
6234 --Repeating activity
6235 l_scheduler_frequency := to_number(WF_ENGINE.GetItemAttrText(
6236 itemtype => itemtype,
6237 itemkey => itemkey ,
6238 aname => 'AMS_SCHEDULER_FREQUENCY' ));
6239 IF (l_scheduler_frequency IS NULL) THEN
6240 -- this means that the repeating activity is in the Schedule Execution flow
6241 AMS_SCHEDULER_PVT.Schedule_Repeat (
6242 p_last_run_date => SYSDATE,
6243 p_frequency => l_query_freq,
6244 p_frequency_type => l_query_freq_type,
6245 x_next_run_date => l_scheduler_next_run_date,
6246 x_return_status => l_return_status,
6247 x_msg_count => l_msg_count,
6248 x_msg_data => l_msg_data);
6249 OPEN l_camp_det(l_campaign_id);
6250 FETCH l_camp_det INTO l_campaign_end_date;
6251 CLOSE l_camp_det;
6252
6253 IF (nvl(l_csch_end_date, l_campaign_end_date) <= l_scheduler_next_run_date)
6254 THEN
6255
6256 -- if this is the last executed activity Return true
6257 -- so that the notification will be sent
6258 -- since the workflow does not flow thru the Notification node if the activity is last one
6259 result := 'COMPLETE:T';
6260 ELSE
6261 -- returning False, as we dont want to send the Notification twice
6262 result := 'COMPLETE:F';
6263 END IF;
6264 --RETURN;
6265 ELSE
6266 l_new_schedule_id := TO_NUMBER(WF_ENGINE.GetItemAttrText(
6267 itemtype => itemtype,
6268 itemkey => itemkey ,
6269 aname => 'AMS_NEW_SCHEDULE_ID'));
6270
6271 IF l_new_Schedule_id IS NOT NULL
6272 THEN
6273
6274 OPEN l_new_sch_det(l_new_schedule_id);
6275 FETCH l_new_sch_det INTO l_new_schedule_name;
6276 CLOSE l_new_sch_det;
6277
6278 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
6279 itemkey => itemkey ,
6280 aname => 'AMS_NEW_SCHEDULE_NAME',
6281 avalue => l_new_schedule_name );
6282 END IF;
6283 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
6284 itemkey => itemkey ,
6285 aname => 'AMS_SCHEDULER_FREQ_MEANING',
6286 avalue => l_freq_meaning );
6287
6288 END IF;
6289 -- set the message 'Repeating Activity' from FND_MESSAGES
6290 FND_MESSAGE.Set_Name('AMS', 'AMS_REPEATING_ACTIVITY_PROMPT');
6291 ELSE
6292 -- set the message 'Activity' from FND_MESSAGES to the attribute AMS_ACTIVITY_DESCRIPTION using setItemAttrText
6293 FND_MESSAGE.Set_Name('AMS', 'AMS_ACTIVITY_PROMPT');
6294 END IF;
6295 l_msg_data := FND_MESSAGE.Get;
6296
6297 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
6298 itemkey => itemkey ,
6299 aname => 'AMS_ACTIVITY_DESCRIPTION',
6300 avalue => l_msg_data );
6301 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
6302 itemkey => itemkey ,
6303 aname => 'SOURCE_CODE',
6304 avalue => l_source_code );
6305 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
6306 itemkey => itemkey ,
6307 aname => 'AMS_CHANNEL_DESCRIPTION',
6308 avalue => l_media_name );
6309
6310 END IF;
6311
6312 -- CANCEL mode - Normal Process Execution
6313 IF (funcmode = 'CANCEL')
6314 THEN
6315 result := 'COMPLETE:F' ;
6316 RETURN;
6317 END IF;
6318
6319 -- TIMEOUT mode - Normal Process Execution
6320 IF (funcmode = 'TIMEOUT')
6321 THEN
6322 result := 'COMPLETE:F' ;
6323 RETURN;
6324 END IF;
6325 EXCEPTION
6326 WHEN OTHERS THEN
6327 wf_core.context(G_PKG_NAME,'CHECK_NOTIFICATION_PREFERENCE',itemtype,itemkey,actid,funcmode);
6328 raise ;
6329 END CHECK_NOTIFICATION_PREFERENCE;
6330
6331 END AMS_ScheduleRules_PVT ;