[Home] [Help]
PACKAGE BODY: APPS.IBY_DBCCARD_GRAPH_PVT
Source
1 PACKAGE BODY IBY_DBCCARD_GRAPH_PVT AS
2 /*$Header: ibyvgphb.pls 120.3 2005/10/30 05:49:04 appldev noship $*/
3
4 --------------------------------------------------------------------------------------
5 -- Global Variable Declaration --
6 --------------------------------------------------------------------------------------
7
8 C_INSTRTYPE_CREDITCARD CONSTANT VARCHAR2(20) := IBY_DBCCARD_PVT.C_INSTRTYPE_CREDITCARD;
9 C_INSTRTYPE_PURCHASECARD CONSTANT VARCHAR2(20) := IBY_DBCCARD_PVT.C_INSTRTYPE_PURCHASECARD;
10 -- Bug 3714173: DBC reporting currency is from the profile option
11 -- C_TO_CURRENCY CONSTANT VARCHAR2(5) := IBY_DBCCARD_PVT.C_TO_CURRENCY;
12
13 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_DBCCARD_GRAPH_PVT';
14 g_validation_level CONSTANT NUMBER := FND_API.G_VALID_LEVEL_FULL;
15
16
17 --------------------------------------------------------------------------------------
18 -- API Signatures--
19 --------------------------------------------------------------------------------------
20 /*
21 The following function gets the time stirng for the hour
22 */
23
24 FUNCTION get_time_str ( hour NUMBER
25 ) RETURN VARCHAR2 IS
26
27 BEGIN
28
29 IF(hour = 0) THEN
30 RETURN '12am';
31 ELSIF(hour = 1) THEN
32 RETURN '1am';
33 ELSIF(hour = 2) THEN
34 RETURN '2am';
35 ELSIF(hour = 3) THEN
36 RETURN '3am';
37 ELSIF(hour = 4) THEN
38 RETURN '4am';
39 ELSIF(hour = 5) THEN
40 RETURN '5am';
41 ELSIF(hour = 6) THEN
42 RETURN '6am';
43 ELSIF(hour = 7) THEN
44 RETURN '7am';
45 ELSIF(hour = 8) THEN
46 RETURN '8am';
47 ELSIF(hour = 9) THEN
48 RETURN '9am';
49 ELSIF(hour = 10) THEN
50 RETURN '10am';
51 ELSIF(hour = 11) THEN
52 RETURN '11am';
53 ELSIF(hour = 12) THEN
54 RETURN '12pm';
55 ELSIF(hour = 13) THEN
56 RETURN '1pm';
57 ELSIF(hour = 14) THEN
58 RETURN '2pm';
59 ELSIF(hour = 15) THEN
60 RETURN '3pm';
61 ELSIF(hour = 16) THEN
62 RETURN '4pm';
63 ELSIF(hour = 17) THEN
64 RETURN '5pm';
65 ELSIF(hour = 18) THEN
66 RETURN '6pm';
67 ELSIF(hour = 19) THEN
68 RETURN '7pm';
69 ELSIF(hour = 20) THEN
70 RETURN '8pm';
71 ELSIF(hour = 21) THEN
72 RETURN '9pm';
73 ELSIF(hour = 22) THEN
74 RETURN '10pm';
75 ELSE
76 RETURN '11pm';
77 END IF;
78
79 END get_time_str;
80
81
82 /*
83 The following procedure pads the table.
84 */
85
86 Procedure pad_table ( tbl IN OUT NOCOPY HourlyVol_tbl_type,
87 from_count IN NUMBER,
88 to_count IN NUMBER
89 ) IS
90
91 BEGIN
92
93 FOR i IN from_count..to_count LOOP
94 tbl(i+1).columnId := i+1;
95 tbl(i+1).time := get_time_str(i);
96 tbl(i+1).totalTrxn := 0;
97 END LOOP;
98
99 END pad_table;
100
101 /*
102 The following procedure returns a complete table if the input table has
103 any missing records.
104 */
105
106 Procedure complete_table ( tbl IN OUT NOCOPY Trends_tbl_type,
107 from_date IN DATE,
108 type IN VARCHAR2,
109 period IN VARCHAR2
110 ) IS
111
112 l_final_tbl Trends_tbl_type := Trends_tbl_type();
113 l_curr_date DATE;
114 l_input_count PLS_INTEGER;
115 l_upper_cnt PLS_INTEGER;
116 l_date_format VARCHAR2(5);
117 l_date_str VARCHAR2(5);
118 l_date_factor PLS_INTEGER;
119
120 BEGIN
121
122 IF( period = C_PERIOD_YEARLY) THEN
123 l_upper_cnt := 3;
124 l_date_format := 'yyyy';
125 l_date_str := 'yyyy';
126 l_date_factor := 12;
127 ELSE
128 l_upper_cnt := 12;
129 l_date_format := 'mm';
130 l_date_str := 'MON';
131 l_date_factor := 1;
132 END IF;
133
134 l_input_count := 1;
135
136 FOR i IN 1..l_upper_cnt LOOP
137 WHILE( NOT l_final_tbl.EXISTS(i)) LOOP
138 l_final_tbl.EXTEND;
139 l_final_tbl(l_final_tbl.COUNT).value := 0;
140 END LOOP;
141 l_curr_date := TRUNC(ADD_MONTHS(from_date,(i * l_date_factor)), l_date_format);
142 IF( (tbl.EXISTS(l_input_count)) AND (tbl(l_input_count).tdate = l_curr_date) ) THEN
143 --l_final_tbl(i) := tbl(l_input_count);
144 l_final_tbl(i).month := tbl(l_input_count).month;
145 l_final_tbl(i).type := tbl(l_input_count).type;
146 l_final_tbl(i).tdate := tbl(l_input_count).tdate;
147 l_final_tbl(i).value := tbl(l_input_count).value;
148 l_input_count := l_input_count + 1;
149 ELSE
150 l_final_tbl(i).month := TO_CHAR(l_curr_date, l_date_str);
151 l_final_tbl(i).type := type;
152 l_final_tbl(i).tdate := l_curr_date;
153 l_final_tbl(i).value := 0;
154 END IF;
155
156 END LOOP;
157
158 tbl := l_final_tbl;
159
160 END complete_table;
161
162 /*
163 The procedure adds the a table records to another.
164 */
165 Procedure add_to_output( to_tbl IN OUT NOCOPY TrxnTrends_tbl_type,
166 from_tbl IN Trends_tbl_type
167 ) IS
168 BEGIN
169
170 FOR i IN 1..from_tbl.COUNT LOOP
171 to_tbl( to_tbl.count + 1) := from_tbl(i);
172 END LOOP;
173
174 End add_to_output;
175
176 /*
177 The procedure adds the table record values to another
178 table records. It assumes that the order of records is same
179 in both the tables.
180 */
181 Procedure add_to_total (total_tbl IN OUT NOCOPY Trends_tbl_type,
182 curr_tbl IN Trends_tbl_type
183 ) IS
184 BEGIN
185
186 FOR i IN 1..12 LOOP
187 WHILE( NOT total_tbl.EXISTS(i)) LOOP
188 total_tbl.EXTEND;
189 total_tbl(total_tbl.COUNT).value := 0;
190 END LOOP;
191 total_tbl(i).month := curr_tbl(i).month;
192 total_tbl(i).value := total_tbl(i).value + curr_tbl(i).value;
193 total_tbl(i).type := 'TOTAL';
194 total_tbl(i).tdate := curr_tbl(i).tdate;
195 END LOOP;
196
197 End add_to_total;
198
199 --------------------------------------------------------------------------------------
200 -- 1. Get_Hourly_Volume
201 -- Start of comments
202 -- API name : Get_Hourly_Volume
203 -- Type : Private
204 -- Pre-reqs : None
205 -- Function : Fetches the information for hourly transaction.
206 -- Parameters :
207 -- IN : payee_id IN VARCHAR2
208 -- HourlyVol_tbl OUT HourlyVol_tbl_type
209 -- End of comments
210 --------------------------------------------------------------------------------------
211 Procedure Get_Hourly_Volume ( payee_id IN VARCHAR2,
212 HourlyVol_tbl OUT NOCOPY HourlyVol_tbl_type
213 ) IS
214
215 CURSOR hourly_volume_csr(l_payeeid VARCHAR2) IS
216 SELECT COUNT(*) totalTrxn,
217 -- DECODE(trxntypeid, 3, 2, 1) factor, -- Bug 3458221
218 DECODE(trxntypeid, 8, 0, 9, 0, 1) factor,
219 TO_NUMBER(TO_CHAR(updatedate, 'hh24')) hour
220 FROM iby_trxn_summaries_all
221 WHERE TRUNC(updatedate) = TRUNC(SYSDATE)
222 AND instrtype IN ('CREDITCARD', 'PURCHASECARD')
223 AND trxntypeid IN (2,3,5,8,9,10,11)
224 AND payeeid LIKE l_payeeId
225 AND status IN
226 (-99,0,1,2,4,5,8,15,16,17,19,20,21,100,109,111,9999)
227 GROUP BY TO_CHAR(updatedate, 'hh24'),
228 -- DECODE(trxntypeid, 3, 2, 1) -- Bug 3458221
229 DECODE(trxntypeid, 8, 0, 9, 0, 1)
230 ORDER BY hour ASC;
231
232 l_payeeid VARCHAR2(80);
233 l_prev_hour NUMBER;
234 l_curr_hour NUMBER;
235
236 BEGIN
237
238 -- Set the payee value accordingly.
239 IF( payee_id is NULL ) THEN
240 l_payeeId := '%';
241 ELSE
242 l_payeeId := TRIM(payee_id);
243 END IF;
244
245 -- close the cursors, if it is already open.
246 IF( hourly_volume_csr%ISOPEN ) THEN
247 CLOSE hourly_volume_csr;
248 END IF;
249
250 /* --- Processing begins ---- */
251
252 l_prev_hour := -1;
253 l_curr_hour := 0;
254
255 FOR t_vol IN hourly_volume_csr(l_payeeId) LOOP
256
257 l_curr_hour := t_vol.hour;
258
259 IF( (l_curr_hour - l_prev_hour) > 1 ) THEN
260 pad_table( HourlyVol_tbl, l_prev_hour+1, l_curr_hour -1);
261 END IF;
262
263 HourlyVol_tbl(l_curr_hour+1).columnId := l_curr_hour+1;
264 HourlyVol_tbl(l_curr_hour+1).time := get_time_str(l_curr_hour);
265 HourlyVol_tbl(l_curr_hour+1).totalTrxn := HourlyVol_tbl(l_curr_hour+1).totalTrxn +
266 (t_vol.totalTrxn * t_vol.factor);
267
268
269 l_prev_hour := l_curr_hour;
270
271 END LOOP;
272
273 IF( HourlyVol_tbl.count < 24 ) THEN
274 pad_table( HourlyVol_tbl, HourlyVol_tbl.count, 23);
275 END IF;
276
277 For i IN 1..HourlyVol_tbl.count loop
278 NULL;
279 -- dbms_output.put_line('The Columns are ' || HourlyVol_tbl(i).totalTrxn || ' : ' || HourlyVol_tbl(i).time );
280 -- dbms_output.put_line('The time is ' || HourlyVol_tbl(i).time );
281 end loop;
282
283 END Get_Hourly_Volume;
284
285 --------------------------------------------------------------------------------------
286 -- 2. Get_Trxn_Trends
287 -- Start of comments
288 -- API name : Get_Trxn_Trends
289 -- Type : Private
290 -- Pre-reqs : None
291 -- Function : Fetches the information for Credit/Purchase Cards.
292 -- Parameters :
293 -- IN : payee_id IN VARCHAR2
294 -- output_type IN VARCHAR2
295 -- TrxnTrend_tbl OUT TrxnTrends_tbl_type
296 -- End of comments
297 --------------------------------------------------------------------------------------
298 Procedure Get_Trxn_Trends ( payee_id IN VARCHAR2,
299 output_type IN VARCHAR2,
300 TrxnTrend_tbl OUT NOCOPY TrxnTrends_tbl_type
301 ) IS
302
303 CURSOR get_trends_csr( from_date DATE, to_date DATE, l_payeeId VARCHAR2) IS
304 SELECT CurrencyNameCode currency,
305 instrtype type,
306 -- DECODE(trxntypeid, 3, 2, 5,-1, 10, -1, 11, -1, 1) factor, -- Bug 3458221
307 DECODE(trxntypeid, 5, -1, 8, 0, 9, 0, 10, -1, 11, -1, 1) factor,
308 COUNT(*) total_trxn,
309 SUM(amount) total_amt,
310 TRUNC(updatedate) trxndate
311 FROM iby_trxn_summaries_all
312 WHERE TRUNC(updatedate) > from_date
313 AND TRUNC(updatedate) <= to_date
314 AND trxntypeid IN (2,3,5,8,9,10,11)
315 AND instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
316 AND payeeid LIKE l_payeeId
317 AND status IN
318 (-99,0,1,2,4,5,8,15,16,17,19,20,21,100,109,111,9999)
319 GROUP BY instrtype,
320 -- DECODE(trxntypeid, 3, 2, 5,-1, 10, -1, 11, -1, 1), -- Bug 3458221
321 DECODE(trxntypeid, 5, -1, 8, 0, 9, 0, 10, -1, 11, -1, 1),
322 CurrencyNameCode, TRUNC(updatedate)
323 ORDER BY instrtype, TRUNC(updatedate) ASC;
324
325 l_from_date DATE;
326 l_to_date DATE;
327 l_curr_date DATE := NULL;
328 l_prev_date DATE := NULL;
329 l_curr_type VARCHAR2(100);
330 l_prev_type VARCHAR2(100);
331 returnAmount boolean := FALSE;
332 l_currType_tbl Trends_tbl_type := Trends_tbl_type();
333 l_total_tbl Trends_tbl_type := Trends_tbl_type();
334 l_amount NUMBER := 0;
335
336 l_tbl_count PLS_INTEGER;
337 l_payeeId VARCHAR2(80);
338
339 -- Bug 3714173: reporting currency
340 l_to_currency VARCHAR2(10);
341
342 BEGIN
343 -- Bug 3714173: Retrieve the reporting currency
344 l_to_currency := nvl(fnd_profile.value('IBY_DBC_REPORTING_CURRENCY'), 'USD');
345
346 -- Check whether 'amount' or 'transactions' need to be returned.
347 returnAmount := (output_type = C_OUTPUTTYPE_AMOUNT);
348
349 -- Set the dates appropriately
350 l_from_date := LAST_DAY(ADD_MONTHS(SYSDATE, -13));
351 l_to_date := LAST_DAY(ADD_MONTHS(SYSDATE, -1));
352
353 -- Set the payee if it is null.
354 IF( payee_id IS NULL OR TRIM(payee_id) = '') THEN
355 l_payeeId := '%';
356 ELSE
357 l_payeeId := payee_id;
358 END IF;
359
360 /* --- Processing Card types ---- */
361
362 -- Initialize the count
363 l_tbl_count := 1;
364 l_curr_type := '*';
365 l_prev_type := '*';
366
367 FOR t_type IN get_trends_csr( l_from_date, l_to_date, l_payeeId) LOOP
368 l_amount := IBY_DBCCARD_PVT.Convert_Amount( t_type.currency,
369 -- C_TO_CURRENCY,
370 l_to_currency,
371 t_type.trxndate,
372 t_type.total_amt,
373 NULL);
374
375 -- We ignore the cases when the RATE or CURRENCY is not found.
376 IF ( (l_amount >= 0) ) THEN
377
378 l_curr_date := TRUNC(t_type.trxndate, 'mm');
379 l_curr_type := t_type.type;
380
381 IF( (l_prev_type <> '*') AND (l_prev_type <> l_curr_type) ) THEN
382 complete_table( l_currType_tbl, l_from_date, l_prev_type, C_PERIOD_MONTHLY);
383 add_to_output( TrxnTrend_tbl, l_currType_tbl);
384 add_to_total( l_total_tbl, l_currType_tbl);
385 l_currType_tbl.TRIM(l_currType_tbl.COUNT);
386 -- Initialize the values
387 l_tbl_count := 1;
388 l_curr_date := NULL;
389 l_prev_date := NULL;
390 END IF;
391
392 l_prev_type := l_curr_type;
393
394 IF( (l_prev_date IS NOT NULL) AND (l_prev_date <> l_curr_date) ) THEN
395 l_tbl_count := l_tbl_count + 1;
396 END IF;
397
398 WHILE (NOT l_currType_tbl.EXISTS(l_tbl_COUNT) ) LOOP
399 l_currType_tbl.EXTEND;
400 l_currTYpe_tbl(l_currTYpe_tbl.COUNT).value := 0;
401 END LOOP;
402
403 l_prev_date := l_curr_date;
404
405 l_currType_tbl(l_tbl_count).month := TO_CHAR(l_curr_date, 'MON');
406
407 IF( returnAmount ) THEN
408 l_currType_tbl(l_tbl_count).value := l_currType_tbl(l_tbl_count).value + (t_type.total_amt * t_type.factor);
409 ELSE
410 -- We should count a transaction twice if it is AuthCapture
411 /* -- Bug 3458221: Only auth trans count, and capt trans are excluded.
412 IF( t_type.factor = 2) THEN
413 l_currType_tbl(l_tbl_count).value := l_currType_tbl(l_tbl_count).value + (2 * t_type.total_trxn);
414 ELSE
415 l_currType_tbl(l_tbl_count).value := l_currType_tbl(l_tbl_count).value + t_type.total_trxn;
416 END IF;
417 */
418 l_currType_tbl(l_tbl_count).value := l_currType_tbl(l_tbl_count).value + abs(t_type.factor) * t_type.total_trxn;
419 END IF;
420 l_currType_tbl(l_tbl_count).type := t_type.type;
421 l_currType_tbl(l_tbl_count).tdate := l_curr_date;
422
423 END IF;
424 END LOOP;
425
426 IF( l_curr_type = '*' ) THEN
427 l_curr_type := 'No Records Found';
428 complete_table( l_currType_tbl, l_from_date, l_curr_type, C_PERIOD_MONTHLY);
429 add_to_output( TrxnTrend_tbl, l_currType_tbl);
430 ELSE
431 complete_table( l_currType_tbl, l_from_date, l_curr_type, C_PERIOD_MONTHLY);
435 add_to_output( TrxnTrend_tbl, l_total_tbl);
432 add_to_output( TrxnTrend_tbl, l_currType_tbl);
433 add_to_total( l_total_tbl, l_currType_tbl);
434
436 END IF;
437
438
439 END Get_Trxn_Trends;
440
441 --------------------------------------------------------------------------------------
442 -- 3. Get_Processor_Trends
443 -- Start of comments
444 -- API name : Get_Processor_Trends
445 -- Type : Private
446 -- Pre-reqs : None
447 -- Function : Fetches the information for Processors.
448 -- Parameters :
449 -- IN : payee_id IN VARCHAR2
450 -- output_type IN VARCHAR2
451 -- TrxnTrend_tbl OUT TrxnTrends_tbl_type
452 -- End of comments
453 --------------------------------------------------------------------------------------
454 Procedure Get_Processor_Trends ( payee_id IN VARCHAR2,
455 output_type IN VARCHAR2,
456 TrxnTrend_tbl OUT NOCOPY TrxnTrends_tbl_type
457 ) IS
458
459 CURSOR get_trends_csr( from_date DATE, to_date DATE, l_payeeId VARCHAR2) IS
460 SELECT a.CurrencyNameCode currency,
461 b.name TYPE,
462 -- DECODE(a.trxntypeid, 3, 2, 5,-1, 10, -1, 11, -1, 1) factor, -- Bug 3458221
463 DECODE(a.trxntypeid, 5, -1, 8, 0, 9, 0, 10, -1, 11, -1, 1) factor,
464 COUNT(*) total_trxn,
465 SUM(a.amount) total_amt,
466 TRUNC(a.updatedate) trxndate
467 FROM iby_trxn_summaries_all a,
468 iby_bepinfo b
469 WHERE TRUNC(a.updatedate) > from_date
470 AND TRUNC(a.updatedate) <= to_date
471 AND a.trxntypeid IN (2,3,5,8,9,10,11)
472 AND a.instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
473 AND a.payeeid LIKE l_payeeId
474 AND a.bepid = b.bepid
475 AND a.status IN
476 (-99,0,1,2,4,5,8,15,16,17,19,20,21,100,109,111,9999)
477 GROUP BY b.name,
478 -- DECODE(a.trxntypeid, 3, 2, 5,-1, 10, -1, 11, -1, 1), -- Bug 3458221
479 DECODE(a.trxntypeid, 5, -1, 8, 0, 9, 0, 10, -1, 11, -1, 1),
480 a.CurrencyNameCode, TRUNC(a.updatedate)
481 ORDER BY b.name, TRUNC(a.updatedate) ASC;
482
483 l_from_date DATE;
484 l_to_date DATE;
485 l_curr_date DATE := NULL;
486 l_prev_date DATE := NULL;
487 l_curr_type VARCHAR2(100);
488 l_prev_type VARCHAR2(100);
489 returnAmount boolean := FALSE;
490 l_currType_tbl Trends_tbl_type := Trends_tbl_type();
491 --l_total_tbl Trends_tbl_type := Trends_tbl_type();
492 l_amount NUMBER := 0;
493
494 l_tbl_count PLS_INTEGER;
495 l_payeeId VARCHAR2(80);
496
497 -- Bug 3714173: reporting currency
498 l_to_currency VARCHAR2(10);
499
500 BEGIN
501 -- Bug 3714173: Retrieve the reporting currency
502 l_to_currency := nvl(fnd_profile.value('IBY_DBC_REPORTING_CURRENCY'), 'USD');
503
504 -- Check whether 'amount' or 'transactions' need to be returned.
505 returnAmount := (output_type = C_OUTPUTTYPE_AMOUNT);
506
507 -- Set the dates appropriately
508 l_from_date := LAST_DAY(ADD_MONTHS(SYSDATE, -13));
509 l_to_date := LAST_DAY(ADD_MONTHS(SYSDATE, -1));
510
511 -- Set the payee if it is null.
512 IF( payee_id IS NULL OR TRIM(payee_id) = '') THEN
513 l_payeeId := '%';
514 ELSE
515 l_payeeId := payee_id;
516 END IF;
517
518 /* --- Processing Processor types ---- */
519
520 -- Initialize the count
521 l_tbl_count := 1;
522 l_curr_type := '*';
523 l_prev_type := '*';
524
525 FOR t_type IN get_trends_csr( l_from_date, l_to_date, l_payeeId) LOOP
526 l_amount := IBY_DBCCARD_PVT.Convert_Amount( t_type.currency,
527 -- C_TO_CURRENCY,
528 l_to_currency,
529 t_type.trxndate,
530 t_type.total_amt,
531 NULL);
532
533 -- We ignore the cases when the RATE or CURRENCY is not found.
534 IF ( (l_amount >= 0) ) THEN
535
536 l_curr_date := TRUNC(t_type.trxndate, 'mm');
537 l_curr_type := t_type.type;
538
539 IF( (l_prev_type <> '*') AND (l_prev_type <> l_curr_type) ) THEN
540 complete_table( l_currType_tbl, l_from_date, l_prev_type, C_PERIOD_MONTHLY);
541 add_to_output( TrxnTrend_tbl, l_currType_tbl);
542 l_currType_tbl.TRIM(l_currType_tbl.COUNT);
543 -- Initialize the values
544 l_tbl_count := 1;
545 l_curr_date := NULL;
546 l_prev_date := NULL;
547 END IF;
548
549 l_prev_type := l_curr_type;
550
551 IF( (l_prev_date IS NOT NULL) AND (l_prev_date <> l_curr_date) ) THEN
552 l_tbl_count := l_tbl_count + 1;
553 END IF;
554
555 WHILE (NOT l_currType_tbl.EXISTS(l_tbl_COUNT) ) LOOP
556 l_currType_tbl.EXTEND;
557 l_currTYpe_tbl(l_currTYpe_tbl.COUNT).value := 0;
558 END LOOP;
559
560 l_prev_date := l_curr_date;
561
562 l_currType_tbl(l_tbl_count).month := TO_CHAR(l_curr_date, 'MON');
563
564 IF( returnAmount ) THEN
565 l_currType_tbl(l_tbl_count).value := l_currType_tbl(l_tbl_count).value + (t_type.total_amt * t_type.factor);
566 ELSE
567 -- We should count a transaction twice if it is AuthCapture
571 ELSE
568 /* -- Bug 3458221: Only auth trans count and capt trans are excluded.
569 IF( t_type.factor = 2) THEN
570 l_currType_tbl(l_tbl_count).value := l_currType_tbl(l_tbl_count).value + (2 * t_type.total_trxn);
572 l_currType_tbl(l_tbl_count).value := l_currType_tbl(l_tbl_count).value + t_type.total_trxn;
573 END IF;
574 */
575 l_currType_tbl(l_tbl_count).value := l_currType_tbl(l_tbl_count).value + abs(t_type.factor) * t_type.total_trxn;
576 END IF;
577 l_currType_tbl(l_tbl_count).type := t_type.type;
578 l_currType_tbl(l_tbl_count).tdate := l_curr_date;
579
580 END IF;
581 END LOOP;
582
583 IF ( l_curr_type = '*' ) THEN
584 l_curr_type := 'No Records Found';
585 END IF;
586
587 complete_table( l_currType_tbl, l_from_date, l_curr_type, C_PERIOD_MONTHLY);
588 add_to_output( TrxnTrend_tbl, l_currType_tbl);
589
590
591 END Get_Processor_Trends;
592
593 --------------------------------------------------------------------------------------
594 -- 4. Get_Subtype_Trends
595 -- Start of comments
596 -- API name : Get_Subtype_Trends
597 -- Type : Private
598 -- Pre-reqs : None
599 -- Function : Fetches the information for Credit/Purchase SubTypes.
600 -- Parameters :
601 -- IN : payee_id IN VARCHAR2
602 -- output_type IN VARCHAR2
603 -- TrxnTrend_tbl OUT TrxnTrends_tbl_type
604 -- End of comments
605 --------------------------------------------------------------------------------------
606 Procedure Get_Subtype_Trends ( payee_id IN VARCHAR2,
607 output_type IN VARCHAR2,
608 TrxnTrend_tbl OUT NOCOPY TrxnTrends_tbl_type
609 ) IS
610
611 CURSOR get_trends_csr( from_date DATE, to_date DATE, l_payeeId VARCHAR2) IS
612 SELECT CurrencyNameCode currency,
613 instrsubtype TYPE,
614 -- DECODE(trxntypeid, 3, 2, 5,-1, 10, -1, 11, -1, 1) factor, -- Bug 3458221
615 DECODE(trxntypeid, 5, -1, 8, 0, 9, 0, 10, -1, 11, -1, 1) factor,
616 COUNT(*) total_trxn,
617 SUM(amount) total_amt,
618 TRUNC(updatedate) trxndate
619 FROM iby_trxn_summaries_all
620 WHERE TRUNC(updatedate) > from_date
621 AND TRUNC(updatedate) <= TO_DATE
622 AND trxntypeid IN (2,3,5,8,9,10,11)
623 AND instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
624 AND instrsubtype IS NOT NULL
625 AND payeeid LIKE l_payeeId
626 AND status IN
627 (-99,0,1,2,4,5,8,15,16,17,19,20,21,100,109,111,9999)
628 GROUP BY instrsubtype,
629 -- DECODE(trxntypeid, 3, 2, 5,-1, 10, -1, 11, -1, 1), -- Bug 3458221
630 DECODE(trxntypeid, 5, -1, 8, 0, 9, 0, 10, -1, 11, -1, 1),
631 CurrencyNameCode, TRUNC(updatedate)
632 ORDER BY instrsubtype, TRUNC(updatedate) ASC;
633
634 l_from_date DATE;
635 l_to_date DATE;
636 l_curr_date DATE := NULL;
637 l_prev_date DATE := NULL;
638 l_curr_type VARCHAR2(100);
639 l_prev_type VARCHAR2(100);
640 returnAmount boolean := FALSE;
641 l_currType_tbl Trends_tbl_type := Trends_tbl_type();
642 --l_total_tbl Trends_tbl_type := Trends_tbl_type();
643 l_amount NUMBER := 0;
644
645 l_tbl_count PLS_INTEGER;
646 l_payeeId VARCHAR2(80);
647
648 -- Bug 3714173: reporting currency
649 l_to_currency VARCHAR2(10);
650
651 BEGIN
652 -- Bug 3714173: Retrieve the reporting currency
653 l_to_currency := nvl(fnd_profile.value('IBY_DBC_REPORTING_CURRENCY'), 'USD');
654
655 -- Check whether 'amount' or 'transactions' need to be returned.
656 returnAmount := (output_type = C_OUTPUTTYPE_AMOUNT);
657
658 -- Set the dates appropriately
659 l_from_date := LAST_DAY(ADD_MONTHS(SYSDATE, -13));
660 l_to_date := LAST_DAY(ADD_MONTHS(SYSDATE, -1));
661
662 -- Set the payee if it is null.
663 IF( payee_id IS NULL OR TRIM(payee_id) = '') THEN
664 l_payeeId := '%';
665 ELSE
666 l_payeeId := payee_id;
667 END IF;
668
669 /* --- Processing Processor types ---- */
670
671 -- Initialize the count
672 l_tbl_count := 1;
673 l_curr_type := '*';
674 l_prev_type := '*';
675
676 FOR t_type IN get_trends_csr( l_from_date, l_to_date, l_payeeId) LOOP
677 l_amount := IBY_DBCCARD_PVT.Convert_Amount( t_type.currency,
678 -- C_TO_CURRENCY,
679 l_to_currency,
680 t_type.trxndate,
681 t_type.total_amt,
682 NULL);
683
684 -- We ignore the cases when the RATE or CURRENCY is not found.
685 IF ( (l_amount >= 0) ) THEN
686
687 l_curr_date := TRUNC(t_type.trxndate, 'mm');
688 l_curr_type := t_type.type;
689
690 IF( (l_prev_type <> '*') AND (l_prev_type <> l_curr_type) ) THEN
691 complete_table( l_currType_tbl, l_from_date, l_prev_type, C_PERIOD_MONTHLY);
692 add_to_output( TrxnTrend_tbl, l_currType_tbl);
693 l_currType_tbl.TRIM(l_currType_tbl.COUNT);
694 -- Initialize the values
695 l_tbl_count := 1;
696 l_curr_date := NULL;
697 l_prev_date := NULL;
698 END IF;
699
700 l_prev_type := l_curr_type;
701
705
702 IF( (l_prev_date IS NOT NULL) AND (l_prev_date <> l_curr_date) ) THEN
703 l_tbl_count := l_tbl_count + 1;
704 END IF;
706 WHILE (NOT l_currType_tbl.EXISTS(l_tbl_COUNT) ) LOOP
707 l_currType_tbl.EXTEND;
708 l_currTYpe_tbl(l_currTYpe_tbl.COUNT).value := 0;
709 END LOOP;
710
711 l_prev_date := l_curr_date;
712
713 l_currType_tbl(l_tbl_count).month := TO_CHAR(l_curr_date, 'MON');
714
715 IF( returnAmount ) THEN
716 l_currType_tbl(l_tbl_count).value := l_currType_tbl(l_tbl_count).value + (t_type.total_amt * t_type.factor);
717 ELSE
718 -- We should count a transaction twice if it is AuthCapture
719 /* -- Bug 3458221: Only auth trans count and capt trans are excluded.
720 IF( t_type.factor = 2) THEN
721 l_currType_tbl(l_tbl_count).value := l_currType_tbl(l_tbl_count).value + (2 * t_type.total_trxn);
722 ELSE
723 l_currType_tbl(l_tbl_count).value := l_currType_tbl(l_tbl_count).value + t_type.total_trxn;
724 END IF;
725 */
726 l_currType_tbl(l_tbl_count).value := l_currType_tbl(l_tbl_count).value + abs(t_type.factor) * t_type.total_trxn;
727 END IF;
728 l_currType_tbl(l_tbl_count).type := t_type.type;
729 l_currType_tbl(l_tbl_count).tdate := l_curr_date;
730
731 END IF;
732 END LOOP;
733
734 IF ( l_curr_type = '*' ) THEN
735 l_curr_type := 'No Records Found';
736 END IF;
737
738 complete_table( l_currType_tbl, l_from_date, l_curr_type, C_PERIOD_MONTHLY);
739 add_to_output( TrxnTrend_tbl, l_currType_tbl);
740
741
742 END Get_Subtype_Trends;
743
744 --------------------------------------------------------------------------------------
745 -- 5. Get_Failure_Trends
746 -- Start of comments
747 -- API name : Get_Failure_Trends
748 -- Type : Private
749 -- Pre-reqs : None
750 -- Function : Fetches the information for Authorization and Settlement failures.
751 -- Parameters :
752 -- IN : payee_id IN VARCHAR2
753 -- output_type IN VARCHAR2
754 -- TrxnTrend_tbl OUT TrxnTrends_tbl_type
755 -- End of comments
756 --------------------------------------------------------------------------------------
757 Procedure Get_Failure_Trends ( payee_id IN VARCHAR2,
758 output_type IN VARCHAR2,
759 TrxnTrend_tbl OUT NOCOPY TrxnTrends_tbl_type
760 ) IS
761
762 CURSOR get_trends_csr( from_date DATE, to_date DATE, l_payeeId VARCHAR2) IS
763 SELECT CurrencyNameCode currency,
764 DECODE(trxntypeid, 2, 'A', 3, 'B', 'C') factor,
765 COUNT(*) total_trxn,
766 SUM(amount) total_amt,
767 TRUNC(updatedate) trxndate
768 FROM iby_trxn_summaries_all
769 WHERE TRUNC(updatedate) >= from_date
770 AND TRUNC(updatedate) <= TO_DATE
771 AND trxntypeid IN (2,3,8,9)
772 AND instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
773 AND payeeid LIKE l_payeeId
774 AND status IN
775 (-99,1,2,4,5,8,15,16,17,19,20,21,9999)
776 GROUP BY DECODE(trxntypeid, 2, 'A', 3, 'B', 'C'),
777 CurrencyNameCode, TRUNC(updatedate)
778 ORDER BY TRUNC(updatedate) ASC;
779
780 l_from_date DATE;
781 l_to_date DATE;
782
783 l_curr_auth_date DATE := NULL;
784 l_prev_auth_date DATE := NULL;
785
786 l_curr_sett_date DATE := NULL;
787 l_prev_sett_date DATE := NULL;
788
789 returnAmount boolean := FALSE;
790
791 l_authFail_tbl Trends_tbl_type := Trends_tbl_type();
792 l_settFail_tbl Trends_tbl_type := Trends_tbl_type();
793
794 l_amount NUMBER := 0;
795
796 l_auth_count PLS_INTEGER;
797 l_sett_count PLS_INTEGER;
798
799 l_payeeId VARCHAR2(80);
800
801 C_AUTHORIZATIONS CONSTANT VARCHAR2(15) := 'Authorizations';
802 C_SETTLEMENTS CONSTANT VARCHAR2(15) := 'Settlements';
803
804 -- Bug 3714173: reporting currency
805 l_to_currency VARCHAR2(10);
806
807 BEGIN
808 -- Bug 3714173: Retrieve the reporting currency
809 l_to_currency := nvl(fnd_profile.value('IBY_DBC_REPORTING_CURRENCY'), 'USD');
810
811 -- Check whether 'amount' or 'transactions' need to be returned.
812 returnAmount := (output_type = C_OUTPUTTYPE_AMOUNT);
813
814 -- Set the dates appropriately
815 l_from_date := TRUNC(ADD_MONTHS(SYSDATE, -37), 'yyyy');
816 l_to_date := (TRUNC(SYSDATE, 'yyyy') - 1);
817
818 -- Set the payee if it is null.
819 IF( payee_id IS NULL OR TRIM(payee_id) = '') THEN
820 l_payeeId := '%';
821 ELSE
822 l_payeeId := payee_id;
823 END IF;
824
825 /* --- Processing Failures ---- */
826
827 -- Initialize the count
828 l_auth_count := 1;
829 l_sett_count := 1;
830
831 FOR t_type IN get_trends_csr( l_from_date, l_to_date, l_payeeId) LOOP
832 l_amount := IBY_DBCCARD_PVT.Convert_Amount( t_type.currency,
833 -- C_TO_CURRENCY,
834 l_to_currency,
835 t_type.trxndate,
836 t_type.total_amt,
837 NULL);
838
839 -- We ignore the cases when the RATE or CURRENCY is not found.
840 IF ( (l_amount >= 0) ) THEN
841
842
846 l_curr_auth_date := TRUNC(t_type.trxndate, 'yyyy');
843 -- Processing Authorization requests here
844 IF (t_type.factor IN ('A','B')) THEN
845
847 IF( (l_prev_auth_date IS NOT NULL) AND (l_prev_auth_date <> l_curr_auth_date) ) THEN
848 l_auth_count := l_auth_count + 1;
849 END IF;
850
851 l_prev_auth_date := l_curr_auth_date;
852
853 WHILE (NOT l_authFail_tbl.EXISTS(l_auth_count) ) LOOP
854 l_authFail_tbl.EXTEND;
855 l_authFail_tbl(l_authFail_tbl.COUNT).value := 0;
856 END LOOP;
857
858 l_authFail_tbl(l_auth_count).month := TO_CHAR(l_curr_auth_date, 'yyyy');
859 IF( returnAmount ) THEN
860 l_authFail_tbl(l_auth_count).value := l_authFail_tbl(l_auth_count).value + t_type.total_amt;
861 ELSE
862 l_authFail_tbl(l_auth_count).value := l_authFail_tbl(l_auth_count).value + t_type.total_trxn;
863 END IF;
864 l_authFail_tbl(l_auth_count).type := C_AUTHORIZATIONS;
865 l_authFail_tbl(l_auth_count).tdate := l_curr_auth_date;
866
867 END IF;
868
869 -- Processing Settlements requests here
870 IF (t_type.factor IN ('B','C')) THEN
871
872 l_curr_sett_date := TRUNC(t_type.trxndate, 'yyyy');
873 IF( (l_prev_sett_date IS NOT NULL) AND (l_prev_sett_date <> l_curr_sett_date) ) THEN
874 l_sett_count := l_sett_count + 1;
875 END IF;
876
877 l_prev_sett_date := l_curr_sett_date;
878
879 WHILE (NOT l_settFail_tbl.EXISTS(l_sett_count) ) LOOP
880 l_settFail_tbl.EXTEND;
881 l_settFail_tbl(l_settFail_tbl.COUNT).value := 0;
882 END LOOP;
883
884 l_settFail_tbl(l_sett_count).month := TO_CHAR(l_curr_sett_date, 'yyyy');
885 IF( returnAmount ) THEN
886 l_settFail_tbl(l_sett_count).value := l_settFail_tbl(l_sett_count).value + t_type.total_amt;
887 ELSE
888 l_settFail_tbl(l_sett_count).value := l_settFail_tbl(l_sett_count).value + t_type.total_trxn;
889 END IF;
890 l_settFail_tbl(l_sett_count).type := C_SETTLEMENTS;
891 l_settFail_tbl(l_sett_count).tdate := l_curr_sett_date;
892
893 END IF;
894
895 END IF;
896 END LOOP;
897
898 complete_table( l_authFail_tbl, ADD_MONTHS(l_from_date,-12), C_AUTHORIZATIONS, C_PERIOD_YEARLY);
899 complete_table( l_settFail_tbl, ADD_MONTHS(l_from_date,-12), C_SETTLEMENTS, C_PERIOD_YEARLY);
900 add_to_output( TrxnTrend_tbl, l_authFail_tbl);
901 add_to_output( TrxnTrend_tbl, l_settFail_tbl);
902
903
904 END Get_Failure_Trends;
905
906 END IBY_DBCCARD_GRAPH_PVT;