[Home] [Help]
PACKAGE BODY: APPS.IEC_O_ALG_EXPR_DEFS_PKG
Source
1 package body IEC_O_ALG_EXPR_DEFS_PKG as
2 /* $Header: IECHEXDB.pls 120.1 2005/07/20 13:04:34 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_EXPR_CODE in VARCHAR2,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_DESCRIPTION in VARCHAR2,
8 X_SUBST_TEXT in VARCHAR2,
9 X_CREATION_DATE in DATE,
10 X_CREATED_BY in NUMBER,
11 X_LAST_UPDATE_DATE in DATE,
12 X_LAST_UPDATED_BY in NUMBER,
13 X_LAST_UPDATE_LOGIN in NUMBER
14 ) is
15 cursor C is select ROWID from IEC_O_ALG_EXPR_DEFS_B
16 where EXPR_CODE = X_EXPR_CODE
17 ;
18 begin
19 insert into IEC_O_ALG_EXPR_DEFS_B (
20 EXPR_CODE,
21 OBJECT_VERSION_NUMBER,
22 CREATION_DATE,
23 CREATED_BY,
24 LAST_UPDATE_DATE,
25 LAST_UPDATED_BY,
26 LAST_UPDATE_LOGIN
27 ) values (
28 X_EXPR_CODE,
29 X_OBJECT_VERSION_NUMBER,
30 X_CREATION_DATE,
31 X_CREATED_BY,
32 X_LAST_UPDATE_DATE,
33 X_LAST_UPDATED_BY,
34 X_LAST_UPDATE_LOGIN
35 );
36
37 insert into IEC_O_ALG_EXPR_DEFS_TL (
38 EXPR_CODE,
39 DESCRIPTION,
40 SUBST_TEXT,
41 CREATED_BY,
42 CREATION_DATE,
43 LAST_UPDATED_BY,
44 LAST_UPDATE_DATE,
45 LAST_UPDATE_LOGIN,
46 OBJECT_VERSION_NUMBER,
47 LANGUAGE,
48 SOURCE_LANG
49 ) select
50 X_EXPR_CODE,
51 X_DESCRIPTION,
52 X_SUBST_TEXT,
53 X_CREATED_BY,
54 X_CREATION_DATE,
55 X_LAST_UPDATED_BY,
56 X_LAST_UPDATE_DATE,
57 X_LAST_UPDATE_LOGIN,
58 X_OBJECT_VERSION_NUMBER,
59 L.LANGUAGE_CODE,
60 userenv('LANG')
61 from FND_LANGUAGES L
62 where L.INSTALLED_FLAG in ('I', 'B')
63 and not exists
64 (select NULL
65 from IEC_O_ALG_EXPR_DEFS_TL T
66 where T.EXPR_CODE = X_EXPR_CODE
67 and T.LANGUAGE = L.LANGUAGE_CODE);
68
69 open c;
70 fetch c into X_ROWID;
71 if (c%notfound) then
72 close c;
73 raise no_data_found;
74 end if;
75 close c;
76
77 end INSERT_ROW;
78
79 procedure LOCK_ROW (
80 X_EXPR_CODE in VARCHAR2,
81 X_OBJECT_VERSION_NUMBER in NUMBER,
82 X_DESCRIPTION in VARCHAR2,
83 X_SUBST_TEXT in VARCHAR2
84 ) is
85 cursor c is select
86 OBJECT_VERSION_NUMBER
87 from IEC_O_ALG_EXPR_DEFS_B
88 where EXPR_CODE = X_EXPR_CODE
89 for update of EXPR_CODE nowait;
90 recinfo c%rowtype;
91
92 cursor c1 is select
93 DESCRIPTION,
94 SUBST_TEXT,
95 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
96 from IEC_O_ALG_EXPR_DEFS_TL
97 where EXPR_CODE = X_EXPR_CODE
98 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
99 for update of EXPR_CODE nowait;
100 begin
101 open c;
102 fetch c into recinfo;
103 if (c%notfound) then
104 close c;
105 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
106 app_exception.raise_exception;
107 end if;
108 close c;
109 if recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER then
110 null;
111 else
112 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
113 app_exception.raise_exception;
114 end if;
115
116 for tlinfo in c1 loop
117 if (tlinfo.BASELANG = 'Y') then
118 if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
119 AND (tlinfo.SUBST_TEXT = X_SUBST_TEXT)
120 ) then
121 null;
122 else
123 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
124 app_exception.raise_exception;
125 end if;
126 end if;
127 end loop;
128 return;
129 end LOCK_ROW;
130
131 procedure UPDATE_ROW (
132 X_EXPR_CODE in VARCHAR2,
133 X_OBJECT_VERSION_NUMBER in NUMBER,
134 X_DESCRIPTION in VARCHAR2,
135 X_SUBST_TEXT in VARCHAR2,
136 X_LAST_UPDATE_DATE in DATE,
137 X_LAST_UPDATED_BY in NUMBER,
138 X_LAST_UPDATE_LOGIN in NUMBER
139 ) is
140 begin
141 update IEC_O_ALG_EXPR_DEFS_B set
142 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
143 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
144 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
145 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
146 where EXPR_CODE = X_EXPR_CODE;
147
148 if (sql%notfound) then
149 raise no_data_found;
150 end if;
151
152 update IEC_O_ALG_EXPR_DEFS_TL set
153 DESCRIPTION = X_DESCRIPTION,
154 SUBST_TEXT = X_SUBST_TEXT,
155 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
156 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
157 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
158 SOURCE_LANG = userenv('LANG')
159 where EXPR_CODE = X_EXPR_CODE
160 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
161
162 if (sql%notfound) then
163 raise no_data_found;
164 end if;
165 end UPDATE_ROW;
166
167 procedure DELETE_ROW (
168 X_EXPR_CODE in VARCHAR2
169 ) is
170 begin
171 delete from IEC_O_ALG_EXPR_DEFS_TL
172 where EXPR_CODE = X_EXPR_CODE;
173
174 if (sql%notfound) then
175 raise no_data_found;
176 end if;
177
178 delete from IEC_O_ALG_EXPR_DEFS_B
179 where EXPR_CODE = X_EXPR_CODE;
180
181 if (sql%notfound) then
182 raise no_data_found;
183 end if;
184 end DELETE_ROW;
185
186 procedure ADD_LANGUAGE
187 is
188 begin
189 delete from IEC_O_ALG_EXPR_DEFS_TL T
190 where not exists
191 (select NULL
192 from IEC_O_ALG_EXPR_DEFS_B B
193 where B.EXPR_CODE = T.EXPR_CODE
194 );
195
196 update IEC_O_ALG_EXPR_DEFS_TL T set (
197 DESCRIPTION,
198 SUBST_TEXT
199 ) = (select
200 B.DESCRIPTION,
201 B.SUBST_TEXT
202 from IEC_O_ALG_EXPR_DEFS_TL B
203 where B.EXPR_CODE = T.EXPR_CODE
204 and B.LANGUAGE = T.SOURCE_LANG)
205 where (
206 T.EXPR_CODE,
207 T.LANGUAGE
208 ) in (select
209 SUBT.EXPR_CODE,
210 SUBT.LANGUAGE
211 from IEC_O_ALG_EXPR_DEFS_TL SUBB, IEC_O_ALG_EXPR_DEFS_TL SUBT
212 where SUBB.EXPR_CODE = SUBT.EXPR_CODE
213 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
214 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
215 or SUBB.SUBST_TEXT <> SUBT.SUBST_TEXT
216 ));
217
218 insert into IEC_O_ALG_EXPR_DEFS_TL (
219 EXPR_CODE,
220 DESCRIPTION,
221 SUBST_TEXT,
222 CREATED_BY,
223 CREATION_DATE,
224 LAST_UPDATED_BY,
225 LAST_UPDATE_DATE,
226 LAST_UPDATE_LOGIN,
227 OBJECT_VERSION_NUMBER,
228 LANGUAGE,
229 SOURCE_LANG
230 ) select /*+ ORDERED */
231 B.EXPR_CODE,
232 B.DESCRIPTION,
233 B.SUBST_TEXT,
234 B.CREATED_BY,
235 B.CREATION_DATE,
236 B.LAST_UPDATED_BY,
237 B.LAST_UPDATE_DATE,
238 B.LAST_UPDATE_LOGIN,
239 B.OBJECT_VERSION_NUMBER,
240 L.LANGUAGE_CODE,
241 B.SOURCE_LANG
242 from IEC_O_ALG_EXPR_DEFS_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 IEC_O_ALG_EXPR_DEFS_TL T
248 where T.EXPR_CODE = B.EXPR_CODE
249 and T.LANGUAGE = L.LANGUAGE_CODE);
250 end ADD_LANGUAGE;
251
252 procedure LOAD_ROW (
253 X_EXPR_CODE in VARCHAR2,
254 X_DESCRIPTION in VARCHAR2,
255 X_SUBST_TEXT in VARCHAR2,
256 X_OWNER in VARCHAR2
257 ) is
258
259 USER_ID NUMBER := 0;
260 ROW_ID VARCHAR2(500);
261 begin
262
263 USER_ID := fnd_load_util.owner_id(x_owner);
264
265 UPDATE_ROW (X_EXPR_CODE, 0, X_DESCRIPTION, X_SUBST_TEXT, SYSDATE, USER_ID, 0);
266
267 exception
268 when no_data_found then
269 INSERT_ROW (ROW_ID, X_EXPR_CODE, 0, X_DESCRIPTION, X_SUBST_TEXT, SYSDATE, USER_ID, SYSDATE, USER_ID, 0);
270
271 end LOAD_ROW;
272
273 procedure LOAD_SEED_ROW (
274 X_upload_mode in VARCHAR2,
275 X_EXPR_CODE in VARCHAR2,
276 X_DESCRIPTION in VARCHAR2,
277 X_SUBST_TEXT in VARCHAR2,
278 X_OWNER in VARCHAR2
279 ) is
280 begin
281 if(X_upload_mode='NLS') then
282 IEC_O_ALG_EXPR_DEFS_PKG.TRANSLATE_ROW (
283 X_EXPR_CODE,
284 X_DESCRIPTION,
285 X_SUBST_TEXT,
286 X_OWNER);
287 else
288 IEC_O_ALG_EXPR_DEFS_PKG.LOAD_ROW (
289 X_EXPR_CODE,
290 X_DESCRIPTION,
291 X_SUBST_TEXT,
292 X_OWNER);
293 end if;
294
295 end LOAD_SEED_ROW;
296
297 procedure TRANSLATE_ROW (
298 X_EXPR_CODE in VARCHAR2,
299 X_DESCRIPTION in VARCHAR2,
300 X_SUBST_TEXT in VARCHAR2,
301 X_OWNER in VARCHAR2
302 ) is
303 begin
304
305 -- only UPDATE rows that have not been altered by user
306
307 update IEC_O_ALG_EXPR_DEFS_TL set
308 DESCRIPTION = X_DESCRIPTION,
309 SUBST_TEXT = X_SUBST_TEXT,
310 SOURCE_LANG = userenv('LANG'),
311 LAST_UPDATE_DATE = SYSDATE,
312 LAST_UPDATED_BY = fnd_load_util.owner_id(X_OWNER),
313 LAST_UPDATE_LOGIN = 0
314 where EXPR_CODE = X_EXPR_CODE
315 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
316
317 end TRANSLATE_ROW;
318
319 end IEC_O_ALG_EXPR_DEFS_PKG;