1 package body AP_POL_LOCATIONS_PKG as
2 /* $Header: apwplocb.pls 120.4 2005/11/16 04:24:34 rlangi noship $ */
3
4 /*=======================================================================+
5 | Standard handlers
6 *=======================================================================*/
7 procedure INSERT_ROW (
8 X_ROWID in out nocopy VARCHAR2,
9 X_LOCATION_ID in NUMBER,
10 X_TERRITORY_CODE in VARCHAR2,
11 X_UNDEFINED_LOCATION_FLAG in VARCHAR2,
12 X_END_DATE in DATE,
13 X_LOCATION in VARCHAR2,
14 X_DESCRIPTION in VARCHAR2,
15 X_STATUS in VARCHAR2,
16 X_LOCATION_TYPE in VARCHAR2,
17 X_COUNTRY in VARCHAR2,
18 X_STATE_PROVINCE_ID in NUMBER,
19 X_COUNTY_ID in NUMBER,
20 X_CITY_LOCALITY_ID in NUMBER,
21 X_CREATION_DATE in DATE,
22 X_CREATED_BY in NUMBER,
23 X_LAST_UPDATE_DATE in DATE,
24 X_LAST_UPDATED_BY in NUMBER,
25 X_LAST_UPDATE_LOGIN in NUMBER) is
26
27 begin
28 INSERT_ROW (X_ROWID,
29 X_LOCATION_ID,
30 X_TERRITORY_CODE,
31 X_UNDEFINED_LOCATION_FLAG,
32 X_END_DATE,
33 X_LOCATION,
34 X_DESCRIPTION,
35 X_STATUS,
36 X_LOCATION_TYPE,
37 X_COUNTRY,
38 X_STATE_PROVINCE_ID,
39 X_COUNTY_ID,
40 X_CITY_LOCALITY_ID,
41 X_CREATION_DATE,
42 X_CREATED_BY,
43 X_LAST_UPDATE_DATE,
44 X_LAST_UPDATED_BY,
45 X_LAST_UPDATE_LOGIN,
46 NULL,
47 userenv('LANG'));
48 end INSERT_ROW;
49
50 procedure LOCK_ROW (
51 X_LOCATION_ID in NUMBER,
52 X_TERRITORY_CODE in VARCHAR2,
53 X_UNDEFINED_LOCATION_FLAG in VARCHAR2,
54 X_END_DATE in DATE,
55 X_LOCATION in VARCHAR2,
56 X_DESCRIPTION in VARCHAR2,
57 X_STATUS in VARCHAR2,
58 X_LOCATION_TYPE in VARCHAR2,
59 X_COUNTRY in VARCHAR2,
60 X_STATE_PROVINCE_ID in NUMBER,
61 X_COUNTY_ID in NUMBER,
62 X_CITY_LOCALITY_ID in NUMBER) is
63
64 begin
65 LOCK_ROW (X_LOCATION_ID,
66 X_TERRITORY_CODE,
67 X_UNDEFINED_LOCATION_FLAG,
68 X_END_DATE,
69 X_LOCATION,
70 X_DESCRIPTION,
71 X_STATUS,
72 X_LOCATION_TYPE,
73 X_COUNTRY,
74 X_STATE_PROVINCE_ID,
75 X_COUNTY_ID,
76 X_CITY_LOCALITY_ID,
77 NULL,
78 userenv('LANG'));
79
80 end LOCK_ROW;
81
82 procedure UPDATE_ROW (X_LOCATION_ID in NUMBER,
83 X_TERRITORY_CODE in VARCHAR2,
84 X_UNDEFINED_LOCATION_FLAG in VARCHAR2,
85 X_END_DATE in DATE,
86 X_LOCATION in VARCHAR2,
87 X_DESCRIPTION in VARCHAR2,
88 X_STATUS in VARCHAR2,
89 X_LOCATION_TYPE in VARCHAR2,
90 X_COUNTRY in VARCHAR2,
91 X_STATE_PROVINCE_ID in NUMBER,
92 X_COUNTY_ID in NUMBER,
93 X_CITY_LOCALITY_ID in NUMBER,
94 X_LAST_UPDATE_DATE in DATE,
95 X_LAST_UPDATED_BY in NUMBER,
96 X_LAST_UPDATE_LOGIN in NUMBER) is
97 begin
98 UPDATE_ROW (X_LOCATION_ID,
99 X_TERRITORY_CODE,
100 X_UNDEFINED_LOCATION_FLAG,
101 X_END_DATE,
102 X_LOCATION,
103 X_DESCRIPTION,
104 X_STATUS,
105 X_LOCATION_TYPE,
106 X_COUNTRY,
107 X_STATE_PROVINCE_ID,
108 X_COUNTY_ID,
109 X_CITY_LOCALITY_ID,
110 X_LAST_UPDATE_DATE,
111 X_LAST_UPDATED_BY,
112 X_LAST_UPDATE_LOGIN,
113 NULL,
114 userenv('LANG'));
115
116 end UPDATE_ROW;
117
118 procedure ADD_LANGUAGE is
119 begin
120 ADD_LANGUAGE(NULL,userenv('LANG'));
121 end ADD_LANGUAGE;
122
123 procedure DELETE_ROW (
124 X_LOCATION_ID in NUMBER,
125 X_LANGUAGE in VARCHAR2 --bug 2650513
126 ) is
127 begin
128 delete from AP_POL_LOCATIONS_TL
129 where LOCATION_ID = X_LOCATION_ID;
130
131 if (sql%notfound) then
132 raise no_data_found;
133 end if;
134
135 delete from AP_POL_LOCATIONS_B
136 where LOCATION_ID = X_LOCATION_ID;
137
138 if (sql%notfound) then
139 raise no_data_found;
140 end if;
141 end DELETE_ROW;
142
143 /*=======================================================================+
144 | Handlers with language
145 *=======================================================================*/
146 procedure INSERT_ROW (
147 X_ROWID in out nocopy VARCHAR2,
148 X_LOCATION_ID in NUMBER,
149 X_TERRITORY_CODE in VARCHAR2,
150 X_UNDEFINED_LOCATION_FLAG in VARCHAR2,
151 X_END_DATE in DATE,
152 X_LOCATION in VARCHAR2,
153 X_DESCRIPTION in VARCHAR2,
154 X_STATUS in VARCHAR2,
155 X_LOCATION_TYPE in VARCHAR2,
156 X_COUNTRY in VARCHAR2,
157 X_STATE_PROVINCE_ID in NUMBER,
158 X_COUNTY_ID in NUMBER,
159 X_CITY_LOCALITY_ID in NUMBER,
160 X_CREATION_DATE in DATE,
161 X_CREATED_BY in NUMBER,
162 X_LAST_UPDATE_DATE in DATE,
163 X_LAST_UPDATED_BY in NUMBER,
164 X_LAST_UPDATE_LOGIN in NUMBER,
165 X_LANGUAGE in VARCHAR2,
166 X_SOURCE_LANG in VARCHAR2) is
167
168 cursor C is select ROWID from AP_POL_LOCATIONS_B
169 where LOCATION_ID = X_LOCATION_ID;
170
171 begin
172
173 insert into AP_POL_LOCATIONS_B (
174 LOCATION_ID,
175 TERRITORY_CODE,
176 UNDEFINED_LOCATION_FLAG,
177 END_DATE,
178 STATUS,
179 LOCATION_TYPE,
180 COUNTRY,
181 STATE_PROVINCE_ID,
182 COUNTY_ID,
183 CITY_LOCALITY_ID,
184 CREATION_DATE,
185 CREATED_BY,
186 LAST_UPDATE_DATE,
187 LAST_UPDATED_BY,
188 LAST_UPDATE_LOGIN
189 ) values (
190 X_LOCATION_ID,
191 X_TERRITORY_CODE,
192 X_UNDEFINED_LOCATION_FLAG,
193 X_END_DATE,
194 X_STATUS,
195 X_LOCATION_TYPE,
196 X_COUNTRY,
197 X_STATE_PROVINCE_ID,
198 X_COUNTY_ID,
199 X_CITY_LOCALITY_ID,
200 X_CREATION_DATE,
201 X_CREATED_BY,
202 X_LAST_UPDATE_DATE,
203 X_LAST_UPDATED_BY,
204 X_LAST_UPDATE_LOGIN
205 );
206
207 insert into AP_POL_LOCATIONS_TL (
208 LOCATION_ID,
209 LOCATION,
213 LAST_UPDATE_LOGIN,
210 DESCRIPTION,
211 CREATION_DATE,
212 CREATED_BY,
214 LAST_UPDATE_DATE,
215 LAST_UPDATED_BY,
216 LANGUAGE,
217 SOURCE_LANG
218 ) select
219 X_LOCATION_ID,
220 X_LOCATION,
221 X_DESCRIPTION,
222 X_CREATION_DATE,
223 X_CREATED_BY,
224 X_LAST_UPDATE_LOGIN,
225 X_LAST_UPDATE_DATE,
226 X_LAST_UPDATED_BY,
227 L.LANGUAGE_CODE,
228 NVL(X_SOURCE_LANG,BASE.LANGUAGE_CODE)
229 from FND_LANGUAGES L,
230 FND_LANGUAGES BASE
231 where L.INSTALLED_FLAG in ('I', 'B')
232 AND BASE.INSTALLED_FLAG = 'B'
233 and not exists
237 and T.LANGUAGE = L.LANGUAGE_CODE);
234 (select NULL
235 from AP_POL_LOCATIONS_TL T
236 where T.LOCATION_ID = X_LOCATION_ID
238
239 open c;
240 fetch c into X_ROWID;
241 if (c%notfound) then
242 close c;
243 raise no_data_found;
244 end if;
245 close c;
246
247 end INSERT_ROW;
248
249 procedure LOCK_ROW (
250 X_LOCATION_ID in NUMBER,
251 X_TERRITORY_CODE in VARCHAR2,
252 X_UNDEFINED_LOCATION_FLAG in VARCHAR2,
253 X_END_DATE in DATE,
254 X_LOCATION in VARCHAR2,
255 X_DESCRIPTION in VARCHAR2,
256 X_STATUS in VARCHAR2,
257 X_LOCATION_TYPE in VARCHAR2,
258 X_COUNTRY in VARCHAR2,
259 X_STATE_PROVINCE_ID in NUMBER,
260 X_COUNTY_ID in NUMBER,
261 X_CITY_LOCALITY_ID in NUMBER,
262 X_LANGUAGE in VARCHAR2,
263 X_SOURCE_LANG in VARCHAR2
264 ) is
265 cursor c is select
266 TERRITORY_CODE,
267 UNDEFINED_LOCATION_FLAG,
268 END_DATE,
269 STATUS,
270 LOCATION_TYPE,
271 COUNTRY,
272 STATE_PROVINCE_ID,
273 COUNTY_ID,
274 CITY_LOCALITY_ID
275 from AP_POL_LOCATIONS_B
276 where LOCATION_ID = X_LOCATION_ID
277 for update of LOCATION_ID nowait;
278 recinfo c%rowtype;
279
280 cursor c1 is select
281 LOCATION,
282 DESCRIPTION,
283 decode(LANGUAGE, NVL(X_SOURCE_LANG,userenv('LANG')), 'Y', 'N') BASELANG
284 from AP_POL_LOCATIONS_TL
285 where LOCATION_ID = X_LOCATION_ID
286 and NVL(X_SOURCE_LANG,userenv('LANG')) in (LANGUAGE, SOURCE_LANG)
287 for update of LOCATION_ID nowait;
288 begin
289 open c;
290 fetch c into recinfo;
291 if (c%notfound) then
292 close c;
293 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
294 app_exception.raise_exception;
295 end if;
296 close c;
297 if ( ((recinfo.TERRITORY_CODE = X_TERRITORY_CODE)
298 OR ((recinfo.TERRITORY_CODE is null) AND (X_TERRITORY_CODE is null)))
299 AND ((recinfo.UNDEFINED_LOCATION_FLAG = X_UNDEFINED_LOCATION_FLAG)
300 OR ((recinfo.UNDEFINED_LOCATION_FLAG is null) AND (X_UNDEFINED_LOCATION_FLAG is null)))
301 AND ((recinfo.STATUS = X_STATUS)
302 OR ((recinfo.STATUS is null) AND (X_STATUS is null)))
303 AND ((recinfo.LOCATION_TYPE = X_LOCATION_TYPE)
304 OR ((recinfo.LOCATION_TYPE is null) AND (X_LOCATION_TYPE is null)))
305 AND ((recinfo.COUNTRY = X_COUNTRY)
306 OR ((recinfo.COUNTRY is null) AND (X_COUNTRY is null)))
307 AND ((recinfo.STATE_PROVINCE_ID = X_STATE_PROVINCE_ID)
308 OR ((recinfo.STATE_PROVINCE_ID is null) AND (X_STATE_PROVINCE_ID is null)))
309 AND ((recinfo.COUNTY_ID = X_COUNTY_ID)
310 OR ((recinfo.COUNTY_ID is null) AND (X_COUNTY_ID is null)))
311 AND ((recinfo.CITY_LOCALITY_ID = X_CITY_LOCALITY_ID)
312 OR ((recinfo.CITY_LOCALITY_ID is null) AND (X_CITY_LOCALITY_ID is null)))
313 AND ((recinfo.END_DATE = X_END_DATE)
314 OR ((recinfo.END_DATE is null) AND (X_END_DATE is null)))
315 ) then
316 null;
317 else
318 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
319 app_exception.raise_exception;
320 end if;
321
322 for tlinfo in c1 loop
323 if (tlinfo.BASELANG = 'Y') then
324 if ( ((tlinfo.LOCATION = X_LOCATION)
325 OR ((tlinfo.LOCATION is null) AND (X_LOCATION is null)))
326 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
327 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
328 ) then
329 null;
330 else
331 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
332 app_exception.raise_exception;
333 end if;
334 end if;
335 end loop;
336 return;
337 end LOCK_ROW;
338
339 procedure UPDATE_ROW (
340 X_LOCATION_ID in NUMBER,
341 X_TERRITORY_CODE in VARCHAR2,
342 X_UNDEFINED_LOCATION_FLAG in VARCHAR2,
343 X_END_DATE in DATE,
344 X_LOCATION in VARCHAR2,
345 X_DESCRIPTION in VARCHAR2,
346 X_STATUS in VARCHAR2,
347 X_LOCATION_TYPE in VARCHAR2,
348 X_COUNTRY in VARCHAR2,
349 X_STATE_PROVINCE_ID in NUMBER,
350 X_COUNTY_ID in NUMBER,
351 X_CITY_LOCALITY_ID in NUMBER,
352 X_LAST_UPDATE_DATE in DATE,
353 X_LAST_UPDATED_BY in NUMBER,
354 X_LAST_UPDATE_LOGIN in NUMBER,
355 X_LANGUAGE in VARCHAR2,
356 X_SOURCE_LANG in VARCHAR2) is
357 begin
358 update AP_POL_LOCATIONS_B set
359 TERRITORY_CODE = X_TERRITORY_CODE,
360 UNDEFINED_LOCATION_FLAG = X_UNDEFINED_LOCATION_FLAG,
361 END_DATE = X_END_DATE,
362 STATUS = X_STATUS,
363 LOCATION_TYPE = X_LOCATION_TYPE,
364 COUNTRY = X_COUNTRY,
365 STATE_PROVINCE_ID = X_STATE_PROVINCE_ID,
366 COUNTY_ID = X_COUNTY_ID,
367 CITY_LOCALITY_ID = X_CITY_LOCALITY_ID,
368 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
369 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
370 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
371 where LOCATION_ID = X_LOCATION_ID;
372
373 if (sql%notfound) then
374 raise no_data_found;
375 end if;
376
377 update AP_POL_LOCATIONS_TL set
378 LOCATION = X_LOCATION,
379 DESCRIPTION = X_DESCRIPTION,
380 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
381 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
382 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
383 SOURCE_LANG = NVL(X_SOURCE_LANG,userenv('LANG'))
384 where LOCATION_ID = X_LOCATION_ID
385 and NVL(X_SOURCE_LANG,userenv('LANG')) in (LANGUAGE, SOURCE_LANG);
389 raise no_data_found;
386
387
388 if (sql%notfound) then
390 end if;
391 end UPDATE_ROW;
392
393 procedure ADD_LANGUAGE (X_LANGUAGE in VARCHAR2,
394 X_SOURCE_LANG in VARCHAR2) is
395 begin
396 delete from AP_POL_LOCATIONS_TL T
397 where not exists
398 (select NULL
399 from AP_POL_LOCATIONS_B B
403 update AP_POL_LOCATIONS_TL T set (
400 where B.LOCATION_ID = T.LOCATION_ID
401 );
402
404 LOCATION,
405 DESCRIPTION
406 ) = (select
407 B.LOCATION,
408 B.DESCRIPTION
409 from AP_POL_LOCATIONS_TL B
410 where B.LOCATION_ID = T.LOCATION_ID
411 and B.LANGUAGE = T.SOURCE_LANG)
412 where (
413 T.LOCATION_ID,
414 T.LANGUAGE
415 ) in (select
416 SUBT.LOCATION_ID,
417 SUBT.LANGUAGE
418 from AP_POL_LOCATIONS_TL SUBB, AP_POL_LOCATIONS_TL SUBT
419 where SUBB.LOCATION_ID = SUBT.LOCATION_ID
420 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
421 and (SUBB.LOCATION <> SUBT.LOCATION
422 or (SUBB.LOCATION is null and SUBT.LOCATION is not null)
423 or (SUBB.LOCATION is not null and SUBT.LOCATION is null)
424 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
425 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
426 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
427 ));
428
429 insert into AP_POL_LOCATIONS_TL (
430 LOCATION_ID,
431 LOCATION,
432 DESCRIPTION,
433 CREATION_DATE,
434 CREATED_BY,
435 LAST_UPDATE_LOGIN,
436 LAST_UPDATE_DATE,
437 LAST_UPDATED_BY,
438 LANGUAGE,
439 SOURCE_LANG
440 ) select
441 B.LOCATION_ID,
442 B.LOCATION,
443 B.DESCRIPTION,
444 B.CREATION_DATE,
445 B.CREATED_BY,
446 B.LAST_UPDATE_LOGIN,
447 B.LAST_UPDATE_DATE,
448 B.LAST_UPDATED_BY,
449 L.LANGUAGE_CODE,
450 B.SOURCE_LANG
451 from AP_POL_LOCATIONS_TL B, FND_LANGUAGES L
452 where L.INSTALLED_FLAG in ('I', 'B')
453 and B.LANGUAGE = NVL(X_SOURCE_LANG,userenv('LANG'))
454 and not exists
455 (select NULL
456 from AP_POL_LOCATIONS_TL T
457 where T.LOCATION_ID = B.LOCATION_ID
458 and T.LANGUAGE = L.LANGUAGE_CODE);
459 end ADD_LANGUAGE;
460
461 end AP_POL_LOCATIONS_PKG;
[Home] [Help]