[Home] [Help]
PACKAGE BODY: APPS.FUN_RULE_DFF_PKG
Source
1 PACKAGE BODY FUN_RULE_DFF_PKG AS
2 /*$Header: FUNXTMRULDFFTBB.pls 120.1 2006/02/22 10:51:43 ammishra noship $ */
3
4 g_is_use_instance BOOLEAN := FALSE;
5
6 PROCEDURE Process_Row (
7 X_TABLE_NAME IN VARCHAR2,
8 X_RULE_DETAIL_ID IN NUMBER,
9 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
10 X_ATTRIBUTE1 IN VARCHAR2,
11 X_ATTRIBUTE2 IN VARCHAR2,
12 X_ATTRIBUTE3 IN VARCHAR2,
13 X_ATTRIBUTE4 IN VARCHAR2,
14 X_ATTRIBUTE5 IN VARCHAR2,
15 X_ATTRIBUTE6 IN VARCHAR2,
16 X_ATTRIBUTE7 IN VARCHAR2,
17 X_ATTRIBUTE8 IN VARCHAR2,
18 X_ATTRIBUTE9 IN VARCHAR2,
19 X_ATTRIBUTE10 IN VARCHAR2,
20 X_ATTRIBUTE11 IN VARCHAR2,
21 X_ATTRIBUTE12 IN VARCHAR2,
22 X_ATTRIBUTE13 IN VARCHAR2,
23 X_ATTRIBUTE14 IN VARCHAR2,
24 X_ATTRIBUTE15 IN VARCHAR2,
25 X_RULE_OBJECT_ID IN NUMBER
26 ) IS
27 l_num NUMBER := 0;
28 source_cursor INTEGER;
29 ignore INTEGER;
30
31 BEGIN
32
33 /*Determine, if the Rule Object Id passed is a Rule Object Instance or Not.
34 If its a rule object instance, then the Rule Obejct Id will be populated in
35 the DFF table, else the Rule Object id will be a NULL value.
36 */
37
38 g_is_use_instance := FUN_RULE_UTILITY_PKG.IS_USE_INSTANCE(X_RULE_OBJECT_ID);
39
40 -- Prepare a cursor to select from the source table:
41
42 source_cursor := dbms_sql.open_cursor;
43 DBMS_SQL.PARSE(source_cursor,
44 'SELECT COUNT(1) FROM ' || X_TABLE_NAME || ' WHERE RULE_DETAIL_ID = :X_RULE_DETAIL_ID' ,
45 DBMS_SQL.native);
46 DBMS_SQL.BIND_VARIABLE(source_cursor, 'X_RULE_DETAIL_ID', X_RULE_DETAIL_ID);
47
48 if(g_is_use_instance) then
49 DBMS_SQL.PARSE(source_cursor,
50 'SELECT COUNT(1) FROM ' || X_TABLE_NAME || ' WHERE RULE_DETAIL_ID = :X_RULE_DETAIL_ID AND RULE_OBJECT_ID = :X_RULE_OBJECT_ID' ,
51 DBMS_SQL.native);
52 DBMS_SQL.BIND_VARIABLE(source_cursor, 'X_RULE_DETAIL_ID', X_RULE_DETAIL_ID);
53 DBMS_SQL.BIND_VARIABLE(source_cursor, 'X_RULE_OBJECT_ID', X_RULE_OBJECT_ID);
54 end if;
55
56
57 DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, l_num);
58 ignore := DBMS_SQL.EXECUTE(source_cursor);
59
60 IF DBMS_SQL.FETCH_ROWS(source_cursor)>0 THEN
61 -- get column values of the row
62 DBMS_SQL.COLUMN_VALUE(source_cursor, 1, l_num);
63 END IF;
64
65 IF (l_num = 0) THEN
66 FUN_RULE_DFF_PKG.Insert_Row (
67 X_TABLE_NAME,
68 X_RULE_DETAIL_ID,
69 X_ATTRIBUTE_CATEGORY,
70 X_ATTRIBUTE1,
71 X_ATTRIBUTE2,
72 X_ATTRIBUTE3,
73 X_ATTRIBUTE4,
74 X_ATTRIBUTE5,
75 X_ATTRIBUTE6,
76 X_ATTRIBUTE7,
77 X_ATTRIBUTE8,
78 X_ATTRIBUTE9,
79 X_ATTRIBUTE10,
80 X_ATTRIBUTE11,
81 X_ATTRIBUTE12,
82 X_ATTRIBUTE13,
83 X_ATTRIBUTE14,
84 X_ATTRIBUTE15,
85 X_RULE_OBJECT_ID);
86 ELSE
87 FUN_RULE_DFF_PKG.Update_Row (
88 X_TABLE_NAME,
89 X_RULE_DETAIL_ID,
90 X_ATTRIBUTE_CATEGORY,
91 X_ATTRIBUTE1,
92 X_ATTRIBUTE2,
93 X_ATTRIBUTE3,
94 X_ATTRIBUTE4,
95 X_ATTRIBUTE5,
96 X_ATTRIBUTE6,
97 X_ATTRIBUTE7,
98 X_ATTRIBUTE8,
99 X_ATTRIBUTE9,
100 X_ATTRIBUTE10,
101 X_ATTRIBUTE11,
102 X_ATTRIBUTE12,
103 X_ATTRIBUTE13,
104 X_ATTRIBUTE14,
105 X_ATTRIBUTE15,
106 X_RULE_OBJECT_ID);
107
108 END IF;
109 COMMIT;
110 DBMS_SQL.CLOSE_CURSOR(source_cursor);
111
112 END;
113
114 PROCEDURE Insert_Row (
115 X_TABLE_NAME IN VARCHAR2,
116 X_RULE_DETAIL_ID IN NUMBER,
117 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
118 X_ATTRIBUTE1 IN VARCHAR2,
119 X_ATTRIBUTE2 IN VARCHAR2,
120 X_ATTRIBUTE3 IN VARCHAR2,
121 X_ATTRIBUTE4 IN VARCHAR2,
122 X_ATTRIBUTE5 IN VARCHAR2,
123 X_ATTRIBUTE6 IN VARCHAR2,
124 X_ATTRIBUTE7 IN VARCHAR2,
125 X_ATTRIBUTE8 IN VARCHAR2,
126 X_ATTRIBUTE9 IN VARCHAR2,
127 X_ATTRIBUTE10 IN VARCHAR2,
128 X_ATTRIBUTE11 IN VARCHAR2,
129 X_ATTRIBUTE12 IN VARCHAR2,
130 X_ATTRIBUTE13 IN VARCHAR2,
131 X_ATTRIBUTE14 IN VARCHAR2,
132 X_ATTRIBUTE15 IN VARCHAR2,
133 X_RULE_OBJECT_ID IN NUMBER
134 ) IS
135
136 destination_cursor INTEGER;
137 ignore INTEGER;
138
139
140 BEGIN
141
142 /* Rule Object Instance Enhancement for MULTIVALUE:
143 * Construct the dynamic SQL to use RULE_OBJECT_ID depending on if the Rule Object
144 * uses Instances or not.
145 */
146 -- Prepare a cursor to insert into the destination table:
147 destination_cursor := DBMS_SQL.OPEN_CURSOR;
148 if(g_is_use_instance) then
149 DBMS_SQL.PARSE(destination_cursor,
150 'INSERT INTO ' || X_TABLE_NAME ||' ( '||
151 ' RULE_DETAIL_ID, '||
152 ' ATTRIBUTE_CATEGORY, '||
153 ' ATTRIBUTE1, '||
154 ' ATTRIBUTE2, '||
155 ' ATTRIBUTE3, '||
156 ' ATTRIBUTE4, '||
157 ' ATTRIBUTE5, '||
158 ' ATTRIBUTE6, '||
159 ' ATTRIBUTE7, '||
160 ' ATTRIBUTE8, '||
161 ' ATTRIBUTE9, '||
162 ' ATTRIBUTE10, '||
163 ' ATTRIBUTE11, '||
164 ' ATTRIBUTE12, '||
165 ' ATTRIBUTE13, '||
166 ' ATTRIBUTE14, '||
167 ' ATTRIBUTE15, '||
168 ' CREATED_BY, '||
169 ' CREATION_DATE, '||
170 ' LAST_UPDATE_LOGIN, '||
171 ' LAST_UPDATE_DATE, '||
172 ' LAST_UPDATED_BY, '||
173 ' RULE_OBJECT_ID '||
174 ' ) '||
175 ' VALUES( '||
176 ' :X_RULE_DETAIL_ID , '||
177 ' :X_ATTRIBUTE_CATEGORY , '||
178 ' :X_ATTRIBUTE1, '||
179 ' :X_ATTRIBUTE2, '||
180 ' :X_ATTRIBUTE3, '||
181 ' :X_ATTRIBUTE4, '||
182 ' :X_ATTRIBUTE5, '||
183 ' :X_ATTRIBUTE6, '||
184 ' :X_ATTRIBUTE7, '||
185 ' :X_ATTRIBUTE8, '||
186 ' :X_ATTRIBUTE9, '||
187 ' :X_ATTRIBUTE10, '||
188 ' :X_ATTRIBUTE11, '||
189 ' :X_ATTRIBUTE12, '||
190 ' :X_ATTRIBUTE13, '||
191 ' :X_ATTRIBUTE14, '||
192 ' :X_ATTRIBUTE15, '||
193 ' FUN_RULE_UTILITY_PKG.CREATED_BY, '||
194 ' FUN_RULE_UTILITY_PKG.CREATION_DATE, '||
195 ' FUN_RULE_UTILITY_PKG.LAST_UPDATE_LOGIN, '||
196 ' FUN_RULE_UTILITY_PKG.LAST_UPDATE_DATE, '||
197 ' FUN_RULE_UTILITY_PKG.LAST_UPDATED_BY, '||
198 ' :X_RULE_OBJECT_ID )',
199 DBMS_SQL.native);
200 else
201 DBMS_SQL.PARSE(destination_cursor,
202 'INSERT INTO ' || X_TABLE_NAME ||' ( '||
203 ' RULE_DETAIL_ID, '||
204 ' ATTRIBUTE_CATEGORY, '||
205 ' ATTRIBUTE1, '||
206 ' ATTRIBUTE2, '||
207 ' ATTRIBUTE3, '||
208 ' ATTRIBUTE4, '||
209 ' ATTRIBUTE5, '||
210 ' ATTRIBUTE6, '||
211 ' ATTRIBUTE7, '||
212 ' ATTRIBUTE8, '||
213 ' ATTRIBUTE9, '||
214 ' ATTRIBUTE10, '||
215 ' ATTRIBUTE11, '||
216 ' ATTRIBUTE12, '||
217 ' ATTRIBUTE13, '||
218 ' ATTRIBUTE14, '||
219 ' ATTRIBUTE15, '||
220 ' CREATED_BY, '||
221 ' CREATION_DATE, '||
222 ' LAST_UPDATE_LOGIN, '||
223 ' LAST_UPDATE_DATE, '||
224 ' LAST_UPDATED_BY '||
225 ' ) '||
226 ' VALUES( '||
227 ' :X_RULE_DETAIL_ID , '||
228 ' :X_ATTRIBUTE_CATEGORY , '||
229 ' :X_ATTRIBUTE1, '||
230 ' :X_ATTRIBUTE2, '||
231 ' :X_ATTRIBUTE3, '||
232 ' :X_ATTRIBUTE4, '||
233 ' :X_ATTRIBUTE5, '||
234 ' :X_ATTRIBUTE6, '||
235 ' :X_ATTRIBUTE7, '||
236 ' :X_ATTRIBUTE8, '||
237 ' :X_ATTRIBUTE9, '||
238 ' :X_ATTRIBUTE10, '||
239 ' :X_ATTRIBUTE11, '||
240 ' :X_ATTRIBUTE12, '||
241 ' :X_ATTRIBUTE13, '||
242 ' :X_ATTRIBUTE14, '||
243 ' :X_ATTRIBUTE15, '||
244 ' FUN_RULE_UTILITY_PKG.CREATED_BY, '||
245 ' FUN_RULE_UTILITY_PKG.CREATION_DATE, '||
246 ' FUN_RULE_UTILITY_PKG.LAST_UPDATE_LOGIN, '||
247 ' FUN_RULE_UTILITY_PKG.LAST_UPDATE_DATE, '||
248 ' FUN_RULE_UTILITY_PKG.LAST_UPDATED_BY )',
249 DBMS_SQL.native);
250 end if;
251
252 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_RULE_DETAIL_ID', X_RULE_DETAIL_ID);
253 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE_CATEGORY', X_ATTRIBUTE_CATEGORY);
254 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE1', X_ATTRIBUTE1);
255 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE2', X_ATTRIBUTE2);
256 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE3', X_ATTRIBUTE3);
257 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE4', X_ATTRIBUTE4);
258 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE5', X_ATTRIBUTE5);
259 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE6', X_ATTRIBUTE6);
260 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE7', X_ATTRIBUTE7);
261 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE8', X_ATTRIBUTE8);
262 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE9', X_ATTRIBUTE9);
263 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE10', X_ATTRIBUTE10);
264 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE11', X_ATTRIBUTE11);
265 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE12', X_ATTRIBUTE12);
266 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE13', X_ATTRIBUTE13);
267 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE14', X_ATTRIBUTE14);
268 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE15', X_ATTRIBUTE15);
269
270 if(g_is_use_instance) then
271 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_RULE_OBJECT_ID', X_RULE_OBJECT_ID);
272 end if;
273
274 ignore := DBMS_SQL.EXECUTE(destination_cursor);
275 DBMS_SQL.CLOSE_CURSOR(destination_cursor);
276
277
278 EXCEPTION
279 WHEN OTHERS THEN
280 IF DBMS_SQL.IS_OPEN(destination_cursor) THEN
281 DBMS_SQL.CLOSE_CURSOR(destination_cursor);
282 END IF;
283 RAISE;
284
285 END Insert_Row;
286
287 PROCEDURE Update_Row (
288 X_TABLE_NAME IN VARCHAR2,
289 X_RULE_DETAIL_ID IN NUMBER,
290 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
291 X_ATTRIBUTE1 IN VARCHAR2,
292 X_ATTRIBUTE2 IN VARCHAR2,
293 X_ATTRIBUTE3 IN VARCHAR2,
294 X_ATTRIBUTE4 IN VARCHAR2,
295 X_ATTRIBUTE5 IN VARCHAR2,
296 X_ATTRIBUTE6 IN VARCHAR2,
297 X_ATTRIBUTE7 IN VARCHAR2,
298 X_ATTRIBUTE8 IN VARCHAR2,
299 X_ATTRIBUTE9 IN VARCHAR2,
300 X_ATTRIBUTE10 IN VARCHAR2,
301 X_ATTRIBUTE11 IN VARCHAR2,
302 X_ATTRIBUTE12 IN VARCHAR2,
303 X_ATTRIBUTE13 IN VARCHAR2,
304 X_ATTRIBUTE14 IN VARCHAR2,
305 X_ATTRIBUTE15 IN VARCHAR2,
306 X_RULE_OBJECT_ID IN NUMBER
307 ) IS
308
309 destination_cursor INTEGER;
310 ignore INTEGER;
311
312 BEGIN
313
314 /* Rule Object Instance Enhancement for MULTIVALUE:
315 * Construct the dynamic SQL to use RULE_OBJECT_ID depending on if the Rule Object
316 * uses Instances or not.
317 */
318
319 -- Prepare a cursor to insert into the destination table:
323 'UPDATE ' || X_TABLE_NAME ||' SET '||
320 destination_cursor := DBMS_SQL.OPEN_CURSOR;
321 if(g_is_use_instance) then
322 DBMS_SQL.PARSE(destination_cursor,
324 ' ATTRIBUTE_CATEGORY = :X_ATTRIBUTE_CATEGORY, '||
325 ' ATTRIBUTE1 = :X_ATTRIBUTE1, '||
326 ' ATTRIBUTE2 = :X_ATTRIBUTE2, '||
327 ' ATTRIBUTE3 = :X_ATTRIBUTE3, '||
328 ' ATTRIBUTE4 = :X_ATTRIBUTE4, '||
329 ' ATTRIBUTE5 = :X_ATTRIBUTE5, '||
330 ' ATTRIBUTE6 = :X_ATTRIBUTE6, '||
331 ' ATTRIBUTE7 = :X_ATTRIBUTE7, '||
332 ' ATTRIBUTE8 = :X_ATTRIBUTE8, '||
333 ' ATTRIBUTE9 = :X_ATTRIBUTE9, '||
334 ' ATTRIBUTE10 = :X_ATTRIBUTE10, '||
335 ' ATTRIBUTE11 = :X_ATTRIBUTE11, '||
336 ' ATTRIBUTE12 = :X_ATTRIBUTE12, '||
337 ' ATTRIBUTE13 = :X_ATTRIBUTE13, '||
338 ' ATTRIBUTE14 = :X_ATTRIBUTE14, '||
339 ' ATTRIBUTE15 = :X_ATTRIBUTE15, '||
340 ' CREATED_BY = FUN_RULE_UTILITY_PKG.CREATED_BY, '||
341 ' CREATION_DATE = FUN_RULE_UTILITY_PKG.CREATION_DATE, '||
342 ' LAST_UPDATE_LOGIN = FUN_RULE_UTILITY_PKG.LAST_UPDATE_LOGIN, '||
343 ' LAST_UPDATE_DATE = FUN_RULE_UTILITY_PKG.LAST_UPDATE_DATE, '||
344 ' LAST_UPDATED_BY = FUN_RULE_UTILITY_PKG.LAST_UPDATED_BY '||
345 ' WHERE RULE_DETAIL_ID = :X_RULE_DETAIL_ID AND RULE_OBJECT_ID = :X_RULE_OBJECT_ID',
346 DBMS_SQL.native);
347 else
348 DBMS_SQL.PARSE(destination_cursor,
349 'UPDATE ' || X_TABLE_NAME ||' SET '||
350 ' ATTRIBUTE_CATEGORY = :X_ATTRIBUTE_CATEGORY, '||
351 ' ATTRIBUTE1 = :X_ATTRIBUTE1, '||
352 ' ATTRIBUTE2 = :X_ATTRIBUTE2, '||
353 ' ATTRIBUTE3 = :X_ATTRIBUTE3, '||
354 ' ATTRIBUTE4 = :X_ATTRIBUTE4, '||
355 ' ATTRIBUTE5 = :X_ATTRIBUTE5, '||
356 ' ATTRIBUTE6 = :X_ATTRIBUTE6, '||
357 ' ATTRIBUTE7 = :X_ATTRIBUTE7, '||
358 ' ATTRIBUTE8 = :X_ATTRIBUTE8, '||
359 ' ATTRIBUTE9 = :X_ATTRIBUTE9, '||
360 ' ATTRIBUTE10 = :X_ATTRIBUTE10, '||
361 ' ATTRIBUTE11 = :X_ATTRIBUTE11, '||
362 ' ATTRIBUTE12 = :X_ATTRIBUTE12, '||
363 ' ATTRIBUTE13 = :X_ATTRIBUTE13, '||
364 ' ATTRIBUTE14 = :X_ATTRIBUTE14, '||
365 ' ATTRIBUTE15 = :X_ATTRIBUTE15, '||
366 ' CREATED_BY = FUN_RULE_UTILITY_PKG.CREATED_BY, '||
367 ' CREATION_DATE = FUN_RULE_UTILITY_PKG.CREATION_DATE, '||
368 ' LAST_UPDATE_LOGIN = FUN_RULE_UTILITY_PKG.LAST_UPDATE_LOGIN, '||
369 ' LAST_UPDATE_DATE = FUN_RULE_UTILITY_PKG.LAST_UPDATE_DATE, '||
370 ' LAST_UPDATED_BY = FUN_RULE_UTILITY_PKG.LAST_UPDATED_BY '||
371 ' WHERE RULE_DETAIL_ID = :X_RULE_DETAIL_ID',
372 DBMS_SQL.native);
373 end if;
374
375
376 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE_CATEGORY', X_ATTRIBUTE_CATEGORY);
377 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE1', X_ATTRIBUTE1);
378 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE2', X_ATTRIBUTE2);
379 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE3', X_ATTRIBUTE3);
380 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE4', X_ATTRIBUTE4);
381 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE5', X_ATTRIBUTE5);
382 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE6', X_ATTRIBUTE6);
383 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE7', X_ATTRIBUTE7);
384 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE8', X_ATTRIBUTE8);
385 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE9', X_ATTRIBUTE9);
386 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE10', X_ATTRIBUTE10);
387 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE11', X_ATTRIBUTE11);
388 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE12', X_ATTRIBUTE12);
389 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE13', X_ATTRIBUTE13);
390 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE14', X_ATTRIBUTE14);
391 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_ATTRIBUTE15', X_ATTRIBUTE15);
392 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_RULE_DETAIL_ID', X_RULE_DETAIL_ID);
393
394 if(g_is_use_instance) then
395 DBMS_SQL.BIND_VARIABLE(destination_cursor, 'X_RULE_OBJECT_ID', X_RULE_OBJECT_ID);
396 end if;
397
398
399 ignore := DBMS_SQL.EXECUTE(destination_cursor);
400 DBMS_SQL.CLOSE_CURSOR(destination_cursor);
401
402 EXCEPTION
403 WHEN OTHERS THEN
404 IF DBMS_SQL.IS_OPEN(destination_cursor) THEN
405 DBMS_SQL.CLOSE_CURSOR(destination_cursor);
406 END IF;
407 RAISE;
408
409 END Update_Row;
410
411
412 /*
413 PROCEDURE Lock_Row (
414 X_TABLE_NAME IN VARCHAR2,
415 X_RULE_DETAIL_ID IN NUMBER,
416 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
417 X_ATTRIBUTE1 IN VARCHAR2,
418 X_ATTRIBUTE2 IN VARCHAR2,
419 X_ATTRIBUTE3 IN VARCHAR2,
420 X_ATTRIBUTE4 IN VARCHAR2,
421 X_ATTRIBUTE5 IN VARCHAR2,
422 X_ATTRIBUTE6 IN VARCHAR2,
423 X_ATTRIBUTE7 IN VARCHAR2,
424 X_ATTRIBUTE8 IN VARCHAR2,
425 X_ATTRIBUTE9 IN VARCHAR2,
426 X_ATTRIBUTE10 IN VARCHAR2,
430 X_ATTRIBUTE14 IN VARCHAR2,
427 X_ATTRIBUTE11 IN VARCHAR2,
428 X_ATTRIBUTE12 IN VARCHAR2,
429 X_ATTRIBUTE13 IN VARCHAR2,
431 X_ATTRIBUTE15 IN VARCHAR2,
432 X_CREATED_BY IN NUMBER,
433 X_CREATION_DATE IN DATE,
434 X_LAST_UPDATE_LOGIN IN NUMBER,
435 X_LAST_UPDATE_DATE IN DATE,
436 X_LAST_UPDATED_BY IN NUMBER,
437 X_RULE_OBJECT_ID IN NUMBER
438 ) IS
439
440 CURSOR C IS
441 SELECT * FROM FUN_RULE_DFF
442 WHERE RULE_DETAIL_ID = X_RULE_DETAIL_ID
443 FOR UPDATE NOWAIT;
444 Recinfo C%ROWTYPE;
445
446 BEGIN
447
448 OPEN C;
449 FETCH C INTO Recinfo;
450 IF ( C%NOTFOUND ) THEN
451 CLOSE C;
452 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
453 APP_EXCEPTION.RAISE_EXCEPTION;
454 END IF;
455 CLOSE C;
456
457 IF (
458 ( ( RULE_DETAIL_ID = X_RULE_DETAIL_ID )
459 OR ( ( RULE_DETAIL_ID IS NULL )
460 AND ( X_RULE_DETAIL_ID IS NULL ) ) )
461 AND ( ( ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY )
462 OR ( ( ATTRIBUTE_CATEGORY IS NULL )
463 AND ( X_ATTRIBUTE_CATEGORY IS NULL ) ) )
464 AND ( ( ATTRIBUTE1 = X_ATTRIBUTE1 )
465 OR ( ( ATTRIBUTE1 IS NULL )
466 AND ( X_ATTRIBUTE1 IS NULL ) ) )
467 AND ( ( ATTRIBUTE2 = X_ATTRIBUTE2 )
468 OR ( ( ATTRIBUTE2 IS NULL )
469 AND ( X_ATTRIBUTE2 IS NULL ) ) )
470 AND ( ( ATTRIBUTE3 = X_ATTRIBUTE3 )
471 OR ( ( ATTRIBUTE3 IS NULL )
472 AND ( X_ATTRIBUTE3 IS NULL ) ) )
473 AND ( ( ATTRIBUTE4 = X_ATTRIBUTE4 )
474 OR ( ( ATTRIBUTE4 IS NULL )
475 AND ( X_ATTRIBUTE4 IS NULL ) ) )
476 AND ( ( ATTRIBUTE5 = X_ATTRIBUTE5 )
477 OR ( ( ATTRIBUTE5 IS NULL )
478 AND ( X_ATTRIBUTE5 IS NULL ) ) )
479 AND ( ( ATTRIBUTE6 = X_ATTRIBUTE6 )
480 OR ( ( ATTRIBUTE6 IS NULL )
481 AND ( X_ATTRIBUTE6 IS NULL ) ) )
482 AND ( ( ATTRIBUTE7 = X_ATTRIBUTE7 )
483 OR ( ( ATTRIBUTE7 IS NULL )
484 AND ( X_ATTRIBUTE7 IS NULL ) ) )
485 AND ( ( ATTRIBUTE8 = X_ATTRIBUTE8 )
486 OR ( ( ATTRIBUTE8 IS NULL )
487 AND ( X_ATTRIBUTE8 IS NULL ) ) )
488 AND ( ( ATTRIBUTE9 = X_ATTRIBUTE9 )
489 OR ( ( ATTRIBUTE9 IS NULL )
490 AND ( X_ATTRIBUTE9 IS NULL ) ) )
491 AND ( ( ATTRIBUTE10 = X_ATTRIBUTE10 )
492 OR ( ( ATTRIBUTE10 IS NULL )
493 AND ( X_ATTRIBUTE10 IS NULL ) ) )
494 AND ( ( ATTRIBUTE11 = X_ATTRIBUTE11 )
495 OR ( ( ATTRIBUTE11 IS NULL )
496 AND ( X_ATTRIBUTE11 IS NULL ) ) )
497 AND ( ( ATTRIBUTE12 = X_ATTRIBUTE12 )
498 OR ( ( ATTRIBUTE12 IS NULL )
499 AND ( X_ATTRIBUTE12 IS NULL ) ) )
500 AND ( ( ATTRIBUTE13 = X_ATTRIBUTE13 )
501 OR ( ( ATTRIBUTE13 IS NULL )
502 AND ( X_ATTRIBUTE13 IS NULL ) ) )
503 AND ( ( ATTRIBUTE14 = X_ATTRIBUTE14 )
504 OR ( ( ATTRIBUTE14 IS NULL )
505 AND ( X_ATTRIBUTE14 IS NULL ) ) )
506 AND ( ( ATTRIBUTE15 = X_ATTRIBUTE15 )
507 OR ( ( ATTRIBUTE15 IS NULL )
508 AND ( X_ATTRIBUTE15 IS NULL ) ) )
509 AND ( ( CREATED_BY = X_CREATED_BY )
510 OR ( ( CREATED_BY IS NULL )
511 AND ( X_CREATED_BY IS NULL ) ) )
512 AND ( ( CREATION_DATE = X_CREATION_DATE )
513 OR ( ( CREATION_DATE IS NULL )
514 AND ( X_CREATION_DATE IS NULL ) ) )
515 AND ( ( LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN )
516 OR ( ( LAST_UPDATE_LOGIN IS NULL )
517 AND ( X_LAST_UPDATE_LOGIN IS NULL ) ) )
518 AND ( ( LAST_UPDATE_DATE = X_LAST_UPDATE_DATE )
519 OR ( ( LAST_UPDATE_DATE IS NULL )
520 AND ( X_LAST_UPDATE_DATE IS NULL ) ) )
521 AND ( ( LAST_UPDATED_BY = X_LAST_UPDATED_BY )
522 OR ( ( LAST_UPDATED_BY IS NULL )
523 AND ( X_LAST_UPDATED_BY IS NULL ) ) )
524 ) THEN
525 RETURN;
526 ELSE
527 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
528 APP_EXCEPTION.RAISE_EXCEPTION;
529 END IF;
530
531 END Lock_Row;
532 */
533
534 PROCEDURE Lock_Row (
535 X_TABLE_NAME IN VARCHAR2,
536 X_RULE_DETAIL_ID IN NUMBER,
537 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
538 X_ATTRIBUTE1 IN VARCHAR2,
539 X_ATTRIBUTE2 IN VARCHAR2,
540 X_ATTRIBUTE3 IN VARCHAR2,
541 X_ATTRIBUTE4 IN VARCHAR2,
542 X_ATTRIBUTE5 IN VARCHAR2,
543 X_ATTRIBUTE6 IN VARCHAR2,
544 X_ATTRIBUTE7 IN VARCHAR2,
545 X_ATTRIBUTE8 IN VARCHAR2,
546 X_ATTRIBUTE9 IN VARCHAR2,
547 X_ATTRIBUTE10 IN VARCHAR2,
548 X_ATTRIBUTE11 IN VARCHAR2,
549 X_ATTRIBUTE12 IN VARCHAR2,
550 X_ATTRIBUTE13 IN VARCHAR2,
551 X_ATTRIBUTE14 IN VARCHAR2,
552 X_ATTRIBUTE15 IN VARCHAR2,
553 X_CREATED_BY IN NUMBER,
554 X_CREATION_DATE IN DATE,
555 X_LAST_UPDATE_LOGIN IN NUMBER,
556 X_LAST_UPDATE_DATE IN DATE,
557 X_LAST_UPDATED_BY IN NUMBER,
558 X_RULE_OBJECT_ID IN NUMBER
559 ) IS
560
561 source_cursor INTEGER;
562 destination_cursor INTEGER;
563 ignore INTEGER;
564
568 ATTRIBUTE2 VARCHAR2(150);
565 RULE_DETAIL_ID NUMBER;
566 ATTRIBUTE_CATEGORY VARCHAR2(150);
567 ATTRIBUTE1 VARCHAR2(150);
569 ATTRIBUTE3 VARCHAR2(150);
570 ATTRIBUTE4 VARCHAR2(150);
571 ATTRIBUTE5 VARCHAR2(150);
572 ATTRIBUTE6 VARCHAR2(150);
573 ATTRIBUTE7 VARCHAR2(150);
574 ATTRIBUTE8 VARCHAR2(150);
575 ATTRIBUTE9 VARCHAR2(150);
576 ATTRIBUTE10 VARCHAR2(150);
577 ATTRIBUTE11 VARCHAR2(150);
578 ATTRIBUTE12 VARCHAR2(150);
579 ATTRIBUTE13 VARCHAR2(150);
580 ATTRIBUTE14 VARCHAR2(150);
581 ATTRIBUTE15 VARCHAR2(150);
582 CREATED_BY NUMBER;
583 CREATION_DATE DATE;
584 LAST_UPDATE_LOGIN NUMBER;
585 LAST_UPDATE_DATE DATE;
586 LAST_UPDATED_BY NUMBER;
587 RULE_OBJECT_ID NUMBER;
588
589 BEGIN
590
591 -- Prepare a cursor to select from the source table:
592
593 source_cursor := dbms_sql.open_cursor;
594 if(g_is_use_instance) then
595 DBMS_SQL.PARSE(source_cursor,
596 'SELECT * FROM '|| X_TABLE_NAME || ' WHERE RULE_DETAIL_ID = :X_RULE_DETAIL_ID AND RULE_OBJECT_ID = :X_RULE_OBJECT_ID FOR UPDATE NOWAIT',
597 DBMS_SQL.native);
598 else
599 DBMS_SQL.PARSE(source_cursor,
600 'SELECT * FROM '|| X_TABLE_NAME || ' WHERE RULE_DETAIL_ID = :X_RULE_DETAIL_ID FOR UPDATE NOWAIT',
601 DBMS_SQL.native);
602 end if;
603
604 if(g_is_use_instance) then
605 DBMS_SQL.BIND_VARIABLE(source_cursor, 'X_RULE_OBJECT_ID', X_RULE_OBJECT_ID);
606 end if;
607
608 DBMS_SQL.BIND_VARIABLE(source_cursor, 'X_RULE_DETAIL_ID', X_RULE_DETAIL_ID);
609
610 DBMS_SQL.DEFINE_COLUMN(source_cursor , 1, RULE_DETAIL_ID);
611 DBMS_SQL.DEFINE_COLUMN(source_cursor , 2, ATTRIBUTE_CATEGORY, 150);
612 DBMS_SQL.DEFINE_COLUMN(source_cursor , 3, ATTRIBUTE1,150);
613 DBMS_SQL.DEFINE_COLUMN(source_cursor , 4, ATTRIBUTE2,150);
614 DBMS_SQL.DEFINE_COLUMN(source_cursor , 5, ATTRIBUTE3,150);
615 DBMS_SQL.DEFINE_COLUMN(source_cursor , 6, ATTRIBUTE4,150);
616 DBMS_SQL.DEFINE_COLUMN(source_cursor , 7, ATTRIBUTE5,150);
617 DBMS_SQL.DEFINE_COLUMN(source_cursor , 8, ATTRIBUTE6,150);
618 DBMS_SQL.DEFINE_COLUMN(source_cursor , 9, ATTRIBUTE7,150);
619 DBMS_SQL.DEFINE_COLUMN(source_cursor , 10,ATTRIBUTE8,150);
620 DBMS_SQL.DEFINE_COLUMN(source_cursor , 11,ATTRIBUTE9,150);
621 DBMS_SQL.DEFINE_COLUMN(source_cursor , 12,ATTRIBUTE10,150);
622 DBMS_SQL.DEFINE_COLUMN(source_cursor , 13,ATTRIBUTE11,150);
623 DBMS_SQL.DEFINE_COLUMN(source_cursor , 14,ATTRIBUTE12,150);
624 DBMS_SQL.DEFINE_COLUMN(source_cursor , 15,ATTRIBUTE13,150);
625 DBMS_SQL.DEFINE_COLUMN(source_cursor , 16,ATTRIBUTE14,150);
626 DBMS_SQL.DEFINE_COLUMN(source_cursor , 17,ATTRIBUTE15,150);
627 DBMS_SQL.DEFINE_COLUMN(source_cursor , 18,CREATED_BY);
628 DBMS_SQL.DEFINE_COLUMN(source_cursor , 19,CREATION_DATE);
629 DBMS_SQL.DEFINE_COLUMN(source_cursor , 20,LAST_UPDATE_LOGIN);
630 DBMS_SQL.DEFINE_COLUMN(source_cursor , 21,LAST_UPDATE_DATE);
631 DBMS_SQL.DEFINE_COLUMN(source_cursor , 22,LAST_UPDATED_BY);
632 if(g_is_use_instance) then
633 DBMS_SQL.DEFINE_COLUMN(source_cursor , 23,RULE_OBJECT_ID);
634 end if;
635
636
637 ignore := DBMS_SQL.EXECUTE(source_cursor);
638
639 IF (
640 ( ( RULE_DETAIL_ID = X_RULE_DETAIL_ID )
641 OR ( ( RULE_DETAIL_ID IS NULL )
642 AND ( X_RULE_DETAIL_ID IS NULL ) ) )
643 AND ( ( ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY )
644 OR ( ( ATTRIBUTE_CATEGORY IS NULL )
645 AND ( X_ATTRIBUTE_CATEGORY IS NULL ) ) )
646 AND ( ( ATTRIBUTE1 = X_ATTRIBUTE1 )
647 OR ( ( ATTRIBUTE1 IS NULL )
648 AND ( X_ATTRIBUTE1 IS NULL ) ) )
649 AND ( ( ATTRIBUTE2 = X_ATTRIBUTE2 )
650 OR ( ( ATTRIBUTE2 IS NULL )
651 AND ( X_ATTRIBUTE2 IS NULL ) ) )
652 AND ( ( ATTRIBUTE3 = X_ATTRIBUTE3 )
653 OR ( ( ATTRIBUTE3 IS NULL )
654 AND ( X_ATTRIBUTE3 IS NULL ) ) )
655 AND ( ( ATTRIBUTE4 = X_ATTRIBUTE4 )
656 OR ( ( ATTRIBUTE4 IS NULL )
657 AND ( X_ATTRIBUTE4 IS NULL ) ) )
658 AND ( ( ATTRIBUTE5 = X_ATTRIBUTE5 )
659 OR ( ( ATTRIBUTE5 IS NULL )
660 AND ( X_ATTRIBUTE5 IS NULL ) ) )
661 AND ( ( ATTRIBUTE6 = X_ATTRIBUTE6 )
662 OR ( ( ATTRIBUTE6 IS NULL )
663 AND ( X_ATTRIBUTE6 IS NULL ) ) )
664 AND ( ( ATTRIBUTE7 = X_ATTRIBUTE7 )
665 OR ( ( ATTRIBUTE7 IS NULL )
666 AND ( X_ATTRIBUTE7 IS NULL ) ) )
667 AND ( ( ATTRIBUTE8 = X_ATTRIBUTE8 )
668 OR ( ( ATTRIBUTE8 IS NULL )
669 AND ( X_ATTRIBUTE8 IS NULL ) ) )
670 AND ( ( ATTRIBUTE9 = X_ATTRIBUTE9 )
671 OR ( ( ATTRIBUTE9 IS NULL )
672 AND ( X_ATTRIBUTE9 IS NULL ) ) )
673 AND ( ( ATTRIBUTE10 = X_ATTRIBUTE10 )
674 OR ( ( ATTRIBUTE10 IS NULL )
675 AND ( X_ATTRIBUTE10 IS NULL ) ) )
676 AND ( ( ATTRIBUTE11 = X_ATTRIBUTE11 )
677 OR ( ( ATTRIBUTE11 IS NULL )
678 AND ( X_ATTRIBUTE11 IS NULL ) ) )
679 AND ( ( ATTRIBUTE12 = X_ATTRIBUTE12 )
680 OR ( ( ATTRIBUTE12 IS NULL )
681 AND ( X_ATTRIBUTE12 IS NULL ) ) )
682 AND ( ( ATTRIBUTE13 = X_ATTRIBUTE13 )
683 OR ( ( ATTRIBUTE13 IS NULL )
684 AND ( X_ATTRIBUTE13 IS NULL ) ) )
685 AND ( ( ATTRIBUTE14 = X_ATTRIBUTE14 )
686 OR ( ( ATTRIBUTE14 IS NULL )
687 AND ( X_ATTRIBUTE14 IS NULL ) ) )
691 AND ( ( CREATED_BY = X_CREATED_BY )
688 AND ( ( ATTRIBUTE15 = X_ATTRIBUTE15 )
689 OR ( ( ATTRIBUTE15 IS NULL )
690 AND ( X_ATTRIBUTE15 IS NULL ) ) )
692 OR ( ( CREATED_BY IS NULL )
693 AND ( X_CREATED_BY IS NULL ) ) )
694 AND ( ( CREATION_DATE = X_CREATION_DATE )
695 OR ( ( CREATION_DATE IS NULL )
696 AND ( X_CREATION_DATE IS NULL ) ) )
697 AND ( ( LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN )
698 OR ( ( LAST_UPDATE_LOGIN IS NULL )
699 AND ( X_LAST_UPDATE_LOGIN IS NULL ) ) )
700 AND ( ( LAST_UPDATE_DATE = X_LAST_UPDATE_DATE )
701 OR ( ( LAST_UPDATE_DATE IS NULL )
702 AND ( X_LAST_UPDATE_DATE IS NULL ) ) )
703 AND ( ( LAST_UPDATED_BY = X_LAST_UPDATED_BY )
704 OR ( ( LAST_UPDATED_BY IS NULL )
705 AND ( X_LAST_UPDATED_BY IS NULL ) ) )
706 ) THEN
707 RETURN;
708 ELSE
709 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
710 APP_EXCEPTION.RAISE_EXCEPTION;
711 END IF;
712
713 EXCEPTION
714 WHEN OTHERS THEN
715 IF DBMS_SQL.IS_OPEN(source_cursor) THEN
716 DBMS_SQL.CLOSE_CURSOR(source_cursor);
717 END IF;
718 RAISE;
719
720 END Lock_Row;
721
722 PROCEDURE Select_Row (
723 X_TABLE_NAME IN VARCHAR2,
724 X_RULE_DETAIL_ID IN OUT NOCOPY NUMBER,
725 X_ATTRIBUTE_CATEGORY OUT NOCOPY VARCHAR2,
726 X_ATTRIBUTE1 OUT NOCOPY VARCHAR2,
727 X_ATTRIBUTE2 OUT NOCOPY VARCHAR2,
728 X_ATTRIBUTE3 OUT NOCOPY VARCHAR2,
729 X_ATTRIBUTE4 OUT NOCOPY VARCHAR2,
730 X_ATTRIBUTE5 OUT NOCOPY VARCHAR2,
731 X_ATTRIBUTE6 OUT NOCOPY VARCHAR2,
732 X_ATTRIBUTE7 OUT NOCOPY VARCHAR2,
733 X_ATTRIBUTE8 OUT NOCOPY VARCHAR2,
734 X_ATTRIBUTE9 OUT NOCOPY VARCHAR2,
735 X_ATTRIBUTE10 OUT NOCOPY VARCHAR2,
736 X_ATTRIBUTE11 OUT NOCOPY VARCHAR2,
737 X_ATTRIBUTE12 OUT NOCOPY VARCHAR2,
738 X_ATTRIBUTE13 OUT NOCOPY VARCHAR2,
739 X_ATTRIBUTE14 OUT NOCOPY VARCHAR2,
740 X_ATTRIBUTE15 OUT NOCOPY VARCHAR2,
741 X_RULE_OBJECT_ID OUT NOCOPY NUMBER
742 ) IS
743
744 l_select_stmt VARCHAR2(2000);
745 source_cursor INTEGER;
746 ignore INTEGER;
747
748
749 BEGIN
750
751 /* Rule Object Instance Enhancement for MULTIVALUE:
752 * Construct the dynamic SQL to use RULE_OBJECT_ID depending on if the Rule Object
753 * uses Instances or not.
754 */
755
756 -- Prepare a cursor to select from the source table:
757 source_cursor := dbms_sql.open_cursor;
758
759 if(g_is_use_instance) then
760 l_select_stmt :='SELECT '||
761 ' RULE_DETAIL_ID, '||
762 ' ATTRIBUTE_CATEGORY, '||
763 ' ATTRIBUTE1, '||
764 ' ATTRIBUTE2, '||
765 ' ATTRIBUTE3, '||
766 ' ATTRIBUTE4, '||
767 ' ATTRIBUTE5, '||
768 ' ATTRIBUTE6, '||
769 ' ATTRIBUTE7, '||
770 ' ATTRIBUTE8, '||
771 ' ATTRIBUTE9, '||
772 ' ATTRIBUTE10, '||
773 ' ATTRIBUTE11, '||
774 ' ATTRIBUTE12, '||
775 ' ATTRIBUTE13, '||
776 ' ATTRIBUTE14, '||
777 ' ATTRIBUTE15 '||
778 ' FROM '|| X_TABLE_NAME ||
779 ' WHERE RULE_DETAIL_ID = :X_RULE_DETAIL_ID AND RULE_OBJECT_ID = :X_RULE_OBJECT_ID ' ||
780 ' AND ROWNUM = 1 ';
781 else
782 l_select_stmt :='SELECT '||
783 ' RULE_DETAIL_ID, '||
784 ' ATTRIBUTE_CATEGORY, '||
785 ' ATTRIBUTE1, '||
786 ' ATTRIBUTE2, '||
787 ' ATTRIBUTE3, '||
788 ' ATTRIBUTE4, '||
789 ' ATTRIBUTE5, '||
790 ' ATTRIBUTE6, '||
791 ' ATTRIBUTE7, '||
792 ' ATTRIBUTE8, '||
793 ' ATTRIBUTE9, '||
794 ' ATTRIBUTE10, '||
795 ' ATTRIBUTE11, '||
796 ' ATTRIBUTE12, '||
797 ' ATTRIBUTE13, '||
798 ' ATTRIBUTE14, '||
799 ' ATTRIBUTE15 '||
800 ' FROM '|| X_TABLE_NAME ||
801 ' WHERE RULE_DETAIL_ID = :X_RULE_DETAIL_ID ' ||
802 ' AND ROWNUM = 1 ';
803 end if;
804 DBMS_SQL.PARSE(source_cursor,l_select_stmt , DBMS_SQL.native);
805
806 DBMS_SQL.BIND_VARIABLE(source_cursor, 'X_RULE_DETAIL_ID', X_RULE_DETAIL_ID);
807 if(g_is_use_instance) then
808 DBMS_SQL.BIND_VARIABLE(source_cursor, 'X_RULE_OBJECT_ID', X_RULE_OBJECT_ID);
809 end if;
810
811 DBMS_SQL.DEFINE_COLUMN(source_cursor , 1, X_RULE_DETAIL_ID);
812 DBMS_SQL.DEFINE_COLUMN(source_cursor , 2, X_ATTRIBUTE_CATEGORY, 150);
813 DBMS_SQL.DEFINE_COLUMN(source_cursor , 3, X_ATTRIBUTE1,150);
814 DBMS_SQL.DEFINE_COLUMN(source_cursor , 4, X_ATTRIBUTE2,150);
815 DBMS_SQL.DEFINE_COLUMN(source_cursor , 5, X_ATTRIBUTE3,150);
816 DBMS_SQL.DEFINE_COLUMN(source_cursor , 6, X_ATTRIBUTE4,150);
817 DBMS_SQL.DEFINE_COLUMN(source_cursor , 7, X_ATTRIBUTE5,150);
818 DBMS_SQL.DEFINE_COLUMN(source_cursor , 8, X_ATTRIBUTE6,150);
819 DBMS_SQL.DEFINE_COLUMN(source_cursor , 9, X_ATTRIBUTE7,150);
820 DBMS_SQL.DEFINE_COLUMN(source_cursor , 10,X_ATTRIBUTE8,150);
821 DBMS_SQL.DEFINE_COLUMN(source_cursor , 11,X_ATTRIBUTE9,150);
822 DBMS_SQL.DEFINE_COLUMN(source_cursor , 12,X_ATTRIBUTE10,150);
823 DBMS_SQL.DEFINE_COLUMN(source_cursor , 13,X_ATTRIBUTE11,150);
824 DBMS_SQL.DEFINE_COLUMN(source_cursor , 14,X_ATTRIBUTE12,150);
825 DBMS_SQL.DEFINE_COLUMN(source_cursor , 15,X_ATTRIBUTE13,150);
826 DBMS_SQL.DEFINE_COLUMN(source_cursor , 16,X_ATTRIBUTE14,150);
827 DBMS_SQL.DEFINE_COLUMN(source_cursor , 17,X_ATTRIBUTE15,150);
828 if(g_is_use_instance) then
829 DBMS_SQL.DEFINE_COLUMN(source_cursor , 18, X_RULE_OBJECT_ID);
833 IF DBMS_SQL.FETCH_ROWS(source_cursor)>0 THEN
830 end if;
831 ignore := DBMS_SQL.EXECUTE(source_cursor);
832
834 -- get column values of the row
835 DBMS_SQL.COLUMN_VALUE(source_cursor , 1, X_RULE_DETAIL_ID);
836 DBMS_SQL.COLUMN_VALUE(source_cursor , 2, X_ATTRIBUTE_CATEGORY);
837 DBMS_SQL.COLUMN_VALUE(source_cursor , 3, X_ATTRIBUTE1);
838 DBMS_SQL.COLUMN_VALUE(source_cursor , 4, X_ATTRIBUTE2);
839 DBMS_SQL.COLUMN_VALUE(source_cursor , 5, X_ATTRIBUTE3);
840 DBMS_SQL.COLUMN_VALUE(source_cursor , 6, X_ATTRIBUTE4);
841 DBMS_SQL.COLUMN_VALUE(source_cursor , 7, X_ATTRIBUTE5);
842 DBMS_SQL.COLUMN_VALUE(source_cursor , 8, X_ATTRIBUTE6);
843 DBMS_SQL.COLUMN_VALUE(source_cursor , 9, X_ATTRIBUTE7);
844 DBMS_SQL.COLUMN_VALUE(source_cursor , 10,X_ATTRIBUTE8);
845 DBMS_SQL.COLUMN_VALUE(source_cursor , 11,X_ATTRIBUTE9);
846 DBMS_SQL.COLUMN_VALUE(source_cursor , 12,X_ATTRIBUTE10);
847 DBMS_SQL.COLUMN_VALUE(source_cursor , 13,X_ATTRIBUTE11);
848 DBMS_SQL.COLUMN_VALUE(source_cursor , 14,X_ATTRIBUTE12);
849 DBMS_SQL.COLUMN_VALUE(source_cursor , 15,X_ATTRIBUTE13);
850 DBMS_SQL.COLUMN_VALUE(source_cursor , 16,X_ATTRIBUTE14);
851 DBMS_SQL.COLUMN_VALUE(source_cursor , 17,X_ATTRIBUTE15);
852 if(g_is_use_instance) then
853 DBMS_SQL.COLUMN_VALUE(source_cursor , 18, X_RULE_OBJECT_ID);
854 end if;
855 END IF;
856
857 DBMS_SQL.CLOSE_CURSOR(source_cursor);
858
859 EXCEPTION
860 WHEN NO_DATA_FOUND THEN
861 IF DBMS_SQL.IS_OPEN(source_cursor) THEN
862 DBMS_SQL.CLOSE_CURSOR(source_cursor);
863 END IF;
864
865 FND_MESSAGE.SET_NAME( 'FUN', 'FUN_RULE_API_NO_RECORD' );
866 FND_MESSAGE.SET_TOKEN( 'RECORD', 'FUN_RULE_DFF');
867 FND_MESSAGE.SET_TOKEN( 'VALUE', X_RULE_DETAIL_ID);
868 FND_MSG_PUB.ADD;
869 RAISE FND_API.G_EXC_ERROR;
870
871 WHEN OTHERS THEN
872 IF DBMS_SQL.IS_OPEN(source_cursor) THEN
873 DBMS_SQL.CLOSE_CURSOR(source_cursor);
874 END IF;
875 RAISE;
876
877
878 END Select_Row;
879
880 PROCEDURE Delete_Row (
881 X_TABLE_NAME IN VARCHAR2,
882 X_RULE_DETAIL_ID IN NUMBER,
883 X_RULE_OBJECT_ID IN NUMBER
884 ) IS
885
886 source_cursor INTEGER;
887 ignore INTEGER;
888
889 BEGIN
890
891 /* Rule Object Instance Enhancement for MULTIVALUE:
892 * Construct the dynamic SQL to use RULE_OBJECT_ID depending on if the Rule Object
893 * uses Instances or not.
894 */
895
896 -- Prepare a cursor to select from the source table:
897 source_cursor := DBMS_SQL.OPEN_CURSOR;
898
899 if(g_is_use_instance) then
900 DBMS_SQL.PARSE(source_cursor, 'delete from ' || X_TABLE_NAME ||' WHERE RULE_DETAIL_ID = :X_RULE_DETAIL_ID AND RULE_OBJECT_ID = :X_RULE_OBJECT_ID',
901 DBMS_SQL.native);
902 else
903 DBMS_SQL.PARSE(source_cursor, 'delete from ' || X_TABLE_NAME ||' WHERE RULE_DETAIL_ID = :X_RULE_DETAIL_ID',
904 DBMS_SQL.native);
905 end if;
906
907 DBMS_SQL.BIND_VARIABLE(source_cursor, 'X_RULE_DETAIL_ID', X_RULE_DETAIL_ID);
908 if(g_is_use_instance) then
909 DBMS_SQL.BIND_VARIABLE(source_cursor, 'X_RULE_OBJECT_ID', X_RULE_OBJECT_ID);
910 end if;
911 ignore := DBMS_SQL.EXECUTE(source_cursor);
912
913 DBMS_SQL.CLOSE_CURSOR(source_cursor);
914 COMMIT;
915 EXCEPTION
916 WHEN NO_DATA_FOUND THEN
917 IF DBMS_SQL.IS_OPEN(source_cursor) THEN
918 DBMS_SQL.CLOSE_CURSOR(source_cursor);
919 END IF;
920
921 FND_MESSAGE.SET_NAME( 'FUN', 'FUN_RULE_API_NO_RECORD' );
922 FND_MESSAGE.SET_TOKEN( 'RECORD', 'FUN_RULE_DFF');
923 FND_MESSAGE.SET_TOKEN( 'VALUE', X_RULE_DETAIL_ID);
924 FND_MSG_PUB.ADD;
925 RAISE FND_API.G_EXC_ERROR;
926
927 WHEN OTHERS THEN
928 IF DBMS_SQL.IS_OPEN(source_cursor) THEN
929 DBMS_SQL.CLOSE_CURSOR(source_cursor);
930 END IF;
931 RAISE;
932
933
934 END Delete_Row;
935
936 END FUN_RULE_DFF_PKG;