1 PACKAGE BODY igs_fi_refunds_process AS
2 /* $Header: IGSFI66B.pls 120.4 2006/06/27 14:21:13 skharida noship $ */
3
4 /***********************************************************************************************
5
6 Created By : Sarakshi
7 Date Created By: 1-Mar-2002
8 Purpose : This package is used for transfering data to and fro interface
9 refunds tables.
10 Known limitations,enhancements,remarks:
11 Change History
12 Who When What
13 skharida 26-Jun-2006 Bug# 5208136 - Modified update_refunds proc, removed the obsoleted columns from IGS_FI_REFUNDS_PKG
14 sapanigr 03-May-2006 Enh#3924836 Precision Issue. Modified update_refunds and insert_interface.
15 sapanigr 14-Feb-2006 Bug#5018036 - R12 Repository tuning. Cursors changed in procedures log_person and transfer_to_int.
16 pathipat 23-Apr-2003 Enh 2831569 - Commercial Receivables build
17 Modified transfer_to_int() - added call to chk_manage_account()
18 Added flag g_b_data_found in transfer_int_rec()
19 pathipat 18-Feb-2003 Enh 2747329 - Payables Intg build - Modified transfer_int_rec and transfer_to_int
20 Added global variable g_v_offset
21 vvutukur 03-Jan-2003 Bug#2727216.Modifications done in function lookup_desc and procedure transfer_to_int.
22 vvutukur 19-Nov-2002 Enh#2584986.Modifications done in insert_interface,update_refunds.
23 vchappid 03-Jul-2002 Bug#2442030, Refunds Inbound payment information is obsoleted,
24 reference to payment_date, payment_number, payment_mode is removed from the
25 table IGS_FI_REFUND_INT_ALL
26 Refund Record Status 'COMPLETED' has been removed.
27
28 vchappid 13-Jun-2002 Bug#2411529, Incorrectly used message name has been modified
29 agairola 16-May-2002 For bug fix 2374103, modified log person, transfer_int_rec and update_pay_info
30 vchappid 06-Mar-2002 Enh#2144600, new concurrent manager program update_pay_info
31 ********************************************************************************************** */
32
33 g_five_space CONSTANT VARCHAR2(10) :=' ';
34 g_ten_space CONSTANT VARCHAR2(12) :=' ';
35 g_transferred CONSTANT VARCHAR2(15) :='TRANSFERRED';
36 g_todo CONSTANT VARCHAR2(10) :='TODO';
37 g_v_offset CONSTANT VARCHAR2(15) := 'OFFSET';
38
39 g_b_data_found BOOLEAN := FALSE;
40
41 g_last_person_id igs_pe_person.person_id%TYPE :=NULL;
42 g_update_last_person igs_fi_parties_v.person_id%TYPE :=NULL;
43
44 e_resource_busy EXCEPTION;
45 PRAGMA EXCEPTION_INIT(e_resource_busy,-0054);
46
47 CURSOR cur_ref(cp_person_id igs_pe_person.person_id%TYPE,
48 cp_start_date igs_fi_refunds.voucher_date%TYPE,
49 cp_end_date igs_fi_refunds.voucher_date%TYPE) IS
50 SELECT rfnd.*,rfnd.rowid
51 FROM igs_fi_refunds rfnd
52 WHERE (rfnd.person_id=cp_person_id OR (cp_person_id IS NULL))
53 AND rfnd.transfer_status='TODO'
54 AND rfnd.source_refund_id IS NOT NULL
55 AND (TRUNC(rfnd.voucher_date) >= TRUNC(cp_start_date) OR (cp_start_date IS NULL))
56 AND (TRUNC(rfnd.voucher_date) <= TRUNC(cp_end_date) OR (cp_end_date IS NULL))
57 ORDER BY rfnd.person_id,rfnd.refund_id;
58
59 CURSOR cur_refund(cp_person_id igs_pe_person.person_id%TYPE,
60 cp_start_date igs_fi_refunds.voucher_date%TYPE,
61 cp_end_date igs_fi_refunds.voucher_date%TYPE) IS
62 SELECT rfnd.*,rfnd.rowid
63 FROM igs_fi_refunds rfnd
64 WHERE (rfnd.person_id=cp_person_id OR (cp_person_id IS NULL))
65 AND rfnd.transfer_status='TODO'
66 AND rfnd.source_refund_id IS NULL
67 AND NVL(rfnd.reversal_ind,'N') <> 'Y'
68 AND (TRUNC(rfnd.voucher_date) >= TRUNC(cp_start_date) OR (cp_start_date IS NULL))
69 AND (TRUNC(rfnd.voucher_date) <= TRUNC(cp_end_date) OR (cp_end_date IS NULL))
70 ORDER BY rfnd.person_id,rfnd.refund_id;
71
72 FUNCTION lookup_desc( p_type IN igs_lookups_view.lookup_type%TYPE,
73 p_code IN igs_lookups_view.lookup_code%TYPE)
74 RETURN VARCHAR2 IS
75 /***********************************************************************************************
76
77 Created By : Sarakshi
78 Date Created By: 1-Mar-2002
79 Purpose : To fetch the meaning of a corresponding lookup code of a lookup type
80
81 Known limitations,enhancements,remarks:
82 Change History
83 Who When What
84 vvutukur 03-Jan-2003. Bug#2727216. Removed the logic of deriving lookup meaning and placed the
85 call to generic function igs_fi_gen_gl.get_lkp_meaning,for doing same.
86 ********************************************************************************************** */
87
88 l_cur_desc igs_lookup_values.meaning%TYPE ;
89
90 BEGIN
91 l_cur_desc := igs_fi_gen_gl.get_lkp_meaning(p_v_lookup_type => p_type,
92 p_v_lookup_code => p_code
93 );
94 RETURN l_cur_desc;
95 END lookup_desc;
96
97 PROCEDURE log_person(p_person_id igs_pe_person_v.person_id%TYPE,
98 p_invoice_id igs_fi_refunds.invoice_id%TYPE,
99 p_refund_id igs_fi_refunds.refund_id%TYPE,
100 p_pay_person_id igs_fi_refunds.pay_person_id%TYPE,
101 p_fee_type igs_fi_refunds.fee_type%TYPE,
102 p_fee_cal_type igs_fi_refunds.fee_cal_type%TYPE,
103 p_sequence_number igs_fi_refunds.fee_ci_sequence_number%TYPE,
104 p_reversal_ind igs_fi_refunds.reversal_ind%TYPE,
105 p_rec_status VARCHAR2,
106 p_err_msg fnd_new_messages.message_text%TYPE,
107 p_status BOOLEAN ) AS
108 /***********************************************************************************************
109
110 Created By : Sarakshi
111 Date Created By: 4-Mar-2002
112 Purpose : Logging information related to each refunds record being processed.
113
114 Known limitations,enhancements,remarks:
115 Change History
116 Who When What
117 sapanigr 14-Feb-2006 Bug#5018036 - 1. Cursor cur_date modified to use igs_fi_f_typ_ca_inst_all and igs_ca_inst
118 instead of igs_fi_f_typ_ca_inst_lkp_v
119 2. Cursor cur_pers changed to query hz_parties instead of igs_fi_parties_v
120
121 ********************************************************************************************** */
122
123 CURSOR cur_pers(cp_person_id hz_parties.party_id%TYPE) IS
124 SELECT party_number
125 FROM hz_parties
126 WHERE party_id= cp_person_id;
127 l_cur_pers cur_pers%ROWTYPE;
128
129 CURSOR cur_inv IS
130 SELECT invoice_number
131 FROM igs_fi_inv_int
132 WHERE invoice_id=p_invoice_id;
133 l_cur_inv cur_inv%ROWTYPE;
134
135 CURSOR cur_date IS
136 SELECT ci.start_dt start_dt,ci.end_dt end_dt
137 FROM igs_fi_f_typ_ca_inst_all ftci, igs_ca_inst ci
138 WHERE ftci.fee_type=p_fee_type
139 AND ftci.fee_cal_type=p_fee_cal_type
140 AND ftci.fee_ci_sequence_number=p_sequence_number
141 AND ci.cal_type = ftci.fee_cal_type
142 AND ci.sequence_number = ftci.fee_ci_sequence_number;
143 l_cur_date cur_date%ROWTYPE;
144
145 BEGIN
146
147 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
148 IF g_last_person_id = p_person_id THEN
149 NULL;
150 ELSE
151 OPEN cur_pers(p_person_id);
152 FETCH cur_pers INTO l_cur_pers;
153 CLOSE cur_pers;
154 FND_FILE.PUT_LINE(FND_FILE.LOG,lookup_desc('IGS_FI_LOCKBOX','PERSON')||':'||l_cur_pers.party_number);
155 END IF;
156 g_last_person_id:=p_person_id;
157
158 FND_FILE.PUT_LINE(FND_FILE.LOG,g_five_space||lookup_desc('IGS_FI_LOCKBOX','REFUND_ID')||':'||TO_CHAR(p_refund_id));
159
160
161 OPEN cur_inv;
162 FETCH cur_inv INTO l_cur_inv;
163 CLOSE cur_inv;
164 FND_FILE.PUT_LINE(FND_FILE.LOG,g_ten_space||lookup_desc('IGS_FI_LOCKBOX','INVOICE_NUMBER')||':'||l_cur_inv.invoice_number);
165
166 OPEN cur_pers(p_pay_person_id);
167 FETCH cur_pers INTO l_cur_pers;
168 CLOSE cur_pers;
169 FND_FILE.PUT_LINE(FND_FILE.LOG,g_ten_space||lookup_desc('IGS_FI_LOCKBOX','PAYEE')||':'||l_cur_pers.party_number);
170
171 FND_FILE.PUT_LINE(FND_FILE.LOG,g_ten_space||lookup_desc('IGS_FI_LOCKBOX','FEE_TYPE')||':'||p_fee_type);
172
173 FND_FILE.PUT_LINE(FND_FILE.LOG,g_ten_space||lookup_desc('IGS_FI_LOCKBOX','FEE_CAL_TYPE')||':'||p_fee_cal_type);
174
175 OPEN cur_date;
176 FETCH cur_date INTO l_cur_date;
177 CLOSE cur_date;
178 FND_FILE.PUT_LINE(FND_FILE.LOG,g_ten_space||lookup_desc('IGS_FI_LOCKBOX','START_DT')||':'||TO_CHAR(l_cur_date.start_dt,'DD-MON-YYYY'));
179
180 FND_FILE.PUT_LINE(FND_FILE.LOG,g_ten_space||lookup_desc('IGS_FI_LOCKBOX','END_DT')||':'||TO_CHAR(l_cur_date.end_dt,'DD-MON-YYYY'));
181
182 FND_FILE.PUT_LINE(FND_FILE.LOG,g_ten_space||lookup_desc('IGS_FI_LOCKBOX','REVERSED')||':'||lookup_desc('VS_AS_YN',p_reversal_ind));
183
184 IF p_status THEN
185 FND_FILE.PUT_LINE(FND_FILE.LOG,g_ten_space||lookup_desc('IGS_FI_LOCKBOX','STATUS')||':'||
186 lookup_desc('REFUND_TRANSFER_STATUS',p_rec_status));
187 ELSE
188 FND_FILE.PUT_LINE(FND_FILE.LOG,g_ten_space||lookup_desc('IGS_FI_LOCKBOX','STATUS')||':'||lookup_desc('IGS_FI_LOCKBOX','ERROR'));
189 END IF;
190
191 IF p_status=FALSE THEN
192 FND_FILE.PUT_LINE(FND_FILE.LOG,g_ten_space||lookup_desc('IGS_FI_LOCKBOX','REASON')||':'||p_err_msg);
193 END IF;
194 END log_person;
195
196 PROCEDURE log_messages ( p_msg_name VARCHAR2 ,
197 p_msg_val VARCHAR2
198 ) AS
199 /***********************************************************************************************
200
201 Created By : Sarakshi
202 Date Created By: 1-Mar-2001
203 Purpose : To log the parameters
204
205 Known limitations,enhancements,remarks:
206 Change History
207 Who When What
208
209 ********************************************************************************************** */
210 BEGIN
211 FND_MESSAGE.SET_NAME('IGS','IGS_FI_CAL_BALANCES_LOG');
212 FND_MESSAGE.SET_TOKEN('PARAMETER_NAME',p_msg_name);
213 FND_MESSAGE.SET_TOKEN('PARAMETER_VAL' ,p_msg_val) ;
214 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
215 END log_messages ;
216
217 PROCEDURE update_refunds(p_status IN igs_fi_refunds.transfer_status%TYPE,
218 p_cur_ref_upd IN cur_refund%ROWTYPE ,
219 p_dml_status OUT NOCOPY BOOLEAN,
220 p_err_msg OUT NOCOPY fnd_new_messages.message_text%TYPE) AS
221 /***********************************************************************************************
222
223 Created By : Sarakshi
224 Date Created By: 1-Mar-2001
225 Purpose : To update records in refunds table
226
227 Known limitations,enhancements,remarks:
228 Change History
229 Who When What
230 skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns from the table IGS_FI_REFUNDS
231 sapanigr 03-May-2006 Enh#3924836 Precision Issue. Amount values being inserted into igs_fi_refunds
232 is now rounded off to currency precision
233 vvutukur 19-Nov-2002 Modified the call to igs_fi_refunds_pkg.update_row to add new parameters gl_date,
234 reversal_gl_date.
235 ********************************************************************************************** */
236
237 BEGIN
238 p_dml_status:=TRUE;
239 p_err_msg:=NULL;
240
241 -- Call to igs_fi_gen_gl.get_formatted_amount formats refund_amount by rounding off to currency precision
242 igs_fi_refunds_pkg.update_row(X_ROWID => p_cur_ref_upd.rowid,
243 X_REFUND_ID => p_cur_ref_upd.refund_id,
244 X_VOUCHER_DATE => p_cur_ref_upd.voucher_date,
245 X_PERSON_ID => p_cur_ref_upd.person_id,
246 X_PAY_PERSON_ID => p_cur_ref_upd.pay_person_id,
247 X_DR_GL_CCID => p_cur_ref_upd.dr_gl_ccid,
248 X_CR_GL_CCID => p_cur_ref_upd.cr_gl_ccid,
249 X_DR_ACCOUNT_CD => p_cur_ref_upd.dr_account_cd,
250 X_CR_ACCOUNT_CD => p_cur_ref_upd.cr_account_cd,
251 X_REFUND_AMOUNT => igs_fi_gen_gl.get_formatted_amount(p_cur_ref_upd.refund_amount),
252 X_FEE_TYPE => p_cur_ref_upd.fee_type,
253 X_FEE_CAL_TYPE => p_cur_ref_upd.fee_cal_type,
254 X_FEE_CI_SEQUENCE_NUMBER => p_cur_ref_upd.fee_ci_sequence_number,
255 X_SOURCE_REFUND_ID => p_cur_ref_upd.source_refund_id,
256 X_INVOICE_ID => p_cur_ref_upd.invoice_id,
257 X_TRANSFER_STATUS => p_status,
258 X_REVERSAL_IND => p_cur_ref_upd.reversal_ind,
259 X_REASON => p_cur_ref_upd.reason,
260 X_ATTRIBUTE_CATEGORY => p_cur_ref_upd.attribute_category,
261 X_ATTRIBUTE1 => p_cur_ref_upd.attribute1,
262 X_ATTRIBUTE2 => p_cur_ref_upd.attribute2,
263 X_ATTRIBUTE3 => p_cur_ref_upd.attribute3,
264 X_ATTRIBUTE4 => p_cur_ref_upd.attribute4,
265 X_ATTRIBUTE5 => p_cur_ref_upd.attribute5,
266 X_ATTRIBUTE6 => p_cur_ref_upd.attribute6,
267 X_ATTRIBUTE7 => p_cur_ref_upd.attribute7,
268 X_ATTRIBUTE8 => p_cur_ref_upd.attribute8,
269 X_ATTRIBUTE9 => p_cur_ref_upd.attribute9,
270 X_ATTRIBUTE10 => p_cur_ref_upd.attribute10,
271 X_ATTRIBUTE11 => p_cur_ref_upd.attribute11,
272 X_ATTRIBUTE12 => p_cur_ref_upd.attribute12,
273 X_ATTRIBUTE13 => p_cur_ref_upd.attribute13,
274 X_ATTRIBUTE14 => p_cur_ref_upd.attribute14,
275 X_ATTRIBUTE15 => p_cur_ref_upd.attribute15,
276 X_ATTRIBUTE16 => p_cur_ref_upd.attribute16,
277 X_ATTRIBUTE17 => p_cur_ref_upd.attribute17,
278 X_ATTRIBUTE18 => p_cur_ref_upd.attribute18,
279 X_ATTRIBUTE19 => p_cur_ref_upd.attribute19,
280 X_ATTRIBUTE20 => p_cur_ref_upd.attribute20,
281 X_MODE => 'R',
282 X_GL_DATE => p_cur_ref_upd.gl_date,
283 X_REVERSAL_GL_DATE => p_cur_ref_upd.reversal_gl_date
284 );
285 EXCEPTION
286 WHEN OTHERS THEN
287 p_dml_status:=FALSE;
288 p_err_msg:=FND_MESSAGE.GET;
289 --If any unexpected event occured , other than what is expected from TBH, say ORA errors , then
290 -- return that
291 IF p_err_msg IS NULL THEN
292 p_err_msg:=sqlerrm;
293 END IF;
294 END update_refunds;
295
296 PROCEDURE insert_interface(p_cur_ref_ins IN cur_ref%ROWTYPE ,
297 p_dml_status OUT NOCOPY BOOLEAN,
298 p_err_msg OUT NOCOPY fnd_new_messages.message_text%TYPE) AS
299 /***********************************************************************************************
300
301 Created By : Sarakshi
302 Date Created By: 1-Mar-2001
303 Purpose : To insert records in the refunds interface table
304
305 Known limitations,enhancements,remarks:
306 Change History
307 Who When What
308 sapanigr 03-May-2006 Enh#3924836 Precision Issue. Amount values being inserted into igs_fi_refund_int
309 is now rounded off to currency precision
310 vvutukur 19-Nov-2002 Enh#2584986.Modified the call to igs_fi_refund_int_pkg.insert_row to include the new parameter
311 x_gl_date.
312 ********************************************************************************************** */
313
314 l_rowid VARCHAR2(25) ;
315 BEGIN
316 p_dml_status:=TRUE;
317 p_err_msg:=NULL;
318
319 -- Call to igs_fi_gen_gl.get_formatted_amount formats refund_amount by rounding off to currency precision
320 igs_fi_refund_int_pkg.insert_row( X_ROWID => l_rowid,
321 X_REFUND_ID => p_cur_ref_ins.refund_id,
322 X_VOUCHER_DATE => p_cur_ref_ins.voucher_date,
323 X_PERSON_ID => p_cur_ref_ins.person_id,
324 X_PAY_PERSON_ID => p_cur_ref_ins.pay_person_id,
325 X_DR_GL_CCID => p_cur_ref_ins.dr_gl_ccid,
326 X_CR_GL_CCID => p_cur_ref_ins.cr_gl_ccid,
327 X_DR_ACCOUNT_CD => p_cur_ref_ins.dr_account_cd,
328 X_CR_ACCOUNT_CD => p_cur_ref_ins.cr_account_cd,
329 X_REFUND_AMOUNT => igs_fi_gen_gl.get_formatted_amount(p_cur_ref_ins.refund_amount),
330 X_FEE_TYPE => p_cur_ref_ins.fee_type,
331 X_FEE_CAL_TYPE => p_cur_ref_ins.fee_cal_type,
332 X_FEE_CI_SEQUENCE_NUMBER => p_cur_ref_ins.fee_ci_sequence_number,
333 X_SOURCE_REFUND_ID => p_cur_ref_ins.source_refund_id,
334 X_INVOICE_ID => p_cur_ref_ins.invoice_id,
335 X_REASON => p_cur_ref_ins.reason,
336 X_ATTRIBUTE_CATEGORY => p_cur_ref_ins.attribute_category,
337 X_ATTRIBUTE1 => p_cur_ref_ins.attribute1,
338 X_ATTRIBUTE2 => p_cur_ref_ins.attribute2,
339 X_ATTRIBUTE3 => p_cur_ref_ins.attribute3,
340 X_ATTRIBUTE4 => p_cur_ref_ins.attribute4,
341 X_ATTRIBUTE5 => p_cur_ref_ins.attribute5,
342 X_ATTRIBUTE6 => p_cur_ref_ins.attribute6,
343 X_ATTRIBUTE7 => p_cur_ref_ins.attribute7,
344 X_ATTRIBUTE8 => p_cur_ref_ins.attribute8,
345 X_ATTRIBUTE9 => p_cur_ref_ins.attribute9,
346 X_ATTRIBUTE10 => p_cur_ref_ins.attribute10,
347 X_ATTRIBUTE11 => p_cur_ref_ins.attribute11,
348 X_ATTRIBUTE12 => p_cur_ref_ins.attribute12,
349 X_ATTRIBUTE13 => p_cur_ref_ins.attribute13,
350 X_ATTRIBUTE14 => p_cur_ref_ins.attribute14,
351 X_ATTRIBUTE15 => p_cur_ref_ins.attribute15,
352 X_ATTRIBUTE16 => p_cur_ref_ins.attribute16,
353 X_ATTRIBUTE17 => p_cur_ref_ins.attribute17,
354 X_ATTRIBUTE18 => p_cur_ref_ins.attribute18,
355 X_ATTRIBUTE19 => p_cur_ref_ins.attribute19,
356 X_ATTRIBUTE20 => p_cur_ref_ins.attribute20,
357 X_MODE => 'R',
358 X_GL_DATE => p_cur_ref_ins.gl_date
359 );
360
361 EXCEPTION
362 WHEN OTHERS THEN
363 p_dml_status:=FALSE;
364 p_err_msg:=FND_MESSAGE.GET;
365 --If any unexpected event occured , other than what is expected from TBH, say ORA errors , then
366 -- return that
367 IF p_err_msg IS NULL THEN
368 p_err_msg:=sqlerrm;
369 END IF;
370 END insert_interface;
371
372 PROCEDURE transfer_int_rec(p_person_id igs_pe_person.person_id%TYPE,
373 p_test_run igs_lookups_view.lookup_code%TYPE,
374 p_start_date igs_fi_refunds.voucher_date%TYPE,
375 p_end_date igs_fi_refunds.voucher_date%TYPE) AS
376 /***********************************************************************************************
377
378 Created By : Sarakshi
379 Date Created By: 1-Mar-2001
380 Purpose : To Transfer refunds records to interface table and to update the status
381 of the refund record accordingly.
382 Known limitations,enhancements,remarks:
383 Change History
384 Who When What
385 pathipat 12-May-2003 Enh 2831569 - Commercial Receivables build
386 Added flag (g_b_data_found) to keep track of no-data-found conditions
387 pathipat 18-Feb-2003 Enh 2747329 - Payables Intg build
388 The status for Reversed Refund record and its Source is set to Offset
389 instead of Transferred
390 agairola 16-May-2002 Modified the cursor cur_check and cur_ref removing FOR UPDATE clause
391 Also made individual calls to Log_Person in case the Refunds table
392 is updated for bug 2374103
393 ********************************************************************************************** */
394
395 CURSOR cur_check(cp_refund_id igs_fi_refunds.refund_id%TYPE) IS
396 SELECT r.*,r.rowid
397 FROM igs_fi_refunds r
398 WHERE refund_id = cp_refund_id
399 AND NVL(reversal_ind,'N') = 'Y';
400 l_cur_check cur_check%ROWTYPE;
401
402 l_status BOOLEAN;
403 l_err_msg fnd_new_messages.message_text%TYPE;
404
405 BEGIN
406
407 --The below loop is for all those records which has been reversed.
408 FOR l_cur_ref IN cur_ref(p_person_id,p_start_date,p_end_date) LOOP
409
410 g_b_data_found := TRUE;
411
412 OPEN cur_check(l_cur_ref.source_refund_id);
413 FETCH cur_check INTO l_cur_check;
414 CLOSE cur_check;
415 l_status:=TRUE;
416 l_err_msg:=NULL;
417
418 IF l_cur_check.transfer_status = g_todo THEN
419
420 -- Update the refunds record
421 -- If the record is a Reversed Refund record, then set status to Offset for that record and for the
422 -- Source Refund record. These 2 records will not be transferred to the Interface table, hence the status of Offset.
423 update_refunds(g_v_offset, l_cur_ref,l_status,l_err_msg);
424
425 log_person(p_person_id => l_cur_ref.person_id,
426 p_invoice_id => l_cur_ref.invoice_id,
427 p_refund_id => l_cur_ref.refund_id,
428 p_pay_person_id => l_cur_ref.pay_person_id,
429 p_fee_type => l_cur_ref.fee_type,
430 p_fee_cal_type => l_cur_ref.fee_cal_type,
431 p_sequence_number => l_cur_ref.fee_ci_sequence_number,
432 p_reversal_ind => l_cur_ref.reversal_ind,
433 p_rec_status => g_v_offset,
434 p_err_msg => l_err_msg,
435 p_status => l_status );
436
437 IF l_status THEN
438 -- Update the parent refund record (Source refund record)
439 -- Set status to Offset, instead of Transferred
440 update_refunds(g_v_offset, l_cur_check,l_status,l_err_msg);
441
442 log_person(p_person_id => l_cur_check.person_id,
443 p_invoice_id => l_cur_check.invoice_id,
444 p_refund_id => l_cur_check.refund_id,
445 p_pay_person_id => l_cur_check.pay_person_id,
446 p_fee_type => l_cur_check.fee_type,
447 p_fee_cal_type => l_cur_check.fee_cal_type,
448 p_sequence_number => l_cur_check.fee_ci_sequence_number,
449 p_reversal_ind => l_cur_check.reversal_ind,
450 p_rec_status => g_v_offset,
451 p_err_msg => l_err_msg,
452 p_status => l_status );
453 END IF;
454 ELSIF l_cur_check.transfer_status = g_transferred THEN
455 --Insert in the interface table
456 insert_interface(l_cur_ref,l_status,l_err_msg);
457 IF l_status THEN
458 --Update the refunds record
459 update_refunds(g_transferred, l_cur_ref,l_status,l_err_msg);
460 END IF;
461 log_person(p_person_id => l_cur_ref.person_id,
462 p_invoice_id => l_cur_ref.invoice_id,
463 p_refund_id => l_cur_ref.refund_id,
464 p_pay_person_id => l_cur_ref.pay_person_id,
465 p_fee_type => l_cur_ref.fee_type,
466 p_fee_cal_type => l_cur_ref.fee_cal_type,
467 p_sequence_number => l_cur_ref.fee_ci_sequence_number,
468 p_reversal_ind => l_cur_ref.reversal_ind,
469 p_rec_status => g_transferred,
470 p_err_msg => l_err_msg,
471 p_status => l_status );
472 END IF;
473
474
475 --Commit the transaction if test run is set to false and above operations
476 --are succcessful else rollback the transaction
477 IF ((p_test_run='N') AND (l_status=TRUE)) THEN
478 COMMIT;
479 ELSE
480 ROLLBACK;
481 END IF;
482
483 END LOOP;
484
485 --The below loop is for all those records which has not been reversed.
486 FOR l_cur_refund IN cur_refund(p_person_id,p_start_date,p_end_date) LOOP
487
488 g_b_data_found := TRUE;
489
490 l_status:=TRUE;
491 l_err_msg:=NULL;
492 --Insert in the interface table
493 insert_interface(l_cur_refund,l_status,l_err_msg);
494 IF l_status THEN
495 --Update the refunds record
496 update_refunds(g_transferred, l_cur_refund,l_status,l_err_msg);
497 END IF;
498
499 --Logging the record information
500 log_person(p_person_id => l_cur_refund.person_id,
501 p_invoice_id => l_cur_refund.invoice_id,
502 p_refund_id => l_cur_refund.refund_id,
503 p_pay_person_id => l_cur_refund.pay_person_id,
504 p_fee_type => l_cur_refund.fee_type,
505 p_fee_cal_type => l_cur_refund.fee_cal_type,
506 p_sequence_number => l_cur_refund.fee_ci_sequence_number,
507 p_reversal_ind => l_cur_refund.reversal_ind,
508 p_rec_status => g_transferred,
509 p_err_msg => l_err_msg,
510 p_status => l_status );
511
512
513 --Commit the transaction if test run is set to false and above operations
514 --are succcessful else rollback the transaction
515 IF ((p_test_run='N') AND (l_status=TRUE)) THEN
516 COMMIT;
517 ELSE
518 ROLLBACK;
519 END IF;
520 END LOOP;
521
522
523 END transfer_int_rec;
524
525 PROCEDURE transfer_to_int( errbuf OUT NOCOPY VARCHAR2,
526 retcode OUT NOCOPY NUMBER,
527 p_person_id IN igs_pe_person.person_id%TYPE,
528 p_person_id_grp IN igs_pe_persid_group.group_id%TYPE,
529 p_start_date IN VARCHAR2,
530 p_end_date IN VARCHAR2,
531 p_test_run IN igs_lookups_view.lookup_code%TYPE) AS
532 /***********************************************************************************************
533 Created By : Sarakshi
534 Date Created By: 1-Mar-2001
535 Purpose : To Transfer refunds records to interface table and to update the status
536 of the refund record accordingly.
537 Known limitations,enhancements,remarks:
538 Change History
539 Who When What
540 sapanigr 14-Feb-2006 Bug #5018036. Cursor cur_pers modified to query hz_parties instead of igs_fi_parties_v
541 ridas 13-Feb-2006 Bug #5021084. Added new parameter lv_group_type in call to igf_ap_ss_pkg.get_pid
542 pathipat 23-Apr-2003 Enh 2831569 - Commercial Receivables build
543 Added validation for manage_account - call to chk_manage_account()
544 Added code to log messages for test run = Y and for no-data-found cases
545 Increased width of l_v_sql_query from 1000 to 32767
546 pathipat 18-Feb-2003 Enh 2747329 - Payables Intg build
547 1. Modified cursor cur_pers_grp - used igs_pe_all_persid_group_v instead of igs_pe_persid_group
548 Changed declaration of l_group_code accordingly.
549 2. Added validation to check for Refunds Destination before allowing the process to be run
550 3. Modified approach to identify persons in a person id group
551 vvutukur 03-Jan-2003 Bug#2727216.Modified the logic for logging the parameters, by removing the
552 redundant code.
553 vchappid 13-Jun-2002 Bug#2411529, Incorrectly used message name has been modified
554 ********************************************************************************************** */
555 CURSOR cur_pers(cp_person_id hz_parties.party_id%TYPE) IS
556 SELECT party_number
557 FROM hz_parties
558 WHERE party_id=cp_person_id;
559 l_cur_pers cur_pers%ROWTYPE;
560
561 -- Cursor for validating Person Id Group
562 CURSOR cur_pers_grp IS
563 SELECT group_cd
564 FROM igs_pe_all_persid_group_v
565 WHERE group_id = p_person_id_grp
566 AND closed_ind = 'N';
567
568 l_group_code igs_pe_all_persid_group_v.group_cd%TYPE;
569
570 -- Ref cursor used to execute the dynamic sql query for
571 -- identifying persons in a Person Id Group
572 TYPE person_group_ref IS REF CURSOR;
573 cur_person_grp_ref person_group_ref;
574
575 -- The variable l_cur_person_grp_rec is used to hold the values
576 -- fetched by the ref cursor
577 TYPE person_group_rec IS RECORD ( p_n_person_id igs_pe_prsid_grp_mem.person_id%TYPE);
578 l_cur_person_grp_rec person_group_rec;
579
580 -- Variable to hold the dynamic sql query to identify persons
581 -- in a Person Id Group
582 l_v_sql_query VARCHAR2(32767) := NULL;
583
584 -- Out variable to be passed to function returning sql query
585 l_v_status VARCHAR2(30) := NULL;
586
587 -- Variable to hold value of rfnd_destination in igs_fi_control
588 l_v_rfnd_destination igs_fi_control_all.rfnd_destination%TYPE;
589
590 l_start_date DATE :=NULL;
591 l_end_date DATE :=NULL;
592
593 l_pers_valid BOOLEAN := TRUE;
594 l_pers_id_grp BOOLEAN := TRUE;
595 l_valid_param BOOLEAN := TRUE;
596
597 l_v_manage_acc igs_fi_control_all.manage_accounts%TYPE := NULL;
598 l_v_message_name fnd_new_messages.message_name%TYPE := NULL;
599 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
600
601 BEGIN
602
603 -- initialises the out parameter to 0
604 retcode := 0;
605 -- sets the orgid
606 IGS_GE_GEN_003.set_org_id(NULL);
607
608 --Get the party number for the input party id, if passed and check if it is valid.
609 IF p_person_id IS NOT NULL THEN
610 OPEN cur_pers(p_person_id);
611 FETCH cur_pers INTO l_cur_pers;
612 IF cur_pers%NOTFOUND THEN
613 l_pers_valid := FALSE;
614 END IF;
615 CLOSE cur_pers;
616 END IF;
617
618 -- Get the group code for input person id group, if passed and check if it is valid.
619 IF p_person_id_grp IS NOT NULL THEN
620 OPEN cur_pers_grp;
621 FETCH cur_pers_grp INTO l_group_code;
622 IF cur_pers_grp%NOTFOUND THEN
623 l_pers_id_grp := FALSE;
624 END IF;
625 CLOSE cur_pers_grp;
626 END IF;
627
628 --converting the Start Date and End Date to Date format from Canonical Format.
629 IF p_start_date IS NOT NULL THEN
630 l_start_date:=igs_ge_date.igsdate(p_start_date);
631 END IF;
632 IF p_end_date IS NOT NULL THEN
633 l_end_date:=igs_ge_date.igsdate(p_end_date);
634 END IF;
635
636 --Logging all the parameters passed to the concurrent program.
637 log_messages(lookup_desc('IGS_FI_LOCKBOX','PERSON'),NVL(l_cur_pers.party_number,p_person_id));
638 IF ( p_person_id_grp IS NOT NULL ) THEN
639 log_messages(lookup_desc('IGS_FI_LOCKBOX','PERSON_GROUP'),l_group_code||'('||TO_CHAR(p_person_id_grp)||')');
640 ELSE
641 log_messages(lookup_desc('IGS_FI_LOCKBOX','PERSON_GROUP'),TO_CHAR(p_person_id_grp));
642 END IF;
643 log_messages(lookup_desc('IGS_FI_LOCKBOX','START_DT'),l_start_date);
644 log_messages(lookup_desc('IGS_FI_LOCKBOX','END_DT'),l_end_date);
645 log_messages(lookup_desc('IGS_FI_LOCKBOX','TEST_MODE'),lookup_desc('VS_AS_YN',p_test_run));
646
647
648 -- Obtain the value of manage_accounts in the System Options form
649 -- If it is null or 'OTHER', then this process is not available, so error out.
650 igs_fi_com_rec_interface.chk_manage_account( p_v_manage_acc => l_v_manage_acc,
651 p_v_message_name => l_v_message_name
652 );
653 IF (l_v_manage_acc = 'OTHER') OR (l_v_manage_acc IS NULL) THEN
654 fnd_message.set_name('IGS',l_v_message_name);
655 igs_ge_msg_stack.add;
656 fnd_file.put_line(fnd_file.log,fnd_message.get());
657 fnd_file.put_line(fnd_file.log,' ');
658 retcode := 2;
659 RETURN;
660 END IF;
661
662 -- If Refund destination is 'Payables' or Null, then this process is not available.
663 -- The Refund destination set in igs_fi_control has to be 'Other' for this process to be run.
664 l_v_rfnd_destination := igs_fi_gen_apint.get_rfnd_destination;
665
666 IF (l_v_rfnd_destination = 'PAYABLES') OR (l_v_rfnd_destination IS NULL) THEN
667 fnd_message.set_name('IGS','IGS_FI_RFND_DST_OTH');
668 igs_ge_msg_stack.add;
669 fnd_file.put_line(fnd_file.log,fnd_message.get);
670 retcode := 2;
671 RETURN;
672 END IF;
673
674 --Validates if both person_id and person id group is passed
675 IF ((p_person_id IS NOT NULL) AND (p_person_id_grp IS NOT NULL)) THEN
676 FND_MESSAGE.SET_NAME('IGS','IGS_FI_PRS_PRSIDGRP_NULL');
677 IGS_GE_MSG_STACK.ADD;
678 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
679 l_valid_param := FALSE;
680 END IF;
681
682 IF NOT l_pers_valid THEN
683 FND_MESSAGE.SET_NAME('IGS','IGS_FI_INVALID_PERSON');
684 FND_MESSAGE.SET_TOKEN('PERSON_ID',TO_CHAR(p_person_id));
685 IGS_GE_MSG_STACK.ADD;
686 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
687 l_valid_param := FALSE;
688 END IF;
689
690 IF NOT l_pers_id_grp THEN
691 FND_MESSAGE.SET_NAME('IGS','IGS_FI_INVPERS_ID_GRP');
692 IGS_GE_MSG_STACK.ADD;
693 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
694 l_valid_param := FALSE;
695 END IF;
696
697 --Validates if start date is less than end date
698 IF ((p_start_date IS NOT NULL) AND (p_end_date IS NOT NULL)) THEN
699 IF l_start_date > l_end_date THEN
700 FND_MESSAGE.SET_NAME('IGS','IGS_FI_ST_DT_LE_END_DT');
701 IGS_GE_MSG_STACK.ADD;
702 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
703 l_valid_param := FALSE;
704 END IF;
705 END IF;
706
707 IF NOT l_valid_param THEN
708 retcode := 2;
709 RETURN;
710 END IF;
711
712 IF p_person_id_grp IS NOT NULL THEN
713
714 -- The persons belonging to the person_id group are obtained dynamically
715 -- The following function returns the sql query which would identify the persons
716 --Bug #5021084
717 l_v_sql_query := igf_ap_ss_pkg.get_pid( p_person_id_grp,
718 l_v_status,
719 lv_group_type
720 );
721
722 -- Using ref cursor, the sql above is executed and the persons identified
723 --Bug #5021084. Passing Group ID if the group type is STATIC.
724 IF lv_group_type = 'STATIC' THEN
725 OPEN cur_person_grp_ref FOR l_v_sql_query USING p_person_id_grp;
726 ELSIF lv_group_type = 'DYNAMIC' THEN
727 OPEN cur_person_grp_ref FOR l_v_sql_query;
728 END IF;
729
730 LOOP
731 FETCH cur_person_grp_ref INTO l_cur_person_grp_rec;
732 EXIT WHEN cur_person_grp_ref%NOTFOUND;
733 -- Invoking the below procedure for transfering records in refunds interface table
734 -- when either person_id or person_id_group is provided
735 transfer_int_rec( p_person_id => l_cur_person_grp_rec.p_n_person_id,
736 p_test_run => p_test_run,
737 p_start_date => l_start_date,
738 p_end_date => l_end_date);
739 END LOOP;
740 CLOSE cur_person_grp_ref;
741
742 -- If a particular person has been specified, or Person ID/Person ID group are both null
743 ELSE
744 transfer_int_rec(p_person_id => p_person_id,
745 p_test_run => p_test_run,
746 p_start_date => l_start_date,
747 p_end_date => l_end_date);
748
749 END IF;
750
751 -- Log message if no data found for processing
752 IF (NOT g_b_data_found) THEN
753 fnd_message.set_name('IGS','IGS_GE_NO_DATA_FOUND');
754 igs_ge_msg_stack.add;
755 fnd_file.put_line(fnd_file.log,fnd_message.get);
756 fnd_file.new_line(fnd_file.log);
757 END IF;
758
759 -- If run in test mode = Y and records have been processed,
760 -- log message saying transactions have been rolled back
761 IF (p_test_run = 'Y') AND (g_b_data_found = TRUE) THEN
762 fnd_message.set_name('IGS','IGS_FI_PRC_TEST_RUN');
763 igs_ge_msg_stack.add;
764 fnd_file.new_line(fnd_file.log);
765 fnd_file.put_line(fnd_file.log,fnd_message.get);
766 END IF;
767
768 EXCEPTION
769 WHEN e_resource_busy THEN
770 retcode := 2;
771 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING('IGS','IGS_FI_RFND_REC_LOCK'));
772 WHEN OTHERS THEN
773 retcode := 2;
774 errbuf := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION')||':'||sqlerrm;
775 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL ;
776 END transfer_to_int;
777
778
779
780 PROCEDURE update_pay_info ( errbuf OUT NOCOPY VARCHAR2,
781 retcode OUT NOCOPY NUMBER,
782 p_person_id IN igs_fi_parties_v.person_id%TYPE,
783 p_person_id_group IN igs_pe_persid_group.group_id%TYPE,
784 p_start_date IN VARCHAR2,
785 p_end_date IN VARCHAR2,
786 p_test_run IN igs_lookups_view.lookup_code%TYPE)
787 AS
788 /***********************************************************************************************
789 Created By : vchappid
790 Date Created By: 04-MAR-2001
791 Purpose : Concurrent Manager Procedure for updating the payment information for the
792 Refunds transactions
793 Known limitations,enhancements,remarks:
794 Change History
795 Who When What
796 vchappid 03-Jul-2002 Bug# 2442030, Concurrent Process is obsoleted, and hence nullified the code
797 vchappid 13-Jun-2002 Bug#2411529, Incorrectly used message name has been modified
798 agairola 16-May-2002 Moidified the cursor cur_refunds to remove FOR UPDATE clause
799
800 ********************************************************************************************** */
801
802 BEGIN
803 --Bug# 2442030, Concurrent Process is obsoleted, and hence nullified the code
804 FND_MESSAGE.Set_Name('IGS',
805 'IGS_GE_OBSOLETE_JOB');
806 FND_FILE.Put_Line(FND_FILE.Log,
807 FND_MESSAGE.Get);
808 retcode := 0;
809 EXCEPTION
810 WHEN OTHERS THEN
811 retcode := 2;
812 FND_MESSAGE.Set_Name('IGS',
813 'IGS_GE_UNHANDLED_EXCEPTION');
814 IGS_GE_MSG_STACK.Add;
815 APP_EXCEPTION.Raise_Exception;
816 END update_pay_info;
817
818 END igs_fi_refunds_process;
[Home] [Help]