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