[Home] [Help]
PACKAGE: APPS.HZ_GEOGRAPHY_PUB
Source
1 PACKAGE HZ_GEOGRAPHY_PUB AUTHID CURRENT_USER AS
2 /*$Header: ARHGEOSS.pls 120.3 2006/02/17 09:09:20 idali noship $ */
3
4 --------------------------------------
5 -- declaration of record type
6 --------------------------------------
7
8 TYPE master_relation_rec_type IS RECORD(
9 geography_id NUMBER,
10 parent_geography_id NUMBER,
11 start_date DATE DEFAULT SYSDATE,
12 end_date DATE DEFAULT to_date('31-12-4712','DD-MM-YYYY'),
13 created_by_module VARCHAR2(150),
14 application_id NUMBER
15 );
16
17 TYPE geo_identifier_rec_type IS RECORD(
18 geography_id NUMBER,
19 identifier_subtype VARCHAR2(30),
20 identifier_value VARCHAR2(360),
21 identifier_type VARCHAR2(30),
22 geo_data_provider VARCHAR2(30) DEFAULT 'USER_ENTERED',
23 primary_flag VARCHAR2(1) DEFAULT 'N',
24 language_code VARCHAR2(4) DEFAULT userenv('LANG'),
25 created_by_module VARCHAR2(150),
26 application_id NUMBER,
27 new_identifier_value VARCHAR2(360),
28 new_identifier_subtype VARCHAR2(30)
29 );
30
31 TYPE parent_geography_tbl_type IS TABLE OF NUMBER
32 INDEX BY BINARY_INTEGER;
33
34
35 TYPE master_geography_rec_type IS RECORD(
36 geography_type VARCHAR2(30),
37 geography_name VARCHAR2(360),
38 geography_code VARCHAR2(30),
39 geography_code_type VARCHAR2(30),
40 start_date DATE DEFAULT SYSDATE,
41 end_date DATE DEFAULT to_date('31-12-4712','DD-MM-YYYY'),
42 geo_data_provider VARCHAR2(30) DEFAULT 'USER_ENTERED',
43 language_code VARCHAR2(4) DEFAULT userenv('LANG'),
44 parent_geography_id PARENT_GEOGRAPHY_TBL_TYPE,
45 geometry MDSYS.SDO_GEOMETRY,
46 timezone_code VARCHAR2(50),
47 created_by_module VARCHAR2(150),
48 application_id NUMBER
49 );
50
51 TYPE geography_range_rec_type IS RECORD(
52 zone_id NUMBER,
53 master_ref_geography_id NUMBER,
54 identifier_type VARCHAR2(30),
55 geography_from VARCHAR2(360),
56 geography_to VARCHAR2(360),
57 geography_type VARCHAR2(30),
58 start_date DATE DEFAULT SYSDATE,
59 end_date DATE DEFAULT to_date('31-12-4712','DD-MM-YYYY'),
60 created_by_module VARCHAR2(150),
61 application_id NUMBER
62 );
63
64 TYPE zone_relation_rec_type IS RECORD (
65 included_geography_id NUMBER,
66 geography_from VARCHAR2(360),
67 geography_to VARCHAR2(360),
68 identifier_type VARCHAR2(30),
69 geography_type VARCHAR2(30),
70 start_date DATE DEFAULT SYSDATE,
71 end_date DATE DEFAULT to_date('31-12-4712','DD-MM-YYYY')
72 );
73
74 TYPE zone_relation_tbl_type IS TABLE OF zone_relation_rec_type
75 INDEX BY BINARY_INTEGER;
76
77
78 -------------------------------------------------
79 -- declaration of public procedures and functions
80 -------------------------------------------------
81
82 /**
83 * PROCEDURE create_master_relation
84 *
85 * DESCRIPTION
86 * Creates Geography Relationships.
87 *
88 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
89 *
90 * ARGUMENTS
91 * IN:
92 * p_init_msg_list Initialize message stack if it is set to
93 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
94 * p_master_relation_rec Geography type record.
95 * IN/OUT:
96 * OUT:
97 * x_relationship_id Returns relationship_id for the relationship created.
98 * x_return_status Return status after the call. The status can
99 * be FND_API.G_RET_STS_SUCCESS (success),
100 * FND_API.G_RET_STS_ERROR (error),
101 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
102 * x_msg_count Number of messages in message stack.
103 * x_msg_data Message text if x_msg_count is 1.
104 *
105 * NOTES
106 *
107 * MODIFICATION HISTORY
108 * 11-21-2002 Rekha Nalluri o Created.
109 *
110 */
111
112 PROCEDURE create_master_relation (
113 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
114 p_master_relation_rec IN MASTER_RELATION_REC_TYPE,
115 x_relationship_id OUT NOCOPY NUMBER,
116 x_return_status OUT NOCOPY VARCHAR2,
117 x_msg_count OUT NOCOPY NUMBER,
118 x_msg_data OUT NOCOPY VARCHAR2
119 );
120
121
122 /**
123 * PROCEDURE update_relationship
124 *
125 * DESCRIPTION
126 * Updates Geography Relationships.
127 *
128 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
129 *
130 * ARGUMENTS
131 * IN:
132 * p_init_msg_list Initialize message stack if it is set to
133 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
134 * p_master_relation_rec Geography type record.
135 * p_object_version_number Object version number of the row
136 * IN/OUT:
137 * OUT:
138 *
139 * x_return_status Return status after the call. The status can
140 * be FND_API.G_RET_STS_SUCCESS (success),
141 * FND_API.G_RET_STS_ERROR (error),
142 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
143 * x_msg_count Number of messages in message stack.
144 * x_msg_data Message text if x_msg_count is 1.
145 *
146 * NOTES
147 *
148 * MODIFICATION HISTORY
149 * 11-22-2002 Rekha Nalluri o Created.
150 *
151 */
152
153 PROCEDURE update_relationship (
154 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
155 p_relationship_id IN NUMBER,
156 p_status IN VARCHAR2,
157 p_object_version_number IN OUT NOCOPY NUMBER,
158 x_return_status OUT NOCOPY VARCHAR2,
159 x_msg_count OUT NOCOPY NUMBER,
160 x_msg_data OUT NOCOPY VARCHAR2
161 );
162
163 /**
164 * PROCEDURE create_geo_identifier
165 *
166 * DESCRIPTION
167 * Creates Geography Identifiers.
168 *
169 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
170 *
171 * ARGUMENTS
172 * IN:
173 * p_init_msg_list Initialize message stack if it is set to
174 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
175 * p_geo_identifier_rec Geo_identifier type record.
176 * IN/OUT:
177 * OUT:
178 *
179 * x_return_status Return status after the call. The status can
180 * be FND_API.G_RET_STS_SUCCESS (success),
181 * FND_API.G_RET_STS_ERROR (error),
182 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
183 * x_msg_count Number of messages in message stack.
184 * x_msg_data Message text if x_msg_count is 1.
185 *
186 * NOTES
187 *
188 * MODIFICATION HISTORY
189 * 12-03-2002 Rekha Nalluri o Created.
190 *
191 */
192
193 PROCEDURE create_geo_identifier(
194 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
195 p_geo_identifier_rec IN GEO_IDENTIFIER_REC_TYPE,
196 x_return_status OUT NOCOPY VARCHAR2,
197 x_msg_count OUT NOCOPY NUMBER,
198 x_msg_data OUT NOCOPY VARCHAR2
199 );
200
201 /**
202 * PROCEDURE update_geo_identifier
203 *
204 * DESCRIPTION
205 * Creates Geography Identifiers.
206 *
207 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
208 *
209 * ARGUMENTS
210 * IN:
211 * p_init_msg_list Initialize message stack if it is set to
212 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
213 * p_geo_identifier_rec Geo_identifier type record.
214 *
215 * IN/OUT:
216 * p_object_version_number
217 * OUT:
218 *
219 * x_cp_request_id Concurrent Program Request Id, whenever CP
220 * to update denormalized data gets kicked off.
221 * x_return_status Return status after the call. The status can
222 * be FND_API.G_RET_STS_SUCCESS (success),
223 * FND_API.G_RET_STS_ERROR (error),
224 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
225 * x_msg_count Number of messages in message stack.
226 * x_msg_data Message text if x_msg_count is 1.
227 *
228 * NOTES
229 *
230 * MODIFICATION HISTORY
231 * 12-03-2002 Rekha Nalluri o Created.
232 * 21-Oct-2005 Nishant Added x_cp_request_id OUT parameter
233 * for Bug 457886
234 *
235 */
236 PROCEDURE update_geo_identifier (
237 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
238 p_geo_identifier_rec IN GEO_IDENTIFIER_REC_TYPE,
239 p_object_version_number IN OUT NOCOPY NUMBER,
240 x_cp_request_id OUT NOCOPY NUMBER,
241 x_return_status OUT NOCOPY VARCHAR2,
242 x_msg_count OUT NOCOPY NUMBER,
243 x_msg_data OUT NOCOPY VARCHAR2
244 );
245
246 /**
247 * PROCEDURE delete_geo_identifier
248 *
249 * DESCRIPTION
250 * Deletes Geography Identifiers.
251 *
252 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
253 *
254 * ARGUMENTS
255 * IN:
256 * p_init_msg_list Initialize message stack if it is set to
257 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
258 * p_geography_id geography id
259 * p_identifier_type
260 * p_identifier_subtype
261 * p_identifier_value
262 *
263 * OUT:
264 *
265 * x_return_status Return status after the call. The status can
266 * be FND_API.G_RET_STS_SUCCESS (success),
267 * FND_API.G_RET_STS_ERROR (error),
268 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
269 * x_msg_count Number of messages in message stack.
270 * x_msg_data Message text if x_msg_count is 1.
271 *
272 * NOTES
273 *
274 * MODIFICATION HISTORY
275 * 01-02-2003 Rekha Nalluri o Created.
276 *
277 */
278
279 PROCEDURE delete_geo_identifier(
280 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
281 p_geography_id IN NUMBER,
282 p_identifier_type IN VARCHAR2,
283 p_identifier_subtype IN VARCHAR2,
284 p_identifier_value IN VARCHAR2,
285 p_language_code IN VARCHAR2,
286 x_return_status OUT NOCOPY VARCHAR2,
287 x_msg_count OUT NOCOPY NUMBER,
288 x_msg_data OUT NOCOPY VARCHAR2
289 );
290
291
292 /**
293 * PROCEDURE create_master_geography
294 *
295 * DESCRIPTION
296 * Creates Master Geography.
297 *
298 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
299 *
300 * ARGUMENTS
301 * IN:
302 * p_init_msg_list Initialize message stack if it is set to
303 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
304 * p_master_geography_rec Master Geography type record.
305 * IN/OUT:
306 * OUT:
307 *
308 * x_geography_id Return ID of the geography being created.
309 * x_return_status Return status after the call. The status can
310 * be FND_API.G_RET_STS_SUCCESS (success),
311 * FND_API.G_RET_STS_ERROR (error),
312 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
313 * x_msg_count Number of messages in message stack.
314 * x_msg_data Message text if x_msg_count is 1.
315 *
316 * NOTES
317 *
318 * MODIFICATION HISTORY
319 * 12-03-2002 Rekha Nalluri o Created.
320 *
321 */
322
323 PROCEDURE create_master_geography(
324 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
325 p_master_geography_rec IN MASTER_GEOGRAPHY_REC_TYPE,
326 x_geography_id OUT NOCOPY NUMBER,
327 x_return_status OUT NOCOPY VARCHAR2,
328 x_msg_count OUT NOCOPY NUMBER,
329 x_msg_data OUT NOCOPY VARCHAR2
330 );
331
332 /**
333 * PROCEDURE update_geography
334 *
335 * DESCRIPTION
336 * Updates Geography
337 *
338 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
339 *
340 * ARGUMENTS
341 * IN:
342 * p_init_msg_list Initialize message stack if it is set to
343 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
344 * p_master_geography_rec Master Geography type record.
345 *
346 * IN/OUT:
347 * p_object_version_number
348 * OUT:
349 *
350 * x_return_status Return status after the call. The status can
351 * be FND_API.G_RET_STS_SUCCESS (success),
352 * FND_API.G_RET_STS_ERROR (error),
353 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
354 * x_msg_count Number of messages in message stack.
355 * x_msg_data Message text if x_msg_count is 1.
356 *
357 * NOTES
358 *
359 * MODIFICATION HISTORY
360 * 12-12-2002 Rekha Nalluri o Created.
361 *
362 */
363 PROCEDURE update_geography (
364 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
365 p_geography_id IN NUMBER,
366 p_end_date IN DATE,
367 p_geometry IN MDSYS.SDO_GEOMETRY,
368 p_timezone_code IN VARCHAR2,
369 p_object_version_number IN OUT NOCOPY NUMBER,
370 x_return_status OUT NOCOPY VARCHAR2,
371 x_msg_count OUT NOCOPY NUMBER,
372 x_msg_data OUT NOCOPY VARCHAR2
373 );
374
375 /**
376 * PROCEDURE create_geography_range
377 *
378 * DESCRIPTION
379 * Creates Geography Range.
380 *
381 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
382 *
383 * ARGUMENTS
384 * IN:
385 * p_init_msg_list Initialize message stack if it is set to
386 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
387 * p_geography_range_rec Geography range type record.
388 * IN/OUT:
389 * OUT:
390 *
391 * x_return_status Return status after the call. The status can
392 * be FND_API.G_RET_STS_SUCCESS (success),
393 * FND_API.G_RET_STS_ERROR (error),
394 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
395 * x_msg_count Number of messages in message stack.
396 * x_msg_data Message text if x_msg_count is 1.
397 *
398 * NOTES
399 *
400 * MODIFICATION HISTORY
401 * 01-20-2003 Rekha Nalluri o Created.
402 *
403 */
404
405 PROCEDURE create_geography_range(
406 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
407 p_geography_range_rec IN GEOGRAPHY_RANGE_REC_TYPE,
408 x_return_status OUT NOCOPY VARCHAR2,
409 x_msg_count OUT NOCOPY NUMBER,
410 x_msg_data OUT NOCOPY VARCHAR2
411 );
412
413
414 /**
415 * PROCEDURE update_geography_range
416 *
417 * DESCRIPTION
418 * Updates Geography range
419 *
420 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
421 *
422 * ARGUMENTS
423 * IN:
424 * p_init_msg_list Initialize message stack if it is set to
425 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
426 * geography_id
427 * geography_from
428 * start_date
429 * end_date
430 *
431 * IN/OUT:
432 * p_object_version_number
433 * OUT:
434 *
435 * x_return_status Return status after the call. The status can
439 * x_msg_count Number of messages in message stack.
436 * be FND_API.G_RET_STS_SUCCESS (success),
437 * FND_API.G_RET_STS_ERROR (error),
438 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
440 * x_msg_data Message text if x_msg_count is 1.
441 *
442 * NOTES
443 *
444 * MODIFICATION HISTORY
445 * 01-23-2003 Rekha Nalluri o Created.
446 *
447 */
448 PROCEDURE update_geography_range (
449 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
450 p_geography_id IN NUMBER,
451 p_geography_from IN VARCHAR2,
452 p_start_date IN DATE,
453 p_end_date IN DATE,
454 p_object_version_number IN OUT NOCOPY NUMBER,
455 x_return_status OUT NOCOPY VARCHAR2,
456 x_msg_count OUT NOCOPY NUMBER,
457 x_msg_data OUT NOCOPY VARCHAR2
458 );
459
460
461 /**
462 * PROCEDURE create_zone_relation
463 *
464 * DESCRIPTION
465 * Creates Zone Relation.
466 *
467 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
468 *
469 * ARGUMENTS
470 * IN:
471 * p_init_msg_list Initialize message stack if it is set to
472 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
473 * p_geography_id
474 * p_zone_relation_tbl Zone relation table of records.
475 * IN/OUT:
476 * OUT:
477 *
478 * x_return_status Return status after the call. The status can
479 * be FND_API.G_RET_STS_SUCCESS (success),
480 * FND_API.G_RET_STS_ERROR (error),
481 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
482 * x_msg_count Number of messages in message stack.
483 * x_msg_data Message text if x_msg_count is 1.
484 *
485 * NOTES
486 *
487 * MODIFICATION HISTORY
488 * 01-23-2003 Rekha Nalluri o Created.
489 *
490 */
491
492 PROCEDURE create_zone_relation(
493 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
494 p_geography_id IN NUMBER,
495 p_zone_relation_tbl IN ZONE_RELATION_TBL_TYPE,
496 p_created_by_module IN VARCHAR2,
497 p_application_id IN NUMBER,
498 x_return_status OUT NOCOPY VARCHAR2,
499 x_msg_count OUT NOCOPY NUMBER,
500 x_msg_data OUT NOCOPY VARCHAR2
501 );
502
503
504 /**
505 * PROCEDURE create_zone
506 *
507 * DESCRIPTION
508 * Creates Zone
509 *
510 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
511 *
512 * ARGUMENTS
513 * IN:
514 * p_init_msg_list Initialize message stack if it is set to
515 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
516 * p_zone_type
517 * p_zone_name
518 * p_zone_code
519 * p_start_date
520 * p_end_date
521 * p_geo_data_provider
522 * p_zone_relation_tbl table of records to create relationships
523 * p_geometry
524 * p_timezone_code
525 * p_created_by_module
526 * p_application_id
527 * p_program_login_id
528 *
529 * OUT:
530 * x_return_status
531 * Return status after the call. The status can
532 * be FND_API.G_RET_STS_SUCCESS (success),
533 * FND_API.G_RET_STS_ERROR (error),
534 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
535 * x_msg_count Number of messages in message stack.
536 * x_msg_data Message text if x_msg_count is 1.
537 *
538 * NOTES
539 *
540 * MODIFICATION HISTORY
541 * 01-24-2003 Rekha Nalluri o Created.
542 *
543 */
544
545 PROCEDURE create_zone(
546 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
547 p_zone_type IN VARCHAR2,
548 p_zone_name IN VARCHAR2,
549 p_zone_code IN VARCHAR2,
550 p_zone_code_type IN VARCHAR2,
551 p_start_date IN DATE ,
552 p_end_date IN DATE ,
553 p_geo_data_provider IN VARCHAR2 ,
554 p_language_code IN VARCHAR2,
555 p_zone_relation_tbl IN ZONE_RELATION_TBL_TYPE,
556 p_geometry IN MDSYS.SDO_GEOMETRY,
557 p_timezone_code IN VARCHAR2,
558 x_geography_id OUT NOCOPY NUMBER,
559 p_created_by_module IN VARCHAR2,
560 p_application_id IN NUMBER,
561 x_return_status OUT NOCOPY VARCHAR2,
562 x_msg_count OUT NOCOPY NUMBER,
563 x_msg_data OUT NOCOPY VARCHAR2
564 );
565
566
567 END HZ_GEOGRAPHY_PUB;