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