[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_INTVW_PNLS_PKG
Source
1 PACKAGE BODY igs_ad_intvw_pnls_pkg AS
2 /* $Header: IGSAIG9B.pls 115.2 2003/06/23 06:40:04 akadam noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_AD_INTVW_PNLS%ROWTYPE;
6 new_references IGS_AD_INTVW_PNLS%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_panel_code IN VARCHAR2,
12 x_panel_type_code IN VARCHAR2,
13 x_panel_level_code IN VARCHAR2,
14 x_panel_desc IN VARCHAR2,
15 x_closed_flag IN VARCHAR2,
16 x_attribute_category IN VARCHAR2,
17 x_attribute1 IN VARCHAR2,
18 x_attribute2 IN VARCHAR2,
19 x_attribute3 IN VARCHAR2,
20 x_attribute4 IN VARCHAR2,
21 x_attribute5 IN VARCHAR2,
22 x_attribute6 IN VARCHAR2,
23 x_attribute7 IN VARCHAR2,
24 x_attribute8 IN VARCHAR2,
25 x_attribute9 IN VARCHAR2,
26 x_attribute10 IN VARCHAR2,
27 x_attribute11 IN VARCHAR2,
28 x_attribute12 IN VARCHAR2,
29 x_attribute13 IN VARCHAR2,
30 x_attribute14 IN VARCHAR2,
31 x_attribute15 IN VARCHAR2,
32 x_attribute16 IN VARCHAR2,
33 x_attribute17 IN VARCHAR2,
34 x_attribute18 IN VARCHAR2,
35 x_attribute19 IN VARCHAR2,
36 x_attribute20 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 ) AS
43 /*
44 || Created By : Amit Kadam
45 || Created On : 13-JUN-2003
46 || Purpose : Initialises the Old and New references for the columns of the table.
47 || Known limitations, enhancements or remarks :
48 || Change History :
49 || Who When What
50 || (reverse chronological order - newest change first)
51 */
52
53 CURSOR cur_old_ref_values IS
54 SELECT *
55 FROM igs_ad_intvw_pnls
56 WHERE rowid = x_rowid;
57
58 BEGIN
59
60 l_rowid := x_rowid;
61
62 -- Code for setting the Old and New Reference Values.
63 -- Populate Old Values.
64 OPEN cur_old_ref_values;
65 FETCH cur_old_ref_values INTO old_references;
66 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
67 CLOSE cur_old_ref_values;
68 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
69 igs_ge_msg_stack.add;
70 app_exception.raise_exception;
71 RETURN;
72 END IF;
73 CLOSE cur_old_ref_values;
74
75 -- Populate New Values.
76 new_references.panel_code := x_panel_code;
77 new_references.panel_type_code := x_panel_type_code;
78 new_references.panel_level_code := x_panel_level_code;
79 new_references.panel_desc := x_panel_desc;
80 new_references.closed_flag := x_closed_flag;
81 new_references.attribute_category := x_attribute_category;
82 new_references.attribute1 := x_attribute1;
83 new_references.attribute2 := x_attribute2;
84 new_references.attribute3 := x_attribute3;
85 new_references.attribute4 := x_attribute4;
86 new_references.attribute5 := x_attribute5;
87 new_references.attribute6 := x_attribute6;
88 new_references.attribute7 := x_attribute7;
89 new_references.attribute8 := x_attribute8;
90 new_references.attribute9 := x_attribute9;
91 new_references.attribute10 := x_attribute10;
92 new_references.attribute11 := x_attribute11;
93 new_references.attribute12 := x_attribute12;
94 new_references.attribute13 := x_attribute13;
95 new_references.attribute14 := x_attribute14;
96 new_references.attribute15 := x_attribute15;
97 new_references.attribute16 := x_attribute16;
98 new_references.attribute17 := x_attribute17;
99 new_references.attribute18 := x_attribute18;
100 new_references.attribute19 := x_attribute19;
101 new_references.attribute20 := x_attribute20;
102
103 IF (p_action = 'UPDATE') THEN
104 new_references.creation_date := old_references.creation_date;
105 new_references.created_by := old_references.created_by;
106 ELSE
107 new_references.creation_date := x_creation_date;
108 new_references.created_by := x_created_by;
109 END IF;
110
111 new_references.last_update_date := x_last_update_date;
112 new_references.last_updated_by := x_last_updated_by;
113 new_references.last_update_login := x_last_update_login;
114
115 END set_column_values;
116
117
118 PROCEDURE check_child_existance AS
119 /*
120 || Created By : Amit Kadam
121 || Created On : 13-JUN-2003
122 || Purpose : Checks for the existance of Child records.
123 || Known limitations, enhancements or remarks :
124 || Change History :
125 || Who When What
126 || (reverse chronological order - newest change first)
127 */
128 BEGIN
129
130 igs_ad_panel_dtls_pkg.get_fk_igs_ad_intvw_pnls (
131 old_references.panel_code
132 );
133
134 igs_ad_panel_membrs_pkg.get_fk_igs_ad_intvw_pnls (
135 old_references.panel_code
136 );
137
138 END check_child_existance;
139
140
141 FUNCTION get_pk_for_validation (
142 x_panel_code IN VARCHAR2
143 ) RETURN BOOLEAN AS
144 /*
145 || Created By : Amit Kadam
146 || Created On : 13-JUN-2003
147 || Purpose : Validates the Primary Key of the table.
148 || Known limitations, enhancements or remarks :
149 || Change History :
150 || Who When What
151 || (reverse chronological order - newest change first)
152 */
153 CURSOR cur_rowid IS
154 SELECT rowid
155 FROM igs_ad_intvw_pnls
156 WHERE panel_code = x_panel_code
157 FOR UPDATE NOWAIT;
158
159 lv_rowid cur_rowid%RowType;
160
161 BEGIN
162
163 OPEN cur_rowid;
164 FETCH cur_rowid INTO lv_rowid;
165 IF (cur_rowid%FOUND) THEN
166 CLOSE cur_rowid;
167 RETURN(TRUE);
168 ELSE
169 CLOSE cur_rowid;
170 RETURN(FALSE);
171 END IF;
172
173 END get_pk_for_validation;
174
175
176 PROCEDURE before_dml (
177 p_action IN VARCHAR2,
178 x_rowid IN VARCHAR2,
179 x_panel_code IN VARCHAR2,
180 x_panel_type_code IN VARCHAR2,
181 x_panel_level_code IN VARCHAR2,
182 x_panel_desc IN VARCHAR2,
183 x_closed_flag IN VARCHAR2,
184 x_attribute_category IN VARCHAR2,
185 x_attribute1 IN VARCHAR2,
186 x_attribute2 IN VARCHAR2,
187 x_attribute3 IN VARCHAR2,
188 x_attribute4 IN VARCHAR2,
189 x_attribute5 IN VARCHAR2,
190 x_attribute6 IN VARCHAR2,
191 x_attribute7 IN VARCHAR2,
192 x_attribute8 IN VARCHAR2,
193 x_attribute9 IN VARCHAR2,
194 x_attribute10 IN VARCHAR2,
195 x_attribute11 IN VARCHAR2,
196 x_attribute12 IN VARCHAR2,
197 x_attribute13 IN VARCHAR2,
198 x_attribute14 IN VARCHAR2,
199 x_attribute15 IN VARCHAR2,
200 x_attribute16 IN VARCHAR2,
201 x_attribute17 IN VARCHAR2,
202 x_attribute18 IN VARCHAR2,
203 x_attribute19 IN VARCHAR2,
204 x_attribute20 IN VARCHAR2,
205 x_creation_date IN DATE,
206 x_created_by IN NUMBER,
207 x_last_update_date IN DATE,
208 x_last_updated_by IN NUMBER,
209 x_last_update_login IN NUMBER
210 ) AS
211 /*
212 || Created By : Amit Kadam
213 || Created On : 13-JUN-2003
214 || Purpose : Initialises the columns, Checks Constraints, Calls the
215 || Trigger Handlers for the table, before any DML operation.
216 || Known limitations, enhancements or remarks :
217 || Change History :
218 || Who When What
219 || (reverse chronological order - newest change first)
220 */
221 BEGIN
222
223 set_column_values (
224 p_action,
225 x_rowid,
226 x_panel_code,
227 x_panel_type_code,
228 x_panel_level_code,
229 x_panel_desc ,
230 x_closed_flag,
231 x_attribute_category,
232 x_attribute1,
233 x_attribute2,
234 x_attribute3,
235 x_attribute4,
236 x_attribute5,
237 x_attribute6,
238 x_attribute7,
239 x_attribute8,
240 x_attribute9,
241 x_attribute10,
242 x_attribute11,
243 x_attribute12,
244 x_attribute13,
245 x_attribute14,
246 x_attribute15,
247 x_attribute16,
248 x_attribute17,
249 x_attribute18,
250 x_attribute19,
251 x_attribute20,
252 x_creation_date,
253 x_created_by,
254 x_last_update_date,
255 x_last_updated_by,
256 x_last_update_login
257 );
258
259 IF (p_action = 'INSERT') THEN
260 -- Call all the procedures related to Before Insert.
261 IF ( get_pk_for_validation(
262 new_references.panel_code
263 )
264 ) THEN
265 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
266 igs_ge_msg_stack.add;
267 app_exception.raise_exception;
268 END IF;
269 ELSIF (p_action = 'DELETE') THEN
270 -- Call all the procedures related to Before Delete.
271 check_child_existance;
272 ELSIF (p_action = 'VALIDATE_INSERT') THEN
273 -- Call all the procedures related to Before Insert.
274 IF ( get_pk_for_validation (
275 new_references.panel_code
276 )
277 ) THEN
278 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
279 igs_ge_msg_stack.add;
280 app_exception.raise_exception;
281 END IF;
282 ELSIF (p_action = 'VALIDATE_DELETE') THEN
283 check_child_existance;
284 END IF;
285
286 END before_dml;
287
288
289 PROCEDURE insert_row (
290 x_rowid IN OUT NOCOPY VARCHAR2,
291 x_panel_code IN VARCHAR2,
292 x_panel_type_code IN VARCHAR2,
293 x_panel_level_code IN VARCHAR2,
294 x_panel_desc IN VARCHAR2,
295 x_closed_flag IN VARCHAR2,
296 x_attribute_category IN VARCHAR2,
297 x_attribute1 IN VARCHAR2,
298 x_attribute2 IN VARCHAR2,
299 x_attribute3 IN VARCHAR2,
300 x_attribute4 IN VARCHAR2,
301 x_attribute5 IN VARCHAR2,
302 x_attribute6 IN VARCHAR2,
303 x_attribute7 IN VARCHAR2,
304 x_attribute8 IN VARCHAR2,
305 x_attribute9 IN VARCHAR2,
306 x_attribute10 IN VARCHAR2,
307 x_attribute11 IN VARCHAR2,
308 x_attribute12 IN VARCHAR2,
309 x_attribute13 IN VARCHAR2,
310 x_attribute14 IN VARCHAR2,
311 x_attribute15 IN VARCHAR2,
312 x_attribute16 IN VARCHAR2,
313 x_attribute17 IN VARCHAR2,
314 x_attribute18 IN VARCHAR2,
315 x_attribute19 IN VARCHAR2,
316 x_attribute20 IN VARCHAR2,
317 x_mode IN VARCHAR2
318 ) AS
319 /*
320 || Created By : Amit Kadam
321 || Created On : 13-JUN-2003
322 || Purpose : Handles the INSERT DML logic for the table.
323 || Known limitations, enhancements or remarks :
324 || Change History :
325 || Who When What
326 || (reverse chronological order - newest change first)
327 */
328
329 x_last_update_date DATE;
330 x_last_updated_by NUMBER;
331 x_last_update_login NUMBER;
332
333 BEGIN
334
335 x_last_update_date := SYSDATE;
336 IF (x_mode = 'I') THEN
337 x_last_updated_by := 1;
338 x_last_update_login := 0;
339 ELSIF (x_mode = 'R') THEN
340 x_last_updated_by := fnd_global.user_id;
341 IF (x_last_updated_by IS NULL) THEN
342 x_last_updated_by := -1;
343 END IF;
344 x_last_update_login := fnd_global.login_id;
345 IF (x_last_update_login IS NULL) THEN
346 x_last_update_login := -1;
347 END IF;
348 ELSE
349 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
350 fnd_message.set_token ('ROUTINE', 'IGS_AD_INTVW_PNLS_PKG.INSERT_ROW');
351 igs_ge_msg_stack.add;
352 app_exception.raise_exception;
353 END IF;
354
355 before_dml(
356 p_action => 'INSERT',
357 x_rowid => x_rowid,
358 x_panel_code => x_panel_code,
359 x_panel_type_code => x_panel_type_code,
360 x_panel_level_code => x_panel_level_code,
361 x_panel_desc => x_panel_desc,
362 x_closed_flag => x_closed_flag,
363 x_attribute_category => x_attribute_category,
364 x_attribute1 => x_attribute1,
365 x_attribute2 => x_attribute2,
366 x_attribute3 => x_attribute3,
367 x_attribute4 => x_attribute4,
368 x_attribute5 => x_attribute5,
369 x_attribute6 => x_attribute6,
370 x_attribute7 => x_attribute7,
371 x_attribute8 => x_attribute8,
372 x_attribute9 => x_attribute9,
373 x_attribute10 => x_attribute10,
374 x_attribute11 => x_attribute11,
375 x_attribute12 => x_attribute12,
376 x_attribute13 => x_attribute13,
377 x_attribute14 => x_attribute14,
378 x_attribute15 => x_attribute15,
379 x_attribute16 => x_attribute16,
380 x_attribute17 => x_attribute17,
381 x_attribute18 => x_attribute18,
382 x_attribute19 => x_attribute19,
383 x_attribute20 => x_attribute20,
384 x_creation_date => x_last_update_date,
385 x_created_by => x_last_updated_by,
386 x_last_update_date => x_last_update_date,
387 x_last_updated_by => x_last_updated_by,
388 x_last_update_login => x_last_update_login
389 );
390
391 INSERT INTO igs_ad_intvw_pnls (
392 panel_code,
393 panel_type_code,
394 panel_level_code,
395 panel_desc ,
396 closed_flag,
397 attribute_category,
398 attribute1,
399 attribute2,
400 attribute3,
401 attribute4,
402 attribute5,
403 attribute6,
404 attribute7,
405 attribute8,
406 attribute9,
407 attribute10,
408 attribute11,
409 attribute12,
410 attribute13,
411 attribute14,
412 attribute15,
413 attribute16,
414 attribute17,
415 attribute18,
416 attribute19,
417 attribute20,
418 creation_date,
419 created_by,
420 last_update_date,
421 last_updated_by,
422 last_update_login
423 ) VALUES (
424 new_references.panel_code,
425 new_references.panel_type_code,
426 new_references.panel_level_code,
427 new_references.panel_desc ,
428 new_references.closed_flag,
429 new_references.attribute_category,
430 new_references.attribute1,
431 new_references.attribute2,
432 new_references.attribute3,
433 new_references.attribute4,
434 new_references.attribute5,
435 new_references.attribute6,
436 new_references.attribute7,
437 new_references.attribute8,
438 new_references.attribute9,
439 new_references.attribute10,
440 new_references.attribute11,
441 new_references.attribute12,
442 new_references.attribute13,
443 new_references.attribute14,
444 new_references.attribute15,
445 new_references.attribute16,
446 new_references.attribute17,
447 new_references.attribute18,
448 new_references.attribute19,
449 new_references.attribute20,
450 x_last_update_date,
451 x_last_updated_by,
452 x_last_update_date,
453 x_last_updated_by,
454 x_last_update_login
455 ) RETURNING ROWID INTO x_rowid;
456
457 END insert_row;
458
459
460 PROCEDURE lock_row (
461 x_rowid IN VARCHAR2,
462 x_panel_code IN VARCHAR2,
463 x_panel_type_code IN VARCHAR2,
464 x_panel_level_code IN VARCHAR2,
465 x_panel_desc IN VARCHAR2,
466 x_closed_flag IN VARCHAR2,
467 x_attribute_category IN VARCHAR2,
468 x_attribute1 IN VARCHAR2,
469 x_attribute2 IN VARCHAR2,
470 x_attribute3 IN VARCHAR2,
471 x_attribute4 IN VARCHAR2,
472 x_attribute5 IN VARCHAR2,
473 x_attribute6 IN VARCHAR2,
474 x_attribute7 IN VARCHAR2,
475 x_attribute8 IN VARCHAR2,
476 x_attribute9 IN VARCHAR2,
477 x_attribute10 IN VARCHAR2,
478 x_attribute11 IN VARCHAR2,
479 x_attribute12 IN VARCHAR2,
480 x_attribute13 IN VARCHAR2,
481 x_attribute14 IN VARCHAR2,
482 x_attribute15 IN VARCHAR2,
483 x_attribute16 IN VARCHAR2,
484 x_attribute17 IN VARCHAR2,
485 x_attribute18 IN VARCHAR2,
486 x_attribute19 IN VARCHAR2,
487 x_attribute20 IN VARCHAR2
488 ) AS
489 /*
490 || Created By : Amit Kadam
491 || Created On : 13-JUN-2003
492 || Purpose : Handles the LOCK mechanism for the table.
493 || Known limitations, enhancements or remarks :
494 || Change History :
495 || Who When What
496 || (reverse chronological order - newest change first)
497 */
498 CURSOR c1 IS
499 SELECT
500 panel_type_code,
501 panel_level_code,
502 panel_desc ,
503 closed_flag,
504 attribute_category,
505 attribute1,
506 attribute2,
507 attribute3,
508 attribute4,
509 attribute5,
510 attribute6,
511 attribute7,
512 attribute8,
513 attribute9,
514 attribute10,
515 attribute11,
516 attribute12,
517 attribute13,
518 attribute14,
519 attribute15,
520 attribute16,
521 attribute17,
522 attribute18,
523 attribute19,
524 attribute20
525 FROM igs_ad_intvw_pnls
526 WHERE rowid = x_rowid
527 FOR UPDATE NOWAIT;
528
529 tlinfo c1%ROWTYPE;
530
531 BEGIN
532
533 OPEN c1;
534 FETCH c1 INTO tlinfo;
535 IF (c1%notfound) THEN
536 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
537 igs_ge_msg_stack.add;
538 CLOSE c1;
539 app_exception.raise_exception;
540 RETURN;
541 END IF;
542 CLOSE c1;
543
544 IF (
545 (tlinfo.panel_type_code = x_panel_type_code)
546 AND ((tlinfo.panel_level_code = x_panel_level_code) OR ((tlinfo.panel_level_code IS NULL) AND (X_panel_level_code IS NULL)))
547 AND (tlinfo.panel_desc = x_panel_desc )
548 AND (tlinfo.closed_flag = x_closed_flag)
549 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
550 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
551 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
552 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
553 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
557 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
554 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
555 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
556 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
558 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
559 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
560 AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (X_attribute11 IS NULL)))
561 AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (X_attribute12 IS NULL)))
562 AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (X_attribute13 IS NULL)))
563 AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (X_attribute14 IS NULL)))
564 AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (X_attribute15 IS NULL)))
565 AND ((tlinfo.attribute16 = x_attribute16) OR ((tlinfo.attribute16 IS NULL) AND (X_attribute16 IS NULL)))
566 AND ((tlinfo.attribute17 = x_attribute17) OR ((tlinfo.attribute17 IS NULL) AND (X_attribute17 IS NULL)))
567 AND ((tlinfo.attribute18 = x_attribute18) OR ((tlinfo.attribute18 IS NULL) AND (X_attribute18 IS NULL)))
568 AND ((tlinfo.attribute19 = x_attribute19) OR ((tlinfo.attribute19 IS NULL) AND (X_attribute19 IS NULL)))
569 AND ((tlinfo.attribute20 = x_attribute20) OR ((tlinfo.attribute20 IS NULL) AND (X_attribute20 IS NULL)))
570 ) THEN
571 NULL;
572 ELSE
573 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
574 igs_ge_msg_stack.add;
575 app_exception.raise_exception;
576 END IF;
577
578 RETURN;
579
580 END lock_row;
581
582
583 PROCEDURE update_row (
584 x_rowid IN VARCHAR2,
585 x_panel_code IN VARCHAR2,
586 x_panel_type_code IN VARCHAR2,
587 x_panel_level_code IN VARCHAR2,
588 x_panel_desc IN VARCHAR2,
589 x_closed_flag IN VARCHAR2,
590 x_attribute_category IN VARCHAR2,
591 x_attribute1 IN VARCHAR2,
592 x_attribute2 IN VARCHAR2,
593 x_attribute3 IN VARCHAR2,
594 x_attribute4 IN VARCHAR2,
595 x_attribute5 IN VARCHAR2,
596 x_attribute6 IN VARCHAR2,
597 x_attribute7 IN VARCHAR2,
598 x_attribute8 IN VARCHAR2,
599 x_attribute9 IN VARCHAR2,
600 x_attribute10 IN VARCHAR2,
601 x_attribute11 IN VARCHAR2,
602 x_attribute12 IN VARCHAR2,
603 x_attribute13 IN VARCHAR2,
604 x_attribute14 IN VARCHAR2,
605 x_attribute15 IN VARCHAR2,
606 x_attribute16 IN VARCHAR2,
607 x_attribute17 IN VARCHAR2,
608 x_attribute18 IN VARCHAR2,
609 x_attribute19 IN VARCHAR2,
610 x_attribute20 IN VARCHAR2,
611 x_mode IN VARCHAR2
612 ) AS
613 /*
614 || Created By : Amit Kadam
615 || Created On : 13-JUN-2003
616 || Purpose : Handles the UPDATE DML logic for the table.
617 || Known limitations, enhancements or remarks :
618 || Change History :
619 || Who When What
620 || (reverse chronological order - newest change first)
621 */
622 x_last_update_date DATE ;
623 x_last_updated_by NUMBER;
624 x_last_update_login NUMBER;
625
626 BEGIN
627
628 x_last_update_date := SYSDATE;
629 IF (X_MODE = 'I') THEN
630 x_last_updated_by := 1;
631 x_last_update_login := 0;
632 ELSIF (x_mode = 'R') THEN
633 x_last_updated_by := fnd_global.user_id;
634 IF x_last_updated_by IS NULL THEN
635 x_last_updated_by := -1;
636 END IF;
637 x_last_update_login := fnd_global.login_id;
638 IF (x_last_update_login IS NULL) THEN
639 x_last_update_login := -1;
640 END IF;
641 ELSE
642 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
643 fnd_message.set_token ('ROUTINE', 'IGS_AD_INTVW_PNLS_PKG.UPDATE_ROW');
644 igs_ge_msg_stack.add;
645 app_exception.raise_exception;
646 END IF;
647
648 before_dml(
649 p_action => 'UPDATE',
650 x_rowid => x_rowid,
651 x_panel_code => x_panel_code,
652 x_panel_type_code => x_panel_type_code,
653 x_panel_level_code => x_panel_level_code,
654 x_panel_desc => x_panel_desc ,
655 x_closed_flag => x_closed_flag,
656 x_attribute_category => x_attribute_category,
657 x_attribute1 => x_attribute1,
658 x_attribute2 => x_attribute2,
659 x_attribute3 => x_attribute3,
660 x_attribute4 => x_attribute4,
661 x_attribute5 => x_attribute5,
662 x_attribute6 => x_attribute6,
663 x_attribute7 => x_attribute7,
664 x_attribute8 => x_attribute8,
665 x_attribute9 => x_attribute9,
666 x_attribute10 => x_attribute10,
667 x_attribute11 => x_attribute11,
668 x_attribute12 => x_attribute12,
669 x_attribute13 => x_attribute13,
670 x_attribute14 => x_attribute14,
671 x_attribute15 => x_attribute15,
672 x_attribute16 => x_attribute16,
673 x_attribute17 => x_attribute17,
674 x_attribute18 => x_attribute18,
675 x_attribute19 => x_attribute19,
676 x_attribute20 => x_attribute20,
677 x_creation_date => x_last_update_date,
678 x_created_by => x_last_updated_by,
679 x_last_update_date => x_last_update_date,
680 x_last_updated_by => x_last_updated_by,
681 x_last_update_login => x_last_update_login
682 );
683
684 UPDATE igs_ad_intvw_pnls
685 SET
686 panel_type_code = new_references.panel_type_code,
687 panel_level_code = new_references.panel_level_code,
688 panel_desc = new_references.panel_desc ,
689 closed_flag = new_references.closed_flag,
690 attribute_category = new_references.attribute_category,
691 attribute1 = new_references.attribute1,
692 attribute2 = new_references.attribute2,
693 attribute3 = new_references.attribute3,
694 attribute4 = new_references.attribute4,
695 attribute5 = new_references.attribute5,
696 attribute6 = new_references.attribute6,
697 attribute7 = new_references.attribute7,
698 attribute8 = new_references.attribute8,
699 attribute9 = new_references.attribute9,
700 attribute10 = new_references.attribute10,
701 attribute11 = new_references.attribute11,
702 attribute12 = new_references.attribute12,
703 attribute13 = new_references.attribute13,
704 attribute14 = new_references.attribute14,
705 attribute15 = new_references.attribute15,
706 attribute16 = new_references.attribute16,
707 attribute17 = new_references.attribute17,
708 attribute18 = new_references.attribute18,
709 attribute19 = new_references.attribute19,
710 attribute20 = new_references.attribute20,
711 last_update_date = x_last_update_date,
712 last_updated_by = x_last_updated_by,
713 last_update_login = x_last_update_login
714 WHERE rowid = x_rowid;
715
716 IF (SQL%NOTFOUND) THEN
717 RAISE NO_DATA_FOUND;
718 END IF;
719
720 END update_row;
721
722
723 PROCEDURE add_row (
724 x_rowid IN OUT NOCOPY VARCHAR2,
725 x_panel_code IN VARCHAR2,
726 x_panel_type_code IN VARCHAR2,
727 x_panel_level_code IN VARCHAR2,
728 x_panel_desc IN VARCHAR2,
729 x_closed_flag IN VARCHAR2,
730 x_attribute_category IN VARCHAR2,
731 x_attribute1 IN VARCHAR2,
732 x_attribute2 IN VARCHAR2,
733 x_attribute3 IN VARCHAR2,
734 x_attribute4 IN VARCHAR2,
735 x_attribute5 IN VARCHAR2,
736 x_attribute6 IN VARCHAR2,
737 x_attribute7 IN VARCHAR2,
738 x_attribute8 IN VARCHAR2,
739 x_attribute9 IN VARCHAR2,
740 x_attribute10 IN VARCHAR2,
741 x_attribute11 IN VARCHAR2,
742 x_attribute12 IN VARCHAR2,
743 x_attribute13 IN VARCHAR2,
744 x_attribute14 IN VARCHAR2,
745 x_attribute15 IN VARCHAR2,
746 x_attribute16 IN VARCHAR2,
747 x_attribute17 IN VARCHAR2,
748 x_attribute18 IN VARCHAR2,
749 x_attribute19 IN VARCHAR2,
750 x_attribute20 IN VARCHAR2,
751 x_mode IN VARCHAR2
752 ) AS
753 /*
754 || Created By : Amit Kadam
755 || Created On : 13-JUN-2003
756 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
757 || Known limitations, enhancements or remarks :
758 || Change History :
759 || Who When What
760 || (reverse chronological order - newest change first)
761 */
762 CURSOR c1 IS
763 SELECT rowid
764 FROM igs_ad_intvw_pnls
765 WHERE panel_code = x_panel_code;
766
767 BEGIN
768
769 OPEN c1;
770 FETCH c1 INTO x_rowid;
771 IF (c1%NOTFOUND) THEN
772 CLOSE c1;
773
774 insert_row (
775 x_rowid,
776 x_panel_code,
777 x_panel_type_code,
778 x_panel_level_code,
779 x_panel_desc ,
780 x_closed_flag,
781 x_attribute_category,
782 x_attribute1,
783 x_attribute2,
784 x_attribute3,
785 x_attribute4,
786 x_attribute5,
787 x_attribute6,
788 x_attribute7,
789 x_attribute8,
790 x_attribute9,
791 x_attribute10,
792 x_attribute11,
793 x_attribute12,
794 x_attribute13,
795 x_attribute14,
796 x_attribute15,
797 x_attribute16,
798 x_attribute17,
799 x_attribute18,
800 x_attribute19,
801 x_attribute20,
802 x_mode
803 );
804 RETURN;
805 END IF;
806 CLOSE c1;
807
808 update_row (
809 x_rowid,
810 x_panel_code,
811 x_panel_type_code,
812 x_panel_level_code,
813 x_panel_desc ,
814 x_closed_flag,
815 x_attribute_category,
816 x_attribute1,
817 x_attribute2,
818 x_attribute3,
819 x_attribute4,
820 x_attribute5,
821 x_attribute6,
822 x_attribute7,
823 x_attribute8,
824 x_attribute9,
825 x_attribute10,
826 x_attribute11,
827 x_attribute12,
828 x_attribute13,
829 x_attribute14,
830 x_attribute15,
831 x_attribute16,
832 x_attribute17,
833 x_attribute18,
834 x_attribute19,
835 x_attribute20,
836 x_mode
837 );
838
839 END add_row;
840
841
842 PROCEDURE delete_row (
843 x_rowid IN VARCHAR2
844 ) AS
845 /*
846 || Created By : Amit Kadam
847 || Created On : 13-JUN-2003
848 || Purpose : Handles the DELETE DML logic for the table.
849 || Known limitations, enhancements or remarks :
850 || Change History :
851 || Who When What
852 || (reverse chronological order - newest change first)
853 */
854 BEGIN
855
856 before_dml (
857 p_action => 'DELETE',
858 x_rowid => x_rowid
859 );
860
861 DELETE FROM igs_ad_intvw_pnls
862 WHERE rowid = x_rowid;
863
864 IF (SQL%NOTFOUND) THEN
865 RAISE NO_DATA_FOUND;
866 END IF;
867
868 END delete_row;
869
870
871 END igs_ad_intvw_pnls_pkg;