4 -- PROCDURE : Insert_Row
1 PACKAGE BODY pn_options_pkg AS
2 -- $Header: PNTOPTNB.pls 120.2 2005/12/01 08:25:59 appldev ship $
3 -------------------------------------------------------------------------------
5 -- HISTORY :
6 -- 28-NOV-05 pikhar o fetched org_id using cursor
7 -------------------------------------------------------------------------------
8 PROCEDURE Insert_Row (
9 X_ROWID IN OUT NOCOPY VARCHAR2,
10 X_OPTION_ID IN OUT NOCOPY NUMBER,
11 X_OPTION_NUM IN OUT NOCOPY VARCHAR2,
12 X_LEASE_ID IN NUMBER,
13 X_LEASE_CHANGE_ID IN NUMBER,
14 X_OPTION_TYPE_CODE IN VARCHAR2,
15 X_START_DATE IN DATE,
16 X_EXPIRATION_DATE IN DATE,
17 X_OPTION_SIZE IN NUMBER,
18 X_UOM_CODE IN VARCHAR2,
19 X_OPTION_STATUS_LOOKUP_CODE IN VARCHAR2,
20 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
21 X_ATTRIBUTE1 IN VARCHAR2,
22 X_ATTRIBUTE2 IN VARCHAR2,
23 X_ATTRIBUTE3 IN VARCHAR2,
24 X_ATTRIBUTE4 IN VARCHAR2,
25 X_ATTRIBUTE5 IN VARCHAR2,
26 X_ATTRIBUTE6 IN VARCHAR2,
27 X_ATTRIBUTE7 IN VARCHAR2,
28 X_ATTRIBUTE8 IN VARCHAR2,
29 X_ATTRIBUTE9 IN VARCHAR2,
30 X_ATTRIBUTE10 IN VARCHAR2,
31 X_ATTRIBUTE11 IN VARCHAR2,
32 X_ATTRIBUTE12 IN VARCHAR2,
33 X_ATTRIBUTE13 IN VARCHAR2,
34 X_ATTRIBUTE14 IN VARCHAR2,
35 X_ATTRIBUTE15 IN VARCHAR2,
36 X_CREATION_DATE IN DATE,
37 X_CREATED_BY IN NUMBER,
38 X_LAST_UPDATE_DATE IN DATE,
39 X_LAST_UPDATED_BY IN NUMBER,
40 X_LAST_UPDATE_LOGIN IN NUMBER,
41 X_OPTION_EXER_START_DATE IN DATE,
42 X_OPTION_EXER_END_DATE IN DATE,
43 X_OPTION_ACTION_DATE IN DATE,
44 X_OPTION_COST IN VARCHAR2,
45 X_OPTION_AREA_CHANGE IN NUMBER,
46 X_OPTION_REFERENCE IN VARCHAR2,
47 X_OPTION_NOTICE_REQD IN VARCHAR2,
48 X_OPTION_COMMENTS IN VARCHAR2,
49 x_org_id IN NUMBER
50 )
51 IS
52
53 CURSOR c IS
54 SELECT ROWID
55 FROM pn_options_all
56 WHERE option_id = x_option_id ;
57
58 CURSOR org_cur IS
59 SELECT org_id
60 FROM pn_leases_all
61 WHERE lease_id = x_lease_id ;
62
63 l_org_id NUMBER;
64
65 BEGIN
66
67 pnp_debug_pkg.debug ('PN_OPTIONS_PKG.INSERT_ROW (+)');
68
69 -------------------------------------------------------
70 -- We need to generate the lease change number
71 -------------------------------------------------------
72 SELECT NVL(MAX(TO_NUMBER(pno.option_num)), 0)+1
73 INTO x_option_num
74 FROM pn_options_all PNO
75 WHERE pno.lease_id = x_lease_id;
76
77 -------------------------------------------------------
78 -- SELECT the NEXTVAL for option id
79 -------------------------------------------------------
80
81 IF x_org_id IS NULL THEN
82 FOR rec IN org_cur LOOP
83 l_org_id := rec.org_id;
84 END LOOP;
85 ELSE
86 l_org_id := x_org_id;
87 END IF;
88
89 IF ( X_OPTION_ID IS NULL) THEN
90 SELECT pn_options_s.NEXTVAL
91 INTO x_option_id
92 FROM DUAL;
93 END IF;
94
95 INSERT INTO pn_options_all
96 (
97 OPTION_ID,
98 LAST_UPDATE_DATE,
99 LAST_UPDATED_BY,
100 CREATION_DATE,
101 CREATED_BY,
102 LAST_UPDATE_LOGIN,
103 LEASE_ID,
104 LEASE_CHANGE_ID,
105 OPTION_NUM,
106 OPTION_TYPE_CODE,
107 START_DATE,
108 EXPIRATION_DATE,
109 OPTION_SIZE,
110 UOM_CODE,
111 OPTION_STATUS_LOOKUP_CODE,
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 OPTION_EXER_START_DATE,
129 OPTION_EXER_END_DATE,
130 OPTION_ACTION_DATE,
131 OPTION_COST,
132 OPTION_AREA_CHANGE,
133 OPTION_REFERENCE,
134 OPTION_NOTICE_REQD,
135 OPTION_COMMENTS,
139 (
136 org_id
137 )
138 VALUES
140 X_OPTION_ID,
141 X_LAST_UPDATE_DATE,
142 X_LAST_UPDATED_BY,
143 X_CREATION_DATE,
144 X_CREATED_BY,
145 X_LAST_UPDATE_LOGIN,
146 X_LEASE_ID,
147 X_LEASE_CHANGE_ID,
148 X_OPTION_NUM,
149 X_OPTION_TYPE_CODE,
150 X_START_DATE,
151 X_EXPIRATION_DATE,
152 X_OPTION_SIZE,
153 X_UOM_CODE,
154 X_OPTION_STATUS_LOOKUP_CODE,
155 X_ATTRIBUTE_CATEGORY,
156 X_ATTRIBUTE1,
157 X_ATTRIBUTE2,
158 X_ATTRIBUTE3,
159 X_ATTRIBUTE4,
160 X_ATTRIBUTE5,
161 X_ATTRIBUTE6,
162 X_ATTRIBUTE7,
163 X_ATTRIBUTE8,
164 X_ATTRIBUTE9,
165 X_ATTRIBUTE10,
166 X_ATTRIBUTE11,
167 X_ATTRIBUTE12,
168 X_ATTRIBUTE13,
169 X_ATTRIBUTE14,
170 X_ATTRIBUTE15,
171 X_OPTION_EXER_START_DATE,
172 X_OPTION_EXER_END_DATE,
173 X_OPTION_ACTION_DATE,
174 X_OPTION_COST,
175 X_OPTION_AREA_CHANGE,
176 X_OPTION_REFERENCE,
177 X_OPTION_NOTICE_REQD,
178 X_OPTION_COMMENTS,
179 l_org_id
180 );
181
182 OPEN c;
183 FETCH c INTO X_ROWID;
184 IF (c%NOTFOUND) THEN
185 CLOSE c;
186 RAISE NO_DATA_FOUND;
187 END IF;
188 CLOSE c;
189
190 pnp_debug_pkg.debug ('PN_OPTIONS_PKG.INSERT_ROW (-)');
191
192 END Insert_Row;
193
194 -------------------------------------------------------------------------------
195 -- PROCDURE : Lock_Row
196 -- INVOKED FROM : Lock_Row procedure
197 -- PURPOSE : locks the row
198 -- HISTORY :
199 -- 05-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_options with _ALL table.
200 -------------------------------------------------------------------------------
201 PROCEDURE Lock_Row (
202 X_OPTION_ID IN NUMBER,
203 X_LEASE_ID IN NUMBER,
204 X_LEASE_CHANGE_ID IN NUMBER,
205 X_OPTION_NUM IN VARCHAR2,
206 X_OPTION_TYPE_CODE IN VARCHAR2,
207 X_START_DATE IN DATE,
208 X_EXPIRATION_DATE IN DATE,
209 X_OPTION_SIZE IN NUMBER,
210 X_UOM_CODE IN VARCHAR2,
211 X_OPTION_STATUS_LOOKUP_CODE IN VARCHAR2,
212 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
213 X_ATTRIBUTE1 IN VARCHAR2,
214 X_ATTRIBUTE2 IN VARCHAR2,
215 X_ATTRIBUTE3 IN VARCHAR2,
216 X_ATTRIBUTE4 IN VARCHAR2,
217 X_ATTRIBUTE5 IN VARCHAR2,
218 X_ATTRIBUTE6 IN VARCHAR2,
219 X_ATTRIBUTE7 IN VARCHAR2,
220 X_ATTRIBUTE8 IN VARCHAR2,
221 X_ATTRIBUTE9 IN VARCHAR2,
222 X_ATTRIBUTE10 IN VARCHAR2,
223 X_ATTRIBUTE11 IN VARCHAR2,
224 X_ATTRIBUTE12 IN VARCHAR2,
225 X_ATTRIBUTE13 IN VARCHAR2,
226 X_ATTRIBUTE14 IN VARCHAR2,
227 X_ATTRIBUTE15 IN VARCHAR2,
228 X_OPTION_EXER_START_DATE IN DATE,
229 X_OPTION_EXER_END_DATE IN DATE,
230 X_OPTION_ACTION_DATE IN DATE,
231 X_OPTION_COST IN VARCHAR2,
232 X_OPTION_AREA_CHANGE IN NUMBER,
233 X_OPTION_REFERENCE IN VARCHAR2,
234 X_OPTION_NOTICE_REQD IN VARCHAR2,
235 X_OPTION_COMMENTS IN VARCHAR2
236 )
237 IS
238 CURSOR c1 IS
239 SELECT *
240 FROM pn_options_all
241 WHERE option_id = x_option_id
242 FOR UPDATE OF option_id NOWAIT;
243
244 tlinfo c1%ROWTYPE;
245
246 BEGIN
247
248 pnp_debug_pkg.debug ('PN_OPTIONS_PKG.LOCK_ROW (+)');
249
250 OPEN c1;
251 FETCH c1 INTO tlinfo;
252 IF (c1%NOTFOUND) THEN
253 CLOSE c1;
254 RETURN;
255 END IF;
256 CLOSE c1;
257
258 IF NOT (tlinfo.OPTION_ID = X_OPTION_ID) THEN
259 pn_var_rent_pkg.lock_row_exception('OPTION_ID',tlinfo.OPTION_ID);
260 END IF;
261
262 IF NOT (tlinfo.LEASE_ID = X_LEASE_ID) THEN
263 pn_var_rent_pkg.lock_row_exception('LEASE_ID',tlinfo.LEASE_ID);
264 END IF;
265
266 IF NOT (tlinfo.LEASE_CHANGE_ID = X_LEASE_CHANGE_ID) THEN
267 pn_var_rent_pkg.lock_row_exception('LEASE_CHANGE_ID',tlinfo.LEASE_CHANGE_ID);
268 END IF;
269
270 IF NOT (tlinfo.OPTION_NUM = X_OPTION_NUM) THEN
271 pn_var_rent_pkg.lock_row_exception('OPTION_NUM',tlinfo.OPTION_NUM);
272 END IF;
273
274 IF NOT (tlinfo.OPTION_TYPE_CODE = X_OPTION_TYPE_CODE) THEN
275 pn_var_rent_pkg.lock_row_exception('OPTION_TYPE_CODE',tlinfo.OPTION_TYPE_CODE);
276 END IF;
277
278 IF NOT ((tlinfo.START_DATE = X_START_DATE)
279 OR ((tlinfo.START_DATE IS NULL) AND (X_START_DATE IS NULL))) THEN
280 pn_var_rent_pkg.lock_row_exception('START_DATE',tlinfo.START_DATE);
281 END IF;
282
283 IF NOT ((tlinfo.EXPIRATION_DATE = X_EXPIRATION_DATE)
284 OR ((tlinfo.EXPIRATION_DATE IS NULL) AND (X_EXPIRATION_DATE IS NULL))) THEN
285 pn_var_rent_pkg.lock_row_exception('EXPIRATION_DATE',tlinfo.EXPIRATION_DATE);
286 END IF;
287
288 IF NOT ((tlinfo.OPTION_SIZE = X_OPTION_SIZE)
289 OR ((tlinfo.OPTION_SIZE IS NULL) AND (X_OPTION_SIZE IS NULL))) THEN
290 pn_var_rent_pkg.lock_row_exception('OPTION_SIZE',tlinfo.OPTION_SIZE);
291 END IF;
292
293 IF NOT ((tlinfo.UOM_CODE = X_UOM_CODE)
294 OR ((tlinfo.UOM_CODE IS NULL) AND (X_UOM_CODE IS NULL))) THEN
295 pn_var_rent_pkg.lock_row_exception('UOM_CODE',tlinfo.UOM_CODE);
296 END IF;
297
298 IF NOT ((tlinfo.OPTION_STATUS_LOOKUP_CODE = X_OPTION_STATUS_LOOKUP_CODE)
299 OR ((tlinfo.OPTION_STATUS_LOOKUP_CODE IS NULL) AND (X_OPTION_STATUS_LOOKUP_CODE IS NULL))) THEN
300 pn_var_rent_pkg.lock_row_exception('OPTION_STATUS_LOOKUP_CODE',tlinfo.OPTION_STATUS_LOOKUP_CODE);
301 END IF;
302
303 IF NOT ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
304 OR ((tlinfo.ATTRIBUTE_CATEGORY IS NULL) AND (X_ATTRIBUTE_CATEGORY IS NULL))) THEN
305 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE_CATEGORY',tlinfo.ATTRIBUTE_CATEGORY);
306 END IF;
307
308 IF NOT ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
309 OR ((tlinfo.ATTRIBUTE1 IS NULL) AND (X_ATTRIBUTE1 IS NULL))) THEN
310 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE1',tlinfo.ATTRIBUTE1);
311 END IF;
312
313 IF NOT ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
314 OR ((tlinfo.ATTRIBUTE2 IS NULL) AND (X_ATTRIBUTE2 IS NULL))) THEN
315 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE2',tlinfo.ATTRIBUTE2);
316 END IF;
317
318 IF NOT ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
319 OR ((tlinfo.ATTRIBUTE3 IS NULL) AND (X_ATTRIBUTE3 IS NULL))) THEN
320 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE3',tlinfo.ATTRIBUTE3);
321 END IF;
322
323 IF NOT ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
324 OR ((tlinfo.ATTRIBUTE4 IS NULL) AND (X_ATTRIBUTE4 IS NULL))) THEN
325 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE4',tlinfo.ATTRIBUTE4);
326 END IF;
327
328 IF NOT ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
329 OR ((tlinfo.ATTRIBUTE5 IS NULL) AND (X_ATTRIBUTE5 IS NULL))) THEN
330 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE5',tlinfo.ATTRIBUTE5);
331 END IF;
332
333 IF NOT ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
334 OR ((tlinfo.ATTRIBUTE6 IS NULL) AND (X_ATTRIBUTE6 IS NULL))) THEN
335 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE6',tlinfo.ATTRIBUTE6);
336 END IF;
337
338 IF NOT ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
339 OR ((tlinfo.ATTRIBUTE7 IS NULL) AND (X_ATTRIBUTE7 IS NULL))) THEN
340 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE7',tlinfo.ATTRIBUTE7);
341 END IF;
342
343 IF NOT ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
344 OR ((tlinfo.ATTRIBUTE8 IS NULL) AND (X_ATTRIBUTE8 IS NULL))) THEN
345 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE8',tlinfo.ATTRIBUTE8);
346 END IF;
347
348 IF NOT ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
349 OR ((tlinfo.ATTRIBUTE9 IS NULL) AND (X_ATTRIBUTE9 IS NULL))) THEN
350 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE9',tlinfo.ATTRIBUTE9);
351 END IF;
352
353 IF NOT ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
354 OR ((tlinfo.ATTRIBUTE10 IS NULL) AND (X_ATTRIBUTE10 IS NULL))) THEN
355 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE10',tlinfo.ATTRIBUTE10);
356 END IF;
357
358 IF NOT ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
359 OR ((tlinfo.ATTRIBUTE11 IS NULL) AND (X_ATTRIBUTE11 IS NULL))) THEN
360 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE11',tlinfo.ATTRIBUTE11);
361 END IF;
362
363 IF NOT ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
364 OR ((tlinfo.ATTRIBUTE12 IS NULL) AND (X_ATTRIBUTE12 IS NULL))) THEN
365 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE12',tlinfo.ATTRIBUTE12);
366 END IF;
367
368 IF NOT ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
369 OR ((tlinfo.ATTRIBUTE13 IS NULL) AND (X_ATTRIBUTE13 IS NULL))) THEN
370 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE13',tlinfo.ATTRIBUTE13);
371 END IF;
372
373 IF NOT ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
374 OR ((tlinfo.ATTRIBUTE14 IS NULL) AND (X_ATTRIBUTE14 IS NULL))) THEN
375 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE14',tlinfo.ATTRIBUTE14);
376 END IF;
377
378 IF NOT ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
379 OR ((tlinfo.ATTRIBUTE15 IS NULL) AND (X_ATTRIBUTE15 IS NULL))) THEN
380 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE15',tlinfo.ATTRIBUTE15);
381 END IF;
382
383 IF NOT ((tlinfo.OPTION_EXER_START_DATE = X_OPTION_EXER_START_DATE)
384 OR ((tlinfo.OPTION_EXER_START_DATE IS NULL) AND (X_OPTION_EXER_START_DATE IS NULL))) THEN
385 pn_var_rent_pkg.lock_row_exception('OPTION_EXER_START_DATE',tlinfo.OPTION_EXER_START_DATE);
386 END IF;
390 pn_var_rent_pkg.lock_row_exception('OPTION_EXER_END_DATE',tlinfo.OPTION_EXER_END_DATE);
387
388 IF NOT ((tlinfo.OPTION_EXER_END_DATE = X_OPTION_EXER_END_DATE)
389 OR ((tlinfo.OPTION_EXER_END_DATE IS NULL) AND (X_OPTION_EXER_END_DATE IS NULL))) THEN
391 END IF;
392
393 IF NOT ((tlinfo.OPTION_ACTION_DATE = X_OPTION_ACTION_DATE)
394 OR ((tlinfo.OPTION_ACTION_DATE IS NULL) AND (X_OPTION_ACTION_DATE IS NULL))) THEN
395 pn_var_rent_pkg.lock_row_exception('OPTION_ACTION_DATE',tlinfo.OPTION_ACTION_DATE);
396 END IF;
397
398 IF NOT ((tlinfo.OPTION_COST = X_OPTION_COST)
399 OR ((tlinfo.OPTION_COST IS NULL) AND (X_OPTION_COST IS NULL))) THEN
400 pn_var_rent_pkg.lock_row_exception('OPTION_COST',tlinfo.OPTION_COST);
401 END IF;
402
403 IF NOT ((tlinfo.OPTION_AREA_CHANGE = X_OPTION_AREA_CHANGE)
404 OR ((tlinfo.OPTION_AREA_CHANGE IS NULL) AND (X_OPTION_AREA_CHANGE IS NULL))) THEN
405 pn_var_rent_pkg.lock_row_exception('OPTION_AREA_CHANGE',tlinfo.OPTION_AREA_CHANGE);
406 END IF;
407
408 IF NOT ((tlinfo.OPTION_REFERENCE = X_OPTION_REFERENCE)
409 OR ((tlinfo.OPTION_REFERENCE IS NULL) AND (X_OPTION_REFERENCE IS NULL))) THEN
410 pn_var_rent_pkg.lock_row_exception('OPTION_REFERENCE',tlinfo.OPTION_REFERENCE);
411 END IF;
412
413 IF NOT ((tlinfo.OPTION_NOTICE_REQD = X_OPTION_NOTICE_REQD)
414 OR ((tlinfo.OPTION_NOTICE_REQD IS NULL) AND (X_OPTION_NOTICE_REQD IS NULL))) THEN
415 pn_var_rent_pkg.lock_row_exception('OPTION_NOTICE_REQD',tlinfo.OPTION_NOTICE_REQD);
416 END IF;
417
418 IF NOT ((tlinfo.OPTION_COMMENTS = X_OPTION_COMMENTS)
419 OR ((tlinfo.OPTION_COMMENTS IS NULL) AND (X_OPTION_COMMENTS IS NULL))) THEN
420 pn_var_rent_pkg.lock_row_exception('OPTION_COMMENTS',tlinfo.OPTION_COMMENTS);
421 END IF;
422
423 pnp_debug_pkg.debug ('PN_OPTIONS_PKG.LOCK_ROW (-)');
424
425 END Lock_Row;
426
427 -------------------------------------------------------------------------------
428 -- PROCDURE : Update_Row
429 -- INVOKED FROM : Update_Row procedure
430 -- PURPOSE : updates the row
431 -- HISTORY :
432 -- 05-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_options with _ALL table.
433 -------------------------------------------------------------------------------
434 PROCEDURE Update_Row (
435 X_OPTION_ID IN NUMBER,
436 X_LEASE_ID IN NUMBER,
437 X_LEASE_CHANGE_ID IN NUMBER,
438 X_OPTION_NUM IN VARCHAR2,
439 X_OPTION_TYPE_CODE IN VARCHAR2,
440 X_START_DATE IN DATE,
441 X_EXPIRATION_DATE IN DATE,
442 X_OPTION_SIZE IN NUMBER,
443 X_UOM_CODE IN VARCHAR2,
444 X_OPTION_STATUS_LOOKUP_CODE IN VARCHAR2,
445 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
446 X_ATTRIBUTE1 IN VARCHAR2,
447 X_ATTRIBUTE2 IN VARCHAR2,
448 X_ATTRIBUTE3 IN VARCHAR2,
449 X_ATTRIBUTE4 IN VARCHAR2,
450 X_ATTRIBUTE5 IN VARCHAR2,
451 X_ATTRIBUTE6 IN VARCHAR2,
452 X_ATTRIBUTE7 IN VARCHAR2,
453 X_ATTRIBUTE8 IN VARCHAR2,
454 X_ATTRIBUTE9 IN VARCHAR2,
455 X_ATTRIBUTE10 IN VARCHAR2,
456 X_ATTRIBUTE11 IN VARCHAR2,
457 X_ATTRIBUTE12 IN VARCHAR2,
458 X_ATTRIBUTE13 IN VARCHAR2,
459 X_ATTRIBUTE14 IN VARCHAR2,
460 X_ATTRIBUTE15 IN VARCHAR2,
461 X_LAST_UPDATE_DATE IN DATE,
462 X_LAST_UPDATED_BY IN NUMBER,
463 X_LAST_UPDATE_LOGIN IN NUMBER,
464 X_OPTION_EXER_START_DATE IN DATE,
465 X_OPTION_EXER_END_DATE IN DATE,
466 X_OPTION_ACTION_DATE IN DATE,
467 X_OPTION_COST IN VARCHAR2,
468 X_OPTION_AREA_CHANGE IN NUMBER,
469 X_OPTION_REFERENCE IN VARCHAR2,
470 X_OPTION_NOTICE_REQD IN VARCHAR2,
471 X_OPTION_COMMENTS IN VARCHAR2
472 )
473 IS
474
475 CURSOR c2 IS
476 SELECT *
477 FROM pn_options_all
478 WHERE option_id = x_option_id;
479
480 recInfoForHist c2%ROWTYPE;
481 l_optionHistoryId NUMBER := NULL;
482 l_leaseStatus VARCHAR2(30) := NULL;
483
484 BEGIN
485 pnp_debug_pkg.debug ('PN_OPTIONS_PKG.UPDATE_ROW (+)');
486
487 ----------------------------------------------------
488 -- get the lease status
489 ----------------------------------------------------
490 l_leaseStatus := PNP_UTIL_FUNC.GET_LEASE_STATUS (X_LEASE_ID);
491
492 ---------------------------------------------------------------
493 -- We need to INSERT the history row IF the lease IS finalised
494 ---------------------------------------------------------------
495 IF (l_leaseStatus = 'F') THEN
496
497 OPEN c2;
498 FETCH c2 INTO recInfoForHist;
499 IF (c2%NOTFOUND) THEN
500 CLOSE c2;
501 RAISE NO_DATA_FOUND;
502 END IF;
503 CLOSE c2;
504
505 IF (recInfoForHist.LEASE_CHANGE_ID <> X_LEASE_CHANGE_ID) THEN
506
507 SELECT pn_options_history_s.NEXTVAL
508 INTO l_optionHistoryId
509 FROM DUAL;
510
511 INSERT INTO pn_options_history
512 (
513 option_history_id,
514 option_id,
515 last_update_date,
516 last_updated_by,
517 creation_date,
518 created_by,
519 last_update_login,
520 lease_id,
521 lease_change_id,
522 new_lease_change_id,
523 option_num,
524 option_type_code,
525 start_date,
526 expiration_date,
527 option_size,
528 uom_code,
529 option_status_lookup_code,
530 attribute_category,
531 attribute1,
532 attribute2,
533 attribute3,
534 attribute4,
535 attribute5,
536 attribute6,
537 attribute7,
538 attribute8,
539 attribute9,
540 attribute10,
541 attribute11,
542 attribute12,
543 attribute13,
544 attribute14,
545 attribute15,
546 option_exer_start_date,
547 option_exer_end_date,
548 option_action_date,
549 option_cost,
550 option_area_change,
551 option_reference,
552 option_notice_reqd,
553 option_comments
554 )
555 VALUES
556 (
557 l_optionHistoryId,
558 recInfoForHist.option_id,
559 recInfoForHist.last_update_date,
560 recInfoForHist.last_updated_by,
561 recInfoForHist.creation_date,
562 recInfoForHist.created_by,
563 recInfoForHist.last_update_login,
564 recInfoForHist.lease_id,
565 recInfoForHist.lease_change_id,
566 x_lease_change_id,
567 recInfoForHist.option_num,
568 recInfoForHist.option_type_code,
569 recInfoForHist.start_date,
570 recInfoForHist.expiration_date,
571 recInfoForHist.option_size,
572 recInfoForHist.uom_code,
573 recInfoForHist.option_status_lookup_code,
574 recInfoForHist.attribute_category,
575 recInfoForHist.attribute1,
576 recInfoForHist.attribute2,
577 recInfoForHist.attribute3,
578 recInfoForHist.attribute4,
579 recInfoForHist.attribute5,
580 recInfoForHist.attribute6,
581 recInfoForHist.attribute7,
582 recInfoForHist.attribute8,
583 recInfoForHist.attribute9,
584 recInfoForHist.attribute10,
585 recInfoForHist.attribute11,
586 recInfoForHist.attribute12,
587 recInfoForHist.attribute13,
588 recInfoForHist.attribute14,
589 recInfoForHist.attribute15,
590 recInfoForHist.option_exer_start_date,
591 recInfoForHist.option_exer_end_date,
592 recInfoForHist.option_action_date,
593 recInfoForHist.option_cost,
594 recInfoForHist.option_area_change,
595 recInfoForHist.option_reference,
596 recInfoForHist.option_notice_reqd,
597 recInfoForHist.option_comments
598 );
599 END IF;
600 END IF;
601
602 UPDATE pn_options_all
603 SET lease_id = x_lease_id,
604 lease_change_id = x_lease_change_id,
605 option_num = x_option_num,
606 option_type_code = x_option_type_code,
607 start_date = x_start_date,
608 expiration_date = x_expiration_date,
609 option_size = x_option_size,
610 uom_code = x_uom_code,
611 option_status_lookup_code = x_option_status_lookup_code,
612 attribute_category = x_attribute_category,
613 attribute1 = x_attribute1,
614 attribute2 = x_attribute2,
615 attribute3 = x_attribute3,
616 attribute4 = x_attribute4,
617 attribute5 = x_attribute5,
618 attribute6 = x_attribute6,
619 attribute7 = x_attribute7,
620 attribute8 = x_attribute8,
621 attribute9 = x_attribute9,
622 attribute10 = x_attribute10,
623 attribute11 = x_attribute11,
624 attribute12 = x_attribute12,
625 attribute13 = x_attribute13,
626 attribute14 = x_attribute14,
627 attribute15 = x_attribute15,
628 option_id = x_option_id,
629 last_update_date = x_last_update_date,
630 last_updated_by = x_last_updated_by,
631 last_update_login = x_last_update_login,
632 option_exer_start_date = x_option_exer_start_date,
633 option_exer_end_date = x_option_exer_end_date,
634 option_action_date = x_option_action_date,
635 option_cost = x_option_cost,
636 option_area_change = x_option_area_change,
637 option_reference = x_option_reference,
638 option_notice_reqd = x_option_notice_reqd,
639 option_comments = x_option_comments
640 WHERE option_id = x_option_id ;
641
642 IF (SQL%NOTFOUND) THEN
643 RAISE NO_DATA_FOUND;
644 END IF;
645
646 pnp_debug_pkg.debug ('PN_OPTIONS_PKG.UPDATE_ROW (-)');
647
648 END Update_Row;
649
650 -------------------------------------------------------------------------------
651 -- PROCDURE : Delete_Row
652 -- INVOKED FROM : Delete_Row procedure
653 -- PURPOSE : deletes the row
654 -- HISTORY :
655 -- 05-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_options with _ALL table.
656 -------------------------------------------------------------------------------
657 PROCEDURE Delete_Row
658 (
659 x_option_id IN NUMBER
660 )
661 IS
662 BEGIN
663
664 pnp_debug_pkg.debug ('PN_OPTIONS_PKG.DELETE_ROW (+)');
665
666 DELETE FROM pn_options_all
667 WHERE option_id = x_option_id;
668
669 IF (SQL%NOTFOUND) THEN
670 RAISE NO_DATA_FOUND;
671 END IF;
672
673 pnp_debug_pkg.debug ('PN_OPTIONS_PKG.DELETE_ROW (-)');
674
675 END Delete_Row;
676
677 END pn_options_pkg;
[Home] [Help]