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