1 PACKAGE BODY IGS_EN_VAL_UDDC AS
2 /* $Header: IGSEN71B.pls 115.3 2002/11/29 00:08:40 nsidana ship $ */
3 --
4 -- Validate the administrative unit status closed indicator
5 FUNCTION enrp_val_aus_closed(
6 p_aus IN VARCHAR2 ,
7 p_message_name OUT NOCOPY varchar2 )
8 RETURN BOOLEAN AS
9 BEGIN
10 DECLARE
11
12 v_closed_ind VARCHAR2(1);
13 CURSOR c_aus IS
14 SELECT closed_ind
15 FROM IGS_AD_ADM_UNIT_STAT
16 WHERE administrative_unit_status = p_aus;
17 BEGIN
18 -- Check if the IGS_AD_ADM_UNIT_STAT is closed
19 p_message_name := null;
20 OPEN c_aus;
21 FETCH c_aus INTO v_closed_ind;
22 IF (c_aus%NOTFOUND) THEN
23 CLOSE c_aus;
24 RETURN TRUE;
25 END IF;
26 IF (v_closed_ind = 'Y') THEN
27 p_message_name := 'IGS_EN_ADM_UNT_STAT_CLOSED';
28 CLOSE c_aus;
29 RETURN FALSE;
30 END IF;
31 CLOSE c_aus;
32 RETURN TRUE;
33 EXCEPTION
34 WHEN OTHERS THEN
35 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
36 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_UDDC.enrp_val_aus_closed');
37 IGS_GE_MSG_STACK.ADD;
38 App_Exception.Raise_Exception;
39 END;
40 END enrp_val_aus_closed;
41 --
42 -- Validate the AUS unit attempt status is 'DISCONTIN'
43 FUNCTION enrp_val_aus_discont(
44 p_aus IN VARCHAR2 ,
45 p_message_name OUT NOCOPY varchar2 )
46 RETURN BOOLEAN AS
47 BEGIN
48 DECLARE
49
50 v_unit_att_status IGS_AD_ADM_UNIT_STAT.unit_attempt_status%TYPE;
51 CURSOR c_aus IS
52 SELECT unit_attempt_status
53 FROM IGS_AD_ADM_UNIT_STAT
54 WHERE administrative_unit_status = p_aus;
55 BEGIN
56 -- Check if the IGS_AD_ADM_UNIT_STAT is closed
57 p_message_name := null;
58 OPEN c_aus;
59 FETCH c_aus INTO v_unit_att_status;
60 IF (c_aus%NOTFOUND) THEN
61 CLOSE c_aus;
62 RETURN TRUE;
63 END IF;
64 IF (v_unit_att_status = 'DISCONTIN') THEN
65 CLOSE c_aus;
66 RETURN TRUE;
67 END IF;
68 p_message_name := 'IGS_EN_GRAD_ONLY_APPL_ADMN';
69 CLOSE c_aus;
70 RETURN FALSE;
71 EXCEPTION
72 WHEN OTHERS THEN
73 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
74 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_UDDC.enrp_val_aus_discont');
75 IGS_GE_MSG_STACK.ADD;
76 App_Exception.Raise_Exception;
77 END;
78 END enrp_val_aus_discont;
79 --
80 -- To validate TEACHING date alias.
81 FUNCTION enrp_val_teaching_da(
82 p_dt_alias IN VARCHAR2 ,
83 p_message_name OUT NOCOPY varchar2 )
84 RETURN BOOLEAN AS
85 BEGIN
86 DECLARE
87
88 v_closed_ind IGS_CA_DA.closed_ind%TYPE;
89 v_s_cal_cat IGS_CA_DA.s_cal_cat%TYPE;
90 CURSOR c_dt_alias IS
91 SELECT s_cal_cat,
92 closed_ind
93 FROM IGS_CA_DA
94 WHERE dt_alias = p_dt_alias;
95 BEGIN
96 -- This module checks if the IGS_CA_DA is closed
97 -- and if the SI_CA_S_CA_CAT is specified, then it must
98 -- be 'TEACHING'
99 p_message_name := null;
100 OPEN c_dt_alias;
101 FETCH c_dt_alias INTO v_s_cal_cat,
102 v_closed_ind;
103 -- check if a record has been found
104 IF (c_dt_alias%NOTFOUND) THEN
105 CLOSE c_dt_alias;
106 RETURN TRUE;
107 END IF;
108 CLOSE c_dt_alias;
109 -- check if the closed_ind is 'Y'
110 IF (v_closed_ind = 'Y') THEN
111 p_message_name := 'IGS_CA_DTALIAS_IS_CLOSED';
112 RETURN FALSE;
113 END IF;
114 -- check if the SI_CA_S_CA_CAT is not null and not of
115 -- type teaching
116 IF (v_s_cal_cat IS NOT NULL AND v_s_cal_cat <> 'TEACHING') THEN
117 p_message_name := 'IGS_EN_DA_MUST_BE_CAT_TEACHIN';
118 RETURN FALSE;
119 END IF;
120 RETURN TRUE;
121 EXCEPTION
122 WHEN OTHERS THEN
123 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
124 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_UDDC.enrp_val_teaching_da');
125 IGS_GE_MSG_STACK.ADD;
126 App_Exception.Raise_Exception;
127 END;
128 END enrp_val_teaching_da;
129 --
130 -- Validate either the admin unit status or delete indicator is set.
131 FUNCTION enrp_val_uddc_fields(
132 p_aus IN VARCHAR2 ,
133 p_delete_ind IN VARCHAR2 DEFAULT 'N',
134 p_message_name OUT NOCOPY varchar2 )
135 RETURN BOOLEAN AS
136
137 BEGIN
138 DECLARE
139 BEGIN
140 --When inserting/updating unit discontinuation date criteria, ensure that
141 -- either the IGS_AD_ADM_UNIT_STAT field or the delete_ind field
142 -- is set.
143 p_message_name := null;
144 IF (p_aus IS NULL AND
145 p_delete_ind <> 'Y') THEN
146 p_message_name := 'IGS_EN_ADMIN_UNIT_ST_DEL';
147 RETURN FALSE;
148 END IF;
149 IF (p_aus IS NOT NULL AND
150 p_delete_ind = 'Y') THEN
151 p_message_name := 'IGS_EN_ADMIN_UNIT_ST_NOTSET';
152 RETURN FALSE;
153 END IF;
154 --- Return the default value
155 RETURN TRUE;
156 END;
157 EXCEPTION
158 WHEN OTHERS THEN
159 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
160 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_VAL_UDDC.enrp_val_uddc_fields');
161 IGS_GE_MSG_STACK.ADD;
162 App_Exception.Raise_Exception;
163 END enrp_val_uddc_fields;
164 END IGS_EN_VAL_UDDC;