[Home] [Help]
PACKAGE BODY: APPS.IGS_GR_CRMN_PKG
Source
1 package body IGS_GR_CRMN_PKG as
2 /* $Header: IGSGI08B.pls 115.6 2002/11/29 00:35:36 nsidana ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_GR_CRMN_ALL%RowType;
5 new_references IGS_GR_CRMN_ALL%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_grd_cal_type IN VARCHAR2 DEFAULT NULL,
11 x_grd_ci_sequence_number IN NUMBER DEFAULT NULL,
12 x_ceremony_number IN NUMBER DEFAULT NULL,
13 x_venue_cd IN VARCHAR2 DEFAULT NULL,
14 x_ceremony_dt_alias IN VARCHAR2 DEFAULT NULL,
15 x_ceremony_dai_sequence_number IN NUMBER DEFAULT NULL,
16 x_closing_dt_alias IN VARCHAR2 DEFAULT NULL,
17 x_closing_dai_sequence_number IN NUMBER DEFAULT NULL,
18 x_ceremony_start_time IN DATE DEFAULT NULL,
19 x_ceremony_end_time IN DATE DEFAULT NULL,
20 x_ceremony_fee IN NUMBER DEFAULT NULL,
21 x_number_of_guests IN NUMBER DEFAULT NULL,
22 x_creation_date IN DATE DEFAULT NULL,
23 x_created_by IN NUMBER DEFAULT NULL,
24 x_last_update_date IN DATE DEFAULT NULL,
25 x_last_updated_by IN NUMBER DEFAULT NULL,
26 x_last_update_login IN NUMBER DEFAULT NULL ,
27 x_org_id IN NUMBER DEFAULT NULL
28 ) AS
29
30 CURSOR cur_old_ref_values IS
31 SELECT *
32 FROM IGS_GR_CRMN_ALL
33 WHERE rowid = x_rowid;
34
35 BEGIN
36
37 l_rowid := x_rowid;
38
39 -- Code for setting the Old and New Reference Values.
40 -- Populate Old Values.
41 Open cur_old_ref_values;
42 Fetch cur_old_ref_values INTO old_references;
43 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
44 Close cur_old_ref_values;
45 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
46 IGS_GE_MSG_STACK.ADD;
47 App_Exception.Raise_Exception;
48 Return;
49 END IF;
50 Close cur_old_ref_values;
51
52 -- Populate New Values.
53 new_references.grd_cal_type := x_grd_cal_type;
54 new_references.grd_ci_sequence_number := x_grd_ci_sequence_number;
55 new_references.ceremony_number := x_ceremony_number;
56 new_references.venue_cd := x_venue_cd;
57 new_references.ceremony_dt_alias := x_ceremony_dt_alias;
58 new_references.ceremony_dai_sequence_number := x_ceremony_dai_sequence_number;
59 new_references.closing_dt_alias := x_closing_dt_alias;
60 new_references.closing_dai_sequence_number := x_closing_dai_sequence_number;
61 new_references.ceremony_start_time := x_ceremony_start_time;
62 new_references.ceremony_end_time := x_ceremony_end_time;
63 new_references.ceremony_fee := x_ceremony_fee;
64 new_references.number_of_guests := x_number_of_guests;
65 new_references.org_id := x_org_id;
66 IF (p_action = 'UPDATE') THEN
67 new_references.creation_date := old_references.creation_date;
68 new_references.created_by := old_references.created_by;
69 ELSE
70 new_references.creation_date := x_creation_date;
71 new_references.created_by := x_created_by;
72 END IF;
73 new_references.last_update_date := x_last_update_date;
74 new_references.last_updated_by := x_last_updated_by;
75 new_references.last_update_login := x_last_update_login;
76
77 END Set_Column_Values;
78
79 -- Trigger description :-
80 -- "OSS_TST".trg_gc_br_iu
81 -- BEFORE INSERT OR UPDATE
82 -- ON IGS_GR_CRMN_ALL
83 -- FOR EACH ROW
84
85 PROCEDURE BeforeRowInsertUpdate1(
86 p_inserting IN BOOLEAN DEFAULT FALSE,
87 p_updating IN BOOLEAN DEFAULT FALSE,
88 p_deleting IN BOOLEAN DEFAULT FALSE
89 ) AS
90 v_message_name VARCHAR2(30);
91 BEGIN
92 -- Validate the graduation ceremony record can be updated
93 IF p_updating THEN
94 IF (new_references.ceremony_dt_alias <> old_references.ceremony_dt_alias OR
95 new_references.ceremony_dai_sequence_number <> old_references.ceremony_dai_sequence_number OR
96 new_references.closing_dt_alias <> old_references.closing_dt_alias OR
97 new_references.closing_dai_sequence_number <> old_references.closing_dai_sequence_number) THEN
98 IF IGS_GR_VAL_GC.grdp_val_gc_upd(
99 new_references.grd_cal_type,
100 new_references.grd_ci_sequence_number,
101 new_references.ceremony_number,
102 v_message_name) = FALSE THEN
103 Fnd_Message.Set_Name('IGS', v_message_name);
104 IGS_GE_MSG_STACK.ADD;
105 App_Exception.Raise_Exception;
106 END IF;
107 END IF;
108 END IF;
109 IF p_inserting OR (p_updating AND new_references.venue_cd <> old_references.venue_cd) THEN
110 -- Validate venue is related to a location with a s_location_type of grd_ctr
111 IF IGS_GR_VAL_GC.grdp_val_ve_lot(
112 new_references.venue_cd,
113 v_message_name) = FALSE THEN
114 Fnd_Message.Set_Name('IGS', v_message_name);
115 IGS_GE_MSG_STACK.ADD;
116 App_Exception.Raise_Exception;
117 END IF;
118 -- Validate venue is not closed
119 IF IGS_GR_VAL_GC.assp_val_ve_closed(
120 new_references.venue_cd,
121 v_message_name) = FALSE THEN
122 Fnd_Message.Set_Name('IGS', v_message_name);
123 IGS_GE_MSG_STACK.ADD;
124 App_Exception.Raise_Exception;
125 END IF;
126 END IF;
127 IF p_inserting OR (p_updating AND
128 (new_references.ceremony_dt_alias <> old_references.ceremony_dt_alias OR
129 new_references.ceremony_dai_sequence_number <> old_references.ceremony_dai_sequence_number OR
130 new_references.closing_dt_alias <> old_references.closing_dt_alias OR
131 new_references.closing_dai_sequence_number <> old_references.closing_dai_sequence_number)) THEN
132 -- Validate the graduation ceremony date aliases
133 IF IGS_GR_VAL_GC.grdp_val_gc_dai(
134 new_references.grd_cal_type,
135 new_references.grd_ci_sequence_number,
136 new_references.ceremony_dt_alias,
137 new_references.ceremony_dai_sequence_number,
138 new_references.closing_dt_alias,
139 new_references.closing_dai_sequence_number,
140 v_message_name) = FALSE THEN
141 Fnd_Message.Set_Name('IGS', v_message_name);
142 IGS_GE_MSG_STACK.ADD;
143 App_Exception.Raise_Exception;
144 END IF;
145 END IF;
146
147
148 END BeforeRowInsertUpdate1;
149
150 -- Trigger description :-
151 -- "OSS_TST".trg_gc_ar_iu
152 -- AFTER INSERT OR UPDATE
153 -- ON IGS_GR_CRMN_ALL
154 -- FOR EACH ROW
155
156 PROCEDURE AfterRowInsertUpdate2(
157 p_inserting IN BOOLEAN DEFAULT FALSE,
158 p_updating IN BOOLEAN DEFAULT FALSE,
159 p_deleting IN BOOLEAN DEFAULT FALSE
160 ) AS
161 v_message_name VARCHAR2(30);
162 v_rowid_saved BOOLEAN := FALSE;
163 BEGIN
164 IF p_inserting OR (p_updating AND
165 (new_references.ceremony_start_time <> old_references.ceremony_start_time OR
166 new_references.ceremony_end_time <> old_references.ceremony_end_time)) THEN
167 -- validate graduation ceremony start and end times
168 IF IGS_GR_VAL_GC.grdp_val_gc_times(
169 NEW_REFERENCES.grd_cal_type,
170 NEW_REFERENCES.grd_ci_sequence_number,
171 NEW_REFERENCES.ceremony_number,
172 NEW_REFERENCES.venue_cd,
173 NEW_REFERENCES.ceremony_dt_alias,
174 NEW_REFERENCES.ceremony_dai_sequence_number,
175 NEW_REFERENCES.ceremony_start_time,
176 NEW_REFERENCES.ceremony_end_time,
177 v_message_name) = FALSE THEN
178 Fnd_Message.Set_Name('IGS', v_message_name);
179 IGS_GE_MSG_STACK.ADD;
180 App_Exception.Raise_Exception;
181 END IF;
182 v_rowid_saved := TRUE;
183 END IF;
184
185 END AfterRowInsertUpdate2;
186
187 PROCEDURE Check_Parent_Existance AS
188 BEGIN
189
190 IF (((old_references.ceremony_dt_alias = new_references.ceremony_dt_alias) AND
191 (old_references.ceremony_dai_sequence_number = new_references.ceremony_dai_sequence_number) AND
192 (old_references.grd_cal_type = new_references.grd_cal_type) AND
193 (old_references.grd_ci_sequence_number = new_references.grd_ci_sequence_number)) OR
194 ((new_references.ceremony_dt_alias IS NULL) OR
195 (new_references.ceremony_dai_sequence_number IS NULL) OR
196 (new_references.grd_cal_type IS NULL) OR
197 (new_references.grd_ci_sequence_number IS NULL))) THEN
198 NULL;
199 ELSE
200 IF NOT IGS_CA_DA_INST_PKG.Get_PK_For_Validation (
201 new_references.ceremony_dt_alias,
202 new_references.ceremony_dai_sequence_number,
203 new_references.grd_cal_type,
204 new_references.grd_ci_sequence_number
205 ) THEN
206 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
207 IGS_GE_MSG_STACK.ADD;
208 APP_EXCEPTION.RAISE_EXCEPTION;
209 END IF;
210
211 END IF;
212
213 IF (((old_references.closing_dt_alias = new_references.closing_dt_alias) AND
214 (old_references.closing_dai_sequence_number = new_references.closing_dai_sequence_number) AND
215 (old_references.grd_cal_type = new_references.grd_cal_type) AND
216 (old_references.grd_ci_sequence_number = new_references.grd_ci_sequence_number)) OR
217 ((new_references.closing_dt_alias IS NULL) OR
218 (new_references.closing_dai_sequence_number IS NULL) OR
219 (new_references.grd_cal_type IS NULL) OR
220 (new_references.grd_ci_sequence_number IS NULL))) THEN
221 NULL;
222 ELSE
223 IF NOT IGS_CA_DA_INST_PKG.Get_PK_For_Validation (
224 new_references.closing_dt_alias,
225 new_references.closing_dai_sequence_number,
226 new_references.grd_cal_type,
227 new_references.grd_ci_sequence_number
228 ) THEN
229 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
230 IGS_GE_MSG_STACK.ADD;
231 APP_EXCEPTION.RAISE_EXCEPTION;
232 END IF;
233
234 END IF;
235
236 IF (((old_references.grd_cal_type = new_references.grd_cal_type) AND
237 (old_references.grd_ci_sequence_number = new_references.grd_ci_sequence_number)) OR
238 ((new_references.grd_cal_type IS NULL) OR
239 (new_references.grd_ci_sequence_number IS NULL))) THEN
240 NULL;
241 ELSE
242 IF NOT IGS_GR_CRMN_ROUND_PKG.Get_PK_For_Validation (
243 new_references.grd_cal_type,
244 new_references.grd_ci_sequence_number
245 ) THEN
246 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
247 IGS_GE_MSG_STACK.ADD;
248 APP_EXCEPTION.RAISE_EXCEPTION;
249 END IF;
250
251 END IF;
252
253 IF (((old_references.venue_cd = new_references.venue_cd)) OR
254 ((new_references.venue_cd IS NULL))) THEN
255 NULL;
256 ELSE
257 IF NOT IGS_GR_VENUE_PKG.Get_PK_For_Validation (
258 new_references.venue_cd
259 ) THEN
260 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
261 IGS_GE_MSG_STACK.ADD;
262 APP_EXCEPTION.RAISE_EXCEPTION;
263 END IF;
264
265 END IF;
266
267 END Check_Parent_Existance;
268
269 PROCEDURE CHECK_CONSTRAINTS(
270 Column_Name IN VARCHAR2 DEFAULT NULL,
271 Column_Value IN VARCHAR2 DEFAULT NULL
272 ) AS
273 BEGIN
274 IF Column_Name is null THEN
275 NULL;
276 ELSIF upper(Column_name) = 'CEREMONY_DT_ALIAS' THEN
277 new_references.CEREMONY_DT_ALIAS:= COLUMN_VALUE ;
278
279 ELSIF upper(Column_name) = 'CLOSING_DT_ALIAS' THEN
280 new_references.CLOSING_DT_ALIAS:= COLUMN_VALUE ;
281
282 ELSIF upper(Column_name) = 'GRD_CAL_TYPE' THEN
283 new_references.GRD_CAL_TYPE:= COLUMN_VALUE ;
284
285 ELSIF upper(Column_name) = 'VENUE_CD' THEN
286 new_references.VENUE_CD:= COLUMN_VALUE ;
287
288 ELSIF upper(Column_name) = 'GRD_CI_SEQUENCE_NUMBER' THEN
289 new_references.GRD_CI_SEQUENCE_NUMBER:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
290
291 ELSIF upper(Column_name) = 'CEREMONY_NUMBER' THEN
292 new_references.CEREMONY_NUMBER:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
293
294 ELSIF upper(Column_name) = 'CEREMONY_DAI_SEQUENCE_NUMBER' THEN
295 new_references.CEREMONY_DAI_SEQUENCE_NUMBER:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
296
297 ELSIF upper(Column_name) = 'CLOSING_DAI_SEQUENCE_NUMBER' THEN
298 new_references.CLOSING_DAI_SEQUENCE_NUMBER:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
299
300 ELSIF upper(Column_name) = 'CEREMONY_FEE' THEN
301 new_references.CEREMONY_FEE:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
302
303 ELSIF upper(Column_name) = 'NUMBER_OF_GUESTS' THEN
304 new_references.NUMBER_OF_GUESTS:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
305 END IF ;
306
307 IF upper(Column_name) = 'CEREMONY_DT_ALIAS' OR COLUMN_NAME IS NULL THEN
308 IF new_references.CEREMONY_DT_ALIAS<> upper(NEW_REFERENCES.CEREMONY_DT_ALIAS) then
309 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
310 IGS_GE_MSG_STACK.ADD;
311 App_Exception.Raise_Exception ;
312 END IF;
313
314 END IF ;
315
316 IF upper(Column_name) = 'CLOSING_DT_ALIAS' OR COLUMN_NAME IS NULL THEN
317 IF new_references.CLOSING_DT_ALIAS<> upper(NEW_REFERENCES.CLOSING_DT_ALIAS) then
318 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
319 IGS_GE_MSG_STACK.ADD;
320 App_Exception.Raise_Exception ;
321 END IF;
322
323 END IF ;
324
325 IF upper(Column_name) = 'GRD_CAL_TYPE' OR COLUMN_NAME IS NULL THEN
326 IF new_references.GRD_CAL_TYPE<> upper(NEW_REFERENCES.GRD_CAL_TYPE) then
327 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
328 IGS_GE_MSG_STACK.ADD;
329 App_Exception.Raise_Exception ;
330 END IF;
331
332 END IF ;
333
334 IF upper(Column_name) = 'VENUE_CD' OR COLUMN_NAME IS NULL THEN
335 IF new_references.VENUE_CD<> upper(NEW_REFERENCES.VENUE_CD) then
336 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
337 IGS_GE_MSG_STACK.ADD;
338 App_Exception.Raise_Exception ;
339 END IF;
340
341 END IF ;
342 IF upper(Column_name) = 'GRD_CI_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
343 IF new_references.GRD_CI_SEQUENCE_NUMBER < 1 OR new_references.GRD_CI_SEQUENCE_NUMBER > 999999 then
344 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
345 IGS_GE_MSG_STACK.ADD;
346 App_Exception.Raise_Exception ;
347 END IF;
348
349 END IF ;
350 IF upper(Column_name) = 'CEREMONY_NUMBER' OR COLUMN_NAME IS NULL THEN
351 IF new_references.CEREMONY_NUMBER < 0 OR new_references.CEREMONY_NUMBER > 999 then
352 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
353 IGS_GE_MSG_STACK.ADD;
354 App_Exception.Raise_Exception ;
355 END IF;
356
357 END IF ;
358 IF upper(Column_name) = 'CEREMONY_DAI_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
359 IF new_references.CEREMONY_DAI_SEQUENCE_NUMBER < 1 OR new_references.CEREMONY_DAI_SEQUENCE_NUMBER > 999999 then
360 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
361 IGS_GE_MSG_STACK.ADD;
362 App_Exception.Raise_Exception ;
363 END IF;
364
365 END IF ;
366 IF upper(Column_name) = 'CLOSING_DAI_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
367 IF new_references.CLOSING_DAI_SEQUENCE_NUMBER < 1 OR new_references.CLOSING_DAI_SEQUENCE_NUMBER > 999999 then
368 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
369 IGS_GE_MSG_STACK.ADD;
370 App_Exception.Raise_Exception ;
371 END IF;
372
373 END IF ;
374 IF upper(Column_name) = 'CEREMONY_FEE' OR COLUMN_NAME IS NULL THEN
375 IF new_references.CEREMONY_FEE < 0.00 OR new_references.CEREMONY_FEE > 999.99 then
376 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
377 IGS_GE_MSG_STACK.ADD;
378 App_Exception.Raise_Exception ;
379 END IF;
380
381 END IF ;
382 IF upper(Column_name) = 'NUMBER_OF_GUESTS' OR COLUMN_NAME IS NULL THEN
383 IF new_references.NUMBER_OF_GUESTS < 0 OR new_references.NUMBER_OF_GUESTS > 999 then
384 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
385 IGS_GE_MSG_STACK.ADD;
386 App_Exception.Raise_Exception ;
387 END IF;
388
389 END IF ;
390 END;
391
392 PROCEDURE Check_Child_Existance AS
393 BEGIN
394
395 IGS_GR_AWD_CEREMONY_PKG.GET_FK_IGS_GR_CRMN (
396 old_references.grd_cal_type,
397 old_references.grd_ci_sequence_number,
398 old_references.ceremony_number
399 );
400
401 IGS_GR_CRMN_NOTE_PKG.GET_FK_IGS_GR_CRMN (
402 old_references.grd_cal_type,
403 old_references.grd_ci_sequence_number,
404 old_references.ceremony_number
405 );
406
407 END Check_Child_Existance;
408
409 FUNCTION Get_PK_For_Validation (
410 x_grd_cal_type IN VARCHAR2,
411 x_grd_ci_sequence_number IN NUMBER,
412 x_ceremony_number IN NUMBER
413 )RETURN BOOLEAN AS
414
415 CURSOR cur_rowid IS
416 SELECT rowid
417 FROM IGS_GR_CRMN_ALL
418 WHERE grd_cal_type = x_grd_cal_type
419 AND grd_ci_sequence_number = x_grd_ci_sequence_number
420 AND ceremony_number = x_ceremony_number
421 FOR UPDATE NOWAIT;
422
423 lv_rowid cur_rowid%RowType;
424
425 BEGIN
426
427 Open cur_rowid;
428 Fetch cur_rowid INTO lv_rowid;
429 IF (cur_rowid%FOUND) THEN
430 Close cur_rowid;
431 Return (TRUE);
432 ELSE
433 Close cur_rowid;
434 Return (FALSE);
435 END IF;
436 END Get_PK_For_Validation;
437
438 PROCEDURE GET_FK_IGS_CA_DA_INST (
439 x_dt_alias IN VARCHAR2,
440 x_sequence_number IN NUMBER,
441 x_cal_type IN VARCHAR2,
442 x_ci_sequence_number IN NUMBER
443 ) AS
444
445 CURSOR cur_rowid IS
446 SELECT rowid
447 FROM IGS_GR_CRMN_ALL
448 WHERE (ceremony_dt_alias = x_dt_alias
449 AND ceremony_dai_sequence_number = x_sequence_number
450 AND grd_cal_type = x_cal_type
451 AND grd_ci_sequence_number = x_ci_sequence_number)
452 OR (closing_dt_alias = x_dt_alias
453 AND closing_dai_sequence_number = x_sequence_number
454 AND grd_cal_type = x_cal_type
455 AND grd_ci_sequence_number = x_ci_sequence_number) ;
456
457 lv_rowid cur_rowid%RowType;
458
459 BEGIN
460
461 Open cur_rowid;
462 Fetch cur_rowid INTO lv_rowid;
463 IF (cur_rowid%FOUND) THEN
464 Close cur_rowid;
465 Fnd_Message.Set_Name ('IGS', 'IGS_GR_GC_CEREMONY_DAI_FK');
466 IGS_GE_MSG_STACK.ADD;
467 App_Exception.Raise_Exception;
468 Return;
469 END IF;
470 Close cur_rowid;
471
472 END GET_FK_IGS_CA_DA_INST;
473
474 PROCEDURE GET_FK_IGS_GR_CRMN_ROUND (
475 x_grd_cal_type IN VARCHAR2,
476 x_grd_ci_sequence_number IN NUMBER
477 ) AS
478
479 CURSOR cur_rowid IS
480 SELECT rowid
481 FROM IGS_GR_CRMN_ALL
482 WHERE grd_cal_type = x_grd_cal_type
483 AND grd_ci_sequence_number = x_grd_ci_sequence_number ;
484
485 lv_rowid cur_rowid%RowType;
486
487 BEGIN
488
489 Open cur_rowid;
490 Fetch cur_rowid INTO lv_rowid;
491 IF (cur_rowid%FOUND) THEN
492 Close cur_rowid;
493 Fnd_Message.Set_Name ('IGS', 'IGS_GR_GC_CRD_FK');
494 IGS_GE_MSG_STACK.ADD;
495 App_Exception.Raise_Exception;
496 Return;
497 END IF;
498 Close cur_rowid;
499
500 END GET_FK_IGS_GR_CRMN_ROUND;
501
502 PROCEDURE GET_FK_IGS_GR_VENUE (
503 x_venue_cd IN VARCHAR2
504 ) AS
505
506 CURSOR cur_rowid IS
507 SELECT rowid
508 FROM IGS_GR_CRMN_ALL
509 WHERE venue_cd = x_venue_cd ;
510
511 lv_rowid cur_rowid%RowType;
512
513 BEGIN
514
515 Open cur_rowid;
516 Fetch cur_rowid INTO lv_rowid;
517 IF (cur_rowid%FOUND) THEN
518 Close cur_rowid;
519 Fnd_Message.Set_Name ('IGS', 'IGS_GR_GC_VE_FK');
520 IGS_GE_MSG_STACK.ADD;
521 App_Exception.Raise_Exception;
522 Return;
523 END IF;
524 Close cur_rowid;
525
526 END GET_FK_IGS_GR_VENUE;
527
528 PROCEDURE Before_DML (
529 p_action IN VARCHAR2,
530 x_rowid IN VARCHAR2 DEFAULT NULL,
531 x_grd_cal_type IN VARCHAR2 DEFAULT NULL,
532 x_grd_ci_sequence_number IN NUMBER DEFAULT NULL,
533 x_ceremony_number IN NUMBER DEFAULT NULL,
534 x_venue_cd IN VARCHAR2 DEFAULT NULL,
535 x_ceremony_dt_alias IN VARCHAR2 DEFAULT NULL,
536 x_ceremony_dai_sequence_number IN NUMBER DEFAULT NULL,
537 x_closing_dt_alias IN VARCHAR2 DEFAULT NULL,
538 x_closing_dai_sequence_number IN NUMBER DEFAULT NULL,
539 x_ceremony_start_time IN DATE DEFAULT NULL,
540 x_ceremony_end_time IN DATE DEFAULT NULL,
541 x_ceremony_fee IN NUMBER DEFAULT NULL,
542 x_number_of_guests IN NUMBER DEFAULT NULL,
543 x_creation_date IN DATE DEFAULT NULL,
544 x_created_by IN NUMBER DEFAULT NULL,
545 x_last_update_date IN DATE DEFAULT NULL,
546 x_last_updated_by IN NUMBER DEFAULT NULL,
547 x_last_update_login IN NUMBER DEFAULT NULL,
548 x_org_id IN NUMBER DEFAULT NULL
549 ) AS
550 BEGIN
551
552 Set_Column_Values (
553 p_action,
554 x_rowid,
555 x_grd_cal_type,
556 x_grd_ci_sequence_number,
557 x_ceremony_number,
558 x_venue_cd,
559 x_ceremony_dt_alias,
560 x_ceremony_dai_sequence_number,
561 x_closing_dt_alias,
562 x_closing_dai_sequence_number,
563 x_ceremony_start_time,
564 x_ceremony_end_time,
565 x_ceremony_fee,
566 x_number_of_guests,
567 x_creation_date,
568 x_created_by,
569 x_last_update_date,
570 x_last_updated_by,
571 x_last_update_login ,
572 x_org_id
573 );
574
575 IF (p_action = 'INSERT') THEN
576 -- Call all the procedures related to Before Insert.
577 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
578 IF GET_PK_FOR_VALIDATION(
579 NEW_REFERENCES.grd_cal_type,
580 NEW_REFERENCES.grd_ci_sequence_number,
581 NEW_REFERENCES.ceremony_number
582 ) THEN
583 Fnd_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
584 IGS_GE_MSG_STACK.ADD;
585 App_Exception.Raise_Exception;
586 END IF;
587
588 check_constraints;
589 Check_Parent_Existance;
590 ELSIF (p_action = 'UPDATE') THEN
591 -- Call all the procedures related to Before Update.
592 BeforeRowInsertUpdate1 ( p_updating => TRUE );
593
594 check_constraints;
595 Check_Parent_Existance;
596 ELSIF (p_action = 'DELETE') THEN
597 -- Call all the procedures related to Before Delete.
598 Check_Child_Existance;
599 ELSIF (p_action = 'VALIDATE_INSERT') THEN
600 IF GET_PK_FOR_VALIDATION(
601 NEW_REFERENCES.grd_cal_type,
602 NEW_REFERENCES.grd_ci_sequence_number,
603 NEW_REFERENCES.ceremony_number
604 ) THEN
605 Fnd_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
606 IGS_GE_MSG_STACK.ADD;
607 App_Exception.Raise_Exception;
608 END IF;
609
610 check_constraints;
611 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
612
613 check_constraints;
614 ELSIF (p_action = 'VALIDATE_DELETE') THEN
615 check_child_existance;
616 END IF;
617
618 END Before_DML;
619
620 PROCEDURE After_DML (
621 p_action IN VARCHAR2,
622 x_rowid IN VARCHAR2
623 ) AS
624 BEGIN
625
626 l_rowid := x_rowid;
627
628 IF (p_action = 'INSERT') THEN
629 -- Call all the procedures related to After Insert.
630 AfterRowInsertUpdate2 ( p_inserting => TRUE );
631 ELSIF (p_action = 'UPDATE') THEN
632 -- Call all the procedures related to After Update.
633 AfterRowInsertUpdate2 ( p_updating => TRUE );
634 END IF;
635
636 END After_DML;
637
638 procedure INSERT_ROW (
639 X_ROWID in out NOCOPY VARCHAR2,
640 X_GRD_CAL_TYPE in VARCHAR2,
641 X_GRD_CI_SEQUENCE_NUMBER in NUMBER,
642 X_CEREMONY_NUMBER in NUMBER,
643 X_VENUE_CD in VARCHAR2,
644 X_CEREMONY_DT_ALIAS in VARCHAR2,
645 X_CEREMONY_DAI_SEQUENCE_NUMBER in NUMBER,
646 X_CLOSING_DT_ALIAS in VARCHAR2,
647 X_CLOSING_DAI_SEQUENCE_NUMBER in NUMBER,
648 X_CEREMONY_START_TIME in DATE,
649 X_CEREMONY_END_TIME in DATE,
650 X_CEREMONY_FEE in NUMBER,
651 X_NUMBER_OF_GUESTS in NUMBER,
652 X_MODE in VARCHAR2 default 'R',
653 X_ORG_ID in NUMBER
654 ) AS
655 cursor C is select ROWID from IGS_GR_CRMN_ALL
656 where GRD_CAL_TYPE = X_GRD_CAL_TYPE
657 and GRD_CI_SEQUENCE_NUMBER = X_GRD_CI_SEQUENCE_NUMBER
658 and CEREMONY_NUMBER = X_CEREMONY_NUMBER;
659 X_LAST_UPDATE_DATE DATE;
660 X_LAST_UPDATED_BY NUMBER;
661 X_LAST_UPDATE_LOGIN NUMBER;
662 begin
663 X_LAST_UPDATE_DATE := SYSDATE;
664 if(X_MODE = 'I') then
665 X_LAST_UPDATED_BY := 1;
666 X_LAST_UPDATE_LOGIN := 0;
667 elsif (X_MODE = 'R') then
668 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
669 if X_LAST_UPDATED_BY is NULL then
670 X_LAST_UPDATED_BY := -1;
671 end if;
672 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
673 if X_LAST_UPDATE_LOGIN is NULL then
674 X_LAST_UPDATE_LOGIN := -1;
675 end if;
676 else
677 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
678 IGS_GE_MSG_STACK.ADD;
679 app_exception.raise_exception;
680 end if;
681
682 Before_DML (
683 p_action => 'INSERT',
684 x_rowid => X_ROWID,
685 x_grd_cal_type => X_GRD_CAL_TYPE,
686 x_grd_ci_sequence_number => X_GRD_CI_SEQUENCE_NUMBER,
687 x_ceremony_number => X_CEREMONY_NUMBER,
688 x_venue_cd => X_VENUE_CD,
689 x_ceremony_dt_alias => X_CEREMONY_DT_ALIAS,
690 x_ceremony_dai_sequence_number => X_CEREMONY_DAI_SEQUENCE_NUMBER,
691 x_closing_dt_alias => X_CLOSING_DT_ALIAS,
692 x_closing_dai_sequence_number => X_CLOSING_DAI_SEQUENCE_NUMBER,
693 x_ceremony_start_time => X_CEREMONY_START_TIME,
694 x_ceremony_end_time => X_CEREMONY_END_TIME,
695 x_ceremony_fee => X_CEREMONY_FEE,
696 x_number_of_guests => X_NUMBER_OF_GUESTS,
697 x_creation_date => X_LAST_UPDATE_DATE,
698 x_created_by => X_LAST_UPDATED_BY,
699 x_last_update_date => X_LAST_UPDATE_DATE,
700 x_last_updated_by => X_LAST_UPDATED_BY,
701 x_last_update_login => X_LAST_UPDATE_LOGIN,
702 x_org_id => igs_ge_gen_003.get_org_id
703 );
704
705 insert into IGS_GR_CRMN_ALL (
706 GRD_CAL_TYPE,
707 GRD_CI_SEQUENCE_NUMBER,
708 CEREMONY_NUMBER,
709 VENUE_CD,
710 CEREMONY_DT_ALIAS,
711 CEREMONY_DAI_SEQUENCE_NUMBER,
712 CLOSING_DT_ALIAS,
713 CLOSING_DAI_SEQUENCE_NUMBER,
714 CEREMONY_START_TIME,
715 CEREMONY_END_TIME,
716 CEREMONY_FEE,
717 NUMBER_OF_GUESTS,
718 CREATION_DATE,
719 CREATED_BY,
720 LAST_UPDATE_DATE,
721 LAST_UPDATED_BY,
722 LAST_UPDATE_LOGIN,
723 ORG_ID
724 ) values (
725 NEW_REFERENCES.GRD_CAL_TYPE,
726 NEW_REFERENCES.GRD_CI_SEQUENCE_NUMBER,
727 NEW_REFERENCES.CEREMONY_NUMBER,
728 NEW_REFERENCES.VENUE_CD,
729 NEW_REFERENCES.CEREMONY_DT_ALIAS,
730 NEW_REFERENCES.CEREMONY_DAI_SEQUENCE_NUMBER,
731 NEW_REFERENCES.CLOSING_DT_ALIAS,
732 NEW_REFERENCES.CLOSING_DAI_SEQUENCE_NUMBER,
733 NEW_REFERENCES.CEREMONY_START_TIME,
734 NEW_REFERENCES.CEREMONY_END_TIME,
735 NEW_REFERENCES.CEREMONY_FEE,
736 NEW_REFERENCES.NUMBER_OF_GUESTS,
737 X_LAST_UPDATE_DATE,
738 X_LAST_UPDATED_BY,
739 X_LAST_UPDATE_DATE,
740 X_LAST_UPDATED_BY,
741 X_LAST_UPDATE_LOGIN,
742 NEW_REFERENCES.ORG_ID
743 );
744
745 open c;
746 fetch c into X_ROWID;
747 if (c%notfound) then
748 close c;
749 raise no_data_found;
750 end if;
751 close c;
752
753 After_DML (
754 p_action => 'INSERT',
755 x_rowid => X_ROWID
756 );
757
758 end INSERT_ROW;
759
760 procedure LOCK_ROW (
761 X_ROWID in VARCHAR2,
762 X_GRD_CAL_TYPE in VARCHAR2,
763 X_GRD_CI_SEQUENCE_NUMBER in NUMBER,
764 X_CEREMONY_NUMBER in NUMBER,
765 X_VENUE_CD in VARCHAR2,
766 X_CEREMONY_DT_ALIAS in VARCHAR2,
767 X_CEREMONY_DAI_SEQUENCE_NUMBER in NUMBER,
768 X_CLOSING_DT_ALIAS in VARCHAR2,
769 X_CLOSING_DAI_SEQUENCE_NUMBER in NUMBER,
770 X_CEREMONY_START_TIME in DATE,
771 X_CEREMONY_END_TIME in DATE,
772 X_CEREMONY_FEE in NUMBER,
773 X_NUMBER_OF_GUESTS in NUMBER
774 ) AS
775 cursor c1 is select
776 VENUE_CD,
777 CEREMONY_DT_ALIAS,
778 CEREMONY_DAI_SEQUENCE_NUMBER,
779 CLOSING_DT_ALIAS,
780 CLOSING_DAI_SEQUENCE_NUMBER,
781 CEREMONY_START_TIME,
782 CEREMONY_END_TIME,
783 CEREMONY_FEE,
784 NUMBER_OF_GUESTS
785 from IGS_GR_CRMN_ALL
786 where ROWID = X_ROWID for update nowait;
787 tlinfo c1%rowtype;
788
789 begin
790 open c1;
791 fetch c1 into tlinfo;
792 if (c1%notfound) then
793 close c1;
794 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
795 app_exception.raise_exception;
796 return;
797 end if;
798 close c1;
799
800 if ( (tlinfo.VENUE_CD = X_VENUE_CD)
801 AND (tlinfo.CEREMONY_DT_ALIAS = X_CEREMONY_DT_ALIAS)
802 AND (tlinfo.CEREMONY_DAI_SEQUENCE_NUMBER = X_CEREMONY_DAI_SEQUENCE_NUMBER)
803 AND (tlinfo.CLOSING_DT_ALIAS = X_CLOSING_DT_ALIAS)
804 AND (tlinfo.CLOSING_DAI_SEQUENCE_NUMBER = X_CLOSING_DAI_SEQUENCE_NUMBER)
805 AND (tlinfo.CEREMONY_START_TIME = X_CEREMONY_START_TIME)
806 AND (tlinfo.CEREMONY_END_TIME = X_CEREMONY_END_TIME)
807 AND ((tlinfo.CEREMONY_FEE = X_CEREMONY_FEE)
808 OR ((tlinfo.CEREMONY_FEE is null)
809 AND (X_CEREMONY_FEE is null)))
810 AND ((tlinfo.NUMBER_OF_GUESTS = X_NUMBER_OF_GUESTS)
811 OR ((tlinfo.NUMBER_OF_GUESTS is null)
812 AND (X_NUMBER_OF_GUESTS is null)))
813
814 ) then
815 null;
816 else
817 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
818 app_exception.raise_exception;
819 end if;
820 return;
821 end LOCK_ROW;
822
823 procedure UPDATE_ROW (
824 X_ROWID in VARCHAR2,
825 X_GRD_CAL_TYPE in VARCHAR2,
826 X_GRD_CI_SEQUENCE_NUMBER in NUMBER,
827 X_CEREMONY_NUMBER in NUMBER,
828 X_VENUE_CD in VARCHAR2,
829 X_CEREMONY_DT_ALIAS in VARCHAR2,
830 X_CEREMONY_DAI_SEQUENCE_NUMBER in NUMBER,
831 X_CLOSING_DT_ALIAS in VARCHAR2,
832 X_CLOSING_DAI_SEQUENCE_NUMBER in NUMBER,
833 X_CEREMONY_START_TIME in DATE,
834 X_CEREMONY_END_TIME in DATE,
835 X_CEREMONY_FEE in NUMBER,
836 X_NUMBER_OF_GUESTS in NUMBER,
837 X_MODE in VARCHAR2 default 'R'
838 ) AS
839 X_LAST_UPDATE_DATE DATE;
840 X_LAST_UPDATED_BY NUMBER;
841 X_LAST_UPDATE_LOGIN NUMBER;
842 begin
843 X_LAST_UPDATE_DATE := SYSDATE;
844 if(X_MODE = 'I') then
845 X_LAST_UPDATED_BY := 1;
846 X_LAST_UPDATE_LOGIN := 0;
847 elsif (X_MODE = 'R') then
848 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
849 if X_LAST_UPDATED_BY is NULL then
850 X_LAST_UPDATED_BY := -1;
851 end if;
852 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
853 if X_LAST_UPDATE_LOGIN is NULL then
854 X_LAST_UPDATE_LOGIN := -1;
855 end if;
856 else
857 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
858 IGS_GE_MSG_STACK.ADD;
859 app_exception.raise_exception;
860 end if;
861
862 Before_DML (
863 p_action => 'UPDATE',
864 x_rowid => X_ROWID,
865 x_grd_cal_type => X_GRD_CAL_TYPE,
866 x_grd_ci_sequence_number => X_GRD_CI_SEQUENCE_NUMBER,
867 x_ceremony_number => X_CEREMONY_NUMBER,
868 x_venue_cd => X_VENUE_CD,
869 x_ceremony_dt_alias => X_CEREMONY_DT_ALIAS,
870 x_ceremony_dai_sequence_number => X_CEREMONY_DAI_SEQUENCE_NUMBER,
871 x_closing_dt_alias => X_CLOSING_DT_ALIAS,
872 x_closing_dai_sequence_number => X_CLOSING_DAI_SEQUENCE_NUMBER,
873 x_ceremony_start_time => X_CEREMONY_START_TIME,
874 x_ceremony_end_time => X_CEREMONY_END_TIME,
875 x_ceremony_fee => X_CEREMONY_FEE,
876 x_number_of_guests => X_NUMBER_OF_GUESTS,
877 x_creation_date => X_LAST_UPDATE_DATE,
878 x_created_by => X_LAST_UPDATED_BY,
879 x_last_update_date => X_LAST_UPDATE_DATE,
880 x_last_updated_by => X_LAST_UPDATED_BY,
881 x_last_update_login => X_LAST_UPDATE_LOGIN
882 );
883
884 update IGS_GR_CRMN_ALL set
885 VENUE_CD = NEW_REFERENCES.VENUE_CD,
886 CEREMONY_DT_ALIAS = NEW_REFERENCES.CEREMONY_DT_ALIAS,
887 CEREMONY_DAI_SEQUENCE_NUMBER = NEW_REFERENCES.CEREMONY_DAI_SEQUENCE_NUMBER,
888 CLOSING_DT_ALIAS = NEW_REFERENCES.CLOSING_DT_ALIAS,
889 CLOSING_DAI_SEQUENCE_NUMBER = NEW_REFERENCES.CLOSING_DAI_SEQUENCE_NUMBER,
890 CEREMONY_START_TIME = NEW_REFERENCES.CEREMONY_START_TIME,
891 CEREMONY_END_TIME = NEW_REFERENCES.CEREMONY_END_TIME,
892 CEREMONY_FEE = NEW_REFERENCES.CEREMONY_FEE,
893 NUMBER_OF_GUESTS = NEW_REFERENCES.NUMBER_OF_GUESTS,
894 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
895 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
896 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
897 where ROWID = X_ROWID
898 ;
899 if (sql%notfound) then
900 raise no_data_found;
901 end if;
902
903 After_DML (
904 p_action => 'UPDATE',
905 x_rowid => X_ROWID
906 );
907
908 end UPDATE_ROW;
909
910 procedure ADD_ROW (
911 X_ROWID in out NOCOPY VARCHAR2,
912 X_GRD_CAL_TYPE in VARCHAR2,
913 X_GRD_CI_SEQUENCE_NUMBER in NUMBER,
914 X_CEREMONY_NUMBER in NUMBER,
915 X_VENUE_CD in VARCHAR2,
916 X_CEREMONY_DT_ALIAS in VARCHAR2,
917 X_CEREMONY_DAI_SEQUENCE_NUMBER in NUMBER,
918 X_CLOSING_DT_ALIAS in VARCHAR2,
919 X_CLOSING_DAI_SEQUENCE_NUMBER in NUMBER,
920 X_CEREMONY_START_TIME in DATE,
921 X_CEREMONY_END_TIME in DATE,
922 X_CEREMONY_FEE in NUMBER,
923 X_NUMBER_OF_GUESTS in NUMBER,
924 X_MODE in VARCHAR2 default 'R',
925 X_ORG_ID in NUMBER
926 ) AS
927 cursor c1 is select rowid from IGS_GR_CRMN_ALL
928 where GRD_CAL_TYPE = X_GRD_CAL_TYPE
929 and GRD_CI_SEQUENCE_NUMBER = X_GRD_CI_SEQUENCE_NUMBER
930 and CEREMONY_NUMBER = X_CEREMONY_NUMBER
931 ;
932
933 begin
934 open c1;
935 fetch c1 into X_ROWID;
936 if (c1%notfound) then
937 close c1;
938 INSERT_ROW (
939 X_ROWID,
940 X_GRD_CAL_TYPE,
941 X_GRD_CI_SEQUENCE_NUMBER,
942 X_CEREMONY_NUMBER,
943 X_VENUE_CD,
944 X_CEREMONY_DT_ALIAS,
945 X_CEREMONY_DAI_SEQUENCE_NUMBER,
946 X_CLOSING_DT_ALIAS,
947 X_CLOSING_DAI_SEQUENCE_NUMBER,
948 X_CEREMONY_START_TIME,
949 X_CEREMONY_END_TIME,
950 X_CEREMONY_FEE,
951 X_NUMBER_OF_GUESTS,
952 X_MODE,
953 x_org_id
954 );
955 return;
956 end if;
957 close c1;
958 UPDATE_ROW (
959 X_ROWID,
960 X_GRD_CAL_TYPE,
961 X_GRD_CI_SEQUENCE_NUMBER,
962 X_CEREMONY_NUMBER,
963 X_VENUE_CD,
964 X_CEREMONY_DT_ALIAS,
965 X_CEREMONY_DAI_SEQUENCE_NUMBER,
966 X_CLOSING_DT_ALIAS,
967 X_CLOSING_DAI_SEQUENCE_NUMBER,
968 X_CEREMONY_START_TIME,
969 X_CEREMONY_END_TIME,
970 X_CEREMONY_FEE,
971 X_NUMBER_OF_GUESTS,
972 X_MODE
973 );
974 end ADD_ROW;
975
976 procedure DELETE_ROW (
977 X_ROWID in VARCHAR2
978 ) AS
979 begin
980
981 Before_DML (
982 p_action => 'DELETE',
983 x_rowid => X_ROWID
984 );
985
986 delete from IGS_GR_CRMN_ALL
987 where ROWID = X_ROWID;
988 if (sql%notfound) then
989 raise no_data_found;
990 end if;
991
992 end DELETE_ROW;
993
994 end IGS_GR_CRMN_PKG;