1 PACKAGE BODY BSC_DIM_LEVEL_FILTERS_PVT AS
2 /* $Header: BSCVFILB.pls 120.0.12000000.1 2007/07/17 07:44:42 appldev noship $ */
3 /*
4 REM +=======================================================================+
5 REM | Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA |
6 REM | All rights reserved. |
7 REM +=======================================================================+
8 REM | FILENAME |
9 REM | BSCVFILB.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Module: This Package handle Common Dimension Level for Scorecards |
13 REM | |
14 REM | NOTES |
15 REM | 16-12-2006 PSOMESUL E#5678943 MIGRATE COMMON DIMENSIONS AND DIMENSION FILTERS TO SCORECARD DESIGNER|
16 REM +=======================================================================+
17 */
18
19 PROCEDURE delete_filters
20 (
21 p_tab_id IN NUMBER
22 ,p_dim_level_id IN NUMBER
23 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
24 ,x_return_status OUT NOCOPY VARCHAR2
25 ,x_msg_count OUT NOCOPY NUMBER
26 ,x_msg_data OUT NOCOPY VARCHAR2
27 )
28 IS
29
30 BEGIN
31 IF (p_tab_id is not null and p_dim_level_id is not null) THEN
32 DELETE
33 FROM bsc_sys_filters
34 WHERE source_type = 1
35 AND source_code = p_tab_id
36 AND dim_level_id = p_dim_level_id;
37 END IF;
38
39 IF (p_commit = FND_API.G_TRUE) THEN
40 COMMIT;
41 END IF;
42
43 EXCEPTION
44 WHEN FND_API.G_EXC_ERROR THEN
45 FND_MSG_PUB.Count_And_Get
46 ( p_encoded => FND_API.G_FALSE
47 , p_count => x_msg_count
48 , p_data => x_msg_data
49 );
50
51 x_return_status := FND_API.G_RET_STS_ERROR;
52 RAISE;
53 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
54 FND_MSG_PUB.Count_And_Get
55 ( p_encoded => FND_API.G_FALSE
56 , p_count => x_msg_count
57 , p_data => x_msg_data
58 );
59 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
60
61 RAISE;
62 WHEN NO_DATA_FOUND THEN
63 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
64 IF (x_msg_data IS NOT NULL) THEN
65 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PVT.delete_filters ';
66 ELSE
67 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PVT.delete_filters ';
68 END IF;
69
70 RAISE;
71 WHEN OTHERS THEN
72 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
73 IF (x_msg_data IS NOT NULL) THEN
74 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PVT.delete_filters ';
75 ELSE
76 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PVT.delete_filters ';
77 END IF;
78
79 RAISE;
80
81 END delete_filters;
82
83
84 PROCEDURE insert_filters
85 (
86 p_source_type IN bsc_sys_filters.source_type%TYPE
87 ,p_source_code IN bsc_sys_filters.source_code%TYPE
88 ,p_dim_level_id IN bsc_sys_filters.dim_level_id%TYPE
89 ,p_dim_level_value IN bsc_sys_filters.dim_level_value%TYPE
90 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
91 ,x_return_status OUT NOCOPY VARCHAR2
92 ,x_msg_count OUT NOCOPY NUMBER
93 ,x_msg_data OUT NOCOPY VARCHAR2
94 )
95 IS
96 BEGIN
97 IF (p_source_type IS NOT NULL AND p_source_code IS NOT NULL AND p_dim_level_id IS NOT NULL AND p_dim_level_value IS NOT NULL) THEN
98 INSERT
99 INTO
100 bsc_sys_filters(source_type,
101 source_code,
102 dim_level_id,
103 dim_level_value
104 )
105 VALUES (p_source_type,
106 p_source_code ,
107 p_dim_level_id,
108 p_dim_level_value
109 );
110 IF (p_commit = FND_API.G_TRUE) THEN
111 COMMIT;
112 END IF;
113 END IF;
114
115 EXCEPTION
116 WHEN FND_API.G_EXC_ERROR THEN
117 FND_MSG_PUB.Count_And_Get
118 ( p_encoded => FND_API.G_FALSE
119 , p_count => x_msg_count
120 , p_data => x_msg_data
121 );
122
123 x_return_status := FND_API.G_RET_STS_ERROR;
124 RAISE;
125 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
126 FND_MSG_PUB.Count_And_Get
127 ( p_encoded => FND_API.G_FALSE
128 , p_count => x_msg_count
129 , p_data => x_msg_data
130 );
131 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
132
133 RAISE;
134 WHEN NO_DATA_FOUND THEN
135 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
136 IF (x_msg_data IS NOT NULL) THEN
137 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PVT.insert_filters ';
138 ELSE
139 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PVT.insert_filters ';
140 END IF;
141
142 RAISE;
143 WHEN OTHERS THEN
144 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
145 IF (x_msg_data IS NOT NULL) THEN
146 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PVT.insert_filters ';
147 ELSE
148 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PVT.insert_filters ';
149 END IF;
150
151 RAISE;
152
153 END insert_filters;
154
155
156 PROCEDURE delete_filters_view
157 (
158 p_tab_id IN NUMBER
159 ,p_dim_level_id IN NUMBER
160 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
161 ,x_return_status OUT NOCOPY VARCHAR2
162 ,x_msg_count OUT NOCOPY NUMBER
163 ,x_msg_data OUT NOCOPY VARCHAR2
164 )
165 IS
166
167 BEGIN
168 IF (p_tab_id is not null and p_dim_level_id is not null) THEN
169 DELETE
170 FROM bsc_sys_filters_views
171 WHERE source_type = 1
172 AND source_code = p_tab_id
173 AND dim_level_id = p_dim_level_id;
174 END IF;
175
176 IF (p_commit = FND_API.G_TRUE) THEN
177 COMMIT;
178 END IF;
179
180 EXCEPTION
181 WHEN FND_API.G_EXC_ERROR THEN
182 FND_MSG_PUB.Count_And_Get
183 ( p_encoded => FND_API.G_FALSE
184 , p_count => x_msg_count
185 , p_data => x_msg_data
186 );
187
188 x_return_status := FND_API.G_RET_STS_ERROR;
189 RAISE;
190 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
191 FND_MSG_PUB.Count_And_Get
192 ( p_encoded => FND_API.G_FALSE
193 , p_count => x_msg_count
194 , p_data => x_msg_data
195 );
196 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
197
198 RAISE;
199 WHEN NO_DATA_FOUND THEN
200 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
201 IF (x_msg_data IS NOT NULL) THEN
202 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PVT.delete_filters_view ';
203 ELSE
204 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PVT.delete_filters_view ';
205 END IF;
206
207 RAISE;
208 WHEN OTHERS THEN
209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
210 IF (x_msg_data IS NOT NULL) THEN
211 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PVT.delete_filters_view ';
212 ELSE
213 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PVT.delete_filters_view ';
214 END IF;
215
216 RAISE;
217 END delete_filters_view;
218
219
220 PROCEDURE insert_filters_view
221 (
222 p_source_type IN bsc_sys_filters_views.source_type%TYPE
223 ,p_source_code IN bsc_sys_filters_views.source_code%TYPE
224 ,p_dim_level_id IN bsc_sys_filters_views.dim_level_id%TYPE
225 ,p_level_table_name IN bsc_sys_filters_views.level_table_name%TYPE
226 ,p_level_view_name IN bsc_sys_filters_views.level_view_name%TYPE
227 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
228 ,x_return_status OUT NOCOPY VARCHAR2
229 ,x_msg_count OUT NOCOPY NUMBER
230 ,x_msg_data OUT NOCOPY VARCHAR2
231 )
232 IS
233 BEGIN
234
235 IF (p_source_type IS NOT NULL AND p_source_code IS NOT NULL AND p_dim_level_id IS NOT NULL AND p_level_table_name IS NOT NULL AND p_level_view_name IS NOT NULL) THEN
236 INSERT
237 INTO
238 bsc_sys_filters_views(
239 source_type,
240 source_code,
241 dim_level_id,
242 level_table_name,
243 level_view_name)
244 VALUES (p_source_type,
245 p_source_code,
246 p_dim_level_id,
247 p_level_table_name,
248 p_level_view_name
249 );
250 IF (p_commit = FND_API.G_TRUE) THEN
251 COMMIT;
252 END IF;
253 END IF;
254
255 EXCEPTION
256 WHEN FND_API.G_EXC_ERROR THEN
257 FND_MSG_PUB.Count_And_Get
258 ( p_encoded => FND_API.G_FALSE
259 , p_count => x_msg_count
260 , p_data => x_msg_data
261 );
262
263 x_return_status := FND_API.G_RET_STS_ERROR;
264 RAISE;
265 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
266 FND_MSG_PUB.Count_And_Get
267 ( p_encoded => FND_API.G_FALSE
268 , p_count => x_msg_count
269 , p_data => x_msg_data
270 );
271 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
272
273 RAISE;
274 WHEN NO_DATA_FOUND THEN
275 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
276 IF (x_msg_data IS NOT NULL) THEN
277 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PVT.insert_filters_view ';
278 ELSE
279 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PVT.insert_filters_view ';
280 END IF;
281
282 RAISE;
283 WHEN OTHERS THEN
284 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
285 IF (x_msg_data IS NOT NULL) THEN
286 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PVT.insert_filters_view ';
287 ELSE
288 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PVT.insert_filters_view ';
289 END IF;
290
291 RAISE;
292
293 END insert_filters_view;
294
295 END BSC_DIM_LEVEL_FILTERS_PVT;
[Home] [Help]