[Home] [Help]
PACKAGE BODY: APPS.IEM_QUEUE_MANAGEMENT_PVT_W
Source
1 package body iem_queue_management_pvt_w as
2 /* $Header: IEMPQUMB.pls 120.1 2006/02/13 14:33 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_queue_management_pvt.message_tbl, a0 JTF_NUMBER_TABLE
14 , a1 JTF_NUMBER_TABLE
15 , a2 JTF_VARCHAR2_TABLE_300
16 , a3 JTF_VARCHAR2_TABLE_2000
17 , a4 JTF_VARCHAR2_TABLE_100
18 , a5 JTF_VARCHAR2_TABLE_400
19 , a6 JTF_VARCHAR2_TABLE_500
20 , a7 JTF_NUMBER_TABLE
21 , a8 JTF_NUMBER_TABLE
22 , a9 JTF_VARCHAR2_TABLE_100
23 , a10 JTF_DATE_TABLE
24 ) as
25 ddindx binary_integer; indx binary_integer;
26 begin
27 if a0 is not null and a0.count > 0 then
28 if a0.count > 0 then
29 indx := a0.first;
30 ddindx := 1;
31 while true loop
32 t(ddindx).message_id := a0(indx);
33 t(ddindx).email_account_id := a1(indx);
34 t(ddindx).sender_name := a2(indx);
35 t(ddindx).subject := a3(indx);
36 t(ddindx).classification_name := a4(indx);
37 t(ddindx).customer_name := a5(indx);
38 t(ddindx).sent_date := a6(indx);
39 t(ddindx).message_uid := a7(indx);
40 t(ddindx).agent_account_id := a8(indx);
41 t(ddindx).group_name := a9(indx);
42 t(ddindx).real_received_date := rosetta_g_miss_date_in_map(a10(indx));
43 ddindx := ddindx+1;
44 if a0.last =indx
45 then exit;
46 end if;
47 indx := a0.next(indx);
48 end loop;
49 end if;
50 end if;
51 end rosetta_table_copy_in_p1;
52 procedure rosetta_table_copy_out_p1(t iem_queue_management_pvt.message_tbl, a0 out nocopy JTF_NUMBER_TABLE
53 , a1 out nocopy JTF_NUMBER_TABLE
54 , a2 out nocopy JTF_VARCHAR2_TABLE_300
55 , a3 out nocopy JTF_VARCHAR2_TABLE_2000
56 , a4 out nocopy JTF_VARCHAR2_TABLE_100
57 , a5 out nocopy JTF_VARCHAR2_TABLE_400
58 , a6 out nocopy JTF_VARCHAR2_TABLE_500
59 , a7 out nocopy JTF_NUMBER_TABLE
60 , a8 out nocopy JTF_NUMBER_TABLE
61 , a9 out nocopy JTF_VARCHAR2_TABLE_100
62 , a10 out nocopy JTF_DATE_TABLE
63 ) as
64 ddindx binary_integer; indx binary_integer;
65 begin
66 if t is null or t.count = 0 then
67 a0 := JTF_NUMBER_TABLE();
68 a1 := JTF_NUMBER_TABLE();
69 a2 := JTF_VARCHAR2_TABLE_300();
70 a3 := JTF_VARCHAR2_TABLE_2000();
71 a4 := JTF_VARCHAR2_TABLE_100();
72 a5 := JTF_VARCHAR2_TABLE_400();
73 a6 := JTF_VARCHAR2_TABLE_500();
74 a7 := JTF_NUMBER_TABLE();
75 a8 := JTF_NUMBER_TABLE();
76 a9 := JTF_VARCHAR2_TABLE_100();
77 a10 := JTF_DATE_TABLE();
78 else
79 a0 := JTF_NUMBER_TABLE();
80 a1 := JTF_NUMBER_TABLE();
81 a2 := JTF_VARCHAR2_TABLE_300();
82 a3 := JTF_VARCHAR2_TABLE_2000();
83 a4 := JTF_VARCHAR2_TABLE_100();
84 a5 := JTF_VARCHAR2_TABLE_400();
85 a6 := JTF_VARCHAR2_TABLE_500();
86 a7 := JTF_NUMBER_TABLE();
87 a8 := JTF_NUMBER_TABLE();
88 a9 := JTF_VARCHAR2_TABLE_100();
89 a10 := JTF_DATE_TABLE();
90 if t.count > 0 then
91 a0.extend(t.count);
92 a1.extend(t.count);
93 a2.extend(t.count);
94 a3.extend(t.count);
95 a4.extend(t.count);
96 a5.extend(t.count);
97 a6.extend(t.count);
98 a7.extend(t.count);
99 a8.extend(t.count);
100 a9.extend(t.count);
101 a10.extend(t.count);
102 ddindx := t.first;
103 indx := 1;
104 while true loop
105 a0(indx) := t(ddindx).message_id;
106 a1(indx) := t(ddindx).email_account_id;
107 a2(indx) := t(ddindx).sender_name;
108 a3(indx) := t(ddindx).subject;
109 a4(indx) := t(ddindx).classification_name;
110 a5(indx) := t(ddindx).customer_name;
111 a6(indx) := t(ddindx).sent_date;
112 a7(indx) := t(ddindx).message_uid;
113 a8(indx) := t(ddindx).agent_account_id;
114 a9(indx) := t(ddindx).group_name;
115 a10(indx) := t(ddindx).real_received_date;
116 indx := indx+1;
117 if t.last =ddindx
118 then exit;
119 end if;
120 ddindx := t.next(ddindx);
121 end loop;
122 end if;
123 end if;
124 end rosetta_table_copy_out_p1;
125
126 procedure rosetta_table_copy_in_p3(t out nocopy iem_queue_management_pvt.temp_message_tbl, a0 JTF_NUMBER_TABLE
127 , a1 JTF_NUMBER_TABLE
128 , a2 JTF_VARCHAR2_TABLE_300
129 , a3 JTF_VARCHAR2_TABLE_2000
130 , a4 JTF_VARCHAR2_TABLE_100
131 , a5 JTF_VARCHAR2_TABLE_400
132 , a6 JTF_VARCHAR2_TABLE_500
133 , a7 JTF_VARCHAR2_TABLE_100
134 , a8 JTF_NUMBER_TABLE
135 , a9 JTF_VARCHAR2_TABLE_100
136 ) as
137 ddindx binary_integer; indx binary_integer;
138 begin
139 if a0 is not null and a0.count > 0 then
140 if a0.count > 0 then
141 indx := a0.first;
142 ddindx := 1;
143 while true loop
144 t(ddindx).message_id := a0(indx);
145 t(ddindx).email_account_id := a1(indx);
146 t(ddindx).sender_name := a2(indx);
147 t(ddindx).subject := a3(indx);
148 t(ddindx).classification_name := a4(indx);
149 t(ddindx).customer_name := a5(indx);
150 t(ddindx).sent_date := a6(indx);
151 t(ddindx).real_sent_date := a7(indx);
152 t(ddindx).message_uid := a8(indx);
153 t(ddindx).group_name := a9(indx);
154 ddindx := ddindx+1;
155 if a0.last =indx
156 then exit;
157 end if;
158 indx := a0.next(indx);
159 end loop;
160 end if;
161 end if;
162 end rosetta_table_copy_in_p3;
163 procedure rosetta_table_copy_out_p3(t iem_queue_management_pvt.temp_message_tbl, a0 out nocopy JTF_NUMBER_TABLE
164 , a1 out nocopy JTF_NUMBER_TABLE
165 , a2 out nocopy JTF_VARCHAR2_TABLE_300
166 , a3 out nocopy JTF_VARCHAR2_TABLE_2000
167 , a4 out nocopy JTF_VARCHAR2_TABLE_100
168 , a5 out nocopy JTF_VARCHAR2_TABLE_400
169 , a6 out nocopy JTF_VARCHAR2_TABLE_500
170 , a7 out nocopy JTF_VARCHAR2_TABLE_100
171 , a8 out nocopy JTF_NUMBER_TABLE
172 , a9 out nocopy JTF_VARCHAR2_TABLE_100
173 ) as
174 ddindx binary_integer; indx binary_integer;
175 begin
176 if t is null or t.count = 0 then
177 a0 := JTF_NUMBER_TABLE();
178 a1 := JTF_NUMBER_TABLE();
179 a2 := JTF_VARCHAR2_TABLE_300();
180 a3 := JTF_VARCHAR2_TABLE_2000();
181 a4 := JTF_VARCHAR2_TABLE_100();
182 a5 := JTF_VARCHAR2_TABLE_400();
183 a6 := JTF_VARCHAR2_TABLE_500();
184 a7 := JTF_VARCHAR2_TABLE_100();
185 a8 := JTF_NUMBER_TABLE();
186 a9 := JTF_VARCHAR2_TABLE_100();
187 else
188 a0 := JTF_NUMBER_TABLE();
189 a1 := JTF_NUMBER_TABLE();
190 a2 := JTF_VARCHAR2_TABLE_300();
191 a3 := JTF_VARCHAR2_TABLE_2000();
192 a4 := JTF_VARCHAR2_TABLE_100();
193 a5 := JTF_VARCHAR2_TABLE_400();
194 a6 := JTF_VARCHAR2_TABLE_500();
195 a7 := JTF_VARCHAR2_TABLE_100();
196 a8 := JTF_NUMBER_TABLE();
197 a9 := JTF_VARCHAR2_TABLE_100();
198 if t.count > 0 then
199 a0.extend(t.count);
200 a1.extend(t.count);
201 a2.extend(t.count);
202 a3.extend(t.count);
203 a4.extend(t.count);
204 a5.extend(t.count);
205 a6.extend(t.count);
206 a7.extend(t.count);
207 a8.extend(t.count);
208 a9.extend(t.count);
209 ddindx := t.first;
210 indx := 1;
211 while true loop
212 a0(indx) := t(ddindx).message_id;
213 a1(indx) := t(ddindx).email_account_id;
214 a2(indx) := t(ddindx).sender_name;
215 a3(indx) := t(ddindx).subject;
216 a4(indx) := t(ddindx).classification_name;
217 a5(indx) := t(ddindx).customer_name;
218 a6(indx) := t(ddindx).sent_date;
219 a7(indx) := t(ddindx).real_sent_date;
220 a8(indx) := t(ddindx).message_uid;
221 a9(indx) := t(ddindx).group_name;
222 indx := indx+1;
223 if t.last =ddindx
224 then exit;
225 end if;
226 ddindx := t.next(ddindx);
227 end loop;
228 end if;
229 end if;
230 end rosetta_table_copy_out_p3;
231
232 procedure rosetta_table_copy_in_p5(t out nocopy iem_queue_management_pvt.resource_count_tbl, a0 JTF_NUMBER_TABLE
233 , a1 JTF_VARCHAR2_TABLE_200
234 , a2 JTF_NUMBER_TABLE
235 , a3 JTF_VARCHAR2_TABLE_500
236 ) as
237 ddindx binary_integer; indx binary_integer;
238 begin
239 if a0 is not null and a0.count > 0 then
240 if a0.count > 0 then
241 indx := a0.first;
242 ddindx := 1;
243 while true loop
244 t(ddindx).resource_id := a0(indx);
245 t(ddindx).resource_name := a1(indx);
246 t(ddindx).email_count := a2(indx);
247 t(ddindx).last_login_time := a3(indx);
248 ddindx := ddindx+1;
249 if a0.last =indx
250 then exit;
251 end if;
252 indx := a0.next(indx);
253 end loop;
254 end if;
255 end if;
256 end rosetta_table_copy_in_p5;
257 procedure rosetta_table_copy_out_p5(t iem_queue_management_pvt.resource_count_tbl, a0 out nocopy JTF_NUMBER_TABLE
258 , a1 out nocopy JTF_VARCHAR2_TABLE_200
259 , a2 out nocopy JTF_NUMBER_TABLE
260 , a3 out nocopy JTF_VARCHAR2_TABLE_500
261 ) as
262 ddindx binary_integer; indx binary_integer;
263 begin
264 if t is null or t.count = 0 then
265 a0 := JTF_NUMBER_TABLE();
266 a1 := JTF_VARCHAR2_TABLE_200();
267 a2 := JTF_NUMBER_TABLE();
268 a3 := JTF_VARCHAR2_TABLE_500();
269 else
270 a0 := JTF_NUMBER_TABLE();
271 a1 := JTF_VARCHAR2_TABLE_200();
272 a2 := JTF_NUMBER_TABLE();
273 a3 := JTF_VARCHAR2_TABLE_500();
274 if t.count > 0 then
275 a0.extend(t.count);
276 a1.extend(t.count);
277 a2.extend(t.count);
278 a3.extend(t.count);
279 ddindx := t.first;
280 indx := 1;
281 while true loop
282 a0(indx) := t(ddindx).resource_id;
283 a1(indx) := t(ddindx).resource_name;
284 a2(indx) := t(ddindx).email_count;
285 a3(indx) := t(ddindx).last_login_time;
286 indx := indx+1;
287 if t.last =ddindx
288 then exit;
289 end if;
290 ddindx := t.next(ddindx);
291 end loop;
292 end if;
293 end if;
294 end rosetta_table_copy_out_p5;
295
296 procedure rosetta_table_copy_in_p7(t out nocopy iem_queue_management_pvt.resource_group_count_tbl, a0 JTF_NUMBER_TABLE
297 , a1 JTF_VARCHAR2_TABLE_200
298 , a2 JTF_NUMBER_TABLE
299 , a3 JTF_NUMBER_TABLE
300 ) as
301 ddindx binary_integer; indx binary_integer;
302 begin
303 if a0 is not null and a0.count > 0 then
304 if a0.count > 0 then
305 indx := a0.first;
306 ddindx := 1;
307 while true loop
308 t(ddindx).group_id := a0(indx);
309 t(ddindx).group_name := a1(indx);
310 t(ddindx).agent_count := a2(indx);
311 t(ddindx).email_count := a3(indx);
312 ddindx := ddindx+1;
313 if a0.last =indx
314 then exit;
315 end if;
316 indx := a0.next(indx);
317 end loop;
318 end if;
319 end if;
320 end rosetta_table_copy_in_p7;
321 procedure rosetta_table_copy_out_p7(t iem_queue_management_pvt.resource_group_count_tbl, a0 out nocopy JTF_NUMBER_TABLE
322 , a1 out nocopy JTF_VARCHAR2_TABLE_200
323 , a2 out nocopy JTF_NUMBER_TABLE
324 , a3 out nocopy JTF_NUMBER_TABLE
325 ) as
326 ddindx binary_integer; indx binary_integer;
327 begin
328 if t is null or t.count = 0 then
329 a0 := JTF_NUMBER_TABLE();
330 a1 := JTF_VARCHAR2_TABLE_200();
331 a2 := JTF_NUMBER_TABLE();
332 a3 := JTF_NUMBER_TABLE();
333 else
334 a0 := JTF_NUMBER_TABLE();
335 a1 := JTF_VARCHAR2_TABLE_200();
336 a2 := JTF_NUMBER_TABLE();
337 a3 := JTF_NUMBER_TABLE();
338 if t.count > 0 then
339 a0.extend(t.count);
340 a1.extend(t.count);
341 a2.extend(t.count);
342 a3.extend(t.count);
343 ddindx := t.first;
344 indx := 1;
345 while true loop
349 a3(indx) := t(ddindx).email_count;
346 a0(indx) := t(ddindx).group_id;
347 a1(indx) := t(ddindx).group_name;
348 a2(indx) := t(ddindx).agent_count;
350 indx := indx+1;
351 if t.last =ddindx
352 then exit;
353 end if;
354 ddindx := t.next(ddindx);
355 end loop;
356 end if;
357 end if;
358 end rosetta_table_copy_out_p7;
359
360 procedure search_messages_in_queue(p_api_version_number NUMBER
361 , p_init_msg_list VARCHAR2
362 , p_commit VARCHAR2
363 , p_email_account_id NUMBER
364 , p_classification_id NUMBER
365 , p_subject VARCHAR2
366 , p_customer_name VARCHAR2
367 , p_sender_name VARCHAR2
368 , p_sent_date_from VARCHAR2
369 , p_sent_date_to VARCHAR2
370 , p_sent_date_format VARCHAR2
371 , p_group_id NUMBER
372 , p_sort_column NUMBER
373 , p_sort_state VARCHAR2
374 , p14_a0 out nocopy JTF_NUMBER_TABLE
375 , p14_a1 out nocopy JTF_NUMBER_TABLE
376 , p14_a2 out nocopy JTF_VARCHAR2_TABLE_300
377 , p14_a3 out nocopy JTF_VARCHAR2_TABLE_2000
378 , p14_a4 out nocopy JTF_VARCHAR2_TABLE_100
379 , p14_a5 out nocopy JTF_VARCHAR2_TABLE_400
380 , p14_a6 out nocopy JTF_VARCHAR2_TABLE_500
381 , p14_a7 out nocopy JTF_NUMBER_TABLE
382 , p14_a8 out nocopy JTF_NUMBER_TABLE
383 , p14_a9 out nocopy JTF_VARCHAR2_TABLE_100
384 , p14_a10 out nocopy JTF_DATE_TABLE
385 , x_return_status out nocopy VARCHAR2
386 , x_msg_count out nocopy NUMBER
387 , x_msg_data out nocopy VARCHAR2
388 )
389
390 as
391 ddx_message_tbl iem_queue_management_pvt.message_tbl;
392 ddindx binary_integer; indx binary_integer;
393 begin
394
395 -- copy data to the local IN or IN-OUT args, if any
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414 -- here's the delegated call to the old PL/SQL routine
415 iem_queue_management_pvt.search_messages_in_queue(p_api_version_number,
416 p_init_msg_list,
417 p_commit,
418 p_email_account_id,
419 p_classification_id,
420 p_subject,
421 p_customer_name,
422 p_sender_name,
423 p_sent_date_from,
424 p_sent_date_to,
425 p_sent_date_format,
426 p_group_id,
427 p_sort_column,
428 p_sort_state,
429 ddx_message_tbl,
430 x_return_status,
431 x_msg_count,
432 x_msg_data);
433
434 -- copy data back from the local variables to OUT or IN-OUT args, if any
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449 iem_queue_management_pvt_w.rosetta_table_copy_out_p1(ddx_message_tbl, p14_a0
450 , p14_a1
451 , p14_a2
452 , p14_a3
453 , p14_a4
454 , p14_a5
455 , p14_a6
456 , p14_a7
457 , p14_a8
458 , p14_a9
459 , p14_a10
460 );
461
462
463
464 end;
465
466 procedure show_agent_list(p_api_version_number NUMBER
467 , p_init_msg_list VARCHAR2
468 , p_commit VARCHAR2
469 , p_email_account_id NUMBER
470 , p_sort_column NUMBER
471 , p_sort_state VARCHAR2
472 , p6_a0 out nocopy JTF_NUMBER_TABLE
473 , p6_a1 out nocopy JTF_VARCHAR2_TABLE_200
474 , p6_a2 out nocopy JTF_NUMBER_TABLE
475 , p6_a3 out nocopy JTF_VARCHAR2_TABLE_500
476 , x_return_status out nocopy VARCHAR2
477 , x_msg_count out nocopy NUMBER
478 , x_msg_data out nocopy VARCHAR2
479 )
480
481 as
482 ddx_resource_count iem_queue_management_pvt.resource_count_tbl;
483 ddindx binary_integer; indx binary_integer;
484 begin
485
486 -- copy data to the local IN or IN-OUT args, if any
487
488
489
490
491
492
493
494
495
496
497 -- here's the delegated call to the old PL/SQL routine
498 iem_queue_management_pvt.show_agent_list(p_api_version_number,
499 p_init_msg_list,
500 p_commit,
501 p_email_account_id,
502 p_sort_column,
503 p_sort_state,
504 ddx_resource_count,
505 x_return_status,
506 x_msg_count,
507 x_msg_data);
508
509 -- copy data back from the local variables to OUT or IN-OUT args, if any
510
511
512
513
514
515
516 iem_queue_management_pvt_w.rosetta_table_copy_out_p5(ddx_resource_count, p6_a0
517 , p6_a1
518 , p6_a2
519 , p6_a3
520 );
521
522
523
524 end;
525
526 procedure show_resource_group_list(p_api_version_number NUMBER
527 , p_init_msg_list VARCHAR2
528 , p_commit VARCHAR2
529 , p_email_account_id NUMBER
530 , p_sort_column NUMBER
531 , p_sort_state VARCHAR2
532 , p6_a0 out nocopy JTF_NUMBER_TABLE
533 , p6_a1 out nocopy JTF_VARCHAR2_TABLE_200
534 , p6_a2 out nocopy JTF_NUMBER_TABLE
535 , p6_a3 out nocopy JTF_NUMBER_TABLE
536 , x_return_status out nocopy VARCHAR2
537 , x_msg_count out nocopy NUMBER
538 , x_msg_data out nocopy VARCHAR2
539 )
540
541 as
542 ddx_resource_group_count iem_queue_management_pvt.resource_group_count_tbl;
543 ddindx binary_integer; indx binary_integer;
544 begin
545
546 -- copy data to the local IN or IN-OUT args, if any
547
548
549
550
551
552
553
554
555
556
557 -- here's the delegated call to the old PL/SQL routine
558 iem_queue_management_pvt.show_resource_group_list(p_api_version_number,
559 p_init_msg_list,
560 p_commit,
561 p_email_account_id,
562 p_sort_column,
563 p_sort_state,
564 ddx_resource_group_count,
565 x_return_status,
566 x_msg_count,
567 x_msg_data);
568
569 -- copy data back from the local variables to OUT or IN-OUT args, if any
570
571
572
573
574
575
576 iem_queue_management_pvt_w.rosetta_table_copy_out_p7(ddx_resource_group_count, p6_a0
577 , p6_a1
578 , p6_a2
579 , p6_a3
580 );
581
582
583
584 end;
585
586 end iem_queue_management_pvt_w;