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