1 PACKAGE BODY igf_sl_gen AS
2 /* $Header: IGFSL12B.pls 120.4 2006/08/07 13:21:12 azmohamm ship $ */
3
4
5 ------------------------------------------------------------------------
6 -- Who When What
7 ------------------------------------------------------------------------
8 -- azmohamm 03-AUG-2006 FA-163 : Added chk_cl_gplus function
9 ------------------------------------------------------------------------
10 -- museshad 05-May-2005 Bug# 4346258.
11 -- Modified the function
12 -- 'get_cl_version' so that it takes
13 -- into account any overriding CL version
14 -- for a specific Organization Unit in
15 -- FFELP Setup override.
16 ------------------------------------------------------------------------
17 -- sjadhav 09-Nov-2004 Bug #3416936.added rel code to cl version
18 ------------------------------------------------------------------------
19 -- ayedubat 20-OCT-2004 FA 149 COD-XML Standards build bug # 3416863
20 -- Changed the logic as per the TD
21 ------------------------------------------------------------------------
22 -- svuppala 20-Oct-2004 Bug 3416936 Added new update change status
23 ------------------------------------------------------------------------
24
25 -- sjadhav 15-Oct-2004 Bug 3416863
26 -- Added ENTITY_ID for CODXML
27 ------------------------------------------------------------------------
28 -- smadathi 14-oct-2004 Bug 3416936.Added new generic functions as
29 -- given in the TD.
30 ------------------------------------------------------------------------
31 -- ugummall 14-NOV-2003 Bug 3102439. FA 126 - Multiple FA Offices.
32 -- Added the cursor cur_get_num_applinst.
33 ------------------------------------------------------------------------
34 -- ugummall 14-OCT-2003 Bug# 3102439. FA 126 Multiple FA Offices
35 -- Added new routines get_associated_org and
36 -- get_stu_fao_code.
37 ------------------------------------------------------------------------
38 -- bkkumar 15-Sep-2003 Bug# 3104228. FA 122 Loans Enhancements
39 -- Added new routine check_rel,get_person_details
40 -- and check_lend_relation
41 ------------------------------------------------------------------------
42 -- sjadhav 30-Apr-2003 Bug 2922549.
43 -- Modified get_person_phone
44 -- added code to strip phone number
45 -- of special characters
46 ------------------------------------------------------------------------
47 -- sjadhav 24-Feb-2003 Bug 275823.
48 -- Modified get_person_phone
49 -- Added CONTACT_POINT_TYPE in the
50 -- query to read phone numbers
51 ------------------------------------------------------------------------
52 --
53 -- Bug 2415041, sjadhav
54 -- Following fields are deemed as optional henceforth
55 --
56 -- S_PERMT_ADDR2
57 -- P_PERMT_ADDR2
58 -- S_MIDDLE_NAME
59 -- P_MIDDLE_NAME
60 -- S_PERMT_PHONE
61 -- P_PERMT_PHONE
62 --
63 -- set_complete_status for these fields is taken out NOCOPY
64 -- fill in spaces for addr2/middle initial/
65 -- all these fields if null are filled with spaces
66 -- while sending origination record [ see igfsl08bpls ]
67 --
68 -- Phone number if not available then 'N/A' is returned
69 -- Area Code is padded with '000' if it is null
70 -- Coutry Code is not padded with anythin
71 --
72 -- Created By : venagara
73 -- Date Created On : 2000/11/20
74 -- Purpose :
75 -- Know limitations, enhancements or remarks
76 -- Change History
77 --
78 -- (reverse chronological order - newest change first)
79 ------------------------------------------------------------------------
80 --
81
82 g_debug_string VARCHAR2(4000) := NULL;
83
84 FUNCTION chk_dl_fed_fund_code(p_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE)
85 RETURN VARCHAR2
86 AS
87 /*************************************************************
88 Created By : venagara
89 Date Created On : 2000/11/20
90 Purpose :
91 Know limitations, enhancements or remarks
92 Change History
93 Who When What
94
95 (reverse chronological order - newest change first)
96 ***************************************************************/
97
98 BEGIN
99
100 IF UPPER(p_fed_fund_code) in ('DLS','DLU','DLP','GPLUSDL') then
101 RETURN 'TRUE';
102 END IF;
103
104 RETURN 'FALSE';
105 END chk_dl_fed_fund_code;
106
107
108
109 FUNCTION chk_dl_stafford(p_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE)
110 RETURN VARCHAR2
111 AS
112 /*************************************************************
113 Created By : venagara
114 Date Created On : 2000/11/20
115 Purpose :
116 Know limitations, enhancements or remarks
117 Change History
118 Who When What
119
120 (reverse chronological order - newest change first)
121 ***************************************************************/
122
123 l_temp VARCHAR2(10);
124 CURSOR c1 IS
125 SELECT 'x' FROM igf_lookups_view
126 WHERE lookup_type = 'IGF_SL_DL_STAFFORD'
127 AND lookup_code = UPPER(p_fed_fund_code)
128 AND enabled_flag = 'Y';
129 BEGIN
130 OPEN c1;
131 FETCH c1 into l_temp;
132 IF c1%NOTFOUND THEN
133 CLOSE c1;
134 RETURN 'FALSE';
135 END IF;
136
137 CLOSE c1;
138 RETURN 'TRUE';
139 END chk_dl_stafford;
140
141
142
143 FUNCTION chk_dl_plus(p_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE)
144 RETURN VARCHAR2
145 AS
146 /*************************************************************
147 Created By : venagara
148 Date Created On : 2000/11/20
149 Purpose :
150 Know limitations, enhancements or remarks
151 Change History
152 Who When What
153
154 (reverse chronological order - newest change first)
155 ***************************************************************/
156
157 l_temp VARCHAR2(10);
158 CURSOR c1 IS
159 SELECT 'x' FROM igf_lookups_view
160 WHERE lookup_type = 'IGF_SL_DL_PLUS'
161 AND lookup_code = UPPER(p_fed_fund_code)
162 AND enabled_flag = 'Y';
163 BEGIN
164 OPEN c1;
165 FETCH c1 into l_temp;
166 IF c1%NOTFOUND THEN
167 CLOSE c1;
168 RETURN 'FALSE';
169 END IF;
170
171 CLOSE c1;
172 RETURN 'TRUE';
173 END chk_dl_plus;
174
175
176
177
178 FUNCTION chk_cl_fed_fund_code(p_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE)
179 RETURN VARCHAR2
180 AS
181 /*************************************************************
182 Created By : venagara
183 Date Created On : 2000/11/20
184 Purpose :
185 Know limitations, enhancements or remarks
186 Change History
187 Who When What
188 azmohamm 24-JUL-2006 FA-163: Included GPLUSFL funcode
189 (reverse chronological order - newest change first)
190 ***************************************************************/
191
192 BEGIN
193
194 IF UPPER(p_fed_fund_code) in ('FLS','FLU','FLP','ALT','GPLUSFL') then
195 RETURN 'TRUE';
196 END IF;
197
198 RETURN 'FALSE';
199 END chk_cl_fed_fund_code;
200
201 FUNCTION chk_cl_gplus(p_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE)
202 RETURN VARCHAR2
203 AS
204 /*************************************************************
205 Created By : azmohamm
206 Date Created On : 2006/07/24
207 Purpose :
208 Know limitations, enhancements or remarks
209 Change History
210 Who When What
211
212 (reverse chronological order - newest change first)
213 ***************************************************************/
214
215 l_temp VARCHAR2(10);
216 CURSOR c1 IS
217 SELECT 'x' FROM igf_lookups_view
218 WHERE lookup_type = 'IGF_SL_CL_GPLUS'
219 AND lookup_code = UPPER(p_fed_fund_code)
220 AND enabled_flag = 'Y';
221 BEGIN
222 OPEN c1;
223 FETCH c1 into l_temp;
224 IF c1%NOTFOUND THEN
225 CLOSE c1;
226 RETURN 'FALSE';
227 END IF;
228
229 CLOSE c1;
230 RETURN 'TRUE';
231 END chk_cl_gplus;
232
233 FUNCTION chk_cl_stafford(p_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE)
234 RETURN VARCHAR2
235 AS
236 /*************************************************************
237 Created By : venagara
238 Date Created On : 2000/11/20
239 Purpose :
240 Know limitations, enhancements or remarks
241 Change History
242 Who When What
243
244 (reverse chronological order - newest change first)
245 ***************************************************************/
246
247 l_temp VARCHAR2(10);
248 CURSOR c1 IS
249 SELECT 'x' FROM igf_lookups_view
250 WHERE lookup_type = 'IGF_SL_CL_STAFFORD'
251 AND lookup_code = UPPER(p_fed_fund_code)
252 AND enabled_flag = 'Y';
253 BEGIN
254 OPEN c1;
255 FETCH c1 into l_temp;
256 IF c1%NOTFOUND THEN
257 CLOSE c1;
258 RETURN 'FALSE';
259 END IF;
260
261 CLOSE c1;
262 RETURN 'TRUE';
263 END chk_cl_stafford;
264
265
266
267 FUNCTION chk_cl_plus(p_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE)
268 RETURN VARCHAR2
269 AS
270 /*************************************************************
271 Created By : venagara
272 Date Created On : 2000/11/20
273 Purpose :
274 Know limitations, enhancements or remarks
275 Change History
276 Who When What
277
278 (reverse chronological order - newest change first)
279 ***************************************************************/
280
281 l_temp VARCHAR2(10);
282 CURSOR c1 IS
283 SELECT 'x' FROM igf_lookups_view
284 WHERE lookup_type = 'IGF_SL_CL_PLUS'
285 AND lookup_code = UPPER(p_fed_fund_code)
286 AND enabled_flag = 'Y';
287 BEGIN
288 OPEN c1;
289 FETCH c1 into l_temp;
290 IF c1%NOTFOUND THEN
291 CLOSE c1;
292 RETURN 'FALSE';
293 END IF;
294
295 CLOSE c1;
296 RETURN 'TRUE';
297 END chk_cl_plus;
298
299 /* Function to check Alternative Loan */
300 FUNCTION chk_cl_alt(p_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE)
301 RETURN VARCHAR2
302 AS
303 /*************************************************************
304 Created By : pkpatel
305 Date Created On : 2001/05/09
306 Purpose :
307 Know limitations, enhancements or remarks
308 Change History
309 Who When What
310
311 (reverse chronological order - newest change first)
312 ***************************************************************/
313
314 BEGIN
315
316 IF UPPER(p_fed_fund_code) = 'ALT' then
317 RETURN 'TRUE';
318 END IF;
319
320 RETURN 'FALSE';
321 END chk_cl_alt;
322
323
324
325 FUNCTION base10_to_base36(p_base_10 NUMBER)
326 RETURN VARCHAR
327 AS
328 /*************************************************************
329 Created By : venagara
330 Date Created On : 2000/11/20
331 Purpose :
332 Know limitations, enhancements or remarks
333 Change History
334 Who When What
335
336 (reverse chronological order - newest change first)
337 ***************************************************************/
338
339 l_power NUMBER(9);
340 l_remainder NUMBER(9);
341 l_base10 NUMBER(9);
342 l_base36 NUMBER(9);
343 l_unit NUMBER(9);
344 l_base36_char VARCHAR2(4000);
345 BEGIN
346
347 -- Algorithm is given in the Appendex C of CL Release 5
348 l_base10 := p_base_10;
349 FOR l_power in 1..999999999 LOOP
350
351 l_remainder := mod(l_base10, power(36,l_power));
352
353 l_unit := l_remainder / power(36,(l_power-1));
354
355 IF l_unit < 10 THEN
356 l_base36 := l_unit + 48;
357 ELSE
358 l_base36 := l_unit + 55;
359 END IF;
360
361 l_base36_char := fnd_global.local_chr(l_base36) || l_base36_char;
362
363 l_base10 := l_base10 - l_remainder;
364
365 IF l_base10 = 0 THEN
366 EXIT;
367 END IF;
368
369 END LOOP;
370
371 RETURN l_base36_char;
372
373 END base10_to_base36;
374
375
376 FUNCTION get_grade_level_desc(p_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE,
377 p_grade_level_code igf_sl_lor_all.grade_level_code%TYPE)
378 RETURN VARCHAR2
379 AS
380 /*************************************************************
381 Created By : venagara
382 Date Created On : 2000/11/20
383 Purpose :
384 Know limitations, enhancements or remarks
385 Change History
386 Who When What
387
388 (reverse chronological order - newest change first)
389 ***************************************************************/
390 BEGIN
391 RETURN p_grade_level_code||'-'||igf_aw_gen.lookup_desc('IGF_SL_CL_GRADE_LEVEL', p_grade_level_code);
392 END get_grade_level_desc;
393
394
395 FUNCTION get_enrollment_desc(p_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE,
396 p_enrollment_code igf_sl_lor_all.enrollment_code%TYPE)
397 RETURN VARCHAR2
398 AS
399 /*************************************************************
400 Created By : venagara
401 Date Created On : 2000/11/20
402 Purpose :
403 Know limitations, enhancements or remarks
404 Change History
405 Who When What
406
407 (reverse chronological order - newest change first)
408 ***************************************************************/
409 BEGIN
410 RETURN p_enrollment_code||'-'||igf_aw_gen.lookup_desc('IGF_SL_CL_ENROL_STATUS', p_enrollment_code) ;
411 END get_enrollment_desc;
412
413
414
415 FUNCTION get_dl_version(p_ci_cal_type igf_sl_dl_setup_all.ci_cal_type%TYPE,
416 p_ci_seq_num igf_sl_dl_setup_all.ci_sequence_number%TYPE)
417 RETURN VARCHAR2
418 AS
419 /*************************************************************
420 Created By : venagara
421 Date Created On : 2000/11/20
422 Purpose :
423 Know limitations, enhancements or remarks
424 Change History
425 Who When What
426
427 (reverse chronological order - newest change first)
428 ***************************************************************/
429 l_dl_version igf_sl_dl_setup_all.dl_version%TYPE;
430 CURSOR c_dl_setup IS
431 SELECT dl_version FROM igf_sl_dl_setup_all
432 WHERE ci_cal_type = p_ci_cal_type
433 AND ci_sequence_number = p_ci_seq_num;
434 BEGIN
435
436 OPEN c_dl_setup;
437 FETCH c_dl_setup into l_dl_version;
438 IF c_dl_setup%NOTFOUND THEN
439 CLOSE c_dl_setup;
440 RAISE NO_DATA_FOUND;
441 END IF;
442 CLOSE c_dl_setup;
443 RETURN l_dl_version;
444 END get_dl_version;
445
446
447
448
449 FUNCTION get_cl_version(p_ci_cal_type igf_sl_cl_setup_all.ci_cal_type%TYPE,
450 p_ci_seq_num igf_sl_cl_setup_all.ci_sequence_number%TYPE,
451 p_relationship_cd igf_sl_cl_setup_all.relationship_cd%TYPE,
452 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
453 RETURN VARCHAR2
454 AS
455 /*************************************************************
456 Created By : venagara
457 Date Created On : 2000/11/20
458 Purpose :
459 Know limitations, enhancements or remarks
460 Change History
461 Who When What
462 museshad 05-May-2005 Bug #4346258
463 1) Added a new parameter - p_base_id to the
464 function. This parameter is used to
465 arrive at the associated Organization Id
466 Note: The function has not been overloaded
467 to yield the correct CL version number
468 2) Modified the function so that it takes
469 into account any overriding CL version
470 for a specific Organization Unit in
471 FFELP Setup override.
472
473 bkkumar 30-sep-2003 FA 122 Loans Enhancements
474 Changed the entire code
475 (reverse chronological order - newest change first)
476 ***************************************************************/
477 CURSOR cur_cl_version (p_ci_cal_type igf_sl_cl_setup_all.ci_cal_type%TYPE,
478 p_ci_seq_num igf_sl_cl_setup_all.ci_sequence_number%TYPE,
479 p_relationship_cd igf_sl_cl_setup_all.relationship_cd%TYPE,
480 p_party_id hz_parties.party_id%TYPE
481 ) IS
482 SELECT cl_version
483 FROM igf_sl_cl_setup_all
484 WHERE ci_cal_type = p_ci_cal_type
485 AND ci_sequence_number = p_ci_seq_num
486 AND relationship_cd = p_relationship_cd
487 AND NVL(PARTY_ID, -99) = NVL(p_party_id, -99);
488
489 lv_cl_version igf_sl_cl_setup_all.cl_version%TYPE;
490 l_v_party_number hz_parties.party_number%TYPE;
491 l_v_org_party_id hz_parties.party_id%TYPE;
492 l_v_module VARCHAR2(1024);
493 l_v_return_status VARCHAR2(1024);
494 l_v_msg_data VARCHAR2(1024);
495
496 BEGIN
497 -- Get Associated Org Id
498 igf_sl_gen.get_associated_org(p_base_id, l_v_party_number, l_v_org_party_id, l_v_module, l_v_return_status, l_v_msg_data);
499
500 OPEN cur_cl_version( p_ci_cal_type, p_ci_seq_num, p_relationship_cd, l_v_org_party_id);
501 FETCH cur_cl_version INTO lv_cl_version;
502 CLOSE cur_cl_version;
503
504 -- Overriding CL Setup is missing Hence going for default setup.
505 IF lv_cl_version IS NULL THEN
506 OPEN cur_cl_version (p_ci_cal_type, p_ci_seq_num, p_relationship_cd, NULL);
507 FETCH cur_cl_version INTO lv_cl_version;
508 CLOSE cur_cl_version ;
509 END IF;
510 -- END Overriding CL Setup is missing Hence going for default se
511 RETURN lv_cl_version;
512
513 END get_cl_version;
514
515
516
517
518 FUNCTION get_dl_file_type(p_dl_version igf_sl_dl_file_type.dl_version%TYPE,
519 p_dl_file_type igf_sl_dl_file_type.dl_file_type%TYPE,
520 p_dl_loan_catg igf_sl_dl_file_type.dl_loan_catg%TYPE,
521 p_return_type VARCHAR2)
522 RETURN VARCHAR2
523 AS
524 /*************************************************************
525 Created By : venagara
526 Date Created On : 2000/11/20
527 Purpose :
528 Know limitations, enhancements or remarks
529 Change History
530 Who When What
531
532 (reverse chronological order - newest change first)
533 ***************************************************************/
534 l_message_class igf_sl_dl_file_type.message_class%TYPE;
535 l_batch_type igf_sl_dl_file_type.batch_type%TYPE;
536 l_cycle_year igf_sl_dl_file_type.cycle_year%TYPE;
537 l_rec_length igf_sl_dl_file_type.rec_length%TYPE;
538
539 CURSOR c_file_type IS
540 SELECT message_class, batch_type, cycle_year, rec_length FROM igf_sl_dl_file_type
541 WHERE dl_version = p_dl_version
542 AND dl_file_type = p_dl_file_type
543 AND dl_loan_catg = p_dl_loan_catg;
544 BEGIN
545 OPEN c_file_type;
546 FETCH c_file_type INTO l_message_class, l_batch_type, l_cycle_year, l_rec_length;
547 IF c_file_type%NOTFOUND THEN
548 CLOSE c_file_type;
549 RAISE NO_DATA_FOUND;
550 END IF;
551 CLOSE c_file_type;
552
553 IF UPPER(p_return_type) = 'MESSAGE-CLASS' THEN
554 RETURN l_message_class;
555 ELSIF UPPER(p_return_type) = 'BATCH-TYPE' THEN
556 RETURN l_batch_type;
557 ELSIF UPPER(p_return_type) = 'CYCLE-YEAR' THEN
558 RETURN l_cycle_year;
559 ELSIF UPPER(p_return_type) = 'REC-LENGTH' THEN
560 RETURN l_rec_length;
561 END IF;
562 RETURN NULL;
563
564 END get_dl_file_type;
565
566
567
568 PROCEDURE get_dl_batch_details(p_message_class IN igf_sl_dl_file_type.message_class%TYPE,
569 p_batch_type IN igf_sl_dl_file_type.batch_type%TYPE,
570 p_dl_version OUT NOCOPY igf_sl_dl_file_type.dl_version%TYPE,
571 p_dl_file_type OUT NOCOPY igf_sl_dl_file_type.dl_file_type%TYPE,
572 p_dl_loan_catg OUT NOCOPY igf_sl_dl_file_type.dl_loan_catg%TYPE)
573 AS
574 /*************************************************************
575 Created By : venagara
576 Date Created On : 2000/11/20
577 Purpose :
578 Know limitations, enhancements or remarks
579 Change History
580 Who When What
581
582 (reverse chronological order - newest change first)
583 ***************************************************************/
584 l_dl_version igf_sl_dl_file_type.dl_version%TYPE;
585 l_dl_file_type igf_sl_dl_file_type.dl_file_type%TYPE;
586 l_dl_loan_catg igf_sl_dl_file_type.dl_loan_catg%TYPE;
587
588 CURSOR c_file_type IS
589 SELECT dl_version, dl_file_type, dl_loan_catg FROM igf_sl_dl_file_type
590 WHERE message_class = p_message_class
591 AND batch_type = p_batch_type;
592 BEGIN
593 OPEN c_file_type;
594 FETCH c_file_type INTO l_dl_version, l_dl_file_type, l_dl_loan_catg;
595 IF c_file_type%NOTFOUND THEN
596 l_dl_version := 'INVALID-FILE';
597 l_dl_file_type := 'INVALID-FILE';
598 l_dl_loan_catg := 'INVALID-FILE';
599 END IF;
600 CLOSE c_file_type;
601 p_dl_version := l_dl_version;
602 p_dl_file_type := l_dl_file_type;
603 p_dl_loan_catg := l_dl_loan_catg;
604
605 END get_dl_batch_details;
606
607
608 FUNCTION get_cl_file_type(p_cl_version igf_sl_dl_file_type.dl_version%TYPE,
609 p_cl_file_type igf_sl_dl_file_type.dl_file_type%TYPE,
610 p_return_type VARCHAR2)
611 RETURN VARCHAR2
612 AS
613 /*************************************************************
614 Created By : venagara
615 Date Created On : 2000/11/20
616 Purpose :
617 Know limitations, enhancements or remarks
618 Change History
619 Who When What
620
621 (reverse chronological order - newest change first)
622 ***************************************************************/
623 l_file_ident_code igf_sl_cl_file_type.file_ident_code%TYPE;
624 l_file_ident_name igf_sl_cl_file_type.file_ident_name%TYPE;
625
626 CURSOR c_file_type IS
627 SELECT file_ident_code, file_ident_name FROM igf_sl_cl_file_type
628 WHERE cl_version = p_cl_version
629 AND cl_file_type = p_cl_file_type;
630 BEGIN
631 OPEN c_file_type;
632 FETCH c_file_type INTO l_file_ident_code, l_file_ident_name;
633 IF c_file_type%NOTFOUND THEN
634 CLOSE c_file_type;
635 RAISE NO_DATA_FOUND;
636 END IF;
637 CLOSE c_file_type;
638
639 IF UPPER(p_return_type) = 'FILE-IDENT-CODE' THEN
640 RETURN l_file_ident_code;
641 ELSIF UPPER(p_return_type) = 'FILE-IDENT-NAME' THEN
642 RETURN l_file_ident_name;
643 END IF;
644 RETURN NULL;
645
646 END get_cl_file_type;
647
648 PROCEDURE get_cl_batch_details(p_file_ident_code IN igf_sl_cl_file_type.file_ident_code%TYPE,
649 p_file_ident_name IN igf_sl_cl_file_type.file_ident_name%TYPE,
650 p_cl_version OUT NOCOPY igf_sl_cl_file_type.cl_version%TYPE,
651 p_cl_file_type OUT NOCOPY igf_sl_cl_file_type.cl_file_type%TYPE)
652 AS
653 /*************************************************************
654 Created By : venagara
655 Date Created On : 2000/11/20
656 Purpose :
657 Know limitations, enhancements or remarks
658 Change History
659 Who When What
660
661 (reverse chronological order - newest change first)
662 ***************************************************************/
663 l_cl_version igf_sl_cl_file_type.cl_version%TYPE;
664 l_cl_file_type igf_sl_cl_file_type.cl_file_type%TYPE;
665
666 CURSOR c_file_type IS
667 SELECT cl_version, cl_file_type FROM igf_sl_cl_file_type
668 WHERE file_ident_code = p_file_ident_code
669 AND file_ident_name = p_file_ident_name;
670 BEGIN
671 OPEN c_file_type;
672 FETCH c_file_type INTO l_cl_version, l_cl_file_type;
673 IF c_file_type%NOTFOUND THEN
674 l_cl_version := 'INVALID-FILE';
675 l_cl_file_type := 'INVALID-FILE';
676 END IF;
677 CLOSE c_file_type;
678
679 p_cl_version := l_cl_version;
680 p_cl_file_type := l_cl_file_type;
681
682 END get_cl_batch_details;
683
684 -- Function to return the Disbursement Date
685 FUNCTION get_disb_date(p_loan_id IN igf_sl_loans.loan_id%TYPE,
686 p_disb_num IN igf_aw_awd_disb.disb_num%TYPE)
687 RETURN DATE
688 AS
689 /*************************************************************
690 Created By : pkpatel
691 Date Created On : 2001/05/09
692 Purpose :
693 Know limitations, enhancements or remarks
694 Change History
695 Who When What
696 pkpatel 11-may-2001 return NULL instead of raising Exception when no data is found
697 (reverse chronological order - newest change first)
698 ***************************************************************/
699 /* Declared the CURSOR to select the disbursement date for the particular loan ID and Disbursement Number */
700 CURSOR c_disb_date IS
701 SELECT iaad.disb_date
702 FROM igf_sl_loans isl, igf_aw_awd_disb iaad
703 WHERE isl.loan_id = p_loan_id
704 AND iaad.disb_num = p_disb_num
705 AND isl.award_id = iaad.award_id;
706
707 l_disb_date IGF_AW_AWD_DISB.disb_date%TYPE; --variable declared to hold the Disbursement date
708 BEGIN
709 OPEN c_disb_date;
710 FETCH c_disb_date INTO l_disb_date;
711 IF c_disb_date%notfound THEN
712 CLOSE c_disb_date;
713 RETURN NULL;
714 END IF;
715 CLOSE c_disb_date;
716 RETURN l_disb_date;
717 END get_disb_date;
718
719
720 -- Function to get the phone number of the person
721 FUNCTION get_person_phone(p_person_id IGS_PE_CONTACTS_V.owner_table_id%TYPE)
722 RETURN VARCHAR2
723 AS
724 --
725 ------------------------------------------------------------------------
726 -- Created By : pkpatel
727 -- Date Created On : 2001/05/09
728 -- Purpose :
729 -- Know limitations, enhancements or remarks
730 -- Change History
731 -- Who When What
732 ------------------------------------------------------------------------
733 -- sjadhav 30-Apr-2003 Bug 2922549.
734 -- added code to strip phone number
735 -- of special characters
736 ------------------------------------------------------------------------
737 -- sjadhav 24-Feb-2003 Bug 275823.
738 -- Added CONTACT_POINT_TYPE in the
739 -- query to read phone numbers
740 ------------------------------------------------------------------------
741 --
742 -- Declared the CURSOR to get the Primary Telephone Number for a particular Student */
743 --
744
745 CURSOR c_person_phone
746 IS
747 SELECT
748 phone_area_code,
749 phone_number
750 FROM igs_pe_contacts_v
751 WHERE owner_table_id = p_person_id
752 AND primary_flag = 'Y'
753 AND status = 'A'
754 AND contact_point_type = 'PHONE';
755
756 --
757 -- Declared variables to hold the phone details information */
758 --
759 l_phone_area_code igs_pe_contacts_v.phone_area_code%TYPE;
760 l_phone_number igs_pe_contacts_v.phone_number%TYPE;
761
762
763 BEGIN
764
765 OPEN c_person_phone;
766 FETCH c_person_phone
767 INTO l_phone_area_code, l_phone_number;
768
769 IF c_person_phone%NOTFOUND THEN
770 CLOSE c_person_phone;
771 RETURN 'N/A';
772 END IF;
773 CLOSE c_person_phone;
774
775 l_phone_area_code := TRANSLATE (UPPER(LTRIM(RTRIM(l_phone_area_code))),'1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ`~!@#$%^&*_+=-,./?><():; ','1234567890');
776 l_phone_area_code := LPAD(l_phone_area_code,3,'0');
777
778 l_phone_number := TRANSLATE (UPPER(LTRIM(RTRIM(l_phone_number))),'1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ`~!@#$%^&*_+=-,./?><():; ','1234567890');
779 l_phone_number := LPAD(l_phone_number,7,'0');
780
781 RETURN ( NVL(l_phone_area_code,'000')|| l_phone_number);
782
783 END get_person_phone;
784
785 PROCEDURE check_lend_relation( p_person_id IN igf_sl_cl_pref_lenders.person_id%TYPE,
786 p_start_date IN DATE,
787 p_end_date IN DATE,
788 p_message OUT NOCOPY VARCHAR2)
789 AS
790 /*************************************************************
791 Created By : bkkumar
792 Date Created On : 05-Sep-2003
793 Purpose : FA 122 Loans Enhancements.
794 It checks if the lender set up is valid or not.
795 Know limitations, enhancements or remarks
796 Change History
797 Who When What
798
799 (reverse chronological order - newest change first)
800 ***************************************************************/
801
802 CURSOR c_chk_active_rec (
803 cp_person_id igf_sl_cl_pref_lenders.person_id%TYPE
804 )
805 IS
806 SELECT count(*) cnt
807 FROM igf_sl_cl_pref_lenders
808 WHERE person_id = cp_person_id
809 AND end_date IS NULL;
810
811 l_chk_active_rec c_chk_active_rec%ROWTYPE;
812
813 CURSOR c_chk_overlap_date (
814 cp_person_id igf_sl_cl_pref_lenders.person_id%TYPE,
815 cp_start_date DATE,
816 cp_end_date DATE
817 )
818 IS
819 SELECT count(*) cnt
820 FROM igf_sl_cl_pref_lenders
821 WHERE person_id = cp_person_id
822 AND ( ( cp_start_date BETWEEN start_date AND NVL(end_date,TO_DATE('4712/12/31','YYYY/MM/DD')) )
823 OR ( NVL(cp_end_date,TO_DATE('4712/12/31','YYYY/MM/DD')) BETWEEN start_date AND NVL(end_date,TO_DATE('4712/12/31','YYYY/MM/DD')))
824 OR ( cp_start_date < start_date AND NVL(cp_end_date,TO_DATE('4712/12/31','YYYY/MM/DD')) > NVL(end_date,TO_DATE('4712/12/31','YYYY/MM/DD'))));
825
826 l_chk_overlap_date c_chk_overlap_date%ROWTYPE;
827
828 BEGIN
829
830 p_message := NULL;
831
832 l_chk_active_rec := NULL;
833
834 -- this cursor checks that there should be only one preferred lender at any point of time.
835 OPEN c_chk_active_rec(p_person_id);
836 FETCH c_chk_active_rec INTO l_chk_active_rec;
837 CLOSE c_chk_active_rec;
838
839 IF l_chk_active_rec.cnt IS NOT NULL AND l_chk_active_rec.cnt > 1 THEN
840 p_message := 'IGF_SL_PREF_LEND_ONE';
841 RETURN;
842 END IF;
843
844 l_chk_overlap_date := NULL;
845
846 -- this cursor checks that there should not be any overlapping dates
847 OPEN c_chk_overlap_date(p_person_id,p_start_date,p_end_date);
848 FETCH c_chk_overlap_date INTO l_chk_overlap_date;
849 CLOSE c_chk_overlap_date;
850 -- add a message to the logging framework
851 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
852 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_gen.check_lend_relation.debug','After the cursor to check overlapping dates');
853 END IF;
854
855 IF l_chk_overlap_date.cnt IS NOT NULL AND l_chk_overlap_date.cnt > 1 THEN
856 p_message := 'IGF_SL_PREF_LEND_DATES';
857 END IF;
858
859 EXCEPTION WHEN OTHERS THEN
860 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
861 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'igf.plsql.igf_sl_gen.check_lend_relation.exception',SQLERRM);
862 END IF;
863 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
864 fnd_message.set_token('NAME','IGF_SL_GEN.CHECK_LEND_RELATION');
865 igs_ge_msg_stack.conc_exception_hndl;
866 app_exception.raise_exception;
867
868 END check_lend_relation;
869
870 PROCEDURE get_person_details ( p_person_id IN igf_sl_cl_pref_lenders.person_id%TYPE,
871 p_person_dtl_rec IN OUT NOCOPY person_dtl_cur)
872 AS
873 /*************************************************************
874 Created By : bkkumar
875 Date Created On : 05-Sep-2003
876 Purpose : FA 122 Loans Enhancements.
877 It gets the person details for all given person id
878 Know limitations, enhancements or remarks
879 Change History
880 Who When What
881 gmaheswa 24-Nov-2003 Bug : 3227107 Address active check changes
882 Modified c_get_paddr cursor to select active records only and to select
883 start date and end date from igs_pe_hz_pty_sites.
884 (reverse chronological order - newest change first)
885 **************************************************************/
886 CURSOR c_get_ssn (
887 cp_person_id igf_sl_cl_pref_lenders.person_id%TYPE,
888 cp_person_id_type igs_pe_person_id_typ.s_person_id_type%TYPE
889 )
890 IS
891 SELECT api.api_person_id_uf ssn,
892 api.person_id_type,
893 api.start_dt,
894 api.end_dt
895 FROM igs_pe_alt_pers_id api,
896 igs_pe_person_id_typ pid
897 WHERE api.pe_person_id = cp_person_id
898 AND api.person_id_type = pid.person_id_type
899 AND pid.s_person_id_type = cp_person_id_type --
900 AND SYSDATE BETWEEN api.start_dt AND NVL(api.end_dt,SYSDATE);
901
902 l_get_ssn c_get_ssn%ROWTYPE;
903
904 CURSOR c_get_name (
905 cp_person_id igf_sl_cl_pref_lenders.person_id%TYPE
906 )
907 IS
908 SELECT person_id,
909 person_number,
910 pre_name_adjunct,
911 first_name,
912 middle_name,
913 last_name,
914 title,
915 full_name,
916 suffix,
917 birth_date,
918 gender
919 FROM igs_pe_person_base_v
920 WHERE person_id = cp_person_id;
921
922 l_get_name c_get_name%ROWTYPE;
923
924
925 CURSOR c_get_emailaddr (
926 cp_person_id igf_sl_cl_pref_lenders.person_id%TYPE,
927 cp_primary_flag igs_pe_contacts_v.primary_flag%TYPE,
928 cp_status igs_pe_contacts_v.status%TYPE,
929 cp_contact_point_type igs_pe_contacts_v.contact_point_type%TYPE
930 )
931 IS
932 SELECT email_address
933 FROM igs_pe_contacts_v
934 WHERE owner_table_id = cp_person_id
935 AND primary_flag = cp_primary_flag
936 AND status = cp_status
937 AND contact_point_type = cp_contact_point_type ;
938
939 l_get_emailaddr c_get_emailaddr%ROWTYPE;
940
941 CURSOR c_get_paddr (
942 cp_person_id igf_sl_cl_pref_lenders.person_id%TYPE,
943 cp_identifying_address_flag hz_party_sites.identifying_address_flag%TYPE
944 )
945 IS
946 SELECT ps.party_id,
947 ps.identifying_address_flag,
948 l.address1,
949 l.address2,
950 l.address3,
951 l.address4,
952 l.city,
953 l.state,
954 l.province,
955 l.county,
956 l.country,
957 l.postal_code,
958 l.last_update_date
959 FROM hz_party_sites ps,
960 hz_locations l ,
961 igs_pe_hz_pty_sites ihps
962 WHERE ps.location_id = l.location_id
963 AND ps.party_site_id = ihps.party_site_id(+)
964 AND ps.identifying_address_flag = cp_identifying_address_flag
965 AND ( ps.status = 'A' AND SYSDATE BETWEEN NVL(ihps.start_date,SYSDATE)
966 AND NVL(ihps.end_date,SYSDATE))
967 AND ps.party_id = cp_person_id;
968
969 l_get_paddr c_get_paddr%ROWTYPE;
970
971
972 CURSOR c_get_lic_num (
973 cp_person_id igf_sl_cl_pref_lenders.person_id%TYPE,
974 cp_person_id_type igs_pe_person_id_typ.s_person_id_type%TYPE
975 )
976 IS
977 SELECT api.api_person_id,
978 api.region_cd,
979 api.person_id_type,
980 api.start_dt,
981 api.end_dt,
982 pid.s_person_id_type
983 FROM igs_pe_alt_pers_id api,
984 igs_pe_person_id_typ pid
985 WHERE api.pe_person_id = cp_person_id
986 AND api.person_id_type = pid.person_id_type
987 AND pid.s_person_id_type = cp_person_id_type
988 AND SYSDATE BETWEEN api.start_dt AND NVL(api.end_dt,SYSDATE);
989
990 l_get_lic_num c_get_lic_num%ROWTYPE;
991
992 CURSOR c_get_addr (
993 cp_person_id igf_sl_cl_pref_lenders.person_id%TYPE,
994 cp_status hz_party_site_uses.status%TYPE,
995 cp_site_use_type hz_party_site_uses.site_use_type%TYPE
996 )
997 IS
998 SELECT ps.party_id,
999 psu.site_use_type,
1000 ps.identifying_address_flag,
1001 l.address1,
1002 l.address2,
1003 l.address3,
1004 l.address4,
1005 l.city,
1006 l.state,
1007 l.province,
1008 l.county,
1009 l.country,
1010 l.postal_code,
1011 l.last_update_date
1012 FROM hz_party_sites ps,
1013 hz_locations l,
1014 hz_party_site_uses psu
1015 WHERE ps.location_id = l.location_id
1016 AND ps.party_site_id = psu.party_site_id
1017 AND psu.status = cp_status
1018 AND psu.site_use_type = cp_site_use_type
1019 AND SYSDATE BETWEEN NVL(ps.start_date_active,SYSDATE) AND NVL(ps.end_date_active,SYSDATE)
1020 AND ps.party_id = cp_person_id
1021 ORDER BY ps.start_date_active DESC;
1022
1023 l_get_addr c_get_addr%ROWTYPE;
1024
1025
1026 CURSOR c_get_reg_num (
1027 cp_person_id igf_sl_cl_pref_lenders.person_id%TYPE,
1028 cp_perm_res_cntry igs_pe_eit_perm_res_v.perm_res_cntry%TYPE
1029 )
1030 IS
1031 SELECT document_num
1032 FROM igs_pe_eit_perm_res_v
1033 WHERE perm_res_cntry = cp_perm_res_cntry
1034 AND person_id = cp_person_id
1035 AND SYSDATE BETWEEN start_date AND NVL(end_date,SYSDATE);
1036
1037 l_get_reg_num VARCHAR2(150);
1038
1039 CURSOR c_get_res_state (
1040 cp_person_id igf_sl_cl_pref_lenders.person_id%TYPE,
1041 cp_information_type igs_pe_eit.information_type%TYPE
1042 )
1043 IS
1044 SELECT pei_information1 state_code,
1045 start_date
1046 FROM igs_pe_eit
1047 WHERE person_id = cp_person_id
1048 AND information_type = cp_information_type
1049 AND SYSDATE BETWEEN start_date AND NVL(end_date,SYSDATE);
1050
1051 l_get_res_state c_get_res_state%ROWTYPE;
1052
1053
1054 CURSOR c_get_citzn_status (
1055 cp_person_id igf_sl_cl_pref_lenders.person_id%TYPE,
1056 cp_lookup_type igs_lookup_values.lookup_type%TYPE
1057 )
1058 IS
1059 SELECT lkup.tag,
1060 pct.restatus_code
1061 FROM igs_lookup_values lkup,
1062 igs_pe_eit_restatus_v pct
1063 WHERE lkup.lookup_type = cp_lookup_type
1064 AND lkup.lookup_code = pct.restatus_code
1065 AND pct.person_id = cp_person_id
1066 AND SYSDATE BETWEEN start_date AND NVL(end_date,SYSDATE)
1067 AND lkup.tag IN ('1','2','3');
1068
1069 l_get_citzn_status c_get_citzn_status%ROWTYPE;
1070
1071 l_length_alien_num NUMBER;
1072
1073 BEGIN
1074 -- this cursor gets the license number related info
1075 l_get_lic_num := NULL;
1076 OPEN c_get_lic_num(p_person_id,'DRIVER_LIC');
1077 FETCH c_get_lic_num INTO l_get_lic_num;
1078 CLOSE c_get_lic_num;
1079
1080 -- this cursor gets the citizenship status related info
1081 l_get_citzn_status := NULL;
1082 OPEN c_get_citzn_status(p_person_id,'PE_CITI_STATUS');
1083 FETCH c_get_citzn_status INTO l_get_citzn_status;
1084 CLOSE c_get_citzn_status;
1085
1086 -- this cursor gets the legal residence state related info
1087 l_get_res_state := NULL;
1088 OPEN c_get_res_state(p_person_id,'PE_STAT_RES_STATE');
1089 FETCH c_get_res_state INTO l_get_res_state;
1090 CLOSE c_get_res_state;
1091
1092 -- this cursor gets the registration number related info
1093 l_get_reg_num := NULL;
1094 OPEN c_get_reg_num(p_person_id,'US');
1095 FETCH c_get_reg_num INTO l_get_reg_num;
1096 CLOSE c_get_reg_num;
1097
1098 -- Bug # 5006583 - If alien registration number > 10 characters then put a null.
1099
1100 l_length_alien_num := length(l_get_reg_num);
1101 IF ( l_length_alien_num > 10) THEN
1102 l_get_reg_num := NULL;
1103 END IF;
1104
1105
1106 -- this cursor gets the SSN related info
1107 l_get_ssn := NULL;
1108 OPEN c_get_ssn(p_person_id,'SSN');
1109 FETCH c_get_ssn INTO l_get_ssn;
1110 CLOSE c_get_ssn;
1111
1112 -- this cursor gets the person name related info
1113 l_get_name := NULL;
1114 OPEN c_get_name(p_person_id);
1115 FETCH c_get_name INTO l_get_name;
1116 CLOSE c_get_name;
1117
1118 -- this cursor gets the local address related info
1119 l_get_addr := NULL;
1120 OPEN c_get_addr(p_person_id,'A','RESIDES_AT');
1121 FETCH c_get_addr INTO l_get_addr;
1122 CLOSE c_get_addr;
1123
1124 -- this cursor gets the permanent address related info
1125 l_get_paddr := NULL;
1126 OPEN c_get_paddr(p_person_id,'Y');
1127 FETCH c_get_paddr INTO l_get_paddr;
1128 CLOSE c_get_paddr;
1129
1130 -- this cursor gets the email address related info
1131 l_get_emailaddr := NULL;
1132 OPEN c_get_emailaddr(p_person_id,'Y','A','EMAIL');
1133 FETCH c_get_emailaddr INTO l_get_emailaddr;
1134 CLOSE c_get_emailaddr;
1135 -- add a message to the logging framework
1136 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1137 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_gen.get_person_details.debug','Before opening the ref cursor');
1138 END IF;
1139
1140 -- open the ref cursor with the values obtained from the above cursors.
1141 OPEN p_person_dtl_rec FOR
1142 SELECT l_get_reg_num,
1143 l_get_citzn_status.tag,
1144 l_get_name.birth_date,
1145 l_get_emailaddr.email_address,
1146 l_get_name.first_name,
1147 l_get_name.full_name,
1148 l_get_name.last_name,
1149 l_get_res_state.start_date,
1150 l_get_lic_num.api_person_id,
1151 l_get_lic_num.region_cd,
1152 l_get_name.middle_name,
1153 l_get_paddr.address1,
1154 l_get_paddr.address2,
1155 l_get_paddr.city,
1156 l_get_paddr.state,
1157 l_get_paddr.postal_code,
1158 igf_gr_gen.get_ssn_digits(NVL(l_get_ssn.ssn,'')),
1159 l_get_res_state.state_code,
1160 l_get_paddr.province,
1161 l_get_paddr.county,
1162 l_get_paddr.country,
1163 l_get_addr.address1,
1164 l_get_addr.address2,
1165 l_get_addr.city,
1166 l_get_addr.state,
1167 l_get_addr.postal_code
1168
1169 FROM DUAL;
1170
1171 EXCEPTION WHEN OTHERS THEN
1172 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1173 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'igf.plsql.igf_sl_gen.get_person_details.exception',SQLERRM);
1174 END IF;
1175 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1176 fnd_message.set_token('NAME','IGF_SL_GEN.GET_PERSON_DETAILS');
1177 igs_ge_msg_stack.conc_exception_hndl;
1178 app_exception.raise_exception;
1179
1180 END get_person_details;
1181
1182 PROCEDURE check_rel (
1183 p_rel_code IN igf_sl_cl_setup_all.relationship_cd%TYPE,
1184 p_flag OUT NOCOPY VARCHAR2
1185 )
1186 AS
1187 /*************************************************************
1188 Created By : bkkumar
1189 Date Created On : 05-Sep-2003
1190 Purpose : FA 122 Loans Enhancements.
1191 It checks whether the relationship exists or not.
1192 Know limitations, enhancements or remarks
1193 Change History
1194 Who When What
1195 bkkumar 10-apr-04 FACR116 - Added the check to select
1196 the rel_code from igf_aw_fund_cat_all
1197 (reverse chronological order - newest change first)
1198 **************************************************************/
1199
1200 CURSOR c_chk_rel_code1 (
1201 cp_rel_code igf_sl_cl_setup_all.relationship_cd%TYPE
1202 )
1203 IS
1204 SELECT relationship_cd
1205 FROM igf_sl_cl_setup_all
1206 WHERE relationship_cd = cp_rel_code
1207 AND ROWNUM = 1;
1208
1209 l_chk_rel_code1 c_chk_rel_code1%ROWTYPE;
1210
1211 CURSOR c_chk_rel_code2 (
1212 cp_rel_code igf_sl_cl_setup_all.relationship_cd%TYPE
1213 )
1214 IS
1215 SELECT relationship_cd
1216 FROM igf_sl_cl_pref_lenders
1217 WHERE relationship_cd = cp_rel_code
1218 AND ROWNUM = 1;
1219
1220 l_chk_rel_code2 c_chk_rel_code2%ROWTYPE;
1221 CURSOR c_chk_rel_code3 (
1222 cp_rel_code igf_sl_cl_setup_all.relationship_cd%TYPE
1223 )
1224 IS
1225 SELECT alt_rel_code
1226 FROM igf_aw_fund_cat_all
1227 WHERE alt_rel_code = cp_rel_code
1228 AND ROWNUM = 1;
1229
1230 l_chk_rel_code3 c_chk_rel_code3%ROWTYPE;
1231 BEGIN
1232
1233 l_chk_rel_code1 := NULL;
1234 -- cursor to check whether the relationship exists in the igf_Sl_cl_setup table
1235 OPEN c_chk_rel_code1(p_rel_code);
1236 FETCH c_chk_rel_code1 INTO l_chk_rel_code1;
1237 CLOSE c_chk_rel_code1;
1238
1239 l_chk_rel_code2 := NULL;
1240 -- cursor to check whether the relationship exists in the igf_sl_cl_pref_lenders table
1241 OPEN c_chk_rel_code2(p_rel_code);
1242 FETCH c_chk_rel_code2 INTO l_chk_rel_code2;
1243 CLOSE c_chk_rel_code2;
1244
1245 l_chk_rel_code3 := NULL;
1246 -- cursor to check whether the relationship exists in the igf_aw_fund_cat_all table
1247 OPEN c_chk_rel_code3(p_rel_code);
1248 FETCH c_chk_rel_code3 INTO l_chk_rel_code3;
1249 CLOSE c_chk_rel_code3;
1250
1251 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1252 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_gen.check_rel.debug','After the check of three cursors');
1253 END IF;
1254
1255 IF l_chk_rel_code1.relationship_cd IS NULL AND l_chk_rel_code2.relationship_cd IS NULL AND l_chk_rel_code3.alt_rel_code IS NULL THEN
1256 p_flag := 'FALSE';
1257 ELSE
1258 p_flag := 'TRUE';
1259 END IF;
1260
1261 EXCEPTION WHEN OTHERS THEN
1262 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1263 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'igf.plsql.igf_sl_gen.check_rel.exception',SQLERRM);
1264 END IF;
1265 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1266 fnd_message.set_token('NAME','IGF_SL_GEN.CHECK_REL');
1267 igs_ge_msg_stack.conc_exception_hndl;
1268 app_exception.raise_exception;
1269
1270 END check_rel;
1271
1272 PROCEDURE get_associated_org (p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
1273 x_org_unit_cd OUT NOCOPY hz_parties.party_number%TYPE,
1274 x_org_party_id OUT NOCOPY hz_parties.party_id%TYPE,
1275 x_module OUT NOCOPY VARCHAR2,
1276 x_return_status OUT NOCOPY VARCHAR2,
1277 x_msg_data OUT NOCOPY VARCHAR2)
1278 AS
1279 /*
1280 || Created By : ugummall
1281 || Created On : 14-OCT-2003
1282 || Purpose : Bug# 3102439. FA 126 Multiple FA Offices
1283 || For obtaining responsible org unit code that
1284 || is associated with the student.
1285 || Known limitations, enhancements or remarks :
1286 || Change History :
1287 || Who When What
1288 || ugummall 14-NOV-2003 Bug 3102439. FA 126 - Multiple FA Offices.
1289 || Added the cursor cur_get_num_applinst.
1290 || (reverse chronological order - newest change first)
1291 */
1292
1293 -- get Org Unit from program that is obatined as key program from enrollment api
1294 CURSOR c_get_org_enr (cp_course_cd igs_ps_ver_all.course_cd%TYPE,
1295 cp_version_number igs_ps_ver_all.version_number%TYPE) IS
1296 SELECT ps.responsible_org_unit_cd org_unit_cd,
1297 hz.party_id
1298 FROM igs_ps_ver_all ps,
1299 hz_parties hz
1300 WHERE hz.party_number = ps.responsible_org_unit_cd
1301 AND ps.course_cd = cp_course_cd
1302 AND ps.version_number = cp_version_number;
1303
1304 c_get_org_enr_rec c_get_org_enr%ROWTYPE;
1305
1306 -- get number of applications for the Person
1307 CURSOR cur_get_num_appl (p_person_id HZ_PARTIES.PARTY_ID%TYPE) IS
1308 SELECT count(ADMISSION_APPL_NUMBER) num_of_appls
1309 FROM IGS_AD_APPL_ALL appl,
1310 IGS_AD_APPL_STAT_V stat
1311 WHERE appl.ADM_APPL_STATUS = stat.ADM_APPL_STATUS
1312 AND stat.S_ADM_APPL_STATUS <> 'WITHDRAWN'
1313 AND PERSON_ID = p_person_id;
1314 rec_get_num_appl cur_get_num_appl%ROWTYPE;
1315
1316 -- get total number of application instances.
1317 CURSOR cur_get_num_applinst(p_person_id HZ_PARTIES.PARTY_ID%TYPE) IS
1318 SELECT count(*) num_of_records
1319 FROM IGS_AD_APPL_ALL appl,
1320 IGS_AD_PS_APPL_INST applinst,
1321 IGS_AD_OU_STAT_V igsl2
1322 WHERE appl.person_id = applinst.person_id
1323 AND appl.admission_appl_number = applinst.admission_appl_number
1324 AND igsl2.adm_outcome_status = applinst.adm_outcome_status
1325 AND igsl2.s_adm_outcome_status NOT IN ('CANCELLED', 'NO-QUOTA', 'REJECTED', 'SUSPEND', 'VOIDED', 'WITHDRAWN')
1326 AND applinst.person_id = p_person_id;
1327 rec_get_num_applinst cur_get_num_applinst%ROWTYPE;
1328
1329 -- get Org Unit from Admissions.
1330 CURSOR c_get_org_adm (p_person_id hz_parties.party_id%TYPE) IS
1331 SELECT ps.responsible_org_unit_cd,
1332 hz.party_name,
1333 hz.party_id,
1334 count(*) NUM_OF_RECORDS
1335 FROM igs_ps_ver_all ps,
1336 hz_parties hz,
1337 igs_ad_appl_all appl,
1338 igs_ad_ps_appl_inst applinst,
1339 igs_ad_appl_stat_v igsl1,
1340 igs_ad_ou_stat_v igsl2
1341 WHERE ps.responsible_org_unit_cd = hz.party_number
1342 AND appl.person_id = applinst.person_id
1343 AND appl.admission_appl_number = applinst.admission_appl_number
1344 AND applinst.course_cd = ps.course_cd
1345 AND applinst.crv_version_number = ps.version_number
1346 AND igsl1.adm_appl_status = appl.adm_appl_status
1347 AND igsl1.s_adm_appl_status <> 'WITHDRAWN'
1348 AND igsl2.adm_outcome_status = applinst.adm_outcome_status
1349 AND igsl2.s_adm_outcome_status not in ('CANCELLED','NO-QUOTA', 'REJECTED', 'SUSPEND', 'VOIDED', 'WITHDRAWN')
1350 AND applinst.person_id = p_person_id
1351 GROUP BY
1352 responsible_org_unit_cd,
1353 party_name,
1354 party_id;
1355 c_get_org_adm_rec c_get_org_adm%ROWTYPE;
1356
1357 -- get Org Unit from FA Base Record's assoc_org_num.
1358 CURSOR c_get_assoc_org IS
1359 SELECT fa.assoc_org_num,
1360 hz.party_number,
1361 hz.party_name,
1362 hz.party_id
1363 FROM igf_ap_fa_base_rec_all fa,
1364 hz_parties hz
1365 WHERE fa.base_id = p_base_id
1366 AND fa.assoc_org_num = hz.party_id;
1367 c_get_assoc_org_rec c_get_assoc_org%ROWTYPE;
1368
1369 l_person_id hz_parties.party_id%TYPE;
1370 x_key_program_course_cd igs_ps_ver_all.course_cd%TYPE;
1371 x_version_number igs_ps_ver_all.version_number%TYPE;
1372
1373
1374 BEGIN
1375 -- initialize
1376 x_return_status := 'S';
1377 x_msg_data := NULL;
1378 x_module := NULL;
1379 x_org_unit_cd := NULL;
1380 x_key_program_course_cd := NULL;
1381 x_version_number := NULL;
1382
1383 -- obtain the person_id from the base_id value
1384 l_person_id := igf_gr_gen.get_person_id(p_base_id);
1385
1386 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1387 g_debug_string := 'Base ID = ' || p_base_id || ' Person ID = ' || l_person_id;
1388 END IF;
1389
1390 -- determine the key program from the api. the key program is returned if the term has a
1391 -- key program override at a term level, otherwise the key program at the spa table is returned
1392 igf_ap_gen_001.get_key_program(p_base_id, x_key_program_course_cd, x_version_number);
1393 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1394 g_debug_string := g_debug_string || 'key program = ' || x_key_program_course_cd || ' version number = '||x_version_number ||':: ';
1395 END IF;
1396 IF(x_key_program_course_cd IS NOT NULL AND l_person_id IS NOT NULL)THEN
1397 OPEN c_get_org_enr(x_key_program_course_cd, x_version_number);
1398 FETCH c_get_org_enr INTO c_get_org_enr_rec;
1399 CLOSE c_get_org_enr;
1400
1401 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1402 g_debug_string := g_debug_string || 'Org Unit Code(from key program) = ' || c_get_org_enr_rec.org_unit_cd || ' :: ';
1403 END IF;
1404
1405 IF (c_get_org_enr_rec.org_unit_cd IS NOT NULL) THEN -- means, student is having key program.
1406 x_module := 'EN';
1407 x_org_unit_cd := c_get_org_enr_rec.org_unit_cd;
1408 x_org_party_id := c_get_org_enr_rec.party_id;
1409 END IF;
1410 ELSE
1411 -- no org unit could be derived from the key program
1412
1413 -- get number of applications the person has. if the person has one application and has one appl instance attached to it
1414 -- get the org unit of the program that he has applied for. if he has more than one application instance (this can be
1415 -- out of the same application or from a different application), then determine the org unit from the FA Base Record
1416 OPEN cur_get_num_appl(l_person_id);
1417 FETCH cur_get_num_appl INTO rec_get_num_appl;
1418 CLOSE cur_get_num_appl;
1419
1420 IF (rec_get_num_appl.num_of_appls >= 1) THEN
1421
1422 -- get total number of instances.
1423 OPEN cur_get_num_applinst(l_person_id);
1424 FETCH cur_get_num_applinst INTO rec_get_num_applinst;
1425 CLOSE cur_get_num_applinst;
1426
1427 IF (rec_get_num_applinst.num_of_records = 1) THEN -- means, One application, One instance. So get org from Admissions.
1428
1429 -- get org unit from Admissions.
1430 OPEN c_get_org_adm(l_person_id);
1431 FETCH c_get_org_adm INTO c_get_org_adm_rec;
1432 CLOSE c_get_org_adm;
1433
1434 x_module := 'AD';
1435 x_org_unit_cd := c_get_org_adm_rec.responsible_org_unit_cd;
1436 x_org_party_id := c_get_org_adm_rec.party_id;
1437 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1438 g_debug_string := g_debug_string || 'Org Unit Code(from no_of_applications) = ' || c_get_org_adm_rec.responsible_org_unit_cd || ' :: ';
1439 END IF;
1440 RETURN;
1441 ELSE -- means more than one instance. Applications may be one or more. So get org from FA Base.
1442
1443 -- get org unit from FA Base Record.
1444 OPEN c_get_assoc_org;
1445 FETCH c_get_assoc_org INTO c_get_assoc_org_rec;
1446 CLOSE c_get_assoc_org;
1447
1448 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1449 g_debug_string := g_debug_string || 'Org Unit Code(from FA Base Record form) = ' || c_get_assoc_org_rec.party_number || ' :: ';
1450 END IF;
1451
1452 IF (c_get_assoc_org_rec.party_number IS NOT NULL) THEN
1453 x_module := 'FA';
1454 x_org_unit_cd := c_get_assoc_org_rec.party_number;
1455 x_org_party_id := c_get_assoc_org_rec.assoc_org_num;
1456 ELSE -- means more than one instances and FA Base record says nothing about associated org.
1457 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1458 g_debug_string := g_debug_string || 'Org Unit Code is not derived';
1459 END IF;
1460 x_return_status := 'E';
1461 x_msg_data := 'IGF_AP_NO_PERSON_ORG';
1462 END IF;
1463 END IF;
1464 ELSE -- No applications for him.
1465 x_return_status := 'E';
1466 x_msg_data := 'IGF_AP_NO_PERSON_ORG';
1467 END IF;
1468 END IF;
1469
1470 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1471 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_gen.get_associated_org.debug', g_debug_string);
1472 END IF;
1473
1474 EXCEPTION
1475
1476 WHEN OTHERS THEN
1477
1478 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1479 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_gen.get_associated_org.debug', SQLERRM);
1480 END IF;
1481
1482 x_return_status := 'E';
1483 x_msg_data := 'IGF_AP_NO_PERSON_ORG';
1484 fnd_message.set_name('IGF', 'IGF_GE_UNHANDLED_EXP');
1485 fnd_message.set_token('NAME', 'IGF_GR_GEN.GET_ASSOCIATED_ORG'||' '||SQLERRM);
1486 igs_ge_msg_stack.add;
1487 app_exception.raise_exception;
1488 END get_associated_org;
1489
1490
1491 /* The following procedure obtains school code configured at Org Unit of the student's Key Program.
1492 Possible values of p_office_type are: OPE_ID_NUM, FED_SCH_CD, DL_SCH_CD, ETI_DES_NUM,
1493 CAM_SER_NUM, CEEB_CD PELL_ID SCH_NON_ED_BRC_ID.
1494 This procedure does not through any exceptions but returns success status 'S' for SUCCESS
1495 or 'E' for ERROR. The calling procedure should error out based on x_return_status. Also
1496 x_msg_data contains the proper error message if x_return_status is 'E'
1497 */
1498 PROCEDURE get_stu_fao_code (p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
1499 p_office_type IN igs_lookups_view.lookup_code%TYPE,
1500 x_office_cd OUT NOCOPY igs_or_org_alt_ids.org_alternate_id_type%TYPE,
1501 x_return_status OUT NOCOPY VARCHAR2,
1502 x_msg_data OUT NOCOPY VARCHAR2)
1503 AS
1504 /*
1505 || Created By : ugummall
1506 || Created On : 14-OCT-2003
1507 || Purpose : Bug # 3102439. FA 126 Multiple FA offices.
1508 || For obtaining school code configured at org unit
1509 || of the student's key program.
1510 || Known limitations, enhancements or remarks :
1511 || Change History :
1512 || Who When What
1513 || (reverse chronological order - newest change first)
1514 */
1515
1516 CURSOR c_get_fa_office( p_org_unit_cd IN hz_parties.party_number%TYPE,
1517 p_office_type IN igs_lookups_view.lookup_code%TYPE) IS
1518 SELECT org.org_alternate_id office_cd
1519 FROM igs_or_org_alt_ids org,
1520 igs_or_org_alt_idtyp idt
1521 WHERE org.org_structure_id = p_org_unit_cd
1522 AND org.org_alternate_id_type = idt.org_alternate_id_type
1523 AND SYSDATE BETWEEN org.start_date AND NVL(org.end_date, SYSDATE)
1524 AND idt.system_id_type = p_office_type;
1525 c_get_fa_office_rec c_get_fa_office%ROWTYPE;
1526
1527 l_org_unit_cd hz_parties.party_number%TYPE;
1528 l_module VARCHAR2(2);
1529 l_ret_status VARCHAR2(1);
1530 l_msg_data VARCHAR2(30);
1531 l_org_party_id hz_parties.party_id%TYPE;
1532
1533 BEGIN
1534
1535 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1536 g_debug_string := 'Base ID = ' || p_base_id || ' :: Office Type = ' || p_office_type;
1537 END IF;
1538
1539 IF (p_base_id IS NOT NULL AND p_office_type IS NOT NULL) THEN
1540
1541 get_associated_org(p_base_id, l_org_unit_cd, l_org_party_id, l_module, l_ret_status, l_msg_data);
1542
1543 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1544 g_debug_string := g_debug_string || 'associated org = ' || l_org_unit_cd || ' l_module = '||l_module ||':: ';
1545 END IF;
1546
1547 IF (l_ret_status = 'E' AND l_msg_data IS NOT NULL) THEN
1548 x_return_status := l_ret_status;
1549 x_msg_data := l_msg_data;
1550 ELSE
1551 OPEN c_get_fa_office(l_org_unit_cd, p_office_type);
1552 FETCH c_get_fa_office INTO c_get_fa_office_rec;
1553 CLOSE c_get_fa_office;
1554
1555 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1556 g_debug_string := g_debug_string || ' Office ID = ' || c_get_fa_office_rec.office_cd;
1557 END IF;
1558
1559 IF (c_get_fa_office_rec.office_cd IS NOT NULL) THEN
1560 x_office_cd := c_get_fa_office_rec.office_cd;
1561 x_return_status := 'S';
1562 RETURN;
1563 ELSE
1564 x_return_status := 'E';
1565 IF (p_office_type = 'FED_SCH_CD') THEN
1566 x_msg_data := 'IGF_AP_STU_FED_SCH_CD_NFND';
1567 ELSIF (p_office_type = 'SCH_NON_ED_BRC_ID') THEN
1568 x_msg_data := 'IGF_AP_SCH_NONED_NOTFND';
1569 ELSIF (p_office_type = 'OPE_ID_NUM') THEN
1570 x_msg_data := 'IGF_SL_STU_OPE_NOTFND';
1571 ELSIF (p_office_type = 'DL_SCH_CD') THEN
1572 x_msg_data := 'IGF_SL_DL_STU_DLCD_NOTFND';
1573 ELSIF (p_office_type = 'PELL_ID') THEN
1574 x_msg_data := 'IGF_GR_NO_ATTEND_PELL';
1575 ELSIF (p_office_type = 'ETI_DES_NUM') THEN
1576 x_msg_data := 'IGF_AP_NO_ETI_DES_NUM';
1577 ELSIF (p_office_type = 'ENTITY_ID') THEN
1578 x_msg_data := 'IGF_GR_NO_ATTEND_ENTITY';
1579 ELSE
1580 x_msg_data := NULL;
1581 END IF;
1582 END IF;
1583 END IF;
1584 ELSE
1585 x_return_status := 'E';
1586 END IF;
1587
1588 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1589 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_gen.get_stu_fao_code.debug', g_debug_string);
1590 END IF;
1591
1592 EXCEPTION
1593 WHEN OTHERS THEN
1594 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1595 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_gen.get_stu_fao_code.debug', SQLERRM);
1596 END IF;
1597
1598 x_return_status := 'E';
1599 END get_stu_fao_code;
1600
1601 FUNCTION get_fed_fund_code (p_n_award_id IN igf_aw_award_all.award_id%TYPE,
1602 p_v_message_name OUT NOCOPY VARCHAR2)
1603 RETURN igf_aw_fund_cat_all.fed_fund_code%TYPE AS
1604 ------------------------------------------------------------------
1605 --Created by : Sanil Madathil, Oracle IDC
1606 --Date created: 13 October 2004
1607 --
1608 -- Purpose : Generic Function to return fed fund code for the input award id
1609 -- Invoked :
1610 -- Function :
1611 --
1612 -- Parameters : p_n_award_id : IN parameter. Required.
1613 -- p_v_message_name : OUT parameter
1614 --
1615 --
1616 --Known limitations/enhancements and/or remarks:
1617 --
1618 --Change History:
1619 --Who When What
1620 ------------------------------------------------------------------
1621 CURSOR c_igf_aw_award (cp_n_award_id igf_aw_award_all.award_id%TYPE) IS
1622 SELECT fund_id
1623 FROM igf_aw_award_all
1624 WHERE award_id = cp_n_award_id;
1625
1626 CURSOR c_igf_fmast_fcat (cp_n_fund_id igf_aw_fund_mast_all.fund_id%TYPE) IS
1627 SELECT fcat.fed_fund_code
1628 FROM igf_aw_fund_mast_all fmast
1629 ,igf_aw_fund_cat_all fcat
1630 WHERE fmast.fund_code = fcat.fund_code
1631 AND fmast.fund_id = cp_n_fund_id;
1632
1633 l_n_fund_id igf_aw_fund_mast_all.fund_id%TYPE;
1634 l_v_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE;
1635
1636 BEGIN
1637
1638 IF p_n_award_id IS NULL THEN
1639 p_v_message_name:= 'IGS_GE_INVALID_VALUE';
1640 RETURN NULL;
1641 END IF;
1642
1643 -- get the fund id corresponding to the input award id
1644 OPEN c_igf_aw_award (cp_n_award_id => p_n_award_id);
1645 FETCH c_igf_aw_award INTO l_n_fund_id;
1646 CLOSE c_igf_aw_award ;
1647
1648 OPEN c_igf_fmast_fcat (cp_n_fund_id => l_n_fund_id);
1649 FETCH c_igf_fmast_fcat INTO l_v_fed_fund_code;
1650 CLOSE c_igf_fmast_fcat;
1651
1652 p_v_message_name := NULL;
1653 RETURN l_v_fed_fund_code;
1654 EXCEPTION
1655 WHEN OTHERS THEN
1656 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1657 THEN
1658 fnd_log.string( fnd_log.level_exception, 'igf_sl_gen.get_fed_fund_code exception', SQLERRM);
1659 END IF;
1660 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1661 fnd_message.set_token('NAME','igf_sl_gen.get_fed_fund_code');
1662 igs_ge_msg_stack.add;
1663 app_exception.raise_exception;
1664
1665 END get_fed_fund_code;
1666
1667 FUNCTION check_prc_chg (p_v_relationship_cd IN igf_sl_cl_setup_all.relationship_cd%TYPE,
1668 p_v_cal_type IN igf_aw_fund_mast_all.ci_cal_type%TYPE ,
1669 p_n_sequence_number IN igf_aw_fund_mast_all.ci_sequence_number%TYPE
1670 )
1671 RETURN BOOLEAN AS
1672 ------------------------------------------------------------------
1673 --Created by : Sanil Madathil, Oracle IDC
1674 --Date created: 13 October 2004
1675 --
1676 -- Purpose : Generic Function
1677 -- Invoked :
1678 -- Function :
1679 --
1680 -- Parameters : p_v_relationship_cd : IN parameter. Required.
1681 -- p_v_cal_type : IN parameter. Required.
1682 -- p_n_sequence_number : IN parameter. Required.
1683 --
1684 --Known limitations/enhancements and/or remarks:
1685 --
1686 --Change History:
1687 --Who When What
1688 ------------------------------------------------------------------
1689 CURSOR c_loan_num ( cp_v_relationship_cd igf_sl_cl_setup_all.relationship_cd%TYPE,
1690 cp_v_cal_type igf_aw_fund_mast_all.ci_cal_type%TYPE,
1691 cp_n_sequence_number igf_aw_fund_mast_all.ci_sequence_number%TYPE
1692 ) IS
1693 SELECT loans.loan_number
1694 FROM igf_sl_lor_all lor
1695 ,igf_sl_loans_all loans
1696 ,igf_aw_award_all awd
1697 ,igf_aw_fund_mast_all fmast
1698 WHERE lor.relationship_cd = cp_v_relationship_cd
1699 AND loans.loan_id = lor.loan_id
1700 AND (loans.loan_status = 'S' OR loans.loan_chg_status = 'S')
1701 AND awd.award_id = loans.award_id
1702 AND fmast.fund_id = awd.fund_id
1703 AND fmast.ci_cal_type = cp_v_cal_type
1704 AND fmast.ci_sequence_number = cp_n_sequence_number;
1705
1706 l_v_loan_number igf_sl_loans_all.loan_number%TYPE;
1707 BEGIN
1708
1709 OPEN c_loan_num ( cp_v_relationship_cd => p_v_relationship_cd,
1710 cp_v_cal_type => p_v_cal_type,
1711 cp_n_sequence_number => p_n_sequence_number
1712 );
1713 FETCH c_loan_num INTO l_v_loan_number;
1714 -- if there are no FFELP Loan Records that have Loan Status or
1715 -- Loan Change Status in 'Sent' Status that use Relationship Code
1716 IF c_loan_num%NOTFOUND THEN
1717 CLOSE c_loan_num;
1718 RETURN TRUE;
1719 END IF;
1720 CLOSE c_loan_num;
1721 RETURN FALSE;
1722 EXCEPTION
1723 WHEN OTHERS THEN
1724 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1725 THEN
1726 fnd_log.string( fnd_log.level_exception, 'igf_sl_gen.check_prc_chg exception', SQLERRM);
1727 END IF;
1728 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1729 fnd_message.set_token('NAME','igf_sl_gen.check_prc_chg');
1730 igs_ge_msg_stack.add;
1731 app_exception.raise_exception;
1732 END check_prc_chg;
1733
1734 FUNCTION check_prc_chgm (p_v_relationship_cd IN igf_sl_cl_setup_all.relationship_cd%TYPE,
1735 p_v_cal_type IN igf_aw_fund_mast_all.ci_cal_type%TYPE ,
1736 p_n_sequence_number IN igf_aw_fund_mast_all.ci_sequence_number%TYPE
1737 )
1738 RETURN BOOLEAN AS
1739 ------------------------------------------------------------------
1740 --Created by : Sanil Madathil, Oracle IDC
1741 --Date created: 13 October 2004
1742 --
1743 -- Purpose : Generic Function
1744 -- Invoked :
1745 -- Function :
1746 --
1747 -- Parameters : p_v_relationship_cd : IN parameter. Required.
1748 -- p_v_cal_type : IN parameter. Required.
1749 -- p_n_sequence_number : IN parameter. Required.
1750 --
1751 --Known limitations/enhancements and/or remarks:
1752 --
1753 --Change History:
1754 --Who When What
1755 ------------------------------------------------------------------
1756 CURSOR c_loan_num ( cp_v_relationship_cd igf_sl_cl_setup_all.relationship_cd%TYPE,
1757 cp_v_cal_type igf_aw_fund_mast_all.ci_cal_type%TYPE,
1758 cp_n_sequence_number igf_aw_fund_mast_all.ci_sequence_number%TYPE
1759 ) IS
1760 SELECT loans.loan_number
1761 FROM igf_sl_lor_all lor
1762 ,igf_sl_loans_all loans
1763 ,igf_aw_award_all awd
1764 ,igf_aw_fund_mast_all fmast
1765 WHERE lor.relationship_cd = cp_v_relationship_cd
1766 AND loans.loan_id = lor.loan_id
1767 AND awd.award_id = loans.award_id
1768 AND fmast.fund_id = awd.fund_id
1769 AND fmast.ci_cal_type = cp_v_cal_type
1770 AND fmast.ci_sequence_number = cp_n_sequence_number;
1771
1772 l_v_loan_number igf_sl_loans_all.loan_number%TYPE;
1773
1774 BEGIN
1775 OPEN c_loan_num ( cp_v_relationship_cd => p_v_relationship_cd,
1776 cp_v_cal_type => p_v_cal_type,
1777 cp_n_sequence_number => p_n_sequence_number
1778 );
1779 FETCH c_loan_num INTO l_v_loan_number;
1780 -- if there are no FFELP Loan Records that use Relationship Code
1781 IF c_loan_num%NOTFOUND THEN
1782 CLOSE c_loan_num;
1783 RETURN TRUE;
1784 END IF;
1785 CLOSE c_loan_num;
1786 RETURN FALSE;
1787 EXCEPTION
1788 WHEN OTHERS THEN
1789 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1790 THEN
1791 fnd_log.string( fnd_log.level_exception, 'igf_sl_gen.check_prc_chgm exception', SQLERRM);
1792 END IF;
1793 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1794 fnd_message.set_token('NAME','igf_sl_gen.check_prc_chgm');
1795 igs_ge_msg_stack.add;
1796 app_exception.raise_exception;
1797 END check_prc_chgm;
1798
1799 PROCEDURE update_cl_chg_status(p_v_loan_number IN igf_sl_loans_all.loan_number%TYPE) IS
1800 ------------------------------------------------------------------
1801 --Created by : svuppala, Oracle IDC
1802 --Date created: 20-Oct-2004
1803 --
1804 -- Purpose : Update Loan Change Status
1805 -- Invoked :
1806 --
1807 -- Parameters : p_loan_id : IN parameter. Required.
1808 --
1809 --
1810 --Known limitations/enhancements and/or remarks:
1811 --
1812 --Change History:
1813 --Who When What
1814 ------------------------------------------------------------------
1815
1816 --l_loan_chg_status igf_sl_loans_all.loan_chg_status%TYPE;
1817 -- ROWID row_id
1818
1819 CURSOR c_sl_loans (cp_loan_number_txt igf_sl_loans_all.loan_number%TYPE) IS
1820 SELECT igfsla.* ,igfsla.ROWID ROW_ID
1821 FROM igf_sl_loans_all igfsla
1822 WHERE loan_number = cp_loan_number_txt;
1823
1824 rec_c_sl_loans c_sl_loans%ROWTYPE;
1825
1826 CURSOR c_clchsn (cp_loan_number_txt igf_sl_loans_all.loan_number%TYPE) IS
1827 SELECT 'x'
1828 FROM igf_sl_clchsn_dtls
1829 WHERE loan_number_txt = cp_loan_number_txt
1830 AND status_code = 'S';
1831
1832 rec_c_clchsn c_clchsn%ROWTYPE;
1833
1834 CURSOR c_clchsn2 (cp_loan_number_txt igf_sl_loans_all.loan_number%TYPE) IS
1835 SELECT 'x'
1836 FROM igf_sl_clchsn_dtls
1837 WHERE loan_number_txt = cp_loan_number_txt
1838 AND status_code = 'A'
1839 AND response_status_code = 'R';
1840
1841 rec_c_clchsn2 c_clchsn2%ROWTYPE;
1842
1843
1844 CURSOR c_clchsn3 (cp_loan_number_txt igf_sl_loans_all.loan_number%TYPE) IS
1845 SELECT 'x'
1846 FROM igf_sl_clchsn_dtls
1847 WHERE loan_number_txt = cp_loan_number_txt
1848 AND status_code = 'A'
1849 AND (response_status_code = 'F' OR response_status_code = 'P') ;
1850
1851 rec_c_clchsn3 c_clchsn2%ROWTYPE;
1852
1853 CURSOR c_get_clchsn_accept (cp_loan_number_txt igf_sl_loans_all.loan_number%TYPE) IS
1854 SELECT 'x'
1855 FROM igf_sl_clchsn_dtls
1856 WHERE loan_number_txt = cp_loan_number_txt
1857 AND status_code = 'A'
1858 AND response_status_code = 'A';
1859
1860 rec_c_get_clchsn_accept c_get_clchsn_accept%ROWTYPE;
1861
1862 CURSOR c_clchsn4 (cp_loan_number_txt igf_sl_loans_all.loan_number%TYPE) IS
1863 SELECT 'x'
1864 FROM igf_sl_clchsn_dtls
1865 WHERE loan_number_txt = cp_loan_number_txt
1866 AND status_code = 'R';
1867
1868 rec_c_clchsn4 c_clchsn4%ROWTYPE;
1869 lv_chg_status VARCHAR2(30);
1870
1871 BEGIN
1872 lv_chg_status := '*';
1873
1874 IF lv_chg_status = '*' THEN
1875 OPEN c_clchsn(p_v_loan_number);
1876 FETCH c_clchsn INTO rec_c_clchsn;
1877 IF(c_clchsn%FOUND) THEN
1878 -- if any of the change record in sent status update the loan change status to sent
1879 lv_chg_status := 'S';
1880 END IF;
1881 CLOSE c_clchsn;
1882 END IF;
1883 -- If any of the Change Record is in "Acknowledged" status and there are Reject Codes present
1884 -- for the transaction, then Loan Change Status would be updated to "Rejected"
1885 IF lv_chg_status = '*' THEN
1886 OPEN c_clchsn2(p_v_loan_number);
1887 FETCH c_clchsn2 INTO rec_c_clchsn2;
1888 IF(c_clchsn2%FOUND) THEN
1889 lv_chg_status := 'R';
1890 END IF;
1891 CLOSE c_clchsn2;
1892 END IF;
1893 -- If there are no "Acknowledged" Rejected Records and no "Sent" Records,
1894 -- the Loan Status would be updated to Sent" if any of the "Acknowledged" record
1895 -- is Forwarded or Pending
1896 IF lv_chg_status = '*' THEN
1897 OPEN c_clchsn3(p_v_loan_number);
1898 FETCH c_clchsn3 INTO rec_c_clchsn3;
1899 IF(c_clchsn3%FOUND) THEN
1900 lv_chg_status := 'S';
1901 END IF;
1902 CLOSE c_clchsn3;
1903 END IF;
1904 -- loan change status would be updated to "Accepted"
1905 -- if all of the "Acknowledged" records are Accepted
1906 IF lv_chg_status = '*' THEN
1907 OPEN c_get_clchsn_accept (cp_loan_number_txt => p_v_loan_number);
1908 FETCH c_get_clchsn_accept INTO rec_c_get_clchsn_accept ;
1909 IF c_get_clchsn_accept%FOUND THEN
1910 lv_chg_status := 'A';
1911 END IF;
1912 CLOSE c_get_clchsn_accept ;
1913 END IF;
1914 -- If there are no "Acknowledged" Records, "Sent" Records and any one Change Record
1915 -- is in "Ready to Send" then Loan Change Status would be "Ready to Send" else
1916 -- it would be "Not Ready".
1917 IF lv_chg_status = '*' THEN
1918 OPEN c_clchsn4(cp_loan_number_txt => p_v_loan_number);
1919 FETCH c_clchsn4 INTO rec_c_clchsn4;
1920 IF(c_clchsn4%FOUND) THEN
1921 -- if change record is ready to send then loan change status
1922 -- should be G - Ready to Send
1923 lv_chg_status := 'G';
1924 ELSE
1925 lv_chg_status := 'N';
1926 END IF;
1927 CLOSE c_clchsn4;
1928 END IF;
1929 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1930 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_gen.update_cl_chg_status.debug','Loan Change Status = ' || lv_chg_status);
1931 END IF;
1932 IF lv_chg_status <> '*' THEN
1933 OPEN c_sl_loans(p_v_loan_number);
1934 FETCH c_sl_loans INTO rec_c_sl_loans;
1935 CLOSE c_sl_loans;
1936
1937 igf_sl_loans_pkg.update_row(
1938 x_rowid => rec_c_sl_loans.row_id,
1939 x_loan_id => rec_c_sl_loans.loan_id,
1940 x_award_id => rec_c_sl_loans.award_id,
1941 x_seq_num => rec_c_sl_loans.seq_num,
1942 x_loan_number => rec_c_sl_loans.loan_number,
1943 x_loan_per_begin_date => rec_c_sl_loans.loan_per_begin_date,
1944 x_loan_per_end_date => rec_c_sl_loans.loan_per_end_date,
1945 x_loan_status => rec_c_sl_loans.loan_status,
1946 x_loan_status_date => rec_c_sl_loans.loan_status_date,
1947 x_loan_chg_status => lv_chg_status,
1948 x_loan_chg_status_date => rec_c_sl_loans.loan_chg_status_date,
1949 x_active => rec_c_sl_loans.active,
1950 x_active_date => rec_c_sl_loans.active_date,
1951 x_borw_detrm_code => rec_c_sl_loans.borw_detrm_code,
1952 x_legacy_record_flag => rec_c_sl_loans.legacy_record_flag,
1953 x_external_loan_id_txt => rec_c_sl_loans.external_loan_id_txt
1954 );
1955 END IF;
1956
1957 END update_cl_chg_status;
1958
1959 PROCEDURE get_stu_ant_fao_code
1960 (p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
1961 p_office_type IN igs_lookups_view.lookup_code%TYPE,
1962 x_office_cd OUT NOCOPY igs_or_org_alt_ids.org_alternate_id_type%TYPE,
1963 x_return_status OUT NOCOPY VARCHAR2,
1964 x_msg_data OUT NOCOPY VARCHAR2)
1965 AS
1966 /*
1967 || Created By : cdcruz
1968 || Created On : 11-NOV-2004
1969 || Purpose : Bug# 3102439. FA 152 Auto Re-pkg
1970 || For obtaining responsible org unit code from Anticipated data that
1971 || is associated with the student if Actual data is not available.
1972 || Known limitations, enhancements or remarks :
1973 || Change History :
1974 || Who When What
1975 || museshad 15-Jul-2005 Build FA 140.
1976 || Modified the logic for getting the anticipated
1977 || Org Unit. Anticipated Org Unit is derived by -
1978 || 1) Get anticipated Org Unit from anticipated
1979 || table (igf_ap_fa_ant_data) directly
1980 || 2) If step 1 does not give a valid Org Unit,
1981 || then get the anticipated key program and
1982 || get the Org Unit corresponding to this
1983 || key program.
1984 || (reverse chronological order - newest change first)
1985 */
1986
1987 -- This cursor gets the anticipated data for all the terms
1988 -- We then loop thru all the terms to find out if anticipated
1989 -- data is available for the current term
1990 CURSOR c_ant_data_curr_term(cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
1991 IS
1992 SELECT fant.*
1993 FROM igf_ap_fa_ant_data fant
1994 WHERE fant.base_id = cp_base_id;
1995
1996 lv_ant_data_curr_term_rec c_ant_data_curr_term%ROWTYPE;
1997
1998 -- Scans all the terms (starting from the earliest) in the student's
1999 -- award year for a valid anticipated Org Unit.
2000 -- This would be used if the current term does not have anticipated data.
2001 CURSOR c_ant_data_all_terms(cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
2002 IS
2003 SELECT ant_data.*
2004 FROM
2005 igf_aw_awd_ld_cal_v awd_year_terms,
2006 igf_ap_fa_ant_data ant_data
2007 WHERE
2008 ant_data.ld_cal_type = awd_year_terms.ld_cal_type AND
2009 ant_data.ld_sequence_number = awd_year_terms.ld_sequence_number AND
2010 ant_data.base_id = cp_base_id AND
2011 ant_data.org_unit_cd IS NOT NULL
2012 ORDER BY
2013 igf_aw_packaging.get_term_start_date(cp_base_id, awd_year_terms.ld_cal_type, awd_year_terms.ld_sequence_number) ASC;
2014
2015 lv_ant_data_all_terms_rec c_ant_data_all_terms%ROWTYPE;
2016
2017 -- Scans all the terms (starting from the earliest) in the student's
2018 -- award year for a valid anticipated key prgram and version. Returns the
2019 -- Org Unit corresponding to the anticipated key prog and version
2020 CURSOR c_get_ant_org_unit(cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
2021 IS
2022 SELECT prog.*
2023 FROM
2024 igf_aw_awd_ld_cal_v awd_year_terms,
2025 igf_ap_fa_ant_data ant_data,
2026 igs_ps_ver_all prog
2027 WHERE
2028 ant_data.ld_cal_type = awd_year_terms.ld_cal_type AND
2029 ant_data.ld_sequence_number = awd_year_terms.ld_sequence_number AND
2030 ant_data.base_id = cp_base_id and
2031 ant_data.program_cd = prog.course_cd AND
2032 prog.course_status = 'ACTIVE' AND
2033 ant_data.program_cd IS NOT NULL AND
2034 prog.responsible_org_unit_cd IS NOT NULL
2035 ORDER BY
2036 igf_aw_packaging.get_term_start_date(cp_base_id, awd_year_terms.ld_cal_type, awd_year_terms.ld_sequence_number) ASC,
2037 prog.version_number DESC;
2038
2039 l_get_ant_org_unit_rec c_get_ant_org_unit%ROWTYPE;
2040
2041 CURSOR c_get_fa_office( p_org_unit_cd IN hz_parties.party_number%TYPE,
2042 p_office_type IN igs_lookups_view.lookup_code%TYPE) IS
2043 SELECT org.org_alternate_id office_cd
2044 FROM igs_or_org_alt_ids org,
2045 igs_or_org_alt_idtyp idt
2046 WHERE org.org_structure_id = p_org_unit_cd
2047 AND org.org_alternate_id_type = idt.org_alternate_id_type
2048 AND SYSDATE BETWEEN org.start_date AND NVL(org.end_date, SYSDATE)
2049 AND idt.system_id_type = p_office_type;
2050
2051 c_get_fa_office_rec c_get_fa_office%ROWTYPE;
2052
2053 lv_start_dt DATE;
2054 lv_end_dt DATE;
2055 l_person_id NUMBER;
2056 lv_ant_org_unit_cd igf_ap_fa_ant_data.org_unit_cd%TYPE := NULL;
2057
2058 BEGIN
2059 -- initialize
2060 x_return_status := 'S';
2061 x_msg_data := NULL;
2062 x_office_cd := NULL;
2063
2064 -- obtain the person_id from the base_id value
2065 OPEN c_ant_data_curr_term(p_base_id);
2066 LOOP
2067 FETCH c_ant_data_curr_term INTO lv_ant_data_curr_term_rec;
2068 EXIT when c_ant_data_curr_term%NOTFOUND;
2069
2070 lv_start_dt := igs_ca_compute_da_val_pkg.cal_da_elt_val(
2071 'FIRST_DAY_TERM',
2072 lv_ant_data_curr_term_rec.ld_cal_type,
2073 lv_ant_data_curr_term_rec.ld_sequence_number,
2074 lv_ant_data_curr_term_rec.org_unit_cd,
2075 lv_ant_data_curr_term_rec.program_type,
2076 lv_ant_data_curr_term_rec.program_cd
2077 );
2078
2079 lv_end_dt := igs_ca_compute_da_val_pkg.cal_da_elt_val(
2080 'LAST_DAY_TERM',
2081 lv_ant_data_curr_term_rec.ld_cal_type,
2082 lv_ant_data_curr_term_rec.ld_sequence_number,
2083 lv_ant_data_curr_term_rec.org_unit_cd,
2084 lv_ant_data_curr_term_rec.program_type,
2085 lv_ant_data_curr_term_rec.program_cd
2086 );
2087
2088 IF sysdate BETWEEN lv_start_dt AND lv_end_dt AND (lv_ant_data_curr_term_rec.org_unit_cd IS NOT NULL) THEN
2089 -- Current term has anticipated data
2090 lv_ant_org_unit_cd := lv_ant_data_curr_term_rec.org_unit_cd;
2091
2092 -- Log values
2093 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2094 fnd_log.string(fnd_log.level_statement,
2095 'igf.plsql.igf_sl_gen.get_stu_ant_fao_code',
2096 'Found anticipated Org Unit for the current term. Org Unit: ' ||lv_ant_org_unit_cd||
2097 ', ld_cal_type: ' ||lv_ant_data_curr_term_rec.ld_cal_type||
2098 ', ld_sequence_number: ' ||lv_ant_data_curr_term_rec.ld_sequence_number);
2099 END IF;
2100 END IF;
2101
2102 END LOOP;
2103 CLOSE c_ant_data_curr_term;
2104
2105 IF lv_ant_org_unit_cd IS NULL THEN
2106 -- Current term does not have anticipated data.
2107 -- Search for anticipated data
2108 OPEN c_ant_data_all_terms(p_base_id);
2109 FETCH c_ant_data_all_terms INTO lv_ant_data_all_terms_rec;
2110 CLOSE c_ant_data_all_terms;
2111
2112 IF (lv_ant_data_all_terms_rec.org_unit_cd IS NOT NULL) THEN
2113 lv_ant_org_unit_cd := lv_ant_data_all_terms_rec.org_unit_cd;
2114
2115 -- Log values
2116 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2117 fnd_log.string(fnd_log.level_statement,
2118 'igf.plsql.igf_sl_gen.get_stu_ant_fao_code',
2119 'Found anticipated Org Unit in one of the terms in the award year. Org Unit: ' ||lv_ant_org_unit_cd||
2120 ', ld_cal_type: ' ||lv_ant_data_all_terms_rec.ld_cal_type||
2121 ', ld_sequence_number: ' ||lv_ant_data_all_terms_rec.ld_sequence_number);
2122 END IF;
2123 ELSE
2124 -- Anticipated Org Unit is not available.
2125 -- Try to get the Org Unit from the anticipated key prog
2126 OPEN c_get_ant_org_unit(p_base_id);
2127 FETCH c_get_ant_org_unit INTO l_get_ant_org_unit_rec;
2128
2129 IF (c_get_ant_org_unit%FOUND) THEN
2130 lv_ant_org_unit_cd := l_get_ant_org_unit_rec.responsible_org_unit_cd;
2131
2132 -- Log values
2133 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2134 fnd_log.string(fnd_log.level_statement,
2135 'igf.plsql.igf_sl_gen.get_stu_ant_fao_code',
2136 'Found Org Unit for the anticipated key program. Org Unit: ' ||lv_ant_org_unit_cd||
2137 ', Key prog: ' ||l_get_ant_org_unit_rec.course_cd||
2138 ', Version: ' ||l_get_ant_org_unit_rec.version_number);
2139 END IF;
2140 ELSE
2141 x_return_status := 'E';
2142 END IF;
2143
2144 CLOSE c_get_ant_org_unit;
2145 END IF;
2146 END IF;
2147
2148 IF x_return_status <> 'E' AND lv_ant_org_unit_cd IS NOT NULL THEN
2149 OPEN c_get_fa_office(lv_ant_org_unit_cd, p_office_type);
2150 FETCH c_get_fa_office INTO c_get_fa_office_rec;
2151 CLOSE c_get_fa_office;
2152 END IF;
2153
2154 IF (c_get_fa_office_rec.office_cd IS NOT NULL) THEN
2155 x_office_cd := c_get_fa_office_rec.office_cd;
2156 x_return_status := 'S';
2157 RETURN;
2158 ELSE
2159 x_return_status := 'E';
2160 IF (p_office_type = 'FED_SCH_CD') THEN
2161 x_msg_data := 'IGF_AP_STU_FED_SCH_CD_NFND';
2162 ELSIF (p_office_type = 'SCH_NON_ED_BRC_ID') THEN
2163 x_msg_data := 'IGF_AP_SCH_NONED_NOTFND';
2164 ELSIF (p_office_type = 'OPE_ID_NUM') THEN
2165 x_msg_data := 'IGF_SL_STU_OPE_NOTFND';
2166 ELSIF (p_office_type = 'DL_SCH_CD') THEN
2167 x_msg_data := 'IGF_SL_DL_STU_DLCD_NOTFND';
2168 ELSIF (p_office_type = 'PELL_ID') THEN
2169 x_msg_data := 'IGF_GR_NO_ATTEND_PELL';
2170 ELSIF (p_office_type = 'ETI_DES_NUM') THEN
2171 x_msg_data := 'IGF_AP_NO_ETI_DES_NUM';
2172 ELSIF (p_office_type = 'ENTITY_ID') THEN
2173 x_msg_data := 'IGF_GR_NO_ATTEND_ENTITY';
2174 ELSE
2175 x_msg_data := NULL;
2176 END IF;
2177
2178 -- Log values
2179 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2180 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_gen.get_stu_ant_fao_code', 'Anticipated Org Unit not defined');
2181 END IF;
2182 END IF;
2183
2184 END get_stu_ant_fao_code;
2185
2186 END igf_sl_gen;
[Home] [Help]