1 PACKAGE BODY IGS_AS_VAL_VEA AS
2 /* $Header: IGSAS38B.pls 115.7 2002/11/28 22:48:44 nsidana ship $ */
3
4 --
5 -- bug id : 1956374
6 -- sjadhav ,29-aug-2001
7 -- removed function enrp_val_pc_closed
8 --
9 -------------------------------------------------------------------------------------------
10 --Change History:
11 --Who When What
12 --smadathi 29-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 -------------------------------------------------------------------------------------------
15 -- Retrofitted
16 FUNCTION assp_val_vea_coraddr(
17 p_venue_cd IGS_AD_LOCVENUE_ADDR.LOCATION_venue_cd%TYPE ,
18 p_addr_type FND_LOOKUP_VALUES.LOOKUP_CODE%TYPE ,
19 p_start_dt HZ_LOCATIONS.ADDRESS_EFFECTIVE_DATE%TYPE ,
20 p_end_dt HZ_LOCATIONS.ADDRESS_EXPIRATION_DATE%TYPE ,
21 p_message_name OUT NOCOPY VARCHAR2)
22 RETURN BOOLEAN IS
23 gv_other_detail VARCHAR2(255);
24 return_val BOOLEAN;
25 BEGIN --assp_val_vea_coraddr
26 --This module validates that a IGS_GR_VENUE has only one active correspondence
27 --address in any parent or future time frame
28 DECLARE
29 v_adt_rec VARCHAR2(1);
30 CURSOR c_adt IS
31 SELECT 'X'
32 FROM IGS_CO_ADDR_TYPE
33 WHERE addr_type = p_addr_type;
34 CURSOR c_vea IS
35 SELECT vea.start_dt,
36 vea.end_dt
37 FROM IGS_GR_VENUE_ADDR vea
38 WHERE vea.correspondence_ind = 'Y' AND
39 vea.addr_type <> p_addr_type AND
40 vea.venue_cd = p_venue_cd;
41 BEGIN
42 --- Set the default message number
43 p_message_name := null;
44 -- Set the default return value
45 return_val := TRUE;
46 --Check if the parameter address type has the correpondence indicator set to
47 --'TRUE'
48 OPEN c_adt;
49 FETCH c_adt INTO v_adt_rec;
50 IF (c_adt%NOTFOUND) THEN
51 CLOSE c_adt;
52 RETURN return_val;
53 END IF;
54 CLOSE c_adt;
55 --The IGS_CO_ADDR_TYPE passed in as a parameter has the correspondence_ind set.
56 --Now loop through the IGS_GR_VENUE_ADDR records for the IGS_GR_VENUE to determine if they
57 --have any other active records with an IGS_CO_ADDR_TYPE
58 --(apart from the IGS_CO_ADDR_TYPE passed in as a parameter)
59 --which also has the correspondence_ind set. If so, set the error message.
60 FOR v_vea_rec IN c_vea LOOP
61 IF (v_vea_rec.end_dt >= SYSDATE OR
62 v_vea_rec.end_dt IS NULL) THEN
63 --Validate for date overlaps against the parameter details passed in
64 --Validation will fail if any of the following numbered points are true
65 IF (v_vea_rec.end_dt IS NOT NULL) THEN
66 --1. The parameter start date is between the fetched record date range
67 IF (p_start_dt BETWEEN v_vea_rec.start_dt AND v_vea_rec.end_dt) THEN
68 p_message_name := 'IGS_AS_ADDTYPE_USED_COR_PURPO';
69 return_val := FALSE;
70 EXIT;
71 END IF;
72 IF (p_end_dt IS NOT NULL) THEN
73 --2. The parameter end date is between the fetched record date range
74 IF (p_end_dt BETWEEN v_vea_rec.start_dt AND v_vea_rec.end_dt) THEN
75 p_message_name := 'IGS_AS_ADDTYPE_USED_COR_PURPO';
76 return_val := FALSE;
77 EXIT;
78 END IF;
79 --3. The parameter dates overlap the entire fetched record date range
80 IF (p_start_dt <= v_vea_rec.start_dt AND
81 p_end_dt >= v_vea_rec.end_dt) THEN
82 p_message_name := 'IGS_AS_ADDTYPE_USED_COR_PURPO';
83 return_val := FALSE;
84 EXIT;
85 END IF;
86 END IF;
87 IF (p_end_dt IS NULL) THEN
88 --4. The parameter date range overlaps the fetched record open-ended date
89 --range
90 IF (p_start_dt <= v_vea_rec.start_dt) THEN
91 p_message_name := 'IGS_AS_ADDTYPE_USED_COR_PURPO';
92 return_val := FALSE;
93 EXIT;
94 END IF;
95 END IF;
96 END IF;
97 IF (v_vea_rec.end_dt IS NULL) THEN
98 IF (p_end_dt IS NOT NULL) THEN
99 --5. The parameter dates overlap the fetched record start date
100 IF (v_vea_rec.start_dt BETWEEN p_start_dt AND p_end_dt) THEN
101 p_message_name := 'IGS_AS_ADDTYPE_USED_COR_PURPO';
102 return_val := FALSE;
103 EXIT;
104 END IF;
105 --6 The parameter dates overlap the fetched record open-ended start date
106 IF (v_vea_rec.start_dt <= p_start_dt) THEN
107 p_message_name := 'IGS_AS_ADDTYPE_USED_COR_PURPO';
108 return_val := FALSE;
109 EXIT;
110 END IF;
111 END IF;
112 IF ( p_end_dt IS NULL) THEN
113 p_message_name := 'IGS_AS_ADDTYPE_USED_COR_PURPO';
114 return_val := FALSE;
115 EXIT;
116 END IF;
117 END IF;
118 END IF;
119 END LOOP;
120 RETURN return_val;
121 END;
122 EXCEPTION
123 WHEN OTHERS THEN
124 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
125 Fnd_Message.Set_Token('NAME','IGS_AS_VAL_VEA.ASSP_VAL_VEA_CORADDR');
126 Igs_Ge_Msg_stack.Add;
127 App_Exception.Raise_Exception;
128 END assp_val_vea_coraddr;
129 --
130 -- Retrofitted
131 FUNCTION ASSP_VAL_VEA_OVRLP(
132 p_venue_cd IGS_AD_LOCVENUE_ADDR.location_venue_cd%TYPE ,
133 p_addr_type FND_LOOKUP_VALUES.LOOKUP_CODE%TYPE ,
134 p_start_dt HZ_LOCATIONs.ADDRESS_EFFECTIVE_DATE%TYPE ,
135 p_end_dt HZ_LOCATIONS.ADDRESS_EXPIRATION_DATE%TYPE ,
136 p_message_name OUT NOCOPY VARCHAR2)
137 RETURN BOOLEAN IS
138 gv_other_detail VARCHAR2(255);
139 BEGIN -- assp_val_vea_ovrlp
140 -- Validate that the IGS_GR_VENUE_ADDR record being created or updated does not
141 -- overlap with an existing address record of the same type for the IGS_GR_VENUE.
142 DECLARE
143 v_start_dt IGS_GR_VENUE_ADDR.start_dt%TYPE;
144 v_end_dt IGS_GR_VENUE_ADDR.end_dt%TYPE;
145 v_p_end_dt IGS_GR_VENUE_ADDR.end_dt%TYPE;
146 CURSOR c_va IS
147 SELECT IGS_GR_VENUE_ADDR.start_dt,
148 NVL(IGS_GR_VENUE_ADDR.end_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
149 FROM IGS_GR_VENUE_ADDR
150 WHERE venue_cd = p_venue_cd AND
151 addr_type = p_addr_type AND
152 start_dt <> p_start_dt;
153 BEGIN
154 p_message_name := null;
155 -- set p_end_dt to a high date if null
156
157 v_p_end_dt := NVL(p_end_dt,IGS_GE_DATE.IGSDATE('YYYY/MM/DD'));
158 -- v_p_end_dt := NVL(p_end_dt, TO_DATE('01/01/9999','DD/MM/YYYY'));
159 OPEN c_va;
160 -- Validation will fail if any of the following are true
161 LOOP
162 EXIT WHEN (c_va%NOTFOUND);
163 FETCH c_va INTO v_start_dt,
164 v_end_dt;
165 -- (a) The current start date is between an existing date range.
166 IF (p_start_dt >= v_start_dt AND
167 p_start_dt <= v_end_dt) THEN
168 CLOSE c_va;
169 p_message_name := 'IGS_EN_ADO_STDT_BTWN_STDT';
170 RETURN FALSE;
171 END IF;
172 -- (b) The current end date is between an existing date range.
173 IF (v_p_end_dt >= v_start_dt AND
174 v_p_end_dt <= v_end_dt) THEN
175 CLOSE c_va;
176 p_message_name := 'IGS_EN_ADO_ENDDT_BTWN_ENDDT';
177 RETURN FALSE;
178 END IF;
179 -- (c) The current dates overlap an entire existing date range.
180 IF (p_start_dt <= v_start_dt AND
181 v_p_end_dt >= v_end_dt) THEN
182 CLOSE c_va;
183 p_message_name := 'IGS_EN_ADO_DT_OVERLAP';
184 Return FALSE;
185 END IF;
186 END LOOP;
187 CLOSE c_va;
188 RETURN TRUE;
189 END;
190 EXCEPTION
191 WHEN OTHERS THEN
192 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
193 Fnd_Message.Set_Token('NAME','IGS_AS_VAL_VEA.ASSP_VAL_VEA_OVRLP');
194 Igs_Ge_Msg_stack.Add;
195 App_Exception.Raise_Exception;
196 END assp_val_vea_ovrlp;
197 --
198 --
199 --
200 PROCEDURE Validate_Address
201 (
202 p_city IN VARCHAR2,
203 p_state IN VARCHAR2,
204 p_province IN VARCHAR2,
205 p_county IN VARCHAR2,
206 p_country IN VARCHAR2,
207 p_postcode IN VARCHAR2,
208 p_valid_address OUT NOCOPY BOOLEAN,
209 p_error_msg OUT NOCOPY VARCHAR2
210 ) IS
211 BEGIN
212
213 -- Custom Validation Logic Implemented by the user.
214 -- After Validation, if the user finds this address is valid, do the following.
215 -- p_valid_address := TRUE;
216 -- p_error_msg := NULL;
217
218 -- If the address is not valid, the do the following.
219 -- p_valid_address := FALSE;
220 -- p_error_msg := p_city; -- The Parameter which is not valid. (e.g., p_city)
221
222 p_valid_address := TRUE;
223 p_error_msg := NULL;
224
225 END Validate_Address;
226
227 END IGS_AS_VAL_VEA;
[Home] [Help]