1 package body JTF_R_RULESETS_PKG as
2 /* $Header: ibagrstb.pls 115.1 2000/11/08 15:17:26 pkm ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out VARCHAR2,
5 X_RULESET_ID in NUMBER,
6 X_APPLICATION_ID in NUMBER,
7 X_STATUS in VARCHAR2,
8 X_IBA_SORT_BY_CODE in VARCHAR2,
9 X_START_DATE in DATE,
10 X_END_DATE in DATE,
11 X_RULESET_CLASS in BLOB,
12 X_RULESET_SER in LONG,
13 X_OBJECT_VERSION_NUMBER in NUMBER,
14 X_IBA_SORT_ORDER in NUMBER,
15 X_NAME in VARCHAR2,
16 X_DESCRIPTION in VARCHAR2,
17 X_CREATION_DATE in DATE,
18 X_CREATED_BY in NUMBER,
19 X_LAST_UPDATE_DATE in DATE,
20 X_LAST_UPDATED_BY in NUMBER,
21 X_LAST_UPDATE_LOGIN in NUMBER
22 ) is
23 cursor C is select ROWID from JTF_R_RULESETS_B
24 where RULESET_ID = X_RULESET_ID
25 ;
26 begin
27 insert into JTF_R_RULESETS_B (
28 RULESET_ID,
29 APPLICATION_ID,
30 STATUS,
31 IBA_SORT_BY_CODE,
32 START_DATE,
33 END_DATE,
34 RULESET_CLASS,
35 RULESET_SER,
36 OBJECT_VERSION_NUMBER,
37 IBA_SORT_ORDER,
38 CREATION_DATE,
39 CREATED_BY,
40 LAST_UPDATE_DATE,
41 LAST_UPDATED_BY,
42 LAST_UPDATE_LOGIN
43 ) values (
44 X_RULESET_ID,
45 X_APPLICATION_ID,
46 X_STATUS,
47 X_IBA_SORT_BY_CODE,
48 X_START_DATE,
49 X_END_DATE,
50 X_RULESET_CLASS,
51 X_RULESET_SER,
52 X_OBJECT_VERSION_NUMBER,
53 X_IBA_SORT_ORDER,
54 X_CREATION_DATE,
55 X_CREATED_BY,
56 X_LAST_UPDATE_DATE,
57 X_LAST_UPDATED_BY,
58 X_LAST_UPDATE_LOGIN
59 );
60
61 insert into JTF_R_RULESETS_TL (
62 DESCRIPTION,
63 OBJECT_VERSION_NUMBER,
64 LAST_UPDATED_BY,
65 LAST_UPDATE_DATE,
66 LAST_UPDATE_LOGIN,
67 NAME,
68 RULESET_ID,
69 CREATED_BY,
70 CREATION_DATE,
71 LANGUAGE,
72 SOURCE_LANG
73 ) select
74 X_DESCRIPTION,
75 X_OBJECT_VERSION_NUMBER,
76 X_LAST_UPDATED_BY,
77 X_LAST_UPDATE_DATE,
78 X_LAST_UPDATE_LOGIN,
79 X_NAME,
80 X_RULESET_ID,
81 X_CREATED_BY,
82 X_CREATION_DATE,
83 L.LANGUAGE_CODE,
84 userenv('LANG')
85 from FND_LANGUAGES L
86 where L.INSTALLED_FLAG in ('I', 'B')
87 and not exists
88 (select NULL
89 from JTF_R_RULESETS_TL T
90 where T.RULESET_ID = X_RULESET_ID
91 and T.LANGUAGE = L.LANGUAGE_CODE);
92
93 open c;
94 fetch c into X_ROWID;
95 if (c%notfound) then
96 close c;
97 raise no_data_found;
98 end if;
99 close c;
100
101 end INSERT_ROW;
102
103 procedure LOCK_ROW (
104 X_RULESET_ID in NUMBER,
105 X_APPLICATION_ID in NUMBER,
106 X_STATUS in VARCHAR2,
107 X_IBA_SORT_BY_CODE in VARCHAR2,
108 X_START_DATE in DATE,
109 X_END_DATE in DATE,
110 X_RULESET_CLASS in BLOB,
111 X_RULESET_SER in LONG,
112 X_OBJECT_VERSION_NUMBER in NUMBER,
113 X_IBA_SORT_ORDER in NUMBER,
114 X_NAME in VARCHAR2,
115 X_DESCRIPTION in VARCHAR2
116 ) is
117 cursor c is select
118 APPLICATION_ID,
119 STATUS,
120 IBA_SORT_BY_CODE,
121 START_DATE,
122 END_DATE,
123 RULESET_CLASS,
124 RULESET_SER,
125 OBJECT_VERSION_NUMBER,
126 IBA_SORT_ORDER
127 from JTF_R_RULESETS_B
128 where RULESET_ID = X_RULESET_ID
129 for update of RULESET_ID nowait;
130 recinfo c%rowtype;
131
132 cursor c1 is select
133 NAME,
134 DESCRIPTION,
135 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
136 from JTF_R_RULESETS_TL
137 where RULESET_ID = X_RULESET_ID
138 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
139 for update of RULESET_ID nowait;
140 begin
141 open c;
142 fetch c into recinfo;
143 if (c%notfound) then
144 close c;
145 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
146 app_exception.raise_exception;
147 end if;
148 close c;
149 if ( (recinfo.APPLICATION_ID = X_APPLICATION_ID)
150 AND (recinfo.STATUS = X_STATUS)
151 AND ((recinfo.IBA_SORT_BY_CODE = X_IBA_SORT_BY_CODE)
152 OR ((recinfo.IBA_SORT_BY_CODE is null) AND (X_IBA_SORT_BY_CODE is null)))
153 AND ((recinfo.START_DATE = X_START_DATE)
154 OR ((recinfo.START_DATE is null) AND (X_START_DATE is null)))
155 AND ((recinfo.END_DATE = X_END_DATE)
156 OR ((recinfo.END_DATE is null) AND (X_END_DATE is null)))
157 /* commenting this BLOB comparison, since the compilation is failing
158 AND ((recinfo.RULESET_CLASS = X_RULESET_CLASS)
159 OR ((recinfo.RULESET_CLASS is null) AND (X_RULESET_CLASS is null)))
160 */
161 AND ((recinfo.RULESET_SER = X_RULESET_SER)
162 OR ((recinfo.RULESET_SER is null) AND (X_RULESET_SER is null)))
163 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
164 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
165 AND ((recinfo.IBA_SORT_ORDER = X_IBA_SORT_ORDER)
166 OR ((recinfo.IBA_SORT_ORDER is null) AND (X_IBA_SORT_ORDER is null)))
167 ) then
168 null;
169 else
170 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
171 app_exception.raise_exception;
172 end if;
173
174 for tlinfo in c1 loop
175 if (tlinfo.BASELANG = 'Y') then
176 if ( (tlinfo.NAME = X_NAME)
177 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
178 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
179 ) then
180 null;
181 else
182 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
183 app_exception.raise_exception;
184 end if;
185 end if;
186 end loop;
187 return;
188 end LOCK_ROW;
189
190 procedure UPDATE_ROW (
191 X_RULESET_ID in NUMBER,
192 X_APPLICATION_ID in NUMBER,
193 X_STATUS in VARCHAR2,
194 X_IBA_SORT_BY_CODE in VARCHAR2,
195 X_START_DATE in DATE,
196 X_END_DATE in DATE,
197 X_RULESET_CLASS in BLOB,
198 X_RULESET_SER in LONG,
199 X_OBJECT_VERSION_NUMBER in NUMBER,
200 X_IBA_SORT_ORDER in NUMBER,
201 X_NAME in VARCHAR2,
202 X_DESCRIPTION in VARCHAR2,
203 X_LAST_UPDATE_DATE in DATE,
204 X_LAST_UPDATED_BY in NUMBER,
205 X_LAST_UPDATE_LOGIN in NUMBER
206 ) is
207 begin
208 update JTF_R_RULESETS_B set
209 APPLICATION_ID = X_APPLICATION_ID,
210 STATUS = X_STATUS,
211 IBA_SORT_BY_CODE = X_IBA_SORT_BY_CODE,
212 START_DATE = X_START_DATE,
213 END_DATE = X_END_DATE,
214 RULESET_CLASS = X_RULESET_CLASS,
215 RULESET_SER = X_RULESET_SER,
216 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
217 IBA_SORT_ORDER = X_IBA_SORT_ORDER,
218 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
219 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
220 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
221 where RULESET_ID = X_RULESET_ID;
222
223 if (sql%notfound) then
224 raise no_data_found;
225 end if;
226
227 update JTF_R_RULESETS_TL set
228 NAME = X_NAME,
229 DESCRIPTION = X_DESCRIPTION,
230 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
231 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
232 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
233 SOURCE_LANG = userenv('LANG')
234 where RULESET_ID = X_RULESET_ID
235 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
236
237 if (sql%notfound) then
238 raise no_data_found;
239 end if;
240 end UPDATE_ROW;
241
242 procedure DELETE_ROW (
243 X_RULESET_ID in NUMBER
244 ) is
245 begin
246 delete from JTF_R_RULESETS_TL
247 where RULESET_ID = X_RULESET_ID;
248
249 if (sql%notfound) then
250 raise no_data_found;
251 end if;
252
253 delete from JTF_R_RULESETS_B
254 where RULESET_ID = X_RULESET_ID;
255
256 if (sql%notfound) then
257 raise no_data_found;
258 end if;
259 end DELETE_ROW;
260
261 procedure ADD_LANGUAGE
262 is
263 begin
264 delete from JTF_R_RULESETS_TL T
265 where not exists
266 (select NULL
267 from JTF_R_RULESETS_B B
268 where B.RULESET_ID = T.RULESET_ID
269 );
270
271 update JTF_R_RULESETS_TL T set (
272 NAME,
273 DESCRIPTION
274 ) = (select
275 B.NAME,
276 B.DESCRIPTION
277 from JTF_R_RULESETS_TL B
278 where B.RULESET_ID = T.RULESET_ID
279 and B.LANGUAGE = T.SOURCE_LANG)
280 where (
281 T.RULESET_ID,
282 T.LANGUAGE
283 ) in (select
284 SUBT.RULESET_ID,
285 SUBT.LANGUAGE
286 from JTF_R_RULESETS_TL SUBB, JTF_R_RULESETS_TL SUBT
287 where SUBB.RULESET_ID = SUBT.RULESET_ID
288 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
289 and (SUBB.NAME <> SUBT.NAME
290 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
291 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
292 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
293 ));
294
295 insert into JTF_R_RULESETS_TL (
296 DESCRIPTION,
297 OBJECT_VERSION_NUMBER,
298 LAST_UPDATED_BY,
299 LAST_UPDATE_DATE,
300 LAST_UPDATE_LOGIN,
301 NAME,
302 RULESET_ID,
303 CREATED_BY,
304 CREATION_DATE,
305 LANGUAGE,
306 SOURCE_LANG
307 ) select
308 B.DESCRIPTION,
309 B.OBJECT_VERSION_NUMBER,
310 B.LAST_UPDATED_BY,
311 B.LAST_UPDATE_DATE,
312 B.LAST_UPDATE_LOGIN,
313 B.NAME,
314 B.RULESET_ID,
315 B.CREATED_BY,
316 B.CREATION_DATE,
317 L.LANGUAGE_CODE,
318 B.SOURCE_LANG
319 from JTF_R_RULESETS_TL B, FND_LANGUAGES L
320 where L.INSTALLED_FLAG in ('I', 'B')
321 and B.LANGUAGE = userenv('LANG')
322 and not exists
323 (select NULL
324 from JTF_R_RULESETS_TL T
325 where T.RULESET_ID = B.RULESET_ID
326 and T.LANGUAGE = L.LANGUAGE_CODE);
327 end ADD_LANGUAGE;
328
329 procedure TRANSLATE_ROW(
330 X_RULESET_ID in NUMBER,
331 X_OWNER in VARCHAR2,
332 X_NAME in VARCHAR2,
333 X_DESCRIPTION in VARCHAR2
334 ) is
335 begin
336 update JTF_R_RULESETS_TL set
337 name = nvl(X_NAME, name),
338 description = nvl(X_DESCRIPTION, description),
339 last_update_date = sysdate,
340 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
341 last_update_login = 0,
342 source_lang = userenv('LANG')
343 where RULESET_ID = X_RULESET_ID
344 and userenv('LANG') in (language, source_lang);
345
346 end TRANSLATE_ROW;
347
348 end JTF_R_RULESETS_PKG;
[Home] [Help]