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