1 PACKAGE BODY igs_fi_crdapi_util AS
2 /* $Header: IGSFI84B.pls 120.5 2006/02/10 04:43:13 sapanigr ship $ */
3
4 /*
5 Procedure VALIDATE_PARAMETERS is the main procedure which has calls to
6 individual procedures or functions. When this procedure is invoked with FULL
7 validation level then all parameter validations will take place.
8 */
9 /***********************************************************************************************
10 Created By :
11 Date Created By:
12 Purpose :
13
14 Known limitations,enhancements,remarks:
15 Change History
16 Who When What
17 sapanigr 12-Feb-2006 Bug#5018036 - Modified cursor c_valid_person in validate_party_id procedure. (R12 SQL Repository tuning)
18 sapanigr 22-NOV-2005 Bug#4675424. Added function val_cal_inst
19 svuppala 07-JUL-2005 Enh 3392095 - Tution Waivers build
20 Modified validations of credit class and credit instrument in Validate_parameters.
21 svuppala 9-JUN-2005 Enh 4213629 - The automatic generation of the Receipt Number.
22 Removed the validation to check if Credit Number is passed
23 if the combination of party_id and credit_number already exists in the system.
24 vvutukur 13-Sep-2003 Enh#3045007.Payment Plans Build. Added procedures apply_installments,validate_plan_balance.
25 vvutukur 14-Jul-2003 Enh#3038511.FICR106 Build. Added procedure get_award_year_status,modified validate_parameters.
26 vvutukur 18-Jun-2003 Enh#2831582.Lockbox Build. Removed the function validate_lockbox and related call to the same.
27 ********************************************************************************************** */
28
29 PROCEDURE get_award_year_status(p_v_awd_cal_type IN VARCHAR2,
30 p_n_awd_seq_number IN PLS_INTEGER,
31 p_v_awd_yr_status OUT NOCOPY VARCHAR2,
32 p_v_message_name OUT NOCOPY VARCHAR2) AS
33
34 /***********************************************************************************************
35 Created By : vvutukur, Oracle India
36 Date Created By: 15-Jul-2003
37 Purpose : This procedure is meant for getting the award year status code
38 for the specified award year.
39
40 Known limitations,enhancements,remarks:
41 Change History
42 Who When What
43 ********************************************************************************************** */
44 --Cursor to fetch the award year status code for the specified award year.
45 CURSOR cur_awd_yr_status(cp_v_cal_type VARCHAR2,
46 cp_n_ci_seq_number NUMBER) IS
47 SELECT award_year_status_code
48 FROM igf_ap_batch_aw_map
49 WHERE ci_cal_type = cp_v_cal_type
50 AND ci_sequence_number = cp_n_ci_seq_number;
51
52 l_v_awd_yr_status_cd igf_ap_batch_aw_map.award_year_status_code%TYPE;
53
54 BEGIN
55
56 --Both input parameters are mandatory to this procedure, if either of them is null, return from the procedure
57 --assigning false to return status and null to award year status.
58
59 IF p_v_awd_cal_type IS NULL OR p_n_awd_seq_number IS NULL THEN
60 p_v_message_name := 'IGS_FI_INV_AWD_YR';
61 p_v_awd_yr_status := NULL;
62 RETURN;
63 END IF;
64
65 --if both the input parameters passed are passed as not null, then..
66 --Fetch the award year status.
67 OPEN cur_awd_yr_status(p_v_awd_cal_type,p_n_awd_seq_number);
68 FETCH cur_awd_yr_status INTO l_v_awd_yr_status_cd;
69
70 --If no matching award year status found for the input parameters passed, then
71 IF cur_awd_yr_status%NOTFOUND THEN
72 --Return from the procedure assigning false to return status and null to award year status.
73 CLOSE cur_awd_yr_status;
74 p_v_message_name := 'IGS_FI_INV_AWD_YR';
75 p_v_awd_yr_status := NULL;
76 RETURN;
77 END IF;
78
79 CLOSE cur_awd_yr_status;
80 p_v_awd_yr_status := l_v_awd_yr_status_cd;
81
82 IF p_v_awd_yr_status <> 'O' THEN
83 p_v_message_name := 'IGF_SP_INVALID_AWD_YR_STATUS';
84 ELSE
85 p_v_message_name := NULL;
86 END IF;
87
88 END get_award_year_status;
89
90 PROCEDURE validate_plan_balance(p_n_person_id IN PLS_INTEGER,
91 p_n_amount IN NUMBER,
92 p_b_status OUT NOCOPY BOOLEAN,
93 p_v_message_name OUT NOCOPY VARCHAR2
94 ) AS
95 /***********************************************************************************************
96 Created By : vvutukur, Oracle India
97 Date Created By: 13-Sep-2003
98 Purpose : Procedure to verify if the Installment balance for the person is
99 greater than or equal to the amount of the receipt that is being created.
100
101 Known limitations,enhancements,remarks:
102 Change History
103 Who When What
104 ********************************************************************************************** */
105
106 l_n_act_plan_id igs_fi_pp_std_attrs.student_plan_id%TYPE;
107 l_v_act_plan_name igs_fi_pp_std_attrs.payment_plan_name%TYPE;
108 l_n_pln_bal NUMBER;
109
110 BEGIN
111 --Get the Student's Active Payment Plan's details.
112 igs_fi_gen_008.get_plan_details(p_n_person_id => p_n_person_id,
113 p_n_act_plan_id => l_n_act_plan_id,
114 p_v_act_plan_name => l_v_act_plan_name);
115
116 --If there is no active Payment Plan for the student, return from the procedure
117 --by assigning proper error message and return status into the out parameters.
118 IF l_v_act_plan_name IS NULL THEN
119 p_b_status := FALSE;
120 p_v_message_name := 'IGS_FI_PP_PRSN_NOT_ACTIVE_PP';
121 RETURN;
122 END IF;
123
124 --Get the Student's Active Payment Plan Balance.
125 l_n_pln_bal := igs_fi_gen_008.get_plan_balance(p_n_act_plan_id => l_n_act_plan_id,
126 p_d_effective_date => NULL
127 );
128 --If the payment plan balance is less than the amount passed, return from the procedure
129 --by assigning proper error message and return status into the out parameters.
130 IF NVL(l_n_pln_bal,0) < NVL(p_n_amount,0) THEN
131 p_b_status := FALSE;
132 p_v_message_name := 'IGS_FI_PP_MORE_AMOUNT';
133 RETURN;
134 END IF;
135
136 --if above validations are passed, return from the procedure with true status and message name being null.
137 p_b_status := TRUE;
138 p_v_message_name := NULL;
139
140 END validate_plan_balance;
141
142 PROCEDURE apply_installments(p_n_person_id IN NUMBER,
143 p_n_amount IN NUMBER,
144 p_n_credit_id IN igs_fi_credits.credit_id%TYPE,
145 p_n_cr_activity_id IN igs_fi_cr_activities.credit_activity_id%TYPE) AS
146 /***********************************************************************************************
147 Created By : vvutukur, Oracle India
148 Date Created By: 13-Sep-2003
149 Purpose : Procedure to apply an Installment Payment against the student's Active
150 Payment Plans installments in the FIFO basis.
151 Known limitations,enhancements,remarks:
152 Change History
153 Who When What
154 ********************************************************************************************** */
155 --Cursor to fetch the details of Student's Active Payment Plan installments.
156 CURSOR cur_pp_insts(cp_n_person_id NUMBER,
157 cp_v_plan_status VARCHAR2) IS
158 SELECT inst.rowid row_id, inst.*
159 FROM igs_fi_pp_instlmnts inst,
160 igs_fi_pp_std_attrs stt
161 WHERE stt.student_plan_id = inst.student_plan_id AND
162 stt.person_id = cp_n_person_id AND
163 stt.plan_status_code = cp_v_plan_status AND
164 inst.due_amt > 0
165 ORDER BY inst.due_date;
166
167 rec_cur_pp_insts cur_pp_insts%ROWTYPE;
168
169 --Cursor to fetch the details of Student's Active Payment Plan record.
170 CURSOR cur_pmt_plan(cp_n_person_id NUMBER,
171 cp_v_plan_status VARCHAR2) IS
172 SELECT stt.rowid row_id,stt.*
173 FROM igs_fi_pp_std_attrs stt
174 WHERE stt.person_id = cp_n_person_id AND
175 stt.plan_status_code = cp_v_plan_status;
176
177 rec_cur_pmt_plan cur_pmt_plan%ROWTYPE;
178
179
180 l_n_credit_amt igs_fi_credits.amount%TYPE;
181 l_n_amt_apply igs_fi_credits.amount%TYPE;
182 l_n_amt_due igs_fi_credits.amount%TYPE;
183 l_rowid ROWID;
184 l_n_inst_appl_id igs_fi_pp_ins_appls.installment_application_id%TYPE;
185
186 BEGIN
187
188 l_n_credit_amt := p_n_amount;
189
190 --Fetch the Student's Active Payment Plan Installment record details and loop through each one of them.
191 OPEN cur_pp_insts(p_n_person_id,'ACTIVE');
192 LOOP
193 FETCH cur_pp_insts INTO rec_cur_pp_insts;
194
195 --Calculate the amount to be applied and due amount for each Active Payment Plan Installment record
196 --to be updated.
197 IF NVL(rec_cur_pp_insts.due_amt,0) >= NVL(l_n_credit_amt,0) THEN
198 l_n_amt_apply := l_n_credit_amt;
199 l_n_amt_due := rec_cur_pp_insts.due_amt - l_n_credit_amt;
200 l_n_credit_amt := 0;
201 ELSE
202 l_n_amt_apply := rec_cur_pp_insts.due_amt;
203 l_n_amt_due := 0;
204 l_n_credit_amt := l_n_credit_amt - rec_cur_pp_insts.due_amt;
205 END IF;
206
207 --Update the due_amt column of igs_fi_pp_instlmnts table with outstanding due amt.
208 igs_fi_pp_instlmnts_pkg.update_row(
209 x_rowid => rec_cur_pp_insts.row_id,
210 x_installment_id => rec_cur_pp_insts.installment_id,
211 x_student_plan_id => rec_cur_pp_insts.student_plan_id,
212 x_installment_line_num => rec_cur_pp_insts.installment_line_num,
213 x_due_day => rec_cur_pp_insts.due_day,
214 x_due_month_code => rec_cur_pp_insts.due_month_code,
215 x_due_year => rec_cur_pp_insts.due_year,
216 x_due_date => rec_cur_pp_insts.due_date,
217 x_installment_amt => rec_cur_pp_insts.installment_amt,
218 x_due_amt => l_n_amt_due,
219 x_penalty_flag => rec_cur_pp_insts.penalty_flag,
220 x_mode => 'R'
221 );
222
223 --Create an installment application record in igs_fi_pp_ins_appls table with appropriate amount applied.
224 --and application type code as 'APP'.
225 l_rowid := NULL;
226 l_n_inst_appl_id := NULL;
227
228 igs_fi_pp_ins_appls_pkg.insert_row(
229 x_rowid => l_rowid,
230 x_installment_application_id => l_n_inst_appl_id,
231 x_application_type_code => 'APP',
232 x_installment_id => rec_cur_pp_insts.installment_id,
233 x_credit_id => p_n_credit_id,
234 x_credit_activity_id => p_n_cr_activity_id,
235 x_applied_amt => l_n_amt_apply,
236 x_transaction_date => TRUNC(SYSDATE),
237 x_link_application_id => NULL,
238 x_mode => 'R'
239 );
240
241 --if the transaction amount become 0, terminate the looping Student's Active Payment Plan installment records.
242 IF l_n_credit_amt = 0 THEN
243 EXIT;
244 END IF;
245
246 END LOOP;
247 CLOSE cur_pp_insts;
248
249 --If the person has paid-off all his installments, then his active payment plan needs to be closed.
250 IF igs_fi_gen_008.get_plan_balance(rec_cur_pp_insts.student_plan_id,NULL) = 0 THEN
251
252 --Fetch the Student Active Payment Plan's details.
253 OPEN cur_pmt_plan(p_n_person_id,'ACTIVE');
254 FETCH cur_pmt_plan INTO rec_cur_pmt_plan;
255 CLOSE cur_pmt_plan;
256
257 --Close the Payment Plan specifying the System Date as plan end date.
258 igs_fi_pp_std_attrs_pkg.update_row(
259 x_rowid => rec_cur_pmt_plan.row_id,
260 x_student_plan_id => rec_cur_pmt_plan.student_plan_id,
261 x_person_id => rec_cur_pmt_plan.person_id,
262 x_payment_plan_name => rec_cur_pmt_plan.payment_plan_name,
263 x_plan_start_date => rec_cur_pmt_plan.plan_start_date,
264 x_plan_end_date => TRUNC(SYSDATE),
265 x_plan_status_code => 'CLOSED',
266 x_processing_fee_amt => rec_cur_pmt_plan.processing_fee_amt,
267 x_processing_fee_type => rec_cur_pmt_plan.processing_fee_type,
268 x_fee_cal_type => rec_cur_pmt_plan.fee_cal_type,
269 x_fee_ci_sequence_number => rec_cur_pmt_plan.fee_ci_sequence_number,
270 x_notes => rec_cur_pmt_plan.notes,
271 x_invoice_id => rec_cur_pmt_plan.invoice_id,
272 x_attribute_category => rec_cur_pmt_plan.attribute_category,
273 x_attribute1 => rec_cur_pmt_plan.attribute1,
274 x_attribute2 => rec_cur_pmt_plan.attribute2,
275 x_attribute3 => rec_cur_pmt_plan.attribute3,
276 x_attribute4 => rec_cur_pmt_plan.attribute4,
277 x_attribute5 => rec_cur_pmt_plan.attribute5,
278 x_attribute6 => rec_cur_pmt_plan.attribute6,
279 x_attribute7 => rec_cur_pmt_plan.attribute7,
280 x_attribute8 => rec_cur_pmt_plan.attribute8,
281 x_attribute9 => rec_cur_pmt_plan.attribute9,
282 x_attribute10 => rec_cur_pmt_plan.attribute10,
283 x_attribute11 => rec_cur_pmt_plan.attribute11,
284 x_attribute12 => rec_cur_pmt_plan.attribute12,
285 x_attribute13 => rec_cur_pmt_plan.attribute13,
286 x_attribute14 => rec_cur_pmt_plan.attribute14,
287 x_attribute15 => rec_cur_pmt_plan.attribute15,
288 x_attribute16 => rec_cur_pmt_plan.attribute16,
289 x_attribute17 => rec_cur_pmt_plan.attribute17,
290 x_attribute18 => rec_cur_pmt_plan.attribute18,
291 x_attribute19 => rec_cur_pmt_plan.attribute19,
292 x_attribute20 => rec_cur_pmt_plan.attribute20,
293 x_mode => 'R'
294 );
295 END IF;
296 END apply_installments;
297
298 PROCEDURE validate_parameters (
299 p_n_validation_level IN NUMBER,
300 p_credit_rec IN igs_fi_credit_pvt.credit_rec_type,
301 p_attribute_rec IN igs_fi_credits_api_pub.attribute_rec_type,
302 p_b_return_status OUT NOCOPY BOOLEAN
303 ) AS
304 /***********************************************************************************************
305 Created By : Shtatiko
306 Date Created By: 03-APR-2003
307 Purpose : Procedure VALIDATE_PARAMETERS is the main procedure which has calls to
308 individual procedures or functions. When this procedure is invoked with FULL
309 validation level then all parameter validations will take place.
310
311 Known limitations,enhancements,remarks:
312 Change History
313 Who When What
314 svuppala 07-JUL-2005 Enh 3392095 - Tution Waivers build
315 Modified validations of credit class and credit instrument in Validate_parameters.
316 svuppala 9-JUN-2005 Enh 4213629 - The automatic generation of the Receipt Number.
317 Removed the validation to check if Credit Number is passed
318 if the combination of party_id and credit_number already exists in the system.
319 vvutukur 23-Sep-2003 Enh#3045007.Payment Plan Build.Changes as specified in TD.
320 vvutukur 15-Jul-2003 Enh#3038511.FICR106 Build. Added call to newly created generic procedure
321 get_award_year_status to validate the Award Year Status.
322 vvutukur 18-Jun-2003 Enh#2831582.Lockbox Build. Removed call to function validate_lockbox as the same has been removed.
323 schodava 11-Jun-03 Enh # 2831587. Credit Card Fund Transfer Build
324 Added validations for Credit Card status and credit card payee
325 ********************************************************************************************** */
326 l_v_credit_class igs_fi_cr_types_all.credit_class%TYPE;
327 l_n_pay_cr_type_id igs_fi_cr_types.payment_credit_type_id%TYPE;
328 l_v_ld_cal_type igs_ca_inst.cal_type%TYPE;
329 l_v_ld_ci_seq_num igs_ca_inst.sequence_number%TYPE;
330 l_b_return_status BOOLEAN;
331 l_v_message_name fnd_new_messages.message_name%TYPE;
332 l_v_awd_yr_status_cd igf_ap_batch_aw_map.award_year_status_code%TYPE;
333
334 FUNCTION get_cr_type(p_credit_type_id igs_fi_cr_types.credit_type_id%TYPE) RETURN VARCHAR2 AS
335 /***********************************************************************************************
336 Created By: shtatiko
337 Date Created By: 03-APR-2003
338 Purpose: This function returns credit type name for the specified credit type id.
339
340 Known limitations,enhancements,remarks:
341 Change History
342 Who When What
343 svuppala 9-JUN-2005 Enh 4213629 - The automatic generation of the Receipt Number.
344 Removed the validation to check if Credit Number is passed
345 if the combination of party_id and credit_number already exists in the system.
346 ********************************************************************************************** */
347 CURSOR cur_cr IS
348 SELECT credit_type_name
349 FROM igs_fi_cr_types
350 WHERE credit_type_id = p_credit_type_id;
351 l_cr_type igs_fi_cr_types.credit_type_name%TYPE;
352 BEGIN
353 OPEN cur_cr;
354 FETCH cur_cr INTO l_cr_type;
355 CLOSE cur_cr;
356 RETURN l_cr_type;
357 END get_cr_type;
358
359 BEGIN
360
361 -- Currently p_n_validation_level can only take values 0 and 100
362 IF p_n_validation_level NOT IN (FND_API.G_VALID_LEVEL_NONE, FND_API.G_VALID_LEVEL_FULL) THEN
363 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
364 fnd_msg_pub.ADD;
365 p_b_return_status := FALSE;
366 RETURN;
367 END IF;
368
369 -- If p_n_validation_level is 0 then no validations need to be done.
370 IF p_n_validation_level = FND_API.G_VALID_LEVEL_NONE THEN
371 p_b_return_status := TRUE;
372 RETURN;
373 END IF;
374
375 -- Do all kinds of validations if validation level is 100, FND_API.G_VALID_LEVEL_FULL
376 IF p_n_validation_level = FND_API.G_VALID_LEVEL_FULL THEN
377
378
379
380 -- Check the status with which the current transaction is being created
381 IF NOT igs_fi_crdapi_util.validate_credit_status ( p_v_crd_status => p_credit_rec.p_credit_status ) THEN
382 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
383 fnd_message.set_name ( 'IGS', 'IGS_FI_CAPI_INVALID_CR_STAT');
384 fnd_message.set_token ( 'CR_STAT', p_credit_rec.p_credit_status );
385 fnd_msg_pub.ADD;
386 p_b_return_status := FALSE;
387 RETURN;
388 END IF;
389 END IF;
390
391 -- Validate lookup code passed for Credit Source
392 IF p_credit_rec.p_credit_source IS NOT NULL THEN
393 IF NOT igs_fi_crdapi_util.validate_igf_lkp ( p_v_lookup_type => 'IGF_AW_FED_FUND', p_v_lookup_code => p_credit_rec.p_credit_source) THEN
394 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
395 fnd_message.set_name ( 'IGS', 'IGS_FI_CAPI_CRD_SRC_NULL');
396 fnd_message.set_token ( 'CR_SOURCE', p_credit_rec.p_credit_source );
397 fnd_msg_pub.ADD;
398 p_b_return_status := FALSE;
399 RETURN;
400 END IF;
401 END IF;
402 END IF;
403
404 -- Check if the Credit Type is active as on the current date
405 igs_fi_crdapi_util.validate_credit_type ( p_n_credit_type_id => p_credit_rec.p_credit_type_id,
406 p_v_credit_class => l_v_credit_class,
407 p_b_return_stat => l_b_return_status);
408 IF (l_b_return_status = FALSE)
409 OR (igs_fi_crdapi_util.validate_igs_lkp ( 'IGS_FI_CREDIT_CLASS', l_v_credit_class ) = FALSE) THEN
410 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
411 fnd_message.set_name ( 'IGS', 'IGS_FI_CAPI_CR_TYPE_INVALID');
412 fnd_message.set_token ( 'CR_TYPE', p_credit_rec.p_credit_type_id );
413 fnd_msg_pub.ADD;
414 p_b_return_status := FALSE;
415 RETURN;
416 END IF;
417 END IF;
418 --- Check if credit_class holds a value of WAIVER
419 -- 3392095
420 IF l_v_credit_class = 'WAIVER' THEN
421 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
422 fnd_message.set_name ( 'IGS', 'IGS_FI_CAPI_CR_TYPE_INVALID');
423 fnd_message.set_token ( 'CR_TYPE', p_credit_rec.p_credit_type_id );
424 fnd_msg_pub.ADD;
425 p_b_return_status := FALSE;
426 RETURN;
427 END IF;
428 END IF;
429
430
431 -- Check if the Party ID, Credit Class combination is valid.
432 IF NOT igs_fi_crdapi_util.validate_party_id ( p_n_party_id => p_credit_rec.p_party_id,
433 p_v_credit_class => l_v_credit_class) THEN
434 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
435 fnd_message.set_name ( 'IGS', 'IGS_FI_CAPI_PARTY_INVALID');
436 fnd_message.set_token ( 'PARTY_ID', p_credit_rec.p_party_id);
437 fnd_msg_pub.ADD;
438 p_b_return_status := FALSE;
439 RETURN;
440 END IF;
441 END IF;
442
443 IF l_v_credit_class IN ('EXTFA','INTFA') THEN
444 IF p_credit_rec.p_awd_yr_cal_type IS NULL
445 OR p_credit_rec.p_awd_yr_ci_sequence_number IS NULL
446 OR p_credit_rec.p_fee_cal_type IS NULL
447 OR p_credit_rec.p_fee_ci_sequence_number IS NULL THEN
448 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
449 fnd_message.set_name ( 'IGS', 'IGS_FI_FPAY_MAND');
450 fnd_msg_pub.ADD;
451 p_b_return_status := FALSE;
452 RETURN;
453 END IF;
454 END IF;
455 END IF;
456
457 -- Check if the Credit Instrument is valid
458 IF NOT igs_fi_crdapi_util.validate_igs_lkp ( p_v_lookup_type => 'IGS_FI_CREDIT_INSTRUMENT',
459 p_v_lookup_code => p_credit_rec.p_credit_instrument) THEN
460
461 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
462 fnd_message.set_name ( 'IGS', 'IGS_FI_CAPI_CRD_INSTR_NULL');
463 fnd_message.set_token ( 'CR_INSTR', p_credit_rec.p_credit_instrument);
464 fnd_msg_pub.ADD;
465 p_b_return_status := FALSE;
466 RETURN;
467 END IF;
468
469 ELSE
470 -- Check if the Credit Instrument is valid and whether instrument is of WAIVER Type
471 -- 3392095
472 IF p_credit_rec.p_credit_instrument = 'WAIVER' AND
473 fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
474
475 fnd_message.set_name ( 'IGS', 'IGS_FI_CAPI_CRD_INSTR_NULL');
476 fnd_message.set_token ( 'CR_INSTR', p_credit_rec.p_credit_instrument);
477 fnd_msg_pub.ADD;
478 p_b_return_status := FALSE;
479 RETURN;
480
481 END IF;
482 END IF;
483
484 -- If the Credit Class is either 'Enrollment Deposit' or 'Other Deposit' then check if the
485 -- Payment Credit Type attached to the Deposit Credit Type is active as on the current date.
486 IF l_v_credit_class IN ('ENRDEPOSIT','OTHDEPOSIT') THEN
487 igs_fi_crdapi_util.validate_dep_crtype( p_credit_rec.p_credit_type_id,
488 l_n_pay_cr_type_id,
489 l_b_return_status);
490 IF NOT l_b_return_status THEN
491 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
492 fnd_message.set_name('IGS','IGS_FI_PCT_DCT_INVALID');
493 fnd_message.set_token('PAY_CR_TYPE', l_n_pay_cr_type_id);
494 fnd_message.set_token('DEP_CR_TYPE', p_credit_rec.p_credit_type_id);
495 fnd_msg_pub.ADD;
496 p_b_return_status := FALSE;
497 END IF;
498 END IF;
499 END IF;
500
501 -- Only for a Credit Instrument of 'Check', a value can be provided to the Check Number parameter.
502 IF p_credit_rec.p_check_number IS NOT NULL AND p_credit_rec.p_credit_instrument <> 'CHECK' THEN
503 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
504 fnd_message.set_name ( 'IGS', 'IGS_FI_INVALID_CHECK_NUMBER');
505 fnd_msg_pub.ADD;
506 p_b_return_status := FALSE;
507 RETURN;
508 END IF;
509 END IF;
510
511 -- Check if the GL Date parameter (P_CREDIT_REC.P_GL_DATE) is in valid period
512 igs_fi_crdapi_util.validate_gl_date ( p_d_gl_date => p_credit_rec.p_gl_date,
513 p_v_credit_class => l_v_credit_class,
514 p_b_return_status=> l_b_return_status,
515 p_v_message_name => l_v_message_name );
516 IF l_b_return_status = FALSE THEN
517 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
518 fnd_message.set_name ( 'IGS', l_v_message_name);
519 IF l_v_message_name = 'IGS_FI_INVALID_GL_DATE' THEN
520 fnd_message.set_token ('GL_DATE', p_credit_rec.p_gl_date );
521 END IF;
522 fnd_msg_pub.ADD;
523 p_b_return_status := FALSE;
524 RETURN;
525 END IF;
526 END IF;
527
528 -- User is not allowed to create a transaction with Credit Class as Payment/Enrollment Deposit/Other Deposit/
529 -- Installment Payments with Credit Instrument as 'DEPOSIT'.
530 IF l_v_credit_class IN ('PMT','ENRDEPOSIT','OTHDEPOSIT','INSTALLMENT_PAYMENTS') AND p_credit_rec.p_credit_instrument = 'DEPOSIT' THEN
531 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
532 fnd_message.set_name('IGS','IGS_FI_CAPI_CRD_INSTR_NULL');
533 fnd_message.set_token('CR_INSTR', p_credit_rec.p_credit_instrument);
534 fnd_msg_pub.ADD;
535 p_b_return_status := FALSE;
536 RETURN;
537 END IF;
538 END IF;
539
540 -- Validate Source Transaction Type Parameter, if passed
541 IF p_credit_rec.p_source_tran_type IS NOT NULL THEN
542 igs_fi_crdapi_util.validate_source_tran_type ( p_v_source_tran_type => p_credit_rec.p_source_tran_type,
543 p_v_credit_class => l_v_credit_class,
544 p_v_credit_instrument=> p_credit_rec.p_credit_instrument,
545 p_b_return_status => l_b_return_status,
546 p_v_message_name => l_v_message_name );
547 IF l_b_return_status = FALSE THEN
548 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
549 fnd_message.set_name('IGS', l_v_message_name);
550 IF l_v_message_name = 'IGS_FI_SOURCE_TRAN_TYP_INVALID' THEN
551 fnd_message.set_token('VALUE', p_credit_rec.p_source_tran_type);
552 END IF;
553 fnd_msg_pub.ADD;
554 p_b_return_status := FALSE;
555 RETURN;
556 END IF;
557 END IF;
558 ELSIF l_v_credit_class = 'ENRDEPOSIT' THEN
559 -- Source Transaction Type is mandatory for ENRDEPOSIT Credit Class
560 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
561 fnd_message.set_name('IGS','IGS_GE_INSUFFICIENT_PARAMETER');
562 fnd_msg_pub.ADD;
563 p_b_return_status := FALSE;
564 RETURN;
565 END IF;
566 END IF;
567
568 -- Validate Source Transaction Reference Number
569 IF l_v_credit_class = 'ENRDEPOSIT' AND p_credit_rec.p_credit_instrument <> 'DEPOSIT' THEN
570 IF NOT igs_fi_crdapi_util.validate_source_tran_ref_num(p_n_party_id => p_credit_rec.p_party_id,
571 p_n_source_tran_ref_num => p_credit_rec.p_source_tran_ref_number) THEN
572 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
573 fnd_message.set_name('IGS', 'IGS_FI_SOURCE_TRAN_REF_INVALID');
574 fnd_message.set_token('VALUE', p_credit_rec.p_source_tran_ref_number);
575 fnd_msg_pub.ADD;
576 p_b_return_status := FALSE;
577 RETURN;
578 END IF;
579 END IF;
580 END IF;
581
582 -- Validate Award Year, If passed
583 IF p_credit_rec.p_awd_yr_cal_type IS NOT NULL AND p_credit_rec.p_awd_yr_ci_sequence_number IS NOT NULL THEN
584 IF NOT igs_fi_crdapi_util.validate_cal_inst ( p_v_cal_type => p_credit_rec.p_awd_yr_cal_type,
585 p_n_ci_sequence_number => p_credit_rec.p_awd_yr_ci_sequence_number,
586 p_v_s_cal_cat => 'AWARD') THEN
587 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
588 fnd_message.set_name('IGS', 'IGS_FI_INV_AWD_YR');
589 fnd_msg_pub.ADD;
590 p_b_return_status := FALSE;
591 RETURN;
592 END IF;
593 END IF;
594
595 l_v_message_name := NULL;
596 igs_fi_crdapi_util.get_award_year_status( p_v_awd_cal_type => p_credit_rec.p_awd_yr_cal_type,
597 p_n_awd_seq_number => p_credit_rec.p_awd_yr_ci_sequence_number,
598 p_v_awd_yr_status => l_v_awd_yr_status_cd,
599 p_v_message_name => l_v_message_name
600 );
601
602 IF l_v_message_name IS NOT NULL THEN
603 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
604 fnd_message.set_name('IGS',l_v_message_name);
605 fnd_msg_pub.ADD;
606 p_b_return_status := FALSE;
607 RETURN;
608 END IF;
609 END IF;
610 END IF;
611
612 -- Validate Fee Period, If passed
613 IF p_credit_rec.p_fee_cal_type IS NOT NULL AND p_credit_rec.p_fee_ci_sequence_number IS NOT NULL THEN
614 IF NOT igs_fi_crdapi_util.validate_cal_inst ( p_v_cal_type => p_credit_rec.p_fee_cal_type,
615 p_n_ci_sequence_number => p_credit_rec.p_fee_ci_sequence_number,
616 p_v_s_cal_cat => 'FEE') THEN
617 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
618 fnd_message.set_name('IGS', 'IGS_FI_FCI_NOTFOUND');
619 fnd_msg_pub.ADD;
620 p_b_return_status := FALSE;
621 RETURN;
622 END IF;
623 END IF;
624 END IF;
625
626 -- If Fee period is passed and it is valid then check if there exists any relation with load calendar
627 IF p_credit_rec.p_fee_cal_type IS NOT NULL AND p_credit_rec.p_fee_ci_sequence_number IS NOT NULL THEN
628 igs_fi_crdapi_util.validate_fci_lci_reln ( p_v_fee_cal_type => p_credit_rec.p_fee_cal_type,
629 p_n_fee_ci_sequence_number => p_credit_rec.p_fee_ci_sequence_number,
630 p_v_ld_cal_type => l_v_ld_cal_type,
631 p_n_ld_ci_sequence_number => l_v_ld_ci_seq_num,
632 p_v_message_name => l_v_message_name,
633 p_b_return_stat => l_b_return_status ) ;
634 IF l_b_return_status = FALSE THEN
635 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
636 fnd_message.set_name('IGS', l_v_message_name);
637 fnd_msg_pub.ADD;
638 p_b_return_status := FALSE;
639 RETURN;
640 END IF;
641 END IF;
642 END IF;
643
644 -- Validate the currency code
645 IF p_credit_rec.p_currency_cd IS NULL THEN
646 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
647 fnd_message.set_name('IGS', 'IGS_GE_INSUFFICIENT_PARAMETER');
648 fnd_msg_pub.ADD;
649 p_b_return_status := FALSE;
650 RETURN;
651 END IF;
652 ELSIF NOT igs_fi_crdapi_util.validate_curr ( p_v_currency_cd => p_credit_rec.p_currency_cd) THEN
653 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
654 fnd_message.set_name('IGS', 'IGS_FI_INVALID_CUR');
655 fnd_message.set_token('CUR_CD', p_credit_rec.p_currency_cd);
656 fnd_msg_pub.ADD;
657 p_b_return_status := FALSE;
658 RETURN;
659 END IF;
660 END IF;
661
662 -- Validate Amount Passed
663 igs_fi_crdapi_util.validate_amount ( p_n_amount => p_credit_rec.p_amount,
664 p_b_return_status => l_b_return_status,
665 p_v_message_name => l_v_message_name );
666 IF l_b_return_status = FALSE THEN
667 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
668 fnd_message.set_name('IGS', l_v_message_name);
669 IF l_v_message_name = 'IGS_FI_CRD_AMT_NEGATIVE' THEN
670 fnd_message.set_token ( 'CR_AMT', p_credit_rec.p_amount );
671 END IF;
672 fnd_msg_pub.ADD;
673 p_b_return_status := FALSE;
674 RETURN;
675 END IF;
676 END IF;
677
678 l_v_message_name := NULL;
679 l_b_return_status := NULL;
680 IF l_v_credit_class = 'INSTALLMENT_PAYMENTS' THEN
681 --User should not be allowed to create an Installment Payment with the transaction amount greater than
682 --the active Installment balance for the person.
683 igs_fi_crdapi_util.validate_plan_balance(p_n_person_id => p_credit_rec.p_party_id,
684 p_n_amount => p_credit_rec.p_amount,
685 p_b_status => l_b_return_status,
686 p_v_message_name => l_v_message_name
687 );
688 IF NOT l_b_return_status THEN
689 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
690 fnd_message.set_name('IGS',l_v_message_name);
691 fnd_msg_pub.ADD;
692 p_b_return_status := FALSE;
693 RETURN;
694 END IF;
695 END IF;
696 END IF;
697
698 -- Validate Credit Card Code, if passed
699 IF p_credit_rec.p_credit_card_code IS NOT NULL THEN
700 IF NOT igs_fi_crdapi_util.validate_igs_lkp ( p_v_lookup_type => 'IGS_CREDIT_CARDS',
701 p_v_lookup_code => p_credit_rec.p_credit_card_code) THEN
702 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
703 fnd_message.set_name('IGS', 'IGS_FI_CRD_CARD_INVALID');
704 fnd_message.set_token('CRD_CARD', p_credit_rec.p_credit_card_code);
705 fnd_msg_pub.ADD;
706 p_b_return_status := FALSE;
707 RETURN;
708 END IF;
709 END IF;
710 END IF;
711
712 -- Validate Credit Card Expiration date, if passed
713 IF p_credit_rec.p_credit_card_expiration_date IS NOT NULL
714 AND TRUNC(p_credit_rec.p_credit_card_expiration_date) < TRUNC(SYSDATE) THEN
715 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
716 fnd_message.set_name('IGS', 'IGS_FI_CRD_EXPDT_INVALID');
717 fnd_message.set_token('EXP_DATE', p_credit_rec.p_credit_card_expiration_date);
718 fnd_msg_pub.ADD;
719 p_b_return_status := FALSE;
720 RETURN;
721 END IF;
722 END IF;
723
724
725 -- Validate the Invoice Id, if passed
726 IF p_credit_rec.p_invoice_id IS NOT NULL THEN
727 IF NOT igs_fi_crdapi_util.validate_invoice_id ( p_n_invoice_id => p_credit_rec.p_invoice_id) THEN
728 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
729 fnd_message.set_name('IGS', 'IGS_FI_NO_INV_INVOICE_TBL');
730 fnd_msg_pub.ADD;
731 p_b_return_status := FALSE;
732 RETURN;
733 END IF;
734 END IF;
735 END IF;
736
737 -- Validate the Credit Card Payee, if passed
738 IF p_credit_rec.p_v_credit_card_payee_cd IS NOT NULL THEN
739 IF NOT igs_fi_crdapi_util.validate_credit_card_payee(p_v_credit_card_payee_cd => p_credit_rec.p_v_credit_card_payee_cd) THEN
740 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
741 fnd_message.set_name('IGS', 'IGS_FI_INV_PAYEEID');
742 fnd_msg_pub.ADD;
743 p_b_return_status := FALSE;
744 RETURN;
745 END IF;
746 END IF;
747 END IF;
748
749 -- Check if the Credit Card Status is valid, if passed
750 IF p_credit_rec.p_v_credit_card_status_code IS NOT NULL THEN
751 IF NOT igs_fi_crdapi_util.validate_igs_lkp ( p_v_lookup_type => 'IGS_FI_CREDIT_CARD_STATUS',
752 p_v_lookup_code => p_credit_rec.p_v_credit_card_status_code)
753 OR p_credit_rec.p_v_credit_card_status_code <> 'PENDING' THEN
754 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
755 fnd_message.set_name ( 'IGS', 'IGS_FI_INV_CC_STATUS');
756 fnd_msg_pub.ADD;
757 p_b_return_status := FALSE;
758 RETURN;
759 END IF;
760 END IF;
761 END IF;
762
763 -- Validate descriptive flex-field attribute values
764 IF NOT igs_fi_crdapi_util.validate_desc_flex (
765 p_v_attribute_category => p_attribute_rec.p_attribute_category,
766 p_v_attribute1 => p_attribute_rec.p_attribute1,
767 p_v_attribute2 => p_attribute_rec.p_attribute2,
768 p_v_attribute3 => p_attribute_rec.p_attribute3,
769 p_v_attribute4 => p_attribute_rec.p_attribute4,
770 p_v_attribute5 => p_attribute_rec.p_attribute5,
771 p_v_attribute6 => p_attribute_rec.p_attribute6,
772 p_v_attribute7 => p_attribute_rec.p_attribute7,
773 p_v_attribute8 => p_attribute_rec.p_attribute8,
774 p_v_attribute9 => p_attribute_rec.p_attribute9,
775 p_v_attribute10 => p_attribute_rec.p_attribute10,
776 p_v_attribute11 => p_attribute_rec.p_attribute11,
777 p_v_attribute12 => p_attribute_rec.p_attribute12,
778 p_v_attribute13 => p_attribute_rec.p_attribute13,
779 p_v_attribute14 => p_attribute_rec.p_attribute14,
780 p_v_attribute15 => p_attribute_rec.p_attribute15,
781 p_v_attribute16 => p_attribute_rec.p_attribute16,
782 p_v_attribute17 => p_attribute_rec.p_attribute17,
783 p_v_attribute18 => p_attribute_rec.p_attribute18,
784 p_v_attribute19 => p_attribute_rec.p_attribute19,
785 p_v_attribute20 => p_attribute_rec.p_attribute20,
786 p_v_desc_flex_name => 'IGS_FI_CREDITS_ALL_FLEX') THEN
787 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_ERROR) THEN
788 fnd_message.set_name('IGS', 'IGS_AD_INVALID_DESC_FLEX');
789 fnd_msg_pub.ADD;
790 p_b_return_status := FALSE;
791 RETURN;
792 END IF;
793 END IF;
794
795 -- All validations are successfully completed. Return with success
796 p_b_return_status := TRUE;
797 RETURN;
798 END IF; -- p_validation_level = FND_API.G_VALID_LEVEL_FULL
799
800 END validate_parameters;
801
802 FUNCTION validate_credit_status ( p_v_crd_status IN VARCHAR2 ) RETURN BOOLEAN
803 AS
804 /***********************************************************************************************
805 Created By : Shtatiko
806 Date Created By: 03-APR-2003
807 Purpose : This function checks if transaction status is valid
808
809 Known limitations,enhancements,remarks:
810 Change History
811 Who When What
812 ********************************************************************************************** */
813
814 BEGIN
815 -- Parameter p_v_crd_status is mandatory.
816 IF (p_v_crd_status IS NULL) OR (p_v_crd_status <> 'CLEARED') THEN
817 RETURN FALSE;
818 END IF;
819 RETURN igs_fi_crdapi_util.validate_igs_lkp (p_v_lookup_type => 'IGS_FI_CREDIT_STATUS', p_v_lookup_code => 'CLEARED');
820
821 END validate_credit_status;
822
823 FUNCTION validate_igs_lkp (
824 p_v_lookup_type IN VARCHAR2,
825 p_v_lookup_code IN VARCHAR2
826 ) RETURN BOOLEAN AS
827 /***********************************************************************************************
828 Created By : Shtatiko
829 Date Created By: 03-APR-2003
830 Purpose : This function checks if the IGS lookup code is valid for the lookup type.
831
832 Known limitations,enhancements,remarks:
833 Change History
834 Who When What
835 ********************************************************************************************** */
836 CURSOR cur_igs_lkp (cp_lookup_code igs_lookup_values.lookup_code%TYPE,
837 cp_lookup_type igs_lookup_values.lookup_type%TYPE,
838 cp_enabled_flag VARCHAR2)
839 IS
840 SELECT 'x'
841 FROM igs_lookup_values
842 WHERE lookup_type = cp_lookup_type
843 AND lookup_code = cp_lookup_code
844 AND enabled_flag = cp_enabled_flag
845 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE_ACTIVE, SYSDATE)) AND TRUNC(NVL(END_DATE_ACTIVE, SYSDATE));
846 rec_igs_lkp cur_igs_lkp%ROWTYPE;
847
848 BEGIN
849 -- Both parameters are mandatory
850 IF p_v_lookup_type IS NULL OR p_v_lookup_code IS NULL THEN
851 RETURN FALSE;
852 END IF;
853 OPEN cur_igs_lkp (p_v_lookup_code, p_v_lookup_type, 'Y');
854 FETCH cur_igs_lkp INTO rec_igs_lkp;
855 IF cur_igs_lkp%FOUND THEN
856 CLOSE cur_igs_lkp;
857 RETURN TRUE;
858 ELSE
859 CLOSE cur_igs_lkp;
860 RETURN FALSE;
861 END IF;
862 END validate_igs_lkp;
863
864 FUNCTION validate_igf_lkp (
865 p_v_lookup_type IN VARCHAR2,
866 p_v_lookup_code IN VARCHAR2
867 ) RETURN BOOLEAN AS
868 /***********************************************************************************************
869 Created By : Shtatiko
870 Date Created By: 03-APR-2003
871 Purpose : This function checks if the IGF lookup code is valid for the lookup type.
872
873 Known limitations,enhancements,remarks:
874 Change History
875 Who When What
876 ********************************************************************************************** */
877 CURSOR cur_igf_lkp (cp_lookup_code igf_lookups_view.lookup_code%TYPE,
878 cp_lookup_type igf_lookups_view.lookup_type%TYPE,
879 cp_enabled_flag VARCHAR2)
880 IS
881 SELECT 'x'
882 FROM igf_lookups_view
883 WHERE lookup_type = cp_lookup_type
884 AND lookup_code = cp_lookup_code
885 AND enabled_flag= cp_enabled_flag;
886 rec_igf_lkp cur_igf_lkp%ROWTYPE;
887
888 BEGIN
889 -- Both parameters are mandatory
890 IF p_v_lookup_type IS NULL OR p_v_lookup_code IS NULL THEN
891 RETURN FALSE;
892 END IF;
893
894 OPEN cur_igf_lkp (p_v_lookup_code, p_v_lookup_type, 'Y');
895 FETCH cur_igf_lkp INTO rec_igf_lkp;
896 IF cur_igf_lkp%FOUND THEN
897 CLOSE cur_igf_lkp;
898 RETURN TRUE;
899 ELSE
900 CLOSE cur_igf_lkp;
901 RETURN FALSE;
902 END IF;
903 END validate_igf_lkp;
904
905 PROCEDURE validate_credit_type (
906 p_n_credit_type_id IN PLS_INTEGER,
907 p_v_credit_class OUT NOCOPY VARCHAR2,
908 p_b_return_stat OUT NOCOPY BOOLEAN
909 ) AS
910 /***********************************************************************************************
911 Created By : Shtatiko
912 Date Created By: 03-APR-2003
913 Purpose : This procedure validates a credit type is active and effective as on the
914 current system.
915
916 Known limitations,enhancements,remarks:
917 Change History
918 Who When What
919 ********************************************************************************************** */
920 CURSOR cur_cr_typ (cp_credit_type_id igs_fi_cr_types_all.credit_type_id%TYPE)
921 IS
922 SELECT credit_class
923 FROM igs_fi_cr_types_all
924 WHERE credit_type_id = cp_credit_type_id
925 AND TRUNC(SYSDATE) BETWEEN TRUNC(effective_start_date) AND TRUNC(NVL(effective_end_date,SYSDATE));
926
927 BEGIN
928
929 IF p_n_credit_type_id IS NULL THEN
930 p_v_credit_class := NULL;
931 p_b_return_stat := FALSE;
932 RETURN;
933 END IF;
934
935 OPEN cur_cr_typ(p_n_credit_type_id);
936 FETCH cur_cr_typ INTO p_v_credit_class;
937 IF cur_cr_typ%FOUND THEN
938 p_b_return_stat := TRUE;
939 ELSE
940 p_v_credit_class := NULL;
941 p_b_return_stat := FALSE;
942 END IF;
943 CLOSE cur_cr_typ;
944 END validate_credit_type;
945
946 FUNCTION validate_curr ( p_v_currency_cd IN VARCHAR2 ) RETURN BOOLEAN
947 AS
948 /***********************************************************************************************
949 Created By : Shtatiko
950 Date Created By: 03-APR-2003
951 Purpose : This function checks if the currency code is active in the system.
952
953 Known limitations,enhancements,remarks:
954 Change History
955 Who When What
956 ********************************************************************************************** */
957 CURSOR c_cur_cd (cp_currency_cd fnd_currencies_active_v.currency_code%TYPE)
958 IS
959 SELECT 'x'
960 FROM fnd_currencies_active_v
961 WHERE currency_code = cp_currency_cd
962 AND currency_code <> 'STAT';
963 rec_cur_cd c_cur_cd%ROWTYPE;
964 BEGIN
965
966 IF p_v_currency_cd IS NULL THEN
967 RETURN FALSE;
968 END IF;
969 OPEN c_cur_cd( p_v_currency_cd );
970 FETCH c_cur_cd INTO rec_cur_cd;
971 IF c_cur_cd%FOUND THEN
972 CLOSE c_cur_cd;
973 RETURN TRUE;
974 ELSE
975 CLOSE c_cur_cd;
976 RETURN FALSE;
977 END IF;
978
979 END validate_curr;
980
981 FUNCTION validate_cal_inst (
982 p_v_cal_type IN VARCHAR2,
983 p_n_ci_sequence_number IN PLS_INTEGER,
984 p_v_s_cal_cat IN VARCHAR2
985 ) RETURN BOOLEAN AS
986 /***********************************************************************************************
987 Created By : Shtatiko
988 Date Created By: 03-APR-2003
989 Purpose : This function checks if the Calendar Instance is active in the system.
990
991 Known limitations,enhancements,remarks:
992 Change History
993 Who When What
994 ********************************************************************************************** */
995 CURSOR c_cal_inst(cp_cal_type IN igs_ca_inst.cal_type%TYPE,
996 cp_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE,
997 cp_s_cal_cat IN igs_ca_type.s_cal_cat%TYPE)
998 IS
999 SELECT 'x'
1000 FROM igs_ca_inst ci,
1001 igs_ca_type cat,
1002 igs_ca_stat stat
1003 WHERE ci.cal_type = cp_cal_type
1004 AND ci.sequence_number = cp_ci_sequence_number
1005 AND ci.cal_status = stat.cal_status
1006 AND stat.s_cal_status = 'ACTIVE'
1007 AND ci.cal_type = cat.cal_type
1008 AND cat.s_cal_cat = cp_s_cal_cat;
1009 rec_cal_inst c_cal_inst%ROWTYPE;
1010
1011 BEGIN
1012
1013 -- All three parameters are mandatory
1014 IF p_v_cal_type IS NULL OR p_n_ci_sequence_number IS NULL OR p_v_s_cal_cat IS NULL THEN
1015 RETURN FALSE;
1016 END IF;
1017
1018 OPEN c_cal_inst ( p_v_cal_type, p_n_ci_sequence_number, p_v_s_cal_cat );
1019 FETCH c_cal_inst INTO rec_cal_inst;
1020 IF c_cal_inst%FOUND THEN
1021 CLOSE c_cal_inst;
1022 RETURN TRUE;
1023 ELSE
1024 CLOSE c_cal_inst;
1025 RETURN FALSE;
1026 END IF;
1027
1028 END validate_cal_inst;
1029
1030 FUNCTION val_cal_inst (
1031 p_v_cal_type IN VARCHAR2,
1032 p_n_ci_sequence_number IN NUMBER,
1033 p_v_s_cal_cat IN VARCHAR2
1034 ) RETURN VARCHAR2 AS
1035 /***********************************************************************************************
1036 Created By : sapanigr
1037 Date Created By: 22-NOV-2005
1038 Purpose : This function is similar to validate_cal_inst but returns VARCHAR TRUE or FALSE
1039
1040 Known limitations,enhancements,remarks:
1041 Change History
1042 Who When What
1043 ********************************************************************************************** */
1044 BEGIN
1045 IF igs_fi_crdapi_util.validate_cal_inst(
1046 p_v_cal_type,
1047 p_n_ci_sequence_number,
1048 p_v_s_cal_cat) THEN
1049 RETURN 'TRUE';
1050 ELSE
1051 RETURN 'FALSE';
1052 END IF;
1053 EXCEPTION
1054 WHEN OTHERS THEN
1055 APP_EXCEPTION.RAISE_EXCEPTION;
1056 END val_cal_inst;
1057
1058
1059
1060 PROCEDURE validate_fci_lci_reln (
1061 p_v_fee_cal_type IN VARCHAR2,
1062 p_n_fee_ci_sequence_number IN PLS_INTEGER,
1063 p_v_ld_cal_type OUT NOCOPY VARCHAR2,
1064 p_n_ld_ci_sequence_number OUT NOCOPY PLS_INTEGER,
1065 p_v_message_name OUT NOCOPY VARCHAR2,
1066 p_b_return_stat OUT NOCOPY BOOLEAN
1067 ) AS
1068 /***********************************************************************************************
1069 Created By : Shtatiko
1070 Date Created By: 03-APR-2003
1071 Purpose : This procedure checks if there exists a relation between Fee and Load calendar
1072 instance and checks if the Load Calendar Instance is active in the system.
1073
1074 Known limitations,enhancements,remarks:
1075 Change History
1076 Who When What
1077 ********************************************************************************************** */
1078 l_b_ret BOOLEAN;
1079 l_v_ld_cal_type igs_ca_inst.cal_type%TYPE;
1080 l_n_ld_seq_number igs_ca_inst.sequence_number%TYPE;
1081 l_v_message_name fnd_new_messages.message_name%TYPE;
1082
1083 BEGIN
1084
1085 IF p_v_fee_cal_type IS NULL OR p_n_fee_ci_sequence_number IS NULL THEN
1086 p_v_message_name := NULL;
1087 p_b_return_stat := FALSE;
1088 RETURN;
1089 END IF;
1090
1091 -- Check if a relation exists
1092 l_b_ret := igs_fi_gen_001.finp_get_lfci_reln (
1093 p_cal_type => p_v_fee_cal_type,
1094 p_ci_sequence_number => p_n_fee_ci_sequence_number,
1095 p_cal_category => 'FEE',
1096 p_ret_cal_type => l_v_ld_cal_type,
1097 p_ret_ci_sequence_number => l_n_ld_seq_number,
1098 p_message_name => l_v_message_name );
1099 IF NOT l_b_ret THEN
1100 p_v_message_name := l_v_message_name;
1101 p_b_return_stat := FALSE;
1102 ELSE
1103 -- Check if load calendar instance is active in the system.
1104 IF igs_fi_crdapi_util.validate_cal_inst (
1105 p_v_cal_type => l_v_ld_cal_type,
1106 p_n_ci_sequence_number => l_n_ld_seq_number,
1107 p_v_s_cal_cat => 'LOAD') THEN
1108 p_v_message_name := NULL;
1109 p_b_return_stat := TRUE;
1110 p_v_ld_cal_type := l_v_ld_cal_type;
1111 p_n_ld_ci_sequence_number := l_n_ld_seq_number;
1112 ELSE
1113 p_v_message_name := 'IGS_FI_LOAD_CAL_NOT_ACTIVE';
1114 p_b_return_stat := FALSE;
1115 p_v_ld_cal_type := NULL;
1116 p_n_ld_ci_sequence_number := NULL;
1117 END IF;
1118 END IF;
1119
1120 END validate_fci_lci_reln;
1121
1122 PROCEDURE validate_dep_crtype (
1123 p_n_credit_type_id IN PLS_INTEGER,
1124 p_n_pay_credit_type_id OUT NOCOPY PLS_INTEGER,
1125 p_b_return_stat OUT NOCOPY BOOLEAN
1126 ) AS
1127 /***********************************************************************************************
1128 Created By : Shtatiko
1129 Date Created By: 03-APR-2003
1130 Purpose : This procedure checks if the payment credit type attached to the Enrollment
1131 Deposit or Other Deposit credit type is active in the system as on the current
1132 system date. When Payment Credit Type is found to be active then this procedure
1133 returns this payment credit type as OUT variable.
1134
1135 Known limitations,enhancements,remarks:
1136 Change History
1137 Who When What
1138 ***********************************************************************************************/
1139 CURSOR c_check_cr_typ_id (cp_cr_typ_id igs_fi_cr_types.credit_type_id%TYPE)
1140 IS
1141 SELECT c.payment_credit_type_id
1142 FROM igs_fi_cr_types c
1143 WHERE c.credit_type_id = cp_cr_typ_id
1144 AND TRUNC(SYSDATE) BETWEEN TRUNC(c.effective_start_date) AND TRUNC(NVL(c.effective_end_date,SYSDATE))
1145 AND EXISTS (SELECT 'x'
1146 FROM igs_fi_cr_types p
1147 WHERE p.credit_type_id = c.payment_credit_type_id
1148 AND TRUNC(SYSDATE) BETWEEN TRUNC(p.effective_start_date) AND TRUNC(NVL(p.effective_end_date,SYSDATE)));
1149
1150 BEGIN
1151
1152 IF p_n_credit_type_id IS NULL THEN
1153 p_n_pay_credit_type_id := NULL;
1154 p_b_return_stat := FALSE;
1155 RETURN;
1156 END IF;
1157
1158 OPEN c_check_cr_typ_id ( p_n_credit_type_id );
1159 FETCH c_check_cr_typ_id INTO p_n_pay_credit_type_id;
1160 IF c_check_cr_typ_id%FOUND THEN
1161 CLOSE c_check_cr_typ_id;
1162 p_b_return_stat := TRUE;
1163 ELSE
1164 CLOSE c_check_cr_typ_id;
1165 p_b_return_stat := FALSE;
1166 p_n_pay_credit_type_id := NULL;
1167 END IF;
1168
1169 END validate_dep_crtype;
1170
1171 FUNCTION validate_party_id (
1172 p_n_party_id IN PLS_INTEGER,
1173 p_v_credit_class IN VARCHAR2
1174 ) RETURN BOOLEAN AS
1175 /***********************************************************************************************
1176 Created By : Shtatiko
1177 Date Created By: 03-APR-2003
1178 Purpose : Validates the combination of Party Id and Credit Class
1179
1180 Known limitations,enhancements,remarks:
1181 Change History
1182 Who When What
1183 sapanigr 12-Feb-2006 Bug#5018036 - Cursor c_valid_person now uses hz_parties instead of igs_fi_parties_v. (R12 SQL Repository tuning)
1184 vvutukur 13-Sep-2003 Enh#3045007.Payment Plans Build. Modified the code so that the function validates if the
1185 input person is on an Active Payment Plan if the input credit class is Installment Payments.
1186 ***********************************************************************************************/
1187 CURSOR c_valid_person( cp_party_id igs_fi_parties_v.person_id%TYPE,
1188 cp_party_type igs_fi_parties_v.party_type%TYPE
1189 ) IS
1190 SELECT 'x'
1191 FROM hz_parties
1192 WHERE party_id = cp_party_id
1193 AND party_type = cp_party_type;
1194 rec_valid_person c_valid_person%ROWTYPE;
1195
1196 BEGIN
1197
1198 IF p_n_party_id IS NULL OR p_v_credit_class IS NULL THEN
1199 RETURN FALSE;
1200 END IF;
1201
1202 IF p_v_credit_class NOT IN ('ENRDEPOSIT','OTHDEPOSIT') THEN
1203 IF igs_fi_gen_007.validate_person( p_n_party_id ) = 'N' THEN
1204 RETURN FALSE;
1205 ELSE
1206 --If the input credit class is Installment Payments.. then
1207 IF p_v_credit_class = 'INSTALLMENT_PAYMENTS' THEN
1208 --Check if the person is on an active Payment Plan. If yes, return TRUE else return FALSE from this function.
1209 IF igs_fi_gen_008.chk_active_pay_plan(p_n_party_id) = 'Y' THEN
1210 RETURN TRUE;
1211 ELSE
1212 RETURN FALSE;
1213 END IF;
1214 END IF;
1215 RETURN TRUE;
1216 END IF;
1217 ELSE -- If Credit Class is ENRDEPOSIT or OTHDEPOSIT
1218 -- If Credit Class is ENRDEPOSIT Party should be a PERSON and a STUDENT
1219 -- If Credit Class is OTHDEPOSIT Party should be a PERSON
1220 OPEN c_valid_person ( p_n_party_id, 'PERSON' );
1221 FETCH c_valid_person INTO rec_valid_person;
1222 IF c_valid_person%FOUND THEN
1223 CLOSE c_valid_person;
1224 IF ( p_v_credit_class = 'ENRDEPOSIT' AND NVL(SUBSTR(igs_en_gen_007.enrp_get_student_ind(p_n_party_id),1,1),'N')= 'Y' )
1225 OR p_v_credit_class = 'OTHDEPOSIT' THEN
1226 RETURN TRUE;
1227 ELSE
1228 RETURN FALSE;
1229 END IF;
1230 ELSE
1231 CLOSE c_valid_person;
1232 RETURN FALSE;
1233 END IF;
1234 END IF;
1235
1236 END validate_party_id;
1237
1238 PROCEDURE validate_gl_date (
1239 p_d_gl_date IN DATE,
1240 p_v_credit_class IN VARCHAR2,
1241 p_b_return_status OUT NOCOPY BOOLEAN,
1242 p_v_message_name OUT NOCOPY VARCHAR2
1243 ) AS
1244 /***********************************************************************************************
1245 Created By : Shtatiko
1246 Date Created By: 03-APR-2003
1247 Purpose : This procedure checks if the GL Date is valid in the system.
1248
1249 Known limitations,enhancements,remarks:
1250 Change History
1251 Who When What
1252 ***********************************************************************************************/
1253 l_v_message_name fnd_new_messages.message_name%TYPE;
1254 l_v_closing_status gl_period_statuses.closing_status%TYPE;
1255 BEGIN
1256
1257 IF p_d_gl_date IS NULL OR p_v_credit_class IS NULL THEN
1258 p_b_return_status := FALSE;
1259 p_v_message_name := 'IGS_GE_INSUFFICIENT_PARAMETER' ;
1260 RETURN;
1261 END IF;
1262
1263 IF p_v_credit_class IN ('ONLINE PAYMENT') THEN
1264 p_b_return_status := TRUE;
1265 p_v_message_name := NULL ;
1266 RETURN;
1267 END IF;
1268
1269 -- Check the closing status of the passed gl_date
1270 igs_fi_gen_gl.get_period_status_for_date(p_d_date => p_d_gl_date,
1271 p_v_closing_status => l_v_closing_status,
1272 p_v_message_name => l_v_message_name);
1273 IF l_v_message_name IS NOT NULL THEN
1274 p_b_return_status := FALSE;
1275 p_v_message_name := l_v_message_name;
1276 RETURN;
1277 END IF;
1278 IF l_v_closing_status NOT IN ('O','F') THEN
1279 p_b_return_status := FALSE;
1280 p_v_message_name := 'IGS_FI_INVALID_GL_DATE';
1281 RETURN;
1282 END IF;
1283 p_v_message_name := NULL ;
1284 p_b_return_status := TRUE;
1285 END validate_gl_date;
1286
1287 PROCEDURE validate_source_tran_type (
1288 p_v_source_tran_type IN VARCHAR2,
1289 p_v_credit_class IN VARCHAR2,
1290 p_v_credit_instrument IN VARCHAR2,
1291 p_b_return_status OUT NOCOPY BOOLEAN,
1292 p_v_message_name OUT NOCOPY VARCHAR2
1293 ) AS
1294 /***********************************************************************************************
1295 Created By : Shtatiko
1296 Date Created By: 03-APR-2003
1297 Purpose : This procedure checks if the combination of Source Transaction Type,
1298 Credit Class is valid.
1299
1300 Known limitations,enhancements,remarks:
1301 Change History
1302 Who When What
1303 vvutukur 13-Sep-2003 Enh#3045007.Payment Plans Build. Modified the code to validate source transaction type,
1304 if provided.
1305 ***********************************************************************************************/
1306
1307 BEGIN
1308
1309 IF p_v_source_tran_type IS NULL OR p_v_credit_class IS NULL THEN
1310 p_b_return_status := FALSE;
1311 p_v_message_name := 'IGS_GE_INSUFFICIENT_PARAMETER' ;
1312 RETURN;
1313 END IF;
1314
1315 -- Check if source transaction type is valid
1316 IF NOT igs_fi_crdapi_util.validate_igs_lkp ( p_v_lookup_type => 'IGS_FI_SOURCE_TRANSACTION_REF',
1317 p_v_lookup_code => p_v_source_tran_type ) THEN
1318 p_b_return_status := FALSE;
1319 p_v_message_name := 'IGS_FI_SOURCE_TRAN_TYP_INVALID';
1320 RETURN;
1321 END IF;
1322
1323 -- Source transaction reference type can be provided for Payment, Enrollment Deposit ,
1324 -- Other Deposit,Installment Payments credit class only.
1325 IF p_v_credit_class NOT IN ('PMT', 'ENRDEPOSIT', 'OTHDEPOSIT','INSTALLMENT_PAYMENTS') THEN
1326 p_b_return_status := FALSE;
1327 p_v_message_name := 'IGS_FI_SOURCE_TRAN_CC_INVALID';
1328 RETURN;
1329 END IF;
1330
1331 -- If Credit Class is 'Other Deposit' then the Source Transaction type should be other than 'ADM', 'DEPOSIT'
1332 -- For a receipt transaction with 'Payment' credit class and with a Credit Instrument other than 'Deposit', if a value is
1333 -- passed to Source Transaction Type parameter then check if the value provided is other than 'Admission Application' or 'Deposit'
1334 -- If Credit Class is 'Enrollment Deposit' then the Source Transaction type should always be 'Admission Application' - 'ADM'
1335 IF ((p_v_credit_class = 'OTHDEPOSIT' AND p_v_source_tran_type IN ('ADM', 'DEPOSIT'))
1336 OR (p_v_credit_class IN ('PMT','INSTALLMENT_PAYMENTS') AND p_v_credit_instrument <> 'DEPOSIT' AND p_v_source_tran_type IN ('ADM', 'DEPOSIT'))
1337 OR (p_v_credit_class IN ('ENRDEPOSIT') AND p_v_source_tran_type <> 'ADM')
1338 ) THEN
1339 p_b_return_status := FALSE;
1340 p_v_message_name := 'IGS_FI_SOURCE_TRAN_TYP_INVALID';
1341 RETURN;
1342 END IF;
1343 -- If all validations passed then return with success.
1344 p_b_return_status := TRUE;
1345 p_v_message_name := NULL;
1346
1347 END validate_source_tran_type;
1348
1349 FUNCTION validate_source_tran_ref_num (
1350 p_n_party_id IN PLS_INTEGER,
1351 p_n_source_tran_ref_num IN PLS_INTEGER
1352 ) RETURN BOOLEAN AS
1353 /***********************************************************************************************
1354 Created By : Shtatiko
1355 Date Created By: 03-APR-2003
1356 Purpose : This function checks if the Source Transaction Reference Number is valid in the system.
1357
1358 Known limitations,enhancements,remarks:
1359 Change History
1360 Who When What
1361 ***********************************************************************************************/
1362 CURSOR c_source_tran_ref (cp_party_id IN NUMBER,
1363 cp_appl_id IN NUMBER
1364 )IS
1365 SELECT 'x'
1366 FROM igs_ad_appl appl
1367 WHERE appl.person_id = cp_party_id
1368 AND appl.application_id = cp_appl_id
1369 AND EXISTS ( SELECT 'X'
1370 FROM igs_ad_ps_appl_inst apl_in,
1371 igs_ad_ofrdfrmt_stat df,
1372 igs_ad_ofr_resp_stat off
1373 WHERE apl_in.person_id = appl.person_id
1374 AND apl_in.admission_appl_number = appl.admission_appl_number
1375 AND apl_in.adm_offer_resp_status = off.adm_offer_resp_status
1376 AND apl_in.adm_offer_dfrmnt_status = df.adm_offer_dfrmnt_status
1377 AND (off.s_adm_offer_resp_status ='ACCEPTED' OR
1378 (off.s_adm_offer_resp_status ='DEFERRAL' AND df.s_adm_offer_dfrmnt_status = 'CONFIRM')
1379 ));
1380 rec_source_tran_ref c_source_tran_ref%ROWTYPE;
1381
1382 BEGIN
1383
1384 IF p_n_party_id IS NULL OR p_n_source_tran_ref_num IS NULL THEN
1385 RETURN FALSE;
1386 END IF;
1387
1388 -- Check if the Source Transaction Reference Number is a valid application number for the given person
1389 OPEN c_source_tran_ref ( p_n_party_id, p_n_source_tran_ref_num );
1390 FETCH c_source_tran_ref INTO rec_source_tran_ref;
1391 IF c_source_tran_ref%FOUND THEN
1392 CLOSE c_source_tran_ref;
1393 RETURN TRUE;
1394 ELSE
1395 CLOSE c_source_tran_ref;
1396 RETURN FALSE;
1397 END IF;
1398
1399 END validate_source_tran_ref_num;
1400
1401 PROCEDURE validate_amount (
1402 p_n_amount IN NUMBER,
1403 p_b_return_status OUT NOCOPY BOOLEAN,
1404 p_v_message_name OUT NOCOPY VARCHAR2
1405 ) AS
1406 /***********************************************************************************************
1407 Created By : Shtatiko
1408 Date Created By: 03-APR-2003
1409 Purpose : This procedure will validates Amount
1410
1411 Known limitations,enhancements,remarks:
1412 Change History
1413 Who When What
1414 ***********************************************************************************************/
1415 BEGIN
1416
1417 IF p_n_amount IS NULL THEN
1418 p_b_return_status := FALSE;
1419 p_v_message_name := 'IGS_FI_CAPI_CRD_AMT_NULL';
1420 RETURN;
1421 END IF;
1422
1423 IF p_n_amount < 0 THEN
1424 p_b_return_status := FALSE;
1425 p_v_message_name := 'IGS_FI_CRD_AMT_NEGATIVE';
1426 RETURN;
1427 END IF;
1428
1429 p_b_return_status := TRUE;
1430 p_v_message_name := NULL;
1431
1432 END validate_amount;
1433
1434 FUNCTION validate_desc_flex (
1435 p_v_attribute_category IN VARCHAR2,
1436 p_v_attribute1 IN VARCHAR2,
1437 p_v_attribute2 IN VARCHAR2,
1438 p_v_attribute3 IN VARCHAR2,
1439 p_v_attribute4 IN VARCHAR2,
1440 p_v_attribute5 IN VARCHAR2,
1441 p_v_attribute6 IN VARCHAR2,
1442 p_v_attribute7 IN VARCHAR2,
1443 p_v_attribute8 IN VARCHAR2,
1444 p_v_attribute9 IN VARCHAR2,
1445 p_v_attribute10 IN VARCHAR2,
1446 p_v_attribute11 IN VARCHAR2,
1447 p_v_attribute12 IN VARCHAR2,
1448 p_v_attribute13 IN VARCHAR2,
1449 p_v_attribute14 IN VARCHAR2,
1450 p_v_attribute15 IN VARCHAR2,
1451 p_v_attribute16 IN VARCHAR2,
1452 p_v_attribute17 IN VARCHAR2,
1453 p_v_attribute18 IN VARCHAR2,
1454 p_v_attribute19 IN VARCHAR2,
1455 p_v_attribute20 IN VARCHAR2,
1456 p_v_desc_flex_name IN VARCHAR2
1457 ) RETURN BOOLEAN AS
1458 /***********************************************************************************************
1459 Created By : Shtatiko
1460 Date Created By: 03-APR-2003
1461 Purpose : Function for validating Descriptive Flex-Field combination.
1462
1463 Known limitations,enhancements,remarks:
1464 Change History
1465 Who When What
1466 ***********************************************************************************************/
1467
1468 BEGIN
1469 -- Parameter p_v_desc_flex_name are mandatory
1470 IF p_v_desc_flex_name IS NULL THEN
1471 RETURN FALSE;
1472 END IF;
1473
1474 RETURN igs_ad_imp_018.validate_desc_flex (
1475 p_attribute_category => p_v_attribute_category,
1476 p_attribute1 => p_v_attribute1,
1477 p_attribute2 => p_v_attribute2,
1478 p_attribute3 => p_v_attribute3,
1479 p_attribute4 => p_v_attribute4,
1480 p_attribute5 => p_v_attribute5,
1481 p_attribute6 => p_v_attribute6,
1482 p_attribute7 => p_v_attribute7,
1483 p_attribute8 => p_v_attribute8,
1484 p_attribute9 => p_v_attribute9,
1485 p_attribute10 => p_v_attribute10,
1486 p_attribute11 => p_v_attribute11,
1487 p_attribute12 => p_v_attribute12,
1488 p_attribute13 => p_v_attribute13,
1489 p_attribute14 => p_v_attribute14,
1490 p_attribute15 => p_v_attribute15,
1491 p_attribute16 => p_v_attribute16,
1492 p_attribute17 => p_v_attribute17,
1493 p_attribute18 => p_v_attribute18,
1494 p_attribute19 => p_v_attribute19,
1495 p_attribute20 => p_v_attribute20,
1496 p_desc_flex_name => p_v_desc_flex_name);
1497
1498 END validate_desc_flex;
1499
1500 FUNCTION validate_invoice_id ( p_n_invoice_id IN PLS_INTEGER ) RETURN BOOLEAN
1501 AS
1502 /***********************************************************************************************
1503 Created By : Shtatiko
1504 Date Created By: 03-APR-2003
1505 Purpose : This function checks if the Invoice ID exists in the system.
1506
1507 Known limitations,enhancements,remarks:
1508 Change History
1509 Who When What
1510 ***********************************************************************************************/
1511 CURSOR c_inv_id(cp_invoice_id igs_fi_inv_int.invoice_id%TYPE) IS
1512 SELECT 'x'
1513 FROM igs_fi_inv_int
1514 WHERE invoice_id=cp_invoice_id;
1515 rec_inv_id c_inv_id%ROWTYPE;
1516
1517 BEGIN
1518 IF p_n_invoice_id IS NULL THEN
1519 RETURN FALSE;
1520 END IF;
1521
1522 OPEN c_inv_id ( p_n_invoice_id );
1523 FETCH c_inv_id INTO rec_inv_id;
1524 IF c_inv_id%FOUND THEN
1525 CLOSE c_inv_id;
1526 RETURN TRUE;
1527 ELSE
1528 CLOSE c_inv_id;
1529 RETURN FALSE;
1530 END IF;
1531
1532 END validate_invoice_id;
1533
1534 FUNCTION validate_credit_card_payee (p_v_credit_card_payee_cd IN VARCHAR2 ) RETURN BOOLEAN
1535 AS
1536 /***********************************************************************************************
1537 Created By : schodava
1538 Date Created By: 11-Jun-2003
1539 Purpose : This function checks if credit card payee is valid
1540
1541 Known limitations,enhancements,remarks:
1542 Change History
1543 Who When What
1544 ********************************************************************************************** */
1545
1546 -- Get the payee
1547 CURSOR c_payee (cp_payeeid IN VARCHAR2) IS
1548 SELECT name, payeeid
1549 FROM iby_payee
1550 WHERE payeeid = cp_payeeid
1551 AND NVL(activestatus,'N')='Y';
1552 rec_payee c_payee%ROWTYPE;
1553
1554 BEGIN
1555 -- Check if the payee parameter is a valid payee in the iPayment table
1556 OPEN c_payee(cp_payeeid => p_v_credit_card_payee_cd);
1557 FETCH c_payee INTO rec_payee;
1558 IF c_payee%NOTFOUND THEN
1559 CLOSE c_payee;
1560 RETURN FALSE;
1561 ELSE
1562 CLOSE c_payee;
1563 RETURN TRUE;
1564 END IF;
1565 END validate_credit_card_payee;
1566
1567 PROCEDURE translate_local_currency (
1568 p_n_amount IN OUT NOCOPY NUMBER,
1569 p_v_currency_cd IN OUT NOCOPY VARCHAR2,
1570 p_n_exchange_rate IN NUMBER,
1571 p_b_return_status OUT NOCOPY BOOLEAN,
1572 p_v_message_name OUT NOCOPY VARCHAR2
1573 ) AS
1574 /***********************************************************************************************
1575 Created By : Shtatiko
1576 Date Created By: 03-APR-2003
1577 Purpose : This procedure will determine the transaction amount in terms of the
1578 functional currency.
1579
1580 Known limitations,enhancements,remarks:
1581 Change History
1582 Who When What
1583 ***********************************************************************************************/
1584 l_v_currency_cd igs_fi_control_v.currency_cd%TYPE;
1585 l_v_curr_desc igs_fi_control_v.name%TYPE;
1586 l_v_message_name fnd_new_messages.message_name%TYPE;
1587
1588 BEGIN
1589
1590 IF p_n_amount IS NULL
1591 OR p_v_currency_cd IS NULL THEN
1592 p_b_return_status := FALSE;
1593 p_v_message_name := 'IGS_GE_INSUFFICIENT_PARAMETER';
1594 RETURN;
1595 END IF;
1596 -- Get the local currency that is set at the System Options Form
1597 igs_fi_gen_gl.finp_get_cur ( p_v_currency_cd => l_v_currency_cd,
1598 p_v_curr_desc => l_v_curr_desc,
1599 p_v_message_name=> l_v_message_name );
1600 IF l_v_message_name IS NOT NULL THEN
1601 p_b_return_status := FALSE;
1602 p_v_message_name := l_v_message_name;
1603 RETURN;
1604 END IF;
1605
1606 -- Check if the derived local currency code and the passed one are same.
1607 IF l_v_currency_cd = p_v_currency_cd THEN
1608 p_b_return_status := TRUE;
1609 p_v_message_name := NULL;
1610 RETURN;
1611 END IF;
1612
1613 -- Local currency code and the passed one are different, then exchange rate should be passed
1614 IF p_n_exchange_rate IS NULL THEN
1615 p_b_return_status := FALSE;
1616 p_v_message_name := 'IGS_FI_NO_EXCHG_RATE';
1617 ELSE
1618 p_v_currency_cd := l_v_currency_cd;
1619 p_n_amount:= p_n_amount * p_n_exchange_rate;
1620 p_b_return_status := TRUE;
1621 p_v_message_name := NULL;
1622 END IF;
1623
1624 END translate_local_currency;
1625
1626 END igs_fi_crdapi_util;
[Home] [Help]