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