[Home] [Help]
PACKAGE BODY: APPS.IEM_SEARCHMESSAGE_PVT_W
Source
1 package body iem_searchmessage_pvt_w as
2 /* $Header: iemsearchb.pls 120.0 2005/06/02 14:10:31 appldev 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_searchmessage_pvt.message_rec_tbl, a0 JTF_NUMBER_TABLE
14 , a1 JTF_NUMBER_TABLE
15 , a2 JTF_VARCHAR2_TABLE_500
16 , a3 JTF_VARCHAR2_TABLE_2000
17 , a4 JTF_VARCHAR2_TABLE_2000
18 , a5 JTF_VARCHAR2_TABLE_100
19 ) as
20 ddindx binary_integer; indx binary_integer;
21 begin
22 if a0 is not null and a0.count > 0 then
23 if a0.count > 0 then
24 indx := a0.first;
25 ddindx := 1;
26 while true loop
27 t(ddindx).message_id := a0(indx);
28 t(ddindx).ih_media_item_id := a1(indx);
29 t(ddindx).from_str := a2(indx);
30 t(ddindx).to_str := a3(indx);
31 t(ddindx).subject := a4(indx);
32 t(ddindx).sent_date := a5(indx);
33 ddindx := ddindx+1;
34 if a0.last =indx
35 then exit;
36 end if;
37 indx := a0.next(indx);
38 end loop;
39 end if;
40 end if;
41 end rosetta_table_copy_in_p1;
42 procedure rosetta_table_copy_out_p1(t iem_searchmessage_pvt.message_rec_tbl, a0 out nocopy JTF_NUMBER_TABLE
43 , a1 out nocopy JTF_NUMBER_TABLE
44 , a2 out nocopy JTF_VARCHAR2_TABLE_500
45 , a3 out nocopy JTF_VARCHAR2_TABLE_2000
46 , a4 out nocopy JTF_VARCHAR2_TABLE_2000
47 , a5 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_500();
55 a3 := JTF_VARCHAR2_TABLE_2000();
56 a4 := JTF_VARCHAR2_TABLE_2000();
57 a5 := JTF_VARCHAR2_TABLE_100();
58 else
59 a0 := JTF_NUMBER_TABLE();
60 a1 := JTF_NUMBER_TABLE();
61 a2 := JTF_VARCHAR2_TABLE_500();
62 a3 := JTF_VARCHAR2_TABLE_2000();
63 a4 := JTF_VARCHAR2_TABLE_2000();
64 a5 := JTF_VARCHAR2_TABLE_100();
65 if t.count > 0 then
66 a0.extend(t.count);
67 a1.extend(t.count);
68 a2.extend(t.count);
69 a3.extend(t.count);
70 a4.extend(t.count);
71 a5.extend(t.count);
72 ddindx := t.first;
73 indx := 1;
74 while true loop
75 a0(indx) := t(ddindx).message_id;
76 a1(indx) := t(ddindx).ih_media_item_id;
77 a2(indx) := t(ddindx).from_str;
78 a3(indx) := t(ddindx).to_str;
79 a4(indx) := t(ddindx).subject;
80 a5(indx) := t(ddindx).sent_date;
81 indx := indx+1;
82 if t.last =ddindx
83 then exit;
84 end if;
85 ddindx := t.next(ddindx);
86 end loop;
87 end if;
88 end if;
89 end rosetta_table_copy_out_p1;
90
91 procedure searchmessages(p_api_version_number NUMBER
92 , p_init_msg_list VARCHAR2
93 , p_commit VARCHAR2
94 , p_email_account_id NUMBER
95 , p_resource_id NUMBER
96 , p_email_queue VARCHAR2
97 , p_sent_date_from VARCHAR2
98 , p_sent_date_to VARCHAR2
99 , p_received_date_from date
100 , p_received_date_to date
101 , p_from_str VARCHAR2
102 , p_recepients VARCHAR2
103 , p_cc_flag VARCHAR2
104 , p_subject VARCHAR2
105 , p_message_body VARCHAR2
106 , p_customer_id NUMBER
107 , p_classification VARCHAR2
108 , p_resolved_agent VARCHAR2
109 , p_resolved_group VARCHAR2
110 , p19_a0 out nocopy JTF_NUMBER_TABLE
111 , p19_a1 out nocopy JTF_NUMBER_TABLE
112 , p19_a2 out nocopy JTF_VARCHAR2_TABLE_500
113 , p19_a3 out nocopy JTF_VARCHAR2_TABLE_2000
114 , p19_a4 out nocopy JTF_VARCHAR2_TABLE_2000
115 , p19_a5 out nocopy JTF_VARCHAR2_TABLE_100
116 , x_return_status out nocopy VARCHAR2
117 , x_msg_count out nocopy NUMBER
118 , x_msg_data out nocopy VARCHAR2
119 )
120
121 as
122 ddp_received_date_from date;
123 ddp_received_date_to date;
124 ddx_message_tbl iem_searchmessage_pvt.message_rec_tbl;
125 ddindx binary_integer; indx binary_integer;
126 begin
127
128 -- copy data to the local IN or IN-OUT args, if any
129
130
131
132
133
134
135
136
137 ddp_received_date_from := rosetta_g_miss_date_in_map(p_received_date_from);
138
139 ddp_received_date_to := rosetta_g_miss_date_in_map(p_received_date_to);
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154 -- here's the delegated call to the old PL/SQL routine
155 iem_searchmessage_pvt.searchmessages(p_api_version_number,
156 p_init_msg_list,
157 p_commit,
158 p_email_account_id,
159 p_resource_id,
160 p_email_queue,
161 p_sent_date_from,
162 p_sent_date_to,
163 ddp_received_date_from,
164 ddp_received_date_to,
165 p_from_str,
166 p_recepients,
167 p_cc_flag,
168 p_subject,
169 p_message_body,
170 p_customer_id,
171 p_classification,
172 p_resolved_agent,
173 p_resolved_group,
174 ddx_message_tbl,
175 x_return_status,
176 x_msg_count,
177 x_msg_data);
178
179 -- copy data back from the local variables to OUT or IN-OUT args, if any
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199 iem_searchmessage_pvt_w.rosetta_table_copy_out_p1(ddx_message_tbl, p19_a0
200 , p19_a1
201 , p19_a2
202 , p19_a3
203 , p19_a4
204 , p19_a5
205 );
206
207
208
209 end;
210
211 end iem_searchmessage_pvt_w;