1 PACKAGE BODY igs_ad_tst_rslt_dtls_pkg AS
2 /* $Header: IGSAI80B.pls 120.4 2005/08/22 04:45:30 appldev ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ad_tst_rslt_dtls%RowType;
5 new_references igs_ad_tst_rslt_dtls%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_tst_rslt_dtls_id IN NUMBER DEFAULT NULL,
11 x_test_results_id IN NUMBER DEFAULT NULL,
12 x_test_segment_id IN NUMBER DEFAULT NULL,
13 x_test_score IN NUMBER DEFAULT NULL,
14 x_percentile IN NUMBER DEFAULT NULL,
15 x_national_percentile IN NUMBER DEFAULT NULL,
16 x_state_percentile IN NUMBER DEFAULT NULL,
17 x_percentile_year_rank IN NUMBER DEFAULT NULL,
18 x_score_band_lower IN NUMBER DEFAULT NULL,
19 x_score_band_upper IN NUMBER DEFAULT NULL,
20 x_irregularity_code_id IN NUMBER DEFAULT NULL,
21 x_attribute_category IN VARCHAR2 DEFAULT NULL,
22 x_attribute1 IN VARCHAR2 DEFAULT NULL,
23 x_attribute2 IN VARCHAR2 DEFAULT NULL,
24 x_attribute3 IN VARCHAR2 DEFAULT NULL,
25 x_attribute4 IN VARCHAR2 DEFAULT NULL,
26 x_attribute5 IN VARCHAR2 DEFAULT NULL,
27 x_attribute6 IN VARCHAR2 DEFAULT NULL,
28 x_attribute7 IN VARCHAR2 DEFAULT NULL,
29 x_attribute8 IN VARCHAR2 DEFAULT NULL,
30 x_attribute9 IN VARCHAR2 DEFAULT NULL,
31 x_attribute10 IN VARCHAR2 DEFAULT NULL,
32 x_attribute11 IN VARCHAR2 DEFAULT NULL,
33 x_attribute12 IN VARCHAR2 DEFAULT NULL,
34 x_attribute13 IN VARCHAR2 DEFAULT NULL,
35 x_attribute14 IN VARCHAR2 DEFAULT NULL,
36 x_attribute15 IN VARCHAR2 DEFAULT NULL,
37 x_attribute16 IN VARCHAR2 DEFAULT NULL,
38 x_attribute17 IN VARCHAR2 DEFAULT NULL,
39 x_attribute18 IN VARCHAR2 DEFAULT NULL,
40 x_attribute19 IN VARCHAR2 DEFAULT NULL,
41 x_attribute20 IN VARCHAR2 DEFAULT NULL,
42 x_creation_date IN DATE DEFAULT NULL,
43 x_created_by IN NUMBER DEFAULT NULL,
44 x_last_update_date IN DATE DEFAULT NULL,
45 x_last_updated_by IN NUMBER DEFAULT NULL,
46 x_last_update_login IN NUMBER DEFAULT NULL
47 ) AS
48
49 /*************************************************************
50 Created By :
51 Date Created By :
52 Purpose :
53 Know limitations, enhancements or remarks
54 Change History
55 Who When What
56
57 (reverse chronological order - newest change first)
58 ***************************************************************/
59
60 CURSOR cur_old_ref_values IS
61 SELECT *
62 FROM IGS_AD_TST_RSLT_DTLS
63 WHERE rowid = x_rowid;
64
65 BEGIN
66
67 l_rowid := x_rowid;
68
69 -- Code for setting the Old and New Reference Values.
70 -- Populate Old Values.
71 Open cur_old_ref_values;
72 Fetch cur_old_ref_values INTO old_references;
73 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
74 Close cur_old_ref_values;
75 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
76 IGS_GE_MSG_STACK.ADD;
77 App_Exception.Raise_Exception;
78 Return;
79 END IF;
80 Close cur_old_ref_values;
81
82 -- Populate New Values.
83 new_references.tst_rslt_dtls_id := x_tst_rslt_dtls_id;
84 new_references.test_results_id := x_test_results_id;
85 new_references.test_segment_id := x_test_segment_id;
86 new_references.test_score := x_test_score;
87 new_references.percentile := x_percentile;
88 new_references.national_percentile := x_national_percentile;
89 new_references.state_percentile := x_state_percentile;
90 new_references.percentile_year_rank := x_percentile_year_rank;
91 new_references.score_band_lower := x_score_band_lower;
92 new_references.score_band_upper := x_score_band_upper;
93 new_references.irregularity_code_id := x_irregularity_code_id;
94 new_references.attribute_category := x_attribute_category;
95 new_references.attribute1 := x_attribute1;
96 new_references.attribute2 := x_attribute2;
97 new_references.attribute3 := x_attribute3;
98 new_references.attribute4 := x_attribute4;
99 new_references.attribute5 := x_attribute5;
100 new_references.attribute6 := x_attribute6;
101 new_references.attribute7 := x_attribute7;
102 new_references.attribute8 := x_attribute8;
103 new_references.attribute9 := x_attribute9;
104 new_references.attribute10 := x_attribute10;
105 new_references.attribute11 := x_attribute11;
106 new_references.attribute12 := x_attribute12;
107 new_references.attribute13 := x_attribute13;
108 new_references.attribute14 := x_attribute14;
109 new_references.attribute15 := x_attribute15;
110 new_references.attribute16 := x_attribute16;
111 new_references.attribute17 := x_attribute17;
112 new_references.attribute18 := x_attribute18;
113 new_references.attribute19 := x_attribute19;
114 new_references.attribute20 := x_attribute20;
115 IF (p_action = 'UPDATE') THEN
116 new_references.creation_date := old_references.creation_date;
117 new_references.created_by := old_references.created_by;
118 ELSE
119 new_references.creation_date := x_creation_date;
120 new_references.created_by := x_created_by;
121 END IF;
122 new_references.last_update_date := x_last_update_date;
123 new_references.last_updated_by := x_last_updated_by;
124 new_references.last_update_login := x_last_update_login;
125
126 END Set_Column_Values;
127
128 PROCEDURE Check_Constraints (
129 Column_Name IN VARCHAR2 DEFAULT NULL,
130 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
131 /*************************************************************
132 Created By :
133 Date Created By :
134 Purpose :
135 Know limitations, enhancements or remarks
136 Change History
137 Who When What
138
139 (reverse chronological order - newest change first)
140 ***************************************************************/
141
142 BEGIN
143
144 IF column_name IS NULL THEN
145 NULL;
146 ELSIF UPPER(column_name) = 'STATE_PERCENTILE' THEN
147 new_references.state_percentile := IGS_GE_NUMBER.TO_NUM(column_value);
148 ELSIF UPPER(column_name) = 'PERCENTILE_YEAR_RANK' THEN
149 new_references.percentile_year_rank := IGS_GE_NUMBER.TO_NUM(column_value);
150 ELSIF UPPER(column_name) = 'SCORE_BAND_LOWER' THEN
151 new_references.score_band_lower := IGS_GE_NUMBER.TO_NUM(column_value);
152 ELSIF UPPER(column_name) = 'SCORE_BAND_UPPER' THEN
153 new_references.score_band_upper := IGS_GE_NUMBER.TO_NUM(column_value);
154 ELSIF UPPER(column_name) = 'NATIONAL_PERCENTILE' THEN
155 new_references.national_percentile := IGS_GE_NUMBER.TO_NUM(column_value);
156 ELSIF UPPER(column_name) = 'TEST_SCORE' THEN
157 new_references.test_score := IGS_GE_NUMBER.TO_NUM(column_value);
158 NULL;
159 END IF;
160
161
162
163 -- The following code checks for check constraints on the Columns.
164 IF Upper(Column_Name) = 'STATE_PERCENTILE' OR
165 Column_Name IS NULL THEN
166 IF NOT (new_references.state_percentile>=0 and new_references.state_percentile<=100) THEN
167 Fnd_Message.Set_Name('IGS','IGS_AD_PCTL_NOT_GT_100_OR_LT_0');
168 IGS_GE_MSG_STACK.ADD;
169 App_Exception.Raise_Exception;
170 END IF;
171 END IF;
172
173 -- The following code checks for check constraints on the Columns.
174 IF Upper(Column_Name) = 'PERCENTILE_YEAR_RANK' OR
175 Column_Name IS NULL THEN
176 IF NOT (new_references.percentile_year_rank>=0) THEN
177 Fnd_Message.Set_Name('IGS','IGS_AD_PCTLYR_RANK_NOT_LT_0');
178 IGS_GE_MSG_STACK.ADD;
179 App_Exception.Raise_Exception;
180 END IF;
181 END IF;
182
183 -- The following code checks for check constraints on the Columns.
184 IF Upper(Column_Name) = 'SCORE_BAND_LOWER' OR
185 Column_Name IS NULL THEN
186 IF NOT (new_references.score_band_lower>=0
187 OR new_references.score_band_lower IS NULL) THEN
188 Fnd_Message.Set_Name('IGS','IGS_AD_SB_NOT_LT_0');
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) = 'SCORE_BAND_UPPER' OR
196 Column_Name IS NULL THEN
197 IF NOT (new_references.score_band_upper>=0
198 OR new_references.score_band_upper IS NULL) THEN
199 Fnd_Message.Set_Name('IGS','IGS_AD_SB_NOT_LT_0');
200 IGS_GE_MSG_STACK.ADD;
201 App_Exception.Raise_Exception;
202 END IF;
203 END IF;
204
205 -- The following code checks for check constraints on the Columns.
206 IF Upper(Column_Name) = 'PERCENTILE' OR
207 Column_Name IS NULL THEN
208 IF NOT (new_references.percentile>=0 and new_references.percentile<=100) THEN
209 Fnd_Message.Set_Name('IGS','IGS_AD_PCTL_NOT_GT_100_OR_LT_0');
210 IGS_GE_MSG_STACK.ADD;
211 App_Exception.Raise_Exception;
212 END IF;
213 END IF;
214
215 -- The following code checks for check constraints on the Columns.
216 IF Upper(Column_Name) = 'NATIONAL_PERCENTILE' OR
217 Column_Name IS NULL THEN
218 IF NOT (new_references.national_percentile>=0 and new_references.national_percentile<=100) THEN
219 Fnd_Message.Set_Name('IGS','IGS_IGS_AD_PCTL_NOT_GT_100_OR_LT_0');
220 IGS_GE_MSG_STACK.ADD;
221 App_Exception.Raise_Exception;
222 END IF;
223 END IF;
224
225 -- The following code checks for check constraints on the Columns.
226 IF Upper(Column_Name) = 'TEST_SCORE' OR
227 Column_Name IS NULL THEN
228 IF NOT (new_references.test_score>=0) THEN
229 Fnd_Message.Set_Name('IGS','IGS_AD_SCORE_NOT_LT_ZERO');
230 IGS_GE_MSG_STACK.ADD;
231 App_Exception.Raise_Exception;
232 END IF;
233 END IF;
234
235
236 END Check_Constraints;
237
238 PROCEDURE Check_Uniqueness AS
239 /*************************************************************
240 Created By :
241 Date Created By :
242 Purpose :
243 Know limitations, enhancements or remarks
244 Change History
245 Who When What
246
247 (reverse chronological order - newest change first)
248 ***************************************************************/
249
250 begin
251 IF Get_Uk_For_Validation (
252 new_references.test_results_id
253 ,new_references.test_segment_id
254 ) THEN
255 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
256 IGS_GE_MSG_STACK.ADD;
257 app_exception.raise_exception;
258 END IF;
259 END Check_Uniqueness ;
260 PROCEDURE Check_Parent_Existance AS
261 /*************************************************************
262 Created By :
263 Date Created By :
264 Purpose :
265 Know limitations, enhancements or remarks
266 Change History
267 Who When What
268
269 (reverse chronological order - newest change first)
270 ***************************************************************/
271
272 BEGIN
273
274 IF (((old_references.irregularity_code_id = new_references.irregularity_code_id)) OR
275 ((new_references.irregularity_code_id IS NULL))) THEN
276 NULL;
277 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
278 new_references.irregularity_code_id,
279 'IRREGULARITY_CODE',
280 'N'
281 ) THEN
282 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
283 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_IRREGULARITY'));
284 IGS_GE_MSG_STACK.ADD;
285 App_Exception.Raise_Exception;
286 END IF;
287
288 IF (((old_references.test_results_id = new_references.test_results_id)) OR
289 ((new_references.test_results_id IS NULL))) THEN
290 NULL;
291 ELSIF NOT Igs_Ad_Test_Results_Pkg.Get_PK_For_Validation (
292 new_references.test_results_id
293 ) THEN
294 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
295 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_TEST_RESULT'));
296 IGS_GE_MSG_STACK.ADD;
297 App_Exception.Raise_Exception;
298 END IF;
299
300 IF (((old_references.test_segment_id = new_references.test_segment_id)) OR
301 ((new_references.test_segment_id IS NULL))) THEN
302 NULL;
303 ELSIF NOT Igs_Ad_Test_Segments_Pkg.Get_PK_For_Validation (
304 new_references.test_segment_id,
305 'N'
306 ) THEN
307 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
308 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_TEST_SEGMNT'));
309 IGS_GE_MSG_STACK.ADD;
310 App_Exception.Raise_Exception;
311 END IF;
312
313 END Check_Parent_Existance;
314
315 FUNCTION Get_PK_For_Validation (
316 x_tst_rslt_dtls_id IN NUMBER
317 ) RETURN BOOLEAN AS
318
319 /*************************************************************
320 Created By :
321 Date Created By :
322 Purpose :
323 Know limitations, enhancements or remarks
324 Change History
325 Who When What
326
327 (reverse chronological order - newest change first)
328 ***************************************************************/
329
330 CURSOR cur_rowid IS
331 SELECT rowid
332 FROM igs_ad_tst_rslt_dtls
333 WHERE tst_rslt_dtls_id = x_tst_rslt_dtls_id
334 FOR UPDATE NOWAIT;
335
336 lv_rowid cur_rowid%RowType;
337
338 BEGIN
339
340 Open cur_rowid;
341 Fetch cur_rowid INTO lv_rowid;
342 IF (cur_rowid%FOUND) THEN
343 Close cur_rowid;
344 Return(TRUE);
345 ELSE
346 Close cur_rowid;
347 Return(FALSE);
348 END IF;
349 END Get_PK_For_Validation;
350
351 FUNCTION Get_UK_For_Validation (
352 x_test_results_id IN NUMBER,
353 x_test_segment_id IN NUMBER
354 ) RETURN BOOLEAN AS
355
356 /*************************************************************
357 Created By :
358 Date Created By :
359 Purpose :
360 Know limitations, enhancements or remarks
361 Change History
362 Who When What
363
364 (reverse chronological order - newest change first)
365 ***************************************************************/
366
367 CURSOR cur_rowid IS
368 SELECT rowid
369 FROM igs_ad_tst_rslt_dtls
370 WHERE test_results_id = x_test_results_id
371 AND test_segment_id = x_test_segment_id and ((l_rowid is null) or (rowid <> l_rowid))
372
373 ;
374 lv_rowid cur_rowid%RowType;
375
376 BEGIN
377
378 Open cur_rowid;
379 Fetch cur_rowid INTO lv_rowid;
380 IF (cur_rowid%FOUND) THEN
381 Close cur_rowid;
382 return (true);
383 ELSE
384 close cur_rowid;
385 return(false);
386 END IF;
387 END Get_UK_For_Validation ;
388
389 PROCEDURE Get_FK_Igs_Ad_Code_Classes (
390 x_code_id IN NUMBER
391 ) AS
392
393 /*************************************************************
394 Created By :
395 Date Created By :
396 Purpose :
397 Know limitations, enhancements or remarks
398 Change History
399 Who When What
400
401 (reverse chronological order - newest change first)
402 ***************************************************************/
403
404 CURSOR cur_rowid IS
405 SELECT rowid
406 FROM igs_ad_tst_rslt_dtls
407 WHERE irregularity_code_id = x_code_id ;
408
409 lv_rowid cur_rowid%RowType;
410
411 BEGIN
412
413 Open cur_rowid;
414 Fetch cur_rowid INTO lv_rowid;
415 IF (cur_rowid%FOUND) THEN
416 Close cur_rowid;
417 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ATRD_ACDC_FK');
418 IGS_GE_MSG_STACK.ADD;
419 App_Exception.Raise_Exception;
420 Return;
421 END IF;
422 Close cur_rowid;
423
424 END Get_FK_Igs_Ad_Code_Classes;
425
426 PROCEDURE Get_FK_Igs_Ad_Test_Results (
427 x_test_results_id IN NUMBER
428 ) AS
429
430 /*************************************************************
431 Created By :
432 Date Created By :
433 Purpose :
434 Know limitations, enhancements or remarks
435 Change History
436 Who When What
437
438 (reverse chronological order - newest change first)
439 ***************************************************************/
440
441 CURSOR cur_rowid IS
442 SELECT rowid
443 FROM igs_ad_tst_rslt_dtls
444 WHERE test_results_id = x_test_results_id ;
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 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ATRD_ATR_FK');
455 IGS_GE_MSG_STACK.ADD;
456 App_Exception.Raise_Exception;
457 Return;
458 END IF;
459 Close cur_rowid;
460
461 END Get_FK_Igs_Ad_Test_Results;
462
463 PROCEDURE Get_FK_Igs_Ad_Test_Segments (
464 x_test_segment_id IN NUMBER
465 ) AS
466
467 /*************************************************************
468 Created By :
469 Date Created By :
470 Purpose :
471 Know limitations, enhancements or remarks
472 Change History
473 Who When What
474
475 (reverse chronological order - newest change first)
476 ***************************************************************/
477
478 CURSOR cur_rowid IS
479 SELECT rowid
480 FROM igs_ad_tst_rslt_dtls
481 WHERE test_segment_id = x_test_segment_id ;
482
483 lv_rowid cur_rowid%RowType;
484
485 BEGIN
486
487 Open cur_rowid;
488 Fetch cur_rowid INTO lv_rowid;
489 IF (cur_rowid%FOUND) THEN
490 Close cur_rowid;
491 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ATRD_ATS_FK');
492 IGS_GE_MSG_STACK.ADD;
493 App_Exception.Raise_Exception;
494 Return;
495 END IF;
496 Close cur_rowid;
497
498 END Get_FK_Igs_Ad_Test_Segments;
499
500 PROCEDURE Check_Child_Existance AS
501 BEGIN
502 -- Next part of code has been added as per bug# 2401170
503 -- Start of new code.
504 igs_av_stnd_unit_pkg.get_fk_igs_ad_tst_rslt_dtls (
505 old_references.tst_rslt_dtls_id
506 );
507 igs_av_stnd_unit_lvl_pkg.get_fk_igs_ad_tst_rslt_dtls (
508 old_references.tst_rslt_dtls_id
509 );
510 -- End of new code. Bug# 2401170
511 END Check_Child_Existance;
512
513 PROCEDURE update_parent_composite_score(p_test_results_id IN NUMBER)
514 AS
515
516 CURSOR c_calc_comp_score(cp_test_results_id IN NUMBER) IS
517 SELECT SUM(test_score)
518 FROM IGS_AD_TST_RSLT_DTLS A,
519 IGS_AD_TEST_SEGMENTS B
520 WHERE A.TEST_results_ID = cp_test_results_id
521 AND A.TEST_SEGMENT_ID = B.test_segment_id
522 AND B.INCLUDE_IN_COMP_SCORE = 'Y';
523
524 l_comp_score NUMBER;
525 l_test_result_id NUMBER(15);
526
527 CURSOR c_get_test_score_record(cp_test_results_id IN NUMBER) IS
528 SELECT rowid,A.*
529 FROM IGS_AD_TEST_RESULTS A
530 WHERE test_results_id = cp_test_results_id
531 FOR UPDATE NOWAIT;
532
533 l_test_result c_get_test_score_record%ROWTYPE;
534 e_resource_busy_exception EXCEPTION;
535 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
536 testResult VARCHAR2(100);
537 BEGIN
538
539 OPEN c_calc_comp_score(p_test_results_id);
540 FETCH c_calc_comp_score INTO l_comp_score;
541 CLOSE c_calc_comp_score;
542
543 OPEN c_get_test_score_record(p_test_results_id);
544 FETCH c_get_test_score_record INTO l_test_result;
545 CLOSE c_get_test_score_record;
546
547 Igs_Ad_Test_Results_Pkg.Update_Row (
548 X_Mode => 'R',
549 X_RowId => l_test_result.ROWID,
550 X_Test_Results_Id => l_test_result.Test_Results_Id,
551 X_Person_Id => l_test_result.Person_Id,
552 X_Admission_Test_Type => l_test_result.Admission_Test_Type,
553 X_Test_Date => l_test_result.Test_Date,
554 X_Score_Report_Date => l_test_result.Score_Report_Date,
555 X_Edu_Level_Id => l_test_result.Edu_Level_Id,
556 X_Score_Type => l_test_result.Score_Type,
557 X_Score_Source_Id => l_test_result.Score_Source_Id,
558 X_Non_Standard_Admin => l_test_result.Non_Standard_Admin,
559 X_Comp_Test_Score => l_comp_score,
560 X_Special_Code => l_test_result.Special_Code,
561 X_Registration_Number => l_test_result.Registration_Number,
562 X_Grade_Id => l_test_result.Grade_Id,
563 X_Attribute_Category => l_test_result.Attribute_Category,
564 X_Attribute1 => l_test_result.Attribute1,
565 X_Attribute2 => l_test_result.Attribute2,
566 X_Attribute3 => l_test_result.Attribute3,
567 X_Attribute4 => l_test_result.Attribute4,
568 X_Attribute5 => l_test_result.Attribute5,
569 X_Attribute6 => l_test_result.Attribute6,
570 X_Attribute7 => l_test_result.Attribute7,
571 X_Attribute8 => l_test_result.Attribute8,
572 X_Attribute9 => l_test_result.Attribute9,
573 X_Attribute10 => l_test_result.Attribute10,
574 X_Attribute11 => l_test_result.Attribute11,
575 X_Attribute12 => l_test_result.Attribute12,
576 X_Attribute13 => l_test_result.Attribute13,
577 X_Attribute14 => l_test_result.Attribute14,
578 X_Attribute15 => l_test_result.Attribute15,
579 X_Attribute16 => l_test_result.Attribute16,
580 X_Attribute17 => l_test_result.Attribute17,
581 X_Attribute18 => l_test_result.Attribute18,
582 X_Attribute19 => l_test_result.Attribute19,
583 X_Attribute20 => l_test_result.Attribute20,
584 X_Active_Ind => l_test_result.Active_Ind
585 );
586
587 EXCEPTION
588 WHEN e_resource_busy_exception THEN
589 fnd_message.set_name ('IGS', 'IGS_AD_TSTRESULT');
590 testResult := fnd_message.get();
591 fnd_message.set_name ('IGS', 'IGS_PR_LOCK_DETECTED');
592 fnd_message.set_token('RECORD',testResult);
593 igs_ge_msg_stack.add;
594 App_Exception.Raise_Exception;
595 END update_parent_composite_score;
596
597 PROCEDURE validate_record(p_error OUT NOCOPY BOOLEAN,
598 p_message OUT NOCOPY VARCHAR2,
599 p_entity1 OUT NOCOPY VARCHAR2,
600 p_entity2 OUT NOCOPY VARCHAR2,
601 p_entity3 OUT NOCOPY VARCHAR2)
602 IS
603 CURSOR c_test_ind_cur(cp_test_segment_id igs_ad_test_segments.test_segment_id%TYPE) IS
604 SELECT
605 include_in_comp_score,
606 score_ind,
607 percentile_ind,
608 irregularity_code_ind,
609 percentile_year_rank_ind,
610 national_percentile_ind,
611 state_percentile_ind,
612 score_band_upper_ind,
613 score_band_lower_ind
614 FROM
615 IGS_AD_TEST_SEGMENTS
616 WHERE
617 test_segment_id = cp_test_segment_id ;
618
619
620 CURSOR c_admission_test_type IS
621 SELECT admission_test_type
622 FROM igs_ad_test_results
623 WHERE test_results_id = new_references.test_results_id;
624
625 CURSOR c_val_test_seg_cur (cp_admission_test_type igs_ad_test_results.admission_test_type%TYPE)IS
626 SELECT 'x'
627 FROM
628 igs_ad_test_segments
629 WHERE
630 test_segment_id = new_references.test_segment_id
631 AND ADMISSION_TEST_TYPE = cp_admission_test_type
632 AND closed_ind = 'N';
633
634 CURSOR
635 c_test_score_range_cur
636 IS
637 SELECT
638 min_score,
639 max_score,
640 description
641 FROM
642 igs_ad_test_segments
643 WHERE
644 TEST_SEGMENT_ID = new_references.test_segment_id ;
645
646 c_test_ind_rec c_test_ind_cur%ROWTYPE;
647 c_val_test_seg_rec c_val_test_seg_cur%ROWTYPE;
648 l_admission_test_type igs_ad_test_results.admission_test_type%TYPE;
649
650 BEGIN
651
652 l_admission_test_type := NULL;
653 OPEN c_admission_test_type;
654 FETCH c_admission_test_type INTO l_admission_test_type;
655 CLOSE c_admission_test_type;
656
657 IF l_admission_test_type IS NOT NULL THEN
658 OPEN c_val_test_seg_cur(l_admission_test_type);
659 FETCH c_val_test_seg_cur INTO c_val_test_seg_rec;
660 IF c_val_test_seg_cur%NOTFOUND THEN
661 p_message := 'IGS_AD_TST_TYP_SEG_COM_NOT_EXT';
662 p_error := TRUE;
663 CLOSE c_val_test_seg_cur;
664 RETURN;
665 END IF;
666 CLOSE c_val_test_seg_cur;
667 ELSE
668 p_message := 'IGS_AD_TST_TYP_SEG_COM_NOT_EXT';
669 p_error := TRUE;
670 RETURN;
671 END IF;
672
673
674 IF (((old_references.test_score IS NOT NULL AND new_references.test_score IS NULL)) OR
675 ((old_references.test_score IS NULL AND new_references.test_score IS NOT NULL)) OR
676 ((old_references.test_score <> new_references.test_score))) THEN
677 FOR c_test_score_range_rec IN c_test_score_range_cur
678 LOOP
679 IF (new_references.test_score < c_test_score_range_rec.min_score OR
680 new_references.test_score > c_test_score_range_rec.max_score) THEN
681 p_error := TRUE;
682 p_message := 'IGS_AD_OUT_OF_RANGE';
683 p_entity1 := c_test_score_range_rec.min_score;
684 p_entity2 := c_test_score_range_rec.max_score;
685 p_entity3 := c_test_score_range_rec.description;
686 RETURN;
687 END IF;
688 END LOOP;
689 END IF;
690
691 OPEN c_test_ind_cur(new_references.test_segment_id);
692 FETCH c_test_ind_cur INTO c_test_ind_rec;
693 CLOSE c_test_ind_cur;
694
695 IF (((old_references.test_score IS NOT NULL AND new_references.test_score IS NULL)) OR
696 ((old_references.test_score IS NULL AND new_references.test_score IS NOT NULL)) OR
697 ((old_references.test_score <> new_references.test_score))) THEN
698 IF c_test_ind_rec.score_ind = 'N' AND new_references.test_score IS NOT NULL THEN
699 p_error := TRUE;
700 p_message :='IGS_AD_NOT_APL_ATTR_TST_SEG';
701 p_entity1 := 'Test Score';
702 RETURN;
703 END IF;
704 END IF;
705
706 IF (((old_references.percentile IS NOT NULL AND new_references.percentile IS NULL)) OR
707 ((old_references.percentile IS NULL AND new_references.percentile IS NOT NULL)) OR
708 ((old_references.percentile <> new_references.percentile))) THEN
709 IF c_test_ind_rec.percentile_ind = 'N' AND new_references.percentile IS NOT NULL THEN
710 p_error := TRUE;
711 p_message :='IGS_AD_NOT_APL_ATTR_TST_SEG';
712 p_entity1 := 'Percentile';
713 RETURN;
714 END IF;
715 END IF;
716
717 IF (((old_references.irregularity_code_id IS NOT NULL AND new_references.irregularity_code_id IS NULL)) OR
718 ((old_references.irregularity_code_id IS NULL AND new_references.irregularity_code_id IS NOT NULL)) OR
719 ((old_references.irregularity_code_id <> new_references.irregularity_code_id))) THEN
720 IF c_test_ind_rec.irregularity_code_ind = 'N' AND new_references.irregularity_code_id IS NOT NULL THEN
721 p_error := TRUE;
722 p_message :='IGS_AD_NOT_APL_ATTR_TST_SEG';
723 p_entity1 := 'Rrregularity Code';
724 RETURN;
725 END IF;
726 END IF;
727
728
729 IF (((old_references.percentile_year_rank IS NOT NULL AND new_references.percentile_year_rank IS NULL)) OR
730 ((old_references.percentile_year_rank IS NULL AND new_references.percentile_year_rank IS NOT NULL)) OR
731 ((old_references.percentile_year_rank <> new_references.percentile_year_rank))) THEN
732 IF c_test_ind_rec.percentile_year_rank_ind = 'N' AND new_references.percentile_year_rank IS NOT NULL THEN
733 p_error := TRUE;
734 p_message :='IGS_AD_NOT_APL_ATTR_TST_SEG';
735 p_entity1 := 'Percentile Year Rank';
736 RETURN;
737 END IF;
738 END IF;
739
740 IF (((old_references.national_percentile IS NOT NULL AND new_references.national_percentile IS NULL)) OR
741 ((old_references.national_percentile IS NULL AND new_references.national_percentile IS NOT NULL)) OR
742 ((old_references.national_percentile <> new_references.national_percentile)) ) THEN
743 IF c_test_ind_rec.national_percentile_ind = 'N' AND new_references.national_percentile IS NOT NULL THEN
744 p_error := TRUE;
745 p_message :='IGS_AD_NOT_APL_ATTR_TST_SEG';
746 p_entity1 := 'National Percentile';
747 RETURN;
748 END IF;
749 END IF;
750
751
752 IF (((old_references.state_percentile IS NOT NULL AND new_references.state_percentile IS NULL)) OR
753 ((old_references.state_percentile IS NULL AND new_references.state_percentile IS NOT NULL)) OR
754 ((old_references.state_percentile <> new_references.state_percentile)) ) THEN
755 IF c_test_ind_rec.state_percentile_ind = 'N' AND new_references.state_percentile IS NOT NULL THEN
756 p_error := TRUE;
757 p_message :='IGS_AD_NOT_APL_ATTR_TST_SEG';
758 p_entity1 := 'State Percentile';
759 RETURN;
760 END IF;
761 END IF;
762
763 IF (((old_references.score_band_upper IS NOT NULL AND new_references.score_band_upper IS NULL)) OR
764 ((old_references.score_band_upper IS NULL AND new_references.score_band_upper IS NOT NULL)) OR
765 ((old_references.score_band_upper <> new_references.score_band_upper))) THEN
766
767 IF c_test_ind_rec.score_band_upper_ind = 'N' AND new_references.score_band_upper IS NOT NULL THEN
768 p_error := TRUE;
769 p_message :='IGS_AD_NOT_APL_ATTR_TST_SEG';
770 p_entity1 := 'Score Band Upper';
771 RETURN;
772 END IF;
773 END IF;
774
775 IF (((old_references.score_band_lower IS NOT NULL AND new_references.score_band_lower IS NULL)) OR
776 ((old_references.score_band_lower IS NULL AND new_references.score_band_lower IS NOT NULL)) OR
777 ((old_references.score_band_lower <> new_references.score_band_lower))) THEN
778 IF c_test_ind_rec.score_band_lower_ind = 'N' AND new_references.score_band_lower IS NOT NULL THEN
779 p_error := TRUE;
780 p_message :='IGS_AD_NOT_APL_ATTR_TST_SEG';
781 p_entity1 := 'Score Band Lower';
782 RETURN;
783 END IF;
784 END IF;
785
786 p_error := FALSE;
787 p_entity1 := NULL;
788 p_entity2 := NULL;
789 p_entity3 := NULL;
790 p_message := NULL;
791 END validate_record;
792
793
794 PROCEDURE Before_DML (
795 p_action IN VARCHAR2,
796 x_rowid IN VARCHAR2 DEFAULT NULL,
797 x_tst_rslt_dtls_id IN NUMBER DEFAULT NULL,
798 x_test_results_id IN NUMBER DEFAULT NULL,
799 x_test_segment_id IN NUMBER DEFAULT NULL,
800 x_test_score IN NUMBER DEFAULT NULL,
801 x_percentile IN NUMBER DEFAULT NULL,
802 x_national_percentile IN NUMBER DEFAULT NULL,
803 x_state_percentile IN NUMBER DEFAULT NULL,
804 x_percentile_year_rank IN NUMBER DEFAULT NULL,
805 x_score_band_lower IN NUMBER DEFAULT NULL,
806 x_score_band_upper IN NUMBER DEFAULT NULL,
807 x_irregularity_code_id IN NUMBER DEFAULT NULL,
808 x_attribute_category IN VARCHAR2 DEFAULT NULL,
809 x_attribute1 IN VARCHAR2 DEFAULT NULL,
810 x_attribute2 IN VARCHAR2 DEFAULT NULL,
811 x_attribute3 IN VARCHAR2 DEFAULT NULL,
812 x_attribute4 IN VARCHAR2 DEFAULT NULL,
813 x_attribute5 IN VARCHAR2 DEFAULT NULL,
814 x_attribute6 IN VARCHAR2 DEFAULT NULL,
815 x_attribute7 IN VARCHAR2 DEFAULT NULL,
816 x_attribute8 IN VARCHAR2 DEFAULT NULL,
817 x_attribute9 IN VARCHAR2 DEFAULT NULL,
818 x_attribute10 IN VARCHAR2 DEFAULT NULL,
819 x_attribute11 IN VARCHAR2 DEFAULT NULL,
820 x_attribute12 IN VARCHAR2 DEFAULT NULL,
821 x_attribute13 IN VARCHAR2 DEFAULT NULL,
822 x_attribute14 IN VARCHAR2 DEFAULT NULL,
823 x_attribute15 IN VARCHAR2 DEFAULT NULL,
824 x_attribute16 IN VARCHAR2 DEFAULT NULL,
825 x_attribute17 IN VARCHAR2 DEFAULT NULL,
826 x_attribute18 IN VARCHAR2 DEFAULT NULL,
827 x_attribute19 IN VARCHAR2 DEFAULT NULL,
828 x_attribute20 IN VARCHAR2 DEFAULT NULL,
829 x_creation_date IN DATE DEFAULT NULL,
830 x_created_by IN NUMBER DEFAULT NULL,
831 x_last_update_date IN DATE DEFAULT NULL,
832 x_last_updated_by IN NUMBER DEFAULT NULL,
833 x_last_update_login IN NUMBER DEFAULT NULL
834 ) AS
835 /*************************************************************
836 Created By :
837 Date Created By :
838 Purpose :
839 Know limitations, enhancements or remarks
840 Change History
841 Who When What
842
843 (reverse chronological order - newest change first)
844 ***************************************************************/
845 l_error BOOLEAN := FALSE;
846 l_message_name VARCHAR2(30);
847 l_entity1 VARCHAR2(100);
848 l_entity2 VARCHAR2(100);
849 l_entity3 VARCHAR2(100);
850
851 BEGIN
852
853 Set_Column_Values (
854 p_action,
855 x_rowid,
856 x_tst_rslt_dtls_id,
857 x_test_results_id,
858 x_test_segment_id,
859 x_test_score,
860 x_percentile,
861 x_national_percentile,
862 x_state_percentile,
863 x_percentile_year_rank,
864 x_score_band_lower,
865 x_score_band_upper,
866 x_irregularity_code_id,
867 x_attribute_category,
868 x_attribute1,
869 x_attribute2,
870 x_attribute3,
871 x_attribute4,
872 x_attribute5,
873 x_attribute6,
874 x_attribute7,
875 x_attribute8,
876 x_attribute9,
877 x_attribute10,
878 x_attribute11,
879 x_attribute12,
880 x_attribute13,
881 x_attribute14,
882 x_attribute15,
883 x_attribute16,
884 x_attribute17,
885 x_attribute18,
886 x_attribute19,
887 x_attribute20,
888 x_creation_date,
889 x_created_by,
890 x_last_update_date,
891 x_last_updated_by,
892 x_last_update_login
893 );
894
895 l_entity1 :=NULL;
896 l_message_name :=NULL;
897 l_error := FALSE;
898 l_entity2 := NULL;
899
900 IF (p_action = 'INSERT') THEN
901 -- Call all the procedures related to Before Insert.
902 Null;
903 IF Get_Pk_For_Validation(
904 new_references.tst_rslt_dtls_id) THEN
905 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
906 IGS_GE_MSG_STACK.ADD;
907 App_Exception.Raise_Exception;
908 END IF;
909
910 validate_record(l_error,l_message_name,l_entity1,l_entity2,l_entity3);
911
912 IF l_error = TRUE THEN
913 IF l_message_name = 'IGS_AD_NOT_APL_ATTR_TST_SEG' THEN
914 FND_MESSAGE.SET_NAME('IGS','IGS_AD_NOT_APL_ATTR_TST_SEG');
915 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',l_entity1);
916 ELSIF l_message_name = 'IGS_AD_TST_TYP_SEG_COM_NOT_EXT' THEN
917 FND_MESSAGE.SET_NAME('IGS','IGS_AD_TST_TYP_SEG_COM_NOT_EXT');
918 ELSIF l_message_name = 'IGS_AD_OUT_OF_RANGE' THEN
919 FND_MESSAGE.SET_NAME('IGS','IGS_AD_OUT_OF_RANGE');
920 FND_MESSAGE.SET_TOKEN('TEST_SEGMENT',l_entity3);
921 FND_MESSAGE.SET_TOKEN('MIN_SCORE',l_entity1);
922 FND_MESSAGE.SET_TOKEN('MAX_SCORE',l_entity2);
923 END IF;
924 IGS_GE_MSG_STACK.ADD;
925 App_Exception.Raise_Exception;
926 END IF;
927 Check_Uniqueness;
928 Check_Constraints;
929 Check_Parent_Existance;
930 ELSIF (p_action = 'UPDATE') THEN
931 -- Call all the procedures related to Before Update.
932 Null;
933
934 validate_record(l_error,l_message_name,l_entity1,l_entity2,l_entity3);
935 IF l_error = TRUE THEN
936 IF l_message_name = 'IGS_AD_NOT_APL_ATTR_TST_SEG' THEN
937 FND_MESSAGE.SET_NAME('IGS','IGS_AD_NOT_APL_ATTR_TST_SEG');
938 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',l_entity1);
939 ELSIF l_message_name = 'IGS_AD_TST_TYP_SEG_COM_NOT_EXT' THEN
940 FND_MESSAGE.SET_NAME('IGS','IGS_AD_TST_TYP_SEG_COM_NOT_EXT');
941 ELSIF l_message_name = 'IGS_AD_OUT_OF_RANGE' THEN
942 FND_MESSAGE.SET_NAME('IGS','IGS_AD_OUT_OF_RANGE');
943 FND_MESSAGE.SET_TOKEN('MIN_SCORE',l_entity1);
944 FND_MESSAGE.SET_TOKEN('MAX_SCORE',l_entity2);
945 FND_MESSAGE.SET_TOKEN('TEST_SEGMENT',l_entity3);
946 END IF;
947 IGS_GE_MSG_STACK.ADD;
948 App_Exception.Raise_Exception;
949 END IF;
950
951 Check_Uniqueness;
952 Check_Constraints;
953 Check_Parent_Existance;
954 ELSIF (p_action = 'DELETE') THEN
955 -- Call all the procedures related to Before Delete.
956 Check_Child_Existance;
957 ELSIF (p_action = 'VALIDATE_INSERT') THEN
958 -- Call all the procedures related to Before Insert.
959 IF Get_PK_For_Validation (
960 new_references.tst_rslt_dtls_id) THEN
961 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
962 IGS_GE_MSG_STACK.ADD;
963 App_Exception.Raise_Exception;
964 END IF;
965
966 Check_Uniqueness;
967 Check_Constraints;
968 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
969
970 Check_Uniqueness;
971 Check_Constraints;
972 ELSIF (p_action = 'VALIDATE_DELETE') THEN
973 Check_Child_Existance;
974 END IF;
975
976 END Before_DML;
977
978 PROCEDURE After_DML (
979 p_action IN VARCHAR2,
980 x_rowid IN VARCHAR2
981 ) AS
982 -------------------------------------------------------------------------------
983 -- Bug ID : 1818617
984 -- who when what
985 -- sjadhav jun 28,2001 this procedure is modified to trigger
986 -- a Concurrent Request (IGFAPJ10) which
987 -- will create a new record in IGF To
988 -- Do table
989 -------------------------------------------------------------------------------
990 /*************************************************************
991 Created By :
992 Date Created By :
993 Purpose :
994 Know limitations, enhancements or remarks
995 Change History
996 Who When What
997
998 (reverse chronological order - newest change first)
999 ***************************************************************/
1000 CURSOR c_person_id IS
1001 SELECT person_id
1002 FROM IGS_AD_TEST_RESULTS
1003 WHERE TEST_RESULTS_ID = new_references.test_results_id;
1004
1005 CURSOR test_comb_score_cur(cp_test_segment_id igs_ad_test_segments.test_segment_id%TYPE) IS
1006 SELECT include_in_comp_score
1007 FROM igs_ad_test_segments
1008 WHERE test_segment_id = cp_test_segment_id;
1009
1010 test_comb_score_rec test_comb_score_cur%ROWTYPE;
1011
1012 l_person_id hz_parties.party_id%TYPE;
1013 BEGIN
1014 l_rowid := x_rowid;
1015 OPEN c_person_id;
1016 FETCH c_person_id INTO l_person_id;
1017 CLOSE c_person_id;
1018
1019
1020 IF (p_action = 'INSERT') THEN
1021 -- Call all the procedures related to After Insert.
1022 OPEN test_comb_score_cur(new_references.test_segment_id);
1023 FETCH test_comb_score_cur INTO test_comb_score_rec;
1024 CLOSE test_comb_score_cur;
1025
1026 IF (test_comb_score_rec.include_in_comp_score = 'Y' AND new_references.test_score IS NOT NULL) THEN
1027 update_parent_composite_score(new_references.test_results_id);
1028 END IF;
1029
1030 --Raise the buisness event
1031 igs_ad_wf_001.TESTSEG_CRT_EVENT
1032 (
1033 P_TEST_RESULTS_ID => new_references.test_results_id,
1034 P_TST_RSLT_DTLS_ID => new_references.tst_rslt_dtls_id,
1035 P_TEST_SEGMENT_ID => new_references.test_segment_id,
1036 P_PERSON_ID => l_person_id
1037 );
1038 ELSIF (p_action = 'UPDATE') THEN
1039 -- Call all the procedures related to After Update.
1040
1041 IF (NVL(new_references.test_score,-1) <> NVL(old_references.test_score,-1)) THEN
1042 update_parent_composite_score(new_references.test_results_id);
1043 END IF;
1044
1045 --Raise the buisness event
1046 igs_ad_wf_001.TESTSEG_UPD_EVENT
1047 (
1048 P_TEST_RESULTS_ID => new_references.test_results_id,
1049 P_TST_RSLT_DTLS_ID => new_references.tst_rslt_dtls_id,
1050 P_TEST_SEGMENT_ID => new_references.test_segment_id,
1051 P_PERSON_ID => l_person_id,
1052 P_TEST_SCORE_NEW => new_references.test_score,
1053 P_TEST_SCORE_OLD => old_references.test_score
1054 );
1055
1056 ELSIF (p_action = 'DELETE') THEN
1057 -- Call all the procedures related to After Delete.
1058 IF (old_references.test_score IS NOT NULL) THEN
1059 update_parent_composite_score(old_references.test_results_id);
1060 END IF;
1061 END IF;
1062
1063 l_rowid:=NULL;
1064
1065 END After_DML;
1066
1067 procedure INSERT_ROW (
1068 X_ROWID in out NOCOPY VARCHAR2,
1069 x_TST_RSLT_DTLS_ID IN OUT NOCOPY NUMBER,
1070 x_TEST_RESULTS_ID IN NUMBER,
1071 x_TEST_SEGMENT_ID IN NUMBER,
1072 x_TEST_SCORE IN NUMBER,
1073 x_PERCENTILE IN NUMBER,
1074 x_NATIONAL_PERCENTILE IN NUMBER,
1075 x_STATE_PERCENTILE IN NUMBER,
1076 x_PERCENTILE_YEAR_RANK IN NUMBER,
1077 x_SCORE_BAND_LOWER IN NUMBER,
1078 x_SCORE_BAND_UPPER IN NUMBER,
1079 x_IRREGULARITY_CODE_ID IN NUMBER,
1080 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
1081 x_ATTRIBUTE1 IN VARCHAR2,
1082 x_ATTRIBUTE2 IN VARCHAR2,
1083 x_ATTRIBUTE3 IN VARCHAR2,
1084 x_ATTRIBUTE4 IN VARCHAR2,
1085 x_ATTRIBUTE5 IN VARCHAR2,
1086 x_ATTRIBUTE6 IN VARCHAR2,
1087 x_ATTRIBUTE7 IN VARCHAR2,
1088 x_ATTRIBUTE8 IN VARCHAR2,
1089 x_ATTRIBUTE9 IN VARCHAR2,
1090 x_ATTRIBUTE10 IN VARCHAR2,
1091 x_ATTRIBUTE11 IN VARCHAR2,
1092 x_ATTRIBUTE12 IN VARCHAR2,
1093 x_ATTRIBUTE13 IN VARCHAR2,
1094 x_ATTRIBUTE14 IN VARCHAR2,
1095 x_ATTRIBUTE15 IN VARCHAR2,
1096 x_ATTRIBUTE16 IN VARCHAR2,
1097 x_ATTRIBUTE17 IN VARCHAR2,
1098 x_ATTRIBUTE18 IN VARCHAR2,
1099 x_ATTRIBUTE19 IN VARCHAR2,
1100 x_ATTRIBUTE20 IN VARCHAR2,
1101 X_MODE in VARCHAR2
1102 ) AS
1103 /*************************************************************
1104 Created By :
1105 Date Created By :
1106 Purpose :
1107 Know limitations, enhancements or remarks
1108 Change History
1109 Who When What
1110 RAVISHAR Feb,25 2005 Removed the default value of X_MODE parameter from
1111 body of this package for bug 4163319
1112 GSCC standard says that default value should be
1113 present only in specification
1114
1115 (reverse chronological order - newest change first)
1116 ***************************************************************/
1117
1118 cursor C is select ROWID from IGS_AD_TST_RSLT_DTLS
1119 where TST_RSLT_DTLS_ID= X_TST_RSLT_DTLS_ID
1120 ;
1121 X_LAST_UPDATE_DATE DATE ;
1122 X_LAST_UPDATED_BY NUMBER ;
1123 X_LAST_UPDATE_LOGIN NUMBER ;
1124 X_REQUEST_ID NUMBER;
1125 X_PROGRAM_ID NUMBER;
1126 X_PROGRAM_APPLICATION_ID NUMBER;
1127 X_PROGRAM_UPDATE_DATE DATE;
1128 L_MODE VARCHAR2(1);
1129 begin
1130 L_MODE := NVL(X_MODE,'R');
1131 X_LAST_UPDATE_DATE := SYSDATE;
1132 if(L_MODE = 'I') then
1133 X_LAST_UPDATED_BY := 1;
1134 X_LAST_UPDATE_LOGIN := 0;
1135 elsif (L_MODE IN ('R','S')) then
1136 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1137 if X_LAST_UPDATED_BY is NULL then
1138 X_LAST_UPDATED_BY := -1;
1139 end if;
1140 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1141 if X_LAST_UPDATE_LOGIN is NULL then
1142 X_LAST_UPDATE_LOGIN := -1;
1143 end if;
1144 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1145 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1146 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1147 if (X_REQUEST_ID = -1) then
1148 X_REQUEST_ID := NULL;
1149 X_PROGRAM_ID := NULL;
1150 X_PROGRAM_APPLICATION_ID := NULL;
1151 X_PROGRAM_UPDATE_DATE := NULL;
1152 else
1153 X_PROGRAM_UPDATE_DATE := SYSDATE;
1154 end if;
1155 else
1156 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1157 IGS_GE_MSG_STACK.ADD;
1158 app_exception.raise_exception;
1159 end if;
1160
1161 X_TST_RSLT_DTLS_ID := -1;
1162 Before_DML(
1163 p_action=>'INSERT',
1164 x_rowid=>X_ROWID,
1165 x_tst_rslt_dtls_id=>X_TST_RSLT_DTLS_ID,
1166 x_test_results_id=>X_TEST_RESULTS_ID,
1167 x_test_segment_id=>X_TEST_SEGMENT_ID,
1168 x_test_score=>X_TEST_SCORE,
1169 x_percentile=>X_PERCENTILE,
1170 x_national_percentile=>X_NATIONAL_PERCENTILE,
1171 x_state_percentile=>X_STATE_PERCENTILE,
1172 x_percentile_year_rank=>X_PERCENTILE_YEAR_RANK,
1173 x_score_band_lower=>X_SCORE_BAND_LOWER,
1174 x_score_band_upper=>X_SCORE_BAND_UPPER,
1175 x_irregularity_code_id=>X_IRREGULARITY_CODE_ID,
1176 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
1177 x_attribute1=>X_ATTRIBUTE1,
1178 x_attribute2=>X_ATTRIBUTE2,
1179 x_attribute3=>X_ATTRIBUTE3,
1180 x_attribute4=>X_ATTRIBUTE4,
1181 x_attribute5=>X_ATTRIBUTE5,
1182 x_attribute6=>X_ATTRIBUTE6,
1183 x_attribute7=>X_ATTRIBUTE7,
1184 x_attribute8=>X_ATTRIBUTE8,
1185 x_attribute9=>X_ATTRIBUTE9,
1186 x_attribute10=>X_ATTRIBUTE10,
1187 x_attribute11=>X_ATTRIBUTE11,
1188 x_attribute12=>X_ATTRIBUTE12,
1189 x_attribute13=>X_ATTRIBUTE13,
1190 x_attribute14=>X_ATTRIBUTE14,
1191 x_attribute15=>X_ATTRIBUTE15,
1192 x_attribute16=>X_ATTRIBUTE16,
1193 x_attribute17=>X_ATTRIBUTE17,
1194 x_attribute18=>X_ATTRIBUTE18,
1195 x_attribute19=>X_ATTRIBUTE19,
1196 x_attribute20=>X_ATTRIBUTE20,
1197 x_creation_date=>X_LAST_UPDATE_DATE,
1198 x_created_by=>X_LAST_UPDATED_BY,
1199 x_last_update_date=>X_LAST_UPDATE_DATE,
1200 x_last_updated_by=>X_LAST_UPDATED_BY,
1201 x_last_update_login=>X_LAST_UPDATE_LOGIN);
1202 IF (x_mode = 'S') THEN
1203 igs_sc_gen_001.set_ctx('R');
1204 END IF;
1205 insert into IGS_AD_TST_RSLT_DTLS (
1206 TST_RSLT_DTLS_ID
1207 ,TEST_RESULTS_ID
1208 ,TEST_SEGMENT_ID
1209 ,TEST_SCORE
1210 ,PERCENTILE
1211 ,NATIONAL_PERCENTILE
1212 ,STATE_PERCENTILE
1213 ,PERCENTILE_YEAR_RANK
1214 ,SCORE_BAND_LOWER
1215 ,SCORE_BAND_UPPER
1216 ,IRREGULARITY_CODE_ID
1217 ,ATTRIBUTE_CATEGORY
1218 ,ATTRIBUTE1
1219 ,ATTRIBUTE2
1220 ,ATTRIBUTE3
1221 ,ATTRIBUTE4
1222 ,ATTRIBUTE5
1223 ,ATTRIBUTE6
1224 ,ATTRIBUTE7
1225 ,ATTRIBUTE8
1226 ,ATTRIBUTE9
1227 ,ATTRIBUTE10
1228 ,ATTRIBUTE11
1229 ,ATTRIBUTE12
1230 ,ATTRIBUTE13
1231 ,ATTRIBUTE14
1232 ,ATTRIBUTE15
1233 ,ATTRIBUTE16
1234 ,ATTRIBUTE17
1235 ,ATTRIBUTE18
1236 ,ATTRIBUTE19
1237 ,ATTRIBUTE20
1238 ,CREATION_DATE
1239 ,CREATED_BY
1240 ,LAST_UPDATE_DATE
1241 ,LAST_UPDATED_BY
1242 ,LAST_UPDATE_LOGIN
1243 ,REQUEST_ID
1244 ,PROGRAM_ID
1245 ,PROGRAM_APPLICATION_ID
1246 ,PROGRAM_UPDATE_DATE
1247 ) values (
1248 IGS_AD_TST_RSLT_DTLS_S.NEXTVAL
1249 ,NEW_REFERENCES.TEST_RESULTS_ID
1250 ,NEW_REFERENCES.TEST_SEGMENT_ID
1251 ,NEW_REFERENCES.TEST_SCORE
1252 ,NEW_REFERENCES.PERCENTILE
1253 ,NEW_REFERENCES.NATIONAL_PERCENTILE
1254 ,NEW_REFERENCES.STATE_PERCENTILE
1255 ,NEW_REFERENCES.PERCENTILE_YEAR_RANK
1256 ,NEW_REFERENCES.SCORE_BAND_LOWER
1257 ,NEW_REFERENCES.SCORE_BAND_UPPER
1258 ,NEW_REFERENCES.IRREGULARITY_CODE_ID
1259 ,NEW_REFERENCES.ATTRIBUTE_CATEGORY
1260 ,NEW_REFERENCES.ATTRIBUTE1
1261 ,NEW_REFERENCES.ATTRIBUTE2
1262 ,NEW_REFERENCES.ATTRIBUTE3
1263 ,NEW_REFERENCES.ATTRIBUTE4
1264 ,NEW_REFERENCES.ATTRIBUTE5
1265 ,NEW_REFERENCES.ATTRIBUTE6
1266 ,NEW_REFERENCES.ATTRIBUTE7
1267 ,NEW_REFERENCES.ATTRIBUTE8
1268 ,NEW_REFERENCES.ATTRIBUTE9
1269 ,NEW_REFERENCES.ATTRIBUTE10
1270 ,NEW_REFERENCES.ATTRIBUTE11
1271 ,NEW_REFERENCES.ATTRIBUTE12
1272 ,NEW_REFERENCES.ATTRIBUTE13
1273 ,NEW_REFERENCES.ATTRIBUTE14
1274 ,NEW_REFERENCES.ATTRIBUTE15
1275 ,NEW_REFERENCES.ATTRIBUTE16
1276 ,NEW_REFERENCES.ATTRIBUTE17
1277 ,NEW_REFERENCES.ATTRIBUTE18
1278 ,NEW_REFERENCES.ATTRIBUTE19
1279 ,NEW_REFERENCES.ATTRIBUTE20
1280 ,X_LAST_UPDATE_DATE
1281 ,X_LAST_UPDATED_BY
1282 ,X_LAST_UPDATE_DATE
1283 ,X_LAST_UPDATED_BY
1284 ,X_LAST_UPDATE_LOGIN
1285 ,X_REQUEST_ID
1286 ,X_PROGRAM_ID
1287 ,X_PROGRAM_APPLICATION_ID
1288 ,X_PROGRAM_UPDATE_DATE
1289 )RETURNING TST_RSLT_DTLS_ID INTO X_TST_RSLT_DTLS_ID ;
1290 IF (x_mode = 'S') THEN
1291 igs_sc_gen_001.unset_ctx('R');
1292 END IF;
1293
1294
1295 NEW_REFERENCES.TST_RSLT_DTLS_ID := X_TST_RSLT_DTLS_ID;
1296
1297 open c;
1298 fetch c into X_ROWID;
1299 if (c%notfound) then
1300 close c;
1301 raise no_data_found;
1302 end if;
1303 close c;
1304 After_DML (
1305 p_action => 'INSERT' ,
1306 x_rowid => X_ROWID
1307 );
1308 EXCEPTION
1309 WHEN OTHERS THEN
1310 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
1311 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1312 fnd_message.set_token ('ERR_CD', SQLCODE);
1313 igs_ge_msg_stack.add;
1314 igs_sc_gen_001.unset_ctx('R');
1315 app_exception.raise_exception;
1316 ELSE
1317 igs_sc_gen_001.unset_ctx('R');
1318 RAISE;
1319 END IF;
1320
1321 end INSERT_ROW;
1322 procedure LOCK_ROW (
1323 X_ROWID in VARCHAR2,
1324 x_TST_RSLT_DTLS_ID IN NUMBER,
1325 x_TEST_RESULTS_ID IN NUMBER,
1326 x_TEST_SEGMENT_ID IN NUMBER,
1327 x_TEST_SCORE IN NUMBER,
1328 x_PERCENTILE IN NUMBER,
1329 x_NATIONAL_PERCENTILE IN NUMBER,
1330 x_STATE_PERCENTILE IN NUMBER,
1331 x_PERCENTILE_YEAR_RANK IN NUMBER,
1332 x_SCORE_BAND_LOWER IN NUMBER,
1333 x_SCORE_BAND_UPPER IN NUMBER,
1334 x_IRREGULARITY_CODE_ID IN NUMBER,
1335 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
1336 x_ATTRIBUTE1 IN VARCHAR2,
1337 x_ATTRIBUTE2 IN VARCHAR2,
1338 x_ATTRIBUTE3 IN VARCHAR2,
1339 x_ATTRIBUTE4 IN VARCHAR2,
1340 x_ATTRIBUTE5 IN VARCHAR2,
1341 x_ATTRIBUTE6 IN VARCHAR2,
1342 x_ATTRIBUTE7 IN VARCHAR2,
1343 x_ATTRIBUTE8 IN VARCHAR2,
1344 x_ATTRIBUTE9 IN VARCHAR2,
1345 x_ATTRIBUTE10 IN VARCHAR2,
1346 x_ATTRIBUTE11 IN VARCHAR2,
1347 x_ATTRIBUTE12 IN VARCHAR2,
1348 x_ATTRIBUTE13 IN VARCHAR2,
1349 x_ATTRIBUTE14 IN VARCHAR2,
1350 x_ATTRIBUTE15 IN VARCHAR2,
1351 x_ATTRIBUTE16 IN VARCHAR2,
1352 x_ATTRIBUTE17 IN VARCHAR2,
1353 x_ATTRIBUTE18 IN VARCHAR2,
1354 x_ATTRIBUTE19 IN VARCHAR2,
1355 x_ATTRIBUTE20 IN VARCHAR2 ) AS
1356 /*************************************************************
1357 Created By :
1358 Date Created By :
1359 Purpose :
1360 Know limitations, enhancements or remarks
1361 Change History
1362 Who When What
1363
1364 (reverse chronological order - newest change first)
1365 ***************************************************************/
1366
1367 cursor c1 is select
1368 TEST_RESULTS_ID
1369 , TEST_SEGMENT_ID
1370 , TEST_SCORE
1371 , PERCENTILE
1372 , NATIONAL_PERCENTILE
1373 , STATE_PERCENTILE
1374 , PERCENTILE_YEAR_RANK
1375 , SCORE_BAND_LOWER
1376 , SCORE_BAND_UPPER
1377 , IRREGULARITY_CODE_ID
1378 , ATTRIBUTE_CATEGORY
1379 , ATTRIBUTE1
1380 , ATTRIBUTE2
1381 , ATTRIBUTE3
1382 , ATTRIBUTE4
1383 , ATTRIBUTE5
1384 , ATTRIBUTE6
1385 , ATTRIBUTE7
1386 , ATTRIBUTE8
1387 , ATTRIBUTE9
1388 , ATTRIBUTE10
1389 , ATTRIBUTE11
1390 , ATTRIBUTE12
1391 , ATTRIBUTE13
1392 , ATTRIBUTE14
1393 , ATTRIBUTE15
1394 , ATTRIBUTE16
1395 , ATTRIBUTE17
1396 , ATTRIBUTE18
1397 , ATTRIBUTE19
1398 , ATTRIBUTE20
1399 from IGS_AD_TST_RSLT_DTLS
1400 where ROWID = X_ROWID
1401 for update nowait;
1402 tlinfo c1%rowtype;
1403 begin
1404 open c1;
1405 fetch c1 into tlinfo;
1406 if (c1%notfound) then
1407 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1408 IGS_GE_MSG_STACK.ADD;
1409 close c1;
1410 app_exception.raise_exception;
1411 return;
1412 end if;
1413 close c1;
1414 if ( ( tlinfo.TEST_RESULTS_ID = X_TEST_RESULTS_ID)
1415 AND (tlinfo.TEST_SEGMENT_ID = X_TEST_SEGMENT_ID)
1416 AND ((tlinfo.TEST_SCORE = X_TEST_SCORE)
1417 OR ((tlinfo.TEST_SCORE is null)
1418 AND (X_TEST_SCORE is null)))
1419 AND ((tlinfo.PERCENTILE = X_PERCENTILE)
1420 OR ((tlinfo.PERCENTILE is null)
1421 AND (X_PERCENTILE is null)))
1422 AND ((tlinfo.NATIONAL_PERCENTILE = X_NATIONAL_PERCENTILE)
1423 OR ((tlinfo.NATIONAL_PERCENTILE is null)
1424 AND (X_NATIONAL_PERCENTILE is null)))
1425 AND ((tlinfo.STATE_PERCENTILE = X_STATE_PERCENTILE)
1426 OR ((tlinfo.STATE_PERCENTILE is null)
1427 AND (X_STATE_PERCENTILE is null)))
1428 AND ((tlinfo.PERCENTILE_YEAR_RANK = X_PERCENTILE_YEAR_RANK)
1429 OR ((tlinfo.PERCENTILE_YEAR_RANK is null)
1430 AND (X_PERCENTILE_YEAR_RANK is null)))
1431 AND ((tlinfo.SCORE_BAND_LOWER = X_SCORE_BAND_LOWER)
1432 OR ((tlinfo.SCORE_BAND_LOWER is null)
1433 AND (X_SCORE_BAND_LOWER is null)))
1434 AND ((tlinfo.SCORE_BAND_UPPER = X_SCORE_BAND_UPPER)
1435 OR ((tlinfo.SCORE_BAND_UPPER is null)
1436 AND (X_SCORE_BAND_UPPER is null)))
1437 AND ((tlinfo.IRREGULARITY_CODE_ID = X_IRREGULARITY_CODE_ID)
1438 OR ((tlinfo.IRREGULARITY_CODE_ID is null)
1439 AND (X_IRREGULARITY_CODE_ID is null)))
1440 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
1441 OR ((tlinfo.ATTRIBUTE_CATEGORY is null)
1442 AND (X_ATTRIBUTE_CATEGORY is null)))
1443 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
1444 OR ((tlinfo.ATTRIBUTE1 is null)
1445 AND (X_ATTRIBUTE1 is null)))
1446 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
1447 OR ((tlinfo.ATTRIBUTE2 is null)
1448 AND (X_ATTRIBUTE2 is null)))
1449 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
1450 OR ((tlinfo.ATTRIBUTE3 is null)
1451 AND (X_ATTRIBUTE3 is null)))
1452 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
1453 OR ((tlinfo.ATTRIBUTE4 is null)
1454 AND (X_ATTRIBUTE4 is null)))
1455 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
1456 OR ((tlinfo.ATTRIBUTE5 is null)
1457 AND (X_ATTRIBUTE5 is null)))
1458 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
1459 OR ((tlinfo.ATTRIBUTE6 is null)
1460 AND (X_ATTRIBUTE6 is null)))
1461 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
1462 OR ((tlinfo.ATTRIBUTE7 is null)
1463 AND (X_ATTRIBUTE7 is null)))
1464 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
1465 OR ((tlinfo.ATTRIBUTE8 is null)
1466 AND (X_ATTRIBUTE8 is null)))
1467 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
1468 OR ((tlinfo.ATTRIBUTE9 is null)
1469 AND (X_ATTRIBUTE9 is null)))
1470 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
1471 OR ((tlinfo.ATTRIBUTE10 is null)
1472 AND (X_ATTRIBUTE10 is null)))
1473 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
1474 OR ((tlinfo.ATTRIBUTE11 is null)
1475 AND (X_ATTRIBUTE11 is null)))
1476 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
1477 OR ((tlinfo.ATTRIBUTE12 is null)
1478 AND (X_ATTRIBUTE12 is null)))
1479 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
1480 OR ((tlinfo.ATTRIBUTE13 is null)
1481 AND (X_ATTRIBUTE13 is null)))
1482 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
1483 OR ((tlinfo.ATTRIBUTE14 is null)
1484 AND (X_ATTRIBUTE14 is null)))
1485 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
1486 OR ((tlinfo.ATTRIBUTE15 is null)
1487 AND (X_ATTRIBUTE15 is null)))
1488 AND ((tlinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
1489 OR ((tlinfo.ATTRIBUTE16 is null)
1490 AND (X_ATTRIBUTE16 is null)))
1491 AND ((tlinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
1492 OR ((tlinfo.ATTRIBUTE17 is null)
1493 AND (X_ATTRIBUTE17 is null)))
1494 AND ((tlinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
1495 OR ((tlinfo.ATTRIBUTE18 is null)
1496 AND (X_ATTRIBUTE18 is null)))
1497 AND ((tlinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
1498 OR ((tlinfo.ATTRIBUTE19 is null)
1499 AND (X_ATTRIBUTE19 is null)))
1500 AND ((tlinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
1501 OR ((tlinfo.ATTRIBUTE20 is null)
1502 AND (X_ATTRIBUTE20 is null)))
1503 ) then
1504 null;
1505 else
1506 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1507 IGS_GE_MSG_STACK.ADD;
1508 app_exception.raise_exception;
1509 end if;
1510 return;
1511 end LOCK_ROW;
1512 Procedure UPDATE_ROW (
1513 X_ROWID in VARCHAR2,
1514 x_TST_RSLT_DTLS_ID IN NUMBER,
1515 x_TEST_RESULTS_ID IN NUMBER,
1516 x_TEST_SEGMENT_ID IN NUMBER,
1517 x_TEST_SCORE IN NUMBER,
1518 x_PERCENTILE IN NUMBER,
1519 x_NATIONAL_PERCENTILE IN NUMBER,
1520 x_STATE_PERCENTILE IN NUMBER,
1521 x_PERCENTILE_YEAR_RANK IN NUMBER,
1522 x_SCORE_BAND_LOWER IN NUMBER,
1523 x_SCORE_BAND_UPPER IN NUMBER,
1524 x_IRREGULARITY_CODE_ID IN NUMBER,
1525 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
1526 x_ATTRIBUTE1 IN VARCHAR2,
1527 x_ATTRIBUTE2 IN VARCHAR2,
1528 x_ATTRIBUTE3 IN VARCHAR2,
1529 x_ATTRIBUTE4 IN VARCHAR2,
1530 x_ATTRIBUTE5 IN VARCHAR2,
1531 x_ATTRIBUTE6 IN VARCHAR2,
1532 x_ATTRIBUTE7 IN VARCHAR2,
1533 x_ATTRIBUTE8 IN VARCHAR2,
1534 x_ATTRIBUTE9 IN VARCHAR2,
1535 x_ATTRIBUTE10 IN VARCHAR2,
1536 x_ATTRIBUTE11 IN VARCHAR2,
1537 x_ATTRIBUTE12 IN VARCHAR2,
1538 x_ATTRIBUTE13 IN VARCHAR2,
1539 x_ATTRIBUTE14 IN VARCHAR2,
1540 x_ATTRIBUTE15 IN VARCHAR2,
1541 x_ATTRIBUTE16 IN VARCHAR2,
1542 x_ATTRIBUTE17 IN VARCHAR2,
1543 x_ATTRIBUTE18 IN VARCHAR2,
1544 x_ATTRIBUTE19 IN VARCHAR2,
1545 x_ATTRIBUTE20 IN VARCHAR2,
1546 X_MODE in VARCHAR2
1547 ) AS
1548 /*************************************************************
1549 Created By :
1550 Date Created By :
1551 Purpose :
1552 Know limitations, enhancements or remarks
1553 Change History
1554 Who When What
1555 RAVISHAR Feb,25 2005 Removed the default value of X_MODE parameter from
1556 body of this package for bug 4163319
1557 GSCC standard says that default value should be
1558 present only in specification
1559
1560 (reverse chronological order - newest change first)
1561 ***************************************************************/
1562
1563 X_LAST_UPDATE_DATE DATE ;
1564 X_LAST_UPDATED_BY NUMBER ;
1565 X_LAST_UPDATE_LOGIN NUMBER ;
1566 X_REQUEST_ID NUMBER;
1567 X_PROGRAM_ID NUMBER;
1568 X_PROGRAM_APPLICATION_ID NUMBER;
1569 X_PROGRAM_UPDATE_DATE DATE;
1570 L_MODE VARCHAR2(1);
1571 begin
1572 L_MODE := NVL(X_MODE,'R');
1573 X_LAST_UPDATE_DATE := SYSDATE;
1574 if(L_MODE = 'I') then
1575 X_LAST_UPDATED_BY := 1;
1576 X_LAST_UPDATE_LOGIN := 0;
1577 elsif (L_MODE IN ('R','S')) then
1578 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1579 if X_LAST_UPDATED_BY is NULL then
1580 X_LAST_UPDATED_BY := -1;
1581 end if;
1582 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1583 if X_LAST_UPDATE_LOGIN is NULL then
1584 X_LAST_UPDATE_LOGIN := -1;
1585 end if;
1586 else
1587 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1588 IGS_GE_MSG_STACK.ADD;
1589 app_exception.raise_exception;
1590 end if;
1591 Before_DML(
1592 p_action=>'UPDATE',
1593 x_rowid=>X_ROWID,
1594 x_tst_rslt_dtls_id=>X_TST_RSLT_DTLS_ID,
1595 x_test_results_id=>X_TEST_RESULTS_ID,
1596 x_test_segment_id=>X_TEST_SEGMENT_ID,
1597 x_test_score=>X_TEST_SCORE,
1598 x_percentile=>X_PERCENTILE,
1599 x_national_percentile=>X_NATIONAL_PERCENTILE,
1600 x_state_percentile=>X_STATE_PERCENTILE,
1601 x_percentile_year_rank=>X_PERCENTILE_YEAR_RANK,
1602 x_score_band_lower=>X_SCORE_BAND_LOWER,
1603 x_score_band_upper=>X_SCORE_BAND_UPPER,
1604 x_irregularity_code_id=>X_IRREGULARITY_CODE_ID,
1605 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
1606 x_attribute1=>X_ATTRIBUTE1,
1607 x_attribute2=>X_ATTRIBUTE2,
1608 x_attribute3=>X_ATTRIBUTE3,
1609 x_attribute4=>X_ATTRIBUTE4,
1610 x_attribute5=>X_ATTRIBUTE5,
1611 x_attribute6=>X_ATTRIBUTE6,
1612 x_attribute7=>X_ATTRIBUTE7,
1613 x_attribute8=>X_ATTRIBUTE8,
1614 x_attribute9=>X_ATTRIBUTE9,
1615 x_attribute10=>X_ATTRIBUTE10,
1616 x_attribute11=>X_ATTRIBUTE11,
1617 x_attribute12=>X_ATTRIBUTE12,
1618 x_attribute13=>X_ATTRIBUTE13,
1619 x_attribute14=>X_ATTRIBUTE14,
1620 x_attribute15=>X_ATTRIBUTE15,
1621 x_attribute16=>X_ATTRIBUTE16,
1622 x_attribute17=>X_ATTRIBUTE17,
1623 x_attribute18=>X_ATTRIBUTE18,
1624 x_attribute19=>X_ATTRIBUTE19,
1625 x_attribute20=>X_ATTRIBUTE20,
1626 x_creation_date=>X_LAST_UPDATE_DATE,
1627 x_created_by=>X_LAST_UPDATED_BY,
1628 x_last_update_date=>X_LAST_UPDATE_DATE,
1629 x_last_updated_by=>X_LAST_UPDATED_BY,
1630 x_last_update_login=>X_LAST_UPDATE_LOGIN);
1631
1632 if (L_MODE IN ('R','S')) then
1633 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1634 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1635 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1636 if (X_REQUEST_ID = -1) then
1637 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
1638 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
1639 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
1640 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
1641 else
1642 X_PROGRAM_UPDATE_DATE := SYSDATE;
1643 end if;
1644 end if;
1645
1646 IF (x_mode = 'S') THEN
1647 igs_sc_gen_001.set_ctx('R');
1648 END IF;
1649 update IGS_AD_TST_RSLT_DTLS set
1650 TEST_RESULTS_ID = NEW_REFERENCES.TEST_RESULTS_ID,
1651 TEST_SEGMENT_ID = NEW_REFERENCES.TEST_SEGMENT_ID,
1652 TEST_SCORE = NEW_REFERENCES.TEST_SCORE,
1653 PERCENTILE = NEW_REFERENCES.PERCENTILE,
1654 NATIONAL_PERCENTILE = NEW_REFERENCES.NATIONAL_PERCENTILE,
1655 STATE_PERCENTILE = NEW_REFERENCES.STATE_PERCENTILE,
1656 PERCENTILE_YEAR_RANK = NEW_REFERENCES.PERCENTILE_YEAR_RANK,
1657 SCORE_BAND_LOWER = NEW_REFERENCES.SCORE_BAND_LOWER,
1658 SCORE_BAND_UPPER = NEW_REFERENCES.SCORE_BAND_UPPER,
1659 IRREGULARITY_CODE_ID = NEW_REFERENCES.IRREGULARITY_CODE_ID,
1660 ATTRIBUTE_CATEGORY = NEW_REFERENCES.ATTRIBUTE_CATEGORY,
1661 ATTRIBUTE1 = NEW_REFERENCES.ATTRIBUTE1,
1662 ATTRIBUTE2 = NEW_REFERENCES.ATTRIBUTE2,
1663 ATTRIBUTE3 = NEW_REFERENCES.ATTRIBUTE3,
1664 ATTRIBUTE4 = NEW_REFERENCES.ATTRIBUTE4,
1665 ATTRIBUTE5 = NEW_REFERENCES.ATTRIBUTE5,
1666 ATTRIBUTE6 = NEW_REFERENCES.ATTRIBUTE6,
1667 ATTRIBUTE7 = NEW_REFERENCES.ATTRIBUTE7,
1668 ATTRIBUTE8 = NEW_REFERENCES.ATTRIBUTE8,
1669 ATTRIBUTE9 = NEW_REFERENCES.ATTRIBUTE9,
1670 ATTRIBUTE10 = NEW_REFERENCES.ATTRIBUTE10,
1671 ATTRIBUTE11 = NEW_REFERENCES.ATTRIBUTE11,
1672 ATTRIBUTE12 = NEW_REFERENCES.ATTRIBUTE12,
1673 ATTRIBUTE13 = NEW_REFERENCES.ATTRIBUTE13,
1674 ATTRIBUTE14 = NEW_REFERENCES.ATTRIBUTE14,
1675 ATTRIBUTE15 = NEW_REFERENCES.ATTRIBUTE15,
1676 ATTRIBUTE16 = NEW_REFERENCES.ATTRIBUTE16,
1677 ATTRIBUTE17 = NEW_REFERENCES.ATTRIBUTE17,
1678 ATTRIBUTE18 = NEW_REFERENCES.ATTRIBUTE18,
1679 ATTRIBUTE19 = NEW_REFERENCES.ATTRIBUTE19,
1680 ATTRIBUTE20 = NEW_REFERENCES.ATTRIBUTE20,
1681 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1682 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1683 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1684 , REQUEST_ID = X_REQUEST_ID,
1685 PROGRAM_ID = X_PROGRAM_ID,
1686 PROGRAM_APPLICATION_ID = PROGRAM_APPLICATION_ID,
1687 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
1688 where ROWID = X_ROWID;
1689 if (sql%notfound) then
1690 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1691 igs_ge_msg_stack.add;
1692 igs_sc_gen_001.unset_ctx('R');
1693 app_exception.raise_exception;
1694 end if;
1695 IF (x_mode = 'S') THEN
1696 igs_sc_gen_001.unset_ctx('R');
1697 END IF;
1698
1699
1700 After_DML (
1701 p_action => 'UPDATE',
1702 x_rowid => X_ROWID
1703
1704 );
1705 EXCEPTION
1706 WHEN OTHERS THEN
1707 IF (SQLCODE = (-28115)) THEN
1708 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1709 fnd_message.set_token ('ERR_CD', SQLCODE);
1710 igs_ge_msg_stack.add;
1711 igs_sc_gen_001.unset_ctx('R');
1712 app_exception.raise_exception;
1713 ELSE
1714 igs_sc_gen_001.unset_ctx('R');
1715 RAISE;
1716 END IF;
1717
1718 end UPDATE_ROW;
1719 procedure ADD_ROW (
1720 X_ROWID in out NOCOPY VARCHAR2,
1721 x_TST_RSLT_DTLS_ID IN OUT NOCOPY NUMBER,
1722 x_TEST_RESULTS_ID IN NUMBER,
1723 x_TEST_SEGMENT_ID IN NUMBER,
1724 x_TEST_SCORE IN NUMBER,
1725 x_PERCENTILE IN NUMBER,
1726 x_NATIONAL_PERCENTILE IN NUMBER,
1727 x_STATE_PERCENTILE IN NUMBER,
1728 x_PERCENTILE_YEAR_RANK IN NUMBER,
1729 x_SCORE_BAND_LOWER IN NUMBER,
1730 x_SCORE_BAND_UPPER IN NUMBER,
1731 x_IRREGULARITY_CODE_ID IN NUMBER,
1732 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
1733 x_ATTRIBUTE1 IN VARCHAR2,
1734 x_ATTRIBUTE2 IN VARCHAR2,
1735 x_ATTRIBUTE3 IN VARCHAR2,
1736 x_ATTRIBUTE4 IN VARCHAR2,
1737 x_ATTRIBUTE5 IN VARCHAR2,
1738 x_ATTRIBUTE6 IN VARCHAR2,
1739 x_ATTRIBUTE7 IN VARCHAR2,
1740 x_ATTRIBUTE8 IN VARCHAR2,
1741 x_ATTRIBUTE9 IN VARCHAR2,
1742 x_ATTRIBUTE10 IN VARCHAR2,
1743 x_ATTRIBUTE11 IN VARCHAR2,
1744 x_ATTRIBUTE12 IN VARCHAR2,
1745 x_ATTRIBUTE13 IN VARCHAR2,
1746 x_ATTRIBUTE14 IN VARCHAR2,
1747 x_ATTRIBUTE15 IN VARCHAR2,
1748 x_ATTRIBUTE16 IN VARCHAR2,
1749 x_ATTRIBUTE17 IN VARCHAR2,
1750 x_ATTRIBUTE18 IN VARCHAR2,
1751 x_ATTRIBUTE19 IN VARCHAR2,
1752 x_ATTRIBUTE20 IN VARCHAR2,
1753 X_MODE in VARCHAR2
1754 ) AS
1755 /*************************************************************
1756 Created By :
1757 Date Created By :
1758 Purpose :
1759 Know limitations, enhancements or remarks
1760 Change History
1761 Who When What
1762 RAVISHAR Feb,25 2005 Removed the default value of X_MODE parameter from
1763 body of this package for bug 4163319
1764 GSCC standard says that default value should be
1765 present only in specification
1766
1767 (reverse chronological order - newest change first)
1768 ***************************************************************/
1769
1770 cursor c1 is select ROWID from IGS_AD_TST_RSLT_DTLS
1771 where TST_RSLT_DTLS_ID= X_TST_RSLT_DTLS_ID
1772 ;
1773 L_MODE VARCHAR2(1);
1774 begin
1775 L_MODE := NVL(X_MODE,'R');
1776 open c1;
1777 fetch c1 into X_ROWID;
1778 if (c1%notfound) then
1779 close c1;
1780 INSERT_ROW (
1781 X_ROWID,
1782 X_TST_RSLT_DTLS_ID,
1783 X_TEST_RESULTS_ID,
1784 X_TEST_SEGMENT_ID,
1785 X_TEST_SCORE,
1786 X_PERCENTILE,
1787 X_NATIONAL_PERCENTILE,
1788 X_STATE_PERCENTILE,
1789 X_PERCENTILE_YEAR_RANK,
1790 X_SCORE_BAND_LOWER,
1791 X_SCORE_BAND_UPPER,
1792 X_IRREGULARITY_CODE_ID,
1793 X_ATTRIBUTE_CATEGORY,
1794 X_ATTRIBUTE1,
1795 X_ATTRIBUTE2,
1796 X_ATTRIBUTE3,
1797 X_ATTRIBUTE4,
1798 X_ATTRIBUTE5,
1799 X_ATTRIBUTE6,
1800 X_ATTRIBUTE7,
1801 X_ATTRIBUTE8,
1802 X_ATTRIBUTE9,
1803 X_ATTRIBUTE10,
1804 X_ATTRIBUTE11,
1805 X_ATTRIBUTE12,
1806 X_ATTRIBUTE13,
1807 X_ATTRIBUTE14,
1808 X_ATTRIBUTE15,
1809 X_ATTRIBUTE16,
1810 X_ATTRIBUTE17,
1811 X_ATTRIBUTE18,
1812 X_ATTRIBUTE19,
1813 X_ATTRIBUTE20,
1814 L_MODE );
1815 return;
1816 end if;
1817 close c1;
1818 UPDATE_ROW (
1819 X_ROWID,
1820 X_TST_RSLT_DTLS_ID,
1821 X_TEST_RESULTS_ID,
1822 X_TEST_SEGMENT_ID,
1823 X_TEST_SCORE,
1824 X_PERCENTILE,
1825 X_NATIONAL_PERCENTILE,
1826 X_STATE_PERCENTILE,
1827 X_PERCENTILE_YEAR_RANK,
1828 X_SCORE_BAND_LOWER,
1829 X_SCORE_BAND_UPPER,
1830 X_IRREGULARITY_CODE_ID,
1831 X_ATTRIBUTE_CATEGORY,
1832 X_ATTRIBUTE1,
1833 X_ATTRIBUTE2,
1834 X_ATTRIBUTE3,
1835 X_ATTRIBUTE4,
1836 X_ATTRIBUTE5,
1837 X_ATTRIBUTE6,
1838 X_ATTRIBUTE7,
1839 X_ATTRIBUTE8,
1840 X_ATTRIBUTE9,
1841 X_ATTRIBUTE10,
1842 X_ATTRIBUTE11,
1843 X_ATTRIBUTE12,
1844 X_ATTRIBUTE13,
1845 X_ATTRIBUTE14,
1846 X_ATTRIBUTE15,
1847 X_ATTRIBUTE16,
1848 X_ATTRIBUTE17,
1849 X_ATTRIBUTE18,
1850 X_ATTRIBUTE19,
1851 X_ATTRIBUTE20,
1852 L_MODE );
1853 end ADD_ROW;
1854 procedure DELETE_ROW (
1855 X_ROWID in VARCHAR2,
1856 x_mode IN VARCHAR2
1857 ) AS
1858 /*************************************************************
1859 Created By :
1860 Date Created By :
1861 Purpose :
1862 Know limitations, enhancements or remarks
1863 Change History
1864 Who When What
1865
1866 (reverse chronological order - newest change first)
1867 ***************************************************************/
1868
1869 begin
1870 Before_DML (
1871 p_action => 'DELETE',
1872 x_rowid => X_ROWID
1873 );
1874 IF (x_mode = 'S') THEN
1875 igs_sc_gen_001.set_ctx('R');
1876 END IF;
1877 delete from IGS_AD_TST_RSLT_DTLS
1878 where ROWID = X_ROWID;
1879 if (sql%notfound) then
1880 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1881 igs_ge_msg_stack.add;
1882 igs_sc_gen_001.unset_ctx('R');
1883 app_exception.raise_exception;
1884 end if;
1885 IF (x_mode = 'S') THEN
1886 igs_sc_gen_001.unset_ctx('R');
1887 END IF;
1888
1889 After_DML (
1890 p_action => 'DELETE',
1891 x_rowid => X_ROWID
1892 );
1893
1894 END DELETE_ROW;
1895
1896 END igs_ad_tst_rslt_dtls_pkg;
[Home] [Help]