[Home] [Help]
PACKAGE BODY: APPS.IEM_AGENT_INBOX_MGMT_PVT_W
Source
1 package body iem_agent_inbox_mgmt_pvt_w as
2 /* $Header: IEMPAIMB.pls 120.1 2006/02/14 15:17 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_agent_inbox_mgmt_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_400
23 , a10 JTF_NUMBER_TABLE
24 , a11 JTF_NUMBER_TABLE
25 , a12 JTF_DATE_TABLE
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).message_id := a0(indx);
35 t(ddindx).email_account_id := a1(indx);
36 t(ddindx).sender_name := a2(indx);
37 t(ddindx).subject := a3(indx);
38 t(ddindx).classification_name := a4(indx);
39 t(ddindx).customer_name := a5(indx);
40 t(ddindx).sent_date := a6(indx);
41 t(ddindx).message_uid := a7(indx);
42 t(ddindx).agent_account_id := a8(indx);
43 t(ddindx).resource_name := a9(indx);
44 t(ddindx).rt_media_item_id := a10(indx);
45 t(ddindx).agent_id := a11(indx);
46 t(ddindx).real_received_date := rosetta_g_miss_date_in_map(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_agent_inbox_mgmt_pvt.message_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_VARCHAR2_TABLE_2000
60 , a4 out nocopy JTF_VARCHAR2_TABLE_100
61 , a5 out nocopy JTF_VARCHAR2_TABLE_400
62 , a6 out nocopy JTF_VARCHAR2_TABLE_500
63 , a7 out nocopy JTF_NUMBER_TABLE
64 , a8 out nocopy JTF_NUMBER_TABLE
65 , a9 out nocopy JTF_VARCHAR2_TABLE_400
66 , a10 out nocopy JTF_NUMBER_TABLE
67 , a11 out nocopy JTF_NUMBER_TABLE
68 , a12 out nocopy JTF_DATE_TABLE
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_VARCHAR2_TABLE_2000();
77 a4 := JTF_VARCHAR2_TABLE_100();
78 a5 := JTF_VARCHAR2_TABLE_400();
79 a6 := JTF_VARCHAR2_TABLE_500();
80 a7 := JTF_NUMBER_TABLE();
81 a8 := JTF_NUMBER_TABLE();
82 a9 := JTF_VARCHAR2_TABLE_400();
83 a10 := JTF_NUMBER_TABLE();
84 a11 := JTF_NUMBER_TABLE();
85 a12 := JTF_DATE_TABLE();
86 else
87 a0 := JTF_NUMBER_TABLE();
88 a1 := JTF_NUMBER_TABLE();
89 a2 := JTF_VARCHAR2_TABLE_300();
90 a3 := JTF_VARCHAR2_TABLE_2000();
91 a4 := JTF_VARCHAR2_TABLE_100();
92 a5 := JTF_VARCHAR2_TABLE_400();
93 a6 := JTF_VARCHAR2_TABLE_500();
94 a7 := JTF_NUMBER_TABLE();
95 a8 := JTF_NUMBER_TABLE();
96 a9 := JTF_VARCHAR2_TABLE_400();
97 a10 := JTF_NUMBER_TABLE();
98 a11 := JTF_NUMBER_TABLE();
99 a12 := JTF_DATE_TABLE();
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).message_id;
118 a1(indx) := t(ddindx).email_account_id;
119 a2(indx) := t(ddindx).sender_name;
120 a3(indx) := t(ddindx).subject;
121 a4(indx) := t(ddindx).classification_name;
122 a5(indx) := t(ddindx).customer_name;
123 a6(indx) := t(ddindx).sent_date;
124 a7(indx) := t(ddindx).message_uid;
125 a8(indx) := t(ddindx).agent_account_id;
126 a9(indx) := t(ddindx).resource_name;
127 a10(indx) := t(ddindx).rt_media_item_id;
128 a11(indx) := t(ddindx).agent_id;
129 a12(indx) := t(ddindx).real_received_date;
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_agent_inbox_mgmt_pvt.temp_message_tbl, a0 JTF_NUMBER_TABLE
141 , a1 JTF_NUMBER_TABLE
142 , a2 JTF_VARCHAR2_TABLE_300
143 , a3 JTF_VARCHAR2_TABLE_2000
144 , a4 JTF_VARCHAR2_TABLE_100
145 , a5 JTF_VARCHAR2_TABLE_400
146 , a6 JTF_VARCHAR2_TABLE_500
147 , a7 JTF_VARCHAR2_TABLE_100
148 , a8 JTF_NUMBER_TABLE
149 , a9 JTF_VARCHAR2_TABLE_400
150 , a10 JTF_NUMBER_TABLE
151 , a11 JTF_NUMBER_TABLE
152 ) as
153 ddindx binary_integer; indx binary_integer;
154 begin
155 if a0 is not null and a0.count > 0 then
156 if a0.count > 0 then
157 indx := a0.first;
158 ddindx := 1;
159 while true loop
160 t(ddindx).message_id := a0(indx);
161 t(ddindx).email_account_id := a1(indx);
162 t(ddindx).sender_name := a2(indx);
163 t(ddindx).subject := a3(indx);
164 t(ddindx).classification_name := a4(indx);
165 t(ddindx).customer_name := a5(indx);
166 t(ddindx).sent_date := a6(indx);
167 t(ddindx).real_sent_date := a7(indx);
168 t(ddindx).message_uid := a8(indx);
169 t(ddindx).resource_name := a9(indx);
170 t(ddindx).rt_media_item_id := a10(indx);
171 t(ddindx).agent_id := a11(indx);
172 ddindx := ddindx+1;
173 if a0.last =indx
174 then exit;
175 end if;
176 indx := a0.next(indx);
177 end loop;
178 end if;
179 end if;
180 end rosetta_table_copy_in_p3;
181 procedure rosetta_table_copy_out_p3(t iem_agent_inbox_mgmt_pvt.temp_message_tbl, a0 out nocopy JTF_NUMBER_TABLE
182 , a1 out nocopy JTF_NUMBER_TABLE
183 , a2 out nocopy JTF_VARCHAR2_TABLE_300
184 , a3 out nocopy JTF_VARCHAR2_TABLE_2000
185 , a4 out nocopy JTF_VARCHAR2_TABLE_100
186 , a5 out nocopy JTF_VARCHAR2_TABLE_400
187 , a6 out nocopy JTF_VARCHAR2_TABLE_500
188 , a7 out nocopy JTF_VARCHAR2_TABLE_100
189 , a8 out nocopy JTF_NUMBER_TABLE
190 , a9 out nocopy JTF_VARCHAR2_TABLE_400
191 , a10 out nocopy JTF_NUMBER_TABLE
192 , a11 out nocopy JTF_NUMBER_TABLE
193 ) as
194 ddindx binary_integer; indx binary_integer;
195 begin
196 if t is null or t.count = 0 then
197 a0 := JTF_NUMBER_TABLE();
198 a1 := JTF_NUMBER_TABLE();
199 a2 := JTF_VARCHAR2_TABLE_300();
200 a3 := JTF_VARCHAR2_TABLE_2000();
201 a4 := JTF_VARCHAR2_TABLE_100();
202 a5 := JTF_VARCHAR2_TABLE_400();
203 a6 := JTF_VARCHAR2_TABLE_500();
204 a7 := JTF_VARCHAR2_TABLE_100();
205 a8 := JTF_NUMBER_TABLE();
206 a9 := JTF_VARCHAR2_TABLE_400();
207 a10 := JTF_NUMBER_TABLE();
208 a11 := JTF_NUMBER_TABLE();
209 else
210 a0 := JTF_NUMBER_TABLE();
211 a1 := JTF_NUMBER_TABLE();
212 a2 := JTF_VARCHAR2_TABLE_300();
213 a3 := JTF_VARCHAR2_TABLE_2000();
214 a4 := JTF_VARCHAR2_TABLE_100();
215 a5 := JTF_VARCHAR2_TABLE_400();
216 a6 := JTF_VARCHAR2_TABLE_500();
217 a7 := JTF_VARCHAR2_TABLE_100();
218 a8 := JTF_NUMBER_TABLE();
219 a9 := JTF_VARCHAR2_TABLE_400();
220 a10 := JTF_NUMBER_TABLE();
221 a11 := JTF_NUMBER_TABLE();
222 if t.count > 0 then
223 a0.extend(t.count);
224 a1.extend(t.count);
225 a2.extend(t.count);
226 a3.extend(t.count);
227 a4.extend(t.count);
228 a5.extend(t.count);
229 a6.extend(t.count);
230 a7.extend(t.count);
231 a8.extend(t.count);
232 a9.extend(t.count);
233 a10.extend(t.count);
234 a11.extend(t.count);
235 ddindx := t.first;
236 indx := 1;
237 while true loop
238 a0(indx) := t(ddindx).message_id;
239 a1(indx) := t(ddindx).email_account_id;
240 a2(indx) := t(ddindx).sender_name;
241 a3(indx) := t(ddindx).subject;
242 a4(indx) := t(ddindx).classification_name;
243 a5(indx) := t(ddindx).customer_name;
244 a6(indx) := t(ddindx).sent_date;
245 a7(indx) := t(ddindx).real_sent_date;
246 a8(indx) := t(ddindx).message_uid;
247 a9(indx) := t(ddindx).resource_name;
248 a10(indx) := t(ddindx).rt_media_item_id;
249 a11(indx) := t(ddindx).agent_id;
250 indx := indx+1;
251 if t.last =ddindx
252 then exit;
253 end if;
254 ddindx := t.next(ddindx);
255 end loop;
256 end if;
257 end if;
258 end rosetta_table_copy_out_p3;
259
260 procedure rosetta_table_copy_in_p5(t out nocopy iem_agent_inbox_mgmt_pvt.resource_count_tbl, a0 JTF_NUMBER_TABLE
261 , a1 JTF_VARCHAR2_TABLE_200
262 , a2 JTF_NUMBER_TABLE
263 , a3 JTF_VARCHAR2_TABLE_500
264 ) as
265 ddindx binary_integer; indx binary_integer;
266 begin
267 if a0 is not null and a0.count > 0 then
268 if a0.count > 0 then
269 indx := a0.first;
270 ddindx := 1;
271 while true loop
272 t(ddindx).resource_id := a0(indx);
273 t(ddindx).resource_name := a1(indx);
274 t(ddindx).email_count := a2(indx);
275 t(ddindx).last_login_time := a3(indx);
276 ddindx := ddindx+1;
277 if a0.last =indx
278 then exit;
279 end if;
280 indx := a0.next(indx);
281 end loop;
282 end if;
283 end if;
284 end rosetta_table_copy_in_p5;
285 procedure rosetta_table_copy_out_p5(t iem_agent_inbox_mgmt_pvt.resource_count_tbl, a0 out nocopy JTF_NUMBER_TABLE
286 , a1 out nocopy JTF_VARCHAR2_TABLE_200
287 , a2 out nocopy JTF_NUMBER_TABLE
288 , a3 out nocopy JTF_VARCHAR2_TABLE_500
289 ) as
290 ddindx binary_integer; indx binary_integer;
291 begin
292 if t is null or t.count = 0 then
293 a0 := JTF_NUMBER_TABLE();
294 a1 := JTF_VARCHAR2_TABLE_200();
295 a2 := JTF_NUMBER_TABLE();
296 a3 := JTF_VARCHAR2_TABLE_500();
297 else
298 a0 := JTF_NUMBER_TABLE();
299 a1 := JTF_VARCHAR2_TABLE_200();
300 a2 := JTF_NUMBER_TABLE();
301 a3 := JTF_VARCHAR2_TABLE_500();
302 if t.count > 0 then
303 a0.extend(t.count);
304 a1.extend(t.count);
305 a2.extend(t.count);
306 a3.extend(t.count);
307 ddindx := t.first;
308 indx := 1;
309 while true loop
310 a0(indx) := t(ddindx).resource_id;
311 a1(indx) := t(ddindx).resource_name;
312 a2(indx) := t(ddindx).email_count;
313 a3(indx) := t(ddindx).last_login_time;
314 indx := indx+1;
315 if t.last =ddindx
316 then exit;
317 end if;
318 ddindx := t.next(ddindx);
319 end loop;
320 end if;
321 end if;
322 end rosetta_table_copy_out_p5;
323
324 procedure search_messages_in_inbox(p_api_version_number NUMBER
325 , p_init_msg_list VARCHAR2
326 , p_commit VARCHAR2
327 , p_email_account_id NUMBER
328 , p_classification_id NUMBER
329 , p_subject VARCHAR2
330 , p_customer_name VARCHAR2
331 , p_sender_name VARCHAR2
332 , p_sent_date_from VARCHAR2
333 , p_sent_date_to VARCHAR2
334 , p_sent_date_format VARCHAR2
335 , p_resource_name VARCHAR2
336 , p_resource_id NUMBER
337 , p_page_flag NUMBER
338 , p_sort_column NUMBER
339 , p_sort_state VARCHAR2
340 , p16_a0 out nocopy JTF_NUMBER_TABLE
341 , p16_a1 out nocopy JTF_NUMBER_TABLE
342 , p16_a2 out nocopy JTF_VARCHAR2_TABLE_300
343 , p16_a3 out nocopy JTF_VARCHAR2_TABLE_2000
344 , p16_a4 out nocopy JTF_VARCHAR2_TABLE_100
345 , p16_a5 out nocopy JTF_VARCHAR2_TABLE_400
346 , p16_a6 out nocopy JTF_VARCHAR2_TABLE_500
347 , p16_a7 out nocopy JTF_NUMBER_TABLE
348 , p16_a8 out nocopy JTF_NUMBER_TABLE
349 , p16_a9 out nocopy JTF_VARCHAR2_TABLE_400
350 , p16_a10 out nocopy JTF_NUMBER_TABLE
351 , p16_a11 out nocopy JTF_NUMBER_TABLE
352 , p16_a12 out nocopy JTF_DATE_TABLE
353 , x_return_status out nocopy VARCHAR2
354 , x_msg_count out nocopy NUMBER
355 , x_msg_data out nocopy VARCHAR2
356 )
357
358 as
359 ddx_message_tbl iem_agent_inbox_mgmt_pvt.message_tbl;
360 ddindx binary_integer; indx binary_integer;
361 begin
362
363 -- copy data to the local IN or IN-OUT args, if any
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384 -- here's the delegated call to the old PL/SQL routine
385 iem_agent_inbox_mgmt_pvt.search_messages_in_inbox(p_api_version_number,
386 p_init_msg_list,
387 p_commit,
388 p_email_account_id,
389 p_classification_id,
390 p_subject,
391 p_customer_name,
392 p_sender_name,
393 p_sent_date_from,
394 p_sent_date_to,
395 p_sent_date_format,
396 p_resource_name,
397 p_resource_id,
398 p_page_flag,
399 p_sort_column,
400 p_sort_state,
401 ddx_message_tbl,
402 x_return_status,
403 x_msg_count,
404 x_msg_data);
405
406 -- copy data back from the local variables to OUT or IN-OUT args, if any
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423 iem_agent_inbox_mgmt_pvt_w.rosetta_table_copy_out_p1(ddx_message_tbl, p16_a0
424 , p16_a1
425 , p16_a2
426 , p16_a3
427 , p16_a4
428 , p16_a5
429 , p16_a6
430 , p16_a7
431 , p16_a8
432 , p16_a9
433 , p16_a10
434 , p16_a11
435 , p16_a12
436 );
437
438
439
440 end;
441
442 procedure show_agent_list(p_api_version_number NUMBER
443 , p_init_msg_list VARCHAR2
444 , p_commit VARCHAR2
445 , p_email_account_id NUMBER
446 , p_sort_column NUMBER
447 , p_sort_state VARCHAR2
448 , p_resource_role NUMBER
449 , p_resource_name VARCHAR2
450 , p_transferrer_id NUMBER
451 , p9_a0 out nocopy JTF_NUMBER_TABLE
452 , p9_a1 out nocopy JTF_VARCHAR2_TABLE_200
453 , p9_a2 out nocopy JTF_NUMBER_TABLE
454 , p9_a3 out nocopy JTF_VARCHAR2_TABLE_500
455 , x_return_status out nocopy VARCHAR2
456 , x_msg_count out nocopy NUMBER
457 , x_msg_data out nocopy VARCHAR2
458 )
459
460 as
461 ddx_resource_count iem_agent_inbox_mgmt_pvt.resource_count_tbl;
462 ddindx binary_integer; indx binary_integer;
463 begin
464
465 -- copy data to the local IN or IN-OUT args, if any
466
467
468
469
470
471
472
473
474
475
476
477
478
479 -- here's the delegated call to the old PL/SQL routine
480 iem_agent_inbox_mgmt_pvt.show_agent_list(p_api_version_number,
481 p_init_msg_list,
482 p_commit,
483 p_email_account_id,
484 p_sort_column,
485 p_sort_state,
486 p_resource_role,
487 p_resource_name,
488 p_transferrer_id,
489 ddx_resource_count,
490 x_return_status,
491 x_msg_count,
492 x_msg_data);
493
494 -- copy data back from the local variables to OUT or IN-OUT args, if any
495
496
497
498
499
500
501
502
503
504 iem_agent_inbox_mgmt_pvt_w.rosetta_table_copy_out_p5(ddx_resource_count, p9_a0
505 , p9_a1
506 , p9_a2
507 , p9_a3
508 );
509
510
511
512 end;
513
514 end iem_agent_inbox_mgmt_pvt_w;