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