1 PACKAGE BODY igf_ap_batch_aw_map_pkg AS
2 /* $Header: IGFAI22B.pls 120.1 2005/07/12 08:23:37 appldev ship $ */
3
4 /*=======================================================================+
5 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA|
6 | All rights reserved. |
7 +=======================================================================+
8 | |
9 | DESCRIPTION |
10 | PL/SQL body for package: IGF_AP_BATCH_AW_MAP_PKG
11 | |
12 | NOTES |
13 | |
14 | This package has a flag on the end of some of the procedures called |
15 | X_MODE. Pass either 'R' for runtime, or 'I' for Install-time. |
16 | This will control how the who columns are filled in; If you are |
17 | running in runtime mode, they are taken from the profiles, whereas in |
18 | install-time mode they get defaulted with special values to indicate |
19 | that they were inserted by datamerge. |
20 | |
21 | The ADD_ROW routine will see whether a row exists by selecting |
22 | based on the primary key, and updates the row if it exists, |
23 | or inserts the row if it doesn't already exist. |
24 | |
25 | This module is called by AutoInstall (afplss.drv) on install and |
26 | upgrade. The WHENEVER SQLERROR and EXIT (at bottom) are required. |
27 | |
28 | HISTORY |
29 | Who When What |
30 | bannamal 29-Sep-2004 3416863 cod xml changes for pell and |
31 | direct loan. added two new columns |
32 | cdcruz 06-Jun-2003 # 2858504 FA 118.1 Legacy Import |
33 | Added new column : award_year_status_code |
34 | masehgal 17-Oct-2002 # 2613546 FA 105_108 Multiple Award Years |
35 | Added unique check on system award year |
36 | Added new column : |
37 | sys_award_year |
38 | masehgal 14-Jun-2002 # 2413695 Changed message to |
39 | 'IGF','IGF_AP_BAM_CI_FK' |
40 | |
41 | brajendr 04-Jul-2002 Bug # 2436484 - FACR009 Calendar Relations |
42 | Following columns are obsoleted. Signature |
43 | of PKG is retained and all the references |
44 | are removed |
45 | ci_sequence_number_acad |
46 | ci_cal_type_acad |
47 | ci_cal_type_adm |
48 | ci_sequence_number_adm |
49 | |
50 *=======================================================================*/
51
52 l_rowid VARCHAR2(25);
53 old_references igf_ap_batch_aw_map_all%ROWTYPE;
54 new_references igf_ap_batch_aw_map_all%ROWTYPE;
55
56 PROCEDURE set_column_values (
57 p_action IN VARCHAR2 ,
58 x_rowid IN VARCHAR2 ,
59 x_batch_year IN NUMBER ,
60 x_ci_sequence_number IN NUMBER ,
61 x_ci_cal_type IN VARCHAR2 ,
62 x_ci_sequence_number_acad IN NUMBER ,
63 x_ci_cal_type_acad IN VARCHAR2 ,
64 x_ci_cal_type_adm IN VARCHAR2 ,
65 x_ci_sequence_number_adm IN NUMBER ,
66 x_bam_id IN NUMBER ,
67 x_css_academic_year IN NUMBER ,
68 x_efc_frml IN VARCHAR2 ,
69 x_num_days_divisor IN NUMBER ,
70 x_roundoff_fact IN VARCHAR2 ,
71 x_efc_dob IN DATE ,
72 x_dl_code IN VARCHAR2 ,
73 x_ffel_code IN VARCHAR2 ,
74 x_pell_code IN VARCHAR2 ,
75 x_isir_code IN VARCHAR2 ,
76 x_profile_code IN VARCHAR2 ,
77 x_tolerance_limit IN NUMBER ,
78 x_sys_award_year IN VARCHAR2 ,
79 x_award_year_status_code IN VARCHAR2 DEFAULT NULL,
80 x_pell_participant_code IN VARCHAR2 ,
81 x_dl_participant_code IN VARCHAR2 ,
82 x_creation_date IN DATE ,
83 x_created_by IN NUMBER ,
84 x_last_update_date IN DATE ,
85 x_last_updated_by IN NUMBER ,
86 x_last_update_login IN NUMBER,
87 x_publish_in_ss_flag IN VARCHAR2
88 ) AS
89 /*
90 || Created By : prchandr
91 || Created On : 28-MAR-2001
92 || Purpose : Initialises the Old and New references for the columns of the table.
93 || Known limitations, enhancements or remarks :
94 || Change History :
95 || Who When What
96 || (reverse chronological order - newest change first)
97 */
98
99 CURSOR cur_old_ref_values IS
100 SELECT *
101 FROM igf_ap_batch_aw_map_all
102 WHERE rowid = x_rowid;
103
104 BEGIN
105
106 l_rowid := x_rowid;
107
108 -- Code for setting the Old and New Reference Values.
109 -- Populate Old Values.
110 OPEN cur_old_ref_values;
111 FETCH cur_old_ref_values INTO old_references;
112 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
113 CLOSE cur_old_ref_values;
114 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
115 IGS_GE_MSG_STACK.ADD;
116 APP_EXCEPTION.RAISE_EXCEPTION;
117 RETURN;
118 END IF;
119 CLOSE cur_old_ref_values;
120
121 -- Populate New Values.
122 new_references.batch_year := x_batch_year;
123 new_references.ci_sequence_number := x_ci_sequence_number;
124 new_references.ci_cal_type := x_ci_cal_type;
125 new_references.ci_sequence_number_acad := NULL;
126 new_references.ci_cal_type_acad := NULL;
127 new_references.ci_cal_type_adm := NULL;
128 new_references.ci_sequence_number_adm := NULL;
129 new_references.bam_id := x_bam_id;
130 new_references.css_academic_year := x_css_academic_year;
131 new_references.efc_frml := x_efc_frml ;
132 new_references.num_days_divisor := x_num_days_divisor ;
133 new_references.roundoff_fact := x_roundoff_fact ;
134 new_references.efc_dob := x_efc_dob ;
135 new_references.dl_code := x_dl_code;
136 new_references.ffel_code := x_ffel_code;
137 new_references.pell_code := x_pell_code;
138 new_references.isir_code := x_isir_code;
139 new_references.profile_code := x_profile_code;
140 new_references.tolerance_limit := x_tolerance_limit ;
141 new_references.sys_award_year := x_sys_award_year ;
142 new_references.award_year_status_code := x_award_year_status_code ;
143 new_references.pell_participant_code := x_pell_participant_code;
144 new_references.dl_participant_code := x_dl_participant_code;
145 new_references.publish_in_ss_flag := x_publish_in_ss_flag;
146
147 IF (p_action = 'UPDATE') THEN
148 new_references.creation_date := old_references.creation_date;
149 new_references.created_by := old_references.created_by;
150 ELSE
151 new_references.creation_date := x_creation_date;
152 new_references.created_by := x_created_by;
153 END IF;
154
155 new_references.last_update_date := x_last_update_date;
156 new_references.last_updated_by := x_last_updated_by;
157 new_references.last_update_login := x_last_update_login;
158
159 END set_column_values;
160
161
162 PROCEDURE check_uniqueness AS
163 /*
164 || Created By : prchandr
165 || Created On : 28-MAR-2001
166 || Purpose : Handles the Unique Constraint logic defined for the columns.
167 || Known limitations, enhancements or remarks :
168 || Change History :
169 || Who When What
170 || masehgal 18-oct-2002 # 2613546 Multiple Award Years Enhancements
171 || Added uniqueness check for system award year
172 || (reverse chronological order - newest change first)
173 */
174 BEGIN
175
176 IF ( get_uk2_for_validation ( new_references.ci_cal_type,
177 new_references.ci_sequence_number )) THEN
178 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
179 IGS_GE_MSG_STACK.ADD;
180 APP_EXCEPTION.RAISE_EXCEPTION;
181 END IF;
182
183 IF ( get_uk6_for_validation ( new_references.sys_award_year )) THEN
184 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
185 IGS_GE_MSG_STACK.ADD;
186 APP_EXCEPTION.RAISE_EXCEPTION;
187 END IF;
188
189 END check_uniqueness;
190
191
192 PROCEDURE check_parent_existance AS
193 /*
194 || Created By : prchandr
195 || Created On : 28-MAR-2001
196 || Purpose : Checks for the existance of Parent records.
197 || Known limitations, enhancements or remarks :
198 || Change History :
199 || Who When What
200 || (reverse chronological order - newest change first)
201 */
202 BEGIN
203
204 IF ( ( (old_references.ci_cal_type = new_references.ci_cal_type)
205 AND (old_references.ci_sequence_number = new_references.ci_sequence_number))
206 OR ( (new_references.ci_cal_type IS NULL)
207 OR (new_references.ci_sequence_number IS NULL))) THEN
208 NULL;
209 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation ( new_references.ci_cal_type,
210 new_references.ci_sequence_number ) THEN
211 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
212 IGS_GE_MSG_STACK.ADD;
213 APP_EXCEPTION.RAISE_EXCEPTION;
214 END IF;
215
216 END check_parent_existance;
217
218
219 FUNCTION get_pk_for_validation ( x_bam_id IN NUMBER )
220 RETURN BOOLEAN AS
221 /*
222 || Created By : prchandr
223 || Created On : 28-MAR-2001
224 || Purpose : Validates the Primary Key of the table.
225 || Known limitations, enhancements or remarks :
226 || Change History :
227 || Who When What
228 || (reverse chronological order - newest change first)
229 */
230 CURSOR cur_rowid IS
231 SELECT rowid
232 FROM igf_ap_batch_aw_map_all
233 WHERE bam_id = x_bam_id
234 FOR UPDATE NOWAIT;
235
236 lv_rowid cur_rowid%ROWTYPE;
237
238 BEGIN
239
240 OPEN cur_rowid;
241 FETCH cur_rowid INTO lv_rowid;
242 IF (cur_rowid%FOUND) THEN
243 CLOSE cur_rowid;
244 RETURN(TRUE);
245 ELSE
246 CLOSE cur_rowid;
247 RETURN(FALSE);
248 END IF;
249
250 END get_pk_for_validation;
251
252
253 FUNCTION get_uk2_for_validation ( x_ci_cal_type IN VARCHAR2,
254 x_ci_sequence_number IN NUMBER )
255 RETURN BOOLEAN AS
256 /*
257 || Created By : prchandr
258 || Created On : 28-MAR-2001
259 || Purpose : Validates the Unique Keys of the table.
260 || Known limitations, enhancements or remarks :
261 || Change History :
262 || Who When What
263 || (reverse chronological order - newest change first)
264 */
265 CURSOR cur_rowid IS
266 SELECT rowid
267 FROM igf_ap_batch_aw_map_all
268 WHERE ci_cal_type = x_ci_cal_type
269 AND ci_sequence_number = x_ci_sequence_number
270 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
271
272 lv_rowid cur_rowid%ROWTYPE;
273
274 BEGIN
275
276 OPEN cur_rowid;
277 FETCH cur_rowid INTO lv_rowid;
278 IF (cur_rowid%FOUND) THEN
279 CLOSE cur_rowid;
280 RETURN (TRUE);
281 ELSE
282 CLOSE cur_rowid;
283 RETURN(FALSE);
284 END IF;
285
286 END get_uk2_for_validation ;
287
288
289 FUNCTION get_uk6_for_validation ( x_sys_award_year IN VARCHAR2 )
290 RETURN BOOLEAN AS
291 /*
292 || Created By : masehgal
293 || Created On : 18-Oct-2002
294 || Purpose : Validates the Unique Key ( System Award Year ) of the table.
295 || Known limitations, enhancements or remarks :
296 || Change History :
297 || Who When What
298 || (reverse chronological order - newest change first)
299 */
300 CURSOR cur_rowid IS
301 SELECT rowid
302 FROM igf_ap_batch_aw_map_all
303 WHERE sys_award_year = x_sys_award_year
304 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
305
306 lv_rowid cur_rowid%ROWTYPE;
307
308 BEGIN
309
310 OPEN cur_rowid;
311 FETCH cur_rowid INTO lv_rowid;
312 IF (cur_rowid%FOUND) THEN
313 CLOSE cur_rowid;
314 RETURN (TRUE);
315 ELSE
316 CLOSE cur_rowid;
317 RETURN(FALSE);
318 END IF;
319
320 END get_uk6_for_validation ;
321
322
323 PROCEDURE get_fk_igs_ca_inst ( x_cal_type IN VARCHAR2,
324 x_sequence_number IN NUMBER ) AS
325 /*
326 || Created By : prchandr
327 || Created On : 28-MAR-2001
328 || Purpose : Validates the Foreign Keys for the table.
329 || Known limitations, enhancements or remarks :
330 || Change History :
331 || Who When What
332 || masehgal 14-Jun-2002 # 2413695 Changed message to
333 || 'IGF','IGF_AP_BAM_CI_FK'
334 || (reverse chronological order - newest change first)
335 */
336 CURSOR cur_rowid IS
337 SELECT rowid
338 FROM igf_ap_batch_aw_map_all
339 WHERE ((ci_cal_type = x_cal_type) AND
340 (ci_sequence_number = x_sequence_number))
341 OR ((ci_cal_type_acad = x_cal_type) AND
342 (ci_sequence_number_acad = x_sequence_number)) ;
343
344 lv_rowid cur_rowid%ROWTYPE;
345
346 BEGIN
347
348 OPEN cur_rowid;
349 FETCH cur_rowid INTO lv_rowid;
350 IF (cur_rowid%FOUND) THEN
351 CLOSE cur_rowid;
352 FND_MESSAGE.SET_NAME ('IGF','IGF_AP_BAM_CI_FK');
353 IGS_GE_MSG_STACK.ADD;
354 APP_EXCEPTION.RAISE_EXCEPTION;
355 RETURN;
356 END IF;
360
357 CLOSE cur_rowid;
358
359 END get_fk_igs_ca_inst;
361
362 PROCEDURE before_dml (
363 p_action IN VARCHAR2 ,
364 x_rowid IN VARCHAR2 ,
365 x_batch_year IN NUMBER ,
366 x_ci_sequence_number IN NUMBER ,
367 x_ci_cal_type IN VARCHAR2 ,
368 x_ci_sequence_number_acad IN NUMBER ,
369 x_ci_cal_type_acad IN VARCHAR2 ,
370 x_ci_cal_type_adm IN VARCHAR2 ,
371 x_ci_sequence_number_adm IN NUMBER ,
372 x_bam_id IN NUMBER ,
373 x_css_academic_year IN NUMBER ,
374 x_efc_frml IN VARCHAR2 ,
375 x_num_days_divisor IN NUMBER ,
376 x_roundoff_fact IN VARCHAR2 ,
377 x_efc_dob IN DATE ,
378 x_dl_code IN VARCHAR2 ,
379 x_ffel_code IN VARCHAR2 ,
380 x_pell_code IN VARCHAR2 ,
381 x_isir_code IN VARCHAR2 ,
382 x_profile_code IN VARCHAR2 ,
383 x_tolerance_limit IN NUMBER ,
384 x_sys_award_year IN VARCHAR2 ,
385 x_award_year_status_code IN VARCHAR2 ,
386 x_pell_participant_code IN VARCHAR2 ,
387 x_dl_participant_code IN VARCHAR2 ,
388 x_creation_date IN DATE ,
389 x_created_by IN NUMBER ,
390 x_last_update_date IN DATE ,
391 x_last_updated_by IN NUMBER ,
392 x_last_update_login IN NUMBER,
393 x_publish_in_ss_flag IN VARCHAR2
394 ) AS
395 /*
396 || Created By : prchandr
397 || Created On : 28-MAR-2001
398 || Purpose : Initialises the columns, Checks Constraints, Calls the
399 || Trigger Handlers for the table, before any DML operation.
400 || Known limitations, enhancements or remarks :
401 || Change History :
402 || Who When What
403 || (reverse chronological order - newest change first)
404 */
405 BEGIN
406
407 set_column_values (
408 p_action,
409 x_rowid,
410 x_batch_year,
411 x_ci_sequence_number,
412 x_ci_cal_type,
413 x_ci_sequence_number_acad,
414 x_ci_cal_type_acad,
415 x_ci_cal_type_adm,
416 x_ci_sequence_number_adm,
417 x_bam_id,
418 x_css_academic_year,
419 x_efc_frml ,
420 x_num_days_divisor ,
421 x_roundoff_fact ,
422 x_efc_dob,
423 x_dl_code,
424 x_ffel_code,
425 x_pell_code,
426 x_isir_code,
427 x_profile_code,
428 x_tolerance_limit,
429 x_sys_award_year,
430 x_award_year_status_code,
431 x_pell_participant_code,
432 x_dl_participant_code,
433 x_creation_date,
434 x_created_by,
435 x_last_update_date,
436 x_last_updated_by,
437 x_last_update_login,
438 x_publish_in_ss_flag
439 );
440
441 IF (p_action = 'INSERT') THEN
442 -- Call all the procedures related to Before Insert.
443 IF ( get_pk_for_validation( new_references.bam_id )) THEN
444 FND_MESSAGE.SET_NAME('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
445 IGS_GE_MSG_STACK.ADD;
446 APP_EXCEPTION.RAISE_EXCEPTION;
447 END IF;
448 check_uniqueness;
449 check_parent_existance;
450 ELSIF (p_action = 'UPDATE') THEN
451 -- Call all the procedures related to Before Update.
452 check_uniqueness;
453 check_parent_existance;
454 ELSIF (p_action = 'VALIDATE_INSERT') THEN
455 -- Call all the procedures related to Before Insert.
456 IF ( get_pk_for_validation ( new_references.bam_id ) ) THEN
457 FND_MESSAGE.SET_NAME('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
458 IGS_GE_MSG_STACK.ADD;
459 APP_EXCEPTION.RAISE_EXCEPTION;
460 END IF;
461 check_uniqueness;
462 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
463 check_uniqueness;
464
465 END IF;
466
467 END before_dml;
468
469
470 PROCEDURE insert_row (
471 x_rowid IN OUT NOCOPY VARCHAR2 ,
472 x_batch_year IN NUMBER ,
473 x_ci_sequence_number IN NUMBER ,
474 x_ci_cal_type IN VARCHAR2 ,
475 x_ci_sequence_number_acad IN NUMBER ,
476 x_ci_cal_type_acad IN VARCHAR2 ,
477 x_ci_cal_type_adm IN VARCHAR2 ,
478 x_ci_sequence_number_adm IN NUMBER ,
479 x_bam_id IN OUT NOCOPY NUMBER ,
480 x_css_academic_year IN NUMBER ,
481 x_efc_frml IN VARCHAR2 ,
482 x_num_days_divisor IN NUMBER ,
483 x_roundoff_fact IN VARCHAR2 ,
484 x_efc_dob IN DATE ,
488 x_isir_code IN VARCHAR2 ,
485 x_dl_code IN VARCHAR2 ,
486 x_ffel_code IN VARCHAR2 ,
487 x_pell_code IN VARCHAR2 ,
489 x_profile_code IN VARCHAR2 ,
490 x_tolerance_limit IN NUMBER ,
491 x_sys_award_year IN VARCHAR2 ,
492 x_award_year_status_code IN VARCHAR2 ,
493 x_pell_participant_code IN VARCHAR2 ,
494 x_dl_participant_code IN VARCHAR2 ,
495 x_mode IN VARCHAR2,
496 x_publish_in_ss_flag IN VARCHAR2
497 ) AS
498 /*
499 || Created By : prchandr
500 || Created On : 28-MAR-2001
501 || Purpose : Handles the INSERT DML logic for the table.
502 || Known limitations, enhancements or remarks :
503 || Change History :
504 || Who When What
505 || (reverse chronological order - newest change first)
506 */
507 CURSOR c IS
508 SELECT rowid
509 FROM igf_ap_batch_aw_map_all
510 WHERE bam_id = x_bam_id;
511
512 x_last_update_date DATE;
513 x_last_updated_by NUMBER;
514 x_last_update_login NUMBER;
515
516 l_org_id igf_ap_batch_aw_map_all.org_id%TYPE := igf_aw_gen.get_org_id;
517
518 BEGIN
519
520 x_last_update_date := SYSDATE;
521 IF (x_mode = 'I') THEN
522 x_last_updated_by := 1;
523 x_last_update_login := 0;
524 ELSIF (x_mode = 'R') THEN
525 x_last_updated_by := FND_GLOBAL.USER_ID;
526 IF (x_last_updated_by IS NULL) THEN
527 x_last_updated_by := -1;
528 END IF;
529
530 x_last_update_login := FND_GLOBAL.LOGIN_ID;
531
532 IF (x_last_update_login IS NULL) THEN
533 x_last_update_login := -1;
534 END IF;
535 ELSE
536 FND_MESSAGE.SET_NAME ('FND', 'SYSTEM-INVALID ARGS');
537 IGS_GE_MSG_STACK.ADD;
538 APP_EXCEPTION.RAISE_EXCEPTION;
539 END IF;
540
541 SELECT igf_ap_batch_aw_map_all_s.NEXTVAL INTO x_bam_id FROM dual;
542
543 before_dml(
544 p_action => 'INSERT',
545 x_rowid => x_rowid,
546 x_batch_year => x_batch_year,
547 x_ci_sequence_number => x_ci_sequence_number,
548 x_ci_cal_type => x_ci_cal_type,
549 x_ci_sequence_number_acad => x_ci_sequence_number_acad,
550 x_ci_cal_type_acad => x_ci_cal_type_acad,
551 x_ci_cal_type_adm => x_ci_cal_type_adm,
552 x_ci_sequence_number_adm => x_ci_sequence_number_adm,
553 x_bam_id => x_bam_id,
554 x_css_academic_year => x_css_academic_year,
555 x_efc_frml => x_efc_frml,
556 x_num_days_divisor => x_num_days_divisor,
557 x_roundoff_fact => x_roundoff_fact,
558 x_efc_dob => x_efc_dob,
559 x_dl_code => x_dl_code,
560 x_ffel_code => x_ffel_code,
561 x_pell_code => x_pell_code,
562 x_isir_code => x_isir_code,
563 x_profile_code => x_profile_code,
564 x_tolerance_limit => x_tolerance_limit ,
565 x_sys_award_year => x_sys_award_year ,
566 x_award_year_status_code => x_award_year_status_code,
567 x_pell_participant_code => x_pell_participant_code,
568 x_dl_participant_code => x_dl_participant_code,
569 x_creation_date => x_last_update_date,
570 x_created_by => x_last_updated_by,
571 x_last_update_date => x_last_update_date,
572 x_last_updated_by => x_last_updated_by,
573 x_last_update_login => x_last_update_login,
574 x_publish_in_ss_flag => x_publish_in_ss_flag
575 );
576
577 INSERT INTO igf_ap_batch_aw_map_all (
578 batch_year,
579 ci_sequence_number,
580 ci_cal_type,
581 ci_sequence_number_acad,
582 ci_cal_type_acad,
583 ci_cal_type_adm,
584 ci_sequence_number_adm,
585 bam_id,
586 css_academic_year,
587 efc_frml ,
588 num_days_divisor,
589 roundoff_fact,
590 efc_dob,
591 dl_code,
592 ffel_code,
593 pell_code,
594 isir_code,
595 profile_code,
596 tolerance_limit ,
597 sys_award_year ,
598 award_year_status_code ,
599 pell_participant_code ,
600 dl_participant_code ,
601 creation_date,
602 created_by,
603 last_update_date,
604 last_updated_by,
605 last_update_login,
606 org_id,
607 publish_in_ss_flag
608 ) VALUES (
609 new_references.batch_year,
610 new_references.ci_sequence_number,
611 new_references.ci_cal_type,
612 NULL,
616 new_references.bam_id,
613 NULL,
614 NULL,
615 NULL,
617 new_references.css_academic_year,
618 new_references.efc_frml ,
619 new_references.num_days_divisor,
620 new_references.roundoff_fact,
621 new_references.efc_dob,
622 new_references.dl_code,
623 new_references.ffel_code,
624 new_references.pell_code,
625 new_references.isir_code,
626 new_references.profile_code,
627 new_references.tolerance_limit ,
628 new_references.sys_award_year ,
629 new_references.award_year_status_code ,
630 new_references.pell_participant_code ,
631 new_references.dl_participant_code ,
632 x_last_update_date,
633 x_last_updated_by,
634 x_last_update_date,
635 x_last_updated_by,
636 x_last_update_login,
637 l_org_id,
638 new_references.publish_in_ss_flag
639 );
640
641 OPEN c;
642 FETCH c INTO x_rowid;
643 IF (c%NOTFOUND) THEN
644 CLOSE c;
645 RAISE NO_DATA_FOUND;
646 END IF;
647 CLOSE c;
648
649 END insert_row;
650
651
652 PROCEDURE lock_row (
653 x_rowid IN VARCHAR2 ,
654 x_batch_year IN NUMBER ,
655 x_ci_sequence_number IN NUMBER ,
656 x_ci_cal_type IN VARCHAR2 ,
657 x_ci_sequence_number_acad IN NUMBER ,
658 x_ci_cal_type_acad IN VARCHAR2 ,
659 x_ci_cal_type_adm IN VARCHAR2 ,
660 x_ci_sequence_number_adm IN NUMBER ,
661 x_bam_id IN NUMBER ,
662 x_css_academic_year IN NUMBER ,
663 x_efc_frml IN VARCHAR2 ,
664 x_num_days_divisor IN NUMBER ,
665 x_roundoff_fact IN VARCHAR2 ,
666 x_efc_dob IN DATE ,
667 x_dl_code IN VARCHAR2 ,
668 x_ffel_code IN VARCHAR2 ,
669 x_pell_code IN VARCHAR2 ,
670 x_isir_code IN VARCHAR2 ,
671 x_profile_code IN VARCHAR2 ,
672 x_tolerance_limit IN NUMBER ,
673 x_sys_award_year IN VARCHAR2 ,
674 x_award_year_status_code IN VARCHAR2 ,
675 x_pell_participant_code IN VARCHAR2 ,
676 x_dl_participant_code IN VARCHAR2,
677 x_publish_in_ss_flag IN VARCHAR2
678
679 ) AS
680 /*
681 || Created By : prchandr
682 || Created On : 28-MAR-2001
683 || Purpose : Handles the LOCK mechanism for the table.
684 || Known limitations, enhancements or remarks :
685 || Change History :
686 || Who When What
687 || (reverse chronological order - newest change first)
688 */
689 CURSOR c1 IS
690 SELECT batch_year,
691 ci_sequence_number,
692 ci_cal_type,
693 css_academic_year,
694 efc_frml ,
695 num_days_divisor ,
696 roundoff_fact ,
697 efc_dob,
698 dl_code,
699 ffel_code,
700 pell_code,
701 isir_code,
702 profile_code,
703 tolerance_limit,
704 sys_award_year,
705 award_year_status_code,
706 pell_participant_code,
707 dl_participant_code,
708 publish_in_ss_flag
709 FROM igf_ap_batch_aw_map_all
710 WHERE rowid = x_rowid
711 FOR UPDATE NOWAIT;
712
713 tlinfo c1%ROWTYPE;
714
715 BEGIN
716
717 OPEN c1;
718 FETCH c1 INTO tlinfo;
719 IF (c1%notfound) THEN
720 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
721 IGS_GE_MSG_STACK.ADD;
722 CLOSE c1;
723 APP_EXCEPTION.RAISE_EXCEPTION;
724 RETURN;
725 END IF;
726 CLOSE c1;
727
728 IF (
729 (tlinfo.batch_year = x_batch_year)
730 AND (tlinfo.ci_sequence_number = x_ci_sequence_number)
731 AND (tlinfo.ci_cal_type = x_ci_cal_type)
732 AND (tlinfo.css_academic_year = x_css_academic_year) OR ((tlinfo.css_academic_year IS NULL) AND (x_css_academic_year IS NULL))
733 AND (tlinfo.efc_frml = x_efc_frml) OR ((tlinfo.efc_frml IS NULL) AND (x_efc_frml IS NULL))
734 AND (tlinfo.num_days_divisor = x_efc_frml) OR ((tlinfo.num_days_divisor IS NULL) AND (x_num_days_divisor IS NULL))
735 AND (tlinfo.roundoff_fact = x_roundoff_fact) OR ((tlinfo.roundoff_fact IS NULL) AND (x_roundoff_fact IS NULL))
736 AND (tlinfo.efc_dob = x_efc_dob) OR ((tlinfo.efc_dob IS NULL) AND (x_efc_dob IS NULL))
737 AND (tlinfo.dl_code = x_dl_code) OR ((tlinfo.dl_code IS NULL) AND (x_dl_code IS NULL))
738 AND (tlinfo.ffel_code = x_ffel_code) OR ((tlinfo.ffel_code IS NULL) AND (x_ffel_code IS NULL))
739 AND (tlinfo.pell_code = x_pell_code) OR ((tlinfo.pell_code IS NULL) AND (x_pell_code IS NULL))
743 AND (tlinfo.sys_award_year = x_sys_award_year) OR ((tlinfo.sys_award_year IS NULL) AND (x_sys_award_year IS NULL))
740 AND (tlinfo.isir_code = x_isir_code) OR ((tlinfo.isir_code IS NULL) AND (x_isir_code IS NULL))
741 AND (tlinfo.profile_code = x_profile_code) OR ((tlinfo.profile_code IS NULL) AND (x_profile_code IS NULL))
742 AND (tlinfo.tolerance_limit = x_tolerance_limit) OR ((tlinfo.tolerance_limit IS NULL) AND (x_tolerance_limit IS NULL))
744 AND (tlinfo.award_year_status_code = x_award_year_status_code) OR ((tlinfo.award_year_status_code IS NULL) AND (x_award_year_status_code IS NULL))
745 AND (tlinfo.pell_participant_code = x_pell_participant_code) OR ((tlinfo.pell_participant_code IS NULL) AND (x_pell_participant_code IS NULL))
746 AND (tlinfo.dl_participant_code = x_dl_participant_code) OR ((tlinfo.dl_participant_code IS NULL) AND (x_dl_participant_code IS NULL))
747 AND ((tlinfo.publish_in_ss_flag = x_publish_in_ss_flag) OR ((tlinfo.publish_in_ss_flag IS NULL) AND (x_publish_in_ss_flag IS NULL)))
748 ) THEN
749 NULL;
750 ELSE
751 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
752 IGS_GE_MSG_STACK.ADD;
753 APP_EXCEPTION.RAISE_EXCEPTION;
754 END IF;
755
756 RETURN;
757
758 END lock_row;
759
760
761 PROCEDURE update_row (
762 x_rowid IN VARCHAR2 ,
763 x_batch_year IN NUMBER ,
764 x_ci_sequence_number IN NUMBER ,
765 x_ci_cal_type IN VARCHAR2 ,
766 x_ci_sequence_number_acad IN NUMBER ,
767 x_ci_cal_type_acad IN VARCHAR2 ,
768 x_ci_cal_type_adm IN VARCHAR2 ,
769 x_ci_sequence_number_adm IN NUMBER ,
770 x_bam_id IN NUMBER ,
771 x_css_academic_year IN NUMBER ,
772 x_efc_frml IN VARCHAR2 ,
773 x_num_days_divisor IN NUMBER ,
774 x_roundoff_fact IN VARCHAR2 ,
775 x_efc_dob IN DATE ,
776 x_dl_code IN VARCHAR2 ,
777 x_ffel_code IN VARCHAR2 ,
778 x_pell_code IN VARCHAR2 ,
779 x_isir_code IN VARCHAR2 ,
780 x_profile_code IN VARCHAR2 ,
781 x_tolerance_limit IN NUMBER ,
782 x_sys_award_year IN VARCHAR2 ,
783 x_award_year_status_code IN VARCHAR2 ,
784 x_pell_participant_code IN VARCHAR2 ,
785 x_dl_participant_code IN VARCHAR2 ,
786 x_mode IN VARCHAR2,
787 x_publish_in_ss_flag IN VARCHAR2
788 ) AS
789 /*
790 || Created By : prchandr
791 || Created On : 28-MAR-2001
792 || Purpose : Handles the UPDATE DML logic for the table.
793 || Known limitations, enhancements or remarks :
794 || Change History :
795 || Who When What
796 || (reverse chronological order - newest change first)
797 */
798 x_last_update_date DATE ;
799 x_last_updated_by NUMBER;
800 x_last_update_login NUMBER;
801
802 BEGIN
803
804 x_last_update_date := SYSDATE;
805 IF (X_MODE = 'I') THEN
806 x_last_updated_by := 1;
807 x_last_update_login := 0;
808 ELSIF (x_mode = 'R') THEN
809 x_last_updated_by := FND_GLOBAL.USER_ID;
810 IF x_last_updated_by IS NULL THEN
811 x_last_updated_by := -1;
812 END IF;
813
814 x_last_update_login := FND_GLOBAL.LOGIN_ID;
815
816 IF (x_last_update_login IS NULL) THEN
817 x_last_update_login := -1;
818 END IF;
819 ELSE
820 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
821 IGS_GE_MSG_STACK.ADD;
822 APP_EXCEPTION.RAISE_EXCEPTION;
823 END IF;
824
825 before_dml(
826 p_action => 'UPDATE',
827 x_rowid => x_rowid,
828 x_batch_year => x_batch_year,
829 x_ci_sequence_number => x_ci_sequence_number,
830 x_ci_cal_type => x_ci_cal_type,
831 x_ci_sequence_number_acad => x_ci_sequence_number_acad,
832 x_ci_cal_type_acad => x_ci_cal_type_acad,
833 x_ci_cal_type_adm => x_ci_cal_type_adm,
834 x_ci_sequence_number_adm => x_ci_sequence_number_adm,
835 x_bam_id => x_bam_id,
836 x_css_academic_year => x_css_academic_year,
837 x_efc_frml => x_efc_frml,
838 x_num_days_divisor => x_num_days_divisor,
839 x_roundoff_fact => x_roundoff_fact,
840 x_efc_dob => x_efc_dob,
841 x_dl_code => x_dl_code,
842 x_ffel_code => x_ffel_code,
843 x_pell_code => x_pell_code,
844 x_isir_code => x_isir_code,
845 x_profile_code => x_profile_code,
846 x_tolerance_limit => x_tolerance_limit ,
847 x_sys_award_year => x_sys_award_year ,
848 x_award_year_status_code => x_award_year_status_code ,
849 x_pell_participant_code => x_pell_participant_code ,
850 x_dl_participant_code => x_dl_participant_code ,
851 x_creation_date => x_last_update_date,
852 x_created_by => x_last_updated_by,
853 x_last_update_date => x_last_update_date,
854 x_last_updated_by => x_last_updated_by,
855 x_last_update_login => x_last_update_login,
856 x_publish_in_ss_flag => x_publish_in_ss_flag
857 );
858
859 UPDATE igf_ap_batch_aw_map_all
860 SET
861 batch_year = new_references.batch_year,
862 ci_sequence_number = new_references.ci_sequence_number,
863 ci_cal_type = new_references.ci_cal_type,
864 css_academic_year = new_references.css_academic_year,
865 efc_frml = new_references.efc_frml,
866 num_days_divisor = new_references.num_days_divisor,
867 roundoff_fact = new_references.roundoff_fact,
868 efc_dob = new_references.efc_dob,
869 dl_code = new_references.dl_code,
870 ffel_code = new_references.ffel_code,
871 pell_code = new_references.pell_code,
872 isir_code = new_references.isir_code,
873 profile_code = new_references.profile_code,
874 tolerance_limit = new_references.tolerance_limit ,
875 sys_award_year = new_references.sys_award_year ,
876 award_year_status_code = new_references.award_year_status_code ,
877 pell_participant_code = new_references.pell_participant_code,
878 dl_participant_code = new_references.dl_participant_code,
879 last_update_date = x_last_update_date,
880 last_updated_by = x_last_updated_by,
881 last_update_login = x_last_update_login,
882 publish_in_ss_flag = new_references.publish_in_ss_flag
883 WHERE rowid = x_rowid;
884
885 IF (SQL%NOTFOUND) THEN
886 RAISE NO_DATA_FOUND;
887 END IF;
888
889 END update_row;
890
891
892 PROCEDURE add_row (
893 x_rowid IN OUT NOCOPY VARCHAR2 ,
894 x_batch_year IN NUMBER ,
895 x_ci_sequence_number IN NUMBER ,
896 x_ci_cal_type IN VARCHAR2 ,
897 x_ci_sequence_number_acad IN NUMBER ,
898 x_ci_cal_type_acad IN VARCHAR2 ,
899 x_ci_cal_type_adm IN VARCHAR2 ,
900 x_ci_sequence_number_adm IN NUMBER ,
901 x_bam_id IN OUT NOCOPY NUMBER ,
902 x_css_academic_year IN NUMBER ,
903 x_efc_frml IN VARCHAR2 ,
904 x_num_days_divisor IN NUMBER ,
905 x_roundoff_fact IN VARCHAR2 ,
906 x_efc_dob IN DATE ,
907 x_dl_code IN VARCHAR2 ,
908 x_ffel_code IN VARCHAR2 ,
909 x_pell_code IN VARCHAR2 ,
910 x_isir_code IN VARCHAR2 ,
911 x_profile_code IN VARCHAR2 ,
912 x_tolerance_limit IN NUMBER ,
913 x_sys_award_year IN VARCHAR2 ,
914 x_award_year_status_code IN VARCHAR2 ,
915 x_pell_participant_code IN VARCHAR2 ,
916 x_dl_participant_code IN VARCHAR2 ,
917 x_mode IN VARCHAR2,
918 x_publish_in_ss_flag IN VARCHAR2
919 ) AS
920 /*
921 || Created By : prchandr
922 || Created On : 28-MAR-2001
923 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
924 || Known limitations, enhancements or remarks :
925 || Change History :
926 || Who When What
927 || (reverse chronological order - newest change first)
928 */
929 CURSOR c1 IS
930 SELECT rowid
931 FROM igf_ap_batch_aw_map_all
932 WHERE bam_id = x_bam_id;
933
934 BEGIN
935
936 OPEN c1;
937 FETCH c1 INTO x_rowid;
938 IF (c1%NOTFOUND) THEN
939 CLOSE c1;
943 x_batch_year,
940
941 insert_row (
942 x_rowid,
944 x_ci_sequence_number,
945 x_ci_cal_type,
946 x_ci_sequence_number_acad,
947 x_ci_cal_type_acad,
948 x_ci_cal_type_adm,
949 x_ci_sequence_number_adm,
950 x_bam_id,
951 x_efc_frml ,
952 x_num_days_divisor,
953 x_roundoff_fact ,
954 x_efc_dob,
955 x_dl_code,
956 x_ffel_code,
957 x_pell_code,
958 x_isir_code,
959 x_profile_code,
960 x_tolerance_limit ,
961 x_sys_award_year ,
962 x_award_year_status_code,
963 x_pell_participant_code,
964 x_dl_participant_code,
965 x_mode,
966 x_publish_in_ss_flag
967 );
968 RETURN;
969 END IF;
970 CLOSE c1;
971
972 update_row (
973 x_rowid,
974 x_batch_year,
975 x_ci_sequence_number,
976 x_ci_cal_type,
977 x_ci_sequence_number_acad,
978 x_ci_cal_type_acad,
979 x_ci_cal_type_adm,
980 x_ci_sequence_number_adm,
981 x_bam_id,
982 x_css_academic_year,
983 x_efc_frml ,
984 x_num_days_divisor ,
985 x_roundoff_fact ,
986 x_efc_dob,
987 x_dl_code,
988 x_ffel_code,
989 x_pell_code,
990 x_isir_code,
991 x_profile_code,
992 x_tolerance_limit ,
993 x_sys_award_year ,
994 x_award_year_status_code,
995 x_pell_participant_code,
996 x_dl_participant_code,
997 x_mode,
998 x_publish_in_ss_flag
999 );
1000
1001 END add_row;
1002
1003
1004 PROCEDURE delete_row (
1005 x_rowid IN VARCHAR2
1006 ) AS
1007 /*
1008 || Created By : prchandr
1009 || Created On : 28-MAR-2001
1010 || Purpose : Handles the DELETE DML logic for the table.
1011 || Known limitations, enhancements or remarks :
1012 || Change History :
1013 || Who When What
1014 || (reverse chronological order - newest change first)
1015 */
1016 BEGIN
1017
1018 before_dml (
1019 p_action => 'DELETE',
1020 x_rowid => x_rowid
1021 );
1022
1023 DELETE FROM igf_ap_batch_aw_map_all
1024 WHERE rowid = x_rowid;
1025
1026 IF (SQL%NOTFOUND) THEN
1027 RAISE NO_DATA_FOUND;
1028 END IF;
1029
1030 END delete_row;
1031
1032
1033 END igf_ap_batch_aw_map_pkg;
[Home] [Help]