1 package body CSD_RET_RULE_CONDITIONS_PKG as
2 /* $Header: csdtrrcb.pls 120.0 2011/07/06 09:27:45 subhat noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_RULE_CONDITION_ID in NUMBER,
6 X_ATTRIBUTE10 in VARCHAR2,
7 X_ATTRIBUTE9 in VARCHAR2,
8 X_ATTRIBUTE14 in VARCHAR2,
9 X_ATTRIBUTE15 in VARCHAR2,
10 X_OBJECT_VERSION_NUMBER in NUMBER,
11 X_ATTRIBUTE_CATEGORY in VARCHAR2,
12 X_ATTRIBUTE1 in VARCHAR2,
13 X_ATTRIBUTE2 in VARCHAR2,
14 X_ATTRIBUTE3 in VARCHAR2,
15 X_ATTRIBUTE4 in VARCHAR2,
16 X_ATTRIBUTE5 in VARCHAR2,
17 X_ATTRIBUTE6 in VARCHAR2,
18 X_ATTRIBUTE7 in VARCHAR2,
19 X_ATTRIBUTE8 in VARCHAR2,
20 X_RETURN_RULE_ID in NUMBER,
21 X_ATTRIBUTE11 in VARCHAR2,
22 X_ATTRIBUTE12 in VARCHAR2,
23 X_ATTRIBUTE13 in VARCHAR2,
24 X_NAME in VARCHAR2,
25 X_CREATION_DATE in DATE,
26 X_CREATED_BY in NUMBER,
27 X_LAST_UPDATE_DATE in DATE,
28 X_LAST_UPDATED_BY in NUMBER,
29 X_LAST_UPDATE_LOGIN in NUMBER
30 ) is
31 cursor C is select ROWID from CSD_RET_RULE_CONDITIONS_B
32 where RULE_CONDITION_ID = X_RULE_CONDITION_ID
33 ;
34 begin
35 insert into CSD_RET_RULE_CONDITIONS_B (
36 RULE_CONDITION_ID,
37 ATTRIBUTE10,
38 ATTRIBUTE9,
39 ATTRIBUTE14,
40 ATTRIBUTE15,
41 OBJECT_VERSION_NUMBER,
42 ATTRIBUTE_CATEGORY,
43 ATTRIBUTE1,
44 ATTRIBUTE2,
45 ATTRIBUTE3,
46 ATTRIBUTE4,
47 ATTRIBUTE5,
48 ATTRIBUTE6,
49 ATTRIBUTE7,
50 ATTRIBUTE8,
51 RETURN_RULE_ID,
52 ATTRIBUTE11,
53 ATTRIBUTE12,
54 ATTRIBUTE13,
55 CREATION_DATE,
56 CREATED_BY,
57 LAST_UPDATE_DATE,
58 LAST_UPDATED_BY,
59 LAST_UPDATE_LOGIN
60 ) values (
61 X_RULE_CONDITION_ID,
62 X_ATTRIBUTE10,
63 X_ATTRIBUTE9,
64 X_ATTRIBUTE14,
65 X_ATTRIBUTE15,
66 X_OBJECT_VERSION_NUMBER,
67 X_ATTRIBUTE_CATEGORY,
68 X_ATTRIBUTE1,
69 X_ATTRIBUTE2,
70 X_ATTRIBUTE3,
71 X_ATTRIBUTE4,
72 X_ATTRIBUTE5,
73 X_ATTRIBUTE6,
74 X_ATTRIBUTE7,
75 X_ATTRIBUTE8,
76 X_RETURN_RULE_ID,
77 X_ATTRIBUTE11,
78 X_ATTRIBUTE12,
79 X_ATTRIBUTE13,
80 X_CREATION_DATE,
81 X_CREATED_BY,
82 X_LAST_UPDATE_DATE,
83 X_LAST_UPDATED_BY,
84 X_LAST_UPDATE_LOGIN
85 );
86
87 insert into CSD_RET_RULE_CONDITIONS_TL (
88 RULE_CONDITION_ID,
89 NAME,
90 CREATION_DATE,
91 CREATED_BY,
92 LAST_UPDATED_BY,
93 LAST_UPDATE_DATE,
94 LAST_UPDATE_LOGIN,
95 LANGUAGE,
96 SOURCE_LANG
97 ) select
98 X_RULE_CONDITION_ID,
99 X_NAME,
100 X_CREATION_DATE,
101 X_CREATED_BY,
102 X_LAST_UPDATED_BY,
103 X_LAST_UPDATE_DATE,
104 X_LAST_UPDATE_LOGIN,
105 L.LANGUAGE_CODE,
106 userenv('LANG')
107 from FND_LANGUAGES L
108 where L.INSTALLED_FLAG in ('I', 'B')
109 and not exists
110 (select NULL
111 from CSD_RET_RULE_CONDITIONS_TL T
112 where T.RULE_CONDITION_ID = X_RULE_CONDITION_ID
113 and T.LANGUAGE = L.LANGUAGE_CODE);
114
115 open c;
116 fetch c into X_ROWID;
117 if (c%notfound) then
118 close c;
119 raise no_data_found;
120 end if;
121 close c;
122
123 end INSERT_ROW;
124
125 procedure LOCK_ROW (
126 X_RULE_CONDITION_ID in NUMBER,
127 X_ATTRIBUTE10 in VARCHAR2,
128 X_ATTRIBUTE9 in VARCHAR2,
129 X_ATTRIBUTE14 in VARCHAR2,
130 X_ATTRIBUTE15 in VARCHAR2,
131 X_OBJECT_VERSION_NUMBER in NUMBER,
132 X_ATTRIBUTE_CATEGORY in VARCHAR2,
133 X_ATTRIBUTE1 in VARCHAR2,
134 X_ATTRIBUTE2 in VARCHAR2,
135 X_ATTRIBUTE3 in VARCHAR2,
136 X_ATTRIBUTE4 in VARCHAR2,
137 X_ATTRIBUTE5 in VARCHAR2,
138 X_ATTRIBUTE6 in VARCHAR2,
139 X_ATTRIBUTE7 in VARCHAR2,
140 X_ATTRIBUTE8 in VARCHAR2,
141 X_RETURN_RULE_ID in NUMBER,
142 X_ATTRIBUTE11 in VARCHAR2,
143 X_ATTRIBUTE12 in VARCHAR2,
144 X_ATTRIBUTE13 in VARCHAR2,
145 X_NAME in VARCHAR2
146 ) is
147 cursor c is select
148 ATTRIBUTE10,
149 ATTRIBUTE9,
150 ATTRIBUTE14,
151 ATTRIBUTE15,
152 OBJECT_VERSION_NUMBER,
153 ATTRIBUTE_CATEGORY,
154 ATTRIBUTE1,
155 ATTRIBUTE2,
156 ATTRIBUTE3,
157 ATTRIBUTE4,
158 ATTRIBUTE5,
159 ATTRIBUTE6,
160 ATTRIBUTE7,
161 ATTRIBUTE8,
162 RETURN_RULE_ID,
163 ATTRIBUTE11,
164 ATTRIBUTE12,
165 ATTRIBUTE13
166 from CSD_RET_RULE_CONDITIONS_B
167 where RULE_CONDITION_ID = X_RULE_CONDITION_ID
168 for update of RULE_CONDITION_ID nowait;
169 recinfo c%rowtype;
170
171 cursor c1 is select
172 NAME,
173 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
174 from CSD_RET_RULE_CONDITIONS_TL
175 where RULE_CONDITION_ID = X_RULE_CONDITION_ID
176 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
177 for update of RULE_CONDITION_ID nowait;
178 begin
179 open c;
180 fetch c into recinfo;
181 if (c%notfound) then
182 close c;
183 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
184 app_exception.raise_exception;
185 end if;
186 close c;
187 if ( ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
188 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
189 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
190 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
191 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
192 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
193 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
194 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
195 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
196 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
197 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
198 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
199 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
200 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
201 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
202 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
203 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
204 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
205 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
206 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
207 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
208 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
209 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
210 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
211 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
212 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
213 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
214 AND (recinfo.RETURN_RULE_ID = X_RETURN_RULE_ID)
215 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
216 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
217 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
218 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
219 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
220 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
221 ) then
222 null;
223 else
224 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
225 app_exception.raise_exception;
226 end if;
227
228 for tlinfo in c1 loop
229 if (tlinfo.BASELANG = 'Y') then
230 if ( ((tlinfo.NAME = X_NAME)
231 OR ((tlinfo.NAME is null) AND (X_NAME is null)))
232 ) then
233 null;
234 else
235 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
236 app_exception.raise_exception;
237 end if;
238 end if;
239 end loop;
240 return;
241 end LOCK_ROW;
242
243 procedure UPDATE_ROW (
244 X_RULE_CONDITION_ID in NUMBER,
245 X_ATTRIBUTE10 in VARCHAR2,
246 X_ATTRIBUTE9 in VARCHAR2,
247 X_ATTRIBUTE14 in VARCHAR2,
248 X_ATTRIBUTE15 in VARCHAR2,
249 X_OBJECT_VERSION_NUMBER in NUMBER,
250 X_ATTRIBUTE_CATEGORY in VARCHAR2,
251 X_ATTRIBUTE1 in VARCHAR2,
252 X_ATTRIBUTE2 in VARCHAR2,
253 X_ATTRIBUTE3 in VARCHAR2,
254 X_ATTRIBUTE4 in VARCHAR2,
255 X_ATTRIBUTE5 in VARCHAR2,
256 X_ATTRIBUTE6 in VARCHAR2,
257 X_ATTRIBUTE7 in VARCHAR2,
258 X_ATTRIBUTE8 in VARCHAR2,
259 X_RETURN_RULE_ID in NUMBER,
260 X_ATTRIBUTE11 in VARCHAR2,
261 X_ATTRIBUTE12 in VARCHAR2,
262 X_ATTRIBUTE13 in VARCHAR2,
263 X_NAME in VARCHAR2,
264 X_LAST_UPDATE_DATE in DATE,
265 X_LAST_UPDATED_BY in NUMBER,
266 X_LAST_UPDATE_LOGIN in NUMBER
267 ) is
268 begin
269 update CSD_RET_RULE_CONDITIONS_B set
270 ATTRIBUTE10 = X_ATTRIBUTE10,
271 ATTRIBUTE9 = X_ATTRIBUTE9,
272 ATTRIBUTE14 = X_ATTRIBUTE14,
273 ATTRIBUTE15 = X_ATTRIBUTE15,
274 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
275 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
276 ATTRIBUTE1 = X_ATTRIBUTE1,
277 ATTRIBUTE2 = X_ATTRIBUTE2,
278 ATTRIBUTE3 = X_ATTRIBUTE3,
279 ATTRIBUTE4 = X_ATTRIBUTE4,
280 ATTRIBUTE5 = X_ATTRIBUTE5,
281 ATTRIBUTE6 = X_ATTRIBUTE6,
282 ATTRIBUTE7 = X_ATTRIBUTE7,
283 ATTRIBUTE8 = X_ATTRIBUTE8,
284 RETURN_RULE_ID = X_RETURN_RULE_ID,
285 ATTRIBUTE11 = X_ATTRIBUTE11,
286 ATTRIBUTE12 = X_ATTRIBUTE12,
287 ATTRIBUTE13 = X_ATTRIBUTE13,
288 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
289 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
290 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
291 where RULE_CONDITION_ID = X_RULE_CONDITION_ID;
292
293 if (sql%notfound) then
294 raise no_data_found;
295 end if;
296
297 update CSD_RET_RULE_CONDITIONS_TL set
298 NAME = X_NAME,
299 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
300 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
301 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
302 SOURCE_LANG = userenv('LANG')
303 where RULE_CONDITION_ID = X_RULE_CONDITION_ID
304 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
305
306 if (sql%notfound) then
307 raise no_data_found;
308 end if;
309 end UPDATE_ROW;
310
311 procedure DELETE_ROW (
312 X_RULE_CONDITION_ID in NUMBER
313 ) is
314 begin
315 delete from CSD_RET_RULE_CONDITIONS_TL
316 where RULE_CONDITION_ID = X_RULE_CONDITION_ID;
317
318 if (sql%notfound) then
319 raise no_data_found;
320 end if;
321
322 delete from CSD_RET_RULE_CONDITIONS_B
323 where RULE_CONDITION_ID = X_RULE_CONDITION_ID;
324
325 if (sql%notfound) then
326 raise no_data_found;
327 end if;
328 end DELETE_ROW;
329
330 procedure ADD_LANGUAGE
331 is
332 begin
333 delete from CSD_RET_RULE_CONDITIONS_TL T
334 where not exists
335 (select NULL
336 from CSD_RET_RULE_CONDITIONS_B B
337 where B.RULE_CONDITION_ID = T.RULE_CONDITION_ID
338 );
339
340 update CSD_RET_RULE_CONDITIONS_TL T set (
341 NAME
342 ) = (select
343 B.NAME
344 from CSD_RET_RULE_CONDITIONS_TL B
345 where B.RULE_CONDITION_ID = T.RULE_CONDITION_ID
346 and B.LANGUAGE = T.SOURCE_LANG)
347 where (
348 T.RULE_CONDITION_ID,
349 T.LANGUAGE
350 ) in (select
351 SUBT.RULE_CONDITION_ID,
352 SUBT.LANGUAGE
353 from CSD_RET_RULE_CONDITIONS_TL SUBB, CSD_RET_RULE_CONDITIONS_TL SUBT
354 where SUBB.RULE_CONDITION_ID = SUBT.RULE_CONDITION_ID
355 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
356 and (SUBB.NAME <> SUBT.NAME
357 or (SUBB.NAME is null and SUBT.NAME is not null)
358 or (SUBB.NAME is not null and SUBT.NAME is null)
359 ));
360
361 insert into CSD_RET_RULE_CONDITIONS_TL (
362 RULE_CONDITION_ID,
363 NAME,
364 CREATION_DATE,
365 CREATED_BY,
366 LAST_UPDATED_BY,
367 LAST_UPDATE_DATE,
368 LAST_UPDATE_LOGIN,
369 LANGUAGE,
370 SOURCE_LANG
371 ) select /*+ ORDERED */
372 B.RULE_CONDITION_ID,
373 B.NAME,
374 B.CREATION_DATE,
375 B.CREATED_BY,
376 B.LAST_UPDATED_BY,
377 B.LAST_UPDATE_DATE,
378 B.LAST_UPDATE_LOGIN,
379 L.LANGUAGE_CODE,
380 B.SOURCE_LANG
381 from CSD_RET_RULE_CONDITIONS_TL B, FND_LANGUAGES L
382 where L.INSTALLED_FLAG in ('I', 'B')
383 and B.LANGUAGE = userenv('LANG')
384 and not exists
385 (select NULL
386 from CSD_RET_RULE_CONDITIONS_TL T
387 where T.RULE_CONDITION_ID = B.RULE_CONDITION_ID
388 and T.LANGUAGE = L.LANGUAGE_CODE);
389 end ADD_LANGUAGE;
390
391 end CSD_RET_RULE_CONDITIONS_PKG;
[Home] [Help]