[Home] [Help]
PACKAGE BODY: APPS.CN_SALES_HIER_PUB_W
Source
1 package body cn_sales_hier_pub_w as
2 /* $Header: cnwhierb.pls 115.5 2002/11/25 23:57:30 fting 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_p1(t out nocopy cn_sales_hier_pub.hier_tbl_type, a0 JTF_VARCHAR2_TABLE_300
14 , a1 JTF_VARCHAR2_TABLE_100
15 , a2 JTF_VARCHAR2_TABLE_100
16 , a3 JTF_DATE_TABLE
17 , a4 JTF_DATE_TABLE
18 ) as
19 ddindx binary_integer; indx binary_integer;
20 begin
21 if a0 is not null and a0.count > 0 then
22 if a0.count > 0 then
23 indx := a0.first;
24 ddindx := 1;
25 while true loop
26 t(ddindx).name := a0(indx);
27 t(ddindx).number := a1(indx);
28 t(ddindx).role := a2(indx);
29 t(ddindx).start_date := rosetta_g_miss_date_in_map(a3(indx));
30 t(ddindx).end_date := rosetta_g_miss_date_in_map(a4(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_p1;
40 procedure rosetta_table_copy_out_p1(t cn_sales_hier_pub.hier_tbl_type, a0 out nocopy JTF_VARCHAR2_TABLE_300
41 , a1 out nocopy JTF_VARCHAR2_TABLE_100
42 , a2 out nocopy JTF_VARCHAR2_TABLE_100
43 , a3 out nocopy JTF_DATE_TABLE
44 , a4 out nocopy JTF_DATE_TABLE
45 ) as
46 ddindx binary_integer; indx binary_integer;
47 begin
48 if t is null or t.count = 0 then
49 a0 := JTF_VARCHAR2_TABLE_300();
50 a1 := JTF_VARCHAR2_TABLE_100();
51 a2 := JTF_VARCHAR2_TABLE_100();
52 a3 := JTF_DATE_TABLE();
53 a4 := JTF_DATE_TABLE();
54 else
55 a0 := JTF_VARCHAR2_TABLE_300();
56 a1 := JTF_VARCHAR2_TABLE_100();
57 a2 := JTF_VARCHAR2_TABLE_100();
58 a3 := JTF_DATE_TABLE();
59 a4 := JTF_DATE_TABLE();
60 if t.count > 0 then
61 a0.extend(t.count);
62 a1.extend(t.count);
63 a2.extend(t.count);
64 a3.extend(t.count);
65 a4.extend(t.count);
66 ddindx := t.first;
67 indx := 1;
68 while true loop
69 a0(indx) := t(ddindx).name;
70 a1(indx) := t(ddindx).number;
71 a2(indx) := t(ddindx).role;
72 a3(indx) := t(ddindx).start_date;
73 a4(indx) := t(ddindx).end_date;
74 indx := indx+1;
75 if t.last =ddindx
76 then exit;
77 end if;
78 ddindx := t.next(ddindx);
79 end loop;
80 end if;
81 end if;
82 end rosetta_table_copy_out_p1;
83
84 procedure rosetta_table_copy_in_p3(t out nocopy cn_sales_hier_pub.grp_tbl_type, a0 JTF_VARCHAR2_TABLE_100
85 , a1 JTF_NUMBER_TABLE
86 , a2 JTF_VARCHAR2_TABLE_300
87 , a3 JTF_VARCHAR2_TABLE_100
88 ) as
89 ddindx binary_integer; indx binary_integer;
90 begin
91 if a0 is not null and a0.count > 0 then
92 if a0.count > 0 then
93 indx := a0.first;
94 ddindx := 1;
95 while true loop
96 t(ddindx).grp_name := a0(indx);
97 t(ddindx).grp_id := a1(indx);
98 t(ddindx).mgr_name := a2(indx);
99 t(ddindx).mgr_number := a3(indx);
100 ddindx := ddindx+1;
101 if a0.last =indx
102 then exit;
103 end if;
104 indx := a0.next(indx);
105 end loop;
106 end if;
107 end if;
108 end rosetta_table_copy_in_p3;
109 procedure rosetta_table_copy_out_p3(t cn_sales_hier_pub.grp_tbl_type, a0 out nocopy JTF_VARCHAR2_TABLE_100
110 , a1 out nocopy JTF_NUMBER_TABLE
111 , a2 out nocopy JTF_VARCHAR2_TABLE_300
112 , a3 out nocopy JTF_VARCHAR2_TABLE_100
113 ) as
114 ddindx binary_integer; indx binary_integer;
115 begin
116 if t is null or t.count = 0 then
117 a0 := JTF_VARCHAR2_TABLE_100();
118 a1 := JTF_NUMBER_TABLE();
119 a2 := JTF_VARCHAR2_TABLE_300();
120 a3 := JTF_VARCHAR2_TABLE_100();
121 else
122 a0 := JTF_VARCHAR2_TABLE_100();
123 a1 := JTF_NUMBER_TABLE();
124 a2 := JTF_VARCHAR2_TABLE_300();
125 a3 := JTF_VARCHAR2_TABLE_100();
126 if t.count > 0 then
127 a0.extend(t.count);
128 a1.extend(t.count);
129 a2.extend(t.count);
130 a3.extend(t.count);
131 ddindx := t.first;
132 indx := 1;
133 while true loop
134 a0(indx) := t(ddindx).grp_name;
135 a1(indx) := t(ddindx).grp_id;
136 a2(indx) := t(ddindx).mgr_name;
137 a3(indx) := t(ddindx).mgr_number;
138 indx := indx+1;
139 if t.last =ddindx
140 then exit;
141 end if;
142 ddindx := t.next(ddindx);
143 end loop;
144 end if;
145 end if;
146 end rosetta_table_copy_out_p3;
147
148 procedure get_sales_hier(p_api_version NUMBER
149 , p_init_msg_list VARCHAR2
150 , p_validation_level VARCHAR2
151 , x_return_status out nocopy VARCHAR2
152 , x_msg_count out nocopy NUMBER
153 , x_msg_data out nocopy VARCHAR2
154 , x_loading_status out nocopy VARCHAR2
155 , p_salesrep_id NUMBER
156 , p_comp_group_id NUMBER
157 , p_date date
158 , p_start_record NUMBER
159 , p_increment_count NUMBER
160 , p_start_record_grp NUMBER
161 , p_increment_count_grp NUMBER
162 , p14_a0 out nocopy JTF_VARCHAR2_TABLE_300
163 , p14_a1 out nocopy JTF_VARCHAR2_TABLE_100
164 , p14_a2 out nocopy JTF_VARCHAR2_TABLE_100
165 , p14_a3 out nocopy JTF_DATE_TABLE
166 , p14_a4 out nocopy JTF_DATE_TABLE
167 , x_mgr_count out nocopy NUMBER
168 , p16_a0 out nocopy JTF_VARCHAR2_TABLE_300
169 , p16_a1 out nocopy JTF_VARCHAR2_TABLE_100
170 , p16_a2 out nocopy JTF_VARCHAR2_TABLE_100
171 , p16_a3 out nocopy JTF_DATE_TABLE
172 , p16_a4 out nocopy JTF_DATE_TABLE
173 , x_srp_count out nocopy NUMBER
174 , p18_a0 out nocopy JTF_VARCHAR2_TABLE_100
175 , p18_a1 out nocopy JTF_NUMBER_TABLE
176 , p18_a2 out nocopy JTF_VARCHAR2_TABLE_300
177 , p18_a3 out nocopy JTF_VARCHAR2_TABLE_100
178 , x_grp_count out nocopy NUMBER
179 )
180
181 as
182 ddp_date date;
183 ddx_mgr_tbl cn_sales_hier_pub.hier_tbl_type;
184 ddx_srp_tbl cn_sales_hier_pub.hier_tbl_type;
185 ddx_grp_tbl cn_sales_hier_pub.grp_tbl_type;
186 ddindx binary_integer; indx binary_integer;
187 begin
188
189 -- copy data to the local IN or IN-OUT args, if any
190
191
192
193
194
195
196
197
198
199 ddp_date := rosetta_g_miss_date_in_map(p_date);
200
201
202
203
204
205
206
207
208
209
210
211 -- here's the delegated call to the old PL/SQL routine
212 cn_sales_hier_pub.get_sales_hier(p_api_version,
213 p_init_msg_list,
214 p_validation_level,
215 x_return_status,
216 x_msg_count,
217 x_msg_data,
218 x_loading_status,
219 p_salesrep_id,
220 p_comp_group_id,
221 ddp_date,
222 p_start_record,
223 p_increment_count,
224 p_start_record_grp,
225 p_increment_count_grp,
226 ddx_mgr_tbl,
227 x_mgr_count,
228 ddx_srp_tbl,
229 x_srp_count,
230 ddx_grp_tbl,
231 x_grp_count);
232
233 -- copy data back from the local variables to OUT or IN-OUT args, if any
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248 cn_sales_hier_pub_w.rosetta_table_copy_out_p1(ddx_mgr_tbl, p14_a0
249 , p14_a1
250 , p14_a2
251 , p14_a3
252 , p14_a4
253 );
254
255
256 cn_sales_hier_pub_w.rosetta_table_copy_out_p1(ddx_srp_tbl, p16_a0
257 , p16_a1
258 , p16_a2
259 , p16_a3
260 , p16_a4
261 );
262
263
264 cn_sales_hier_pub_w.rosetta_table_copy_out_p3(ddx_grp_tbl, p18_a0
265 , p18_a1
266 , p18_a2
267 , p18_a3
268 );
269
270 end;
271
272 end cn_sales_hier_pub_w;