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