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