[Home] [Help]
PACKAGE BODY: APPS.IGS_GR_VENUE_PKG
Source
1 package body IGS_GR_VENUE_PKG as
2 /* $Header: IGSGI19B.pls 115.10 2003/10/30 13:28:59 rghosh ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_GR_VENUE_ALL%RowType;
5 new_references IGS_GR_VENUE_ALL%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_venue_cd IN VARCHAR2 DEFAULT NULL,
11 x_exam_location_cd IN VARCHAR2 DEFAULT NULL,
12 x_description IN VARCHAR2 DEFAULT NULL,
13 x_number_of_seats IN NUMBER DEFAULT NULL,
14 x_booking_cost IN NUMBER DEFAULT NULL,
15 x_priority_cd IN NUMBER DEFAULT NULL,
16 x_supervisor_limit IN NUMBER DEFAULT NULL,
17 x_coord_person_id IN NUMBER DEFAULT NULL,
18 x_closed_ind IN VARCHAR2 DEFAULT NULL,
19 x_comments IN VARCHAR2 DEFAULT NULL,
20 x_resources_available IN VARCHAR2 DEFAULT NULL,
21 x_announcements IN VARCHAR2 DEFAULT NULL,
22 x_booking_information IN VARCHAR2 DEFAULT NULL,
23 x_seating_information IN VARCHAR2 DEFAULT NULL,
24 x_instructions IN VARCHAR2 DEFAULT NULL,
25 x_creation_date IN DATE DEFAULT NULL,
26 x_created_by IN NUMBER DEFAULT NULL,
27 x_last_update_date IN DATE DEFAULT NULL,
28 x_last_updated_by IN NUMBER DEFAULT NULL,
29 x_last_update_login IN NUMBER DEFAULT NULL ,
30 x_org_id IN NUMBER DEFAULT NULL
31 ) AS
32
33 CURSOR cur_old_ref_values IS
34 SELECT *
35 FROM IGS_GR_VENUE_ALL
36 WHERE rowid = x_rowid;
37
38 BEGIN
39
40 l_rowid := x_rowid;
41
42 -- Code for setting the Old and New Reference Values.
43 -- Populate Old Values.
44 Open cur_old_ref_values;
45 Fetch cur_old_ref_values INTO old_references;
46 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
47 Close cur_old_ref_values;
48 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
49 App_Exception.Raise_Exception;
50 Return;
51 END IF;
52 Close cur_old_ref_values;
53
54 -- Populate New Values.
55 new_references.venue_cd := x_venue_cd;
56 new_references.exam_location_cd := x_exam_location_cd;
57 new_references.description := x_description;
58 new_references.number_of_seats := x_number_of_seats;
59 new_references.booking_cost := x_booking_cost;
60 new_references.priority_cd := x_priority_cd;
61 new_references.supervisor_limit := x_supervisor_limit;
62 new_references.coord_person_id := x_coord_person_id;
63 new_references.closed_ind := x_closed_ind;
64 new_references.comments := x_comments;
65 new_references.resources_available := x_resources_available;
66 new_references.announcements := x_announcements;
67 new_references.booking_information := x_booking_information;
68 new_references.seating_information := x_seating_information;
69 new_references.instructions := x_instructions;
70 new_references.org_id := x_org_id;
71 IF (p_action = 'UPDATE') THEN
72 new_references.creation_date := old_references.creation_date;
73 new_references.created_by := old_references.created_by;
74 ELSE
75 new_references.creation_date := x_creation_date;
76 new_references.created_by := x_created_by;
77 END IF;
78 new_references.last_update_date := x_last_update_date;
79 new_references.last_updated_by := x_last_updated_by;
80 new_references.last_update_login := x_last_update_login;
81
82 END Set_Column_Values;
83
84 -- Trigger description :-
85 -- "OSS_TST".trg_ve_br_iu
86 -- BEFORE INSERT OR UPDATE
87 -- ON IGS_GR_VENUE_ALL
88 -- FOR EACH ROW
89
90 PROCEDURE BeforeRowInsertUpdate1(
91 p_inserting IN BOOLEAN DEFAULT FALSE,
92 p_updating IN BOOLEAN DEFAULT FALSE,
93 p_deleting IN BOOLEAN DEFAULT FALSE
94 ) AS
95 v_message_name VARCHAR2(30);
96 BEGIN
97 -- Validate that inserts are allowed
98 IF p_inserting THEN
99 -- <ve1>
100 -- Cannot create against location with s_location_type <> 'EXAM_CTR'
101 IF IGS_AS_VAL_VE.assp_val_ve_lot (
102 new_references.exam_location_cd,
103 v_message_name) = FALSE THEN
104 Fnd_Message.Set_Name('IGS', v_message_name);
105 App_Exception.Raise_Exception;
106 END IF;
107 -- <ve2>
108 -- Cannot created against closed exam_location
109 IF IGS_AS_VAL_ELS.ORGP_VAL_LOC_CLOSED (
110 new_references.exam_location_cd,
111 v_message_name) = FALSE THEN
112 Fnd_Message.Set_Name('IGS', v_message_name);
113 App_Exception.Raise_Exception;
114 END IF;
115 END IF;
116 -- Validate that inserts/updates are allowed
117 IF p_inserting OR p_updating THEN
118 -- <ve3>
119 -- Cannot re-open against closed exam_location
120 IF IGS_AS_VAL_VE.assp_val_ve_reopen (
121 new_references.exam_location_cd,
122 new_references.closed_ind,
123 v_message_name) = FALSE THEN
124 Fnd_Message.Set_Name('IGS', v_message_name);
125 App_Exception.Raise_Exception;
126 END IF;
127 -- <ve4>
128 -- Validate co-ordinator is a valid person
129 IF (new_references.coord_person_id IS NOT NULL and
130 new_references.coord_person_id <> old_references.coord_person_id) THEN
131 IF IGS_CO_VAL_OC.genp_val_prsn_id (
132 new_references.coord_person_id,
133 v_message_name) = FALSE THEN
134 Fnd_Message.Set_Name('IGS', v_message_name);
135 App_Exception.Raise_Exception;
136 END IF;
137 END IF;
138 END IF;
139
140
141 END BeforeRowInsertUpdate1;
142
143 PROCEDURE Check_Parent_Existance AS
144 BEGIN
145
146 IF (((old_references.exam_location_cd = new_references.exam_location_cd)) OR
147 ((new_references.exam_location_cd IS NULL))) THEN
148 NULL;
149 ELSE
150 IF NOT IGS_AD_LOCATION_PKG.Get_PK_For_Validation (
151 new_references.exam_location_cd,
152 'N'
153 ) THEN
154 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
155 APP_EXCEPTION.RAISE_EXCEPTION;
156 END IF;
157 END IF;
158
159 IF (((old_references.coord_person_id = new_references.coord_person_id)) OR
160 ((new_references.coord_person_id IS NULL))) THEN
161 NULL;
162 ELSE
163 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
164 new_references.coord_person_id
165 ) THEN
166 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
167 APP_EXCEPTION.RAISE_EXCEPTION;
168 END IF;
169
170 END IF;
171
172 END Check_Parent_Existance;
173
174 PROCEDURE Check_Child_Existance AS
175 BEGIN
176
177 IGS_AS_EXAM_INSTANCE_PKG.GET_FK_IGS_GR_VENUE (
178 old_references.venue_cd
179 );
180
181 IGS_AS_EXMVNU_SESAVL_PKG.GET_FK_IGS_GR_VENUE (
182 old_references.venue_cd
183 );
184
185 IGS_GR_CRMN_PKG.GET_FK_IGS_GR_VENUE (
186 old_references.venue_cd
187 );
188
189
190 /*IGS_GR_VENUE_ADDR_PKG.GET_FK_IGS_GR_VENUE (
191 old_references.venue_cd
192 );
193 */
194 END Check_Child_Existance;
195
196 FUNCTION Get_PK_For_Validation (
197 x_venue_cd IN VARCHAR2
198 ) RETURN BOOLEAN AS
199
200 CURSOR cur_rowid IS
201 SELECT rowid
202 FROM IGS_GR_VENUE_ALL
203 WHERE venue_cd = x_venue_cd
204 FOR UPDATE NOWAIT;
205
206 lv_rowid cur_rowid%RowType;
207
208 BEGIN
209
210 Open cur_rowid;
211 Fetch cur_rowid INTO lv_rowid;
212 IF (cur_rowid%FOUND) THEN
213 Close cur_rowid;
214 Return (TRUE);
215 ELSE
216 Close cur_rowid;
217 Return (FALSE);
218 END IF;
219
220 END Get_PK_For_Validation;
221
222 PROCEDURE CHECK_CONSTRAINTS(
223 Column_Name IN VARCHAR2 DEFAULT NULL,
224 Column_Value IN VARCHAR2 DEFAULT NULL
225 ) AS
226 BEGIN
227 IF Column_Name is null THEN
228 NULL;
229 ELSIF upper(Column_name) = 'SUPERVISOR_LIMIT' THEN
230 new_references.SUPERVISOR_LIMIT:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
231
232 ELSIF upper(Column_name) = 'CLOSED_IND' THEN
233 new_references.CLOSED_IND:= COLUMN_VALUE ;
234
235 ELSIF upper(Column_name) = 'EXAM_LOCATION_CD' THEN
236 new_references.EXAM_LOCATION_CD:= COLUMN_VALUE ;
237
238 ELSIF upper(Column_name) = 'VENUE_CD' THEN
239 new_references.VENUE_CD:= COLUMN_VALUE ;
240
241 ELSIF upper(Column_name) = 'BOOKING_COST' THEN
242 new_references.BOOKING_COST:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
243
244 ELSIF upper(Column_name) = 'NUMBER_OF_SEATS' THEN
245 new_references.NUMBER_OF_SEATS:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
246
247 ELSIF upper(Column_name) = 'PRIORITY_CD' THEN
248 new_references.PRIORITY_CD:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
249
250 END IF ;
251
252 IF upper(Column_name) = 'SUPERVISOR_LIMIT' OR COLUMN_NAME IS NULL THEN
253 IF new_references.SUPERVISOR_LIMIT < 0 or new_references.SUPERVISOR_LIMIT > 99 then
254 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
255 App_Exception.Raise_Exception ;
256 END IF;
257
258 END IF ;
259
260 IF upper(Column_name) = 'CLOSED_IND' OR COLUMN_NAME IS NULL THEN
261 IF new_references.CLOSED_IND not in ('Y','N') then
262 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
263 App_Exception.Raise_Exception ;
264 END IF;
265
266 END IF ;
267
268 IF upper(Column_name) = 'EXAM_LOCATION_CD' OR COLUMN_NAME IS NULL THEN
269 IF new_references.EXAM_LOCATION_CD<> upper(new_references.EXAM_LOCATION_CD) then
270 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
271 App_Exception.Raise_Exception ;
272 END IF;
273
274 END IF ;
275
276 IF upper(Column_name) = 'VENUE_CD' OR COLUMN_NAME IS NULL THEN
277 IF new_references.VENUE_CD<> upper(new_references.VENUE_CD) then
278 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
279 App_Exception.Raise_Exception ;
280 END IF;
281
282 END IF ;
283
284 IF upper(Column_name) = 'BOOKING_COST' OR COLUMN_NAME IS NULL THEN
285 IF new_references.BOOKING_COST < 0 or new_references.BOOKING_COST > 99999.99 then
286 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
287 App_Exception.Raise_Exception ;
288 END IF;
289
290 END IF ;
291
292 IF upper(Column_name) = 'NUMBER_OF_SEATS' OR COLUMN_NAME IS NULL THEN
293 IF new_references.NUMBER_OF_SEATS < 0 or new_references.NUMBER_OF_SEATS > 9999 then
294 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
295 App_Exception.Raise_Exception ;
296 END IF;
297
298 END IF ;
299
300 IF upper(Column_name) = 'PRIORITY_CD' OR COLUMN_NAME IS NULL THEN
301 IF new_references.PRIORITY_CD < 0 or new_references.PRIORITY_CD > 999 then
302 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
303 App_Exception.Raise_Exception ;
304 END IF;
305
306 END IF ;
307 END CHECK_CONSTRAINTS;
308
309 PROCEDURE GET_FK_IGS_AD_LOCATION (
310 x_location_cd IN VARCHAR2
311 ) AS
312
313 CURSOR cur_rowid IS
314 SELECT rowid
315 FROM IGS_GR_VENUE_ALL
316 WHERE exam_location_cd = x_location_cd ;
317
318 lv_rowid cur_rowid%RowType;
319
320 BEGIN
321
322 Open cur_rowid;
323 Fetch cur_rowid INTO lv_rowid;
324 IF (cur_rowid%FOUND) THEN
325 Close cur_rowid;
326 Fnd_Message.Set_Name ('IGS', 'IGS_GR_VE_LOC_FK');
327 App_Exception.Raise_Exception;
328 Return;
329 END IF;
330 Close cur_rowid;
331
332 END GET_FK_IGS_AD_LOCATION;
333
334 PROCEDURE GET_FK_IGS_PE_PERSON (
335 x_person_id IN VARCHAR2
336 ) AS
337
338 CURSOR cur_rowid IS
339 SELECT rowid
340 FROM IGS_GR_VENUE_ALL
341 WHERE coord_person_id = x_person_id ;
342
343 lv_rowid cur_rowid%RowType;
344
345 BEGIN
346
347 Open cur_rowid;
348 Fetch cur_rowid INTO lv_rowid;
349 IF (cur_rowid%FOUND) THEN
350 Close cur_rowid;
351 Fnd_Message.Set_Name ('IGS', 'IGS_GR_VE_PE_FK');
352 App_Exception.Raise_Exception;
353 Return;
354 END IF;
355 Close cur_rowid;
356
357 END GET_FK_IGS_PE_PERSON;
358
359 PROCEDURE Before_DML (
360 p_action IN VARCHAR2,
361 x_rowid IN VARCHAR2 DEFAULT NULL,
362 x_venue_cd IN VARCHAR2 DEFAULT NULL,
363 x_exam_location_cd IN VARCHAR2 DEFAULT NULL,
364 x_description IN VARCHAR2 DEFAULT NULL,
365 x_number_of_seats IN NUMBER DEFAULT NULL,
366 x_booking_cost IN NUMBER DEFAULT NULL,
367 x_priority_cd IN NUMBER DEFAULT NULL,
368 x_supervisor_limit IN NUMBER DEFAULT NULL,
369 x_coord_person_id IN NUMBER DEFAULT NULL,
370 x_closed_ind IN VARCHAR2 DEFAULT NULL,
371 x_comments IN VARCHAR2 DEFAULT NULL,
372 x_resources_available IN VARCHAR2 DEFAULT NULL,
373 x_announcements IN VARCHAR2 DEFAULT NULL,
374 x_booking_information IN VARCHAR2 DEFAULT NULL,
375 x_seating_information IN VARCHAR2 DEFAULT NULL,
376 x_instructions IN VARCHAR2 DEFAULT NULL,
377 x_creation_date IN DATE DEFAULT NULL,
378 x_created_by IN NUMBER DEFAULT NULL,
379 x_last_update_date IN DATE DEFAULT NULL,
380 x_last_updated_by IN NUMBER DEFAULT NULL,
381 x_last_update_login IN NUMBER DEFAULT NULL,
382 x_org_id IN NUMBER DEFAULT NULL
383 ) AS
384 BEGIN
385
386 Set_Column_Values (
387 p_action,
388 x_rowid,
389 x_venue_cd,
390 x_exam_location_cd,
391 x_description,
392 x_number_of_seats,
393 x_booking_cost,
394 x_priority_cd,
395 x_supervisor_limit,
396 x_coord_person_id,
397 x_closed_ind,
398 x_comments,
399 x_resources_available,
400 x_announcements,
401 x_booking_information,
402 x_seating_information,
403 x_instructions,
404 x_creation_date,
405 x_created_by,
406 x_last_update_date,
407 x_last_updated_by,
408 x_last_update_login ,
409 x_org_id
410 );
411
412 IF (p_action = 'INSERT') THEN
413 -- Call all the procedures related to Before Insert.
414 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
415 IF GET_PK_FOR_VALIDATION(NEW_REFERENCES.venue_cd) THEN
416 Fnd_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
417 App_Exception.Raise_Exception;
418 END IF;
419
420 check_constraints;
421 Check_Parent_Existance;
422 ELSIF (p_action = 'UPDATE') THEN
423 -- Call all the procedures related to Before Update.
424 BeforeRowInsertUpdate1 ( p_updating => TRUE );
425
426 check_constraints;
427 Check_Parent_Existance;
428 ELSIF (p_action = 'DELETE') THEN
429 -- Call all the procedures related to Before Delete.
430 Check_Child_Existance;
431 ELSIF (p_action = 'VALIDATE_INSERT') THEN
432 IF GET_PK_FOR_VALIDATION(NEW_REFERENCES.venue_cd) THEN
433 Fnd_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
434 App_Exception.Raise_Exception;
435 END IF;
436
437 check_constraints;
438 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
439
440 check_constraints;
441 ELSIF (p_action = 'VALIDATE_DELETE') THEN
442 check_child_existance;
443 END IF;
444
445 END Before_DML;
446
447 procedure INSERT_ROW (
448 X_ROWID in out NOCOPY VARCHAR2,
449 X_VENUE_CD in VARCHAR2,
450 X_EXAM_LOCATION_CD in VARCHAR2,
451 X_DESCRIPTION in VARCHAR2,
452 X_NUMBER_OF_SEATS in NUMBER,
453 X_BOOKING_COST in NUMBER,
454 X_PRIORITY_CD in NUMBER,
455 X_SUPERVISOR_LIMIT in NUMBER,
456 X_COORD_PERSON_ID in NUMBER,
457 X_CLOSED_IND in VARCHAR2,
458 X_COMMENTS in VARCHAR2,
459 X_RESOURCES_AVAILABLE in VARCHAR2,
460 X_ANNOUNCEMENTS in VARCHAR2,
461 X_BOOKING_INFORMATION in VARCHAR2,
462 X_SEATING_INFORMATION in VARCHAR2,
463 X_INSTRUCTIONS in VARCHAR2,
464 X_MODE in VARCHAR2 default 'R',
465 X_ORG_ID in NUMBER
466 ) AS
467 cursor C is select ROWID from IGS_GR_VENUE_ALL
468 where VENUE_CD = X_VENUE_CD;
469 X_LAST_UPDATE_DATE DATE;
470 X_LAST_UPDATED_BY NUMBER;
471 X_LAST_UPDATE_LOGIN NUMBER;
472 begin
473 X_LAST_UPDATE_DATE := SYSDATE;
474 if(X_MODE = 'I') then
475 X_LAST_UPDATED_BY := 1;
476 X_LAST_UPDATE_LOGIN := 0;
477 elsif (X_MODE = 'R') then
478 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
479 if X_LAST_UPDATED_BY is NULL then
480 X_LAST_UPDATED_BY := -1;
481 end if;
482 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
483 if X_LAST_UPDATE_LOGIN is NULL then
484 X_LAST_UPDATE_LOGIN := -1;
485 end if;
486 else
487 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
488 app_exception.raise_exception;
489 end if;
490
491 Before_DML (
492 p_action => 'INSERT',
493 x_rowid => X_ROWID,
494 x_venue_cd => X_VENUE_CD,
495 x_exam_location_cd => X_EXAM_LOCATION_CD,
496 x_description => X_DESCRIPTION,
497 x_number_of_seats => X_NUMBER_OF_SEATS,
498 x_booking_cost => X_BOOKING_COST,
499 x_priority_cd => X_PRIORITY_CD,
500 x_supervisor_limit => X_SUPERVISOR_LIMIT,
501 x_coord_person_id => X_COORD_PERSON_ID,
502 x_closed_ind => NVL(X_CLOSED_IND, 'N'),
503 x_comments => X_COMMENTS,
504 x_resources_available => X_RESOURCES_AVAILABLE,
505 x_announcements => X_ANNOUNCEMENTS,
506 x_booking_information => X_BOOKING_INFORMATION,
507 x_seating_information => X_SEATING_INFORMATION,
508 x_instructions => X_INSTRUCTIONS,
509 x_creation_date => X_LAST_UPDATE_DATE,
510 x_created_by => X_LAST_UPDATED_BY,
511 x_last_update_date => X_LAST_UPDATE_DATE,
512 x_last_updated_by => X_LAST_UPDATED_BY,
513 x_last_update_login => X_LAST_UPDATE_LOGIN,
514 x_org_id => igs_ge_gen_003.get_org_id
515 );
516
517 insert into IGS_GR_VENUE_ALL (
518 VENUE_CD,
519 EXAM_LOCATION_CD,
520 DESCRIPTION,
521 NUMBER_OF_SEATS,
522 BOOKING_COST,
523 PRIORITY_CD,
524 SUPERVISOR_LIMIT,
525 COORD_PERSON_ID,
526 CLOSED_IND,
527 COMMENTS,
528 RESOURCES_AVAILABLE,
529 ANNOUNCEMENTS,
530 BOOKING_INFORMATION,
531 SEATING_INFORMATION,
532 INSTRUCTIONS,
533 CREATION_DATE,
534 CREATED_BY,
535 LAST_UPDATE_DATE,
536 LAST_UPDATED_BY,
537 LAST_UPDATE_LOGIN,
538 ORG_ID
539 ) values (
540 NEW_REFERENCES.VENUE_CD,
541 NEW_REFERENCES.EXAM_LOCATION_CD,
542 NEW_REFERENCES.DESCRIPTION,
543 NEW_REFERENCES.NUMBER_OF_SEATS,
544 NEW_REFERENCES.BOOKING_COST,
545 NEW_REFERENCES.PRIORITY_CD,
546 NEW_REFERENCES.SUPERVISOR_LIMIT,
547 NEW_REFERENCES.COORD_PERSON_ID,
548 NEW_REFERENCES.CLOSED_IND,
549 NEW_REFERENCES.COMMENTS,
550 NEW_REFERENCES.RESOURCES_AVAILABLE,
551 NEW_REFERENCES.ANNOUNCEMENTS,
552 NEW_REFERENCES.BOOKING_INFORMATION,
553 NEW_REFERENCES.SEATING_INFORMATION,
554 NEW_REFERENCES.INSTRUCTIONS,
555 X_LAST_UPDATE_DATE,
556 X_LAST_UPDATED_BY,
557 X_LAST_UPDATE_DATE,
558 X_LAST_UPDATED_BY,
559 X_LAST_UPDATE_LOGIN,
560 NEW_REFERENCES.ORG_ID
561 );
562
563 open c;
564 fetch c into X_ROWID;
565 if (c%notfound) then
566 close c;
567 raise no_data_found;
568 end if;
569 close c;
570
571 end INSERT_ROW;
572
573 procedure LOCK_ROW (
574 X_ROWID in VARCHAR2,
575 X_VENUE_CD in VARCHAR2,
576 X_EXAM_LOCATION_CD in VARCHAR2,
577 X_DESCRIPTION in VARCHAR2,
578 X_NUMBER_OF_SEATS in NUMBER,
579 X_BOOKING_COST in NUMBER,
580 X_PRIORITY_CD in NUMBER,
581 X_SUPERVISOR_LIMIT in NUMBER,
582 X_COORD_PERSON_ID in NUMBER,
583 X_CLOSED_IND in VARCHAR2,
584 X_COMMENTS in VARCHAR2,
585 X_RESOURCES_AVAILABLE in VARCHAR2,
586 X_ANNOUNCEMENTS in VARCHAR2,
587 X_BOOKING_INFORMATION in VARCHAR2,
588 X_SEATING_INFORMATION in VARCHAR2,
589 X_INSTRUCTIONS in VARCHAR2
590 ) AS
591 cursor c1 is select
592 EXAM_LOCATION_CD,
593 DESCRIPTION,
594 NUMBER_OF_SEATS,
595 BOOKING_COST,
596 PRIORITY_CD,
597 SUPERVISOR_LIMIT,
598 COORD_PERSON_ID,
599 CLOSED_IND,
600 COMMENTS,
601 RESOURCES_AVAILABLE,
602 ANNOUNCEMENTS,
603 BOOKING_INFORMATION,
604 SEATING_INFORMATION,
605 INSTRUCTIONS
606 from IGS_GR_VENUE_ALL
607 where ROWID = X_ROWID for update nowait;
608 tlinfo c1%rowtype;
609
610 begin
611 open c1;
612 fetch c1 into tlinfo;
613 if (c1%notfound) then
614 close c1;
615 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
616 app_exception.raise_exception;
617 return;
618 end if;
619 close c1;
620
621 if ( (tlinfo.EXAM_LOCATION_CD = X_EXAM_LOCATION_CD)
622 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
623 AND (tlinfo.NUMBER_OF_SEATS = X_NUMBER_OF_SEATS)
624 AND ((tlinfo.BOOKING_COST = X_BOOKING_COST)
625 OR ((tlinfo.BOOKING_COST is null)
626 AND (X_BOOKING_COST is null)))
627 AND ((tlinfo.PRIORITY_CD = X_PRIORITY_CD)
628 OR ((tlinfo.PRIORITY_CD is null)
629 AND (X_PRIORITY_CD is null)))
630 AND ((tlinfo.SUPERVISOR_LIMIT = X_SUPERVISOR_LIMIT)
631 OR ((tlinfo.SUPERVISOR_LIMIT is null)
632 AND (X_SUPERVISOR_LIMIT is null)))
633 AND ((tlinfo.COORD_PERSON_ID = X_COORD_PERSON_ID)
634 OR ((tlinfo.COORD_PERSON_ID is null)
635 AND (X_COORD_PERSON_ID is null)))
636 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
637 AND ((tlinfo.COMMENTS = X_COMMENTS)
638 OR ((tlinfo.COMMENTS is null)
639 AND (X_COMMENTS is null)))
640 AND ((tlinfo.RESOURCES_AVAILABLE = X_RESOURCES_AVAILABLE)
641 OR ((tlinfo.RESOURCES_AVAILABLE is null)
642 AND (X_RESOURCES_AVAILABLE is null)))
643 AND ((tlinfo.ANNOUNCEMENTS = X_ANNOUNCEMENTS)
644 OR ((tlinfo.ANNOUNCEMENTS is null)
645 AND (X_ANNOUNCEMENTS is null)))
646 AND ((tlinfo.BOOKING_INFORMATION = X_BOOKING_INFORMATION)
647 OR ((tlinfo.BOOKING_INFORMATION is null)
648 AND (X_BOOKING_INFORMATION is null)))
649 AND ((tlinfo.SEATING_INFORMATION = X_SEATING_INFORMATION)
650 OR ((tlinfo.SEATING_INFORMATION is null)
651 AND (X_SEATING_INFORMATION is null)))
652 AND ((tlinfo.INSTRUCTIONS = X_INSTRUCTIONS)
653 OR ((tlinfo.INSTRUCTIONS is null)
654 AND (X_INSTRUCTIONS is null)))
655
656 ) then
657 null;
658 else
659 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
660 app_exception.raise_exception;
661 end if;
662 return;
663 end LOCK_ROW;
664
665 procedure UPDATE_ROW (
666 X_ROWID in VARCHAR2,
667 X_VENUE_CD in VARCHAR2,
668 X_EXAM_LOCATION_CD in VARCHAR2,
669 X_DESCRIPTION in VARCHAR2,
670 X_NUMBER_OF_SEATS in NUMBER,
671 X_BOOKING_COST in NUMBER,
672 X_PRIORITY_CD in NUMBER,
673 X_SUPERVISOR_LIMIT in NUMBER,
674 X_COORD_PERSON_ID in NUMBER,
675 X_CLOSED_IND in VARCHAR2,
676 X_COMMENTS in VARCHAR2,
677 X_RESOURCES_AVAILABLE in VARCHAR2,
678 X_ANNOUNCEMENTS in VARCHAR2,
679 X_BOOKING_INFORMATION in VARCHAR2,
680 X_SEATING_INFORMATION in VARCHAR2,
681 X_INSTRUCTIONS in VARCHAR2,
682 X_MODE in VARCHAR2 default 'R'
683 ) AS
684 X_LAST_UPDATE_DATE DATE;
685 X_LAST_UPDATED_BY NUMBER;
686 X_LAST_UPDATE_LOGIN NUMBER;
687 begin
688 X_LAST_UPDATE_DATE := SYSDATE;
689 if(X_MODE = 'I') then
690 X_LAST_UPDATED_BY := 1;
691 X_LAST_UPDATE_LOGIN := 0;
692 elsif (X_MODE = 'R') then
693 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
694 if X_LAST_UPDATED_BY is NULL then
695 X_LAST_UPDATED_BY := -1;
696 end if;
697 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
698 if X_LAST_UPDATE_LOGIN is NULL then
699 X_LAST_UPDATE_LOGIN := -1;
700 end if;
701 else
702 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
703 app_exception.raise_exception;
704 end if;
705
706 Before_DML (
707 p_action => 'UPDATE',
708 x_rowid => X_ROWID,
709 x_venue_cd => X_VENUE_CD,
710 x_exam_location_cd => X_EXAM_LOCATION_CD,
711 x_description => X_DESCRIPTION,
712 x_number_of_seats => X_NUMBER_OF_SEATS,
713 x_booking_cost => X_BOOKING_COST,
714 x_priority_cd => X_PRIORITY_CD,
715 x_supervisor_limit => X_SUPERVISOR_LIMIT,
716 x_coord_person_id => X_COORD_PERSON_ID,
717 x_closed_ind => X_CLOSED_IND,
718 x_comments => X_COMMENTS,
719 x_resources_available => X_RESOURCES_AVAILABLE,
720 x_announcements => X_ANNOUNCEMENTS,
721 x_booking_information => X_BOOKING_INFORMATION,
722 x_seating_information => X_SEATING_INFORMATION,
723 x_instructions => X_INSTRUCTIONS,
724 x_creation_date => X_LAST_UPDATE_DATE,
725 x_created_by => X_LAST_UPDATED_BY,
726 x_last_update_date => X_LAST_UPDATE_DATE,
727 x_last_updated_by => X_LAST_UPDATED_BY,
728 x_last_update_login => X_LAST_UPDATE_LOGIN
729 );
730
731 update IGS_GR_VENUE_ALL set
732 EXAM_LOCATION_CD = NEW_REFERENCES.EXAM_LOCATION_CD,
733 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
734 NUMBER_OF_SEATS = NEW_REFERENCES.NUMBER_OF_SEATS,
735 BOOKING_COST = NEW_REFERENCES.BOOKING_COST,
736 PRIORITY_CD = NEW_REFERENCES.PRIORITY_CD,
737 SUPERVISOR_LIMIT = NEW_REFERENCES.SUPERVISOR_LIMIT,
738 COORD_PERSON_ID = NEW_REFERENCES.COORD_PERSON_ID,
739 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
740 COMMENTS = NEW_REFERENCES.COMMENTS,
741 RESOURCES_AVAILABLE = NEW_REFERENCES.RESOURCES_AVAILABLE,
742 ANNOUNCEMENTS = NEW_REFERENCES.ANNOUNCEMENTS,
743 BOOKING_INFORMATION = NEW_REFERENCES.BOOKING_INFORMATION,
744 SEATING_INFORMATION = NEW_REFERENCES.SEATING_INFORMATION,
745 INSTRUCTIONS = NEW_REFERENCES.INSTRUCTIONS,
746 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
747 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
748 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
749 where ROWID = X_ROWID
750 ;
751 if (sql%notfound) then
752 raise no_data_found;
753 end if;
754
755 end UPDATE_ROW;
756
757 procedure ADD_ROW (
758 X_ROWID in out NOCOPY VARCHAR2,
759 X_VENUE_CD in VARCHAR2,
760 X_EXAM_LOCATION_CD in VARCHAR2,
761 X_DESCRIPTION in VARCHAR2,
762 X_NUMBER_OF_SEATS in NUMBER,
763 X_BOOKING_COST in NUMBER,
764 X_PRIORITY_CD in NUMBER,
765 X_SUPERVISOR_LIMIT in NUMBER,
766 X_COORD_PERSON_ID in NUMBER,
767 X_CLOSED_IND in VARCHAR2,
768 X_COMMENTS in VARCHAR2,
769 X_RESOURCES_AVAILABLE in VARCHAR2,
770 X_ANNOUNCEMENTS in VARCHAR2,
771 X_BOOKING_INFORMATION in VARCHAR2,
772 X_SEATING_INFORMATION in VARCHAR2,
773 X_INSTRUCTIONS in VARCHAR2,
774 X_MODE in VARCHAR2 default 'R',
775 X_ORG_ID in NUMBER
776 ) AS
777 cursor c1 is select rowid from IGS_GR_VENUE_ALL
778 where VENUE_CD = X_VENUE_CD
779 ;
780
781 begin
782 open c1;
783 fetch c1 into X_ROWID;
784 if (c1%notfound) then
785 close c1;
786 INSERT_ROW (
787 X_ROWID,
788 X_VENUE_CD,
789 X_EXAM_LOCATION_CD,
790 X_DESCRIPTION,
791 X_NUMBER_OF_SEATS,
792 X_BOOKING_COST,
793 X_PRIORITY_CD,
794 X_SUPERVISOR_LIMIT,
795 X_COORD_PERSON_ID,
796 X_CLOSED_IND,
797 X_COMMENTS,
798 X_RESOURCES_AVAILABLE,
799 X_ANNOUNCEMENTS,
800 X_BOOKING_INFORMATION,
801 X_SEATING_INFORMATION,
802 X_INSTRUCTIONS,
803 X_MODE,
804 x_org_id
805 );
806 return;
807 end if;
808 close c1;
809 UPDATE_ROW (
810 X_ROWID,
811 X_VENUE_CD,
812 X_EXAM_LOCATION_CD,
813 X_DESCRIPTION,
814 X_NUMBER_OF_SEATS,
815 X_BOOKING_COST,
816 X_PRIORITY_CD,
817 X_SUPERVISOR_LIMIT,
818 X_COORD_PERSON_ID,
819 X_CLOSED_IND,
820 X_COMMENTS,
821 X_RESOURCES_AVAILABLE,
822 X_ANNOUNCEMENTS,
823 X_BOOKING_INFORMATION,
824 X_SEATING_INFORMATION,
825 X_INSTRUCTIONS,
826 X_MODE
827 );
828 end ADD_ROW;
829
830 procedure DELETE_ROW (
831 X_ROWID in VARCHAR2
832 ) AS
833 begin
834
835 Before_DML (
836 p_action => 'DELETE',
837 x_rowid => X_ROWID
838 );
839
840 delete from IGS_GR_VENUE_ALL
841 where ROWID = X_ROWID;
842 if (sql%notfound) then
843 raise no_data_found;
844 end if;
845
846 end DELETE_ROW;
847
848 end IGS_GR_VENUE_PKG;