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