1 PACKAGE BODY IGS_OR_VAL_LR AS
2 /* $Header: IGSOR07B.pls 115.4 2002/11/29 01:47:32 nsidana ship $ */
3 /* change history
4 who when what
5 npalanis 20-apr-2002 The call to IGS_OR_VAL_LR.assp_val_lr_dflt_one in assp_val_lr_dfltslot
6 is removed because the code is now transferred to post forms commit
7 BUG - 2322096
8 */
9
10 -- Validate the location relationship.
11 FUNCTION orgp_val_lr(
12 p_location_cd IN VARCHAR2 ,
13 p_sub_location_cd IN VARCHAR2 ,
14 p_message_name OUT NOCOPY VARCHAR2 )
15 RETURN BOOLEAN AS
16 -- Local function : to perform recursive loop of the location relationship.
17 FUNCTION orgp_val_lr_loop (
18 p_location_cd IN IGS_AD_LOCATION.location_cd%TYPE)
19 RETURN BOOLEAN
20 IS
21 CURSOR c_lr IS
22 SELECT location_cd
23 FROM IGS_AD_LOCATION_REL
24 WHERE sub_location_cd = p_location_cd;
25 v_valid BOOLEAN DEFAULT TRUE;
26 v_other_detail VARCHAR2(255);
27 BEGIN
28 FOR lr IN c_lr LOOP
29 IF lr.location_cd = p_sub_location_cd THEN
30 v_valid := FALSE;
31 EXIT;
32 END IF;
33 IF orgp_val_lr_loop (lr.location_cd) = FALSE THEN
34 v_valid := FALSE;
35 EXIT;
36 END IF;
37 END LOOP;
38 RETURN v_valid;
39 EXCEPTION
40 WHEN OTHERS THEN
41 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
42 IGS_GE_MSG_STACK.ADD;
43 App_Exception.Raise_Exception ;
44 END orgp_val_lr_loop;
45 BEGIN
46 p_message_name := NULL;
47 -- Validate the closed indicator for the owning location code.
48 IF orgp_val_loc_cd (p_location_cd, p_message_name) = FALSE THEN
49 RETURN FALSE;
50 END IF;
51 -- Validate the closed indicator for the sub-location code.
52 IF orgp_val_loc_cd (p_sub_location_cd, p_message_name) = FALSE THEN
53 RETURN FALSE;
54 END IF;
55 -- Validate the sub-location is not the same as the location
56 IF p_location_cd = p_sub_location_cd THEN
57 p_message_name := 'IGS_GE_INVALID_VALUE';
58 RETURN FALSE;
59 END IF;
60 -- Validate the location structure to ensure the sub-location
61 -- does not appear further up the structure.
62 IF orgp_val_lr_loop (p_location_cd) = FALSE THEN
63 p_message_name := 'IGS_OR_OWN_SUB_LOC_REL_XS';
64 RETURN FALSE;
65 END IF;
66 RETURN TRUE;
67 END orgp_val_lr;
68 --
69 -- Validate the location.
70 FUNCTION orgp_val_loc_cd(
71 p_location_cd IN VARCHAR2 ,
72 p_message_name OUT NOCOPY VARCHAR2 )
73 RETURN BOOLEAN AS
74 CURSOR c_loc IS
75 SELECT closed_ind
76 FROM IGS_AD_LOCATION
77 WHERE location_cd = p_location_cd
78 AND closed_ind = 'Y';
79 v_other_detail VARCHAR2(255);
80 BEGIN
81 p_message_name := NULL;
82 FOR loc IN c_loc LOOP
83 p_message_name := 'IGS_OR_LOCATION_CLOSED';
84 RETURN FALSE;
85 END LOOP;
86 RETURN TRUE;
87 EXCEPTION
88 WHEN OTHERS THEN
89 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
90 IGS_GE_MSG_STACK.ADD;
91 App_Exception.Raise_Exception ;
92 END orgp_val_loc_cd;
93 --
94 -- Retrofitted
95 FUNCTION assp_val_lr_dfltslot(
96 p_location_cd IGS_AD_LOCATION_REL.location_cd%TYPE ,
97 p_sub_location_cd IGS_AD_LOCATION_REL.sub_location_cd%TYPE ,
98 p_dflt_ind IGS_AD_LOCATION_REL.dflt_ind%TYPE ,
99 p_message_name OUT NOCOPY VARCHAR2 )
100 RETURN BOOLEAN AS
101 gv_other_detail VARCHAR2(255);
102 BEGIN -- assp_val_lr_dfltslot
103 -- Can only set the default indicator when:
104 -- parent location has an s_location_type = CAMPUS and
105 -- child location has an s_location_type = EXAM_CTR or GRD_CTR
106 DECLARE
107 v_s_location_type IGS_AD_LOCATION_TYPE.s_location_type%TYPE;
108 v_sub_s_location_type IGS_AD_LOCATION_TYPE.s_location_type%TYPE;
109 v_message_name VARCHAR2(30);
110 BEGIN
111 -- 1. Set the default message number
112 p_message_name := NULL;
113 IF (p_dflt_ind = 'Y') THEN
114 -- 1. Validate system location types are appropriate for setting
115 -- the default indicator
116 -- Fetch system location type for parent location.
117 v_s_location_type := IGS_OR_GEN_001.ORGP_GET_S_LOC_TYPE(
118 p_location_cd);
119 IF (NVL(v_s_location_type, '-1') <> 'CAMPUS') THEN
120 -- The system location type for the parent location must be
121 -- of type 'CAMPUS' when setting the default indicator.
122 p_message_name := 'IGS_AS_SYS_LOCTYPE_CAMPUS';
123 RETURN FALSE;
124 END IF;
125 -- Fetch system location type for a child locations.
126 v_sub_s_location_type := IGS_OR_GEN_001.ORGP_GET_S_LOC_TYPE(
127 p_sub_location_cd);
128 IF (NVL(v_sub_s_location_type, '-1') <> 'EXAM_CTR' AND
129 NVL(v_sub_s_location_type, '-1') <> 'GRD_CTR') THEN
130 -- The system location type for the child location must be
131 -- of type EXAM_CTR or GRD_CTR when setting the default indicator.
132 p_message_name := 'IGS_AS_SYS_LOCTYPE_EXAMCTR';
133 RETURN FALSE;
134 END IF;
135
136 END IF;
137 -- 3. Validation successful
138 RETURN TRUE;
139 END;
140 EXCEPTION
141 WHEN OTHERS THEN
142 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
143 IGS_GE_MSG_STACK.ADD;
144 App_Exception.Raise_Exception ;
145 END assp_val_lr_dfltslot;
146 --
147 -- Retrofitted
148 FUNCTION assp_val_lr_dflt_one(
149 p_location_cd IGS_AD_LOCATION_REL.location_cd%TYPE ,
150 p_sub_location_cd IGS_AD_LOCATION_REL.sub_location_cd%TYPE ,
151 p_sub_s_location_type IN VARCHAR2 ,
152 p_message_name OUT NOCOPY VARCHAR2 )
153 RETURN BOOLEAN AS
154 gv_other_detail VARCHAR2(255);
155 BEGIN -- assp_val_lr_dflt_one
156 -- This module validates that a single location (system location type of
157 -- CAMPUS) can only have <= 1 default exam location (system location type
158 -- of EXAM_CTR or GRD_CTR). This validation is invoked from
159 -- ASSP_VAL_LR_DFLTSLOT validation.
160 DECLARE
161 CURSOR c_lr IS
162 SELECT 'x'
163 FROM IGS_AD_LOCATION_REL lr,
164 IGS_AD_LOCATION loc,
165 IGS_AD_LOCATION_TYPE lot
166 WHERE lr.location_cd = p_location_cd AND
167 lr.sub_location_cd <> p_sub_location_cd AND
168 lr.dflt_ind = 'Y' AND
169 lr.sub_location_cd = loc.location_cd AND
170 loc.location_type = lot.location_type AND
171 lot.s_location_type = p_sub_s_location_type;
172 v_lr_exists VARCHAR2(1);
173 BEGIN
174 -- Set the default message number
175 p_message_name := NULL;
176 OPEN c_lr;
177 FETCH c_lr INTO v_lr_exists;
178 IF (c_lr%FOUND) THEN
179 CLOSE c_lr;
180 -- Only one location relationship for any particular parent
181 -- location may have the default indicator set.
182 p_message_name := 'IGS_AS_LOC_ONLYONE_SUBLOC';
183 RETURN FALSE;
184 END IF;
185 CLOSE c_lr;
186 -- Validation successful.
187 RETURN TRUE;
188 END;
189 EXCEPTION
190 WHEN OTHERS THEN
191 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
192 IGS_GE_MSG_STACK.ADD;
193 App_Exception.Raise_Exception ;
194 END assp_val_lr_dflt_one;
195 --
196 -- Retrofitted
197 FUNCTION assp_val_lr_lr(
198 p_location_cd IGS_AD_LOCATION_REL.location_cd%TYPE ,
199 p_sub_location_cd IGS_AD_LOCATION_REL.sub_location_cd%TYPE ,
200 p_message_name OUT NOCOPY VARCHAR2 )
201 RETURN BOOLEAN AS
202 gv_other_detail VARCHAR2(255);
203 BEGIN -- assp_val_lr_lr
204 -- Can not make a location with a system location type of CAMPUS
205 -- a child of a location with a system location type of EXAM_CTR or GRD_CTR
206 DECLARE
207 v_s_location_type IGS_AD_LOCATION_TYPE.s_location_type%TYPE;
208 v_sub_s_location_type IGS_AD_LOCATION_TYPE.s_location_type%TYPE;
209 BEGIN
210 -- 1. Set the default message number
211 p_message_name := NULL;
212 -- 2. Check system location type for the locations in the relationship
213 -- Fetch system location type for parent IGS_AD_LOCATION.
214 v_s_location_type := IGS_OR_GEN_001.ORGP_GET_S_LOC_TYPE(
215 p_location_cd);
216 -- Fetch system location type for a child locations.
217 v_sub_s_location_type := IGS_OR_GEN_001.ORGP_GET_S_LOC_TYPE(
218 p_sub_location_cd);
219 IF ( (NVL(v_s_location_type, '-1') = 'GRD_CTR' OR
220 NVL(v_s_location_type, '-1') = 'EXAM_CTR') AND
221 NVL(v_sub_s_location_type, '-1') = 'CAMPUS') THEN
222 -- The system location type for the parent location can not be
223 -- of type EXAM_CTR or GRD_CTR when the system location type for the child
224 -- location is of type 'CAMPUS'.
225 p_message_name := 'IGS_AS_SYS_LCOTYPE_GRD_CTR';
226 RETURN FALSE;
227 END IF;
228 -- 3. Validation successful
229 RETURN TRUE;
230 END;
231 EXCEPTION
232 WHEN OTHERS THEN
233 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
234 IGS_GE_MSG_STACK.ADD;
235 App_Exception.Raise_Exception ;
236 END assp_val_lr_lr;
237 END IGS_OR_VAL_LR;
[Home] [Help]