[Home] [Help]
PACKAGE BODY: APPS.IGF_SE_AUTH_PKG
Source
1 PACKAGE BODY igf_se_auth_pkg AS
2 /* $Header: IGFSI04B.pls 120.0 2005/06/03 14:28:02 appldev noship $ */
3 /*=======================================================================+
4 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | |
8 | DESCRIPTION |
9 | PL/SQL body for package: igf_se_auth_PKG
10 | |
11 | NOTES |
12 | |
13 | This package has a flag on the end of some of the procedures called |
14 | X_MODE. Pass either 'R' for runtime, or 'I' for Install-time. |
15 | This will control how the who columns are filled in; If you are |
16 | running in runtime mode, they are taken from the profiles, whereas in |
17 | install-time mode they get defaulted with special values to indicate |
18 | that they were inserted by datamerge. |
19 | |
20 | The ADD_ROW routine will see whether a row exists by selecting |
21 | based on the primary key, and updates the row if it exists, |
22 | or inserts the row if it doesn't already exist. |
23 | |
24 | This module is called by AutoInstall (afplss.drv) on install and |
25 | upgrade. The WHENEVER SQLERROR and EXIT (at bottom) are required. |
26 | |
27 | HISTORY |
28 |veramach July 2004 Obsoleted min_hr_rate,max_hr_rate, |
29 | govt_share_perct,ld_cal_type, |
30 | ld_sequence_number |
31 | Added award_id,authorization_date, |
32 | notification_date |
33 *=======================================================================*/
34
35 l_rowid VARCHAR2(25);
36 old_references igf_se_auth%ROWTYPE;
37 new_references igf_se_auth%ROWTYPE;
38
39 PROCEDURE set_column_values (
40 p_action IN VARCHAR2,
41 x_rowid IN VARCHAR2 DEFAULT NULL,
42 x_sequence_no IN NUMBER DEFAULT NULL,
43 x_auth_id IN NUMBER DEFAULT NULL,
44 x_flag IN VARCHAR2 DEFAULT NULL,
45 x_person_id IN NUMBER DEFAULT NULL,
46 x_first_name IN VARCHAR2 DEFAULT NULL,
47 x_last_name IN VARCHAR2 DEFAULT NULL,
48 x_address1 IN VARCHAR2 DEFAULT NULL,
49 x_address2 IN VARCHAR2 DEFAULT NULL,
50 x_address3 IN VARCHAR2 DEFAULT NULL,
51 x_address4 IN VARCHAR2 DEFAULT NULL,
52 x_city IN VARCHAR2 DEFAULT NULL,
53 x_state IN VARCHAR2 DEFAULT NULL,
54 x_province IN VARCHAR2 DEFAULT NULL,
55 x_county IN VARCHAR2 DEFAULT NULL,
56 x_country IN VARCHAR2 DEFAULT NULL,
57 x_sex IN VARCHAR2 DEFAULT NULL,
58 x_birth_dt IN DATE DEFAULT NULL,
59 x_ssn_no IN VARCHAR2 DEFAULT NULL,
60 x_marital_status IN VARCHAR2 DEFAULT NULL,
61 x_visa_type IN VARCHAR2 DEFAULT NULL,
62 x_visa_category IN VARCHAR2 DEFAULT NULL,
63 x_visa_number IN VARCHAR2 DEFAULT NULL,
64 x_visa_expiry_dt IN DATE DEFAULT NULL,
65 x_entry_date IN DATE DEFAULT NULL,
66 x_fund_id IN NUMBER DEFAULT NULL,
67 x_threshold_perct IN NUMBER DEFAULT NULL,
68 x_threshold_value IN NUMBER DEFAULT NULL,
69 x_accepted_amnt IN NUMBER DEFAULT NULL,
70 x_aw_cal_type IN VARCHAR2 DEFAULT NULL,
71 x_aw_sequence_number IN NUMBER DEFAULT NULL,
72 x_creation_date IN DATE DEFAULT NULL,
73 x_created_by IN NUMBER DEFAULT NULL,
74 x_last_update_date IN DATE DEFAULT NULL,
75 x_last_updated_by IN NUMBER DEFAULT NULL,
76 x_last_update_login IN NUMBER DEFAULT NULL,
77 x_award_id IN NUMBER DEFAULT NULL,
78 x_authorization_date IN DATE DEFAULT NULL,
79 x_notification_date IN DATE DEFAULT NULL
80 ) AS
81 /*
82 || Created By : ssawhney
83 || Created On : 31-DEC-2001
84 || Purpose : Initialises the Old and New references for the columns of the table.
85 || Known limitations, enhancements or remarks :
86 || Change History :
87 || Who When What
88 || (reverse chronological order - newest change first)
89 */
90
91 CURSOR cur_old_ref_values IS
92 SELECT *
93 FROM IGF_SE_AUTH
94 WHERE rowid = x_rowid;
95
96 BEGIN
97
98 l_rowid := x_rowid;
99
100 -- Code for setting the Old and New Reference Values.
101 -- Populate Old Values.
102 OPEN cur_old_ref_values;
103 FETCH cur_old_ref_values INTO old_references;
104 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
105 CLOSE cur_old_ref_values;
106 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
107 igs_ge_msg_stack.add;
108 app_exception.raise_exception;
109 RETURN;
110 END IF;
111 CLOSE cur_old_ref_values;
112
113 -- Populate New Values.
114 new_references.sequence_no := x_sequence_no;
115 new_references.auth_id := x_auth_id;
116 new_references.flag := x_flag;
117 new_references.person_id := x_person_id;
118 new_references.first_name := x_first_name;
119 new_references.last_name := x_last_name;
120 new_references.address1 := x_address1;
121 new_references.address2 := x_address2;
122 new_references.address3 := x_address3;
123 new_references.address4 := x_address4;
124 new_references.city := x_city;
125 new_references.state := x_state;
126 new_references.province := x_province;
127 new_references.county := x_county;
128 new_references.country := x_country;
129 new_references.sex := x_sex;
130 new_references.birth_dt := x_birth_dt;
131 new_references.ssn_no := x_ssn_no;
132 new_references.marital_status := x_marital_status;
133 new_references.visa_type := x_visa_type;
134 new_references.visa_category := x_visa_category;
135 new_references.visa_number := x_visa_number;
136 new_references.visa_expiry_dt := x_visa_expiry_dt;
137 new_references.entry_date := x_entry_date;
138 new_references.fund_id := x_fund_id;
139 new_references.threshold_perct := x_threshold_perct;
140 new_references.threshold_value := x_threshold_value;
141 new_references.accepted_amnt := x_accepted_amnt;
142 new_references.aw_cal_type := x_aw_cal_type;
143 new_references.aw_sequence_number := x_aw_sequence_number;
144 new_references.award_id := x_award_id;
145 new_references.authorization_date := x_authorization_date;
146 new_references.notification_date := x_notification_date;
147
148 IF (p_action = 'UPDATE') THEN
149 new_references.creation_date := old_references.creation_date;
150 new_references.created_by := old_references.created_by;
151 ELSE
152 new_references.creation_date := x_creation_date;
153 new_references.created_by := x_created_by;
154 END IF;
155
156 new_references.last_update_date := x_last_update_date;
157 new_references.last_updated_by := x_last_updated_by;
158 new_references.last_update_login := x_last_update_login;
159
160 END set_column_values;
161
162
163 PROCEDURE check_constraints (
164 column_name IN VARCHAR2 DEFAULT NULL,
165 column_value IN VARCHAR2 DEFAULT NULL
166 ) AS
167 /*
168 || Created By : ssawhney
169 || Created On : 02-JAN-2002
170 || Purpose : Handles the Check Constraint logic for the the columns.
171 || Known limitations, enhancements or remarks :
172 || Change History :
173 || Who When What
174 || (reverse chronological order - newest change first)
175 */
176 BEGIN
177
178 IF (column_name IS NULL) THEN
179 NULL;
180 ELSIF (UPPER(column_name) = 'FLAG') THEN
181 new_references.flag := column_value;
182 END IF;
183
184 IF (UPPER(column_name) = 'FLAG' OR column_name IS NULL) THEN
185 IF NOT (new_references.flag IN ('A','I')) THEN
186 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
187 igs_ge_msg_stack.add;
188 app_exception.raise_exception;
189 END IF;
190 END IF;
191
192 END check_constraints;
193
194
195
196 FUNCTION get_pk_for_validation (
197 x_sequence_no IN NUMBER
198 ) RETURN BOOLEAN AS
199 /*
200 || Created By : ssawhney
201 || Created On : 31-DEC-2001
202 || Purpose : Validates the Primary Key of the table.
203 || Known limitations, enhancements or remarks :
204 || Change History :
205 || Who When What
206 || (reverse chronological order - newest change first)
207 */
208 CURSOR cur_rowid IS
209 SELECT rowid
210 FROM igf_se_auth
211 WHERE sequence_no = x_sequence_no
212 FOR UPDATE NOWAIT;
213
214 lv_rowid cur_rowid%RowType;
215
216 BEGIN
217
218 OPEN cur_rowid;
219 FETCH cur_rowid INTO lv_rowid;
220 IF (cur_rowid%FOUND) THEN
221 CLOSE cur_rowid;
222 RETURN(TRUE);
223 ELSE
224 CLOSE cur_rowid;
225 RETURN(FALSE);
226 END IF;
227
228 END get_pk_for_validation;
229
230
231
232 PROCEDURE before_dml (
233 p_action IN VARCHAR2,
234 x_rowid IN VARCHAR2 DEFAULT NULL,
235 x_sequence_no IN NUMBER DEFAULT NULL,
236 x_auth_id IN NUMBER DEFAULT NULL,
237 x_flag IN VARCHAR2 DEFAULT NULL,
238 x_person_id IN NUMBER DEFAULT NULL,
239 x_first_name IN VARCHAR2 DEFAULT NULL,
240 x_last_name IN VARCHAR2 DEFAULT NULL,
241 x_address1 IN VARCHAR2 DEFAULT NULL,
242 x_address2 IN VARCHAR2 DEFAULT NULL,
243 x_address3 IN VARCHAR2 DEFAULT NULL,
244 x_address4 IN VARCHAR2 DEFAULT NULL,
245 x_city IN VARCHAR2 DEFAULT NULL,
246 x_state IN VARCHAR2 DEFAULT NULL,
247 x_province IN VARCHAR2 DEFAULT NULL,
248 x_county IN VARCHAR2 DEFAULT NULL,
249 x_country IN VARCHAR2 DEFAULT NULL,
250 x_sex IN VARCHAR2 DEFAULT NULL,
251 x_birth_dt IN DATE DEFAULT NULL,
252 x_ssn_no IN VARCHAR2 DEFAULT NULL,
253 x_marital_status IN VARCHAR2 DEFAULT NULL,
254 x_visa_type IN VARCHAR2 DEFAULT NULL,
255 x_visa_category IN VARCHAR2 DEFAULT NULL,
256 x_visa_number IN VARCHAR2 DEFAULT NULL,
257 x_visa_expiry_dt IN DATE DEFAULT NULL,
258 x_entry_date IN DATE DEFAULT NULL,
259 x_fund_id IN NUMBER DEFAULT NULL,
260 x_threshold_perct IN NUMBER DEFAULT NULL,
261 x_threshold_value IN NUMBER DEFAULT NULL,
262 x_accepted_amnt IN NUMBER DEFAULT NULL,
263 x_aw_cal_type IN VARCHAR2 DEFAULT NULL,
264 x_aw_sequence_number IN NUMBER DEFAULT NULL,
265 x_creation_date IN DATE DEFAULT NULL,
266 x_created_by IN NUMBER DEFAULT NULL,
267 x_last_update_date IN DATE DEFAULT NULL,
268 x_last_updated_by IN NUMBER DEFAULT NULL,
269 x_last_update_login IN NUMBER DEFAULT NULL,
270 x_award_id IN NUMBER DEFAULT NULL,
271 x_authorization_date IN DATE DEFAULT NULL,
272 x_notification_date IN DATE DEFAULT NULL
273 ) AS
274 /*
275 || Created By : ssawhney
276 || Created On : 31-DEC-2001
277 || Purpose : Initialises the columns, Checks Constraints, Calls the
278 || Trigger Handlers for the table, before any DML operation.
279 || Known limitations, enhancements or remarks :
280 || Change History :
281 || Who When What
282 || (reverse chronological order - newest change first)
283 */
284 BEGIN
285
286 set_column_values (
287 p_action,
288 x_rowid,
289 x_sequence_no,
290 x_auth_id,
291 x_flag,
292 x_person_id,
293 x_first_name,
294 x_last_name,
295 x_address1,
296 x_address2,
297 x_address3,
298 x_address4,
299 x_city,
300 x_state,
301 x_province,
302 x_county,
303 x_country,
304 x_sex,
305 x_birth_dt,
306 x_ssn_no,
307 x_marital_status,
308 x_visa_type,
309 x_visa_category,
310 x_visa_number,
311 x_visa_expiry_dt,
312 x_entry_date,
313 x_fund_id,
314 x_threshold_perct,
315 x_threshold_value,
316 x_accepted_amnt,
317 x_aw_cal_type,
318 x_aw_sequence_number,
319 x_creation_date,
320 x_created_by,
321 x_last_update_date,
322 x_last_updated_by,
323 x_last_update_login,
324 x_award_id,
325 x_authorization_date,
326 x_notification_date
327 );
328
329 IF (p_action = 'INSERT') THEN
330 -- Call all the procedures related to Before Insert.
331 IF ( get_pk_for_validation(
332 new_references.sequence_no
333 )
334 ) THEN
335 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
336 igs_ge_msg_stack.add;
340 check_constraints;
337 app_exception.raise_exception;
338 END IF;
339 -- check_uniqueness;
341 ELSIF (p_action = 'UPDATE') THEN
342 -- Call all the procedures related to Before Update.
343 -- check_uniqueness;
344 check_constraints;
345 ELSIF (p_action = 'VALIDATE_INSERT') THEN
346 -- Call all the procedures related to Before Insert.
347 IF ( get_pk_for_validation (
348 new_references.sequence_no
349 )
350 ) THEN
351 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
352 igs_ge_msg_stack.add;
353 app_exception.raise_exception;
354 END IF;
355 -- check_uniqueness;
356 check_constraints;
357 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
358 -- check_uniqueness;
359 check_constraints;
360 END IF;
361
362 END before_dml;
363
364
365 PROCEDURE insert_row (
366 x_rowid IN OUT NOCOPY VARCHAR2,
367 x_sequence_no IN OUT NOCOPY NUMBER,
368 x_auth_id IN NUMBER,
369 x_flag IN VARCHAR2,
370 x_person_id IN NUMBER,
371 x_first_name IN VARCHAR2,
372 x_last_name IN VARCHAR2,
373 x_address1 IN VARCHAR2,
374 x_address2 IN VARCHAR2,
375 x_address3 IN VARCHAR2,
376 x_address4 IN VARCHAR2,
377 x_city IN VARCHAR2,
378 x_state IN VARCHAR2,
379 x_province IN VARCHAR2,
380 x_county IN VARCHAR2,
381 x_country IN VARCHAR2,
382 x_sex IN VARCHAR2,
383 x_birth_dt IN DATE,
384 x_ssn_no IN VARCHAR2,
385 x_marital_status IN VARCHAR2,
386 x_visa_type IN VARCHAR2,
387 x_visa_category IN VARCHAR2,
388 x_visa_number IN VARCHAR2,
389 x_visa_expiry_dt IN DATE,
390 x_entry_date IN DATE,
391 x_fund_id IN NUMBER,
392 x_threshold_perct IN NUMBER,
393 x_threshold_value IN NUMBER,
394 x_accepted_amnt IN NUMBER,
395 x_aw_cal_type IN VARCHAR2,
396 x_aw_sequence_number IN NUMBER,
397 x_mode IN VARCHAR2 DEFAULT 'R',
398 x_award_id IN NUMBER,
399 x_authorization_date IN DATE,
400 x_notification_date IN DATE
401 ) AS
402 /*
403 || Created By : ssawhney
404 || Created On : 31-DEC-2001
405 || Purpose : Handles the INSERT DML logic for the table.
406 || Known limitations, enhancements or remarks :
407 || Change History :
408 || Who When What
409 || (reverse chronological order - newest change first)
410 */
411 CURSOR c IS
412 SELECT rowid
413 FROM igf_se_auth
414 WHERE sequence_no = x_sequence_no;
415
416 x_last_update_date DATE;
417 x_last_updated_by NUMBER;
418 x_last_update_login NUMBER;
419 x_request_id NUMBER;
420 x_program_id NUMBER;
421 x_program_application_id NUMBER;
422 x_program_update_date DATE;
423
424 BEGIN
425
426 x_last_update_date := SYSDATE;
427 IF (x_mode = 'I') THEN
428 x_last_updated_by := 1;
429 x_last_update_login := 0;
430 ELSIF (x_mode = 'R') THEN
431 x_last_updated_by := fnd_global.user_id;
432 IF (x_last_updated_by IS NULL) THEN
433 x_last_updated_by := -1;
434 END IF;
435 x_last_update_login := fnd_global.login_id;
436 IF (x_last_update_login IS NULL) THEN
437 x_last_update_login := -1;
438 END IF;
439 x_request_id := fnd_global.conc_request_id;
440 x_program_id := fnd_global.conc_program_id;
441 x_program_application_id := fnd_global.prog_appl_id;
442
443 IF (x_request_id = -1) THEN
444 x_request_id := NULL;
445 x_program_id := NULL;
446 x_program_application_id := NULL;
447 x_program_update_date := NULL;
448 ELSE
449 x_program_update_date := SYSDATE;
450 END IF;
451 ELSE
452 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
453 igs_ge_msg_stack.add;
454 app_exception.raise_exception;
455 END IF;
456
457 SELECT igf_se_auth_s.NEXTVAL
458 INTO x_sequence_no
459 FROM dual;
460
461 before_dml(
462 p_action => 'INSERT',
463 x_rowid => x_rowid,
464 x_sequence_no => x_sequence_no,
465 x_auth_id => x_auth_id,
469 x_last_name => x_last_name,
466 x_flag => x_flag,
467 x_person_id => x_person_id,
468 x_first_name => x_first_name,
470 x_address1 => x_address1,
471 x_address2 => x_address2,
472 x_address3 => x_address3,
473 x_address4 => x_address4,
474 x_city => x_city,
475 x_state => x_state,
476 x_province => x_province,
477 x_county => x_county,
478 x_country => x_country,
479 x_sex => x_sex,
480 x_birth_dt => x_birth_dt,
481 x_ssn_no => x_ssn_no,
482 x_marital_status => x_marital_status,
483 x_visa_type => x_visa_type,
484 x_visa_category => x_visa_category,
485 x_visa_number => x_visa_number,
486 x_visa_expiry_dt => x_visa_expiry_dt,
487 x_entry_date => x_entry_date,
488 x_fund_id => x_fund_id,
489 x_threshold_perct => x_threshold_perct,
490 x_threshold_value => x_threshold_value,
491 x_accepted_amnt => x_accepted_amnt,
492 x_aw_cal_type => x_aw_cal_type,
493 x_aw_sequence_number => x_aw_sequence_number,
494 x_creation_date => x_last_update_date,
495 x_created_by => x_last_updated_by,
496 x_last_update_date => x_last_update_date,
497 x_last_updated_by => x_last_updated_by,
498 x_last_update_login => x_last_update_login,
499 x_award_id => x_award_id,
500 x_authorization_date => x_authorization_date,
501 x_notification_date => x_notification_date
502 );
503
504 INSERT INTO igf_se_auth (
505 sequence_no,
506 auth_id,
507 flag,
508 person_id,
509 first_name,
510 last_name,
511 address1,
512 address2,
513 address3,
514 address4,
515 city,
516 state,
517 province,
518 county,
519 country,
520 sex,
521 birth_dt,
522 ssn_no,
523 marital_status,
524 visa_type,
525 visa_category,
526 visa_number,
527 visa_expiry_dt,
528 entry_date,
529 fund_id,
530 threshold_perct,
531 threshold_value,
532 accepted_amnt,
533 aw_cal_type,
534 aw_sequence_number,
535 creation_date,
536 created_by,
537 last_update_date,
538 last_updated_by,
539 last_update_login,
540 request_id,
541 program_id,
542 program_application_id,
543 program_update_date,
544 award_id,
545 authorization_date,
546 notification_date
547 ) VALUES (
548 new_references.sequence_no,
549 new_references.auth_id,
550 new_references.flag,
551 new_references.person_id,
552 new_references.first_name,
553 new_references.last_name,
554 new_references.address1,
555 new_references.address2,
556 new_references.address3,
557 new_references.address4,
558 new_references.city,
559 new_references.state,
560 new_references.province,
561 new_references.county,
562 new_references.country,
563 new_references.sex,
564 new_references.birth_dt,
565 new_references.ssn_no,
566 new_references.marital_status,
567 new_references.visa_type,
568 new_references.visa_category,
569 new_references.visa_number,
570 new_references.visa_expiry_dt,
571 new_references.entry_date,
572 new_references.fund_id,
573 new_references.threshold_perct,
574 new_references.threshold_value,
575 new_references.accepted_amnt,
576 new_references.aw_cal_type,
577 new_references.aw_sequence_number,
578 x_last_update_date,
579 x_last_updated_by,
580 x_last_update_date,
581 x_last_updated_by,
582 x_last_update_login ,
583 x_request_id,
584 x_program_id,
585 x_program_application_id,
586 x_program_update_date,
587 new_references.award_id,
588 new_references.authorization_date,
589 new_references.notification_date
590 );
591
592 OPEN c;
593 FETCH c INTO x_rowid;
594 IF (c%NOTFOUND) THEN
595 CLOSE c;
596 RAISE NO_DATA_FOUND;
597 END IF;
598 CLOSE c;
599
600 END insert_row;
601
602
603 PROCEDURE lock_row (
604 x_rowid IN VARCHAR2,
605 x_sequence_no IN NUMBER,
606 x_auth_id IN NUMBER,
610 x_last_name IN VARCHAR2,
607 x_flag IN VARCHAR2,
608 x_person_id IN NUMBER,
609 x_first_name IN VARCHAR2,
611 x_address1 IN VARCHAR2,
612 x_address2 IN VARCHAR2,
613 x_address3 IN VARCHAR2,
614 x_address4 IN VARCHAR2,
615 x_city IN VARCHAR2,
616 x_state IN VARCHAR2,
617 x_province IN VARCHAR2,
618 x_county IN VARCHAR2,
619 x_country IN VARCHAR2,
620 x_sex IN VARCHAR2,
621 x_birth_dt IN DATE,
622 x_ssn_no IN VARCHAR2,
623 x_marital_status IN VARCHAR2,
624 x_visa_type IN VARCHAR2,
625 x_visa_category IN VARCHAR2,
626 x_visa_number IN VARCHAR2,
627 x_visa_expiry_dt IN DATE,
628 x_entry_date IN DATE,
629 x_fund_id IN NUMBER,
630 x_threshold_perct IN NUMBER,
631 x_threshold_value IN NUMBER,
632 x_accepted_amnt IN NUMBER,
633 x_aw_cal_type IN VARCHAR2,
634 x_aw_sequence_number IN NUMBER,
635 x_award_id IN NUMBER,
636 x_authorization_date IN DATE,
637 x_notification_date IN DATE
638 ) AS
639 /*
640 || Created By : ssawhney
641 || Created On : 31-DEC-2001
642 || Purpose : Handles the LOCK mechanism for the table.
643 || Known limitations, enhancements or remarks :
644 || Change History :
645 || Who When What
646 || (reverse chronological order - newest change first)
647 */
648 CURSOR c1 IS
649 SELECT
650 auth_id,
651 flag,
652 person_id,
653 first_name,
654 last_name,
655 address1,
656 address2,
657 address3,
658 address4,
659 city,
660 state,
661 province,
662 county,
663 country,
664 sex,
665 birth_dt,
666 ssn_no,
667 marital_status,
668 visa_type,
669 visa_category,
670 visa_number,
671 visa_expiry_dt,
672 entry_date,
673 fund_id,
674 threshold_perct,
675 threshold_value,
676 accepted_amnt,
677 aw_cal_type,
678 aw_sequence_number,
679 award_id,
680 authorization_date,
681 notification_date
682 FROM igf_se_auth
683 WHERE rowid = x_rowid
684 FOR UPDATE NOWAIT;
685
686 tlinfo c1%ROWTYPE;
687
688 BEGIN
689
690 OPEN c1;
691 FETCH c1 INTO tlinfo;
692 IF (c1%notfound) THEN
693 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
694 igs_ge_msg_stack.add;
695 CLOSE c1;
696 app_exception.raise_exception;
697 RETURN;
698 END IF;
699 CLOSE c1;
700
701 IF (
702 (tlinfo.auth_id = x_auth_id)
703 AND (tlinfo.flag = x_flag)
704 AND (tlinfo.person_id = x_person_id)
705 AND (tlinfo.first_name = x_first_name)
706 AND (tlinfo.last_name = x_last_name)
707 AND (tlinfo.address1 = x_address1)
708 AND ((tlinfo.address2 = x_address2) OR ((tlinfo.address2 IS NULL) AND (X_address2 IS NULL)))
709 AND ((tlinfo.address3 = x_address3) OR ((tlinfo.address3 IS NULL) AND (X_address3 IS NULL)))
710 AND ((tlinfo.address4 = x_address4) OR ((tlinfo.address4 IS NULL) AND (X_address4 IS NULL)))
711 AND ((tlinfo.city = x_city) OR ((tlinfo.city IS NULL) AND (X_city IS NULL)))
712 AND ((tlinfo.state = x_state) OR ((tlinfo.state IS NULL) AND (X_state IS NULL)))
713 AND ((tlinfo.province = x_province) OR ((tlinfo.province IS NULL) AND (X_province IS NULL)))
714 AND ((tlinfo.county = x_county) OR ((tlinfo.county IS NULL) AND (X_county IS NULL)))
715 AND (tlinfo.country = x_country)
716 AND ((tlinfo.sex = x_sex) OR ((tlinfo.sex IS NULL) AND (X_sex IS NULL)))
717 AND (tlinfo.birth_dt = x_birth_dt)
718 AND (tlinfo.ssn_no = x_ssn_no)
719 AND (tlinfo.marital_status = x_marital_status)
720 AND ((tlinfo.visa_type = x_visa_type) OR ((tlinfo.visa_type IS NULL) AND (X_visa_type IS NULL)))
721 AND ((tlinfo.visa_category = x_visa_category) OR ((tlinfo.visa_category IS NULL) AND (X_visa_category IS NULL)))
722 AND ((tlinfo.visa_number = x_visa_number) OR ((tlinfo.visa_number IS NULL) AND (X_visa_number IS NULL)))
723 AND ((tlinfo.visa_expiry_dt = x_visa_expiry_dt) OR ((tlinfo.visa_expiry_dt IS NULL) AND (X_visa_expiry_dt IS NULL)))
724 AND ((tlinfo.entry_date = x_entry_date) OR ((tlinfo.entry_date IS NULL) AND (X_entry_date IS NULL)))
725 AND (tlinfo.fund_id = x_fund_id)
729 AND (tlinfo.aw_cal_type = x_aw_cal_type)
726 AND ((tlinfo.threshold_perct = x_threshold_perct) OR ((tlinfo.threshold_perct IS NULL) AND (X_threshold_perct IS NULL)))
727 AND ((tlinfo.threshold_value = x_threshold_value) OR ((tlinfo.threshold_value IS NULL) AND (X_threshold_value IS NULL)))
728 AND (tlinfo.accepted_amnt = x_accepted_amnt)
730 AND (tlinfo.aw_sequence_number = x_aw_sequence_number)
731 AND ((tlinfo.award_id = x_award_id) OR ((tlinfo.award_id IS NULL) AND (x_award_id IS NULL)))
732 AND ((tlinfo.authorization_date = x_authorization_date) OR ((tlinfo.authorization_date IS NULL) AND (x_authorization_date IS NULL)))
733 AND ((tlinfo.notification_date = x_notification_date) OR ((tlinfo.notification_date IS NULL) AND (x_notification_date IS NULL)))
734 ) THEN
735 NULL;
736 ELSE
737 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
738 igs_ge_msg_stack.add;
739 app_exception.raise_exception;
740 END IF;
741
742 RETURN;
743
744 END lock_row;
745
746
747 PROCEDURE update_row (
748 x_rowid IN VARCHAR2,
749 x_sequence_no IN NUMBER,
750 x_auth_id IN NUMBER,
751 x_flag IN VARCHAR2,
752 x_person_id IN NUMBER,
753 x_first_name IN VARCHAR2,
754 x_last_name IN VARCHAR2,
755 x_address1 IN VARCHAR2,
756 x_address2 IN VARCHAR2,
757 x_address3 IN VARCHAR2,
758 x_address4 IN VARCHAR2,
759 x_city IN VARCHAR2,
760 x_state IN VARCHAR2,
761 x_province IN VARCHAR2,
762 x_county IN VARCHAR2,
763 x_country IN VARCHAR2,
764 x_sex IN VARCHAR2,
765 x_birth_dt IN DATE,
766 x_ssn_no IN VARCHAR2,
767 x_marital_status IN VARCHAR2,
768 x_visa_type IN VARCHAR2,
769 x_visa_category IN VARCHAR2,
770 x_visa_number IN VARCHAR2,
771 x_visa_expiry_dt IN DATE,
772 x_entry_date IN DATE,
773 x_fund_id IN NUMBER,
774 x_threshold_perct IN NUMBER,
775 x_threshold_value IN NUMBER,
776 x_accepted_amnt IN NUMBER,
777 x_aw_cal_type IN VARCHAR2,
778 x_aw_sequence_number IN NUMBER,
779 x_mode IN VARCHAR2 DEFAULT 'R',
780 x_award_id IN NUMBER,
781 x_authorization_date IN DATE,
782 x_notification_date IN DATE
783 ) AS
784 /*
785 || Created By : ssawhney
786 || Created On : 31-DEC-2001
787 || Purpose : Handles the UPDATE DML logic for the table.
788 || Known limitations, enhancements or remarks :
789 || Change History :
790 || Who When What
791 || (reverse chronological order - newest change first)
792 */
793 x_last_update_date DATE ;
794 x_last_updated_by NUMBER;
795 x_last_update_login NUMBER;
796 x_request_id NUMBER;
797 x_program_id NUMBER;
798 x_program_application_id NUMBER;
799 x_program_update_date DATE;
800
801 BEGIN
802
803 x_last_update_date := SYSDATE;
804 IF (X_MODE = 'I') THEN
805 x_last_updated_by := 1;
806 x_last_update_login := 0;
807 ELSIF (x_mode = 'R') THEN
808 x_last_updated_by := fnd_global.user_id;
809 IF x_last_updated_by IS NULL THEN
810 x_last_updated_by := -1;
811 END IF;
812 x_last_update_login := fnd_global.login_id;
813 IF (x_last_update_login IS NULL) THEN
814 x_last_update_login := -1;
815 END IF;
816 ELSE
817 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
818 igs_ge_msg_stack.add;
819 app_exception.raise_exception;
820 END IF;
821
822 before_dml(
823 p_action => 'UPDATE',
824 x_rowid => x_rowid,
825 x_sequence_no => x_sequence_no,
826 x_auth_id => x_auth_id,
827 x_flag => x_flag,
828 x_person_id => x_person_id,
829 x_first_name => x_first_name,
830 x_last_name => x_last_name,
831 x_address1 => x_address1,
832 x_address2 => x_address2,
833 x_address3 => x_address3,
834 x_address4 => x_address4,
835 x_city => x_city,
836 x_state => x_state,
837 x_province => x_province,
838 x_county => x_county,
842 x_ssn_no => x_ssn_no,
839 x_country => x_country,
840 x_sex => x_sex,
841 x_birth_dt => x_birth_dt,
843 x_marital_status => x_marital_status,
844 x_visa_type => x_visa_type,
845 x_visa_category => x_visa_category,
846 x_visa_number => x_visa_number,
847 x_visa_expiry_dt => x_visa_expiry_dt,
848 x_entry_date => x_entry_date,
849 x_fund_id => x_fund_id,
850 x_threshold_perct => x_threshold_perct,
851 x_threshold_value => x_threshold_value,
852 x_accepted_amnt => x_accepted_amnt,
853 x_aw_cal_type => x_aw_cal_type,
854 x_aw_sequence_number => x_aw_sequence_number,
855 x_creation_date => x_last_update_date,
856 x_created_by => x_last_updated_by,
857 x_last_update_date => x_last_update_date,
858 x_last_updated_by => x_last_updated_by,
859 x_last_update_login => x_last_update_login,
860 x_award_id => x_award_id,
861 x_authorization_date => x_authorization_date,
862 x_notification_date => x_notification_date
863 );
864
865 IF (x_mode = 'R') THEN
866 x_request_id := fnd_global.conc_request_id;
867 x_program_id := fnd_global.conc_program_id;
868 x_program_application_id := fnd_global.prog_appl_id;
869 IF (x_request_id = -1) THEN
870 x_request_id := old_references.request_id;
871 x_program_id := old_references.program_id;
872 x_program_application_id := old_references.program_application_id;
873 x_program_update_date := old_references.program_update_date;
874 ELSE
875 x_program_update_date := SYSDATE;
876 END IF;
877 END IF;
878
879 UPDATE igf_se_auth
880 SET
881 auth_id = new_references.auth_id,
882 flag = new_references.flag,
883 person_id = new_references.person_id,
884 first_name = new_references.first_name,
885 last_name = new_references.last_name,
886 address1 = new_references.address1,
887 address2 = new_references.address2,
888 address3 = new_references.address3,
889 address4 = new_references.address4,
890 city = new_references.city,
891 state = new_references.state,
892 province = new_references.province,
893 county = new_references.county,
894 country = new_references.country,
895 sex = new_references.sex,
896 birth_dt = new_references.birth_dt,
897 ssn_no = new_references.ssn_no,
898 marital_status = new_references.marital_status,
899 visa_type = new_references.visa_type,
900 visa_category = new_references.visa_category,
901 visa_number = new_references.visa_number,
902 visa_expiry_dt = new_references.visa_expiry_dt,
903 entry_date = new_references.entry_date,
904 fund_id = new_references.fund_id,
905 threshold_perct = new_references.threshold_perct,
906 threshold_value = new_references.threshold_value,
907 accepted_amnt = new_references.accepted_amnt,
908 aw_cal_type = new_references.aw_cal_type,
909 aw_sequence_number = new_references.aw_sequence_number,
910 last_update_date = x_last_update_date,
911 last_updated_by = x_last_updated_by,
912 last_update_login = x_last_update_login ,
913 request_id = x_request_id,
914 program_id = x_program_id,
915 program_application_id = x_program_application_id,
916 program_update_date = x_program_update_date,
917 award_id = x_award_id,
918 authorization_date = x_authorization_date,
919 notification_date = x_notification_date
920 WHERE rowid = x_rowid;
921 IF (SQL%NOTFOUND) THEN
922 RAISE NO_DATA_FOUND;
923 END IF;
924
925 END update_row;
926
927
928 PROCEDURE add_row (
929 x_rowid IN OUT NOCOPY VARCHAR2,
930 x_sequence_no IN OUT NOCOPY NUMBER,
931 x_auth_id IN NUMBER,
932 x_flag IN VARCHAR2,
933 x_person_id IN NUMBER,
934 x_first_name IN VARCHAR2,
935 x_last_name IN VARCHAR2,
936 x_address1 IN VARCHAR2,
937 x_address2 IN VARCHAR2,
938 x_address3 IN VARCHAR2,
939 x_address4 IN VARCHAR2,
940 x_city IN VARCHAR2,
941 x_state IN VARCHAR2,
942 x_province IN VARCHAR2,
943 x_county IN VARCHAR2,
944 x_country IN VARCHAR2,
945 x_sex IN VARCHAR2,
946 x_birth_dt IN DATE,
947 x_ssn_no IN VARCHAR2,
948 x_marital_status IN VARCHAR2,
949 x_visa_type IN VARCHAR2,
950 x_visa_category IN VARCHAR2,
951 x_visa_number IN VARCHAR2,
952 x_visa_expiry_dt IN DATE,
953 x_entry_date IN DATE,
954 x_fund_id IN NUMBER,
955 x_threshold_perct IN NUMBER,
956 x_threshold_value IN NUMBER,
957 x_accepted_amnt IN NUMBER,
958 x_aw_cal_type IN VARCHAR2,
959 x_aw_sequence_number IN NUMBER,
960 x_mode IN VARCHAR2 DEFAULT 'R',
961 x_award_id IN NUMBER,
962 x_authorization_date IN DATE,
963 x_notification_date IN DATE
964 ) AS
965 /*
966 || Created By : ssawhney
967 || Created On : 31-DEC-2001
968 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
969 || Known limitations, enhancements or remarks :
970 || Change History :
971 || Who When What
972 || (reverse chronological order - newest change first)
973 */
974 CURSOR c1 IS
975 SELECT rowid
976 FROM igf_se_auth
977 WHERE sequence_no = x_sequence_no;
978
979 BEGIN
980
981 OPEN c1;
982 FETCH c1 INTO x_rowid;
983 IF (c1%NOTFOUND) THEN
984 CLOSE c1;
985
986 insert_row (
987 x_rowid,
988 x_sequence_no,
989 x_auth_id,
990 x_flag,
991 x_person_id,
992 x_first_name,
993 x_last_name,
994 x_address1,
995 x_address2,
996 x_address3,
997 x_address4,
998 x_city,
999 x_state,
1000 x_province,
1001 x_county,
1002 x_country,
1003 x_sex,
1004 x_birth_dt,
1005 x_ssn_no,
1006 x_marital_status,
1007 x_visa_type,
1008 x_visa_category,
1009 x_visa_number,
1010 x_visa_expiry_dt,
1011 x_entry_date,
1012 x_fund_id,
1013 x_threshold_perct,
1014 x_threshold_value,
1015 x_accepted_amnt,
1016 x_aw_cal_type,
1017 x_aw_sequence_number,
1018 x_mode,
1019 x_award_id,
1020 x_authorization_date,
1021 x_notification_date
1022 );
1023 RETURN;
1024 END IF;
1025 CLOSE c1;
1026
1027 update_row (
1028 x_rowid,
1029 x_sequence_no,
1030 x_auth_id,
1031 x_flag,
1032 x_person_id,
1033 x_first_name,
1034 x_last_name,
1035 x_address1,
1036 x_address2,
1037 x_address3,
1038 x_address4,
1039 x_city,
1040 x_state,
1041 x_province,
1042 x_county,
1043 x_country,
1044 x_sex,
1045 x_birth_dt,
1046 x_ssn_no,
1047 x_marital_status,
1048 x_visa_type,
1049 x_visa_category,
1050 x_visa_number,
1051 x_visa_expiry_dt,
1052 x_entry_date,
1053 x_fund_id,
1054 x_threshold_perct,
1055 x_threshold_value,
1056 x_accepted_amnt,
1057 x_aw_cal_type,
1058 x_aw_sequence_number,
1059 x_mode,
1060 x_award_id,
1061 x_authorization_date,
1062 x_notification_date
1063 );
1064
1065 END add_row;
1066
1067
1068 PROCEDURE delete_row (
1069 x_rowid IN VARCHAR2
1070 ) AS
1071 /*
1072 || Created By : ssawhney
1073 || Created On : 31-DEC-2001
1074 || Purpose : Handles the DELETE DML logic for the table.
1075 || Known limitations, enhancements or remarks :
1076 || Change History :
1077 || Who When What
1078 || (reverse chronological order - newest change first)
1079 */
1080 BEGIN
1081
1082 before_dml (
1083 p_action => 'DELETE',
1084 x_rowid => x_rowid
1085 );
1086
1087 DELETE FROM igf_se_auth
1088 WHERE rowid = x_rowid;
1089
1090 IF (SQL%NOTFOUND) THEN
1091 RAISE NO_DATA_FOUND;
1092 END IF;
1093
1094 END delete_row;
1095
1096
1097 END igf_se_auth_pkg;