[Home] [Help]
PACKAGE BODY: APPS.IBY_DBCCARD_PVT
Source
1 PACKAGE BODY IBY_DBCCARD_PVT AS
2 /*$Header: ibyvdbcb.pls 120.5 2011/03/28 11:35:28 lmallick ship $*/
3
4 --------------------------------------------------------------------------------------
5 -- Global Variable Declaration --
6 --------------------------------------------------------------------------------------
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_DBCCARD_PVT';
9 g_validation_level CONSTANT NUMBER := FND_API.G_VALID_LEVEL_FULL;
10
11 --------------------------------------------------------------------------------------
12 -- API Signatures--
13 --------------------------------------------------------------------------------------
14
15 /*
16 The following function is a wrapper on a GL function that returns a converted amount.
17 If the rate is not found or the currency does not exist, GL functions a negative number.
18 */
19 FUNCTION Convert_Amount ( from_currency VARCHAR2,
20 to_currency VARCHAR2,
21 eff_date DATE,
22 amount NUMBER,
23 conv_type VARCHAR2
24 ) RETURN NUMBER IS
25
26 converted_amount NUMBER;
27 BEGIN
28
29 converted_amount := amount;
30
31 IF( amount is NULL) THEN
32 converted_amount := 0;
33 END IF;
34
35 converted_amount := GL_CURRENCY_API.CONVERT_AMOUNT_SQL( from_currency,
36 to_currency,
37 eff_date,
38 conv_type,
39 converted_amount
40 );
41
42 RETURN converted_amount;
43
44 EXCEPTION
45 WHEN OTHERS THEN
46 converted_amount := -1;
47 RETURN converted_amount;
48
49 END Convert_Amount;
50
51
52 /*
53 The following function filters all the statuses that are supported in this release.
54 It returns an appropriate value if a match is found, else it returns 'UNKNOWN'.
55 */
56 FUNCTION get_status_meaning ( status NUMBER
57 ) RETURN VARCHAR2 IS
58
59 BEGIN
60
61 IF( status = 0 ) THEN
62 RETURN C_STATUS_SUCCESS;
63 ELSIF( status IN (-99,1,2,4,5,8,15,16,17,19,20,21,9999) ) THEN
64 RETURN C_STATUS_FAILED;
65 ELSIF( status IN (100,109,111) ) THEN
66 RETURN C_STATUS_PENDING;
67 ELSE
68 RETURN C_STATUS_UNKNOWN;
69 END IF;
70
71 EXCEPTION
72 WHEN OTHERS THEN
73 RETURN C_STATUS_UNKNOWN;
74
75 END get_status_meaning;
76
77 /*
78 The following procedure will sort the records based on the statuses.
79 */
80
81 PROCEDURE bubble_sort( l_failTemp IN OUT NOCOPY TrxnFail_tbl_type
82 ) IS
83
84 switch TrxnFail_tbl_type;
85 l_cnt PLS_INTEGER;
86
87 BEGIN
88
89 l_cnt := l_failTemp.COUNT;
90
91 --sort by total_trxn
92 FOR outer_loop IN 1..l_cnt LOOP
93 FOR counter IN REVERSE outer_loop..l_cnt-1 LOOP
94 IF (l_failTemp(counter).totalTrxn < l_failTemp(counter+1).totalTrxn) THEN
95 switch(1) := l_failTemp(counter);
96 l_failTemp(counter) := l_failTemp(counter+1);
97 l_failTemp(counter+1) := switch(1);
98 END IF;
99 END LOOP;
100 END LOOP;
101
102 END bubble_sort;
103
104
105 /*
106 The following function gets the CAUSE for the error
107 */
108
109 FUNCTION get_status_cause ( status NUMBER
110 ) RETURN VARCHAR2 IS
111
112 BEGIN
113
114 IF( status = -99) THEN
115 RETURN 'Invalid Status';
116 ELSIF( status = 1) THEN
117 RETURN 'Communication Error';
118 ELSIF( status = 2) THEN
119 RETURN 'Duplicate Order Id';
120 ELSIF( status = 4) THEN
121 RETURN 'Field Missing';
122 ELSIF( status = 5) THEN
123 RETURN 'Back End Payment System returned Error';
124 ELSIF( status = 8) THEN
125 RETURN 'Status not supported';
126 ELSIF( status = 15) THEN
127 RETURN 'Failed to Schedule';
128 ELSIF( status = 16) THEN
129 RETURN 'Failed at Back End Payment System';
130 ELSIF( status = 17) THEN
131 RETURN 'Unable to Pay';
132 ELSIF( status = 19) THEN
133 RETURN 'Invalid CreditCard';
134 ELSIF( status = 20) THEN
135 RETURN 'Transaction Declined';
136 ELSIF( status = 21) THEN
137 RETURN 'Voice Authorization Required';
138 ELSIF( status = 9999) THEN
139 RETURN 'Timed Out';
140 ELSE
141 RETURN 'Unknown';
142 END IF;
143
144
145 EXCEPTION
146 WHEN OTHERS THEN
147 RETURN 'Unknown';
148
149 END get_status_cause;
150
151
152 /*
153 The following function will either chop or pad the table so that the length is 'l_length'.
154 */
155
156 PROCEDURE get_final_padded( l_failTemp IN OUT NOCOPY TrxnFail_tbl_type,
157 l_length IN NUMBER
158 ) IS
159
160 l_final_tbl TrxnFail_tbl_type;
161 BEGIN
162
163 IF( l_failTemp.COUNT = l_length ) THEN
164 RETURN;
165 ELSIF(l_failTemp.COUNT > l_length ) THEN
166 FOR i IN 1..l_length LOOP
167 l_final_tbl(i) := l_failTemp(i);
168 END LOOP;
169 l_failTemp := l_final_tbl;
170 ELSE
171 FOR i IN (l_failTemp.COUNT + 1)..l_length LOOP
172 l_failTemp(i).cause := ' ';
173 l_failTemp(i).columnId := i;
174 END LOOP;
175 END IF;
176
177 END get_final_padded;
178
179
180 /*
181 The following function gets the DATE that should be used
182 depending on the period passed.
183 */
184
185 FUNCTION get_date ( l_period VARCHAR2
186 ) RETURN DATE IS
187
188 BEGIN
189
190 -- Set the date.
191 IF ( l_period = C_PERIOD_DAILY ) THEN
192 RETURN TRUNC(SYSDATE);
193 ELSIF ( l_period = C_PERIOD_WEEKLY ) THEN
194 -- We don't want the trailing 7 days to overlap between months.
195 -- If the last 7 days also include a portion of last month then
196 -- we just take data for the current month.
197 IF( TO_NUMBER( TO_CHAR( SYSDATE, 'dd')) < 7 ) THEN
198 RETURN TRUNC(SYSDATE, 'mm');
199 ELSE
200 RETURN TRUNC(SYSDATE - 6);
201 END IF;
202 ELSE
203 RETURN TRUNC(SYSDATE, 'mm');
204 END IF;
205
206 EXCEPTION
207 WHEN OTHERS THEN
208 RETURN TRUNC(SYSDATE);
209
210 END get_date;
211
212 --------------------------------------------------------------------------------------
213 -- 1. Get_Trxn_Summary
214 -- Start of comments
215 -- API name : Get_Trxn_Summary
216 -- Type : Private
217 -- Pre-reqs : None
218 -- Function : Fetches the information for a trsnaction.
219 -- Parameters :
220 -- IN : payee_id IN VARCHAR2
221 -- period IN VARCHAR2 Required
222 -- summary_tbl OUT Summary_tbl_type
223 -- trxnSum_tbl OUT TrxnSum_tbl_type
224 -- End of comments
225 --------------------------------------------------------------------------------------
226 Procedure Get_Trxn_Summary ( payee_id IN VARCHAR2,
227 period IN VARCHAR2,
228 summary_tbl OUT NOCOPY Summary_tbl_type,
229 trxnSum_tbl OUT NOCOPY TrxnSum_tbl_type
230 ) IS
231
232 CURSOR auth_summary_csr(l_date DATE, l_payeeid VARCHAR2) IS
233 SELECT CurrencyNameCode currency,
234 InstrType,
235 Status,
236 COUNT(*) total_trxn,
237 SUM(amount) total_amt,
238 TRUNC(updatedate) trxndate
239 FROM iby_trxn_summaries_all
240 WHERE TRUNC(updatedate) >= l_date
241 AND trxntypeid IN (2,3)
242 AND instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
243 AND payeeid LIKE l_payeeId
244 GROUP BY INSTRTYPE,CurrencyNameCode, STATUS, TRUNC(updatedate)
245 ORDER BY INSTRTYPE,STATUS ASC;
246
247 CURSOR capt_summary_csr(l_date DATE, l_payeeid VARCHAR2) IS
248 SELECT CurrencyNameCode currency,
249 InstrType,
250 Status,
251 COUNT(*) total_trxn,
252 SUM(amount) total_amt,
253 TRUNC(updatedate) trxndate
254 FROM iby_trxn_summaries_all
255 WHERE TRUNC(updatedate) >= l_date
256 AND trxntypeid IN (3,8,9)
257 AND instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
258 AND payeeid LIKE l_payeeId
259 GROUP BY INSTRTYPE,CurrencyNameCode, STATUS, TRUNC(updatedate)
260 ORDER BY INSTRTYPE,STATUS ASC;
261
262 CURSOR cred_summary_csr(l_date DATE, l_payeeid VARCHAR2) IS
263 SELECT CurrencyNameCode currency,
264 InstrType,
265 Status,
266 COUNT(*) total_trxn,
267 SUM(amount) total_amt,
268 TRUNC(updatedate) trxndate
269 FROM iby_trxn_summaries_all
270 WHERE TRUNC(updatedate) >= l_date
271 AND trxntypeid IN (5,10,11)
272 AND instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
273 AND payeeid LIKE l_payeeId
274 GROUP BY INSTRTYPE,CurrencyNameCode, STATUS, TRUNC(updatedate)
275 ORDER BY INSTRTYPE,STATUS ASC;
276
277 CURSOR load_auth_outstand_csr(l_date DATE, l_payeeid VARCHAR2) IS
278 SELECT CurrencyNameCode currency,
279 COUNT(*) total_trxn,
280 SUM(amount) total_amt,
281 TRUNC(updatedate) trxndate
282 FROM iby_trxn_summaries_all
283 WHERE transactionid IN
284 (
285 SELECT transactionid
286 FROM iby_trxn_summaries_all
287 WHERE instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
288 AND status = 0
289 GROUP BY transactionid
290 HAVING COUNT(*) = 1
291 )
292 AND TRUNC(updatedate) >= l_date
293 AND trxntypeid = 2
294 AND payeeid LIKE l_payeeid
295 GROUP BY CurrencyNamecode, TRUNC(updatedate)
296 ORDER BY CurrencyNameCode;
297
298 -- All Transactions.
299 l_all_trxns_no NUMBER := 0;
300 l_all_trxns_amt NUMBER(38,2) := 0;
301
302 -- Total Authorization Requests
303 l_total_auth_no NUMBER := 0;
304 l_total_auth_amt NUMBER(38,2) := 0;
305
306 -- Total Capture/Settlement Requests
307 l_total_capt_no NUMBER := 0;
308 l_total_capt_amt NUMBER(38,2) := 0;
309
310 -- Total Refunds/Credits Requests
311 l_total_Cred_no NUMBER := 0;
312 l_total_Cred_amt NUMBER(38,2) := 0;
313
314 -- Total Authorizations Settled
315 l_total_authSet_no NUMBER := 0;
316 l_total_authSet_amt NUMBER(38,2) := 0;
317
318 -- Total Authorizations Outstanding
319 l_total_authOut_no NUMBER := 0;
320 l_total_authOut_amt NUMBER(38,2) := 0;
321
322 -- Total Credit Card Transactions
323 l_total_ccard_no NUMBER := 0;
324 l_total_ccard_amt NUMBER(38,2) := 0;
325
326 -- Total Purchase Card Transactions
327 l_total_pcard_no NUMBER := 0;
328 l_total_pcard_amt NUMBER(38,2) := 0;
329
330 -- Following are for Transaction Summary Table
331
332 -- Authorization Requests
333 l_auth_succ_no NUMBER := 0;
334 l_auth_fail_no NUMBER := 0;
335 l_auth_pend_no NUMBER := 0;
336
337 -- Capture/Settlement Requests
338 l_capt_succ_no NUMBER := 0;
339 l_capt_fail_no NUMBER := 0;
340 l_capt_pend_no NUMBER := 0;
341
342 -- Refunds/Credits Requests
343 l_cred_succ_no NUMBER := 0;
344 l_cred_fail_no NUMBER := 0;
345 l_cred_pend_no NUMBER := 0;
346
347 -- other local variables
348 l_updatedate DATE;
349 l_payeeId VARCHAR2(80);
350 l_amount NUMBER := 0;
351 l_status VARCHAR2(15);
352
353 -- Bug 3714173: reporting currency
354 l_to_currency VARCHAR2(10);
355
356 l_conv_type VARCHAR2(10);
357
358 BEGIN
359 -- Bug 3714173: Retrieve the reporting currency
360 l_to_currency := nvl(fnd_profile.value('IBY_DBC_REPORTING_CURRENCY'), 'USD');
361
362 l_conv_type := fnd_profile.value('IBY_DBC_REPORTING_CURR_CONV_RATE_TYPE');
363
364 -- Set the payee value accordingly.
365 IF( payee_id is NULL ) THEN
366 l_payeeId := '%';
367 ELSE
368 l_payeeId := TRIM(payee_id);
369 END IF;
370
371 -- Set the date.
372 l_updatedate := get_date(period);
373
374 -- close the cursors, if it is already open.
375 IF( auth_summary_csr%ISOPEN ) THEN
376 CLOSE auth_summary_csr;
377 END IF;
378
379 /* --- Processing Authorization Requests ---- */
380
381 FOR t_auths IN auth_summary_csr( l_updatedate, l_payeeId) LOOP
382
383 -- Bug 3714173: reporting currency is from the profile option
384 -- l_amount := Convert_Amount( t_auths.currency, C_TO_CURRENCY, t_auths.trxndate, t_auths.total_amt, NULL);
385 l_amount := Convert_Amount( t_auths.currency, l_to_currency, t_auths.trxndate, t_auths.total_amt, l_conv_type);
386 l_status := get_status_meaning( t_auths.status);
387
388 -- We ignore the cases when the RATE or CURRENCY is not found.
389 -- We only process if the status is supported.
390 IF ( (l_amount >= 0) AND (l_status <> C_STATUS_UNKNOWN) ) THEN
391
392 -- Add up all auths.
393 l_total_auth_no := l_total_auth_no + t_auths.total_trxn;
394 l_total_auth_amt := l_total_auth_amt + l_amount;
395
396 -- Bug 3306449: Only capture trans count.
397 -- Bug 3458221: Re-install the auth trans count.
398 -- Add all creditcard or purchasecard trxns
399 IF( t_auths.instrtype = C_INSTRTYPE_CREDITCARD ) THEN
400 l_total_ccard_no := l_total_ccard_no + t_auths.total_trxn;
401 l_total_ccard_amt := l_total_ccard_amt + l_amount;
402 ELSIF( t_auths.instrtype = C_INSTRTYPE_PURCHASECARD ) THEN
403 l_total_pcard_no := l_total_pcard_no + t_auths.total_trxn;
404 l_total_pcard_amt := l_total_pcard_amt + l_amount;
405 END IF;
406
407 -- Add up all auths based on status.
408 IF( l_status = C_STATUS_SUCCESS ) THEN
409 l_auth_succ_no := l_auth_succ_no + t_auths.total_trxn;
410 ELSIF( l_status = C_STATUS_FAILED ) THEN
411 l_auth_fail_no := l_auth_fail_no + t_auths.total_trxn;
412 ELSIF( l_status = C_STATUS_PENDING ) THEN
413 l_auth_pend_no := l_auth_pend_no + t_auths.total_trxn;
414 END IF;
415
416 END IF; -- for check l_amount > 0
417
418 END LOOP; -- For Authorization Requests
419
420 -- close the cursors, if it is already open.
421 IF( capt_summary_csr%ISOPEN ) THEN
422 CLOSE capt_summary_csr;
423 END IF;
424
425 /* --- Processing Capture/Settlement Requests ---- */
426
427 FOR t_capts IN capt_summary_csr( l_updatedate, l_payeeId) LOOP
428
429 -- Bug 3714173: reporting currency is from the profile option
430 -- l_amount := Convert_Amount( t_capts.currency, C_TO_CURRENCY, t_capts.trxndate, t_capts.total_amt, NULL);
431 l_amount := Convert_Amount( t_capts.currency, l_to_currency, t_capts.trxndate, t_capts.total_amt, l_conv_type);
432 l_status := get_status_meaning( t_capts.status);
433
434 -- We ignore the cases when the RATE or CURRENCY is not found.
435 -- We only process if the status is supported.
436 IF ( (l_amount >= 0) AND (l_status <> C_STATUS_UNKNOWN) ) THEN
437
438 -- Add up all captures/settlements.
439 l_total_capt_no := l_total_capt_no + t_capts.total_trxn;
440 l_total_capt_amt := l_total_capt_amt + l_amount;
441
442 -- Bug 3458221: Only auth trans count in calculating totals.
443 -- Add all creditcard or purchasecard trxns
444 /*
445 IF( t_capts.instrtype = C_INSTRTYPE_CREDITCARD ) THEN
446 l_total_ccard_no := l_total_ccard_no + t_capts.total_trxn;
447 l_total_ccard_amt := l_total_ccard_amt + l_amount;
448 ELSIF( t_capts.instrtype = C_INSTRTYPE_PURCHASECARD ) THEN
449 l_total_pcard_no := l_total_pcard_no + t_capts.total_trxn;
450 l_total_pcard_amt := l_total_pcard_amt + l_amount;
451 END IF;
452 */
453
454 -- Add up all captures based on status.
455 IF( l_status = C_STATUS_SUCCESS ) THEN
456 l_capt_succ_no := l_capt_succ_no + t_capts.total_trxn;
457 -- Total Authorizations Settled is same as successful captures.
458 l_total_authSet_no := l_total_authSet_no + t_capts.total_trxn;
459 l_total_authSet_amt := l_total_authSet_amt + l_amount;
460 ELSIF( l_status = C_STATUS_FAILED ) THEN
461 l_capt_fail_no := l_capt_fail_no + t_capts.total_trxn;
462 ELSIF( l_status = C_STATUS_PENDING ) THEN
463 l_capt_pend_no := l_capt_pend_no + t_capts.total_trxn;
464 END IF;
465
466 END IF; -- for check l_amount > 0
467
468 END LOOP; -- For Capture/Settlement Requests
469
470 -- close the cursors, if it is already open.
471 IF( cred_summary_csr%ISOPEN ) THEN
472 CLOSE cred_summary_csr;
473 END IF;
474
475 /* --- Processing Credits/Refunds Requests ---- */
476
477 FOR t_creds IN cred_summary_csr( l_updatedate, l_payeeId) LOOP
478
479 -- Bug 3714173: reporting currency is from the profile option
480 -- l_amount := Convert_Amount( t_creds.currency, C_TO_CURRENCY, t_creds.trxndate, t_creds.total_amt, NULL);
481 l_amount := Convert_Amount( t_creds.currency, l_to_currency, t_creds.trxndate, t_creds.total_amt, l_conv_type);
482 l_status := get_status_meaning( t_creds.status);
483
484 -- We ignore the cases when the RATE or CURRENCY is not found.
485 -- We only process if the status is supported.
486 IF ( (l_amount >= 0) AND (l_status <> C_STATUS_UNKNOWN) ) THEN
487
488 -- Add up all credits/refunds.
489 l_total_cred_no := l_total_cred_no + t_creds.total_trxn;
490 -- we want it to be a negative number.
491 l_total_cred_amt := l_total_cred_amt - l_amount;
492
493 -- Add all creditcard or purchasecard trxns
494 IF( t_creds.instrtype = C_INSTRTYPE_CREDITCARD ) THEN
495 l_total_ccard_no := l_total_ccard_no + t_creds.total_trxn;
496 l_total_ccard_amt := l_total_ccard_amt - l_amount;
497 ELSIF( t_creds.instrtype = C_INSTRTYPE_PURCHASECARD ) THEN
498 l_total_pcard_no := l_total_pcard_no + t_creds.total_trxn;
499 l_total_pcard_amt := l_total_pcard_amt - l_amount;
500 END IF;
501
502 -- Add up all credits/refunds based on status.
503 IF( l_status = C_STATUS_SUCCESS ) THEN
504 l_cred_succ_no := l_cred_succ_no + t_creds.total_trxn;
505 ELSIF( l_status = C_STATUS_FAILED ) THEN
506 l_cred_fail_no := l_cred_fail_no + t_creds.total_trxn;
507 ELSIF( l_status = C_STATUS_PENDING ) THEN
508 l_cred_pend_no := l_cred_pend_no + t_creds.total_trxn;
509 END IF;
510
511 END IF; -- for check l_amount > 0
512
513 END LOOP; -- For Credits/Refunds Requests
514
515 -- close the cursors, if it is already open.
516 IF( load_auth_outstand_csr%ISOPEN ) THEN
517 CLOSE load_auth_outstand_csr;
518 END IF;
519
520 /* --- Processing Authorizations Outstanding---- */
521
522 FOR t_outs IN load_auth_outstand_csr( l_updatedate,l_payeeId) LOOP
523
524 -- Bug 3714173: reporting currency is from the profile option
525 -- l_amount := Convert_Amount( t_outs.currency, C_TO_CURRENCY, t_outs.trxndate, t_outs.total_amt, NULL);
526 l_amount := Convert_Amount( t_outs.currency, l_to_currency, t_outs.trxndate, t_outs.total_amt, l_conv_type);
527
528 -- We ignore the cases when the RATE or CURRENCY is not found.
529 IF (l_amount >= 0) THEN
530 -- Add up all records.
531 l_total_authOut_no := l_total_authOut_no + t_outs.total_trxn;
532 l_total_authOut_amt := l_total_authOut_amt + l_amount;
533 END IF; -- for check l_amount > 0
534
535 END LOOP; -- For Outstanding Authorizations
536
537
538 -- Finally set the "All Transactions"
539 -- Bug 3306449: Amount and count from auth trans are commented out.
540 -- l_all_trxns_no := l_total_auth_no + l_total_capt_no + l_total_cred_no;
541 -- l_all_trxns_amt := l_total_auth_amt + l_total_capt_amt + l_total_cred_amt;
542 -- Bug 3458221: Switched from capt to auth.
543 -- l_all_trxns_no := l_total_capt_no + l_total_cred_no;
544 -- l_all_trxns_amt := l_total_capt_amt + l_total_cred_amt;
545 l_all_trxns_no := l_total_auth_no + l_total_cred_no;
546 l_all_trxns_amt := l_total_auth_amt + l_total_cred_amt;
547
548 -- Populate the summary table
549 summary_tbl(1).columnId := 1;
550 summary_tbl(1).totalTrxn := l_all_trxns_no;
551 summary_tbl(1).totalAmt := l_all_trxns_amt;
552
553 summary_tbl(2).columnId := 2;
554 summary_tbl(2).totalTrxn := l_total_auth_no;
555 summary_tbl(2).totalAmt := l_total_auth_amt;
556
557 summary_tbl(3).columnId := 3;
558 summary_tbl(3).totalTrxn := l_total_capt_no;
559 summary_tbl(3).totalAmt := l_total_capt_amt;
560
561 summary_tbl(4).columnId := 4;
562 summary_tbl(4).totalTrxn := l_total_cred_no;
563 summary_tbl(4).totalAmt := l_total_cred_amt;
564
565 summary_tbl(5).columnId := 5;
566 summary_tbl(5).totalTrxn := l_total_authSet_no;
567 summary_tbl(5).totalAmt := l_total_authSet_amt;
568
569 summary_tbl(6).columnId := 6;
570 summary_tbl(6).totalTrxn := l_total_authOut_no;
571 summary_tbl(6).totalAmt := l_total_authOut_amt;
572
573 summary_tbl(7).columnId := 7;
574 summary_tbl(7).totalTrxn := l_total_ccard_no;
575 summary_tbl(7).totalAmt := l_total_ccard_amt;
576
577 summary_tbl(8).columnId := 8;
578 summary_tbl(8).totalTrxn := l_total_pcard_no;
579 summary_tbl(8).totalAmt := l_total_pcard_amt;
580
581 -- Populate the Transation Summary table
582 trxnSum_tbl(1).columnId := 1;
583 trxnSum_tbl(1).totalReq := l_total_auth_no;
584 trxnSum_tbl(1).totalSuc := l_auth_succ_no;
585 trxnSum_tbl(1).totalFail := l_auth_fail_no;
586 trxnSum_tbl(1).totalPend := l_auth_pend_no;
587
588 trxnSum_tbl(2).columnId := 2;
589 trxnSum_tbl(2).totalReq := l_total_capt_no;
590 trxnSum_tbl(2).totalSuc := l_capt_succ_no;
591 trxnSum_tbl(2).totalFail := l_capt_fail_no;
592 trxnSum_tbl(2).totalPend := l_capt_pend_no;
593
594 trxnSum_tbl(3).columnId := 3;
595 trxnSum_tbl(3).totalReq := l_total_cred_no;
596 trxnSum_tbl(3).totalSuc := l_cred_succ_no;
597 trxnSum_tbl(3).totalFail := l_cred_fail_no;
598 trxnSum_tbl(3).totalPend := l_cred_pend_no;
599
600 END Get_Trxn_Summary;
601
602 --------------------------------------------------------------------------------------
603 -- 2. Get_Failure_Summary
604 -- Start of comments
605 -- API name : Get_Failure_Summary
606 -- Type : Private
607 -- Pre-reqs : None
608 -- Function : Fetches the information for Failures
609 -- Parameters :
610 -- IN : payee_id IN VARCHAR2
611 -- period IN VARCHAR2 Required
612 -- authFail_tbl OUT TrxnFail_tbl_type
613 -- settFail_tbl OUT TrxnFail_tbl_type
614 -- End of comments
615 --------------------------------------------------------------------------------------
616 Procedure Get_Failure_Summary ( payee_id IN VARCHAR2,
617 period IN VARCHAR2,
618 authFail_tbl OUT NOCOPY TrxnFail_tbl_type,
619 settFail_tbl OUT NOCOPY TrxnFail_tbl_type
620 ) IS
621
622 CURSOR get_authFail_csr( l_date DATE, l_payeeId VARCHAR2) IS
623 SELECT CurrencyNameCode currency,
624 Status,
625 COUNT(*) total_trxn,
626 SUM(amount) total_amt,
627 TRUNC(updatedate) trxndate
628 FROM iby_trxn_summaries_all
629 WHERE TRUNC(updatedate) >= l_date
630 AND trxntypeid IN (2,3)
631 AND instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
632 AND payeeid LIKE l_payeeId
633 AND status IN (-99,1,2,4,5,8,15,16,17,19,20,21,9999)
634 GROUP BY STATUS, CurrencyNameCode, TRUNC(updatedate)
635 ORDER BY status ASC;
636
637 CURSOR get_settFail_csr( l_date DATE, l_payeeId VARCHAR2) IS
638 SELECT CurrencyNameCode currency,
639 Status,
640 COUNT(*) total_trxn,
641 SUM(amount) total_amt,
642 TRUNC(updatedate) trxndate
643 FROM iby_trxn_summaries_all
644 WHERE TRUNC(updatedate) >= l_date
645 AND trxntypeid IN (3,8,9)
646 AND instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
647 AND payeeid LIKE l_payeeId
648 AND status IN (-99,1,2,4,5,8,15,16,17,19,20,21,9999)
649 GROUP BY STATUS, CurrencyNameCode, TRUNC(updatedate)
650 ORDER BY status ASC;
651
652 -- other local variables
653 l_updatedate DATE;
654 l_payeeId VARCHAR2(80);
655 l_amount NUMBER := 0;
656
657 l_curr_status NUMBER(15);
658 l_prev_status NUMBER(15);
659 l_tbl_count PLS_INTEGER;
660
661 l_failTemp TrxnFail_tbl_type;
662 l_failSett TrxnFail_tbl_type;
663
664 -- Bug 3714173: DBC reporting currency
665 l_to_currency VARCHAR2(10);
666
667 l_conv_type VARCHAR2(10);
668
669 BEGIN
670 -- Bug 3714173: Retrieve the reporting currency
671 l_to_currency := nvl(fnd_profile.value('IBY_DBC_REPORTING_CURRENCY'), 'USD');
672
673 l_conv_type := fnd_profile.value('IBY_DBC_REPORTING_CURR_CONV_RATE_TYPE');
674
675 -- Set the payee value accordingly.
676 IF( payee_id is NULL ) THEN
677 l_payeeId := '%';
678 ELSE
679 l_payeeId := TRIM(payee_id);
680 END IF;
681
682 -- Set the date.
683 l_updatedate := get_date(period);
684
685 -- close the cursors, if it is already open.
686 IF( get_authFail_csr%ISOPEN ) THEN
687 CLOSE get_authFail_csr;
688 END IF;
689
690 /* --- Processing Authorization Failures ---- */
691
692 -- Initialize the count
693 l_tbl_count := 1;
694 l_curr_status := 0;
695 l_prev_status := 0;
696
697 FOR t_auths IN get_authFail_csr( l_updatedate, l_payeeId) LOOP
698
699 -- Bug 3714173: reporting currency is from the profile option
700 -- l_amount := Convert_Amount( t_auths.currency, C_TO_CURRENCY, t_auths.trxndate, t_auths.total_amt, NULL);
701 l_amount := Convert_Amount( t_auths.currency, l_to_currency, t_auths.trxndate, t_auths.total_amt, l_conv_type);
702 l_curr_status := t_auths.status;
703
704 -- We ignore the cases when the RATE or CURRENCY is not found.
705 IF ( (l_amount >= 0) ) THEN
706
707 IF( (l_prev_status <> 0) AND (l_prev_status <> l_curr_status) ) THEN
708 l_tbl_count := l_tbl_count + 1;
709 END IF;
710
711 l_failTemp(l_tbl_count).status := l_curr_status;
712 l_failTemp(l_tbl_count).cause := get_status_cause(l_curr_status);
713 l_failTemp(l_tbl_count).totalTrxn := l_failTemp(l_tbl_count).totalTrxn + t_auths.total_trxn;
714 l_failTemp(l_tbl_count).totalAmt := l_failTemp(l_tbl_count).totalAmt + l_amount;
715
716 -- set the prev status to curr status for the next loop.
717 l_prev_status := l_curr_status;
718
719
720 END IF;
721
722 END LOOP; -- For get_authFail_csr
723
724
725 /*
726 l_tbl_count := 1;
727
728 dbms_output.put_line('The TOTAL count for the table is ' || l_failTemp.count );
729
730 WHILE( l_tbl_count <= l_failTemp.count ) LOOP
731 --dbms_output.put_line('The status for ' || l_tbl_count || ' is ' || l_failTemp(l_tbl_count).status );
732 dbms_output.put_line('The count for ' || l_tbl_count || ' is ' || l_failTemp(l_tbl_count).totalTrxn);
733 --dbms_output.put_line('The amount for ' || l_tbl_count || ' is ' || l_failTemp(l_tbl_count).totalAmt);
734 l_tbl_count := l_tbl_count + 1;
735 END LOOP;
736 */
737
738 -- Sort the table and then make the length 5.
739 bubble_sort(l_failTemp);
740 get_final_padded(l_failTemp, 5);
741
742 /*
743 dbms_output.put_line('After bubble sort !!!!');
744
745 l_tbl_count := 1;
746
747 dbms_output.put_line('The toatl count for the table is ' || l_failTemp.count );
748
749 WHILE( l_tbl_count <= l_failTemp.count ) LOOP
750 --dbms_output.put_line('The status for ' || l_tbl_count || ' is ' || l_failTemp(l_tbl_count).status );
751 dbms_output.put_line('The count for ' || l_tbl_count || ' is ' || l_failTemp(l_tbl_count).totalTrxn);
752 --dbms_output.put_line('The amount for ' || l_tbl_count || ' is ' || l_failTemp(l_tbl_count).totalAmt);
753 l_tbl_count := l_tbl_count + 1;
754 END LOOP;
755 */
756
757 authFail_tbl := l_failTemp;
758
759 /* --- Processing Settlement/Capture Failures ---- */
760
761 -- close the cursors, if it is already open.
762 IF( get_settFail_csr%ISOPEN ) THEN
763 CLOSE get_settFail_csr;
764 END IF;
765
766 -- Initialize the count
767 l_tbl_count := 1;
768 l_curr_status := 0;
769 l_prev_status := 0;
770
771 FOR t_auths IN get_settFail_csr( l_updatedate, l_payeeId) LOOP
772
773 -- Bug 3714173: reporting currency is from the profile option
774 -- l_amount := Convert_Amount( t_auths.currency, C_TO_CURRENCY, t_auths.trxndate, t_auths.total_amt, NULL);
775 l_amount := Convert_Amount( t_auths.currency, l_to_currency, t_auths.trxndate, t_auths.total_amt, l_conv_type);
776 l_curr_status := t_auths.status;
777
778 -- We ignore the cases when the RATE or CURRENCY is not found.
779 IF ( (l_amount >= 0) ) THEN
780
781 IF( (l_prev_status <> 0) AND (l_prev_status <> l_curr_status) ) THEN
782 l_tbl_count := l_tbl_count + 1;
783 END IF;
784
785 l_failSett(l_tbl_count).status := l_curr_status;
786 l_failSett(l_tbl_count).cause := get_status_cause(l_curr_status);
787 l_failSett(l_tbl_count).totalTrxn := l_failSett(l_tbl_count).totalTrxn + t_auths.total_trxn;
788 l_failSett(l_tbl_count).totalAmt := l_failSett(l_tbl_count).totalAmt + l_amount;
789
790 -- set the prev status to curr status for the next loop.
791 l_prev_status := l_curr_status;
792
793
794 END IF;
795
796 END LOOP; -- For get_authSett_csr
797
798
799 /*
800 l_tbl_count := 1;
801
802 dbms_output.put_line('The TOTAL count for the table is ' || l_failSett.count );
803
804 WHILE( l_tbl_count <= l_failSett.count ) LOOP
805 dbms_output.put_line('The cause for ' || l_tbl_count || ' is ' || l_failSett(l_tbl_count).cause );
806 dbms_output.put_line('The count for ' || l_tbl_count || ' is ' || l_failSett(l_tbl_count).totalTrxn);
807 dbms_output.put_line('The amount for ' || l_tbl_count || ' is ' || l_failSett(l_tbl_count).totalAmt);
808 l_tbl_count := l_tbl_count + 1;
809 END LOOP;
810 */
811
812 -- Sort the table and then make the length 5.
813 bubble_sort(l_failSett);
814 get_final_padded(l_failSett, 5);
815
816 /*
817 dbms_output.put_line('After bubble sort !!!!');
818
819 l_tbl_count := 1;
820
821 dbms_output.put_line('The toatl count for the table is ' || l_failSett.count );
822
823 WHILE( l_tbl_count <= l_failSett.count ) LOOP
824 dbms_output.put_line('The cause for ' || l_tbl_count || ' is ' || l_failSett(l_tbl_count).cause );
825 dbms_output.put_line('The count for ' || l_tbl_count || ' is ' || l_failSett(l_tbl_count).totalTrxn);
826 dbms_output.put_line('The amount for ' || l_tbl_count || ' is ' || l_failSett(l_tbl_count).totalAmt);
827 l_tbl_count := l_tbl_count + 1;
828 END LOOP;
829 */
830
831 settFail_tbl := l_failSett;
832
833
834 END Get_Failure_Summary;
835
836
837 --------------------------------------------------------------------------------------
838 -- 3. Get_CardType_Summary
839 -- Start of comments
840 -- API name : Get_CardType_Summary
841 -- Type : Private
842 -- Pre-reqs : None
843 -- Function : Fetches the information for Card Sub Types.
844 -- Parameters :
845 -- IN : payee_id IN VARCHAR2
846 -- period IN VARCHAR2 Required
847 -- cardType_tbl OUT TrxnFail_tbl_type
848 -- End of comments
849 --------------------------------------------------------------------------------------
850 Procedure Get_CardType_Summary ( payee_id IN VARCHAR2,
851 period IN VARCHAR2,
852 cardType_tbl OUT NOCOPY TrxnFail_tbl_type
853 ) IS
854
855 CURSOR get_CardType_csr( l_date DATE, l_payeeId VARCHAR2) IS
856 SELECT CurrencyNameCode currency,
857 instrsubtype,
858 -- DECODE(trxntypeid, 3, 2, 5,-1, 10, -1, 11, -1, 1) factor, -- Bug 3306449
859 -- DECODE(trxntypeid, 5, -1, 10, -1, 11, -1, 2, 0, 1) factor, -- Bug 3458221
860 DECODE(trxntypeid, 5, -1, 8, 0, 9, 0, 10, -1, 11, -1, 1) factor,
861 COUNT(*) total_trxn,
862 SUM(amount) total_amt,
863 TRUNC(updatedate) trxndate
864 FROM iby_trxn_summaries_all
865 WHERE TRUNC(updatedate) >= l_date
866 AND trxntypeid IN (2,3,5,8,9,10,11)
867 AND instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
868 AND payeeid LIKE l_payeeId
869 AND instrsubtype IS NOT NULL
870 AND status IN
871 (-99,0,1,2,4,5,8,15,16,17,19,20,21,100,109,111,9999)
872 GROUP BY instrsubtype,
873 -- DECODE(trxntypeid, 3, 2, 5,-1, 10, -1, 11, -1, 1), -- Bug 3306449
874 -- DECODE(trxntypeid, 5, -1, 10, -1, 11, -1, 2, 0, 1), -- Bug 3458221
875 DECODE(trxntypeid, 5, -1, 8, 0, 9, 0, 10, -1, 11, -1, 1),
876 CurrencyNameCode, TRUNC(updatedate)
877 ORDER BY UPPER(instrsubtype) ASC;
878
879 -- other local variables
880 l_updatedate DATE;
881 l_payeeId VARCHAR2(80);
882 l_amount NUMBER := 0;
883
884 l_curr_subtype VARCHAR2(30);
885 l_prev_subtype VARCHAR2(30);
886 l_tbl_count PLS_INTEGER;
887
888 -- Bug 3714173: DBC reporting currency
889 l_to_currency VARCHAR2(10);
890
891 l_conv_type VARCHAR2(10);
892
893 BEGIN
894 -- Bug 3714173: Retrieve the reporting currency
895 l_to_currency := nvl(fnd_profile.value('IBY_DBC_REPORTING_CURRENCY'), 'USD');
896
897 l_conv_type := fnd_profile.value('IBY_DBC_REPORTING_CURR_CONV_RATE_TYPE');
898
899 -- Set the payee value accordingly.
900 IF( payee_id is NULL ) THEN
901 l_payeeId := '%';
902 ELSE
903 l_payeeId := TRIM(payee_id);
904 END IF;
905
906 -- Set the date.
907 l_updatedate := get_date(period);
908
909 -- close the cursors, if it is already open.
910 IF( get_CardType_csr%ISOPEN ) THEN
911 CLOSE get_CardType_csr;
912 END IF;
913
914 /* --- Processing Card types ---- */
915
916 -- Initialize the count
917 l_tbl_count := 1;
918 l_curr_subtype := '*';
919 l_prev_subtype := '*';
920
921 FOR t_auths IN get_CardType_csr( l_updatedate, l_payeeId) LOOP
922
923 -- Bug 3714173: reporting currency is from the profile option
924 -- l_amount := Convert_Amount( t_auths.currency, C_TO_CURRENCY, t_auths.trxndate, t_auths.total_amt, NULL);
925 l_amount := Convert_Amount( t_auths.currency, l_to_currency, t_auths.trxndate, t_auths.total_amt, l_conv_type);
926 l_curr_subtype := t_auths.instrsubtype;
927
928 -- We ignore the cases when the RATE or CURRENCY is not found.
929 IF ( (l_amount >= 0) ) THEN
930
931 IF( (l_prev_subtype <> '*') AND (l_prev_subtype <> l_curr_subtype) ) THEN
932 l_tbl_count := l_tbl_count + 1;
933 END IF;
934
935 cardType_tbl(l_tbl_count).columnId := l_tbl_count;
936 cardType_tbl(l_tbl_count).cause := l_curr_subtype;
937 -- Bug 3306449: The following case will not be true.
938 /*
939 -- We should count a transaction twice if it is AuthCapture
940 IF( t_auths.factor = 2) THEN
941 cardType_tbl(l_tbl_count).totalTrxn := cardType_tbl(l_tbl_count).totalTrxn + (2 * t_auths.total_trxn);
942 ELSE
943 cardType_tbl(l_tbl_count).totalTrxn := cardType_tbl(l_tbl_count).totalTrxn + t_auths.total_trxn;
944 END IF;
945 */
946 cardType_tbl(l_tbl_count).totalTrxn := cardType_tbl(l_tbl_count).totalTrxn
947 + abs(t_auths.factor) * t_auths.total_trxn;
948 cardType_tbl(l_tbl_count).totalAmt := cardType_tbl(l_tbl_count).totalAmt + (t_auths.factor * l_amount);
949
950 -- set the prev status to curr status for the next loop.
951 l_prev_subtype := l_curr_subtype;
952
953
954 END IF;
955
956 END LOOP; -- For get_CardType_csr
957
958 END Get_CardType_Summary;
959
960 --------------------------------------------------------------------------------------
961 -- 4. Get_Processor_Summary
962 -- Start of comments
963 -- API name : Get_Processor_Summary
964 -- Type : Private
965 -- Pre-reqs : None
966 -- Function : Fetches the information for the Processors
967 -- Parameters :
968 -- IN : payee_id IN VARCHAR2
969 -- period IN VARCHAR2 Required
970 -- Processor_tbl OUT TrxnFail_tbl_type
971 -- End of comments
972 --------------------------------------------------------------------------------------
973 Procedure Get_Processor_Summary ( payee_id IN VARCHAR2,
974 period IN VARCHAR2,
975 Processor_tbl OUT NOCOPY TrxnFail_tbl_type
976 ) IS
977
978 CURSOR get_Processor_csr( l_date DATE, l_payeeId VARCHAR2) IS
979 SELECT b.name,
980 a.CurrencyNameCode currency,
981 -- DECODE(a.trxntypeid, 3, 2, 5,-1, 10, -1, 11, -1, 1) factor, -- Bug 3306449
982 -- DECODE(a.trxntypeid, 5, -1, 10, -1, 11, -1, 2, 0, 1) factor, -- Bug 3458221
983 DECODE(a.trxntypeid, 5, -1, 8, 0, 9, 0, 10, -1, 11, -1, 1) factor,
984 COUNT(*) total_trxn,
985 SUM(a.amount) total_amt,
986 TRUNC(a.updatedate) trxndate
987 FROM iby_trxn_summaries_all a,
988 iby_bepinfo b
989 WHERE TRUNC(updatedate) >= l_date
990 AND a.trxntypeid IN (2,3,5,8,9,10,11)
991 AND a.instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
992 AND a.payeeid LIKE l_payeeId
993 AND b.bepid = a.bepid
994 -- AND b.activestatus = 'Y'
995 AND a.status IN
996 (-99,0,1,2,4,5,8,15,16,17,19,20,21,100,109,111,9999)
997 GROUP BY b.name,
998 -- DECODE(a.trxntypeid, 3, 2, 5,-1, 10, -1, 11, -1, 1), -- Bug 3306449
999 -- DECODE(a.trxntypeid, 5, -1, 10, -1, 11, -1, 2, 0, 1), -- Bug 3458221
1000 DECODE(a.trxntypeid, 5, -1, 8, 0, 9, 0, 10, -1, 11, -1, 1),
1001 a.CurrencyNameCode, TRUNC(a.updatedate)
1002 ORDER BY b.name ASC;
1003
1004 -- other local variables
1005 l_updatedate DATE;
1006 l_payeeId VARCHAR2(80);
1007 l_amount NUMBER := 0;
1008
1009 l_curr_processor VARCHAR2(30);
1010 l_prev_processor VARCHAR2(30);
1011 l_tbl_count PLS_INTEGER;
1012
1013 -- Bug 3714173: DBC reporting currency
1014 l_to_currency VARCHAR2(10);
1015
1016 l_conv_type VARCHAR2(10);
1017
1018 BEGIN
1019 -- Bug 3714173: Retrieve the reporting currency
1020 l_to_currency := nvl(fnd_profile.value('IBY_DBC_REPORTING_CURRENCY'), 'USD');
1021
1022 l_conv_type := fnd_profile.value('IBY_DBC_REPORTING_CURR_CONV_RATE_TYPE');
1023
1024 -- Set the payee value accordingly.
1025 IF( payee_id is NULL ) THEN
1026 l_payeeId := '%';
1027 ELSE
1028 l_payeeId := TRIM(payee_id);
1029 END IF;
1030
1031 -- Set the date.
1032 l_updatedate := get_date(period);
1033
1034 -- close the cursors, if it is already open.
1035 IF( get_Processor_csr%ISOPEN ) THEN
1036 CLOSE get_Processor_csr;
1037 END IF;
1038
1039 /* --- Processing all Processors ---- */
1040
1041 -- Initialize the count
1042 l_tbl_count := 1;
1043 l_curr_processor := '*';
1044 l_prev_processor := '*';
1045
1046 FOR t_auths IN get_Processor_csr( l_updatedate, l_payeeId) LOOP
1047
1048 -- Bug 3714173: reporting currency is from the profile option
1049 -- l_amount := Convert_Amount( t_auths.currency, C_TO_CURRENCY, t_auths.trxndate, t_auths.total_amt, NULL);
1050 l_amount := Convert_Amount( t_auths.currency, l_to_currency, t_auths.trxndate, t_auths.total_amt, l_conv_type);
1051
1052 l_curr_processor := t_auths.name;
1053
1054 -- We ignore the cases when the RATE or CURRENCY is not found.
1055 IF ( (l_amount >= 0) ) THEN
1056
1057 IF( (l_prev_processor <> '*') AND (l_prev_processor <> l_curr_processor) ) THEN
1058 l_tbl_count := l_tbl_count + 1;
1059 END IF;
1060
1061 Processor_tbl(l_tbl_count).columnId := l_tbl_count;
1062 Processor_tbl(l_tbl_count).cause := l_curr_processor;
1063 -- Bug 3306449: Only capture trxn counts.
1064 /*
1065 -- We should count a transaction twice if it is AuthCapture
1066 IF( t_auths.factor = 2) THEN
1067 Processor_tbl(l_tbl_count).totalTrxn := Processor_tbl(l_tbl_count).totalTrxn + (2 * t_auths.total_trxn);
1068 ELSE
1069 Processor_tbl(l_tbl_count).totalTrxn := Processor_tbl(l_tbl_count).totalTrxn + t_auths.total_trxn;
1070 END IF;
1071 */
1072 Processor_tbl(l_tbl_count).totalTrxn := Processor_tbl(l_tbl_count).totalTrxn
1073 + abs(t_auths.factor) * t_auths.total_trxn;
1074 Processor_tbl(l_tbl_count).totalAmt := Processor_tbl(l_tbl_count).totalAmt + (t_auths.factor * l_amount);
1075
1076 -- set the prev status to curr status for the next loop.
1077 l_prev_processor := l_curr_processor;
1078
1079
1080 END IF;
1081
1082 END LOOP; -- For get_processor_csr
1083
1084 END Get_Processor_Summary;
1085
1086
1087 --------------------------------------------------------------------------------------
1088 -- 5. Get_Risk_Summary
1089 -- Start of comments
1090 -- API name : Get_Risk_Summary
1091 -- Type : Private
1092 -- Pre-reqs : None
1093 -- Function : Fetches the information for Risks
1094 -- Parameters :
1095 -- IN : payee_id IN VARCHAR2
1096 -- period IN VARCHAR2 Required
1097 -- total_screened OUT NUMBER
1098 -- total_risky OUT NUMBER
1099 -- End of comments
1100 --------------------------------------------------------------------------------------
1101 Procedure Get_Risk_Summary ( payee_id IN VARCHAR2,
1102 period IN VARCHAR2,
1103 total_screened OUT NOCOPY NUMBER,
1104 total_risky OUT NOCOPY NUMBER
1105 ) IS
1106
1107 CURSOR get_risk_csr( l_date DATE, l_payeeId VARCHAR2) IS
1108 SELECT DECODE(a.overall_score - b.threshold, 0,0,a.overall_score - b.threshold ) value,
1109 -- DECODE(a.trxntypeid, 3, 2, 1) factor, -- Bug 3306449
1110 -- DECODE(a.trxntypeid, 2, 0, 1) factor, -- Bug 3458221
1111 DECODE(a.trxntypeid, 8, 0, 9, 0, 1) factor,
1112 COUNT(*) total_trxn
1113 FROM iby_trxn_summaries_all a,
1114 iby_payee b
1115 WHERE TRUNC(updatedate) >= l_date
1116 AND trxntypeid IN (2,3,5,8,9,10,11)
1117 AND instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
1118 AND a.payeeid = b.payeeid
1119 AND b.payeeid LIKE l_payeeId
1120 -- AND b.activestatus = 'Y'
1121 AND b.threshold IS NOT NULL
1122 AND a.overall_score IS NOT NULL
1123 AND status IN (-99,0,1,2,4,5,8,15,16,17,19,20,21,100,109,111,9999)
1124 GROUP BY DECODE(a.overall_score - b.threshold,0,0,a.overall_score - b.threshold ),
1125 -- DECODE(a.trxntypeid, 3, 2, 1) -- Bug 3306449
1126 -- DECODE(a.trxntypeid, 2, 0, 1) -- Bug 3458221
1127 DECODE(a.trxntypeid, 8, 0, 9, 0, 1)
1128 ORDER BY value ASC;
1129
1130 -- other local variables
1131 l_updatedate DATE;
1132 l_payeeId VARCHAR2(80);
1133
1134 BEGIN
1135
1136 -- Set the payee value accordingly.
1137 IF( payee_id is NULL ) THEN
1138 l_payeeId := '%';
1139 ELSE
1140 l_payeeId := TRIM(payee_id);
1141 END IF;
1142
1143 -- Set the date.
1144 l_updatedate := get_date(period);
1145
1146 -- close the cursors, if it is already open.
1147 IF( get_risk_csr%ISOPEN ) THEN
1148 CLOSE get_risk_csr;
1149 END IF;
1150
1151 /* --- Processing all Processors ---- */
1152
1153 total_screened := 0;
1154 total_risky := 0;
1155
1156 FOR t_risk IN get_risk_csr( l_updatedate, l_payeeId) LOOP
1157 total_screened := total_screened + (t_risk.total_trxn * t_risk.factor);
1158 IF ( t_risk.value < 0 ) THEN
1159 total_risky := total_risky + (t_risk.total_trxn * t_risk.factor);
1160 END IF;
1161 END LOOP; -- For get_risk_csr
1162
1163 END Get_Risk_Summary;
1164
1165 END IBY_DBCCARD_PVT;