[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_GRP_SUM_PUB_W
Source
1 package body jtf_rs_grp_sum_pub_w as
2 /* $Header: jtfrsrgb.pls 120.0 2005/05/11 08:21:37 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 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 NOCOPY jtf_rs_grp_sum_pub.grp_sum_tbl_type, a0 JTF_NUMBER_TABLE
23 , a1 JTF_VARCHAR2_TABLE_100
24 , a2 JTF_VARCHAR2_TABLE_300
25 , a3 JTF_VARCHAR2_TABLE_100
26 , a4 JTF_DATE_TABLE
27 , a5 JTF_DATE_TABLE
28 , a6 JTF_VARCHAR2_TABLE_100
29 , a7 JTF_NUMBER_TABLE
30 , a8 JTF_VARCHAR2_TABLE_100
31 , a9 JTF_NUMBER_TABLE
32 ) as
33 ddindx binary_integer; indx binary_integer;
34 begin
35 if a0 is not null and a0.count > 0 then
36 if a0.count > 0 then
37 indx := a0.first;
38 ddindx := 1;
39 while true loop
40 t(ddindx).group_id := a0(indx);
41 t(ddindx).group_name := a1(indx);
42 t(ddindx).group_desc := a2(indx);
43 t(ddindx).group_number := a3(indx);
44 t(ddindx).start_date_active := rosetta_g_miss_date_in_map(a4(indx));
45 t(ddindx).end_date_active := rosetta_g_miss_date_in_map(a5(indx));
46 t(ddindx).parent_group := a6(indx);
47 t(ddindx).parent_group_id := a7(indx);
48 t(ddindx).child_group := a8(indx);
49 t(ddindx).child_group_id := a9(indx);
50 ddindx := ddindx+1;
51 if a0.last =indx
52 then exit;
53 end if;
54 indx := a0.next(indx);
55 end loop;
56 end if;
57 end if;
58 end rosetta_table_copy_in_p1;
59 procedure rosetta_table_copy_out_p1(t jtf_rs_grp_sum_pub.grp_sum_tbl_type, a0 out NOCOPY JTF_NUMBER_TABLE
60 , a1 out NOCOPY JTF_VARCHAR2_TABLE_100
61 , a2 out NOCOPY JTF_VARCHAR2_TABLE_300
62 , a3 out NOCOPY JTF_VARCHAR2_TABLE_100
63 , a4 out NOCOPY JTF_DATE_TABLE
64 , a5 out NOCOPY JTF_DATE_TABLE
65 , a6 out NOCOPY JTF_VARCHAR2_TABLE_100
66 , a7 out NOCOPY JTF_NUMBER_TABLE
67 , a8 out NOCOPY JTF_VARCHAR2_TABLE_100
68 , a9 out NOCOPY JTF_NUMBER_TABLE
69 ) as
70 ddindx binary_integer; indx binary_integer;
71 begin
72 if t is null or t.count = 0 then
73 a0 := JTF_NUMBER_TABLE();
74 a1 := JTF_VARCHAR2_TABLE_100();
75 a2 := JTF_VARCHAR2_TABLE_300();
76 a3 := JTF_VARCHAR2_TABLE_100();
77 a4 := JTF_DATE_TABLE();
78 a5 := JTF_DATE_TABLE();
79 a6 := JTF_VARCHAR2_TABLE_100();
80 a7 := JTF_NUMBER_TABLE();
81 a8 := JTF_VARCHAR2_TABLE_100();
82 a9 := JTF_NUMBER_TABLE();
83 else
84 a0 := JTF_NUMBER_TABLE();
85 a1 := JTF_VARCHAR2_TABLE_100();
86 a2 := JTF_VARCHAR2_TABLE_300();
87 a3 := JTF_VARCHAR2_TABLE_100();
88 a4 := JTF_DATE_TABLE();
89 a5 := JTF_DATE_TABLE();
90 a6 := JTF_VARCHAR2_TABLE_100();
91 a7 := JTF_NUMBER_TABLE();
92 a8 := JTF_VARCHAR2_TABLE_100();
93 a9 := JTF_NUMBER_TABLE();
94 if t.count > 0 then
95 a0.extend(t.count);
96 a1.extend(t.count);
97 a2.extend(t.count);
98 a3.extend(t.count);
99 a4.extend(t.count);
100 a5.extend(t.count);
101 a6.extend(t.count);
102 a7.extend(t.count);
103 a8.extend(t.count);
104 a9.extend(t.count);
105 ddindx := t.first;
106 indx := 1;
107 while true loop
108 a0(indx) := t(ddindx).group_id;
109 a1(indx) := t(ddindx).group_name;
110 a2(indx) := t(ddindx).group_desc;
111 a3(indx) := t(ddindx).group_number;
112 a4(indx) := t(ddindx).start_date_active;
113 a5(indx) := t(ddindx).end_date_active;
114 a6(indx) := t(ddindx).parent_group;
115 a7(indx) := t(ddindx).parent_group_id;
116 a8(indx) := t(ddindx).child_group;
117 a9(indx) := t(ddindx).child_group_id;
118 indx := indx+1;
119 if t.last =ddindx
120 then exit;
121 end if;
122 ddindx := t.next(ddindx);
123 end loop;
124 end if;
125 end if;
126 end rosetta_table_copy_out_p1;
127
128 procedure get_group(p_range_low NUMBER
129 , p_range_high NUMBER
130 , p_called_from VARCHAR2
131 , p_user_id NUMBER
132 , p_group_name VARCHAR2
133 , p_group_number VARCHAR2
134 , p_group_desc VARCHAR2
135 , p_group_email VARCHAR2
136 , p_from_date VARCHAR2
137 , p_to_date VARCHAR2
138 , p_date_format VARCHAR2
139 , p_group_id NUMBER
140 , p_group_usage VARCHAR2
141 , x_total_rows out NOCOPY NUMBER
142 , p14_a0 out NOCOPY JTF_NUMBER_TABLE
143 , p14_a1 out NOCOPY JTF_VARCHAR2_TABLE_100
144 , p14_a2 out NOCOPY JTF_VARCHAR2_TABLE_300
145 , p14_a3 out NOCOPY JTF_VARCHAR2_TABLE_100
146 , p14_a4 out NOCOPY JTF_DATE_TABLE
147 , p14_a5 out NOCOPY JTF_DATE_TABLE
148 , p14_a6 out NOCOPY JTF_VARCHAR2_TABLE_100
149 , p14_a7 out NOCOPY JTF_NUMBER_TABLE
150 , p14_a8 out NOCOPY JTF_VARCHAR2_TABLE_100
151 , p14_a9 out NOCOPY JTF_NUMBER_TABLE
152 )
153 as
154 ddx_result_tbl jtf_rs_grp_sum_pub.grp_sum_tbl_type;
155 ddindx binary_integer; indx binary_integer;
156 begin
157
158 -- copy data to the local IN or IN-OUT args, if any
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174 -- here's the delegated call to the old PL/SQL routine
175 jtf_rs_grp_sum_pub.get_group(p_range_low,
176 p_range_high,
177 p_called_from,
178 p_user_id,
179 p_group_name,
180 p_group_number,
181 p_group_desc,
182 p_group_email,
183 p_from_date,
184 p_to_date,
185 p_date_format,
186 p_group_id,
187 p_group_usage,
188 x_total_rows,
189 ddx_result_tbl);
190
191 -- copy data back from the local OUT or IN-OUT args, if any
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206 jtf_rs_grp_sum_pub_w.rosetta_table_copy_out_p1(ddx_result_tbl, p14_a0
207 , p14_a1
208 , p14_a2
209 , p14_a3
210 , p14_a4
211 , p14_a5
212 , p14_a6
213 , p14_a7
214 , p14_a8
215 , p14_a9
216 );
217 end;
218
219 end jtf_rs_grp_sum_pub_w;