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