1 PACKAGE arh_addr_pkg AUTHID CURRENT_USER as
2 /* $Header: ARHADDRS.pls 120.8 2005/06/24 18:11:40 apandit ship $*/
3 --
4 --
5 FUNCTION format_address( address_style IN VARCHAR2,
6 address1 IN VARCHAR2,
7 address2 IN VARCHAR2,
8 address3 IN VARCHAR2,
9 address4 IN VARCHAR2,
10 city IN VARCHAR2,
11 county IN VARCHAR2,
12 state IN VARCHAR2,
13 province IN VARCHAR2,
14 postal_code IN VARCHAR2,
15 territory_short_name IN VARCHAR2,
16 country_code IN VARCHAR2 default NULL,
17 customer_name IN VARCHAR2 default NULL,
18 first_name IN VARCHAR2 default NULL,
19 last_name IN VARCHAR2 default NULL,
20 mail_stop IN VARCHAR2 default NULL,
21 default_country_code IN VARCHAR2 default NULL,
22 default_country_desc IN VARCHAR2 default NULL,
23 print_home_country_flag IN VARCHAR2 default 'Y',
24 print_default_attn_flag IN VARCHAR2 default 'N',
25 width IN NUMBER default 1000,
26 height_min IN NUMBER default 1,
27 height_max IN NUMBER default 1
28
29 )return VARCHAR2;
30
31
32 FUNCTION arxtw_format_address( address_style IN VARCHAR2,
33 address1 IN VARCHAR2,
34 address2 IN VARCHAR2,
35 address3 IN VARCHAR2,
36 address4 IN VARCHAR2,
37 city IN VARCHAR2,
38 county IN VARCHAR2,
39 state IN VARCHAR2,
40 province IN VARCHAR2,
41 postal_code IN VARCHAR2,
42 territory_short_name IN VARCHAR2
43 )return VARCHAR2;
44
45
46 FUNCTION format_last_address_line(p_address_style varchar2,
47 p_address3 varchar2,
48 p_address4 varchar2,
49 p_city varchar2,
50 p_county varchar2,
51 p_state varchar2,
52 p_province varchar2,
53 p_country varchar2,
54 p_postal_code varchar2 )
55 RETURN varchar2;
56
57
58
59 procedure check_unique_orig_system_ref(p_orig_system_reference in varchar2 );
60 procedure identifying_address_flag(x_party_id in number );
61 --
62 --
63 procedure update_site_use_flag ( p_address_id in number,
64 p_site_use_code in varchar2,
65 p_site_use_flag in varchar2 );
66 --
67 --
68 PROCEDURE insert_row (
69 X_Address_Id IN OUT NOCOPY NUMBER,
70 X_Last_Update_Date DATE,
71 X_Last_Updated_By NUMBER,
72 X_Creation_Date DATE,
73 X_Created_By NUMBER,
74 X_Status VARCHAR2,
75 X_Orig_System_Reference IN OUT NOCOPY VARCHAR2,
76 X_Country VARCHAR2,
77 X_Address1 VARCHAR2,
78 X_Address2 VARCHAR2,
79 X_Address3 VARCHAR2,
80 X_Address4 VARCHAR2,
81 X_City VARCHAR2,
82 X_Postal_Code VARCHAR2,
83 X_State VARCHAR2,
84 X_Province VARCHAR2,
85 X_County VARCHAR2,
86 X_Last_Update_Login NUMBER,
87 X_Address_Key VARCHAR2,
88 X_Language VARCHAR2,
89 X_Attribute_Category VARCHAR2,
90 X_Attribute1 VARCHAR2,
91 X_Attribute2 VARCHAR2,
92 X_Attribute3 VARCHAR2,
93 X_Attribute4 VARCHAR2,
94 X_Attribute5 VARCHAR2,
95 X_Attribute6 VARCHAR2,
96 X_Attribute7 VARCHAR2,
97 X_Attribute8 VARCHAR2,
98 X_Attribute9 VARCHAR2,
99 X_Attribute10 VARCHAR2,
100 X_Attribute11 VARCHAR2,
101 X_Attribute12 VARCHAR2,
102 X_Attribute13 VARCHAR2,
103 X_Attribute14 VARCHAR2,
104 X_Attribute15 VARCHAR2,
105 X_Attribute16 VARCHAR2,
106 X_Attribute17 VARCHAR2,
107 X_Attribute18 VARCHAR2,
108 X_Attribute19 VARCHAR2,
109 X_Attribute20 VARCHAR2,
110 X_Address_warning out NOCOPY boolean,
111 X_Address_Lines_Phonetic VARCHAR2,
112 X_Global_Attribute_Category VARCHAR2,
113 X_Global_Attribute1 VARCHAR2,
114 X_Global_Attribute2 VARCHAR2,
115 X_Global_Attribute3 VARCHAR2,
116 X_Global_Attribute4 VARCHAR2,
117 X_Global_Attribute5 VARCHAR2,
118 X_Global_Attribute6 VARCHAR2,
119 X_Global_Attribute7 VARCHAR2,
120 X_Global_Attribute8 VARCHAR2,
121 X_Global_Attribute9 VARCHAR2,
122 X_Global_Attribute10 VARCHAR2,
123 X_Global_Attribute11 VARCHAR2,
124 X_Global_Attribute12 VARCHAR2,
125 X_Global_Attribute13 VARCHAR2,
126 X_Global_Attribute14 VARCHAR2,
127 X_Global_Attribute15 VARCHAR2,
128 X_Global_Attribute16 VARCHAR2,
129 X_Global_Attribute17 VARCHAR2,
130 X_Global_Attribute18 VARCHAR2,
131 X_Global_Attribute19 VARCHAR2,
132 X_Global_Attribute20 VARCHAR2,
133 X_Party_site_id IN OUT NOCOPY NUMBER,
134 X_Party_id NUMBER,
135 X_Location_id IN OUT NOCOPY NUMBER,
136 X_Party_Site_Number IN OUT NOCOPY VARCHAR2,
137 X_Identifying_address_flag VARCHAR2,
138 X_Cust_acct_site_id in out NOCOPY NUMBER,
139 X_Cust_account_id NUMBER,
140 X_su_Bill_To_Flag VARCHAR2,
141 X_su_Ship_To_Flag VARCHAR2,
142 X_su_Market_Flag VARCHAR2,
143 X_su_stmt_flag VARCHAR2,
144 X_su_dun_flag VARCHAR2,
145 X_su_legal_flag VARCHAR2,
146 X_Customer_Category VARCHAR2,
147 X_Key_Account_Flag VARCHAR2,
148 X_Territory_id NUMBER,
149 X_ece_tp_location_code VARCHAR2,
150 x_address_mode VARCHAR2,
151 x_territory VARCHAR2,
152 x_translated_customer_name VARCHAR2,
153 x_sales_tax_geo_code VARCHAR2,
154 x_sale_tax_inside_city_limits VARCHAR2,
155 x_ADDRESSEE VARCHAR2,
156 x_shared_party_site IN VARCHAR2,
157 x_update_account_site IN VARCHAR2,
158 x_create_location_party_site IN VARCHAR2,
159 x_msg_count OUT NOCOPY NUMBER,
160 x_msg_data OUT NOCOPY VARCHAR2,
161 x_return_status OUT NOCOPY VARCHAR2,
162 --Bug#2689667 {
163 x_description IN VARCHAR2 DEFAULT NULL,
164 x_short_description IN VARCHAR2 DEFAULT NULL,
165 x_floor IN VARCHAR2 DEFAULT NULL,
166 x_house_number IN VARCHAR2 DEFAULT NULL,
167 x_location_directions IN VARCHAR2 DEFAULT NULL,
168 x_postal_plus4_code IN VARCHAR2 DEFAULT NULL,
169 x_po_box_number IN VARCHAR2 DEFAULT NULL,
170 x_street IN VARCHAR2 DEFAULT NULL,
171 x_street_number IN VARCHAR2 DEFAULT NULL,
172 x_street_suffix IN VARCHAR2 DEFAULT NULL,
173 x_suite IN VARCHAR2 DEFAULT NULL,
174 --}
175 /*Bug 3976386 MOAC changes*/
176 X_ORG_ID IN NUMBER DEFAULT NULL
177
178 );
179
180
181
182 --
183 FUNCTION location_exists (p_address_id IN Number
184 ) return BOOLEAN;
185 --
186 --
187 FUNCTION transaction_exists (p_address_id IN Number,
188 p_customer_id IN Number
189 ) return BOOLEAN;
190
191 FUNCTION transaction_morg_exists (p_address_id IN Number,
192 p_customer_id IN Number
193 ) return BOOLEAN;
194
195 FUNCTION check_tran_for_all_accts(p_location_id in number) return BOOLEAN;
196 --
197 --
198 procedure check_unique_edi_location(p_edi_location in varchar2,
199 p_customer_id in number,
200 p_orig_system_reference in varchar2);
201
202
203
204 PROCEDURE delete_customer_alt_names(p_rowid in varchar2,
205 p_status in varchar2,
206 p_customer_id in number,
207 p_address_id in number );
208 --
209
210 PROCEDURE update_row (
211 X_Address_Id NUMBER,
212 X_Last_Update_Date IN OUT NOCOPY DATE,
213 X_party_site_Last_Update_Date IN OUT NOCOPY DATE,
214 X_loc_Last_Update_Date IN OUT NOCOPY DATE,
215 X_Last_Updated_By NUMBER,
216 X_Status VARCHAR2,
217 X_Orig_System_Reference VARCHAR2,
218 X_Country VARCHAR2,
219 X_Address1 VARCHAR2,
220 X_Address2 VARCHAR2,
221 X_Address3 VARCHAR2,
222 X_Address4 VARCHAR2,
223 X_City VARCHAR2,
224 X_Postal_Code VARCHAR2,
225 X_State VARCHAR2,
226 X_Province VARCHAR2,
227 X_County VARCHAR2,
228 X_Last_Update_Login NUMBER,
229 X_Address_Key VARCHAR2,
230 X_Language VARCHAR2,
231 X_Attribute_Category VARCHAR2,
232 X_Attribute1 VARCHAR2,
233 X_Attribute2 VARCHAR2,
234 X_Attribute3 VARCHAR2,
235 X_Attribute4 VARCHAR2,
236 X_Attribute5 VARCHAR2,
237 X_Attribute6 VARCHAR2,
238 X_Attribute7 VARCHAR2,
239 X_Attribute8 VARCHAR2,
240 X_Attribute9 VARCHAR2,
241 X_Attribute10 VARCHAR2,
242 X_Attribute11 VARCHAR2,
243 X_Attribute12 VARCHAR2,
244 X_Attribute13 VARCHAR2,
245 X_Attribute14 VARCHAR2,
246 X_Attribute15 VARCHAR2,
247 X_Attribute16 VARCHAR2,
248 X_Attribute17 VARCHAR2,
249 X_Attribute18 VARCHAR2,
250 X_Attribute19 VARCHAR2,
251 X_Attribute20 VARCHAR2,
252 X_Address_warning OUT NOCOPY BOOLEAN,
253 X_Address_Lines_Phonetic VARCHAR2,
254 X_Global_Attribute_Category VARCHAR2,
255 X_Global_Attribute1 VARCHAR2,
256 X_Global_Attribute2 VARCHAR2,
257 X_Global_Attribute3 VARCHAR2,
258 X_Global_Attribute4 VARCHAR2,
259 X_Global_Attribute5 VARCHAR2,
260 X_Global_Attribute6 VARCHAR2,
261 X_Global_Attribute7 VARCHAR2,
262 X_Global_Attribute8 VARCHAR2,
263 X_Global_Attribute9 VARCHAR2,
264 X_Global_Attribute10 VARCHAR2,
265 X_Global_Attribute11 VARCHAR2,
266 X_Global_Attribute12 VARCHAR2,
267 X_Global_Attribute13 VARCHAR2,
268 X_Global_Attribute14 VARCHAR2,
269 X_Global_Attribute15 VARCHAR2,
270 X_Global_Attribute16 VARCHAR2,
271 X_Global_Attribute17 VARCHAR2,
272 X_Global_Attribute18 VARCHAR2,
273 X_Global_Attribute19 VARCHAR2,
274 X_Global_Attribute20 VARCHAR2,
275 X_Party_site_id NUMBER,
279 X_Identifying_address_flag VARCHAR2,
276 X_Party_id NUMBER,
277 X_Location_id NUMBER,
278 X_Party_Site_Number VARCHAR2,
280 X_Cust_acct_site_id NUMBER,
281 X_Cust_account_id NUMBER,
282 X_su_Bill_To_Flag VARCHAR2,
283 X_su_Ship_To_Flag VARCHAR2,
284 X_su_Market_Flag VARCHAR2,
285 X_su_stmt_flag VARCHAR2,
286 X_su_dun_flag VARCHAR2,
287 X_su_legal_flag VARCHAR2,
288 X_Customer_Category VARCHAR2,
289 X_Key_Account_Flag VARCHAR2,
290 X_Territory_id NUMBER,
291 X_ece_tp_location_code VARCHAR2,
292 x_address_mode VARCHAR2,
293 X_Territory VARCHAR2,
294 X_Translated_Customer_Name VARCHAR2,
295 X_Sales_Tax_Geocode VARCHAR2,
296 X_Sales_Tax_Inside_City_Limits VARCHAR2,
297 x_ADDRESSEE VARCHAR2,
298 x_msg_count OUT NOCOPY NUMBER,
299 x_msg_data OUT NOCOPY VARCHAR2,
300 x_return_status OUT NOCOPY VARCHAR2,
301 x_object_version IN NUMBER DEFAULT -1,
302 x_object_version_party_site IN NUMBER DEFAULT -1,
303 x_object_version_location IN NUMBER DEFAULT -1,
304 --Bug#2689667 {
305 x_description IN VARCHAR2 DEFAULT NULL,
306 x_short_description IN VARCHAR2 DEFAULT NULL,
307 x_floor IN VARCHAR2 DEFAULT NULL,
308 x_house_number IN VARCHAR2 DEFAULT NULL,
309 x_location_directions IN VARCHAR2 DEFAULT NULL,
310 x_postal_plus4_code IN VARCHAR2 DEFAULT NULL,
311 x_po_box_number IN VARCHAR2 DEFAULT NULL,
312 x_street IN VARCHAR2 DEFAULT NULL,
313 x_street_number IN VARCHAR2 DEFAULT NULL,
314 x_street_suffix IN VARCHAR2 DEFAULT NULL,
315 x_suite IN VARCHAR2 DEFAULT NULL,
316
317 --}
318 /*Bug 3976386 MOAC changes*/
319 X_ORG_ID IN NUMBER DEFAULT NULL
320 );
321
322 --{BUG#4058539
323 PROCEDURE get_location_data
324 (p_location_id IN NUMBER,
325 x_Country IN OUT NOCOPY VARCHAR2,
326 x_Address1 IN OUT NOCOPY VARCHAR2,
327 x_Address2 IN OUT NOCOPY VARCHAR2,
328 x_Address3 IN OUT NOCOPY VARCHAR2,
329 x_Address4 IN OUT NOCOPY VARCHAR2,
330 x_City IN OUT NOCOPY VARCHAR2,
331 x_Postal_Code IN OUT NOCOPY VARCHAR2,
332 x_State IN OUT NOCOPY VARCHAR2,
333 x_Province IN OUT NOCOPY VARCHAR2,
334 x_County IN OUT NOCOPY VARCHAR2,
335 x_description IN OUT NOCOPY VARCHAR2,
336 x_short_description IN OUT NOCOPY VARCHAR2,
337 x_floor IN OUT NOCOPY VARCHAR2,
338 x_house_number IN OUT NOCOPY VARCHAR2,
339 x_location_directions IN OUT NOCOPY VARCHAR2,
340 x_postal_plus4_code IN OUT NOCOPY VARCHAR2,
341 x_po_box_number IN OUT NOCOPY VARCHAR2,
342 x_street IN OUT NOCOPY VARCHAR2,
343 x_street_number IN OUT NOCOPY VARCHAR2,
344 x_street_suffix IN OUT NOCOPY VARCHAR2,
345 x_suite IN OUT NOCOPY VARCHAR2,
346 x_Address_Key IN OUT NOCOPY VARCHAR2,
347 x_Language IN OUT NOCOPY VARCHAR2,
348 x_Address_Lines_Phonetic IN OUT NOCOPY VARCHAR2,
349 x_Sales_Tax_Geocode IN OUT NOCOPY VARCHAR2,
350 x_Sales_Tax_Inside_City_Limits IN OUT NOCOPY VARCHAR2,
351 --
352 x_return_status IN OUT NOCOPY VARCHAR2,
353 x_msg_data IN OUT NOCOPY VARCHAR2,
354 x_msg_count IN OUT NOCOPY NUMBER);
355
356 --
357 -- This procedure compare_location_existing overloaded
358 -- return x_loc_updated
359 -- 'Y' if the location data is different to the existing one
360 -- 'N' if the location data is the same as to the existing one
361 -- 'X' if the p_location_id is no hitting any existing location
362 -- In the case of
363 -- x_loc_updated = 'Y', all the x_<attribute_name> will return the existing data
364 -- otherwise they stay the same as inputs
365 --
366 PROCEDURE compare_location_existing
367 (p_location_id IN NUMBER,
368 x_Country IN OUT NOCOPY VARCHAR2,
369 x_Address1 IN OUT NOCOPY VARCHAR2,
370 x_Address2 IN OUT NOCOPY VARCHAR2,
371 x_Address3 IN OUT NOCOPY VARCHAR2,
372 x_Address4 IN OUT NOCOPY VARCHAR2,
373 x_City IN OUT NOCOPY VARCHAR2,
377 x_County IN OUT NOCOPY VARCHAR2,
374 x_Postal_Code IN OUT NOCOPY VARCHAR2,
375 x_State IN OUT NOCOPY VARCHAR2,
376 x_Province IN OUT NOCOPY VARCHAR2,
378 x_description IN OUT NOCOPY VARCHAR2,
379 x_short_description IN OUT NOCOPY VARCHAR2,
380 x_floor IN OUT NOCOPY VARCHAR2,
381 x_house_number IN OUT NOCOPY VARCHAR2,
382 x_location_directions IN OUT NOCOPY VARCHAR2,
383 x_postal_plus4_code IN OUT NOCOPY VARCHAR2,
384 x_po_box_number IN OUT NOCOPY VARCHAR2,
385 x_street IN OUT NOCOPY VARCHAR2,
386 x_street_number IN OUT NOCOPY VARCHAR2,
387 x_street_suffix IN OUT NOCOPY VARCHAR2,
388 x_suite IN OUT NOCOPY VARCHAR2,
389 x_Language IN OUT NOCOPY VARCHAR2,
390 x_Address_Lines_Phonetic IN OUT NOCOPY VARCHAR2,
391 x_Sales_Tax_Geocode IN OUT NOCOPY VARCHAR2,
392 x_Sales_Tax_Inside_City_Limits IN OUT NOCOPY VARCHAR2,
393 x_loc_updated IN OUT NOCOPY VARCHAR2);
394 --}
395
396 -- This is a overloaded procedure for check_printed_trx
397 -- bug#4058539
398 -- this procedure check_printed_trx
399 -- Return in the input parameters the existing location data if location exist
400 -- Return in the x_printed_trx_loc_modified
401 -- * FALSE : No violation to transaction about the printed invoice on location update
402 -- * TRUE : Violation to transaction about the printed invoice on location update
403 -- The x_return_status is set to error only other error are detected
404 --
405 PROCEDURE check_printed_trx
406 (p_location_id IN NUMBER,
407 x_Country IN OUT NOCOPY VARCHAR2,
408 x_Address1 IN OUT NOCOPY VARCHAR2,
409 x_Address2 IN OUT NOCOPY VARCHAR2,
410 x_Address3 IN OUT NOCOPY VARCHAR2,
411 x_Address4 IN OUT NOCOPY VARCHAR2,
412 x_City IN OUT NOCOPY VARCHAR2,
413 x_Postal_Code IN OUT NOCOPY VARCHAR2,
414 x_State IN OUT NOCOPY VARCHAR2,
415 x_Province IN OUT NOCOPY VARCHAR2,
416 x_County IN OUT NOCOPY VARCHAR2,
417 x_description IN OUT NOCOPY VARCHAR2,
418 x_short_description IN OUT NOCOPY VARCHAR2,
419 x_floor IN OUT NOCOPY VARCHAR2,
420 x_house_number IN OUT NOCOPY VARCHAR2,
421 x_location_directions IN OUT NOCOPY VARCHAR2,
422 x_postal_plus4_code IN OUT NOCOPY VARCHAR2,
423 x_po_box_number IN OUT NOCOPY VARCHAR2,
424 x_street IN OUT NOCOPY VARCHAR2,
425 x_street_number IN OUT NOCOPY VARCHAR2,
426 x_street_suffix IN OUT NOCOPY VARCHAR2,
427 x_suite IN OUT NOCOPY VARCHAR2,
428 x_Language IN OUT NOCOPY VARCHAR2,
429 x_Address_Lines_Phonetic IN OUT NOCOPY VARCHAR2,
430 x_Sales_Tax_Geocode IN OUT NOCOPY VARCHAR2,
431 x_Sales_Tax_Inside_City_Limits IN OUT NOCOPY VARCHAR2,
432 --
433 x_printed_trx_loc_modified IN OUT NOCOPY VARCHAR2,
434 x_return_status IN OUT NOCOPY VARCHAR2,
435 x_msg_data IN OUT NOCOPY VARCHAR2,
436 x_msg_count IN OUT NOCOPY NUMBER);
437
438 PROCEDURE check_addr_modif_allowed
439 (p_location_id IN NUMBER,
440 x_loc_modif_allowed IN OUT NOCOPY VARCHAR2,
441 x_return_status IN OUT NOCOPY VARCHAR2,
442 x_msg_data IN OUT NOCOPY VARCHAR2,
443 x_msg_count IN OUT NOCOPY NUMBER);
444
445 FUNCTION the_ar_miss_char RETURN VARCHAR2;
446
450 END arh_addr_pkg;
447 FUNCTION the_ar_null_char RETURN VARCHAR2;
448 --}
449
[Home] [Help]