[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_VISIT_HISTRY_PKG
Source
1 PACKAGE BODY igs_pe_visit_histry_pkg AS
2 /* $Header: IGSNI52B.pls 120.2 2005/10/17 02:21:43 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pe_visit_histry%ROWTYPE;
6 new_references igs_pe_visit_histry%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_port_of_entry IN VARCHAR2,
12 x_cntry_entry_form_num IN VARCHAR2,
13 x_visa_id IN NUMBER,
14 x_visit_start_date IN DATE,
15 x_visit_end_date IN DATE,
16 x_remarks IN VARCHAR2,
17 x_attribute_category IN VARCHAR2,
18 x_attribute1 IN VARCHAR2,
19 x_attribute2 IN VARCHAR2,
20 x_attribute3 IN VARCHAR2,
21 x_attribute4 IN VARCHAR2,
22 x_attribute5 IN VARCHAR2,
23 x_attribute6 IN VARCHAR2,
24 x_attribute7 IN VARCHAR2,
25 x_attribute8 IN VARCHAR2,
26 x_attribute9 IN VARCHAR2,
27 x_attribute10 IN VARCHAR2,
28 x_attribute11 IN VARCHAR2,
29 x_attribute12 IN VARCHAR2,
30 x_attribute13 IN VARCHAR2,
31 x_attribute14 IN VARCHAR2,
32 x_attribute15 IN VARCHAR2,
33 x_attribute16 IN VARCHAR2,
34 x_attribute17 IN VARCHAR2,
35 x_attribute18 IN VARCHAR2,
36 x_attribute19 IN VARCHAR2,
37 x_attribute20 IN VARCHAR2,
38 x_creation_date IN DATE,
39 x_created_by IN NUMBER,
40 x_last_update_date IN DATE,
41 x_last_updated_by IN NUMBER,
42 x_last_update_login IN NUMBER
43 ) AS
44 /*
45 || Created By : [email protected]
46 || Created On : 28-NOV-2002
47 || Purpose : Initialises the Old and New references for the columns of the table.
48 || Known limitations, enhancements or remarks :
49 || Change History :
50 || Who When What
51 || (reverse chronological order - newest change first)
52 */
53
54 CURSOR cur_old_ref_values IS
55 SELECT *
56 FROM igs_pe_visit_histry
57 WHERE rowid = x_rowid;
58
59 BEGIN
60
61 l_rowid := x_rowid;
62
63 -- Code for setting the Old and New Reference Values.
64 -- Populate Old Values.
65 OPEN cur_old_ref_values;
66 FETCH cur_old_ref_values INTO old_references;
67 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
68 CLOSE cur_old_ref_values;
69 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
70 igs_ge_msg_stack.add;
71 app_exception.raise_exception;
72 RETURN;
73 END IF;
74 CLOSE cur_old_ref_values;
75
76 -- Populate New Values.
77 new_references.port_of_entry := x_port_of_entry;
78 new_references.cntry_entry_form_num := x_cntry_entry_form_num;
79 new_references.visa_id := x_visa_id;
80 new_references.visit_start_date := x_visit_start_date;
81 new_references.visit_end_date := x_visit_end_date;
82 new_references.remarks := x_remarks;
83 new_references.attribute_category := x_attribute_category;
84 new_references.attribute1 := x_attribute1;
85 new_references.attribute2 := x_attribute2;
86 new_references.attribute3 := x_attribute3;
87 new_references.attribute4 := x_attribute4;
88 new_references.attribute5 := x_attribute5;
89 new_references.attribute6 := x_attribute6;
90 new_references.attribute7 := x_attribute7;
91 new_references.attribute8 := x_attribute8;
92 new_references.attribute9 := x_attribute9;
93 new_references.attribute10 := x_attribute10;
94 new_references.attribute11 := x_attribute11;
95 new_references.attribute12 := x_attribute12;
96 new_references.attribute13 := x_attribute13;
97 new_references.attribute14 := x_attribute14;
98 new_references.attribute15 := x_attribute15;
99 new_references.attribute16 := x_attribute16;
100 new_references.attribute17 := x_attribute17;
101 new_references.attribute18 := x_attribute18;
102 new_references.attribute19 := x_attribute19;
103 new_references.attribute20 := x_attribute20;
104
105 IF (p_action = 'UPDATE') THEN
106 new_references.creation_date := old_references.creation_date;
107 new_references.created_by := old_references.created_by;
108 ELSE
109 new_references.creation_date := x_creation_date;
110 new_references.created_by := x_created_by;
111 END IF;
112
113 new_references.last_update_date := x_last_update_date;
114 new_references.last_updated_by := x_last_updated_by;
115 new_references.last_update_login := x_last_update_login;
116
117 END set_column_values;
118
119
120 PROCEDURE check_parent_existance AS
121 /*
122 || Created By : [email protected]
123 || Created On : 28-NOV-2002
124 || Purpose : Checks for the existance of Parent records.
125 || Known limitations, enhancements or remarks :
126 || Change History :
127 || Who When What
128 || (reverse chronological order - newest change first)
129 */
130 BEGIN
131
132 IF (((old_references.visa_id = new_references.visa_id)) OR
133 ((new_references.visa_id IS NULL))) THEN
134 NULL;
135 ELSIF NOT igs_pe_visa_pkg.get_pk_for_validation (
136 new_references.visa_id
137 ) THEN
138 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
139 igs_ge_msg_stack.add;
140 app_exception.raise_exception;
141 END IF;
142
143 END check_parent_existance;
144
145 PROCEDURE beforerowinsertupdate(p_inserting BOOLEAN,p_updating BOOLEAN) AS
146 /*
147 || Created By : pkpatel
148 || Created On : 6-Jun-2005
149 || Purpose : Handles the Unique Constraint logic defined for the columns.
150 || Known limitations, enhancements or remarks :
151 || Change History :
152 || Who When What
153 || (reverse chronological order - newest change first)
154 */
155 CURSOR visa_dtl_cur (cp_visa_id igs_pe_visa.visa_id%TYPE) IS
156 SELECT visa_issue_date, visa_expiry_date
157 FROM igs_pe_visa
158 WHERE visa_id = cp_visa_id;
159
160 visa_dtl_rec visa_dtl_cur%ROWTYPE;
161
162 BEGIN
163 IF p_inserting THEN
164 OPEN visa_dtl_cur(new_references.visa_id);
165 FETCH visa_dtl_cur INTO visa_dtl_rec;
166 CLOSE visa_dtl_cur;
167
168 IF (new_references.visit_start_date NOT BETWEEN visa_dtl_rec.visa_issue_date AND visa_dtl_rec.visa_expiry_date) OR
169 (new_references.visit_end_date IS NOT NULL AND
170 new_references.visit_end_date NOT BETWEEN visa_dtl_rec.visa_issue_date AND visa_dtl_rec.visa_expiry_date+30) THEN
171 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PE_POE_VISA_OVERLAP');
172 FND_MESSAGE.SET_TOKEN('VISA_ISSUE', visa_dtl_rec.visa_issue_date);
173 FND_MESSAGE.SET_TOKEN('VISA_EXP', visa_dtl_rec.visa_expiry_date + 30);
174 IGS_GE_MSG_STACK.ADD;
175 APP_EXCEPTION.RAISE_EXCEPTION;
176 END IF;
177 END IF;
178
179 IF p_updating THEN
180 IF ((new_references.visit_start_date <> old_references.visit_start_date) OR
181 (new_references.visit_end_date IS NOT NULL AND
182 new_references.visit_end_date <> NVL(old_references.visit_end_date,TO_DATE('01/01/1000','DD/MM/YYYY')))) THEN
183
184 OPEN visa_dtl_cur(new_references.visa_id);
185 FETCH visa_dtl_cur INTO visa_dtl_rec;
186 CLOSE visa_dtl_cur;
187
188 IF (new_references.visit_start_date NOT BETWEEN visa_dtl_rec.visa_issue_date AND visa_dtl_rec.visa_expiry_date) OR
189 (new_references.visit_end_date NOT BETWEEN visa_dtl_rec.visa_issue_date AND visa_dtl_rec.visa_expiry_date+30) THEN
190 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PE_POE_VISA_OVERLAP');
191 FND_MESSAGE.SET_TOKEN('VISA_ISSUE', visa_dtl_rec.visa_issue_date);
192 FND_MESSAGE.SET_TOKEN('VISA_EXP', visa_dtl_rec.visa_expiry_date + 30);
193 IGS_GE_MSG_STACK.ADD;
194 APP_EXCEPTION.RAISE_EXCEPTION;
195 END IF;
196 END IF;
197 END IF;
198
199 IF p_inserting OR p_updating THEN
200 IF new_references.visit_start_date > new_references.visit_end_date THEN
201 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PE_FROM_DT_GRT_TO_DATE');
202 IGS_GE_MSG_STACK.ADD;
203 APP_EXCEPTION.RAISE_EXCEPTION;
204 END IF;
205 END IF;
206 END beforerowinsertupdate;
207
208
209 FUNCTION get_pk_for_validation (
210 x_port_of_entry IN VARCHAR2,
211 x_cntry_entry_form_num IN VARCHAR2
212 ) RETURN BOOLEAN AS
213 /*
214 || Created By : [email protected]
215 || Created On : 28-NOV-2002
216 || Purpose : Validates the Primary Key of the table.
217 || Known limitations, enhancements or remarks :
218 || Change History :
219 || Who When What
220 || (reverse chronological order - newest change first)
221 */
222 CURSOR cur_rowid IS
223 SELECT rowid
224 FROM igs_pe_visit_histry
225 WHERE port_of_entry = x_port_of_entry
226 AND cntry_entry_form_num = x_cntry_entry_form_num
227 FOR UPDATE NOWAIT;
228
229 lv_rowid cur_rowid%RowType;
230
231 BEGIN
232
233 OPEN cur_rowid;
234 FETCH cur_rowid INTO lv_rowid;
235 IF (cur_rowid%FOUND) THEN
236 CLOSE cur_rowid;
237 RETURN(TRUE);
238 ELSE
239 CLOSE cur_rowid;
240 RETURN(FALSE);
241 END IF;
242
243 END get_pk_for_validation;
244
245
246 PROCEDURE get_fk_igs_pe_visa (
247 x_visa_id IN NUMBER
248 ) AS
249 /*
250 || Created By : [email protected]
251 || Created On : 28-NOV-2002
252 || Purpose : Validates the Foreign Keys for the table.
253 || Known limitations, enhancements or remarks :
254 || Change History :
255 || Who When What
256 || (reverse chronological order - newest change first)
257 */
258 CURSOR cur_rowid IS
259 SELECT rowid
260 FROM igs_pe_visit_histry
261 WHERE ((visa_id = x_visa_id));
262
263 lv_rowid cur_rowid%RowType;
264
265 BEGIN
266
267 OPEN cur_rowid;
268 FETCH cur_rowid INTO lv_rowid;
269 IF (cur_rowid%FOUND) THEN
270 CLOSE cur_rowid;
271 fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
272 igs_ge_msg_stack.add;
273 app_exception.raise_exception;
274 RETURN;
275 END IF;
276 CLOSE cur_rowid;
277
278 END get_fk_igs_pe_visa;
279
280
281 PROCEDURE before_dml (
282 p_action IN VARCHAR2,
283 x_rowid IN VARCHAR2,
284 x_port_of_entry IN VARCHAR2,
285 x_cntry_entry_form_num IN VARCHAR2,
286 x_visa_id IN NUMBER,
287 x_visit_start_date IN DATE,
288 x_visit_end_date IN DATE,
289 x_remarks IN VARCHAR2,
290 x_attribute_category IN VARCHAR2,
291 x_attribute1 IN VARCHAR2,
292 x_attribute2 IN VARCHAR2,
293 x_attribute3 IN VARCHAR2,
294 x_attribute4 IN VARCHAR2,
295 x_attribute5 IN VARCHAR2,
296 x_attribute6 IN VARCHAR2,
297 x_attribute7 IN VARCHAR2,
298 x_attribute8 IN VARCHAR2,
299 x_attribute9 IN VARCHAR2,
300 x_attribute10 IN VARCHAR2,
301 x_attribute11 IN VARCHAR2,
302 x_attribute12 IN VARCHAR2,
303 x_attribute13 IN VARCHAR2,
304 x_attribute14 IN VARCHAR2,
305 x_attribute15 IN VARCHAR2,
306 x_attribute16 IN VARCHAR2,
307 x_attribute17 IN VARCHAR2,
308 x_attribute18 IN VARCHAR2,
309 x_attribute19 IN VARCHAR2,
310 x_attribute20 IN VARCHAR2,
311 x_creation_date IN DATE,
312 x_created_by IN NUMBER,
313 x_last_update_date IN DATE,
314 x_last_updated_by IN NUMBER,
315 x_last_update_login IN NUMBER
316 ) AS
317 /*
318 || Created By : [email protected]
319 || Created On : 28-NOV-2002
320 || Purpose : Initialises the columns, Checks Constraints, Calls the
321 || Trigger Handlers for the table, before any DML operation.
322 || Known limitations, enhancements or remarks :
323 || Change History :
324 || Who When What
325 || (reverse chronological order - newest change first)
326 */
327 BEGIN
328
329 set_column_values (
330 p_action,
331 x_rowid,
332 x_port_of_entry,
333 x_cntry_entry_form_num,
334 x_visa_id,
335 x_visit_start_date,
336 x_visit_end_date,
337 x_remarks,
338 x_attribute_category,
339 x_attribute1,
340 x_attribute2,
341 x_attribute3,
342 x_attribute4,
343 x_attribute5,
344 x_attribute6,
345 x_attribute7,
346 x_attribute8,
347 x_attribute9,
348 x_attribute10,
349 x_attribute11,
350 x_attribute12,
351 x_attribute13,
352 x_attribute14,
353 x_attribute15,
354 x_attribute16,
355 x_attribute17,
356 x_attribute18,
357 x_attribute19,
358 x_attribute20,
359 x_creation_date,
360 x_created_by,
361 x_last_update_date,
362 x_last_updated_by,
363 x_last_update_login
364 );
365
366 IF (p_action = 'INSERT') THEN
367 -- Call all the procedures related to Before Insert.
368 beforerowinsertupdate(TRUE,FALSE);
369 IF ( get_pk_for_validation(
370 new_references.port_of_entry,
371 new_references.cntry_entry_form_num
372 )
373 ) THEN
374 fnd_message.set_name('IGS','IGS_PE_PORT_DUP_EXISTS');
375 igs_ge_msg_stack.add;
376 app_exception.raise_exception;
377 END IF;
378 check_parent_existance;
379 ELSIF (p_action = 'UPDATE') THEN
380 -- Call all the procedures related to Before Update.
381 beforerowinsertupdate(FALSE,TRUE);
382 check_parent_existance;
383 ELSIF (p_action = 'VALIDATE_INSERT') THEN
384 -- Call all the procedures related to Before Insert.
385 IF ( get_pk_for_validation (
386 new_references.port_of_entry,
387 new_references.cntry_entry_form_num
388 )
389 ) THEN
390 fnd_message.set_name('IGS','IGS_PE_PORT_DUP_EXISTS');
391 igs_ge_msg_stack.add;
392 app_exception.raise_exception;
393 END IF;
394 END IF;
395
396 END before_dml;
397
398 PROCEDURE afterrowinsertupdate(
399 p_inserting IN BOOLEAN,
400 p_updating IN BOOLEAN,
401 p_deleting IN BOOLEAN
402 ) AS
403 ------------------------------------------------------------------------------------------
404 --Created by : pkpatel
405 --Date created: 24-FEB-2003
406 --
407 --Purpose:Bug 2783882. Moved the overlap validation from library post-forms-commit
408 --
409 --Known limitations/enhancements and/or remarks:
410 --
411 --Change History:
412 --Who When What
413 ----------------------------------------------------------------------------------------------
414
415 CURSOR c_visit_hist_overlap(cp_person_id igs_pe_visa.person_id%TYPE) IS
416 SELECT count(1)
417 FROM igs_pe_visit_histry_v a ,igs_pe_visit_histry_v b
418 WHERE a.person_id = cp_person_id AND
419 a.person_id = b.person_id AND
420 a.row_id <> b.row_id AND
421 NVL(a.visit_end_date,TO_DATE('4712/12/31','YYYY/MM/DD')) >= b.visit_start_date AND
422 NVL(a.visit_end_date,TO_DATE('4712/12/31','YYYY/MM/DD')) <= NVL(b.visit_end_date,TO_DATE('4712/12/31','YYYY/MM/DD'));
423
424 CURSOR person_id_cur(cp_visa_id igs_pe_visit_histry.visa_id%TYPE) IS
425 SELECT person_id
426 FROM igs_pe_visa
427 WHERE visa_id = cp_visa_id;
428
429 l_count NUMBER(1);
430 l_person_id igs_pe_visa.person_id%TYPE;
431
432 BEGIN
433 OPEN person_id_cur(new_references.visa_id);
434 FETCH person_id_cur INTO l_person_id;
435 CLOSE person_id_cur;
436
437 OPEN c_visit_hist_overlap(l_person_id);
438 FETCH c_visit_hist_overlap INTO l_count;
439 CLOSE c_visit_hist_overlap;
440
441 IF l_count > 0 THEN
442 FND_MESSAGE.SET_NAME('IGS','IGS_PE_PORT_DATE_OVERLAP');
443 IGS_GE_MSG_STACK.ADD;
444 APP_EXCEPTION.RAISE_EXCEPTION;
445 END IF;
446
447 END afterrowinsertupdate;
448
449 PROCEDURE After_DML (
450 p_action IN VARCHAR2,
451 x_rowid IN VARCHAR2
452 ) as
453 ------------------------------------------------------------------------------------------
454 --Created by : pkpatel
455 --Date created: 24-FEB-2003
456 --
457 --Purpose:Bug 2783882.
458 --
459 --Known limitations/enhancements and/or remarks:
460 --
461 --Change History:
462 --Who When What
463 ----------------------------------------------------------------------------------------------
464 BEGIN
465 l_rowid := x_rowid;
466 IF (p_action = 'INSERT') THEN
467 -- Call all the procedures related to After Insert.
468 AfterRowInsertUpdate (
469 p_inserting => TRUE,
470 p_updating => FALSE,
471 p_deleting => FALSE
472 );
473 ELSIF (p_action = 'UPDATE') THEN
474 -- Call all the procedures related to After Update.
475 AfterRowInsertUpdate (
476 p_inserting => FALSE,
477 p_updating => TRUE,
478 p_deleting => FALSE
479 );
480 ELSIF (p_action = 'DELETE') THEN
481 -- Call all the procedures related to After Delete.
482 Null;
483 END IF;
484 END After_DML;
485
486 PROCEDURE insert_row (
487 x_rowid IN OUT NOCOPY VARCHAR2,
488 x_port_of_entry IN VARCHAR2,
489 x_cntry_entry_form_num IN VARCHAR2,
490 x_visa_id IN NUMBER,
491 x_visit_start_date IN DATE,
492 x_visit_end_date IN DATE,
493 x_remarks IN VARCHAR2,
494 x_attribute_category IN VARCHAR2,
495 x_attribute1 IN VARCHAR2,
496 x_attribute2 IN VARCHAR2,
497 x_attribute3 IN VARCHAR2,
498 x_attribute4 IN VARCHAR2,
499 x_attribute5 IN VARCHAR2,
500 x_attribute6 IN VARCHAR2,
501 x_attribute7 IN VARCHAR2,
502 x_attribute8 IN VARCHAR2,
503 x_attribute9 IN VARCHAR2,
504 x_attribute10 IN VARCHAR2,
505 x_attribute11 IN VARCHAR2,
506 x_attribute12 IN VARCHAR2,
507 x_attribute13 IN VARCHAR2,
508 x_attribute14 IN VARCHAR2,
509 x_attribute15 IN VARCHAR2,
510 x_attribute16 IN VARCHAR2,
511 x_attribute17 IN VARCHAR2,
512 x_attribute18 IN VARCHAR2,
513 x_attribute19 IN VARCHAR2,
514 x_attribute20 IN VARCHAR2,
515 x_mode IN VARCHAR2
516 ) AS
517 /*
518 || Created By : [email protected]
519 || Created On : 28-NOV-2002
520 || Purpose : Handles the INSERT DML logic for the table.
521 || Known limitations, enhancements or remarks :
522 || Change History :
523 || Who When What
524 || (reverse chronological order - newest change first)
525 */
526
527 x_last_update_date DATE;
528 x_last_updated_by NUMBER;
529 x_last_update_login NUMBER;
530
531 BEGIN
532
533 x_last_update_date := SYSDATE;
534 IF (x_mode = 'I') THEN
535 x_last_updated_by := 1;
536 x_last_update_login := 0;
537 ELSIF (x_mode = 'R') THEN
538 x_last_updated_by := fnd_global.user_id;
539 IF (x_last_updated_by IS NULL) THEN
540 x_last_updated_by := -1;
541 END IF;
542 x_last_update_login := fnd_global.login_id;
543 IF (x_last_update_login IS NULL) THEN
544 x_last_update_login := -1;
545 END IF;
546 ELSE
547 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
548 igs_ge_msg_stack.add;
549 app_exception.raise_exception;
550 END IF;
551
552 before_dml(
553 p_action => 'INSERT',
554 x_rowid => x_rowid,
555 x_port_of_entry => x_port_of_entry,
556 x_cntry_entry_form_num => x_cntry_entry_form_num,
557 x_visa_id => x_visa_id,
558 x_visit_start_date => x_visit_start_date,
559 x_visit_end_date => x_visit_end_date,
560 x_remarks => x_remarks,
561 x_attribute_category => x_attribute_category,
562 x_attribute1 => x_attribute1,
563 x_attribute2 => x_attribute2,
564 x_attribute3 => x_attribute3,
565 x_attribute4 => x_attribute4,
566 x_attribute5 => x_attribute5,
567 x_attribute6 => x_attribute6,
568 x_attribute7 => x_attribute7,
569 x_attribute8 => x_attribute8,
570 x_attribute9 => x_attribute9,
571 x_attribute10 => x_attribute10,
572 x_attribute11 => x_attribute11,
573 x_attribute12 => x_attribute12,
574 x_attribute13 => x_attribute13,
575 x_attribute14 => x_attribute14,
576 x_attribute15 => x_attribute15,
577 x_attribute16 => x_attribute16,
578 x_attribute17 => x_attribute17,
579 x_attribute18 => x_attribute18,
580 x_attribute19 => x_attribute19,
581 x_attribute20 => x_attribute20,
582 x_creation_date => x_last_update_date,
583 x_created_by => x_last_updated_by,
584 x_last_update_date => x_last_update_date,
585 x_last_updated_by => x_last_updated_by,
586 x_last_update_login => x_last_update_login
587 );
588
589 INSERT INTO igs_pe_visit_histry (
590 port_of_entry,
591 cntry_entry_form_num,
592 visa_id,
593 visit_start_date,
594 visit_end_date,
595 remarks,
596 attribute_category,
597 attribute1,
598 attribute2,
599 attribute3,
600 attribute4,
601 attribute5,
602 attribute6,
603 attribute7,
604 attribute8,
605 attribute9,
606 attribute10,
607 attribute11,
608 attribute12,
609 attribute13,
610 attribute14,
611 attribute15,
612 attribute16,
613 attribute17,
614 attribute18,
615 attribute19,
616 attribute20,
617 creation_date,
618 created_by,
619 last_update_date,
620 last_updated_by,
621 last_update_login
622 ) VALUES (
623 new_references.port_of_entry,
624 new_references.cntry_entry_form_num,
625 new_references.visa_id,
626 new_references.visit_start_date,
627 new_references.visit_end_date,
628 new_references.remarks,
629 new_references.attribute_category,
630 new_references.attribute1,
631 new_references.attribute2,
632 new_references.attribute3,
633 new_references.attribute4,
634 new_references.attribute5,
635 new_references.attribute6,
636 new_references.attribute7,
637 new_references.attribute8,
638 new_references.attribute9,
639 new_references.attribute10,
640 new_references.attribute11,
641 new_references.attribute12,
642 new_references.attribute13,
643 new_references.attribute14,
644 new_references.attribute15,
645 new_references.attribute16,
646 new_references.attribute17,
647 new_references.attribute18,
648 new_references.attribute19,
649 new_references.attribute20,
650 x_last_update_date,
651 x_last_updated_by,
652 x_last_update_date,
653 x_last_updated_by,
654 x_last_update_login
655 ) RETURNING ROWID INTO x_rowid;
656
657 After_DML(
658 p_action => 'INSERT',
659 x_rowid => X_ROWID
660 );
661
662 END insert_row;
663
664
665 PROCEDURE lock_row (
666 x_rowid IN VARCHAR2,
667 x_port_of_entry IN VARCHAR2,
668 x_cntry_entry_form_num IN VARCHAR2,
669 x_visa_id IN NUMBER,
670 x_visit_start_date IN DATE,
671 x_visit_end_date IN DATE,
672 x_remarks IN VARCHAR2,
673 x_attribute_category IN VARCHAR2,
674 x_attribute1 IN VARCHAR2,
675 x_attribute2 IN VARCHAR2,
676 x_attribute3 IN VARCHAR2,
677 x_attribute4 IN VARCHAR2,
678 x_attribute5 IN VARCHAR2,
679 x_attribute6 IN VARCHAR2,
680 x_attribute7 IN VARCHAR2,
681 x_attribute8 IN VARCHAR2,
682 x_attribute9 IN VARCHAR2,
683 x_attribute10 IN VARCHAR2,
684 x_attribute11 IN VARCHAR2,
685 x_attribute12 IN VARCHAR2,
686 x_attribute13 IN VARCHAR2,
687 x_attribute14 IN VARCHAR2,
688 x_attribute15 IN VARCHAR2,
689 x_attribute16 IN VARCHAR2,
690 x_attribute17 IN VARCHAR2,
691 x_attribute18 IN VARCHAR2,
692 x_attribute19 IN VARCHAR2,
693 x_attribute20 IN VARCHAR2
694 ) AS
695 /*
696 || Created By : [email protected]
697 || Created On : 28-NOV-2002
698 || Purpose : Handles the LOCK mechanism for the table.
699 || Known limitations, enhancements or remarks :
700 || Change History :
701 || Who When What
702 || (reverse chronological order - newest change first)
703 */
704 CURSOR c1 IS
705 SELECT
706 visa_id,
707 visit_start_date,
708 visit_end_date,
709 remarks,
710 attribute_category,
711 attribute1,
712 attribute2,
713 attribute3,
714 attribute4,
715 attribute5,
716 attribute6,
717 attribute7,
718 attribute8,
719 attribute9,
720 attribute10,
721 attribute11,
722 attribute12,
723 attribute13,
724 attribute14,
725 attribute15,
726 attribute16,
727 attribute17,
728 attribute18,
729 attribute19,
730 attribute20
731 FROM igs_pe_visit_histry
732 WHERE rowid = x_rowid
733 FOR UPDATE NOWAIT;
734
735 tlinfo c1%ROWTYPE;
736
737 BEGIN
738
739 OPEN c1;
740 FETCH c1 INTO tlinfo;
741 IF (c1%notfound) THEN
742 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
743 igs_ge_msg_stack.add;
744 CLOSE c1;
745 app_exception.raise_exception;
746 RETURN;
747 END IF;
748 CLOSE c1;
749
750 IF (
751 (tlinfo.visa_id = x_visa_id)
752 AND (tlinfo.visit_start_date = x_visit_start_date)
753 AND ((tlinfo.visit_end_date = x_visit_end_date) OR ((tlinfo.visit_end_date IS NULL) AND (X_visit_end_date IS NULL)))
754 AND ((tlinfo.remarks = x_remarks) OR ((tlinfo.remarks IS NULL) AND (X_remarks IS NULL)))
755 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
756 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
757 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
758 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
759 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
760 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
761 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
762 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
763 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
764 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
765 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
766 AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (X_attribute11 IS NULL)))
767 AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (X_attribute12 IS NULL)))
768 AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (X_attribute13 IS NULL)))
769 AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (X_attribute14 IS NULL)))
770 AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (X_attribute15 IS NULL)))
771 AND ((tlinfo.attribute16 = x_attribute16) OR ((tlinfo.attribute16 IS NULL) AND (X_attribute16 IS NULL)))
772 AND ((tlinfo.attribute17 = x_attribute17) OR ((tlinfo.attribute17 IS NULL) AND (X_attribute17 IS NULL)))
773 AND ((tlinfo.attribute18 = x_attribute18) OR ((tlinfo.attribute18 IS NULL) AND (X_attribute18 IS NULL)))
774 AND ((tlinfo.attribute19 = x_attribute19) OR ((tlinfo.attribute19 IS NULL) AND (X_attribute19 IS NULL)))
775 AND ((tlinfo.attribute20 = x_attribute20) OR ((tlinfo.attribute20 IS NULL) AND (X_attribute20 IS NULL)))
776 ) THEN
777 NULL;
778 ELSE
779 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
780 igs_ge_msg_stack.add;
781 app_exception.raise_exception;
782 END IF;
783
784 RETURN;
785
786 END lock_row;
787
788
789 PROCEDURE update_row (
790 x_rowid IN VARCHAR2,
791 x_port_of_entry IN VARCHAR2,
792 x_cntry_entry_form_num IN VARCHAR2,
793 x_visa_id IN NUMBER,
794 x_visit_start_date IN DATE,
795 x_visit_end_date IN DATE,
796 x_remarks IN VARCHAR2,
797 x_attribute_category IN VARCHAR2,
798 x_attribute1 IN VARCHAR2,
799 x_attribute2 IN VARCHAR2,
800 x_attribute3 IN VARCHAR2,
801 x_attribute4 IN VARCHAR2,
802 x_attribute5 IN VARCHAR2,
803 x_attribute6 IN VARCHAR2,
804 x_attribute7 IN VARCHAR2,
805 x_attribute8 IN VARCHAR2,
806 x_attribute9 IN VARCHAR2,
807 x_attribute10 IN VARCHAR2,
808 x_attribute11 IN VARCHAR2,
809 x_attribute12 IN VARCHAR2,
810 x_attribute13 IN VARCHAR2,
811 x_attribute14 IN VARCHAR2,
812 x_attribute15 IN VARCHAR2,
813 x_attribute16 IN VARCHAR2,
814 x_attribute17 IN VARCHAR2,
815 x_attribute18 IN VARCHAR2,
816 x_attribute19 IN VARCHAR2,
817 x_attribute20 IN VARCHAR2,
818 x_mode IN VARCHAR2
819 ) AS
820 /*
821 || Created By : [email protected]
822 || Created On : 28-NOV-2002
823 || Purpose : Handles the UPDATE DML logic for the table.
824 || Known limitations, enhancements or remarks :
825 || Change History :
826 || Who When What
827 || (reverse chronological order - newest change first)
828 */
829 x_last_update_date DATE ;
830 x_last_updated_by NUMBER;
831 x_last_update_login NUMBER;
832
833 CURSOR cur_rowid IS
834 SELECT rowid
835 FROM igs_pe_visit_histry
836 WHERE port_of_entry = x_port_of_entry
837 AND cntry_entry_form_num = x_cntry_entry_form_num
838 AND rowid <> x_rowid
839 FOR UPDATE NOWAIT;
840
841 lv_rowid cur_rowid%RowType;
842
843 BEGIN
844
845 x_last_update_date := SYSDATE;
846 IF (X_MODE = 'I') THEN
847 x_last_updated_by := 1;
848 x_last_update_login := 0;
849 ELSIF (x_mode = 'R') THEN
850 x_last_updated_by := fnd_global.user_id;
851 IF x_last_updated_by IS NULL THEN
852 x_last_updated_by := -1;
853 END IF;
854 x_last_update_login := fnd_global.login_id;
855 IF (x_last_update_login IS NULL) THEN
856 x_last_update_login := -1;
857 END IF;
858 ELSE
859 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
860 igs_ge_msg_stack.add;
861 app_exception.raise_exception;
862 END IF;
863
864 before_dml(
865 p_action => 'UPDATE',
866 x_rowid => x_rowid,
867 x_port_of_entry => x_port_of_entry,
868 x_cntry_entry_form_num => x_cntry_entry_form_num,
869 x_visa_id => x_visa_id,
870 x_visit_start_date => x_visit_start_date,
871 x_visit_end_date => x_visit_end_date,
872 x_remarks => x_remarks,
873 x_attribute_category => x_attribute_category,
874 x_attribute1 => x_attribute1,
875 x_attribute2 => x_attribute2,
876 x_attribute3 => x_attribute3,
877 x_attribute4 => x_attribute4,
878 x_attribute5 => x_attribute5,
879 x_attribute6 => x_attribute6,
880 x_attribute7 => x_attribute7,
881 x_attribute8 => x_attribute8,
882 x_attribute9 => x_attribute9,
883 x_attribute10 => x_attribute10,
884 x_attribute11 => x_attribute11,
885 x_attribute12 => x_attribute12,
886 x_attribute13 => x_attribute13,
887 x_attribute14 => x_attribute14,
888 x_attribute15 => x_attribute15,
889 x_attribute16 => x_attribute16,
890 x_attribute17 => x_attribute17,
891 x_attribute18 => x_attribute18,
892 x_attribute19 => x_attribute19,
893 x_attribute20 => x_attribute20,
894 x_creation_date => x_last_update_date,
895 x_created_by => x_last_updated_by,
896 x_last_update_date => x_last_update_date,
897 x_last_updated_by => x_last_updated_by,
898 x_last_update_login => x_last_update_login
899 );
900
901 OPEN cur_rowid;
902 FETCH cur_rowid INTO lv_rowid;
903 IF cur_rowid%FOUND THEN
904 CLOSE cur_rowid;
905 fnd_message.set_name('IGS','IGS_PE_PORT_DUP_EXISTS');
906 igs_ge_msg_stack.add;
907 app_exception.raise_exception;
908 END IF;
909
910 CLOSE cur_rowid;
911
912 UPDATE igs_pe_visit_histry
913 SET
914 cntry_entry_form_num = new_references.cntry_entry_form_num,
915 port_of_entry = new_references.port_of_entry,
916 visa_id = new_references.visa_id,
917 visit_start_date = new_references.visit_start_date,
918 visit_end_date = new_references.visit_end_date,
919 remarks = new_references.remarks,
920 attribute_category = new_references.attribute_category,
921 attribute1 = new_references.attribute1,
922 attribute2 = new_references.attribute2,
923 attribute3 = new_references.attribute3,
924 attribute4 = new_references.attribute4,
925 attribute5 = new_references.attribute5,
926 attribute6 = new_references.attribute6,
927 attribute7 = new_references.attribute7,
928 attribute8 = new_references.attribute8,
929 attribute9 = new_references.attribute9,
930 attribute10 = new_references.attribute10,
931 attribute11 = new_references.attribute11,
932 attribute12 = new_references.attribute12,
933 attribute13 = new_references.attribute13,
934 attribute14 = new_references.attribute14,
935 attribute15 = new_references.attribute15,
936 attribute16 = new_references.attribute16,
937 attribute17 = new_references.attribute17,
938 attribute18 = new_references.attribute18,
939 attribute19 = new_references.attribute19,
940 attribute20 = new_references.attribute20,
941 last_update_date = x_last_update_date,
942 last_updated_by = x_last_updated_by,
943 last_update_login = x_last_update_login
944 WHERE rowid = x_rowid;
945
946 IF (SQL%NOTFOUND) THEN
947 RAISE NO_DATA_FOUND;
948 END IF;
949
950 After_DML(
951 p_action => 'UPDATE',
952 x_rowid => X_ROWID
953 );
954
955 END update_row;
956
957
958 PROCEDURE add_row (
959 x_rowid IN OUT NOCOPY VARCHAR2,
960 x_port_of_entry IN VARCHAR2,
961 x_cntry_entry_form_num IN VARCHAR2,
962 x_visa_id IN NUMBER,
963 x_visit_start_date IN DATE,
964 x_visit_end_date IN DATE,
965 x_remarks IN VARCHAR2,
966 x_attribute_category IN VARCHAR2,
967 x_attribute1 IN VARCHAR2,
968 x_attribute2 IN VARCHAR2,
969 x_attribute3 IN VARCHAR2,
970 x_attribute4 IN VARCHAR2,
971 x_attribute5 IN VARCHAR2,
972 x_attribute6 IN VARCHAR2,
973 x_attribute7 IN VARCHAR2,
974 x_attribute8 IN VARCHAR2,
975 x_attribute9 IN VARCHAR2,
976 x_attribute10 IN VARCHAR2,
977 x_attribute11 IN VARCHAR2,
978 x_attribute12 IN VARCHAR2,
979 x_attribute13 IN VARCHAR2,
980 x_attribute14 IN VARCHAR2,
981 x_attribute15 IN VARCHAR2,
982 x_attribute16 IN VARCHAR2,
983 x_attribute17 IN VARCHAR2,
984 x_attribute18 IN VARCHAR2,
985 x_attribute19 IN VARCHAR2,
986 x_attribute20 IN VARCHAR2,
987 x_mode IN VARCHAR2
988 ) AS
989 /*
990 || Created By : [email protected]
991 || Created On : 28-NOV-2002
992 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
993 || Known limitations, enhancements or remarks :
994 || Change History :
995 || Who When What
996 || (reverse chronological order - newest change first)
997 */
998 CURSOR c1 IS
999 SELECT rowid
1000 FROM igs_pe_visit_histry
1001 WHERE port_of_entry = x_port_of_entry
1002 AND cntry_entry_form_num = x_cntry_entry_form_num;
1003
1004 BEGIN
1005
1006 OPEN c1;
1007 FETCH c1 INTO x_rowid;
1008 IF (c1%NOTFOUND) THEN
1009 CLOSE c1;
1010
1011 insert_row (
1012 x_rowid,
1013 x_port_of_entry,
1014 x_cntry_entry_form_num,
1015 x_visa_id,
1016 x_visit_start_date,
1017 x_visit_end_date,
1018 x_remarks,
1019 x_attribute_category,
1020 x_attribute1,
1021 x_attribute2,
1022 x_attribute3,
1023 x_attribute4,
1024 x_attribute5,
1025 x_attribute6,
1026 x_attribute7,
1027 x_attribute8,
1028 x_attribute9,
1029 x_attribute10,
1030 x_attribute11,
1031 x_attribute12,
1032 x_attribute13,
1033 x_attribute14,
1034 x_attribute15,
1035 x_attribute16,
1036 x_attribute17,
1037 x_attribute18,
1038 x_attribute19,
1039 x_attribute20,
1040 x_mode
1041 );
1042 RETURN;
1043 END IF;
1044 CLOSE c1;
1045
1046 update_row (
1047 x_rowid,
1048 x_port_of_entry,
1049 x_cntry_entry_form_num,
1050 x_visa_id,
1051 x_visit_start_date,
1052 x_visit_end_date,
1053 x_remarks,
1054 x_attribute_category,
1055 x_attribute1,
1056 x_attribute2,
1057 x_attribute3,
1058 x_attribute4,
1059 x_attribute5,
1060 x_attribute6,
1061 x_attribute7,
1062 x_attribute8,
1063 x_attribute9,
1064 x_attribute10,
1065 x_attribute11,
1066 x_attribute12,
1067 x_attribute13,
1068 x_attribute14,
1069 x_attribute15,
1070 x_attribute16,
1071 x_attribute17,
1072 x_attribute18,
1073 x_attribute19,
1074 x_attribute20,
1075 x_mode
1076 );
1077
1078 END add_row;
1079
1080
1081 PROCEDURE delete_row (
1082 x_rowid IN VARCHAR2
1083 ) AS
1084 /*
1085 || Created By : [email protected]
1086 || Created On : 28-NOV-2002
1087 || Purpose : Handles the DELETE DML logic for the table.
1088 || Known limitations, enhancements or remarks :
1089 || Change History :
1090 || Who When What
1091 || (reverse chronological order - newest change first)
1092 */
1093 BEGIN
1094
1095 before_dml (
1096 p_action => 'DELETE',
1097 x_rowid => x_rowid
1098 );
1099
1100 DELETE FROM igs_pe_visit_histry
1101 WHERE rowid = x_rowid;
1102
1103 IF (SQL%NOTFOUND) THEN
1104 RAISE NO_DATA_FOUND;
1105 END IF;
1106
1107 END delete_row;
1108
1109
1110 END igs_pe_visit_histry_pkg;