[Home] [Help]
31: sequence_number igs_ca_inst_all.sequence_number%TYPE,
32: fund_id igf_aw_fund_mast_all.fund_id%TYPE,
33: discontinue_fund igf_aw_fund_mast_all.discontinue_fund%TYPE,
34: fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE,
35: base_id igf_ap_fa_base_rec_all.base_id%TYPE,
36: award_id igf_aw_award_all.award_id%TYPE,
37: loan_id igf_sl_loans_all.loan_id%TYPE,
38: loan_number igf_sl_loans_all.loan_number%TYPE,
39: loan_status igf_sl_loans_all.loan_status%TYPE,
61: (
62: p_v_cal_type IN igs_ca_inst_all.cal_type%TYPE,
63: p_n_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
64: p_n_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
65: p_n_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
66: p_n_loan_id IN igf_sl_loans_all.loan_id%TYPE
67: );
68: FUNCTION get_base_id
69: (
68: FUNCTION get_base_id
69: (
70: p_v_cal_type IN igs_ca_inst_all.cal_type%TYPE,
71: p_n_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
72: p_n_person_id IN igf_ap_fa_base_rec_all.person_id%TYPE
73: )
74: RETURN igf_ap_fa_base_rec_all.base_id%TYPE;
75:
76: FUNCTION get_person_number (p_n_person_id IN igf_ap_fa_base_rec_all.person_id%TYPE)
70: p_v_cal_type IN igs_ca_inst_all.cal_type%TYPE,
71: p_n_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
72: p_n_person_id IN igf_ap_fa_base_rec_all.person_id%TYPE
73: )
74: RETURN igf_ap_fa_base_rec_all.base_id%TYPE;
75:
76: FUNCTION get_person_number (p_n_person_id IN igf_ap_fa_base_rec_all.person_id%TYPE)
77: RETURN hz_parties.party_number%TYPE;
78:
72: p_n_person_id IN igf_ap_fa_base_rec_all.person_id%TYPE
73: )
74: RETURN igf_ap_fa_base_rec_all.base_id%TYPE;
75:
76: FUNCTION get_person_number (p_n_person_id IN igf_ap_fa_base_rec_all.person_id%TYPE)
77: RETURN hz_parties.party_number%TYPE;
78:
79: FUNCTION validate_cl_lar (p_rec_lorlar lorlar_recTyp) RETURN BOOLEAN;
80:
91: retcode OUT NOCOPY NUMBER,
92: p_v_award_year IN VARCHAR2,
93: p_n_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
94: p_n_dummy_1 IN NUMBER,
95: p_n_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
96: p_n_dummy_2 IN NUMBER,
97: p_n_loan_id IN igf_sl_loans_all.loan_id%TYPE,
98: p_n_dummy_3 IN NUMBER,
99: p_n_person_id_grp IN igs_pe_persid_group_all.group_id%TYPE,
215:
216: l_v_cal_type igs_ca_inst_all.cal_type%TYPE;
217: l_n_sequence_number igs_ca_inst_all.sequence_number%TYPE;
218: l_n_loan_id igf_sl_loans_all.loan_id%TYPE;
219: l_n_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
220: l_n_fund_id igf_aw_fund_mast_all.fund_id%TYPE;
221: l_n_person_id igf_ap_fa_base_rec_all.person_id%TYPE;
222: l_n_person_id_grp igs_pe_persid_group_all.group_id%TYPE;
223: l_v_school_id igf_sl_school_codes_v.alternate_identifier%TYPE;
217: l_n_sequence_number igs_ca_inst_all.sequence_number%TYPE;
218: l_n_loan_id igf_sl_loans_all.loan_id%TYPE;
219: l_n_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
220: l_n_fund_id igf_aw_fund_mast_all.fund_id%TYPE;
221: l_n_person_id igf_ap_fa_base_rec_all.person_id%TYPE;
222: l_n_person_id_grp igs_pe_persid_group_all.group_id%TYPE;
223: l_v_school_id igf_sl_school_codes_v.alternate_identifier%TYPE;
224: l_v_media_type igf_lookups_view.lookup_code%TYPE;
225: l_v_sch_non_ed_branch igf_sl_school_codes_v.alternate_identifier%TYPE;
722: FUNCTION get_base_id
723: (
724: p_v_cal_type IN igs_ca_inst_all.cal_type%TYPE,
725: p_n_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
726: p_n_person_id IN igf_ap_fa_base_rec_all.person_id%TYPE
727: ) RETURN igf_ap_fa_base_rec_all.base_id%TYPE AS
728: ------------------------------------------------------------------
729: --Created by : Sanil Madathil, Oracle IDC
730: --Date created: 25 October 2004
723: (
724: p_v_cal_type IN igs_ca_inst_all.cal_type%TYPE,
725: p_n_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
726: p_n_person_id IN igf_ap_fa_base_rec_all.person_id%TYPE
727: ) RETURN igf_ap_fa_base_rec_all.base_id%TYPE AS
728: ------------------------------------------------------------------
729: --Created by : Sanil Madathil, Oracle IDC
730: --Date created: 25 October 2004
731: --
742: --
743: --Change History:
744: --Who When What
745: ------------------------------------------------------------------
746: CURSOR c_ap_fa_base_rec (cp_n_person_id igf_ap_fa_base_rec_all.person_id%TYPE,
747: cp_v_cal_type igs_ca_inst_all.cal_type%TYPE,
748: cp_n_sequence_number igs_ca_inst_all.sequence_number%TYPE
749: ) IS
750: SELECT base_id
747: cp_v_cal_type igs_ca_inst_all.cal_type%TYPE,
748: cp_n_sequence_number igs_ca_inst_all.sequence_number%TYPE
749: ) IS
750: SELECT base_id
751: FROM igf_ap_fa_base_rec_all fabase
752: WHERE fabase.person_id = cp_n_person_id
753: AND fabase.ci_cal_type = cp_v_cal_type
754: AND fabase.ci_sequence_number = cp_n_sequence_number;
755:
752: WHERE fabase.person_id = cp_n_person_id
753: AND fabase.ci_cal_type = cp_v_cal_type
754: AND fabase.ci_sequence_number = cp_n_sequence_number;
755:
756: l_n_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
757: BEGIN
758: log_to_fnd(p_v_module => 'get_base_id',
759: p_v_string => ' Entered Procedure get_base_id: The input parameters are '||
760: ' p_v_cal_type : ' ||p_v_cal_type ||
772: );
773: RETURN l_n_base_id;
774: END get_base_id;
775:
776: FUNCTION get_person_number (p_n_person_id IN igf_ap_fa_base_rec_all.person_id%TYPE)
777: RETURN hz_parties.party_number%TYPE AS
778: ------------------------------------------------------------------
779: --Created by : Sanil Madathil, Oracle IDC
780: --Date created: 25 October 2004
791: --Change History:
792: --Who When What
793: ------------------------------------------------------------------
794: -- cursor to get person number
795: CURSOR c_person_number (cp_n_person_id igf_ap_fa_base_rec_all.person_id%TYPE) IS
796: SELECT person_number
797: FROM igs_pe_person_base_v
798: WHERE person_id = cp_n_person_id;
799:
966: (
967: p_v_cal_type IN igs_ca_inst_all.cal_type%TYPE,
968: p_n_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
969: p_n_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
970: p_n_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
971: p_n_loan_id IN igf_sl_loans_all.loan_id%TYPE
972: ) AS
973: ------------------------------------------------------------------
974: --Created by : Sanil Madathil, Oracle IDC
1002:
1003: CURSOR c_lor_lar (cp_v_cal_type IN igs_ca_inst_all.cal_type%TYPE,
1004: cp_n_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
1005: cp_n_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
1006: cp_n_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
1007: cp_n_loan_id IN igf_sl_loans_all.loan_id%TYPE
1008: ) IS
1009: SELECT loans.row_id
1010: ,loans.loan_id
1054: AND clset.ci_sequence_number = fmast.ci_sequence_number
1055: AND clset.relationship_cd = lor.relationship_cd
1056: AND loans.loan_chg_status = 'G'
1057: AND EXISTS (SELECT '1'
1058: FROM igf_ap_fa_base_rec_all fabase
1059: WHERE fabase.base_id = awd.base_id
1060: AND fabase.ci_cal_type = fmast.ci_cal_type
1061: AND fabase.ci_sequence_number = fmast.ci_sequence_number
1062: );
1336: retcode OUT NOCOPY NUMBER,
1337: p_v_cal_type IN igs_ca_inst_all.cal_type%TYPE,
1338: p_n_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
1339: p_n_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
1340: p_n_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
1341: p_n_loan_id IN igf_sl_loans_all.loan_id%TYPE,
1342: p_v_relationship_cd IN igf_sl_cl_recipient.relationship_cd%TYPE,
1343: p_v_media_type IN igf_lookups_view.lookup_code%TYPE,
1344: p_v_school_id IN igf_sl_school_codes_v.alternate_identifier%TYPE,
1376: ------------------------------------------------------------------
1377: CURSOR c_recip_dtls( cp_v_cal_type IN igs_ca_inst_all.cal_type%TYPE,
1378: cp_n_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
1379: cp_n_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
1380: cp_n_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
1381: cp_n_loan_id IN igf_sl_loans_all.loan_id%TYPE,
1382: cp_v_relationship_cd IN igf_sl_cl_recipient.relationship_cd%TYPE,
1383: cp_c_loan_chg_status IN igf_sl_loans_all.loan_chg_status%TYPE,
1384: cp_v_school_id IN igf_sl_school_codes_v.alternate_identifier%TYPE
1530: ,igf_aw_award_all awd
1531: ,igf_aw_fund_mast_all fmast
1532: ,igf_aw_fund_cat_all fcat
1533: ,igf_sl_cl_recipient recip
1534: ,igf_ap_fa_base_rec_all fabase
1535: WHERE lor.relationship_cd = cp_v_relationship_cd
1536: AND ((lor.loan_id = cp_n_loan_id) OR cp_n_loan_id IS NULL)
1537: AND loans.loan_id = lor.loan_id
1538: AND loans.loan_chg_status = cp_c_loan_chg_status
1621:
1622: rec_c_aw_awd_disb2 c_aw_awd_disb2%ROWTYPE;
1623:
1624: CURSOR cur_get_fin_aid(cp_n_award_id IN igf_aw_award_all.award_id%TYPE ,
1625: cp_n_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
1626: cp_awd_status_1 IN igf_aw_award.award_status%TYPE,
1627: cp_awd_status_2 IN igf_aw_award.award_status%TYPE
1628: ) IS
1629: SELECT TRUNC(SUM(NVL(NVL(awd.accepted_amt,awd.offered_amt),0))) etsimated_fin
1645: -- input variables for this procedure
1646: l_v_cal_type igs_ca_inst_all.cal_type%TYPE;
1647: l_n_sequence_number igs_ca_inst_all.sequence_number%TYPE;
1648: l_n_loan_id igf_sl_loans_all.loan_id%TYPE;
1649: l_n_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
1650: l_n_fund_id igf_aw_fund_mast_all.fund_id%TYPE;
1651: l_v_school_id igf_sl_school_codes_v.alternate_identifier%TYPE;
1652: l_v_media_type igf_lookups_view.lookup_code%TYPE;
1653: l_v_sch_non_ed_branch igf_sl_school_codes_v.alternate_identifier%TYPE;
1680: l_n_1cntr NUMBER;
1681: l_n_2cntr NUMBER;
1682:
1683: -- variables for (@1-02)
1684: l_n_borrower_id igf_ap_fa_base_rec_all.person_id%TYPE;
1685: l_v_chg_01_2_rec VARCHAR2(2000);
1686: c_borrower_dtls igf_sl_gen.person_dtl_cur;
1687: rec_borrower_dtls igf_sl_gen.person_dtl_rec;
1688: l_v_filler_3_char VARCHAR2(3);
1756: l_v_chg_01_24_rec VARCHAR2(4000);
1757: l_v_disb_rec VARCHAR2(1000);
1758: l_c_01_24_flg VARCHAR2(1);
1759: l_n_loan_amt_increase igf_aw_awd_disb_all.disb_accepted_amt%TYPE;
1760: l_n_coa igf_ap_fa_base_rec_all.coa_f%TYPE;
1761: l_n_efc igf_ap_fa_base_rec_all.efc_f%TYPE;
1762: l_n_est_fin igf_aw_award_all.accepted_amt%TYPE;
1763: l_n_pell_efc NUMBER;
1764: l_n_efc_f NUMBER;
1757: l_v_disb_rec VARCHAR2(1000);
1758: l_c_01_24_flg VARCHAR2(1);
1759: l_n_loan_amt_increase igf_aw_awd_disb_all.disb_accepted_amt%TYPE;
1760: l_n_coa igf_ap_fa_base_rec_all.coa_f%TYPE;
1761: l_n_efc igf_ap_fa_base_rec_all.efc_f%TYPE;
1762: l_n_est_fin igf_aw_award_all.accepted_amt%TYPE;
1763: l_n_pell_efc NUMBER;
1764: l_n_efc_f NUMBER;
1765: l_n_ctr_disb NUMBER;