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