[Home] [Help]
PACKAGE BODY: APPS.HZ_LOCATIONS_PKG
Source
1 PACKAGE BODY hz_locations_pkg AS
2 /*$Header: ARHLOCTB.pls 120.3 2005/07/29 01:24:49 jhuang ship $ */
3
4 g_miss_content_source_type CONSTANT VARCHAR2(30) := 'USER_ENTERED';
5
6 -- J. del Callar: global variable for the default geometry status
7 g_default_geometry_status_code VARCHAR2(30) := 'DIRTY';
8
9 PROCEDURE insert_row (
10 x_location_id IN OUT NOCOPY NUMBER,
11 x_attribute_category IN VARCHAR2,
12 x_attribute1 IN VARCHAR2,
13 x_attribute2 IN VARCHAR2,
14 x_attribute3 IN VARCHAR2,
15 x_attribute4 IN VARCHAR2,
16 x_attribute5 IN VARCHAR2,
17 x_attribute6 IN VARCHAR2,
18 x_attribute7 IN VARCHAR2,
19 x_attribute8 IN VARCHAR2,
20 x_attribute9 IN VARCHAR2,
21 x_attribute10 IN VARCHAR2,
22 x_attribute11 IN VARCHAR2,
23 x_attribute12 IN VARCHAR2,
24 x_attribute13 IN VARCHAR2,
25 x_attribute14 IN VARCHAR2,
26 x_attribute15 IN VARCHAR2,
27 x_attribute16 IN VARCHAR2,
28 x_attribute17 IN VARCHAR2,
29 x_attribute18 IN VARCHAR2,
30 x_attribute19 IN VARCHAR2,
31 x_attribute20 IN VARCHAR2,
32 x_orig_system_reference IN VARCHAR2,
33 x_country IN VARCHAR2,
34 x_address1 IN VARCHAR2,
35 x_address2 IN VARCHAR2,
36 x_address3 IN VARCHAR2,
37 x_address4 IN VARCHAR2,
38 x_city IN VARCHAR2,
39 x_postal_code IN VARCHAR2,
40 x_state IN VARCHAR2,
41 x_province IN VARCHAR2,
42 x_county IN VARCHAR2,
43 x_address_key IN VARCHAR2,
44 x_address_style IN VARCHAR2,
45 x_validated_flag IN VARCHAR2,
46 x_address_lines_phonetic IN VARCHAR2,
47 x_po_box_number IN VARCHAR2,
48 x_house_number IN VARCHAR2,
49 x_street_suffix IN VARCHAR2,
50 x_street IN VARCHAR2,
51 x_street_number IN VARCHAR2,
52 x_floor IN VARCHAR2,
53 x_suite IN VARCHAR2,
54 x_postal_plus4_code IN VARCHAR2,
55 x_position IN VARCHAR2,
56 x_location_directions IN VARCHAR2,
57 x_address_effective_date IN DATE,
58 x_address_expiration_date IN DATE,
59 x_clli_code IN VARCHAR2,
60 x_language IN VARCHAR2,
61 x_short_description IN VARCHAR2,
62 x_description IN VARCHAR2,
63 x_content_source_type IN VARCHAR2,
64 x_loc_hierarchy_id IN NUMBER,
65 x_sales_tax_geocode IN VARCHAR2,
66 x_sales_tax_inside_city_limits IN VARCHAR2,
67 x_fa_location_id IN NUMBER,
68 x_geometry IN mdsys.sdo_geometry,
69 x_object_version_number IN NUMBER,
70 x_timezone_id IN NUMBER,
71 x_created_by_module IN VARCHAR2,
72 x_application_id IN NUMBER,
73 x_actual_content_source IN VARCHAR2,
74 x_geometry_status_code IN VARCHAR2 DEFAULT NULL,
75 -- BUG 2670546
76 x_delivery_point_code IN VARCHAR2
77 ) IS
78
79 l_success VARCHAR2(1) := 'N';
80 l_geometry mdsys.sdo_geometry := NULL;
81 l_primary_key_passed BOOLEAN := FALSE;
82
83 BEGIN
84
85 -- The following lines are used to take care of the situation
86 -- when content_source_type is not USER_ENTERED, because of
87 -- policy funcation, when we do unique validation, we cannot
88 -- see all of the records. Thus, we have to double check here
89 -- and raise corresponding exception. We donot need to do anything
90 -- for those tables without polity functions.
91
92 IF x_location_id IS NOT NULL AND
93 x_location_id <> fnd_api.g_miss_num
94 THEN
95 l_primary_key_passed := TRUE;
96 END IF;
97
98 IF x_geometry.sdo_gtype <> FND_API.G_MISS_NUM OR
99 x_geometry.sdo_srid <> FND_API.G_MISS_NUM OR
100 x_geometry.sdo_point IS NOT NULL OR
101 x_geometry.sdo_elem_info IS NOT NULL OR
102 x_geometry.sdo_ordinates IS NOT NULL
103 THEN
104 l_geometry := x_GEOMETRY;
105 END IF;
106
107 WHILE l_success = 'N' LOOP
108 BEGIN
109 INSERT INTO hz_locations (
110 location_id,
111 last_update_date,
112 last_updated_by,
113 creation_date,
114 created_by,
115 last_update_login,
116 request_id,
117 program_application_id,
118 program_id,
119 program_update_date,
120 attribute_category,
121 attribute1,
122 attribute2,
123 attribute3,
124 attribute4,
125 attribute5,
126 attribute6,
127 attribute7,
128 attribute8,
129 attribute9,
130 attribute10,
131 attribute11,
132 attribute12,
133 attribute13,
134 attribute14,
135 attribute15,
136 attribute16,
137 attribute17,
138 attribute18,
139 attribute19,
140 attribute20,
141 orig_system_reference,
142 country,
143 address1,
144 address2,
145 address3,
146 address4,
147 city,
148 postal_code,
149 state,
150 province,
151 county,
152 address_key,
153 address_style,
154 validated_flag,
155 address_lines_phonetic,
156 postal_plus4_code,
157 position,
158 location_directions,
159 address_effective_date,
160 address_expiration_date,
161 clli_code,
162 language,
163 short_description,
164 description,
165 content_source_type,
166 loc_hierarchy_id,
167 sales_tax_geocode,
168 sales_tax_inside_city_limits,
169 fa_location_id,
170 geometry,
171 object_version_number,
172 timezone_id,
173 created_by_module,
174 application_id,
175 actual_content_source,
176 geometry_status_code,
177 -- Bug 2670546
178 delivery_point_code
179 )
180 VALUES (
181 DECODE(x_location_id,
182 fnd_api.g_miss_num, hr_locations_s.NEXTVAL,
183 NULL, hr_locations_s.NEXTVAL,
184 x_location_id),
185 hz_utility_v2pub.last_update_date,
186 hz_utility_v2pub.last_updated_by,
187 hz_utility_v2pub.creation_date,
188 hz_utility_v2pub.created_by,
189 hz_utility_v2pub.last_update_login,
190 hz_utility_v2pub.request_id,
191 hz_utility_v2pub.program_application_id,
192 hz_utility_v2pub.program_id,
193 hz_utility_v2pub.program_update_date,
194 DECODE(x_attribute_category,
195 fnd_api.g_miss_char, NULL,
196 x_attribute_category),
197 DECODE(x_attribute1, fnd_api.g_miss_char, NULL, x_attribute1),
198 DECODE(x_attribute2, fnd_api.g_miss_char, NULL, x_attribute2),
199 DECODE(x_attribute3, fnd_api.g_miss_char, NULL, x_attribute3),
200 DECODE(x_attribute4, fnd_api.g_miss_char, NULL, x_attribute4),
201 DECODE(x_attribute5, fnd_api.g_miss_char, NULL, x_attribute5),
202 DECODE(x_attribute6, fnd_api.g_miss_char, NULL, x_attribute6),
203 DECODE(x_attribute7, fnd_api.g_miss_char, NULL, x_attribute7),
204 DECODE(x_attribute8, fnd_api.g_miss_char, NULL, x_attribute8),
205 DECODE(x_attribute9, fnd_api.g_miss_char, NULL, x_attribute9),
206 DECODE(x_attribute10, fnd_api.g_miss_char, NULL, x_attribute10),
207 DECODE(x_attribute11, fnd_api.g_miss_char, NULL, x_attribute11),
208 DECODE(x_attribute12, fnd_api.g_miss_char, NULL, x_attribute12),
209 DECODE(x_attribute13, fnd_api.g_miss_char, NULL, x_attribute13),
210 DECODE(x_attribute14, fnd_api.g_miss_char, NULL, x_attribute14),
211 DECODE(x_attribute15, fnd_api.g_miss_char, NULL, x_attribute15),
212 DECODE(x_attribute16, fnd_api.g_miss_char, NULL, x_attribute16),
213 DECODE(x_attribute17, fnd_api.g_miss_char, NULL, x_attribute17),
214 DECODE(x_attribute18, fnd_api.g_miss_char, NULL, x_attribute18),
215 DECODE(x_attribute19, fnd_api.g_miss_char, NULL, x_attribute19),
216 DECODE(x_attribute20, fnd_api.g_miss_char, NULL, x_attribute20),
217 DECODE(x_orig_system_reference,
218 fnd_api.g_miss_char, TO_CHAR(NVL(x_location_id,
219 hr_locations_s.CURRVAL)),
220 NULL, TO_CHAR(NVL(x_location_id, hr_locations_s.CURRVAL)),
221 x_orig_system_reference),
222 DECODE(x_country, fnd_api.g_miss_char, NULL, x_country),
223 DECODE(x_address1, fnd_api.g_miss_char, NULL, x_address1),
224 DECODE(x_address2, fnd_api.g_miss_char, NULL, x_address2),
225 DECODE(x_address3, fnd_api.g_miss_char, NULL, x_address3),
226 DECODE(x_address4, fnd_api.g_miss_char, NULL, x_address4),
227 DECODE(x_city, fnd_api.g_miss_char, NULL, x_city),
228 DECODE(x_postal_code, fnd_api.g_miss_char, NULL, x_postal_code),
229 DECODE(x_state, fnd_api.g_miss_char, NULL, x_state),
230 DECODE(x_province, fnd_api.g_miss_char, NULL, x_province),
231 DECODE(x_county, fnd_api.g_miss_char, NULL, x_county),
232 DECODE(x_address_key, fnd_api.g_miss_char, NULL, x_address_key),
233 DECODE(x_address_style, fnd_api.g_miss_char, NULL, x_address_style),
234 DECODE(x_validated_flag,
235 fnd_api.g_miss_char, 'N',
236 NULL, 'N',
237 x_validated_flag),
238 DECODE(x_address_lines_phonetic,
239 fnd_api.g_miss_char, NULL,
240 x_address_lines_phonetic),
241 DECODE(x_postal_plus4_code,
242 fnd_api.g_miss_char, NULL,
243 x_postal_plus4_code),
244 DECODE(x_position, fnd_api.g_miss_char, NULL, x_position),
245 DECODE(x_location_directions,
246 fnd_api.g_miss_char, NULL,
247 x_location_directions),
248 DECODE(x_address_effective_date,
249 fnd_api.g_miss_date, TO_DATE(NULL),
250 x_address_effective_date),
251 DECODE(x_address_expiration_date,
252 fnd_api.g_miss_date, TO_DATE(NULL),
253 x_address_expiration_date),
254 DECODE(x_clli_code, fnd_api.g_miss_char, NULL, x_clli_code),
255 DECODE(x_language, fnd_api.g_miss_char, NULL, x_language),
256 DECODE(x_short_description,
257 fnd_api.g_miss_char, NULL,
258 x_short_description),
259 DECODE(x_description, fnd_api.g_miss_char, NULL, x_description),
260 DECODE(x_content_source_type,
261 fnd_api.g_miss_char, g_miss_content_source_type,
262 NULL, g_miss_content_source_type,
263 x_content_source_type),
264 DECODE(x_loc_hierarchy_id,
265 fnd_api.g_miss_num, NULL,
266 x_loc_hierarchy_id),
267 DECODE(x_sales_tax_geocode,
268 fnd_api.g_miss_char, NULL,
269 x_sales_tax_geocode),
270 DECODE(x_sales_tax_inside_city_limits,
271 fnd_api.g_miss_char, '1',
272 NULL, '1',
273 x_sales_tax_inside_city_limits),
274 DECODE(x_fa_location_id, fnd_api.g_miss_num, NULL, x_fa_location_id),
275 l_geometry,
276 DECODE(x_object_version_number,
277 fnd_api.g_miss_num, NULL,
278 x_object_version_number),
279 DECODE(x_timezone_id, fnd_api.g_miss_num, NULL, x_timezone_id),
280 DECODE(x_created_by_module,
281 fnd_api.g_miss_char, NULL,
282 x_created_by_module),
283 DECODE(x_application_id, fnd_api.g_miss_num, NULL, x_application_id),
284 DECODE(x_actual_content_source,
285 fnd_api.g_miss_char, g_miss_content_source_type,
286 NULL, g_miss_content_source_type,
287 x_actual_content_source),
288 DECODE(x_geometry_status_code,
289 NULL, g_default_geometry_status_code,
290 fnd_api.g_miss_char, NULL,
291 x_geometry_status_code),
292 -- Bug 2670546.
293 DECODE(x_delivery_point_code,
294 fnd_api.g_miss_char,
295 NULL,
296 x_delivery_point_code)
297 ) RETURNING
298 location_id
299 INTO
300 x_location_id;
301
302 l_success := 'Y';
303
304 EXCEPTION
305 WHEN DUP_VAL_ON_INDEX THEN
306 IF INSTRB(SQLERRM, 'HZ_LOCATIONS_U1') <> 0 OR
307 INSTRB(SQLERRM, 'HZ_LOCATIONS_PK') <> 0
308 THEN
309 IF l_primary_key_passed THEN
310 fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
311 fnd_message.set_token('COLUMN', 'location_id');
312 fnd_msg_pub.add;
313 RAISE fnd_api.g_exc_error;
314 END IF;
315
316 DECLARE
317 l_count NUMBER;
318 l_dummy VARCHAR2(1);
319 BEGIN
320 l_count := 1;
321 WHILE l_count > 0 LOOP
322 SELECT hr_locations_s.NEXTVAL
323 INTO x_location_id
324 FROM dual;
325
326 BEGIN
327 SELECT 'Y'
328 INTO l_dummy
329 FROM hz_locations hl
330 WHERE hl.location_id = x_location_id;
331 l_count := 1;
332 EXCEPTION
333 WHEN NO_DATA_FOUND THEN
334 l_count := 0;
335 END;
336 END LOOP;
337 END;
338 ELSE
339 RAISE;
340 END IF;
341 END;
342 END LOOP;
343 END insert_row;
344
345 PROCEDURE update_row (
346 x_rowid IN OUT NOCOPY VARCHAR2,
347 x_location_id IN NUMBER,
348 x_attribute_category IN VARCHAR2,
349 x_attribute1 IN VARCHAR2,
350 x_attribute2 IN VARCHAR2,
351 x_attribute3 IN VARCHAR2,
352 x_attribute4 IN VARCHAR2,
353 x_attribute5 IN VARCHAR2,
354 x_attribute6 IN VARCHAR2,
355 x_attribute7 IN VARCHAR2,
356 x_attribute8 IN VARCHAR2,
357 x_attribute9 IN VARCHAR2,
358 x_attribute10 IN VARCHAR2,
359 x_attribute11 IN VARCHAR2,
360 x_attribute12 IN VARCHAR2,
361 x_attribute13 IN VARCHAR2,
362 x_attribute14 IN VARCHAR2,
363 x_attribute15 IN VARCHAR2,
364 x_attribute16 IN VARCHAR2,
365 x_attribute17 IN VARCHAR2,
366 x_attribute18 IN VARCHAR2,
367 x_attribute19 IN VARCHAR2,
368 x_attribute20 IN VARCHAR2,
369 x_orig_system_reference IN VARCHAR2,
370 x_country IN VARCHAR2,
371 x_address1 IN VARCHAR2,
372 x_address2 IN VARCHAR2,
373 x_address3 IN VARCHAR2,
374 x_address4 IN VARCHAR2,
375 x_city IN VARCHAR2,
376 x_postal_code IN VARCHAR2,
377 x_state IN VARCHAR2,
378 x_province IN VARCHAR2,
379 x_county IN VARCHAR2,
380 x_address_key IN VARCHAR2,
381 x_address_style IN VARCHAR2,
382 x_validated_flag IN VARCHAR2,
383 x_address_lines_phonetic IN VARCHAR2,
384 x_po_box_number IN VARCHAR2,
385 x_house_number IN VARCHAR2,
386 x_street_suffix IN VARCHAR2,
387 x_street IN VARCHAR2,
388 x_street_number IN VARCHAR2,
389 x_floor IN VARCHAR2,
390 x_suite IN VARCHAR2,
391 x_postal_plus4_code IN VARCHAR2,
392 x_position IN VARCHAR2,
393 x_location_directions IN VARCHAR2,
394 x_address_effective_date IN DATE,
395 x_address_expiration_date IN DATE,
396 x_clli_code IN VARCHAR2,
397 x_language IN VARCHAR2,
398 x_short_description IN VARCHAR2,
399 x_description IN VARCHAR2,
400 x_content_source_type IN VARCHAR2,
401 x_loc_hierarchy_id IN NUMBER,
402 x_sales_tax_geocode IN VARCHAR2,
403 x_sales_tax_inside_city_limits IN VARCHAR2,
404 x_fa_location_id IN NUMBER,
405 x_geometry IN mdsys.sdo_geometry,
406 x_object_version_number IN NUMBER,
407 x_timezone_id IN NUMBER,
408 x_created_by_module IN VARCHAR2,
409 x_application_id IN NUMBER,
410 x_actual_content_source IN VARCHAR2 DEFAULT NULL,
411 x_geometry_status_code IN VARCHAR2 DEFAULT NULL,
412 -- Bug 2670546
413 x_delivery_point_code IN VARCHAR2
414 ) IS
415 BEGIN
416 UPDATE hz_locations
417 SET location_id = DECODE(x_location_id,
418 NULL, location_id,
419 fnd_api.g_miss_num, NULL,
420 x_location_id),
421 last_update_date = hz_utility_v2pub.last_update_date,
422 last_updated_by = hz_utility_v2pub.last_updated_by,
423 creation_date = creation_date,
424 created_by = created_by,
425 last_update_login = hz_utility_v2pub.last_update_login,
426 request_id = hz_utility_v2pub.request_id,
427 program_application_id = hz_utility_v2pub.program_application_id,
428 program_id = hz_utility_v2pub.program_id,
429 program_update_date = hz_utility_v2pub.program_update_date,
430 attribute_category = DECODE(x_attribute_category,
431 NULL, attribute_category,
432 fnd_api.g_miss_char, NULL,
433 x_attribute_category),
434 attribute1 = DECODE(x_attribute1,
435 NULL, attribute1,
436 fnd_api.g_miss_char, NULL,
437 x_attribute1),
438 attribute2 = DECODE(x_attribute2,
439 NULL, attribute2,
440 fnd_api.g_miss_char, NULL,
441 x_attribute2),
442 attribute3 = DECODE(x_attribute3,
443 NULL, attribute3,
444 fnd_api.g_miss_char, NULL,
445 x_attribute3),
446 attribute4 = DECODE(x_attribute4,
447 NULL, attribute4,
448 fnd_api.g_miss_char, NULL,
449 x_attribute4),
450 attribute5 = DECODE(x_attribute5,
451 NULL, attribute5,
452 fnd_api.g_miss_char, NULL,
453 x_attribute5),
454 attribute6 = DECODE(x_attribute6,
455 NULL, attribute6,
456 fnd_api.g_miss_char, NULL,
457 x_attribute6),
458 attribute7 = DECODE(x_attribute7,
459 NULL, attribute7,
460 fnd_api.g_miss_char, NULL,
461 x_attribute7),
462 attribute8 = DECODE(x_attribute8,
463 NULL, attribute8,
464 fnd_api.g_miss_char, NULL,
465 x_attribute8),
466 attribute9 = DECODE(x_attribute9,
467 NULL, attribute9,
468 fnd_api.g_miss_char, NULL,
469 x_attribute9),
470 attribute10 = DECODE(x_attribute10,
471 NULL, attribute10,
472 fnd_api.g_miss_char, NULL,
473 x_attribute10),
474 attribute11 = DECODE(x_attribute11,
475 NULL, attribute11,
476 fnd_api.g_miss_char, NULL,
477 x_attribute11),
478 attribute12 = DECODE(x_attribute12,
479 NULL, attribute12,
480 fnd_api.g_miss_char, NULL,
481 x_attribute12),
482 attribute13 = DECODE(x_attribute13,
483 NULL, attribute13,
484 fnd_api.g_miss_char, NULL,
485 x_attribute13),
486 attribute14 = DECODE(x_attribute14,
487 NULL, attribute14,
488 fnd_api.g_miss_char, NULL,
489 x_attribute14),
490 attribute15 = DECODE(x_attribute15,
491 NULL, attribute15,
492 fnd_api.g_miss_char, NULL,
493 x_attribute15),
494 attribute16 = DECODE(x_attribute16,
495 NULL, attribute16,
496 fnd_api.g_miss_char, NULL,
497 x_attribute16),
498 attribute17 = DECODE(x_attribute17,
499 NULL, attribute17,
500 fnd_api.g_miss_char, NULL,
501 x_attribute17),
502 attribute18 = DECODE(x_attribute18,
503 NULL, attribute18,
504 fnd_api.g_miss_char, NULL,
505 x_attribute18),
506 attribute19 = DECODE(x_attribute19,
507 NULL, attribute19,
508 fnd_api.g_miss_char, NULL,
509 x_attribute19),
510 attribute20 = DECODE(x_attribute20,
511 NULL, attribute20,
512 fnd_api.g_miss_char, NULL,
513 x_attribute20),
514 orig_system_reference
515 = DECODE(x_orig_system_reference,
516 NULL, orig_system_reference,
517 fnd_api.g_miss_char, orig_system_reference,
518 x_orig_system_reference),
519 country = DECODE(x_country,
520 NULL, country,
521 fnd_api.g_miss_char, NULL,
522 x_country),
523 address1 = DECODE(x_address1,
524 NULL, address1,
525 fnd_api.g_miss_char, NULL,
526 x_address1),
527 address2 = DECODE(x_address2,
528 NULL, address2,
529 fnd_api.g_miss_char, NULL,
530 x_address2),
531 address3 = DECODE(x_address3,
532 NULL, address3,
533 fnd_api.g_miss_char, NULL,
534 x_address3),
535 address4 = DECODE(x_address4,
536 NULL, address4,
537 fnd_api.g_miss_char, NULL,
538 x_address4),
539 city = DECODE(x_city,
540 NULL, city,
541 fnd_api.g_miss_char, NULL,
542 x_city),
543 postal_code = DECODE(x_postal_code,
544 NULL, postal_code,
545 fnd_api.g_miss_char, NULL,
546 x_postal_code),
547 state = DECODE(x_state,
548 NULL, state,
549 fnd_api.g_miss_char, NULL,
550 x_state),
551 province = DECODE(x_province,
552 NULL, province,
553 fnd_api.g_miss_char, NULL,
554 x_province),
555 county = DECODE(x_county,
556 NULL, county,
557 fnd_api.g_miss_char, NULL,
558 x_county),
559 address_key = DECODE(x_address_key,
560 NULL, address_key,
561 fnd_api.g_miss_char, NULL,
562 x_address_key),
563 address_style = DECODE(x_address_style,
564 NULL, address_style,
565 fnd_api.g_miss_char, NULL,
566 x_address_style),
567 validated_flag = DECODE(x_validated_flag,
568 NULL, validated_flag,
569 fnd_api.g_miss_char, 'N',
570 x_validated_flag),
571 address_lines_phonetic = DECODE(x_address_lines_phonetic,
572 NULL, address_lines_phonetic,
573 fnd_api.g_miss_char, NULL,
574 x_address_lines_phonetic),
575 postal_plus4_code = DECODE(x_postal_plus4_code,
576 NULL, postal_plus4_code,
577 fnd_api.g_miss_char, NULL,
578 x_postal_plus4_code),
579 position = DECODE(x_position,
580 NULL, position,
581 fnd_api.g_miss_char, NULL,
582 x_position),
583 location_directions = DECODE(x_location_directions,
584 NULL, location_directions,
585 fnd_api.g_miss_char, NULL,
586 x_location_directions),
587 address_effective_date = DECODE(x_address_effective_date,
588 NULL, address_effective_date,
589 fnd_api.g_miss_date, NULL,
590 x_address_effective_date),
591 address_expiration_date = DECODE(x_address_expiration_date,
592 NULL, address_expiration_date,
593 fnd_api.g_miss_date, NULL,
594 x_address_expiration_date),
595 clli_code = DECODE(x_clli_code,
596 NULL, clli_code,
597 fnd_api.g_miss_char, NULL,
598 x_clli_code),
599 language = DECODE(x_language,
600 NULL, language,
601 fnd_api.g_miss_char, NULL,
602 x_language),
603 short_description = DECODE(x_short_description,
604 NULL, short_description,
605 fnd_api.g_miss_char, NULL,
606 x_short_description),
607 description = DECODE(x_description,
608 NULL, description,
609 fnd_api.g_miss_char, NULL,
610 x_description),
611 content_source_type = DECODE(x_content_source_type,
612 NULL, content_source_type,
613 fnd_api.g_miss_char, NULL,
614 x_content_source_type),
615 loc_hierarchy_id = DECODE(x_loc_hierarchy_id,
616 NULL, loc_hierarchy_id,
617 fnd_api.g_miss_num, NULL,
618 x_loc_hierarchy_id),
619 sales_tax_geocode = DECODE(x_sales_tax_geocode,
620 NULL, sales_tax_geocode,
621 fnd_api.g_miss_char, NULL,
622 x_sales_tax_geocode),
623 sales_tax_inside_city_limits
624 = DECODE(x_sales_tax_inside_city_limits,
625 NULL, sales_tax_inside_city_limits,
626 fnd_api.g_miss_char, '1',
627 x_sales_tax_inside_city_limits),
628 fa_location_id = DECODE(x_fa_location_id,
629 NULL, fa_location_id,
630 fnd_api.g_miss_num, NULL,
631 x_fa_location_id),
632 geometry = x_geometry,
633 object_version_number = DECODE(x_object_version_number,
634 NULL, object_version_number,
635 fnd_api.g_miss_num, NULL,
636 x_object_version_number),
637 timezone_id = DECODE(x_timezone_id,
638 NULL, timezone_id,
639 fnd_api.g_miss_num, NULL,
640 x_timezone_id),
641 created_by_module = DECODE(x_created_by_module,
642 NULL, created_by_module,
643 fnd_api.g_miss_char, NULL,
644 x_created_by_module),
645 application_id = DECODE(x_application_id,
646 NULL, application_id,
647 fnd_api.g_miss_num, NULL,
648 x_application_id),
649 actual_content_source = DECODE(x_actual_content_source,
650 NULL, actual_content_source,
651 fnd_api.g_miss_char, NULL,
652 x_actual_content_source),
653 geometry_status_code = DECODE(x_geometry_status_code,
654 NULL, geometry_status_code,
655 fnd_api.g_miss_char, NULL,
656 x_geometry_status_code),
657 -- Bug 2670546.
658 delivery_point_code = DECODE(x_delivery_point_code,
659 NULL,delivery_point_code,
660 fnd_api.g_miss_char,NULL,
661 x_delivery_point_code)
662 WHERE ROWID = x_rowid;
663
664 IF (SQL%NOTFOUND) THEN
665 RAISE NO_DATA_FOUND;
666 END IF;
667 END update_row;
668
669
670 PROCEDURE lock_row (
671 x_rowid IN OUT NOCOPY VARCHAR2,
672 x_location_id IN NUMBER,
673 x_last_update_date IN DATE,
674 x_last_updated_by IN NUMBER,
675 x_creation_date IN DATE,
676 x_created_by IN NUMBER,
677 x_last_update_login IN NUMBER,
678 x_request_id IN NUMBER,
679 x_program_application_id IN NUMBER,
680 x_program_id IN NUMBER,
681 x_program_update_date IN DATE,
682 x_attribute_category IN VARCHAR2,
683 x_attribute1 IN VARCHAR2,
684 x_attribute2 IN VARCHAR2,
685 x_attribute3 IN VARCHAR2,
686 x_attribute4 IN VARCHAR2,
687 x_attribute5 IN VARCHAR2,
688 x_attribute6 IN VARCHAR2,
689 x_attribute7 IN VARCHAR2,
690 x_attribute8 IN VARCHAR2,
691 x_attribute9 IN VARCHAR2,
692 x_attribute10 IN VARCHAR2,
693 x_attribute11 IN VARCHAR2,
694 x_attribute12 IN VARCHAR2,
695 x_attribute13 IN VARCHAR2,
696 x_attribute14 IN VARCHAR2,
697 x_attribute15 IN VARCHAR2,
698 x_attribute16 IN VARCHAR2,
699 x_attribute17 IN VARCHAR2,
700 x_attribute18 IN VARCHAR2,
701 x_attribute19 IN VARCHAR2,
702 x_attribute20 IN VARCHAR2,
703 x_orig_system_reference IN VARCHAR2,
704 x_country IN VARCHAR2,
705 x_address1 IN VARCHAR2,
706 x_address2 IN VARCHAR2,
707 x_address3 IN VARCHAR2,
708 x_address4 IN VARCHAR2,
709 x_city IN VARCHAR2,
710 x_postal_code IN VARCHAR2,
711 x_state IN VARCHAR2,
712 x_province IN VARCHAR2,
713 x_county IN VARCHAR2,
714 x_address_key IN VARCHAR2,
715 x_address_style IN VARCHAR2,
716 x_validated_flag IN VARCHAR2,
717 x_address_lines_phonetic IN VARCHAR2,
718 x_po_box_number IN VARCHAR2,
719 x_house_number IN VARCHAR2,
720 x_street_suffix IN VARCHAR2,
721 x_street IN VARCHAR2,
722 x_street_number IN VARCHAR2,
723 x_floor IN VARCHAR2,
724 x_suite IN VARCHAR2,
725 x_postal_plus4_code IN VARCHAR2,
726 x_position IN VARCHAR2,
727 x_location_directions IN VARCHAR2,
728 x_address_effective_date IN DATE,
729 x_address_expiration_date IN DATE,
730 x_clli_code IN VARCHAR2,
731 x_language IN VARCHAR2,
732 x_short_description IN VARCHAR2,
733 x_description IN VARCHAR2,
734 x_content_source_type IN VARCHAR2,
735 x_loc_hierarchy_id IN NUMBER,
736 x_sales_tax_geocode IN VARCHAR2,
737 x_sales_tax_inside_city_limits IN VARCHAR2,
738 x_fa_location_id IN NUMBER,
739 x_geometry IN mdsys.sdo_geometry,
740 x_object_version_number IN NUMBER,
741 x_timezone_id IN NUMBER,
742 x_created_by_module IN VARCHAR2,
743 x_application_id IN NUMBER,
744 x_actual_content_source IN VARCHAR2 DEFAULT NULL,
745 x_geometry_status_code IN VARCHAR2 DEFAULT NULL,
746 -- Bug 2670546
747 x_delivery_point_code IN VARCHAR2
748 ) IS
749
750 CURSOR c IS
751 SELECT *
752 FROM hz_locations
753 WHERE ROWID = x_rowid
754 FOR UPDATE NOWAIT;
755
756 recinfo c%ROWTYPE;
757 BEGIN
758 OPEN c;
759 FETCH c INTO recinfo;
760 IF (C%NOTFOUND) THEN
761 CLOSE c;
762 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
763 app_exception.raise_exception;
764 END IF;
765 CLOSE c;
766
767 IF (((recinfo.location_id = x_location_id)
768 OR ((recinfo.location_id IS NULL)
769 AND (x_location_id IS NULL)))
770 AND ((recinfo.last_update_date = x_last_update_date)
771 OR ((recinfo.last_update_date IS NULL)
772 AND (x_last_update_date IS NULL)))
773 AND ((recinfo.last_updated_by = x_last_updated_by)
774 OR ((recinfo.last_updated_by IS NULL)
775 AND (x_last_updated_by IS NULL)))
776 AND ((recinfo.creation_date = x_creation_date)
777 OR ((recinfo.creation_date IS NULL)
778 AND (x_creation_date IS NULL)))
779 AND ((recinfo.created_by = x_created_by)
780 OR ((recinfo.created_by IS NULL)
781 AND (x_created_by IS NULL)))
782 AND ((recinfo.last_update_login = x_last_update_login)
783 OR ((recinfo.last_update_login IS NULL)
784 AND (x_last_update_login IS NULL)))
785 AND ((recinfo.request_id = x_request_id)
786 OR ((recinfo.request_id IS NULL)
787 AND (x_request_id IS NULL)))
788 AND ((recinfo.program_application_id = x_program_application_id)
789 OR ((recinfo.program_application_id IS NULL)
790 AND (x_program_application_id IS NULL)))
791 AND ((recinfo.program_id = x_program_id)
792 OR ((recinfo.program_id IS NULL)
793 AND (x_program_id IS NULL)))
794 AND ((recinfo.program_update_date = x_program_update_date)
795 OR ((recinfo.program_update_date IS NULL)
796 AND (x_program_update_date IS NULL)))
797 AND ((recinfo.attribute_category = x_attribute_category)
798 OR ((recinfo.attribute_category IS NULL)
799 AND (x_attribute_category IS NULL)))
800 AND ((recinfo.attribute1 = x_attribute1)
801 OR ((recinfo.attribute1 IS NULL)
802 AND (x_attribute1 IS NULL)))
803 AND ((recinfo.attribute2 = x_attribute2)
804 OR ((recinfo.attribute2 IS NULL)
805 AND (x_attribute2 IS NULL)))
806 AND ((recinfo.attribute3 = x_attribute3)
807 OR ((recinfo.attribute3 IS NULL)
808 AND (x_attribute3 IS NULL)))
809 AND ((recinfo.attribute4 = x_attribute4)
810 OR ((recinfo.attribute4 IS NULL)
811 AND (x_attribute4 IS NULL)))
812 AND ((recinfo.attribute5 = x_attribute5)
813 OR ((recinfo.attribute5 IS NULL)
814 AND (x_attribute5 IS NULL)))
815 AND ((recinfo.attribute6 = x_attribute6)
816 OR ((recinfo.attribute6 IS NULL)
817 AND (x_attribute6 IS NULL)))
818 AND ((recinfo.attribute7 = x_attribute7)
819 OR ((recinfo.attribute7 IS NULL)
820 AND (x_attribute7 IS NULL)))
821 AND ((recinfo.attribute8 = x_attribute8)
822 OR ((recinfo.attribute8 IS NULL)
823 AND (x_attribute8 IS NULL)))
824 AND ((recinfo.attribute9 = x_attribute9)
825 OR ((recinfo.attribute9 IS NULL)
826 AND (x_attribute9 IS NULL)))
827 AND ((recinfo.attribute10 = x_attribute10)
828 OR ((recinfo.attribute10 IS NULL)
829 AND (x_attribute10 IS NULL)))
830 AND ((recinfo.attribute11 = x_attribute11)
831 OR ((recinfo.attribute11 IS NULL)
832 AND (x_attribute11 IS NULL)))
833 AND ((recinfo.attribute12 = x_attribute12)
834 OR ((recinfo.attribute12 IS NULL)
835 AND (x_attribute12 IS NULL)))
836 AND ((recinfo.attribute13 = x_attribute13)
837 OR ((recinfo.attribute13 IS NULL)
838 AND (x_attribute13 IS NULL)))
839 AND ((recinfo.attribute14 = x_attribute14)
840 OR ((recinfo.attribute14 IS NULL)
841 AND (x_attribute14 IS NULL)))
842 AND ((recinfo.attribute15 = x_attribute15)
843 OR ((recinfo.attribute15 IS NULL)
844 AND (x_attribute15 IS NULL)))
845 AND ((recinfo.attribute16 = x_attribute16)
846 OR ((recinfo.attribute16 IS NULL)
847 AND (x_attribute16 IS NULL)))
848 AND ((recinfo.attribute17 = x_attribute17)
849 OR ((recinfo.attribute17 IS NULL)
850 AND (x_attribute17 IS NULL)))
851 AND ((recinfo.attribute18 = x_attribute18)
852 OR ((recinfo.attribute18 IS NULL)
853 AND (x_attribute18 IS NULL)))
854 AND ((recinfo.attribute19 = x_attribute19)
855 OR ((recinfo.attribute19 IS NULL)
856 AND (x_attribute19 IS NULL)))
857 AND ((recinfo.attribute20 = x_attribute20)
858 OR ((recinfo.attribute20 IS NULL)
859 AND (x_attribute20 IS NULL)))
860 AND ((recinfo.orig_system_reference = x_orig_system_reference)
861 OR ((recinfo.orig_system_reference IS NULL)
862 AND (x_orig_system_reference IS NULL)))
863 AND ((recinfo.country = x_country)
864 OR ((recinfo.country IS NULL)
865 AND (x_country IS NULL)))
866 AND ((recinfo.address1 = x_address1)
867 OR ((recinfo.address1 IS NULL)
868 AND (x_address1 IS NULL)))
869 AND ((recinfo.address2 = x_address2)
870 OR ((recinfo.address2 IS NULL)
871 AND (x_address2 IS NULL)))
872 AND ((recinfo.address3 = x_address3)
873 OR ((recinfo.address3 IS NULL)
874 AND (x_address3 IS NULL)))
875 AND ((recinfo.address4 = x_address4)
876 OR ((recinfo.address4 IS NULL)
877 AND (x_address4 IS NULL)))
878 AND ((recinfo.city = x_city)
879 OR ((recinfo.city IS NULL)
880 AND (x_city IS NULL)))
881 AND ((recinfo.postal_code = x_postal_code)
882 OR ((recinfo.postal_code IS NULL)
883 AND (x_postal_code IS NULL)))
884 AND ((recinfo.state = x_state)
885 OR ((recinfo.state IS NULL)
886 AND (x_state IS NULL)))
887 AND ((recinfo.province = x_province)
888 OR ((recinfo.province IS NULL)
889 AND (x_province IS NULL)))
890 AND ((recinfo.county = x_county)
891 OR ((recinfo.county IS NULL)
892 AND (x_county IS NULL)))
893 AND ((recinfo.address_key = x_address_key)
894 OR ((recinfo.address_key IS NULL)
895 AND (x_address_key IS NULL)))
896 AND ((recinfo.address_style = x_address_style)
897 OR ((recinfo.address_style IS NULL)
898 AND (x_address_style IS NULL)))
899 AND ((recinfo.validated_flag = x_validated_flag)
900 OR ((recinfo.validated_flag IS NULL)
901 AND (x_validated_flag IS NULL)))
902 AND ((recinfo.address_lines_phonetic = x_address_lines_phonetic)
903 OR ((recinfo.address_lines_phonetic IS NULL)
904 AND (x_address_lines_phonetic IS NULL)))
905 AND ((recinfo.postal_plus4_code = x_postal_plus4_code)
906 OR ((recinfo.postal_plus4_code IS NULL)
907 AND (x_postal_plus4_code IS NULL)))
908 AND ((recinfo.position = x_position)
909 OR ((recinfo.position IS NULL)
910 AND (x_position IS NULL)))
911 AND ((recinfo.location_directions = x_location_directions)
912 OR ((recinfo.location_directions IS NULL)
913 AND (x_location_directions IS NULL)))
914 AND ((recinfo.address_effective_date = x_address_effective_date)
915 OR ((recinfo.address_effective_date IS NULL)
916 AND (x_address_effective_date IS NULL)))
917 AND ((recinfo.address_expiration_date = x_address_expiration_date)
918 OR ((recinfo.address_expiration_date IS NULL)
919 AND (x_address_expiration_date IS NULL)))
920 AND ((recinfo.clli_code = x_clli_code)
921 OR ((recinfo.clli_code IS NULL)
922 AND (x_clli_code IS NULL)))
923 AND ((recinfo.language = x_language)
924 OR ((recinfo.language IS NULL)
925 AND (x_language IS NULL)))
926 AND ((recinfo.short_description = x_short_description)
927 OR ((recinfo.short_description IS NULL)
928 AND (x_short_description IS NULL)))
929 AND ((recinfo.description = x_description)
930 OR ((recinfo.description IS NULL)
931 AND (x_description IS NULL)))
932 AND ((recinfo.content_source_type = x_content_source_type)
933 OR ((recinfo.content_source_type IS NULL)
934 AND (x_content_source_type IS NULL)))
935 AND ((recinfo.loc_hierarchy_id = x_loc_hierarchy_id)
936 OR ((recinfo.loc_hierarchy_id IS NULL)
937 AND (x_loc_hierarchy_id IS NULL)))
938 AND ((recinfo.sales_tax_geocode = x_sales_tax_geocode)
939 OR ((recinfo.sales_tax_geocode IS NULL)
940 AND (x_sales_tax_geocode IS NULL)))
941 AND ((recinfo.sales_tax_inside_city_limits
942 = x_sales_tax_inside_city_limits)
943 OR ((recinfo.sales_tax_inside_city_limits IS NULL)
944 AND (x_sales_tax_inside_city_limits IS NULL)))
945 AND ((recinfo.fa_location_id = x_fa_location_id)
946 OR ((recinfo.fa_location_id IS NULL)
947 AND (x_fa_location_id IS NULL)))
948 AND ((recinfo.object_version_number = x_object_version_number)
949 OR ((recinfo.object_version_number IS NULL)
950 AND (x_object_version_number IS NULL)))
951 AND ((recinfo.timezone_id = x_timezone_id)
952 OR ((recinfo.timezone_id IS NULL)
953 AND (x_timezone_id IS NULL)))
954 AND ((recinfo.created_by_module = x_created_by_module)
955 OR ((recinfo.created_by_module IS NULL)
956 AND (x_created_by_module IS NULL)))
957 AND ((recinfo.application_id = x_application_id)
958 OR ((recinfo.application_id IS NULL)
959 AND (x_application_id IS NULL)))
960 AND ((recinfo.actual_content_source = x_actual_content_source)
961 OR ((recinfo.actual_content_source IS NULL)
962 AND (x_actual_content_source IS NULL)))
963 AND ((recinfo.geometry_status_code = x_geometry_status_code)
964 OR ((recinfo.geometry_status_code IS NULL)
965 AND (x_geometry_status_code IS NULL)))
966 -- Bug 2670546
967 AND ((recinfo.delivery_point_code = x_delivery_point_code)
968 OR (( recinfo.delivery_point_code IS NULL)
969 AND (x_delivery_point_code IS NULL))))
970 THEN
971 RETURN;
972 ELSE
973 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
974 app_exception.raise_exception;
975 END IF;
976 END lock_row;
977
978
979 PROCEDURE select_row (
980 x_location_id IN OUT NOCOPY NUMBER,
981 x_attribute_category OUT NOCOPY VARCHAR2,
982 x_attribute1 OUT NOCOPY VARCHAR2,
983 x_attribute2 OUT NOCOPY VARCHAR2,
984 x_attribute3 OUT NOCOPY VARCHAR2,
985 x_attribute4 OUT NOCOPY VARCHAR2,
986 x_attribute5 OUT NOCOPY VARCHAR2,
987 x_attribute6 OUT NOCOPY VARCHAR2,
988 x_attribute7 OUT NOCOPY VARCHAR2,
989 x_attribute8 OUT NOCOPY VARCHAR2,
990 x_attribute9 OUT NOCOPY VARCHAR2,
991 x_attribute10 OUT NOCOPY VARCHAR2,
992 x_attribute11 OUT NOCOPY VARCHAR2,
993 x_attribute12 OUT NOCOPY VARCHAR2,
994 x_attribute13 OUT NOCOPY VARCHAR2,
995 x_attribute14 OUT NOCOPY VARCHAR2,
996 x_attribute15 OUT NOCOPY VARCHAR2,
997 x_attribute16 OUT NOCOPY VARCHAR2,
998 x_attribute17 OUT NOCOPY VARCHAR2,
999 x_attribute18 OUT NOCOPY VARCHAR2,
1000 x_attribute19 OUT NOCOPY VARCHAR2,
1001 x_attribute20 OUT NOCOPY VARCHAR2,
1002 x_orig_system_reference OUT NOCOPY VARCHAR2,
1003 x_country OUT NOCOPY VARCHAR2,
1004 x_address1 OUT NOCOPY VARCHAR2,
1005 x_address2 OUT NOCOPY VARCHAR2,
1006 x_address3 OUT NOCOPY VARCHAR2,
1007 x_address4 OUT NOCOPY VARCHAR2,
1008 x_city OUT NOCOPY VARCHAR2,
1009 x_postal_code OUT NOCOPY VARCHAR2,
1010 x_state OUT NOCOPY VARCHAR2,
1011 x_province OUT NOCOPY VARCHAR2,
1012 x_county OUT NOCOPY VARCHAR2,
1013 x_address_key OUT NOCOPY VARCHAR2,
1014 x_address_style OUT NOCOPY VARCHAR2,
1015 x_validated_flag OUT NOCOPY VARCHAR2,
1016 x_address_lines_phonetic OUT NOCOPY VARCHAR2,
1017 x_po_box_number OUT NOCOPY VARCHAR2,
1018 x_house_number OUT NOCOPY VARCHAR2,
1019 x_street_suffix OUT NOCOPY VARCHAR2,
1020 x_street OUT NOCOPY VARCHAR2,
1021 x_street_number OUT NOCOPY VARCHAR2,
1022 x_floor OUT NOCOPY VARCHAR2,
1023 x_suite OUT NOCOPY VARCHAR2,
1024 x_postal_plus4_code OUT NOCOPY VARCHAR2,
1025 x_position OUT NOCOPY VARCHAR2,
1026 x_location_directions OUT NOCOPY VARCHAR2,
1027 x_address_effective_date OUT NOCOPY DATE,
1028 x_address_expiration_date OUT NOCOPY DATE,
1029 x_clli_code OUT NOCOPY VARCHAR2,
1030 x_language OUT NOCOPY VARCHAR2,
1031 x_short_description OUT NOCOPY VARCHAR2,
1032 x_description OUT NOCOPY VARCHAR2,
1033 x_content_source_type OUT NOCOPY VARCHAR2,
1034 x_loc_hierarchy_id OUT NOCOPY NUMBER,
1035 x_sales_tax_geocode OUT NOCOPY VARCHAR2,
1036 x_sales_tax_inside_city_limits OUT NOCOPY VARCHAR2,
1037 x_fa_location_id OUT NOCOPY NUMBER,
1038 x_geometry OUT NOCOPY mdsys.sdo_geometry,
1039 x_timezone_id OUT NOCOPY NUMBER,
1040 x_created_by_module OUT NOCOPY VARCHAR2,
1041 x_application_id OUT NOCOPY NUMBER,
1042 x_actual_content_source OUT NOCOPY VARCHAR2,
1043 x_geometry_status_code OUT NOCOPY VARCHAR2,
1044 -- Bug 2670546
1045 x_delivery_point_code OUT NOCOPY VARCHAR2
1046 ) IS
1047 BEGIN
1048 SELECT NVL(location_id, fnd_api.g_miss_num),
1049 NVL(attribute_category, fnd_api.g_miss_char),
1050 NVL(attribute1, fnd_api.g_miss_char),
1051 NVL(attribute2, fnd_api.g_miss_char),
1052 NVL(attribute3, fnd_api.g_miss_char),
1053 NVL(attribute4, fnd_api.g_miss_char),
1054 NVL(attribute5, fnd_api.g_miss_char),
1055 NVL(attribute6, fnd_api.g_miss_char),
1056 NVL(attribute7, fnd_api.g_miss_char),
1057 NVL(attribute8, fnd_api.g_miss_char),
1058 NVL(attribute9, fnd_api.g_miss_char),
1059 NVL(attribute10, fnd_api.g_miss_char),
1060 NVL(attribute11, fnd_api.g_miss_char),
1061 NVL(attribute12, fnd_api.g_miss_char),
1062 NVL(attribute13, fnd_api.g_miss_char),
1063 NVL(attribute14, fnd_api.g_miss_char),
1064 NVL(attribute15, fnd_api.g_miss_char),
1065 NVL(attribute16, fnd_api.g_miss_char),
1066 NVL(attribute17, fnd_api.g_miss_char),
1067 NVL(attribute18, fnd_api.g_miss_char),
1068 NVL(attribute19, fnd_api.g_miss_char),
1069 NVL(attribute20, fnd_api.g_miss_char),
1070 NVL(orig_system_reference, fnd_api.g_miss_char),
1071 NVL(country, fnd_api.g_miss_char),
1072 NVL(address1, fnd_api.g_miss_char),
1073 NVL(address2, fnd_api.g_miss_char),
1074 NVL(address3, fnd_api.g_miss_char),
1075 NVL(address4, fnd_api.g_miss_char),
1076 NVL(city, fnd_api.g_miss_char),
1077 NVL(postal_code, fnd_api.g_miss_char),
1078 NVL(state, fnd_api.g_miss_char),
1079 NVL(province, fnd_api.g_miss_char),
1080 NVL(county, fnd_api.g_miss_char),
1081 NVL(address_key, fnd_api.g_miss_char),
1082 NVL(address_style, fnd_api.g_miss_char),
1083 NVL(validated_flag, fnd_api.g_miss_char),
1084 NVL(address_lines_phonetic, fnd_api.g_miss_char),
1085 NVL(po_box_number, fnd_api.g_miss_char),
1086 NVL(house_number, fnd_api.g_miss_char),
1087 NVL(street_suffix, fnd_api.g_miss_char),
1088 NVL(street, fnd_api.g_miss_char),
1089 NVL(street_number, fnd_api.g_miss_char),
1090 NVL(floor, fnd_api.g_miss_char),
1091 NVL(suite, fnd_api.g_miss_char),
1092 NVL(postal_plus4_code, fnd_api.g_miss_char),
1093 NVL(position, fnd_api.g_miss_char),
1094 NVL(location_directions, fnd_api.g_miss_char),
1095 NVL(address_effective_date, fnd_api.g_miss_date),
1096 NVL(address_expiration_date, fnd_api.g_miss_date),
1097 NVL(clli_code, fnd_api.g_miss_char),
1098 NVL(language, fnd_api.g_miss_char),
1099 NVL(short_description, fnd_api.g_miss_char),
1100 NVL(description, fnd_api.g_miss_char),
1101 NVL(content_source_type, fnd_api.g_miss_char),
1102 NVL(loc_hierarchy_id, fnd_api.g_miss_num),
1103 sales_tax_geocode,
1104 NVL(sales_tax_inside_city_limits, fnd_api.g_miss_char),
1105 NVL(fa_location_id, fnd_api.g_miss_num),
1106 geometry,
1107 NVL(timezone_id, fnd_api.g_miss_num),
1108 NVL(created_by_module, fnd_api.g_miss_char),
1109 NVL(application_id, fnd_api.g_miss_num),
1110 NVL(actual_content_source, fnd_api.g_miss_char),
1111 NVL(geometry_status_code, fnd_api.g_miss_char),
1112 -- Bug 2670546
1113 NVL(delivery_point_code,fnd_api.g_miss_char)
1114 INTO x_location_id,
1115 x_attribute_category,
1116 x_attribute1,
1117 x_attribute2,
1118 x_attribute3,
1119 x_attribute4,
1120 x_attribute5,
1121 x_attribute6,
1122 x_attribute7,
1123 x_attribute8,
1124 x_attribute9,
1125 x_attribute10,
1126 x_attribute11,
1127 x_attribute12,
1128 x_attribute13,
1129 x_attribute14,
1130 x_attribute15,
1131 x_attribute16,
1132 x_attribute17,
1133 x_attribute18,
1134 x_attribute19,
1135 x_attribute20,
1136 x_orig_system_reference,
1137 x_country,
1138 x_address1,
1139 x_address2,
1140 x_address3,
1141 x_address4,
1142 x_city,
1143 x_postal_code,
1144 x_state,
1145 x_province,
1146 x_county,
1147 x_address_key,
1148 x_address_style,
1149 x_validated_flag,
1150 x_address_lines_phonetic,
1151 x_po_box_number,
1152 x_house_number,
1153 x_street_suffix,
1154 x_street,
1155 x_street_number,
1156 x_floor,
1157 x_suite,
1158 x_postal_plus4_code,
1159 x_position,
1160 x_location_directions,
1161 x_address_effective_date,
1162 x_address_expiration_date,
1163 x_clli_code,
1164 x_language,
1165 x_short_description,
1166 x_description,
1167 x_content_source_type,
1168 x_loc_hierarchy_id,
1169 x_sales_tax_geocode,
1170 x_sales_tax_inside_city_limits,
1171 x_fa_location_id,
1172 x_geometry,
1173 x_timezone_id,
1174 x_created_by_module,
1175 x_application_id,
1176 x_actual_content_source,
1177 x_geometry_status_code,
1178 -- Bug 2670546
1179 x_delivery_point_code
1180 FROM hz_locations
1181 WHERE location_id = x_location_id;
1182 EXCEPTION
1183 WHEN NO_DATA_FOUND THEN
1184 fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
1185 fnd_message.set_token('RECORD', 'location_rec');
1186 fnd_message.set_token('VALUE', TO_CHAR(x_location_id));
1187 fnd_msg_pub.add;
1188 RAISE fnd_api.g_exc_error;
1189 END select_row;
1190
1191 PROCEDURE delete_row (x_location_id IN NUMBER) IS
1192 BEGIN
1193 DELETE FROM hz_locations
1194 WHERE location_id = x_location_id;
1195
1196 IF (SQL%NOTFOUND) THEN
1197 RAISE NO_DATA_FOUND;
1198 END IF;
1199 END delete_row;
1200 END hz_locations_pkg;