1 PACKAGE BODY igs_fi_gen_refunds AS
2 /* $Header: IGSFI68B.pls 120.1 2006/02/13 04:16:14 sapanigr noship $ */
3
4 /*************************************************************
5 Created By : vchappid
6 Date Created By : 01-Mar-2002
7 Purpose : Refunds General Package, Functions and Procedures used in the Refunds Build
8 Change History
9 Who When What
10 sapanigr 13-Feb-2006 Bug# 5018036. Modified cur_fund_auth in check_fund_auth for R12 SQL repository perf tuning.
11 shtatiko 24-Sep-2002 Bug# 2564643, Removed Sub Account References
12 agairola 30-Apr-2002 Modified the get_fee_prd for the bug 2348883
13 (reverse chronological order - newest change first)
14 ***************************************************************/
15
16 g_active CONSTANT VARCHAR2(10) := 'ACTIVE';
17 g_refund CONSTANT VARCHAR2(10) := 'REFUND';
18 g_stoprefund CONSTANT VARCHAR2(15) := 'STOPREFUND';
19 g_borrower CONSTANT VARCHAR2(10) := 'BORROWER';
20
21 FUNCTION check_fund_auth(p_person_id igs_fi_parties_v.person_id%TYPE) RETURN BOOLEAN AS
22
23 /*************************************************************
24 Created By : vchappid
25 Date Created By : 01-Mar-2002
26 Purpose : Identifies whether the Fund Autorization is set for the person
27 Know limitations, enhancements or remarks
28 Change History
29 Who When What
30 sapanigr 13-Feb-2006 Bug 5018036. Modified cur_fund_auth to query from igs_pe_hz_parties instead of igs_fi_parties_v
31 (reverse chronological order - newest change first)
32 ***************************************************************/
33
34 CURSOR cur_fund_auth (cp_person_id igs_pe_hz_parties.party_id%TYPE) IS
35 SELECT NVL(fund_authorization,'N') fund_authorization
36 FROM igs_pe_hz_parties
37 WHERE party_id = cp_person_id;
38 l_fund_auth igs_fi_parties_v.fund_authorization%TYPE;
39 BEGIN
40 -- For the person in context get the fund authorization is enabled or not
41 -- if no record is found then the function returns FALSE else returns TRUE
42 OPEN cur_fund_auth(p_person_id);
43 FETCH cur_fund_auth INTO l_fund_auth;
44 IF cur_fund_auth%NOTFOUND THEN
45 RETURN FALSE;
46 ELSE
47 IF l_fund_auth = 'Y' THEN
48 RETURN TRUE;
49 ELSE
50 RETURN FALSE;
51 END IF;
52 END IF;
53 END check_fund_auth;
54
55
56 PROCEDURE get_fee_prd( p_fee_type OUT NOCOPY igs_fi_fee_type.fee_type%TYPE,
57 p_fee_cal_type IN OUT NOCOPY igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
58 p_fee_ci_sequence_number IN OUT NOCOPY igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE,
59 p_status OUT NOCOPY BOOLEAN) AS
60 /*************************************************************
61 Created By : vchappid
62 Date Created By : 01-Mar-2002
63 Purpose : Gets the Fee Type for the given Sub Account and Fee Period
64 Know limitations, enhancements or remarks
65 Change History
66 Who When What
67 shtatiko 24-Sep-2002 Bug# 3564643, Removed Subaccount_id from the parameter list and modified the code accordingly.
68 agairola 30-Apr-2002 Added the Fee Structure Status check in cur_fee_type and cur_ftyp_no_ftci for 2348883
69 (reverse chronological order - newest change first)
70 ***************************************************************/
71
72
73 CURSOR cur_fee_type ( cp_fee_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
74 cp_fee_ci_sequence_number igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE)
75 IS
76 SELECT ft.fee_type
77 FROM igs_fi_f_typ_ca_inst ftci,
78 igs_fi_fee_type ft,
79 igs_fi_fee_str_stat fsst
80 WHERE ft.fee_type = ftci.fee_type
81 AND ftci.fee_cal_type = p_fee_cal_type
82 AND ftci.fee_ci_sequence_number = p_fee_ci_sequence_number
83 AND ftci.fee_type_ci_status = fsst.fee_structure_status
84 AND fsst.s_fee_structure_status = g_active
85 AND ft.s_fee_type = g_refund
86 AND NVL(ft.closed_ind,'N') = 'N';
87
88 l_cur_fee_type igs_fi_fee_type.fee_type%TYPE;
89
90 CURSOR cur_ftyp_no_ftci
91 IS
92 SELECT ftci.fee_type,
93 ci.cal_type,
94 ci.sequence_number
95 FROM igs_fi_fee_type ft,
96 igs_fi_f_typ_ca_inst ftci,
97 igs_ca_inst ci,
98 igs_fi_fee_str_stat fsst
99 WHERE ft.fee_type = ftci.fee_type
100 AND ft.s_fee_type = g_refund
101 AND ci.cal_type = ftci.fee_cal_type
102 AND ci.sequence_number = ftci.fee_ci_sequence_number
103 AND ftci.fee_type_ci_status = fsst.fee_structure_status
104 AND fsst.s_fee_structure_status = g_active
105 AND (TRUNC(SYSDATE) BETWEEN TRUNC(ci.start_dt) AND TRUNC(NVL(ci.end_dt,SYSDATE)))
106 AND NVL(ft.closed_ind,'N') = 'N'
107 ORDER BY ci.start_dt, ci.sequence_number ASC;
108 l_cur_ftyp_no_ftci cur_ftyp_no_ftci%ROWTYPE;
109
110 BEGIN
111 -- If the Fee Period is passed then for Fee Period Fee Type is identified
112 IF ( p_fee_cal_type IS NOT NULL AND p_fee_ci_sequence_number IS NOT NULL) THEN
113 OPEN cur_fee_type( p_fee_cal_type, p_fee_ci_sequence_number);
114 FETCH cur_fee_type INTO l_cur_fee_type;
115 IF cur_fee_type%NOTFOUND THEN
116 p_status := FALSE;
117 p_fee_type := NULL;
118 CLOSE cur_fee_type;
119 RETURN;
120 END IF;
121 p_status :=TRUE;
122 p_fee_type := l_cur_fee_type;
123 CLOSE cur_fee_type;
124 ELSE
125 -- If the Fee Period is not passed then for the latest Fee Calendar Instance, Fee Type is identified
126 OPEN cur_ftyp_no_ftci;
127 FETCH cur_ftyp_no_ftci INTO l_cur_ftyp_no_ftci;
128 IF cur_ftyp_no_ftci%NOTFOUND THEN
129 p_status := FALSE;
130 p_fee_type := NULL;
131 p_fee_cal_type := NULL;
132 p_fee_ci_sequence_number := NULL;
133 CLOSE cur_ftyp_no_ftci;
134 RETURN;
135 END IF;
136 p_status :=TRUE;
137 p_fee_type := l_cur_ftyp_no_ftci.fee_type;
138 p_fee_cal_type := l_cur_ftyp_no_ftci.cal_type;
139 p_fee_ci_sequence_number := l_cur_ftyp_no_ftci.sequence_number;
140 CLOSE cur_ftyp_no_ftci;
141 END IF;
142 END get_fee_prd;
143
144 FUNCTION get_rfnd_hold (p_person_id igs_pe_person.person_id%TYPE) RETURN BOOLEAN IS
145 /*************************************************************
146 Created By : vchappid
147 Date Created By : 01-Mar-2002
148 Purpose : Function to determine whether a person is having active STOPREFUND hold effects hold
149 Know limitations, enhancements or remarks
150 Change History
151 Who When What
152 (reverse chronological order - newest change first)
153 ***************************************************************/
154
155 CURSOR cur_hold_check (cp_person_id igs_pe_person.person_id%TYPE)
156 IS
157 SELECT 'X'
158 FROM igs_pe_persenc_effct
159 WHERE person_id = cp_person_id
160 AND s_encmb_effect_type = g_stoprefund
161 AND TRUNC(NVL(expiry_dt,SYSDATE)) >= TRUNC(SYSDATE);
162
163 l_temp VARCHAR2(1);
164 BEGIN
165 -- Returns FALSE when the Person Id is not input
166 IF ( p_person_id IS NULL) THEN
167 RETURN FALSE;
168 END IF;
169
170 -- Returns FALSE when there is no hold effect of STOPREFUND else returns TRUE
171 OPEN cur_hold_check(p_person_id);
172 FETCH cur_hold_check INTO l_temp;
173 IF cur_hold_check%NOTFOUND THEN
174 CLOSE cur_hold_check;
175 RETURN FALSE;
176 ELSE
177 CLOSE cur_hold_check;
178 RETURN TRUE;
179 END IF;
180 END get_rfnd_hold;
181
182 FUNCTION val_add_drop (p_fee_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
183 p_fee_ci_sequence_number igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE) RETURN BOOLEAN AS
184 /*************************************************************
185 Created By : vchappid
186 Date Created By : 01-Mar-2002
187 Purpose : Function to determine the Refund Date Alias is less than the Sysdate
188 Know limitations, enhancements or remarks
189 Change History
190 Who When What
191 (reverse chronological order - newest change first)
192 ***************************************************************/
193
194 CURSOR cur_control
195 IS
196 SELECT refund_dt_alias
197 FROM igs_fi_control;
198 l_refund_dt_alias igs_ca_da_inst.dt_alias%TYPE;
199
200 CURSOR cur_alias_val (cp_fee_cal_type igs_ca_inst.cal_type%TYPE,
201 cp_fee_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
202 cp_date_alias igs_ca_da_inst.dt_alias%TYPE)
203 IS
204 SELECT alias_val
205 FROM igs_ca_da_inst_v
206 WHERE cal_type = cp_fee_cal_type
207 AND ci_sequence_number = cp_fee_ci_sequence_number
208 AND dt_alias = cp_date_alias
209 ORDER BY alias_val DESC;
210 l_refund_dt_val igs_ca_da_inst_v.alias_val%TYPE;
211
212 BEGIN
213 -- If the mandatory parameters are not passed then the function returnd FALSE
214 IF (p_fee_cal_type IS NULL OR p_fee_ci_sequence_number IS NULL) THEN
215 RETURN FALSE;
216 END IF;
217
218 -- Identify the refund date alias value in the System Setup form
219 -- when no data is setup then the function returns FALSE
220 OPEN cur_control;
221 FETCH cur_control INTO l_refund_dt_alias;
222 IF cur_control%NOTFOUND THEN
223 CLOSE cur_control;
224 RETURN FALSE;
225 END IF;
226 CLOSE cur_control;
227
228 -- If the Identified refund date alias value in the System Setup form
229 -- is null then the function returns FALSE
230 IF (l_refund_dt_alias IS NULL) THEN
231 RETURN FALSE;
232 END IF;
233
234 -- Get the Alias Value from the igs_ca_da_inst_v view, returns FALSE when no data found
235 OPEN cur_alias_val(p_fee_cal_type, p_fee_ci_sequence_number, l_refund_dt_alias);
236 FETCH cur_alias_val INTO l_refund_dt_val;
237 IF cur_alias_val%NOTFOUND THEN
238 CLOSE cur_alias_val;
239 RETURN FALSE;
240 END IF;
241 CLOSE cur_alias_val;
242
243 -- If the Alias Value is Less Than the current Date then the function will return FALSE
244 -- else TRUE is returned
245 IF (l_refund_dt_val < SYSDATE) THEN
246 RETURN FALSE;
247 ELSE
248 RETURN TRUE;
249 END IF;
250 END val_add_drop;
251
252 PROCEDURE get_borw_det (p_credit_id igs_fi_credits.credit_id%TYPE,
253 p_determination OUT NOCOPY igs_lookups_view.lookup_code%TYPE,
254 p_err_message OUT NOCOPY fnd_new_messages.message_name%TYPE,
255 p_status OUT NOCOPY BOOLEAN) AS
256 /*************************************************************
257 Created By : vchappid
258 Date Created By : 01-Mar-2002
259 Purpose : Procedure will determine the Borrower for the input Credit Id
260 Know limitations, enhancements or remarks
261 Change History
262 Who When What
263 (reverse chronological order - newest change first)
264 ***************************************************************/
265
266 CURSOR cur_award( cp_credit_id igs_fi_credits.credit_id%TYPE)
267 IS
268 SELECT award_id
269 FROM igf_db_awd_disb_dtl
270 WHERE sf_credit_id = cp_credit_id;
271 l_cur_award cur_award%ROWTYPE;
272
273 CURSOR cur_borrower (cp_award_id igf_aw_award.award_id%TYPE)
274 IS
275 SELECT NVL(borw_detrm_code,g_borrower) borw_detrm_code
276 FROM igf_sl_loans
277 WHERE award_id = cp_award_id;
278 l_cur_borrower cur_borrower%ROWTYPE;
279
280 BEGIN
281 -- If the mandatory parameter credit id is not passed then return setting
282 -- NULL to the OUT NOCOPY variables
283 IF (p_credit_id IS NULL ) THEN
284 p_determination := NULL;
285 p_err_message := NULL;
286 p_status := FALSE;
287 RETURN;
288 END IF;
289
290 -- For the input credit id, get the award id from the Disbursment Details Table, return message if no
291 -- data is found
292 OPEN cur_award( p_credit_id);
293 FETCH cur_award INTO l_cur_award;
294 IF cur_award%NOTFOUND THEN
295 p_determination := NULL;
296 p_err_message := 'IGS_FI_RFND_INVDATA';
297 p_status := FALSE;
298 CLOSE cur_award;
299 RETURN;
300 END IF;
301 CLOSE cur_award;
302
303 -- for the award id get the borrower from the loans table
304 -- when the column value is NULL the default BORROWER is
305 -- returned as the Borrower code
306 OPEN cur_borrower( l_cur_award.award_id);
307 FETCH cur_borrower INTO l_cur_borrower;
308 IF cur_borrower%NOTFOUND THEN
309 p_determination := NULL;
310 p_err_message := NULL;
311 p_status := FALSE;
312 CLOSE cur_borrower;
313 RETURN;
314 END IF;
315 p_determination := l_cur_borrower.borw_detrm_code;
316 p_err_message := NULL;
317 p_status := TRUE;
318 CLOSE cur_borrower;
319
320 END get_borw_det;
321
322 PROCEDURE get_refund_acc ( p_dr_gl_ccid OUT NOCOPY igs_fi_f_typ_ca_inst.rec_gl_ccid%TYPE,
323 p_dr_account_cd OUT NOCOPY igs_fi_f_typ_ca_inst.rec_account_cd%TYPE,
324 p_cr_gl_ccid OUT NOCOPY igs_fi_f_typ_ca_inst.rec_gl_ccid%TYPE,
325 p_cr_account_cd OUT NOCOPY igs_fi_f_typ_ca_inst.rec_account_cd%TYPE,
326 p_err_message OUT NOCOPY fnd_new_messages.message_name%TYPE,
327 p_status OUT NOCOPY BOOLEAN) AS
328 /*************************************************************
329 Created By : vchappid
330 Date Created By : 01-Mar-2002
331 Purpose : Get the Refunds Account Codes setup at the System Options Form
332 depending whether AR is installed or not
333 Know limitations, enhancements or remarks
334 Change History
335 Who When What
336 (reverse chronological order - newest change first)
337 ***************************************************************/
338
339
340 CURSOR cur_refund
341 IS
342 SELECT refund_dr_gl_ccid,
343 refund_cr_gl_ccid,
344 refund_dr_account_cd,
345 refund_cr_account_cd,
346 NVL(rec_installed,'N') rec_installed
347 FROM igs_fi_control;
348
349 l_cur_refund cur_refund%ROWTYPE;
350
351 BEGIN
352
353 -- If there is no record in the igs_fi_control table then the procedure will return
354 -- an error and assigns NULL to the OUT NOCOPY parameters
355 OPEN cur_refund;
356 FETCH cur_refund INTO l_cur_refund;
357 IF cur_refund%NOTFOUND THEN
358 p_dr_gl_ccid := NULL;
359 p_dr_account_cd := NULL;
360 p_cr_gl_ccid := NULL;
361 p_cr_account_cd := NULL;
362 p_err_message := 'IGS_FI_REFUND_ACC_ERR';
363 p_status:= FALSE;
364 RETURN;
365 END IF;
366 CLOSE cur_refund;
367
368 -- If AR is installed then the CCID columns will be assigned to the Out NOCOPY Parameters
369 -- else Account codes are assigned to the OUT NOCOPY parameters
370 IF (l_cur_refund.rec_installed ='Y') THEN
371 IF (l_cur_refund.refund_dr_gl_ccid IS NULL OR l_cur_refund.refund_cr_gl_ccid IS NULL) THEN
372 p_dr_gl_ccid := NULL;
373 p_dr_account_cd := NULL;
374 p_cr_gl_ccid := NULL;
375 p_cr_account_cd := NULL;
376 p_err_message := 'IGS_FI_REFUND_ACC_ERR';
377 p_status:= FALSE;
378 RETURN;
379 ELSE
380 p_dr_gl_ccid := l_cur_refund.refund_dr_gl_ccid;
381 p_dr_account_cd := NULL;
382 p_cr_gl_ccid := l_cur_refund.refund_cr_gl_ccid;
383 p_cr_account_cd := NULL;
384 p_err_message := NULL;
385 p_status:= TRUE;
386 RETURN;
387 END IF;
388 ELSE
389 IF (l_cur_refund.refund_dr_account_cd IS NULL OR l_cur_refund.refund_cr_account_cd IS NULL) THEN
390 p_dr_gl_ccid := NULL;
391 p_dr_account_cd := NULL;
392 p_cr_gl_ccid := NULL;
393 p_cr_account_cd := NULL;
394 p_err_message := 'IGS_FI_REFUND_ACC_ERR';
395 p_status:= FALSE;
396 RETURN;
397 ELSE
398 p_dr_gl_ccid := NULL;
399 p_dr_account_cd := l_cur_refund.refund_dr_account_cd;
400 p_cr_gl_ccid := NULL;
401 p_cr_account_cd := l_cur_refund.refund_cr_account_cd;
402 p_err_message := NULL;
403 p_status:= TRUE;
404 RETURN;
405 END IF;
406 END IF;
407 END get_refund_acc;
408 END igs_fi_gen_refunds;
[Home] [Help]