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