[Home] [Help]
PACKAGE: APPS.IGF_SL_GEN
Source
1 PACKAGE igf_sl_gen AS
2 /* $Header: IGFSL12S.pls 120.1 2006/08/07 13:21:34 azmohamm noship $ */
3
4 /****************************************************************************
5 Created By : venagara
6 Date Created On : 2000/11/20
7 Purpose :
8 Know limitations, enhancements or remarks
9 Change History
10 Who When What
11 azmohamm 03-Aug-2006 FA-163 : Added chk_cl_gplus function
12 museshad 06-May-2005 Bug# 4346258 - Added extra parameter 'p_base_id'
13 to the function 'get_cl_version()'. This is
14 needed to arrive at the correct CL version# by
15 taking into account any CL version# override
16 for any particular Organization Unit.
17 sjadhav 09-Nov-2004 Bug #3416936.added rel code to cl version
18 svuppala 20-Oct-2004 Bug #3416936.Added new update change status.
19
20 smadathi 14-oct-2004 Bug 3416936.Added new generic functions as
21 given in the TD.
22 ugummall 14-OCT-2003 Bug# 3102439. FA 126 Multiple FA Offices.
23 Added new routines get_associated_org and
24 get_stu_fao_code.
25
26 bkkumar 15-Sep-2003 Bug# 3104228. FA 122 Loans Enhancements
27 Added new routine check_rel,get_person_details
28 and check_lend_relation
29
30 (reverse chronological order - newest change first)
31 ***********************************************************************************/
32
33 TYPE person_dtl_rec IS RECORD
34 (
35 p_alien_reg_num VARCHAR2(150),
36 p_citizenship_status VARCHAR2(30),
37 p_date_of_birth DATE,
38 p_email_addr VARCHAR2(2000),
39 p_first_name VARCHAR2(150),
40 p_full_name VARCHAR2(450),
41 p_last_name VARCHAR2(150),
42 p_legal_res_date DATE,
43 p_license_num VARCHAR2(20),
44 p_license_state VARCHAR2(30),
45 p_middle_name VARCHAR2(60),
46 p_permt_addr1 VARCHAR2(240),
47 p_permt_addr2 VARCHAR2(240),
48 p_permt_city VARCHAR2(60),
49 p_permt_state VARCHAR2(150),
50 p_permt_zip VARCHAR2(60),
51 p_ssn VARCHAR2(20),
52 p_state_of_legal_res VARCHAR2(60),
53 p_province VARCHAR2(60),
54 p_county VARCHAR2(60),
55 p_country VARCHAR2(60),
56 p_local_addr1 VARCHAR2(240),
57 p_local_addr2 VARCHAR2(240),
58 p_local_city VARCHAR2(60),
59 p_local_state VARCHAR2(150),
60 p_local_zip VARCHAR2(60)
61 );
62
63 TYPE person_dtl_cur IS REF CURSOR RETURN person_dtl_rec;
64
65
66 PROCEDURE get_person_details ( p_person_id IN igf_sl_cl_pref_lenders.person_id%TYPE,
67 p_person_dtl_rec IN OUT NOCOPY person_dtl_cur);
68 PROCEDURE check_rel (
69 p_rel_code IN igf_sl_cl_setup_all.relationship_cd%TYPE,
70 p_flag OUT NOCOPY VARCHAR2
71 );
72
73 FUNCTION chk_dl_fed_fund_code(p_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE)
74 RETURN VARCHAR2;
75
76 FUNCTION chk_dl_stafford(p_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE)
77 RETURN VARCHAR2;
78
79
80 FUNCTION chk_dl_plus(p_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE)
81 RETURN VARCHAR2;
82
83 FUNCTION chk_cl_fed_fund_code(p_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE)
84 RETURN VARCHAR2;
85
86 FUNCTION chk_cl_gplus(p_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE)
87 RETURN VARCHAR2;
88
89 FUNCTION chk_cl_stafford(p_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE)
90 RETURN VARCHAR2;
91
92 FUNCTION chk_cl_plus(p_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE)
93 RETURN VARCHAR2;
94 /* Function to check for Alternative Loan */
95 FUNCTION chk_cl_alt(p_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE)
96 RETURN VARCHAR2;
97
98 FUNCTION base10_to_base36(p_base_10 NUMBER)
99 RETURN VARCHAR;
100
101 FUNCTION get_grade_level_desc(p_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE,
102 p_grade_level_code igf_sl_lor_all.grade_level_code%TYPE)
103 RETURN VARCHAR2;
104
105 FUNCTION get_enrollment_desc(p_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE,
106 p_enrollment_code igf_sl_lor_all.enrollment_code%TYPE)
107 RETURN VARCHAR2;
108
109 FUNCTION get_dl_version(p_ci_cal_type igf_sl_dl_setup_all.ci_cal_type%TYPE,
110 p_ci_seq_num igf_sl_dl_setup_all.ci_sequence_number%TYPE)
111 RETURN VARCHAR2;
112
113 -- museshad. Bug# 4346258 - Added extra parameter p_base_id. This is needed to
114 -- arrive at the correct CL version# by taking into account any CL version#
115 -- override for any particular Organization Unit.
116 FUNCTION get_cl_version(p_ci_cal_type igf_sl_cl_setup_all.ci_cal_type%TYPE,
117 p_ci_seq_num igf_sl_cl_setup_all.ci_sequence_number%TYPE,
118 p_relationship_cd igf_sl_cl_setup_all.relationship_cd%TYPE,
119 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
120 RETURN VARCHAR2;
121
122 FUNCTION get_dl_file_type(p_dl_version igf_sl_dl_file_type.dl_version%TYPE,
123 p_dl_file_type igf_sl_dl_file_type.dl_file_type%TYPE,
124 p_dl_loan_catg igf_sl_dl_file_type.dl_loan_catg%TYPE,
125 p_return_type VARCHAR2)
126 RETURN VARCHAR2;
127
128 PROCEDURE get_dl_batch_details(p_message_class IN igf_sl_dl_file_type.message_class%TYPE,
129 p_batch_type IN igf_sl_dl_file_type.batch_type%TYPE,
130 p_dl_version OUT NOCOPY igf_sl_dl_file_type.dl_version%TYPE,
131 p_dl_file_type OUT NOCOPY igf_sl_dl_file_type.dl_file_type%TYPE,
132 p_dl_loan_catg OUT NOCOPY igf_sl_dl_file_type.dl_loan_catg%TYPE);
133
134 FUNCTION get_cl_file_type(p_cl_version igf_sl_dl_file_type.dl_version%TYPE,
135 p_cl_file_type igf_sl_dl_file_type.dl_file_type%TYPE,
136 p_return_type VARCHAR2)
137 RETURN VARCHAR2;
138
139 PROCEDURE get_cl_batch_details(p_file_ident_code IN igf_sl_cl_file_type.file_ident_code%TYPE,
140 p_file_ident_name IN igf_sl_cl_file_type.file_ident_name%TYPE,
141 p_cl_version OUT NOCOPY igf_sl_cl_file_type.cl_version%TYPE,
142 p_cl_file_type OUT NOCOPY igf_sl_cl_file_type.cl_file_type%TYPE);
143 /* Function to get the Disbursement Date */
144 FUNCTION get_disb_date(p_loan_id IN igf_sl_loans.loan_id%TYPE,
145 p_disb_num IN igf_aw_awd_disb.disb_num%TYPE)
146 RETURN DATE;
147 /* Function to get the Phone Number */
148 FUNCTION get_person_phone(p_person_id IN igs_pe_contacts_v.owner_table_id%TYPE)
149 RETURN VARCHAR2;
150
151 PROCEDURE check_lend_relation( p_person_id IN igf_sl_cl_pref_lenders.person_id%TYPE,
152 p_start_date IN DATE,
153 p_end_date IN DATE,
154 p_message OUT NOCOPY VARCHAR2);
155
156 /* Procedure for obtaining responsible org unit code associated with the student */
157 PROCEDURE get_associated_org( p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
158 x_org_unit_cd OUT NOCOPY hz_parties.party_number%TYPE,
159 x_org_party_id OUT NOCOPY hz_parties.party_id%TYPE,
160 x_module OUT NOCOPY VARCHAR2,
161 x_return_status OUT NOCOPY VARCHAR2,
162 x_msg_data OUT NOCOPY VARCHAR2);
163
164 /* Procedure for obtaining school code configured at Org Unit of the Student's key program */
165 PROCEDURE get_stu_fao_code( p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
166 p_office_type IN igs_lookups_view.lookup_code%TYPE,
167 x_office_cd OUT NOCOPY igs_or_org_alt_ids.org_alternate_id_type%TYPE,
168 x_return_status OUT NOCOPY VARCHAR2,
169 x_msg_data OUT NOCOPY VARCHAR2);
170
171 FUNCTION get_fed_fund_code (p_n_award_id IN igf_aw_award_all.award_id%TYPE,
172 p_v_message_name OUT NOCOPY VARCHAR2)
173 RETURN igf_aw_fund_cat_all.fed_fund_code%TYPE;
174
175 FUNCTION check_prc_chg (p_v_relationship_cd IN igf_sl_cl_setup_all.relationship_cd%TYPE,
176 p_v_cal_type IN igf_aw_fund_mast_all.ci_cal_type%TYPE ,
177 p_n_sequence_number IN igf_aw_fund_mast_all.ci_sequence_number%TYPE
178 )
179 RETURN BOOLEAN;
180
181 FUNCTION check_prc_chgm (p_v_relationship_cd IN igf_sl_cl_setup_all.relationship_cd%TYPE,
182 p_v_cal_type IN igf_aw_fund_mast_all.ci_cal_type%TYPE ,
183 p_n_sequence_number IN igf_aw_fund_mast_all.ci_sequence_number%TYPE
184 )
185 RETURN BOOLEAN;
186
187 PROCEDURE update_cl_chg_status(p_v_loan_number IN igf_sl_loans_all.loan_number%TYPE);
188
189 PROCEDURE get_stu_ant_fao_code
190 (p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
191 p_office_type IN igs_lookups_view.lookup_code%TYPE,
192 x_office_cd OUT NOCOPY igs_or_org_alt_ids.org_alternate_id_type%TYPE,
193 x_return_status OUT NOCOPY VARCHAR2,
194 x_msg_data OUT NOCOPY VARCHAR2);
195
196
197 END igf_sl_gen;