[Home] [Help]
PACKAGE BODY: APPS.BIS_DIMLEVEL_ERRS_PVT
Source
1 PACKAGE BODY BIS_DIMLEVEL_ERRS_PVT AS
2 /* $Header: BISVEDEB.pls 115.12 2003/03/27 20:09:22 sashaik ship $ */
3 /*
4 REM +=======================================================================+
5 REM | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA |
6 REM | All rights reserved. |
7 REM +=======================================================================+
8 REM | FILENAME |
9 REM | BISVGDLS.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Private API for getting the Select String for DimensionLevelValues|
13 REM | This API will get the Select String from either EDW or BIS |
14 REM | depending on the profile option BIS_SOURCE |
15 REM | |
16 REM | HISTORY |
17 REM | December-2000 amkulkar Creation |
18 REM +=======================================================================+
19 */
20 --
21 -- CONSTANTS
22 EDW_ACCT_FLEXFIELD VARCHAR2(200) := 'EDW_GL';
23 EDW_LVL_TBL_SUFFIX VARCHAR2(200) := '_LTC';
24 EDW_LVL_FLEX_PK_SUFFIX VARCHAR2(200) := '_NAME';
25 G_PKG_NAME VARCHAR2(200) := 'BIS_PMF_GET_DIMLEVELS_PVT';
26 --Copied from JK's utilities blindly almost
27 PROCEDURE FILE_OPEN
28 (p_file_name IN VARCHAR2
29 ,x_file_handle OUT NOCOPY UTL_FILE.FILE_TYPE
30 )
31 IS
32 l_dir VARCHAR2(32000);
33
34 CURSOR c_file IS
35 SELECT vp.value
36 FROM v$parameter vp
37 WHERE vp.name = 'utl_file_dir';
38
39 l_file_name VARCHAR2(32000);
40
41 BEGIN
42 IF (p_file_name IS NULL) THEN
43 l_file_name := EDW_ERRORS;
44 ELSE
45 l_file_name := p_file_name;
46 END IF;
47 OPEN c_file;
48 FETCH c_file INTO l_dir;
49 CLOSE c_file;
50 IF instr(l_dir,',') > 0 THEN
51 l_dir := substr(l_dir,1,instr(l_dir,',')-1);
52 END IF;
53 IF UTL_FILE.IS_OPEN(x_file_handle) THEN
54 UTL_FILE.FCLOSE(x_file_handle);
55 END IF;
56 x_file_handle := UTL_FILE.FOPEN(l_dir, l_file_name, 'w');
57 EXCEPTION
58 WHEN UTL_FILE.INVALID_PATH THEN
59 RAISE_APPLICATION_ERROR(-20100, 'Invalid PAth');
60 WHEN UTL_FILE.INVALID_MODE THEN
61 RAISE_APPLICATION_ERROR(-20101,'Invalid Mode');
62 WHEN UTL_FILE.INVALID_OPERATION THEN
63 RAISE_APPLICATION_ERROR(-20101,' Invalid operation');
64 END;
65 --Copied from JK's utilities blindly almost
66 PROCEDURE WRITE_TO_FILE
67 (p_text IN VARCHAR2
68 ,p_file_handle IN UTL_FILE.FILE_TYPE
69 )
70 IS
71 BEGIN
72 UTL_FILE.putf(p_file_handle, '%s \n', p_text);
73 UTL_FILE.fflush(p_file_handle);
74 EXCEPTION
75 WHEN UTL_FILE.INVALID_OPERATION THEN
76 RAISE_APPLICATION_ERROR(-20101, 'Invalid Operation');
77 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
78 RAISE_APPLICATION_ERROR(-20103,
79 'Debug: Invalid File Handle');
80 WHEN UTL_FILE.WRITE_ERROR THEN
81 RAISE_APPLICATION_ERROR(-20104,
82 'Debug: Write Error');
83 END;
84 PROCEDURE REPORT_ERRORS
85 (p_Dim_Level_Name IN VARCHAR2 DEFAULT NULL
86 ,p_file_name IN VARCHAR2 DEFAULT NULL
87 )
88 IS
89 l_lvlshortname VARCHAR2(32000);
90 l_pkkey VARCHAR(2000);
91 l_valuename VARCHAR2(2000);
92 l_tablename VARCHAR2(2000);
93 l_distinct VARCHAR2(2000);
94 l_select_string VARCHAR2(32000);
95 l_temp_tablename VARCHAR2(32000);
96 l_sql_string VARCHAR2(32000);
97
98 /*CURSOR c_dimlvls IS
99 SELECT
100 lvl.name lvlshortname, lvl.longname lvlname, lvl.description lvldesc
101 ,lvl.prefix prefix
102 FROM
103 cmplevel_v lvl
104 WHERE
105 (lvl.name = p_dim_level_name OR p_dim_level_name IS NULL)
106 ;
107 CURSOR c_dims(p_lvlshort_name IN varchar2) IS
108 SELECT dim.name dimshortname
109 FROM cmpwbdimension_v dim, cmplevel_v lvl
110 WHERE
111 lvl.name=p_lvlshort_name AND
112 lvl.dimension = dim.elementid
113 ;
114
115 CURSOR c_pkkey IS
116 SELECT item.name
117 FROM cmpitem_v item, cmprelation_v rel,
118 cmpwbitemsetusage_v isu, cmpuniquekey_v pk
119 WHERE pk.owningrelation=rel.elementid
120 and isu.itemset=pk.elementid
121 and isu.attribute=item.elementid
122 and upper(rel.name) = upper(l_lvlshortname )
123 and item.name like '%PK_KEY';
124
125 CURSOR c_pkkey IS
126 SELECT level_table_col_name
127 FROM edw_level_Table_atts_md_v
128 WHERE key_type='UK' AND
129 upper(level_Table_name) = upper(l_lvlshortname||'_LTC') AND
130 level_table_col_name like '%PK_KEY%';
131 i*/
132 l_time_columns VARCHAR2(2000);
133 l_err_file_handle UTL_FILE.FILE_TYPE;
134 l_success_file_handle UTL_FILE.FILE_TYPE;
135 l_error_text VARCHAR2(32000);
136 l_success_text VARCHAR2(32000);
137 l_dimshortname VARCHAR2(32000);
138 l_success_file VARCHAR2(32000) := 'DIMLEVELSUCCESS.log';
139 l_status VARCHAR2(32000);
140 l_pkkey_sql VARCHAR2(32000);
141 l_dim_level_sql VARCHAR2(32000);
142 l_dim_sql VARCHAR2(32000);
143 l_lvlshortname_ltc VARCHAR2(32000);
144 l_longname VARCHAR2(32000);
145 l_description VARCHAR2(32000);
146 l_prefix VARCHAR2(32000);
147 TYPE dimlvls_cursor_type IS REF CURSOR;
148 l_dimlvls_cursor dimlvls_cursor_type;
149
150 BEGIN
151 l_pkkey_sql := ' SELECT level_table_col_name '||
152 --2245747 ' FROM edw_level_Table_atts_md_v '||
153 ' FROM EDW_LVL_TBL_UK_MD_V '|| --2245747
154 ' WHERE key_type=''UK'' AND '||
155 ' upper(level_Table_name) = upper(:l_lvlshortname_ltc) AND '||
156 ' level_table_col_name like ''%PK_KEY%''';
157 -- Fix for 2214178 starts
158 /*
159 l_dim_sql := ' SELECT dim.name dimshortname '||
160 ' FROM cmpwbdimension_v dim, cmplevel_v lvl '||
161 ' WHERE '||
162 ' lvl.name=:p_lvlshort_name AND '||
163 ' lvl.dimension = dim.elementid ';
164 l_dim_level_sql := ' SELECT '||
165 ' lvl.name lvlshortname, lvl.longname lvlname, lvl.description lvldesc '||
166 ' ,lvl.prefix prefix '||
167 ' FROM '||
168 ' cmplevel_v lvl '||
169 ' WHERE '||
170 ' (lvl.name = :1 OR :2 IS NULL) ';
171 */
172 l_dim_sql := ' SELECT dim.dim_name dimshortname '||
173 ' FROM edw_dimensions_md_v dim, edw_levels_md_v lvl '||
174 ' WHERE '||
175 ' lvl.level_name=:p_lvlshort_name AND '||
176 ' lvl.dim_id = dim.dim_id ';
177
178 l_dim_level_sql := ' SELECT '||
179 ' lvl.LEVEL_NAME lvlshortname, lvl.LEVEL_LONG_NAME lvlname, lvl.DESCRIPTION lvldesc '||
180 ' ,lvl.LEVEL_PREFIX prefix '||
181 ' FROM '||
182 ' edw_levels_md_v lvl '||
183 ' WHERE '||
184 ' (lvl.LEVEL_NAME = :1 OR :2 IS NULL) ';
185
186 -- Fix for 2214178 ends
187 FILE_OPEN(p_File_name, l_err_file_handle);
188 FILE_OPEN(l_success_file, l_success_file_handle);
189 OPEN l_dimlvls_cursor FOR l_dim_level_sql USING p_dim_level_name,p_dim_level_name;
190 --FOR c_rec IN c_dimlvls LOOP
191 LOOP
192 FETCH l_dimlvls_cursor INTO l_lvlshortname, l_longname, l_description, l_prefix;
193 EXIT WHEN l_dimlvls_cursor%NOTFOUND;
194 l_status := FND_API.G_RET_STS_SUCCESS;
195 --l_error_text := c_rec.lvlshortname || ' : ';
196 --l_lvlshortname := c_Rec.lvlshortname;
197 l_error_text := l_lvlshortname || ' : ';
198 /*OPEN c_dims(l_lvlshortname);
199 FETCH c_dims INTO l_dimshortname;
200 CLOSE c_dims;
201 */
202 EXECUTE IMMEDIATE l_dim_sql INTO l_dimshortname USING l_lvlshortname;
203 IF (BIS_PMF_GET_DIMLEVELS_PVT.isAccounting_Flexfield(l_lvlshortname))
204 THEN
205 --l_pkkey := c_rec.prefix||EDW_LVL_FLEX_PK_SUFFIX;
206 l_pkkey := l_prefix||EDW_LVL_FLEX_PK_SUFFIX;
207 l_tablename := l_dimshortname;
208 l_distinct := ' DISTINCT ';
209 l_valuename := L_prefix||EDW_LVL_FLEX_PK_SUFFIX;
210 l_sql_string := 'SELECT '||l_pkkey||' from '||l_tablename|| ' where rownum < 2';
211 BEGIN
212 EXECUTE IMMEDIATE l_sql_string ;
213 EXCEPTION
214 WHEN OTHERS THEN
215 IF (SQLCODE= -904) THEN
216 FND_MESSAGE.SET_NAME('BIS','BIS_INVALID_EDW_PK_KEY');
217 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME',l_lvlshortname);
218 l_status := FND_API.G_RET_STS_ERROR;
219 l_error_text := l_lvlshortname || ' : ';
220 l_error_text := l_error_text || FND_MESSAGE.GET;
221 WRITE_TO_FILE(l_error_text, l_err_file_handle);
222 END IF;
223 END;
224 ELSE
225 /*OPEN c_pkkey;
226 FETCH c_pkkey INTO l_pkkey;
227 IF c_pkkey%NOTFOUND THEN
228 FND_MESSAGE.SET_NAME('BIS','BIS_INVALID_EDW_PK_KEY');
229 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME',l_lvlshortname);
230 l_status := FND_API.G_RET_STS_ERROR;
231 l_error_text := l_lvlshortname || ' : ';
232 l_error_text := l_error_text || FND_MESSAGE.GET;
233 WRITE_TO_FILE(l_error_text, l_err_file_handle);
234 END IF;
235 CLOSE c_pkkey;
236 */
237 l_lvlshortname_ltc := l_lvlshortname || '_LTC';
238 BEGIN
239 EXECUTE IMMEDIATE l_pkkey_sql INTO l_pkkey USING l_lvlshortname_ltc;
240 EXCEPTION
241 WHEN NO_DATA_FOUND THEN
242 FND_MESSAGE.SET_NAME('BIS','BIS_INVALID_EDW_PK_KEY');
243 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME',l_lvlshortname);
244 l_status := FND_API.G_RET_STS_ERROR;
245 l_error_text := l_lvlshortname || ' : ';
246 l_error_text := l_error_text || FND_MESSAGE.GET;
247 WRITE_TO_FILE(l_error_text, l_err_file_handle);
248 END;
249 l_tablename := l_lvlshortname || EDW_LVL_TBL_SUFFIX ;
250 l_sql_string := 'SELECT '||l_pkkey||' from '||l_tablename|| ' where rownum < 2';
251 BEGIN
252 EXECUTE IMMEDIATE l_sql_string ;
253 EXCEPTION
254 WHEN OTHERS THEN
255 IF (SQLCODE = -942) THEN
256 FND_MESSAGE.SET_NAME ('BIS', 'BIS_NO_LTC_TABLE');
257 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME', p_dim_level_name);
258 l_status := FND_API.G_RET_STS_ERROR;
259 l_error_text := l_lvlshortname || ' : ';
260 l_error_text := l_error_text || FND_MESSAGE.GET;
261 WRITE_TO_FILE(l_error_text, l_err_file_handle);
262 END IF;
263 IF (SQLCODE= -904) THEN
264 FND_MESSAGE.SET_NAME ('BIS', 'BIS_INVALID_EDW_PK_KEY');
265 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME', p_dim_level_name);
266 l_status := FND_API.G_RET_STS_ERROR;
267 l_error_text := l_lvlshortname || ' : ';
268 l_error_text := l_error_text || FND_MESSAGE.GET;
269 WRITE_TO_FILE(l_error_text, l_err_file_handle);
270 END IF;
271 END;
272 l_valuename := ' NAME ';
273 END IF;
274 --IF (l_dimshortname = 'EDW_TIME_M')
275 IF ((l_dimshortname = 'EDW_TIME_M') AND
276 (l_lvlshortname <> BIS_UTILITIES_PVT.GET_TOTAL_DIMLEVEL_NAME(p_dim_short_name=>l_dimshortname
277 ,p_DimLevelId => NULL
278 ,p_DimLevelName => l_lvlshortname)))
279 THEN
280 l_time_columns := ' ,start_date, end_date ';
281 l_sql_string := 'SELECT start_date from '||l_tablename||' where rownum < 2';
282 BEGIN
283 EXECUTE IMMEDIATE l_sql_string ;
284 EXCEPTION
285 WHEN OTHERS THEN
286 IF (SQLCODE= -904) THEN
287 FND_MESSAGE.SET_NAME('BIS','BIS_INVALID_START_DATE');
288 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME',l_lvlshortname);
289 l_status := FND_API.G_RET_STS_ERROR;
290 l_error_text := l_lvlshortname || ' : ';
291 l_error_text := l_error_text || FND_MESSAGE.GET;
292 WRITE_TO_FILE(l_error_text, l_err_file_handle);
293 END IF;
294 END;
295 l_sql_string := 'SELECT end_date from '||l_tablename||' where rownum < 2';
296 BEGIN
297 EXECUTE IMMEDIATE l_sql_string ;
298 EXCEPTION
299 WHEN OTHERS THEN
300 IF (SQLCODE= -904) THEN
301 FND_MESSAGE.SET_NAME('BIS','BIS_INVALID_EDW_END_DATE');
302 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME',l_lvlshortname);
303 l_status := FND_API.G_RET_STS_ERROR;
304 l_error_text := l_lvlshortname || ' : ';
305 l_error_text := l_error_text || FND_MESSAGE.GET;
306 WRITE_TO_FILE(l_error_text, l_err_file_handle);
307 END IF;
308 END;
309 ELSE
310 l_time_columns := '';
311 END IF;
312 -- IF (NOT(BIS_PMF_GET_DIMLEVELS_PVT.isAccounting_Flexfield(l_lvlshortname))) THEN
313 l_sql_string := 'SELECT '||l_valuename||' from '||l_tablename|| ' where rownum < 2';
314 BEGIN
315 EXECUTE IMMEDIATE l_sql_string ;
316 EXCEPTION
317 WHEN OTHERS THEN
318 IF (SQLCODE = -942) THEN
319 FND_MESSAGE.SET_NAME ('BIS', 'BIS_NO_LTC_TABLE');
320 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME', l_lvlshortname);
321 l_status := FND_API.G_RET_STS_ERROR;
322 l_error_text := l_lvlshortname || ' : ';
323 l_error_text := l_error_text || FND_MESSAGE.GET;
324 WRITE_TO_FILE(l_error_text, l_err_file_handle);
325 END IF;
326 IF (SQLCODE= -904) THEN
327 FND_MESSAGE.SET_NAME ('BIS', 'BIS_INVALID_VALUE');
328 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME', l_lvlshortname);
329 l_status := FND_API.G_RET_STS_ERROR;
330 l_error_text := l_lvlshortname || ' : ';
331 l_error_text := l_error_text || FND_MESSAGE.GET;
332 WRITE_TO_FILE(l_error_text, l_err_file_handle);
333 END IF;
334 END;
335 --END IF;
336 IF (l_status = FND_API.G_RET_STS_SUCCESS) THEN
337 FND_MESSAGE.SET_NAME ('BIS', 'BIS_NO_ERRORS');
338 l_success_text := l_lvlshortname || ' ' || FND_MESSAGE.GET;
339 WRITE_TO_FILE(l_success_text , l_success_file_handle);
340 END IF;
341 END LOOP;
342 utl_file.fclose(l_success_file_handle);
343 utl_file.fclose(l_err_file_handle);
344 /*
345 EXCEPTION
346 WHEN OTHERS THEN
347 utl_file.fclose(l_err_file_handle);
348 utl_file.fclose(l_success_file_handle);
349 */
350 END;
351
352 END BIS_DIMLEVEL_ERRS_PVT;