[Home] [Help]
PACKAGE BODY: APPS.CSD_RULE_CONDITIONS_PKG
Source
1 package body CSD_RULE_CONDITIONS_PKG as
2 /* $Header: csdtrcnb.pls 120.0.12020000.2 2013/01/15 21:33:46 vicli ship $ */
3
4 procedure INSERT_ROW (
5 PX_ROWID in out nocopy VARCHAR2,
6 PX_RULE_CONDITION_ID in out nocopy NUMBER,
7 P_OBJECT_VERSION_NUMBER in NUMBER,
8 P_CREATION_DATE in DATE,
9 P_CREATED_BY in NUMBER,
10 P_LAST_UPDATE_DATE in DATE,
11 P_LAST_UPDATED_BY in NUMBER,
12 P_LAST_UPDATE_LOGIN in NUMBER,
13 P_NAME in VARCHAR2,
14 P_RULE_ID in NUMBER,
15 P_ATTRIBUTE_CATEGORY in VARCHAR2,
16 P_ATTRIBUTE1 in VARCHAR2,
17 P_ATTRIBUTE2 in VARCHAR2,
18 P_ATTRIBUTE3 in VARCHAR2,
19 P_ATTRIBUTE4 in VARCHAR2,
20 P_ATTRIBUTE5 in VARCHAR2,
21 P_ATTRIBUTE6 in VARCHAR2,
22 P_ATTRIBUTE7 in VARCHAR2,
23 P_ATTRIBUTE8 in VARCHAR2,
24 P_ATTRIBUTE9 in VARCHAR2,
25 P_ATTRIBUTE10 in VARCHAR2,
26 P_ATTRIBUTE11 in VARCHAR2,
27 P_ATTRIBUTE12 in VARCHAR2,
28 P_ATTRIBUTE13 in VARCHAR2,
29 P_ATTRIBUTE14 in VARCHAR2,
30 P_ATTRIBUTE15 in VARCHAR2,
31 P_SOO_ATTRIBUTE_CATEGORY in VARCHAR2,
32 P_SOO_ATTRIBUTE1 in VARCHAR2,
33 P_SOO_ATTRIBUTE2 in VARCHAR2,
34 P_SOO_ATTRIBUTE3 in VARCHAR2
35 ) is
36
37 cursor C is select ROWID from CSD_RULE_CONDITIONS_B
38 where RULE_CONDITION_ID = PX_RULE_CONDITION_ID
39 ;
40
41 begin
42
43 select CSD_RULE_CONDITIONS_S1.nextval
44 into PX_RULE_CONDITION_ID
45 from dual;
46
47 insert into CSD_RULE_CONDITIONS_B (
48 RULE_CONDITION_ID,
49 OBJECT_VERSION_NUMBER,
50 CREATION_DATE,
51 CREATED_BY,
52 LAST_UPDATE_DATE,
53 LAST_UPDATED_BY,
54 LAST_UPDATE_LOGIN,
55 RULE_ID,
56 ATTRIBUTE_CATEGORY,
57 ATTRIBUTE1,
58 ATTRIBUTE2,
59 ATTRIBUTE3,
60 ATTRIBUTE4,
61 ATTRIBUTE5,
62 ATTRIBUTE6,
63 ATTRIBUTE7,
64 ATTRIBUTE8,
65 ATTRIBUTE9,
66 ATTRIBUTE10,
67 ATTRIBUTE11,
68 ATTRIBUTE12,
69 ATTRIBUTE13,
70 ATTRIBUTE14,
71 ATTRIBUTE15,
72 SOO_ATTRIBUTE_CATEGORY,
73 SOO_ATTRIBUTE1,
74 SOO_ATTRIBUTE2,
75 SOO_ATTRIBUTE3
76 ) values (
77 PX_RULE_CONDITION_ID,
78 P_OBJECT_VERSION_NUMBER,
79 P_CREATION_DATE,
80 P_CREATED_BY,
81 P_LAST_UPDATE_DATE,
82 P_LAST_UPDATED_BY,
83 P_LAST_UPDATE_LOGIN,
84 P_RULE_ID,
85 P_ATTRIBUTE_CATEGORY,
86 P_ATTRIBUTE1,
87 P_ATTRIBUTE2,
88 P_ATTRIBUTE3,
89 P_ATTRIBUTE4,
90 P_ATTRIBUTE5,
91 P_ATTRIBUTE6,
92 P_ATTRIBUTE7,
93 P_ATTRIBUTE8,
94 P_ATTRIBUTE9,
95 P_ATTRIBUTE10,
96 P_ATTRIBUTE11,
97 P_ATTRIBUTE12,
98 P_ATTRIBUTE13,
99 P_ATTRIBUTE14,
100 P_ATTRIBUTE15,
101 P_SOO_ATTRIBUTE_CATEGORY,
102 P_SOO_ATTRIBUTE1,
103 P_SOO_ATTRIBUTE2,
104 P_SOO_ATTRIBUTE3
105 );
106
107 insert into CSD_RULE_CONDITIONS_TL (
108 RULE_CONDITION_ID,
109 NAME,
110 CREATED_BY,
111 CREATION_DATE,
112 LAST_UPDATED_BY,
113 LAST_UPDATE_DATE,
114 LAST_UPDATE_LOGIN,
115 LANGUAGE,
116 SOURCE_LANG
117 ) select
118 PX_RULE_CONDITION_ID,
119 P_NAME,
120 P_CREATED_BY,
121 P_CREATION_DATE,
122 P_LAST_UPDATED_BY,
123 P_LAST_UPDATE_DATE,
124 P_LAST_UPDATE_LOGIN,
125 L.LANGUAGE_CODE,
126 userenv('LANG')
127 from FND_LANGUAGES L
128 where L.INSTALLED_FLAG in ('I', 'B')
129 and not exists
130 (select NULL
131 from CSD_RULE_CONDITIONS_TL T
132 where T.RULE_CONDITION_ID = PX_RULE_CONDITION_ID
133 and T.LANGUAGE = L.LANGUAGE_CODE);
134
135 open c;
136 fetch c into PX_ROWID;
137 if (c%notfound) then
138 close c;
139 raise no_data_found;
140 end if;
141 close c;
142
143 end INSERT_ROW;
144
145 procedure LOCK_ROW (
146 P_RULE_CONDITION_ID in NUMBER,
147 P_OBJECT_VERSION_NUMBER in NUMBER
148 ) is
149 cursor c is select
150 OBJECT_VERSION_NUMBER
151 from CSD_RULE_CONDITIONS_B
152 where RULE_CONDITION_ID = P_RULE_CONDITION_ID
153 for update of RULE_CONDITION_ID nowait;
154 recinfo c%rowtype;
155
156 begin
157
158 open c;
159 fetch c into recinfo;
160 if (c%notfound) then
161 close c;
162 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
163 app_exception.raise_exception;
164 end if;
165 close c;
166
167 if (recinfo.OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER) then
168 null;
169 else
170 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
171 app_exception.raise_exception;
172 end if;
173
174 /*
175 for tlinfo in c1 loop
176 if (tlinfo.BASELANG = 'Y') then
177 if ( ((tlinfo.DESCRIPTION = P_DESCRIPTION)
178 OR ((tlinfo.DESCRIPTION is null) AND (P_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 */
188
189 return;
190 end LOCK_ROW;
191
192 procedure UPDATE_ROW (
193 P_RULE_CONDITION_ID in NUMBER,
194 P_OBJECT_VERSION_NUMBER in NUMBER,
195 P_CREATION_DATE in DATE,
196 P_CREATED_BY in NUMBER,
197 P_LAST_UPDATE_DATE in DATE,
198 P_LAST_UPDATED_BY in NUMBER,
199 P_LAST_UPDATE_LOGIN in NUMBER,
200 P_NAME in VARCHAR2,
201 P_RULE_ID in NUMBER,
202 P_ATTRIBUTE_CATEGORY in VARCHAR2,
203 P_ATTRIBUTE1 in VARCHAR2,
204 P_ATTRIBUTE2 in VARCHAR2,
205 P_ATTRIBUTE3 in VARCHAR2,
206 P_ATTRIBUTE4 in VARCHAR2,
207 P_ATTRIBUTE5 in VARCHAR2,
208 P_ATTRIBUTE6 in VARCHAR2,
209 P_ATTRIBUTE7 in VARCHAR2,
210 P_ATTRIBUTE8 in VARCHAR2,
211 P_ATTRIBUTE9 in VARCHAR2,
212 P_ATTRIBUTE10 in VARCHAR2,
213 P_ATTRIBUTE11 in VARCHAR2,
214 P_ATTRIBUTE12 in VARCHAR2,
215 P_ATTRIBUTE13 in VARCHAR2,
216 P_ATTRIBUTE14 in VARCHAR2,
217 P_ATTRIBUTE15 in VARCHAR2,
218 P_SOO_ATTRIBUTE_CATEGORY in VARCHAR2,
219 P_SOO_ATTRIBUTE1 in VARCHAR2,
220 P_SOO_ATTRIBUTE2 in VARCHAR2,
221 P_SOO_ATTRIBUTE3 in VARCHAR2
222 ) is
223 begin
224 update CSD_RULE_CONDITIONS_B set
225 OBJECT_VERSION_NUMBER = decode( P_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, NULL, OBJECT_VERSION_NUMBER, P_OBJECT_VERSION_NUMBER)
226 ,CREATED_BY = decode( P_CREATED_BY, FND_API.G_MISS_NUM, NULL, NULL, CREATED_BY, P_CREATED_BY)
227 ,CREATION_DATE = decode( P_CREATION_DATE, FND_API.G_MISS_DATE, NULL, NULL, CREATION_DATE, P_CREATION_DATE)
228 ,LAST_UPDATED_BY = decode( P_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, NULL, LAST_UPDATED_BY, P_LAST_UPDATED_BY)
229 ,LAST_UPDATE_DATE = decode( P_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, NULL, LAST_UPDATE_DATE, P_LAST_UPDATE_DATE)
230 ,LAST_UPDATE_LOGIN = decode( P_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, NULL, LAST_UPDATE_LOGIN, P_LAST_UPDATE_LOGIN)
231
232 ,RULE_ID = decode( P_RULE_ID, FND_API.G_MISS_NUM, NULL, NULL, RULE_ID, P_RULE_ID)
233
234 ,ATTRIBUTE_CATEGORY = decode( P_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE_CATEGORY, P_ATTRIBUTE_CATEGORY)
235 ,ATTRIBUTE1 = decode( P_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE1, P_ATTRIBUTE1)
236 ,ATTRIBUTE2 = decode( P_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE2, P_ATTRIBUTE2)
237 ,ATTRIBUTE3 = decode( P_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE3, P_ATTRIBUTE3)
238 ,ATTRIBUTE4 = decode( P_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE4, P_ATTRIBUTE4)
239 ,ATTRIBUTE5 = decode( P_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE5, P_ATTRIBUTE5)
240 ,ATTRIBUTE6 = decode( P_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE6, P_ATTRIBUTE6)
241 ,ATTRIBUTE7 = decode( P_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE7, P_ATTRIBUTE7)
242 ,ATTRIBUTE8 = decode( P_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE8, P_ATTRIBUTE8)
243 ,ATTRIBUTE9 = decode( P_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE9, P_ATTRIBUTE9)
244 ,ATTRIBUTE10 = decode( P_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE10, P_ATTRIBUTE10)
245 ,ATTRIBUTE11 = decode( P_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE11, P_ATTRIBUTE11)
246 ,ATTRIBUTE12 = decode( P_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE12, P_ATTRIBUTE12)
247 ,ATTRIBUTE13 = decode( P_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE13, P_ATTRIBUTE13)
248 ,ATTRIBUTE14 = decode( P_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE14, P_ATTRIBUTE14)
249 ,ATTRIBUTE15 = decode( P_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE15, P_ATTRIBUTE15)
250 ,SOO_ATTRIBUTE_CATEGORY = decode( P_SOO_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, NULL, SOO_ATTRIBUTE_CATEGORY, P_SOO_ATTRIBUTE_CATEGORY)
251 ,SOO_ATTRIBUTE1 = decode( P_SOO_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, NULL, SOO_ATTRIBUTE1, P_SOO_ATTRIBUTE1)
252 ,SOO_ATTRIBUTE2 = decode( P_SOO_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, NULL, SOO_ATTRIBUTE2, P_SOO_ATTRIBUTE2)
253 ,SOO_ATTRIBUTE3 = decode( P_SOO_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, NULL, SOO_ATTRIBUTE3, P_SOO_ATTRIBUTE3)
254 where RULE_CONDITION_ID = P_RULE_CONDITION_ID;
255
256 if (sql%notfound) then
257 raise no_data_found;
258 end if;
259
260 update CSD_RULE_CONDITIONS_TL set
261 CREATED_BY = decode( P_CREATED_BY, FND_API.G_MISS_NUM, NULL, NULL, CREATED_BY, P_CREATED_BY)
262 ,CREATION_DATE = decode( P_CREATION_DATE, FND_API.G_MISS_DATE, NULL, NULL, CREATION_DATE, P_CREATION_DATE)
263 ,LAST_UPDATED_BY = decode( P_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, NULL, LAST_UPDATED_BY, P_LAST_UPDATED_BY)
264 ,LAST_UPDATE_DATE = decode( P_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, NULL, LAST_UPDATE_DATE, P_LAST_UPDATE_DATE)
265 ,LAST_UPDATE_LOGIN = decode( P_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, NULL, LAST_UPDATE_LOGIN, P_LAST_UPDATE_LOGIN)
266 ,NAME = decode( P_NAME, FND_API.G_MISS_CHAR, NULL, NULL, NAME, P_NAME)
267 ,SOURCE_LANG = userenv('LANG')
268 where RULE_CONDITION_ID = P_RULE_CONDITION_ID
269 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
270
271 if (sql%notfound) then
272 raise no_data_found;
273 end if;
274
275 end UPDATE_ROW;
276
277 procedure DELETE_ROW (
278 P_RULE_CONDITION_ID in NUMBER
279 ) is
280 begin
281 delete from CSD_RULE_CONDITIONS_TL
282 where RULE_CONDITION_ID = P_RULE_CONDITION_ID;
283
284 if (sql%notfound) then
285 raise no_data_found;
286 end if;
287
288 delete from CSD_RULE_CONDITIONS_B
289 where RULE_CONDITION_ID = P_RULE_CONDITION_ID;
290
291 if (sql%notfound) then
292 raise no_data_found;
293 end if;
294 end DELETE_ROW;
295
296 procedure ADD_LANGUAGE
297 is
298 begin
299 delete from CSD_RULE_CONDITIONS_TL T
300 where not exists
301 (select NULL
302 from CSD_RULE_CONDITIONS_B B
303 where B.RULE_CONDITION_ID = T.RULE_CONDITION_ID
304 );
305
306 update CSD_RULE_CONDITIONS_TL T set (
307 NAME
308 ) = (select
309 B.NAME
310 from CSD_RULE_CONDITIONS_TL B
311 where B.RULE_CONDITION_ID = T.RULE_CONDITION_ID
312 and B.LANGUAGE = T.SOURCE_LANG)
313 where (
314 T.RULE_CONDITION_ID,
315 T.LANGUAGE
316 ) in (select
317 SUBT.RULE_CONDITION_ID,
318 SUBT.LANGUAGE
319 from CSD_RULE_CONDITIONS_TL SUBB, CSD_RULE_CONDITIONS_TL SUBT
320 where SUBB.RULE_CONDITION_ID = SUBT.RULE_CONDITION_ID
321 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
322 and (SUBB.NAME <> SUBT.NAME
323 or (SUBB.NAME is null and SUBT.NAME is not null)
324 or (SUBB.NAME is not null and SUBT.NAME is null)
325 ));
326
327 insert into CSD_RULE_CONDITIONS_TL (
328 RULE_CONDITION_ID,
329 CREATED_BY,
330 CREATION_DATE,
331 LAST_UPDATED_BY,
332 LAST_UPDATE_DATE,
333 LAST_UPDATE_LOGIN,
334 NAME,
335 LANGUAGE,
336 SOURCE_LANG
337 ) select /*+ ORDERED */
338 B.RULE_CONDITION_ID,
339 B.CREATED_BY,
340 B.CREATION_DATE,
341 B.LAST_UPDATED_BY,
342 B.LAST_UPDATE_DATE,
343 B.LAST_UPDATE_LOGIN,
344 B.NAME,
345 L.LANGUAGE_CODE,
346 B.SOURCE_LANG
347 from CSD_RULE_CONDITIONS_TL B, FND_LANGUAGES L
348 where L.INSTALLED_FLAG in ('I', 'B')
349 and B.LANGUAGE = userenv('LANG')
350 and not exists
351 (select NULL
352 from CSD_RULE_CONDITIONS_TL T
353 where T.RULE_CONDITION_ID = B.RULE_CONDITION_ID
354 and T.LANGUAGE = L.LANGUAGE_CODE);
355 end ADD_LANGUAGE;
356
357 end CSD_RULE_CONDITIONS_PKG;