1 PACKAGE HZ_GNR_UTIL_PKG AUTHID CURRENT_USER AS
2 /*$Header: ARHGNRUS.pls 120.12 2011/05/05 07:38:46 rgokavar ship $ */
3
4 --------------------------------------
5 -- declaration of record type
6 --------------------------------------
7
8 TYPE loc_components_rec_type IS RECORD(
9 ADDRESS_STYLE VARCHAR2(30),
10 COUNTRY VARCHAR2(60),
11 VALIDATE_COUNTRY_AGAINST VARCHAR2(1),
12 CITY VARCHAR2(60),
13 POSTAL_CODE VARCHAR2(60),
14 STATE VARCHAR2(60),
15 VALIDATE_STATE_AGAINST VARCHAR2(1),
16 PROVINCE VARCHAR2(60),
17 VALIDATE_PROVINCE_AGAINST VARCHAR2(1),
18 COUNTY VARCHAR2(60),
19 POSTAL_PLUS4_CODE VARCHAR2(10),
20 ATTRIBUTE1 VARCHAR2(150),
21 ATTRIBUTE2 VARCHAR2(150),
22 ATTRIBUTE3 VARCHAR2(150),
23 ATTRIBUTE4 VARCHAR2(150),
24 ATTRIBUTE5 VARCHAR2(150),
25 ATTRIBUTE6 VARCHAR2(150),
26 ATTRIBUTE7 VARCHAR2(150),
27 ATTRIBUTE8 VARCHAR2(150),
28 ATTRIBUTE9 VARCHAR2(150),
29 ATTRIBUTE10 VARCHAR2(150)
30 );
31
32 TYPE map_rec_type IS RECORD(
33 MAP_ID NUMBER(15),
34 COUNTRY_CODE VARCHAR2(2),
35 LOC_TBL_NAME VARCHAR2(30),
36 ADDRESS_STYLE VARCHAR2(30)
37 );
38
39 TYPE usage_rec_type IS RECORD(
40 USAGE_ID NUMBER(15),
41 MAP_ID NUMBER(15),
42 USAGE_CODE VARCHAR2(30)
43 );
44
45 TYPE usage_tbl_type IS TABLE OF usage_rec_type
46 INDEX BY BINARY_INTEGER;
47
48 TYPE usage_dtls_rec_type IS RECORD(
49 USAGE_ID NUMBER(15),
50 GEOGRAPHY_TYPE VARCHAR2(30)
51 );
52
53 TYPE usage_dtls_tbl_type IS TABLE OF usage_dtls_rec_type
54 INDEX BY BINARY_INTEGER;
55
56
57 TYPE maploc_rec_type IS RECORD(
58 LOC_SEQ_NUM NUMBER,
59 LOC_COMPONENT VARCHAR2(30),
60 GEOGRAPHY_TYPE VARCHAR2(30),
61 GEO_ELEMENT_COL VARCHAR2(30),
62 LOC_COMPVAL VARCHAR2(150),
63 GEOGRAPHY_ID NUMBER,
64 GEOGRAPHY_CODE VARCHAR2(30)
65 );
66
67 TYPE maploc_rec_tbl_type IS TABLE OF maploc_rec_type
68 INDEX BY BINARY_INTEGER;
69
70 TYPE v_tbl_type IS TABLE OF varchar2(150)
71 INDEX BY BINARY_INTEGER;
72
73 -- Added by Nishant on 16-Feb-2006 for creating pre and post location update
74 -- processing procedures
75 TYPE loc_other_param_rec_type IS RECORD(
76 called_from VARCHAR2(30)
77 );
78
79 --------------------------------------
80 -- procedures and functions
81 --------------------------------------
82 PROCEDURE getLocCompValues(
83 P_loc_table IN VARCHAR2,
84 p_loc_components_rec IN loc_components_rec_type,
85 x_map_dtls_tbl IN OUT NOCOPY maploc_rec_tbl_type,
86 x_status OUT NOCOPY VARCHAR2
87 );
88 --------------------------------------
89 --------------------------------------
90 FUNCTION getLocCompCount(
91 p_map_dtls_tbl IN maploc_rec_tbl_type) RETURN NUMBER;
92 --------------------------------------
93 --------------------------------------
94 PROCEDURE fill_values(
95 x_map_dtls_tbl IN OUT NOCOPY maploc_rec_tbl_type
96 );
97 --------------------------------------
98 --------------------------------------
99 FUNCTION fix_multiparent(
100 p_geography_id IN NUMBER,
101 x_map_dtls_tbl IN OUT NOCOPY maploc_rec_tbl_type
102 ) RETURN BOOLEAN;
103 --------------------------------------
104 --------------------------------------
105 FUNCTION fix_child(
106 x_map_dtls_tbl IN OUT NOCOPY maploc_rec_tbl_type
107 ) RETURN BOOLEAN;
108 --------------------------------------
109 --------------------------------------
110 FUNCTION getQuery(
111 p_map_dtls_tbl IN maploc_rec_tbl_type,
112 p_mdu_tbl IN maploc_rec_tbl_type,
113 x_status OUT NOCOPY VARCHAR2
114 ) RETURN VARCHAR2;
115 --------------------------------------
116 --------------------------------------
117 -- Below function is for creating the query when there is a cause MULTIPLE_MATCH
118 -- In this case the query is same as the query created by getQuery function except that
119 -- this also add the check to verify the identifier_type is NAME
120 -- Fix for bug #
121 FUNCTION getQueryforMultiMatch(
122 p_map_dtls_tbl IN maploc_rec_tbl_type,
123 p_mdu_tbl IN maploc_rec_tbl_type,
124 x_status OUT NOCOPY VARCHAR2
125 ) RETURN VARCHAR2;
126 --------------------------------------
127 --------------------------------------
128 FUNCTION check_GNR_For_Usage(
129 p_location_id IN NUMBER,
130 p_location_table_name IN VARCHAR2,
131 p_usage_code IN VARCHAR2,
132 p_mdu_tbl IN maploc_rec_tbl_type,
133 x_status OUT NOCOPY varchar2
134 ) RETURN BOOLEAN;
135 --------------------------------------
136 --------------------------------------
137 PROCEDURE create_gnr (
138 p_location_id IN number,
139 p_location_table_name IN varchar2,
140 p_usage_code IN varchar2,
141 p_map_status IN varchar2,
142 p_loc_components_rec IN loc_components_rec_type,
143 p_lock_flag IN varchar2,
144 p_map_dtls_tbl IN maploc_rec_tbl_type,
145 x_status OUT NOCOPY varchar2
146 );
147 --------------------------------------
148 --------------------------------------
149 FUNCTION get_usage_val_status(
150 p_map_dtls_tbl IN maploc_rec_tbl_type,
151 p_mdu_tbl IN maploc_rec_tbl_type
152 ) RETURN VARCHAR2;
153 --------------------------------------
154 --------------------------------------
155 PROCEDURE fix_no_match(
156 x_map_dtls_tbl IN OUT NOCOPY maploc_rec_tbl_type,
157 x_status OUT NOCOPY VARCHAR2
158 );
159 --------------------------------------
160 --------------------------------------
161 PROCEDURE getMinValStatus(
162 p_mdu_tbl IN maploc_rec_tbl_type,
163 x_status IN OUT NOCOPY VARCHAR2
164 );
165 --------------------------------------
166 --------------------------------------
167 FUNCTION do_usage_val(
168 p_cause IN VARCHAR2,
169 p_map_dtls_tbl IN maploc_rec_tbl_type,
170 p_mdu_tbl IN maploc_rec_tbl_type,
171 x_mdtl_derived_tbl IN OUT NOCOPY maploc_rec_tbl_type,
172 x_status OUT NOCOPY varchar2
173 ) RETURN BOOLEAN;
174 --------------------------------------
175 --------------------------------------
176 FUNCTION getAddrValStatus(
177 p_map_dtls_tbl IN maploc_rec_tbl_type,
178 p_mdu_tbl IN maploc_rec_tbl_type,
179 p_called_from IN VARCHAR2,
180 p_addr_val_level IN VARCHAR2,
181 x_addr_warn_msg OUT NOCOPY VARCHAR2,
182 x_map_status IN VARCHAR2,
183 x_status IN OUT NOCOPY VARCHAR2
184 ) RETURN VARCHAR2;
185 --------------------------------------
186 --------------------------------------
187
188 /**
189 * PROCEDURE getMapRec
190 *
191 * DESCRIPTION
192 * This private procedure is used to gets
193 * 1. the map record for a given location.
194 * 2. populates component values from loc rec
195 *
196 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
197 *
198 * ARGUMENTS
199 * IN:
200 *
201 * p_locId Location Identifier
202 * p_locTbl Location Table
203 *
204 * OUT:
205 *
206 * x_mapTbl Table of records that has
207 * geo element, type and loc components and their values
208 * x_status Y in case of success, otherwise error message name
209 * x_mapId map identifier
210 * x_cntry country code
211 *
212 * NOTES
213 *
214 *
215 * MODIFICATION HISTORY
216 *
217 *
218 */
219 PROCEDURE getMapRec(
220 p_locId IN NUMBER,
221 p_locTbl IN VARCHAR2,
222 x_mlTbl OUT NOCOPY maploc_rec_tbl_type,
223 x_mapId OUT NOCOPY NUMBER,
224 x_cntry OUT NOCOPY varchar2,
225 x_status OUT NOCOPY VARCHAR2
226 );
227 -----------------------------------------
228 /**
229 * PROCEDURE getLocRec
230 *
231 * DESCRIPTION
232 * This private procedure is used to get
233 * the location record.
234 *
235 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
236 *
237 * ARGUMENTS
238 * IN:
239 *
240 * p_locId Location Identifier
241 * p_locTbl Location Table
242 *
243 * IN OUT:
244 * x_mapTbl Table of records that has location sequence number,
245 * geo element, type and loc components and their values
246 *
247 * OUT:
248 * x_status procedure status
249 *
250 * EXCEPTIONS RAISED
251 *
252 *
253 * NOTES
254 * By the time thi sprocedure was called but for loc_comval all
255 * other elements of x_mapTbl were already populated.
256 *
257 * MODIFICATION HISTORY
258 *
259 *
260 */
261 PROCEDURE getLocRec (
262 p_locId IN NUMBER,
263 p_locTbl IN VARCHAR2,
264 x_mlTbl IN OUT NOCOPY maploc_rec_tbl_type,
265 x_status OUT NOCOPY VARCHAR2
266 );
267 -----------------------------------------------------------
268 /**
269 * PROCEDURE getCntryStyle
270 *
271 * DESCRIPTION
272 * This private procedure is used to get the country code address style for a location.
273 *
274 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
275 *
276 * ARGUMENTS
277 * IN:
278 *
279 * p_locId Location Identifier
280 * p_locTbl Location Table
281 *
282 * OUT:
283 *
284 * x_cntry Country Code
285 * x_addrStyle Address Style
286 *
287 * EXCEPTIONS RAISED
288 *
289 * HZ_NO_LOC_TBL
290 * HZ_GEO_INVALID_COUNTRY
291 * HZ_GEO_NO_LOC_REC
292 *
293 * NOTES
294 *
295 *
296 * MODIFICATION HISTORY
297 *
298 *
299 */
300
301 PROCEDURE getCntryStyle (
302 p_locId IN NUMBER,
303 p_locTbl IN VARCHAR2,
304 x_cntry OUT NOCOPY VARCHAR2,
305 x_addrStyle OUT NOCOPY VARCHAR2,
306 x_status OUT NOCOPY VARCHAR2
307 );
308 -----------------------------------------------------------
309 /**
310 * PROCEDURE gnrIns
311 *
312 * DESCRIPTION
313 * This private procedure is used to insert or update the
314 * GNR table.
315 * This procedure will update if the same location id and
316 * geography id combination is existing otherwise this will insert.
317 *
318 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
319 *
320 * ARGUMENTS
321 * IN:
322 *
323 * p_locId Location Identifier
324 * p_locTbl Location Table
325 *
326 * p_mapTbl Table of records that has location sequence number,
327 * geo element, type and loc components and their values
328 *
329 * OUT:
330 *
331 * x_status procedure status
332 *
333 *
334 * EXCEPTIONS RAISED
335 *
336 *
337 * NOTES
338 *
339 *
340 * MODIFICATION HISTORY
341 *
342 *
343 */
344
345 PROCEDURE gnrIns (
346 p_locId IN NUMBER,
347 p_locTbl IN VARCHAR2,
348 p_mapTbl IN maploc_rec_tbl_type,
349 x_status OUT NOCOPY VARCHAR2
350 );
351 -----------------------------------------------------------
352 /**
353 * PROCEDURE gnrl
354 *
355 * DESCRIPTION
356 * This private procedure is used to insert or update the
357 * GNR Log table. This log table will be updated irrespective
358 * of whether the GNRing of a location record is sucessfull or not.
359 *
360 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
361 *
362 * ARGUMENTS
363 * IN:
364 * p_locId Location Identifier
365 * p_locTbl Location Table
366 * p_mapStatus sucess, error or warning
367 * p_mesg encoded mesg
368 *
369 *
370 * EXCEPTIONS RAISED
371 *
372 *
373 * NOTES
374 *
375 *
376 * MODIFICATION HISTORY
377 *
378 *
379 */
380
381 PROCEDURE gnrl (
382 p_locId IN NUMBER,
383 p_locTbl IN VARCHAR2,
384 p_mapStatus IN VARCHAR2,
385 p_mesg IN VARCHAR2
386 );
387 ----------------------------------------------
388 /**
389 * Procedure to write a message to the out file
390 **/
391 ----------------------------------------------
392 PROCEDURE out(
393 message IN VARCHAR2,
394 newline IN BOOLEAN DEFAULT TRUE);
395 ----------------------------------------------
396 /**
397 * Procedure to write a message to the log file
398 **/
399 ----------------------------------------------
400 PROCEDURE log(
401 message IN VARCHAR2,
402 newline IN BOOLEAN DEFAULT TRUE);
403 ----------------------------------------------
404 /**
405 * Procedure to write a message to the out and log files
406 **/
407 ----------------------------------------------
408 PROCEDURE outandlog(
409 message IN VARCHAR2,
410 newline IN BOOLEAN DEFAULT TRUE);
411 ----------------------------------------------
412 /**
413 * Function to fetch messages of the stack and log the error
414 * Also returns the error
415 **/
416 ----------------------------------------------
417 FUNCTION logerror RETURN VARCHAR2;
418 ----------------------------------------------
419 /**
420 * procedure to fetch messages of the stack and log the error
421 **/
422 ----------------------------------------------
423 PROCEDURE logerr;
424 ----------------------------------------------
425 /*
426 this procedure takes a message_name and enters into the message stack
427 and writes into the log file also.
428 */
429 PROCEDURE mesglog(
430 p_locId IN NUMBER,
431 p_locTbl IN VARCHAR2,
432 p_message IN VARCHAR2,
433 p_tkn1_name IN VARCHAR2,
434 p_tkn1_val IN VARCHAR2,
435 p_tkn2_name IN VARCHAR2,
436 p_tkn2_val IN VARCHAR2
437 );
438 ----------------------------------------------
439 /**
440 * PROCEDURE getMapId
441 *
442 * DESCRIPTION
443 * This private procedure is used to gets the
444 * map identifier for a given location id, loc table name
445 *
446 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
447 *
448 * ARGUMENTS
449 * IN:
450 *
451 * p_locId Location Identifier
452 * p_locTbl Location Table
453 *
454 * OUT:
455 *
456 * x_status Y in case of success, otherwise error message name
457 *
458 * NOTES
459 *
460 *
461 * MODIFICATION HISTORY
462 *
463 *
464 */
465 PROCEDURE getMapId(
466 p_locId IN NUMBER,
467 p_locTbl IN VARCHAR2,
468 x_cntry OUT NOCOPY VARCHAR2,
469 x_mapId OUT NOCOPY NUMBER,
470 x_status OUT NOCOPY VARCHAR2
471 );
472 -----------------------------------------
473 /**
474 Function : gnr_exists
475
476 DESCRIPTION :
477 Function to tell if the GNR already processed for a given
478 location record or not.
479
480 ARGUMENTS :
481 IN p_location_id NUMBER
482 IN p_location_table_name VARCHAR2
483
484 RETURNS : BOOLEAN
485 TRUE : If GNR exists
486 FALSE : If GNR does not exists
487
488 MODIFICATION HISTORY:
489 17-FEB-2006 Baiju nair Created
490
491 **/
492 FUNCTION gnr_exists(p_location_id IN NUMBER,
493 p_location_table_name IN VARCHAR2) RETURN BOOLEAN;
494
495 -----------------------------------------
496
497 /**
498 Function : location_updation_allowed
499
500 DESCRIPTION :
501 Function to tell if the location can be updated or not. It directly calls
502 ARH_ADDR_PKG.check_tran_for_all_accts to do this validation. This function is
503 just a wrapper for ease of use in GNR code
504
505 EXTERNAL PROCEDURES/FUNCTIONS ACCESSED :
506 ARH_ADDR_PKG
507
508 ARGUMENTS :
509 IN p_location_id NUMBER
510
511 RETURNS : BOOLEAN
512 TRUE : Location updation is allowed
513 FALSE : Location updation is not allowed
514
515 MODIFICATION HISTORY:
516 16-FEB-2006 Nishant Singhai Created
517
518 **/
519
520 FUNCTION location_updation_allowed(p_location_id IN NUMBER) RETURN BOOLEAN;
521
522 /**
523 Procedure : pre_location_update
524
525 DESCRIPTION :
526 Procedure to do pre-update processing for a given location record. This will
527 be used in GNR program, where it updates the location components.
528
529 EXTERNAL PROCEDURES/FUNCTIONS ACCESSED :
530 HZ_LOCATION_V2PUB
531 hz_fuzzy_pub
532 hz_timezone_pub
533
534 ARGUMENTS :
535 IN p_old_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE
536
537 IN OUT p_new_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE
538 IN OUT p_other_location_params HZ_GNR_UTIL_PKG.location_other_param_rec_type
539 (extendible - for future use)
540
541 MODIFICATION HISTORY:
542 16-FEB-2006 Nishant Singhai Created
543 **/
544
545 PROCEDURE pre_location_update (
546 p_old_location_rec IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
547 p_new_location_rec IN OUT NOCOPY HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
548 p_other_location_params IN OUT NOCOPY HZ_GNR_UTIL_PKG.loc_other_param_rec_type,
549 x_return_status OUT NOCOPY VARCHAR2,
550 x_msg_count OUT NOCOPY NUMBER,
551 x_msg_data OUT NOCOPY VARCHAR2
552 );
553
554 /**
555 Procedure : post_location_update
556
557 DESCRIPTION :
558 Procedure to do post-update processing for a given location record. This will
559 be used in GNR program, where it updates the location components.
560
561 EXTERNAL PROCEDURES/FUNCTIONS ACCESSED :
562 HZ_LOCATION_V2PUB
563 HZ_UTILITY_V2PUB
564 HZ_DQM_SYNC
565 HZ_BUSINESS_EVENT_V2PVT
566 HZ_POPULATE_BOT_PKG
567
568 ARGUMENTS :
569 IN p_old_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE
570
571 IN OUT p_new_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE
572 IN OUT p_other_location_params HZ_GNR_UTIL_PKG.location_other_param_rec_type
573 (extendible - for future use)
574
575 MODIFICATION HISTORY:
576 16-FEB-2006 Nishant Singhai Created
577 **/
578
579 PROCEDURE post_location_update (
580 p_old_location_rec IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
581 p_new_location_rec IN OUT NOCOPY HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
582 p_other_location_params IN OUT NOCOPY HZ_GNR_UTIL_PKG.loc_other_param_rec_type,
583 x_return_status OUT NOCOPY VARCHAR2,
584 x_msg_count OUT NOCOPY NUMBER,
585 x_msg_data OUT NOCOPY VARCHAR2
586 );
587
588 --ER#7240974
589 /**
590 Function : postal_code_to_validate
591
592 DESCRIPTION :
593 Based on profile(HZ_VAL_FIRST_5_DIGIT_US_ZIP) value,
594 it will return the postal code that needs to be validated.
595
596 ARGUMENTS :
597 IN p_country_code VARCHAR2
598 IN p_postal_code VARCHAR2
599
600 RETURNS : VARCHAR2
601 postal code that needs to be validated
602
603
604 MODIFICATION HISTORY:
605 17-DEC-2008 Sudhir Gokavarapu Created
606
607 **/
608
609 FUNCTION postal_code_to_validate(
610 p_country_code IN VARCHAR2,
611 p_postal_code IN VARCHAR2
612 ) RETURN VARCHAR2;
613
614
615 /**
616 * PROCEDURE conc_gen_pkg
617 *
618 * DESCRIPTION
619 * Concurrent program for generating
620 * gnr packages for all the existing Map Id's
621 *
622 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
623 *
624 * IN:
625 * OUT:
626 * errbuf Buffer for error message.
627 * retcode Return code.
628 *
629 * MODIFICATION HISTORY
630 *
631 * 02-MAY-2011 Sudhir Gokavarapu o Created.
632 */
633
634 PROCEDURE conc_gen_pkg (
635 errbuf OUT NOCOPY VARCHAR2,
636 retcode OUT NOCOPY VARCHAR2
637 );
638
639
640 END HZ_GNR_UTIL_PKG;
[Home] [Help]