[Home] [Help]
PACKAGE BODY: APPS.IEM_SPV_MONITORING_PVT_W
Source
1 package body iem_spv_monitoring_pvt_w as
2 /* $Header: IEMPSPMB.pls 115.4 2003/09/08 23:46:58 chtang 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 -- this is to workaround the JDBC bug regarding IN DATE of value GMiss
7 function rosetta_g_miss_date_in_map(d date) return date as
8 begin
9 if d = rosetta_g_mistake_date then return fnd_api.g_miss_date; end if;
10 return d;
11 end;
12
13 procedure rosetta_table_copy_in_p1(t out nocopy iem_spv_monitoring_pvt.email_activity_tbl, a0 JTF_NUMBER_TABLE
14 , a1 JTF_NUMBER_TABLE
15 , a2 JTF_VARCHAR2_TABLE_300
16 , a3 JTF_NUMBER_TABLE
17 , a4 JTF_NUMBER_TABLE
18 , a5 JTF_NUMBER_TABLE
19 , a6 JTF_NUMBER_TABLE
20 , a7 JTF_NUMBER_TABLE
21 , a8 JTF_NUMBER_TABLE
22 , a9 JTF_NUMBER_TABLE
23 , a10 JTF_NUMBER_TABLE
24 , a11 JTF_VARCHAR2_TABLE_100
25 , a12 JTF_VARCHAR2_TABLE_100
26 ) as
27 ddindx binary_integer; indx binary_integer;
28 begin
29 if a0 is not null and a0.count > 0 then
30 if a0.count > 0 then
31 indx := a0.first;
32 ddindx := 1;
33 while true loop
34 t(ddindx).email_account_id := a0(indx);
35 t(ddindx).classification_id := a1(indx);
36 t(ddindx).account_classification_name := a2(indx);
37 t(ddindx).queue_count := a3(indx);
38 t(ddindx).total_count := a4(indx);
39 t(ddindx).queue_wait_time := a5(indx);
40 t(ddindx).agent_count := a6(indx);
41 t(ddindx).inbox_count := a7(indx);
42 t(ddindx).inbox_wait_time := a8(indx);
43 t(ddindx).queue_average_time := a9(indx);
44 t(ddindx).inbox_average_time := a10(indx);
45 t(ddindx).queue_zero_flag := a11(indx);
46 t(ddindx).inbox_zero_flag := a12(indx);
47 ddindx := ddindx+1;
48 if a0.last =indx
49 then exit;
50 end if;
51 indx := a0.next(indx);
52 end loop;
53 end if;
54 end if;
55 end rosetta_table_copy_in_p1;
56 procedure rosetta_table_copy_out_p1(t iem_spv_monitoring_pvt.email_activity_tbl, a0 out nocopy JTF_NUMBER_TABLE
57 , a1 out nocopy JTF_NUMBER_TABLE
58 , a2 out nocopy JTF_VARCHAR2_TABLE_300
59 , a3 out nocopy JTF_NUMBER_TABLE
60 , a4 out nocopy JTF_NUMBER_TABLE
61 , a5 out nocopy JTF_NUMBER_TABLE
62 , a6 out nocopy JTF_NUMBER_TABLE
63 , a7 out nocopy JTF_NUMBER_TABLE
64 , a8 out nocopy JTF_NUMBER_TABLE
65 , a9 out nocopy JTF_NUMBER_TABLE
66 , a10 out nocopy JTF_NUMBER_TABLE
67 , a11 out nocopy JTF_VARCHAR2_TABLE_100
68 , a12 out nocopy JTF_VARCHAR2_TABLE_100
69 ) as
70 ddindx binary_integer; indx binary_integer;
71 begin
72 if t is null or t.count = 0 then
73 a0 := JTF_NUMBER_TABLE();
74 a1 := JTF_NUMBER_TABLE();
75 a2 := JTF_VARCHAR2_TABLE_300();
76 a3 := JTF_NUMBER_TABLE();
77 a4 := JTF_NUMBER_TABLE();
78 a5 := JTF_NUMBER_TABLE();
79 a6 := JTF_NUMBER_TABLE();
80 a7 := JTF_NUMBER_TABLE();
81 a8 := JTF_NUMBER_TABLE();
82 a9 := JTF_NUMBER_TABLE();
83 a10 := JTF_NUMBER_TABLE();
84 a11 := JTF_VARCHAR2_TABLE_100();
85 a12 := JTF_VARCHAR2_TABLE_100();
86 else
87 a0 := JTF_NUMBER_TABLE();
88 a1 := JTF_NUMBER_TABLE();
89 a2 := JTF_VARCHAR2_TABLE_300();
90 a3 := JTF_NUMBER_TABLE();
91 a4 := JTF_NUMBER_TABLE();
92 a5 := JTF_NUMBER_TABLE();
93 a6 := JTF_NUMBER_TABLE();
94 a7 := JTF_NUMBER_TABLE();
95 a8 := JTF_NUMBER_TABLE();
96 a9 := JTF_NUMBER_TABLE();
97 a10 := JTF_NUMBER_TABLE();
98 a11 := JTF_VARCHAR2_TABLE_100();
99 a12 := JTF_VARCHAR2_TABLE_100();
100 if t.count > 0 then
101 a0.extend(t.count);
102 a1.extend(t.count);
103 a2.extend(t.count);
104 a3.extend(t.count);
105 a4.extend(t.count);
106 a5.extend(t.count);
107 a6.extend(t.count);
108 a7.extend(t.count);
109 a8.extend(t.count);
110 a9.extend(t.count);
111 a10.extend(t.count);
112 a11.extend(t.count);
113 a12.extend(t.count);
114 ddindx := t.first;
115 indx := 1;
116 while true loop
117 a0(indx) := t(ddindx).email_account_id;
118 a1(indx) := t(ddindx).classification_id;
119 a2(indx) := t(ddindx).account_classification_name;
120 a3(indx) := t(ddindx).queue_count;
121 a4(indx) := t(ddindx).total_count;
122 a5(indx) := t(ddindx).queue_wait_time;
123 a6(indx) := t(ddindx).agent_count;
124 a7(indx) := t(ddindx).inbox_count;
125 a8(indx) := t(ddindx).inbox_wait_time;
126 a9(indx) := t(ddindx).queue_average_time;
127 a10(indx) := t(ddindx).inbox_average_time;
128 a11(indx) := t(ddindx).queue_zero_flag;
129 a12(indx) := t(ddindx).inbox_zero_flag;
130 indx := indx+1;
131 if t.last =ddindx
132 then exit;
133 end if;
134 ddindx := t.next(ddindx);
135 end loop;
136 end if;
137 end if;
138 end rosetta_table_copy_out_p1;
139
140 procedure rosetta_table_copy_in_p3(t out nocopy iem_spv_monitoring_pvt.agent_activity_tbl, a0 JTF_NUMBER_TABLE
141 , a1 JTF_NUMBER_TABLE
142 , a2 JTF_VARCHAR2_TABLE_800
143 , a3 JTF_NUMBER_TABLE
144 , a4 JTF_NUMBER_TABLE
145 , a5 JTF_NUMBER_TABLE
146 , a6 JTF_NUMBER_TABLE
147 , a7 JTF_VARCHAR2_TABLE_500
148 , a8 JTF_NUMBER_TABLE
149 , a9 JTF_VARCHAR2_TABLE_100
150 , a10 JTF_VARCHAR2_TABLE_100
151 ) as
152 ddindx binary_integer; indx binary_integer;
153 begin
154 if a0 is not null and a0.count > 0 then
155 if a0.count > 0 then
156 indx := a0.first;
157 ddindx := 1;
158 while true loop
159 t(ddindx).resource_id := a0(indx);
160 t(ddindx).email_account_id := a1(indx);
161 t(ddindx).resource_account_name := a2(indx);
162 t(ddindx).email_count := a3(indx);
163 t(ddindx).assigned_email_count := a4(indx);
164 t(ddindx).average_age := a5(indx);
165 t(ddindx).oldest_age := a6(indx);
166 t(ddindx).last_login_time := a7(indx);
167 t(ddindx).account_count := a8(indx);
168 t(ddindx).zero_flag := a9(indx);
169 t(ddindx).requeue_all_flag := a10(indx);
170 ddindx := ddindx+1;
171 if a0.last =indx
172 then exit;
173 end if;
174 indx := a0.next(indx);
175 end loop;
176 end if;
177 end if;
178 end rosetta_table_copy_in_p3;
179 procedure rosetta_table_copy_out_p3(t iem_spv_monitoring_pvt.agent_activity_tbl, a0 out nocopy JTF_NUMBER_TABLE
180 , a1 out nocopy JTF_NUMBER_TABLE
181 , a2 out nocopy JTF_VARCHAR2_TABLE_800
182 , a3 out nocopy JTF_NUMBER_TABLE
183 , a4 out nocopy JTF_NUMBER_TABLE
184 , a5 out nocopy JTF_NUMBER_TABLE
185 , a6 out nocopy JTF_NUMBER_TABLE
186 , a7 out nocopy JTF_VARCHAR2_TABLE_500
187 , a8 out nocopy JTF_NUMBER_TABLE
188 , a9 out nocopy JTF_VARCHAR2_TABLE_100
189 , a10 out nocopy JTF_VARCHAR2_TABLE_100
190 ) as
191 ddindx binary_integer; indx binary_integer;
192 begin
193 if t is null or t.count = 0 then
194 a0 := JTF_NUMBER_TABLE();
195 a1 := JTF_NUMBER_TABLE();
196 a2 := JTF_VARCHAR2_TABLE_800();
197 a3 := JTF_NUMBER_TABLE();
198 a4 := JTF_NUMBER_TABLE();
199 a5 := JTF_NUMBER_TABLE();
200 a6 := JTF_NUMBER_TABLE();
201 a7 := JTF_VARCHAR2_TABLE_500();
202 a8 := JTF_NUMBER_TABLE();
203 a9 := JTF_VARCHAR2_TABLE_100();
204 a10 := JTF_VARCHAR2_TABLE_100();
205 else
206 a0 := JTF_NUMBER_TABLE();
207 a1 := JTF_NUMBER_TABLE();
208 a2 := JTF_VARCHAR2_TABLE_800();
209 a3 := JTF_NUMBER_TABLE();
210 a4 := JTF_NUMBER_TABLE();
211 a5 := JTF_NUMBER_TABLE();
212 a6 := JTF_NUMBER_TABLE();
213 a7 := JTF_VARCHAR2_TABLE_500();
214 a8 := JTF_NUMBER_TABLE();
215 a9 := JTF_VARCHAR2_TABLE_100();
216 a10 := JTF_VARCHAR2_TABLE_100();
217 if t.count > 0 then
218 a0.extend(t.count);
219 a1.extend(t.count);
220 a2.extend(t.count);
221 a3.extend(t.count);
222 a4.extend(t.count);
223 a5.extend(t.count);
224 a6.extend(t.count);
225 a7.extend(t.count);
226 a8.extend(t.count);
227 a9.extend(t.count);
228 a10.extend(t.count);
229 ddindx := t.first;
230 indx := 1;
231 while true loop
232 a0(indx) := t(ddindx).resource_id;
233 a1(indx) := t(ddindx).email_account_id;
234 a2(indx) := t(ddindx).resource_account_name;
235 a3(indx) := t(ddindx).email_count;
236 a4(indx) := t(ddindx).assigned_email_count;
237 a5(indx) := t(ddindx).average_age;
238 a6(indx) := t(ddindx).oldest_age;
239 a7(indx) := t(ddindx).last_login_time;
240 a8(indx) := t(ddindx).account_count;
241 a9(indx) := t(ddindx).zero_flag;
242 a10(indx) := t(ddindx).requeue_all_flag;
243 indx := indx+1;
244 if t.last =ddindx
245 then exit;
246 end if;
247 ddindx := t.next(ddindx);
248 end loop;
249 end if;
250 end if;
251 end rosetta_table_copy_out_p3;
252
253 procedure get_email_activity(p_api_version_number NUMBER
254 , p_init_msg_list VARCHAR2
255 , p_commit VARCHAR2
256 , p3_a0 out nocopy JTF_NUMBER_TABLE
257 , p3_a1 out nocopy JTF_NUMBER_TABLE
258 , p3_a2 out nocopy JTF_VARCHAR2_TABLE_300
259 , p3_a3 out nocopy JTF_NUMBER_TABLE
260 , p3_a4 out nocopy JTF_NUMBER_TABLE
261 , p3_a5 out nocopy JTF_NUMBER_TABLE
262 , p3_a6 out nocopy JTF_NUMBER_TABLE
263 , p3_a7 out nocopy JTF_NUMBER_TABLE
264 , p3_a8 out nocopy JTF_NUMBER_TABLE
265 , p3_a9 out nocopy JTF_NUMBER_TABLE
266 , p3_a10 out nocopy JTF_NUMBER_TABLE
267 , p3_a11 out nocopy JTF_VARCHAR2_TABLE_100
268 , p3_a12 out nocopy JTF_VARCHAR2_TABLE_100
269 , x_return_status out nocopy VARCHAR2
270 , x_msg_count out nocopy NUMBER
271 , x_msg_data out nocopy VARCHAR2
272 )
273
274 as
275 ddx_email_activity_tbl iem_spv_monitoring_pvt.email_activity_tbl;
276 ddindx binary_integer; indx binary_integer;
277 begin
278
279 -- copy data to the local IN or IN-OUT args, if any
280
281
282
283
284
285
286
287 -- here's the delegated call to the old PL/SQL routine
288 iem_spv_monitoring_pvt.get_email_activity(p_api_version_number,
289 p_init_msg_list,
290 p_commit,
291 ddx_email_activity_tbl,
292 x_return_status,
293 x_msg_count,
294 x_msg_data);
295
296 -- copy data back from the local variables to OUT or IN-OUT args, if any
297
298
299
300 iem_spv_monitoring_pvt_w.rosetta_table_copy_out_p1(ddx_email_activity_tbl, p3_a0
301 , p3_a1
302 , p3_a2
303 , p3_a3
304 , p3_a4
305 , p3_a5
306 , p3_a6
307 , p3_a7
308 , p3_a8
309 , p3_a9
310 , p3_a10
311 , p3_a11
312 , p3_a12
313 );
314
315
316
317 end;
318
319 procedure get_agent_activity(p_api_version_number NUMBER
320 , p_init_msg_list VARCHAR2
321 , p_commit VARCHAR2
322 , p_resource_role NUMBER
323 , p_resource_name VARCHAR2
324 , p5_a0 out nocopy JTF_NUMBER_TABLE
325 , p5_a1 out nocopy JTF_NUMBER_TABLE
326 , p5_a2 out nocopy JTF_VARCHAR2_TABLE_800
327 , p5_a3 out nocopy JTF_NUMBER_TABLE
328 , p5_a4 out nocopy JTF_NUMBER_TABLE
329 , p5_a5 out nocopy JTF_NUMBER_TABLE
330 , p5_a6 out nocopy JTF_NUMBER_TABLE
331 , p5_a7 out nocopy JTF_VARCHAR2_TABLE_500
332 , p5_a8 out nocopy JTF_NUMBER_TABLE
333 , p5_a9 out nocopy JTF_VARCHAR2_TABLE_100
334 , p5_a10 out nocopy JTF_VARCHAR2_TABLE_100
335 , x_return_status out nocopy VARCHAR2
336 , x_msg_count out nocopy NUMBER
337 , x_msg_data out nocopy VARCHAR2
338 )
339
340 as
341 ddx_agent_activity_tbl iem_spv_monitoring_pvt.agent_activity_tbl;
342 ddindx binary_integer; indx binary_integer;
343 begin
344
345 -- copy data to the local IN or IN-OUT args, if any
346
347
348
349
350
351
352
353
354
355 -- here's the delegated call to the old PL/SQL routine
356 iem_spv_monitoring_pvt.get_agent_activity(p_api_version_number,
357 p_init_msg_list,
358 p_commit,
359 p_resource_role,
360 p_resource_name,
361 ddx_agent_activity_tbl,
362 x_return_status,
363 x_msg_count,
364 x_msg_data);
365
366 -- copy data back from the local variables to OUT or IN-OUT args, if any
367
368
369
370
371
372 iem_spv_monitoring_pvt_w.rosetta_table_copy_out_p3(ddx_agent_activity_tbl, p5_a0
373 , p5_a1
374 , p5_a2
375 , p5_a3
376 , p5_a4
377 , p5_a5
378 , p5_a6
379 , p5_a7
380 , p5_a8
381 , p5_a9
382 , p5_a10
383 );
384
385
386
387 end;
388
389 end iem_spv_monitoring_pvt_w;