[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_WAIVERS_API_PUB
Source
1 PACKAGE BODY igs_fi_waivers_api_pub AS
2 /* $Header: IGSFI94B.pls 120.3 2005/10/31 10:33:51 appldev noship $ */
3 ------------------------------------------------------------------
4 --Created by : Sanil Madathil, Oracle IDC
5 --Date created: 29 July 2005
6 --
7 --Purpose: Public Waiver API
8 --
9 --
10 --Known limitations/enhancements and/or remarks:
11 --
12 --Change History:
13 --Who When What
14 --
15 ------------------------------------------------------------------
16 g_pkg_name CONSTANT VARCHAR2(30) := 'igs_fi_waivers_api_pub';
17
18 -- Procedure for validating all inbound parameters to API
19 PROCEDURE validate_parameters(
20 p_fee_cal_type IN igs_ca_inst_all.cal_type%TYPE ,
21 p_fee_ci_seq_number IN igs_ca_inst_all.sequence_number%TYPE ,
22 p_waiver_name IN igs_fi_waiver_pgms.waiver_name%TYPE ,
23 p_person_id IN igs_fi_credits_all.party_id%TYPE ,
24 p_waiver_amount IN igs_fi_credits_all.amount%TYPE ,
25 p_currency_cd IN igs_fi_credits_all.currency_cd%TYPE ,
26 p_gl_date IN igs_fi_credits_all.gl_date%TYPE ,
27 p_source_credit_id IN igs_fi_credits_all.credit_id%TYPE ,
28 p_b_return_status OUT NOCOPY BOOLEAN
29 );
30
31 -- Procedure for enabling statement level logging
32 PROCEDURE log_to_fnd (
33 p_v_module IN VARCHAR2,
34 p_v_string IN VARCHAR2
35 );
36
37 CURSOR c_waiver_pgms (
38 cp_v_fee_cal_type IN igs_ca_inst_all.cal_type%TYPE,
39 cp_n_fee_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
40 cp_v_waiver_name IN igs_fi_waiver_pgms.waiver_name%TYPE
41 ) IS
42 SELECT fwp.fee_cal_type
43 ,fwp.fee_ci_sequence_number
44 ,fwp.waiver_name
45 ,fwp.waiver_method_code
46 ,fwp.waiver_status_code
47 ,fwp.credit_type_id
48 ,fwp.target_fee_type
49 ,fwp.adjustment_fee_type
50 ,fwp.waiver_mode_code
51 FROM igs_fi_waiver_pgms fwp
52 WHERE fwp.fee_cal_type = cp_v_fee_cal_type
53 AND fwp.fee_ci_sequence_number = cp_n_fee_sequence_number
54 AND fwp.waiver_name = cp_v_waiver_name;
55
56
57
58 PROCEDURE create_manual_waivers(
59 p_api_version IN NUMBER ,
60 p_init_msg_list IN VARCHAR2 ,
61 p_commit IN VARCHAR2 ,
62 x_return_status OUT NOCOPY VARCHAR2 ,
63 x_msg_count OUT NOCOPY NUMBER ,
64 x_msg_data OUT NOCOPY VARCHAR2 ,
65 p_fee_cal_type IN igs_ca_inst_all.cal_type%TYPE ,
66 p_fee_ci_seq_number IN igs_ca_inst_all.sequence_number%TYPE ,
67 p_waiver_name IN igs_fi_waiver_pgms.waiver_name%TYPE ,
68 p_person_id IN igs_fi_credits_all.party_id%TYPE ,
69 p_waiver_amount IN igs_fi_credits_all.amount%TYPE ,
70 p_currency_cd IN igs_fi_credits_all.currency_cd%TYPE ,
71 p_exchange_rate IN igs_fi_credits_all.exchange_rate%TYPE ,
72 p_gl_date IN igs_fi_credits_all.gl_date%TYPE ,
73 p_source_credit_id IN igs_fi_credits_all.credit_id%TYPE ,
74 x_waiver_credit_id OUT NOCOPY NUMBER ,
75 x_waiver_adjustment_id OUT NOCOPY NUMBER
76 ) AS
77 ------------------------------------------------------------------
78 --Created by : Sanil Madathil, Oracle IDC
79 --Date created: 29 July 2005
80 --
81 -- Purpose : procedure for importing Waiver Credits and
82 -- Waiver Charge Adjustments
83 -- Invoked : from External Source
84 -- Function :
85 --
86 -- Parameters : p_api_version : IN parameter. Required.
87 -- p_init_msg_list : IN parameter
88 -- p_commit : IN parameter
89 -- x_return_status : OUT parameter
90 -- x_msg_count : OUT parameter
91 -- x_msg_data : OUT parameter
92 -- p_fee_cal_type : IN parameter. Required
93 -- p_fee_ci_seq_number : IN parameter. Required
94 -- p_waiver_name : IN parameter. Required.
95 -- p_person_id : IN parameter. Required.
96 -- p_waiver_amount : IN parameter. Required
97 -- p_currency_cd : IN parameter. Required
98 -- p_exchange_rate : IN parameter. Required
99 -- p_gl_date : IN parameter. Required
100 -- p_source_credit_id : IN parameter. Optional
101 -- x_waiver_credit_id : OUT parameter
102 -- x_waiver_adjustment_id : OUT parameter
103 --
104 --Known limitations/enhancements and/or remarks:
105 --
106 --Change History:
107 --Who When What
108 ------------------------------------------------------------------
109 l_api_version CONSTANT NUMBER := 1.0;
110 l_api_name CONSTANT VARCHAR2(30) := 'create_manual_waivers';
111
112 rec_c_waiver_pgms c_waiver_pgms%ROWTYPE;
113
114 l_v_manage_accounts igs_fi_control_all.manage_accounts%TYPE;
115 l_v_message_name fnd_new_messages.message_name%TYPE;
116 l_v_return_status VARCHAR2(1);
117
118 l_n_conv_process_run_ind igs_fi_control_all.conv_process_run_ind%TYPE;
119 l_n_version_number igs_fi_balance_rules.version_number%TYPE;
120 l_n_balance_rule_id igs_fi_balance_rules.balance_rule_id%TYPE;
121 l_n_credit_id igs_fi_credits_all.credit_id%TYPE;
122 l_n_invoice_id igs_fi_inv_int_all.invoice_id%TYPE;
123
124 l_d_last_conversion_date DATE;
125
126 l_b_return_status BOOLEAN;
127 BEGIN
128 --Standard start of API savepoint
129 SAVEPOINT create_manual_waivers_pub;
130
131 log_to_fnd(p_v_module => 'create_manual_waivers',
132 p_v_string => ' Entered Procedure create_manual_waivers: The input parameters are '||
133 ' p_api_version : ' ||p_api_version ||
134 ' p_init_msg_list : ' ||p_init_msg_list ||
135 ' p_commit : ' ||p_commit ||
136 ' p_fee_cal_type : ' ||p_fee_cal_type ||
137 ' p_fee_ci_seq_number : ' ||p_fee_ci_seq_number ||
138 ' p_waiver_name : ' ||p_waiver_name ||
139 ' p_person_id : ' ||p_person_id ||
140 ' p_waiver_amount : ' ||p_waiver_amount ||
141 ' p_currency_cd : ' ||p_currency_cd ||
142 ' p_exchange_rate : ' ||p_exchange_rate ||
143 ' p_gl_date : ' ||p_gl_date ||
144 ' p_source_credit_id : ' ||p_source_credit_id
145 );
146
147 --Standard call to check for call compatibility
148 IF NOT fnd_api.compatible_api_call(
149 p_current_version_number => l_api_version,
150 p_caller_version_number => p_api_version,
151 p_api_name => l_api_name,
152 p_pkg_name => g_pkg_name
153 ) THEN
154 RAISE fnd_api.g_exc_unexpected_error;
155 END IF;
156
157 --Initialize message list if p_init_msg_list is set to TRUE
158 IF fnd_api.to_boolean(p_init_msg_list) THEN
159 fnd_msg_pub.initialize;
160 END IF;
161
162 --Initialize API return status to success
163 x_return_status := fnd_api.g_ret_sts_success;
164
165 ----------------------- Start of API Body---------------------------
166
167 -- Verify the value of Manage Accounts set in System Options form.
168 -- If this value is either NULL or OTHER, API would error out
169 igs_fi_com_rec_interface.chk_manage_account (
170 p_v_manage_acc => l_v_manage_accounts,
171 p_v_message_name => l_v_message_name
172 );
173 IF (l_v_manage_accounts IS NULL OR l_v_manage_accounts = 'OTHER') THEN
174 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
175 fnd_message.set_name ( 'IGS', l_v_message_name );
176 fnd_msg_pub.add;
177 RAISE fnd_api.g_exc_error;
178 END IF;
179 END IF;
180
181 -- Verify if holds conversion process is currently being executed
182 -- The API would error out if holds conversion process is currently
183 -- being executed
184 l_v_message_name := NULL;
185 igs_fi_gen_007.finp_get_conv_prc_run_ind(
186 p_n_conv_process_run_ind => l_n_conv_process_run_ind,
187 p_v_message_name => l_v_message_name
188 );
189 IF l_n_conv_process_run_ind = 1 AND l_v_message_name IS NULL THEN
190 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
191 fnd_message.set_name('IGS','IGS_FI_REASS_BAL_PRC_RUN');
192 fnd_msg_pub.add;
193 RAISE fnd_api.g_exc_error;
194 END IF;
195 END IF;
196 IF l_v_message_name IS NOT NULL THEN
197 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
198 fnd_message.set_name('IGS',l_v_message_name);
199 fnd_msg_pub.add;
200 RAISE fnd_api.g_exc_error;
201 END IF;
202 END IF;
203
204 -- Verify if active balance rule for holds balance type has been set up
205 -- in the balance rules form
206 igs_fi_gen_007.finp_get_balance_rule(
207 p_v_balance_type => 'HOLDS',
208 p_v_action => 'ACTIVE',
209 p_n_balance_rule_id => l_n_balance_rule_id,
210 p_d_last_conversion_date => l_d_last_conversion_date,
211 p_n_version_number => l_n_version_number
212 );
213 --If no active holds balance rule exists, API would error out
214 IF l_n_version_number = 0 THEN
215 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
216 fnd_message.set_name('IGS','IGS_FI_CANNOT_CRT_TXN');
217 fnd_msg_pub.add;
218 RAISE fnd_api.g_exc_error;
219 END IF;
220 END IF;
221
222 -- Validate all the inbound parameters to API
223 validate_parameters(
224 p_fee_cal_type => p_fee_cal_type ,
225 p_fee_ci_seq_number => p_fee_ci_seq_number ,
226 p_waiver_name => p_waiver_name ,
227 p_person_id => p_person_id ,
228 p_waiver_amount => p_waiver_amount ,
229 p_currency_cd => p_currency_cd ,
230 p_gl_date => p_gl_date ,
231 p_source_credit_id => p_source_credit_id ,
232 p_b_return_status => l_b_return_status
233 );
234 IF NOT (l_b_return_status) THEN
235 RAISE fnd_api.g_exc_error;
236 END IF;
237
238 OPEN c_waiver_pgms (
239 cp_v_fee_cal_type => p_fee_cal_type ,
240 cp_n_fee_sequence_number => p_fee_ci_seq_number ,
241 cp_v_waiver_name => p_waiver_name
242 );
243 FETCH c_waiver_pgms INTO rec_c_waiver_pgms;
244 CLOSE c_waiver_pgms;
245
246 IF p_waiver_amount > 0 THEN
247
248 log_to_fnd(p_v_module => 'create_manual_waivers',
249 p_v_string => ' Invoking Procedure igs_fi_wav_utils_002.call_credits_api with input parameters '||
250 ' p_n_person_id : ' ||p_person_id ||
251 ' p_v_fee_cal_type : ' ||p_fee_cal_type ||
252 ' p_n_fee_ci_seq_number : ' ||p_fee_ci_seq_number ||
253 ' p_v_waiver_name : ' ||p_waiver_name ||
254 ' p_n_credit_type_id : ' ||rec_c_waiver_pgms.credit_type_id ||
255 ' p_v_currency_cd : ' ||p_currency_cd ||
256 ' p_n_waiver_amt : ' ||p_waiver_amount ||
257 ' p_d_gl_date : ' ||p_gl_date
258 );
259
260 -- invoke the credits api to create the waiver credit
261 igs_fi_wav_utils_002.call_credits_api (
262 p_n_person_id => p_person_id ,
263 p_v_fee_cal_type => p_fee_cal_type ,
264 p_n_fee_ci_seq_number => p_fee_ci_seq_number ,
265 p_v_waiver_name => p_waiver_name ,
266 p_n_credit_type_id => rec_c_waiver_pgms.credit_type_id ,
267 p_v_currency_cd => p_currency_cd ,
268 p_n_waiver_amt => p_waiver_amount ,
269 p_d_gl_date => p_gl_date ,
270 p_n_credit_id => l_n_credit_id ,
271 x_return_status => l_v_return_status
272 );
273 IF l_v_return_status <> 'S' THEN
274 log_to_fnd(p_v_module => 'create_manual_waivers',
275 p_v_string => ' Procedure igs_fi_wav_utils_002.call_credits_api errored out'
276 );
277 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
278 fnd_message.set_name('IGS','IGS_FI_WAV_NO_TRANS_CREATED');
279 fnd_msg_pub.add;
280 RAISE fnd_api.g_exc_error;
281 END IF;
282 END IF;
283
284 log_to_fnd(p_v_module => 'create_manual_waivers',
285 p_v_string => ' Procedure igs_fi_wav_utils_002.call_credits_api returned success'||
286 ' p_n_credit_id : ' ||l_n_credit_id
287 );
288
289 log_to_fnd(p_v_module => 'create_manual_waivers',
290 p_v_string => ' Invoking Procedure igs_fi_wav_utils_001.apply_waivers with input parameters '||
291 ' p_n_person_id : ' ||p_person_id ||
292 ' p_v_fee_cal_type : ' ||p_fee_cal_type ||
293 ' p_n_fee_ci_seq_number : ' ||p_fee_ci_seq_number ||
294 ' p_v_waiver_name : ' ||p_waiver_name ||
295 ' p_v_target_fee_type : ' ||rec_c_waiver_pgms.target_fee_type ||
296 ' p_v_adj_fee_type : ' ||rec_c_waiver_pgms.adjustment_fee_type ||
297 ' p_v_waiver_method_code : ' ||rec_c_waiver_pgms.waiver_method_code ||
298 ' p_v_waiver_mode_code : ' ||rec_c_waiver_pgms.waiver_mode_code ||
299 ' p_v_currency_cd : ' ||p_currency_cd ||
300 ' p_d_gl_date : ' ||p_gl_date
301 );
302
303 l_v_return_status := NULL;
304
305 -- invoke apply waiver routine
306 igs_fi_wav_utils_001.apply_waivers (
307 p_n_person_id => p_person_id ,
308 p_v_fee_cal_type => p_fee_cal_type ,
309 p_n_fee_ci_seq_number => p_fee_ci_seq_number ,
310 p_v_waiver_name => p_waiver_name ,
311 p_v_target_fee_type => rec_c_waiver_pgms.target_fee_type ,
312 p_v_adj_fee_type => rec_c_waiver_pgms.adjustment_fee_type ,
313 p_v_waiver_method_code => rec_c_waiver_pgms.waiver_method_code ,
314 p_v_waiver_mode_code => rec_c_waiver_pgms.waiver_mode_code ,
315 p_n_source_credit_id => l_n_credit_id ,
316 p_n_source_invoice_id => NULL ,
317 p_v_currency_cd => p_currency_cd ,
318 p_d_gl_date => p_gl_date ,
319 x_return_status => l_v_return_status
320 );
321
322 IF l_v_return_status <> 'S' THEN
323 log_to_fnd(p_v_module => 'create_manual_waivers',
324 p_v_string => ' Procedure igs_fi_wav_utils_001.apply_waivers errored out'
325 );
326 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
327 fnd_message.set_name('IGS','IGS_FI_WAV_NO_TRANS_CREATED');
328 fnd_msg_pub.add;
329 RAISE fnd_api.g_exc_error;
330 END IF;
331 END IF;
332
333 x_waiver_credit_id := l_n_credit_id;
334
335 ELSIF p_waiver_amount < 0 THEN
336
337 log_to_fnd(p_v_module => 'create_manual_waivers',
338 p_v_string => ' Invoking Procedure igs_fi_wav_utils_002.call_charges_api with input parameters '||
339 ' p_n_person_id : ' ||p_person_id ||
340 ' p_v_fee_cal_type : ' ||p_fee_cal_type ||
341 ' p_n_fee_ci_seq_number : ' ||p_fee_ci_seq_number ||
342 ' p_v_waiver_name : ' ||p_waiver_name ||
343 ' p_v_adj_fee_type : ' ||rec_c_waiver_pgms.adjustment_fee_type ||
344 ' p_v_currency_cd : ' ||p_currency_cd ||
345 ' p_n_waiver_amt : ' ||p_waiver_amount ||
346 ' p_d_gl_date : ' ||p_gl_date
347 );
348
349 -- invoke the charges api to create the waiver adjustment charge
350 igs_fi_wav_utils_002.call_charges_api (
351 p_n_person_id => p_person_id ,
352 p_v_fee_cal_type => p_fee_cal_type ,
353 p_n_fee_ci_seq_number => p_fee_ci_seq_number ,
354 p_v_waiver_name => p_waiver_name ,
355 p_v_adj_fee_type => rec_c_waiver_pgms.adjustment_fee_type ,
356 p_v_currency_cd => p_currency_cd ,
357 p_n_waiver_amt => ABS(p_waiver_amount) ,
358 p_d_gl_date => p_gl_date ,
359 p_n_invoice_id => l_n_invoice_id ,
360 x_return_status => l_v_return_status
361 );
362
363 IF l_v_return_status <> 'S' THEN
364 log_to_fnd(p_v_module => 'create_manual_waivers',
365 p_v_string => ' Procedure igs_fi_wav_utils_002.call_charges_api errored out'
366 );
367 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
368 fnd_message.set_name('IGS','IGS_FI_WAV_NO_TRANS_CREATED');
369 fnd_msg_pub.add;
370 RAISE fnd_api.g_exc_error;
371 END IF;
372 END IF;
373
374 log_to_fnd(p_v_module => 'create_manual_waivers',
375 p_v_string => ' Procedure igs_fi_wav_utils_002.call_charges_api returned success'||
376 ' p_n_invoice_id : ' ||l_n_invoice_id
377 );
378
379 log_to_fnd(p_v_module => 'create_manual_waivers',
380 p_v_string => ' Invoking Procedure igs_fi_wav_utils_001.apply_waivers with input parameters '||
381 ' p_n_person_id : ' ||p_person_id ||
382 ' p_v_fee_cal_type : ' ||p_fee_cal_type ||
383 ' p_n_fee_ci_seq_number : ' ||p_fee_ci_seq_number ||
384 ' p_v_waiver_name : ' ||p_waiver_name ||
385 ' p_v_target_fee_type : ' ||rec_c_waiver_pgms.target_fee_type ||
386 ' p_v_adj_fee_type : ' ||rec_c_waiver_pgms.adjustment_fee_type ||
387 ' p_v_waiver_method_code : ' ||rec_c_waiver_pgms.waiver_method_code ||
388 ' p_v_waiver_mode_code : ' ||rec_c_waiver_pgms.waiver_mode_code ||
389 ' p_v_currency_cd : ' ||p_currency_cd ||
390 ' p_d_gl_date : ' ||p_gl_date
391 );
392
393 l_v_return_status := NULL;
394
395 -- invoke apply waiver routine
396 igs_fi_wav_utils_001.apply_waivers (
397 p_n_person_id => p_person_id ,
398 p_v_fee_cal_type => p_fee_cal_type ,
399 p_n_fee_ci_seq_number => p_fee_ci_seq_number ,
400 p_v_waiver_name => p_waiver_name ,
401 p_v_target_fee_type => rec_c_waiver_pgms.target_fee_type ,
402 p_v_adj_fee_type => rec_c_waiver_pgms.adjustment_fee_type ,
403 p_v_waiver_method_code => rec_c_waiver_pgms.waiver_method_code ,
404 p_v_waiver_mode_code => rec_c_waiver_pgms.waiver_mode_code ,
405 p_n_source_credit_id => p_source_credit_id ,
406 p_n_source_invoice_id => NULL ,
407 p_v_currency_cd => p_currency_cd ,
408 p_d_gl_date => p_gl_date ,
409 x_return_status => l_v_return_status
410 );
411
412 IF l_v_return_status <> 'S' THEN
413 log_to_fnd(p_v_module => 'create_manual_waivers',
414 p_v_string => ' Procedure igs_fi_wav_utils_001.apply_waivers errored out'
415 );
416 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
417 fnd_message.set_name('IGS','IGS_FI_WAV_NO_TRANS_CREATED');
418 fnd_msg_pub.add;
419 RAISE fnd_api.g_exc_error;
420 END IF;
421 END IF;
422 x_waiver_adjustment_id := l_n_invoice_id;
423 END IF;
424
425 ----------------------- End of API Body---------------------------
426
427 -- Standard check of p_commit
428 IF fnd_api.to_boolean( p_commit) THEN
429 COMMIT WORK;
430 END IF;
431
432 --Standard call to get message count and if count is 1, get message info.
433 fnd_msg_pub.count_and_get(
434 p_count => x_msg_count,
435 p_data => x_msg_data
436 );
437 EXCEPTION
438 WHEN fnd_api.g_exc_error THEN
439 ROLLBACK TO create_manual_waivers_pub;
440 x_return_status := fnd_api.g_ret_sts_error;
441 fnd_msg_pub.count_and_get(
442 p_count => x_msg_count ,
443 p_data => x_msg_data
444 );
445 WHEN fnd_api.g_exc_unexpected_error THEN
446 ROLLBACK TO create_manual_waivers_pub ;
447 x_return_status := fnd_api.g_ret_sts_unexp_error;
448 fnd_msg_pub.count_and_get(
449 p_count => x_msg_count ,
450 p_data => x_msg_data
451 );
452 WHEN OTHERS THEN
453 ROLLBACK TO create_manual_waivers_pub ;
454 x_return_status := fnd_api.g_ret_sts_unexp_error;
455 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
456 fnd_log.string(fnd_log.level_exception,'igs.plsql.igs_fi_waivers_api_pub.create_manual_waivers.exception','Error : ' || SQLERRM);
457 END IF;
458 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
459 fnd_msg_pub.add_exc_msg(
460 p_pkg_name => g_pkg_name,
461 p_procedure_name => l_api_name
462 );
463 END IF;
464 fnd_msg_pub.count_and_get(
465 p_count => x_msg_count ,
466 p_data => x_msg_data
467 );
468 END create_manual_waivers;
469
470 PROCEDURE validate_parameters(
471 p_fee_cal_type IN igs_ca_inst_all.cal_type%TYPE ,
472 p_fee_ci_seq_number IN igs_ca_inst_all.sequence_number%TYPE ,
473 p_waiver_name IN igs_fi_waiver_pgms.waiver_name%TYPE ,
474 p_person_id IN igs_fi_credits_all.party_id%TYPE ,
475 p_waiver_amount IN igs_fi_credits_all.amount%TYPE ,
476 p_currency_cd IN igs_fi_credits_all.currency_cd%TYPE ,
477 p_gl_date IN igs_fi_credits_all.gl_date%TYPE ,
478 p_source_credit_id IN igs_fi_credits_all.credit_id%TYPE ,
479 p_b_return_status OUT NOCOPY BOOLEAN
480 ) AS
481 ------------------------------------------------------------------
482 --Created by : Sanil Madathil, Oracle IDC
483 --Date created: 04 August 2005
484 --
485 -- Purpose:
486 -- Invoked : from within create_manual_waivers procedure
487 -- Function : Private procedure for validating all the inbound parametere
488 -- to API
489 --
490 -- Parameters : p_fee_cal_type : IN parameter. Required.
491 -- p_fee_ci_seq_number : IN parameter. Required.
492 -- p_waiver_name : IN parameter. Required.
493 -- p_person_id : IN parameter. Required.
494 -- p_waiver_amount : IN parameter. Required.
495 -- p_currency_cd : IN parameter. Required.
496 -- p_gl_date : IN parameter. Required.
497 -- p_source_credit_id : IN parameter. Optional
498 --
499 --Known limitations/enhancements and/or remarks:
500 --
501 --Change History:
502 --Who When What
503 --smadathi 28-Oct-2005 Bug 4704177: Enhancement for Tuition Waiver
504 -- CCR. Added check for the Error Account = 'Y
505 ------------------------------------------------------------------
506
507 rec_c_waiver_pgms c_waiver_pgms%ROWTYPE;
508
509 CURSOR c_credits (
510 cp_n_credit_id IN igs_fi_credits_all.credit_id%TYPE,
511 cp_v_fee_cal_type IN igs_ca_inst_all.cal_type%TYPE,
512 cp_n_fee_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
513 cp_v_waiver_name IN igs_fi_waiver_pgms.waiver_name%TYPE
514 ) IS
515 SELECT crd.fee_cal_type
516 ,crd.fee_ci_sequence_number
517 ,crd.waiver_name
518 ,crd.credit_id
519 ,crd.status
520 ,crd.amount
521 ,crd.unapplied_amount
522 FROM igs_fi_credits_all crd
523 WHERE crd.credit_id = cp_n_credit_id
524 AND crd.fee_cal_type = cp_v_fee_cal_type
525 AND crd.fee_ci_sequence_number = cp_n_fee_sequence_number
526 AND crd.waiver_name = cp_v_waiver_name;
527
528 rec_c_credits c_credits%ROWTYPE;
529
530 CURSOR c_waiver_adj_appl (
531 cp_n_credit_id IN igs_fi_credits_all.credit_id%TYPE,
532 cp_v_fee_cal_type IN igs_ca_inst_all.cal_type%TYPE,
533 cp_n_fee_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
534 cp_v_waiver_name IN igs_fi_waiver_pgms.waiver_name%TYPE
535 ) IS
536 SELECT appl.amount_applied
537 FROM igs_fi_applications appl,
538 igs_fi_inv_int_all inv
539 WHERE appl.credit_id = cp_n_credit_id
540 AND appl.invoice_id = inv.invoice_id
541 AND appl.application_type = 'APP'
542 AND inv.fee_cal_type = cp_v_fee_cal_type
543 AND inv.fee_ci_sequence_number = cp_n_fee_sequence_number
544 AND inv.waiver_name = cp_v_waiver_name
545 AND inv.transaction_type = 'WAIVER_ADJ';
546
547 rec_c_waiver_adj_appl c_waiver_adj_appl%ROWTYPE;
548
549 l_b_ret_status BOOLEAN;
550 l_b_validation_success BOOLEAN;
551 l_b_chr_err_account NUMBER;
552
553 l_v_message_name fnd_new_messages.message_name%TYPE;
554 l_v_ld_cal_type igs_ca_inst.cal_type%TYPE;
555 l_v_ld_ci_seq_number igs_ca_inst.sequence_number%TYPE;
556 l_v_closing_status gl_period_statuses.closing_status%TYPE;
557 l_v_return_status VARCHAR2(1);
558
559 l_n_eligible_amount igs_fi_credits_all.amount%TYPE;
560 l_n_wavadj_amt igs_fi_credits_all.amount%TYPE;
561 BEGIN
562
563 log_to_fnd(p_v_module => 'validate_parameters',
564 p_v_string => ' Entered Procedure validate_parameters: The input parameters are '||
565 ' p_fee_cal_type : ' ||p_fee_cal_type ||
566 ' p_fee_ci_seq_number : ' ||p_fee_ci_seq_number ||
567 ' p_waiver_name : ' ||p_waiver_name ||
568 ' p_person_id : ' ||p_person_id ||
569 ' p_waiver_amount : ' ||p_waiver_amount ||
570 ' p_currency_cd : ' ||p_currency_cd ||
571 ' p_gl_date : ' ||p_gl_date ||
572 ' p_source_credit_id : ' ||p_source_credit_id
573 );
574 -- initialize the return status to TRUE
575 l_b_validation_success := TRUE;
576
577 -- Verify if al the mandatory parameters have been supplied to the API
578 IF ( p_fee_cal_type IS NULL OR p_fee_ci_seq_number IS NULL OR
579 p_waiver_name IS NULL OR p_person_id IS NULL OR
580 p_gl_date IS NULL OR p_currency_cd IS NULL
581 )THEN
582 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
583 fnd_message.set_name('IGS', 'IGS_GE_INSUFFICIENT_PARAMETER');
584 fnd_msg_pub.add;
585 l_b_validation_success := FALSE;
586 END IF;
587 END IF;
588
589 -- Verify if the Fee Calendar Type and Fee Sequence number passed as inbound parameters
590 -- to the API is a valid value or not
591 IF NOT igs_fi_crdapi_util.validate_cal_inst (
592 p_v_cal_type => p_fee_cal_type,
593 p_n_ci_sequence_number => p_fee_ci_seq_number,
594 p_v_s_cal_cat => 'FEE'
595 ) THEN
596 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
597 fnd_message.set_name('IGS', 'IGS_FI_FCI_NOTFOUND');
598 fnd_msg_pub.add;
599 l_b_validation_success := FALSE;
600 END IF;
601 END IF;
602
603 -- Verify load calendar mapping exists in the calendar subsystem for the Fee Calendar Type and
604 -- Fee Sequence number passed as inbound parameters to the API
605 IF p_fee_cal_type IS NOT NULL AND p_fee_ci_seq_number IS NOT NULL THEN
606 igs_fi_crdapi_util.validate_fci_lci_reln (
607 p_v_fee_cal_type => p_fee_cal_type,
608 p_n_fee_ci_sequence_number => p_fee_ci_seq_number,
609 p_v_ld_cal_type => l_v_ld_cal_type,
610 p_n_ld_ci_sequence_number => l_v_ld_ci_seq_number,
611 p_v_message_name => l_v_message_name,
612 p_b_return_stat => l_b_ret_status
613 ) ;
614 IF NOT (l_b_ret_status) THEN
615 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
616 fnd_message.set_name('IGS', l_v_message_name);
617 fnd_msg_pub.add;
618 l_b_validation_success := FALSE;
619 END IF;
620 END IF;
621 END IF;
622
623 -- Verify if inbound parameter p_waiver_name holds a value or not
624 IF p_waiver_name IS NULL THEN
625 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
626 fnd_message.set_name('IGS', 'IGS_FI_WAV_PGM_INVALID');
627 fnd_msg_pub.add;
628 l_b_validation_success := FALSE;
629 END IF;
630 END IF;
631
632 -- Validate if Waiver program exists for the combination of Fee calendar Type,
633 -- Fee Calendar sequence number and waiver program passed as inbound parameters
634 -- to the API
635 IF (p_fee_cal_type IS NOT NULL AND p_fee_ci_seq_number IS NOT NULL AND p_waiver_name IS NOT NULL) THEN
636 OPEN c_waiver_pgms (
637 cp_v_fee_cal_type => p_fee_cal_type ,
638 cp_n_fee_sequence_number => p_fee_ci_seq_number ,
639 cp_v_waiver_name => p_waiver_name
640 );
641 FETCH c_waiver_pgms INTO rec_c_waiver_pgms;
642 IF c_waiver_pgms%NOTFOUND THEN
643 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
644 fnd_message.set_name('IGS', 'IGS_FI_WAV_FEE_CAL_INST');
645 fnd_msg_pub.add;
646 l_b_validation_success := FALSE;
647 END IF;
648 END IF;
649 CLOSE c_waiver_pgms;
650
651 -- Validate the status of waiver program retrieved by the cusor
652 -- API should process only waiver programs of status "Active"
653 IF rec_c_waiver_pgms.waiver_status_code <> 'ACTIVE' THEN
654 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
655 fnd_message.set_name('IGS', 'IGS_FI_WAV_PGM_INACTIVE');
656 fnd_msg_pub.add;
657 l_b_validation_success := FALSE;
658 END IF;
659 END IF;
660
661 -- Validate if the Waiver program belongs to category "Manual"
662 -- API should process Manual waiver programs
663 IF rec_c_waiver_pgms.waiver_method_code <> 'MANUAL' THEN
664 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
665 fnd_message.set_name('IGS', 'IGS_FI_WAV_METHOD_INVALID');
666 fnd_msg_pub.add;
667 l_b_validation_success := FALSE;
668 END IF;
669 END IF;
670
671 -- Validate the Manual waiver program is of type Fee Level
672 -- API should process Manual waiver program is of type Fee Level
673 IF rec_c_waiver_pgms.waiver_mode_code <> 'FEE_LEVEL' THEN
674 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
675 fnd_message.set_name('IGS', 'IGS_FI_WAV_MODE_INVALID');
676 fnd_message.set_token('WAIV_PGM',rec_c_waiver_pgms.waiver_name);
677 fnd_msg_pub.add;
678 l_b_validation_success := FALSE;
679 END IF;
680 END IF;
681 END IF;
682
683 -- Validate if the person id passed as inbound parameter to the API is valid or not
684 IF igs_fi_gen_007.validate_person ( p_person_id) = 'N' THEN
685 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
686 fnd_message.set_name('IGS', 'IGS_FI_INVALID_PERSON');
687 fnd_message.set_token('PERSON_ID',p_person_id);
688 fnd_msg_pub.add;
689 l_b_validation_success := FALSE;
690 END IF;
691 END IF;
692
693 -- Validate if the value of the inbound parameter P_GL_DATE is within an Open or Future period
694 l_v_message_name := NULL;
695 igs_fi_gen_gl.get_period_status_for_date(
696 p_d_date => p_gl_date,
697 p_v_closing_status => l_v_closing_status,
698 p_v_message_name => l_v_message_name
699 );
700
701 IF l_v_message_name IS NOT NULL THEN
702 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
703 fnd_message.set_name('IGS', l_v_message_name);
704 fnd_msg_pub.add;
705 l_b_validation_success := FALSE;
706 END IF;
707 END IF;
708
709 IF l_v_closing_status NOT IN ('O','F') THEN
710 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
711 fnd_message.set_name('IGS', 'IGS_FI_INVALID_GL_DATE');
712 fnd_message.set_token('GL_DATE',p_gl_date);
713 fnd_msg_pub.add;
714 l_b_validation_success := FALSE;
715 END IF;
716 END IF;
717
718 -- Validate if value of the inbound parameter P_CURRENCY_CD is active in the System
719 IF NOT igs_fi_crdapi_util.validate_curr ( p_v_currency_cd => p_currency_cd) THEN
720 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
721 fnd_message.set_name('IGS', 'IGS_FI_INVALID_CUR');
722 fnd_message.set_token('CUR_CD', p_currency_cd);
723 fnd_msg_pub.add;
724 l_b_validation_success := FALSE;
725 END IF;
726 END IF;
727
728 -- Validate the waiver amount
729 IF p_waiver_amount = 0 OR p_waiver_amount IS NULL THEN
730 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
731 fnd_message.set_name('IGS', 'IGS_FI_WAV_INVALID_AMT');
732 fnd_msg_pub.add;
733 l_b_validation_success := FALSE;
734 END IF;
735 END IF;
736
737 -- Check if the Charge transactions for the Person, Fee Type and Fee Period combination
738 -- have the Error Account flag set.
739 l_b_chr_err_account := igs_fi_wav_utils_002.check_chg_error_account (
740 p_n_person_id => p_person_id,
741 p_v_fee_type => rec_c_waiver_pgms.target_fee_type,
742 p_v_fee_cal_type => p_fee_cal_type,
743 p_n_fee_ci_seq_number => p_fee_ci_seq_number
744 );
745 IF (l_b_chr_err_account=1) THEN
746 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
747 fnd_message.set_name('IGS','IGS_FI_WAV_CHG_ERR');
748 fnd_message.set_token('PERSON',igs_fi_gen_008.get_party_number(p_n_party_id => p_person_id));
749 fnd_message.set_token('FEE_TYPE',rec_c_waiver_pgms.target_fee_type);
750 fnd_message.set_token('FEE_PERIOD',
751 igs_ca_gen_001.calp_get_alt_cd(p_cal_type => p_fee_cal_type,
752 p_sequence_number => p_fee_ci_seq_number)
753 );
754 fnd_msg_pub.add;
755 l_b_validation_success := FALSE;
756 END IF;
757 END IF;
758
759 IF NVL(p_waiver_amount,0) > 0 THEN
760 IF p_source_credit_id IS NOT NULL THEN
761 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
762 fnd_message.set_name('IGS', 'IGS_FI_WAV_CRD_TXN_ID');
763 fnd_msg_pub.add;
764 l_b_validation_success := FALSE;
765 END IF;
766 END IF;
767
768 -- Determine the maximum amount that can be actually waived for the Person Id, target fee type
769 -- the value of TARGET_FEE_TYPE column obtained from the cursor c_waiver_pgms select list )
770 -- and Fee calendar instance combination.
771 log_to_fnd(p_v_module => 'validate_parameters',
772 p_v_string => ' Invoking Procedure igs_fi_wav_utils_001.get_eligible_waiver_amt with input parameters '||
773 ' p_n_person_id : ' ||p_person_id ||
774 ' p_v_fee_cal_type : ' ||p_fee_cal_type ||
775 ' p_n_fee_ci_seq_number : ' ||p_fee_ci_seq_number ||
776 ' p_v_waiver_name : ' ||p_waiver_name ||
777 ' p_v_target_fee_type : ' ||rec_c_waiver_pgms.target_fee_type ||
778 ' p_v_waiver_method_code : ' ||rec_c_waiver_pgms.waiver_method_code ||
779 ' p_v_waiver_mode_code : ' ||rec_c_waiver_pgms.waiver_mode_code
780 );
781
782 igs_fi_wav_utils_001.get_eligible_waiver_amt(
783 p_n_person_id => p_person_id ,
784 p_v_fee_cal_type => p_fee_cal_type ,
785 p_n_fee_ci_seq_number => p_fee_ci_seq_number ,
786 p_v_waiver_name => p_waiver_name ,
787 p_v_target_fee_type => rec_c_waiver_pgms.target_fee_type ,
788 p_v_waiver_method_code => rec_c_waiver_pgms.waiver_method_code ,
789 p_v_waiver_mode_code => rec_c_waiver_pgms.waiver_mode_code ,
790 p_n_source_invoice_id => NULL ,
791 x_return_status => l_v_return_status ,
792 x_eligible_amount => l_n_eligible_amount
793 );
794
795 IF l_v_return_status = 'E' THEN
796 log_to_fnd(p_v_module => 'validate_parameters',
797 p_v_string => ' The Procedure igs_fi_wav_utils_001.get_eligible_waiver_amt returned a status of error '
798 );
799 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
800 fnd_message.set_name('IGS', 'IGS_FI_WAV_ELIG_AMT_FAIL');
801 fnd_msg_pub.add;
802 l_b_validation_success := FALSE;
803 END IF;
804 END IF;
805
806 log_to_fnd(p_v_module => 'validate_parameters',
807 p_v_string => ' The Procedure igs_fi_wav_utils_001.get_eligible_waiver_amt returned a status of error '||
808 ' Eligible waiver amount : ' ||NVL(l_n_eligible_amount,0)
809 );
810
811 IF ((NVL(p_waiver_amount,0) - NVL(l_n_eligible_amount,0))>0) THEN
812 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
813 fnd_message.set_name('IGS', 'IGS_FI_WAV_GREATER_AMT');
814 fnd_msg_pub.add;
815 l_b_validation_success := FALSE;
816 END IF;
817 END IF;
818
819 ELSIF NVL(p_waiver_amount,0) < 0 THEN
820 IF p_source_credit_id IS NULL THEN
821 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
822 fnd_message.set_name('IGS', 'IGS_FI_WAV_SRC_CRD_MAND');
823 fnd_msg_pub.add;
824 l_b_validation_success := FALSE;
825 END IF;
826 END IF;
827 -- Determine if a waiver credit record already exists in the system for the P_SOURCE_CREDIT_ID
828 -- passed as inbound parameter to the API
829 OPEN c_credits (
830 cp_n_credit_id => p_source_credit_id ,
831 cp_v_fee_cal_type => p_fee_cal_type ,
832 cp_n_fee_sequence_number => p_fee_ci_seq_number ,
833 cp_v_waiver_name => p_waiver_name
834 );
835 FETCH c_credits INTO rec_c_credits;
836 IF c_credits%NOTFOUND THEN
837 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
838 fnd_message.set_name('IGS', 'IGS_FI_WAV_SRC_CRD_INVALID');
839 fnd_msg_pub.add;
840 l_b_validation_success := FALSE;
841 END IF;
842 END IF;
843 CLOSE c_credits;
844
845 -- Validate the status of the waiver credit
846 IF rec_c_credits.status <> 'CLEARED' THEN
847 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
848 fnd_message.set_name('IGS', 'IGS_FI_WAV_SRC_CRD_REVERSED');
849 fnd_msg_pub.add;
850 l_b_validation_success := FALSE;
851 END IF;
852 END IF;
853
854 -- The amount of Waiver Charge Adjustment to be created should be less than the
855 -- Waiver Credit amount less any prior waiver charge adjustments applied to it
856 l_n_wavadj_amt := 0;
857 FOR rec_c_waiver_adj_appl IN c_waiver_adj_appl (
858 cp_n_credit_id => p_source_credit_id ,
859 cp_v_fee_cal_type => p_fee_cal_type ,
860 cp_n_fee_sequence_number => p_fee_ci_seq_number ,
861 cp_v_waiver_name => p_waiver_name
862 )
863 LOOP
864 l_n_wavadj_amt := NVL(l_n_wavadj_amt,0) + rec_c_waiver_adj_appl.amount_applied ;
865 END LOOP;
866
867 log_to_fnd(p_v_module => 'validate_parameters',
868 p_v_string => ' p_waiver_amount : ' ||p_waiver_amount ||
869 ' prior waiver charge adjustments : ' ||l_n_wavadj_amt
870 );
871
872 IF ABS(p_waiver_amount) > (NVL(rec_c_credits.amount,0) - l_n_wavadj_amt) THEN
873 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
874 fnd_message.set_name('IGS', 'IGS_FI_WAV_ADJ_AMT_GREATER');
875 fnd_msg_pub.add;
876 l_b_validation_success := FALSE;
877 END IF;
878 END IF;
879 END IF;
880
881 IF NOT (l_b_validation_success) THEN
882 log_to_fnd(p_v_module => 'validate_parameters',
883 p_v_string => ' Procedure returning status of failure '
884 );
885 p_b_return_status := FALSE;
886 END IF;
887
888 END validate_parameters;
889
890
891 PROCEDURE log_to_fnd (
892 p_v_module IN VARCHAR2,
893 p_v_string IN VARCHAR2
894 ) AS
895 ------------------------------------------------------------------
896 --Created by : Sanil Madathil, Oracle IDC
897 --Date created: 04 August 2005
898 --
899 -- Purpose:
900 -- Invoked : from within API
901 -- Function : Private procedure for logging all the statement level
902 -- messages
903 -- Parameters : p_v_module : IN parameter. Required.
904 -- p_v_string : IN parameter. Required.
905 --
906 --
907 --Known limitations/enhancements and/or remarks:
908 --
909 --Change History:
910 --Who When What
911 ------------------------------------------------------------------
912 BEGIN
913
914 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
915 fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_fi_waivers_api_pub.'||p_v_module, p_v_string);
916 END IF;
917 END log_to_fnd;
918
919 END igs_fi_waivers_api_pub;