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