1 PACKAGE BODY igs_fi_credits_api_pub AS
2 /* $Header: IGSFI54B.pls 120.5 2005/07/27 13:00:04 appldev ship $ */
3
4
5 /*Change History
6
7 Who When What
8 pmarada 26-JUL-2005 Enh 3392095, modifed as per tution waiver build, passing p_api_version
9 parameter value as 2.1 to the igs_fi_credit_pvt.create_credit call
10 svuppala 9-JUN-2005 Enh 4213629 - The automatic generation of the Receipt Number.
11 Added a new procedure, create_credit with all the parameters of existing procedure
12 and additional OUT parameter, x_credit_number
13 vvutukur 16-Jun-2003 Enh#2831582.Lockbox Build.Modified procedure create_credit.
14 schodava 11-Jun-2003 Enh # 2831587. Added 3 new parameters to the Public API
15 vvutukur 04-Apr-2003 Enh#2831554. Internal Credits API Build. Rewritten the code by placing a call to igs_fi_credit_pvt.create_credit and
16 removing earlier the code.
17 vvutukur 11-Dec-2002 Enh#2584741.Removed parameter p_validation_level and Added 3 new parameters p_v_check_number,
18 p_v_source_tran_type,p_v_source_tran_ref_number in create_credit procedure.Modified
19 validate_credit_type,created local functions validate_unique_enrdeposit, validate_dep_crtype
20 and changes as specified in TD.
21 vvutukur 23-Nov-2002 Enh#2584986.Added local function validate_gl_date.Modifications done in create_credit.
22 Removed local procedure get_local_curr and modified validate_cur.
23 vvutukur 07-Oct-2002 Enh#2562745.Reassess Balances Build. Modifications done in create_credit
24 procedure and validate_lkp function.
25 smvk 16-Sep-2002 Removed the parameter subaccount_id and the associated code as a part of Bug # 2564643.
26 Removed the default used in this api to overcome gscc warnings.
27 smadathi 03-Jul-2002 Bug 2443082. Modified create_credit procedure call.
28 vchappid 13-Jun-2002 Bug#2411529, Incorrectly used message name has been modified
29 vvutukur 27-02-2002 removed local function validate_person and called generic function
30 igs_fi_gen_007.validate_person in create_credit procedure.as part of bug:2238362
31 ------------------------------------------------------------------------------------------*/
32
33 PROCEDURE create_credit(p_api_version IN NUMBER,
34 p_init_msg_list IN VARCHAR2,
35 p_commit IN VARCHAR2,
36 p_credit_number IN igs_fi_credits_all.credit_number%TYPE,
37 p_credit_status IN igs_fi_credits_all.status%TYPE ,
38 p_credit_source IN igs_fi_credits_all.credit_source%TYPE,
39 p_party_id IN igs_fi_credits_all.party_id%TYPE,
40 p_credit_type_id IN igs_fi_credits_all.credit_type_id%TYPE,
41 p_credit_instrument IN igs_fi_credits_all.credit_instrument%TYPE,
42 p_description IN igs_fi_credits_all.description%TYPE,
43 p_amount IN igs_fi_credits_all.amount%TYPE,
44 p_currency_cd IN igs_fi_credits_all.currency_cd%TYPE,
45 p_exchange_rate IN igs_fi_credits_all.exchange_rate%TYPE,
46 p_transaction_date IN igs_fi_credits_all.transaction_date%TYPE,
47 p_effective_date IN igs_fi_credits_all.effective_date%TYPE,
48 p_source_transaction_id IN igs_fi_credits_all.source_transaction_id%TYPE,
49 /* Removed the parameter p_subaccount_id as a part of Bug # 2564643 */
50 p_receipt_lockbox_number IN igs_fi_credits_all.receipt_lockbox_number%TYPE,
51 p_credit_card_code IN igs_fi_credits_all.credit_card_code%TYPE,
52 p_credit_card_holder_name IN igs_fi_credits_all.credit_card_holder_name%TYPE,
53 p_credit_card_number IN igs_fi_credits_all.credit_card_number%TYPE,
54 p_credit_card_expiration_date IN igs_fi_credits_all.credit_card_expiration_date%TYPE,
55 p_credit_card_approval_code IN igs_fi_credits_all.credit_card_approval_code%TYPE,
56 p_attribute_record IN attribute_rec_type,
57 p_invoice_id IN igs_fi_inv_int_all.invoice_id%TYPE,--bug:2195715
58 /* Parameters added as part of bug:2191470 - sfcr020 */
59 p_awd_yr_cal_type IN igs_fi_credits_all.awd_yr_cal_type%TYPE,
60 p_awd_yr_ci_sequence_number IN igs_fi_credits_all.awd_yr_ci_sequence_number%TYPE,
61 p_fee_cal_type IN igs_fi_credits_all.fee_cal_type%TYPE,
62 p_fee_ci_sequence_number IN igs_fi_credits_all.fee_ci_sequence_number%TYPE,
63 p_d_gl_date IN igs_fi_credits_all.gl_date%TYPE,
64 /* Parameters added as part of bug:2191470 - sfcr020 */
65 x_credit_id OUT NOCOPY igs_fi_credits_all.credit_id%TYPE,
66 x_credit_activity_id OUT NOCOPY igs_fi_cr_activities.credit_activity_id%TYPE,
67 x_return_status OUT NOCOPY VARCHAR2,
68 x_msg_count OUT NOCOPY NUMBER,
69 x_msg_data OUT NOCOPY VARCHAR2,
70 p_v_check_number IN VARCHAR2,
71 p_v_source_tran_type IN VARCHAR2,
72 p_v_source_tran_ref_number IN VARCHAR2,
73 p_v_credit_card_payee_cd IN VARCHAR2,
74 p_v_credit_card_status_code IN VARCHAR2,
75 p_v_credit_card_tangible_cd IN VARCHAR2,
76 p_lockbox_interface_id IN igs_fi_credits_all.lockbox_interface_id%TYPE,
77 p_batch_name IN igs_fi_credits_all.batch_name%TYPE,
78 p_deposit_date IN igs_fi_credits_all.deposit_date%TYPE
79 )AS
80 /***********************************************************************************************
81
82 Created By: Amit Gairola
83
84 Date Created By: 05-05-2001
85
86 Purpose: This procedure is the main api call.
87
88 Known limitations,enhancements,remarks:
89
90 Change History
91
92 Who When What
93 svuppala 9-JUN-2005 Enh 4213629 - The automatic generation of the Receipt Number.
94 Removed all the logic and called new create_credit procedure with added x_credit_number.
95 vvutukur 16-Jun-2003 Enh#2831582.Lockbox Build. Added 3 new parameters lockbox_interface_id,batch_name,deposit_date.
96 schodava 11-Jun-2003 Enh# 2831587. Added 3 new parameters to the Public API
97 vvutukur 04-Apr-2003 Enh#2831554. Internal Credits API Build. Rewritten the code by placing a call to igs_fi_credit_pvt.create_credit and
98 removing earlier the code.
99 vvutukur 11-Dec-2002 Enh#2584741.Removed parameter p_validation_level. Added 3 new parameters p_v_check_number,
100 p_v_source_tran_type,p_v_source_tran_ref_number.
101 vvutukur 23-Nov-2002 Enh#2584986.Added new parameter p_d_gl_date and validated.Passed p_d_gl_date to the call to
102 igs_fi_credits_pkg.insert_row,and p_d_gl_date,gl_posted_date,posting_control_id to the call
103 to igs_fi_crd_activities_pkg.insert_row. Modified the logic of validation of currency_cd
104 and calculation of exchange_rate as exchange has been made mandatory if currency passed is
105 other than local currency specified in System Options Form. If currency is passed is same
106 as the one that is specified in System Options form, exchange rate is non-mandatory
107 and even if it is passed with some value, it will be ignored and has been considered as 1.
108 vvutukur 07-Oct-2002 Enh#2562745.1)Added two validations to error out NOCOPY of credits api a)if this procedure
109 is called while holds conversion process is running b)if no active balance rule
110 exists for HOLDS.2)Added code to call Update_balances procedure to update/create
111 Holds balance in igs_fi_balances table real time whenever a credit gets created
112 (just like STANDARD balance real time updation).
113 smvk 16-Sep-2002 Removed the parameter p_subaccount_id as a part of Bug # 2564643
114 Removed the local paramter l_psa.
115 smadathi 03-Jul-2002 Bug 2443082. Modified update_balances procedure call. Modified to pass transaction date
116 instead of system date.
117 vchappid 13-Jun-2002 Bug#2411529, Incorrectly used message name has been modified
118 SYKRISHN 19-APR-2002 Bug 2324088 - Introduced Desc Flex Field Validations.
119 vvutukur 27-02-2002 placed call to igs_fi_gen_007.validate_person instead of calling local function
120 validate_person.for bug:2238362
121 jbegum 12-Feb-2001 As part of Enh bug # 2201081
122 Added call to IGS_FI_GEN_005.validate_psa and IGS_FI_PARTY_SA_PKG.insert_row
123 sykrishn 4-FEB-2002 Added the new IN parameters p_awd_yr_cal_type,p_awd_yr_ci_sequence_number,p_fee_cal_type,p_fee_ci_sequence_number
124 These parameters are mandatory when the Credit Class is Of Internal or External Financial Aid
125 Changes realted to credit source- refer DLD - sfcr020
126 Validations for Fee Period - LCI relation
127 IGS_LOOKUPS -check for credit source
128
129 vvutukur 31-Jan-2002 Added new parameter p_invoice_id and logic,if accounting method is ACCRUAL,
130 the default clearing account defined for the Adjustment Credit type gets
131 overridden by the revenue account of the charge being adjusted.
132 sarakshi 18-dec-2001 Removed the parameters p_source_date,p_fee_type,p_credit_type_id from
133 the call to procedure Update_Balances and added parameter p_source_id
134 as a part of Enh. bug:2124001
135 sarakshi 8-oct-2001 Replaced procedure igs_fi_prc_balances.calculate_balances with
136 igs_fi_prc_balances.update_balances also removed balance_flag parameter
137 from call to insert row of credits table.bug no:2030448
138 msrinivi 13 Aug,2001 Call to build process to populate rev/rec ccid/code before
139 inserting intoigs_fi_activities table
140 ********************************************************************************************** */
141
142 l_credit_number igs_fi_credits_all.credit_number%TYPE;
143
144 BEGIN
145
146 create_credit( p_api_version => p_api_version,
147 p_init_msg_list => p_init_msg_list,
148 p_commit => p_commit ,
149 p_credit_status => p_credit_status ,
150 p_credit_source => p_credit_source ,
151 p_party_id => p_party_id ,
152 p_credit_type_id => p_credit_type_id ,
153 p_credit_instrument => p_credit_instrument ,
154 p_description => p_description ,
155 p_amount => p_amount ,
156 p_currency_cd => p_currency_cd ,
157 p_exchange_rate => p_exchange_rate ,
158 p_transaction_date => p_transaction_date ,
159 p_effective_date => p_effective_date ,
160 p_source_transaction_id => p_source_transaction_id ,
161 p_receipt_lockbox_number => p_receipt_lockbox_number ,
162 p_credit_card_code => p_credit_card_code ,
163 p_credit_card_holder_name => p_credit_card_holder_name,
164 p_credit_card_number => p_credit_card_number ,
165 p_credit_card_expiration_date => p_credit_card_expiration_date ,
166 p_credit_card_approval_code => p_credit_card_approval_code ,
167 p_attribute_record => p_attribute_record ,
168 p_invoice_id => p_invoice_id ,
169 p_awd_yr_cal_type => p_awd_yr_cal_type ,
170 p_awd_yr_ci_sequence_number => p_awd_yr_ci_sequence_number ,
171 p_fee_cal_type => p_fee_cal_type ,
172 p_fee_ci_sequence_number => p_fee_ci_sequence_number ,
173 p_d_gl_date => p_d_gl_date ,
174 x_credit_id => x_credit_id ,
175 x_credit_activity_id => x_credit_activity_id ,
176 x_return_status => x_return_status ,
177 x_msg_count => x_msg_count ,
178 x_msg_data => x_msg_data ,
179 p_v_check_number => p_v_check_number ,
180 p_v_source_tran_type => p_v_source_tran_type ,
181 p_v_source_tran_ref_number => p_v_source_tran_ref_number ,
182 p_v_credit_card_payee_cd => p_v_credit_card_payee_cd ,
183 p_v_credit_card_status_code => p_v_credit_card_status_code ,
184 p_v_credit_card_tangible_cd => p_v_credit_card_tangible_cd ,
185 p_lockbox_interface_id => p_lockbox_interface_id ,
186 p_batch_name => p_batch_name ,
187 p_deposit_date => p_deposit_date ,
188 x_credit_number => l_credit_number
189 );
190
191 END create_credit;
192
193
194 PROCEDURE create_credit(p_api_version IN NUMBER,
195 p_init_msg_list IN VARCHAR2,
196 p_commit IN VARCHAR2,
197 p_credit_status IN igs_fi_credits_all.status%TYPE ,
198 p_credit_source IN igs_fi_credits_all.credit_source%TYPE,
199 p_party_id IN igs_fi_credits_all.party_id%TYPE,
200 p_credit_type_id IN igs_fi_credits_all.credit_type_id%TYPE,
201 p_credit_instrument IN igs_fi_credits_all.credit_instrument%TYPE,
202 p_description IN igs_fi_credits_all.description%TYPE,
203 p_amount IN igs_fi_credits_all.amount%TYPE,
204 p_currency_cd IN igs_fi_credits_all.currency_cd%TYPE,
205 p_exchange_rate IN igs_fi_credits_all.exchange_rate%TYPE,
206 p_transaction_date IN igs_fi_credits_all.transaction_date%TYPE,
207 p_effective_date IN igs_fi_credits_all.effective_date%TYPE,
208 p_source_transaction_id IN igs_fi_credits_all.source_transaction_id%TYPE,
209 p_receipt_lockbox_number IN igs_fi_credits_all.receipt_lockbox_number%TYPE,
210 p_credit_card_code IN igs_fi_credits_all.credit_card_code%TYPE,
211 p_credit_card_holder_name IN igs_fi_credits_all.credit_card_holder_name%TYPE,
212 p_credit_card_number IN igs_fi_credits_all.credit_card_number%TYPE,
213 p_credit_card_expiration_date IN igs_fi_credits_all.credit_card_expiration_date%TYPE,
214 p_credit_card_approval_code IN igs_fi_credits_all.credit_card_approval_code%TYPE,
215 p_attribute_record IN attribute_rec_type,
216 p_invoice_id IN igs_fi_inv_int_all.invoice_id%TYPE,
217 p_awd_yr_cal_type IN igs_fi_credits_all.awd_yr_cal_type%TYPE,
218 p_awd_yr_ci_sequence_number IN igs_fi_credits_all.awd_yr_ci_sequence_number%TYPE,
219 p_fee_cal_type IN igs_fi_credits_all.fee_cal_type%TYPE,
220 p_fee_ci_sequence_number IN igs_fi_credits_all.fee_ci_sequence_number%TYPE,
221 p_d_gl_date IN igs_fi_credits_all.gl_date%TYPE,
222 x_credit_id OUT NOCOPY igs_fi_credits_all.credit_id%TYPE,
223 x_credit_activity_id OUT NOCOPY igs_fi_cr_activities.credit_activity_id%TYPE,
224 x_return_status OUT NOCOPY VARCHAR2,
225 x_msg_count OUT NOCOPY NUMBER,
226 x_msg_data OUT NOCOPY VARCHAR2,
227 p_v_check_number IN VARCHAR2,
228 p_v_source_tran_type IN VARCHAR2,
229 p_v_source_tran_ref_number IN VARCHAR2,
230 p_v_credit_card_payee_cd IN VARCHAR2,
231 p_v_credit_card_status_code IN VARCHAR2,
232 p_v_credit_card_tangible_cd IN VARCHAR2,
233 p_lockbox_interface_id IN igs_fi_credits_all.lockbox_interface_id%TYPE,
234 p_batch_name IN igs_fi_credits_all.batch_name%TYPE,
235 p_deposit_date IN igs_fi_credits_all.deposit_date%TYPE,
236 x_credit_number OUT NOCOPY igs_fi_credits_all.credit_number%TYPE
237 )AS
238
239 /***********************************************************************************************
240
241 Created By: Sunil Vuppala
242
243 Date Created By: 10-06-2005
244
245 Purpose: This procedure is the modified api call.
246 Enh 4213629 - The automatic generation of the Receipt Number.
247
248 Known limitations,enhancements,remarks:
249
250 Change History
251
252 Who When What
253
254 ********************************************************************************************** */
255
256 l_pkg_name CONSTANT VARCHAR2(30) := 'IGS_FI_CREDITS_API_PUB';
257 l_api_name CONSTANT VARCHAR2(30) := 'create_credit';
258 l_api_version CONSTANT NUMBER := 1.3;
259
260 l_credit_rec igs_fi_credit_pvt.credit_rec_type;
261 l_attribute_rec_type attribute_rec_type;
262
263
264 BEGIN
265
266 SAVEPOINT create_credit_pub;
267
268 --Check for the Compatible API call.
269 IF NOT fnd_api.compatible_api_call( l_api_version,
270 p_api_version,
271 l_api_name,
272 l_pkg_name) THEN
273 --if the versions of the API and the version passed are different then raise the unexpected error message.
274 RAISE fnd_api.g_exc_unexpected_error;
275 END IF;
276
277 --If the calling program has passed the parameter for initializing the message list.
278 IF fnd_api.to_boolean(p_init_msg_list) THEN
279 --then call the Initialize program of the fnd_msg_pub package.
280 fnd_msg_pub.initialize;
281 END IF;
282
283 l_credit_rec.p_credit_status := p_credit_status;
284 l_credit_rec.p_credit_source := p_credit_source;
285 l_credit_rec.p_party_id := p_party_id;
286 l_credit_rec.p_credit_type_id := p_credit_type_id;
287 l_credit_rec.p_credit_instrument := p_credit_instrument;
288 l_credit_rec.p_description := p_description;
289 l_credit_rec.p_amount := p_amount;
290 l_credit_rec.p_currency_cd := p_currency_cd;
291 l_credit_rec.p_exchange_rate := p_exchange_rate;
292 l_credit_rec.p_transaction_date := p_transaction_date;
293 l_credit_rec.p_effective_date := p_effective_date;
294 l_credit_rec.p_source_transaction_id := p_source_transaction_id;
295 l_credit_rec.p_receipt_lockbox_number := p_receipt_lockbox_number;
296 l_credit_rec.p_credit_card_code := p_credit_card_code;
297 l_credit_rec.p_credit_card_holder_name := p_credit_card_holder_name;
298 l_credit_rec.p_credit_card_number := p_credit_card_number;
299 l_credit_rec.p_credit_card_expiration_date:= p_credit_card_expiration_date;
300 l_credit_rec.p_credit_card_approval_code := p_credit_card_approval_code;
301 l_credit_rec.p_invoice_id := p_invoice_id;
302 l_credit_rec.p_awd_yr_cal_type := p_awd_yr_cal_type;
303 l_credit_rec.p_awd_yr_ci_sequence_number := p_awd_yr_ci_sequence_number;
304 l_credit_rec.p_fee_cal_type := p_fee_cal_type;
305 l_credit_rec.p_fee_ci_sequence_number := p_fee_ci_sequence_number;
306 l_credit_rec.p_check_number := p_v_check_number;
307 l_credit_rec.p_source_tran_type := p_v_source_tran_type;
308 l_credit_rec.p_source_tran_ref_number := p_v_source_tran_ref_number;
309 l_credit_rec.p_gl_date := p_d_gl_date;
310 l_credit_rec.p_v_credit_card_payee_cd := p_v_credit_card_payee_cd;
311 l_credit_rec.p_v_credit_card_status_code := p_v_credit_card_status_code;
312 l_credit_rec.p_v_credit_card_tangible_cd := p_v_credit_card_tangible_cd;
313 l_credit_rec.p_lockbox_interface_id := p_lockbox_interface_id;
314 l_credit_rec.p_batch_name := p_batch_name;
315 l_credit_rec.p_deposit_date := p_deposit_date;
316
317 l_attribute_rec_type := p_attribute_record;
318
319 --Call the private api with full validation_level.
320 igs_fi_credit_pvt.create_credit( p_api_version => 2.1,
321 p_init_msg_list => p_init_msg_list,
322 p_commit => p_commit,
323 p_validation_level => fnd_api.g_valid_level_full,
324 x_return_status => x_return_status,
325 x_msg_count => x_msg_count,
326 x_msg_data => x_msg_data,
327 p_credit_rec => l_credit_rec,
328 p_attribute_record => l_attribute_rec_type,
329 x_credit_id => x_credit_id,
330 x_credit_activity_id => x_credit_activity_id,
331 x_credit_number => x_credit_number
332 );
333 EXCEPTION
334 WHEN fnd_api.g_exc_error THEN
335 ROLLBACK TO create_credit_pub;
336 x_return_status := fnd_api.g_ret_sts_error;
337 fnd_msg_pub.count_and_get( p_count => x_msg_count,
338 p_data => x_msg_data);
339
340 WHEN fnd_api.g_exc_unexpected_error THEN
341 ROLLBACK TO create_credit_pub;
342 x_return_status := fnd_api.g_ret_sts_unexp_error;
343 fnd_msg_pub.count_and_get( p_count => x_msg_count,
344 p_data => x_msg_data);
345
346 WHEN OTHERS THEN
347 ROLLBACK TO create_credit_pub;
348 x_return_status := fnd_api.g_ret_sts_unexp_error;
349 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
350 fnd_msg_pub.add_exc_msg(l_pkg_name,
351 l_api_name);
352 END IF;
353 fnd_msg_pub.count_and_get( p_count => x_msg_count,
354 p_data => x_msg_data);
355 END create_credit;
356
357
358 END igs_fi_credits_api_pub;