[Home] [Help]
PACKAGE BODY: APPS.IGW_SETUP_PERSONS_PVT
Source
1 PACKAGE BODY IGW_SETUP_PERSONS_PVT AS
2 --$Header: igwvspeb.pls 115.5 2002/11/15 00:48:33 ashkumar noship $
3
4 PROCEDURE CREATE_PERSON (
5 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_False,
6 p_validate_only IN VARCHAR2 := Fnd_Api.G_False,
7 p_commit IN VARCHAR2 := Fnd_Api.G_False,
8 p_status IN VARCHAR2,
9 p_person_pre_name_adjunct IN VARCHAR2,
10 p_person_first_name IN VARCHAR2,
11 p_person_middle_name IN VARCHAR2,
12 p_person_last_name IN VARCHAR2,
13 p_ssn IN VARCHAR2,
14 p_date_of_birth IN DATE,
15 p_address1 IN VARCHAR2,
16 p_address2 IN VARCHAR2,
17 p_address3 IN VARCHAR2,
18 p_city IN VARCHAR2,
19 p_state IN VARCHAR2,
20 p_postal_code IN VARCHAR2,
21 p_county IN VARCHAR2,
22 p_country_name IN VARCHAR2,
23 p_country_code IN VARCHAR2,
24 x_party_id OUT NOCOPY NUMBER,
25 x_return_status OUT NOCOPY VARCHAR2,
26 x_msg_count OUT NOCOPY NUMBER,
27 x_msg_data OUT NOCOPY VARCHAR2) IS
28
29
30 x_party_number VARCHAR2(30);
31 x_profile_id NUMBER;
32 x_location_id NUMBER;
33 l_party_site_id NUMBER;
34 l_country_code VARCHAR2(2) := p_country_code;
35 l_party_site_number VARCHAR2(30);
36 l_party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE;
37 l_person_rec HZ_PARTY_V2PUB.PERSON_REC_TYPE;
38 l_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
39 l_party_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
40
41 BEGIN
42 IF Fnd_Api.To_Boolean(p_commit) THEN
43 SAVEPOINT Create_Person_Pvt;
44 END IF;
45
46 x_return_status := Fnd_Api.G_Ret_Sts_Success;
47
48 IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
49 Fnd_Msg_Pub.Initialize;
50 END IF;
51
52 -- get country_code from country_name if country_code is null
53 if (p_country_name is null) then
54 l_country_code := null;
55 else
56 GET_COUNTRY_CODE(p_country_name => p_country_name,
57 x_country_code => l_country_code);
58 end if;
59 check_errors;
60
61 l_person_rec.person_pre_name_adjunct := p_person_pre_name_adjunct;
62 l_person_rec.person_first_name := p_person_first_name;
63 l_person_rec.person_middle_name := p_person_middle_name;
64 l_person_rec.person_last_name := p_person_last_name;
65 l_person_rec.jgzz_fiscal_code := p_ssn;
66 l_person_rec.date_of_birth := p_date_of_birth;
67 l_person_rec.created_by_module := 'IGW';
68
69 l_party_rec.orig_system_reference := 'IGW';
70 l_party_rec.status := p_status;
71
72 IF Fnd_Profile.Value_Wnps('HZ_GENERATE_PARTY_NUMBER') = 'N' THEN
73 SELECT to_char(hz_party_number_s.nextval)
74 INTO l_party_rec.party_number
75 FROM dual;
76 END IF;
77
78 l_person_rec.party_rec := l_party_rec;
79
80
81 Hz_Party_V2pub.Create_Person
82 (
83 p_init_msg_list => FND_API.g_false,
84 p_person_rec => l_person_rec,
85 x_party_id => x_party_id,
86 x_party_number => x_party_number,
87 x_profile_id => x_profile_id,
88 X_RETURN_STATUS => X_RETURN_STATUS,
89 X_MSG_COUNT => X_MSG_COUNT,
90 X_MSG_DATA => X_MSG_DATA
91 );
92 check_errors;
93
94 l_location_rec.address1 := p_address1;
95 l_location_rec.address2 := p_address2;
96 l_location_rec.address3 := p_address3;
97 l_location_rec.city := p_city;
98 l_location_rec.state := p_state;
99 l_location_rec.postal_code := p_postal_code;
100 l_location_rec.county := p_county;
101 l_location_rec.country := l_country_code;
102 l_location_rec.orig_system_reference := 'IGW';
103 l_location_rec.created_by_module := 'IGW';
104
105 Hz_Location_V2pub.Create_Location
106 (
107 P_INIT_MSG_LIST => FND_API.g_false,
108 p_location_rec => l_location_rec,
109 x_location_id => x_location_id,
110 X_RETURN_STATUS => X_RETURN_STATUS,
111 X_MSG_COUNT => X_MSG_COUNT,
112 X_MSG_DATA => X_MSG_DATA
113 );
114 check_errors;
115
116 l_party_site_rec.party_id := x_party_id;
117 l_party_site_rec.location_id := x_location_id;
118 l_party_site_rec.identifying_address_flag := 'Y';
119 l_party_site_rec.orig_system_reference := 'IGW';
120 l_party_site_rec.created_by_module := 'IGW';
121
122 IF Fnd_Profile.Value_Wnps('HZ_GENERATE_PARTY_SITE_NUMBER') = 'N' THEN
123 SELECT to_char(hz_party_site_number_s.nextval)
124 INTO l_party_site_rec.party_site_number
125 FROM dual;
126 END IF;
127
128 Hz_Party_Site_V2pub.Create_Party_Site
129 (
130 P_INIT_MSG_LIST => FND_API.g_false,
131 p_party_site_rec => l_party_site_rec,
132 x_party_site_id => l_party_site_id,
133 x_party_site_number => l_party_site_number,
134 X_RETURN_STATUS => X_RETURN_STATUS,
135 X_MSG_COUNT => X_MSG_COUNT,
136 X_MSG_DATA => X_MSG_DATA
137 );
138
139 check_errors;
140
141
142
143 IF Fnd_Api.To_Boolean(p_commit) THEN
144
145 COMMIT;
146
147 END IF;
148
149 EXCEPTION
150 WHEN FND_API.G_EXC_ERROR THEN
151 IF p_commit = FND_API.G_TRUE THEN
152 ROLLBACK TO Create_Person_Pvt;
153 END IF;
154
155 x_return_status := FND_API.G_RET_STS_ERROR;
156
157 fnd_msg_pub.count_and_get(p_count => x_msg_count,
158 p_data => x_msg_data);
159
160
161 WHEN OTHERS THEN
162 IF p_commit = FND_API.G_TRUE THEN
163 ROLLBACK TO Create_Person_Pvt;
164 END IF;
165
166 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
167
168 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_SETUP_PERSONS_PVT',
169 p_procedure_name => 'CREATE_PERSON',
170 p_error_text => SUBSTRB(SQLERRM,1,240));
171
172 fnd_msg_pub.count_and_get(p_count => x_msg_count,
173 p_data => x_msg_data);
174
175
176 END CREATE_PERSON;
177
178 --------------------------------------------------------------------------------------------------------
179 PROCEDURE UPDATE_PERSON (
180 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_False,
181 p_validate_only IN VARCHAR2 := Fnd_Api.G_False,
182 p_commit IN VARCHAR2 := Fnd_Api.G_False,
183 p_party_id IN NUMBER,
184 p_location_id IN NUMBER,
185 p_status IN VARCHAR2,
186 p_person_pre_name_adjunct IN VARCHAR2,
187 p_person_first_name IN VARCHAR2,
188 p_person_middle_name IN VARCHAR2,
189 p_person_last_name IN VARCHAR2,
190 p_ssn IN VARCHAR2,
191 p_date_of_birth IN DATE,
192 p_address1 IN VARCHAR2,
193 p_address2 IN VARCHAR2,
194 p_address3 IN VARCHAR2,
195 p_city IN VARCHAR2,
196 p_state IN VARCHAR2,
197 p_postal_code IN VARCHAR2,
198 p_county IN VARCHAR2,
199 p_country_name IN VARCHAR2,
200 p_country_code IN VARCHAR2,
201 p_party_object_version_number IN NUMBER,
202 p_loc_object_version_number IN NUMBER,
203 x_return_status OUT NOCOPY VARCHAR2,
204 x_msg_count OUT NOCOPY NUMBER,
205 x_msg_data OUT NOCOPY VARCHAR2) IS
206
207 l_party_object_version_number NUMBER := p_party_object_version_number;
208 l_loc_object_version_number NUMBER := p_party_object_version_number;
209 x_profile_id NUMBER;
210 x_location_id NUMBER;
211 l_party_site_id NUMBER;
212 l_country_code VARCHAR2(2) := p_country_code;
213 l_party_site_number VARCHAR2(30);
214 l_party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE;
215 l_person_rec HZ_PARTY_V2PUB.PERSON_REC_TYPE;
216 l_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
217 l_party_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
218
219 BEGIN
220 IF Fnd_Api.To_Boolean(p_commit) THEN
221 SAVEPOINT Update_Person_Pvt;
222 END IF;
223
224 x_return_status := Fnd_Api.G_Ret_Sts_Success;
225
226 IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
227 Fnd_Msg_Pub.Initialize;
228 END IF;
229
230 -- get country_code from country_name if country_code is null
231 if (p_country_name is null) then
232 l_country_code := null;
233 else
234 GET_COUNTRY_CODE(p_country_name => p_country_name,
235 x_country_code => l_country_code);
236 end if;
237 check_errors;
238
239 l_party_rec.orig_system_reference := 'IGW';
240 l_party_rec.party_id := p_party_id;
241 l_party_rec.status := p_status;
242
243 l_person_rec.person_pre_name_adjunct := p_person_pre_name_adjunct;
244 l_person_rec.person_first_name := p_person_first_name;
245 l_person_rec.person_middle_name := p_person_middle_name;
246 l_person_rec.person_last_name := p_person_last_name;
247 l_person_rec.jgzz_fiscal_code := p_ssn;
248 l_person_rec.date_of_birth := p_date_of_birth;
249 l_person_rec.created_by_module := 'IGW';
250 l_person_rec.party_rec := l_party_rec;
251
252
253 Hz_Party_V2pub.Update_Person
254 (
255 p_init_msg_list => FND_API.g_false,
256 p_person_rec => l_person_rec,
257 p_party_object_version_number => l_party_object_version_number,
258 x_profile_id => x_profile_id,
259 X_RETURN_STATUS => X_RETURN_STATUS,
260 X_MSG_COUNT => X_MSG_COUNT,
261 X_MSG_DATA => X_MSG_DATA
262 );
263 check_errors;
264
265 l_location_rec.location_id := p_location_id;
266 l_location_rec.address1 := p_address1;
267 l_location_rec.address2 := p_address2;
268 l_location_rec.address3 := p_address3;
269 l_location_rec.city := p_city;
270 l_location_rec.state := p_state;
271 l_location_rec.postal_code := p_postal_code;
272 l_location_rec.county := p_county;
273 l_location_rec.country := l_country_code;
274 l_location_rec.orig_system_reference := 'IGW';
275 l_location_rec.created_by_module := 'IGW';
276
277 Hz_Location_V2pub.Update_Location
278 (
279 P_INIT_MSG_LIST => FND_API.g_false,
280 p_location_rec => l_location_rec,
281 p_object_version_number => l_loc_object_version_number,
282 X_RETURN_STATUS => X_RETURN_STATUS,
283 X_MSG_COUNT => X_MSG_COUNT,
284 X_MSG_DATA => X_MSG_DATA
285 );
286 check_errors;
287
288 IF Fnd_Api.To_Boolean(p_commit) THEN
289
290 COMMIT;
291
292 END IF;
293
294 EXCEPTION
295 WHEN FND_API.G_EXC_ERROR THEN
296 IF p_commit = FND_API.G_TRUE THEN
297 ROLLBACK TO Update_Person_Pvt;
298 END IF;
299
300 x_return_status := FND_API.G_RET_STS_ERROR;
301
302 fnd_msg_pub.count_and_get(p_count => x_msg_count,
303 p_data => x_msg_data);
304
305
306 WHEN OTHERS THEN
307 IF p_commit = FND_API.G_TRUE THEN
308 ROLLBACK TO Update_Person_Pvt;
309 END IF;
310
311 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
312
313 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_SETUP_PERSONS_PVT',
314 p_procedure_name => 'UPDATE_PERSON',
315 p_error_text => SUBSTRB(SQLERRM,1,240));
316
317 fnd_msg_pub.count_and_get(p_count => x_msg_count,
318 p_data => x_msg_data);
319
320
321 END UPDATE_PERSON;
322
323 -----------------------------------------------------------------------------------------
324 PROCEDURE GET_COUNTRY_CODE (P_COUNTRY_NAME IN VARCHAR2,
325 X_COUNTRY_CODE OUT NOCOPY VARCHAR2) IS
326
327
328 l_country_code VARCHAR2(2);
329 BEGIN
330 select territory_code
331 into l_country_code
332 from fnd_territories_vl
333 where upper(territory_short_name) = upper(p_country_name);
334
335 x_country_code := l_country_code;
336
337 exception
338 when no_data_found then
339 FND_MESSAGE.SET_NAME('IGW','IGW_SS_COUNTRY_INVALID');
340 FND_MSG_PUB.Add;
341 raise fnd_api.g_exc_error;
342 when too_many_rows then
343 FND_MESSAGE.SET_NAME('IGW','IGW_SS_COUNTRY_INVALID');
344 FND_MSG_PUB.Add;
345 raise fnd_api.g_exc_error;
346 when others then
347 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_SETUP_PERSONS_PVT',
348 p_procedure_name => 'GET_COUNTRY_CODE',
349 p_error_text => SUBSTRB(SQLERRM,1,240));
350 raise fnd_api.g_exc_unexpected_error;
351
352 END GET_COUNTRY_CODE;
353
354
355 ----------------------------------------------------------------------------------------------
356 PROCEDURE CHECK_ERRORS is
357 l_msg_count NUMBER;
358 BEGIN
359 l_msg_count := fnd_msg_pub.count_msg;
360 IF (l_msg_count > 0) THEN
361 RAISE FND_API.G_EXC_ERROR;
362 END IF;
363
364 END CHECK_ERRORS;
365
366
367 END IGW_SETUP_PERSONS_PVT;