[Home] [Help]
PACKAGE BODY: APPS.IEM_KNOWLEDGEBASE_PUB_W
Source
1 package body iem_knowledgebase_pub_w as
2 /* $Header: IEMVKBSB.pls 115.10 2003/08/04 15:11:28 ukari ship $ */
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_p2(t out nocopy iem_knowledgebase_pub.emsgresp_tbl_type, a0 JTF_VARCHAR2_TABLE_100
14 , a1 JTF_VARCHAR2_TABLE_100
15 , a2 JTF_VARCHAR2_TABLE_100
16 , a3 JTF_VARCHAR2_TABLE_100
17 , a4 JTF_VARCHAR2_TABLE_100
18 , a5 JTF_VARCHAR2_TABLE_300
19 , a6 JTF_DATE_TABLE
20 ) as
21 ddindx binary_integer; indx binary_integer;
22 begin
23 if a0 is not null and a0.count > 0 then
24 if a0.count > 0 then
25 indx := a0.first;
26 ddindx := 1;
27 while true loop
28 t(ddindx).document_id := a0(indx);
29 t(ddindx).score := a1(indx);
30 t(ddindx).kb_repository_name := a2(indx);
31 t(ddindx).kb_category_name := a3(indx);
32 t(ddindx).document_title := a4(indx);
33 t(ddindx).url := a5(indx);
34 t(ddindx).document_last_modified_date := rosetta_g_miss_date_in_map(a6(indx));
35 ddindx := ddindx+1;
36 if a0.last =indx
37 then exit;
38 end if;
39 indx := a0.next(indx);
40 end loop;
41 end if;
42 end if;
43 end rosetta_table_copy_in_p2;
44 procedure rosetta_table_copy_out_p2(t iem_knowledgebase_pub.emsgresp_tbl_type, a0 out nocopy JTF_VARCHAR2_TABLE_100
45 , a1 out nocopy JTF_VARCHAR2_TABLE_100
46 , a2 out nocopy JTF_VARCHAR2_TABLE_100
47 , a3 out nocopy JTF_VARCHAR2_TABLE_100
48 , a4 out nocopy JTF_VARCHAR2_TABLE_100
49 , a5 out nocopy JTF_VARCHAR2_TABLE_300
50 , a6 out nocopy JTF_DATE_TABLE
51 ) as
52 ddindx binary_integer; indx binary_integer;
53 begin
54 if t is null or t.count = 0 then
55 a0 := JTF_VARCHAR2_TABLE_100();
56 a1 := JTF_VARCHAR2_TABLE_100();
57 a2 := JTF_VARCHAR2_TABLE_100();
58 a3 := JTF_VARCHAR2_TABLE_100();
59 a4 := JTF_VARCHAR2_TABLE_100();
60 a5 := JTF_VARCHAR2_TABLE_300();
61 a6 := JTF_DATE_TABLE();
62 else
63 a0 := JTF_VARCHAR2_TABLE_100();
64 a1 := JTF_VARCHAR2_TABLE_100();
65 a2 := JTF_VARCHAR2_TABLE_100();
66 a3 := JTF_VARCHAR2_TABLE_100();
67 a4 := JTF_VARCHAR2_TABLE_100();
68 a5 := JTF_VARCHAR2_TABLE_300();
69 a6 := JTF_DATE_TABLE();
70 if t.count > 0 then
71 a0.extend(t.count);
72 a1.extend(t.count);
73 a2.extend(t.count);
74 a3.extend(t.count);
75 a4.extend(t.count);
76 a5.extend(t.count);
77 a6.extend(t.count);
78 ddindx := t.first;
79 indx := 1;
80 while true loop
81 a0(indx) := t(ddindx).document_id;
82 a1(indx) := t(ddindx).score;
83 a2(indx) := t(ddindx).kb_repository_name;
84 a3(indx) := t(ddindx).kb_category_name;
85 a4(indx) := t(ddindx).document_title;
86 a5(indx) := t(ddindx).url;
87 a6(indx) := t(ddindx).document_last_modified_date;
88 indx := indx+1;
89 if t.last =ddindx
90 then exit;
91 end if;
92 ddindx := t.next(ddindx);
93 end loop;
94 end if;
95 end if;
96 end rosetta_table_copy_out_p2;
97
98 procedure rosetta_table_copy_in_p3(t out nocopy iem_knowledgebase_pub.kbcat_tbl_type, a0 JTF_VARCHAR2_TABLE_100
99 , a1 JTF_VARCHAR2_TABLE_100
100 , a2 JTF_NUMBER_TABLE
101 , a3 JTF_NUMBER_TABLE
102 , a4 JTF_NUMBER_TABLE
103 ) as
104 ddindx binary_integer; indx binary_integer;
105 begin
106 if a0 is not null and a0.count > 0 then
107 if a0.count > 0 then
108 indx := a0.first;
109 ddindx := 1;
110 while true loop
111 t(ddindx).display_name := a0(indx);
112 t(ddindx).is_repository := a1(indx);
113 t(ddindx).category_id := a2(indx);
114 t(ddindx).parent_cat_id := a3(indx);
115 t(ddindx).category_order := a4(indx);
116 ddindx := ddindx+1;
117 if a0.last =indx
118 then exit;
119 end if;
120 indx := a0.next(indx);
121 end loop;
122 end if;
123 end if;
124 end rosetta_table_copy_in_p3;
125 procedure rosetta_table_copy_out_p3(t iem_knowledgebase_pub.kbcat_tbl_type, a0 out nocopy JTF_VARCHAR2_TABLE_100
126 , a1 out nocopy JTF_VARCHAR2_TABLE_100
127 , a2 out nocopy JTF_NUMBER_TABLE
128 , a3 out nocopy JTF_NUMBER_TABLE
129 , a4 out nocopy JTF_NUMBER_TABLE
130 ) as
131 ddindx binary_integer; indx binary_integer;
132 begin
133 if t is null or t.count = 0 then
134 a0 := JTF_VARCHAR2_TABLE_100();
135 a1 := JTF_VARCHAR2_TABLE_100();
136 a2 := JTF_NUMBER_TABLE();
137 a3 := JTF_NUMBER_TABLE();
138 a4 := JTF_NUMBER_TABLE();
139 else
140 a0 := JTF_VARCHAR2_TABLE_100();
141 a1 := JTF_VARCHAR2_TABLE_100();
142 a2 := JTF_NUMBER_TABLE();
143 a3 := JTF_NUMBER_TABLE();
144 a4 := JTF_NUMBER_TABLE();
145 if t.count > 0 then
146 a0.extend(t.count);
147 a1.extend(t.count);
148 a2.extend(t.count);
149 a3.extend(t.count);
150 a4.extend(t.count);
151 ddindx := t.first;
152 indx := 1;
153 while true loop
154 a0(indx) := t(ddindx).display_name;
155 a1(indx) := t(ddindx).is_repository;
156 a2(indx) := t(ddindx).category_id;
157 a3(indx) := t(ddindx).parent_cat_id;
158 a4(indx) := t(ddindx).category_order;
159 indx := indx+1;
160 if t.last =ddindx
161 then exit;
162 end if;
163 ddindx := t.next(ddindx);
164 end loop;
165 end if;
166 end if;
167 end rosetta_table_copy_out_p3;
168
169 procedure get_suggresponse(p_api_version_number NUMBER
170 , p_init_msg_list VARCHAR2
171 , p_commit VARCHAR2
172 , p_email_account_id NUMBER
173 , p_message_id VARCHAR2
174 , p_classification_id NUMBER
175 , x_return_status out nocopy VARCHAR2
176 , x_msg_count out nocopy NUMBER
177 , x_msg_data out nocopy VARCHAR2
178 , p9_a0 out nocopy JTF_VARCHAR2_TABLE_100
179 , p9_a1 out nocopy JTF_VARCHAR2_TABLE_100
180 , p9_a2 out nocopy JTF_VARCHAR2_TABLE_100
181 , p9_a3 out nocopy JTF_VARCHAR2_TABLE_100
182 , p9_a4 out nocopy JTF_VARCHAR2_TABLE_100
183 , p9_a5 out nocopy JTF_VARCHAR2_TABLE_300
184 , p9_a6 out nocopy JTF_DATE_TABLE
185 )
186
187 as
188 ddx_email_suggresp_tbl iem_knowledgebase_pub.emsgresp_tbl_type;
189 ddindx binary_integer; indx binary_integer;
190 begin
191
192 -- copy data to the local IN or IN-OUT args, if any
193
194
195
196
197
198
199
200
201
202
203 -- here's the delegated call to the old PL/SQL routine
204 iem_knowledgebase_pub.get_suggresponse(p_api_version_number,
205 p_init_msg_list,
206 p_commit,
207 p_email_account_id,
208 p_message_id,
209 p_classification_id,
210 x_return_status,
211 x_msg_count,
212 x_msg_data,
213 ddx_email_suggresp_tbl);
214
215 -- copy data back from the local variables to OUT or IN-OUT args, if any
216
217
218
219
220
221
222
223
224
225 iem_knowledgebase_pub_w.rosetta_table_copy_out_p2(ddx_email_suggresp_tbl, p9_a0
226 , p9_a1
227 , p9_a2
228 , p9_a3
229 , p9_a4
230 , p9_a5
231 , p9_a6
232 );
233 end;
234
235 procedure get_kbcategories(p_api_version_number NUMBER
236 , p_init_msg_list VARCHAR2
237 , p_commit VARCHAR2
238 , p_email_account_id NUMBER
239 , p_level NUMBER
240 , x_return_status out nocopy VARCHAR2
241 , x_msg_count out nocopy NUMBER
242 , x_msg_data out nocopy VARCHAR2
243 , p8_a0 out nocopy JTF_VARCHAR2_TABLE_100
244 , p8_a1 out nocopy JTF_VARCHAR2_TABLE_100
245 , p8_a2 out nocopy JTF_NUMBER_TABLE
246 , p8_a3 out nocopy JTF_NUMBER_TABLE
247 , p8_a4 out nocopy JTF_NUMBER_TABLE
248 )
249
250 as
251 ddx_kb_cat_tbl iem_knowledgebase_pub.kbcat_tbl_type;
252 ddindx binary_integer; indx binary_integer;
253 begin
254
255 -- copy data to the local IN or IN-OUT args, if any
256
257
258
259
260
261
262
263
264
265 -- here's the delegated call to the old PL/SQL routine
266 iem_knowledgebase_pub.get_kbcategories(p_api_version_number,
267 p_init_msg_list,
268 p_commit,
269 p_email_account_id,
270 p_level,
271 x_return_status,
272 x_msg_count,
273 x_msg_data,
274 ddx_kb_cat_tbl);
275
276 -- copy data back from the local variables to OUT or IN-OUT args, if any
277
278
279
280
281
282
283
284
285 iem_knowledgebase_pub_w.rosetta_table_copy_out_p3(ddx_kb_cat_tbl, p8_a0
286 , p8_a1
287 , p8_a2
288 , p8_a3
289 , p8_a4
290 );
291 end;
292
293 procedure get_kb_suggresponse(p_api_version_number NUMBER
294 , p_init_msg_list VARCHAR2
295 , p_commit VARCHAR2
296 , p_email_account_id NUMBER
297 , p_message_id VARCHAR2
298 , p_classification_id NUMBER
299 , x_return_status out nocopy VARCHAR2
300 , x_msg_count out nocopy NUMBER
301 , x_msg_data out nocopy VARCHAR2
302 , p9_a0 out nocopy JTF_VARCHAR2_TABLE_100
303 , p9_a1 out nocopy JTF_VARCHAR2_TABLE_100
304 , p9_a2 out nocopy JTF_VARCHAR2_TABLE_100
305 , p9_a3 out nocopy JTF_VARCHAR2_TABLE_100
306 , p9_a4 out nocopy JTF_VARCHAR2_TABLE_100
307 , p9_a5 out nocopy JTF_VARCHAR2_TABLE_300
308 , p9_a6 out nocopy JTF_DATE_TABLE
309 )
310
311 as
312 ddx_email_suggresp_tbl iem_knowledgebase_pub.emsgresp_tbl_type;
313 ddindx binary_integer; indx binary_integer;
314 begin
315
316 -- copy data to the local IN or IN-OUT args, if any
317
318
319
320
321
322
323
324
325
326
327 -- here's the delegated call to the old PL/SQL routine
328 iem_knowledgebase_pub.get_kb_suggresponse(p_api_version_number,
329 p_init_msg_list,
330 p_commit,
331 p_email_account_id,
332 p_message_id,
333 p_classification_id,
334 x_return_status,
335 x_msg_count,
336 x_msg_data,
337 ddx_email_suggresp_tbl);
338
339 -- copy data back from the local variables to OUT or IN-OUT args, if any
340
341
342
343
344
345
346
347
348
349 iem_knowledgebase_pub_w.rosetta_table_copy_out_p2(ddx_email_suggresp_tbl, p9_a0
350 , p9_a1
351 , p9_a2
352 , p9_a3
353 , p9_a4
354 , p9_a5
355 , p9_a6
356 );
357 end;
358
359 procedure get_suggresponse_dtl(p_api_version_number NUMBER
360 , p_init_msg_list VARCHAR2
361 , p_commit VARCHAR2
362 , p_email_account_id NUMBER
363 , p_message_id VARCHAR2
364 , p_classification_id NUMBER
365 , x_return_status out nocopy VARCHAR2
366 , x_msg_count out nocopy NUMBER
367 , x_msg_data out nocopy VARCHAR2
368 , p9_a0 out nocopy JTF_VARCHAR2_TABLE_100
369 , p9_a1 out nocopy JTF_VARCHAR2_TABLE_100
370 , p9_a2 out nocopy JTF_VARCHAR2_TABLE_100
371 , p9_a3 out nocopy JTF_VARCHAR2_TABLE_100
372 , p9_a4 out nocopy JTF_VARCHAR2_TABLE_100
373 , p9_a5 out nocopy JTF_VARCHAR2_TABLE_300
374 , p9_a6 out nocopy JTF_DATE_TABLE
375 )
376
377 as
378 ddx_email_suggresp_tbl iem_knowledgebase_pub.emsgresp_tbl_type;
379 ddindx binary_integer; indx binary_integer;
380 begin
381
382 -- copy data to the local IN or IN-OUT args, if any
383
384
385
386
387
388
389
390
391
392
393 -- here's the delegated call to the old PL/SQL routine
394 iem_knowledgebase_pub.get_suggresponse_dtl(p_api_version_number,
395 p_init_msg_list,
396 p_commit,
397 p_email_account_id,
398 p_message_id,
399 p_classification_id,
400 x_return_status,
401 x_msg_count,
402 x_msg_data,
403 ddx_email_suggresp_tbl);
404
405 -- copy data back from the local variables to OUT or IN-OUT args, if any
406
407
408
409
410
411
412
413
414
415 iem_knowledgebase_pub_w.rosetta_table_copy_out_p2(ddx_email_suggresp_tbl, p9_a0
416 , p9_a1
417 , p9_a2
418 , p9_a3
419 , p9_a4
420 , p9_a5
421 , p9_a6
422 );
423 end;
424
425 procedure get_suggresponse_dtl(p_api_version_number NUMBER
426 , p_init_msg_list VARCHAR2
427 , p_commit VARCHAR2
428 , p_email_account_id NUMBER
429 , p_message_id VARCHAR2
430 , x_return_status out nocopy VARCHAR2
431 , x_msg_count out nocopy NUMBER
432 , x_msg_data out nocopy VARCHAR2
433 , p8_a0 out nocopy JTF_VARCHAR2_TABLE_100
434 , p8_a1 out nocopy JTF_VARCHAR2_TABLE_100
435 , p8_a2 out nocopy JTF_VARCHAR2_TABLE_100
436 , p8_a3 out nocopy JTF_VARCHAR2_TABLE_100
437 , p8_a4 out nocopy JTF_VARCHAR2_TABLE_100
438 , p8_a5 out nocopy JTF_VARCHAR2_TABLE_300
439 , p8_a6 out nocopy JTF_DATE_TABLE
440 )
441
442 as
443 ddx_email_suggresp_tbl iem_knowledgebase_pub.emsgresp_tbl_type;
444 ddindx binary_integer; indx binary_integer;
445 begin
446
447 -- copy data to the local IN or IN-OUT args, if any
448
449
450
451
452
453
454
455
456
457 -- here's the delegated call to the old PL/SQL routine
458 iem_knowledgebase_pub.get_suggresponse_dtl(p_api_version_number,
459 p_init_msg_list,
460 p_commit,
461 p_email_account_id,
462 p_message_id,
463 x_return_status,
464 x_msg_count,
465 x_msg_data,
466 ddx_email_suggresp_tbl);
467
468 -- copy data back from the local variables to OUT or IN-OUT args, if any
469
470
471
472
473
474
475
476
477 iem_knowledgebase_pub_w.rosetta_table_copy_out_p2(ddx_email_suggresp_tbl, p8_a0
478 , p8_a1
479 , p8_a2
480 , p8_a3
481 , p8_a4
482 , p8_a5
483 , p8_a6
484 );
485 end;
486
487 end iem_knowledgebase_pub_w;