1 PACKAGE BODY ozf_location_pvt as
2 /* $Header: ozfvlocb.pls 120.1 2005/09/15 19:47:45 appldev ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30):='OZF_LOCATION_PVT';
5
6 FUNCTION ozfx_format_address( address_style IN VARCHAR2,
7 address1 IN VARCHAR2,
8 address2 IN VARCHAR2,
9 address3 IN VARCHAR2,
10 address4 IN VARCHAR2,
11 city IN VARCHAR2,
12 county IN VARCHAR2,
13 state IN VARCHAR2,
14 province IN VARCHAR2,
15 postal_code IN VARCHAR2,
16 territory_short_name IN VARCHAR2
17 )return VARCHAR2 IS
18 l_address varchar2(1000);
19 BEGIN
20 --
21 -- address1 is a NOT NULL field.
22 --
23 l_address := address1;
24
25 IF ( address2 IS NOT NULL ) THEN
26 l_address := l_address || ', ' || address2;
27 END IF;
28
29 IF ( address3 IS NOT NULL ) THEN
30 l_address := l_address || ', ' || address3;
31 END IF;
32
33 IF ( address4 IS NOT NULL ) THEN
34 l_address := l_address || ', ' || address4;
35 END IF;
36
37 IF ( city IS NOT NULL ) THEN
38 l_address := l_address || ', ' || city;
39 END IF;
40
41 IF ( county IS NOT NULL ) THEN
42 l_address := l_address || ', ' || county;
43 END IF;
44
45 IF ( state IS NOT NULL ) THEN
46 l_address := l_address || ', ' || state;
47 END IF;
48
49 IF ( province IS NOT NULL ) THEN
50 l_address := l_address || ', ' || province;
51 END IF;
52
53 IF ( postal_code IS NOT NULL ) THEN
54 l_address := l_address || ', ' || postal_code;
55 END IF;
56
57 IF ( territory_short_name IS NOT NULL ) THEN
58 l_address := l_address || ', ' || territory_short_name;
59 END IF;
60
61 RETURN( l_address );
62 END ozfx_format_address;
63
64 /*--------------------------------------------------------------------+
65 PUBLIC FUNCTION
66 format_address
67
68 DESCRIPTION
69 This function returns a single string of concatenated address
70 segments. The segments and their display order may vary according
71 to a given address format. Line breaks are inserted in order for the
72 segments to be allocated inside the given box dimension.
73
74 If the box size is not big enough to contain all the required
75 segment together with segment joint characters(spaces/commas),
76 or the box width is not long enough to contain any segment,
77 then the function truncates the string to provide the possible output.
78
79 REQUIRES
80 address_style : address format style
81 address1 : address line 1
82 address2 : address line 2
83 address3 : address line 3
84 address4 : address line 4
85 city : name of city
86 county : name of county
87 state : name of state
88 province : name of province
89 postal_code : postal code
90 territory_short_name : territory short name
91
92 OPTIONAL REQUIRES
93 country_code : country code
94 customer_name : customer name
95 first_name : contact first name
96 last_name : contact last name
97 mail_stop : mailing informatioin
98 default_country_code : default country code
99 default_country_desc : default territory short name
100 print_home_country_flag : flag to control home county printing
101 print_default_attn_flag : flag to control default attention message
102 width NUMBER : address box width
103 height_min : address box minimum height
104 height_max : address box maximum height
105
106 RETURN
107 formatted address string
108
109 +--------------------------------------------------------------------*/
110 FUNCTION format_address( address_style IN VARCHAR2,
111 address1 IN VARCHAR2,
112 address2 IN VARCHAR2,
113 address3 IN VARCHAR2,
114 address4 IN VARCHAR2,
115 city IN VARCHAR2,
116 county IN VARCHAR2,
117 state IN VARCHAR2,
118 province IN VARCHAR2,
119 postal_code IN VARCHAR2,
120 territory_short_name IN VARCHAR2,
121 country_code IN VARCHAR2 default NULL,
122 customer_name IN VARCHAR2 default NULL,
123 first_name IN VARCHAR2 default NULL,
124 last_name IN VARCHAR2 default NULL,
125 mail_stop IN VARCHAR2 default NULL,
126 default_country_code IN VARCHAR2 default NULL,
127 default_country_desc IN VARCHAR2 default NULL,
128 print_home_country_flag IN VARCHAR2 default 'Y',
129 print_default_attn_flag IN VARCHAR2 default 'N',
130 width IN NUMBER default 1000,
131 height_min IN NUMBER default 1,
132 height_max IN NUMBER default 1
133 )return VARCHAR2 IS
134 BEGIN
135 return( ozfx_format_address( address_style,
136 address1,
137 address2,
138 address3,
139 address4,
140 city,
141 county,
142 state,
143 province,
144 postal_code,
145 territory_short_name ) );
146
147 END format_address;
148
149
150
151 -- the following is not needed right now but may be useful later. CHECK WHILE CODING.
152 FUNCTION format_last_address_line(p_address_style varchar2,
153 p_address3 varchar2,
154 p_address4 varchar2,
155 p_city varchar2,
156 p_county varchar2,
157 p_state varchar2,
158 p_province varchar2,
159 p_country varchar2,
160 p_postal_code varchar2 )
161 RETURN varchar2 IS
162
163
164 l_address varchar2(1000);
165 BEGIN
166 IF ( p_address3 IS NOT NULL )
167 THEN
168 l_address := p_address3;
169 END IF;
170
171 IF ( p_address4 IS NOT NULL )
172 THEN
173 IF (l_address IS NOT NULL)
174 THEN
175 l_address := l_address || ', ' || p_address4;
176 ELSE l_address := p_address4;
177 END IF;
178 END IF;
179
180 IF ( p_city IS NOT NULL )
181 THEN
182 IF (l_address IS NOT NULL)
183 THEN
184 l_address := l_address || ', ' || p_city;
185 ELSE l_address := p_city;
186 END IF;
187 END IF;
188
189 IF ( p_state IS NOT NULL )
190 THEN
191 IF (l_address IS NOT NULL)
192 THEN
193 l_address := l_address || ', ' || p_state;
194 ELSE l_address := p_state;
195 END IF;
196 END IF;
197
198 IF ( p_province IS NOT NULL )
199 THEN
200 IF (l_address IS NOT NULL)
201 THEN
202 l_address := l_address || ', ' || p_province;
203 ELSE l_address := p_province;
204 END IF;
205 END IF;
206
207 IF ( p_postal_code IS NOT NULL )
208 THEN
209 IF (l_address IS NOT NULL)
210 THEN
211 l_address := l_address || ' ' || p_postal_code;
212 ELSE l_address := p_postal_code;
213 END IF;
214 END IF;
215
216 IF ( p_country IS NOT NULL )
217 THEN
218 IF (l_address IS NOT NULL)
219 THEN
220 l_address := l_address || ' ' || p_country;
221 ELSE l_address := p_country;
222 END IF;
223 END IF;
224
225 RETURN(l_address);
226
227 END format_last_address_line;
228
229
230 FUNCTION get_location (p_location_id IN NUMBER,
231 p_cust_site_use_code IN VARCHAR2 := NULL)
232 return VARCHAR2 IS
233
234 CURSOR locations (c_location_id NUMBER) IS
235 SELECT
236 loc.address_style, loc.address1, loc.address2, loc.address3, loc.address4, loc.city,
237 loc.county, loc.state, loc.province, loc.postal_code, terr.territory_short_name
238 FROM
239 hz_locations loc,
240 fnd_territories_vl terr
241 WHERE
242 loc.location_id = c_location_id
243 AND loc.country = terr.territory_code(+);
244
245 locations_rec locations%rowtype;
246 l_cust_site_use_code ozf_account_allocations.site_use_code%TYPE;
247
248 BEGIN
249 OPEN locations (p_location_id);
250 FETCH locations into locations_rec;
251 CLOSE locations;
252
253 IF p_cust_site_use_code IS NULL THEN
254
255 return( ozfx_format_address( locations_rec.address_style,
256 locations_rec.address1,
257 locations_rec.address2,
258 locations_rec.address3,
259 locations_rec.address4,
260 locations_rec.city,
261 locations_rec.county,
262 locations_rec.state,
263 locations_rec.province,
264 locations_rec.postal_code,
265 locations_rec.territory_short_name ) );
266
267 ELSE
268
269 l_cust_site_use_code := INITCAP(LOWER(p_cust_site_use_code));
270
271 return( l_cust_site_use_code || ': '||
272 ozfx_format_address( locations_rec.address_style,
273 locations_rec.address1,
274 locations_rec.address2,
275 locations_rec.address3,
276 locations_rec.address4,
277 locations_rec.city,
278 locations_rec.county,
279 locations_rec.state,
280 locations_rec.province,
281 locations_rec.postal_code,
282 locations_rec.territory_short_name ) );
283
284 END IF;
285
286 END get_location;
287
288
289 FUNCTION get_location_id (p_site_use_id IN NUMBER)
290 return NUMBER IS
291
292 CURSOR location_csr
293 IS
294 SELECT
295 hzps.location_id location_id
296 FROM
297 hz_cust_site_uses_all hzcsu,
298 hz_cust_acct_sites_all hzcas,
299 hz_party_sites hzps
300 WHERE
301 hzcsu.site_use_id = p_site_use_id
302 AND hzcsu.cust_acct_site_id = hzcas.cust_acct_site_id
303 AND hzcas.party_site_id = hzps.party_site_id;
304
305 -- The following is commented because _all tables are not used
306 -- Instead org-striped views are used
307 ---- AND hzcas.org_id = p_org_id
308 ---- AND hzcsu.org_id = p_org_id;
309
310 l_location_id NUMBER;
311 l_site_use_id NUMBER;
312 l_api_name CONSTANT VARCHAR2(30) := 'get_location_id';
313 l_full_api_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
314
315 BEGIN
316 l_site_use_id := p_site_use_id;
317
318 OPEN location_csr;
319 FETCH location_csr into l_location_id;
320 CLOSE location_csr;
321
322 return l_location_id;
323
324 EXCEPTION
325 WHEN OTHERS THEN
326 OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
327 return NULL;
328 END get_location_id;
329
330
331 FUNCTION get_party_name(p_party_id IN NUMBER,
332 p_site_use_id IN NUMBER)
333 RETURN VARCHAR2
334 IS
335 x_party_name VARCHAR2(250);
336 x_location VARCHAR2(250);
337 BEGIN
338
339 /*
340 SELECT hz.party_name||' '||hzcsu.location into x_party_name
341 FROM hz_parties hz,
342 hz_cust_site_uses_all hzcsu
343 WHERE hzcsu.site_use_id = p_site_use_id
344 AND hz.party_id = p_party_id;
345 */
346 BEGIN
347 SELECT hz.party_name into x_party_name
348 FROM hz_parties hz
349 WHERE hz.party_id = p_party_id;
350 EXCEPTION
351 WHEN OTHERS THEN
352 x_party_name := TO_CHAR(p_party_id);
353 END;
354 IF p_party_id = -9999 THEN
355 x_party_name := fnd_message.get_string('OZF', 'OZF_TP_UNALLOC_ACCOUNT_TXT');
356 END IF;
357 BEGIN
358 SELECT hzcsu.location into x_location
359 FROM hz_cust_site_uses_all hzcsu
360 WHERE hzcsu.site_use_id = p_site_use_id;
361 EXCEPTION
362 WHEN OTHERS THEN
363 x_location := TO_CHAR(p_site_use_id);
364 END;
365
366 RETURN x_party_name||' '||x_location;
367 EXCEPTION
368 WHEN OTHERS THEN
369 --RETURN 'Target Sausalito (OPS)';
370 RETURN NULL;
371 END;
372
373
374 END ozf_location_pvt;
[Home] [Help]