1 PACKAGE BODY IGS_PS_VAL_POSp AS
2 /* $Header: IGSPS51B.pls 115.4 2002/11/29 03:06:23 nsidana ship $ */
3
4 -- Validate the calendar type is categorised teaching and is not closed.
5 FUNCTION crsp_val_posp_cat(
6 p_cal_type IN IGS_CA_TYPE.cal_type%TYPE ,
7 p_message_name OUT NOCOPY VARCHAR2)
8 RETURN BOOLEAN AS
9 gv_other_detail VARCHAR2(255);
10 BEGIN -- crsp_val_posp_cat
11 -- Validate the cal_type is not closed and the s_cal_cat is set to
12 -- 'TEACHING'.
13 DECLARE
14 v_cat_closed_ind IGS_CA_TYPE.closed_ind%TYPE;
15 v_cat_s_cal_cat IGS_CA_TYPE.s_cal_cat%TYPE;
16 CURSOR c_cat IS
17 SELECT cat.closed_ind,
18 cat.s_cal_cat
19 FROM IGS_CA_TYPE cat
20 WHERE cat.cal_type = p_cal_type;
21 BEGIN
22 IF p_cal_type IS NULL THEN
23 p_message_name := null;
24 RETURN TRUE;
25 ELSE
26 OPEN c_cat;
27 FETCH c_cat INTO v_cat_closed_ind,
28 v_cat_s_cal_cat;
29 IF (c_cat%FOUND) THEN
30 -- Check if the calendar type is closed
31 IF v_cat_closed_ind = 'Y' THEN
32 CLOSE c_cat;
33 p_message_name:= 'IGS_CA_CALTYPE_CLOSED';
34 RETURN FALSE;
35 END IF;
36 -- Check if the calendar type is of category 'TEACHING'
37 IF v_cat_s_cal_cat <> 'TEACHING' THEN
38 CLOSE c_cat;
39 p_message_name:= 'IGS_PS_CALTYPE_TEACHING_CAL';
40 RETURN FALSE;
41 END IF;
42 END IF;
43 CLOSE c_cat;
44 END IF;
45 p_message_name := null;
46 RETURN TRUE;
47 EXCEPTION
48 WHEN OTHERS THEN
49 IF (c_cat%ISOPEN) THEN
50 CLOSE c_cat;
51 END IF;
52 RAISE;
53 END;
54 END crsp_val_posp_cat;
55 --
56 -- Validate future relationship between IGS_CA_TYPE and teach_cal_type.
57 FUNCTION crsp_val_posp_cir(
58 p_cal_type IN IGS_CA_TYPE.cal_type%TYPE ,
59 p_teach_cal_type IN IGS_CA_TYPE.cal_type%TYPE ,
60 p_message_name OUT NOCOPY VARCHAR2)
61 RETURN BOOLEAN AS
62 BEGIN -- crsp_val_posp_cir
63 -- Warn the user if there isn't a relationship between future instances
64 -- of the IGS_CA_TYPE and teach_cal_type, with the teaching calendar as the
65 -- subordinate to the academic calendar.
66 DECLARE
67 v_dummy VARCHAR(1);
68 CURSOR c_cir_ci IS
69 SELECT 'X'
70 FROM IGS_CA_INST_REL cir,
71 IGS_CA_INST ci_sub,
72 IGS_CA_INST ci_sup
73 WHERE cir.sub_cal_type = p_teach_cal_type AND
74 cir.sub_cal_type = ci_sub.cal_type AND
75 cir.sub_ci_sequence_number = ci_sub.sequence_number AND
76 ci_sub.end_dt > SYSDATE AND
77 cir.sup_cal_type = p_cal_type AND
78 cir.sup_cal_type = ci_sup.cal_type AND
79 cir.sup_ci_sequence_number = ci_sup.sequence_number AND
80 ci_sup.end_dt > SYSDATE;
81 BEGIN
82 IF p_cal_type IS NULL OR
83 p_teach_cal_type IS NULL THEN
84 p_message_name := NULL;
85 RETURN TRUE;
86 ELSE
87 -- Check for IGS_CA_INST_REL records where the p_teach_cal_type
88 -- is the sub_cal_type and the p_cal_type is the sup_cal_type and the
89 -- related calendar_instance records have an end date greater than todays
90 -- date
91 OPEN c_cir_ci;
92 FETCH c_cir_ci INTO v_dummy;
93 IF (c_cir_ci%NOTFOUND) THEN
94 CLOSE c_cir_ci;
95 p_message_name := 'IGS_PS_NO_FUTURE_CAL_INST_REL';
96 RETURN FALSE;
97 END IF;
98 CLOSE c_cir_ci;
99 END IF;
100 p_message_name := NULL;
101 RETURN TRUE;
102 EXCEPTION
103 WHEN OTHERS THEN
104 IF (c_cir_ci%ISOPEN) THEN
105 CLOSE c_cir_ci;
106 END IF;
107 App_Exception.Raise_Exception;
108 END;
109 EXCEPTION
110 WHEN OTHERS THEN
111 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
112 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_POSp.crsp_val_posp_cir');
113 IGS_GE_MSG_STACK.ADD;
114 App_Exception.Raise_Exception;
115 END crsp_val_posp_cir;
116 --
117 -- Validate pattern of study period record is unique.
118 FUNCTION crsp_val_posp_iu(
119 p_course_cd IN VARCHAR2 ,
120 p_version_number IN NUMBER ,
121 p_cal_type IN VARCHAR2 ,
122 p_pos_sequence_number IN NUMBER ,
123 p_sequence_number IN NUMBER ,
124 p_acad_period_num IN NUMBER ,
125 p_teach_cal_type IN VARCHAR2 ,
126 p_message_name OUT NOCOPY VARCHAR2)
127 RETURN BOOLEAN AS
128 BEGIN -- crsp_val_posp_iu
129 -- Validate IGS_PS_PAT_STUDY_PRD records. More than one record cannot
130 -- exist with the same academic period number and teaching calendar type
131 -- for a parent IGS_PS_PAT_OF_STUDY.
132 DECLARE
133 v_dummy VARCHAR(1);
134 CURSOR c_posp IS
135 SELECT 'X'
136 FROM IGS_PS_PAT_STUDY_PRD posp
137 WHERE posp.course_cd = p_course_cd AND
138 posp.version_number = p_version_number AND
139 posp.cal_type = p_cal_type AND
140 posp.pos_sequence_number = p_pos_sequence_number AND
141 (p_sequence_number IS NULL OR
142 posp.sequence_number <> p_sequence_number) AND
143 posp.acad_period_num = p_acad_period_num AND
144 posp.teach_cal_type = p_teach_cal_type;
145 BEGIN
146 IF p_course_cd IS NULL OR
147 p_version_number IS NULL OR
148 p_cal_type IS NULL OR
149 p_pos_sequence_number IS NULL OR
150 p_acad_period_num IS NULL OR
151 p_teach_cal_type IS NULL THEN
152 p_message_name := NULL;
153 RETURN TRUE;
154 ELSE
155 OPEN c_posp;
156 FETCH c_posp INTO v_dummy;
157 IF (c_posp%FOUND) THEN
158 CLOSE c_posp;
159 p_message_name := 'IGS_GE_RECORD_ALREADY_EXISTS';
160 RETURN FALSE;
161 END IF;
162 CLOSE c_posp;
163 END IF;
164 p_message_name := NULL;
165 RETURN TRUE;
166 EXCEPTION
167 WHEN OTHERS THEN
168 IF (c_posp%ISOPEN) THEN
169 CLOSE c_posp;
170 END IF;
171 App_Exception.Raise_Exception;
172 END;
173 EXCEPTION
174 WHEN OTHERS THEN
175 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
176 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_POSp.crsp_val_posp_iu');
177 IGS_GE_MSG_STACK.ADD;
178 App_Exception.Raise_Exception;
179 END crsp_val_posp_iu;
180 --
181 END IGS_PS_VAL_POSp;
[Home] [Help]