[Home] [Help]
PACKAGE BODY: APPS.BIS_INDICATOR_REGION_PUB
Source
1 package body BIS_INDICATOR_REGION_PUB AS
2 /* $Header: BISPREGB.pls 115.26 2003/01/30 09:08:44 sugopal ship $ */
3
4 Procedure Create_User_Ind_Selection(
5 p_api_version IN NUMBER,
6 p_Indicator_Region_Rec
7 IN BIS_INDICATOR_REGION_PUB.Indicator_Region_Rec_Type,
8 x_return_status OUT NOCOPY VARCHAR2,
9 x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
10 )
11 IS
12 l_user_id NUMBER;
13 l_user_name VARCHAR2(100);
14 l_user_ind_id NUMBER;
15 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
16 l_rowid VARCHAR2(18);
17 e_CreateException EXCEPTION;
18 cursor c is
19 select rowid from BIS_USER_IND_SELECTIONS
20 where IND_SELECTION_ID = l_user_ind_id;
21
22 BEGIN
23 x_return_status := FND_API.G_RET_STS_SUCCESS;
24
25 BIS_UTILITIES_PUB.Retrieve_User(
26 p_user_id => p_Indicator_Region_Rec.user_id
27 ,p_user_name => p_Indicator_Region_Rec.user_name
28 ,x_user_id => l_user_id
29 ,x_user_name => l_user_name
30 ,x_return_status => x_return_status
31 ,x_error_Tbl => x_error_Tbl
32 );
33
34 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
35 RAISE e_CreateException;
36 END IF;
37
38 BIS_INDICATOR_REGION_PVT.Validate_User_Ind_Selection(
39 p_api_version => 1.0,
40 p_event => 'CREATE',
41 p_user_id => l_user_id,
42 p_Indicator_Region_Rec => p_Indicator_Region_Rec,
43 x_return_status => x_return_status);
44
45 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
46 RAISE e_CreateException;
47 END IF;
48
49 BIS_INDICATOR_REGION_PVT.Create_User_Ind_Selection(
50 p_api_version => 1.0,
51 p_Indicator_Region_Rec => p_Indicator_Region_Rec,
52 x_return_status => x_return_status ,
53 x_error_Tbl => x_error_Tbl);
54
55 EXCEPTION
56 WHEN e_CreateException THEN
57 x_return_status := FND_API.G_RET_STS_ERROR;
58 WHEN OTHERS THEN
59 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
60 l_error_tbl := x_error_Tbl;
61 BIS_UTILITIES_PVT.Add_Error_Message
62 ( p_error_table => l_error_Tbl
63 , p_error_msg_id => SQLCODE
64 , p_error_description => SQLERRM
65 , x_error_table => x_error_Tbl
66 );
67 htp.p('BIS_INDICATOR_REGION_PUB.Create_User_Ind_Selection:'||SQLERRM); htp.para;
68
69 END Create_User_Ind_Selection;
70
71 Procedure Retrieve_User_Ind_Selections(
72 p_api_version IN NUMBER,
73 p_user_id IN NUMBER Default BIS_UTILITIES_PUB.G_NULL_NUM,
74 p_user_name IN VARCHAR2 Default BIS_UTILITIES_PUB.G_NULL_CHAR,
75 p_plug_id IN NUMBER ,
76 p_all_info IN VARCHAR2 Default FND_API.G_TRUE,
77 x_Indicator_Region_Tbl
78 OUT NOCOPY BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type,
79 x_return_status OUT NOCOPY VARCHAR2,
80 x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
81 )
82 IS
83 l_Indicator_Region_rec BIS_INDICATOR_REGION_PUB.Indicator_Region_Rec_Type;
84 e_RetrieveException EXCEPTION;
85 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
86 l_Indicator_Region_Tbl BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type;
87 BEGIN
88 x_return_status := FND_API.G_RET_STS_SUCCESS;
89
90 BIS_UTILITIES_PUB.Retrieve_User(
91 p_user_id => p_user_id
92 ,p_user_name => p_user_name
93 ,x_user_id => l_indicator_region_rec.user_id
94 ,x_user_name => l_indicator_region_rec.user_name
95 ,x_return_status => x_return_status
96 ,x_error_Tbl => x_error_Tbl
97 );
98
99 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
100 RAISE e_RetrieveException;
101 END IF;
102
103 l_Indicator_Region_rec.plug_id := p_plug_id;
104
105 BIS_INDICATOR_REGION_PVT.Validate_User_Ind_Selection(
106 p_api_version => 1.0,
107 p_event => 'RETRIEVE',
108 p_user_id => l_indicator_region_rec.user_id,
109 p_Indicator_Region_Rec => l_Indicator_Region_Rec,
110 x_return_status => x_return_status);
111
112 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
113 RAISE e_RetrieveException;
114 END IF;
115
116 BIS_INDICATOR_REGION_PVT.Retrieve_User_Ind_Selections(
117 p_api_version => 1.0,
118 p_user_id => l_indicator_region_rec.user_id,
119 p_user_name => l_indicator_region_rec.user_name,
120 p_plug_id => l_indicator_region_rec.plug_id,
121 p_all_info => p_all_info,
122 x_Indicator_Region_Tbl => x_Indicator_Region_Tbl,
123 x_return_status => x_return_status ,
124 x_error_Tbl => x_error_Tbl);
125
126 -- mdamle 01/15/2001 - Resequence Dimensions
127 IF (x_Indicator_Region_Tbl.COUNT > 0) THEN
128 l_Indicator_Region_Tbl := x_Indicator_Region_Tbl;
129 FOR l_count IN 1..l_Indicator_Region_Tbl.COUNT LOOP
130 BIS_UTILITIES_PVT.reseq_ind_dim_level_values(l_Indicator_Region_Tbl(l_count),
131 'R',
132 x_Indicator_Region_Tbl(l_count),
133 x_Error_tbl);
134 END LOOP;
135 END IF;
136
137 EXCEPTION
138 WHEN e_RetrieveException THEN
139 x_return_status := FND_API.G_RET_STS_ERROR;
140 WHEN OTHERS THEN
141 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
142 l_error_tbl := x_error_Tbl;
143 BIS_UTILITIES_PVT.Add_Error_Message
144 ( p_error_table => l_error_Tbl
145 , p_error_msg_id => SQLCODE
146 , p_error_description => SQLERRM
147 , x_error_table => x_error_Tbl
148 );
149 htp.p('BIS_INDICATOR_REGION_PUB.Retrieve_User_Ind_Selections:'||SQLERRM);
150 htp.para;
151
152 END Retrieve_User_Ind_Selections;
153
154 Procedure Retrieve_User_Ind_Selections(
155 p_api_version IN NUMBER,
156 p_all_info IN VARCHAR2 Default FND_API.G_TRUE,
157 p_Target_level_rec IN BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type,
158 x_Indicator_Region_Tbl
159 OUT NOCOPY BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type,
160 x_return_status OUT NOCOPY VARCHAR2,
161 x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
162 )
163 IS
164
165 l_Indicator_Region_rec BIS_INDICATOR_REGION_PUB.Indicator_Region_Rec_Type;
166 l_target_level_rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
167 e_RetrieveException EXCEPTION;
168 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
169 l_target_level_rec_p BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
170 l_Indicator_Region_Tbl BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type;
171 BEGIN
172
173 x_return_status := FND_API.G_RET_STS_SUCCESS;
174 l_Target_level_rec := p_Target_level_rec;
175
176 IF BIS_UTILITIES_PUB.VALUE_MISSING(p_Target_level_rec.Target_Level_ID)
177 = FND_API.G_TRUE THEN
178
179 -- mdamle 01/15/2001 - Resequence Dimensions
180 IF (l_target_level_rec.org_level_id IS NOT NULL) AND
181 (l_target_level_rec.time_level_id IS NOT NULL) THEN
182 l_target_level_Rec_p := l_target_level_Rec;
183 BIS_UTILITIES_PVT.resequence_dim_levels(l_target_level_rec_p,
184 'N',
185 l_target_level_Rec,
186 x_Error_tbl);
187 end if;
188
189 l_target_level_Rec_p := l_target_level_Rec;
190 BIS_TARGET_LEVEL_PUB.Retrieve_Target_Level
191 ( p_api_version => 1.0
192 , p_Target_level_rec => l_Target_level_rec_p
193 , p_all_info => FND_API.G_FALSE
194 , x_Target_level_rec => l_Target_level_rec
195 , x_return_status => x_return_status
196 , x_error_Tbl => x_error_Tbl
197 );
198
199 -- mdamle 01/15/2001 - Resequence Dimensions
200 l_target_level_Rec_p := l_target_level_Rec;
201 BIS_UTILITIES_PVT.resequence_dim_levels(l_target_level_rec_p,
202 'R',
203 l_target_level_Rec,
204 x_Error_tbl);
205
206 -- meastmon 07/31/2001
207 --Comment this line which is causing error
208 --htp.p('tar lev aft retrieve: ' ||l_Target_level_rec.Target_Level_ID);
209
210 --dbms_output.put_line('tar lev aft retrieve: '
211 -- ||l_Target_level_rec.Target_Level_ID);
212 END IF;
213
214 BIS_INDICATOR_REGION_PVT.Retrieve_User_Ind_Selections
215 ( p_api_version => 1.0
216 , p_all_info => p_all_info
217 , p_Target_level_id => l_Target_level_rec.Target_Level_ID
218 , x_Indicator_Region_Tbl => x_Indicator_Region_Tbl
219 , x_return_status => x_return_status
220 , x_error_Tbl => x_error_Tbl
221 );
222
223 -- mdamle 01/15/2001 - Resequence Dimensions
224 IF (x_Indicator_Region_Tbl.COUNT > 0) THEN
225 l_Indicator_Region_Tbl := x_Indicator_Region_Tbl;
226 FOR l_count IN 1..l_Indicator_Region_Tbl.COUNT LOOP
227 BIS_UTILITIES_PVT.reseq_ind_dim_level_values(l_Indicator_Region_Tbl(l_count),
228 'R',
229 x_Indicator_Region_Tbl(l_count),
230 x_Error_tbl);
231 END LOOP;
232 END IF;
233
234
235 EXCEPTION
236 WHEN e_RetrieveException THEN
237 x_return_status := FND_API.G_RET_STS_ERROR;
238 WHEN OTHERS THEN
239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
240 l_error_tbl := x_error_Tbl;
241 BIS_UTILITIES_PVT.Add_Error_Message
242 ( p_error_table => l_error_Tbl
243 , p_error_msg_id => SQLCODE
244 , p_error_description => SQLERRM
245 , x_error_table => x_error_Tbl
246 );
247
248 END Retrieve_User_Ind_Selections;
249
250
251 Procedure Update_User_Ind_Selection(
252 p_api_version IN NUMBER,
253 p_user_id IN NUMBER Default BIS_UTILITIES_PUB.G_NULL_NUM,
254 p_user_name IN VARCHAR2 Default BIS_UTILITIES_PUB.G_NULL_CHAR,
255 p_plug_id IN NUMBER ,
256 p_Indicator_Region_Tbl
257 IN BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type,
258 x_return_status OUT NOCOPY VARCHAR2,
259 x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
260 )
261 IS
262 l_user_id NUMBER;
263 l_user_name VARCHAR2(100);
264 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
265
266 e_UpdateException EXCEPTION;
267 BEGIN
268 x_return_status := FND_API.G_RET_STS_SUCCESS;
269
270 BIS_UTILITIES_PUB.Retrieve_User(
271 p_user_id => p_user_id
272 ,p_user_name => p_user_name
273 ,x_user_id => l_user_id
274 ,x_user_name => l_user_name
275 ,x_return_status => x_return_status
276 ,x_error_Tbl => x_error_Tbl
277 );
278
279 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
280 RAISE e_UpdateException;
281 END IF;
282
283 FOR i in 1..p_Indicator_Region_Tbl.count LOOP
284
285 BIS_INDICATOR_REGION_PVT.Validate_User_Ind_Selection(
286 p_api_version => 1.0,
287 p_event => 'UPDATE',
288 p_user_id => l_user_id,
289 p_Indicator_Region_Rec => p_Indicator_Region_Tbl(i),
290 x_return_status => x_return_status);
291
292 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
293 RAISE e_UpdateException;
294 END IF;
295
296 BIS_INDICATOR_REGION_PVT.Update_User_Ind_Selection(
297 p_api_version => 1.0,
298 p_user_id => l_user_id,
299 p_user_name => l_user_name,
300 p_plug_id => p_plug_id,
301 p_Indicator_Region_Rec => p_Indicator_Region_Tbl(i),
302 x_return_status => x_return_status ,
303 x_error_Tbl => x_error_Tbl);
304
305 END LOOP;
306
307 EXCEPTION
308 WHEN e_UpdateException THEN
309 x_return_status := FND_API.G_RET_STS_ERROR;
310 WHEN OTHERS THEN
311 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
312 l_error_tbl := x_error_Tbl;
313 BIS_UTILITIES_PVT.Add_Error_Message
314 ( p_error_table => l_error_Tbl
315 , p_error_msg_id => SQLCODE
316 , p_error_description => SQLERRM
317 , x_error_table => x_error_Tbl
318 );
319 htp.p('BIS_INDICATOR_REGION_PUB.Update_User_Ind_Selection:'||SQLERRM); htp.para;
320
321 END Update_User_Ind_Selection;
322
323
324 Procedure Delete_User_Ind_Selections(
325 p_api_version IN NUMBER,
326 p_user_id IN NUMBER Default BIS_UTILITIES_PUB.G_NULL_NUM,
327 p_user_name IN VARCHAR2 Default BIS_UTILITIES_PUB.G_NULL_CHAR,
328 p_plug_id IN NUMBER,
329 x_return_status OUT NOCOPY VARCHAR2,
330 x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
331 )
332 IS
333 l_Indicator_Region_rec BIS_INDICATOR_REGION_PUB.Indicator_Region_Rec_Type;
334 l_user_id NUMBER;
335 l_user_name VARCHAR2(100);
336 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
337 e_DeleteException EXCEPTION;
338 BEGIN
339 x_return_status := FND_API.G_RET_STS_SUCCESS;
340
341 BIS_UTILITIES_PUB.Retrieve_User(
342 p_user_id => p_user_id
343 ,p_user_name => p_user_name
344 ,x_user_id => l_user_id
345 ,x_user_name => l_user_name
346 ,x_return_status => x_return_status
347 ,x_error_Tbl => x_error_Tbl
348 );
349
350 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
351 RAISE e_DeleteException;
352 END IF;
353
354 l_Indicator_Region_rec.user_id := l_user_id;
355 l_Indicator_Region_rec.user_name := l_user_name;
356 l_Indicator_Region_rec.plug_id := p_plug_id;
357
358 BIS_INDICATOR_REGION_PVT.Validate_User_Ind_Selection(
359 p_api_version => 1.0,
360 p_event => 'DELETE',
361 p_user_id => l_Indicator_Region_Rec.user_id,
362 p_Indicator_Region_Rec => l_Indicator_Region_Rec,
363 x_return_status => x_return_status);
364
365 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
366 RAISE e_DeleteException;
367 END IF;
371 p_user_id => l_indicator_region_rec.user_id,
368
369 BIS_INDICATOR_REGION_PVT.Delete_User_Ind_Selections(
370 p_api_version => 1.0,
375 x_error_Tbl => x_error_Tbl);
372 p_user_name => l_indicator_region_rec.user_name,
373 p_plug_id => l_indicator_region_rec.plug_id,
374 x_return_status => x_return_status ,
376
377 EXCEPTION
378 WHEN e_DeleteException THEN
379 x_return_status := FND_API.G_RET_STS_ERROR;
380 WHEN OTHERS THEN
381 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
382 l_error_tbl := x_error_Tbl;
383 BIS_UTILITIES_PVT.Add_Error_Message
384 ( p_error_table => l_error_Tbl
385 , p_error_msg_id => SQLCODE
386 , p_error_description => SQLERRM
387 , x_error_table => x_error_Tbl
388 );
389 htp.p('BIS_INDICATOR_REGION_PUB.Delete_User_Ind_Selection:'||SQLERRM); htp.para;
390
391 END Delete_User_Ind_Selections;
392
393
394 END BIS_INDICATOR_REGION_PUB;