[Home] [Help]
1 PACKAGE HZ_FUZZY_PUB AUTHID CURRENT_USER as
2 /*$Header: ARHFUZYS.pls 120.1 2005/06/16 21:11:56 jhuang ship $ */
3 --
4 --
5
6 -- this record structure contains only fields which are used for
7 -- generating the key in the parties table
8 TYPE party_rec_type IS RECORD(
9 party_id NUMBER := FND_API.G_MISS_NUM,
10 party_name VARCHAR2(360) := FND_API.G_MISS_CHAR,
11 party_type VARCHAR2(30) := FND_API.G_MISS_CHAR,
12 first_name VARCHAR2(150):= FND_API.G_MISS_CHAR,
13 last_name VARCHAR2(150):=FND_API.G_MISS_CHAR
14 );
15
16 -- this record structure contains only fields which are used for
17 -- generating the key in the locations table
18 TYPE location_rec_type IS RECORD(
19 location_id NUMBER := FND_API.G_MISS_NUM,
20 address1 VARCHAR2(240) := FND_API.G_MISS_CHAR,
21 address2 VARCHAR2(240) := FND_API.G_MISS_CHAR,
22 address3 VARCHAR2(240):= FND_API.G_MISS_CHAR,
23 address4 VARCHAR2(240):= FND_API.G_MISS_CHAR,
24 postal_code VARCHAR2(60):=FND_API.G_MISS_CHAR
25 );
26
27 TYPE location_tbl_type is TABLE of hz_locations.location_id%TYPE
28 INDEX BY BINARY_INTEGER;
29
30 TYPE party_tbl_type is TABLE of hz_parties.party_id%TYPE
31 INDEX BY BINARY_INTEGER;
32
33
34 /*===========================================================================+
35 | FUNCTION |
36 | Generate_Key |
37 | |
38 | DESCRIPTION |
39 | Function to generate a party (organization/perosn/group) key, |
40 | address key for use in fuzzy find by TCA. |
41 | |
42 | SCOPE - Public |
43 | |
44 | ARGUMENTS : IN: |
45 | p_key_type |
46 | p_party_name |
47 | p_address1 |
48 | p_address2 |
49 | p_address3 |
50 | p_address4 |
51 | p_postal_code |
52 | p_first_name |
53 | p_last_name |
54 | OUT: |
55 | IN/ OUT: |
56 | |
57 | RETURNS : l_key |
58 | |
59 | NOTES : |
60 | p_key_type must be 'ORGANIZATION', 'PERSON', 'GROUP', 'ADDRESS'. |
61 | For key_type 'ORGANIZATION', 'GROUP' party name is needed |
62 | For key_type 'ADDRESS', address1/address2/address3/address4 and |
63 | postal_code are required |
64 | For key_type 'PERSON' first_name and last_name should be passed in |
65 | |
66 | MODIFICATION HISTORY |
67 | Indrajit Sen 22-JUN-00 Created |
68 | |
69 +===========================================================================*/
70
71 FUNCTION Generate_Key (
72 p_key_type VARCHAR2,
73 p_party_name VARCHAR2 DEFAULT NULL,
74 p_address1 VARCHAR2 DEFAULT NULL,
75 p_address2 VARCHAR2 DEFAULT NULL,
76 p_address3 VARCHAR2 DEFAULT NULL,
77 p_address4 VARCHAR2 DEFAULT NULL,
78 p_postal_code VARCHAR2 DEFAULT NULL,
79 p_first_name VARCHAR2 DEFAULT NULL,
80 p_last_name VARCHAR2 DEFAULT NULL
81 ) RETURN VARCHAR2;
82
83
84
85 /*===========================================================================+
86 | PROCEDURE |
87 | Generate_Full_Table_Key |
88 | |
89 | DESCRIPTION |
90 | Procedure to generate full table key for a particular type of key |
91 | This program is designed to be run as concurrent program |
92 | |
93 | SCOPE - Public |
94 | |
95 | ARGUMENTS : IN: |
96 | Errbuf |
97 | Retcode |
98 | p_key_type |
99 | p_new_rows |
100 | OUT: |
101 | IN/ OUT: |
102 | |
103 | RETURNS : l_location_rec |
104 | |
105 | NOTES : p_key_type can be PARTY, ADDRESS |
106 | p_new_rows can be 'Y', 'N' |
107 | |
108 | MODIFICATION HISTORY |
109 | Indrajit Sen 22-JUN-00 Created |
110 | |
111 +===========================================================================*/
112
113 PROCEDURE Generate_Full_Table_Key (
114 Errbuf OUT NOCOPY VARCHAR2,
115 Retcode OUT NOCOPY VARCHAR2,
116 p_key_type IN VARCHAR2 DEFAULT NULL,
117 p_new_rows IN VARCHAR2 DEFAULT 'Y'
118 );
119
120
121 /*===========================================================================+
122 | PROCEDURE |
123 | Is_Duplicate_Party |
124 | |
125 | DESCRIPTION |
126 | Public procedure for checking if the passed party information |
127 | is a duplicate of an existing party. If program finds that it is a |
128 | duplicate record, it sets p_duplicate to fnd_api.g_true. Also a |
129 | message is set and one matching party_id is returned. |
130 | |
131 | SCOPE - Public |
132 | |
133 | ARGUMENTS : IN: |
134 | p_party_type |
135 | p_person_first_name |
136 | p_person_last_name |
137 | p_party_name |
138 | p_key_search_flag |
139 | OUT: |
140 | p_duplicate |
141 | p_msg_count |
142 | p_msg_data |
143 | p_party_id |
144 | IN/ OUT: |
145 | |
146 | RETURNS : None |
147 | |
148 | NOTES : It does not return all the matching parties. |
149 | |
150 | MODIFICATION HISTORY |
151 | Indrajit Sen 22-JUN-00 Created |
152 | |
153 +===========================================================================*/
154
155 PROCEDURE Is_Duplicate_Party (
156 p_party_type IN VARCHAR2,
157 p_person_first_name IN VARCHAR2 DEFAULT NULL,
158 p_person_last_name IN VARCHAR2 DEFAULT NULL,
159 p_party_name IN VARCHAR2 DEFAULT NULL,
160 p_key_search_flag IN VARCHAR2 DEFAULT FND_API.G_TRUE, --'T' for search on key, 'F' othewise
161 p_duplicate OUT NOCOPY VARCHAR2,
162 p_msg_count OUT NOCOPY NUMBER,
163 p_msg_data OUT NOCOPY VARCHAR2,
164 p_party_id OUT NOCOPY NUMBER
165 );
166
167
168 /*===========================================================================+
169 | PROCEDURE |
170 | Is_Duplicate_Location |
171 | |
172 | DESCRIPTION |
173 | Public procedure for checking if the passed location information |
174 | is a duplicate of an existing location.If a duplicate location is |
175 | found, p_duplicate is set to fnd_api.g_true and a message is also set |
176 | stating that. p_key_search_flag determines whether to do a key match |
177 | or exact match. |
178 | |
179 | SCOPE - Public |
180 | |
181 | ARGUMENTS : IN: |
182 | p_address1 |
183 | p_address2 |
184 | p_address3 |
185 | p_address4 |
186 | p_postal_code |
187 | p_state |
188 | p_city |
189 | p_country |
190 | p_key_search_flag |
191 | OUT: |
192 | p_duplicate |
193 | p_msg_count |
194 | p_msg_data |
195 | p_location_id |
196 | IN/ OUT: |
197 | |
198 | RETURNS : None |
199 | |
200 | NOTES : It does not return all the matching locations. |
201 | |
202 | MODIFICATION HISTORY |
203 | Indrajit Sen 22-JUN-00 Created |
204 | |
205 +===========================================================================*/
206
207 PROCEDURE Is_Duplicate_Location (
208 p_address1 IN VARCHAR2 DEFAULT NULL,
209 p_address2 IN VARCHAR2 DEFAULT NULL,
210 p_address3 IN VARCHAR2 DEFAULT NULL,
211 p_address4 IN VARCHAR2 DEFAULT NULL,
212 p_postal_code IN VARCHAR2 DEFAULT NULL,
213 p_state IN VARCHAR2 DEFAULT NULL,
214 p_city IN VARCHAR2 DEFAULT NULL,
215 p_country IN VARCHAR2 DEFAULT NULL,
216 p_key_search_flag IN VARCHAR2 DEFAULT FND_API.G_TRUE, --'T' for search on key, 'F' othewise
217 p_duplicate OUT NOCOPY VARCHAR2,
218 p_msg_count OUT NOCOPY NUMBER,
219 p_msg_data OUT NOCOPY VARCHAR2,
220 p_location_id OUT NOCOPY NUMBER
221 );
222
223
224
225 /*===========================================================================+
226 | PROCEDURE |
227 | Fuzzy_Search_Address |
228 | |
229 | DESCRIPTION |
230 | Public procedure for doing fuzzy search for address. Pass the address |
231 | lines and the postal code. The number of duplicate records found |
232 | is returned in p_count and list of location ids is returned in |
233 | p_location_tbl. |
234 | |
235 | SCOPE - Public |
236 | |
237 | ARGUMENTS : IN: |
238 | p_address1 |
239 | p_address2 |
240 | p_address3 |
241 | p_address4 |
242 | p_postal_code |
243 | OUT: |
244 | p_location_tbl |
245 | p_count |
246 | IN/ OUT: |
247 | |
248 | RETURNS : None |
249 | |
250 | NOTES : This does fuzzy search based on the generated key only. |
251 | |
252 | MODIFICATION HISTORY |
253 | Indrajit Sen 22-JUN-00 Created |
254 | |
255 +===========================================================================*/
256
257 PROCEDURE Fuzzy_Search_Address(
258 p_address1 IN HZ_LOCATIONS.ADDRESS1%TYPE DEFAULT NULL,
259 p_address2 IN HZ_LOCATIONS.ADDRESS2%TYPE DEFAULT NULL,
260 p_address3 IN HZ_LOCATIONS.ADDRESS3%TYPE DEFAULT NULL,
261 p_address4 IN HZ_LOCATIONS.ADDRESS4%TYPE DEFAULT NULL,
262 p_postal_code IN HZ_LOCATIONS.POSTAL_CODE%TYPE DEFAULT NULL,
263 p_location_tbl OUT NOCOPY LOCATION_TBL_TYPE,
264 p_count OUT NOCOPY NUMBER
265 );
266
267
268 /*===========================================================================+
269 | PROCEDURE |
270 | Fuzzy_Search_Party |
271 | |
272 | DESCRIPTION |
273 | Public procedure for doing fuzzy search for party. In case of an |
274 | organization search, pass party_name and in case of person search |
275 | pass forst_name and last_name. The number of duplicate records found |
276 | is returned in p_count and list of party ids is returned in |
277 | p_party_tbl. |
278 | |
279 | SCOPE - Public |
280 | |
281 | ARGUMENTS : IN: |
282 | p_party_type |
283 | p_party_name |
284 | p_first_name |
285 | p_last_name |
286 | OUT: |
287 | p_party_tbl |
288 | p_count |
289 | IN/ OUT: |
290 | |
291 | RETURNS : None |
292 | |
293 | NOTES : This does fuzzy search based on the generated key only. |
294 | |
295 | MODIFICATION HISTORY |
296 | Indrajit Sen 22-JUN-00 Created |
297 | |
298 +===========================================================================*/
299
300 PROCEDURE Fuzzy_Search_Party(
301 p_party_type IN HZ_PARTIES.PARTY_TYPE%TYPE,
302 p_party_name IN HZ_PARTIES.PARTY_NAME%TYPE DEFAULT NULL,
303 p_first_name IN HZ_PARTIES.PERSON_FIRST_NAME%TYPE DEFAULT NULL,
304 p_last_name IN HZ_PARTIES.PERSON_LAST_NAME%TYPE DEFAULT NULL,
305 p_party_tbl OUT NOCOPY PARTY_TBL_TYPE,
306 p_count OUT NOCOPY NUMBER
307 );
308
309 END;
|
|
|
|