[Home] [Help]
PACKAGE BODY: APPS.AMS_EVENTHEADER_PVT
Source
1 PACKAGE BODY AMS_EventHeader_PVT AS
2 /* $Header: amsvevhb.pls 120.2 2006/04/25 09:53:26 vmodur noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30):='AMS_EventHeader_PVT';
5 g_file_name CONSTANT VARCHAR2(12):='amsvevhb.pls';
6
7
8 -- Debug mode
9 -- g_debug boolean := FALSE;
10 -- g_debug boolean := TRUE;
11
12 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
13 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
14 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
15
16 PROCEDURE Check_Dates_Range (
17 p_evh_rec IN evh_rec_type,
18 x_return_status OUT NOCOPY VARCHAR2
19 );
20
21 PROCEDURE Check_Parent_Active (
22 p_evh_rec IN evh_rec_type,
23 x_return_status OUT NOCOPY VARCHAR2
24 );
25
26
27 PROCEDURE Update_Metrics (
28 p_evh_rec IN evh_rec_type,
29 x_return_status OUT NOCOPY VARCHAR2,
30 x_msg_count OUT NOCOPY VARCHAR2,
31 x_msg_data OUT NOCOPY VARCHAR2
32 );
33
34 ---------------------------------------------------------------------
35 -- PROCEDURE
36 -- create_event_header
37 --
38 -- HISTORY
39 -- 11/17/1999 gdeodhar Created.
40 -- 01/17/2000 gdeodhar Changed the procedure to pick up the
41 -- correct user_status_id and system_status_code.
42 -- Fixed the time formats.
43 -- Added code to generate source_code.
44 ---------------------------------------------------------------------
45 PROCEDURE create_event_header(
46 p_api_version IN NUMBER,
47 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
48 p_commit IN VARCHAR2 := FND_API.g_false,
49 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
50
51 x_return_status OUT NOCOPY VARCHAR2,
52 x_msg_count OUT NOCOPY NUMBER,
53 x_msg_data OUT NOCOPY VARCHAR2,
54
55 p_evh_rec IN evh_rec_type,
56 x_evh_id OUT NOCOPY NUMBER
57 )
58 IS
59
60 l_api_version CONSTANT NUMBER := 1.0;
61 l_api_name CONSTANT VARCHAR2(30) := 'create_event_header';
62 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
63
64 l_return_status VARCHAR2(1);
65 l_source_code_id NUMBER;
66 l_evh_rec evh_rec_type := p_evh_rec;
67 l_evh_count NUMBER;
68
69 l_start_time DATE;
70 l_end_time DATE;
71 l_user_id NUMBER;
72 l_res_id NUMBER;
73 l_org_id NUMBER;
74 l_ovn NUMBER(9) := 1;
75
76
77 CURSOR c_evh_seq IS
78 SELECT ams_event_headers_all_b_s.NEXTVAL
79 FROM DUAL;
80
81 CURSOR c_evh_count(evh_id IN NUMBER) IS
82 SELECT COUNT(*)
83 FROM ams_event_headers_vl
84 WHERE event_header_id = evh_id;
85
86 CURSOR c_evh_status_evagd(ust_id IN NUMBER) IS
87 SELECT system_status_code
88 FROM ams_user_statuses_b
89 WHERE user_status_id = ust_id
90 AND system_status_type = 'AMS_EVENT_AGENDA_STATUS';
91
92 CURSOR get_res_id(l_user_id IN NUMBER) IS
93 SELECT resource_id
94 FROM ams_jtf_rs_emp_v
95 WHERE user_id = l_user_id;
96
97 BEGIN
98
99 --------------------- initialize -----------------------
100 SAVEPOINT create_event_header;
101
102 IF (AMS_DEBUG_HIGH_ON) THEN
103
104
105
106 AMS_Utility_PVT.debug_message(l_full_name||': start');
107
108 END IF;
109
110 IF FND_API.to_boolean(p_init_msg_list) THEN
111 FND_MSG_PUB.initialize;
112 END IF;
113
114 IF NOT FND_API.compatible_api_call(l_api_version,
115 p_api_version,
116 l_api_name,
117 g_pkg_name
118 ) THEN
119 RAISE FND_API.g_exc_unexpected_error;
120 END IF;
121
122 x_return_status := FND_API.g_ret_sts_success;
123 --------------- calendar----------------------------
124 -- added sugupta 08/28/20000--------------
125 -- default event calendar, present;y defailting it to be same as campaigns calendar.. SHOULD CHANGE
126 -- not sure about the logic, should it be defaulted only for MAIN events, not the agenda..
127 -- IF l_evh_rec.event_calendar IS NULL THEN
128 l_evh_rec.event_calendar := FND_PROFILE.value('AMS_CAMPAIGN_DEFAULT_CALENDER');
129 -- END IF;
130
131 -- we will override any coming value of system status code
132 -- added sugupta 07/20/2000 for event agenda, stastuses shouldnt be defaulted to 1/NEW
133 -- for main event, while creation.. user status shud always be 1, system status always NEW
134 if l_evh_rec.event_level = 'MAIN' then
135 l_evh_rec.user_status_id := ams_utility_pvt.get_default_user_status('AMS_EVENT_STATUS','NEW');
136 l_evh_rec.system_status_code := 'NEW';
137 else
138 -- pick up the correct system_status_code
139 IF l_evh_rec.user_status_id IS NOT NULL THEN
140 OPEN c_evh_status_evagd(l_evh_rec.user_status_id);
141 FETCH c_evh_status_evagd INTO l_evh_rec.system_status_code;
142 CLOSE c_evh_status_evagd;
143 END IF;
144 end if;
145
146 ----------------------- validate -----------------------
147 IF (AMS_DEBUG_HIGH_ON) THEN
148
149 AMS_Utility_PVT.debug_message(l_full_name ||': validate');
150 END IF;
151
152 validate_event_header(
153 p_api_version => l_api_version,
154 p_init_msg_list => p_init_msg_list,
155 p_validation_level => p_validation_level,
156 x_return_status => l_return_status,
157 x_msg_count => x_msg_count,
158 x_msg_data => x_msg_data,
159 p_evh_rec => l_evh_rec
160 );
161
162 IF l_return_status = FND_API.g_ret_sts_error THEN
163 RAISE FND_API.g_exc_error;
164 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
165 RAISE FND_API.g_exc_unexpected_error;
166 END IF;
167 --------------- CHECK ACCESS FOR THE USER ONLY FOR EVENT AGENDA-------------------
168 ----------added sugupta 07/25/2000
169 IF l_evh_rec.event_level = 'SUB' THEN
170 l_user_id := FND_GLOBAL.User_Id;
171 IF (AMS_DEBUG_HIGH_ON) THEN
172
173 AMS_Utility_PVT.debug_message(' CHECK ACCESS l_user_id is ' ||l_user_id );
174 END IF;
175 if l_user_id IS NOT NULL then
176 open get_res_id(l_user_id);
177 fetch get_res_id into l_res_id;
178 close get_res_id;
179 end if;
180 if AMS_ACCESS_PVT.check_update_access(l_evh_rec.parent_event_header_id, 'EVEH', l_res_id, 'USER') = 'N' then
181 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
182 FND_MESSAGE.set_name('AMS', 'AMS_EVO_NO_UPDATE_ACCESS');-- reusing the message
183 FND_MSG_PUB.add;
184 END IF;
185 RAISE FND_API.g_exc_error;
186 end if;
187 ELSIF l_evh_rec.event_level = 'MAIN' THEN
188 null;
189
190 END IF;
191
192 IF l_evh_rec.event_level = 'MAIN' THEN
193 null;
194 /* Hornet :call Task creation API ThisAPI is not yet coded */
195 END IF;
196
197 -- ==========================================================
198 -- Following code is added by mukumar on 10/30/2000
199 -- the code will convert the transaction currency in to
200 -- functional currency.
201 -- ==========================================================
202
203 IF (l_evh_rec.event_level = 'MAIN' AND l_evh_rec.fund_amount_tc IS NOT NULL )THEN
204 AMS_EvhRules_PVT.Convert_Evnt_Currency(
205 p_tc_curr => l_evh_rec.currency_code_tc,
206 p_tc_amt => l_evh_rec.fund_amount_tc,
207 x_fc_curr => l_evh_rec.currency_code_fc,
208 x_fc_amt => l_evh_rec.fund_amount_fc
209 ) ;
210 END IF ;
211 -------------------------- insert --------------------------
212 IF (AMS_DEBUG_HIGH_ON) THEN
213
214 AMS_Utility_PVT.debug_message(l_full_name ||': Get Sequence');
215 END IF;
216
217 IF l_evh_rec.event_header_id IS NULL THEN
218 LOOP
219 OPEN c_evh_seq;
220 FETCH c_evh_seq INTO l_evh_rec.event_header_id;
221 CLOSE c_evh_seq;
222
223 OPEN c_evh_count(l_evh_rec.event_header_id);
224 FETCH c_evh_count INTO l_evh_count;
225 CLOSE c_evh_count;
226
227 EXIT WHEN l_evh_count = 0;
228 END LOOP;
229 END IF;
230
231
232 -- Global flag if not passed from the screen, default it as N
233 IF l_evh_rec.global_flag IS NULL THEN
234 l_evh_rec.global_flag := 'N';
235 END IF;
236
237 -- if incoming source_code is NULL, it is generated only for event_level = 'MAIN'
238 IF l_evh_rec.source_code IS NULL
239 AND l_evh_rec.event_level = 'MAIN'
240 THEN
241 -- choang - 16-May-2000
242 -- Replaced get_source_code with get_new_source_code
243 -- for internal rollout requirement #20.
244 -- MODIFIED SUGUPTA 05/30/2000 get_new_source_code FUNCTION NOT INCLUDED IN
245 -- 11.5.1.0.4 RELEASE.. SO COMMENTING OUT THIS FUNCTION CALL AND
246 -- UNCOMMENTING OLD FUNCTION CALL TO USE OLD FN. get_source_code TO GET SOURCE CODE
247 -- *****SHOULD REVERT THIS IN LATER REVISIONS TO USE NEW SOURCE CODE FUNCTIONS****
248 -- choang - 07-Jul-2000
249 -- Re-introduce get_new_source_code for internal rollout/R2
250 -- NOTE: need to implement global flag.
251 l_evh_rec.source_code := AMS_SourceCode_PVT.get_new_source_code (
252 p_object_type => 'EVEH',
253 p_custsetup_id => l_evh_rec.custom_setup_id,
254 p_global_flag => l_evh_rec.global_flag
255 );
256 --l_evh_rec.source_code := AMS_SourceCode_PVT.get_source_code(
257 -- 'EVEH',
258 -- l_evh_rec.event_type_code
259 -- );
260 END IF;
261
262 IF (AMS_DEBUG_HIGH_ON) THEN
263
264
265
266 AMS_Utility_PVT.debug_message(l_full_name ||': Source Code');
267
268 END IF;
269
270 -- convert incoming time entries appropriately.
271 IF l_evh_rec.agenda_start_time IS NOT NULL THEN
272 l_start_time := to_date(to_char(l_evh_rec.agenda_start_time, 'HH24:MI'),'HH24:MI');
273 l_evh_rec.agenda_start_time := l_start_time;
274 END IF;
275 IF l_evh_rec.agenda_end_time IS NOT NULL THEN
276 l_end_time := to_date(to_char(l_evh_rec.agenda_end_time, 'HH24:MI'),'HH24:MI');
277 l_evh_rec.agenda_end_time := l_end_time;
278 END IF;
279
280 /* Code Added By GMadana for date/time validation for Agendas*/
281 IF l_start_time > l_end_time THEN
282 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error)
283 THEN
284 Fnd_Message.set_name('AMS', 'AMS_EVO_START_TM_GT_END_TM'); -- reusing EVEO message
285 Fnd_Msg_Pub.ADD;
286 END IF;
287 x_return_status := Fnd_Api.g_ret_sts_error;
288 RAISE Fnd_Api.g_exc_error;
289 END IF; -- st tm > end tm
290
291 /* Code Added by GMADANA for Date validation for attaching Program as Parent */
292 /* Check_Dates_Range has date validation for MAIN level as agenda for Event Header
293 has no dates on the GUI.
294 */
295
296 IF (p_evh_rec.event_level = 'MAIN') THEN
297 Check_Dates_Range(
298 p_evh_rec => p_evh_rec,
299 x_return_status => x_return_status
300 );
301
302 IF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
303 RAISE Fnd_Api.g_exc_unexpected_error;
304 ELSIF x_return_status = Fnd_Api.g_ret_sts_error THEN
305 RAISE Fnd_Api.g_exc_error;
306 END IF;
307 END IF;
308
309
310
311 -------------------------- insert --------------------------
312 IF (AMS_DEBUG_HIGH_ON) THEN
313
314 AMS_Utility_PVT.debug_message(l_full_name ||': insert B');
315 END IF;
316 IF (AMS_DEBUG_HIGH_ON) THEN
317
318 AMS_Utility_PVT.debug_message(l_full_name ||': insert B1'||l_evh_rec.agenda_start_time||','||l_evh_rec.agenda_end_time);
319 END IF;
320 IF (AMS_DEBUG_HIGH_ON) THEN
321
322 AMS_Utility_PVT.debug_message(l_full_name ||': insert B2'||l_evh_rec.active_from_date||','||l_evh_rec.active_to_date);
323 END IF;
324
325 -- Added by rmajumda (09/15/05). MOAC changes
326 l_org_id := fnd_profile.value('DEFAULT_ORG_ID');
327
328 IF l_evh_rec.object_version_number = 2 THEN -- copy
329 l_ovn := 2;
330 END IF;
331
332
333 INSERT INTO ams_event_headers_all_b(
334 event_header_id
335 ,setup_type_id
336 ,last_update_date
337 ,last_updated_by
338 ,creation_date
339 ,created_by
340 ,last_update_login
341 ,object_version_number
342 ,event_level
343 ,application_id
344 ,event_type_code
345 ,active_flag
346 ,private_flag
347 ,user_status_id
348 ,system_status_code
349 ,last_status_date
350 ,stream_type_code
351 ,source_code
352 ,event_standalone_flag
353 ,day_of_event
354 ,agenda_start_time
355 ,agenda_end_time
356 ,reg_required_flag
357 ,reg_charge_flag
358 ,reg_invited_only_flag
359 ,partner_flag
360 ,overflow_flag
361 ,parent_event_header_id
362 ,duration
363 ,duration_uom_code
364 ,active_from_date
365 ,active_to_date
366 ,reg_maximum_capacity
367 ,reg_minimum_capacity
368 ,main_language_code
369 ,cert_credit_type_code
370 ,certification_credits
371 ,inventory_item_id
372 ,organization_id
373 ,org_id
374 ,forecasted_revenue
375 ,actual_revenue
376 ,forecasted_cost
377 ,actual_cost
378 ,coordinator_id
379 ,fund_source_type_code
380 ,fund_source_id
381 ,fund_amount_tc
382 ,fund_amount_fc
383 ,currency_code_tc
384 ,currency_code_fc
385 ,owner_user_id
386 ,url
387 ,phone
388 ,email
389 ,priority_type_code
390 ,cancellation_reason_code
391 ,inbound_script_name
392 ,attribute_category
393 ,attribute1
394 ,attribute2
395 ,attribute3
396 ,attribute4
397 ,attribute5
398 ,attribute6
399 ,attribute7
400 ,attribute8
401 ,attribute9
402 ,attribute10
403 ,attribute11
404 ,attribute12
405 ,attribute13
406 ,attribute14
407 ,attribute15
408 ,country_code
409 ,business_unit_id
410 ,event_calendar
411 ,start_period_name
412 ,end_period_name
413 ,global_flag
414 ,task_id
415 ,program_id
416 ,CREATE_ATTENDANT_LEAD_FLAG /*hornet*/
417 ,CREATE_REGISTRANT_LEAD_FLAG /*hornet*/
418 ,EVENT_PURPOSE_CODE /*hornet*/
419 )
420 VALUES(
421 l_evh_rec.event_header_id,
422 l_evh_rec.custom_setup_id,
423 SYSDATE,
424 FND_GLOBAL.user_id,
425 SYSDATE,
426 FND_GLOBAL.user_id,
427 FND_GLOBAL.conc_login_id,
428 l_ovn,
429 l_evh_rec.event_level, -- MAIN (event header), SUB (agenda item)
430 -- Level will be sent by the UI.
431 l_evh_rec.application_id,
432 l_evh_rec.event_type_code,
433 NVL(l_evh_rec.active_flag, 'Y'), -- it is set to Y if it is null.
434 NVL(l_evh_rec.private_flag,'N'), -- Value will come from the User Interface.
435 l_evh_rec.user_status_id, -- This is defaulted to 1 for level=MAIN
436 l_evh_rec.system_status_code, -- This is defaulted to 'NEW' for level=MAIN
437 NVL(l_evh_rec.last_status_date,SYSDATE),
438 l_evh_rec.stream_type_code,
439 l_evh_rec.source_code, -- If the incoming value is NULL, it is generated.
440 NVL(l_evh_rec.event_standalone_flag,'N'), -- Value will come from the User Interface.
441 l_evh_rec.day_of_event,
442 l_evh_rec.agenda_start_time, -- This is converted appropriately
443 l_evh_rec.agenda_end_time, -- This is converted appropriately
444 NVL(l_evh_rec.reg_required_flag,'Y'), -- Value will come from the UI.
445 NVL(l_evh_rec.reg_charge_flag,'Y'), -- Value will come from the UI.
446 NVL(l_evh_rec.reg_invited_only_flag,'N'), -- Value will come from the UI.
447 NVL(l_evh_rec.partner_flag,'N'),
448 NVL(l_evh_rec.overflow_flag,'N'), -- Value will come from the UI.
449 l_evh_rec.parent_event_header_id,
450 l_evh_rec.duration,
451 l_evh_rec.duration_uom_code,
452 l_evh_rec.active_from_date,
453 l_evh_rec.active_to_date,
454 l_evh_rec.reg_maximum_capacity,
455 l_evh_rec.reg_minimum_capacity,
456 l_evh_rec.main_language_code,
457 l_evh_rec.cert_credit_type_code,
458 l_evh_rec.certification_credits,
459 l_evh_rec.inventory_item_id,
460 l_evh_rec.organization_id,
461 --TO_NUMBER(SUBSTRB(userenv('CLIENT_INFO'),1,10)), -- org_id
462 l_org_id,
463 l_evh_rec.forecasted_revenue,
464 l_evh_rec.actual_revenue,
465 l_evh_rec.forecasted_cost,
466 l_evh_rec.actual_cost,
467 l_evh_rec.coordinator_id,
468 l_evh_rec.fund_source_type_code,
469 l_evh_rec.fund_source_id,
470 l_evh_rec.fund_amount_tc,
471 l_evh_rec.fund_amount_fc,
472 l_evh_rec.currency_code_tc,
473 l_evh_rec.currency_code_fc,
474 l_evh_rec.owner_user_id,
475 l_evh_rec.url,
476 l_evh_rec.phone,
477 l_evh_rec.email,
478 l_evh_rec.priority_type_code,
479 l_evh_rec.cancellation_reason_code,
480 l_evh_rec.inbound_script_name,
481 l_evh_rec.attribute_category,
482 l_evh_rec.attribute1,
483 l_evh_rec.attribute2,
484 l_evh_rec.attribute3,
485 l_evh_rec.attribute4,
486 l_evh_rec.attribute5,
487 l_evh_rec.attribute6,
488 l_evh_rec.attribute7,
489 l_evh_rec.attribute8,
490 l_evh_rec.attribute9,
491 l_evh_rec.attribute10,
492 l_evh_rec.attribute11,
493 l_evh_rec.attribute12,
494 l_evh_rec.attribute13,
495 l_evh_rec.attribute14,
496 l_evh_rec.attribute15,
497 -- l_evh_rec.country_code,
498 -- The above will require the JSP to send the country_code as part of the rec.
499 -- This is not needed the API can pick it up as follows:
500 NVL(l_evh_rec.country_code, TO_NUMBER(FND_PROFILE.value('AMS_SRCGEN_USER_CITY'))),
501 -- The above picks up the country code from the Profile option if the one sent in
502 -- by the JSP page is null.
503 l_evh_rec.business_unit_id,
504 -- The JSPs are expected to send the value of the business_unit_id. It is nullable.
505 l_evh_rec.event_calendar,
506 l_evh_rec.start_period_name,
507 l_evh_rec.end_period_name,
508 nvl(l_evh_rec.global_flag, 'N'),
509 --above 4 fields added to be in synch with campaigns
510 l_evh_rec.task_id, /*hornet create Taskid */
511 l_evh_rec.program_id /*hornet create Taskid */
512 ,l_evh_rec.CREATE_ATTENDANT_LEAD_FLAG /*hornet*/
513 ,l_evh_rec.CREATE_REGISTRANT_LEAD_FLAG /*hornet*/
514 ,l_evh_rec.EVENT_PURPOSE_CODE /*hornet*/
515 );
516
517 IF (AMS_DEBUG_HIGH_ON) THEN
518
519
520
521 AMS_Utility_PVT.debug_message(l_full_name ||': insert TL');
522
523 END IF;
524
525 INSERT INTO ams_event_headers_all_tl(
526 event_header_id,
527 language,
528 last_update_date,
529 last_updated_by,
530 creation_date,
531 created_by,
532 last_update_login,
533 source_lang,
534 event_header_name,
535 event_mktg_message,
536 description
537 )
538 SELECT
539 l_evh_rec.event_header_id,
540 l.language_code,
541 SYSDATE,
542 FND_GLOBAL.user_id,
543 SYSDATE,
544 FND_GLOBAL.user_id,
545 FND_GLOBAL.conc_login_id,
546 USERENV('LANG'),
547 l_evh_rec.event_header_name,
548 l_evh_rec.event_mktg_message,
549 l_evh_rec.description
550 FROM fnd_languages l
551 WHERE l.installed_flag in ('I', 'B')
552 AND NOT EXISTS(
553 SELECT NULL
554 FROM ams_event_headers_all_tl t
555 WHERE t.event_header_id = l_evh_rec.event_header_id
556 AND t.language = l.language_code );
557
558
559
560 -- added by murali on may/2001
561 -- create obj attributes for newly created master event
562 -- Should do it only for 'MAIN' event level
563 IF l_evh_rec.event_level = 'MAIN' THEN
564 AMS_EvhRules_PVT.push_source_code(
565 l_evh_rec.source_code,
566 'EVEH',
567 l_evh_rec.event_header_id
568 );
569 END IF;
570 -- The AMS_SourceCode_PVT takes care of inserting the newly generated
571 -- Source Code in ams_source_codes table.
572 IF l_evh_rec.event_level = 'MAIN' THEN
573 -- attach seeded metrics
574 AMS_RefreshMetric_PVT.copy_seeded_metric(
575 p_api_version => l_api_version,
576 x_return_status => l_return_status,
577 x_msg_count => x_msg_count,
578 x_msg_data => x_msg_data,
579 p_arc_act_metric_used_by =>'EVEH',
580 p_act_metric_used_by_id => l_evh_rec.event_header_id,
581 p_act_metric_used_by_type => NULL
582 );
583 IF l_return_status = FND_API.g_ret_sts_error THEN
584 RAISE FND_API.g_exc_error;
585 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
586 RAISE FND_API.g_exc_unexpected_error;
587 END IF;
588 IF (AMS_DEBUG_HIGH_ON) THEN
589
590 AMS_Utility_PVT.debug_message('Before Add_Update_Access_record');
591 END IF;
592 AMS_EvhRules_PVT.Add_Update_Access_record(p_object_type => 'EVEH',
593 p_object_id => l_evh_rec.event_header_id,
594 p_Owner_user_id => l_evh_rec.owner_user_id,
595 x_return_status => l_return_status,
596 x_msg_count => x_msg_count,
597 x_msg_data => x_msg_data);
598 IF (AMS_DEBUG_HIGH_ON) THEN
599
600 AMS_Utility_PVT.debug_message('After Add_Update_Access_record' || l_return_status);
601 END IF;
602 END IF; -- check for event level MAIN
603
604 ------------------------- finish -------------------------------
605 x_evh_id := l_evh_rec.event_header_id;
606
607 IF FND_API.to_boolean(p_commit) THEN
608 COMMIT;
609 END IF;
610
611 FND_MSG_PUB.count_and_get(
612 p_encoded => FND_API.g_false,
613 p_count => x_msg_count,
614 p_data => x_msg_data
615 );
616
617 IF (AMS_DEBUG_HIGH_ON) THEN
618
619
620
621 AMS_Utility_PVT.debug_message(l_full_name ||': end');
622
623 END IF;
624
625 EXCEPTION
626
627 WHEN FND_API.g_exc_error THEN
628 ROLLBACK TO create_event_header;
629 x_return_status := FND_API.g_ret_sts_error;
630 FND_MSG_PUB.count_and_get(
631 p_encoded => FND_API.g_false,
632 p_count => x_msg_count,
633 p_data => x_msg_data
634 );
635
636 WHEN FND_API.g_exc_unexpected_error THEN
637 ROLLBACK TO create_event_header;
638 x_return_status := FND_API.g_ret_sts_unexp_error ;
639 FND_MSG_PUB.count_and_get(
640 p_encoded => FND_API.g_false,
641 p_count => x_msg_count,
642 p_data => x_msg_data
643 );
644
645
646 WHEN OTHERS THEN
647 ROLLBACK TO create_event_header;
648 x_return_status := FND_API.g_ret_sts_unexp_error ;
649
650 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
651 THEN
652 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
653 END IF;
654
655 FND_MSG_PUB.count_and_get(
656 p_encoded => FND_API.g_false,
657 p_count => x_msg_count,
658 p_data => x_msg_data
659 );
660
661 END create_event_header;
662
663
664 ---------------------------------------------------------------
665 -- PROCEDURE
666 -- delete_event_header
667 --
668 -- HISTORY
669 -- 11/17/1999 gdeodhar Created.
670 ---------------------------------------------------------------
671 PROCEDURE delete_event_header(
672 p_api_version IN NUMBER,
673 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
674 p_commit IN VARCHAR2 := FND_API.g_false,
675
676 x_return_status OUT NOCOPY VARCHAR2,
677 x_msg_count OUT NOCOPY NUMBER,
678 x_msg_data OUT NOCOPY VARCHAR2,
679
680 p_evh_id IN NUMBER,
681 p_object_version IN NUMBER
682 )
683 IS
684
685 l_api_version CONSTANT NUMBER := 1.0;
686 l_api_name CONSTANT VARCHAR2(30) := 'delete_event_header';
687 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
688 l_hdr_id NUMBER;
689 l_level VARCHAR2(30);
690 l_user_id NUMBER;
691 l_res_id NUMBER;
692 CURSOR get_res_id(l_user_id IN NUMBER) IS
693 SELECT resource_id
694 FROM ams_jtf_rs_emp_v
695 WHERE user_id = l_user_id;
696
697 CURSOR get_parent_header_info(l_evh_id IN NUMBER) IS
698 SELECT event_level,parent_event_header_id
699 FROM ams_event_headers_all_b
700 WHERE event_header_id = l_evh_id
701 and event_level = 'SUB';
702
703 BEGIN
704
705 --------------------- initialize -----------------------
706 SAVEPOINT delete_event_header;
707
708 IF (AMS_DEBUG_HIGH_ON) THEN
709
710
711
712 AMS_Utility_PVT.debug_message(l_full_name||': start');
713
714 END IF;
715
716 IF FND_API.to_boolean(p_init_msg_list) THEN
717 FND_MSG_PUB.initialize;
718 END IF;
719
720 IF NOT FND_API.compatible_api_call(
721 l_api_version,
722 p_api_version,
723 l_api_name,
724 g_pkg_name
725 ) THEN
726 RAISE FND_API.g_exc_unexpected_error;
727 END IF;
728
729 x_return_status := FND_API.G_RET_STS_SUCCESS;
730 --------------- CHECK ACCESS FOR THE USER-------------------
731 ----------added sugupta 07/25/2000
732 l_user_id := FND_GLOBAL.User_Id;
733 IF (AMS_DEBUG_HIGH_ON) THEN
734
735 AMS_Utility_PVT.debug_message(' CHECK ACCESS l_user_id is ' ||l_user_id );
736 END IF;
737 if l_user_id IS NOT NULL then
738 open get_res_id(l_user_id);
739 fetch get_res_id into l_res_id;
740 close get_res_id;
741 end if;
742 open get_parent_header_info(p_evh_id);
743 fetch get_parent_header_info into l_level, l_hdr_id;
744 close get_parent_header_info;
745 IF (l_level <> 'SUB' OR l_level is NULL) THEN
746 l_hdr_id := p_evh_id;
747 END IF;
748 IF (AMS_DEBUG_HIGH_ON) THEN
749
750 AMS_Utility_PVT.debug_message('l_hdr_id:'||l_hdr_id || 'P_evh_id:' || p_evh_id || 'l_res_id:' || l_res_id);
751 END IF;
752 if AMS_ACCESS_PVT.check_update_access(l_hdr_id, 'EVEH', l_res_id, 'USER') = 'N' then
753 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
754 FND_MESSAGE.set_name('AMS', 'AMS_EVO_NO_UPDATE_ACCESS'); --reusing the message
755 FND_MSG_PUB.add;
756 END IF;
757 RAISE FND_API.g_exc_error;
758 end if;
759 ------------------------ delete ------------------------
760 IF (AMS_DEBUG_HIGH_ON) THEN
761
762 AMS_Utility_PVT.debug_message(l_full_name ||': delete');
763 END IF;
764
765 UPDATE ams_event_headers_all_b
766 SET active_flag = 'N'
767 WHERE event_header_id = p_evh_id
768 AND object_version_number = p_object_version;
769
770 IF (SQL%NOTFOUND) THEN
771 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
772 THEN
773 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
774 FND_MSG_PUB.add;
775 END IF;
776 RAISE FND_API.g_exc_error;
777 END IF;
778 /*
779 DELETE FROM ams_event_headers_all_tl
780 WHERE event_header_id = p_evh_id;
781 */
782 IF (SQL%NOTFOUND) THEN
783 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
784 THEN
785 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
786 FND_MSG_PUB.add;
787 END IF;
788 RAISE FND_API.g_exc_error;
789 END IF;
790
791 -------------------- finish --------------------------
792 IF FND_API.to_boolean(p_commit) THEN
793 COMMIT;
794 END IF;
795
796 FND_MSG_PUB.count_and_get(
797 p_encoded => FND_API.g_false,
798 p_count => x_msg_count,
799 p_data => x_msg_data
800 );
801
802 IF (AMS_DEBUG_HIGH_ON) THEN
803
804
805
806 AMS_Utility_PVT.debug_message(l_full_name ||': end');
807
808 END IF;
809
810 EXCEPTION
811
812 WHEN FND_API.g_exc_error THEN
813 ROLLBACK TO delete_event_header;
814 x_return_status := FND_API.g_ret_sts_error;
815 FND_MSG_PUB.count_and_get(
816 p_encoded => FND_API.g_false,
817 p_count => x_msg_count,
818 p_data => x_msg_data
819 );
820
821 WHEN FND_API.g_exc_unexpected_error THEN
822 ROLLBACK TO delete_event_header;
823 x_return_status := FND_API.g_ret_sts_unexp_error ;
824 FND_MSG_PUB.count_and_get(
825 p_encoded => FND_API.g_false,
826 p_count => x_msg_count,
827 p_data => x_msg_data
828 );
829
830 WHEN OTHERS THEN
831 ROLLBACK TO delete_event_header;
832 x_return_status := FND_API.g_ret_sts_unexp_error ;
833
834 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
835 THEN
836 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
837 END IF;
838
839 FND_MSG_PUB.count_and_get(
840 p_encoded => FND_API.g_false,
841 p_count => x_msg_count,
842 p_data => x_msg_data
843 );
844
845 END delete_event_header;
846
847
848 -------------------------------------------------------------------
849 -- PROCEDURE
850 -- lock_event_header
851 --
852 -- HISTORY
853 -- 11/17/1999 GDEODHAR Created
854 --------------------------------------------------------------------
855 PROCEDURE lock_event_header(
856 p_api_version IN NUMBER,
857 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
858
859 x_return_status OUT NOCOPY VARCHAR2,
860 x_msg_count OUT NOCOPY NUMBER,
861 x_msg_data OUT NOCOPY VARCHAR2,
862
863 p_evh_id IN NUMBER,
864 p_object_version IN NUMBER
865 )
866 IS
867
868 l_api_version CONSTANT NUMBER := 1.0;
869 l_api_name CONSTANT VARCHAR2(30) := 'lock_event_header';
870 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
871
872 l_evh_id NUMBER;
873
874 CURSOR c_evh_b IS
875 SELECT event_header_id
876 FROM ams_event_headers_all_b
877 WHERE event_header_id = p_evh_id
878 AND object_version_number = p_object_version
879 FOR UPDATE OF event_header_id NOWAIT;
880
881 CURSOR c_evh_tl IS
882 SELECT event_header_id
883 FROM ams_event_headers_all_tl
884 WHERE event_header_id = p_evh_id
885 AND USERENV('LANG') IN (language, source_lang)
886 FOR UPDATE OF event_header_id NOWAIT;
887
888 BEGIN
889
890 -------------------- initialize ------------------------
891 IF (AMS_DEBUG_HIGH_ON) THEN
892
893 AMS_Utility_PVT.debug_message(l_full_name||': start');
894 END IF;
895
896 IF FND_API.to_boolean(p_init_msg_list) THEN
897 FND_MSG_PUB.initialize;
898 END IF;
899
900 IF NOT FND_API.compatible_api_call(
901 l_api_version,
902 p_api_version,
903 l_api_name,
904 g_pkg_name
905 ) THEN
906 RAISE FND_API.g_exc_unexpected_error;
907 END IF;
908
909 x_return_status := FND_API.G_RET_STS_SUCCESS;
910
911 ------------------------ lock -------------------------
912 IF (AMS_DEBUG_HIGH_ON) THEN
913
914 AMS_Utility_PVT.debug_message(l_full_name||': lock');
915 END IF;
916
917 OPEN c_evh_b;
918 FETCH c_evh_b INTO l_evh_id;
919 IF (c_evh_b%NOTFOUND) THEN
920 CLOSE c_evh_b;
921 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
922 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
923 FND_MSG_PUB.add;
924 END IF;
925 RAISE FND_API.g_exc_error;
926 END IF;
927 CLOSE c_evh_b;
928
929 OPEN c_evh_tl;
930 CLOSE c_evh_tl;
931
932 -------------------- finish --------------------------
933 FND_MSG_PUB.count_and_get(
934 p_encoded => FND_API.g_false,
935 p_count => x_msg_count,
936 p_data => x_msg_data
937 );
938
939 IF (AMS_DEBUG_HIGH_ON) THEN
940
941
942
943 AMS_Utility_PVT.debug_message(l_full_name ||': end');
944
945 END IF;
946
947 EXCEPTION
948
949 WHEN AMS_Utility_PVT.resource_locked THEN
950 x_return_status := FND_API.g_ret_sts_error;
951 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
952 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
953 FND_MSG_PUB.add;
954 END IF;
955
956 FND_MSG_PUB.count_and_get(
957 p_encoded => FND_API.g_false,
958 p_count => x_msg_count,
959 p_data => x_msg_data
960 );
961
962 WHEN FND_API.g_exc_error THEN
963 x_return_status := FND_API.g_ret_sts_error;
964 FND_MSG_PUB.count_and_get(
965 p_encoded => FND_API.g_false,
966 p_count => x_msg_count,
967 p_data => x_msg_data
968 );
969
970 WHEN FND_API.g_exc_unexpected_error THEN
971 x_return_status := FND_API.g_ret_sts_unexp_error ;
972 FND_MSG_PUB.count_and_get(
973 p_encoded => FND_API.g_false,
974 p_count => x_msg_count,
975 p_data => x_msg_data
976 );
977
978 WHEN OTHERS THEN
979 x_return_status := FND_API.g_ret_sts_unexp_error ;
980 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
981 THEN
982 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
983 END IF;
984
985 FND_MSG_PUB.count_and_get(
986 p_encoded => FND_API.g_false,
987 p_count => x_msg_count,
988 p_data => x_msg_data
989 );
990
991 END lock_event_header;
992
993
994 ---------------------------------------------------------------------
995 -- PROCEDURE
996 -- update_event_header
997 --
998 -- HISTORY
999 -- 11/17/1999 gdeodhar Created
1000 -- 01/17/2000 gdeodhar Fixed the time formats.
1001 -- 01/21/2000 gdeodhar Added code to pick up the system_status_code
1002 -- from ams_user_statuses_b table. The UI will
1003 -- never pass this code.
1004 ----------------------------------------------------------------------
1005 PROCEDURE update_event_header(
1006 p_api_version IN NUMBER,
1007 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1008 p_commit IN VARCHAR2 := FND_API.g_false,
1009 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1010
1011 x_return_status OUT NOCOPY VARCHAR2,
1012 x_msg_count OUT NOCOPY NUMBER,
1013 x_msg_data OUT NOCOPY VARCHAR2,
1014
1015 p_evh_rec IN evh_rec_type
1016 )
1017 IS
1018
1019 l_api_version CONSTANT NUMBER := 1.0;
1020 l_api_name CONSTANT VARCHAR2(30) := 'update_event_header';
1021 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1022
1023 l_evh_rec evh_rec_type;
1024 l_global_flag VARCHAR2(25);
1025 l_source_code VARCHAR2(30);
1026 l_return_status VARCHAR2(1);
1027 l_hdr_id NUMBER;
1028 l_user_id NUMBER;
1029 l_res_id NUMBER;
1030 l_dummy NUMBER;
1031 -- added by soagrawa for bug# 2761612 21-jan-2003
1032 l_dummy_source_code VARCHAR2(30);
1033
1034 CURSOR c_evh_status_evh IS
1035 SELECT system_status_code
1036 FROM ams_user_statuses_b
1037 WHERE user_status_id = p_evh_rec.user_status_id
1038 AND system_status_type = 'AMS_EVENT_STATUS';
1039
1040 CURSOR c_evh_status_evagd IS
1041 SELECT system_status_code
1042 FROM ams_user_statuses_b
1043 WHERE user_status_id = p_evh_rec.user_status_id
1044 AND system_status_type = 'AMS_EVENT_AGENDA_STATUS';
1045
1046 CURSOR get_res_id(l_user_id IN NUMBER) IS
1047 SELECT resource_id
1048 FROM ams_jtf_rs_emp_v
1049 WHERE user_id = l_user_id;
1050
1051 CURSOR c_bdgt_line_yn(id_in IN NUMBER, objtype_in IN VARCHAR2) IS
1052 SELECT count(*)
1053 FROM OZF_ACT_BUDGETS --anchaudh: changed call from ams_act_budgets to ozf_act_budgets : bug#3453430
1054 WHERE arc_act_budget_used_by = objtype_in
1055 AND act_budget_used_by_id =id_in;
1056
1057 CURSOR c_evh IS
1058 SELECT global_flag,source_code
1059 FROM ams_event_headers_vl
1060 WHERE event_header_id = p_evh_rec.event_header_id;
1061
1062
1063 BEGIN
1064
1065 -------------------- initialize -------------------------
1066 SAVEPOINT update_event_header;
1067
1068 IF (AMS_DEBUG_HIGH_ON) THEN
1069
1070
1071
1072 AMS_Utility_PVT.debug_message(l_full_name||': start');
1073
1074 END IF;
1075
1076 IF FND_API.to_boolean(p_init_msg_list) THEN
1077 FND_MSG_PUB.initialize;
1078 END IF;
1079
1080 IF NOT FND_API.compatible_api_call(
1081 l_api_version,
1082 p_api_version,
1083 l_api_name,
1084 g_pkg_name
1085 ) THEN
1086 RAISE FND_API.g_exc_unexpected_error;
1087 END IF;
1088
1089 x_return_status := FND_API.G_RET_STS_SUCCESS;
1090
1091 ----------------------- validate ----------------------
1092 IF (AMS_DEBUG_HIGH_ON) THEN
1093
1094 AMS_Utility_PVT.debug_message(l_full_name ||': validate');
1095 END IF;
1096
1097 --
1098 -- Call the complete rec
1099 --
1100 -- replace g_miss_char/num/date with current column values
1101
1102 -- The following procedure will pick up all the fields from p_evh_rec
1103 -- and copy them to l_evh_rec.
1104 -- For all missing fields in p_evh_rec, it will pick up the current
1105 -- column values and place in those fields.
1106
1107 complete_evh_rec(p_evh_rec, l_evh_rec);
1108
1109 -- add check evh update
1110 -- check if it is needed..
1111
1112 -- Check if (budget lines are available added 06/04/2001 murali)
1113 IF (p_evh_rec.currency_code_tc <> FND_API.g_miss_char) THEN
1114 if (p_evh_rec.currency_code_tc <> nvl(l_evh_rec.currency_code_tc, '1') ) THEN
1115 OPEN c_bdgt_line_yn(l_evh_rec.event_header_id, 'EVEH');
1116 FETCH c_bdgt_line_yn INTO l_dummy;
1117 IF c_bdgt_line_yn%NOTFOUND THEN
1118 CLOSE c_bdgt_line_yn;
1119 else
1120 CLOSE c_bdgt_line_yn;
1121 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1122 FND_MESSAGE.set_name('AMS', 'AMS_EVENT_BUD_PRESENT');
1123 FND_MSG_PUB.add;
1124 END IF;
1125 RAISE FND_API.g_exc_error;
1126 END IF;
1127 END IF;
1128 END IF;
1129
1130 IF (AMS_DEBUG_HIGH_ON) THEN
1131
1132
1133
1134 AMS_Utility_PVT.debug_message(l_full_name ||':before Rules_EVH_Update');
1135
1136 END IF;
1137
1138 AMS_EvhRules_PVT.check_evh_update(
1139 p_evh_rec => p_evh_rec,
1140 x_return_status => l_return_status
1141 );
1142
1143 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1144 RAISE FND_API.g_exc_unexpected_error;
1145 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1146 RAISE FND_API.g_exc_error;
1147 END IF;
1148
1149 IF (AMS_DEBUG_HIGH_ON) THEN
1150
1151
1152
1153 AMS_Utility_PVT.debug_message(l_full_name ||':after Rules_EVH_Update');
1154
1155 END IF;
1156
1157 -- item level validation
1158
1159 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1160 check_evh_items(
1161 p_evh_rec => p_evh_rec,
1162 p_validation_mode => JTF_PLSQL_API.g_update,
1163 x_return_status => l_return_status
1164 );
1165 IF (AMS_DEBUG_HIGH_ON) THEN
1166
1167 AMS_Utility_PVT.debug_message(l_full_name ||':after check_evh_items');
1168 END IF;
1169
1170 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1171 RAISE FND_API.g_exc_unexpected_error;
1172 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1173 RAISE FND_API.g_exc_error;
1174 END IF;
1175 END IF;
1176
1177
1178 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
1179 check_evh_record(
1180 p_evh_rec => p_evh_rec,
1181 p_complete_rec => l_evh_rec,
1182 x_return_status => l_return_status
1183 );
1184
1185 IF (AMS_DEBUG_HIGH_ON) THEN
1186
1187
1188
1189 AMS_Utility_PVT.debug_message(l_full_name ||':after check_evh_record');
1190
1191 END IF;
1192
1193 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1194 RAISE FND_API.g_exc_unexpected_error;
1195 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1196 RAISE FND_API.g_exc_error;
1197 END IF;
1198 END IF;
1199
1200
1201 -- inter-entity level
1202 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_inter_entity THEN
1203 check_evh_inter_entity(
1204 p_evh_rec => p_evh_rec,
1205 p_complete_rec => l_evh_rec,
1206 p_validation_mode => JTF_PLSQL_API.g_update,
1207 x_return_status => l_return_status
1208 );
1209
1210 IF (AMS_DEBUG_HIGH_ON) THEN
1211
1212
1213
1214 AMS_Utility_PVT.debug_message(l_full_name ||':after check_evh_inter_entity');
1215
1216 END IF;
1217
1218 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1219 RAISE FND_API.g_exc_unexpected_error;
1220 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1221 RAISE FND_API.g_exc_error;
1222 END IF;
1223 END IF;
1224
1225
1226 -- Handle status
1227
1228 IF p_evh_rec.user_status_id <> FND_API.g_miss_num
1229 THEN
1230
1231 -- looks like the following procedure needs the system_status_code
1232 -- as well.
1233
1234 -- pick up the correct system_status_code first.
1235 IF l_evh_rec.event_level = 'MAIN'
1236 THEN
1237 OPEN c_evh_status_evh;
1238 FETCH c_evh_status_evh INTO l_evh_rec.system_status_code;
1239 CLOSE c_evh_status_evh;
1240 ELSIF l_evh_rec.event_level = 'SUB'
1241 THEN
1242 OPEN c_evh_status_evagd;
1243 FETCH c_evh_status_evagd INTO l_evh_rec.system_status_code;
1244 CLOSE c_evh_status_evagd;
1245 END IF;
1246
1247 -- this following procedure must check if the user tried to update
1248 -- the status id. this will be clear if the new intended user_status_id
1249 -- is different than the current user_status_id.
1250 -- this should kick the status order rules and pick up the next
1251 -- correct status id for the record.
1252 -- 07/18/2000 sugupta check for statuses done in check_evh_update..
1253 -- the api call to handle_evh_status below is redundant ..
1254
1255
1256 IF (AMS_DEBUG_HIGH_ON) THEN
1257
1258
1259
1260
1261
1262 AMS_Utility_PVT.debug_message(l_full_name ||': user_status_id = ' || l_evh_rec.user_status_id);
1263
1264
1265 END IF;
1266 IF (AMS_DEBUG_HIGH_ON) THEN
1267
1268 AMS_Utility_PVT.debug_message(l_full_name ||': system_status_code = ' || l_evh_rec.system_status_code);
1269 END IF;
1270
1271 /*
1272 -- 01/21/2000 : gdeodhar
1273 -- commented out this call as it gives AMS_EVH_BAD_USER_STATUS
1274 -- error.
1275 -- Anyway, this is not doing anything currently.
1276 -- 01/25/2000 : gdeodhar
1277 -- Ravi fixed the AMS_EvhRules_PVT.handle_evh_status.
1278 -- Testing again.
1279 -- Well, it does not compile.
1280 -- SO COMMENTED IT AGAIN.
1281 -- 03/29/00 sugupta Corrected handle_evh_status .. uncommenting call to handle_evh_status
1282 -- 07/18/2000 sugupta check for statuses done in check_evh_update..
1283 -- the api call to handle_evh_status below is redundant ..
1284
1285
1286 -- AMS_EvhRules_PVT.handle_evh_status(
1287 -- l_evh_rec.user_status_id,
1288 -- l_evh_rec.system_status_code,
1289 -- l_return_status
1290 -- );
1291
1292 -- IF l_return_status = FND_API.g_ret_sts_error THEN
1293 -- RAISE FND_API.g_exc_error;
1294 -- ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1295 -- RAISE FND_API.g_exc_unexpected_error;
1296 -- END IF;
1297 */
1298 END IF;
1299
1300 -- handle source code update
1301 IF (AMS_DEBUG_HIGH_ON) THEN
1302
1303 AMS_Utility_PVT.debug_message(l_full_name ||': update source code:'||l_evh_rec.system_status_code);
1304 END IF;
1305 -- only for main and non active events
1306
1307 /* Added by GMADANA */
1308 OPEN c_evh;
1309 FETCH c_evh INTO l_global_flag, l_source_code;
1310 CLOSE c_evh;
1311
1312 IF p_evh_rec.source_code <> FND_API.g_miss_char
1313 THEN
1314 IF p_evh_rec.source_code <> l_source_code
1315 THEN
1316 IF l_evh_rec.event_level = 'MAIN'
1317 AND l_evh_rec.system_status_code = 'NEW'
1318 -- commented by musman oct 10
1319 -- OR l_evh_rec.system_status_code = 'PLANNING') since the source code canbe update only in new status
1320 THEN
1321 IF (AMS_DEBUG_HIGH_ON) THEN
1322
1323 AMS_Utility_PVT.debug_message(l_full_name ||': update source code:'||l_evh_rec.system_status_code);
1324 END IF;
1325
1326 -- extracting out source code modified by soagrawa
1327 -- 21-jan-2003 bug# 2761612
1328 AMS_EvhRules_PVT.update_evh_source_code(
1329 l_evh_rec.event_header_id,
1330 l_evh_rec.source_code,
1331 l_evh_rec.global_flag,
1332 l_dummy_source_code,
1333 l_return_status
1334 );
1335 l_evh_rec.source_code := l_dummy_source_code;
1336 IF l_return_status = FND_API.g_ret_sts_error THEN
1337 RAISE FND_API.g_exc_error;
1338 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1339 RAISE FND_API.g_exc_unexpected_error;
1340 END IF;
1341 ELSE
1342 FND_MESSAGE.set_name('AMS', 'AMS_CAMP_UPDATE_SRC_STAT');
1343 FND_MSG_PUB.add;
1344 RAISE FND_API.g_exc_error;
1345 END IF;
1346 END IF;
1347 ELSIF p_evh_rec.source_code IS NULL /* added by musman for bug 2618242 fix*/
1348 THEN
1349 l_evh_rec.source_code := l_source_code;
1350 END IF;
1351
1352 IF p_evh_rec.global_flag <> FND_API.g_miss_char
1353 OR p_evh_rec.global_flag is NULL
1354 THEN
1355 IF (AMS_DEBUG_HIGH_ON) THEN
1356
1357 AMS_Utility_PVT.debug_message('global_flag = ' || p_evh_rec.global_flag);
1358 END IF;
1359
1360 IF p_evh_rec.global_flag <> l_global_flag
1361 THEN
1362 IF l_evh_rec.event_level = 'MAIN'
1363 AND l_evh_rec.system_status_code = 'NEW'
1364 -- commented by musman oct 10
1365 --( OR l_evh_rec.system_status_code = 'PLANNING') since the source code canbe update only in new status
1366 THEN
1367 -- extracting out source code modified by soagrawa
1368 -- 21-jan-2003 bug# 2761612
1369 AMS_EvhRules_PVT.update_evh_source_code(
1370 l_evh_rec.event_header_id,
1371 l_evh_rec.source_code,
1372 l_evh_rec.global_flag,
1373 l_dummy_source_code,
1374 l_return_status
1375 );
1376 l_evh_rec.source_code := l_dummy_source_code;
1377 IF l_return_status = FND_API.g_ret_sts_error THEN
1378 RAISE FND_API.g_exc_error;
1379 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1380 RAISE FND_API.g_exc_unexpected_error;
1381 END IF;
1382 ELSE
1383 FND_MESSAGE.set_name('AMS', 'AMS_EVNT_UPDATE_GFLG_STAT');
1384 FND_MSG_PUB.add;
1385 RAISE FND_API.g_exc_error;
1386 END IF;
1387 END IF;
1388 END IF;
1389 --------------- CHECK ACCESS FOR THE USER-------------------
1390 ----------added sugupta 07/25/2000
1391 l_user_id := FND_GLOBAL.User_Id;
1392 IF (AMS_DEBUG_HIGH_ON) THEN
1393
1394 AMS_Utility_PVT.debug_message(' CHECK ACCESS l_user_id is ' ||l_user_id );
1395 END IF;
1396 if l_user_id IS NOT NULL then
1397 open get_res_id(l_user_id);
1398 fetch get_res_id into l_res_id;
1399 close get_res_id;
1400 end if;
1401 IF l_evh_rec.event_level = 'SUB' THEN
1402 l_hdr_id := l_evh_rec.parent_event_header_id;
1403 ELSE
1404 l_hdr_id := l_evh_rec.event_header_id;
1405 END IF;
1406
1407 if AMS_ACCESS_PVT.check_update_access(l_hdr_id, 'EVEH', l_res_id, 'USER') = 'N' then
1408 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1409 THEN
1410 FND_MESSAGE.set_name('AMS', 'AMS_EVO_NO_UPDATE_ACCESS');-- reusing the message
1411 FND_MSG_PUB.add;
1412 END IF;
1413 RAISE FND_API.g_exc_error;
1414 end if;
1415
1416 -- ==========================================================
1417 -- Following code is added by mukumar on 10/30/2000
1418 -- the code will convert the transaction currency in to
1419 -- functional currency.
1420 -- ==========================================================
1421 IF p_evh_rec.fund_amount_tc IS NOT NULL THEN
1422 IF p_evh_rec.fund_amount_tc <> FND_API.g_miss_num THEN
1423 AMS_EvhRules_PVT.Convert_Evnt_Currency(
1424 p_tc_curr => l_evh_rec.currency_code_tc,
1425 p_tc_amt => l_evh_rec.fund_amount_tc,
1426 x_fc_curr => l_evh_rec.currency_code_fc,
1427 x_fc_amt => l_evh_rec.fund_amount_fc
1428 ) ;
1429 END IF ;
1430 ELSE
1431 l_evh_rec.fund_amount_fc := null ;
1432 END IF;
1433
1434 /* Code Added by GMADANA for checking whether parent is active or not */
1435
1436 Check_Parent_Active(
1437 p_evh_rec => l_evh_rec,
1438 x_return_status => x_return_status
1439 );
1440
1441 IF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1442 RAISE Fnd_Api.g_exc_unexpected_error;
1443 ELSIF x_return_status = Fnd_Api.g_ret_sts_error THEN
1444 RAISE Fnd_Api.g_exc_error;
1445 END IF;
1446
1447
1448 /* Code Added by GMADANA for Date validation for attaching Program as Parent */
1449 /* Check_Dates_Range has date validation for MAIN level as agenda for Event Header
1450 has no dates on the GUI.
1451 */
1452
1453 IF (l_evh_rec.event_level = 'MAIN') THEN
1454 Check_Dates_Range(
1455 p_evh_rec => l_evh_rec,
1456 x_return_status => x_return_status
1457 );
1458
1459 IF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1460 RAISE Fnd_Api.g_exc_unexpected_error;
1461 ELSIF x_return_status = Fnd_Api.g_ret_sts_error THEN
1462 RAISE Fnd_Api.g_exc_error;
1463 END IF;
1464 END IF;
1465
1466 /* Call to Metrics If Progam name has chnaged */
1467 Update_Metrics (
1468 p_evh_rec => l_evh_rec,
1469 x_return_status => x_return_status,
1470 x_msg_count => x_msg_count,
1471 x_msg_data =>x_msg_data
1472 );
1473
1474 IF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1475 RAISE Fnd_Api.g_exc_unexpected_error;
1476 ELSIF x_return_status = Fnd_Api.g_ret_sts_error THEN
1477 RAISE Fnd_Api.g_exc_error;
1478 END IF;
1479
1480
1481 /* If the owner user id cahnges call AMS_EvhRules_PVT.Update_Owner */
1482 -- Change the owner in Access table if the owner is changed.
1483
1484 IF p_evh_rec.owner_user_id <> FND_API.g_miss_num
1485 THEN
1486 AMS_EvhRules_PVT.Update_Owner(
1487 p_api_version => p_api_version,
1488 p_init_msg_list => p_init_msg_list,
1489 p_commit => p_commit,
1490 p_validation_level => p_validation_level,
1491 x_return_status => l_return_status,
1492 x_msg_count => x_msg_count,
1493 x_msg_data => x_msg_data,
1494 p_object_type => 'EVEH',
1495 p_event_id => l_evh_rec.event_header_id,
1496 p_owner_id => p_evh_rec.owner_user_id
1497 );
1498
1499 IF l_return_status = FND_API.g_ret_sts_error THEN
1500 RAISE FND_API.g_exc_error;
1501 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1502 RAISE FND_API.g_exc_unexpected_error;
1503 END IF;
1504 END IF ;
1505
1506 -------------------------- update --------------------
1507 IF (AMS_DEBUG_HIGH_ON) THEN
1508
1509 AMS_Utility_PVT.debug_message(l_full_name ||': update');
1510 END IF;
1511
1512 -- GDEODHAR : Sept. 26, 2000 added two separate update statements.
1513 -- One for the main events where the workflow has to be kicked off for status change
1514 -- and hence the update of the base table should not update the status related fields.
1515 -- The other update statement is needed for the Agenda items for which the status change
1516 -- is straight-forward.
1517
1518 IF l_evh_rec.event_level = 'MAIN' THEN
1519
1520 UPDATE ams_event_headers_all_b SET
1521 last_update_date = SYSDATE,
1522 last_updated_by = FND_GLOBAL.user_id,
1523 last_update_login = FND_GLOBAL.conc_login_id,
1524 object_version_number = l_evh_rec.object_version_number + 1,
1525 application_id = l_evh_rec.application_id,
1526 event_type_code = l_evh_rec.event_type_code,
1527 source_code = l_evh_rec.source_code,
1528 active_flag = l_evh_rec.active_flag,
1529 private_flag = l_evh_rec.private_flag,
1530 stream_type_code = l_evh_rec.stream_type_code,
1531 event_standalone_flag = l_evh_rec.event_standalone_flag,
1532 day_of_event = l_evh_rec.day_of_event,
1533 agenda_start_time = to_date((to_char(l_evh_rec.agenda_start_time,'HH24:MI')),'HH24:MI'),
1534 agenda_end_time = to_date((to_char(l_evh_rec.agenda_end_time,'HH24:MI')),'HH24:MI'),
1535 reg_required_flag = l_evh_rec.reg_required_flag,
1536 reg_charge_flag = l_evh_rec.reg_charge_flag,
1537 reg_invited_only_flag = l_evh_rec.reg_invited_only_flag,
1538 partner_flag = l_evh_rec.partner_flag,
1539 overflow_flag = l_evh_rec.overflow_flag,
1540 parent_event_header_id = l_evh_rec.parent_event_header_id,
1541 duration = l_evh_rec.duration,
1542 duration_uom_code = l_evh_rec.duration_uom_code,
1543 active_from_date = l_evh_rec.active_from_date,
1544 active_to_date = l_evh_rec.active_to_date,
1545 reg_maximum_capacity = l_evh_rec.reg_maximum_capacity,
1546 reg_minimum_capacity = l_evh_rec.reg_minimum_capacity,
1547 main_language_code = l_evh_rec.main_language_code,
1548 cert_credit_type_code = l_evh_rec.cert_credit_type_code,
1549 certification_credits = l_evh_rec.certification_credits,
1550 organization_id = l_evh_rec.organization_id, -- check if update is allowed on this field.
1551 inventory_item_id = l_evh_rec.inventory_item_id, -- check if update is allowed on this field.
1552 forecasted_revenue = l_evh_rec.forecasted_revenue,
1553 actual_revenue = l_evh_rec.actual_revenue,
1554 forecasted_cost = l_evh_rec.forecasted_cost,
1555 actual_cost = l_evh_rec.actual_cost,
1556 coordinator_id = l_evh_rec.coordinator_id,
1557 fund_source_type_code = l_evh_rec.fund_source_type_code,
1558 fund_source_id = l_evh_rec.fund_source_id,
1559 fund_amount_tc = l_evh_rec.fund_amount_tc,
1560 fund_amount_fc = l_evh_rec.fund_amount_fc,
1561 currency_code_tc = l_evh_rec.currency_code_tc,
1562 currency_code_fc = l_evh_rec.currency_code_fc,
1563 owner_user_id = l_evh_rec.owner_user_id,
1564 url = l_evh_rec.url,
1565 phone = l_evh_rec.phone,
1566 email = l_evh_rec.email,
1567 priority_type_code = l_evh_rec.priority_type_code,
1568 cancellation_reason_code = l_evh_rec.cancellation_reason_code,
1569 inbound_script_name = l_evh_rec.inbound_script_name,
1570 attribute_category = l_evh_rec.attribute_category,
1571 attribute1 = l_evh_rec.attribute1,
1572 attribute2 = l_evh_rec.attribute2,
1573 attribute3 = l_evh_rec.attribute3,
1574 attribute4 = l_evh_rec.attribute4,
1575 attribute5 = l_evh_rec.attribute5,
1576 attribute6 = l_evh_rec.attribute6,
1577 attribute7 = l_evh_rec.attribute7,
1578 attribute8 = l_evh_rec.attribute8,
1579 attribute9 = l_evh_rec.attribute9,
1580 attribute10 = l_evh_rec.attribute10,
1581 attribute11 = l_evh_rec.attribute11,
1582 attribute12 = l_evh_rec.attribute12,
1583 attribute13 = l_evh_rec.attribute13,
1584 attribute14 = l_evh_rec.attribute14,
1585 attribute15 = l_evh_rec.attribute15,
1586 country_code = l_evh_rec.country_code,
1587 business_unit_id = l_evh_rec.business_unit_id,
1588 event_calendar = l_evh_rec.event_calendar,
1589 start_period_name = l_evh_rec.start_period_name,
1590 end_period_name = l_evh_rec.end_period_name,
1591 global_flag = nvl(l_evh_rec.global_flag, 'N'),
1592 task_id = l_evh_rec.task_id,
1593 program_id = l_evh_rec.program_id
1594 ,CREATE_ATTENDANT_LEAD_FLAG = l_evh_rec.CREATE_ATTENDANT_LEAD_FLAG /*hornet*/
1595 ,CREATE_REGISTRANT_LEAD_FLAG = l_evh_rec.CREATE_REGISTRANT_LEAD_FLAG /*hornet*/
1596 ,EVENT_PURPOSE_CODE = l_evh_rec.EVENT_PURPOSE_CODE /*hornet*/
1597 WHERE event_header_id = l_evh_rec.event_header_id
1598 AND object_version_number = l_evh_rec.object_version_number;
1599
1600 ELSIF l_evh_rec.event_level = 'SUB' THEN
1601
1602 UPDATE ams_event_headers_all_b SET
1603 last_update_date = SYSDATE,
1604 last_updated_by = FND_GLOBAL.user_id,
1605 last_update_login = FND_GLOBAL.conc_login_id,
1606 object_version_number = l_evh_rec.object_version_number + 1,
1607 application_id = l_evh_rec.application_id,
1608 event_type_code = l_evh_rec.event_type_code,
1609 source_code = l_evh_rec.source_code,
1610 active_flag = l_evh_rec.active_flag,
1611 private_flag = l_evh_rec.private_flag,
1612 user_status_id = l_evh_rec.user_status_id,
1613 system_status_code = l_evh_rec.system_status_code,
1614 last_status_date = l_evh_rec.last_status_date,
1615 stream_type_code = l_evh_rec.stream_type_code,
1616 event_standalone_flag = l_evh_rec.event_standalone_flag,
1617 day_of_event = l_evh_rec.day_of_event,
1618 agenda_start_time = to_date((to_char(l_evh_rec.agenda_start_time,'HH24:MI')),'HH24:MI'),
1619 agenda_end_time = to_date((to_char(l_evh_rec.agenda_end_time,'HH24:MI')),'HH24:MI'),
1620 reg_required_flag = l_evh_rec.reg_required_flag,
1621 reg_charge_flag = l_evh_rec.reg_charge_flag,
1622 reg_invited_only_flag = l_evh_rec.reg_invited_only_flag,
1623 partner_flag = l_evh_rec.partner_flag,
1624 overflow_flag = l_evh_rec.overflow_flag,
1625 parent_event_header_id = l_evh_rec.parent_event_header_id,
1626 duration = l_evh_rec.duration,
1627 duration_uom_code = l_evh_rec.duration_uom_code,
1628 active_from_date = l_evh_rec.active_from_date,
1629 active_to_date = l_evh_rec.active_to_date,
1630 reg_maximum_capacity = l_evh_rec.reg_maximum_capacity,
1631 reg_minimum_capacity = l_evh_rec.reg_minimum_capacity,
1632 main_language_code = l_evh_rec.main_language_code,
1633 cert_credit_type_code = l_evh_rec.cert_credit_type_code,
1634 certification_credits = l_evh_rec.certification_credits,
1635 organization_id = l_evh_rec.organization_id, -- check if update is allowed on this field.
1636 inventory_item_id = l_evh_rec.inventory_item_id, -- check if update is allowed on this field.
1637 forecasted_revenue = l_evh_rec.forecasted_revenue,
1638 actual_revenue = l_evh_rec.actual_revenue,
1639 forecasted_cost = l_evh_rec.forecasted_cost,
1640 actual_cost = l_evh_rec.actual_cost,
1641 coordinator_id = l_evh_rec.coordinator_id,
1642 fund_source_type_code = l_evh_rec.fund_source_type_code,
1643 fund_source_id = l_evh_rec.fund_source_id,
1644 fund_amount_tc = l_evh_rec.fund_amount_tc,
1645 fund_amount_fc = l_evh_rec.fund_amount_fc,
1646 currency_code_tc = l_evh_rec.currency_code_tc,
1647 currency_code_fc = l_evh_rec.currency_code_fc,
1648 owner_user_id = l_evh_rec.owner_user_id,
1649 url = l_evh_rec.url,
1650 phone = l_evh_rec.phone,
1651 email = l_evh_rec.email,
1652 priority_type_code = l_evh_rec.priority_type_code,
1653 cancellation_reason_code = l_evh_rec.cancellation_reason_code,
1654 inbound_script_name = l_evh_rec.inbound_script_name,
1655 attribute_category = l_evh_rec.attribute_category,
1656 attribute1 = l_evh_rec.attribute1,
1657 attribute2 = l_evh_rec.attribute2,
1658 attribute3 = l_evh_rec.attribute3,
1659 attribute4 = l_evh_rec.attribute4,
1660 attribute5 = l_evh_rec.attribute5,
1661 attribute6 = l_evh_rec.attribute6,
1662 attribute7 = l_evh_rec.attribute7,
1663 attribute8 = l_evh_rec.attribute8,
1664 attribute9 = l_evh_rec.attribute9,
1665 attribute10 = l_evh_rec.attribute10,
1666 attribute11 = l_evh_rec.attribute11,
1667 attribute12 = l_evh_rec.attribute12,
1668 attribute13 = l_evh_rec.attribute13,
1669 attribute14 = l_evh_rec.attribute14,
1670 attribute15 = l_evh_rec.attribute15,
1671 country_code = l_evh_rec.country_code,
1672 business_unit_id = l_evh_rec.business_unit_id,
1673 event_calendar = l_evh_rec.event_calendar,
1674 start_period_name = l_evh_rec.start_period_name,
1675 end_period_name = l_evh_rec.end_period_name,
1676 global_flag = nvl(l_evh_rec.global_flag, 'N')
1677 WHERE event_header_id = l_evh_rec.event_header_id
1678 AND object_version_number = l_evh_rec.object_version_number;
1679
1680 END IF;
1681
1682 -- GDEODHAR : End of changes. Sept. 26th 2000.
1683
1684 IF (AMS_DEBUG_HIGH_ON) THEN
1685
1686
1687
1688 AMS_Utility_PVT.debug_message(l_full_name ||': update done');
1689
1690 END IF;
1691
1692 IF (SQL%NOTFOUND) THEN
1693 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1694 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1695 FND_MSG_PUB.add;
1696 END IF;
1697 RAISE FND_API.g_exc_error;
1698 END IF;
1699
1700 IF (AMS_DEBUG_HIGH_ON) THEN
1701
1702
1703
1704 AMS_Utility_PVT.debug_message(l_full_name ||':BEFORE TL');
1705
1706 END IF;
1707 IF (AMS_DEBUG_HIGH_ON) THEN
1708
1709 AMS_Utility_PVT.debug_message('FOR NAGEN : Before Update evhname = ' || l_evh_rec.event_header_name);
1710 END IF;
1711 IF (AMS_DEBUG_HIGH_ON) THEN
1712
1713 AMS_Utility_PVT.debug_message('FOR NAGEN : Before Update evhdesc = ' || l_evh_rec.description);
1714 END IF;
1715
1716 -- GDEODHAR : Sept. 26th, 2000 : Note that for MAIN Events and Agenda (SUB) items, the
1717 -- update of the TL table is the same.
1718
1719 update ams_event_headers_all_tl set
1720 event_header_name = l_evh_rec.event_header_name,
1721 event_mktg_message = l_evh_rec.event_mktg_message,
1722 description = l_evh_rec.description,
1723 last_update_date = SYSDATE,
1724 last_updated_by = FND_GLOBAL.user_id,
1725 last_update_login = FND_GLOBAL.conc_login_id,
1726 source_lang = USERENV('LANG')
1727 WHERE event_header_id = l_evh_rec.event_header_id
1728 AND USERENV('LANG') IN (language, source_lang);
1729
1730 IF (SQL%NOTFOUND) THEN
1731 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1732 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1733 FND_MSG_PUB.add;
1734 END IF;
1735 RAISE FND_API.g_exc_error;
1736 END IF;
1737
1738 IF (AMS_DEBUG_HIGH_ON) THEN
1739
1740
1741
1742 AMS_Utility_PVT.debug_message(l_full_name ||':AFTER TL');
1743
1744 END IF;
1745
1746 ---murali call "update_event_status 09/26/00 S
1747 -- GDEODHAR : Added a condition. (Sept. 26th 2000)
1748
1749 IF l_evh_rec.event_level = 'MAIN' THEN
1750 AMS_EvhRules_PVT.update_event_status(
1751 p_event_id => l_evh_rec.event_header_id,
1752 p_event_activity_type => 'EVEH',
1753 p_user_status_id => l_evh_rec.user_status_id,
1754 p_fund_amount_tc => l_evh_rec.fund_amount_tc,
1755 p_currency_code_tc => l_evh_rec.currency_code_tc
1756 );
1757 IF (AMS_DEBUG_HIGH_ON) THEN
1758
1759 AMS_Utility_PVT.debug_message('calling before Add_Update_Access_record');
1760 END IF;
1761 AMS_EvhRules_PVT.Add_Update_Access_record(p_object_type => 'EVEH',
1762 p_object_id => l_evh_rec.event_header_id,
1763 p_Owner_user_id => l_evh_rec.owner_user_id,
1764 x_return_status => l_return_status,
1765 x_msg_count => x_msg_count,
1766 x_msg_data => x_msg_data);
1767 IF (AMS_DEBUG_HIGH_ON) THEN
1768
1769 AMS_Utility_PVT.debug_message('after before Add_Update_Access_record || l_return_status');
1770 END IF;
1771 END IF;
1772 ---murali call "update_event_status 09/26/00 E
1773
1774
1775 -------------------- finish --------------------------
1776 IF FND_API.to_boolean(p_commit) THEN
1777 IF (AMS_DEBUG_HIGH_ON) THEN
1778
1779 AMS_Utility_PVT.debug_message(l_full_name ||':Calling Commit.');
1780 END IF;
1781 COMMIT;
1782 ELSE
1783 IF (AMS_DEBUG_HIGH_ON) THEN
1784
1785 AMS_Utility_PVT.debug_message(l_full_name ||':Did not call Commit.');
1786 END IF;
1787 END IF;
1788
1789 IF (AMS_DEBUG_HIGH_ON) THEN
1790
1791
1792
1793 AMS_Utility_PVT.debug_message(l_full_name ||': getting messages');
1794
1795 END IF;
1796
1797 FND_MSG_PUB.count_and_get(
1798 p_encoded => FND_API.g_false,
1799 p_count => x_msg_count,
1800 p_data => x_msg_data
1801 );
1802
1803 IF (AMS_DEBUG_HIGH_ON) THEN
1804
1805
1806
1807 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1808
1809 END IF;
1810
1811 EXCEPTION
1812
1813 WHEN FND_API.g_exc_error THEN
1814 ROLLBACK TO update_event_header;
1815 x_return_status := FND_API.g_ret_sts_error;
1816 FND_MSG_PUB.count_and_get(
1817 p_encoded => FND_API.g_false,
1818 p_count => x_msg_count,
1819 p_data => x_msg_data
1820 );
1821
1822 WHEN FND_API.g_exc_unexpected_error THEN
1823 ROLLBACK TO update_event_header;
1824 x_return_status := FND_API.g_ret_sts_unexp_error ;
1825 FND_MSG_PUB.count_and_get(
1826 p_encoded => FND_API.g_false,
1827 p_count => x_msg_count,
1828 p_data => x_msg_data
1829 );
1830
1831 WHEN OTHERS THEN
1832 ROLLBACK TO update_event_header;
1833 x_return_status := FND_API.g_ret_sts_unexp_error ;
1834
1835 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1836 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1837 END IF;
1838
1839 FND_MSG_PUB.count_and_get(
1840 p_encoded => FND_API.g_false,
1841 p_count => x_msg_count,
1842 p_data => x_msg_data
1843 );
1844
1845 END update_event_header;
1846
1847
1848 --------------------------------------------------------------------
1849 -- PROCEDURE
1850 -- validate_event_header
1851 --
1852 -- HISTORY
1853 -- 11/17/1999 gdeodhar Created.
1854 --------------------------------------------------------------------
1855 PROCEDURE validate_event_header(
1856 p_api_version IN NUMBER,
1857 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1858 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1859
1860 x_return_status OUT NOCOPY VARCHAR2,
1861 x_msg_count OUT NOCOPY NUMBER,
1862 x_msg_data OUT NOCOPY VARCHAR2,
1863
1864 p_evh_rec IN evh_rec_type
1865 )
1866 IS
1867
1868 l_api_version CONSTANT NUMBER := 1.0;
1869 l_api_name CONSTANT VARCHAR2(30) := 'validate_event_header';
1870 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1871 l_return_status VARCHAR2(1);
1872
1873 BEGIN
1874
1875 ----------------------- initialize --------------------
1876 IF (AMS_DEBUG_HIGH_ON) THEN
1877
1878 AMS_Utility_PVT.debug_message(l_full_name||': start');
1879 END IF;
1880
1881 IF FND_API.to_boolean(p_init_msg_list) THEN
1882 FND_MSG_PUB.initialize;
1883 END IF;
1884
1885 IF NOT FND_API.compatible_api_call(
1886 l_api_version,
1887 p_api_version,
1888 l_api_name,
1889 g_pkg_name
1890 ) THEN
1891 RAISE FND_API.g_exc_unexpected_error;
1892 END IF;
1893
1894 x_return_status := FND_API.g_ret_sts_success;
1895
1896 ---------------------- validate ------------------------
1897 IF (AMS_DEBUG_HIGH_ON) THEN
1898
1899 AMS_Utility_PVT.debug_message(l_full_name||': check items');
1900 END IF;
1901
1902 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1903 check_evh_items(
1904 p_evh_rec => p_evh_rec,
1905 p_validation_mode => JTF_PLSQL_API.g_create,
1906 x_return_status => l_return_status
1907 );
1908
1909 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1910 RAISE FND_API.g_exc_unexpected_error;
1911 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1912 RAISE FND_API.g_exc_error;
1913 END IF;
1914 END IF;
1915
1916 IF (AMS_DEBUG_HIGH_ON) THEN
1917
1918
1919
1920 AMS_Utility_PVT.debug_message(l_full_name||': check record');
1921
1922 END IF;
1923
1924 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
1925 check_evh_record(
1926 p_evh_rec => p_evh_rec,
1927 p_complete_rec => NULL,
1928 x_return_status => l_return_status
1929 );
1930
1931 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1932 RAISE FND_API.g_exc_unexpected_error;
1933 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1934 RAISE FND_API.g_exc_error;
1935 END IF;
1936 END IF;
1937
1938
1939 IF (AMS_DEBUG_HIGH_ON) THEN
1940
1941
1942
1943
1944
1945 AMS_Utility_PVT.debug_message(l_full_name||': check inter-entity');
1946
1947
1948 END IF;
1949
1950 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_inter_entity THEN
1951 IF p_evh_rec.event_level = 'MAIN' THEN
1952 check_evh_inter_entity(
1953 p_evh_rec => p_evh_rec,
1954 p_complete_rec => p_evh_rec,
1955 p_validation_mode => JTF_PLSQL_API.g_create,
1956 x_return_status => l_return_status
1957 );
1958 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1959 RAISE FND_API.g_exc_unexpected_error;
1960 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1961 RAISE FND_API.g_exc_error;
1962 END IF;
1963 END IF;
1964 END IF;
1965
1966 -------------------- finish --------------------------
1967 FND_MSG_PUB.count_and_get(
1968 p_encoded => FND_API.g_false,
1969 p_count => x_msg_count,
1970 p_data => x_msg_data
1971 );
1972
1973 IF (AMS_DEBUG_HIGH_ON) THEN
1974
1975
1976
1977 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1978
1979 END IF;
1980
1981 EXCEPTION
1982
1983 WHEN FND_API.g_exc_error THEN
1984 x_return_status := FND_API.g_ret_sts_error;
1985 FND_MSG_PUB.count_and_get(
1986 p_encoded => FND_API.g_false,
1987 p_count => x_msg_count,
1988 p_data => x_msg_data
1989 );
1990
1991 WHEN FND_API.g_exc_unexpected_error THEN
1992 x_return_status := FND_API.g_ret_sts_unexp_error ;
1993 FND_MSG_PUB.count_and_get(
1994 p_encoded => FND_API.g_false,
1995 p_count => x_msg_count,
1996 p_data => x_msg_data
1997 );
1998
1999 WHEN OTHERS THEN
2000 x_return_status := FND_API.g_ret_sts_unexp_error;
2001 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
2002 THEN
2003 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2004 END IF;
2005
2006 FND_MSG_PUB.count_and_get(
2007 p_encoded => FND_API.g_false,
2008 p_count => x_msg_count,
2009 p_data => x_msg_data
2010 );
2011
2012 END validate_event_header;
2013
2014
2015 ---------------------------------------------------------------------
2016 -- PROCEDURE
2017 -- check_evh_update_ok_items
2018 --
2019 -- HISTORY
2020 -- 11/18/1999 gdeodhar Created.
2021 ---------------------------------------------------------------------
2022 PROCEDURE check_evh_update_ok_items(
2023 p_evh_rec IN evh_rec_type,
2024 x_return_status OUT NOCOPY VARCHAR2
2025 )
2026 IS
2027
2028 BEGIN
2029 x_return_status := FND_API.g_ret_sts_success;
2030
2031 -- Which validations should go here?
2032 -- must check with Ravi.
2033
2034 END check_evh_update_ok_items;
2035
2036
2037 ---------------------------------------------------------------------
2038 -- PROCEDURE
2039 -- check_evh_req_items
2040 --
2041 -- HISTORY
2042 -- 11/17/1999 gdeodhar Created.
2043 -- 11/19/1999 rvaka updated.
2044 --
2045 --
2046 -- NOTES
2047 -- not checking all flags and last_status_date as they are defaulted
2048 ---------------------------------------------------------------------
2049 PROCEDURE check_evh_req_items(
2050 p_evh_rec IN evh_rec_type,
2051 x_return_status OUT NOCOPY VARCHAR2
2052 )
2053 IS
2054 BEGIN
2055
2056 x_return_status := FND_API.g_ret_sts_success;
2057 IF (AMS_DEBUG_HIGH_ON) THEN
2058
2059 AMS_Utility_PVT.debug_message('INSIDE EVH REQ');
2060 END IF;
2061
2062 ------------------------ owner_user_id --------------------------
2063 IF p_evh_rec.owner_user_id IS NULL THEN
2064 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2065 THEN
2066 FND_MESSAGE.set_name('AMS', 'AMS_EVH_NO_OWNER_ID');
2067 FND_MSG_PUB.add;
2068 END IF;
2069 x_return_status := FND_API.g_ret_sts_error;
2070 RETURN;
2071 END IF;
2072
2073 -- user_status_id cannot be made as a required field.
2074 -- it is defaulted to 1 (i.e NEW) in create.
2075 -- sometimes it is sent by update, however mostly it is driven
2076 -- by the status order rules and updated through workflow.
2077
2078 ------------------------ event_type_code --------------------
2079 -------------------- required only for MAIN event_level------
2080 IF p_evh_rec.event_level = 'MAIN' AND
2081 p_evh_rec.event_type_code IS NULL THEN
2082
2083 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2084 THEN
2085 FND_MESSAGE.set_name('AMS', 'AMS_EVH_NO_EVENT_TYPE_CODE');
2086 FND_MSG_PUB.add;
2087 END IF;
2088
2089 x_return_status := FND_API.g_ret_sts_error;
2090 RETURN;
2091
2092 END IF;
2093 ------------------------ event_level --------------------------
2094 IF p_evh_rec.event_level IS NULL THEN
2095 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2096 THEN
2097 FND_MESSAGE.set_name('AMS', 'AMS_EVH_NO_EVENT_LEVEL');
2098 FND_MSG_PUB.add;
2099 END IF;
2100
2101 x_return_status := FND_API.g_ret_sts_error;
2102 RETURN;
2103 END IF;
2104 ------------------------ event_header_name --------------------------
2105 IF p_evh_rec.event_header_name IS NULL THEN
2106 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2107 THEN
2108 FND_MESSAGE.set_name('AMS', 'AMS_EVH_NO_NAME');
2109 FND_MSG_PUB.add;
2110 END IF;
2111 x_return_status := FND_API.g_ret_sts_error;
2112 RETURN;
2113 END IF;
2114
2115 ------------------------ application_id --------------------------
2116 IF p_evh_rec.application_id IS NULL THEN
2117 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2118 THEN
2119 FND_MESSAGE.set_name('AMS', 'AMS_API_NO_APPLICATION_ID');
2120 FND_MSG_PUB.add;
2121 END IF;
2122
2123 x_return_status := FND_API.g_ret_sts_error;
2124 RETURN;
2125 END IF;
2126
2127 ------------------------ custom_setup_id --------------------------
2128 IF (p_evh_rec.event_level = 'MAIN' AND
2129 (p_evh_rec.custom_setup_id IS NULL OR p_evh_rec.custom_setup_id = FND_API.g_miss_num)) THEN
2130 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2131 THEN
2132 FND_MESSAGE.set_name('AMS', 'AMS_EVO_NO_SETUP_ID'); -- this message is generic enuf for EVO or EVH
2133 FND_MSG_PUB.add;
2134 END IF;
2135 x_return_status := FND_API.g_ret_sts_error;
2136 RETURN;
2137 END IF;
2138
2139 -- check other required items.
2140 -- if duration is not null, duration uom code must be present and vice-versa.
2141
2142
2143 END check_evh_req_items;
2144
2145
2146 ---------------------------------------------------------------------
2147 -- PROCEDURE
2148 -- check_evh_uk_items
2149 --
2150 -- HISTORY
2151 -- 11/17/1999 gdeodhar Created.
2152 -- 11/19/1999 rvaka updated.
2153 ---------------------------------------------------------------------
2154 PROCEDURE check_evh_uk_items(
2155 p_evh_rec IN evh_rec_type,
2156 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
2157 x_return_status OUT NOCOPY VARCHAR2
2158 )
2159 IS
2160
2161
2162 l_valid_flag VARCHAR2(1);
2163 l_dummy NUMBER;
2164 cursor c_src_code(src_code_in IN VARCHAR2) IS
2165 SELECT 1 FROM DUAL WHERE EXISTS (select 1 from ams_source_codes
2166 where SOURCE_CODE = src_code_in);
2167 BEGIN
2168
2169 x_return_status := FND_API.g_ret_sts_success;
2170
2171 IF (AMS_DEBUG_HIGH_ON) THEN
2172
2173
2174
2175 AMS_Utility_PVT.debug_message('INSIDE EVH UK');
2176
2177 END IF;
2178
2179 -- For create_event_header, when event_header_id is passed in, we need to
2180 -- check if this event_header_id is unique.
2181
2182 IF p_validation_mode = JTF_PLSQL_API.g_create
2183 AND p_evh_rec.event_header_id IS NOT NULL
2184 THEN
2185 IF AMS_Utility_PVT.check_uniqueness(
2186 'ams_event_headers_vl',
2187 'event_header_id = ' || p_evh_rec.event_header_id
2188 ) = FND_API.g_false
2189 THEN
2190 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2191 THEN
2192 FND_MESSAGE.set_name('AMS', 'AMS_EVH_DUPLICATE_ID');
2193 FND_MSG_PUB.add;
2194 END IF;
2195 x_return_status := FND_API.g_ret_sts_error;
2196 RETURN;
2197 END IF;
2198 END IF;
2199
2200 -- For create_event_header, when source_code is passed in, we need to
2201 -- check if this source_code is unique.
2202 -- For creating, check if source_code is unique in ams_source_codes.
2203 -- Update of source_code is not allowed.
2204 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
2205 IF p_evh_rec.source_code IS NOT NULL THEN
2206 /*
2207 IF AMS_Utility_PVT.check_uniqueness(
2208 'ams_source_codes',
2209 'source_code = ''' || p_evh_rec.source_code ||''''
2210 ) = FND_API.g_false
2211 */
2212 open c_src_code(p_evh_rec.source_code);
2213 fetch c_src_code into l_dummy;
2214 close c_src_code;
2215 IF (AMS_DEBUG_HIGH_ON) THEN
2216
2217 AMS_UTILITY_PVt.debug_message('the value of l_dummy is '||l_dummy);
2218 END IF;
2219 IF l_dummy = 1
2220 THEN
2221 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2222 THEN
2223 FND_MESSAGE.set_name('AMS', 'AMS_EVH_DUPLICATE_SOURCE_CODE');
2224 FND_MSG_PUB.add;
2225 END IF;
2226 x_return_status := FND_API.g_ret_sts_error;
2227 RETURN;
2228 END IF;
2229 END IF;
2230 /* source code should not be passed from screen and it can never be updated
2231 will not complete source code column in complete_evh_rec and will not
2232 update this column in update sql stmt
2233 CHANGE OF LOGIC: SOURCE CODE CAN BE PASSED FROM SCREEN
2234 WILL BE COMPLETED IN COMPLETE_EVH_REC AND WILL BE UPDATED
2235 UNDER EVENTRULES_API, WILL CHECK FOR UNIQUENESS OF SOURCE CODE PASSED AND REVOKE OLD CODE AND CREATE NEW ONE
2236 IF NECESSARY.
2237 HOWEVER, SOURCE CODE CANNOT BE CHANGED IF STATUS <> NEW... CHECKED IN BUSINESS RULES AS WELL...
2238
2239 ELSIF p_evh_rec.source_code <> FND_API.g_miss_char THEN
2240 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2241 THEN
2242 FND_MESSAGE.set_name('AMS', 'AMS_EVH_CANT_UPD_SRCCODE');
2243 FND_MSG_PUB.add;
2244 END IF;
2245 x_return_status := FND_API.g_ret_sts_error;
2246 RETURN;
2247 */
2248 END IF;
2249
2250 -- check other unique items
2251
2252 END check_evh_uk_items;
2253
2254
2255 ---------------------------------------------------------------------
2256 -- PROCEDURE
2257 -- check_evh_fk_items
2258 --
2259 -- HISTORY
2260 -- 11/17/1999 gdeodhar Created.
2261 ---------------------------------------------------------------------
2262 PROCEDURE check_evh_fk_items(
2263 p_evh_rec IN evh_rec_type,
2264 x_return_status OUT NOCOPY VARCHAR2
2265 )
2266 IS
2267 l_table_name VARCHAR2(30);
2268 l_pk_name VARCHAR2(30);
2269 l_pk_value VARCHAR2(30);
2270 l_pk_data_type NUMBER;
2271 l_additional_where_clause VARCHAR2(4000);
2272 BEGIN
2273
2274 x_return_status := FND_API.g_ret_sts_success;
2275
2276 IF (AMS_DEBUG_HIGH_ON) THEN
2277
2278
2279
2280 AMS_Utility_PVT.debug_message('CC' ||':INSIDE EVH FK');
2281
2282 END IF;
2283 ----------------------- owner_user_id ------------------------
2284 IF p_evh_rec.owner_user_id <> FND_API.g_miss_num THEN
2285 IF AMS_Utility_PVT.check_fk_exists(
2286 'ams_jtf_rs_emp_v',
2287 'resource_id',
2288 p_evh_rec.owner_user_id
2289 ) = FND_API.g_false
2290 THEN
2291 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2292 THEN
2293 FND_MESSAGE.set_name('AMS', 'AMS_EVH_BAD_OWNER_USER_ID');
2294 FND_MSG_PUB.add;
2295 END IF;
2296 x_return_status := FND_API.g_ret_sts_error;
2297 RETURN;
2298 END IF;
2299 END IF;
2300
2301
2302 --------------------- application_id ------------------------
2303 IF p_evh_rec.application_id <> FND_API.g_miss_num THEN
2304 IF AMS_Utility_PVT.check_fk_exists(
2305 'fnd_application',
2306 'application_id',
2307 p_evh_rec.application_id
2308 ) = FND_API.g_false
2309 THEN
2310 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2311 THEN
2312 FND_MESSAGE.set_name('AMS', 'AMS_EVH_BAD_APPLICATION_ID');
2313 FND_MSG_PUB.add;
2314 END IF;
2315 x_return_status := FND_API.g_ret_sts_error;
2316 RETURN;
2317 END IF;
2318 END IF;
2319
2320 --------------------- inbound_script_name ------------------------
2321 IF p_evh_rec.inbound_script_name <> FND_API.g_miss_char
2322 AND p_evh_rec.inbound_script_name IS NOT NULL
2323 THEN
2324 IF AMS_Utility_PVT.check_fk_exists(
2325 'ies_deployed_scripts',
2326 'dscript_name',
2327 p_evh_rec.inbound_script_name,
2328 AMS_Utility_PVT.g_varchar2
2329 ) = FND_API.g_false
2330 THEN
2331 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2332 THEN
2333 FND_MESSAGE.set_name('AMS', 'AMS_EVH_BAD_INBOUND_SCRIPT');
2334 FND_MSG_PUB.add;
2335 END IF;
2336 x_return_status := FND_API.g_ret_sts_error;
2337 RETURN;
2338 END IF;
2339 END IF;
2340
2341 ----------------------- parent_event_header_id ------------------------
2342 IF p_evh_rec.parent_event_header_id <> FND_API.g_miss_num THEN
2343 IF AMS_Utility_PVT.check_fk_exists(
2344 'ams_event_headers_vl',
2345 'event_header_id',
2346 p_evh_rec.parent_event_header_id
2347 ) = FND_API.g_false
2348 THEN
2349 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2350 THEN
2351 FND_MESSAGE.set_name('AMS', 'AMS_EVH_BAD_PARENT_EVEH');
2352 FND_MSG_PUB.add;
2353 END IF;
2354
2355 x_return_status := FND_API.g_ret_sts_error;
2356 RETURN;
2357 END IF;
2358 END IF;
2359
2360 ----------------------- program_id ------------------------
2361 IF p_evh_rec.program_id <> FND_API.g_miss_num THEN
2362 IF AMS_Utility_PVT.check_fk_exists(
2363 'AMS_CAMPAIGNS_ALL_B',
2364 'CAMPAIGN_ID',
2365 p_evh_rec.program_id
2366 ) = FND_API.g_false
2367 THEN
2368 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2369 THEN
2370 FND_MESSAGE.set_name('AMS', 'AMS_EVH_BAD_PARENT_EVEH');
2371 FND_MSG_PUB.add;
2372 END IF;
2373
2374 x_return_status := FND_API.g_ret_sts_error;
2375 RETURN;
2376 END IF;
2377 END IF;
2378
2379 --------------------- currency_code_tc ------------------------
2380 IF p_evh_rec.currency_code_tc <> FND_API.g_miss_char
2381 AND p_evh_rec.currency_code_tc IS NOT NULL
2382 THEN
2383 IF AMS_Utility_PVT.check_fk_exists(
2384 'fnd_currencies_vl',
2385 'currency_code',
2386 p_evh_rec.currency_code_tc,
2387 AMS_Utility_PVT.g_varchar2
2388 ) = FND_API.g_false
2389 THEN
2390 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2391 THEN
2392 FND_MESSAGE.set_name('AMS', 'AMS_EVH_BAD_CURRENCY_CODE_TC');
2393 FND_MSG_PUB.add;
2394 END IF;
2395 x_return_status := FND_API.g_ret_sts_error;
2396 RETURN;
2397 END IF;
2398 END IF;
2399
2400 --------------------- currency_code_fc ------------------------
2401 IF p_evh_rec.currency_code_fc <> FND_API.g_miss_char
2402 AND p_evh_rec.currency_code_fc IS NOT NULL
2403 THEN
2404 IF AMS_Utility_PVT.check_fk_exists(
2405 'fnd_currencies_vl',
2406 'currency_code',
2407 p_evh_rec.currency_code_fc,
2408 AMS_Utility_PVT.g_varchar2
2409 ) = FND_API.g_false
2410 THEN
2411 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2412 THEN
2413 FND_MESSAGE.set_name('AMS', 'AMS_EVH_BAD_CURRENCY_CODE_FC');
2414 FND_MSG_PUB.add;
2415 END IF;
2416 x_return_status := FND_API.g_ret_sts_error;
2417 RETURN;
2418 END IF;
2419 END IF;
2420 ----------------------- user_status_id ------------------------
2421 IF p_evh_rec.user_status_id <> FND_API.g_miss_num THEN
2422 IF AMS_Utility_PVT.check_fk_exists(
2423 'ams_user_statuses_b',
2424 'user_status_id',
2425 p_evh_rec.user_status_id
2426 ) = FND_API.g_false
2427 THEN
2428 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2429 THEN
2430 FND_MESSAGE.set_name('AMS', 'AMS_EVH_BAD_USER_STATUS_ID');
2431 FND_MSG_PUB.add;
2432 END IF;
2433 x_return_status := FND_API.g_ret_sts_error;
2434 RETURN;
2435 END IF;
2436 END IF;
2437
2438
2439 --------------------- country_code ----------------------------
2440 /* Since from hornet country code contains country id we need to rplace the old validation with new
2441 validation the followinfg is the new valiation
2442 */
2443 IF p_evh_rec.country_code <> FND_API.g_miss_char AND
2444 p_evh_rec.country_code IS NOT NULL THEN
2445
2446 l_table_name := 'jtf_loc_hierarchies_b';
2447 l_pk_name := 'location_hierarchy_id' ;
2448 l_pk_data_type := AMS_Utility_PVT.G_NUMBER ;
2449 l_pk_value := to_number(p_evh_rec.country_code);
2450 l_additional_where_clause := null ;
2451
2452 IF AMS_Utility_PVT.check_fk_exists(
2453 p_table_name => l_table_name,
2454 p_pk_name => l_pk_name,
2455 p_pk_value => l_pk_value,
2456 p_pk_data_type => l_pk_data_type,
2457 p_additional_where_clause => l_additional_where_clause
2458 ) = FND_API.g_false
2459 THEN
2460 AMS_Utility_Pvt.Error_Message(p_message_name => 'AMS_CSCH_BAD_COUNTRY') ;
2461 x_return_status := FND_API.g_ret_sts_error;
2462 RETURN;
2463 END IF;
2464 END IF;
2465
2466 /* old code
2467 IF p_evh_rec.country_code <> FND_API.g_miss_char
2468 AND p_evh_rec.country_code IS NOT NULL
2469 THEN
2470 IF AMS_Utility_PVT.check_fk_exists(
2471 'fnd_territories_vl',
2472 'territory_code',
2473 p_evh_rec.country_code,
2474 AMS_Utility_PVT.g_varchar2,
2475 NULL
2476 ) = FND_API.g_false
2477 THEN
2478 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2479 THEN
2480 FND_MESSAGE.set_name('AMS', 'AMS_CAMP_BAD_CITY');
2481 FND_MSG_PUB.add;
2482 END IF;
2483 x_return_status := FND_API.g_ret_sts_error;
2484 RETURN;
2485 END IF;
2486 END IF;
2487 */
2488 -- check other fk items
2489 -- no need to check system_status_code as we are
2490 -- storing it in the header table just to ease the reporting.
2491
2492 END check_evh_fk_items;
2493
2494 ---------------------------------------------------------------------
2495 -- PROCEDURE
2496 -- check_evh_lookup_items
2497 --
2498 -- HISTORY
2499 -- 11/18/1999 gdeodhar Created.
2500 ---------------------------------------------------------------------
2501 PROCEDURE check_evh_lookup_items(
2502 p_evh_rec IN evh_rec_type,
2503 x_return_status OUT NOCOPY VARCHAR2
2504 )
2505 IS
2506 BEGIN
2507
2508 x_return_status := FND_API.g_ret_sts_success;
2509 IF (AMS_DEBUG_HIGH_ON) THEN
2510
2511 AMS_Utility_PVT.debug_message('INSIDE EVH LOOKUP');
2512 END IF;
2513
2514 /*
2515 ----------------------- system_status_code ------------------------
2516 IF p_evh_rec.system_status_code <> FND_API.g_miss_char THEN
2517 IF AMS_Utility_PVT.check_lookup_exists(
2518 p_lookup_table_name => 'AMS_USER_STATUSES_B',
2519 p_lookup_type => 'AMS_EVENT_STATUS',
2520 p_lookup_code => p_evh_rec.system_status_code
2521 ) = FND_API.g_false
2522 THEN
2523 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2524 THEN
2525 FND_MESSAGE.set_name('AMS', 'AMS_EVH_BAD_STATUS_CODE');
2526 FND_MSG_PUB.add;
2527 END IF;
2528
2529 x_return_status := FND_API.g_ret_sts_error;
2530 RETURN;
2531 END IF;
2532 END IF;
2533
2534 -- Note: it may not be necessary to check the system_status_code in this procedure.
2535 -- we are storing this field in headers table just for ease of reporting.
2536 */
2537
2538 -- check other lookup codes
2539 -- event_level must be checked here. (MAIN or SUB)
2540
2541 ----------------------- event_type ------------------------
2542 IF p_evh_rec.event_type_code <> FND_API.g_miss_char
2543 AND p_evh_rec.event_type_code IS NOT NULL
2544 THEN
2545 IF AMS_Utility_PVT.check_lookup_exists(
2546 p_lookup_type => 'AMS_EVENT_TYPE',
2547 p_lookup_code => p_evh_rec.event_type_code
2548 ) = FND_API.g_false
2549 THEN
2550 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2551 THEN
2552 FND_MESSAGE.set_name('AMS', 'AMS_EVH_BAD_TYPE');
2553 FND_MSG_PUB.add;
2554 END IF;
2555 x_return_status := FND_API.g_ret_sts_error;
2556 RETURN;
2557 END IF;
2558 END IF;
2559
2560 ----------------------- event_level ------------------------
2561 IF p_evh_rec.event_level <> FND_API.g_miss_char
2562 AND p_evh_rec.event_level IS NOT NULL
2563 THEN
2564 IF AMS_Utility_PVT.check_lookup_exists(
2565 p_lookup_type => 'AMS_EVENT_LEVEL',
2566 p_lookup_code => p_evh_rec.event_level
2567 ) = FND_API.g_false
2568 THEN
2569 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2570 THEN
2571 FND_MESSAGE.set_name('AMS', 'AMS_EVH_BAD_EVENT_LEVEL');
2572 FND_MSG_PUB.add;
2573 END IF;
2574 x_return_status := FND_API.g_ret_sts_error;
2575 RETURN;
2576 END IF;
2577 END IF;
2578
2579 ----------------------- priority ------------------------
2580 IF p_evh_rec.priority_type_code <> FND_API.g_miss_char
2581 AND p_evh_rec.priority_type_code IS NOT NULL
2582 THEN
2583 IF AMS_Utility_PVT.check_lookup_exists(
2584 p_lookup_type => 'AMS_PRIORITY',
2585 p_lookup_code => p_evh_rec.priority_type_code
2586 ) = FND_API.g_false
2587 THEN
2588 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2589 THEN
2590 FND_MESSAGE.set_name('AMS', 'AMS_EVH_BAD_PRIORITY');
2591 FND_MSG_PUB.add;
2592 END IF;
2593 x_return_status := FND_API.g_ret_sts_error;
2594 RETURN;
2595 END IF;
2596 END IF;
2597
2598
2599 ----------------------- fund_source_type ------------------------
2600 IF p_evh_rec.fund_source_type_code <> FND_API.g_miss_char
2601 AND p_evh_rec.fund_source_type_code IS NOT NULL
2602 THEN
2603 IF AMS_Utility_PVT.check_lookup_exists(
2604 p_lookup_type => 'AMS_FUND_SOURCE',
2605 p_lookup_code => p_evh_rec.fund_source_type_code
2606 ) = FND_API.g_false
2607 THEN
2608 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2609 THEN
2610 FND_MESSAGE.set_name('AMS', 'AMS_EVH_BAD_FUND_SOURCE');
2611 FND_MSG_PUB.add;
2612 END IF;
2613 x_return_status := FND_API.g_ret_sts_error;
2614 RETURN;
2615 END IF;
2616 END IF;
2617
2618 ----------------------- day_of_event ------------------------
2619 IF p_evh_rec.day_of_event <> FND_API.g_miss_char
2620 AND p_evh_rec.day_of_event IS NOT NULL
2621 THEN
2622 IF AMS_Utility_PVT.check_lookup_exists(
2623 p_lookup_type => 'AMS_EVENT_DAY',
2624 p_lookup_code => p_evh_rec.day_of_event
2625 ) = FND_API.g_false
2626 THEN
2627 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2628 THEN
2629 FND_MESSAGE.set_name('AMS', 'AMS_EVH_BAD_DAY_OF_EVENT');
2630 FND_MSG_PUB.add;
2631 END IF;
2632 x_return_status := FND_API.g_ret_sts_error;
2633 RETURN;
2634 END IF;
2635 END IF;
2636
2637 ----------------------- certification_credit_type ------------------------
2638 IF p_evh_rec.cert_credit_type_code <> FND_API.g_miss_char
2639 AND p_evh_rec.cert_credit_type_code IS NOT NULL
2640 THEN
2641 IF AMS_Utility_PVT.check_lookup_exists(
2642 p_lookup_type => 'AMS_EVENT_CERT_CREDIT_TYPE',
2643 p_lookup_code => p_evh_rec.cert_credit_type_code
2644 ) = FND_API.g_false
2645 THEN
2646 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2647 THEN
2648 FND_MESSAGE.set_name('AMS', 'AMS_EVH_BAD_CERT_CREDIT_TYPE');
2649 FND_MSG_PUB.add;
2650 END IF;
2651 x_return_status := FND_API.g_ret_sts_error;
2652 RETURN;
2653 END IF;
2654 END IF;
2655
2656 ----------------------- cancellation_reason_code ------------------------
2657 IF p_evh_rec.cancellation_reason_code <> FND_API.g_miss_char
2658 AND p_evh_rec.cancellation_reason_code IS NOT NULL
2659 THEN
2660 IF AMS_Utility_PVT.check_lookup_exists(
2661 p_lookup_type => 'AMS_EVENT_CANCEL_REASON',
2662 p_lookup_code => p_evh_rec.cancellation_reason_code
2663 ) = FND_API.g_false
2664 THEN
2665 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2666 THEN
2667 FND_MESSAGE.set_name('AMS', 'AMS_EVH_BAD_CANCEL_REASON');
2668 FND_MSG_PUB.add;
2669 END IF;
2670 x_return_status := FND_API.g_ret_sts_error;
2671 RETURN;
2672 END IF;
2673 END IF;
2674
2675 ----------------------- stream_type_code ------------------------
2676 IF p_evh_rec.stream_type_code <> FND_API.g_miss_char
2677 AND p_evh_rec.stream_type_code IS NOT NULL
2678 THEN
2679 IF AMS_Utility_PVT.check_lookup_exists(
2680 p_lookup_type => 'AMS_EVENT_STREAM_TYPE',
2681 p_lookup_code => p_evh_rec.stream_type_code
2682 ) = FND_API.g_false
2683 THEN
2684 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2685 THEN
2686 FND_MESSAGE.set_name('AMS', 'AMS_EVH_BAD_STREAM_TYPE');
2687 FND_MSG_PUB.add;
2688 END IF;
2689 x_return_status := FND_API.g_ret_sts_error;
2690 RETURN;
2691 END IF;
2692 END IF;
2693
2694 IF (AMS_DEBUG_HIGH_ON) THEN
2695
2696
2697
2698 AMS_Utility_PVT.debug_message('AFTER EVH LOOKUP');
2699
2700 END IF;
2701
2702
2703 END check_evh_lookup_items;
2704
2705
2706 ---------------------------------------------------------------------
2707 -- PROCEDURE
2708 -- check_evh_flag_items
2709 --
2710 -- HISTORY
2711 -- 11/18/1999 gdeodhar Created
2712 -- 11/19/1999 rvaka updated
2713 ---------------------------------------------------------------------
2714 PROCEDURE check_evh_flag_items(
2715 p_evh_rec IN evh_rec_type,
2716 x_return_status OUT NOCOPY VARCHAR2
2717 )
2718 IS
2719 BEGIN
2720
2721 x_return_status := FND_API.g_ret_sts_success;
2722 IF (AMS_DEBUG_HIGH_ON) THEN
2723
2724 AMS_Utility_PVT.debug_message('INSIDE EVH FLAG');
2725 END IF;
2726
2727 ----------------------- active_flag ------------------------
2728 IF p_evh_rec.active_flag <> FND_API.g_miss_char
2729 AND p_evh_rec.active_flag IS NOT NULL
2730 THEN
2731 IF AMS_Utility_PVT.is_Y_or_N(p_evh_rec.active_flag) = FND_API.g_false
2732 THEN
2733 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2734 THEN
2735 FND_MESSAGE.set_name('AMS', 'AMS_EVH_BAD_ACTIVE_FLAG');
2736 FND_MSG_PUB.add;
2737 END IF;
2738 x_return_status := FND_API.g_ret_sts_error;
2739 RETURN;
2740 END IF;
2741 END IF;
2742
2743 ----------------------- event_standalone_flag ------------------------
2744 IF p_evh_rec.event_standalone_flag <> FND_API.g_miss_char
2745 AND p_evh_rec.event_standalone_flag IS NOT NULL
2746 THEN
2747 IF AMS_Utility_PVT.is_Y_or_N(p_evh_rec.event_standalone_flag) = FND_API.g_false
2748 THEN
2749 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2750 THEN
2751 FND_MESSAGE.set_name('AMS', 'AMS_EVH_BAD_STANDALONE_FL');
2752 FND_MSG_PUB.add;
2753 END IF;
2754 x_return_status := FND_API.g_ret_sts_error;
2755 RETURN;
2756 END IF;
2757 END IF;
2758
2759 ----------------------- private_flag ------------------------
2760 IF p_evh_rec.private_flag <> FND_API.g_miss_char
2761 AND p_evh_rec.private_flag IS NOT NULL
2762 THEN
2763 IF AMS_Utility_PVT.is_Y_or_N(p_evh_rec.private_flag) = FND_API.g_false
2764 THEN
2765 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2766 THEN
2767 FND_MESSAGE.set_name('AMS', 'AMS_EVH_BAD_PRIVATE_FLAG');
2768 FND_MSG_PUB.add;
2769 END IF;
2770 x_return_status := FND_API.g_ret_sts_error;
2771 RETURN;
2772 END IF;
2773 END IF;
2774
2775 ----------------------- reg_required_flag ------------------------
2776 IF p_evh_rec.reg_required_flag <> FND_API.g_miss_char
2777 AND p_evh_rec.reg_required_flag IS NOT NULL
2778 THEN
2779 IF AMS_Utility_PVT.is_Y_or_N(p_evh_rec.reg_required_flag) = FND_API.g_false
2780 THEN
2781 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2782 THEN
2783 FND_MESSAGE.set_name('AMS', 'AMS_EVH_BAD_REG_REQUIRED_FLAG');
2784 FND_MSG_PUB.add;
2785 END IF;
2786
2787 x_return_status := FND_API.g_ret_sts_error;
2788 RETURN;
2789 END IF;
2790 END IF;
2791
2792 ----------------------- reg_invited_only_flag ------------------------
2793 IF p_evh_rec.reg_invited_only_flag <> FND_API.g_miss_char
2794 AND p_evh_rec.reg_invited_only_flag IS NOT NULL
2795 THEN
2796 IF AMS_Utility_PVT.is_Y_or_N(p_evh_rec.reg_invited_only_flag) = FND_API.g_false
2797 THEN
2798 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2799 THEN
2800 FND_MESSAGE.set_name('AMS', 'AMS_EVH_BAD_REG_INV_ONLY');
2801 FND_MSG_PUB.add;
2802 END IF;
2803
2804 x_return_status := FND_API.g_ret_sts_error;
2805 RETURN;
2806 END IF;
2807 END IF;
2808
2809 ----------------------- reg_charge_flag ------------------------
2810 IF p_evh_rec.reg_charge_flag <> FND_API.g_miss_char
2811 AND p_evh_rec.reg_charge_flag IS NOT NULL
2812 THEN
2813 IF AMS_Utility_PVT.is_Y_or_N(p_evh_rec.reg_charge_flag) = FND_API.g_false
2814 THEN
2815 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2816 THEN
2817 FND_MESSAGE.set_name('AMS', 'AMS_EVH_BAD_REG_CHARGE_FLAG');
2818 FND_MSG_PUB.add;
2819 END IF;
2820
2821 x_return_status := FND_API.g_ret_sts_error;
2822 RETURN;
2823 END IF;
2824 END IF;
2825
2826
2827 ----------------------- overflow_flag ------------------------
2828 IF p_evh_rec.overflow_flag <> FND_API.g_miss_char
2829 AND p_evh_rec.overflow_flag IS NOT NULL
2830 THEN
2831 IF AMS_Utility_PVT.is_Y_or_N(p_evh_rec.overflow_flag) = FND_API.g_false
2832 THEN
2833 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2834 THEN
2835 FND_MESSAGE.set_name('AMS', 'AMS_EVH_BAD_OVERFLOW_FLAG');
2836 FND_MSG_PUB.add;
2837 END IF;
2838
2839 x_return_status := FND_API.g_ret_sts_error;
2840 RETURN;
2841 END IF;
2842 END IF;
2843
2844 -- check other flags
2845 IF (AMS_DEBUG_HIGH_ON) THEN
2846
2847 AMS_Utility_PVT.debug_message('AFTER EVH FLAG');
2848 END IF;
2849
2850
2851 END check_evh_flag_items;
2852
2853
2854 ---------------------------------------------------------------------
2855 -- PROCEDURE
2856 -- check_evh_items
2857 --
2858 -- HISTORY
2859 -- 11/18/1999 gdeodhar Created.
2860 -- 12/18/1999 rvaka updated.
2861 ---------------------------------------------------------------------
2862 PROCEDURE check_evh_items(
2863 p_evh_rec IN evh_rec_type,
2864 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
2865 x_return_status OUT NOCOPY VARCHAR2
2866 )
2867 IS
2868 BEGIN
2869 IF (AMS_DEBUG_HIGH_ON) THEN
2870
2871 AMS_Utility_PVT.debug_message('INSIDE EVH ITEMS');
2872 END IF;
2873 -------------------------- Update Mode ----------------------------
2874 -- check if the p_evh_rec has any columns that should not be updated at this stage as per the business logic.
2875 -- Also when the event is in active stage (will add later)
2876 --changes to marketing message and budget related columns should not be allowed.
2877
2878 IF p_validation_mode = JTF_PLSQL_API.g_update THEN
2879 check_evh_update_ok_items(
2880 p_evh_rec => p_evh_rec,
2881 x_return_status => x_return_status
2882 );
2883
2884 IF x_return_status <> FND_API.g_ret_sts_success THEN
2885 RETURN;
2886 END IF;
2887 END IF;
2888
2889 -------------------------- Create or Update Mode ----------------------------
2890
2891 check_evh_req_items(
2892 p_evh_rec => p_evh_rec,
2893 x_return_status => x_return_status
2894 );
2895
2896 IF x_return_status <> FND_API.g_ret_sts_success THEN
2897 RETURN;
2898 END IF;
2899
2900 check_evh_uk_items(
2901 p_evh_rec => p_evh_rec,
2902 p_validation_mode => p_validation_mode,
2903 x_return_status => x_return_status
2904 );
2905
2906 IF x_return_status <> FND_API.g_ret_sts_success THEN
2907 RETURN;
2908 END IF;
2909
2910 check_evh_fk_items(
2911 p_evh_rec => p_evh_rec,
2912 x_return_status => x_return_status
2913 );
2914
2915 IF x_return_status <> FND_API.g_ret_sts_success THEN
2916 RETURN;
2917 END IF;
2918
2919 check_evh_lookup_items(
2920 p_evh_rec => p_evh_rec,
2921 x_return_status => x_return_status
2922 );
2923
2924 IF x_return_status <> FND_API.g_ret_sts_success THEN
2925 RETURN;
2926 END IF;
2927
2928 check_evh_flag_items(
2929 p_evh_rec => p_evh_rec,
2930 x_return_status => x_return_status
2931 );
2932
2933 IF x_return_status <> FND_API.g_ret_sts_success THEN
2934 RETURN;
2935 END IF;
2936
2937 END check_evh_items;
2938
2939
2940 ---------------------------------------------------------------------
2941 -- PROCEDURE
2942 -- check_evh_record
2943 --
2944 -- HISTORY
2945 -- 11/18/1999 gdeodhar Created.
2946 -- 12/18/1999 rvaka updated.
2947 ---------------------------------------------------------------------
2948 PROCEDURE check_evh_record(
2949 p_evh_rec IN evh_rec_type,
2950 p_complete_rec IN evh_rec_type,
2951 x_return_status OUT NOCOPY VARCHAR2
2952 )
2953 IS
2954
2955 l_start_date DATE := p_evh_rec.active_from_date;
2956 l_end_date DATE := p_evh_rec.active_to_date;
2957 l_start_time DATE := p_evh_rec.agenda_start_time;
2958 l_end_time DATE := p_evh_rec.agenda_end_time;
2959
2960 BEGIN
2961 IF (AMS_DEBUG_HIGH_ON) THEN
2962
2963 AMS_Utility_PVT.debug_message('INSIDE EVH RECORD');
2964 END IF;
2965 x_return_status := FND_API.g_ret_sts_success;
2966
2967
2968 IF p_evh_rec.active_from_date = FND_API.g_miss_date THEN
2969 l_start_date := p_complete_rec.active_from_date;
2970 ELSE
2971 l_start_date := p_evh_rec.active_from_date;
2972 END IF;
2973
2974 IF p_evh_rec.active_to_date = FND_API.g_miss_date THEN
2975 l_end_date := p_complete_rec.active_to_date;
2976 ELSE
2977 l_end_date := p_evh_rec.active_to_date;
2978 END IF;
2979
2980 IF (AMS_DEBUG_HIGH_ON) THEN
2981
2982
2983
2984 AMS_Utility_PVT.debug_message('L_ST_DATE:'||to_char(l_start_date,'DD-MON-YYY'));
2985
2986 END IF;
2987 IF (AMS_DEBUG_HIGH_ON) THEN
2988
2989 AMS_Utility_PVT.debug_message('L_END_DATE:'||to_char(l_end_date,'DD-MON-YYY'));
2990 END IF;
2991
2992 IF (l_start_date > l_end_date) THEN
2993 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2994 THEN
2995 FND_MESSAGE.set_name('AMS', 'AMS_EVH_START_DT_GT_END_DT');
2996 FND_MSG_PUB.add;
2997 END IF;
2998 x_return_status := FND_API.g_ret_sts_error;
2999 RAISE Fnd_Api.g_exc_error;
3000 return;
3001 END IF;
3002
3003 /* Code Added by GMADANA
3004 Agenda Start time and Agenda End Time are present only for those with event level = 'SUB'
3005 */
3006
3007 IF p_complete_rec.event_level = 'SUB' THEN
3008 IF p_evh_rec.agenda_start_time = Fnd_Api.g_miss_date THEN
3009 l_start_time := p_complete_rec.agenda_start_time;
3010 ELSE
3011 l_start_time := p_evh_rec.agenda_start_time;
3012 END IF;
3013
3014 IF p_evh_rec.agenda_end_time = Fnd_Api.g_miss_date THEN
3015 l_end_time := p_complete_rec.agenda_end_time;
3016 ELSE
3017 l_end_time := p_evh_rec.agenda_end_time;
3018 END IF;
3019
3020 IF l_start_time > l_end_time THEN
3021 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3022 THEN
3023 FND_MESSAGE.set_name('AMS', 'AMS_EVO_START_TM_GT_END_TM'); -- reusing EVEO message
3024 FND_MSG_PUB.add;
3025 END IF;
3026 x_return_status := FND_API.g_ret_sts_error;
3027 RAISE Fnd_Api.g_exc_error;
3028 return;
3029 END IF; -- st tm > end tm
3030 END IF; -- event_level = 'SUB'
3031
3032 -- Check if the above logic will work if either of the dates are NULL.
3033
3034 IF (AMS_DEBUG_HIGH_ON) THEN
3035
3036
3037
3038 AMS_Utility_PVT.debug_message('p_complete_rec.DURATION:'||p_complete_rec.DURATION);
3039
3040 END IF;
3041 IF (AMS_DEBUG_HIGH_ON) THEN
3042
3043 AMS_Utility_PVT.debug_message('p_evh_rec.DURATION:'||p_evh_rec.DURATION);
3044 END IF;
3045 IF (AMS_DEBUG_HIGH_ON) THEN
3046
3047 AMS_Utility_PVT.debug_message('p_complete_rec.DURATION_UOM_CODE:'||nvl(p_complete_rec.DURATION_UOM_CODE,'NULL'));
3048 END IF;
3049 IF (AMS_DEBUG_HIGH_ON) THEN
3050
3051 AMS_Utility_PVT.debug_message('p_evh_rec.DURATION_UOM_CODE:'||nvl(p_evh_rec.DURATION_UOM_CODE, 'NULL') );
3052 END IF;
3053
3054 -- do other record level checkings
3055 IF (p_evh_rec.DURATION <> FND_API.g_miss_num
3056 AND p_evh_rec.DURATION IS NOT NULL )
3057 OR p_complete_rec.DURATION IS NOT NULL THEN
3058
3059 IF (p_evh_rec.DURATION_UOM_CODE = FND_API.g_miss_char
3060 AND p_complete_rec.DURATION_UOM_CODE IS NULL)
3061 OR p_evh_rec.DURATION_UOM_CODE IS NULL
3062 THEN
3063 AMS_Utility_PVT.error_message('AMS_EVO_NO_DUR_UOM_CODE');
3064 x_return_status := FND_API.g_ret_sts_error;
3065 return;
3066 END IF;
3067 END IF;
3068
3069 IF (p_evh_rec.DURATION_UOM_CODE <> FND_API.g_miss_char
3070 AND p_evh_rec.DURATION_UOM_CODE IS NOT NULL)
3071 OR p_complete_rec.DURATION_UOM_CODE IS NOT NULL THEN
3072
3073 IF (p_evh_rec.DURATION = FND_API.g_miss_num
3074 AND p_complete_rec.DURATION IS NULL)
3075 OR p_evh_rec.DURATION IS NULL
3076 THEN
3077 AMS_Utility_PVT.error_message('AMS_EVO_NO_DUR_WITH_CODE');
3078 x_return_status := FND_API.g_ret_sts_error;
3079 return;
3080 END IF;
3081 END IF;
3082 -- added sugupta 07/20/2000 if budget amount's there, there has to be currency code
3083 IF (AMS_DEBUG_HIGH_ON) THEN
3084
3085 AMS_Utility_PVT.debug_message('p_complete_rec.FUND_AMOUNT_TC:'||p_complete_rec.FUND_AMOUNT_TC);
3086 END IF;
3087 IF (AMS_DEBUG_HIGH_ON) THEN
3088
3089 AMS_Utility_PVT.debug_message('p_evh_rec.FUND_AMOUNT_TC:'||p_evh_rec.FUND_AMOUNT_TC);
3090 END IF;
3091 IF (AMS_DEBUG_HIGH_ON) THEN
3092
3093 AMS_Utility_PVT.debug_message('p_complete_rec.CURRENCY_CODE_TC:'||nvl(p_complete_rec.CURRENCY_CODE_TC,'NULL'));
3094 END IF;
3095 IF (AMS_DEBUG_HIGH_ON) THEN
3096
3097 AMS_Utility_PVT.debug_message('p_evh_rec.CURRENCY_CODE_TC:'||nvl(p_evh_rec.CURRENCY_CODE_TC, 'NULL') );
3098 END IF;
3099 IF p_evh_rec.FUND_AMOUNT_TC <> FND_API.g_miss_num
3100 OR p_complete_rec.FUND_AMOUNT_TC IS NOT NULL THEN
3101
3102 IF p_evh_rec.CURRENCY_CODE_TC = FND_API.g_miss_char
3103 AND p_complete_rec.CURRENCY_CODE_TC IS NULL THEN
3104 AMS_Utility_PVT.error_message('AMS_CAMP_BUDGET_NO_CURRENCY'); -- reusing campaign message
3105 x_return_status := FND_API.g_ret_sts_error;
3106 return;
3107 END IF;
3108 END IF;
3109
3110
3111 END check_evh_record;
3112
3113
3114 ---------------------------------------------------------------------
3115 -- PROCEDURE
3116 -- init_evh_rec
3117 --
3118 -- HISTORY
3119 -- 11/18/1999 gdeodhar Created.
3120 -- 12/18/1999 rvaka updated.
3121 ---------------------------------------------------------------------
3122 PROCEDURE init_evh_rec(
3123 x_evh_rec OUT NOCOPY evh_rec_type
3124 )
3125 IS
3126 BEGIN
3127
3128 x_evh_rec.event_header_id := FND_API.g_miss_num;
3129 x_evh_rec.last_update_date := FND_API.g_miss_date;
3130 x_evh_rec.last_updated_by := FND_API.g_miss_num;
3131 x_evh_rec.creation_date := FND_API.g_miss_date;
3132 x_evh_rec.created_by := FND_API.g_miss_num;
3133 x_evh_rec.last_update_login := FND_API.g_miss_num;
3134 x_evh_rec.object_version_number := FND_API.g_miss_num;
3135 x_evh_rec.event_level := FND_API.g_miss_char;
3136 x_evh_rec.application_id := FND_API.g_miss_num;
3137 x_evh_rec.event_type_code := FND_API.g_miss_char;
3138 x_evh_rec.active_flag := FND_API.g_miss_char;
3139 x_evh_rec.private_flag := FND_API.g_miss_char;
3140 x_evh_rec.user_status_id := FND_API.g_miss_num;
3141 x_evh_rec.system_status_code := FND_API.g_miss_char;
3142 x_evh_rec.last_status_date := FND_API.g_miss_date;
3143 x_evh_rec.stream_type_code := FND_API.g_miss_char;
3144 x_evh_rec.source_code := FND_API.g_miss_char;
3145 x_evh_rec.event_standalone_flag := FND_API.g_miss_char;
3146 x_evh_rec.day_of_event := FND_API.g_miss_char;
3147 x_evh_rec.agenda_start_time := FND_API.g_miss_date;
3148 x_evh_rec.agenda_end_time := FND_API.g_miss_date;
3149 x_evh_rec.reg_required_flag := FND_API.g_miss_char;
3150 x_evh_rec.reg_charge_flag := FND_API.g_miss_char;
3151 x_evh_rec.reg_invited_only_flag := FND_API.g_miss_char;
3152 x_evh_rec.partner_flag := FND_API.g_miss_char;
3153 x_evh_rec.overflow_flag := FND_API.g_miss_char;
3154 x_evh_rec.parent_event_header_id := FND_API.g_miss_num;
3155 x_evh_rec.duration := FND_API.g_miss_num;
3156 x_evh_rec.duration_uom_code := FND_API.g_miss_char;
3157 x_evh_rec.active_from_date := FND_API.g_miss_date;
3158 x_evh_rec.active_to_date := FND_API.g_miss_date;
3159 x_evh_rec.reg_maximum_capacity := FND_API.g_miss_num;
3160 x_evh_rec.reg_minimum_capacity := FND_API.g_miss_num;
3161 x_evh_rec.main_language_code := FND_API.g_miss_char;
3162 x_evh_rec.cert_credit_type_code := FND_API.g_miss_char;
3163 x_evh_rec.certification_credits := FND_API.g_miss_num;
3164 x_evh_rec.inventory_item_id := FND_API.g_miss_num;
3165 x_evh_rec.org_id := FND_API.g_miss_num;
3166 x_evh_rec.forecasted_revenue := FND_API.g_miss_num;
3167 x_evh_rec.actual_revenue := FND_API.g_miss_num;
3168 x_evh_rec.forecasted_cost := FND_API.g_miss_num;
3169 x_evh_rec.actual_cost := FND_API.g_miss_num;
3170 x_evh_rec.coordinator_id := FND_API.g_miss_num;
3171 x_evh_rec.fund_source_type_code := FND_API.g_miss_char;
3172 x_evh_rec.fund_source_id := FND_API.g_miss_num;
3173 x_evh_rec.fund_amount_tc := FND_API.g_miss_num;
3174 x_evh_rec.fund_amount_fc := FND_API.g_miss_num;
3175 x_evh_rec.currency_code_tc := FND_API.g_miss_char;
3176 x_evh_rec.currency_code_fc := FND_API.g_miss_char;
3177 x_evh_rec.owner_user_id := FND_API.g_miss_num;
3178 x_evh_rec.url := FND_API.g_miss_char;
3179 x_evh_rec.phone := FND_API.g_miss_char;
3180 x_evh_rec.email := FND_API.g_miss_char;
3181 x_evh_rec.priority_type_code := FND_API.g_miss_char;
3182 x_evh_rec.cancellation_reason_code := FND_API.g_miss_char;
3183 x_evh_rec.inbound_script_name := FND_API.g_miss_char;
3184 x_evh_rec.attribute_category := FND_API.g_miss_char;
3185 x_evh_rec.attribute1 := FND_API.g_miss_char;
3186 x_evh_rec.attribute2 := FND_API.g_miss_char;
3187 x_evh_rec.attribute3 := FND_API.g_miss_char;
3188 x_evh_rec.attribute4 := FND_API.g_miss_char;
3189 x_evh_rec.attribute5 := FND_API.g_miss_char;
3190 x_evh_rec.attribute6 := FND_API.g_miss_char;
3191 x_evh_rec.attribute7 := FND_API.g_miss_char;
3192 x_evh_rec.attribute8 := FND_API.g_miss_char;
3193 x_evh_rec.attribute9 := FND_API.g_miss_char;
3194 x_evh_rec.attribute10 := FND_API.g_miss_char;
3195 x_evh_rec.attribute11 := FND_API.g_miss_char;
3196 x_evh_rec.attribute12 := FND_API.g_miss_char;
3197 x_evh_rec.attribute13 := FND_API.g_miss_char;
3198 x_evh_rec.attribute14 := FND_API.g_miss_char;
3199 x_evh_rec.attribute15 := FND_API.g_miss_char;
3200 x_evh_rec.event_header_name := FND_API.g_miss_char;
3201 x_evh_rec.event_mktg_message := FND_API.g_miss_char;
3202 x_evh_rec.description := FND_API.g_miss_char;
3203 x_evh_rec.custom_setup_id := FND_API.g_miss_num;
3204 x_evh_rec.country_code := FND_API.g_miss_char;
3205 x_evh_rec.business_unit_id := FND_API.g_miss_num;
3206 x_evh_rec.event_calendar := FND_API.g_miss_char;
3207 x_evh_rec.start_period_name := FND_API.g_miss_char;
3208 x_evh_rec.end_period_name := FND_API.g_miss_char;
3209 x_evh_rec.global_flag := FND_API.g_miss_char;
3210 x_evh_rec.task_id := FND_API.g_miss_num;
3211 x_evh_rec.program_id := FND_API.g_miss_num;
3212 x_evh_rec.CREATE_ATTENDANT_LEAD_FLAG := FND_API.g_miss_char; /*hornet*/
3213 x_evh_rec.CREATE_REGISTRANT_LEAD_FLAG := FND_API.g_miss_char;/*hornet*/
3214 x_evh_rec.event_purpose_code := FND_API.g_miss_char;/* Hornet : added aug13*/
3215
3216 END init_evh_rec;
3217
3218
3219 ---------------------------------------------------------------------
3220 -- PROCEDURE
3221 -- check_evh_inter_entity
3222 --
3223 -- HISTORY
3224 --
3225 ---------------------------------------------------------------------
3226 PROCEDURE check_evh_inter_entity(
3227 p_evh_rec IN evh_rec_type,
3228 p_complete_rec IN evh_rec_type,
3229 p_validation_mode IN VARCHAR2,
3230 x_return_status OUT NOCOPY VARCHAR2
3231 )
3232 IS
3233
3234 l_return_status VARCHAR2(1);
3235
3236 BEGIN
3237
3238 x_return_status := FND_API.g_ret_sts_success;
3239
3240 ------------------- check fund source ----------------------
3241 -- no need to check for event_level=MAIN
3242 IF p_evh_rec.fund_source_type_code <> FND_API.g_miss_char
3243 OR p_evh_rec.fund_source_id <> FND_API.g_miss_num
3244 THEN
3245 AMS_EvhRules_PVT.check_evh_fund_source(
3246 p_complete_rec.fund_source_type_code,
3247 p_complete_rec.fund_source_id,
3248 l_return_status
3249 );
3250 IF l_return_status <> FND_API.g_ret_sts_success THEN
3251 x_return_status := l_return_status;
3252 END IF;
3253 END IF;
3254
3255 IF (AMS_DEBUG_HIGH_ON) THEN
3256
3257
3258
3259 AMS_Utility_PVT.debug_message('start_date' ||p_evh_rec.active_from_date);
3260
3261 END IF;
3262 IF (AMS_DEBUG_HIGH_ON) THEN
3263
3264 AMS_Utility_PVT.debug_message('end_date' ||p_evh_rec.active_to_date);
3265 END IF;
3266
3267 ------------------- check calendar ----------------------
3268 IF p_evh_rec.event_calendar <> FND_API.g_miss_char
3269 OR p_evh_rec.start_period_name <> FND_API.g_miss_char
3270 OR p_evh_rec.end_period_name <> FND_API.g_miss_char
3271 OR p_evh_rec.active_from_date <> FND_API.g_miss_date
3272 OR p_evh_rec.active_to_date <> FND_API.g_miss_date
3273 THEN
3274 AMS_EvhRules_PVT.check_evh_calendar(
3275 p_complete_rec.event_calendar,
3276 p_complete_rec.start_period_name,
3277 p_complete_rec.end_period_name,
3278 p_complete_rec.active_from_date,
3279 p_complete_rec.active_to_date,
3280 l_return_status
3281 );
3282 IF l_return_status <> FND_API.g_ret_sts_success THEN
3283 x_return_status := l_return_status;
3284 END IF;
3285
3286 null;
3287 END IF;
3288 ------------------------------Source code-------------------
3289 /* IF ((p_evh_rec.source_code IS NOT NULL) AND (p_evh_rec.source_code <> p_complete_rec.source_code)) THEN
3290 IF AMS_Utility_PVT.check_uniqueness(
3291 'ams_source_codes',
3292 'source_code = ''' || p_evh_rec.source_code ||
3293 ''' AND active_flag = ''Y'''
3294 ) = FND_API.g_false
3295 THEN
3296 AMS_Utility_PVT.Error_Message('AMS_CAMP_DUPLICATE_CODE');
3297 x_return_status := FND_API.g_ret_sts_error;
3298 RETURN;
3299 END IF;
3300 END IF;
3301 */
3302 END check_evh_inter_entity;
3303
3304 ---------------------------------------------------------------------
3305 -- PROCEDURE
3306 -- complete_evh_rec
3307 --
3308 -- HISTORY
3309 -- 11/18/1999 gdeodhar Created.
3310 ---------------------------------------------------------------------
3311 PROCEDURE complete_evh_rec(
3312 p_evh_rec IN evh_rec_type,
3313 x_complete_rec OUT NOCOPY evh_rec_type
3314 )
3315 IS
3316 l_api_version CONSTANT NUMBER := 1.0;
3317 l_api_name CONSTANT VARCHAR2(30) := 'complete evh rec';
3318 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
3319 x_msg_count number;
3320 x_msg_data varchar2(240);
3321 x_return_status varchar2(240);
3322
3323 CURSOR c_evh IS
3324 SELECT *
3325 FROM ams_event_headers_vl
3326 WHERE event_header_id = p_evh_rec.event_header_id;
3327
3328 l_evh_rec c_evh%ROWTYPE;
3329
3330 -- modified sugupta 08/13/2000 since ams_event_headers_vl doesnt have setup_id, and dont want to
3331 -- change odf files now.. add new cursor to get setup from table...
3332 -- need to change ams_event_headers_vl later to include setup_id
3333 CURSOR c_setup IS
3334 select setup_type_id
3335 from ams_event_headers_all_b
3336 where event_header_id = p_evh_rec.event_header_id;
3337
3338 l_setup NUMBER;
3339
3340 BEGIN
3341 IF (AMS_DEBUG_HIGH_ON) THEN
3342
3343 AMS_Utility_PVT.debug_message('compelte_evh' ||':inside compelte');
3344 END IF;
3345 x_complete_rec := p_evh_rec;
3346
3347 OPEN c_evh;
3348 FETCH c_evh INTO l_evh_rec;
3349 IF c_evh%NOTFOUND THEN
3350 CLOSE c_evh;
3351 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3352 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
3353 FND_MSG_PUB.add;
3354 END IF;
3355 RAISE FND_API.g_exc_error;
3356 END IF;
3357 CLOSE c_evh;
3358
3359 OPEN c_setup;
3360 FETCH c_setup INTO l_setup;
3361 IF c_setup%NOTFOUND THEN
3362 CLOSE c_setup;
3363 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3364 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
3365 FND_MSG_PUB.add;
3366 END IF;
3367 RAISE FND_API.g_exc_error;
3368 END IF;
3369 CLOSE c_setup;
3370
3371 -- This procedure should complete the record by going through all the items in the incoming record.
3372 -- Somewhere it must be checked however if certain fields can be or cannot be updated by the user based on the status of the event.
3373 -- For example, if the event is in active stage, the user will not be able to update the Marketing Message or budget related columns.
3374
3375 -- adding code to complete setup_type_id ( custom_setup_id in evo_rec)
3376 IF p_evh_rec.custom_setup_id = FND_API.g_miss_num THEN
3377 x_complete_rec.custom_setup_id := l_setup;
3378 END IF;
3379
3380 IF p_evh_rec.event_level = FND_API.g_miss_char THEN
3381 x_complete_rec.event_level := l_evh_rec.event_level;
3382 END IF;
3383
3384 IF p_evh_rec.application_id = FND_API.g_miss_num THEN
3385 x_complete_rec.application_id := l_evh_rec.application_id;
3386 END IF;
3387
3388 IF p_evh_rec.event_type_code = FND_API.g_miss_char THEN
3389 x_complete_rec.event_type_code := l_evh_rec.event_type_code;
3390 END IF;
3391
3392 IF p_evh_rec.active_flag = FND_API.g_miss_char THEN
3393 x_complete_rec.active_flag := l_evh_rec.active_flag;
3394 END IF;
3395
3396 IF p_evh_rec.private_flag = FND_API.g_miss_char THEN
3397 x_complete_rec.private_flag := l_evh_rec.private_flag;
3398 END IF;
3399
3400 IF p_evh_rec.user_status_id = FND_API.g_miss_num THEN
3401 x_complete_rec.user_status_id := l_evh_rec.user_status_id;
3402 END IF;
3403
3404 IF p_evh_rec.system_status_code = FND_API.g_miss_char THEN
3405 x_complete_rec.system_status_code := l_evh_rec.system_status_code;
3406 END IF;
3407
3408 IF p_evh_rec.last_status_date = FND_API.g_miss_date
3409 OR p_evh_rec.last_status_date IS NULL
3410 THEN
3411 IF p_evh_rec.user_status_id = l_evh_rec.user_status_id THEN
3412 -- no status change, set it to be the original value
3413 x_complete_rec.last_status_date := l_evh_rec.last_status_date;
3414 ELSE
3415 -- status changed, set it to be SYSDATE
3416 x_complete_rec.last_status_date := SYSDATE;
3417 END IF;
3418 END IF;
3419
3420 IF p_evh_rec.stream_type_code = FND_API.g_miss_char THEN
3421 x_complete_rec.stream_type_code := l_evh_rec.stream_type_code;
3422 END IF;
3423
3424 -- sugupta 03/29/00 source_code can be updated by the user until status is not active.
3425 -- if it is passed from screen, it will be validated under check_evh_update
3426
3427 IF p_evh_rec.source_code = FND_API.g_miss_char THEN
3428 x_complete_rec.source_code := l_evh_rec.source_code;
3429 END IF;
3430
3431 IF p_evh_rec.event_standalone_flag = FND_API.g_miss_char THEN
3432 x_complete_rec.event_standalone_flag := l_evh_rec.event_standalone_flag;
3433 END IF;
3434
3435 IF p_evh_rec.day_of_event = FND_API.g_miss_char THEN
3436 x_complete_rec.day_of_event := l_evh_rec.day_of_event;
3437 END IF;
3438
3439 IF p_evh_rec.agenda_start_time = FND_API.g_miss_date THEN
3440 x_complete_rec.agenda_start_time := l_evh_rec.agenda_start_time;
3441 END IF;
3442
3443 IF p_evh_rec.agenda_end_time = FND_API.g_miss_date THEN
3444 x_complete_rec.agenda_end_time := l_evh_rec.agenda_end_time;
3445 END IF;
3446
3447 IF p_evh_rec.reg_required_flag = FND_API.g_miss_char THEN
3448 x_complete_rec.reg_required_flag := l_evh_rec.reg_required_flag;
3449 END IF;
3450
3451 IF p_evh_rec.reg_charge_flag = FND_API.g_miss_char THEN
3452 x_complete_rec.reg_charge_flag := l_evh_rec.reg_charge_flag;
3453 END IF;
3454
3455 IF p_evh_rec.reg_invited_only_flag = FND_API.g_miss_char THEN
3456 x_complete_rec.reg_invited_only_flag := l_evh_rec.reg_invited_only_flag;
3457 END IF;
3458
3459 IF p_evh_rec.partner_flag = FND_API.g_miss_char THEN
3460 x_complete_rec.partner_flag := l_evh_rec.partner_flag;
3461 END IF;
3462
3463 IF p_evh_rec.overflow_flag = FND_API.g_miss_char THEN
3464 x_complete_rec.overflow_flag := l_evh_rec.overflow_flag;
3465 END IF;
3466
3467 IF p_evh_rec.parent_event_header_id = FND_API.g_miss_num THEN
3468 x_complete_rec.parent_event_header_id := l_evh_rec.parent_event_header_id;
3469 END IF;
3470
3471 IF p_evh_rec.duration = FND_API.g_miss_num THEN
3472 x_complete_rec.duration := l_evh_rec.duration;
3473 END IF;
3474
3475 IF p_evh_rec.duration_uom_code = FND_API.g_miss_char THEN
3476 x_complete_rec.duration_uom_code := l_evh_rec.duration_uom_code;
3477 END IF;
3478
3479 IF p_evh_rec.active_from_date = FND_API.g_miss_date THEN
3480 x_complete_rec.active_from_date := l_evh_rec.active_from_date;
3481 END IF;
3482
3483 IF p_evh_rec.active_to_date = FND_API.g_miss_date THEN
3484 x_complete_rec.active_to_date := l_evh_rec.active_to_date;
3485 END IF;
3486
3487 IF p_evh_rec.reg_maximum_capacity = FND_API.g_miss_num THEN
3488 x_complete_rec.reg_maximum_capacity := l_evh_rec.reg_maximum_capacity;
3489 END IF;
3490
3491 IF p_evh_rec.reg_minimum_capacity = FND_API.g_miss_num THEN
3492 x_complete_rec.reg_minimum_capacity := l_evh_rec.reg_minimum_capacity;
3493 END IF;
3494
3495 IF p_evh_rec.main_language_code = FND_API.g_miss_char THEN
3496 x_complete_rec.main_language_code := l_evh_rec.main_language_code;
3497 END IF;
3498
3499 IF p_evh_rec.cert_credit_type_code = FND_API.g_miss_char THEN
3500 x_complete_rec.cert_credit_type_code := l_evh_rec.cert_credit_type_code;
3501 END IF;
3502
3503 IF p_evh_rec.certification_credits = FND_API.g_miss_num THEN
3504 x_complete_rec.certification_credits := l_evh_rec.certification_credits;
3505 END IF;
3506
3507 IF p_evh_rec.inventory_item_id = FND_API.g_miss_num THEN
3508 x_complete_rec.inventory_item_id := l_evh_rec.inventory_item_id;
3509 END IF;
3510
3511 IF p_evh_rec.organization_id = FND_API.g_miss_num THEN
3512 x_complete_rec.organization_id := l_evh_rec.organization_id;
3513 END IF;
3514
3515 IF p_evh_rec.org_id = FND_API.g_miss_num THEN
3516 x_complete_rec.org_id := l_evh_rec.org_id;
3517 END IF;
3518
3519 IF p_evh_rec.forecasted_revenue = FND_API.g_miss_num THEN
3520 x_complete_rec.forecasted_revenue := l_evh_rec.forecasted_revenue;
3521 END IF;
3522
3523 IF p_evh_rec.actual_revenue = FND_API.g_miss_num THEN
3524 x_complete_rec.actual_revenue := l_evh_rec.actual_revenue;
3525 END IF;
3526
3527 IF p_evh_rec.forecasted_cost = FND_API.g_miss_num THEN
3528 x_complete_rec.forecasted_cost := l_evh_rec.forecasted_cost;
3529 END IF;
3530
3531 IF p_evh_rec.actual_cost = FND_API.g_miss_num THEN
3532 x_complete_rec.actual_cost := l_evh_rec.actual_cost;
3533 END IF;
3534
3535 IF p_evh_rec.coordinator_id = FND_API.g_miss_num THEN
3536 x_complete_rec.coordinator_id := l_evh_rec.coordinator_id;
3537 END IF;
3538
3539 IF p_evh_rec.fund_source_type_code = FND_API.g_miss_char THEN
3540 x_complete_rec.fund_source_type_code := l_evh_rec.fund_source_type_code;
3541 END IF;
3542
3543 IF p_evh_rec.fund_source_id = FND_API.g_miss_num THEN
3544 x_complete_rec.fund_source_id := l_evh_rec.fund_source_id;
3545 END IF;
3546
3547 IF p_evh_rec.fund_amount_tc = FND_API.g_miss_num THEN
3548 x_complete_rec.fund_amount_tc := l_evh_rec.fund_amount_tc;
3549 END IF;
3550
3551 IF p_evh_rec.fund_amount_fc = FND_API.g_miss_num THEN
3552 x_complete_rec.fund_amount_fc := l_evh_rec.fund_amount_fc;
3553 END IF;
3554
3555 IF p_evh_rec.currency_code_tc = FND_API.g_miss_char THEN
3556 x_complete_rec.currency_code_tc := l_evh_rec.currency_code_tc;
3557 END IF;
3558
3559 IF p_evh_rec.currency_code_fc = FND_API.g_miss_char THEN
3560 x_complete_rec.currency_code_fc := l_evh_rec.currency_code_fc;
3561 END IF;
3562
3563 IF p_evh_rec.owner_user_id = FND_API.g_miss_num THEN
3564 x_complete_rec.owner_user_id := l_evh_rec.owner_user_id;
3565 END IF;
3566
3567 IF p_evh_rec.url = FND_API.g_miss_char THEN
3568 x_complete_rec.url := l_evh_rec.url;
3569 END IF;
3570
3571 IF p_evh_rec.phone = FND_API.g_miss_char THEN
3572 x_complete_rec.phone := l_evh_rec.phone;
3573 END IF;
3574
3575 IF p_evh_rec.email = FND_API.g_miss_char THEN
3576 x_complete_rec.email := l_evh_rec.email;
3577 END IF;
3578
3579 IF p_evh_rec.priority_type_code = FND_API.g_miss_char THEN
3580 x_complete_rec.priority_type_code := l_evh_rec.priority_type_code;
3581 END IF;
3582
3583 IF p_evh_rec.cancellation_reason_code = FND_API.g_miss_char THEN
3584 x_complete_rec.cancellation_reason_code := l_evh_rec.cancellation_reason_code;
3585 END IF;
3586
3587 IF p_evh_rec.inbound_script_name = FND_API.g_miss_char THEN
3588 x_complete_rec.inbound_script_name := l_evh_rec.inbound_script_name;
3589 END IF;
3590
3591 IF p_evh_rec.attribute_category = FND_API.g_miss_char THEN
3592 x_complete_rec.attribute_category := l_evh_rec.attribute_category;
3593 END IF;
3594
3595 IF p_evh_rec.attribute1 = FND_API.g_miss_char THEN
3596 x_complete_rec.attribute1 := l_evh_rec.attribute1;
3597 END IF;
3598
3599 IF p_evh_rec.attribute2 = FND_API.g_miss_char THEN
3600 x_complete_rec.attribute2 := l_evh_rec.attribute2;
3601 END IF;
3602
3603 IF p_evh_rec.attribute3 = FND_API.g_miss_char THEN
3604 x_complete_rec.attribute3 := l_evh_rec.attribute3;
3605 END IF;
3606
3607 IF p_evh_rec.attribute4 = FND_API.g_miss_char THEN
3608 x_complete_rec.attribute4 := l_evh_rec.attribute4;
3609 END IF;
3610
3611 IF p_evh_rec.attribute5 = FND_API.g_miss_char THEN
3612 x_complete_rec.attribute5 := l_evh_rec.attribute5;
3613 END IF;
3614
3615 IF p_evh_rec.attribute6 = FND_API.g_miss_char THEN
3616 x_complete_rec.attribute6 := l_evh_rec.attribute6;
3617 END IF;
3618
3619 IF p_evh_rec.attribute7 = FND_API.g_miss_char THEN
3620 x_complete_rec.attribute7 := l_evh_rec.attribute7;
3621 END IF;
3622
3623 IF p_evh_rec.attribute8 = FND_API.g_miss_char THEN
3624 x_complete_rec.attribute8 := l_evh_rec.attribute8;
3625 END IF;
3626
3627 IF p_evh_rec.attribute9 = FND_API.g_miss_char THEN
3628 x_complete_rec.attribute9 := l_evh_rec.attribute9;
3629 END IF;
3630
3631 IF p_evh_rec.attribute10 = FND_API.g_miss_char THEN
3632 x_complete_rec.attribute10 := l_evh_rec.attribute10;
3633 END IF;
3634
3635 IF p_evh_rec.attribute11 = FND_API.g_miss_char THEN
3636 x_complete_rec.attribute11 := l_evh_rec.attribute11;
3637 END IF;
3638
3639 IF p_evh_rec.attribute12 = FND_API.g_miss_char THEN
3640 x_complete_rec.attribute12 := l_evh_rec.attribute12;
3641 END IF;
3642
3643 IF p_evh_rec.attribute13 = FND_API.g_miss_char THEN
3644 x_complete_rec.attribute13 := l_evh_rec.attribute13;
3645 END IF;
3646
3647 IF p_evh_rec.attribute14 = FND_API.g_miss_char THEN
3648 x_complete_rec.attribute14 := l_evh_rec.attribute14;
3649 END IF;
3650
3651 IF p_evh_rec.attribute15 = FND_API.g_miss_char THEN
3652 x_complete_rec.attribute15 := l_evh_rec.attribute15;
3653 END IF;
3654
3655 IF p_evh_rec.event_header_name = FND_API.g_miss_char THEN
3656 x_complete_rec.event_header_name := l_evh_rec.event_header_name;
3657 END IF;
3658
3659 IF p_evh_rec.event_mktg_message = FND_API.g_miss_char THEN
3660 x_complete_rec.event_mktg_message := l_evh_rec.event_mktg_message;
3661 END IF;
3662
3663 IF p_evh_rec.description = FND_API.g_miss_char THEN
3664 x_complete_rec.description := l_evh_rec.description;
3665 END IF;
3666
3667 IF p_evh_rec.country_code = FND_API.g_miss_char THEN
3668 x_complete_rec.country_code := l_evh_rec.country_code;
3669 END IF;
3670
3671 IF p_evh_rec.business_unit_id = FND_API.g_miss_num THEN
3672 x_complete_rec.business_unit_id := l_evh_rec.business_unit_id;
3673 END IF;
3674 IF p_evh_rec.event_calendar = FND_API.g_miss_char THEN
3675 x_complete_rec.event_calendar := FND_PROFILE.value('AMS_CAMPAIGN_DEFAULT_CALENDER');
3676 END IF;
3677
3678 IF p_evh_rec.start_period_name = FND_API.g_miss_char THEN
3679 x_complete_rec.start_period_name := l_evh_rec.start_period_name;
3680 END IF;
3681
3682 IF p_evh_rec.end_period_name = FND_API.g_miss_char THEN
3683 x_complete_rec.end_period_name := l_evh_rec.end_period_name;
3684 END IF;
3685
3686 IF p_evh_rec.global_flag = FND_API.g_miss_char THEN
3687 x_complete_rec.global_flag := l_evh_rec.global_flag;
3688 END IF;
3689
3690 IF p_evh_rec.task_id = FND_API.g_miss_num THEN
3691 x_complete_rec.task_id := l_evh_rec.task_id;
3692 END IF;
3693 IF p_evh_rec.program_id = FND_API.g_miss_num THEN
3694 x_complete_rec.program_id := l_evh_rec.program_id;
3695 END IF;
3696 IF p_evh_rec.CREATE_ATTENDANT_LEAD_FLAG = FND_API.g_miss_char THEN
3697 x_complete_rec.CREATE_ATTENDANT_LEAD_FLAG := l_evh_rec.CREATE_ATTENDANT_LEAD_FLAG;
3698 END IF;
3699 IF p_evh_rec.CREATE_REGISTRANT_LEAD_FLAG = FND_API.g_miss_char THEN
3700 x_complete_rec.CREATE_REGISTRANT_LEAD_FLAG := l_evh_rec.CREATE_REGISTRANT_LEAD_FLAG;
3701 END IF;
3702
3703 IF p_evh_rec.event_purpose_code = FND_API.g_miss_char THEN
3704 x_complete_rec.event_purpose_code := l_evh_rec.event_purpose_code; /* Hornet : added aug13*/
3705 END IF;
3706
3707 --EXCEPTION
3708 --WHEN OTHERS THEN
3709 -- ROLLBACK TO update_campaign;
3710 --x_return_status := FND_API.g_ret_sts_unexp_error;
3711
3712 --IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
3713 --THEN
3714 --FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3715 --END IF;
3716
3717 --FND_MSG_PUB.count_and_get(
3718 -- p_encoded => FND_API.g_false,
3719 -- p_count => x_msg_count,
3720 -- p_data => x_msg_data
3721 --);
3722
3723
3724 END complete_evh_rec;
3725
3726
3727 ---------------------------------------------------------------------
3728 -- PROCEDURE
3729 -- unit_test_update
3730 --
3731 -- HISTORY
3732 -- 01/19/2000 gdeodhar Created.
3733 ---------------------------------------------------------------------
3734 PROCEDURE unit_test_update
3735 IS
3736
3737 l_evh_rec AMS_EVENTHEADER_PVT.evh_rec_type;
3738 l_return_status VARCHAR2(1);
3739 l_msg_count NUMBER;
3740 l_msg_data VARCHAR2(200);
3741 l_evh_id AMS_EVENT_HEADERS_ALL_B.event_header_id%type;
3742
3743 BEGIN
3744
3745 l_evh_rec.event_header_id := 10009 ;
3746 l_evh_rec.object_version_number := 1 ;
3747 l_evh_rec.day_of_event := 'ONE' ;
3748 l_evh_rec.stream_type_code := 'A' ;
3749 --l_evh_rec.agenda_start_time := to_date(to_char('1970-01-01 10:00:00.0', 'HH24:MI'), 'HH24:MI') ;
3750 --l_evh_rec.agenda_end_time := to_date(to_char('1970-01-01 10:30:00.0', 'HH24.MI'), 'HH24:MI') ;
3751 l_evh_rec.agenda_start_time := to_date('15:00', 'HH24:MI') ;
3752 l_evh_rec.agenda_end_time := to_date('13:00', 'HH24.MI') ;
3753 l_evh_rec.event_header_name := 'Test 2' ;
3754 l_evh_rec.user_status_id := 16 ;
3755 l_evh_rec.system_status_code := 'PLANNING' ;
3756 l_evh_rec.application_id := 530 ;
3757
3758 AMS_EVENTHEADER_PVT.update_event_header(
3759 p_api_version => 1.0 -- p_api_version
3760 ,p_init_msg_list => FND_API.G_FALSE
3761 ,p_commit => FND_API.G_FALSE
3762 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
3763 ,x_return_status => l_return_status
3764 ,x_msg_count => l_msg_count
3765 ,x_msg_data => l_msg_data
3766 ,p_evh_rec => l_evh_rec
3767 );
3768
3769 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3770 AMS_UTILITY_PVT.display_messages;
3771 ELSE
3772 commit work;
3773 AMS_UTILITY_PVT.display_messages;
3774 END IF;
3775
3776 END unit_test_update;
3777
3778 -------------------------------------------------------
3779
3780 -------------------------------------------------------------
3781 -- Check_Parent_Active
3782 -------------------------------------------------------------
3783 PROCEDURE Check_Parent_Active (
3784 p_evh_rec IN evh_rec_type,
3785 x_return_status OUT NOCOPY VARCHAR2
3786 )IS
3787 l_evh_rec evh_rec_type;
3788 l_system_status_code p_evh_rec.system_status_code%TYPE ;
3789
3790 CURSOR c_program IS
3791 SELECT status_code FROM ams_campaigns_v
3792 WHERE campaign_id = p_evh_rec.program_id;
3793
3794 BEGIN
3795 x_return_status := FND_API.g_ret_sts_success;
3796
3797 IF (p_evh_rec.event_level = 'MAIN' AND p_evh_rec.active_flag = 'Y' AND p_evh_rec.system_status_code = 'ACTIVE' ) THEN
3798
3799 OPEN c_program;
3800 FETCH c_program INTO l_system_status_code;
3801 CLOSE c_program;
3802
3803 IF l_system_status_code <> 'ACTIVE' THEN
3804 IF (AMS_DEBUG_HIGH_ON) THEN
3805
3806 Ams_Utility_Pvt.debug_message('The Parent is not Active');
3807 END IF;
3808 Fnd_Message.set_name('AMS', 'AMS_PROGRAM_NOT_ACTIVE');
3809 Fnd_Msg_Pub.ADD;
3810 x_return_status := Fnd_Api.g_ret_sts_error;
3811 RETURN;
3812 END IF;
3813
3814 END IF;
3815
3816 END Check_Parent_Active;
3817
3818
3819 -------------------------------------------------------------
3820 -- Check_Dates_Range
3821 -- History
3822 -- 07-feb-2003 dbiswas added code c_eveo validation for event-event_sched
3823 -- start and end dates
3824 -------------------------------------------------------------
3825 PROCEDURE Check_Dates_Range (
3826 p_evh_rec IN evh_rec_type,
3827 x_return_status OUT NOCOPY VARCHAR2
3828 )IS
3829 l_evh_rec evh_rec_type;
3830 l_start_date DATE;
3831 l_end_date DATE;
3832
3833 CURSOR c_program IS
3834 SELECT actual_exec_start_date , actual_exec_end_date FROM ams_campaigns_vl
3835 WHERE campaign_id = p_evh_rec.program_id;
3836
3837 CURSOR c_eveo IS
3838 SELECT event_start_date AS start_date,
3839 event_end_date AS end_date
3840 FROM ams_event_offers_vl
3841 WHERE event_header_id = p_evh_rec.event_header_id and system_status_code<> 'CANCELLED';--implemented ER2381975 by anchaudh.
3842
3843
3844 BEGIN
3845
3846 OPEN c_program;
3847 FETCH c_program INTO l_start_date,l_end_date;
3848 CLOSE c_program;
3849
3850 x_return_status := FND_API.g_ret_sts_success;
3851
3852 IF (p_evh_rec.active_from_date IS NOT NULL AND l_start_date IS NOT NULL ) THEN
3853 IF (p_evh_rec.active_from_date < l_start_date) THEN
3854 IF (AMS_DEBUG_HIGH_ON) THEN
3855
3856 Ams_Utility_Pvt.debug_message('The start date of Event can not be lesser than that of Program');
3857 END IF;
3858 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
3859 Fnd_Message.set_name('AMS', 'AMS_EVT_STDT_LS_PRG_STDT');
3860 Fnd_Msg_Pub.ADD;
3861 x_return_status := Fnd_Api.g_ret_sts_error;
3862 RETURN;
3863 END IF;
3864 END IF;
3865 END IF;
3866
3867 IF (p_evh_rec.active_to_date IS NOT NULL AND l_end_date IS NOT NULL ) THEN
3868 IF (p_evh_rec.active_to_date > l_end_date) THEN
3869 IF (AMS_DEBUG_HIGH_ON) THEN
3870
3871 Ams_Utility_Pvt.debug_message('The end date of Event can not be greater than that of Program');
3872 END IF;
3873 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
3874 Fnd_Message.set_name('AMS', 'AMS_EVT_EDDT_GT_PRG_EDDT');
3875 Fnd_Msg_Pub.ADD;
3876 x_return_status := Fnd_Api.g_ret_sts_error;
3877 RETURN;
3878 END IF;
3879 END IF;
3880 ELSE
3881 IF ( p_evh_rec.active_to_date IS NULL AND l_end_date IS NOT NULL ) THEN
3882 IF (AMS_DEBUG_HIGH_ON) THEN
3883
3884 Ams_Utility_Pvt.debug_message('The end date of Event can not be greater than that of Program');
3885 END IF;
3886 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
3887 Fnd_Message.set_name('AMS', 'AMS_EVT_EDDT_GT_PRG_EDDT');
3888 Fnd_Msg_Pub.ADD;
3889 x_return_status := Fnd_Api.g_ret_sts_error;
3890 RETURN;
3891 END IF;
3892 END IF;
3893 END IF;
3894
3895 FOR l_eveo_rec in c_eveo LOOP
3896 IF(p_evh_rec.active_from_date <>FND_API.g_miss_date OR l_eveo_rec.start_date<> FND_API.g_miss_date )THEN
3897 IF (p_evh_rec.active_from_date IS NOT NULL AND l_eveo_rec.start_date IS NOT NULL ) THEN
3898 IF (l_eveo_rec.start_date < p_evh_rec.active_from_date) THEN
3899 IF (AMS_DEBUG_HIGH_ON) THEN
3900 Ams_Utility_Pvt.debug_message('The start date of an Offer can not be lesser than that of Event');
3901 END IF;
3902 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
3903 Fnd_Message.set_name('AMS', 'AMS_EVEO_STDT_LS_EVT_STDT');
3904 Fnd_Msg_Pub.ADD;
3905 x_return_status := Fnd_Api.g_ret_sts_error;
3906 RETURN;
3907 END IF;
3908 END IF;
3909 ELSIF(p_evh_rec.active_from_date IS NULL AND l_eveo_rec.start_date IS NOT NULL) THEN
3910 IF (AMS_DEBUG_HIGH_ON) THEN
3911 Ams_Utility_Pvt.debug_message('The start date of an Offer can not be lesser than that of Event');
3912 END IF;
3913 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
3914 Fnd_Message.set_name('AMS', 'AMS_EVEO_STDT_LS_EVT_STDT');
3915 Fnd_Msg_Pub.ADD;
3916 x_return_status := Fnd_Api.g_ret_sts_error;
3917 RETURN;
3918 END IF;
3919 END IF;
3920 END IF;
3921
3922 IF(p_evh_rec.active_from_date <>FND_API.g_miss_date OR l_eveo_rec.start_date<> FND_API.g_miss_date )THEN
3923 IF (p_evh_rec.active_to_date IS NOT NULL AND l_eveo_rec.end_date IS NOT NULL ) THEN
3924 IF (p_evh_rec.active_to_date < l_eveo_rec.end_date) THEN
3925 IF (AMS_DEBUG_HIGH_ON) THEN
3926
3927 Ams_Utility_Pvt.debug_message('The end date of Event can not be lesser than that of Offer');
3928 END IF;
3929 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
3930 Fnd_Message.set_name('AMS', 'AMS_EVEO_EDDT_GT_EVT_EDDT');
3931 Fnd_Msg_Pub.ADD;
3932 x_return_status := Fnd_Api.g_ret_sts_error;
3933 RETURN;
3934 END IF;
3935 END IF;
3936 ELSIF ( p_evh_rec.active_to_date IS NOT NULL AND l_eveo_rec.end_date IS NULL ) THEN
3937 IF (AMS_DEBUG_HIGH_ON) THEN
3938
3939 Ams_Utility_Pvt.debug_message('The end date of Offer can not be greater than that of Event');
3940 END IF;
3941 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
3942 Fnd_Message.set_name('AMS', 'AMS_EVEO_EDDT_GT_EVT_EDDT');
3943 Fnd_Msg_Pub.ADD;
3944 x_return_status := Fnd_Api.g_ret_sts_error;
3945 RETURN;
3946 END IF;
3947 END IF;
3948 END IF;
3949 END LOOP;
3950 END Check_Dates_Range;
3951 --------------------------------------------------------------------
3952 -------------------------------------------------------------
3953 -- Update_Metrics
3954 -------------------------------------------------------------
3955 PROCEDURE Update_Metrics (
3956 p_evh_rec IN evh_rec_type,
3957 x_return_status OUT NOCOPY VARCHAR2,
3958 x_msg_count OUT NOCOPY VARCHAR2,
3959 x_msg_data OUT NOCOPY VARCHAR2
3960 ) IS
3961 l_program_id NUMBER;
3962 l_api_version CONSTANT NUMBER := 1.0;
3963
3964
3965 CURSOR c_program IS
3966
3967 SELECT program_id from ams_event_headers_v
3968 WHERE event_header_id = p_evh_rec.event_header_id;
3969 BEGIN
3970
3971 OPEN c_program;
3972 FETCH c_program INTO l_program_id;
3973 CLOSE c_program;
3974
3975 x_return_status := FND_API.g_ret_sts_success;
3976
3977 /* The AMS_ACTMETRIC_PVT.INVALIDATE_ROLLUP should be called
3978 1) When Program is removed or updated (changed)
3979 2) No need of calling when program is attached first time
3980 */
3981
3982 IF( l_program_id IS NOT NULL )THEN
3983 IF( l_program_id <> nvl(p_evh_rec.program_id,0))THEN
3984 AMS_ACTMETRIC_PVT.INVALIDATE_ROLLUP(
3985 p_api_version => l_api_version,
3986 p_init_msg_list => Fnd_Api.g_false,
3987 p_commit => Fnd_Api.G_FALSE,
3988
3989 x_return_status => x_return_status,
3990 x_msg_count => x_msg_count,
3991 x_msg_data => x_msg_data,
3992
3993 p_used_by_type => 'EVEH',
3994 p_used_by_id => p_evh_rec.event_header_id
3995 );
3996
3997 IF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
3998 RAISE Fnd_Api.g_exc_unexpected_error;
3999 ELSIF x_return_status = Fnd_Api.g_ret_sts_error THEN
4000 RAISE Fnd_Api.g_exc_error;
4001 END IF;
4002 END IF;
4003 END IF;
4004
4005
4006 END Update_Metrics;
4007
4008 ------------------------------------------------------------------------------------------
4009 END AMS_EventHeader_PVT;