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