[Home] [Help]
PACKAGE BODY: APPS.HR_CN_PERSON_ADDRESS_API
Source
1 PACKAGE BODY hr_cn_person_address_api AS
2 /* $Header: hrcnwrpa.pkb 120.1 2011/12/20 12:04:27 jmarupil ship $ */
3
4 g_package varchar2(33) := 'hr_cn_person_address_api.';
5
6
7 -- ----------------------------------------------------------------------------
8 -- |------------------------< create_cn_person_address >-------------------------|
9 -- ----------------------------------------------------------------------------
10 -- {Start Of Comments}
11 --
12 -- Description:
13 -- This API creates a new address, for a particular person.
14 --
15 -- If creating the first address for the specified person, then it must be
16 -- the primary address. As one and only one primary address can exist at
17 -- any given time for a person, any subsequent addresses must not be
18 -- primary.
19 --
20 -- Prerequisites:
21 -- A valid person (p_person_id) must exist on the start date (p_date_from)
22 -- of the address.
23 --
24 -- The address_type attribute can only be used after QuickCodes have been
25 -- defined for the 'ADDRESS_TYPE' lookup type.
26 --
27 -- In Parameters:
28 -- Name Reqd Type Description
29 -- p_validate boolean If true, the database
30 -- remains unchanged. If false,
31 -- the address is created.
32 -- p_effective_date Yes date Effective date.
33 -- p_validate_county boolean set to true.
34 -- p_person_id number Person for whom the address
35 -- applies.
36 -- p_primary_flag Yes varchar2 Identifies the primary
37 -- address.
38 -- p_date_from Yes date The date from which the
39 -- address applies.
40 -- p_date_to date The date on which the
41 -- address no longer applies.
42 -- p_address_type varchar2 Type of address.
43 -- p_comments long Comments.
44 -- p_address_line1 Yes varchar2 Line 1 of address.
45 -- p_address_line2 varchar2 Line 2 of address.
46 -- p_province_city_sar Yes varchar2 province/city.
47 -- p_postal_code varchar2 Determined by p_style
48 -- (eg. Postcode for CN).
49 -- p_country Yes varchar2 Country.
50 -- p_telephone varchar2 Telephone number.
51 -- p_fax varchar2 Fax number.
52 -- p_addr_attribute_category varchar2 Determines context of the
53 -- addr_attribute Descriptive
54 -- flexfield in parameters.
55 -- p_addr_attribute1 varchar2 Descriptive flexfield.
56 -- p_addr_attribute2 varchar2 Descriptive flexfield.
57 -- p_addr_attribute3 varchar2 Descriptive flexfield.
58 -- p_addr_attribute4 varchar2 Descriptive flexfield.
59 -- p_addr_attribute5 varchar2 Descriptive flexfield.
60 -- p_addr_attribute6 varchar2 Descriptive flexfield.
61 -- p_addr_attribute7 varchar2 Descriptive flexfield.
62 -- p_addr_attribute8 varchar2 Descriptive flexfield.
63 -- p_addr_attribute9 varchar2 Descriptive flexfield.
64 -- p_addr_attribute10 varchar2 Descriptive flexfield.
65 -- p_addr_attribute11 varchar2 Descriptive flexfield.
66 -- p_addr_attribute12 varchar2 Descriptive flexfield.
67 -- p_addr_attribute13 varchar2 Descriptive flexfield.
68 -- p_addr_attribute14 varchar2 Descriptive flexfield.
69 -- p_addr_attribute15 varchar2 Descriptive flexfield.
70 -- p_addr_attribute16 varchar2 Descriptive flexfield.
71 -- p_addr_attribute17 varchar2 Descriptive flexfield.
72 -- p_addr_attribute18 varchar2 Descriptive flexfield.
73 -- p_addr_attribute19 varchar2 Descriptive flexfield.
74 -- p_addr_attribute20 varchar2 Descriptive flexfield.
75 -- p_add_information13 varchar2 Descriptive flexfield.
76 -- p_add_information14 varchar2 Descriptive flexfield.
77 -- p_add_information15 varchar2 Descriptive flexfield.
78 -- p_add_information16 varchar2 Descriptive flexfield.
79 -- p_add_information17 varchar2 Tax Address State.
80 -- p_add_information18 varchar2 Tax Address City.
81 -- p_add_information19 varchar2 Tax Address County.
82 -- p_add_information20 varchar2 Tax Address Zip.
83 -- p_party_id number Party for whom the address --HR/TCA merge
84 -- applies.
85 --
86 -- Post Success:
87 -- When the address is valid, the API sets the following out parameters.
88 --
89 -- Name Type Description
90 -- p_address_id number If p_validate is false, uniquely
91 -- identifies the address created.
92 -- If p_validate is true, set to
93 -- null.
94 -- p_object_version_number number If p_validate is false, set to
95 -- the version number of this
96 -- address. If p_validate is true,
97 -- set to null.
98 --
99 -- Post Failure:
100 -- The API does not create the address and raises an error.
101 --
102 -- Access Status:
103 -- Public.
104 -- {End Of Comments}
105 --
106
107 PROCEDURE create_cn_person_address
108 (p_validate IN BOOLEAN DEFAULT false
109 ,p_effective_date IN DATE
110 ,p_pradd_ovlapval_override IN BOOLEAN DEFAULT false
111 ,p_validate_county IN BOOLEAN DEFAULT true
112 ,p_person_id IN NUMBER DEFAULT null
113 ,p_primary_flag IN VARCHAR2
114 ,p_date_from IN DATE
115 ,p_date_to IN DATE DEFAULT null
116 ,p_address_type IN VARCHAR2 DEFAULT null
117 ,p_comments IN per_addresses.comments%TYPE DEFAULT null
118 ,p_address_line1 IN VARCHAR2
119 ,p_address_line2 IN VARCHAR2 DEFAULT null
120 ,p_province_city_sar IN VARCHAR2
121 ,p_postal_code IN VARCHAR2 DEFAULT null
122 ,p_country IN VARCHAR2
123 ,p_telephone IN VARCHAR2 DEFAULT null
124 ,p_fax IN VARCHAR2 DEFAULT null
125 ,p_addr_attribute_category IN VARCHAR2 DEFAULT null
126 ,p_addr_attribute1 IN VARCHAR2 DEFAULT null
127 ,p_addr_attribute2 IN VARCHAR2 DEFAULT null
128 ,p_addr_attribute3 IN VARCHAR2 DEFAULT null
129 ,p_addr_attribute4 IN VARCHAR2 DEFAULT null
130 ,p_addr_attribute5 IN VARCHAR2 DEFAULT null
131 ,p_addr_attribute6 IN VARCHAR2 DEFAULT null
132 ,p_addr_attribute7 IN VARCHAR2 DEFAULT null
133 ,p_addr_attribute8 IN VARCHAR2 DEFAULT null
134 ,p_addr_attribute9 IN VARCHAR2 DEFAULT null
135 ,p_addr_attribute10 IN VARCHAR2 DEFAULT null
136 ,p_addr_attribute11 IN VARCHAR2 DEFAULT null
137 ,p_addr_attribute12 IN VARCHAR2 DEFAULT null
138 ,p_addr_attribute13 IN VARCHAR2 DEFAULT null
139 ,p_addr_attribute14 IN VARCHAR2 DEFAULT null
140 ,p_addr_attribute15 IN VARCHAR2 DEFAULT null
141 ,p_addr_attribute16 IN VARCHAR2 DEFAULT null
142 ,p_addr_attribute17 IN VARCHAR2 DEFAULT null
143 ,p_addr_attribute18 IN VARCHAR2 DEFAULT null
144 ,p_addr_attribute19 IN VARCHAR2 DEFAULT null
145 ,p_addr_attribute20 IN VARCHAR2 DEFAULT null
146 ,p_add_information13 IN VARCHAR2 DEFAULT null
147 ,p_add_information14 IN VARCHAR2 DEFAULT null
148 ,p_add_information15 IN VARCHAR2 DEFAULT null
149 ,p_add_information16 IN VARCHAR2 DEFAULT null
150 ,p_add_information17 IN VARCHAR2 DEFAULT null
151 ,p_add_information18 IN VARCHAR2 DEFAULT null
152 ,p_add_information19 IN VARCHAR2 DEFAULT null
153 ,p_add_information20 IN VARCHAR2 DEFAULT null
154 ,p_party_id IN NUMBER DEFAULT null
155 ,p_address_id OUT NOCOPY NUMBER
156 ,p_object_version_number OUT NOCOPY NUMBER )
157 IS
158
159 --
160 -- Declare cursors and local variables
161 --
162 l_proc VARCHAR2(72) := g_package||'create_cn_person_address';
163 --
164 BEGIN
165
166 hr_cn_api.set_location(g_trace, 'Entering:'|| l_proc, 10);
167
168 --
169 -- Create Person Address details.
170 --
171 hr_cn_api.check_person (p_person_id, 'CN', trunc(p_effective_date));
172 --
173 hr_cn_api.set_location(g_trace, l_proc, 20);
174 --
175 hr_person_address_api.create_person_address
176 (p_validate => p_validate
177 ,p_effective_date => p_effective_date
178 ,p_pradd_ovlapval_override => p_pradd_ovlapval_override
179 ,p_validate_county => p_validate_county
180 ,p_person_id => p_person_id
181 ,p_primary_flag => p_primary_flag
182 ,p_style => 'CN_GLB'
183 ,p_date_from => p_date_from
184 ,p_date_to => p_date_to
185 ,p_address_type => p_address_type
186 ,p_comments => p_comments
187 ,p_address_line1 => p_address_line1
188 ,p_address_line2 => p_address_line2
189 ,p_town_or_city => p_province_city_sar
190 ,p_postal_code => p_postal_code
191 ,p_country => p_country
192 ,p_telephone_number_1 => p_telephone
193 ,p_telephone_number_2 => p_fax
194 ,p_addr_attribute_category => p_addr_attribute_category
195 ,p_addr_attribute1 => p_addr_attribute1
196 ,p_addr_attribute2 => p_addr_attribute2
197 ,p_addr_attribute3 => p_addr_attribute3
198 ,p_addr_attribute4 => p_addr_attribute4
199 ,p_addr_attribute5 => p_addr_attribute5
200 ,p_addr_attribute6 => p_addr_attribute6
201 ,p_addr_attribute7 => p_addr_attribute7
202 ,p_addr_attribute8 => p_addr_attribute8
203 ,p_addr_attribute9 => p_addr_attribute9
204 ,p_addr_attribute10 => p_addr_attribute10
205 ,p_addr_attribute11 => p_addr_attribute11
206 ,p_addr_attribute12 => p_addr_attribute12
207 ,p_addr_attribute13 => p_addr_attribute13
208 ,p_addr_attribute14 => p_addr_attribute14
209 ,p_addr_attribute15 => p_addr_attribute15
210 ,p_addr_attribute16 => p_addr_attribute16
211 ,p_addr_attribute17 => p_addr_attribute17
212 ,p_addr_attribute18 => p_addr_attribute18
213 ,p_addr_attribute19 => p_addr_attribute19
214 ,p_addr_attribute20 => p_addr_attribute20
215 ,p_add_information13 => p_add_information13
216 ,p_add_information14 => p_add_information14
217 ,p_add_information15 => p_add_information15
218 ,p_add_information16 => p_add_information16
219 ,p_add_information17 => p_add_information17
220 ,p_add_information18 => p_add_information18
221 ,p_add_information19 => p_add_information19
222 ,p_add_information20 => p_add_information20
223 ,p_party_id => p_party_id
224 ,p_address_id => p_address_id
225 ,p_object_version_number => p_object_version_number
226 );
227
228 --
229 hr_cn_api.set_location(g_trace, 'Leaving:'|| l_proc, 20);
230
231 End create_cn_person_address;
232
233 -- ----------------------------------------------------------------------------
234 -- |------------------------< update_cn_person_address >-------------------------|
235 -- ----------------------------------------------------------------------------
236 -- {Start Of Comments}
237 --
238 -- Description:
239 -- This API updates the address as identified by the in
240 -- parameter p_address_id and the in out parameter p_object_version_number.
241 --
242 -- Please note. If you set the p_validate_county flag to FALSE and do not
243 -- enter a county then the address will not be valid for US payroll
244 -- processing.
245 --
246 -- Prerequisites:
247 -- The address as identified by the in parameter p_address_id and the in out
248 -- parameter p_object_version_number must already exist.
249 --
250 -- The address_type attribute can only be used after QuickCodes have been
251 -- defined for the 'ADDRESS_TYPE' lookup type.
252 --
253 -- In Parameters:
254 -- Name Reqd Type Description
255 -- p_validate boolean If true, the database
256 -- remains unchanged. If false,
257 -- the address is updated.
258 -- p_effective_date Yes date Effective date.
259 -- p_validate_county boolean set to true
260 -- p_address_id Yes number The primary key of the
261 -- address.
262 -- p_object_version_number Yes number The current version of the
263 -- address to be updated.
264 -- p_date_from Yes date The date from which the
265 -- address applies.
266 -- p_date_to date The date on which the
267 -- address no longer applies.
268 -- p_person_id number Person for whom the address
269 -- applies.
270 -- p_address_type varchar2 Type of address.
271 -- p_comments long Comments.
272 -- p_address_line1 Yes varchar2 Line 1 of address.
273 -- p_address_line2 varchar2 Line 2 of address.
274 -- p_province_city_sar Yes varchar2 Province/city.
275 -- p_postal_code varchar2 Determined by p_style
276 -- (eg. Postcode for CN).
277 -- p_country Yes varchar2 Country.
278 -- p_telephone varchar2 Telephone number.
279 -- p_fax varchar2 Fax number.
280 -- p_addr_attribute_category varchar2 Determines context of the
281 -- addr_attribute Descriptive
282 -- flexfield in parameters.
283 -- p_addr_attribute1 varchar2 Descriptive flexfield.
284 -- p_addr_attribute2 varchar2 Descriptive flexfield.
285 -- p_addr_attribute3 varchar2 Descriptive flexfield.
286 -- p_addr_attribute4 varchar2 Descriptive flexfield.
287 -- p_addr_attribute5 varchar2 Descriptive flexfield.
288 -- p_addr_attribute6 varchar2 Descriptive flexfield.
289 -- p_addr_attribute7 varchar2 Descriptive flexfield.
290 -- p_addr_attribute8 varchar2 Descriptive flexfield.
291 -- p_addr_attribute9 varchar2 Descriptive flexfield.
292 -- p_addr_attribute10 varchar2 Descriptive flexfield.
293 -- p_addr_attribute11 varchar2 Descriptive flexfield.
294 -- p_addr_attribute12 varchar2 Descriptive flexfield.
295 -- p_addr_attribute13 varchar2 Descriptive flexfield.
296 -- p_addr_attribute14 varchar2 Descriptive flexfield.
297 -- p_addr_attribute15 varchar2 Descriptive flexfield.
298 -- p_addr_attribute16 varchar2 Descriptive flexfield.
299 -- p_addr_attribute17 varchar2 Descriptive flexfield.
300 -- p_addr_attribute18 varchar2 Descriptive flexfield.
301 -- p_addr_attribute19 varchar2 Descriptive flexfield.
302 -- p_addr_attribute20 varchar2 Descriptive flexfield.
303 -- p_add_information13 varchar2 Descriptive flexfield.
304 -- p_add_information14 varchar2 Descriptive flexfield.
305 -- p_add_information15 varchar2 Descriptive flexfield.
306 -- p_add_information16 varchar2 Descriptive flexfield.
307 -- p_add_information17 varchar2 Tax Address State.
308 -- p_add_information18 varchar2 Tax Address City.
309 -- p_add_information19 varchar2 Tax Address County.
310 -- p_add_information20 varchar2 Tax Address Zip.
311 -- p_party_id number Party for whom the address
312 -- applies.
313 --
314 -- Post Success:
315 -- When the address is valid, the API sets the following out parameters.
316 --
317 -- Name Type Description
318 -- p_object_version_number number If p_validate is false, set to
319 -- the version number of this
320 -- address. If p_validate is true,
321 -- set to null.
322 --
323 -- Post Failure:
324 -- The API does not update the address and raises an error.
325 --
326 -- Access Status:
327 -- Public.
328 --
329 -- {End Of Comments}
330 --
331
332 PROCEDURE update_cn_person_address
333 (p_validate IN BOOLEAN DEFAULT false
334 ,p_effective_date IN DATE
335 ,p_validate_county IN BOOLEAN DEFAULT true
336 ,p_address_id IN NUMBER
337 ,p_object_version_number IN OUT NOCOPY NUMBER
338 ,p_date_from IN DATE DEFAULT hr_api.g_date
339 ,p_date_to IN DATE DEFAULT hr_api.g_date
340 ,p_primary_flag IN VARCHAR2 DEFAULT hr_api.g_varchar2
341 ,p_address_type IN VARCHAR2 DEFAULT hr_api.g_varchar2
342 ,p_comments IN per_addresses.comments%TYPE DEFAULT null
343 ,p_address_line1 IN VARCHAR2 DEFAULT hr_api.g_varchar2
344 ,p_address_line2 IN VARCHAR2 DEFAULT hr_api.g_varchar2
345 ,p_province_city_sar IN VARCHAR2 DEFAULT hr_api.g_varchar2
346 ,p_postal_code IN VARCHAR2 DEFAULT hr_api.g_varchar2
347 ,p_country IN VARCHAR2 DEFAULT hr_api.g_varchar2
348 ,p_telephone IN VARCHAR2 DEFAULT hr_api.g_varchar2
349 ,p_fax IN VARCHAR2 DEFAULT hr_api.g_varchar2
350 ,p_addr_attribute_category IN VARCHAR2 DEFAULT hr_api.g_varchar2
351 ,p_addr_attribute1 IN VARCHAR2 DEFAULT hr_api.g_varchar2
352 ,p_addr_attribute2 IN VARCHAR2 DEFAULT hr_api.g_varchar2
353 ,p_addr_attribute3 IN VARCHAR2 DEFAULT hr_api.g_varchar2
354 ,p_addr_attribute4 IN VARCHAR2 DEFAULT hr_api.g_varchar2
355 ,p_addr_attribute5 IN VARCHAR2 DEFAULT hr_api.g_varchar2
356 ,p_addr_attribute6 IN VARCHAR2 DEFAULT hr_api.g_varchar2
357 ,p_addr_attribute7 IN VARCHAR2 DEFAULT hr_api.g_varchar2
358 ,p_addr_attribute8 IN VARCHAR2 DEFAULT hr_api.g_varchar2
359 ,p_addr_attribute9 IN VARCHAR2 DEFAULT hr_api.g_varchar2
360 ,p_addr_attribute10 IN VARCHAR2 DEFAULT hr_api.g_varchar2
361 ,p_addr_attribute11 IN VARCHAR2 DEFAULT hr_api.g_varchar2
362 ,p_addr_attribute12 IN VARCHAR2 DEFAULT hr_api.g_varchar2
363 ,p_addr_attribute13 IN VARCHAR2 DEFAULT hr_api.g_varchar2
364 ,p_addr_attribute14 IN VARCHAR2 DEFAULT hr_api.g_varchar2
365 ,p_addr_attribute15 IN VARCHAR2 DEFAULT hr_api.g_varchar2
366 ,p_addr_attribute16 IN VARCHAR2 DEFAULT hr_api.g_varchar2
367 ,p_addr_attribute17 IN VARCHAR2 DEFAULT hr_api.g_varchar2
368 ,p_addr_attribute18 IN VARCHAR2 DEFAULT hr_api.g_varchar2
369 ,p_addr_attribute19 IN VARCHAR2 DEFAULT hr_api.g_varchar2
370 ,p_addr_attribute20 IN VARCHAR2 DEFAULT hr_api.g_varchar2
371 ,p_add_information13 IN VARCHAR2 DEFAULT hr_api.g_varchar2
372 ,p_add_information14 IN VARCHAR2 DEFAULT hr_api.g_varchar2
373 ,p_add_information15 IN VARCHAR2 DEFAULT hr_api.g_varchar2
374 ,p_add_information16 IN VARCHAR2 DEFAULT hr_api.g_varchar2
375 ,p_add_information17 IN VARCHAR2 DEFAULT hr_api.g_varchar2
376 ,p_add_information18 IN VARCHAR2 DEFAULT hr_api.g_varchar2
377 ,p_add_information19 IN VARCHAR2 DEFAULT hr_api.g_varchar2
378 ,p_add_information20 IN VARCHAR2 DEFAULT hr_api.g_varchar2
379 ,p_party_id IN NUMBER DEFAULT hr_api.g_number)
380 IS
381
382 --
383 -- Declare cursors and local variables
384 --
385 l_proc VARCHAR2(72) := g_package||'update_cn_person_address';
386 l_style per_addresses.style%TYPE;
387 --
388 CURSOR csr_add_style IS
389 SELECT style
390 FROM per_addresses
391 WHERE address_id = p_address_id;
392 --
393 BEGIN
394
395 hr_cn_api.set_location(g_trace, 'Entering:'|| l_proc, 5);
396
397 --
398 -- Check that the address is CN_GLB style.
399 --
400 hr_cn_api.check_address (p_address_id => p_address_id,
401 p_address_style => 'CN_GLB');
402 --
403
404 hr_cn_api.set_location(g_trace, l_proc, 5);
405
406 --
407 -- Update Person Address details.
408 --
409 hr_person_address_api.update_person_address
410 (p_validate => p_validate
411 ,p_effective_date => p_effective_date
412 ,p_validate_county => p_validate_county
413 ,p_address_id => p_address_id
414 ,p_object_version_number => p_object_version_number
415 ,p_date_from => p_date_from
416 ,p_date_to => p_date_to
417 ,p_primary_flag => p_primary_flag
418 ,p_address_type => p_address_type
419 ,p_comments => p_comments
420 ,p_address_line1 => p_address_line1
421 ,p_address_line2 => p_address_line2
422 ,p_town_or_city => p_province_city_sar
423 ,p_postal_code => p_postal_code
424 ,p_country => p_country
425 ,p_telephone_number_1 => p_telephone
426 ,p_telephone_number_2 => p_fax
427 ,p_addr_attribute_category => p_addr_attribute_category
428 ,p_addr_attribute1 => p_addr_attribute1
429 ,p_addr_attribute2 => p_addr_attribute2
430 ,p_addr_attribute3 => p_addr_attribute3
431 ,p_addr_attribute4 => p_addr_attribute4
432 ,p_addr_attribute5 => p_addr_attribute5
433 ,p_addr_attribute6 => p_addr_attribute6
434 ,p_addr_attribute7 => p_addr_attribute7
435 ,p_addr_attribute8 => p_addr_attribute8
436 ,p_addr_attribute9 => p_addr_attribute9
437 ,p_addr_attribute10 => p_addr_attribute10
438 ,p_addr_attribute11 => p_addr_attribute11
439 ,p_addr_attribute12 => p_addr_attribute12
440 ,p_addr_attribute13 => p_addr_attribute13
441 ,p_addr_attribute14 => p_addr_attribute14
442 ,p_addr_attribute15 => p_addr_attribute15
443 ,p_addr_attribute16 => p_addr_attribute16
444 ,p_addr_attribute17 => p_addr_attribute17
445 ,p_addr_attribute18 => p_addr_attribute18
446 ,p_addr_attribute19 => p_addr_attribute19
447 ,p_addr_attribute20 => p_addr_attribute20
448 ,p_add_information13 => p_add_information13
449 ,p_add_information14 => p_add_information14
450 ,p_add_information15 => p_add_information15
451 ,p_add_information16 => p_add_information16
452 ,p_add_information17 => p_add_information17
453 ,p_add_information18 => p_add_information18
454 ,p_add_information19 => p_add_information19
455 ,p_add_information20 => p_add_information20
456 ,p_party_id => p_party_id
457 );
458
459 hr_cn_api.set_location(g_trace, 'Leaving:'|| l_proc, 5);
460 --
461
462 End update_cn_person_address;
463
464 END hr_cn_person_address_api;