1 package body amv_match_pvt_w as
2 /* $Header: amvwmatb.pls 120.2 2005/06/30 08:06 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 rosetta_table_copy_in_p3(t out nocopy amv_match_pvt.terr_id_tbl_type, a0 JTF_NUMBER_TABLE) as
23 ddindx binary_integer; indx binary_integer;
24 begin
25 if a0 is not null and a0.count > 0 then
26 if a0.count > 0 then
27 indx := a0.first;
28 ddindx := 1;
29 while true loop
30 t(ddindx) := rosetta_g_miss_num_map(a0(indx));
31 ddindx := ddindx+1;
32 if a0.last =indx
33 then exit;
34 end if;
35 indx := a0.next(indx);
36 end loop;
37 end if;
38 end if;
39 end rosetta_table_copy_in_p3;
40 procedure rosetta_table_copy_out_p3(t amv_match_pvt.terr_id_tbl_type, a0 out nocopy JTF_NUMBER_TABLE) as
41 ddindx binary_integer; indx binary_integer;
42 begin
43 if t is null or t.count = 0 then
44 a0 := JTF_NUMBER_TABLE();
45 else
46 a0 := JTF_NUMBER_TABLE();
47 if t.count > 0 then
48 a0.extend(t.count);
49 ddindx := t.first;
50 indx := 1;
51 while true loop
52 a0(indx) := rosetta_g_miss_num_map(t(ddindx));
53 indx := indx+1;
54 if t.last =ddindx
55 then exit;
56 end if;
57 ddindx := t.next(ddindx);
58 end loop;
59 end if;
60 end if;
61 end rosetta_table_copy_out_p3;
62
63 procedure rosetta_table_copy_in_p4(t out nocopy amv_match_pvt.terr_name_tbl_type, a0 JTF_VARCHAR2_TABLE_4000) as
64 ddindx binary_integer; indx binary_integer;
65 begin
66 if a0 is not null and a0.count > 0 then
67 if a0.count > 0 then
68 indx := a0.first;
69 ddindx := 1;
70 while true loop
71 t(ddindx) := a0(indx);
72 ddindx := ddindx+1;
73 if a0.last =indx
74 then exit;
75 end if;
76 indx := a0.next(indx);
77 end loop;
78 end if;
79 end if;
80 end rosetta_table_copy_in_p4;
81 procedure rosetta_table_copy_out_p4(t amv_match_pvt.terr_name_tbl_type, a0 out nocopy JTF_VARCHAR2_TABLE_4000) as
82 ddindx binary_integer; indx binary_integer;
83 begin
84 if t is null or t.count = 0 then
85 a0 := JTF_VARCHAR2_TABLE_4000();
86 else
87 a0 := JTF_VARCHAR2_TABLE_4000();
88 if t.count > 0 then
89 a0.extend(t.count);
90 ddindx := t.first;
91 indx := 1;
92 while true loop
93 a0(indx) := t(ddindx);
94 indx := indx+1;
95 if t.last =ddindx
96 then exit;
97 end if;
98 ddindx := t.next(ddindx);
99 end loop;
100 end if;
101 end if;
102 end rosetta_table_copy_out_p4;
103
104 procedure do_itemchannelmatch(p_api_version NUMBER
105 , p_init_msg_list VARCHAR2
106 , p_commit VARCHAR2
107 , p_validation_level NUMBER
108 , x_return_status out nocopy VARCHAR2
109 , x_msg_count out nocopy NUMBER
110 , x_msg_data out nocopy VARCHAR2
111 , p_check_login_user VARCHAR2
112 , p_application_id NUMBER
113 , p_category_id NUMBER
114 , p_channel_id NUMBER
115 , p_item_id NUMBER
116 , p_table_name_code VARCHAR2
117 , p_match_type VARCHAR2
118 , p_territory_tbl JTF_NUMBER_TABLE
119 )
120
121 as
122 ddp_territory_tbl amv_match_pvt.terr_id_tbl_type;
123 ddindx binary_integer; indx binary_integer;
124 begin
125
126 -- copy data to the local IN or IN-OUT args, if any
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141 amv_match_pvt_w.rosetta_table_copy_in_p3(ddp_territory_tbl, p_territory_tbl);
142
143 -- here's the delegated call to the old PL/SQL routine
144 amv_match_pvt.do_itemchannelmatch(p_api_version,
145 p_init_msg_list,
146 p_commit,
147 p_validation_level,
148 x_return_status,
149 x_msg_count,
150 x_msg_data,
151 p_check_login_user,
152 p_application_id,
153 p_category_id,
154 p_channel_id,
155 p_item_id,
156 p_table_name_code,
157 p_match_type,
158 ddp_territory_tbl);
159
160 -- copy data back from the local variables to OUT or IN-OUT args, if any
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175 end;
176
177 procedure get_userterritory(p_api_version NUMBER
178 , p_init_msg_list VARCHAR2
179 , x_return_status out nocopy VARCHAR2
180 , x_msg_count out nocopy NUMBER
181 , x_msg_data out nocopy VARCHAR2
182 , p_check_login_user VARCHAR2
183 , p_resource_id NUMBER
184 , p_resource_type VARCHAR2
185 , x_terr_id_tbl out nocopy JTF_NUMBER_TABLE
186 , x_terr_name_tbl out nocopy JTF_VARCHAR2_TABLE_4000
187 )
188
189 as
190 ddx_terr_id_tbl amv_match_pvt.terr_id_tbl_type;
191 ddx_terr_name_tbl amv_match_pvt.terr_name_tbl_type;
192 ddindx binary_integer; indx binary_integer;
193 begin
194
195 -- copy data to the local IN or IN-OUT args, if any
196
197
198
199
200
201
202
203
204
205
206 -- here's the delegated call to the old PL/SQL routine
207 amv_match_pvt.get_userterritory(p_api_version,
208 p_init_msg_list,
209 x_return_status,
210 x_msg_count,
211 x_msg_data,
212 p_check_login_user,
213 p_resource_id,
214 p_resource_type,
215 ddx_terr_id_tbl,
216 ddx_terr_name_tbl);
217
218 -- copy data back from the local variables to OUT or IN-OUT args, if any
219
220
221
222
223
224
225
226
227 amv_match_pvt_w.rosetta_table_copy_out_p3(ddx_terr_id_tbl, x_terr_id_tbl);
228
229 amv_match_pvt_w.rosetta_table_copy_out_p4(ddx_terr_name_tbl, x_terr_name_tbl);
230 end;
231
232 procedure get_publishedterritories(p_api_version NUMBER
233 , p_init_msg_list VARCHAR2
234 , x_return_status out nocopy VARCHAR2
235 , x_msg_count out nocopy NUMBER
236 , x_msg_data out nocopy VARCHAR2
237 , p_check_login_user VARCHAR2
238 , p_terr_id NUMBER
239 , p_table_name_code VARCHAR2
240 , x_item_id_tbl out nocopy JTF_NUMBER_TABLE
241 )
242
243 as
244 ddx_item_id_tbl amv_match_pvt.terr_id_tbl_type;
245 ddindx binary_integer; indx binary_integer;
246 begin
247
248 -- copy data to the local IN or IN-OUT args, if any
249
250
251
252
253
254
255
256
257
258 -- here's the delegated call to the old PL/SQL routine
259 amv_match_pvt.get_publishedterritories(p_api_version,
260 p_init_msg_list,
261 x_return_status,
262 x_msg_count,
263 x_msg_data,
264 p_check_login_user,
265 p_terr_id,
266 p_table_name_code,
267 ddx_item_id_tbl);
268
269 -- copy data back from the local variables to OUT or IN-OUT args, if any
270
271
272
273
274
275
276
277
278 amv_match_pvt_w.rosetta_table_copy_out_p3(ddx_item_id_tbl, x_item_id_tbl);
279 end;
280
281 end amv_match_pvt_w;