[Home] [Help]
PACKAGE BODY: APPS.IEC_P_VDU_PORT_PARAMS_PKG
Source
1 package body IEC_P_VDU_PORT_PARAMS_PKG as
2 /* $Header: IECHVPPB.pls 115.10 2003/08/22 20:41:50 hhuang ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_VDU_PORT_PARAM_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_VDU_TYPE_ID in NUMBER,
8 X_MIDDLEWARE_TYPE_ID in NUMBER,
9 X_PARAM_CODE in VARCHAR2,
10 X_VALUE_TYPE in VARCHAR2,
11 X_VALUE_LENGTH in VARCHAR2,
12 X_AUTO_GEN_FLAG in VARCHAR2,
13 X_PARAM_NAME in VARCHAR2,
14 X_PARAM_DESCRIPTION in VARCHAR2,
15 X_CREATION_DATE in DATE,
16 X_CREATED_BY in NUMBER,
17 X_LAST_UPDATE_DATE in DATE,
18 X_LAST_UPDATED_BY in NUMBER,
19 X_LAST_UPDATE_LOGIN in NUMBER
20 ) is
21 cursor C is select ROWID from IEC_P_VDU_PORT_PARAMS_B
22 where VDU_PORT_PARAM_ID = X_VDU_PORT_PARAM_ID
23 ;
24 begin
25 insert into IEC_P_VDU_PORT_PARAMS_B (
26 VDU_PORT_PARAM_ID,
27 OBJECT_VERSION_NUMBER,
28 VDU_TYPE_ID,
29 MIDDLEWARE_TYPE_ID,
30 PARAM_CODE,
31 VALUE_TYPE,
32 VALUE_LENGTH,
33 AUTO_GEN_FLAG,
34 CREATION_DATE,
35 CREATED_BY,
36 LAST_UPDATE_DATE,
37 LAST_UPDATED_BY,
38 LAST_UPDATE_LOGIN
39 ) values (
40 X_VDU_PORT_PARAM_ID,
41 X_OBJECT_VERSION_NUMBER,
42 X_VDU_TYPE_ID,
43 X_MIDDLEWARE_TYPE_ID,
44 X_PARAM_CODE,
45 X_VALUE_TYPE,
46 X_VALUE_LENGTH,
47 X_AUTO_GEN_FLAG,
48 X_CREATION_DATE,
49 X_CREATED_BY,
50 X_LAST_UPDATE_DATE,
51 X_LAST_UPDATED_BY,
52 X_LAST_UPDATE_LOGIN
53 );
54
55 insert into IEC_P_VDU_PORT_PARAMS_TL (
56 VDU_PORT_PARAM_ID,
57 CREATED_BY,
58 CREATION_DATE,
59 LAST_UPDATED_BY,
60 LAST_UPDATE_DATE,
61 LAST_UPDATE_LOGIN,
62 PARAM_NAME,
63 PARAM_DESCRIPTION,
64 LANGUAGE,
65 SOURCE_LANG
66 ) select
67 X_VDU_PORT_PARAM_ID,
68 X_CREATED_BY,
69 X_CREATION_DATE,
70 X_LAST_UPDATED_BY,
71 X_LAST_UPDATE_DATE,
72 X_LAST_UPDATE_LOGIN,
73 X_PARAM_NAME,
74 X_PARAM_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 IEC_P_VDU_PORT_PARAMS_TL T
82 where T.VDU_PORT_PARAM_ID = X_VDU_PORT_PARAM_ID
83 and T.LANGUAGE = L.LANGUAGE_CODE);
84
85 open c;
86 fetch c into X_ROWID;
87 if (c%notfound) then
88 close c;
89 raise no_data_found;
90 end if;
91 close c;
92
93 end INSERT_ROW;
94
95 procedure LOCK_ROW (
96 X_VDU_PORT_PARAM_ID in NUMBER,
97 X_OBJECT_VERSION_NUMBER in NUMBER,
98 X_VDU_TYPE_ID in NUMBER,
99 X_MIDDLEWARE_TYPE_ID in NUMBER,
100 X_PARAM_CODE in VARCHAR2,
101 X_VALUE_TYPE in VARCHAR2,
102 X_VALUE_LENGTH in VARCHAR2,
103 X_AUTO_GEN_FLAG in VARCHAR2,
104 X_PARAM_NAME in VARCHAR2,
105 X_PARAM_DESCRIPTION in VARCHAR2
106 ) is
107 cursor c is select
108 OBJECT_VERSION_NUMBER,
109 VDU_TYPE_ID,
110 MIDDLEWARE_TYPE_ID,
111 PARAM_CODE,
112 VALUE_TYPE,
113 VALUE_LENGTH,
114 AUTO_GEN_FLAG
115 from IEC_P_VDU_PORT_PARAMS_B
116 where VDU_PORT_PARAM_ID = X_VDU_PORT_PARAM_ID
117 for update of VDU_PORT_PARAM_ID nowait;
118 recinfo c%rowtype;
119
120 cursor c1 is select
121 PARAM_NAME,
122 PARAM_DESCRIPTION,
123 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
124 from IEC_P_VDU_PORT_PARAMS_TL
125 where VDU_PORT_PARAM_ID = X_VDU_PORT_PARAM_ID
126 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
127 for update of VDU_PORT_PARAM_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.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
138 AND (recinfo.VDU_TYPE_ID = X_VDU_TYPE_ID)
139 AND ((recinfo.MIDDLEWARE_TYPE_ID = X_MIDDLEWARE_TYPE_ID)
140 OR ((recinfo.MIDDLEWARE_TYPE_ID is null) AND (X_MIDDLEWARE_TYPE_ID is null)))
141 AND (recinfo.PARAM_CODE = X_PARAM_CODE)
142 AND ((recinfo.VALUE_TYPE = X_VALUE_TYPE)
143 OR ((recinfo.VALUE_TYPE is null) AND (X_VALUE_TYPE is null)))
144 AND ((recinfo.VALUE_LENGTH = X_VALUE_LENGTH)
145 OR ((recinfo.VALUE_LENGTH is null) AND (X_VALUE_LENGTH is null)))
146 AND ((recinfo.AUTO_GEN_FLAG = X_AUTO_GEN_FLAG)
147 OR ((recinfo.AUTO_GEN_FLAG is null) AND (X_AUTO_GEN_FLAG is null)))
148 ) then
149 null;
150 else
151 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
152 app_exception.raise_exception;
153 end if;
154
155 for tlinfo in c1 loop
156 if (tlinfo.BASELANG = 'Y') then
157 if ( (tlinfo.PARAM_NAME = X_PARAM_NAME)
158 AND ((tlinfo.PARAM_DESCRIPTION = X_PARAM_DESCRIPTION)
159 OR ((tlinfo.PARAM_DESCRIPTION is null) AND (X_PARAM_DESCRIPTION is null)))
160 ) then
161 null;
162 else
163 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
164 app_exception.raise_exception;
165 end if;
166 end if;
167 end loop;
168 return;
169 end LOCK_ROW;
170
171 procedure UPDATE_ROW (
172 X_VDU_PORT_PARAM_ID in NUMBER,
173 X_OBJECT_VERSION_NUMBER in NUMBER,
174 X_VDU_TYPE_ID in NUMBER,
175 X_MIDDLEWARE_TYPE_ID in NUMBER,
176 X_PARAM_CODE in VARCHAR2,
177 X_VALUE_TYPE in VARCHAR2,
178 X_VALUE_LENGTH in VARCHAR2,
179 X_AUTO_GEN_FLAG in VARCHAR2,
180 X_PARAM_NAME in VARCHAR2,
181 X_PARAM_DESCRIPTION in VARCHAR2,
182 X_LAST_UPDATE_DATE in DATE,
183 X_LAST_UPDATED_BY in NUMBER,
184 X_LAST_UPDATE_LOGIN in NUMBER
185 ) is
186 begin
187 update IEC_P_VDU_PORT_PARAMS_B set
188 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
189 VDU_TYPE_ID = X_VDU_TYPE_ID,
190 MIDDLEWARE_TYPE_ID = X_MIDDLEWARE_TYPE_ID,
191 PARAM_CODE = X_PARAM_CODE,
192 VALUE_TYPE = X_VALUE_TYPE,
193 VALUE_LENGTH = X_VALUE_LENGTH,
194 AUTO_GEN_FLAG = X_AUTO_GEN_FLAG,
195 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
196 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
197 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
198 where VDU_PORT_PARAM_ID = X_VDU_PORT_PARAM_ID;
199
200 if (sql%notfound) then
201 raise no_data_found;
202 end if;
203
204 update IEC_P_VDU_PORT_PARAMS_TL set
205 PARAM_NAME = X_PARAM_NAME,
206 PARAM_DESCRIPTION = X_PARAM_DESCRIPTION,
207 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
208 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
209 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
210 SOURCE_LANG = userenv('LANG')
211 where VDU_PORT_PARAM_ID = X_VDU_PORT_PARAM_ID
212 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
213
214 if (sql%notfound) then
215 raise no_data_found;
216 end if;
217 end UPDATE_ROW;
218
219 procedure DELETE_ROW (
220 X_VDU_PORT_PARAM_ID in NUMBER
221 ) is
222 begin
223 delete from IEC_P_VDU_PORT_PARAMS_TL
224 where VDU_PORT_PARAM_ID = X_VDU_PORT_PARAM_ID;
225
226 if (sql%notfound) then
227 raise no_data_found;
228 end if;
229
230 delete from IEC_P_VDU_PORT_PARAMS_B
231 where VDU_PORT_PARAM_ID = X_VDU_PORT_PARAM_ID;
232
233 if (sql%notfound) then
234 raise no_data_found;
235 end if;
236 end DELETE_ROW;
237
238 procedure ADD_LANGUAGE
239 is
240 begin
241 delete from IEC_P_VDU_PORT_PARAMS_TL T
242 where not exists
243 (select NULL
244 from IEC_P_VDU_PORT_PARAMS_B B
245 where B.VDU_PORT_PARAM_ID = T.VDU_PORT_PARAM_ID
246 );
247
248 update IEC_P_VDU_PORT_PARAMS_TL T set (
249 PARAM_NAME,
250 PARAM_DESCRIPTION
251 ) = (select
252 B.PARAM_NAME,
253 B.PARAM_DESCRIPTION
254 from IEC_P_VDU_PORT_PARAMS_TL B
255 where B.VDU_PORT_PARAM_ID = T.VDU_PORT_PARAM_ID
256 and B.LANGUAGE = T.SOURCE_LANG)
257 where (
258 T.VDU_PORT_PARAM_ID,
259 T.LANGUAGE
260 ) in (select
261 SUBT.VDU_PORT_PARAM_ID,
262 SUBT.LANGUAGE
263 from IEC_P_VDU_PORT_PARAMS_TL SUBB, IEC_P_VDU_PORT_PARAMS_TL SUBT
264 where SUBB.VDU_PORT_PARAM_ID = SUBT.VDU_PORT_PARAM_ID
265 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
266 and (SUBB.PARAM_NAME <> SUBT.PARAM_NAME
267 or SUBB.PARAM_DESCRIPTION <> SUBT.PARAM_DESCRIPTION
268 or (SUBB.PARAM_DESCRIPTION is null and SUBT.PARAM_DESCRIPTION is not null)
269 or (SUBB.PARAM_DESCRIPTION is not null and SUBT.PARAM_DESCRIPTION is null)
270 ));
271
272 insert into IEC_P_VDU_PORT_PARAMS_TL (
273 VDU_PORT_PARAM_ID,
274 CREATED_BY,
275 CREATION_DATE,
276 LAST_UPDATED_BY,
277 LAST_UPDATE_DATE,
278 LAST_UPDATE_LOGIN,
279 PARAM_NAME,
280 PARAM_DESCRIPTION,
281 LANGUAGE,
282 SOURCE_LANG
283 ) select /*+ ORDERED */
284 B.VDU_PORT_PARAM_ID,
285 B.CREATED_BY,
286 B.CREATION_DATE,
287 B.LAST_UPDATED_BY,
288 B.LAST_UPDATE_DATE,
289 B.LAST_UPDATE_LOGIN,
290 B.PARAM_NAME,
291 B.PARAM_DESCRIPTION,
292 L.LANGUAGE_CODE,
293 B.SOURCE_LANG
294 from IEC_P_VDU_PORT_PARAMS_TL B, FND_LANGUAGES L
295 where L.INSTALLED_FLAG in ('I', 'B')
296 and B.LANGUAGE = userenv('LANG')
297 and not exists
298 (select NULL
299 from IEC_P_VDU_PORT_PARAMS_TL T
300 where T.VDU_PORT_PARAM_ID = B.VDU_PORT_PARAM_ID
301 and T.LANGUAGE = L.LANGUAGE_CODE);
302 end ADD_LANGUAGE;
303
304 procedure LOAD_ROW (
305 X_VDU_PORT_PARAM_ID in NUMBER,
306 X_VDU_TYPE_ID in NUMBER,
307 X_MIDDLEWARE_TYPE_ID in NUMBER,
308 X_PARAM_CODE in VARCHAR2,
309 X_VALUE_TYPE in VARCHAR2,
310 X_VALUE_LENGTH in VARCHAR2,
311 X_AUTO_GEN_FLAG in VARCHAR2,
312 X_PARAM_NAME in VARCHAR2,
313 X_PARAM_DESCRIPTION in VARCHAR2,
314 X_OWNER in VARCHAR2
315 ) is
316
317 USER_ID NUMBER := 0;
318 ROW_ID VARCHAR2(500);
319 begin
320
321 if (X_OWNER = 'SEED') then
322 USER_ID := 1;
323 end if;
324
325 UPDATE_ROW ( X_VDU_PORT_PARAM_ID
326 , 0
327 , X_VDU_TYPE_ID
328 , X_MIDDLEWARE_TYPE_ID
329 , X_PARAM_CODE
330 , X_VALUE_TYPE
331 , X_VALUE_LENGTH
332 , X_AUTO_GEN_FLAG
333 , X_PARAM_NAME
334 , X_PARAM_DESCRIPTION
335 , SYSDATE
336 , USER_ID
337 , 0);
338
339 exception
340 when no_data_found then
341 INSERT_ROW ( ROW_ID
342 , X_VDU_PORT_PARAM_ID
343 , 0
344 , X_VDU_TYPE_ID
345 , X_MIDDLEWARE_TYPE_ID
346 , X_PARAM_CODE
347 , X_VALUE_TYPE
348 , X_VALUE_LENGTH
349 , X_AUTO_GEN_FLAG
350 , X_PARAM_NAME
351 , X_PARAM_DESCRIPTION
352 , SYSDATE
353 , USER_ID
354 , SYSDATE
355 , USER_ID
356 , 0);
357
358 end LOAD_ROW;
359
360 procedure TRANSLATE_ROW (
361 X_VDU_PORT_PARAM_ID in NUMBER,
362 X_PARAM_NAME in VARCHAR2,
363 X_PARAM_DESCRIPTION in VARCHAR2,
364 X_OWNER in VARCHAR2
365 ) is
366 begin
367
368 -- only UPDATE rows that have not been altered by user
369
370 update IEC_P_VDU_PORT_PARAMS_TL set
371 PARAM_NAME = X_PARAM_NAME,
372 PARAM_DESCRIPTION = X_PARAM_DESCRIPTION,
373 SOURCE_LANG = userenv('LANG'),
374 LAST_UPDATE_DATE = SYSDATE,
375 LAST_UPDATED_BY = DECODE(X_OWNER, 'SEED', 1, 0),
376 LAST_UPDATE_LOGIN = 0
377 where VDU_PORT_PARAM_ID = X_VDU_PORT_PARAM_ID
378 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
379
380 end TRANSLATE_ROW;
381
382 end IEC_P_VDU_PORT_PARAMS_PKG;