[Home] [Help]
PACKAGE BODY: APPS.IGF_SP_GEN_001
Source
1 PACKAGE BODY IGF_SP_GEN_001 AS
2 /* $Header: IGFSP04B.pls 120.1 2006/05/15 23:51:26 svuppala noship $ */
3
4 ------------------------------------------------------------------------------------
5 --Created by : smanglm ( Oracle IDC)
6 --Date created: 2002/01/11
7 --
8 --Purpose: Created as part of the build for DLD Sponsorship
9 -- This package has generic fucntion which can be used by the system.
10 -- They are;
11 -- i) get_credit_points
12 -- ii) get_program_charge
13 -- iii) check_unit_attempt
14 -- iv) check_min_att_type
15 -- v) get_fee_cls_charge
16 --
17 --Known limitations/enhancements and/or remarks:
18 --
19 --Change History:
20 --Who When What
21 --smadathi 17-May-2002 Bug 2369173. The function get_program_charge
22 -- was modified.
23 --svuppala 16-May-2006 Bug 5194095 .Removed the functionS get_program_charge,get_fee_cls_charge.
24 -- Created procedures get_sponsor_amts, log_to_fnd.
25 -------------------------------------------------------------------------------------
26
27 -- Procedure for enabling statement level logging
28 PROCEDURE log_to_fnd (
29 p_v_module IN VARCHAR2,
30 p_v_string IN VARCHAR2
31 );
32
33 PROCEDURE get_sponsor_amts (
34 p_n_person_id IN hz_parties.party_id%TYPE,
35 p_v_fee_cal_type IN igs_ca_inst_all.cal_type%TYPE,
36 p_n_fee_seq_number IN igs_ca_inst_all.sequence_number%TYPE,
37 p_v_fund_code IN igf_aw_fund_cat_all.fund_code%TYPE,
38 p_v_ld_cal_type IN igs_ca_inst_all.cal_type%TYPE,
39 p_n_ld_seq_number IN igs_ca_inst_all.sequence_number%TYPE,
40 p_v_fee_class IN igs_fi_fee_type_all.fee_class%TYPE,
41 p_v_course_cd IN igs_fi_inv_int_all.course_cd%TYPE,
42 p_v_unit_cd IN igs_ps_unit_ofr_opt.unit_cd%TYPE,
43 p_n_unit_ver_num IN igs_ps_unit_ofr_opt_all.version_number%TYPE,
44 x_eligible_amount OUT NOCOPY NUMBER,
45 x_new_spnsp_amount OUT NOCOPY NUMBER
46 ) AS
47 -----------------------------------------------------------------------------------
48 --Created by : svuppala ( Oracle IDC)
49 --Date created: 28-Apr-2006
50 --
51 --Purpose: Created as part of Bug 4658908.
52 -- To determine both the new sponsor amount and the sponsor amount that can be given to student,
53 -- Fee calendar type and fee calendar sequence number combination so
54 -- that Sponsor amount does not exceed the total charge amount of the charges.
55 --
56 --Known limitations/enhancements and/or remarks:
57 --
58 --Change History:
59 --Who When What
60 -------------------------------------------------------------------------------------
61
62 -- cursor is used if Fee Class parameter has not been passed (Total Sponsor amount case)
63 CURSOR cur_get_all_charges (
64 cp_n_person_id IN hz_parties.party_id%TYPE,
65 cp_v_fee_cal_type IN igs_ca_inst_all.cal_type%TYPE,
66 cp_n_fee_seq_number IN igs_ca_inst_all.sequence_number%TYPE
67 ) IS
68 SELECT inv.invoice_id, inv.invoice_amount
69 FROM igs_fi_inv_int_all inv
70 WHERE inv.person_id = cp_n_person_id
71 AND inv.fee_cal_type = cp_v_fee_cal_type
72 AND inv.fee_ci_sequence_number = cp_n_fee_seq_number
73 AND inv.transaction_type NOT IN ('RETENTION','REFUND','AID_ADJ','WAIVER_ADJ','SPONSOR','PAY_PLAN');
74
75 -- cursor is used if course code or unit code and version number parameters are not passed.
76 CURSOR cur_get_charges_no_coursecd (
77 cp_n_person_id IN hz_parties.party_id%TYPE,
78 cp_v_fee_cal_type IN igs_ca_inst_all.cal_type%TYPE,
79 cp_n_fee_seq_number IN igs_ca_inst_all.sequence_number%TYPE,
80 cp_v_fee_class IN igs_fi_fee_type_all.fee_class%TYPE
81 ) IS
82 SELECT inv.invoice_id, inv.invoice_amount
83 FROM igs_fi_inv_int_all inv,
84 igs_fi_fee_type_all ft
85 WHERE inv.person_id = cp_n_person_id
86 AND inv.fee_cal_type = cp_v_fee_cal_type
87 AND inv.fee_ci_sequence_number = cp_n_fee_seq_number
88 AND inv.transaction_type NOT IN ('RETENTION','REFUND','AID_ADJ','WAIVER_ADJ','SPONSOR','PAY_PLAN')
89 AND ft.fee_type = inv.fee_type
90 AND ft.fee_class = cp_v_fee_class;
91
92 CURSOR cur_get_charges_with_coursecd ( cp_n_person_id IN hz_parties.party_id%TYPE,
93 cp_v_fee_cal_type IN igs_ca_inst_all.cal_type%TYPE,
94 cp_n_fee_seq_number IN igs_ca_inst_all.sequence_number%TYPE,
95 cp_v_fee_class IN igs_fi_fee_type_all.fee_class%TYPE,
96 cp_v_course_cd IN igs_fi_inv_int_all.course_cd%TYPE
97 ) IS
98 SELECT inv.invoice_id, inv.invoice_amount
99 FROM igs_fi_inv_int_all inv,
100 igs_fi_fee_type_all ft
101 WHERE inv.person_id = cp_n_person_id
102 AND inv.fee_cal_type = cp_v_fee_cal_type
103 AND inv.fee_ci_sequence_number = cp_n_fee_seq_number
104 AND inv.transaction_type <> 'RETENTION'
105 AND inv.course_cd = cp_v_course_cd
106 AND ft.fee_type = inv.fee_type
107 AND ft.fee_class = cp_v_fee_class;
108
109 CURSOR cur_get_charges_for_unit (
110 cp_n_person_id IN hz_parties.party_id%TYPE,
111 cp_v_fee_cal_type IN igs_ca_inst_all.cal_type%TYPE,
112 cp_n_fee_seq_number IN igs_ca_inst.sequence_number%TYPE,
113 cp_v_fee_class IN igs_fi_fee_type_all.fee_class%TYPE,
114 cp_v_unit_cd IN igs_ps_unit_ver_all.unit_cd%TYPE,
115 cp_n_unit_ver_num IN igs_ps_unit_ver_all.version_number%TYPE
116 ) IS
117 SELECT inv.invoice_id, inv.invoice_amount
118 FROM igs_fi_inv_int_all inv,
119 igs_fi_invln_int_all invln,
120 igs_fi_fee_type_all ft,
121 igs_ps_unit_ofr_opt_all uoo
122 WHERE inv.person_id = cp_n_person_id
123 AND inv.fee_cal_type = cp_v_fee_cal_type
124 AND inv.fee_ci_sequence_number =cp_n_fee_seq_number
125 AND inv.transaction_type <> 'RETENTION'
126 AND invln.invoice_id = inv.invoice_id
127 AND ft.fee_type = inv.fee_type
128 AND ft.fee_class = cp_v_fee_class
129 AND uoo.uoo_id = invln.uoo_id
130 AND uoo.unit_cd = cp_v_unit_cd
131 AND uoo.version_number = cp_n_unit_ver_num;
132
133
134 CURSOR cur_neg_chg_adj(cp_n_invoice_id igs_fi_inv_int_all.invoice_id%TYPE)
135 IS
136 SELECT appl.amount_applied AS amount_applied
137 FROM igs_fi_applications appl,
138 igs_fi_credits_all crd,
139 igs_fi_cr_types_all cr
140 WHERE appl.invoice_id = cp_n_invoice_id
141 AND appl.application_type = 'APP'
142 AND crd.credit_id = appl.credit_id
143 AND crd.credit_type_id = cr.credit_type_id
144 AND cr.credit_class = 'CHGADJ';
145
146 CURSOR cur_waiver_credit(cp_n_invoice_id IN igs_fi_inv_int_all.invoice_id%TYPE,
147 cp_v_fee_cal_type IN igs_ca_inst_all.cal_type%TYPE,
148 cp_n_fee_seq_number IN igs_ca_inst_all.sequence_number%TYPE)
149 IS
150 SELECT igs_fi_gen_007.get_sum_appl_amnt (appl.application_id) amount_applied , application_id
151 FROM igs_fi_applications appl,
152 igs_fi_credits_all crd,
153 igs_fi_cr_types_all cr
154 WHERE appl.invoice_id = cp_n_invoice_id
155 AND appl.application_type = 'APP'
156 AND crd.credit_id = appl.credit_id
157 AND crd.status = 'CLEARED'
158 AND crd.fee_cal_type = cp_v_fee_cal_type
159 AND crd.fee_ci_sequence_number = cp_n_fee_seq_number
160 AND crd.credit_type_id = cr.credit_type_id
161 AND cr.credit_class = 'WAIVER';
162
163 CURSOR cur_sponsor_credit(cp_n_invoice_id IN igs_fi_inv_int_all.invoice_id%TYPE,
164 cp_v_fee_cal_type IN igs_ca_inst_all.cal_type%TYPE,
165 cp_n_fee_seq_number IN igs_ca_inst.sequence_number%TYPE,
166 cp_v_ld_cal_type IN igs_ca_inst_all.cal_type%TYPE,
167 cp_n_ld_seq_number IN igs_ca_inst.sequence_number%TYPE,
168 cp_v_fund_code IN igf_aw_fund_cat_all.fund_code%TYPE)
169 IS
170 SELECT appl.amount_applied AS amount_applied, appl.credit_id as credit_id
171 FROM igs_fi_applications appl,
172 igs_fi_credits_all crd,
173 igs_fi_cr_types_all cr
174 WHERE appl.invoice_id = cp_n_invoice_id
175 AND appl.application_type = 'APP'
176 AND crd.credit_id = appl.credit_id
177 AND crd.fee_cal_type = cp_v_fee_cal_type
178 AND crd.fee_ci_sequence_number = cp_n_fee_seq_number
179 AND crd.status = 'CLEARED'
180 AND crd.credit_type_id = cr.credit_type_id
181 AND cr.credit_class = 'SPNSP'
182 AND NOT EXISTS (
183 SELECT '1'
184 FROM igf_db_awd_disb_dtl_all disb_dtl
185 , igf_aw_awd_disb_all disb
186 , igf_aw_award_all awd
187 , igf_aw_fund_mast_all fmast
188 WHERE disb_dtl.sf_credit_id = crd.credit_id
189 AND disb.award_id = disb_dtl.award_id
190 AND disb.disb_num = disb_dtl.disb_num
191 AND disb.ld_cal_type = cp_v_ld_cal_type
192 AND disb.ld_sequence_number = cp_n_ld_seq_number
193 AND awd.award_id = disb.award_id
194 AND fmast.fund_id = awd.fund_id
195 AND fmast.fund_code = cp_v_fund_code
196 AND ROWNUM = 1
197 );
198
199
200 -- PL/SQL record for the Invoice Information
201 TYPE invoice_rec IS RECORD(invoice_id igs_fi_inv_int_all.invoice_id%TYPE,
202 invoice_amount igs_fi_inv_int_all.invoice_amount%TYPE );
203 TYPE invoice_table_type IS TABLE OF invoice_rec INDEX BY PLS_INTEGER;
204 l_v_invoice_info invoice_table_type;
205 l_n_invoice_amt igs_fi_inv_int_all.invoice_amount%TYPE;
206 l_n_invoice_id igs_fi_inv_int_all.invoice_id%TYPE;
207 l_n_chgadj_amt igs_fi_inv_int_all.invoice_amount%TYPE;
208 l_n_net_invoice_amt igs_fi_inv_int_all.invoice_amount%TYPE;
209 l_n_wavapp_amt igs_fi_inv_int_all.invoice_amount%TYPE;
210 l_n_spnsp_amt igs_fi_inv_int_all.invoice_amount%TYPE;
211 l_n_eligible_spnsp_amt igs_fi_inv_int_all.invoice_amount%TYPE;
212 l_new_spnsp_amount igs_fi_inv_int_all.invoice_amount%TYPE;
213 l_n_cntr NUMBER;
214 l_v_found VARCHAR(1);
215 BEGIN
216
217 log_to_fnd(p_v_module => 'get_sponsor_amts',
218 p_v_string => ' Entered Procedure get_sponsor_amts: The input parameters are '||
219 ' p_v_fee_cal_type : ' || p_v_fee_cal_type ||
220 ' p_n_fee_seq_number : ' || p_n_fee_seq_number ||
221 ' p_v_fund_code : ' || p_v_fund_code ||
222 ' p_v_ld_cal_type : ' || p_v_ld_cal_type ||
223 ' p_n_ld_seq_number : ' || p_n_ld_seq_number ||
224 ' p_v_fee_class : ' || p_v_fee_class ||
225 ' p_n_person_id : ' || p_n_person_id ||
226 ' p_v_course_cd : ' || p_v_course_cd ||
227 ' p_v_unit_cd : ' || p_v_unit_cd ||
228 ' p_n_unit_ver_num : ' || p_n_unit_ver_num
229 );
230 IF p_v_fee_class IS NULL THEN
231 OPEN cur_get_all_charges(p_n_person_id, p_v_fee_cal_type, p_n_fee_seq_number);
232 FETCH cur_get_all_charges BULK COLLECT INTO l_v_invoice_info;
233 CLOSE cur_get_all_charges;
234 ELSIF p_v_unit_cd IS NOT NULL AND p_n_unit_ver_num IS NOT NULL THEN
235 OPEN cur_get_charges_for_unit(p_n_person_id, p_v_fee_cal_type, p_n_fee_seq_number, p_v_fee_class, p_v_unit_cd, p_n_unit_ver_num);
236 FETCH cur_get_charges_for_unit BULK COLLECT INTO l_v_invoice_info;
237 CLOSE cur_get_charges_for_unit;
238 ELSIF p_v_course_cd IS NOT NULL THEN
239 OPEN cur_get_charges_with_coursecd( p_n_person_id, p_v_fee_cal_type, p_n_fee_seq_number, p_v_fee_class , p_v_course_cd );
240 FETCH cur_get_charges_with_coursecd BULK COLLECT INTO l_v_invoice_info;
241 CLOSE cur_get_charges_with_coursecd;
242 ELSE
243 OPEN cur_get_charges_no_coursecd( p_n_person_id, p_v_fee_cal_type, p_n_fee_seq_number, p_v_fee_class );
244 FETCH cur_get_charges_no_coursecd BULK COLLECT INTO l_v_invoice_info;
245 CLOSE cur_get_charges_no_coursecd;
246 END IF;
247
248 IF l_v_invoice_info.COUNT = 0 THEN
249
250 log_to_fnd(p_v_module => 'get_sponsor_amts',
251 p_v_string => 'Both x_new_spnsp_amount, x_eligible_amount are 0'
252 );
253
254 x_eligible_amount := 0;
255 x_new_spnsp_amount := 0;
256 RETURN;
257 END IF;
258 l_n_eligible_spnsp_amt := 0;
259 l_new_spnsp_amount := 0;
260 FOR l_n_cntr in l_v_invoice_info.FIRST..l_v_invoice_info.LAST
261 LOOP
262 l_n_invoice_amt := l_v_invoice_info(l_n_cntr).invoice_amount;
263 l_n_invoice_id := l_v_invoice_info(l_n_cntr).invoice_id;
264
265 log_to_fnd(p_v_module => 'get_sponsor_amts',
266 p_v_string => 'Context Charge id : '||l_n_invoice_id ||
267 'Context Charge Amount : '||l_n_invoice_amt
268 );
269
270 l_n_chgadj_amt := 0;
271
272 FOR rec_neg_chg_adj IN cur_neg_chg_adj( l_n_invoice_id )
273 LOOP
274 l_n_chgadj_amt := l_n_chgadj_amt + rec_neg_chg_adj.amount_applied;
275 END LOOP;
276 log_to_fnd(p_v_module => 'get_sponsor_amts',
277 p_v_string => 'Sum of -ve Charge Adj. Credits : ' || l_n_chgadj_amt );
278
279 l_n_net_invoice_amt := l_n_invoice_amt - l_n_chgadj_amt;
280 l_n_wavapp_amt := 0;
281
282 FOR rec_waiver_credit IN cur_waiver_credit( l_n_invoice_id , p_v_fee_cal_type, p_n_fee_seq_number)
283 LOOP
284 l_n_wavapp_amt := l_n_wavapp_amt + rec_waiver_credit.amount_applied;
285 END LOOP;
286 log_to_fnd(p_v_module => 'get_sponsor_amts',
287 p_v_string => 'Sum of Waiver Credits : ' || l_n_wavapp_amt );
288 l_n_spnsp_amt := 0;
289
290 FOR rec_sponsor_credit IN cur_sponsor_credit( l_n_invoice_id , p_v_fee_cal_type, p_n_fee_seq_number, p_v_ld_cal_type, p_n_ld_seq_number, p_v_fund_code)
291 LOOP
292 l_n_spnsp_amt := l_n_spnsp_amt + rec_sponsor_credit.amount_applied;
293 END LOOP;
294 log_to_fnd(p_v_module => 'get_sponsor_amts',
295 p_v_string => 'Sum of Sponsorship Credits : ' || l_n_wavapp_amt );
296
297
298 l_new_spnsp_amount := l_new_spnsp_amount + ( l_n_net_invoice_amt - l_n_wavapp_amt );
299
300 l_n_eligible_spnsp_amt := l_n_eligible_spnsp_amt + (l_n_net_invoice_amt - l_n_wavapp_amt - l_n_spnsp_amt);
301
302
303 END LOOP;
304 log_to_fnd(p_v_module => 'get_sponsor_amts',
305 p_v_string => 'New Sponsorship amount : ' || l_new_spnsp_amount );
306
307 log_to_fnd(p_v_module => 'get_sponsor_amts',
308 p_v_string => 'Eligible Sponsorship amount : ' || l_n_eligible_spnsp_amt );
309
310 x_new_spnsp_amount := l_new_spnsp_amount;
311 x_eligible_amount := l_n_eligible_spnsp_amt;
312
313 END get_sponsor_amts;
314
315
316 FUNCTION check_unit_attempt
317 (p_person_id IN igs_pe_person.person_id%TYPE,
318 p_ld_cal_type IN igs_ca_inst.cal_type%TYPE,
319 p_ld_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE,
320 p_course_cd IN igs_ps_ver.course_cd%TYPE,
321 p_course_version_number IN igs_ps_ver.version_number%TYPE,
322 p_unit_cd IN igs_ps_unit_ver.unit_cd%TYPE,
323 p_unit_version_number IN igs_ps_unit_ver.version_number%TYPE,
324 p_msg_count OUT NOCOPY NUMBER,
325 p_msg_data OUT NOCOPY VARCHAR2)
326 RETURN BOOLEAN
327 AS
328 -----------------------------------------------------------------------------------
329 --Created by : smanglm ( Oracle IDC)
330 --Date created: 2002/01/11
331 --
332 --Purpose: Created as part of the build for DLD Sponsorship
333 -- check_unit_attempt: checks whether the student is actively enrolled in
334 -- the given unit or not.
335 --
336 --Known limitations/enhancements and/or remarks:
337 --
338 --Change History:
339 --Who When What
340 -------------------------------------------------------------------------------------
341 /*
342 cursor to see whether the student is actively enrolled in the unit or not
343 */
344 CURSOR c_enroll (cp_person_id igs_pe_person.person_id%TYPE,
345 cp_course_cd igs_en_su_attempt.course_cd%TYPE,
346 cp_unit_cd igs_en_su_attempt.unit_cd%TYPE,
347 cp_unit_version_number igs_en_su_attempt.version_number%TYPE,
348 cp_ld_cal_type igs_ca_inst.cal_type%TYPE,
349 cp_ld_ci_sequence_number igs_ca_inst.sequence_number%TYPE) IS
350 SELECT 'x'
351 FROM igs_en_su_attempt
352 WHERE person_id = cp_person_id
353 AND course_cd = cp_course_cd
354 AND unit_attempt_status IN ('ENROLLED', 'COMPLETED')
355 AND unit_cd = cp_unit_cd
356 AND version_number = cp_unit_version_number
357 AND (cal_type, ci_sequence_number) IN (SELECT teach_cal_type, teach_ci_sequence_number
358 FROM igs_ca_load_to_teach_v
359 WHERE load_cal_type = cp_ld_cal_type
360 AND load_ci_sequence_number = cp_ld_ci_sequence_number);
361 l_enroll c_enroll%ROWTYPE;
362 BEGIN
363 /*
364 Check whether the student is actively enrolled in the unit or not.
365 Always unit is attached to a teaching period.
366 So check whether is teaching period falls under the given load calendar.
367 */
368 OPEN c_enroll (p_person_id,
369 p_course_cd,
370 p_unit_cd,
371 p_unit_version_number,
372 p_ld_cal_type,
373 p_ld_ci_sequence_number);
374 FETCH c_enroll INTO l_enroll;
375 IF c_enroll%NOTFOUND THEN
376 p_msg_count := NVL(p_msg_count,0) + 1;
377 p_msg_data := 'IGF_SP_SUA_NOT_ELGB';
378 CLOSE c_enroll;
379 RETURN FALSE;
380 ELSE
381 CLOSE c_enroll;
382 RETURN TRUE;
383 END IF;
384
385 END check_unit_attempt;
386
387 PROCEDURE log_to_fnd (
388 p_v_module IN VARCHAR2,
389 p_v_string IN VARCHAR2
390 ) AS
391 ------------------------------------------------------------------
392 --Created by : svuppala, Oracle IDC
393 --Date created: 16-May-2006
394 --
395 -- Purpose:
396 -- Invoked : from within API
397 -- Function : Private procedure for logging all the statement level
398 -- messages
399 -- Parameters : p_v_module : IN parameter. Required.
400 -- p_v_string : IN parameter. Required.
401 --
402 --
403 --Known limitations/enhancements and/or remarks:
404 --
405 --Change History:
406 --Who When What
407 ------------------------------------------------------------------
408 BEGIN
409
410 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
411 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_sp_gen_001.'||p_v_module, p_v_string);
412 END IF;
413 END log_to_fnd;
414
415
416 END igf_sp_gen_001;