1 PACKAGE BODY pn_insurance_requirements_pkg AS
2 -- $Header: PNTINRQB.pls 120.2 2005/12/01 07:39:58 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_insurance_requirements with
9 -- _ALL table.
10 -- 28-Nov-05 pikhar o fetched org_id using cursor
11 -------------------------------------------------------------------------------
12 PROCEDURE Insert_Row (
13 X_ROWID IN OUT NOCOPY VARCHAR2,
14 X_INSURANCE_REQUIREMENT_ID IN OUT NOCOPY NUMBER,
15 X_INSURANCE_TYPE_LOOKUP_CODE IN VARCHAR2,
16 X_LEASE_ID IN NUMBER,
17 X_LEASE_CHANGE_ID IN NUMBER,
18 X_POLICY_START_DATE IN DATE,
19 X_POLICY_EXPIRATION_DATE IN DATE,
20 X_INSURER_NAME IN VARCHAR2,
21 X_POLICY_NUMBER IN VARCHAR2,
22 X_INSURED_AMOUNT IN NUMBER,
23 X_REQUIRED_AMOUNT IN NUMBER,
24 X_STATUS IN VARCHAR2,
25 X_INSURANCE_COMMENTS IN VARCHAR2,
26 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
27 X_ATTRIBUTE1 IN VARCHAR2,
28 X_ATTRIBUTE2 IN VARCHAR2,
29 X_ATTRIBUTE3 IN VARCHAR2,
30 X_ATTRIBUTE4 IN VARCHAR2,
31 X_ATTRIBUTE5 IN VARCHAR2,
32 X_ATTRIBUTE6 IN VARCHAR2,
33 X_ATTRIBUTE7 IN VARCHAR2,
34 X_ATTRIBUTE8 IN VARCHAR2,
35 X_ATTRIBUTE9 IN VARCHAR2,
36 X_ATTRIBUTE10 IN VARCHAR2,
37 X_ATTRIBUTE11 IN VARCHAR2,
38 X_ATTRIBUTE12 IN VARCHAR2,
39 X_ATTRIBUTE13 IN VARCHAR2,
40 X_ATTRIBUTE14 IN VARCHAR2,
41 X_ATTRIBUTE15 IN VARCHAR2,
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_org_id IN NUMBER
48 )
49 IS
50 CURSOR C IS
51 SELECT ROWID
52 FROM pn_insurance_requirements_all
53 WHERE insurance_requirement_id = x_insurance_requirement_id;
54
55 CURSOR org_cur IS
56 SELECT org_id
57 FROM pn_leases_all
58 WHERE lease_id = X_LEASE_ID ;
59
60 l_org_id NUMBER;
61
62 BEGIN
63
64 IF x_insurance_requirement_id IS NULL THEN
65
66 SELECT pn_insurance_requirements_s.NEXTVAL
67 INTO x_insurance_requirement_id
68 FROM DUAL;
69
70 END IF;
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 INSERT INTO PN_INSURANCE_REQUIREMENTS_ALL
81 (
82 CREATION_DATE,
83 CREATED_BY,
84 LAST_UPDATE_LOGIN,
85 INSURANCE_TYPE_LOOKUP_CODE,
86 LEASE_ID,
87 LEASE_CHANGE_ID,
88 POLICY_START_DATE,
89 POLICY_EXPIRATION_DATE,
90 INSURER_NAME,
91 POLICY_NUMBER,
92 INSURED_AMOUNT,
93 REQUIRED_AMOUNT,
94 STATUS,
95 INSURANCE_COMMENTS,
96 ATTRIBUTE_CATEGORY,
97 ATTRIBUTE1,
98 ATTRIBUTE2,
99 ATTRIBUTE3,
100 ATTRIBUTE4,
101 ATTRIBUTE5,
102 ATTRIBUTE6,
103 ATTRIBUTE7,
104 ATTRIBUTE8,
105 ATTRIBUTE9,
106 ATTRIBUTE10,
107 ATTRIBUTE11,
108 ATTRIBUTE12,
109 ATTRIBUTE13,
110 ATTRIBUTE14,
111 ATTRIBUTE15,
112 INSURANCE_REQUIREMENT_ID,
113 LAST_UPDATE_DATE,
114 LAST_UPDATED_BY,
115 org_id
116 )
117 VALUES
118 (
119 X_CREATION_DATE,
120 X_CREATED_BY,
121 X_LAST_UPDATE_LOGIN,
122 X_INSURANCE_TYPE_LOOKUP_CODE,
123 X_LEASE_ID,
124 X_LEASE_CHANGE_ID,
125 X_POLICY_START_DATE,
126 X_POLICY_EXPIRATION_DATE,
127 X_INSURER_NAME,
128 X_POLICY_NUMBER,
129 X_INSURED_AMOUNT,
130 X_REQUIRED_AMOUNT,
131 X_STATUS,
132 X_INSURANCE_COMMENTS,
133 X_ATTRIBUTE_CATEGORY,
134 X_ATTRIBUTE1,
135 X_ATTRIBUTE2,
136 X_ATTRIBUTE3,
137 X_ATTRIBUTE4,
138 X_ATTRIBUTE5,
139 X_ATTRIBUTE6,
140 X_ATTRIBUTE7,
141 X_ATTRIBUTE8,
142 X_ATTRIBUTE9,
143 X_ATTRIBUTE10,
144 X_ATTRIBUTE11,
145 X_ATTRIBUTE12,
146 X_ATTRIBUTE13,
147 X_ATTRIBUTE14,
148 X_ATTRIBUTE15,
149 X_INSURANCE_REQUIREMENT_ID,
150 X_LAST_UPDATE_DATE,
151 X_LAST_UPDATED_BY,
152 l_org_id
153 );
154
155 OPEN c;
156 FETCH c INTO X_ROWID;
157 IF (c%NOTFOUND) THEN
158 CLOSE c;
159 RAISE NO_DATA_FOUND;
160 END IF;
161 CLOSE c;
162
163 END Insert_Row;
164
165 -------------------------------------------------------------------------------
166 -- PROCDURE : Lock_row
167 -- INVOKED FROM : Lock_row procedure
168 -- PURPOSE : locks the row
169 -- HISTORY :
170 -- 05-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_insurance_requirements with
171 -- _ALL table.
172 -------------------------------------------------------------------------------
173 PROCEDURE Lock_Row
174 (
175 X_INSURANCE_REQUIREMENT_ID IN NUMBER,
176 X_INSURANCE_TYPE_LOOKUP_CODE IN VARCHAR2,
177 X_LEASE_ID IN NUMBER,
178 X_LEASE_CHANGE_ID IN NUMBER,
179 X_POLICY_START_DATE IN DATE,
180 X_POLICY_EXPIRATION_DATE IN DATE,
181 X_INSURER_NAME IN VARCHAR2,
182 X_POLICY_NUMBER IN VARCHAR2,
183 X_INSURED_AMOUNT IN NUMBER,
184 X_REQUIRED_AMOUNT IN NUMBER,
185 X_STATUS IN VARCHAR2,
186 X_INSURANCE_COMMENTS IN VARCHAR2,
187 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
188 X_ATTRIBUTE1 IN VARCHAR2,
189 X_ATTRIBUTE2 IN VARCHAR2,
190 X_ATTRIBUTE3 IN VARCHAR2,
191 X_ATTRIBUTE4 IN VARCHAR2,
192 X_ATTRIBUTE5 IN VARCHAR2,
193 X_ATTRIBUTE6 IN VARCHAR2,
194 X_ATTRIBUTE7 IN VARCHAR2,
195 X_ATTRIBUTE8 IN VARCHAR2,
196 X_ATTRIBUTE9 IN VARCHAR2,
197 X_ATTRIBUTE10 IN VARCHAR2,
198 X_ATTRIBUTE11 IN VARCHAR2,
199 X_ATTRIBUTE12 IN VARCHAR2,
200 X_ATTRIBUTE13 IN VARCHAR2,
201 X_ATTRIBUTE14 IN VARCHAR2,
202 X_ATTRIBUTE15 IN VARCHAR2
203 )
204 IS
205 CURSOR c1 IS
206 SELECT *
207 FROM PN_INSURANCE_REQUIREMENTS_ALL
208 WHERE INSURANCE_REQUIREMENT_ID = X_INSURANCE_REQUIREMENT_ID
209 FOR UPDATE OF INSURANCE_REQUIREMENT_ID NOWAIT;
210
211 tlinfo c1%ROWTYPE;
212
213 BEGIN
214 OPEN c1;
215 FETCH c1 INTO tlinfo;
216 IF (c1%NOTFOUND) THEN
217 CLOSE c1;
218 RETURN;
219 END IF;
220 CLOSE c1;
221
222 IF NOT (tlinfo.INSURANCE_REQUIREMENT_ID = X_INSURANCE_REQUIREMENT_ID) THEN
223 pn_var_rent_pkg.lock_row_exception('INSURANCE_REQUIREMENT_ID',tlinfo.INSURANCE_REQUIREMENT_ID);
224 END IF;
225
226 IF NOT (tlinfo.INSURANCE_TYPE_LOOKUP_CODE = X_INSURANCE_TYPE_LOOKUP_CODE) THEN
227 pn_var_rent_pkg.lock_row_exception('INSURANCE_TYPE_LOOKUP_CODE',tlinfo.INSURANCE_TYPE_LOOKUP_CODE);
228 END IF;
229
230 IF NOT (tlinfo.LEASE_ID = X_LEASE_ID) THEN
231 pn_var_rent_pkg.lock_row_exception('LEASE_ID',tlinfo.LEASE_ID);
232 END IF;
233
234 IF NOT (tlinfo.LEASE_CHANGE_ID = X_LEASE_CHANGE_ID) THEN
235 pn_var_rent_pkg.lock_row_exception('LEASE_CHANGE_ID',tlinfo.LEASE_CHANGE_ID);
236 END IF;
237
238 IF NOT (tlinfo.STATUS = X_STATUS) THEN
239 pn_var_rent_pkg.lock_row_exception('STATUS',tlinfo.STATUS);
240 END IF;
241
242 IF NOT ((tlinfo.INSURANCE_COMMENTS = X_INSURANCE_COMMENTS)
243 OR ((tlinfo.INSURANCE_COMMENTS IS NULL) AND (X_INSURANCE_COMMENTS IS NULL))) THEN
244 pn_var_rent_pkg.lock_row_exception('INSURANCE_COMMENTS',tlinfo.INSURANCE_COMMENTS);
245 END IF;
246
247 IF NOT ((tlinfo.POLICY_START_DATE = X_POLICY_START_DATE)
248 OR ((tlinfo.POLICY_START_DATE IS NULL) AND (X_POLICY_START_DATE IS NULL))) THEN
249 pn_var_rent_pkg.lock_row_exception('POLICY_START_DATE',tlinfo.POLICY_START_DATE);
250 END IF;
251
252 IF NOT ((tlinfo.POLICY_EXPIRATION_DATE = X_POLICY_EXPIRATION_DATE)
253 OR ((tlinfo.POLICY_EXPIRATION_DATE IS NULL) AND (X_POLICY_EXPIRATION_DATE IS NULL))) THEN
254 pn_var_rent_pkg.lock_row_exception('POLICY_EXPIRATION_DATE',tlinfo.POLICY_EXPIRATION_DATE);
255 END IF;
256
257 IF NOT ((tlinfo.INSURER_NAME = X_INSURER_NAME)
258 OR ((tlinfo.INSURER_NAME IS NULL) AND (X_INSURER_NAME IS NULL))) THEN
259 pn_var_rent_pkg.lock_row_exception('INSURER_NAME',tlinfo.INSURER_NAME);
260 END IF;
261
262 IF NOT ((tlinfo.POLICY_NUMBER = X_POLICY_NUMBER)
263 OR ((tlinfo.POLICY_NUMBER IS NULL) AND (X_POLICY_NUMBER IS NULL))) THEN
264 pn_var_rent_pkg.lock_row_exception('POLICY_NUMBER',tlinfo.POLICY_NUMBER);
265 END IF;
266
267 IF NOT ((tlinfo.INSURED_AMOUNT = X_INSURED_AMOUNT)
268 OR ((tlinfo.INSURED_AMOUNT IS NULL) AND (X_INSURED_AMOUNT IS NULL))) THEN
269 pn_var_rent_pkg.lock_row_exception('INSURED_AMOUNT',tlinfo.INSURED_AMOUNT);
270 END IF;
271
272 IF NOT ((tlinfo.REQUIRED_AMOUNT = X_REQUIRED_AMOUNT)
273 OR ((tlinfo.REQUIRED_AMOUNT IS NULL) AND (X_REQUIRED_AMOUNT IS NULL))) THEN
274 pn_var_rent_pkg.lock_row_exception('REQUIRED_AMOUNT',tlinfo.REQUIRED_AMOUNT);
275 END IF;
276
277 IF NOT ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
278 OR ((tlinfo.ATTRIBUTE_CATEGORY IS NULL) AND (X_ATTRIBUTE_CATEGORY IS NULL))) THEN
279 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE_CATEGORY',tlinfo.ATTRIBUTE_CATEGORY);
280 END IF;
281
282 IF NOT ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
283 OR ((tlinfo.ATTRIBUTE1 IS NULL) AND (X_ATTRIBUTE1 IS NULL))) THEN
284 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE1',tlinfo.ATTRIBUTE1);
285 END IF;
286
287 IF NOT ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
288 OR ((tlinfo.ATTRIBUTE2 IS NULL) AND (X_ATTRIBUTE2 IS NULL))) THEN
289 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE2',tlinfo.ATTRIBUTE2);
290 END IF;
291
292 IF NOT ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
293 OR ((tlinfo.ATTRIBUTE3 IS NULL) AND (X_ATTRIBUTE3 IS NULL))) THEN
294 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE3',tlinfo.ATTRIBUTE3);
295 END IF;
296
297 IF NOT ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
298 OR ((tlinfo.ATTRIBUTE4 IS NULL) AND (X_ATTRIBUTE4 IS NULL))) THEN
299 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE4',tlinfo.ATTRIBUTE4);
300 END IF;
301
302 IF NOT ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
303 OR ((tlinfo.ATTRIBUTE5 IS NULL) AND (X_ATTRIBUTE5 IS NULL))) THEN
304 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE5',tlinfo.ATTRIBUTE5);
305 END IF;
306
307 IF NOT ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
308 OR ((tlinfo.ATTRIBUTE6 IS NULL) AND (X_ATTRIBUTE6 IS NULL))) THEN
309 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE6',tlinfo.ATTRIBUTE6);
310 END IF;
311
312 IF NOT ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
313 OR ((tlinfo.ATTRIBUTE7 IS NULL) AND (X_ATTRIBUTE7 IS NULL))) THEN
314 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE7',tlinfo.ATTRIBUTE7);
315 END IF;
316
317 IF NOT ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
318 OR ((tlinfo.ATTRIBUTE8 IS NULL) AND (X_ATTRIBUTE8 IS NULL))) THEN
319 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE8',tlinfo.ATTRIBUTE8);
320 END IF;
321
322 IF NOT ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
323 OR ((tlinfo.ATTRIBUTE9 IS NULL) AND (X_ATTRIBUTE9 IS NULL))) THEN
324 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE9',tlinfo.ATTRIBUTE9);
325 END IF;
326
327 IF NOT ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
328 OR ((tlinfo.ATTRIBUTE10 IS NULL) AND (X_ATTRIBUTE10 IS NULL))) THEN
329 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE10',tlinfo.ATTRIBUTE10);
330 END IF;
331
332 IF NOT ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
333 OR ((tlinfo.ATTRIBUTE11 IS NULL) AND (X_ATTRIBUTE11 IS NULL))) THEN
334 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE11',tlinfo.ATTRIBUTE11);
335 END IF;
336
337 IF NOT ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
338 OR ((tlinfo.ATTRIBUTE12 IS NULL) AND (X_ATTRIBUTE12 IS NULL))) THEN
339 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE12',tlinfo.ATTRIBUTE12);
340 END IF;
341
342 IF NOT ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
343 OR ((tlinfo.ATTRIBUTE13 IS NULL) AND (X_ATTRIBUTE13 IS NULL))) THEN
344 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE13',tlinfo.ATTRIBUTE13);
345 END IF;
346
347 IF NOT ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
348 OR ((tlinfo.ATTRIBUTE14 IS NULL) AND (X_ATTRIBUTE14 IS NULL))) THEN
349 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE14',tlinfo.ATTRIBUTE14);
350 END IF;
351
352 IF NOT ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
353 OR ((tlinfo.ATTRIBUTE15 IS NULL) AND (X_ATTRIBUTE15 IS NULL))) THEN
354 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE15',tlinfo.ATTRIBUTE15);
355 END IF;
356
357 RETURN;
358
359 END Lock_Row;
360
361 -------------------------------------------------------------------------------
362 -- PROCDURE : Update_Row
363 -- INVOKED FROM : Update_Row procedure
364 -- PURPOSE : updates the row
365 -- HISTORY :
366 -- 05-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_insurance_requirements with
367 -- _ALL table.
368 -------------------------------------------------------------------------------
369 PROCEDURE Update_Row
370 (
371 X_INSURANCE_REQUIREMENT_ID IN NUMBER,
372 X_INSURANCE_TYPE_LOOKUP_CODE IN VARCHAR2,
373 X_LEASE_ID IN NUMBER,
374 X_LEASE_CHANGE_ID IN NUMBER,
375 X_POLICY_START_DATE IN DATE,
376 X_POLICY_EXPIRATION_DATE IN DATE,
377 X_INSURER_NAME IN VARCHAR2,
378 X_POLICY_NUMBER IN VARCHAR2,
379 X_INSURED_AMOUNT IN NUMBER,
380 X_REQUIRED_AMOUNT IN NUMBER,
381 X_STATUS IN VARCHAR2,
382 X_INSURANCE_COMMENTS IN VARCHAR2,
383 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
384 X_ATTRIBUTE1 IN VARCHAR2,
385 X_ATTRIBUTE2 IN VARCHAR2,
386 X_ATTRIBUTE3 IN VARCHAR2,
387 X_ATTRIBUTE4 IN VARCHAR2,
388 X_ATTRIBUTE5 IN VARCHAR2,
389 X_ATTRIBUTE6 IN VARCHAR2,
390 X_ATTRIBUTE7 IN VARCHAR2,
391 X_ATTRIBUTE8 IN VARCHAR2,
392 X_ATTRIBUTE9 IN VARCHAR2,
393 X_ATTRIBUTE10 IN VARCHAR2,
394 X_ATTRIBUTE11 IN VARCHAR2,
395 X_ATTRIBUTE12 IN VARCHAR2,
396 X_ATTRIBUTE13 IN VARCHAR2,
397 X_ATTRIBUTE14 IN VARCHAR2,
398 X_ATTRIBUTE15 IN VARCHAR2,
399 X_LAST_UPDATE_DATE IN DATE,
400 X_LAST_UPDATED_BY IN NUMBER,
401 X_LAST_UPDATE_LOGIN IN NUMBER
402 )
403 IS
404 CURSOR c2 IS
405 SELECT *
406 FROM pn_insurance_requirements_all
407 WHERE insurance_requirement_id = x_insurance_requirement_id;
408
409 recInfoForHist c2%ROWTYPE;
410
411 l_insuranceHistoryId NUMBER := NULL;
412 l_leaseStatus VARCHAR2(30) := NULL;
413
414 BEGIN
415 ---------------------------------------------------------------
416 -- get the lease status
417 ---------------------------------------------------------------
418 l_leaseStatus := PNP_UTIL_FUNC.GET_LEASE_STATUS (X_LEASE_ID);
419
420 ---------------------------------------------------------------
421 -- We need to INSERT the history row IF the lease IS finalised
422 ---------------------------------------------------------------
423 IF (l_leaseStatus = 'F') THEN
424
425 OPEN c2;
426 FETCH c2 INTO recInfoForHist;
427 IF (c2%NOTFOUND) THEN
428 CLOSE c2;
429 RAISE NO_DATA_FOUND;
430 END IF;
431 CLOSE c2;
432
433 IF (recInfoForHist.LEASE_CHANGE_ID <> X_LEASE_CHANGE_ID) THEN
434
435 SELECT pn_insur_require_history_s.NEXTVAL
436 INTO l_insuranceHistoryId
437 FROM DUAL;
438
439 INSERT INTO PN_INSUR_REQUIRE_HISTORY
440 (
441 INSURANCE_HISTORY_ID,
442 INSURANCE_TYPE_LOOKUP_CODE,
443 LEASE_ID,
444 LEASE_CHANGE_ID,
445 NEW_LEASE_CHANGE_ID,
446 POLICY_START_DATE,
447 POLICY_EXPIRATION_DATE,
448 INSURER_NAME,
449 POLICY_NUMBER,
450 INSURED_AMOUNT,
451 REQUIRED_AMOUNT,
452 STATUS,
453 INSURANCE_COMMENTS,
454 ATTRIBUTE_CATEGORY,
455 ATTRIBUTE1,
456 ATTRIBUTE2,
457 ATTRIBUTE3,
458 ATTRIBUTE4,
459 ATTRIBUTE5,
460 ATTRIBUTE6,
461 ATTRIBUTE7,
462 ATTRIBUTE8,
463 ATTRIBUTE9,
464 ATTRIBUTE10,
465 ATTRIBUTE11,
466 ATTRIBUTE12,
467 ATTRIBUTE13,
468 ATTRIBUTE14,
469 ATTRIBUTE15,
470 INSURANCE_REQUIREMENT_ID,
471 CREATION_DATE,
472 CREATED_BY,
473 LAST_UPDATE_DATE,
474 LAST_UPDATED_BY,
475 LAST_UPDATE_LOGIN
476 )
477 VALUES
478 (
479 l_insuranceHistoryId,
480 recInfoForHist.INSURANCE_TYPE_LOOKUP_CODE,
481 recInfoForHist.LEASE_ID,
482 recInfoForHist.LEASE_CHANGE_ID,
483 X_LEASE_CHANGE_ID,
484 recInfoForHist.POLICY_START_DATE,
485 recInfoForHist.POLICY_EXPIRATION_DATE,
486 recInfoForHist.INSURER_NAME,
487 recInfoForHist.POLICY_NUMBER,
488 recInfoForHist.INSURED_AMOUNT,
489 recInfoForHist.REQUIRED_AMOUNT,
490 recInfoForHist.STATUS,
491 recInfoForHist.INSURANCE_COMMENTS,
492 recInfoForHist.ATTRIBUTE_CATEGORY,
493 recInfoForHist.ATTRIBUTE1,
494 recInfoForHist.ATTRIBUTE2,
495 recInfoForHist.ATTRIBUTE3,
496 recInfoForHist.ATTRIBUTE4,
497 recInfoForHist.ATTRIBUTE5,
498 recInfoForHist.ATTRIBUTE6,
499 recInfoForHist.ATTRIBUTE7,
500 recInfoForHist.ATTRIBUTE8,
501 recInfoForHist.ATTRIBUTE9,
502 recInfoForHist.ATTRIBUTE10,
503 recInfoForHist.ATTRIBUTE11,
504 recInfoForHist.ATTRIBUTE12,
505 recInfoForHist.ATTRIBUTE13,
506 recInfoForHist.ATTRIBUTE14,
507 recInfoForHist.ATTRIBUTE15,
508 recInfoForHist.INSURANCE_REQUIREMENT_ID,
509 recInfoForHist.CREATION_DATE,
510 recInfoForHist.CREATED_BY,
511 recInfoForHist.LAST_UPDATE_DATE,
512 recInfoForHist.LAST_UPDATED_BY,
513 recInfoForHist.LAST_UPDATE_LOGIN
514 );
515 END IF;
516 END IF;
517
518 UPDATE pn_insurance_requirements_all
519 SET INSURANCE_TYPE_LOOKUP_CODE = X_INSURANCE_TYPE_LOOKUP_CODE,
520 LEASE_CHANGE_ID = X_LEASE_CHANGE_ID,
521 POLICY_START_DATE = X_POLICY_START_DATE,
522 POLICY_EXPIRATION_DATE = X_POLICY_EXPIRATION_DATE,
523 INSURER_NAME = X_INSURER_NAME,
524 POLICY_NUMBER = X_POLICY_NUMBER,
525 INSURED_AMOUNT = X_INSURED_AMOUNT,
526 REQUIRED_AMOUNT = X_REQUIRED_AMOUNT,
527 STATUS = X_STATUS,
528 INSURANCE_COMMENTS = X_INSURANCE_COMMENTS,
529 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
530 ATTRIBUTE1 = X_ATTRIBUTE1,
531 ATTRIBUTE2 = X_ATTRIBUTE2,
532 ATTRIBUTE3 = X_ATTRIBUTE3,
533 ATTRIBUTE4 = X_ATTRIBUTE4,
534 ATTRIBUTE5 = X_ATTRIBUTE5,
535 ATTRIBUTE6 = X_ATTRIBUTE6,
536 ATTRIBUTE7 = X_ATTRIBUTE7,
537 ATTRIBUTE8 = X_ATTRIBUTE8,
538 ATTRIBUTE9 = X_ATTRIBUTE9,
539 ATTRIBUTE10 = X_ATTRIBUTE10,
540 ATTRIBUTE11 = X_ATTRIBUTE11,
541 ATTRIBUTE12 = X_ATTRIBUTE12,
542 ATTRIBUTE13 = X_ATTRIBUTE13,
543 ATTRIBUTE14 = X_ATTRIBUTE14,
544 ATTRIBUTE15 = X_ATTRIBUTE15,
545 INSURANCE_REQUIREMENT_ID = X_INSURANCE_REQUIREMENT_ID,
546 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
547 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
548 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
549 WHERE INSURANCE_REQUIREMENT_ID = X_INSURANCE_REQUIREMENT_ID ;
550
551 IF (SQL%NOTFOUND) THEN
552 RAISE NO_DATA_FOUND;
553 END IF;
554
555 END Update_Row;
556
557 -------------------------------------------------------------------------------
558 -- PROCDURE : Delete_Row
559 -- INVOKED FROM : Delete_Row procedure
560 -- PURPOSE : deletes the row
561 -- HISTORY :
562 -- 05-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_insurance_requirements with
563 -- _ALL table.
564 -------------------------------------------------------------------------------
565 PROCEDURE Delete_Row
566 (
567 X_INSURANCE_REQUIREMENT_ID IN NUMBER
568 )
569 IS
570 BEGIN
571 DELETE FROM pn_insurance_requirements_all
572 WHERE insurance_requirement_id = x_insurance_requirement_id;
573
574 IF (SQL%NOTFOUND) THEN
575 RAISE NO_DATA_FOUND;
576 END IF;
577 END Delete_Row;
578
579 END pn_insurance_requirements_pkg;
[Home] [Help]