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