[Home] [Help]
PACKAGE BODY: APPS.FEM_GEOGRAPHY_PKG
Source
1 package body FEM_GEOGRAPHY_PKG as
2 /* $Header: fem_geogrphy_pkb.plb 120.1 2005/06/27 13:27:58 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_GEOGRAPHY_ID in NUMBER,
6 X_VALUE_SET_ID in NUMBER,
7 X_DIMENSION_GROUP_ID in NUMBER,
8 X_GEOGRAPHY_DISPLAY_CODE in VARCHAR2,
9 X_ENABLED_FLAG in VARCHAR2,
10 X_PERSONAL_FLAG in VARCHAR2,
11 X_OBJECT_VERSION_NUMBER in NUMBER,
12 X_READ_ONLY_FLAG in VARCHAR2,
13 X_GEOGRAPHY_NAME in VARCHAR2,
14 X_DESCRIPTION in VARCHAR2,
15 X_CREATION_DATE in DATE,
16 X_CREATED_BY in NUMBER,
17 X_LAST_UPDATE_DATE in DATE,
18 X_LAST_UPDATED_BY in NUMBER,
19 X_LAST_UPDATE_LOGIN in NUMBER
20 ) is
21 cursor C is select ROWID from FEM_GEOGRAPHY_B
22 where GEOGRAPHY_ID = X_GEOGRAPHY_ID
23 and VALUE_SET_ID = X_VALUE_SET_ID
24 ;
25 begin
26 insert into FEM_GEOGRAPHY_B (
27 GEOGRAPHY_ID,
28 VALUE_SET_ID,
29 DIMENSION_GROUP_ID,
30 GEOGRAPHY_DISPLAY_CODE,
31 ENABLED_FLAG,
32 PERSONAL_FLAG,
33 OBJECT_VERSION_NUMBER,
34 READ_ONLY_FLAG,
35 CREATION_DATE,
36 CREATED_BY,
37 LAST_UPDATE_DATE,
38 LAST_UPDATED_BY,
39 LAST_UPDATE_LOGIN
40 ) values (
41 X_GEOGRAPHY_ID,
42 X_VALUE_SET_ID,
43 X_DIMENSION_GROUP_ID,
44 X_GEOGRAPHY_DISPLAY_CODE,
45 X_ENABLED_FLAG,
46 X_PERSONAL_FLAG,
47 X_OBJECT_VERSION_NUMBER,
48 X_READ_ONLY_FLAG,
49 X_CREATION_DATE,
50 X_CREATED_BY,
51 X_LAST_UPDATE_DATE,
52 X_LAST_UPDATED_BY,
53 X_LAST_UPDATE_LOGIN
54 );
55
56 insert into FEM_GEOGRAPHY_TL (
57 GEOGRAPHY_ID,
58 VALUE_SET_ID,
59 GEOGRAPHY_NAME,
60 DESCRIPTION,
61 CREATION_DATE,
62 CREATED_BY,
63 LAST_UPDATED_BY,
64 LAST_UPDATE_DATE,
65 LAST_UPDATE_LOGIN,
66 LANGUAGE,
67 SOURCE_LANG
68 ) select
69 X_GEOGRAPHY_ID,
70 X_VALUE_SET_ID,
71 X_GEOGRAPHY_NAME,
72 X_DESCRIPTION,
73 X_CREATION_DATE,
74 X_CREATED_BY,
75 X_LAST_UPDATED_BY,
76 X_LAST_UPDATE_DATE,
77 X_LAST_UPDATE_LOGIN,
78 L.LANGUAGE_CODE,
79 userenv('LANG')
80 from FND_LANGUAGES L
81 where L.INSTALLED_FLAG in ('I', 'B')
82 and not exists
83 (select NULL
84 from FEM_GEOGRAPHY_TL T
85 where T.GEOGRAPHY_ID = X_GEOGRAPHY_ID
86 and T.VALUE_SET_ID = X_VALUE_SET_ID
87 and T.LANGUAGE = L.LANGUAGE_CODE);
88
89 open c;
90 fetch c into X_ROWID;
91 if (c%notfound) then
92 close c;
93 raise no_data_found;
94 end if;
95 close c;
96
97 end INSERT_ROW;
98
99 procedure LOCK_ROW (
100 X_GEOGRAPHY_ID in NUMBER,
101 X_VALUE_SET_ID in NUMBER,
102 X_DIMENSION_GROUP_ID in NUMBER,
103 X_GEOGRAPHY_DISPLAY_CODE in VARCHAR2,
104 X_ENABLED_FLAG in VARCHAR2,
105 X_PERSONAL_FLAG in VARCHAR2,
106 X_OBJECT_VERSION_NUMBER in NUMBER,
107 X_READ_ONLY_FLAG in VARCHAR2,
108 X_GEOGRAPHY_NAME in VARCHAR2,
109 X_DESCRIPTION in VARCHAR2
110 ) is
111 cursor c is select
112 DIMENSION_GROUP_ID,
113 GEOGRAPHY_DISPLAY_CODE,
114 ENABLED_FLAG,
115 PERSONAL_FLAG,
116 OBJECT_VERSION_NUMBER,
117 READ_ONLY_FLAG
118 from FEM_GEOGRAPHY_B
119 where GEOGRAPHY_ID = X_GEOGRAPHY_ID
120 and VALUE_SET_ID = X_VALUE_SET_ID
121 for update of GEOGRAPHY_ID nowait;
122 recinfo c%rowtype;
123
124 cursor c1 is select
125 GEOGRAPHY_NAME,
126 DESCRIPTION,
127 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
128 from FEM_GEOGRAPHY_TL
129 where GEOGRAPHY_ID = X_GEOGRAPHY_ID
130 and VALUE_SET_ID = X_VALUE_SET_ID
131 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
132 for update of GEOGRAPHY_ID nowait;
133 begin
134 open c;
135 fetch c into recinfo;
136 if (c%notfound) then
137 close c;
138 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
139 app_exception.raise_exception;
140 end if;
141 close c;
142 if ( ((recinfo.DIMENSION_GROUP_ID = X_DIMENSION_GROUP_ID)
143 OR ((recinfo.DIMENSION_GROUP_ID is null) AND (X_DIMENSION_GROUP_ID is null)))
144 AND (recinfo.GEOGRAPHY_DISPLAY_CODE = X_GEOGRAPHY_DISPLAY_CODE)
145 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
146 AND (recinfo.PERSONAL_FLAG = X_PERSONAL_FLAG)
147 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
148 AND (recinfo.READ_ONLY_FLAG = X_READ_ONLY_FLAG)
149 ) then
150 null;
151 else
152 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
153 app_exception.raise_exception;
154 end if;
155
156 for tlinfo in c1 loop
157 if (tlinfo.BASELANG = 'Y') then
158 if ( (tlinfo.GEOGRAPHY_NAME = X_GEOGRAPHY_NAME)
159 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
160 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
161 ) then
162 null;
163 else
164 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
165 app_exception.raise_exception;
166 end if;
167 end if;
168 end loop;
169 return;
170 end LOCK_ROW;
171
172 procedure UPDATE_ROW (
173 X_GEOGRAPHY_ID in NUMBER,
174 X_VALUE_SET_ID in NUMBER,
175 X_DIMENSION_GROUP_ID in NUMBER,
176 X_GEOGRAPHY_DISPLAY_CODE in VARCHAR2,
177 X_ENABLED_FLAG in VARCHAR2,
178 X_PERSONAL_FLAG in VARCHAR2,
179 X_OBJECT_VERSION_NUMBER in NUMBER,
180 X_READ_ONLY_FLAG in VARCHAR2,
181 X_GEOGRAPHY_NAME in VARCHAR2,
182 X_DESCRIPTION in VARCHAR2,
183 X_LAST_UPDATE_DATE in DATE,
184 X_LAST_UPDATED_BY in NUMBER,
185 X_LAST_UPDATE_LOGIN in NUMBER
186 ) is
187 begin
188 update FEM_GEOGRAPHY_B set
189 DIMENSION_GROUP_ID = X_DIMENSION_GROUP_ID,
190 GEOGRAPHY_DISPLAY_CODE = X_GEOGRAPHY_DISPLAY_CODE,
191 ENABLED_FLAG = X_ENABLED_FLAG,
192 PERSONAL_FLAG = X_PERSONAL_FLAG,
193 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
194 READ_ONLY_FLAG = X_READ_ONLY_FLAG,
195 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
196 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
197 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
198 where GEOGRAPHY_ID = X_GEOGRAPHY_ID
199 and VALUE_SET_ID = X_VALUE_SET_ID;
200
201 if (sql%notfound) then
202 raise no_data_found;
203 end if;
204
205 update FEM_GEOGRAPHY_TL set
206 GEOGRAPHY_NAME = X_GEOGRAPHY_NAME,
207 DESCRIPTION = X_DESCRIPTION,
208 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
209 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
210 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
211 SOURCE_LANG = userenv('LANG')
212 where GEOGRAPHY_ID = X_GEOGRAPHY_ID
213 and VALUE_SET_ID = X_VALUE_SET_ID
214 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
215
216 if (sql%notfound) then
217 raise no_data_found;
218 end if;
219 end UPDATE_ROW;
220
221 procedure DELETE_ROW (
222 X_GEOGRAPHY_ID in NUMBER,
223 X_VALUE_SET_ID in NUMBER
224 ) is
225 begin
226 delete from FEM_GEOGRAPHY_TL
227 where GEOGRAPHY_ID = X_GEOGRAPHY_ID
228 and VALUE_SET_ID = X_VALUE_SET_ID;
229
230 if (sql%notfound) then
231 raise no_data_found;
232 end if;
233
234 delete from FEM_GEOGRAPHY_B
235 where GEOGRAPHY_ID = X_GEOGRAPHY_ID
236 and VALUE_SET_ID = X_VALUE_SET_ID;
237
238 if (sql%notfound) then
239 raise no_data_found;
240 end if;
241 end DELETE_ROW;
242
243 procedure ADD_LANGUAGE
244 is
245 begin
246 delete from FEM_GEOGRAPHY_TL T
247 where not exists
248 (select NULL
249 from FEM_GEOGRAPHY_B B
250 where B.GEOGRAPHY_ID = T.GEOGRAPHY_ID
251 and B.VALUE_SET_ID = T.VALUE_SET_ID
252 );
253
254 update FEM_GEOGRAPHY_TL T set (
255 GEOGRAPHY_NAME,
256 DESCRIPTION
257 ) = (select
258 B.GEOGRAPHY_NAME,
259 B.DESCRIPTION
260 from FEM_GEOGRAPHY_TL B
261 where B.GEOGRAPHY_ID = T.GEOGRAPHY_ID
262 and B.VALUE_SET_ID = T.VALUE_SET_ID
263 and B.LANGUAGE = T.SOURCE_LANG)
264 where (
265 T.GEOGRAPHY_ID,
266 T.VALUE_SET_ID,
267 T.LANGUAGE
268 ) in (select
269 SUBT.GEOGRAPHY_ID,
270 SUBT.VALUE_SET_ID,
271 SUBT.LANGUAGE
272 from FEM_GEOGRAPHY_TL SUBB, FEM_GEOGRAPHY_TL SUBT
273 where SUBB.GEOGRAPHY_ID = SUBT.GEOGRAPHY_ID
274 and SUBB.VALUE_SET_ID = SUBT.VALUE_SET_ID
275 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
276 and (SUBB.GEOGRAPHY_NAME <> SUBT.GEOGRAPHY_NAME
277 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
278 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
279 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
280 ));
281
282 insert into FEM_GEOGRAPHY_TL (
283 GEOGRAPHY_ID,
284 VALUE_SET_ID,
285 GEOGRAPHY_NAME,
286 DESCRIPTION,
287 CREATION_DATE,
288 CREATED_BY,
289 LAST_UPDATED_BY,
290 LAST_UPDATE_DATE,
291 LAST_UPDATE_LOGIN,
292 LANGUAGE,
293 SOURCE_LANG
294 ) select /*+ ORDERED */
295 B.GEOGRAPHY_ID,
296 B.VALUE_SET_ID,
297 B.GEOGRAPHY_NAME,
298 B.DESCRIPTION,
299 B.CREATION_DATE,
300 B.CREATED_BY,
301 B.LAST_UPDATED_BY,
302 B.LAST_UPDATE_DATE,
303 B.LAST_UPDATE_LOGIN,
304 L.LANGUAGE_CODE,
305 B.SOURCE_LANG
306 from FEM_GEOGRAPHY_TL B, FND_LANGUAGES L
307 where L.INSTALLED_FLAG in ('I', 'B')
308 and B.LANGUAGE = userenv('LANG')
309 and not exists
310 (select NULL
311 from FEM_GEOGRAPHY_TL T
312 where T.GEOGRAPHY_ID = B.GEOGRAPHY_ID
313 and T.VALUE_SET_ID = B.VALUE_SET_ID
314 and T.LANGUAGE = L.LANGUAGE_CODE);
315 end ADD_LANGUAGE;
316 PROCEDURE TRANSLATE_ROW(
317 x_GEOGRAPHY_ID in number,
318 x_VALUE_SET_ID in number,
319 x_owner in varchar2,
320 x_last_update_date in varchar2,
321 x_GEOGRAPHY_NAME in varchar2,
322 x_description in varchar2,
323 x_custom_mode in varchar2) is
324
325 owner_id number;
326 ludate date;
327 row_id varchar2(64);
328 f_luby number; -- entity owner in file
329 f_ludate date; -- entity update date in file
330 db_luby number; -- entity owner in db
331 db_ludate date; -- entity update date in db
332 begin
333
334
335 -- Translate owner to file_last_updated_by
336 f_luby := fnd_load_util.owner_id(x_owner);
337
338 -- Translate char last_update_date to date
339 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
340 begin
341 select LAST_UPDATED_BY, LAST_UPDATE_DATE
342 into db_luby, db_ludate
343 from FEM_GEOGRAPHY_TL
344 where GEOGRAPHY_ID = x_GEOGRAPHY_ID
345 and LANGUAGE = userenv('LANG');
346
347 -- Test for customization and version
348 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
349 db_ludate, x_custom_mode)) then
350 -- Update translations for this language
351 update FEM_GEOGRAPHY_TL set
352 GEOGRAPHY_NAME = decode(x_GEOGRAPHY_NAME,
353 fnd_load_util.null_value, null, -- Real null
354 null, x_GEOGRAPHY_NAME, -- No change
355 x_GEOGRAPHY_NAME),
356 DESCRIPTION = nvl(x_description, DESCRIPTION),
357 LAST_UPDATE_DATE = f_ludate,
358 LAST_UPDATED_BY = f_luby,
359 LAST_UPDATE_LOGIN = 0,
360 SOURCE_LANG = userenv('LANG')
361 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
362 and GEOGRAPHY_ID = x_GEOGRAPHY_ID;
363 end if;
364 exception
365 when no_data_found then
366 -- Do not insert missing translations, skip this row
367 null;
368 end;
369 end TRANSLATE_ROW;
370
371
372 end FEM_GEOGRAPHY_PKG;