[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_LOCATION_ADDR_PKG
Source
1 PACKAGE BODY IGS_AD_LOCATION_ADDR_PKG AS
2 /* $Header: IGSAI45B.pls 120.2 2006/06/23 05:49:03 gmaheswa noship $ */
3
4 PROCEDURE INSERT_ROW (
5 x_rowid IN OUT NOCOPY VARCHAR2,
6 x_location_venue_addr_id IN OUT NOCOPY NUMBER,
7 x_location_id IN OUT NOCOPY NUMBER,
8 x_location_venue_cd IN VARCHAR2,
9 x_start_dt IN DATE,
10 x_end_dt IN DATE,
11 x_country IN VARCHAR2,
12 x_address_style IN VARCHAR2,
13 x_addr_line_1 IN VARCHAR2,
14 x_addr_line_2 IN VARCHAR2,
15 x_addr_line_3 IN VARCHAR2,
16 x_addr_line_4 IN VARCHAR2,
17 x_date_last_verified IN DATE,
18 x_correspondence IN VARCHAR2,
19 x_city IN VARCHAR2,
20 x_state IN VARCHAR2,
21 x_province IN VARCHAR2,
22 x_county IN VARCHAR2,
23 x_postal_code IN VARCHAR2,
24 x_address_lines_phonetic IN VARCHAR2,
25 x_delivery_point_code IN VARCHAR2,
26 x_other_details_1 IN VARCHAR2,
27 x_other_details_2 IN VARCHAR2,
28 x_other_details_3 IN VARCHAR2,
29 x_source_type IN VARCHAR2,
30 x_contact_person IN VARCHAR2 default NULL,
31 x_msg_data OUT NOCOPY VARCHAR2,
32 l_return_status OUT NOCOPY VARCHAR2,
33 x_object_version_number IN OUT NOCOPY NUMBER,
34 X_MODE IN VARCHAR2 DEFAULT 'R'
35 ) AS
36
37 p_location_rec_insert HZ_LOCATION_V2PUB.location_rec_type;
38
39 l_msg_count NUMBER;
40 l_msg_data VARCHAR2(1000);
41 l_location_id NUMBER;
42 l_loc_id NUMBER;
43 l_location_venue_addr_id NUMBER := x_location_venue_addr_id;
44
45 lv_rowid VARCHAR2(25);
46 lv_rowid_locvenue VARCHAR2(25);
47
48 tmp_var1 VARCHAR2(2000);
49 tmp_var VARCHAR2(2000);
50
51 l_addr_val_status VARCHAR2(30);
52 l_addr_warn_msg VARCHAR2(2000);
53
54 BEGIN
55
56 IF x_start_dt IS NOT NULL AND x_end_dt IS NOT NULL THEN
57 IF x_start_dt > x_end_dt THEN
58 FND_MESSAGE.SET_NAME('IGS','IGS_PE_FROM_DT_GRT_TO_DATE');
59 IGS_GE_MSG_STACK.ADD;
60 APP_EXCEPTION.RAISE_EXCEPTION;
61 END IF;
62 END IF;
63
64 IF x_start_dt IS NULL AND x_end_dt IS NOT NULL THEN
65 FND_MESSAGE.SET_NAME('IGS','IGS_PE_CANT_SPECIFY_FROM_DATE');
66 IGS_GE_MSG_STACK.ADD;
67 APP_EXCEPTION.RAISE_EXCEPTION;
68 END IF;
69
70 p_location_rec_insert.country := x_country;
71 p_location_rec_insert.address_style := x_address_style;
72 p_location_rec_insert.address1 := x_addr_line_1;
73 p_location_rec_insert.address2 := x_addr_line_2;
74 p_location_rec_insert.address3 := x_addr_line_3;
75 p_location_rec_insert.address4 := x_addr_line_4;
76 p_location_rec_insert.city := x_city;
77 p_location_rec_insert.state := x_state;
78 p_location_rec_insert.province := x_province;
79 p_location_rec_insert.county := x_county;
80 p_location_rec_insert.postal_code := x_postal_code;
81
82 p_location_rec_insert.delivery_point_code := x_delivery_point_code;
83
84 p_location_rec_insert.address_lines_phonetic := x_address_lines_phonetic;
85 p_location_rec_insert.address_effective_date := x_start_dt;
86 p_location_rec_insert.address_expiration_date := x_end_dt;
87 p_location_rec_insert.created_by_module := 'IGS';
88 p_location_rec_insert.application_id := 8405;
89
90 HZ_LOCATION_V2PUB.create_location (
91 P_INIT_MSG_LIST => FND_API.G_TRUE,
92 P_LOCATION_REC => p_location_rec_insert,
93 P_DO_ADDR_VAL => 'Y',
94 X_LOCATION_ID => l_location_id,
95 x_addr_val_status => l_addr_val_status,
96 x_addr_warn_msg => l_addr_warn_msg,
97 X_RETURN_STATUS => l_return_status,
98 X_MSG_COUNT => l_msg_count,
99 X_MSG_DATA => l_msg_data
100 );
101
102
103 IF l_return_status IN ('E', 'U') THEN
104
105 -- bug 2338473 logic to display more than one error modified.
106 x_msg_data := l_msg_data;
107 IF l_msg_count > 1 THEN
108 FOR i IN 1..l_msg_count LOOP
109 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
110 tmp_var1 := tmp_var1 || ' '|| tmp_var;
111 END LOOP;
112 x_msg_data := tmp_var1;
113 END IF;
114 RETURN;
115
116
117 ELSIF l_return_status = 'S' THEN
118
119 IGS_PE_HZ_LOCATIONS_PKG.INSERT_ROW(
120 x_rowid => lv_rowid,
121 x_location_id => l_location_id,
122 x_other_details_1 => x_other_details_1,
123 x_other_details_2 => x_other_details_2,
124 x_other_details_3 => x_other_details_3,
125 -- x_correspondence => x_correspondence,
126 x_date_last_verified => x_date_last_verified,
127 x_contact_person => x_contact_person
128 );
129 IGS_AD_LOCVENUE_ADDR_PKG.INSERT_ROW(
130 x_rowid => lv_rowid_locvenue,
131 x_location_venue_addr_id => l_location_venue_addr_id,
132 x_location_id => l_location_id,
133 x_location_venue_cd => x_location_venue_cd,
134 x_source_type => x_source_type,
135 x_identifying_address_flag => NVL(x_correspondence,'N')
136 );
137 x_location_venue_addr_id := l_location_venue_addr_id;
138 x_rowid := lv_rowid_locvenue;
139 x_location_id := l_location_id;
140 x_object_version_number := 1;
141 END IF;
142
143 IF l_addr_val_status = 'W' THEN
144 x_msg_data := l_addr_warn_msg;
145 l_return_status := l_addr_val_status;
146 END IF;
147
148 END INSERT_ROW;
149
150 PROCEDURE UPDATE_ROW (
151 x_rowid IN VARCHAR2,
152 x_location_venue_addr_id IN NUMBER,
153 x_location_id IN NUMBER,
154 x_location_venue_cd IN VARCHAR2,
155 x_start_dt IN DATE,
156 x_end_dt IN DATE,
157 x_country IN VARCHAR2,
158 x_address_style IN VARCHAR2,
159 x_addr_line_1 IN VARCHAR2,
160 x_addr_line_2 IN VARCHAR2,
161 x_addr_line_3 IN VARCHAR2,
162 x_addr_line_4 IN VARCHAR2,
163 x_date_last_verified IN DATE,
164 x_correspondence IN VARCHAR2,
165 x_city IN VARCHAR2,
166 x_state IN VARCHAR2,
167 x_province IN VARCHAR2,
168 x_county IN VARCHAR2,
169 x_postal_code IN VARCHAR2,
170 x_address_lines_phonetic IN VARCHAR2,
171 x_delivery_point_code IN VARCHAR2,
172 x_other_details_1 IN VARCHAR2,
173 x_other_details_2 IN VARCHAR2,
174 x_other_details_3 IN VARCHAR2,
175 x_source_type IN VARCHAR2,
176 x_contact_person IN VARCHAR2 default NULL,
177 x_msg_data OUT NOCOPY VARCHAR2,
178 l_return_status OUT NOCOPY VARCHAR2 ,
179 x_object_version_number IN OUT NOCOPY NUMBER,
180 X_MODE IN VARCHAR2 DEFAULT 'R'
181 ) AS
182
183 p_location_rec_update HZ_LOCATION_V2PUB.location_rec_type;
184
185 l_msg_count NUMBER;
186 l_msg_data VARCHAR2(1000);
187 l_location_id NUMBER := x_location_id;
188 l_loc_id NUMBER;
189 lv_rowid VARCHAR2(25);
190
191 tmp_var1 VARCHAR2(2000);
192 tmp_var VARCHAR2(2000);
193 l_addr_val_status VARCHAR2(30);
194 l_addr_warn_msg VARCHAR2(2000);
195
196 CURSOR c IS
197 SELECT ROWID
198 FROM igs_pe_hz_locations
199 WHERE location_id = x_location_id;
200
201
202 BEGIN
203
204 IF x_start_dt IS NOT NULL AND x_end_dt IS NOT NULL THEN
205 IF x_start_dt > x_end_dt THEN
206 FND_MESSAGE.SET_NAME('IGS','IGS_PE_FROM_DT_GRT_TO_DATE');
207 IGS_GE_MSG_STACK.ADD;
208 APP_EXCEPTION.RAISE_EXCEPTION;
209 END IF;
210 END IF;
211
212 IF x_start_dt IS NULL AND x_end_dt IS NOT NULL THEN
213 FND_MESSAGE.SET_NAME('IGS','IGS_PE_CANT_SPECIFY_FROM_DATE');
214 IGS_GE_MSG_STACK.ADD;
215 APP_EXCEPTION.RAISE_EXCEPTION;
216 END IF;
217
218
219 p_location_rec_update.location_id := x_location_id;
220 p_location_rec_update.country := x_country;
221 p_location_rec_update.address_style := NVL(x_address_style,FND_API.G_MISS_CHAR);
222 p_location_rec_update.address1 := NVL(x_addr_line_1,FND_API.G_MISS_CHAR);
223 p_location_rec_update.address2 := NVL(x_addr_line_2,FND_API.G_MISS_CHAR);
224 p_location_rec_update.address3 := NVL(x_addr_line_3,FND_API.G_MISS_CHAR);
225 p_location_rec_update.address4 := NVL(x_addr_line_4,FND_API.G_MISS_CHAR);
226 p_location_rec_update.city := NVL(x_city,FND_API.G_MISS_CHAR);
227 p_location_rec_update.state := NVL(x_state,FND_API.G_MISS_CHAR);
228 p_location_rec_update.province := NVL(x_province,FND_API.G_MISS_CHAR);
229 p_location_rec_update.county := NVL(x_county,FND_API.G_MISS_CHAR);
230 p_location_rec_update.postal_code := NVL(x_postal_code,FND_API.G_MISS_CHAR);
231
232 p_location_rec_update.delivery_point_code := NVL(x_delivery_point_code,FND_API.G_MISS_CHAR);
233
234 p_location_rec_update.address_lines_phonetic := NVL(x_address_lines_phonetic,FND_API.G_MISS_CHAR);
235 p_location_rec_update.address_effective_date := NVL(x_start_dt,FND_API.G_MISS_DATE);
236 p_location_rec_update.address_expiration_date := NVL(x_end_dt,FND_API.G_MISS_DATE);
237
238 HZ_LOCATION_V2PUB.update_location(
239 P_INIT_MSG_LIST => FND_API.G_TRUE,
240 P_LOCATION_REC => p_location_rec_update,
241 p_do_addr_val => 'Y',
242 p_object_version_number => x_object_version_number,
243 x_addr_val_status => l_addr_val_status,
244 x_addr_warn_msg => l_addr_warn_msg,
245 X_RETURN_STATUS => l_return_status,
246 X_MSG_COUNT => l_msg_count,
247 X_MSG_DATA => l_msg_data
248 );
249
250 IF l_return_status IN ('E', 'U') THEN
251 -- bug 2338473 logic to display more than one error modified.
252 x_msg_data := l_msg_data;
253
254 IF l_msg_count > 1 THEN
255 FOR i IN 1..l_msg_count LOOP
256 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
257 tmp_var1 := tmp_var1 || ' '|| tmp_var;
258 END LOOP;
259 x_msg_data := tmp_var1;
260 END IF;
261 RETURN;
262
263 ELSIF l_return_status = 'S' THEN
264
265 OPEN c ;
266 FETCH c INTO lv_rowid;
267 CLOSE c;
268
269 IGS_PE_HZ_LOCATIONS_PKG.UPDATE_ROW(
270 x_rowid => lv_rowid,
271 x_location_id => x_location_id,
272 x_other_details_1 => x_other_details_1,
273 x_other_details_2 => x_other_details_2,
274 x_other_details_3 => x_other_details_3,
275 -- x_correspondence => x_correspondence,
276 x_date_last_verified => x_date_last_verified,
277 x_contact_person => x_contact_person );
278
279 IGS_AD_LOCVENUE_ADDR_PKG.UPDATE_ROW(
280 x_rowid => x_rowid,
281 x_location_venue_addr_id => x_location_venue_addr_id,
282 x_location_id => x_location_id,
283 x_location_venue_cd => x_location_venue_cd,
284 x_source_type => x_source_type,
285 x_identifying_address_flag => NVL(x_correspondence,'N') );
286
287 END IF;
288 IF l_addr_val_status = 'W' THEN
289 x_msg_data := l_addr_warn_msg;
290 l_return_status := l_addr_val_status;
291 END IF;
292
293 END UPDATE_ROW;
294
295 END IGS_AD_LOCATION_ADDR_PKG;