[Home] [Help]
PACKAGE BODY: APPS.AMS_COPYELEMENTS_PVT
Source
1 PACKAGE BODY ams_copyelements_pvt AS
2 /* $Header: amsvcpeb.pls 120.5 2007/12/26 09:35:23 spragupa ship $ */
3
4 -- Start Of Comments
5 --
6 -- Name:
7 -- Ams_CopyElements_PVT
8 --
9 -- Purpose:
10 -- This is the package body for copying the different elements in Oracle Marketing.
11 -- These procedures will be called by marketing activities such as promotions,campaigns,
12 -- channels,events,etc while copying them.
13 --Procedures:
14 -- copy_act_messages (see below for specification)
15 -- copy_act_products (see below for specification)
16 -- copy_act_geographic areas (see below for specification)
17 -- copy_act_attachments (see below for specification)
18 -- copy_act_deliverables (see below for specification)
19 -- copy_act_business_parties (see below for specification)
20 -- copy_act_access (see below for specification)
21 -- copy_act_categories (see below for specifications)
22 -- copy_act_deliv_method for event_headers (see below for specifications)
23 -- Notes:
24 --
25 -- History:
26 -- 02/10/2000 Mumu Pande created for new schema ([email protected])
27 --
28 -- 07/11/2000 skarumur
29 -- Added the following procedures
30 -- copy_tasks
31 -- copy_partners
32 -- Changed object assoications to use master_object_id
33 -- Included new columns include in the latest release.
34 -- 08/15/2000 gjoby
35 -- Removed the copy_act_offers procedure
36 -- 05-Apr-2001 choang Added copy_list_select_actions
37 -- 06/04/2001 abhola In copy objects , we need to make copied QTY as null.
38 -- 18-Aug-2001 ptendulk Added api to copy Schedules
39 -- 19-Oct-2001 ptendulk Modified the AMS_Act_Attachments callout.
40 -- 24-Oct-2001 rrajesh Bug fix:2072789
41 -- 02-Nov-2001 rrajesh Modified to copy schedule attributes along with
42 -- copying schedules of a campaign
43 -- 20-may-2002 soagrawa Modified copy_selected_schedule to fix bug # 2380670
44 -- 11-Aug-2003 sunkumar bug# 3064251
45 -- 15-Aug-2003 dbiswas Added usage and purpose cols in copy schedules
46 -- 25-Aug-2003 dbiswas Added sales_methodology_id col in copy schedules
47 -- 30-sep-2003 soagrawa Added API copy_act_collateral
48 -- 06-oct-2003 sodixit Added API copy_target_group
49 -- 28-jan-2005 spendem Fix for bug # 4145845. Added to_char function to the schedule_id
50 -- 24-Dec-2007 spragupa ER - 6467510 - Extend Copy functionality to include TASKS for campaign schedules/activities
51 -- End Of Comments
52
53 g_pkg_name CONSTANT VARCHAR2 (30) := 'AMS_CopyElements_PVT';
54
55 -- Sub-Program unit declarations
56 -- Copy products from promotion,campaign,media_mix,channels - all activities
57
58 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
59 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
60 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
61
62 PROCEDURE copy_act_prod (
63 p_src_act_type IN VARCHAR2,
64 p_new_act_type IN VARCHAR2 := NULL,
65 p_src_act_id IN NUMBER,
66 p_new_act_id IN NUMBER,
67 p_errnum OUT NOCOPY NUMBER,
68 p_errcode OUT NOCOPY VARCHAR2,
69 p_errmsg OUT NOCOPY VARCHAR2
70 )
71 -- PL/SQL Block
72 IS
73 l_stmt_num NUMBER;
74 l_name VARCHAR2 (80);
75 l_mesg_text VARCHAR2 (2000);
76 l_api_version NUMBER;
77 l_return_status VARCHAR2 (1);
78 x_msg_count NUMBER;
79 l_msg_data VARCHAR2 (512);
80 l_act_product_id NUMBER;
81 l_act_prod_rec ams_actproduct_pvt.act_product_rec_type;
82 temp_act_prod_rec ams_actproduct_pvt.act_product_rec_type;
83 l_lookup_meaning VARCHAR2 (80);
84 -- select all products of the calling activity
85 CURSOR prod_cur IS
86 SELECT *
87 FROM ams_act_products
88 WHERE act_product_used_by_id = p_src_act_id
89 AND arc_act_product_used_by = p_src_act_type;
90 BEGIN
91
92
93 ams_utility_pvt.get_lookup_meaning ( 'AMS_SYS_ARC_QUALIFIER',
94 'PROD',
95 l_return_status,
96 l_lookup_meaning
97 );
98 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
99 RAISE FND_API.G_EXC_ERROR ;
100 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
101 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
102 END IF;
103
104 fnd_message.set_name ('AMS', 'AMS_COPY_ELEMENTS');
105 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
106 l_mesg_text := fnd_message.get;
107 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
108 p_src_act_id,
109 l_mesg_text,
110 'GENERAL'
111 );
112 l_stmt_num := 1;
113
114 FOR prod_rec IN prod_cur
115 LOOP
116 BEGIN
117 SAVEPOINT ams_act_products;
118 l_api_version := 1.0;
119 l_act_product_id := 0;
120 l_act_prod_rec := temp_act_prod_rec;
121 l_act_prod_rec.act_product_used_by_id := p_new_act_id;
122 l_act_prod_rec.arc_act_product_used_by :=
123 NVL(p_new_act_type,p_src_act_type);
124 l_act_prod_rec.product_sale_type :=
125 prod_rec.product_sale_type;
126 l_act_prod_rec.primary_product_flag :=
127 prod_rec.primary_product_flag;
128 l_act_prod_rec.enabled_flag := prod_rec.enabled_flag;
129 l_act_prod_rec.category_id := prod_rec.category_id;
130 l_act_prod_rec.category_set_id := prod_rec.category_set_id;
131 l_act_prod_rec.organization_id := prod_rec.organization_id;
132 l_act_prod_rec.inventory_item_id := prod_rec.inventory_item_id;
133 l_act_prod_rec.level_type_code := prod_rec.level_type_code;
134 l_act_prod_rec.attribute_category := prod_rec.attribute_category;
135 l_act_prod_rec.attribute1 := prod_rec.attribute1;
136 l_act_prod_rec.attribute2 := prod_rec.attribute2;
137 l_act_prod_rec.attribute1 := prod_rec.attribute3;
138 l_act_prod_rec.attribute4 := prod_rec.attribute4;
139 l_act_prod_rec.attribute5 := prod_rec.attribute5;
140 l_act_prod_rec.attribute6 := prod_rec.attribute6;
141 l_act_prod_rec.attribute7 := prod_rec.attribute7;
142 l_act_prod_rec.attribute8 := prod_rec.attribute8;
143 l_act_prod_rec.attribute9 := prod_rec.attribute9;
144 l_act_prod_rec.attribute10 := prod_rec.attribute10;
145 l_act_prod_rec.attribute11 := prod_rec.attribute11;
146 l_act_prod_rec.attribute12 := prod_rec.attribute12;
147 l_act_prod_rec.attribute13 := prod_rec.attribute13;
148 l_act_prod_rec.attribute14 := prod_rec.attribute14;
149 l_act_prod_rec.attribute15 := prod_rec.attribute15;
150 l_act_prod_rec.excluded_flag := prod_rec.excluded_flag;
151 -- 11/30/2001 yzhao: add line_lumpsum_amount, line_lumpsum_qty
152 l_act_prod_rec.line_lumpsum_amount := prod_rec.line_lumpsum_amount;
153 l_act_prod_rec.line_lumpsum_qty := prod_rec.line_lumpsum_qty;
154
155 ams_actproduct_pvt.create_act_product (
156 p_api_version => l_api_version,
157 p_init_msg_list => fnd_api.g_true,
158 x_return_status => l_return_status,
159 x_msg_count => x_msg_count,
160 x_msg_data => l_msg_data,
161 p_act_product_rec => l_act_prod_rec,
162 x_act_product_id => l_act_product_id
163 );
164
165 IF l_return_status = fnd_api.g_ret_sts_error
166 OR l_return_status = fnd_api.g_ret_sts_unexp_error THEN
167 FOR l_counter IN 1 .. x_msg_count
168 LOOP
169 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
170 l_stmt_num := 2;
171 p_errnum := 1;
172 p_errmsg := substr(l_mesg_text||' , '|| TO_CHAR (l_stmt_num) ||
173 ' , ' || '): ' || l_counter ||
174 ' OF ' || x_msg_count, 1, 4000);
175 ams_cpyutility_pvt.write_log_mesg( p_src_act_type,
176 p_src_act_id,
177 p_errmsg,
178 'ERROR'
179 );
180 END LOOP;
181 ---- if error then right a copy log message to the log table
182 ROLLBACK TO ams_act_products;
183 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
184 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
185 l_mesg_text := fnd_message.get;
186 p_errmsg := SUBSTR ( l_mesg_text || ' - ' ||
187 ams_cpyutility_pvt.get_product_name
188 (prod_rec.category_id),
189 1, 4000);
190 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
191 p_src_act_id,
192 p_errmsg,
193 'ERROR'
194 );
195 END IF;
196 EXCEPTION
197 WHEN OTHERS
198 THEN
199 ROLLBACK TO ams_act_products;
200 p_errcode := SQLCODE;
201 p_errnum := 3;
202 l_stmt_num := 4;
203 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR');
204 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
205 l_mesg_text := fnd_message.get;
206 p_errmsg := SUBSTR ( l_mesg_text || TO_CHAR (l_stmt_num) ||
207 '): ' || p_errcode || SQLERRM, 1, 4000);
208
209 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
210 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
211 l_mesg_text := fnd_message.get;
212 p_errmsg := l_mesg_text ||
213 ams_cpyutility_pvt.get_product_name
214 (prod_rec.category_id) || p_errmsg;
215 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
216 p_src_act_id,
217 p_errmsg,
218 'ERROR'
219 );
220
221 END;
222 END LOOP;
223 fnd_message.set_name ('AMS', 'AMS_END_COPY_ELEMENTS');
224 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
225 fnd_message.set_token('ELEMENT_NAME',' ' ,TRUE);
226 l_mesg_text := fnd_message.get;
227 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
228 p_src_act_id,
229 l_mesg_text,
230 'GENERAL'
231 );
232 EXCEPTION
233 WHEN OTHERS
234 THEN
235 p_errcode := SQLCODE;
236 p_errnum := 4;
237 l_stmt_num := 5;
238 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR3');
239 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
240 l_mesg_text := fnd_message.get;
241 p_errmsg := SUBSTR ( l_mesg_text || TO_CHAR (l_stmt_num) || ',' ||
242 '): ' || p_errcode || SQLERRM, 1, 4000);
243 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
244 p_src_act_id,
245 p_errmsg,
246 'ERROR'
247 );
248 END copy_act_prod;
249
250 PROCEDURE copy_act_messages (
251 p_src_act_type IN VARCHAR2,
252 p_new_act_type IN VARCHAR2 := NULL,
253 p_src_act_id IN NUMBER,
254 p_new_act_id IN NUMBER,
255 p_errnum OUT NOCOPY NUMBER,
256 p_errcode OUT NOCOPY VARCHAR2,
257 p_errmsg OUT NOCOPY VARCHAR2
258 )
259 -- PL/SQL Block
260 IS
261 l_stmt_num NUMBER;
262 l_name VARCHAR2 (80);
263 l_mesg_text VARCHAR2 (2000);
264 l_api_version NUMBER;
265 l_return_status VARCHAR2 (1);
266 x_msg_count NUMBER;
267 l_msg_data VARCHAR2 (512);
268 l_act_message_id NUMBER;
269 l_lookup_meaning VARCHAR2 (80) := 'Messages';
270 -- select all products of the calling activity
271 CURSOR message_cur IS
272 SELECT *
273 FROM ams_act_messages
274 WHERE message_used_by_id = p_src_act_id
275 AND message_used_by = p_src_act_type;
276 BEGIN
277 p_errcode := NULL;
278 p_errnum := 0;
279 p_errmsg := NULL;
280 -------have to add once sysarc qualifier is created----------------------
281 AMS_UTILITY_PVT.get_lookup_meaning('AMS_SYS_ARC_QUALIFIER',
282 'MESG',
283 l_return_status,
284 l_lookup_meaning);
285
286 fnd_message.set_name ('AMS', 'AMS_COPY_ELEMENTS');
287 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
288 l_mesg_text := fnd_message.get;
289 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
290 p_src_act_id,
291 l_mesg_text,
292 'GENERAL'
293 );
294 l_stmt_num := 1;
295
296 FOR message_rec IN message_cur
297 LOOP
298 BEGIN
299 p_errcode := NULL;
300 p_errnum := 0;
301 p_errmsg := NULL;
302 l_api_version := 1.0;
303 l_return_status := NULL;
304 x_msg_count := 0;
305 l_msg_data := NULL;
306 l_act_message_id := 0;
307 ams_act_messages_pvt.create_act_messages
308 (
309 p_api_version => l_api_version,
310 p_init_msg_list => fnd_api.g_true,
311 x_return_status => l_return_status,
312 x_msg_count => x_msg_count,
313 x_msg_data => l_msg_data,
314 p_message_id => message_rec.message_id,
315 p_message_used_by => NVL(p_new_act_type,p_src_act_type),
316 p_msg_used_by_id => p_new_act_id,
317 x_act_msg_id => l_act_message_id
318 );
319 IF l_return_status = fnd_api.g_ret_sts_error
320 OR l_return_status = fnd_api.g_ret_sts_unexp_error THEN
321 FOR l_counter IN 1 .. x_msg_count
322 LOOP
323 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
324 l_stmt_num := 2;
325 p_errnum := 1;
326 p_errmsg := substr(l_mesg_text||' , '|| TO_CHAR (l_stmt_num) ||
327 ' , ' || '): ' || l_counter ||
328 ' OF ' || x_msg_count, 1, 4000);
329 ams_cpyutility_pvt.write_log_mesg( p_src_act_type,
330 p_src_act_id,
331 p_errmsg,
332 'ERROR'
333 );
334 END LOOP;
335
336 p_errmsg := SUBSTR( l_mesg_text || ' - ' ||
337 ams_cpyutility_pvt.get_message_name
338 (message_rec.message_id), 1, 4000);
339 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
340 p_src_act_id,
341 p_errmsg,
342 'ERROR'
343 );
344 END IF;
345 EXCEPTION
346 WHEN OTHERS
347 THEN
348 p_errcode := SQLCODE;
349 p_errnum := 3;
350 l_stmt_num := 4;
351 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR');
352 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
353 l_mesg_text := fnd_message.get;
354 p_errmsg := SUBSTR ( l_mesg_text || ' , ' ||
355 TO_CHAR (l_stmt_num) || '): ' || p_errcode ||
356 SQLERRM, 1, 4000);
357 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
358 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
359 l_mesg_text := fnd_message.get;
360 p_errmsg := l_mesg_text || ': - ' ||
361 ams_cpyutility_pvt.get_message_name
362 (message_rec.message_id) || p_errmsg;
363 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
364 p_src_act_id,
365 p_errmsg,
366 'ERROR'
367 );
368 END;
369 END LOOP;
370 fnd_message.set_name ('AMS', 'AMS_END_COPY_ELEMENTS');
371 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
372 fnd_message.set_token('ELEMENT_NAME',' ' ,TRUE);
373 l_mesg_text := fnd_message.get;
374 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
375 p_src_act_id,
376 l_mesg_text,
377 'GENERAL'
378 );
379 EXCEPTION
380 WHEN OTHERS
381 THEN
382 p_errcode := SQLCODE;
383 p_errnum := 4;
384 l_stmt_num := 5;
385 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR3');
386 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
387 l_mesg_text := fnd_message.get;
388 p_errmsg := SUBSTR ( l_mesg_text || TO_CHAR (l_stmt_num) ||
389 ',' || '): ' || p_errcode || SQLERRM, 1, 4000);
390 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
391 p_src_act_id,
392 p_errmsg,
393 'ERROR'
394 );
395 END copy_act_messages;
396
397 -- Sub-Program unit declarations
398 /* Copy deliverables from promotion,campaign,media_mix,channels -
399 all activities */
400
401 PROCEDURE copy_object_associations (
402 p_src_act_type IN VARCHAR2,
403 p_new_act_type IN VARCHAR2 := NULL,
404 p_src_act_id IN NUMBER,
405 p_new_act_id IN NUMBER,
406 p_errnum OUT NOCOPY NUMBER,
407 p_errcode OUT NOCOPY VARCHAR2,
408 p_errmsg OUT NOCOPY VARCHAR2
409 )
410 IS
411 -- PL/SQL Block
412 l_stmt_num NUMBER;
413 l_name VARCHAR2 (80);
414 l_mesg_text VARCHAR2 (2000);
415 l_api_version NUMBER;
416 l_return_status VARCHAR2 (1);
417 x_msg_count NUMBER;
418 l_msg_data VARCHAR2 (512);
419 l_obj_association_id NUMBER;
420 l_association_rec ams_associations_pvt.association_rec_type;
421 temp_association_rec ams_associations_pvt.association_rec_type;
422 l_usage_type VARCHAR2 (30);
423 -- select all assciations of the calling activity
424 -- Changed the select statement to master_object_id
425 CURSOR association_cur IS
426 SELECT *
427 FROM ams_object_associations
428 WHERE master_object_id = p_src_act_id
429 AND master_object_type = p_src_act_type ;
430
431 CURSOR cur_get_old_start IS
432 SELECT actual_exec_start_date
433 FROM ams_campaigns_v
434 WHERE campaign_id = p_src_act_id;
435
436 CURSOR cur_get_new_start IS
437 SELECT actual_exec_start_date
438 FROM ams_campaigns_v
439 WHERE campaign_id = p_new_act_id;
440
441 l_new_date date;
442 l_old_date date;
443 BEGIN
444 p_errcode := NULL;
445 p_errnum := 0;
446 p_errmsg := NULL;
447 l_api_version := 1.0;
448 l_return_status := NULL;
449 x_msg_count := 0;
450 l_msg_data := NULL;
451 l_obj_association_id := 0;
452 fnd_message.set_name ('AMS', 'AMS_COPY_ELEMENTS');
453 fnd_message.set_token ('ELEMENTS', 'AMS_COPY_ASSOCIATIONS', TRUE);
454 l_mesg_text := fnd_message.get;
455 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
456 p_src_act_id,
457 l_mesg_text,
458 'GENERAL'
459 );
460 l_stmt_num := 1;
461
462 FOR association_rec IN association_cur
463 LOOP
464 BEGIN
465 p_errcode := NULL;
466 p_errnum := 0;
467 p_errmsg := NULL;
468 l_association_rec := temp_association_rec;
469 l_association_rec.object_version_number := 1;
470 l_association_rec.master_object_type
471 := association_rec.master_object_type;
472 l_association_rec.master_object_id := p_new_act_id;
473 l_association_rec.using_object_type
474 := association_rec.using_object_type;
475 l_association_rec.using_object_id
476 := association_rec.using_object_id;
477 l_association_rec.primary_flag := association_rec.primary_flag;
478 ams_utility_pvt.get_lookup_meaning ( 'AMS_OBJECT_USAGE_TYPE',
479 'USED_BY',
480 l_return_status,
481 l_usage_type
482 );
483 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
484 RAISE FND_API.G_EXC_ERROR ;
485 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
486 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
487 END IF;
488
489 l_association_rec.usage_type := 'USED_BY';
490 /* l_association_rec.quantity_needed := association_rec.quantity_needed; */
491 l_association_rec.quantity_needed := NULL;
492
493 l_association_rec.quantity_needed_by_date := NULL;
494 IF p_src_act_type = 'CAMP' THEN
495
496 OPEN cur_get_old_start;
497 FETCH cur_get_old_start into l_old_date;
498 CLOSE cur_get_old_start;
499
500 OPEN cur_get_new_start;
501 FETCH cur_get_new_start into l_new_date;
502 CLOSE cur_get_new_start;
503
504 IF association_rec.quantity_needed_by_date is not NULL THEN
505
506 l_association_rec.quantity_needed_by_date := l_new_date +
507 (association_rec.quantity_needed_by_date - l_old_date );
508 END IF;
509 END IF;
510 l_association_rec.cost_frozen_flag := 'N';
511 l_association_rec.pct_of_cost_to_charge_used_by := NULL;
512 l_association_rec.max_cost_to_charge_used_by := NULL;
513 l_association_rec.max_cost_currency_code :=
514 association_rec.max_cost_currency_code;
515 l_association_rec.metric_class := association_rec.metric_class;
516 l_association_rec.attribute_category :=
517 association_rec.attribute_category;
518 l_association_rec.attribute1 := association_rec.attribute1;
519 l_association_rec.attribute2 := association_rec.attribute2;
520 l_association_rec.attribute1 := association_rec.attribute3;
521 l_association_rec.attribute4 := association_rec.attribute4;
522 l_association_rec.attribute5 := association_rec.attribute5;
523 l_association_rec.attribute6 := association_rec.attribute6;
524 l_association_rec.attribute7 := association_rec.attribute7;
525 l_association_rec.attribute8 := association_rec.attribute8;
526 l_association_rec.attribute9 := association_rec.attribute9;
527 l_association_rec.attribute10 := association_rec.attribute10;
528 l_association_rec.attribute11 := association_rec.attribute11;
529 l_association_rec.attribute12 := association_rec.attribute12;
530 l_association_rec.attribute13 := association_rec.attribute13;
531 l_association_rec.attribute14 := association_rec.attribute14;
532 l_association_rec.attribute15 := association_rec.attribute15;
533 -- Calling create Api to create a new associaitons in the
534 -- ams_Act_objective table based on the old one
535
536
537 ams_associations_pvt.create_association
538 (
539 p_api_version => l_api_version,
540 x_return_status => l_return_status,
541 p_init_msg_list => fnd_api.g_true,
542 x_msg_count => x_msg_count,
543 x_msg_data => l_msg_data,
544 p_association_rec => l_association_rec,
545 x_object_association_id => l_obj_association_id
546 );
547 -- If failed creating then get all the messages for that Api from
548 -- the message list and put it into the log table
549 IF l_return_status = fnd_api.g_ret_sts_error
550 OR l_return_status = fnd_api.g_ret_sts_unexp_error THEN
551 FOR l_counter IN 1 .. x_msg_count
552 LOOP
553 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
554 l_stmt_num := 2;
555 p_errnum := 1;
556 p_errmsg := substr(l_mesg_text||' , '|| TO_CHAR (l_stmt_num) ||
557 ' , ' || '): ' || l_counter ||
558 ' OF ' || x_msg_count, 1, 4000);
559 ams_cpyutility_pvt.write_log_mesg( p_src_act_type,
560 p_src_act_id,
561 p_errmsg,
562 'ERROR'
563 );
564 END LOOP;
565 -- Is failed write a copy failed message in the log table
566 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
567 fnd_message.set_token ( 'ELEMENTS',
568 l_association_rec.using_object_type ||
569 l_association_rec.using_object_id,
570 TRUE
571 );
572 l_mesg_text := fnd_message.get;
573 p_errmsg := SUBSTR (l_mesg_text ||
574 ams_utility_pvt.get_object_name (
575 l_association_rec.using_object_type,
576 l_association_rec.using_object_id),
577 1,
578 4000
579 );
580 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
581 p_src_act_id,
582 p_errmsg,
583 'ERROR'
584 );
585 END IF;
586 -- Exception block writes a message in the log table if any failure
587 EXCEPTION
588 WHEN OTHERS
589 THEN
590 p_errcode := SQLCODE;
591 p_errnum := 3;
592 l_stmt_num := 4;
593 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR');
594 fnd_message.set_token ( 'ELEMENTS',
595 l_association_rec.using_object_type ||
596 ' - ' ||
597 l_association_rec.using_object_id,
598 TRUE);
599 l_mesg_text := fnd_message.get;
600 p_errmsg := SUBSTR ( l_mesg_text || ',' ||
601 TO_CHAR (l_stmt_num) || ',' || '): ' ||
602 p_errcode || SQLERRM, 1, 4000);
603 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
604 fnd_message.set_token ('ELEMENTS','AMS_COPY_ASSOCIATIONS', TRUE);
605 l_mesg_text := fnd_message.get;
606 p_errmsg := l_mesg_text ||
607 ams_utility_pvt.get_object_name (
608 l_association_rec.master_object_type,
609 l_association_rec.master_object_id)
610 || p_errmsg;
611 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
612 p_src_act_id,
613 p_errmsg,
614 'ERROR'
615 );
616 END;
617 END LOOP;
618 EXCEPTION
619 WHEN OTHERS THEN
620 p_errcode := SQLCODE;
621 p_errnum := 4;
622 l_stmt_num := 5;
623 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR3');
624 fnd_message.set_token ('ELEMENTS', 'AMS_COPY_ASSOCIATIONS', TRUE);
625 l_mesg_text := fnd_message.get;
626 p_errmsg := SUBSTR ( l_mesg_text || TO_CHAR (l_stmt_num) ||
627 ',' || '): ' || p_errcode || SQLERRM, 1,
628 4000);
629 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
630 p_src_act_id,
631 p_errmsg,
632 'ERROR'
633 );
634 END copy_object_associations;
635
636 -- Sub-Program unit declarations
637 /* Copy geo areas from promotion,campaign,media_mix,channels - all activities */
638
639 PROCEDURE copy_act_geo_areas (
640 p_src_act_type IN VARCHAR2,
641 p_new_act_type IN VARCHAR2 := NULL,
642 p_src_act_id IN NUMBER,
643 p_new_act_id IN NUMBER,
644 p_errnum OUT NOCOPY NUMBER,
645 p_errcode OUT NOCOPY VARCHAR2,
646 p_errmsg OUT NOCOPY VARCHAR2
647 )
648 IS
649 l_stmt_num NUMBER;
650 l_name VARCHAR2 (80);
651 l_mesg_text VARCHAR2 (2000);
652 l_api_version NUMBER;
653 l_return_status VARCHAR2 (1);
654 x_msg_count NUMBER;
655 l_msg_data VARCHAR2 (512);
656 l_act_geo_area_id NUMBER;
657 l_geo_area_rec ams_geo_areas_pvt.geo_area_rec_type;
658 temp_geo_area_rec ams_geo_areas_pvt.geo_area_rec_type;
659 l_lookup_meaning VARCHAR2 (80);
660
661 CURSOR geo_areas_cur IS
662 SELECT *
663 FROM ams_act_geo_areas
664 WHERE act_geo_area_used_by_id = p_src_act_id
665 AND arc_act_geo_area_used_by = p_src_act_type;
666
667 l_location_name varchar2(240);
668 CURSOR c_geo_source_name(l_location_id NUMBER,
669 l_location_type VARCHAR2) IS
670
671 SELECT substr(location_name||','||location,1,240)
672 FROM ams_geoarea_scr_v
673 WHERE location_hierarchy_id = l_location_id
674 AND location_type_code = l_location_type;
675 BEGIN
676 p_errcode := NULL;
677 p_errnum := 0;
678 p_errmsg := NULL;
679 ams_utility_pvt.get_lookup_meaning (
680 'AMS_SYS_ARC_QUALIFIER',
681 'GEOS',
682 l_return_status,
683 l_lookup_meaning
684 );
685 fnd_message.set_name ('AMS', 'AMS_COPY_ELEMENTS');
686 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
687 l_mesg_text := fnd_message.get;
688 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
689 p_src_act_id,
690 l_mesg_text,
691 'GENERAL'
692 );
693 l_stmt_num := 1;
694
695 FOR geo_areas_rec IN geo_areas_cur LOOP
696 BEGIN
697 p_errcode := NULL;
698 p_errnum := 0;
699 p_errmsg := NULL;
700 l_api_version := 1.0;
701 l_return_status := NULL;
702 x_msg_count := 0;
703 l_msg_data := NULL;
704 l_act_geo_area_id := 0;
705 l_geo_area_rec := temp_geo_area_rec;
706 l_geo_area_rec.act_geo_area_used_by_id := p_new_act_id;
707 l_geo_area_rec.arc_act_geo_area_used_by :=
708 NVL(p_new_act_type,p_src_act_type);
709 l_geo_area_rec.attribute_category :=
710 geo_areas_rec.attribute_category;
711 l_geo_area_rec.attribute1 := geo_areas_rec.attribute1;
712 l_geo_area_rec.attribute2 := geo_areas_rec.attribute2;
713 l_geo_area_rec.attribute3 := geo_areas_rec.attribute3;
714 l_geo_area_rec.attribute4 := geo_areas_rec.attribute4;
715 l_geo_area_rec.attribute5 := geo_areas_rec.attribute5;
716 l_geo_area_rec.attribute6 := geo_areas_rec.attribute6;
717 l_geo_area_rec.attribute7 := geo_areas_rec.attribute7;
718 l_geo_area_rec.attribute8 := geo_areas_rec.attribute8;
719 l_geo_area_rec.attribute9 := geo_areas_rec.attribute9;
720 l_geo_area_rec.attribute10 := geo_areas_rec.attribute10;
721 l_geo_area_rec.attribute11 := geo_areas_rec.attribute11;
722 l_geo_area_rec.attribute12 := geo_areas_rec.attribute12;
723 l_geo_area_rec.attribute13 := geo_areas_rec.attribute13;
724 l_geo_area_rec.attribute14 := geo_areas_rec.attribute14;
725 l_geo_area_rec.attribute15 := geo_areas_rec.attribute15;
726 l_geo_area_rec.geo_area_type_code
727 := geo_areas_rec.geo_area_type_code;
728 l_geo_area_rec.geo_hierarchy_id := geo_areas_rec.geo_hierarchy_id;
729 ams_geo_areas_pvt.create_geo_area
730 ( p_api_version => l_api_version,
731 x_return_status => l_return_status,
732 p_init_msg_list => fnd_api.g_true,
733 x_msg_count => x_msg_count,
734 x_msg_data => l_msg_data,
735 p_geo_area_rec => l_geo_area_rec,
736 x_geo_area_id => l_act_geo_area_id
737 );
738
739 IF l_return_status = fnd_api.g_ret_sts_error
740 OR l_return_status = fnd_api.g_ret_sts_unexp_error
741 THEN
742 FOR l_counter IN 1 .. x_msg_count
743 LOOP
744 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
745 l_stmt_num := 2;
746 p_errnum := 1;
747 p_errmsg := substr(l_mesg_text||' , '|| TO_CHAR (l_stmt_num) ||
748 ' , ' || '): ' || l_counter ||
749 ' OF ' || x_msg_count, 1, 4000);
750 ams_cpyutility_pvt.write_log_mesg( p_src_act_type,
751 p_src_act_id,
752 p_errmsg,
753 'ERROR'
754 );
755 END LOOP;
756 ---- if error then right a copy log message to the log table
757
758 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
759 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
760 l_mesg_text := fnd_message.get;
761 p_errmsg := SUBSTR ( l_mesg_text ||
762 ' - ' ||
763 ams_cpyutility_pvt.get_geo_area_name (
764 geo_areas_rec.geo_hierarchy_id,
765 geo_areas_rec.geo_area_type_code
766 ),
767 1, 4000);
768 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
769 p_src_act_id,
770 p_errmsg,
771 'ERROR'
772 );
773 ELSE
774 open c_geo_source_name ( geo_areas_rec.geo_hierarchy_id,
775 geo_areas_rec.geo_area_type_code);
776 fetch c_geo_source_name into l_location_name ;
777 close c_geo_source_name ;
778 fnd_message.set_name ('AMS', 'AMS_END_COPY_ELEMENTS');
779 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
780 fnd_message.set_token('ELEMENT_NAME',l_location_name,TRUE);
781 l_mesg_text := fnd_message.get;
782 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
783 p_src_act_id,
784 l_mesg_text,
785 'GENERAL'
786 );
787
788 END IF;
789 EXCEPTION
790 WHEN OTHERS THEN
791 p_errcode := SQLCODE;
792 p_errnum := 3;
793 l_stmt_num := 4;
794 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR');
795 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
796 l_mesg_text := fnd_message.get;
797 p_errmsg := SUBSTR ( l_mesg_text ||
798 TO_CHAR (l_stmt_num) ||
799 '): ' || p_errcode || SQLERRM, 1, 4000);
800 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
801 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
802 l_mesg_text := fnd_message.get;
803 p_errmsg := l_mesg_text ||
804 ams_cpyutility_pvt.get_geo_area_name (
805 geo_areas_rec.geo_hierarchy_id,
806 geo_areas_rec.geo_area_type_code
807 ) || p_errmsg;
808 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
809 p_src_act_id,
810 p_errmsg,
811 'ERROR'
812 );
813 END;
814 END LOOP;
815 EXCEPTION
816 WHEN OTHERS THEN
817 p_errcode := SQLCODE;
818 p_errnum := 4;
819 l_stmt_num := 5;
820 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR3');
821 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
822 l_mesg_text := fnd_message.get;
823 p_errmsg := SUBSTR ( l_mesg_text ||
824 TO_CHAR (l_stmt_num) || ',' || '): ' ||
825 p_errcode || SQLERRM, 1, 4000);
826 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
827 p_src_act_id,
828 p_errmsg,
829 'ERROR'
830 );
831 END copy_act_geo_areas;
832
833 -- Sub-Program unit declarations
834 /* Copy business parties from promotion,campaign,media_mix,channels - all activities */
835
836 /* copy ing resource is not supported funcutionality so I am commenting OUT NOCOPY hte API murali 05/13/2002
837 PROCEDURE copy_act_resources (
838 p_src_act_type IN VARCHAR2,
839 p_new_act_type IN VARCHAR2 := NULL,
840 p_src_act_id IN NUMBER,
841 p_new_act_id IN NUMBER,
842 p_errnum OUT NOCOPY NUMBER,
843 p_errcode OUT NOCOPY VARCHAR2,
844 p_errmsg OUT NOCOPY VARCHAR2
845 )
846 IS
847 -- PL/SQL Block
848 l_stmt_num NUMBER;
849 l_name VARCHAR2 (80);
850 l_mesg_text VARCHAR2 (2000);
851 l_api_version NUMBER;
852 l_return_status VARCHAR2 (1);
853 x_msg_count NUMBER;
854 l_msg_data VARCHAR2 (512);
855 l_act_resource_id NUMBER;
856 l_act_resource_rec ams_actresource_pvt.act_resource_rec_type;
857 l_lookup_meaning VARCHAR2 (80);
858
859 CURSOR resource_cur
860 IS
861 SELECT *
862 FROM ams_act_resources
863 WHERE act_resource_used_by_id = p_src_act_id
864 AND arc_act_resource_used_by = p_src_act_type;
865 BEGIN
866 p_errcode := NULL;
867 p_errnum := 0;
868 p_errmsg := NULL;
869 ams_utility_pvt.get_lookup_meaning ( 'AMS_SYS_ARC_QUALIFIER',
870 'RESC',
871 l_return_status,
872 l_lookup_meaning
873 );
874 fnd_message.set_name ('AMS', 'AMS_COPY_ELEMENTS');
875 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
876 l_mesg_text := fnd_message.get;
877 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
878 p_src_act_id,
879 l_mesg_text,
880 'GENERAL'
881 );
882 l_stmt_num := 1;
883
884 FOR resource_rec IN resource_cur
885 LOOP
886 BEGIN
887 p_errcode := NULL;
888 p_errnum := 0;
889 p_errmsg := NULL;
890 l_api_version := 1.0;
891 l_return_status := NULL;
892 x_msg_count := 0;
893 l_msg_data := NULL;
894 l_act_resource_id := 0;
895 l_act_resource_rec.act_resource_used_by_id := p_new_act_id;
896 l_act_resource_rec.arc_act_resource_used_by
897 := NVL(p_new_act_type,p_src_act_type);
898 l_act_resource_rec.resource_id := resource_rec.resource_id;
899 l_act_resource_rec.role_relate_id := resource_rec.role_relate_id;
900 l_act_resource_rec.user_status_id := resource_rec.user_status_id;
901 l_act_resource_rec.system_status_code
902 := resource_rec.system_status_code;
903 l_act_resource_rec.description := resource_rec.description;
904 l_act_resource_rec.attribute_category
905 := resource_rec.attribute_category;
906 l_act_resource_rec.attribute1 := resource_rec.attribute1;
907 l_act_resource_rec.attribute2 := resource_rec.attribute2;
908 l_act_resource_rec.attribute3 := resource_rec.attribute3;
909 l_act_resource_rec.attribute4 := resource_rec.attribute4;
910 l_act_resource_rec.attribute5 := resource_rec.attribute5;
911 l_act_resource_rec.attribute6 := resource_rec.attribute6;
912 l_act_resource_rec.attribute7 := resource_rec.attribute7;
913 l_act_resource_rec.attribute8 := resource_rec.attribute8;
914 l_act_resource_rec.attribute9 := resource_rec.attribute9;
915 l_act_resource_rec.attribute10 := resource_rec.attribute10;
916 l_act_resource_rec.attribute11 := resource_rec.attribute11;
917 l_act_resource_rec.attribute12 := resource_rec.attribute12;
918 l_act_resource_rec.attribute13 := resource_rec.attribute13;
919 l_act_resource_rec.attribute14 := resource_rec.attribute14;
920 l_act_resource_rec.attribute15 := resource_rec.attribute15;
921 ams_actresource_pvt.create_act_resource
922 ( p_api_version => l_api_version,
923 p_init_msg_list => fnd_api.g_true,
924 x_return_status => l_return_status,
925 x_msg_count => x_msg_count,
926 x_msg_data => l_msg_data,
927 p_act_resource_rec => l_act_resource_rec,
928 x_act_resource_id => l_act_resource_id
929 );
930
931 IF l_return_status = fnd_api.g_ret_sts_error
932 OR l_return_status = fnd_api.g_ret_sts_unexp_error THEN
933 FOR l_counter IN 1 .. x_msg_count
934 LOOP
935 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
936 l_stmt_num := 2;
937 p_errnum := 1;
938 p_errmsg := substr(l_mesg_text||' , '|| TO_CHAR (l_stmt_num) ||
939 ' , ' || '): ' || l_counter ||
940 ' OF ' || x_msg_count, 1, 4000);
941 ams_cpyutility_pvt.write_log_mesg( p_src_act_type,
942 p_src_act_id,
943 p_errmsg,
944 'ERROR'
945 );
946 END LOOP;
947 ---- if error then right a copy log message to the log table
948
949 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
950 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
951 l_mesg_text := fnd_message.get;
952 p_errmsg := SUBSTR ( l_mesg_text || ' - ' ||
953 ams_cpyutility_pvt.get_resource_name (
954 resource_rec.resource_id),
955 1, 4000);
956 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
957 p_src_act_id,
958 p_errmsg,
959 'ERROR'
960 );
961 END IF;
962 EXCEPTION
963 WHEN OTHERS
964 THEN
965 p_errcode := SQLCODE;
966 p_errnum := 3;
967 l_stmt_num := 4;
968 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR');
969 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
970 l_mesg_text := fnd_message.get;
971 p_errmsg := SUBSTR ( l_mesg_text || TO_CHAR (l_stmt_num) ||
972 '): ' || p_errcode || SQLERRM, 1, 4000);
973 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
974 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
975 l_mesg_text := fnd_message.get;
976 p_errmsg := l_mesg_text ||
977 ams_cpyutility_pvt.get_resource_name (
978 resource_rec.resource_id) || p_errmsg;
979 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
980 p_src_act_id,
981 p_errmsg,
982 'ERROR'
983 );
984 END;
985 END LOOP;
986 EXCEPTION
987 WHEN OTHERS THEN
988 p_errcode := SQLCODE;
989 p_errnum := 4;
990 l_stmt_num := 5;
991 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR3');
992 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
993 l_mesg_text := fnd_message.get;
994 p_errmsg := SUBSTR ( l_mesg_text || TO_CHAR (l_stmt_num) ||
995 ',' || '): ' || p_errcode ||
996 SQLERRM, 1, 4000);
997 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
998 p_src_act_id,
999 p_errmsg,
1000 'ERROR'
1001 );
1002 END copy_act_resources;
1003 */
1004 -- Sub-Program unit declarations
1005 /* Copy attachments from promotion,campaign,media_mix,channels - all activities */
1006
1007 PROCEDURE copy_act_attachments (
1008 p_src_act_type IN VARCHAR2,
1009 p_new_act_type IN VARCHAR2 := NULL,
1010 p_src_act_id IN NUMBER,
1011 p_new_act_id IN NUMBER,
1012 p_errnum OUT NOCOPY NUMBER,
1013 p_errcode OUT NOCOPY VARCHAR2,
1014 p_errmsg OUT NOCOPY VARCHAR2
1015 )
1016 IS
1017 -- PL/SQL Block
1018 l_stmt_num NUMBER;
1019 l_name VARCHAR2 (80);
1020 l_mesg_text VARCHAR2 (2000);
1021 l_api_version NUMBER := 1 ;
1022 l_return_status VARCHAR2 (1);
1023 x_msg_count NUMBER;
1024 l_msg_data VARCHAR2 (512);
1025 l_act_attachment_id NUMBER;
1026 attach_rec jtf_amv_attachment_pub.act_attachment_rec_type;
1027 temp_attach_rec jtf_amv_attachment_pub.act_attachment_rec_type;
1028 l_lookup_meaning VARCHAR2(80);
1029
1030 CURSOR c_doc_att IS
1031 SELECT * FROM fnd_attached_documents
1032 WHERE entity_name = p_src_act_type
1033 AND pk1_value = p_src_act_id ;
1034 l_doc_att_rec c_doc_att%ROWTYPE ;
1035
1036 CURSOR c_doc_det (l_doc_id IN NUMBER) IS
1037 SELECT b.datatype_id ,b.category_id ,b.security_type ,
1038 b.publish_flag ,tl.description ,b.file_name ,
1039 b.media_id ,tl.doc_attribute2 ,tl.language,tl.short_text,
1040 DECODE(b.datatype_id,1,'TEXT',5,'URL',6,'FILE',3,'IMAGE') att_type
1041 FROM fnd_documents b, fnd_documents_tl tl
1042 WHERE b.document_id = tl.document_id
1043 AND tl.language = USERENV('LANG')
1044 AND b.document_id = l_doc_id ;
1045 l_doc_rec c_doc_det%ROWTYPE ;
1046
1047 CURSOR c_short_txt (p_media_id IN NUMBER)
1048 IS
1049 select short_text
1050 from fnd_documents_short_text
1051 where media_id = p_media_id;
1052
1053 l_short_text VARCHAR2(4000);
1054
1055 l_doc_attach_rec AMS_Attachment_PVT.fnd_attachment_rec_type ;
1056
1057 CURSOR attachments_cur(p_doc_id IN NUMBER)
1058 IS
1059 SELECT *
1060 FROM jtf_amv_attachments
1061 WHERE attachment_used_by_id = p_src_act_id
1062 AND attachment_used_by = p_src_act_type
1063 AND document_id = p_doc_id
1064 -- added by soagrawa on 25-jan-2002 to copy content
1065 -- bug# 2175580
1066 AND (attachment_type IN ('TEXT' , 'URL', 'FILE' ,'IMAGE'));
1067 attachments_rec attachments_cur%ROWTYPE ;
1068 l_dummy_id NUMBER ;
1069 BEGIN
1070
1071
1072 p_errcode := NULL;
1073 p_errnum := 0;
1074 p_errmsg := NULL;
1075 ams_utility_pvt.get_lookup_meaning ( 'AMS_SYS_ARC_QUALIFIER',
1076 'ATCH',
1077 l_return_status,
1078 l_lookup_meaning
1079 );
1080 fnd_message.set_name ('AMS', 'AMS_COPY_ELEMENTS');
1081 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
1082 l_mesg_text := fnd_message.get;
1083 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
1084 p_src_act_id ,
1085 l_mesg_text,
1086 'GENERAL'
1087 );
1088 l_stmt_num := 1;
1089
1090 OPEN c_doc_att ;
1091 LOOP
1092
1093 FETCH c_doc_att INTO l_doc_att_rec ;
1094 EXIT WHEN c_doc_att%NOTFOUND ;
1095 OPEN c_doc_det(l_doc_att_rec.document_id) ;
1096 FETCH c_doc_det INTO l_doc_rec ;
1097 CLOSE c_doc_det ;
1098
1099 l_doc_attach_rec.datatype_id := l_doc_rec.datatype_id ;
1100 l_doc_attach_rec.category_id := l_doc_rec.category_id ;
1101 l_doc_attach_rec.security_type := l_doc_rec.security_type ;
1102 l_doc_attach_rec.publish_flag := l_doc_rec.publish_flag ;
1103 l_doc_attach_rec.description := l_doc_rec.description ;
1104 l_doc_attach_rec.file_name := l_doc_rec.file_name ;
1105 l_doc_attach_rec.media_id := l_doc_rec.media_id ;
1106 l_doc_attach_rec.file_size := l_doc_rec.doc_attribute2 ;
1107 --l_doc_attach_rec.attached_document_id
1108 l_doc_attach_rec.seq_num := l_doc_att_rec.seq_num ;
1109 l_doc_attach_rec.entity_name := p_src_act_type ;
1110 l_doc_attach_rec.PK1_VALUE := p_new_act_id ;
1111 l_doc_attach_rec.automatically_added_flag := l_doc_att_rec.automatically_added_flag ;
1112 l_doc_attach_rec.short_text := l_doc_rec.short_text ;
1113
1114 --dbms_output.put_line('Data Type = ' || l_doc_rec.datatype_id);
1115 --dbms_output.put_line('MEDIA ID = ' || l_doc_rec.media_id);
1116
1117 if l_doc_rec.datatype_id = 1
1118 then
1119 open c_short_txt(l_doc_rec.media_id);
1120 fetch c_short_txt into l_short_text;
1121 close c_short_txt;
1122 --dbms_output.put_line('Short Text = ' || l_short_text);
1123 l_doc_attach_rec.short_text := l_short_text;
1124 l_doc_attach_rec.media_id := null;
1125 end if;
1126
1127 l_doc_attach_rec.attachment_type := l_doc_rec.att_type;
1128 l_doc_attach_rec.language := l_doc_rec.language ;
1129
1130 AMS_Attachment_PVT.Create_Fnd_Attachment(
1131 p_api_version_number => l_api_version,
1132 p_init_msg_list => FND_API.g_false,
1133 p_commit => FND_API.g_false,
1134 p_validation_level => FND_API.g_valid_level_full,
1135 x_return_status => l_return_status,
1136 x_msg_count => x_msg_count,
1137 x_msg_data => l_msg_data,
1138 p_fnd_attachment_rec => l_doc_attach_rec,
1139 x_document_id => l_doc_attach_rec.document_id,
1140 x_attached_document_id => l_dummy_id
1141 );
1142
1143
1144 IF l_return_status = fnd_api.g_ret_sts_error
1145 OR l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1146 FOR l_counter IN 1 .. x_msg_count
1147 LOOP
1148 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
1149 l_stmt_num := 2;
1150 p_errnum := 1;
1151 p_errmsg := substr(l_mesg_text||' , '||
1152 TO_CHAR (l_stmt_num) ||
1153 ' , ' || '): ' || l_counter ||
1154 ' OF ' || x_msg_count, 1, 4000);
1155 ams_cpyutility_pvt.write_log_mesg( p_src_act_type,
1156 p_new_act_id,
1157 p_errmsg,
1158 'ERROR'
1159 );
1160 END LOOP;
1161 -- If failed write a copy failed message in the log table
1162 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
1163 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
1164 l_mesg_text := fnd_message.get;
1165 p_errmsg := SUBSTR ( l_mesg_text ||
1166 ' - ' ||
1167 ams_cpyutility_pvt.get_attachment_name (
1168 attachments_rec.attachment_id),
1169 1, 4000);
1170 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
1171 p_src_act_id,
1172 p_errmsg,
1173 'ERROR');
1174 END IF;
1175
1176 -- Create jtf amv attachments
1177 OPEN attachments_cur(l_doc_att_rec.document_id) ;
1178 FETCH attachments_cur INTO attachments_rec ;
1179 CLOSE attachments_cur ;
1180
1181 BEGIN
1182 p_errcode := NULL;
1183 p_errnum := 0;
1184 p_errmsg := NULL;
1185 l_api_version := 1.0;
1186 l_return_status := NULL;
1187 x_msg_count := 0;
1188 l_msg_data := NULL;
1189 l_act_attachment_id := 0;
1190 attach_rec := temp_attach_rec;
1191 attach_rec.owner_user_id := attachments_rec.owner_user_id;
1192 attach_rec.attachment_used_by_id := p_new_act_id;
1193 attach_rec.attachment_used_by :=
1194 NVL(p_new_act_type,p_src_act_type);
1195 attach_rec.version := attachments_rec.version;
1196 attach_rec.enabled_flag := attachments_rec.enabled_flag;
1197 attach_rec.can_fulfill_electronic_flag :=
1198 attachments_rec.can_fulfill_electronic_flag;
1199 attach_rec.file_id := attachments_rec.file_id;
1200 attach_rec.file_name := attachments_rec.file_name;
1201 attach_rec.file_extension := attachments_rec.file_extension;
1202 attach_rec.keywords := attachments_rec.keywords;
1203 attach_rec.display_width := attachments_rec.display_width;
1204 attach_rec.display_height := attachments_rec.display_height;
1205 attach_rec.display_location := attachments_rec.display_location;
1206 attach_rec.link_to := attachments_rec.link_to;
1207 attach_rec.link_url := attachments_rec.link_url;
1208 attach_rec.send_for_preview_flag := attachments_rec.send_for_preview_flag;
1209 attach_rec.attachment_type := attachments_rec.attachment_type;
1210 attach_rec.language_code := attachments_rec.language_code;
1211 attach_rec.application_id := attachments_rec.application_id;
1212 attach_rec.description := attachments_rec.description;
1213 attach_rec.default_style_sheet := attachments_rec.default_style_sheet;
1214 attach_rec.display_url := attachments_rec.display_url;
1215 attach_rec.display_rule_id := attachments_rec.display_rule_id;
1216 attach_rec.display_program := attachments_rec.display_program;
1217 attach_rec.attribute_category := attachments_rec.attribute_category;
1218 attach_rec.attribute1 := attachments_rec.attribute1;
1219 attach_rec.attribute2 := attachments_rec.attribute2;
1220 attach_rec.attribute3 := attachments_rec.attribute3;
1221 attach_rec.attribute4 := attachments_rec.attribute4;
1222 attach_rec.attribute5 := attachments_rec.attribute5;
1223 attach_rec.attribute6 := attachments_rec.attribute6;
1224 attach_rec.attribute7 := attachments_rec.attribute7;
1225 attach_rec.attribute8 := attachments_rec.attribute8;
1226 attach_rec.attribute9 := attachments_rec.attribute9;
1227 attach_rec.attribute10 := attachments_rec.attribute10;
1228 attach_rec.attribute11 := attachments_rec.attribute11;
1229 attach_rec.attribute12 := attachments_rec.attribute12;
1230 attach_rec.attribute13 := attachments_rec.attribute13;
1231 attach_rec.attribute14 := attachments_rec.attribute14;
1232 attach_rec.attribute15 := attachments_rec.attribute15;
1233 attach_rec.default_style_sheet := attachments_rec.default_style_sheet;
1234 attach_rec.display_rule_id := attachments_rec.display_rule_id;
1235 attach_rec.display_program := attachments_rec.display_program;
1236 attach_rec.document_id := l_doc_attach_rec.document_id ;
1237
1238 jtf_amv_attachment_pub.create_act_attachment (
1239 p_api_version => l_api_version,
1240 x_return_status => l_return_status,
1241 x_msg_count => x_msg_count,
1242 x_msg_data => l_msg_data,
1243 p_act_attachment_rec => attach_rec,
1244 x_act_attachment_id => l_act_attachment_id
1245 );
1246
1247 IF l_return_status = fnd_api.g_ret_sts_error
1248 OR l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1249 FOR l_counter IN 1 .. x_msg_count
1250 LOOP
1251 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
1252 l_stmt_num := 2;
1253 p_errnum := 1;
1254 p_errmsg := substr(l_mesg_text||' , '||
1255 TO_CHAR (l_stmt_num) ||
1256 ' , ' || '): ' || l_counter ||
1257 ' OF ' || x_msg_count, 1, 4000);
1258 ams_cpyutility_pvt.write_log_mesg( p_src_act_type,
1259 p_new_act_id,
1260 p_errmsg,
1261 'ERROR'
1262 );
1263 END LOOP;
1264 -- If failed write a copy failed message in the log table
1265 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
1266 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
1267 l_mesg_text := fnd_message.get;
1268 p_errmsg := SUBSTR ( l_mesg_text ||
1269 ' - ' ||
1270 ams_cpyutility_pvt.get_attachment_name (
1271 attachments_rec.attachment_id),
1272 1, 4000);
1273 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
1274 p_src_act_id,
1275 p_errmsg,
1276 'ERROR');
1277 END IF;
1278 END ;
1279
1280
1281
1282 END LOOP ;
1283 CLOSE c_doc_att ;
1284
1285
1286 /* Following code is modified by ptendulk on 18 Oct-2001
1287 As the attachments is changed.
1288 FOR attachments_rec IN attachments_cur
1289 LOOP
1290 BEGIN
1291 p_errcode := NULL;
1292 p_errnum := 0;
1293 p_errmsg := NULL;
1294 l_api_version := 1.0;
1295 l_return_status := NULL;
1296 x_msg_count := 0;
1297 l_msg_data := NULL;
1298 l_act_attachment_id := 0;
1299 attach_rec := temp_attach_rec;
1300 attach_rec.owner_user_id := attachments_rec.owner_user_id;
1301 attach_rec.attachment_used_by_id := p_new_act_id;
1302 attach_rec.attachment_used_by :=
1303 NVL(p_new_act_type,p_src_act_type);
1304 attach_rec.version := attachments_rec.version;
1305 attach_rec.enabled_flag := attachments_rec.enabled_flag;
1306 attach_rec.can_fulfill_electronic_flag :=
1307 attachments_rec.can_fulfill_electronic_flag;
1308 attach_rec.file_id := attachments_rec.file_id;
1309 attach_rec.file_name := attachments_rec.file_name;
1310 attach_rec.file_extension := attachments_rec.file_extension;
1311 attach_rec.keywords := attachments_rec.keywords;
1312 attach_rec.display_width := attachments_rec.display_width;
1313 attach_rec.display_height := attachments_rec.display_height;
1314 attach_rec.display_location := attachments_rec.display_location;
1315 attach_rec.link_to := attachments_rec.link_to;
1316 attach_rec.link_url := attachments_rec.link_url;
1317 attach_rec.send_for_preview_flag := attachments_rec.send_for_preview_flag;
1318 attach_rec.attachment_type := attachments_rec.attachment_type;
1319 attach_rec.language_code := attachments_rec.language_code;
1320 attach_rec.application_id := attachments_rec.application_id;
1321 attach_rec.description := attachments_rec.description;
1322 attach_rec.default_style_sheet := attachments_rec.default_style_sheet;
1323 attach_rec.display_url := attachments_rec.display_url;
1324 attach_rec.display_rule_id := attachments_rec.display_rule_id;
1325 attach_rec.display_program := attachments_rec.display_program;
1326 attach_rec.attribute_category := attachments_rec.attribute_category;
1327 attach_rec.attribute1 := attachments_rec.attribute1;
1328 attach_rec.attribute2 := attachments_rec.attribute2;
1329 attach_rec.attribute3 := attachments_rec.attribute3;
1330 attach_rec.attribute4 := attachments_rec.attribute4;
1331 attach_rec.attribute5 := attachments_rec.attribute5;
1332 attach_rec.attribute6 := attachments_rec.attribute6;
1333 attach_rec.attribute7 := attachments_rec.attribute7;
1334 attach_rec.attribute8 := attachments_rec.attribute8;
1335 attach_rec.attribute9 := attachments_rec.attribute9;
1336 attach_rec.attribute10 := attachments_rec.attribute10;
1337 attach_rec.attribute11 := attachments_rec.attribute11;
1338 attach_rec.attribute12 := attachments_rec.attribute12;
1339 attach_rec.attribute13 := attachments_rec.attribute13;
1340 attach_rec.attribute14 := attachments_rec.attribute14;
1341 attach_rec.attribute15 := attachments_rec.attribute15;
1342 attach_rec.default_style_sheet := attachments_rec.default_style_sheet;
1343 attach_rec.display_rule_id := attachments_rec.display_rule_id;
1344 attach_rec.display_program := attachments_rec.display_program;
1345 jtf_amv_attachment_pub.create_act_attachment (
1346 p_api_version => l_api_version,
1347 x_return_status => l_return_status,
1348 x_msg_count => x_msg_count,
1349 x_msg_data => l_msg_data,
1350 p_act_attachment_rec => attach_rec,
1351 x_act_attachment_id => l_act_attachment_id
1352 );
1353
1354 IF l_return_status = fnd_api.g_ret_sts_error
1355 OR l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1356 FOR l_counter IN 1 .. x_msg_count
1357 LOOP
1358 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
1359 l_stmt_num := 2;
1360 p_errnum := 1;
1361 p_errmsg := substr(l_mesg_text||' , '||
1362 TO_CHAR (l_stmt_num) ||
1363 ' , ' || '): ' || l_counter ||
1364 ' OF ' || x_msg_count, 1, 4000);
1365 ams_cpyutility_pvt.write_log_mesg( p_src_act_type,
1366 p_new_act_id,
1367 p_errmsg,
1368 'ERROR'
1369 );
1370 END LOOP;
1371 -- If failed write a copy failed message in the log table
1372 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
1373 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
1374 l_mesg_text := fnd_message.get;
1375 p_errmsg := SUBSTR ( l_mesg_text ||
1376 ' - ' ||
1377 ams_cpyutility_pvt.get_attachment_name (
1378 attachments_rec.attachment_id),
1379 1, 4000);
1380 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
1381 p_src_act_id,
1382 p_errmsg,
1383 'ERROR');
1384 END IF;
1385
1386 AMS_ObjectAttribute_PVT.modify_object_attribute(
1387 p_api_version => l_api_version,
1388 p_init_msg_list => FND_API.g_false,
1389 p_commit => FND_API.g_false,
1390 p_validation_level => FND_API.g_valid_level_full,
1391 x_return_status => l_return_status,
1392 x_msg_count => x_msg_count,
1393 x_msg_data => l_msg_data,
1394 p_object_type => p_src_act_type,
1395 p_object_id => p_new_act_id ,
1396 p_attr => 'ATCH',
1397 p_attr_defined_flag => 'Y'
1398 );
1399 IF l_return_status = FND_API.g_ret_sts_error THEN
1400 RAISE FND_API.g_exc_error;
1401 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1402 RAISE FND_API.g_exc_unexpected_error;
1403 END IF;
1404 fnd_message.set_name ('AMS', 'AMS_END_COPY_ELEMENTS');
1405 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
1406 fnd_message.set_token('ELEMENT_NAME',' ' ,TRUE);
1407 l_mesg_text := fnd_message.get;
1408 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
1409 p_src_act_id,
1410 l_mesg_text,
1411 'GENERAL'
1412 );
1413 EXCEPTION
1414 WHEN OTHERS
1415 THEN
1416 p_errcode := SQLCODE;
1417 p_errnum := 3;
1418 l_stmt_num := 4;
1419 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR');
1420 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
1421 l_mesg_text := fnd_message.get;
1422 p_errmsg := SUBSTR ( l_mesg_text || ',' ||
1423 TO_CHAR (l_stmt_num) || ',' || '): ' ||
1424 p_errcode || SQLERRM, 1, 4000);
1425 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
1426 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
1427 l_mesg_text := fnd_message.get;
1428 p_errmsg := SUBSTR ( l_mesg_text ||
1429 ams_cpyutility_pvt.get_attachment_name (
1430 attachments_rec.attachment_id) ||
1431 p_errmsg, 1, 4000);
1432 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
1433 p_src_act_id,
1434 p_errmsg,
1435 'ERROR'
1436 );
1437 END;
1438 END LOOP;
1439 */
1440
1441 EXCEPTION
1442 WHEN OTHERS
1443 THEN
1444 p_errcode := SQLCODE;
1445 p_errnum := 4;
1446 l_stmt_num := 5;
1447 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR3');
1448 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
1449 l_mesg_text := fnd_message.get;
1450 p_errmsg := SUBSTR ( l_mesg_text || ' , ' ||
1451 TO_CHAR (l_stmt_num) || ' , ' || '): ' ||
1452 p_errcode || SQLERRM, 1, 4000);
1453 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
1454 p_src_act_id ,
1455 p_errmsg,
1456 'ERROR'
1457 );
1458 END copy_act_attachments;
1459 --
1460 PROCEDURE copy_act_access (
1461 p_src_act_type IN VARCHAR2,
1462 p_new_act_type IN VARCHAR2 := NULL,
1463 p_src_act_id IN NUMBER,
1464 p_new_act_id IN NUMBER,
1465 p_errnum OUT NOCOPY NUMBER,
1466 p_errcode OUT NOCOPY VARCHAR2,
1467 p_errmsg OUT NOCOPY VARCHAR2
1468 )
1469 IS
1470 -- PL/SQL Block
1471 l_stmt_num NUMBER;
1472 l_name VARCHAR2 (80);
1473 l_mesg_text VARCHAR2 (2000);
1474 l_api_version NUMBER;
1475 l_return_status VARCHAR2 (1);
1476 x_msg_count NUMBER;
1477 l_msg_data VARCHAR2 (512);
1478 l_act_access_id NUMBER;
1479 l_access_rec ams_access_pvt.access_rec_type;
1480 temp_access_rec ams_access_pvt.access_rec_type;
1481
1482 CURSOR access_cur
1483 IS
1484 SELECT *
1485 FROM ams_act_access a
1486 WHERE act_access_to_object_id = p_src_act_id
1487 AND arc_act_access_to_object = p_src_act_type
1488 AND a.delete_flag = 'N'
1489 AND NOT EXISTS (select 1 from ams_act_access b
1490 WHERE b.act_access_to_object_id = p_new_act_id
1491 AND b.arc_act_access_to_object = p_new_act_type
1492 AND a.user_or_role_id = b.user_or_role_id
1493 AND a.arc_user_or_role_type = b.arc_user_or_role_type
1494 AND b.delete_flag = 'N'
1495 ) ;
1496 BEGIN
1497 p_errcode := NULL;
1498 p_errnum := 0;
1499 p_errmsg := NULL;
1500 fnd_message.set_name ('AMS', 'AMS_COPY_ELEMENTS');
1501 fnd_message.set_token ('ELEMENTS', 'AMS_COPY_ACCESS', TRUE);
1502 l_mesg_text := fnd_message.get;
1503 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
1504 p_src_act_id,
1505 l_mesg_text,
1506 'GENERAL'
1507 );
1508 l_stmt_num := 1;
1509
1510 FOR access_rec IN access_cur
1511 LOOP
1512 BEGIN
1513 p_errcode := NULL;
1514 p_errnum := 0;
1515 p_errmsg := NULL;
1516 l_api_version := 1.0;
1517 l_return_status := NULL;
1518 x_msg_count := 0;
1519 l_msg_data := NULL;
1520 l_act_access_id := 0;
1521 l_access_rec := temp_access_rec;
1522 l_access_rec.act_access_to_object_id := p_new_act_id;
1523 l_access_rec.arc_act_access_to_object :=
1524 NVL(p_new_act_type,p_src_act_type);
1525 l_access_rec.active_to_date := NULL;
1526 l_access_rec.active_from_date := SYSDATE;
1527 l_access_rec.user_or_role_id := access_rec.user_or_role_id;
1528 l_access_rec.arc_user_or_role_type :=
1529 access_rec.arc_user_or_role_type;
1530
1531 --sunkumar bug# 3064251 11-AUG-2003
1532 l_access_rec.owner_flag :=
1533 access_rec.owner_flag;
1534 -----------------clarify access-------------------------------------
1535 -- l_access_rec.ADMIN_FLAG
1536
1537
1538
1539 ams_access_pvt.create_access ( p_api_version => l_api_version,
1540 p_init_msg_list => fnd_api.g_true,
1541 x_return_status => l_return_status,
1542 x_msg_count => x_msg_count,
1543 x_msg_data => l_msg_data,
1544 p_access_rec => l_access_rec,
1545 x_access_id => l_act_access_id
1546 );
1547
1548
1549 IF l_return_status = fnd_api.g_ret_sts_error
1550 OR l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1551 FOR l_counter IN 1 .. x_msg_count
1552 LOOP
1553 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
1554 l_stmt_num := 2;
1555 p_errnum := 1;
1556 p_errmsg := substr(l_mesg_text||' , '|| TO_CHAR (l_stmt_num) ||
1557 ' , ' || '): ' || l_counter ||
1558 ' OF ' || x_msg_count, 1, 4000);
1559 ams_cpyutility_pvt.write_log_mesg( p_src_act_type,
1560 p_src_act_id,
1561 p_errmsg,
1562 'ERROR'
1563 );
1564 END LOOP;
1565 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
1566 fnd_message.set_token ('ELEMENTS', 'AMS_COPY_ACCESS', TRUE);
1567 l_mesg_text := fnd_message.get;
1568 p_errmsg := SUBSTR (l_mesg_text || access_rec.user_or_role_id, 1, 4000);
1569 ams_cpyutility_pvt.write_log_mesg (
1570 p_src_act_type,
1571 p_src_act_id,
1572 p_errmsg,
1573 'ERROR'
1574 );
1575 END IF;
1576 EXCEPTION
1577 WHEN OTHERS
1578 THEN
1579 p_errcode := SQLCODE;
1580 l_stmt_num := 3;
1581 p_errnum := 4;
1582 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR');
1583 fnd_message.set_token ('ELEMENTS', 'AMS_COPY_ACCESS', TRUE);
1584 l_mesg_text := fnd_message.get;
1585 p_errmsg := SUBSTR ( l_mesg_text || ',' ||
1586 TO_CHAR (l_stmt_num) || ',' || '): ' ||
1587 p_errcode || SQLERRM, 1, 4000);
1588 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
1589 fnd_message.set_token ('ELEMENTS', 'AMS_COPY_ACCESS', TRUE);
1590 l_mesg_text := fnd_message.get;
1591 p_errmsg := SUBSTR ( l_mesg_text ||
1592 access_rec.user_or_role_id || p_errmsg,
1593 1, 4000);
1594 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
1595 p_src_act_id,
1596 p_errmsg,
1597 'ERROR'
1598 );
1599 END;
1600 END LOOP;
1601 EXCEPTION
1602 WHEN OTHERS
1603 THEN
1604 p_errcode := SQLCODE;
1605 p_errnum := 4;
1606 l_stmt_num := 5;
1607 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR3');
1608 fnd_message.set_token ('ELEMENTS', 'AMS_COPY_ACCESS', TRUE);
1609 l_mesg_text := fnd_message.get;
1610 p_errmsg := SUBSTR ( l_mesg_text || '): ' ||
1611 TO_CHAR (l_stmt_num) || ',' || '): ' ||
1612 p_errcode || SQLERRM, 1, 4000);
1613 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
1614 p_src_act_id,
1615 p_errmsg,
1616 'ERROR'
1617 );
1618 END copy_act_access;
1619
1620 PROCEDURE copy_act_market_segments (
1621 p_src_act_type IN VARCHAR2,
1622 p_new_act_type IN VARCHAR2 := NULL,
1623 p_src_act_id IN NUMBER,
1624 p_new_act_id IN NUMBER,
1625 p_errnum OUT NOCOPY NUMBER,
1626 p_errcode OUT NOCOPY VARCHAR2,
1627 p_errmsg OUT NOCOPY VARCHAR2
1628 )
1629 IS
1630 -- PL/SQL Block
1631 l_stmt_num NUMBER;
1632 l_name VARCHAR2 (80);
1633 l_mesg_text VARCHAR2 (2000);
1634 l_api_version NUMBER;
1635 l_return_status VARCHAR2 (1);
1636 x_msg_count NUMBER;
1637 l_msg_data VARCHAR2 (512);
1638 l_act_segment_id NUMBER;
1639 l_segments_rec ams_act_market_segments_pvt.mks_rec_type;
1640 tmp_segments_rec ams_act_market_segments_pvt.mks_rec_type;
1641 l_lookup_meaning VARCHAR2 (80);
1642
1643 CURSOR segments_cur IS
1644 SELECT *
1645 FROM ams_act_market_segments
1646 WHERE act_market_segment_used_by_id = p_src_act_id
1647 AND arc_act_market_segment_used_by = p_src_act_type;
1648
1649 l_segment_id number;
1650 l_segment_name varchar2(240);
1651 CURSOR c_segment_name (l_segment_id in number ) is
1652 SELECT cell_name
1653 from ams_cells_vl
1654 where cell_id = l_segment_id ;
1655 BEGIN
1656 p_errcode := NULL;
1657 p_errnum := 0;
1658 p_errmsg := NULL;
1659 ams_utility_pvt.get_lookup_meaning ( 'AMS_SYS_ARC_QUALIFIER',
1660 'CELL',
1661 l_return_status,
1662 l_lookup_meaning
1663 );
1664 fnd_message.set_name ('AMS', 'AMS_COPY_ELEMENTS');
1665 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
1666 l_mesg_text := fnd_message.get;
1667 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
1668 p_src_act_id,
1669 l_mesg_text,
1670 'GENERAL'
1671 );
1672 l_stmt_num := 1;
1673
1674 FOR segments_rec IN segments_cur
1675 LOOP
1676 BEGIN
1677 l_segments_rec := tmp_segments_rec;
1678 l_segments_rec.object_version_number := 1;
1679 l_segments_rec.act_market_segment_used_by_id := p_new_act_id;
1680 l_segments_rec.arc_act_market_segment_used_by :=
1681 NVL(p_new_act_type,p_src_act_type);
1682 l_segments_rec.market_segment_id := segments_rec.market_segment_id;
1683 l_segments_rec.attribute_category := segments_rec.attribute_category;
1684 l_segments_rec.attribute1 := segments_rec.attribute1;
1685 l_segments_rec.attribute2 := segments_rec.attribute2;
1686 l_segments_rec.attribute3 := segments_rec.attribute3;
1687 l_segments_rec.attribute4 := segments_rec.attribute4;
1688 l_segments_rec.attribute5 := segments_rec.attribute5;
1689 l_segments_rec.attribute6 := segments_rec.attribute6;
1690 l_segments_rec.attribute7 := segments_rec.attribute7;
1691 l_segments_rec.attribute8 := segments_rec.attribute8;
1692 l_segments_rec.attribute9 := segments_rec.attribute9;
1693 l_segments_rec.attribute10 := segments_rec.attribute10;
1694 l_segments_rec.attribute11 := segments_rec.attribute11;
1695 l_segments_rec.attribute12 := segments_rec.attribute12;
1696 l_segments_rec.attribute13 := segments_rec.attribute13;
1697 l_segments_rec.attribute14 := segments_rec.attribute14;
1698 l_segments_rec.attribute15 := segments_rec.attribute15;
1699 l_segments_rec.segment_type := segments_rec.segment_type;
1700 -- 11/30/2001 yzhao: add exclude_flag and group_code
1701 l_segments_rec.exclude_flag := segments_rec.exclude_flag;
1702 l_segments_rec.group_code := segments_rec.group_code;
1703 -- l_segments_rec.eligibility_type := segments_rec.eligibility_type;
1704 -- l_segments_rec.terr_hierarchy_id := segments_rec.terr_hierarchy_id;
1705 l_api_version := 1.0;
1706 l_act_segment_id := 0;
1707 ams_act_market_segments_pvt.create_market_segments (
1708 p_api_version => l_api_version,
1709 p_init_msg_list => fnd_api.g_true,
1710 x_return_status => l_return_status,
1711 x_msg_count => x_msg_count,
1712 x_msg_data => l_msg_data,
1713 p_mks_rec => l_segments_rec,
1714 x_act_mks_id => l_act_segment_id
1715 );
1716
1717
1718 IF l_return_status = fnd_api.g_ret_sts_error
1719 OR l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1720 FOR l_counter IN 1 .. x_msg_count
1721 LOOP
1722 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
1723 l_stmt_num := 2;
1724 p_errnum := 1;
1725 p_errmsg := substr(l_mesg_text||' , '|| TO_CHAR (l_stmt_num) ||
1726 ' , ' || '): ' || l_counter ||
1727 ' OF ' || x_msg_count, 1, 4000);
1728 ams_cpyutility_pvt.write_log_mesg( p_src_act_type,
1729 p_src_act_id,
1730 p_errmsg,
1731 'ERROR'
1732 );
1733 END LOOP;
1734 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
1735 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
1736 l_mesg_text := fnd_message.get;
1737 p_errmsg := SUBSTR ( l_mesg_text || ' - ' ||
1738 ams_cpyutility_pvt.get_segment_name (
1739 segments_rec.market_segment_id),
1740 1, 4000);
1741 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
1742 p_src_act_id,
1743 p_errmsg,
1744 'ERROR'
1745 );
1746 ELSE
1747 open c_segment_name ( l_segments_rec.market_segment_id );
1748 fetch c_segment_name into l_segment_name ;
1749 close c_segment_name ;
1750 fnd_message.set_name ('AMS', 'AMS_END_COPY_ELEMENTS');
1751 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
1752 fnd_message.set_token('ELEMENT_NAME',l_segment_name,TRUE);
1753 l_mesg_text := fnd_message.get;
1754 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
1755 p_src_act_id,
1756 l_mesg_text,
1757 'GENERAL'
1758 );
1759
1760 END IF;
1761 EXCEPTION
1762 WHEN OTHERS
1763 THEN
1764 p_errcode := SQLCODE;
1765 p_errnum := 3;
1766 l_stmt_num := 4;
1767 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR');
1768 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
1769 l_mesg_text := fnd_message.get;
1770 p_errmsg := SUBSTR ( l_mesg_text || ',' ||
1771 TO_CHAR (l_stmt_num) || ',' || '): ' ||
1772 p_errcode || SQLERRM, 1, 4000);
1773 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
1774 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
1775 l_mesg_text := fnd_message.get;
1776 p_errmsg := SUBSTR ( l_mesg_text ||
1777 ams_cpyutility_pvt.get_segment_name (
1778 segments_rec.market_segment_id
1779 ) || p_errmsg, 1, 4000);
1780 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
1781 p_src_act_id,
1782 p_errmsg,
1783 'ERROR'
1784 );
1785 END;
1786 END LOOP;
1787 EXCEPTION
1788 WHEN OTHERS
1789 THEN
1790 p_errcode := SQLCODE;
1791 p_errnum := 4;
1792 l_stmt_num := 5;
1793 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR3');
1794 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
1795 l_mesg_text := fnd_message.get;
1796 p_errmsg := SUBSTR ( l_mesg_text ||
1797 TO_CHAR (l_stmt_num) || ',' || '): ' ||
1798 p_errcode || SQLERRM, 1, 4000);
1799 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
1800 p_src_act_id,
1801 p_errmsg,
1802 'ERROR'
1803 );
1804 END copy_act_market_segments;
1805
1806 PROCEDURE copy_act_categories (
1807 p_src_act_type IN VARCHAR2,
1808 p_new_act_type IN VARCHAR2 := NULL,
1809 p_src_act_id IN NUMBER,
1810 p_new_act_id IN NUMBER,
1811 p_errnum OUT NOCOPY NUMBER,
1812 p_errcode OUT NOCOPY VARCHAR2,
1813 p_errmsg OUT NOCOPY VARCHAR2
1814 )
1815 IS
1816 -- PL/SQL Block
1817 l_stmt_num NUMBER;
1818 l_name VARCHAR2 (80);
1819 l_mesg_text VARCHAR2 (2000);
1820 l_api_version NUMBER;
1821 l_return_status VARCHAR2 (1);
1822 x_msg_count NUMBER;
1823 l_msg_data VARCHAR2 (512);
1824 l_act_category_id NUMBER;
1825 l_categories_rec ams_actcategory_pvt.act_category_rec_type;
1826 temp_categories_rec ams_actcategory_pvt.act_category_rec_type;
1827 l_lookup_meaning VARCHAR2 (80);
1828
1829 CURSOR categories_cur IS
1830 SELECT *
1831 FROM ams_act_categories
1832 WHERE act_category_used_by_id = p_src_act_id
1833 AND arc_act_category_used_by = p_src_act_type;
1834 BEGIN
1835 p_errcode := NULL;
1836 p_errnum := 0;
1837 p_errmsg := NULL;
1838 ams_utility_pvt.get_lookup_meaning ( 'AMS_SYS_ARC_QUALIFIER',
1839 'CATG',
1840 l_return_status,
1841 l_lookup_meaning
1842 );
1843 fnd_message.set_name ('AMS', 'AMS_COPY_ELEMENTS');
1844 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
1845 l_mesg_text := fnd_message.get;
1846 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
1847 p_src_act_id,
1848 l_mesg_text,
1849 'GENERAL'
1850 );
1851 l_stmt_num := 1;
1852
1853 FOR categories_rec IN categories_cur
1854 LOOP
1855 BEGIN
1856 p_errcode := NULL;
1857 p_errnum := 0;
1858 p_errmsg := NULL;
1859 l_categories_rec := temp_categories_rec;
1860 l_categories_rec.object_version_number := 1;
1861 l_categories_rec.act_category_used_by_id := p_new_act_id;
1862 l_categories_rec.arc_act_category_used_by := NVL(p_new_act_type,p_src_act_type);
1863 l_categories_rec.category_id := categories_rec.category_id;
1864 l_categories_rec.attribute_category := categories_rec.attribute_category;
1865 l_categories_rec.attribute1 := categories_rec.attribute1;
1866 l_categories_rec.attribute2 := categories_rec.attribute2;
1867 l_categories_rec.attribute3 := categories_rec.attribute3;
1868 l_categories_rec.attribute4 := categories_rec.attribute4;
1869 l_categories_rec.attribute5 := categories_rec.attribute5;
1870 l_categories_rec.attribute6 := categories_rec.attribute6;
1871 l_categories_rec.attribute7 := categories_rec.attribute7;
1872 l_categories_rec.attribute8 := categories_rec.attribute8;
1873 l_categories_rec.attribute9 := categories_rec.attribute9;
1874 l_categories_rec.attribute10 := categories_rec.attribute10;
1875 l_categories_rec.attribute11 := categories_rec.attribute11;
1876 l_categories_rec.attribute12 := categories_rec.attribute12;
1877 l_categories_rec.attribute13 := categories_rec.attribute13;
1878 l_categories_rec.attribute14 := categories_rec.attribute14;
1879 l_categories_rec.attribute15 := categories_rec.attribute15;
1880 l_api_version := 1.0;
1881 l_return_status := NULL;
1882 x_msg_count := 0;
1883 l_msg_data := NULL;
1884 l_act_category_id := 0;
1885 ams_actcategory_pvt.create_act_category (
1886 p_api_version => l_api_version,
1887 p_init_msg_list => fnd_api.g_true,
1888 x_return_status => l_return_status,
1889 x_msg_count => x_msg_count,
1890 x_msg_data => l_msg_data,
1891 p_act_category_rec => l_categories_rec,
1892 x_act_category_id => l_act_category_id
1893 );
1894
1895
1896 IF l_return_status = fnd_api.g_ret_sts_error
1897 OR l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1898 FOR l_counter IN 1 .. x_msg_count
1899 LOOP
1900 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
1901 l_stmt_num := 2;
1902 p_errnum := 1;
1903 p_errmsg := substr(l_mesg_text||' , '|| TO_CHAR (l_stmt_num) ||
1904 ' , ' || '): ' || l_counter ||
1905 ' OF ' || x_msg_count, 1, 4000);
1906 ams_cpyutility_pvt.write_log_mesg( p_src_act_type,
1907 p_src_act_id,
1908 p_errmsg,
1909 'ERROR'
1910 );
1911 END LOOP;
1912 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
1913 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
1914 l_mesg_text := fnd_message.get;
1915 p_errmsg := SUBSTR ( l_mesg_text || ' - ' ||
1916 ams_cpyutility_pvt.get_category_name (
1917 categories_rec.category_id
1918 ), 1, 4000);
1919 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
1920 p_src_act_id,
1921 p_errmsg,
1922 'ERROR'
1923 );
1924 END IF;
1925 EXCEPTION
1926 WHEN OTHERS THEN
1927 p_errcode := SQLCODE;
1928 p_errnum := 3;
1929 l_stmt_num := 4;
1930 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR');
1931 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
1932 l_mesg_text := fnd_message.get;
1933 p_errmsg := SUBSTR ( l_mesg_text ||
1934 ',' || TO_CHAR (l_stmt_num) ||
1935 ',' || '): ' || p_errcode ||
1936 SQLERRM, 1, 4000);
1937 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
1938 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
1939 l_mesg_text := fnd_message.get;
1940 p_errmsg := SUBSTR ( l_mesg_text ||
1941 ams_cpyutility_pvt.get_category_name (
1942 categories_rec.category_id
1943 ) || p_errmsg, 1, 4000);
1944 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
1945 p_src_act_id,
1946 p_errmsg,
1947 'ERROR'
1948 );
1949 END;
1950 END LOOP;
1951 EXCEPTION
1952 WHEN OTHERS
1953 THEN
1954 p_errcode := SQLCODE;
1955 p_errnum := 4;
1956 l_stmt_num := 5;
1957 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR3');
1958 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
1959 l_mesg_text := fnd_message.get;
1960 p_errmsg := SUBSTR ( l_mesg_text || TO_CHAR (l_stmt_num) ||
1961 ',' || '): ' || p_errcode || SQLERRM,
1962 1, 4000);
1963 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
1964 p_src_act_id,
1965 p_errmsg,
1966 'ERROR'
1967 );
1968 END copy_act_categories;
1969
1970 PROCEDURE copy_act_delivery_method (
1971 p_src_act_type IN VARCHAR2,
1972 p_new_act_type IN VARCHAR2 := NULL,
1973 p_src_act_id IN NUMBER,
1974 p_new_act_id IN NUMBER,
1975 p_errnum OUT NOCOPY NUMBER,
1976 p_errcode OUT NOCOPY VARCHAR2,
1977 p_errmsg OUT NOCOPY VARCHAR2
1978 )
1979 IS
1980 -- PL/SQL Block
1981 l_stmt_num NUMBER;
1982 l_name VARCHAR2 (80);
1983 l_mesg_text VARCHAR2 (2000);
1984 l_api_version NUMBER;
1985 l_return_status VARCHAR2 (1);
1986 x_msg_count NUMBER;
1987 l_msg_data VARCHAR2 (512);
1988 l_act_deliv_method_id NUMBER;
1989 l_deliv_methods_rec ams_actdelvmethod_pvt.act_delvmethod_rec_type;
1990 temp_deliv_methods_rec ams_actdelvmethod_pvt.act_delvmethod_rec_type;
1991 l_lookup_meaning VARCHAR2 (80);
1992
1993 CURSOR deliv_method_cur
1994 IS
1995 SELECT *
1996 FROM ams_act_delivery_methods
1997 WHERE act_delivery_method_used_by_id = p_src_act_id
1998 AND arc_act_delivery_used_by = p_src_act_type;
1999 BEGIN
2000 p_errcode := NULL;
2001 p_errnum := 0;
2002 p_errmsg := NULL;
2003 fnd_message.set_name ('AMS', 'COPY_ACT_ELEMENTS');
2004 fnd_message.set_token ('ELEMENTS', 'AMS_COPY_DELIVMETHODS', TRUE);
2005 l_mesg_text := fnd_message.get;
2006 ams_cpyutility_pvt.write_log_mesg (
2007 p_src_act_type,
2008 p_src_act_id,
2009 l_mesg_text,
2010 'GENERAL'
2011 );
2012 l_stmt_num := 1;
2013
2014 FOR deliv_method_rec IN deliv_method_cur
2015 LOOP
2016 BEGIN
2017 p_errcode := NULL;
2018 p_errnum := 0;
2019 p_errmsg := NULL;
2020 l_deliv_methods_rec := temp_deliv_methods_rec;
2021 l_deliv_methods_rec.act_delivery_method_used_by_id := p_new_act_id;
2022 l_deliv_methods_rec.arc_act_delivery_used_by := NVL(p_new_act_type,p_src_act_type);
2023 l_deliv_methods_rec.delivery_media_type_code :=
2024 deliv_method_rec.delivery_media_type_code;
2025 l_deliv_methods_rec.attribute_category := deliv_method_rec.attribute_category;
2026 l_deliv_methods_rec.attribute1 := deliv_method_rec.attribute1;
2027 l_deliv_methods_rec.attribute2 := deliv_method_rec.attribute2;
2028 l_deliv_methods_rec.attribute3 := deliv_method_rec.attribute3;
2029 l_deliv_methods_rec.attribute4 := deliv_method_rec.attribute4;
2030 l_deliv_methods_rec.attribute5 := deliv_method_rec.attribute5;
2031 l_deliv_methods_rec.attribute6 := deliv_method_rec.attribute6;
2032 l_deliv_methods_rec.attribute7 := deliv_method_rec.attribute7;
2033 l_deliv_methods_rec.attribute8 := deliv_method_rec.attribute8;
2034 l_deliv_methods_rec.attribute9 := deliv_method_rec.attribute9;
2035 l_deliv_methods_rec.attribute10 := deliv_method_rec.attribute10;
2036 l_deliv_methods_rec.attribute11 := deliv_method_rec.attribute11;
2037 l_deliv_methods_rec.attribute12 := deliv_method_rec.attribute12;
2038 l_deliv_methods_rec.attribute13 := deliv_method_rec.attribute13;
2039 l_deliv_methods_rec.attribute14 := deliv_method_rec.attribute14;
2040 l_deliv_methods_rec.attribute15 := deliv_method_rec.attribute15;
2041 l_api_version := 1.0;
2042 l_return_status := NULL;
2043 x_msg_count := 0;
2044 l_msg_data := NULL;
2045 l_act_deliv_method_id := 0;
2046 ams_actdelvmethod_pvt.create_act_delvmethod (
2047 p_api_version => l_api_version,
2048 p_init_msg_list => fnd_api.g_true,
2049 x_return_status => l_return_status,
2050 x_msg_count => x_msg_count,
2051 x_msg_data => l_msg_data,
2052 p_act_delvmethod_rec => l_deliv_methods_rec,
2053 x_act_delvmethod_id => l_act_deliv_method_id
2054 );
2055
2056
2057 IF l_return_status = fnd_api.g_ret_sts_error
2058 OR l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2059 FOR l_counter IN 1 .. x_msg_count
2060 LOOP
2061 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
2062 l_stmt_num := 2;
2063 p_errnum := 1;
2064 p_errmsg := substr(l_mesg_text||' , '|| TO_CHAR (l_stmt_num) ||
2065 ' , ' || '): ' || l_counter ||
2066 ' OF ' || x_msg_count, 1, 4000);
2067 ams_cpyutility_pvt.write_log_mesg( p_src_act_type,
2068 p_src_act_id,
2069 p_errmsg,
2070 'ERROR'
2071 );
2072 END LOOP;
2073 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR');
2074 fnd_message.set_token ('ELEMENTS', 'AMS_COPY_DELIVMETHODS', TRUE);
2075 l_mesg_text := fnd_message.get;
2076 p_errmsg := SUBSTR ( l_mesg_text || ' - ' ||
2077 deliv_method_rec.activity_delivery_method_id,
2078 1, 4000);
2079 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
2080 p_src_act_id,
2081 p_errmsg,
2082 'ERROR'
2083 );
2084 END IF;
2085 EXCEPTION
2086 WHEN OTHERS THEN p_errcode := SQLCODE;
2087 p_errnum := 3;
2088 l_stmt_num := 4;
2089 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
2090 fnd_message.set_token ('ELEMENTS','AMS_COPY_DELIVMETHODS', TRUE);
2091
2092 l_mesg_text := fnd_message.get;
2093 p_errmsg := SUBSTR ( l_mesg_text || ',' || TO_CHAR (l_stmt_num)
2094 || ',' || '): ' || p_errcode || SQLERRM ||
2095 deliv_method_rec.activity_delivery_method_id,
2096 1, 4000);
2097 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
2098 p_src_act_id,
2099 p_errmsg,
2100 'ERROR'
2101 );
2102 END;
2103 END LOOP;
2104 EXCEPTION
2105 WHEN OTHERS
2106 THEN
2107 p_errcode := SQLCODE;
2108 p_errnum := 4;
2109 l_stmt_num := 5;
2110 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR3');
2111 fnd_message.set_token ('ELEMENTS','AMS_COPY_DELIVMETHODS', TRUE);
2112 l_mesg_text := fnd_message.get;
2113 p_errmsg := SUBSTR ( l_mesg_text || TO_CHAR (l_stmt_num) ||
2114 ',' || '): ' || p_errcode || SQLERRM, 1, 4000);
2115 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
2116 p_src_act_id,
2117 p_errmsg,
2118 'ERROR'
2119 );
2120 END copy_act_delivery_method;
2121
2122 PROCEDURE copy_deliv_kits (
2123 p_src_deli_id IN NUMBER,
2124 p_new_deliv_id IN NUMBER,
2125 p_errnum OUT NOCOPY NUMBER,
2126 p_errcode OUT NOCOPY VARCHAR2,
2127 p_errmsg OUT NOCOPY VARCHAR2
2128 )
2129 IS
2130 -- PL/SQL Block
2131 l_stmt_num NUMBER;
2132 l_name VARCHAR2 (80);
2133 l_rowcount NUMBER;
2134 l_errnum NUMBER;
2135 l_errcode VARCHAR2 (80);
2136 l_errmsg VARCHAR2 (120);
2137 l_api_version NUMBER;
2138 l_return_status VARCHAR2 (1);
2139 x_msg_count NUMBER;
2140 l_msg_data VARCHAR2 (512);
2141 l_deliv_kit_id NUMBER;
2142 l_mesg_text VARCHAR2 (2000);
2143 l_delivkit_item_rec ams_delivkititem_pvt.deliv_kit_item_rec_type;
2144
2145 CURSOR deliv_kit_cur IS
2146 SELECT *
2147 FROM ams_deliv_kit_items
2148 WHERE deliverable_kit_id = p_src_deli_id;
2149 BEGIN
2150 p_errcode := NULL;
2151 p_errnum := 0;
2152 p_errmsg := NULL;
2153 fnd_message.set_name ('AMS', 'COPY_ACT_ELEMENTS');
2154 fnd_message.set_token ('ELEMENTS', 'AMS_COPY_DELIV_KITS', TRUE);
2155 l_mesg_text := fnd_message.get;
2156 ams_cpyutility_pvt.write_log_mesg ('DELV',
2157 p_src_deli_id,
2158 l_mesg_text,
2159 'GENERAL');
2160 l_stmt_num := 1;
2161
2162 FOR deliv_kit_rec IN deliv_kit_cur
2163 LOOP
2164 BEGIN
2165 l_delivkit_item_rec.object_version_number :=
2166 deliv_kit_rec.object_version_number;
2167 l_delivkit_item_rec.deliverable_kit_id := p_new_deliv_id;
2168 l_delivkit_item_rec.deliverable_kit_part_id :=
2169 deliv_kit_rec.deliverable_kit_part_id;
2170 l_delivkit_item_rec.kit_part_included_from_kit_id :=
2171 deliv_kit_rec.kit_part_included_from_kit_id;
2172 l_delivkit_item_rec.quantity := deliv_kit_rec.quantity;
2173 l_delivkit_item_rec.attribute_category := deliv_kit_rec.attribute_category;
2174 l_delivkit_item_rec.attribute1 := deliv_kit_rec.attribute1;
2175 l_delivkit_item_rec.attribute2 := deliv_kit_rec.attribute2;
2176 l_delivkit_item_rec.attribute3 := deliv_kit_rec.attribute3;
2177 l_delivkit_item_rec.attribute4 := deliv_kit_rec.attribute4;
2178 l_delivkit_item_rec.attribute5 := deliv_kit_rec.attribute5;
2179 l_delivkit_item_rec.attribute6 := deliv_kit_rec.attribute6;
2180 l_delivkit_item_rec.attribute7 := deliv_kit_rec.attribute7;
2181 l_delivkit_item_rec.attribute8 := deliv_kit_rec.attribute8;
2182 l_delivkit_item_rec.attribute9 := deliv_kit_rec.attribute9;
2183 l_delivkit_item_rec.attribute10 := deliv_kit_rec.attribute10;
2184 l_delivkit_item_rec.attribute11 := deliv_kit_rec.attribute11;
2185 l_delivkit_item_rec.attribute12 := deliv_kit_rec.attribute12;
2186 l_delivkit_item_rec.attribute13 := deliv_kit_rec.attribute13;
2187 l_delivkit_item_rec.attribute14 := deliv_kit_rec.attribute14;
2188 l_delivkit_item_rec.attribute15 := deliv_kit_rec.attribute15;
2189 p_errcode := NULL;
2190 p_errnum := 0;
2191 p_errmsg := NULL;
2192 l_api_version := 1.0;
2193 l_return_status := NULL;
2194 x_msg_count := 0;
2195 l_msg_data := NULL;
2196 l_deliv_kit_id := 0;
2197 ams_delivkititem_pvt.create_deliv_kit_item (
2198 p_api_version => l_api_version,
2199 x_return_status => l_return_status,
2200 x_msg_count => x_msg_count,
2201 x_msg_data => l_msg_data,
2202 x_deliv_kit_item_id => l_deliv_kit_id,
2203 p_deliv_kit_item_rec => l_delivkit_item_rec
2204 );
2205 -- If failed creating then get all the messages for that Api frpom the message list and put it into the log table
2206 IF l_return_status = fnd_api.g_ret_sts_error
2207 OR l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2208 FOR l_counter IN 1 .. x_msg_count
2209 LOOP
2210 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
2211 l_stmt_num := 2;
2212 p_errnum := 1;
2213 p_errmsg := substr(l_mesg_text||' , '|| TO_CHAR (l_stmt_num) ||
2214 ' , ' || '): ' || l_counter ||
2215 ' OF ' || x_msg_count, 1, 4000);
2216 ams_cpyutility_pvt.write_log_mesg (
2217 'DELV',
2218 p_src_deli_id,
2219 p_errmsg,
2220 'ERROR'
2221 );
2222 END LOOP;
2223 ---- if error then right a copy log message to the log table
2224
2225 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
2226 fnd_message.set_token ('ELEMENTS','AMS_COPY_DELIV_KITS', TRUE);
2227 l_mesg_text := fnd_message.get;
2228 p_errmsg := SUBSTR (l_mesg_text || ' - '
2229 ||deliv_kit_rec.deliverable_kit_id , 1, 4000);
2230 ams_cpyutility_pvt.write_log_mesg ( 'DELV',
2231 p_src_deli_id,
2232 p_errmsg,
2233 'ERROR'
2234 );
2235 END IF;
2236 EXCEPTION
2237 WHEN OTHERS
2238 THEN
2239 p_errcode := SQLCODE;
2240 p_errnum := 3;
2241 l_stmt_num := 4;
2242 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR');
2243 fnd_message.set_token ('ELEMENTS','AMS_COPY_DELIV_KITS', TRUE);
2244 l_mesg_text := fnd_message.get;
2245 p_errmsg := SUBSTR ( l_mesg_text || TO_CHAR (l_stmt_num) ||
2246 '): ' || p_errcode || SQLERRM, 1, 4000);
2247 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
2248 fnd_message.set_token ('ELEMENTS','AMS_COPY_DELIV_KITS', TRUE);
2249 l_mesg_text := fnd_message.get;
2250 p_errmsg := l_mesg_text ||deliv_kit_rec.deliverable_kit_id
2251 ||p_errmsg;
2252 ams_cpyutility_pvt.write_log_mesg ( 'DELV',
2253 p_src_deli_id,
2254 p_errmsg,
2255 'ERROR'
2256 );
2257 END;
2258 END LOOP;
2259 EXCEPTION
2260 WHEN OTHERS
2261 THEN
2262 p_errcode := SQLCODE;
2263 p_errnum := 4;
2264 l_stmt_num := 5;
2265 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR3');
2266 fnd_message.set_token ('ELEMENTS','AMS_COPY_DELIV_KITS', TRUE);
2267 l_mesg_text := fnd_message.get;
2268 p_errmsg := SUBSTR ( l_mesg_text || TO_CHAR (l_stmt_num) ||
2269 ',' || '): ' || p_errcode || SQLERRM, 1, 4000);
2270
2271 ams_cpyutility_pvt.write_log_mesg ('DELV',
2272 p_src_deli_id,
2273 p_errmsg,
2274 'ERROR');
2275
2276 END copy_deliv_kits;
2277
2278 -- removed by soagrawa on 02-oct-2002
2279 -- refer to bug# 2605184
2280
2281 /*
2282 PROCEDURE copy_campaign_schedules (
2283 p_api_version IN NUMBER,
2284 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2285 x_return_status OUT NOCOPY VARCHAR2,
2286 x_msg_count OUT NOCOPY NUMBER,
2287 x_msg_data OUT NOCOPY VARCHAR2,
2288 x_campaign_schedule_id OUT NOCOPY NUMBER,
2289 p_src_camp_schedule_id IN NUMBER,
2290 p_new_camp_id IN NUMBER
2291 )
2292 IS
2293 l_api_version CONSTANT NUMBER := 1.0;
2294 l_api_name CONSTANT VARCHAR2 (30) := 'copy_campaign_schedules';
2295 l_full_name CONSTANT VARCHAR2 (60) := 'g_pkg_name'||'.'|| l_api_name;
2296 l_return_status VARCHAR2 (1);
2297 l_name VARCHAR2 (80);
2298 l_msg_data VARCHAR2 (512);
2299 -- Campaign Schedule Id
2300 l_camp_sch_id NUMBER;
2301 p_camp_csch_rec ams_campaignschedule_pvt.csch_rec_type;
2302 l_mesg_text VARCHAR2 (2000);
2303 p_errmsg VARCHAR2 (3000);
2304 l_camp_sch_rec ams_campaign_schedules%ROWTYPE;
2305 l_errcode VARCHAR2 (80);
2306 l_errnum NUMBER;
2307 l_errmsg VARCHAR2 (3000);
2308 l_lookup_meaning VARCHAR2 (80);
2309 BEGIN
2310 SAVEPOINT copy_campaign_schedules;
2311 IF (AMS_DEBUG_HIGH_ON) THEN
2312
2313 ams_utility_pvt.debug_message (l_full_name || ': start');
2314 END IF;
2315
2316 IF fnd_api.to_boolean (p_init_msg_list) THEN
2317 fnd_msg_pub.initialize;
2318 END IF;
2319
2320 IF NOT fnd_api.compatible_api_call (
2321 l_api_version,
2322 p_api_version,
2323 l_api_name,
2324 g_pkg_name
2325 )
2326 THEN
2327 RAISE fnd_api.g_exc_unexpected_error;
2328 END IF;
2329
2330 x_return_status := fnd_api.g_ret_sts_success;
2331 ----------------------- insert -----------------------
2332 IF (AMS_DEBUG_HIGH_ON) THEN
2333
2334 ams_utility_pvt.debug_message (l_full_name || ': start');
2335 END IF;
2336
2337 ams_utility_pvt.get_lookup_meaning ( 'AMS_SYS_ARC_QUALIFIER',
2338 'CSCH',
2339 l_return_status,
2340 l_lookup_meaning
2341 );
2342 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2343 RAISE FND_API.G_EXC_ERROR ;
2344 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2345 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2346 END IF;
2347
2348 -- General Message saying copying has started
2349 fnd_message.set_name ('AMS', 'COPY_ACT_ELEMENTS');
2350 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
2351 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
2352
2353 -- Writing to the Pl/SQLtable
2354 ams_cpyutility_pvt.write_log_mesg ( 'CAMP',
2355 p_src_camp_schedule_id,
2356 l_mesg_text,
2357 'GENERAL'
2358 );
2359 x_msg_count := 0;
2360 l_msg_data := NULL;
2361
2362 -- selects the campaign to copy
2363 SELECT *
2364 INTO l_camp_sch_rec
2365 FROM ams_campaign_schedules
2366 WHERE campaign_schedule_id = p_src_camp_schedule_id;
2367
2368 p_camp_csch_rec.object_version_number := 1;
2369
2370 -- p_camp_csch_rec.user_status_id
2371 p_camp_csch_rec.status_code := 'NEW';
2372
2373 --- status date is defaulted to sysdate -----------------
2374 p_camp_csch_rec.status_date := SYSDATE;
2375
2376 --- new source code has to be generated ----------------------
2377
2378 p_camp_csch_rec.forecasted_start_date_time := NULL;
2379 p_camp_csch_rec.forecasted_end_date_time := NULL;
2380 p_camp_csch_rec.actual_start_date_time := NULL; ---
2381 p_camp_csch_rec.actual_end_date_time := NULL;
2382 p_camp_csch_rec.frequency := l_camp_sch_rec.frequency;
2383 p_camp_csch_rec.frequency_uom_code := l_camp_sch_rec.frequency_uom_code;
2384 p_camp_csch_rec.activity_offer_id := l_camp_sch_rec.activity_offer_id;
2385 p_camp_csch_rec.deliverable_id := l_camp_sch_rec.deliverable_id;
2386 p_camp_csch_rec.attribute_category := l_camp_sch_rec.attribute_category;
2387 p_camp_csch_rec.attribute1 := l_camp_sch_rec.attribute1;
2388 p_camp_csch_rec.attribute2 := l_camp_sch_rec.attribute2;
2389 p_camp_csch_rec.attribute3 := l_camp_sch_rec.attribute3;
2390 p_camp_csch_rec.attribute4 := l_camp_sch_rec.attribute4;
2391 p_camp_csch_rec.attribute5 := l_camp_sch_rec.attribute5;
2392 p_camp_csch_rec.attribute6 := l_camp_sch_rec.attribute6;
2393 p_camp_csch_rec.attribute7 := l_camp_sch_rec.attribute7;
2394 p_camp_csch_rec.attribute8 := l_camp_sch_rec.attribute8;
2395 p_camp_csch_rec.attribute9 := l_camp_sch_rec.attribute9;
2396 p_camp_csch_rec.attribute10 := l_camp_sch_rec.attribute10;
2397 p_camp_csch_rec.attribute11 := l_camp_sch_rec.attribute11;
2398 p_camp_csch_rec.attribute12 := l_camp_sch_rec.attribute12;
2399 p_camp_csch_rec.attribute13 := l_camp_sch_rec.attribute13;
2400 p_camp_csch_rec.attribute14 := l_camp_sch_rec.attribute14;
2401 p_camp_csch_rec.attribute15 := l_camp_sch_rec.attribute15;
2402 p_camp_csch_rec.triggered_flag := l_camp_sch_rec.triggered_flag;
2403 p_camp_csch_rec.active_flag := l_camp_sch_rec.active_flag;
2404 p_camp_csch_rec.inbound_dscript_name:=l_camp_sch_rec.inbound_dscript_name;
2405 p_camp_csch_rec.outbound_dscript_name := l_camp_sch_rec.outbound_dscript_name;
2406 p_camp_csch_rec.inbound_url := l_camp_sch_rec.inbound_url;
2407 p_camp_csch_rec.inbound_email_id := l_camp_sch_rec.inbound_email_id;
2408 p_camp_csch_rec.inbound_phone_no := l_camp_sch_rec.inbound_phone_no;
2409 ams_campaignschedule_pvt.create_schedule
2410 ( p_api_version => l_api_version,
2411 x_return_status => l_return_status,
2412 x_msg_count => x_msg_count,
2413 x_msg_data => l_msg_data,
2414 x_csch_id => x_campaign_schedule_id,
2415 p_csch_rec => p_camp_csch_rec
2416 );
2417
2418 IF l_return_status = fnd_api.g_ret_sts_unexp_error OR
2419 l_return_status = fnd_api.g_ret_sts_error THEN
2420 FOR l_counter IN 1 .. x_msg_count
2421 LOOP
2422 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
2423 p_errmsg := substr(l_mesg_text || '): ' || l_counter ||
2424 ' OF ' || x_msg_count, 1, 3000);
2425 ams_cpyutility_pvt.write_log_mesg ( 'CSCH',
2426 p_src_camp_schedule_id,
2427 p_errmsg,
2428 'ERROR'
2429 );
2430 END LOOP;
2431 ams_cpyutility_pvt.write_log_mesg ( 'CSCH',
2432 p_src_camp_schedule_id,
2433 p_errmsg,
2434 'ERROR'
2435 );
2436
2437 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
2438 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
2439 l_mesg_text := fnd_message.get;
2440 p_errmsg := l_mesg_text ||
2441 ams_utility_pvt.get_object_name ('CAMP', p_new_camp_id)
2442 || p_errmsg;
2443 ams_cpyutility_pvt.write_log_mesg ( 'CSCH',
2444 p_src_camp_schedule_id,
2445 p_errmsg,
2446 'ERROR'
2447 );
2448 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2449 RAISE FND_API.G_EXC_ERROR ;
2450 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2451 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2452 END IF;
2453 END IF;
2454 EXCEPTION
2455 WHEN fnd_api.g_exc_error
2456 THEN
2457 ROLLBACK TO copy_campaign_schedules;
2458 x_return_status := fnd_api.g_ret_sts_error;
2459 fnd_msg_pub.count_and_get (
2460 p_encoded => fnd_api.g_false,
2461 p_count => x_msg_count,
2462 p_data => x_msg_data
2463 );
2464 WHEN fnd_api.g_exc_unexpected_error
2465 THEN
2466 ROLLBACK TO copy_campaign_schedules;
2467 x_return_status := fnd_api.g_ret_sts_unexp_error;
2468 fnd_msg_pub.count_and_get (
2469 p_encoded => fnd_api.g_false,
2470 p_count => x_msg_count,
2471 p_data => x_msg_data
2472 );
2473 WHEN OTHERS
2474 THEN
2475 ROLLBACK TO copy_campaign_schedules;
2476 x_return_status := fnd_api.g_ret_sts_unexp_error;
2477
2478 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2479 THEN
2480 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2481 END IF;
2482
2483 fnd_msg_pub.count_and_get (
2484 p_encoded => fnd_api.g_false,
2485 p_count => x_msg_count,
2486 p_data => x_msg_data
2487 );
2488 END copy_campaign_schedules;
2489 */
2490
2491 PROCEDURE copy_tasks (
2492 p_api_version IN NUMBER,
2493 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2494 x_return_status OUT NOCOPY VARCHAR2,
2495 x_msg_count OUT NOCOPY NUMBER,
2496 x_msg_data OUT NOCOPY VARCHAR2,
2497 p_old_camp_id IN NUMBER,
2498 p_new_camp_id IN NUMBER,
2499 p_task_id IN NUMBER,
2500 p_owner_id IN NUMBER,
2501 p_actual_due_date IN DATE
2502 ) IS
2503
2504 CURSOR cur_get_tasks IS
2505 SELECT *
2506 FROM jtf_tasks_vl -- changed from _v for perf fixes
2507 WHERE task_id = p_task_id;
2508
2509 CURSOR cur_get_task_assgmts IS
2510 SELECT *
2511 FROM jtf_task_assignments
2512 WHERE task_id = p_task_id;
2513
2514 l_api_version CONSTANT NUMBER := 1.0;
2515 l_api_name CONSTANT VARCHAR2 (30) := 'copy_tasks';
2516 l_full_name CONSTANT VARCHAR2 (60) := g_pkg_name || '.' || l_api_name;
2517 l_return_status VARCHAR2 (1);
2518 l_mesg_text VARCHAR2 (2000);
2519 p_errmsg VARCHAR2 (3000);
2520 l_errcode VARCHAR2 (80);
2521 l_errnum NUMBER;
2522 l_errmsg VARCHAR2 (3000);
2523 l_lookup_meaning VARCHAR2 (2000);
2524 l_task_id NUMBER;
2525 l_task_status NUMBER;
2526 l_task_assignment_id NUMBER;
2527 BEGIN
2528 SAVEPOINT copy_tasks;
2529
2530 IF (AMS_DEBUG_HIGH_ON) THEN
2531
2532
2533
2534 ams_utility_pvt.debug_message (l_full_name || ': start');
2535
2536 END IF;
2537 IF fnd_api.to_boolean (p_init_msg_list)
2538 THEN
2539 fnd_msg_pub.initialize;
2540 END IF;
2541
2542 IF NOT fnd_api.compatible_api_call ( l_api_version,
2543 p_api_version,
2544 l_api_name,
2545 g_pkg_name
2546 )
2547 THEN
2548 RAISE fnd_api.g_exc_unexpected_error;
2549 END IF;
2550
2551 x_return_status := fnd_api.g_ret_sts_success;
2552 ----------------------- insert -----------------------
2553 IF (AMS_DEBUG_HIGH_ON) THEN
2554
2555 ams_utility_pvt.debug_message (l_full_name || ': start');
2556 END IF;
2557
2558 ams_utility_pvt.get_lookup_meaning( 'AMS_SYS_ARC_QUALIFIER',
2559 'TASK',
2560 l_return_status,
2561 l_lookup_meaning
2562 );
2563 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2564 RAISE FND_API.G_EXC_ERROR ;
2565 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2566 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2567 END IF;
2568 -- General Message saying copying has started
2569 fnd_message.set_name ('AMS', 'COPY_ACT_ELEMENTS');
2570 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
2571 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
2572
2573 -- Writing to the Pl/SQLtable
2574 ams_cpyutility_pvt.write_log_mesg ( 'CAMP',
2575 p_old_camp_id,
2576 l_mesg_text,
2577 'GENERAL'
2578 );
2579
2580
2581 l_task_status := to_number(FND_PROFILE.Value
2582 ('JTF_TASK_DEFAULT_TASK_STATUS'));
2583 FOR tasks_rec in cur_get_tasks LOOP
2584 ams_task_pvt.create_task
2585 (p_api_version => 1.0,
2586 p_init_msg_list => fnd_api.g_false,
2587 p_commit => fnd_api.g_false,
2588 p_task_id => NULL,
2589 p_task_name => tasks_rec.task_name,
2590 p_task_type_id => tasks_rec.task_type_id,
2591 p_task_status_id => l_task_status,
2592 p_task_priority_id => tasks_rec.task_priority_id,
2593 p_owner_id => p_owner_id,
2594 p_owner_type_code => tasks_rec.owner_type_code,
2595 p_private_flag => tasks_rec.private_flag,
2596 p_planned_start_date => NULL,
2597 p_planned_end_date => NULL,
2598 p_actual_start_date => NULL,
2599 p_actual_end_date => NULL,
2600 p_source_object_type_code => 'AMS_CAMP',
2601 p_source_object_id => p_new_camp_id,
2602 p_source_object_name => to_char(p_new_camp_id),
2603 x_return_status => l_return_status,
2604 x_msg_count => x_msg_count,
2605 x_msg_data => x_msg_data,
2606 x_task_id => l_task_id
2607 );
2608
2609 IF l_return_status = fnd_api.g_ret_sts_unexp_error OR
2610 l_return_status = fnd_api.g_ret_sts_error THEN
2611 FOR l_counter IN 1 .. x_msg_count
2612 LOOP
2613 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
2614 p_errmsg := substr(l_mesg_text || '): ' || l_counter ||
2615 ' OF ' || x_msg_count, 1, 3000);
2616 ams_cpyutility_pvt.write_log_mesg ( 'CAMP',
2617 p_old_camp_id,
2618 p_errmsg,
2619 'ERROR'
2620 );
2621 END LOOP;
2622 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
2623 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
2624 l_mesg_text := fnd_message.get;
2625 p_errmsg := l_mesg_text ||
2626 ams_utility_pvt.get_object_name ('CAMP', p_new_camp_id)
2627 || p_errmsg;
2628 ams_cpyutility_pvt.write_log_mesg ( 'CAMP',
2629 p_old_camp_id,
2630 p_errmsg,
2631 'ERROR'
2632 );
2633 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2634 RAISE FND_API.G_EXC_ERROR ;
2635 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2636 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2637 END IF;
2638 END IF;
2639
2640
2641 FOR task_assg_rec in cur_get_task_assgmts LOOP
2642 SAVEPOINT ams_task_assgn;
2643 AMS_TASK_PVT.create_Task_Assignment (
2644 p_api_version => l_api_version,
2645 p_init_msg_list => fnd_api.g_false ,
2646 p_commit => fnd_api.g_false ,
2647 p_task_id => l_task_id,
2648 p_resource_type_code => task_assg_rec.resource_type_code,
2649 p_resource_id => p_owner_id,
2650 p_assignment_status_id => l_task_status,
2651 x_return_status => l_return_status,
2652 x_msg_count => x_msg_count,
2653 x_msg_data => x_msg_data,
2654 x_task_assignment_id => l_task_assignment_id ) ;
2655
2656 IF l_return_status = fnd_api.g_ret_sts_error OR
2657 l_return_status = fnd_api.g_ret_sts_unexp_error then
2658 FOR l_counter IN 1 .. x_msg_count
2659 LOOP
2660 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
2661 p_errmsg := SUBSTR ( l_mesg_text || '): ' || l_counter ||
2662 ' OF ' || x_msg_count, 1, 3000);
2663 ams_cpyutility_pvt.write_log_mesg ( 'CAMP',
2664 p_old_camp_id,
2665 p_errmsg,
2666 'ERROR'
2667 );
2668 -- Is failed write a copy failed message in the log table
2669 END LOOP;
2670 ROLLBACK TO ams_task_assgn;
2671 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
2672 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
2673 l_mesg_text := fnd_message.get;
2674 p_errmsg := l_mesg_text || ams_utility_pvt.get_object_name
2675 ('CAMP', p_new_camp_id) || p_errmsg;
2676 ams_cpyutility_pvt.write_log_mesg ( 'CAMP',
2677 p_old_camp_id,
2678 p_errmsg,
2679 'ERROR');
2680 if l_return_status = fnd_api.g_ret_sts_unexp_error then
2681 RAISE fnd_api.g_exc_unexpected_error;
2682 else
2683 RAISE fnd_api.g_exc_error;
2684 END IF;
2685 END IF;
2686
2687 END LOOP;
2688
2689 END LOOP;
2690
2691 EXCEPTION
2692 WHEN fnd_api.g_exc_error
2693 THEN
2694 ROLLBACK TO copy_tasks;
2695 x_return_status := fnd_api.g_ret_sts_error;
2696 fnd_msg_pub.count_and_get (
2697 p_encoded => fnd_api.g_false,
2698 p_count => x_msg_count,
2699 p_data => x_msg_data
2700 );
2701 WHEN fnd_api.g_exc_unexpected_error
2702 THEN
2703 ROLLBACK TO copy_tasks;
2704 x_return_status := fnd_api.g_ret_sts_unexp_error;
2705 fnd_msg_pub.count_and_get (
2706 p_encoded => fnd_api.g_false,
2707 p_count => x_msg_count,
2708 p_data => x_msg_data
2709 );
2710 WHEN OTHERS
2711 THEN
2712 ROLLBACK TO copy_tasks;
2713 x_return_status := fnd_api.g_ret_sts_unexp_error;
2714
2715 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2716 THEN
2717 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2718 END IF;
2719
2720 fnd_msg_pub.count_and_get (
2721 p_encoded => fnd_api.g_false,
2722 p_count => x_msg_count,
2723 p_data => x_msg_data
2724 );
2725 END copy_tasks;
2726
2727
2728 PROCEDURE copy_partners (
2729 p_api_version IN NUMBER,
2730 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2731 x_return_status OUT NOCOPY VARCHAR2,
2732 x_msg_count OUT NOCOPY NUMBER,
2733 x_msg_data OUT NOCOPY VARCHAR2,
2734 p_old_camp_id IN NUMBER,
2735 p_new_camp_id IN NUMBER
2736 )IS
2737
2738 l_act_partner AMS_ACTPARTNER_PVT.act_partner_rec_type;
2739 temp_act_partner AMS_ACTPARTNER_PVT.act_partner_rec_type;
2740
2741 CURSOR cur_get_partner IS
2742 SELECT *
2743 FROM AMS_ACT_PARTNERS
2744 WHERE act_partner_used_by_id = p_old_camp_id
2745 AND arc_act_partner_used_by = 'CAMP';
2746
2747 l_api_version CONSTANT NUMBER := 1.0;
2748 l_api_name CONSTANT VARCHAR2 (30) := 'copy_partners';
2749 l_full_name CONSTANT VARCHAR2 (60) := 'g_pkg_name' || '.'|| l_api_name;
2750 l_return_status VARCHAR2 (1); -- variables for the OUT parameters of the called create procedures
2751 l_mesg_text VARCHAR2 (2000);
2752 p_errmsg VARCHAR2 (3000);
2753 l_errcode VARCHAR2 (80);
2754 l_errnum NUMBER;
2755 l_errmsg VARCHAR2 (3000);
2756 l_lookup_meaning VARCHAR2 (2000);
2757 l_act_partner_id NUMBER;
2758 BEGIN
2759
2760 IF (AMS_DEBUG_HIGH_ON) THEN
2761
2762
2763
2764 ams_utility_pvt.debug_message (l_full_name || ': start');
2765
2766 END IF;
2767
2768 IF fnd_api.to_boolean (p_init_msg_list)
2769 THEN
2770 fnd_msg_pub.initialize;
2771 END IF;
2772
2773 IF NOT fnd_api.compatible_api_call (
2774 l_api_version,
2775 p_api_version,
2776 l_api_name,
2777 g_pkg_name
2778 )
2779 THEN
2780 RAISE fnd_api.g_exc_unexpected_error;
2781 END IF;
2782
2783 x_return_status := fnd_api.g_ret_sts_success;
2784 ----------------------- insert -----------------------
2785 IF (AMS_DEBUG_HIGH_ON) THEN
2786
2787 ams_utility_pvt.debug_message (l_full_name || ': start');
2788 END IF;
2789
2790 ams_utility_pvt.get_lookup_meaning ( 'AMS_SYS_ARC_QUALIFIER',
2791 'PTNR',
2792 l_return_status,
2793 l_lookup_meaning
2794 );
2795 -- General Message saying copying has started
2796 fnd_message.set_name ('AMS', 'COPY_ACT_ELEMENTS');
2797 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
2798 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
2799
2800 -- Writing to the Pl/SQLtable
2801 ams_cpyutility_pvt.write_log_mesg ( 'CAMP',
2802 p_old_camp_id,
2803 l_mesg_text,
2804 'GENERAL'
2805 );
2806 FOR partner_rec in cur_get_partner LOOP
2807 BEGIN
2808
2809 SAVEPOINT copy_partners ;
2810 l_act_partner := temp_act_partner;
2811 l_act_partner.object_version_number := 1;
2812 l_act_partner.act_partner_used_by_id := p_new_camp_id;
2813 l_act_partner.arc_act_partner_used_by := 'CAMP';
2814 l_act_partner.partner_id := partner_rec.partner_id ;
2815 l_act_partner.partner_type := partner_rec.partner_type ;
2816 l_act_partner.description := partner_rec.description ;
2817 l_act_partner.attribute_category :=
2818 partner_rec.attribute_category;
2819 l_act_partner.attribute1 := partner_rec.attribute1 ;
2820 l_act_partner.attribute2 := partner_rec.attribute2;
2821 l_act_partner.attribute3 := partner_rec.attribute3;
2822 l_act_partner.attribute4 := partner_rec.attribute4;
2823 l_act_partner.attribute5 := partner_rec.attribute5;
2824 l_act_partner.attribute6 := partner_rec.attribute6;
2825 l_act_partner.attribute7 := partner_rec.attribute7;
2826 l_act_partner.attribute8 := partner_rec.attribute8;
2827 l_act_partner.attribute9 := partner_rec.attribute9;
2828 l_act_partner.attribute10 := partner_rec.attribute10;
2829 l_act_partner.attribute13 := partner_rec.attribute13;
2830 l_act_partner.attribute14 := partner_rec.attribute14;
2831 l_act_partner.attribute15 := partner_rec.attribute15;
2832
2833 -- Bug fix:2072789
2834 -- added by rrajesh on 10/24/01
2835 l_act_partner.partner_address_id := partner_rec.partner_address_id;
2836 l_act_partner.primary_contact_id := partner_rec.primary_contact_id;
2837 l_act_partner.preferred_vad_id := partner_rec.preferred_vad_id;
2838 l_act_partner.primary_flag := partner_rec.primary_flag;
2839 -- End fix:2072789
2840
2841 AMS_actpartner_pvt.create_act_partner
2842 ( p_api_version => l_api_version,
2843 p_init_msg_list => fnd_api.g_false,
2844 p_commit => fnd_api.g_false,
2845 x_return_status => l_return_status,
2846 x_msg_count => x_msg_count,
2847 x_msg_data => x_msg_data,
2848 p_act_partner_rec => l_act_partner,
2849 x_act_partner_id => l_act_partner_id
2850 );
2851
2852
2853 IF l_return_status = fnd_api.g_ret_sts_error
2854 THEN
2855 IF x_msg_count >= 1
2856 THEN
2857 FOR l_counter IN 1 .. x_msg_count
2858 LOOP
2859 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
2860 p_errmsg := SUBSTR (
2861 l_mesg_text ||
2862 '): ' ||
2863 l_counter ||
2864 ' OF ' ||
2865 x_msg_count,
2866 1,
2867 3000
2868 );
2869 ams_cpyutility_pvt.write_log_mesg (
2870 'CAMP',
2871 p_old_camp_id,
2872 p_errmsg,
2873 'ERROR'
2874 );
2875 -- Is failed write a copy failed message in the log table
2876 END LOOP;
2877 ELSIF x_msg_count = 1
2878 THEN
2879 l_mesg_text := x_msg_data;
2880 p_errmsg := SUBSTR (
2881 l_mesg_text ||
2882 ' , ' ||
2883 '): ' ||
2884 x_msg_count ||
2885 ' OF ' ||
2886 x_msg_count,
2887 1,
2888 4000
2889 );
2890 ams_cpyutility_pvt.write_log_mesg (
2891 'CAMP',
2892 p_old_camp_id,
2893 p_errmsg,
2894 'ERROR'
2895 );
2896 END IF;
2897 ---- if error then right a copy log message to the log table
2898
2899 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
2900 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
2901 l_mesg_text := fnd_message.get;
2902 p_errmsg := l_mesg_text ||
2903 ams_utility_pvt.get_object_name ('CAMP',
2904 p_new_camp_id) ||
2905 p_errmsg;
2906 ams_cpyutility_pvt.write_log_mesg ( 'CAMP',
2907 p_old_camp_id,
2908 p_errmsg,
2909 'ERROR'
2910 );
2911 RAISE fnd_api.g_exc_error;
2912 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
2913 THEN
2914 IF l_return_status = fnd_api.g_ret_sts_error
2915 OR l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2916 FOR l_counter IN 1 .. x_msg_count
2917 LOOP
2918 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
2919 p_errmsg := SUBSTR ( l_mesg_text || ' , ' || '): ' ||
2920 x_msg_count || ' OF ' || x_msg_count,
2921 1, 4000);
2922 ams_cpyutility_pvt.write_log_mesg (
2923 'CAMP',
2924 p_old_camp_id,
2925 p_errmsg,
2926 'ERROR'
2927 );
2928 END LOOP;
2929 END IF;
2930 ---- if error then right a copy log message to the log table
2931
2932 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
2933 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
2934 l_mesg_text := fnd_message.get;
2935 p_errmsg := l_mesg_text || ams_utility_pvt.get_object_name
2936 ('CAMP', p_new_camp_id) || p_errmsg;
2937 ams_cpyutility_pvt.write_log_mesg ( 'CAMP',
2938 p_old_camp_id,
2939 p_errmsg,
2940 'ERROR'
2941 );
2942 RAISE fnd_api.g_exc_unexpected_error;
2943 END IF;
2944 EXCEPTION
2945 WHEN fnd_api.g_exc_error
2946 THEN
2947 ROLLBACK TO copy_partners;
2948 x_return_status := fnd_api.g_ret_sts_error;
2949 fnd_msg_pub.count_and_get ( p_encoded => fnd_api.g_false,
2950 p_count => x_msg_count,
2951 p_data => x_msg_data
2952 );
2953 WHEN fnd_api.g_exc_unexpected_error
2954 THEN
2955 ROLLBACK TO copy_partners;
2956 x_return_status := fnd_api.g_ret_sts_unexp_error;
2957 fnd_msg_pub.count_and_get ( p_encoded => fnd_api.g_false,
2958 p_count => x_msg_count,
2959 p_data => x_msg_data
2960 );
2961 WHEN OTHERS
2962 THEN
2963 ROLLBACK TO copy_partners;
2964 x_return_status := fnd_api.g_ret_sts_unexp_error;
2965
2966 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2967 THEN
2968 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2969 END IF;
2970
2971 fnd_msg_pub.count_and_get (
2972 p_encoded => fnd_api.g_false,
2973 p_count => x_msg_count,
2974 p_data => x_msg_data
2975 );
2976 END;
2977 END LOOP;
2978 EXCEPTION
2979 WHEN others THEN
2980 ROLLBACK TO copy_partners;
2981 x_return_status := fnd_api.g_ret_sts_unexp_error;
2982 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2983 THEN
2984 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2985 END IF;
2986 fnd_msg_pub.count_and_get (
2987 p_encoded => fnd_api.g_false,
2988 p_count => x_msg_count,
2989 p_data => x_msg_data
2990 );
2991 END copy_partners;
2992
2993
2994 --
2995 -- History
2996 -- 05-Apr-2001 choang Created.
2997 -- 06-Apr-2001 choang Added check of return_status in call to create_listaction api.
2998 -- 09-Apr-2001 choang Added order by order_number to main cursor
2999 PROCEDURE copy_list_select_actions (
3000 p_api_version IN NUMBER,
3001 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3002 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3003 x_return_status OUT NOCOPY VARCHAR2,
3004 x_msg_count OUT NOCOPY NUMBER,
3005 x_msg_data OUT NOCOPY VARCHAR2,
3006 p_object_type IN VARCHAR2,
3007 p_src_object_id IN NUMBER,
3008 p_tar_object_id IN NUMBER
3009 )
3010 IS
3011 L_API_NAME CONSTANT VARCHAR2(30) := 'copy_list_select_actions';
3012 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
3013
3014 l_select_action_rec AMS_ListAction_PVT.action_rec_type;
3015 l_select_action_id NUMBER;
3016
3017 --
3018 -- order by order_number is needed because the first select
3019 -- action needs to be INCLUDE
3020 CURSOR c_source_rec (p_object_type IN VARCHAR2, p_object_id IN NUMBER) IS
3021 SELECT *
3022 FROM ams_list_select_actions
3023 WHERE arc_action_used_by = p_object_type
3024 AND action_used_by_id = p_object_id
3025 ORDER BY order_number
3026 ;
3027 BEGIN
3028 -- Standard Start of API savepoint
3029 SAVEPOINT copy_list_select_actions_pvt;
3030
3031 -- Standard call to check for call compatibility.
3032 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3033 p_api_version,
3034 l_api_name,
3035 G_PKG_NAME)
3036 THEN
3037 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3038 END IF;
3039
3040 -- Initialize message list if p_init_msg_list is set to TRUE.
3041 IF FND_API.to_Boolean( p_init_msg_list )THEN
3042 FND_MSG_PUB.initialize;
3043 END IF;
3044
3045 IF (AMS_DEBUG_HIGH_ON) THEN
3046
3047
3048
3049 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' start');
3050
3051 END IF;
3052
3053 -- Initialize API return status to SUCCESS
3054 x_return_status := FND_API.G_RET_STS_SUCCESS;
3055
3056 --
3057 -- Start of API body.
3058 --
3059 -- object type and id will be the same for
3060 -- all the select actions in the copy operation
3061 l_select_action_rec.arc_action_used_by := p_object_type;
3062 l_select_action_rec.action_used_by_id := p_tar_object_id;
3063
3064 FOR l_source_rec IN c_source_rec (p_object_type, p_src_object_id) LOOP
3065 l_select_action_rec.order_number := l_source_rec.order_number;
3066 l_select_action_rec.list_action_type := l_source_rec.list_action_type;
3067 l_select_action_rec.arc_incl_object_from := l_source_rec.arc_incl_object_from;
3068 l_select_action_rec.incl_object_id := l_source_rec.incl_object_id;
3069 l_select_action_rec.rank := l_source_rec.rank;
3070
3071 AMS_ListAction_PVT.Create_ListAction (
3072 p_api_version => 1.0,
3073 p_init_msg_list => FND_API.G_FALSE,
3074 p_commit => FND_API.G_FALSE,
3075 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3076 x_return_status => x_return_status,
3077 x_msg_count => x_msg_count,
3078 x_msg_data => x_msg_data,
3079 p_action_rec => l_select_action_rec,
3080 x_action_id => l_select_action_id
3081 );
3082 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3083 RAISE FND_API.G_EXC_ERROR;
3084 END IF;
3085 END LOOP;
3086
3087 --
3088 -- End of API body.
3089 --
3090
3091 -- Standard check for p_commit
3092 IF FND_API.to_Boolean( p_commit ) THEN
3093 COMMIT WORK;
3094 END IF;
3095
3096
3097 -- Debug Message
3098 IF (AMS_DEBUG_HIGH_ON) THEN
3099
3100 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' end');
3101 END IF;
3102
3103 -- Standard call to get message count and if count is 1, get message info.
3104 FND_MSG_PUB.Count_And_Get (
3105 p_count => x_msg_count,
3106 p_data => x_msg_data
3107 );
3108 EXCEPTION
3109 WHEN FND_API.G_EXC_ERROR THEN
3110 ROLLBACK TO copy_list_select_actions_pvt;
3111 x_return_status := FND_API.G_RET_STS_ERROR;
3112 -- Standard call to get message count and if count=1, get the message
3113 FND_MSG_PUB.Count_And_Get (
3114 p_encoded => FND_API.G_FALSE,
3115 p_count => x_msg_count,
3116 p_data => x_msg_data
3117 );
3118 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3119 ROLLBACK TO copy_list_select_actions_pvt;
3120 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3121 -- Standard call to get message count and if count=1, get the message
3122 FND_MSG_PUB.Count_And_Get (
3123 p_encoded => FND_API.G_FALSE,
3124 p_count => x_msg_count,
3125 p_data => x_msg_data
3126 );
3127 WHEN OTHERS THEN
3128 ROLLBACK TO copy_list_select_actions_pvt;
3129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3130 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3131 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3132 END IF;
3133 -- Standard call to get message count and if count=1, get the message
3134 FND_MSG_PUB.Count_And_Get (
3135 p_encoded => FND_API.G_FALSE,
3136 p_count => x_msg_count,
3137 p_data => x_msg_data
3138 );
3139 END copy_list_select_actions;
3140
3141
3142 PROCEDURE copy_partners_generic (
3143 p_api_version IN NUMBER,
3144 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3145 x_return_status OUT NOCOPY VARCHAR2,
3146 x_msg_count OUT NOCOPY NUMBER,
3147 x_msg_data OUT NOCOPY VARCHAR2,
3148 p_old_id IN NUMBER,
3149 p_new_id IN NUMBER,
3150 p_type IN VARCHAR2
3151 )
3152 IS
3153
3154 l_act_partner AMS_ACTPARTNER_PVT.act_partner_rec_type;
3155 temp_act_partner AMS_ACTPARTNER_PVT.act_partner_rec_type;
3156
3157 CURSOR cur_get_partner IS
3158 SELECT *
3159 FROM AMS_ACT_PARTNERS
3160 WHERE act_partner_used_by_id = p_old_id
3161 AND arc_act_partner_used_by = p_type;
3162
3163 l_api_version CONSTANT NUMBER := 1.0;
3164 l_api_name CONSTANT VARCHAR2 (30) := 'copy_partners';
3165 l_full_name CONSTANT VARCHAR2 (60) := 'g_pkg_name' || '.'|| l_api_name;
3166 l_return_status VARCHAR2 (1); -- variables for the OUT parameters of the called create procedures
3167 l_mesg_text VARCHAR2 (2000);
3168 p_errmsg VARCHAR2 (3000);
3169 l_errcode VARCHAR2 (80);
3170 l_errnum NUMBER;
3171 l_errmsg VARCHAR2 (3000);
3172 l_lookup_meaning VARCHAR2 (2000);
3173 l_act_partner_id NUMBER;
3174 BEGIN
3175
3176 IF (AMS_DEBUG_HIGH_ON) THEN
3177
3178
3179
3180 ams_utility_pvt.debug_message (l_full_name || ': start');
3181
3182 END IF;
3183
3184 IF fnd_api.to_boolean (p_init_msg_list)
3185 THEN
3186 fnd_msg_pub.initialize;
3187 END IF;
3188
3189 IF NOT fnd_api.compatible_api_call (
3190 l_api_version,
3191 p_api_version,
3192 l_api_name,
3193 g_pkg_name
3194 )
3195 THEN
3196 RAISE fnd_api.g_exc_unexpected_error;
3197 END IF;
3198
3199 x_return_status := fnd_api.g_ret_sts_success;
3200 ----------------------- insert -----------------------
3201 IF (AMS_DEBUG_HIGH_ON) THEN
3202
3203 ams_utility_pvt.debug_message (l_full_name || ': start');
3204 END IF;
3205
3206 ams_utility_pvt.get_lookup_meaning ( 'AMS_SYS_ARC_QUALIFIER',
3207 'PTNR',
3208 l_return_status,
3209 l_lookup_meaning
3210 );
3211 -- General Message saying copying has started
3212 fnd_message.set_name ('AMS', 'COPY_ACT_ELEMENTS');
3213 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
3214 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
3215
3216 -- Writing to the Pl/SQLtable
3217 ams_cpyutility_pvt.write_log_mesg ( p_type,
3218 p_old_id,
3219 l_mesg_text,
3220 'GENERAL'
3221 );
3222 FOR partner_rec in cur_get_partner LOOP
3223 BEGIN
3224
3225 SAVEPOINT copy_partners ;
3226 l_act_partner := temp_act_partner;
3227 l_act_partner.object_version_number := 1;
3228 l_act_partner.act_partner_used_by_id := p_new_id;
3229 l_act_partner.arc_act_partner_used_by := p_type;
3230 l_act_partner.partner_id := partner_rec.partner_id ;
3231 l_act_partner.partner_type := partner_rec.partner_type ;
3232 l_act_partner.description := partner_rec.description ;
3233 l_act_partner.attribute_category :=
3234 partner_rec.attribute_category;
3235 l_act_partner.attribute1 := partner_rec.attribute1 ;
3236 l_act_partner.attribute2 := partner_rec.attribute2;
3237 l_act_partner.attribute3 := partner_rec.attribute3;
3238 l_act_partner.attribute4 := partner_rec.attribute4;
3239 l_act_partner.attribute5 := partner_rec.attribute5;
3240 l_act_partner.attribute6 := partner_rec.attribute6;
3241 l_act_partner.attribute7 := partner_rec.attribute7;
3242 l_act_partner.attribute8 := partner_rec.attribute8;
3243 l_act_partner.attribute9 := partner_rec.attribute9;
3244 l_act_partner.attribute10 := partner_rec.attribute10;
3245 l_act_partner.attribute13 := partner_rec.attribute13;
3246 l_act_partner.attribute14 := partner_rec.attribute14;
3247 l_act_partner.attribute15 := partner_rec.attribute15;
3248
3249 AMS_actpartner_pvt.create_act_partner
3250 ( p_api_version => l_api_version,
3251 p_init_msg_list => fnd_api.g_false,
3252 p_commit => fnd_api.g_false,
3253 x_return_status => l_return_status,
3254 x_msg_count => x_msg_count,
3255 x_msg_data => x_msg_data,
3256 p_act_partner_rec => l_act_partner,
3257 x_act_partner_id => l_act_partner_id
3258 );
3259
3260
3261 IF l_return_status = fnd_api.g_ret_sts_error
3262 THEN
3263 IF x_msg_count >= 1
3264 THEN
3265 FOR l_counter IN 1 .. x_msg_count
3266 LOOP
3267 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
3268 p_errmsg := SUBSTR (
3269 l_mesg_text ||
3270 '): ' ||
3271 l_counter ||
3272 ' OF ' ||
3273 x_msg_count,
3274 1,
3275 3000
3276 );
3277 ams_cpyutility_pvt.write_log_mesg (
3278 p_type,
3279 p_old_id,
3280 p_errmsg,
3281 'ERROR'
3282 );
3283 -- Is failed write a copy failed message in the log table
3284 END LOOP;
3285 ELSIF x_msg_count = 1
3286 THEN
3287 l_mesg_text := x_msg_data;
3288 p_errmsg := SUBSTR (
3289 l_mesg_text ||
3290 ' , ' ||
3291 '): ' ||
3292 x_msg_count ||
3293 ' OF ' ||
3294 x_msg_count,
3295 1,
3296 4000
3297 );
3298 ams_cpyutility_pvt.write_log_mesg (
3299 p_type,
3300 p_old_id,
3301 p_errmsg,
3302 'ERROR'
3303 );
3304 END IF;
3305 ---- if error then right a copy log message to the log table
3306
3307 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
3308 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
3309 l_mesg_text := fnd_message.get;
3310 p_errmsg := l_mesg_text ||
3311 ams_utility_pvt.get_object_name (p_type,
3312 p_new_id) ||
3313 p_errmsg;
3314 ams_cpyutility_pvt.write_log_mesg ( p_type,
3315 p_old_id,
3316 p_errmsg,
3317 'ERROR'
3318 );
3319 RAISE fnd_api.g_exc_error;
3320 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
3321 THEN
3322 IF l_return_status = fnd_api.g_ret_sts_error
3323 OR l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3324 FOR l_counter IN 1 .. x_msg_count
3325 LOOP
3326 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
3327 p_errmsg := SUBSTR ( l_mesg_text || ' , ' || '): ' ||
3328 x_msg_count || ' OF ' || x_msg_count,
3329 1, 4000);
3330 ams_cpyutility_pvt.write_log_mesg (
3331 p_type,
3332 p_old_id,
3333 p_errmsg,
3334 'ERROR'
3335 );
3336 END LOOP;
3337 END IF;
3338 ---- if error then right a copy log message to the log table
3339
3340 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
3341 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
3342 l_mesg_text := fnd_message.get;
3343 p_errmsg := l_mesg_text || ams_utility_pvt.get_object_name
3344 (p_type, p_new_id) || p_errmsg;
3345 ams_cpyutility_pvt.write_log_mesg ( p_type,
3346 p_old_id,
3347 p_errmsg,
3348 'ERROR'
3349 );
3350 RAISE fnd_api.g_exc_unexpected_error;
3351 END IF;
3352 EXCEPTION
3353 WHEN fnd_api.g_exc_error
3354 THEN
3355 ROLLBACK TO copy_partners;
3356 x_return_status := fnd_api.g_ret_sts_error;
3357 fnd_msg_pub.count_and_get ( p_encoded => fnd_api.g_false,
3358 p_count => x_msg_count,
3359 p_data => x_msg_data
3360 );
3361 WHEN fnd_api.g_exc_unexpected_error
3362 THEN
3363 ROLLBACK TO copy_partners;
3364 x_return_status := fnd_api.g_ret_sts_unexp_error;
3365 fnd_msg_pub.count_and_get ( p_encoded => fnd_api.g_false,
3366 p_count => x_msg_count,
3367 p_data => x_msg_data
3368 );
3369 WHEN OTHERS
3370 THEN
3371 ROLLBACK TO copy_partners;
3372 x_return_status := fnd_api.g_ret_sts_unexp_error;
3373
3374 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3375 THEN
3376 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3377 END IF;
3378
3379 fnd_msg_pub.count_and_get (
3380 p_encoded => fnd_api.g_false,
3381 p_count => x_msg_count,
3382 p_data => x_msg_data
3383 );
3384 END;
3385 END LOOP;
3386 EXCEPTION
3387 WHEN others THEN
3388 ROLLBACK TO copy_partners;
3389 x_return_status := fnd_api.g_ret_sts_unexp_error;
3390 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3391 THEN
3392 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3393 END IF;
3394 fnd_msg_pub.count_and_get (
3395 p_encoded => fnd_api.g_false,
3396 p_count => x_msg_count,
3397 p_data => x_msg_data
3398 );
3399 END copy_partners_generic;
3400
3401
3402 --======================================================================
3403 -- FUNCTION
3404 -- copy_act_schedules
3405 --
3406 -- PURPOSE
3407 -- Created to copy schedules for the campaign.
3408 --
3409 -- HISTORY
3410 -- 18-Aug-2001 ptendulk Create.
3411 -- 08-oct-2001 soagrawa Removed the security group id
3412 --======================================================================
3413 PROCEDURE copy_act_schedules(
3414 p_old_camp_id IN NUMBER,
3415 p_new_camp_id IN NUMBER,
3416 p_new_start_date IN DATE,
3417 x_return_status OUT NOCOPY VARCHAR2,
3418 x_msg_count OUT NOCOPY NUMBER,
3419 x_msg_data OUT NOCOPY VARCHAR2)
3420 IS
3421 L_API_NAME CONSTANT VARCHAR2(30) := 'copy_act_schedules';
3422
3423 CURSOR c_schedule_det IS
3424 SELECT * FROM ams_campaign_Schedules_vl
3425 WHERE campaign_id = p_old_camp_id ;
3426 l_reference_rec c_schedule_det%ROWTYPE;
3427
3428 l_schedule_rec AMS_Camp_Schedule_PVT.schedule_rec_type ;
3429
3430
3431 BEGIN
3432 -- Standard Start of API savepoint
3433 SAVEPOINT Copy_Act_Schedule;
3434
3435 -- Debug Message
3436 IF (AMS_DEBUG_HIGH_ON) THEN
3437
3438 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
3439 END IF;
3440 -- Initialize API return status to SUCCESS
3441 x_return_status := FND_API.G_RET_STS_SUCCESS;
3442
3443 OPEN c_schedule_det ;
3444 LOOP
3445 FETCH c_schedule_det INTO l_reference_rec ;
3446 EXIT WHEN c_schedule_det%NOTFOUND ;
3447 l_schedule_rec.activity_type_code := l_reference_rec.activity_type_code ;
3448 l_schedule_rec.activity_id := l_reference_rec.activity_id;
3449 l_schedule_rec.arc_marketing_medium_from := l_reference_rec.arc_marketing_medium_from;
3450 l_schedule_rec.marketing_medium_id := l_reference_rec.marketing_medium_id;
3451 l_schedule_rec.custom_setup_id := l_reference_rec.custom_setup_id;
3452 l_schedule_rec.triggerable_flag := l_reference_rec.triggerable_flag;
3453 l_schedule_rec.trigger_id := l_reference_rec.trigger_id;
3454 l_schedule_rec.notify_user_id := l_reference_rec.notify_user_id;
3455 l_schedule_rec.approver_user_id := l_reference_rec.approver_user_id;
3456 l_schedule_rec.owner_user_id := l_reference_rec.owner_user_id;
3457 l_schedule_rec.active_flag := l_reference_rec.active_flag;
3458 l_schedule_rec.cover_letter_id := l_reference_rec.cover_letter_id;
3459 l_schedule_rec.reply_to_mail := l_reference_rec.reply_to_mail;
3460 l_schedule_rec.mail_sender_name := l_reference_rec.mail_sender_name;
3461 l_schedule_rec.mail_subject := l_reference_rec.mail_subject;
3462 l_schedule_rec.from_fax_no := l_reference_rec.from_fax_no;
3463 l_schedule_rec.accounts_closed_flag := l_reference_rec.accounts_closed_flag;
3464 l_schedule_rec.org_id := l_reference_rec.org_id;
3465 l_schedule_rec.objective_code := l_reference_rec.objective_code;
3466 l_schedule_rec.country_id := l_reference_rec.country_id;
3467 l_schedule_rec.campaign_calendar := l_reference_rec.campaign_calendar;
3468 l_schedule_rec.start_period_name := l_reference_rec.start_period_name;
3469 l_schedule_rec.end_period_name := l_reference_rec.end_period_name;
3470 l_schedule_rec.priority := l_reference_rec.priority;
3471 l_schedule_rec.workflow_item_key := l_reference_rec.workflow_item_key;
3472 l_schedule_rec.transaction_currency_code := l_reference_rec.transaction_currency_code;
3473 l_schedule_rec.functional_currency_code := l_reference_rec.functional_currency_code;
3474 l_schedule_rec.budget_amount_tc := l_reference_rec.budget_amount_tc;
3475 l_schedule_rec.budget_amount_fc := l_reference_rec.budget_amount_fc;
3476 l_schedule_rec.language_code := l_reference_rec.language_code;
3477 l_schedule_rec.task_id := l_reference_rec.task_id;
3478 l_schedule_rec.related_event_from := l_reference_rec.related_event_from;
3479 l_schedule_rec.related_event_id := l_reference_rec.related_event_id;
3480 l_schedule_rec.attribute_category := l_reference_rec.attribute_category;
3481 l_schedule_rec.attribute1 := l_reference_rec.attribute1;
3482 l_schedule_rec.attribute2 := l_reference_rec.attribute2;
3483 l_schedule_rec.attribute3 := l_reference_rec.attribute3;
3484 l_schedule_rec.attribute4 := l_reference_rec.attribute4;
3485 l_schedule_rec.attribute5 := l_reference_rec.attribute5;
3486 l_schedule_rec.attribute6 := l_reference_rec.attribute6;
3487 l_schedule_rec.attribute7 := l_reference_rec.attribute7;
3488 l_schedule_rec.attribute8 := l_reference_rec.attribute8;
3489 l_schedule_rec.attribute9 := l_reference_rec.attribute9;
3490 l_schedule_rec.attribute10 := l_reference_rec.attribute10;
3491 l_schedule_rec.attribute11 := l_reference_rec.attribute11;
3492 l_schedule_rec.attribute12 := l_reference_rec.attribute12;
3493 l_schedule_rec.attribute13 := l_reference_rec.attribute13;
3494 l_schedule_rec.attribute14 := l_reference_rec.attribute14;
3495 l_schedule_rec.attribute15 := l_reference_rec.attribute15;
3496 l_schedule_rec.activity_attribute_category := l_reference_rec.activity_attribute_category;
3497 l_schedule_rec.activity_attribute1 := l_reference_rec.activity_attribute1;
3498 l_schedule_rec.activity_attribute2 := l_reference_rec.activity_attribute2;
3499 l_schedule_rec.activity_attribute3 := l_reference_rec.activity_attribute3;
3500 l_schedule_rec.activity_attribute4 := l_reference_rec.activity_attribute4;
3501 l_schedule_rec.activity_attribute5 := l_reference_rec.activity_attribute5;
3502 l_schedule_rec.activity_attribute6 := l_reference_rec.activity_attribute6;
3503 l_schedule_rec.activity_attribute7 := l_reference_rec.activity_attribute7;
3504 l_schedule_rec.activity_attribute8 := l_reference_rec.activity_attribute8;
3505 l_schedule_rec.activity_attribute9 := l_reference_rec.activity_attribute9;
3506 l_schedule_rec.activity_attribute10 := l_reference_rec.activity_attribute10;
3507 l_schedule_rec.activity_attribute11 := l_reference_rec.activity_attribute11;
3508 l_schedule_rec.activity_attribute12 := l_reference_rec.activity_attribute12;
3509 l_schedule_rec.activity_attribute13 := l_reference_rec.activity_attribute13;
3510 l_schedule_rec.activity_attribute14 := l_reference_rec.activity_attribute14;
3511 l_schedule_rec.activity_attribute15 := l_reference_rec.activity_attribute15;
3512 -- removed by soagrawa on 08-oct-2001
3513 -- l_schedule_rec.security_group_id := l_reference_rec.security_group_id;
3514 l_schedule_rec.schedule_name := l_reference_rec.schedule_name;
3515 l_schedule_rec.description := l_reference_rec.description;
3516 l_schedule_rec.related_source_object := l_reference_rec.related_event_from;
3517 l_schedule_rec.related_source_id := l_reference_rec.related_event_id;
3518 l_schedule_rec.query_id := l_reference_rec.query_id;
3519 l_schedule_rec.include_content_flag := l_reference_rec.include_content_flag;
3520 l_schedule_rec.content_type := l_reference_rec.content_type;
3521 l_schedule_rec.test_email_address := l_reference_rec.test_email_address ;
3522 l_schedule_rec.greeting_text := l_reference_rec.greeting_text;
3523 l_schedule_rec.footer_text := l_reference_rec.footer_text;
3524
3525 IF (AMS_DEBUG_HIGH_ON) THEN
3526
3527
3528
3529 AMS_Utility_PVT.Debug_Message('Copy Timezone and other details');
3530
3531 END IF;
3532 l_schedule_rec.timezone_id := l_reference_rec.timezone_id;
3533 l_schedule_rec.user_status_id := AMS_Utility_PVT.get_default_user_status('AMS_CAMPAIGN_SCHEDULE_STATUS','NEW') ;
3534 l_schedule_rec.status_code := 'NEW';
3535 l_schedule_rec.campaign_id := p_new_camp_id ;
3536 l_schedule_rec.source_code := null;
3537 l_schedule_rec.use_parent_code_flag := l_reference_rec.use_parent_code_flag;
3538 l_schedule_rec.start_date_time := p_new_start_date ;
3539
3540 l_schedule_rec.end_date_time := p_new_start_date + (l_reference_rec.end_date_time - l_reference_rec.start_date_time) ;
3541 l_schedule_rec.schedule_id := null ;
3542
3543 IF (AMS_DEBUG_HIGH_ON) THEN
3544
3545
3546
3547 AMS_Utility_PVT.Debug_Message('Create Schedule');
3548
3549 END IF;
3550 AMS_Camp_Schedule_PVT.Create_Camp_Schedule(
3551 p_api_version_number => 1,
3552
3553 x_return_status => x_return_status,
3554 x_msg_count => x_msg_count,
3555 x_msg_data => x_msg_data,
3556
3557 p_schedule_rec => l_schedule_rec,
3558 x_schedule_id => l_schedule_rec.schedule_id
3559 );
3560
3561 IF x_return_status = FND_API.g_ret_sts_error THEN
3562 RAISE FND_API.g_exc_error;
3563 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3564 RAISE FND_API.g_exc_unexpected_error;
3565 END IF;
3566
3567 END LOOP;
3568 CLOSE c_schedule_det ;
3569
3570 -- Debug Message
3571 IF (AMS_DEBUG_HIGH_ON) THEN
3572
3573 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
3574 END IF;
3575
3576 -- Standard call to get message count and if count is 1, get message info.
3577 FND_MSG_PUB.Count_And_Get
3578 (p_count => x_msg_count,
3579 p_data => x_msg_data
3580 );
3581 EXCEPTION
3582
3583 WHEN AMS_Utility_PVT.resource_locked THEN
3584 IF c_schedule_det%ISOPEN THEN
3585 CLOSE c_schedule_det ;
3586 END IF ;
3587 x_return_status := FND_API.g_ret_sts_error;
3588 AMS_Utility_Pvt.Error_Message('AMS_API_RESOURCE_LOCKED');
3589
3590 WHEN FND_API.G_EXC_ERROR THEN
3591 ROLLBACK TO Copy_Act_Schedule;
3592 IF c_schedule_det%ISOPEN THEN
3593 CLOSE c_schedule_det ;
3594 END IF ;
3595 x_return_status := FND_API.G_RET_STS_ERROR;
3596 -- Standard call to get message count and if count=1, get the message
3597 FND_MSG_PUB.Count_And_Get (
3598 p_encoded => FND_API.G_FALSE,
3599 p_count => x_msg_count,
3600 p_data => x_msg_data
3601 );
3602
3603 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3604 ROLLBACK TO Copy_Act_Schedule;
3605 IF c_schedule_det%ISOPEN THEN
3606 CLOSE c_schedule_det ;
3607 END IF ;
3608 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3609 -- Standard call to get message count and if count=1, get the message
3610 FND_MSG_PUB.Count_And_Get (
3611 p_encoded => FND_API.G_FALSE,
3612 p_count => x_msg_count,
3613 p_data => x_msg_data
3614 );
3615
3616 WHEN OTHERS THEN
3617 ROLLBACK TO Copy_Act_Schedule;
3618 IF c_schedule_det%ISOPEN THEN
3619 CLOSE c_schedule_det ;
3620 END IF ;
3621 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3622 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3623 THEN
3624 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3625 END IF;
3626 -- Standard call to get message count and if count=1, get the message
3627 FND_MSG_PUB.Count_And_Get (
3628 p_encoded => FND_API.G_FALSE,
3629 p_count => x_msg_count,
3630 p_data => x_msg_data
3631 );
3632
3633 END copy_act_schedules ;
3634
3635 --======================================================================
3636 -- FUNCTION
3637 -- copy_selected_schedule
3638 --
3639 -- PURPOSE
3640 -- Created to copy selected schedule of the campaign.
3641 --
3642 -- HISTORY
3643 -- 05-Sep-2001 rrajesh Created.
3644 -- 08-oct-2001 soagrawa Removed the security group id
3645 -- 18-oct-2001 soagrawa Fixed bug# 2063240
3646 -- 02-Nov-2001 rrajesh Modified to copy schedule attributes along with
3647 -- copying schedules of a campaign
3648 -- 20-may-2002 soagrawa Modified to fix bug # 2380670
3649 -- 11-july-2003 anchaudh fixed bug#3046802
3650 --======================================================================
3651 PROCEDURE copy_selected_schedule(
3652 p_old_camp_id IN NUMBER,
3653 p_new_camp_id IN NUMBER,
3654 p_old_schedule_id IN NUMBER,
3655 p_new_start_date IN DATE,
3656 p_new_end_date IN DATE,
3657 x_return_status OUT NOCOPY VARCHAR2,
3658 x_msg_count OUT NOCOPY NUMBER,
3659 x_msg_data OUT NOCOPY VARCHAR2)
3660 IS
3661 L_API_NAME CONSTANT VARCHAR2(30) := 'copy_act_schedules';
3662
3663 CURSOR c_schedule_details IS
3664 SELECT * FROM ams_campaign_Schedules_vl
3665 WHERE campaign_id = p_old_camp_id
3666 and schedule_id = p_old_schedule_id;
3667 l_reference_rec c_schedule_details%ROWTYPE;
3668
3669 -- following cursor added by soagrawa on 18-oct-2001
3670 -- bug# 2063240
3671 CURSOR c_camp_details IS
3672 SELECT owner_user_id
3673 FROM ams_campaigns_all_b
3674 WHERE campaign_id = p_new_camp_id;
3675
3676 l_schedule_rec AMS_Camp_Schedule_PVT.schedule_rec_type ;
3677 l_camp_owner NUMBER;
3678
3679 -- Added by rrajesh on 11/02/01
3680 l_attr_list Ams_CopyActivities_PVT.schedule_attr_rec_type;
3681
3682 -- from here added by soagrawa on 20-may-2002 for bug# 2380670
3683 CURSOR fetch_event_details (event_id NUMBER) IS
3684 SELECT * FROM ams_event_offers_vl
3685 WHERE event_offer_id = event_id ;
3686
3687 CURSOR c_delivery (delv_id NUMBER) IS
3688 SELECT delivery_media_type_code
3689 FROM ams_act_delivery_methods
3690 WHERE activity_delivery_method_id = delv_id;
3691
3692 -- soagrawa 22-oct-2002 for bug# 2594717
3693 CURSOR c_eone_srccd(event_id NUMBER) IS
3694 SELECT source_code
3695 FROM ams_event_offers_all_b
3696 WHERE event_offer_id = event_id;
3697
3698 l_eone_srccd VARCHAR2(30);
3699
3700 l_new_event_offer_id NUMBER;
3701 l_event_offer_rec AMS_EventOffer_PVT.evo_rec_type;
3702 l_reference_event_rec fetch_event_details%ROWTYPE;
3703
3704
3705 BEGIN
3706 -- Standard Start of API savepoint
3707 SAVEPOINT Copy_Act_Schedule;
3708
3709 -- Debug Message
3710 IF (AMS_DEBUG_HIGH_ON) THEN
3711
3712 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
3713 END IF;
3714 -- Initialize API return status to SUCCESS
3715 x_return_status := FND_API.G_RET_STS_SUCCESS;
3716
3717 -- fetching from c_camp_details added by soagrawa on 18-oct-2001
3718 -- bug# 2063240
3719 OPEN c_camp_details;
3720 FETCH c_camp_details INTO l_camp_owner;
3721 CLOSE c_camp_details;
3722
3723 OPEN c_schedule_details ;
3724 FETCH c_schedule_details INTO l_reference_rec ;
3725 l_schedule_rec.activity_type_code := l_reference_rec.activity_type_code ;
3726 l_schedule_rec.activity_id := l_reference_rec.activity_id;
3727 l_schedule_rec.arc_marketing_medium_from := l_reference_rec.arc_marketing_medium_from;
3728 l_schedule_rec.marketing_medium_id := l_reference_rec.marketing_medium_id;
3729 l_schedule_rec.custom_setup_id := l_reference_rec.custom_setup_id;
3730
3731 -- following trigger info won't get copied from now on: anchaudh for bug#3046802
3732 l_schedule_rec.triggerable_flag := 'N'; --l_reference_rec.triggerable_flag;
3733 --l_schedule_rec.trigger_id := l_reference_rec.trigger_id;
3734 -- following are added by anchaudh on 29-may-2003
3735 --l_schedule_rec.trig_repeat_flag := l_reference_rec.trig_repeat_flag;
3736 --l_schedule_rec.tgrp_exclude_prev_flag := l_reference_rec.tgrp_exclude_prev_flag;
3737
3738 l_schedule_rec.cover_letter_version := l_reference_rec.cover_letter_version;
3739
3740 l_schedule_rec.notify_user_id := l_reference_rec.notify_user_id;
3741 l_schedule_rec.approver_user_id := l_reference_rec.approver_user_id;
3742 -- modified by soagrawa on 18-oct-2001
3743 -- bug# 2063240
3744 -- l_schedule_rec.owner_user_id := l_reference_rec.owner_user_id;
3745 l_schedule_rec.owner_user_id := l_camp_owner;
3746 l_schedule_rec.active_flag := l_reference_rec.active_flag;
3747 l_schedule_rec.cover_letter_id := l_reference_rec.cover_letter_id;
3748 l_schedule_rec.reply_to_mail := l_reference_rec.reply_to_mail;
3749 l_schedule_rec.mail_sender_name := l_reference_rec.mail_sender_name;
3750 l_schedule_rec.mail_subject := l_reference_rec.mail_subject;
3751 l_schedule_rec.from_fax_no := l_reference_rec.from_fax_no;
3752 l_schedule_rec.accounts_closed_flag := l_reference_rec.accounts_closed_flag;
3753 l_schedule_rec.org_id := l_reference_rec.org_id;
3754 l_schedule_rec.objective_code := l_reference_rec.objective_code;
3755 l_schedule_rec.country_id := l_reference_rec.country_id;
3756 l_schedule_rec.campaign_calendar := l_reference_rec.campaign_calendar;
3757 l_schedule_rec.start_period_name := l_reference_rec.start_period_name;
3758 l_schedule_rec.end_period_name := l_reference_rec.end_period_name;
3759 l_schedule_rec.priority := l_reference_rec.priority;
3760 l_schedule_rec.workflow_item_key := l_reference_rec.workflow_item_key;
3761 l_schedule_rec.transaction_currency_code := l_reference_rec.transaction_currency_code;
3762 l_schedule_rec.functional_currency_code := l_reference_rec.functional_currency_code;
3763 l_schedule_rec.budget_amount_tc := l_reference_rec.budget_amount_tc;
3764 l_schedule_rec.budget_amount_fc := l_reference_rec.budget_amount_fc;
3765 l_schedule_rec.language_code := l_reference_rec.language_code;
3766 l_schedule_rec.task_id := l_reference_rec.task_id;
3767 -- l_schedule_rec.related_event_from := l_reference_rec.related_event_from;
3768 -- copying event offer id removed by soagrawa on 20-may-2002 for bug# 2380670
3769 -- l_schedule_rec.related_event_id := l_reference_rec.related_event_id;
3770 l_schedule_rec.related_event_id := NULL;
3771
3772 l_schedule_rec.attribute_category := l_reference_rec.attribute_category;
3773 l_schedule_rec.attribute1 := l_reference_rec.attribute1;
3774 l_schedule_rec.attribute2 := l_reference_rec.attribute2;
3775 l_schedule_rec.attribute3 := l_reference_rec.attribute3;
3776 l_schedule_rec.attribute4 := l_reference_rec.attribute4;
3777 l_schedule_rec.attribute5 := l_reference_rec.attribute5;
3778 l_schedule_rec.attribute6 := l_reference_rec.attribute6;
3779 l_schedule_rec.attribute7 := l_reference_rec.attribute7;
3780 l_schedule_rec.attribute8 := l_reference_rec.attribute8;
3781 l_schedule_rec.attribute9 := l_reference_rec.attribute9;
3782 l_schedule_rec.attribute10 := l_reference_rec.attribute10;
3783 l_schedule_rec.attribute11 := l_reference_rec.attribute11;
3784 l_schedule_rec.attribute12 := l_reference_rec.attribute12;
3785 l_schedule_rec.attribute13 := l_reference_rec.attribute13;
3786 l_schedule_rec.attribute14 := l_reference_rec.attribute14;
3787 l_schedule_rec.attribute15 := l_reference_rec.attribute15;
3788 l_schedule_rec.activity_attribute_category := l_reference_rec.activity_attribute_category;
3789 l_schedule_rec.activity_attribute1 := l_reference_rec.activity_attribute1;
3790 l_schedule_rec.activity_attribute2 := l_reference_rec.activity_attribute2;
3791 l_schedule_rec.activity_attribute3 := l_reference_rec.activity_attribute3;
3792 l_schedule_rec.activity_attribute4 := l_reference_rec.activity_attribute4;
3793 l_schedule_rec.activity_attribute5 := l_reference_rec.activity_attribute5;
3794 l_schedule_rec.activity_attribute6 := l_reference_rec.activity_attribute6;
3795 l_schedule_rec.activity_attribute7 := l_reference_rec.activity_attribute7;
3796 l_schedule_rec.activity_attribute8 := l_reference_rec.activity_attribute8;
3797 l_schedule_rec.activity_attribute9 := l_reference_rec.activity_attribute9;
3798 l_schedule_rec.activity_attribute10 := l_reference_rec.activity_attribute10;
3799 l_schedule_rec.activity_attribute11 := l_reference_rec.activity_attribute11;
3800 l_schedule_rec.activity_attribute12 := l_reference_rec.activity_attribute12;
3801 l_schedule_rec.activity_attribute13 := l_reference_rec.activity_attribute13;
3802 l_schedule_rec.activity_attribute14 := l_reference_rec.activity_attribute14;
3803 l_schedule_rec.activity_attribute15 := l_reference_rec.activity_attribute15;
3804 -- removed by soagrawa on 08-oct-2001
3805 -- l_schedule_rec.security_group_id := l_reference_rec.security_group_id;
3806 l_schedule_rec.schedule_name := l_reference_rec.schedule_name;
3807 l_schedule_rec.description := l_reference_rec.description;
3808
3809 -- soagrawa 22-oct-2002 for bug# 2594717
3810 -- l_schedule_rec.related_source_object := l_reference_rec.related_event_from;
3811 -- l_schedule_rec.related_source_id := l_reference_rec.related_event_id;
3812
3813 l_schedule_rec.query_id := l_reference_rec.query_id;
3814 l_schedule_rec.include_content_flag := l_reference_rec.include_content_flag;
3815 l_schedule_rec.content_type := l_reference_rec.content_type;
3816 l_schedule_rec.test_email_address := l_reference_rec.test_email_address ;
3817 l_schedule_rec.greeting_text := l_reference_rec.greeting_text;
3818 l_schedule_rec.footer_text := l_reference_rec.footer_text;
3819 -- dbiswas added the following two columns for copy on Aug 15, 2003
3820 l_schedule_rec.usage := l_reference_rec.usage;
3821 l_schedule_rec.purpose := l_reference_rec.purpose;
3822 -- dbiswas added the following column for copy on Aug 25, 2003
3823 l_schedule_rec.sales_methodology_id := l_reference_rec.sales_methodology_id;
3824
3825 IF (AMS_DEBUG_HIGH_ON) THEN
3826
3827
3828
3829 AMS_Utility_PVT.Debug_Message('Copy Timezone and other details');
3830
3831 END IF;
3832 l_schedule_rec.timezone_id := l_reference_rec.timezone_id;
3833 l_schedule_rec.user_status_id := AMS_Utility_PVT.get_default_user_status('AMS_CAMPAIGN_SCHEDULE_STATUS','NEW') ;
3834 l_schedule_rec.status_code := 'NEW';
3835 l_schedule_rec.campaign_id := p_new_camp_id ;
3836 l_schedule_rec.source_code := null;
3837 l_schedule_rec.use_parent_code_flag := l_reference_rec.use_parent_code_flag;
3838
3839 -- #Fix for bug 2989203 by asaha
3840 IF(l_schedule_rec.activity_type_code = 'EVENTS' AND
3841 l_schedule_rec.use_parent_code_flag = 'Y') THEN
3842 IF(AMS_DEBUG_HIGH_ON) THEN
3843 AMS_UTILITY_PVT.Debug_Message('change use parent flag to N for Event type schedule');
3844 END IF;
3845 l_schedule_rec.use_parent_code_flag := 'N';
3846 END IF;
3847 -- end of Fix for bug 2989203
3848
3849 l_schedule_rec.start_date_time := p_new_start_date ;
3850
3851 l_schedule_rec.end_date_time := p_new_start_date + (l_reference_rec.end_date_time - l_reference_rec.start_date_time) ;
3852
3853 if (l_schedule_rec.end_date_time > p_new_end_date)
3854 THEN
3855 l_schedule_rec.end_date_time := p_new_end_date;
3856 END IF;
3857
3858 l_schedule_rec.schedule_id := null ;
3859
3860
3861 -- from here added by soagrawa on 20-may-2002 for bug# 2380670
3862 -- copy event details into a new EONE and update new schedule with that new id
3863 IF l_reference_rec.activity_type_code = 'EVENTS'
3864 AND l_reference_rec.related_event_id IS NOT null
3865 THEN
3866
3867 -- get original related event's data
3868 OPEN fetch_event_details(l_reference_rec.related_event_id);
3869 FETCH fetch_event_details INTO l_reference_event_rec;
3870 CLOSE fetch_event_details;
3871
3872 OPEN c_delivery(l_reference_event_rec.event_delivery_method_id);
3873 FETCH c_delivery INTO l_event_offer_rec.event_delivery_method_code;
3874 CLOSE c_delivery;
3875
3876
3877 -- copy whatever remains same
3878 l_event_offer_rec.event_level := l_reference_event_rec.event_level ;
3879 l_event_offer_rec.event_type_code := l_reference_event_rec.event_type_code ;
3880 l_event_offer_rec.event_object_type := 'EONE' ;
3881
3882 -- l_event_offer_rec.event_delivery_method_id := l_reference_event_rec.event_delivery_method_id ;
3883 l_event_offer_rec.event_venue_id := l_reference_event_rec.event_venue_id ;
3884 l_event_offer_rec.event_location_id := l_reference_event_rec.event_location_id ;
3885 l_event_offer_rec.reg_required_flag := l_reference_event_rec.reg_required_flag ;
3886 l_event_offer_rec.reg_charge_flag := l_reference_event_rec.reg_charge_flag ;
3887 l_event_offer_rec.reg_invited_only_flag := l_reference_event_rec.reg_invited_only_flag ;
3888 l_event_offer_rec.event_standalone_flag := l_reference_event_rec.event_standalone_flag ;
3889 l_event_offer_rec.create_attendant_lead_flag := l_reference_event_rec.create_attendant_lead_flag ;
3890 l_event_offer_rec.create_registrant_lead_flag := l_reference_event_rec.create_registrant_lead_flag ;
3891 l_event_offer_rec.private_flag := l_reference_event_rec.private_flag ;
3892 l_event_offer_rec.parent_type := l_reference_event_rec.parent_type;
3893 l_event_offer_rec.country_code := l_reference_event_rec.country_code;
3894 l_event_offer_rec.user_status_id := l_reference_event_rec.user_status_id;
3895 l_event_offer_rec.system_status_code := l_reference_event_rec.system_status_code;
3896 l_event_offer_rec.application_id := l_reference_event_rec.application_id;
3897 l_event_offer_rec.custom_setup_id := l_reference_event_rec.setup_type_id;
3898
3899 -- modify whatever needs to be changed
3900 l_event_offer_rec.event_start_date := l_schedule_rec.start_date_time ;
3901 l_event_offer_rec.event_end_date := l_schedule_rec.end_date_time ;
3902 l_event_offer_rec.event_offer_name := l_schedule_rec.schedule_name;
3903 l_event_offer_rec.owner_user_id := l_schedule_rec.owner_user_id;
3904 -- l_event_offer_rec.source_code := NVL (l_event_offer_rec.source_code, NULL);
3905 -- l_event_offer_rec.currency_code_tc := NVL (l_event_offer_rec.source_code, NULL);
3906 l_event_offer_rec.event_language_code:= l_schedule_rec.language_code;
3907 l_event_offer_rec.parent_id := l_schedule_rec.campaign_id;
3908
3909 -- null valued attributes
3910 l_event_offer_rec.business_unit_id := NULL;
3911 l_event_offer_rec.reg_start_date := NULL;
3912 l_event_offer_rec.reg_end_date := NULL;
3913 l_event_offer_rec.city := NULL;
3914 l_event_offer_rec.state := NULL;
3915 l_event_offer_rec.description := NULL;
3916 l_event_offer_rec.start_period_name := NULL;
3917 l_event_offer_rec.end_period_name := NULL;
3918 l_event_offer_rec.priority_type_code := NULL;
3919 l_event_offer_rec.INVENTORY_ITEM_ID := NULL;
3920 l_event_offer_rec.PRICELIST_HEADER_ID := NULL;
3921 l_event_offer_rec.PRICELIST_LINE_ID := NULL;
3922 l_event_offer_rec.FORECASTED_REVENUE := NULL;
3923 l_event_offer_rec.ACTUAL_REVENUE := NULL;
3924 l_event_offer_rec.FORECASTED_COST := NULL;
3925 l_event_offer_rec.ACTUAL_COST := NULL;
3926 l_event_offer_rec.FUND_SOURCE_TYPE_CODE := NULL;
3927 l_event_offer_rec.FUND_SOURCE_ID := NULL;
3928 l_event_offer_rec.FUND_AMOUNT_FC := NULL;
3929 l_event_offer_rec.FUND_AMOUNT_TC := NULL;
3930
3931 IF (AMS_DEBUG_HIGH_ON) THEN
3932
3933
3934
3935 AMS_UTILITY_PVT.debug_message('before req_items>'||l_event_offer_rec.event_delivery_method_id||'<');
3936
3937 END IF;
3938 IF (AMS_DEBUG_HIGH_ON) THEN
3939
3940 AMS_UTILITY_PVT.debug_message('before req_items>'||l_reference_event_rec.event_delivery_method_id||'<');
3941 END IF;
3942
3943
3944 -- created the event EONE
3945 AMS_EventOffer_PVT.create_event_offer (
3946 p_api_version => 1.0,
3947 p_init_msg_list => FND_API.G_FALSE,
3948 p_commit => FND_API.G_FALSE,
3949 p_validation_level => FND_API.g_valid_level_full,
3950 p_evo_rec => l_event_offer_rec,
3951 x_return_status => x_return_status,
3952 x_msg_count => x_msg_count,
3953 x_msg_data => x_msg_data,
3954 x_evo_id => l_new_event_offer_id
3955 );
3956
3957
3958 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3959 RAISE FND_API.G_EXC_ERROR;
3960 END IF;
3961
3962
3963
3964 /*
3965 AMS_EventSchedule_Copy_PVT.copy_act_delivery_method(
3966 p_src_act_type => 'EONE',
3967 p_new_act_type => 'EONE',
3968 p_src_act_id => l_schedule_rec.related_event_id,
3969 p_new_act_id => l_new_event_offer_id,
3970 p_errnum => l_errnum,
3971 p_errcode => l_errcode,
3972 p_errmsg => l_errmsg
3973 );
3974
3975 IF l_errnum > 0 THEN
3976 RAISE FND_API.G_EXC_ERROR;
3977 END IF;
3978 */
3979
3980
3981 -- update new schedule with this id
3982 l_schedule_rec.related_event_from := l_schedule_rec.related_event_from;
3983 l_schedule_rec.related_event_id := l_new_event_offer_id;
3984
3985 -- soagrawa 22-oct-2002 for bug# 2594717
3986 OPEN c_eone_srccd(l_new_event_offer_id);
3987 FETCH c_eone_srccd INTO l_eone_srccd;
3988 CLOSE c_eone_srccd;
3989 l_schedule_rec.related_source_id := l_new_event_offer_id;
3990 l_schedule_rec.related_source_code := l_eone_srccd;
3991 l_schedule_rec.related_source_object := 'EONE';
3992
3993 END IF;
3994
3995
3996
3997 IF (AMS_DEBUG_HIGH_ON) THEN
3998
3999
4000
4001
4002
4003
4004
4005 AMS_Utility_PVT.Debug_Message('Create Schedule');
4006
4007
4008
4009 END IF;
4010 AMS_Camp_Schedule_PVT.Create_Camp_Schedule(
4011 p_api_version_number => 1,
4012 p_init_msg_list => FND_API.G_FALSE,
4013 x_return_status => x_return_status,
4014 x_msg_count => x_msg_count,
4015 x_msg_data => x_msg_data,
4016
4017 p_schedule_rec => l_schedule_rec,
4018 x_schedule_id => l_schedule_rec.schedule_id
4019 );
4020
4021 IF x_return_status = FND_API.g_ret_sts_error THEN
4022 RAISE FND_API.g_exc_error;
4023 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
4024 RAISE FND_API.g_exc_unexpected_error;
4025 END IF;
4026
4027 -- Following code is added by rrajesh on 11/02/01. bug fix:
4028 l_attr_list.p_AGEN := 'Y';
4029 l_attr_list.p_ATCH := 'Y';
4030 l_attr_list.p_CATG := 'Y';
4031 l_attr_list.p_CELL := 'Y';
4032 l_attr_list.p_DELV := 'Y';
4033 l_attr_list.p_MESG := 'Y';
4034 l_attr_list.p_PROD := 'Y';
4035 l_attr_list.p_PTNR := 'Y';
4036 l_attr_list.p_REGS := 'Y';
4037
4038 Ams_CopyActivities_PVT.copy_schedule_attributes (
4039 p_api_version => 1.0,
4040 p_init_msg_list => FND_API.G_FALSE,
4041 p_commit => FND_API.G_FALSE,
4042 x_return_status => x_return_status,
4043 x_msg_count => x_msg_count,
4044 x_msg_data => x_msg_data,
4045 p_object_type => 'CSCH',
4046 p_src_object_id => p_old_schedule_id,
4047 p_tar_object_id => l_schedule_rec.schedule_id,
4048 p_attr_list => l_attr_list
4049 );
4050 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4051 RAISE FND_API.G_EXC_ERROR;
4052 END IF;
4053 -- End change 11/02/01
4054
4055 --END LOOP;
4056 CLOSE c_schedule_details ;
4057
4058 -- Debug Message
4059 IF (AMS_DEBUG_HIGH_ON) THEN
4060
4061 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
4062 END IF;
4063
4064 -- Standard call to get message count and if count is 1, get message info.
4065 FND_MSG_PUB.Count_And_Get
4066 (p_count => x_msg_count,
4067 p_data => x_msg_data
4068 );
4069 EXCEPTION
4070
4071 WHEN AMS_Utility_PVT.resource_locked THEN
4072 IF c_schedule_details%ISOPEN THEN
4073 CLOSE c_schedule_details ;
4074 END IF ;
4075 x_return_status := FND_API.g_ret_sts_error;
4076 AMS_Utility_Pvt.Error_Message('AMS_API_RESOURCE_LOCKED');
4077
4078 WHEN FND_API.G_EXC_ERROR THEN
4079 ROLLBACK TO Copy_Act_Schedule;
4080 IF c_schedule_details%ISOPEN THEN
4081 CLOSE c_schedule_details ;
4082 END IF ;
4083 x_return_status := FND_API.G_RET_STS_ERROR;
4084 -- Standard call to get message count and if count=1, get the message
4085 FND_MSG_PUB.Count_And_Get (
4086 p_encoded => FND_API.G_FALSE,
4087 p_count => x_msg_count,
4088 p_data => x_msg_data
4089 );
4090
4091 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4092 ROLLBACK TO Copy_Act_Schedule;
4093 IF c_schedule_details%ISOPEN THEN
4094 CLOSE c_schedule_details ;
4095 END IF ;
4096 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4097 -- Standard call to get message count and if count=1, get the message
4098 FND_MSG_PUB.Count_And_Get (
4099 p_encoded => FND_API.G_FALSE,
4100 p_count => x_msg_count,
4101 p_data => x_msg_data
4102 );
4103
4104 WHEN OTHERS THEN
4105 ROLLBACK TO Copy_Act_Schedule;
4106 IF c_schedule_details%ISOPEN THEN
4107 CLOSE c_schedule_details ;
4108 END IF ;
4109 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4110 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4111 THEN
4112 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4113 END IF;
4114 -- Standard call to get message count and if count=1, get the message
4115 FND_MSG_PUB.Count_And_Get (
4116 p_encoded => FND_API.G_FALSE,
4117 p_count => x_msg_count,
4118 p_data => x_msg_data
4119 );
4120 END copy_selected_schedule ;
4121
4122
4123
4124 -- added by soagrawa on 25-jan-2002 to copy content
4125 -- bug# 2175580
4126
4127 PROCEDURE copy_act_content (
4128 p_src_act_type IN VARCHAR2,
4129 p_new_act_type IN VARCHAR2 := NULL,
4130 p_src_act_id IN NUMBER,
4131 p_new_act_id IN NUMBER,
4132 p_errnum OUT NOCOPY NUMBER,
4133 p_errcode OUT NOCOPY VARCHAR2,
4134 p_errmsg OUT NOCOPY VARCHAR2
4135 )
4136 IS
4137 -- PL/SQL Block
4138 l_stmt_num NUMBER;
4139 l_mesg_text VARCHAR2 (2000);
4140 l_api_version NUMBER := 1 ;
4141 l_return_status VARCHAR2 (1);
4142 x_msg_count NUMBER;
4143 l_msg_data VARCHAR2 (512);
4144 l_act_attachment_id NUMBER;
4145 attach_rec jtf_amv_attachment_pub.act_attachment_rec_type;
4146 temp_attach_rec jtf_amv_attachment_pub.act_attachment_rec_type;
4147 l_lookup_meaning VARCHAR2(80);
4148
4149 CURSOR attachments_cur
4150 IS
4151 SELECT *
4152 FROM jtf_amv_attachments
4153 WHERE attachment_used_by_id = p_src_act_id
4154 AND attachment_used_by = p_src_act_type
4155 AND (attachment_type IN ('WEB_TEXT' ,'WEB_IMAGE'));
4156
4157 attachments_rec attachments_cur%ROWTYPE ;
4158 l_dummy_id NUMBER ;
4159
4160 BEGIN
4161
4162
4163 p_errcode := NULL;
4164 p_errnum := 0;
4165 p_errmsg := NULL;
4166 ams_utility_pvt.get_lookup_meaning ( 'AMS_SYS_ARC_QUALIFIER',
4167 'ATCH',
4168 l_return_status,
4169 l_lookup_meaning
4170 );
4171 fnd_message.set_name ('AMS', 'AMS_COPY_ELEMENTS');
4172 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
4173 l_mesg_text := fnd_message.get;
4174 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
4175 p_src_act_id ,
4176 l_mesg_text,
4177 'GENERAL'
4178 );
4179 l_stmt_num := 1;
4180
4181
4182 -- Create jtf amv attachments
4183 FOR attachments_rec IN attachments_cur
4184 LOOP
4185 BEGIN
4186
4187 p_errcode := NULL;
4188 p_errnum := 0;
4189 p_errmsg := NULL;
4190 l_api_version := 1.0;
4191 l_return_status := NULL;
4192 x_msg_count := 0;
4193 l_msg_data := NULL;
4194 l_act_attachment_id := 0;
4195 attach_rec := temp_attach_rec;
4196 attach_rec.owner_user_id := attachments_rec.owner_user_id;
4197 attach_rec.attachment_used_by_id := p_new_act_id;
4198 attach_rec.attachment_used_by := attachments_rec.attachment_used_by;
4199 attach_rec.version := attachments_rec.version;
4200 attach_rec.enabled_flag := attachments_rec.enabled_flag;
4201 attach_rec.can_fulfill_electronic_flag :=
4202 attachments_rec.can_fulfill_electronic_flag;
4203 attach_rec.file_id := attachments_rec.file_id;
4204 attach_rec.file_name := attachments_rec.file_name;
4205 attach_rec.file_extension := attachments_rec.file_extension;
4206 attach_rec.keywords := attachments_rec.keywords;
4207 attach_rec.display_width := attachments_rec.display_width;
4208 attach_rec.display_height := attachments_rec.display_height;
4209 attach_rec.display_location := attachments_rec.display_location;
4210 attach_rec.link_to := attachments_rec.link_to;
4211 attach_rec.link_url := attachments_rec.link_url;
4212 attach_rec.send_for_preview_flag := attachments_rec.send_for_preview_flag;
4213 attach_rec.attachment_type := attachments_rec.attachment_type;
4214 attach_rec.language_code := attachments_rec.language_code;
4215 attach_rec.application_id := attachments_rec.application_id;
4216 attach_rec.description := attachments_rec.description;
4217 attach_rec.default_style_sheet := attachments_rec.default_style_sheet;
4218 attach_rec.display_url := attachments_rec.display_url;
4219 attach_rec.display_rule_id := attachments_rec.display_rule_id;
4220 attach_rec.display_program := attachments_rec.display_program;
4221 attach_rec.attribute_category := attachments_rec.attribute_category;
4222 attach_rec.attribute1 := attachments_rec.attribute1;
4223 attach_rec.attribute2 := attachments_rec.attribute2;
4224 attach_rec.attribute3 := attachments_rec.attribute3;
4225 attach_rec.attribute4 := attachments_rec.attribute4;
4226 attach_rec.attribute5 := attachments_rec.attribute5;
4227 attach_rec.attribute6 := attachments_rec.attribute6;
4228 attach_rec.attribute7 := attachments_rec.attribute7;
4229 attach_rec.attribute8 := attachments_rec.attribute8;
4230 attach_rec.attribute9 := attachments_rec.attribute9;
4231 attach_rec.attribute10 := attachments_rec.attribute10;
4232 attach_rec.attribute11 := attachments_rec.attribute11;
4233 attach_rec.attribute12 := attachments_rec.attribute12;
4234 attach_rec.attribute13 := attachments_rec.attribute13;
4235 attach_rec.attribute14 := attachments_rec.attribute14;
4236 attach_rec.attribute15 := attachments_rec.attribute15;
4237 attach_rec.default_style_sheet := attachments_rec.default_style_sheet;
4238 attach_rec.document_id := attachments_rec.document_id;
4239 attach_rec.alternate_text := attachments_rec.alternate_text;
4240 attach_rec.attachment_sub_type := attachments_rec.attachment_sub_type;
4241
4242
4243 jtf_amv_attachment_pub.create_act_attachment (
4244 p_api_version => l_api_version,
4245 x_return_status => l_return_status,
4246 x_msg_count => x_msg_count,
4247 x_msg_data => l_msg_data,
4248 p_act_attachment_rec => attach_rec,
4249 x_act_attachment_id => l_act_attachment_id
4250 );
4251
4252 IF l_return_status = fnd_api.g_ret_sts_error
4253 OR l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4254 FOR l_counter IN 1 .. x_msg_count
4255 LOOP
4256 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
4257 l_stmt_num := 2;
4258 p_errnum := 1;
4259 p_errmsg := substr(l_mesg_text||' , '||
4260 TO_CHAR (l_stmt_num) ||
4261 ' , ' || '): ' || l_counter ||
4262 ' OF ' || x_msg_count, 1, 4000);
4263 ams_cpyutility_pvt.write_log_mesg( p_src_act_type,
4264 p_new_act_id,
4265 p_errmsg,
4266 'ERROR'
4267 );
4268 END LOOP;
4269 -- If failed write a copy failed message in the log table
4270 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
4271 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
4272 l_mesg_text := fnd_message.get;
4273 p_errmsg := SUBSTR ( l_mesg_text ||
4274 ' - ' ||
4275 ams_cpyutility_pvt.get_attachment_name (
4276 attachments_rec.attachment_id),
4277 1, 4000);
4278 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
4279 p_src_act_id,
4280 p_errmsg,
4281 'ERROR');
4282 END IF;
4283 END ;
4284
4285
4286
4287 END LOOP ;
4288 CLOSE attachments_cur ;
4289
4290
4291
4292 EXCEPTION
4293 WHEN OTHERS
4294 THEN
4295 p_errcode := SQLCODE;
4296 p_errnum := 4;
4297 l_stmt_num := 3;
4298 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR3');
4299 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
4300 l_mesg_text := fnd_message.get;
4301 p_errmsg := SUBSTR ( l_mesg_text || ' , ' ||
4302 TO_CHAR (l_stmt_num) || ' , ' || '): ' ||
4303 p_errcode || SQLERRM, 1, 4000);
4304 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
4305 p_src_act_id ,
4306 p_errmsg,
4307 'ERROR'
4308 );
4309 END copy_act_content;
4310
4311
4312 --========================================================================================================================
4313 -- FUNCTION
4314 -- copy_act_collateral
4315 --
4316 -- PURPOSE
4317 -- Created to copy collateral for 11.5.10
4318 --
4319 -- HISTORY
4320 -- 30-sep-2003 soagrawa Created.
4321 -- 28-jan-2005 spendem Fix for bug # 4145845. Added to_char function to the schedule_id
4322 -- 06-aug-2005 anchaudh modified the api to loop through the cursor values for copying collateral contents
4323 --=========================================================================================================================
4324
4325 PROCEDURE copy_act_collateral (
4326 p_src_act_type IN VARCHAR2,
4327 p_new_act_type IN VARCHAR2 := NULL,
4328 p_src_act_id IN NUMBER,
4329 p_new_act_id IN NUMBER,
4330 p_errnum OUT NOCOPY NUMBER,
4331 p_errcode OUT NOCOPY VARCHAR2,
4332 p_errmsg OUT NOCOPY VARCHAR2
4333 )
4334 IS
4335
4336 CURSOR c_get_assoc IS
4337 SELECT content_item_id
4338 FROM ibc_associations
4339 WHERE association_type_code = p_src_act_type
4340 AND ASSOCIATED_OBJECT_VAL1 = to_char(p_src_act_id); -- fix for bug # 4145845
4341
4342
4343 l_content_item_id NUMBER;
4344 l_return_status VARCHAR2 (1);
4345 l_msg_count NUMBER;
4346 l_msg_data VARCHAR2 (512);
4347 l_mesg_text VARCHAR2 (2000);
4348 l_stmt_num NUMBER;
4349
4350 BEGIN
4351
4352 OPEN c_get_assoc;
4353 LOOP
4354 FETCH c_get_assoc INTO l_content_item_id;
4355 EXIT WHEN c_get_assoc%NOTFOUND;
4356
4357 IBC_ASSOCIATIONS_GRP.Create_Association (
4358 p_api_version => 1.0,
4359 p_assoc_type_code => nvl(p_new_act_type, p_src_act_type),
4360 p_assoc_object1 => p_new_act_id,
4361 p_content_item_id => l_content_item_id,
4362 x_return_status => l_return_status,
4363 x_msg_count => l_msg_count,
4364 x_msg_data => l_msg_data
4365 );
4366
4367 IF l_return_status = fnd_api.g_ret_sts_error
4368 OR l_return_status = fnd_api.g_ret_sts_unexp_error
4369 THEN
4370 FOR l_counter IN 1 .. l_msg_count
4371 LOOP
4372 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
4373 l_stmt_num := 1;
4374 p_errnum := 1;
4375 p_errmsg := substr(l_mesg_text||' , '|| TO_CHAR (l_stmt_num) ||
4376 ' , ' || '): ' || l_counter ||
4377 ' OF ' || l_msg_count, 1, 4000);
4378 ams_cpyutility_pvt.write_log_mesg( p_src_act_type,
4379 p_src_act_id,
4380 p_errmsg,
4381 'ERROR'
4382 );
4383 END LOOP;
4384 END IF;
4385
4386 END LOOP;
4387
4388 CLOSE c_get_assoc;
4389
4390 END copy_act_collateral;
4391
4392
4393 -- ======================================================================
4394 -- FUNCTION
4395 -- copy_target_group
4396 --
4397 -- PURPOSE
4398 -- Created to copy target group of schedule for 11.5.10 LITE and CLASSIC schedules
4399 --
4400 -- HISTORY
4401 -- 06-oct-2003 sodixit Created.
4402 --======================================================================
4403
4404 PROCEDURE copy_target_group (
4405 p_src_act_type IN VARCHAR2,
4406 p_new_act_type IN VARCHAR2 := NULL,
4407 p_src_act_id IN NUMBER,
4408 p_new_act_id IN NUMBER,
4409 p_errnum OUT NOCOPY NUMBER,
4410 p_errcode OUT NOCOPY VARCHAR2,
4411 p_errmsg OUT NOCOPY VARCHAR2
4412 )
4413 IS
4414
4415 l_return_status VARCHAR2 (1);
4416 l_msg_count NUMBER;
4417 l_msg_data VARCHAR2 (512);
4418 l_mesg_text VARCHAR2 (2000);
4419 l_stmt_num NUMBER;
4420
4421 BEGIN
4422
4423 AMS_ACT_LIST_PVT.copy_target_group(
4424 p_from_schedule_id => p_src_act_id,
4425 p_to_schedule_id => p_new_act_id,
4426 p_list_used_by => 'CSCH',
4427 x_msg_count => l_msg_count,
4428 x_msg_data => l_msg_data,
4429 x_return_status => l_return_status
4430 ) ;
4431
4432 IF l_return_status = fnd_api.g_ret_sts_error
4433 OR l_return_status = fnd_api.g_ret_sts_unexp_error
4434 THEN
4435 FOR l_counter IN 1 .. l_msg_count
4436 LOOP
4437 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
4438 l_stmt_num := 1;
4439 p_errnum := 1;
4440 p_errmsg := substr(l_mesg_text||' , '|| TO_CHAR (l_stmt_num) ||
4441 ' , ' || '): ' || l_counter ||
4442 ' OF ' || l_msg_count, 1, 4000);
4443 ams_cpyutility_pvt.write_log_mesg( p_src_act_type,
4444 p_src_act_id,
4445 p_errmsg,
4446 'ERROR'
4447 );
4448 END LOOP;
4449 END IF;
4450
4451
4452 END copy_target_group;
4453
4454 -- start add procedure copy_act_task for ER 6467510 - for extending COPY functionality of activities tasks
4455
4456 PROCEDURE copy_act_task (
4457 p_src_act_type IN VARCHAR2,
4458 p_new_act_type IN VARCHAR2 := NULL,
4459 p_src_act_id IN NUMBER,
4460 p_new_act_id IN NUMBER,
4461 p_errnum OUT NOCOPY NUMBER,
4462 p_errcode OUT NOCOPY VARCHAR2,
4463 p_errmsg OUT NOCOPY VARCHAR2
4464 )
4465 -- PL/SQL Block
4466 IS
4467 l_stmt_num NUMBER;
4468 l_name VARCHAR2 (80);
4469 l_mesg_text VARCHAR2 (2000);
4470 l_api_version NUMBER;
4471 l_return_status VARCHAR2 (1);
4472 x_msg_count NUMBER;
4473 l_msg_data VARCHAR2 (512);
4474 l_lookup_meaning VARCHAR2 (80);
4475 l_task_id NUMBER;
4476 x_task_id NUMBER;
4477
4478 -- select all tasks of the calling activity
4479 CURSOR task_cur IS
4480 SELECT *
4481 FROM jtf_tasks_vl
4482 WHERE source_object_id = p_src_act_id
4483 AND source_object_type_code = p_src_act_type;
4484 BEGIN
4485
4486
4487 ams_utility_pvt.get_lookup_meaning ( 'AMS_SYS_ARC_QUALIFIER',
4488 'TASK',
4489 l_return_status,
4490 l_lookup_meaning
4491 );
4492 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
4493 RAISE FND_API.G_EXC_ERROR ;
4494 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4495 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4496 END IF;
4497
4498 fnd_message.set_name ('AMS', 'AMS_COPY_ELEMENTS');
4499 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
4500 l_mesg_text := fnd_message.get;
4501 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
4502 p_src_act_id,
4503 l_mesg_text,
4504 'GENERAL'
4505 );
4506 l_stmt_num := 1;
4507
4508 FOR task_rec IN task_cur
4509 LOOP
4510 BEGIN
4511 SAVEPOINT ams_act_tasks;
4512
4513 select jtf_tasks_s.nextval into l_task_id from dual;
4514
4515 JTF_TASKS_PUB.create_task(
4516 p_api_version => 1.0,
4517 p_init_msg_list => FND_API.G_TRUE,
4518 p_commit => FND_API.G_FALSE,
4519 p_task_id => l_task_id,
4520 p_task_name => task_rec.task_name,
4521 p_task_type_id => task_rec.task_type_id,
4522 p_description => task_rec.description,
4523 p_task_status_id => task_rec.task_status_id,
4524 p_task_priority_id => task_rec.task_priority_id,
4525 p_owner_type_code => task_rec.owner_type_code,
4526 p_owner_id => task_rec.owner_id,
4527 p_planned_start_date => task_rec.planned_start_date,
4528 p_planned_end_date => task_rec.planned_end_date,
4529 p_scheduled_start_date => task_rec.scheduled_start_date,
4530 p_scheduled_end_date => task_rec.scheduled_end_date,
4531 p_actual_start_date => task_rec.actual_start_date,
4532 p_actual_end_date => task_rec.actual_end_date,
4533 p_timezone_id => task_rec.timezone_id,
4534 p_source_object_type_code => task_rec.source_object_type_code,
4535 p_source_object_id => p_new_act_id,
4536 p_source_object_name => task_rec.source_object_name,
4537 p_planned_effort => task_rec.planned_effort,
4538 p_planned_effort_uom => task_rec.planned_effort_uom,
4539 p_private_flag => task_rec.private_flag,
4540 p_publish_flag => task_rec.publish_flag,
4541 p_restrict_closure_flag => task_rec.restrict_closure_flag,
4542 x_return_status => l_return_status,
4543 x_msg_count => x_msg_count,
4544 x_msg_data => l_msg_data,
4545 x_task_id => x_task_id,
4546 p_attribute1 => task_rec.attribute1,
4547 p_attribute2 => task_rec.attribute2,
4548 p_attribute3 => task_rec.attribute3,
4549 p_attribute4 => task_rec.attribute4,
4550 p_attribute5 => task_rec.attribute5,
4551 p_attribute6 => task_rec.attribute6,
4552 p_attribute7 => task_rec.attribute7,
4553 p_attribute8 => task_rec.attribute8,
4554 p_attribute9 => task_rec.attribute9,
4555 p_attribute10 => task_rec.attribute10,
4556 p_attribute11 => task_rec.attribute11,
4557 p_attribute12 => task_rec.attribute12,
4558 p_attribute13 => task_rec.attribute13,
4559 p_attribute14 => task_rec.attribute14,
4560 p_attribute15 => task_rec.attribute15,
4561 p_attribute_category => task_rec.attribute_category,
4562 p_owner_status_id => null,
4563 p_template_id => task_rec.template_id,
4564 p_template_group_id => task_rec.template_group_id,
4565 p_date_selected => task_rec.date_selected,
4566 p_customer_id => task_rec.customer_id,
4567 p_cust_account_id => task_rec.cust_account_id,
4568 p_address_id => task_rec.address_id,
4569 p_escalation_level => task_rec.escalation_level,
4570 p_reference_flag => null,
4571 p_location_id => task_rec.location_id,
4572 p_enable_workflow => null,
4573 p_abort_workflow => null,
4574 p_task_split_flag => null);
4575
4576
4577 IF l_return_status = fnd_api.g_ret_sts_error
4578 OR l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4579 FOR l_counter IN 1 .. x_msg_count
4580 LOOP
4581 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
4582 l_stmt_num := 2;
4583 p_errnum := 1;
4584 p_errmsg := substr(l_mesg_text||' , '|| TO_CHAR (l_stmt_num) ||
4585 ' , ' || '): ' || l_counter ||
4586 ' OF ' || x_msg_count, 1, 4000);
4587 ams_cpyutility_pvt.write_log_mesg( p_src_act_type,
4588 p_src_act_id,
4589 p_errmsg,
4590 'ERROR'
4591 );
4592 END LOOP;
4593 ---- if error then right a copy log message to the log table
4594 ROLLBACK TO ams_act_tasks;
4595 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
4596 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
4597 l_mesg_text := fnd_message.get;
4598 p_errmsg := SUBSTR ( l_mesg_text || ' - ' || task_rec.task_name,1, 4000);
4599 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
4600 p_src_act_id,
4601 p_errmsg,
4602 'ERROR'
4603 );
4604 END IF;
4605 EXCEPTION
4606 WHEN OTHERS
4607 THEN
4608 ROLLBACK TO ams_act_tasks;
4609 p_errcode := SQLCODE;
4610 p_errnum := 3;
4611 l_stmt_num := 4;
4612 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR');
4613 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
4614 l_mesg_text := fnd_message.get;
4615 p_errmsg := SUBSTR ( l_mesg_text || TO_CHAR (l_stmt_num) ||
4616 '): ' || p_errcode || SQLERRM, 1, 4000);
4617
4618 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR2');
4619 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
4620 l_mesg_text := fnd_message.get;
4621 p_errmsg := l_mesg_text || task_rec.task_name || p_errmsg;
4622 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
4623 p_src_act_id,
4624 p_errmsg,
4625 'ERROR'
4626 );
4627
4628 END;
4629 END LOOP;
4630 fnd_message.set_name ('AMS', 'AMS_END_COPY_ELEMENTS');
4631 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
4632 fnd_message.set_token('ELEMENT_NAME',' ' ,TRUE);
4633 l_mesg_text := fnd_message.get;
4634 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
4635 p_src_act_id,
4636 l_mesg_text,
4637 'GENERAL'
4638 );
4639 EXCEPTION
4640 WHEN OTHERS
4641 THEN
4642 p_errcode := SQLCODE;
4643 p_errnum := 4;
4644 l_stmt_num := 5;
4645 fnd_message.set_name ('AMS', 'AMS_COPY_ERROR3');
4646 fnd_message.set_token ('ELEMENTS', l_lookup_meaning, TRUE);
4647 l_mesg_text := fnd_message.get;
4648 p_errmsg := SUBSTR ( l_mesg_text || TO_CHAR (l_stmt_num) || ',' ||
4649 '): ' || p_errcode || SQLERRM, 1, 4000);
4650 ams_cpyutility_pvt.write_log_mesg ( p_src_act_type,
4651 p_src_act_id,
4652 p_errmsg,
4653 'ERROR'
4654 );
4655 END copy_act_task;
4656
4657 -- end add procedure copy_act_task for ER 6467510 - for extending COPY functionality of activities tasks
4658
4659
4660 END ams_copyelements_pvt;