[Home] [Help]
PACKAGE BODY: APPS.AMS_CPYUTILITY_PVT
Source
1 PACKAGE BODY ams_cpyutility_pvt AS
2 /* $Header: amsvcpub.pls 120.1 2006/01/18 11:20:21 musman noship $ */
3
4 -- Start Of Comments
5 --
6 -- Name:
7 -- Ams_CPYUTILITY_PVT
8 --
9 -- Purpose:
10 -- This is the body for the utility packages in copy functionality in Oracle Marketing
11 -- from their ids.
12
13 -- GET_name functions
14 -- These functions will be called by marketing activities such as promotions,campaigns,
15 -- channels,events,etc and marketing elements such as products,scripts,resources etc while copying them
16 -- and will provide the names of the particular element if their is any error.
17 -- Function:
18 -- get_objective_name (see below for specification
19 -- get_offer_name (see below for specification)
20 -- get_script_name (see below for specification)
21 -- get_reosurce_name (see below for specification)-- get_product_name (see below for specification)
22 -- get_cell_name (see below for specification)
23 -- get_geo_area_name (see below for specification)
24 -- get_attachment_name (see below for specification)
25 -- get_deliverable_name (see below for specification)
26 -- get_business_party_name (see below for specification)
27 -- get_list_header_name (see below for specification)
28 -- get_access_name (see below for specification)
29 -- get_deliverable_header_name (see below for specification)
30 -- get_event_header_name
31
32 -- Notes:
33 --
34 -- History:
35 -- 07//1999 Mumu Pande Updated Comments
36 -- AMS_CPYUTILITY_PVT package.
37 -- 07/15/1999 Mumu Pande Created ([email protected])
38 -- 17-Feb-2001 ptendulk Added autonomous transaction. in insert_log_message
39 -- 05-Apr-2001 choang Added get_column_value
40 --
41 -- 29-Oct-2001 rrajesh Added check_attrib_exists.
42 -- End Of Comments
43 -- PL/SQL table type Global Variable for writing the log messages
44 -- PL/SQL table type Global Variable for writing the log messages
45 g_log_mesg_txt log_mesg_txt_table;
46 g_log_mesg_type log_mesg_type_table;
47 g_act_used_by log_act_used_by_table;
48 g_act_used_id log_act_used_id_table;
49 g_index NUMBER;
50
51 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
52 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
53 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
54
55 FUNCTION get_deliverable_name (p_deliverable_id IN NUMBER)
56 RETURN VARCHAR2
57 IS
58 CURSOR c_deliv_name(p_deliverable_id IN NUMBER) IS
59 SELECT deliverable_name
60 FROM ams_deliverables_vl
61 WHERE deliverable_id = p_deliverable_id;
62
63 -- PL/SQL Block
64
65 l_name VARCHAR2 (240);
66
67 BEGIN
68
69 OPEN c_deliv_name(p_deliverable_id);
70 FETCH c_deliv_name INTO l_name;
71 CLOSE c_deliv_name;
72
73 RETURN '"' || l_name || '"';
74 EXCEPTION
75 WHEN OTHERS
76 THEN
77 RETURN ' "' || p_deliverable_id || '"';
78 END get_deliverable_name;
79
80 FUNCTION get_event_header_name (p_event_header_id IN NUMBER)
81 RETURN VARCHAR2
82 IS
83
84 CURSOR c_eveh_name(p_event_header_id IN NUMBER) IS
85 SELECT event_header_name
86 FROM ams_event_headers_vl
87 WHERE event_header_id = p_event_header_id;
88
89 -- PL/SQL Block
90
91 l_name VARCHAR2 (240);
92 BEGIN
93
94 OPEN c_eveh_name(p_event_header_id);
95 FETCH c_eveh_name INTO l_name;
96 CLOSE c_eveh_name;
97
98 RETURN ' "' || l_name || '"';
99 EXCEPTION
100 WHEN OTHERS
101 THEN
102 RETURN '"' || p_event_header_id || '"';
103 END get_event_header_name;
104
105 FUNCTION get_offer_code (p_offer_id IN NUMBER)
106 RETURN VARCHAR2
107
108 IS
109 CURSOR c_offer_code(p_offer_id IN NUMBER) IS
110 SELECT offer_code
111 FROM ams_act_offers
112 WHERE activity_offer_id = p_offer_id;
113
114 -- PL/SQL Block
115
116 l_name VARCHAR2 (240);
117 BEGIN
118
119 OPEN c_offer_code(p_offer_id);
120 FETCH c_offer_code INTO l_name;
121 CLOSE c_offer_code;
122
123 RETURN ' "' || l_name || '"';
124
125 EXCEPTION
126 WHEN OTHERS
127 THEN
128 RETURN ' "' || p_offer_id || '"';
129 END get_offer_code;
130
131 FUNCTION get_product_name (p_category_id IN NUMBER)
132 RETURN VARCHAR2
133 IS
134
135 /* bug 4957294 fix musman 18-jan-06
136 CURSOR c_product_name(p_category_id IN NUMBER) IS
137 SELECT DISTINCT (category_name)
138 FROM ams_act_products_v
139 WHERE category_id = p_category_id;
140 */
141 CURSOR c_product_name(p_category_id IN NUMBER) IS
142 SELECT concatenated_segments
143 FROM MTL_CATEGORIES_b_kfv
144 WHERE category_id = p_category_id;
145 -- PL/SQL Block
146 l_name VARCHAR2 (240);
147
148 BEGIN
149
150 OPEN c_product_name(p_category_id);
151 FETCH c_product_name INTO l_name;
152 CLOSE c_product_name;
153
154
155 RETURN ' "' ||l_name || '"';
156
157 EXCEPTION
158 WHEN OTHERS
159 THEN
160 RETURN ' "' ||p_category_id||' "' ;
161
162 END get_product_name;
163
164 FUNCTION get_message_name (p_message_id IN NUMBER)
165 RETURN VARCHAR2
166 IS
167 CURSOR c_message_name(p_message_id IN NUMBER) IS
168 SELECT message_name
169 FROM ams_messages_vl
170 WHERE message_id = p_message_id;
171
172 -- PL/SQL Block
173 l_name VARCHAR2 (240);
174 BEGIN
175 OPEN c_message_name(p_message_id);
176 FETCH c_message_name INTO l_name;
177 CLOSE c_message_name;
178
179 RETURN ' "' || l_name || '"';
180 EXCEPTION
181 WHEN OTHERS
182 THEN
183 RETURN '"' || p_message_id || '"';
184 END get_message_name;
185
186 FUNCTION get_event_offer_name (p_event_offering_id IN NUMBER)
187 RETURN VARCHAR2
188 IS
189 -- PL/SQL Block
190 l_name VARCHAR2 (240);
191
192 CURSOR c_eveo_name(p_event_offering_id IN NUMBER) IS
193 SELECT event_offer_name
194 FROM ams_event_offers_vl
195 WHERE event_offer_id = p_event_offering_id;
196
197 BEGIN
198
199 OPEN c_eveo_name(p_event_offering_id);
200 FETCH c_eveo_name INTO l_name;
201 CLOSE c_eveo_name;
202
203 RETURN '"' || l_name || '"';
204 EXCEPTION
205 WHEN OTHERS
206 THEN
207 RETURN '"' || p_event_offering_id || '"';
208 END get_event_offer_name;
209
210 FUNCTION get_geo_area_name (
211 p_geo_hierarchy_id IN NUMBER,
212 p_geo_area_type IN VARCHAR2
213 )
214 RETURN VARCHAR2
215 IS
216 CURSOR c_geo_area_name( p_geo_hierarchy_id IN NUMBER,
217 p_geo_area_type IN VARCHAR2) IS
218 SELECT decode(lh.location_type_code, 'AREA1',
219 lh.area1_name, 'AREA2',
220 lh.area2_name, 'COUNTRY',
221 lh.country_name, 'CREGION',
222 lh.country_region_name, 'STATE',
223 lh.state_name, 'SREGION',
224 lh.state_region_name, 'CITY',
225 lh.city_name, 'POSTAL_CODE',
226 lh.postal_code_start||'-'||lh.postal_code_end) GEO_AREA_NAME
227 FROM jtf_loc_hierarchies_vl lh
228 WHERE location_hierarchy_id = p_geo_hierarchy_id
229 AND location_type_code = p_geo_area_type;
230 /* dbiswas changed the following select clause and replaced with the decode select clause above
231 for sql repository performance issue bug # 3631235
232 SELECT geo_area_name
233 FROM ams_act_geo_areas_v
234 WHERE geo_hierarchy_id = p_geo_hierarchy_id
235 AND geo_type_code = p_geo_area_type;
236 */
237 -- PL/SQL Block
238 l_name VARCHAR2 (240);
239 BEGIN
240
241 OPEN c_geo_area_name( p_geo_hierarchy_id,p_geo_area_type );
242 FETCH c_geo_area_name INTO l_name;
243 CLOSE c_geo_area_name;
244
245 RETURN '"' || l_name || '"';
246 EXCEPTION
247 WHEN OTHERS
248 THEN
249 RETURN '"' ||p_geo_hierarchy_id ||'"-"' ||p_geo_area_type ||'"';
250 END get_geo_area_name;
251
252 FUNCTION get_resource_name (p_resource_id IN NUMBER)
253 RETURN VARCHAR2
254 IS
255 -- PL/SQL Block
256 l_name VARCHAR2 (240);
257
258 CURSOR c_resource_name(p_resource_id IN NUMBER) IS
259 SELECT full_name
260 FROM ams_jtf_rs_emp_v
261 WHERE resource_id = p_resource_id;
262
263 BEGIN
264
265 OPEN c_resource_name(p_resource_id);
266 FETCH c_resource_name INTO l_name;
267 CLOSE c_resource_name;
268
269 RETURN ' "' ||l_name|| ' "' ;
270 EXCEPTION
271 WHEN OTHERS
272 THEN
273 RETURN ' "' || p_resource_id||' "';
274 END get_resource_name;
275
276
277 FUNCTION get_segment_name (p_cell_id IN NUMBER)
278 RETURN VARCHAR2
279 IS
280 -- PL/SQL Block
281 l_name VARCHAR2 (240);
282
283 CURSOR c_segment_name(p_cell_id IN NUMBER) IS
284 SELECT cell_name
285 FROM ams_cells_vl
286 WHERE cell_id = p_cell_id;
287
288 BEGIN
289
290 OPEN c_segment_name(p_cell_id);
291 FETCH c_segment_name INTO l_name;
292 CLOSE c_segment_name;
293
294
295 RETURN ' "' ||l_name||' "';
296 EXCEPTION
297 WHEN OTHERS
298 THEN
299 RETURN ' "' || p_cell_id||' "' ;
300 END get_segment_name;
301
302 FUNCTION get_attachment_name (p_act_attachment_id IN NUMBER)
303 RETURN VARCHAR2
304 IS
305 -- PL/SQL Block
306 l_name VARCHAR2 (240);
307
308 CURSOR c_attch_name(p_act_attachment_id IN NUMBER) IS
309 SELECT DISTINCT (file_name)
310 FROM jtf_amv_attachments
311 WHERE attachment_id = p_act_attachment_id;
312
313 BEGIN
314
315 OPEN c_attch_name(p_act_attachment_id);
316 FETCH c_attch_name INTO l_name;
317 CLOSE c_attch_name;
318
319 RETURN ' "' ||l_name||' "' ;
320 EXCEPTION
321 WHEN OTHERS
322 THEN
323 RETURN ' "' ||p_act_attachment_id||' "' ;
324 END get_attachment_name;
325
326 FUNCTION get_category_name (p_category_id IN NUMBER)
327 RETURN VARCHAR2
328 IS
329 -- PL/SQL Block
330 l_name VARCHAR2 (256);
331 CURSOR c_category_name(p_category_id IN NUMBER) IS
332 SELECT category_name
333 FROM ams_categories_tl
334 WHERE category_id = p_category_id;
335
336 BEGIN
337
338 OPEN c_category_name(p_category_id);
339 FETCH c_category_name INTO l_name;
340 CLOSE c_category_name;
341
342 RETURN '"' || l_name || '" ';
343 EXCEPTION
344 WHEN OTHERS
345 THEN
346 RETURN '"' || p_category_id || '"';
347 END get_category_name;
348
349
350 -- Sub-Program Unit Declarations
351 /* Log an error. */
352 PROCEDURE write_log_mesg (
353 p_act_type IN VARCHAR2,
354 p_act_id IN NUMBER,
355 p_message_text IN VARCHAR2,
356 p_message_type IN VARCHAR2
357 )
358 IS
359 BEGIN
360
361 BEGIN
362 g_index := NVL (g_index, 0) + 1;
363 g_log_mesg_txt (g_index) := p_message_text;
364 g_log_mesg_type (g_index) := p_message_type;
365 g_act_used_by (g_index) := p_act_type;
366 g_act_used_id (g_index) := p_act_id;
367 END;
368 EXCEPTION
369 WHEN OTHERS
370 THEN
371 RAISE;
372 END write_log_mesg;
373
374 /* Refresh the PL/SQL log table. */
375 PROCEDURE refresh_log_mesg
376 IS
377 BEGIN
378 g_index := 0;
379 g_log_mesg_txt.delete;
380 g_log_mesg_type.delete;
381 g_act_used_by.delete;
382 g_act_used_id.delete;
383 EXCEPTION
384 WHEN OTHERS
385 THEN
386 RAISE;
387 END refresh_log_mesg;
388
389 /* Write the log infomation from PL/SQL table into ams_activity_logs. */
390
391 -- 17-Feb-2001 ptendulk Made the api Autonomous
392
393 PROCEDURE insert_log_mesg (x_transaction_id OUT NOCOPY NUMBER)
394 IS
395 PRAGMA AUTONOMOUS_TRANSACTION;
396 BEGIN
397 SELECT ams_Act_logs_transaction_id_s.nextval
398 into x_transaction_id
399 FROM DUAL;
400 IF g_index <> 0
401 THEN
402 FOR i IN 1 .. g_index
403 LOOP
404 INSERT INTO ams_act_logs
405 (
406 activity_log_id,
407 last_update_date,
408 last_updated_by,
409 creation_date,
410 created_by,
411 last_update_login,
412 act_log_used_by_id,
413 arc_act_log_used_by,
414 log_transaction_id,
415 log_message_text,
416 log_message_type
417 )
418 VALUES (
419 ams_act_logs_s.nextval,
420 SYSDATE,
421 fnd_global.user_id,
422 SYSDATE,
423 fnd_global.user_id,
424 fnd_global.conc_login_id,
425 g_act_used_id (i),
426 g_act_used_by (i),
427 x_transaction_id,
428 g_log_mesg_txt (i),
429 g_log_mesg_type (i)
430 );
431 END LOOP;
432 END IF;
433 COMMIT ;
434 EXCEPTION
435 WHEN OTHERS
436 THEN
437 RAISE;
438 END insert_log_mesg;
439
440
441 FUNCTION get_dates(p_arc_act_code IN VARCHAR2,
442 p_activity_id IN NUMBER,
443 x_return_status OUT NOCOPY VARCHAR2)
444 RETURN NUMBER
445 IS
446 start_date date;
447 end_date date;
448 l_number NUMBER;
449
450 BEGIN
451
452 x_return_status := fnd_api.g_ret_sts_success;
453 l_number := 0;
454 IF p_arc_act_code = 'CAMP' THEN
455 SELECT actual_exec_end_date ,actual_exec_start_date
456 INTO end_date,start_date
457 FROM AMS_CAMPAIGNS_VL
458 WHERE campaign_id = p_activity_id;
459
460 l_number := (end_date - start_date);
461
462 ELSIF p_arc_act_code = 'EVEO' THEN
463 SELECT event_end_date,event_start_date
464 INTO end_date,start_date
465 FROM AMS_EVENT_OFFERS_VL
466 WHERE event_offer_id = p_activity_id;
467 l_number := (end_date - start_date);
468
469 ELSIF p_arc_act_code = 'EVEH' THEN
470 SELECT active_to_date,active_from_date
471 INTO end_date,start_date
472 FROM AMS_EVENT_HEADERS_VL
473 WHERE event_header_id = p_activity_id;
474 l_number := (end_date - start_date);
475 END IF ;
476
477 RETURN l_number;
478
479 EXCEPTION
480 WHEN OTHERS
481 THEN
482 x_return_status := fnd_api.g_ret_sts_error;
483 END get_dates;
484
485
486 -- History
487 -- 05-Apr-2001 choang Created.
488 PROCEDURE get_column_value (
489 p_column_name IN VARCHAR2,
490 p_columns_table IN copy_columns_table_type,
491 x_column_value OUT NOCOPY VARCHAR2
492 )
493 IS
494 BEGIN
495 -- if the column is not found, then
496 -- return null
497 FOR i IN 1..p_columns_table.COUNT LOOP
498 IF p_column_name = p_columns_table(i).column_name THEN
499 x_column_value := p_columns_table(i).column_value;
500 RETURN;
501 END IF;
502 END LOOP;
503 END get_column_value;
504
505 -- History
506 -- 05-Apr-2001 choang Created.
507 FUNCTION is_copy_attribute (
508 p_attribute IN VARCHAR2,
509 p_attributes_table IN copy_attributes_table_type
510 ) RETURN VARCHAR2
511 IS
512 BEGIN
513 FOR i IN 1..p_attributes_table.COUNT LOOP
514 IF p_attribute = p_attributes_table(i) THEN
515 RETURN FND_API.G_TRUE;
516 END IF;
517 END LOOP;
518 RETURN FND_API.G_FALSE;
519 END is_copy_attribute;
520
521 -- History
522 -- 29 OCT 2001 rrajesh Created.
523 FUNCTION check_attrib_exists (
524 p_obj_type IN VARCHAR2,
525 p_obj_id IN NUMBER,
526 p_obj_attribute IN VARCHAR2
527 ) RETURN VARCHAR2
528 IS
529 CURSOR c_csch_exists(p_camp_id NUMBER) IS
530 SELECT COUNT(1) from ams_campaign_schedules_vl
531 WHERE campaign_id = p_camp_id;
532 CURSOR c_mesg_exists(p_obj_id NUMBER, p_obj_type VARCHAR2) IS
533 SELECT COUNT(1) from ams_act_messages
534 WHERE message_used_by_id = p_obj_id
535 AND message_used_by = p_obj_type;
536 CURSOR c_delv_exists(p_obj_id NUMBER, p_obj_type VARCHAR2) IS
537 SELECT COUNT(1)
538 FROM ams_object_associations
539 WHERE master_object_id = p_obj_id
540 AND master_object_type = p_obj_type ;
541 CURSOR c_prod_exists(p_obj_id NUMBER, p_obj_type VARCHAR2) IS
542 SELECT COUNT(1)
543 FROM ams_act_products
544 WHERE act_product_used_by_id = p_obj_id
545 AND arc_act_product_used_by = p_obj_type;
546 CURSOR c_geos_exists(p_obj_id NUMBER, p_obj_type VARCHAR2) IS
547 SELECT COUNT(1)
548 FROM ams_act_geo_areas
549 WHERE act_geo_area_used_by_id = p_obj_id
550 AND arc_act_geo_area_used_by = p_obj_type;
551 CURSOR c_atch_exists(p_obj_id NUMBER, p_obj_type VARCHAR2) IS
552 SELECT COUNT(1)
553 FROM fnd_attached_documents
554 WHERE entity_name = p_obj_type
555 AND pk1_value = p_obj_id ;
556 CURSOR c_cell_exists(p_obj_id NUMBER, p_obj_type VARCHAR2) IS
557 SELECT COUNT(1)
558 FROM ams_act_market_segments
559 WHERE act_market_segment_used_by_id = p_obj_id
560 AND arc_act_market_segment_used_by = p_obj_type;
561 CURSOR c_ptnr_exists(p_obj_id NUMBER, p_obj_type VARCHAR2) IS
562 SELECT COUNT(1)
563 FROM AMS_ACT_PARTNERS
564 WHERE act_partner_used_by_id = p_obj_id
565 AND arc_act_partner_used_by = p_obj_type;
566 l_tmp NUMBER;
567 BEGIN
568 IF p_obj_attribute = 'CSCH' THEN
569 OPEN c_csch_exists(p_obj_id);
570 FETCH c_csch_exists INTO l_tmp;
571 CLOSE c_csch_exists;
572 IF l_tmp > 0 THEN
573 RETURN FND_API.G_TRUE;
574 ELSE
575 RETURN FND_API.G_FALSE;
576 END IF;
577 END IF;
578 IF p_obj_attribute = 'MESG' THEN
579 OPEN c_mesg_exists(p_obj_id, p_obj_type);
580 FETCH c_mesg_exists INTO l_tmp;
581 CLOSE c_mesg_exists;
582 IF l_tmp > 0 THEN
583 RETURN FND_API.G_TRUE;
584 ELSE
585 RETURN FND_API.G_FALSE;
586 END IF;
587 END IF;
588 IF p_obj_attribute = 'ATCH' THEN
589 OPEN c_atch_exists(p_obj_id, p_obj_type);
590 FETCH c_atch_exists INTO l_tmp;
591 IF l_tmp > 0 THEN
592 RETURN FND_API.G_TRUE;
593 ELSE
594 RETURN FND_API.G_FALSE;
595 END IF;
596 END IF;
597 IF p_obj_attribute = 'GEOS' THEN
598 OPEN c_geos_exists(p_obj_id, p_obj_type);
599 FETCH c_geos_exists INTO l_tmp;
600 IF l_tmp > 0 THEN
601 RETURN FND_API.G_TRUE;
602 ELSE
603 RETURN FND_API.G_FALSE;
604 END IF;
605 END IF;
606 IF p_obj_attribute = 'DELV' THEN
607 OPEN c_delv_exists(p_obj_id, p_obj_type);
608 FETCH c_delv_exists INTO l_tmp;
609 IF l_tmp > 0 THEN
610 RETURN FND_API.G_TRUE;
611 ELSE
612 RETURN FND_API.G_FALSE;
613 END IF;
614 END IF;
615 IF p_obj_attribute = 'PROD' THEN
616 OPEN c_prod_exists(p_obj_id, p_obj_type);
617 FETCH c_prod_exists INTO l_tmp;
618 IF l_tmp > 0 THEN
619 RETURN FND_API.G_TRUE;
620 ELSE
621 RETURN FND_API.G_FALSE;
622 END IF;
623 END IF;
624 IF p_obj_attribute = 'CELL' THEN
625 OPEN c_cell_exists(p_obj_id, p_obj_type);
626 FETCH c_cell_exists INTO l_tmp;
627 IF l_tmp > 0 THEN
628 RETURN FND_API.G_TRUE;
629 ELSE
630 RETURN FND_API.G_FALSE;
631 END IF;
632 END IF;
633 IF p_obj_attribute = 'PTNR' THEN
634 OPEN c_ptnr_exists(p_obj_id, p_obj_type);
635 FETCH c_ptnr_exists INTO l_tmp;
636 IF l_tmp > 0 THEN
637 RETURN FND_API.G_TRUE;
638 ELSE
639 RETURN FND_API.G_FALSE;
640 END IF;
641 END IF;
642
643 END check_attrib_exists;
644
645 END;