[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_TEST_RESULTS_PKG
Source
1 PACKAGE BODY igs_ad_test_results_pkg AS
2 /* $Header: IGSAI79B.pls 120.3 2005/08/05 05:55:13 appldev ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ad_test_results%RowType;
5 new_references igs_ad_test_results%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_test_results_id IN NUMBER DEFAULT NULL,
11 x_person_id IN NUMBER DEFAULT NULL,
12 x_admission_test_type IN VARCHAR2 DEFAULT NULL,
13 x_test_date IN DATE DEFAULT NULL,
14 x_score_report_date IN DATE DEFAULT NULL,
15 x_edu_level_id IN NUMBER DEFAULT NULL,
16 x_score_type IN VARCHAR2 DEFAULT NULL,
17 x_score_source_id IN NUMBER DEFAULT NULL,
18 x_non_standard_admin IN VARCHAR2 DEFAULT NULL,
19 x_comp_test_score IN NUMBER DEFAULT NULL,
20 x_special_code IN VARCHAR2 DEFAULT NULL,
21 x_registration_number IN VARCHAR2 DEFAULT NULL,
22 x_grade_id IN NUMBER DEFAULT NULL,
23 x_attribute_category IN VARCHAR2 DEFAULT NULL,
24 x_attribute1 IN VARCHAR2 DEFAULT NULL,
25 x_attribute2 IN VARCHAR2 DEFAULT NULL,
26 x_attribute3 IN VARCHAR2 DEFAULT NULL,
27 x_attribute4 IN VARCHAR2 DEFAULT NULL,
28 x_attribute5 IN VARCHAR2 DEFAULT NULL,
29 x_attribute6 IN VARCHAR2 DEFAULT NULL,
30 x_attribute7 IN VARCHAR2 DEFAULT NULL,
31 x_attribute8 IN VARCHAR2 DEFAULT NULL,
32 x_attribute9 IN VARCHAR2 DEFAULT NULL,
33 x_attribute10 IN VARCHAR2 DEFAULT NULL,
34 x_attribute11 IN VARCHAR2 DEFAULT NULL,
35 x_attribute12 IN VARCHAR2 DEFAULT NULL,
36 x_attribute13 IN VARCHAR2 DEFAULT NULL,
37 x_attribute14 IN VARCHAR2 DEFAULT NULL,
38 x_attribute15 IN VARCHAR2 DEFAULT NULL,
39 x_attribute16 IN VARCHAR2 DEFAULT NULL,
40 x_attribute17 IN VARCHAR2 DEFAULT NULL,
41 x_attribute18 IN VARCHAR2 DEFAULT NULL,
42 x_attribute19 IN VARCHAR2 DEFAULT NULL,
43 x_attribute20 IN VARCHAR2 DEFAULT NULL,
44 x_creation_date IN DATE DEFAULT NULL,
45 x_created_by IN NUMBER DEFAULT NULL,
46 x_last_update_date IN DATE DEFAULT NULL,
47 x_last_updated_by IN NUMBER DEFAULT NULL,
48 x_last_update_login IN NUMBER DEFAULT NULL ,
49 x_active_ind IN VARCHAR2 DEFAULT NULL
50
51 ) AS
52
53 /*************************************************************
54 Created By :samaresh.in
55 Date Created By : 15-May-2000
56 Purpose :
57 Know limitations, enhancements or remarks
58 Change History
59 Who When What
60
61 (reverse chronological order - newest change first)
62 ***************************************************************/
63
64 CURSOR cur_old_ref_values IS
65 SELECT *
66 FROM IGS_AD_TEST_RESULTS
67 WHERE rowid = x_rowid;
68
69 CURSOR score_type_cur(cp_admission_test_type VARCHAR2) IS
70 SELECT score_type
71 FROM igs_ad_test_type
72 WHERE admission_test_type = cp_admission_test_type;
73
74 l_score_type igs_ad_test_type.score_type%TYPE;
75 BEGIN
76
77 l_rowid := x_rowid;
78
79 -- Code for setting the Old and New Reference Values.
80 -- Populate Old Values.
81 Open cur_old_ref_values;
82 Fetch cur_old_ref_values INTO old_references;
83 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
84 Close cur_old_ref_values;
85 Fnd_Message.Set_Name ('IGS', 'FORM_RECORD_DELETED');
86 IGS_GE_MSG_STACK.ADD;
87 App_Exception.Raise_Exception;
88 Return;
89 END IF;
90 Close cur_old_ref_values;
91
92 -- Populate New Values.
93 new_references.test_results_id := x_test_results_id;
94 new_references.person_id := x_person_id;
95 new_references.admission_test_type := x_admission_test_type;
96 new_references.test_date := TRUNC(x_test_date);
97 new_references.score_report_date := TRUNC(x_score_report_date);
98 new_references.edu_level_id := x_edu_level_id;
99
100 IF x_score_type IS NOT NULL THEN
101 new_references.score_type := x_score_type;
102 ELSE
103 OPEN score_type_cur(x_admission_test_type);
104 FETCH score_type_cur INTO l_score_type;
105 CLOSE score_type_cur;
106
107 new_references.score_type := l_score_type;
108 END IF;
109
110 new_references.score_source_id := x_score_source_id;
111 new_references.non_standard_admin := x_non_standard_admin;
112 new_references.comp_test_score := x_comp_test_score;
113 new_references.special_code := x_special_code;
114 new_references.registration_number := x_registration_number;
115 new_references.grade_id := x_grade_id;
116 new_references.attribute_category := x_attribute_category;
117 new_references.attribute1 := x_attribute1;
118 new_references.attribute2 := x_attribute2;
119 new_references.attribute3 := x_attribute3;
120 new_references.attribute4 := x_attribute4;
121 new_references.attribute5 := x_attribute5;
122 new_references.attribute6 := x_attribute6;
123 new_references.attribute7 := x_attribute7;
124 new_references.attribute8 := x_attribute8;
125 new_references.attribute9 := x_attribute9;
126 new_references.attribute10 := x_attribute10;
127 new_references.attribute11 := x_attribute11;
128 new_references.attribute12 := x_attribute12;
129 new_references.attribute13 := x_attribute13;
130 new_references.attribute14 := x_attribute14;
131 new_references.attribute15 := x_attribute15;
132 new_references.attribute16 := x_attribute16;
133 new_references.attribute17 := x_attribute17;
134 new_references.attribute18 := x_attribute18;
135 new_references.attribute19 := x_attribute19;
136 new_references.attribute20 := x_attribute20;
137 new_references.active_ind := x_active_ind ;
138 IF (p_action = 'UPDATE') THEN
139 new_references.creation_date := old_references.creation_date;
140 new_references.created_by := old_references.created_by;
141 ELSE
142 new_references.creation_date := x_creation_date;
143 new_references.created_by := x_created_by;
144 END IF;
145 new_references.last_update_date := x_last_update_date;
146 new_references.last_updated_by := x_last_updated_by;
147 new_references.last_update_login := x_last_update_login;
148
149 END Set_Column_Values;
150
151 PROCEDURE Check_Constraints (
152 Column_Name IN VARCHAR2 DEFAULT NULL,
153 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
154 /*************************************************************
155 Created By :samaresh.in
156 Date Created By : 15-May-2000
157 Purpose :
158 Know limitations, enhancements or remarks
159 Change History
160 Who When What
161
162 (reverse chronological order - newest change first)
163 nshee 25-JUL-2002 commented the tochar(todate(<date>,FMT)FMT)
164 conversion of score_Report_date which was giving problems.
165 ***************************************************************/
166 l_column_value VARCHAR2(2000);
167 BEGIN
168
169 IF column_name IS NULL THEN
170 NULL;
171 ELSIF UPPER(column_name) = 'TEST_DATE' THEN
172 new_references.test_date := IGS_GE_DATE.IGSDATE(column_value);
173 ELSIF UPPER(column_name) = 'SCORE_REPORT_DATE' THEN
174 -- l_column_value := tochar( todate ( column_value, 'DD/MM/YYYY'), 'YYYY/MM/DD');
175 new_references.score_report_date := IGS_GE_DATE.IGSDATE(l_column_value);
176 ELSIF UPPER(column_name) = 'NON_STANDARD_ADMIN' THEN
177 new_references.non_standard_admin := column_value;
178 NULL;
179 END IF;
180
181 -- The following code checks for check constraints on the Columns.
182
183 IF Upper(Column_Name) = 'SCORE_REPORT_DATE' OR
184 Column_Name IS NULL THEN
185 IF TRUNC(new_references.score_report_date) > TRUNC(sysdate) OR
186 TRUNC(new_references.score_report_date) < TRUNC(new_references.test_date) THEN
187 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
188 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_SCORE_RPT_DATE'));
189 IGS_GE_MSG_STACK.ADD;
190 App_Exception.Raise_Exception;
191 END IF;
192 END IF;
193
194 -- The following code checks for check constraints on the Columns.
195 IF Upper(Column_Name) = 'NON_STANDARD_ADMIN' OR
196 Column_Name IS NULL THEN
197 IF NOT (new_references.non_standard_admin in ('Y','N')) THEN
198 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
199 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_NON_STANDARD_ADMIN'));
200 IGS_GE_MSG_STACK.ADD;
201 App_Exception.Raise_Exception;
202 END IF;
203 END IF;
204
205 END Check_Constraints;
206
207 PROCEDURE Check_Parent_Existance AS
208 /*************************************************************
209 Created By :samaresh.in
210 Date Created By : 15-May-2000
211 Purpose :
212 Know limitations, enhancements or remarks
213 Change History
214 Who When What
215
216 (reverse chronological order - newest change first)
217 ***************************************************************/
218
219 BEGIN
220
221 IF (((old_references.edu_level_id = new_references.edu_level_id)) OR
222 ((new_references.edu_level_id IS NULL))) THEN
223 NULL;
224 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
225 new_references.edu_level_id,
226 'EDU_LEVEL',
227 'N'
228 ) THEN
229 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
230 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_EDU_LEVEL'));
231 IGS_GE_MSG_STACK.ADD;
232 App_Exception.Raise_Exception;
233 END IF;
234
235 IF (((old_references.admission_test_type = new_references.admission_test_type)) OR
236 ((new_references.admission_test_type IS NULL))) THEN
237 NULL;
238 ELSIF NOT Igs_Ad_Test_Type_Pkg.Get_PK_For_Validation (
239 new_references.admission_test_type,
240 'N'
241 ) THEN
242 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
243 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_TEST'));
244 IGS_GE_MSG_STACK.ADD;
245 App_Exception.Raise_Exception;
246 END IF;
247
248 IF (((old_references.grade_id = new_references.grade_id)) OR
249 ((new_references.grade_id IS NULL))) THEN
250 NULL;
251 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
252 new_references.grade_id,
253 'TEST_RESULTS_GRADE',
254 'N'
255 ) THEN
256 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
257 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_GRADE'));
258 IGS_GE_MSG_STACK.ADD;
259 END IF;
260
261 IF (((old_references.score_source_id = new_references.score_source_id)) OR
262 ((new_references.score_source_id IS NULL))) THEN
263 NULL;
264 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
265 new_references.score_source_id,
266 'SYS_SCORE_SOURCE',
267 'N'
268 ) THEN
269 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
270 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_SCORE_SOURCE'));
271 IGS_GE_MSG_STACK.ADD;
272 App_Exception.Raise_Exception;
273 END IF;
274
275 IF (((old_references.person_id = new_references.person_id)) OR
276 ((new_references.person_id IS NULL))) THEN
277 NULL;
278 ELSIF NOT Igs_Pe_Person_Pkg.Get_PK_For_Validation (
279 new_references.person_id
280 ) THEN
281 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
282 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_PERSON'));
283 IGS_GE_MSG_STACK.ADD;
284 App_Exception.Raise_Exception;
285 END IF;
286
287 IF (((old_references.score_type = new_references.score_type)) OR
288 ((new_references.score_type IS NULL))) THEN
289 NULL;
290 ELSE
291 IF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation (
292 'TEST_SCORE_TYPE',
293 new_references.score_type
294 ) THEN
295 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
296 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_SCORE_TYPE'));
297 IGS_GE_MSG_STACK.ADD;
298 App_Exception.Raise_Exception;
299 END IF;
300 END IF;
301
302 IF (((old_references.special_code = new_references.special_code)) OR
303 ((new_references.special_code IS NULL))) THEN
304 NULL;
305 ELSE
306 IF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation (
307 'TEST_SPECIAL_CODE',
308 new_references.special_code
309 ) THEN
310 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
311 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_GE_SPECIAL'));
312 IGS_GE_MSG_STACK.ADD;
313 App_Exception.Raise_Exception;
314 END IF;
315 END IF;
316
317 END Check_Parent_Existance;
318
319 PROCEDURE Check_Child_Existance IS
320 /*************************************************************
321 Created By :samaresh.in
322 Date Created By : 15-May-2000
323 Purpose :
324 Know limitations, enhancements or remarks
325 Change History
326 Who When What
327
328 (reverse chronological order - newest change first)
329 ***************************************************************/
330
331 BEGIN
332
333 Igs_Ad_Tst_Rslt_Dtls_Pkg.Get_FK_Igs_Ad_Test_Results (
334 old_references.test_results_id
335 );
336
337 END Check_Child_Existance;
338
339
340 PROCEDURE Check_Child_For_Update AS
341
342 /*************************************************************
343 Created By : Adarsh Padegal
344 Date Created By : 28 Dec 2004
345 Purpose : To check if child records are available while updating
346 the parent record.
347 Know limitations, enhancements or remarks
348 Change History
349 Who When What
353 ***************************************************************/
350 pkpatel 21-Jul-2005 Bug 4327807 (Person SS Enhancement)
351 Added the check that test date can't be a future date when child records exist
352 (Reverse chronological order - newest change first)
354
355 CURSOR cur_rowid(cp_test_results_id NUMBER) IS
356 SELECT rowid
357 FROM igs_ad_tst_rslt_dtls
358 WHERE test_results_id = cp_test_results_id;
359
360 lv_rowid cur_rowid%RowType;
361
362 BEGIN
363 IF old_references.admission_test_type <> new_references.admission_test_type THEN
364 Open cur_rowid(old_references.test_results_id);
365 Fetch cur_rowid INTO lv_rowid;
366 IF (cur_rowid%FOUND) THEN
367 Close cur_rowid;
368 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ATRD_ATR_UPD_FK');
369 IGS_GE_MSG_STACK.ADD;
370 App_Exception.Raise_Exception;
371 Return;
372 END IF;
373 Close cur_rowid;
374 END IF;
375
376 IF new_references.test_date <> old_references.test_date THEN
377 Open cur_rowid(old_references.test_results_id);
378 Fetch cur_rowid INTO lv_rowid;
379 IF (cur_rowid%FOUND) THEN
380 IF (new_references.test_date > TRUNC(SYSDATE)) THEN
381 Close cur_rowid;
382 Fnd_Message.Set_Name ('IGS', 'IGS_SS_AD_SEG_NOT_IN_FUTURE');
383 IGS_GE_MSG_STACK.ADD;
384 App_Exception.Raise_Exception;
385 END IF;
386 END IF;
387 Close cur_rowid;
388 END IF;
389
390 END Check_Child_For_Update;
391
392 PROCEDURE beforerowinsertupdate(p_inserting BOOLEAN,p_updating BOOLEAN) AS
393 /*
394 || Created By : pkpatel
395 || Created On : 6-Jun-2005
396 || Purpose : Handles the Unique Constraint logic defined for the columns.
397 || Known limitations, enhancements or remarks :
398 || Change History :
399 || Who When What
400 || (reverse chronological order - newest change first)
401 */
402 CURSOR get_dob_dt_cur(cp_person_id igs_pe_passport.person_id%TYPE)
403 IS
404 SELECT birth_date
405 FROM igs_pe_person_base_v
406 WHERE person_id = cp_person_id;
407
408 l_birth_dt igs_pe_person_base_v.birth_date%TYPE;
409 BEGIN
410 IF p_inserting or p_updating THEN
411 OPEN get_dob_dt_cur(new_references.person_id);
412 FETCH get_dob_dt_cur INTO l_birth_dt;
413 CLOSE get_dob_dt_cur;
414
415 IF l_birth_dt IS NOT NULL AND new_references.test_date IS NOT NULL THEN
416 IF l_birth_dt > new_references.test_date THEN
417 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_STRT_DT_LESS_BIRTH_DT');
418 IGS_GE_MSG_STACK.ADD;
419 APP_EXCEPTION.RAISE_EXCEPTION;
420 END IF;
421 END IF;
422 END IF;
423 END beforerowinsertupdate;
424
425 FUNCTION Get_PK_For_Validation (
426 x_test_results_id IN NUMBER
427 ) RETURN BOOLEAN AS
428
429 /*************************************************************
430 Created By :samaresh.in
431 Date Created By : 15-May-2000
432 Purpose :
433 Know limitations, enhancements or remarks
434 Change History
435 Who When What
436
437 (reverse chronological order - newest change first)
438 ***************************************************************/
439
440 CURSOR cur_rowid IS
441 SELECT rowid
442 FROM igs_ad_test_results
443 WHERE test_results_id = x_test_results_id
444 FOR UPDATE NOWAIT;
445
446 lv_rowid cur_rowid%RowType;
447
448 BEGIN
449
450 Open cur_rowid;
451 Fetch cur_rowid INTO lv_rowid;
452 IF (cur_rowid%FOUND) THEN
453 Close cur_rowid;
454 Return(TRUE);
455 ELSE
456 Close cur_rowid;
457 Return(FALSE);
458 END IF;
459 END Get_PK_For_Validation;
460
461 PROCEDURE Get_FK_Igs_Ad_Code_Classes (
462 x_code_id IN NUMBER
463 ) AS
464
465 /*************************************************************
466 Created By :samaresh.in
467 Date Created By : 15-May-2000
468 Purpose :
469 Know limitations, enhancements or remarks
470 Change History
471 Who When What
472
473 (reverse chronological order - newest change first)
474 ***************************************************************/
475
476 CURSOR cur_rowid IS
477 SELECT rowid
478 FROM igs_ad_test_results
479 WHERE edu_level_id = x_code_id ;
480
481 CURSOR cur_rowid2 IS
482 SELECT rowid
483 FROM igs_ad_test_results
484 WHERE grade_id = x_code_id ;
485
486 CURSOR cur_rowid3 IS
487 SELECT rowid
488 FROM igs_ad_test_results
489 WHERE score_source_id = x_code_id ;
490
491 lv_rowid cur_rowid%RowType;
492
493 BEGIN
494
495 Open cur_rowid;
496 Fetch cur_rowid INTO lv_rowid;
497 IF (cur_rowid%FOUND) THEN
498 Close cur_rowid;
499 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ATR_ACDC_FK2');
500 IGS_GE_MSG_STACK.ADD;
504 Close cur_rowid;
501 App_Exception.Raise_Exception;
502 Return;
503 END IF;
505
506 Open cur_rowid2;
507 Fetch cur_rowid2 INTO lv_rowid;
508 IF (cur_rowid2%FOUND) THEN
509 Close cur_rowid2;
510 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ATR_ACDC_FK3');
511 IGS_GE_MSG_STACK.ADD;
512 App_Exception.Raise_Exception;
513 Return;
514 END IF;
515 Close cur_rowid2;
516
517 Open cur_rowid3;
518 Fetch cur_rowid3 INTO lv_rowid;
519 IF (cur_rowid3%FOUND) THEN
520 Close cur_rowid3;
521 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ATR_ACDC_FK');
522 IGS_GE_MSG_STACK.ADD;
523 App_Exception.Raise_Exception;
524 Return;
525 END IF;
526 Close cur_rowid3;
527 END Get_FK_Igs_Ad_Code_Classes;
528
529 PROCEDURE Get_FK_Igs_Ad_Test_Type (
530 x_admission_test_type IN VARCHAR2
531 ) AS
532
533 /*************************************************************
534 Created By :samaresh.in
535 Date Created By : 15-May-2000
536 Purpose :
537 Know limitations, enhancements or remarks
538 Change History
539 Who When What
540
541 (reverse chronological order - newest change first)
542 ***************************************************************/
543
544 CURSOR cur_rowid IS
545 SELECT rowid
546 FROM igs_ad_test_results
547 WHERE admission_test_type = x_admission_test_type ;
548
549 lv_rowid cur_rowid%RowType;
550
551 BEGIN
552
553 Open cur_rowid;
554 Fetch cur_rowid INTO lv_rowid;
555 IF (cur_rowid%FOUND) THEN
556 Close cur_rowid;
557 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ATR_ADMTT_FK');
558 IGS_GE_MSG_STACK.ADD;
559 App_Exception.Raise_Exception;
560 Return;
561 END IF;
562 Close cur_rowid;
563
564 END Get_FK_Igs_Ad_Test_Type;
565
566
567 PROCEDURE Get_FK_Igs_Pe_Person (
568 x_person_id IN NUMBER
569 ) AS
570
571 /*************************************************************
572 Created By :samaresh.in
573 Date Created By : 15-May-2000
574 Purpose :
575 Know limitations, enhancements or remarks
576 Change History
577 Who When What
578
579 (reverse chronological order - newest change first)
580 ***************************************************************/
581
582 CURSOR cur_rowid IS
583 SELECT rowid
584 FROM igs_ad_test_results
585 WHERE person_id = x_person_id ;
586
587 lv_rowid cur_rowid%RowType;
588
589 BEGIN
590
591 Open cur_rowid;
592 Fetch cur_rowid INTO lv_rowid;
593 IF (cur_rowid%FOUND) THEN
594 Close cur_rowid;
595 Fnd_Message.Set_Name ('IGS', 'IGS_AD_PE_PERSON_FK');
596 IGS_GE_MSG_STACK.ADD;
597 App_Exception.Raise_Exception;
598 Return;
599 END IF;
600 Close cur_rowid;
601
602 END Get_FK_Igs_Pe_Person;
603
604
605 PROCEDURE Before_DML (
606 p_action IN VARCHAR2,
607 x_rowid IN VARCHAR2 DEFAULT NULL,
608 x_test_results_id IN NUMBER DEFAULT NULL,
609 x_person_id IN NUMBER DEFAULT NULL,
610 x_admission_test_type IN VARCHAR2 DEFAULT NULL,
611 x_test_date IN DATE DEFAULT NULL,
612 x_score_report_date IN DATE DEFAULT NULL,
613 x_edu_level_id IN NUMBER DEFAULT NULL,
614 x_score_type IN VARCHAR2 DEFAULT NULL,
615 x_score_source_id IN NUMBER DEFAULT NULL,
616 x_non_standard_admin IN VARCHAR2 DEFAULT NULL,
617 x_comp_test_score IN NUMBER DEFAULT NULL,
618 x_special_code IN VARCHAR2 DEFAULT NULL,
619 x_registration_number IN VARCHAR2 DEFAULT NULL,
620 x_grade_id IN NUMBER DEFAULT NULL,
621 x_attribute_category IN VARCHAR2 DEFAULT NULL,
622 x_attribute1 IN VARCHAR2 DEFAULT NULL,
623 x_attribute2 IN VARCHAR2 DEFAULT NULL,
624 x_attribute3 IN VARCHAR2 DEFAULT NULL,
625 x_attribute4 IN VARCHAR2 DEFAULT NULL,
626 x_attribute5 IN VARCHAR2 DEFAULT NULL,
627 x_attribute6 IN VARCHAR2 DEFAULT NULL,
628 x_attribute7 IN VARCHAR2 DEFAULT NULL,
629 x_attribute8 IN VARCHAR2 DEFAULT NULL,
630 x_attribute9 IN VARCHAR2 DEFAULT NULL,
631 x_attribute10 IN VARCHAR2 DEFAULT NULL,
632 x_attribute11 IN VARCHAR2 DEFAULT NULL,
633 x_attribute12 IN VARCHAR2 DEFAULT NULL,
634 x_attribute13 IN VARCHAR2 DEFAULT NULL,
635 x_attribute14 IN VARCHAR2 DEFAULT NULL,
636 x_attribute15 IN VARCHAR2 DEFAULT NULL,
637 x_attribute16 IN VARCHAR2 DEFAULT NULL,
638 x_attribute17 IN VARCHAR2 DEFAULT NULL,
639 x_attribute18 IN VARCHAR2 DEFAULT NULL,
640 x_attribute19 IN VARCHAR2 DEFAULT NULL,
641 x_attribute20 IN VARCHAR2 DEFAULT NULL,
642 x_creation_date IN DATE DEFAULT NULL,
643 x_created_by IN NUMBER DEFAULT NULL,
644 x_last_update_date IN DATE DEFAULT NULL,
645 x_last_updated_by IN NUMBER DEFAULT NULL,
646 x_last_update_login IN NUMBER DEFAULT NULL,
647 x_active_ind IN VARCHAR2 DEFAULT NULL
648 ) AS
652 Purpose :
649 /*************************************************************
650 Created By :samaresh.in
651 Date Created By : 15-May-2000
653 Know limitations, enhancements or remarks
654 Change History
655 Who When What
656 APADEGAL 10-JAN-2005 To check if the child records exist
657 before the update of parent record.
658
659 (reverse chronological order - newest change first)
660 ***************************************************************/
661 BEGIN
662
663 Set_Column_Values (
664 p_action,
665 x_rowid,
666 x_test_results_id,
667 x_person_id,
668 x_admission_test_type,
669 x_test_date,
670 x_score_report_date,
671 x_edu_level_id,
672 x_score_type,
673 x_score_source_id,
674 x_non_standard_admin,
675 x_comp_test_score,
676 x_special_code,
677 x_registration_number,
678 x_grade_id,
679 x_attribute_category,
680 x_attribute1,
681 x_attribute2,
682 x_attribute3,
683 x_attribute4,
684 x_attribute5,
685 x_attribute6,
686 x_attribute7,
687 x_attribute8,
688 x_attribute9,
689 x_attribute10,
690 x_attribute11,
691 x_attribute12,
692 x_attribute13,
693 x_attribute14,
694 x_attribute15,
695 x_attribute16,
696 x_attribute17,
697 x_attribute18,
698 x_attribute19,
699 x_attribute20,
700 x_creation_date,
701 x_created_by,
702 x_last_update_date,
703 x_last_updated_by,
704 x_last_update_login,
705 x_active_ind
706 );
707
708
709 IF (p_action = 'INSERT') THEN
710 -- Call all the procedures related to Before Insert.
711 beforerowinsertupdate(TRUE, FALSE);
712 IF Get_Pk_For_Validation(
713 new_references.test_results_id) THEN
714 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
715 IGS_GE_MSG_STACK.ADD;
716 App_Exception.Raise_Exception;
717 END IF;
718 Check_Constraints;
719 Check_Parent_Existance;
720 ELSIF (p_action = 'UPDATE') THEN
721 -- Call all the procedures related to Before Update.
722 beforerowinsertupdate(FALSE, TRUE);
723 Check_Constraints;
724 Check_Parent_Existance;
725
726 Check_Child_For_Update;
727
728 ELSIF (p_action = 'DELETE') THEN
729 -- Call all the procedures related to Before Delete.
730 Null;
731 Check_Child_Existance;
732 ELSIF (p_action = 'VALIDATE_INSERT') THEN
733 -- Call all the procedures related to Before Insert.
734 IF Get_PK_For_Validation (
735 new_references.test_results_id) THEN
736 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
737 IGS_GE_MSG_STACK.ADD;
738 App_Exception.Raise_Exception;
739 END IF;
740 Check_Constraints;
741 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
742 Check_Constraints;
743 ELSIF (p_action = 'VALIDATE_DELETE') THEN
744 Check_Child_Existance;
745 END IF;
746
747 END Before_DML;
748
749 PROCEDURE After_DML (
750 p_action IN VARCHAR2,
751 x_rowid IN VARCHAR2
752 ) IS
753 /*************************************************************
754 Created By :samaresh.in
755 Date Created By : 15-May-2000
756 Purpose :
757 Know limitations, enhancements or remarks
758 Change History
759 Who When What
760
761 (reverse chronological order - newest change first)
762 ***************************************************************/
763
764 BEGIN
765
766 l_rowid := x_rowid;
767
768 IF (p_action = 'INSERT') THEN
769 -- Call all the procedures related to After Insert.
770 --Raise the buisness event
771 igs_ad_wf_001.TESTSCORE_CRT_EVENT
772 (
773 P_TEST_RESULTS_ID => new_references.test_results_id,
774 P_PERSON_ID => new_references.person_id,
775 P_ACTIVE_IND => new_references.active_ind
776 );
777 ELSIF (p_action = 'UPDATE') THEN
778 -- Call all the procedures related to After Update.
779 --Raise the buisness event
780 igs_ad_wf_001.TESTSCORE_UPD_EVENT
781 (
782 P_TEST_RESULTS_ID => new_references.test_results_id,
783 P_PERSON_ID => new_references.person_id,
784 P_ACTIVE_IND_NEW => new_references.active_ind,
785 P_ACTIVE_IND_OLD => old_references.active_ind,
786 P_ADMISSION_TEST_TYPE_NEW => new_references.admission_test_type,
787 P_ADMISSION_TEST_TYPE_OLD => old_references.admission_test_type,
788 P_GRADE_ID_NEW => new_references.grade_id,
789 P_GRADE_ID_OLD => old_references.grade_id,
790 P_COMP_TEST_SCORE_NEW => new_references.comp_test_score,
791 P_COMP_TEST_SCORE_OLD => old_references.comp_test_score
792 );
793 ELSIF (p_action = 'DELETE') THEN
797
794 -- Call all the procedures related to After Delete.
795 Null;
796 END IF;
798 END After_DML;
799
800 procedure INSERT_ROW (
801 X_ROWID in out NOCOPY VARCHAR2,
802 x_TEST_RESULTS_ID IN OUT NOCOPY NUMBER,
803 x_PERSON_ID IN NUMBER,
804 x_ADMISSION_TEST_TYPE IN VARCHAR2,
805 x_TEST_DATE IN DATE,
806 x_SCORE_REPORT_DATE IN DATE,
807 x_EDU_LEVEL_ID IN NUMBER,
808 x_SCORE_TYPE IN VARCHAR2,
809 x_SCORE_SOURCE_ID IN NUMBER,
810 x_NON_STANDARD_ADMIN IN VARCHAR2,
811 x_COMP_TEST_SCORE IN NUMBER,
812 x_SPECIAL_CODE IN VARCHAR2,
813 x_REGISTRATION_NUMBER IN VARCHAR2,
814 x_GRADE_ID IN NUMBER,
815 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
816 x_ATTRIBUTE1 IN VARCHAR2,
817 x_ATTRIBUTE2 IN VARCHAR2,
818 x_ATTRIBUTE3 IN VARCHAR2,
819 x_ATTRIBUTE4 IN VARCHAR2,
820 x_ATTRIBUTE5 IN VARCHAR2,
821 x_ATTRIBUTE6 IN VARCHAR2,
822 x_ATTRIBUTE7 IN VARCHAR2,
823 x_ATTRIBUTE8 IN VARCHAR2,
824 x_ATTRIBUTE9 IN VARCHAR2,
825 x_ATTRIBUTE10 IN VARCHAR2,
826 x_ATTRIBUTE11 IN VARCHAR2,
827 x_ATTRIBUTE12 IN VARCHAR2,
828 x_ATTRIBUTE13 IN VARCHAR2,
829 x_ATTRIBUTE14 IN VARCHAR2,
830 x_ATTRIBUTE15 IN VARCHAR2,
831 x_ATTRIBUTE16 IN VARCHAR2,
832 x_ATTRIBUTE17 IN VARCHAR2,
833 x_ATTRIBUTE18 IN VARCHAR2,
834 x_ATTRIBUTE19 IN VARCHAR2,
835 x_ATTRIBUTE20 IN VARCHAR2,
836 X_MODE in VARCHAR2,
837 x_ACTIVE_IND IN VARCHAR2 default NULL
838 ) AS
839 /*************************************************************
840 Created By :samaresh.in
841 Date Created By : 15-May-2000
842 Purpose :
843 Know limitations, enhancements or remarks
844 Change History
845 Who When What
846
847 RAVISHAR Feb,25 2005 Removed the default value of X_MODE parameter from
848 body of this package for bug 4163319
849 GSCC standard says that default value should be
850 present only in specification
851
852 (reverse chronological order - newest change first)
853 pbondugu 03-04-2003 For test_date and score_report_date trunc of those fileds are inserted into tables.
854
855 ***************************************************************/
856
857 cursor C is select ROWID from IGS_AD_TEST_RESULTS
858 where TEST_RESULTS_ID= X_TEST_RESULTS_ID;
859
860 X_LAST_UPDATE_DATE DATE ;
861 X_LAST_UPDATED_BY NUMBER ;
862 X_LAST_UPDATE_LOGIN NUMBER ;
863 X_REQUEST_ID NUMBER;
864 X_PROGRAM_ID NUMBER;
865 X_PROGRAM_APPLICATION_ID NUMBER;
866 X_PROGRAM_UPDATE_DATE DATE;
867 L_MODE VARCHAR2(1);
868 begin
869 L_MODE := NVL(X_MODE,'R');
870 X_LAST_UPDATE_DATE := SYSDATE;
871 if(L_MODE = 'I') then
872 X_LAST_UPDATED_BY := 1;
873 X_LAST_UPDATE_LOGIN := 0;
874 elsif (L_MODE = 'R') then
875 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
876 if X_LAST_UPDATED_BY is NULL then
877 X_LAST_UPDATED_BY := -1;
878 end if;
879 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
880 if X_LAST_UPDATE_LOGIN is NULL then
881 X_LAST_UPDATE_LOGIN := -1;
882 end if;
883 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
884 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
885 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
886 if (X_REQUEST_ID = -1) then
887 X_REQUEST_ID := NULL;
888 X_PROGRAM_ID := NULL;
889 X_PROGRAM_APPLICATION_ID := NULL;
890 X_PROGRAM_UPDATE_DATE := NULL;
891 else
892 X_PROGRAM_UPDATE_DATE := SYSDATE;
893 end if;
894 else
895 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
896 IGS_GE_MSG_STACK.ADD;
897 app_exception.raise_exception;
898 end if;
899
900 X_TEST_RESULTS_ID := -1;
901 Before_DML(
902 p_action=>'INSERT',
903 x_rowid=>X_ROWID,
904 x_test_results_id=>X_TEST_RESULTS_ID,
905 x_person_id=>X_PERSON_ID,
906 x_admission_test_type=>X_ADMISSION_TEST_TYPE,
907 x_test_date=>X_TEST_DATE,
908 x_score_report_date=>X_SCORE_REPORT_DATE,
909 x_edu_level_id=>X_EDU_LEVEL_ID,
910 x_score_type=>X_SCORE_TYPE,
911 x_score_source_id=>X_SCORE_SOURCE_ID,
912 x_non_standard_admin=>X_NON_STANDARD_ADMIN,
913 x_comp_test_score=>X_COMP_TEST_SCORE,
914 x_special_code=>X_SPECIAL_CODE,
915 x_registration_number=>X_REGISTRATION_NUMBER,
916 x_grade_id=>X_GRADE_ID,
917 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
918 x_attribute1=>X_ATTRIBUTE1,
919 x_attribute2=>X_ATTRIBUTE2,
920 x_attribute3=>X_ATTRIBUTE3,
921 x_attribute4=>X_ATTRIBUTE4,
922 x_attribute5=>X_ATTRIBUTE5,
923 x_attribute6=>X_ATTRIBUTE6,
924 x_attribute7=>X_ATTRIBUTE7,
925 x_attribute8=>X_ATTRIBUTE8,
926 x_attribute9=>X_ATTRIBUTE9,
927 x_attribute10=>X_ATTRIBUTE10,
931 x_attribute14=>X_ATTRIBUTE14,
928 x_attribute11=>X_ATTRIBUTE11,
929 x_attribute12=>X_ATTRIBUTE12,
930 x_attribute13=>X_ATTRIBUTE13,
932 x_attribute15=>X_ATTRIBUTE15,
933 x_attribute16=>X_ATTRIBUTE16,
934 x_attribute17=>X_ATTRIBUTE17,
935 x_attribute18=>X_ATTRIBUTE18,
936 x_attribute19=>X_ATTRIBUTE19,
937 x_attribute20=>X_ATTRIBUTE20,
938 x_creation_date=>X_LAST_UPDATE_DATE,
939 x_created_by=>X_LAST_UPDATED_BY,
940 x_last_update_date=>X_LAST_UPDATE_DATE,
941 x_last_updated_by=>X_LAST_UPDATED_BY,
942 x_last_update_login=>X_LAST_UPDATE_LOGIN,
943 x_active_ind => X_ACTIVE_IND );
944 IF (x_mode = 'S') THEN
945 igs_sc_gen_001.set_ctx('R');
946 END IF;
947 insert into IGS_AD_TEST_RESULTS (
948 TEST_RESULTS_ID
949 ,PERSON_ID
950 ,ADMISSION_TEST_TYPE
951 ,TEST_DATE
952 ,SCORE_REPORT_DATE
953 ,EDU_LEVEL_ID
954 ,SCORE_TYPE
955 ,SCORE_SOURCE_ID
956 ,NON_STANDARD_ADMIN
957 ,COMP_TEST_SCORE
958 ,SPECIAL_CODE
959 ,REGISTRATION_NUMBER
960 ,GRADE_ID
961 ,ATTRIBUTE_CATEGORY
962 ,ATTRIBUTE1
963 ,ATTRIBUTE2
964 ,ATTRIBUTE3
965 ,ATTRIBUTE4
966 ,ATTRIBUTE5
967 ,ATTRIBUTE6
968 ,ATTRIBUTE7
969 ,ATTRIBUTE8
970 ,ATTRIBUTE9
971 ,ATTRIBUTE10
972 ,ATTRIBUTE11
973 ,ATTRIBUTE12
974 ,ATTRIBUTE13
975 ,ATTRIBUTE14
976 ,ATTRIBUTE15
977 ,ATTRIBUTE16
978 ,ATTRIBUTE17
979 ,ATTRIBUTE18
980 ,ATTRIBUTE19
981 ,ATTRIBUTE20
982 ,CREATION_DATE
983 ,CREATED_BY
984 ,LAST_UPDATE_DATE
985 ,LAST_UPDATED_BY
986 ,LAST_UPDATE_LOGIN
987 ,REQUEST_ID
988 ,PROGRAM_ID
989 ,PROGRAM_APPLICATION_ID
990 ,PROGRAM_UPDATE_DATE
991 ,ACTIVE_IND
992 ) values (
993 IGS_AD_TEST_RESULTS_S.NEXTVAL
994 ,NEW_REFERENCES.PERSON_ID
995 ,NEW_REFERENCES.ADMISSION_TEST_TYPE
996 ,NEW_REFERENCES.TEST_DATE
997 ,NEW_REFERENCES.SCORE_REPORT_DATE
998 ,NEW_REFERENCES.EDU_LEVEL_ID
999 ,NEW_REFERENCES.SCORE_TYPE
1000 ,NEW_REFERENCES.SCORE_SOURCE_ID
1001 ,NEW_REFERENCES.NON_STANDARD_ADMIN
1002 ,NEW_REFERENCES.COMP_TEST_SCORE
1003 ,NEW_REFERENCES.SPECIAL_CODE
1004 ,NEW_REFERENCES.REGISTRATION_NUMBER
1005 ,NEW_REFERENCES.GRADE_ID
1006 ,NEW_REFERENCES.ATTRIBUTE_CATEGORY
1007 ,NEW_REFERENCES.ATTRIBUTE1
1008 ,NEW_REFERENCES.ATTRIBUTE2
1009 ,NEW_REFERENCES.ATTRIBUTE3
1010 ,NEW_REFERENCES.ATTRIBUTE4
1011 ,NEW_REFERENCES.ATTRIBUTE5
1012 ,NEW_REFERENCES.ATTRIBUTE6
1013 ,NEW_REFERENCES.ATTRIBUTE7
1014 ,NEW_REFERENCES.ATTRIBUTE8
1015 ,NEW_REFERENCES.ATTRIBUTE9
1016 ,NEW_REFERENCES.ATTRIBUTE10
1017 ,NEW_REFERENCES.ATTRIBUTE11
1018 ,NEW_REFERENCES.ATTRIBUTE12
1019 ,NEW_REFERENCES.ATTRIBUTE13
1020 ,NEW_REFERENCES.ATTRIBUTE14
1021 ,NEW_REFERENCES.ATTRIBUTE15
1022 ,NEW_REFERENCES.ATTRIBUTE16
1023 ,NEW_REFERENCES.ATTRIBUTE17
1024 ,NEW_REFERENCES.ATTRIBUTE18
1025 ,NEW_REFERENCES.ATTRIBUTE19
1026 ,NEW_REFERENCES.ATTRIBUTE20
1027 ,X_LAST_UPDATE_DATE
1028 ,X_LAST_UPDATED_BY
1029 ,X_LAST_UPDATE_DATE
1030 ,X_LAST_UPDATED_BY
1031 ,X_LAST_UPDATE_LOGIN
1032 ,X_REQUEST_ID
1033 ,X_PROGRAM_ID
1034 ,X_PROGRAM_APPLICATION_ID
1035 ,X_PROGRAM_UPDATE_DATE
1036 ,NEW_REFERENCES.ACTIVE_IND
1037 )RETURNING TEST_RESULTS_ID INTO X_TEST_RESULTS_ID;
1038 IF (x_mode = 'S') THEN
1039 igs_sc_gen_001.unset_ctx('R');
1040 END IF;
1041
1042
1043 NEW_REFERENCES.TEST_RESULTS_ID := X_TEST_RESULTS_ID;
1044 open c;
1045 fetch c into X_ROWID;
1046 if (c%notfound) then
1047 close c;
1048 raise no_data_found;
1049 end if;
1050 close c;
1051 After_DML (
1052 p_action => 'INSERT' ,
1053 x_rowid => X_ROWID );
1054 EXCEPTION
1055 WHEN OTHERS THEN
1056 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
1057 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1058 fnd_message.set_token ('ERR_CD', SQLCODE);
1059 igs_ge_msg_stack.add;
1060 igs_sc_gen_001.unset_ctx('R');
1061 app_exception.raise_exception;
1062 ELSE
1063 igs_sc_gen_001.unset_ctx('R');
1064 RAISE;
1065 END IF;
1066
1067 end INSERT_ROW;
1068 procedure LOCK_ROW (
1069 X_ROWID in VARCHAR2,
1070 x_TEST_RESULTS_ID IN NUMBER,
1071 x_PERSON_ID IN NUMBER,
1072 x_ADMISSION_TEST_TYPE IN VARCHAR2,
1076 x_SCORE_TYPE IN VARCHAR2,
1073 x_TEST_DATE IN DATE,
1074 x_SCORE_REPORT_DATE IN DATE,
1075 x_EDU_LEVEL_ID IN NUMBER,
1077 x_SCORE_SOURCE_ID IN NUMBER,
1078 x_NON_STANDARD_ADMIN IN VARCHAR2,
1079 x_COMP_TEST_SCORE IN NUMBER,
1080 x_SPECIAL_CODE IN VARCHAR2,
1081 x_REGISTRATION_NUMBER IN VARCHAR2,
1082 x_GRADE_ID IN NUMBER,
1083 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
1084 x_ATTRIBUTE1 IN VARCHAR2,
1085 x_ATTRIBUTE2 IN VARCHAR2,
1086 x_ATTRIBUTE3 IN VARCHAR2,
1087 x_ATTRIBUTE4 IN VARCHAR2,
1088 x_ATTRIBUTE5 IN VARCHAR2,
1089 x_ATTRIBUTE6 IN VARCHAR2,
1090 x_ATTRIBUTE7 IN VARCHAR2,
1091 x_ATTRIBUTE8 IN VARCHAR2,
1092 x_ATTRIBUTE9 IN VARCHAR2,
1093 x_ATTRIBUTE10 IN VARCHAR2,
1094 x_ATTRIBUTE11 IN VARCHAR2,
1095 x_ATTRIBUTE12 IN VARCHAR2,
1096 x_ATTRIBUTE13 IN VARCHAR2,
1097 x_ATTRIBUTE14 IN VARCHAR2,
1098 x_ATTRIBUTE15 IN VARCHAR2,
1099 x_ATTRIBUTE16 IN VARCHAR2,
1100 x_ATTRIBUTE17 IN VARCHAR2,
1101 x_ATTRIBUTE18 IN VARCHAR2,
1102 x_ATTRIBUTE19 IN VARCHAR2,
1103 x_ATTRIBUTE20 IN VARCHAR2,
1104 x_ACTIVE_IND IN VARCHAR2 ) AS
1105 /*************************************************************
1106 Created By :samaresh.in
1107 Date Created By : 15-May-2000
1108 Purpose :
1109 Know limitations, enhancements or remarks
1110 Change History
1111 Who When What
1112
1113 (reverse chronological order - newest change first)
1114 pbondugu 03-04-2003 For test_date and score_report_date check is done for trunc of those fileds
1115 ***************************************************************/
1116
1117 cursor c1 is select
1118 PERSON_ID
1119 , ADMISSION_TEST_TYPE
1120 , TEST_DATE
1121 , SCORE_REPORT_DATE
1122 , EDU_LEVEL_ID
1123 , SCORE_TYPE
1124 , SCORE_SOURCE_ID
1125 , NON_STANDARD_ADMIN
1126 , COMP_TEST_SCORE
1127 , SPECIAL_CODE
1128 , REGISTRATION_NUMBER
1129 , GRADE_ID
1130 , ATTRIBUTE_CATEGORY
1131 , ATTRIBUTE1
1132 , ATTRIBUTE2
1133 , ATTRIBUTE3
1134 , ATTRIBUTE4
1135 , ATTRIBUTE5
1136 , ATTRIBUTE6
1137 , ATTRIBUTE7
1138 , ATTRIBUTE8
1139 , ATTRIBUTE9
1140 , ATTRIBUTE10
1141 , ATTRIBUTE11
1142 , ATTRIBUTE12
1143 , ATTRIBUTE13
1144 , ATTRIBUTE14
1145 , ATTRIBUTE15
1146 , ATTRIBUTE16
1147 , ATTRIBUTE17
1148 , ATTRIBUTE18
1149 , ATTRIBUTE19
1150 , ATTRIBUTE20
1151 , ACTIVE_IND
1152 from IGS_AD_TEST_RESULTS
1153 where ROWID = X_ROWID
1154 for update nowait;
1155 tlinfo c1%rowtype;
1156 begin
1157 open c1;
1158 fetch c1 into tlinfo;
1159 if (c1%notfound) then
1160 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1161 IGS_GE_MSG_STACK.ADD;
1162 close c1;
1163 app_exception.raise_exception;
1164 return;
1165 end if;
1166 close c1;
1167 if ( ( tlinfo.PERSON_ID = X_PERSON_ID)
1168 AND (tlinfo.ADMISSION_TEST_TYPE = X_ADMISSION_TEST_TYPE)
1169 AND (trunc(tlinfo.TEST_DATE) = trunc(X_TEST_DATE))
1170 AND ((trunc(tlinfo.SCORE_REPORT_DATE) = trunc(X_SCORE_REPORT_DATE))
1171 OR ((tlinfo.SCORE_REPORT_DATE is null)
1172 AND (X_SCORE_REPORT_DATE is null)))
1173 AND ((tlinfo.EDU_LEVEL_ID = X_EDU_LEVEL_ID)
1174 OR ((tlinfo.EDU_LEVEL_ID is null)
1175 AND (X_EDU_LEVEL_ID is null)))
1176 AND ((tlinfo.SCORE_TYPE = X_SCORE_TYPE)
1177 OR ((tlinfo.SCORE_TYPE is null)
1178 AND (X_SCORE_TYPE is null)))
1179 AND ((tlinfo.SCORE_SOURCE_ID = X_SCORE_SOURCE_ID)
1180 OR ((tlinfo.SCORE_SOURCE_ID is null)
1181 AND (X_SCORE_SOURCE_ID is null)))
1182 AND ((tlinfo.NON_STANDARD_ADMIN = X_NON_STANDARD_ADMIN)
1183 OR ((tlinfo.NON_STANDARD_ADMIN is null)
1184 AND (X_NON_STANDARD_ADMIN is null)))
1185 AND ((tlinfo.COMP_TEST_SCORE = X_COMP_TEST_SCORE)
1186 OR ((tlinfo.COMP_TEST_SCORE is null)
1187 AND (X_COMP_TEST_SCORE is null)))
1188 AND ((tlinfo.SPECIAL_CODE = X_SPECIAL_CODE)
1189 OR ((tlinfo.SPECIAL_CODE is null)
1190 AND (X_SPECIAL_CODE is null)))
1191 AND ((tlinfo.REGISTRATION_NUMBER = X_REGISTRATION_NUMBER)
1192 OR ((tlinfo.REGISTRATION_NUMBER is null)
1193 AND (X_REGISTRATION_NUMBER is null)))
1194 AND ((tlinfo.GRADE_ID = X_GRADE_ID)
1195 OR ((tlinfo.GRADE_ID is null)
1196 AND (X_GRADE_ID is null)))
1197 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
1198 OR ((tlinfo.ATTRIBUTE_CATEGORY is null)
1199 AND (X_ATTRIBUTE_CATEGORY is null)))
1200 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
1201 OR ((tlinfo.ATTRIBUTE1 is null)
1202 AND (X_ATTRIBUTE1 is null)))
1203 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
1204 OR ((tlinfo.ATTRIBUTE2 is null)
1205 AND (X_ATTRIBUTE2 is null)))
1206 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
1207 OR ((tlinfo.ATTRIBUTE3 is null)
1208 AND (X_ATTRIBUTE3 is null)))
1209 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
1210 OR ((tlinfo.ATTRIBUTE4 is null)
1211 AND (X_ATTRIBUTE4 is null)))
1212 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
1216 OR ((tlinfo.ATTRIBUTE6 is null)
1213 OR ((tlinfo.ATTRIBUTE5 is null)
1214 AND (X_ATTRIBUTE5 is null)))
1215 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
1217 AND (X_ATTRIBUTE6 is null)))
1218 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
1219 OR ((tlinfo.ATTRIBUTE7 is null)
1220 AND (X_ATTRIBUTE7 is null)))
1221 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
1222 OR ((tlinfo.ATTRIBUTE8 is null)
1223 AND (X_ATTRIBUTE8 is null)))
1224 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
1225 OR ((tlinfo.ATTRIBUTE9 is null)
1226 AND (X_ATTRIBUTE9 is null)))
1227 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
1228 OR ((tlinfo.ATTRIBUTE10 is null)
1229 AND (X_ATTRIBUTE10 is null)))
1230 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
1231 OR ((tlinfo.ATTRIBUTE11 is null)
1232 AND (X_ATTRIBUTE11 is null)))
1233 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
1234 OR ((tlinfo.ATTRIBUTE12 is null)
1235 AND (X_ATTRIBUTE12 is null)))
1236 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
1237 OR ((tlinfo.ATTRIBUTE13 is null)
1238 AND (X_ATTRIBUTE13 is null)))
1239 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
1240 OR ((tlinfo.ATTRIBUTE14 is null)
1241 AND (X_ATTRIBUTE14 is null)))
1242 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
1243 OR ((tlinfo.ATTRIBUTE15 is null)
1244 AND (X_ATTRIBUTE15 is null)))
1245 AND ((tlinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
1246 OR ((tlinfo.ATTRIBUTE16 is null)
1247 AND (X_ATTRIBUTE16 is null)))
1248 AND ((tlinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
1249 OR ((tlinfo.ATTRIBUTE17 is null)
1250 AND (X_ATTRIBUTE17 is null)))
1251 AND ((tlinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
1252 OR ((tlinfo.ATTRIBUTE18 is null)
1253 AND (X_ATTRIBUTE18 is null)))
1254 AND ((tlinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
1255 OR ((tlinfo.ATTRIBUTE19 is null)
1256 AND (X_ATTRIBUTE19 is null)))
1257 AND ((tlinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
1258 OR ((tlinfo.ATTRIBUTE20 is null)
1259 AND (X_ATTRIBUTE20 is null)))
1260 AND ((tlinfo.ACTIVE_IND = X_ACTIVE_IND)
1261 OR ((tlinfo.ACTIVE_IND is null)
1262 AND (X_ACTIVE_IND is null)))
1263 ) then
1264 null;
1265 else
1266 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1267 IGS_GE_MSG_STACK.ADD;
1268 app_exception.raise_exception;
1269 end if;
1270 return;
1271 end LOCK_ROW;
1272 Procedure UPDATE_ROW (
1273 X_ROWID in VARCHAR2,
1274 x_TEST_RESULTS_ID IN NUMBER,
1275 x_PERSON_ID IN NUMBER,
1276 x_ADMISSION_TEST_TYPE IN VARCHAR2,
1277 x_TEST_DATE IN DATE,
1278 x_SCORE_REPORT_DATE IN DATE,
1279 x_EDU_LEVEL_ID IN NUMBER,
1280 x_SCORE_TYPE IN VARCHAR2,
1281 x_SCORE_SOURCE_ID IN NUMBER,
1282 x_NON_STANDARD_ADMIN IN VARCHAR2,
1283 x_COMP_TEST_SCORE IN NUMBER,
1284 x_SPECIAL_CODE IN VARCHAR2,
1285 x_REGISTRATION_NUMBER IN VARCHAR2,
1286 x_GRADE_ID IN NUMBER,
1287 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
1288 x_ATTRIBUTE1 IN VARCHAR2,
1289 x_ATTRIBUTE2 IN VARCHAR2,
1290 x_ATTRIBUTE3 IN VARCHAR2,
1291 x_ATTRIBUTE4 IN VARCHAR2,
1292 x_ATTRIBUTE5 IN VARCHAR2,
1293 x_ATTRIBUTE6 IN VARCHAR2,
1294 x_ATTRIBUTE7 IN VARCHAR2,
1295 x_ATTRIBUTE8 IN VARCHAR2,
1296 x_ATTRIBUTE9 IN VARCHAR2,
1297 x_ATTRIBUTE10 IN VARCHAR2,
1298 x_ATTRIBUTE11 IN VARCHAR2,
1299 x_ATTRIBUTE12 IN VARCHAR2,
1300 x_ATTRIBUTE13 IN VARCHAR2,
1301 x_ATTRIBUTE14 IN VARCHAR2,
1302 x_ATTRIBUTE15 IN VARCHAR2,
1303 x_ATTRIBUTE16 IN VARCHAR2,
1304 x_ATTRIBUTE17 IN VARCHAR2,
1305 x_ATTRIBUTE18 IN VARCHAR2,
1306 x_ATTRIBUTE19 IN VARCHAR2,
1307 x_ATTRIBUTE20 IN VARCHAR2,
1308 X_MODE in VARCHAR2,
1309 x_ACTIVE_IND IN VARCHAR2 DEFAULT NULL
1310 ) AS
1311 /*************************************************************
1312 Created By :samaresh.in
1313 Date Created By : 15-May-2000
1314 Purpose :
1315 Know limitations, enhancements or remarks
1316 Change History
1317 Who When What
1318
1319 (reverse chronological order - newest change first)
1320 RAVISHAR Feb,25 2005 Removed the default value of X_MODE parameter from
1321 body of this package for bug 4163319
1322 GSCC standard says that default value should be
1323 present only in specification
1324
1325 pbondugu 03-04-2003 For test_date and score_report_date trunc of those fileds are updated into table.
1326
1327 ***************************************************************/
1328
1329 X_LAST_UPDATE_DATE DATE ;
1330 X_LAST_UPDATED_BY NUMBER ;
1331 X_LAST_UPDATE_LOGIN NUMBER ;
1332 X_REQUEST_ID NUMBER;
1333 X_PROGRAM_ID NUMBER;
1334 X_PROGRAM_APPLICATION_ID NUMBER;
1335 X_PROGRAM_UPDATE_DATE DATE;
1336 L_MODE VARCHAR2(1);
1337 begin
1338 L_MODE := NVL(X_MODE,'R');
1339 X_LAST_UPDATE_DATE := SYSDATE;
1340 if(L_MODE = 'I') then
1341 X_LAST_UPDATED_BY := 1;
1342 X_LAST_UPDATE_LOGIN := 0;
1343 elsif (L_MODE = 'R') then
1344 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1345 if X_LAST_UPDATED_BY is NULL then
1346 X_LAST_UPDATED_BY := -1;
1350 X_LAST_UPDATE_LOGIN := -1;
1347 end if;
1348 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1349 if X_LAST_UPDATE_LOGIN is NULL then
1351 end if;
1352 else
1353 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1354 IGS_GE_MSG_STACK.ADD;
1355 app_exception.raise_exception;
1356 end if;
1357 Before_DML(
1358 p_action=>'UPDATE',
1359 x_rowid=>X_ROWID,
1360 x_test_results_id=>X_TEST_RESULTS_ID,
1361 x_person_id=>X_PERSON_ID,
1362 x_admission_test_type=>X_ADMISSION_TEST_TYPE,
1363 x_test_date=>X_TEST_DATE,
1364 x_score_report_date=>X_SCORE_REPORT_DATE,
1365 x_edu_level_id=>X_EDU_LEVEL_ID,
1366 x_score_type=>X_SCORE_TYPE,
1367 x_score_source_id=>X_SCORE_SOURCE_ID,
1368 x_non_standard_admin=>X_NON_STANDARD_ADMIN,
1369 x_comp_test_score=>X_COMP_TEST_SCORE,
1370 x_special_code=>X_SPECIAL_CODE,
1371 x_registration_number=>X_REGISTRATION_NUMBER,
1372 x_grade_id=>X_GRADE_ID,
1373 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
1374 x_attribute1=>X_ATTRIBUTE1,
1375 x_attribute2=>X_ATTRIBUTE2,
1376 x_attribute3=>X_ATTRIBUTE3,
1377 x_attribute4=>X_ATTRIBUTE4,
1378 x_attribute5=>X_ATTRIBUTE5,
1379 x_attribute6=>X_ATTRIBUTE6,
1380 x_attribute7=>X_ATTRIBUTE7,
1381 x_attribute8=>X_ATTRIBUTE8,
1382 x_attribute9=>X_ATTRIBUTE9,
1383 x_attribute10=>X_ATTRIBUTE10,
1384 x_attribute11=>X_ATTRIBUTE11,
1385 x_attribute12=>X_ATTRIBUTE12,
1386 x_attribute13=>X_ATTRIBUTE13,
1387 x_attribute14=>X_ATTRIBUTE14,
1388 x_attribute15=>X_ATTRIBUTE15,
1389 x_attribute16=>X_ATTRIBUTE16,
1390 x_attribute17=>X_ATTRIBUTE17,
1391 x_attribute18=>X_ATTRIBUTE18,
1392 x_attribute19=>X_ATTRIBUTE19,
1393 x_attribute20=>X_ATTRIBUTE20,
1394 x_creation_date=>X_LAST_UPDATE_DATE,
1395 x_created_by=>X_LAST_UPDATED_BY,
1396 x_last_update_date=>X_LAST_UPDATE_DATE,
1397 x_last_updated_by=>X_LAST_UPDATED_BY,
1398 x_last_update_login=>X_LAST_UPDATE_LOGIN,
1399 x_active_ind => X_ACTIVE_IND);
1400
1401 if (L_MODE = 'R') then
1402 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1403 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1404 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1405 if (X_REQUEST_ID = -1) then
1406 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
1407 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
1408 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
1409 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
1410 else
1411 X_PROGRAM_UPDATE_DATE := SYSDATE;
1412 end if;
1413 end if;
1414
1415 IF (x_mode = 'S') THEN
1416 igs_sc_gen_001.set_ctx('R');
1417 END IF;
1418 update IGS_AD_TEST_RESULTS set
1419 PERSON_ID = NEW_REFERENCES.PERSON_ID,
1420 ADMISSION_TEST_TYPE = NEW_REFERENCES.ADMISSION_TEST_TYPE,
1421 TEST_DATE = NEW_REFERENCES.TEST_DATE,
1422 SCORE_REPORT_DATE = NEW_REFERENCES.SCORE_REPORT_DATE,
1423 EDU_LEVEL_ID = NEW_REFERENCES.EDU_LEVEL_ID,
1424 SCORE_TYPE = NEW_REFERENCES.SCORE_TYPE,
1425 SCORE_SOURCE_ID = NEW_REFERENCES.SCORE_SOURCE_ID,
1426 NON_STANDARD_ADMIN = NEW_REFERENCES.NON_STANDARD_ADMIN,
1427 COMP_TEST_SCORE = NEW_REFERENCES.COMP_TEST_SCORE,
1428 SPECIAL_CODE = NEW_REFERENCES.SPECIAL_CODE,
1429 REGISTRATION_NUMBER = NEW_REFERENCES.REGISTRATION_NUMBER,
1430 GRADE_ID = NEW_REFERENCES.GRADE_ID,
1431 ATTRIBUTE_CATEGORY = NEW_REFERENCES.ATTRIBUTE_CATEGORY,
1432 ATTRIBUTE1 = NEW_REFERENCES.ATTRIBUTE1,
1433 ATTRIBUTE2 = NEW_REFERENCES.ATTRIBUTE2,
1434 ATTRIBUTE3 = NEW_REFERENCES.ATTRIBUTE3,
1435 ATTRIBUTE4 = NEW_REFERENCES.ATTRIBUTE4,
1436 ATTRIBUTE5 = NEW_REFERENCES.ATTRIBUTE5,
1437 ATTRIBUTE6 = NEW_REFERENCES.ATTRIBUTE6,
1438 ATTRIBUTE7 = NEW_REFERENCES.ATTRIBUTE7,
1439 ATTRIBUTE8 = NEW_REFERENCES.ATTRIBUTE8,
1440 ATTRIBUTE9 = NEW_REFERENCES.ATTRIBUTE9,
1441 ATTRIBUTE10 = NEW_REFERENCES.ATTRIBUTE10,
1442 ATTRIBUTE11 = NEW_REFERENCES.ATTRIBUTE11,
1443 ATTRIBUTE12 = NEW_REFERENCES.ATTRIBUTE12,
1444 ATTRIBUTE13 = NEW_REFERENCES.ATTRIBUTE13,
1445 ATTRIBUTE14 = NEW_REFERENCES.ATTRIBUTE14,
1446 ATTRIBUTE15 = NEW_REFERENCES.ATTRIBUTE15,
1447 ATTRIBUTE16 = NEW_REFERENCES.ATTRIBUTE16,
1448 ATTRIBUTE17 = NEW_REFERENCES.ATTRIBUTE17,
1449 ATTRIBUTE18 = NEW_REFERENCES.ATTRIBUTE18,
1450 ATTRIBUTE19 = NEW_REFERENCES.ATTRIBUTE19,
1451 ATTRIBUTE20 = NEW_REFERENCES.ATTRIBUTE20,
1452 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1453 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1454 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1455 REQUEST_ID = X_REQUEST_ID,
1456 PROGRAM_ID = X_PROGRAM_ID,
1457 PROGRAM_APPLICATION_ID = PROGRAM_APPLICATION_ID,
1458 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
1459 ACTIVE_IND = NEW_REFERENCES.ACTIVE_IND
1460 where ROWID = X_ROWID;
1461 if (sql%notfound) then
1462 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1463 igs_ge_msg_stack.add;
1464 igs_sc_gen_001.unset_ctx('R');
1465 app_exception.raise_exception;
1466 end if;
1467 IF (x_mode = 'S') THEN
1468 igs_sc_gen_001.unset_ctx('R');
1469 END IF;
1470
1471
1472 After_DML (
1473 p_action => 'UPDATE' ,
1474 x_rowid => X_ROWID
1475 );
1476 EXCEPTION
1477 WHEN OTHERS THEN
1478 IF (SQLCODE = (-28115)) THEN
1479 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1480 fnd_message.set_token ('ERR_CD', SQLCODE);
1481 igs_ge_msg_stack.add;
1482 igs_sc_gen_001.unset_ctx('R');
1483 app_exception.raise_exception;
1484 ELSE
1485 igs_sc_gen_001.unset_ctx('R');
1486 RAISE;
1487 END IF;
1488
1489 end UPDATE_ROW;
1490 procedure ADD_ROW (
1491 X_ROWID in out NOCOPY VARCHAR2,
1492 x_TEST_RESULTS_ID IN OUT NOCOPY NUMBER,
1493 x_PERSON_ID IN NUMBER,
1494 x_ADMISSION_TEST_TYPE IN VARCHAR2,
1495 x_TEST_DATE IN DATE,
1496 x_SCORE_REPORT_DATE IN DATE,
1497 x_EDU_LEVEL_ID IN NUMBER,
1498 x_SCORE_TYPE IN VARCHAR2,
1499 x_SCORE_SOURCE_ID IN NUMBER,
1500 x_NON_STANDARD_ADMIN IN VARCHAR2,
1501 x_COMP_TEST_SCORE IN NUMBER,
1502 x_SPECIAL_CODE IN VARCHAR2,
1503 x_REGISTRATION_NUMBER IN VARCHAR2,
1504 x_GRADE_ID IN NUMBER,
1505 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
1506 x_ATTRIBUTE1 IN VARCHAR2,
1507 x_ATTRIBUTE2 IN VARCHAR2,
1508 x_ATTRIBUTE3 IN VARCHAR2,
1509 x_ATTRIBUTE4 IN VARCHAR2,
1510 x_ATTRIBUTE5 IN VARCHAR2,
1511 x_ATTRIBUTE6 IN VARCHAR2,
1512 x_ATTRIBUTE7 IN VARCHAR2,
1513 x_ATTRIBUTE8 IN VARCHAR2,
1514 x_ATTRIBUTE9 IN VARCHAR2,
1515 x_ATTRIBUTE10 IN VARCHAR2,
1516 x_ATTRIBUTE11 IN VARCHAR2,
1517 x_ATTRIBUTE12 IN VARCHAR2,
1518 x_ATTRIBUTE13 IN VARCHAR2,
1519 x_ATTRIBUTE14 IN VARCHAR2,
1520 x_ATTRIBUTE15 IN VARCHAR2,
1521 x_ATTRIBUTE16 IN VARCHAR2,
1522 x_ATTRIBUTE17 IN VARCHAR2,
1523 x_ATTRIBUTE18 IN VARCHAR2,
1524 x_ATTRIBUTE19 IN VARCHAR2,
1525 x_ATTRIBUTE20 IN VARCHAR2,
1526 X_MODE in VARCHAR2,
1527 x_ACTIVE_IND IN VARCHAR2
1528 ) AS
1529 /*************************************************************
1530 Created By :samaresh.in
1531 Date Created By : 15-May-2000
1532 Purpose :
1533 Know limitations, enhancements or remarks
1534 Change History
1535 Who When What
1536 (reverse chronological order - newest change first)
1537 RAVISHAR Feb,25 2005 Removed the default value of X_MODE parameter from
1538 body of this package for bug 4163319
1539 GSCC standard says that default value should be
1540 present only in specification
1541 ***************************************************************/
1542
1543 cursor c1 is select ROWID from IGS_AD_TEST_RESULTS
1544 where TEST_RESULTS_ID= X_TEST_RESULTS_ID
1545 ;
1546 L_MODE VARCHAR2(1);
1547 begin
1548 L_MODE := NVL(X_MODE,'R');
1549 open c1;
1550 fetch c1 into X_ROWID;
1551 if (c1%notfound) then
1552 close c1;
1553 INSERT_ROW (
1554 X_ROWID,
1555 X_TEST_RESULTS_ID,
1556 X_PERSON_ID,
1557 X_ADMISSION_TEST_TYPE,
1558 X_TEST_DATE,
1559 X_SCORE_REPORT_DATE,
1560 X_EDU_LEVEL_ID,
1561 X_SCORE_TYPE,
1562 X_SCORE_SOURCE_ID,
1563 X_NON_STANDARD_ADMIN,
1564 X_COMP_TEST_SCORE,
1565 X_SPECIAL_CODE,
1566 X_REGISTRATION_NUMBER,
1567 X_GRADE_ID,
1568 X_ATTRIBUTE_CATEGORY,
1569 X_ATTRIBUTE1,
1570 X_ATTRIBUTE2,
1571 X_ATTRIBUTE3,
1572 X_ATTRIBUTE4,
1573 X_ATTRIBUTE5,
1574 X_ATTRIBUTE6,
1575 X_ATTRIBUTE7,
1576 X_ATTRIBUTE8,
1577 X_ATTRIBUTE9,
1578 X_ATTRIBUTE10,
1579 X_ATTRIBUTE11,
1580 X_ATTRIBUTE12,
1581 X_ATTRIBUTE13,
1582 X_ATTRIBUTE14,
1583 X_ATTRIBUTE15,
1584 X_ATTRIBUTE16,
1585 X_ATTRIBUTE17,
1586 X_ATTRIBUTE18,
1587 X_ATTRIBUTE19,
1588 X_ATTRIBUTE20,
1589 L_MODE,
1590 X_ACTIVE_IND);
1591 return;
1592 end if;
1593 close c1;
1594 UPDATE_ROW (
1595 X_ROWID,
1596 X_TEST_RESULTS_ID,
1597 X_PERSON_ID,
1598 X_ADMISSION_TEST_TYPE,
1599 X_TEST_DATE,
1600 X_SCORE_REPORT_DATE,
1601 X_EDU_LEVEL_ID,
1602 X_SCORE_TYPE,
1603 X_SCORE_SOURCE_ID,
1604 X_NON_STANDARD_ADMIN,
1605 X_COMP_TEST_SCORE,
1606 X_SPECIAL_CODE,
1607 X_REGISTRATION_NUMBER,
1608 X_GRADE_ID,
1609 X_ATTRIBUTE_CATEGORY,
1610 X_ATTRIBUTE1,
1611 X_ATTRIBUTE2,
1612 X_ATTRIBUTE3,
1613 X_ATTRIBUTE4,
1614 X_ATTRIBUTE5,
1615 X_ATTRIBUTE6,
1616 X_ATTRIBUTE7,
1617 X_ATTRIBUTE8,
1618 X_ATTRIBUTE9,
1619 X_ATTRIBUTE10,
1620 X_ATTRIBUTE11,
1621 X_ATTRIBUTE12,
1622 X_ATTRIBUTE13,
1623 X_ATTRIBUTE14,
1624 X_ATTRIBUTE15,
1625 X_ATTRIBUTE16,
1626 X_ATTRIBUTE17,
1627 X_ATTRIBUTE18,
1628 X_ATTRIBUTE19,
1629 X_ATTRIBUTE20,
1630 L_MODE,
1631 X_ACTIVE_IND );
1632 end ADD_ROW;
1633 procedure DELETE_ROW (
1634 X_ROWID in VARCHAR2,
1635 x_mode IN VARCHAR2
1636 ) AS
1637 /*************************************************************
1638 Created By :samaresh.in
1639 Date Created By : 15-May-2000
1640 Purpose :
1641 Know limitations, enhancements or remarks
1642 Change History
1643 Who When What
1644
1645 (reverse chronological order - newest change first)
1646 ***************************************************************/
1647
1648 begin
1649 Before_DML (
1650 p_action => 'DELETE',
1651 x_rowid => X_ROWID
1652 );
1653 IF (x_mode = 'S') THEN
1654 igs_sc_gen_001.set_ctx('R');
1655 END IF;
1656 delete from IGS_AD_TEST_RESULTS
1657 where ROWID = X_ROWID;
1658 if (sql%notfound) then
1659 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1660 igs_ge_msg_stack.add;
1661 igs_sc_gen_001.unset_ctx('R');
1662 app_exception.raise_exception;
1663 end if;
1664 IF (x_mode = 'S') THEN
1665 igs_sc_gen_001.unset_ctx('R');
1666 END IF;
1667
1668 After_DML (
1669 p_action => 'DELETE',
1670 x_rowid => X_ROWID
1671 );
1672 end DELETE_ROW;
1673 END igs_ad_test_results_pkg;