1 package body iby_dbccard_graph_pvt_w as
2 /* $Header: ibyrdghb.pls 115.1 2002/11/16 01:42:18 jleybovi noship $ */
3 rosetta_g_mistake_date date := to_date('01/01/+4713', 'MM/DD/SYYYY');
4 rosetta_g_miss_date date := to_date('01/01/-4712', 'MM/DD/SYYYY');
5
6 function rosetta_g_miss_num_map(n number) return number as
7 a number := fnd_api.g_miss_num;
8 b number := 0-1962.0724;
9 begin
10 if n=a then return b; end if;
11 if n=b then return a; end if;
12 return n;
13 end;
14
15 -- this is to workaround the JDBC bug regarding IN DATE of value GMiss
16 function rosetta_g_miss_date_in_map(d date) return date as
17 begin
18 if d = rosetta_g_mistake_date then return fnd_api.g_miss_date; end if;
19 return d;
20 end;
21
22 procedure rosetta_table_copy_in_p12(t out nocopy iby_dbccard_graph_pvt.hourlyvol_tbl_type, a0 JTF_NUMBER_TABLE
23 , a1 JTF_NUMBER_TABLE
24 , a2 JTF_VARCHAR2_TABLE_100
25 ) as
26 ddindx binary_integer; indx binary_integer;
27 begin
28 if a0 is not null and a0.count > 0 then
29 if a0.count > 0 then
30 indx := a0.first;
31 ddindx := 1;
32 while true loop
33 t(ddindx).columnid := rosetta_g_miss_num_map(a0(indx));
34 t(ddindx).totaltrxn := rosetta_g_miss_num_map(a1(indx));
35 t(ddindx).time := a2(indx);
36 ddindx := ddindx+1;
37 if a0.last =indx
38 then exit;
39 end if;
40 indx := a0.next(indx);
41 end loop;
42 end if;
43 end if;
44 end rosetta_table_copy_in_p12;
45 procedure rosetta_table_copy_out_p12(t iby_dbccard_graph_pvt.hourlyvol_tbl_type, a0 out nocopy JTF_NUMBER_TABLE
46 , a1 out nocopy JTF_NUMBER_TABLE
47 , a2 out nocopy JTF_VARCHAR2_TABLE_100
48 ) as
49 ddindx binary_integer; indx binary_integer;
50 begin
51 if t is null or t.count = 0 then
52 a0 := JTF_NUMBER_TABLE();
53 a1 := JTF_NUMBER_TABLE();
54 a2 := JTF_VARCHAR2_TABLE_100();
55 else
56 a0 := JTF_NUMBER_TABLE();
57 a1 := JTF_NUMBER_TABLE();
58 a2 := JTF_VARCHAR2_TABLE_100();
59 if t.count > 0 then
60 a0.extend(t.count);
61 a1.extend(t.count);
62 a2.extend(t.count);
63 ddindx := t.first;
64 indx := 1;
65 while true loop
66 a0(indx) := rosetta_g_miss_num_map(t(ddindx).columnid);
67 a1(indx) := rosetta_g_miss_num_map(t(ddindx).totaltrxn);
68 a2(indx) := t(ddindx).time;
69 indx := indx+1;
70 if t.last =ddindx
71 then exit;
72 end if;
73 ddindx := t.next(ddindx);
74 end loop;
75 end if;
76 end if;
77 end rosetta_table_copy_out_p12;
78
79 procedure rosetta_table_copy_in_p13(t out nocopy iby_dbccard_graph_pvt.trxntrends_tbl_type, a0 JTF_VARCHAR2_TABLE_100
80 , a1 JTF_NUMBER_TABLE
81 , a2 JTF_VARCHAR2_TABLE_100
82 , a3 JTF_DATE_TABLE
83 ) as
84 ddindx binary_integer; indx binary_integer;
85 begin
86 if a0 is not null and a0.count > 0 then
87 if a0.count > 0 then
88 indx := a0.first;
89 ddindx := 1;
90 while true loop
91 t(ddindx).month := a0(indx);
92 t(ddindx).value := rosetta_g_miss_num_map(a1(indx));
93 t(ddindx).type := a2(indx);
94 t(ddindx).tdate := rosetta_g_miss_date_in_map(a3(indx));
95 ddindx := ddindx+1;
96 if a0.last =indx
97 then exit;
98 end if;
99 indx := a0.next(indx);
100 end loop;
101 end if;
102 end if;
103 end rosetta_table_copy_in_p13;
104 procedure rosetta_table_copy_out_p13(t iby_dbccard_graph_pvt.trxntrends_tbl_type, a0 out nocopy JTF_VARCHAR2_TABLE_100
105 , a1 out nocopy JTF_NUMBER_TABLE
106 , a2 out nocopy JTF_VARCHAR2_TABLE_100
107 , a3 out nocopy JTF_DATE_TABLE
108 ) as
109 ddindx binary_integer; indx binary_integer;
110 begin
111 if t is null or t.count = 0 then
112 a0 := JTF_VARCHAR2_TABLE_100();
113 a1 := JTF_NUMBER_TABLE();
114 a2 := JTF_VARCHAR2_TABLE_100();
115 a3 := JTF_DATE_TABLE();
116 else
117 a0 := JTF_VARCHAR2_TABLE_100();
118 a1 := JTF_NUMBER_TABLE();
119 a2 := JTF_VARCHAR2_TABLE_100();
120 a3 := JTF_DATE_TABLE();
121 if t.count > 0 then
122 a0.extend(t.count);
123 a1.extend(t.count);
124 a2.extend(t.count);
125 a3.extend(t.count);
126 ddindx := t.first;
127 indx := 1;
128 while true loop
129 a0(indx) := t(ddindx).month;
130 a1(indx) := rosetta_g_miss_num_map(t(ddindx).value);
131 a2(indx) := t(ddindx).type;
132 a3(indx) := t(ddindx).tdate;
133 indx := indx+1;
134 if t.last =ddindx
135 then exit;
136 end if;
137 ddindx := t.next(ddindx);
138 end loop;
139 end if;
140 end if;
141 end rosetta_table_copy_out_p13;
142
143 procedure rosetta_table_copy_in_p14(t out nocopy iby_dbccard_graph_pvt.trends_tbl_type, a0 JTF_VARCHAR2_TABLE_100
144 , a1 JTF_NUMBER_TABLE
145 , a2 JTF_VARCHAR2_TABLE_100
146 , a3 JTF_DATE_TABLE
147 ) as
148 ddindx binary_integer; indx binary_integer;
149 begin
150 if a0 is not null and a0.count=0 then
151 t := iby_dbccard_graph_pvt.trends_tbl_type();
152 elsif a0 is not null and a0.count > 0 then
153 if a0.count > 0 then
154 t := iby_dbccard_graph_pvt.trends_tbl_type();
155 t.extend(a0.count);
156 indx := a0.first;
157 ddindx := 1;
158 while true loop
159 t(ddindx).month := a0(indx);
160 t(ddindx).value := rosetta_g_miss_num_map(a1(indx));
161 t(ddindx).type := a2(indx);
162 t(ddindx).tdate := rosetta_g_miss_date_in_map(a3(indx));
163 ddindx := ddindx+1;
164 if a0.last =indx
165 then exit;
166 end if;
167 indx := a0.next(indx);
168 end loop;
169 end if;
170 end if;
171 end rosetta_table_copy_in_p14;
172 procedure rosetta_table_copy_out_p14(t iby_dbccard_graph_pvt.trends_tbl_type, a0 out nocopy JTF_VARCHAR2_TABLE_100
173 , a1 out nocopy JTF_NUMBER_TABLE
174 , a2 out nocopy JTF_VARCHAR2_TABLE_100
175 , a3 out nocopy JTF_DATE_TABLE
176 ) as
177 ddindx binary_integer; indx binary_integer;
178 begin
179 if t is null then
180 a0 := null;
181 a1 := null;
182 a2 := null;
183 a3 := null;
184 elsif t.count = 0 then
185 a0 := JTF_VARCHAR2_TABLE_100();
186 a1 := JTF_NUMBER_TABLE();
187 a2 := JTF_VARCHAR2_TABLE_100();
188 a3 := JTF_DATE_TABLE();
189 else
190 a0 := JTF_VARCHAR2_TABLE_100();
191 a1 := JTF_NUMBER_TABLE();
192 a2 := JTF_VARCHAR2_TABLE_100();
193 a3 := JTF_DATE_TABLE();
194 if t.count > 0 then
195 a0.extend(t.count);
196 a1.extend(t.count);
197 a2.extend(t.count);
198 a3.extend(t.count);
199 ddindx := t.first;
200 indx := 1;
201 while true loop
202 a0(indx) := t(ddindx).month;
203 a1(indx) := rosetta_g_miss_num_map(t(ddindx).value);
204 a2(indx) := t(ddindx).type;
205 a3(indx) := t(ddindx).tdate;
206 indx := indx+1;
207 if t.last =ddindx
208 then exit;
209 end if;
210 ddindx := t.next(ddindx);
211 end loop;
212 end if;
213 end if;
214 end rosetta_table_copy_out_p14;
215
216 procedure get_hourly_volume(payee_id VARCHAR2
217 , p1_a0 out nocopy JTF_NUMBER_TABLE
218 , p1_a1 out nocopy JTF_NUMBER_TABLE
219 , p1_a2 out nocopy JTF_VARCHAR2_TABLE_100
220 )
221 as
222 ddhourlyvol_tbl iby_dbccard_graph_pvt.hourlyvol_tbl_type;
223 ddindx binary_integer; indx binary_integer;
224 begin
225
226 -- copy data to the local IN or IN-OUT args, if any
227
228
229 -- here's the delegated call to the old PL/SQL routine
230 iby_dbccard_graph_pvt.get_hourly_volume(payee_id,
231 ddhourlyvol_tbl);
232
233 -- copy data back from the local OUT or IN-OUT args, if any
234
235 iby_dbccard_graph_pvt_w.rosetta_table_copy_out_p12(ddhourlyvol_tbl, p1_a0
236 , p1_a1
237 , p1_a2
238 );
239 end;
240
241 procedure get_trxn_trends(payee_id VARCHAR2
242 , output_type VARCHAR2
243 , p2_a0 out nocopy JTF_VARCHAR2_TABLE_100
244 , p2_a1 out nocopy JTF_NUMBER_TABLE
245 , p2_a2 out nocopy JTF_VARCHAR2_TABLE_100
246 , p2_a3 out nocopy JTF_DATE_TABLE
247 )
248 as
249 ddtrxntrend_tbl iby_dbccard_graph_pvt.trxntrends_tbl_type;
250 ddindx binary_integer; indx binary_integer;
251 begin
252
253 -- copy data to the local IN or IN-OUT args, if any
254
255
256
257 -- here's the delegated call to the old PL/SQL routine
258 iby_dbccard_graph_pvt.get_trxn_trends(payee_id,
259 output_type,
260 ddtrxntrend_tbl);
261
262 -- copy data back from the local OUT or IN-OUT args, if any
263
264
265 iby_dbccard_graph_pvt_w.rosetta_table_copy_out_p13(ddtrxntrend_tbl, p2_a0
266 , p2_a1
267 , p2_a2
268 , p2_a3
269 );
270 end;
271
272 procedure get_processor_trends(payee_id VARCHAR2
273 , output_type VARCHAR2
274 , p2_a0 out nocopy JTF_VARCHAR2_TABLE_100
275 , p2_a1 out nocopy JTF_NUMBER_TABLE
276 , p2_a2 out nocopy JTF_VARCHAR2_TABLE_100
277 , p2_a3 out nocopy JTF_DATE_TABLE
278 )
279 as
280 ddtrxntrend_tbl iby_dbccard_graph_pvt.trxntrends_tbl_type;
281 ddindx binary_integer; indx binary_integer;
282 begin
283
284 -- copy data to the local IN or IN-OUT args, if any
285
286
287
288 -- here's the delegated call to the old PL/SQL routine
289 iby_dbccard_graph_pvt.get_processor_trends(payee_id,
290 output_type,
291 ddtrxntrend_tbl);
292
293 -- copy data back from the local OUT or IN-OUT args, if any
294
295
296 iby_dbccard_graph_pvt_w.rosetta_table_copy_out_p13(ddtrxntrend_tbl, p2_a0
297 , p2_a1
298 , p2_a2
299 , p2_a3
300 );
301 end;
302
303 procedure get_subtype_trends(payee_id VARCHAR2
304 , output_type VARCHAR2
305 , p2_a0 out nocopy JTF_VARCHAR2_TABLE_100
306 , p2_a1 out nocopy JTF_NUMBER_TABLE
307 , p2_a2 out nocopy JTF_VARCHAR2_TABLE_100
308 , p2_a3 out nocopy JTF_DATE_TABLE
309 )
310 as
311 ddtrxntrend_tbl iby_dbccard_graph_pvt.trxntrends_tbl_type;
312 ddindx binary_integer; indx binary_integer;
313 begin
314
315 -- copy data to the local IN or IN-OUT args, if any
316
317
318
319 -- here's the delegated call to the old PL/SQL routine
320 iby_dbccard_graph_pvt.get_subtype_trends(payee_id,
321 output_type,
322 ddtrxntrend_tbl);
323
324 -- copy data back from the local OUT or IN-OUT args, if any
325
326
327 iby_dbccard_graph_pvt_w.rosetta_table_copy_out_p13(ddtrxntrend_tbl, p2_a0
328 , p2_a1
329 , p2_a2
330 , p2_a3
331 );
332 end;
333
334 procedure get_failure_trends(payee_id VARCHAR2
335 , output_type VARCHAR2
336 , p2_a0 out nocopy JTF_VARCHAR2_TABLE_100
337 , p2_a1 out nocopy JTF_NUMBER_TABLE
338 , p2_a2 out nocopy JTF_VARCHAR2_TABLE_100
339 , p2_a3 out nocopy JTF_DATE_TABLE
340 )
341 as
342 ddtrxntrend_tbl iby_dbccard_graph_pvt.trxntrends_tbl_type;
343 ddindx binary_integer; indx binary_integer;
344 begin
345
346 -- copy data to the local IN or IN-OUT args, if any
347
348
349
350 -- here's the delegated call to the old PL/SQL routine
351 iby_dbccard_graph_pvt.get_failure_trends(payee_id,
352 output_type,
353 ddtrxntrend_tbl);
354
358 iby_dbccard_graph_pvt_w.rosetta_table_copy_out_p13(ddtrxntrend_tbl, p2_a0
355 -- copy data back from the local OUT or IN-OUT args, if any
356
357
359 , p2_a1
360 , p2_a2
361 , p2_a3
362 );
363 end;
364
365 end iby_dbccard_graph_pvt_w;