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