1 PACKAGE BODY pn_lease_changes_pkg AS
2 -- $Header: PNTLCHGB.pls 120.3 2006/01/20 03:56:27 appldev ship $
3
4 -------------------------------------------------------------------------------
5 -- PROCDURE : INSERT_ROW
6 -- INVOKED FROM : insert_row procedure
7 -- PURPOSE : inserts the row
8 -- HISTORY :
9 -- 21-JUN-05 sdmahesh o Bug 4284035 - Replaced pn_lease_transactions,
10 -- pn_lease_changes with _ALL table.
11 -- 28-NOV-05 pikhar o fetched org_id using cursor
12 -- 18-JAN-06 piagrawa o Bug#4931780 - Added parameter x_cutoff_date in
13 -- Insert_Row.
14 -------------------------------------------------------------------------------
15 PROCEDURE Insert_Row
16 (
17 X_ROWID IN OUT NOCOPY VARCHAR2,
18 X_LEASE_CHANGE_ID IN OUT NOCOPY NUMBER,
19 X_LEASE_ID IN NUMBER,
20 X_LEASE_CHANGE_NUMBER IN OUT NOCOPY NUMBER,
21 X_LEASE_CHANGE_NAME IN VARCHAR2,
22 X_RESPONSIBLE_USER IN NUMBER,
23 X_CHANGE_COMMENCEMENT_DATE IN DATE,
24 X_CHANGE_TERMINATION_DATE IN DATE,
25 X_CHANGE_TYPE_LOOKUP_CODE IN VARCHAR2,
26 X_CHANGE_EXECUTION_DATE IN DATE,
27 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
28 X_ATTRIBUTE1 IN VARCHAR2,
29 X_ATTRIBUTE2 IN VARCHAR2,
30 X_ATTRIBUTE3 IN VARCHAR2,
31 X_ATTRIBUTE4 IN VARCHAR2,
32 X_ATTRIBUTE5 IN VARCHAR2,
33 X_ATTRIBUTE6 IN VARCHAR2,
34 X_ATTRIBUTE7 IN VARCHAR2,
35 X_ATTRIBUTE8 IN VARCHAR2,
36 X_ATTRIBUTE9 IN VARCHAR2,
37 X_ATTRIBUTE10 IN VARCHAR2,
38 X_ATTRIBUTE11 IN VARCHAR2,
39 X_ATTRIBUTE12 IN VARCHAR2,
40 X_ATTRIBUTE13 IN VARCHAR2,
41 X_ATTRIBUTE14 IN VARCHAR2,
42 X_ATTRIBUTE15 IN VARCHAR2,
43 X_ABSTRACTED_BY_USER IN NUMBER,
44 X_CREATION_DATE IN DATE,
45 X_CREATED_BY IN NUMBER,
46 X_LAST_UPDATE_DATE IN DATE,
47 X_LAST_UPDATED_BY IN NUMBER,
48 X_LAST_UPDATE_LOGIN IN NUMBER,
49 x_org_id IN NUMBER,
50 x_cutoff_date IN DATE
51 )
52 IS
53
54 CURSOR C IS
55 SELECT ROWID
56 FROM pn_lease_changes_all
57 WHERE lease_change_id = x_lease_change_id;
58
59 l_leaseTransactionId NUMBER := NULL;
60 l_return_status VARCHAR2(30) := NULL;
61
62 CURSOR org_cur IS
63 SELECT org_id
64 FROM pn_leases_all
65 WHERE lease_id = x_lease_id;
66
67 l_org_id NUMBER;
68
69
70 BEGIN
71
72 pnp_debug_pkg.debug ('PN_LEASE_CHANGES_PKG.Insert_Row (+)');
73 --------------------------------------------------------
74 -- IF the LEASE_CHANGE_NUMBER IS NULL THEN we need to
75 -- generate it
76 --------------------------------------------------------
77 IF (x_change_type_lookup_code IN ('AMEND', 'EDIT'))
78 THEN
79 IF (x_lease_change_number IS NULL)
80 THEN
81 SELECT NVL(MAX(pnc.lease_change_number), 0)
82 INTO x_lease_change_number
83 FROM pn_lease_changes_all pnc
84 WHERE pnc.lease_id = x_lease_id
85 AND pnc.change_type_lookup_code = x_change_type_lookup_code;
86
87 x_lease_change_number := x_lease_change_number + 1;
88
89 END IF;
90 ELSE
91 X_LEASE_CHANGE_NUMBER := NULL;
92 END IF;
93
94
95 --------------------------------------------------------
96 -- We need to check IF the Lease Change Number IS unique
97 -- within a lease AND Change Type Lookup Code
98 --------------------------------------------------------
99 l_return_status := NULL;
100 PN_LEASE_CHANGES_PKG.CHECK_UNIQUE_CHANGE_NUMBER
101 (
102 x_return_status => l_return_status
103 ,X_LEASE_ID => X_LEASE_ID
104 ,X_CHANGE_TYPE_LOOKUP_CODE => X_CHANGE_TYPE_LOOKUP_CODE
105 ,X_LEASE_CHANGE_NUMBER => X_LEASE_CHANGE_NUMBER
106 );
107 IF (l_return_status IS NOT NULL) THEN
108 app_exception.Raise_Exception;
109 END IF;
110
111
112 --------------------------------------------------------
113 -- We need to INSERT a row IN pn_lease_transactions
114 --------------------------------------------------------
115 SELECT pn_lease_transactions_s.NEXTVAL
116 INTO l_leaseTransactionId
117 FROM DUAL;
118
119 IF x_org_id IS NULL THEN
120 FOR rec IN org_cur LOOP
121 l_org_id := rec.org_id;
122 END LOOP;
123 ELSE
124 l_org_id := x_org_id;
125 END IF;
126
127 INSERT INTO pn_lease_transactions_all
128 (
129 LEASE_TRANSACTION_ID
130 ,LEASE_ID
131 ,LOCATION_ID
132 ,TRANSACTION_TYPE
133 ,LAST_UPDATE_DATE
134 ,LAST_UPDATED_BY
135 ,CREATION_DATE
136 ,CREATED_BY
137 ,LAST_UPDATE_LOGIN
138 ,DATE_EFFECTIVE
139 ,org_id
140 )
141 VALUES
142 (
143 l_leaseTransactionId
144 ,X_LEASE_ID
145 ,NULL
146 ,X_CHANGE_TYPE_LOOKUP_CODE
147 ,X_LAST_UPDATE_DATE
148 ,X_LAST_UPDATED_BY
149 ,X_CREATION_DATE
150 ,X_CREATED_BY
151 ,X_LAST_UPDATE_LOGIN
152 ,X_CREATION_DATE
153 ,l_org_id
154 );
155
156 --------------------------------------------------------
157 -- We need INSERT row INTO PN_LEASE_CHANGES
158 --------------------------------------------------------
159 IF (x_lease_change_id IS NULL) THEN
160 SELECT pn_lease_changes_s.NEXTVAL
161 INTO x_lease_change_id
162 FROM DUAL;
163 END IF;
164
165 INSERT INTO pn_lease_changes_all
166 (
167 LEASE_CHANGE_ID
168 ,LEASE_ID
169 ,LEASE_TRANSACTION_ID
170 ,LEASE_CHANGE_NUMBER
171 ,LEASE_CHANGE_NAME
172 ,RESPONSIBLE_USER
173 ,CHANGE_COMMENCEMENT_DATE
174 ,CHANGE_TERMINATION_DATE
175 ,CHANGE_TYPE_LOOKUP_CODE
176 ,CHANGE_EXECUTION_DATE
177 ,ATTRIBUTE_CATEGORY
178 ,ATTRIBUTE1
179 ,ATTRIBUTE2
180 ,ATTRIBUTE3
181 ,ATTRIBUTE4
182 ,ATTRIBUTE5
183 ,ATTRIBUTE6
184 ,ATTRIBUTE7
185 ,ATTRIBUTE8
186 ,ATTRIBUTE9
187 ,ATTRIBUTE10
188 ,ATTRIBUTE11
189 ,ATTRIBUTE12
190 ,ATTRIBUTE13
191 ,ATTRIBUTE14
192 ,ATTRIBUTE15
193 ,ABSTRACTED_BY_USER
194 ,CREATION_DATE
195 ,CREATED_BY
196 ,LAST_UPDATE_DATE
197 ,LAST_UPDATED_BY
198 ,LAST_UPDATE_LOGIN
199 ,org_id
200 ,CUTOFF_DATE
201 )
202 VALUES
203 (
204 X_LEASE_CHANGE_ID
205 ,X_LEASE_ID
206 ,l_leaseTransactionId
207 ,X_LEASE_CHANGE_NUMBER
208 ,X_LEASE_CHANGE_NAME
209 ,X_RESPONSIBLE_USER
210 ,X_CHANGE_COMMENCEMENT_DATE
211 ,X_CHANGE_TERMINATION_DATE
212 ,X_CHANGE_TYPE_LOOKUP_CODE
213 ,X_CHANGE_EXECUTION_DATE
214 ,X_ATTRIBUTE_CATEGORY
215 ,X_ATTRIBUTE1
216 ,X_ATTRIBUTE2
217 ,X_ATTRIBUTE3
218 ,X_ATTRIBUTE4
219 ,X_ATTRIBUTE5
220 ,X_ATTRIBUTE6
221 ,X_ATTRIBUTE7
222 ,X_ATTRIBUTE8
223 ,X_ATTRIBUTE9
224 ,X_ATTRIBUTE10
225 ,X_ATTRIBUTE11
226 ,X_ATTRIBUTE12
227 ,X_ATTRIBUTE13
228 ,X_ATTRIBUTE14
229 ,X_ATTRIBUTE15
230 ,X_ABSTRACTED_BY_USER
231 ,X_CREATION_DATE
232 ,X_CREATED_BY
233 ,X_LAST_UPDATE_DATE
234 ,X_LAST_UPDATED_BY
235 ,X_LAST_UPDATE_LOGIN
236 ,l_org_id
237 ,x_cutoff_date
238 );
239
240 OPEN c;
241 FETCH c INTO X_ROWID;
242 IF (c%NOTFOUND) THEN
243 CLOSE c;
244 RAISE NO_DATA_FOUND;
245 END IF;
246 CLOSE c;
247
248 pnp_debug_pkg.debug ('PN_LEASE_CHANGES_PKG.Insert_Row (-)');
249
250 END Insert_Row;
251
252 -------------------------------------------------------------------------------
253 -- PROCDURE : Lock_Row
254 -- INVOKED FROM : Lock_Row procedure
255 -- PURPOSE : locks the row
256 -- HISTORY :
257 -- 21-JUN-05 sdmahesh o Bug 4284035 - Replaced pn_lease_changes with _ALL table.
258 -------------------------------------------------------------------------------
259 PROCEDURE Lock_Row
260 (
261 X_LEASE_CHANGE_ID IN NUMBER,
262 X_RESPONSIBLE_USER IN NUMBER,
263 X_CHANGE_COMMENCEMENT_DATE IN DATE,
264 X_CHANGE_TERMINATION_DATE IN DATE,
265 X_CHANGE_TYPE_LOOKUP_CODE IN VARCHAR2,
266 X_CHANGE_EXECUTION_DATE IN DATE,
267 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
268 X_ATTRIBUTE1 IN VARCHAR2,
269 X_ATTRIBUTE2 IN VARCHAR2,
270 X_ATTRIBUTE3 IN VARCHAR2,
271 X_ATTRIBUTE4 IN VARCHAR2,
272 X_ATTRIBUTE5 IN VARCHAR2,
273 X_ATTRIBUTE6 IN VARCHAR2,
274 X_ATTRIBUTE7 IN VARCHAR2,
275 X_ATTRIBUTE8 IN VARCHAR2,
276 X_ATTRIBUTE9 IN VARCHAR2,
277 X_ATTRIBUTE10 IN VARCHAR2,
278 X_ATTRIBUTE11 IN VARCHAR2,
279 X_ATTRIBUTE12 IN VARCHAR2,
280 X_ATTRIBUTE13 IN VARCHAR2,
281 X_ATTRIBUTE14 IN VARCHAR2,
282 X_ATTRIBUTE15 IN VARCHAR2,
283 X_LEASE_ID IN NUMBER,
284 X_LEASE_TRANSACTION_ID IN NUMBER,
285 X_LEASE_CHANGE_NUMBER IN NUMBER,
286 X_LEASE_CHANGE_NAME IN VARCHAR2,
287 X_ABSTRACTED_BY_USER IN NUMBER
288 )
289 IS
290 CURSOR c1 IS
291 SELECT *
292 FROM pn_lease_changes_all
293 WHERE lease_change_id = x_lease_change_id
294 FOR UPDATE OF lease_change_id NOWAIT;
295
296 tlinfo c1%ROWTYPE;
297
298 BEGIN
299 pnp_debug_pkg.debug ('PN_LEASE_CHANGES_PKG.Lock_Row (+)');
300 OPEN c1;
301 FETCH c1 INTO tlinfo;
302 IF (c1%NOTFOUND) THEN
303 CLOSE c1;
304 RETURN;
305 END IF;
306 CLOSE c1;
307
308 IF NOT (tlinfo.LEASE_CHANGE_ID = X_LEASE_CHANGE_ID) THEN
309 pn_var_rent_pkg.lock_row_exception('LEASE_CHANGE_ID',tlinfo.LEASE_CHANGE_ID);
310 END IF;
311
312 IF NOT ((tlinfo.RESPONSIBLE_USER = X_RESPONSIBLE_USER)
313 OR ((tlinfo.RESPONSIBLE_USER IS NULL) AND (X_RESPONSIBLE_USER IS NULL))) THEN
314 pn_var_rent_pkg.lock_row_exception('RESPONSIBLE_USER',tlinfo.RESPONSIBLE_USER);
315 END IF;
316
317 IF NOT ((tlinfo.CHANGE_COMMENCEMENT_DATE = X_CHANGE_COMMENCEMENT_DATE)
318 OR ((tlinfo.CHANGE_COMMENCEMENT_DATE IS NULL) AND (X_CHANGE_COMMENCEMENT_DATE IS NULL))) THEN
319 pn_var_rent_pkg.lock_row_exception('CHANGE_COMMENCEMENT_DATE',tlinfo.CHANGE_COMMENCEMENT_DATE);
320 END IF;
321
322 IF NOT ((tlinfo.CHANGE_TERMINATION_DATE = X_CHANGE_TERMINATION_DATE)
323 OR ((tlinfo.CHANGE_TERMINATION_DATE IS NULL) AND (X_CHANGE_TERMINATION_DATE IS NULL))) THEN
324 pn_var_rent_pkg.lock_row_exception('CHANGE_TERMINATION_DATE',tlinfo.CHANGE_TERMINATION_DATE);
325 END IF;
326
327 IF NOT ((tlinfo.CHANGE_TYPE_LOOKUP_CODE = X_CHANGE_TYPE_LOOKUP_CODE)
328 OR ((tlinfo.CHANGE_TYPE_LOOKUP_CODE IS NULL) AND (X_CHANGE_TYPE_LOOKUP_CODE IS NULL))) THEN
329 pn_var_rent_pkg.lock_row_exception('CHANGE_TYPE_LOOKUP_CODE',tlinfo.CHANGE_TYPE_LOOKUP_CODE);
330 END IF;
331
332 IF NOT ((tlinfo.CHANGE_EXECUTION_DATE = X_CHANGE_EXECUTION_DATE)
333 OR ((tlinfo.CHANGE_EXECUTION_DATE IS NULL) AND (X_CHANGE_EXECUTION_DATE IS NULL))) THEN
334 pn_var_rent_pkg.lock_row_exception('CHANGE_EXECUTION_DATE',tlinfo.CHANGE_EXECUTION_DATE);
335 END IF;
336
337 IF NOT ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
338 OR ((tlinfo.ATTRIBUTE_CATEGORY IS NULL) AND (X_ATTRIBUTE_CATEGORY IS NULL))) THEN
339 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE_CATEGORY',tlinfo.ATTRIBUTE_CATEGORY);
340 END IF;
341
342 IF NOT ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
343 OR ((tlinfo.ATTRIBUTE1 IS NULL) AND (X_ATTRIBUTE1 IS NULL))) THEN
344 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE1',tlinfo.ATTRIBUTE1);
345 END IF;
346
347 IF NOT ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
348 OR ((tlinfo.ATTRIBUTE2 IS NULL) AND (X_ATTRIBUTE2 IS NULL))) THEN
349 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE2',tlinfo.ATTRIBUTE2);
350 END IF;
351
352 IF NOT ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
353 OR ((tlinfo.ATTRIBUTE3 IS NULL) AND (X_ATTRIBUTE3 IS NULL))) THEN
354 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE3',tlinfo.ATTRIBUTE3);
355 END IF;
356
357 IF NOT ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
358 OR ((tlinfo.ATTRIBUTE4 IS NULL) AND (X_ATTRIBUTE4 IS NULL))) THEN
359 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE4',tlinfo.ATTRIBUTE4);
360 END IF;
361
362 IF NOT ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
363 OR ((tlinfo.ATTRIBUTE5 IS NULL) AND (X_ATTRIBUTE5 IS NULL))) THEN
364 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE5',tlinfo.ATTRIBUTE5);
365 END IF;
366
367 IF NOT ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
368 OR ((tlinfo.ATTRIBUTE6 IS NULL) AND (X_ATTRIBUTE6 IS NULL))) THEN
369 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE6',tlinfo.ATTRIBUTE6);
370 END IF;
371
375 END IF;
372 IF NOT ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
373 OR ((tlinfo.ATTRIBUTE7 IS NULL) AND (X_ATTRIBUTE7 IS NULL))) THEN
374 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE7',tlinfo.ATTRIBUTE7);
376
377 IF NOT ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
378 OR ((tlinfo.ATTRIBUTE8 IS NULL) AND (X_ATTRIBUTE8 IS NULL))) THEN
379 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE8',tlinfo.ATTRIBUTE8);
380 END IF;
381
382 IF NOT ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
383 OR ((tlinfo.ATTRIBUTE9 IS NULL) AND (X_ATTRIBUTE9 IS NULL))) THEN
384 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE9',tlinfo.ATTRIBUTE9);
385 END IF;
386
387 IF NOT ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
388 OR ((tlinfo.ATTRIBUTE10 IS NULL) AND (X_ATTRIBUTE10 IS NULL))) THEN
389 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE10',tlinfo.ATTRIBUTE10);
390 END IF;
391
392 IF NOT ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
393 OR ((tlinfo.ATTRIBUTE11 IS NULL) AND (X_ATTRIBUTE11 IS NULL))) THEN
394 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE11',tlinfo.ATTRIBUTE11);
395 END IF;
396
397 IF NOT ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
398 OR ((tlinfo.ATTRIBUTE12 IS NULL) AND (X_ATTRIBUTE12 IS NULL))) THEN
399 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE12',tlinfo.ATTRIBUTE12);
400 END IF;
401
402 IF NOT ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
403 OR ((tlinfo.ATTRIBUTE13 IS NULL) AND (X_ATTRIBUTE13 IS NULL))) THEN
404 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE13',tlinfo.ATTRIBUTE13);
405 END IF;
406
407 IF NOT ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
408 OR ((tlinfo.ATTRIBUTE14 IS NULL) AND (X_ATTRIBUTE14 IS NULL))) THEN
409 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE14',tlinfo.ATTRIBUTE14);
410 END IF;
411
412 IF NOT ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
413 OR ((tlinfo.ATTRIBUTE15 IS NULL) AND (X_ATTRIBUTE15 IS NULL))) THEN
414 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE15',tlinfo.ATTRIBUTE15);
415 END IF;
416
417 IF NOT (tlinfo.LEASE_ID = X_LEASE_ID) THEN
418 pn_var_rent_pkg.lock_row_exception('LEASE_ID',tlinfo.LEASE_ID);
419 END IF;
420
421 IF NOT (tlinfo.LEASE_TRANSACTION_ID = X_LEASE_TRANSACTION_ID) THEN
422 pn_var_rent_pkg.lock_row_exception('LEASE_TRANSACTION_ID',tlinfo.LEASE_TRANSACTION_ID);
423 END IF;
424
425 IF NOT ((tlinfo.LEASE_CHANGE_NUMBER = X_LEASE_CHANGE_NUMBER)
426 OR ((tlinfo.LEASE_CHANGE_NUMBER IS NULL) AND (X_LEASE_CHANGE_NUMBER IS NULL))) THEN
427 pn_var_rent_pkg.lock_row_exception('LEASE_CHANGE_NUMBER',tlinfo.LEASE_CHANGE_NUMBER);
428 END IF;
429
430 IF NOT ((tlinfo.LEASE_CHANGE_NAME = X_LEASE_CHANGE_NAME)
431 OR ((tlinfo.LEASE_CHANGE_NAME IS NULL) AND (X_LEASE_CHANGE_NAME IS NULL))) THEN
432 pn_var_rent_pkg.lock_row_exception('LEASE_CHANGE_NAME',tlinfo.LEASE_CHANGE_NAME);
433 END IF;
434
435 IF NOT ((tlinfo.ABSTRACTED_BY_USER = X_ABSTRACTED_BY_USER)
436 OR ((tlinfo.ABSTRACTED_BY_USER IS NULL) AND (X_ABSTRACTED_BY_USER IS NULL))) THEN
437 pn_var_rent_pkg.lock_row_exception('ABSTRACTED_BY_USER',tlinfo.ABSTRACTED_BY_USER);
438 END IF;
439
440 pnp_debug_pkg.debug ('PN_LEASE_CHANGES_PKG.Lock_Row (-)');
441
442 END Lock_Row;
443
444 -------------------------------------------------------------------------------
445 -- PROCDURE : Update_Row
446 -- INVOKED FROM : Update_Row procedure
447 -- PURPOSE : updates the row
448 -- HISTORY :
449 -- 21-JUN-05 sdmahesh o Bug 4284035 - Replaced pn_lease_changes with _ALL table
450 -------------------------------------------------------------------------------
451 PROCEDURE Update_Row
452 (
453 X_LEASE_CHANGE_ID IN NUMBER,
454 X_RESPONSIBLE_USER IN NUMBER,
455 X_CHANGE_COMMENCEMENT_DATE IN DATE,
456 X_CHANGE_TERMINATION_DATE IN DATE,
457 X_CHANGE_TYPE_LOOKUP_CODE IN VARCHAR2,
458 X_CHANGE_EXECUTION_DATE IN DATE,
459 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
460 X_ATTRIBUTE1 IN VARCHAR2,
461 X_ATTRIBUTE2 IN VARCHAR2,
462 X_ATTRIBUTE3 IN VARCHAR2,
463 X_ATTRIBUTE4 IN VARCHAR2,
464 X_ATTRIBUTE5 IN VARCHAR2,
465 X_ATTRIBUTE6 IN VARCHAR2,
466 X_ATTRIBUTE7 IN VARCHAR2,
467 X_ATTRIBUTE8 IN VARCHAR2,
468 X_ATTRIBUTE9 IN VARCHAR2,
469 X_ATTRIBUTE10 IN VARCHAR2,
470 X_ATTRIBUTE11 IN VARCHAR2,
471 X_ATTRIBUTE12 IN VARCHAR2,
472 X_ATTRIBUTE13 IN VARCHAR2,
473 X_ATTRIBUTE14 IN VARCHAR2,
474 X_ATTRIBUTE15 IN VARCHAR2,
475 X_LEASE_ID IN NUMBER,
476 X_LEASE_TRANSACTION_ID IN NUMBER,
477 X_LEASE_CHANGE_NUMBER IN NUMBER,
478 X_LEASE_CHANGE_NAME IN VARCHAR2,
479 X_ABSTRACTED_BY_USER IN NUMBER,
480 X_LAST_UPDATE_DATE IN DATE,
481 X_LAST_UPDATED_BY IN NUMBER,
482 X_LAST_UPDATE_LOGIN IN NUMBER
483 )
484 IS
485 BEGIN
486 pnp_debug_pkg.debug ('PN_LEASE_CHANGES_PKG.Update_Row (+)');
487
488 UPDATE pn_lease_changes_all
492 CHANGE_TYPE_LOOKUP_CODE = X_CHANGE_TYPE_LOOKUP_CODE,
489 SET RESPONSIBLE_USER = X_RESPONSIBLE_USER,
490 CHANGE_COMMENCEMENT_DATE = X_CHANGE_COMMENCEMENT_DATE,
491 CHANGE_TERMINATION_DATE = X_CHANGE_TERMINATION_DATE,
493 CHANGE_EXECUTION_DATE = X_CHANGE_EXECUTION_DATE,
494 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
495 ATTRIBUTE1 = X_ATTRIBUTE1,
496 ATTRIBUTE2 = X_ATTRIBUTE2,
497 ATTRIBUTE3 = X_ATTRIBUTE3,
498 ATTRIBUTE4 = X_ATTRIBUTE4,
499 ATTRIBUTE5 = X_ATTRIBUTE5,
500 ATTRIBUTE6 = X_ATTRIBUTE6,
501 ATTRIBUTE7 = X_ATTRIBUTE7,
502 ATTRIBUTE8 = X_ATTRIBUTE8,
503 ATTRIBUTE9 = X_ATTRIBUTE9,
504 ATTRIBUTE10 = X_ATTRIBUTE10,
505 ATTRIBUTE11 = X_ATTRIBUTE11,
506 ATTRIBUTE12 = X_ATTRIBUTE12,
507 ATTRIBUTE13 = X_ATTRIBUTE13,
508 ATTRIBUTE14 = X_ATTRIBUTE14,
509 ATTRIBUTE15 = X_ATTRIBUTE15,
510 LEASE_ID = X_LEASE_ID,
511 LEASE_TRANSACTION_ID = X_LEASE_TRANSACTION_ID,
512 LEASE_CHANGE_NUMBER = X_LEASE_CHANGE_NUMBER,
513 LEASE_CHANGE_NAME = X_LEASE_CHANGE_NAME,
514 ABSTRACTED_BY_USER = X_ABSTRACTED_BY_USER,
515 LEASE_CHANGE_ID = X_LEASE_CHANGE_ID,
516 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
517 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
518 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
519 WHERE LEASE_CHANGE_ID = X_LEASE_CHANGE_ID;
520
521 IF (SQL%NOTFOUND) THEN
522 RAISE NO_DATA_FOUND;
523 END IF;
524
525 pnp_debug_pkg.debug ('PN_LEASE_CHANGES_PKG.Update_Row (-)');
526 END Update_Row;
527
528 -------------------------------------------------------------------------------
529 -- PROCDURE : Delete_Row
530 -- INVOKED FROM : Delete_Row procedure
531 -- PURPOSE : deletes the row
532 -- HISTORY :
533 -- 21-JUN-05 sdmahesh o Bug 4284035 - Replaced pn_lease_changes with _ALL table
534 -------------------------------------------------------------------------------
535 PROCEDURE Delete_Row
536 (
537 X_LEASE_CHANGE_ID IN NUMBER
538 )
539 IS
540 BEGIN
541 pnp_debug_pkg.debug ('PN_LEASE_CHANGES_PKG.Delete_Row (+)');
542 DELETE FROM pn_lease_changes_all
543 WHERE lease_change_id = x_lease_change_id;
544
545 IF (SQL%NOTFOUND) THEN
546 RAISE NO_DATA_FOUND;
547 END IF;
548
549 pnp_debug_pkg.debug ('PN_LEASE_CHANGES_PKG.Delete_Row (-)');
550 END Delete_Row;
551
552 -------------------------------------------------------------------------------
553 -- PROCDURE : Delete_Row_Transactions
554 -- INVOKED FROM :
555 -- PURPOSE : deletes the row
556 -- HISTORY :
557 -- 21-JUN-05 sdmahesh o Bug 4284035 - Replaced pn_lease_transactions with
558 -- _ALL table
559 -------------------------------------------------------------------------------
560 PROCEDURE Delete_Row_Transactions
561 (
562 X_LEASE_TRANSACTION_ID IN NUMBER
563 )
564 IS
565 BEGIN
566 pnp_debug_pkg.debug ('PN_LEASE_CHANGES_PKG.Delete_Row_Transactions (+)');
567 DELETE FROM pn_lease_transactions_all
568 WHERE lease_transaction_id = x_lease_transaction_id;
569
570 IF (SQL%NOTFOUND) THEN
571 RAISE NO_DATA_FOUND;
572 END IF;
573
574 pnp_debug_pkg.debug ('PN_LEASE_CHANGES_PKG.Delete_Row_Transactions (-)');
575 END Delete_Row_Transactions;
576
577 -------------------------------------------------------------------------------
578 -- PROCDURE : CHECK_UNIQUE_CHANGE_NUMBER
579 -- INVOKED FROM : insert_row and update_row
580 -- PURPOSE : checks unique change number
581 -- HISTORY :
582 -- 21-JUN-05 sdmahesh o Bug 4284035 - Replaced pn_lease_changes with
583 -- _ALL table
584 -------------------------------------------------------------------------------
585 PROCEDURE CHECK_UNIQUE_CHANGE_NUMBER
586 (
587 x_return_status IN OUT NOCOPY VARCHAR2
588 ,X_LEASE_ID IN NUMBER
589 ,X_CHANGE_TYPE_LOOKUP_CODE IN VARCHAR2
590 ,X_LEASE_CHANGE_NUMBER IN VARCHAR2
591 )
592 IS
593 l_leaseName VARCHAR2 (50) := NULL;
594 l_dummy NUMBER := NULL;
595 BEGIN
596 pnp_debug_pkg.debug ('PN_LEASE_CHANGES_PKG.CHECK_UNIQUE_CHANGE_NUMBER (+)');
597
598 IF (X_CHANGE_TYPE_LOOKUP_CODE = 'ABSTRACT') THEN
599 BEGIN
600 SELECT 1
601 INTO l_dummy
602 FROM DUAL
603 WHERE NOT EXISTS (SELECT 1
604 FROM pn_lease_changes_all plc
605 WHERE plc.change_type_lookup_code = x_change_type_lookup_code
606 AND plc.lease_id = X_LEASE_ID
607 );
608
609 END;
610
614 INTO l_leaseName
611 ELSIF (X_CHANGE_TYPE_LOOKUP_CODE IN ('AMEND', 'EDIT')) THEN
612 BEGIN
613 SELECT name
615 FROM pn_leases_all
616 WHERE lease_id = X_LEASE_ID;
617
618 SELECT 1
619 INTO l_dummy
620 FROM DUAL
621 WHERE NOT EXISTS (SELECT 1
622 FROM pn_lease_changes_all plc
623 WHERE plc.change_type_lookup_code = X_CHANGE_TYPE_LOOKUP_CODE
624 AND plc.lease_change_number = X_LEASE_CHANGE_NUMBER
625 AND plc.lease_id = X_LEASE_ID
626 );
627
628 EXCEPTION
629 WHEN NO_DATA_FOUND THEN
630 IF (X_CHANGE_TYPE_LOOKUP_CODE = 'AMEND') THEN
631 fnd_message.set_name('PN', 'PN_DUP_LEASE_CHANGE_NUMBER');
632 fnd_message.set_token('LEASE_CHANGE_NUMBER', x_lease_change_number);
633 fnd_message.set_token('LEASE_NAME', l_leaseName);
634 ELSE
635 fnd_message.set_name ('PN', 'PN_DUP_EDIT_NUMBER');
636 fnd_message.set_token('EDIT_NUMBER', x_lease_change_number);
637 fnd_message.set_token('LEASE_NAME', l_leaseName);
638 END IF;
639 x_return_status := 'E';
640 END;
641 ELSE
642 RAISE NO_DATA_FOUND;
643 END IF;
644
645 pnp_debug_pkg.debug ('PN_LEASE_CHANGES_PKG.CHECK_UNIQUE_CHANGE_NUMBER (-)');
646 END CHECK_UNIQUE_CHANGE_NUMBER;
647
648 END PN_LEASE_CHANGES_PKG;
[Home] [Help]