1 PACKAGE HZ_LOCATION_V2PUB AUTHID CURRENT_USER AS
2 /*$Header: ARH2LOSS.pls 120.10 2006/08/17 10:14:08 idali noship $ */
3 /*#
4 * This package contains the public APIs for locations.
5 * @rep:scope public
6 * @rep:product HZ
7 * @rep:displayname Location
8 * @rep:category BUSINESS_ENTITY HZ_ADDRESS
9 * @rep:lifecycle active
10 * @rep:doccd 120hztig.pdf Location APIs, Oracle Trading Community Architecture Technical Implementation Guide
11 */
12
13 --------------------------------------
14 -- declaration of record type
15 --------------------------------------
16
17 hz_geometry_default CONSTANT mdsys.sdo_geometry :=
18 mdsys.sdo_geometry(fnd_api.g_miss_num,
19 fnd_api.g_miss_num, NULL, NULL, NULL);
20 geometry_status_code_default CONSTANT VARCHAR2(30) := 'DIRTY';
21
22 -- Bug 2197181:: Added the constant for Mix-N-Match project to reduce the dependencies.
23
24 g_miss_content_source_type CONSTANT VARCHAR2(30) := 'USER_ENTERED';
25
26 TYPE location_rec_type IS RECORD(
27 location_id NUMBER,
28 orig_system_reference VARCHAR2(240),
29 orig_system VARCHAR2(30),
30 country VARCHAR2(60),
31 address1 VARCHAR2(240),
32 address2 VARCHAR2(240),
33 address3 VARCHAR2(240),
34 address4 VARCHAR2(240),
35 city VARCHAR2(60),
36 postal_code VARCHAR2(60),
37 state VARCHAR2(60),
38 province VARCHAR2(60),
39 county VARCHAR2(60),
40 address_key VARCHAR2(500),
41 address_style VARCHAR2(30),
42 validated_flag VARCHAR2(1),
43 address_lines_phonetic VARCHAR2(560),
44 po_box_number VARCHAR2(50),
45 house_number VARCHAR2(50),
46 street_suffix VARCHAR2(50),
47 street VARCHAR2(50),
48 street_number VARCHAR2(50),
49 floor VARCHAR2(50),
50 suite VARCHAR2(50),
51 postal_plus4_code VARCHAR2(10),
52 position VARCHAR2(50),
53 location_directions VARCHAR2(640),
54 address_effective_date DATE,
55 address_expiration_date DATE,
56 clli_code VARCHAR2(60),
57 language VARCHAR2(4) ,
58 short_description VARCHAR2(240),
59 description VARCHAR2(2000),
60 geometry mdsys.sdo_geometry := hz_geometry_default,
61 geometry_status_code VARCHAR2(30) := geometry_status_code_default,
62 loc_hierarchy_id NUMBER,
63 sales_tax_geocode VARCHAR2(30),
64 sales_tax_inside_city_limits VARCHAR2(30),
65 fa_location_id NUMBER,
66 content_source_type VARCHAR2(30) := g_miss_content_source_type,
67 attribute_category VARCHAR2(30) ,
68 attribute1 VARCHAR2(150),
69 attribute2 VARCHAR2(150),
70 attribute3 VARCHAR2(150),
71 attribute4 VARCHAR2(150),
72 attribute5 VARCHAR2(150),
73 attribute6 VARCHAR2(150),
74 attribute7 VARCHAR2(150),
75 attribute8 VARCHAR2(150),
76 attribute9 VARCHAR2(150),
77 attribute10 VARCHAR2(150),
78 attribute11 VARCHAR2(150),
79 attribute12 VARCHAR2(150),
80 attribute13 VARCHAR2(150),
81 attribute14 VARCHAR2(150),
82 attribute15 VARCHAR2(150),
83 attribute16 VARCHAR2(150),
84 attribute17 VARCHAR2(150),
85 attribute18 VARCHAR2(150),
86 attribute19 VARCHAR2(150),
87 attribute20 VARCHAR2(150),
88 timezone_id NUMBER,
89 created_by_module VARCHAR2(150),
90 application_id NUMBER,
91 actual_content_source VARCHAR2(30),
92 -- Bug 2670546
93 delivery_point_code VARCHAR2(50)
94 );
95
96 --------------------------------------
97 -- declaration of public procedures and functions
98 --------------------------------------
99
100 /**
101 * PROCEDURE create_location
102 *
103 * DESCRIPTION
104 * Creates location.
105 *
106 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
107 *
108 * ARGUMENTS
109 * IN:
110 * p_init_msg_list Initialize message stack if it is set to
111 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
112 * p_location_rec Location record.
113 * IN/OUT:
114 * OUT:
115 * x_location_id Location ID.
116 * x_return_status Return status after the call. The status can
117 * be FND_API.G_RET_STS_SUCCESS (success),
118 * FND_API.G_RET_STS_ERROR (error),
119 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
120 * x_msg_count Number of messages in message stack.
121 * x_msg_data Message text if x_msg_count is 1.
122 *
123 * NOTES
124 *
125 * MODIFICATION HISTORY
126 *
127 * 07-23-2001 Indrajit Sen o Created.
128 *
129 */
130
131 /*#
132 * Use this routine to create an address location. This API creates a record
133 * in the HZ_LOCATIONS table. The API also creates a record in the
134 * HZ_LOCATIONS_PROFILES table that stores address-specific information about the location.
135 * The location created by this API is a physical location that you can use to create a
136 * party site or customer account site. If orig_system is passed in, then the API also
137 * creates a record in the HZ_ORIG_SYS_REFERENCES table to store the mapping between the
138 * source system reference and the TCA primary key. If timezone_id is not passed in, then
139 * the API generates a time zone value, based on the address components and time zone
140 * setup. However, if the user passes in the time zone, then the API keeps the time
141 * zone value that the user chose.
142 * @rep:scope public
143 * @rep:lifecycle active
144 * @rep:displayname Create Location
145 * @rep:businessevent oracle.apps.ar.hz.Location.create
146 * @rep:doccd 120hztig.pdf Location APIs, Oracle Trading Community Architecture Technical Implementation Guide
147 */
148 PROCEDURE create_location (
149 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
150 p_location_rec IN LOCATION_REC_TYPE,
151 x_location_id OUT NOCOPY NUMBER,
152 x_return_status OUT NOCOPY VARCHAR2,
153 x_msg_count OUT NOCOPY NUMBER,
154 x_msg_data OUT NOCOPY VARCHAR2
155 );
156
157 /**
158 * PROCEDURE create_location
159 *
160 * DESCRIPTION
161 * Creates location(overloaded procedure with address validation).
162 *
163 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
164 *
165 * ARGUMENTS
166 * IN:
167 * p_init_msg_list Initialize message stack if it is set to
168 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
169 * p_location_rec Location record.
170 * p_do_addr_val Do address validation if 'Y'
171 * IN/OUT:
172 * OUT:
173 * x_location_id Location ID.
174 * x_addr_val_status Address validation status based on address validation level.
175 * x_addr_warn_msg Warning message if x_addr_val_status is 'W'
176 * x_return_status Return status after the call. The status can
177 * be FND_API.G_RET_STS_SUCCESS (success),
178 * FND_API.G_RET_STS_ERROR (error),
179 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
180 * x_msg_count Number of messages in message stack.
181 * x_msg_data Message text if x_msg_count is 1.
182 *
183 * NOTES
184 *
185 * MODIFICATION HISTORY
186 *
187 * 10-04-2005 Baiju Nair o Created.
188 *
189 */
190
191 /*#
192 * This is an overloaded procedure for create location API.
193 * The only difference is that this will call Address validation API to save GNR results if p_do_addr_val is 'Y'.
194 * If the Address validation is success it will call the old create location API.
195 * OUT parameter, x_addr_val_status will return the address validation status based on address validation level.
196 * If x_addr_val_status is 'W', x_addr_warn_msg will have the warning message.
197 * Use this routine to create an address location. This API creates a record
198 * in the HZ_LOCATIONS table. The API also creates a record in the
199 * HZ_LOCATIONS_PROFILES table that stores address-specific information about the location.
200 * The location created by this API is a physical location that you can use to create a
201 * party site or customer account site. If orig_system is passed in, then the API also
202 * creates a record in the HZ_ORIG_SYS_REFERENCES table to store the mapping between the
203 * source system reference and the TCA primary key. If timezone_id is not passed in, then
204 * the API generates a time zone value, based on the address components and time zone
205 * setup. However, if the user passes in the time zone, then the API keeps the time
206 * zone value that the user chose.
207 * @rep:scope public
208 * @rep:lifecycle active
209 * @rep:displayname Create Location
210 * @rep:businessevent oracle.apps.ar.hz.Location.create
211 */
212 PROCEDURE create_location (
213 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
214 p_location_rec IN LOCATION_REC_TYPE,
215 p_do_addr_val IN VARCHAR2,
216 x_location_id OUT NOCOPY NUMBER,
217 x_addr_val_status OUT NOCOPY VARCHAR2,
218 x_addr_warn_msg OUT NOCOPY VARCHAR2,
219 x_return_status OUT NOCOPY VARCHAR2,
220 x_msg_count OUT NOCOPY NUMBER,
221 x_msg_data OUT NOCOPY VARCHAR2
222 );
223
224 /**
225 * PROCEDURE update_location
226 *
227 * DESCRIPTION
228 * Updates location.
229 *
230 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
231 *
232 * ARGUMENTS
233 * IN:
234 * p_init_msg_list Initialize message stack if it is set to
235 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
236 * p_location_rec Location record.
237 * IN/OUT:
238 * p_object_version_number Used for locking the being updated record.
239 * OUT:
240 * x_return_status Return status after the call. The status can
241 * be FND_API.G_RET_STS_SUCCESS (success),
242 * FND_API.G_RET_STS_ERROR (error),
243 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
244 * x_msg_count Number of messages in message stack.
245 * x_msg_data Message text if x_msg_count is 1.
246 *
247 * NOTES
248 *
249 * MODIFICATION HISTORY
250 *
251 * 07-23-2001 Indrajit Sen o Created.
252 *
253 */
254
255 /*#
256 * Use this routine to update an address location. The API updates a record
257 * in the HZ_LOCATIONS table. The API also creates or updates a record in the
258 * HZ_LOCATIONS_PROFILES table. Whether to create or update a location profile
259 * record depends on the value of the HZ:Maintain Location History and HZ: Allow
260 * to Update Standardized Address profile options. If the primary key is not
261 * passed in, then get the primary key from the HZ_ORIG_SYS_REFERENCES table, based on
262 * orig_system and orig_system_reference that must be unique and not null. If timezone_id
263 * is not passed in, then the API generates a time zone value, based on the changes of the
264 * address components and the time zone setup, even if a time zone already exists in the
265 * database. However, if the user passes in the time zone, then the API keeps the time zone
266 * value that the user chose.
267 * @rep:scope public
268 * @rep:lifecycle active
269 * @rep:displayname Update Location
270 * @rep:businessevent oracle.apps.ar.hz.Location.update
271 * @rep:doccd 120hztig.pdf Location APIs, Oracle Trading Community Architecture Technical Implementation Guide
272 */
273 PROCEDURE update_location (
274 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
275 p_location_rec IN LOCATION_REC_TYPE,
276 p_object_version_number IN OUT NOCOPY NUMBER,
277 x_return_status OUT NOCOPY VARCHAR2,
278 x_msg_count OUT NOCOPY NUMBER,
279 x_msg_data OUT NOCOPY VARCHAR2
280 );
281
282 /**
283 * PROCEDURE update_location
284 *
285 * DESCRIPTION
286 * Updates location(overloaded procedure with address validation).
287 *
288 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
289 *
290 * ARGUMENTS
291 * IN:
292 * p_init_msg_list Initialize message stack if it is set to
293 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
294 * p_location_rec Location record.
295 * p_do_addr_val Do address validation if 'Y'
296 * IN/OUT:
297 * p_object_version_number Used for locking the being updated record.
298 * OUT:
299 * x_addr_val_status Address validation status based on address validation level.
300 * x_addr_warn_msg Warning message if x_addr_val_status is 'W'
301 * x_return_status Return status after the call. The status can
302 * be FND_API.G_RET_STS_SUCCESS (success),
303 * FND_API.G_RET_STS_ERROR (error),
304 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
305 * x_msg_count Number of messages in message stack.
306 * x_msg_data Message text if x_msg_count is 1.
307 *
308 * NOTES
309 *
310 * MODIFICATION HISTORY
311 *
312 * 10-04-2005 Baiju Nair o Created.
313 *
314 */
315
316 /*#
317 * This is an overloaded procedure for update location API.
318 * The only difference is that this will call Address validation API to save GNR results if p_do_addr_val is 'Y'.
319 * If the Address validation is success it will call the old update location API.
320 * OUT parameter, x_addr_val_status will return the address validation status based on address validation level.
321 * If x_addr_val_status is 'W', x_addr_warn_msg will have the warning message.
322 * Use this routine to update an address location. The API updates a record
323 * in the HZ_LOCATIONS table. The API also creates or updates a record in the
324 * HZ_LOCATIONS_PROFILES table. Whether to create or update a location profile
325 * record depends on the value of the HZ:Maintain Location History and HZ: Allow
326 * to Update Standardized Address profile options. If the primary key is not
327 * passed in, then get the primary key from the HZ_ORIG_SYS_REFERENCES table, based on
328 * orig_system and orig_system_reference that must be unique and not null. If timezone_id
329 * is not passed in, then the API generates a time zone value, based on the changes of the
330 * address components and the time zone setup, even if a time zone already exists in the
331 * database. However, if the user passes in the time zone, then the API keeps the time zone
332 * value that the user chose.
333 * @rep:scope public
334 * @rep:lifecycle active
335 * @rep:displayname Update Location
336 * @rep:businessevent oracle.apps.ar.hz.Location.update
337 */
338
339 PROCEDURE update_location (
340 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
344 x_addr_val_status OUT NOCOPY VARCHAR2,
341 p_location_rec IN LOCATION_REC_TYPE,
342 p_do_addr_val IN VARCHAR2,
343 p_object_version_number IN OUT NOCOPY NUMBER,
345 x_addr_warn_msg OUT NOCOPY VARCHAR2,
346 x_return_status OUT NOCOPY VARCHAR2,
347 x_msg_count OUT NOCOPY NUMBER,
348 x_msg_data OUT NOCOPY VARCHAR2
349 );
350
351 /**
352 * PROCEDURE get_location_rec
353 *
354 * DESCRIPTION
355 * Gets location record.
356 *
357 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
358 *
359 * ARGUMENTS
360 * IN:
361 * p_init_msg_list Initialize message stack if it is set to
362 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
363 * p_location_id Location ID.
364 * IN/OUT:
365 * OUT:
366 * x_location_rec Location record.
367 * x_return_status Return status after the call. The status can
368 * be FND_API.G_RET_STS_SUCCESS (success),
369 * FND_API.G_RET_STS_ERROR (error),
370 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
371 * x_msg_count Number of messages in message stack.
372 * x_msg_data Message text if x_msg_count is 1.
373 *
374 * NOTES
375 *
376 * MODIFICATION HISTORY
377 *
378 * 07-23-2001 Indrajit Sen o Created.
379 *
380 */
381
382 PROCEDURE get_location_rec(
383 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
384 p_location_id IN NUMBER,
385 x_location_rec OUT NOCOPY LOCATION_REC_TYPE,
386 x_return_status OUT NOCOPY VARCHAR2,
387 x_msg_count OUT NOCOPY NUMBER,
388 x_msg_data OUT NOCOPY VARCHAR2
389 );
390
391 /**
392 * PROCEDURE fill_geometry_for_locations
393 *
394 * DESCRIPTION
395 * Concurrent program to fill geometry column in hz_locations.
396 *
397 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
398 *
399 * ARGUMENTS
400 * OUT:
401 * p_errbuf Error buffer.
402 * p_retcode Return code.
403 *
404 * NOTES
405 *
406 * MODIFICATION HISTORY
407 *
408 * 07-23-2001 Indrajit Sen o Created.
409 *
410 */
411
412 PROCEDURE fill_geometry_for_locations(
413 p_errbuf OUT NOCOPY VARCHAR2,
414 p_retcode OUT NOCOPY NUMBER
415 );
416
417 END HZ_LOCATION_V2PUB;
[Home] [Help]