[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_VISA_PKG
Source
1 PACKAGE BODY igs_pe_visa_pkg AS
2 /* $Header: IGSNI40B.pls 120.3 2005/10/17 02:20:25 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pe_visa%ROWTYPE;
6 new_references igs_pe_visa%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_visa_id IN NUMBER,
12 x_person_id IN NUMBER,
13 x_visa_type IN VARCHAR2,
14 x_visa_number IN VARCHAR2,
15 x_visa_issue_date IN DATE,
16 x_visa_expiry_date IN DATE,
17 x_visa_category IN VARCHAR2,
18 x_visa_issuing_post IN VARCHAR2,
19 x_passport_id IN NUMBER,
20 x_agent_org_unit_cd IN VARCHAR2,
21 x_agent_person_id IN NUMBER,
22 x_agent_contact_name IN VARCHAR2,
23 x_attribute_category IN VARCHAR2,
24 x_attribute1 IN VARCHAR2,
25 x_attribute2 IN VARCHAR2,
26 x_attribute3 IN VARCHAR2,
27 x_attribute4 IN VARCHAR2,
28 x_attribute5 IN VARCHAR2,
29 x_attribute6 IN VARCHAR2,
30 x_attribute7 IN VARCHAR2,
31 x_attribute8 IN VARCHAR2,
32 x_attribute9 IN VARCHAR2,
33 x_attribute10 IN VARCHAR2,
34 x_attribute11 IN VARCHAR2,
35 x_attribute12 IN VARCHAR2,
36 x_attribute13 IN VARCHAR2,
37 x_attribute14 IN VARCHAR2,
38 x_attribute15 IN VARCHAR2,
39 x_attribute16 IN VARCHAR2,
40 x_attribute17 IN VARCHAR2,
41 x_attribute18 IN VARCHAR2,
42 x_attribute19 IN VARCHAR2,
43 x_attribute20 IN VARCHAR2,
44 x_visa_issuing_country IN VARCHAR2,
45 x_creation_date IN DATE,
46 x_created_by IN NUMBER,
47 x_last_update_date IN DATE,
48 x_last_updated_by IN NUMBER,
49 x_last_update_login IN NUMBER
50 ) AS
51 /*
52 || Created By : [email protected]
53 || Created On : 28-NOV-2002
54 || Purpose : Initialises the Old and New references for the columns of the table.
55 || Known limitations, enhancements or remarks :
56 || Change History :
57 || Who When What
58 || (reverse chronological order - newest change first)
59 */
60
61 CURSOR cur_old_ref_values IS
62 SELECT *
63 FROM igs_pe_visa
64 WHERE rowid = x_rowid;
65
66 BEGIN
67
68 l_rowid := x_rowid;
69
70 -- Code for setting the Old and New Reference Values.
71 -- Populate Old Values.
72 OPEN cur_old_ref_values;
73 FETCH cur_old_ref_values INTO old_references;
74 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
75 CLOSE cur_old_ref_values;
76 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
77 igs_ge_msg_stack.add;
78 app_exception.raise_exception;
79 RETURN;
80 END IF;
81 CLOSE cur_old_ref_values;
82
83 -- Populate New Values.
84 new_references.visa_id := x_visa_id;
85 new_references.person_id := x_person_id;
86 new_references.visa_type := x_visa_type;
87 new_references.visa_number := x_visa_number;
88 new_references.visa_issue_date := x_visa_issue_date;
89 new_references.visa_expiry_date := x_visa_expiry_date;
90 new_references.visa_category := x_visa_category;
91 new_references.visa_issuing_post := x_visa_issuing_post;
92 new_references.passport_id := x_passport_id;
93 new_references.agent_org_unit_cd := x_agent_org_unit_cd;
94 new_references.agent_person_id := x_agent_person_id;
95 new_references.agent_contact_name := x_agent_contact_name;
96 new_references.attribute_category := x_attribute_category;
97 new_references.attribute1 := x_attribute1;
98 new_references.attribute2 := x_attribute2;
99 new_references.attribute3 := x_attribute3;
100 new_references.attribute4 := x_attribute4;
101 new_references.attribute5 := x_attribute5;
102 new_references.attribute6 := x_attribute6;
103 new_references.attribute7 := x_attribute7;
104 new_references.attribute8 := x_attribute8;
105 new_references.attribute9 := x_attribute9;
106 new_references.attribute10 := x_attribute10;
107 new_references.attribute11 := x_attribute11;
108 new_references.attribute12 := x_attribute12;
109 new_references.attribute13 := x_attribute13;
110 new_references.attribute14 := x_attribute14;
111 new_references.attribute15 := x_attribute15;
112 new_references.attribute16 := x_attribute16;
113 new_references.attribute17 := x_attribute17;
114 new_references.attribute18 := x_attribute18;
115 new_references.attribute19 := x_attribute19;
116 new_references.attribute20 := x_attribute20;
117 new_references.visa_issuing_country := x_visa_issuing_country;
118
119 IF (p_action = 'UPDATE') THEN
120 new_references.creation_date := old_references.creation_date;
121 new_references.created_by := old_references.created_by;
122 ELSE
123 new_references.creation_date := x_creation_date;
124 new_references.created_by := x_created_by;
125 END IF;
126
127 new_references.last_update_date := x_last_update_date;
128 new_references.last_updated_by := x_last_updated_by;
129 new_references.last_update_login := x_last_update_login;
130
131 END set_column_values;
132
133 PROCEDURE beforerowinsertupdate(p_inserting BOOLEAN,p_updating BOOLEAN) AS
134 /*
135 || Created By : Npalanis
136 || Created On : 5-MAR-2003
137 || Purpose : Handles the Unique Constraint logic defined for the columns.
138 || Known limitations, enhancements or remarks :
139 || Change History :
140 || Who When What
141 || pkpatel 13-Jun-2005 Added the validations for DOB, Dates and the POE Overlap
142 || (reverse chronological order - newest change first)
143 */
144 CURSOR get_pass_exp_dt(p_passport_id igs_pe_passport.passport_id%TYPE)
145 IS
146 SELECT passport_expiry_date
147 FROM IGS_PE_PASSPORT
148 WHERE passport_id = p_passport_id;
149
150 CURSOR get_dob_dt_cur(cp_person_id igs_pe_passport.person_id%TYPE)
151 IS
152 SELECT birth_date
153 FROM igs_pe_person_base_v
154 WHERE person_id = cp_person_id;
155
156 CURSOR overlap_poe_cur(cp_visa_id igs_pe_visa.visa_id%TYPE,
157 cp_issue_date igs_pe_visa.visa_issue_date%TYPE,
158 cp_expiry_date igs_pe_visa.visa_expiry_date%TYPE) IS
159 SELECT 1
160 FROM igs_pe_visit_histry
161 WHERE visa_id = cp_visa_id AND
162 (visit_start_date < cp_issue_date OR visit_end_date > cp_expiry_date+30);
163
164 l_birth_dt igs_pe_person_base_v.birth_date%TYPE;
165 l_exp_date igs_pe_passport.passport_expiry_date%TYPE;
166 l_count NUMBER(1);
167
168 BEGIN
169 IF p_inserting or p_updating THEN
170 IF new_references.passport_id IS NOT NULL THEN
171 OPEN get_pass_exp_dt(new_references.passport_id);
172 FETCH get_pass_exp_dt INTO l_exp_date;
173 CLOSE get_pass_exp_dt;
174 IF new_references.visa_issue_date > l_exp_date THEN
175 fnd_message.set_name ('IGS', 'IGS_PE_VIS_ASOC_PASS_EXP');
176 fnd_message.set_token('PASSPORT_DATE',to_char(l_exp_date,'DD-MON-YYYY'));
177 fnd_message.set_token('VISA_DATE',to_char(new_references.visa_issue_date,'DD-MON-YYYY'));
178 igs_ge_msg_stack.add;
179 app_exception.raise_exception;
180 END IF;
181 END IF;
182
183
184 IF new_references.visa_issue_date > new_references.visa_expiry_date THEN
185 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PE_FROM_DT_GRT_TO_DATE');
186 IGS_GE_MSG_STACK.ADD;
187 APP_EXCEPTION.RAISE_EXCEPTION;
188 END IF;
189
190 OPEN get_dob_dt_cur(new_references.person_id);
191 FETCH get_dob_dt_cur INTO l_birth_dt;
192 CLOSE get_dob_dt_cur;
193
194 IF l_birth_dt IS NOT NULL AND new_references.visa_issue_date IS NOT NULL THEN
195 IF l_birth_dt > new_references.visa_issue_date THEN
196 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PE_INT_DT_LT_BRDT');
197 IGS_GE_MSG_STACK.ADD;
198 APP_EXCEPTION.RAISE_EXCEPTION;
199 END IF;
200 END IF;
201
202 END IF;
203
204 IF p_updating THEN
205 IF (new_references.visa_issue_date <> old_references.visa_issue_date OR
206 new_references.visa_expiry_date <> old_references.visa_expiry_date) THEN
207 OPEN overlap_poe_cur(new_references.visa_id, new_references.visa_issue_date, new_references.visa_expiry_date);
208 FETCH overlap_poe_cur INTO l_count;
209 IF overlap_poe_cur%FOUND THEN
210 CLOSE overlap_poe_cur;
211 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PE_POE_VISA_OVERLAP');
212 FND_MESSAGE.SET_TOKEN('VISA_ISSUE', NULL);
213 FND_MESSAGE.SET_TOKEN('VISA_EXP', NULL);
214 IGS_GE_MSG_STACK.ADD;
215 APP_EXCEPTION.RAISE_EXCEPTION;
216 END IF;
217 CLOSE overlap_poe_cur;
218 END IF;
219 END IF;
220 END beforerowinsertupdate;
221
222
223 PROCEDURE check_uniqueness AS
224 /*
225 || Created By : [email protected]
226 || Created On : 28-NOV-2002
227 || Purpose : Handles the Unique Constraint logic defined for the columns.
228 || Known limitations, enhancements or remarks :
229 || Change History :
230 || Who When What
231 || (reverse chronological order - newest change first)
232 */
233 BEGIN
234
235 IF ( get_uk_for_validation (
236 new_references.person_id,
237 new_references.visa_type,
238 new_references.visa_issue_date
239 )
240 ) THEN
241 fnd_message.set_name ('IGS', 'IGS_PE_VISA_DUP_EXISTS');
242 igs_ge_msg_stack.add;
243 app_exception.raise_exception;
244 END IF;
245
246 END check_uniqueness;
247
248
249 PROCEDURE check_parent_existance AS
250 /*
251 || Created By : [email protected]
252 || Created On : 28-NOV-2002
253 || Purpose : Checks for the existance of Parent records.
254 || Known limitations, enhancements or remarks :
255 || Change History :
256 || Who When What
257 || (reverse chronological order - newest change first)
258 */
259 BEGIN
260
261 IF (((old_references.passport_id = new_references.passport_id)) OR
262 ((new_references.passport_id IS NULL))) THEN
263 NULL;
264 ELSIF NOT igs_pe_passport_pkg.get_pk_for_validation (
265 new_references.passport_id
266 ) THEN
267 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
268 igs_ge_msg_stack.add;
269 app_exception.raise_exception;
270 END IF;
271
272 IF (((old_references.person_id = new_references.person_id)) OR
273 ((new_references.person_id IS NULL))) THEN
274 NULL;
275 ELSIF NOT Igs_Pe_Person_Pkg.Get_PK_For_Validation (
276 new_references.person_id
277 ) THEN
278 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
279 IGS_GE_MSG_STACK.ADD;
280 App_Exception.Raise_Exception;
281 END IF;
282
283 END check_parent_existance;
284
285
286 PROCEDURE check_child_existance AS
287 /*
288 || Created By : [email protected]
289 || Created On : 28-NOV-2002
290 || Purpose : Checks for the existance of Child records.
291 || Known limitations, enhancements or remarks :
292 || Change History :
293 || Who When What
294 || (reverse chronological order - newest change first)
295 */
296 BEGIN
297
298 igs_pe_visit_histry_pkg.get_fk_igs_pe_visa (
299 old_references.visa_id
300 );
301
302 END check_child_existance;
303
304
305 FUNCTION get_pk_for_validation (
306 x_visa_id IN NUMBER
307 ) RETURN BOOLEAN AS
308 /*
309 || Created By : [email protected]
310 || Created On : 28-NOV-2002
311 || Purpose : Validates the Primary Key of the table.
312 || Known limitations, enhancements or remarks :
313 || Change History :
314 || Who When What
315 || (reverse chronological order - newest change first)
316 */
317 CURSOR cur_rowid IS
318 SELECT rowid
319 FROM igs_pe_visa
320 WHERE visa_id = x_visa_id
321 FOR UPDATE NOWAIT;
322
323 lv_rowid cur_rowid%RowType;
324
325 BEGIN
326
327 OPEN cur_rowid;
328 FETCH cur_rowid INTO lv_rowid;
329 IF (cur_rowid%FOUND) THEN
330 CLOSE cur_rowid;
331 RETURN(TRUE);
332 ELSE
333 CLOSE cur_rowid;
334 RETURN(FALSE);
335 END IF;
336
337 END get_pk_for_validation;
338
339
340 FUNCTION get_uk_for_validation (
341 x_person_id IN NUMBER,
342 x_visa_type IN VARCHAR2,
343 x_visa_issue_date IN DATE
344 ) RETURN BOOLEAN AS
345 /*
346 || Created By : [email protected]
347 || Created On : 28-NOV-2002
348 || Purpose : Validates the Unique Keys of the table.
349 || Known limitations, enhancements or remarks :
350 || Change History :
351 || Who When What
352 || (reverse chronological order - newest change first)
353 */
354 CURSOR cur_rowid IS
355 SELECT rowid
356 FROM igs_pe_visa
357 WHERE person_id = x_person_id
358 AND visa_type = x_visa_type
359 AND visa_issue_date = x_visa_issue_date
360 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
361
362 lv_rowid cur_rowid%RowType;
363
364 BEGIN
365
366 OPEN cur_rowid;
367 FETCH cur_rowid INTO lv_rowid;
368 IF (cur_rowid%FOUND) THEN
369 CLOSE cur_rowid;
370 RETURN (true);
371 ELSE
372 CLOSE cur_rowid;
373 RETURN(FALSE);
374 END IF;
375
376 END get_uk_for_validation ;
377
378
379 PROCEDURE get_fk_igs_pe_passport (
380 x_passport_id IN NUMBER
381 ) AS
382 /*
383 || Created By : [email protected]
384 || Created On : 28-NOV-2002
385 || Purpose : Validates the Foreign Keys for the table.
386 || Known limitations, enhancements or remarks :
387 || Change History :
388 || Who When What
389 || (reverse chronological order - newest change first)
390 */
391 CURSOR cur_rowid IS
392 SELECT rowid
393 FROM igs_pe_visa
394 WHERE ((passport_id = x_passport_id));
395
396 lv_rowid cur_rowid%RowType;
397
398 BEGIN
399
400 OPEN cur_rowid;
401 FETCH cur_rowid INTO lv_rowid;
402 IF (cur_rowid%FOUND) THEN
403 CLOSE cur_rowid;
404 fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
405 igs_ge_msg_stack.add;
406 app_exception.raise_exception;
407 RETURN;
408 END IF;
409 CLOSE cur_rowid;
410
411 END get_fk_igs_pe_passport;
412
413
414 PROCEDURE before_dml (
415 p_action IN VARCHAR2,
416 x_rowid IN VARCHAR2,
417 x_visa_id IN NUMBER,
418 x_person_id IN NUMBER,
419 x_visa_type IN VARCHAR2,
420 x_visa_number IN VARCHAR2,
421 x_visa_issue_date IN DATE,
422 x_visa_expiry_date IN DATE,
423 x_visa_category IN VARCHAR2,
424 x_visa_issuing_post IN VARCHAR2,
425 x_passport_id IN NUMBER,
426 x_agent_org_unit_cd IN VARCHAR2,
427 x_agent_person_id IN NUMBER,
428 x_agent_contact_name IN VARCHAR2,
429 x_attribute_category IN VARCHAR2,
430 x_attribute1 IN VARCHAR2,
431 x_attribute2 IN VARCHAR2,
432 x_attribute3 IN VARCHAR2,
433 x_attribute4 IN VARCHAR2,
434 x_attribute5 IN VARCHAR2,
435 x_attribute6 IN VARCHAR2,
436 x_attribute7 IN VARCHAR2,
437 x_attribute8 IN VARCHAR2,
438 x_attribute9 IN VARCHAR2,
439 x_attribute10 IN VARCHAR2,
440 x_attribute11 IN VARCHAR2,
441 x_attribute12 IN VARCHAR2,
442 x_attribute13 IN VARCHAR2,
443 x_attribute14 IN VARCHAR2,
444 x_attribute15 IN VARCHAR2,
445 x_attribute16 IN VARCHAR2,
446 x_attribute17 IN VARCHAR2,
447 x_attribute18 IN VARCHAR2,
448 x_attribute19 IN VARCHAR2,
449 x_attribute20 IN VARCHAR2,
450 x_visa_issuing_country IN VARCHAR2,
451 x_creation_date IN DATE,
452 x_created_by IN NUMBER,
453 x_last_update_date IN DATE,
454 x_last_updated_by IN NUMBER,
455 x_last_update_login IN NUMBER
456 ) AS
457 /*
458 || Created By : [email protected]
459 || Created On : 28-NOV-2002
460 || Purpose : Initialises the columns, Checks Constraints, Calls the
461 || Trigger Handlers for the table, before any DML operation.
462 || Known limitations, enhancements or remarks :
463 || Change History :
464 || Who When What
465 || (reverse chronological order - newest change first)
466 */
467 BEGIN
468
469 set_column_values (
470 p_action,
471 x_rowid,
472 x_visa_id,
473 x_person_id,
474 x_visa_type,
475 x_visa_number,
476 x_visa_issue_date,
477 x_visa_expiry_date,
478 x_visa_category,
479 x_visa_issuing_post,
480 x_passport_id,
481 x_agent_org_unit_cd,
482 x_agent_person_id,
483 x_agent_contact_name,
484 x_attribute_category,
485 x_attribute1,
486 x_attribute2,
487 x_attribute3,
488 x_attribute4,
489 x_attribute5,
490 x_attribute6,
491 x_attribute7,
492 x_attribute8,
493 x_attribute9,
494 x_attribute10,
495 x_attribute11,
496 x_attribute12,
497 x_attribute13,
498 x_attribute14,
499 x_attribute15,
500 x_attribute16,
501 x_attribute17,
502 x_attribute18,
503 x_attribute19,
504 x_attribute20,
505 x_visa_issuing_country,
506 x_creation_date,
507 x_created_by,
508 x_last_update_date,
509 x_last_updated_by,
510 x_last_update_login
511 );
512
513 IF (p_action = 'INSERT') THEN
514 -- Call all the procedures related to Before Insert.
515 beforerowinsertupdate(TRUE,FALSE);
516 IF ( get_pk_for_validation(
517 new_references.visa_id
518 )
519 ) THEN
520 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
521 igs_ge_msg_stack.add;
522 app_exception.raise_exception;
523 END IF;
524 check_uniqueness;
525 check_parent_existance;
526 ELSIF (p_action = 'UPDATE') THEN
527 -- Call all the procedures related to Before Update.
528 beforerowinsertupdate(FALSE,TRUE);
529 check_uniqueness;
530 check_parent_existance;
531 ELSIF (p_action = 'DELETE') THEN
532 -- Call all the procedures related to Before Delete.
533 check_child_existance;
534 ELSIF (p_action = 'VALIDATE_INSERT') THEN
535 -- Call all the procedures related to Before Insert.
536 IF ( get_pk_for_validation (
537 new_references.visa_id
538 )
539 ) THEN
540 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
541 igs_ge_msg_stack.add;
542 app_exception.raise_exception;
543 END IF;
544 check_uniqueness;
545 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
546 check_uniqueness;
547 ELSIF (p_action = 'VALIDATE_DELETE') THEN
548 check_child_existance;
549 END IF;
550
551 END before_dml;
552
553 PROCEDURE afterrowinsertupdate(
554 p_inserting IN BOOLEAN,
555 p_updating IN BOOLEAN,
556 p_deleting IN BOOLEAN
557 ) AS
558 ------------------------------------------------------------------------------------------
559 --Created by : pkpatel
560 --Date created: 24-FEB-2003
561 --
562 --Purpose:Bug 2783882. Moved the overlap validation from library post-forms-commit
563 --
564 --Known limitations/enhancements and/or remarks:
565 --
566 --Change History:
567 --Who When What
568 ----------------------------------------------------------------------------------------------
569 CURSOR c_visa_overlap(cp_person_id igs_pe_visa.person_id%TYPE) IS
570 SELECT count(1)
571 FROM igs_pe_visa a ,igs_pe_visa b
572 WHERE a.person_id = cp_person_id AND
573 a.person_id = b.person_id AND
574 a.rowid <> b.rowid AND
575 a.visa_expiry_date >= b.visa_issue_date AND
576 a.visa_expiry_date <= b.visa_expiry_date;
577
578 l_count NUMBER(1);
579 BEGIN
580 OPEN c_visa_overlap(new_references.person_id);
581 FETCH c_visa_overlap INTO l_count;
582 CLOSE c_visa_overlap;
583
584 IF l_count > 0 THEN
585 FND_MESSAGE.SET_NAME('IGS','IGS_PE_VISA_DATE_OVERLAP');
586 IGS_GE_MSG_STACK.ADD;
587 APP_EXCEPTION.RAISE_EXCEPTION;
588 END IF;
589
590 END afterrowinsertupdate;
591
592 PROCEDURE After_DML (
593 p_action IN VARCHAR2,
594 x_rowid IN VARCHAR2
595 ) AS
596 ------------------------------------------------------------------------------------------
597 --Created by : pkpatel
598 --Date created: 24-FEB-2003
599 --
600 --Purpose:Bug 2783882. Moved the overlap validation from library post-forms-commit
601 --
602 --Known limitations/enhancements and/or remarks:
603 --
604 --Change History:
605 --Who When What
606 ----------------------------------------------------------------------------------------------
607 BEGIN
608 l_rowid := x_rowid;
609 IF (p_action = 'INSERT') THEN
610 -- Call all the procedures related to After Insert.
611 AfterRowInsertUpdate (
612 p_inserting => TRUE,
613 p_updating => FALSE,
614 p_deleting => FALSE
615 );
616 ELSIF (p_action = 'UPDATE') THEN
617 -- Call all the procedures related to After Update.
618 AfterRowInsertUpdate (
619 p_inserting => FALSE,
620 p_updating => TRUE,
621 p_deleting => FALSE
622 );
623 ELSIF (p_action = 'DELETE') THEN
624 -- Call all the procedures related to After Delete.
625 Null;
626 END IF;
627 END After_DML;
628
629 PROCEDURE insert_row (
630 x_rowid IN OUT NOCOPY VARCHAR2,
631 x_visa_id IN OUT NOCOPY NUMBER,
632 x_person_id IN NUMBER,
633 x_visa_type IN VARCHAR2,
634 x_visa_number IN VARCHAR2,
635 x_visa_issue_date IN DATE,
636 x_visa_expiry_date IN DATE,
637 x_visa_category IN VARCHAR2,
638 x_visa_issuing_post IN VARCHAR2,
639 x_passport_id IN NUMBER,
640 x_agent_org_unit_cd IN VARCHAR2,
641 x_agent_person_id IN NUMBER,
642 x_agent_contact_name IN VARCHAR2,
643 x_attribute_category IN VARCHAR2,
644 x_attribute1 IN VARCHAR2,
645 x_attribute2 IN VARCHAR2,
646 x_attribute3 IN VARCHAR2,
647 x_attribute4 IN VARCHAR2,
648 x_attribute5 IN VARCHAR2,
649 x_attribute6 IN VARCHAR2,
650 x_attribute7 IN VARCHAR2,
651 x_attribute8 IN VARCHAR2,
652 x_attribute9 IN VARCHAR2,
653 x_attribute10 IN VARCHAR2,
654 x_attribute11 IN VARCHAR2,
655 x_attribute12 IN VARCHAR2,
656 x_attribute13 IN VARCHAR2,
657 x_attribute14 IN VARCHAR2,
658 x_attribute15 IN VARCHAR2,
659 x_attribute16 IN VARCHAR2,
660 x_attribute17 IN VARCHAR2,
661 x_attribute18 IN VARCHAR2,
662 x_attribute19 IN VARCHAR2,
663 x_attribute20 IN VARCHAR2,
664 x_visa_issuing_country IN VARCHAR2,
665 x_mode IN VARCHAR2
666 ) AS
667 /*
668 || Created By : [email protected]
669 || Created On : 28-NOV-2002
670 || Purpose : Handles the INSERT DML logic for the table.
671 || Known limitations, enhancements or remarks :
672 || Change History :
673 || Who When What
674 || (reverse chronological order - newest change first)
675 */
676
677 x_last_update_date DATE;
678 x_last_updated_by NUMBER;
679 x_last_update_login NUMBER;
680
681 BEGIN
682
683 x_last_update_date := SYSDATE;
684 IF (x_mode = 'I') THEN
685 x_last_updated_by := 1;
686 x_last_update_login := 0;
687 ELSIF (X_MODE IN ('R', 'S')) THEN
688 x_last_updated_by := fnd_global.user_id;
689 IF (x_last_updated_by IS NULL) THEN
690 x_last_updated_by := -1;
691 END IF;
692 x_last_update_login := fnd_global.login_id;
693 IF (x_last_update_login IS NULL) THEN
694 x_last_update_login := -1;
695 END IF;
696 ELSE
697 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
698 igs_ge_msg_stack.add;
699 app_exception.raise_exception;
700 END IF;
701
702 before_dml(
703 p_action => 'INSERT',
704 x_rowid => x_rowid,
705 x_visa_id => x_visa_id,
706 x_person_id => x_person_id,
707 x_visa_type => x_visa_type,
708 x_visa_number => x_visa_number,
709 x_visa_issue_date => x_visa_issue_date,
710 x_visa_expiry_date => x_visa_expiry_date,
711 x_visa_category => x_visa_category,
712 x_visa_issuing_post => x_visa_issuing_post,
713 x_passport_id => x_passport_id,
714 x_agent_org_unit_cd => x_agent_org_unit_cd,
715 x_agent_person_id => x_agent_person_id,
716 x_agent_contact_name => x_agent_contact_name,
717 x_attribute_category => x_attribute_category,
718 x_attribute1 => x_attribute1,
719 x_attribute2 => x_attribute2,
720 x_attribute3 => x_attribute3,
721 x_attribute4 => x_attribute4,
722 x_attribute5 => x_attribute5,
723 x_attribute6 => x_attribute6,
724 x_attribute7 => x_attribute7,
725 x_attribute8 => x_attribute8,
726 x_attribute9 => x_attribute9,
727 x_attribute10 => x_attribute10,
728 x_attribute11 => x_attribute11,
729 x_attribute12 => x_attribute12,
730 x_attribute13 => x_attribute13,
731 x_attribute14 => x_attribute14,
732 x_attribute15 => x_attribute15,
733 x_attribute16 => x_attribute16,
734 x_attribute17 => x_attribute17,
735 x_attribute18 => x_attribute18,
736 x_attribute19 => x_attribute19,
737 x_attribute20 => x_attribute20,
738 x_visa_issuing_country => x_visa_issuing_country,
739 x_creation_date => x_last_update_date,
740 x_created_by => x_last_updated_by,
741 x_last_update_date => x_last_update_date,
742 x_last_updated_by => x_last_updated_by,
743 x_last_update_login => x_last_update_login
744 );
745
746 IF (x_mode = 'S') THEN
747 igs_sc_gen_001.set_ctx('R');
748 END IF;
749 INSERT INTO igs_pe_visa (
750 visa_id,
751 person_id,
752 visa_type,
753 visa_number,
754 visa_issue_date,
755 visa_expiry_date,
756 visa_category,
757 visa_issuing_post,
758 passport_id,
759 agent_org_unit_cd,
760 agent_person_id,
761 agent_contact_name,
762 attribute_category,
763 attribute1,
764 attribute2,
765 attribute3,
766 attribute4,
767 attribute5,
768 attribute6,
769 attribute7,
770 attribute8,
771 attribute9,
772 attribute10,
773 attribute11,
774 attribute12,
775 attribute13,
776 attribute14,
777 attribute15,
778 attribute16,
779 attribute17,
780 attribute18,
781 attribute19,
782 attribute20,
783 visa_issuing_country,
784 creation_date,
785 created_by,
786 last_update_date,
787 last_updated_by,
788 last_update_login
789 ) VALUES (
790 igs_pe_visa_s.NEXTVAL,
791 new_references.person_id,
792 new_references.visa_type,
793 new_references.visa_number,
794 new_references.visa_issue_date,
795 new_references.visa_expiry_date,
796 new_references.visa_category,
797 new_references.visa_issuing_post,
798 new_references.passport_id,
799 new_references.agent_org_unit_cd,
800 new_references.agent_person_id,
801 new_references.agent_contact_name,
802 new_references.attribute_category,
803 new_references.attribute1,
804 new_references.attribute2,
805 new_references.attribute3,
806 new_references.attribute4,
807 new_references.attribute5,
808 new_references.attribute6,
809 new_references.attribute7,
810 new_references.attribute8,
811 new_references.attribute9,
812 new_references.attribute10,
813 new_references.attribute11,
814 new_references.attribute12,
815 new_references.attribute13,
816 new_references.attribute14,
817 new_references.attribute15,
818 new_references.attribute16,
819 new_references.attribute17,
820 new_references.attribute18,
821 new_references.attribute19,
822 new_references.attribute20,
823 new_references.visa_issuing_country,
824 x_last_update_date,
825 x_last_updated_by,
826 x_last_update_date,
827 x_last_updated_by,
828 x_last_update_login
829 ) RETURNING ROWID, visa_id INTO x_rowid, x_visa_id;
830 IF (x_mode = 'S') THEN
831 igs_sc_gen_001.unset_ctx('R');
832 END IF;
833
834
835
836 After_DML(
837 p_action => 'INSERT',
838 x_rowid => X_ROWID
839 );
840
841
842 EXCEPTION
843 WHEN OTHERS THEN
844 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
845 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
846 fnd_message.set_token ('ERR_CD', SQLCODE);
847 igs_ge_msg_stack.add;
848 igs_sc_gen_001.unset_ctx('R');
849 app_exception.raise_exception;
850 ELSE
851 igs_sc_gen_001.unset_ctx('R');
852 RAISE;
853 END IF;
854 END insert_row;
855
856
857 PROCEDURE lock_row (
858 x_rowid IN VARCHAR2,
859 x_visa_id IN NUMBER,
860 x_person_id IN NUMBER,
861 x_visa_type IN VARCHAR2,
862 x_visa_number IN VARCHAR2,
863 x_visa_issue_date IN DATE,
864 x_visa_expiry_date IN DATE,
865 x_visa_category IN VARCHAR2,
866 x_visa_issuing_post IN VARCHAR2,
867 x_passport_id IN NUMBER,
868 x_agent_org_unit_cd IN VARCHAR2,
869 x_agent_person_id IN NUMBER,
870 x_agent_contact_name IN VARCHAR2,
871 x_attribute_category IN VARCHAR2,
872 x_attribute1 IN VARCHAR2,
873 x_attribute2 IN VARCHAR2,
874 x_attribute3 IN VARCHAR2,
875 x_attribute4 IN VARCHAR2,
876 x_attribute5 IN VARCHAR2,
877 x_attribute6 IN VARCHAR2,
878 x_attribute7 IN VARCHAR2,
879 x_attribute8 IN VARCHAR2,
880 x_attribute9 IN VARCHAR2,
881 x_attribute10 IN VARCHAR2,
882 x_attribute11 IN VARCHAR2,
883 x_attribute12 IN VARCHAR2,
884 x_attribute13 IN VARCHAR2,
885 x_attribute14 IN VARCHAR2,
886 x_attribute15 IN VARCHAR2,
887 x_attribute16 IN VARCHAR2,
888 x_attribute17 IN VARCHAR2,
889 x_attribute18 IN VARCHAR2,
890 x_attribute19 IN VARCHAR2,
891 x_attribute20 IN VARCHAR2,
892 x_visa_issuing_country IN VARCHAR2
893 ) AS
894 /*
895 || Created By : [email protected]
896 || Created On : 28-NOV-2002
897 || Purpose : Handles the LOCK mechanism for the table.
898 || Known limitations, enhancements or remarks :
899 || Change History :
900 || Who When What
901 || (reverse chronological order - newest change first)
902 */
903 CURSOR c1 IS
904 SELECT
905 person_id,
906 visa_type,
907 visa_number,
908 visa_issue_date,
909 visa_expiry_date,
910 visa_category,
911 visa_issuing_post,
912 passport_id,
913 agent_org_unit_cd,
914 agent_person_id,
915 agent_contact_name,
916 attribute_category,
917 attribute1,
918 attribute2,
919 attribute3,
920 attribute4,
921 attribute5,
922 attribute6,
923 attribute7,
924 attribute8,
925 attribute9,
926 attribute10,
927 attribute11,
928 attribute12,
929 attribute13,
930 attribute14,
931 attribute15,
932 attribute16,
933 attribute17,
934 attribute18,
935 attribute19,
936 attribute20,
937 visa_issuing_country
938 FROM igs_pe_visa
939 WHERE rowid = x_rowid
940 FOR UPDATE NOWAIT;
941
942 tlinfo c1%ROWTYPE;
943
944 BEGIN
945
946 OPEN c1;
947 FETCH c1 INTO tlinfo;
948 IF (c1%notfound) THEN
949 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
950 igs_ge_msg_stack.add;
951 CLOSE c1;
952 app_exception.raise_exception;
953 RETURN;
954 END IF;
955 CLOSE c1;
956
957 IF (
958 (tlinfo.person_id = x_person_id)
959 AND (tlinfo.visa_type = x_visa_type)
960 AND (tlinfo.visa_number = x_visa_number)
961 AND (tlinfo.visa_issue_date = x_visa_issue_date)
962 AND (tlinfo.visa_expiry_date = x_visa_expiry_date)
963 AND ((tlinfo.visa_category = x_visa_category) OR ((tlinfo.visa_category IS NULL) AND (X_visa_category IS NULL)))
964 AND ((tlinfo.visa_issuing_post = x_visa_issuing_post) OR ((tlinfo.visa_issuing_post IS NULL) AND (X_visa_issuing_post IS NULL)))
965 AND ((tlinfo.passport_id = x_passport_id) OR ((tlinfo.passport_id IS NULL) AND (X_passport_id IS NULL)))
966 AND ((tlinfo.agent_org_unit_cd = x_agent_org_unit_cd) OR ((tlinfo.agent_org_unit_cd IS NULL) AND (X_agent_org_unit_cd IS NULL)))
967 AND ((tlinfo.agent_person_id = x_agent_person_id) OR ((tlinfo.agent_person_id IS NULL) AND (X_agent_person_id IS NULL)))
968 AND ((tlinfo.agent_contact_name = x_agent_contact_name) OR ((tlinfo.agent_contact_name IS NULL) AND (X_agent_contact_name IS NULL)))
969 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
970 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
971 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
972 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
973 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
974 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
975 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
976 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
977 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
978 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
979 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
980 AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (X_attribute11 IS NULL)))
981 AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (X_attribute12 IS NULL)))
982 AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (X_attribute13 IS NULL)))
983 AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (X_attribute14 IS NULL)))
984 AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (X_attribute15 IS NULL)))
985 AND ((tlinfo.attribute16 = x_attribute16) OR ((tlinfo.attribute16 IS NULL) AND (X_attribute16 IS NULL)))
986 AND ((tlinfo.attribute17 = x_attribute17) OR ((tlinfo.attribute17 IS NULL) AND (X_attribute17 IS NULL)))
987 AND ((tlinfo.attribute18 = x_attribute18) OR ((tlinfo.attribute18 IS NULL) AND (X_attribute18 IS NULL)))
988 AND ((tlinfo.attribute19 = x_attribute19) OR ((tlinfo.attribute19 IS NULL) AND (X_attribute19 IS NULL)))
989 AND ((tlinfo.attribute20 = x_attribute20) OR ((tlinfo.attribute20 IS NULL) AND (X_attribute20 IS NULL)))
990 AND ((tlinfo.visa_issuing_country = x_visa_issuing_country) OR ((tlinfo.visa_issuing_country IS NULL) AND (x_visa_issuing_country IS NULL)))
991 ) THEN
992 NULL;
993 ELSE
994 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
995 igs_ge_msg_stack.add;
996 app_exception.raise_exception;
997 END IF;
998
999 RETURN;
1000
1001 END lock_row;
1002
1003
1004 PROCEDURE update_row (
1005 x_rowid IN VARCHAR2,
1006 x_visa_id IN NUMBER,
1007 x_person_id IN NUMBER,
1008 x_visa_type IN VARCHAR2,
1009 x_visa_number IN VARCHAR2,
1010 x_visa_issue_date IN DATE,
1011 x_visa_expiry_date IN DATE,
1012 x_visa_category IN VARCHAR2,
1013 x_visa_issuing_post IN VARCHAR2,
1014 x_passport_id IN NUMBER,
1015 x_agent_org_unit_cd IN VARCHAR2,
1016 x_agent_person_id IN NUMBER,
1017 x_agent_contact_name IN VARCHAR2,
1018 x_attribute_category IN VARCHAR2,
1019 x_attribute1 IN VARCHAR2,
1020 x_attribute2 IN VARCHAR2,
1021 x_attribute3 IN VARCHAR2,
1022 x_attribute4 IN VARCHAR2,
1023 x_attribute5 IN VARCHAR2,
1024 x_attribute6 IN VARCHAR2,
1025 x_attribute7 IN VARCHAR2,
1026 x_attribute8 IN VARCHAR2,
1027 x_attribute9 IN VARCHAR2,
1028 x_attribute10 IN VARCHAR2,
1029 x_attribute11 IN VARCHAR2,
1030 x_attribute12 IN VARCHAR2,
1031 x_attribute13 IN VARCHAR2,
1032 x_attribute14 IN VARCHAR2,
1033 x_attribute15 IN VARCHAR2,
1034 x_attribute16 IN VARCHAR2,
1035 x_attribute17 IN VARCHAR2,
1036 x_attribute18 IN VARCHAR2,
1037 x_attribute19 IN VARCHAR2,
1038 x_attribute20 IN VARCHAR2,
1039 x_visa_issuing_country IN VARCHAR2,
1040 x_mode IN VARCHAR2
1041 ) AS
1042 /*
1043 || Created By : [email protected]
1044 || Created On : 28-NOV-2002
1045 || Purpose : Handles the UPDATE DML logic for the table.
1046 || Known limitations, enhancements or remarks :
1047 || Change History :
1048 || Who When What
1049 || (reverse chronological order - newest change first)
1050 */
1051 x_last_update_date DATE ;
1052 x_last_updated_by NUMBER;
1053 x_last_update_login NUMBER;
1054
1055 BEGIN
1056
1057 x_last_update_date := SYSDATE;
1058 IF (X_MODE = 'I') THEN
1059 x_last_updated_by := 1;
1060 x_last_update_login := 0;
1061 ELSIF (X_MODE IN ('R', 'S')) THEN
1062 x_last_updated_by := fnd_global.user_id;
1063 IF x_last_updated_by IS NULL THEN
1064 x_last_updated_by := -1;
1065 END IF;
1066 x_last_update_login := fnd_global.login_id;
1067 IF (x_last_update_login IS NULL) THEN
1068 x_last_update_login := -1;
1069 END IF;
1070 ELSE
1071 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1072 igs_ge_msg_stack.add;
1073 app_exception.raise_exception;
1074 END IF;
1075
1076 before_dml(
1077 p_action => 'UPDATE',
1078 x_rowid => x_rowid,
1079 x_visa_id => x_visa_id,
1080 x_person_id => x_person_id,
1081 x_visa_type => x_visa_type,
1082 x_visa_number => x_visa_number,
1083 x_visa_issue_date => x_visa_issue_date,
1084 x_visa_expiry_date => x_visa_expiry_date,
1085 x_visa_category => x_visa_category,
1086 x_visa_issuing_post => x_visa_issuing_post,
1087 x_passport_id => x_passport_id,
1088 x_agent_org_unit_cd => x_agent_org_unit_cd,
1089 x_agent_person_id => x_agent_person_id,
1090 x_agent_contact_name => x_agent_contact_name,
1091 x_attribute_category => x_attribute_category,
1092 x_attribute1 => x_attribute1,
1093 x_attribute2 => x_attribute2,
1094 x_attribute3 => x_attribute3,
1095 x_attribute4 => x_attribute4,
1096 x_attribute5 => x_attribute5,
1097 x_attribute6 => x_attribute6,
1098 x_attribute7 => x_attribute7,
1099 x_attribute8 => x_attribute8,
1100 x_attribute9 => x_attribute9,
1101 x_attribute10 => x_attribute10,
1102 x_attribute11 => x_attribute11,
1103 x_attribute12 => x_attribute12,
1104 x_attribute13 => x_attribute13,
1105 x_attribute14 => x_attribute14,
1106 x_attribute15 => x_attribute15,
1107 x_attribute16 => x_attribute16,
1108 x_attribute17 => x_attribute17,
1109 x_attribute18 => x_attribute18,
1110 x_attribute19 => x_attribute19,
1111 x_attribute20 => x_attribute20,
1112 x_visa_issuing_country => x_visa_issuing_country,
1113 x_creation_date => x_last_update_date,
1114 x_created_by => x_last_updated_by,
1115 x_last_update_date => x_last_update_date,
1116 x_last_updated_by => x_last_updated_by,
1117 x_last_update_login => x_last_update_login
1118 );
1119
1120 IF (x_mode = 'S') THEN
1121 igs_sc_gen_001.set_ctx('R');
1122 END IF;
1123 UPDATE igs_pe_visa
1124 SET
1125 person_id = new_references.person_id,
1126 visa_type = new_references.visa_type,
1127 visa_number = new_references.visa_number,
1128 visa_issue_date = new_references.visa_issue_date,
1129 visa_expiry_date = new_references.visa_expiry_date,
1130 visa_category = new_references.visa_category,
1131 visa_issuing_post = new_references.visa_issuing_post,
1132 passport_id = new_references.passport_id,
1133 agent_org_unit_cd = new_references.agent_org_unit_cd,
1134 agent_person_id = new_references.agent_person_id,
1135 agent_contact_name = new_references.agent_contact_name,
1136 attribute_category = new_references.attribute_category,
1137 attribute1 = new_references.attribute1,
1138 attribute2 = new_references.attribute2,
1139 attribute3 = new_references.attribute3,
1140 attribute4 = new_references.attribute4,
1141 attribute5 = new_references.attribute5,
1142 attribute6 = new_references.attribute6,
1143 attribute7 = new_references.attribute7,
1144 attribute8 = new_references.attribute8,
1145 attribute9 = new_references.attribute9,
1146 attribute10 = new_references.attribute10,
1147 attribute11 = new_references.attribute11,
1148 attribute12 = new_references.attribute12,
1149 attribute13 = new_references.attribute13,
1150 attribute14 = new_references.attribute14,
1151 attribute15 = new_references.attribute15,
1152 attribute16 = new_references.attribute16,
1153 attribute17 = new_references.attribute17,
1154 attribute18 = new_references.attribute18,
1155 attribute19 = new_references.attribute19,
1156 attribute20 = new_references.attribute20,
1157 visa_issuing_country = new_references.visa_issuing_country,
1158 last_update_date = x_last_update_date,
1159 last_updated_by = x_last_updated_by,
1160 last_update_login = x_last_update_login
1161 WHERE rowid = x_rowid;
1162
1163 IF (SQL%NOTFOUND) THEN
1164 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1165 igs_ge_msg_stack.add;
1166 igs_sc_gen_001.unset_ctx('R');
1167 app_exception.raise_exception;
1168 END IF;
1169 IF (x_mode = 'S') THEN
1170 igs_sc_gen_001.unset_ctx('R');
1171 END IF;
1172
1173
1174 After_DML(
1175 p_action => 'UPDATE',
1176 x_rowid => X_ROWID
1177 );
1178
1179
1180 EXCEPTION
1181 WHEN OTHERS THEN
1182 IF (SQLCODE = (-28115)) THEN
1183 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1184 fnd_message.set_token ('ERR_CD', SQLCODE);
1185 igs_ge_msg_stack.add;
1186 igs_sc_gen_001.unset_ctx('R');
1187 app_exception.raise_exception;
1188 ELSE
1189 igs_sc_gen_001.unset_ctx('R');
1190 RAISE;
1191 END IF;
1192 END update_row;
1193
1194
1195 PROCEDURE add_row (
1196 x_rowid IN OUT NOCOPY VARCHAR2,
1197 x_visa_id IN OUT NOCOPY NUMBER,
1198 x_person_id IN NUMBER,
1199 x_visa_type IN VARCHAR2,
1200 x_visa_number IN VARCHAR2,
1201 x_visa_issue_date IN DATE,
1202 x_visa_expiry_date IN DATE,
1203 x_visa_category IN VARCHAR2,
1204 x_visa_issuing_post IN VARCHAR2,
1205 x_passport_id IN NUMBER,
1206 x_agent_org_unit_cd IN VARCHAR2,
1207 x_agent_person_id IN NUMBER,
1208 x_agent_contact_name IN VARCHAR2,
1209 x_attribute_category IN VARCHAR2,
1210 x_attribute1 IN VARCHAR2,
1211 x_attribute2 IN VARCHAR2,
1212 x_attribute3 IN VARCHAR2,
1213 x_attribute4 IN VARCHAR2,
1214 x_attribute5 IN VARCHAR2,
1215 x_attribute6 IN VARCHAR2,
1216 x_attribute7 IN VARCHAR2,
1217 x_attribute8 IN VARCHAR2,
1218 x_attribute9 IN VARCHAR2,
1219 x_attribute10 IN VARCHAR2,
1220 x_attribute11 IN VARCHAR2,
1221 x_attribute12 IN VARCHAR2,
1222 x_attribute13 IN VARCHAR2,
1223 x_attribute14 IN VARCHAR2,
1224 x_attribute15 IN VARCHAR2,
1225 x_attribute16 IN VARCHAR2,
1226 x_attribute17 IN VARCHAR2,
1227 x_attribute18 IN VARCHAR2,
1228 x_attribute19 IN VARCHAR2,
1229 x_attribute20 IN VARCHAR2,
1230 x_visa_issuing_country IN VARCHAR2,
1231 x_mode IN VARCHAR2
1232 ) AS
1233 /*
1234 || Created By : [email protected]
1235 || Created On : 28-NOV-2002
1236 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1237 || Known limitations, enhancements or remarks :
1238 || Change History :
1239 || Who When What
1240 || (reverse chronological order - newest change first)
1241 */
1242 CURSOR c1 IS
1243 SELECT rowid
1244 FROM igs_pe_visa
1245 WHERE visa_id = x_visa_id;
1246
1247 BEGIN
1248
1249 OPEN c1;
1250 FETCH c1 INTO x_rowid;
1251 IF (c1%NOTFOUND) THEN
1252 CLOSE c1;
1253
1254 insert_row (
1255 x_rowid,
1256 x_visa_id,
1257 x_person_id,
1258 x_visa_type,
1259 x_visa_number,
1260 x_visa_issue_date,
1261 x_visa_expiry_date,
1262 x_visa_category,
1263 x_visa_issuing_post,
1264 x_passport_id,
1265 x_agent_org_unit_cd,
1266 x_agent_person_id,
1267 x_agent_contact_name,
1268 x_attribute_category,
1269 x_attribute1,
1270 x_attribute2,
1271 x_attribute3,
1272 x_attribute4,
1273 x_attribute5,
1274 x_attribute6,
1275 x_attribute7,
1276 x_attribute8,
1277 x_attribute9,
1278 x_attribute10,
1279 x_attribute11,
1280 x_attribute12,
1281 x_attribute13,
1282 x_attribute14,
1283 x_attribute15,
1284 x_attribute16,
1285 x_attribute17,
1286 x_attribute18,
1287 x_attribute19,
1288 x_attribute20,
1289 x_visa_issuing_country,
1290 x_mode
1291 );
1292 RETURN;
1293 END IF;
1294 CLOSE c1;
1295
1296 update_row (
1297 x_rowid,
1298 x_visa_id,
1299 x_person_id,
1300 x_visa_type,
1301 x_visa_number,
1302 x_visa_issue_date,
1303 x_visa_expiry_date,
1304 x_visa_category,
1305 x_visa_issuing_post,
1306 x_passport_id,
1307 x_agent_org_unit_cd,
1308 x_agent_person_id,
1309 x_agent_contact_name,
1310 x_attribute_category,
1311 x_attribute1,
1312 x_attribute2,
1313 x_attribute3,
1314 x_attribute4,
1315 x_attribute5,
1316 x_attribute6,
1317 x_attribute7,
1318 x_attribute8,
1319 x_attribute9,
1320 x_attribute10,
1321 x_attribute11,
1322 x_attribute12,
1323 x_attribute13,
1324 x_attribute14,
1325 x_attribute15,
1326 x_attribute16,
1327 x_attribute17,
1328 x_attribute18,
1329 x_attribute19,
1330 x_attribute20,
1331 x_visa_issuing_country,
1332 x_mode
1333 );
1334
1335 END add_row;
1336
1337
1338 PROCEDURE delete_row (
1339 x_rowid IN VARCHAR2,
1340 x_mode IN VARCHAR2
1341 ) AS
1342 /*
1343 || Created By : [email protected]
1344 || Created On : 28-NOV-2002
1345 || Purpose : Handles the DELETE DML logic for the table.
1346 || Known limitations, enhancements or remarks :
1347 || Change History :
1348 || Who When What
1349 || (reverse chronological order - newest change first)
1350 */
1351 BEGIN
1352
1353 before_dml (
1354 p_action => 'DELETE',
1355 x_rowid => x_rowid
1356 );
1357
1358 IF (x_mode = 'S') THEN
1359 igs_sc_gen_001.set_ctx('R');
1360 END IF;
1361 DELETE FROM igs_pe_visa
1362 WHERE rowid = x_rowid;
1363
1364 IF (SQL%NOTFOUND) THEN
1365 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1366 igs_ge_msg_stack.add;
1367 igs_sc_gen_001.unset_ctx('R');
1368 app_exception.raise_exception;
1369 END IF;
1370 IF (x_mode = 'S') THEN
1371 igs_sc_gen_001.unset_ctx('R');
1372 END IF;
1373
1374
1375 END delete_row;
1376
1377
1378 END igs_pe_visa_pkg;