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