1 package body BIS_ACTUAL_PUB AS
2 /* $Header: BISPACVB.pls 115.21 2003/05/20 05:26:43 sugopal ship $ */
3
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(30):='BIS_ACTUAL_PUB';
6 --
7
8 -- Retrieves the KPIs users have selected to monitor on the personal homepage
9 -- or in the summary report. This should be called before calling Post_Actual.
10 PROCEDURE Retrieve_User_Selections
11 ( p_api_version IN NUMBER
12 ,p_Target_Level_Rec
13 IN BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type
14 ,x_Indicator_Region_Tbl
15 OUT NOCOPY BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type
16 ,x_return_status OUT NOCOPY VARCHAR2
17 ,x_msg_count OUT NOCOPY NUMBER
18 ,x_msg_data OUT NOCOPY VARCHAR2
19 ,x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
20 )
21 IS
22 l_target_level_rec BIS_TARGET_LEVEL_PUB.TARGET_LEVEL_REC_TYPE;
23 l_indicator_region_tbl BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type;
24
25 BEGIN
26 x_return_status := FND_API.G_RET_STS_SUCCESS;
27 l_Target_level_Rec := p_target_level_rec;
28
29 -- mdamle 01/15/2001 - Resequence the dimensions levels for product teams still using Org and Time
30 IF (l_target_level_rec.org_level_id IS NOT NULL) AND
31 (l_target_level_rec.time_level_id IS NOT NULL) THEN
32 BIS_UTILITIES_PVT.resequence_dim_levels
33 (p_target_level_rec
34 ,'N'
35 ,l_target_level_rec
36 ,x_error_tbl);
37 END IF;
38
39 BIS_INDICATOR_REGION_PUB.Retrieve_User_Ind_Selections
40 ( p_api_version => p_api_version
41 , p_all_info => FND_API.G_TRUE
42 , p_Target_level_Rec => l_Target_level_Rec
43 , x_Indicator_Region_Tbl => x_Indicator_Region_Tbl
44 , x_return_status => x_return_status
45 , x_error_Tbl => x_error_Tbl
46 );
47
48 -- mdamle 01/12/2001 Resequence the dimensions levels for product teams still using Org and Time
49 IF (x_Indicator_Region_Tbl.COUNT > 0) THEN
50 l_indicator_region_tbl := x_Indicator_Region_Tbl;
51 FOR l_count IN 1..l_indicator_region_tbl.COUNT LOOP
52 BIS_UTILITIES_PVT.reseq_ind_dim_level_values(
53 l_indicator_region_tbl(l_count)
54 ,'R'
55 ,x_Indicator_Region_Tbl(l_count)
56 ,x_Error_tbl
57 );
58 END LOOP;
59 END IF;
60
61 EXCEPTION
62 WHEN OTHERS THEN
63 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
64
65 END Retrieve_User_Selections;
66
67
68 -- Posts actual value into BIS table.
69 PROCEDURE Post_Actual
70 ( p_api_version IN NUMBER
71 ,p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
72 ,p_commit IN VARCHAR2 Default FND_API.G_FALSE
73 ,p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
74 ,p_Actual_Rec IN BIS_ACTUAL_PUB.Actual_Rec_Type
75 ,x_return_status OUT NOCOPY VARCHAR2
76 ,x_msg_count OUT NOCOPY NUMBER
77 ,x_msg_data OUT NOCOPY VARCHAR2
78 ,x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
79 )
80 IS
81 l_Actual_Rec BIS_ACTUAL_PUB.Actual_Rec_Type;
82 l_actual_rec_p BIS_ACTUAL_PUB.Actual_Rec_Type;
83 l_Actual_Rec_Validated BIS_ACTUAL_PUB.Actual_Rec_Type; -- 2730145
84 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
85 BEGIN
86
87 -- meastmon 08/23/2001 Bug#1912417
88 -- We need to resequence first and then value id conversion
89
90 l_Actual_Rec := p_Actual_Rec;
91
92 -- mdamle 01/15/2001 - Resequence the dimensions levels for product teams still using Org and Time
93 IF (l_actual_rec.org_level_value_id IS NOT NULL) AND
94 (l_actual_rec.time_level_value_id IS NOT NULL) THEN
95 l_actual_rec_p := l_actual_rec;
96 BIS_UTILITIES_PVT.RESEQ_ACTUAL_DIM_LEVEL_VALUES
97 (p_dim_values_rec => l_actual_rec_p
98 ,p_sequence_dir => 'N'
99 ,x_dim_values_rec => l_actual_rec
100 ,x_error_tbl => x_error_tbl);
101 END IF;
102
103 BIS_ACTUAL_PVT.Value_ID_Conversion
104 ( p_api_version => p_api_version
105 , p_Actual_Rec => l_Actual_Rec
106 , x_Actual_Rec => l_Actual_Rec_Validated -- l_Actual_Rec
107 , x_return_status => x_return_status
108 , x_error_Tbl => x_error_Tbl
109 );
110
111 BIS_ACTUAL_PVT.Post_Actual
112 ( p_api_version => p_api_version
113 , p_commit => p_commit
114 , p_Actual_Rec => l_Actual_Rec_Validated -- l_Actual_Rec
115 , x_return_status => x_return_status
116 , x_error_Tbl => x_error_Tbl
117 );
118
119 EXCEPTION
120 when FND_API.G_EXC_ERROR then
121 --dbms_output.put_line('PUB 1 Exception');
122 x_return_status := FND_API.G_RET_STS_ERROR ;
123 when FND_API.G_EXC_UNEXPECTED_ERROR then
124 --dbms_output.put_line('PUB 2 Exception');
125 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
126 when others then
127 --dbms_output.put_line('PUB 3 Exception');
128 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
129 l_error_tbl := x_error_tbl;
130 BIS_UTILITIES_PVT.Add_Error_Message
131 ( p_error_msg_id => SQLCODE
132 , p_error_description => SQLERRM
133 , p_error_proc_name => G_PKG_NAME||'.Create_Actual'
134 , p_error_table => l_error_tbl
135 , x_error_table => x_error_tbl
136 );
137
138 END Post_Actual;
139
140 PROCEDURE Retrieve_Actual
141 ( p_api_version IN NUMBER
142 ,p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
143 ,p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL ,p_all_info IN VARCHAR2 Default FND_API.G_TRUE
144 ,p_Actual_Rec IN BIS_ACTUAL_PUB.Actual_Rec_Type
145 ,x_Actual_Rec OUT NOCOPY BIS_ACTUAL_PUB.Actual_Rec_Type
146 ,x_return_status OUT NOCOPY VARCHAR2
147 ,x_msg_count OUT NOCOPY NUMBER
148 ,x_msg_data OUT NOCOPY VARCHAR2
149 ,x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
150 )
151 IS
152
153 l_actual_rec BIS_ACTUAL_PUB.Actual_Rec_Type;
154 l_actual_rec_p BIS_ACTUAL_PUB.Actual_Rec_Type;
155
156 BEGIN
157
158 x_return_status := FND_API.G_RET_STS_SUCCESS;
159 -- dbms_output.put_line('In PUB Retrieve_Actual');
160
161 BIS_ACTUAL_PVT.Value_ID_Conversion
162 ( p_api_version => p_api_version
163 , p_Actual_Rec => p_Actual_Rec
164 , x_Actual_Rec => l_Actual_Rec
165 , x_return_status => x_return_status
166 , x_error_Tbl => x_error_Tbl
167 );
168
169 -- mdamle 01/15/2001 - Resequence the dimensions levels for product teams still using Org and Time
170 IF (l_actual_rec.org_level_value_id IS NOT NULL) AND
171 (l_actual_rec.time_level_value_id IS NOT NULL) THEN
172 l_actual_rec_p := l_actual_rec;
173 BIS_UTILITIES_PVT.reseq_actual_dim_level_values(
174 l_actual_rec_p
175 ,'N'
176 ,l_Actual_Rec
177 ,x_error_tbl
178 );
179 END IF;
180
181 BIS_ACTUAL_PVT.Retrieve_Actual
182 ( p_api_version => 1.0
183 , p_all_info => p_all_info
184 , p_Actual_Rec => l_Actual_Rec
185 , x_Actual_rec => x_Actual_rec
186 , x_return_status => x_return_status
187 , x_error_Tbl => x_error_Tbl
188 );
189
190 l_actual_rec_p := x_Actual_Rec;
191
192 -- mdamle 01/12/2001 Resequence the dimensions levels for product teams still using Org and Time
193 BIS_UTILITIES_PVT.reseq_actual_dim_level_values (
194 l_actual_rec_p
195 ,'R'
196 ,x_Actual_Rec
197 ,x_error_tbl
198 );
199
200 EXCEPTION
201 WHEN OTHERS THEN
202 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
203
204 END Retrieve_Actual;
205
206
207 -- Retrieves all actual values for the specified Indicator Level
208 -- i.e. all organizations, all time periods, etc.
209 -- If information about dimension values are not required, set all_info
210 -- to FALSE.
211 PROCEDURE Retrieve_Actuals
212 ( p_api_version IN NUMBER
213 ,p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
214 ,p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
215 ,p_all_info IN VARCHAR2 Default FND_API.G_TRUE
216 ,p_Target_Level_Rec IN BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type
217 ,x_Actual_Tbl OUT NOCOPY BIS_ACTUAL_PUB.Actual_Tbl_Type
218 ,x_return_status OUT NOCOPY VARCHAR2
219 ,x_msg_count OUT NOCOPY NUMBER
220 ,x_msg_data OUT NOCOPY VARCHAR2
221 ,x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
222 )
223 IS
224 BEGIN
225 x_return_status := FND_API.G_RET_STS_SUCCESS;
226 -- dbms_output.put_line('In PUB Retrieve_Actuals');
227
228 EXCEPTION
229 WHEN OTHERS THEN
230 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
231
232 END Retrieve_Actuals;
233
234
235 -- Retrieves the most current actual value for the specified set
236 -- of dimension values. (time level value not necessary.)
237 -- If information about dimension values are not required, set all_info
238 -- to FALSE.
239 PROCEDURE Retrieve_Latest_Actual
240 ( p_api_version IN NUMBER
241 ,p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
242 ,p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
243 ,p_all_info IN VARCHAR2 Default FND_API.G_TRUE
244 ,p_Actual_Rec IN BIS_ACTUAL_PUB.Actual_Rec_Type
245 ,x_Actual_Rec OUT NOCOPY BIS_ACTUAL_PUB.Actual_Rec_Type
246 ,x_return_status OUT NOCOPY VARCHAR2
247 ,x_msg_count OUT NOCOPY NUMBER
248 ,x_msg_data OUT NOCOPY VARCHAR2
249 ,x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
250 )
251 IS
252 BEGIN
253 x_return_status := FND_API.G_RET_STS_SUCCESS;
254 -- dbms_output.put_line('In PUB Retrieve_Latest_Actual');
255
256 EXCEPTION
257 WHEN OTHERS THEN
258 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
259
260 END Retrieve_Latest_Actual;
261
262
263 -- Retrieves the most current actual values for the specified Indicator Level
264 -- i.e. for all organizations, etc. (time level value not necessary.)
265 -- If information about dimension values are not required, set all_info
266 -- to FALSE.
267 PROCEDURE Retrieve_Latest_Actuals
268 ( p_api_version IN NUMBER
269 ,p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
270 ,p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
271 ,p_all_info IN VARCHAR2 Default FND_API.G_TRUE
272 ,p_Target_Level_Rec IN BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type
273 ,x_Actual_Tbl OUT NOCOPY BIS_ACTUAL_PUB.Actual_Tbl_Type
274 ,x_return_status OUT NOCOPY VARCHAR2
275 ,x_msg_count OUT NOCOPY NUMBER
276 ,x_msg_data OUT NOCOPY VARCHAR2
277 ,x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
278 )
279 IS
280 BEGIN
281 x_return_status := FND_API.G_RET_STS_SUCCESS;
282 -- dbms_output.put_line('In PUB Retrieve_Latest_Actuals');
283
284 EXCEPTION
285 WHEN OTHERS THEN
286 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
287
288 END Retrieve_Latest_Actuals;
289
290
291 PROCEDURE Validate_Actual
292 ( p_api_version IN NUMBER
293 , p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
294 , p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
295 , p_event IN VARCHAR2
296 , p_user_id IN NUMBER
297 , p_Actual_Rec IN BIS_ACTUAL_PUB.Actual_Rec_Type
298 , x_return_status OUT NOCOPY VARCHAR2
304 BEGIN
299 , x_msg_count OUT NOCOPY NUMBER
300 , x_msg_data OUT NOCOPY VARCHAR2
301 ,x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
302 )
303 IS
305 x_return_status := FND_API.G_RET_STS_SUCCESS;
306 -- dbms_output.put_line('In PUB Validate_Actual');
307
308
309
310 EXCEPTION
311 WHEN OTHERS THEN
312 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
313
314 END Validate_Actual;
315
316 END BIS_ACTUAL_PUB;