[Home] [Help]
PACKAGE BODY: APPS.AR_ECAPP_PKG
Source
1 PACKAGE BODY AR_ECAPP_PKG AS
2 /*$Header: ARECAPPB.pls 120.6 2011/09/23 10:20:19 vpusulur ship $*/
3
4 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
5
6 PROCEDURE UPDATE_STATUS(
7 totalRows IN NUMBER,
8 txn_id_Tab IN JTF_VARCHAR2_TABLE_100,
9 req_type_Tab IN JTF_VARCHAR2_TABLE_100,
10 Status_Tab IN JTF_NUMBER_TABLE,
11 updatedt_Tab IN JTF_DATE_TABLE,
12 refcode_Tab IN JTF_VARCHAR2_TABLE_100,
13 o_status OUT NOCOPY VARCHAR2,
14 o_errcode OUT NOCOPY VARCHAR2,
15 o_errmsg OUT NOCOPY VARCHAR2,
16 o_statusindiv_Tab IN OUT NOCOPY JTF_VARCHAR2_TABLE_100
17 ) AS
18
19 cnt number := 0;
20 extendRows INTEGER:=1;
21 BEGIN
22
23 IF PG_DEBUG in ('Y', 'C') THEN
24 fnd_file.put_line(FND_FILE.LOG,'AR_ECAPP_PKG.UPDATE_STATUS()+');
25 fnd_file.put_line(FND_FILE.LOG, totalRows);
26 arp_standard.debug ('Inserting record into ar_settlement_errors_gt table');
27 END IF;
28
29 FORALL i in 1 .. totalRows
30 insert into ar_settlement_errors_gt(
31 txn_id,
32 req_type,
33 Status,
34 updatedt,
35 refcode)
36 values(
37 txn_id_tab(i),
38 req_type_tab(i),
39 Status_tab(i),
40 updatedt_tab(i),
41 refcode_tab(i)
42 );
43
44 IF PG_DEBUG in ('Y', 'C') THEN
45 arp_standard.debug ('Number of rows inserted: ' ||sql%rowcount);
46 arp_standard.debug ('Printing the IN parametr list from IBY');
47 IF txn_id_tab.count > 0 THEN
48 FOR i in 1 .. totalRows LOOP
49 cnt := cnt + 1;
50 arp_standard.debug ('txn_id_Tab(' || cnt || ')' || txn_id_Tab(i)) ;
51 arp_standard.debug ('req_type_Tab(' || cnt || ')' || req_type_Tab(i)) ;
52 arp_standard.debug ('Status_Tab(' || cnt || ')' || Status_Tab(i)) ;
53 arp_standard.debug ('updatedt_Tab(' || cnt || ')' || updatedt_Tab(i)) ;
54 arp_standard.debug ('refcode_Tab(' || cnt || ')' || refcode_Tab(i)) ;
55 END LOOP;
56 END IF;
57 arp_standard.debug ('Calling correct_settlement_error routine');
58 END IF;
59 correct_settlement_error ;
60
61 /***************************************************************
62 * All these records are processed. Pass TRUE in the OUT table *
63 * o_statusindiv_Tab to set the records so that they would not *
64 * be passed again. *
65 ***************************************************************/
66 o_statusindiv_Tab := JTF_VARCHAR2_TABLE_100();
67 FOR i in 1 .. totalRows LOOP
68 o_statusindiv_Tab.extend(extendRows);
69 o_statusindiv_Tab(i) := 'TRUE';
70 END LOOP;
71
72 IF PG_DEBUG in ('Y', 'C') THEN
73 fnd_file.put_line(FND_FILE.LOG,'AR_ECAPP_PKG.UPDATE_STATUS()-');
74 END IF;
75
76 EXCEPTION
77 WHEN OTHERS THEN
78 fnd_file.put_line(FND_FILE.LOG,'AR_ECAPP_PKG.UPDATE_STATUS() - Exception');
79 fnd_file.put_line(FND_FILE.LOG, sqlerrm);
80 END;
81
82
83
84 PROCEDURE correct_settlement_error AS
85
86 l_cash_receipt_id AR_ECAPP_PKG.t_cash_receipt_id;
87 l_receipt_number AR_ECAPP_PKG.t_receipt_number;
88 l_org_id AR_ECAPP_PKG.t_org_id;
89 l_bepcode AR_ECAPP_PKG.t_bepcode;
90 l_bepmessage AR_ECAPP_PKG.t_bepmessage;
91 l_instrtype AR_ECAPP_PKG.t_instrtype;
92
93 l_request_id AR_ECAPP_PKG.t_request_id;
94 l_cc_org_id AR_ECAPP_PKG.t_org_id;
95
96 l_receipt_info AR_RECEIPT_API_PUB.CR_ID_TABLE;
97 l_empty_receipt_info AR_RECEIPT_API_PUB.CR_ID_TABLE;
98 l_called_from varchar2(30);
99 l_return_status varchar2(10);
100 l_msg_count number;
101 l_msg_data varchar2(2000);
102
103 l_error_buf varchar2(240);
104 l_ret_code varchar2(240);
105
106
107 j number;
108 k number;
109 l_call_api varchar2(1);
110 l_last_record varchar2(1);
111 l_org_return_status VARCHAR2(1);
112
113
114 CURSOR C1 IS
115 SELECT cr.cash_receipt_id, cr.receipt_number, cr.org_id,
116 summ.bepcode, summ.bepmessage, summ.instrtype
117 FROM ar_cash_receipts_all cr, ar_cash_receipt_history_all crh ,
118 ar_settlement_errors_gt gt, iby_trxn_summaries_all summ,
119 iby_fndcpt_tx_operations op
120 WHERE gt.req_type in ('ORAPMTCAPTURE', 'ORAPMTRETURN', 'ORAPMTCREDIT', 'ORAPMTVOID', 'ORAPMTBATCHREQ')
121 AND summ.transactionid = gt.txn_id
122 AND summ.reqtype = gt.req_type
123 AND op.transactionid = summ.transactionid
124 and cr.payment_trxn_extension_id = op.trxn_extension_id
125 and cr.cash_receipt_id = crh.cash_receipt_id
126 and cr.org_id = crh.org_id
127 and crh.status = 'REMITTED'
128 and crh.current_record_flag = 'Y'
129 AND NOT EXISTS
130 (SELECT 1
131 FROM ar_settlement_errors_gt gt_in
132 WHERE gt_in.txn_id = gt.txn_id
133 AND gt_in.status = 0)
134 AND summ.rowid IN
135 (SELECT max(rowid)
136 FROM iby_trxn_summaries_all summ_in
137 WHERE summ.transactionid = summ_in.transactionid
138 AND summ_in.status not in (1, 0))
139 ORDER by cr.org_id, cr.cash_receipt_id;
140
141
145 IF PG_DEBUG in ('Y', 'C') THEN
142
143 BEGIN
144
146 fnd_file.put_line(FND_FILE.LOG,'AR_ECAPP_PKG.correct_settlement_error()+');
147 END IF;
148
149 l_called_from := 'SUBMIT_OFFLINE';
150
151 IF PG_DEBUG in ('Y', 'C') THEN
152 arp_standard.debug('Open Cursor C1');
153 END IF;
154
155 OPEN C1;
156 FETCH C1 BULK COLLECT INTO
157 l_cash_receipt_id,
158 l_receipt_number,
159 l_org_id,
160 l_bepcode,
161 l_bepmessage,
162 l_instrtype;
163 CLOSE C1;
164
165 IF PG_DEBUG in ('Y', 'C') THEN
166 arp_standard.debug('Close Cursor C1');
167 END IF;
168
169 fnd_file.put_line(FND_FILE.LOG,'Receipts being processed' || ' ' || 'Operating Unit');
170 fnd_file.put_line(FND_FILE.LOG, '------------------------'|| ' ' || '--------------');
171
172 k := l_org_id.count;
173 j := 0;
174 l_call_api := 'N';
175 IF l_org_id.count > 0 THEN
176 FOR i in l_org_id.first..l_org_id.last
177 LOOP
178 l_last_record := 'N';
179
180 IF i = k THEN
181 l_Last_record := 'Y';
182 l_call_api := 'Y';
183 END IF;
184
185 IF l_last_record <> 'Y' THEN
186 IF l_org_id(i) <> l_org_id(i+1) THEN
187 l_call_api := 'Y';
188 END IF;
189 END IF;
190
191 fnd_file.put_line(FND_FILE.LOG,rpad(substr(l_receipt_number(i),1,24),24, ' ' ) || ' ' || l_org_id(i));
192 j := j+1;
193 l_receipt_info.cash_receipt_id(j) := l_cash_receipt_id(i);
194 l_receipt_info.cc_error_code(j) := l_bepcode(i);
195 l_receipt_info.cc_error_text(j) := l_bepmessage(i);
196 l_receipt_info.cc_instrtype(j) := l_instrtype(i);
197
198 IF l_call_api = 'Y'
199 THEN
200
201 IF PG_DEBUG in ('Y', 'C')
202 THEN
203 arp_standard.debug('Setting Org Context for Org_Id: '||l_org_id(i));
204 END IF;
205
206 /*mo_global.set_policy_context('S', l_org_id(i));*/
207 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id(i),
208 p_return_status =>l_org_return_status);
209 IF l_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
210 fnd_file.put_line(FND_FILE.LOG,'l_org_return_status '|| l_org_return_status);
211 fnd_file.put_line(FND_FILE.LOG,'Org not getting processed '|| l_org_id(i));
212 ELSE
213
214 BEGIN
215 IF PG_DEBUG in ('Y', 'C')
216 THEN
217 fnd_file.put_line(FND_FILE.LOG,'Calling API Reverse_Remittances_in_err');
218 END IF;
219
220 AR_RECEIPT_API_PUB.Reverse_Remittances_in_err (
221 p_api_version => 1.0,
222 p_cash_receipts_id => l_receipt_info,
223 p_called_from => l_called_from,
224 p_commit => FND_API.G_TRUE,
225 x_return_status => l_return_status,
226 x_msg_count => l_msg_count,
227 x_msg_data => l_msg_data
228 );
229
230 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
231 THEN
232 -- Dump the error message in log for only those errored receipts.
233 fnd_file.put_line(FND_FILE.LOG,'l_return_status '|| l_return_status);
234 fnd_file.put_line(FND_FILE.LOG,'l_msg_count '|| l_msg_count);
235 fnd_file.put_line(FND_FILE.LOG,'l_msg_data '|| l_msg_data);
236 APP_EXCEPTION.RAISE_EXCEPTION;
237 END IF;
238
239 EXCEPTION
240 WHEN OTHERS THEN
241 -- Dump the error message in log for only those errored receipts.
242 fnd_file.put_line(FND_FILE.LOG,'l_return_status '|| l_return_status);
243 fnd_file.put_line(FND_FILE.LOG,'l_msg_count '|| l_msg_count);
244 fnd_file.put_line(FND_FILE.LOG,'l_msg_data '|| l_msg_data);
245 fnd_file.put_line(FND_FILE.LOG,'Sqlerrm '|| sqlerrm);
246 RAISE;
247 END;
248
249 END IF;
250 j := 0;
251 l_call_api := 'N';
252 l_receipt_info.cash_receipt_id := l_empty_receipt_info.cash_receipt_id;
253 l_receipt_info.cc_error_code := l_empty_receipt_info.cc_error_code;
254 l_receipt_info.cc_error_text := l_empty_receipt_info.cc_error_text;
255 l_receipt_info.cc_instrtype := l_empty_receipt_info.cc_instrtype;
256 END IF;
257 END LOOP;
258 ELSE
259 fnd_file.put_line(FND_FILE.LOG,'No receipts in Error for reverting remittance');
260 RETURN;
261 END IF;
262
263 /* Now call ARP_CORRECT_CC_ERRORS code to correct predefined errors */
264 /* This routine takes Request_Id as a parameter. Here request ID need to be gathered.*/
265
266 BEGIN
267
268 IF PG_DEBUG in ('Y', 'C') THEN
269 fnd_file.put_line(FND_FILE.LOG,'Call ARP_CORRECT_CC_ERRORS.cc_auto_correct_cover()+');
270 END IF;
271
272
273 SELECT DISTINCT cr.request_id , cr.org_id
274 BULK COLLECT INTO l_request_id, l_cc_org_id
275 FROM ar_cash_receipts_all cr,
276 ar_cash_receipt_history_all crh
277 WHERE cr.cash_receipt_id = crh.cash_receipt_id
278 AND cr.org_id = crh.org_id
279 AND cr.cc_error_flag = 'Y'
280 AND crh.status = 'CONFIRMED'
281 AND crh.current_record_flag = 'Y'
282 AND crh.request_id = fnd_global.conc_request_id;
283
284 IF l_request_id.count > 0 THEN
285 FOR i in l_request_id.first .. l_request_id.last LOOP
286 Begin
287
288 IF PG_DEBUG in ('Y', 'C') THEN
289 arp_standard.debug('Setting org context before calling CC Auto Correct');
290 arp_standard.debug('Org ID: ' || l_cc_org_id(i));
291 arp_standard.debug('Request ID: ' || l_request_id(i));
292 END IF;
293
294 mo_global.set_policy_context('S', l_cc_org_id(i));
295
296 ARP_CORRECT_CC_ERRORS.cc_auto_correct(
297 errbuf => l_error_buf,
298 retcode => l_ret_code,
299 p_request_id => l_request_id(i),
300 p_mode => 'REMITTANCE' );
301 IF l_ret_code <> 0 THEN
302 fnd_file.put_line(FND_FILE.LOG,l_error_buf);
303 APP_EXCEPTION.RAISE_EXCEPTION;
304 END IF;
305
306 EXCEPTION
307 WHEN OTHERS THEN
308 fnd_file.put_line(FND_FILE.LOG,'Exception inner ARP_CORRECT_CC_ERRORS.cc_auto_correct ' || sqlerrm);
309 RAISE;
310 END;
311 END LOOP;
312 END IF;
313
314 IF PG_DEBUG in ('Y', 'C') THEN
315 fnd_file.put_line(FND_FILE.LOG,'Call ARP_CORRECT_CC_ERRORS.cc_auto_correct_cover()-');
316 END IF;
317
318 EXCEPTION
319 WHEN NO_DATA_FOUND THEN
320 fnd_file.put_line(FND_FILE.LOG,'No receipt fetched for Credit Card Error Correction');
321
322 WHEN OTHERS THEN
323 fnd_file.put_line(FND_FILE.LOG,'Exception outer ARP_CORRECT_CC_ERRORS.cc_auto_correct ' || sqlerrm);
324 RAISE;
325 END;
326 /* ARP_CORRECT_CC_ERRORS code ends here */
327
328
329 IF PG_DEBUG in ('Y', 'C') THEN
330 fnd_file.put_line(FND_FILE.LOG,'AR_ECAPP_PKG.correct_settlement_error()-');
331 END IF;
332
333
334 EXCEPTION
335 WHEN OTHERS THEN
336 fnd_file.put_line(FND_FILE.LOG,'Exception AR_ECAPP_PKG.correct_settlement_error ' || sqlerrm);
337 RAISE;
338 END;
339
340
341
342 END AR_ECAPP_PKG;