[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_AWD_PKG
Source
1 PACKAGE BODY IGS_PS_AWD_PKG AS
2 /* $Header: IGSPI01B.pls 115.9 2003/02/25 08:10:44 sarakshi ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PS_AWD%ROWTYPE;
6 new_references IGS_PS_AWD%ROWTYPE;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_award_cd IN VARCHAR2 DEFAULT NULL,
12 x_award_title IN VARCHAR2 DEFAULT NULL,
13 x_s_award_type IN VARCHAR2 DEFAULT NULL,
14 x_testamur_type IN VARCHAR2 DEFAULT NULL,
15 x_closed_ind IN VARCHAR2 DEFAULT NULL,
16 x_notes IN VARCHAR2 DEFAULT NULL,
17 x_creation_date IN DATE DEFAULT NULL,
18 x_created_by IN NUMBER DEFAULT NULL,
19 x_last_update_date IN DATE DEFAULT NULL,
20 x_last_updated_by IN NUMBER DEFAULT NULL,
21 x_last_update_login IN NUMBER DEFAULT NULL ,
22 x_grading_schema_cd IN VARCHAR2 DEFAULT NULL ,
23 x_gs_version_number IN NUMBER DEFAULT NULL
24 ) AS
25
26 CURSOR cur_old_ref_values IS
27 SELECT *
28 FROM IGS_PS_AWD
29 WHERE ROWID = x_rowid;
30
31 BEGIN
32
33 l_rowid := x_rowid;
34
35 -- Code for setting the Old and New Reference Values.
36 -- Populate Old Values.
37 OPEN cur_old_ref_values;
38 FETCH cur_old_ref_values INTO old_references;
39 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
40 CLOSE cur_old_ref_values;
41 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
42 IGS_GE_MSG_STACK.ADD;
43 App_Exception.Raise_Exception; RETURN;
44 END IF;
45 CLOSE cur_old_ref_values;
46
47 -- Populate New Values.
48 new_references.award_cd := x_award_cd;
49 new_references.award_title := x_award_title;
50 new_references.s_award_type := x_s_award_type;
51 new_references.testamur_type := x_testamur_type;
52 new_references.closed_ind := x_closed_ind;
53 new_references.notes := x_notes;
54 IF (p_action = 'UPDATE') THEN
55 new_references.creation_date := old_references.creation_date;
56 new_references.created_by := old_references.created_by;
57 ELSE
58 new_references.creation_date := x_creation_date;
59 new_references.created_by := x_created_by;
60 END IF;
61 new_references.last_update_date := x_last_update_date;
62 new_references.last_updated_by := x_last_updated_by;
63 new_references.last_update_login := x_last_update_login;
64
65 -- Added by aiyer for the build of PSCR015 Tertiary And Secondary Bug No #2216952
66 new_references.grading_schema_cd := x_grading_schema_cd;
67 new_references.gs_version_number := x_gs_version_number;
68
69 END Set_Column_Values;
70
71 PROCEDURE BeforeRowInsertUpdate1(
72 p_inserting IN BOOLEAN DEFAULT FALSE,
73 p_updating IN BOOLEAN DEFAULT FALSE,
74 p_deleting IN BOOLEAN DEFAULT FALSE
75 ) AS
76 v_message_name VARCHAR2(30);
77 BEGIN
78 -- Validate the IGS_PS_AWD is not closed
79 IF p_inserting OR p_updating THEN
80 IF NVL(new_references.testamur_type, 'NULL') <> NVL(old_references.testamur_type, 'NULL') THEN
81 IF new_references.testamur_type IS NOT NULL THEN
82 IF IGS_PS_VAL_AW.crsp_val_tt_closed(
83 new_references.testamur_type,
84 v_message_name) = FALSE THEN
85 Fnd_Message.Set_Name('IGS',v_message_name);
86 IGS_GE_MSG_STACK.ADD;
87 App_Exception.Raise_Exception;
88 END IF;
89 END IF;
90 END IF;
91 END IF;
92 -- Validate update to IGS_PS_AWD system IGS_PS_AWD type
93 IF p_updating THEN
94 IF IGS_PS_VAL_AW.crsp_val_aw_upd(
95 new_references.award_cd,
96 new_references.s_award_type,
97 old_references.s_award_type,
98 v_message_name) = FALSE THEN
99 Fnd_Message.Set_Name('IGS',v_message_name);
100 IGS_GE_MSG_STACK.ADD;
101 App_Exception.Raise_Exception;
102 END IF;
103 END IF;
104
105 END BeforeRowInsertUpdate1;
106
107 PROCEDURE Check_Constraints (
108 Column_Name IN VARCHAR2 DEFAULT NULL,
109 Column_Value IN VARCHAR2 DEFAULT NULL
110 )
111 AS
112 BEGIN
113
114 IF column_name IS NULL THEN
115 NULL;
116 ELSIF UPPER(Column_name) = 'CLOSED_IND' THEN
117 new_references.closed_ind := column_value;
118 ELSIF UPPER(Column_name) = 'S_AWARD_TYPE' THEN
119 new_references.s_award_type := column_value;
120 ELSIF UPPER(Column_name) = 'AWARD_CD' THEN
121 new_references.award_cd := column_value;
122 ELSIF UPPER(Column_name) = 'TESTAMUR_TYPE' THEN
123 new_references.testamur_type := column_value;
124 ELSIF UPPER(Column_name) = 'GRADING_SCHEMA_CD' THEN
125 new_references.GRADING_SCHEMA_CD := column_value;
126 ELSIF UPPER(Column_name) = 'GS_VERSION_NUMBER' THEN
127 new_references.GS_VERSION_NUMBER := igs_ge_number.to_num(column_value);
128 END IF;
129
130 IF UPPER(column_name) = 'CLOSED_IND' OR
131 column_name IS NULL THEN
132 IF ( new_references.closed_ind NOT IN ( 'Y' , 'N' ) ) THEN
133 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
134 IGS_GE_MSG_STACK.ADD;
135 App_Exception.Raise_Exception;
136 END IF;
137 END IF;
138
139 IF UPPER(column_name) = 'S_AWARD_TYPE' OR
140 column_name IS NULL THEN
141 IF ( new_references.s_award_type NOT IN ( 'COURSE' , 'HONORARY' , 'MEDAL' , 'PRIZE' ,'ENTRYQUAL') ) THEN
142 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
143 IGS_GE_MSG_STACK.ADD;
144 App_Exception.Raise_Exception;
145 END IF;
146 END IF;
147
148 IF UPPER(column_name) = 'AWARD_CD' OR
149 column_name IS NULL THEN
150 IF ( new_references.award_cd <> UPPER(new_references.award_cd) ) THEN
151 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
152 IGS_GE_MSG_STACK.ADD;
153 App_Exception.Raise_Exception;
154 END IF;
155 END IF;
156
157 IF UPPER(column_name) = 'CLOSED_IND' OR
158 column_name IS NULL THEN
159 IF ( new_references.closed_ind <> UPPER(new_references.closed_ind) ) THEN
160 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
161 IGS_GE_MSG_STACK.ADD;
162 App_Exception.Raise_Exception;
163 END IF;
164 END IF;
165
166 IF UPPER(column_name) = 'S_AWARD_TYPE' OR
167 column_name IS NULL THEN
168 IF ( new_references.s_award_type <> UPPER(new_references.s_award_type) ) THEN
169 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
170 IGS_GE_MSG_STACK.ADD;
171 App_Exception.Raise_Exception;
172 END IF;
173 END IF;
174
175 IF UPPER(column_name) = 'TESTAMUR_TYPE' OR
176 column_name IS NULL THEN
177 IF ( new_references.testamur_type <> UPPER(new_references.testamur_type) ) THEN
178 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
179 IGS_GE_MSG_STACK.ADD;
180 App_Exception.Raise_Exception;
181 END IF;
182 END IF;
183
184 -- Added by aiyer for the build of PSCR015 Tertiary And Secondary Bug No #2216952
185 IF UPPER(column_name) = 'GRADING_SCHEMA_CD' OR
186 column_name IS NULL THEN
187 IF new_references.GRADING_SCHEMA_CD <> UPPER(new_references.GRADING_SCHEMA_CD) THEN
188 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
189 IGS_GE_MSG_STACK.ADD;
190 APP_EXCEPTION.RAISE_EXCEPTION;
191 END IF;
192 END IF;
193
194 -- Added by aiyer for the build of PSCR015 Tertiary And Secondary Bug No #2216952
195 IF UPPER(column_name) = 'GS_VERSION_NUMBER' OR
196 column_name IS NULL THEN
197 IF new_references.GS_VERSION_NUMBER < 0 OR new_references.GS_VERSION_NUMBER > 999 THEN
198 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
199 IGS_GE_MSG_STACK.ADD;
200 APP_EXCEPTION.RAISE_EXCEPTION;
201 END IF;
202 END IF;
203
204 END Check_Constraints;
205
206 PROCEDURE Check_Parent_Existance AS
207 BEGIN
208
209 IF (((old_references.testamur_type = new_references.testamur_type)) OR
210 ((new_references.testamur_type IS NULL))) THEN
211 NULL;
212 ELSE
213 IF NOT IGS_GR_TESTAMUR_TYPE_PKG.Get_PK_For_Validation (
214 new_references.testamur_type ) THEN
215 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
216 IGS_GE_MSG_STACK.ADD;
217 App_Exception.Raise_Exception;
218 END IF;
219 END IF;
220
221 -- Added by aiyer for the build of PSCR015 Tertiary And Secondary Bug No #2216952
222
223 IF (
224 (
225 (old_references.grading_schema_cd = new_references.grading_schema_cd)
226 AND
227 (old_references.gs_version_number = new_references.gs_version_number)
228 )
229 OR
230 (
231 (new_references.grading_schema_cd IS NULL)
232 AND
233 (new_references.gs_version_number IS NULL)
234 )
235 )
236 THEN
237 NULL;
238 ELSE
239 IF NOT IGS_AS_GRD_SCHEMA_PKG.Get_PK_For_Validation (
240 new_references.grading_schema_cd,
241 new_references.gs_version_number
242 ) THEN
243 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
244 IGS_GE_MSG_STACK.ADD;
245 App_Exception.Raise_Exception;
246 END IF;
247 END IF;
248
249 END Check_Parent_Existance;
250
251
252 FUNCTION Get_PK_For_Validation (
253 x_award_cd IN VARCHAR2 )
254 RETURN BOOLEAN AS
255
256 CURSOR cur_rowid IS
257 SELECT ROWID
258 FROM IGS_PS_AWD
259 WHERE award_cd = x_award_cd;
260
261 lv_rowid cur_rowid%ROWTYPE;
262
263 BEGIN
264
265 OPEN cur_rowid;
266 FETCH cur_rowid INTO lv_rowid;
267 IF (cur_rowid%FOUND) THEN
268 CLOSE cur_rowid;
269 RETURN (TRUE);
270 ELSE
271 CLOSE cur_rowid;
272 RETURN (FALSE);
273 END IF;
274
275 END Get_PK_For_Validation;
276
277 PROCEDURE GET_FK_IGS_GR_TESTAMUR_TYPE (
278 x_testamur_type IN VARCHAR2
279 ) AS
280
281 CURSOR cur_rowid IS
282 SELECT ROWID
283 FROM IGS_PS_AWD
284 WHERE testamur_type = x_testamur_type ;
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_PS_AW_TT_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_FK_IGS_GR_TESTAMUR_TYPE;
302
303 -- Added by aiyer for the build of PSCR015 Tertiary And Secondary Bug No #2216952
304
305 PROCEDURE get_fk_igs_as_grd_schema (
306 x_grading_schema_cd IN VARCHAR2 ,
307 x_gs_version_number IN NUMBER
308 ) AS
309
310 CURSOR cur_rowid IS
311 SELECT ROWID
312 FROM IGS_PS_AWD
313 WHERE grading_schema_cd = x_grading_schema_cd
314 AND
315 gs_version_number = x_gs_version_number;
316
317 lv_rowid cur_rowid%ROWTYPE;
318
319 BEGIN
320
321 OPEN cur_rowid;
322 FETCH cur_rowid INTO lv_rowid;
323 IF (cur_rowid%FOUND) THEN
324 CLOSE cur_rowid;
325 Fnd_Message.Set_Name ('IGS', 'IGS_PS_AW_GS_FK');
326 IGS_GE_MSG_STACK.ADD;
327 App_Exception.Raise_Exception;
328 RETURN;
329 END IF;
330 CLOSE cur_rowid;
331
332 END get_fk_igs_as_grd_schema;
333
334 PROCEDURE Before_DML (
335 p_action IN VARCHAR2,
336 x_rowid IN VARCHAR2 DEFAULT NULL,
337 x_award_cd IN VARCHAR2 DEFAULT NULL,
338 x_award_title IN VARCHAR2 DEFAULT NULL,
339 x_s_award_type IN VARCHAR2 DEFAULT NULL,
340 x_testamur_type IN VARCHAR2 DEFAULT NULL,
341 x_closed_ind IN VARCHAR2 DEFAULT NULL,
342 x_notes IN VARCHAR2 DEFAULT NULL,
343 x_creation_date IN DATE DEFAULT NULL,
344 x_created_by IN NUMBER DEFAULT NULL,
345 x_last_update_date IN DATE DEFAULT NULL,
346 x_last_updated_by IN NUMBER DEFAULT NULL,
347 x_last_update_login IN NUMBER DEFAULT NULL ,
348 x_grading_schema_cd IN VARCHAR2 DEFAULT NULL ,
349 x_gs_version_number IN NUMBER DEFAULT NULL
350 ) AS
351 BEGIN
352
353 Set_Column_Values (
354 p_action,
355 x_rowid,
356 x_award_cd,
357 x_award_title,
358 x_s_award_type,
359 x_testamur_type,
360 x_closed_ind,
361 x_notes,
362 x_creation_date,
363 x_created_by,
364 x_last_update_date,
365 x_last_updated_by,
366 x_last_update_login ,
367 x_grading_schema_cd ,
368 x_gs_version_number
369 );
370
371 IF (p_action = 'INSERT') THEN
372 -- Call all the procedures related to Before Insert.
373 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
374 IF Get_PK_For_Validation (
375 new_references.award_cd) THEN
376 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
377 IGS_GE_MSG_STACK.ADD;
378 App_Exception.Raise_Exception;
379 END IF;
380 Check_Constraints;
381 Check_Parent_Existance;
382 ELSIF (p_action = 'UPDATE') THEN
383 -- Call all the procedures related to Before Update.
384 BeforeRowInsertUpdate1 ( p_updating => TRUE );
385 Check_Constraints;
386 Check_Parent_Existance;
387 ELSIF (p_action = 'VALIDATE_INSERT') THEN
388 IF Get_PK_For_Validation (
389 new_references.award_cd ) THEN
390 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
391 IGS_GE_MSG_STACK.ADD;
392 App_Exception.Raise_Exception;
393 END IF;
394 Check_Constraints;
395 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
396 Check_Constraints;
397 END IF;
398
399 END Before_DML;
400
401 PROCEDURE After_DML (
402 p_action IN VARCHAR2,
403 x_rowid IN VARCHAR2
404 ) AS
405 BEGIN
406
407 l_rowid := x_rowid;
408
409 END After_DML;
410
411 PROCEDURE INSERT_ROW (
412 X_ROWID IN OUT NOCOPY VARCHAR2,
413 X_AWARD_CD IN VARCHAR2,
414 X_AWARD_TITLE IN VARCHAR2,
415 X_S_AWARD_TYPE IN VARCHAR2,
416 X_TESTAMUR_TYPE IN VARCHAR2,
417 X_CLOSED_IND IN VARCHAR2,
418 X_NOTES IN VARCHAR2,
419 X_MODE IN VARCHAR2 DEFAULT 'R' ,
420 X_GRADING_SCHEMA_CD IN VARCHAR2 ,
421 X_GS_VERSION_NUMBER IN NUMBER
422 ) AS
423 CURSOR C IS SELECT ROWID FROM IGS_PS_AWD
424 WHERE AWARD_CD = X_AWARD_CD;
425 X_LAST_UPDATE_DATE DATE;
426 X_LAST_UPDATED_BY NUMBER;
427 X_LAST_UPDATE_LOGIN NUMBER;
428 BEGIN
429 X_LAST_UPDATE_DATE := SYSDATE;
430 IF(X_MODE = 'I') THEN
431 X_LAST_UPDATED_BY := 1;
432 X_LAST_UPDATE_LOGIN := 0;
433 ELSIF (X_MODE = 'R') THEN
434 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
435 IF X_LAST_UPDATED_BY IS NULL THEN
436 X_LAST_UPDATED_BY := -1;
437 END IF;
438 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
439 IF X_LAST_UPDATE_LOGIN IS NULL THEN
440 X_LAST_UPDATE_LOGIN := -1;
441 END IF;
442 ELSE
443 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
444 IGS_GE_MSG_STACK.ADD;
445 app_exception.raise_exception;
446 END IF;
447
448 Before_DML (
449 p_action => 'INSERT',
450 x_rowid => X_ROWID,
451 x_award_cd => X_AWARD_CD,
452 x_award_title => X_AWARD_TITLE,
453 x_s_award_type => X_S_AWARD_TYPE,
454 x_testamur_type => X_TESTAMUR_TYPE,
455 x_closed_ind => NVL(X_CLOSED_IND,'N'),
456 x_notes => X_NOTES,
457 x_creation_date => X_LAST_UPDATE_DATE,
458 x_created_by => X_LAST_UPDATED_BY,
459 x_last_update_date => X_LAST_UPDATE_DATE,
460 x_last_updated_by => X_LAST_UPDATED_BY,
461 x_last_update_login => X_LAST_UPDATE_LOGIN ,
462 x_grading_schema_cd => X_GRADING_SCHEMA_CD,
463 x_gs_version_number => X_GS_VERSION_NUMBER
464 );
465
466 INSERT INTO IGS_PS_AWD (
467 AWARD_CD,
468 AWARD_TITLE,
469 S_AWARD_TYPE,
470 TESTAMUR_TYPE,
471 CLOSED_IND,
472 NOTES,
473 CREATION_DATE,
474 CREATED_BY,
475 LAST_UPDATE_DATE,
476 LAST_UPDATED_BY,
477 LAST_UPDATE_LOGIN,
478 GRADING_SCHEMA_CD,
479 GS_VERSION_NUMBER
480 ) VALUES (
481 NEW_REFERENCES.AWARD_CD,
482 NEW_REFERENCES.AWARD_TITLE,
483 NEW_REFERENCES.S_AWARD_TYPE,
484 NEW_REFERENCES.TESTAMUR_TYPE,
485 NEW_REFERENCES.CLOSED_IND,
486 NEW_REFERENCES.NOTES,
487 X_LAST_UPDATE_DATE,
488 X_LAST_UPDATED_BY,
489 X_LAST_UPDATE_DATE,
490 X_LAST_UPDATED_BY,
491 X_LAST_UPDATE_LOGIN,
492 NEW_REFERENCES.GRADING_SCHEMA_CD,
493 NEW_REFERENCES.GS_VERSION_NUMBER
494 );
495
496 OPEN c;
497 FETCH c INTO X_ROWID;
498 IF (c%NOTFOUND) THEN
499 CLOSE c;
500 RAISE NO_DATA_FOUND;
501 END IF;
502 CLOSE c;
503
504 After_DML (
505 p_action => 'INSERT',
506 x_rowid => X_ROWID
507 );
508
509 END INSERT_ROW;
510
511 PROCEDURE LOCK_ROW (
512 X_ROWID IN VARCHAR2,
513 X_AWARD_CD IN VARCHAR2,
514 X_AWARD_TITLE IN VARCHAR2,
515 X_S_AWARD_TYPE IN VARCHAR2,
516 X_TESTAMUR_TYPE IN VARCHAR2,
517 X_CLOSED_IND IN VARCHAR2,
518 X_NOTES IN VARCHAR2 ,
519 X_GRADING_SCHEMA_CD IN VARCHAR2 ,
520 X_GS_VERSION_NUMBER IN NUMBER
521 ) AS
522 CURSOR c1 IS SELECT
523 AWARD_TITLE,
524 S_AWARD_TYPE,
525 TESTAMUR_TYPE,
526 CLOSED_IND,
527 NOTES,
528 GRADING_SCHEMA_CD,
529 GS_VERSION_NUMBER
530 FROM IGS_PS_AWD
531 WHERE ROWID = X_ROWID
532 FOR UPDATE NOWAIT;
533 tlinfo c1%ROWTYPE;
534
535 BEGIN
536 OPEN c1;
537 FETCH c1 INTO tlinfo;
538 IF (c1%NOTFOUND) THEN
539 CLOSE c1;
540 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
541 IGS_GE_MSG_STACK.ADD;
542 app_exception.raise_exception;
543 RETURN;
544 END IF;
545 CLOSE c1;
546
547 IF (
548 (tlinfo.AWARD_TITLE = X_AWARD_TITLE)
549 AND (tlinfo.S_AWARD_TYPE = X_S_AWARD_TYPE)
550 AND ((tlinfo.TESTAMUR_TYPE = X_TESTAMUR_TYPE)
551 OR ((tlinfo.TESTAMUR_TYPE IS NULL)
552 AND (X_TESTAMUR_TYPE IS NULL)))
553 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
554 AND ((tlinfo.NOTES = X_NOTES)
555 OR ((tlinfo.NOTES IS NULL)
556 AND (X_NOTES IS NULL)))
557 AND (
558 (tlinfo.grading_schema_cd = X_grading_schema_cd)
559 OR
560 (
561 (tlinfo.grading_schema_cd IS NULL)
562 AND
563 (x_grading_schema_cd IS NULL)
564 )
565 )
566 AND (
567 (tlinfo.gs_version_number = X_gs_version_number)
568 OR
569 (
570 (tlinfo.gs_version_number IS NULL)
571 AND
572 (x_gs_version_number IS NULL)
573 )
574 )
575
576 ) THEN
577 NULL;
578 ELSE
579 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
580 IGS_GE_MSG_STACK.ADD;
581 app_exception.raise_exception;
582 END IF;
583 RETURN;
584 END LOCK_ROW;
585
586 PROCEDURE UPDATE_ROW (
587 X_ROWID IN VARCHAR2,
588 X_AWARD_CD IN VARCHAR2,
589 X_AWARD_TITLE IN VARCHAR2,
590 X_S_AWARD_TYPE IN VARCHAR2,
591 X_TESTAMUR_TYPE IN VARCHAR2,
592 X_CLOSED_IND IN VARCHAR2,
593 X_NOTES IN VARCHAR2,
594 X_MODE IN VARCHAR2 DEFAULT 'R' ,
595 X_GRADING_SCHEMA_CD IN VARCHAR2 ,
596 X_GS_VERSION_NUMBER IN NUMBER
597 ) AS
598 X_LAST_UPDATE_DATE DATE;
599 X_LAST_UPDATED_BY NUMBER;
600 X_LAST_UPDATE_LOGIN NUMBER;
601 BEGIN
602 X_LAST_UPDATE_DATE := SYSDATE;
603 IF(X_MODE = 'I') THEN
604 X_LAST_UPDATED_BY := 1;
605 X_LAST_UPDATE_LOGIN := 0;
606 ELSIF (X_MODE = 'R') THEN
607 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
608 IF X_LAST_UPDATED_BY IS NULL THEN
609 X_LAST_UPDATED_BY := -1;
610 END IF;
611 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
612 IF X_LAST_UPDATE_LOGIN IS NULL THEN
613 X_LAST_UPDATE_LOGIN := -1;
614 END IF;
615 ELSE
616 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
617 IGS_GE_MSG_STACK.ADD;
618 app_exception.raise_exception;
619 END IF;
620
621 Before_DML (
622 p_action => 'UPDATE',
623 x_rowid => X_ROWID,
624 x_award_cd => X_AWARD_CD ,
625 x_award_title => X_AWARD_TITLE ,
626 x_s_award_type => X_S_AWARD_TYPE ,
627 x_testamur_type => X_TESTAMUR_TYPE ,
628 x_closed_ind => X_CLOSED_IND ,
629 x_notes => X_NOTES ,
630 x_creation_date => X_LAST_UPDATE_DATE ,
631 x_created_by => X_LAST_UPDATED_BY ,
632 x_last_update_date => X_LAST_UPDATE_DATE ,
633 x_last_updated_by => X_LAST_UPDATED_BY ,
634 x_last_update_login => X_LAST_UPDATE_LOGIN ,
635 x_grading_schema_cd => X_GRADING_SCHEMA_CD ,
636 x_gs_version_number => X_GS_VERSION_NUMBER
637 );
638
639 UPDATE IGS_PS_AWD SET
640 AWARD_TITLE = NEW_REFERENCES.AWARD_TITLE,
641 S_AWARD_TYPE = NEW_REFERENCES.S_AWARD_TYPE,
642 TESTAMUR_TYPE = NEW_REFERENCES.TESTAMUR_TYPE,
643 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
644 NOTES = NEW_REFERENCES.NOTES,
645 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
646 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
647 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
648 GRADING_SCHEMA_CD = NEW_REFERENCES.GRADING_SCHEMA_CD ,
649 GS_VERSION_NUMBER = NEW_REFERENCES.GS_VERSION_NUMBER
650 WHERE ROWID = X_ROWID
651 ;
652 IF (SQL%NOTFOUND) THEN
653 RAISE NO_DATA_FOUND;
654 END IF;
655
656 After_DML (
657 p_action => 'UPDATE',
658 x_rowid => X_ROWID
659 );
660
661 END UPDATE_ROW;
662
663 PROCEDURE ADD_ROW (
664 X_ROWID IN OUT NOCOPY VARCHAR2,
665 X_AWARD_CD IN VARCHAR2,
666 X_AWARD_TITLE IN VARCHAR2,
667 X_S_AWARD_TYPE IN VARCHAR2,
668 X_TESTAMUR_TYPE IN VARCHAR2,
669 X_CLOSED_IND IN VARCHAR2,
670 X_NOTES IN VARCHAR2,
671 X_MODE IN VARCHAR2 DEFAULT 'R',
672 X_GRADING_SCHEMA_CD IN VARCHAR2 ,
673 X_GS_VERSION_NUMBER IN NUMBER
674 ) AS
675 CURSOR c1 IS SELECT ROWID FROM IGS_PS_AWD
676 WHERE AWARD_CD = X_AWARD_CD
677 ;
678 BEGIN
679 OPEN c1;
680 FETCH c1 INTO X_ROWID;
681 IF (c1%NOTFOUND) THEN
682 CLOSE c1;
683 INSERT_ROW (
684 X_ROWID,
685 X_AWARD_CD,
686 X_AWARD_TITLE,
687 X_S_AWARD_TYPE,
688 X_TESTAMUR_TYPE,
689 X_CLOSED_IND,
690 X_NOTES,
691 X_MODE,
692 X_GRADING_SCHEMA_CD ,
693 X_GS_VERSION_NUMBER
694 );
695 RETURN;
696 END IF;
697 CLOSE c1;
698 UPDATE_ROW (
699 X_ROWID,
700 X_AWARD_CD,
701 X_AWARD_TITLE,
702 X_S_AWARD_TYPE,
703 X_TESTAMUR_TYPE,
704 X_CLOSED_IND,
705 X_NOTES,
706 X_MODE,
707 X_GRADING_SCHEMA_CD ,
708 X_GS_VERSION_NUMBER
709 );
710 END ADD_ROW;
711
712
713 END IGS_PS_AWD_PKG;