[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_TSTSCR_USED_PKG
Source
1 PACKAGE BODY igs_ad_tstscr_used_pkg AS
2 /* $Header: IGSAIA7B.pls 120.1 2005/10/03 08:23:17 appldev ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ad_tstscr_used%RowType;
5 new_references igs_ad_tstscr_used%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_tstscr_used_id IN NUMBER DEFAULT NULL,
11 x_comments IN VARCHAR2 DEFAULT NULL,
12 x_person_id IN NUMBER DEFAULT NULL,
13 x_admission_appl_number IN NUMBER DEFAULT NULL,
14 x_nominated_course_cd IN VARCHAR2 DEFAULT NULL,
15 x_sequence_number IN NUMBER DEFAULT NULL,
16 x_attribute_category IN VARCHAR2 DEFAULT NULL,
17 x_attribute1 IN VARCHAR2 DEFAULT NULL,
18 x_attribute2 IN VARCHAR2 DEFAULT NULL,
19 x_attribute3 IN VARCHAR2 DEFAULT NULL,
20 x_attribute4 IN VARCHAR2 DEFAULT NULL,
21 x_attribute5 IN VARCHAR2 DEFAULT NULL,
22 x_attribute6 IN VARCHAR2 DEFAULT NULL,
23 x_attribute7 IN VARCHAR2 DEFAULT NULL,
24 x_attribute8 IN VARCHAR2 DEFAULT NULL,
25 x_attribute9 IN VARCHAR2 DEFAULT NULL,
26 x_attribute10 IN VARCHAR2 DEFAULT NULL,
27 x_attribute11 IN VARCHAR2 DEFAULT NULL,
28 x_attribute12 IN VARCHAR2 DEFAULT NULL,
29 x_attribute13 IN VARCHAR2 DEFAULT NULL,
30 x_attribute14 IN VARCHAR2 DEFAULT NULL,
31 x_attribute15 IN VARCHAR2 DEFAULT NULL,
32 x_attribute16 IN VARCHAR2 DEFAULT NULL,
33 x_attribute17 IN VARCHAR2 DEFAULT NULL,
34 x_attribute18 IN VARCHAR2 DEFAULT NULL,
35 x_attribute19 IN VARCHAR2 DEFAULT NULL,
36 x_attribute20 IN VARCHAR2 DEFAULT NULL,
37 x_creation_date IN DATE DEFAULT NULL,
38 x_created_by IN NUMBER DEFAULT NULL,
39 x_last_update_date IN DATE DEFAULT NULL,
40 x_last_updated_by IN NUMBER DEFAULT NULL,
41 x_last_update_login IN NUMBER DEFAULT NULL
42 ) AS
43
44 /*************************************************************
45 Created By :
46 Date Created By :
47 Purpose :
48 Know limitations, enhancements or remarks
49 Change History
50 Who When What
51
52 (reverse chronological order - newest change first)
53 ***************************************************************/
54
55 CURSOR cur_old_ref_values IS
56 SELECT *
57 FROM IGS_AD_TSTSCR_USED
58 WHERE rowid = x_rowid;
59
60 BEGIN
61
62 l_rowid := x_rowid;
63
64 -- Code for setting the Old and New Reference Values.
65 -- Populate Old Values.
66 Open cur_old_ref_values;
67 Fetch cur_old_ref_values INTO old_references;
68 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
69 Close cur_old_ref_values;
70 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
71 IGS_GE_MSG_STACK.ADD;
72 App_Exception.Raise_Exception;
73 Return;
74 END IF;
75 Close cur_old_ref_values;
76
77 -- Populate New Values.
78 new_references.tstscr_used_id := x_tstscr_used_id;
79 new_references.comments := x_comments;
80 new_references.person_id := x_person_id;
81 new_references.admission_appl_number := x_admission_appl_number;
82 new_references.nominated_course_cd := x_nominated_course_cd;
83 new_references.sequence_number := x_sequence_number;
84 new_references.attribute_category := x_attribute_category;
85 new_references.attribute1 := x_attribute1;
86 new_references.attribute2 := x_attribute2;
87 new_references.attribute3 := x_attribute3;
88 new_references.attribute4 := x_attribute4;
89 new_references.attribute5 := x_attribute5;
90 new_references.attribute6 := x_attribute6;
91 new_references.attribute7 := x_attribute7;
92 new_references.attribute8 := x_attribute8;
93 new_references.attribute9 := x_attribute9;
94 new_references.attribute10 := x_attribute10;
95 new_references.attribute11 := x_attribute11;
96 new_references.attribute12 := x_attribute12;
97 new_references.attribute13 := x_attribute13;
98 new_references.attribute14 := x_attribute14;
99 new_references.attribute15 := x_attribute15;
100 new_references.attribute16 := x_attribute16;
101 new_references.attribute17 := x_attribute17;
102 new_references.attribute18 := x_attribute18;
103 new_references.attribute19 := x_attribute19;
104 new_references.attribute20 := x_attribute20;
105 IF (p_action = 'UPDATE') THEN
106 new_references.creation_date := old_references.creation_date;
107 new_references.created_by := old_references.created_by;
108 ELSE
109 new_references.creation_date := x_creation_date;
110 new_references.created_by := x_created_by;
111 END IF;
112 new_references.last_update_date := x_last_update_date;
113 new_references.last_updated_by := x_last_updated_by;
114 new_references.last_update_login := x_last_update_login;
115
116 END Set_Column_Values;
117
118 PROCEDURE Check_Constraints (
119 Column_Name IN VARCHAR2 DEFAULT NULL,
120 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
121 /*************************************************************
122 Created By :
123 Date Created By :
124 Purpose :
125 Know limitations, enhancements or remarks
126 Change History
127 Who When What
128
129 (reverse chronological order - newest change first)
130 ***************************************************************/
131
132 BEGIN
133
134 IF column_name IS NULL THEN
135 NULL;
136 NULL;
137 END IF;
138
139
140
141
142 END Check_Constraints;
143
144 PROCEDURE Check_Parent_Existance AS
145 /*************************************************************
146 Created By :
147 Date Created By :
148 Purpose :
149 Know limitations, enhancements or remarks
150 Change History
151 Who When What
152
153 (reverse chronological order - newest change first)
154 ***************************************************************/
155
156 BEGIN
157
158 IF (((old_references.person_id = new_references.person_id) AND
159 (old_references.admission_appl_number = new_references.admission_appl_number) AND
160 (old_references.nominated_course_cd = new_references.nominated_course_cd) AND
161 (old_references.sequence_number = new_references.sequence_number)) OR
162 ((new_references.person_id IS NULL) OR
163 (new_references.admission_appl_number IS NULL) OR
164 (new_references.nominated_course_cd IS NULL) OR
165 (new_references.sequence_number IS NULL))) THEN
166 NULL;
167 ELSIF NOT Igs_Ad_Ps_Appl_Inst_Pkg.Get_PK_For_Validation (
168 new_references.person_id,
169 new_references.admission_appl_number,
170 new_references.nominated_course_cd,
171 new_references.sequence_number
172 ) THEN
173 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
174 IGS_GE_MSG_STACK.ADD;
175 App_Exception.Raise_Exception;
176 END IF;
177
178 END Check_Parent_Existance;
179
180 FUNCTION Get_PK_For_Validation (
181 x_tstscr_used_id IN NUMBER
182 ) RETURN BOOLEAN AS
183
184 /*************************************************************
185 Created By :
186 Date Created By :
187 Purpose :
188 Know limitations, enhancements or remarks
189 Change History
190 Who When What
191
192 (reverse chronological order - newest change first)
193 ***************************************************************/
194
195 CURSOR cur_rowid IS
196 SELECT rowid
197 FROM igs_ad_tstscr_used
198 WHERE tstscr_used_id = x_tstscr_used_id
199 FOR UPDATE NOWAIT;
200
201 lv_rowid cur_rowid%RowType;
202
203 BEGIN
204
205 Open cur_rowid;
206 Fetch cur_rowid INTO lv_rowid;
207 IF (cur_rowid%FOUND) THEN
208 Close cur_rowid;
209 Return(TRUE);
210 ELSE
211 Close cur_rowid;
212 Return(FALSE);
213 END IF;
214 END Get_PK_For_Validation;
215
216 PROCEDURE Get_FK_Igs_Ad_Ps_Appl_Inst (
217 x_person_id IN NUMBER,
218 x_admission_appl_number IN NUMBER,
219 x_nominated_course_cd IN VARCHAR2,
220 x_sequence_number IN NUMBER
221 ) AS
222
223 /*************************************************************
224 Created By :
225 Date Created By :
226 Purpose :
227 Know limitations, enhancements or remarks
228 Change History
229 Who When What
230
231 (reverse chronological order - newest change first)
232 ***************************************************************/
233
234 CURSOR cur_rowid IS
235 SELECT rowid
236 FROM igs_ad_tstscr_used
237 WHERE person_id = x_person_id
238 AND admission_appl_number = x_admission_appl_number
239 AND nominated_course_cd = x_nominated_course_cd
240 AND sequence_number = x_sequence_number ;
241
242 lv_rowid cur_rowid%RowType;
243
244 BEGIN
245
246 Open cur_rowid;
247 Fetch cur_rowid INTO lv_rowid;
248 IF (cur_rowid%FOUND) THEN
249 Close cur_rowid;
250 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ATV_ACAI_FK');
251 IGS_GE_MSG_STACK.ADD;
252 App_Exception.Raise_Exception;
253 Return;
254 END IF;
255 Close cur_rowid;
256
257 END Get_FK_Igs_Ad_Ps_Appl_Inst;
258
259 PROCEDURE Before_DML (
260 p_action IN VARCHAR2,
261 x_rowid IN VARCHAR2 DEFAULT NULL,
262 x_tstscr_used_id IN NUMBER DEFAULT NULL,
263 x_comments IN VARCHAR2 DEFAULT NULL,
264 x_person_id IN NUMBER DEFAULT NULL,
265 x_admission_appl_number IN NUMBER DEFAULT NULL,
266 x_nominated_course_cd IN VARCHAR2 DEFAULT NULL,
267 x_sequence_number IN NUMBER DEFAULT NULL,
268 x_attribute_category IN VARCHAR2 DEFAULT NULL,
269 x_attribute1 IN VARCHAR2 DEFAULT NULL,
270 x_attribute2 IN VARCHAR2 DEFAULT NULL,
271 x_attribute3 IN VARCHAR2 DEFAULT NULL,
272 x_attribute4 IN VARCHAR2 DEFAULT NULL,
273 x_attribute5 IN VARCHAR2 DEFAULT NULL,
274 x_attribute6 IN VARCHAR2 DEFAULT NULL,
275 x_attribute7 IN VARCHAR2 DEFAULT NULL,
276 x_attribute8 IN VARCHAR2 DEFAULT NULL,
277 x_attribute9 IN VARCHAR2 DEFAULT NULL,
278 x_attribute10 IN VARCHAR2 DEFAULT NULL,
279 x_attribute11 IN VARCHAR2 DEFAULT NULL,
280 x_attribute12 IN VARCHAR2 DEFAULT NULL,
281 x_attribute13 IN VARCHAR2 DEFAULT NULL,
282 x_attribute14 IN VARCHAR2 DEFAULT NULL,
283 x_attribute15 IN VARCHAR2 DEFAULT NULL,
284 x_attribute16 IN VARCHAR2 DEFAULT NULL,
285 x_attribute17 IN VARCHAR2 DEFAULT NULL,
286 x_attribute18 IN VARCHAR2 DEFAULT NULL,
287 x_attribute19 IN VARCHAR2 DEFAULT NULL,
288 x_attribute20 IN VARCHAR2 DEFAULT NULL,
289 x_creation_date IN DATE DEFAULT NULL,
290 x_created_by IN NUMBER DEFAULT NULL,
291 x_last_update_date IN DATE DEFAULT NULL,
292 x_last_updated_by IN NUMBER DEFAULT NULL,
293 x_last_update_login IN NUMBER DEFAULT NULL
294 ) AS
295 /*************************************************************
296 Created By :
297 Date Created By :
298 Purpose :
299 Know limitations, enhancements or remarks
300 Change History
301 Who When What
302
303 (reverse chronological order - newest change first)
304 ***************************************************************/
305
306 BEGIN
307
308 Set_Column_Values (
309 p_action,
310 x_rowid,
311 x_tstscr_used_id,
312 x_comments,
313 x_person_id,
314 x_admission_appl_number,
315 x_nominated_course_cd,
316 x_sequence_number,
317 x_attribute_category,
318 x_attribute1,
319 x_attribute2,
320 x_attribute3,
321 x_attribute4,
322 x_attribute5,
323 x_attribute6,
324 x_attribute7,
325 x_attribute8,
326 x_attribute9,
327 x_attribute10,
328 x_attribute11,
329 x_attribute12,
330 x_attribute13,
331 x_attribute14,
332 x_attribute15,
333 x_attribute16,
334 x_attribute17,
335 x_attribute18,
336 x_attribute19,
337 x_attribute20,
338 x_creation_date,
339 x_created_by,
340 x_last_update_date,
341 x_last_updated_by,
342 x_last_update_login
343 );
344
345 igs_ad_gen_002.check_adm_appl_inst_stat(
346 nvl(x_person_id,old_references.person_id),
347 nvl(x_admission_appl_number,old_references.admission_appl_number),
348 nvl(x_nominated_course_cd,old_references.nominated_course_cd),
349 nvl(x_sequence_number,old_references.sequence_number)
350 );
351
352 IF (p_action = 'INSERT') THEN
353 -- Call all the procedures related to Before Insert.
354 Null;
355 IF Get_Pk_For_Validation(
356 new_references.tstscr_used_id) THEN
357 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
358 IGS_GE_MSG_STACK.ADD;
359 App_Exception.Raise_Exception;
360 END IF;
361 Check_Constraints;
362 Check_Parent_Existance;
363 ELSIF (p_action = 'UPDATE') THEN
364 -- Call all the procedures related to Before Update.
365 Null;
366 Check_Constraints;
367 Check_Parent_Existance;
368 ELSIF (p_action = 'DELETE') THEN
369 -- Call all the procedures related to Before Delete.
370 Null;
371 ELSIF (p_action = 'VALIDATE_INSERT') THEN
372 -- Call all the procedures related to Before Insert.
373 IF Get_PK_For_Validation (
377 App_Exception.Raise_Exception;
374 new_references.tstscr_used_id) THEN
375 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
376 IGS_GE_MSG_STACK.ADD;
378 END IF;
379 Check_Constraints;
380 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
381 Check_Constraints;
382 ELSIF (p_action = 'VALIDATE_DELETE') THEN
383 Null;
384 END IF;
385
386 END Before_DML;
387
388 PROCEDURE After_DML (
389 p_action IN VARCHAR2,
390 x_rowid IN VARCHAR2
391 ) IS
392 /*************************************************************
393 Created By :
394 Date Created By :
395 Purpose :
396 Know limitations, enhancements or remarks
397 Change History
398 Who When What
399
400 (reverse chronological order - newest change first)
401 ***************************************************************/
402
403 BEGIN
404
405 l_rowid := x_rowid;
406
407 IF (p_action = 'INSERT') THEN
408 -- Call all the procedures related to After Insert.
409 Null;
410 ELSIF (p_action = 'UPDATE') THEN
411 -- Call all the procedures related to After Update.
412 Null;
413 ELSIF (p_action = 'DELETE') THEN
414 -- Call all the procedures related to After Delete.
415 Null;
416 END IF;
417
418 END After_DML;
419
420 procedure INSERT_ROW (
421 X_ROWID in out NOCOPY VARCHAR2,
422 x_TSTSCR_USED_ID IN OUT NOCOPY NUMBER,
423 x_COMMENTS IN VARCHAR2,
424 x_PERSON_ID IN NUMBER,
425 x_ADMISSION_APPL_NUMBER IN NUMBER,
426 x_NOMINATED_COURSE_CD IN VARCHAR2,
427 x_SEQUENCE_NUMBER IN NUMBER,
428 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
429 x_ATTRIBUTE1 IN VARCHAR2,
430 x_ATTRIBUTE2 IN VARCHAR2,
431 x_ATTRIBUTE3 IN VARCHAR2,
432 x_ATTRIBUTE4 IN VARCHAR2,
433 x_ATTRIBUTE5 IN VARCHAR2,
434 x_ATTRIBUTE6 IN VARCHAR2,
435 x_ATTRIBUTE7 IN VARCHAR2,
436 x_ATTRIBUTE8 IN VARCHAR2,
437 x_ATTRIBUTE9 IN VARCHAR2,
438 x_ATTRIBUTE10 IN VARCHAR2,
439 x_ATTRIBUTE11 IN VARCHAR2,
440 x_ATTRIBUTE12 IN VARCHAR2,
441 x_ATTRIBUTE13 IN VARCHAR2,
442 x_ATTRIBUTE14 IN VARCHAR2,
443 x_ATTRIBUTE15 IN VARCHAR2,
444 x_ATTRIBUTE16 IN VARCHAR2,
445 x_ATTRIBUTE17 IN VARCHAR2,
446 x_ATTRIBUTE18 IN VARCHAR2,
447 x_ATTRIBUTE19 IN VARCHAR2,
448 x_ATTRIBUTE20 IN VARCHAR2,
449 X_MODE in VARCHAR2 default 'R'
450 ) AS
451 /*************************************************************
452 Created By :
453 Date Created By :
454 Purpose :
455 Know limitations, enhancements or remarks
456 Change History
457 Who When What
458
459 (reverse chronological order - newest change first)
460 ***************************************************************/
461
462 cursor C is select ROWID from IGS_AD_TSTSCR_USED
463 where TSTSCR_USED_ID= X_TSTSCR_USED_ID
464 ;
465 X_LAST_UPDATE_DATE DATE ;
466 X_LAST_UPDATED_BY NUMBER ;
467 X_LAST_UPDATE_LOGIN NUMBER ;
468 begin
469 X_LAST_UPDATE_DATE := SYSDATE;
470 if(X_MODE = 'I') then
471 X_LAST_UPDATED_BY := 1;
472 X_LAST_UPDATE_LOGIN := 0;
473 elsif (X_MODE = 'R') then
474 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
475 if X_LAST_UPDATED_BY is NULL then
476 X_LAST_UPDATED_BY := -1;
477 end if;
478 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
479 if X_LAST_UPDATE_LOGIN is NULL then
480 X_LAST_UPDATE_LOGIN := -1;
481 end if;
482 else
483 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
484 IGS_GE_MSG_STACK.ADD;
485 app_exception.raise_exception;
486 end if;
487
488 X_TSTSCR_USED_ID := -1;
489 Before_DML(
490 p_action=>'INSERT',
491 x_rowid=>X_ROWID,
492 x_tstscr_used_id=>X_TSTSCR_USED_ID,
493 x_comments=>X_COMMENTS,
494 x_person_id=>X_PERSON_ID,
495 x_admission_appl_number=>X_ADMISSION_APPL_NUMBER,
496 x_nominated_course_cd=>X_NOMINATED_COURSE_CD,
497 x_sequence_number=>X_SEQUENCE_NUMBER,
498 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
499 x_attribute1=>X_ATTRIBUTE1,
500 x_attribute2=>X_ATTRIBUTE2,
501 x_attribute3=>X_ATTRIBUTE3,
502 x_attribute4=>X_ATTRIBUTE4,
503 x_attribute5=>X_ATTRIBUTE5,
504 x_attribute6=>X_ATTRIBUTE6,
505 x_attribute7=>X_ATTRIBUTE7,
506 x_attribute8=>X_ATTRIBUTE8,
507 x_attribute9=>X_ATTRIBUTE9,
508 x_attribute10=>X_ATTRIBUTE10,
509 x_attribute11=>X_ATTRIBUTE11,
510 x_attribute12=>X_ATTRIBUTE12,
511 x_attribute13=>X_ATTRIBUTE13,
512 x_attribute14=>X_ATTRIBUTE14,
513 x_attribute15=>X_ATTRIBUTE15,
514 x_attribute16=>X_ATTRIBUTE16,
515 x_attribute17=>X_ATTRIBUTE17,
519 x_creation_date=>X_LAST_UPDATE_DATE,
516 x_attribute18=>X_ATTRIBUTE18,
517 x_attribute19=>X_ATTRIBUTE19,
518 x_attribute20=>X_ATTRIBUTE20,
520 x_created_by=>X_LAST_UPDATED_BY,
521 x_last_update_date=>X_LAST_UPDATE_DATE,
522 x_last_updated_by=>X_LAST_UPDATED_BY,
523 x_last_update_login=>X_LAST_UPDATE_LOGIN);
524 insert into IGS_AD_TSTSCR_USED (
525 TSTSCR_USED_ID
526 ,COMMENTS
527 ,PERSON_ID
528 ,ADMISSION_APPL_NUMBER
529 ,NOMINATED_COURSE_CD
530 ,SEQUENCE_NUMBER
531 ,ATTRIBUTE_CATEGORY
532 ,ATTRIBUTE1
533 ,ATTRIBUTE2
534 ,ATTRIBUTE3
535 ,ATTRIBUTE4
536 ,ATTRIBUTE5
537 ,ATTRIBUTE6
538 ,ATTRIBUTE7
539 ,ATTRIBUTE8
540 ,ATTRIBUTE9
541 ,ATTRIBUTE10
542 ,ATTRIBUTE11
543 ,ATTRIBUTE12
544 ,ATTRIBUTE13
545 ,ATTRIBUTE14
546 ,ATTRIBUTE15
547 ,ATTRIBUTE16
548 ,ATTRIBUTE17
549 ,ATTRIBUTE18
550 ,ATTRIBUTE19
551 ,ATTRIBUTE20
552 ,CREATION_DATE
553 ,CREATED_BY
554 ,LAST_UPDATE_DATE
555 ,LAST_UPDATED_BY
556 ,LAST_UPDATE_LOGIN
557 ) values (
558 IGS_AD_TSTSCR_USED_S.NEXTVAL
559 ,NEW_REFERENCES.COMMENTS
560 ,NEW_REFERENCES.PERSON_ID
561 ,NEW_REFERENCES.ADMISSION_APPL_NUMBER
562 ,NEW_REFERENCES.NOMINATED_COURSE_CD
563 ,NEW_REFERENCES.SEQUENCE_NUMBER
564 ,NEW_REFERENCES.ATTRIBUTE_CATEGORY
565 ,NEW_REFERENCES.ATTRIBUTE1
566 ,NEW_REFERENCES.ATTRIBUTE2
567 ,NEW_REFERENCES.ATTRIBUTE3
568 ,NEW_REFERENCES.ATTRIBUTE4
569 ,NEW_REFERENCES.ATTRIBUTE5
570 ,NEW_REFERENCES.ATTRIBUTE6
571 ,NEW_REFERENCES.ATTRIBUTE7
572 ,NEW_REFERENCES.ATTRIBUTE8
573 ,NEW_REFERENCES.ATTRIBUTE9
574 ,NEW_REFERENCES.ATTRIBUTE10
575 ,NEW_REFERENCES.ATTRIBUTE11
576 ,NEW_REFERENCES.ATTRIBUTE12
577 ,NEW_REFERENCES.ATTRIBUTE13
578 ,NEW_REFERENCES.ATTRIBUTE14
579 ,NEW_REFERENCES.ATTRIBUTE15
580 ,NEW_REFERENCES.ATTRIBUTE16
581 ,NEW_REFERENCES.ATTRIBUTE17
582 ,NEW_REFERENCES.ATTRIBUTE18
583 ,NEW_REFERENCES.ATTRIBUTE19
584 ,NEW_REFERENCES.ATTRIBUTE20
585 ,X_LAST_UPDATE_DATE
586 ,X_LAST_UPDATED_BY
587 ,X_LAST_UPDATE_DATE
588 ,X_LAST_UPDATED_BY
589 ,X_LAST_UPDATE_LOGIN
590 )RETURNING TSTSCR_USED_ID INTO X_TSTSCR_USED_ID;
591 open c;
592 fetch c into X_ROWID;
593 if (c%notfound) then
594 close c;
595 raise no_data_found;
596 end if;
597 close c;
598 After_DML (
599 p_action => 'INSERT' ,
600 x_rowid => X_ROWID );
601 end INSERT_ROW;
602 procedure LOCK_ROW (
603 X_ROWID in VARCHAR2,
604 x_TSTSCR_USED_ID IN NUMBER,
605 x_COMMENTS IN VARCHAR2,
606 x_PERSON_ID IN NUMBER,
607 x_ADMISSION_APPL_NUMBER IN NUMBER,
608 x_NOMINATED_COURSE_CD IN VARCHAR2,
609 x_SEQUENCE_NUMBER IN NUMBER,
610 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
611 x_ATTRIBUTE1 IN VARCHAR2,
612 x_ATTRIBUTE2 IN VARCHAR2,
613 x_ATTRIBUTE3 IN VARCHAR2,
614 x_ATTRIBUTE4 IN VARCHAR2,
615 x_ATTRIBUTE5 IN VARCHAR2,
616 x_ATTRIBUTE6 IN VARCHAR2,
617 x_ATTRIBUTE7 IN VARCHAR2,
618 x_ATTRIBUTE8 IN VARCHAR2,
619 x_ATTRIBUTE9 IN VARCHAR2,
620 x_ATTRIBUTE10 IN VARCHAR2,
621 x_ATTRIBUTE11 IN VARCHAR2,
622 x_ATTRIBUTE12 IN VARCHAR2,
623 x_ATTRIBUTE13 IN VARCHAR2,
624 x_ATTRIBUTE14 IN VARCHAR2,
625 x_ATTRIBUTE15 IN VARCHAR2,
626 x_ATTRIBUTE16 IN VARCHAR2,
627 x_ATTRIBUTE17 IN VARCHAR2,
628 x_ATTRIBUTE18 IN VARCHAR2,
629 x_ATTRIBUTE19 IN VARCHAR2,
630 x_ATTRIBUTE20 IN VARCHAR2 ) AS
631 /*************************************************************
632 Created By :
633 Date Created By :
634 Purpose :
635 Know limitations, enhancements or remarks
636 Change History
637 Who When What
638
639 (reverse chronological order - newest change first)
640 ***************************************************************/
641
642 cursor c1 is select
643 COMMENTS
644 , PERSON_ID
645 , ADMISSION_APPL_NUMBER
646 , NOMINATED_COURSE_CD
647 , SEQUENCE_NUMBER
648 , ATTRIBUTE_CATEGORY
649 , ATTRIBUTE1
650 , ATTRIBUTE2
651 , ATTRIBUTE3
652 , ATTRIBUTE4
653 , ATTRIBUTE5
654 , ATTRIBUTE6
655 , ATTRIBUTE7
656 , ATTRIBUTE8
657 , ATTRIBUTE9
658 , ATTRIBUTE10
659 , ATTRIBUTE11
660 , ATTRIBUTE12
661 , ATTRIBUTE13
662 , ATTRIBUTE14
663 , ATTRIBUTE15
664 , ATTRIBUTE16
665 , ATTRIBUTE17
666 , ATTRIBUTE18
667 , ATTRIBUTE19
668 , ATTRIBUTE20
669 from IGS_AD_TSTSCR_USED
670 where ROWID = X_ROWID
671 for update nowait;
672 tlinfo c1%rowtype;
673 begin
674 open c1;
675 fetch c1 into tlinfo;
679 close c1;
676 if (c1%notfound) then
677 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
678 IGS_GE_MSG_STACK.ADD;
680 app_exception.raise_exception;
681 return;
682 end if;
683 close c1;
684 if ( ( tlinfo.COMMENTS = X_COMMENTS)
685 AND (tlinfo.PERSON_ID = X_PERSON_ID)
686 AND (tlinfo.ADMISSION_APPL_NUMBER = X_ADMISSION_APPL_NUMBER)
687 AND (tlinfo.NOMINATED_COURSE_CD = X_NOMINATED_COURSE_CD)
688 AND (tlinfo.SEQUENCE_NUMBER = X_SEQUENCE_NUMBER)
689 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
690 OR ((tlinfo.ATTRIBUTE_CATEGORY is null)
691 AND (X_ATTRIBUTE_CATEGORY is null)))
692 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
693 OR ((tlinfo.ATTRIBUTE1 is null)
694 AND (X_ATTRIBUTE1 is null)))
695 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
696 OR ((tlinfo.ATTRIBUTE2 is null)
697 AND (X_ATTRIBUTE2 is null)))
698 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
699 OR ((tlinfo.ATTRIBUTE3 is null)
700 AND (X_ATTRIBUTE3 is null)))
701 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
702 OR ((tlinfo.ATTRIBUTE4 is null)
703 AND (X_ATTRIBUTE4 is null)))
704 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
705 OR ((tlinfo.ATTRIBUTE5 is null)
706 AND (X_ATTRIBUTE5 is null)))
707 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
708 OR ((tlinfo.ATTRIBUTE6 is null)
709 AND (X_ATTRIBUTE6 is null)))
710 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
711 OR ((tlinfo.ATTRIBUTE7 is null)
712 AND (X_ATTRIBUTE7 is null)))
713 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
714 OR ((tlinfo.ATTRIBUTE8 is null)
715 AND (X_ATTRIBUTE8 is null)))
716 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
717 OR ((tlinfo.ATTRIBUTE9 is null)
718 AND (X_ATTRIBUTE9 is null)))
719 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
720 OR ((tlinfo.ATTRIBUTE10 is null)
721 AND (X_ATTRIBUTE10 is null)))
722 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
723 OR ((tlinfo.ATTRIBUTE11 is null)
724 AND (X_ATTRIBUTE11 is null)))
725 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
726 OR ((tlinfo.ATTRIBUTE12 is null)
727 AND (X_ATTRIBUTE12 is null)))
728 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
729 OR ((tlinfo.ATTRIBUTE13 is null)
730 AND (X_ATTRIBUTE13 is null)))
731 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
732 OR ((tlinfo.ATTRIBUTE14 is null)
733 AND (X_ATTRIBUTE14 is null)))
734 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
735 OR ((tlinfo.ATTRIBUTE15 is null)
736 AND (X_ATTRIBUTE15 is null)))
737 AND ((tlinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
738 OR ((tlinfo.ATTRIBUTE16 is null)
739 AND (X_ATTRIBUTE16 is null)))
740 AND ((tlinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
741 OR ((tlinfo.ATTRIBUTE17 is null)
742 AND (X_ATTRIBUTE17 is null)))
743 AND ((tlinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
744 OR ((tlinfo.ATTRIBUTE18 is null)
745 AND (X_ATTRIBUTE18 is null)))
746 AND ((tlinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
747 OR ((tlinfo.ATTRIBUTE19 is null)
748 AND (X_ATTRIBUTE19 is null)))
749 AND ((tlinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
750 OR ((tlinfo.ATTRIBUTE20 is null)
751 AND (X_ATTRIBUTE20 is null)))
752 ) then
753 null;
754 else
755 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
756 IGS_GE_MSG_STACK.ADD;
757 app_exception.raise_exception;
758 end if;
759 return;
760 end LOCK_ROW;
761 Procedure UPDATE_ROW (
762 X_ROWID in VARCHAR2,
763 x_TSTSCR_USED_ID IN NUMBER,
764 x_COMMENTS IN VARCHAR2,
765 x_PERSON_ID IN NUMBER,
766 x_ADMISSION_APPL_NUMBER IN NUMBER,
767 x_NOMINATED_COURSE_CD IN VARCHAR2,
768 x_SEQUENCE_NUMBER IN NUMBER,
769 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
770 x_ATTRIBUTE1 IN VARCHAR2,
771 x_ATTRIBUTE2 IN VARCHAR2,
772 x_ATTRIBUTE3 IN VARCHAR2,
773 x_ATTRIBUTE4 IN VARCHAR2,
774 x_ATTRIBUTE5 IN VARCHAR2,
775 x_ATTRIBUTE6 IN VARCHAR2,
776 x_ATTRIBUTE7 IN VARCHAR2,
777 x_ATTRIBUTE8 IN VARCHAR2,
778 x_ATTRIBUTE9 IN VARCHAR2,
779 x_ATTRIBUTE10 IN VARCHAR2,
780 x_ATTRIBUTE11 IN VARCHAR2,
781 x_ATTRIBUTE12 IN VARCHAR2,
782 x_ATTRIBUTE13 IN VARCHAR2,
783 x_ATTRIBUTE14 IN VARCHAR2,
784 x_ATTRIBUTE15 IN VARCHAR2,
785 x_ATTRIBUTE16 IN VARCHAR2,
786 x_ATTRIBUTE17 IN VARCHAR2,
787 x_ATTRIBUTE18 IN VARCHAR2,
788 x_ATTRIBUTE19 IN VARCHAR2,
789 x_ATTRIBUTE20 IN VARCHAR2,
790 X_MODE in VARCHAR2 default 'R'
791 ) AS
792 /*************************************************************
793 Created By :
794 Date Created By :
795 Purpose :
796 Know limitations, enhancements or remarks
797 Change History
798 Who When What
799
800 (reverse chronological order - newest change first)
801 ***************************************************************/
802
803 X_LAST_UPDATE_DATE DATE ;
804 X_LAST_UPDATED_BY NUMBER ;
805 X_LAST_UPDATE_LOGIN NUMBER ;
806 begin
807 X_LAST_UPDATE_DATE := SYSDATE;
808 if(X_MODE = 'I') then
809 X_LAST_UPDATED_BY := 1;
813 if X_LAST_UPDATED_BY is NULL then
810 X_LAST_UPDATE_LOGIN := 0;
811 elsif (X_MODE = 'R') then
812 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
814 X_LAST_UPDATED_BY := -1;
815 end if;
816 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
817 if X_LAST_UPDATE_LOGIN is NULL then
818 X_LAST_UPDATE_LOGIN := -1;
819 end if;
820 else
821 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
822 IGS_GE_MSG_STACK.ADD;
823 app_exception.raise_exception;
824 end if;
825 Before_DML(
826 p_action=>'UPDATE',
827 x_rowid=>X_ROWID,
828 x_tstscr_used_id=>X_TSTSCR_USED_ID,
829 x_comments=>X_COMMENTS,
830 x_person_id=>X_PERSON_ID,
831 x_admission_appl_number=>X_ADMISSION_APPL_NUMBER,
832 x_nominated_course_cd=>X_NOMINATED_COURSE_CD,
833 x_sequence_number=>X_SEQUENCE_NUMBER,
834 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
835 x_attribute1=>X_ATTRIBUTE1,
836 x_attribute2=>X_ATTRIBUTE2,
837 x_attribute3=>X_ATTRIBUTE3,
838 x_attribute4=>X_ATTRIBUTE4,
839 x_attribute5=>X_ATTRIBUTE5,
840 x_attribute6=>X_ATTRIBUTE6,
841 x_attribute7=>X_ATTRIBUTE7,
842 x_attribute8=>X_ATTRIBUTE8,
843 x_attribute9=>X_ATTRIBUTE9,
844 x_attribute10=>X_ATTRIBUTE10,
845 x_attribute11=>X_ATTRIBUTE11,
846 x_attribute12=>X_ATTRIBUTE12,
847 x_attribute13=>X_ATTRIBUTE13,
848 x_attribute14=>X_ATTRIBUTE14,
849 x_attribute15=>X_ATTRIBUTE15,
850 x_attribute16=>X_ATTRIBUTE16,
851 x_attribute17=>X_ATTRIBUTE17,
852 x_attribute18=>X_ATTRIBUTE18,
853 x_attribute19=>X_ATTRIBUTE19,
854 x_attribute20=>X_ATTRIBUTE20,
855 x_creation_date=>X_LAST_UPDATE_DATE,
856 x_created_by=>X_LAST_UPDATED_BY,
857 x_last_update_date=>X_LAST_UPDATE_DATE,
858 x_last_updated_by=>X_LAST_UPDATED_BY,
859 x_last_update_login=>X_LAST_UPDATE_LOGIN);
860 update IGS_AD_TSTSCR_USED set
861 COMMENTS = NEW_REFERENCES.COMMENTS,
862 PERSON_ID = NEW_REFERENCES.PERSON_ID,
863 ADMISSION_APPL_NUMBER = NEW_REFERENCES.ADMISSION_APPL_NUMBER,
864 NOMINATED_COURSE_CD = NEW_REFERENCES.NOMINATED_COURSE_CD,
865 SEQUENCE_NUMBER = NEW_REFERENCES.SEQUENCE_NUMBER,
866 ATTRIBUTE_CATEGORY = NEW_REFERENCES.ATTRIBUTE_CATEGORY,
867 ATTRIBUTE1 = NEW_REFERENCES.ATTRIBUTE1,
868 ATTRIBUTE2 = NEW_REFERENCES.ATTRIBUTE2,
869 ATTRIBUTE3 = NEW_REFERENCES.ATTRIBUTE3,
870 ATTRIBUTE4 = NEW_REFERENCES.ATTRIBUTE4,
871 ATTRIBUTE5 = NEW_REFERENCES.ATTRIBUTE5,
872 ATTRIBUTE6 = NEW_REFERENCES.ATTRIBUTE6,
873 ATTRIBUTE7 = NEW_REFERENCES.ATTRIBUTE7,
874 ATTRIBUTE8 = NEW_REFERENCES.ATTRIBUTE8,
875 ATTRIBUTE9 = NEW_REFERENCES.ATTRIBUTE9,
876 ATTRIBUTE10 = NEW_REFERENCES.ATTRIBUTE10,
877 ATTRIBUTE11 = NEW_REFERENCES.ATTRIBUTE11,
878 ATTRIBUTE12 = NEW_REFERENCES.ATTRIBUTE12,
879 ATTRIBUTE13 = NEW_REFERENCES.ATTRIBUTE13,
880 ATTRIBUTE14 = NEW_REFERENCES.ATTRIBUTE14,
881 ATTRIBUTE15 = NEW_REFERENCES.ATTRIBUTE15,
882 ATTRIBUTE16 = NEW_REFERENCES.ATTRIBUTE16,
883 ATTRIBUTE17 = NEW_REFERENCES.ATTRIBUTE17,
884 ATTRIBUTE18 = NEW_REFERENCES.ATTRIBUTE18,
885 ATTRIBUTE19 = NEW_REFERENCES.ATTRIBUTE19,
886 ATTRIBUTE20 = NEW_REFERENCES.ATTRIBUTE20,
887 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
888 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
889 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
890 where ROWID = X_ROWID;
891 if (sql%notfound) then
892 raise no_data_found;
893 end if;
894
895 After_DML (
896 p_action => 'UPDATE' ,
897 x_rowid => X_ROWID
898 );
899 end UPDATE_ROW;
900 procedure ADD_ROW (
901 X_ROWID in out NOCOPY VARCHAR2,
902 x_TSTSCR_USED_ID IN OUT NOCOPY NUMBER,
903 x_COMMENTS IN VARCHAR2,
904 x_PERSON_ID IN NUMBER,
905 x_ADMISSION_APPL_NUMBER IN NUMBER,
906 x_NOMINATED_COURSE_CD IN VARCHAR2,
907 x_SEQUENCE_NUMBER IN NUMBER,
908 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
909 x_ATTRIBUTE1 IN VARCHAR2,
910 x_ATTRIBUTE2 IN VARCHAR2,
911 x_ATTRIBUTE3 IN VARCHAR2,
912 x_ATTRIBUTE4 IN VARCHAR2,
913 x_ATTRIBUTE5 IN VARCHAR2,
914 x_ATTRIBUTE6 IN VARCHAR2,
915 x_ATTRIBUTE7 IN VARCHAR2,
916 x_ATTRIBUTE8 IN VARCHAR2,
917 x_ATTRIBUTE9 IN VARCHAR2,
918 x_ATTRIBUTE10 IN VARCHAR2,
919 x_ATTRIBUTE11 IN VARCHAR2,
920 x_ATTRIBUTE12 IN VARCHAR2,
921 x_ATTRIBUTE13 IN VARCHAR2,
922 x_ATTRIBUTE14 IN VARCHAR2,
923 x_ATTRIBUTE15 IN VARCHAR2,
924 x_ATTRIBUTE16 IN VARCHAR2,
925 x_ATTRIBUTE17 IN VARCHAR2,
926 x_ATTRIBUTE18 IN VARCHAR2,
927 x_ATTRIBUTE19 IN VARCHAR2,
928 x_ATTRIBUTE20 IN VARCHAR2,
929 X_MODE in VARCHAR2 default 'R'
930 ) AS
931 /*************************************************************
932 Created By :
933 Date Created By :
934 Purpose :
935 Know limitations, enhancements or remarks
936 Change History
937 Who When What
938
939 (reverse chronological order - newest change first)
940 ***************************************************************/
941
942 cursor c1 is select ROWID from IGS_AD_TSTSCR_USED
943 where TSTSCR_USED_ID= X_TSTSCR_USED_ID
944 ;
945 begin
946 open c1;
947 fetch c1 into X_ROWID;
948 if (c1%notfound) then
949 close c1;
950 INSERT_ROW (
951 X_ROWID,
952 X_TSTSCR_USED_ID,
953 X_COMMENTS,
954 X_PERSON_ID,
955 X_ADMISSION_APPL_NUMBER,
956 X_NOMINATED_COURSE_CD,
957 X_SEQUENCE_NUMBER,
958 X_ATTRIBUTE_CATEGORY,
959 X_ATTRIBUTE1,
960 X_ATTRIBUTE2,
961 X_ATTRIBUTE3,
962 X_ATTRIBUTE4,
963 X_ATTRIBUTE5,
964 X_ATTRIBUTE6,
965 X_ATTRIBUTE7,
966 X_ATTRIBUTE8,
967 X_ATTRIBUTE9,
968 X_ATTRIBUTE10,
969 X_ATTRIBUTE11,
970 X_ATTRIBUTE12,
971 X_ATTRIBUTE13,
972 X_ATTRIBUTE14,
973 X_ATTRIBUTE15,
974 X_ATTRIBUTE16,
975 X_ATTRIBUTE17,
976 X_ATTRIBUTE18,
977 X_ATTRIBUTE19,
978 X_ATTRIBUTE20,
979 X_MODE );
980 return;
981 end if;
982 close c1;
983 UPDATE_ROW (
984 X_ROWID,
985 X_TSTSCR_USED_ID,
986 X_COMMENTS,
987 X_PERSON_ID,
988 X_ADMISSION_APPL_NUMBER,
989 X_NOMINATED_COURSE_CD,
990 X_SEQUENCE_NUMBER,
991 X_ATTRIBUTE_CATEGORY,
992 X_ATTRIBUTE1,
993 X_ATTRIBUTE2,
994 X_ATTRIBUTE3,
995 X_ATTRIBUTE4,
996 X_ATTRIBUTE5,
997 X_ATTRIBUTE6,
998 X_ATTRIBUTE7,
999 X_ATTRIBUTE8,
1000 X_ATTRIBUTE9,
1001 X_ATTRIBUTE10,
1002 X_ATTRIBUTE11,
1003 X_ATTRIBUTE12,
1004 X_ATTRIBUTE13,
1005 X_ATTRIBUTE14,
1006 X_ATTRIBUTE15,
1007 X_ATTRIBUTE16,
1008 X_ATTRIBUTE17,
1009 X_ATTRIBUTE18,
1010 X_ATTRIBUTE19,
1011 X_ATTRIBUTE20,
1012 X_MODE );
1013 end ADD_ROW;
1014 procedure DELETE_ROW (
1015 X_ROWID in VARCHAR2
1016 ) AS
1017 /*************************************************************
1018 Created By :
1019 Date Created By :
1020 Purpose :
1021 Know limitations, enhancements or remarks
1022 Change History
1023 Who When What
1024
1025 (reverse chronological order - newest change first)
1026 ***************************************************************/
1027
1028 begin
1029 Before_DML (
1030 p_action => 'DELETE',
1031 x_rowid => X_ROWID
1032 );
1033 delete from IGS_AD_TSTSCR_USED
1034 where ROWID = X_ROWID;
1035 if (sql%notfound) then
1036 raise no_data_found;
1037 end if;
1038 After_DML (
1039 p_action => 'DELETE',
1040 x_rowid => X_ROWID
1041 );
1042 end DELETE_ROW;
1043
1044 END igs_ad_tstscr_used_pkg;