1 PACKAGE BODY DEFRULE_pkg AS
2 /* $Header: OEXVDRLB.pls 120.0 2005/06/01 00:19:00 appldev noship $ */
3
4
5 PROCEDURE Insert_Row(
6 p_rowid in out NOCOPY /* file.sql.39 change */ varchar2
7 ,p_condition_id in number
8 ,p_attr_def_condition_id in NUMBER
9 ,p_attr_def_rule_id in out NOCOPY /* file.sql.39 change */ NUMBER
10 ,p_sequence_no in NUMBER
11 ,p_database_object_name in VARCHAR2
12 ,p_attribute_code in VARCHAR2
13 ,p_src_type in VARCHAR2
14 ,p_src_api_pkg in VARCHAR2
15 ,p_src_api_fn in VARCHAR2
16 ,p_src_profile_option in VARCHAR2
17 ,p_src_constant_value in VARCHAR2
18 ,p_src_system_variable_expr in VARCHAR2
19 ,p_src_parameter_name in VARCHAR2
20 ,p_src_foreign_key_name in VARCHAR2
21 ,p_src_database_object_name in VARCHAR2
22 ,p_src_attribute_code in VARCHAR2
23 ,p_src_sequence_name in VARCHAR2
24 ,p_system_flag in VARCHAR2
25 ,p_permanent_flag in VARCHAR2
26 ,p_created_by in number
27 ,p_creation_date in date
28 ,p_last_updated_by in number
29 ,p_last_update_date in date
30 ,p_last_update_login in number
31 ,p_attribute_category in varchar2
32 ,p_attribute1 in varchar2
33 ,p_attribute2 in varchar2
34 ,p_attribute3 in varchar2
35 ,p_attribute4 in varchar2
36 ,p_attribute5 in varchar2
37 ,p_attribute6 in varchar2
38 ,p_attribute7 in varchar2
39 ,p_attribute8 in varchar2
40 ,p_attribute9 in varchar2
41 ,p_attribute10 in varchar2
42 ,p_attribute11 in varchar2
43 ,p_attribute12 in varchar2
44 ,p_attribute13 in varchar2
45 ,p_attribute14 in varchar2
46 ,p_attribute15 in varchar2
47 ) IS
48
49 CURSOR C IS SELECT rowid from OE_DEF_attr_def_rules
50 WHERE attr_def_rule_id = p_attr_def_rule_id;
51
52 BEGIN
53
54 INSERT INTO OE_DEF_ATTR_DEF_RULES(
55 attr_def_condition_id,
56 attr_def_rule_id,
57 sequence_no,
58 database_object_name,
59 attribute_code,
60 src_type,
61 src_api_pkg,
62 src_api_fn,
63 src_profile_option,
64 src_constant_value,
65 src_system_variable_expr,
66 src_parameter_name,
67 src_foreign_key_name,
68 src_database_object_name,
69 src_attribute_code,
70 system_flag ,
71 permanent_flag,
72 created_by,
73 creation_date,
74 last_updated_by,
75 last_update_date,last_update_login)
76
77 VALUES (
78 p_attr_def_condition_id,
79 p_attr_def_rule_id,
80 p_sequence_no,
81 p_database_object_name,
82 p_attribute_code,
83 p_src_type,
84 p_src_api_pkg,
85 p_src_api_fn,
86 p_src_profile_option,
87 p_src_constant_value,
88 p_src_system_variable_expr,
89 p_src_parameter_name,
90 p_src_foreign_key_name,
91 p_src_database_object_name,
92 p_src_attribute_code,
93 p_system_flag ,
94 p_permanent_flag,
95 p_created_by,
96 p_creation_date,
97 p_last_updated_by,
98 p_last_update_date,
99 p_last_update_login);
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 exception
111 when others then
112
113 APP_EXCEPTION.RAISE_EXCEPTION;
114 END Insert_Row;
115
116
117
118 PROCEDURE Update_Row(
119 p_rowid in out NOCOPY /* file.sql.39 change */ varchar2
120 ,p_condition_id in number
121 ,p_attr_def_condition_id in NUMBER
122 ,p_attr_def_rule_id in NUMBER
123 ,p_sequence_no in NUMBER
124 ,p_database_object_name in VARCHAR2
125 ,p_attribute_code in VARCHAR2
126 ,p_src_type in VARCHAR2
127 ,p_src_api_pkg in VARCHAR2
128 ,p_src_api_fn in VARCHAR2
129 ,p_src_profile_option in VARCHAR2
130 ,p_src_constant_value in VARCHAR2
131 ,p_src_system_variable_expr in VARCHAR2
132 ,p_src_parameter_name in VARCHAR2
133 ,p_src_foreign_key_name in VARCHAR2
134 ,p_src_database_object_name in VARCHAR2
135 ,p_src_attribute_code in VARCHAR2
136 ,p_src_sequence_name in VARCHAR2
137 ,p_system_flag in VARCHAR2
138 ,p_permanent_flag in VARCHAR2
139 ,p_created_by in number
140 ,p_creation_date in date
141 ,p_last_updated_by in number
142 ,p_last_update_date in date
143 ,p_last_update_login in number
144 ,p_attribute_category in varchar2
145 ,p_attribute1 in varchar2
146 ,p_attribute2 in varchar2
147 ,p_attribute3 in varchar2
148 ,p_attribute4 in varchar2
149 ,p_attribute5 in varchar2
150 ,p_attribute6 in varchar2
151 ,p_attribute7 in varchar2
152 ,p_attribute8 in varchar2
153 ,p_attribute9 in varchar2
154 ,p_attribute10 in varchar2
155 ,p_attribute11 in varchar2
156 ,p_attribute12 in varchar2
157 ,p_attribute13 in varchar2
158 ,p_attribute14 in varchar2
159 ,p_attribute15 in varchar2
160 ) IS
161
162 BEGIN
163
164 UPDATE OE_DEF_ATTR_DEF_RULES
165 set sequence_no = p_sequence_no,
166 database_object_name = p_database_object_name,
167 attribute_code=p_attribute_code,
168 src_type = p_src_type,
169 src_api_pkg = p_src_api_pkg,
170 src_api_fn = p_src_api_fn,
171 src_profile_option = p_src_profile_option,
172 src_constant_value = p_src_constant_value,
173 src_system_variable_expr = p_src_system_variable_expr,
174 src_parameter_name = p_src_parameter_name,
175 src_foreign_key_name = p_src_foreign_key_name,
176 src_database_object_name = p_src_database_object_name,
177 src_attribute_code = p_src_attribute_code,
178 last_updated_by=p_last_updated_by,
179 last_update_date=p_last_update_date
180 WHERE rowid = p_rowid;
181
182 IF (SQL%NOTFOUND) then
183 Raise NO_DATA_FOUND;
184 end if;
185
186 END Update_Row;
187
188
189 PROCEDURE Delete_Row(p_Rowid VARCHAR2,
190 p_system_flag varchar2,
191 p_permanent_flag varchar2) IS
192 BEGIN
193
194 DELETE FROM OE_DEF_ATTR_DEF_RULES
195 WHERE rowid = p_rowid;
196
197
198 if (SQL%NOTFOUND) then
199 Raise NO_DATA_FOUND;
200 end if;
201
202 END Delete_Row;
203
204
205 PROCEDURE Lock_Row(
206 p_rowid in out NOCOPY /* file.sql.39 change */ varchar2
207 ,p_condition_id in number
208 ,p_attr_def_condition_id in NUMBER
209 ,p_attr_def_rule_id in NUMBER
210 ,p_sequence_no in NUMBER
211 ,p_database_object_name in VARCHAR2
212 ,p_attribute_code in VARCHAR2
213 ,p_src_type in VARCHAR2
214 ,p_src_api_pkg in VARCHAR2
215 ,p_src_api_fn in VARCHAR2
216 ,p_src_profile_option in VARCHAR2
217 ,p_src_constant_value in VARCHAR2
218 ,p_src_system_variable_expr in VARCHAR2
219 ,p_src_parameter_name in VARCHAR2
220 ,p_src_foreign_key_name in VARCHAR2
221 ,p_src_database_object_name in VARCHAR2
222 ,p_src_attribute_code in VARCHAR2
223 ,p_src_sequence_name in VARCHAR2
224 ,p_system_flag in VARCHAR2
225 ,p_permanent_flag in VARCHAR2
226 ,p_created_by in number
227 ,p_creation_date in date
228 ,p_last_updated_by in number
229 ,p_last_update_date in date
230 ,p_last_update_login in number
231 ,p_attribute_category in varchar2
232 ,p_attribute1 in varchar2
233 ,p_attribute2 in varchar2
234 ,p_attribute3 in varchar2
235 ,p_attribute4 in varchar2
236 ,p_attribute5 in varchar2
237 ,p_attribute6 in varchar2
238 ,p_attribute7 in varchar2
239 ,p_attribute8 in varchar2
240 ,p_attribute9 in varchar2
241 ,p_attribute10 in varchar2
242 ,p_attribute11 in varchar2
243 ,p_attribute12 in varchar2
244 ,p_attribute13 in varchar2
245 ,p_attribute14 in varchar2
246 ,p_attribute15 in varchar2
247 )
248 IS
249
250 CURSOR C IS
251 SELECT * FROM OE_DEF_ATTR_DEF_RULES
252 WHERE rowid = p_Rowid
253 FOR UPDATE OF attr_def_rule_id ;
254
255 Recinfo C%ROWTYPE;
256
257 BEGIN
258
259 OPEN C;
260 FETCH C into Recinfo;
261 IF (C%NOTFOUND) then
262 CLOSE C;
263 FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
264 APP_EXCEPTION.Raise_Exception;
265 END IF;
266 CLOSE C;
267
268
269 if (
270 (Recinfo.attr_def_rule_id = p_attr_def_rule_id)
271 AND (Recinfo.attr_def_condition_id = p_attr_def_condition_id)
272 AND (Recinfo.database_object_name = p_database_object_name)
273 AND (Recinfo.attribute_code = p_attribute_code)
274 AND (Recinfo.sequence_no = p_sequence_no)
275 AND (Recinfo.src_type = p_src_type)
276 AND (Recinfo.system_flag = p_system_flag)
277 AND (Recinfo.permanent_flag = p_permanent_flag)
278 AND ( (Recinfo.created_by = p_created_by)
279 OR ( ( Recinfo.created_by IS NULL)
280 AND ( p_created_by IS NULL)))
281 AND ( (Recinfo.creation_date = p_creation_date)
282 OR ( ( Recinfo.creation_date IS NULL)
283 AND ( p_creation_date IS NULL)))
284 AND ( (Recinfo.last_updated_by = p_last_updated_by)
285 OR ( ( Recinfo.last_updated_by IS NULL)
286 AND ( p_last_updated_by IS NULL)))
287 AND ( (Recinfo.last_update_date = p_last_update_date)
288 OR ( ( Recinfo.last_update_date IS NULL)
289 AND ( p_last_update_date IS NULL)))
290 AND ( (Recinfo.src_api_pkg = p_src_api_pkg)
291 OR ( ( Recinfo.src_api_pkg IS NULL)
292 AND ( p_src_api_pkg IS NULL)))
293 AND ( (Recinfo.src_api_fn = p_src_api_fn)
294 OR ( ( Recinfo.src_api_fn IS NULL)
295 AND ( p_src_api_fn IS NULL)))
296 AND ( (Recinfo.src_profile_option = p_src_profile_option)
297 OR ( ( Recinfo.src_profile_option IS NULL)
298 AND ( p_src_profile_option IS NULL)))
299 AND ( (Recinfo.src_constant_value = p_src_constant_value)
300 OR ( ( Recinfo.src_constant_value IS NULL)
301 AND ( p_src_constant_value IS NULL)))
302 AND ( (Recinfo.src_system_variable_expr = p_src_system_variable_expr)
303 OR ( ( Recinfo.src_system_variable_expr IS NULL)
304 AND ( p_src_system_variable_expr IS NULL)))
305 AND ( (Recinfo.src_parameter_name = p_src_parameter_name)
306 OR ( ( Recinfo.src_parameter_name IS NULL)
307 AND ( p_src_parameter_name IS NULL)))
308 AND ( (Recinfo.src_foreign_key_name = p_src_foreign_key_name)
309 OR ( ( Recinfo.src_foreign_key_name IS NULL)
310 AND ( p_src_foreign_key_name IS NULL)))
311 AND ( (Recinfo.src_database_object_name = p_src_database_object_name)
312 OR ( ( Recinfo.src_database_object_name IS NULL)
313 AND ( p_src_database_object_name IS NULL)))
314 AND ( (Recinfo.src_attribute_code = p_src_attribute_code)
315 OR ( ( Recinfo.src_attribute_code IS NULL)
316 AND ( p_src_attribute_code IS NULL)))
317 AND ( (Recinfo.src_sequence_name = p_src_sequence_name)
318 OR ( ( Recinfo.src_sequence_name IS NULL)
319 AND ( p_src_sequence_name IS NULL)))
320 AND ( (Recinfo.last_update_login = p_last_update_login)
321 OR ( ( Recinfo.last_update_login IS NULL)
322 AND ( p_last_update_login IS NULL)))
323 AND ( (Recinfo.attribute_category = p_attribute_category)
324 OR ( ( Recinfo.attribute_category IS NULL)
325 AND ( p_attribute_category IS NULL)))
326 AND ( (Recinfo.attribute1 = p_attribute1)
327 OR ( ( Recinfo.attribute1 IS NULL)
328 AND ( p_attribute1 IS NULL)))
329 AND ( (Recinfo.attribute2 = p_attribute2)
330 OR ( ( Recinfo.attribute2 IS NULL)
331 AND ( p_attribute2 IS NULL)))
332 AND ( (Recinfo.attribute3 = p_attribute3)
333 OR ( ( Recinfo.attribute3 IS NULL)
334 AND ( p_attribute3 IS NULL)))
335 AND ( (Recinfo.attribute4 = p_attribute4)
336 OR ( ( Recinfo.attribute4 IS NULL)
337 AND ( p_attribute4 IS NULL)))
338 AND ( (Recinfo.attribute5 = p_attribute5)
339 OR ( ( Recinfo.attribute5 IS NULL)
340 AND ( p_attribute5 IS NULL)))
341 AND ( (Recinfo.attribute6 = p_attribute6)
342 OR ( ( Recinfo.attribute6 IS NULL)
343 AND ( p_attribute6 IS NULL)))
344 AND ( (Recinfo.attribute7 = p_attribute7)
345 OR ( ( Recinfo.attribute7 IS NULL)
346 AND ( p_attribute7 IS NULL)))
347 AND ( (Recinfo.attribute8 = p_attribute8)
348 OR ( ( Recinfo.attribute8 IS NULL)
349 AND ( p_attribute8 IS NULL)))
350 AND ( (Recinfo.attribute9 = p_attribute9)
351 OR ( ( Recinfo.attribute9 IS NULL)
352 AND ( p_attribute9 IS NULL)))
353 AND ( (Recinfo.attribute10 = p_attribute10)
354 OR ( ( Recinfo.attribute10 IS NULL)
355 AND ( p_attribute10 IS NULL)))
356 AND ( (Recinfo.attribute11 = p_attribute11)
357 OR ( ( Recinfo.attribute11 IS NULL)
358 AND ( p_attribute11 IS NULL)))
359 AND ( (Recinfo.attribute12 = p_attribute12)
360 OR ( ( Recinfo.attribute12 IS NULL)
361 AND ( p_attribute12 IS NULL)))
362 AND ( (Recinfo.attribute13 = p_attribute13)
363 OR ( ( Recinfo.attribute13 IS NULL)
364 AND ( p_attribute13 IS NULL)))
365 AND ( (Recinfo.attribute14 = p_attribute14)
366 OR ( ( Recinfo.attribute14 IS NULL)
367 AND ( p_attribute14 IS NULL)))
368 AND ( (Recinfo.attribute15 = p_attribute15)
369 OR ( ( Recinfo.attribute15 IS NULL)
370 AND ( p_attribute15 IS NULL)))
371 )
372 then return;
373 else
374 FND_MESSAGE.set_Name('FND','FORM_RECORD_CHANGED');
375 APP_EXCEPTION.Raise_Exception;
376 end if;
377
378
379 END Lock_Row;
380
381 Function check_Unique(p_attr_def_condition_id in NUMBER,
382 p_database_object_name in VARCHAR2,
383 p_attribute_code in VARCHAR2,
384 p_sequence_no in NUMBER) RETURN BOOLEAN is
385 dmy_result varchar2(20);
386 begin
387 select 'dup sequence'
388 into dmy_result
389 from oe_def_attr_rules_v
390 where attr_def_condition_id = p_attr_def_condition_id
391 AND database_object_name = p_database_object_name
392 AND attribute_code = p_attribute_code
393 AND SEQUENCE_NO = p_SEQUENCE_NO
394 and rownum =1;
395
396 return TRUE;
397 exception when no_data_found then return FALSE;
398
399 end;
400
401 END DEFRULE_pkg;
[Home] [Help]