[Home] [Help]
PACKAGE BODY: APPS.CN_GET_COMM_SUMM_DATA_W
Source
1 package body cn_get_comm_summ_data_w as
2 /* $Header: cnwcommb.pls 115.3 2001/01/15 18:45:57 pkm 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 function rosetta_g_miss_num_map(n number) return number as
7 a number := fnd_api.g_miss_num;
8 b number := 0-1962.0724;
9 begin
10 if n=a then return b; end if;
11 if n=b then return a; end if;
12 return n;
13 end;
14
15 -- this is to workaround the JDBC bug regarding IN DATE of value GMiss
16 function rosetta_g_miss_date_in_map(d date) return date as
17 begin
18 if d = rosetta_g_mistake_date then return fnd_api.g_miss_date; end if;
19 return d;
20 end;
21
22 procedure rosetta_table_copy_in_p1(t out cn_get_comm_summ_data.comm_summ_tbl_type, a0 JTF_NUMBER_TABLE
23 , a1 JTF_VARCHAR2_TABLE_300
24 , a2 JTF_VARCHAR2_TABLE_100
25 , a3 JTF_VARCHAR2_TABLE_100
26 , a4 JTF_VARCHAR2_TABLE_100
27 , a5 JTF_VARCHAR2_TABLE_300
28 , a6 JTF_VARCHAR2_TABLE_100
29 , a7 JTF_VARCHAR2_TABLE_100
30 , a8 JTF_NUMBER_TABLE
31 , a9 JTF_NUMBER_TABLE
32 , a10 JTF_NUMBER_TABLE
33 ) as
34 ddindx binary_integer; indx binary_integer;
35 begin
36 if a0 is not null and a0.count > 0 then
37 if a0.count > 0 then
38 indx := a0.first;
39 ddindx := 1;
40 while true loop
41 t(ddindx).srp_plan_assign_id := a0(indx);
42 t(ddindx).name := a1(indx);
43 t(ddindx).emp_num := a2(indx);
44 t(ddindx).cost_center := a3(indx);
45 t(ddindx).charge_to_cost_center := a4(indx);
46 t(ddindx).analyst_name := a5(indx);
47 t(ddindx).role_name := a6(indx);
48 t(ddindx).plan_name := a7(indx);
49 t(ddindx).begin_balance := a8(indx);
50 t(ddindx).draw := a9(indx);
51 t(ddindx).net_due := a10(indx);
52 ddindx := ddindx+1;
53 if a0.last =indx
54 then exit;
55 end if;
56 indx := a0.next(indx);
57 end loop;
58 end if;
59 end if;
60 end rosetta_table_copy_in_p1;
61 procedure rosetta_table_copy_out_p1(t cn_get_comm_summ_data.comm_summ_tbl_type, a0 out JTF_NUMBER_TABLE
62 , a1 out JTF_VARCHAR2_TABLE_300
63 , a2 out JTF_VARCHAR2_TABLE_100
64 , a3 out JTF_VARCHAR2_TABLE_100
65 , a4 out JTF_VARCHAR2_TABLE_100
66 , a5 out JTF_VARCHAR2_TABLE_300
67 , a6 out JTF_VARCHAR2_TABLE_100
68 , a7 out JTF_VARCHAR2_TABLE_100
69 , a8 out JTF_NUMBER_TABLE
70 , a9 out JTF_NUMBER_TABLE
71 , a10 out JTF_NUMBER_TABLE
72 ) as
73 ddindx binary_integer; indx binary_integer;
74 begin
75 if t is null or t.count = 0 then
76 a0 := JTF_NUMBER_TABLE();
77 a1 := JTF_VARCHAR2_TABLE_300();
78 a2 := JTF_VARCHAR2_TABLE_100();
79 a3 := JTF_VARCHAR2_TABLE_100();
80 a4 := JTF_VARCHAR2_TABLE_100();
81 a5 := JTF_VARCHAR2_TABLE_300();
82 a6 := JTF_VARCHAR2_TABLE_100();
83 a7 := JTF_VARCHAR2_TABLE_100();
84 a8 := JTF_NUMBER_TABLE();
85 a9 := JTF_NUMBER_TABLE();
86 a10 := JTF_NUMBER_TABLE();
87 else
88 a0 := JTF_NUMBER_TABLE();
89 a1 := JTF_VARCHAR2_TABLE_300();
90 a2 := JTF_VARCHAR2_TABLE_100();
91 a3 := JTF_VARCHAR2_TABLE_100();
92 a4 := JTF_VARCHAR2_TABLE_100();
93 a5 := JTF_VARCHAR2_TABLE_300();
94 a6 := JTF_VARCHAR2_TABLE_100();
95 a7 := JTF_VARCHAR2_TABLE_100();
96 a8 := JTF_NUMBER_TABLE();
97 a9 := JTF_NUMBER_TABLE();
98 a10 := JTF_NUMBER_TABLE();
99 if t.count > 0 then
100 a0.extend(t.count);
101 a1.extend(t.count);
102 a2.extend(t.count);
103 a3.extend(t.count);
104 a4.extend(t.count);
105 a5.extend(t.count);
106 a6.extend(t.count);
107 a7.extend(t.count);
108 a8.extend(t.count);
109 a9.extend(t.count);
110 a10.extend(t.count);
111 ddindx := t.first;
112 indx := 1;
113 while true loop
114 a0(indx) := t(ddindx).srp_plan_assign_id;
115 a1(indx) := t(ddindx).name;
116 a2(indx) := t(ddindx).emp_num;
117 a3(indx) := t(ddindx).cost_center;
118 a4(indx) := t(ddindx).charge_to_cost_center;
119 a5(indx) := t(ddindx).analyst_name;
120 a6(indx) := t(ddindx).role_name;
121 a7(indx) := t(ddindx).plan_name;
122 a8(indx) := t(ddindx).begin_balance;
123 a9(indx) := t(ddindx).draw;
124 a10(indx) := t(ddindx).net_due;
125 indx := indx+1;
126 if t.last =ddindx
127 then exit;
128 end if;
129 ddindx := t.next(ddindx);
130 end loop;
131 end if;
132 end if;
133 end rosetta_table_copy_out_p1;
134
135 procedure rosetta_table_copy_in_p3(t out cn_get_comm_summ_data.pe_info_tbl_type, a0 JTF_VARCHAR2_TABLE_100
136 , a1 JTF_NUMBER_TABLE
137 , a2 JTF_NUMBER_TABLE
138 , a3 JTF_NUMBER_TABLE
139 , a4 JTF_NUMBER_TABLE
140 , a5 JTF_NUMBER_TABLE
141 ) as
142 ddindx binary_integer; indx binary_integer;
143 begin
144 if a0 is not null and a0.count > 0 then
145 if a0.count > 0 then
146 indx := a0.first;
147 ddindx := 1;
148 while true loop
149 t(ddindx).quota_group_code := a0(indx);
150 t(ddindx).annual_quota := a1(indx);
151 t(ddindx).pct_annual_quota := a2(indx);
152 t(ddindx).target := a3(indx);
153 t(ddindx).credit := a4(indx);
154 t(ddindx).earnings := a5(indx);
155 ddindx := ddindx+1;
156 if a0.last =indx
157 then exit;
158 end if;
159 indx := a0.next(indx);
160 end loop;
161 end if;
162 end if;
163 end rosetta_table_copy_in_p3;
164 procedure rosetta_table_copy_out_p3(t cn_get_comm_summ_data.pe_info_tbl_type, a0 out JTF_VARCHAR2_TABLE_100
165 , a1 out JTF_NUMBER_TABLE
166 , a2 out JTF_NUMBER_TABLE
167 , a3 out JTF_NUMBER_TABLE
168 , a4 out JTF_NUMBER_TABLE
169 , a5 out JTF_NUMBER_TABLE
170 ) as
171 ddindx binary_integer; indx binary_integer;
172 begin
173 if t is null or t.count = 0 then
174 a0 := JTF_VARCHAR2_TABLE_100();
175 a1 := JTF_NUMBER_TABLE();
176 a2 := JTF_NUMBER_TABLE();
177 a3 := JTF_NUMBER_TABLE();
178 a4 := JTF_NUMBER_TABLE();
179 a5 := JTF_NUMBER_TABLE();
180 else
181 a0 := JTF_VARCHAR2_TABLE_100();
182 a1 := JTF_NUMBER_TABLE();
183 a2 := JTF_NUMBER_TABLE();
184 a3 := JTF_NUMBER_TABLE();
185 a4 := JTF_NUMBER_TABLE();
186 a5 := JTF_NUMBER_TABLE();
187 if t.count > 0 then
188 a0.extend(t.count);
189 a1.extend(t.count);
190 a2.extend(t.count);
191 a3.extend(t.count);
192 a4.extend(t.count);
193 a5.extend(t.count);
194 ddindx := t.first;
195 indx := 1;
196 while true loop
197 a0(indx) := t(ddindx).quota_group_code;
198 a1(indx) := t(ddindx).annual_quota;
199 a2(indx) := t(ddindx).pct_annual_quota;
200 a3(indx) := t(ddindx).target;
201 a4(indx) := t(ddindx).credit;
202 a5(indx) := t(ddindx).earnings;
203 indx := indx+1;
204 if t.last =ddindx
205 then exit;
206 end if;
207 ddindx := t.next(ddindx);
208 end loop;
209 end if;
210 end if;
211 end rosetta_table_copy_out_p3;
212
213 procedure get_quota_summary(p_first NUMBER
214 , p_last NUMBER
215 , p_period_id NUMBER
216 , p_user_id NUMBER
217 , p_credit_type_id NUMBER
218 , x_total_rows out NUMBER
219 , p6_a0 out JTF_NUMBER_TABLE
220 , p6_a1 out JTF_VARCHAR2_TABLE_300
221 , p6_a2 out JTF_VARCHAR2_TABLE_100
222 , p6_a3 out JTF_VARCHAR2_TABLE_100
223 , p6_a4 out JTF_VARCHAR2_TABLE_100
224 , p6_a5 out JTF_VARCHAR2_TABLE_300
225 , p6_a6 out JTF_VARCHAR2_TABLE_100
226 , p6_a7 out JTF_VARCHAR2_TABLE_100
227 , p6_a8 out JTF_NUMBER_TABLE
228 , p6_a9 out JTF_NUMBER_TABLE
229 , p6_a10 out JTF_NUMBER_TABLE
230 )
231 as
232 ddx_result_tbl cn_get_comm_summ_data.comm_summ_tbl_type;
233 ddindx binary_integer; indx binary_integer;
234 begin
235
236 -- copy data to the local IN or IN-OUT args, if any
237
238
239
240
241
242
243
244 -- here's the delegated call to the old PL/SQL routine
245 cn_get_comm_summ_data.get_quota_summary(p_first,
246 p_last,
247 p_period_id,
248 p_user_id,
249 p_credit_type_id,
250 x_total_rows,
251 ddx_result_tbl);
252
253 -- copy data back from the local OUT or IN-OUT args, if any
254
255
256
257
258
259
260 cn_get_comm_summ_data_w.rosetta_table_copy_out_p1(ddx_result_tbl, p6_a0
261 , p6_a1
262 , p6_a2
263 , p6_a3
264 , p6_a4
265 , p6_a5
266 , p6_a6
267 , p6_a7
268 , p6_a8
269 , p6_a9
270 , p6_a10
271 );
272 end;
273
274 procedure get_pe_info(p_srp_plan_assign_id NUMBER
275 , p_period_id NUMBER
276 , p_credit_type_id NUMBER
277 , p3_a0 out JTF_VARCHAR2_TABLE_100
278 , p3_a1 out JTF_NUMBER_TABLE
279 , p3_a2 out JTF_NUMBER_TABLE
280 , p3_a3 out JTF_NUMBER_TABLE
281 , p3_a4 out JTF_NUMBER_TABLE
282 , p3_a5 out JTF_NUMBER_TABLE
283 , p4_a0 out JTF_VARCHAR2_TABLE_100
284 , p4_a1 out JTF_NUMBER_TABLE
285 , p4_a2 out JTF_NUMBER_TABLE
286 , p4_a3 out JTF_NUMBER_TABLE
287 , p4_a4 out JTF_NUMBER_TABLE
288 , p4_a5 out JTF_NUMBER_TABLE
289 , x_ytd_total_earnings out NUMBER
290 , x_ptd_total_earnings out NUMBER
291 )
292 as
293 ddx_ytd_pe_info cn_get_comm_summ_data.pe_info_tbl_type;
294 ddx_ptd_pe_info cn_get_comm_summ_data.pe_info_tbl_type;
295 ddindx binary_integer; indx binary_integer;
296 begin
297
298 -- copy data to the local IN or IN-OUT args, if any
299
300
301
302
303
304
305
306 -- here's the delegated call to the old PL/SQL routine
307 cn_get_comm_summ_data.get_pe_info(p_srp_plan_assign_id,
308 p_period_id,
309 p_credit_type_id,
310 ddx_ytd_pe_info,
311 ddx_ptd_pe_info,
312 x_ytd_total_earnings,
313 x_ptd_total_earnings);
314
315 -- copy data back from the local OUT or IN-OUT args, if any
316
317
318
319 cn_get_comm_summ_data_w.rosetta_table_copy_out_p3(ddx_ytd_pe_info, p3_a0
320 , p3_a1
321 , p3_a2
322 , p3_a3
323 , p3_a4
324 , p3_a5
325 );
326
327 cn_get_comm_summ_data_w.rosetta_table_copy_out_p3(ddx_ptd_pe_info, p4_a0
328 , p4_a1
329 , p4_a2
330 , p4_a3
331 , p4_a4
332 , p4_a5
333 );
334
335
336 end;
337
338 procedure get_group_codes(p0_a0 out JTF_VARCHAR2_TABLE_100
339 , p0_a1 out JTF_NUMBER_TABLE
340 , p0_a2 out JTF_NUMBER_TABLE
341 , p0_a3 out JTF_NUMBER_TABLE
342 , p0_a4 out JTF_NUMBER_TABLE
343 , p0_a5 out JTF_NUMBER_TABLE
344 )
345 as
346 ddx_result_tbl cn_get_comm_summ_data.pe_info_tbl_type;
347 ddindx binary_integer; indx binary_integer;
348 begin
349
350 -- copy data to the local IN or IN-OUT args, if any
351
352 -- here's the delegated call to the old PL/SQL routine
353 cn_get_comm_summ_data.get_group_codes(ddx_result_tbl);
354
355 -- copy data back from the local OUT or IN-OUT args, if any
356 cn_get_comm_summ_data_w.rosetta_table_copy_out_p3(ddx_result_tbl, p0_a0
357 , p0_a1
358 , p0_a2
359 , p0_a3
360 , p0_a4
361 , p0_a5
362 );
363 end;
364
365 end cn_get_comm_summ_data_w;