1 PACKAGE BODY IGS_PE_PERSON_ADDR_PKG AS
2 /* $Header: IGSNI12B.pls 120.2 2006/07/24 12:44:33 vskumar noship $ */
3
4 procedure INSERT_ROW (
5 p_action IN VARCHAR2,
6 p_rowid IN OUT NOCOPY VARCHAR2,
7 p_location_id IN OUT NOCOPY NUMBER,
8 p_start_dt IN igs_pe_hz_pty_sites.start_date%TYPE,
9 p_end_dt IN igs_pe_hz_pty_sites.end_date%TYPE,
10 p_country IN VARCHAR2,
11 p_address_style IN VARCHAR2,
12 p_addr_line_1 IN VARCHAR2,
13 p_addr_line_2 IN VARCHAR2,
14 p_addr_line_3 IN VARCHAR2,
15 p_addr_line_4 IN VARCHAR2,
16 p_date_last_verified IN DATE,
17 p_correspondence IN VARCHAR2,
18 p_city IN VARCHAR2,
19 p_state IN VARCHAR2,
20 p_province IN VARCHAR2,
21 p_county IN VARCHAR2,
22 p_postal_code IN VARCHAR2,
23 p_address_lines_phonetic IN VARCHAR2,
24 p_delivery_point_code IN VARCHAR2,
25 p_other_details_1 IN VARCHAR2,
26 p_other_details_2 IN VARCHAR2,
27 p_other_details_3 IN VARCHAR2,
28 l_return_status OUT NOCOPY VARCHAR2,
29 l_msg_data OUT NOCOPY VARCHAR2,
30 p_party_id IN NUMBER,
31 p_party_site_id IN OUT NOCOPY NUMBER,
32 p_party_type IN VARCHAR2,
33 p_last_update_date IN OUT NOCOPY DATE,
34 p_party_site_ovn IN OUT NOCOPY hz_party_sites.object_version_number%TYPE,
35 p_location_ovn IN OUT NOCOPY hz_party_sites.object_version_number%TYPE,
36 p_status IN hz_party_sites.status%TYPE
37 ) AS
38
39 p_init_msg_list VARCHAR2(30) := FND_API.G_TRUE;
40 p_lock_flag VARCHAR2(30) := FND_API.G_FALSE;
41 l_contact_person VARCHAR2(40) := NULL;
42 l_msg_count NUMBER(15);
43 l_loc_id NUMBER(15);
44 lv_rowid VARCHAR2(25);
45 l_tmp_var1 VARCHAR2(2000);
46 l_tmp_var VARCHAR2(2000);
47 p_location_rec_insert HZ_LOCATION_V2PUB.location_rec_type;
48 p_party_site_rec_insert HZ_PARTY_SITE_V2PUB.party_site_rec_type;
49 l_addr_val_status VARCHAR2(30);
50 l_addr_warn_msg VARCHAR2(2000);
51
52 CURSOR c_birth_date_val IS SELECT date_of_birth FROM HZ_PERSON_PROFILES
53 WHERE party_id = p_party_id AND effective_end_Date IS NULL;
54 l_date_of_birth HZ_PERSON_PROFILES.DATE_OF_BIRTH%TYPE;
55
56 CURSOR c_party_site_number IS
57 SELECT hz_party_site_number_s.NEXTVAL
58 FROM dual;
59
60 l_party_site_number HZ_PARTY_SITES.PARTY_SITE_NUMBER%TYPE;
61 l_hz_gen_party_site VARCHAR2(10);
62 BEGIN
63
64 IF p_start_dt IS NOT NULL AND p_end_dt IS NOT NULL THEN
65 IF p_start_dt > p_end_dt THEN
66 FND_MESSAGE.SET_NAME('IGS','IGS_PE_FROM_DT_GRT_TO_DATE');
67 IGS_GE_MSG_STACK.ADD;
68 APP_EXCEPTION.RAISE_EXCEPTION;
69 END IF;
70 END IF;
71
72 IF p_start_dt IS NULL AND p_end_dt IS NOT NULL THEN
73 FND_MESSAGE.SET_NAME('IGS','IGS_PE_CANT_SPECIFY_FROM_DATE');
74 IGS_GE_MSG_STACK.ADD;
75 APP_EXCEPTION.RAISE_EXCEPTION;
76 END IF;
77
78 IF p_start_dt IS NOT NULL THEN
79 OPEN c_birth_date_val; FETCH c_birth_date_val INTO l_date_of_birth;
80 CLOSE c_birth_date_val;
81 IF(p_start_dt IS NOT NULL AND l_Date_of_birth IS NOT NULL) THEN
82 IF(p_start_dt < l_date_of_birth) THEN
83 Fnd_Message.Set_Name('IGS','IGS_PE_DREC_GT_BTDT');
84 IGS_GE_MSG_STACK.ADD;
85 App_Exception.Raise_Exception;
86 END IF;
87 END IF;
88 END IF;
89
90 p_location_rec_insert.country := p_country;
91 p_location_rec_insert.address_style := p_address_style;
92 p_location_rec_insert.address1 := p_addr_line_1;
93 p_location_rec_insert.address2 := p_addr_line_2;
94 p_location_rec_insert.address3 := p_addr_line_3;
95 p_location_rec_insert.address4 := p_addr_line_4;
96 p_location_rec_insert.city := p_city;
97 p_location_rec_insert.state := p_state;
98 p_location_rec_insert.province := p_province;
99 p_location_rec_insert.county := p_county;
100 p_location_rec_insert.postal_code := p_postal_code;
101 p_location_rec_insert.address_lines_phonetic := p_address_lines_phonetic;
102 p_location_rec_insert.address_effective_date := NULL;
103 p_location_rec_insert.address_expiration_date := NULL;
104 p_location_rec_insert.created_by_module := 'IGS';
105 p_location_rec_insert.application_id := 8405;
106 p_location_rec_insert.delivery_point_code := p_delivery_point_code;
107
108 HZ_LOCATION_V2PUB.create_location (
109 P_INIT_MSG_LIST => p_init_msg_list,
110 P_LOCATION_REC => p_location_rec_insert,
111 P_DO_ADDR_VAL => 'Y',
112 X_LOCATION_ID => p_location_id,
113 x_addr_val_status => l_addr_val_status,
114 x_addr_warn_msg => l_addr_warn_msg,
115 X_RETURN_STATUS => l_return_status,
116 X_MSG_COUNT => l_msg_count,
117 X_MSG_DATA => l_msg_data
118 );
119
120 IF l_return_status in ('E', 'U') THEN
121 --sbaliga added this code corresponding to IF condition as part of #2338473
122
123 IF l_msg_count > 1 THEN
124 FOR i IN 1..l_msg_count
125 LOOP
126 l_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
127 l_tmp_var1 := l_tmp_var1 || ' '|| l_tmp_var;
128 END LOOP;
129 l_msg_data := l_tmp_var1;
130 END IF;
131 RETURN;
132 ELSIF l_return_status = 'S' THEN
133 p_location_ovn := 1;
134 -- bug 2203778 : ssawhney, correspondence flag is obsoleted from IGS table and from now
135 -- this flag will be replaced by hz_party_sites
136
137 P_PARTY_SITE_REC_INSERT.PARTY_ID := p_party_id;
138 P_PARTY_SITE_REC_INSERT.LOCATION_ID := p_location_id;
139 P_PARTY_SITE_REC_INSERT.IDENTIFYING_ADDRESS_FLAG := p_correspondence ;
140 P_PARTY_SITE_REC_INSERT.created_by_module := 'IGS';
141 P_PARTY_SITE_REC_INSERT.application_id := 8405;
142 P_PARTY_SITE_REC_INSERT.status := p_status;
143
144 -- ssawhney bug 2379291. added logic to pass party site number if profile is set to autogenerate false.
145
146 fnd_profile.get('HZ_GENERATE_PARTY_SITE_NUMBER',l_hz_gen_party_site);
147 IF l_hz_gen_party_site = 'N' THEN
148 OPEN c_party_site_number;
149 FETCH c_party_site_number INTO l_party_site_number;
150 CLOSE c_party_site_number;
151 P_PARTY_SITE_REC_INSERT.PARTY_SITE_NUMBER := 'IGS-'||l_party_site_number;
152 END IF;
153
154
155 HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE
156 (
157 P_INIT_MSG_LIST => p_init_msg_list,
158 P_PARTY_SITE_REC => p_party_site_rec_insert,
159 X_RETURN_STATUS => l_return_status,
160 X_MSG_COUNT => l_msg_count,
161 X_MSG_DATA => l_msg_data,
162 X_PARTY_SITE_ID => p_party_site_id,
163 X_PARTY_SITE_NUMBER => l_party_site_number /* not passed to form */
164 );
165
166 IF l_return_status ='E' or l_return_status ='U' THEN
167 --ssawhney added this code corresponding to IF condition as part of #2338473
168 IF l_msg_count > 1 THEN
169 FOR i IN 1..l_msg_count
170 LOOP
171 l_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
172 l_tmp_var1 := l_tmp_var1 || ' '|| l_tmp_var;
173 END LOOP;
174 l_msg_data := l_tmp_var1;
175 END IF;
176 RETURN;
177 -- remove code for creation of customer account site.
178 -- ssawhney : 2225917
179 ELSIF l_return_status = 'S' THEN
180 p_party_site_ovn := 1;
181 IF p_start_dt IS NOT NULL OR p_end_dt IS NOT NULL THEN
182 igs_pe_hz_pty_sites_pkg.insert_row(x_rowid => lv_rowid,
183 x_party_site_id => p_party_site_id,
184 x_start_date => p_start_dt,
185 x_end_date => p_end_dt );
186 END IF;
187 END IF;
188 END IF;
189
190 IF l_addr_val_status = 'W' THEN
191 l_msg_data := l_addr_warn_msg;
192 l_return_status := l_addr_val_status;
193 END IF;
194
195 END INSERT_ROW;
196
197 procedure UPDATE_ROW (
198 p_action IN VARCHAR2,
199 p_rowid IN OUT NOCOPY VARCHAR2,
200 p_location_id IN OUT NOCOPY NUMBER,
201 p_start_dt IN igs_pe_hz_pty_sites.start_date%TYPE,
202 p_end_dt IN igs_pe_hz_pty_sites.end_date%TYPE,
203 p_country IN VARCHAR2,
204 p_address_style IN VARCHAR2,
205 p_addr_line_1 IN VARCHAR2,
206 p_addr_line_2 IN VARCHAR2,
207 p_addr_line_3 IN VARCHAR2,
208 p_addr_line_4 IN VARCHAR2,
209 p_date_last_verified IN DATE,
210 p_correspondence IN VARCHAR2,
211 p_city IN VARCHAR2,
212 p_state IN VARCHAR2,
213 p_province IN VARCHAR2,
214 p_county IN VARCHAR2,
215 p_postal_code IN VARCHAR2,
216 p_address_lines_phonetic IN VARCHAR2,
217 p_delivery_point_code IN VARCHAR2,
218 p_other_details_1 IN VARCHAR2,
219 p_other_details_2 IN VARCHAR2,
220 p_other_details_3 IN VARCHAR2,
221 l_return_status OUT NOCOPY VARCHAR2,
222 l_msg_data OUT NOCOPY VARCHAR2,
223 p_party_id IN NUMBER,
224 p_party_site_id IN OUT NOCOPY NUMBER,
225 p_party_type IN VARCHAR2,
226 p_last_update_date IN OUT NOCOPY DATE,
227 p_party_site_ovn IN OUT NOCOPY hz_party_sites.object_version_number%TYPE,
228 p_location_ovn IN OUT NOCOPY hz_party_sites.object_version_number%TYPE,
229 p_status IN hz_party_sites.status%TYPE
230 )
231 AS
232 p_init_msg_list VARCHAR2(30) := FND_API.G_TRUE;
233 p_lock_flag VARCHAR2(30) := FND_API.G_FALSE;
234 l_contact_person VARCHAR2(40) := NULL;
235 l_party_site_number HZ_PARTY_SITES.PARTY_SITE_NUMBER%TYPE;
236 l_msg_count NUMBER(15);
237 l_loc_id NUMBER(15);
238 lv_rowid VARCHAR2(25);
239 p_location_rec_update HZ_LOCATION_V2PUB.location_rec_type;
240 p_party_site_rec_update HZ_PARTY_SITE_V2PUB.party_site_rec_type;
241 l_tmp_var1 VARCHAR2(2000);
242 l_tmp_var VARCHAR2(2000);
243 l_start_dt DATE;
244 l_addr_val_status VARCHAR2(30);
245 l_addr_warn_msg VARCHAR2(2000);
246
247 CURSOR c_birth_date_val IS SELECT date_of_birth FROM HZ_PERSON_PROFILES
248 WHERE party_id = p_party_id AND effective_end_Date IS NULL;
249 l_date_of_birth HZ_PERSON_PROFILES.DATE_OF_BIRTH%TYPE;
250
251
252 CURSOR get_rowid(p_party_site_id NUMBER) IS
253 SELECT ROWID,start_date
254 FROM igs_pe_hz_pty_sites WHERE party_site_id = p_party_site_id;
255
256 BEGIN
257
258 -- bug 2203778 : ssawhney, correspondence flag is obsoleted from IGS table and from now
259 -- this flag will be replaced by hz_party_sites
260
261 IF p_start_dt IS NOT NULL AND p_end_dt IS NOT NULL THEN
262 IF p_start_dt > p_end_dt THEN
263 FND_MESSAGE.SET_NAME('IGS','IGS_PE_FROM_DT_GRT_TO_DATE');
264 IGS_GE_MSG_STACK.ADD;
265 APP_EXCEPTION.RAISE_EXCEPTION;
266 END IF;
267 END IF;
268
269 IF p_start_dt IS NULL AND p_end_dt IS NOT NULL THEN
270 FND_MESSAGE.SET_NAME('IGS','IGS_PE_CANT_SPECIFY_FROM_DATE');
271 IGS_GE_MSG_STACK.ADD;
272 APP_EXCEPTION.RAISE_EXCEPTION;
273 END IF;
274 IF p_start_dt IS NOT NULL THEN
275 OPEN c_birth_date_val; FETCH c_birth_date_val INTO l_date_of_birth;
276 CLOSE c_birth_date_val;
277 IF(p_start_dt IS NOT NULL AND l_Date_of_birth IS NOT NULL) THEN
278 IF(p_start_dt < l_date_of_birth) THEN
279 Fnd_Message.Set_Name('IGS','IGS_PE_DREC_GT_BTDT');
280 IGS_GE_MSG_STACK.ADD;
281 App_Exception.Raise_Exception;
282 END IF;
283 END IF;
284 END IF;
285
286 p_location_rec_update.location_id := p_location_id;
287 p_location_rec_update.country := p_country;
288 p_location_rec_update.address_style := NVL(p_address_style,FND_API.G_MISS_CHAR);
289 p_location_rec_update.address1 := NVL(p_addr_line_1,FND_API.G_MISS_CHAR);
290 p_location_rec_update.address2 := NVL(p_addr_line_2,FND_API.G_MISS_CHAR);
291 p_location_rec_update.address3 := NVL(p_addr_line_3,FND_API.G_MISS_CHAR);
292 p_location_rec_update.address4 := NVL(p_addr_line_4,FND_API.G_MISS_CHAR);
293 p_location_rec_update.city := NVL(p_city,FND_API.G_MISS_CHAR);
294 p_location_rec_update.state := NVL(p_state,FND_API.G_MISS_CHAR);
295 p_location_rec_update.province := NVL(p_province,FND_API.G_MISS_CHAR);
296 p_location_rec_update.county := NVL(p_county,FND_API.G_MISS_CHAR);
297 p_location_rec_update.postal_code := NVL(p_postal_code,FND_API.G_MISS_CHAR);
298 p_location_rec_update.address_lines_phonetic := NVL(p_address_lines_phonetic,FND_API.G_MISS_CHAR);
299 p_location_rec_update.delivery_point_code := NVL(p_delivery_point_code,FND_API.G_MISS_CHAR);
300 P_PARTY_SITE_REC_update.PARTY_ID := p_party_id;
301 P_PARTY_SITE_REC_update.PARTY_SITE_ID := p_party_site_id;
302 P_PARTY_SITE_REC_update.LOCATION_ID := p_location_id;
303 P_PARTY_SITE_REC_update.IDENTIFYING_ADDRESS_FLAG := p_correspondence;
304 P_PARTY_SITE_REC_update.status := p_status;
305
306 HZ_LOCATION_V2PUB.update_location(
307 P_INIT_MSG_LIST => p_init_msg_list,
308 P_LOCATION_REC => p_location_rec_update,
309 p_do_addr_val => 'Y',
310 p_object_version_number => p_location_ovn,
311 x_addr_val_status => l_addr_val_status,
312 x_addr_warn_msg => l_addr_warn_msg,
313 X_RETURN_STATUS => l_return_status,
314 X_MSG_COUNT => l_msg_count,
315 X_MSG_DATA => l_msg_data
316 );
317 IF l_return_status in ('E', 'U') THEN
318 --sbaliga added this code corresponding to IF condition as part of #2338473
319 IF l_msg_count > 1 THEN
320 FOR i IN 1..l_msg_count
321 LOOP
322 l_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
323 l_tmp_var1 := l_tmp_var1 || ' '|| l_tmp_var;
324 END LOOP;
325 l_msg_data := l_tmp_var1;
326 END IF;
327 RETURN;
328 ELSIF l_return_status = 'S' THEN
329 HZ_PARTY_SITE_V2PUB.UPDATE_PARTY_SITE(
330 p_init_msg_list => p_init_msg_list,
331 p_party_site_rec => p_party_site_rec_update,
332 p_object_version_number => p_party_site_ovn,
333 x_return_status => l_return_status,
334 x_msg_count => l_msg_count,
335 x_msg_data => l_msg_data
336 );
337
338 IF l_return_status ='E' or l_return_status ='U' THEN
339 --ssawhney added this code corresponding to IF condition as part of #2338473
340 IF l_msg_count > 1 THEN
341 FOR i IN 1..l_msg_count LOOP
342 l_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
343 l_tmp_var1 := l_tmp_var1 || ' '|| l_tmp_var;
344 END LOOP;
345 l_msg_data := l_tmp_var1;
346 END IF;
347 RETURN;
348 -- remove code for creation of customer account site.
349 -- ssawhney : 2225917
350 ELSIF l_return_status = 'S' THEN
351 OPEN get_rowid(p_party_site_id);
352 FETCH get_rowid INTO lv_rowid,l_start_dt;
353 CLOSE get_rowid;
354
355 IF p_start_dt IS NOT NULL OR l_start_dt IS NOT NULL THEN
356 igs_pe_hz_pty_sites_pkg.add_row(x_rowid => lv_rowid,
357 x_party_site_id => p_party_site_id,
358 x_start_date => p_start_dt,
359 x_end_date => p_end_dt);
360
361 END IF; -- for party site.
362 END IF; -- for rowid
363 END IF; -- success of update location
364
365 IF l_addr_val_status = 'W' THEN
366 l_msg_data := l_addr_warn_msg;
367 l_return_status := l_addr_val_status;
368 END IF;
369 END update_row;
370
371 END igs_pe_person_addr_pkg;
[Home] [Help]