1 PACKAGE BODY IGS_OR_VAL_IA AS
2 /* $Header: IGSOR02B.pls 115.8 2003/11/19 10:40:09 gmaheswa ship $ */
3 --
4 -- bug id : 1956374
5 -- sjadhav , 29-aug-2001
6 -- removed function enrp_val_pc_closed
7 --
8
9 -----------------------------------------------------------------------------------------
10 --Change History:
11 --Who When What
12 --smadathi 28-AUG-2001 Bug No. 1956374 .The function genp_val_strt_end_dt removed
13 --smadathi 27-AUG-2001 Bug No. 1956374 .The function orgp_val_addr_type removed
14 --gmaheswa 18-Nov-2003 Bug No. 3227107 .Address Changes. modified cursors related to
15 -- address,start_dt and end_dt as to select only active address records.
16 -----------------------------------------------------------------------------------------
17 -- Validate that there is only one cor address per IGS_OR_INSTITUTION
18 FUNCTION orgp_val_ia_cor_addr(
19 p_institution_cd IN VARCHAR2 ,
20 p_addr_type IN VARCHAR2 ,
21 p_start_dt IN DATE ,
22 p_end_dt IN DATE ,
23 p_message_name OUT NOCOPY VARCHAR2 )
24 RETURN BOOLEAN AS
25 gv_other_detail VARCHAR2(255);
26 BEGIN
27 DECLARE
28 v_ia_count NUMBER(5);
29 CURSOR c_ia_count IS
30 SELECT count(*)
31 FROM IGS_CO_ADDR_TYPE adt
32 WHERE adt.addr_type = p_addr_type;
33 CURSOR c_ia IS
34 SELECT ia.addr_type,
35 ia.start_dt,
36 ia.end_dt
37 FROM IGS_OR_INST_ADDR ia
38 WHERE ia.institution_cd = p_institution_cd AND
39 ia.addr_type <> p_addr_type AND
40 ia.status = 'A' AND
41 ia.correspondence_ind = 'Y';
42 BEGIN
43 -- Validate that an organisational unit only has one active correspondant
44 -- address in any present or future time frame.
45 --- Set the default message number
46 p_message_name := null;
47 OPEN c_ia_count;
48 FETCH c_ia_count INTO v_ia_count;
49 CLOSE c_ia_count;
50 -- Check if the parameter address type has the correspondence indicator set
51 -- to 'TRUE'.
52 IF v_ia_count = 0 THEN
53 RETURN TRUE;
54 END IF;
55 -- The addr_type passed in as a parameter has the correspondence_ind set.
56 -- Now loop through the institution_addr records for the location to
57 -- determine if they have any other active records with an addr_type
58 -- (apart from the addr_type passed in as a parameter) which also has
59 -- the correspondence_ind set. If so set the error message.
60 FOR v_ia IN c_ia LOOP
61 --validate for date overlaps against the parameter details passed in.
62 --Validation will fail if any of the following are true:
63 -- a) The parameter start date is between the fetched record date range.
64 -- b) The parameter end date is between the fetched record date range.
65 -- c) The paramter dates overlap the entire fetched record range.
66 -- d) The parameter dates overlap the fetched record open_ended
67 -- date range.
68 -- e) The parameter dates overlap the fetched record start date.
69 -- f) The parameter dates overlap the fetched record open_ended
70 -- start date.
71 --An exception is when the fetched record end date is less than the current
72 -- date (ie SYSDATE). The reason for this is that even though the parameter
73 -- dates may overlap the dates of this record, the end date has already passed
74 -- and this date will not be used.
75 IF (v_ia.end_dt >= SYSDATE OR
76 v_ia.end_dt IS NULL) THEN
77 IF v_ia.end_dt IS NOT NULL THEN
78 IF (p_start_dt >= v_ia.start_dt AND
79 p_start_dt <= v_ia.end_dt)THEN
80 p_message_name := 'IGS_OR_ONE_CORR_ADDR_ACTIVE';
81 RETURN FALSE;
82 END IF;
83 IF p_end_dt IS NOT NULL THEN
84 IF (p_end_dt >= v_ia.start_dt AND
85 p_end_dt <= v_ia.end_dt) THEN
86 p_message_name := 'IGS_OR_ONE_CORR_ADDR_ACTIVE';
87 RETURN FALSE;
88 END IF;
89 IF (p_start_dt <= v_ia.start_dt AND
90 p_end_dt >= v_ia.end_dt) THEN
91 p_message_name := 'IGS_OR_ONE_CORR_ADDR_ACTIVE';
92 RETURN FALSE;
93 END IF;
94 ELSE
95 IF (p_start_dt <= v_ia.start_dt) THEN
96 p_message_name := 'IGS_OR_ONE_CORR_ADDR_ACTIVE';
97 RETURN FALSE;
98 END IF;
99 END IF;
100 ELSE
101 IF p_end_dt IS NOT NULL THEN
102 IF ( v_ia.start_dt >= p_start_dt AND
103 v_ia.start_dt <= p_end_dt) THEN
104 p_message_name := 'IGS_OR_ONE_CORR_ADDR_ACTIVE';
105 RETURN FALSE;
106 END IF;
107 IF (v_ia.start_dt <= p_start_dt) THEN
108 p_message_name := 'IGS_OR_ONE_CORR_ADDR_ACTIVE';
109 RETURN FALSE;
110 END IF;
111 END IF;
112 IF (p_end_dt IS NULL) THEN
113 p_message_name := 'IGS_OR_ONE_CORR_ADDR_ACTIVE';
114 RETURN FALSE;
115 END IF;
116 END IF;
117 END IF;
118 END LOOP;
119 --- Return the default value
120 RETURN TRUE;
121 END;
122 EXCEPTION
123 WHEN OTHERS THEN
124 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
125 IGS_GE_MSG_STACK.ADD;
126 App_Exception.Raise_Exception ;
127 END orgp_val_ia_cor_addr;
128 --
129 -- Validate that only one IGS_OR_INSTITUTION address is open per address type
130 FUNCTION orgp_val_ia_one_open(
131 p_institution_cd IN VARCHAR2 ,
132 p_addr_type IN VARCHAR2 ,
133 p_start_dt IN DATE ,
134 p_message_name OUT NOCOPY VARCHAR2 )
135 RETURN BOOLEAN AS
136 gv_other_detail VARCHAR2(255);
137 BEGIN
138 DECLARE
139 v_count NUMBER(5);
140 CURSOR c_ia IS
141 SELECT count(*)
142 FROM IGS_OR_INST_ADDR ia
143 WHERE ia.institution_cd = p_institution_cd AND
144 ia.addr_type = p_addr_type AND
145 ia.status = 'A' AND
146 ia.start_dt <> p_start_dt AND
147 ia.end_dt IS NULL;
148 BEGIN
149 --Validate the institution_addr table to ensure that only one record is open
150 --(ie; has a null end date).
151 --- Set the default message number
152 p_message_name := null;
153 OPEN c_ia;
154 FETCH c_ia into v_count;
155 CLOSE c_ia;
156 --If any open ended records, then return error.
157 IF v_count > 0 THEN
158 p_message_name := 'IGS_OR_MULTIPLE_INST_ADDR';
159 RETURN FALSE;
160 END IF;
161 --- Return the default value
162 RETURN TRUE;
163 END;
164 EXCEPTION
165 WHEN OTHERS THEN
166 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
167 IGS_GE_MSG_STACK.ADD;
168 App_Exception.Raise_Exception ;
169 END orgp_val_ia_one_open;
170 --
171 -- Validate that address dates do not overlap for an institution
172 FUNCTION orgp_val_ia_ovrlp(
173 p_institution_cd IN VARCHAR2 ,
174 p_addr_type IN VARCHAR2 ,
175 p_start_dt IN DATE ,
176 p_end_dt IN DATE ,
177 p_message_name OUT NOCOPY VARCHAR2 )
178 RETURN BOOLEAN AS
179 gv_other_detail VARCHAR2(255);
180 BEGIN
181 DECLARE
182 CURSOR c_ia (
183 cp_institution_cd IGS_OR_INST_ADDR.institution_cd%TYPE,
184 cp_addr_type IGS_OR_INST_ADDR.addr_type%TYPE) IS
185 SELECT ia.start_dt,
186 ia.end_dt
187 FROM IGS_OR_INST_ADDR ia
188 WHERE ia.institution_cd = cp_institution_cd AND
189 ia.addr_type = cp_addr_type AND
190 ia.status = 'A' AND
191 ia.start_dt <> p_start_dt;
192 v_end_dt IGS_OR_INST_ADDR.end_dt%TYPE;
193 BEGIN
194 -- This module checks that the institution_addr record,
195 -- which is being created or updated, does not overlap
196 -- with an existing record of the same type for the institution.
197 -- Set the default message number
198 p_message_name := null;
199 -- set p_end_dt to a high date if null
200 IF (p_end_dt IS NULL) THEN
201 v_end_dt := IGS_GE_DATE.IGSDATE('9999/01/01');
202 ELSE
203 v_end_dt := p_end_dt;
204 END IF;
205 -- Loop through the selected institution_addr records,
206 -- validating each record for date overlaps.
207 -- Do not validate against the record passed in.
208 -- Validation will fail if any of the following are true -
209 -- a. The current start date is between an existing date range.
210 -- b. The current end date is between an existing date range.
211 -- c. The current dates overlap an entire existing date range.
212 FOR v_ia_rec IN c_ia(
213 p_institution_cd,
214 p_addr_type) LOOP
215 -- check if p_start_dt between existing date range.
216 IF (p_start_dt >= v_ia_rec.start_dt) AND
217 (p_start_dt <= NVL(v_ia_rec.end_dt,IGS_GE_DATE.IGSDATE('9999/01/01'))) THEN
218 p_message_name:= 'IGS_EN_ADDRESS_DATES_OVERLAP';
219 RETURN FALSE;
220 END IF;
221 -- check if p_end_date between existing date range.
222 IF (v_end_dt >= v_ia_rec.start_dt) AND
223 (v_end_dt <= NVL(v_ia_rec.end_dt, IGS_GE_DATE.IGSDATE('9999/01/01'))) THEN
224 p_message_name:= 'IGS_EN_ADDRESS_DATES_OVERLAP';
225 RETURN FALSE;
226 END IF;
227 -- check if input dates overlap entire existing
228 -- date range.
229 IF (p_start_dt <= v_ia_rec.start_dt) AND
230 (v_end_dt >= NVL(v_ia_rec.end_dt, IGS_GE_DATE.IGSDATE('9999/01/01'))) THEN
231 p_message_name:= 'IGS_EN_ADDRESS_DATES_OVERLAP';
232 RETURN FALSE;
233 END IF;
234 END LOOP;
235 -- Return the default value
236 RETURN TRUE;
237 END;
238 EXCEPTION
239 WHEN OTHERS THEN
240 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
241 IGS_GE_MSG_STACK.ADD;
242 App_Exception.Raise_Exception ;
243 END orgp_val_ia_ovrlp;
244 --
245 --
246
247 PROCEDURE Validate_Address
248 (
249 p_city IN VARCHAR2,
250 p_state IN VARCHAR2,
251 p_province IN VARCHAR2,
252 p_county IN VARCHAR2,
253 p_country IN VARCHAR2,
254 p_postcode IN VARCHAR2,
255 p_valid_address OUT NOCOPY BOOLEAN,
256 p_error_msg OUT NOCOPY VARCHAR2
257 ) AS
258 BEGIN
259
260 -- Custom Validation Logic Implemented by the user.
261 -- After Validation, if the user finds this address is valid, do the following.
262 -- p_valid_address := TRUE;
263 -- p_error_msg := NULL;
264
265 -- If the address is not valid, the do the following.
266 -- p_valid_address := FALSE;
267 -- p_error_msg := p_city; -- The Parameter which is not valid. (e.g., p_city)
268
269 p_valid_address := TRUE;
270 p_error_msg := NULL;
271
272 END Validate_Address;
273
274 END IGS_OR_VAL_IA;
[Home] [Help]