[Home] [Help]
PACKAGE BODY: APPS.PN_LEASES_PKG
Source
1 PACKAGE BODY pn_leases_pkg AS
2 -- $Header: PNTLEASB.pls 120.2 2005/12/01 07:37:07 appldev ship $
3
4 -------------------------------------------------------------------------------
5 -- PROCDURE : INSERT_ROW
6 -- INVOKED FROM : insert_row procedure
7 -- PURPOSE : inserts the row
8 -- HISTORY :
9 -- 19-MAR-02 lkatputu o Added Send_Entries into the table handler
10 -- as per the 'DO NOT SEND' enhancement requirement.
11 -- 05-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_leases with
12 -- _ALL table.
13 -- 01-DEC-05 kiran o passed org_id in pn_leases_pkg.check_unique_lease_number
14 -------------------------------------------------------------------------------
15 PROCEDURE Insert_Row
16 (
17 X_ROWID IN OUT NOCOPY VARCHAR2,
18 X_LEASE_ID IN OUT NOCOPY NUMBER,
19 X_LEASE_CHANGE_ID IN OUT NOCOPY NUMBER,
20 X_LEASE_DETAIL_ID IN OUT NOCOPY NUMBER,
21 X_NAME IN VARCHAR2,
22 X_LEASE_NUM IN OUT NOCOPY VARCHAR2,
23 X_PARENT_LEASE_ID IN NUMBER,
24 X_LEASE_TYPE_CODE IN VARCHAR2,
25 X_LEASE_CLASS_CODE IN VARCHAR2,
26 X_PAYMENT_TERM_PRORATION_RULE IN NUMBER,
27 X_ABSTRACTED_BY_USER IN NUMBER,
28 X_STATUS IN VARCHAR2,
29 X_LEASE_STATUS IN VARCHAR2,
30 X_CREATION_DATE IN DATE,
31 X_CREATED_BY IN NUMBER,
32 X_LAST_UPDATE_DATE IN DATE,
33 X_LAST_UPDATED_BY IN NUMBER,
34 X_LAST_UPDATE_LOGIN IN NUMBER,
35 X_RESPONSIBLE_USER IN NUMBER,
36 X_EXPENSE_ACCOUNT_ID IN NUMBER,
37 X_ACCRUAL_ACCOUNT_ID IN NUMBER,
38 X_RECEIVABLE_ACCOUNT_ID IN NUMBER,
39 X_TERM_TEMPLATE_ID IN NUMBER ,
40 X_LEASE_COMMENCEMENT_DATE IN DATE,
41 X_LEASE_TERMINATION_DATE IN DATE,
42 X_LEASE_EXECUTION_DATE IN DATE,
43 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
44 X_ATTRIBUTE1 IN VARCHAR2,
45 X_ATTRIBUTE2 IN VARCHAR2,
46 X_ATTRIBUTE3 IN VARCHAR2,
47 X_ATTRIBUTE4 IN VARCHAR2,
48 X_ATTRIBUTE5 IN VARCHAR2,
49 X_ATTRIBUTE6 IN VARCHAR2,
50 X_ATTRIBUTE7 IN VARCHAR2,
51 X_ATTRIBUTE8 IN VARCHAR2,
52 X_ATTRIBUTE9 IN VARCHAR2,
53 X_ATTRIBUTE10 IN VARCHAR2,
54 X_ATTRIBUTE11 IN VARCHAR2,
55 X_ATTRIBUTE12 IN VARCHAR2,
56 X_ATTRIBUTE13 IN VARCHAR2,
57 X_ATTRIBUTE14 IN VARCHAR2,
58 X_ATTRIBUTE15 IN VARCHAR2,
59 x_org_id IN NUMBER,
60 x_location_id IN NUMBER,
61 x_customer_id IN NUMBER,
62 x_grouping_rule_id IN NUMBER
63 )
64 IS
65
66 CURSOR c IS
67 SELECT ROWID
68 FROM pn_leases_all
69 WHERE lease_id = x_lease_id;
70
71 l_return_status VARCHAR2(30) := NULL;
72 l_rowId VARCHAR2(18) := NULL;
73 l_leaseDetailId NUMBER := NULL;
74 l_leaseChangeNumber NUMBER := NULL;
75
76 BEGIN
77 pnp_debug_pkg.debug ('PN_LEASES_PKG.Insert_Row (+)');
78
79 -- Check IF lease NUMBER IS unique
80 l_return_status := NULL;
81 pn_leases_pkg.check_unique_lease_number(l_return_status,
82 x_lease_id,
83 x_lease_num,
84 x_org_id);
85
86 IF (l_return_status IS NOT NULL) THEN
87 APP_EXCEPTION.Raise_Exception;
88 END IF;
89
90 INSERT INTO pn_leases_all
91 (
92 LEASE_ID,
93 LAST_UPDATE_DATE,
94 LAST_UPDATED_BY,
95 CREATION_DATE,
96 CREATED_BY,
97 LAST_UPDATE_LOGIN,
98 NAME,
99 LEASE_NUM,
100 PARENT_LEASE_ID,
101 LEASE_TYPE_CODE,
102 LEASE_CLASS_CODE,
103 PAYMENT_TERM_PRORATION_RULE,
104 ABSTRACTED_BY_USER,
105 STATUS,
106 LEASE_STATUS,
107 org_id,
108 location_id,
109 customer_id
110 )
111 VALUES
112 (
113 NVL(X_LEASE_ID,pn_leases_s.NEXTVAL),
114 X_LAST_UPDATE_DATE,
115 X_LAST_UPDATED_BY,
116 X_CREATION_DATE,
117 X_CREATED_BY,
118 X_LAST_UPDATE_LOGIN,
119 X_NAME,
120 NVL(X_LEASE_NUM,pn_leases_s.CURRVAL),
121 X_PARENT_LEASE_ID,
122 X_LEASE_TYPE_CODE,
123 X_LEASE_CLASS_CODE,
124 X_PAYMENT_TERM_PRORATION_RULE,
125 X_ABSTRACTED_BY_USER,
126 X_STATUS,
127 X_LEASE_STATUS,
128 x_org_id,
129 x_location_id,
130 x_customer_id
131 )
132 RETURNING lease_id, lease_num INTO x_lease_id, x_lease_num;
133
134 OPEN c;
135 FETCH C INTO x_rowid;
136 IF (c%NOTFOUND) THEN
137 CLOSE c;
138 RAISE NO_DATA_FOUND;
139 END IF;
140 CLOSE c;
141
142 ------------------------------------------------------
143 -- We need to insert a record in PN_LEASE_CHANGES
144 ------------------------------------------------------
145 l_rowId := NULL;
146 pn_lease_changes_pkg.Insert_Row
147 (
148 X_ROWID => l_rowId
149 ,X_LEASE_CHANGE_ID => X_LEASE_CHANGE_ID
150 ,X_LEASE_ID => X_LEASE_ID
151 ,X_LEASE_CHANGE_NUMBER => l_leaseChangeNumber
152 ,X_LEASE_CHANGE_NAME => NULL
153 ,X_RESPONSIBLE_USER => NULL
154 ,X_CHANGE_COMMENCEMENT_DATE => NULL
155 ,X_CHANGE_TERMINATION_DATE => NULL
156 ,X_CHANGE_TYPE_LOOKUP_CODE => 'ABSTRACT'
157 ,X_CHANGE_EXECUTION_DATE => NULL
158 ,X_ATTRIBUTE_CATEGORY => NULL
159 ,X_ATTRIBUTE1 => NULL
160 ,X_ATTRIBUTE2 => NULL
161 ,X_ATTRIBUTE3 => NULL
162 ,X_ATTRIBUTE4 => NULL
163 ,X_ATTRIBUTE5 => NULL
164 ,X_ATTRIBUTE6 => NULL
165 ,X_ATTRIBUTE7 => NULL
166 ,X_ATTRIBUTE8 => NULL
167 ,X_ATTRIBUTE9 => NULL
168 ,X_ATTRIBUTE10 => NULL
169 ,X_ATTRIBUTE11 => NULL
170 ,X_ATTRIBUTE12 => NULL
171 ,X_ATTRIBUTE13 => NULL
172 ,X_ATTRIBUTE14 => NULL
173 ,X_ATTRIBUTE15 => NULL
174 ,X_ABSTRACTED_BY_USER => NULL
175 ,X_CREATION_DATE => X_CREATION_DATE
176 ,X_CREATED_BY => X_CREATED_BY
177 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
178 ,X_LAST_UPDATED_BY => X_LAST_UPDATED_BY
179 ,X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
180 ,x_org_id => x_org_id
181 );
182
183 ------------------------------------------------------
184 -- We need to insert a record in PN_LEASE_DETAILS
185 ------------------------------------------------------
186 l_rowId := NULL;
187 pn_lease_details_pkg.Insert_Row
188 (
189 X_ROWID => l_rowId
190 ,X_LEASE_DETAIL_ID => X_LEASE_DETAIL_ID
191 ,X_LEASE_CHANGE_ID => X_LEASE_CHANGE_ID
192 ,X_LEASE_ID => X_LEASE_ID
193 ,X_RESPONSIBLE_USER => X_RESPONSIBLE_USER
194 ,X_EXPENSE_ACCOUNT_ID => X_EXPENSE_ACCOUNT_ID
195 ,X_LEASE_COMMENCEMENT_DATE => X_LEASE_COMMENCEMENT_DATE
196 ,X_LEASE_TERMINATION_DATE => X_LEASE_TERMINATION_DATE
197 ,X_LEASE_EXECUTION_DATE => X_LEASE_EXECUTION_DATE
198 ,X_CREATION_DATE => X_CREATION_DATE
199 ,X_CREATED_BY => X_CREATED_BY
200 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
201 ,X_LAST_UPDATED_BY => X_LAST_UPDATED_BY
202 ,X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
203 ,X_ACCRUAL_ACCOUNT_ID => X_ACCRUAL_ACCOUNT_ID
204 ,X_RECEIVABLE_ACCOUNT_ID => X_RECEIVABLE_ACCOUNT_ID
205 ,X_TERM_TEMPLATE_ID => X_TERM_TEMPLATE_ID
206 ,X_GROUPING_RULE_ID => X_GROUPING_RULE_ID
207 ,X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY
208 ,x_ATTRIBUTE1 => x_ATTRIBUTE1
209 ,x_ATTRIBUTE2 => x_ATTRIBUTE2
210 ,x_ATTRIBUTE3 => x_ATTRIBUTE3
211 ,x_ATTRIBUTE4 => x_ATTRIBUTE4
212 ,x_ATTRIBUTE5 => x_ATTRIBUTE5
213 ,x_ATTRIBUTE6 => x_ATTRIBUTE6
214 ,x_ATTRIBUTE7 => x_ATTRIBUTE7
215 ,x_ATTRIBUTE8 => x_ATTRIBUTE8
216 ,x_ATTRIBUTE9 => x_ATTRIBUTE9
217 ,x_ATTRIBUTE10 => x_ATTRIBUTE10
218 ,x_ATTRIBUTE11 => x_ATTRIBUTE11
219 ,x_ATTRIBUTE12 => x_ATTRIBUTE12
220 ,x_ATTRIBUTE13 => x_ATTRIBUTE13
221 ,x_ATTRIBUTE14 => x_ATTRIBUTE14
222 ,x_ATTRIBUTE15 => x_ATTRIBUTE15
223 ,x_org_id => x_org_id
224 );
225
226 pnp_debug_pkg.debug ('PN_LEASES_PKG.Insert_Row (-)');
227
228 END Insert_Row;
229
230
231 -------------------------------------------------------------------------------
232 -- PROCDURE : Lock_Row
233 -- INVOKED FROM : Lock_Row procedure
234 -- PURPOSE : locks the row
235 -- HISTORY :
236 -- 19-MAR-02 lkatputu o Added Send_Entries into the table handler
237 -- as per the 'DO NOT SEND' enhancement requirement.
238 -- 05-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_leases with
239 -- _ALL table.
240 -------------------------------------------------------------------------------
241 PROCEDURE Lock_Row
242 (
243 X_LEASE_ID IN NUMBER,
244 X_LEASE_DETAIL_ID IN NUMBER,
245 X_LEASE_CHANGE_ID IN NUMBER,
246 X_NAME IN VARCHAR2,
247 X_LEASE_NUM IN VARCHAR2,
248 X_PARENT_LEASE_ID IN NUMBER,
249 X_LEASE_TYPE_CODE IN VARCHAR2,
250 X_LEASE_CLASS_CODE IN VARCHAR2,
251 X_PAYMENT_TERM_PRORATION_RULE IN NUMBER,
252 X_ABSTRACTED_BY_USER IN NUMBER,
253 X_STATUS IN VARCHAR2,
254 X_LEASE_STATUS IN VARCHAR2,
255 X_RESPONSIBLE_USER IN NUMBER,
256 X_EXPENSE_ACCOUNT_ID IN NUMBER,
257 X_ACCRUAL_ACCOUNT_ID IN NUMBER,
258 X_RECEIVABLE_ACCOUNT_ID IN NUMBER,
259 X_TERM_TEMPLATE_ID IN NUMBER,
260 X_LEASE_COMMENCEMENT_DATE IN DATE,
261 X_LEASE_TERMINATION_DATE IN DATE,
262 X_LEASE_EXECUTION_DATE IN DATE,
263 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
264 X_ATTRIBUTE1 IN VARCHAR2,
265 X_ATTRIBUTE2 IN VARCHAR2,
266 X_ATTRIBUTE3 IN VARCHAR2,
267 X_ATTRIBUTE4 IN VARCHAR2,
268 X_ATTRIBUTE5 IN VARCHAR2,
269 X_ATTRIBUTE6 IN VARCHAR2,
270 X_ATTRIBUTE7 IN VARCHAR2,
271 X_ATTRIBUTE8 IN VARCHAR2,
272 X_ATTRIBUTE9 IN VARCHAR2,
273 X_ATTRIBUTE10 IN VARCHAR2,
274 X_ATTRIBUTE11 IN VARCHAR2,
275 X_ATTRIBUTE12 IN VARCHAR2,
276 X_ATTRIBUTE13 IN VARCHAR2,
277 X_ATTRIBUTE14 IN VARCHAR2,
278 X_ATTRIBUTE15 IN VARCHAR2,
279 x_location_id IN NUMBER,
280 x_customer_id IN NUMBER,
281 x_grouping_rule_id IN NUMBER
282 )
283 IS
284 CURSOR c1 IS
285 SELECT *
286 FROM pn_leases_all --sdm_MOAC
287 WHERE lease_id = x_lease_id
288 FOR UPDATE OF lease_id NOWAIT;
289
290 tlinfo c1%ROWTYPE;
291
292 BEGIN
293 pnp_debug_pkg.debug ('PN_LEASES_PKG.Lock_Row (+)');
294 OPEN c1;
295 FETCH c1 INTO tlinfo;
296 IF (c1%NOTFOUND) THEN
297 CLOSE c1;
298 RETURN;
299 END IF;
300 CLOSE c1;
301
302 IF NOT (tlinfo.LEASE_ID = X_LEASE_ID) THEN
303 pn_var_rent_pkg.lock_row_exception('LEASE_ID',tlinfo.LEASE_ID);
304 END IF;
305
306 IF NOT (tlinfo.NAME = X_NAME) THEN
307 pn_var_rent_pkg.lock_row_exception('NAME',tlinfo.NAME);
308 END IF;
309
310 IF NOT (tlinfo.LEASE_NUM = X_LEASE_NUM) THEN
311 pn_var_rent_pkg.lock_row_exception('LEASE_NUM',tlinfo.LEASE_NUM);
312 END IF;
313
314 IF NOT ((tlinfo.PARENT_LEASE_ID = X_PARENT_LEASE_ID)
315 OR ((tlinfo.PARENT_LEASE_ID IS NULL) AND (X_PARENT_LEASE_ID IS NULL))) THEN
316 pn_var_rent_pkg.lock_row_exception('PARENT_LEASE_ID',tlinfo.PARENT_LEASE_ID);
317 END IF;
318
319 IF NOT ((tlinfo.LEASE_TYPE_CODE = X_LEASE_TYPE_CODE)
320 OR ((tlinfo.LEASE_TYPE_CODE IS NULL) AND (X_LEASE_TYPE_CODE IS NULL))) THEN
321 pn_var_rent_pkg.lock_row_exception('LEASE_TYPE_CODE',tlinfo.LEASE_TYPE_CODE);
322 END IF;
323
324 IF NOT ((tlinfo.LEASE_CLASS_CODE = X_LEASE_CLASS_CODE)
325 OR ((tlinfo.LEASE_CLASS_CODE IS NULL) AND (X_LEASE_CLASS_CODE IS NULL))) THEN
326 pn_var_rent_pkg.lock_row_exception('LEASE_CLASS_CODE',tlinfo.LEASE_CLASS_CODE);
327 END IF;
328
329 IF NOT ((tlinfo.LEASE_STATUS = X_LEASE_STATUS)
330 OR ((tlinfo.LEASE_STATUS IS NULL) AND (X_LEASE_STATUS IS NULL))) THEN
331 pn_var_rent_pkg.lock_row_exception('LEASE_STATUS',tlinfo.LEASE_STATUS);
332 END IF;
333
334 IF NOT ((tlinfo.STATUS = X_STATUS)
335 OR ((tlinfo.STATUS IS NULL) AND (X_STATUS IS NULL))) THEN
336 pn_var_rent_pkg.lock_row_exception('STATUS',tlinfo.STATUS);
337 END IF;
338
339 IF NOT ((tlinfo.PAYMENT_TERM_PRORATION_RULE = X_PAYMENT_TERM_PRORATION_RULE)
340 OR ((tlinfo.PAYMENT_TERM_PRORATION_RULE IS NULL) AND
341 (X_PAYMENT_TERM_PRORATION_RULE IS NULL))) THEN
342 pn_var_rent_pkg.lock_row_exception('PAYMENT_TERM_PRORATION_RULE',tlinfo.PAYMENT_TERM_PRORATION_RULE);
343 END IF;
344
345 IF NOT ((tlinfo.ABSTRACTED_BY_USER = X_ABSTRACTED_BY_USER)
346 OR ((tlinfo.ABSTRACTED_BY_USER IS NULL) AND (X_ABSTRACTED_BY_USER IS NULL))) THEN
347 pn_var_rent_pkg.lock_row_exception('ABSTRACTED_BY_USER',tlinfo.ABSTRACTED_BY_USER);
348 END IF;
349
350 IF NOT ((tlinfo.location_id = x_location_id)
351 OR ((tlinfo.location_id IS NULL) AND (x_location_id IS NULL))) THEN
352 pn_var_rent_pkg.lock_row_exception('LOCATION_ID',tlinfo.location_id);
353 END IF;
354
355 IF NOT ((tlinfo.customer_id = x_customer_id)
356 OR ((tlinfo.customer_id IS NULL) AND (x_customer_id IS NULL))) THEN
357 pn_var_rent_pkg.lock_row_exception('CUSTOMER_ID',tlinfo.customer_id);
358 END IF;
359
360 ------------------------------------------------------
361 -- We need to lock records in pn_lease_details
362 ------------------------------------------------------
363
364 pn_lease_details_pkg.Lock_Row
365 (
366 X_LEASE_DETAIL_ID =>X_LEASE_DETAIL_ID
367 ,X_LEASE_CHANGE_ID =>X_LEASE_CHANGE_ID
368 ,X_LEASE_ID =>X_LEASE_ID
369 ,X_RESPONSIBLE_USER =>X_RESPONSIBLE_USER
370 ,X_EXPENSE_ACCOUNT_ID =>X_EXPENSE_ACCOUNT_ID
374 ,X_ACCRUAL_ACCOUNT_ID =>X_ACCRUAL_ACCOUNT_ID
371 ,X_LEASE_COMMENCEMENT_DATE =>X_LEASE_COMMENCEMENT_DATE
372 ,X_LEASE_TERMINATION_DATE =>X_LEASE_TERMINATION_DATE
373 ,X_LEASE_EXECUTION_DATE =>X_LEASE_EXECUTION_DATE
375 ,X_RECEIVABLE_ACCOUNT_ID =>X_RECEIVABLE_ACCOUNT_ID
376 ,X_TERM_TEMPLATE_ID =>X_TERM_TEMPLATE_ID
377 ,X_GROUPING_RULE_ID =>X_GROUPING_RULE_ID
378 ,X_ATTRIBUTE_CATEGORY =>X_ATTRIBUTE_CATEGORY
379 ,x_ATTRIBUTE1 =>X_ATTRIBUTE1
380 ,x_ATTRIBUTE2 =>X_ATTRIBUTE2
381 ,x_ATTRIBUTE3 =>X_ATTRIBUTE3
382 ,x_ATTRIBUTE4 =>X_ATTRIBUTE4
383 ,x_ATTRIBUTE5 =>X_ATTRIBUTE5
384 ,x_ATTRIBUTE6 =>X_ATTRIBUTE6
385 ,x_ATTRIBUTE7 =>X_ATTRIBUTE7
386 ,x_ATTRIBUTE8 =>X_ATTRIBUTE8
387 ,x_ATTRIBUTE9 =>X_ATTRIBUTE9
388 ,x_ATTRIBUTE10 =>X_ATTRIBUTE10
389 ,x_ATTRIBUTE11 =>X_ATTRIBUTE11
390 ,x_ATTRIBUTE12 =>X_ATTRIBUTE12
391 ,x_ATTRIBUTE13 =>X_ATTRIBUTE13
392 ,x_ATTRIBUTE14 =>X_ATTRIBUTE14
393 ,x_ATTRIBUTE15 =>X_ATTRIBUTE15
394 );
395
396 -- NOTE: We will not check for the lease PN_LEASE_CHANGES table
397
398 pnp_debug_pkg.debug ('PN_LEASES_PKG.Lock_Row (-)');
399
400 END Lock_Row;
401
402
403 -------------------------------------------------------------------------------
404 -- PROCDURE : INSERT_ROW
405 -- INVOKED FROM : insert_row procedure
406 -- PURPOSE : inserts the row
407 -- HISTORY :
408 -- 19-MAR-02 lkatputu o Added Send_Entries into the table handler
409 -- as per the 'DO NOT SEND' enhancement requirement.
410 -- 25-OCT-02 STRIPATH o Modified Update_Row for Lease Number/Name and MTM.
411 -- Now first call pn_lease_details_pkg.Update_row and then
412 -- Update pn_leases_all, for history creation of 3 columns
413 -- of pn_leases_all (name, lease_num, lease_status) in
414 -- table pn_lease_details_history.
415 -- 02-FEB-05 VIVESHAR o Added lease extension end date as input parameter in
416 -- pn_leases_pkg.Update_row and pn_lease_details_pkg.
417 -- Update_Row. Fix for bug# 4142423
418 -- 05-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_leases with
419 -- _ALL table.
420 -- 01-DEC-05 kiran o passed org_id in pn_leases_pkg.check_unique_lease_number
421 -------------------------------------------------------------------------------
422 PROCEDURE Update_Row
423 (
424 X_LEASE_ID IN NUMBER,
425 X_LEASE_DETAIL_ID IN NUMBER,
426 X_LEASE_CHANGE_ID IN NUMBER,
427 X_NAME IN VARCHAR2,
428 X_LEASE_NUM IN VARCHAR2,
429 X_PARENT_LEASE_ID IN NUMBER,
430 X_LEASE_TYPE_CODE IN VARCHAR2,
431 X_LEASE_CLASS_CODE IN VARCHAR2,
432 X_PAYMENT_TERM_PRORATION_RULE IN NUMBER,
433 X_ABSTRACTED_BY_USER IN NUMBER,
434 X_STATUS IN VARCHAR2,
435 X_LEASE_STATUS IN VARCHAR2,
436 X_LAST_UPDATE_DATE IN DATE,
437 X_LAST_UPDATED_BY IN NUMBER,
438 X_LAST_UPDATE_LOGIN IN NUMBER,
439 X_RESPONSIBLE_USER IN NUMBER,
440 X_EXPENSE_ACCOUNT_ID IN NUMBER,
441 X_ACCRUAL_ACCOUNT_ID IN NUMBER,
442 X_RECEIVABLE_ACCOUNT_ID IN NUMBER,
443 X_TERM_TEMPLATE_ID IN NUMBER,
444 X_LEASE_COMMENCEMENT_DATE IN DATE,
445 X_LEASE_TERMINATION_DATE IN DATE,
446 X_LEASE_EXECUTION_DATE IN DATE,
447 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
448 X_ATTRIBUTE1 IN VARCHAR2,
449 X_ATTRIBUTE2 IN VARCHAR2,
450 X_ATTRIBUTE3 IN VARCHAR2,
451 X_ATTRIBUTE4 IN VARCHAR2,
452 X_ATTRIBUTE5 IN VARCHAR2,
453 X_ATTRIBUTE6 IN VARCHAR2,
454 X_ATTRIBUTE7 IN VARCHAR2,
455 X_ATTRIBUTE8 IN VARCHAR2,
456 X_ATTRIBUTE9 IN VARCHAR2,
457 X_ATTRIBUTE10 IN VARCHAR2,
458 X_ATTRIBUTE11 IN VARCHAR2,
459 X_ATTRIBUTE12 IN VARCHAR2,
460 X_ATTRIBUTE13 IN VARCHAR2,
461 X_ATTRIBUTE14 IN VARCHAR2,
462 X_ATTRIBUTE15 IN VARCHAR2,
463 x_location_id IN NUMBER,
464 x_customer_id IN NUMBER,
465 x_grouping_rule_id IN NUMBER,
466 x_lease_extension_end_date IN DATE
467 )
468 IS
469 l_return_status VARCHAR2(30) := NULL;
470
471 CURSOR org_cur IS
472 SELECT org_id FROM pn_leases_all WHERE lease_id = x_lease_id;
473
474 l_org_id NUMBER;
475
476 BEGIN
477 pnp_debug_pkg.debug ('PN_LEASES_PKG.Update_Row (+)');
478
479 /* Check IF lease NUMBER IS unique */
480 FOR rec IN org_cur LOOP
484 l_return_status := NULL;
481 l_org_id := rec.org_id;
482 END LOOP;
483
485 pn_leases_pkg.check_unique_lease_number
486 (
487 l_return_status,
488 x_lease_id,
489 x_lease_num,
490 l_org_id
491 );
492 IF (l_return_status IS NOT NULL) THEN
493 APP_EXCEPTION.Raise_Exception;
494 END IF;
495
496 IF (SQL%NOTFOUND) THEN
497 RAISE NO_DATA_FOUND;
498 END IF;
499
500 ------------------------------------------------------
501 -- We need to update records in pn_lease_details
502 ------------------------------------------------------
503 pn_lease_details_pkg.Update_Row
504 (
505 X_LEASE_DETAIL_ID => X_LEASE_DETAIL_ID
506 ,X_LEASE_CHANGE_ID => X_LEASE_CHANGE_ID
507 ,X_LEASE_ID => X_LEASE_ID
508 ,X_RESPONSIBLE_USER => X_RESPONSIBLE_USER
509 ,X_EXPENSE_ACCOUNT_ID => X_EXPENSE_ACCOUNT_ID
510 ,X_LEASE_COMMENCEMENT_DATE => X_LEASE_COMMENCEMENT_DATE
511 ,X_LEASE_TERMINATION_DATE => X_LEASE_TERMINATION_DATE
512 ,X_LEASE_EXECUTION_DATE => X_LEASE_EXECUTION_DATE
513 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
514 ,X_LAST_UPDATED_BY => X_LAST_UPDATED_BY
515 ,X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
516 ,X_ACCRUAL_ACCOUNT_ID => X_ACCRUAL_ACCOUNT_ID
517 ,X_RECEIVABLE_ACCOUNT_ID => X_RECEIVABLE_ACCOUNT_ID
518 ,X_TERM_TEMPLATE_ID => X_TERM_TEMPLATE_ID
519 ,X_GROUPING_RULE_ID => X_GROUPING_RULE_ID
520 ,X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY
521 ,X_ATTRIBUTE1 => X_ATTRIBUTE1
522 ,X_ATTRIBUTE2 => X_ATTRIBUTE2
523 ,X_ATTRIBUTE3 => X_ATTRIBUTE3
524 ,X_ATTRIBUTE4 => X_ATTRIBUTE4
525 ,X_ATTRIBUTE5 => X_ATTRIBUTE5
526 ,X_ATTRIBUTE6 => X_ATTRIBUTE6
527 ,X_ATTRIBUTE7 => X_ATTRIBUTE7
528 ,X_ATTRIBUTE8 => X_ATTRIBUTE8
529 ,X_ATTRIBUTE9 => X_ATTRIBUTE9
530 ,X_ATTRIBUTE10 => X_ATTRIBUTE10
531 ,X_ATTRIBUTE11 => X_ATTRIBUTE11
532 ,X_ATTRIBUTE12 => X_ATTRIBUTE12
533 ,X_ATTRIBUTE13 => X_ATTRIBUTE13
534 ,X_ATTRIBUTE14 => X_ATTRIBUTE14
535 ,X_ATTRIBUTE15 => X_ATTRIBUTE15
536 ,x_lease_extension_end_date => x_lease_extension_end_date
537 );
538
539 UPDATE pn_leases_all
540 SET NAME = X_NAME,
541 LEASE_NUM = X_LEASE_NUM,
542 PARENT_LEASE_ID = X_PARENT_LEASE_ID,
543 LEASE_TYPE_CODE = X_LEASE_TYPE_CODE,
544 LEASE_CLASS_CODE = X_LEASE_CLASS_CODE,
545 PAYMENT_TERM_PRORATION_RULE = X_PAYMENT_TERM_PRORATION_RULE,
546 ABSTRACTED_BY_USER = X_ABSTRACTED_BY_USER,
547 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
548 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
549 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
550 STATUS = X_STATUS,
551 LEASE_STATUS = X_LEASE_STATUS,
552 location_id = x_location_id,
553 customer_id = x_customer_id
554 WHERE LEASE_ID = X_LEASE_ID;
555
556 pnp_debug_pkg.debug ('PN_LEASES_PKG.Update_Row (-)');
557
558 END Update_Row;
559
560
561 -------------------------------------------------------------------------------
562 -- PROCDURE : Delete_Row
563 -- INVOKED FROM : Delete_Row procedure
564 -- PURPOSE : Deletes the row
565 -- HISTORY :
566 -- 10-SEP-02 dthota o Replaced the predicate in PN_LEASES_PKG.delete_row
567 -- SELECT clause for performance issues
568 -- Fix for bug # 2558646
569 -- 05-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_leases, pn_lease_details,
570 -- pn_lease_transactions, pn_lease_changes with
571 -- _ALL table.
572 -------------------------------------------------------------------------------
573 PROCEDURE Delete_Row
574 (
575 X_LEASE_ID in NUMBER
576 )
577 IS
578 l_leaseDetailId NUMBER := NULL;
579 l_leaseTransactionId NUMBER := NULL;
580 l_leaseChangeId NUMBER := NULL;
581 BEGIN
582 pnp_debug_pkg.debug ('PN_LEASES_PKG.Delete_Row (+)');
583
584 SELECT pd.lease_detail_id,
585 pt.lease_transaction_id,
586 pc.lease_change_id
587 INTO l_leaseDetailId,l_leaseTransactionId,l_leaseChangeId
588 FROM pn_lease_details_all pd
589 ,pn_lease_transactions_all pt
590 ,pn_lease_changes_all pc
591 WHERE pd.lease_id = x_lease_id
592 AND pc.lease_change_id = pd.lease_change_id
593 AND pt.lease_transaction_id = pc.lease_transaction_id
594 FOR UPDATE OF lease_detail_id NOWAIT;
595
596 -- first we need to DELETE the lease detail rows.
597 pn_lease_details_pkg.Delete_Row (X_LEASE_DETAIL_ID =>l_leaseDetailId);
598
599
603
600 -- we need to DELETE the transactions rows.
601 pn_lease_changes_pkg.Delete_Row_transactions (X_LEASE_TRANSACTION_ID =>l_leaseTransactionId);
602
604 -- we need to DELETE the lease changes rows.
605 pn_lease_changes_pkg.Delete_Row (X_LEASE_CHANGE_ID =>l_leaseChangeId);
606
607 DELETE FROM pn_leases_all
608 WHERE lease_id = x_lease_id;
609
610 IF (SQL%NOTFOUND) THEN
611 RAISE NO_DATA_FOUND;
612 END IF;
613
614 pnp_debug_pkg.debug ('PN_LEASES_PKG.Delete_Row (-)');
615
616 END Delete_Row;
617
618 -------------------------------------------------------------------------------
619 -- PROCDURE : check_unique_lease_number
620 -- INVOKED FROM : insert_row and update_row procedure
621 -- PURPOSE : checks unique lease number
622 -- HISTORY :
623 -------------------------------------------------------------------------------
624 PROCEDURE check_unique_lease_number
625 (
626 x_return_status IN OUT NOCOPY VARCHAR2,
627 x_lease_id IN NUMBER,
628 x_lease_number IN VARCHAR2
629 )
630 IS
631 l_dummy NUMBER;
632 BEGIN
633 pnp_debug_pkg.debug ('PN_LEASES_PKG.check_UNIQUE_lease_number (+)');
634
635 SELECT 1
636 INTO l_dummy
637 FROM DUAL
638 WHERE NOT EXISTS (SELECT 1
639 FROM pn_leases pnl
640 WHERE pnl.lease_num = x_lease_number
641 AND ((x_lease_id IS NULL) OR (pnl.lease_id <> x_lease_id))
642 );
643
644 pnp_debug_pkg.debug ('PN_LEASES_PKG.check_UNIQUE_lease_number (-)');
645
646 EXCEPTION
647 WHEN NO_DATA_FOUND THEN
648 fnd_message.set_name ('PN','PN_DUP_LEASE_NUMBER');
649 fnd_message.set_token('LEASE_NUMBER', x_lease_number);
650 x_return_status := 'E';
651 END check_unique_lease_number;
652
653 /* --- OVERLOADED functions and procedures for MOAC START --- */
654 -------------------------------------------------------------------------------
655 -- PROCDURE : check_unique_lease_number
656 -- INVOKED FROM : insert_row and update_row procedure
657 -- PURPOSE : checks unique lease number
658 -- IMPORTANT - Use this function once MOAC is enabled. All form libraries
659 -- must call this.
660 -- HISTORY :
661 -- 05-JUL-05 piagrawa o Bug 4284035 - Created
662 -------------------------------------------------------------------------------
663 PROCEDURE check_unique_lease_number
664 (
665 x_return_status IN OUT NOCOPY VARCHAR2,
666 x_lease_id IN NUMBER,
667 x_lease_number IN VARCHAR2,
668 x_org_id IN NUMBER
669 )
670 IS
671 l_dummy NUMBER;
672 BEGIN
673 pnp_debug_pkg.debug ('PN_LEASES_PKG.check_UNIQUE_lease_number (+)');
674
675 SELECT 1
676 INTO l_dummy
677 FROM DUAL
678 WHERE NOT EXISTS (SELECT 1
679 FROM pn_leases_all pnl
680 WHERE pnl.lease_num = x_lease_number
681 AND ((x_lease_id IS NULL) OR (pnl.lease_id <> x_lease_id))
682 AND org_id = x_org_id
683 );
684
685 pnp_debug_pkg.debug ('PN_LEASES_PKG.check_UNIQUE_lease_number (-)');
686
687 EXCEPTION
688 WHEN NO_DATA_FOUND THEN
689 fnd_message.set_name ('PN','PN_DUP_LEASE_NUMBER');
690 fnd_message.set_token('LEASE_NUMBER', x_lease_number);
691 x_return_status := 'E';
692 END check_unique_lease_number;
693 /* --- OVERLOADED functions and procedures for MOAC END --- */
694
695 END pn_leases_pkg;