[Home] [Help]
PACKAGE BODY: APPS.AMS_CAMPAIGN_SCHEDULES_B_PKG
Source
1 PACKAGE BODY AMS_CAMPAIGN_SCHEDULES_B_PKG as
2 /* $Header: amstschb.pls 120.3 2006/05/31 11:56:41 srivikri ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_CAMPAIGN_SCHEDULES_B_PKG
7 -- Purpose
8 -- Table api to create/insert/update campaign Schedules.
9 -- History
10 -- 22-Jan-2000 ptendulk Created.
11 -- 24-sep-2001 soagrawa Removed security group id from everywhere
12 -- 26-sep-2001 soagrawa Modified insert_row and update_row to save date properly
13 -- 26-dec-2001 aranka Modified update_row to comment creation_date and created_by Bug#2160058
14 -- 31-jan-2002 soagrawa Modified update_row for bug# 2160058, 2204087
15 -- 12-jun-2002 soagrawa Added add_language for bug# 2323843 for mainline
16 -- 12-jun-2002 soagrawa Fixed ATT bug# 2376329 created by updated by issue from insert_row
17 -- 26-Jan-2003 ptendulk Commented debug messages. Bug 2767243
18 -- 27-jun-2003 anchaudh Added four more columns(trig_repeat_flag,trgp_exclude_prev,orig_csch_id,cover_letter_version) to be inserted and updated into the table.
19 -- 12-aug-2003 dbiswas Added 3 new columns(usage,purpose,last_activation_date) to be inserted and updated into the table.
20 -- 25-aug-2003 dbiswas Added 1 new columns(sales_methodology_id) to be inserted and updated into the table.
21 -- 27-jul-2005 dbiswas Added 1 new columns(notify_on_activation_flag) to be inserted and updated into the table.
22 -- 29-May-2006 srivikri added column delivery_mode
23 -- NOTE
24 --
25 -- End of Comments
26 -- ===============================================================
27
28
29 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_CAMPAIGN_SCHEDULES_B_PKG';
30 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstschb.pls';
31
32 -- ===============================================================
33 -- Start of Comments
34 -- Procedure name
35 -- Insert_Row
36 -- Purpose
37 -- Table api to insert campaign Schedules.
38 -- History
39 -- 22-Jan-2000 ptendulk Created.
40 -- 26-Sep-2001 ptendulk Modified inserting date, so that time would be saved as well
41 -- 27-jun-2003 anchaudh Added four more columns(trig_repeat_flag,trgp_exclude_prev,orig_csch_id,cover_letter_version) to be inserted and updated into the table.
42 -- 12-aug-2003 dbiswas Added 3 new columns(usage,purpose,last_activation_date) to be inserted and updated into the table.
43 -- 25-aug-2003 dbiswas Added 1 new column(sales_methodology_id) to be inserted and updated into the table.
44 -- 27-jul-2005 dbiswas Added 1 new columns(notify_on_activation_flag) to be inserted and updated into the table.
45 -- 29-May-2006 srivikri added column delivery_mode
46 -- NOTE
47 --
48 -- End of Comments
49 -- ===============================================================
50 PROCEDURE Insert_Row(
51 px_schedule_id IN OUT NOCOPY NUMBER,
52 p_last_update_date DATE,
53 p_last_updated_by NUMBER,
54 p_creation_date DATE,
55 p_created_by NUMBER,
56 p_last_update_login NUMBER,
57 px_object_version_number IN OUT NOCOPY NUMBER,
58 p_campaign_id NUMBER,
59 p_user_status_id NUMBER,
60 p_status_code VARCHAR2,
61 p_status_date DATE,
62 p_source_code VARCHAR2,
63 p_use_parent_code_flag VARCHAR2,
64 p_start_date_time DATE,
65 p_end_date_time DATE,
66 p_timezone_id NUMBER,
67 p_activity_type_code VARCHAR2,
68 p_activity_id NUMBER,
69 p_arc_marketing_medium_from VARCHAR2,
70 p_marketing_medium_id NUMBER,
71 p_custom_setup_id NUMBER,
72 p_triggerable_flag VARCHAR2,
73 p_trigger_id NUMBER,
74 p_notify_user_id NUMBER,
75 p_approver_user_id NUMBER,
76 p_owner_user_id NUMBER,
77 p_active_flag VARCHAR2,
78 p_cover_letter_id NUMBER,
79 p_reply_to_mail VARCHAR2,
80 p_mail_sender_name VARCHAR2,
81 p_mail_subject VARCHAR2,
82 p_from_fax_no VARCHAR2,
83 p_accounts_closed_flag VARCHAR2,
84 px_org_id IN OUT NOCOPY NUMBER,
85 p_objective_code VARCHAR2,
86 p_country_id NUMBER,
87 p_campaign_calendar VARCHAR2,
88 p_start_period_name VARCHAR2,
89 p_end_period_name VARCHAR2,
90 p_priority VARCHAR2,
91 p_workflow_item_key VARCHAR2,
92 p_transaction_currency_code VARCHAR2,
93 p_functional_currency_code VARCHAR2,
94 p_budget_amount_tc NUMBER,
95 p_budget_amount_fc NUMBER,
96 p_language_code VARCHAR2,
97 p_task_id NUMBER,
98 p_related_event_from VARCHAR2,
99 p_related_event_id NUMBER,
100 p_attribute_category VARCHAR2,
101 p_attribute1 VARCHAR2,
102 p_attribute2 VARCHAR2,
103 p_attribute3 VARCHAR2,
104 p_attribute4 VARCHAR2,
105 p_attribute5 VARCHAR2,
106 p_attribute6 VARCHAR2,
107 p_attribute7 VARCHAR2,
108 p_attribute8 VARCHAR2,
109 p_attribute9 VARCHAR2,
110 p_attribute10 VARCHAR2,
111 p_attribute11 VARCHAR2,
112 p_attribute12 VARCHAR2,
113 p_attribute13 VARCHAR2,
114 p_attribute14 VARCHAR2,
115 p_attribute15 VARCHAR2,
116 p_activity_attribute_category VARCHAR2,
117 p_activity_attribute1 VARCHAR2,
118 p_activity_attribute2 VARCHAR2,
119 p_activity_attribute3 VARCHAR2,
120 p_activity_attribute4 VARCHAR2,
121 p_activity_attribute5 VARCHAR2,
122 p_activity_attribute6 VARCHAR2,
123 p_activity_attribute7 VARCHAR2,
124 p_activity_attribute8 VARCHAR2,
125 p_activity_attribute9 VARCHAR2,
126 p_activity_attribute10 VARCHAR2,
127 p_activity_attribute11 VARCHAR2,
128 p_activity_attribute12 VARCHAR2,
129 p_activity_attribute13 VARCHAR2,
130 p_activity_attribute14 VARCHAR2,
131 p_activity_attribute15 VARCHAR2,
132 -- removed by soagrawa on 24-sep-2001
133 -- p_security_group_id NUMBER,
134 p_query_id NUMBER,
135 p_include_content_flag VARCHAR2,
136 p_content_type VARCHAR2,
137 p_test_email_address VARCHAR2,
138 p_schedule_name VARCHAR2,
139 p_schedule_description VARCHAR2,
140 p_greeting_text VARCHAR2,
141 p_footer_text VARCHAR2,
142 --added by anchaudh on 30-apr-2003
143 p_trig_repeat_flag VARCHAR2,
144 p_tgrp_exclude_prev_flag VARCHAR2,
145 --added by anchaudh on 06-may-2003
146 p_orig_csch_id NUMBER,
147 p_cover_letter_version NUMBER,
148 --added by dbiswas on 12-aug-2003
149 p_usage VARCHAR2,
150 p_purpose VARCHAR2,
151 p_last_activation_date DATE,
152 p_sales_methodology_id NUMBER,
153 p_printer_address VARCHAR2,
154 --added by dbiswas on 27-jul-2005
155 p_notify_on_activation_flag VARCHAR2,
156 --added by anchaudh on 01-feb-2006
157 p_sender_display_name VARCHAR2,
158 --added by srivikri
159 p_delivery_mode VARCHAR2
160 )
161
162 IS
163 x_rowid VARCHAR2(30);
164 -- date variables added by ptendulk on 26-sep-2001
165 l_start_date DATE ;
166 l_end_date DATE ;
167 BEGIN
168
169
170 px_object_version_number := 1;
171
172 -- added by ptendulk on 26-sep-2001
173 -- for storing time correctly in the database
174 -- DBMS_OUTPUT.PUT_LINE('Start date : '||TO_CHAR(p_start_date_time,'DDMMYY HH:MI:SS PM'));
175
176 IF p_start_date_time = FND_API.g_miss_date THEN
177 l_start_date := NULL ;
178 ELSE
179 l_start_date := p_start_date_time ;
180 END IF ;
181
182 IF p_end_date_time = FND_API.g_miss_date THEN
183 l_end_date := null ;
184 ELSE
185 l_end_date := p_end_date_time ;
186 END IF ;
187
188 -- end ptendulk 26-sep-2001
189
190 INSERT INTO AMS_CAMPAIGN_SCHEDULES_B(
191 schedule_id,
192 last_update_date,
193 last_updated_by,
194 creation_date,
195 created_by,
196 last_update_login,
197 object_version_number,
198 campaign_id,
199 user_status_id,
200 status_code,
201 status_date,
202 source_code,
203 use_parent_code_flag,
204 start_date_time,
205 end_date_time,
206 timezone_id,
207 activity_type_code,
208 activity_id,
209 arc_marketing_medium_from,
210 marketing_medium_id,
211 custom_setup_id,
212 triggerable_flag,
213 trigger_id,
214 notify_user_id,
215 approver_user_id,
216 owner_user_id,
217 active_flag,
218 cover_letter_id,
219 reply_to_mail,
220 mail_sender_name,
221 mail_subject,
222 from_fax_no,
223 accounts_closed_flag,
224 org_id,
225 objective_code,
226 country_id,
227 campaign_calendar,
228 start_period_name,
229 end_period_name,
230 priority,
231 workflow_item_key,
232 transaction_currency_code,
233 functional_currency_code,
234 budget_amount_tc,
235 budget_amount_fc,
236 language_code,
237 task_id,
238 related_event_from,
239 related_event_id,
240 attribute_category,
241 attribute1,
242 attribute2,
243 attribute3,
244 attribute4,
245 attribute5,
246 attribute6,
247 attribute7,
248 attribute8,
249 attribute9,
250 attribute10,
251 attribute11,
252 attribute12,
253 attribute13,
254 attribute14,
255 attribute15,
256 activity_attribute_category,
257 activity_attribute1,
258 activity_attribute2,
259 activity_attribute3,
260 activity_attribute4,
261 activity_attribute5,
262 activity_attribute6,
263 activity_attribute7,
264 activity_attribute8,
265 activity_attribute9,
266 activity_attribute10,
267 activity_attribute11,
268 activity_attribute12,
269 activity_attribute13,
270 activity_attribute14,
271 activity_attribute15,
272 -- removed by soagrawa on 24-sep-2001
273 -- security_group_id,
274 query_id,
275 include_content_flag,
276 content_type,
277 test_email_address,
278 --added by anchaudh on 30-apr-2003
279 trig_repeat_flag,
280 tgrp_exclude_prev_flag,
281 --added by anchaudh on 06-may-2003
282 orig_csch_id,
283 cover_letter_version,
284 --added by dbiswas on 12-aug-2003
285 usage,
286 purpose,
287 last_activation_date,
288 sales_methodology_id,
289 printer_address,
290 notify_on_activation_flag,
291 sender_display_name,
292 delivery_mode
293 ) VALUES (
294 DECODE( px_schedule_id, FND_API.g_miss_num, NULL, px_schedule_id),
295 -- last updated by, created by, last update login modified by soagrawa on 12-jun-2002 for ATT bug 2376329
296 DECODE( p_last_update_date, FND_API.g_miss_date, SYSDATE, p_last_update_date),
297 DECODE( p_last_updated_by, FND_API.g_miss_num, FND_GLOBAL.USER_ID, p_last_updated_by),
298 DECODE( p_creation_date, FND_API.g_miss_date, SYSDATE, p_creation_date),
299 DECODE( p_created_by, FND_API.g_miss_num, FND_GLOBAL.USER_ID, p_created_by),
300 DECODE( p_last_update_login, FND_API.g_miss_num, FND_GLOBAL.LOGIN_ID, p_last_update_login),
301 /*
302 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
303 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
304 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
305 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
306 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
307 */
308 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
309 DECODE( p_campaign_id, FND_API.g_miss_num, NULL, p_campaign_id),
310 DECODE( p_user_status_id, FND_API.g_miss_num, NULL, p_user_status_id),
311 DECODE( p_status_code, FND_API.g_miss_char, NULL, p_status_code),
312 DECODE( p_status_date, FND_API.g_miss_date, SYSDATE, p_status_date),
313 DECODE( p_source_code, FND_API.g_miss_char, NULL, p_source_code),
314 DECODE( p_use_parent_code_flag, FND_API.g_miss_char, 'N', p_use_parent_code_flag),
315 -- date stuff modified by ptendulk on 26-sep-2001
316 -- as the time was not being saved from the api.
317 --DECODE( p_start_date_time, FND_API.g_miss_date, NULL, TO_DATE(TO_CHAR(p_start_date_time,'DDMMRRRR HH:MI:SS PM'),'DDMMRRRR HH:MI:SS PM')),
318 --TO_DATE(TO_CHAR(p_start_date_time,'DDMMRRRR HH:MI:SS PM'),'DDMMRRRR HH:MI:SS PM'),
319 l_start_date,
320 --DECODE( p_end_date_time, FND_API.g_miss_date, NULL, p_end_date_time),
321 l_end_date,
322 DECODE( p_timezone_id, FND_API.g_miss_num, NULL, p_timezone_id),
323 DECODE( p_activity_type_code, FND_API.g_miss_char, NULL, p_activity_type_code),
324 DECODE( p_activity_id, FND_API.g_miss_num, NULL, p_activity_id),
325 DECODE( p_arc_marketing_medium_from, FND_API.g_miss_char, NULL, p_arc_marketing_medium_from),
326 DECODE( p_marketing_medium_id, FND_API.g_miss_num, NULL, p_marketing_medium_id),
327 DECODE( p_custom_setup_id, FND_API.g_miss_num, NULL, p_custom_setup_id),
328 DECODE( p_triggerable_flag, FND_API.g_miss_char, 'N', p_triggerable_flag),
329 DECODE( p_trigger_id, FND_API.g_miss_num, NULL, p_trigger_id),
330 DECODE( p_notify_user_id, FND_API.g_miss_num, NULL, p_notify_user_id),
331 DECODE( p_approver_user_id, FND_API.g_miss_num, NULL, p_approver_user_id),
332 DECODE( p_owner_user_id, FND_API.g_miss_num, NULL, p_owner_user_id),
333 DECODE( p_active_flag, FND_API.g_miss_char, 'Y', p_active_flag),
334 DECODE( p_cover_letter_id, FND_API.g_miss_num, NULL, p_cover_letter_id),
338 DECODE( p_from_fax_no, FND_API.g_miss_char, NULL, p_from_fax_no),
335 DECODE( p_reply_to_mail, FND_API.g_miss_char, NULL, p_reply_to_mail),
336 DECODE( p_mail_sender_name, FND_API.g_miss_char, NULL, p_mail_sender_name),
337 DECODE( p_mail_subject, FND_API.g_miss_char, NULL, p_mail_subject),
339 DECODE( p_accounts_closed_flag, FND_API.g_miss_char, 'N', p_accounts_closed_flag),
340 DECODE( px_org_id, FND_API.g_miss_num, NULL, px_org_id),
341 DECODE( p_objective_code, FND_API.g_miss_char, NULL, p_objective_code),
342 DECODE( p_country_id, FND_API.g_miss_num, NULL, p_country_id),
343 DECODE( p_campaign_calendar, FND_API.g_miss_char, NULL, p_campaign_calendar),
344 DECODE( p_start_period_name, FND_API.g_miss_char, NULL, p_start_period_name),
345 DECODE( p_end_period_name, FND_API.g_miss_char, NULL, p_end_period_name),
346 DECODE( p_priority, FND_API.g_miss_char, NULL, p_priority),
347 DECODE( p_workflow_item_key, FND_API.g_miss_char, NULL, p_workflow_item_key),
348 DECODE( p_transaction_currency_code, FND_API.g_miss_char, NULL, p_transaction_currency_code),
349 DECODE( p_functional_currency_code, FND_API.g_miss_char, NULL, p_functional_currency_code),
350 DECODE( p_budget_amount_tc ,FND_API.g_miss_num,NULL,p_budget_amount_tc),
351 DECODE( p_budget_amount_fc ,FND_API.g_miss_num,NULL,p_budget_amount_fc),
352 DECODE( p_language_code ,FND_API.g_miss_char,NULL,p_language_code),
353 DECODE( p_task_id ,FND_API.g_miss_num,NULL,p_task_id),
354 DECODE( p_related_event_from ,FND_API.g_miss_char,NULL,p_related_event_from),
355 DECODE( p_related_event_id ,FND_API.g_miss_num,NULL,p_related_event_id),
356 DECODE( p_attribute_category, FND_API.g_miss_char, NULL, p_attribute_category),
357 DECODE( p_attribute1, FND_API.g_miss_char, NULL, p_attribute1),
358 DECODE( p_attribute2, FND_API.g_miss_char, NULL, p_attribute2),
359 DECODE( p_attribute3, FND_API.g_miss_char, NULL, p_attribute3),
360 DECODE( p_attribute4, FND_API.g_miss_char, NULL, p_attribute4),
361 DECODE( p_attribute5, FND_API.g_miss_char, NULL, p_attribute5),
362 DECODE( p_attribute6, FND_API.g_miss_char, NULL, p_attribute6),
363 DECODE( p_attribute7, FND_API.g_miss_char, NULL, p_attribute7),
364 DECODE( p_attribute8, FND_API.g_miss_char, NULL, p_attribute8),
365 DECODE( p_attribute9, FND_API.g_miss_char, NULL, p_attribute9),
366 DECODE( p_attribute10, FND_API.g_miss_char, NULL, p_attribute10),
367 DECODE( p_attribute11, FND_API.g_miss_char, NULL, p_attribute11),
368 DECODE( p_attribute12, FND_API.g_miss_char, NULL, p_attribute12),
369 DECODE( p_attribute13, FND_API.g_miss_char, NULL, p_attribute13),
370 DECODE( p_attribute14, FND_API.g_miss_char, NULL, p_attribute14),
371 DECODE( p_attribute15, FND_API.g_miss_char, NULL, p_attribute15),
372 DECODE( p_activity_attribute_category, FND_API.g_miss_char, NULL, p_activity_attribute_category),
373 DECODE( p_activity_attribute1, FND_API.g_miss_char, NULL, p_activity_attribute1),
374 DECODE( p_activity_attribute2, FND_API.g_miss_char, NULL, p_activity_attribute2),
375 DECODE( p_activity_attribute3, FND_API.g_miss_char, NULL, p_activity_attribute3),
376 DECODE( p_activity_attribute4, FND_API.g_miss_char, NULL, p_activity_attribute4),
377 DECODE( p_activity_attribute5, FND_API.g_miss_char, NULL, p_activity_attribute5),
378 DECODE( p_activity_attribute6, FND_API.g_miss_char, NULL, p_activity_attribute6),
379 DECODE( p_activity_attribute7, FND_API.g_miss_char, NULL, p_activity_attribute7),
380 DECODE( p_activity_attribute8, FND_API.g_miss_char, NULL, p_activity_attribute8),
381 DECODE( p_activity_attribute9, FND_API.g_miss_char, NULL, p_activity_attribute9),
382 DECODE( p_activity_attribute10, FND_API.g_miss_char, NULL, p_activity_attribute10),
383 DECODE( p_activity_attribute11, FND_API.g_miss_char, NULL, p_activity_attribute11),
384 DECODE( p_activity_attribute12, FND_API.g_miss_char, NULL, p_activity_attribute12),
385 DECODE( p_activity_attribute13, FND_API.g_miss_char, NULL, p_activity_attribute13),
386 DECODE( p_activity_attribute14, FND_API.g_miss_char, NULL, p_activity_attribute14),
387 DECODE( p_activity_attribute15, FND_API.g_miss_char, NULL, p_activity_attribute15),
388 -- removed by soagrawa on 24-sep-2001
389 -- DECODE( p_security_group_id, FND_API.g_miss_num, NULL, p_security_group_id),
390 DECODE( p_query_id, FND_API.g_miss_num, NULL, p_query_id),
391 DECODE( p_include_content_flag, FND_API.g_miss_char,NULL,p_include_content_flag),
392 DECODE( p_content_type, FND_API.g_miss_char,NULL,p_content_type),
393 DECODE( p_test_email_address, FND_API.g_miss_char,NULL,p_test_email_address),
394 --added by anchaudh on 30-apr-2003
395 DECODE( p_trig_repeat_flag, FND_API.g_miss_char, 'N',p_trig_repeat_flag),
396 DECODE( p_tgrp_exclude_prev_flag, FND_API.g_miss_char, 'N', p_tgrp_exclude_prev_flag),
397 --added by anchaudh on 06-may-2003
398 DECODE( p_orig_csch_id, FND_API.g_miss_num, NULL, p_orig_csch_id),
399 DECODE( p_cover_letter_version, FND_API.g_miss_num, NULL, p_cover_letter_version),
400 DECODE( p_usage, FND_API.g_miss_char,NULL,p_usage),
401 DECODE( p_purpose, FND_API.g_miss_char,NULL,p_purpose),
402 DECODE( p_last_activation_date, FND_API.g_miss_date,NULL,p_last_activation_date),
403 DECODE( p_sales_methodology_id, FND_API.g_miss_num,NULL,p_sales_methodology_id),
407 ,DECODE( p_delivery_mode, FND_API.g_miss_char, NULL, p_delivery_mode)
404 DECODE( p_printer_address, FND_API.g_miss_char,NULL,p_printer_address),
405 DECODE( p_notify_on_activation_flag, FND_API.g_miss_char,NULL,p_notify_on_activation_flag),
406 DECODE( p_sender_display_name, FND_API.g_miss_char,NULL,p_sender_display_name)--anchaudh
408 );
409
410 INSERT INTO AMS_CAMPAIGN_SCHEDULES_TL(
411 schedule_id,
412 language,
413 last_update_date,
414 last_updated_by,
415 creation_date,
416 created_by,
417 last_update_login,
418 source_lang,
419 schedule_name,
420 description,
421 -- removed by soagrawa on 24-sep-2001
422 -- security_group_id,
423 greeting_text,
424 footer_text
425 )
426 SELECT
427 DECODE( px_schedule_id, FND_API.g_miss_num, NULL, px_schedule_id),
428 l.language_code,
429 SYSDATE,
430 FND_GLOBAL.user_id,
431 SYSDATE,
432 FND_GLOBAL.user_id,
433 FND_GLOBAL.conc_login_id,
434 USERENV('LANG'),
435 DECODE( p_schedule_name, FND_API.g_miss_char, NULL, p_schedule_name),
436 DECODE( p_schedule_description, FND_API.g_miss_char, NULL, p_schedule_description),
437 -- removed by soagrawa on 24-sep-2001
438 -- DECODE( p_security_group_id, FND_API.g_miss_num, NULL, p_security_group_id),
439 DECODE( p_greeting_text, FND_API.g_miss_char, NULL, p_greeting_text),
440 DECODE( p_footer_text, FND_API.g_miss_char, NULL, p_footer_text)
441 FROM fnd_languages l
442 WHERE l.installed_flag IN ('I','B')
443 AND NOT EXISTS(
444 SELECT NULL
445 FROM ams_campaign_schedules_tl t
446 WHERE t.schedule_id = DECODE( px_schedule_id, FND_API.g_miss_num, NULL, px_schedule_id)
447 AND t.language = l.language_code ) ;
448
449
450
451 END Insert_Row;
452
453
454 -- ===============================================================
455 -- Start of Comments
456 -- Procedure name
457 -- Update_Row
458 -- Purpose
459 -- Table api to update campaign Schedules.
460 -- History
461 -- 22-Jan-2000 ptendulk Created.
462 -- 26-sep-2001 soagrawa Modified updating start date and end date
463 -- 26-dec-2001 aranka Modified creation_Date and created_by
464 -- 31-jan-2002 soagrawa Modified last updated by for bug# 2160058, #2204087
465 -- 27-jun-2003 anchaudh Added four more columns(trig_repeat_flag,trgp_exclude_prev,orig_csch_id,cover_letter_version) to be inserted and updated into the table.
466 -- 12-aug-2003 dbiswas Added 3 new columns(usage,purpose,last_activation_date) to be inserted and updated into the table.
467 -- 25-aug-2003 dbiswas Added 1 new column(sales_methodology_id) to be inserted and updated into the table.
468 -- 27-jul-2005 dbiswas Added 1 new column(notify_on_activation_flag) to be inserted and updated into the table.
469 -- 29-May-2006 srivikri Added column Delivery_mode
470 -- NOTE
471 --
472 -- End of Comments
473 -- ===============================================================
474 PROCEDURE Update_Row(
475 p_schedule_id NUMBER,
476 p_last_update_date DATE,
477 p_last_updated_by NUMBER,
478 p_creation_date DATE,
479 p_created_by NUMBER,
480 p_last_update_login NUMBER,
481 p_object_version_number NUMBER,
482 p_campaign_id NUMBER,
483 p_user_status_id NUMBER,
484 p_status_code VARCHAR2,
485 p_status_date DATE,
486 p_source_code VARCHAR2,
487 p_use_parent_code_flag VARCHAR2,
488 p_start_date_time DATE,
489 p_end_date_time DATE,
490 p_timezone_id NUMBER,
491 p_activity_type_code VARCHAR2,
492 p_activity_id NUMBER,
493 p_arc_marketing_medium_from VARCHAR2,
494 p_marketing_medium_id NUMBER,
495 p_custom_setup_id NUMBER,
496 p_triggerable_flag VARCHAR2,
497 p_trigger_id NUMBER,
498 p_notify_user_id NUMBER,
499 p_approver_user_id NUMBER,
500 p_owner_user_id NUMBER,
501 p_active_flag VARCHAR2,
502 p_cover_letter_id NUMBER,
503 p_reply_to_mail VARCHAR2,
504 p_mail_sender_name VARCHAR2,
505 p_mail_subject VARCHAR2,
506 p_from_fax_no VARCHAR2,
507 p_accounts_closed_flag VARCHAR2,
508 p_org_id NUMBER,
509 p_objective_code VARCHAR2,
510 p_country_id NUMBER,
511 p_campaign_calendar VARCHAR2,
512 p_start_period_name VARCHAR2,
513 p_end_period_name VARCHAR2,
514 p_priority VARCHAR2,
515 p_workflow_item_key VARCHAR2,
516 p_transaction_currency_code VARCHAR2,
517 p_functional_currency_code VARCHAR2,
518 p_budget_amount_tc NUMBER,
519 p_budget_amount_fc NUMBER,
520 p_language_code VARCHAR2,
521 p_task_id NUMBER,
522 p_related_event_from VARCHAR2,
523 p_related_event_id NUMBER,
524 p_attribute_category VARCHAR2,
528 p_attribute4 VARCHAR2,
525 p_attribute1 VARCHAR2,
526 p_attribute2 VARCHAR2,
527 p_attribute3 VARCHAR2,
529 p_attribute5 VARCHAR2,
530 p_attribute6 VARCHAR2,
531 p_attribute7 VARCHAR2,
532 p_attribute8 VARCHAR2,
533 p_attribute9 VARCHAR2,
534 p_attribute10 VARCHAR2,
535 p_attribute11 VARCHAR2,
536 p_attribute12 VARCHAR2,
537 p_attribute13 VARCHAR2,
538 p_attribute14 VARCHAR2,
539 p_attribute15 VARCHAR2,
540 p_activity_attribute_category VARCHAR2,
541 p_activity_attribute1 VARCHAR2,
542 p_activity_attribute2 VARCHAR2,
543 p_activity_attribute3 VARCHAR2,
544 p_activity_attribute4 VARCHAR2,
545 p_activity_attribute5 VARCHAR2,
546 p_activity_attribute6 VARCHAR2,
547 p_activity_attribute7 VARCHAR2,
548 p_activity_attribute8 VARCHAR2,
549 p_activity_attribute9 VARCHAR2,
550 p_activity_attribute10 VARCHAR2,
551 p_activity_attribute11 VARCHAR2,
552 p_activity_attribute12 VARCHAR2,
553 p_activity_attribute13 VARCHAR2,
554 p_activity_attribute14 VARCHAR2,
555 p_activity_attribute15 VARCHAR2,
556 -- removed by soagrawa on 24-sep-2001
557 -- p_security_group_id NUMBER ,
558 p_query_id NUMBER,
559 p_include_content_flag VARCHAR2,
560 p_content_type VARCHAR2,
561 p_test_email_address VARCHAR2,
562 p_schedule_name VARCHAR2,
563 p_schedule_description VARCHAR2,
564 p_greeting_text VARCHAR2,
565 p_footer_text VARCHAR2,
566 --added by anchaudh on 30-apr-2003
567 p_trig_repeat_flag VARCHAR2,
568 p_tgrp_exclude_prev_flag VARCHAR2,
569 --added by anchaudh on 06-may-2003
570 p_orig_csch_id NUMBER,
571 p_cover_letter_version NUMBER,
572 --added by dbiswas on 12-aug-2003
573 p_usage VARCHAR2,
574 p_purpose VARCHAR2,
575 p_last_activation_date DATE,
576 p_sales_methodology_id NUMBER,
577 p_printer_address VARCHAR2,
578 --added by dbiswas on 27-jul-2005
579 p_notify_on_activation_flag VARCHAR2,
580 --added by anchaudh on 01-feb-2006
581 p_sender_display_name VARCHAR2,
582 p_delivery_mode VARCHAR2
583 )
584 IS
585 -- date variables added by soagrawa on 26-sep-2001
586 l_start_date DATE ;
587 l_end_date DATE ;
588
589 BEGIN
590
591 -- added by soagrawa on 26-sep-2001
592 -- for storing time correctly in the database
593 -- DBMS_OUTPUT.PUT_LINE('Start date : '||TO_CHAR(p_start_date_time,'DDMMYY HH:MI:SS PM'));
594
595 IF p_start_date_time = FND_API.g_miss_date THEN
596 l_start_date := NULL ;
597 ELSE
598 l_start_date := p_start_date_time ;
599 END IF ;
600
601 IF p_end_date_time = FND_API.g_miss_date THEN
602 l_end_date := null ;
603 ELSE
604 l_end_date := p_end_date_time ;
605 END IF ;
606
607 -- end soagrawa 26-sep-2001
608 -- Debug Message Commented by ptendulk Bug 2767243
609 -- ams_utility_pvt.debug_message('Transaction currency :: '||p_transaction_currency_code);
610 UPDATE ams_campaign_schedules_b
611 SET schedule_id = DECODE( p_schedule_id, FND_API.g_miss_num, schedule_id, p_schedule_id),
612 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
613 -- last update date modified by soagrawa on 31-jan-2002 for bug# 2160058, 2204087
614 last_updated_by = FND_GLOBAL.USER_ID,
615 -- last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
616 -- removed by aranka on 26-dec-2001
617 -- creation_date = DECODE( p_creation_date, FND_API.g_miss_date, creation_date, p_creation_date),
618 -- created_by = DECODE( p_created_by, FND_API.g_miss_num, created_by, p_created_by),
619 -- removed by aranka on 26-dec-2001
620 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
621 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number+ 1),
622 campaign_id = DECODE( p_campaign_id, FND_API.g_miss_num, campaign_id, p_campaign_id),
623 user_status_id = DECODE( p_user_status_id, FND_API.g_miss_num, user_status_id, p_user_status_id),
624 status_code = DECODE( p_status_code, FND_API.g_miss_char, status_code, p_status_code),
625 status_date = DECODE( p_status_date, FND_API.g_miss_date, status_date, p_status_date),
626 source_code = DECODE( p_source_code, FND_API.g_miss_char, source_code, p_source_code),
627 use_parent_code_flag = DECODE( p_use_parent_code_flag, FND_API.g_miss_char, use_parent_code_flag, p_use_parent_code_flag),
628 -- date stuff modified by soagrawa on 26-sep-2001 to save time as well, decode messes time up
629 -- start_date_time = DECODE( p_start_date_time, FND_API.g_miss_date, start_date_time, p_start_date_time),
630 -- end_date_time = DECODE( p_end_date_time, FND_API.g_miss_date, end_date_time, p_end_date_time),
631 start_date_time = l_start_date,
632 end_date_time = l_end_date,
636 arc_marketing_medium_from = DECODE( p_arc_marketing_medium_from, FND_API.g_miss_char, arc_marketing_medium_from, p_arc_marketing_medium_from),
633 timezone_id = DECODE( p_timezone_id, FND_API.g_miss_num, timezone_id, p_timezone_id),
634 activity_type_code = DECODE( p_activity_type_code, FND_API.g_miss_char, activity_type_code, p_activity_type_code),
635 activity_id = DECODE( p_activity_id, FND_API.g_miss_num, activity_id, p_activity_id),
637 marketing_medium_id = DECODE( p_marketing_medium_id, FND_API.g_miss_num, marketing_medium_id, p_marketing_medium_id),
638 custom_setup_id = DECODE( p_custom_setup_id, FND_API.g_miss_num, custom_setup_id, p_custom_setup_id),
639 triggerable_flag = DECODE( p_triggerable_flag, FND_API.g_miss_char, triggerable_flag, p_triggerable_flag),
640 trigger_id = DECODE( p_trigger_id, FND_API.g_miss_num, trigger_id, p_trigger_id),
641 notify_user_id = DECODE( p_notify_user_id, FND_API.g_miss_num, notify_user_id, p_notify_user_id),
642 approver_user_id = DECODE( p_approver_user_id, FND_API.g_miss_num, approver_user_id, p_approver_user_id),
643 owner_user_id = DECODE( p_owner_user_id, FND_API.g_miss_num, owner_user_id, p_owner_user_id),
644 active_flag = DECODE( p_active_flag, FND_API.g_miss_char, active_flag, p_active_flag),
645 cover_letter_id = DECODE( p_cover_letter_id, FND_API.g_miss_num, cover_letter_id, p_cover_letter_id),
646 reply_to_mail = DECODE( p_reply_to_mail, FND_API.g_miss_char, reply_to_mail, p_reply_to_mail),
647 mail_sender_name = DECODE( p_mail_sender_name, FND_API.g_miss_char, mail_sender_name, p_mail_sender_name),
648 mail_subject = DECODE( p_mail_subject, FND_API.g_miss_char, mail_subject, p_mail_subject),
649 from_fax_no = DECODE( p_from_fax_no, FND_API.g_miss_char, from_fax_no, p_from_fax_no),
650 accounts_closed_flag = DECODE( p_accounts_closed_flag, FND_API.g_miss_char, accounts_closed_flag, p_accounts_closed_flag),
651 org_id = DECODE( p_org_id, FND_API.g_miss_num, org_id, p_org_id),
652 objective_code = DECODE( p_objective_code, FND_API.g_miss_char, objective_code, p_objective_code),
653 country_id = DECODE( p_country_id, FND_API.g_miss_num, country_id, p_country_id),
654 campaign_calendar = DECODE( p_campaign_calendar, FND_API.g_miss_char, campaign_calendar, p_campaign_calendar),
655 start_period_name = DECODE( p_start_period_name, FND_API.g_miss_char, start_period_name, p_start_period_name),
656 end_period_name = DECODE( p_end_period_name, FND_API.g_miss_char, end_period_name, p_end_period_name),
657 priority = DECODE( p_priority, FND_API.g_miss_char, priority, p_priority),
658 workflow_item_key = DECODE( p_workflow_item_key, FND_API.g_miss_char, workflow_item_key, p_workflow_item_key),
659 transaction_currency_code = DECODE(p_transaction_currency_code,FND_API.g_miss_char,transaction_currency_code,p_transaction_currency_code),
660 functional_currency_code = DECODE(p_functional_currency_code,FND_API.g_miss_char,functional_currency_code,p_functional_currency_code),
661 budget_amount_tc = DECODE( p_budget_amount_tc ,FND_API.g_miss_num,budget_amount_tc,p_budget_amount_tc),
662 budget_amount_fc = DECODE( p_budget_amount_fc ,FND_API.g_miss_num,budget_amount_fc,p_budget_amount_fc),
663 language_code = DECODE( p_language_code ,FND_API.g_miss_char,language_code,p_language_code),
664 task_id = DECODE( p_task_id ,FND_API.g_miss_num,task_id,p_task_id),
665 related_event_from = DECODE( p_related_event_from ,FND_API.g_miss_char,related_event_from,p_related_event_from),
666 related_event_id = DECODE( p_related_event_id ,FND_API.g_miss_num,related_event_id,p_related_event_id),
667 attribute_category = DECODE( p_attribute_category, FND_API.g_miss_char, attribute_category, p_attribute_category),
668 attribute1 = DECODE( p_attribute1, FND_API.g_miss_char, attribute1, p_attribute1),
669 attribute2 = DECODE( p_attribute2, FND_API.g_miss_char, attribute2, p_attribute2),
670 attribute3 = DECODE( p_attribute3, FND_API.g_miss_char, attribute3, p_attribute3),
671 attribute4 = DECODE( p_attribute4, FND_API.g_miss_char, attribute4, p_attribute4),
672 attribute5 = DECODE( p_attribute5, FND_API.g_miss_char, attribute5, p_attribute5),
673 attribute6 = DECODE( p_attribute6, FND_API.g_miss_char, attribute6, p_attribute6),
674 attribute7 = DECODE( p_attribute7, FND_API.g_miss_char, attribute7, p_attribute7),
675 attribute8 = DECODE( p_attribute8, FND_API.g_miss_char, attribute8, p_attribute8),
676 attribute9 = DECODE( p_attribute9, FND_API.g_miss_char, attribute9, p_attribute9),
677 attribute10 = DECODE( p_attribute10, FND_API.g_miss_char, attribute10, p_attribute10),
678 attribute11 = DECODE( p_attribute11, FND_API.g_miss_char, attribute11, p_attribute11),
679 attribute12 = DECODE( p_attribute12, FND_API.g_miss_char, attribute12, p_attribute12),
680 attribute13 = DECODE( p_attribute13, FND_API.g_miss_char, attribute13, p_attribute13),
681 attribute14 = DECODE( p_attribute14, FND_API.g_miss_char, attribute14, p_attribute14),
682 attribute15 = DECODE( p_attribute15, FND_API.g_miss_char, attribute15, p_attribute15),
683 activity_attribute_category = DECODE( p_activity_attribute_category, FND_API.g_miss_char, activity_attribute_category, p_activity_attribute_category),
684 activity_attribute1 = DECODE( p_activity_attribute1, FND_API.g_miss_char, activity_attribute1, p_activity_attribute1),
688 activity_attribute5 = DECODE( p_activity_attribute5, FND_API.g_miss_char, activity_attribute5, p_activity_attribute5),
685 activity_attribute2 = DECODE( p_activity_attribute2, FND_API.g_miss_char, activity_attribute2, p_activity_attribute2),
686 activity_attribute3 = DECODE( p_activity_attribute3, FND_API.g_miss_char, activity_attribute3, p_activity_attribute3),
687 activity_attribute4 = DECODE( p_activity_attribute4, FND_API.g_miss_char, activity_attribute4, p_activity_attribute4),
689 activity_attribute6 = DECODE( p_activity_attribute6, FND_API.g_miss_char, activity_attribute6, p_activity_attribute6),
690 activity_attribute7 = DECODE( p_activity_attribute7, FND_API.g_miss_char, activity_attribute7, p_activity_attribute7),
691 activity_attribute8 = DECODE( p_activity_attribute8, FND_API.g_miss_char, activity_attribute8, p_activity_attribute8),
692 activity_attribute9 = DECODE( p_activity_attribute9, FND_API.g_miss_char, activity_attribute9, p_activity_attribute9),
693 activity_attribute10 = DECODE( p_activity_attribute10, FND_API.g_miss_char, activity_attribute10, p_activity_attribute10),
694 activity_attribute11 = DECODE( p_activity_attribute11, FND_API.g_miss_char, activity_attribute11, p_activity_attribute11),
695 activity_attribute12 = DECODE( p_activity_attribute12, FND_API.g_miss_char, activity_attribute12, p_activity_attribute12),
696 activity_attribute13 = DECODE( p_activity_attribute13, FND_API.g_miss_char, activity_attribute13, p_activity_attribute13),
697 activity_attribute14 = DECODE( p_activity_attribute14, FND_API.g_miss_char, activity_attribute14, p_activity_attribute14),
698 activity_attribute15 = DECODE( p_activity_attribute15, FND_API.g_miss_char, activity_attribute15, p_activity_attribute15),
699 -- removed by soagrawa on 24-sep-2001
700 -- security_group_id = DECODE( p_security_group_id, FND_API.g_miss_num, security_group_id, p_security_group_id),
701 query_id = DECODE( p_query_id, FND_API.g_miss_num, query_id, p_query_id),
702 include_content_flag = DECODE( p_include_content_flag, FND_API.g_miss_char, include_content_flag, p_include_content_flag),
703 content_type = DECODE( p_content_type, FND_API.g_miss_char, content_type, p_content_type),
704 test_email_address = DECODE( p_test_email_address, FND_API.g_miss_char, test_email_address, p_test_email_address),
705 --added by anchaudh on 30-apr-2003
706 trig_repeat_flag = DECODE( p_trig_repeat_flag, FND_API.g_miss_char,trig_repeat_flag ,p_trig_repeat_flag),
707 tgrp_exclude_prev_flag = DECODE( p_tgrp_exclude_prev_flag, FND_API.g_miss_char, tgrp_exclude_prev_flag, p_tgrp_exclude_prev_flag),
708 --added by anchaudh on 06-may-2003
709 orig_csch_id = DECODE( p_orig_csch_id, FND_API.g_miss_num, orig_csch_id, p_orig_csch_id),
710 cover_letter_version = DECODE( p_cover_letter_version, FND_API.g_miss_num, cover_letter_version, p_cover_letter_version),
711 --added by dbiswas on 12-aug-2003
712 usage = DECODE( p_usage, FND_API.g_miss_char,usage ,p_usage),
713 purpose = DECODE( p_purpose, FND_API.g_miss_char, purpose, p_purpose),
714 last_activation_date = DECODE( p_last_activation_date, FND_API.g_miss_date, last_activation_date, p_last_activation_date),
715 sales_methodology_id = DECODE( p_sales_methodology_id, FND_API.g_miss_num, sales_methodology_id, p_sales_methodology_id),
716 printer_address = DECODE( p_printer_address, FND_API.g_miss_char, printer_address, p_printer_address),
717 notify_on_activation_flag = DECODE( p_notify_on_activation_flag, FND_API.g_miss_char, notify_on_activation_flag, p_notify_on_activation_flag),
718 sender_display_name = DECODE( p_sender_display_name, FND_API.g_miss_char, sender_display_name, p_sender_display_name),
719 delivery_mode = DECODE( p_delivery_mode, FND_API.g_miss_char, delivery_mode, p_delivery_mode)
720 WHERE schedule_id = p_schedule_id
721 AND object_version_number = p_object_version_number;
722 -- Debug Message Commented by ptendulk Bug 2767243
723 -- AMS_UTILITY_PVT.debug_message('Start Update Table Handler');
724 IF (SQL%NOTFOUND) THEN
725 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
726 END IF;
727
728 UPDATE ams_campaign_schedules_tl
729 SET schedule_name = DECODE(p_schedule_name,FND_API.g_miss_char,schedule_name,p_schedule_name),
730 description = DECODE(p_schedule_description,FND_API.g_miss_char,description,p_schedule_description),
731 greeting_text = DECODE(p_greeting_text,FND_API.g_miss_char,greeting_text,p_greeting_text),
732 footer_text = DECODE(p_footer_text,FND_API.g_miss_char,footer_text,p_footer_text),
733 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
734 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
735 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
736 source_lang = USERENV('LANG')
737 WHERE schedule_id = p_schedule_id
738 AND USERENV('LANG') IN (language, source_lang);
739
740 IF (SQL%NOTFOUND) THEN
741 RAISE FND_API.g_exc_error;
742 END IF;
743
744 END Update_Row;
745
746 -- ===============================================================
747 -- Start of Comments
748 -- Procedure name
749 -- Delete_Row
750 -- Purpose
751 -- Table api to Delete campaign Schedules.
752 -- History
753 -- 22-Jan-2000 ptendulk Created.
754 -- NOTE
755 --
756 -- End of Comments
760 IS
757 -- ===============================================================
758 PROCEDURE Delete_Row(
759 p_schedule_id NUMBER)
761 BEGIN
762
763 DELETE FROM ams_campaign_schedules_b
764 WHERE schedule_id = p_schedule_id;
765 If (SQL%NOTFOUND) THEN
766 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
767 End If;
768
769 END Delete_Row ;
770
771 -- ===============================================================
772 -- Start of Comments
773 -- Procedure name
774 -- Lock_Row
775 -- Purpose
776 -- Table api to lock campaign Schedules.
777 -- History
778 -- 22-Jan-2000 ptendulk Created.
779 -- NOTE
780 --
781 -- End of Comments
782 -- ===============================================================
783 PROCEDURE Lock_Row(
784 p_schedule_id NUMBER
785 )
786
787 IS
788 CURSOR C IS
789 SELECT *
790 FROM ams_campaign_schedules_b
791 WHERE schedule_id = p_schedule_id
792 FOR UPDATE OF schedule_id NOWAIT;
793 Recinfo C%ROWTYPE;
794 BEGIN
795 OPEN c;
796 FETCH c INTO Recinfo;
797 If (c%NOTFOUND) THEN
798 CLOSE c;
799 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
800 APP_EXCEPTION.RAISE_EXCEPTION;
801 END IF;
802 CLOSE C;
803
807 -- ===============================================================
804 END Lock_Row;
805
806
808 -- Start of Comments
809 -- Procedure name
810 -- ADD_LANGUAGE
811 -- Purpose
812 --
813 -- History
814 -- 16-apr-2002 soagrawa Created. Refer to bug# 2323843.
815 -- NOTE
816 --
817 -- End of Comments
818 -- ===============================================================
819
820
821
822 procedure ADD_LANGUAGE
823 is
824 begin
825 delete from ams_campaign_schedules_tl T
826 where not exists
827 (select NULL
828 from ams_campaign_schedules_b B
829 where B.schedule_id = T.schedule_ID
830 );
831
832 update ams_campaign_schedules_tl T set (
833 schedule_name
834 , DESCRIPTION
835 , greeting_text
836 , footer_text
837 ) = (select
838 B.schedule_name
839 , B.DESCRIPTION
840 , B.greeting_text
841 , B.footer_text
842 from ams_campaign_schedules_tl B
843 where B.schedule_id = T.schedule_id
844 and B.LANGUAGE = T.SOURCE_LANG)
845 where (
846 T.schedule_id,
847 T.LANGUAGE
848 ) in (select
849 SUBT.schedule_id,
850 SUBT.LANGUAGE
851 from ams_campaign_schedules_tl SUBB, ams_campaign_schedules_tl SUBT
852 where SUBB.schedule_id = SUBT.schedule_id
853 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
854 and (SUBB.schedule_name <> SUBT.schedule_name
855 or SUBB.greeting_text <> SUBT.greeting_text
856 or (SUBB.greeting_text is null and SUBT.greeting_text is not null)
857 or (SUBB.greeting_text is not null and SUBT.greeting_text is null)
858 or SUBB.footer_text <> SUBT.footer_text
859 or (SUBB.footer_text is null and SUBT.footer_text is not null)
860 or (SUBB.footer_text is not null and SUBT.footer_text is null)
861 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
862 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
863 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
864 ));
865
866 insert into ams_campaign_schedules_tl (
867 schedule_id,
868 CREATION_DATE,
869 CREATED_BY,
870 LAST_UPDATE_DATE,
871 LAST_UPDATED_BY,
872 LAST_UPDATE_LOGIN,
873 schedule_name,
874 greeting_text,
875 footer_text,
876 DESCRIPTION,
877 LANGUAGE,
878 SOURCE_LANG
879 ) select
880 B.schedule_id,
881 B.CREATION_DATE,
882 B.CREATED_BY,
883 B.LAST_UPDATE_DATE,
884 B.LAST_UPDATED_BY,
885 B.LAST_UPDATE_LOGIN,
886 B.schedule_name,
887 B.greeting_text,
888 B.footer_text,
889 B.DESCRIPTION,
890 L.LANGUAGE_CODE,
891 B.SOURCE_LANG
892 from ams_campaign_schedules_tl B, FND_LANGUAGES L
893 where L.INSTALLED_FLAG in ('I', 'B')
894 and B.LANGUAGE = userenv('LANG')
895 and not exists
896 (select NULL
897 from ams_campaign_schedules_tl T
898 where T.schedule_id = B.schedule_id
899 and T.LANGUAGE = L.LANGUAGE_CODE);
900 end ADD_LANGUAGE;
901
902
903
904
905 END AMS_CAMPAIGN_SCHEDULES_B_PKG;