[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_SERVICE_PUB_W
Source
1 package body jtf_terr_service_pub_w as
2 /* $Header: jtfptsvwb.pls 120.2 2009/08/27 09:48:02 ppillai 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 get_winningterrmembers(p_api_version_number NUMBER
14 , p_init_msg_list VARCHAR2
15 , p2_a0 NUMBER
16 , p2_a1 NUMBER
17 , p2_a2 VARCHAR2
18 , p2_a3 NUMBER
19 , p2_a4 VARCHAR2
20 , p2_a5 VARCHAR2
21 , p2_a6 VARCHAR2
22 , p2_a7 VARCHAR2
23 , p2_a8 VARCHAR2
24 , p2_a9 VARCHAR2
25 , p2_a10 VARCHAR2
26 , p2_a11 NUMBER
27 , p2_a12 NUMBER
28 , p2_a13 NUMBER
29 , p2_a14 NUMBER
30 , p2_a15 VARCHAR2
31 , p2_a16 NUMBER
32 , p2_a17 NUMBER
33 , p2_a18 NUMBER
34 , p2_a19 NUMBER
35 , p2_a20 VARCHAR2
36 , p2_a21 NUMBER
37 , p2_a22 VARCHAR2
38 , p2_a23 VARCHAR2
39 , p2_a24 VARCHAR2
40 , p2_a25 VARCHAR2
41 , p2_a26 VARCHAR2
42 , p2_a27 VARCHAR2
43 , p2_a28 VARCHAR2
44 , p2_a29 VARCHAR2
45 , p2_a30 VARCHAR2
46 , p2_a31 VARCHAR2
47 , p2_a32 VARCHAR2
48 , p2_a33 VARCHAR2
49 , p2_a34 VARCHAR2
50 , p2_a35 VARCHAR2
51 , p2_a36 VARCHAR2
52 , p2_a37 NUMBER
53 , p2_a38 NUMBER
54 , p2_a39 NUMBER
55 , p2_a40 NUMBER
56 , p2_a41 NUMBER
57 , p2_a42 NUMBER
58 , p2_a43 NUMBER
59 , p2_a44 NUMBER
60 , p2_a45 NUMBER
61 , p2_a46 NUMBER
62 , p2_a47 NUMBER
63 , p2_a48 VARCHAR2
64 , p2_a49 VARCHAR2
65 , p2_a50 VARCHAR2
66 , p2_a51 VARCHAR2
67 , p2_a52 VARCHAR2
68 , p2_a53 NUMBER
69 , p2_a54 VARCHAR2
70 , p2_a55 VARCHAR2
71 , p_resource_type VARCHAR2
72 , p_role VARCHAR2
73 , p_plan_start_date date
74 , p_plan_end_date date
75 , x_return_status out nocopy VARCHAR2
76 , x_msg_count out nocopy NUMBER
77 , x_msg_data out nocopy VARCHAR2
78 , p10_a0 out nocopy JTF_NUMBER_TABLE
79 , p10_a1 out nocopy JTF_NUMBER_TABLE
80 , p10_a2 out nocopy JTF_VARCHAR2_TABLE_100
81 , p10_a3 out nocopy JTF_NUMBER_TABLE
82 , p10_a4 out nocopy JTF_VARCHAR2_TABLE_100
83 , p10_a5 out nocopy JTF_DATE_TABLE
84 , p10_a6 out nocopy JTF_DATE_TABLE
85 , p10_a7 out nocopy JTF_VARCHAR2_TABLE_100
86 , p10_a8 out nocopy JTF_VARCHAR2_TABLE_100
87 , p10_a9 out nocopy JTF_NUMBER_TABLE
88 , p10_a10 out nocopy JTF_VARCHAR2_TABLE_100
89 , p10_a11 out nocopy JTF_NUMBER_TABLE
90 )
91
92 as
93 ddp_terrservreq_rec jtf_territory_pub.jtf_serv_req_rec_type;
94 ddp_plan_start_date date;
95 ddp_plan_end_date date;
96 ddx_terrresource_tbl jtf_territory_pub.winningterrmember_tbl_type;
97 ddindx binary_integer; indx binary_integer;
98 begin
99
100 -- copy data to the local IN or IN-OUT args, if any
101
102
103 ddp_terrservreq_rec.service_request_id := p2_a0;
104 ddp_terrservreq_rec.party_id := p2_a1;
105 ddp_terrservreq_rec.country := p2_a2;
106 ddp_terrservreq_rec.party_site_id := p2_a3;
107 ddp_terrservreq_rec.city := p2_a4;
108 ddp_terrservreq_rec.postal_code := p2_a5;
109 ddp_terrservreq_rec.state := p2_a6;
110 ddp_terrservreq_rec.area_code := p2_a7;
111 ddp_terrservreq_rec.county := p2_a8;
112 ddp_terrservreq_rec.comp_name_range := p2_a9;
113 ddp_terrservreq_rec.province := p2_a10;
114 ddp_terrservreq_rec.num_of_employees := p2_a11;
115 ddp_terrservreq_rec.incident_type_id := p2_a12;
116 ddp_terrservreq_rec.incident_severity_id := p2_a13;
117 ddp_terrservreq_rec.incident_urgency_id := p2_a14;
118 ddp_terrservreq_rec.problem_code := p2_a15;
119 ddp_terrservreq_rec.incident_status_id := p2_a16;
120 ddp_terrservreq_rec.platform_id := p2_a17;
121 ddp_terrservreq_rec.support_site_id := p2_a18;
122 ddp_terrservreq_rec.customer_site_id := p2_a19;
123 ddp_terrservreq_rec.sr_creation_channel := p2_a20;
124 ddp_terrservreq_rec.inventory_item_id := p2_a21;
125 ddp_terrservreq_rec.attribute1 := p2_a22;
126 ddp_terrservreq_rec.attribute2 := p2_a23;
127 ddp_terrservreq_rec.attribute3 := p2_a24;
128 ddp_terrservreq_rec.attribute4 := p2_a25;
129 ddp_terrservreq_rec.attribute5 := p2_a26;
130 ddp_terrservreq_rec.attribute6 := p2_a27;
131 ddp_terrservreq_rec.attribute7 := p2_a28;
132 ddp_terrservreq_rec.attribute8 := p2_a29;
133 ddp_terrservreq_rec.attribute9 := p2_a30;
134 ddp_terrservreq_rec.attribute10 := p2_a31;
135 ddp_terrservreq_rec.attribute11 := p2_a32;
136 ddp_terrservreq_rec.attribute12 := p2_a33;
137 ddp_terrservreq_rec.attribute13 := p2_a34;
138 ddp_terrservreq_rec.attribute14 := p2_a35;
139 ddp_terrservreq_rec.attribute15 := p2_a36;
140 ddp_terrservreq_rec.organization_id := p2_a37;
141 ddp_terrservreq_rec.squal_num12 := p2_a38;
142 ddp_terrservreq_rec.squal_num13 := p2_a39;
143 ddp_terrservreq_rec.squal_num14 := p2_a40;
144 ddp_terrservreq_rec.squal_num15 := p2_a41;
145 ddp_terrservreq_rec.squal_num16 := p2_a42;
146 ddp_terrservreq_rec.squal_num23 := p2_a43;
147 ddp_terrservreq_rec.squal_num24 := p2_a44;
148 ddp_terrservreq_rec.squal_num17 := p2_a45;
149 ddp_terrservreq_rec.squal_num18 := p2_a46;
150 ddp_terrservreq_rec.squal_num19 := p2_a47;
151 ddp_terrservreq_rec.squal_char11 := p2_a48;
152 ddp_terrservreq_rec.squal_char12 := p2_a49;
153 ddp_terrservreq_rec.squal_char13 := p2_a50;
154 ddp_terrservreq_rec.squal_char21 := p2_a51;
155 ddp_terrservreq_rec.squal_char20 := p2_a52;
156 ddp_terrservreq_rec.squal_num30 := p2_a53;
157 ddp_terrservreq_rec.day_of_week := p2_a54;
158 ddp_terrservreq_rec.time_of_day := p2_a55;
159
160
161
162 ddp_plan_start_date := rosetta_g_miss_date_in_map(p_plan_start_date);
163
164 ddp_plan_end_date := rosetta_g_miss_date_in_map(p_plan_end_date);
165
166
167
168
169
170 -- here's the delegated call to the old PL/SQL routine
171 jtf_terr_service_pub.get_winningterrmembers(p_api_version_number,
172 p_init_msg_list,
173 ddp_terrservreq_rec,
174 p_resource_type,
175 p_role,
176 ddp_plan_start_date,
177 ddp_plan_end_date,
178 x_return_status,
179 x_msg_count,
180 x_msg_data,
181 ddx_terrresource_tbl);
182
183 -- copy data back from the local variables to OUT or IN-OUT args, if any
184
185
186
187
188
189
190
191
192
193
194 jtf_territory_pub_w.rosetta_table_copy_out_p1(ddx_terrresource_tbl, p10_a0
195 , p10_a1
196 , p10_a2
197 , p10_a3
198 , p10_a4
199 , p10_a5
200 , p10_a6
201 , p10_a7
202 , p10_a8
203 , p10_a9
204 , p10_a10
205 , p10_a11
206 );
207 end;
208
209 procedure get_winningterrmembers(p_api_version_number NUMBER
210 , p_init_msg_list VARCHAR2
211 , p2_a0 NUMBER
212 , p2_a1 NUMBER
213 , p2_a2 NUMBER
214 , p2_a3 VARCHAR2
215 , p2_a4 NUMBER
216 , p2_a5 VARCHAR2
217 , p2_a6 VARCHAR2
218 , p2_a7 VARCHAR2
219 , p2_a8 VARCHAR2
220 , p2_a9 VARCHAR2
221 , p2_a10 VARCHAR2
222 , p2_a11 VARCHAR2
223 , p2_a12 NUMBER
224 , p2_a13 NUMBER
225 , p2_a14 NUMBER
226 , p2_a15 NUMBER
227 , p2_a16 NUMBER
228 , p2_a17 NUMBER
229 , p2_a18 NUMBER
230 , p2_a19 VARCHAR2
231 , p2_a20 NUMBER
232 , p2_a21 NUMBER
233 , p2_a22 NUMBER
234 , p2_a23 NUMBER
235 , p2_a24 VARCHAR2
236 , p2_a25 NUMBER
237 , p2_a26 VARCHAR2
238 , p2_a27 VARCHAR2
239 , p2_a28 VARCHAR2
240 , p2_a29 VARCHAR2
241 , p2_a30 VARCHAR2
242 , p2_a31 VARCHAR2
243 , p2_a32 VARCHAR2
244 , p2_a33 VARCHAR2
245 , p2_a34 VARCHAR2
246 , p2_a35 VARCHAR2
247 , p2_a36 VARCHAR2
248 , p2_a37 VARCHAR2
249 , p2_a38 VARCHAR2
250 , p2_a39 VARCHAR2
251 , p2_a40 VARCHAR2
252 , p2_a41 NUMBER
253 , p2_a42 NUMBER
254 , p2_a43 NUMBER
255 , p2_a44 NUMBER
256 , p2_a45 NUMBER
257 , p2_a46 NUMBER
258 , p2_a47 NUMBER
259 , p2_a48 NUMBER
260 , p2_a49 NUMBER
261 , p2_a50 NUMBER
262 , p2_a51 NUMBER
263 , p2_a52 VARCHAR2
264 , p2_a53 VARCHAR2
265 , p2_a54 VARCHAR2
266 , p2_a55 VARCHAR2
267 , p2_a56 VARCHAR2
268 , p2_a57 NUMBER
269 , p2_a58 VARCHAR2
270 , p2_a59 VARCHAR2
271 , p_resource_type VARCHAR2
272 , p_role VARCHAR2
273 , p_plan_start_date date
274 , p_plan_end_date date
275 , x_return_status out nocopy VARCHAR2
276 , x_msg_count out nocopy NUMBER
277 , x_msg_data out nocopy VARCHAR2
278 , p10_a0 out nocopy JTF_NUMBER_TABLE
279 , p10_a1 out nocopy JTF_NUMBER_TABLE
280 , p10_a2 out nocopy JTF_VARCHAR2_TABLE_100
281 , p10_a3 out nocopy JTF_NUMBER_TABLE
282 , p10_a4 out nocopy JTF_VARCHAR2_TABLE_100
283 , p10_a5 out nocopy JTF_DATE_TABLE
284 , p10_a6 out nocopy JTF_DATE_TABLE
285 , p10_a7 out nocopy JTF_VARCHAR2_TABLE_100
286 , p10_a8 out nocopy JTF_VARCHAR2_TABLE_100
287 , p10_a9 out nocopy JTF_NUMBER_TABLE
288 , p10_a10 out nocopy JTF_VARCHAR2_TABLE_100
289 , p10_a11 out nocopy JTF_NUMBER_TABLE
290 )
291
292 as
293 ddp_terrsrvtask_rec jtf_territory_pub.jtf_srv_task_rec_type;
294 ddp_plan_start_date date;
295 ddp_plan_end_date date;
296 ddx_terrresource_tbl jtf_territory_pub.winningterrmember_tbl_type;
297 ddindx binary_integer; indx binary_integer;
298 begin
299
300 -- copy data to the local IN or IN-OUT args, if any
301
302
303 ddp_terrsrvtask_rec.task_id := p2_a0;
304 ddp_terrsrvtask_rec.service_request_id := p2_a1;
305 ddp_terrsrvtask_rec.party_id := p2_a2;
306 ddp_terrsrvtask_rec.country := p2_a3;
307 ddp_terrsrvtask_rec.party_site_id := p2_a4;
308 ddp_terrsrvtask_rec.city := p2_a5;
309 ddp_terrsrvtask_rec.postal_code := p2_a6;
310 ddp_terrsrvtask_rec.state := p2_a7;
311 ddp_terrsrvtask_rec.area_code := p2_a8;
312 ddp_terrsrvtask_rec.county := p2_a9;
313 ddp_terrsrvtask_rec.comp_name_range := p2_a10;
314 ddp_terrsrvtask_rec.province := p2_a11;
315 ddp_terrsrvtask_rec.num_of_employees := p2_a12;
316 ddp_terrsrvtask_rec.task_type_id := p2_a13;
317 ddp_terrsrvtask_rec.task_status_id := p2_a14;
318 ddp_terrsrvtask_rec.task_priority_id := p2_a15;
319 ddp_terrsrvtask_rec.incident_type_id := p2_a16;
320 ddp_terrsrvtask_rec.incident_severity_id := p2_a17;
321 ddp_terrsrvtask_rec.incident_urgency_id := p2_a18;
322 ddp_terrsrvtask_rec.problem_code := p2_a19;
323 ddp_terrsrvtask_rec.incident_status_id := p2_a20;
324 ddp_terrsrvtask_rec.platform_id := p2_a21;
325 ddp_terrsrvtask_rec.support_site_id := p2_a22;
326 ddp_terrsrvtask_rec.customer_site_id := p2_a23;
327 ddp_terrsrvtask_rec.sr_creation_channel := p2_a24;
328 ddp_terrsrvtask_rec.inventory_item_id := p2_a25;
329 ddp_terrsrvtask_rec.attribute1 := p2_a26;
330 ddp_terrsrvtask_rec.attribute2 := p2_a27;
331 ddp_terrsrvtask_rec.attribute3 := p2_a28;
332 ddp_terrsrvtask_rec.attribute4 := p2_a29;
333 ddp_terrsrvtask_rec.attribute5 := p2_a30;
334 ddp_terrsrvtask_rec.attribute6 := p2_a31;
335 ddp_terrsrvtask_rec.attribute7 := p2_a32;
336 ddp_terrsrvtask_rec.attribute8 := p2_a33;
337 ddp_terrsrvtask_rec.attribute9 := p2_a34;
338 ddp_terrsrvtask_rec.attribute10 := p2_a35;
339 ddp_terrsrvtask_rec.attribute11 := p2_a36;
340 ddp_terrsrvtask_rec.attribute12 := p2_a37;
341 ddp_terrsrvtask_rec.attribute13 := p2_a38;
342 ddp_terrsrvtask_rec.attribute14 := p2_a39;
343 ddp_terrsrvtask_rec.attribute15 := p2_a40;
344 ddp_terrsrvtask_rec.organization_id := p2_a41;
345 ddp_terrsrvtask_rec.squal_num12 := p2_a42;
346 ddp_terrsrvtask_rec.squal_num13 := p2_a43;
347 ddp_terrsrvtask_rec.squal_num14 := p2_a44;
348 ddp_terrsrvtask_rec.squal_num15 := p2_a45;
349 ddp_terrsrvtask_rec.squal_num16 := p2_a46;
350 ddp_terrsrvtask_rec.squal_num23 := p2_a47;
351 ddp_terrsrvtask_rec.squal_num24 := p2_a48;
352 ddp_terrsrvtask_rec.squal_num17 := p2_a49;
353 ddp_terrsrvtask_rec.squal_num18 := p2_a50;
354 ddp_terrsrvtask_rec.squal_num19 := p2_a51;
355 ddp_terrsrvtask_rec.squal_char11 := p2_a52;
356 ddp_terrsrvtask_rec.squal_char12 := p2_a53;
357 ddp_terrsrvtask_rec.squal_char13 := p2_a54;
358 ddp_terrsrvtask_rec.squal_char21 := p2_a55;
359 ddp_terrsrvtask_rec.squal_char20 := p2_a56;
360 ddp_terrsrvtask_rec.squal_num30 := p2_a57;
361 ddp_terrsrvtask_rec.day_of_week := p2_a58;
362 ddp_terrsrvtask_rec.time_of_day := p2_a59;
363
364
365
366 ddp_plan_start_date := rosetta_g_miss_date_in_map(p_plan_start_date);
367
368 ddp_plan_end_date := rosetta_g_miss_date_in_map(p_plan_end_date);
369
370
371
372
373
374 -- here's the delegated call to the old PL/SQL routine
375 jtf_terr_service_pub.get_winningterrmembers(p_api_version_number,
376 p_init_msg_list,
377 ddp_terrsrvtask_rec,
378 p_resource_type,
379 p_role,
380 ddp_plan_start_date,
381 ddp_plan_end_date,
382 x_return_status,
383 x_msg_count,
384 x_msg_data,
385 ddx_terrresource_tbl);
386
387 -- copy data back from the local variables to OUT or IN-OUT args, if any
388
389
390
391
392
393
394
395
396
397
398 jtf_territory_pub_w.rosetta_table_copy_out_p1(ddx_terrresource_tbl, p10_a0
399 , p10_a1
400 , p10_a2
401 , p10_a3
402 , p10_a4
403 , p10_a5
404 , p10_a6
405 , p10_a7
406 , p10_a8
407 , p10_a9
408 , p10_a10
409 , p10_a11
410 );
411 end;
412
413 end jtf_terr_service_pub_w;