1 package body PN_VAR_CONSTRAINTS_PKG as
2 /* $Header: PNVRCONB.pls 120.3 2007/03/14 12:23:33 pikhar noship $ */
3
4 -------------------------------------------------------------------------------
5 -- PROCDURE : INSERT_ROW
6 -- INVOKED FROM : insert_row procedure
7 -- PURPOSE : inserts the row
8 -- HISTORY :
9 -- 04-JUL-05 Piagrawa o Bug 4284035 - Replaced PN_VAR_CONSTRAINTS with _ALL table.
10 -- 14-MAR-07 Pikhar o Bug 5930407. Commented call to CHECK_MAX_CONSTR
11 -------------------------------------------------------------------------------
12
13 procedure INSERT_ROW (
14 X_ROWID in out NOCOPY VARCHAR2,
15 X_CONSTRAINT_ID in out NOCOPY NUMBER,
16 X_CONSTRAINT_NUM in out NOCOPY NUMBER,
17 X_PERIOD_ID in NUMBER,
18 X_CONSTR_CAT_CODE in VARCHAR2,
19 X_TYPE_CODE in VARCHAR2,
20 X_AMOUNT in NUMBER,
21 X_AGREEMENT_TEMPLATE_ID in NUMBER,
22 X_CONSTR_TEMPLATE_ID in NUMBER,
23 X_CONSTR_DEFAULT_ID in NUMBER,
24 X_COMMENTS in VARCHAR2,
25 X_ATTRIBUTE_CATEGORY in VARCHAR2,
26 X_ATTRIBUTE1 in VARCHAR2,
27 X_ATTRIBUTE2 in VARCHAR2,
28 X_ATTRIBUTE3 in VARCHAR2,
29 X_ATTRIBUTE4 in VARCHAR2,
30 X_ATTRIBUTE5 in VARCHAR2,
31 X_ATTRIBUTE6 in VARCHAR2,
32 X_ATTRIBUTE7 in VARCHAR2,
33 X_ATTRIBUTE8 in VARCHAR2,
34 X_ATTRIBUTE9 in VARCHAR2,
35 X_ATTRIBUTE10 in VARCHAR2,
36 X_ATTRIBUTE11 in VARCHAR2,
37 X_ATTRIBUTE12 in VARCHAR2,
38 X_ATTRIBUTE13 in VARCHAR2,
39 X_ATTRIBUTE14 in VARCHAR2,
40 X_ATTRIBUTE15 in VARCHAR2,
41 X_ORG_ID in NUMBER,
42 X_CREATION_DATE in DATE,
43 X_CREATED_BY in NUMBER,
44 X_LAST_UPDATE_DATE in DATE,
45 X_LAST_UPDATED_BY in NUMBER,
46 X_LAST_UPDATE_LOGIN in NUMBER,
47 X_CONSTR_START_DATE in DATE,
48 X_CONSTR_END_DATE in DATE
49 )
50 IS
51 CURSOR C IS
52 SELECT ROWID
53 FROM PN_VAR_CONSTRAINTS_ALL
54 WHERE CONSTRAINT_ID = X_CONSTRAINT_ID;
55
56 l_return_status VARCHAR2(30) := NULL;
57
58 BEGIN
59
60 PNP_DEBUG_PKG.debug ('PN_VAR_CONSTRAINTS_PKG.INSERT_ROW (+)');
61
62 -------------------------------------------------------
63 -- We need to generate the constraints number
64 -------------------------------------------------------
65 SELECT nvl(max(CONSTRAINT_NUM),0)
66 INTO X_CONSTRAINT_NUM
67 FROM PN_VAR_CONSTRAINTS_ALL
68 WHERE PERIOD_ID = X_PERIOD_ID;
69
70 X_CONSTRAINT_NUM := X_CONSTRAINT_NUM + 1;
71
72 -------------------------------------------------------
73 -- Select the nextval for constraints id
74 -------------------------------------------------------
75 IF ( X_CONSTRAINT_ID IS NULL) THEN
76 SELECT pn_var_constraints_s.nextval
77 INTO X_CONSTRAINT_ID
78 FROM dual;
79 END IF;
80
81 -- Check for constraint range
82 /*l_return_status := NULL;
83 PN_VAR_CONSTRAINTS_PKG.CHECK_MAX_CONSTR
84 (
85 l_return_status,
86 x_period_id,
87 x_constraint_id,
88 x_constr_cat_code,
89 x_type_code,
90 x_amount
91 );
92 IF (l_return_status IS NOT NULL) THEN
93 APP_EXCEPTION.Raise_Exception;
94 END IF;*/
95
96
97 INSERT INTO PN_VAR_CONSTRAINTS_ALL
98 (
99 CONSTRAINT_ID,
100 CONSTRAINT_NUM,
101 PERIOD_ID,
102 CONSTR_CAT_CODE,
103 TYPE_CODE,
104 AMOUNT,
105 AGREEMENT_TEMPLATE_ID,
106 CONSTR_TEMPLATE_ID,
107 CONSTR_DEFAULT_ID,
108 COMMENTS,
109 ATTRIBUTE_CATEGORY,
110 ATTRIBUTE1,
111 ATTRIBUTE2,
112 ATTRIBUTE3,
113 ATTRIBUTE4,
114 ATTRIBUTE5,
115 ATTRIBUTE6,
116 ATTRIBUTE7,
117 ATTRIBUTE8,
118 ATTRIBUTE9,
119 ATTRIBUTE10,
120 ATTRIBUTE11,
121 ATTRIBUTE12,
122 ATTRIBUTE13,
123 ATTRIBUTE14,
124 ATTRIBUTE15,
125 ORG_ID,
126 LAST_UPDATE_DATE,
127 LAST_UPDATED_BY,
128 CREATION_DATE,
129 CREATED_BY,
130 LAST_UPDATE_LOGIN,
131 CONSTR_START_DATE,
132 CONSTR_END_DATE
133 )
134 VALUES
135 (
136 X_CONSTRAINT_ID,
137 X_CONSTRAINT_NUM,
138 X_PERIOD_ID,
139 X_CONSTR_CAT_CODE,
140 X_TYPE_CODE,
141 X_AMOUNT,
142 X_AGREEMENT_TEMPLATE_ID,
143 X_CONSTR_TEMPLATE_ID,
144 X_CONSTR_DEFAULT_ID,
145 X_COMMENTS,
146 X_ATTRIBUTE_CATEGORY,
147 X_ATTRIBUTE1,
148 X_ATTRIBUTE2,
149 X_ATTRIBUTE3,
150 X_ATTRIBUTE4,
151 X_ATTRIBUTE5,
152 X_ATTRIBUTE6,
153 X_ATTRIBUTE7,
154 X_ATTRIBUTE8,
155 X_ATTRIBUTE9,
156 X_ATTRIBUTE10,
157 X_ATTRIBUTE11,
158 X_ATTRIBUTE12,
159 X_ATTRIBUTE13,
160 X_ATTRIBUTE14,
161 X_ATTRIBUTE15,
162 X_ORG_ID,
163 X_LAST_UPDATE_DATE,
164 X_LAST_UPDATED_BY,
165 X_CREATION_DATE,
166 X_CREATED_BY,
167 X_LAST_UPDATE_LOGIN,
168 X_CONSTR_START_DATE,
169 X_CONSTR_END_DATE);
170
171 OPEN c;
172 FETCH c INTO X_ROWID;
173 IF (c%notfound) THEN
174 CLOSE c;
175 RAISE NO_DATA_FOUND;
176 END IF;
177 CLOSE c;
178
179 PNP_DEBUG_PKG.debug ('PN_VAR_CONSTRAINTS_PKG.INSERT_ROW (-)');
180
181 END INSERT_ROW;
182
183 -------------------------------------------------------------------------------
184 -- PROCDURE : LOCK_ROW
185 -- INVOKED FROM : LOCK_ROW procedure
186 -- PURPOSE : loacks the row
187 -- HISTORY :
188 -- 04-JUL-05 piagrawa o Bug 4284035 - Replaced PN_VAR_CONSTRAINTS with _ALL table.
189 -------------------------------------------------------------------------------
190
191 procedure LOCK_ROW (
192 X_CONSTRAINT_ID in NUMBER,
193 X_CONSTRAINT_NUM in NUMBER,
194 X_PERIOD_ID in NUMBER,
195 X_CONSTR_CAT_CODE in VARCHAR2,
196 X_TYPE_CODE in VARCHAR2,
197 X_AMOUNT in NUMBER,
198 X_AGREEMENT_TEMPLATE_ID in NUMBER,
199 X_CONSTR_TEMPLATE_ID in NUMBER,
200 X_CONSTR_DEFAULT_ID in NUMBER,
201 X_COMMENTS in VARCHAR2,
202 X_ATTRIBUTE_CATEGORY in VARCHAR2,
203 X_ATTRIBUTE1 in VARCHAR2,
204 X_ATTRIBUTE2 in VARCHAR2,
205 X_ATTRIBUTE3 in VARCHAR2,
206 X_ATTRIBUTE4 in VARCHAR2,
207 X_ATTRIBUTE5 in VARCHAR2,
208 X_ATTRIBUTE6 in VARCHAR2,
209 X_ATTRIBUTE7 in VARCHAR2,
210 X_ATTRIBUTE8 in VARCHAR2,
211 X_ATTRIBUTE9 in VARCHAR2,
212 X_ATTRIBUTE10 in VARCHAR2,
213 X_ATTRIBUTE11 in VARCHAR2,
214 X_ATTRIBUTE12 in VARCHAR2,
215 X_ATTRIBUTE13 in VARCHAR2,
216 X_ATTRIBUTE14 in VARCHAR2,
217 X_ATTRIBUTE15 in VARCHAR2,
218 X_CONSTR_START_DATE in DATE,
219 X_CONSTR_END_DATE in DATE
220 )
221 IS
222 CURSOR c1 IS
223 SELECT *
224 FROM PN_VAR_CONSTRAINTS_ALL
225 WHERE CONSTRAINT_ID = X_CONSTRAINT_ID
226 FOR UPDATE OF CONSTRAINT_ID NOWAIT;
227
228 tlinfo c1%rowtype;
229
230 BEGIN
231 PNP_DEBUG_PKG.debug ('PN_VAR_CONSTRAINTS_PKG.LOCK_ROW (+)');
232
233 OPEN c1;
234 FETCH c1 INTO tlinfo;
235 IF (c1%notfound) THEN
236 CLOSE c1;
237 RETURN;
238 END IF;
239 CLOSE c1;
240
241
242 if (tlinfo.CONSTRAINT_ID = X_CONSTRAINT_ID) then
243 null;
244 else
245 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('CONSTRAINT_ID', to_char(tlinfo.CONSTRAINT_ID));
246 end if;
247
248 if (tlinfo.CONSTRAINT_NUM = X_CONSTRAINT_NUM) then
249 null;
250 else
251 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('CONSTRAINT_NUM',to_char(tlinfo.CONSTRAINT_NUM));
252 end if;
253
254 if ((tlinfo.PERIOD_ID = X_PERIOD_ID)
255 OR ((tlinfo.PERIOD_ID is null) AND (X_PERIOD_ID is null))) then
256 null;
257 else
258 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('PERIOD_ID',to_char(tlinfo.PERIOD_ID));
259 end if;
260
261 if (tlinfo.CONSTR_CAT_CODE = X_CONSTR_CAT_CODE) then
262 null;
263 else
264 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('CONSTR_CAT_CODE',tlinfo.CONSTR_CAT_CODE);
265 end if;
266
267 if (tlinfo.TYPE_CODE = X_TYPE_CODE) then
268 null;
269 else
270 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('TYPE_CODE',tlinfo.TYPE_CODE);
271 end if;
272
273 if ((tlinfo.AMOUNT = X_AMOUNT)
274 OR ((tlinfo.AMOUNT is null) AND (X_AMOUNT is null))) then
275 null;
276 else
277 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('AMOUNT',to_char(tlinfo.AMOUNT));
278 end if;
279
280 if ((tlinfo.AGREEMENT_TEMPLATE_ID = X_AGREEMENT_TEMPLATE_ID)
281 OR ((tlinfo.AGREEMENT_TEMPLATE_ID is null) AND (X_AGREEMENT_TEMPLATE_ID is null))) then
282 null;
283 else
284 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('AGREEMENT_TEMPLATE_ID',to_char(tlinfo.AGREEMENT_TEMPLATE_ID));
285 end if;
286
287 if ((tlinfo.CONSTR_DEFAULT_ID = X_CONSTR_DEFAULT_ID)
288 OR ((tlinfo.CONSTR_DEFAULT_ID is null) AND (X_CONSTR_DEFAULT_ID is null))) then
289 null;
290 else
291 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('CONSTR_DEFAULT_ID',to_char(tlinfo.CONSTR_DEFAULT_ID));
292 end if;
293
294 if ((tlinfo.CONSTR_TEMPLATE_ID = X_CONSTR_TEMPLATE_ID)
295 OR ((tlinfo.CONSTR_TEMPLATE_ID is null) AND (X_CONSTR_TEMPLATE_ID is null))) then
296 null;
297 else
298 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('CONSTR_TEMPLATE_ID',to_char(tlinfo.CONSTR_TEMPLATE_ID));
299 end if;
300
301 if ((tlinfo.COMMENTS = X_COMMENTS)
302 OR ((tlinfo.COMMENTS is null) AND (X_COMMENTS is null))) then
303 null;
304 else
305 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('COMMENTS',tlinfo.COMMENTS);
306 end if;
307
308 if ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
309 OR ((tlinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null))) then
310 null;
311 else
312 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE_CATEGORY',tlinfo.ATTRIBUTE_CATEGORY);
313 end if;
314
315 if ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
316 OR ((tlinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null))) then
317 null;
318 else
319 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE1',tlinfo.ATTRIBUTE1);
320 end if;
321
322 if ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
323 OR ((tlinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null))) then
324 null;
325 else
326 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE2',tlinfo.ATTRIBUTE2);
327 end if;
328
329 if ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
330 OR ((tlinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null))) then
331 null;
332 else
333 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE3',tlinfo.ATTRIBUTE3);
334 end if;
335
336 if ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
337 OR ((tlinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null))) then
338 null;
339 else
340 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE4',tlinfo.ATTRIBUTE4);
341 end if;
342
343 if ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
344 OR ((tlinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null))) then
345 null;
346 else
347 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE5',tlinfo.ATTRIBUTE5);
348 end if;
349
350 if ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
351 OR ((tlinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null))) then
352 null;
353 else
354 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE6',tlinfo.ATTRIBUTE6);
355 end if;
356
357 if ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
358 OR ((tlinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null))) then
359 null;
360 else
361 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE7',tlinfo.ATTRIBUTE7);
362 end if;
363
364 if ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
365 OR ((tlinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null))) then
366 null;
367 else
368 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE8',tlinfo.ATTRIBUTE8);
369 end if;
370
371 if ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
372 OR ((tlinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null))) then
373 null;
374 else
375 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE9',tlinfo.ATTRIBUTE9);
376 end if;
377
378 if ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
379 OR ((tlinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null))) then
380 null;
381 else
382 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE10', tlinfo.ATTRIBUTE10);
383 end if;
384
385 if ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
386 OR ((tlinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null))) then
387 null;
388 else
389 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE11',tlinfo.ATTRIBUTE11);
390 end if;
391
392 if ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
393 OR ((tlinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null))) then
394 null;
395 else
396 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE12',tlinfo.ATTRIBUTE12);
397 end if;
398
399 if ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
400 OR ((tlinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null))) then
401 null;
402 else
403 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE13',tlinfo.ATTRIBUTE13);
404 end if;
405
406 if ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
407 OR ((tlinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null))) then
408 null;
409 else
410 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE14',tlinfo.ATTRIBUTE14);
411 end if;
412
413 if ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
414 OR ((tlinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null))) then
415 null;
416 else
417 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE15',tlinfo.ATTRIBUTE15);
418 end if;
419
420 if ((tlinfo.CONSTR_START_DATE = X_CONSTR_START_DATE)
421 OR ((tlinfo.CONSTR_START_DATE is null) AND (X_CONSTR_START_DATE is null))) then
422 null;
423 else
424 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('CONSTR_START_DATE',tlinfo.CONSTR_START_DATE);
425 end if;
426 if ((tlinfo.CONSTR_END_DATE = X_CONSTR_END_DATE)
427 OR ((tlinfo.CONSTR_END_DATE is null) AND (X_CONSTR_END_DATE is null))) then
428 null;
429 else
430 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('CONSTR_END_DATE',tlinfo.CONSTR_END_DATE);
431 end if;
432
433 PNP_DEBUG_PKG.debug ('PN_VAR_CONSTRAINTS_PKG.LOCK_ROW (-)');
434
435 END LOCK_ROW;
436
437
438 -------------------------------------------------------------------------------
439 -- PROCDURE : UPDATE_ROW
440 -- INVOKED FROM : UPDATE_ROW procedure
441 -- PURPOSE : updates the row
442 -- HISTORY :
443 -- 04-JUL-05 piagrawa o Bug 4284035 - Replaced PN_VAR_CONSTRAINTS with _ALL table.
447 procedure UPDATE_ROW (
444 -- 14-MAR-07 Pikhar o Bug 5930407. Commented call to CHECK_MAX_CONSTR
445 -------------------------------------------------------------------------------
446
448 X_CONSTRAINT_ID in NUMBER,
449 X_CONSTRAINT_NUM in NUMBER,
450 X_PERIOD_ID in NUMBER,
451 X_CONSTR_CAT_CODE in VARCHAR2,
452 X_TYPE_CODE in VARCHAR2,
453 X_AMOUNT in NUMBER,
454 X_AGREEMENT_TEMPLATE_ID in NUMBER,
455 X_CONSTR_TEMPLATE_ID in NUMBER,
456 X_CONSTR_DEFAULT_ID in NUMBER,
457 X_COMMENTS in VARCHAR2,
458 X_ATTRIBUTE_CATEGORY in VARCHAR2,
459 X_ATTRIBUTE1 in VARCHAR2,
460 X_ATTRIBUTE2 in VARCHAR2,
461 X_ATTRIBUTE3 in VARCHAR2,
462 X_ATTRIBUTE4 in VARCHAR2,
463 X_ATTRIBUTE5 in VARCHAR2,
464 X_ATTRIBUTE6 in VARCHAR2,
465 X_ATTRIBUTE7 in VARCHAR2,
466 X_ATTRIBUTE8 in VARCHAR2,
467 X_ATTRIBUTE9 in VARCHAR2,
468 X_ATTRIBUTE10 in VARCHAR2,
469 X_ATTRIBUTE11 in VARCHAR2,
470 X_ATTRIBUTE12 in VARCHAR2,
471 X_ATTRIBUTE13 in VARCHAR2,
472 X_ATTRIBUTE14 in VARCHAR2,
473 X_ATTRIBUTE15 in VARCHAR2,
474 X_LAST_UPDATE_DATE in DATE,
475 X_LAST_UPDATED_BY in NUMBER,
476 X_LAST_UPDATE_LOGIN in NUMBER,
477 X_CONSTR_START_DATE in DATE,
478 X_CONSTR_END_DATE in DATE
479 )
480 IS
481 l_return_status VARCHAR2(30) := NULL;
482
483 BEGIN
484
485 PNP_DEBUG_PKG.debug ('PN_VAR_CONSTRAINTS_PKG.UPDATE_ROW (+)');
486
487 -- Check for constraint range
488 l_return_status := NULL;
489
490 /*PN_VAR_CONSTRAINTS_PKG.CHECK_MAX_CONSTR
491 (
492 l_return_status,
493 x_period_id,
494 x_constraint_id,
495 x_constr_cat_code,
496 x_type_code,
497 x_amount
498 );
499 IF (l_return_status IS NOT NULL) THEN
500 APP_EXCEPTION.Raise_Exception;
501 END IF;*/
502
503 UPDATE PN_VAR_CONSTRAINTS_ALL SET
504 CONSTRAINT_NUM = X_CONSTRAINT_NUM,
505 PERIOD_ID = X_PERIOD_ID,
506 CONSTR_CAT_CODE = X_CONSTR_CAT_CODE,
507 TYPE_CODE = X_TYPE_CODE,
508 AMOUNT = X_AMOUNT,
509 AGREEMENT_TEMPLATE_ID = X_AGREEMENT_TEMPLATE_ID,
510 CONSTR_TEMPLATE_ID = X_CONSTR_TEMPLATE_ID,
511 CONSTR_DEFAULT_ID = X_CONSTR_DEFAULT_ID,
512 COMMENTS = X_COMMENTS,
513 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
514 ATTRIBUTE1 = X_ATTRIBUTE1,
515 ATTRIBUTE2 = X_ATTRIBUTE2,
516 ATTRIBUTE3 = X_ATTRIBUTE3,
517 ATTRIBUTE4 = X_ATTRIBUTE4,
518 ATTRIBUTE5 = X_ATTRIBUTE5,
519 ATTRIBUTE6 = X_ATTRIBUTE6,
520 ATTRIBUTE7 = X_ATTRIBUTE7,
521 ATTRIBUTE8 = X_ATTRIBUTE8,
522 ATTRIBUTE9 = X_ATTRIBUTE9,
523 ATTRIBUTE10 = X_ATTRIBUTE10,
524 ATTRIBUTE11 = X_ATTRIBUTE11,
525 ATTRIBUTE12 = X_ATTRIBUTE12,
526 ATTRIBUTE13 = X_ATTRIBUTE13,
527 ATTRIBUTE14 = X_ATTRIBUTE14,
528 ATTRIBUTE15 = X_ATTRIBUTE15,
529 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
530 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
531 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
532 CONSTR_START_DATE = X_CONSTR_START_DATE,
533 CONSTR_END_DATE = X_CONSTR_END_DATE
534 WHERE CONSTRAINT_ID = X_CONSTRAINT_ID;
535
536 IF (sql%notfound) THEN
537 RAISE NO_DATA_FOUND;
538 END IF;
539
540 PNP_DEBUG_PKG.debug ('PN_VAR_CONSTRAINTS_PKG.UPDATE_ROW (+)');
541
542 END UPDATE_ROW;
543
544
545 -------------------------------------------------------------------------------
546 -- PROCDURE : DELETE_ROW
547 -- INVOKED FROM : DELETE_ROW procedure
548 -- PURPOSE : deletes the row
549 -- HISTORY :
550 -- 04-JUL-05 piagrawa o Bug 4284035 - Replaced PN_VAR_CONSTRAINTS with _ALL table.
551 -------------------------------------------------------------------------------
552
553 procedure DELETE_ROW (
554 X_CONSTRAINT_ID in NUMBER
555 )
556 IS
557 BEGIN
558
559 PNP_DEBUG_PKG.debug ('PN_VAR_CONSTRAINTS_PKG.DELETE_ROW (+)');
560
561 DELETE FROM PN_VAR_CONSTRAINTS_ALL
562 WHERE CONSTRAINT_ID = X_CONSTRAINT_ID;
563
564 IF (sql%notfound) THEN
565 RAISE NO_DATA_FOUND;
566 END IF;
567
568 PNP_DEBUG_PKG.debug ('PN_VAR_CONSTRAINTS_PKG.DELETE_ROW (-)');
569
570 END DELETE_ROW;
571
572 -------------------------------------------------------------------------------
573 -- PROCDURE : CHECK_MAX_CONSTR
574 -- INVOKED FROM : INSERT_ROW and UPDATE_ROW procedure
575 -- PURPOSE : checks for maximum constraint
576 -- HISTORY :
577 -- 04-JUL-05 piagrawa o Bug 4284035 - Replaced PN_VAR_CONSTRAINTS with _ALL table.
578 -------------------------------------------------------------------------------
579
580 PROCEDURE CHECK_MAX_CONSTR
581 (
582 x_return_status IN OUT NOCOPY VARCHAR2,
583 x_period_id IN NUMBER,
584 x_constraint_id IN NUMBER,
585 x_constr_cat_code IN VARCHAR2,
586 x_type_code IN VARCHAR2,
587 x_amount IN NUMBER
588 )
589 IS
590 l_dummy NUMBER;
591 BEGIN
592 PNP_DEBUG_PKG.debug ('PN_VAR_RENTS_PKG.CHECK_MAX_CONSTR (+)');
593
594 IF x_type_code = 'MIN' THEN
595
596 SELECT 1
597 INTO l_dummy
598 FROM dual
599 WHERE not exists
600 (
601 SELECT 1
602 FROM pn_var_constraints_all constr
603 WHERE constr.amount < (x_amount)
604 AND ((x_constraint_id is null) or
605 (constr.constraint_id <> x_constraint_id))
606 AND constr.period_id = x_period_id
607 AND constr.constr_cat_code = x_constr_cat_code
608 AND constr.type_code = 'MAX'
609 );
610
611 END IF;
612
613 PNP_DEBUG_PKG.debug ('PN_VAR_RENTS_PKG.CHECK_MAX_CONSTR (-)');
614
615 EXCEPTION
616 WHEN NO_DATA_FOUND THEN
617 fnd_message.set_name ('PN','PN_VAR_WRONG_RANGE');
618 --fnd_message.set_token('RENT_NUMBER',
619 --x_rent_num);
620 x_return_status := 'E';
621 END CHECK_MAX_CONSTR;
622
623 END PN_VAR_CONSTRAINTS_PKG;
[Home] [Help]