1 PACKAGE BODY AMS_EvhRules_PVT AS
2 /* $Header: amsvebrb.pls 120.1 2006/01/20 06:02:55 vmodur noship $ */
3 g_pkg_name CONSTANT VARCHAR2(30):='AMS_EvhRules_PVT';
4
5 -----------------------------------------------------------------------
6 -- PROCEDURE
7 -- handle_evh_status
8 --
9 -- HISTORY
10 -- 11/19/99 rvaka Created.
11 -- 07/20/2000 THIS PROCEDURE SHOULDNT BE CALLED AS IT DOESNT DIFFERENTIATES
12 -- WHETHER THE EVENT LEVEL IS MAIN OR SUB.. IF NEED BE, USE AFTER CHANGING THE CODE
13 -- TO INCLUDE CHECK AGAINST AMS_EVENT_AGENDA_STATUS FOR EVENT_LEVEL=SUB
14 -----------------------------------------------------------------------
15 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
16 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
17 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
18
19 -- soagrawa 03-feb-2003
20 -- added get_user_id bug# 2781219
21
22 FUNCTION get_user_id (
23 p_resource_id IN NUMBER
24 )
25 RETURN NUMBER
26 IS
27 l_user_id NUMBER;
28
29 CURSOR c_user IS
30 SELECT user_id
31 FROM ams_jtf_rs_emp_v
32 WHERE resource_id = p_resource_id;
33 BEGIN
34 OPEN c_user;
35 FETCH c_user INTO l_user_id;
36 IF c_user%NOTFOUND THEN
37 l_user_id := -1;
38 -- Adding an error message will cause the function
39 -- to violate the WNDS pragma, preventing it from
40 -- being able to be called from a SQL statement.
41 END IF;
42 CLOSE c_user;
43
44 RETURN l_user_id;
45 END get_user_id;
46
47
48 --==========================================================================
49 -- PROCEDURE
50 -- Complete_Event_Offers
51 --
52 -- PURPOSE
53 -- The api is created to complete the underlying schedules of an event
54 --
55 -- HISTORY
56 -- 05-Jan-2004 soagrawa Created.
57 -- 17-Mar-2005 spendem Call api to raise business event. enh # 3805347
58 --=============================================================================
59
60
61 PROCEDURE Complete_Event_Offers(p_eveh_id IN NUMBER) IS
62
63 -- Modified the cursor to select event_object_type, as per enh # 3805347
64 CURSOR c_ev_schedule IS
65 SELECT event_offer_id, object_version_number, system_status_code, event_object_type
66 FROM ams_event_offers_all_b
67 WHERE event_header_id = p_eveh_id
68 AND system_status_code <> 'COMPLETED' ;
69
70 l_event_offer_id NUMBER ;
71 l_obj_version NUMBER ;
72 l_status_code VARCHAR2(30) ;
73 l_status_id NUMBER := AMS_Utility_PVT.get_default_user_status('AMS_EVENT_STATUS','COMPLETED');
74 l_obj_type VARCHAR2(4); -- added as per enh # 3805347
75
76 BEGIN
77
78 OPEN c_ev_schedule ;
79 LOOP
80 FETCH c_ev_schedule
81 INTO l_event_offer_id, l_obj_version, l_status_code, l_obj_type;
82 EXIT WHEN c_ev_schedule%NOTFOUND ;
83 IF FND_API.G_TRUE = AMS_Utility_PVT.Check_Status_Change('AMS_EVENT_STATUS',l_status_code,'COMPLETED') THEN
84 -- Can cancel the event offer
85 UPDATE ams_event_offers_all_b
86 SET system_status_code = 'COMPLETED',
87 last_status_date = SYSDATE,
88 user_status_id = l_status_id,
89 object_version_number = object_version_number + 1
90 WHERE event_offer_id = l_event_offer_id
91 AND object_version_number = l_obj_version ;
92
93 -- call to api to raise business event, as per enh # 3805347
94 AMS_SCHEDULERULES_PVT.RAISE_BE_ON_STATUS_CHANGE(p_obj_id => l_event_offer_id,
95 p_obj_type => l_obj_type,
96 p_old_status_code => l_status_code,
97 p_new_status_code => 'COMPLETED' );
98
99
100 IF (SQL%NOTFOUND) THEN
101 CLOSE c_ev_schedule ;
102 AMS_Utility_PVT.Error_Message('AMS_API_RECORD_NOT_FOUND');
103 RAISE FND_API.g_exc_error;
104 END IF;
105
106 AMS_EvhRules_PVT.process_leads(p_event_id => l_event_offer_id);
107
108 ELSE -- Can not cancel the schedule as the status is can not go to cancel from current status
109 CLOSE c_ev_schedule;
110 AMS_Utility_PVT.Error_Message('AMS_EVEH_CANNOT_COMPLETE');
111 RAISE FND_API.g_exc_error;
112 END IF ;
113
114 END LOOP;
115 CLOSE c_ev_schedule;
116
117 END Complete_Event_Offers;
118
119
120
121 PROCEDURE handle_evh_status(
122 p_user_status_id IN NUMBER,
123 x_status_code OUT NOCOPY VARCHAR2,
124 x_return_status OUT NOCOPY VARCHAR2
125 )
126 IS
127 l_status_code VARCHAR2(30);
128 CURSOR e_status_code IS
129 SELECT system_status_code
130 FROM ams_user_statuses_vl
131 WHERE user_status_id = p_user_status_id
132 AND system_status_type = 'AMS_EVENT_STATUS';
133 BEGIN
134 x_return_status := FND_API.g_ret_sts_success;
135 OPEN e_status_code;
136 FETCH e_status_code INTO l_status_code;
137 CLOSE e_status_code;
138 IF l_status_code IS NULL THEN
139 x_return_status := FND_API.g_ret_sts_error;
140 AMS_Utility_PVT.error_message('AMS_EVH_NO_USER_STATUS');
141 END IF;
142 x_status_code := l_status_code;
143 END handle_evh_status;
144 ---------------------------------------------------------------------
145 -- PROCEDURE
146 -- check_evh_update
147 --
148 -- HISTORY
149 -- 11/19/99 rvaka Created.
150 -- 04/03/00 sugupta modified
151 ---------------------------------------------------------------------
152 PROCEDURE check_evh_update(
153 p_evh_rec IN AMS_EventHeader_PVT.evh_rec_type,
154 x_return_status OUT NOCOPY VARCHAR2
155 )
156 IS
157 CURSOR c_evh IS
158 SELECT *
159 FROM ams_event_headers_vl
160 WHERE event_header_id = p_evh_rec.event_header_id;
161
162 CURSOR c_source_code IS
163 SELECT 1
164 FROM ams_source_codes
165 WHERE source_code = p_evh_rec.source_code
166 AND active_flag = 'Y';
167
168 l_msg_data VARCHAR2(2000);
169 l_msg_count NUMBER;
170 l_dummy NUMBER;
171 l_evh_rec c_evh%ROWTYPE;
172
173 BEGIN
174 x_return_status := FND_API.g_ret_sts_success;
175
176 OPEN c_evh;
177 FETCH c_evh INTO l_evh_rec;
178 IF c_evh%NOTFOUND THEN
179 CLOSE c_evh;
180 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
181 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
182 FND_MSG_PUB.add;
183 END IF;
184 RAISE FND_API.g_exc_error;
185 END IF;
186 CLOSE c_evh;
187
188 ---------------------------- status codes-----------------------
189 -- change status through workflow
190 -- modified sugupta 07/20/2000
191 --NOT NEEDED FOR EVENT AGENDAS
192
193 -- Commented the old style of approval process call.
194 -- gdeodhar : Oct 06, 2000.
195 /*
196 if p_evh_rec.event_level = 'MAIN' then
197 IF p_evh_rec.user_status_id <> FND_API.g_miss_num
198 AND p_evh_rec.user_status_id <> l_evh_rec.user_status_id
199 THEN
200 AMS_WFCmpApr_PVT.StartProcess(
201 p_approval_for => 'EVEH',
202 p_approval_for_id => p_evh_rec.event_header_id,
203 p_object_version_number => p_evh_rec.object_version_number,
204 p_orig_stat_id => l_evh_rec.user_status_id,
205 p_new_stat_id => p_evh_rec.user_status_id,
206 p_requester_userid => FND_GLOBAL.user_id
207 );
208 END IF;
209
210 -- the following will be locked after theme approval
211 IF l_evh_rec.system_status_code <> 'NEW' THEN
212 IF p_evh_rec.event_header_name <> FND_API.g_miss_char
213 AND p_evh_rec.event_header_name <> l_evh_rec.event_header_name
214 THEN
215 AMS_Utility_PVT.error_message('AMS_EVH_UPDATE_EVH_NAME');
216 x_return_status := FND_API.g_ret_sts_error;
217 RETURN;
218 END IF;
219 IF p_evh_rec.active_from_date <> FND_API.g_miss_date
220 AND p_evh_rec.active_from_date <>
221 l_evh_rec.active_from_date
222 THEN
223 AMS_Utility_PVT.error_message('AMS_EVH_UPDATE_START_DATE');
224 x_return_status := FND_API.g_ret_sts_error;
225 RETURN;
226 END IF;
227 IF p_evh_rec.active_to_date <> FND_API.g_miss_date
228 AND p_evh_rec.active_to_date <>
229 l_evh_rec.active_to_date
230 THEN
231 AMS_Utility_PVT.error_message('AMS_EVH_UPDATE_END_DATE');
232 x_return_status := FND_API.g_ret_sts_error;
233 RETURN;
234 END IF;
235 END IF;
236 end if; -- event_level MAIN
237 */
238 -- Commented part for the old style of approval process call ends.
239 -- Locking of fields will be added later.
240 -- gdeodhar : Oct 06, 2000.
241
242
243 ----------------------------------------------------------------------------
244 ------------- source_code logic ------------------
245 ----------------------------------------------------------------------------
246 -- modified sugupta 07/20/2000
247 --NOT NEEDED FOR EVENT AGENDAS
248 /* commented OUT NOCOPY by murali on july17 2001
249 IF p_evh_rec.event_level = 'MAIN' THEN
250 IF p_evh_rec.source_code <> FND_API.g_miss_char
251 AND p_evh_rec.source_code <> l_evh_rec.source_code
252 THEN
253 IF p_evh_rec.source_code IS NULL THEN
254 AMS_Utility_PVT.error_message('AMS_EVH_NO_SOURCE_CODE');
255 x_return_status := FND_API.g_ret_sts_error;
256 RETURN;
257 END IF;
258
259 -- source_code cannot be changed if status is not NEW
260 IF l_evh_rec.system_status_code <> 'NEW' THEN
261 AMS_Utility_PVT.error_message('AMS_EVH_UPDATE_SOURCE_CODE');
262 x_return_status := FND_API.g_ret_sts_error;
263 RETURN;
264 END IF;
265
266 -- check if the new source code is unique
267 l_dummy := NULL;
268 OPEN c_source_code;
269 FETCH c_source_code INTO l_dummy;
270 CLOSE c_source_code;
271
272 IF l_dummy IS NOT NULL THEN
273 AMS_Utility_PVT.error_message('AMS_EVH_DUPE_SOURCE');
274 x_return_status := FND_API.g_ret_sts_error;
275 RETURN;
276 END IF;
277
278 -- otherwise revoke the old one and add the new one to ams_source_codes
279 AMS_SourceCode_PVT.revoke_sourcecode(
280 p_api_version => 1.0,
281 p_init_msg_list => FND_API.g_false,
282 p_commit => FND_API.g_false,
283 p_validation_level => FND_API.g_valid_level_full,
284
285 x_return_status => x_return_status,
286 x_msg_count => l_msg_count,
287 x_msg_data => l_msg_data,
288
289 p_sourcecode => p_evh_rec.source_code
290 );
291
292 IF x_return_status <> FND_API.g_ret_sts_success THEN
293 RAISE FND_API.g_exc_error;
294 END IF;
295
296 AMS_SourceCode_PVT.create_sourcecode(
297 p_api_version => 1.0,
298 p_init_msg_list => FND_API.g_false,
299 p_commit => FND_API.g_false,
300 p_validation_level => FND_API.g_valid_level_full,
301
302 x_return_status => x_return_status,
303 x_msg_count => l_msg_count,
304 x_msg_data => l_msg_data,
305
306 p_sourcecode => p_evh_rec.source_code,
307 p_sourcecode_for => 'EVEH',
308 p_sourcecode_for_id => p_evh_rec.event_header_id,
309 x_sourcecode_id => l_dummy
310 );
311
312 IF x_return_status <> FND_API.g_ret_sts_success THEN
313 RAISE FND_API.g_exc_error;
314 END IF;
315
316 END IF; -- p_evh_rec.source_code
317 END IF; -- p_evh_rec.event_level
318 */
319 END check_evh_update;
320 ---------------------------------------------------------------------
321 -- PROCEDURE
322 -- check_evh_fund_source
323 --
324 -- HISTORY
325 -- 11/19/99 rvaka Created.
326 ---------------------------------------------------------------------
327 PROCEDURE check_evh_fund_source(
328 p_fund_source_type IN VARCHAR2,
329 p_fund_source_id IN NUMBER,
330 x_return_status OUT NOCOPY VARCHAR2
331 )
332 IS
333 l_dummy NUMBER;
334 CURSOR c_camp IS
335 SELECT 1
336 FROM ams_campaigns_vl
337 WHERE campaign_id = p_fund_source_id;
338 CURSOR c_eveh IS
339 SELECT 1
340 FROM ams_event_headers_vl
341 WHERE event_header_id = p_fund_source_id;
342 CURSOR c_eveo IS
343 SELECT 1
344 FROM ams_event_offers_vl
345 WHERE event_offer_id = p_fund_source_id;
346 CURSOR c_eone IS
347 SELECT 1
348 FROM ams_event_offers_vl
349 WHERE event_offer_id = p_fund_source_id;
350 BEGIN
351 x_return_status := FND_API.g_ret_sts_success;
352 IF p_fund_source_type IS NULL AND p_fund_source_id IS NULL THEN
353 RETURN;
354 ELSIF p_fund_source_type IS NULL AND p_fund_source_id IS NOT NULL THEN
355 AMS_Utility_PVT.error_message('AMS_EVH_NO_FUND_SOURCE_TYPE');
356 END IF;
357 IF p_fund_source_type = 'FUND' THEN
358 -- todo add code to check against a fund
359 NULL;
360 ELSIF p_fund_source_type = 'CAMP' THEN
361 IF p_fund_source_id IS NOT NULL THEN
362 OPEN c_camp;
363 FETCH c_camp INTO l_dummy;
364 IF c_camp%NOTFOUND THEN
365 x_return_status := FND_API.g_ret_sts_error;
366 AMS_Utility_PVT.error_message('AMS_EVH_BAD_FUND_SOURCE_CAMP');
367 END IF;
368 CLOSE c_camp;
369 END IF;
370 ELSIF p_fund_source_type = 'EVEH' THEN
371 IF p_fund_source_id IS NOT NULL THEN
372 OPEN c_eveh;
373 FETCH c_eveh INTO l_dummy;
374 IF c_eveh%NOTFOUND THEN
375 x_return_status := FND_API.g_ret_sts_error;
376 AMS_Utility_PVT.error_message('AMS_EVH_BAD_FUND_SOURCE_EVEH');
377 END IF;
378 CLOSE c_eveh;
379 END IF;
380 ELSIF p_fund_source_type = 'EVEO' THEN
381 IF p_fund_source_id IS NOT NULL THEN
382 OPEN c_eveo;
383 FETCH c_eveo INTO l_dummy;
384 IF c_eveo%NOTFOUND THEN
385 x_return_status := FND_API.g_ret_sts_error;
386 AMS_Utility_PVT.error_message('AMS_EVH_BAD_FUND_SOURCE_EVEO');
387 END IF;
388 CLOSE c_eveo;
389 END IF;
390 ELSIF p_fund_source_type = 'EONE' THEN
391 IF p_fund_source_id IS NOT NULL THEN
392 OPEN c_eone;
393 FETCH c_eone INTO l_dummy;
394 IF c_eone%NOTFOUND THEN
395 x_return_status := FND_API.g_ret_sts_error;
396 AMS_Utility_PVT.error_message('AMS_EVH_BAD_FUND_SOURCE_EVEO');
397 END IF;
398 CLOSE c_eone;
399 END IF;
400 ELSE
401 x_return_status := FND_API.g_ret_sts_error;
402 AMS_Utility_PVT.error_message('AMS_EVH_BAD_FUND_SOURCE');
403 END IF;
404 END check_evh_fund_source;
405 ---------------------------------------------------------------------
406 -- PROCEDURE
407 -- check_evh_calendar
408 --
409 -- HISTORY
410 -- 10/01/2000 sugupta Created.
411 ---------------------------------------------------------------------
412 PROCEDURE check_evh_calendar(
413 p_evh_calendar IN VARCHAR2,
414 p_start_period_name IN VARCHAR2,
415 p_end_period_name IN VARCHAR2,
416 p_start_date IN DATE,
417 p_end_date IN DATE,
418 x_return_status OUT NOCOPY VARCHAR2
419 )
420 IS
421
422 l_start_start DATE;
423 l_start_end DATE;
424 l_end_start DATE;
425 l_end_end DATE;
426 l_dummy NUMBER;
427
428 CURSOR c_evh_calendar IS
429 SELECT 1
430 FROM DUAL
431 WHERE EXISTS(
432 SELECT 1
433 FROM gl_periods_v
434 WHERE period_set_name = p_evh_calendar
435 );
436
437 CURSOR c_start_period IS
438 SELECT start_date, end_date
439 FROM gl_periods_v
440 WHERE period_set_name = p_evh_calendar
441 AND period_name = p_start_period_name;
442
443 CURSOR c_end_period IS
444 SELECT start_date, end_date
445 FROM gl_periods_v
446 WHERE period_set_name = p_evh_calendar
447 AND period_name = p_end_period_name;
448
449 BEGIN
450
451 x_return_status := FND_API.g_ret_sts_success;
452
453 -- check if p_evh_calendar is null
454 IF p_evh_calendar IS NULL
455 AND p_start_period_name IS NULL
456 AND p_end_period_name IS NULL
457 THEN
458 RETURN;
459 ELSIF p_evh_calendar IS NULL THEN
460 x_return_status := FND_API.g_ret_sts_error;
461 AMS_Utility_PVT.error_message('AMS_EVH_NO_EVENT_CALENDAR');
462 RETURN;
463 END IF;
464
465 -- check if p_evh_calendar is valid
466 OPEN c_evh_calendar;
467 FETCH c_evh_calendar INTO l_dummy;
468 CLOSE c_evh_calendar;
469
470 IF l_dummy IS NULL THEN
471 x_return_status := FND_API.g_ret_sts_error;
472 AMS_Utility_PVT.error_message('AMS_EVH_BAD_EVENT_CALENDAR');
473 RETURN;
474 END IF;
475
476 -- check p_start_period_name
477 IF p_start_period_name IS NOT NULL THEN
478 OPEN c_start_period;
479 FETCH c_start_period INTO l_start_start, l_start_end;
480 CLOSE c_start_period;
481
482 IF l_start_start IS NULL THEN
483 x_return_status := FND_API.g_ret_sts_error;
484 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_START_PERIOD'); -- resusing CAMP message
485 RETURN;
486 ELSIF p_start_date < l_start_start OR p_start_date > l_start_end THEN
487 x_return_status := FND_API.g_ret_sts_error;
488 AMS_Utility_PVT.error_message('AMS_EVH_OUT_START_PERIOD');
489 RETURN;
490 END IF;
491 END IF;
492
493 -- check p_end_period_name
494 IF p_end_period_name IS NOT NULL THEN
495 OPEN c_end_period;
496 FETCH c_end_period INTO l_end_start, l_end_end;
497 CLOSE c_end_period;
498
499 IF l_end_end IS NULL THEN
500 x_return_status := FND_API.g_ret_sts_error;
501 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_END_PERIOD'); --resuing CAMP message
502 RETURN;
503 ELSIF p_end_date < l_end_start OR p_end_date > l_end_end THEN
504 x_return_status := FND_API.g_ret_sts_error;
505 AMS_Utility_PVT.error_message('AMS_EVH_OUT_END_PERIOD');
506 RETURN;
507 END IF;
508 END IF;
509
510 -- compare the start date and the end date
511 IF l_start_start > l_end_end THEN
512 x_return_status := FND_API.g_ret_sts_error;
513 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_PERIODS');--resuing CAMP message
514 END IF;
515
516 END check_evh_calendar;
517
518 ---------------------------------------------------------------------
519 -- PROCEDURE
520 -- push_source_code
521 --
522 -- HISTORY
523 -- 11/01/99 holiu Created.
524 ---------------------------------------------------------------------
525 /*
526 PROCEDURE push_source_code(
527 p_source_code IN VARCHAR2,
528 p_arc_object IN VARCHAR2,
529 p_object_id IN NUMBER
530 )
531 IS
532 l_pk NUMBER;
533 CURSOR c_seq IS
534 SELECT ams_source_codes_s.NEXTVAL
535 FROM DUAL;
536 BEGIN
537 OPEN c_seq;
538 FETCH c_seq INTO l_pk;
539 CLOSE c_seq;
540 INSERT INTO ams_source_codes(
541 source_code_id,
542 last_update_date,
543 last_updated_by,
544 creation_date,
545 created_by,
546 last_update_login,
547 object_version_number,
548 source_code,
549 source_code_for_id,
550 arc_source_code_for
551 )
552 VALUES(
553 l_pk,
554 SYSDATE,
555 FND_GLOBAL.user_id,
556 SYSDATE,
557 FND_GLOBAL.user_id,
558 FND_GLOBAL.conc_login_id,
559 1, -- object_version_number
560 p_source_code,
561 p_object_id,
562 p_arc_object
563 );
564 END push_source_code;
565 */
566 -----------------------------------------------------------------------
567 -- PROCEDURE
568 -- udpate_evh_source_code
569 --
570 -- HISTORY
571 -- 09/31/00 sugupta Created.
572 -----------------------------------------------------------------------
573 PROCEDURE update_evh_source_code(
574 p_evh_id IN NUMBER,
575 p_source_code IN VARCHAR2,
576 p_global_flag IN VARCHAR2,
577 x_source_code OUT NOCOPY VARCHAR2,
578 x_return_status OUT NOCOPY VARCHAR2
579 )
580 IS
581
582 l_msg_data VARCHAR2(2000);
583 l_msg_count NUMBER;
584
585 l_source_code VARCHAR2(30);
586 l_global_flag VARCHAR2(1);
587 l_custom_setup_id NUMBER;
588 l_source_code_id NUMBER;
589
590 CURSOR c_old_info IS
591 SELECT global_flag, source_code
592 FROM ams_event_headers_vl
593 WHERE event_header_id = p_evh_id;
594
595 CURSOR c_source_code IS
596 SELECT source_code_id
597 FROM ams_source_codes
598 WHERE source_code = x_source_code
599 AND active_flag = 'Y';
600
601 CURSOR c_setup_id IS
602 SELECT SETUP_TYPE_ID
603 FROM AMS_EVENT_HEADERS_ALL_B
604 WHERE EVENT_HEADER_ID = p_evh_id;
605 /*
606 CURSOR c_setup_id IS
607 SELECT custom_setup_id
608 FROM ams_object_attributes
609 WHERE object_type = 'EVEH'
610 AND object_id = p_evh_id;
611 */
612 BEGIN
613
614 x_source_code := p_source_code;
615 x_return_status := FND_API.g_ret_sts_success;
616
617 OPEN c_old_info;
618 FETCH c_old_info INTO l_global_flag, l_source_code;
619 CLOSE c_old_info;
620
621 OPEN c_setup_id;
622 FETCH c_setup_id INTO l_custom_setup_id;
623 CLOSE c_setup_id;
624 -- generate a new source code if global flag is updated and
625 -- source code is not cascaded to schedules
626 IF p_global_flag <> l_global_flag THEN
627 x_source_code := AMS_SourceCode_PVT.get_new_source_code(
628 p_object_type => 'EVEH',
629 p_custsetup_id => l_custom_setup_id,
630 p_global_flag => p_global_flag
631 );
632 END IF;
633
634 IF x_source_code = l_source_code THEN
635 RETURN;
636 END IF;
637
638 IF x_source_code IS NULL THEN
639 AMS_Utility_PVT.error_message('AMS_EVO_NO_SOURCE_CODE');
640 x_return_status := FND_API.g_ret_sts_error;
641 RETURN;
642 END IF;
643
644 -- check if the new source code is unique
645 OPEN c_source_code;
646 FETCH c_source_code INTO l_source_code_id;
647 CLOSE c_source_code;
648
649 IF l_source_code_id IS NOT NULL THEN
650 AMS_Utility_PVT.error_message('AMS_CAMP_DUPLICATE_CODE'); --reuse message
651 x_return_status := FND_API.g_ret_sts_error;
652 RETURN;
653 END IF;
654
655 -- otherwise revoke the old one and add the new one to ams_source_codes
656 AMS_SourceCode_PVT.revoke_sourcecode(
657 p_api_version => 1.0,
658 p_init_msg_list => FND_API.g_false,
659 p_commit => FND_API.g_false,
660 p_validation_level => FND_API.g_valid_level_full,
661
662 x_return_status => x_return_status,
663 x_msg_count => l_msg_count,
664 x_msg_data => l_msg_data,
665
666 p_sourcecode => l_source_code
667 );
668
669 IF x_return_status <> FND_API.g_ret_sts_success THEN
670 RAISE FND_API.g_exc_error;
671 END IF;
672
673 AMS_SourceCode_PVT.create_sourcecode(
674 p_api_version => 1.0,
675 p_init_msg_list => FND_API.g_false,
676 p_commit => FND_API.g_false,
677 p_validation_level => FND_API.g_valid_level_full,
678 x_return_status => x_return_status,
679 x_msg_count => l_msg_count,
680 x_msg_data => l_msg_data,
681
682 p_sourcecode => x_source_code,
683 p_sourcecode_for => 'EVEH',
684 p_sourcecode_for_id => p_evh_id,
685 x_sourcecode_id => l_source_code_id
686 );
687 IF x_return_status <> FND_API.g_ret_sts_success THEN
688 RAISE FND_API.g_exc_error;
689 END IF;
690
691 END update_evh_source_code;
692
693 -----------------------------------------------------------------------
694 -- PROCEDURE
695 -- udpate_evo_source_code
696 --
697 -- HISTORY
698 -- 09/31/00 sugupta Created.
699 -----------------------------------------------------------------------
700 PROCEDURE update_evo_source_code(
701 p_evo_id IN NUMBER,
702 p_source_code IN VARCHAR2,
703 p_global_flag IN VARCHAR2,
704 x_source_code OUT NOCOPY VARCHAR2,
705 x_return_status OUT NOCOPY VARCHAR2
706 )
707 IS
708
709 l_msg_data VARCHAR2(2000);
710 l_msg_count NUMBER;
711
712 l_source_code VARCHAR2(30);
713 l_global_flag VARCHAR2(1);
714 l_custom_setup_id NUMBER;
715 l_source_code_id NUMBER;
716
717 CURSOR c_old_info IS
718 SELECT global_flag, source_code
719 FROM ams_event_offers_vl
720 WHERE event_offer_id = p_evo_id;
721
722 CURSOR c_source_code IS
723 SELECT source_code_id
724 FROM ams_source_codes
725 WHERE source_code = x_source_code
726 AND active_flag = 'Y';
727
728 CURSOR c_setup_id IS
729 SELECT SETUP_TYPE_ID
730 FROM AMS_EVENT_OFFERS_ALL_B
731 WHERE EVENT_HEADER_ID = p_evo_id;
732 /*
733 CURSOR c_setup_id IS
734 SELECT custom_setup_id
735 FROM ams_object_attributes
736 WHERE object_type = 'EVEO'
737 AND object_id = p_evo_id;
738 */
739 BEGIN
740
741 x_source_code := p_source_code;
742 x_return_status := FND_API.g_ret_sts_success;
743
744 OPEN c_old_info;
745 FETCH c_old_info INTO l_global_flag, l_source_code;
746 CLOSE c_old_info;
747
748 OPEN c_setup_id;
749 FETCH c_setup_id INTO l_custom_setup_id;
750 CLOSE c_setup_id;
751
752 -- generate a new source code if global flag is updated and
753 -- source code is not cascaded to schedules
754 IF p_global_flag <> l_global_flag THEN
755 x_source_code := AMS_SourceCode_PVT.get_new_source_code(
756 p_object_type => 'EVEO',
757 p_custsetup_id => l_custom_setup_id,
758 p_global_flag => p_global_flag
759 );
760 END IF;
761
762 IF x_source_code = l_source_code THEN
763 RETURN;
764 END IF;
765
766 IF x_source_code IS NULL THEN
767 AMS_Utility_PVT.error_message('AMS_EVO_NO_SOURCE_CODE');
768 x_return_status := FND_API.g_ret_sts_error;
769 RETURN;
770 END IF;
771
772 -- check if the new source code is unique
773 OPEN c_source_code;
774 FETCH c_source_code INTO l_source_code_id;
775 CLOSE c_source_code;
776
777 IF l_source_code_id IS NOT NULL THEN
778 AMS_Utility_PVT.error_message('AMS_CAMP_DUPLICATE_CODE'); --reuse message
779 x_return_status := FND_API.g_ret_sts_error;
780 RETURN;
781 END IF;
782
783 -- otherwise revoke the old one and add the new one to ams_source_codes
784 AMS_SourceCode_PVT.revoke_sourcecode(
785 p_api_version => 1.0,
786 p_init_msg_list => FND_API.g_false,
787 p_commit => FND_API.g_false,
788 p_validation_level => FND_API.g_valid_level_full,
789
790 x_return_status => x_return_status,
791 x_msg_count => l_msg_count,
792 x_msg_data => l_msg_data,
793
794 p_sourcecode => l_source_code
795 );
796
797 IF x_return_status <> FND_API.g_ret_sts_success THEN
798 RAISE FND_API.g_exc_error;
799 END IF;
800
801 AMS_SourceCode_PVT.create_sourcecode(
802 p_api_version => 1.0,
803 p_init_msg_list => FND_API.g_false,
804 p_commit => FND_API.g_false,
805 p_validation_level => FND_API.g_valid_level_full,
806
807 x_return_status => x_return_status,
808 x_msg_count => l_msg_count,
809 x_msg_data => l_msg_data,
810
811 p_sourcecode => x_source_code,
812 p_sourcecode_for => 'EVEO',
813 p_sourcecode_for_id => p_evo_id,
814 x_sourcecode_id => l_source_code_id
815 );
816
817 IF x_return_status <> FND_API.g_ret_sts_success THEN
818 RAISE FND_API.g_exc_error;
819 END IF;
820
821 END update_evo_source_code;
822
823 -----------------------------------------------------------------------
824 -- PROCEDURE
825 -- udpate_eone_source_code
826 --
827 -- HISTORY
828 -- 09/31/00 sugupta Created.
829 -----------------------------------------------------------------------
830 PROCEDURE update_eone_source_code(
831 p_evo_id IN NUMBER,
832 p_source_code IN VARCHAR2,
833 p_global_flag IN VARCHAR2,
834 x_source_code OUT NOCOPY VARCHAR2,
835 x_return_status OUT NOCOPY VARCHAR2
836 )
837 IS
838
839 l_msg_data VARCHAR2(2000);
840 l_msg_count NUMBER;
841
842 l_source_code VARCHAR2(30);
843 l_global_flag VARCHAR2(1);
844 l_custom_setup_id NUMBER;
845 l_source_code_id NUMBER;
846
847 CURSOR c_old_info IS
848 SELECT global_flag, source_code
849 FROM ams_event_offers_vl
850 WHERE event_offer_id = p_evo_id;
851
852 CURSOR c_source_code IS
853 SELECT source_code_id
854 FROM ams_source_codes
855 WHERE source_code = x_source_code
856 AND active_flag = 'Y';
857
858 CURSOR c_setup_id IS
859 SELECT SETUP_TYPE_ID
860 FROM AMS_EVENT_OFFERS_ALL_B
861 WHERE EVENT_HEADER_ID = p_evo_id;
862 /*
863 CURSOR c_setup_id IS
864 SELECT custom_setup_id
865 FROM ams_object_attributes
866 WHERE object_type = 'EONE'
867 AND object_id = p_evo_id;
868 */
869 BEGIN
870
871 x_source_code := p_source_code;
872 x_return_status := FND_API.g_ret_sts_success;
873
874 OPEN c_old_info;
875 FETCH c_old_info INTO l_global_flag, l_source_code;
876 CLOSE c_old_info;
877
878 OPEN c_setup_id;
879 FETCH c_setup_id INTO l_custom_setup_id;
880 CLOSE c_setup_id;
881
882 -- generate a new source code if global flag is updated and
883 -- source code is not cascaded to schedules
884 IF p_global_flag <> l_global_flag THEN
885 x_source_code := AMS_SourceCode_PVT.get_new_source_code(
886 p_object_type => 'EONE',
887 p_custsetup_id => l_custom_setup_id,
888 p_global_flag => p_global_flag
889 );
890 END IF;
891
892 IF x_source_code = l_source_code THEN
893 RETURN;
894 END IF;
895
896 IF x_source_code IS NULL THEN
897 AMS_Utility_PVT.error_message('AMS_EVO_NO_SOURCE_CODE');
898 x_return_status := FND_API.g_ret_sts_error;
899 RETURN;
900 END IF;
901
902 -- check if the new source code is unique
903 OPEN c_source_code;
904 FETCH c_source_code INTO l_source_code_id;
905 CLOSE c_source_code;
906
907 IF l_source_code_id IS NOT NULL THEN
908 AMS_Utility_PVT.error_message('AMS_CAMP_DUPLICATE_CODE'); --reuse message
909 x_return_status := FND_API.g_ret_sts_error;
910 RETURN;
911 END IF;
912
913 -- otherwise revoke the old one and add the new one to ams_source_codes
914 AMS_SourceCode_PVT.revoke_sourcecode(
915 p_api_version => 1.0,
916 p_init_msg_list => FND_API.g_false,
917 p_commit => FND_API.g_false,
918 p_validation_level => FND_API.g_valid_level_full,
919
920 x_return_status => x_return_status,
921 x_msg_count => l_msg_count,
922 x_msg_data => l_msg_data,
923
924 p_sourcecode => l_source_code
925 );
926
927 IF x_return_status <> FND_API.g_ret_sts_success THEN
928 RAISE FND_API.g_exc_error;
929 END IF;
930
931 AMS_SourceCode_PVT.create_sourcecode(
932 p_api_version => 1.0,
933 p_init_msg_list => FND_API.g_false,
934 p_commit => FND_API.g_false,
935 p_validation_level => FND_API.g_valid_level_full,
936
937 x_return_status => x_return_status,
938 x_msg_count => l_msg_count,
939 x_msg_data => l_msg_data,
940
941 p_sourcecode => x_source_code,
942 p_sourcecode_for => 'EONE',
943 p_sourcecode_for_id => p_evo_id,
944 x_sourcecode_id => l_source_code_id
945 );
946
947 IF x_return_status <> FND_API.g_ret_sts_success THEN
948 RAISE FND_API.g_exc_error;
949 END IF;
950
951 END update_eone_source_code;
952
953
954
955 ----------------------------------------------------------------------------------------------------------------
956 -- PROCEDURE
957 -- udpate_event_status
958 --
959 -- HISTORY
960 -- 09/25/00 mukumar Created.
961 -- 25-Oct-2002 soagrawa Added code for automatic budget line approval enh# 2445453
962 -- 17-Mar-2005 spendem code inclusions for raising a business event on UserStatus change. dummy enh # 3805347
963 -------------------------------------------------------------------------------------------------------------------
964 PROCEDURE update_event_status(
965 p_event_id IN NUMBER,
966 p_event_activity_type IN VARCHAR2,
967 p_user_status_id IN NUMBER,
968 p_fund_amount_tc IN NUMBER,
969 p_currency_code_tc IN VARCHAR2
970 )
971 IS
972
973 l_old_status_id NUMBER;
974 l_old_status_id1 NUMBER;
975 l_new_status_id NUMBER;
976 l_deny_status_id NUMBER;
977 l_object_version NUMBER;
978 l_object_version1 NUMBER;
979 l_approval_type VARCHAR2(30);
980 l_return_status VARCHAR2(1);
981 l_bgtsrc_exist NUMBER;
982 l_custom_setup_id NUMBER;
983 l_custom_setup_id1 NUMBER;
984 l_event_header_id NUMBER;
985 l_event_offer_id NUMBER;
986 l_program_id NUMBER;
987 l_parent_id NUMBER;
988 l_parent_system_status_code VARCHAR2(30);
989 l_system_status_code VARCHAR2(30);
990 l_msg_count NUMBER ;
991 l_msg_data VARCHAR2(2000);
992 l_old_status_code VARCHAR2(30); -- added as per enh # 3805347
993 l_new_status_code VARCHAR2(30); -- added as per enh # 3805347
994
995 CURSOR c_old_status_EVEH (l_event_header_id IN NUMBER) IS
996 SELECT user_status_id, object_version_number, setup_type_id, program_id
997 FROM ams_event_headers_all_b
998 WHERE event_header_id = l_event_header_id;
999
1000 -- Modified the cursor as per enh # 3805347
1001 CURSOR c_old_status_EVEO IS
1002 SELECT user_status_id, object_version_number, setup_type_id, event_header_id, system_status_code
1003 FROM ams_event_offers_all_b
1004 WHERE event_offer_id = p_event_id;
1005
1006 -- Modified the cursor as per enh # 3805347
1007 CURSOR c_old_status_EONE IS
1008 SELECT user_status_id, object_version_number, setup_type_id, parent_id, system_status_code
1009 FROM ams_event_offers_all_b
1010 WHERE event_offer_id = p_event_id;
1011
1012 /* Cursor to get the user status id of program */
1013 CURSOR c_PROGRAM_status (l_event_offer_id IN NUMBER) IS
1014 SELECT user_status_id
1015 FROM ams_campaigns_all_b
1016 WHERE campaign_id = l_event_offer_id;
1017
1018
1019
1020 CURSOR c_bgtsrc_exist IS
1021 SELECT 1
1022 FROM DUAL
1023 WHERE EXISTS(
1024 SELECT 1
1025 FROM ozf_act_budgets --anchaudh: changed call from ams_act_budgets to ozf_act_budgets : bug#3453430
1026 WHERE act_budget_used_by_id = p_event_id
1027 AND arc_act_budget_used_by = p_event_activity_type);
1028
1029 BEGIN
1030
1031 IF p_event_activity_type = 'EVEH' THEN
1032
1033 OPEN c_old_status_EVEH(p_event_id);
1034 FETCH c_old_status_EVEH INTO l_old_status_id, l_object_version, l_custom_setup_id, l_program_id;
1035 CLOSE c_old_status_EVEH;
1036
1037 IF l_program_id IS NOT NULL then
1038
1039 OPEN c_PROGRAM_status(l_program_id);
1040 FETCH c_PROGRAM_status INTO l_old_status_id1;
1041 CLOSE c_PROGRAM_status;
1042
1043 END IF;
1044
1045 l_system_status_code := AMS_Utility_PVT.get_system_status_code(p_user_status_id);
1046 l_parent_system_status_code := AMS_Utility_PVT.get_system_status_code(l_old_status_id1);
1047
1048 If l_system_status_code = 'ACTIVE' and l_parent_system_status_code <> 'ACTIVE'THEN
1049 FND_MESSAGE.set_name('AMS', 'AMS_PROGRAM_NOT_ACTIVE');
1050 FND_MSG_PUB.add;
1051 RAISE FND_API.g_exc_error;
1052 END IF;
1053
1054 ELSIF p_event_activity_type ='EVEO' THEN
1055
1056 -- Modified the cursor as per enh # 3805347
1057 OPEN c_old_status_EVEO;
1058 FETCH c_old_status_EVEO INTO l_old_status_id, l_object_version, l_custom_setup_id, l_event_header_id, l_old_status_code;
1059 CLOSE c_old_status_EVEO;
1060
1061 IF l_event_header_id IS NOT NULL then
1062
1063 OPEN c_old_status_EVEH(l_event_header_id);
1064 FETCH c_old_status_EVEH INTO l_old_status_id1, l_object_version1, l_custom_setup_id1, l_program_id;
1065 CLOSE c_old_status_EVEH;
1066
1067 END IF;
1068
1069 l_system_status_code := AMS_Utility_PVT.get_system_status_code(p_user_status_id);
1070 l_parent_system_status_code := AMS_Utility_PVT.get_system_status_code(l_old_status_id1);
1071
1072 If l_system_status_code = 'ACTIVE' and l_parent_system_status_code <> 'ACTIVE'THEN
1073 -- changed 'AMS_EVENT_NOT ACTIVE' to 'AMS_EVENT_NOT_ACTIVE'
1074 FND_MESSAGE.set_name('AMS', 'AMS_EVENT_NOT_ACTIVE');
1075 FND_MSG_PUB.add;
1076 RAISE FND_API.g_exc_error;
1077 END IF;
1078
1079 ELSIF p_event_activity_type ='EONE' THEN
1080
1081 -- Modified the cursor as per enh # 3805347
1082 OPEN c_old_status_EONE;
1083 FETCH c_old_status_EONE INTO l_old_status_id, l_object_version, l_custom_setup_id, l_parent_id, l_old_status_code;
1084 CLOSE c_old_status_EONE;
1085
1086 IF l_parent_id IS NOT NULL then
1087
1088 OPEN c_PROGRAM_status(l_parent_id);
1089 FETCH c_PROGRAM_status INTO l_old_status_id1;
1090 CLOSE c_PROGRAM_status;
1091
1092 END IF;
1093
1094 l_system_status_code := AMS_Utility_PVT.get_system_status_code(p_user_status_id);
1095 l_parent_system_status_code := AMS_Utility_PVT.get_system_status_code(l_old_status_id1);
1096
1097 If l_system_status_code = 'ACTIVE' and l_parent_system_status_code <> 'ACTIVE'THEN
1098 FND_MESSAGE.set_name('AMS', 'AMS_PROGRAM_NOT_ACTIVE');
1099 FND_MSG_PUB.add;
1100 RAISE FND_API.g_exc_error;
1101 END IF;
1102
1103 END IF;
1104
1105 /* If there is no chnage in user status, just return i.e. if the status is new, and
1106 user again selects new as user status .
1107 */
1108
1109 IF l_old_status_id = p_user_status_id THEN
1110 RETURN;
1111 END IF;
1112
1113 -- Call the procedure which will make the new status as CLOSED if it is COMPLETED.
1114
1115 -- Call the procedure which does lead import.
1116
1117 -- The following procedure checks whether the status change is allowed from the
1118 -- approval perspective.
1119 -- So the code which does the Lead Import has to be called before this line.
1120
1121 AMS_Utility_PVT.check_new_status_change(
1122 p_object_type => p_event_activity_type,
1123 p_object_id => p_event_id,
1124 p_old_status_id => l_old_status_id,
1125 p_new_status_id => p_user_status_id,
1126 p_custom_setup_id => l_custom_setup_id,
1127 x_approval_type => l_approval_type,
1128 x_return_status => l_return_status
1129 );
1130
1131 IF l_return_status <> FND_API.g_ret_sts_success THEN
1132 RAISE FND_API.g_exc_error;
1133 END IF;
1134
1135 IF l_approval_type = 'BUDGET' THEN
1136 /* commented on oct 30 to support zero budget approval
1137 -- Check if budget source has been assigned to the event.
1138 OPEN c_bgtsrc_exist;
1139 FETCH c_bgtsrc_exist INTO l_bgtsrc_exist;
1140 CLOSE c_bgtsrc_exist;
1141 IF l_bgtsrc_exist IS NOT NULL THEN
1142
1143 -- Also check if budget amount has been specified.
1144 IF p_fund_amount_tc IS NOT NULL
1145 AND p_currency_code_tc IS NOT NULL
1146 AND p_fund_amount_tc <> FND_API.g_miss_num
1147 AND p_currency_code_tc <> FND_API.g_miss_char
1148 THEN
1149 */
1150 l_new_status_id := AMS_Utility_PVT.get_default_user_status(
1151 'AMS_EVENT_STATUS',
1152 'SUBMITTED_BA'
1153 );
1154 l_deny_status_id := AMS_Utility_PVT.get_default_user_status(
1155 'AMS_EVENT_STATUS',
1156 'DENIED_BA'
1157 );
1158 AMS_Approval_PVT.StartProcess(
1159 p_activity_type => p_event_activity_type,
1160 p_activity_id => p_event_id,
1161 p_approval_type => l_approval_type,
1162 p_object_version_number => l_object_version,
1163 p_orig_stat_id => l_old_status_id,
1164 p_new_stat_id => p_user_status_id,
1165 p_reject_stat_id => l_deny_status_id,
1166 p_requester_userid =>
1167 AMS_Utility_PVT.get_resource_id(FND_GLOBAL.user_id),
1168 p_workflowprocess => 'AMS_APPROVAL',
1169 p_item_type => 'AMSAPRV'
1170 );
1171 /* ELSE
1172 AMS_Utility_PVT.error_message('AMS_EVE_NO_BGT_AMT');
1173 -- Please Specify the Budget Amount Before seeking Approval! Error.
1174 RAISE FND_API.g_exc_error;
1175 END IF;
1176 ELSE
1177 AMS_Utility_PVT.error_message('AMS_EVE_NO_BGT_SRC');
1178 -- Please Specify Budget Source Before seeking Approval! Error.
1179 RAISE FND_API.g_exc_error;
1180 END IF;
1181 */
1182 ELSIF l_approval_type = 'THEME' THEN
1183 l_new_status_id := AMS_Utility_PVT.get_default_user_status(
1184 'AMS_EVENT_STATUS',
1185 'SUBMITTED_TA'
1186 );
1187 l_deny_status_id := AMS_Utility_PVT.get_default_user_status(
1188 'AMS_EVENT_STATUS',
1189 'DENIED_TA'
1190 );
1191 AMS_Approval_PVT.StartProcess(
1192 p_activity_type => p_event_activity_type,
1193 p_activity_id => p_event_id,
1194 p_approval_type => 'CONCEPT',
1195 p_object_version_number => l_object_version,
1196 p_orig_stat_id => l_old_status_id,
1197 p_new_stat_id => p_user_status_id,
1198 p_reject_stat_id => l_deny_status_id,
1199 p_requester_userid =>
1200 AMS_Utility_PVT.get_resource_id(FND_GLOBAL.user_id),
1201 p_workflowprocess => 'AMS_CONCEPT_APPROVAL',
1202 p_item_type => 'AMSAPRV'
1203 );
1204 ELSE
1205
1206 -- Following budget line api call added by soagrawa on 25-oct-2002
1207 -- for enhancement # 2445453
1208 If l_system_status_code = 'ACTIVE'
1209 THEN
1210 --anchaudh: changed call from ams_budgetapproval_pvt to ozf_budget_approval_pvt: bug#3453430
1211 OZF_BudgetApproval_PVT.budget_request_approval(
1212 p_init_msg_list => FND_API.G_FALSE
1213 , p_api_version => 1.0
1214 , p_commit => FND_API.G_False
1215 , x_return_status => l_return_status
1216 , x_msg_count => l_msg_count
1217 , x_msg_data => l_msg_data
1218 , p_object_type => p_event_activity_type
1219 , p_object_id => p_event_id
1220 -- , x_status_code =>
1221 );
1222
1223 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1224 RAISE FND_API.G_EXC_ERROR;
1225 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1226 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1227 END IF;
1228 END IF;
1229 l_new_status_id := p_user_status_id;
1230 END IF;
1231
1232 -- Update header or offers table with the new status
1233
1234 IF p_event_activity_type = 'EVEH' THEN
1235 /* UPDATE ams_event_headers_all_b
1236 SET user_status_id = l_new_status_id,
1237 system_status_code = AMS_Utility_PVT.get_system_status_code(l_new_status_id),
1238 last_status_date = SYSDATE
1239 WHERE event_header_id = p_event_id; */
1240
1241 AMS_EvhRules_PVT.Update_Event_Header_Status(p_event_header_id => p_event_id,
1242 p_new_status_id => l_new_status_id,
1243 p_new_status_code => AMS_Utility_PVT.get_system_status_code(l_new_status_id)
1244 );
1245
1246 ELSIF p_event_activity_type ='EVEO' THEN
1247 UPDATE ams_event_offers_all_b
1248 SET user_status_id = l_new_status_id,
1249 system_status_code = AMS_Utility_PVT.get_system_status_code(l_new_status_id),
1250 last_status_date = SYSDATE
1251 WHERE event_offer_id = p_event_id;
1252
1253 l_new_status_code := AMS_Utility_PVT.get_system_status_code(l_new_status_id); -- added as per enh # 3805347
1254
1255 -- call to api to raise business event, as per enh # 3805347
1256 AMS_SCHEDULERULES_PVT.RAISE_BE_ON_STATUS_CHANGE(p_obj_id => p_event_id,
1257 p_obj_type => p_event_activity_type,
1258 p_old_status_code => l_old_status_code,
1259 p_new_status_code => l_new_status_code );
1260
1261
1262
1263 ELSIF p_event_activity_type ='EONE' THEN
1264 UPDATE ams_event_offers_all_b
1265 SET user_status_id = l_new_status_id,
1266 system_status_code = AMS_Utility_PVT.get_system_status_code(l_new_status_id),
1267 last_status_date = SYSDATE
1268 WHERE event_offer_id = p_event_id;
1269
1270 l_new_status_code := AMS_Utility_PVT.get_system_status_code(l_new_status_id); -- added as per enh # 3805347
1271
1272 -- call to api to raise business event, as per enh # 3805347
1273 AMS_SCHEDULERULES_PVT.RAISE_BE_ON_STATUS_CHANGE(p_obj_id => p_event_id,
1274 p_obj_type => p_event_activity_type,
1275 p_old_status_code => l_old_status_code,
1276 p_new_status_code => l_new_status_code );
1277
1278
1279 END IF;
1280 -- GDEODHAR : Added the following code :
1281 -- Call the procedure which does lead import if necessary.
1282 -- Lead Import will be done only if the new status is CLOSED.
1283 -- Assumption : In Events area : if the status is changed to COMPLETED,
1284 -- it will be automatically changed to CLOSED before
1285 -- this point.
1286
1287 --IF l_new_status_id = 7 THEN -- The following is better than hardcoding 7 here
1288
1289 -- 05-jan-2004 soagrawa Fixed bug# 3100394 related to completing an event header
1290 IF AMS_Utility_PVT.get_system_status_code(l_new_status_id) = 'COMPLETED'
1291 THEN
1292 IF p_event_activity_type = 'EVEH'
1293 THEN
1294 Complete_Event_Offers(p_eveh_id => p_event_id);
1295 ELSIF ((p_event_activity_type = 'EONE') OR (p_event_activity_type = 'EVEO'))
1296 THEN
1297 -- Call the lead import procedure.
1298 process_leads(p_event_id => p_event_id);
1299 END IF;
1300 END IF;
1301
1302 END update_event_status;
1303
1304
1305 --=======================================================================
1306 -- PROCEDURE
1307 -- process_leads
1308 --
1309 -- NOTES
1310 -- This procedure is created to create leads in OTS based on the event
1311 -- registrations and attendance.
1312 -- When the status of the event schedule is changed to CLOSED, this
1313 -- procedure will be called.
1314 -- This method should be pulled out from here and be created as a concurrent
1315 -- program as this operation is more suited for batch operations.
1316 --
1317 -- HISTORY
1318 -- 08/02/2001 gdeodhar Created.
1319 -- 22-oct-2002 soagrawa Modified API signature to take obj type and obj srccd
1320 -- to be able to generate leads against non-event src cd.
1321 -- 05-feb-2003 soagrawa Bug# 2787303: consolidated 2 separate cursors into 1 cursor
1322 --=======================================================================
1323
1324 PROCEDURE process_leads(
1325 p_event_id IN NUMBER
1326 , p_obj_type IN VARCHAR2 := NULL
1327 , p_obj_srccd IN VARCHAR2 := NULL
1328 )
1329 IS
1330 l_lit_batch_id NUMBER; --Batch ID of this Lead Import process for the lead interface tabel.
1331 l_reg_lead_flag VARCHAR2(1); --Value of CREATE_REGISTRANT_LEAD_FLAG from ams_event_offers_all_b.
1332 l_atnd_lead_flag VARCHAR2(1); --Value of CREATE_ATTENDANT_LEAD_FLAG from ams_event_offers_all_b.
1333 l_evnt_sched_src_cd VARCHAR2(30); --Value of the source code for event schedule.
1334 l_loaded_rows NUMBER; --holds the count of rows that were inserted in the import interface table for this batch.
1335 l_request_id NUMBER; --request id. Used when we call the concurrent program.
1336 l_return_status VARCHAR2(1); --holds the status coming back from other procedure calls.
1337
1338 CURSOR c_batch_id IS --Cursor to pick up the Batch ID.
1339 SELECT as_sl_imp_batch_s.NEXTVAL
1340 FROM DUAL;
1341
1342 CURSOR c_get_evnt_sched_src_cd(event_id_in IN NUMBER) IS
1343 SELECT source_code
1344 FROM ams_event_offers_all_b
1345 WHERE event_offer_id = event_id_in;
1346
1347 CURSOR c_get_reg_lead_flag(event_id_in IN NUMBER) IS
1348 SELECT create_registrant_lead_flag
1349 FROM ams_event_offers_all_b
1350 WHERE event_offer_id = event_id_in;
1351
1352 CURSOR c_get_atnd_lead_flag(event_id_in IN NUMBER) IS
1353 SELECT create_attendant_lead_flag
1354 FROM ams_event_offers_all_b
1355 WHERE event_offer_id = event_id_in;
1356
1357 -- following cursor added by soagrawa on 22-oct-2002
1358 -- to merge the above 3 cursors into 1 single cursor
1359 -- while fixing bug# 2594717
1360 CURSOR c_get_evnt_details(event_id_in IN NUMBER) IS
1361 SELECT source_code, create_registrant_lead_flag, create_attendant_lead_flag
1362 FROM ams_event_offers_all_b
1363 WHERE event_offer_id = event_id_in;
1364
1365
1366 -- The following is actually : create leads for enrollees.
1367 CURSOR c_get_registrant_for_lead (event_id_in IN NUMBER) IS
1368 SELECT registrant_party_id
1369 , registrant_contact_id
1370 , attendant_party_id
1371 , attendant_contact_id
1372 FROM ams_event_registrations
1373 WHERE event_offer_id = event_id_in
1374 AND system_status_code = 'REGISTERED' -- In future when we kill the REGISTERED status and leave ENROLLED, change this to ENROLLED.
1375 AND active_flag = 'Y';
1376
1377 l_registrant_for_lead_rec c_get_registrant_for_lead%ROWTYPE;
1378
1379 CURSOR c_get_attendant_for_lead (event_id_in IN NUMBER) IS
1380 SELECT registrant_party_id
1381 , registrant_contact_id
1382 , attendant_party_id
1383 , attendant_contact_id
1384 FROM ams_event_registrations
1385 WHERE event_offer_id = event_id_in
1386 AND system_status_code = 'REGISTERED' -- In future when we kill the REGISTERED status and leave ENROLLED, change this to ENROLLED.
1387 AND attended_flag = 'Y'
1388 AND active_flag = 'Y';
1389
1390 l_attendant_for_lead_rec c_get_attendant_for_lead%ROWTYPE;
1391
1392 CURSOR c_loaded_rows_for_lead (batch_id_in IN NUMBER) IS
1393 SELECT COUNT(*)
1394 FROM as_import_interface
1395 WHERE batch_id = batch_id_in;
1396
1397 -- dbiswas 23-apr-2003 added cursor for NI bug# 2610067 carryforward to next releases
1398 CURSOR c_get_person_id (rel_party_id_in IN NUMBER) IS
1399 SELECT subject_id -- person id
1400 FROM hz_relationships -- anchaudh: bug fix 3764927.
1401 WHERE party_id = rel_party_id_in
1402 AND directional_flag = 'F';
1403
1404 l_reg_att_party_id NUMBER;
1405
1406 -- soagrawa 05-feb-2003 bug# 2787303
1407 CURSOR c_get_reg_att_for_lead (event_id_in IN NUMBER) IS
1408 SELECT reg.registrant_party_id party_id
1409 , reg.registrant_contact_id contact_id
1410 FROM ams_event_registrations reg,
1411 ams_event_offers_all_b event
1412 WHERE reg.event_offer_id = event_id_in
1413 AND reg.system_status_code = 'REGISTERED' -- In future when we kill the REGISTERED status and leave ENROLLED, change this to ENROLLED.
1414 AND reg.active_flag = 'Y'
1415 AND event.event_offer_id = reg.event_offer_id
1416 AND event.create_registrant_lead_flag = 'Y'
1417 UNION
1418 SELECT reg.attendant_party_id party_id
1419 , reg.attendant_contact_id contact_id
1420 FROM ams_event_registrations reg,
1421 ams_event_offers_all_b event
1422 WHERE reg.event_offer_id = event_id_in
1423 AND reg.system_status_code = 'REGISTERED' -- In future when we kill the REGISTERED status and leave ENROLLED, change this to ENROLLED.
1424 AND reg.active_flag = 'Y'
1425 AND reg.attended_flag = 'Y'
1426 AND event.event_offer_id = reg.event_offer_id
1427 AND event.create_attendant_lead_flag = 'Y';
1428
1429 l_reg_att_for_lead_rec c_get_reg_att_for_lead%ROWTYPE;
1430
1431 BEGIN
1432 -- Pick up the Batch ID from the sequence.
1433 -- This will be recorded in the Lead Import Interface table for each lead we create.
1434
1435 OPEN c_batch_id;
1436 FETCH c_batch_id INTO l_lit_batch_id;
1437 CLOSE c_batch_id;
1438
1439 -- Call to the following 3 separate cursors compiled into one single cursor
1440 -- by soagrawa on 22-oct-2002
1441 -- while fixing bug# 2594717
1442 /*
1443 -- Pick up the value of SOURCE_CODE for the event shchedule.
1444
1445 OPEN c_get_evnt_sched_src_cd(p_event_id);
1446 FETCH c_get_evnt_sched_src_cd INTO l_evnt_sched_src_cd;
1447 CLOSE c_get_evnt_sched_src_cd;
1448
1449 -- Pick up the value of CREATE_REGISTRANT_LEAD_FLAG for the event shchedule.
1450 -- This is actually for CREATE_ENROLLEE_AS_LEAD.
1451
1452 OPEN c_get_reg_lead_flag(p_event_id);
1453 FETCH c_get_reg_lead_flag INTO l_reg_lead_flag;
1454 CLOSE c_get_reg_lead_flag;
1455
1456 -- Pick up the value of CREATE_ATTENDANT_LEAD_FLAG for the event shchedule.
1457
1458 OPEN c_get_atnd_lead_flag(p_event_id);
1459 FETCH c_get_atnd_lead_flag INTO l_atnd_lead_flag;
1460 CLOSE c_get_atnd_lead_flag;
1461 */
1462
1463 OPEN c_get_evnt_details(p_event_id);
1464 FETCH c_get_evnt_details INTO l_evnt_sched_src_cd, l_reg_lead_flag, l_atnd_lead_flag;
1465 CLOSE c_get_evnt_details;
1466
1467 IF p_obj_type IS NOT NULL
1468 AND p_obj_srccd IS NOT NULL
1469 THEN
1470 l_evnt_sched_src_cd := p_obj_srccd;
1471 END IF;
1472
1473 -- soagrawa 05-feb-2003 bug# 2787303
1474 -- consolidated 2 separate cursors into 1 cursor
1475 /*
1476 IF l_reg_lead_flag = 'Y'
1477 THEN
1478 -- Pick up each record from ams_event_registrations for this event schedule.
1479 -- Create a lead record for the Registrant in Lead Import Interface table.
1480 -- Mark the record to say the the Lead is created for the Registrant.
1481
1482 OPEN c_get_registrant_for_lead(p_event_id);
1483 LOOP
1484 FETCH c_get_registrant_for_lead INTO l_registrant_for_lead_rec;
1485 EXIT WHEN c_get_registrant_for_lead%NOTFOUND;
1486
1487 -- Assumption : attendant_party_id has party_id from HZ_PARTIES
1488 -- against whom the lead has to be created.
1489 -- This is actually the enrollee and not the person who calls in (the call center) to do the registrations.
1490
1491
1492 -- insert_lead_rec(l_registrant_for_lead_rec.attendant_party_id
1493 -- ,l_lit_batch_id
1494 -- ,p_event_id
1495 -- ,l_evnt_sched_src_cd);
1496
1497
1498
1499 if (l_registrant_for_lead_rec.registrant_party_id = l_registrant_for_lead_rec.registrant_contact_id)
1500 then
1501 insert_lead_rec(
1502 p_party_id => l_registrant_for_lead_rec.registrant_party_id
1503 ,p_lit_batch_id => l_lit_batch_id
1504 ,p_event_id => p_event_id
1505 ,p_source_code => l_evnt_sched_src_cd);
1506 else
1507
1508 insert_lead_rec(
1509 p_party_id => l_registrant_for_lead_rec.registrant_party_id
1510 ,p_lit_batch_id => l_lit_batch_id
1511 ,p_event_id => p_event_id
1512 ,p_source_code => l_evnt_sched_src_cd
1513 ,p_contact_party_id => l_registrant_for_lead_rec.registrant_contact_id);
1514
1515 end if;
1516
1517
1518
1519 END LOOP;
1520 CLOSE c_get_registrant_for_lead;
1521
1522 END IF;
1523
1524 IF l_atnd_lead_flag = 'Y'
1525 THEN
1526 -- Pick up each record from ams_event_registrations for this event schedule where the enrollee has attended the event.
1527 -- This is indicated by a flag : ATTENDED_FLAG in ams_event_registrations.
1528 -- Create a lead record for the Attendee in Lead Import Interface table.
1529 -- Mark the record to say the the Lead is created for the Attendee.
1530
1531 OPEN c_get_attendant_for_lead(p_event_id);
1532 LOOP
1533 FETCH c_get_attendant_for_lead INTO l_attendant_for_lead_rec;
1534 EXIT WHEN c_get_attendant_for_lead%NOTFOUND;
1535
1536 -- Assumption : registrant_party_id has party_id from HZ_PARTIES
1537 -- against whom the lead has to be created.
1538
1539 --insert_lead_rec(l_attendant_for_lead_rec.attendant_party_id
1540 -- ,l_lit_batch_id
1541 -- ,p_event_id
1542 -- ,l_evnt_sched_src_cd);
1543 --
1544 --
1545 if (l_attendant_for_lead_rec.attendant_party_id = l_attendant_for_lead_rec.attendant_contact_id)
1546 then
1547 insert_lead_rec(
1548 p_party_id => l_attendant_for_lead_rec.attendant_party_id
1549 ,p_lit_batch_id => l_lit_batch_id
1550 ,p_event_id => p_event_id
1551 ,p_source_code => l_evnt_sched_src_cd);
1552 else
1553 insert_lead_rec(
1554 p_party_id => l_attendant_for_lead_rec.attendant_party_id
1555 ,p_lit_batch_id => l_lit_batch_id
1556 ,p_event_id => p_event_id
1557 ,p_source_code => l_evnt_sched_src_cd
1558 ,p_contact_party_id => l_attendant_for_lead_rec.attendant_contact_id);
1559
1560 end if;
1561
1562
1563 END LOOP;
1564 CLOSE c_get_attendant_for_lead;
1565
1566 END IF;
1567 */
1568
1569 -- soagrawa 05-feb-2003 bug# 2787303
1570 -- added processing based on new cursor
1571 OPEN c_get_reg_att_for_lead(p_event_id);
1572 LOOP
1573 FETCH c_get_reg_att_for_lead INTO l_reg_att_for_lead_rec;
1574 EXIT WHEN c_get_reg_att_for_lead%NOTFOUND;
1575
1576 if (l_reg_att_for_lead_rec.party_id = l_reg_att_for_lead_rec.contact_id)
1577 then
1578 insert_lead_rec(
1579 p_party_id => l_reg_att_for_lead_rec.party_id
1580 ,p_lit_batch_id => l_lit_batch_id
1581 ,p_event_id => p_event_id
1582 ,p_source_code => l_evnt_sched_src_cd);
1583 else
1584 -- b2b
1585 -- dbiswas 23-apr-2003 modified for NI bug# 2610067
1586 OPEN c_get_person_id(l_reg_att_for_lead_rec.contact_id);
1587 FETCH c_get_person_id INTO l_reg_att_party_id;
1588 CLOSE c_get_person_id;
1589
1590 insert_lead_rec(
1591 p_party_id => l_reg_att_for_lead_rec.party_id
1592 ,p_lit_batch_id => l_lit_batch_id
1593 ,p_event_id => p_event_id
1594 ,p_source_code => l_evnt_sched_src_cd
1595 -- ,p_contact_party_id => l_reg_att_for_lead_rec.contact_id);
1596 ,p_contact_party_id => l_reg_att_party_id);
1597
1598 end if;
1599
1600
1601 END LOOP;
1602 CLOSE c_get_reg_att_for_lead;
1603
1604
1605
1606 -- At this point we will have added all the records in as_import_interface table.
1607 -- Now we can call the concurrent program for lead process.
1608
1609 OPEN c_loaded_rows_for_lead(l_lit_batch_id);
1610 FETCH c_loaded_rows_for_lead INTO l_loaded_rows;
1611 CLOSE c_loaded_rows_for_lead;
1612
1613 -- Later add a new message for the following.
1614 -- Using the one from Lists area for now.
1615 FND_MESSAGE.set_name ('AMS', 'AMS_IMP_LOADED_NO_ROWS');
1616 FND_MESSAGE.set_token ('NUM_ROWS', l_loaded_rows);
1617
1618 l_request_id := 0;
1619
1620 -- Call the concurrent program for leads.
1621 l_request_id := FND_REQUEST.SUBMIT_REQUEST (
1622 application => 'AS',
1623 program => 'ASXSLIMP',
1624 argument1 => 'MARKETING', --'NEW'
1625 argument2 => NULL,
1626 argument3 => l_lit_batch_id --NULL
1627 );
1628
1629 AMS_Utility_PVT.Create_Log (
1630 x_return_status => l_return_status,
1631 p_arc_log_used_by => 'EREG',
1632 p_log_used_by_id => p_event_id,
1633 p_msg_data => 'Starting LEAD program (ASXSLIMP) -- concurrent program_id is ' || to_char(l_request_id),
1634 p_msg_type => 'DEBUG'
1635 );
1636
1637 IF l_request_id = 0 THEN
1638 RAISE FND_API.g_exc_unexpected_error;
1639 --Change the above to raise a specific error later.
1640 END IF;
1641
1642 -- Import completed successfully
1643 -- Later add a new message for the following.
1644 -- Using the one from Lists area.
1645 FND_MESSAGE.set_name ('AMS', 'AMS_IMP_LOAD_COMPLETE');
1646 FND_MESSAGE.set_token ('REQUEST_ID', l_request_id);
1647
1648 END process_leads;
1649
1650 --=======================================================================
1651 -- PROCEDURE
1652 -- insert_lead_rec
1653 --
1654 -- NOTES
1655 -- This procedure actually inserts a record in the lead import interface
1656 -- table.
1657 --
1658 -- HISTORY
1659 -- 08/13/2001 gdeodhar Created.
1660 --=======================================================================
1661
1662 PROCEDURE insert_lead_rec(
1663 p_party_id IN NUMBER
1664 ,p_lit_batch_id IN NUMBER
1665 ,p_event_id IN NUMBER
1666 ,p_source_code IN VARCHAR2
1667 ,p_contact_party_id IN NUMBER := NULL
1668 )
1669 IS
1670 l_seq NUMBER; --Next value for the primary key in the import lead interface table.
1671 l_party_type VARCHAR2(30); --party_type for the p_party_id.
1672 l_party_site_id NUMBER; --primary party_site_id for the party_id from hz_party_sites table.
1673
1674 CURSOR c_rec_id IS --Cursor to pick up the next value in the sequence.
1675 SELECT as_import_interface_s.NEXTVAL
1676 FROM DUAL;
1677
1678 --Cursor to pick up the data from HZ_PARTIES table.
1679 CURSOR c_party_info (party_id_in IN NUMBER) IS
1680 SELECT party_type
1681 FROM hz_parties
1682 WHERE party_id = party_id_in;
1683
1684 --Cursor to pick up the data from HZ_PARTIE_SITES table.
1685 CURSOR c_party_site_info (party_id_in IN NUMBER) IS
1686 SELECT party_site_id
1687 FROM hz_party_sites
1688 WHERE party_id = party_id_in
1689 AND identifying_address_flag = 'Y';
1690
1691 BEGIN
1692 -- Pick up the next value from the sequence.
1693 -- This will be recorded in the Lead Import Interface table as the primary key.
1694
1695 OPEN c_rec_id;
1696 FETCH c_rec_id INTO l_seq;
1697 CLOSE c_rec_id;
1698
1699 -- pick up the party_type.
1700
1701 OPEN c_party_info(p_party_id);
1702 FETCH c_party_info INTO l_party_type;
1703 CLOSE c_party_info;
1704
1705 -- pick up the party_site_id.
1706
1707 OPEN c_party_site_info(p_party_id);
1708 FETCH c_party_site_info INTO l_party_site_id;
1709 CLOSE c_party_site_info;
1710
1711 -- insert the record in as_import_interface table.
1712
1713 INSERT INTO as_import_interface
1714 (
1715 IMPORT_INTERFACE_ID --NOT NULL NUMBER
1716 , LAST_UPDATE_DATE --NOT NULL DATE
1717 , LAST_UPDATED_BY --NOT NULL NUMBER
1718 , CREATION_DATE --NOT NULL DATE
1719 , CREATED_BY --NOT NULL NUMBER
1720 , LAST_UPDATE_LOGIN --NOT NULL NUMBER
1721 , LOAD_TYPE -- VARCHAR2(20)
1722 , LOAD_DATE --NOT NULL DATE
1723 , PROMOTION_CODE -- VARCHAR2(50)
1724 , STATUS_CODE -- VARCHAR2(30)
1725 , LEAD_NOTE -- VARCHAR2(2000)
1726 , SOURCE_SYSTEM -- VARCHAR2(30)
1727 , PARTY_TYPE -- VARCHAR2(30)
1728 , BATCH_ID -- NUMBER(15)
1729 , PARTY_ID -- NUMBER(15)
1730 , PARTY_SITE_ID -- NUMBER(15)
1731 ,load_status
1732 ,contact_party_id
1733 )
1734 VALUES
1735 (
1736 l_seq --IMPORT_INTERFACE_ID --NOT NULL NUMBER
1737 , SYSDATE --LAST_UPDATE_DATE --NOT NULL DATE
1738 , FND_GLOBAL.user_id --LAST_UPDATED_BY --NOT NULL NUMBER
1739 , SYSDATE --CREATION_DATE --NOT NULL DATE
1740 , FND_GLOBAL.user_id --CREATED_BY --NOT NULL NUMBER
1741 , FND_GLOBAL.conc_login_id --LAST_UPDATE_LOGIN --NOT NULL NUMBER
1742 , 'LEAD_LOAD' --LOAD_TYPE -- VARCHAR2(20)
1743 , SYSDATE --LOAD_DATE --NOT NULL DATE
1744 , p_source_code --PROMOTION_CODE -- VARCHAR2(50)
1745 , FND_PROFILE.Value('AS_DEFAULT_LEAD_STATUS') --STATUS_CODE -- VARCHAR2(30)
1746 , 'Event Registrant is created as a lead.' --LEAD_NOTE -- VARCHAR2(2000)
1747 , 'MARKETING' --SOURCE_SYSTEM -- VARCHAR2(30)
1748 , l_party_type --PARTY_TYPE -- VARCHAR2(30)
1749 , p_lit_batch_id --BATCH_ID -- NUMBER(15)
1750 , p_party_id --PARTY_ID -- NUMBER(15)
1751 , l_party_site_id --PARTY_SITE_ID -- NUMBER(15)
1752 ,'NEW'
1753 , p_contact_party_id -- load_status
1754 );
1755
1756 null;
1757 END insert_lead_rec;
1758
1759 --=======================================================================
1760 -- PROCEDURE
1761 -- Convert_Evnt_Currency
1762 -- NOTES
1763 -- This procedure is created to convert the transaction currency into
1764 -- functional currency.
1765 -- HISTORY
1766 -- 10/30/2000 mukumar Created.
1767 --=======================================================================
1768 PROCEDURE Convert_Evnt_Currency(
1769 p_tc_curr IN VARCHAR2,
1770 p_tc_amt IN NUMBER,
1771 x_fc_curr OUT NOCOPY VARCHAR2,
1772 x_fc_amt OUT NOCOPY NUMBER
1773 )
1774 IS
1775 L_FUNC_CURR_PROF CONSTANT VARCHAR2(30) := 'AMS_DEFAULT_CURR_CODE';
1776 l_curr_code VARCHAR2(240) ;
1777 l_return_status VARCHAR2(30);
1778 BEGIN
1779 l_curr_code := FND_PROFILE.Value(L_FUNC_CURR_PROF);
1780 IF l_curr_code IS NULL THEN
1781 l_curr_code := 'USD' ;
1782 END IF ;
1783
1784 AMS_Utility_PVT.Convert_Currency(
1785 x_return_status => l_return_status ,
1786 p_from_currency => p_tc_curr,
1787 p_to_currency => l_curr_code,
1788 p_from_amount => p_tc_amt,
1789 x_to_amount => x_fc_amt
1790 );
1791
1792 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1793 RAISE FND_API.g_exc_error;
1794 END IF;
1795
1796 x_fc_curr := l_curr_code ;
1797
1798 END Convert_Evnt_Currency;
1799
1800 --=======================================================================
1801 -- PROCEDURE
1802 -- Add_Update_Access_record
1803 -- NOTES
1804 -- This procedure is to create or update Acess_record(owner record)
1805 -- HISTORY
1806 -- 10/30/2000 mukumar Created.
1807 --=======================================================================
1808 PROCEDURE Add_Update_Access_record(
1809 p_object_type IN VARCHAR2,
1810 p_object_id IN NUMBER,
1811 p_Owner_user_id IN NUMBER,
1812 x_return_status OUT NOCOPY VARCHAR2,
1813 x_msg_count OUT NOCOPY VARCHAR2,
1814 x_msg_data OUT NOCOPY VARCHAR2
1815 )
1816 IS
1817
1818 l_owner_user_id NUMBER;
1819 l_Access_rec AMS_access_PVT.access_rec_type;
1820 l_access_id NUMBER;
1821 l_ret_stat VARCHAR2(1);
1822 l_msg_data VARCHAR2(2000);
1823 l_msg_cnt NUMBER;
1824
1825 CURSOR c_get_access_data(object_type_in IN VARCHAR2, object_id_in IN NUMBER, owner_user_id_in IN NUMBER) IS
1826 SELECT USER_OR_ROLE_ID
1827 FROM AMS_ACT_ACCESS
1828 WHERE ACT_ACCESS_TO_OBJECT_ID = object_id_in
1829 AND ARC_ACT_ACCESS_TO_OBJECT = object_type_in
1830 AND OWNER_FLAG = 'Y';
1831 BEGIN
1832 open c_get_access_data(p_object_type, p_object_id, p_Owner_user_id);
1833 fetch c_get_access_data INTO l_owner_user_id;
1834 If c_get_access_data%NOTFOUND THEN
1835 l_Access_rec.ACT_ACCESS_TO_OBJECT_ID := p_object_id;
1836 l_Access_rec.ARC_ACT_ACCESS_TO_OBJECT := p_object_type;
1837 l_Access_rec.USER_OR_ROLE_ID := p_Owner_user_id;
1838 l_Access_rec.OBJECT_VERSION_NUMBER := 1;
1839 l_Access_rec.OWNER_FLAG := 'Y';
1840 l_Access_rec.ARC_USER_OR_ROLE_TYPE := 'USER';
1841 AMS_access_PVT.create_access(p_api_version => 1,
1842 x_return_status => x_return_status,
1843 x_msg_count => x_msg_count,
1844 x_msg_data => x_msg_data,
1845 p_access_rec => l_Access_rec,
1846 x_access_id => l_access_id);
1847 ELSE
1848 IF (l_owner_user_id <> p_Owner_user_id) THEN
1849 AMS_access_PVT.update_object_owner(p_api_version => 1,
1850 x_return_status => x_return_status,
1851 x_msg_data => x_msg_data,
1852 x_msg_count => x_msg_count,
1853 p_object_type => p_object_type,
1854 p_object_id => p_object_id,
1855 p_resource_id => p_Owner_user_id,
1856 p_old_resource_id => l_owner_user_id);
1857 END IF;
1858 END IF;
1859
1860 END Add_Update_Access_record;
1861 ---------------------------------------------------------------------
1862 -- PROCEDURE
1863 -- push_source_code
1864 --
1865 -- HISTORY
1866 -- 11/01/99 holiu Created.
1867 ---------------------------------------------------------------------
1868 PROCEDURE push_source_code(
1869 p_source_code IN VARCHAR2,
1870 p_arc_object IN VARCHAR2,
1871 p_object_id IN NUMBER,
1872 p_related_source_code IN VARCHAR2 := NULL,
1873 p_related_source_object IN VARCHAR2 := NULL,
1874 p_related_source_id IN NUMBER := NULL
1875 )
1876 IS
1877
1878 l_sourcecode_id NUMBER;
1879 l_return_status VARCHAR2(1);
1880 l_msg_count NUMBER;
1881 l_msg_data VARCHAR2(2000);
1882
1883 BEGIN
1884
1885 AMS_SourceCode_PVT.create_sourcecode(
1886 p_api_version => 1.0,
1887 p_init_msg_list => FND_API.g_false,
1888 p_commit => FND_API.g_false,
1889 p_validation_level => FND_API.g_valid_level_full,
1890
1891 x_return_status => l_return_status,
1892 x_msg_count => l_msg_count,
1893 x_msg_data => l_msg_data,
1894
1895 p_sourcecode => p_source_code,
1896 p_sourcecode_for => p_arc_object,
1897 p_sourcecode_for_id => p_object_id,
1898 p_related_sourcecode => p_related_source_code,
1899 p_releated_sourceobj => p_related_source_object,
1900 p_related_sourceid => p_related_source_id,
1901 x_sourcecode_id => l_sourcecode_id
1902 );
1903
1904 IF l_return_status <> FND_API.g_ret_sts_success THEN
1905 RAISE FND_API.g_exc_error;
1906 END IF;
1907
1908 END push_source_code;
1909 --========================================================================
1910 -- PROCEDURE
1911 -- Create_list
1912 --
1913 -- PURPOSE
1914 --
1915 --
1916 -- NOTE
1917 -- The list of Type <> is created in list header and the association is
1918 -- created in the ams_act_lists table.
1919 --
1920 -- HISTORY
1921 -- 06/01/01 mukumar created
1922 --
1923 --========================================================================
1924 PROCEDURE Create_list
1925 (p_evo_id IN NUMBER,
1926 p_evo_name IN VARCHAR2,
1927 p_obj_type In VARCHAR2,
1928 p_owner_id IN NUMBER)
1929 IS
1930 l_return_status VARCHAR2(1) ;
1931 l_msg_count NUMBER ;
1932 l_msg_data VARCHAR2(2000);
1933 l_api_version NUMBER := 1.0 ;
1934 l_dummy number;
1935
1936 l_list_header_rec AMS_ListHeader_Pvt.list_header_rec_type;
1937 l_act_list_rec AMS_Act_List_Pvt.act_list_rec_type;
1938 l_list_header_id NUMBER ;
1939 l_act_list_header_id NUMBER ;
1940
1941 CURSOR c_evnt_name_exist(name_in in VARCHAR2) IS
1942 SELECT count(event_offer_name)
1943 FROM ams_event_offers_vl
1944 WHERE event_offer_name = name_in;
1945 BEGIN
1946 null;
1947 /*
1948 l_dummy := 0;
1949 -- AMS_ListHeader_PVT.init_listheader_rec(l_list_header_rec);
1950 open c_evnt_name_exist(p_evo_name);
1951 fetch c_evnt_name_exist into l_dummy;
1952 close c_evnt_name_exist;
1953 if l_dummy > 0 then
1954 l_list_header_rec.list_name := p_evo_name ||' - '||AMS_Utility_PVT.get_lookup_meaning('AMS_SYS_ARC_QUALIFIER','ILST') || to_char(l_dummy+1) ;
1955 else
1956 l_list_header_rec.list_name := p_evo_name ||' - '||AMS_Utility_PVT.get_lookup_meaning('AMS_SYS_ARC_QUALIFIER','ILST');
1957 end if;
1958 l_list_header_rec.list_type := 'TARGET';
1959 -- Have to be removed.
1960 l_list_header_rec.list_source_type := 'PERSON_LIST' ;
1961 l_list_header_rec.owner_user_id := p_owner_id;
1962 AMS_ListHeader_PVT.Create_Listheader
1963 ( p_api_version => 1.0,
1964 p_init_msg_list => FND_API.g_false,
1965 p_commit => FND_API.g_false,
1966 p_validation_level => FND_API.g_valid_level_full,
1967
1968 x_return_status => l_return_status,
1969 x_msg_count => l_msg_count,
1970 x_msg_data => l_msg_data,
1971 p_listheader_rec => l_list_header_rec,
1972 x_listheader_id => l_list_header_id
1973 );
1974
1975 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1976 RAISE FND_API.G_EXC_ERROR;
1977 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1978 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1979 END IF;
1980
1981 l_act_list_rec.list_header_id := l_list_header_id;
1982 l_act_list_rec.list_used_by := p_obj_type;
1983 l_act_list_rec.list_used_by_id := p_evo_id ;
1984 l_act_list_rec.list_act_type := 'TARGET';
1985
1986 AMS_Act_List_PVT.Create_Act_List(
1987 p_api_version_number => 1.0,
1988 p_init_msg_list => FND_API.g_false,
1989 p_commit => FND_API.g_false,
1990 p_validation_level => FND_API.g_valid_level_full,
1991 x_return_status => l_return_status,
1992 x_msg_count => l_msg_count,
1993 x_msg_data => l_msg_data,
1994 p_act_list_rec => l_act_list_rec ,
1995 x_act_list_header_id => l_act_list_header_id
1996 ) ;
1997
1998 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1999 RAISE FND_API.G_EXC_ERROR;
2000 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2001 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2002 END IF;
2003 */
2004 END Create_list;
2005
2006 --==========================================================================
2007 -- PROCEDURE
2008 -- Cancel_RollupEvent
2009 --
2010 -- PURPOSE
2011 -- Cancels the Rolup event and their associated event schedules. If the status
2012 -- order rules does not permit it, it will error out.
2013 --
2014 -- NOTES
2015 -- HISTORY
2016 -- 17-Jul-2001 mukumar Created.
2017 --==========================================================================
2018
2019 PROCEDURE Cancel_RollupEvent(p_evh_id IN NUMBER) IS
2020
2021 CURSOR c_evh IS
2022 SELECT event_header_id,object_version_number,system_status_code
2023 FROM ams_event_headers_all_b
2024 WHERE EVENT_HEADER_ID = p_evh_id
2025 AND system_status_code <> 'CANCELLED' ;
2026
2027 l_event_header_id NUMBER ;
2028 l_obj_version NUMBER ;
2029 l_status_code VARCHAR2(30) ;
2030 l_status_id NUMBER := AMS_Utility_PVT.get_default_user_status('AMS_EVENT_STATUS','CANCELLED');
2031
2032 BEGIN
2033
2034 Cancel_Exec_Event(p_evh_id);
2035 OPEN c_evh ;
2036 LOOP
2037 FETCH c_evh INTO l_event_header_id,l_obj_version,l_status_code ;
2038 EXIT WHEN c_evh%NOTFOUND ;
2039 IF FND_API.G_TRUE = AMS_Utility_PVT.Check_Status_Change('AMS_EVENT_STATUS',l_status_code,'CANCELLED') THEN
2040 -- Can cancel the schedule
2041 UPDATE ams_event_headers_all_b
2042 SET system_status_code = 'CANCELLED',
2043 last_status_date = SYSDATE,
2044 user_status_id = l_status_id,
2045 object_version_number = object_version_number + 1
2046 WHERE event_header_id = l_event_header_id
2047 AND object_version_number = l_obj_version ;
2048
2049 IF (SQL%NOTFOUND) THEN
2050 CLOSE c_evh ;
2051 AMS_Utility_PVT.Error_Message('AMS_API_RECORD_NOT_FOUND');
2052 RAISE FND_API.g_exc_error;
2053 END IF;
2054 ELSE -- Can not cancel the schedule as the status is can not go to cancel from current status
2055 CLOSE c_evh;
2056 AMS_Utility_PVT.Error_Message('AMS_EVH_CANNOT_CANCEL');
2057 RAISE FND_API.g_exc_error;
2058 END IF ;
2059
2060 END LOOP;
2061 CLOSE c_evh;
2062
2063
2064 END Cancel_RollupEvent;
2065
2066 --================================================================================
2067 -- PROCEDURE
2068 -- Cancel_Exec_Event
2069 --
2070 -- PURPOSE
2071 -- Cancels the Rolup event and their associated event schedules. If the status
2072 -- order rules does not permit it, it will error out.
2073 --
2074 -- NOTES
2075 -- HISTORY
2076 -- 17-Jul-2001 mukumar Created.
2077 -- 17-Mar-2005 spendem Modified to raise the business event. enh # 3805347
2078 --==================================================================================
2079
2080 PROCEDURE Cancel_Exec_Event(p_evh_id IN NUMBER) IS
2081
2082 -- Modified the select statement of the cursor to get event_object_type, as per enh # 3805347
2083 CURSOR c_evo_list IS
2084 SELECT event_offer_id,object_version_number,system_status_code, event_object_type
2085 FROM ams_event_offers_all_b
2086 WHERE event_header_id = p_evh_id
2087 AND system_status_code <> 'CANCELLED' ;
2088
2089 l_event_offer_id NUMBER ;
2090 l_obj_version NUMBER ;
2091 l_status_code VARCHAR2(30) ;
2092 l_status_id NUMBER := AMS_Utility_PVT.get_default_user_status('AMS_EVENT_STATUS','CANCELLED');
2093 l_obj_type VARCHAR2(4); -- added as per enh # 3805347
2094
2095 BEGIN
2096
2097 OPEN c_evo_list ;
2098 LOOP
2099 FETCH c_evo_list INTO l_event_offer_id,l_obj_version,l_status_code,l_obj_type ; -- modified as per enh # 3805347
2100 EXIT WHEN c_evo_list%NOTFOUND ;
2101 IF FND_API.G_TRUE = AMS_Utility_PVT.Check_Status_Change('AMS_EVENT_STATUS',l_status_code,'CANCELLED') THEN
2102 -- Can cancel the schedule
2103 UPDATE ams_event_offers_all_b
2104 SET system_status_code = 'CANCELLED',
2105 last_status_date = SYSDATE,
2106 user_status_id = l_status_id,
2107 object_version_number = object_version_number + 1
2108 WHERE event_offer_id = l_event_offer_id
2109 AND object_version_number = l_obj_version ;
2110
2111 -- call to api to raise business event, as per enh # 3805347
2112 AMS_SCHEDULERULES_PVT.RAISE_BE_ON_STATUS_CHANGE(p_obj_id => l_event_offer_id,
2113 p_obj_type => l_obj_type,
2114 p_old_status_code => l_status_code,
2115 p_new_status_code => 'CANCELLED' );
2116
2117
2118 IF (SQL%NOTFOUND) THEN
2119 CLOSE c_evo_list ;
2120 AMS_Utility_PVT.Error_Message('AMS_API_RECORD_NOT_FOUND');
2121 RAISE FND_API.g_exc_error;
2122 END IF;
2123 ELSE -- Can not cancel the schedule as the status is can not go to cancel from current status
2124 CLOSE c_evo_list;
2125 AMS_Utility_PVT.Error_Message('AMS_EVO_CANNOT_CANCEL');
2126 RAISE FND_API.g_exc_error;
2127 END IF ;
2128
2129 END LOOP;
2130 CLOSE c_evo_list;
2131
2132
2133 END Cancel_exec_event;
2134
2135 --===============================================================================
2136 -- PROCEDURE
2137 -- Cancel_oneoff_Event
2138 --
2139 -- PURPOSE
2140 -- Cancels the Rolup event and their associated event schedules. If the status
2141 -- order rules does not permit it, it will error out.
2142 --
2143 -- NOTES
2144 -- HISTORY
2145 -- 17-Jul-2001 mukumar Created.
2146 -- 17-Mar-2005 spendem Modified to raise the business event. enh # 3805347
2147 --=================================================================================
2148
2149 PROCEDURE Cancel_oneoff_event(p_offer_id IN NUMBER) IS
2150
2151 -- Modified the select statement of the cursor to get event_object_type, as per enh # 3805347
2152 CURSOR c_evo_list IS
2153 SELECT event_offer_id,object_version_number,system_status_code, event_object_type
2154 FROM ams_event_offers_all_b
2155 WHERE event_offer_id = p_offer_id
2156 AND system_status_code <> 'CANCELLED' ;
2157
2158 l_event_offer_id NUMBER ;
2159 l_obj_version NUMBER ;
2160 l_status_code VARCHAR2(30) ;
2161 l_status_id NUMBER := AMS_Utility_PVT.get_default_user_status('AMS_EVENT_STATUS','CANCELLED');
2162 l_obj_type VARCHAR2(4); -- added as per enh # 3805347
2163
2164 BEGIN
2165
2166 OPEN c_evo_list ;
2167 LOOP
2168 FETCH c_evo_list INTO l_event_offer_id,l_obj_version,l_status_code, l_obj_type; -- Modified as per enh # 3805347
2169 EXIT WHEN c_evo_list%NOTFOUND ;
2170 IF FND_API.G_TRUE = AMS_Utility_PVT.Check_Status_Change('AMS_EVENT_STATUS',l_status_code,'CANCELLED') THEN
2171 -- Can cancel the schedule
2172 UPDATE ams_event_offers_all_b
2173 SET system_status_code = 'CANCELLED',
2174 last_status_date = SYSDATE,
2175 user_status_id = l_status_id,
2176 object_version_number = object_version_number + 1
2177 WHERE event_offer_id = l_event_offer_id
2178 AND object_version_number = l_obj_version ;
2179
2180 -- call to api to raise business event, as per enh # 3805347
2181 AMS_SCHEDULERULES_PVT.RAISE_BE_ON_STATUS_CHANGE(p_obj_id => l_event_offer_id,
2182 p_obj_type => l_obj_type,
2183 p_old_status_code => l_status_code,
2184 p_new_status_code => 'CANCELLED' );
2185
2186
2187 IF (SQL%NOTFOUND) THEN
2188 CLOSE c_evo_list ;
2189 AMS_Utility_PVT.Error_Message('AMS_API_RECORD_NOT_FOUND');
2190 RAISE FND_API.g_exc_error;
2191 END IF;
2192 ELSE -- Can not cancel the schedule as the status is can not go to cancel from current status
2193 CLOSE c_evo_list;
2194 AMS_Utility_PVT.Error_Message('AMS_EONE_CANNOT_CANCEL');
2195 RAISE FND_API.g_exc_error;
2196 END IF ;
2197
2198 END LOOP;
2199 CLOSE c_evo_list;
2200
2201
2202 END Cancel_oneoff_event;
2203
2204 --==========================================================================
2205 -- FUNCTION
2206 -- cancel_all_Event
2207 --
2208 -- PURPOSE
2209 -- completes all Rolup event and their associated event schedules. If the status
2210 -- order rules does not permit it, it will error out.
2211 --
2212 -- NOTES
2213 -- HISTORY
2214 -- 17-Jul-2001 mukumar Created.
2215 -- 18-Feb-2002 GMADANA Modified.
2216 -- Changed it from procrdure to Function which returns
2217 -- TRUE if all the event schedules or event headers attached
2218 -- to a given program id are CANCELLED or FALSE otherwise.
2219 --==========================================================================
2220
2221
2222 FUNCTION Cancel_all_Event(p_prog_id IN NUMBER) RETURN VARCHAR2
2223 IS
2224
2225 CURSOR c_evh_list IS
2226 SELECT count(*)
2227 FROM ams_event_headers_all_b
2228 WHERE program_id = p_prog_id
2229 AND system_status_code NOT IN ('CANCELLED', 'ARCHIVED');
2230
2231 CURSOR c_evo_list IS
2232 SELECT count(*)
2233 FROM ams_event_offers_all_b
2234 WHERE parent_id = p_prog_id
2235 AND event_object_type = 'EONE'
2236 AND parent_type = 'RCAM'
2237 AND system_status_code NOT IN ('CANCELLED', 'ARCHIVED');
2238
2239 l_event_offers NUMBER ;
2240 l_event_headers NUMBER ;
2241
2242 BEGIN
2243 OPEN c_evh_list;
2244 FETCH c_evh_list INTO l_event_headers ;
2245 CLOSE c_evh_list;
2246
2247 OPEN c_evo_list ;
2248 FETCH c_evo_list INTO l_event_offers;
2249 CLOSE c_evo_list;
2250
2251 IF(l_event_offers > 0 OR l_event_headers > 0)
2252 THEN
2253 RETURN FND_API.g_false;
2254 ELSE
2255 RETURN FND_API.g_true;
2256 END IF;
2257
2258 END Cancel_all_event;
2259
2260 --==========================================================================
2261 -- PROCEDURE
2262 -- complete_RollupEvent
2263 --
2264 -- PURPOSE
2265 -- completes the Rolup event and their associated event schedules. If the status
2266 -- order rules does not permit it, it will error out.
2267 --
2268 -- NOTES
2269 -- HISTORY
2270 -- 17-Jul-2001 mukumar Created.
2271 --==========================================================================
2272
2273 PROCEDURE complete_RollupEvent(p_evh_id IN NUMBER) IS
2274
2275 CURSOR c_evh IS
2276 SELECT event_header_id,object_version_number,system_status_code
2277 FROM ams_event_headers_all_b
2278 WHERE EVENT_HEADER_ID = p_evh_id
2279 AND system_status_code <> 'COMPLETED' ;
2280
2281 l_event_header_id NUMBER ;
2282 l_obj_version NUMBER ;
2283 l_status_code VARCHAR2(30) ;
2284 l_status_id NUMBER := AMS_Utility_PVT.get_default_user_status('AMS_EVENT_STATUS','COMPLETED');
2285
2286 BEGIN
2287
2288 complete_Exec_Event(p_evh_id);
2289 OPEN c_evh ;
2290 LOOP
2291 FETCH c_evh INTO l_event_header_id,l_obj_version,l_status_code ;
2292 EXIT WHEN c_evh%NOTFOUND ;
2293 IF FND_API.G_TRUE = AMS_Utility_PVT.Check_Status_Change('AMS_EVENT_STATUS',l_status_code,'COMPLETED') THEN
2294 -- Can complete the schedule
2295 UPDATE ams_event_headers_all_b
2296 SET system_status_code = 'COMPLETED',
2297 last_status_date = SYSDATE,
2298 user_status_id = l_status_id,
2299 object_version_number = object_version_number + 1
2300 WHERE event_header_id = l_event_header_id
2301 AND object_version_number = l_obj_version ;
2302
2303 IF (SQL%NOTFOUND) THEN
2304 CLOSE c_evh ;
2305 AMS_Utility_PVT.Error_Message('AMS_API_RECORD_NOT_FOUND');
2306 RAISE FND_API.g_exc_error;
2307 END IF;
2308 ELSE -- Can not complete the schedule as the status is can not go to complete from current status
2309 CLOSE c_evh;
2310 AMS_Utility_PVT.Error_Message('AMS_EVH_CANNOT_COMPLETE');
2311 RAISE FND_API.g_exc_error;
2312 END IF ;
2313
2314 END LOOP;
2315 CLOSE c_evh;
2316
2317 END complete_RollupEvent;
2318
2319 --==================================================================================
2320 -- PROCEDURE
2321 -- complete_Exec_Event
2322 --
2323 -- PURPOSE
2324 -- completes the Rolup event and their associated event schedules. If the status
2325 -- order rules does not permit it, it will error out.
2326 --
2327 -- NOTES
2328 -- HISTORY
2329 -- 17-Jul-2001 mukumar Created.
2330 -- 17-Mar-2005 spendem Modified to raise the business event. enh # 3805347
2331 --===================================================================================
2332
2333 PROCEDURE complete_Exec_Event(p_evh_id IN NUMBER) IS
2334
2335 -- Modified the select statement of the cursor to get event_object_type, as per enh # 3805347
2336 CURSOR c_evo_list IS
2337 SELECT event_offer_id,object_version_number,system_status_code, event_object_type
2338 FROM ams_event_offers_all_b
2339 WHERE event_header_id = p_evh_id
2340 AND system_status_code <> 'COMPLETED' ;
2341
2342 l_event_offer_id NUMBER ;
2343 l_obj_version NUMBER ;
2344 l_status_code VARCHAR2(30) ;
2345 l_status_id NUMBER := AMS_Utility_PVT.get_default_user_status('AMS_EVENT_STATUS','COMPLETED');
2346 l_obj_type VARCHAR2(4); -- added as per enh # 3805347
2347
2348 BEGIN
2349
2350 OPEN c_evo_list ;
2351 LOOP
2352 FETCH c_evo_list INTO l_event_offer_id,l_obj_version,l_status_code, l_obj_type; -- Modified as per enh # 3805347.
2353 EXIT WHEN c_evo_list%NOTFOUND ;
2354 IF FND_API.G_TRUE = AMS_Utility_PVT.Check_Status_Change('AMS_EVENT_STATUS',l_status_code,'COMPLETED') THEN
2355 -- Can complete the schedule
2356 UPDATE ams_event_offers_all_b
2357 SET system_status_code = 'COMPLETED',
2358 last_status_date = SYSDATE,
2359 user_status_id = l_status_id,
2360 object_version_number = object_version_number + 1
2361 WHERE event_offer_id = l_event_offer_id
2362 AND object_version_number = l_obj_version ;
2363
2364 -- call to api to raise business event, as per enh # 3805347
2365 AMS_SCHEDULERULES_PVT.RAISE_BE_ON_STATUS_CHANGE(p_obj_id => l_event_offer_id,
2366 p_obj_type => l_obj_type,
2367 p_old_status_code => l_status_code,
2368 p_new_status_code => 'COMPLETED' );
2369
2370
2371 IF (SQL%NOTFOUND) THEN
2372 CLOSE c_evo_list ;
2373 AMS_Utility_PVT.Error_Message('AMS_API_RECORD_NOT_FOUND');
2374 RAISE FND_API.g_exc_error;
2375 END IF;
2376 ELSE -- Can not complete the schedule as the status is can not go to complete from current status
2377 CLOSE c_evo_list;
2378 AMS_Utility_PVT.Error_Message('AMS_EVO_CANNOT_COMPLETE');
2379 RAISE FND_API.g_exc_error;
2380 END IF ;
2381
2382 END LOOP;
2383 CLOSE c_evo_list;
2384
2385 END complete_exec_event;
2386
2387 --=================================================================================
2388 -- PROCEDURE
2389 -- complete_oneoff_Event
2390 --
2391 -- PURPOSE
2392 -- completes the Rolup event and their associated event schedules. If the status
2393 -- order rules does not permit it, it will error out.
2394 --
2395 -- NOTES
2396 -- HISTORY
2397 -- 17-Jul-2001 mukumar Created.
2398 -- 17-Mar-2005 spendem Modified to raise the business event. enh # 3805347
2399 --===================================================================================
2400
2401 PROCEDURE complete_oneoff_event(p_offer_id IN NUMBER) IS
2402
2403 -- Modified the select statement of the cursor to get event_object_type, as per enh # 3805347
2404 CURSOR c_evo_list IS
2405 SELECT event_offer_id,object_version_number,system_status_code, event_object_type
2406 FROM ams_event_offers_all_b
2407 WHERE event_offer_id = p_offer_id
2408 AND system_status_code <> 'COMPLETED';
2409
2410 l_event_offer_id NUMBER ;
2411 l_obj_version NUMBER ;
2412 l_status_code VARCHAR2(30) ;
2413 l_status_id NUMBER := AMS_Utility_PVT.get_default_user_status('AMS_EVENT_STATUS','COMPLETED');
2414 l_obj_type VARCHAR2(4); -- added as per enh # 3805347
2415
2416 BEGIN
2417
2418 OPEN c_evo_list ;
2419 LOOP
2420 FETCH c_evo_list INTO l_event_offer_id,l_obj_version,l_status_code, l_obj_type; -- Modified as per enh # 3805347
2421 EXIT WHEN c_evo_list%NOTFOUND ;
2422 IF FND_API.G_TRUE = AMS_Utility_PVT.Check_Status_Change('AMS_EVENT_STATUS',l_status_code,'COMPLETED') THEN
2423 -- Can complete the schedule
2424 UPDATE ams_event_offers_all_b
2425 SET system_status_code = 'COMPLETED',
2426 last_status_date = SYSDATE,
2427 user_status_id = l_status_id,
2428 object_version_number = object_version_number + 1
2429 WHERE event_offer_id = l_event_offer_id
2430 AND object_version_number = l_obj_version ;
2431
2432
2433
2434 -- call to api to raise business event, as per enh # 3805347
2435 AMS_SCHEDULERULES_PVT.RAISE_BE_ON_STATUS_CHANGE(p_obj_id => l_event_offer_id,
2436 p_obj_type => l_obj_type,
2437 p_old_status_code => l_status_code,
2438 p_new_status_code => 'COMPLETED' );
2439
2440 IF (SQL%NOTFOUND) THEN
2441 CLOSE c_evo_list ;
2442 AMS_Utility_PVT.Error_Message('AMS_API_RECORD_NOT_FOUND');
2443 RAISE FND_API.g_exc_error;
2444 END IF;
2445 ELSE -- Can not complete the schedule as the status is can not go to complete from current status
2446 CLOSE c_evo_list;
2447 AMS_Utility_PVT.Error_Message('AMS_EONE_CANNOT_COMPLETE');
2448 RAISE FND_API.g_exc_error;
2449 END IF ;
2450
2451 END LOOP;
2452 CLOSE c_evo_list;
2453
2454
2455 END complete_oneoff_event;
2456
2457 --==========================================================================
2458 -- PROCEDURE
2459 -- complete_all_Event
2460 --
2461 -- PURPOSE
2462 -- completes all Rolup event and their associated event schedules. If the status
2463 -- order rules does not permit it, it will error out.
2464 --
2465 -- NOTES
2466 -- HISTORY
2467 -- 17-Jul-2001 mukumar Created.
2468 --==========================================================================
2469
2470 PROCEDURE complete_all_Event(p_prog_id IN NUMBER) IS
2471
2472 CURSOR c_evh_list IS
2473 SELECT event_header_id,object_version_number,system_status_code
2474 FROM ams_event_headers_all_b
2475 WHERE program_id = p_prog_id;
2476
2477 CURSOR c_evo_list IS
2478 SELECT event_offer_id,object_version_number,system_status_code
2479 FROM ams_event_offers_all_b
2480 WHERE parent_id = p_prog_id
2481 AND event_object_type = 'EONE'
2482 AND parent_type = 'RCAM';
2483
2484 l_event_offer_id NUMBER ;
2485 l_event_header_id NUMBER ;
2486 l_obj_version NUMBER ;
2487 l_status_code VARCHAR2(30) ;
2488 l_status_id NUMBER := AMS_Utility_PVT.get_default_user_status('AMS_EVENT_STATUS','COMPLETED');
2489
2490 BEGIN
2491 OPEN c_evh_list;
2492 LOOP
2493 FETCH c_evh_list INTO l_event_header_id,l_obj_version,l_status_code ;
2494 EXIT WHEN c_evh_list%NOTFOUND ;
2495 complete_RollupEvent(l_event_header_id);
2496 END LOOP;
2497 CLOSE c_evh_list;
2498
2499 OPEN c_evo_list ;
2500 LOOP
2501 FETCH c_evo_list INTO l_event_offer_id,l_obj_version,l_status_code ;
2502 EXIT WHEN c_evo_list%NOTFOUND ;
2503 complete_oneoff_event(l_event_offer_id);
2504 END LOOP;
2505 CLOSE c_evo_list;
2506
2507 END complete_all_event;
2508
2509 --==========================================================================
2510 -- PROCEDURE
2511 -- Create_inventory_item
2512 --
2513 -- PURPOSE
2514 -- completes all Rolup event and their associated event schedules. If the status
2515 -- order rules does not permit it, it will error out.
2516 --
2517 -- NOTES
2518 -- HISTORY
2519 -- 17-Jul-2001 mukumar Created.
2520 --==========================================================================
2521
2522 PROCEDURE create_inventory_item(p_item_number IN VARCHAR2,
2523 p_item_desc IN VARCHAR2,
2524 p_item_long_desc IN VARCHAR2,
2525 p_user_id IN NUMBER,
2526 x_org_id OUT NOCOPY NUMBER,
2527 x_inv_item_id OUT NOCOPY NUMBER,
2528 x_return_status OUT NOCOPY VARCHAR2,
2529 x_msg_count OUT NOCOPY NUMBER,
2530 x_msg_data OUT NOCOPY VARCHAR2) IS
2531
2532 l_org_id NUMBER;
2533 l_owner_id NUMBER;
2534 p_item_owner_Rec AMS_ITEM_OWNER_PVT.ITEM_OWNER_Rec_Type;
2535 Item_rec_in AMS_ITEM_OWNER_PVT.ITEM_REC_TYPE; --INV_Item_GRP.Item_rec_type;
2536 Item_rec_out AMS_ITEM_OWNER_PVT.ITEM_REC_TYPE; --INV_Item_GRP.Item_rec_type;
2537 Error_tbl AMS_ITEM_OWNER_PVT.Error_tbl_type;
2538 x_item_return_status VARCHAR2(1);
2539 l_api_name CONSTANT VARCHAR2(30) := 'create_inventory_item';
2540 l_err_txt VARCHAR2(4000);
2541 inv_item_creation_error EXCEPTION;
2542
2543 BEGIN
2544 x_return_status := FND_API.G_RET_STS_SUCCESS;
2545 l_org_id := FND_PROFILE.Value('AMS_ITEM_ORGANIZATION_ID');
2546 p_item_owner_rec.ORGANIZATION_ID := l_org_id;
2547 p_item_owner_rec.ITEM_NUMBER := p_item_number;
2548 p_item_owner_rec.OWNER_ID := p_user_id;
2549 p_item_owner_rec.is_master_item := 'Y';
2550
2551 Item_rec_in.ORGANIZATION_ID := l_org_id;
2552 Item_rec_in.ITEM_NUMBER := p_item_number;
2553 Item_rec_in.DESCRIPTION := p_item_desc;
2554 Item_rec_in.LONG_DESCRIPTION := p_item_long_desc;
2555 x_org_id := l_org_id;
2556
2557 AMS_ITEM_OWNER_PVT.Create_item_owner(
2558 P_Api_Version_Number => 1.0,
2559 X_Return_Status => x_return_status,
2560 X_Msg_Count => x_msg_count,
2561 X_Msg_Data => x_msg_data,
2562 P_ITEM_OWNER_Rec => p_item_owner_rec,
2563 X_ITEM_OWNER_ID => l_owner_id, --- for create api
2564 P_ITEM_REC_In => Item_rec_in,
2565 P_ITEM_REC_Out => Item_rec_out,
2566 x_item_return_status => x_item_return_status,
2567 x_Error_tbl => Error_tbl
2568 );
2569
2570 IF x_item_return_status <> FND_API.g_ret_sts_success THEN
2571 RAISE inv_item_creation_error;
2572 else
2573 IF x_return_status = FND_API.g_ret_sts_success THEN
2574 x_inv_item_id := Item_rec_out.INVENTORY_ITEM_ID;
2575 x_org_id := Item_rec_out.ORGANIZATION_ID;
2576 END IF;
2577
2578 END IF;
2579 EXCEPTION
2580
2581 WHEN inv_item_creation_error THEN
2582
2583 FOR i IN 1 .. error_tbl.count LOOP
2584 l_err_txt := error_tbl(i).message_name;
2585 END LOOP;
2586 /*
2587 x_msg_data := l_err_txt;
2588 FND_MSG_PUB.count_and_get(
2589 p_encoded => FND_API.g_false,
2590 p_count => x_msg_count,
2591 p_data => x_msg_data
2592 );
2593 */
2594 FND_MESSAGE.set_name('INV', l_err_txt); -- error_tbl(i).message_name);
2595 FND_MSG_PUB.add;
2596 --END LOOP;
2597
2598 WHEN FND_API.g_exc_unexpected_error THEN
2599 --x_return_status := FND_API.g_ret_sts_unexp_error ;
2600 x_msg_count := error_tbl.count;
2601 FOR i IN 1 .. error_tbl.count LOOP
2602 FND_MSG_PUB.count_and_get(
2603 p_encoded => FND_API.g_false,
2604 p_count => x_msg_count,
2605 p_data => error_tbl(i).message_text
2606 );
2607 END LOOP;
2608
2609 WHEN OTHERS THEN
2610 -- x_return_status := FND_API.g_ret_sts_unexp_error;
2611 x_msg_count := error_tbl.count;
2612 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
2613 THEN
2614 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2615 END IF;
2616
2617 FOR i IN 1 .. error_tbl.count LOOP
2618 FND_MSG_PUB.count_and_get(
2619 p_encoded => FND_API.g_false,
2620 p_count => x_msg_count,
2621 -- p_data => x_msg_data
2622 p_data => error_tbl(i).message_text
2623 );
2624 END LOOP;
2625
2626 END create_inventory_item;
2627
2628
2629 -------------------------------------------------------------------------------
2630 -- Procedure Update_Event_Header_Status
2631 --
2632 -- Purpose If the Event Header Status is Active, make all the schedules
2633 -- attached to it which are in available status as Active
2634 --
2635 -- If the Event Header is cancelled, all its children are also
2636 -- cancelled.
2637 --
2638 -- HISTORY
2639 -- 07-Jan-2002 gmadana Created.
2640 -- 15-Feb-2002 gmadana Modified.
2641 -- If the Event Header is to be cancelled, first check whether its
2642 -- children are cancelled. If any one of them is not Cancelled, then
2643 -- you cannot cancel the Event Header.
2644 -- 03/11/2002 Changed the messages AMS_EVENT_NOT ACTIVE to AMS_EVENT_NOT_ACTIVE
2645 -- 17-Mar-2005 spendem Modified to raise the business event. enh # 3805347
2646 -----------------------------------------------------------------------------------
2647 PROCEDURE Update_Event_Header_Status (
2648 p_event_header_id IN NUMBER,
2649 p_new_status_id IN NUMBER,
2650 p_new_status_code IN VARCHAR2
2651 ) IS
2652 l_schedule_id NUMBER ;
2653 l_obj_version NUMBER ;
2654 l_evh_obj_ver NUMBER;
2655 l_status_code VARCHAR2(30) ;
2656 l_numOfSchedules NUMBER;
2657 l_obj_type VARCHAR2(4); -- added as per enh # 3805347
2658
2659 -- Modified the select statement of the cursor to get event_object_type, as per enh # 3805347
2660 CURSOR c_schedule IS
2661 SELECT event_offer_id , object_version_number, event_object_type
2662 FROM ams_event_offers_vl
2663 WHERE event_header_id = p_event_header_id
2664 AND system_status_code = 'AVAILABLE';
2665
2666 CURSOR c_header_obj_ver IS
2667 SELECT object_version_number
2668 FROM ams_event_headers_vl
2669 WHERE event_header_id = p_event_header_id;
2670
2671 CURSOR c_no_schedule IS
2672 SELECT count(*)
2673 FROM ams_event_offers_vl
2674 WHERE event_header_id = p_event_header_id
2675 AND system_status_code NOT IN ( 'CANCELLED', 'ARCHIVED');
2676
2677 CURSOR c_header_status IS
2678 SELECT system_status_code
2679 FROM ams_event_headers_vl
2680 WHERE event_header_id = p_event_header_id;
2681
2682
2683 BEGIN
2684
2685 /* Getting the obj_ver_num of Event Header */
2686 OPEN c_header_obj_ver;
2687 FETCH c_header_obj_ver INTO l_evh_obj_ver;
2688 IF c_header_obj_ver%NOTFOUND THEN
2689 CLOSE c_header_obj_ver;
2690 END IF;
2691
2692
2693 /* If the Event Header Status is Active, make all the schedules
2694 attached to it which are in available status as Active.
2695 If the Event Header Status is Cancelled, check whether all the schedules
2696 attached to it Cancelled. If No, you cannot cancel the Event Header.
2697 */
2698
2699 IF(p_new_status_code = 'ACTIVE') THEN
2700
2701 /* updating the Event Header */
2702 UPDATE ams_event_headers_all_b
2703 SET user_status_id = p_new_status_id,
2704 system_status_code = p_new_status_code,
2705 last_status_date = SYSDATE,
2706 object_version_number = l_evh_obj_ver + 1
2707 WHERE event_header_id = p_event_header_id;
2708
2709 /*Updating the Schedules */
2710 OPEN c_schedule;
2711 LOOP
2712 FETCH c_schedule INTO l_schedule_id, l_obj_version, l_obj_type; -- Modified the fetch, as per enh # 3805347
2713 EXIT WHEN c_schedule%NOTFOUND ;
2714
2715 UPDATE ams_event_offers_all_b
2716 SET system_status_code = 'ACTIVE',
2717 last_status_date = SYSDATE ,
2718 user_status_id = p_new_status_id,
2719 object_version_number = l_obj_version + 1
2720 WHERE event_offer_id = l_schedule_id ;
2721
2722 -- call to api to raise business event, as per enh # 3805347
2723 AMS_SCHEDULERULES_PVT.RAISE_BE_ON_STATUS_CHANGE(p_obj_id => l_schedule_id,
2724 p_obj_type => l_obj_type,
2725 p_old_status_code => 'AVAILABLE',
2726 p_new_status_code => 'ACTIVE' );
2727
2728
2729 END LOOP;
2730 CLOSE c_schedule;
2731
2732 ELSIF(p_new_status_code = 'CANCELLED') THEN
2733
2734 /* Check if all the children are Cancelled. If 'Yes' then cancel
2735 Event Header.
2736 */
2737
2738 /* Getting the obj_ver_num of Event Header */
2739 OPEN c_no_schedule;
2740 FETCH c_no_schedule INTO l_numOfSchedules;
2741 IF c_no_schedule%NOTFOUND THEN
2742 CLOSE c_no_schedule;
2743 END IF;
2744
2745 OPEN c_header_status;
2746 FETCH c_header_status INTO l_status_code;
2747 CLOSE c_header_status;
2748
2749 IF (l_numOfSchedules = 0
2750 -- AND Fnd_Api.G_TRUE = Ams_Utility_Pvt.Check_Status_Change('AMS_EVENT_STATUS',l_status_code,'CANCELLED')
2751 )
2752 THEN
2753
2754 UPDATE ams_event_headers_all_b
2755 SET user_status_id = p_new_status_id,
2756 system_status_code = p_new_status_code,
2757 last_status_date = SYSDATE,
2758 object_version_number = l_evh_obj_ver + 1
2759 WHERE event_header_id = p_event_header_id;
2760 ELSE
2761
2762 FND_MESSAGE.set_name('AMS', 'AMS_EVH_CANNOT_CANCEL');
2763 FND_MSG_PUB.add;
2764 RAISE FND_API.g_exc_error;
2765
2766 END IF;
2767
2768 ELSE
2769 /* Bug #2250937 03/05/2002*/
2770
2771 UPDATE ams_event_headers_all_b
2772 SET user_status_id = p_new_status_id,
2773 system_status_code = p_new_status_code,
2774 last_status_date = SYSDATE,
2775 object_version_number = l_evh_obj_ver + 1
2776 WHERE event_header_id = p_event_header_id;
2777 END IF;
2778
2779 END Update_Event_Header_Status;
2780
2781 ---------------------------------------------------------------------------------
2782 -- Update_Event_Schedule_Status
2783
2784 -- HISTORY
2785 -- 07-Jan-2002 gmadana Created.
2786 -- 17-Mar-2005 spendem Modified to raise the business event. enh # 3805347
2787 -----------------------------------------------------------------------------------
2788 PROCEDURE Update_Event_Schedule_Status (
2789 p_event_offer_id IN NUMBER,
2790 p_new_status_id IN NUMBER,
2791 p_new_status_code IN VARCHAR2
2792 ) IS
2793
2794 -- declare cursor for enh # 3805347
2795 CURSOR c_eve_det IS
2796 SELECT system_status_code, event_object_type
2797 FROM ams_event_offers_all_b
2798 WHERE event_offer_id = p_event_offer_id;
2799
2800 l_old_status_code VARCHAR2(30); -- added as per enh # 3805347
2801 l_obj_type VARCHAR2(4); -- added as per enh # 3805347
2802
2803 BEGIN
2804
2805 --Open cursor to fetch the old status code, as per enh # 3805347
2806 OPEN c_eve_det;
2807 FETCH c_eve_det INTO l_old_status_code, l_obj_type;
2808 CLOSE c_eve_det;
2809
2810 UPDATE ams_event_offers_all_b
2811 SET user_status_id = p_new_status_id,
2812 system_status_code = p_new_status_code,
2813 last_status_date = SYSDATE
2814 WHERE event_offer_id = p_event_offer_id;
2815
2816 -- call to api to raise business event, as per enh # 3805347
2817 AMS_SCHEDULERULES_PVT.RAISE_BE_ON_STATUS_CHANGE(p_obj_id => p_event_offer_id,
2818 p_obj_type => l_obj_type,
2819 p_old_status_code => l_old_status_code,
2820 p_new_status_code => p_new_status_code );
2821
2822
2823
2824 END Update_Event_Schedule_Status;
2825 ------------------------------------------------------------------------------------------
2826
2827
2828 --=====================================================================
2829 -- PROCEDURE
2830 -- Update_Owner
2831 --
2832 -- PURPOSE
2833 -- The api is created to update the owner of the event from the
2834 -- access table if the owner is changed in update.
2835 --
2836 -- HISTORY
2837 -- 14-Jan-2001 gmadana Created.
2838 --=====================================================================
2839 PROCEDURE Update_Owner(
2840 p_api_version IN NUMBER,
2841 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2842 p_commit IN VARCHAR2 := FND_API.g_false,
2843 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2844 x_return_status OUT NOCOPY VARCHAR2,
2845 x_msg_count OUT NOCOPY NUMBER,
2846 x_msg_data OUT NOCOPY VARCHAR2,
2847 p_object_type IN VARCHAR2 := NULL ,
2848 p_event_id IN NUMBER,
2849 p_owner_id IN NUMBER )
2850 IS
2851 CURSOR c_header_owner IS
2852 SELECT owner_user_id
2853 FROM ams_event_headers_all_b
2854 WHERE event_header_id = p_event_id ;
2855
2856 CURSOR c_offer_owner IS
2857 SELECT owner_user_id
2858 FROM ams_event_offers_all_b
2859 WHERE event_offer_id = p_event_id ;
2860
2861 l_old_owner NUMBER ;
2862
2863 BEGIN
2864
2865 IF p_object_type = 'EVEH' THEN
2866 OPEN c_header_owner ;
2867 FETCH c_header_owner INTO l_old_owner ;
2868
2869 IF c_header_owner%NOTFOUND THEN
2870 CLOSE c_header_owner;
2871 AMS_Utility_Pvt.Error_Message('AMS_API_RECORD_NOT_FOUND');
2872 RAISE FND_API.g_exc_error;
2873 ELSE
2874 CLOSE c_header_owner;
2875 END IF;
2876
2877 ELSE
2878 OPEN c_offer_owner ;
2879 FETCH c_offer_owner INTO l_old_owner ;
2880
2881 IF c_offer_owner%NOTFOUND THEN
2882 CLOSE c_offer_owner;
2883 AMS_Utility_Pvt.Error_Message('AMS_API_RECORD_NOT_FOUND');
2884 RAISE FND_API.g_exc_error;
2885 ELSE
2886 CLOSE c_offer_owner;
2887 END IF;
2888
2889 END IF;
2890
2891 IF p_owner_id <> l_old_owner THEN
2892 AMS_Access_PVT.update_object_owner(
2893 p_api_version => p_api_version,
2894 p_init_msg_list => p_init_msg_list,
2895 p_commit => p_commit,
2896 p_validation_level => p_validation_level,
2897 x_return_status => x_return_status,
2898 x_msg_count => x_msg_count,
2899 x_msg_data => x_msg_data,
2900 p_object_type => p_object_type,
2901 p_object_id => p_event_id,
2902 p_resource_id => p_owner_id,
2903 p_old_resource_id => l_old_owner
2904 );
2905
2906 IF x_return_status = FND_API.g_ret_sts_error THEN
2907 RAISE FND_API.g_exc_error;
2908 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2909 RAISE FND_API.g_exc_unexpected_error;
2910 END IF;
2911
2912 END IF ;
2913
2914 END Update_Owner ;
2915
2916 --=====================================================================
2917 -- PROCEDURE
2918 -- Send_Out_Information
2919 --
2920 -- PURPOSE
2921 -- The api is created to send information (changed) to customer
2922 -- when ever there is a change in Venue/Date/Status(Cancelled).
2923 --
2924 -- HISTORY
2925 -- 24-Apr-2002 gmadana Created.
2926 -- 18-nov-2002 soagrawa Fixed bug# 2672928
2927 -- 13-Dec-2002 ptendulk Modified the api for 1:1 integration
2928 -- 13-feb-2002 soagrawa Fixed bug# 2798626
2929 -- dbiswas missing fixes for bugs 2837977 and 2908547
2930 -- 27-may-2003 soagrawa Fixed NI Mail profile issue bug# 2978952
2931 --=====================================================================
2932 PROCEDURE Send_Out_Information(
2933 p_object_type IN VARCHAR2,
2934 p_object_id IN NUMBER ,
2935 p_trigger_type IN VARCHAR2 ,
2936 -- p_bind_values IN AMF_REQUEST.string_tbl_type , -- Modified by ptendulk for 1:1
2937 p_bind_values IN JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE,
2938 p_bind_names IN JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE,
2939 x_return_status OUT NOCOPY VARCHAR2
2940 )
2941 IS
2942 l_request_history_id NUMBER;
2943
2944 /* Following code is modified by ptendulk to move to 1:1 ffm
2945 l_email AMF_REQUEST.string_tbl_type;
2946 l_fax AMF_REQUEST.string_tbl_type;
2947 l_party_id AMF_REQUEST.number_tbl_type;
2948 */
2949
2950 l_email JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
2951 l_fax JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
2952 l_party_id JTF_FM_REQUEST_GRP.G_NUMBER_TBL_TYPE;
2953
2954
2955 l_msg_count NUMBER;
2956 l_logged_in_user_id NUMBER;
2957 l_obj_resource_id NUMBER;
2958 l_msg_data VARCHAR2(2000);
2959
2960 -- following declarations added by soagrawa on 18-nov-2002
2961 -- for bug# 2672928
2962 l_object_type VARCHAR2(10);
2963 l_object_id NUMBER;
2964 l_parent_type VARCHAR2(10);
2965 l_csch_id NUMBER;
2966
2967 -- soagrawa 03-feb-2003 bug# 2781219
2968 CURSOR c_get_parent_type IS
2969 SELECT parent_type, owner_user_id
2970 FROM ams_event_offers_all_b
2971 WHERE event_offer_id = p_object_id;
2972
2973 CURSOR c_csch_id IS
2974 SELECT schedule_id
2975 FROM ams_campaign_schedules_b
2976 WHERE related_event_id = p_object_id;
2977
2978
2979 BEGIN
2980
2981 -- l_logged_in_user_id := AMS_Utility_PVT.get_resource_id(FND_GLOBAL.USER_ID);
2982 l_logged_in_user_id := FND_GLOBAL.user_id ;
2983
2984 IF (AMS_DEBUG_HIGH_ON) THEN
2985 AMS_Utility_PVT.debug_message('Calling AMS_CT_RULE_PVT.check_content_rule');
2986 END IF;
2987
2988
2989 -- soagrawa 18-nov-2002 for bug# 2672928
2990 l_object_type := p_object_type;
2991 l_object_id := p_object_id;
2992
2993 OPEN c_get_parent_type;
2994 FETCH c_get_parent_type INTO l_parent_type, l_obj_resource_id;
2995 CLOSE c_get_parent_type;
2996
2997 IF l_object_type = 'EONE'
2998 THEN
2999 -- soagrawa 12-feb-2003 bug# 2798626 - removed from here and added out of the IF clause
3000 /*
3001 OPEN c_get_parent_type;
3002 FETCH c_get_parent_type INTO l_parent_type, l_obj_resource_id;
3003 CLOSE c_get_parent_type;
3004 */
3005 IF l_parent_type = 'CAMP'
3006 THEN
3007
3008 OPEN c_csch_id;
3009 FETCH c_csch_id INTO l_csch_id;
3010 CLOSE c_csch_id;
3011
3012 l_object_type := 'CSCH';
3013 l_object_id := l_csch_id;
3014 END IF;
3015 END IF;
3016 -- end soagrawa 18-nov-2002 for bug# 2672928
3017
3018
3019 AMS_CT_RULE_PVT.check_content_rule(
3020 p_api_version => 1.0
3021 , p_init_msg_list => FND_API.g_false
3022 , p_commit => FND_API.g_false
3023 , p_object_type => l_object_type --p_object_type
3024 , p_object_id => l_object_id -- p_object_id
3025 , p_trigger_type => p_trigger_type
3026 -- , p_requestor_type => NULL
3027 -- , p_requestor_id => l_logged_in_user_id
3028 -- soagrawa 03-feb-2003 bug# 2781219
3029 , p_requestor_id => get_user_id(l_obj_resource_id) -- l_logged_in_user_id
3030 -- , p_server_group => NULL
3031 -- , p_scheduled_date => SYSDATE
3032 -- , p_media_types => 'E'
3033 -- , p_archive => 'N'
3034 -- , p_log_user_ih => 'N'
3035 -- , p_request_type => 'MASS_CUSTOM'
3036 -- , p_language_code => NULL
3037 -- soagrawa fixed NI issue about the mail profiles 27-may-2003 commented out p_profile_id bug# 2978952
3038 -- , p_profile_id => NULL
3039 -- , p_order_id => NULL
3040 -- , p_collateral_id => NULL
3041 , p_party_id => l_party_id
3042 , p_email => l_email
3043 , p_fax => l_fax
3044 , p_bind_values => p_bind_values
3045 , p_bind_names => p_bind_names -- Added by ptendulk on 13-Dec-2002 for 1:1 integration
3046 , x_return_status => x_return_status
3047 , x_msg_count => l_msg_count
3048 , x_msg_data => l_msg_data
3049 , x_request_history_id => l_request_history_id
3050 );
3051
3052
3053 IF (AMS_DEBUG_HIGH_ON) THEN
3054 AMS_Utility_PVT.debug_message('Request ID: ' || l_request_history_id);
3055 END IF;
3056
3057 IF x_return_status = FND_API.g_ret_sts_error THEN
3058 RAISE FND_API.g_exc_error;
3059 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3060 RAISE FND_API.g_exc_unexpected_error;
3061 END IF;
3062
3063 END Send_Out_Information ;
3064
3065
3066 END AMS_EvhRules_PVT;
[Home] [Help]