[Home] [Help]
359: END get_sectionii_stdnt;
360:
361:
362: FUNCTION get_sectionvi_fund ( p_rec_type IN igf_aw_fisap_vi_h.rec_type%TYPE,
363: p_fund_type IN igf_aw_award_v.fed_fund_code%TYPE,
364: p_depend_stat IN igf_lookups_view.lookup_code%TYPE,
365: p_class_standing IN igf_lookups_view.lookup_code%TYPE,
366: p_ci_cal_type IN igs_ca_inst.cal_type%TYPE,
367: p_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE,
389: -- return -3 if any Unhandled Exception is Raised.
390: /*
391: CURSOR c_non_proff_fund_count (
392: cp_rec_type igf_aw_fisap_vi_h.rec_type%TYPE,
393: cp_fund_type igf_aw_award_v.fed_fund_code%TYPE,
394: cp_depend_stat igf_lookups_view.lookup_code%TYPE,
395: cp_class_standing igf_lookups_view.lookup_code%TYPE,
396: cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
397: cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
399: cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE )
400: IS
401: SELECT NVL(SUM(awd.paid_amt),0)
402: FROM igf_ap_isir_matched isir,
403: igf_aw_award_v awd,
404: igf_ap_fa_base_rec fa
405: WHERE awd.base_id =isir.base_id
406: AND fa.base_id =isir.base_id
407: AND isir.dependency_status =p_depend_stat
421: AND cp_rec_type ='NON_PROFESSIONAL';
422:
423:
424: CURSOR c_proff_fund_count ( cp_rec_type igf_aw_fisap_vi_h.rec_type%TYPE,
425: cp_fund_type igf_aw_award_v.fed_fund_code%TYPE,
426: cp_depend_stat igf_lookups_view.lookup_code%TYPE,
427: cp_class_standing igf_lookups_view.lookup_code%TYPE,
428: cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
429: cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
431: cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE )
432: IS
433: SELECT NVL(SUM(awd.paid_amt),0)
434: FROM igf_ap_isir_matched isir,
435: igf_aw_award_v awd,
436: igf_ap_fa_base_rec fa
437: WHERE awd.base_id =isir.base_id
438: AND fa.base_id =isir.base_id
439: AND isir.dependency_status =p_depend_stat
453: AND cp_rec_type ='PROFESSIONAL';
454:
455: CURSOR c_less_ft_fund_count (
456: cp_rec_type igf_aw_fisap_vi_h.rec_type%TYPE,
457: cp_fund_type igf_aw_award_v.fed_fund_code%TYPE,
458: cp_depend_stat igf_lookups_view.lookup_code%TYPE,
459: cp_class_standing igf_lookups_view.lookup_code%TYPE,
460: cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
461: cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
463: cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE )
464: IS
465: SELECT NVL(SUM(awd.paid_amt),0)
466: FROM igf_ap_isir_matched isir,
467: igf_aw_award_v awd,
468: igf_ap_fa_base_rec fa
469: WHERE awd.base_id =isir.base_id
470: AND fa.base_id =isir.base_id
471: AND isir.dependency_status IN ('I','D')
485: AND cp_rec_type ='LESS_THAN_FULL_TIME';
486:
487: CURSOR c_auto_efc_fund_count (
488: cp_rec_type igf_aw_fisap_vi_h.rec_type%TYPE,
489: cp_fund_type igf_aw_award_v.fed_fund_code%TYPE,
490: cp_depend_stat igf_lookups_view.lookup_code%TYPE,
491: cp_class_standing igf_lookups_view.lookup_code%TYPE,
492: cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
493: cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
495: cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE )
496: IS
497: SELECT NVL(SUM(awd.paid_amt),0)
498: FROM igf_ap_isir_matched isir,
499: igf_aw_award_v awd,
500: igf_ap_fa_base_rec fa
501: WHERE awd.base_id =isir.base_id
502: AND fa.base_id =isir.base_id
503: AND isir.dependency_status IN ('I','D')
520: -- fund total is same data type for
521: -- all the 3 funds.
522:
523: l_rec_type igf_aw_fisap_vi_h.rec_type%TYPE;
524: l_fund_type igf_aw_award_v.fed_fund_code%TYPE;
525:
526: BEGIN
527: -- code commented as part of Fa132. function only returns 0 since this function is being used
528: -- in igf_aw_fisap_section_ii_v and igf_aw_fisap_section_vi_v
679: END get_sectionvi_fund;
680:
681:
682: FUNCTION get_sectionvi_stdnt (p_rec_type IN igf_aw_fisap_vi_h.rec_type%TYPE,
683: p_fund_type IN igf_aw_award_v.fed_fund_code%TYPE,
684: p_depend_stat IN igf_lookups_view.lookup_code%TYPE,
685: p_class_standing IN igf_lookups_view.lookup_code%TYPE,
686: p_ci_cal_type IN igs_ca_inst.cal_type%TYPE,
687: p_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE,
710: -- This cursor would check for the fund types "FWS, PRK, FSEOG"
711: -- and class Standing as 4 ( 4 combination of 1 and 2) and REC_TYPE as "NON_PROFESSIONAL"
712: /*
713: CURSOR c_non_proff_std_count (cp_rec_type igf_aw_fisap_vi_h.rec_type%TYPE,
714: cp_fund_type igf_aw_award_v.fed_fund_code%TYPE,
715: cp_depend_stat igf_lookups_view.lookup_code%TYPE,
716: cp_class_standing igf_lookups_view.lookup_code%TYPE,
717: cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
718: cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
720: cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE )
721: IS
722: SELECT COUNT (isir.base_id )
723: FROM igf_ap_isir_matched isir,
724: igf_aw_award_v awd,
725: igf_ap_fa_base_rec fa
726: WHERE isir.dependency_status =cp_depend_stat
727: AND fa.base_id =isir.base_id
728: AND awd.base_id = isir.base_id
747: -- p_rec_type ='PROFESSIONAL' and the fund_type 'FWS' or 'PRK'
748: -- there is no FSEOG fund for Profession cat.
749:
750: CURSOR c_proff_std_count (cp_rec_type igf_aw_fisap_vi_h.rec_type%TYPE,
751: cp_fund_type igf_aw_award_v.fed_fund_code%TYPE,
752: cp_depend_stat igf_lookups_view.lookup_code%TYPE,
753: cp_class_standing igf_lookups_view.lookup_code%TYPE,
754: cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
755: cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
757: cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE )
758: IS
759: SELECT COUNT (isir.base_id )
760: FROM igf_ap_isir_matched isir,
761: igf_aw_award_v awd,
762: igf_ap_fa_base_rec fa
763: WHERE isir.dependency_status =cp_depend_stat
764: AND awd.base_id= isir.base_id
765: AND fa.base_id = isir.base_id
782: --For total Less than full Time students
783: --The dependency status passed to this "B", valid fed_fund_code and not 'UNDUPL'
784: --and class standing passed would be GRAD-UGRAD
785: CURSOR c_less_ft_std_count (cp_rec_type igf_aw_fisap_vi_h.rec_type%TYPE,
786: cp_fund_type igf_aw_award_v.fed_fund_code%TYPE,
787: cp_depend_stat igf_lookups_view.lookup_code%TYPE,
788: cp_class_standing igf_lookups_view.lookup_code%TYPE,
789: cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
790: cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
792: cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE )
793: IS
794: SELECT COUNT (isir.base_id )
795: FROM igf_ap_isir_matched isir,
796: igf_aw_award_v awd,
797: igf_ap_fa_base_rec fa
798: WHERE isir.dependency_status IN ('I','D')
799: AND awd.base_id = isir.base_id
800: AND fa.base_id =isir.base_id
816: --The dependency status passed to this "B", valid
817: --FED_FUND_CODE and not 'UNDUPL' and class standing passed would be GRAD-UGRAD
818:
819: CURSOR c_auto_efc_std_count ( cp_rec_type igf_aw_fisap_vi_h.rec_type%TYPE,
820: cp_fund_type igf_aw_award_v.fed_fund_code%TYPE,
821: cp_depend_stat igf_lookups_view.lookup_code%TYPE,
822: cp_class_standing igf_lookups_view.lookup_code%TYPE,
823: cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
824: cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
826: cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE )
827: IS
828: SELECT COUNT ( isir.base_id )
829: FROM igf_ap_isir_matched isir,
830: igf_aw_award_v awd,
831: igf_ap_fa_base_rec fa
832: WHERE isir.dependency_status IN ('I','D')
833: AND awd.base_id = isir.base_id
834: AND fa.base_id =isir.base_id
849: -- This cursor would check for the fund types "FWS, PRK, FSEOG"
850: -- and class Standing as 4 and REC_TYPE as "NON_PROFESSIONAL" and Unduplicate student
851:
852: CURSOR c_non_proff_und_std_count (cp_rec_type igf_aw_fisap_vi_h.rec_type%TYPE,
853: cp_fund_type igf_aw_award_v.fed_fund_code%TYPE,
854: cp_depend_stat igf_lookups_view.lookup_code%TYPE,
855: cp_class_standing igf_lookups_view.lookup_code%TYPE,
856: cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
857: cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
859: cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE )
860: IS
861: SELECT COUNT ( DISTINCT (isir.base_id ) )
862: FROM igf_ap_isir_matched isir,
863: igf_aw_award_v awd,
864: igf_ap_fa_base_rec fa
865: WHERE isir.dependency_status = cp_depend_stat
866: AND awd.base_id = isir.base_id
867: AND fa.base_id =isir.base_id
886: -- p_rec_type ='PROFESSIONAL' and the fund_type 'FWS' or 'PRK' and Unduplicate
887: -- there is no FSEOG fund for Profession cat.
888:
889: CURSOR c_proff_und_std_count (cp_rec_type igf_aw_fisap_vi_h.rec_type%TYPE,
890: cp_fund_type igf_aw_award_v.fed_fund_code%TYPE,
891: cp_depend_stat igf_lookups_view.lookup_code%TYPE,
892: cp_class_standing igf_lookups_view.lookup_code%TYPE,
893: cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
894: cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
895: cp_minvalue igf_aw_fi_inc_level.minvalue%TYPE,
896: cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE ) IS
897: SELECT COUNT (DISTINCT (isir.base_id ) )
898: FROM igf_ap_isir_matched isir,
899: igf_aw_award_v awd,
900: igf_ap_fa_base_rec fa
901: WHERE isir.dependency_status =cp_depend_stat
902: AND awd.base_id = isir.base_id
903: AND fa.base_id =isir.base_id
921: --The dependency status passed to this "B", valid fed_fund_code and 'UNDUPL'
922: --and class standing passed would be GRAD-UGRAD
923:
924: CURSOR c_less_ft_und_std_count (cp_rec_type igf_aw_fisap_vi_h.rec_type%TYPE,
925: cp_fund_type igf_aw_award_v.fed_fund_code%TYPE,
926: cp_depend_stat igf_lookups_view.lookup_code%TYPE,
927: cp_class_standing igf_lookups_view.lookup_code%TYPE,
928: cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
929: cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
931: cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE )
932: IS
933: SELECT COUNT (DISTINCT(isir.base_id ))
934: FROM igf_ap_isir_matched isir,
935: igf_aw_award_v awd,
936: igf_ap_fa_base_rec fa
937: WHERE isir.dependency_status IN ('I','D')
938: AND awd.base_id = isir.base_id
939: AND fa.base_id = isir.base_id
956: --The dependency status passed to this "B", valid
957: --FED_FUND_CODE and 'UNDUPL' and class standing passed would be GRAD-UGRAD
958:
959: CURSOR c_auto_efc_und_std_count ( cp_rec_type igf_aw_fisap_vi_h.rec_type%TYPE,
960: cp_fund_type igf_aw_award_v.fed_fund_code%TYPE,
961: cp_depend_stat igf_lookups_view.lookup_code%TYPE,
962: cp_class_standing igf_lookups_view.lookup_code%TYPE,
963: cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
964: cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
966: cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE )
967: IS
968: SELECT COUNT (DISTINCT(isir.base_id ))
969: FROM igf_ap_isir_matched isir,
970: igf_aw_award_v awd,
971: igf_ap_fa_base_rec fa
972: WHERE isir.dependency_status IN ('I','D')
973: AND awd.fed_fund_code IN ('FWS','FSEOG','PRK')
974: AND awd.ci_cal_type =cp_ci_cal_type
988: AND cp_rec_type ='AUTO_ZERO';
989: */
990: l_std_cnt igf_aw_fisap_ii_h.student_count%TYPE DEFAULT 0;
991: l_rec_type igf_aw_fisap_vi_h.rec_type%TYPE;
992: l_fund_type igf_aw_award_v.fed_fund_code%TYPE;
993:
994: BEGIN
995: -- code commented as part of Fa132. function only returns 0 since this function is being used
996: -- in igf_aw_fisap_section_ii_v and igf_aw_fisap_section_vi_v