[Home] [Help]
PACKAGE BODY: APPS.AMV_CATEGORY_GRP_W
Source
1 package body amv_category_grp_w as
2 /* $Header: amvwcagb.pls 120.2 2005/06/30 07:43 appldev 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 function rosetta_g_miss_num_map(n number) return number as
14 a number := fnd_api.g_miss_num;
15 b number := 0-1962.0724;
16 begin
17 if n=a then return b; end if;
18 if n=b then return a; end if;
19 return n;
20 end;
21
22 procedure reorder_category(p_api_version NUMBER
23 , p_init_msg_list VARCHAR2
24 , p_commit VARCHAR2
25 , p_validation_level NUMBER
26 , x_return_status out nocopy VARCHAR2
27 , x_msg_count out nocopy NUMBER
28 , x_msg_data out nocopy VARCHAR2
29 , p_check_login_user VARCHAR2
30 , p_application_id NUMBER
31 , p_category_id_array JTF_NUMBER_TABLE
32 , p_category_new_order JTF_NUMBER_TABLE
33 )
34
35 as
36 ddp_category_id_array amv_category_pvt.amv_number_varray_type;
37 ddp_category_new_order amv_category_pvt.amv_number_varray_type;
38 ddindx binary_integer; indx binary_integer;
39 begin
40
41 -- copy data to the local IN or IN-OUT args, if any
42
43
44
45
46
47
48
49
50
51 amv_category_pvt_w.rosetta_table_copy_in_p3(ddp_category_id_array, p_category_id_array);
52
53 amv_category_pvt_w.rosetta_table_copy_in_p3(ddp_category_new_order, p_category_new_order);
54
55 -- here's the delegated call to the old PL/SQL routine
56 amv_category_grp.reorder_category(p_api_version,
57 p_init_msg_list,
58 p_commit,
59 p_validation_level,
60 x_return_status,
61 x_msg_count,
62 x_msg_data,
63 p_check_login_user,
64 p_application_id,
65 ddp_category_id_array,
66 ddp_category_new_order);
67
68 -- copy data back from the local variables to OUT or IN-OUT args, if any
69
70
71
72
73
74
75
76
77
78
79 end;
80
81 procedure find_categories(p_api_version NUMBER
82 , p_init_msg_list VARCHAR2
83 , p_validation_level NUMBER
84 , x_return_status out nocopy VARCHAR2
85 , x_msg_count out nocopy NUMBER
86 , x_msg_data out nocopy VARCHAR2
87 , p_check_login_user VARCHAR2
88 , p_application_id NUMBER
89 , p_category_name VARCHAR2
90 , p_parent_category_id NUMBER
91 , p_ignore_hierarchy VARCHAR2
92 , p12_a0 out nocopy NUMBER
93 , p12_a1 out nocopy NUMBER
94 , p12_a2 out nocopy NUMBER
95 , p13_a0 out nocopy JTF_NUMBER_TABLE
96 , p13_a1 out nocopy JTF_NUMBER_TABLE
97 , p13_a2 out nocopy JTF_NUMBER_TABLE
98 , p13_a3 out nocopy JTF_NUMBER_TABLE
99 , p13_a4 out nocopy JTF_NUMBER_TABLE
100 , p13_a5 out nocopy JTF_VARCHAR2_TABLE_100
101 , p13_a6 out nocopy JTF_VARCHAR2_TABLE_2000
102 , p13_a7 out nocopy JTF_NUMBER_TABLE
103 , p11_a0 NUMBER := 0-1962.0724
104 , p11_a1 NUMBER := 0-1962.0724
105 , p11_a2 VARCHAR2 := fnd_api.g_miss_char
106 )
107
108 as
109 ddp_request_obj amv_category_pvt.amv_request_obj_type;
110 ddx_return_obj amv_category_pvt.amv_return_obj_type;
111 ddx_chan_category_rec_array amv_category_pvt.amv_category_varray_type;
112 ddindx binary_integer; indx binary_integer;
113 begin
114
115 -- copy data to the local IN or IN-OUT args, if any
116
117
118
119
120
121
122
123
124
125
126
127 ddp_request_obj.records_requested := rosetta_g_miss_num_map(p11_a0);
128 ddp_request_obj.start_record_position := rosetta_g_miss_num_map(p11_a1);
129 ddp_request_obj.return_total_count_flag := p11_a2;
130
131
132
133 -- here's the delegated call to the old PL/SQL routine
134 amv_category_grp.find_categories(p_api_version,
135 p_init_msg_list,
136 p_validation_level,
137 x_return_status,
138 x_msg_count,
139 x_msg_data,
140 p_check_login_user,
141 p_application_id,
142 p_category_name,
143 p_parent_category_id,
144 p_ignore_hierarchy,
145 ddp_request_obj,
146 ddx_return_obj,
147 ddx_chan_category_rec_array);
148
149 -- copy data back from the local variables to OUT or IN-OUT args, if any
150
151
152
153
154
155
156
157
158
159
160
161
162 p12_a0 := rosetta_g_miss_num_map(ddx_return_obj.returned_record_count);
163 p12_a1 := rosetta_g_miss_num_map(ddx_return_obj.next_record_position);
164 p12_a2 := rosetta_g_miss_num_map(ddx_return_obj.total_record_count);
165
166 amv_category_pvt_w.rosetta_table_copy_out_p7(ddx_chan_category_rec_array, p13_a0
167 , p13_a1
168 , p13_a2
169 , p13_a3
170 , p13_a4
171 , p13_a5
172 , p13_a6
173 , p13_a7
174 );
175 end;
176
177 procedure get_channelspercategory(p_api_version NUMBER
178 , p_init_msg_list VARCHAR2
179 , p_validation_level NUMBER
180 , x_return_status out nocopy VARCHAR2
181 , x_msg_count out nocopy NUMBER
182 , x_msg_data out nocopy VARCHAR2
183 , p_check_login_user VARCHAR2
184 , p_category_id NUMBER
185 , p_include_subcats VARCHAR2
186 , p9_a0 out nocopy JTF_NUMBER_TABLE
187 , p9_a1 out nocopy JTF_NUMBER_TABLE
188 , p9_a2 out nocopy JTF_VARCHAR2_TABLE_300
189 )
190
191 as
192 ddx_content_chan_array amv_category_pvt.amv_cat_hierarchy_varray_type;
193 ddindx binary_integer; indx binary_integer;
194 begin
195
196 -- copy data to the local IN or IN-OUT args, if any
197
198
199
200
201
202
203
204
205
206
207 -- here's the delegated call to the old PL/SQL routine
208 amv_category_grp.get_channelspercategory(p_api_version,
209 p_init_msg_list,
210 p_validation_level,
211 x_return_status,
212 x_msg_count,
213 x_msg_data,
214 p_check_login_user,
215 p_category_id,
216 p_include_subcats,
217 ddx_content_chan_array);
218
219 -- copy data back from the local variables to OUT or IN-OUT args, if any
220
221
222
223
224
225
226
227
228
229 amv_category_pvt_w.rosetta_table_copy_out_p10(ddx_content_chan_array, p9_a0
230 , p9_a1
231 , p9_a2
232 );
233 end;
234
235 procedure get_catparentshierarchy(p_api_version NUMBER
236 , p_init_msg_list VARCHAR2
237 , p_validation_level NUMBER
238 , x_return_status out nocopy VARCHAR2
239 , x_msg_count out nocopy NUMBER
240 , x_msg_data out nocopy VARCHAR2
241 , p_check_login_user VARCHAR2
242 , p_category_id NUMBER
243 , p8_a0 out nocopy JTF_NUMBER_TABLE
244 , p8_a1 out nocopy JTF_NUMBER_TABLE
245 , p8_a2 out nocopy JTF_VARCHAR2_TABLE_300
246 )
247
248 as
249 ddx_category_hierarchy amv_category_pvt.amv_cat_hierarchy_varray_type;
250 ddindx binary_integer; indx binary_integer;
251 begin
252
253 -- copy data to the local IN or IN-OUT args, if any
254
255
256
257
258
259
260
261
262
263 -- here's the delegated call to the old PL/SQL routine
264 amv_category_grp.get_catparentshierarchy(p_api_version,
265 p_init_msg_list,
266 p_validation_level,
267 x_return_status,
268 x_msg_count,
269 x_msg_data,
270 p_check_login_user,
271 p_category_id,
272 ddx_category_hierarchy);
273
274 -- copy data back from the local variables to OUT or IN-OUT args, if any
275
276
277
278
279
280
281
282
283 amv_category_pvt_w.rosetta_table_copy_out_p10(ddx_category_hierarchy, p8_a0
284 , p8_a1
285 , p8_a2
286 );
287 end;
288
289 procedure get_catchildrenhierarchy(p_api_version NUMBER
290 , p_init_msg_list VARCHAR2
291 , p_validation_level NUMBER
292 , x_return_status out nocopy VARCHAR2
293 , x_msg_count out nocopy NUMBER
294 , x_msg_data out nocopy VARCHAR2
295 , p_check_login_user VARCHAR2
296 , p_category_id NUMBER
297 , p8_a0 out nocopy JTF_NUMBER_TABLE
298 , p8_a1 out nocopy JTF_NUMBER_TABLE
299 , p8_a2 out nocopy JTF_VARCHAR2_TABLE_300
300 )
301
302 as
303 ddx_category_hierarchy amv_category_pvt.amv_cat_hierarchy_varray_type;
304 ddindx binary_integer; indx binary_integer;
305 begin
306
307 -- copy data to the local IN or IN-OUT args, if any
308
309
310
311
312
313
314
315
316
317 -- here's the delegated call to the old PL/SQL routine
318 amv_category_grp.get_catchildrenhierarchy(p_api_version,
319 p_init_msg_list,
320 p_validation_level,
321 x_return_status,
322 x_msg_count,
323 x_msg_data,
324 p_check_login_user,
325 p_category_id,
326 ddx_category_hierarchy);
327
328 -- copy data back from the local variables to OUT or IN-OUT args, if any
329
330
331
332
333
334
335
336
337 amv_category_pvt_w.rosetta_table_copy_out_p10(ddx_category_hierarchy, p8_a0
338 , p8_a1
339 , p8_a2
340 );
341 end;
342
343 procedure get_chncategoryhierarchy(p_api_version NUMBER
344 , p_init_msg_list VARCHAR2
345 , p_validation_level NUMBER
346 , x_return_status out nocopy VARCHAR2
347 , x_msg_count out nocopy NUMBER
348 , x_msg_data out nocopy VARCHAR2
349 , p_check_login_user VARCHAR2
350 , p_channel_id NUMBER
351 , x_channel_name out nocopy VARCHAR2
352 , p9_a0 out nocopy JTF_NUMBER_TABLE
353 , p9_a1 out nocopy JTF_NUMBER_TABLE
354 , p9_a2 out nocopy JTF_VARCHAR2_TABLE_300
355 )
356
357 as
358 ddx_category_hierarchy amv_category_pvt.amv_cat_hierarchy_varray_type;
359 ddindx binary_integer; indx binary_integer;
360 begin
361
362 -- copy data to the local IN or IN-OUT args, if any
363
364
365
366
367
368
369
370
371
372
373 -- here's the delegated call to the old PL/SQL routine
374 amv_category_grp.get_chncategoryhierarchy(p_api_version,
375 p_init_msg_list,
376 p_validation_level,
377 x_return_status,
378 x_msg_count,
379 x_msg_data,
380 p_check_login_user,
381 p_channel_id,
382 x_channel_name,
383 ddx_category_hierarchy);
384
385 -- copy data back from the local variables to OUT or IN-OUT args, if any
386
387
388
389
390
391
392
393
394
395 amv_category_pvt_w.rosetta_table_copy_out_p10(ddx_category_hierarchy, p9_a0
396 , p9_a1
397 , p9_a2
398 );
399 end;
400
401 procedure get_itemspercategory(p_api_version NUMBER
402 , p_init_msg_list VARCHAR2
403 , p_validation_level NUMBER
404 , x_return_status out nocopy VARCHAR2
405 , x_msg_count out nocopy NUMBER
406 , x_msg_data out nocopy VARCHAR2
407 , p_check_login_user VARCHAR2
408 , p_category_id NUMBER
409 , p_include_subcats VARCHAR2
410 , p11_a0 out nocopy NUMBER
411 , p11_a1 out nocopy NUMBER
412 , p11_a2 out nocopy NUMBER
413 , p12_a0 out nocopy JTF_NUMBER_TABLE
414 , p12_a1 out nocopy JTF_NUMBER_TABLE
415 , p12_a2 out nocopy JTF_VARCHAR2_TABLE_300
416 , p9_a0 NUMBER := 0-1962.0724
417 , p9_a1 NUMBER := 0-1962.0724
418 , p9_a2 VARCHAR2 := fnd_api.g_miss_char
419 , p10_a0 VARCHAR2 := fnd_api.g_miss_char
420 , p10_a1 VARCHAR2 := fnd_api.g_miss_char
421 )
422
423 as
424 ddp_request_obj amv_category_pvt.amv_request_obj_type;
425 ddp_category_sort amv_category_pvt.amv_sort_obj_type;
426 ddx_return_obj amv_category_pvt.amv_return_obj_type;
427 ddx_items_array amv_category_pvt.amv_cat_hierarchy_varray_type;
428 ddindx binary_integer; indx binary_integer;
429 begin
430
431 -- copy data to the local IN or IN-OUT args, if any
432
433
434
435
436
437
438
439
440
441 ddp_request_obj.records_requested := rosetta_g_miss_num_map(p9_a0);
442 ddp_request_obj.start_record_position := rosetta_g_miss_num_map(p9_a1);
443 ddp_request_obj.return_total_count_flag := p9_a2;
444
445 ddp_category_sort.sort_col := p10_a0;
446 ddp_category_sort.sort_dir := p10_a1;
447
448
449
450 -- here's the delegated call to the old PL/SQL routine
451 amv_category_grp.get_itemspercategory(p_api_version,
452 p_init_msg_list,
453 p_validation_level,
454 x_return_status,
455 x_msg_count,
456 x_msg_data,
457 p_check_login_user,
458 p_category_id,
459 p_include_subcats,
460 ddp_request_obj,
461 ddp_category_sort,
462 ddx_return_obj,
463 ddx_items_array);
464
465 -- copy data back from the local variables to OUT or IN-OUT args, if any
466
467
468
469
470
471
472
473
474
475
476
477 p11_a0 := rosetta_g_miss_num_map(ddx_return_obj.returned_record_count);
478 p11_a1 := rosetta_g_miss_num_map(ddx_return_obj.next_record_position);
479 p11_a2 := rosetta_g_miss_num_map(ddx_return_obj.total_record_count);
480
481 amv_category_pvt_w.rosetta_table_copy_out_p10(ddx_items_array, p12_a0
482 , p12_a1
483 , p12_a2
484 );
485 end;
486
487 end amv_category_grp_w;