[Home] [Help]
PACKAGE BODY: APPS.AMS_SCHEDULERULES_PVT
Source
1 PACKAGE BODY AMS_ScheduleRules_PVT AS
2 /* $Header: amsvsbrb.pls 120.31.12010000.2 2008/08/08 11:33:34 amlal 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 *
2022 FROM ams_ctds
2023 WHERE ctd_id = p_ctd_id;
2024
2025 l_status_code VARCHAR2(30);
2026 l_activity_type_code VARCHAR2(30);
2027 l_activity_id NUMBER ;
2028 l_marketing_med_id NUMBER ;
2029 l_schedule_status_code VARCHAR2(30) := AMS_Utility_PVT.get_system_status_code(p_user_status_id) ;
2030 l_custom_setup_id NUMBER;
2031 l_cover_letter_id NUMBER ;
2032
2033 l_msg_count NUMBER ;
2034 l_msg_data VARCHAR2(2000);
2035 l_cover_letter_ver_id NUMBER; -- soagrawa added 30-sep-2003 or 11.5.10
2036 l_printer_address VARCHAR2(255);
2037 l_fulfilment VARCHAR2(30);
2038 l_attr_available VARCHAR2(30);
2039 l_system_url VARCHAR2(4000); -- dbiswas added 26May05 for 11.5.10.RUP4
2040 l_pretty_url_id NUMBER; -- dbiswas added 30Aug06 for R12 bug 5477945
2041 l_ctd_id NUMBER;
2042 l_pretty_url_rec AMS_PRETTY_URL_PVT.pretty_url_rec_type;
2043 l_ctd_rec AMS_CTD_PVT.ctd_rec_type;
2044
2045 x_status_code VARCHAR2(30);
2046
2047 BEGIN
2048
2049 OPEN c_old_status;
2050 FETCH c_old_status INTO l_old_status_id, l_object_version, l_start_time,
2051 l_timezone, l_activity_type_code, l_activity_id, l_marketing_med_id,l_custom_setup_id,l_cover_letter_id, l_printer_address ;
2052 CLOSE c_old_status;
2053
2054 IF l_old_status_id = p_user_status_id THEN
2055 RETURN;
2056 END IF;
2057
2058 -- Follwing code is modified by ptendulk on 10-Jul-2001
2059 -- The old procedure is replaced by new to check the type
2060 -- of the approval required as ams_object_attribute table is
2061 -- obsoleted now.
2062 AMS_Utility_PVT.check_new_status_change(
2063 p_object_type => 'CSCH',
2064 p_object_id => p_schedule_id,
2065 p_old_status_id => l_old_status_id,
2066 p_new_status_id => p_user_status_id,
2067 p_custom_setup_id => l_custom_setup_id,
2068 x_approval_type => l_approval_type,
2069 x_return_status => l_return_status
2070 );
2071
2072
2073 IF l_return_status <> FND_API.g_ret_sts_success THEN
2074 RAISE FND_API.g_exc_error;
2075 END IF;
2076
2077 -- dbiswas added the following pretty url check for bug 4472099
2078 IF l_schedule_status_code = 'SUBMITTED_BA'
2079 THEN
2080 IF (AMS_DEBUG_HIGH_ON) THEN
2081 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);
2082 END IF;
2083 IF (((l_activity_type_code = 'DIRECT_MARKETING') AND ((l_activity_id <> 20) AND (l_activity_id <> 460 )))
2084 OR((l_activity_type_code = 'BROADCAST') OR (l_activity_type_code = 'PUBLIC_RELATIONS') OR (l_activity_type_code = 'IN_STORE')))
2085 THEN
2086 IF (AMS_DEBUG_HIGH_ON) THEN
2087 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);
2088 END IF;
2089 OPEN c_system_url(p_schedule_id);
2090 FETCH c_system_url INTO l_system_url, l_pretty_url_id, l_ctd_id ;
2091 CLOSE c_system_url;
2092 IF(l_system_url IS NOT NULL) THEN
2093 -- dbiswas added the following checks for bug 5477945. Mandatory fields check for PrettyUrl
2094 OPEN c_pretty_url(l_pretty_url_id);
2095 FETCH c_pretty_url INTO l_pretty_url_rec;
2096 CLOSE c_pretty_url;
2097 IF (l_pretty_url_rec.pretty_url_id IS NOT NULL) THEN
2098 AMS_PRETTY_URL_PVT.CHECK_PU_MANDATORY_FIELDS(
2099 p_pretty_url_rec => l_pretty_url_rec,
2100 x_return_status => l_return_status);
2101 IF l_return_status <> FND_API.g_ret_sts_success THEN
2102 RAISE FND_API.g_exc_error;
2103 END IF;
2104 ELSE --Pretty URL rec not found, but system url exists. ERROR
2105 RAISE FND_API.g_exc_error;
2106 END IF;
2107
2108 -- Mandatory fields check for CTD
2109 OPEN c_ctd_items(l_ctd_id);
2110 FETCH c_ctd_items INTO l_ctd_rec;
2111 CLOSE c_ctd_items;
2112 IF (l_ctd_rec.ctd_id IS NOT NULL) THEN
2113 AMS_CTD_PVT.CHECK_MANDATORY_FIELDS(
2114 p_ctd_rec => l_ctd_rec,
2115 x_return_status => l_return_status
2116 );
2117 IF l_return_status <> FND_API.g_ret_sts_success THEN
2118 RAISE FND_API.g_exc_error;
2119 END IF ;
2120 ELSE --CTD Referenced in System url but does not exist. ERROR
2121 RAISE FND_API.g_exc_error;
2122 END IF ;
2123
2124 AMS_PRETTY_URL_PVT.IS_SYSTEM_URL_UNIQ(p_sys_url => l_system_url ,
2125 p_current_used_by_id => p_schedule_id,
2126 p_current_used_by_type => 'CSCH',
2127 x_return_status => l_return_status);
2128 IF l_return_status <> FND_API.g_ret_sts_success THEN
2129 RAISE FND_API.g_exc_error;
2130 END IF ;
2131 --
2132 END IF;
2133 END IF;
2134 END IF; -- end bug fix # 4472099
2135
2136 -- Schedule Can not go active unless the campaign is Active
2137 -- Schedule Camapign Rule 2/5
2138 IF l_schedule_status_code = 'ACTIVE' OR
2139 -- Following line is added by ptendulk on 06-Oct-2001
2140 l_schedule_status_code = 'AVAILABLE'
2141 THEN
2142
2143 -- anchaudh : calling validate activation rules api from R12 onwards; for any activity validation rule, going forward.
2144 validate_activation_rules(p_scheduleid => p_schedule_id , x_status_code => x_status_code);
2145 IF x_status_code <> FND_API.g_ret_sts_success THEN
2146 RAISE FND_API.g_exc_error;
2147 END IF ;
2148
2149 -- Following line of code is added by ptendulk on 08-Jul-2001
2150 --Check if the schedule has target group attached and generated.
2151 -- Following line is modified by ptendulk on 06-Oct-2001 .
2152 -- IF l_activity_type_code IN ('DIRECT_MARKETING','INTERNET','DEAL','TRADE_PROMOTION') THEN
2153 -- SALES related stuff added by asaha on 18th Feb, 2004
2154 IF (l_activity_type_code = 'DIRECT_MARKETING' OR l_activity_type_code = 'DIRECT_SALES') THEN
2155 -- following line added by soagrawa on 04-dec-2001
2156 -- modified by soagrawa on 15-aug-2002 for bug# 2515493 - added direct mail 480
2157 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
2158 IF FND_API.G_FALSE = Target_Group_Exist(p_schedule_id) THEN
2159 AMS_Utility_PVT.Error_Message('AMS_CSCH_NO_TARGET_GROUP');
2160 RAISE FND_API.g_exc_error;
2161 END IF ;
2162 END IF;
2163
2164 -- see if live cover letter version exists for email, fax, print
2165 -- soagrawa modified the way l_cover_letter_id is populated on 30-sep-2003 for 11.5.10
2166 OPEN c_cover_letter_det;
2167 FETCH c_cover_letter_det INTO l_cover_letter_ver_id;
2168 CLOSE c_cover_letter_det;
2169 -- soagrawa added 480 on 30-sep-2003 for 11.5.10
2170 IF (l_activity_id = 20 OR l_activity_id = 10 OR l_activity_id = 480)
2171 AND l_cover_letter_ver_id IS NULL THEN
2172 AMS_Utility_PVT.Error_Message('AMS_CSCH_NO_COVER_LETTER');
2173 RAISE FND_API.g_exc_error;
2174 END IF ;
2175
2176 -- soagrawa added printer validation on 18-nov-2003 for 11.5.10
2177 l_fulfilment := FND_PROFILE.Value('AMS_FULFILL_ENABLE_FLAG');
2178 IF l_activity_id = 480
2179 AND l_fulfilment <> 'N'
2180 AND l_printer_address IS NULL THEN
2181 AMS_Utility_PVT.Error_Message('AMS_CSCH_NO_PRINTER');
2182 RAISE FND_API.g_exc_error;
2183 END IF ;
2184
2185 END IF ;
2186
2187 --anchaudh : commenting out the call to AMS_ActProduct_PVT.IS_ALL_CONTENT_APPROVED for R12 .
2188
2189 --dbiswas added content validation for Collab midtab on 18-Mar-2004 for 11.5.10
2190 /*OPEN c_attr_available(l_custom_setup_id);
2191 FETCH c_attr_available INTO l_attr_available;
2192 CLOSE c_attr_available;
2193 IF (l_attr_available = 'Y') THEN
2194 AMS_ActProduct_PVT.IS_ALL_CONTENT_APPROVED (p_schedule_id => p_schedule_id,
2195 x_return_status => l_return_status);
2196 IF l_return_status <> 'Y'
2197 THEN AMS_Utility_PVT.Error_Message('AMS_CONTENT_NOT_APPROVED');
2198 RAISE FND_API.g_exc_error;
2199 END IF ;
2200 END IF;*/
2201
2202 --anchaudh: from R12 onwards, the above content valdation would be taken care of in the api : validate_activation_rules
2203
2204
2205 --dbiswas added pretty URL uniqueness check for pretty URL region on May 26, 2005 for 11.5.10.RUP4
2206 IF (AMS_DEBUG_HIGH_ON) THEN
2207 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);
2208 END IF;
2209 IF (((l_activity_type_code = 'DIRECT_MARKETING') AND ((l_activity_id <> 20) AND (l_activity_id <> 460 )))
2210 OR((l_activity_type_code = 'BROADCAST') OR (l_activity_type_code = 'PUBLIC_RELATIONS') OR (l_activity_type_code = 'IN_STORE')))
2211 THEN
2212 IF (AMS_DEBUG_HIGH_ON) THEN
2213 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);
2214 END IF;
2215 OPEN c_system_url(p_schedule_id);
2216 FETCH c_system_url INTO l_system_url, l_pretty_url_id, l_ctd_id;
2217 CLOSE c_system_url;
2218 IF(l_system_url IS NOT NULL) THEN
2219 -- dbiswas added the following checks for bug 5477945. Mandatory fields check for PrettyUrl
2220 OPEN c_pretty_url(l_pretty_url_id);
2221 FETCH c_pretty_url INTO l_pretty_url_rec;
2222 CLOSE c_pretty_url;
2223 IF (l_pretty_url_rec.pretty_url_id IS NOT NULL) THEN
2224 AMS_PRETTY_URL_PVT.CHECK_PU_MANDATORY_FIELDS(
2225 p_pretty_url_rec => l_pretty_url_rec,
2226 x_return_status => l_return_status);
2227 IF l_return_status <> FND_API.g_ret_sts_success THEN
2228 RAISE FND_API.g_exc_error;
2229 END IF;
2230 ELSE --Pretty URL rec not found, but system url exists. ERROR
2231 RAISE FND_API.g_exc_error;
2232 END IF;
2233
2234 -- Mandatory fields check for CTD
2235 OPEN c_ctd_items(l_ctd_id);
2236 FETCH c_ctd_items INTO l_ctd_rec;
2237 CLOSE c_ctd_items;
2238 IF (l_ctd_rec.ctd_id IS NOT NULL) THEN
2239 AMS_CTD_PVT.CHECK_MANDATORY_FIELDS(
2240 p_ctd_rec => l_ctd_rec,
2241 x_return_status => l_return_status
2242 );
2243 IF l_return_status <> FND_API.g_ret_sts_success THEN
2244 RAISE FND_API.g_exc_error;
2245 END IF ;
2246 ELSE --CTD Referenced in System url but does not exist. ERROR
2247 RAISE FND_API.g_exc_error;
2248 END IF ;
2249
2250 AMS_PRETTY_URL_PVT.IS_SYSTEM_URL_UNIQ(p_sys_url => l_system_url ,
2251 p_current_used_by_id => p_schedule_id,
2252 p_current_used_by_type => 'CSCH',
2253 x_return_status => l_return_status);
2254 IF l_return_status <> FND_API.g_ret_sts_success THEN
2255 RAISE FND_API.g_exc_error;
2256 END IF ;
2257 END IF;
2258 END IF;
2259
2260
2261 IF l_marketing_med_id IS NULL THEN
2262 IF l_activity_type_code <> 'DIRECT_MARKETING' AND
2263 l_activity_type_code <> 'INTERNET' AND
2264 -- Following line of code is added by ptendulk on 12-Jun-2001
2265 -- Mktg medium is not mandatory for event type schedules
2266 l_activity_type_code <> 'EVENTS' AND
2267 -- Following Line of code is added by ptendulk on 06-Oct-2001
2268 l_activity_type_code <> 'DEAL' AND
2269 l_activity_type_code <> 'TRADE_PROMOTION' AND
2270 -- Following Line of code is added by asaha on 09-Sep-2003 for Sales Channel
2271 l_activity_type_code <> 'DIRECT_SALES'
2272 THEN
2273 AMS_Utility_PVT.Error_Message('AMS_CAMP_CHANNEL_REQUIRED');
2274 RAISE FND_API.g_exc_error;
2275 END IF ;
2276 END IF ;
2277
2278 OPEN c_camp_status ;
2279 FETCH c_camp_status INTO l_status_code ;
2280 CLOSE c_camp_status;
2281
2282 IF l_status_code <> 'ACTIVE' THEN
2283 AMS_Utility_PVT.Error_Message('AMS_CSCH_CAMP_NO_ACTIVE');
2284 RAISE FND_API.g_exc_error;
2285 END IF ;
2286 END IF ; -- Active or Available
2287
2288
2289 IF l_approval_type = 'BUDGET' THEN
2290
2291 /* vmodur 19-Dec-2005 */
2292 AMS_Approval_PVT.Must_Preview(
2293 p_activity_id => p_schedule_id,
2294 p_activity_type => 'CSCH',
2295 p_approval_type => 'BUDGET',
2296 p_act_budget_id => null,
2297 p_requestor_id => AMS_Utility_PVT.get_resource_id(FND_GLOBAL.user_id),
2298 x_must_preview => l_start_wf_process,
2299 x_return_status => l_return_status);
2300
2301 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2302 RAISE FND_API.G_EXC_ERROR;
2303 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2304 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2305 END IF;
2306
2307 /* vmodur 19-Dec-2005 */
2308 IF (l_start_wf_process = 'Y') THEN -- If the user is not the approver and budget approval reqd
2309 -- start budget approval process
2310 l_new_status_id := AMS_Utility_PVT.get_default_user_status(
2311 'AMS_CAMPAIGN_SCHEDULE_STATUS',
2312 'SUBMITTED_BA'
2313 );
2314 l_deny_status_id := AMS_Utility_PVT.get_default_user_status(
2315 'AMS_CAMPAIGN_SCHEDULE_STATUS',
2316 'DENIED_BA'
2317 );
2318
2319 AMS_Approval_PVT.StartProcess(
2320 p_activity_type => 'CSCH',
2321 p_activity_id => p_schedule_id,
2322 p_approval_type => l_approval_type,
2323 p_object_version_number => l_object_version,
2324 p_orig_stat_id => l_old_status_id,
2325 p_new_stat_id => p_user_status_id,
2326 p_reject_stat_id => l_deny_status_id,
2327 p_requester_userid => AMS_Utility_PVT.get_resource_id(FND_GLOBAL.user_id),
2328 p_workflowprocess => 'AMS_APPROVAL',
2329 p_item_type => 'AMSAPRV'
2330 );
2331 ELSE -- If user equals approver and budget approval reqd
2332 IF (AMS_DEBUG_HIGH_ON) THEN
2333 AMS_Utility_PVT.Debug_Message('No need to start Workflow Process for Approval, Status Code ' || l_schedule_status_code );
2334 END IF;
2335 -- Following budget line api call added by soagrawa on 25-oct-2002
2336 -- for enhancement # 2445453
2337
2338 IF l_schedule_status_code = 'ACTIVE' THEN
2339 OZF_BudgetApproval_PVT.budget_request_approval(
2340 p_init_msg_list => FND_API.G_FALSE
2341 , p_api_version => 1.0
2342 , p_commit => FND_API.G_False
2343 , x_return_status => l_return_status
2344 , x_msg_count => l_msg_count
2345 , x_msg_data => l_msg_data
2346 , p_object_type => 'CSCH'
2347 , p_object_id => p_schedule_id
2348 --, x_status_code =>
2349 );
2350
2351 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2352 RAISE FND_API.G_EXC_ERROR;
2353 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2354 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2355 END IF;
2356 END IF; -- Active
2357 l_new_status_id := p_user_status_id;
2358
2359 END IF; -- IF budget approval reqd
2360
2361 ELSE -- No BUDGET Approval
2362
2363 IF (AMS_DEBUG_HIGH_ON) THEN
2364 AMS_Utility_PVT.Debug_Message('No Approval' || l_schedule_status_code );
2365 END IF;
2366 -- Following budget line api call added by soagrawa on 25-oct-2002
2367 -- for enhancement # 2445453
2368
2369 IF l_schedule_status_code = 'ACTIVE' THEN
2370 OZF_BudgetApproval_PVT.budget_request_approval(
2371 p_init_msg_list => FND_API.G_FALSE
2372 , p_api_version => 1.0
2373 , p_commit => FND_API.G_False
2374 , x_return_status => l_return_status
2375 , x_msg_count => l_msg_count
2376 , x_msg_data => l_msg_data
2377 , p_object_type => 'CSCH'
2378 , p_object_id => p_schedule_id
2379 --, x_status_code =>
2380 );
2381
2382 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2383 RAISE FND_API.G_EXC_ERROR;
2384 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2385 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2386 END IF;
2387 END IF;
2388 l_new_status_id := p_user_status_id;
2389
2390 END IF; -- If Budget
2391
2392 --insert_log_mesg('Anirban got value of asn_group_id in api Update_Schedule_Status in amsvsbrb.pls as :'||p_asn_group_id);
2393
2394 update_status(p_schedule_id => p_schedule_id,
2395 p_new_status_id => l_new_status_id,
2396 p_new_status_code => AMS_Utility_PVT.get_system_status_code(l_new_status_id),
2397 p_asn_group_id => p_asn_group_id -- anchaudh added for leads bug.
2398 );
2399
2400 END Update_Schedule_Status;
2401
2402
2403
2404 --========================================================================
2405 -- PROCEDURE
2406 -- Create_list
2407 --
2408 -- PURPOSE
2409 -- This api is called after the creation of the Direct marketing schedules
2410 -- to create the default target group for the schedule. User can go to the
2411 -- target group screen to modify the details.
2412 --
2413 -- NOTE
2414 -- The list of Type Target is created in list header and the association is
2415 -- created in the ams_act_lists table.
2416 --
2417 -- HISTORY
2418 -- 18-May-2001 ptendulk Created.
2419 -- 18-Aug-2001 ptendulk Modified the Target group name
2420 --
2421 --========================================================================
2422 PROCEDURE Create_list
2423 (p_schedule_id IN NUMBER,
2424 p_schedule_name IN VARCHAR2,
2425 p_owner_id IN NUMBER)
2426 IS
2427 l_return_status VARCHAR2(1) ;
2428 l_msg_count NUMBER ;
2429 l_msg_data VARCHAR2(2000);
2430 l_api_version NUMBER := 1.0 ;
2431
2432 l_list_header_rec AMS_ListHeader_Pvt.list_header_rec_type;
2433 l_act_list_rec AMS_Act_List_Pvt.act_list_rec_type;
2434 l_list_header_id NUMBER ;
2435 l_act_list_header_id NUMBER ;
2436
2437 l_tmp NUMBER ;
2438
2439 BEGIN
2440 NULL;
2441 /* Following code is modified by ptendulk on 25-Oct-2001
2442 As we don't have to create the target group for schedules at
2443 schedule creation.
2444 -- AMS_ListHeader_PVT.init_listheader_rec(l_list_header_rec);
2445 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');
2446 l_list_header_rec.list_type := 'TARGET';
2447 -- Have to be removed.
2448 l_list_header_rec.list_source_type := 'PERSON_LIST' ;
2449 l_list_header_rec.owner_user_id := p_owner_id;
2450 AMS_ListHeader_PVT.Create_Listheader
2451 ( p_api_version => 1.0,
2452 p_init_msg_list => FND_API.g_false,
2453 p_commit => FND_API.g_false,
2454 p_validation_level => FND_API.g_valid_level_full,
2455
2456 x_return_status => l_return_status,
2457 x_msg_count => l_msg_count,
2458 x_msg_data => l_msg_data,
2459 p_listheader_rec => l_list_header_rec,
2460 x_listheader_id => l_list_header_id
2461 );
2462
2463 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2464 RAISE FND_API.G_EXC_ERROR;
2465 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2466 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2467 END IF;
2468
2469 l_act_list_rec.list_header_id := l_list_header_id;
2470 l_act_list_rec.list_used_by := 'CSCH';
2471 l_act_list_rec.list_used_by_id := p_schedule_id ;
2472 l_act_list_rec.list_act_type := 'TARGET';
2473
2474 AMS_Act_List_PVT.Create_Act_List(
2475 p_api_version_number => 1.0,
2476 p_init_msg_list => FND_API.g_false,
2477 p_commit => FND_API.g_false,
2478 p_validation_level => FND_API.g_valid_level_full,
2479 x_return_status => l_return_status,
2480 x_msg_count => l_msg_count,
2481 x_msg_data => l_msg_data,
2482 p_act_list_rec => l_act_list_rec ,
2483 x_act_list_header_id => l_act_list_header_id
2484 ) ;
2485
2486 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2487 RAISE FND_API.G_EXC_ERROR;
2488 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2489 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2490 END IF;
2491
2492 */
2493 END Create_list;
2494
2495
2496
2497 --========================================================================
2498 -- PROCEDURE
2499 -- Create_Schedule_Access
2500 --
2501 -- PURPOSE
2502 -- This api is called in Create schedule api to give the access for
2503 -- schedule to the team members of the campaign.
2504 --
2505 -- NOTE
2506 --
2507 -- HISTORY
2508 -- 11-Sep-2001 ptendulk Created.
2509 --
2510 --========================================================================
2511 PROCEDURE Create_Schedule_Access(p_schedule_id IN NUMBER,
2512 p_campaign_id IN NUMBER,
2513 p_owner_id IN NUMBER,
2514 p_init_msg_list IN VARCHAR2,
2515 p_commit IN VARCHAR2,
2516 p_validation_level IN NUMBER,
2517
2518 x_return_status OUT NOCOPY VARCHAR2,
2519 x_msg_count OUT NOCOPY NUMBER,
2520 x_msg_data OUT NOCOPY VARCHAR2
2521 )
2522 IS
2523
2524 CURSOR c_access_det IS
2525 SELECT *
2526 FROM ams_act_access
2527 WHERE arc_act_access_to_object = 'CAMP'
2528 AND act_access_to_object_id = p_campaign_id ;
2529 l_access_det c_access_det%ROWTYPE;
2530
2531 l_access_rec AMS_Access_Pvt.access_rec_type ;
2532 l_dummy_id NUMBER ;
2533
2534 BEGIN
2535
2536 l_access_rec.act_access_to_object_id := p_schedule_id ;
2537 l_access_rec.arc_act_access_to_object := 'CSCH' ;
2538 l_access_rec.user_or_role_id := p_owner_id ;
2539 l_access_rec.arc_user_or_role_type := 'USER' ;
2540 l_access_rec.owner_flag := 'Y' ;
2541 l_access_rec.delete_flag := 'N' ;
2542 l_access_rec.admin_flag := 'Y' ;
2543
2544 AMS_Access_Pvt.Create_Access(
2545 p_api_version => 1,
2546 p_init_msg_list => p_init_msg_list,
2547 p_commit => p_commit,
2548 p_validation_level => p_validation_level,
2549
2550 x_return_status => x_return_status,
2551 x_msg_count => x_msg_count,
2552 x_msg_data => x_msg_data,
2553
2554 p_access_rec => l_access_rec,
2555 x_access_id => l_dummy_id
2556 );
2557 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2558 RAISE FND_API.G_EXC_ERROR;
2559 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2560 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2561 END IF;
2562
2563
2564 OPEN c_access_det ;
2565 LOOP
2566 FETCH c_access_det INTO l_access_det;
2567 EXIT WHEN c_access_det%NOTFOUND ;
2568
2569 IF l_access_det.arc_user_or_role_type = 'USER'
2570 AND l_access_det.user_or_role_id = p_owner_id
2571 THEN
2572 -- Entry of user is already gone is dont do anything
2573 NULL ;
2574 ELSE
2575 -- Create Access for the team /owner
2576 l_access_rec.owner_flag := 'N' ;
2577 l_access_rec.user_or_role_id := l_access_det.user_or_role_id ;
2578 l_access_rec.arc_user_or_role_type := l_access_det.arc_user_or_role_type ;
2579 l_access_rec.delete_flag := l_access_det.delete_flag ;
2580 --l_access_rec.admin_flag := l_access_rec.admin_flag ;
2581 l_access_rec.admin_flag := l_access_det.admin_flag ;--anchaudh: changed rec type to l_access_det.
2582
2583 AMS_Access_Pvt.Create_Access(
2584 p_api_version => 1,
2585 p_init_msg_list => p_init_msg_list,
2586 p_commit => p_commit,
2587 p_validation_level => p_validation_level,
2588
2589 x_return_status => x_return_status,
2590 x_msg_count => x_msg_count,
2591 x_msg_data => x_msg_data,
2592
2593 p_access_rec => l_access_rec,
2594 x_access_id => l_dummy_id
2595 );
2596 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2597 CLOSE c_access_det;
2598 RAISE FND_API.G_EXC_ERROR;
2599 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2600 CLOSE c_access_det;
2601 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2602 END IF;
2603
2604 END IF ;
2605
2606 END LOOP;
2607 CLOSE c_access_det ;
2608
2609 END Create_Schedule_Access ;
2610
2611
2612
2613
2614
2615 --========================================================================
2616 -- PROCEDURE
2617 -- get_user_id
2618 --
2619 -- PURPOSE
2620 -- This api will take a resource id and give the corresponding user_id
2621 --
2622 -- NOTE
2623 --
2624 -- HISTORY
2625 -- 19-mar-2002 soagrawa Created
2626 --========================================================================
2627
2628
2629 FUNCTION get_user_id (
2630 p_resource_id IN NUMBER
2631 )
2632 RETURN NUMBER
2633 IS
2634 l_user_id NUMBER;
2635
2636 CURSOR c_user IS
2637 SELECT user_id
2638 FROM ams_jtf_rs_emp_v
2639 WHERE resource_id = p_resource_id;
2640 BEGIN
2641 OPEN c_user;
2642 FETCH c_user INTO l_user_id;
2643 IF c_user%NOTFOUND THEN
2644 l_user_id := -1;
2645 -- Adding an error message will cause the function
2646 -- to violate the WNDS pragma, preventing it from
2647 -- being able to be called from a SQL statement.
2648 END IF;
2649 CLOSE c_user;
2650
2651 RETURN l_user_id;
2652 END get_user_id;
2653
2654
2655
2656 --========================================================================
2657 -- PROCEDURE
2658 -- write_interaction
2659 --
2660 -- PURPOSE
2661 -- This api is called in update_Status to write to interaction history
2662 -- if it was DIRECT_MARKETING Direct Mail
2663 --
2664 -- NOTE
2665 --
2666 -- HISTORY
2667 -- 19-mar-2002 soagrawa Created to log interactions for
2668 -- DIRECT_MARKETING MAIL
2669 -- 27-may-2003 soagrawa Fixed NI issue about result of interaction bug# 2978948
2670 --========================================================================
2671
2672 PROCEDURE write_interaction(
2673 p_schedule_id IN NUMBER
2674 )
2675
2676 IS
2677
2678 -- CURSOR:
2679 -- get the target grp for this CSCH
2680 -- get the list entries from that target group
2681 -- get the party_id for those list entries
2682
2683 CURSOR c_parties_det IS
2684 SELECT party_id
2685 FROM ams_list_entries
2686 WHERE list_header_id =
2687 (SELECT list_header_id
2688 FROM ams_act_lists
2689 WHERE list_used_by = 'CSCH'
2690 AND list_act_type = 'TARGET'
2691 AND list_used_by_id = p_schedule_id)
2692 AND enabled_flag = 'Y';
2693
2694
2695 CURSOR c_sch_det IS
2696 SELECT start_date_time, end_date_time, owner_user_id, source_code
2697 FROM ams_campaign_schedules_b
2698 WHERE schedule_id = p_schedule_id;
2699
2700 CURSOR c_media_item_id IS
2701 SELECT JTF_IH_MEDIA_ITEMS_S1.NEXTVAL
2702 FROM dual;
2703
2704 CURSOR c_interactions_id IS
2705 SELECT jtf_ih_interactions_s1.NEXTVAL
2706 FROM dual;
2707
2708 CURSOR c_activities_id IS
2709 SELECT JTF_IH_ACTIVITIES_S1.NEXTVAL
2710 FROM dual;
2711
2712 l_interaction_rec JTF_IH_PUB.interaction_rec_type;
2713 l_activities JTF_IH_PUB.activity_tbl_type;
2714 l_activity_rec JTF_IH_PUB.activity_rec_type;
2715 l_media_rec JTF_IH_PUB.media_rec_type;
2716 l_interaction_id NUMBER;
2717 l_media_id NUMBER;
2718 l_party_id NUMBER;
2719 l_schedule_start_time DATE;
2720 l_schedule_end_time DATE;
2721 l_schedule_owner_id NUMBER;
2722 l_schedule_source_code VARCHAR2(30);
2723
2724 l_return_status VARCHAR2(1);
2725 l_msg_count NUMBER;
2726 l_msg_data VARCHAR2(2000);
2727 l_user_id NUMBER;
2728
2729 BEGIN
2730
2731 OPEN c_sch_det;
2732 FETCH c_sch_det INTO l_schedule_start_time, l_schedule_end_time, l_schedule_owner_id, l_schedule_source_code;
2733 CLOSE c_sch_det;
2734
2735 l_user_id := get_user_id(p_resource_id => l_schedule_owner_id);
2736
2737 -- populate media_rec
2738 OPEN c_media_item_id;
2739 FETCH c_media_item_id INTO l_media_rec.media_id ;
2740 CLOSE c_media_item_id;
2741 -- l_media_rec.media_id := JTF_IH_MEDIA_ITEMS_S1.nextval;
2742 l_media_rec.end_date_time := l_schedule_end_time ;
2743 l_media_rec.start_date_time := l_schedule_start_time ;
2744 l_media_rec.media_item_type := 'MAIL' ;
2745
2746 -- create media_rec
2747 JTF_IH_PUB.Create_MediaItem
2748 (
2749 p_api_version => 1.0,
2750 p_init_msg_list => FND_API.g_false,
2751 p_commit => FND_API.g_false,
2752 -- p_resp_appl_id => l_resp_appl_id,
2753 -- p_resp_id => l_resp_id,
2754 p_user_id => l_user_id,
2755 -- p_login_id => l_login_id,
2756 x_return_status => l_return_status,
2757 x_msg_count => l_msg_count,
2758 x_msg_data => l_msg_data,
2759 p_media_rec => l_media_rec,
2760 x_media_id => l_media_id
2761 );
2762 IF l_return_status <> FND_API.g_ret_sts_success THEN
2763 RAISE FND_API.g_exc_error;
2764 RETURN;
2765 END IF;
2766
2767 IF (AMS_DEBUG_HIGH_ON) THEN
2768 AMS_Utility_PVT.debug_message('Write interaction: created media item ');
2769 END IF;
2770
2771 -- loop for each party id found
2772 OPEN c_parties_det;
2773 LOOP
2774 FETCH c_parties_det INTO l_party_id ;
2775 EXIT WHEN c_parties_det%NOTFOUND ;
2776
2777 IF (AMS_DEBUG_HIGH_ON) THEN
2778
2779
2780
2781 AMS_Utility_PVT.debug_message('Write interaction: looping for party id ');
2782
2783 END IF;
2784
2785 -- populate interaction record
2786 /*OPEN c_interactions_id;
2787 FETCH c_interactions_id INTO l_interaction_id ;
2788 CLOSE c_interactions_id;*/
2789 -- l_interaction_id := jtf_ih_interactions_s1.nextval ;
2790
2791 l_interaction_rec.interaction_id := l_interaction_id ;
2792 l_interaction_rec.end_date_time := l_schedule_end_time ;
2793 l_interaction_rec.start_date_time := l_schedule_start_time ;
2794 l_interaction_rec.handler_id := 530 ;
2795 l_interaction_rec.outcome_id := 10 ; -- request processed
2796 -- soagrawa added on 27-may-2003 for NI interaction issue bug# 2978948
2797 l_interaction_rec.result_id := 8 ; -- sent
2798 l_interaction_rec.resource_id := l_schedule_owner_id ;
2799 l_interaction_rec.party_id := l_party_id ; -- looping for all party ids in the list
2800 l_interaction_rec.object_id := p_schedule_id ;
2801 l_interaction_rec.object_type := 'CSCH';
2802 l_interaction_rec.source_code := l_schedule_source_code;
2803
2804 -- populate activity record
2805 /*OPEN c_activities_id;
2806 FETCH c_activities_id INTO l_activity_rec.activity_id ;
2807 CLOSE c_activities_id;*/
2808 -- l_activity_rec.activity_id := JTF_IH_ACTIVITIES_S1.nextval ;
2809 l_activity_rec.end_date_time := l_schedule_end_time ;
2810 l_activity_rec.start_date_time := l_schedule_start_time ;
2811 l_activity_rec.media_id := l_media_id ;
2812 l_activity_rec.action_item_id := 3 ; -- collateral
2813 --l_activity_rec.interaction_id := l_interaction_id ;
2814 l_activity_rec.outcome_id := 10 ; -- request processed
2815 l_activity_rec.result_id := 8 ; -- sent
2816 l_activity_rec.action_id := 5 ; -- sent
2817
2818 -- populate activity table with the activity record
2819 l_activities(1) := l_activity_rec;
2820
2821 -- create interaction
2822 JTF_IH_PUB.Create_Interaction
2823 (
2824 p_api_version => 1.0,
2825 p_init_msg_list => FND_API.g_false,
2826 p_commit => FND_API.g_false,
2827 -- p_resp_appl_id => l_resp_appl_id, -- 530
2828 -- p_resp_id => l_resp_id, -- fnd global
2829 p_user_id => l_user_id,
2830 -- p_login_id => l_login_id,
2831 x_return_status => l_return_status,
2832 x_msg_count => l_msg_count,
2833 x_msg_data => l_msg_data,
2834 p_interaction_rec => l_interaction_rec,
2835 p_activities => l_activities
2836 );
2837 IF l_return_status <> FND_API.g_ret_sts_success THEN
2838 RAISE FND_API.g_exc_error;
2839 RETURN;
2840 END IF;
2841
2842 END LOOP;
2843 CLOSE c_parties_det;
2844
2845
2846
2847
2848 END write_interaction;
2849
2850
2851
2852
2853
2854
2855
2856
2857 --========================================================================
2858 -- PROCEDURE
2859 -- Update_Status
2860 --
2861 -- PURPOSE
2862 -- This api is called in Update schedule api (and in approvals' api)
2863 --
2864 -- NOTE
2865 --
2866 -- HISTORY
2867 -- 26-Sep-2001 soagrawa Created.
2868 -- 05-dec-2001 soagrawa Added code for updating status of the related event
2869 -- for schedules of type event
2870 -- 08-mar-2002 soagrawa Added code to call an events api if changing event schedule
2871 -- status to closed :fix for bug# 2254382
2872 -- 19-mar-2002 soagrawa Added code to fix bug# 2263166 regding TGRP purging
2873 -- 14-may-2002 soagrawa Modified for status of new schedule eblast
2874 -- 08-jul-2002 soagrawa Fixed content related bug# 2442744
2875 -- 26-jul-2002 soagrawa Fixed order of template approval and call to submit_conc_request
2876 -- for bug# 2463596
2877 -- 24-sep-2002 soagrawa Fixed condition for call to process_leads, refer to bug# 2582436
2878 -- 26-may-2003 anchaudh Called list api Update_Prev_contacted_count
2879 -- 24-Aug-2003 ptendulk Modified to call business event on the schedule activation
2880 -- 06-Sep-2003 ptendulk Modified the workflow parameter name to SCHEDULE_ID from AMS_SCHEDULE_ID
2881 -- 26-sep-2003 soagrawa Modified to accommodate triggers and repeating schedules
2882 -- 17-Mar-2005 spendem call the API to raise business event on status change as per enh # 3805347
2883 --========================================================================================================
2884 PROCEDURE update_status( p_schedule_id IN NUMBER,
2885 p_new_status_id IN NUMBER,
2886 p_new_status_code IN VARCHAR2,
2887 p_asn_group_id IN VARCHAR2 DEFAULT NULL -- anchaudh added for leads bug.
2888 )
2889 IS
2890
2891 CURSOR c_sch_det IS
2892 SELECT start_date_time, timezone_id,
2893 activity_type_code, activity_id,
2894 related_event_id -- soagrawa 05-dec-2001 - now also retrieving related event id.
2895 -- so as to update the event's status
2896 , user_status_id, status_code -- soagrawa 19-mar-2002
2897 , source_code -- soagrawa 22-oct-2002 for bug# 2594717
2898 , NVL(triggerable_flag,'N') -- soagrawa 26-sep-2003 for trigger and repeating schedule code change
2899 , NVL(trig_repeat_flag,'N') -- soagrawa 26-sep-2003 for trigger and repeating schedule code change
2900 , orig_csch_id -- soagrawa 26-sep-2003 for trigger and repeating schedule code change
2901 , owner_user_id -- vmodur
2902 , campaign_id -- vmodur
2903 FROM ams_campaign_schedules_b
2904 WHERE schedule_id = p_schedule_id;
2905
2906 l_source_code VARCHAR2(30);
2907 l_new_status_id NUMBER;
2908 l_activity_type_code VARCHAR2(30);
2909 l_activity_id NUMBER ;
2910 l_start_time DATE;
2911 l_sys_start_time DATE;
2912 l_timezone NUMBER;
2913 l_related_event_id NUMBER;
2914 l_old_status_id NUMBER;
2915 l_old_status_code VARCHAR2(30);
2916 l_triggerable_flag VARCHAR2(1); -- soagrawa 26-sep-2003 for trigger and repeating schedule code change
2917 l_trig_repeat_flag VARCHAR2(1); -- soagrawa 26-sep-2003 for trigger and repeating schedule code change
2918 l_orig_csch_id NUMBER; -- soagrawa 26-sep-2003 for trigger and repeating schedule code change
2919
2920 /* REMOVED BY SOAGRAWA ON 26-SEP-2003 : NOT BEING USED ANY MORE
2921 -- the following cursor and vars added by soagrawa
2922 -- on 19-mar-2002 for bug# 2263166
2923
2924 CURSOR c_tgrp_det
2925 IS SELECT list_header_id
2926 FROM ams_act_lists la
2927 WHERE list_act_type = 'TARGET'
2928 AND list_used_by = 'CSCH'
2929 AND list_used_by_id = p_schedule_id
2930 AND EXISTS (SELECT *
2931 FROM ams_list_entries le
2932 WHERE le.list_header_id = la.list_header_id) ;
2933 */
2934
2935 l_return_status VARCHAR2(1);
2936 l_msg_count NUMBER;
2937 l_msg_data VARCHAR2(2000);
2938 l_tgrp_id NUMBER;
2939
2940 /* REMOVED BY SOAGRAWA ON 26-SEP-2003 : NOT BEING USED ANY MORE
2941 -- the following cursor and variables added by soagrawa on 14-may-2002
2942 -- for approving item
2943 CURSOR c_template_det (p_content_item_id NUMBER)
2944 IS SELECT ver.citem_version_id, ver.object_version_number, ci.content_item_status
2945 FROM ibc_citem_versions_vl ver
2946 , ibc_content_items ci
2947 WHERE ci.content_item_id = p_content_item_id
2948 AND ci.content_item_id = ver.content_item_id;
2949 */
2950
2951 l_citem_ver_id NUMBER;
2952 l_RESOURCE_id NUMBER;
2953 p_num_asn_group_id number;
2954 l_obj_ver_num NUMBER;
2955 l_content_item_status VARCHAR2(20);
2956 l_def_flag VARCHAR2(1);
2957
2958 l_parameter_list WF_PARAMETER_LIST_T;
2959 l_new_item_key VARCHAR2(30);
2960 l_owner_user_id NUMBER;
2961 l_campaign_id NUMBER;
2962
2963 l_user_id NUMBER;
2964 l_resp_id NUMBER;
2965 l_resp_appl_id NUMBER;
2966 l_evo_rec AMS_EVENTOFFER_PVT.evo_rec_type; -- vmodur
2967
2968 -- dbiswas added the following cursor for bug 2852078
2969 CURSOR c_is_default_flag_on (p_user_status_id NUMBER)
2970 IS
2971 SELECT default_flag
2972 FROM ams_user_statuses_b
2973 WHERE user_status_id = p_user_status_id;
2974
2975 BEGIN
2976
2977 l_user_id := FND_GLOBAL.USER_ID;
2978 l_resp_id := FND_GLOBAL.RESP_ID;
2979 l_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
2980
2981 -- soagrawa on 19-mar-2002
2982 -- moved the cursor data retrieval from after update to before update
2983 OPEN c_sch_det;
2984 FETCH c_sch_det INTO l_start_time, l_timezone, l_activity_type_code, l_activity_id, l_related_event_id
2985 , l_old_status_id, l_old_status_code, l_source_code
2986 , l_triggerable_flag, l_trig_repeat_flag, l_orig_csch_id, l_owner_user_id, l_campaign_id;
2987 CLOSE c_sch_det;
2988
2989 UPDATE ams_campaign_schedules_b
2990 SET user_status_id = p_new_status_id,
2991 status_code = p_new_status_code, -- AMS_Utility_PVT.get_system_status_code(p_new_status_id),
2992 status_date = SYSDATE,
2993 object_version_number = object_version_number + 1,
2994 last_update_date = SYSDATE
2995 WHERE schedule_id = p_schedule_id;
2996
2997 -- call to api to raise business event, as per enh # 3805347
2998 RAISE_BE_ON_STATUS_CHANGE(p_obj_id => p_schedule_id,
2999 p_obj_type => 'CSCH',
3000 p_old_status_code => l_old_status_code,
3001 p_new_status_code => p_new_status_code );
3002
3003
3004 OPEN c_is_default_flag_on(p_new_status_id);
3005 FETCH c_is_default_flag_on INTO l_def_flag;
3006 CLOSE c_is_default_flag_on;
3007
3008 IF (p_new_status_code = 'ACTIVE' OR p_new_status_code = 'AVAILABLE')
3009 THEN
3010 IF ((l_old_status_code <> 'ON_HOLD' AND l_old_status_code <> 'AVAILABLE')
3011 -- Don't submit process if the status is updated from avail as in avail status
3012 -- there will be process created already
3013 AND l_def_flag = 'Y')
3014 THEN
3015
3016 -- soagrawa 26-sep-2003 Modified logic and code for trigger and repeating schedule code change
3017
3018 -- Logic:
3019 -- If it is a triggerable schedule, do nothing.
3020 -- 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.
3021 -- Otherwise, raise business event for schedule execution with start date of the schedule.
3022
3023 IF l_triggerable_flag <> 'Y' -- not triggerable
3024 THEN
3025 IF l_trig_repeat_flag = 'Y' AND l_orig_csch_id IS NULL -- repeating csch parent instance
3026 THEN
3027 l_new_item_key := p_schedule_id || 'RPT' || TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
3028 l_parameter_list := WF_PARAMETER_LIST_T();
3029 wf_event.AddParameterToList(p_name => 'SCHEDULE_ID',
3030 p_value => p_schedule_id,
3031 p_parameterlist => l_parameter_list);
3032
3033 AMS_UTILITY_PVT.Convert_Timezone(
3034 p_init_msg_list => FND_API.G_TRUE,
3035 x_return_status => l_return_status,
3036 x_msg_count => l_msg_count,
3037 x_msg_data => l_msg_data,
3038
3039 p_user_tz_id => l_timezone,
3040 p_in_time => l_start_time,
3041 p_convert_type => 'SYS',
3042
3043 x_out_time => l_sys_start_time
3044 );
3045
3046 -- If any errors happen let start time be sysdate
3047 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3048 l_start_time := SYSDATE;
3049 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3050 l_start_time := SYSDATE;
3051 END IF;
3052
3053 AMS_Utility_PVT.debug_message('Raise Business event for Repeating Schedule');
3054 WF_EVENT.Raise
3055 ( p_event_name => 'oracle.apps.ams.campaign.RepeatScheduleEvent',
3056 p_event_key => l_new_item_key,
3057 p_parameters => l_parameter_list,
3058 p_send_date => l_sys_start_time);
3059
3060 ELSE -- not repeating csch parent instance
3061 l_new_item_key := p_schedule_id || TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
3062 l_parameter_list := WF_PARAMETER_LIST_T();
3063
3064 wf_event.AddParameterToList(p_name => 'SCHEDULE_ID',
3065 p_value => p_schedule_id,
3066 p_parameterlist => l_parameter_list);
3067 --ANCHAUDH starts modification for the leads bug.
3068 IF ((p_asn_group_id IS NOT NULL) AND (p_asn_group_id <> FND_API.g_miss_char)) THEN
3069 p_num_asn_group_id := to_number(p_asn_group_id);
3070 --insert_log_mesg('Anirban passing value of the param in WF, in amsvsbrb.pls as :'||p_num_asn_group_id);
3071
3072 wf_event.AddParameterToList(p_name => 'ASN_GROUP_ID',
3073 p_value => p_num_asn_group_id,
3074 p_parameterlist => l_parameter_list);
3075 ELSE
3076 p_num_asn_group_id := to_number('9999');
3077 wf_event.AddParameterToList(p_name => 'ASN_GROUP_ID',
3078 p_value => p_num_asn_group_id,
3079 p_parameterlist => l_parameter_list);
3080
3081 --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);
3082
3083 END IF;
3084
3085
3086 l_RESOURCE_id := AMS_Utility_PVT.get_resource_id(FND_GLOBAL.user_id);
3087 --insert_log_mesg('Anirban passing value of l_RESOURCE_id in WF, in amsvsbrb.pls as :'||l_RESOURCE_id);
3088 wf_event.AddParameterToList(p_name => 'ASN_RESOURCE_ID',
3089 p_value => l_RESOURCE_id,
3090 p_parameterlist => l_parameter_list);
3091
3092 --ANCHAUDH starts modification for the leads bug.
3093
3094 AMS_UTILITY_PVT.Convert_Timezone(
3095 p_init_msg_list => FND_API.G_TRUE,
3096 x_return_status => l_return_status,
3097 x_msg_count => l_msg_count,
3098 x_msg_data => l_msg_data,
3099
3100 p_user_tz_id => l_timezone,
3101 p_in_time => l_start_time,
3102 p_convert_type => 'SYS',
3103
3104 x_out_time => l_sys_start_time
3105 );
3106
3107 -- If any errors happen let start time be sysdate
3108 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3109 l_sys_start_time := SYSDATE;
3110 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3111 l_sys_start_time := SYSDATE;
3112 END IF;
3113
3114 AMS_Utility_PVT.Create_Log (
3115 x_return_status => l_return_status,
3116 p_arc_log_used_by => 'CSCH',
3117 p_log_used_by_id => p_schedule_id,
3118 p_msg_data => 'Before Raise : started with : '||TO_CHAR(l_user_id)||' '||TO_CHAR(l_resp_id)||' '||TO_CHAR(l_resp_appl_id),
3119 p_msg_type => 'DEBUG'
3120 );
3121
3122 AMS_Utility_PVT.debug_message('Raise Business event for schedule execution');
3123 WF_EVENT.Raise
3124 ( p_event_name => 'oracle.apps.ams.campaign.ExecuteSchedule',
3125 p_event_key => l_new_item_key,
3126 p_parameters => l_parameter_list,
3127 p_send_date => l_sys_start_time);
3128 END IF; -- repeating parent instance check
3129 END IF; -- not triggerable
3130
3131 UPDATE ams_campaign_schedules_b
3132 SET workflow_item_key = l_new_item_key
3133 WHERE schedule_id = p_schedule_id ;
3134
3135 END IF;
3136 ELSIF (p_new_status_code = 'COMPLETED' AND l_activity_type_code = 'EVENTS')
3137 THEN
3138 IF l_def_flag = 'Y' THEN
3139 AMS_EvhRules_PVT.process_leads(p_event_id => l_related_event_id,
3140 p_obj_type => 'CSCH',
3141 p_obj_srccd => l_source_code);
3142 END IF;
3143 END IF;
3144
3145 IF l_activity_type_code = 'EVENTS'
3146 THEN
3147 l_new_status_id := AMS_Utility_PVT.get_default_user_status('AMS_EVENT_STATUS',p_new_status_code);
3148
3149 --Added by ANSKUMAR for Fulfilment
3150
3151 IF p_new_status_code='ACTIVE' OR p_new_status_code='CANCELLED'
3152 THEN
3153 l_evo_rec.event_offer_id := l_related_event_id;
3154 l_evo_rec.event_object_type := 'EONE';
3155 l_evo_rec.user_status_id := l_new_status_id;
3156 l_evo_rec.system_status_code := p_new_status_code;
3157 --l_evo_rec.last_status_date := SYSDATE;
3158 --l_evo_rec.owner_user_id := l_owner_user_id;
3159 --l_evo_rec.application_id := 530;
3160 --l_evo_rec.event_level := 'MAIN';
3161 --l_evo_rec.parent_type := 'CAMP';
3162 --l_evo_rec.parent_id := l_campaign_id;
3163 --l_evo_rec.custom_setup_id := 3000;
3164
3165 AMS_EventOffer_PVT.fulfill_event_offer(p_evo_rec => l_evo_rec,
3166 x_return_status => l_return_status);
3167
3168 END IF;
3169 -- Not handling return_stauts here
3170
3171 UPDATE ams_event_offers_all_b
3172 SET user_status_id = l_new_status_id,
3173 system_status_code = p_new_status_code,
3174 last_status_date = SYSDATE
3175 WHERE event_offer_id = l_related_event_id;
3176
3177
3178 END IF;
3179
3180 END update_status;
3181
3182
3183
3184
3185 --=====================================================================
3186 -- PROCEDURE
3187 -- Update_Schedule_Owner
3188 --
3189 -- PURPOSE
3190 -- The api is created to update the owner of the schedule from the
3191 -- access table if the owner is changed in update.
3192 --
3193 -- Algorithm:
3194 -- 1. Call update_object_owner from access_pvt
3195 -- 2. Add access from campaign to schedules
3196 --
3197 -- HISTORY
3198 -- 06-Jun-2002 soagrawa Created. Refer to bug# 2406677
3199 -- 18-jun-2002 soagrawa Fixed bug# 2421601
3200 --=====================================================================
3201 PROCEDURE Update_Schedule_Owner(
3202 p_api_version IN NUMBER,
3203 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
3204 p_commit IN VARCHAR2 := FND_API.g_false,
3205 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
3206 x_return_status OUT NOCOPY VARCHAR2,
3207 x_msg_count OUT NOCOPY NUMBER,
3208 x_msg_data OUT NOCOPY VARCHAR2,
3209 p_object_type IN VARCHAR2 := NULL ,
3210 p_schedule_id IN NUMBER,
3211 p_owner_id IN NUMBER )
3212 IS
3213
3214 CURSOR c_owner IS
3215 SELECT owner_user_id , campaign_id
3216 FROM ams_campaign_schedules_vl
3217 WHERE schedule_id = p_schedule_id ;
3218
3219 CURSOR c_access_csch_det(p_owner NUMBER) IS
3220 SELECT *
3221 FROM ams_act_access
3222 WHERE arc_act_access_to_object = 'CSCH'
3223 AND user_or_role_id = p_owner
3224 AND arc_user_or_role_type = 'USER'
3225 AND act_access_to_object_id = p_schedule_id;
3226
3227 CURSOR c_access_camp_det(p_campaign_id NUMBER) IS
3228 SELECT *
3229 FROM ams_act_access
3230 WHERE arc_act_access_to_object = 'CAMP'
3231 -- AND user_or_role_id = p_owner_id
3232 AND arc_user_or_role_type = 'USER'
3233 AND act_access_to_object_id = p_campaign_id;
3234
3235
3236 l_access_csch_rec c_access_csch_det%ROWTYPE;
3237 l_access_camp_rec c_access_camp_det%ROWTYPE;
3238
3239 l_access_rec AMS_Access_Pvt.access_rec_type ;
3240
3241 l_old_owner NUMBER ;
3242 l_campaign_id NUMBER ;
3243
3244 l_dummy_id NUMBER ;
3245
3246
3247 BEGIN
3248 -- the following 2 lines added by soagrawa on 18-jun-2002
3249 -- for bug# 2421601
3250 IF (AMS_DEBUG_HIGH_ON) THEN
3251
3252 AMS_Utility_PVT.debug_message('Update schedule owner ');
3253 END IF;
3254 x_return_status := FND_API.g_ret_sts_success;
3255
3256 OPEN c_owner ;
3257 FETCH c_owner INTO l_old_owner, l_campaign_id ;
3258 IF c_owner%NOTFOUND THEN
3259 CLOSE c_owner;
3260 AMS_Utility_Pvt.Error_Message('AMS_API_RECORD_NOT_FOUND');
3261 RAISE FND_API.g_exc_error;
3262 END IF;
3263 CLOSE c_owner ;
3264
3265 IF p_owner_id <> l_old_owner THEN
3266
3267 -- call update_owner_object
3268 AMS_Access_PVT.update_object_owner(
3269 p_api_version => p_api_version,
3270 p_init_msg_list => p_init_msg_list,
3271 p_commit => p_commit,
3272 p_validation_level => p_validation_level,
3273 x_return_status => x_return_status,
3274 x_msg_count => x_msg_count,
3275 x_msg_data => x_msg_data,
3276 p_object_type => nvl(p_object_type,'CSCH'),
3277 p_object_id => p_schedule_id,
3278 p_resource_id => p_owner_id,
3279 p_old_resource_id => l_old_owner
3280 );
3281
3282 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3283 RAISE FND_API.G_EXC_ERROR;
3284 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3286 END IF;
3287
3288 -- get all the access list ppl of campaign
3289 -- check if they are not in the access list of the schedule
3290 -- if they are do nothing
3291 -- if they are not add them
3292
3293
3294 OPEN c_access_camp_det(l_campaign_id) ;
3295 LOOP
3296 FETCH c_access_camp_det INTO l_access_camp_rec;
3297 EXIT WHEN c_access_camp_det%NOTFOUND ;
3298
3299 OPEN c_access_csch_det(l_access_camp_rec.user_or_role_id);
3300 FETCH c_access_csch_det INTO l_access_csch_rec;
3301 IF c_access_csch_det%NOTFOUND THEN
3302
3303 -- Create Access
3304 l_access_rec.act_access_to_object_id := p_schedule_id ;
3305 l_access_rec.arc_act_access_to_object := 'CSCH' ;
3306 l_access_rec.owner_flag := 'N' ;
3307 l_access_rec.user_or_role_id := l_access_camp_rec.user_or_role_id ;
3308 l_access_rec.arc_user_or_role_type := l_access_camp_rec.arc_user_or_role_type ;
3309 l_access_rec.delete_flag := l_access_camp_rec.delete_flag ;
3310 l_access_rec.admin_flag := l_access_camp_rec.admin_flag ;
3311
3312 AMS_Access_Pvt.Create_Access(
3313 p_api_version => p_api_version,
3314 p_init_msg_list => p_init_msg_list,
3315 p_commit => p_commit,
3316 p_validation_level => p_validation_level,
3317
3318 x_return_status => x_return_status,
3319 x_msg_count => x_msg_count,
3320 x_msg_data => x_msg_data,
3321
3322 p_access_rec => l_access_rec,
3323 x_access_id => l_dummy_id
3324 );
3325 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3326 CLOSE c_access_csch_det;
3327 CLOSE c_access_camp_det;
3328 RAISE FND_API.G_EXC_ERROR;
3329 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3330 CLOSE c_access_csch_det;
3331 CLOSE c_access_camp_det;
3332 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3333 END IF;
3334
3335
3336 ELSE
3337 -- do nothing
3338 NULL;
3339 END IF;
3340 CLOSE c_access_csch_det;
3341
3342 END LOOP;
3343 CLOSE c_access_camp_det ;
3344
3345 END IF ;
3346
3347 END Update_Schedule_Owner ;
3348
3349
3350
3351
3352
3353 -- Start of Comments
3354 --
3355 -- NAME
3356 -- Handle_Error
3357 --
3358 -- PURPOSE
3359 -- This Procedure will Get all the Errors from the Message stack and
3360 -- Set the Workflow item attribut with the Error Messages
3361 --
3362 -- Used By Activities
3363 --
3364 --
3365 -- NOTES
3366 --
3367 -- HISTORY
3368 -- 03-Sep-2003 ptendulk created
3369 -- 14-Oct-2003 dbiswas added wf_attrib to signature
3370 -- End of Comments
3371 PROCEDURE Handle_Error
3372 (p_itemtype IN VARCHAR2 ,
3373 p_itemkey IN VARCHAR2 ,
3374 p_msg_count IN NUMBER , -- Number of error Messages
3375 p_msg_data IN VARCHAR2 ,
3376 p_wf_err_attrib IN VARCHAR2 := 'ERROR_MSG'
3377 )
3378 IS
3379 l_msg_count NUMBER ;
3380 l_msg_data VARCHAR2(2000);
3381 l_final_data VARCHAR2(4000);
3382 l_msg_index NUMBER ;
3383 l_cnt NUMBER := 0 ;
3384 l_return_status VARCHAR2(1);
3385 l_schedule_id NUMBER ;
3386 BEGIN
3387
3388 l_schedule_id := WF_ENGINE.GetItemAttrText(
3389 itemtype => p_itemtype,
3390 itemkey => p_itemkey ,
3391 aname => 'SCHEDULE_ID');
3392
3393 AMS_Utility_PVT.Create_Log (
3394 x_return_status => l_return_status,
3395 p_arc_log_used_by => 'CSCH',
3396 p_log_used_by_id => l_schedule_id,
3397 p_msg_data => 'Error Message handling',
3398 p_msg_type => 'DEBUG'
3399 );
3400
3401 WHILE l_cnt < p_msg_count
3402 LOOP
3403 FND_MSG_PUB.Get(p_msg_index => l_cnt + 1,
3404 p_encoded => FND_API.G_FALSE,
3405 p_data => l_msg_data,
3406 p_msg_index_out => l_msg_index ) ;
3407 l_final_data := l_final_data ||l_msg_index||': '||l_msg_data||fnd_global.local_chr(10);
3408 l_cnt := l_cnt + 1 ;
3409
3410 END LOOP ;
3411
3412 WF_ENGINE.SetItemAttrText(itemtype => p_itemtype,
3413 itemkey => p_itemkey ,
3414 aname => 'ERROR_MESSAGE',
3415 avalue => l_final_data );
3416
3417 END Handle_Error;
3418
3419
3420
3421 --=====================================================================
3422 -- PROCEDURE
3423 -- Init_Schedule_val
3424 --
3425 -- PURPOSE
3426 -- This api will be used by schedule execution workflow to initialize the schedule
3427 -- parameter values.
3428 --
3429 -- HISTORY
3430 -- 23-Aug-2003 ptendulk Created.
3431 -- 19-Sep-2003 dbiswas Update out to out nocopy
3432 -- 09-nov-2004 anchaudh Now setting item owner along with bug fix for bug# 3799053
3433 --=====================================================================
3434 PROCEDURE Init_Schedule_val(itemtype IN VARCHAR2,
3435 itemkey IN VARCHAR2,
3436 actid IN NUMBER,
3437 funcmode IN VARCHAR2,
3438 result OUT NOCOPY VARCHAR2) IS
3439 l_schedule_id NUMBER;
3440
3441 CURSOR c_schedule_det(l_csch_id NUMBER) IS
3442 SELECT schedule_name, status_code,owner_user_id,
3443 DECODE(activity_type_code,'DIRECT_SALES','SALES','DIRECT_MARKETING','DIRECT_MARKETING','OTHERS') activity_type,
3444 activity_id, start_date_time, end_date_time
3445 FROM ams_campaign_schedules_vl
3446 WHERE schedule_id = l_csch_id ;
3447 l_schedule_rec c_schedule_det%ROWTYPE;
3448
3449 CURSOR c_emp_dtl(l_res_id IN NUMBER) IS
3450 SELECT employee_id
3451 FROM ams_jtf_rs_emp_v
3452 WHERE resource_id = l_res_id ;
3453 l_emp_id NUMBER;
3454 l_user_name VARCHAR2(100);
3455 l_display_name VARCHAR2(100);
3456 l_return_status VARCHAR2(1);
3457 l_user_id NUMBER;
3458 l_resp_id NUMBER;
3459 l_resp_appl_id NUMBER;
3460
3461 BEGIN
3462 IF (funcmode = 'RUN')
3463 THEN
3464
3465 l_schedule_id := WF_ENGINE.GetItemAttrText(
3466 itemtype => itemtype,
3467 itemkey => itemkey ,
3468 aname => 'SCHEDULE_ID');
3469
3470 l_user_id := FND_GLOBAL.USER_ID;
3471 l_resp_id := FND_GLOBAL.RESP_ID;
3472 l_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
3473
3474 AMS_Utility_PVT.Create_Log (
3475 x_return_status => l_return_status,
3476 p_arc_log_used_by => 'CSCH',
3477 p_log_used_by_id => l_schedule_id,
3478 p_msg_data => 'Init_Schedule_val : started with '||TO_CHAR(l_user_id)||' '||TO_CHAR(l_resp_id)||' '||TO_CHAR(l_resp_appl_id),
3479 p_msg_type => 'DEBUG'
3480 );
3481
3482 OPEN c_schedule_det(l_schedule_id);
3483 FETCH c_schedule_det INTO l_schedule_rec ;
3484 CLOSE c_schedule_det;
3485
3486 OPEN c_emp_dtl(l_schedule_rec.owner_user_id);
3487 FETCH c_emp_dtl INTO l_emp_id;
3488 -- soagrawa setting item owner along with bug fix for bug# 3799053
3489 IF c_emp_dtl%FOUND
3490 THEN
3491 WF_DIRECTORY.getrolename
3492 ( p_orig_system => 'PER',
3493 p_orig_system_id => l_emp_id ,
3494 p_name => l_user_name,
3495 p_display_name => l_display_name );
3496
3497 IF l_user_name IS NOT NULL THEN
3498 Wf_Engine.SetItemOwner(itemtype => itemtype,
3499 itemkey => itemkey,
3500 owner => l_user_name);
3501 END IF;
3502 END IF;
3503 CLOSE c_emp_dtl;
3504
3505 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3506 itemkey => itemkey,
3507 aname => 'SCHEDULE_NAME',
3508 avalue => l_schedule_rec.schedule_name);
3509
3510 WF_ENGINE.SetItemUserkey(itemtype => itemtype,
3511 itemkey => itemkey ,
3512 userkey => l_schedule_rec.schedule_name);
3513
3514 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3515 itemkey => itemkey,
3516 aname => 'SCHEDULE_OWNER',
3517 avalue => l_user_name);
3518
3519 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3520 itemkey => itemkey,
3521 aname => 'WF_ADMINISTRATOR',
3522 avalue => l_user_name);
3523
3524 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3525 itemkey => itemkey,
3526 aname => 'SCHEDULE_STATUS',
3527 avalue => l_schedule_rec.status_code);
3528
3529 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3530 itemkey => itemkey,
3531 aname => 'SCHEDULE_CHANNEL',
3532 avalue => l_schedule_rec.activity_id );
3533
3534 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3535 itemkey => itemkey,
3536 aname => 'ACTIVITY_TYPE',
3537 avalue => l_schedule_rec.activity_type );
3538
3539 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3540 itemkey => itemkey,
3541 aname => 'ERROR_FLAG',
3542 avalue => 'N');
3543 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3544 itemkey => itemkey,
3545 aname => 'AMS_SCHEDULE_START_DATE',
3546 avalue => l_schedule_rec.start_date_time );
3547
3548 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3549 itemkey => itemkey,
3550 aname => 'AMS_SCHEDULE_END_DATE',
3551 avalue => l_schedule_rec.end_date_time );
3552
3553 END IF;
3554
3555 -- CANCEL mode - Normal Process Execution
3556 IF (funcmode = 'CANCEL')
3557 THEN
3558 RETURN;
3559 END IF;
3560
3561 -- TIMEOUT mode - Normal Process Execution
3562 IF (funcmode = 'TIMEOUT')
3563 THEN
3564 RETURN;
3565 END IF;
3566 -- dbms_output.put_line('End Check Trigger stat :'||result);
3567
3568 EXCEPTION
3569 WHEN OTHERS THEN
3570 wf_core.context(G_PKG_NAME,'Init_Schedule_val',itemtype,itemkey,actid,funcmode);
3571 RAISE ;
3572 END ;
3573
3574 /* Commented for sql rep 14423973. Bug 4956974
3575 PROCEDURE AMS_SELECTOR
3576 ( p_itemtype in varchar2
3577 , p_itemkey in varchar2
3578 , p_actid in number
3579 , p_funcmode in varchar2
3580 , p_result in out nocopy varchar2)
3581 IS
3582 l_user_id NUMBER;
3583 l_resp_id NUMBER;
3584 l_resp_appl_id NUMBER;
3585 l_return_status VARCHAR2(1);
3586 l_schedule_id NUMBER;
3587
3588 CURSOR c_schedule_creator_id (p_schedule_id IN NUMBER) IS
3589 select created_by
3590 from ams_campaign_schedules_b
3591 where schedule_id = p_schedule_id;
3592
3593 CURSOR c_user_resp_dtl(p_user_id IN NUMBER) IS
3594 SELECT responsibility_id
3595 FROM fnd_user_resp_groups
3596 WHERE responsibility_application_id = 530
3597 and user_id = p_user_id
3598 and rownum < 2;
3599
3600 BEGIN
3601 IF (p_funcmode = 'RUN') THEN
3602 -- Code that determines Start Process
3603 p_result := 'COMPLETE';
3604 ELSIF (p_funcmode = 'TEST_CTX') THEN
3605 -- Code that compares current session context
3606 -- with the work item context required to execute
3607 -- the workflow safely
3608 l_user_id := FND_GLOBAL.USER_ID;
3609 l_resp_id := FND_GLOBAL.RESP_ID;
3610 l_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
3611
3612 l_schedule_id := WF_ENGINE.GetItemAttrText(
3613 itemtype => p_itemtype,
3614 itemkey => p_itemkey ,
3615 aname => 'SCHEDULE_ID');
3616
3617 AMS_Utility_PVT.Create_Log (
3618 x_return_status => l_return_status,
3619 p_arc_log_used_by => 'CSCH',
3620 p_log_used_by_id => l_schedule_id,
3621 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),
3622 p_msg_type => 'DEBUG'
3623 );
3624
3625 if l_user_id < 0 then
3626 -- If the background engine is executing the
3627 -- Selector/Callback function, the workflow engine
3628 -- Will immediately run the Selector/Callback
3629 -- Function in SET_CTX mode
3630 OPEN c_schedule_creator_id(l_schedule_id);
3631 FETCH c_schedule_creator_id INTO l_user_id;
3632 CLOSE c_schedule_creator_id;
3633
3634 OPEN c_user_resp_dtl(l_user_id);
3635 FETCH c_user_resp_dtl INTO l_resp_id;
3636 CLOSE c_user_resp_dtl;
3637
3638 l_resp_appl_id := 530;
3639
3640 AMS_Utility_PVT.Create_Log (
3641 x_return_status => l_return_status,
3642 p_arc_log_used_by => 'CSCH',
3643 p_log_used_by_id => l_schedule_id,
3644 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),
3645 p_msg_type => 'DEBUG'
3646 );
3647
3648 -- Set the database session context
3649 FND_GLOBAL.Apps_Initialize(l_user_id, l_resp_id, l_resp_appl_id);
3650 p_result := 'COMPLETE:FALSE';
3651 else
3652 p_result := 'COMPLETE:TRUE';
3653 end if;
3654 ELSIF(p_funcmode = 'SET_CTX') THEN
3655 -- Code that sets the current session context
3656 -- based on the work item context stored in item attributes
3657 -- get Item Attributes for user_id, responsibility_id and application_id
3658 -- this assumes that they were set as item attribute, probably through
3659 -- definition.
3660 l_schedule_id := WF_ENGINE.GetItemAttrText(
3661 itemtype => p_itemtype,
3662 itemkey => p_itemkey ,
3663 aname => 'SCHEDULE_ID');
3664
3665 OPEN c_schedule_creator_id(l_schedule_id);
3666 FETCH c_schedule_creator_id INTO l_user_id;
3667 CLOSE c_schedule_creator_id;
3668
3669 OPEN c_user_resp_dtl(l_user_id);
3670 FETCH c_user_resp_dtl INTO l_resp_id;
3671 CLOSE c_user_resp_dtl;
3672
3673 l_resp_appl_id := 530;
3674
3675 -- Set the database session context which also sets the org
3676 --FND_GLOBAL.Apps_Initialize(l_user_id, l_resp_id, l_resp_appl_id);
3677 AMS_Utility_PVT.Create_Log (
3678 x_return_status => l_return_status,
3679 p_arc_log_used_by => 'CSCH',
3680 p_log_used_by_id => l_schedule_id,
3681 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),
3682 p_msg_type => 'DEBUG'
3683 );
3684
3685 p_result := 'COMPLETE';
3686 ELSE
3687 p_result := 'COMPLETE';
3688 END IF;
3689 EXCEPTION
3690 WHEN OTHERS THEN NULL;
3691 WF_CORE.Context('PROD_STANDARD_WF', 'AMS_SELECTOR', p_itemtype, p_itemkey, p_actid, p_funcmode);
3692 RAISE;
3693 END AMS_SELECTOR;
3694 */
3695
3696 --=====================================================================
3697 -- PROCEDURE
3698 -- Check_Schedule_Status
3699 --
3700 -- PURPOSE
3701 -- This api will be used by schedule execution workflow to check schedule status
3702 -- The schedule can be in available or active status. if the schedule is available
3703 -- workflow will update the status to active.
3704 --
3705 -- HISTORY
3706 -- 23-Aug-2003 ptendulk Created.
3707 -- 19-Sep-2003 dbiswas Added nocopy
3708 --=====================================================================
3709 PROCEDURE Check_Schedule_Status(itemtype IN VARCHAR2,
3710 itemkey IN VARCHAR2,
3711 actid IN NUMBER,
3712 funcmode IN VARCHAR2,
3713 result OUT NOCOPY VARCHAR2) IS
3714 l_schedule_status VARCHAR2(30) ;
3715 l_return_status VARCHAR2(1);
3716 l_schedule_id NUMBER;
3717 BEGIN
3718 -- dbms_output.put_line('Process Check_Repeat');
3719 -- RUN mode - Normal Process Execution
3720 IF (funcmode = 'RUN')
3721 THEN
3722 l_schedule_id := WF_ENGINE.GetItemAttrText(
3723 itemtype => itemtype,
3724 itemkey => itemkey ,
3725 aname => 'SCHEDULE_ID' );
3726
3727 AMS_Utility_PVT.Create_Log (
3728 x_return_status => l_return_status,
3729 p_arc_log_used_by => 'CSCH',
3730 p_log_used_by_id => l_schedule_id,
3731 p_msg_data => 'Check_Schedule_Status : started',
3732 p_msg_type => 'DEBUG'
3733 );
3734
3735 l_schedule_status := WF_ENGINE.GetItemAttrText(
3736 itemtype => itemtype,
3737 itemkey => itemkey ,
3738 aname => 'SCHEDULE_STATUS' );
3739
3740
3741 -- make sure that last activation date is updated
3742 UPDATE ams_campaign_schedules_b
3743 SET last_activation_date = SYSDATE,
3744 object_version_number = object_version_number + 1,
3745 last_update_date = SYSDATE,
3746 last_updated_by = FND_GLOBAL.user_id
3747 WHERE schedule_id = l_schedule_id ;
3748
3749 IF l_schedule_status = 'ACTIVE' THEN
3750 result := 'COMPLETE:ACTIVE' ;
3751 ELSE
3752 result := 'COMPLETE:AVAILABLE' ;
3753 END IF ;
3754 END IF;
3755
3756 -- CANCEL mode - Normal Process Execution
3757 IF (funcmode = 'CANCEL')
3758 THEN
3759 result := 'COMPLETE:' ;
3760 RETURN;
3761 END IF;
3762
3763 -- TIMEOUT mode - Normal Process Execution
3764 IF (funcmode = 'TIMEOUT')
3765 THEN
3766 result := 'COMPLETE:' ;
3767 RETURN;
3768 END IF;
3769 EXCEPTION
3770 WHEN OTHERS THEN
3771 wf_core.context(G_PKG_NAME,'Check_Schedule_Status',itemtype,itemkey,actid,funcmode);
3772 raise ;
3773 END Check_Schedule_Status ;
3774
3775
3776 --=========================================================================================================
3777 -- PROCEDURE
3778 -- Update_Schedule_Status
3779 --
3780 -- PURPOSE
3781 -- This api will be used by schedule execution workflow to update schedule status
3782 -- It will update the schedule status to Active.
3783 --
3784 -- HISTORY
3785 -- 23-Aug-2003 ptendulk Created.
3786 -- 19-Sep-2003 dbiswas Added nocopy
3787 -- 17-Mar-2005 spendem call the API to raise business event on status change as per enh # 3805347
3788 --===========================================================================================================
3789 PROCEDURE Update_Schedule_Status(itemtype IN VARCHAR2,
3790 itemkey IN VARCHAR2,
3791 actid IN NUMBER,
3792 funcmode IN VARCHAR2,
3793 result OUT NOCOPY VARCHAR2) IS
3794
3795 -- declare cursor as per enh # 3805347
3796 CURSOR c_csch_det(p_schedule_id IN NUMBER) IS
3797 SELECT status_code
3798 FROM ams_campaign_schedules_b
3799 WHERE schedule_id = p_schedule_id;
3800
3801 l_schedule_id NUMBER;
3802 l_user_status_id NUMBER ;
3803 l_return_status VARCHAR2(1);
3804 l_old_status_code VARCHAR2(30); -- added as per enh # 3805347.
3805
3806 BEGIN
3807 -- dbms_output.put_line('Process Check_Repeat');
3808 -- RUN mode - Normal Process Execution
3809 IF (funcmode = 'RUN')
3810 THEN
3811 l_schedule_id := WF_ENGINE.GetItemAttrText(
3812 itemtype => itemtype,
3813 itemkey => itemkey ,
3814 aname => 'SCHEDULE_ID' );
3815
3816 AMS_Utility_PVT.Create_Log (
3817 x_return_status => l_return_status,
3818 p_arc_log_used_by => 'CSCH',
3819 p_log_used_by_id => l_schedule_id,
3820 p_msg_data => 'Update_Schedule_Status : started',
3821 p_msg_type => 'DEBUG'
3822 );
3823
3824
3825 -- open cursor here for enh # 3805347
3826 OPEN c_csch_det(l_schedule_id);
3827 FETCH c_csch_det INTO l_old_status_code;
3828 CLOSE c_csch_det;
3829
3830 l_user_status_id := AMS_Utility_PVT.get_default_user_status('AMS_CAMPAIGN_SCHEDULE_STATUS','ACTIVE') ;
3831
3832 UPDATE ams_campaign_schedules_b
3833 SET status_code = 'ACTIVE',
3834 user_status_id = l_user_status_id,
3835 status_date = SYSDATE,
3836 last_activation_date = SYSDATE,
3837 object_version_number = object_version_number + 1,
3838 last_update_date = SYSDATE,
3839 last_updated_by = FND_GLOBAL.user_id
3840 WHERE schedule_id = l_schedule_id ;
3841
3842 -- call to api to raise business event, as per enh # 3805347
3843 RAISE_BE_ON_STATUS_CHANGE(p_obj_id => l_schedule_id,
3844 p_obj_type => 'CSCH',
3845 p_old_status_code => l_old_status_code,
3846 p_new_status_code => 'ACTIVE' );
3847
3848 END IF;
3849
3850 -- CANCEL mode - Normal Process Execution
3851 IF (funcmode = 'CANCEL')
3852 THEN
3853 RETURN;
3854 END IF;
3855
3856 -- TIMEOUT mode - Normal Process Execution
3857 IF (funcmode = 'TIMEOUT')
3858 THEN
3859 RETURN;
3860 END IF;
3861 -- dbms_output.put_line('End Check Trigger stat :'||result);
3862 EXCEPTION
3863 WHEN OTHERS THEN
3864 wf_core.context(G_PKG_NAME,'Update_Schedule_Status',itemtype,itemkey,actid,funcmode);
3865 RAISE ;
3866 END Update_Schedule_Status ;
3867
3868
3869 --=====================================================================
3870 -- PROCEDURE
3871 -- Check_Schedule_Act_Type
3872 --
3873 -- PURPOSE
3874 -- This api will be used by schedule execution workflow to check schedule activity
3875 -- Based on the activity type different apis will be called.
3876 --
3877 -- HISTORY
3878 -- 23-Aug-2003 ptendulk Created.
3879 -- 19-Sep-2003 dbiswas Added nocopy
3880 --=====================================================================
3881 PROCEDURE Check_Schedule_Act_Type(itemtype IN VARCHAR2,
3882 itemkey IN VARCHAR2,
3883 actid IN NUMBER,
3884 funcmode IN VARCHAR2,
3885 result OUT NOCOPY VARCHAR2) IS
3886 l_schedule_activity VARCHAR2(30) ;
3887 l_return_status VARCHAR2(1);
3888 l_schedule_id NUMBER;
3889 BEGIN
3890 -- dbms_output.put_line('Process Check_Repeat');
3891 -- RUN mode - Normal Process Execution
3892 IF (funcmode = 'RUN')
3893 THEN
3894 l_schedule_id := WF_ENGINE.GetItemAttrText(
3895 itemtype => itemtype,
3896 itemkey => itemkey ,
3897 aname => 'SCHEDULE_ID' );
3898
3899 AMS_Utility_PVT.Create_Log (
3900 x_return_status => l_return_status,
3901 p_arc_log_used_by => 'CSCH',
3902 p_log_used_by_id => l_schedule_id,
3903 p_msg_data => 'Check_Schedule_Act_Type : started',
3904 p_msg_type => 'DEBUG'
3905 );
3906
3907 l_schedule_activity := WF_ENGINE.GetItemAttrText(
3908 itemtype => itemtype,
3909 itemkey => itemkey ,
3910 aname => 'ACTIVITY_TYPE' );
3911
3912
3913 result := 'COMPLETE:'||l_schedule_activity ;
3914
3915 END IF;
3916
3917 -- CANCEL mode - Normal Process Execution
3918 IF (funcmode = 'CANCEL')
3919 THEN
3920 result := 'COMPLETE:' ;
3921 RETURN;
3922 END IF;
3923
3924 -- TIMEOUT mode - Normal Process Execution
3925 IF (funcmode = 'TIMEOUT')
3926 THEN
3927 result := 'COMPLETE:' ;
3928 RETURN;
3929 END IF;
3930 EXCEPTION
3931 WHEN OTHERS THEN
3932 wf_core.context(G_PKG_NAME,'Check_Schedule_Act_Type',itemtype,itemkey,actid,funcmode);
3933 raise ;
3934 END Check_Schedule_Act_Type ;
3935
3936 --=====================================================================
3937 -- PROCEDURE
3938 -- Execute_Direct_Marketing
3939 --
3940 -- PURPOSE
3941 -- This api will be used by schedule execution workflow to execute schedule
3942 -- of type Direct Marketing
3943 --
3944 -- ALGORITHM
3945 -- 1. Does target group exist?
3946 -- Yes => 1.1 Increase usage
3947 -- 1.2 Is channel Email, Print, Fax
3948 -- Yes => 1.2.1 Increase contacted count
3949 -- 1.2.2 Stamp version in ibc_associations table
3950 -- 1.2.3 Send Fulfillment Request
3951 -- 1.2.4 Update list sent out date
3952 --
3953 -- Any error in any of the API callouts?
3954 -- => a) Set attribute ERROR_FLAG to Y
3955 -- b) Call Handle_err to set error msg values
3956 -- c) Return
3957 --
3958 -- OPEN ISSUES
3959 -- 1. Use Enable Fulfillment profile before fulilling?
3960 -- 2. If not enabled => write interaction or not?
3961 --
3962 -- HISTORY
3963 -- 23-Aug-2003 ptendulk Created.
3964 -- 19-Sep-2003 dbiswas Added nocopy
3965 -- 29-sep-2003 soagrawa Modified to clean up the code and removed interaction for direct mail channel
3966 -- 29-sep-2003 soagrawa Modified to clean up the code and removed interaction for direct mail channel
3967 -- 05-apr-2004 soagrawa added ELSE part for when TGRP does not exist
3968 -- this is needed for automated flows like Repeating Schedules / Triggers
3969 -- pls refer bug# 3553087
3970 -- 29-apr-2004 anchaudh fixed the reopened bug#3553087
3971 -- 09-nov-2004 anchaudh fixed bug# 3799053 about FFM requests being created with random user ids
3972 -- 28-jan-2005 spendem fix for bug # 4145845. Added to_char function to the schedule_id
3973 -- 14-mar-2005 spendem fix for bug # 4184571. Adding a filter for unwanted error.
3974 --=========================================================================================================
3975 PROCEDURE Execute_Direct_Marketing(itemtype IN VARCHAR2,
3976 itemkey IN VARCHAR2,
3977 actid IN NUMBER,
3978 funcmode IN VARCHAR2,
3979 result OUT NOCOPY VARCHAR2) IS
3980
3981 CURSOR c_tgrp_det(l_csch_id IN NUMBER) IS
3982 SELECT list_header_id
3983 FROM ams_act_lists la
3984 WHERE list_act_type = 'TARGET'
3985 AND list_used_by = 'CSCH'
3986 AND list_used_by_id = l_csch_id
3987 AND EXISTS (SELECT *
3988 FROM ams_list_entries le
3989 WHERE le.list_header_id = la.list_header_id) ;
3990
3991 -- soagrawa added the following cursor on 30-sep-2003 for stamping version
3992 CURSOR c_cover_letter_det (l_csch_id IN NUMBER) IS
3993 SELECT assoc.association_id, assoc.content_item_id, ci.live_citem_version_id
3994 FROM ibc_associations assoc, ibc_content_Items ci
3995 WHERE assoc.association_type_code = 'AMS_CSCH'
3996 AND assoc.associated_object_val1 = to_char(l_csch_id) -- fix for bug # 4145845
3997 AND assoc.content_item_id = ci.content_Item_id;
3998
3999 -- anchaudh added the following cursor on 01-nov-2004 for getting csch owner, bug# 3799053
4000 CURSOR c_csch_det (l_csch_id IN NUMBER) IS
4001 SELECT owner_user_id
4002 FROM ams_campaign_schedules_b
4003 WHERE schedule_id = l_csch_id ;
4004
4005 l_csch_owner_user_id NUMBER;
4006 l_schedule_id NUMBER;
4007 l_return_status VARCHAR2(1) := FND_API.g_ret_sts_success ;
4008 l_log_return_status VARCHAR2(1) := FND_API.g_ret_sts_success ;
4009 l_activity_id NUMBER;
4010 l_msg_count NUMBER;
4011 l_msg_data VARCHAR2(2000);
4012 l_list_id NUMBER;
4013 l_request_id NUMBER;
4014 l_association_id NUMBER;
4015 l_cover_letter_id NUMBER;
4016 l_cover_letter_ver_id NUMBER;
4017 l_error_msg VARCHAR2(4000);
4018
4019 BEGIN
4020
4021 -- RUN mode - Normal Process Execution
4022 IF (funcmode = 'RUN')
4023 THEN
4024 -- get schedule id
4025 l_schedule_id := WF_ENGINE.GetItemAttrText(
4026 itemtype => itemtype,
4027 itemkey => itemkey ,
4028 aname => 'SCHEDULE_ID' );
4029
4030 AMS_Utility_PVT.Create_Log (
4031 x_return_status => l_log_return_status,
4032 p_arc_log_used_by => 'CSCH',
4033 p_log_used_by_id => l_schedule_id,
4034 p_msg_data => 'Execute_Direct_Marketing : started for schedule id '||l_schedule_id,
4035 p_msg_type => 'DEBUG'
4036 );
4037
4038
4039 -- get schedule activity
4040 l_activity_id := WF_ENGINE.GetItemAttrText(
4041 itemtype => itemtype,
4042 itemkey => itemkey ,
4043 aname => 'SCHEDULE_CHANNEL' );
4044 --
4045 -- 1. Does target group exist?
4046 --
4047 OPEN c_tgrp_det (l_schedule_id) ;
4048 FETCH c_tgrp_det INTO l_list_id ;
4049 CLOSE c_tgrp_det ;
4050
4051
4052 IF FND_API.G_TRUE = Target_Group_Exist(l_schedule_id)
4053 THEN
4054 --
4055 -- Yes => 1.1 Increase usage
4056 --
4057 AMS_Utility_PVT.Create_Log (
4058 x_return_status => l_log_return_status,
4059 p_arc_log_used_by => 'CSCH',
4060 p_log_used_by_id => l_schedule_id,
4061 p_msg_data => 'Execute_Direct_Marketing : Increase usage',
4062 p_msg_type => 'DEBUG'
4063 );
4064
4065 AMS_List_Purge_PVT.Increase_Usage
4066 (
4067 p_api_version => 1.0,
4068 p_init_msg_list => FND_API.g_false,
4069 p_commit => FND_API.g_false,
4070 p_validation_level => FND_API.g_valid_level_full,
4071 x_return_status => l_return_status,
4072 x_msg_count => l_msg_count,
4073 x_msg_data => l_msg_data,
4074 p_list_header_id => l_list_id -- target group id
4075 );
4076
4077 IF l_return_status <> FND_API.g_ret_sts_success THEN
4078 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4079 itemkey => itemkey,
4080 aname => 'ERROR_FLAG',
4081 avalue => 'Y');
4082 Handle_Error(p_itemtype => itemtype,
4083 p_itemkey => itemkey,
4084 p_msg_count => l_msg_count,
4085 p_msg_data => l_msg_data);
4086 RETURN;
4087 END IF;
4088
4089 --
4090 -- 1.2 Is channel Email, Print, Fax
4091 --
4092 IF (l_activity_id = 10 OR l_activity_id = 20 OR l_activity_id = 480)
4093 THEN
4094 AMS_Utility_PVT.Create_Log (
4095 x_return_status => l_log_return_status,
4096 p_arc_log_used_by => 'CSCH',
4097 p_log_used_by_id => l_schedule_id,
4098 p_msg_data => 'Execute_Direct_Marketing : update previously contacted',
4099 p_msg_type => 'DEBUG'
4100 );
4101
4102 --
4103 -- Yes => 1.2.1 Increase contacted count
4104 --
4105 AMS_Listheader_PVT.Update_Prev_Contacted_Count(
4106 p_used_by_id => l_schedule_id,
4107 p_used_by => 'CSCH',
4108 p_last_contacted_date => sysdate,
4109 p_init_msg_list => FND_API.g_false,
4110 p_commit => FND_API.g_false,
4111 x_return_status => l_return_status,
4112 x_msg_count => l_msg_count,
4113 x_msg_data => l_msg_data
4114 );
4115
4116 IF l_return_status <> FND_API.g_ret_sts_success THEN
4117 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4118 itemkey => itemkey,
4119 aname => 'ERROR_FLAG',
4120 avalue => 'Y');
4121 Handle_Error(p_itemtype => itemtype,
4122 p_itemkey => itemkey,
4123 p_msg_count => l_msg_count,
4124 p_msg_data => l_msg_data);
4125 RETURN;
4126 END IF;
4127
4128 /*
4129 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4130 itemkey => itemkey,
4131 aname => 'ERROR_FLAG',
4132 avalue => 'Y');
4133 Handle_Error(p_itemtype => itemtype,
4134 p_itemkey => itemkey,
4135 p_msg_count => l_msg_count,
4136 p_msg_data => l_msg_data);*/
4137
4138 --
4139 -- 1.2.2 Stamp version in ibc_associations table
4140 -- anchaudh : from R12 onwards this stamping of cover letter version will take place in the new event subscription api.
4141 --
4142 /*AMS_Utility_PVT.Create_Log (
4143 x_return_status => l_log_return_status,
4144 p_arc_log_used_by => 'CSCH',
4145 p_log_used_by_id => l_schedule_id,
4146 p_msg_data => 'Execute_Direct_Marketing : Stamping version',
4147 p_msg_type => 'DEBUG'
4148 );
4149
4150 -- get associated cover letter and its live version
4151 OPEN c_cover_letter_det(l_schedule_id);
4152 FETCH c_cover_letter_det INTO l_association_id, l_cover_letter_id, l_cover_letter_ver_id;
4153 CLOSE c_cover_letter_det;
4154
4155 IF l_association_id IS NOT null
4156 AND l_cover_letter_id IS NOT null
4157 AND l_cover_letter_ver_id IS NOT NULl
4158 THEN
4159 Ibc_Associations_Pkg.UPDATE_ROW(
4160 p_association_id => l_association_id
4161 ,p_content_item_id => l_cover_letter_id
4162 ,p_citem_version_id => l_cover_letter_ver_id
4163 ,p_association_type_code => 'AMS_CSCH'
4164 ,p_associated_object_val1 => l_schedule_id );
4165 ELSE
4166 -- throw error because no live cover letter is associated with the schedule
4167 -- either no cover letter is associated OR the cover letter associated has no live ver
4168 NULL;
4169 END IF;*/
4170
4171 --
4172 -- 1.2.3 Send Fulfillment Request
4173 --
4174 AMS_Utility_PVT.Create_Log (
4175 x_return_status => l_log_return_status,
4176 p_arc_log_used_by => 'CSCH',
4177 p_log_used_by_id => l_schedule_id,
4178 p_msg_data => 'Execute_Direct_Marketing : Call to fulfillment',
4179 p_msg_type => 'DEBUG'
4180 );
4181
4182 -- user id added by anchaudh on 09-nov-2004 for bug# 3799053
4183 OPEN c_csch_det(l_schedule_id);
4184 FETCH c_csch_det INTO l_csch_owner_user_id;
4185 CLOSE c_csch_det;
4186
4187 AMS_Fulfill_PVT.Ams_Fulfill(
4188 p_api_version => 1.0,
4189 p_init_msg_list => FND_API.g_false,
4190 p_commit => FND_API.g_false,
4191 x_return_status => l_return_status,
4192 x_msg_count => l_msg_count,
4193 x_msg_data => l_msg_data,
4194 x_request_history_id => l_request_id,
4195 p_schedule_id => l_schedule_id,
4196 -- user id passing added by anchaudh on 09-nov-2004 for bug# 3799053
4197 p_user_id => Ams_Utility_pvt.get_user_id(l_csch_owner_user_id)
4198 ) ;
4199
4200 AMS_Utility_PVT.Create_Log (
4201 x_return_status => l_log_return_status,
4202 p_arc_log_used_by => 'CSCH',
4203 p_log_used_by_id => l_schedule_id,
4204 p_msg_data => 'Execute_Direct_Marketing : Call to fulfillment : Return status is '||l_return_status,
4205 p_msg_type => 'DEBUG'
4206 );
4207
4208 IF l_return_status <> FND_API.g_ret_sts_success THEN
4209 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4210 itemkey => itemkey,
4211 aname => 'ERROR_FLAG',
4212 avalue => 'Y');
4213 Handle_Error(p_itemtype => itemtype,
4214 p_itemkey => itemkey,
4215 p_msg_count => l_msg_count,
4216 p_msg_data => l_msg_data);
4217 RETURN;
4218 END IF;
4219
4220 --
4221 -- 1.2.4 Update list sent out date
4222 --
4223 AMS_Utility_PVT.Create_Log (
4224 x_return_status => l_log_return_status,
4225 p_arc_log_used_by => 'CSCH',
4226 p_log_used_by_id => l_schedule_id,
4227 p_msg_data => 'Execute_Direct_Marketing : calling update_list_send_out_date ',
4228 p_msg_type => 'DEBUG'
4229 );
4230
4231 Update_List_Sent_Out_Date(
4232 p_api_version => 1.0,
4233 p_init_msg_list => FND_API.g_false,
4234 p_commit => FND_API.g_false,
4235
4236 x_return_status => l_return_status,
4237 x_msg_count => l_msg_count,
4238 x_msg_data => l_msg_data,
4239
4240 p_list_header_id => l_list_id);
4241
4242 AMS_Utility_PVT.Create_Log (
4243 x_return_status => l_log_return_status,
4244 p_arc_log_used_by => 'CSCH',
4245 p_log_used_by_id => l_schedule_id,
4246 p_msg_data => 'Execute_Direct_Marketing : update_list_send_out_date : Return status is '||l_return_status,
4247 p_msg_type => 'DEBUG'
4248 );
4249
4250 IF l_return_status <> FND_API.g_ret_sts_success THEN
4251 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4252 itemkey => itemkey,
4253 aname => 'ERROR_FLAG',
4254 avalue => 'Y');
4255 Handle_Error(p_itemtype => itemtype,
4256 p_itemkey => itemkey,
4257 p_msg_count => l_msg_count,
4258 p_msg_data => l_msg_data);
4259 RETURN;
4260 END IF;
4261
4262 END IF; -- activity is email / print / fax
4263
4264 -- 05-apr-2004 soagrawa added ELSE part for when TGRP does not exist
4265 -- this is needed for automated flows like Repeating Schedules / Triggers
4266 -- pls refer bug# 3553087
4267
4268 ELSE
4269 -- if TGRP does not exist
4270 -- AMS_Utility_PVT.Error_Message('AMS_CSCH_NO_TARGET_GROUP');
4271
4272 -- Throw a valid error, if TG does not exist.. Filter should be on channel email.fax/print and Telemarketing.
4273 -- fix for bug # 4184571
4274 IF (l_activity_id = 10 OR l_activity_id = 20 OR l_activity_id = 460 OR l_activity_id = 480)
4275 THEN
4276
4277 AMS_Utility_PVT.Create_Log (
4278 x_return_status => l_log_return_status,
4279 p_arc_log_used_by => 'CSCH',
4280 p_log_used_by_id => l_schedule_id,
4281 p_msg_data => 'Execute_Direct_Marketing : Target Group is empty',
4282 p_msg_type => 'DEBUG'
4283 );
4284
4285 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4286 itemkey => itemkey,
4287 aname => 'ERROR_FLAG',
4288 avalue => 'Y');
4289
4290 /* Handle_Error(p_itemtype => itemtype, -- 29-apr-2004 anchaudh : fixed the reopened bug#3553087
4291 p_itemkey => itemkey,
4292 p_msg_count => l_msg_count,
4293 p_msg_data => l_msg_data);*/
4294
4295 l_error_msg := FND_MESSAGE.get_string('AMS','AMS_CSCH_NO_TARGET_GROUP');
4296
4297 WF_ENGINE.SetItemAttrText(itemtype => itemtype, -- 29-apr-2004 anchaudh : fixed the reopened bug#3553087
4298 itemkey => itemkey,
4299 aname => 'ERROR_MESSAGE',
4300 avalue => l_error_msg);
4301
4302
4303 END IF;
4304
4305 END IF; -- target group exists
4306
4307 END IF; -- func mode is RUN
4308
4309 -- CANCEL mode - Normal Process Execution
4310 IF (funcmode = 'CANCEL')
4311 THEN
4312 RETURN;
4313 END IF;
4314
4315 -- TIMEOUT mode - Normal Process Execution
4316 IF (funcmode = 'TIMEOUT')
4317 THEN
4318 RETURN;
4319 END IF;
4320
4321 EXCEPTION
4322 WHEN OTHERS THEN
4323 wf_core.context(G_PKG_NAME,'Execute_Direct_Marketing',itemtype,itemkey,actid,funcmode);
4324 RAISE ;
4325 END Execute_Direct_Marketing;
4326
4327
4328
4329 --=====================================================================
4330 -- PROCEDURE
4331 -- Execute_Sales
4332 --
4333 -- PURPOSE
4334 -- This api will be used by schedule execution workflow to execute schedule
4335 -- of type Sales
4336 --
4337 -- HISTORY
4338 -- 23-Aug-2003 ptendulk Created.
4339 -- 19-Sep-2003 dbiswas Added nocopy
4340 --=====================================================================
4341 PROCEDURE Execute_Sales(itemtype IN VARCHAR2,
4342 itemkey IN VARCHAR2,
4343 actid IN NUMBER,
4344 funcmode IN VARCHAR2,
4345 result OUT NOCOPY VARCHAR2) IS
4346 l_schedule_id NUMBER;
4347 l_return_status VARCHAR2(1) := FND_API.g_ret_sts_success ;
4348 l_msg_count NUMBER;
4349 l_msg_data VARCHAR2(2000);
4350 l_user_id NUMBER;
4351 l_resp_id NUMBER;
4352 l_resp_appl_id NUMBER;
4353 BEGIN
4354 -- RUN mode - Normal Process Execution
4355 IF (funcmode = 'RUN')
4356 THEN
4357 l_schedule_id := WF_ENGINE.GetItemAttrText(
4358 itemtype => itemtype,
4359 itemkey => itemkey ,
4360 aname => 'SCHEDULE_ID' );
4361
4362 l_user_id := FND_GLOBAL.USER_ID;
4363 l_resp_id := FND_GLOBAL.RESP_ID;
4364 l_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
4365
4366 AMS_Utility_PVT.Create_Log (
4367 x_return_status => l_return_status,
4368 p_arc_log_used_by => 'CSCH',
4369 p_log_used_by_id => l_schedule_id,
4370 p_msg_data => 'Execute_Sales : started with '||TO_CHAR(l_user_id)||' '||TO_CHAR(l_resp_id)||' '||TO_CHAR(l_resp_appl_id),
4371 p_msg_type => 'DEBUG'
4372 );
4373
4374 -- Call the api to execute the sales schedule , return the error flag in l_return_status
4375 --generate_leads(l_schedule_id,'CSCH',l_return_status);
4376 generate_leads(l_schedule_id,'CSCH',l_return_status,itemtype,itemkey);--anchaudh changed the signature of this api for the leads bug.
4377
4378 AMS_Utility_PVT.Create_Log (
4379 x_return_status => l_return_status,
4380 p_arc_log_used_by => 'CSCH',
4381 p_log_used_by_id => l_schedule_id,
4382 p_msg_data => 'Execute_Sales : done',
4383 p_msg_type => 'DEBUG'
4384 );
4385
4386 IF l_return_status <> FND_API.g_ret_sts_success THEN
4387 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
4388 itemkey => itemkey,
4389 aname => 'ERROR_FLAG',
4390 avalue => 'Y');
4391 Handle_Error(p_itemtype => itemtype,
4392 p_itemkey => itemkey,
4393 p_msg_count => l_msg_count,
4394 p_msg_data => l_msg_data);
4395 END IF;
4396
4397 END IF;
4398 -- CANCEL mode - Normal Process Execution
4399 IF (funcmode = 'CANCEL')
4400 THEN
4401 RETURN;
4402 END IF;
4403
4404 -- TIMEOUT mode - Normal Process Execution
4405 IF (funcmode = 'TIMEOUT')
4406 THEN
4407 RETURN;
4408 END IF;
4409 -- dbms_output.put_line('End Check Trigger stat :'||result);
4410 EXCEPTION
4411 WHEN OTHERS THEN
4412 wf_core.context(G_PKG_NAME,'Execute_Sales',itemtype,itemkey,actid,funcmode);
4413 RAISE ;
4414 END Execute_Sales;
4415
4416
4417 --=====================================================================
4418 -- PROCEDURE
4419 -- generate_leads
4420 --
4421 -- PURPOSE
4422 -- This api will be used by schedule execution workflow generate leads.
4423 --
4424 -- HISTORY
4425 -- 08-Sep-2003 asaha Created.
4426 -- 09-dec-2005 soagrawa Added limited size batch processing for perf bug 4461415
4427 --=====================================================================
4428 PROCEDURE generate_leads(
4429 p_obj_id IN NUMBER,
4430 p_obj_type IN VARCHAR2,
4431 x_return_status OUT NOCOPY VARCHAR2,
4432 itemtype IN VARCHAR2,--anchaudh changed the signature of this api for the leads bug.
4433 itemkey IN VARCHAR2--anchaudh changed the signature of this api for the leads bug.
4434 ) IS
4435
4436 l_msg_count NUMBER;
4437 l_msg_data VARCHAR2(4000);
4438 l_triggerable_flag VARCHAR2(1);
4439 l_trig_repeat_flag VARCHAR2(1);
4440 l_orig_csch_id NUMBER;
4441 l_csch_offer_id NUMBER := null;
4442
4443 cursor c_party_relationships_csr(p_party_id NUMBER) is
4444 select subject_id, object_id
4445 from hz_relationships
4446 where party_id = p_party_id;
4447
4448 -- anchaudh 17th Mar'05 : modified the cursor to pull up only purchasable products for bug#3607972.
4449 cursor c_assoc_products_csr(p_schedule_id NUMBER) is
4450 select inventory_item_id, ams_act_products.category_id, organization_id
4451 from ams_act_products,ENI_PROD_DEN_HRCHY_PARENTS_V cat
4452 where arc_act_product_used_by = 'CSCH'
4453 and act_product_used_by_id = p_schedule_id
4454 and ams_act_products.category_id = cat.category_id(+)
4455 and nvl(cat.PURCHASE_INTEREST, 'Y') <> 'N';
4456
4457 cursor c_schedule_details_csr(p_schedule_id NUMBER) is
4458 select a.source_code, a.sales_methodology_id, b.source_code_id
4459 from ams_campaign_schedules_b a, ams_source_codes b
4460 where a.schedule_id = p_schedule_id
4461 and a.status_code = 'ACTIVE'
4462 and a.source_code = b.source_code;
4463
4464 CURSOR c_sch_det(p_schedule_id NUMBER) IS -- anchaudh added this new cursor for the leads bug.
4465 SELECT NVL(triggerable_flag,'N')
4466 ,NVL(trig_repeat_flag,'N')
4467 ,orig_csch_id
4468 FROM ams_campaign_schedules_b
4469 WHERE schedule_id = p_schedule_id;
4470
4471 CURSOR c_sch_det_offer(p_schedule_id NUMBER) IS -- anchaudh added this for bug#4957178.
4472 select offer_id
4473 from
4474 OZF_ACT_OFFERS ACT_OFFER,
4475 ozf_offers off
4476 where
4477 ACT_OFFER.ARC_ACT_OFFER_USED_BY = 'CSCH'
4478 AND ACT_OFFER.act_offer_used_by_id = p_schedule_id
4479 AND off.qp_list_header_id = ACT_OFFER.qp_list_header_id
4480 AND ACT_OFFER.PRIMARY_OFFER_FLAG = 'Y';
4481
4482 -- soagrawa 09-dec-2005 added this cursor for bug 4461415
4483 cursor c_parties(p_obj_id NUMBER) is
4484 select decode(pa.party_type,'PARTY_RELATIONSHIP','ORGANIZATION','PERSON') party_type,
4485 decode(pa.party_type,'PARTY_RELATIONSHIP',rel.subject_id,null) contact_party_id,
4486 decode(pa.party_type,'PARTY_RELATIONSHIP',TO_NUMBER(le.col147),le.party_id) main_party_id,
4487 decode(pa.party_type,'PARTY_RELATIONSHIP',le.party_id,null) rel_party_id
4488 from ams_act_lists la, ams_list_entries le, hz_parties pa, hz_relationships rel
4489 where la.list_header_id = le.list_header_id
4490 and la.list_act_type = 'TARGET'
4491 and la.list_used_by = 'CSCH'
4492 and la.list_used_by_id = p_obj_id
4493 and le.enabled_flag = 'Y'
4494 and le.party_id = pa.party_id
4495 and pa.party_id = rel.party_id(+)
4496 and rel.subject_type(+) = 'PERSON';
4497
4498 -- soagrawa 09-dec-2005 added this cursor for bug 4461415
4499 cursor c_lead_headers(srccd VARCHAR2) is
4500 SELECT IMPORT_INTERFACE_ID
4501 FROM as_import_interface
4502 where promotion_code = srccd;
4503
4504
4505 CURSOR c_batch_id IS
4506 SELECT as_sl_imp_batch_s.NEXTVAL
4507 FROM DUAL;
4508
4509 CURSOR c_lead_header_id_csr IS
4510 SELECT AS_IMPORT_INTERFACE_S.NEXTVAL
4511 FROM dual;
4512
4513 CURSOR c_lead_header_id_exists_csr (l_id IN NUMBER) IS
4514 SELECT 1 FROM dual
4515 WHERE EXISTS (SELECT 1 FROM as_import_interface
4516 WHERE import_interface_id = l_id);
4517
4518 CURSOR c_lead_line_id_csr IS
4519 SELECT AS_IMP_LINES_INTERFACE_S.NEXTVAL
4520 FROM dual;
4521
4522 CURSOR c_lead_line_id_exists_csr (l_id IN NUMBER) IS
4523 SELECT 1 FROM dual
4524 WHERE EXISTS (SELECT 1 FROM as_imp_lines_interface
4525 WHERE imp_lines_interface_id = l_id);
4526
4527 CURSOR c_loaded_rows_for_lead (batch_id_in IN NUMBER) IS
4528 SELECT COUNT(*)
4529 FROM as_import_interface
4530 WHERE batch_id = batch_id_in;
4531
4532 l_assoc_product_row c_assoc_products_csr%ROWTYPE;
4533 l_schedule_details c_schedule_details_csr%ROWTYPE;
4534 l_contact_party_details c_party_relationships_csr%ROWTYPE;
4535
4536 TYPE Lead_Header_Id_Table IS TABLE OF as_import_interface.IMPORT_INTERFACE_ID%TYPE;
4537 l_lead_header_ids Lead_Header_Id_Table; -- no need to initialize
4538
4539 TYPE Main_Party_Id_Table IS TABLE OF hz_parties.PARTY_ID%TYPE;
4540 l_main_party_ids Main_Party_Id_Table; -- no need to initialize
4541
4542 TYPE Contact_Point_Party_Id_Table IS TABLE OF hz_parties.PARTY_ID%TYPE;
4543 l_contact_point_party_ids Contact_Point_Party_Id_Table; -- no need to initialize
4544
4545 TYPE Rel_Party_Id_Table IS TABLE OF hz_parties.PARTY_ID%TYPE;
4546 l_rel_party_ids Rel_Party_Id_Table; -- no need to initialize
4547
4548 TYPE Party_Type_Table IS TABLE OF hz_parties.PARTY_TYPE%TYPE;
4549 l_party_types Party_Type_Table; -- no need to initialize
4550
4551 l_return_status VARCHAR2(1);
4552 l_party_id NUMBER;
4553 l_org_id VARCHAR2(500);
4554 l_asn_group_id VARCHAR2(500);
4555 l_num_asn_group_id NUMBER;
4556 l_num_asn_resource_id NUMBER;
4557 l_asn_resource_id NUMBER;
4558 l_contact_id NUMBER;
4559 l_rel_party_id NUMBER;
4560 l_party_type VARCHAR2(30);
4561 l_no_of_prods NUMBER := 0;
4562 l_batch_id NUMBER;
4563 l_loaded_rows NUMBER;
4564 l_no_of_tgrp_entries NUMBER := 0;
4565 l_method_id NUMBER;
4566 l_request_id NUMBER;
4567 j NUMBER;
4568
4569 l_lead_header_id NUMBER;
4570 l_lead_line_id NUMBER;
4571 l_dummy NUMBER;
4572
4573 -- soagrawa 09-dec-2005 added this variable for bug 4461415
4574 l_batch_size NUMBER := 1000;
4575
4576 BEGIN
4577 x_return_status := FND_API.g_ret_sts_success;
4578
4579 IF (AMS_DEBUG_HIGH_ON) THEN
4580 AMS_Utility_PVT.debug_message('generate_leads: Enter');
4581 END IF;
4582
4583 IF(p_obj_type <> 'CSCH') THEN
4584
4585 IF (AMS_DEBUG_HIGH_ON) THEN
4586 AMS_UTILITY_Pvt.debug_message('generate_leads: Unsupported object type : '||p_obj_type);
4587 END IF;
4588
4589 x_return_status := FND_API.g_ret_sts_error;
4590 return;
4591 END IF;
4592
4593 AMS_Utility_PVT.Create_Log (
4594 x_return_status => l_return_status,
4595 p_arc_log_used_by => 'CSCH',
4596 p_log_used_by_id => p_obj_id,
4597 p_msg_data => 'Starting lead generation process schedule id is ' || to_char(p_obj_id),
4598 p_msg_type => 'DEBUG'
4599 );
4600
4601 OPEN c_sch_det_offer(p_obj_id);
4602 FETCH c_sch_det_offer INTO l_csch_offer_id;
4603 CLOSE c_sch_det_offer;
4604
4605
4606 OPEN c_sch_det(p_obj_id); -- anchaudh added for the leads bug.
4607 FETCH c_sch_det INTO l_triggerable_flag,l_trig_repeat_flag,l_orig_csch_id;
4608 CLOSE c_sch_det;
4609
4610 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.
4611
4612 l_asn_group_id := WF_ENGINE.GetItemAttrText(
4613 itemtype => itemtype,
4614 itemkey => itemkey ,
4615 aname => 'ASN_GROUP_ID');--anchaudh added for the leads bug.
4616
4617 --insert_log_mesg('Anirban inside generate_leads api, value of l_asn_group_id retrieved is :'||l_asn_group_id);
4618
4619 l_asn_resource_id := WF_ENGINE.GetItemAttrText(
4620 itemtype => itemtype,
4621 itemkey => itemkey ,
4622 aname => 'ASN_RESOURCE_ID');--anchaudh added for the leads bug.
4623 --insert_log_mesg('Anirban inside generate_leads api, value of l_asn_resource_id retrieved is :'||l_asn_resource_id);
4624
4625 end if;
4626
4627 OPEN c_schedule_details_csr(p_obj_id);
4628 FETCH c_schedule_details_csr INTO l_schedule_details;
4629 IF(c_schedule_details_csr%NOTFOUND) THEN
4630 IF (AMS_DEBUG_HIGH_ON) THEN
4631 AMS_UTILITY_Pvt.debug_message('generate_leads: No Schedule details found for '||TO_CHAR(p_obj_id));
4632 END IF;
4633 CLOSE c_schedule_details_csr;
4634 x_return_status := FND_API.g_ret_sts_error;
4635 return;
4636 END IF;
4637 CLOSE c_schedule_details_csr;
4638
4639 OPEN c_batch_id;
4640 FETCH c_batch_id INTO l_batch_id;
4641 CLOSE c_batch_id;
4642 IF (AMS_DEBUG_HIGH_ON) THEN
4643 AMS_UTILITY_Pvt.debug_message('generate_leads: generated batch id: '||TO_CHAR(l_batch_id));
4644 END IF;
4645
4646 -- bulk collect party related info
4647 -- soagrawa 09-dec-2005 added LIMIT on this bulk collect bug 4461415 and moved it to a cursor
4648
4649 open c_parties(p_obj_id);
4650
4651 loop
4652 fetch c_parties
4653 BULK COLLECT INTO l_party_types, l_contact_point_party_ids, l_main_party_ids, l_rel_party_ids
4654 LIMIT l_batch_size;
4655
4656 AMS_Utility_PVT.Create_Log (
4657 x_return_status => l_return_status,
4658 p_arc_log_used_by => 'CSCH',
4659 p_log_used_by_id => p_obj_id,
4660 p_msg_data => 'Lead interface processing ' || l_main_party_ids.count,
4661 p_msg_type => 'DEBUG'
4662 );
4663
4664 --now generate lead headers for all parties by bulk insert
4665 FORALL j IN l_main_party_ids.FIRST..l_main_party_ids.LAST
4666 -- insert in as_import_interface based on target group entry details
4667 INSERT INTO as_import_interface
4668 (
4669 IMPORT_INTERFACE_ID --NOT NULL NUMBER
4670 , LAST_UPDATE_DATE --NOT NULL DATE
4671 , LAST_UPDATED_BY --NOT NULL NUMBER
4672 , CREATION_DATE --NOT NULL DATE
4673 , CREATED_BY --NOT NULL NUMBER
4674 , LAST_UPDATE_LOGIN --NOT NULL NUMBER
4675 , LOAD_TYPE -- VARCHAR2(20)
4676 , LOAD_DATE --NOT NULL DATE
4677 , PROMOTION_CODE -- VARCHAR2(50)
4678 , STATUS_CODE -- VARCHAR2(30)
4679 , SOURCE_SYSTEM -- VARCHAR2(30)
4680 , PARTY_TYPE -- VARCHAR2(30)
4681 , BATCH_ID -- NUMBER(15)
4682 , PARTY_ID -- NUMBER(15)
4683 , PARTY_SITE_ID -- NUMBER(15)
4684 ,load_status -- VARCHAR2(20)
4685 ,contact_party_id -- NUMBER
4686 ,vehicle_response_code
4687 ,qualified_flag
4688 ,sales_methodology_id -- NUMBER
4689 ,rel_party_id
4690 ,offer_id --anchaudh added for bug#4957178
4691 )
4692 VALUES
4693 (
4694 AS_IMPORT_INTERFACE_S.NEXTVAL --IMPORT_INTERFACE_ID --NOT NULL NUMBER
4695 , SYSDATE --LAST_UPDATE_DATE --NOT NULL DATE
4696 , FND_GLOBAL.user_id --LAST_UPDATED_BY --NOT NULL NUMBER
4697 , SYSDATE --CREATION_DATE --NOT NULL DATE
4698 , FND_GLOBAL.user_id --CREATED_BY --NOT NULL NUMBER
4699 , FND_GLOBAL.conc_login_id --LAST_UPDATE_LOGIN --NOT NULL NUMBER
4700 , 'LEAD_LOAD' --LOAD_TYPE -- VARCHAR2(20)
4701 , SYSDATE --LOAD_DATE --NOT NULL DATE
4702 , l_schedule_details.source_code --PROMOTION_CODE -- VARCHAR2(50)
4703 , null --STATUS_CODE -- VARCHAR2(30)
4704 , 'SALES_CAMPAIGN' --SOURCE_SYSTEM -- VARCHAR2(30)
4705 , l_party_types(j) --PARTY_TYPE -- VARCHAR2(30)
4706 , l_batch_id --BATCH_ID -- NUMBER(15)
4707 , l_main_party_ids(j) --PARTY_ID -- NUMBER(15)
4708 , NULL --PARTY_SITE_ID -- NUMBER(15)
4709 ,'NEW' -- load_status -- VARCHAR2(20)
4710 , l_contact_point_party_ids(j) -- contact party id, subject id for relationship -- NUMBER
4711 , 'SALES'
4712 , 'Y'
4713 ,l_schedule_details.sales_methodology_id -- sales methodology id NUMBER
4714 ,l_rel_party_ids(j) -- relationship party id
4715 ,l_csch_offer_id -- primary offer id --anchaudh added for bug#4957178
4716 );
4717
4718 exit when c_parties%notfound;
4719
4720 end loop;
4721
4722 close c_parties;
4723
4724 IF (AMS_DEBUG_HIGH_ON) THEN
4725 AMS_Utility_PVT.debug_message('generate_leads: insertion done in lead interface tables');
4726 END IF;
4727
4728
4729
4730
4731
4732
4733 -- bulk collect lead header ids for lead lines
4734 -- soagrawa 09-dec-2005 added LIMIT on this bulk collect bug 4461415 and moved it to a cursor
4735
4736 OPEN c_lead_headers(l_schedule_details.source_code);
4737
4738 LOOP
4739 FETCH c_lead_headers
4740 BULK COLLECT INTO l_lead_header_ids
4741 LIMIT l_batch_size;
4742
4743 --open products cursor and collects associated product info
4744 l_no_of_prods := 0;
4745 OPEN c_assoc_products_csr(p_obj_id);
4746 LOOP
4747 FETCH c_assoc_products_csr INTO l_assoc_product_row;
4748 EXIT WHEN c_assoc_products_csr%NOTFOUND;
4749
4750 l_no_of_prods := l_no_of_prods+1;
4751
4752 FORALL j IN l_lead_header_ids.FIRST..l_lead_header_ids.LAST
4753 --bulk insert each Product/Product Category in as_imp_lines_interface table
4754 INSERT INTO as_imp_lines_interface
4755 (
4756 IMP_LINES_INTERFACE_ID --NOT NULL NUMBER
4757 , IMPORT_INTERFACE_ID --NOT NULL NUMBER
4758 , LAST_UPDATE_DATE --NOT NULL DATE
4759 , LAST_UPDATED_BY --NOT NULL NUMBER
4760 , CREATION_DATE --NOT NULL DATE
4761 , CREATED_BY --NOT NULL NUMBER
4762 , LAST_UPDATE_LOGIN --NOT NULL NUMBER
4763 , CATEGORY_ID --NOT NULL NUMBER
4764 , INVENTORY_ITEM_ID --NUMBER
4765 , ORGANIZATION_ID --NUMBER
4766 , SOURCE_PROMOTION_ID --NUMBER
4767 )
4768 VALUES
4769 (
4770 AS_IMP_LINES_INTERFACE_S.NEXTVAL --IMP_LINES_INTERFACE_ID --NOT NULL NUMBER
4771 , l_lead_header_ids(j) --IMPORT_INTERFACE_ID --NOT NULL NUMBER
4772 , SYSDATE --LAST_UPDATE_DATE --NOT NULL DATE
4773 , FND_GLOBAL.user_id --LAST_UPDATED_BY --NOT NULL NUMBER
4774 , SYSDATE --CREATION_DATE --NOT NULL DATE
4775 , FND_GLOBAL.user_id --CREATED_BY --NOT NULL NUMBER
4776 , FND_GLOBAL.conc_login_id --LAST_UPDATE_LOGIN --NOT NULL NUMBER
4777 ,l_assoc_product_row.category_id
4778 ,l_assoc_product_row.inventory_item_id
4779 ,l_assoc_product_row.organization_id
4780 ,l_schedule_details.source_code_id
4781 );
4782
4783 END LOOP; -- for products
4784 CLOSE c_assoc_products_csr;
4785
4786 exit when c_lead_headers%notfound;
4787
4788 end loop;
4789
4790 close c_lead_headers;
4791
4792
4793
4794 IF (AMS_DEBUG_HIGH_ON) THEN
4795 AMS_UTILITY_Pvt.debug_message('generate_leads: No. of Products/Categories : '||TO_CHAR(l_no_of_prods));
4796 END IF;
4797
4798 -- At this point we will have added all the records in as_import_interface table.
4799 -- Now we can call the concurrent program for lead process.
4800 OPEN c_loaded_rows_for_lead(l_batch_id);
4801 FETCH c_loaded_rows_for_lead INTO l_loaded_rows;
4802 CLOSE c_loaded_rows_for_lead;
4803
4804 IF (AMS_DEBUG_HIGH_ON) THEN
4805 AMS_Utility_PVT.debug_message('generate_leads: No of lead header rows created : '||TO_CHAR(l_loaded_rows));
4806 END IF;
4807
4808 AMS_Utility_PVT.Create_Log (
4809 x_return_status => l_return_status,
4810 p_arc_log_used_by => 'CSCH',
4811 p_log_used_by_id => p_obj_id,
4812 p_msg_data => 'No. of lead headers generated '||TO_CHAR(l_loaded_rows),
4813 p_msg_type => 'DEBUG'
4814 );
4815
4816 l_request_id := 0;
4817
4818 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.
4819 l_num_asn_resource_id := to_number(l_asn_resource_id);
4820 if(l_asn_group_id = '9999') then
4821 l_num_asn_group_id := null;
4822 else
4823 l_num_asn_group_id := to_number(l_asn_group_id);
4824 end if;
4825 else
4826 l_num_asn_group_id := null;
4827 l_num_asn_resource_id := null;
4828 end if;
4829
4830 --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);
4831
4832 -- Call the concurrent program for leads.
4833 l_request_id := FND_REQUEST.SUBMIT_REQUEST (
4834 application => 'AS',
4835 program => 'ASXSLIMP',
4836 argument1 => 'SALES_CAMPAIGN',
4837 argument2 => 'N',
4838 --argument2 => NULL,
4839 argument3 => l_batch_id,
4840 argument4 => 'N',
4841 argument5 => null,
4842 argument6 => null,
4843 argument7 => l_num_asn_resource_id,--anchaudh added for the leads bug.
4844 argument8 => l_num_asn_group_id--anchaudh added for the leads bug.
4845 );
4846
4847 --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);
4848
4849 AMS_Utility_PVT.Create_Log (
4850 x_return_status => l_return_status,
4851 p_arc_log_used_by => 'CSCH',
4852 p_log_used_by_id => p_obj_id,
4853 p_msg_data => 'Starting LEAD program (ASXSLIMP) -- concurrent program_id is ' || to_char(l_request_id) ||' for batch id '||TO_CHAR(l_batch_id),
4854 p_msg_type => 'DEBUG'
4855 );
4856
4857 IF l_request_id = 0 THEN
4858 l_msg_data := fnd_message.get;
4859 AMS_Utility_PVT.Create_Log (
4860 x_return_status => l_return_status,
4861 p_arc_log_used_by => 'CSCH',
4862 p_log_used_by_id => p_obj_id,
4863 p_msg_data => l_msg_data,
4864 p_msg_type => 'DEBUG'
4865 );
4866 x_return_status := FND_API.g_ret_sts_error;
4867 --insert_log_mesg('Anirban inside generate_leads api,ERROR occured in the conc. program. ');
4868 return;
4869 END IF;
4870
4871 -- Import completed successfully
4872 IF (AMS_DEBUG_HIGH_ON) THEN
4873 AMS_Utility_PVT.debug_message('generate_leads: Submitted Lead import request with request id : '||TO_CHAR(l_request_id));
4874 AMS_Utility_PVT.debug_message('generate_leads: End');
4875 END IF;
4876
4877 END generate_leads;
4878
4879
4880
4881
4882
4883
4884 --=====================================================================
4885 -- PROCEDURE
4886 -- Check_WF_Error
4887 --
4888 -- PURPOSE
4889 -- This api will be used by schedule execution workflow to check error
4890 -- The api will check the error flag and based on the value, the error
4891 -- notifications will be sent to schedule owner.
4892 --
4893 -- HISTORY
4894 -- 23-Aug-2003 ptendulk Created.
4895 -- 19-Sep-2003 dbiswas Added nocopy
4896 --=====================================================================
4897 PROCEDURE Check_WF_Error(itemtype IN VARCHAR2,
4898 itemkey IN VARCHAR2,
4899 actid IN NUMBER,
4900 funcmode IN VARCHAR2,
4901 result OUT NOCOPY VARCHAR2) IS
4902 l_error_flag VARCHAR2(30) ;
4903 l_return_status VARCHAR2(1);
4904 l_schedule_id NUMBER;
4905 BEGIN
4906 -- dbms_output.put_line('Process Check_Repeat');
4907 -- RUN mode - Normal Process Execution
4908 IF (funcmode = 'RUN')
4909 THEN
4910 l_schedule_id := WF_ENGINE.GetItemAttrText(
4911 itemtype => itemtype,
4912 itemkey => itemkey ,
4913 aname => 'SCHEDULE_ID' );
4914
4915 AMS_Utility_PVT.Create_Log (
4916 x_return_status => l_return_status,
4917 p_arc_log_used_by => 'CSCH',
4918 p_log_used_by_id => l_schedule_id,
4919 p_msg_data => 'Check_WF_Error : started',
4920 p_msg_type => 'DEBUG'
4921 );
4922
4923 l_error_flag := WF_ENGINE.GetItemAttrText(
4924 itemtype => itemtype,
4925 itemkey => itemkey ,
4926 aname => 'ERROR_FLAG' );
4927
4928
4929 IF l_error_flag = 'N' THEN
4930 result := 'COMPLETE:N' ;
4931 ELSE
4932 result := 'COMPLETE:Y' ;
4933 END IF ;
4934 END IF;
4935
4936 -- CANCEL mode - Normal Process Execution
4937 IF (funcmode = 'CANCEL')
4938 THEN
4939 result := 'COMPLETE:Y' ;
4940 RETURN;
4941 END IF;
4942
4943 -- TIMEOUT mode - Normal Process Execution
4944 IF (funcmode = 'TIMEOUT')
4945 THEN
4946 result := 'COMPLETE:Y' ;
4947 RETURN;
4948 END IF;
4949 EXCEPTION
4950 WHEN OTHERS THEN
4951 wf_core.context(G_PKG_NAME,'Check_WF_Error',itemtype,itemkey,actid,funcmode);
4952 raise ;
4953 END Check_WF_Error ;
4954
4955 --========================================================================
4956 -- PROCEDURE
4957 -- WRITE_LOG
4958 -- Purpose
4959 -- This method will be used to write logs for this api
4960 -- HISTORY
4961 -- 10-Oct-2000 dbiswas Created.
4962 --
4963 --========================================================================
4964
4965 PROCEDURE WRITE_LOG ( p_api_name IN VARCHAR2 := NULL,
4966 p_log_message IN VARCHAR2 := NULL)
4967 IS
4968 l_api_name VARCHAR2(30);
4969 l_log_mesg VARCHAR2(2000);
4970 l_return_status VARCHAR2(1);
4971 BEGIN
4972 l_api_name := p_api_name;
4973 l_log_mesg := p_log_message;
4974 AMS_Utility_PVT.debug_message (
4975 p_log_level => g_log_level,
4976 p_module_name => 'ams.plsql.'||'.'|| g_pkg_name||'.'||l_api_name||'.'||l_log_mesg,
4977 p_text => p_log_message
4978 );
4979
4980 AMS_Utility_PVT.Create_Log (
4981 x_return_status => l_return_status,
4982 p_arc_log_used_by => 'CSCH',
4983 p_log_used_by_id => 1,
4984 p_msg_data => p_log_message,
4985 p_msg_type => 'DEBUG'
4986 );
4987
4988 END WRITE_LOG;
4989
4990 --=====================================================================
4991 -- Procedure
4992 -- WF_REPEAT_INIT_VAR
4993 --
4994 -- PURPOSE
4995 -- This api is used by scheduler workflow to initialize the attributes
4996 -- Returns the processId information in the schedules table
4997 --
4998 -- HISTORY
4999 -- 07-Oct-2003 dbiswas Created.
5000 -- 09-nov-2004 anchaudh Now setting item owner along with bug fix for bug# 3799053
5001 --=====================================================================
5002 PROCEDURE Wf_Repeat_Init_var(itemtype IN VARCHAR2,
5003 itemkey IN VARCHAR2,
5004 actid IN NUMBER,
5005 funcmode IN VARCHAR2,
5006 result OUT NOCOPY VARCHAR2) IS
5007
5008 CURSOR c_sched_dat (p_schedule_id IN NUMBER) IS
5009 SELECT csch.schedule_name,
5010 csch.start_date_time,
5011 csch.end_date_time,
5012 csch.status_code,
5013 csch.owner_user_id,
5014 csch.activity_id,
5015 csch.activity_type_code,
5016 nvl(csch.orig_csch_id, csch.schedule_id),
5017 scheduler.frequency,
5018 scheduler.frequency_type,
5019 camp.actual_exec_start_date,
5020 camp.actual_exec_end_date,
5021 parentCSCH.start_date_time,
5022 parentCSCH.end_date_time,
5023 parentCSCH.status_code
5024 FROM ams_campaign_schedules_vl csch,
5025 ams_scheduler scheduler,
5026 ams_campaigns_all_b camp,
5027 ams_campaign_Schedules_b parentCSCH
5028 WHERE csch.schedule_id = p_schedule_id
5029 AND scheduler.OBJECT_ID = nvl(csch.orig_csch_id, csch.schedule_id)
5030 AND scheduler.OBJECT_TYPE = 'CSCH'
5031 AND camp.campaign_id = csch.campaign_id
5032 and parentCSCH.schedule_id = nvl(csch.orig_Csch_id,csch.schedule_id);
5033
5034 CURSOR c_emp_dtl(l_res_id IN NUMBER) IS
5035 SELECT employee_id
5036 FROM ams_jtf_rs_emp_v
5037 WHERE resource_id = l_res_id ;
5038
5039 l_schedule_id NUMBER;
5040 l_schedule_name VARCHAR2(240);
5041 l_csch_st_date DATE;
5042 l_csch_en_date DATE;
5043 l_csch_status VARCHAR2(30);
5044 l_csch_owner NUMBER;
5045 l_csch_act_id NUMBER;
5046 l_csch_act_code VARCHAR2(30);
5047 l_csch_orig_id NUMBER;
5048 l_sched_freq NUMBER;
5049 l_sched_freq_type VARCHAR2(30);
5050 l_camp_st_date DATE;
5051 l_camp_en_date DATE;
5052 l_parent_st_date DATE;
5053 l_parent_en_date DATE;
5054 l_parent_status VARCHAR2(30);
5055 l_api_name VARCHAR2(30);
5056 l_return_status VARCHAR2(1);
5057 l_emp_id NUMBER;
5058 l_user_name VARCHAR2(100);
5059 l_display_name VARCHAR2(100);
5060
5061 l_temp_varaibale VARCHAR2(50);
5062 l_schedule_next_run_st_date DATE;
5063
5064
5065
5066 BEGIN
5067 l_api_name := 'Wf_Repeat_Init_var';
5068 IF (funcmode = 'RUN')
5069 THEN
5070
5071 l_schedule_id := WF_ENGINE.GetItemAttrText(
5072 itemtype => itemtype,
5073 itemkey => itemkey ,
5074 aname => 'SCHEDULE_ID');
5075
5076
5077
5078 l_temp_varaibale := WF_ENGINE.GetItemAttrText(itemtype => itemtype,
5079 itemkey => itemkey ,
5080 aname => 'AMS_PARENT_STATUS'
5081 );
5082
5083 l_schedule_next_run_st_date := to_date(l_temp_varaibale,'DD-MM-RRRR HH24:MI:SS');
5084
5085 WRITE_LOG (l_api_name, 'WF_REPEAT_INIT_VAR: SCHEDULE ID IS '||l_schedule_id
5086 || '|| SCHEDULED KICKOFF TIME: '||to_char(l_schedule_next_run_st_date,'DD-MON-RRRR HH24:MI:SS')
5087 || '|| CURRENT SYSTEM TIME: '||to_char(SYSDATE,'DD-MON-RRRR HH24:MI:SS'));
5088
5089 WRITE_LOG(l_api_name, 'WF_REPEAT_INIT_VAR: AMS_SCHEDULE_NEXT_RUN_ST_DATE DERIVED FROM PARAMETER LIST '
5090 ||'SCHEDULE ID RECEIVED IS:' || l_schedule_id
5091 ||to_char(l_schedule_next_run_st_date,'DD-MON-RRRR HH24:MI:SS')
5092 ||' ; '||'L_TEMP_VARAIBALE VALUE : '
5093 ||l_temp_varaibale);
5094
5095
5096
5097 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
5098 itemkey => itemkey,
5099 aname => 'AMS_SCHEDULE_ID',
5100 avalue => l_schedule_id);
5101
5102 UPDATE ams_campaign_schedules_b
5103 SET REPEAT_WORKFLOW_ITEM_KEY = itemkey
5104 WHERE schedule_id = l_schedule_id;
5105
5106 WRITE_LOG (l_api_name, 'Wf_Repeat_Init_var: Schedule id is '||l_schedule_id);
5107
5108 OPEN c_sched_dat(l_schedule_id);
5109 FETCH c_sched_dat INTO l_schedule_name,
5110 l_csch_st_date,
5111 l_csch_en_date,
5112 l_csch_status,
5113 l_csch_owner,
5114 l_csch_act_id,
5115 l_csch_act_code,
5116 l_csch_orig_id,
5117 l_sched_freq,
5118 l_sched_freq_type,
5119 l_camp_st_date,
5120 l_camp_en_date,
5121 l_parent_st_date,
5122 l_parent_en_date,
5123 l_parent_status
5124 ;
5125 CLOSE c_sched_dat;
5126
5127 OPEN c_emp_dtl(l_csch_owner);
5128 FETCH c_emp_dtl INTO l_emp_id;
5129 -- anchaudh setting item owner along with bug fix for bug# 3799053
5130 IF c_emp_dtl%FOUND
5131 THEN
5132 WF_DIRECTORY.getrolename
5133 ( p_orig_system => 'PER',
5134 p_orig_system_id => l_emp_id ,
5135 p_name => l_user_name,
5136 p_display_name => l_display_name );
5137
5138 IF l_user_name IS NOT NULL THEN
5139 Wf_Engine.SetItemOwner(itemtype => itemtype,
5140 itemkey => itemkey,
5141 owner => l_user_name);
5142 END IF;
5143 END IF;
5144 CLOSE c_emp_dtl;
5145
5146 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
5147 itemkey => itemkey,
5148 aname => 'SCHEDULE_NAME',
5149 avalue => l_schedule_name);
5150
5151 WF_ENGINE.SetItemUserkey(itemtype => itemtype,
5152 itemkey => itemkey ,
5153 userkey => l_schedule_name);
5154
5155 WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
5156 itemkey => itemkey,
5157 aname => 'AMS_SCHEDULE_START_DATE',
5158 avalue => to_date(l_csch_st_date,'DD-MM-RRRR HH24:MI:SS') );
5159
5160 WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
5161 itemkey => itemkey,
5162 aname => 'AMS_SCHEDULE_END_DATE',
5163 avalue => to_date(l_csch_en_date,'DD-MM-RRRR HH24:MI:SS') );
5164
5165 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
5166 itemkey => itemkey,
5167 aname => 'SCHEDULE_STATUS',
5168 avalue => l_csch_status);
5169
5170 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
5171 itemkey => itemkey,
5172 aname => 'SCHEDULE_OWNER',
5173 avalue => l_user_name);
5174
5175 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
5176 itemkey => itemkey,
5177 aname => 'SCHEDULE_CHANNEL',
5178 avalue => l_csch_act_id);
5179
5180 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
5181 itemkey => itemkey,
5182 aname => 'ACTIVITY_TYPE',
5183 avalue => l_csch_act_code);
5184
5185 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
5186 itemkey => itemkey,
5187 aname => 'AMS_ORIG_SCHEDULE_ID',
5188 avalue => l_csch_orig_id);
5189
5190 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
5191 itemkey => itemkey,
5192 aname => 'AMS_SCHEDULER_FREQUENCY',
5193 avalue => l_sched_freq);
5194
5195 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
5196 itemkey => itemkey,
5197 aname => 'AMS_SCHEDULER_FREQUENCY_TYPE',
5198 avalue => l_sched_freq_type);
5199
5200 WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
5201 itemkey => itemkey,
5202 aname => 'AMS_CAMPAIGN_START_DATE',
5203 avalue => to_date(l_camp_st_date,'DD-MM-RRRR HH24:MI:SS') );
5204
5205 WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
5206 itemkey => itemkey,
5207 aname => 'AMS_CAMPAIGN_END_DATE',
5208 avalue => to_date(l_camp_en_date,'DD-MM-RRRR HH24:MI:SS') );
5209
5210 WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
5211 itemkey => itemkey,
5212 aname => 'AMS_PARENT_START_DATE',
5213 avalue => to_date(l_parent_st_date,'DD-MM-RRRR HH24:MI:SS') );
5214
5215 WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
5216 itemkey => itemkey,
5217 aname => 'AMS_PARENT_END_DATE',
5218 avalue => to_date(l_parent_en_date,'DD-MM-RRRR HH24:MI:SS') );
5219
5220 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
5221 itemkey => itemkey,
5222 aname => 'AMS_PARENT_STATUS',
5223 avalue => l_parent_status);
5224
5225
5226 WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
5227 itemkey => itemkey ,
5228 aname => 'AMS_SCHEDULER_NEXT_RUN_ST_DATE',
5229 avalue => l_schedule_next_run_st_date);
5230
5231 END IF;
5232
5233 -- CANCEL mode - Normal Process Execution
5234 IF (funcmode = 'CANCEL')
5235 THEN
5236 RETURN;
5237 END IF;
5238
5239 -- TIMEOUT mode - Normal Process Execution
5240 IF (funcmode = 'TIMEOUT')
5241 THEN
5242 RETURN;
5243 END IF;
5244 -- dbms_output.put_line('End Check scheduler stat :'||result);
5245 EXCEPTION
5246 WHEN OTHERS THEN
5247 wf_core.context(G_PKG_NAME,'Wf_Repeat_Init_var',itemtype,itemkey,actid,funcmode);
5248 raise ;
5249 END Wf_Repeat_Init_var ;
5250
5251 --=====================================================================
5252 -- Procedure
5253 -- WF_REPEAT_CHECK_EXECUTE
5254 --
5255 -- PURPOSE
5256 -- This api is used by scheduler workflow to check if the schedule
5257 -- should execute or not based on status and dates
5258 --
5259 -- HISTORY
5260 -- 07-Oct-2003 dbiswas Created.
5261 --=====================================================================
5262 PROCEDURE Wf_Repeat_Check_Execute(itemtype IN VARCHAR2,
5263 itemkey IN VARCHAR2,
5264 actid IN NUMBER,
5265 funcmode IN VARCHAR2,
5266 result OUT NOCOPY VARCHAR2) IS
5267
5268 CURSOR c_sched_data (p_schedule_id IN NUMBER) IS
5269 SELECT nvl(csch.orig_csch_id, csch.schedule_id)
5270 FROM ams_campaign_schedules_vl csch
5271 WHERE csch.schedule_id = p_schedule_id;
5272
5273
5274 l_schedule_id NUMBER;
5275 l_csch_orig_id NUMBER;
5276 l_sched_end_date DATE;
5277 l_sched_status VARCHAR2(30);
5278 l_orig_csch_id NUMBER;
5279 l_camp_end_date DATE;
5280 l_orig_csch_end_date DATE;
5281 l_orig_csch_status VARCHAR2(30);
5282 l_api_name VARCHAR2(30);
5283 l_return_status VARCHAR2(1);
5284
5285 BEGIN
5286 l_api_name := 'WF_REPEAT_CHECK_EXECUTE';
5287
5288 l_schedule_id := WF_ENGINE.GetItemAttrText(
5289 itemtype => itemtype,
5290 itemkey => itemkey ,
5291 aname => 'SCHEDULE_ID');
5292
5293
5294 OPEN c_sched_data(l_schedule_id);
5295 FETCH c_sched_data INTO l_csch_orig_id ;
5296 CLOSE c_sched_data;
5297
5298
5299 IF (funcmode = 'RUN')
5300 THEN
5301 WRITE_LOG(l_api_name, 'Wf_Repeat_Check_Execute: Schedule id is '||l_schedule_id);
5302
5303 l_sched_status := WF_ENGINE.GetItemAttrText(
5304 itemtype => itemtype,
5305 itemkey => itemkey ,
5306 aname => 'SCHEDULE_STATUS');
5307
5308 l_sched_end_date := WF_ENGINE.GetItemAttrDate(
5309 itemtype => itemtype,
5310 itemkey => itemkey ,
5311 aname => 'AMS_SCHEDULE_END_DATE');
5312
5313 l_orig_csch_status := WF_ENGINE.GetItemAttrText(
5314 itemtype => itemtype,
5315 itemkey => itemkey ,
5316 aname => 'AMS_PARENT_STATUS');
5317
5318 l_orig_csch_end_date := WF_ENGINE.GetItemAttrDate(
5319 itemtype => itemtype,
5320 itemkey => itemkey ,
5321 aname => 'AMS_PARENT_END_DATE');
5322
5323
5324 l_camp_end_date := WF_ENGINE.GetItemAttrDate(
5325 itemtype => itemtype,
5326 itemkey => itemkey ,
5327 aname => 'AMS_CAMPAIGN_END_DATE');
5328
5329
5330
5331 WRITE_LOG(l_api_name, 'Wf_Repeat_Check_Execute: Schedule id is '||l_schedule_id||
5332 '|| SCHEDULE_STATUS is: '||l_sched_status||
5333 '|| AMS_SCHEDULE_END_DATE is: '||l_sched_end_date||
5334 '|| AMS_PARENT_STATUS is: '||l_orig_csch_status||
5335 '|| AMS_PARENT_END_DATE is: '||l_orig_csch_end_date||
5336 '|| AMS_CAMPAIGN_END_DATE is: '||l_camp_end_date);
5337
5338
5339 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.
5340
5341 IF (l_sched_status = 'AVAILABLE' or l_sched_status = 'ACTIVE')
5342 THEN
5343 IF (l_orig_csch_status = 'AVAILABLE' or l_orig_csch_status = 'ACTIVE')
5344 THEN
5345 IF (nvl(l_orig_csch_end_date, l_camp_end_date) >=SYSDATE)
5346 THEN
5347 result := 'COMPLETE:Y' ;
5348 ELSE
5349 WRITE_LOG (l_api_name, 'Wf_Repeat_Check_Execute: returns out of bounds for exec date for schedule id '||l_schedule_id);
5350 result := 'COMPLETE:N';
5351 END IF;
5352 END IF;
5353 END IF;
5354 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.
5355 result := 'COMPLETE:Y';
5356 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.
5357
5358 END IF; --funcmode RUN
5359 -- CANCEL mode - Normal Process Execution
5360 IF (funcmode = 'CANCEL')
5361 THEN
5362 RETURN;
5363 END IF;
5364
5365 -- TIMEOUT mode - Normal Process Execution
5366 IF (funcmode = 'TIMEOUT')
5367 THEN
5368 RETURN;
5369 END IF;
5370 -- dbms_output.put_line('End Check scheduler stat :'||result);
5371 EXCEPTION
5372 WHEN OTHERS THEN
5373 wf_core.context(G_PKG_NAME,'Wf_Repeat_Check_Exec',itemtype,itemkey,actid,funcmode);
5374 raise ;
5375 END Wf_Repeat_Check_Execute;
5376
5377 --=====================================================================
5378 -- Procedure
5379 -- WF_REPEAT_SCHEDULER
5380 --
5381 -- PURPOSE
5382 -- This api is used by scheduler workflow to check when the next schedule run should be
5383 --
5384 -- HISTORY
5385 -- 07-Oct-2003 dbiswas Created.
5386 --=====================================================================
5387 PROCEDURE Wf_Repeat_Scheduler(itemtype IN VARCHAR2,
5388 itemkey IN VARCHAR2,
5389 actid IN NUMBER,
5390 funcmode IN VARCHAR2,
5391 result OUT NOCOPY VARCHAR2) IS
5392
5393 l_schedule_id NUMBER;
5394 l_scheduler_frequency NUMBER;
5395 l_scheduler_frequency_type VARCHAR2(30);
5396 l_scheduler_next_run_date DATE;
5397 l_api_name VARCHAR2(30);
5398 l_msg_count NUMBER;
5399 l_msg_data VARCHAR2(2000);
5400
5401 l_return_status VARCHAR2(1);
5402
5403 l_new_last_run_date DATE;
5404 l_orig_csch_id NUMBER;
5405
5406
5407 BEGIN
5408 l_api_name := 'WF_REPEAT_SCHEDULER';
5409 IF (funcmode = 'RUN')
5410 THEN
5411
5412 l_schedule_id := WF_ENGINE.GetItemAttrText(
5413 itemtype => itemtype,
5414 itemkey => itemkey ,
5415 aname => 'SCHEDULE_ID');
5416 WRITE_LOG(l_api_name, 'Wf_Repeat_Scheduler: Schedule id ' ||l_schedule_id);
5417
5418 l_scheduler_frequency := WF_ENGINE.GetItemAttrNumber(
5419 itemtype => itemtype,
5420 itemkey => itemkey ,
5421 aname => 'AMS_SCHEDULER_FREQUENCY');
5422
5423 l_scheduler_frequency_type := WF_ENGINE.GetItemAttrText(
5424 itemtype => itemtype,
5425 itemkey => itemkey ,
5426 aname => 'AMS_SCHEDULER_FREQUENCY_TYPE');
5427
5428 WRITE_LOG(l_api_name, 'Wf_Repeat_Scheduler: Calling AMS_SCHEDULER_PVT.Schedule_Repeat with freq type ' ||l_scheduler_frequency_type);
5429
5430
5431 l_new_last_run_date := WF_ENGINE.GetItemAttrDate(
5432 itemtype => itemtype,
5433 itemkey => itemkey ,
5434 aname => 'AMS_SCHEDULER_NEXT_RUN_ST_DATE');
5435
5436
5437
5438 SELECT nvl(orig_csch_id, schedule_id)
5439 INTO l_orig_csch_id
5440 FROM ams_campaign_schedules_b
5441 WHERE schedule_id = l_schedule_id;
5442
5443 IF l_new_last_run_date IS NULL then
5444
5445 l_new_last_run_date := SYSDATE;
5446 ELSIF l_new_last_run_date = '' then
5447 l_new_last_run_date := SYSDATE;
5448 END IF;
5449
5450
5451
5452 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);
5453
5454
5455 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);
5456
5457
5458 AMS_SCHEDULER_PVT.Schedule_Repeat (
5459 p_last_run_date => SYSDATE,
5460 p_frequency => l_scheduler_frequency,
5461 p_frequency_type => l_scheduler_frequency_type,
5462 x_next_run_date => l_scheduler_next_run_date,
5463 x_return_status => l_return_status,
5464 x_msg_count => l_msg_count,
5465 x_msg_data => l_msg_data);
5466
5467
5468 IF l_return_status = FND_API.G_RET_STS_SUCCESS
5469 THEN
5470 WRITE_LOG(l_api_name, 'Wf_Repeat_Scheduler: Schedule_Repeat returned success for next run date for schedule id: '||l_schedule_id);
5471 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);
5472
5473 WF_ENGINE.SetItemAttrDate(itemtype => itemtype,
5474 itemkey => itemkey ,
5475 aname => 'AMS_SCHEDULER_NEXT_RUN_ST_DATE',
5476 avalue => l_scheduler_next_run_date);
5477
5478 result := 'COMPLETE:SUCCESS' ;
5479 ELSIF l_return_status <> FND_API.G_RET_STS_SUCCESS
5480 THEN
5481 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
5482 itemkey => itemkey,
5483 aname => 'ERROR_FLAG',
5484 avalue => 'Y');
5485 Handle_Error(p_itemtype => itemtype,
5486 p_itemkey => itemkey,
5487 p_msg_count => l_msg_count,
5488 p_msg_data => l_msg_data,
5489 p_wf_err_attrib => 'AMS_SCHEDULER_ERROR_MSG');
5490
5491 WRITE_LOG(l_api_name, 'Error in scheduling next run start date caught for schedule id: '||l_schedule_id);
5492 result := 'COMPLETE:ERROR' ;
5493
5494 END IF ;
5495 END IF;
5496
5497 IF (funcmode = 'CANCEL')
5498 THEN
5499 result := 'COMPLETE:' ;
5500 RETURN;
5501 END IF;
5502
5503 IF (funcmode = 'TIMEOUT')
5504 THEN
5505 result := 'COMPLETE:' ;
5506 RETURN;
5507 END IF;
5508 EXCEPTION
5509 WHEN OTHERS THEN
5510 wf_core.context(G_PKG_NAME,l_api_name,itemtype,itemkey,actid,funcmode);
5511 raise ;
5512
5513 END Wf_Repeat_Scheduler ;
5514
5515 --=====================================================================
5516 -- Procedure
5517 -- WF_REPEAT_CHECK_CREATE_CSCH
5518 --
5519 -- PURPOSE
5520 -- This api is used by scheduler workflow to check whether to create the next child schedule
5521 -- based on schedule date boundaries. (campaign end date in case parent's end date is null
5522 --
5523 -- HISTORY
5524 -- 07-Oct-2003 dbiswas Created.
5525 --=====================================================================
5526 PROCEDURE WF_REPEAT_CHECK_CREATE_CSCH(itemtype IN VARCHAR2,
5527 itemkey IN VARCHAR2,
5528 actid IN NUMBER,
5529 funcmode IN VARCHAR2,
5530 result OUT NOCOPY VARCHAR2) IS
5531
5532 l_schedule_id NUMBER;
5533 l_schedule_next_run_date DATE;
5534 l_parent_end_date DATE;
5535 l_campaign_end_date DATE;
5536 l_api_name VARCHAR2(30);
5537
5538 l_return_status VARCHAR2(1);
5539 BEGIN
5540 l_api_name := 'WF_REPEAT_CHECK_CREATE_CSCH';
5541 IF (funcmode = 'RUN')
5542 THEN
5543
5544 l_schedule_id := WF_ENGINE.GetItemAttrText(
5545 itemtype => itemtype,
5546 itemkey => itemkey ,
5547 aname => 'SCHEDULE_ID');
5548 WRITE_LOG(l_api_name, 'WF_REPEAT_CHECK_CREATE_CSCH: Started for schedule id ' ||l_schedule_id);
5549
5550 l_schedule_next_run_date := WF_ENGINE.GetItemAttrDate(
5551 itemtype => itemtype,
5552 itemkey => itemkey ,
5553 aname => 'AMS_SCHEDULER_NEXT_RUN_ST_DATE');
5554
5555 l_parent_end_date := WF_ENGINE.GetItemAttrDate(
5556 itemtype => itemtype,
5557 itemkey => itemkey ,
5558 aname => 'AMS_PARENT_END_DATE');
5559
5560 l_campaign_end_date := WF_ENGINE.GetItemAttrDate(
5561 itemtype => itemtype,
5562 itemkey => itemkey ,
5563 aname => 'AMS_CAMPAIGN_END_DATE');
5564
5565 IF (nvl(l_parent_end_date, l_campaign_end_date) > l_schedule_next_run_date)
5566 THEN
5567 result := 'COMPLETE:Y' ;
5568 ELSE
5569 result := 'COMPLETE:N' ;
5570 END IF ;
5571
5572 END IF ; -- end func mode
5573
5574 IF (funcmode = 'CANCEL')
5575 THEN
5576 result := 'COMPLETE:' ;
5577 RETURN;
5578 END IF;
5579
5580 IF (funcmode = 'TIMEOUT')
5581 THEN
5582 result := 'COMPLETE:' ;
5583 RETURN;
5584 END IF;
5585 EXCEPTION
5586 WHEN OTHERS THEN
5587 wf_core.context(G_PKG_NAME,l_api_name,itemtype,itemkey,actid,funcmode);
5588 raise ;
5589
5590 END WF_REPEAT_CHECK_CREATE_CSCH ;
5591
5592 --=====================================================================
5593 -- Procedure
5594 -- WF_REPEAT_CREATE_CSCH
5595 --
5596 -- PURPOSE
5597 -- This api is used by scheduler workflow to create the next child schedule
5598 --
5599 -- HISTORY
5600 -- 11-Oct-2003 dbiswas Created.
5601 --=====================================================================
5602 PROCEDURE WF_REPEAT_CREATE_CSCH(itemtype IN VARCHAR2,
5603 itemkey IN VARCHAR2,
5604 actid IN NUMBER,
5605 funcmode IN VARCHAR2,
5606 result OUT NOCOPY VARCHAR2) IS
5607
5608 l_schedule_id NUMBER;
5609 l_schedule_start_date DATE;
5610 l_schedule_end_date DATE;
5611 l_scheduler_frequency NUMBER;
5612 l_scheduler_frequency_type VARCHAR2(30);
5613 l_parent_sched_id NUMBER;
5614 l_parent_end_date DATE;
5615 l_campaign_end_date DATE;
5616 l_api_name VARCHAR2(30);
5617 l_msg_count NUMBER;
5618 l_msg_data VARCHAR2(2000);
5619 l_return_status VARCHAR2(1);
5620 l_usr_start_time DATE;
5621 l_start_time DATE;
5622 l_timezone NUMBER;
5623 l_child_sched_id NUMBER;
5624
5625 -- CURSOR c_sch_det (p_schedule_id NUMBER) IS
5626 -- SELECT start_date_time, timezone_id
5627 -- FROM ams_campaign_schedules_b
5628 -- WHERE schedule_id = p_schedule_id;
5629
5630 BEGIN
5631 l_api_name := 'WF_REPEAT_CREATE_CSCH';
5632 l_schedule_id := WF_ENGINE.GetItemAttrText(
5633 itemtype => itemtype,
5634 itemkey => itemkey ,
5635 aname => 'SCHEDULE_ID');
5636
5637 IF (funcmode = 'RUN')
5638 THEN
5639 WRITE_LOG(l_api_name, 'WF_REPEAT_CREATE_CSCH: Schedule id is '||l_schedule_id);
5640
5641 l_parent_sched_id := WF_ENGINE.GetItemAttrNumber(
5642 itemtype => itemtype,
5643 itemkey => itemkey ,
5644 aname => 'AMS_ORIG_SCHEDULE_ID');
5645
5646 l_campaign_end_date := WF_ENGINE.GetItemAttrDate(
5647 itemtype => itemtype,
5648 itemkey => itemkey ,
5649 aname => 'AMS_CAMPAIGN_END_DATE');
5650
5651 l_parent_end_date := WF_ENGINE.GetItemAttrDate(
5652 itemtype => itemtype,
5653 itemkey => itemkey ,
5654 aname => 'AMS_PARENT_END_DATE');
5655
5656 l_schedule_start_date := WF_ENGINE.GetItemAttrDate(
5657 itemtype => itemtype,
5658 itemkey => itemkey ,
5659 aname => 'AMS_SCHEDULER_NEXT_RUN_ST_DATE');
5660
5661 l_scheduler_frequency := WF_ENGINE.GetItemAttrNumber(
5662 itemtype => itemtype,
5663 itemkey => itemkey ,
5664 aname => 'AMS_SCHEDULER_FREQUENCY');
5665
5666 l_scheduler_frequency_type := WF_ENGINE.GetItemAttrText(
5667 itemtype => itemtype,
5668 itemkey => itemkey ,
5669 aname => 'AMS_SCHEDULER_FREQUENCY_TYPE');
5670
5671 --anchaudh: commented out on 11 Jun '05 to fix bug#4477717 .
5672 /*AMS_UTILITY_PVT.Convert_Timezone(
5673 p_init_msg_list => FND_API.G_TRUE,
5674 x_return_status => l_return_status,
5675 x_msg_count => l_msg_count,
5676 x_msg_data => l_msg_data,
5677 p_user_tz_id => l_timezone,
5678 p_in_time => l_schedule_start_date,
5679 p_convert_type => 'USER',
5680 x_out_time => l_usr_start_time
5681 );
5682
5683 AMS_SCHEDULER_PVT.Schedule_Repeat(
5684 p_last_run_date => l_usr_start_time,
5685 p_frequency => l_scheduler_frequency,
5686 p_frequency_type => l_scheduler_frequency_type,
5687 x_next_run_date => l_schedule_end_date,
5688 x_return_status => l_return_status,
5689 x_msg_count => l_msg_count,
5690 x_msg_data => l_msg_data);
5691
5692 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS)
5693 THEN
5694 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
5695 itemkey => itemkey,
5696 aname => 'ERROR_FLAG',
5697 avalue => 'Y');
5698 Handle_Error(p_itemtype => itemtype,
5699 p_itemkey => itemkey,
5700 p_msg_count => l_msg_count,
5701 p_msg_data => l_msg_data);
5702
5703 WRITE_LOG (l_api_name, 'Errored when creating child end date'||'.'||l_schedule_start_date);
5704 END IF;*/
5705
5706
5707 -- OPEN c_sch_det(l_schedule_id);
5708 -- FETCH c_sch_det INTO l_start_time, l_timezone;
5709 -- CLOSE c_sch_det;
5710
5711
5712
5713 -- If any errors happen let start time be sysdate
5714 /*IF l_return_status = FND_API.G_RET_STS_ERROR THEN
5715 l_usr_start_time := SYSDATE;
5716 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5717 l_usr_start_time := SYSDATE;
5718 END IF;*/
5719
5720 --IF l_schedule_end_date > nvl(l_parent_end_date, l_campaign_end_date)
5721 --THEN
5722 l_schedule_end_date := nvl(l_parent_end_date, l_campaign_end_date);
5723 --END IF;
5724
5725
5726 AMS_SCHEDULER_PVT.Create_Next_Schedule ( p_parent_sched_id => l_parent_sched_id,
5727 p_child_sched_st_date => l_schedule_start_date,--l_usr_start_time,
5728 p_child_sched_en_date => l_schedule_end_date,
5729 x_child_sched_id => l_child_sched_id,
5730 x_msg_count => l_msg_count,
5731 x_msg_data => l_msg_data,
5732 x_return_status => l_return_status
5733 );
5734
5735 IF l_return_status = FND_API.G_RET_STS_SUCCESS
5736 THEN
5737 WRITE_LOG(l_api_name, 'WF_REPEAT_CREATE_CSCH: Create next schedule returned Success ');
5738
5739 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
5740 itemkey => itemkey,
5741 aname => 'AMS_NEW_SCHEDULE_ID',
5742 avalue => l_child_sched_id);
5743
5744 result := 'COMPLETE:SUCCESS' ;
5745 ELSIF l_return_status <> FND_API.G_RET_STS_SUCCESS
5746 THEN
5747 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
5748 itemkey => itemkey,
5749 aname => 'ERROR_FLAG',
5750 avalue => 'Y');
5751 Handle_Error(p_itemtype => itemtype,
5752 p_itemkey => itemkey,
5753 p_msg_count => l_msg_count,
5754 p_msg_data =>l_msg_data,
5755 p_wf_err_attrib => 'AMS_CSCH_CREATE_ERROR'
5756 );
5757 result := 'COMPLETE:ERROR' ;
5758 END IF; -- success in create_next_schedule
5759 END IF; -- funcmode RUN
5760
5761 IF (funcmode = 'CANCEL')
5762 THEN
5763 result := 'COMPLETE:' ;
5764 RETURN;
5765 END IF;
5766
5767 IF (funcmode = 'TIMEOUT')
5768 THEN
5769 result := 'COMPLETE:' ;
5770 RETURN;
5771 END IF;
5772 EXCEPTION
5773 WHEN OTHERS THEN
5774 wf_core.context(G_PKG_NAME,l_api_name,itemtype,itemkey,actid,funcmode);
5775 raise ;
5776
5777 END WF_REPEAT_CREATE_CSCH;
5778
5779 --=====================================================================
5780 -- Procedure
5781 -- WF_REPEAT_RAISE_EVENT
5782 --
5783 -- PURPOSE
5784 -- This api is used by scheduler workflow to raise the event for the next sched run
5785 --
5786 -- HISTORY
5787 -- 11-Oct-2003 dbiswas Created.
5788 --=====================================================================
5789
5790 PROCEDURE WF_REPEAT_RAISE_EVENT(itemtype IN VARCHAR2,
5791 itemkey IN VARCHAR2,
5792 actid IN NUMBER,
5793 funcmode IN VARCHAR2,
5794 result OUT NOCOPY VARCHAR2) IS
5795
5796 l_schedule_id NUMBER;
5797 l_parameter_list WF_PARAMETER_LIST_T;
5798 l_schedule_next_run_st_date DATE;
5799 l_temp_variable varchar2(50);
5800
5801 l_sch_text VARCHAR2(100);
5802 l_new_item_key VARCHAR2(30);
5803 l_api_name VARCHAR2(30);
5804
5805 BEGIN
5806 l_api_name := 'WF_REPEAT_RAISE_EVENT';
5807 IF (funcmode = 'RUN')
5808 THEN
5809 l_schedule_id := WF_ENGINE.GetItemAttrText(
5810 itemtype => itemtype,
5811 itemkey => itemkey ,
5812 aname => 'AMS_NEW_SCHEDULE_ID');
5813
5814 l_parameter_list := WF_PARAMETER_LIST_T();
5815
5816 wf_event.AddParameterToList(p_name => 'SCHEDULE_ID',
5817 p_value => l_schedule_id,
5818 p_parameterlist => l_parameter_list);
5819
5820 l_schedule_next_run_st_date := WF_ENGINE.GetItemAttrDate(itemtype => itemtype,
5821 itemkey => itemkey ,
5822 aname => 'AMS_SCHEDULER_NEXT_RUN_ST_DATE'
5823 );
5824
5825 l_temp_variable := to_char(l_schedule_next_run_st_date,'DD-MON-RRRR HH24:MI:SS');
5826
5827 wf_event.AddParameterToList(p_name => 'AMS_PARENT_STATUS',
5828 p_value => l_temp_variable,
5829 p_parameterlist => l_parameter_list);
5830
5831 WRITE_LOG(l_api_name, 'WF_REPEAT_RAISE_EVENT: ADD AMS_SCHEDULER_NEXT_RUN_ST_DATE TO PARAMTER LIST : '||
5832 '|| NEW SCHEDULE ID PASSED: '||l_schedule_id||'; || '
5833 ||to_char(l_schedule_next_run_st_date,'DD-MON-RRRR HH24:MI:SS')||' ; '
5834 ||'|| L_TEMP_VARIABLE VALUE PASSED: '||l_temp_variable||'|| SYSDATE: '
5835 ||to_char(sysdate,'DD-MON-RRRR HH24:MI:SS'));
5836
5837
5838 l_new_item_key := l_schedule_id ||'RPT'|| TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
5839
5840 WRITE_LOG(l_api_name, 'WF_REPEAT_RAISE_EVENT: Before raising event ');
5841 WRITE_LOG(l_api_name, 'WF_REPEAT_RAISE_EVENT: Before raising event with key '||l_new_item_key);
5842
5843 Wf_Event.Raise
5844 ( p_event_name => 'oracle.apps.ams.campaign.RepeatScheduleEvent',
5845 p_event_key => l_new_item_key,
5846 p_parameters => l_parameter_list,
5847 p_send_date => l_schedule_next_run_st_date
5848 );
5849
5850 END IF;
5851
5852 -- CANCEL mode - Normal Process Execution
5853 IF (funcmode = 'CANCEL')
5854 THEN
5855 RETURN;
5856 END IF;
5857
5858 -- TIMEOUT mode - Normal Process Execution
5859 IF (funcmode = 'TIMEOUT')
5860 THEN
5861 RETURN;
5862 END IF;
5863 -- dbms_output.put_line('End Check Trigger stat :'||result);
5864 EXCEPTION
5865 WHEN OTHERS THEN
5866 wf_core.context(G_PKG_NAME,l_api_name,itemtype,itemkey,actid,funcmode);
5867 raise ;
5868 END WF_REPEAT_RAISE_EVENT ;
5869
5870
5871 --===============================================================================================
5872 -- Procedure
5873 -- Raise_BE_On_Status_change
5874 --
5875 -- PURPOSE
5876 -- This api is called to raise a Business event on a UserStatus change for CSCH, EVEO, EONE
5877 --
5878 -- ALGORITHM
5879 -- 1. Check for the Object Type (CSCH, EVEO and EONE )
5880 -- Yes => 1.1 Open the respective cursor to get the required values
5881 -- 1.2 if old_status_code not equal to new_status_code
5882 -- Yes => Raise Business event
5883 --
5884 -- Any error in any of the API callouts?
5885 -- => a) Set RETURN STATUS to E
5886 --
5887 -- OPEN ISSUES
5888 -- 1. Should we do a explicit exit on Object_type not found.
5889 --
5890 -- HISTORY
5891 -- 17-Mar-2005 spendem Created. Enhancement # 3805347
5892 --===============================================================================================
5893
5894 PROCEDURE RAISE_BE_ON_STATUS_CHANGE(p_obj_id IN NUMBER,
5895 p_obj_type IN VARCHAR2,
5896 p_old_status_code IN VARCHAR2,
5897 p_new_status_code IN VARCHAR2 ) IS
5898
5899
5900 CURSOR c_csch_det IS
5901 SELECT related_event_from
5902 , related_event_id
5903 FROM ams_campaign_schedules_b
5904 WHERE schedule_id = p_obj_id;
5905
5906
5907 l_api_version CONSTANT NUMBER := 1.0 ;
5908 l_api_name CONSTANT VARCHAR2(30) := 'RAISE_BE_ON_STATUS_CHANGE';
5909
5910 l_old_status_code VARCHAR2(30);
5911 l_related_event_from VARCHAR2(30);
5912 l_related_event_id NUMBER;
5913 l_schedule_type VARCHAR2(4);
5914 l_parameter_list WF_PARAMETER_LIST_T;
5915 l_new_item_key VARCHAR2(100);
5916
5917 BEGIN
5918
5919 -- input debug messages.
5920 IF (AMS_DEBUG_HIGH_ON) THEN
5921
5922 AMS_Utility_PVT.debug_message(l_api_name || ': start');
5923
5924 END IF;
5925
5926
5927 IF ( p_obj_type = 'CSCH' ) THEN
5928
5929 l_schedule_type := p_obj_type;
5930
5931 --open cursor for campaign schedules and fetch values
5932 OPEN c_csch_det;
5933 FETCH c_csch_det INTO l_related_event_from, l_related_event_id;
5934 CLOSE c_csch_det;
5935
5936 ELSIF ( p_obj_type = 'EVEO' OR p_obj_type = 'EONE' ) THEN
5937
5938 l_schedule_type := p_obj_type;
5939
5940 ELSE
5941
5942 RETURN;
5943
5944 END IF;
5945
5946 IF ( p_old_status_code <> p_new_status_code )
5947 THEN
5948
5949 l_parameter_list := WF_PARAMETER_LIST_T();
5950 l_new_item_key := p_obj_id || 'STATUS' || p_obj_type || TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
5951
5952
5953 wf_event.AddParameterToList(p_name => 'OBJECT_TYPE',
5954 p_value => l_schedule_type,
5955 p_parameterlist => l_parameter_list);
5956
5957
5958 wf_event.AddParameterToList(p_name => 'OBJECT_ID',
5959 p_value => p_obj_id,
5960 p_parameterlist => l_parameter_list);
5961
5962
5963 wf_event.AddParameterToList(p_name => 'OLD_STATUS',
5964 p_value => p_old_status_code,
5965 p_parameterlist => l_parameter_list);
5966
5967
5968 wf_event.AddParameterToList(p_name => 'NEW_STATUS',
5969 p_value => p_new_status_code,
5970 p_parameterlist => l_parameter_list);
5971
5972
5973 wf_event.AddParameterToList(p_name => 'RELATED_EVENT_OBJECT_TYPE',
5974 p_value => l_related_event_from,
5975 p_parameterlist => l_parameter_list);
5976
5977
5978 wf_event.AddParameterToList(p_name => 'RELATED_EVENT_OBJECT_ID',
5979 p_value => l_related_event_id,
5980 p_parameterlist => l_parameter_list);
5981
5982 IF (AMS_DEBUG_HIGH_ON) THEN
5983
5984 AMS_Utility_PVT.debug_message('Raise Business event for User Status Change' || ' ' || l_new_item_key);
5985
5986 END IF;
5987
5988
5989
5990 WF_EVENT.Raise
5991 ( p_event_name => 'oracle.apps.ams.common.ObjectStatusChanged',
5992 p_event_key => l_new_item_key,
5993 p_parameters => l_parameter_list);
5994
5995 END IF; -- end if for raise Business event.
5996
5997
5998 END RAISE_BE_ON_STATUS_CHANGE;
5999
6000
6001 -------------------------------------------------------------
6002 -- Start of Comments
6003 -- Name
6004 -- HANDLE_COLLATERAL
6005 --
6006 -- Purpose
6007 -- This function is called from Business Event
6008 -- anchaudh created for R12.
6009 -------------------------------------------------------------
6010 FUNCTION HANDLE_COLLATERAL(p_subscription_guid IN RAW,
6011 p_event IN OUT NOCOPY WF_EVENT_T
6012 ) RETURN VARCHAR2
6013 IS
6014 l_schedule_id NUMBER;
6015 l_association_id NUMBER;
6016 l_citem_id NUMBER;
6017 l_citem_ver_id NUMBER;
6018 l_Return_status varchar2(20);
6019 l_log_return_status VARCHAR2(1) := FND_API.g_ret_sts_success ;
6020
6021 CURSOR c_citem_assoc (l_csch_id IN NUMBER) IS
6022 SELECT assoc.association_id, assoc.content_item_id, ci.live_citem_version_id
6023 FROM ibc_associations assoc, ibc_content_Items ci
6024 WHERE assoc.association_type_code in ('AMS_CSCH','AMS_COLLAT')
6025 AND assoc.associated_object_val1 = to_char(l_csch_id)
6026 AND assoc.content_item_id = ci.content_Item_id;
6027
6028
6029 PROCEDURE_NAME CONSTANT VARCHAR2(30) := 'HANDLE_COLLATERAL';
6030
6031 BEGIN
6032
6033 -- Get the Value of SCHEDULE_ID
6034 l_schedule_id := p_event.getValueForParameter('SCHEDULE_ID');
6035
6036 OPEN c_citem_assoc(l_schedule_id);
6037 LOOP
6038 FETCH c_citem_assoc INTO l_association_id, l_citem_id, l_citem_ver_id;
6039 EXIT WHEN c_citem_assoc%NOTFOUND;
6040
6041 AMS_Utility_PVT.Create_Log (
6042 x_return_status => l_log_return_status,
6043 p_arc_log_used_by => 'CSCH',
6044 p_log_used_by_id => l_schedule_id,
6045 p_msg_data => 'HANDLE_COLLATERAL : Stamping collateral versions',
6046 p_msg_type => 'DEBUG'
6047 );
6048
6049 IF l_association_id IS NOT null
6050 AND l_citem_id IS NOT null
6051 AND l_citem_ver_id IS NOT NULl
6052 THEN
6053 Ibc_Associations_Pkg.UPDATE_ROW(
6054 p_association_id => l_association_id
6055 ,p_citem_version_id => l_citem_ver_id
6056 );
6057 END IF;
6058 END LOOP;
6059 CLOSE c_citem_assoc;
6060
6061
6062 return 'SUCCESS';
6063
6064 EXCEPTION
6065
6066 WHEN OTHERS THEN
6067 WF_CORE.CONTEXT('AMS_ScheduleRules_PVT','HANDLE_COLLATERAL',
6068 p_event.getEventName( ), p_subscription_guid);
6069 WF_EVENT.setErrorInfo(p_event, 'ERROR');
6070 RETURN 'ERROR';
6071 END HANDLE_COLLATERAL;
6072
6073 --===============================================================================================
6074 -- PROCEDURE
6075 -- CHECK_NOTIFICATION_PREFERENCE
6076 --
6077 -- PURPOSE
6078 -- This method will be used to check the notification preference for an activity
6079 --
6080 -- ALGORITHM
6081 -- 1. Check for the NOTIFY_ON_ACTIVATION_FLAG for the Schedule Id
6082 -- Y => RETURN True
6083 -- N => RETURN False
6084 --
6085 -- HISTORY
6086 -- 08-Aug-2005 srivikri Created.
6087 -- 01-sep-2005 soagrawa Cleaned up
6088 -- 30-sep-2005 srivikri Changes for Repeating Frequency Region display
6089 -- 07-Mar-2006 srivikri changes for bug 4690754
6090 --===============================================================================================
6091
6092 PROCEDURE CHECK_NOTIFICATION_PREFERENCE(itemtype IN VARCHAR2,
6093 itemkey IN VARCHAR2,
6094 actid IN NUMBER,
6095 funcmode IN VARCHAR2,
6096 result OUT NOCOPY VARCHAR2) IS
6097
6098 CURSOR l_sch_det (p_schedule_id NUMBER) IS
6099 SELECT
6100 NOTIFY_ON_ACTIVATION_FLAG,
6101 triggerable_flag,
6102 trig_repeat_flag,
6103 source_code,
6104 Med.media_name,
6105 lookup.MEANING,
6106 orig_csch_id,
6107 frequency,
6108 frequency_type,
6109 end_date_time,
6110 campaign_id
6111 FROM ams_campaign_schedules_b csch,
6112 ams_scheduler scheduler,
6113 AMS_MEDIA_VL Med,
6114 ams_lookups lookup
6115 WHERE csch.schedule_id = p_schedule_id
6116 AND scheduler.OBJECT_ID(+) = nvl(csch.orig_csch_id, csch.schedule_id)
6117 AND scheduler.OBJECT_TYPE(+) = 'CSCH'
6118 AND Med.media_id = csch.activity_id
6119 AND lookup.LOOKUP_TYPE(+) = 'AMS_TRIGGER_FREQUENCY_TYPE'
6120 AND lookup.LOOKUP_CODE(+) = scheduler.frequency_type;
6121
6122 CURSOR l_new_sch_det (p_new_schedule_id NUMBER) IS
6123 SELECT
6124 schedule_name
6125 FROM AMS_CAMPAIGN_SCHEDULES_VL
6126 WHERE SCHEDULE_ID = p_new_schedule_id;
6127
6128 CURSOR l_camp_det (p_campaign_id NUMBER) IS
6129 SELECT
6130 actual_exec_end_date
6131 FROM AMS_CAMPAIGNS_ALL_B
6132 WHERE CAMPAIGN_ID = p_campaign_id;
6133
6134
6135 l_api_version CONSTANT NUMBER := 1.0 ;
6136 l_api_name CONSTANT VARCHAR2(35) := 'CHECK_NOTIFICATION_PREFERENCE';
6137 l_flag VARCHAR2(1);
6138 l_triggerable_flag VARCHAR2(1);
6139 l_trig_repeat_flag VARCHAR2(1);
6140 l_schedule_id NUMBER;
6141 l_return_status VARCHAR2(1);
6142 --l_repeat_freq_type VARCHAR2(30);
6143 l_msg_data VARCHAR2(30);
6144 l_source_code VARCHAR2(30);
6145 l_new_schedule_id NUMBER;
6146 l_new_schedule_name VARCHAR2(240);
6147 l_scheduler_frequency NUMBER;
6148 l_media_name VARCHAR2(120);
6149 l_freq_meaning VARCHAR2(80);
6150 l_orig_csch_id NUMBER;
6151
6152 l_query_freq NUMBER;
6153 l_query_freq_type VARCHAR2(80);
6154 l_csch_end_date DATE;
6155 l_scheduler_next_run_date DATE;
6156 l_campaign_end_date DATE;
6157 l_campaign_id NUMBER;
6158 l_msg_count NUMBER;
6159
6160 BEGIN
6161
6162 -- RUN mode - Normal Process Execution
6163 IF (funcmode = 'RUN')
6164 THEN
6165 l_schedule_id := to_number(WF_ENGINE.GetItemAttrText(
6166 itemtype => itemtype,
6167 itemkey => itemkey ,
6168 aname => 'SCHEDULE_ID' ));
6169
6170 AMS_Utility_PVT.Create_Log (
6171 x_return_status => l_return_status,
6172 p_arc_log_used_by => 'CSCH',
6173 p_log_used_by_id => l_schedule_id,
6174 p_msg_data => 'CHECK_NOTIFICATION_PREFERENCE : started',
6175 p_msg_type => 'DEBUG'
6176 );
6177
6178 OPEN l_sch_det(l_schedule_id);
6179 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;
6180 CLOSE l_sch_det;
6181
6182 IF ( l_flag is not null and l_flag = 'Y' ) THEN
6183 result := 'COMPLETE:T' ;
6184 AMS_Utility_PVT.Create_Log (
6185 x_return_status => l_return_status,
6186 p_arc_log_used_by => 'CSCH',
6187 p_log_used_by_id => l_schedule_id,
6188 p_msg_data => 'CHECK_NOTIFICATION_PREFERENCE : NOTIFICATION PREFERENCE IS YES for schedule id '||l_schedule_id,
6189 p_msg_type => 'DEBUG'
6190 );
6191
6192 ELSE
6193 AMS_Utility_PVT.Create_Log (
6194 x_return_status => l_return_status,
6195 p_arc_log_used_by => 'CSCH',
6196 p_log_used_by_id => l_schedule_id,
6197 p_msg_data => 'CHECK_NOTIFICATION_PREFERENCE : NOTIFICATION PREFERENCE IS NO for schedule id '||l_schedule_id,
6198 p_msg_type => 'DEBUG'
6199 );
6200 result := 'COMPLETE:F';
6201 END IF;
6202
6203 IF ((l_triggerable_flag = 'N' AND l_trig_repeat_flag = 'Y') OR l_orig_csch_id IS NOT NULL) THEN
6204 --Repeating activity
6205 l_scheduler_frequency := to_number(WF_ENGINE.GetItemAttrText(
6206 itemtype => itemtype,
6207 itemkey => itemkey ,
6208 aname => 'AMS_SCHEDULER_FREQUENCY' ));
6209 IF (l_scheduler_frequency IS NULL) THEN
6210 -- this means that the repeating activity is in the Schedule Execution flow
6211 AMS_SCHEDULER_PVT.Schedule_Repeat (
6212 p_last_run_date => SYSDATE,
6213 p_frequency => l_query_freq,
6214 p_frequency_type => l_query_freq_type,
6215 x_next_run_date => l_scheduler_next_run_date,
6216 x_return_status => l_return_status,
6217 x_msg_count => l_msg_count,
6218 x_msg_data => l_msg_data);
6219 OPEN l_camp_det(l_campaign_id);
6220 FETCH l_camp_det INTO l_campaign_end_date;
6221 CLOSE l_camp_det;
6222
6223 IF (nvl(l_csch_end_date, l_campaign_end_date) <= l_scheduler_next_run_date)
6224 THEN
6225
6226 -- if this is the last executed activity Return true
6227 -- so that the notification will be sent
6228 -- since the workflow does not flow thru the Notification node if the activity is last one
6229 result := 'COMPLETE:T';
6230 ELSE
6231 -- returning False, as we dont want to send the Notification twice
6232 result := 'COMPLETE:F';
6233 END IF;
6234 --RETURN;
6235 ELSE
6236 l_new_schedule_id := TO_NUMBER(WF_ENGINE.GetItemAttrText(
6237 itemtype => itemtype,
6238 itemkey => itemkey ,
6239 aname => 'AMS_NEW_SCHEDULE_ID'));
6240
6241 IF l_new_Schedule_id IS NOT NULL
6242 THEN
6243
6244 OPEN l_new_sch_det(l_new_schedule_id);
6245 FETCH l_new_sch_det INTO l_new_schedule_name;
6246 CLOSE l_new_sch_det;
6247
6248 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
6249 itemkey => itemkey ,
6250 aname => 'AMS_NEW_SCHEDULE_NAME',
6251 avalue => l_new_schedule_name );
6252 END IF;
6253 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
6254 itemkey => itemkey ,
6255 aname => 'AMS_SCHEDULER_FREQ_MEANING',
6256 avalue => l_freq_meaning );
6257
6258 END IF;
6259 -- set the message 'Repeating Activity' from FND_MESSAGES
6260 FND_MESSAGE.Set_Name('AMS', 'AMS_REPEATING_ACTIVITY_PROMPT');
6261 ELSE
6262 -- set the message 'Activity' from FND_MESSAGES to the attribute AMS_ACTIVITY_DESCRIPTION using setItemAttrText
6263 FND_MESSAGE.Set_Name('AMS', 'AMS_ACTIVITY_PROMPT');
6264 END IF;
6265 l_msg_data := FND_MESSAGE.Get;
6266
6267 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
6268 itemkey => itemkey ,
6269 aname => 'AMS_ACTIVITY_DESCRIPTION',
6270 avalue => l_msg_data );
6271 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
6272 itemkey => itemkey ,
6273 aname => 'SOURCE_CODE',
6274 avalue => l_source_code );
6275 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
6276 itemkey => itemkey ,
6277 aname => 'AMS_CHANNEL_DESCRIPTION',
6278 avalue => l_media_name );
6279
6280 END IF;
6281
6282 -- CANCEL mode - Normal Process Execution
6283 IF (funcmode = 'CANCEL')
6284 THEN
6285 result := 'COMPLETE:F' ;
6286 RETURN;
6287 END IF;
6288
6289 -- TIMEOUT mode - Normal Process Execution
6290 IF (funcmode = 'TIMEOUT')
6291 THEN
6292 result := 'COMPLETE:F' ;
6293 RETURN;
6294 END IF;
6295 EXCEPTION
6296 WHEN OTHERS THEN
6297 wf_core.context(G_PKG_NAME,'CHECK_NOTIFICATION_PREFERENCE',itemtype,itemkey,actid,funcmode);
6298 raise ;
6299 END CHECK_NOTIFICATION_PREFERENCE;
6300
6301 END AMS_ScheduleRules_PVT ;