1 PACKAGE HZ_IMP_LOAD_ADDRESSES_PKG AUTHID CURRENT_USER AS
2 /*$Header: ARHLADDS.pls 120.1 2005/10/30 04:45:40 appldev noship $*/
3
4
5 TYPE insert_cursor_type IS REF CURSOR;
6 TYPE update_cursor_type IS REF CURSOR;
7 TYPE pri_flag_cursor_type IS REF CURSOR;
8 TYPE de_norm_cursor_type IS REF CURSOR;
9
10 TYPE APPLICATION_ID IS TABLE OF HZ_LOCATIONS.APPLICATION_ID%TYPE;
11 TYPE OWNER_TABLE_ID IS TABLE OF HZ_ORIG_SYS_REFERENCES.OWNER_TABLE_ID%TYPE;
12 TYPE SITE_ORIG_SYSTEM IS TABLE OF HZ_IMP_ADDRESSES_INT.SITE_ORIG_SYSTEM%TYPE;
13 TYPE SITE_ORIG_SYSTEM_REFERENCE IS TABLE OF HZ_IMP_ADDRESSES_INT.SITE_ORIG_SYSTEM_REFERENCE%TYPE;
14 TYPE SITE_ID IS TABLE OF HZ_IMP_ADDRESSES_SG.PARTY_SITE_ID%TYPE;
15 TYPE PARTY_ID IS TABLE OF HZ_IMP_ADDRESSES_SG.PARTY_ID%TYPE;
16 TYPE SITE_NAME IS TABLE OF HZ_IMP_ADDRESSES_INT.PARTY_SITE_NAME%TYPE;
17 TYPE PARTY_SITE_NUMBER IS TABLE OF HZ_IMP_ADDRESSES_INT.PARTY_SITE_NUMBER%TYPE;
18
19 TYPE ATTRIBUTE_CATEGORY IS TABLE OF HZ_IMP_ADDRESSES_INT.ATTRIBUTE_CATEGORY%TYPE;
20 TYPE ATTRIBUTE IS TABLE OF HZ_IMP_ADDRESSES_INT.ATTRIBUTE1%TYPE;
21 TYPE COUNTRY IS TABLE OF HZ_IMP_ADDRESSES_INT.COUNTRY%TYPE;
22 TYPE ADDRESS IS TABLE OF HZ_IMP_ADDRESSES_INT.ADDRESS1%TYPE;
23 TYPE CITY IS TABLE OF HZ_IMP_ADDRESSES_INT.CITY%TYPE;
24 TYPE POSTAL_CODE IS TABLE OF HZ_IMP_ADDRESSES_INT.POSTAL_CODE%TYPE;
25 TYPE STATE IS TABLE OF HZ_IMP_ADDRESSES_INT.STATE%TYPE;
26 TYPE PROVINCE IS TABLE OF HZ_IMP_ADDRESSES_INT.PROVINCE%TYPE;
27 TYPE COUNTY IS TABLE OF HZ_IMP_ADDRESSES_INT.COUNTY%TYPE;
28 TYPE ADDRESS_LINES_PHONETIC IS TABLE OF HZ_IMP_ADDRESSES_INT.ADDRESS_LINES_PHONETIC%TYPE;
29 TYPE LAST_UPDATED_BY IS TABLE OF HZ_IMP_ADDRESSES_INT.LAST_UPDATED_BY%TYPE;
30
31 TYPE POSTAL_PLUS4_CODE IS TABLE OF HZ_IMP_ADDRESSES_INT.POSTAL_PLUS4_CODE%TYPE;
32 TYPE TIMEZONE IS TABLE OF fnd_timezones_b.UPGRADE_TZ_ID%TYPE;
33 TYPE TIMEZONE_CODE IS TABLE OF HZ_IMP_ADDRESSES_INT.TIMEZONE_CODE%TYPE;
34 TYPE LOCATION_DIRECTIONS IS TABLE OF HZ_IMP_ADDRESSES_INT.LOCATION_DIRECTIONS%TYPE;
35 TYPE CLLI_CODE IS TABLE OF HZ_IMP_ADDRESSES_INT.CLLI_CODE%TYPE;
36 TYPE LANGUAGE IS TABLE OF HZ_IMP_ADDRESSES_INT.LANGUAGE%TYPE;
37 TYPE SHORT_DESCRIPTION IS TABLE OF HZ_IMP_ADDRESSES_INT.SHORT_DESCRIPTION%TYPE;
38 TYPE DESCRIPTION IS TABLE OF HZ_IMP_ADDRESSES_INT.DESCRIPTION%TYPE;
39 TYPE SALES_TAX_GEOCODE IS TABLE OF HZ_IMP_ADDRESSES_INT.SALES_TAX_GEOCODE%TYPE;
40 TYPE SALES_TAX_LIMITS IS TABLE OF HZ_IMP_ADDRESSES_INT.SALES_TAX_INSIDE_CITY_LIMITS%TYPE;
41 TYPE CREATED_BY_MODULE IS TABLE OF HZ_IMP_ADDRESSES_INT.CREATED_BY_MODULE%TYPE;
42 TYPE DELIVERY_POINT_CODE IS TABLE OF HZ_IMP_ADDRESSES_INT.DELIVERY_POINT_CODE%TYPE;
43 TYPE ACCEPT_STANDARDIZED_FLAG IS TABLE OF HZ_IMP_ADDRESSES_INT.ACCEPT_STANDARDIZED_FLAG%TYPE;
44 TYPE ADAPTER_CONTENT_SRC IS TABLE OF HZ_IMP_ADDRESSES_INT.Adapter_content_source%TYPE;
45 TYPE CORRECT_MOVE_INDICATOR IS TABLE OF HZ_IMP_ADDRESSES_INT.CORRECT_MOVE_INDICATOR%TYPE;
46 TYPE VALID_STATUS_CODE IS TABLE OF HZ_IMP_ADDRESSES_INT.ADDR_VALID_STATUS_CODE%TYPE;
47 TYPE DATE_VALIDATED IS TABLE OF HZ_IMP_ADDRESSES_INT.DATE_VALIDATED%TYPE;
48 TYPE IDENT_ADDR_FLAG IS TABLE OF HZ_PARTY_SITES.IDENTIFYING_ADDRESS_FLAG%TYPE;
49 TYPE VALIDATION_STATUS_CODE IS TABLE OF HZ_LOCATIONS.VALIDATION_STATUS_CODE%TYPE;
50 TYPE PROFILE_SST_FLAG IS TABLE OF HZ_LOCATION_PROFILES.VALIDATION_SST_FLAG%TYPE;
51
52
53 TYPE ACTION_FLAG IS TABLE OF HZ_IMP_ADDRESSES_SG.ACTION_FLAG%TYPE;
54 TYPE LOCATION_ID IS TABLE OF HZ_LOCATIONS.LOCATION_ID%TYPE;
55 TYPE LOCATION_PROFILE_ID IS TABLE OF HZ_LOCATION_PROFILES.LOCATION_PROFILE_ID%TYPE;
56 TYPE FLAG_ERROR IS TABLE OF VARCHAR2(1);
57 TYPE LOOKUP_ERROR IS TABLE OF ar_lookups.lookup_code%TYPE;
58 TYPE ERROR_ID IS TABLE OF HZ_IMP_ADDRESSES_INT.ERROR_ID%TYPE;
59 TYPE NUMBER_COLUMN IS TABLE OF NUMBER;
60 TYPE ERROR_MESSAGE_NAME IS TABLE OF HZ_IMP_ERRORS.MESSAGE_NAME%TYPE;
61 TYPE ERROR_MESSAGE_TOKEN IS TABLE OF HZ_IMP_ERRORS.TOKEN1_VALUE%TYPE;
62 TYPE ERROR_MESSAGE_TOKEN_NAME IS TABLE OF HZ_IMP_ERRORS.TOKEN1_NAME%TYPE;
63 TYPE ROWID IS TABLE OF VARCHAR2(50); --UROWID;
64
65 PROCEDURE load_addresses (
66 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
67 P_UPDATE_STR_ADDR IN VARCHAR2,
68 P_MAINTAIN_LOC_HIST IN VARCHAR2,
69 P_ALLOW_ADDR_CORR IN VARCHAR2,
70 x_return_status OUT NOCOPY VARCHAR2,
71 x_msg_count OUT NOCOPY NUMBER,
72 x_msg_data OUT NOCOPY VARCHAR2
73 );
74
75 FUNCTION validate_desc_flexfield_f(
76 p_attr_category IN VARCHAR2,
77 p_attr1 IN VARCHAR2,
78 p_attr2 IN VARCHAR2,
79 p_attr3 IN VARCHAR2,
80 p_attr4 IN VARCHAR2,
81 p_attr5 IN VARCHAR2,
82 p_attr6 IN VARCHAR2,
83 p_attr7 IN VARCHAR2,
84 p_attr8 IN VARCHAR2,
85 p_attr9 IN VARCHAR2,
86 p_attr10 IN VARCHAR2,
87 p_attr11 IN VARCHAR2,
88 p_attr12 IN VARCHAR2,
89 p_attr13 IN VARCHAR2,
90 p_attr14 IN VARCHAR2,
91 p_attr15 IN VARCHAR2,
92 p_attr16 IN VARCHAR2,
93 p_attr17 IN VARCHAR2,
94 p_attr18 IN VARCHAR2,
95 p_attr19 IN VARCHAR2,
96 p_attr20 IN VARCHAR2,
97 p_validation_date IN DATE
98 ,p_gmiss_char IN VARCHAR2
99 ) RETURN VARCHAR2;
100
101
102 END HZ_IMP_LOAD_ADDRESSES_PKG;
[Home] [Help]