1 PACKAGE BODY DEFCONDNRULE_pkg AS
2 /* $Header: OEXVDCRB.pls 120.0 2005/06/01 01:06:21 appldev noship $ */
3
4 PROCEDURE Insert_Row(
5 p_rowid in out NOCOPY /* file.sql.39 change */ varchar2
6 ,p_attr_def_condition_id in out NOCOPY /* file.sql.39 change */ number
7 ,p_condition_id in number
8 ,p_precedence in number
9 ,p_database_object_name in varchar2
10 ,p_attribute_code in varchar2
11 ,p_created_by in number
12 ,p_creation_date in date
13 ,p_last_updated_by in number
14 ,p_last_update_date in date
15 ,p_last_update_login in number
16 ,p_system_flag in varchar2
17 ,p_enabled_flag in varchar2
18 ,p_attribute_category in varchar2
19 ,p_attribute1 in varchar2
20 ,p_attribute2 in varchar2
21 ,p_attribute3 in varchar2
22 ,p_attribute4 in varchar2
23 ,p_attribute5 in varchar2
24 ,p_attribute6 in varchar2
25 ,p_attribute7 in varchar2
26 ,p_attribute8 in varchar2
27 ,p_attribute9 in varchar2
28 ,p_attribute10 in varchar2
29 ,p_attribute11 in varchar2
30 ,p_attribute12 in varchar2
31 ,p_attribute13 in varchar2
32 ,p_attribute14 in varchar2
33 ,p_attribute15 in varchar2
34 ) IS
35
36 CURSOR C IS SELECT rowid from OE_DEF_attr_condns
37 WHERE attr_def_condition_id = p_attr_def_condition_id;
38
39 BEGIN
40
41 INSERT INTO OE_DEF_ATTR_CONDNS(
42 condition_id,
43 attr_def_condition_id,
44 precedence,
45 database_object_name,
46 attribute_code
47 ,created_by
48 ,creation_date
49 ,last_updated_by
50 ,last_update_date
51 ,last_update_login
52 ,system_flag
53 ,enabled_flag
54 ,attribute_category
55 ,attribute1
56 ,attribute2
57 ,attribute3
58 ,attribute4
59 ,attribute5
60 ,attribute6
61 ,attribute7
62 ,attribute8
63 ,attribute9
64 ,attribute10
65 ,attribute11
66 ,attribute12
67 ,attribute13
68 ,attribute14
69 ,attribute15)
70
71 VALUES(
72 p_condition_id,
73 p_attr_def_condition_id,
74 p_precedence,
75 p_database_object_name,
76 p_attribute_code
77 ,p_created_by
78 ,p_creation_date
79 ,p_last_updated_by
80 ,p_last_update_date
81 ,p_last_update_login
82 ,p_system_flag
83 ,p_enabled_flag
84 ,p_attribute_category
85 ,p_attribute1
86 ,p_attribute2
87 ,p_attribute3
88 ,p_attribute4
89 ,p_attribute5
90 ,p_attribute6
91 ,p_attribute7
92 ,p_attribute8
93 ,p_attribute9
94 ,p_attribute10
95 ,p_attribute11
96 ,p_attribute12
97 ,p_attribute13
98 ,p_attribute14
99 ,p_attribute15);
100
101 OPEN C;
102 FETCH C INTO p_rowid;
103 if (C%NOTFOUND) THEN
104 CLOSE C;
105 Raise NO_DATA_FOUND;
106 else
107 CLOSE C;
108 end if;
109
110 END Insert_Row;
111
112
113 PROCEDURE Update_Row(
114 p_rowid in varchar2
115 ,p_attr_def_condition_id in number
116 ,p_condition_id in number
117 ,p_precedence in number
118 ,p_database_object_name in varchar2
119 ,p_attribute_code in varchar2
120 ,p_enabled_flag in varchar2
121 ,p_created_by in number
122 ,p_creation_date in date
123 ,p_last_updated_by in number
124 ,p_last_update_date in date
125 ,p_last_update_login in number
126 ,p_attribute_category in varchar2
127 ,p_attribute1 in varchar2
128 ,p_attribute2 in varchar2
129 ,p_attribute3 in varchar2
130 ,p_attribute4 in varchar2
131 ,p_attribute5 in varchar2
132 ,p_attribute6 in varchar2
133 ,p_attribute7 in varchar2
134 ,p_attribute8 in varchar2
135 ,p_attribute9 in varchar2
136 ,p_attribute10 in varchar2
137 ,p_attribute11 in varchar2
138 ,p_attribute12 in varchar2
139 ,p_attribute13 in varchar2
140 ,p_attribute14 in varchar2
141 ,p_attribute15 in varchar2
142 ) IS
143
144 BEGIN
145
146 UPDATE OE_DEF_ATTR_CONDNS
147 SET precedence = p_precedence
148 ,condition_id = p_condition_id
149 ,enabled_flag = p_enabled_flag
150 ,created_by = p_created_by
151 ,creation_date = p_creation_date
152 ,last_updated_by = p_last_updated_by
153 ,last_update_date = p_last_update_date
154 ,last_update_login = p_last_update_login
155 ,attribute_category = p_attribute_category
156 ,attribute1 = p_attribute1
157 ,attribute2 = p_attribute2
158 ,attribute3 = p_attribute3
159 ,attribute4 = p_attribute4
160 ,attribute5 = p_attribute5
161 ,attribute6 = p_attribute6
162 ,attribute7 = p_attribute7
163 ,attribute8 = p_attribute8
164 ,attribute9 = p_attribute9
165 ,attribute10 = p_attribute10
166 ,attribute11 = p_attribute11
167 ,attribute12 = p_attribute12
168 ,attribute13 = p_attribute13
169 ,attribute14 = p_attribute14
170 ,attribute15 = p_attribute15
171 WHERE rowid = p_rowid;
172
173 IF (SQL%NOTFOUND) then
174 Raise NO_DATA_FOUND;
175 end if;
176
177 END Update_Row;
178
179
180 PROCEDURE Delete_Row(p_Rowid VARCHAR2,
181 p_attr_def_condition_id NUMBER) IS
182 BEGIN
183
184 DELETE FROM OE_DEF_ATTR_CONDNS
185 WHERE rowid = p_rowid;
186
187 if (SQL%NOTFOUND) then
188 Raise NO_DATA_FOUND;
189 end if;
190
191
192 END Delete_Row;
193
194
195 PROCEDURE Lock_Row(
196 p_rowid in out NOCOPY /* file.sql.39 change */ varchar2
197 ,p_attr_def_condition_id in out NOCOPY /* file.sql.39 change */ number
198 ,p_condition_id in number
199 ,p_precedence in number
200 ,p_database_object_name in varchar2
201 ,p_attribute_code in varchar2
202 ,p_enabled_flag in varchar2
203 ,p_created_by in number
204 ,p_creation_date in date
205 ,p_last_updated_by in number
206 ,p_last_update_date in date
207 ,p_last_update_login in number
208 ,p_attribute_category in varchar2
209 ,p_attribute1 in varchar2
210 ,p_attribute2 in varchar2
211 ,p_attribute3 in varchar2
212 ,p_attribute4 in varchar2
213 ,p_attribute5 in varchar2
214 ,p_attribute6 in varchar2
215 ,p_attribute7 in varchar2
216 ,p_attribute8 in varchar2
217 ,p_attribute9 in varchar2
218 ,p_attribute10 in varchar2
219 ,p_attribute11 in varchar2
220 ,p_attribute12 in varchar2
221 ,p_attribute13 in varchar2
222 ,p_attribute14 in varchar2
223 ,p_attribute15 in varchar2
224 ) IS
225
226 CURSOR C IS
227 SELECT * FROM OE_DEF_ATTR_CONDNS
228 WHERE rowid = p_Rowid
229 FOR UPDATE OF attr_def_condition_id ;
230
231 Recinfo C%ROWTYPE;
232
233 BEGIN
234 OPEN C;
235 FETCH C into Recinfo;
236
237 IF (C%NOTFOUND) then
238 CLOSE C;
239 FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
240 APP_EXCEPTION.Raise_Exception;
241 end if;
242 CLOSE C;
243
244 if (
245 (Recinfo.condition_id = p_condition_id)
246 AND (Recinfo.attr_def_Condition_id = p_attr_def_Condition_id)
247 AND (Recinfo.database_object_name = p_database_object_name)
248 AND (Recinfo.attribute_code = p_attribute_code)
249 AND (Recinfo.precedence = p_precedence)
250 AND (Recinfo.enabled_flag = p_enabled_flag)
251 AND ( (Recinfo.attribute_category = p_attribute_category)
252 OR ( ( Recinfo.attribute_category IS NULL)
253 AND (p_attribute_category is NULL)))
254 AND ( (Recinfo.attribute1 = p_attribute1)
255 OR ( ( Recinfo.attribute1 IS NULL)
256 AND (p_attribute1 is NULL)))
257 AND ( (Recinfo.attribute2 = p_attribute2)
258 OR ( ( Recinfo.attribute2 IS NULL)
259 AND (p_attribute2 is NULL)))
260 AND ( (Recinfo.attribute3 = p_attribute3)
261 OR ( ( Recinfo.attribute3 IS NULL)
262 AND (p_attribute3 is NULL)))
263 AND ( (Recinfo.attribute4 = p_attribute4)
264 OR ( ( Recinfo.attribute4 IS NULL)
265 AND (p_attribute4 is NULL)))
266 AND ( (Recinfo.attribute5 = p_attribute5)
267 OR ( ( Recinfo.attribute5 IS NULL)
268 AND (p_attribute5 is NULL)))
269 AND ( (Recinfo.attribute6 = p_attribute6)
270 OR ( ( Recinfo.attribute6 IS NULL)
271 AND (p_attribute6 is NULL)))
272 AND ( (Recinfo.attribute7 = p_attribute7)
273 OR ( ( Recinfo.attribute7 IS NULL)
274 AND (p_attribute7 is NULL)))
275 AND ( (Recinfo.attribute8 = p_attribute8)
276 OR ( ( Recinfo.attribute8 IS NULL)
277 AND (p_attribute8 is NULL)))
278 AND ( (Recinfo.attribute9 = p_attribute9)
279 OR ( ( Recinfo.attribute9 IS NULL)
280 AND (p_attribute9 is NULL)))
281 AND ( (Recinfo.attribute10 = p_attribute10)
282 OR ( ( Recinfo.attribute10 IS NULL)
283 AND (p_attribute10 is NULL)))
284 AND ( (Recinfo.attribute11 = p_attribute11)
285 OR ( ( Recinfo.attribute11 IS NULL)
286 AND (p_attribute11 is NULL)))
287 AND ( (Recinfo.attribute12 = p_attribute12)
288 OR ( ( Recinfo.attribute12 IS NULL)
289 AND (p_attribute12 is NULL)))
290 AND ( (Recinfo.attribute13 = p_attribute13)
291 OR ( ( Recinfo.attribute13 IS NULL)
292 AND (p_attribute13 is NULL)))
293 AND ( (Recinfo.attribute14 = p_attribute14)
294 OR ( ( Recinfo.attribute14 IS NULL)
295 AND (p_attribute14 is NULL)))
296 AND ( (Recinfo.attribute15 = p_attribute15)
297 OR ( ( Recinfo.attribute15 IS NULL)
298 AND (p_attribute15 is NULL)))
299 )
300 then return;
301 else
302 FND_MESSAGE.set_Name('FND','FORM_RECORD_CHANGED');
303 APP_EXCEPTION.Raise_Exception;
304 end if;
305
306 END Lock_Row;
307
308 FUNCTION Check_References(p_attr_def_condition_id NUMBER) RETURN BOOLEAN IS
309
310 dummy NUMBER;
311
312 BEGIN
313
314 select 1 into dummy from dual where exists
315 (select 1 from oe_def_attr_def_RULES
316 where attr_def_condition_id = p_attr_def_condition_id);
317
318 if (SQL%NOTFOUND) or (dummy =0 ) then
319 RETURN TRUE;
320 else
321 RETURN FALSE;
322 end if;
323
324 EXCEPTION
325 WHEN NO_DATA_FOUND THEN
326 return TRUE;
327 WHEN OTHERS THEN
328 return FALSE;
329
330 END Check_References;
331
332 PROCEDURE Check_Unique(p_rowid VARCHAR2,
333 p_database_object_name VARCHAR2,
334 p_attribute_code VARCHAR2,
335 p_condition_id NUMBER) IS
336 dummy NUMBER;
337 BEGIN
338
339 SELECT COUNT(1)
340 INTO dummy
341 FROM OE_DEF_ATTR_CONDNS
342 WHERE condition_id = p_condition_id
343 AND database_object_name=p_database_object_name
344 AND attribute_code = p_attribute_code
345 AND ((p_rowid IS NULL) OR (ROWID <> p_rowid));
346
347 IF (dummy >=1 ) THEN
348 FND_MESSAGE.SET_NAME('OE','OE_DUP_CONDN');
349 APP_EXCEPTION.RAISE_EXCEPTION;
350 END IF;
351
352 END Check_Unique;
353 END DEFCONDNRULE_pkg;
[Home] [Help]