[Home] [Help]
PACKAGE BODY: APPS.AMS_CAMP_SCHEDULE_PVT
Source
1 PACKAGE BODY AMS_Camp_Schedule_PVT as
2 /* $Header: amsvschb.pls 120.12 2007/12/26 09:36:36 spragupa ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_Camp_Schedule_PVT
7 -- Purpose
8 -- Private api created to Update/insert/Delete campaign
9 -- schedules
10 -- History
11 -- 22-Jan-2001 ptendulk Created.
12 -- 18-May-2001 ptendulk Modified the Create_camp_schedule procedure.
13 -- 24-May-2001 ptendulk Modified Check
14 -- 31-May-2001 ptendulk Modified Create_camp_Schedule procedure to add trigger call
15 -- 16-Jun-2001 soagrawa Modified copy_schedule api for source code changes
16 -- 25-Jun-2001 ptendulk Create Target group for Internet, Trade promo and deal schedules.
17 -- 08-Jul-2001 ptendulk Modified Validate_schedule_rec api, refer bug# 1856924
18 -- 17-Jul-2001 ptendulk Moved the code to start trigger workflow in business rules api
19 -- Change the call to the bus rule api.
20 -- 19-Jul-2001 ptendulk Added columns for eBlast
21 -- 01-Aug-2001 ptendulk Replaced ams_utility_pvt check_uniqueness call with
22 -- manual check. Refer bug #1913448
23 -- 18-Aug-2001 ptendulk Added else if to take the schedule id from the api input
24 -- parameter if passed.
25 -- 11-Sep-2001 ptendulk Added code to add the access to the schedule upon creation
26 -- 24-sep-2001 soagrawa Removed security group id from everywhere
27 -- 26-sep-2001 soagrawa Modified update_camp_schedule for start and end dates
28 -- 12-oct-2001 soagrawa Modified create_camp_schedule : capitalizing the generated source code
29 -- 22 oct-2001 soagrawa Modified copy api to fix bug# 2068786
30 -- 03-dec-2001 soagrawa Modified check_schedule_inter_entity: not checking against campaign's period
31 -- anymore. Refer to bug# 2132456.
32 -- 03-dec-2001 soagrawa Modified validate_schedule_rec, bug# 2131521
33 -- 13-dec-2001 soagrawa In update procedure, modified source code related code, bug# 2133264
34 -- 10-jan-2002 soagrawa Fixed bug# 2178737
35 -- 25-jan-2002 soagrawa Fixed bug# 2175580 in copy schedule method
36 -- 07-feb-2002 soagrawa Fixed bug# 2229618 (copy does not retain the specified source code)
37 -- in copy schedule method
38 -- 20-mar-2002 soagrawa Modified source code related code, bug# 2273902 in update_camp_schedule
39 -- 28-mar-2002 soagrawa Fixed bug# 2289769
40 -- 23-apr-2002 soagrawa Modified validation for cover letter id for new eblast
41 -- 22-may-2002 soagrawa Fixed bug# 2380670
42 -- 06-jun-2002 soagrawa Fixed bug# 2406677 in update. Now updating access table if owner changes.
43 -- 12-jun-2002 soagrawa Fixed ATT bug# 2376329 created by updated by issue from insert_row
44 -- 02-jul-2002 soagrawa Fixed bug# 2442695 in copy_camp_schedule
45 -- 06-feb-2003 soagrawa Fixed bug# 2788922 in copy_camp_schedule
46 -- 24-jun-2003 dbiswas Fixed bug# 3008802 in copy_camp_schedule
47 -- 27-jun-2003 anchaudh completed triggers changes for 11510.
48 -- 12-aug-2003 dbiswas Added 3 new columns for schedule_rec_type
49 -- 13-aug-2003 soagrawa Fixed bug# 3096925 in update_camp_schedule
50 -- 25-aug-2003 dbiswas Added 1 new column(sales_methodology_id) for schedule_rec_type
51 -- 23-mar-2004 soagrawa Modified validation for triggerable schedules in validate_Schedule_rec
52 -- keeping repeating schedules in consideration
53 -- 28-jul-2004 dhsingh Fix for Bug#3798545
54 -- 17-may-2005 soagrawa Added integration with locking rule API validation API
55 -- 26-jul-2005 dbiswas Added 1 new column(notify_on_activation_flag) for schedule_rec_type
56 -- 29-May-2006 srivikri added column delivery_mode
57 -- 24-Dec-2007 spragupa ER - 6467510 - Extend Copy functionality to include TASKS for campaign schedules/activities
58 -- NOTE
59 --
60 -- End of Comments
61 -- ===============================================================
62
63
64 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_Camp_Schedule_PVT';
65 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvschb.pls';
66
67 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
68 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
69
70 --===================================================================
71 -- NAME
72 -- Create_Camp_Schedule
73 --
74 -- PURPOSE
75 -- Creates the Campaign schedules.
76 --
77 -- NOTES
78 --
79 -- HISTORY
80 -- 22-Jan-2001 PTENDULK Created
81 -- 18-May-2001 ptendulk Added call to create list after the
82 -- Direct Marketing schedule is created.
83 -- 31-May-2001 ptendulk Added Trigger creation after the schedule is created.
84 -- 12-oct-2001 soagrawa Capitalizing the generated source code
85 -- 27-jun-2003 anchaudh added the extra columns to be inserted for triggers changes.
86 -- 29-May-2006 srivikri added column delivery_mode
87 --===================================================================
88 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
89 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
90 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
91
92
93
94 PROCEDURE Create_Camp_Schedule(
95 p_api_version_number IN NUMBER,
96 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
97 p_commit IN VARCHAR2 := FND_API.G_FALSE,
98 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
99
100 x_return_status OUT NOCOPY VARCHAR2,
101 x_msg_count OUT NOCOPY NUMBER,
102 x_msg_data OUT NOCOPY VARCHAR2,
103
104 p_schedule_rec IN schedule_rec_type := g_miss_schedule_rec,
105 x_schedule_id OUT NOCOPY NUMBER
106 )
107
108 IS
109 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Camp_Schedule';
110 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
111 l_return_status_full VARCHAR2(1);
112 l_object_version_number NUMBER := 1;
113 l_org_id NUMBER := FND_API.G_MISS_NUM;
114 l_schedule_id NUMBER;
115 l_dummy NUMBER;
116
117 l_schedule_rec schedule_rec_type := p_schedule_rec ;
118 CURSOR c_id IS
119 SELECT ams_campaign_schedules_b_s.NEXTVAL
120 FROM dual;
121
122 CURSOR c_id_exists (l_id IN NUMBER) IS
123 SELECT 1 FROM dual
124 WHERE EXISTS (SELECT 1 FROM AMS_CAMPAIGN_SCHEDULES_B
125 WHERE SCHEDULE_ID = l_id);
126
127 --CURSOR c_trig_exists (l_schedule_id IN NUMBER) IS
128 -- SELECT 1 FROM dual
129 -- WHERE EXISTS (SELECT 1 FROM ams_campaign_schedules_b
130 -- WHERE campaign_id = p_schedule_rec.campaign_id
131 -- AND trigger_id = p_schedule_rec.trigger_id
132 -- AND schedule_id <> l_schedule_id );
133
134 -- soagrawa 22-oct-2002 for bug# 2594717
135 CURSOR c_eone_srccd IS
136 SELECT source_code
137 FROM ams_event_offers_all_b
138 WHERE event_offer_id = P_schedule_rec.related_event_id;
139
140 l_eone_srccd VARCHAR2(30);
141 l_new_event_offer_id NUMBER;
142 l_event_offer_rec AMS_EventOffer_PVT.evo_rec_type;
143
144
145 BEGIN
146 -- Standard Start of API savepoint
147 SAVEPOINT CREATE_Camp_Schedule_PVT;
148
149 -- Standard call to check for call compatibility.
150 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
151 p_api_version_number,
152 l_api_name,
153 G_PKG_NAME)
154 THEN
155 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
156 END IF;
157
158 -- Initialize message list if p_init_msg_list is set to TRUE.
159 IF FND_API.to_Boolean( p_init_msg_list )
160 THEN
161 FND_MSG_PUB.initialize;
162 END IF;
163
164 -- Debug Message
165 IF (AMS_DEBUG_HIGH_ON) THEN
166
167 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
168 END IF;
169
170
171 -- Initialize API return status to SUCCESS
172 x_return_status := FND_API.G_RET_STS_SUCCESS;
173
174 IF (AMS_DEBUG_HIGH_ON) THEN
175
176
177
178 AMS_UTILITY_PVT.debug_message('before handle_shced_source_code');
179
180 END IF;
181 -- Handle Status
182 AMS_ScheduleRules_PVT.Handle_Status(
183 p_schedule_rec.user_status_id,
184 'AMS_CAMPAIGN_SCHEDULE_STATUS',
185 l_schedule_rec.status_code,
186 x_return_status
187 );
188 IF x_return_status = FND_API.g_ret_sts_error THEN
189 RAISE FND_API.g_exc_error;
190 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
191 RAISE FND_API.g_exc_unexpected_error;
192 END IF;
193 --
194 IF (AMS_DEBUG_HIGH_ON) THEN
195
196 AMS_UTILITY_PVT.debug_message('After handle_status');
197 END IF;
198 -- default campaign calendar
199 IF p_schedule_rec.campaign_calendar IS NULL
200 AND (p_schedule_rec.start_period_name IS NOT NULL
201 OR p_schedule_rec.end_period_name IS NOT NULL)
202 THEN
203 l_schedule_rec.campaign_calendar := FND_PROFILE.value('AMS_CAMPAIGN_DEFAULT_CALENDER');
204 END IF;
205
206 IF (AMS_DEBUG_HIGH_ON) THEN
207
208
209
210 AMS_UTILITY_PVT.debug_message('before handle_shced_source_code *'||p_schedule_rec.campaign_id||'*');
211
212 END IF;
213 -- default source_code
214 AMS_ScheduleRules_PVT.Handle_Schedule_Source_Code(
215 p_source_code => p_schedule_rec.source_code,
216 p_camp_id => p_schedule_rec.campaign_id,
217 p_setup_id => p_schedule_rec.custom_setup_id,
218 p_cascade_flag => p_schedule_rec.use_parent_code_flag,
219 x_source_code => l_schedule_rec.source_code,
220 x_return_status => x_return_status ) ;
221 IF x_return_status = FND_API.g_ret_sts_error THEN
222 RAISE FND_API.g_exc_error;
223 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
224 RAISE FND_API.g_exc_unexpected_error;
225 END IF;
226 IF (AMS_DEBUG_HIGH_ON) THEN
227
228 AMS_UTILITY_PVT.debug_message('After handle_shced_source_code');
229 END IF;
230
231 -- Added BY soagrawa ON 12-oct-2001
232 -- to capitalize the automatically generated source code
233
234 l_schedule_rec.source_code := UPPER(l_schedule_rec.source_code);
235
236 IF (AMS_DEBUG_HIGH_ON) THEN
237
238
239
240 AMS_Utility_PVT.debug_message('Source code : ' ||l_schedule_rec.source_code );
241
242 END IF;
243 --
244 --
245 --
246 -- -- Local variable initialization
247 --
248 IF p_schedule_rec.schedule_id IS NULL OR p_schedule_rec.schedule_id = FND_API.g_miss_num THEN
249 LOOP
250 l_dummy := NULL;
251 OPEN c_id;
252 FETCH c_id INTO l_schedule_id;
253 CLOSE c_id;
254
255 OPEN c_id_exists(l_schedule_id);
256 FETCH c_id_exists INTO l_dummy;
257 CLOSE c_id_exists;
258 EXIT WHEN l_dummy IS NULL;
259 END LOOP;
260 -- Following line of code is added by ptendulk on 18-Aug-2001
261 ELSE
262 l_schedule_id := p_schedule_rec.schedule_id ;
263 END IF;
264
265 -- =========================================================================
266 -- Validate Environment
267 -- =========================================================================
268 IF FND_GLOBAL.User_Id IS NULL
269 THEN
270 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_USER_PROFILE_MISSING' );
271 RAISE FND_API.G_EXC_ERROR;
272 END IF;
273
274 -- If activity type code = 'EVENTS' create the underlying event for R12
275 -- dbiswas added extra logic for related_source_id, related_source_code and related_source_object
276 -- for bug 5019554
277 IF l_schedule_rec.activity_type_code = 'EVENTS' AND
278 l_schedule_rec.related_source_id IS NULL
279 AND l_schedule_rec.related_source_code IS NULL
280 AND l_schedule_rec.related_source_object IS NULL
281 THEN
282 -- initialize underlying dummy event
283 l_event_offer_rec.event_level := 'MAIN' ;
284 l_event_offer_rec.event_type_code := 'BRIEFING' ;
285 l_event_offer_rec.event_object_type := 'EONE' ;
286 l_event_offer_rec.reg_required_flag := 'N' ;
287 l_event_offer_rec.reg_charge_flag := 'N' ;
288 l_event_offer_rec.reg_invited_only_flag := 'N' ;
289 l_event_offer_rec.event_standalone_flag := 'Y';
290 l_event_offer_rec.create_attendant_lead_flag := 'N' ;
291 l_event_offer_rec.create_registrant_lead_flag := 'N' ;
292 l_event_offer_rec.private_flag := 'N' ;
293 l_event_offer_rec.parent_type := 'CAMP';
294 l_event_offer_rec.system_status_code := 'NEW';
295 l_event_offer_rec.user_status_id := 1;
296 l_event_offer_rec.application_id := 530;
297 l_event_offer_rec.country_code := l_schedule_rec.country_id;
298 l_event_offer_rec.custom_setup_id := 3000;
299 l_event_offer_rec.event_start_date := l_schedule_rec.start_date_time ;
300 l_event_offer_rec.event_end_date := l_schedule_rec.end_date_time ;
301 l_event_offer_rec.event_offer_name := l_schedule_rec.schedule_name;
302 l_event_offer_rec.owner_user_id := l_schedule_rec.owner_user_id;
303 l_event_offer_rec.event_language_code := l_schedule_rec.language_code;
304 l_event_offer_rec.parent_id := l_schedule_rec.campaign_id;
305 --anshu changes for bug#5006677 starts
306 l_event_offer_rec.timezone_id := l_schedule_rec.timezone_id;
307 l_event_offer_rec.currency_code_fc := l_schedule_rec.functional_currency_code;
308 l_event_offer_rec.currency_code_tc := l_schedule_rec.transaction_currency_code;
309 --anshu changes for bug#5006677 ends
310
311 -- null valued attributes
312 l_event_offer_rec.event_location_id := NULL;
313 l_event_offer_rec.business_unit_id := NULL;
314 l_event_offer_rec.event_venue_id := NULL;
315 l_event_offer_rec.reg_start_date := NULL;
316 l_event_offer_rec.reg_end_date := NULL;
317 l_event_offer_rec.city := NULL;
318 l_event_offer_rec.state := NULL;
319 l_event_offer_rec.country := NULL;
320 l_event_offer_rec.description := NULL;
321 l_event_offer_rec.start_period_name := NULL;
322 l_event_offer_rec.end_period_name := NULL;
323 l_event_offer_rec.priority_type_code := NULL;
324 l_event_offer_rec.INVENTORY_ITEM_ID := NULL;
325 l_event_offer_rec.PRICELIST_HEADER_ID := NULL;
326 l_event_offer_rec.PRICELIST_LINE_ID := NULL;
327 l_event_offer_rec.FORECASTED_REVENUE := NULL;
328 l_event_offer_rec.ACTUAL_REVENUE := NULL;
329 l_event_offer_rec.FORECASTED_COST := NULL;
330 l_event_offer_rec.ACTUAL_COST := NULL;
331 l_event_offer_rec.FUND_SOURCE_TYPE_CODE := NULL;
332 l_event_offer_rec.FUND_SOURCE_ID := NULL;
333 l_event_offer_rec.FUND_AMOUNT_FC := NULL;
334 l_event_offer_rec.FUND_AMOUNT_TC := NULL;
335
336 -- Debug message
337 IF (AMS_DEBUG_HIGH_ON) THEN
338 AMS_UTILITY_PVT.debug_message('before calling create event offer');
339 END IF;
340
341 AMS_EventOffer_PUB.create_EventOffer(
342 p_api_version => 1.0,
343 p_init_msg_list => FND_API.G_FALSE,
344 p_commit => FND_API.G_FALSE,
345 p_validation_level => FND_API.g_valid_level_full,
346 p_evo_rec => l_event_offer_rec,
347 x_return_status => x_return_status,
348 x_msg_count => x_msg_count,
349 x_msg_data => x_msg_data,
350 x_evo_id => l_new_event_offer_id
351 );
352
353
354 -- Debug message
355 IF (AMS_DEBUG_HIGH_ON) THEN
356 AMS_UTILITY_PVT.debug_message('after calling create event offer '||l_new_event_offer_id);
357 END IF;
358
359 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
360 RAISE FND_API.G_EXC_ERROR;
361 END IF;
362
363
364 -- update new schedule with this id
365 l_schedule_rec.related_event_from := 'EONE';
366 l_schedule_rec.related_event_id := l_new_event_offer_id;
367 l_schedule_rec.custom_setup_id :=3000;
368 END IF;
369
370 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
371 THEN
372 -- Debug message
373 IF (AMS_DEBUG_HIGH_ON) THEN
374
375 AMS_UTILITY_PVT.debug_message('Private API: Validate_Camp_Schedule');
376 END IF;
377
378 -- Invoke validation procedures
379 Validate_camp_schedule(
380 p_api_version_number => 1.0,
381 p_init_msg_list => FND_API.G_FALSE,
382 p_validation_level => p_validation_level,
383 p_schedule_rec => l_schedule_rec,
384 p_validation_mode => JTF_PLSQL_API.g_create,
385 x_return_status => x_return_status,
386 x_msg_count => x_msg_count,
387 x_msg_data => x_msg_data);
388 END IF;
389
390 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
391 RAISE FND_API.G_EXC_ERROR;
392 END IF;
393
394 -- Get the functional Currency code
395 IF p_schedule_rec.budget_amount_tc IS NOT NULL AND
396 p_schedule_rec.budget_amount_tc <> FND_API.G_MISS_NUM
397 THEN
398
399 AMS_CampaignRules_PVT.Convert_Camp_Currency(
400 p_tc_curr => p_schedule_rec.transaction_currency_code,
401 p_tc_amt => p_schedule_rec.budget_amount_tc,
402 x_fc_curr => l_schedule_rec.functional_currency_code,
403 x_fc_amt => l_schedule_rec.budget_amount_fc
404 ) ;
405 END IF ;
406
407 -- Debug Message
408 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_UTILITY_PVT.debug_message( 'Private API: Calling create table handler source :'||p_schedule_rec.source_code); END IF;
409
410 -- Invoke table handler(AMS_CAMPAIGN_SCHEDULES_B_PKG.Insert_Row)
411 AMS_CAMPAIGN_SCHEDULES_B_PKG.Insert_Row(
412 px_schedule_id => l_schedule_id,
413 -- modified by soagrawa on 12-jun-2002 for ATT bug# 2376329
414 p_last_update_date => SYSDATE,
415 p_last_updated_by => FND_GLOBAL.USER_ID,
416 p_creation_date => SYSDATE,
417 p_created_by => FND_GLOBAL.USER_ID,
418 p_last_update_login => FND_GLOBAL.LOGIN_ID,
419 /*
420 p_last_update_date => SYSDATE,
421 p_last_updated_by => G_USER_ID,
422 p_creation_date => SYSDATE,
423 p_created_by => G_USER_ID,
424 p_last_update_login => G_LOGIN_ID,
425 */
426 px_object_version_number => l_object_version_number,
427 p_campaign_id => p_schedule_rec.campaign_id,
428 p_user_status_id => p_schedule_rec.user_status_id,
429 p_status_code => l_schedule_rec.status_code,
430 p_status_date => NVL(p_schedule_rec.status_date,SYSDATE),
431 p_source_code => l_schedule_rec.source_code,
432 p_use_parent_code_flag => p_schedule_rec.use_parent_code_flag,
433 p_start_date_time => p_schedule_rec.start_date_time,
434 p_end_date_time => p_schedule_rec.end_date_time,
435 p_timezone_id => p_schedule_rec.timezone_id,
436 p_activity_type_code => p_schedule_rec.activity_type_code,
437 p_activity_id => p_schedule_rec.activity_id,
438 p_arc_marketing_medium_from => p_schedule_rec.arc_marketing_medium_from,
439 p_marketing_medium_id => p_schedule_rec.marketing_medium_id,
440 p_custom_setup_id => p_schedule_rec.custom_setup_id,
441 p_triggerable_flag => p_schedule_rec.triggerable_flag,
442 p_trigger_id => p_schedule_rec.trigger_id,
443 p_notify_user_id => p_schedule_rec.notify_user_id,
444 p_approver_user_id => p_schedule_rec.approver_user_id,
445 p_owner_user_id => p_schedule_rec.owner_user_id,
446 p_active_flag => p_schedule_rec.active_flag,
447 -- soagrawa removed on 03-nov-2003 for 11.5.10
448 p_cover_letter_id => null,
449 p_reply_to_mail => p_schedule_rec.reply_to_mail,
450 p_mail_sender_name => p_schedule_rec.mail_sender_name,
451 p_mail_subject => p_schedule_rec.mail_subject,
452 p_from_fax_no => p_schedule_rec.from_fax_no,
453 p_accounts_closed_flag => NVL(p_schedule_rec.accounts_closed_flag,'N'),
454 px_org_id => l_org_id,
455 p_objective_code => p_schedule_rec.objective_code,
456 p_country_id => p_schedule_rec.country_id,
457 p_campaign_calendar => l_schedule_rec.campaign_calendar,
458 p_start_period_name => p_schedule_rec.start_period_name,
459 p_end_period_name => p_schedule_rec.end_period_name,
460 p_priority => p_schedule_rec.priority,
461 p_workflow_item_key => p_schedule_rec.workflow_item_key,
462 p_transaction_currency_code => p_schedule_rec.transaction_currency_code,
463 p_functional_currency_code => l_schedule_rec.functional_currency_code,
464 p_budget_amount_tc => p_schedule_rec.budget_amount_tc,
465 p_budget_amount_fc => l_schedule_rec.budget_amount_fc,
466 p_language_code => p_schedule_rec.language_code,
467 p_task_id => p_schedule_rec.task_id,
468 p_related_event_from => l_schedule_rec.related_event_from,
469 p_related_event_id => l_schedule_rec.related_event_id,
470 p_attribute_category => p_schedule_rec.attribute_category,
471 p_attribute1 => p_schedule_rec.attribute1,
472 p_attribute2 => p_schedule_rec.attribute2,
473 p_attribute3 => p_schedule_rec.attribute3,
474 p_attribute4 => p_schedule_rec.attribute4,
475 p_attribute5 => p_schedule_rec.attribute5,
476 p_attribute6 => p_schedule_rec.attribute6,
477 p_attribute7 => p_schedule_rec.attribute7,
478 p_attribute8 => p_schedule_rec.attribute8,
479 p_attribute9 => p_schedule_rec.attribute9,
480 p_attribute10 => p_schedule_rec.attribute10,
481 p_attribute11 => p_schedule_rec.attribute11,
482 p_attribute12 => p_schedule_rec.attribute12,
483 p_attribute13 => p_schedule_rec.attribute13,
484 p_attribute14 => p_schedule_rec.attribute14,
485 p_attribute15 => p_schedule_rec.attribute15,
486 p_activity_attribute_category => p_schedule_rec.activity_attribute_category,
487 p_activity_attribute1 => p_schedule_rec.activity_attribute1,
488 p_activity_attribute2 => p_schedule_rec.activity_attribute2,
489 p_activity_attribute3 => p_schedule_rec.activity_attribute3,
490 p_activity_attribute4 => p_schedule_rec.activity_attribute4,
491 p_activity_attribute5 => p_schedule_rec.activity_attribute5,
492 p_activity_attribute6 => p_schedule_rec.activity_attribute6,
493 p_activity_attribute7 => p_schedule_rec.activity_attribute7,
494 p_activity_attribute8 => p_schedule_rec.activity_attribute8,
495 p_activity_attribute9 => p_schedule_rec.activity_attribute9,
496 p_activity_attribute10 => p_schedule_rec.activity_attribute10,
497 p_activity_attribute11 => p_schedule_rec.activity_attribute11,
498 p_activity_attribute12 => p_schedule_rec.activity_attribute12,
499 p_activity_attribute13 => p_schedule_rec.activity_attribute13,
500 p_activity_attribute14 => p_schedule_rec.activity_attribute14,
501 p_activity_attribute15 => p_schedule_rec.activity_attribute15,
502 -- removed by soagrawa on 24-sep-2001
503 -- p_security_group_id => p_schedule_rec.security_group_id,
504 p_query_id => p_schedule_rec.query_id,
505 p_include_content_flag => p_schedule_rec.include_content_flag,
506 p_content_type => p_schedule_rec.content_type,
507 p_test_email_address => p_schedule_rec.test_email_address,
508 p_schedule_name => p_schedule_rec.schedule_name,
509 p_schedule_description => p_schedule_rec.description,
510 p_greeting_text => p_schedule_rec.greeting_text,
511 p_footer_text => p_schedule_rec.footer_text,
512 --following is added by anchaudh on 27-jun-2003
513 p_trig_repeat_flag => p_schedule_rec.trig_repeat_flag,
514 p_tgrp_exclude_prev_flag => p_schedule_rec.tgrp_exclude_prev_flag,
515 p_orig_csch_id => p_schedule_rec.orig_csch_id,
516 p_cover_letter_version => p_schedule_rec.cover_letter_version,
517 -- added by dbiswas on Aug 12, 2003
518 p_usage => p_schedule_rec.usage,
519 p_purpose => p_schedule_rec.purpose,
520 p_last_activation_date => p_schedule_rec.last_activation_date,
521 p_sales_methodology_id => p_schedule_rec.sales_methodology_id,
522 p_printer_address => p_schedule_rec.printer_address,
523 -- added by dbiswas on Jul 27, 2005
524 p_notify_on_activation_flag => p_schedule_rec.notify_on_activation_flag,
525 -- added by anchaudh on Feb 01, 2006
526 p_sender_display_name => p_schedule_rec.sender_display_name,
527 -- added by srivikri on 29-May-2006
528 p_delivery_mode => p_schedule_rec.delivery_mode
529
530 );
531
532 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
533 RAISE FND_API.G_EXC_ERROR;
534 END IF;
535
536
537 IF (AMS_DEBUG_HIGH_ON) THEN
538 AMS_Utility_PVT.debug_message('Before Source code : '||l_schedule_id );
539 END IF;
540
541 -- need to push the source code to ams_source_codes
542 IF p_schedule_rec.use_parent_code_flag = 'N' THEN
543
544 -- soagrawa 22-oct-2002 for bug# 2594717
545 IF P_schedule_rec.related_event_id IS NOT NULL
546 THEN
547 OPEN c_eone_srccd;
548 FETCH c_eone_srccd INTO l_eone_srccd;
549 CLOSE c_eone_srccd;
550
551 AMS_CampaignRules_PVT.push_source_code(
552 l_schedule_rec.source_code,
553 'CSCH',
554 l_schedule_id,
555 l_eone_srccd, --P_schedule_rec.related_source_code,
556 P_schedule_rec.related_source_object,
557 P_schedule_rec.related_event_id -- P_schedule_rec.related_source_id
558 );
559 ELSE
560 AMS_CampaignRules_PVT.push_source_code(
561 l_schedule_rec.source_code,
562 'CSCH',
563 l_schedule_id
564 );
565 END IF;
566 END IF ;
567
568
569 -- Following code is added by ptendulk on 11-Sep-2001
570 -- Create access record for the schedules from campaigs
571 AMS_ScheduleRules_PVT.Create_Schedule_Access(
572 p_schedule_id => l_schedule_id ,
573 p_campaign_id => p_schedule_rec.campaign_id ,
574 p_owner_id => p_schedule_rec.owner_user_id,
575 p_init_msg_list => p_init_msg_list,
576 p_commit => p_commit,
577 p_validation_level => p_validation_level,
578
579 x_return_status => x_return_status,
580 x_msg_count => x_msg_count,
581 x_msg_data => x_msg_data
582 );
583
584 -- Following line of code is added by ptendulk on 31-May-2001
585 -- to start the trigger workflow process.
586 -- Start the trigger if the schedule is using trigger and the trigger
587 -- does not have any other schedule associated to it.
588
589
590 /* IF p_schedule_rec.trigger_id IS NOT NULL AND
591 p_schedule_rec.trigger_id <> FND_API.G_MISS_NUM AND
592 p_schedule_rec.triggerable_flag IS NOT NULL AND
593 p_schedule_rec.triggerable_flag = 'Y'
594 THEN
595 -- Following code is commented by ptendulk on 16-Jul-2001
596 -- Moved the code to the business rule api.
597 --OPEN c_trig_exists(l_schedule_id);
598 --FETCH c_trig_exists INTO l_dummy;
599 --CLOSE c_trig_exists;
600
601 --IF l_dummy IS NULL THEN
602 -- -- Start the trigger process as this is the first schedule associated to the schedule.
603 -- AMS_WFTRIG_PVT.StartProcess(p_trigger_id => p_schedule_rec.trigger_id) ;
604 --END IF ;
605 AMS_ScheduleRules_PVT.Start_Trigger_Process(p_schedule_id => p_schedule_rec.schedule_id,
606 p_trigger_id => p_schedule_rec.trigger_id) ;
607 END IF;*/
608
609 -- create object association when channel is event
610 --IF l_camp_rec.media_type_code = 'EVENTS'
611 -- AND l_camp_rec.channel_id IS NOT NULL
612 -- THEN
613 -- AMS_CampaignRules_PVT.create_camp_association(
614 -- l_camp_rec.campaign_id,
615 -- l_camp_rec.channel_id,
616 -- l_camp_rec.arc_channel_from,
617 -- l_return_status
618 -- );
619 -- IF l_return_status = FND_API.g_ret_sts_error THEN
620 -- RAISE FND_API.g_exc_error;
621 -- ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
622 -- RAISE FND_API.g_exc_unexpected_error;
623 -- END IF;
624 -- END IF;
625
626 -- attach seeded metrics to created schedules
627 AMS_RefreshMetric_PVT.copy_seeded_metric(
628 p_api_version => 1.0,
629 x_return_status => x_return_status,
630 x_msg_count => x_msg_count,
631 x_msg_data => x_msg_data,
632 p_arc_act_metric_used_by =>'CSCH',
633 p_act_metric_used_by_id => l_schedule_id,
634 p_act_metric_used_by_type => NULL
635 );
636 IF (AMS_DEBUG_HIGH_ON) THEN
637
638 AMS_Utility_PVT.debug_message('Metrics Copied Status '||x_return_status);
639 END IF;
640 IF x_return_status = FND_API.g_ret_sts_error THEN
641 RAISE FND_API.g_exc_error;
642 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
643 IF (AMS_DEBUG_HIGH_ON) THEN
644
645 AMS_Utility_PVT.debug_message('Errr '||sqlerrm);
646 END IF;
647 RAISE FND_API.g_exc_unexpected_error;
648 END IF;
649
650 IF (AMS_DEBUG_HIGH_ON) THEN
651
652
653
654 AMS_Utility_PVT.debug_message('Create Target Group for schedule : '||l_schedule_id );
655
656 END IF;
657 IF p_schedule_rec.activity_type_code IN ('DIRECT_MARKETING','INTERNET','DEAL','TRADE_PROMOTION') THEN
658 AMS_ScheduleRules_PVT.Create_list(
659 p_schedule_id => l_schedule_id,
660 p_schedule_name => p_schedule_rec.schedule_name,
661 p_owner_id => p_schedule_rec.owner_user_id) ;
662 IF x_return_status = FND_API.g_ret_sts_error THEN
663 RAISE FND_API.g_exc_error;
664 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
665 IF (AMS_DEBUG_HIGH_ON) THEN
666
667 AMS_Utility_PVT.debug_message('Errr '||sqlerrm);
668 END IF;
669 RAISE FND_API.g_exc_unexpected_error;
670 END IF;
671 END IF ;
672
673 x_schedule_id := l_schedule_id ;
674 --
675 -- End of API body
676 --
677
678 -- Standard check for p_commit
679 IF FND_API.to_Boolean( p_commit )
680 THEN
681 COMMIT WORK;
682 END IF;
683
684 -- Debug Message
685 IF (AMS_DEBUG_HIGH_ON) THEN
686
687 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
688 END IF;
689
690 -- Standard call to get message count and if count is 1, get message info.
691 FND_MSG_PUB.Count_And_Get
692 (p_count => x_msg_count,
693 p_data => x_msg_data
694 );
695 EXCEPTION
696
697 WHEN AMS_Utility_PVT.resource_locked THEN
698 x_return_status := FND_API.g_ret_sts_error;
699 AMS_Utility_Pvt.Error_Message('AMS_API_RESOURCE_LOCKED');
700
701 WHEN FND_API.G_EXC_ERROR THEN
702 ROLLBACK TO CREATE_Camp_Schedule_PVT;
703 x_return_status := FND_API.G_RET_STS_ERROR;
704 -- Standard call to get message count and if count=1, get the message
705 FND_MSG_PUB.Count_And_Get (
706 p_encoded => FND_API.G_FALSE,
707 p_count => x_msg_count,
708 p_data => x_msg_data
709 );
710
711 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
712 ROLLBACK TO CREATE_Camp_Schedule_PVT;
713 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
714 -- Standard call to get message count and if count=1, get the message
715 FND_MSG_PUB.Count_And_Get (
716 p_encoded => FND_API.G_FALSE,
717 p_count => x_msg_count,
718 p_data => x_msg_data
719 );
720
721 WHEN OTHERS THEN
722 ROLLBACK TO CREATE_Camp_Schedule_PVT;
723 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
724 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
725 THEN
726 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
727 END IF;
728 -- Standard call to get message count and if count=1, get the message
729 FND_MSG_PUB.Count_And_Get (
730 p_encoded => FND_API.G_FALSE,
731 p_count => x_msg_count,
732 p_data => x_msg_data
733 );
734 End Create_Camp_Schedule;
735
736 --===================================================================
737 -- NAME
738 -- Update_Camp_Schedule
739 --
740 -- PURPOSE
741 -- Private api to Update Campaign schedules.
742 --
743 -- NOTES
744 -- 1. When the Status of the schedule is updated , depending on
745 -- status order rules and custom setup , workflow process is
746 -- submitted for approvals.
747 --
748 -- HISTORY
749 -- 22-Jan-2001 PTENDULK Created
750 -- 26-sep-2001 soagrawa Modified start date and end date update
751 -- 13-dec-2001 soagrawa Modified source code related code, bug# 2133264
752 -- 20-mar-2002 soagrawa Modified source code related code, bug# 2273902
753 -- 06-jun-2002 soagrawa Fixed bug# 2406677. Now updating access table if owner changes
754 -- 27-jun-2003 anchaudh added the extra columns to be updated for triggers chnages.
755 -- 13-aug-2003 soagrawa Fixed bug# 3096925
756 -- 29-May-2006 srivikri added column delivery_mode
757 --===================================================================
758 PROCEDURE Update_Camp_Schedule(
759 p_api_version_number IN NUMBER,
760 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
761 p_commit IN VARCHAR2 := FND_API.G_FALSE,
762 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
763
764 x_return_status OUT NOCOPY VARCHAR2,
765 x_msg_count OUT NOCOPY NUMBER,
766 x_msg_data OUT NOCOPY VARCHAR2,
767
768 p_schedule_rec IN schedule_rec_type,
769 x_object_version_number OUT NOCOPY NUMBER
770 )
771
772 IS
773
774 CURSOR c_get_camp_schedule(schedule_id IN NUMBER) IS
775 SELECT *
776 FROM ams_campaign_schedules_vl
777 WHERE schedule_id = p_schedule_rec.schedule_id ;
778
779 -- Start: added by anchaudh for trigger related validation on 27-jun-2003.
780 CURSOR c_get_trig_schedule_details(schedule_id IN NUMBER) IS
781 SELECT status_code, triggerable_flag, trigger_id,
782 trig_repeat_flag, tgrp_exclude_prev_flag, activity_type_code
783 FROM ams_campaign_schedules_b
784 WHERE schedule_id = p_schedule_rec.schedule_id ;
785 -- End: added by anchaudh for trigger related validation on 27-jun-2003.
786
787 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Camp_Schedule';
788 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
789 -- Local Variables
790 l_object_version_number NUMBER;
791 l_schedule_id NUMBER;
792 l_ref_schedule_rec c_get_Camp_Schedule%ROWTYPE ;
793 l_complete_rec AMS_Camp_Schedule_PVT.schedule_rec_type := P_schedule_rec;
794 l_tar_schedule_rec AMS_Camp_Schedule_PVT.schedule_rec_type := P_schedule_rec;
795 x_source_code VARCHAR2(30);
796 l_source_code VARCHAR2(30);
797 -- Start: added by anchaudh for trigger related validation on 27-jun-2003.
798 l_status_code VARCHAR2(30);
799 l_triggerable_flag VARCHAR2(1);
800 l_trigger_id NUMBER;
801 l_trig_repeat_flag VARCHAR2(1);
802 l_tgrp_exclude_prev_flag VARCHAR2(1);
803 l_act_type_code VARCHAR2(30);
804 -- End: added by anchaudh for trigger related validation on 27-jun-2003.
805
806 l_field_ak_name_array JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100() ;
807 l_change_indicator_array JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100() ;
808
809
810 BEGIN
811 -- Standard Start of API savepoint
812 SAVEPOINT UPDATE_Camp_Schedule_PVT;
813
814 -- Standard call to check for call compatibility.
815 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
816 p_api_version_number,
817 l_api_name,
818 G_PKG_NAME)
819 THEN
820 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
821 END IF;
822
823 -- Initialize message list if p_init_msg_list is set to TRUE.
824 IF FND_API.to_Boolean( p_init_msg_list )
825 THEN
826 FND_MSG_PUB.initialize;
827 END IF;
828
829 -- Debug Message
830 IF (AMS_DEBUG_HIGH_ON) THEN
831
832 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
833 END IF;
834
835
836 -- Initialize API return status to SUCCESS
837 x_return_status := FND_API.G_RET_STS_SUCCESS;
838
839 -- Debug Message
840 IF (AMS_DEBUG_HIGH_ON) THEN
841
842 AMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
843 END IF;
844
845
846 OPEN c_get_Camp_Schedule(l_tar_schedule_rec.schedule_id);
847 FETCH c_get_Camp_Schedule INTO l_ref_schedule_rec ;
848
849 If ( c_get_Camp_Schedule%NOTFOUND) THEN
850 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
851 THEN
852 FND_MESSAGE.Set_Name('AMS', 'API_MISSING_UPDATE_TARGET');
853 FND_MESSAGE.Set_Token ('INFO', 'Camp_Schedule', FALSE);
854 FND_MSG_PUB.Add;
855 END IF;
856 RAISE FND_API.G_EXC_ERROR;
857 END IF;
858 -- Debug Message
859 IF (AMS_DEBUG_HIGH_ON) THEN
860
861 AMS_UTILITY_PVT.debug_message('Private API: - Close Cursor');
862 END IF;
863 CLOSE c_get_Camp_Schedule;
864
865
866 IF (l_tar_schedule_rec.object_version_number IS NULL OR
867 l_tar_schedule_rec.object_version_number = FND_API.G_MISS_NUM ) THEN
868 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
869 THEN
870 FND_MESSAGE.Set_Name('AMS', 'API_VERSION_MISSING');
871 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
872 FND_MSG_PUB.ADD;
873 END IF;
874 RAISE FND_API.G_EXC_ERROR;
875 END IF;
876
877 -- Check Whether record has been changed by someone else
878 IF (l_tar_schedule_rec.object_version_number <> l_ref_schedule_rec.object_version_number) Then
879 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
880 THEN
881 FND_MESSAGE.Set_Name('AMS', 'API_RECORD_CHANGED');
882 FND_MESSAGE.Set_Token('INFO', 'Camp_Schedule', FALSE);
883 FND_MSG_PUB.ADD;
884 END IF;
885 RAISE FND_API.G_EXC_ERROR;
886 END IF;
887
888 Complete_schedule_Rec(P_schedule_rec => p_schedule_rec ,
889 x_complete_rec => l_complete_rec);
890
891 -- soagrawa 13-aug-2003 modified code in the following lines to fix P1 bug 3096925
892 -- also modified cursor related to this.
893 -- Start: added by anchaudh for trigger related validation on 28-May-2003.
894 OPEN c_get_trig_schedule_details(l_tar_schedule_rec.schedule_id);
895 FETCH c_get_trig_schedule_details
896 INTO l_status_code, l_triggerable_flag, l_trigger_id,
897 l_trig_repeat_flag, l_tgrp_exclude_prev_flag, l_act_type_code ;
898 CLOSE c_get_trig_schedule_details;
899
900 IF (l_act_type_code = 'DIRECT_MARKETING' AND (l_status_code = 'ACTIVE')) THEN
901 IF ( l_complete_rec.triggerable_flag <> l_triggerable_flag
902 OR l_complete_rec.trigger_id <> l_trigger_id
903 OR l_complete_rec.trig_repeat_flag <> l_trig_repeat_flag
904 OR l_complete_rec.tgrp_exclude_prev_flag <> l_tgrp_exclude_prev_flag)
905 THEN
906 AMS_Utility_PVT.Error_Message('AMS_TRIG_DETAILS_NO_UPDATE');
907 x_return_status := FND_API.G_RET_STS_ERROR;
908 RAISE FND_API.G_EXC_ERROR;
909 END IF;
910 END IF;
911 -- End: added by anchaudh for trigger related validation on 28-May-2003.
912
913
914
915 -- Add Locking Rule API validation
916 -- soagrawa 17-may-2005 for Web ADI
917
918 /*
919 AMS_COVER_LETTER cover_letter_id
920 AMS_CSCH_OBJECTIVE objective_code
921 AMS_CURRENCY transaction_currency_code
922 AMS_DESCRIPTION description
923 AMS_END_DATE end_date_time
924 AMS_LANGUAGE language_code
925 AMS_MARKETING_MEDIUM marketing_medium_id
926 AMS_NAME schedule_name
927 AMS_REG_TIMEZONE timezone_id
928 AMS_REPLY_TO reply_to_mail
929 AMS_SENDER mail_sender_name
930 AMS_SEND_NOTIFICATION_TO notify_user_id
931 AMS_SOURCE_CODE source_code
932 AMS_START_DATE start_date_time
933 AMS_STATUS user_status_id
934 AMS_SUBJECT mail_subject
935 AMS_TRIG_EXEC approver_user_id
936 AMS_TRIG_TITLE triggerable_flag
937 AMS_USE_PARENT_SOURCE_CODE use_parent_code_flag
938 AMS_USE_TRIGGER trigger_id
939 */
940
941
942 FOR i in 1 .. 20
943 LOOP
944 l_field_ak_name_array.extend();
945 l_change_indicator_array.extend();
946 l_change_indicator_array(i) := 'N';
947 END LOOP;
948
949 l_field_ak_name_array(1) := 'AMS_COVER_LETTER';
950 l_field_ak_name_array(2) := 'AMS_CSCH_OBJECTIVE';
951 l_field_ak_name_array(3) := 'AMS_CURRENCY';
952 l_field_ak_name_array(4) := 'AMS_DESCRIPTION';
953 l_field_ak_name_array(5) := 'AMS_END_DATE';
954 l_field_ak_name_array(6) := 'AMS_LANGUAGE';
955 l_field_ak_name_array(7) := 'AMS_MARKETING_MEDIUM';
956 l_field_ak_name_array(8) := 'AMS_NAME';
957 l_field_ak_name_array(9) := 'AMS_REG_TIMEZONE';
958 l_field_ak_name_array(10) := 'AMS_REPLY_TO';
959 l_field_ak_name_array(11) := 'AMS_SENDER';
960 l_field_ak_name_array(12) := 'AMS_SEND_NOTIFICATION_TO';
961 l_field_ak_name_array(13) := 'AMS_SOURCE_CODE';
962 l_field_ak_name_array(14) := 'AMS_START_DATE';
963 l_field_ak_name_array(15) := 'AMS_STATUS';
964 l_field_ak_name_array(16) := 'AMS_SUBJECT';
965 l_field_ak_name_array(17) := 'AMS_TRIG_EXEC';
966 l_field_ak_name_array(18) := 'AMS_TRIG_TITLE';
967 l_field_ak_name_array(19) := 'AMS_USE_PARENT_SOURCE_CODE';
968 l_field_ak_name_array(20) := 'AMS_USE_TRIGGER';
969
970
971 IF l_ref_schedule_rec.cover_letter_id <> l_complete_rec.cover_letter_id
972 THEN
973 l_change_indicator_array(1) := 'Y';
974 END IF;
975 IF l_ref_schedule_rec.objective_code <> l_complete_rec.objective_code
976 THEN
977 l_change_indicator_array(2) := 'Y';
978 END IF;
979 IF l_ref_schedule_rec.transaction_currency_code <> l_complete_rec.transaction_currency_code
980 THEN
981 l_change_indicator_array(3) := 'Y';
982 END IF;
983 IF l_ref_schedule_rec.description <> l_complete_rec.description
984 THEN
985 l_change_indicator_array(4) := 'Y';
986 END IF;
987 IF l_ref_schedule_rec.end_date_time <> l_complete_rec.end_date_time
988 THEN
989 l_change_indicator_array(5) := 'Y';
990 END IF;
991 IF l_ref_schedule_rec.language_code <> l_complete_rec.language_code
992 THEN
993 l_change_indicator_array(6) := 'Y';
994 END IF;
995 IF l_ref_schedule_rec.marketing_medium_id <> l_complete_rec.marketing_medium_id
996 THEN
997 l_change_indicator_array(7) := 'Y';
998 END IF;
999 IF l_ref_schedule_rec.schedule_name <> l_complete_rec.schedule_name
1000 THEN
1001 l_change_indicator_array(8) := 'Y';
1002 END IF;
1003 IF l_ref_schedule_rec.timezone_id <> l_complete_rec.timezone_id
1004 THEN
1005 l_change_indicator_array(9) := 'Y';
1006 END IF;
1007 IF l_ref_schedule_rec.reply_to_mail <> l_complete_rec.reply_to_mail
1008 THEN
1009 l_change_indicator_array(10) := 'Y';
1010 END IF;
1011 IF l_ref_schedule_rec.mail_sender_name <> l_complete_rec.mail_sender_name
1012 THEN
1013 l_change_indicator_array(11) := 'Y';
1014 END IF;
1015 IF l_ref_schedule_rec.notify_user_id <> l_complete_rec.notify_user_id
1016 THEN
1017 l_change_indicator_array(12) := 'Y';
1018 END IF;
1019 IF l_ref_schedule_rec.source_code <> l_complete_rec.source_code
1020 THEN
1021 l_change_indicator_array(13) := 'Y';
1022 END IF;
1023 IF l_ref_schedule_rec.start_date_time <> l_complete_rec.start_date_time
1024 THEN
1025 l_change_indicator_array(14) := 'Y';
1026 END IF;
1027 IF l_ref_schedule_rec.user_status_id <> l_complete_rec.user_status_id
1028 THEN
1029 l_change_indicator_array(15) := 'Y';
1030 END IF;
1031 IF l_ref_schedule_rec.mail_subject <> l_complete_rec.mail_subject
1032 THEN
1033 l_change_indicator_array(16) := 'Y';
1034 END IF;
1035 IF l_ref_schedule_rec.approver_user_id <> l_complete_rec.approver_user_id
1036 THEN
1037 l_change_indicator_array(17) := 'Y';
1038 END IF;
1039 IF l_ref_schedule_rec.triggerable_flag <> l_complete_rec.triggerable_flag
1040 THEN
1041 l_change_indicator_array(18) := 'Y';
1042 END IF;
1043 IF l_ref_schedule_rec.use_parent_code_flag <> l_complete_rec.use_parent_code_flag
1044 THEN
1045 l_change_indicator_array(19) := 'Y';
1046 END IF;
1047 IF l_ref_schedule_rec.trigger_id <> l_complete_rec.trigger_id
1048 THEN
1049 l_change_indicator_array(20) := 'Y';
1050 END IF;
1051
1052
1053 AMS_Utility_PVT.validate_locking_rules(
1054 'AMS'
1055 , 'CSCH'
1056 , 'DETL'
1057 , l_ref_schedule_rec.status_code
1058 , l_field_ak_name_array
1059 , l_change_indicator_array
1060 , x_return_status);
1061
1062 IF x_return_status = FND_API.g_ret_sts_error THEN
1063 RAISE FND_API.g_exc_error;
1064 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1065 RAISE FND_API.g_exc_unexpected_error;
1066 END IF;
1067
1068 -- End Locking Rule API Validation
1069
1070
1071
1072 -- default campaign_calendar
1073 IF p_schedule_rec.start_period_name IS NULL
1074 AND p_schedule_rec.end_period_name IS NULL
1075 THEN
1076 l_tar_schedule_rec.campaign_calendar := NULL;
1077 ELSE
1078 l_tar_schedule_rec.campaign_calendar := FND_PROFILE.value('AMS_CAMPAIGN_DEFAULT_CALENDER');
1079 END IF;
1080
1081 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
1082 THEN
1083 -- Debug message
1084 IF (AMS_DEBUG_HIGH_ON) THEN
1085
1086 AMS_UTILITY_PVT.debug_message('Private API: Validate_Camp_Schedule');
1087 END IF;
1088
1089 -- Invoke validation procedures
1090 Validate_camp_schedule(
1091 p_api_version_number => 1.0,
1092 p_init_msg_list => FND_API.G_FALSE,
1093 p_validation_level => p_validation_level,
1094 p_schedule_rec => p_schedule_rec,
1095 p_validation_mode => JTF_PLSQL_API.g_update,
1096 x_return_status => x_return_status,
1097 x_msg_count => x_msg_count,
1098 x_msg_data => x_msg_data);
1099 END IF;
1100
1101 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1102 RAISE FND_API.G_EXC_ERROR;
1103 END IF;
1104
1105 -- Check Schedule Update
1106 AMS_ScheduleRules_PVT.Check_Schedule_Update(
1107 p_schedule_rec => l_complete_rec,
1108 x_return_status => x_return_status);
1109
1110 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1111 RAISE FND_API.G_EXC_ERROR;
1112 END IF;
1113 -- Handle Source Code update
1114
1115 if p_schedule_rec.source_code IS NULL THEN
1116 IF (AMS_DEBUG_HIGH_ON) THEN
1117
1118 AMS_Utility_PVT.debug_message(l_api_name ||': update source code');
1119 END IF;
1120 end if ;
1121 IF p_schedule_rec.use_parent_code_flag = FND_API.g_miss_char
1122 AND p_schedule_rec.source_code = FND_API.g_miss_char
1123 THEN
1124 -- following line added by soagrawa on 20-mar-2002
1125 l_source_code := l_ref_schedule_rec.source_code;
1126 -- NULL ;
1127 ELSE
1128 IF (AMS_DEBUG_HIGH_ON) THEN
1129
1130 AMS_Utility_PVT.debug_message(l_api_name ||': update source code222');
1131 END IF;
1132 AMS_ScheduleRules_PVT.Check_Source_Code(
1133 p_schedule_rec => p_schedule_rec,
1134 x_return_status => x_return_status,
1135 -- next line added by soagrawa on 12-dec-2001, bug# 2133264
1136 x_source_code => l_source_code -- l_complete_rec.source_code
1137 ) ;
1138 IF (AMS_DEBUG_HIGH_ON) THEN
1139
1140 AMS_Utility_PVT.debug_message(l_api_name ||': updated source code '||l_source_code);
1141 END IF;
1142 IF x_return_status = FND_API.g_ret_sts_error THEN
1143 RAISE FND_API.g_exc_error;
1144 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1145 RAISE FND_API.g_exc_unexpected_error;
1146 END IF;
1147
1148
1149
1150 END IF ;
1151
1152 -- Get the functional Currency code
1153 IF p_schedule_rec.budget_amount_tc IS NOT NULL AND
1154 p_schedule_rec.budget_amount_tc <> FND_API.G_MISS_NUM THEN
1155 IF (AMS_DEBUG_HIGH_ON) THEN
1156
1157 AMS_Utility_pvt.debug_message('Transaction curr : '||p_schedule_rec.transaction_currency_code);
1158 END IF;
1159 AMS_CampaignRules_PVT.Convert_Camp_Currency(
1160 p_tc_curr => p_schedule_rec.transaction_currency_code,
1161 p_tc_amt => p_schedule_rec.budget_amount_tc,
1162 x_fc_curr => l_tar_schedule_rec.functional_currency_code,
1163 x_fc_amt => l_tar_schedule_rec.budget_amount_fc
1164 ) ;
1165 END IF ;
1166
1167
1168 -- updating access denorm table added by soagrawa on 06-jun-2002
1169 --refer to bug# 2406677
1170
1171 -- Change the owner in Access table if the owner is changed.
1172
1173 IF p_schedule_rec.owner_user_id <> FND_API.g_miss_num
1174 THEN
1175 AMS_ScheduleRules_PVT.Update_Schedule_Owner(
1176 p_api_version => p_api_version_number,
1177 p_init_msg_list => p_init_msg_list,
1178 p_commit => p_commit,
1179 p_validation_level => p_validation_level,
1180 x_return_status => x_return_status,
1181 x_msg_count => x_msg_count,
1182 x_msg_data => x_msg_data,
1183 p_object_type => 'CSCH' ,
1184 p_schedule_id => p_schedule_rec.schedule_id,
1185 p_owner_id => p_schedule_rec.owner_user_id
1186 );
1187 IF x_return_status = FND_API.g_ret_sts_error THEN
1188 RAISE FND_API.g_exc_error;
1189 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1190 RAISE FND_API.g_exc_unexpected_error;
1191 END IF;
1192 END IF ;
1193
1194
1195
1196 -- Debug Message
1197 IF (AMS_DEBUG_HIGH_ON) THEN
1198
1199 AMS_UTILITY_PVT.debug_message('Private API: Calling update table handler sched : '||to_char(p_schedule_rec.schedule_id));
1200 END IF;
1201
1202 -- Invoke table handler(AMS_CAMPAIGN_SCHEDULES_B_PKG.Update_Row)
1203 AMS_CAMPAIGN_SCHEDULES_B_PKG.Update_Row(
1204 p_schedule_id => p_schedule_rec.schedule_id,
1205 p_last_update_date => SYSDATE,
1206 p_last_updated_by => G_USER_ID,
1207 p_creation_date => SYSDATE,
1208 p_created_by => G_USER_ID,
1209 p_last_update_login => G_LOGIN_ID,
1210 p_object_version_number => p_schedule_rec.object_version_number ,
1211 p_campaign_id => p_schedule_rec.campaign_id,
1212 p_user_status_id => FND_API.G_MISS_NUM,
1213 p_status_code => FND_API.G_MISS_CHAR,
1214 p_status_date => FND_API.G_MISS_DATE,
1215 -- modified on 12-dec-2001 by soagrawa, bug# 2133264
1216 -- p_source_code => p_schedule_rec.source_code,
1217 p_source_code => l_source_code,
1218 p_use_parent_code_flag => p_schedule_rec.use_parent_code_flag,
1219 -- modified by soagrawa on 26-sep-2001 after changing amstschb.pls
1220 -- p_start_date_time => p_schedule_rec.start_date_time,
1221 -- p_end_date_time => p_schedule_rec.end_date_time,
1222 p_start_date_time => l_complete_rec.start_date_time,
1223 p_end_date_time => l_complete_rec.end_date_time,
1224 p_timezone_id => p_schedule_rec.timezone_id,
1225 p_activity_type_code => p_schedule_rec.activity_type_code,
1226 p_activity_id => p_schedule_rec.activity_id,
1227 p_arc_marketing_medium_from => p_schedule_rec.arc_marketing_medium_from,
1228 p_marketing_medium_id => p_schedule_rec.marketing_medium_id,
1229 p_custom_setup_id => p_schedule_rec.custom_setup_id,
1230 p_triggerable_flag => p_schedule_rec.triggerable_flag,
1231 p_trigger_id => p_schedule_rec.trigger_id,
1232 p_notify_user_id => p_schedule_rec.notify_user_id,
1233 p_approver_user_id => p_schedule_rec.approver_user_id,
1234 p_owner_user_id => p_schedule_rec.owner_user_id,
1235 p_active_flag => p_schedule_rec.active_flag,
1236 -- soagrawa removed on 03-nov-2003 for 11.5.10
1237 p_cover_letter_id => null,
1238 p_reply_to_mail => p_schedule_rec.reply_to_mail,
1239 p_mail_sender_name => p_schedule_rec.mail_sender_name,
1240 p_mail_subject => p_schedule_rec.mail_subject,
1241 p_from_fax_no => p_schedule_rec.from_fax_no,
1242 p_accounts_closed_flag => p_schedule_rec.accounts_closed_flag,
1243 p_org_id => p_schedule_rec.org_id,
1244 p_objective_code => p_schedule_rec.objective_code,
1245 p_country_id => p_schedule_rec.country_id,
1246 p_campaign_calendar => l_tar_schedule_rec.campaign_calendar,
1247 p_start_period_name => p_schedule_rec.start_period_name,
1248 p_end_period_name => p_schedule_rec.end_period_name,
1249 p_priority => p_schedule_rec.priority,
1250 p_workflow_item_key => p_schedule_rec.workflow_item_key,
1251 p_transaction_currency_code => p_schedule_rec.transaction_currency_code,
1252 p_functional_currency_code => l_tar_schedule_rec.functional_currency_code,
1253 p_attribute_category => p_schedule_rec.attribute_category,
1254 p_budget_amount_tc => p_schedule_rec.budget_amount_tc,
1255 p_budget_amount_fc => l_tar_schedule_rec.budget_amount_fc,
1256 p_language_code => p_schedule_rec.language_code,
1257 p_task_id => p_schedule_rec.task_id,
1258 p_related_event_from => p_schedule_rec.related_event_from,
1259 p_related_event_id => p_schedule_rec.related_event_id,
1260 p_attribute1 => p_schedule_rec.attribute1,
1261 p_attribute2 => p_schedule_rec.attribute2,
1262 p_attribute3 => p_schedule_rec.attribute3,
1263 p_attribute4 => p_schedule_rec.attribute4,
1264 p_attribute5 => p_schedule_rec.attribute5,
1265 p_attribute6 => p_schedule_rec.attribute6,
1266 p_attribute7 => p_schedule_rec.attribute7,
1267 p_attribute8 => p_schedule_rec.attribute8,
1268 p_attribute9 => p_schedule_rec.attribute9,
1269 p_attribute10 => p_schedule_rec.attribute10,
1270 p_attribute11 => p_schedule_rec.attribute11,
1271 p_attribute12 => p_schedule_rec.attribute12,
1272 p_attribute13 => p_schedule_rec.attribute13,
1273 p_attribute14 => p_schedule_rec.attribute14,
1274 p_attribute15 => p_schedule_rec.attribute15,
1275 p_activity_attribute_category => p_schedule_rec.activity_attribute_category,
1276 p_activity_attribute1 => p_schedule_rec.activity_attribute1,
1277 p_activity_attribute2 => p_schedule_rec.activity_attribute2,
1278 p_activity_attribute3 => p_schedule_rec.activity_attribute3,
1279 p_activity_attribute4 => p_schedule_rec.activity_attribute4,
1280 p_activity_attribute5 => p_schedule_rec.activity_attribute5,
1281 p_activity_attribute6 => p_schedule_rec.activity_attribute6,
1282 p_activity_attribute7 => p_schedule_rec.activity_attribute7,
1283 p_activity_attribute8 => p_schedule_rec.activity_attribute8,
1284 p_activity_attribute9 => p_schedule_rec.activity_attribute9,
1285 p_activity_attribute10 => p_schedule_rec.activity_attribute10,
1286 p_activity_attribute11 => p_schedule_rec.activity_attribute11,
1287 p_activity_attribute12 => p_schedule_rec.activity_attribute12,
1288 p_activity_attribute13 => p_schedule_rec.activity_attribute13,
1289 p_activity_attribute14 => p_schedule_rec.activity_attribute14,
1290 p_activity_attribute15 => p_schedule_rec.activity_attribute15,
1291 -- removed by soagrawa on 24-sep-2001
1292 -- p_security_group_id => p_schedule_rec.security_group_id,
1293 p_query_id => p_schedule_rec.query_id,
1294 p_include_content_flag => p_schedule_rec.include_content_flag,
1295 p_content_type => p_schedule_rec.content_type,
1296 p_test_email_address => p_schedule_rec.test_email_address,
1297 p_schedule_name => p_schedule_rec.schedule_name,
1298 p_schedule_description => p_schedule_rec.description,
1299 p_greeting_text => p_schedule_rec.greeting_text,
1300 p_footer_text => p_schedule_rec.footer_text,
1301 --following is added by anchaudh on 27-jun-2003
1302 p_trig_repeat_flag => p_schedule_rec.trig_repeat_flag,
1303 p_tgrp_exclude_prev_flag => p_schedule_rec.tgrp_exclude_prev_flag,
1304 p_orig_csch_id => p_schedule_rec.orig_csch_id,
1305 p_cover_letter_version => p_schedule_rec.cover_letter_version,
1306 -- added by dbiswas on Aug 12, 2003
1307 p_usage => p_schedule_rec.usage,
1308 p_purpose => p_schedule_rec.purpose,
1309 p_last_activation_date => p_schedule_rec.last_activation_date,
1310 p_sales_methodology_id => p_schedule_rec.sales_methodology_id,
1311 p_printer_address => p_schedule_rec.printer_address,
1312 -- added by dbiswas on Jul 27, 2005
1313 p_notify_on_activation_flag => p_schedule_rec.notify_on_activation_flag,
1314 -- added by anchaudh on Feb 01, 2006
1315 p_sender_display_name => p_schedule_rec.sender_display_name,
1316 p_delivery_mode => p_schedule_rec.delivery_mode
1317 );
1318
1319 -- create object association when channel is event
1320 -- IF p_schedule_rec.media_type_code = 'EVENTS' THEN
1321 -- AMS_CampaignRules_PVT.create_camp_association(
1322 -- l_camp_rec.campaign_id,
1323 -- l_camp_rec.channel_id,
1324 -- l_camp_rec.arc_channel_from,
1325 -- l_return_status
1326 -- );
1327 -- IF l_return_status = FND_API.g_ret_sts_error THEN
1328 -- RAISE FND_API.g_exc_error;
1329 -- ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1330 -- RAISE FND_API.g_exc_unexpected_error;
1331 -- END IF;
1332 -- END IF;
1333
1334 -- update campaign status through workflow
1335 -- AMS_ScheduleRules_PVT.Update_Csch_status(
1336 -- p_schedule_rec.schedule_id,
1337 -- p_schedule_rec.user_status_id,
1338 -- p_schedule_rec.budget_amount_tc
1339 -- );
1340
1341 -- Following line of code is added by ptendulk on 31-May-2001
1342 -- to start the trigger workflow process.
1343 -- Start the trigger if the schedule is using trigger and the trigger
1344 -- does not have any other schedule associated to it.
1345
1346 /* IF p_schedule_rec.trigger_id IS NOT NULL AND
1347 p_schedule_rec.trigger_id <> FND_API.G_MISS_NUM AND
1348 l_complete_rec.triggerable_flag = 'Y'
1349 THEN
1350 AMS_ScheduleRules_PVT.Start_Trigger_Process(p_schedule_id => p_schedule_rec.schedule_id,
1351 p_trigger_id => l_complete_rec.trigger_id) ;
1352 END IF;*/
1353
1354 IF (AMS_DEBUG_HIGH_ON) THEN
1355
1356
1357
1358 AMS_Utility_PVT.Debug_Message('User Status Id : '||l_complete_rec.user_status_id) ;
1359
1360 END IF;
1361
1362 --insert_log_mesg('Anirban got value of asn_group_id in api Update_Camp_Schedule in amsvschb.pls as :'||p_schedule_rec.asn_group_id);
1363
1364 AMS_ScheduleRules_PVT.Update_Schedule_Status(
1365 p_schedule_id => p_schedule_rec.schedule_id,
1366 p_campaign_id => l_complete_rec.campaign_id,
1367 p_user_status_id => l_complete_rec.user_status_id,
1368 p_budget_amount => l_complete_rec.budget_amount_tc,
1369 p_asn_group_id => p_schedule_rec.asn_group_id ); -- anchaudh added for leads bug.
1370
1371 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1372 x_object_version_number := p_schedule_rec.object_version_number + 1 ;
1373 ELSE
1374 x_object_version_number := p_schedule_rec.object_version_number ;
1375 END IF;
1376 --
1377 -- End of API body.
1378 --
1379
1380
1381 -- Standard check for p_commit
1382 IF FND_API.to_Boolean( p_commit )
1383 THEN
1384 COMMIT WORK;
1385 END IF;
1386
1387
1388 -- Debug Message
1389 IF (AMS_DEBUG_HIGH_ON) THEN
1390
1391 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1392 END IF;
1393
1394 -- Standard call to get message count and if count is 1, get message info.
1395 FND_MSG_PUB.Count_And_Get
1396 (p_count => x_msg_count,
1397 p_data => x_msg_data
1398 );
1399 EXCEPTION
1400
1401 WHEN AMS_Utility_PVT.resource_locked THEN
1402 x_return_status := FND_API.g_ret_sts_error;
1403 AMS_Utility_Pvt.Error_Message('AMS_API_RESOURCE_LOCKED');
1404
1405 WHEN FND_API.G_EXC_ERROR THEN
1406 ROLLBACK TO UPDATE_Camp_Schedule_PVT;
1407 x_return_status := FND_API.G_RET_STS_ERROR;
1408 -- Standard call to get message count and if count=1, get the message
1409 FND_MSG_PUB.Count_And_Get (
1410 p_encoded => FND_API.G_FALSE,
1411 p_count => x_msg_count,
1412 p_data => x_msg_data
1413 );
1414
1415 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1416 ROLLBACK TO UPDATE_Camp_Schedule_PVT;
1417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1418 -- Standard call to get message count and if count=1, get the message
1419 FND_MSG_PUB.Count_And_Get (
1420 p_encoded => FND_API.G_FALSE,
1421 p_count => x_msg_count,
1422 p_data => x_msg_data
1423 );
1424
1425 WHEN OTHERS THEN
1426 ROLLBACK TO UPDATE_Camp_Schedule_PVT;
1427 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1428 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1429 THEN
1430 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1431 END IF;
1432 -- Standard call to get message count and if count=1, get the message
1433 FND_MSG_PUB.Count_And_Get (
1434 p_encoded => FND_API.G_FALSE,
1435 p_count => x_msg_count,
1436 p_data => x_msg_data
1437 );
1438 End Update_Camp_Schedule;
1439
1440 --===================================================================
1441 -- NAME
1442 -- Delete_Camp_Schedule
1443 --
1444 -- PURPOSE
1445 -- Private api to Delete Campaign schedules.
1446 --
1447 -- NOTES
1448 -- 1. Schedule will be deleted from database if the user attempts
1449 -- delete NEW schedules.
1450 -- 2. if the schedule is not new, any attempt to delete the schedule
1451 -- will update the active flag to 'N'
1452 --
1453 -- HISTORY
1454 -- 22-Jan-2001 PTENDULK Created
1455 --===================================================================
1456 PROCEDURE Delete_Camp_Schedule(
1457 p_api_version_number IN NUMBER,
1458 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1459 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1460 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1461 x_return_status OUT NOCOPY VARCHAR2,
1462 x_msg_count OUT NOCOPY NUMBER,
1463 x_msg_data OUT NOCOPY VARCHAR2,
1464 p_schedule_id IN NUMBER,
1465 p_object_version_number IN NUMBER
1466 )
1467
1468 IS
1469 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Camp_Schedule';
1470 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1471 l_object_version_number NUMBER;
1472
1473 CURSOR c_camp_schedule IS
1474 SELECT campaign_id, schedule_id, status_code,use_parent_code_flag,source_code
1475 FROM ams_campaign_schedules_b
1476 WHERE schedule_id = p_schedule_id
1477 AND object_version_number = p_object_version_number ;
1478
1479 l_schedule_id NUMBER;
1480 l_status_code VARCHAR2(30) ;
1481 l_campaign_id NUMBER ;
1482 l_dummy NUMBER ;
1483 l_cascade_flag VARCHAR2(1);
1484 l_source_code VARCHAR2(30);
1485
1486 CURSOR c_camp IS
1487 SELECT 1
1488 FROM ams_campaign_schedules_b
1489 WHERE campaign_id = l_campaign_id
1490 AND active_flag = 'Y' ;
1491
1492
1493 BEGIN
1494 -- Standard Start of API savepoint
1495 SAVEPOINT DELETE_Camp_Schedule_PVT;
1496
1497 -- Standard call to check for call compatibility.
1498 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1499 p_api_version_number,
1500 l_api_name,
1501 G_PKG_NAME)
1502 THEN
1503 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1504 END IF;
1505
1506 -- Initialize message list if p_init_msg_list is set to TRUE.
1507 IF FND_API.to_Boolean( p_init_msg_list )
1508 THEN
1509 FND_MSG_PUB.initialize;
1510 END IF;
1511
1512 -- Debug Message
1513 IF (AMS_DEBUG_HIGH_ON) THEN
1514
1515 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1516 END IF;
1517
1518 -- Initialize API return status to SUCCESS
1519 x_return_status := FND_API.G_RET_STS_SUCCESS;
1520
1521 OPEN c_camp_schedule;
1522 FETCH c_camp_schedule INTO l_campaign_id, l_schedule_id, l_status_code, l_cascade_flag, l_source_code;
1523 IF (c_camp_schedule%NOTFOUND) THEN
1524 CLOSE c_camp_schedule;
1525 AMS_Utility_PVT.Error_Message('AMS_API_RECORD_NOT_FOUND');
1526 RAISE FND_API.g_exc_error;
1527 END IF;
1528 CLOSE c_camp_schedule;
1529
1530 --
1531 -- Api body
1532 --
1533 -- Debug Message
1534 IF (AMS_DEBUG_HIGH_ON) THEN
1535
1536 AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
1537 END IF;
1538
1539 -- Delete schedule only if the status is new.
1540 -- Invoke table handler(AMS_CAMPAIGN_SCHEDULES_B_PKG.Delete_Row)
1541 IF l_status_code = 'NEW' THEN
1542 AMS_CAMPAIGN_SCHEDULES_B_PKG.Delete_Row(p_schedule_id => p_schedule_id);
1543 ELSE
1544 UPDATE ams_campaign_schedules_b
1545 SET object_version_number = object_version_number + 1 ,
1546 active_flag = 'N'
1547 WHERE schedule_id = p_schedule_id
1548 AND object_version_number = p_object_version_number ;
1549 END IF ;
1550
1551
1552 OPEN c_camp ;
1553 FETCH c_camp INTO l_dummy ;
1554 CLOSE c_camp ;
1555
1556 -- Revoke the source code
1557 IF l_cascade_flag = 'N' THEN
1558 AMS_SourceCode_PVT.revoke_sourcecode(
1559 p_api_version => 1.0,
1560 p_init_msg_list => FND_API.g_false,
1561 p_commit => FND_API.g_false,
1562 p_validation_level => FND_API.g_valid_level_full,
1563
1564 x_return_status => x_return_status,
1565 x_msg_count => x_msg_count,
1566 x_msg_data => x_msg_data,
1567
1568 p_sourcecode => l_source_code
1569 );
1570 END IF ;
1571
1572 --
1573 -- End of API body
1574 --
1575
1576 -- Standard check for p_commit
1577 IF FND_API.to_Boolean(p_commit)
1578 THEN
1579 COMMIT WORK;
1580 END IF;
1581
1582
1583 -- Debug Message
1584 IF (AMS_DEBUG_HIGH_ON) THEN
1585
1586 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1587 END IF;
1588
1589 -- Standard call to get message count and if count is 1, get message info.
1590 FND_MSG_PUB.Count_And_Get
1591 (p_count => x_msg_count,
1592 p_data => x_msg_data
1593 );
1594 EXCEPTION
1595
1596 WHEN AMS_Utility_PVT.resource_locked THEN
1597 x_return_status := FND_API.g_ret_sts_error;
1598 AMS_Utility_PVT.Error_Message('AMS_API_RESOURCE_LOCKED');
1599
1600 WHEN FND_API.G_EXC_ERROR THEN
1601 ROLLBACK TO DELETE_Camp_Schedule_PVT;
1602 x_return_status := FND_API.G_RET_STS_ERROR;
1603 -- Standard call to get message count and if count=1, get the message
1604 FND_MSG_PUB.Count_And_Get (
1605 p_encoded => FND_API.G_FALSE,
1606 p_count => x_msg_count,
1607 p_data => x_msg_data
1608 );
1609
1610 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1611 ROLLBACK TO DELETE_Camp_Schedule_PVT;
1612 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1613 -- Standard call to get message count and if count=1, get the message
1614 FND_MSG_PUB.Count_And_Get (
1615 p_encoded => FND_API.G_FALSE,
1616 p_count => x_msg_count,
1617 p_data => x_msg_data
1618 );
1619
1620 WHEN OTHERS THEN
1621 ROLLBACK TO DELETE_Camp_Schedule_PVT;
1622 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1623 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1624 THEN
1625 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1626 END IF;
1627 -- Standard call to get message count and if count=1, get the message
1628 FND_MSG_PUB.Count_And_Get (
1629 p_encoded => FND_API.G_FALSE,
1630 p_count => x_msg_count,
1631 p_data => x_msg_data
1632 );
1633 End Delete_Camp_Schedule;
1634
1635
1636 --===================================================================
1637 -- NAME
1638 -- Lock_Camp_Schedule
1639 --
1640 -- PURPOSE
1641 -- Private api to Lock Campaign schedules.
1642 --
1643 -- NOTES
1644 --
1645 -- HISTORY
1646 -- 22-Jan-2001 PTENDULK Created
1647 --===================================================================
1648 PROCEDURE Lock_Camp_Schedule(
1649 p_api_version_number IN NUMBER,
1650 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1651
1652 x_return_status OUT NOCOPY VARCHAR2,
1653 x_msg_count OUT NOCOPY NUMBER,
1654 x_msg_data OUT NOCOPY VARCHAR2,
1655
1656 p_schedule_id IN NUMBER,
1657 p_object_version IN NUMBER
1658 )
1659
1660 IS
1661 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Camp_Schedule';
1662 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1663 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1664 l_SCHEDULE_ID NUMBER;
1665
1666 CURSOR c_Camp_Schedule IS
1667 SELECT SCHEDULE_ID
1668 FROM AMS_CAMPAIGN_SCHEDULES_B
1669 WHERE SCHEDULE_ID = p_SCHEDULE_ID
1670 AND object_version_number = p_object_version
1671 FOR UPDATE NOWAIT;
1672 BEGIN
1673
1674 -- Debug Message
1675 IF (AMS_DEBUG_HIGH_ON) THEN
1676
1677 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1678 END IF;
1679
1680 -- Initialize message list if p_init_msg_list is set to TRUE.
1681 IF FND_API.to_Boolean( p_init_msg_list )
1682 THEN
1683 FND_MSG_PUB.initialize;
1684 END IF;
1685
1686 -- Standard call to check for call compatibility.
1687 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1688 p_api_version_number,
1689 l_api_name,
1690 G_PKG_NAME)
1691 THEN
1692 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1693 END IF;
1694
1695
1696 -- Initialize API return status to SUCCESS
1697 x_return_status := FND_API.G_RET_STS_SUCCESS;
1698
1699
1700 ------------------------ lock -------------------------
1701
1702 IF (AMS_DEBUG_HIGH_ON) THEN
1703
1704
1705
1706 AMS_Utility_PVT.debug_message(l_full_name||': start');
1707
1708 END IF;
1709 OPEN c_Camp_Schedule;
1710 FETCH c_Camp_Schedule INTO l_SCHEDULE_ID;
1711
1712 IF (c_Camp_Schedule%NOTFOUND) THEN
1713 CLOSE c_Camp_Schedule;
1714 AMS_Utility_PVT.Error_Message('AMS_API_RECORD_NOT_FOUND');
1715 RAISE FND_API.g_exc_error;
1716 END IF;
1717 CLOSE c_Camp_Schedule;
1718
1719 -------------------- finish --------------------------
1720 FND_MSG_PUB.count_and_get(
1721 p_encoded => FND_API.g_false,
1722 p_count => x_msg_count,
1723 p_data => x_msg_data);
1724 IF (AMS_DEBUG_HIGH_ON) THEN
1725
1726 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1727 END IF;
1728 EXCEPTION
1729
1730 WHEN AMS_Utility_PVT.resource_locked THEN
1731 x_return_status := FND_API.g_ret_sts_error;
1732 AMS_Utility_PVT.Error_Message('AMS_API_RESOURCE_LOCKED');
1733
1734 WHEN FND_API.G_EXC_ERROR THEN
1735 ROLLBACK TO LOCK_Camp_Schedule_PVT;
1736 x_return_status := FND_API.G_RET_STS_ERROR;
1737 -- Standard call to get message count and if count=1, get the message
1738 FND_MSG_PUB.Count_And_Get (
1739 p_encoded => FND_API.G_FALSE,
1740 p_count => x_msg_count,
1741 p_data => x_msg_data
1742 );
1743
1744 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1745 ROLLBACK TO LOCK_Camp_Schedule_PVT;
1746 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1747 -- Standard call to get message count and if count=1, get the message
1748 FND_MSG_PUB.Count_And_Get (
1749 p_encoded => FND_API.G_FALSE,
1750 p_count => x_msg_count,
1751 p_data => x_msg_data
1752 );
1753
1754 WHEN OTHERS THEN
1755 ROLLBACK TO LOCK_Camp_Schedule_PVT;
1756 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1757 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1758 THEN
1759 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1760 END IF;
1761 -- Standard call to get message count and if count=1, get the message
1762 FND_MSG_PUB.Count_And_Get (
1763 p_encoded => FND_API.G_FALSE,
1764 p_count => x_msg_count,
1765 p_data => x_msg_data
1766 );
1767 End Lock_Camp_Schedule;
1768
1769 --===================================================================
1770 -- NAME
1771 -- Check_Schedule_Uk_Items
1772 --
1773 -- PURPOSE
1774 -- Private api to check unique keys for Campaign schedules.
1775 --
1776 -- NOTES
1777 --
1778 -- HISTORY
1779 -- 22-Jan-2001 PTENDULK Created
1780 --===================================================================
1781 PROCEDURE Check_Schedule_Uk_Items(
1782 p_schedule_rec IN schedule_rec_type,
1783 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1784 x_return_status OUT NOCOPY VARCHAR2)
1785 IS
1786 l_valid_flag VARCHAR2(1);
1787
1788 BEGIN
1789 x_return_status := FND_API.g_ret_sts_success;
1790
1791 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1792 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
1793 'ams_campaign_schedules_b',
1794 'schedule_id = ' || p_schedule_rec.schedule_id
1795 );
1796 IF l_valid_flag = FND_API.g_false THEN
1797 AMS_Utility_PVT.Error_Message('AMS_CSCH_ID_DUPLICATE');
1798 x_return_status := FND_API.g_ret_sts_error;
1799 RETURN;
1800 END IF;
1801 END IF;
1802
1803 -- added by soagrawa on 11-jan-2002
1804 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1805 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
1806 'ams_campaign_schedules_b',
1807 'related_event_id = ' || p_schedule_rec.related_event_id
1808 );
1809 IF l_valid_flag = FND_API.g_false THEN
1810 AMS_Utility_PVT.Error_Message('AMS_EVO_DUPLICATE_ID');
1811 x_return_status := FND_API.g_ret_sts_error;
1812 RETURN;
1813 END IF;
1814 END IF;
1815
1816 END Check_Schedule_Uk_Items;
1817
1818 --===================================================================
1819 -- NAME
1820 -- Check_Schedule_Req_Items
1821 --
1822 -- PURPOSE
1823 -- Private api to check Required items for Campaign schedules.
1824 --
1825 -- NOTES
1826 --
1827 -- HISTORY
1828 -- 22-Jan-2001 PTENDULK Created
1829 --===================================================================
1830 PROCEDURE Check_Schedule_Req_Items(
1831 p_schedule_rec IN schedule_rec_type,
1832 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1833 x_return_status OUT NOCOPY VARCHAR2
1834 )
1835 IS
1836 BEGIN
1837 x_return_status := FND_API.g_ret_sts_success;
1838
1839 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1840
1841 IF p_schedule_rec.campaign_id = FND_API.g_miss_num OR p_schedule_rec.campaign_id IS NULL THEN
1842 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_NO_CAMP_ID') ;
1843 x_return_status := FND_API.g_ret_sts_error;
1844 RETURN;
1845 END IF;
1846
1847
1848 IF p_schedule_rec.user_status_id = FND_API.g_miss_num OR p_schedule_rec.user_status_id IS NULL THEN
1849 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_NO_STATUS_ID') ;
1850 x_return_status := FND_API.g_ret_sts_error;
1851 RETURN;
1852 END IF;
1853
1854
1855 IF p_schedule_rec.activity_type_code = FND_API.g_miss_char OR p_schedule_rec.activity_type_code IS NULL THEN
1856 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_NO_MEDIA_TYPE') ;
1857 x_return_status := FND_API.g_ret_sts_error;
1858 RETURN;
1859 END IF;
1860
1861 IF p_schedule_rec.custom_setup_id = FND_API.g_miss_num OR p_schedule_rec.custom_setup_id IS NULL THEN
1862 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_NO_CUS_SETUP') ;
1863 x_return_status := FND_API.g_ret_sts_error;
1864 RETURN;
1865 END IF;
1866
1867 IF p_schedule_rec.triggerable_flag = FND_API.g_miss_char OR p_schedule_rec.triggerable_flag IS NULL THEN
1868 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_NO_TRIG_FLAG') ;
1869 x_return_status := FND_API.g_ret_sts_error;
1870 RETURN;
1871 END IF;
1872
1873 IF p_schedule_rec.owner_user_id = FND_API.g_miss_num OR p_schedule_rec.owner_user_id IS NULL THEN
1874 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_NO_OWNER') ;
1875 x_return_status := FND_API.g_ret_sts_error;
1876 RETURN;
1877 END IF;
1878
1879
1880 IF p_schedule_rec.active_flag = FND_API.g_miss_char OR p_schedule_rec.active_flag IS NULL THEN
1881 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_NO_ACTIVE_FLAG') ;
1882 x_return_status := FND_API.g_ret_sts_error;
1883 RETURN;
1884 END IF;
1885
1886 IF p_schedule_rec.country_id = FND_API.g_miss_num OR p_schedule_rec.country_id IS NULL THEN
1887 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_NO_COUNTRY') ;
1888 x_return_status := FND_API.g_ret_sts_error;
1889 RETURN;
1890 END IF;
1891
1892 IF p_schedule_rec.schedule_name = FND_API.g_miss_char OR p_schedule_rec.schedule_name IS NULL THEN
1893 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_NO_NAME') ;
1894 x_return_status := FND_API.g_ret_sts_error;
1895 RETURN;
1896 END IF;
1897 ELSE
1898 IF p_schedule_rec.campaign_id IS NULL THEN
1899 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_NO_CAMP_ID') ;
1900 x_return_status := FND_API.g_ret_sts_error;
1901 RETURN;
1902 END IF;
1903
1904 IF p_schedule_rec.user_status_id IS NULL THEN
1905 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_NO_STATUS_ID') ;
1906 x_return_status := FND_API.g_ret_sts_error;
1907 RETURN;
1908 END IF;
1909
1910 IF p_schedule_rec.activity_type_code IS NULL THEN
1911 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_NO_MEDIA_TYPE') ;
1912 x_return_status := FND_API.g_ret_sts_error;
1913 RETURN;
1914 END IF;
1915
1916 IF p_schedule_rec.custom_setup_id IS NULL THEN
1917 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_NO_CUS_SETUP') ;
1918 x_return_status := FND_API.g_ret_sts_error;
1919 RETURN;
1920 END IF;
1921
1922 IF p_schedule_rec.triggerable_flag IS NULL THEN
1923 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_NO_TRIG_FLAG') ;
1924 x_return_status := FND_API.g_ret_sts_error;
1925 RETURN;
1926 END IF;
1927
1928 IF p_schedule_rec.owner_user_id IS NULL THEN
1929 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_NO_OWNER') ;
1930 x_return_status := FND_API.g_ret_sts_error;
1931 RETURN;
1932 END IF;
1933
1934 IF p_schedule_rec.active_flag IS NULL THEN
1935 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_NO_ACTIVE_FLAG') ;
1936 x_return_status := FND_API.g_ret_sts_error;
1937 RETURN;
1938 END IF;
1939
1940 IF p_schedule_rec.country_id IS NULL THEN
1941 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_NO_COUNTRY') ;
1942 x_return_status := FND_API.g_ret_sts_error;
1943 RETURN;
1944 END IF;
1945
1946 IF p_schedule_rec.schedule_name IS NULL THEN
1947 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_NO_NAME') ;
1948 x_return_status := FND_API.g_ret_sts_error;
1949 RETURN;
1950 END IF;
1951 END IF;
1952
1953 END Check_Schedule_Req_Items;
1954
1955 --===================================================================
1956 -- NAME
1957 -- Check_Schedule_FK_Items
1958 --
1959 -- PURPOSE
1960 -- Private api to check foreign key items for Campaign schedules.
1961 --
1962 -- NOTES
1963 --
1964 -- HISTORY
1965 -- 22-Jan-2001 PTENDULK Created
1966 --===================================================================
1967 PROCEDURE Check_Schedule_FK_Items(
1968 p_schedule_rec IN schedule_rec_type,
1969 x_return_status OUT NOCOPY VARCHAR2
1970 )
1971 IS
1972 l_table_name VARCHAR2(30);
1973 l_pk_name VARCHAR2(30);
1974 l_pk_value VARCHAR2(30);
1975 l_pk_data_type NUMBER;
1976 l_additional_where_clause VARCHAR2(4000); -- Used by Check_FK_Exists.
1977 BEGIN
1978 x_return_status := FND_API.g_ret_sts_success;
1979
1980 -- Check Campaign Id
1981 IF p_schedule_rec.campaign_id <> FND_API.g_miss_num THEN
1982 l_table_name := 'ams_campaigns_all_b' ;
1983 l_pk_name := 'campaign_id' ;
1984 l_pk_value := p_schedule_rec.campaign_id ;
1985 l_pk_data_type := AMS_Utility_PVT.G_NUMBER ;
1986 l_additional_where_clause := NULL ;
1987
1988 IF AMS_Utility_PVT.check_fk_exists(
1989 p_table_name => l_table_name,
1990 p_pk_name => l_pk_name,
1991 p_pk_value => l_pk_value,
1992 p_pk_data_type => l_pk_data_type,
1993 p_additional_where_clause => l_additional_where_clause
1994 ) = FND_API.g_false
1995 THEN
1996 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CAMP_BAD_CAMP_ID') ;
1997 x_return_status := FND_API.g_ret_sts_error;
1998 RETURN;
1999 END IF;
2000 END IF;
2001
2002 -- Check User Status Id
2003 IF p_schedule_rec.user_status_id <> FND_API.g_miss_num THEN
2004 l_table_name := 'ams_user_statuses_b' ;
2005 l_pk_name := 'user_status_id' ;
2006 l_pk_value := p_schedule_rec.user_status_id ;
2007 l_pk_data_type := AMS_Utility_PVT.G_NUMBER ;
2008 l_additional_where_clause := ' NVL(start_date_active,SYSDATE) <= SYSDATE ' ||
2009 ' AND NVL(end_date_active,SYSDATE) >= SYSDATE ';
2010
2011 IF AMS_Utility_PVT.check_fk_exists(
2012 p_table_name => l_table_name,
2013 p_pk_name => l_pk_name,
2014 p_pk_value => l_pk_value,
2015 p_pk_data_type => l_pk_data_type,
2016 p_additional_where_clause => l_additional_where_clause
2017 ) = FND_API.g_false
2018 THEN
2019 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_BAD_USER_STATUS') ;
2020 x_return_status := FND_API.g_ret_sts_error;
2021 RETURN;
2022 END IF;
2023 END IF;
2024
2025 -- Check Timezone Id
2026 IF p_schedule_rec.timezone_id <> FND_API.g_miss_num
2027 AND p_schedule_rec.timezone_id IS NOT NULL THEN
2028 l_table_name := 'fnd_timezones_b';
2029 l_pk_name := 'upgrade_tz_id' ;
2030 l_pk_data_type := AMS_Utility_PVT.G_NUMBER ;
2031 l_pk_value := p_schedule_rec.timezone_id ;
2032 l_additional_where_clause := null ;
2033
2034 IF AMS_Utility_PVT.check_fk_exists(
2035 p_table_name => l_table_name,
2036 p_pk_name => l_pk_name,
2037 p_pk_value => l_pk_value,
2038 p_pk_data_type => l_pk_data_type,
2039 p_additional_where_clause => l_additional_where_clause
2040 ) = FND_API.g_false
2041 THEN
2042 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CAMP_BAD_CAMP_ID') ;
2043 x_return_status := FND_API.g_ret_sts_error;
2044 RETURN;
2045 END IF;
2046 END IF;
2047
2048 -- Check Custom Setup Id
2049 IF p_schedule_rec.custom_setup_id <> FND_API.g_miss_num THEN
2050 l_table_name := 'ams_custom_setups_b';
2051 l_pk_name := 'custom_setup_id' ;
2052 l_pk_data_type := AMS_Utility_PVT.G_NUMBER ;
2053 l_pk_value := p_schedule_rec.custom_setup_id ;
2054 l_additional_where_clause := null ;
2055
2056 IF AMS_Utility_PVT.check_fk_exists(
2057 p_table_name => l_table_name,
2058 p_pk_name => l_pk_name,
2059 p_pk_value => l_pk_value,
2060 p_pk_data_type => l_pk_data_type,
2061 p_additional_where_clause => l_additional_where_clause
2062 ) = FND_API.g_false
2063 THEN
2064 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_BAD_SETUP') ;
2065 x_return_status := FND_API.g_ret_sts_error;
2066 RETURN;
2067 END IF;
2068 END IF;
2069
2070
2071 -- Check Trigger Id
2072 IF p_schedule_rec.trigger_id <> FND_API.g_miss_num AND
2073 p_schedule_rec.trigger_id IS NOT NULL THEN
2074 l_table_name := 'ams_triggers';
2075 l_pk_name := 'trigger_id' ;
2076 l_pk_data_type := AMS_Utility_PVT.G_NUMBER ;
2077 l_pk_value := p_schedule_rec.trigger_id ;
2078 l_additional_where_clause := null ;
2079
2080 IF AMS_Utility_PVT.check_fk_exists(
2081 p_table_name => l_table_name,
2082 p_pk_name => l_pk_name,
2083 p_pk_value => l_pk_value,
2084 p_pk_data_type => l_pk_data_type,
2085 p_additional_where_clause => l_additional_where_clause
2086 ) = FND_API.g_false
2087 THEN
2088 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_BAD_TRIGGER') ;
2089 x_return_status := FND_API.g_ret_sts_error;
2090 RETURN;
2091 END IF;
2092 END IF;
2093
2094 -- Check Notify User Id
2095 IF p_schedule_rec.notify_user_id <> FND_API.g_miss_num AND
2096 p_schedule_rec.notify_user_id IS NOT NULL THEN
2097 l_table_name := 'ams_jtf_rs_emp_v';
2098 l_pk_name := 'resource_id' ;
2099 l_pk_data_type := AMS_Utility_PVT.G_NUMBER ;
2100 l_pk_value := p_schedule_rec.notify_user_id ;
2101 l_additional_where_clause := null ;
2102
2103 IF AMS_Utility_PVT.check_fk_exists(
2104 p_table_name => l_table_name,
2105 p_pk_name => l_pk_name,
2106 p_pk_value => l_pk_value,
2107 p_pk_data_type => l_pk_data_type,
2108 p_additional_where_clause => l_additional_where_clause
2109 ) = FND_API.g_false
2110 THEN
2111 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_BAD_NOTIFY_TO') ;
2112 x_return_status := FND_API.g_ret_sts_error;
2113 RETURN;
2114 END IF;
2115 END IF;
2116
2117 -- Check Approver User Id
2118 IF p_schedule_rec.approver_user_id <> FND_API.g_miss_num AND
2119 p_schedule_rec.approver_user_id IS NOT NULL THEN
2120 l_table_name := 'ams_jtf_rs_emp_v';
2121 l_pk_name := 'resource_id' ;
2122 l_pk_data_type := AMS_Utility_PVT.G_NUMBER ;
2123 l_pk_value := p_schedule_rec.approver_user_id ;
2124 l_additional_where_clause := null ;
2125
2126 IF AMS_Utility_PVT.check_fk_exists(
2127 p_table_name => l_table_name,
2128 p_pk_name => l_pk_name,
2129 p_pk_value => l_pk_value,
2130 p_pk_data_type => l_pk_data_type,
2131 p_additional_where_clause => l_additional_where_clause
2132 ) = FND_API.g_false
2133 THEN
2134 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_BAD_APPROVER') ;
2135 x_return_status := FND_API.g_ret_sts_error;
2136 RETURN;
2137 END IF;
2138 END IF;
2139
2140 -- Check Cover Letter
2141 -- This code modified by soagrawa on 23-apr-2002
2142 -- as we will no longer be using JTF cover letters
2143 -- This is for new schedule eblast, wherein a cover letter is an IBC content item
2144
2145 /*
2146 IF p_schedule_rec.cover_letter_id <> FND_API.g_miss_num AND
2147 p_schedule_rec.cover_letter_id IS NOT NULL THEN
2148 l_table_name := 'jtf_amv_items_b';
2149 l_pk_name := 'item_id' ;
2150 l_pk_data_type := AMS_Utility_PVT.G_NUMBER ;
2151 l_pk_value := p_schedule_rec.cover_letter_id ;
2152 l_additional_where_clause := ' content_type_id = 20 ' ;
2153
2154 IF AMS_Utility_PVT.check_fk_exists(
2155 p_table_name => l_table_name,
2156 p_pk_name => l_pk_name,
2157 p_pk_value => l_pk_value,
2158 p_pk_data_type => l_pk_data_type,
2159 p_additional_where_clause => l_additional_where_clause
2160 ) = FND_API.g_false
2161 THEN
2162 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_BAD_COVER_LETTER') ;
2163 x_return_status := FND_API.g_ret_sts_error;
2164 RETURN;
2165 END IF;
2166 END IF;
2167 */
2168
2169 IF p_schedule_rec.cover_letter_id <> FND_API.g_miss_num AND
2170 p_schedule_rec.cover_letter_id IS NOT NULL THEN
2171 l_table_name := 'ibc_content_items';
2172 l_pk_name := 'content_item_id' ;
2173 l_pk_data_type := AMS_Utility_PVT.G_NUMBER ;
2174 l_pk_value := p_schedule_rec.cover_letter_id ;
2175 -- soagrawa modified on 03-nov-2003 for 11.5.10
2176 --l_additional_where_clause := ' content_type_code = ''AMF_TEMPLATE'' ' ;
2177 l_additional_where_clause := NULL;
2178
2179 IF AMS_Utility_PVT.check_fk_exists(
2180 p_table_name => l_table_name,
2181 p_pk_name => l_pk_name,
2182 p_pk_value => l_pk_value,
2183 p_pk_data_type => l_pk_data_type,
2184 p_additional_where_clause => l_additional_where_clause
2185 ) = FND_API.g_false
2186 THEN
2187 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_BAD_COVER_LETTER') ;
2188 x_return_status := FND_API.g_ret_sts_error;
2189 RETURN;
2190 END IF;
2191 END IF;
2192
2193
2194
2195 -- Check country
2196 IF p_schedule_rec.country_id <> FND_API.g_miss_num AND
2197 p_schedule_rec.country_id IS NOT NULL THEN
2198 l_table_name := 'jtf_loc_hierarchies_b';
2199 l_pk_name := 'location_hierarchy_id' ;
2200 l_pk_data_type := AMS_Utility_PVT.G_NUMBER ;
2201 l_pk_value := p_schedule_rec.country_id ;
2202 l_additional_where_clause := null ;
2203
2204 IF AMS_Utility_PVT.check_fk_exists(
2205 p_table_name => l_table_name,
2206 p_pk_name => l_pk_name,
2207 p_pk_value => l_pk_value,
2208 p_pk_data_type => l_pk_data_type,
2209 p_additional_where_clause => l_additional_where_clause
2210 ) = FND_API.g_false
2211 THEN
2212 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_BAD_COUNTRY') ;
2213 x_return_status := FND_API.g_ret_sts_error;
2214 RETURN;
2215 END IF;
2216 END IF;
2217
2218 -- Check Language Code
2219 IF p_schedule_rec.language_code <> FND_API.g_miss_char AND
2220 p_schedule_rec.language_code IS NOT NULL THEN
2221 l_table_name := 'fnd_languages';
2222 l_pk_name := 'language_code' ;
2223 l_pk_data_type := AMS_Utility_PVT.G_VARCHAR2 ;
2224 l_pk_value := p_schedule_rec.language_code ;
2225 l_additional_where_clause := null ;
2226
2227 IF AMS_Utility_PVT.check_fk_exists(
2228 p_table_name => l_table_name,
2229 p_pk_name => l_pk_name,
2230 p_pk_value => l_pk_value,
2231 p_pk_data_type => l_pk_data_type,
2232 p_additional_where_clause => l_additional_where_clause
2233 ) = FND_API.g_false
2234 THEN
2235 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CAMP_BAD_LANG') ;
2236 x_return_status := FND_API.g_ret_sts_error;
2237 RETURN;
2238 END IF;
2239 END IF;
2240
2241 -- Check Task Id
2242 IF p_schedule_rec.task_id <> FND_API.g_miss_num AND
2243 p_schedule_rec.task_id IS NOT NULL THEN
2244 l_table_name := 'jtf_tasks_b';
2245 l_pk_name := 'task_id' ;
2246 l_pk_data_type := AMS_Utility_PVT.G_NUMBER ;
2247 l_pk_value := p_schedule_rec.task_id ;
2248 l_additional_where_clause := null ;
2249
2250 IF AMS_Utility_PVT.check_fk_exists(
2251 p_table_name => l_table_name,
2252 p_pk_name => l_pk_name,
2253 p_pk_value => l_pk_value,
2254 p_pk_data_type => l_pk_data_type,
2255 p_additional_where_clause => l_additional_where_clause
2256 ) = FND_API.g_false
2257 THEN
2258 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CAMP_BAD_TASK') ;
2259 x_return_status := FND_API.g_ret_sts_error;
2260 RETURN;
2261 END IF;
2262 END IF;
2263
2264
2265 END Check_Schedule_FK_Items;
2266
2267 --===================================================================
2268 -- NAME
2269 -- Check_Schedule_Lookup_Items
2270 --
2271 -- PURPOSE
2272 -- Private api to check lookup items for Campaign schedules.
2273 --
2274 -- NOTES
2275 --
2276 -- HISTORY
2277 -- 22-Jan-2001 PTENDULK Created
2278 --===================================================================
2279 PROCEDURE Check_Schedule_Lookup_Items(
2280 p_schedule_rec IN schedule_rec_type,
2281 x_return_status OUT NOCOPY VARCHAR2
2282 )
2283 IS
2284 BEGIN
2285 x_return_status := FND_API.g_ret_sts_success;
2286
2287 -- Check status code
2288 IF p_schedule_rec.status_code <> FND_API.G_MISS_CHAR
2289 THEN
2290 IF AMS_Utility_PVT.Check_Lookup_Exists
2291 ( p_lookup_table_name => 'AMS_LOOKUPS'
2292 ,p_lookup_type => 'AMS_CAMPAIGN_SCHEDULE_STATUS'
2293 ,p_lookup_code => p_schedule_rec.status_code ) = FND_API.G_FALSE
2294 THEN
2295 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_BAD_STATUS') ;
2296 x_return_status := FND_API.g_ret_sts_error;
2297 RETURN;
2298
2299 END IF;
2300 END IF;
2301
2302 -- Check Activity type code
2303 IF p_schedule_rec.activity_type_code <> FND_API.G_MISS_CHAR
2304 THEN
2305 IF AMS_Utility_PVT.Check_Lookup_Exists
2306 ( p_lookup_table_name => 'AMS_LOOKUPS'
2307 ,p_lookup_type => 'AMS_MEDIA_TYPE'
2308 ,p_lookup_code => p_schedule_rec.activity_type_code ) = FND_API.G_FALSE
2309 THEN
2310 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_BAD_ACTIVITY_TYPE') ;
2311 x_return_status := FND_API.g_ret_sts_error;
2312 RETURN;
2313 END IF;
2314 END IF;
2315
2316 -- Check Objective
2317 IF p_schedule_rec.objective_code <> FND_API.G_MISS_CHAR
2318 THEN
2319 IF AMS_Utility_PVT.Check_Lookup_Exists
2320 ( p_lookup_table_name => 'AMS_LOOKUPS'
2321 ,p_lookup_type => 'AMS_SCHEDULE_OBJECTIVE'
2322 ,p_lookup_code => p_schedule_rec.objective_code ) = FND_API.G_FALSE
2323 THEN
2324 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_BAD_OBJECTIVE') ;
2325 x_return_status := FND_API.g_ret_sts_error;
2326 RETURN;
2327 END IF;
2328 END IF;
2329
2330 -- Check Priority
2331 IF p_schedule_rec.priority <> FND_API.G_MISS_CHAR
2332 THEN
2333 IF AMS_Utility_PVT.Check_Lookup_Exists
2334 ( p_lookup_table_name => 'AMS_LOOKUPS'
2335 ,p_lookup_type => 'AMS_PRIORITY'
2336 ,p_lookup_code => p_schedule_rec.priority ) = FND_API.G_FALSE
2337 THEN
2338 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_BAD_PRIORITY') ;
2339 x_return_status := FND_API.g_ret_sts_error;
2340 RETURN;
2341 END IF;
2342 END IF;
2343
2344 END Check_Schedule_Lookup_Items;
2345
2346 --===================================================================
2347 -- NAME
2348 -- Check_Schedule_Flag_Items
2349 --
2350 -- PURPOSE
2351 -- Private api to check Flag items for Campaign schedules.
2352 --
2353 -- NOTES
2354 --
2355 -- HISTORY
2356 -- 22-Jan-2001 PTENDULK Created
2357 --===================================================================
2358 PROCEDURE Check_Schedule_Flag_Items(
2359 p_schedule_rec IN schedule_rec_type,
2360 x_return_status OUT NOCOPY VARCHAR2
2361 )
2362 IS
2363 BEGIN
2364 x_return_status := FND_API.g_ret_sts_success;
2365
2366 -- Check Use parent code flag
2367 IF p_schedule_rec.use_parent_code_flag <> FND_API.g_miss_char
2368 AND p_schedule_rec.use_parent_code_flag IS NOT NULL
2369 THEN
2370 IF AMS_Utility_PVT.is_Y_or_N(p_schedule_rec.use_parent_code_flag) = FND_API.g_false
2371 THEN
2372 AMS_Utility_PVT.Error_Message('AMS_CAMP_BAD_PARENT_FLAG');
2373 x_return_status := FND_API.g_ret_sts_error;
2374 RETURN;
2375 END IF;
2376 END IF;
2377
2378 -- Check Use parent code flag
2379 IF p_schedule_rec.triggerable_flag <> FND_API.g_miss_char
2380 AND p_schedule_rec.triggerable_flag IS NOT NULL
2381 THEN
2382 IF AMS_Utility_PVT.is_Y_or_N(p_schedule_rec.triggerable_flag) = FND_API.g_false
2383 THEN
2384 AMS_Utility_PVT.Error_Message('AMS_CSCH_NO_TRIG_FLAG');
2385 x_return_status := FND_API.g_ret_sts_error;
2386 RETURN;
2387 END IF;
2388 END IF;
2389
2390 -- Check Active flag
2391 IF p_schedule_rec.active_flag <> FND_API.g_miss_char
2392 AND p_schedule_rec.active_flag IS NOT NULL
2393 THEN
2394 IF AMS_Utility_PVT.is_Y_or_N(p_schedule_rec.active_flag) = FND_API.g_false
2395 THEN
2396 AMS_Utility_PVT.Error_Message('AMS_CSCH_NO_ACTIVE_FLAG');
2397 x_return_status := FND_API.g_ret_sts_error;
2398 RETURN;
2399 END IF;
2400 END IF;
2401
2402 -- Check accounts_closed_flag
2403 IF p_schedule_rec.accounts_closed_flag <> FND_API.g_miss_char
2404 AND p_schedule_rec.accounts_closed_flag IS NOT NULL
2405 THEN
2406 IF AMS_Utility_PVT.is_Y_or_N(p_schedule_rec.accounts_closed_flag) = FND_API.g_false
2407 THEN
2408 AMS_Utility_PVT.Error_Message('AMS_CSCH_ACC_CLOSED_FLAG');
2409 x_return_status := FND_API.g_ret_sts_error;
2410 RETURN;
2411 END IF;
2412 END IF;
2413
2414
2415 END Check_Schedule_Flag_Items ;
2416
2417 --===================================================================
2418 -- NAME
2419 -- Check_schedule_Items
2420 --
2421 -- PURPOSE
2422 -- Private api to items for Campaign schedules.
2423 --
2424 -- NOTES
2425 --
2426 -- HISTORY
2427 -- 22-Jan-2001 PTENDULK Created
2428 --===================================================================
2429 PROCEDURE Check_schedule_Items (
2430 P_schedule_rec IN schedule_rec_type,
2431 p_validation_mode IN VARCHAR2,
2432 x_return_status OUT NOCOPY VARCHAR2
2433 )
2434 IS
2435 BEGIN
2436
2437 -- Check Items Uniqueness API calls
2438
2439 check_schedule_uk_items(
2440 p_schedule_rec => p_schedule_rec,
2441 p_validation_mode => p_validation_mode,
2442 x_return_status => x_return_status);
2443 IF x_return_status <> FND_API.g_ret_sts_success THEN
2444 RETURN;
2445 END IF;
2446
2447 -- Check Items Required/NOT NULL API calls
2448
2449 check_schedule_req_items(
2450 p_schedule_rec => p_schedule_rec,
2451 p_validation_mode => p_validation_mode,
2452 x_return_status => x_return_status);
2453 IF x_return_status <> FND_API.g_ret_sts_success THEN
2454 RETURN;
2455 END IF;
2456 -- Check Items Foreign Keys API calls
2457
2458 check_schedule_FK_items(
2459 p_schedule_rec => p_schedule_rec,
2460 x_return_status => x_return_status);
2461 IF x_return_status <> FND_API.g_ret_sts_success THEN
2462 RETURN;
2463 END IF;
2464
2465 -- Check Items Lookups
2466 check_schedule_Lookup_items(
2467 p_schedule_rec => p_schedule_rec,
2468 x_return_status => x_return_status);
2469 IF x_return_status <> FND_API.g_ret_sts_success THEN
2470 RETURN;
2471 END IF;
2472
2473 -- Check Items Flags
2474 Check_Schedule_Flag_Items(
2475 p_schedule_rec => p_schedule_rec,
2476 x_return_status => x_return_status);
2477 IF x_return_status <> FND_API.g_ret_sts_success THEN
2478 RETURN;
2479 END IF;
2480
2481 END Check_schedule_Items;
2482
2483 --===================================================================
2484 -- NAME
2485 -- Complete_schedule_Rec
2486 --
2487 -- PURPOSE
2488 -- Private api to complete rec for Campaign schedules.
2489 --
2490 -- NOTES
2491 --
2492 -- HISTORY
2493 -- 22-Jan-2001 PTENDULK Created
2494 -- 27-jun-2003 ANCHAUDH added the extra columns in the record for triggers changes.
2495 --===================================================================
2496 PROCEDURE Complete_schedule_Rec (
2497 P_schedule_rec IN schedule_rec_type,
2498 x_complete_rec OUT NOCOPY schedule_rec_type
2499 )
2500 IS
2501 CURSOR c_schedule IS
2502 SELECT *
2503 FROM ams_campaign_schedules_vl
2504 WHERE schedule_id = p_schedule_rec.schedule_id;
2505
2506 l_schedule_rec c_schedule%ROWTYPE;
2507
2508 BEGIN
2509
2510 x_complete_rec := p_schedule_rec;
2511
2512 OPEN c_schedule;
2513 FETCH c_schedule INTO l_schedule_rec;
2514 IF c_schedule%NOTFOUND THEN
2515 CLOSE c_schedule;
2516 AMS_Utility_PVT.Error_Message('AMS_API_RECORD_NOT_FOUND');
2517 RAISE FND_API.g_exc_error;
2518 END IF;
2519 CLOSE c_schedule;
2520
2521 IF p_schedule_rec.campaign_id = FND_API.g_miss_num THEN
2522 x_complete_rec.campaign_id := l_schedule_rec.campaign_id ;
2523 END IF;
2524
2525 IF p_schedule_rec.user_status_id = FND_API.g_miss_num THEN
2526 x_complete_rec.user_status_id := l_schedule_rec.user_status_id;
2527 END IF;
2528
2529 -- status_code will go with user_status_id
2530 x_complete_rec.status_code := AMS_Utility_PVT.get_system_status_code(
2531 x_complete_rec.user_status_id
2532 );
2533
2534 IF p_schedule_rec.status_date = FND_API.g_miss_date
2535 OR p_schedule_rec.status_date IS NULL
2536 THEN
2537 IF p_schedule_rec.user_status_id = l_schedule_rec.user_status_id THEN
2538 -- no status change, set it to be the original value
2539 x_complete_rec.status_date := l_schedule_rec.status_date;
2540 ELSE
2541 -- status changed, set it to be SYSDATE
2542 x_complete_rec.status_date := SYSDATE;
2543 END IF;
2544 END IF;
2545
2546 IF p_schedule_rec.source_code = FND_API.g_miss_char THEN
2547 x_complete_rec.source_code := l_schedule_rec.source_code;
2548 END IF;
2549
2550 IF p_schedule_rec.use_parent_code_flag = FND_API.g_miss_char THEN
2551 x_complete_rec.use_parent_code_flag := l_schedule_rec.use_parent_code_flag;
2552 END IF;
2553
2554 IF p_schedule_rec.start_date_time = FND_API.g_miss_date THEN
2555 x_complete_rec.start_date_time := l_schedule_rec.start_date_time;
2556 END IF;
2557
2558 IF p_schedule_rec.end_date_time = FND_API.g_miss_date THEN
2559 x_complete_rec.end_date_time := l_schedule_rec.end_date_time;
2560 END IF;
2561
2562 IF p_schedule_rec.timezone_id = FND_API.g_miss_num THEN
2563 x_complete_rec.timezone_id := l_schedule_rec.timezone_id;
2564 END IF;
2565
2566 IF p_schedule_rec.activity_type_code = FND_API.g_miss_char THEN
2567 x_complete_rec.activity_type_code := l_schedule_rec.activity_type_code;
2568 END IF;
2569
2570 IF p_schedule_rec.activity_id = FND_API.g_miss_num THEN
2571 x_complete_rec.activity_id := l_schedule_rec.activity_id;
2572 END IF;
2573
2574 IF p_schedule_rec.arc_marketing_medium_from = FND_API.g_miss_char THEN
2575 x_complete_rec.arc_marketing_medium_from := l_schedule_rec.arc_marketing_medium_from;
2576 END IF;
2577
2578 IF p_schedule_rec.marketing_medium_id = FND_API.g_miss_num THEN
2579 x_complete_rec.marketing_medium_id := l_schedule_rec.marketing_medium_id;
2580 END IF;
2581
2582 IF p_schedule_rec.custom_setup_id = FND_API.g_miss_num THEN
2583 x_complete_rec.custom_setup_id := l_schedule_rec.custom_setup_id;
2584 END IF;
2585
2586 IF p_schedule_rec.triggerable_flag = FND_API.g_miss_char THEN
2587 x_complete_rec.triggerable_flag := l_schedule_rec.triggerable_flag;
2588 END IF;
2589
2590 IF p_schedule_rec.trigger_id = FND_API.g_miss_num THEN
2591 x_complete_rec.trigger_id := l_schedule_rec.trigger_id;
2592 END IF;
2593
2594 IF p_schedule_rec.notify_user_id = FND_API.g_miss_num THEN
2595 x_complete_rec.notify_user_id := l_schedule_rec.notify_user_id;
2596 END IF;
2597
2598 IF p_schedule_rec.approver_user_id = FND_API.g_miss_num THEN
2599 x_complete_rec.approver_user_id := l_schedule_rec.approver_user_id;
2600 END IF;
2601
2602 IF p_schedule_rec.owner_user_id = FND_API.g_miss_num THEN
2603 x_complete_rec.owner_user_id := l_schedule_rec.owner_user_id;
2604 END IF;
2605
2606 IF p_schedule_rec.active_flag = FND_API.g_miss_char THEN
2607 x_complete_rec.active_flag := l_schedule_rec.active_flag;
2608 END IF;
2609
2610 IF p_schedule_rec.cover_letter_id = FND_API.g_miss_num THEN
2611 x_complete_rec.cover_letter_id := l_schedule_rec.cover_letter_id;
2612 END IF;
2613
2614 IF p_schedule_rec.reply_to_mail = FND_API.g_miss_char THEN
2615 x_complete_rec.reply_to_mail := l_schedule_rec.reply_to_mail;
2616 END IF;
2617
2618 IF p_schedule_rec.mail_sender_name = FND_API.g_miss_char THEN
2619 x_complete_rec.mail_sender_name := l_schedule_rec.mail_sender_name;
2620 END IF;
2621
2622 IF p_schedule_rec.mail_subject = FND_API.g_miss_char THEN
2623 x_complete_rec.mail_subject := l_schedule_rec.mail_subject;
2624 END IF;
2625
2626 IF p_schedule_rec.from_fax_no = FND_API.g_miss_char THEN
2627 x_complete_rec.from_fax_no := l_schedule_rec.from_fax_no;
2628 END IF;
2629
2630 IF p_schedule_rec.accounts_closed_flag = FND_API.g_miss_char THEN
2631 x_complete_rec.accounts_closed_flag := l_schedule_rec.accounts_closed_flag;
2632 END IF;
2633
2634 IF p_schedule_rec.org_id = FND_API.g_miss_num THEN
2635 x_complete_rec.org_id := l_schedule_rec.org_id;
2636 END IF;
2637
2638 IF p_schedule_rec.objective_code = FND_API.g_miss_char THEN
2639 x_complete_rec.objective_code := l_schedule_rec.objective_code;
2640 END IF;
2641
2642 IF p_schedule_rec.country_id = FND_API.g_miss_num THEN
2643 x_complete_rec.country_id := l_schedule_rec.country_id;
2644 END IF;
2645
2646 IF p_schedule_rec.campaign_calendar = FND_API.g_miss_char THEN
2647 x_complete_rec.campaign_calendar:= l_schedule_rec.campaign_calendar ;
2648 END IF;
2649
2650 IF p_schedule_rec.start_period_name = FND_API.g_miss_char THEN
2651 x_complete_rec.start_period_name := l_schedule_rec.start_period_name;
2652 END IF;
2653
2654 IF p_schedule_rec.end_period_name = FND_API.g_miss_char THEN
2655 x_complete_rec.end_period_name := l_schedule_rec.end_period_name;
2656 END IF;
2657
2658 IF p_schedule_rec.priority = FND_API.g_miss_char THEN
2659 x_complete_rec.priority := l_schedule_rec.priority;
2660 END IF;
2661
2662 IF p_schedule_rec.workflow_item_key = FND_API.g_miss_char THEN
2663 x_complete_rec.workflow_item_key := l_schedule_rec.workflow_item_key;
2664 END IF;
2665
2666 IF p_schedule_rec.transaction_currency_code = FND_API.g_miss_char THEN
2667 x_complete_rec.transaction_currency_code := l_schedule_rec.transaction_currency_code ;
2668 END IF;
2669
2670 IF p_schedule_rec.functional_currency_code = FND_API.g_miss_char THEN
2671 x_complete_rec.functional_currency_code := l_schedule_rec.functional_currency_code ;
2672 END IF;
2673
2674 IF p_schedule_rec.budget_amount_tc = FND_API.g_miss_num THEN
2675 x_complete_rec.budget_amount_tc := l_schedule_rec.budget_amount_tc;
2676 END IF;
2677
2678 IF p_schedule_rec.budget_amount_fc = FND_API.g_miss_num THEN
2679 x_complete_rec.budget_amount_fc := l_schedule_rec.budget_amount_fc;
2680 END IF;
2681
2682 IF p_schedule_rec.language_code = FND_API.g_miss_char THEN
2683 x_complete_rec.language_code := l_schedule_rec.language_code ;
2684 END IF;
2685
2686 IF p_schedule_rec.task_id = FND_API.g_miss_num THEN
2687 x_complete_rec.task_id := l_schedule_rec.task_id;
2688 END IF;
2689
2690 IF p_schedule_rec.related_event_from = FND_API.g_miss_char THEN
2691 x_complete_rec.related_event_from := l_schedule_rec.related_event_from ;
2692 END IF;
2693
2694 IF p_schedule_rec.related_event_id = FND_API.g_miss_num THEN
2695 x_complete_rec.related_event_id := l_schedule_rec.related_event_id;
2696 END IF;
2697
2698
2699 IF p_schedule_rec.attribute_category = FND_API.g_miss_char THEN
2700 x_complete_rec.attribute_category := l_schedule_rec.attribute_category;
2701 END IF;
2702
2703 IF p_schedule_rec.attribute1 = FND_API.g_miss_char THEN
2704 x_complete_rec.attribute1 := l_schedule_rec.attribute1;
2705 END IF;
2706
2707 IF p_schedule_rec.attribute2 = FND_API.g_miss_char THEN
2708 x_complete_rec.attribute2 := l_schedule_rec.attribute2;
2709 END IF;
2710
2711 IF p_schedule_rec.attribute3 = FND_API.g_miss_char THEN
2712 x_complete_rec.attribute3 := l_schedule_rec.attribute3;
2713 END IF;
2714
2715 IF p_schedule_rec.attribute4 = FND_API.g_miss_char THEN
2716 x_complete_rec.attribute4 := l_schedule_rec.attribute4;
2717 END IF;
2718
2719 IF p_schedule_rec.attribute5 = FND_API.g_miss_char THEN
2720 x_complete_rec.attribute5 := l_schedule_rec.attribute5;
2721 END IF;
2722
2723 IF p_schedule_rec.attribute6 = FND_API.g_miss_char THEN
2724 x_complete_rec.attribute6 := l_schedule_rec.attribute6;
2725 END IF;
2726
2727 IF p_schedule_rec.attribute7 = FND_API.g_miss_char THEN
2728 x_complete_rec.attribute7 := l_schedule_rec.attribute7;
2729 END IF;
2730
2731 IF p_schedule_rec.attribute8 = FND_API.g_miss_char THEN
2732 x_complete_rec.attribute8 := l_schedule_rec.attribute8;
2733 END IF;
2734
2735 IF p_schedule_rec.attribute9 = FND_API.g_miss_char THEN
2736 x_complete_rec.attribute9 := l_schedule_rec.attribute9;
2737 END IF;
2738
2739 IF p_schedule_rec.attribute10 = FND_API.g_miss_char THEN
2740 x_complete_rec.attribute10 := l_schedule_rec.attribute10;
2741 END IF;
2742
2743 IF p_schedule_rec.attribute11 = FND_API.g_miss_char THEN
2744 x_complete_rec.attribute11 := l_schedule_rec.attribute11;
2745 END IF;
2746
2747 IF p_schedule_rec.attribute12 = FND_API.g_miss_char THEN
2748 x_complete_rec.attribute12 := l_schedule_rec.attribute12;
2749 END IF;
2750
2751 IF p_schedule_rec.attribute13 = FND_API.g_miss_char THEN
2752 x_complete_rec.attribute13 := l_schedule_rec.attribute13;
2753 END IF;
2754
2755 IF p_schedule_rec.attribute14 = FND_API.g_miss_char THEN
2756 x_complete_rec.attribute14 := l_schedule_rec.attribute14;
2757 END IF;
2758
2759 IF p_schedule_rec.attribute15 = FND_API.g_miss_char THEN
2760 x_complete_rec.attribute15 := l_schedule_rec.attribute15;
2761 END IF;
2762
2763 IF p_schedule_rec.attribute_category = FND_API.g_miss_char THEN
2764 x_complete_rec.attribute_category := l_schedule_rec.attribute_category;
2765 END IF;
2766
2767 IF p_schedule_rec.activity_attribute1 = FND_API.g_miss_char THEN
2768 x_complete_rec.activity_attribute1 := l_schedule_rec.activity_attribute1;
2769 END IF;
2770
2771 IF p_schedule_rec.activity_attribute2 = FND_API.g_miss_char THEN
2772 x_complete_rec.activity_attribute2 := l_schedule_rec.activity_attribute2;
2773 END IF;
2774
2775 IF p_schedule_rec.activity_attribute3 = FND_API.g_miss_char THEN
2776 x_complete_rec.activity_attribute3 := l_schedule_rec.activity_attribute3;
2777 END IF;
2778
2779 IF p_schedule_rec.activity_attribute4 = FND_API.g_miss_char THEN
2780 x_complete_rec.activity_attribute4 := l_schedule_rec.activity_attribute4;
2781 END IF;
2782
2783 IF p_schedule_rec.activity_attribute5 = FND_API.g_miss_char THEN
2784 x_complete_rec.activity_attribute5 := l_schedule_rec.activity_attribute5;
2785 END IF;
2786
2787 IF p_schedule_rec.activity_attribute6 = FND_API.g_miss_char THEN
2788 x_complete_rec.activity_attribute6 := l_schedule_rec.activity_attribute6;
2789 END IF;
2790
2791 IF p_schedule_rec.activity_attribute7 = FND_API.g_miss_char THEN
2792 x_complete_rec.activity_attribute7 := l_schedule_rec.activity_attribute7;
2793 END IF;
2794
2795 IF p_schedule_rec.activity_attribute8 = FND_API.g_miss_char THEN
2796 x_complete_rec.activity_attribute8 := l_schedule_rec.activity_attribute8;
2797 END IF;
2798
2799 IF p_schedule_rec.activity_attribute9 = FND_API.g_miss_char THEN
2800 x_complete_rec.activity_attribute9 := l_schedule_rec.activity_attribute9;
2801 END IF;
2802
2803 IF p_schedule_rec.activity_attribute10 = FND_API.g_miss_char THEN
2804 x_complete_rec.activity_attribute10 := l_schedule_rec.activity_attribute10;
2805 END IF;
2806
2807 IF p_schedule_rec.activity_attribute11 = FND_API.g_miss_char THEN
2808 x_complete_rec.activity_attribute11 := l_schedule_rec.activity_attribute11;
2809 END IF;
2810
2811 IF p_schedule_rec.activity_attribute12 = FND_API.g_miss_char THEN
2812 x_complete_rec.activity_attribute12 := l_schedule_rec.activity_attribute12;
2813 END IF;
2814
2815 IF p_schedule_rec.activity_attribute13 = FND_API.g_miss_char THEN
2816 x_complete_rec.activity_attribute13 := l_schedule_rec.activity_attribute13;
2817 END IF;
2818
2819 IF p_schedule_rec.activity_attribute14 = FND_API.g_miss_char THEN
2820 x_complete_rec.activity_attribute14 := l_schedule_rec.activity_attribute14;
2821 END IF;
2822
2823 IF p_schedule_rec.activity_attribute15 = FND_API.g_miss_char THEN
2824 x_complete_rec.activity_attribute15 := l_schedule_rec.activity_attribute15;
2825 END IF;
2826
2827 IF p_schedule_rec.schedule_name = FND_API.g_miss_char THEN
2828 x_complete_rec.schedule_name := l_schedule_rec.schedule_name;
2829 END IF;
2830
2831 IF p_schedule_rec.description = FND_API.g_miss_char THEN
2832 x_complete_rec.description := l_schedule_rec.description;
2833 END IF;
2834
2835 -- removed by soagrawa on 24-sep-2001
2836 -- IF p_schedule_rec.security_group_id = FND_API.g_miss_num THEN
2837 -- x_complete_rec.security_group_id := l_schedule_rec.security_group_id;
2838 -- END IF;
2839
2840 --following are added by anchaudh on 27-jun-2003
2841 IF p_schedule_rec.trig_repeat_flag = FND_API.g_miss_char THEN
2842 x_complete_rec.trig_repeat_flag := l_schedule_rec.trig_repeat_flag;
2843 END IF;
2844
2845 IF p_schedule_rec.tgrp_exclude_prev_flag = FND_API.g_miss_char THEN
2846 x_complete_rec.tgrp_exclude_prev_flag := l_schedule_rec.tgrp_exclude_prev_flag;
2847 END IF;
2848
2849 IF p_schedule_rec.orig_csch_id = FND_API.g_miss_num THEN
2850 x_complete_rec.orig_csch_id := l_schedule_rec.orig_csch_id;
2851 END IF;
2852
2853 IF p_schedule_rec.cover_letter_version = FND_API.g_miss_num THEN
2854 x_complete_rec.cover_letter_version := l_schedule_rec.cover_letter_version;
2855 END IF;
2856
2857 IF p_schedule_rec.usage = FND_API.g_miss_char THEN
2858 x_complete_rec.usage := l_schedule_rec.usage;
2859 END IF;
2860
2861 IF p_schedule_rec.purpose = FND_API.g_miss_char THEN
2862 x_complete_rec.purpose := l_schedule_rec.purpose;
2863 END IF;
2864
2865 IF p_schedule_rec.last_activation_date = FND_API.g_miss_date THEN
2866 x_complete_rec.last_activation_date := l_schedule_rec.last_activation_date;
2867 END IF;
2868
2869 IF p_schedule_rec.sales_methodology_id = FND_API.g_miss_num THEN
2870 x_complete_rec.sales_methodology_id := l_schedule_rec.sales_methodology_id;
2871 END IF;
2872
2873 IF p_schedule_rec.printer_address = FND_API.g_miss_char THEN
2874 x_complete_rec.printer_address := l_schedule_rec.printer_address;
2875 END IF;
2876
2877 IF p_schedule_rec.notify_on_activation_flag = FND_API.g_miss_char THEN
2878 x_complete_rec.notify_on_activation_flag := l_schedule_rec.notify_on_activation_flag;
2879 END IF;
2880
2881 IF p_schedule_rec.sender_display_name = FND_API.g_miss_char THEN
2882 x_complete_rec.sender_display_name := l_schedule_rec.sender_display_name;
2883 END IF;
2884
2885 IF p_schedule_rec.delivery_mode = FND_API.g_miss_char THEN
2886 x_complete_rec.delivery_mode := l_schedule_rec.delivery_mode;
2887 END IF;
2888
2889 END Complete_schedule_Rec;
2890
2891 --===================================================================
2892 -- NAME
2893 -- Validate_camp_schedule
2894 --
2895 -- PURPOSE
2896 -- Validate schedules
2897 --
2898 -- NOTES
2899 --
2900 -- HISTORY
2901 -- 22-Jan-2001 PTENDULK Created
2902 --===================================================================
2903 PROCEDURE Validate_camp_schedule(
2904 p_api_version_number IN NUMBER,
2905 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2906 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2907 p_schedule_rec IN schedule_rec_type,
2908 p_validation_mode IN VARCHAR2,
2909 x_return_status OUT NOCOPY VARCHAR2,
2910 x_msg_count OUT NOCOPY NUMBER,
2911 x_msg_data OUT NOCOPY VARCHAR2
2912 )
2913 IS
2914 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Camp_Schedule';
2915 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
2916 l_object_version_number NUMBER;
2917 l_schedule_rec AMS_Camp_Schedule_PVT.schedule_rec_type := p_schedule_rec ;
2918
2919 BEGIN
2920 -- Standard Start of API savepoint
2921 SAVEPOINT VALIDATE_Camp_Schedule_;
2922
2923 -- Standard call to check for call compatibility.
2924 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2925 p_api_version_number,
2926 l_api_name,
2927 G_PKG_NAME)
2928 THEN
2929 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2930 END IF;
2931
2932 -- Initialize message list if p_init_msg_list is set to TRUE.
2933 IF FND_API.to_Boolean( p_init_msg_list )
2934 THEN
2935 FND_MSG_PUB.initialize;
2936 END IF;
2937
2938 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
2939 Check_schedule_Items(
2940 p_schedule_rec => p_schedule_rec,
2941 p_validation_mode => JTF_PLSQL_API.g_update,
2942 x_return_status => x_return_status
2943 );
2944
2945 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2946 RAISE FND_API.G_EXC_ERROR;
2947 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2948 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2949 END IF;
2950 END IF;
2951
2952 IF p_validation_mode = JTF_PLSQL_API.g_update THEN
2953 IF (AMS_DEBUG_HIGH_ON) THEN
2954
2955 AMS_Utility_PVT.debug_message('After Item validation');
2956 END IF;
2957 Complete_schedule_Rec(
2958 p_schedule_rec => p_schedule_rec,
2959 x_complete_rec => l_schedule_rec
2960 );
2961 END IF ;
2962
2963 IF (AMS_DEBUG_HIGH_ON) THEN
2964
2965
2966
2967 AMS_Utility_PVT.debug_message('Rec Level Validation');
2968
2969 END IF;
2970 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
2971 Validate_schedule_Rec(
2972 p_api_version_number => 1.0,
2973 p_init_msg_list => FND_API.G_FALSE,
2974 x_return_status => x_return_status,
2975 x_msg_count => x_msg_count,
2976 x_msg_data => x_msg_data,
2977 p_schedule_rec => l_schedule_rec);
2978
2979 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2980 RAISE FND_API.G_EXC_ERROR;
2981 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2982 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2983 END IF;
2984 END IF;
2985
2986 IF (AMS_DEBUG_HIGH_ON) THEN
2987
2988
2989
2990 AMS_Utility_PVT.debug_message('Inter Entity Level Validation');
2991
2992 END IF;
2993 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
2994 Check_Schedule_Inter_Entity(
2995 p_schedule_rec => p_schedule_rec,
2996 p_complete_rec => l_schedule_rec,
2997 p_validation_mode => p_validation_mode,
2998 x_return_status => x_return_status
2999 ) ;
3000 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3001 RAISE FND_API.G_EXC_ERROR;
3002 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3003 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3004 END IF;
3005 END IF ;
3006
3007 -- Debug Message
3008 IF (AMS_DEBUG_HIGH_ON) THEN
3009
3010 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
3011 END IF;
3012
3013 -- Initialize API return status to SUCCESS
3014 x_return_status := FND_API.G_RET_STS_SUCCESS;
3015
3016 -- Debug Message
3017 IF (AMS_DEBUG_HIGH_ON) THEN
3018
3019 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
3020 END IF;
3021
3022 -- Standard call to get message count and if count is 1, get message info.
3023 FND_MSG_PUB.Count_And_Get
3024 (p_count => x_msg_count,
3025 p_data => x_msg_data
3026 );
3027 EXCEPTION
3028
3029 WHEN AMS_Utility_PVT.resource_locked THEN
3030 x_return_status := FND_API.g_ret_sts_error;
3031 AMS_Utility_PVT.Error_Message('AMS_API_RESOURCE_LOCKED');
3032
3033 WHEN FND_API.G_EXC_ERROR THEN
3034 ROLLBACK TO VALIDATE_Camp_Schedule_;
3035 x_return_status := FND_API.G_RET_STS_ERROR;
3036 -- Standard call to get message count and if count=1, get the message
3037 FND_MSG_PUB.Count_And_Get (
3038 p_encoded => FND_API.G_FALSE,
3039 p_count => x_msg_count,
3040 p_data => x_msg_data
3041 );
3042
3043 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3044 ROLLBACK TO VALIDATE_Camp_Schedule_;
3045 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3046 -- Standard call to get message count and if count=1, get the message
3047 FND_MSG_PUB.Count_And_Get (
3048 p_encoded => FND_API.G_FALSE,
3049 p_count => x_msg_count,
3050 p_data => x_msg_data
3051 );
3052
3053 WHEN OTHERS THEN
3054 ROLLBACK TO VALIDATE_Camp_Schedule_;
3055 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3056 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3057 THEN
3058 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3059 END IF;
3060 -- Standard call to get message count and if count=1, get the message
3061 FND_MSG_PUB.Count_And_Get (
3062 p_encoded => FND_API.G_FALSE,
3063 p_count => x_msg_count,
3064 p_data => x_msg_data
3065 );
3066 End Validate_Camp_Schedule;
3067
3068 --===================================================================
3069 -- NAME
3070 -- Validate_schedule_rec
3071 --
3072 -- PURPOSE
3073 -- Record level validations for schedules.
3074 --
3075 -- NOTES
3076 --
3077 -- HISTORY
3078 -- 22-Jan-2001 PTENDULK Created
3079 -- 08-Jul-2001 ptendulk Added validation for start_Date and
3080 -- end date Refer bug #1856924
3081 -- 03-dec-2001 soagrawa Added check for event type schedules:
3082 -- end date should be mandatory
3083 -- bug# 2131521
3084 -- 23-mar-2004 soagrawa Modified validation for triggerable schedules
3085 -- keeping repeating schedules in consideration
3086 --===================================================================
3087 PROCEDURE Validate_schedule_rec(
3088 p_api_version_number IN NUMBER,
3089 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3090 x_return_status OUT NOCOPY VARCHAR2,
3091 x_msg_count OUT NOCOPY NUMBER,
3092 x_msg_data OUT NOCOPY VARCHAR2,
3093 p_schedule_rec IN schedule_rec_type
3094 )
3095 IS
3096 BEGIN
3097 -- Initialize message list if p_init_msg_list is set to TRUE.
3098 IF FND_API.to_Boolean( p_init_msg_list )
3099 THEN
3100 FND_MSG_PUB.initialize;
3101 END IF;
3102
3103 -- Initialize API return status to SUCCESS
3104 x_return_status := FND_API.G_RET_STS_SUCCESS;
3105
3106 -- Debug Message
3107 IF (AMS_DEBUG_HIGH_ON) THEN
3108
3109 AMS_UTILITY_PVT.debug_message('Private API: Validate_Schedule_Rec');
3110 END IF;
3111
3112 -- budget amount must come with budget currency
3113 IF p_schedule_rec.transaction_currency_code IS NULL
3114 AND p_schedule_rec.budget_amount_tc IS NOT NULL
3115 THEN
3116 AMS_Utility_PVT.Error_Message('AMS_CAMP_BUDGET_NO_CURRENCY');
3117 x_return_status := FND_API.g_ret_sts_error;
3118 END IF;
3119
3120 IF p_schedule_rec.end_date_time IS NOT NULL THEN
3121 IF p_schedule_rec.start_date_time > p_schedule_rec.end_date_time THEN
3122 AMS_Utility_PVT.Error_Message('AMS_CAMP_START_AFTER_END');
3123 x_return_status := FND_API.g_ret_sts_error;
3124 END IF ;
3125 END IF ;
3126
3127 -- added by soagrawa on 03-dec-2001 : end date mandatory for schedules of type events
3128 -- bug# 2131521
3129 IF p_schedule_rec.activity_type_code = 'EVENTS' THEN
3130 IF p_schedule_rec.end_date_time IS NULL THEN
3131 AMS_Utility_PVT.Error_Message('AMS_CSCH_END_DATE_MAND');
3132 x_return_status := FND_API.G_RET_STS_ERROR;
3133 RETURN;
3134 END IF;
3135 END IF ;
3136 --end soagrawa 03-dec-2001
3137
3138 --added by anchaudh on 27-jun-2003 : triggers related validations
3139 -- following validations modified by soagrawa on 22-nov-2003 for 11.5.10 repeating schedules' addition
3140
3141 IF (p_schedule_rec.triggerable_flag = 'Y' AND p_schedule_rec.trigger_id IS NULL) THEN
3142 AMS_Utility_PVT.Error_Message('AMS_SELECT_TRIGGER');
3143 x_return_status := FND_API.G_RET_STS_ERROR;
3144 RETURN;
3145 END IF;
3146
3147 /* IF (p_schedule_rec.triggerable_flag = 'N' AND p_schedule_rec.tgrp_exclude_prev_flag = 'Y') THEN
3148 AMS_Utility_PVT.Error_Message('AMS_DONOT_TRIG_TGRP');
3149 x_return_status := FND_API.G_RET_STS_ERROR;
3150 RETURN;
3151 END IF;
3152 */
3153
3154 -- soagrawa added clause p_schedule_rec.triggerable_flag = 'Y'
3155 -- on 23-mar-2004
3156 IF (p_schedule_rec.trig_repeat_flag = 'N' AND p_schedule_rec.triggerable_flag = 'Y' AND p_schedule_rec.tgrp_exclude_prev_flag = 'Y') THEN
3157 AMS_Utility_PVT.Error_Message('AMS_TRIG_EXCLUDE_TGRP');
3158 x_return_status := FND_API.G_RET_STS_ERROR;
3159 RETURN;
3160 END IF;
3161
3162 /*
3163 IF (p_schedule_rec.triggerable_flag = 'N' AND p_schedule_rec.trig_repeat_flag = 'Y') THEN
3164 AMS_Utility_PVT.Error_Message('AMS_DONOT_TRIG_REPEAT');
3165 x_return_status := FND_API.G_RET_STS_ERROR;
3166 RETURN;
3167 END IF;
3168 */
3169
3170
3171 --end : triggers related validations
3172
3173 -- Check the Schedule Uniqueness
3174
3175 -- Standard call to get message count and if count is 1, get message info.
3176 -- FND_MSG_PUB.Count_And_Get
3177 -- (p_count => x_msg_count,
3178 -- p_data => x_msg_data
3179 -- );
3180 END Validate_schedule_Rec;
3181
3182 --===================================================================
3183 -- NAME
3184 -- Check_Schedule_Inter_Entity
3185 --
3186 -- PURPOSE
3187 -- Inter Entitiy validations for schedules.
3188 --
3189 -- NOTES
3190 --
3191 -- HISTORY
3192 -- 22-Jan-2001 PTENDULK Created
3193 -- 24-May-2001 ptendulk Added check to validate that event schedule is
3194 -- can not be created for event promotions.
3195 -- 01-Aug-2001 ptendulk 1.Added two more cursors for duplicate check
3196 -- 2.Replaced ams_utility_pvt.check_uniqueness check
3197 -- with manual check. Refer bug #1913448
3198 --===================================================================
3199 PROCEDURE Check_Schedule_Inter_Entity( p_schedule_rec IN schedule_rec_type,
3200 p_complete_rec IN schedule_rec_type,
3201 p_validation_mode IN VARCHAR2,
3202 x_return_status OUT NOCOPY VARCHAR2
3203 )
3204 IS
3205 l_return_status VARCHAR2(1);
3206
3207 l_table_name VARCHAR2(30);
3208 l_where_clause VARCHAR2(4000);
3209 l_valid_flag VARCHAR2(1);
3210 l_pk_name VARCHAR2(100);
3211 l_pk_value NUMBER;
3212 l_pk_data_type VARCHAR2(30);
3213
3214 CURSOR c_camp_status IS
3215 SELECT status_code,rollup_type
3216 FROM ams_campaigns_all_b
3217 WHERE campaign_id = p_schedule_rec.campaign_id ;
3218 l_status_code VARCHAR2(30);
3219 l_rollup_type VARCHAR2(30);
3220
3221 CURSOR c_sch_name IS
3222 SELECT 1 from dual
3223 WHERE EXISTS (SELECT *
3224 FROM ams_campaign_schedules_vl
3225 WHERE campaign_id = p_complete_rec.campaign_id
3226 AND UPPER(schedule_name) = UPPER(p_complete_rec.schedule_name)) ;
3227
3228 CURSOR c_sch_name_updt IS
3229 SELECT 1 from dual
3230 WHERE EXISTS (SELECT *
3231 FROM ams_campaign_schedules_vl
3232 WHERE campaign_id = p_complete_rec.campaign_id
3233 AND UPPER(schedule_name) = UPPER(p_complete_rec.schedule_name)
3234 AND schedule_id <> p_complete_rec.schedule_id ) ;
3235 l_dummy NUMBER ;
3236
3237 BEGIN
3238
3239 x_return_status := FND_API.g_ret_sts_success;
3240
3241 ------------------- check media type ----------------------
3242 -- IF p_camp_rec.parent_campaign_id <> FND_API.g_miss_num
3243 -- OR p_camp_rec.rollup_type <> FND_API.g_miss_char
3244 -- OR p_camp_rec.media_type_code <> FND_API.g_miss_char
3245 -- OR p_camp_rec.media_id <> FND_API.g_miss_num
3246 -- OR p_camp_rec.channel_id <> FND_API.g_miss_num
3247 -- OR p_camp_rec.event_type <> FND_API.g_miss_char
3248 -- OR p_camp_rec.arc_channel_from <> FND_API.g_miss_char
3249 -- THEN
3250 -- AMS_CampaignRules_PVT.check_camp_media_type(
3251 -- p_camp_rec.campaign_id,
3252 -- p_complete_rec.parent_campaign_id,
3253 -- p_complete_rec.rollup_type,
3254 -- p_complete_rec.media_type_code,
3255 -- p_complete_rec.media_id,
3256 -- p_complete_rec.channel_id,
3257 -- p_complete_rec.event_type,
3258 -- p_complete_rec.arc_channel_from,
3259 -- l_return_status
3260 -- );
3261 -- l_return_status := FND_API.g_ret_sts_success ;
3262 -- IF l_return_status <> FND_API.g_ret_sts_success THEN
3263 -- x_return_status := l_return_status;
3264 -- END IF;
3265 -- END IF;
3266
3267
3268 -- Check the campaign
3269 -- No schedules can be added to campaigns in Archieved ,cancelled ,completed campaigns or on hold campaigns
3270 -- Campaign - Schedule rule 1
3271 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
3272 OPEN c_camp_status ;
3273 FETCH c_camp_status INTO l_status_code,l_rollup_type ;
3274 CLOSE c_camp_status ;
3275
3276 IF l_status_code = 'ARCHIVED'
3277 OR l_status_code = 'CANCELLED'
3278 OR l_status_code = 'COMPLETED'
3279 OR l_status_code = 'ON_HOLD'
3280 THEN
3281 AMS_Utility_PVT.Error_Message('AMS_CSCH_INVALID_CAMP_STAT');
3282 x_return_status := FND_API.G_RET_STS_ERROR;
3283 RETURN;
3284 END IF ;
3285
3286 -- Following validation is added by ptendulk on 24-May-2001 to
3287 -- ensure that event type schedules can not be created for event type campaigns.
3288 IF l_rollup_type = 'EVCAM' AND p_complete_rec.activity_type_code = 'EVENTS' THEN
3289 AMS_Utility_PVT.Error_Message('AMS_CSCH_INVALID_EVENT_SCH');
3290 x_return_status := FND_API.G_RET_STS_ERROR;
3291 RETURN;
3292 END IF ;
3293
3294 END IF ;
3295
3296
3297
3298
3299
3300 /* - commented by Nari. Will have to add validation for related events
3301 -- Check Related Event from
3302 IF (p_schedule_rec.related_event_id IS NOT NULL AND p_schedule_rec.related_event_id <> FND_API.g_miss_num)
3303 OR (p_schedule_rec.related_event_from IS NOT NULL AND p_schedule_rec.related_event_from <> FND_API.g_miss_char)
3304 THEN
3305 -- Get table_name and pk_name for the ARC qualifier.
3306 AMS_Utility_PVT.Get_Qual_Table_Name_And_PK (
3307 p_sys_qual => p_complete_rec.related_event_from,
3308 x_return_status => x_return_status,
3309 x_table_name => l_table_name,
3310 x_pk_name => l_pk_name
3311 );
3312
3313 l_pk_value := p_complete_rec.related_event_id ;
3314 l_pk_data_type := AMS_Utility_PVT.G_NUMBER;
3315 l_where_clause := NULL;
3316
3317 IF AMS_Utility_PVT.Check_FK_Exists (
3318 p_table_name => l_table_name
3319 ,p_pk_name => l_pk_name
3320 ,p_pk_value => l_pk_value
3321 ,p_pk_data_type => l_pk_data_type
3322 ,p_additional_where_clause => l_where_clause
3323 ) = FND_API.G_FALSE
3324 THEN
3325 AMS_UTILITY_PVT.Error_Message('AMS_CAMP_INVALID_EVENT');
3326 x_return_status := FND_API.G_RET_STS_ERROR;
3327 RETURN;
3328 END IF;
3329 END IF ;*/
3330
3331
3332 -- checking calendar (i.e. periods) : commented out by soagrawa on 03-dec-2001
3333 --refer to bug# 2132456
3334 ------------------- check calendar ----------------------
3335 /*
3336 IF (p_schedule_rec.campaign_calendar <> FND_API.g_miss_char AND
3337 p_schedule_rec.campaign_calendar IS NOT NULL ) OR
3338 (p_schedule_rec.start_period_name <> FND_API.g_miss_char AND
3339 p_schedule_rec.start_period_name IS NOT NULL ) OR
3340 (p_schedule_rec.end_period_name <> FND_API.g_miss_char AND
3341 p_schedule_rec.end_period_name IS NOT NULL) OR
3342 (p_schedule_rec.start_date_time <> FND_API.g_miss_date AND
3343 p_schedule_rec.start_date_time IS NOT NULL) OR
3344 (p_schedule_rec.end_date_time <> FND_API.g_miss_date AND
3345 p_schedule_rec.end_date_time IS NOT NULL )
3346 THEN
3347 AMS_CampaignRules_PVT.check_camp_calendar(
3348 p_complete_rec.campaign_calendar,
3349 p_complete_rec.start_period_name,
3350 p_complete_rec.end_period_name,
3351 p_complete_rec.start_date_time,
3352 p_complete_rec.end_date_time,
3353 l_return_status
3354 );
3355 IF l_return_status <> FND_API.g_ret_sts_success THEN
3356 x_return_status := l_return_status;
3357 RETURN ;
3358 END IF;
3359 END IF;
3360 */
3361 -- end soagrawa 03-dec-2001
3362
3363 ------------------- check dates ------------------------------
3364 IF (p_schedule_rec.start_date_time <> FND_API.g_miss_date AND
3365 p_schedule_rec.start_date_time IS NOT NULL) OR
3366 (p_schedule_rec.end_date_time <> FND_API.g_miss_date AND
3367 p_schedule_rec.end_date_time IS NOT NULL)
3368 THEN
3369 AMS_ScheduleRules_PVT.Check_Sched_Dates_Vs_Camp(
3370 p_complete_rec.campaign_id,
3371 p_complete_rec.start_date_time,
3372 p_complete_rec.end_date_time,
3373 l_return_status
3374 );
3375 IF l_return_status <> FND_API.g_ret_sts_success THEN
3376 x_return_status := l_return_status;
3377 RETURN ;
3378 END IF;
3379 END IF;
3380
3381 -- Check Schedule_Name
3382 IF p_schedule_rec.campaign_id <> FND_API.g_miss_num OR
3383 p_schedule_rec.schedule_name <> FND_API.g_miss_char
3384 THEN
3385 --l_table_name := 'ams_campaign_schedules_vl' ;
3386 --l_where_clause := 'campaign_id = '||p_complete_rec.campaign_id ;
3387 --l_where_clause := l_where_clause || ' AND UPPER(schedule_name) = '''||UPPER(p_complete_rec.schedule_name)||'''' ;
3388
3389 --IF p_validation_mode = JTF_PLSQL_API.g_update THEN
3390 -- l_where_clause := l_where_clause || ' AND schedule_id <> '||p_complete_rec.schedule_id ;
3391 --END IF ;
3392
3393 --l_valid_flag := AMS_Utility_PVT.check_uniqueness( p_table_name => l_table_name,
3394 -- p_where_clause => l_where_clause) ;
3395 --IF l_valid_flag = FND_API.g_false THEN
3396 -- AMS_Utility_PVT.Error_Message('AMS_CSCH_DUPLICATE_ID');
3397 -- x_return_status := FND_API.g_ret_sts_error;
3398 -- RETURN;
3399 --END IF;
3400 IF p_validation_mode = JTF_PLSQL_API.g_update THEN
3401 OPEN c_sch_name_updt;
3402 FETCH c_sch_name_updt INTO l_dummy ;
3403 CLOSE c_sch_name_updt ;
3404 ELSE
3405 OPEN c_sch_name;
3406 FETCH c_sch_name INTO l_dummy ;
3407 CLOSE c_sch_name ;
3408 END IF ;
3409
3410 IF l_dummy IS NOT NULL THEN
3411 -- Duplicate Schedule
3412 AMS_Utility_PVT.Error_Message('AMS_CSCH_DUPLICATE_ID');
3413 x_return_status := FND_API.g_ret_sts_error;
3414 RETURN;
3415 END IF ;
3416
3417 END IF;
3418
3419
3420 -- Check the Activity type , Activity and Marketing medium associated to the schedule.
3421 IF p_schedule_rec.activity_type_code <> FND_API.g_miss_char
3422 OR p_schedule_rec.activity_id <> FND_API.g_miss_num
3423 OR p_schedule_rec.marketing_medium_id <> FND_API.g_miss_num
3424 OR p_schedule_rec.arc_marketing_medium_from <> FND_API.g_miss_char
3425 THEN
3426 AMS_ScheduleRules_PVT.Check_Schedule_Activity(
3427 p_schedule_id => p_schedule_rec.schedule_id,
3428 p_activity_type => p_schedule_rec.activity_type_code,
3429 p_activity_id => p_schedule_rec.activity_id,
3430 p_medium_id => p_schedule_rec.marketing_medium_id,
3431 p_arc_channel_from => p_schedule_rec.arc_marketing_medium_from,
3432 p_status_code => p_complete_rec.status_code,
3433 x_return_status => x_return_status ) ;
3434
3435 END IF;
3436 -- check parent source flag
3437 IF p_schedule_rec.activity_type_code = 'EVENTS' AND
3438 p_schedule_rec.use_parent_code_flag = 'Y'
3439 THEN
3440 AMS_Utility_PVT.Error_Message('AMS_CSCH_BAD_PARENT_CODE_FLAG');
3441 x_return_status := FND_API.g_ret_sts_error;
3442 END IF;
3443
3444 END Check_Schedule_Inter_Entity;
3445
3446 --===================================================================
3447 -- NAME
3448 -- Init_schedule_rec
3449 --
3450 -- PURPOSE
3451 -- Initialize schedules rec, used for testing.
3452 --
3453 -- NOTES
3454 --
3455 --
3456 -- HISTORY
3457 -- 22-Jan-2001 PTENDULK Created
3458 -- 27-jun-2003 ANCHAUDH added the extra columns in the record for triggers changes.
3459 --===================================================================
3460 PROCEDURE Init_Schedule_Rec(x_schedule_rec OUT NOCOPY schedule_rec_type)
3461 IS
3462 BEGIN
3463 x_schedule_rec.schedule_id := FND_API.G_MISS_NUM ;
3464 x_schedule_rec.last_update_date := FND_API.G_MISS_DATE ;
3465 x_schedule_rec.last_updated_by := FND_API.G_MISS_NUM ;
3466 x_schedule_rec.creation_date := FND_API.G_MISS_DATE ;
3467 x_schedule_rec.created_by := FND_API.G_MISS_NUM ;
3468 x_schedule_rec.last_update_login := FND_API.G_MISS_NUM;
3469 x_schedule_rec.object_version_number := FND_API.G_MISS_NUM ;
3470 x_schedule_rec.campaign_id := FND_API.G_MISS_NUM ;
3471 x_schedule_rec.user_status_id := FND_API.G_MISS_NUM ;
3472 x_schedule_rec.status_code := FND_API.G_MISS_CHAR ;
3473 x_schedule_rec.status_date := FND_API.G_MISS_DATE ;
3474 x_schedule_rec.source_code := FND_API.G_MISS_CHAR ;
3475 x_schedule_rec.use_parent_code_flag := FND_API.G_MISS_CHAR ;
3476 x_schedule_rec.start_date_time := FND_API.G_MISS_DATE ;
3477 x_schedule_rec.end_date_time := FND_API.G_MISS_DATE ;
3478 x_schedule_rec.timezone_id := FND_API.G_MISS_NUM ;
3479 x_schedule_rec.activity_type_code := FND_API.G_MISS_CHAR ;
3480 x_schedule_rec.activity_id := FND_API.G_MISS_NUM ;
3481 x_schedule_rec.arc_marketing_medium_from := FND_API.G_MISS_CHAR ;
3482 x_schedule_rec.marketing_medium_id := FND_API.G_MISS_NUM ;
3483 x_schedule_rec.custom_setup_id := FND_API.G_MISS_NUM ;
3484 x_schedule_rec.triggerable_flag := FND_API.G_MISS_CHAR ;
3485 x_schedule_rec.trigger_id := FND_API.G_MISS_NUM ;
3486 x_schedule_rec.notify_user_id := FND_API.G_MISS_NUM ;
3487 x_schedule_rec.approver_user_id := FND_API.G_MISS_NUM ;
3488 x_schedule_rec.owner_user_id := FND_API.G_MISS_NUM ;
3489 x_schedule_rec.active_flag := FND_API.G_MISS_CHAR ;
3490 x_schedule_rec.cover_letter_id := FND_API.G_MISS_NUM ;
3491 x_schedule_rec.reply_to_mail := FND_API.G_MISS_CHAR ;
3492 x_schedule_rec.mail_sender_name := FND_API.G_MISS_CHAR ;
3493 x_schedule_rec.mail_subject := FND_API.G_MISS_CHAR ;
3494 x_schedule_rec.from_fax_no := FND_API.G_MISS_CHAR ;
3495 x_schedule_rec.accounts_closed_flag := FND_API.G_MISS_CHAR ;
3496 x_schedule_rec.org_id := FND_API.G_MISS_NUM ;
3497 x_schedule_rec.objective_code := FND_API.G_MISS_CHAR ;
3498 x_schedule_rec.country_id := FND_API.G_MISS_NUM ;
3499 x_schedule_rec.campaign_calendar := FND_API.G_MISS_CHAR ;
3500 x_schedule_rec.start_period_name := FND_API.G_MISS_CHAR ;
3501 x_schedule_rec.end_period_name := FND_API.G_MISS_CHAR ;
3502 x_schedule_rec.priority := FND_API.G_MISS_CHAR ;
3503 x_schedule_rec.workflow_item_key := FND_API.G_MISS_CHAR ;
3504 x_schedule_rec.transaction_currency_code := FND_API.G_MISS_CHAR ;
3505 x_schedule_rec.functional_currency_code := FND_API.G_MISS_CHAR ;
3506 x_schedule_rec.budget_amount_tc := FND_API.G_MISS_NUM ;
3507 x_schedule_rec.budget_amount_fc := FND_API.G_MISS_NUM ;
3508 x_schedule_rec.language_code := FND_API.G_MISS_CHAR ;
3509 x_schedule_rec.task_id := FND_API.G_MISS_NUM ;
3510 x_schedule_rec.related_event_from := FND_API.G_MISS_CHAR ;
3511 x_schedule_rec.related_event_id := FND_API.G_MISS_NUM ;
3512 x_schedule_rec.attribute_category := FND_API.G_MISS_CHAR ;
3513 x_schedule_rec.attribute1 := FND_API.G_MISS_CHAR ;
3514 x_schedule_rec.attribute2 := FND_API.G_MISS_CHAR ;
3515 x_schedule_rec.attribute3 := FND_API.G_MISS_CHAR ;
3516 x_schedule_rec.attribute4 := FND_API.G_MISS_CHAR ;
3517 x_schedule_rec.attribute5 := FND_API.G_MISS_CHAR ;
3518 x_schedule_rec.attribute6 := FND_API.G_MISS_CHAR ;
3519 x_schedule_rec.attribute7 := FND_API.G_MISS_CHAR ;
3520 x_schedule_rec.attribute8 := FND_API.G_MISS_CHAR ;
3521 x_schedule_rec.attribute9 := FND_API.G_MISS_CHAR ;
3522 x_schedule_rec.attribute10 := FND_API.G_MISS_CHAR ;
3523 x_schedule_rec.attribute11 := FND_API.G_MISS_CHAR ;
3524 x_schedule_rec.attribute12 := FND_API.G_MISS_CHAR ;
3525 x_schedule_rec.attribute13 := FND_API.G_MISS_CHAR ;
3526 x_schedule_rec.attribute14 := FND_API.G_MISS_CHAR ;
3527 x_schedule_rec.attribute15 := FND_API.G_MISS_CHAR ;
3528 x_schedule_rec.activity_attribute_category := FND_API.G_MISS_CHAR ;
3529 x_schedule_rec.activity_attribute1 := FND_API.G_MISS_CHAR ;
3530 x_schedule_rec.activity_attribute2 := FND_API.G_MISS_CHAR ;
3531 x_schedule_rec.activity_attribute3 := FND_API.G_MISS_CHAR ;
3532 x_schedule_rec.activity_attribute4 := FND_API.G_MISS_CHAR ;
3533 x_schedule_rec.activity_attribute5 := FND_API.G_MISS_CHAR ;
3534 x_schedule_rec.activity_attribute6 := FND_API.G_MISS_CHAR ;
3535 x_schedule_rec.activity_attribute7 := FND_API.G_MISS_CHAR ;
3536 x_schedule_rec.activity_attribute8 := FND_API.G_MISS_CHAR ;
3537 x_schedule_rec.activity_attribute9 := FND_API.G_MISS_CHAR ;
3538 x_schedule_rec.activity_attribute10 := FND_API.G_MISS_CHAR ;
3539 x_schedule_rec.activity_attribute11 := FND_API.G_MISS_CHAR ;
3540 x_schedule_rec.activity_attribute12 := FND_API.G_MISS_CHAR ;
3541 x_schedule_rec.activity_attribute13 := FND_API.G_MISS_CHAR ;
3542 x_schedule_rec.activity_attribute14 := FND_API.G_MISS_CHAR ;
3543 x_schedule_rec.activity_attribute15 := FND_API.G_MISS_CHAR ;
3544 -- removed by soagrawa on 24-sep-2001
3545 -- x_schedule_rec.security_group_id := FND_API.G_MISS_NUM ;
3546 x_schedule_rec.schedule_name := FND_API.G_MISS_CHAR ;
3547 x_schedule_rec.description := FND_API.G_MISS_CHAR ;
3548 x_schedule_rec.related_source_id := FND_API.G_MISS_NUM ;
3549 x_schedule_rec.related_source_code := FND_API.G_MISS_CHAR ;
3550 x_schedule_rec.related_source_object := FND_API.G_MISS_CHAR ;
3551 x_schedule_rec.query_id := FND_API.G_MISS_NUM ;
3552 x_schedule_rec.include_content_flag := FND_API.G_MISS_CHAR;
3553 x_schedule_rec.content_type := FND_API.G_MISS_CHAR;
3554 x_schedule_rec.test_email_address := FND_API.G_MISS_CHAR;
3555 x_schedule_rec.greeting_text := FND_API.G_MISS_CHAR;
3556 x_schedule_rec.footer_text := FND_API.G_MISS_CHAR;
3557 -- following are added by anchaudh on 27-jun-2003
3558 x_schedule_rec.trig_repeat_flag := FND_API.G_MISS_CHAR ;
3559 x_schedule_rec.tgrp_exclude_prev_flag := FND_API.G_MISS_CHAR ;
3560 x_schedule_rec.orig_csch_id := FND_API.G_MISS_NUM ;
3561 x_schedule_rec.cover_letter_version := FND_API.G_MISS_NUM ;
3562 -- following are added by dbiswas on 12-aug-2003
3563 x_schedule_rec.usage := FND_API.G_MISS_CHAR ;
3564 x_schedule_rec.purpose := FND_API.G_MISS_CHAR ;
3565 x_schedule_rec.last_activation_date := FND_API.G_MISS_DATE ;
3566 x_schedule_rec.sales_methodology_id := FND_API.G_MISS_NUM ;
3567 x_schedule_rec.printer_address := FND_API.G_MISS_CHAR ;
3568 -- following is added by dbiswas on 27-jul-2005
3569 x_schedule_rec.notify_on_activation_flag := FND_API.G_MISS_CHAR ;
3570 -- following is added by anchaudh on 01-Feb-2006
3571 x_schedule_rec.sender_display_name := FND_API.G_MISS_CHAR ;
3572 x_schedule_rec.delivery_mode := FND_API.G_MISS_CHAR ;
3573
3574
3575 END Init_Schedule_Rec ;
3576
3577
3578 -- ==============================================================================
3579 -- Start of Comments
3580 -- ==============================================================================
3581 -- API Name
3582 -- Copy_Camp_Schedule
3583 --
3584 -- Description
3585 -- To support the "Copy Schedule" functionality from the schedule overview
3586 -- and detail pages.
3587 --
3588 -- History
3589 -- 30-Apr-2001 soagrawa Created this procedure
3590 -- 22-oct-2001 soagrawa Fixed bug# 2068786
3591 -- 10-jan-2002 soagrawa Fixed bug# 2178737
3592 -- 07-feb-2002 soagrawa Fixed bug# 2229618 (copy does not retain the specified source code)
3593 -- 28-mar-2002 soagrawa Fixed bug# 2289769
3594 -- 22-may-2002 soagrawa Fixed bug# 2380670
3595 -- 02-jul-2002 soagrawa Fixed bug# 2442695 (length of coordinator name)
3596 -- 06-feb-2003 soagrawa Fixed bug# 2788922
3597 -- 27-jun-2003 anchaudh added the extra columns in the record for triggers backporting.
3598 -- 11-july-2003 anchaudh fixed bug#3046802
3599 -- ==============================================================================
3600 --
3601
3602 PROCEDURE Copy_Camp_Schedule(
3603 p_api_version IN NUMBER,
3604 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3605 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3606 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3607
3608 x_return_status OUT NOCOPY VARCHAR2,
3609 x_msg_count OUT NOCOPY NUMBER,
3610 x_msg_data OUT NOCOPY VARCHAR2,
3611
3612 p_source_object_id IN NUMBER,
3613 p_attributes_table IN AMS_CpyUtility_PVT.copy_attributes_table_type,
3614 p_copy_columns_table IN AMS_CpyUtility_PVT.copy_columns_table_type,
3615
3616 x_new_object_id OUT NOCOPY NUMBER,
3617 x_custom_setup_id OUT NOCOPY NUMBER
3618 )
3619 IS
3620
3621 L_API_NAME CONSTANT VARCHAR2(30) := 'Copy_Camp_Schedule';
3622 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
3623 l_return_status_full VARCHAR2(1);
3624 l_object_version_number NUMBER := 1;
3625 l_org_id NUMBER := FND_API.G_MISS_NUM;
3626 l_schedule_id NUMBER;
3627 l_new_schedule_id NUMBER;
3628 l_dummy NUMBER;
3629
3630 l_return_status VARCHAR2(1);
3631 --l_msg_count NUMBER;
3632 --l_msg_data VARCHAR2;
3633 l_custom_setup_id NUMBER := 1;
3634 -- length modified by soagrawa on 10-jan-2002
3635 -- bug# 2178737
3636 l_campaign_name VARCHAR2(240);--VARCHAR(50);
3637 -- soagrawa modified the sizes of country name and coordinator name
3638 -- to fix bug# 2442695
3639 l_country_name VARCHAR(240);
3640 l_coordinator_name VARCHAR(240);
3641
3642 l_attr_list Ams_CopyActivities_PVT.schedule_attr_rec_type;
3643
3644 CURSOR get_camp_id IS
3645 SELECT campaign_id
3646 FROM ams_campaigns_vl
3647 WHERE campaign_name = l_campaign_name;
3648 /*
3649 CURSOR get_country_id IS
3650 SELECT campaign_id
3651 FROM ams_campaigns_vl
3652 WHERE campaign_name = l_campaign_name;
3653
3654 */
3655 /* CURSOR fetch_sch_details (sch_id NUMBER) IS
3656 SELECT
3657 schedule_id,
3658 last_update_date,
3659 last_updated_by,
3660 creation_date,
3661 created_by,
3662 last_update_login,
3663 null, -- obj version number
3664 campaign_id,
3665 user_status_id,
3666 status_code,
3667 status_date,
3668 source_code,
3669 use_parent_code_flag,
3670 start_date_time,
3671 end_date_time,
3672 timezone_id,
3673 activity_type_code,
3674 activity_id,
3675 arc_marketing_medium_from,
3676 marketing_medium_id,
3677 custom_setup_id,
3678 triggerable_flag,
3679 trigger_id,
3680 notify_user_id,
3681 approver_user_id,
3682 owner_user_id,
3683 active_flag,
3684 cover_letter_id,
3685 reply_to_mail,
3686 mail_sender_name,
3687 mail_subject,
3688 from_fax_no,
3689 accounts_closed_flag,
3690 org_id,
3691 objective_code,
3692 country_id,
3693 campaign_calendar,
3694 start_period_name,
3695 end_period_name,
3696 priority,
3697 workflow_item_key,
3698 transaction_currency_code,
3699 functional_currency_code,
3700 budget_amount_tc,
3701 budget_amount_fc,
3702 language_code,
3703 task_id,
3704 related_event_from,
3705 related_event_id,
3706 attribute_category,
3707 attribute1,
3708 attribute2,
3709 attribute3,
3710 attribute4,
3711 attribute5,
3712 attribute6,
3713 attribute7,
3714 attribute8,
3715 attribute9,
3716 attribute10,
3717 attribute11,
3718 attribute12,
3719 attribute13,
3720 attribute14,
3721 attribute15,
3722 activity_attribute_category,
3723 activity_attribute1,
3724 activity_attribute2,
3725 activity_attribute3,
3726 activity_attribute4,
3727 activity_attribute5,
3728 activity_attribute6,
3729 activity_attribute7,
3730 activity_attribute8,
3731 activity_attribute9,
3732 activity_attribute10,
3733 activity_attribute11,
3734 activity_attribute12,
3735 activity_attribute13,
3736 activity_attribute14,
3737 activity_attribute15,
3738 security_group_id,
3739 schedule_name,
3740 description,
3741 null,null,NULL --related source stuff
3742 FROM ams_campaign_schedules_vl
3743 WHERE schedule_id = sch_id;
3744 */
3745 CURSOR fetch_sch_details (sch_id NUMBER) IS
3746 SELECT * FROM ams_campaign_schedules_vl
3747 WHERE schedule_id = sch_id ;
3748
3749 CURSOR fetch_event_details (event_id NUMBER) IS
3750 SELECT * FROM ams_event_offers_vl
3751 WHERE event_offer_id = event_id ;
3752
3753 CURSOR c_delivery (delv_id NUMBER) IS
3754 SELECT delivery_media_type_code
3755 FROM ams_act_delivery_methods
3756 WHERE activity_delivery_method_id = delv_id;
3757
3758 -- soagrawa 22-oct-2002 for bug# 2594717
3759 CURSOR c_eone_srccd(event_id NUMBER) IS
3760 SELECT source_code
3761 FROM ams_event_offers_all_b
3762 WHERE event_offer_id = event_id;
3763
3764 l_eone_srccd VARCHAR2(30);
3765
3766
3767
3768 l_reference_rec fetch_sch_details%ROWTYPE;
3769 -- dhsingh, 28.07.2004, Bug#3798545.
3770 --l_schedule_rec schedule_rec_type;
3771 l_schedule_rec AMS_Camp_Schedule_PUB.schedule_rec_type;
3772 -- END dhsingh.
3773 l_new_reference_rec fetch_sch_details%ROWTYPE;
3774
3775 --added by soagrawa on 11-jan-2002
3776 l_new_event_offer_id NUMBER;
3777 l_event_offer_rec AMS_EventOffer_PVT.evo_rec_type;
3778 l_reference_event_rec fetch_event_details%ROWTYPE;
3779
3780 l_errnum NUMBER;
3781 l_errcode VARCHAR2(30);
3782 l_errmsg VARCHAR2(4000);
3783
3784 BEGIN
3785
3786 -- Standard Start of API savepoint
3787 SAVEPOINT COPY_Camp_Schedule_PVT;
3788
3789 -- Standard call to check for call compatibility.
3790 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3791 p_api_version,
3792 l_api_name,
3793 G_PKG_NAME)
3794 THEN
3795 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3796 END IF;
3797
3798 -- Initialize message list if p_init_msg_list is set to TRUE.
3799 IF FND_API.to_Boolean( p_init_msg_list )
3800 THEN
3801 FND_MSG_PUB.initialize;
3802 END IF;
3803
3804 -- Debug Message
3805 IF (AMS_DEBUG_HIGH_ON) THEN
3806
3807 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
3808 END IF;
3809
3810
3811 -- Initialize API return status to SUCCESS
3812 x_return_status := FND_API.G_RET_STS_SUCCESS;
3813
3814
3815 --
3816 -- Start of API body
3817 --
3818
3819 -- ----------------------------
3820 -- fetch source object details
3821 -- ----------------------------
3822
3823 OPEN fetch_sch_details(p_source_object_id);
3824 FETCH fetch_sch_details INTO l_reference_rec;
3825 CLOSE fetch_sch_details;
3826
3827 -- Copy GENERAL info from source schedule
3828 -- IF AMS_CpyUtility_PVT.is_copy_attribute ('DETL', p_attributes_table) = FND_API.G_TRUE THEN
3829 -- OPEN fetch_sch_details(p_source_object_id);
3830 -- FETCH fetch_sch_details INTO l_schedule_rec;
3831 -- CLOSE fetch_sch_details;
3832 -- END IF;
3833
3834
3835
3836
3837 l_schedule_rec.schedule_id := null;
3838
3839 -- following line of code is added by ptendulk on 06-Jun-2001
3840 --
3841
3842 l_schedule_rec.activity_type_code := l_reference_rec.activity_type_code ;
3843 l_schedule_rec.activity_id := l_reference_rec.activity_id;
3844 l_schedule_rec.arc_marketing_medium_from := l_reference_rec.arc_marketing_medium_from;
3845 l_schedule_rec.marketing_medium_id := l_reference_rec.marketing_medium_id;
3846 l_schedule_rec.custom_setup_id := l_reference_rec.custom_setup_id;
3847 -- anchaudh : for bug#3046802
3848 l_schedule_rec.triggerable_flag := 'N'; --l_reference_rec.triggerable_flag;
3849 -- l_schedule_rec.trigger_id := l_reference_rec.trigger_id;
3850 l_schedule_rec.notify_user_id := l_reference_rec.notify_user_id;
3851 l_schedule_rec.approver_user_id := l_reference_rec.approver_user_id;
3852 l_schedule_rec.owner_user_id := l_reference_rec.owner_user_id;
3853 l_schedule_rec.active_flag := l_reference_rec.active_flag;
3854
3855 -- sodixit 09-oct-2003 commented. 11.5.10 onwards, cover_letter information will exists in IBC
3856 --l_schedule_rec.cover_letter_id := l_reference_rec.cover_letter_id;
3857 l_schedule_rec.reply_to_mail := l_reference_rec.reply_to_mail;
3858 l_schedule_rec.mail_sender_name := l_reference_rec.mail_sender_name;
3859 l_schedule_rec.mail_subject := l_reference_rec.mail_subject;
3860 l_schedule_rec.from_fax_no := l_reference_rec.from_fax_no;
3861 l_schedule_rec.accounts_closed_flag := l_reference_rec.accounts_closed_flag;
3862 l_schedule_rec.org_id := l_reference_rec.org_id;
3863 l_schedule_rec.objective_code := l_reference_rec.objective_code;
3864 l_schedule_rec.country_id := l_reference_rec.country_id;
3865 l_schedule_rec.campaign_calendar := l_reference_rec.campaign_calendar;
3866 l_schedule_rec.start_period_name := l_reference_rec.start_period_name;
3867 l_schedule_rec.end_period_name := l_reference_rec.end_period_name;
3868 l_schedule_rec.priority := l_reference_rec.priority;
3869 l_schedule_rec.workflow_item_key := l_reference_rec.workflow_item_key;
3870 l_schedule_rec.transaction_currency_code := l_reference_rec.transaction_currency_code;
3871 l_schedule_rec.functional_currency_code := l_reference_rec.functional_currency_code;
3872
3873 -- sodixit 09-oct-2003 added usage, purpose, sales_methodology_id for 11.5.10.
3874 l_schedule_rec.usage := l_reference_rec.usage;
3875 l_schedule_rec.purpose := l_reference_rec.purpose;
3876 l_schedule_rec.sales_methodology_id := l_reference_rec.sales_methodology_id;
3877 -- dbiswas 27-jul-2005 added notify_on_activation_flag for R12.
3878 l_schedule_rec.notify_on_activation_flag := l_reference_rec.notify_on_activation_flag;
3879 -- commented out by soagrawa on 22-oct-2001 to fix bug# 2068786
3880 -- l_schedule_rec.budget_amount_tc := l_reference_rec.budget_amount_tc;
3881 -- l_schedule_rec.budget_amount_fc := l_reference_rec.budget_amount_fc;
3882 l_schedule_rec.language_code := l_reference_rec.language_code;
3883 l_schedule_rec.task_id := l_reference_rec.task_id;
3884
3885 -- removed by soagrawa on 11-jan-2002
3886 -- l_schedule_rec.related_event_from := l_reference_rec.related_event_from;
3887 -- l_schedule_rec.related_event_id := l_reference_rec.related_event_id;
3888 l_schedule_rec.related_event_id := NULL;
3889
3890
3891 -- end soagrawa 11-jan-2002
3892 l_schedule_rec.attribute_category := l_reference_rec.attribute_category;
3893 l_schedule_rec.attribute1 := l_reference_rec.attribute1;
3894 l_schedule_rec.attribute2 := l_reference_rec.attribute2;
3895 l_schedule_rec.attribute3 := l_reference_rec.attribute3;
3896 l_schedule_rec.attribute4 := l_reference_rec.attribute4;
3897 l_schedule_rec.attribute5 := l_reference_rec.attribute5;
3898 l_schedule_rec.attribute6 := l_reference_rec.attribute6;
3899 l_schedule_rec.attribute7 := l_reference_rec.attribute7;
3900 l_schedule_rec.attribute8 := l_reference_rec.attribute8;
3901 l_schedule_rec.attribute9 := l_reference_rec.attribute9;
3902 l_schedule_rec.attribute10 := l_reference_rec.attribute10;
3903 l_schedule_rec.attribute11 := l_reference_rec.attribute11;
3904 l_schedule_rec.attribute12 := l_reference_rec.attribute12;
3905 l_schedule_rec.attribute13 := l_reference_rec.attribute13;
3906 l_schedule_rec.attribute14 := l_reference_rec.attribute14;
3907 l_schedule_rec.attribute15 := l_reference_rec.attribute15;
3908 l_schedule_rec.activity_attribute_category := l_reference_rec.activity_attribute_category;
3909 l_schedule_rec.activity_attribute1 := l_reference_rec.activity_attribute1;
3910 l_schedule_rec.activity_attribute2 := l_reference_rec.activity_attribute2;
3911 l_schedule_rec.activity_attribute3 := l_reference_rec.activity_attribute3;
3912 l_schedule_rec.activity_attribute4 := l_reference_rec.activity_attribute4;
3913 l_schedule_rec.activity_attribute5 := l_reference_rec.activity_attribute5;
3914 l_schedule_rec.activity_attribute6 := l_reference_rec.activity_attribute6;
3915 l_schedule_rec.activity_attribute7 := l_reference_rec.activity_attribute7;
3916 l_schedule_rec.activity_attribute8 := l_reference_rec.activity_attribute8;
3917 l_schedule_rec.activity_attribute9 := l_reference_rec.activity_attribute9;
3918 l_schedule_rec.activity_attribute10 := l_reference_rec.activity_attribute10;
3919 l_schedule_rec.activity_attribute11 := l_reference_rec.activity_attribute11;
3920 l_schedule_rec.activity_attribute12 := l_reference_rec.activity_attribute12;
3921 l_schedule_rec.activity_attribute13 := l_reference_rec.activity_attribute13;
3922 l_schedule_rec.activity_attribute14 := l_reference_rec.activity_attribute14;
3923 l_schedule_rec.activity_attribute15 := l_reference_rec.activity_attribute15;
3924 -- removed by soagrawa on 24-sep-2001
3925 -- l_schedule_rec.security_group_id := l_reference_rec.security_group_id;
3926 l_schedule_rec.schedule_name := l_reference_rec.schedule_name;
3927 --l_schedule_rec.description := l_reference_rec.description;
3928
3929 -- soagrawa 22-oct-2002 for bug# 2594717
3930 -- l_schedule_rec.related_source_object := l_reference_rec.related_event_from;
3931 -- l_schedule_rec.related_source_id := l_reference_rec.related_event_id;
3932
3933 l_schedule_rec.query_id := l_reference_rec.query_id;
3934 l_schedule_rec.include_content_flag := l_reference_rec.include_content_flag;
3935 l_schedule_rec.greeting_text := l_reference_rec.greeting_text;
3936 l_schedule_rec.footer_text := l_reference_rec.footer_text;
3937
3938 -- anchaudh 01-feb-2006 added sender_display_name column.
3939 l_schedule_rec.sender_display_name := l_reference_rec.sender_display_name;
3940 l_schedule_rec.delivery_mode := l_reference_rec.delivery_mode;
3941
3942 -- soagrawa 22-oct-2002 for bug# 2594717
3943 -- l_schedule_rec.related_source_id := NULL;
3944 -- l_schedule_rec.related_source_code := NULL;
3945 -- l_schedule_rec.related_source_object := NULL;
3946
3947 -- following trigger info won't get copied from now on: anchaudh for bug#3046802
3948 -- following are added by anchaudh on 27-jun-2003
3949 -- l_schedule_rec.trig_repeat_flag := l_reference_rec.trig_repeat_flag;
3950 --l_schedule_rec.tgrp_exclude_prev_flag := l_reference_rec.tgrp_exclude_prev_flag;
3951
3952 -- l_schedule_rec.orig_csch_id := l_reference_rec.orig_csch_id;
3953
3954 -- sodixit 09-oct-2003 commented. 11.5.10 onwards, cover_letter information will exists in IBC
3955 --l_schedule_rec.cover_letter_version := l_reference_rec.cover_letter_version;
3956 -- ------------------------------
3957 -- copy all required fields
3958 -- i.e. copy values of all mandatory columns from the copy UI
3959 -- Mandatory fields for CSCH are Start Date and End Date
3960 -- ------------------------------
3961
3962 l_schedule_rec.timezone_id := l_reference_rec.timezone_id;
3963 -- l_schedule_rec.user_status_id := AMS_Utility_PVT.get_default_user_status() ;-- NEW l_reference_rec.user_status_id
3964 l_schedule_rec.user_status_id := AMS_Utility_PVT.get_default_user_status('AMS_CAMPAIGN_SCHEDULE_STATUS','NEW') ;
3965 l_schedule_rec.status_code := 'NEW';
3966
3967
3968 -- -------------------------------------------
3969 -- if field is not passed in from copy_columns_table
3970 -- copy from the base object
3971 -- -------------------------------------------
3972
3973
3974 AMS_CpyUtility_PVT.get_column_value ('StartDate', p_copy_columns_table, l_schedule_rec.start_date_time);
3975 l_schedule_rec.start_date_time := NVL (l_schedule_rec.start_date_time, l_reference_rec.start_date_time);
3976 AMS_CpyUtility_PVT.get_column_value ('EndDate', p_copy_columns_table, l_schedule_rec.end_date_time);
3977 l_schedule_rec.end_date_time := NVL (l_schedule_rec.end_date_time, l_reference_rec.end_date_time);
3978
3979 -- added by soagrawa on 16-Jun-2001
3980 AMS_CpyUtility_PVT.get_column_value ('copyParSrcCode', p_copy_columns_table, l_schedule_rec.source_code);
3981 -- next line commented out by soagrawa on 28-mar-2002
3982 --l_schedule_rec.source_code := NVL (l_schedule_rec.source_code, l_reference_rec.source_code);
3983 AMS_CpyUtility_PVT.get_column_value ('copyUseParSrcCode', p_copy_columns_table, l_schedule_rec.use_parent_code_flag);
3984 l_schedule_rec.use_parent_code_flag := NVL (l_schedule_rec.use_parent_code_flag, l_reference_rec.use_parent_code_flag);
3985
3986 --
3987 -- mandatory fields for csch create are
3988 -- name, lang, coordinator, currency, st date, time zone
3989 --
3990
3991 AMS_CpyUtility_PVT.get_column_value ('Language', p_copy_columns_table, l_schedule_rec.language_code);
3992 l_schedule_rec.language_code := NVL (l_schedule_rec.language_code, l_reference_rec.language_code);
3993 AMS_CpyUtility_PVT.get_column_value ('CoordinatorName', p_copy_columns_table, l_coordinator_name);
3994 -- modified by soagrawa for bug# 2380670
3995 -- AMS_CpyUtility_PVT.get_column_value ('CoordinatorId', p_copy_columns_table, l_reference_rec.owner_user_id);
3996 AMS_CpyUtility_PVT.get_column_value ('CoordinatorId', p_copy_columns_table, l_schedule_rec.owner_user_id);
3997 l_schedule_rec.owner_user_id := NVL (l_schedule_rec.owner_user_id, l_reference_rec.owner_user_id);
3998
3999 AMS_CpyUtility_PVT.get_column_value ('Currency', p_copy_columns_table, l_schedule_rec.transaction_currency_code);
4000 l_schedule_rec.transaction_currency_code := NVL (l_schedule_rec.transaction_currency_code, l_reference_rec.transaction_currency_code);
4001
4002 --dbiswas added the following columns that can be edited on the UI.
4003
4004 AMS_CpyUtility_PVT.get_column_value ('MarketingMediumId', p_copy_columns_table, l_schedule_rec.marketing_medium_id);
4005 l_schedule_rec.marketing_medium_id := NVL (l_schedule_rec.marketing_medium_id, l_reference_rec.marketing_medium_id);
4006
4007 AMS_CpyUtility_PVT.get_column_value ('Purpose', p_copy_columns_table, l_schedule_rec.purpose);
4008
4009 AMS_CpyUtility_PVT.get_column_value ('SourceCode', p_copy_columns_table, l_schedule_rec.source_code);
4010
4011 AMS_CpyUtility_PVT.get_column_value ('description', p_copy_columns_table, l_schedule_rec.description);
4012
4013
4014 AMS_CpyUtility_PVT.get_column_value ('attribute_category', p_copy_columns_table, l_schedule_rec.attribute_category);
4015
4016 AMS_CpyUtility_PVT.get_column_value ('attribute1', p_copy_columns_table, l_schedule_rec.attribute1);
4017
4018 AMS_CpyUtility_PVT.get_column_value ('attribute2', p_copy_columns_table, l_schedule_rec.attribute2);
4019
4020 AMS_CpyUtility_PVT.get_column_value ('attribute3', p_copy_columns_table, l_schedule_rec.attribute3);
4021
4022 AMS_CpyUtility_PVT.get_column_value ('attribute4', p_copy_columns_table, l_schedule_rec.attribute4);
4023
4024 AMS_CpyUtility_PVT.get_column_value ('attribute5', p_copy_columns_table, l_schedule_rec.attribute5);
4025
4026 AMS_CpyUtility_PVT.get_column_value ('attribute6', p_copy_columns_table, l_schedule_rec.attribute6);
4027
4028 AMS_CpyUtility_PVT.get_column_value ('attribute7', p_copy_columns_table, l_schedule_rec.attribute7);
4029
4030 AMS_CpyUtility_PVT.get_column_value ('attribute8', p_copy_columns_table, l_schedule_rec.attribute8);
4031
4032 AMS_CpyUtility_PVT.get_column_value ('attribute9', p_copy_columns_table, l_schedule_rec.attribute9);
4033
4034 AMS_CpyUtility_PVT.get_column_value ('attribute10', p_copy_columns_table, l_schedule_rec.attribute10);
4035
4036 AMS_CpyUtility_PVT.get_column_value ('attribute11', p_copy_columns_table, l_schedule_rec.attribute11);
4037
4038 AMS_CpyUtility_PVT.get_column_value ('attribute12', p_copy_columns_table, l_schedule_rec.attribute12);
4039
4040 AMS_CpyUtility_PVT.get_column_value ('attribute13', p_copy_columns_table, l_schedule_rec.attribute13);
4041
4042 AMS_CpyUtility_PVT.get_column_value ('attribute14', p_copy_columns_table, l_schedule_rec.attribute14);
4043
4044 AMS_CpyUtility_PVT.get_column_value ('attribute15', p_copy_columns_table, l_schedule_rec.attribute15);
4045
4046
4047 AMS_CpyUtility_PVT.get_column_value ('activityAttributeCategory', p_copy_columns_table, l_schedule_rec.activity_attribute_category);
4048
4049 AMS_CpyUtility_PVT.get_column_value ('activityAttribute1', p_copy_columns_table, l_schedule_rec.activity_attribute1);
4050
4051 AMS_CpyUtility_PVT.get_column_value ('activityAttribute2', p_copy_columns_table, l_schedule_rec.activity_attribute2);
4052
4053 AMS_CpyUtility_PVT.get_column_value ('activityAttribute3', p_copy_columns_table, l_schedule_rec.activity_attribute3);
4054
4055 AMS_CpyUtility_PVT.get_column_value ('activityAttribute4', p_copy_columns_table, l_schedule_rec.activity_attribute4);
4056
4057 AMS_CpyUtility_PVT.get_column_value ('activityAttribute5', p_copy_columns_table, l_schedule_rec.activity_attribute5);
4058
4059 AMS_CpyUtility_PVT.get_column_value ('activityAttribute6', p_copy_columns_table, l_schedule_rec.activity_attribute6);
4060
4061 AMS_CpyUtility_PVT.get_column_value ('activityAttribute7', p_copy_columns_table, l_schedule_rec.activity_attribute7);
4062
4063 AMS_CpyUtility_PVT.get_column_value ('activityAttribute8', p_copy_columns_table, l_schedule_rec.activity_attribute8);
4064
4065 AMS_CpyUtility_PVT.get_column_value ('activityAttribute9', p_copy_columns_table, l_schedule_rec.activity_attribute9);
4066
4067 AMS_CpyUtility_PVT.get_column_value ('activityAttribute10', p_copy_columns_table, l_schedule_rec.activity_attribute10);
4068
4069 AMS_CpyUtility_PVT.get_column_value ('activityAttribute11', p_copy_columns_table, l_schedule_rec.activity_attribute11);
4070
4071 AMS_CpyUtility_PVT.get_column_value ('activityAttribute12', p_copy_columns_table, l_schedule_rec.activity_attribute12);
4072
4073 AMS_CpyUtility_PVT.get_column_value ('activityAttribute13', p_copy_columns_table, l_schedule_rec.activity_attribute13);
4074
4075 AMS_CpyUtility_PVT.get_column_value ('activityAttribute14', p_copy_columns_table, l_schedule_rec.activity_attribute14);
4076
4077 AMS_CpyUtility_PVT.get_column_value ('activityAttribute15', p_copy_columns_table, l_schedule_rec.activity_attribute15);
4078
4079 -- end updates by dbiswas for columns that can be edited from the UI
4080 -- -------------------------------------------
4081 -- if field is not passed in from copy_columns_table
4082 -- copy from the base object
4083 -- -------------------------------------------
4084
4085 AMS_CpyUtility_PVT.get_column_value ('newObjName', p_copy_columns_table, l_schedule_rec.schedule_name);
4086
4087 AMS_CpyUtility_PVT.get_column_value ('CampaignName', p_copy_columns_table, l_campaign_name);
4088 OPEN get_camp_id;
4089 FETCH get_camp_id INTO l_schedule_rec.campaign_id;
4090 CLOSE get_camp_id;
4091
4092 -- next line removed by soagrawa on 07-mar-2002 to fix bug# 2229618
4093 -- AMS_CpyUtility_PVT.get_column_value ('CampaignSource', p_copy_columns_table, l_schedule_rec.source_code);
4094
4095 /*AMS_CpyUtility_PVT.get_column_value ('CountryName', p_copy_columns_table, l_country_name);
4096 OPEN get_country_id;
4097 FETCH get_camp_id INTO l_schedule_rec.campaign_id;
4098 CLOSE get_camp_id;
4099 */
4100
4101
4102 -- #Fix for bug 2989203 by asaha
4103 IF(l_schedule_rec.activity_type_code = 'EVENTS' AND
4104 l_schedule_rec.use_parent_code_flag = 'Y') THEN
4105 IF(AMS_DEBUG_HIGH_ON) THEN
4106 AMS_UTILITY_PVT.Debug_Message('change use parent flag to N for Event type schedule
4107 ');
4108 END IF;
4109 l_schedule_rec.use_parent_code_flag := 'N';
4110 END IF;
4111 -- end of Fix for bug 2989203
4112
4113
4114
4115 -- AMS_CpyUtility_PVT.get_column_value ('', p_copy_columns_table, l_schedule_rec.);
4116
4117 AMS_UTILITY_PVT.debug_message('before copying event '||l_schedule_rec.activity_type_code);
4118 AMS_UTILITY_PVT.debug_message('before copying event '||l_reference_rec.related_event_id);
4119
4120 -- added by soagrawa on 11-jan-2002
4121 -- copy event details into a new EONE and update new schedule with that new id
4122 IF l_schedule_rec.activity_type_code = 'EVENTS'
4123 -- soagrawa 06-feb-2003 fixed bug# 2788922
4124 -- AND l_schedule_rec.related_event_id IS NOT null
4125 AND l_reference_rec.related_event_id IS NOT null
4126 THEN
4127
4128 -- get original related event's data
4129 OPEN fetch_event_details(l_reference_rec.related_event_id);
4130 FETCH fetch_event_details INTO l_reference_event_rec;
4131 CLOSE fetch_event_details;
4132
4133 OPEN c_delivery(l_reference_event_rec.event_delivery_method_id);
4134 FETCH c_delivery INTO l_event_offer_rec.event_delivery_method_code;
4135 CLOSE c_delivery;
4136
4137 -- copy whatever remains same
4138 l_event_offer_rec.event_level := l_reference_event_rec.event_level ;
4139 l_event_offer_rec.event_type_code := l_reference_event_rec.event_type_code ;
4140 l_event_offer_rec.event_object_type := 'EONE' ;
4141
4142 -- l_event_offer_rec.event_delivery_method_id := l_reference_event_rec.event_delivery_method_id ;
4143 -- dbiswas changed event_venue_id to NULL on 24-Jun-2003. Bug 3008802
4144 -- l_event_offer_rec.event_venue_id := l_reference_event_rec.event_venue_id ;
4145 l_event_offer_rec.event_location_id := l_reference_event_rec.event_location_id ;
4146 l_event_offer_rec.reg_required_flag := l_reference_event_rec.reg_required_flag ;
4147 l_event_offer_rec.reg_charge_flag := l_reference_event_rec.reg_charge_flag ;
4148 l_event_offer_rec.reg_invited_only_flag := l_reference_event_rec.reg_invited_only_flag ;
4149 l_event_offer_rec.event_standalone_flag := l_reference_event_rec.event_standalone_flag ;
4150 l_event_offer_rec.create_attendant_lead_flag := l_reference_event_rec.create_attendant_lead_flag ;
4151 l_event_offer_rec.create_registrant_lead_flag := l_reference_event_rec.create_registrant_lead_flag ;
4152 l_event_offer_rec.private_flag := l_reference_event_rec.private_flag ;
4153 l_event_offer_rec.parent_type := l_reference_event_rec.parent_type;
4154 l_event_offer_rec.country_code := l_reference_event_rec.country_code;
4155 l_event_offer_rec.user_status_id := l_reference_event_rec.user_status_id;
4156 l_event_offer_rec.system_status_code := l_reference_event_rec.system_status_code;
4157 l_event_offer_rec.application_id := l_reference_event_rec.application_id;
4158 l_event_offer_rec.custom_setup_id := l_reference_event_rec.setup_type_id;
4159
4160 -- modify whatever needs to be changed
4161 l_event_offer_rec.event_start_date := l_schedule_rec.start_date_time ;
4162 l_event_offer_rec.event_end_date := l_schedule_rec.end_date_time ;
4163 l_event_offer_rec.event_offer_name := l_schedule_rec.schedule_name;
4164 l_event_offer_rec.owner_user_id := l_schedule_rec.owner_user_id;
4165 -- l_event_offer_rec.source_code := NVL (l_event_offer_rec.source_code, NULL);
4166 -- l_event_offer_rec.currency_code_tc := NVL (l_event_offer_rec.source_code, NULL);
4167 l_event_offer_rec.event_language_code:= l_schedule_rec.language_code;
4168 l_event_offer_rec.parent_id := l_schedule_rec.campaign_id;
4169
4170 -- null valued attributes
4171 l_event_offer_rec.business_unit_id := NULL;
4172 l_event_offer_rec.event_venue_id := NULL;
4173 -- end update by dbiswas for bug 3008802
4174 l_event_offer_rec.reg_start_date := NULL;
4175 l_event_offer_rec.reg_end_date := NULL;
4176 l_event_offer_rec.city := NULL;
4177 l_event_offer_rec.state := NULL;
4178 l_event_offer_rec.country :=NULL;
4179 l_event_offer_rec.description := NULL;
4180 l_event_offer_rec.start_period_name := NULL;
4181 l_event_offer_rec.end_period_name := NULL;
4182 l_event_offer_rec.priority_type_code := NULL;
4183 l_event_offer_rec.INVENTORY_ITEM_ID := NULL;
4184 l_event_offer_rec.PRICELIST_HEADER_ID := NULL;
4185 l_event_offer_rec.PRICELIST_LINE_ID := NULL;
4186 l_event_offer_rec.FORECASTED_REVENUE := NULL;
4187 l_event_offer_rec.ACTUAL_REVENUE := NULL;
4188 l_event_offer_rec.FORECASTED_COST := NULL;
4189 l_event_offer_rec.ACTUAL_COST := NULL;
4190 l_event_offer_rec.FUND_SOURCE_TYPE_CODE := NULL;
4191 l_event_offer_rec.FUND_SOURCE_ID := NULL;
4192 l_event_offer_rec.FUND_AMOUNT_FC := NULL;
4193 l_event_offer_rec.FUND_AMOUNT_TC := NULL;
4194
4195 IF (AMS_DEBUG_HIGH_ON) THEN
4196
4197 AMS_UTILITY_PVT.debug_message('before req_items>'||l_event_offer_rec.event_delivery_method_id||'<');
4198
4199 END IF;
4200 IF (AMS_DEBUG_HIGH_ON) THEN
4201
4202 AMS_UTILITY_PVT.debug_message('before req_items>'||l_reference_event_rec.event_delivery_method_id||'<');
4203 END IF;
4204
4205 AMS_UTILITY_PVT.debug_message('before calling create event offer');
4206
4207 -- created the event EONE
4208 -- dhsingh, 28.07.2004, Bug# 3798545.
4209 /*
4210 AMS_EventOffer_PVT.create_event_offer (
4211 p_api_version => 1.0,
4212 p_init_msg_list => FND_API.G_FALSE,
4213 p_commit => FND_API.G_FALSE,
4214 p_validation_level => FND_API.g_valid_level_full,
4215 p_evo_rec => l_event_offer_rec,
4216 x_return_status => x_return_status,
4217 x_msg_count => x_msg_count,
4218 x_msg_data => x_msg_data,
4219 x_evo_id => l_new_event_offer_id
4220 );
4221 */
4222 AMS_EventOffer_PUB.create_EventOffer(
4223 p_api_version => 1.0,
4224 p_init_msg_list => FND_API.G_FALSE,
4225 p_commit => FND_API.G_FALSE,
4226 p_validation_level => FND_API.g_valid_level_full,
4227 p_evo_rec => l_event_offer_rec,
4228 x_return_status => x_return_status,
4229 x_msg_count => x_msg_count,
4230 x_msg_data => x_msg_data,
4231 x_evo_id => l_new_event_offer_id
4232 );
4233
4234 -- END dhsingh
4235
4236 AMS_UTILITY_PVT.debug_message('after calling create event offer '||l_new_event_offer_id);
4237
4238 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4239 RAISE FND_API.G_EXC_ERROR;
4240 END IF;
4241
4242
4243
4244 /*
4245 AMS_EventSchedule_Copy_PVT.copy_act_delivery_method(
4246 p_src_act_type => 'EONE',
4247 p_new_act_type => 'EONE',
4248 p_src_act_id => l_schedule_rec.related_event_id,
4249 p_new_act_id => l_new_event_offer_id,
4250 p_errnum => l_errnum,
4251 p_errcode => l_errcode,
4252 p_errmsg => l_errmsg
4253 );
4254
4255 IF l_errnum > 0 THEN
4256 RAISE FND_API.G_EXC_ERROR;
4257 END IF;
4258 */
4259
4260
4261 -- update new schedule with this id
4262 l_schedule_rec.related_event_from := l_schedule_rec.related_event_from;
4263 l_schedule_rec.related_event_id := l_new_event_offer_id;
4264
4265 -- soagrawa 22-oct-2002 for bug# 2594717
4266 OPEN c_eone_srccd(l_new_event_offer_id);
4267 FETCH c_eone_srccd INTO l_eone_srccd;
4268 CLOSE c_eone_srccd;
4269 l_schedule_rec.related_source_id := l_new_event_offer_id;
4270 l_schedule_rec.related_source_code := l_eone_srccd;
4271 l_schedule_rec.related_source_object := 'EONE';
4272
4273 END IF;
4274
4275
4276 -- ----------------------------
4277 -- call create api
4278 -- ----------------------------
4279 -- dhsingh, 28.07.2004, Bug#3798545
4280 /*
4281 Create_Camp_Schedule(
4282 p_api_version,
4283 p_init_msg_list,
4284 p_commit,
4285 p_validation_level,
4286
4287 x_return_status,
4288 x_msg_count,
4289 x_msg_data,
4290
4291 l_schedule_rec,
4292 l_new_schedule_id ) ;
4293 */
4294 AMS_Camp_Schedule_PUB.create_camp_schedule(
4295 p_api_version,
4296 p_init_msg_list,
4297 p_commit,
4298 p_validation_level,
4299
4300 x_return_status,
4301 x_msg_count,
4302 x_msg_data,
4303
4304 l_schedule_rec,
4305 l_new_schedule_id ) ;
4306 -- END dhsingh
4307 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4308 RAISE FND_API.G_EXC_ERROR;
4309 END IF;
4310
4311 -- Attributes:
4312 -- Event Agenda AGEN // Leave for later
4313 -- Attachments ATCH
4314 -- Event Category CATG // Leave for later
4315 -- Market CELL
4316 -- Deliverables DELV
4317 -- Messages MESG
4318 -- Products PROD
4319 -- Partners PTNR
4320 -- Registration REGS // Leave for later
4321 -- Task TASK
4322
4323
4324 IF AMS_CpyUtility_PVT.is_copy_attribute ('AGEN', p_attributes_table) = FND_API.G_TRUE
4325 THEN l_attr_list.p_AGEN := 'Y';
4326 END IF;
4327
4328 IF AMS_CpyUtility_PVT.is_copy_attribute ('ATCH', p_attributes_table) = FND_API.G_TRUE
4329 THEN l_attr_list.p_ATCH := 'Y';
4330 END IF;
4331
4332 IF AMS_CpyUtility_PVT.is_copy_attribute ('CATG', p_attributes_table) = FND_API.G_TRUE
4333 THEN l_attr_list.p_CATG := 'Y';
4334 END IF;
4335
4336 IF AMS_CpyUtility_PVT.is_copy_attribute ('CELL', p_attributes_table) = FND_API.G_TRUE
4337 THEN l_attr_list.p_CELL := 'Y';
4338 END IF;
4339
4340 IF AMS_CpyUtility_PVT.is_copy_attribute ('DELV', p_attributes_table) = FND_API.G_TRUE
4341 THEN l_attr_list.p_DELV := 'Y';
4342 END IF;
4343
4344 IF AMS_CpyUtility_PVT.is_copy_attribute ('MESG', p_attributes_table) = FND_API.G_TRUE
4345 THEN l_attr_list.p_MESG := 'Y';
4346 END IF;
4347
4348 IF AMS_CpyUtility_PVT.is_copy_attribute ('PROD', p_attributes_table) = FND_API.G_TRUE
4349 THEN l_attr_list.p_PROD := 'Y';
4350 END IF;
4351
4352 IF AMS_CpyUtility_PVT.is_copy_attribute ('PTNR', p_attributes_table) = FND_API.G_TRUE
4353 THEN l_attr_list.p_PTNR := 'Y';
4354 END IF;
4355
4356 IF AMS_CpyUtility_PVT.is_copy_attribute ('REGS', p_attributes_table) = FND_API.G_TRUE
4357 THEN l_attr_list.p_REGS := 'Y';
4358 END IF;
4359
4360 -- added by soagrawa on 25-jan-2002 bug# 2175580
4361 IF AMS_CpyUtility_PVT.is_copy_attribute ('CONTENT', p_attributes_table) = FND_API.G_TRUE
4362 THEN l_attr_list.p_CONTENT := 'Y';
4363 END IF;
4364
4365 -- added by sodixit on 04-oct-2003 for 11.5.10. Applicable for both LITE schedules only.
4366 IF AMS_CpyUtility_PVT.is_copy_attribute ('TGRP', p_attributes_table) = FND_API.G_TRUE
4367 THEN l_attr_list.p_TGRP := 'Y';
4368 END IF;
4369
4370 -- added by sodixit on 04-oct-2003 for 11.5.10. Applicable for both LITE and CLASSIC schedules
4371 -- For Classic schedules, copy of COLT will always to true - to be in sync with 11.5.9 cover letter copy
4372 IF l_schedule_rec.usage = 'LITE' then
4373 IF AMS_CpyUtility_PVT.is_copy_attribute ('COLT', p_attributes_table) = FND_API.G_TRUE
4374 THEN
4375 l_attr_list.p_COLT := 'Y';
4376 -- Debug Message
4377 IF (AMS_DEBUG_HIGH_ON) THEN
4378 AMS_UTILITY_PVT.debug_message('Copy Attributes, usage is LITE and COLT component is selected' );
4379 END IF;
4380 ELSE
4381 -- Debug Message
4382 IF (AMS_DEBUG_HIGH_ON) THEN
4383 AMS_UTILITY_PVT.debug_message('Copy Attributes, usage is LITE but COLT component is not selected' );
4384 END IF;
4385 END IF;
4386 ELSE
4387 l_attr_list.p_COLT := 'Y';
4388 -- Debug Message
4389 IF (AMS_DEBUG_HIGH_ON) THEN
4390 AMS_UTILITY_PVT.debug_message('Copy Attributes, usage is PHAT thus setting copy COLT to true' );
4391 END IF;
4392 END IF;
4393
4394 IF AMS_CpyUtility_PVT.is_copy_attribute ('COLT', p_attributes_table) = FND_API.G_TRUE
4395 THEN l_attr_list.p_COLT := 'Y';
4396 END IF;
4397
4398 -- added by sodixit on 04-oct-2003 for 11.5.10. Applicable for LITE schedules.'OFFERING' does not exist for Classic schedules
4399 IF AMS_CpyUtility_PVT.is_copy_attribute ('OFFERING', p_attributes_table) = FND_API.G_TRUE
4400 THEN l_attr_list.p_PROD := 'Y';
4401 END IF;
4402
4403 -- added by spragupa on 23-nov-2007 for ER 6467510 - For extending COPY functionality for TASKS
4404 IF AMS_CpyUtility_PVT.is_copy_attribute ('TASK', p_attributes_table) = FND_API.G_TRUE
4405 THEN l_attr_list.p_TASK := 'Y';
4406 END IF;
4407
4408 Ams_CopyActivities_PVT.copy_schedule_attributes (
4409 p_api_version => 1.0,
4410 p_init_msg_list => FND_API.G_FALSE,
4411 p_commit => FND_API.G_FALSE,
4412 x_return_status => x_return_status,
4413 x_msg_count => x_msg_count,
4414 x_msg_data => x_msg_data,
4415 p_object_type => 'CSCH',
4416 p_src_object_id => p_source_object_id,
4417 p_tar_object_id => l_new_schedule_id,
4418 p_attr_list => l_attr_list
4419 );
4420
4421
4422 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4423 RAISE FND_API.G_EXC_ERROR;
4424 END IF;
4425
4426
4427 OPEN fetch_sch_details(l_new_schedule_id);
4428 FETCH fetch_sch_details INTO l_new_reference_rec;
4429 CLOSE fetch_sch_details;
4430
4431 x_new_object_id := l_new_schedule_id;
4432 x_custom_setup_id := l_new_reference_rec.custom_setup_id;
4433
4434
4435 IF FND_API.to_Boolean( p_commit )
4436 THEN
4437 COMMIT WORK;
4438 END IF;
4439
4440 -- Debug Message
4441 IF (AMS_DEBUG_HIGH_ON) THEN
4442
4443 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
4444 END IF;
4445
4446 -- Standard call to get message count and if count is 1, get message info.
4447 FND_MSG_PUB.Count_And_Get
4448 (p_count => x_msg_count,
4449 p_data => x_msg_data
4450 );
4451
4452
4453 EXCEPTION
4454 WHEN FND_API.G_EXC_ERROR THEN
4455 ROLLBACK TO COPY_Camp_Schedule_PVT;
4456 x_return_status := FND_API.G_RET_STS_ERROR;
4457 -- Standard call to get message count and if count=1, get the message
4458 FND_MSG_PUB.Count_And_Get (
4459 p_encoded => FND_API.G_FALSE,
4460 p_count => x_msg_count,
4461 p_data => x_msg_data
4462 );
4463 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4464 ROLLBACK TO COPY_Camp_Schedule_PVT;
4465 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4466 -- Standard call to get message count and if count=1, get the message
4467 FND_MSG_PUB.Count_And_Get (
4468 p_encoded => FND_API.G_FALSE,
4469 p_count => x_msg_count,
4470 p_data => x_msg_data
4471 );
4472 WHEN OTHERS THEN
4473 ROLLBACK TO COPY_Camp_Schedule_PVT;
4474 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4475 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4476 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4477 END IF;
4478 -- Standard call to get message count and if count=1, get the message
4479 FND_MSG_PUB.Count_And_Get (
4480 p_encoded => FND_API.G_FALSE,
4481 p_count => x_msg_count,
4482 p_data => x_msg_data
4483 );
4484
4485
4486 END Copy_Camp_Schedule;
4487
4488 END AMS_Camp_Schedule_PVT;