[Home] [Help]
PACKAGE BODY: APPS.IBY_DBCCARD_PVT
Source
1 PACKAGE BODY IBY_DBCCARD_PVT AS
2 /*$Header: ibyvdbcb.pls 120.4 2005/10/30 05:51:29 appldev noship $*/
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 BEGIN
357 -- Bug 3714173: Retrieve the reporting currency
358 l_to_currency := nvl(fnd_profile.value('IBY_DBC_REPORTING_CURRENCY'), 'USD');
359
360 -- Set the payee value accordingly.
361 IF( payee_id is NULL ) THEN
362 l_payeeId := '%';
363 ELSE
364 l_payeeId := TRIM(payee_id);
365 END IF;
366
367 -- Set the date.
368 l_updatedate := get_date(period);
369
370 -- close the cursors, if it is already open.
371 IF( auth_summary_csr%ISOPEN ) THEN
372 CLOSE auth_summary_csr;
373 END IF;
374
375 /* --- Processing Authorization Requests ---- */
376
377 FOR t_auths IN auth_summary_csr( l_updatedate, l_payeeId) LOOP
378
379 -- Bug 3714173: reporting currency is from the profile option
380 -- l_amount := Convert_Amount( t_auths.currency, C_TO_CURRENCY, t_auths.trxndate, t_auths.total_amt, NULL);
381 l_amount := Convert_Amount( t_auths.currency, l_to_currency, t_auths.trxndate, t_auths.total_amt, NULL);
382 l_status := get_status_meaning( t_auths.status);
383
384 -- We ignore the cases when the RATE or CURRENCY is not found.
385 -- We only process if the status is supported.
386 IF ( (l_amount >= 0) AND (l_status <> C_STATUS_UNKNOWN) ) THEN
387
388 -- Add up all auths.
389 l_total_auth_no := l_total_auth_no + t_auths.total_trxn;
390 l_total_auth_amt := l_total_auth_amt + l_amount;
391
392 -- Bug 3306449: Only capture trans count.
393 -- Bug 3458221: Re-install the auth trans count.
394 -- Add all creditcard or purchasecard trxns
395 IF( t_auths.instrtype = C_INSTRTYPE_CREDITCARD ) THEN
396 l_total_ccard_no := l_total_ccard_no + t_auths.total_trxn;
397 l_total_ccard_amt := l_total_ccard_amt + l_amount;
398 ELSIF( t_auths.instrtype = C_INSTRTYPE_PURCHASECARD ) THEN
399 l_total_pcard_no := l_total_pcard_no + t_auths.total_trxn;
400 l_total_pcard_amt := l_total_pcard_amt + l_amount;
401 END IF;
402
403 -- Add up all auths based on status.
404 IF( l_status = C_STATUS_SUCCESS ) THEN
405 l_auth_succ_no := l_auth_succ_no + t_auths.total_trxn;
406 ELSIF( l_status = C_STATUS_FAILED ) THEN
407 l_auth_fail_no := l_auth_fail_no + t_auths.total_trxn;
408 ELSIF( l_status = C_STATUS_PENDING ) THEN
409 l_auth_pend_no := l_auth_pend_no + t_auths.total_trxn;
410 END IF;
411
412 END IF; -- for check l_amount > 0
413
414 END LOOP; -- For Authorization Requests
415
416 -- close the cursors, if it is already open.
417 IF( capt_summary_csr%ISOPEN ) THEN
418 CLOSE capt_summary_csr;
419 END IF;
420
421 /* --- Processing Capture/Settlement Requests ---- */
422
423 FOR t_capts IN capt_summary_csr( l_updatedate, l_payeeId) LOOP
424
425 -- Bug 3714173: reporting currency is from the profile option
426 -- l_amount := Convert_Amount( t_capts.currency, C_TO_CURRENCY, t_capts.trxndate, t_capts.total_amt, NULL);
427 l_amount := Convert_Amount( t_capts.currency, l_to_currency, t_capts.trxndate, t_capts.total_amt, NULL);
428 l_status := get_status_meaning( t_capts.status);
429
430 -- We ignore the cases when the RATE or CURRENCY is not found.
431 -- We only process if the status is supported.
432 IF ( (l_amount >= 0) AND (l_status <> C_STATUS_UNKNOWN) ) THEN
433
434 -- Add up all captures/settlements.
435 l_total_capt_no := l_total_capt_no + t_capts.total_trxn;
436 l_total_capt_amt := l_total_capt_amt + l_amount;
437
438 -- Bug 3458221: Only auth trans count in calculating totals.
439 -- Add all creditcard or purchasecard trxns
440 /*
441 IF( t_capts.instrtype = C_INSTRTYPE_CREDITCARD ) THEN
442 l_total_ccard_no := l_total_ccard_no + t_capts.total_trxn;
443 l_total_ccard_amt := l_total_ccard_amt + l_amount;
444 ELSIF( t_capts.instrtype = C_INSTRTYPE_PURCHASECARD ) THEN
445 l_total_pcard_no := l_total_pcard_no + t_capts.total_trxn;
446 l_total_pcard_amt := l_total_pcard_amt + l_amount;
447 END IF;
448 */
449
450 -- Add up all captures based on status.
451 IF( l_status = C_STATUS_SUCCESS ) THEN
452 l_capt_succ_no := l_capt_succ_no + t_capts.total_trxn;
453 -- Total Authorizations Settled is same as successful captures.
454 l_total_authSet_no := l_total_authSet_no + t_capts.total_trxn;
455 l_total_authSet_amt := l_total_authSet_amt + l_amount;
456 ELSIF( l_status = C_STATUS_FAILED ) THEN
457 l_capt_fail_no := l_capt_fail_no + t_capts.total_trxn;
458 ELSIF( l_status = C_STATUS_PENDING ) THEN
459 l_capt_pend_no := l_capt_pend_no + t_capts.total_trxn;
460 END IF;
461
462 END IF; -- for check l_amount > 0
463
464 END LOOP; -- For Capture/Settlement Requests
465
466 -- close the cursors, if it is already open.
467 IF( cred_summary_csr%ISOPEN ) THEN
468 CLOSE cred_summary_csr;
469 END IF;
470
471 /* --- Processing Credits/Refunds Requests ---- */
472
473 FOR t_creds IN cred_summary_csr( l_updatedate, l_payeeId) LOOP
474
475 -- Bug 3714173: reporting currency is from the profile option
476 -- l_amount := Convert_Amount( t_creds.currency, C_TO_CURRENCY, t_creds.trxndate, t_creds.total_amt, NULL);
477 l_amount := Convert_Amount( t_creds.currency, l_to_currency, t_creds.trxndate, t_creds.total_amt, NULL);
478 l_status := get_status_meaning( t_creds.status);
479
480 -- We ignore the cases when the RATE or CURRENCY is not found.
481 -- We only process if the status is supported.
482 IF ( (l_amount >= 0) AND (l_status <> C_STATUS_UNKNOWN) ) THEN
483
484 -- Add up all credits/refunds.
485 l_total_cred_no := l_total_cred_no + t_creds.total_trxn;
486 -- we want it to be a negative number.
487 l_total_cred_amt := l_total_cred_amt - l_amount;
488
489 -- Add all creditcard or purchasecard trxns
490 IF( t_creds.instrtype = C_INSTRTYPE_CREDITCARD ) THEN
491 l_total_ccard_no := l_total_ccard_no + t_creds.total_trxn;
492 l_total_ccard_amt := l_total_ccard_amt - l_amount;
493 ELSIF( t_creds.instrtype = C_INSTRTYPE_PURCHASECARD ) THEN
494 l_total_pcard_no := l_total_pcard_no + t_creds.total_trxn;
495 l_total_pcard_amt := l_total_pcard_amt - l_amount;
496 END IF;
497
498 -- Add up all credits/refunds based on status.
499 IF( l_status = C_STATUS_SUCCESS ) THEN
500 l_cred_succ_no := l_cred_succ_no + t_creds.total_trxn;
501 ELSIF( l_status = C_STATUS_FAILED ) THEN
502 l_cred_fail_no := l_cred_fail_no + t_creds.total_trxn;
503 ELSIF( l_status = C_STATUS_PENDING ) THEN
504 l_cred_pend_no := l_cred_pend_no + t_creds.total_trxn;
505 END IF;
506
507 END IF; -- for check l_amount > 0
508
509 END LOOP; -- For Credits/Refunds Requests
510
511 -- close the cursors, if it is already open.
512 IF( load_auth_outstand_csr%ISOPEN ) THEN
513 CLOSE load_auth_outstand_csr;
514 END IF;
515
516 /* --- Processing Authorizations Outstanding---- */
517
518 FOR t_outs IN load_auth_outstand_csr( l_updatedate,l_payeeId) LOOP
519
520 -- Bug 3714173: reporting currency is from the profile option
521 -- l_amount := Convert_Amount( t_outs.currency, C_TO_CURRENCY, t_outs.trxndate, t_outs.total_amt, NULL);
522 l_amount := Convert_Amount( t_outs.currency, l_to_currency, t_outs.trxndate, t_outs.total_amt, NULL);
523
524 -- We ignore the cases when the RATE or CURRENCY is not found.
525 IF (l_amount >= 0) THEN
526 -- Add up all records.
527 l_total_authOut_no := l_total_authOut_no + t_outs.total_trxn;
528 l_total_authOut_amt := l_total_authOut_amt + l_amount;
529 END IF; -- for check l_amount > 0
530
531 END LOOP; -- For Outstanding Authorizations
532
533
534 -- Finally set the "All Transactions"
535 -- Bug 3306449: Amount and count from auth trans are commented out.
536 -- l_all_trxns_no := l_total_auth_no + l_total_capt_no + l_total_cred_no;
537 -- l_all_trxns_amt := l_total_auth_amt + l_total_capt_amt + l_total_cred_amt;
538 -- Bug 3458221: Switched from capt to auth.
539 -- l_all_trxns_no := l_total_capt_no + l_total_cred_no;
540 -- l_all_trxns_amt := l_total_capt_amt + l_total_cred_amt;
541 l_all_trxns_no := l_total_auth_no + l_total_cred_no;
542 l_all_trxns_amt := l_total_auth_amt + l_total_cred_amt;
543
544 -- Populate the summary table
545 summary_tbl(1).columnId := 1;
546 summary_tbl(1).totalTrxn := l_all_trxns_no;
547 summary_tbl(1).totalAmt := l_all_trxns_amt;
548
549 summary_tbl(2).columnId := 2;
550 summary_tbl(2).totalTrxn := l_total_auth_no;
551 summary_tbl(2).totalAmt := l_total_auth_amt;
552
553 summary_tbl(3).columnId := 3;
554 summary_tbl(3).totalTrxn := l_total_capt_no;
555 summary_tbl(3).totalAmt := l_total_capt_amt;
556
557 summary_tbl(4).columnId := 4;
558 summary_tbl(4).totalTrxn := l_total_cred_no;
559 summary_tbl(4).totalAmt := l_total_cred_amt;
560
561 summary_tbl(5).columnId := 5;
562 summary_tbl(5).totalTrxn := l_total_authSet_no;
563 summary_tbl(5).totalAmt := l_total_authSet_amt;
564
565 summary_tbl(6).columnId := 6;
566 summary_tbl(6).totalTrxn := l_total_authOut_no;
567 summary_tbl(6).totalAmt := l_total_authOut_amt;
568
569 summary_tbl(7).columnId := 7;
570 summary_tbl(7).totalTrxn := l_total_ccard_no;
571 summary_tbl(7).totalAmt := l_total_ccard_amt;
572
573 summary_tbl(8).columnId := 8;
574 summary_tbl(8).totalTrxn := l_total_pcard_no;
575 summary_tbl(8).totalAmt := l_total_pcard_amt;
576
577 -- Populate the Transation Summary table
578 trxnSum_tbl(1).columnId := 1;
579 trxnSum_tbl(1).totalReq := l_total_auth_no;
580 trxnSum_tbl(1).totalSuc := l_auth_succ_no;
581 trxnSum_tbl(1).totalFail := l_auth_fail_no;
582 trxnSum_tbl(1).totalPend := l_auth_pend_no;
583
584 trxnSum_tbl(2).columnId := 2;
585 trxnSum_tbl(2).totalReq := l_total_capt_no;
586 trxnSum_tbl(2).totalSuc := l_capt_succ_no;
587 trxnSum_tbl(2).totalFail := l_capt_fail_no;
588 trxnSum_tbl(2).totalPend := l_capt_pend_no;
589
590 trxnSum_tbl(3).columnId := 3;
591 trxnSum_tbl(3).totalReq := l_total_cred_no;
592 trxnSum_tbl(3).totalSuc := l_cred_succ_no;
593 trxnSum_tbl(3).totalFail := l_cred_fail_no;
594 trxnSum_tbl(3).totalPend := l_cred_pend_no;
595
596 END Get_Trxn_Summary;
597
598 --------------------------------------------------------------------------------------
599 -- 2. Get_Failure_Summary
600 -- Start of comments
601 -- API name : Get_Failure_Summary
602 -- Type : Private
603 -- Pre-reqs : None
604 -- Function : Fetches the information for Failures
605 -- Parameters :
606 -- IN : payee_id IN VARCHAR2
607 -- period IN VARCHAR2 Required
608 -- authFail_tbl OUT TrxnFail_tbl_type
609 -- settFail_tbl OUT TrxnFail_tbl_type
610 -- End of comments
611 --------------------------------------------------------------------------------------
612 Procedure Get_Failure_Summary ( payee_id IN VARCHAR2,
613 period IN VARCHAR2,
614 authFail_tbl OUT NOCOPY TrxnFail_tbl_type,
615 settFail_tbl OUT NOCOPY TrxnFail_tbl_type
616 ) IS
617
618 CURSOR get_authFail_csr( l_date DATE, l_payeeId VARCHAR2) IS
619 SELECT CurrencyNameCode currency,
620 Status,
621 COUNT(*) total_trxn,
622 SUM(amount) total_amt,
623 TRUNC(updatedate) trxndate
624 FROM iby_trxn_summaries_all
625 WHERE TRUNC(updatedate) >= l_date
626 AND trxntypeid IN (2,3)
627 AND instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
628 AND payeeid LIKE l_payeeId
629 AND status IN (-99,1,2,4,5,8,15,16,17,19,20,21,9999)
630 GROUP BY STATUS, CurrencyNameCode, TRUNC(updatedate)
631 ORDER BY status ASC;
632
633 CURSOR get_settFail_csr( l_date DATE, l_payeeId VARCHAR2) IS
634 SELECT CurrencyNameCode currency,
635 Status,
636 COUNT(*) total_trxn,
637 SUM(amount) total_amt,
638 TRUNC(updatedate) trxndate
639 FROM iby_trxn_summaries_all
640 WHERE TRUNC(updatedate) >= l_date
641 AND trxntypeid IN (3,8,9)
642 AND instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
643 AND payeeid LIKE l_payeeId
644 AND status IN (-99,1,2,4,5,8,15,16,17,19,20,21,9999)
645 GROUP BY STATUS, CurrencyNameCode, TRUNC(updatedate)
646 ORDER BY status ASC;
647
648 -- other local variables
649 l_updatedate DATE;
650 l_payeeId VARCHAR2(80);
651 l_amount NUMBER := 0;
652
653 l_curr_status NUMBER(15);
654 l_prev_status NUMBER(15);
655 l_tbl_count PLS_INTEGER;
656
657 l_failTemp TrxnFail_tbl_type;
658 l_failSett TrxnFail_tbl_type;
659
660 -- Bug 3714173: DBC reporting currency
661 l_to_currency VARCHAR2(10);
662
663 BEGIN
664 -- Bug 3714173: Retrieve the reporting currency
665 l_to_currency := nvl(fnd_profile.value('IBY_DBC_REPORTING_CURRENCY'), 'USD');
666
667 -- Set the payee value accordingly.
668 IF( payee_id is NULL ) THEN
669 l_payeeId := '%';
670 ELSE
671 l_payeeId := TRIM(payee_id);
672 END IF;
673
674 -- Set the date.
675 l_updatedate := get_date(period);
676
677 -- close the cursors, if it is already open.
678 IF( get_authFail_csr%ISOPEN ) THEN
679 CLOSE get_authFail_csr;
680 END IF;
681
682 /* --- Processing Authorization Failures ---- */
683
684 -- Initialize the count
685 l_tbl_count := 1;
686 l_curr_status := 0;
687 l_prev_status := 0;
688
689 FOR t_auths IN get_authFail_csr( l_updatedate, l_payeeId) LOOP
690
691 -- Bug 3714173: reporting currency is from the profile option
692 -- l_amount := Convert_Amount( t_auths.currency, C_TO_CURRENCY, t_auths.trxndate, t_auths.total_amt, NULL);
693 l_amount := Convert_Amount( t_auths.currency, l_to_currency, t_auths.trxndate, t_auths.total_amt, NULL);
694 l_curr_status := t_auths.status;
695
696 -- We ignore the cases when the RATE or CURRENCY is not found.
697 IF ( (l_amount >= 0) ) THEN
698
699 IF( (l_prev_status <> 0) AND (l_prev_status <> l_curr_status) ) THEN
700 l_tbl_count := l_tbl_count + 1;
701 END IF;
702
703 l_failTemp(l_tbl_count).status := l_curr_status;
704 l_failTemp(l_tbl_count).cause := get_status_cause(l_curr_status);
705 l_failTemp(l_tbl_count).totalTrxn := l_failTemp(l_tbl_count).totalTrxn + t_auths.total_trxn;
706 l_failTemp(l_tbl_count).totalAmt := l_failTemp(l_tbl_count).totalAmt + l_amount;
707
708 -- set the prev status to curr status for the next loop.
709 l_prev_status := l_curr_status;
710
711
712 END IF;
713
714 END LOOP; -- For get_authFail_csr
715
716
717 /*
718 l_tbl_count := 1;
719
720 dbms_output.put_line('The TOTAL count for the table is ' || l_failTemp.count );
721
722 WHILE( l_tbl_count <= l_failTemp.count ) LOOP
723 --dbms_output.put_line('The status for ' || l_tbl_count || ' is ' || l_failTemp(l_tbl_count).status );
724 dbms_output.put_line('The count for ' || l_tbl_count || ' is ' || l_failTemp(l_tbl_count).totalTrxn);
725 --dbms_output.put_line('The amount for ' || l_tbl_count || ' is ' || l_failTemp(l_tbl_count).totalAmt);
726 l_tbl_count := l_tbl_count + 1;
727 END LOOP;
728 */
729
730 -- Sort the table and then make the length 5.
731 bubble_sort(l_failTemp);
732 get_final_padded(l_failTemp, 5);
733
734 /*
735 dbms_output.put_line('After bubble sort !!!!');
736
737 l_tbl_count := 1;
738
739 dbms_output.put_line('The toatl count for the table is ' || l_failTemp.count );
740
741 WHILE( l_tbl_count <= l_failTemp.count ) LOOP
742 --dbms_output.put_line('The status for ' || l_tbl_count || ' is ' || l_failTemp(l_tbl_count).status );
743 dbms_output.put_line('The count for ' || l_tbl_count || ' is ' || l_failTemp(l_tbl_count).totalTrxn);
744 --dbms_output.put_line('The amount for ' || l_tbl_count || ' is ' || l_failTemp(l_tbl_count).totalAmt);
745 l_tbl_count := l_tbl_count + 1;
746 END LOOP;
747 */
748
749 authFail_tbl := l_failTemp;
750
751 /* --- Processing Settlement/Capture Failures ---- */
752
753 -- close the cursors, if it is already open.
754 IF( get_settFail_csr%ISOPEN ) THEN
755 CLOSE get_settFail_csr;
756 END IF;
757
758 -- Initialize the count
759 l_tbl_count := 1;
760 l_curr_status := 0;
761 l_prev_status := 0;
762
763 FOR t_auths IN get_settFail_csr( l_updatedate, l_payeeId) LOOP
764
765 -- Bug 3714173: reporting currency is from the profile option
766 -- l_amount := Convert_Amount( t_auths.currency, C_TO_CURRENCY, t_auths.trxndate, t_auths.total_amt, NULL);
767 l_amount := Convert_Amount( t_auths.currency, l_to_currency, t_auths.trxndate, t_auths.total_amt, NULL);
768 l_curr_status := t_auths.status;
769
770 -- We ignore the cases when the RATE or CURRENCY is not found.
771 IF ( (l_amount >= 0) ) THEN
772
773 IF( (l_prev_status <> 0) AND (l_prev_status <> l_curr_status) ) THEN
774 l_tbl_count := l_tbl_count + 1;
775 END IF;
776
777 l_failSett(l_tbl_count).status := l_curr_status;
778 l_failSett(l_tbl_count).cause := get_status_cause(l_curr_status);
779 l_failSett(l_tbl_count).totalTrxn := l_failSett(l_tbl_count).totalTrxn + t_auths.total_trxn;
780 l_failSett(l_tbl_count).totalAmt := l_failSett(l_tbl_count).totalAmt + l_amount;
781
782 -- set the prev status to curr status for the next loop.
783 l_prev_status := l_curr_status;
784
785
786 END IF;
787
788 END LOOP; -- For get_authSett_csr
789
790
791 /*
792 l_tbl_count := 1;
793
794 dbms_output.put_line('The TOTAL count for the table is ' || l_failSett.count );
795
796 WHILE( l_tbl_count <= l_failSett.count ) LOOP
797 dbms_output.put_line('The cause for ' || l_tbl_count || ' is ' || l_failSett(l_tbl_count).cause );
798 dbms_output.put_line('The count for ' || l_tbl_count || ' is ' || l_failSett(l_tbl_count).totalTrxn);
799 dbms_output.put_line('The amount for ' || l_tbl_count || ' is ' || l_failSett(l_tbl_count).totalAmt);
800 l_tbl_count := l_tbl_count + 1;
801 END LOOP;
802 */
803
804 -- Sort the table and then make the length 5.
805 bubble_sort(l_failSett);
806 get_final_padded(l_failSett, 5);
807
808 /*
809 dbms_output.put_line('After bubble sort !!!!');
810
811 l_tbl_count := 1;
812
813 dbms_output.put_line('The toatl count for the table is ' || l_failSett.count );
814
815 WHILE( l_tbl_count <= l_failSett.count ) LOOP
816 dbms_output.put_line('The cause for ' || l_tbl_count || ' is ' || l_failSett(l_tbl_count).cause );
817 dbms_output.put_line('The count for ' || l_tbl_count || ' is ' || l_failSett(l_tbl_count).totalTrxn);
818 dbms_output.put_line('The amount for ' || l_tbl_count || ' is ' || l_failSett(l_tbl_count).totalAmt);
819 l_tbl_count := l_tbl_count + 1;
820 END LOOP;
821 */
822
823 settFail_tbl := l_failSett;
824
825
826 END Get_Failure_Summary;
827
828
829 --------------------------------------------------------------------------------------
830 -- 3. Get_CardType_Summary
831 -- Start of comments
832 -- API name : Get_CardType_Summary
833 -- Type : Private
834 -- Pre-reqs : None
835 -- Function : Fetches the information for Card Sub Types.
836 -- Parameters :
837 -- IN : payee_id IN VARCHAR2
838 -- period IN VARCHAR2 Required
839 -- cardType_tbl OUT TrxnFail_tbl_type
840 -- End of comments
841 --------------------------------------------------------------------------------------
842 Procedure Get_CardType_Summary ( payee_id IN VARCHAR2,
843 period IN VARCHAR2,
844 cardType_tbl OUT NOCOPY TrxnFail_tbl_type
845 ) IS
846
847 CURSOR get_CardType_csr( l_date DATE, l_payeeId VARCHAR2) IS
848 SELECT CurrencyNameCode currency,
849 instrsubtype,
850 -- DECODE(trxntypeid, 3, 2, 5,-1, 10, -1, 11, -1, 1) factor, -- Bug 3306449
851 -- DECODE(trxntypeid, 5, -1, 10, -1, 11, -1, 2, 0, 1) factor, -- Bug 3458221
852 DECODE(trxntypeid, 5, -1, 8, 0, 9, 0, 10, -1, 11, -1, 1) factor,
853 COUNT(*) total_trxn,
854 SUM(amount) total_amt,
855 TRUNC(updatedate) trxndate
856 FROM iby_trxn_summaries_all
857 WHERE TRUNC(updatedate) >= l_date
858 AND trxntypeid IN (2,3,5,8,9,10,11)
859 AND instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
860 AND payeeid LIKE l_payeeId
861 AND instrsubtype IS NOT NULL
862 AND status IN
863 (-99,0,1,2,4,5,8,15,16,17,19,20,21,100,109,111,9999)
864 GROUP BY instrsubtype,
865 -- DECODE(trxntypeid, 3, 2, 5,-1, 10, -1, 11, -1, 1), -- Bug 3306449
866 -- DECODE(trxntypeid, 5, -1, 10, -1, 11, -1, 2, 0, 1), -- Bug 3458221
867 DECODE(trxntypeid, 5, -1, 8, 0, 9, 0, 10, -1, 11, -1, 1),
868 CurrencyNameCode, TRUNC(updatedate)
869 ORDER BY UPPER(instrsubtype) ASC;
870
871 -- other local variables
872 l_updatedate DATE;
873 l_payeeId VARCHAR2(80);
874 l_amount NUMBER := 0;
875
876 l_curr_subtype VARCHAR2(30);
877 l_prev_subtype VARCHAR2(30);
878 l_tbl_count PLS_INTEGER;
879
880 -- Bug 3714173: DBC reporting currency
881 l_to_currency VARCHAR2(10);
882
883 BEGIN
884 -- Bug 3714173: Retrieve the reporting currency
885 l_to_currency := nvl(fnd_profile.value('IBY_DBC_REPORTING_CURRENCY'), 'USD');
886
887 -- Set the payee value accordingly.
888 IF( payee_id is NULL ) THEN
889 l_payeeId := '%';
890 ELSE
891 l_payeeId := TRIM(payee_id);
892 END IF;
893
894 -- Set the date.
895 l_updatedate := get_date(period);
896
897 -- close the cursors, if it is already open.
898 IF( get_CardType_csr%ISOPEN ) THEN
899 CLOSE get_CardType_csr;
900 END IF;
901
902 /* --- Processing Card types ---- */
903
904 -- Initialize the count
905 l_tbl_count := 1;
906 l_curr_subtype := '*';
907 l_prev_subtype := '*';
908
909 FOR t_auths IN get_CardType_csr( l_updatedate, l_payeeId) LOOP
910
911 -- Bug 3714173: reporting currency is from the profile option
912 -- l_amount := Convert_Amount( t_auths.currency, C_TO_CURRENCY, t_auths.trxndate, t_auths.total_amt, NULL);
913 l_amount := Convert_Amount( t_auths.currency, l_to_currency, t_auths.trxndate, t_auths.total_amt, NULL);
914 l_curr_subtype := t_auths.instrsubtype;
915
916 -- We ignore the cases when the RATE or CURRENCY is not found.
917 IF ( (l_amount >= 0) ) THEN
918
919 IF( (l_prev_subtype <> '*') AND (l_prev_subtype <> l_curr_subtype) ) THEN
920 l_tbl_count := l_tbl_count + 1;
921 END IF;
922
923 cardType_tbl(l_tbl_count).columnId := l_tbl_count;
924 cardType_tbl(l_tbl_count).cause := l_curr_subtype;
925 -- Bug 3306449: The following case will not be true.
926 /*
927 -- We should count a transaction twice if it is AuthCapture
928 IF( t_auths.factor = 2) THEN
929 cardType_tbl(l_tbl_count).totalTrxn := cardType_tbl(l_tbl_count).totalTrxn + (2 * t_auths.total_trxn);
930 ELSE
931 cardType_tbl(l_tbl_count).totalTrxn := cardType_tbl(l_tbl_count).totalTrxn + t_auths.total_trxn;
932 END IF;
933 */
934 cardType_tbl(l_tbl_count).totalTrxn := cardType_tbl(l_tbl_count).totalTrxn
935 + abs(t_auths.factor) * t_auths.total_trxn;
936 cardType_tbl(l_tbl_count).totalAmt := cardType_tbl(l_tbl_count).totalAmt + (t_auths.factor * l_amount);
937
938 -- set the prev status to curr status for the next loop.
939 l_prev_subtype := l_curr_subtype;
940
941
942 END IF;
943
944 END LOOP; -- For get_CardType_csr
945
946 END Get_CardType_Summary;
947
948 --------------------------------------------------------------------------------------
949 -- 4. Get_Processor_Summary
950 -- Start of comments
951 -- API name : Get_Processor_Summary
952 -- Type : Private
953 -- Pre-reqs : None
954 -- Function : Fetches the information for the Processors
955 -- Parameters :
956 -- IN : payee_id IN VARCHAR2
957 -- period IN VARCHAR2 Required
958 -- Processor_tbl OUT TrxnFail_tbl_type
959 -- End of comments
960 --------------------------------------------------------------------------------------
961 Procedure Get_Processor_Summary ( payee_id IN VARCHAR2,
962 period IN VARCHAR2,
963 Processor_tbl OUT NOCOPY TrxnFail_tbl_type
964 ) IS
965
966 CURSOR get_Processor_csr( l_date DATE, l_payeeId VARCHAR2) IS
967 SELECT b.name,
968 a.CurrencyNameCode currency,
969 -- DECODE(a.trxntypeid, 3, 2, 5,-1, 10, -1, 11, -1, 1) factor, -- Bug 3306449
970 -- DECODE(a.trxntypeid, 5, -1, 10, -1, 11, -1, 2, 0, 1) factor, -- Bug 3458221
971 DECODE(a.trxntypeid, 5, -1, 8, 0, 9, 0, 10, -1, 11, -1, 1) factor,
972 COUNT(*) total_trxn,
973 SUM(a.amount) total_amt,
974 TRUNC(a.updatedate) trxndate
975 FROM iby_trxn_summaries_all a,
976 iby_bepinfo b
977 WHERE TRUNC(updatedate) >= l_date
978 AND a.trxntypeid IN (2,3,5,8,9,10,11)
979 AND a.instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
980 AND a.payeeid LIKE l_payeeId
981 AND b.bepid = a.bepid
982 -- AND b.activestatus = 'Y'
983 AND a.status IN
984 (-99,0,1,2,4,5,8,15,16,17,19,20,21,100,109,111,9999)
985 GROUP BY b.name,
986 -- DECODE(a.trxntypeid, 3, 2, 5,-1, 10, -1, 11, -1, 1), -- Bug 3306449
987 -- DECODE(a.trxntypeid, 5, -1, 10, -1, 11, -1, 2, 0, 1), -- Bug 3458221
988 DECODE(a.trxntypeid, 5, -1, 8, 0, 9, 0, 10, -1, 11, -1, 1),
989 a.CurrencyNameCode, TRUNC(a.updatedate)
990 ORDER BY b.name ASC;
991
992 -- other local variables
993 l_updatedate DATE;
994 l_payeeId VARCHAR2(80);
995 l_amount NUMBER := 0;
996
997 l_curr_processor VARCHAR2(30);
998 l_prev_processor VARCHAR2(30);
999 l_tbl_count PLS_INTEGER;
1000
1001 -- Bug 3714173: DBC reporting currency
1002 l_to_currency VARCHAR2(10);
1003
1004 BEGIN
1005 -- Bug 3714173: Retrieve the reporting currency
1006 l_to_currency := nvl(fnd_profile.value('IBY_DBC_REPORTING_CURRENCY'), 'USD');
1007
1008 -- Set the payee value accordingly.
1009 IF( payee_id is NULL ) THEN
1010 l_payeeId := '%';
1011 ELSE
1012 l_payeeId := TRIM(payee_id);
1013 END IF;
1014
1015 -- Set the date.
1016 l_updatedate := get_date(period);
1017
1018 -- close the cursors, if it is already open.
1019 IF( get_Processor_csr%ISOPEN ) THEN
1020 CLOSE get_Processor_csr;
1021 END IF;
1022
1023 /* --- Processing all Processors ---- */
1024
1025 -- Initialize the count
1026 l_tbl_count := 1;
1027 l_curr_processor := '*';
1028 l_prev_processor := '*';
1029
1030 FOR t_auths IN get_Processor_csr( l_updatedate, l_payeeId) LOOP
1031
1032 -- Bug 3714173: reporting currency is from the profile option
1033 -- l_amount := Convert_Amount( t_auths.currency, C_TO_CURRENCY, t_auths.trxndate, t_auths.total_amt, NULL);
1034 l_amount := Convert_Amount( t_auths.currency, l_to_currency, t_auths.trxndate, t_auths.total_amt, NULL);
1035
1036 l_curr_processor := t_auths.name;
1037
1038 -- We ignore the cases when the RATE or CURRENCY is not found.
1039 IF ( (l_amount >= 0) ) THEN
1040
1041 IF( (l_prev_processor <> '*') AND (l_prev_processor <> l_curr_processor) ) THEN
1042 l_tbl_count := l_tbl_count + 1;
1043 END IF;
1044
1045 Processor_tbl(l_tbl_count).columnId := l_tbl_count;
1046 Processor_tbl(l_tbl_count).cause := l_curr_processor;
1047 -- Bug 3306449: Only capture trxn counts.
1048 /*
1049 -- We should count a transaction twice if it is AuthCapture
1050 IF( t_auths.factor = 2) THEN
1051 Processor_tbl(l_tbl_count).totalTrxn := Processor_tbl(l_tbl_count).totalTrxn + (2 * t_auths.total_trxn);
1052 ELSE
1053 Processor_tbl(l_tbl_count).totalTrxn := Processor_tbl(l_tbl_count).totalTrxn + t_auths.total_trxn;
1054 END IF;
1055 */
1056 Processor_tbl(l_tbl_count).totalTrxn := Processor_tbl(l_tbl_count).totalTrxn
1057 + abs(t_auths.factor) * t_auths.total_trxn;
1058 Processor_tbl(l_tbl_count).totalAmt := Processor_tbl(l_tbl_count).totalAmt + (t_auths.factor * l_amount);
1059
1060 -- set the prev status to curr status for the next loop.
1061 l_prev_processor := l_curr_processor;
1062
1063
1064 END IF;
1065
1066 END LOOP; -- For get_processor_csr
1067
1068 END Get_Processor_Summary;
1069
1070
1071 --------------------------------------------------------------------------------------
1072 -- 5. Get_Risk_Summary
1073 -- Start of comments
1074 -- API name : Get_Risk_Summary
1075 -- Type : Private
1076 -- Pre-reqs : None
1077 -- Function : Fetches the information for Risks
1078 -- Parameters :
1079 -- IN : payee_id IN VARCHAR2
1080 -- period IN VARCHAR2 Required
1081 -- total_screened OUT NUMBER
1082 -- total_risky OUT NUMBER
1083 -- End of comments
1084 --------------------------------------------------------------------------------------
1085 Procedure Get_Risk_Summary ( payee_id IN VARCHAR2,
1086 period IN VARCHAR2,
1087 total_screened OUT NOCOPY NUMBER,
1088 total_risky OUT NOCOPY NUMBER
1089 ) IS
1090
1091 CURSOR get_risk_csr( l_date DATE, l_payeeId VARCHAR2) IS
1092 SELECT DECODE(a.overall_score - b.threshold, 0,0,a.overall_score - b.threshold ) value,
1093 -- DECODE(a.trxntypeid, 3, 2, 1) factor, -- Bug 3306449
1094 -- DECODE(a.trxntypeid, 2, 0, 1) factor, -- Bug 3458221
1095 DECODE(a.trxntypeid, 8, 0, 9, 0, 1) factor,
1096 COUNT(*) total_trxn
1097 FROM iby_trxn_summaries_all a,
1098 iby_payee b
1099 WHERE TRUNC(updatedate) >= l_date
1100 AND trxntypeid IN (2,3,5,8,9,10,11)
1101 AND instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
1102 AND a.payeeid = b.payeeid
1103 AND b.payeeid LIKE l_payeeId
1104 -- AND b.activestatus = 'Y'
1105 AND b.threshold IS NOT NULL
1106 AND a.overall_score IS NOT NULL
1107 AND status IN (-99,0,1,2,4,5,8,15,16,17,19,20,21,100,109,111,9999)
1108 GROUP BY DECODE(a.overall_score - b.threshold,0,0,a.overall_score - b.threshold ),
1109 -- DECODE(a.trxntypeid, 3, 2, 1) -- Bug 3306449
1110 -- DECODE(a.trxntypeid, 2, 0, 1) -- Bug 3458221
1111 DECODE(a.trxntypeid, 8, 0, 9, 0, 1)
1112 ORDER BY value ASC;
1113
1114 -- other local variables
1115 l_updatedate DATE;
1116 l_payeeId VARCHAR2(80);
1117
1118 BEGIN
1119
1120 -- Set the payee value accordingly.
1121 IF( payee_id is NULL ) THEN
1122 l_payeeId := '%';
1123 ELSE
1124 l_payeeId := TRIM(payee_id);
1125 END IF;
1126
1127 -- Set the date.
1128 l_updatedate := get_date(period);
1129
1130 -- close the cursors, if it is already open.
1131 IF( get_risk_csr%ISOPEN ) THEN
1132 CLOSE get_risk_csr;
1133 END IF;
1134
1135 /* --- Processing all Processors ---- */
1136
1137 total_screened := 0;
1138 total_risky := 0;
1139
1140 FOR t_risk IN get_risk_csr( l_updatedate, l_payeeId) LOOP
1141 total_screened := total_screened + (t_risk.total_trxn * t_risk.factor);
1142 IF ( t_risk.value < 0 ) THEN
1143 total_risky := total_risky + (t_risk.total_trxn * t_risk.factor);
1144 END IF;
1145 END LOOP; -- For get_risk_csr
1146
1147 END Get_Risk_Summary;
1148
1149 END IBY_DBCCARD_PVT;