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