[Home] [Help]
PACKAGE BODY: APPS.IBY_BANKPAYMENT_UPDT_PUB
Source
1 package body IBY_BANKPAYMENT_UPDT_PUB as
2 /*$Header: ibypbpupb.pls 115.9 2003/08/05 04:50:49 fxzhang noship $ */
3
4 -- package global variables
5 -- status codes used in this package
6 G_STATUS_SUCCESS CONSTANT NUMBER := IBY_UTILITY_PVT.STATUS_SUCCESS;
7 G_STATUS_BEP_ERROR CONSTANT NUMBER := IBY_UTILITY_PVT.STATUS_BEP_ERROR;
8 G_STATUS_BATCH_PARTIAL CONSTANT NUMBER := IBY_UTILITY_PVT.STATUS_BATCH_PARTIAL;
9 G_STATUS_QRY_BATCH_PARTIAL CONSTANT NUMBER := IBY_UTILITY_PVT.STATUS_QRY_BATCH_PARTIAL;
10 G_STATUS_QRY_BATCH_FAIL CONSTANT NUMBER := IBY_UTILITY_PVT.STATUS_QRY_BATCH_FAIL;
11 G_STATUS_QRY_BATCH_PENDING CONSTANT NUMBER := IBY_UTILITY_PVT.STATUS_QRY_BATCH_PENDING;
12 G_STATUS_QRY_TRXN_FAIL CONSTANT NUMBER := IBY_UTILITY_PVT.STATUS_QRY_TRXN_FAIL;
13
14 /* ========================================================================
15 -- Procedure Name: updateBatchStatus
16 --
17 -- Purpose: This procedure will update the iby_pay_batches_all table
18 -- with the new batch status value.
19 -- The possible batch status values considered at present
20 -- includes -
21 -- 1) 18 - Submitted to Processor
22 -- 2) 8 - Request not supported
23 -- 3) 12 - Scheduler in progess
24 -- 4) 101 - Communication error
25 -- 5) 301 - Formatted
26 -- 6) 302 - Confirmed
27 -- 7) 303 - Canceled
28 -- 8) 7 - Batch Failure
29 -- Parameters:
30 -- IN 1) p_batch_id NUMBER
31 -- The batch id.
32 --
33 -- 2)p_new_status NUMBER
34 -- The new batch status.
35 --
36 -- 2)p_error_code VARCHAR2
37 -- The error code if any.
38 --
39 -- 2)p_error_message VARCHAR2
40 -- The error message if any.
41 --
42 -- =======================================================================*/
43
44 procedure updateBatchStatus(
45 p_batch_id IN iby_pay_batches_all.batch_id%TYPE,
46 p_new_status IN iby_pay_batches_all.batch_status%TYPE,
47 p_error_code IN iby_pay_batches_all.bep_code%TYPE,
48 p_error_message IN iby_pay_batches_all.bep_message%TYPE
49 )
50 IS
51
52 begin
53
54 update iby_pay_batches_all
55 set batch_status = p_new_status,
56 bep_code = p_error_code,
57 bep_message = p_error_message,
58 last_update_date = sysdate,
59 last_updated_by = fnd_global.user_id,
60 object_version_number = object_version_number + 1
61 where batch_id = p_batch_id;
62
63 commit;
64
65 end updateBatchStatus;
66
67 /* ========================================================================
68 -- Procedure Name: updateECBatches
69 --
70 -- Purpose: This procedure will update all EC batches associated with
71 -- an iPayment batch to a new status.
72 -- =======================================================================*/
73
74 PROCEDURE updateECBatches
75 (
76 payerid_in IN iby_pay_batches_all.payer_id%TYPE,
77 bepid_in IN iby_pay_batches_all.bepid%TYPE,
78 bepkey_in IN iby_pay_batches_all.bepkey%TYPE,
79 oldstatus_in IN iby_pay_batches_all.batch_status%TYPE,
80 newstatus_in IN iby_pay_batches_all.batch_status%TYPE,
81 oldbatchid_in IN iby_pay_batches_all.iby_batch_id%TYPE,
82 newbatchid_in IN iby_pay_batches_all.iby_batch_id%TYPE
83 )
84 IS
85
86 BEGIN
87 UPDATE iby_pay_batches_all
88 SET
89 batch_status = newstatus_in,
90 iby_batch_id = newbatchid_in,
91 last_update_date = sysdate,
92 last_updated_by = fnd_global.user_id,
93 object_version_number = object_version_number + 1
94
95 WHERE bepid = bepid_in
96 AND bepkey = bepkey_in
97 AND payer_id = payerid_in
98 AND batch_status = oldstatus_in
99 AND ((iby_batch_id IS NULL AND oldbatchid_in IS NULL) OR (iby_batch_id = oldbatchid_in));
100
101 COMMIT;
102
103 END updateECBatches;
104
105
106 /* ========================================================================
107 -- Procedure Name: updateTrxnStatus
108 --
109 -- Purpose: This procedure updates the transaction status field
110 -- in the iby_pay_payments_all table with the new transaction
111 -- status value. For a particular batch, all the
112 -- transactions will be updated with the same status value.
113 -- Since the batch acknowledgment is not considered at
114 -- present this condition is sufficient.
115 -- The possible transaction status values considered at
116 -- present includes
117 -- 1) 18 - Submitted to Processor
118 -- 2) 8 - Request not supported
119 -- 3) 11 - Pending
120 -- 4) 20 - Invalid Transaction
121 -- 5) 21 - Transaction Stopped
122 -- Parameters:
123 -- IN 1) p_batch_id NUMBER
124 -- The batch id.
125 --
126 -- 2) p_trxn_id NUMBER
127 -- The transaction id.
128 --
129 -- 3) p_new_status NUMBER
130 -- The new transaction status.
131 --
132 -- 4) p_error_code VARCHAR2
133 -- The error code if any.
134 --
135 -- 5) p_error_message VARCHAR2
136 -- The error message if any.
137 --
138 -- =======================================================================*/
139
140 PROCEDURE updateTrxnStatus(
141
142 p_batch_id IN iby_pay_payments_all.batch_id%TYPE,
143 p_trxn_id IN iby_pay_payments_all.pmt_trxn_id%TYPE,
144 p_new_status IN iby_pay_payments_all.pmt_status%TYPE,
145 p_error_code IN iby_pay_payments_all.bep_code%TYPE,
146 p_error_message IN iby_pay_payments_all.bep_message%TYPE
147 )
148 IS
149
150 begin
151
152 IF (p_trxn_id <> 0) THEN
153
154 update iby_pay_payments_all
155 set pmt_status = p_new_status,
156 bep_code = p_error_code,
157 bep_message = p_error_message,
158 last_update_date = sysdate,
159 last_updated_by = fnd_global.user_id,
160 object_version_number = object_version_number + 1
161 where batch_id = p_batch_id and pmt_trxn_id = p_trxn_id;
162
163 ELSE
164
165 update iby_pay_payments_all
166 set pmt_status = p_new_status,
167 bep_code = p_error_code,
168 bep_message = p_error_message,
169 last_update_date = sysdate,
170 last_updated_by = fnd_global.user_id,
171 object_version_number = object_version_number + 1
172 where batch_id = p_batch_id;
173
174 END IF;
175
176 commit;
177
178 end updateTrxnStatus;
179
180
181
182
183
184 /* ========================================================================
185 -- Procedure Name: setBatchFail
186 --
187 -- Purpose: This procedure updates the iPayment batch and all
188 -- its child EC batches and trxns to fail based
189 -- on the batch query result returned from the servlet.
190 -- 1) 207 - Batch failed at Bank. for iby merged batch
191 -- and EC batch
192 -- 2) 220 - Transaction failed at Bank. for trxn
193 --
194 -- Parameters:
195 -- IN
196 -- 1) p_payerid VARCHAR2
197 -- The payerid for the iby batch
198 -- iby_batches_all.payeeid
199 --
200 -- 2) p_iby_batchid VARCHAR2
201 -- The iby merged batch (message) id
202 -- iby_batches_all.batchid
203 --
204 -- 3)p_error_code VARCHAR2
205 -- The error code if any.
206 --
207 -- 4)p_error_message VARCHAR2
208 -- The error message if any.
209 --
210 -- =======================================================================*/
211 PROCEDURE setBatchFail (
212 p_payerid IN VARCHAR2,
213 p_iby_batchid IN VARCHAR2,
214 p_error_code IN VARCHAR2,
215 p_error_message IN VARCHAR2
216 )
217 IS
218
219 CURSOR l_ec_batch_csr (p_payerid IN VARCHAR2, p_iby_batchid IN VARCHAR2) IS
220 SELECT pb.batch_id
221 FROM IBY_PAY_BATCHES_ALL pb,
222 IBY_BATCHES_ALL ibyb
223 WHERE ibyb.batchid = p_iby_batchid
224 AND ibyb.payeeid = p_payerid;
225
226 BEGIN
227
228 FOR l_ec_batch_rec IN l_ec_batch_csr(p_payerid, p_iby_batchid) LOOP
229
230 -- update child trxns
231 -- of the EC batch
232 UPDATE iby_pay_payments_all
233 SET
234 pmt_status = G_STATUS_QRY_TRXN_FAIL,
235 bep_code = p_error_code,
236 bep_message = p_error_message,
237 last_update_date = sysdate,
238 last_updated_by = fnd_global.user_id,
239 last_update_login = fnd_global.conc_login_id,
240 object_version_number = object_version_number + 1
241 WHERE batch_id = l_ec_batch_rec.batch_id;
242
243 -- update the EC batch
244 UPDATE iby_pay_batches_all
245 SET
246 batch_status = G_STATUS_QRY_BATCH_FAIL,
247 bep_code = p_error_code,
248 bep_message = p_error_message,
249 last_update_date = sysdate,
250 last_updated_by = fnd_global.user_id,
251 last_update_login = fnd_global.conc_login_id,
252 object_version_number = object_version_number + 1
253 WHERE batch_id = l_ec_batch_rec.batch_id;
254
255 END LOOP;
256
257 -- update the iby merged batch
258 UPDATE iby_batches_all
259 SET
260 batchstatus = G_STATUS_QRY_BATCH_FAIL,
261 bepcode = p_error_code,
262 bepmessage = p_error_message,
263 last_update_date = sysdate,
264 last_updated_by = fnd_global.user_id,
265 last_update_login = fnd_global.conc_login_id,
266 object_version_number = object_version_number + 1
267 WHERE batchid = p_iby_batchid
268 AND payeeid = p_payerid;
269
270 END;
271
272
273
274 /* ========================================================================
275 -- Procedure Name: updateQueryStatus
276 --
277 -- Purpose: This procedure updates the iPayment batch and all
278 -- its child EC batches - those do not have a finished
279 -- status based on the trxn statuses returned from
280 -- the servlet. Note the applicable trxn statuses updates
281 -- must have been done before this procedure is called.
282 -- It's not an error if this procedure is called for
283 -- a iby merged batch that has a final status. In that
284 -- case nothing will be done. Same goes for an EC batch
285 -- with a final status.
286 -- Statuses considered to be final:
287 -- iby batch:
288 -- 1) 0 - success
289 -- 2) 206 - partial sucess -
290 -- all child batch and trxn status known
291 -- 3) 207 - Batch failed at Bank.
292 -- EC batch:
293 -- 1) 0 - success
294 -- 2) 206 - partial sucess -
295 -- all child trxn status known
296 -- 3) 207 - Batch failed at Bank.
297 -- Trxn:
298 -- 1) 0 - success
299 -- 2) 5 - BEP specific error
300 -- 3) 220 - Transaction failed at Bank
301 --
302 -- Parameters:
303 -- IN
304 -- 1) p_payerid VARCHAR2
305 -- The payerid for the iby batch
306 -- iby_batches_all.payeeid
307 --
308 -- 2) p_iby_batchid VARCHAR2
309 -- The iby merged batch (message) id
310 -- iby_batches_all.batchid
311 --
312 -- =======================================================================*/
313 PROCEDURE updateQueryStatus (
314 p_payerid IN VARCHAR2,
315 p_iby_batchid IN VARCHAR2
316 )
317
318 IS
319
320 l_unfinished_ecbatch_notfound BOOLEAN := TRUE;
321 l_finished_ecbatch_found BOOLEAN := FALSE;
322 --l_unsuccess_ecbatch_notfound BOOLEAN := FALSE;
323 l_unfinished_trxn_notfound BOOLEAN := FALSE;
324 l_finished_trxn_found BOOLEAN := FALSE;
325 --l_unsuccess_trxn_notfound BOOLEAN := FALSE;
326 l_ecbatch_id NUMBER;
327 l_pmt_trxn_id NUMBER;
328 l_batch_status NUMBER;
329 l_trxn_count NUMBER;
330 l_failed_trxn_count NUMBER;
331 l_ecbatch_count NUMBER;
332 l_failed_ecbatch_count NUMBER;
333
334
335 CURSOR l_ec_batch_csr (p_payerid IN VARCHAR2, p_iby_batchid IN VARCHAR2) IS
336 SELECT pb.batch_id, pb.batch_status
337 FROM IBY_PAY_BATCHES_ALL pb,
338 IBY_BATCHES_ALL ibyb
339 WHERE ibyb.batchid = p_iby_batchid
340 AND ibyb.payeeid = p_payerid
341 AND pb.batch_status not in (G_STATUS_SUCCESS, G_STATUS_QRY_BATCH_PARTIAL, G_STATUS_QRY_BATCH_FAIL);
342
343 CURSOR l_finished_ecbatch_csr (p_payerid IN VARCHAR2, p_iby_batchid IN VARCHAR2) IS
344 SELECT pb.batch_id
345 FROM IBY_PAY_BATCHES_ALL pb,
346 IBY_BATCHES_ALL ibyb
347 WHERE ibyb.batchid = p_iby_batchid
348 AND ibyb.payeeid = p_payerid
349 AND pb.batch_status in (G_STATUS_SUCCESS, G_STATUS_QRY_BATCH_PARTIAL, G_STATUS_QRY_BATCH_FAIL);
350
351 -- count of all EC batches in an iby merged batch
352 CURSOR l_ecbatch_cnt_csr (p_payerid IN VARCHAR2, p_iby_batchid IN VARCHAR2) IS
353 SELECT count(pb.batch_id)
354 FROM IBY_PAY_BATCHES_ALL pb,
355 IBY_BATCHES_ALL ibyb
356 WHERE ibyb.batchid = p_iby_batchid
357 AND ibyb.payeeid = p_payerid;
358
359 -- count of all unsuccessful EC batches in an iby merged batch
360 CURSOR l_failed_ecbatch_cnt_csr (p_payerid IN VARCHAR2, p_iby_batchid IN VARCHAR2) IS
361 SELECT count(pb.batch_id)
362 FROM IBY_PAY_BATCHES_ALL pb,
363 IBY_BATCHES_ALL ibyb
364 WHERE ibyb.batchid = p_iby_batchid
365 AND ibyb.payeeid = p_payerid
366 AND pb.batch_status in (G_STATUS_QRY_BATCH_PARTIAL, G_STATUS_QRY_BATCH_FAIL);
367
368 CURSOR l_unfinished_trxn_csr (p_batch_id IN NUMBER) IS
369 SELECT pt.pmt_trxn_id
370 FROM IBY_PAY_PAYMENTS_ALL pt
371 WHERE pt.batch_id = p_batch_id
372 AND pt.pmt_status not in (G_STATUS_SUCCESS, G_STATUS_BEP_ERROR, G_STATUS_QRY_TRXN_FAIL);
373
374 CURSOR l_finished_trxn_csr (p_batch_id IN NUMBER) IS
375 SELECT pt.pmt_trxn_id
376 FROM IBY_PAY_PAYMENTS_ALL pt
377 WHERE pt.batch_id = p_batch_id
378 AND pt.pmt_status in (G_STATUS_SUCCESS, G_STATUS_BEP_ERROR, G_STATUS_QRY_TRXN_FAIL);
379
380 -- all trxns of an EC batch
381 CURSOR l_trxn_cnt_csr (p_batch_id IN NUMBER) IS
382 SELECT count(pt.pmt_trxn_id)
383 FROM IBY_PAY_PAYMENTS_ALL pt
384 WHERE pt.batch_id = p_batch_id;
385
386 -- unsuccessful trxns of an EC batch
387 CURSOR l_failed_trxn_cnt_csr (p_batch_id IN NUMBER) IS
388 SELECT count(pt.pmt_trxn_id)
389 FROM IBY_PAY_PAYMENTS_ALL pt
390 WHERE pt.batch_id = p_batch_id
391 --AND pt.pmt_status <> G_STATUS_SUCCESS;
392 AND pt.pmt_status in (G_STATUS_BEP_ERROR, G_STATUS_QRY_TRXN_FAIL);
393
394 BEGIN
395
396 FOR l_ec_batch_rec IN l_ec_batch_csr(p_payerid, p_iby_batchid) LOOP
397
398 l_batch_status := l_ec_batch_rec.batch_status;
399 l_unfinished_ecbatch_notfound := false;
400
401 OPEN l_unfinished_trxn_csr(l_ec_batch_rec.batch_id);
402 FETCH l_unfinished_trxn_csr INTO l_pmt_trxn_id;
403 l_unfinished_trxn_notfound := l_unfinished_trxn_csr%NOTFOUND;
404 CLOSE l_unfinished_trxn_csr;
405
406 -- if no unfinished trxn for the EC batch
407 IF l_unfinished_trxn_notfound THEN
408
409 OPEN l_trxn_cnt_csr(l_ec_batch_rec.batch_id);
410 FETCH l_trxn_cnt_csr INTO l_trxn_count;
411 CLOSE l_trxn_cnt_csr;
412
413 OPEN l_failed_trxn_cnt_csr(l_ec_batch_rec.batch_id);
414 FETCH l_failed_trxn_cnt_csr INTO l_failed_trxn_count;
415 CLOSE l_failed_trxn_cnt_csr;
416
417 -- all child trxns are sucessful
418 IF l_failed_trxn_count = 0 THEN
419 l_batch_status := G_STATUS_SUCCESS;
420 ELSIF l_failed_trxn_count < l_trxn_count THEN
421 l_batch_status := G_STATUS_QRY_BATCH_PARTIAL;
422 ELSE
423 l_batch_status := G_STATUS_QRY_BATCH_FAIL;
424 END IF;
425
426 -- exists unfinished trxn
430
427 -- see if any finished trxn
428 -- if so change batch status to G_STATUS_QRY_BATCH_PENDING
429 ELSE
431 OPEN l_finished_trxn_csr(l_ec_batch_rec.batch_id);
432 FETCH l_finished_trxn_csr INTO l_pmt_trxn_id;
433 l_finished_trxn_found := l_finished_trxn_csr%FOUND;
434 CLOSE l_finished_trxn_csr;
435
436 -- found some finished trxns
437 IF l_finished_trxn_found THEN
438 l_batch_status := G_STATUS_QRY_BATCH_PENDING;
439 END IF;
440
441 END IF;
442
443 -- check to see if we have computed a new status
444 IF l_batch_status <> l_ec_batch_rec.batch_status THEN
445
446 -- update the EC batch
447 -- note as the status is synthesized
448 -- we don't have bep_code and message
449 UPDATE iby_pay_batches_all
450 SET
451 batch_status = l_batch_status,
452 bep_code = null,
453 bep_message = null,
454 last_update_date = sysdate,
455 last_updated_by = fnd_global.user_id,
456 last_update_login = fnd_global.conc_login_id,
457 object_version_number = object_version_number + 1
458 WHERE batch_id = l_ec_batch_rec.batch_id;
459
460 END IF;
461
462 END LOOP;
463
464 -- no unfinished EC batches
465 IF l_unfinished_ecbatch_notfound THEN
466
467 OPEN l_ecbatch_cnt_csr(p_payerid, p_iby_batchid);
468 FETCH l_ecbatch_cnt_csr INTO l_ecbatch_count;
469 CLOSE l_ecbatch_cnt_csr;
470
471 OPEN l_failed_ecbatch_cnt_csr(p_payerid, p_iby_batchid);
472 FETCH l_failed_ecbatch_cnt_csr INTO l_failed_ecbatch_count;
473 CLOSE l_failed_ecbatch_cnt_csr;
474
475 -- all child EC batches are sucessful
476 IF l_failed_ecbatch_count = 0 THEN
477 l_batch_status := G_STATUS_SUCCESS;
478 ELSIF l_failed_ecbatch_count < l_ecbatch_count THEN
479 l_batch_status := G_STATUS_QRY_BATCH_PARTIAL;
480 ELSE
481 l_batch_status := G_STATUS_QRY_BATCH_FAIL;
482 END IF;
483
484 -- update the iby merged batch
485 UPDATE iby_batches_all
486 SET
487 batchstatus = l_batch_status,
488 bepcode = null,
489 bepmessage = null,
490 last_update_date = sysdate,
491 last_updated_by = fnd_global.user_id,
492 last_update_login = fnd_global.conc_login_id,
493 object_version_number = object_version_number + 1
494 WHERE batchid = p_iby_batchid
495 AND payeeid = p_payerid;
496
497 -- exists unfinished EC batches
498 -- see if any finished EC batches
499 ELSE
500
501 OPEN l_finished_ecbatch_csr(p_payerid, p_iby_batchid);
502 FETCH l_finished_ecbatch_csr INTO l_ecbatch_id;
503 l_finished_ecbatch_found := l_finished_ecbatch_csr%FOUND;
504 CLOSE l_finished_ecbatch_csr;
505
506 -- found some finished EC batches
507 IF l_finished_ecbatch_found THEN
508 l_batch_status := G_STATUS_QRY_BATCH_PENDING;
509 END IF;
510
511 -- update the iby merged batch
512 UPDATE iby_batches_all
513 SET
514 batchstatus = l_batch_status,
515 bepcode = null,
516 bepmessage = null,
517 last_update_date = sysdate,
518 last_updated_by = fnd_global.user_id,
519 last_update_login = fnd_global.conc_login_id,
520 object_version_number = object_version_number + 1
521 WHERE batchid = p_iby_batchid
522 AND payeeid = p_payerid;
523
524 END IF;
525
526 END;
527
528
529
530 end IBY_BANKPAYMENT_UPDT_PUB;