1 PACKAGE BODY pn_index_lease_constraints_pkg AS
2 -- $Header: PNTINLCB.pls 120.3 2007/01/30 10:39:43 pseeram ship $
3
4
5 /*============================================================================+
6 | Copyright (c) 2001 Oracle Corporation
7 | Redwood Shores, California, USA
8 | All rights reserved.
9 | DESCRIPTION
10 |
11 | These procedures consist are used a table handlers for the
12 | PN_INDEX_LEASE_CONSTRAINTS table.
13 | They include:
14 | INSERT_ROW - insert a row into PN_INDEX_LEASE_CONSTRAINTS.
15 | DELETE_ROW - deletes a row from PN_INDEX_LEASE_CONSTRAINTS.
16 | UPDATE_ROW - updates a row from PN_INDEX_LEASE_CONSTRAINTS.
17 | LOCKS_ROW - will check if a row has been modified since being
18 | queried by form.
19 |
20 |
21 | HISTORY
22 | 11-APR-2001 jbreyes o Created
23 | 13-DEC-2001 Mrinal Misra o Added dbdrv command.
24 | 15-JAN-2002 Mrinal Misra o In dbdrv command changed phase=pls to phase=plb.
25 | Added checkfile.Ref. Bug# 2184724.
26 | 09-JUL-2002 ftanudja o added x_org_id parameter in insert_row for
27 | shared serv. enh.
28 | 23-JUL-2002 ftanudja o changed lock_row to comply with new standards
29 | 05-Jul-2005 hrodda o overloaded delete_row proc to take PK as parameter
30 | 19-JAN-2007 Prabnhakar o Modified the update_row update where condition.
31 +============================================================================*/
32
33 -------------------------------------------------------------------------------
34 -- PROCDURE : INSERT_ROW
35 -- INVOKED FROM : insert_row procedure
36 -- PURPOSE : inserts the row
37 -- HISTORY :
38 -- 04-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_lease_constraints with
39 -- _ALL table.
40 -------------------------------------------------------------------------------
41 PROCEDURE insert_row (
42 x_rowid IN OUT NOCOPY VARCHAR2
43 ,x_org_id IN NUMBER
44 ,x_index_constraint_id IN OUT NOCOPY NUMBER
45 ,x_index_lease_id IN NUMBER
46 ,x_scope IN VARCHAR2
47 ,x_last_update_date IN DATE
48 ,x_last_updated_by IN NUMBER
49 ,x_creation_date IN DATE
50 ,x_created_by IN NUMBER
51 ,x_minimum_amount IN NUMBER
52 ,x_maximum_amount IN NUMBER
53 ,x_minimum_percent IN NUMBER
54 ,x_maximum_percent IN NUMBER
55 ,x_last_update_login IN NUMBER
56 ,x_attribute_category IN VARCHAR2
57 ,x_attribute1 IN VARCHAR2
58 ,x_attribute2 IN VARCHAR2
59 ,x_attribute3 IN VARCHAR2
60 ,x_attribute4 IN VARCHAR2
61 ,x_attribute5 IN VARCHAR2
62 ,x_attribute6 IN VARCHAR2
63 ,x_attribute7 IN VARCHAR2
64 ,x_attribute8 IN VARCHAR2
65 ,x_attribute9 IN VARCHAR2
66 ,x_attribute10 IN VARCHAR2
67 ,x_attribute11 IN VARCHAR2
68 ,x_attribute12 IN VARCHAR2
69 ,x_attribute13 IN VARCHAR2
70 ,x_attribute14 IN VARCHAR2
71 ,x_attribute15 IN VARCHAR2
72 ) IS
73 CURSOR c IS
74 SELECT ROWID
75 FROM pn_index_lease_constraints_all
76 WHERE index_constraint_id = x_index_constraint_id;
77
78 l_return_status VARCHAR2 (30) := NULL;
79 l_rowid VARCHAR2 (18) := NULL;
80
81 CURSOR org_cur IS
82 SELECT org_id FROM pn_index_leases_all WHERE index_lease_id = x_index_lease_id;
83 l_org_ID NUMBER;
84
85 BEGIN
86
87 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_CONSTRAINTS_PKG.insert_row (+)');
88 /* If no INDEX_CONSTRAINT_ID is provided, get one from sequence */
89
90 IF (x_index_constraint_id IS NULL) THEN
91 SELECT pn_index_lease_constraints_s.NEXTVAL
92 INTO x_index_constraint_id
93 FROM DUAL;
94 END IF;
95
96 IF x_org_id IS NULL THEN
97 FOR rec IN org_cur LOOP
98 l_org_id := rec.org_id;
99 END LOOP;
100 ELSE
101 l_org_id := x_org_id;
102 END IF;
103
104 pn_index_lease_constraints_pkg.check_unq_constraint_scope (
105 l_return_status
106 ,x_index_constraint_id
107 ,x_index_lease_id
108 ,x_scope
109 );
110
111 IF (l_return_status IS NOT NULL) THEN
112 app_exception.raise_exception;
113 END IF;
114
115 INSERT INTO pn_index_lease_constraints_all
116 (
117 index_constraint_id
118 ,org_id
119 ,index_lease_id
120 ,scope
121 ,last_update_date
122 ,last_updated_by
123 ,creation_date
124 ,created_by
125 ,minimum_amount
126 ,maximum_amount
127 ,minimum_percent
128 ,maximum_percent
129 ,last_update_login
130 ,attribute_category
131 ,attribute1
132 ,attribute2
133 ,attribute3
134 ,attribute4
135 ,attribute5
136 ,attribute6
137 ,attribute7
138 ,attribute8
139 ,attribute9
140 ,attribute10
141 ,attribute11
142 ,attribute12
143 ,attribute13
144 ,attribute14
145 ,attribute15
146 )
147 VALUES
148 (
149 x_index_constraint_id
150 ,l_org_id
151 ,x_index_lease_id
152 ,x_scope
153 ,x_last_update_date
154 ,x_last_updated_by
155 ,x_creation_date
156 ,x_created_by
157 ,x_minimum_amount
158 ,x_maximum_amount
159 ,x_minimum_percent
160 ,x_maximum_percent
161 ,x_last_update_login
162 ,x_attribute_category
163 ,x_attribute1
164 ,x_attribute2
165 ,x_attribute3
166 ,x_attribute4
167 ,x_attribute5
168 ,x_attribute6
169 ,x_attribute7
170 ,x_attribute8
171 ,x_attribute9
172 ,x_attribute10
173 ,x_attribute11
174 ,x_attribute12
175 ,x_attribute13
176 ,x_attribute14
177 ,x_attribute15
178 );
179
180 /* Check if a valid record was created. */
181 OPEN c;
182 FETCH c INTO x_rowid;
183 IF (c%NOTFOUND) THEN
184 CLOSE c;
185 RAISE NO_DATA_FOUND;
186 END IF;
187 CLOSE c;
188
189 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_CONSTRAINTS_PKG.insert_row (-)');
190 END insert_row;
191
192
193 -------------------------------------------------------------------------------
194 -- PROCDURE : UPDATE_ROW
195 -- INVOKED FROM : UPDATE_ROW procedure
196 -- PURPOSE : updates the row
197 -- HISTORY :
198 -- 04-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_lease_constraints with
199 -- _ALL table.
200 -- 19-JAN-07 Prabhakar o Bug #5768023
201 -- Modified the update where condition based on
202 -- index_constraint_id.
203 -------------------------------------------------------------------------------
204 PROCEDURE update_row
205 (
206 x_rowid IN VARCHAR2
207 ,x_index_constraint_id IN NUMBER
208 ,x_index_lease_id IN NUMBER
209 ,x_scope IN VARCHAR2
210 ,x_last_update_date IN DATE
211 ,x_last_updated_by IN NUMBER
212 ,x_minimum_amount IN NUMBER
213 ,x_maximum_amount IN NUMBER
214 ,x_minimum_percent IN NUMBER
215 ,x_maximum_percent IN NUMBER
216 ,x_last_update_login IN NUMBER
217 ,x_attribute_category IN VARCHAR2
218 ,x_attribute1 IN VARCHAR2
219 ,x_attribute2 IN VARCHAR2
220 ,x_attribute3 IN VARCHAR2
221 ,x_attribute4 IN VARCHAR2
222 ,x_attribute5 IN VARCHAR2
223 ,x_attribute6 IN VARCHAR2
224 ,x_attribute7 IN VARCHAR2
225 ,x_attribute8 IN VARCHAR2
226 ,x_attribute9 IN VARCHAR2
227 ,x_attribute10 IN VARCHAR2
228 ,x_attribute11 IN VARCHAR2
229 ,x_attribute12 IN VARCHAR2
230 ,x_attribute13 IN VARCHAR2
231 ,x_attribute14 IN VARCHAR2
232 ,x_attribute15 IN VARCHAR2
233 )
234 IS
235 l_return_status VARCHAR2 (30) := NULL;
236 BEGIN
237
238 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_CONSTRAINTS_PKG.update_row (+)');
239
240 pn_index_lease_constraints_pkg.check_unq_constraint_scope (
241 l_return_status
242 ,x_index_constraint_id
243 ,x_index_lease_id
244 ,x_scope
245 );
246
247 IF (l_return_status IS NOT NULL) THEN
248 app_exception.raise_exception;
249 END IF;
250
251 UPDATE pn_index_lease_constraints_all
252 SET
253 index_lease_id = x_index_lease_id
254 ,scope = x_scope
255 ,last_update_date = x_last_update_date
256 ,last_updated_by = x_last_updated_by
257 ,minimum_amount = x_minimum_amount
258 ,maximum_amount = x_maximum_amount
259 ,minimum_percent = x_minimum_percent
260 ,maximum_percent = x_maximum_percent
261 ,last_update_login = x_last_update_login
262 ,attribute_category = x_attribute_category
263 ,attribute1 = x_attribute1
264 ,attribute2 = x_attribute2
265 ,attribute3 = x_attribute3
266 ,attribute4 = x_attribute4
267 ,attribute5 = x_attribute5
268 ,attribute6 = x_attribute6
269 ,attribute7 = x_attribute7
270 ,attribute8 = x_attribute8
271 ,attribute9 = x_attribute9
272 ,attribute10 = x_attribute10
273 ,attribute11 = x_attribute11
274 ,attribute12 = x_attribute12
275 ,attribute13 = x_attribute13
276 ,attribute14 = x_attribute14
277 ,attribute15 = x_attribute15
278 WHERE index_constraint_id = x_index_constraint_id;
279
280
281 IF (SQL%NOTFOUND) THEN
282 RAISE NO_DATA_FOUND;
283 END IF;
284
285 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_CONSTRAINTS_PKG.update_row (-)');
286
287 END update_row;
288
289 -------------------------------------------------------------------------------
290 -- PROCDURE : lock_row
291 -- INVOKED FROM : lock_row procedure
292 -- PURPOSE : locks the row
293 -- HISTORY :
294 -- 04-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_lease_constraints with
295 -- _ALL table and changed the where clause for the cursor.
296 -------------------------------------------------------------------------------
297 PROCEDURE lock_row
298 (
299 x_rowid IN VARCHAR2
300 ,x_index_constraint_id IN NUMBER
301 ,x_index_lease_id IN NUMBER
302 ,x_scope IN VARCHAR2
303 ,x_minimum_amount IN NUMBER
304 ,x_maximum_amount IN NUMBER
305 ,x_minimum_percent IN NUMBER
306 ,x_maximum_percent IN NUMBER
307 ,x_attribute_category IN VARCHAR2
308 ,x_attribute1 IN VARCHAR2
309 ,x_attribute2 IN VARCHAR2
310 ,x_attribute3 IN VARCHAR2
311 ,x_attribute4 IN VARCHAR2
312 ,x_attribute5 IN VARCHAR2
313 ,x_attribute6 IN VARCHAR2
314 ,x_attribute7 IN VARCHAR2
315 ,x_attribute8 IN VARCHAR2
316 ,x_attribute9 IN VARCHAR2
317 ,x_attribute10 IN VARCHAR2
318 ,x_attribute11 IN VARCHAR2
319 ,x_attribute12 IN VARCHAR2
320 ,x_attribute13 IN VARCHAR2
321 ,x_attribute14 IN VARCHAR2
322 ,x_attribute15 IN VARCHAR2
323 )
324 IS
325 CURSOR c1 IS
326 SELECT *
327 FROM pn_index_lease_constraints_all
328 WHERE index_constraint_id = x_index_constraint_id
329 FOR UPDATE OF index_constraint_id NOWAIT;
330
331 tlinfo c1%ROWTYPE;
332 BEGIN
333
334 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_CONSTRAINTS_PKG.lock_row (+)');
335 OPEN c1;
336 FETCH c1 INTO tlinfo;
337 IF (c1%NOTFOUND) THEN
338 CLOSE c1;
339 RETURN;
340 END IF;
341 CLOSE c1;
342
343 IF NOT (tlinfo.index_constraint_id = x_index_constraint_id) THEN
344 pn_var_rent_pkg.lock_row_exception('INDEX_CONSTRAINT_ID',tlinfo.index_constraint_id);
345 END IF;
346
347 IF NOT (tlinfo.index_lease_id = x_index_lease_id) THEN
348 pn_var_rent_pkg.lock_row_exception('INDEX_LEASE_ID',tlinfo.index_lease_id);
349 END IF;
350
351 IF NOT (tlinfo.scope = x_scope) THEN
352 pn_var_rent_pkg.lock_row_exception('SCOPE',tlinfo.scope);
353 END IF;
354
355 IF NOT ((tlinfo.minimum_amount = x_minimum_amount)
356 OR ((tlinfo.minimum_amount IS NULL) AND x_minimum_amount IS NULL)) THEN
357 pn_var_rent_pkg.lock_row_exception('MINIMUM_AMOUNT',tlinfo.minimum_amount);
358 END IF;
359
360 IF NOT ((tlinfo.maximum_amount = x_maximum_amount)
361 OR ((tlinfo.maximum_amount IS NULL) AND x_maximum_amount IS NULL)) THEN
362 pn_var_rent_pkg.lock_row_exception('MAXIMUM_AMOUNT',tlinfo.maximum_amount);
363 END IF;
364
365 IF NOT ((tlinfo.minimum_percent = x_minimum_percent)
366 OR ((tlinfo.minimum_percent IS NULL) AND x_minimum_percent IS NULL)) THEN
367 pn_var_rent_pkg.lock_row_exception('MINIMUM_PERCENT',tlinfo.minimum_percent);
368 END IF;
369
370 IF NOT ((tlinfo.maximum_percent = x_maximum_percent)
371 OR ((tlinfo.maximum_percent IS NULL) AND x_maximum_percent IS NULL)) THEN
372 pn_var_rent_pkg.lock_row_exception('MAXIMUM_PERCENT',tlinfo.maximum_percent);
373 END IF;
374
375 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_CONSTRAINTS_PKG.lock_row (-)');
376 END lock_row;
377
378
379 -------------------------------------------------------------------------------
380 -- PROCDURE : delete_row
381 -- INVOKED FROM : delete_row procedure
382 -- PURPOSE : deletes the row
383 -- HISTORY :
384 -- 04-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_lease_constraints with
385 -- _ALL table.
386 -------------------------------------------------------------------------------
387 PROCEDURE delete_row
388 (
389 x_rowid IN VARCHAR2
390 )
391 IS
392 BEGIN
393 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_CONSTRAINTS_PKG.delete_row (+)');
394
395 DELETE FROM pn_index_lease_constraints_all
396 WHERE ROWID = x_rowid;
397
398 IF (SQL%NOTFOUND) THEN
399 RAISE NO_DATA_FOUND;
400 END IF;
401
402 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_CONSTRAINTS_PKG.delete_row (-)');
403 END delete_row;
404
405
406 -------------------------------------------------------------------------------
407 -- PROCDURE : delete_row
408 -- INVOKED FROM : delete_row procedure
409 -- PURPOSE : deletes the row
410 -- NOTE : overrided delete_row procedure to take PK as in parameter
411 -- HISTORY :
412 -- 04-JUL-05 hrodda o Created.
413 -------------------------------------------------------------------------------
414 PROCEDURE delete_row (
415 x_index_constraint_id IN NUMBER
416 )
417 IS
418 BEGIN
419 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_CONSTRAINTS_PKG.delete_row (+)');
420 DELETE FROM pn_index_lease_constraints_all
421 WHERE index_constraint_id = x_index_constraint_id;
422
423 IF (SQL%NOTFOUND) THEN
424 RAISE NO_DATA_FOUND;
425 END IF;
426
427 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_CONSTRAINTS_PKG.delete_row (-)');
428 END delete_row;
429
430
431 -------------------------------------------------------------------------------
432 -- PROCDURE : check_unq_constraint_scope
433 -- INVOKED FROM : insert_row and update_row procedure
434 -- PURPOSE : Checks unique constraint.
435 -- HISTORY :
436 -- 04-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_lease_constraints with
437 -- _ALL table.
438 -------------------------------------------------------------------------------
439 PROCEDURE check_unq_constraint_scope
440 (
441 x_return_status IN OUT NOCOPY VARCHAR2
442 ,x_index_constraint_id IN NUMBER
443 ,x_index_lease_id IN NUMBER
444 ,x_scope IN VARCHAR2
445 )
446 IS
447 l_dummy NUMBER;
448 BEGIN
449 SELECT 1
450 INTO l_dummy
451 FROM DUAL
452 WHERE NOT EXISTS ( SELECT 1
453 FROM pn_index_lease_constraints_all
454 WHERE (scope = x_scope)
455 AND (index_lease_id = x_index_lease_id)
456 AND ( (x_index_constraint_id IS NULL)
457 OR (index_constraint_id <> x_index_constraint_id)
458 ));
459 EXCEPTION
460 WHEN NO_DATA_FOUND THEN
461 fnd_message.set_name ('PN', 'PN_DUP_INDEX_LEASE_NUMBER');
462 x_return_status := 'E';
463 END check_unq_constraint_scope;
464 END pn_index_lease_constraints_pkg;
[Home] [Help]