[Home] [Help]
PACKAGE BODY: APPS.AMS_SCHEDULER_PVT
Source
1 PACKAGE BODY AMS_SCHEDULER_PVT AS
2 /* $Header: amsvrptb.pls 120.7 2010/11/30 05:38:59 amlal ship $*/
3 --
4
5 G_PKG_NAME CONSTANT VARCHAR2(30):='AMS_SCHEDULER_PVT';
6 G_FILE_NAME CONSTANT VARCHAR2(15):='amsvrptb.pls';
7 g_log_level CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
8
9 --========================================================================
10 -- PROCEDURE
11 -- WRITE_LOG
12 -- Purpose
13 -- This method will be used to write logs for this api
14 -- HISTORY
15 -- 10-Oct-2000 dbiswas Created.
16 --
17 --========================================================================
18
19 PROCEDURE WRITE_LOG ( p_api_name IN VARCHAR2,
20 p_log_message IN VARCHAR2 )
21 IS
22
23 l_api_name VARCHAR2(30);
24 l_log_mesg VARCHAR2(2000);
25 l_return_status VARCHAR2(1);
26 BEGIN
27 l_api_name := p_api_name;
28 l_log_mesg := p_log_message;
29 AMS_Utility_PVT.debug_message (
30 p_log_level => g_log_level,
31 p_module_name => 'ams.plsql.'||'.'|| g_pkg_name||'.'||l_api_name,
32 p_text => p_log_message
33 );
34
35 AMS_Utility_PVT.Create_Log (
36 x_return_status => l_return_status,
37 p_arc_log_used_by => 'CSCH',
38 p_log_used_by_id => 1,
39 p_msg_data => 'amsvrptb.pls: '||p_log_message,
40 p_msg_type => 'DEBUG'
41 );
42 END WRITE_LOG;
43
44
45 FUNCTION Target_Group_Exist (p_schedule_id IN NUMBER --)
46 , p_obj_type IN VARCHAR2 :='CSCH')
47 RETURN VARCHAR2
48 IS
49 CURSOR c_target_det
50 IS SELECT 1
51 FROM ams_act_lists la
52 WHERE list_act_type = 'TARGET'
53 AND list_used_by = p_obj_type --'CSCH'
54 AND list_used_by_id = p_schedule_id
55 AND EXISTS (SELECT *
56 FROM ams_list_entries le
57 WHERE le.list_header_id = la.list_header_id) ;
58 l_dummy NUMBER ;
59 BEGIN
60 OPEN c_target_det ;
61 FETCH c_target_det INTO l_dummy ;
62 CLOSE c_target_det ;
63
64 IF l_dummy IS NULL THEN
65 RETURN FND_API.g_false ;
66 ELSE
67 RETURN FND_API.g_true;
68 END IF;
69
70 END Target_Group_Exist ;
71
72
73 --========================================================================
74 --PROCEDURE
75 -- Schedule_Repeat
76 -- Purpose
77 -- This package calculates next run for repeating schedules
78 -- HISTORY
79 -- 10-Oct-2000 dbiswas Created.
80 -- 29-aug-2005 soagrawa Modified to add debug component for R12 Monitors / Rpt Sch
81 --========================================================================
82
83 PROCEDURE Schedule_Repeat ( p_last_run_date IN DATE,
84 p_frequency IN NUMBER,
85 p_frequency_type IN VARCHAR2,
86 x_next_run_date OUT NOCOPY DATE,
87 x_return_status OUT NOCOPY VARCHAR2,
88 x_msg_count OUT NOCOPY NUMBER,
89 x_msg_data OUT NOCOPY VARCHAR2)
90 IS
91
92 l_last_run_date DATE ;
93 l_frequency NUMBER;
94 l_frequency_type VARCHAR2(30);
95 l_next_run_date DATE ;
96 l_api_name VARCHAR2(30);
97 l_msg_count NUMBER;
98 l_msg_data VARCHAR2(2000);
99 l_debug_mode VARCHAR2(30);
100 --
101 BEGIN
102 x_return_status := FND_API.G_RET_STS_SUCCESS;
103 l_api_name :='Schedule_Repeat';
104 l_last_run_date := p_last_run_date;
105 l_frequency := p_frequency;
106 l_frequency_type := p_frequency_type;
107
108 WRITE_LOG(l_api_name, 'Schedule_Repeat: Start'||'.'||p_last_run_date);
109 WRITE_LOG(l_api_name, 'Schedule_Repeat: Frequency type is '||l_frequency_type);
110 WRITE_LOG(l_api_name, 'Schedule_Repeat: Frequency is '||l_frequency);
111 WRITE_LOG(l_api_name, 'Schedule_Repeat: Last run date is '||l_last_run_date);
112
113 l_debug_mode := FND_PROFILE.value('AMS_MONITOR_DEBUG_INTERVAL');
114
115 WRITE_LOG(l_api_name, 'Schedule_Repeat: Debug Mode is '||l_debug_mode);
116
117 IF l_debug_mode IS NOT NULL THEN
118 l_next_run_date := l_last_run_date + (to_number(l_debug_mode)/(24*60)) ;
119 ELSIF l_frequency_type = 'DAILY' THEN
120 l_next_run_date := l_last_run_date + l_frequency;
121 ELSIF l_frequency_type = 'WEEKLY' THEN
122 l_next_run_date := l_last_run_date + (7 * l_frequency) ;
123 ELSIF l_frequency_type = 'MONTHLY' THEN
124 l_next_run_date := add_months(l_last_run_date , l_frequency) ;
125 ELSIF l_frequency_type = 'YEARLY' THEN
126 l_next_run_date := add_months(l_last_run_date , (12*l_frequency)) ;
127 ElSIF l_frequency_type = 'HOURLY' THEN
128 l_next_run_date := l_last_run_date + (l_frequency/24) ;
129 END IF;
130
131 WRITE_LOG(l_api_name, 'Schedule_Repeat: Next run date is '||l_next_run_date);
132 x_next_run_date := l_next_run_date ;
133
134 WRITE_LOG (l_api_name, 'Schedule_Repeat: Success'||'.'||p_last_run_date);
135
136 EXCEPTION
137 WHEN FND_API.G_EXC_ERROR THEN
138 x_return_status := FND_API.G_RET_STS_ERROR ;
139 FND_MSG_PUB.count_and_get (
140 p_count => x_msg_count,
141 p_data => x_msg_data,
142 p_encoded => FND_API.G_FALSE
143 );
144 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
145 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
146 FND_MSG_PUB.count_and_get (
147 p_count => x_msg_count,
148 p_data => x_msg_data,
149 p_encoded => FND_API.G_FALSE
150 );
151 WHEN OTHERS THEN
152 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
153 FND_MSG_PUB.count_and_get (
154 p_count => x_msg_count,
155 p_data => x_msg_data,
156 p_encoded => FND_API.G_FALSE
157 );
158 END Schedule_Repeat;
159
160 --=============================================================================================================
161 --PROCEDURE
162 -- Create_Next_Schedule
163 -- Purpose
164 -- This package creates the next schedule to be used by the workflow
165 -- HISTORY
166 -- 10-Oct-2000 dbiswas Created.
167 -- 18-feb-2004 soagrawa Fixed bug# 3452264
168 -- 23-mar-2004 soagrawa Now creating association with OCM item conditionally
169 -- 21-apr-2004 soagrawa Made changes to fix bug# 3570234
170 -- 28-jan-2005 spendem fix for bug # 4145845. Added to_char function to the schedule_id
171 -- 17-Mar-2005 spendem call the API to raise business event on status change as per enh # 3805347
172 --==============================================================================================================
173
174 PROCEDURE Create_Next_Schedule ( p_parent_sched_id IN NUMBER,
175 p_child_sched_st_date IN DATE,
176 p_child_sched_en_date IN DATE,
177 x_child_sched_id OUT NOCOPY NUMBER,
178 -- soagrawa added on 18-feb-2004 for bug# 3452264
179 p_orig_sch_name IN VARCHAR2 ,
180 p_trig_repeat_flag IN VARCHAR2,
181 x_msg_count OUT NOCOPY NUMBER,
182 x_msg_data OUT NOCOPY VARCHAR2,
183 x_return_status OUT NOCOPY VARCHAR2
184 )
185 IS
186 l_parent_sched_id NUMBER;
187 l_child_sched_st_date DATE;
188 l_child_sched_en_date DATE;
189
190 l_next_run_date DATE ;
191 l_api_name VARCHAR2(30);
192
193 l_date_suffix VARCHAR2(25);
194 l_dummy NUMBER;
195 l_msg_count NUMBER ;
196 l_msg_data VARCHAR2(2000);
197 l_schedule_rec AMS_Camp_Schedule_PVT.schedule_rec_type;
198 l_new_sched_id NUMBER := NULL;
199
200 CURSOR c_sched(p_orig_sched_id IN NUMBER) IS
201 SELECT *
202 FROM ams_campaign_schedules_vl
203 WHERE schedule_id = p_orig_sched_id;
204
205 l_parent_rec c_sched%ROWTYPE;
206
207 CURSOR c_channel_media(p_medium_id NUMBER, p_activity_id NUMBER) IS
208 SELECT 1
209 FROM ams_media_channels
210 WHERE channel_id = p_medium_id
211 AND media_id = p_activity_id
212 AND ACTIVE_TO_DATE > SYSDATE;
213
214 CURSOR c_def_status(p_status_code VARCHAR2) IS
215 SELECT user_status_id
216 FROM ams_user_statuses_b
217 WHERE system_status_type = 'AMS_CAMPAIGN_SCHEDULE_STATUS'
218 AND system_status_code = p_status_code
219 AND default_flag = 'Y'
220 AND enabled_flag = 'Y';
221
222 -- soagrawa added on 21-apr-2004 for bug# 3570234
223 CURSOR c_max_sch_det(p_schedule_id NUMBER) IS
224 select schedule_id
225 from ams_campaign_schedules_b
226 where creation_date = (select max(creation_date)
227 from ams_campaign_schedules_b
228 where nvl(orig_csch_id, schedule_id) = p_schedule_id);
229
230 l_tgrp_copy_from_csch_id NUMBER;
231
232 l_def_avail_status NUMBER;
233 l_def_new_status NUMBER;
234 l_return_status VARCHAR2(1);
235
236 l_errnum NUMBER;
237 l_errmsg VARCHAR2(3000);
238 l_errcode VARCHAR2(80);
239
240 CURSOR c_get_assoc IS
241 SELECT content_item_id
242 FROM ibc_associations
243 WHERE association_type_code = 'AMS_CSCH'
244 AND ASSOCIATED_OBJECT_VAL1 = to_char(p_parent_sched_id); -- fix for bug # 4145845
245 l_content_item_id NUMBER;
246
247
248 BEGIN
249 x_return_status := FND_API.G_RET_STS_SUCCESS;
250 l_api_name :='Create_Next_Schedule';
251
252 l_parent_sched_id := p_parent_sched_id;
253 l_child_sched_st_date := p_child_sched_st_date;
254 l_child_sched_en_date := p_child_sched_en_date;
255
256 OPEN c_sched(p_parent_sched_id);
257 FETCH c_sched INTO l_parent_rec ;
258 CLOSE c_sched;
259
260
261 l_schedule_rec.start_date_time := l_child_sched_st_date;
262 l_schedule_rec.end_date_time := l_child_sched_en_date;
263 l_schedule_rec.timezone_id := l_parent_rec.timezone_id;
264 l_schedule_rec.campaign_id := l_parent_rec.campaign_id;
265 l_schedule_rec.use_parent_code_flag:= l_parent_rec.use_parent_code_flag;
266 l_schedule_rec.use_parent_code_flag:= l_parent_rec.use_parent_code_flag;
267 l_schedule_rec.source_code := NULL;
268 l_schedule_rec.activity_type_code := l_parent_rec.activity_type_code;
269 l_schedule_rec.activity_id := l_parent_rec.activity_id;
270 l_schedule_rec.marketing_medium_id := null;
271 -- validate media channel id
272 IF l_parent_rec.marketing_medium_id IS NOT NULL
273 THEN
274 WRITE_LOG (l_api_name, 'Create_Next_Schedule: Orig csch mktmg med is not null'||'.'||l_parent_rec.marketing_medium_id);
275
276 OPEN c_channel_media(l_parent_rec.marketing_medium_id, l_parent_rec.activity_id);
277 FETCH c_channel_media INTO l_dummy;
278 CLOSE c_channel_media;
279
280 IF l_dummy IS NULL
281 THEN
282 l_schedule_rec.marketing_medium_id := null;
283 ELSE
284 l_schedule_rec.marketing_medium_id := l_parent_rec.marketing_medium_id;
285 END IF;
286 END IF;
287 WRITE_LOG (l_api_name, 'Create_Next_Schedule: Activity id is '|| l_schedule_rec.activity_id||'; Mktg Med is '||l_schedule_rec.marketing_medium_id);
288
289
290 OPEN c_def_status('NEW');
291 FETCH c_def_status INTO l_def_new_status;
292 CLOSE c_def_status;
293
294 l_schedule_rec.user_status_id := l_def_new_status;
295 l_schedule_rec.status_code := 'NEW';
296
297 -- -------------------------- COPY COVER LETTER ----------------------------------------
298
299 l_date_suffix := ' - ' || TO_CHAR(SYSDATE,'DD-MON-RRRR HH24:MI:SS');
300 -- to be completed later
301
302 -- ------------------------- END COPY COVER LETTER -------------------------------------
303
304 -- Set the attribs for the child rec
305 -- soagrawa modified on 18-feb-2004 for bug# 3452264
306
307 WRITE_LOG (l_api_name, 'CSCH name passed is p_orig_sch_name '||p_orig_sch_name);
308 IF p_orig_sch_name is NOT null
309 THEN
310 -- use this name instead
311 IF length(p_orig_sch_name || l_date_suffix) <= 240
312 THEN
313 l_schedule_rec.schedule_name := p_orig_sch_name || l_date_suffix;
314 ELSE
315 l_schedule_rec.schedule_name := substr(p_orig_sch_name,1,240-length(l_date_suffix)) || l_date_suffix;
316 END IF;
317 ELSE
318 IF length(l_parent_rec.schedule_name || l_date_suffix) <= 240
319 THEN
320 l_schedule_rec.schedule_name := l_parent_rec.schedule_name || l_date_suffix;
321 ELSE
322 l_schedule_rec.schedule_name := substr(l_parent_rec.schedule_name,1,240-length(l_date_suffix)) || l_date_suffix;
323 END IF;
324 END IF;
325
326 l_schedule_rec.reply_to_mail := l_parent_rec.reply_to_mail;
327 l_schedule_rec.mail_sender_name := l_parent_rec.mail_sender_name;
328 l_schedule_rec.mail_subject := l_parent_rec.mail_subject;
329 l_schedule_rec.from_fax_no := l_parent_rec.from_fax_no;
330 l_schedule_rec.campaign_calendar := l_parent_rec.campaign_calendar;
331
332 l_schedule_rec.accounts_closed_flag := l_parent_rec.accounts_closed_flag;
333 l_schedule_rec.org_id := l_parent_rec.org_id;
334 l_schedule_rec.objective_code := l_parent_rec.objective_code;
335 l_schedule_rec.country_id := l_parent_rec.country_id;
336 l_schedule_rec.priority := l_parent_rec.priority;
337 l_schedule_rec.transaction_currency_code := l_parent_rec.transaction_currency_code;
338 l_schedule_rec.functional_currency_code := l_parent_rec.functional_currency_code;
339 l_schedule_rec.language_code := l_parent_rec.language_code;
340 l_schedule_rec.task_id := l_parent_rec.task_id;
341 l_schedule_rec.attribute_category := l_parent_rec.attribute_category;
342 l_schedule_rec.attribute1 := l_parent_rec.attribute1;
343 l_schedule_rec.attribute2 := l_parent_rec.attribute2;
344 l_schedule_rec.attribute3 := l_parent_rec.attribute3;
345 l_schedule_rec.attribute4 := l_parent_rec.attribute4;
346 l_schedule_rec.attribute5 := l_parent_rec.attribute5;
347 l_schedule_rec.attribute6 := l_parent_rec.attribute6;
348 l_schedule_rec.attribute7 := l_parent_rec.attribute7;
349 l_schedule_rec.attribute8 := l_parent_rec.attribute8;
350 l_schedule_rec.attribute9 := l_parent_rec.attribute9;
351 l_schedule_rec.attribute10 := l_parent_rec.attribute10;
352 l_schedule_rec.attribute11 := l_parent_rec.attribute11;
353 l_schedule_rec.attribute12 := l_parent_rec.attribute12;
354 l_schedule_rec.attribute13 := l_parent_rec.attribute13;
355 l_schedule_rec.attribute14 := l_parent_rec.attribute14;
356 l_schedule_rec.attribute15 := l_parent_rec.attribute15;
357 l_schedule_rec.activity_attribute_category := l_parent_rec.activity_attribute_category;
358 l_schedule_rec.activity_attribute1 := l_parent_rec.activity_attribute1;
359 l_schedule_rec.activity_attribute2 := l_parent_rec.activity_attribute2;
360 l_schedule_rec.activity_attribute3 := l_parent_rec.activity_attribute3;
361 l_schedule_rec.activity_attribute4 := l_parent_rec.activity_attribute4;
362 l_schedule_rec.activity_attribute5 := l_parent_rec.activity_attribute5;
363 l_schedule_rec.activity_attribute6 := l_parent_rec.activity_attribute6;
364 l_schedule_rec.activity_attribute7 := l_parent_rec.activity_attribute7;
365 l_schedule_rec.activity_attribute8 := l_parent_rec.activity_attribute8;
366 l_schedule_rec.activity_attribute9 := l_parent_rec.activity_attribute9;
367 l_schedule_rec.activity_attribute10 := l_parent_rec.activity_attribute10;
368 l_schedule_rec.activity_attribute11 := l_parent_rec.activity_attribute11;
369 l_schedule_rec.activity_attribute12 := l_parent_rec.activity_attribute12;
370 l_schedule_rec.activity_attribute13 := l_parent_rec.activity_attribute13;
371 l_schedule_rec.activity_attribute14 := l_parent_rec.activity_attribute14;
372 l_schedule_rec.activity_attribute15 := l_parent_rec.activity_attribute15;
373 l_schedule_rec.custom_setup_id := l_parent_rec.custom_setup_id; -- copy the same, even if disabled
374 l_schedule_rec.triggerable_flag := l_parent_rec.triggerable_flag;
375 l_schedule_rec.trigger_id := l_parent_rec.trigger_id;
376 -- soagrawa added on 18-feb-2004 for bug# 3452264
377 l_schedule_rec.trig_repeat_flag := p_trig_repeat_flag;
378 l_schedule_rec.tgrp_exclude_prev_flag := l_parent_rec.tgrp_exclude_prev_flag;
379 l_schedule_rec.notify_user_id := l_parent_rec.notify_user_id;
380 l_schedule_rec.approver_user_id := l_parent_rec.approver_user_id;
381 l_schedule_rec.owner_user_id := l_parent_rec.owner_user_id;
382 -- l_schedule_rec.active_flag := l_parent_rec.active_flag;
383 l_schedule_rec.description := l_parent_rec.description;
384 l_schedule_rec.test_email_address := l_parent_rec.test_email_address;
385 --l_schedule_rec.ORIG_CSCH_ID := l_parent_rec.schedule_id;
386
387 -- Added by AMLAL : Bug 5611404
388 l_schedule_rec.delivery_mode := l_parent_rec.delivery_mode;
389
390 -- Added by AMLAL : Bug 5738978
391 l_schedule_rec.printer_address := l_parent_rec.printer_address;
392
393 l_schedule_rec.NOTIFY_ON_ACTIVATION_FLAG := l_parent_rec.NOTIFY_ON_ACTIVATION_FLAG; --anchaud added, sept'05.
394
395 IF l_parent_rec.ORIG_CSCH_ID IS NOT null
396 THEN
397 l_schedule_rec.ORIG_CSCH_ID := l_parent_rec.ORIG_CSCH_ID;
398 ELSE
399 l_schedule_rec.ORIG_CSCH_ID := l_parent_rec.schedule_id;
400 END IF;
401
402 l_schedule_rec.usage := l_parent_rec.usage;
403 l_schedule_rec.purpose := l_parent_rec.purpose;
404 l_schedule_rec.sales_methodology_id := l_parent_rec.sales_methodology_id;
405
406 WRITE_LOG (l_api_name, 'Create_Next_Schedule: Before creating schedule');
407
408 -- soagrawa made this change on 21-apr-2003 for bug# 3570234
409 OPEN c_max_sch_det(l_parent_rec.schedule_id);
410 FETCH c_max_sch_det INTO l_tgrp_copy_from_csch_id;
411 CLOSE c_max_sch_det;
412
413 AMS_Camp_Schedule_PVT.Create_Camp_Schedule(
414 p_api_version_number => 1.0,
415 p_init_msg_list => FND_API.G_FALSE,
416 p_commit => FND_API.G_FALSE,
417 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
418 x_return_status => l_return_status,
419 x_msg_count => l_msg_count,
420 x_msg_data => l_msg_data,
421 p_schedule_rec => l_schedule_rec,
422 x_schedule_id => l_new_sched_id);
423
424 IF l_return_status <> FND_API.g_ret_sts_success
425 THEN
426 WRITE_LOG(l_api_name, 'Create_Next_Schedule: ERROR: After create schedule'||l_return_status||'New Schedule name '||l_schedule_rec.schedule_name);
427 l_return_status := FND_API.g_ret_sts_error;
428
429 FOR i IN 1 .. FND_MSG_PUB.count_msg LOOP
430 WRITE_LOG(l_api_name, 'Create_Next_Schedule : (' || i || ') ' || FND_MSG_PUB.get(i, FND_API.g_false));
431 END LOOP;
432
433 -- schedule creation was not successful
434 x_child_sched_id := null;
435 x_msg_data := l_msg_data;
436 x_msg_count := l_msg_count;
437 x_return_status := l_return_status;
438 RETURN;
439
440 ELSE
441 WRITE_LOG(l_api_name, 'Create_Next_Schedule: SUCCESS: After create schedule'||l_return_status||'New Schedule name '||l_schedule_rec.schedule_name);
442 END IF;
443
444
445 -- if Schedule creation was successful
446
447 --Associate Cover letter
448 WRITE_LOG(l_api_name, 'Create_Next_Schedule: Start Copy cover letter '||l_schedule_rec.schedule_name);
449 OPEN c_get_assoc;
450 FETCH c_get_assoc INTO l_content_item_id;
451 CLOSE c_get_assoc;
452
453
454 IF FND_API.G_TRUE = Target_Group_Exist(p_parent_sched_id)
455 THEN
456 -- this if statement added by soagrawa on 23-mar-2004
457 IF l_content_item_id IS NOT null
458 THEN
459 IBC_ASSOCIATIONS_GRP.Create_Association (
460 p_api_version => 1.0,
461 p_assoc_type_code => 'AMS_CSCH',
462 p_assoc_object1 => l_new_sched_id,
463 p_content_item_id => l_content_item_id,
464 x_return_status => l_return_status,
465 x_msg_count => l_msg_count,
466 x_msg_data => l_msg_data
467 );
468
469 IF l_return_status <> FND_API.g_ret_sts_success
470 THEN
471 WRITE_LOG(l_api_name, 'Create_Next_Schedule: ERROR: After content association'||l_return_status||'New Schedule name '||l_schedule_rec.schedule_name);
472 l_return_status := FND_API.g_ret_sts_error;
473
474 FOR i IN 1 .. FND_MSG_PUB.count_msg LOOP
475 WRITE_LOG(l_api_name, 'Create_Next_Schedule : (' || i || ') ' || FND_MSG_PUB.get(i, FND_API.g_false));
476 END LOOP;
477
478 -- association creation was not successful
479 x_child_sched_id := null;
480 x_msg_data := l_msg_data;
481 x_msg_count := l_msg_count;
482 x_return_status := l_return_status;
483 RETURN;
484
485 ELSE
486 WRITE_LOG(l_api_name, 'Create_Next_Schedule: SUCCESS: Aftercontent association'||l_return_status||'New Schedule name '||l_schedule_rec.schedule_name);
487 END IF;
488 END IF;
489 END IF;
490
491
492 -- anchaudh : added for copy of collateral contents attached to the non-direct marketing activities for R12 .
493 ams_copyelements_pvt.copy_act_collateral(
494 p_src_act_type => 'AMS_COLLAT',
495 p_src_act_id => l_parent_rec.schedule_id,
496 p_new_act_id => l_new_sched_id,
497 p_errnum => l_errnum,
498 p_errcode => l_errcode,
499 p_errmsg => l_errmsg);
500
501 -- amlal : added to copy product details from parent into child .
502 WRITE_LOG(l_api_name, 'Going to Copy products '||l_parent_rec.schedule_id||' to '||l_new_sched_id);
503 ams_copyelements_pvt.copy_act_prod(
504 p_src_act_type => 'CSCH',
505 p_src_act_id => l_parent_rec.schedule_id,
506 p_new_act_id => l_new_sched_id,
507 p_errnum => l_errnum,
508 p_errcode => l_errcode,
509 p_errmsg => l_errmsg);
510
511 -- copy metrices
512 WRITE_LOG(l_api_name, 'Create_Next_Schedule: Start Copy metrics for New Schedule name '||l_schedule_rec.schedule_name);
513 Ams_ActMetric_Pvt.copy_act_metrics (
514 p_api_version => 1.0,
515 p_init_msg_list => FND_API.G_FALSE,
516 p_commit => FND_API.G_FALSE,
517 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
518 p_source_object_type => 'CSCH',
519 p_source_object_id => l_parent_rec.schedule_id,
520 p_target_object_id => l_new_sched_id,
521 x_return_status => l_return_status,
522 x_msg_count => l_msg_count,
523 x_msg_data => l_msg_data);
524
525 IF l_return_status <> FND_API.g_ret_sts_success
526 THEN
527 WRITE_LOG(l_api_name, 'Create_Next_Schedule: ERROR: After copy metrics'||l_return_status||'for new schedule name '||l_schedule_rec.schedule_name);
528 l_return_status := FND_API.g_ret_sts_error;
529 FOR i IN 1 .. FND_MSG_PUB.count_msg LOOP
530 WRITE_LOG(l_api_name, 'Create_Next_Schedule : (' || i || ') ' || FND_MSG_PUB.get(i, FND_API.g_false));
531 END LOOP;
532
533 -- metrics copying was not successful
534 x_child_sched_id := null;
535 x_msg_data := l_msg_data;
536 x_msg_count := l_msg_count;
537 x_return_status := l_return_status;
538 RETURN;
539
540 ELSE
541 WRITE_LOG(l_api_name, 'Create_Next_Schedule: SUCCESS: After copy metrics'||l_return_status||'for new schedule name '||l_schedule_rec.schedule_name);
542 END IF;
543
544 WRITE_LOG (l_api_name, 'Create_Next_Schedule: Before copying TGRP, being copied from CSCH id '||l_tgrp_copy_from_csch_id);
545
546 IF FND_API.G_TRUE = Target_Group_Exist(p_parent_sched_id)
547 THEN
548 -- copy target group
549 AMS_ACT_LIST_PVT.copy_target_group(
550 p_from_schedule_id => l_tgrp_copy_from_csch_id,
551 p_to_schedule_id => l_new_sched_id,
552 p_list_used_by => 'CSCH',
553 -- soagrawa made this change on 21-apr-2003 for bug# 3570234
554 p_repeat_flag => FND_API.G_TRUE,
555 x_msg_count => l_msg_count,
556 x_msg_data => l_msg_data,
557 x_return_status => l_return_status
558 ) ;
559
560 IF l_return_status <> FND_API.g_ret_sts_success
561 THEN
562 WRITE_LOG(l_api_name, 'Create_Next_Schedule: ERROR: After copy target group'||l_return_status||'for new schedule name '||l_schedule_rec.schedule_name);
563 l_return_status := FND_API.g_ret_sts_error;
564 FOR i IN 1 .. FND_MSG_PUB.count_msg LOOP
565 WRITE_LOG(l_api_name, 'Create_Next_Schedule : (' || i || ') ' || FND_MSG_PUB.get(i, FND_API.g_false));
566 END LOOP;
567
568 -- target group copying was not successful
569 x_child_sched_id := null;
570 x_msg_data := l_msg_data;
571 x_msg_count := l_msg_count;
572 x_return_status := l_return_status;
573 RETURN;
574
575 ELSE
576 WRITE_LOG(l_api_name, 'Create_Next_Schedule: SUCCESS: After copy target group'||l_return_status||'for new schedule name '||l_schedule_rec.schedule_name);
577 END IF;
578 END IF;
579
580
581
582 -- Update status of new schedule to available
583 OPEN c_def_status('AVAILABLE');
584 FETCH c_def_status INTO l_def_avail_status;
585 CLOSE c_def_status;
586
587 UPDATE AMS_CAMPAIGN_SCHEDULES_B
588 SET STATUS_CODE = 'AVAILABLE' ,
589 status_date = sysdate,
590 user_status_id = l_def_avail_status
591 WHERE SCHEDULE_ID = l_new_sched_id;
592
593 -- call to api to raise business event, as per enh # 3805347
594 AMS_SCHEDULERULES_PVT.RAISE_BE_ON_STATUS_CHANGE(p_obj_id => l_new_sched_id,
595 p_obj_type => 'CSCH',
596 p_old_status_code => 'NEW',
597 p_new_status_code => 'AVAILABLE');
598
599 -- set return status
600 x_child_sched_id := l_new_sched_id;
601 x_msg_data := l_msg_data;
602 x_msg_count := l_msg_count;
603 x_return_status := l_return_status;
604
605 END Create_Next_Schedule;
606
607
608 --========================================================================
609 --PROCEDURE
610 -- Create_Scheduler
611 -- Purpose
612 -- This procedure creates a row in the ams_scheduler table using the table handler package
613 -- HISTORY
614 -- 04-may-2005 soagrawa Created.
615 --
616 --========================================================================
617
618 PROCEDURE Create_Scheduler ( p_obj_type VARCHAR2,
619 p_obj_id NUMBER,
620 p_freq NUMBER,
621 p_freq_type VARCHAR2,
622 x_msg_count OUT NOCOPY NUMBER,
623 x_msg_data OUT NOCOPY VARCHAR2,
624 x_return_status OUT NOCOPY VARCHAR2,
625 x_scheduler_id OUT NOCOPY NUMBER
626 )
627 IS
628 l_scheduler_id NUMBER;
629 l_object_version_number NUMBER := 1;
630 CURSOR c_id IS
631 SELECT ams_scheduler_s.NEXTVAL
632 FROM dual;
633
634
635 BEGIN
636
637 x_return_status := FND_API.G_RET_STS_SUCCESS;
638
639 OPEN c_id;
640 FETCH c_id INTO l_scheduler_id;
641 CLOSE c_id;
642
643 AMS_SCHEDULER_B_PKG.Insert_Row(
644 px_scheduler_id => l_scheduler_id,
645 p_created_by => FND_GLOBAL.USER_ID,
646 p_creation_date => SYSDATE,
647 p_last_updated_by => FND_GLOBAL.USER_ID,
648 p_last_update_date => SYSDATE,
649 p_last_update_login => FND_GLOBAL.LOGIN_ID,
650 px_object_version_number => l_object_version_number,
651 p_object_type => p_obj_type,
652 p_object_id => p_obj_id,
653 p_frequency => p_freq,
654 p_frequency_type => p_freq_type
655 );
656
657 x_scheduler_id := l_scheduler_id;
658
659 END Create_Scheduler;
660
661 END AMS_Scheduler_PVT ;