1 PACKAGE BODY igs_ad_test_segments_pkg AS
2 /* $Header: IGSAI78B.pls 120.1 2005/06/28 04:38:35 appldev ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ad_test_segments%RowType;
5 new_references igs_ad_test_segments%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_min_score IN NUMBER DEFAULT NULL,
11 x_max_score IN NUMBER DEFAULT NULL,
12 x_include_in_comp_score IN VARCHAR2 DEFAULT NULL,
13 x_score_ind IN VARCHAR2 DEFAULT NULL,
14 x_percentile_ind IN VARCHAR2 DEFAULT NULL,
15 x_national_percentile_ind IN VARCHAR2 DEFAULT NULL,
16 x_state_percentile_ind IN VARCHAR2 DEFAULT NULL,
17 x_percentile_year_rank_ind IN VARCHAR2 DEFAULT NULL,
18 x_score_band_upper_ind IN VARCHAR2 DEFAULT NULL,
19 x_score_band_lower_ind IN VARCHAR2 DEFAULT NULL,
20 x_irregularity_code_ind IN VARCHAR2 DEFAULT NULL,
21 x_test_segment_id IN NUMBER DEFAULT NULL,
22 x_admission_test_type IN VARCHAR2 DEFAULT NULL,
23 x_test_segment_name IN VARCHAR2 DEFAULT NULL,
24 x_description IN VARCHAR2 DEFAULT NULL,
25 x_closed_ind IN VARCHAR2 DEFAULT NULL,
26 x_creation_date IN DATE DEFAULT NULL,
27 x_created_by IN NUMBER DEFAULT NULL,
28 x_last_update_date IN DATE DEFAULT NULL,
29 x_last_updated_by IN NUMBER DEFAULT NULL,
30 x_last_update_login IN NUMBER DEFAULT NULL,
31 x_segment_type IN VARCHAR2 DEFAULT NULL,
32 x_segment_group IN NUMBER DEFAULT NULL
33 ) AS
34
35 /*************************************************************
36 Created By :samaresh.in
37 Date Created By :15-May-2000
38 Purpose :
39 Know limitations, enhancements or remarks
40 Change History
41 Who When What
42
43 (reverse chronological order - newest change first)
44 ***************************************************************/
45
46 CURSOR cur_old_ref_values IS
47 SELECT *
48 FROM IGS_AD_TEST_SEGMENTS
49 WHERE rowid = x_rowid;
50
51 BEGIN
52
53 l_rowid := x_rowid;
54
55 -- Code for setting the Old and New Reference Values.
56 -- Populate Old Values.
57 Open cur_old_ref_values;
58 Fetch cur_old_ref_values INTO old_references;
59 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
60 Close cur_old_ref_values;
61 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
62 IGS_GE_MSG_STACK.ADD;
63 App_Exception.Raise_Exception;
64 Return;
65 END IF;
66 Close cur_old_ref_values;
67
68 -- Populate New Values.
69 new_references.min_score := x_min_score;
70 new_references.max_score := x_max_score;
71 new_references.include_in_comp_score := x_include_in_comp_score;
72 new_references.score_ind := x_score_ind;
73 new_references.percentile_ind := x_percentile_ind;
74 new_references.national_percentile_ind := x_national_percentile_ind;
75 new_references.state_percentile_ind := x_state_percentile_ind;
76 new_references.percentile_year_rank_ind := x_percentile_year_rank_ind;
77 new_references.score_band_upper_ind := x_score_band_upper_ind;
78 new_references.score_band_lower_ind := x_score_band_lower_ind;
79 new_references.irregularity_code_ind := x_irregularity_code_ind;
80 new_references.test_segment_id := x_test_segment_id;
81 new_references.admission_test_type := x_admission_test_type;
82 new_references.test_segment_name := x_test_segment_name;
83 new_references.description := x_description;
84 new_references.closed_ind := x_closed_ind;
85 new_references.segment_type := x_segment_type;
86 new_references.segment_group := x_segment_group;
87 IF (p_action = 'UPDATE') THEN
88 new_references.creation_date := old_references.creation_date;
89 new_references.created_by := old_references.created_by;
90 ELSE
91 new_references.creation_date := x_creation_date;
92 new_references.created_by := x_created_by;
93 END IF;
94 new_references.last_update_date := x_last_update_date;
95 new_references.last_updated_by := x_last_updated_by;
96 new_references.last_update_login := x_last_update_login;
97
98 END Set_Column_Values;
99
100 PROCEDURE Check_Constraints (
101 Column_Name IN VARCHAR2 DEFAULT NULL,
102 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
103 /*************************************************************
104 Created By :samaresh.in
105 Date Created By :15-May-2000
106 Purpose :
107 Know limitations, enhancements or remarks
108 Change History
109 Who When What
110
111 (reverse chronological order - newest change first)
112 ***************************************************************/
113
114 BEGIN
115
116 IF column_name IS NULL THEN
117 NULL;
118 ELSIF UPPER(column_name) = 'CLOSED_IND' THEN
119 new_references.closed_ind := column_value;
120 ELSIF UPPER(column_name) = 'PERCENTILE_YEAR_RANK_IND' THEN
121 new_references.percentile_year_rank_ind := column_value;
122 ELSIF UPPER(column_name) = 'PERCENTILE_IND' THEN
123 new_references.percentile_ind := column_value;
124 ELSIF UPPER(column_name) = 'SCORE_BAND_LOWER_IND' THEN
125 new_references.score_band_lower_ind := column_value;
126 ELSIF UPPER(column_name) = 'MIN_SCORE' THEN
127 new_references.min_score := IGS_GE_NUMBER.TO_NUM(column_value);
128 ELSIF UPPER(column_name) = 'INCLUDE_IN_COMP_SCORE' THEN
129 new_references.include_in_comp_score := column_value;
130 ELSIF UPPER(column_name) = 'STATE_PERCENTILE_IND' THEN
131 new_references.state_percentile_ind := column_value;
132 ELSIF UPPER(column_name) = 'IRREGULARITY_CODE_IND' THEN
133 new_references.irregularity_code_ind := column_value;
134 ELSIF UPPER(column_name) = 'SCORE_IND' THEN
135 new_references.score_ind := column_value;
136 ELSIF UPPER(column_name) = 'MAX_SCORE' THEN
137 new_references.max_score := IGS_GE_NUMBER.TO_NUM(column_value);
138 ELSIF UPPER(column_name) = 'NATIONAL_PERCENTILE_IND' THEN
139 new_references.national_percentile_ind := column_value;
140 ELSIF UPPER(column_name) = 'SCORE_BAND_UPPER_IND' THEN
141 new_references.score_band_upper_ind := column_value;
142 NULL;
143 END IF;
144
145
146
147 -- The following code checks for check constraints on the Columns.
148 IF Upper(Column_Name) = 'CLOSED_IND' OR
149 Column_Name IS NULL THEN
150 IF NOT (new_references.closed_ind IN ('Y','N')) THEN
151 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
152 IGS_GE_MSG_STACK.ADD;
153 App_Exception.Raise_Exception;
154 END IF;
155 END IF;
156
157 -- The following code checks for check constraints on the Columns.
158 IF Upper(Column_Name) = 'PERCENTILE_YEAR_RANK_IND' OR
159 Column_Name IS NULL THEN
160 IF NOT (new_references.percentile_year_rank_ind IN ('Y','N')) THEN
161 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
162 IGS_GE_MSG_STACK.ADD;
163 App_Exception.Raise_Exception;
164 END IF;
165 END IF;
166
167 -- The following code checks for check constraints on the Columns.
168 IF Upper(Column_Name) = 'PERCENTILE_IND' OR
169 Column_Name IS NULL THEN
170 IF NOT (new_references.percentile_ind IN ('Y','N')) THEN
171 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
172 IGS_GE_MSG_STACK.ADD;
173 App_Exception.Raise_Exception;
174 END IF;
175 END IF;
176
177 -- The following code checks for check constraints on the Columns.
178 IF Upper(Column_Name) = 'SCORE_BAND_LOWER_IND' OR
179 Column_Name IS NULL THEN
180 IF NOT (new_references.score_band_lower_ind IN ('Y','N')) THEN
181 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
182 IGS_GE_MSG_STACK.ADD;
183 App_Exception.Raise_Exception;
184 END IF;
185 END IF;
186
187 -- The following code checks for check constraints on the Columns.
188 IF Upper(Column_Name) = 'MIN_SCORE' OR
189 Column_Name IS NULL THEN
190 IF NOT (new_references.min_score >= 0) THEN
191 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
192 IGS_GE_MSG_STACK.ADD;
193 App_Exception.Raise_Exception;
194 END IF;
195 END IF;
196
197 -- The following code checks for check constraints on the Columns.
198 IF Upper(Column_Name) = 'INCLUDE_IN_COMP_SCORE' OR
199 Column_Name IS NULL THEN
200 IF NOT (new_references.include_in_comp_score IN ('Y','N')) THEN
201 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
202 IGS_GE_MSG_STACK.ADD;
203 App_Exception.Raise_Exception;
204 END IF;
205 END IF;
206
207 -- The following code checks for check constraints on the Columns.
208 IF Upper(Column_Name) = 'STATE_PERCENTILE_IND' OR
209 Column_Name IS NULL THEN
210 IF NOT (new_references.state_percentile_ind IN ('Y','N')) THEN
211 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
212 IGS_GE_MSG_STACK.ADD;
213 App_Exception.Raise_Exception;
214 END IF;
215 END IF;
216
217 -- The following code checks for check constraints on the Columns.
218 IF Upper(Column_Name) = 'IRREGULARITY_CODE_IND' OR
219 Column_Name IS NULL THEN
220 IF NOT (new_references.irregularity_code_ind IN ('Y','N')) THEN
221 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
222 IGS_GE_MSG_STACK.ADD;
223 App_Exception.Raise_Exception;
224 END IF;
225 END IF;
226
227 -- The following code checks for check constraints on the Columns.
228 IF Upper(Column_Name) = 'SCORE_IND' OR
229 Column_Name IS NULL THEN
230 IF NOT (new_references.score_ind IN ('Y','N')) THEN
231 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
232 IGS_GE_MSG_STACK.ADD;
233 App_Exception.Raise_Exception;
234 END IF;
235 END IF;
236
237 -- The following code checks for check constraints on the Columns.
238 IF Upper(Column_Name) = 'MAX_SCORE' OR
239 Column_Name IS NULL THEN
240 IF NOT (new_references.max_score >=0 and new_references.max_score > new_references.min_score) THEN
241 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
242 IGS_GE_MSG_STACK.ADD;
243 App_Exception.Raise_Exception;
244 END IF;
245 END IF;
246
247 -- The following code checks for check constraints on the Columns.
248 IF Upper(Column_Name) = 'NATIONAL_PERCENTILE_IND' OR
249 Column_Name IS NULL THEN
250 IF NOT (new_references.national_percentile_ind IN ('Y','N')) THEN
251 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
252 IGS_GE_MSG_STACK.ADD;
253 App_Exception.Raise_Exception;
254 END IF;
255 END IF;
256
257 -- The following code checks for check constraints on the Columns.
258 IF Upper(Column_Name) = 'SCORE_BAND_UPPER_IND' OR
259 Column_Name IS NULL THEN
260 IF NOT (new_references.score_band_upper_ind IN ('Y','N')) THEN
261 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
262 IGS_GE_MSG_STACK.ADD;
263 App_Exception.Raise_Exception;
264 END IF;
265 END IF;
266
267
268 END Check_Constraints;
269
270 PROCEDURE Check_Uniqueness AS
271 /*************************************************************
272 Created By :samaresh.in
273 Date Created By :15-May-2000
274 Purpose :
275 Know limitations, enhancements or remarks
276 Change History
277 Who When What
278
279 (reverse chronological order - newest change first)
280 ***************************************************************/
281
282 begin
283 IF Get_Uk_For_Validation (
284 new_references.admission_test_type
285 ,new_references.test_segment_name
286 ) THEN
287 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
288 IGS_GE_MSG_STACK.ADD;
289 app_exception.raise_exception;
290
291 END IF;
292
293 IF new_references.segment_type = 'SCORE' THEN
294 IF Get_UK2_For_Validation (
295 new_references.admission_test_type,
296 new_references.segment_group
297 ) THEN
298 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_UNIQUE_SEG_GRP');
299 IGS_GE_MSG_STACK.ADD;
300 app_exception.raise_exception;
301 END IF;
302 END IF;
303 END Check_Uniqueness ;
304 PROCEDURE Check_Child_Existance IS
305 /*************************************************************
306 Created By :samaresh.in
307 Date Created By :15-May-2000
308 Purpose :
309 Know limitations, enhancements or remarks
310 Change History
311 Who When What
312
313 (reverse chronological order - newest change first)
314 ***************************************************************/
315
316 BEGIN
317
318 Igs_Ad_Tst_Rslt_Dtls_Pkg.Get_FK_Igs_Ad_Test_Segments (
319 old_references.test_segment_id
320 );
321
322 Igs_Ad_Up_Header_Pkg.Get_FK_Igs_Ad_Test_Segments (
323 old_references.test_segment_id
324 );
325
326 END Check_Child_Existance;
327
328 PROCEDURE GET_FK_IGS_TEST_TYPE (
329 x_admission_test_type IN VARCHAR2
330 ) AS
331 /***************************************************************
332 Created By :rboddu
333 Date Created By :12-Oct-2001
334 Purpose : When record is deleted from the master table IGS_AD_TEST_TYPE
335 The corresponding detail IGS_AD_TEST_SEGMENTS are not checked
336 for dependency. To implement the dependency check this procedure
337 is added here and is being called from IGS_AD_TEST_TYPE_PKG.
338 Know limitations, enhancements or remarks
339 Change History
340 Who When What
341
342 (reverse chronological order - newest change first)
343 ***************************************************************/
344
345 CURSOR cur_rowid IS
346 SELECT rowid
347 FROM IGS_AD_TEST_SEGMENTS
348 WHERE admission_test_type = x_admission_test_type ;
349
350
351 lv_rowid cur_rowid%RowType;
352
353 BEGIN
354
355 Open cur_rowid;
356 Fetch cur_rowid INTO lv_rowid;
357 IF (cur_rowid%FOUND) THEN
358 Close cur_rowid;
359 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ADTT_ADTS_FK');
360 IGS_GE_MSG_STACK.ADD;
361 App_Exception.Raise_Exception;
362 Return;
363 END IF;
364
365 Close cur_rowid;
366
367 END GET_FK_IGS_TEST_TYPE;
368
369 FUNCTION Get_PK_For_Validation (
370 x_test_segment_id IN NUMBER,
371 x_closed_ind IN VARCHAR2
372 ) RETURN BOOLEAN AS
373
374 /*************************************************************
375 Created By :samaresh.in
376 Date Created By :15-May-2000
377 Purpose :
378 Know limitations, enhancements or remarks
379 Change History
380 Who When What
381
382 (reverse chronological order - newest change first)
383 ***************************************************************/
384
385 CURSOR cur_rowid IS
386 SELECT rowid
387 FROM igs_ad_test_segments
388 WHERE test_segment_id = x_test_segment_id AND
389 closed_ind = NVL(x_closed_ind,closed_ind)
390 FOR UPDATE NOWAIT;
391
392 lv_rowid cur_rowid%RowType;
393
394 BEGIN
398 IF (cur_rowid%FOUND) THEN
395
396 Open cur_rowid;
397 Fetch cur_rowid INTO lv_rowid;
399 Close cur_rowid;
400 Return(TRUE);
401 ELSE
402 Close cur_rowid;
403 Return(FALSE);
404 END IF;
405 END Get_PK_For_Validation;
406
407 FUNCTION Get_UK_For_Validation (
408 x_admission_test_type IN VARCHAR2,
409 x_test_segment_name IN VARCHAR2,
410 x_closed_ind IN VARCHAR2
411 ) RETURN BOOLEAN AS
412
413 /*************************************************************
414 Created By :samaresh.in
415 Date Created By :15-May-2000
416 Purpose :
417 Know limitations, enhancements or remarks
418 Change History
419 Who When What
420
421 (reverse chronological order - newest change first)
422 ***************************************************************/
423
424 CURSOR cur_rowid IS
425 SELECT rowid
426 FROM igs_ad_test_segments
427 WHERE admission_test_type = x_admission_test_type
428 AND test_segment_name = x_test_segment_name AND
429 ((l_rowid is null) or (rowid <> l_rowid)) AND
430 closed_ind = NVL(x_closed_ind,closed_ind) ;
431
432 lv_rowid cur_rowid%RowType;
433
434 BEGIN
435
436 Open cur_rowid;
437 Fetch cur_rowid INTO lv_rowid;
438 IF (cur_rowid%FOUND) THEN
439 Close cur_rowid;
440 return (true);
441 ELSE
442 close cur_rowid;
443 return(false);
444 END IF;
445 END Get_UK_For_Validation ;
446
447 FUNCTION Get_UK2_For_Validation (
448 x_admission_test_type IN VARCHAR2,
449 x_segment_group IN NUMBER
450 ) RETURN BOOLEAN AS
451
452 /*************************************************************
453 Created By :samaresh.in
454 Date Created By :15-May-2000
455 Purpose :
456 Know limitations, enhancements or remarks
457 Change History
458 Who When What
459
460 (reverse chronological order - newest change first)
461 ***************************************************************/
462
463 CURSOR cur_unique_rowid IS
464 SELECT rowid
465 FROM igs_ad_test_segments
466 WHERE admission_test_type = x_admission_test_type AND
467 ((l_rowid is null) or (rowid <> l_rowid)) AND
468 segment_group = x_segment_group AND
469 segment_type = 'SCORE' ;
470
471 lv_unique_rowid cur_unique_rowid%RowType;
472
473 BEGIN
474
475 Open cur_unique_rowid;
476 Fetch cur_unique_rowid INTO lv_unique_rowid;
477 IF (cur_unique_rowid%FOUND) THEN
478 Close cur_unique_rowid;
479 return (true);
480 ELSE
481 close cur_unique_rowid;
482 return(false);
483 END IF;
484 END Get_UK2_For_Validation ;
485
486 PROCEDURE Before_DML (
487 p_action IN VARCHAR2,
488 x_rowid IN VARCHAR2 DEFAULT NULL,
489 x_min_score IN NUMBER DEFAULT NULL,
490 x_max_score IN NUMBER DEFAULT NULL,
491 x_include_in_comp_score IN VARCHAR2 DEFAULT NULL,
492 x_score_ind IN VARCHAR2 DEFAULT NULL,
493 x_percentile_ind IN VARCHAR2 DEFAULT NULL,
494 x_national_percentile_ind IN VARCHAR2 DEFAULT NULL,
495 x_state_percentile_ind IN VARCHAR2 DEFAULT NULL,
496 x_percentile_year_rank_ind IN VARCHAR2 DEFAULT NULL,
497 x_score_band_upper_ind IN VARCHAR2 DEFAULT NULL,
498 x_score_band_lower_ind IN VARCHAR2 DEFAULT NULL,
499 x_irregularity_code_ind IN VARCHAR2 DEFAULT NULL,
500 x_test_segment_id IN NUMBER DEFAULT NULL,
501 x_admission_test_type IN VARCHAR2 DEFAULT NULL,
502 x_test_segment_name IN VARCHAR2 DEFAULT NULL,
503 x_description IN VARCHAR2 DEFAULT NULL,
504 x_closed_ind IN VARCHAR2 DEFAULT NULL,
505 x_creation_date IN DATE DEFAULT NULL,
506 x_created_by IN NUMBER DEFAULT NULL,
507 x_last_update_date IN DATE DEFAULT NULL,
508 x_last_updated_by IN NUMBER DEFAULT NULL,
509 x_last_update_login IN NUMBER DEFAULT NULL,
510 x_segment_type IN VARCHAR2 DEFAULT NULL,
511 x_segment_group IN NUMBER DEFAULT NULL
512 ) AS
513 /*************************************************************
514 Created By :samaresh.in
515 Date Created By :15-May-2000
516 Purpose :
517 Know limitations, enhancements or remarks
518 Change History
519 Who When What
520
521 (reverse chronological order - newest change first)
522 ***************************************************************/
523
524 BEGIN
525
526 Set_Column_Values (
527 p_action,
528 x_rowid,
529 x_min_score,
530 x_max_score,
531 x_include_in_comp_score,
532 x_score_ind,
533 x_percentile_ind,
534 x_national_percentile_ind,
535 x_state_percentile_ind,
536 x_percentile_year_rank_ind,
537 x_score_band_upper_ind,
538 x_score_band_lower_ind,
539 x_irregularity_code_ind,
540 x_test_segment_id,
541 x_admission_test_type,
542 x_test_segment_name,
543 x_description,
547 x_last_update_date,
544 x_closed_ind,
545 x_creation_date,
546 x_created_by,
548 x_last_updated_by,
549 x_last_update_login,
550 x_segment_type,
551 x_segment_group
552 );
553
554 IF (p_action = 'INSERT') THEN
555 -- Call all the procedures related to Before Insert.
556 Null;
557 IF Get_Pk_For_Validation(
558 new_references.test_segment_id) THEN
559 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
560 IGS_GE_MSG_STACK.ADD;
561 App_Exception.Raise_Exception;
562 END IF;
563 Check_Uniqueness;
564 Check_Constraints;
565 ELSIF (p_action = 'UPDATE') THEN
566 -- Call all the procedures related to Before Update.
567 Null;
568 Check_Uniqueness;
569 Check_Constraints;
570 ELSIF (p_action = 'DELETE') THEN
571 -- Call all the procedures related to Before Delete.
572 Null;
573 Check_Child_Existance;
574 ELSIF (p_action = 'VALIDATE_INSERT') THEN
575 -- Call all the procedures related to Before Insert.
576 IF Get_PK_For_Validation (
577 new_references.test_segment_id) THEN
578 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
579 IGS_GE_MSG_STACK.ADD;
580 App_Exception.Raise_Exception;
581 END IF;
582 Check_Uniqueness;
583 Check_Constraints;
584 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
585 Check_Uniqueness;
586 Check_Constraints;
587 ELSIF (p_action = 'VALIDATE_DELETE') THEN
588 Check_Child_Existance;
589 END IF;
590 l_rowid := NULL;
591 END Before_DML;
592
593 PROCEDURE After_DML (
594 p_action IN VARCHAR2,
595 x_rowid IN VARCHAR2
596 ) IS
597 /*************************************************************
598 Created By :samaresh.in
599 Date Created By :15-May-2000
600 Purpose :
601 Know limitations, enhancements or remarks
602 Change History
603 Who When What
604
605 (reverse chronological order - newest change first)
606 ***************************************************************/
607
608 BEGIN
609
610 l_rowid := x_rowid;
611
612 IF (p_action = 'INSERT') THEN
613 -- Call all the procedures related to After Insert.
614 Null;
615 ELSIF (p_action = 'UPDATE') THEN
616 -- Call all the procedures related to After Update.
617 Null;
618 ELSIF (p_action = 'DELETE') THEN
619 -- Call all the procedures related to After Delete.
620 Null;
621 END IF;
622
623 l_rowid:=NULL;
624 END After_DML;
625
626 procedure INSERT_ROW (
627 X_ROWID in out NOCOPY VARCHAR2,
628 x_MIN_SCORE IN NUMBER,
629 x_MAX_SCORE IN NUMBER,
630 x_INCLUDE_IN_COMP_SCORE IN VARCHAR2,
631 x_SCORE_IND IN VARCHAR2,
632 x_PERCENTILE_IND IN VARCHAR2,
633 x_NATIONAL_PERCENTILE_IND IN VARCHAR2,
634 x_STATE_PERCENTILE_IND IN VARCHAR2,
635 x_PERCENTILE_YEAR_RANK_IND IN VARCHAR2,
636 x_SCORE_BAND_UPPER_IND IN VARCHAR2,
637 x_SCORE_BAND_LOWER_IND IN VARCHAR2,
638 x_IRREGULARITY_CODE_IND IN VARCHAR2,
639 x_TEST_SEGMENT_ID IN OUT NOCOPY NUMBER,
640 x_ADMISSION_TEST_TYPE IN VARCHAR2,
641 x_TEST_SEGMENT_NAME IN VARCHAR2,
642 x_DESCRIPTION IN VARCHAR2,
643 x_CLOSED_IND IN VARCHAR2,
644 X_MODE in VARCHAR2,
645 X_SEGMENT_TYPE IN VARCHAR2 ,
646 X_SEGMENT_GROUP IN NUMBER
647 ) AS
648 /*************************************************************
649 Created By :samaresh.in
650 Date Created By :15-May-2000
651 Purpose :
652 Know limitations, enhancements or remarks
653 Change History
654 Who When What
655
656 (reverse chronological order - newest change first)
657 ***************************************************************/
658
659 CURSOR c IS
660 SELECT ROWID
661 FROM IGS_AD_TEST_SEGMENTS
662 WHERE TEST_SEGMENT_ID= X_TEST_SEGMENT_ID;
663
664 X_LAST_UPDATE_DATE DATE ;
665 X_LAST_UPDATED_BY NUMBER ;
666 X_LAST_UPDATE_LOGIN NUMBER ;
667 l_mode VARCHAR2(1);
668
669 BEGIN
670
671 l_mode := NVL(x_mode, 'R');
672 X_LAST_UPDATE_DATE := SYSDATE;
673
674 IF(l_MODE = 'I') THEN
675 X_LAST_UPDATED_BY := 1;
676 X_LAST_UPDATE_LOGIN := 0;
677 elsif (l_MODE IN ('R','S') ) then
678 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
679 if X_LAST_UPDATED_BY is NULL then
680 X_LAST_UPDATED_BY := -1;
681 end if;
682 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
683 if X_LAST_UPDATE_LOGIN is NULL then
684 X_LAST_UPDATE_LOGIN := -1;
685 end if;
686 else
687 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
688 IGS_GE_MSG_STACK.ADD;
689 app_exception.raise_exception;
690 end if;
691
692 X_TEST_SEGMENT_ID := -1;
693
697 x_min_score=>X_MIN_SCORE,
694 Before_DML(
695 p_action=>'INSERT',
696 x_rowid=>X_ROWID,
698 x_max_score=>X_MAX_SCORE,
699 x_include_in_comp_score=>NVL(X_INCLUDE_IN_COMP_SCORE,'N' ),
700 x_score_ind=>NVL(X_SCORE_IND,'N' ),
701 x_percentile_ind=>NVL(X_PERCENTILE_IND,'N' ),
702 x_national_percentile_ind=>NVL(X_NATIONAL_PERCENTILE_IND,'N' ),
703 x_state_percentile_ind=>NVL(X_STATE_PERCENTILE_IND,'N' ),
704 x_percentile_year_rank_ind=>NVL(X_PERCENTILE_YEAR_RANK_IND,'N' ),
705 x_score_band_upper_ind=>NVL(X_SCORE_BAND_UPPER_IND,'N' ),
706 x_score_band_lower_ind=>NVL(X_SCORE_BAND_LOWER_IND,'N' ),
707 x_irregularity_code_ind=>NVL(X_IRREGULARITY_CODE_IND,'N' ),
708 x_test_segment_id=>X_TEST_SEGMENT_ID,
709 x_admission_test_type=>X_ADMISSION_TEST_TYPE,
710 x_test_segment_name=>X_TEST_SEGMENT_NAME,
711 x_description=>X_DESCRIPTION,
712 x_closed_ind=>NVL(X_CLOSED_IND,'N' ),
713 x_creation_date=>X_LAST_UPDATE_DATE,
714 x_created_by=>X_LAST_UPDATED_BY,
715 x_last_update_date=>X_LAST_UPDATE_DATE,
716 x_last_updated_by=>X_LAST_UPDATED_BY,
717 x_last_update_login=>X_LAST_UPDATE_LOGIN,
718 x_segment_type => x_segment_type,
719 x_segment_group => x_segment_group);
720
721 IF (x_mode = 'S') THEN
722 igs_sc_gen_001.set_ctx('R');
723 END IF;
724 INSERT INTO IGS_AD_TEST_SEGMENTS (
725 MIN_SCORE
726 ,MAX_SCORE
727 ,INCLUDE_IN_COMP_SCORE
728 ,SCORE_IND
729 ,PERCENTILE_IND
730 ,NATIONAL_PERCENTILE_IND
731 ,STATE_PERCENTILE_IND
732 ,PERCENTILE_YEAR_RANK_IND
733 ,SCORE_BAND_UPPER_IND
734 ,SCORE_BAND_LOWER_IND
735 ,IRREGULARITY_CODE_IND
736 ,TEST_SEGMENT_ID
737 ,ADMISSION_TEST_TYPE
738 ,TEST_SEGMENT_NAME
739 ,DESCRIPTION
740 ,CLOSED_IND
741 ,CREATION_DATE
742 ,CREATED_BY
743 ,LAST_UPDATE_DATE
744 ,LAST_UPDATED_BY
745 ,LAST_UPDATE_LOGIN
746 ,SEGMENT_TYPE
747 ,SEGMENT_GROUP
748 ) values (
749 NEW_REFERENCES.MIN_SCORE
750 ,NEW_REFERENCES.MAX_SCORE
751 ,NEW_REFERENCES.INCLUDE_IN_COMP_SCORE
752 ,NEW_REFERENCES.SCORE_IND
753 ,NEW_REFERENCES.PERCENTILE_IND
754 ,NEW_REFERENCES.NATIONAL_PERCENTILE_IND
755 ,NEW_REFERENCES.STATE_PERCENTILE_IND
756 ,NEW_REFERENCES.PERCENTILE_YEAR_RANK_IND
757 ,NEW_REFERENCES.SCORE_BAND_UPPER_IND
758 ,NEW_REFERENCES.SCORE_BAND_LOWER_IND
759 ,NEW_REFERENCES.IRREGULARITY_CODE_IND
760 ,IGS_AD_TEST_SEGMENTS_S.NEXTVAL
761 ,NEW_REFERENCES.ADMISSION_TEST_TYPE
762 ,NEW_REFERENCES.TEST_SEGMENT_NAME
763 ,NEW_REFERENCES.DESCRIPTION
764 ,NEW_REFERENCES.CLOSED_IND
765 ,X_LAST_UPDATE_DATE
766 ,X_LAST_UPDATED_BY
767 ,X_LAST_UPDATE_DATE
768 ,X_LAST_UPDATED_BY
769 ,X_LAST_UPDATE_LOGIN
770 ,NEW_REFERENCES.SEGMENT_TYPE
771 ,NEW_REFERENCES.SEGMENT_GROUP
772 )RETURNING TEST_SEGMENT_ID INTO X_TEST_SEGMENT_ID;
773 IF (x_mode = 'S') THEN
774 igs_sc_gen_001.unset_ctx('R');
775 END IF;
776
777
778 OPEN c;
779 FETCH c INTO X_ROWID;
780
781 IF (c%NOTFOUND) THEN
782 CLOSE c;
783 RAISE NO_DATA_FOUND;
784 END IF;
785
786 CLOSE c;
787
788 After_DML (
789 p_action => 'INSERT' ,
790 x_rowid => X_ROWID );
791
792 EXCEPTION
793 WHEN OTHERS THEN
794 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
795 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
796 fnd_message.set_token ('ERR_CD', SQLCODE);
797 igs_ge_msg_stack.add;
798 igs_sc_gen_001.unset_ctx('R');
799 app_exception.raise_exception;
800 ELSE
801 igs_sc_gen_001.unset_ctx('R');
802 RAISE;
803 END IF;
804
805 end INSERT_ROW;
806
807
808 procedure LOCK_ROW (
809 X_ROWID in VARCHAR2,
810 x_MIN_SCORE IN NUMBER,
811 x_MAX_SCORE IN NUMBER,
812 x_INCLUDE_IN_COMP_SCORE IN VARCHAR2,
813 x_SCORE_IND IN VARCHAR2,
814 x_PERCENTILE_IND IN VARCHAR2,
815 x_NATIONAL_PERCENTILE_IND IN VARCHAR2,
816 x_STATE_PERCENTILE_IND IN VARCHAR2,
817 x_PERCENTILE_YEAR_RANK_IND IN VARCHAR2,
818 x_SCORE_BAND_UPPER_IND IN VARCHAR2,
819 x_SCORE_BAND_LOWER_IND IN VARCHAR2,
820 x_IRREGULARITY_CODE_IND IN VARCHAR2,
821 x_TEST_SEGMENT_ID IN NUMBER,
822 x_ADMISSION_TEST_TYPE IN VARCHAR2,
823 x_TEST_SEGMENT_NAME IN VARCHAR2,
824 x_DESCRIPTION IN VARCHAR2,
825 x_CLOSED_IND IN VARCHAR2,
826 X_SEGMENT_TYPE IN VARCHAR2 ,
827 X_SEGMENT_GROUP IN NUMBER ) AS
828 /*************************************************************
829 Created By :samaresh.in
830 Date Created By :15-May-2000
831 Purpose :
832 Know limitations, enhancements or remarks
833 Change History
834 Who When What
835
836 (reverse chronological order - newest change first)
840 MIN_SCORE
837 ***************************************************************/
838
839 cursor c1 is select
841 , MAX_SCORE
842 , INCLUDE_IN_COMP_SCORE
843 , SCORE_IND
844 , PERCENTILE_IND
845 , NATIONAL_PERCENTILE_IND
846 , STATE_PERCENTILE_IND
847 , PERCENTILE_YEAR_RANK_IND
848 , SCORE_BAND_UPPER_IND
849 , SCORE_BAND_LOWER_IND
850 , IRREGULARITY_CODE_IND
851 , ADMISSION_TEST_TYPE
852 , TEST_SEGMENT_NAME
853 , DESCRIPTION
854 , CLOSED_IND
855 , SEGMENT_TYPE
856 , SEGMENT_GROUP
857 from IGS_AD_TEST_SEGMENTS
858 where ROWID = X_ROWID
859 for update nowait;
860 tlinfo c1%rowtype;
861 begin
862 open c1;
863 fetch c1 into tlinfo;
864 if (c1%notfound) then
865 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
866 IGS_GE_MSG_STACK.ADD;
867 close c1;
868 app_exception.raise_exception;
869 return;
870 end if;
871 close c1;
872 if ( (tlinfo.MIN_SCORE = X_MIN_SCORE)
873 AND (tlinfo.MAX_SCORE = X_MAX_SCORE)
874 AND (tlinfo.INCLUDE_IN_COMP_SCORE = X_INCLUDE_IN_COMP_SCORE)
875 AND (tlinfo.SCORE_IND = X_SCORE_IND)
876 AND (tlinfo.PERCENTILE_IND = X_PERCENTILE_IND)
877 AND (tlinfo.NATIONAL_PERCENTILE_IND = X_NATIONAL_PERCENTILE_IND)
878 AND (tlinfo.STATE_PERCENTILE_IND = X_STATE_PERCENTILE_IND)
879 AND (tlinfo.PERCENTILE_YEAR_RANK_IND = X_PERCENTILE_YEAR_RANK_IND)
880 AND (tlinfo.SCORE_BAND_UPPER_IND = X_SCORE_BAND_UPPER_IND)
881 AND (tlinfo.SCORE_BAND_LOWER_IND = X_SCORE_BAND_LOWER_IND)
882 AND (tlinfo.IRREGULARITY_CODE_IND = X_IRREGULARITY_CODE_IND)
883 AND (tlinfo.ADMISSION_TEST_TYPE = X_ADMISSION_TEST_TYPE)
884 AND (tlinfo.TEST_SEGMENT_NAME = X_TEST_SEGMENT_NAME)
885 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
886 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
887 AND (tlinfo.SEGMENT_TYPE = X_SEGMENT_TYPE)
888 AND (tlinfo.SEGMENT_GROUP = X_SEGMENT_GROUP)
889 ) then
890 null;
891 else
892 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
893 IGS_GE_MSG_STACK.ADD;
894 app_exception.raise_exception;
895 end if;
896 return;
897 end LOCK_ROW;
898 Procedure UPDATE_ROW (
899 X_ROWID in VARCHAR2,
900 x_MIN_SCORE IN NUMBER,
901 x_MAX_SCORE IN NUMBER,
902 x_INCLUDE_IN_COMP_SCORE IN VARCHAR2,
903 x_SCORE_IND IN VARCHAR2,
904 x_PERCENTILE_IND IN VARCHAR2,
905 x_NATIONAL_PERCENTILE_IND IN VARCHAR2,
906 x_STATE_PERCENTILE_IND IN VARCHAR2,
907 x_PERCENTILE_YEAR_RANK_IND IN VARCHAR2,
908 x_SCORE_BAND_UPPER_IND IN VARCHAR2,
909 x_SCORE_BAND_LOWER_IND IN VARCHAR2,
910 x_IRREGULARITY_CODE_IND IN VARCHAR2,
911 x_TEST_SEGMENT_ID IN NUMBER,
912 x_ADMISSION_TEST_TYPE IN VARCHAR2,
913 x_TEST_SEGMENT_NAME IN VARCHAR2,
914 x_DESCRIPTION IN VARCHAR2,
915 x_CLOSED_IND IN VARCHAR2,
916 X_MODE in VARCHAR2,
917 X_SEGMENT_TYPE IN VARCHAR2 ,
918 X_SEGMENT_GROUP IN NUMBER
919 ) AS
920 /*************************************************************
921 Created By :samaresh.in
922 Date Created By :15-May-2000
923 Purpose :
924 Know limitations, enhancements or remarks
925 Change History
926 Who When What
927
928 (reverse chronological order - newest change first)
929 ***************************************************************/
930
931 X_LAST_UPDATE_DATE DATE ;
932 X_LAST_UPDATED_BY NUMBER ;
933 X_LAST_UPDATE_LOGIN NUMBER ;
934 l_mode VARCHAR2(1);
935
936 begin
937 l_mode := NVL(x_mode, 'R');
938 X_LAST_UPDATE_DATE := SYSDATE;
939 if(l_MODE = 'I') then
940 X_LAST_UPDATED_BY := 1;
941 X_LAST_UPDATE_LOGIN := 0;
942 elsif (l_MODE IN ('R','S')) then
943 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
944 if X_LAST_UPDATED_BY is NULL then
945 X_LAST_UPDATED_BY := -1;
946 end if;
947 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
948 if X_LAST_UPDATE_LOGIN is NULL then
949 X_LAST_UPDATE_LOGIN := -1;
950 end if;
951 else
952 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
953 IGS_GE_MSG_STACK.ADD;
954 app_exception.raise_exception;
955 end if;
956 Before_DML(
957 p_action=>'UPDATE',
958 x_rowid=>X_ROWID,
959 x_min_score=>X_MIN_SCORE,
960 x_max_score=>X_MAX_SCORE,
961 x_include_in_comp_score=>NVL(X_INCLUDE_IN_COMP_SCORE,'N' ),
962 x_score_ind=>NVL(X_SCORE_IND,'N' ),
963 x_percentile_ind=>NVL(X_PERCENTILE_IND,'N' ),
964 x_national_percentile_ind=>NVL(X_NATIONAL_PERCENTILE_IND,'N' ),
965 x_state_percentile_ind=>NVL(X_STATE_PERCENTILE_IND,'N' ),
966 x_percentile_year_rank_ind=>NVL(X_PERCENTILE_YEAR_RANK_IND,'N' ),
967 x_score_band_upper_ind=>NVL(X_SCORE_BAND_UPPER_IND,'N' ),
968 x_score_band_lower_ind=>NVL(X_SCORE_BAND_LOWER_IND,'N' ),
969 x_irregularity_code_ind=>NVL(X_IRREGULARITY_CODE_IND,'N' ),
970 x_test_segment_id=>X_TEST_SEGMENT_ID,
971 x_admission_test_type=>X_ADMISSION_TEST_TYPE,
972 x_test_segment_name=>X_TEST_SEGMENT_NAME,
973 x_description=>X_DESCRIPTION,
974 x_closed_ind=>NVL(X_CLOSED_IND,'N' ),
975 x_creation_date=>X_LAST_UPDATE_DATE,
976 x_created_by=>X_LAST_UPDATED_BY,
977 x_last_update_date=>X_LAST_UPDATE_DATE,
978 x_last_updated_by=>X_LAST_UPDATED_BY,
979 x_last_update_login=>X_LAST_UPDATE_LOGIN,
980 x_segment_type=>X_SEGMENT_TYPE,
981 x_segment_group=>X_SEGMENT_GROUP);
982
983 IF (x_mode = 'S') THEN
984 igs_sc_gen_001.set_ctx('R');
985 END IF;
986 update IGS_AD_TEST_SEGMENTS set
987 MIN_SCORE = NEW_REFERENCES.MIN_SCORE,
988 MAX_SCORE = NEW_REFERENCES.MAX_SCORE,
989 INCLUDE_IN_COMP_SCORE = NEW_REFERENCES.INCLUDE_IN_COMP_SCORE,
990 SCORE_IND = NEW_REFERENCES.SCORE_IND,
991 PERCENTILE_IND = NEW_REFERENCES.PERCENTILE_IND,
992 NATIONAL_PERCENTILE_IND = NEW_REFERENCES.NATIONAL_PERCENTILE_IND,
993 STATE_PERCENTILE_IND = NEW_REFERENCES.STATE_PERCENTILE_IND,
994 PERCENTILE_YEAR_RANK_IND = NEW_REFERENCES.PERCENTILE_YEAR_RANK_IND,
995 SCORE_BAND_UPPER_IND = NEW_REFERENCES.SCORE_BAND_UPPER_IND,
996 SCORE_BAND_LOWER_IND = NEW_REFERENCES.SCORE_BAND_LOWER_IND,
997 IRREGULARITY_CODE_IND = NEW_REFERENCES.IRREGULARITY_CODE_IND,
998 ADMISSION_TEST_TYPE = NEW_REFERENCES.ADMISSION_TEST_TYPE,
999 TEST_SEGMENT_NAME = NEW_REFERENCES.TEST_SEGMENT_NAME,
1000 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
1001 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
1002 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1003 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1004 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1005 SEGMENT_TYPE = X_SEGMENT_TYPE,
1006 SEGMENT_GROUP = X_SEGMENT_GROUP
1007 where ROWID = X_ROWID;
1008 if (sql%notfound) then
1009 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1010 igs_ge_msg_stack.add;
1011 igs_sc_gen_001.unset_ctx('R');
1012 app_exception.raise_exception;
1013 end if;
1014 IF (x_mode = 'S') THEN
1015 igs_sc_gen_001.unset_ctx('R');
1016 END IF;
1017
1018
1019 After_DML (
1020 p_action => 'UPDATE' ,
1021 x_rowid => X_ROWID
1022 );
1023 EXCEPTION
1024 WHEN OTHERS THEN
1025 IF (SQLCODE = (-28115)) THEN
1026 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1027 fnd_message.set_token ('ERR_CD', SQLCODE);
1028 igs_ge_msg_stack.add;
1029 igs_sc_gen_001.unset_ctx('R');
1030 app_exception.raise_exception;
1031 ELSE
1032 igs_sc_gen_001.unset_ctx('R');
1033 RAISE;
1034 END IF;
1035
1036 end UPDATE_ROW;
1037 procedure ADD_ROW (
1038 X_ROWID in out NOCOPY VARCHAR2,
1039 x_MIN_SCORE IN NUMBER,
1040 x_MAX_SCORE IN NUMBER,
1041 x_INCLUDE_IN_COMP_SCORE IN VARCHAR2,
1042 x_SCORE_IND IN VARCHAR2,
1043 x_PERCENTILE_IND IN VARCHAR2,
1044 x_NATIONAL_PERCENTILE_IND IN VARCHAR2,
1045 x_STATE_PERCENTILE_IND IN VARCHAR2,
1046 x_PERCENTILE_YEAR_RANK_IND IN VARCHAR2,
1047 x_SCORE_BAND_UPPER_IND IN VARCHAR2,
1048 x_SCORE_BAND_LOWER_IND IN VARCHAR2,
1049 x_IRREGULARITY_CODE_IND IN VARCHAR2,
1050 x_TEST_SEGMENT_ID IN OUT NOCOPY NUMBER,
1051 x_ADMISSION_TEST_TYPE IN VARCHAR2,
1052 x_TEST_SEGMENT_NAME IN VARCHAR2,
1053 x_DESCRIPTION IN VARCHAR2,
1054 x_CLOSED_IND IN VARCHAR2,
1055 X_MODE in VARCHAR2,
1056 X_SEGMENT_TYPE IN VARCHAR2 ,
1057 X_SEGMENT_GROUP IN NUMBER
1058 ) AS
1059 /*************************************************************
1060 Created By :samaresh.in
1061 Date Created By :15-May-2000
1062 Purpose :
1063 Know limitations, enhancements or remarks
1064 Change History
1065 Who When What
1066
1067 (reverse chronological order - newest change first)
1068 ***************************************************************/
1069
1070 cursor c1 is select ROWID from IGS_AD_TEST_SEGMENTS
1071 where TEST_SEGMENT_ID= X_TEST_SEGMENT_ID;
1072 l_mode VARCHAR2(1);
1073
1074 begin
1075
1076 l_mode := NVL(x_mode, 'R');
1077 open c1;
1078 fetch c1 into X_ROWID;
1079 if (c1%notfound) then
1080 close c1;
1081 INSERT_ROW (
1082 X_ROWID,
1083 X_MIN_SCORE,
1084 X_MAX_SCORE,
1085 X_INCLUDE_IN_COMP_SCORE,
1086 X_SCORE_IND,
1087 X_PERCENTILE_IND,
1088 X_NATIONAL_PERCENTILE_IND,
1089 X_STATE_PERCENTILE_IND,
1090 X_PERCENTILE_YEAR_RANK_IND,
1091 X_SCORE_BAND_UPPER_IND,
1092 X_SCORE_BAND_LOWER_IND,
1093 X_IRREGULARITY_CODE_IND,
1094 X_TEST_SEGMENT_ID,
1095 X_ADMISSION_TEST_TYPE,
1096 X_TEST_SEGMENT_NAME,
1097 X_DESCRIPTION,
1098 X_CLOSED_IND,
1099 l_MODE,
1100 X_SEGMENT_TYPE,
1101 X_SEGMENT_GROUP);
1102 return;
1103 end if;
1104 close c1;
1105 UPDATE_ROW (
1106 X_ROWID,
1107 X_MIN_SCORE,
1108 X_MAX_SCORE,
1109 X_INCLUDE_IN_COMP_SCORE,
1110 X_SCORE_IND,
1111 X_PERCENTILE_IND,
1112 X_NATIONAL_PERCENTILE_IND,
1113 X_STATE_PERCENTILE_IND,
1114 X_PERCENTILE_YEAR_RANK_IND,
1115 X_SCORE_BAND_UPPER_IND,
1116 X_SCORE_BAND_LOWER_IND,
1117 X_IRREGULARITY_CODE_IND,
1118 X_TEST_SEGMENT_ID,
1119 X_ADMISSION_TEST_TYPE,
1120 X_TEST_SEGMENT_NAME,
1121 X_DESCRIPTION,
1122 X_CLOSED_IND,
1123 l_MODE,
1124 X_SEGMENT_TYPE,
1125 X_SEGMENT_GROUP);
1126 end ADD_ROW;
1127 procedure DELETE_ROW (
1128 X_ROWID in VARCHAR2,
1129 x_mode IN VARCHAR2
1130 ) AS
1131 /*************************************************************
1132 Created By :samaresh.in
1133 Date Created By :15-May-2000
1134 Purpose :
1135 Know limitations, enhancements or remarks
1136 Change History
1137 Who When What
1138
1139 (reverse chronological order - newest change first)
1140 ***************************************************************/
1141
1142 begin
1143 Before_DML (
1144 p_action => 'DELETE',
1145 x_rowid => X_ROWID
1146 );
1147 IF (x_mode = 'S') THEN
1148 igs_sc_gen_001.set_ctx('R');
1149 END IF;
1150 delete from IGS_AD_TEST_SEGMENTS
1151 where ROWID = X_ROWID;
1152 if (sql%notfound) then
1153 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1154 igs_ge_msg_stack.add;
1155 igs_sc_gen_001.unset_ctx('R');
1156 app_exception.raise_exception;
1157 end if;
1158 IF (x_mode = 'S') THEN
1159 igs_sc_gen_001.unset_ctx('R');
1160 END IF;
1161
1162 After_DML (
1163 p_action => 'DELETE',
1164 x_rowid => X_ROWID
1165 );
1166 end DELETE_ROW;
1167 END igs_ad_test_segments_pkg;
[Home] [Help]