[Home] [Help]
PACKAGE: APPS.IGF_GR_GEN
Source
1 PACKAGE igf_gr_gen AUTHID CURRENT_USER AS
2 /* $Header: IGFGR08S.pls 115.18 2003/12/10 09:16:15 veramach noship $ */
3
4 -----------------------------------------------------------------------------------
5 -- Who When What
6 ------------------------------------------------------------------------------------
7 -- veramach 10-Dec-2003 FA 131 COD Updates
8 -- Removed function get_rep_pell_id
9 -----------------------------------------------------------------------------------
10 -- rasahoo 01-Sep-2003 Replaced igf_ap_fa_base_h_all.derived_attend_type%TYPE
11 -- with igs_en_stdnt_ps_att_all.derived_att_type%TYPE
12 -- as part of the build FA-114 (Obsoletion of FA base record History)
13 -----------------------------------------------------------------------------------
14 -- gmuralid 10-Apr-2003 Bug 2744419
15 -- Added Function get_calendar_desc to get
16 -- the calendar description.
17 -----------------------------------------------------------------------------------
18 -- sjadhav 01-Apr-2003 Bug 2875503
19 -- Changed in parameter for get_ssn_digits
20 -----------------------------------------------------------------------------------
21 -- sjadhav 05-Feb-2003 FA116 Build - Bug 2758812 - 2/4/03
22 -- Added update_current_ssn,update_pell_status,
23 -- match_file_version,get_min_disb_number
24 -----------------------------------------------------------------------------------
25 -- sjadhav Nov,18,2002. Bug 2590991
26 -- Routine to fetch base id
27 -----------------------------------------------------------------------------------
28 -- sjadhav Oct.25.2002 Bug 2613546,2606001
29 -- get_tufees_code,get_def_awd_year,ovrd_coa_exist,
30 -- delete_coa,update_item_dist,insert_coa_items,
31 -- insert_coa_terms,get_pell_code,insert_stu_coa_terms,
32 -- delete_stu_coa_terms,delete_stu_coa_items,
33 -- update_stu_coa_items routines added
34 -----------------------------------------------------------------------------------
35 -- sjadhav Oct.10.2002 Bug 2383690
36 -- 1. Added send_orig_disb
37 -- 2. Added get_min_pell_disb
38 -- 3. Added get_min_awd_disb
39 --
40 -- nsidana 10/31/2003 Multiple FA offices.
41 -- Added 3 new functions to derive the reporting pell ID
42 -- for a student.
43 -----------------------------------------------------------------------------------
44 --
45 -- sjadhav
46 -- This is a generic Utility Package aimed at centralization of
47 -- common functions/procedures
48 --
49 -- This package contains
50 -- 1. get_rep_pell_id
51 -- 2. get_pell_header
52 -- 3. get_pell_trailer
53 -- 4. process_pell_ack
54 -- routines which are very specific to Pell Subsytem
55 --
56 -- Other routines are general
57 --
58 -----------------------------------------------------------------------------------
59
60
61 no_file_version EXCEPTION;
62 file_not_loaded EXCEPTION;
63 batch_not_in_system EXCEPTION;
64 corrupt_data_file EXCEPTION;
65 skip_this_record EXCEPTION;
66
67 FUNCTION get_cycle_year (p_ci_cal_type igf_gr_rfms.ci_cal_type%TYPE,
68 p_ci_sequence_number igf_gr_rfms.ci_sequence_number%TYPE)
69 RETURN VARCHAR2;
70
71 FUNCTION disb_has_adj ( p_award_id igf_aw_award_all.award_id%TYPE,
72 p_disb_num igf_aw_awd_disb_all.disb_num%TYPE)
73 RETURN BOOLEAN;
74
75
76 FUNCTION get_alt_code ( p_ci_cal_type IN igs_ca_inst_all.cal_type%TYPE,
77 p_ci_sequence_number IN igs_ca_inst_all.sequence_number%TYPE)
78 RETURN VARCHAR2;
79
80 FUNCTION get_calendar_desc ( p_ci_cal_type IN igs_ca_inst_all.cal_type%TYPE,
81 p_ci_sequence_number IN igs_ca_inst_all.sequence_number%TYPE)
82 RETURN VARCHAR2;
83
84 FUNCTION get_per_num ( p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE)
85 RETURN VARCHAR2;
86
87
88
89 FUNCTION get_per_num ( p_person_id IN igf_ap_fa_base_rec_all.person_id%TYPE,
90 p_person_number OUT NOCOPY igf_ap_person_v.person_number%TYPE )
91 RETURN BOOLEAN;
92
93
94 FUNCTION get_person_id ( p_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
95 RETURN VARCHAR2;
96
97
98 FUNCTION get_per_num_oss ( p_person_id igf_ap_fa_base_rec_all.person_id%TYPE)
99 RETURN VARCHAR2;
100
101
102 PROCEDURE insert_sys_holds ( p_award_id igf_aw_award_all.award_id%TYPE,
103 p_disb_num igf_aw_awd_disb_all.disb_num%TYPE DEFAULT NULL,
104 p_hold igf_db_disb_holds_all.hold%TYPE);
105
106
107 FUNCTION get_min_awd_disb ( p_award_id igf_aw_award_all.award_id%TYPE)
108 RETURN NUMBER;
109
110
111 ----------------------------------------------------------------------------------------
112 -- Pell Routines
113 ----------------------------------------------------------------------------------------
114
115
116 -- Bug 3102439 FA126 Multiple FA Offices.
117 -- Added two extra parameters p_ci_cal_type and p_ci_sequence_number
118
119 FUNCTION get_pell_header ( p_ver_num IN VARCHAR2,
120 p_cycle_year IN VARCHAR2,
121 p_rep_pell_id IN igf_gr_pell_setup_all.rep_pell_id%TYPE,
122 p_batch_type IN VARCHAR2,
123 p_rfmb_id OUT NOCOPY igf_gr_rfms_batch.rfmb_id%TYPE,
124 p_batch_id OUT NOCOPY VARCHAR2,
125 p_ci_cal_type IN VARCHAR2,
126 p_ci_sequence_number IN NUMBER)
127 RETURN VARCHAR2;
128
129
130
131 FUNCTION get_pell_trailer ( p_ver_num IN VARCHAR2,
132 p_cycle_year IN VARCHAR2,
133 p_rep_pell_id IN igf_gr_pell_setup_all.rep_pell_id%TYPE,
134 p_batch_type IN VARCHAR2,
135 p_num_of_rec IN NUMBER,
136 p_amount_total IN NUMBER,
137 p_batch_id OUT NOCOPY VARCHAR2)
138 RETURN VARCHAR2;
139
140
141 PROCEDURE process_pell_ack ( p_ver_num IN VARCHAR2,
142 p_file_type IN VARCHAR2,
143 p_number_rec OUT NOCOPY NUMBER,
144 p_last_gldr_id OUT NOCOPY NUMBER,
145 p_batch_id OUT NOCOPY VARCHAR2);
146
147
148 FUNCTION get_pell_efc ( p_base_id IN igf_aw_award_all.base_id%TYPE)
149 RETURN NUMBER;
150
151
152 FUNCTION send_orig_disb ( p_orig_id igf_gr_rfms_all.origination_id%TYPE)
153 RETURN BOOLEAN;
154
155
156 FUNCTION get_min_pell_disb ( p_orig_id igf_gr_rfms_all.origination_id%TYPE)
157 RETURN NUMBER;
158
159 FUNCTION get_pell_efc_code ( p_base_id IN igf_aw_award_all.base_id%TYPE)
160 RETURN VARCHAR2;
161
162 FUNCTION fresh_origintn ( p_orig_id igf_gr_rfms_all.origination_id%TYPE)
163 RETURN BOOLEAN;
164
165 FUNCTION get_fund_id ( p_award_id igf_aw_award_all.award_id%TYPE)
166 RETURN NUMBER;
167
168 FUNCTION get_ssn_digits(p_ssn igs_pe_alt_pers_id.api_person_id_uf%TYPE)
169 RETURN VARCHAR2;
170
171 --
172 -- Bug 2613546,2606001
173 -- sjadhav
174 -- Oct,22,2002.
175 --
176 -- ovrd_coa_exist will check if there are any overridden coa items
177 -- for a coa group
178 --
179
180 PROCEDURE ovrd_coa_exist( p_coa_code IN igf_aw_coa_group_all.coa_code%TYPE,
181 p_cal_type IN igf_aw_coa_group_all.ci_cal_type%TYPE,
182 p_sequence_number IN igf_aw_coa_group_all.ci_sequence_number%TYPE,
183 p_exist OUT NOCOPY VARCHAR2
184 );
185 --
186 -- Bug 2613546,2606001
187 -- sjadhav
188 -- Oct,22,2002.
189 --
190 -- delete coa group childs
191 --
192
193 PROCEDURE delete_coa( p_record IN VARCHAR2,
194 p_coa_code IN igf_aw_coa_group_all.coa_code%TYPE,
195 p_cal_type IN igf_aw_coa_group_all.ci_cal_type%TYPE,
196 p_sequence_number IN igf_aw_coa_group_all.ci_sequence_number%TYPE,
197 p_item_code IN igf_aw_coa_grp_item_all.item_code%TYPE DEFAULT NULL
198 );
199 --
200 -- Bug 2613546,2606001
201 -- sjadhav
202 -- Oct,22,2002.
203 --
204 -- update coa item for item overidden distribution
205 --
206
207 PROCEDURE update_item_dist( p_coa_code IN igf_aw_cit_ld_ovrd_all.coa_code%TYPE,
208 p_cal_type IN igf_aw_cit_ld_ovrd_all.ci_cal_type%TYPE,
209 p_sequence_number IN igf_aw_cit_ld_ovrd_all.ci_sequence_number%TYPE,
210 p_item_code IN igf_aw_cit_ld_ovrd_all.item_code%TYPE,
211 p_upd_result OUT NOCOPY VARCHAR2);
212
213 --
214 -- Bug 2613546,2606001
215 -- sjadhav
216 -- Oct,22,2002.
217 --
218 -- Routine to fetch default award year
219 --
220
221 PROCEDURE get_def_awd_year(p_alternate_code OUT NOCOPY igs_ca_inst_all.alternate_code%TYPE,
222 p_cal_type OUT NOCOPY igs_ca_inst_all.cal_type%TYPE,
223 p_sequence_number OUT NOCOPY igs_ca_inst_all.sequence_number%TYPE,
224 p_start_date OUT NOCOPY igs_ca_inst_all.start_dt%TYPE,
225 p_end_date OUT NOCOPY igs_ca_inst_all.end_dt%TYPE,
226 p_err_msg OUT NOCOPY VARCHAR2
227 );
228
229
230 --
231 -- Bug 2613546,2606001
232 -- sjadhav
233 -- Oct,22,2002.
234 --
235 -- Routine to fetch Low Tution and Fees Code
236 --
237
238 FUNCTION get_tufees_code(p_base_id IN igf_gr_rfms_all.base_id%TYPE,
239 p_cal_type IN igf_gr_rfms_all.ci_cal_type%TYPE,
240 p_sequence_number IN igf_gr_rfms_all.ci_sequence_number%TYPE)
241 RETURN VARCHAR2;
242
243 --
244 -- Bug 2606001
245 -- FA105 108 Build
246 -- sjadhav
247 -- routine to add coa items in overide table with default
248 -- term %
249 --
250
251 PROCEDURE insert_coa_items( p_coa_code IN igf_aw_coa_group_all.coa_code%TYPE,
252 p_cal_type IN igf_aw_coa_group_all.ci_cal_type%TYPE,
253 p_sequence_number IN igf_aw_coa_group_all.ci_sequence_number%TYPE,
254 p_item_code IN igf_aw_coa_grp_item_all.item_code%TYPE,
255 p_count OUT NOCOPY NUMBER
256 );
257
258
259 --
260 -- Bug 2606001
261 -- FA105 108 Build
262 -- sjadhav
263 -- routine to add coa items in overide table with 0
264 -- term % once a new term is added
265 --
266
267 PROCEDURE insert_coa_terms( p_coa_code IN igf_aw_coa_group_all.coa_code%TYPE,
268 p_cal_type IN igf_aw_coa_group_all.ci_cal_type%TYPE,
269 p_sequence_number IN igf_aw_coa_group_all.ci_sequence_number%TYPE,
270 p_ld_cal_type IN igf_aw_coa_ld_all.ld_cal_type%TYPE,
271 p_ld_sequence_number IN igf_aw_coa_ld_all.ld_sequence_number%TYPE
272 );
273
274
275
276 --
277 -- Bug 2606001
278 -- FA105 108 Build
279 -- sjadhav
280 -- routine to add coa items in students coa table
281 --
282
283 PROCEDURE insert_stu_coa_terms( p_base_id IN igf_aw_coa_itm_terms.base_id%TYPE,
287 );
284 p_ld_cal_type IN igf_aw_coa_ld_all.ld_cal_type%TYPE,
285 p_ld_sequence_number IN igf_aw_coa_ld_all.ld_sequence_number%TYPE,
286 p_result OUT NOCOPY VARCHAR2
288
289
290 --
291 -- Bug 2606001
292 -- FA105 108 Build
293 -- sjadhav
294 -- routine to delete coa items-terms from students coa table
295 --
296
297 PROCEDURE delete_stu_coa_terms( p_base_id IN igf_aw_coa_itm_terms.base_id%TYPE,
298 p_ld_cal_type IN igf_aw_coa_ld_all.ld_cal_type%TYPE,
299 p_ld_sequence_number IN igf_aw_coa_ld_all.ld_sequence_number%TYPE,
300 p_result OUT NOCOPY VARCHAR2
301 );
302
303 --
304 -- Bug 2606001
305 -- FA105 108 Build
306 -- sjadhav
307 -- routine to delete coa items from students coa table
308 --
309
310 PROCEDURE delete_stu_coa_items( p_base_id IN igf_aw_coa_itm_terms.base_id%TYPE,
311 p_result OUT NOCOPY VARCHAR2,
312 p_item_code IN igf_aw_coa_items.item_code%TYPE DEFAULT NULL
313 );
314
315 --
316 -- Bug 2606001
317 -- FA105 108 Build
318 -- sjadhav
319 -- routine to update coa items-amount in students coa table
320 --
321
322 PROCEDURE update_stu_coa_items( p_base_id IN igf_aw_coa_itm_terms.base_id%TYPE,
323 p_item_code IN igf_aw_coa_itm_terms.item_code%TYPE DEFAULT NULL,
324 p_result OUT NOCOPY VARCHAR2
325 );
326
327
328 --
329 -- Bug 2606001
330 -- FA105 108 Build
331 -- sjadhav
332 -- routine to get pell att code from attend map form
333 --
334
335
336 PROCEDURE insert_existing_terms( p_base_id IN igf_aw_coa_itm_terms.base_id%TYPE,
337 p_item_code IN igf_aw_coa_itm_terms.item_code%TYPE,
338 p_result OUT NOCOPY VARCHAR2
339 );
340
341
342 FUNCTION get_pell_code(p_att_code IN igs_en_stdnt_ps_att_all.derived_att_type%TYPE,
343 p_cal_type IN igf_ap_fa_base_rec.ci_cal_type%TYPE,
344 p_sequence_number IN igf_ap_fa_base_rec.ci_sequence_number%TYPE)
345 RETURN VARCHAR2;
346
347
348 --
349 -- Bug 2590991
350 -- sjadhav
351 -- Nov,18,2002.
352 --
353 -- Routine to fetch base id
354 --
355
356 PROCEDURE get_base_id(p_cal_type IN igs_ca_inst_all.cal_type%TYPE,
357 p_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
358 p_person_id IN igf_ap_fa_base_rec_all.person_id%TYPE,
359 p_base_id OUT NOCOPY igf_ap_fa_base_rec_all.base_id%TYPE,
360 p_err_msg OUT NOCOPY VARCHAR2
361 );
362
363 --
364 -- sjadhav,2/4/03
365 -- FA116 Build - Bug 2758812 - 2/4/03
366 --
367 PROCEDURE update_current_ssn (p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
368 p_cur_ssn IN igf_ap_isir_matched_all.current_ssn%TYPE,
369 p_message OUT NOCOPY fnd_new_messages.message_name%TYPE);
370
371 PROCEDURE update_pell_status (p_award_id IN igf_aw_award_all.award_id%TYPE,
372 p_fed_fund_code IN igf_aw_fund_cat_all.fed_fund_code%TYPE,
373 p_message OUT NOCOPY fnd_new_messages.message_name%TYPE,
374 p_status_desc OUT NOCOPY igf_lookups_view.meaning%TYPE);
375
376 PROCEDURE match_file_version (p_version IN igf_lookups_view.lookup_code%TYPE,
377 p_batch_id IN igf_gr_rfms_batch_all.batch_id%TYPE,
381 RETURN NUMBER;
378 p_message OUT NOCOPY fnd_new_messages.message_name%TYPE);
379
380 FUNCTION get_min_disb_number (p_award_id igf_aw_award_all.award_id%TYPE)
382 -- Added As part of FA121 Build (Verification Worksheet Enhancements)
383 FUNCTION chk_orig_isir_exists( p_base_id IN igf_ap_fa_base_rec.base_id%TYPE,
384 p_transaction_num IN igf_ap_ISIR_matched.transaction_num%TYPE)
385 RETURN BOOLEAN;
386
387
388 FUNCTION get_rep_pell_from_ope(p_cal_type igs_ca_inst_all.cal_type%TYPE,
389 p_seq_num igs_ca_inst_all.sequence_number%TYPE,
390 p_ope_cd igf_gr_report_pell.ope_cd%TYPE)
391 RETURN VARCHAR2;
392
393 FUNCTION get_rep_pell_from_att(p_cal_type igs_ca_inst_all.cal_type%TYPE,
394 p_seq_num igs_ca_inst_all.sequence_number%TYPE,
395 p_att_pell igf_gr_attend_pell.attending_pell_cd%TYPE)
396 RETURN VARCHAR2;
397
398 FUNCTION get_rep_pell_from_base(p_cal_type igs_ca_inst_all.cal_type%TYPE,
399 p_seq_num igs_ca_inst_all.sequence_number%TYPE,
400 p_base_id NUMBER)
401 RETURN VARCHAR2;
402
403 END igf_gr_gen;