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