[Home] [Help]
PACKAGE BODY: APPS.XDP_FE_SW_GEN_LOOKUP_PKG
Source
1 package body XDP_FE_SW_GEN_LOOKUP_PKG as
2 /* $Header: XDPFEGLB.pls 120.2 2005/07/15 01:18:30 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_FE_SW_GEN_LOOKUP_ID in NUMBER,
6 X_FETYPE_ID in NUMBER,
7 X_SW_GENERIC in VARCHAR2,
8 X_ADAPTER_TYPE in VARCHAR2,
9 X_SW_START_PROC in VARCHAR2,
10 X_SW_EXIT_PROC in VARCHAR2,
11 X_DISPLAY_NAME in VARCHAR2,
12 X_DESCRIPTION in VARCHAR2,
13 X_CREATION_DATE in DATE,
14 X_CREATED_BY in NUMBER,
15 X_LAST_UPDATE_DATE in DATE,
16 X_LAST_UPDATED_BY in NUMBER,
17 X_LAST_UPDATE_LOGIN in NUMBER
18 ) is
19 cursor C is select ROWID from XDP_FE_SW_GEN_LOOKUP
20 where FE_SW_GEN_LOOKUP_ID = X_FE_SW_GEN_LOOKUP_ID
21 ;
22 begin
23 insert into XDP_FE_SW_GEN_LOOKUP (
24 FE_SW_GEN_LOOKUP_ID,
25 FETYPE_ID,
26 SW_GENERIC,
27 ADAPTER_TYPE,
28 SW_START_PROC,
29 SW_EXIT_PROC,
30 CREATION_DATE,
31 CREATED_BY,
32 LAST_UPDATE_DATE,
33 LAST_UPDATED_BY,
34 LAST_UPDATE_LOGIN
35 ) values (
36 X_FE_SW_GEN_LOOKUP_ID,
37 X_FETYPE_ID,
38 X_SW_GENERIC,
39 X_ADAPTER_TYPE,
40 X_SW_START_PROC,
41 X_SW_EXIT_PROC,
42 X_CREATION_DATE,
43 X_CREATED_BY,
44 X_LAST_UPDATE_DATE,
45 X_LAST_UPDATED_BY,
46 X_LAST_UPDATE_LOGIN
47 );
48
49 insert into XDP_FE_SW_GEN_LOOKUP_TL (
50 FE_SW_GEN_LOOKUP_ID,
51 DISPLAY_NAME,
52 DESCRIPTION,
53 CREATED_BY,
54 CREATION_DATE,
55 LAST_UPDATED_BY,
56 LAST_UPDATE_DATE,
57 LAST_UPDATE_LOGIN,
58 LANGUAGE,
59 SOURCE_LANG
60 ) select
61 X_FE_SW_GEN_LOOKUP_ID,
62 X_DISPLAY_NAME,
63 X_DESCRIPTION,
64 X_CREATED_BY,
65 X_CREATION_DATE,
66 X_LAST_UPDATED_BY,
67 X_LAST_UPDATE_DATE,
68 X_LAST_UPDATE_LOGIN,
69 L.LANGUAGE_CODE,
70 userenv('LANG')
71 from FND_LANGUAGES L
72 where L.INSTALLED_FLAG in ('I', 'B')
73 and not exists
74 (select NULL
75 from XDP_FE_SW_GEN_LOOKUP_TL T
76 where T.FE_SW_GEN_LOOKUP_ID = X_FE_SW_GEN_LOOKUP_ID
77 and T.LANGUAGE = L.LANGUAGE_CODE);
78
79 open c;
80 fetch c into X_ROWID;
81 if (c%notfound) then
82 close c;
83 raise no_data_found;
84 end if;
85 close c;
86
87 end INSERT_ROW;
88
89 procedure LOCK_ROW (
90 X_FE_SW_GEN_LOOKUP_ID in NUMBER,
91 X_FETYPE_ID in NUMBER,
92 X_SW_GENERIC in VARCHAR2,
93 X_ADAPTER_TYPE in VARCHAR2,
94 X_SW_START_PROC in VARCHAR2,
95 X_SW_EXIT_PROC in VARCHAR2,
96 X_DISPLAY_NAME in VARCHAR2,
97 X_DESCRIPTION in VARCHAR2
98 ) is
99 cursor c is select
100 FETYPE_ID,
101 SW_GENERIC,
102 ADAPTER_TYPE,
103 SW_START_PROC,
104 SW_EXIT_PROC
105 from XDP_FE_SW_GEN_LOOKUP
106 where FE_SW_GEN_LOOKUP_ID = X_FE_SW_GEN_LOOKUP_ID
107 for update of FE_SW_GEN_LOOKUP_ID nowait;
108 recinfo c%rowtype;
109
110 cursor c1 is select
111 DISPLAY_NAME,
112 DESCRIPTION,
113 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
114 from XDP_FE_SW_GEN_LOOKUP_TL
115 where FE_SW_GEN_LOOKUP_ID = X_FE_SW_GEN_LOOKUP_ID
116 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
117 for update of FE_SW_GEN_LOOKUP_ID nowait;
118 begin
119 open c;
120 fetch c into recinfo;
121 if (c%notfound) then
122 close c;
123 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
124 app_exception.raise_exception;
125 end if;
126 close c;
127 if ( (recinfo.FETYPE_ID = X_FETYPE_ID)
128 AND (recinfo.SW_GENERIC = X_SW_GENERIC)
129 AND (recinfo.ADAPTER_TYPE = X_ADAPTER_TYPE)
130 AND ((recinfo.SW_START_PROC = X_SW_START_PROC)
131 OR ((recinfo.SW_START_PROC is null) AND (X_SW_START_PROC is null)))
132 AND ((recinfo.SW_EXIT_PROC = X_SW_EXIT_PROC)
133 OR ((recinfo.SW_EXIT_PROC is null) AND (X_SW_EXIT_PROC is null)))
134 ) then
135 null;
136 else
137 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
138 app_exception.raise_exception;
139 end if;
140
141 for tlinfo in c1 loop
142 if (tlinfo.BASELANG = 'Y') then
143 if ( (tlinfo.DISPLAY_NAME = X_DISPLAY_NAME)
144 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
145 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
146 ) then
147 null;
148 else
149 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
150 app_exception.raise_exception;
151 end if;
152 end if;
153 end loop;
154 return;
155 end LOCK_ROW;
156
157 procedure UPDATE_ROW (
158 X_FE_SW_GEN_LOOKUP_ID in NUMBER,
159 X_FETYPE_ID in NUMBER,
160 X_SW_GENERIC in VARCHAR2,
161 X_ADAPTER_TYPE in VARCHAR2,
162 X_SW_START_PROC in VARCHAR2,
163 X_SW_EXIT_PROC in VARCHAR2,
164 X_DISPLAY_NAME in VARCHAR2,
165 X_DESCRIPTION in VARCHAR2,
166 X_LAST_UPDATE_DATE in DATE,
167 X_LAST_UPDATED_BY in NUMBER,
168 X_LAST_UPDATE_LOGIN in NUMBER
169 ) is
170 begin
171 update XDP_FE_SW_GEN_LOOKUP set
172 FETYPE_ID = X_FETYPE_ID,
173 SW_GENERIC = X_SW_GENERIC,
174 ADAPTER_TYPE = X_ADAPTER_TYPE,
175 SW_START_PROC = X_SW_START_PROC,
176 SW_EXIT_PROC = X_SW_EXIT_PROC,
177 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
178 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
179 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
180 where FE_SW_GEN_LOOKUP_ID = X_FE_SW_GEN_LOOKUP_ID;
181
182 if (sql%notfound) then
183 raise no_data_found;
184 end if;
185
186 update XDP_FE_SW_GEN_LOOKUP_TL set
187 DISPLAY_NAME = X_DISPLAY_NAME,
188 DESCRIPTION = X_DESCRIPTION,
189 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
190 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
191 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
192 SOURCE_LANG = userenv('LANG')
193 where FE_SW_GEN_LOOKUP_ID = X_FE_SW_GEN_LOOKUP_ID
194 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
195
196 if (sql%notfound) then
197 raise no_data_found;
198 end if;
199 end UPDATE_ROW;
200
201 procedure DELETE_ROW (
202 X_FE_SW_GEN_LOOKUP_ID in NUMBER
203 ) is
204 begin
205 delete from XDP_FE_SW_GEN_LOOKUP_TL
206 where FE_SW_GEN_LOOKUP_ID = X_FE_SW_GEN_LOOKUP_ID;
207
208 if (sql%notfound) then
209 raise no_data_found;
210 end if;
211
212 delete from XDP_FE_SW_GEN_LOOKUP
213 where FE_SW_GEN_LOOKUP_ID = X_FE_SW_GEN_LOOKUP_ID;
214
215 if (sql%notfound) then
216 raise no_data_found;
217 end if;
218 end DELETE_ROW;
219
220 procedure ADD_LANGUAGE
221 is
222 begin
223 delete from XDP_FE_SW_GEN_LOOKUP_TL T
224 where not exists
225 (select NULL
226 from XDP_FE_SW_GEN_LOOKUP B
227 where B.FE_SW_GEN_LOOKUP_ID = T.FE_SW_GEN_LOOKUP_ID
228 );
229
230 update XDP_FE_SW_GEN_LOOKUP_TL T set (
231 DISPLAY_NAME,
232 DESCRIPTION
233 ) = (select
234 B.DISPLAY_NAME,
235 B.DESCRIPTION
236 from XDP_FE_SW_GEN_LOOKUP_TL B
237 where B.FE_SW_GEN_LOOKUP_ID = T.FE_SW_GEN_LOOKUP_ID
238 and B.LANGUAGE = T.SOURCE_LANG)
239 where (
240 T.FE_SW_GEN_LOOKUP_ID,
241 T.LANGUAGE
242 ) in (select
243 SUBT.FE_SW_GEN_LOOKUP_ID,
244 SUBT.LANGUAGE
245 from XDP_FE_SW_GEN_LOOKUP_TL SUBB, XDP_FE_SW_GEN_LOOKUP_TL SUBT
246 where SUBB.FE_SW_GEN_LOOKUP_ID = SUBT.FE_SW_GEN_LOOKUP_ID
247 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
248 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
249 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
250 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
251 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
252 ));
253
254 insert into XDP_FE_SW_GEN_LOOKUP_TL (
255 FE_SW_GEN_LOOKUP_ID,
256 DISPLAY_NAME,
257 DESCRIPTION,
258 CREATED_BY,
259 CREATION_DATE,
260 LAST_UPDATED_BY,
261 LAST_UPDATE_DATE,
262 LAST_UPDATE_LOGIN,
263 LANGUAGE,
264 SOURCE_LANG
265 ) select
266 B.FE_SW_GEN_LOOKUP_ID,
267 B.DISPLAY_NAME,
268 B.DESCRIPTION,
269 B.CREATED_BY,
270 B.CREATION_DATE,
271 B.LAST_UPDATED_BY,
272 B.LAST_UPDATE_DATE,
273 B.LAST_UPDATE_LOGIN,
274 L.LANGUAGE_CODE,
275 B.SOURCE_LANG
276 from XDP_FE_SW_GEN_LOOKUP_TL B, FND_LANGUAGES L
277 where L.INSTALLED_FLAG in ('I', 'B')
278 and B.LANGUAGE = userenv('LANG')
279 and not exists
280 (select NULL
281 from XDP_FE_SW_GEN_LOOKUP_TL T
282 where T.FE_SW_GEN_LOOKUP_ID = B.FE_SW_GEN_LOOKUP_ID
283 and T.LANGUAGE = L.LANGUAGE_CODE);
284 end ADD_LANGUAGE;
285
286 procedure LOAD_ROW (
287 X_FE_SW_GEN_LOOKUP_ID in NUMBER,
288 X_FETYPE_ID in NUMBER,
289 X_SW_GENERIC in VARCHAR2,
290 X_ADAPTER_TYPE in VARCHAR2,
291 X_SW_START_PROC in VARCHAR2,
292 X_SW_EXIT_PROC in VARCHAR2,
293 X_DISPLAY_NAME in VARCHAR2,
294 X_DESCRIPTION in VARCHAR2,
295 X_OWNER in VARCHAR2) IS
296 begin
297
298 declare
299 user_id number := 0;
300 row_id varchar2(64);
301
302 begin
303
304 /* The following derivation has been replaced with the FND API. dputhiye 15-JUL-2005. R12 ATG "Seed Version by Date" Uptake */
305 --if (X_OWNER = 'SEED') then
306 -- user_id := 1;
307 --end if;
308 user_id := fnd_load_util.owner_id(X_OWNER);
309
310 XDP_FE_SW_GEN_LOOKUP_PKG.UPDATE_ROW (
311 X_FE_SW_GEN_LOOKUP_ID => X_FE_SW_GEN_LOOKUP_ID,
312 X_FETYPE_ID => X_FETYPE_ID,
313 X_SW_GENERIC => X_SW_GENERIC,
314 X_ADAPTER_TYPE => X_ADAPTER_TYPE,
315 X_SW_START_PROC => X_SW_START_PROC,
316 X_SW_EXIT_PROC => X_SW_EXIT_PROC,
317 X_DISPLAY_NAME => X_DISPLAY_NAME,
318 X_DESCRIPTION => X_DESCRIPTION,
319 X_LAST_UPDATE_DATE => sysdate,
320 X_LAST_UPDATED_BY => user_id,
321 X_LAST_UPDATE_LOGIN => 0);
322
323 exception
324 when NO_DATA_FOUND then
325 XDP_FE_SW_GEN_LOOKUP_PKG.INSERT_ROW (
326 X_ROWID => row_id,
327 X_FE_SW_GEN_LOOKUP_ID => X_FE_SW_GEN_LOOKUP_ID,
328 X_FETYPE_ID => X_FETYPE_ID,
329 X_SW_GENERIC => X_SW_GENERIC,
330 X_ADAPTER_TYPE => X_ADAPTER_TYPE,
331 X_SW_START_PROC => X_SW_START_PROC,
332 X_SW_EXIT_PROC => X_SW_EXIT_PROC,
333 X_DISPLAY_NAME => X_DISPLAY_NAME,
334 X_DESCRIPTION => X_DESCRIPTION,
335 X_CREATION_DATE => sysdate,
336 X_CREATED_BY => user_id,
337 X_LAST_UPDATE_DATE => sysdate,
338 X_LAST_UPDATED_BY => user_id,
339 X_LAST_UPDATE_LOGIN => 0);
340 end;
341 end LOAD_ROW;
342
343 procedure TRANSLATE_ROW (
344 X_FE_SW_GEN_LOOKUP_ID in NUMBER,
345 X_DISPLAY_NAME in VARCHAR2,
346 X_DESCRIPTION in VARCHAR2,
347 X_OWNER in VARCHAR2) IS
348
349 begin
350
351 -- only update rows that have not been altered by user
352
353 update XDP_FE_SW_GEN_LOOKUP_TL
354 set display_name = X_DISPLAY_NAME,
355 description = X_DESCRIPTION,
356 source_lang = userenv('LANG'),
357 last_update_date = sysdate,
358 --last_updated_by = decode(X_OWNER, 'SEED', 1, 0), /*dputhiye 15-JUL-2005. DECODE replaced with FND API.*/
359 last_updated_by = fnd_load_util.owner_id(X_OWNER),
360 last_update_login = 0
361 where fe_sw_gen_lookup_id = X_FE_SW_GEN_LOOKUP_ID
362 and userenv('LANG') in (language, source_lang);
363
364 end TRANSLATE_ROW;
365
366
367 end XDP_FE_SW_GEN_LOOKUP_PKG;