[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_MSHT_SU_ATMPT_PKG
Source
1 package body IGS_AS_MSHT_SU_ATMPT_PKG AS
2 /* $Header: IGSDI16B.pls 115.5 2003/04/30 05:51:32 svanukur ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_AS_MSHT_SU_ATMPT%RowType;
5 new_references IGS_AS_MSHT_SU_ATMPT%RowType;
6 PROCEDURE Set_Column_Values (
7 p_action IN VARCHAR2,
8 x_rowid IN VARCHAR2 DEFAULT NULL,
9 x_sheet_number IN NUMBER DEFAULT NULL,
10 x_person_id IN NUMBER DEFAULT NULL,
11 x_course_cd IN VARCHAR2 DEFAULT NULL,
12 x_unit_cd IN VARCHAR2 DEFAULT NULL,
13 x_cal_type IN VARCHAR2 DEFAULT NULL,
14 x_ci_sequence_number IN NUMBER DEFAULT NULL,
15 x_location_cd IN VARCHAR2 DEFAULT NULL,
16 x_unit_mode IN VARCHAR2 DEFAULT NULL,
17 x_unit_class IN VARCHAR2 DEFAULT NULL,
18 x_student_sequence IN NUMBER 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_uoo_id IN NUMBER DEFAULT NULL
25 ) AS
26 CURSOR cur_old_ref_values IS
27 SELECT *
28 FROM IGS_AS_MSHT_SU_ATMPT
29 WHERE rowid = x_rowid;
30 BEGIN
31 l_rowid := x_rowid;
32 -- Code for setting the Old and New Reference Values.
33 -- Populate Old Values.
34 Open cur_old_ref_values;
35 Fetch cur_old_ref_values INTO old_references;
36 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
37 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
38 IGS_GE_MSG_STACK.ADD;
39 Close cur_old_ref_values;
40 APP_EXCEPTION.RAISE_EXCEPTION;
41
42 Return;
43 END IF;
44 Close cur_old_ref_values;
45 -- Populate New Values.
46 new_references.sheet_number := x_sheet_number;
47 new_references.person_id := x_person_id;
48 new_references.course_cd := x_course_cd;
49 new_references.unit_cd := x_unit_cd;
50 new_references.cal_type := x_cal_type;
51 new_references.ci_sequence_number := x_ci_sequence_number;
52 new_references.location_cd := x_location_cd;
53 new_references.unit_mode := x_unit_mode;
54 new_references.unit_class := x_unit_class;
55 new_references.student_sequence := x_student_sequence;
56 new_references.uoo_id := x_uoo_id;
57 IF (p_action = 'UPDATE') THEN
58 new_references.creation_date := old_references.creation_date;
59 new_references.created_by := old_references.created_by;
60 ELSE
61 new_references.creation_date := x_creation_date;
62 new_references.created_by := x_created_by;
63 END IF;
64 new_references.last_update_date := x_last_update_date;
65 new_references.last_updated_by := x_last_updated_by;
66 new_references.last_update_login := x_last_update_login;
67 END Set_Column_Values;
68 PROCEDURE Check_Parent_Existance AS
69 BEGIN
70 IF (((old_references.sheet_number = new_references.sheet_number)) OR
71 ((new_references.sheet_number IS NULL))) THEN
72 NULL;
73 ELSIF NOT IGS_AS_MARK_SHEET_PKG.Get_PK_For_Validation (
74 new_references.sheet_number
75 ) THEN
76 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
77 IGS_GE_MSG_STACK.ADD;
78 APP_EXCEPTION.RAISE_EXCEPTION;
79 END IF;
80 IF (((old_references.person_id = new_references.person_id) AND
81 (old_references.course_cd = new_references.course_cd) AND
82 (old_references.uoo_id = new_references.uoo_id)) OR
83 ((new_references.person_id IS NULL) OR
84 (new_references.course_cd IS NULL) OR
85 (new_references.uoo_id IS NULL))) THEN
86 NULL;
87 ELSIF NOT IGS_EN_SU_ATTEMPT_PKG.Get_PK_For_Validation (
88 new_references.person_id,
89 new_references.course_cd,
90 new_references.uoo_id
91 ) THEN
92 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
93 IGS_GE_MSG_STACK.ADD;
94 APP_EXCEPTION.RAISE_EXCEPTION;
95 END IF;
96 END Check_Parent_Existance;
97
98 -------------------------------------------------------------------------------------------
99 --Change History:
100 --Who When What
101 --svanukur 29-APR-03 Added uoo_id as part of MUS build, # 2829262
102 -------------------------------------------------------------------------------------------
103 FUNCTION Get_PK_For_Validation (
104 x_sheet_number IN NUMBER,
105 x_course_cd IN VARCHAR2,
106 x_person_id IN NUMBER,
107 x_uoo_id IN NUMBER
108 ) RETURN BOOLEAN AS
109 CURSOR cur_rowid IS
110 SELECT rowid
111 FROM IGS_AS_MSHT_SU_ATMPT
112 WHERE sheet_number = x_sheet_number
113 AND course_cd = x_course_cd
114 AND person_id = x_person_id
115 AND uoo_id = x_uoo_id
116 FOR UPDATE NOWAIT;
117 lv_rowid cur_rowid%RowType;
118 BEGIN
119 Open cur_rowid;
120 Fetch cur_rowid INTO lv_rowid;
121 IF (cur_rowid%FOUND) THEN
122 Close cur_rowid;
123 Return (TRUE);
124 ELSE
125 Close cur_rowid;
126 Return (FALSE);
127 END IF;
128 END Get_PK_For_Validation;
129 PROCEDURE GET_FK_IGS_AS_MARK_SHEET (
130 x_sheet_number IN NUMBER
131 ) AS
132 CURSOR cur_rowid IS
133 SELECT rowid
134 FROM IGS_AS_MSHT_SU_ATMPT
135 WHERE sheet_number = x_sheet_number ;
136 lv_rowid cur_rowid%RowType;
137 BEGIN
138 Open cur_rowid;
139 Fetch cur_rowid INTO lv_rowid;
140 IF (cur_rowid%FOUND) THEN
141 Fnd_Message.Set_Name ('IGS', 'IGS_AS_MSSUA_MS_FK');
142 IGS_GE_MSG_STACK.ADD;
143 Close cur_rowid;
144 APP_EXCEPTION.RAISE_EXCEPTION;
145
146 Return;
147 END IF;
148 Close cur_rowid;
149 END GET_FK_IGS_AS_MARK_SHEET;
150
151 PROCEDURE GET_FK_IGS_EN_SU_ATTEMPT (
152 x_person_id IN NUMBER,
153 x_course_cd IN VARCHAR2,
154 x_uoo_id IN NUMBER
155 ) AS
156 CURSOR cur_rowid IS
157 SELECT rowid
158 FROM IGS_AS_MSHT_SU_ATMPT
159 WHERE person_id = x_person_id
160 AND course_cd = x_course_cd
161 AND uoo_id = x_uoo_id;
162
163 lv_rowid cur_rowid%RowType;
164 BEGIN
165 Open cur_rowid;
166 Fetch cur_rowid INTO lv_rowid;
167 IF (cur_rowid%FOUND) THEN
168 Fnd_Message.Set_Name ('IGS', 'IGS_AS_MSSUA_SUA_FK');
169 IGS_GE_MSG_STACK.ADD;
170 Close cur_rowid;
171 APP_EXCEPTION.RAISE_EXCEPTION;
172
173 Return;
174 END IF;
175 Close cur_rowid;
176 END GET_FK_IGS_EN_SU_ATTEMPT;
177 PROCEDURE Before_DML (
178 p_action IN VARCHAR2,
179 x_rowid IN VARCHAR2 DEFAULT NULL,
180 x_sheet_number IN NUMBER DEFAULT NULL,
181 x_person_id IN NUMBER DEFAULT NULL,
182 x_course_cd IN VARCHAR2 DEFAULT NULL,
183 x_unit_cd IN VARCHAR2 DEFAULT NULL,
184 x_cal_type IN VARCHAR2 DEFAULT NULL,
185 x_ci_sequence_number IN NUMBER DEFAULT NULL,
186 x_location_cd IN VARCHAR2 DEFAULT NULL,
187 x_unit_mode IN VARCHAR2 DEFAULT NULL,
188 x_unit_class IN VARCHAR2 DEFAULT NULL,
189 x_student_sequence IN NUMBER DEFAULT NULL,
190 x_creation_date IN DATE DEFAULT NULL,
191 x_created_by IN NUMBER DEFAULT NULL,
192 x_last_update_date IN DATE DEFAULT NULL,
193 x_last_updated_by IN NUMBER DEFAULT NULL,
194 x_last_update_login IN NUMBER DEFAULT NULL,
195 x_uoo_id IN NUMBER DEFAULT NULL
196 ) AS
197 BEGIN
198 Set_Column_Values (
199 p_action,
200 x_rowid,
201 x_sheet_number,
202 x_person_id,
203 x_course_cd,
204 x_unit_cd,
205 x_cal_type,
206 x_ci_sequence_number,
207 x_location_cd,
208 x_unit_mode,
209 x_unit_class,
210 x_student_sequence,
211 x_creation_date,
212 x_created_by,
213 x_last_update_date,
214 x_last_updated_by,
215 x_last_update_login,
216 x_uoo_id
217 );
218 IF (p_action = 'INSERT') THEN
219 -- Call all the procedures related to Before Insert.
220
221 IF Get_PK_For_Validation ( NEW_REFERENCES.sheet_number,
222 NEW_REFERENCES.course_cd ,
223 NEW_REFERENCES.person_id,
224 NEW_REFERENCES.uoo_id
225 ) THEN
226 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
227 IGS_GE_MSG_STACK.ADD;
228 APP_EXCEPTION.RAISE_EXCEPTION;
229 END IF;
230
231 Check_Constraints;
232
233 Check_Parent_Existance;
234 ELSIF (p_action = 'UPDATE') THEN
235 -- Call all the procedures related to Before Update.
236
237
238 Check_Constraints;
239 Check_Parent_Existance;
240
241 ELSIF (p_action = 'VALIDATE_INSERT') THEN
242 IF Get_PK_For_Validation (NEW_REFERENCES.sheet_number,
243 NEW_REFERENCES.course_cd ,
244 NEW_REFERENCES.person_id,
245 NEW_REFERENCES.uoo_id
246 ) THEN
247 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
248 IGS_GE_MSG_STACK.ADD;
249 APP_EXCEPTION.RAISE_EXCEPTION;
250 END IF;
251
252 CHECK_Constraints;
253 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
254
255 Check_Constraints;
256
257
258
259 END IF;
260 END Before_DML;
261
262 procedure INSERT_ROW (
263 X_ROWID in out NOCOPY VARCHAR2,
264 X_SHEET_NUMBER in NUMBER,
265 X_PERSON_ID in NUMBER,
266 X_COURSE_CD in VARCHAR2,
267 X_UNIT_CD in VARCHAR2,
268 X_CAL_TYPE in VARCHAR2,
269 X_CI_SEQUENCE_NUMBER in NUMBER,
270 X_LOCATION_CD in VARCHAR2,
271 X_UNIT_MODE in VARCHAR2,
272 X_UNIT_CLASS in VARCHAR2,
273 X_STUDENT_SEQUENCE in NUMBER,
274 X_MODE in VARCHAR2 default 'R',
275 X_UOO_ID in NUMBER
276 ) AS
277 cursor C is select ROWID from IGS_AS_MSHT_SU_ATMPT
278 where SHEET_NUMBER = X_SHEET_NUMBER
279 and PERSON_ID = X_PERSON_ID
280 and COURSE_CD = X_COURSE_CD
281 and UOO_ID = X_UOO_ID;
282
283 X_LAST_UPDATE_DATE DATE;
284 X_LAST_UPDATED_BY NUMBER;
285 X_LAST_UPDATE_LOGIN NUMBER;
286 X_REQUEST_ID NUMBER;
287 X_PROGRAM_ID NUMBER;
288 X_PROGRAM_APPLICATION_ID NUMBER;
289 X_PROGRAM_UPDATE_DATE DATE;
290 begin
291 X_LAST_UPDATE_DATE := SYSDATE;
292 if(X_MODE = 'I') then
293 X_LAST_UPDATED_BY := 1;
294 X_LAST_UPDATE_LOGIN := 0;
295 elsif (X_MODE = 'R') then
296 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
297 if X_LAST_UPDATED_BY is NULL then
298 X_LAST_UPDATED_BY := -1;
299 end if;
300 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
301 if X_LAST_UPDATE_LOGIN is NULL then
302 X_LAST_UPDATE_LOGIN := -1;
303 end if;
304 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
305 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
306 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
307 if (X_REQUEST_ID = -1) then
308 X_REQUEST_ID := NULL;
309 X_PROGRAM_ID := NULL;
310 X_PROGRAM_APPLICATION_ID := NULL;
311 X_PROGRAM_UPDATE_DATE := NULL;
312 else
313 X_PROGRAM_UPDATE_DATE := SYSDATE;
314 end if;
315 else
316 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
317 IGS_GE_MSG_STACK.ADD;
318 APP_EXCEPTION.RAISE_EXCEPTION;
319 end if;
320 Before_DML(
321 p_action=>'INSERT',
322 x_rowid=>X_ROWID,
323 x_cal_type=>X_CAL_TYPE,
324 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
325 x_course_cd=>X_COURSE_CD,
326 x_location_cd=>X_LOCATION_CD,
327 x_person_id=>X_PERSON_ID,
328 x_sheet_number=>X_SHEET_NUMBER,
329 x_student_sequence=>X_STUDENT_SEQUENCE,
330 x_unit_cd=>X_UNIT_CD,
331 x_unit_class=>X_UNIT_CLASS,
332 x_unit_mode=>X_UNIT_MODE,
333 x_creation_date=>X_LAST_UPDATE_DATE,
334 x_created_by=>X_LAST_UPDATED_BY,
335 x_last_update_date=>X_LAST_UPDATE_DATE,
336 x_last_updated_by=>X_LAST_UPDATED_BY,
337 x_last_update_login=>X_LAST_UPDATE_LOGIN,
338 x_uoo_id=>X_UOO_ID
339 );
340 insert into IGS_AS_MSHT_SU_ATMPT (
341 SHEET_NUMBER,
342 PERSON_ID,
343 COURSE_CD,
344 UNIT_CD,
345 CAL_TYPE,
346 CI_SEQUENCE_NUMBER,
347 LOCATION_CD,
348 UNIT_MODE,
349 UNIT_CLASS,
350 STUDENT_SEQUENCE,
351 CREATION_DATE,
352 CREATED_BY,
353 LAST_UPDATE_DATE,
354 LAST_UPDATED_BY,
355 LAST_UPDATE_LOGIN,
356 REQUEST_ID,
357 PROGRAM_ID,
358 PROGRAM_APPLICATION_ID,
359 PROGRAM_UPDATE_DATE,
360 UOO_ID
361 ) values (
362 NEW_REFERENCES.SHEET_NUMBER,
363 NEW_REFERENCES.PERSON_ID,
364 NEW_REFERENCES.COURSE_CD,
365 NEW_REFERENCES.UNIT_CD,
366 NEW_REFERENCES.CAL_TYPE,
367 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
368 NEW_REFERENCES.LOCATION_CD,
369 NEW_REFERENCES.UNIT_MODE,
370 NEW_REFERENCES.UNIT_CLASS,
371 NEW_REFERENCES.STUDENT_SEQUENCE,
372 X_LAST_UPDATE_DATE,
373 X_LAST_UPDATED_BY,
374 X_LAST_UPDATE_DATE,
375 X_LAST_UPDATED_BY,
376 X_LAST_UPDATE_LOGIN,
377 X_REQUEST_ID,
378 X_PROGRAM_ID,
379 X_PROGRAM_APPLICATION_ID,
380 X_PROGRAM_UPDATE_DATE,
381 NEW_REFERENCES.UOO_ID
382 );
383 open c;
384 fetch c into X_ROWID;
385 if (c%notfound) then
386 close c;
387 raise no_data_found;
388 end if;
389 close c;
390
391 end INSERT_ROW;
392 procedure LOCK_ROW (
393 X_ROWID in VARCHAR2,
394 X_SHEET_NUMBER in NUMBER,
395 X_PERSON_ID in NUMBER,
396 X_COURSE_CD in VARCHAR2,
397 X_UNIT_CD in VARCHAR2,
398 X_CAL_TYPE in VARCHAR2,
399 X_CI_SEQUENCE_NUMBER in NUMBER,
400 X_LOCATION_CD in VARCHAR2,
401 X_UNIT_MODE in VARCHAR2,
402 X_UNIT_CLASS in VARCHAR2,
403 X_STUDENT_SEQUENCE in NUMBER,
404 X_UOO_ID in NUMBER
405 ) AS
406 cursor c1 is select
407 LOCATION_CD,
408 UNIT_MODE,
409 UNIT_CLASS,
410 STUDENT_SEQUENCE
411 from IGS_AS_MSHT_SU_ATMPT
412 where ROWID = X_ROWID for update nowait;
413 tlinfo c1%rowtype;
414 begin
415 open c1;
416 fetch c1 into tlinfo;
417 if (c1%notfound) then
418 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
419 IGS_GE_MSG_STACK.ADD;
420 APP_EXCEPTION.RAISE_EXCEPTION;
421 close c1;
422 return;
423 end if;
424 close c1;
425 if ( (tlinfo.LOCATION_CD = X_LOCATION_CD)
426 AND (tlinfo.UNIT_MODE = X_UNIT_MODE)
427 AND (tlinfo.UNIT_CLASS = X_UNIT_CLASS)
428 AND (tlinfo.STUDENT_SEQUENCE = X_STUDENT_SEQUENCE)
429 ) then
430 null;
431 else
432 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
433 IGS_GE_MSG_STACK.ADD;
434 APP_EXCEPTION.RAISE_EXCEPTION;
435 end if;
436 return;
437 end LOCK_ROW;
438 procedure UPDATE_ROW (
439 X_ROWID in VARCHAR2,
440 X_SHEET_NUMBER in NUMBER,
441 X_PERSON_ID in NUMBER,
442 X_COURSE_CD in VARCHAR2,
443 X_UNIT_CD in VARCHAR2,
444 X_CAL_TYPE in VARCHAR2,
445 X_CI_SEQUENCE_NUMBER in NUMBER,
446 X_LOCATION_CD in VARCHAR2,
447 X_UNIT_MODE in VARCHAR2,
448 X_UNIT_CLASS in VARCHAR2,
449 X_STUDENT_SEQUENCE in NUMBER,
450 X_MODE in VARCHAR2 default 'R',
451 X_UOO_ID in NUMBER
452 ) AS
453 X_LAST_UPDATE_DATE DATE;
454 X_LAST_UPDATED_BY NUMBER;
455 X_LAST_UPDATE_LOGIN NUMBER;
456 X_REQUEST_ID NUMBER;
457 X_PROGRAM_ID NUMBER;
458 X_PROGRAM_APPLICATION_ID NUMBER;
459 X_PROGRAM_UPDATE_DATE DATE;
460 begin
461 X_LAST_UPDATE_DATE := SYSDATE;
462 if(X_MODE = 'I') then
463 X_LAST_UPDATED_BY := 1;
464 X_LAST_UPDATE_LOGIN := 0;
465 elsif (X_MODE = 'R') then
466 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
467 if X_LAST_UPDATED_BY is NULL then
468 X_LAST_UPDATED_BY := -1;
469 end if;
470 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
471 if X_LAST_UPDATE_LOGIN is NULL then
472 X_LAST_UPDATE_LOGIN := -1;
473 end if;
474 else
475 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
476 IGS_GE_MSG_STACK.ADD;
477 APP_EXCEPTION.RAISE_EXCEPTION;
478 end if;
479 Before_DML(
480 p_action=>'UPDATE',
481 x_rowid=>X_ROWID,
482 x_cal_type=>X_CAL_TYPE,
483 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
484 x_course_cd=>X_COURSE_CD,
485 x_location_cd=>X_LOCATION_CD,
486 x_person_id=>X_PERSON_ID,
487 x_sheet_number=>X_SHEET_NUMBER,
488 x_student_sequence=>X_STUDENT_SEQUENCE,
489 x_unit_cd=>X_UNIT_CD,
490 x_unit_class=>X_UNIT_CLASS,
491 x_unit_mode=>X_UNIT_MODE,
492 x_creation_date=>X_LAST_UPDATE_DATE,
493 x_created_by=>X_LAST_UPDATED_BY,
494 x_last_update_date=>X_LAST_UPDATE_DATE,
495 x_last_updated_by=>X_LAST_UPDATED_BY,
496 x_last_update_login=>X_LAST_UPDATE_LOGIN,
497 x_uoo_id=>X_UOO_ID
498 );
499 if (X_MODE = 'R') then
500 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
501 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
502 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
503 if (X_REQUEST_ID = -1) then
504 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
505 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
506 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
507 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
508 else
509 X_PROGRAM_UPDATE_DATE := SYSDATE;
510 end if;
511 end if;
512 update IGS_AS_MSHT_SU_ATMPT set
513 LOCATION_CD = NEW_REFERENCES.LOCATION_CD,
514 UNIT_MODE = NEW_REFERENCES.UNIT_MODE,
515 UNIT_CLASS = NEW_REFERENCES.UNIT_CLASS,
516 STUDENT_SEQUENCE = NEW_REFERENCES.STUDENT_SEQUENCE,
517 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
518 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
519 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
520 REQUEST_ID = X_REQUEST_ID,
521 PROGRAM_ID = X_PROGRAM_ID,
522 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
523 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
524 where ROWID = X_ROWID;
525 if (sql%notfound) then
526 raise no_data_found;
527 end if;
528
529 end UPDATE_ROW;
530 procedure ADD_ROW (
531 X_ROWID in out NOCOPY VARCHAR2,
532 X_SHEET_NUMBER in NUMBER,
533 X_PERSON_ID in NUMBER,
534 X_COURSE_CD in VARCHAR2,
535 X_UNIT_CD in VARCHAR2,
536 X_CAL_TYPE in VARCHAR2,
537 X_CI_SEQUENCE_NUMBER in NUMBER,
538 X_LOCATION_CD in VARCHAR2,
539 X_UNIT_MODE in VARCHAR2,
540 X_UNIT_CLASS in VARCHAR2,
541 X_STUDENT_SEQUENCE in NUMBER,
542 X_MODE in VARCHAR2 default 'R',
543 X_UOO_ID in NUMBER
544 ) AS
545 cursor c1 is select rowid from IGS_AS_MSHT_SU_ATMPT
546 where SHEET_NUMBER = X_SHEET_NUMBER
547 and PERSON_ID = X_PERSON_ID
548 and COURSE_CD = X_COURSE_CD
549 and UOO_ID = X_UOO_ID ;
550 begin
551 open c1;
552 fetch c1 into X_ROWID;
553 if (c1%notfound) then
554 close c1;
555 INSERT_ROW (
556 X_ROWID,
557 X_SHEET_NUMBER,
558 X_PERSON_ID,
559 X_COURSE_CD,
560 X_UNIT_CD,
561 X_CAL_TYPE,
562 X_CI_SEQUENCE_NUMBER,
563 X_LOCATION_CD,
564 X_UNIT_MODE,
565 X_UNIT_CLASS,
566 X_STUDENT_SEQUENCE,
567 X_MODE,
568 X_UOO_ID);
569 return;
570 end if;
571 close c1;
572 UPDATE_ROW (
573 X_ROWID,
574 X_SHEET_NUMBER,
575 X_PERSON_ID,
576 X_COURSE_CD,
577 X_UNIT_CD,
578 X_CAL_TYPE,
579 X_CI_SEQUENCE_NUMBER,
580 X_LOCATION_CD,
581 X_UNIT_MODE,
582 X_UNIT_CLASS,
583 X_STUDENT_SEQUENCE,
584 X_MODE,
585 X_UOO_ID);
586 end ADD_ROW;
587 procedure DELETE_ROW (
588 X_ROWID in VARCHAR2) AS
589 begin
590 Before_DML(
591 p_action => 'DELETE',
592 x_rowid => X_ROWID
593 );
594 delete from IGS_AS_MSHT_SU_ATMPT
595 where ROWID = X_ROWID;
596 if (sql%notfound) then
597 raise no_data_found;
598 end if;
599
600 end DELETE_ROW;
601 PROCEDURE Check_Constraints (
602 Column_Name IN VARCHAR2 DEFAULT NULL,
603 Column_Value IN VARCHAR2 DEFAULT NULL
604 )
605 AS
606 BEGIN
607 IF column_name is null then
608 NULL;
609 ELSIF upper(Column_name) = 'STUDENT_SEQUENCE' then
610 new_references.STUDENT_SEQUENCE := IGS_GE_NUMBER.TO_NUM(column_value);
611 ELSIF upper(Column_name) = 'CAL_TYPE' then
612 new_references.CAL_TYPE := column_value;
613 ELSIF upper(Column_name) = 'COURSE_CD' then
614 new_references.COURSE_CD := column_value;
615 ELSIF upper(Column_name) = 'UNIT_CD' then
616 new_references.UNIT_CD := column_value;
617 END IF;
618 IF upper(column_name) = 'STUDENT_SEQUENCE' OR
619 column_name is null Then
620 IF new_references.STUDENT_SEQUENCE < 1 OR new_references.STUDENT_SEQUENCE > 999999 Then
621 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
622 IGS_GE_MSG_STACK.ADD;
623 APP_EXCEPTION.RAISE_EXCEPTION;
624 END IF;
625 END IF;
626 IF upper(column_name) = 'CAL_TYPE' OR
627 column_name is null Then
628 IF new_references.CAL_TYPE <> UPPER(new_references.CAL_TYPE) Then
629 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
630 IGS_GE_MSG_STACK.ADD;
631 APP_EXCEPTION.RAISE_EXCEPTION;
632 END IF;
633 END IF;
634 IF upper(column_name) = 'COURSE_CD' OR
635 column_name is null Then
636 IF new_references.COURSE_CD <> UPPER(new_references.COURSE_CD) Then
637 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
638 IGS_GE_MSG_STACK.ADD;
639 APP_EXCEPTION.RAISE_EXCEPTION;
640 END IF;
641 END IF;
642 IF upper(column_name) = 'UNIT_CD' OR
643 column_name is null Then
644 IF new_references.UNIT_CD <> UPPER(new_references.UNIT_CD) Then
645 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
646 IGS_GE_MSG_STACK.ADD;
647 APP_EXCEPTION.RAISE_EXCEPTION;
648 END IF;
649 END IF;
650 END Check_Constraints;
651
652
653 end IGS_AS_MSHT_SU_ATMPT_PKG;