[Home] [Help]
PACKAGE BODY: APPS.XNP_GEO_AREAS_PKG
Source
1 package body XNP_GEO_AREAS_PKG as
2 /* $Header: XNPGEOAB.pls 120.3 2006/02/13 07:49:44 dputhiye ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_GEO_AREA_ID in NUMBER,
6 X_GEO_AREA_TYPE_CODE in VARCHAR2,
7 X_CODE in VARCHAR2,
8 X_ACTIVE_FLAG in VARCHAR2,
9 X_DISPLAY_NAME in VARCHAR2,
10 X_DESCRIPTION in VARCHAR2,
11 X_CREATION_DATE in DATE,
12 X_CREATED_BY in NUMBER,
13 X_LAST_UPDATE_DATE in DATE,
14 X_LAST_UPDATED_BY in NUMBER,
15 X_LAST_UPDATE_LOGIN in NUMBER
16 ) is
17 cursor C is select ROWID from XNP_GEO_AREAS_B
18 where GEO_AREA_ID = X_GEO_AREA_ID
19 ;
20 begin
21 insert into XNP_GEO_AREAS_B (
22 GEO_AREA_ID,
23 GEO_AREA_TYPE_CODE,
24 CODE,
25 ACTIVE_FLAG,
26 CREATION_DATE,
27 CREATED_BY,
28 LAST_UPDATE_DATE,
29 LAST_UPDATED_BY,
30 LAST_UPDATE_LOGIN
31 ) values (
32 X_GEO_AREA_ID,
33 X_GEO_AREA_TYPE_CODE,
34 X_CODE,
35 X_ACTIVE_FLAG,
36 X_CREATION_DATE,
37 X_CREATED_BY,
38 X_LAST_UPDATE_DATE,
39 X_LAST_UPDATED_BY,
40 X_LAST_UPDATE_LOGIN
41 );
42
43 insert into XNP_GEO_AREAS_TL (
44 GEO_AREA_ID,
45 DISPLAY_NAME,
46 DESCRIPTION,
47 CREATED_BY,
48 CREATION_DATE,
49 LAST_UPDATED_BY,
50 LAST_UPDATE_DATE,
51 LAST_UPDATE_LOGIN,
52 LANGUAGE,
53 SOURCE_LANG
54 ) select
55 X_GEO_AREA_ID,
56 X_DISPLAY_NAME,
57 X_DESCRIPTION,
58 X_CREATED_BY,
59 X_CREATION_DATE,
60 X_LAST_UPDATED_BY,
61 X_LAST_UPDATE_DATE,
62 X_LAST_UPDATE_LOGIN,
63 L.LANGUAGE_CODE,
64 userenv('LANG')
65 from FND_LANGUAGES L
66 where L.INSTALLED_FLAG in ('I', 'B')
67 and not exists
68 (select NULL
69 from XNP_GEO_AREAS_TL T
70 where T.GEO_AREA_ID = X_GEO_AREA_ID
71 and T.LANGUAGE = L.LANGUAGE_CODE);
72
73 open c;
74 fetch c into X_ROWID;
75 if (c%notfound) then
76 close c;
77 raise no_data_found;
78 end if;
79 close c;
80
81 end INSERT_ROW;
82
83 procedure LOCK_ROW (
84 X_GEO_AREA_ID in NUMBER,
85 X_GEO_AREA_TYPE_CODE in VARCHAR2,
86 X_CODE in VARCHAR2,
87 X_ACTIVE_FLAG in VARCHAR2,
88 X_DISPLAY_NAME in VARCHAR2,
89 X_DESCRIPTION in VARCHAR2
90 ) is
91 cursor c is select
92 GEO_AREA_TYPE_CODE,
93 CODE,
94 ACTIVE_FLAG
95 from XNP_GEO_AREAS_B
96 where GEO_AREA_ID = X_GEO_AREA_ID
97 for update of GEO_AREA_ID nowait;
98 recinfo c%rowtype;
99
100 cursor c1 is select
101 DISPLAY_NAME,
102 DESCRIPTION,
103 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
104 from XNP_GEO_AREAS_TL
105 where GEO_AREA_ID = X_GEO_AREA_ID
106 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
107 for update of GEO_AREA_ID nowait;
108 begin
109 open c;
110 fetch c into recinfo;
111 if (c%notfound) then
112 close c;
113 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
114 app_exception.raise_exception;
115 end if;
116 close c;
117 if ( (recinfo.GEO_AREA_TYPE_CODE = X_GEO_AREA_TYPE_CODE)
118 AND (recinfo.CODE = X_CODE)
119 AND (recinfo.ACTIVE_FLAG = X_ACTIVE_FLAG)
120 ) then
121 null;
122 else
123 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
124 app_exception.raise_exception;
125 end if;
126
127 for tlinfo in c1 loop
128 if (tlinfo.BASELANG = 'Y') then
129 if ( (tlinfo.DISPLAY_NAME = X_DISPLAY_NAME)
130 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
131 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
132 ) then
133 null;
134 else
135 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
136 app_exception.raise_exception;
137 end if;
138 end if;
139 end loop;
140 return;
141 end LOCK_ROW;
142
143 procedure UPDATE_ROW (
144 X_GEO_AREA_ID in NUMBER,
145 X_GEO_AREA_TYPE_CODE in VARCHAR2,
146 X_CODE in VARCHAR2,
147 X_ACTIVE_FLAG in VARCHAR2,
148 X_DISPLAY_NAME in VARCHAR2,
149 X_DESCRIPTION in VARCHAR2,
150 X_LAST_UPDATE_DATE in DATE,
151 X_LAST_UPDATED_BY in NUMBER,
152 X_LAST_UPDATE_LOGIN in NUMBER
153 ) is
154 begin
155 update XNP_GEO_AREAS_B set
156 GEO_AREA_TYPE_CODE = X_GEO_AREA_TYPE_CODE,
157 CODE = X_CODE,
158 ACTIVE_FLAG = X_ACTIVE_FLAG,
159 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
160 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
161 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
162 where GEO_AREA_ID = X_GEO_AREA_ID;
163
164 if (sql%notfound) then
165 raise no_data_found;
166 end if;
167
168 update XNP_GEO_AREAS_TL set
169 DISPLAY_NAME = X_DISPLAY_NAME,
170 DESCRIPTION = X_DESCRIPTION,
171 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
172 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
173 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
174 SOURCE_LANG = userenv('LANG')
175 where GEO_AREA_ID = X_GEO_AREA_ID
176 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
177
178 if (sql%notfound) then
179 raise no_data_found;
180 end if;
181 end UPDATE_ROW;
182
183 procedure DELETE_ROW (
184 X_GEO_AREA_ID in NUMBER
185 ) is
186 begin
187 delete from XNP_GEO_AREAS_TL
188 where GEO_AREA_ID = X_GEO_AREA_ID;
189
190 if (sql%notfound) then
191 raise no_data_found;
192 end if;
193
194 delete from XNP_GEO_AREAS_B
195 where GEO_AREA_ID = X_GEO_AREA_ID;
196
197 if (sql%notfound) then
198 raise no_data_found;
199 end if;
200 end DELETE_ROW;
201
202 procedure ADD_LANGUAGE
203 is
204 begin
205 delete from XNP_GEO_AREAS_TL T
206 where not exists
207 (select NULL
208 from XNP_GEO_AREAS_B B
209 where B.GEO_AREA_ID = T.GEO_AREA_ID
210 );
211
212 update XNP_GEO_AREAS_TL T set (
213 DISPLAY_NAME,
214 DESCRIPTION
215 ) = (select
216 B.DISPLAY_NAME,
217 B.DESCRIPTION
218 from XNP_GEO_AREAS_TL B
219 where B.GEO_AREA_ID = T.GEO_AREA_ID
220 and B.LANGUAGE = T.SOURCE_LANG)
221 where (
222 T.GEO_AREA_ID,
223 T.LANGUAGE
224 ) in (select
225 SUBT.GEO_AREA_ID,
226 SUBT.LANGUAGE
227 from XNP_GEO_AREAS_TL SUBB, XNP_GEO_AREAS_TL SUBT
228 where SUBB.GEO_AREA_ID = SUBT.GEO_AREA_ID
229 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
230 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
231 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
232 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
233 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
234 ));
235
236 insert into XNP_GEO_AREAS_TL (
237 GEO_AREA_ID,
238 DISPLAY_NAME,
239 DESCRIPTION,
240 CREATED_BY,
241 CREATION_DATE,
242 LAST_UPDATED_BY,
243 LAST_UPDATE_DATE,
244 LAST_UPDATE_LOGIN,
245 LANGUAGE,
246 SOURCE_LANG
247 ) select
248 B.GEO_AREA_ID,
249 B.DISPLAY_NAME,
250 B.DESCRIPTION,
251 B.CREATED_BY,
252 B.CREATION_DATE,
253 B.LAST_UPDATED_BY,
254 B.LAST_UPDATE_DATE,
255 B.LAST_UPDATE_LOGIN,
256 L.LANGUAGE_CODE,
257 B.SOURCE_LANG
258 from XNP_GEO_AREAS_TL B, FND_LANGUAGES L
259 where L.INSTALLED_FLAG in ('I', 'B')
260 and B.LANGUAGE = userenv('LANG')
261 and not exists
262 (select NULL
263 from XNP_GEO_AREAS_TL T
264 where T.GEO_AREA_ID = B.GEO_AREA_ID
265 and T.LANGUAGE = L.LANGUAGE_CODE);
266 end ADD_LANGUAGE;
267 procedure LOAD_ROW (
268 X_CODE in VARCHAR2,
269 X_GEO_AREA_TYPE_CODE in VARCHAR2,
270 X_ACTIVE_FLAG in VARCHAR2,
271 X_DISPLAY_NAME in VARCHAR2,
272 X_DESCRIPTION in VARCHAR2,
273 X_OWNER in VARCHAR2) IS
274 BEGIN
275 DECLARE
276 l_user_id NUMBER := 0;
277 l_row_id varchar2(64);
278 l_geo_area_id NUMBER;
279 BEGIN
280 -- BUG FIX 1647105
281 -- Cannot assume the gea_area_id for WORLD will be 0
282 -- IF X_CODE <> 'WORLD' THEN
283 SELECT geo_area_id INTO l_geo_area_id
284 FROM xnp_geo_areas_b
285 WHERE geo_area_type_code = X_GEO_AREA_TYPE_CODE
286 AND code = X_CODE;
287 -- ELSE
288 -- l_geo_area_id := 0;
289 -- END IF;
290
291 /*The following derivation has been replaced with the FND API. */
292 /*dputhiye 19-JUL-2005. R12 ATG Seed Version by Date Uptake */
293 --IF (X_OWNER = 'SEED') THEN
294 -- l_user_id := 1;
295 --END IF;
296 l_user_id := fnd_load_util.owner_id(X_OWNER);
297
298
299 XNP_GEO_AREAS_PKG.UPDATE_ROW (
300 X_GEO_AREA_ID => l_geo_area_id,
301 X_GEO_AREA_TYPE_CODE => X_GEO_AREA_TYPE_CODE,
302 X_CODE => X_CODE,
303 X_ACTIVE_FLAG => X_ACTIVE_FLAG,
304 X_DISPLAY_NAME => X_DISPLAY_NAME,
305 X_DESCRIPTION => X_DESCRIPTION,
306 X_LAST_UPDATE_DATE => sysdate,
307 X_LAST_UPDATED_BY => l_user_id,
308 X_LAST_UPDATE_LOGIN => 0);
309 EXCEPTION
310 WHEN NO_DATA_FOUND THEN
311 DECLARE
312 l_geo_area_id NUMBER;
313 BEGIN
314 IF X_CODE <> 'WORLD' THEN
315 SELECT XNP_GEO_AREAS_B_S.NEXTVAL
316 INTO l_geo_area_id
317 FROM dual;
318 ELSE
319 l_geo_area_id := 0;
320 END IF;
321 XNP_GEO_AREAS_PKG.INSERT_ROW (
322 X_ROWID => l_row_id,
323 X_GEO_AREA_ID => l_geo_area_id,
324 X_GEO_AREA_TYPE_CODE => X_GEO_AREA_TYPE_CODE,
325 X_CODE => X_CODE,
326 X_ACTIVE_FLAG => X_ACTIVE_FLAG,
327 X_DISPLAY_NAME => X_DISPLAY_NAME,
328 X_DESCRIPTION => X_DESCRIPTION,
329 X_CREATION_DATE => sysdate,
330 X_CREATED_BY => l_user_id,
331 X_LAST_UPDATE_DATE => sysdate,
332 X_LAST_UPDATED_BY => l_user_id,
333 X_LAST_UPDATE_LOGIN => 0);
334 END;
335 END;
336 END LOAD_ROW;
337 procedure TRANSLATE_ROW (
338 X_CODE in VARCHAR2,
339 X_GEO_AREA_TYPE_CODE in VARCHAR2,
340 X_DISPLAY_NAME in VARCHAR2,
341 X_DESCRIPTION in VARCHAR2,
342 X_OWNER in VARCHAR2) IS
343 l_geo_area_id NUMBER;
344 BEGIN
345
346 -- BUG FIX 1647105
347 -- Cannot assume the gea_area_id for WORLD will be 0
348 -- IF X_CODE <> 'WORLD' THEN
349 SELECT geo_area_id INTO l_geo_area_id
350 FROM xnp_geo_areas_b
351 WHERE geo_area_type_code = X_GEO_AREA_TYPE_CODE
352 AND code = X_CODE;
353 -- ELSE
354 -- l_geo_area_id := 0;
355 -- END IF;
356
357 -- Only update rows which have not been altered by user
358 UPDATE XNP_GEO_AREAS_TL
359 SET description = X_DESCRIPTION,
360 display_name = X_DISPLAY_NAME,
361 source_lang = userenv('LANG'),
362 last_update_date = sysdate,
363 --last_updated_by = decode(X_OWNER, 'SEED', 1, 0), /*dputhiye 19-JUL-2005. DECODE replaced with FND API.*/
364 last_updated_by = fnd_load_util.owner_id(X_OWNER),
365 last_update_login = 0
366 WHERE geo_area_id = l_geo_area_id
367 AND userenv('LANG') IN (language, source_lang);
368 END TRANSLATE_ROW;
369 end XNP_GEO_AREAS_PKG;