eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.CSF_SPATIAL_DATALOAD_PVT

Source


1 PACKAGE BODY CSF_SPATIAL_DATALOAD_PVT AS
2 /* $Header: CSFVSDLB.pls 120.6 2011/06/21 08:56:11 srguntur noship $ */
3 
4 /*
5 The following procedure is used to print the log messages.
6 */
7 PROCEDURE dbgl (p_msg_data VARCHAR2)
8  IS
9    i       PLS_INTEGER;
10    l_msg   VARCHAR2 (300);
11 BEGIN
12    i := 1;
13    LOOP
14       l_msg := SUBSTR (p_msg_data, i, 255);
15        EXIT WHEN l_msg IS NULL;
16        EXECUTE IMMEDIATE g_debug_p
17                   USING l_msg;
18    i := i + 255;
19    END LOOP;
20 END dbgl;
21 /*
22 The following procedure is used to print the log messages.
23 */
24 PROCEDURE put_stream (p_handle IN NUMBER, p_msg_data IN VARCHAR2)
25   IS
26 BEGIN
27    IF p_handle = 0
28    THEN
29        dbgl (p_msg_data);
30    ELSIF p_handle = -1
31    THEN
32       IF g_debug
33       THEN
34          dbgl (p_msg_data);
35       END IF;
36    ELSE
37       fnd_file.put_line (p_handle, p_msg_data);
38    END IF;
39 END put_stream;
40 
41 /*
42 The following procedure is used to print the log messages.
43 */
44 /*PROCEDURE CSF_LOG( l_log IN VARCHAR2 )
45  IS
46 BEGIN
47   dbms_output.put_line( l_log );
48 EXCEPTION
49  WHEN OTHERS THEN
50         RETURN;
51 END CSF_LOG; */
52 /*
53  The following procedure drops the index with the help of ad_ddl.do_ddl.
54 */
55 PROCEDURE DROP_INDEX (
56     p_applsys_schema in            VARCHAR2 ,
57     p_app_short_name in            VARCHAR2 ,
58     p_table          in            VARCHAR2 ,
59     p_index          in            VARCHAR2 ,
60     errbuf         OUT NOCOPY      VARCHAR2,
61     retcode        OUT NOCOPY      VARCHAR2
62  )
63 IS
64 BEGIN
65 
66     ad_ddl.do_ddl(
67             applsys_schema          => p_applsys_schema,
68             application_short_name  => p_app_short_name,
69             statement_type          => AD_DDL.DROP_INDEX,
70             statement               => 'DROP INDEX ' || p_index ,
71             object_name             => p_table
72         );
73 
74     --put_stream(g_output, 'Index '|| p_index || ' dropped successfully');
75     put_stream(g_log,'Index '|| p_index || ' dropped successfully');
76     --dbms_output.put_line('Index '|| p_index || ' dropped successfully');
77     --dbms_output.put_line('Index '|| p_index || ' dropped successfully');
78 
79 
80 EXCEPTION
81      WHEN OTHERS THEN
82          IF SQLCODE = -01418 THEN
83             retcode := 1;
84             put_stream(g_log, 'Index ' || p_index || ' on table ' || p_table || ' does not exist');
85             put_stream(g_output, 'Index ' || p_index || ' on table ' || p_table || ' does not exist');
86             --dbms_output.put_line('Index ' || p_index || ' on table ' || p_table || ' does not exist');
87          ELSE
88             retcode := 1;
89             errbuf := SQLERRM;
90             put_stream(g_output, 'Dropping Index ' || p_index || ' failed : ' || SQLCODE||'-'||SQLERRM );
91             put_stream(g_log, 'Dropping Index ' || p_index || ' failed : ' ||SQLCODE||'-'|| SQLERRM );
92          END IF ;
93 END DROP_INDEX;
94 
95 PROCEDURE CREATE_INDEX (
96     p_applsys_schema in VARCHAR2 ,
97     p_app_short_name in VARCHAR2 ,
98     p_table          in VARCHAR2 ,
99     p_index          in VARCHAR2 ,
100     p_columns        in char30_arr ,
101     p_create_sql     in VARCHAR2,
102     errbuf         OUT NOCOPY      VARCHAR2,
103     retcode        OUT NOCOPY      VARCHAR2
104     )
105 IS
106     l_create_index  BOOLEAN ;
107     l_column_name   VARCHAR2(30);
108 
109     CURSOR index_col_cur
110     IS
111     SELECT  column_name
112     FROM    user_ind_columns
113     WHERE   table_name = p_table
114       AND index_name = p_index
115     ORDER BY column_position ;
116 
117 BEGIN
118     open index_col_cur ;
119     FOR i IN p_columns.FIRST..p_columns.COUNT
120     LOOP
121         FETCH index_col_cur INTO l_column_name ;
122         EXIT WHEN index_col_cur%NOTFOUND ;
123         IF l_column_name <> UPPER( p_columns(i) ) THEN
124            l_create_index := TRUE ;
125            exit ;
126         END IF;
127     END LOOP;
128 
129     IF index_col_cur%ROWCOUNT = 0 THEN
130         l_create_index := TRUE ;
131     END IF;
132 
133     CLOSE index_col_cur ;
134 
135     IF l_create_index THEN
136        DROP_INDEX ( p_applsys_schema, p_app_short_name, p_table, p_index, errbuf, retcode );
137        ad_ddl.do_ddl(
138                 applsys_schema          => p_applsys_schema,
139                 application_short_name  => p_app_short_name,
140                 statement_type          => AD_DDL.CREATE_INDEX,
141                 statement               => p_create_sql,
142                 object_name             => p_table
143                 );
144 
145         --put_stream(g_output, 'Index '|| p_index || ' created successfully'  );
146         put_stream(g_log, 'Index  '|| p_index || '  created successfully'  );
147         --dbms_output.put_line('Index '|| p_index || ' created successfully'  );
148 
149     ELSE
150         put_stream(g_log, 'Index ' || p_index || ' on table ' || p_table || ' exists' );
151         --dbms_output.put_line('Index ' || p_index || ' on table ' || p_table || ' exists' );
152     END IF;
153 
154 EXCEPTION
155     WHEN OTHERS THEN
156         IF SQLCODE = -01408 THEN
157             put_stream(g_log,  'Index ' || p_index || ' on table ' || p_table || ' already exists' );
158             --dbms_output.put_line( 'Index ' || p_index || ' on table ' || p_table || ' already exists' );
159         ELSIF SQLCODE = -00955 THEN
160             put_stream(g_log, 'Index ' || p_index || ' on table ' || p_table || ' already exists');
161             --dbms_output.put_line('Index ' || p_index || ' on table ' || p_table || ' already exists');
162         ELSE
163             retcode := 1;
164             errbuf := SQLERRM;
165             put_stream(g_log, 'Index Creation for ' || p_index || ' failed : ' ||SQLCODE||'-'|| SQLERRM );
166             put_stream(g_output, 'Index Creation for ' || p_index || ' failed : ' ||SQLCODE||'-'|| SQLERRM );
167             RAISE FND_API.G_EXC_ERROR;
168             --dbms_output.put_line( 'Index Creation for ' || p_index || ' failed : ' || SQLERRM );
169         END IF ;
170 
171 END CREATE_INDEX;
172 
173 
174 
175 PROCEDURE DROP_INDEXES(
176       p_data_set_name IN          VARCHAR2,
177       p_index_type    IN          VARCHAR2,
178       errbuf          OUT NOCOPY  VARCHAR2,
179       retcode         OUT NOCOPY  VARCHAR2 )
180 IS
181     CURSOR CSF_GET_SPATIAL_INDEXES (p_index_type   in  VARCHAR2)
182     IS
183     SELECT INDEX_NAME,
184           table_name
185     FROM  CSF_SPATIAL_INDEX_STAT_M
186     WHERE index_type = p_index_type AND index_type<>'MAT';
187 
188     l_index_name        VARCHAR2(100);
189     l_applsys_schema    VARCHAR2(10);
190     l_app_short_name    VARCHAR2(20);
191     l_table             VARCHAR2(60);
192     l_data_set_name        VARCHAR2(40);
193 
194 BEGIN
195 
196     l_applsys_schema := upper( 'APPS' ) ;
197     l_data_set_name := p_data_set_name;
198 
199     OPEN CSF_GET_SPATIAL_INDEXES (p_index_type);
200     LOOP
201         FETCH CSF_GET_SPATIAL_INDEXES
202         INTO l_index_name,
203              l_table;
204         EXIT WHEN CSF_GET_SPATIAL_INDEXES%NOTFOUND;
205         -- Materialized view indexes are created in APPS schema.
206         -- All other indexes are created in CSF schema
207         IF p_index_type  = 'MAT' THEN
208             l_app_short_name := upper( 'APPS' ) ;
209         ELSE
210             l_app_short_name := upper( 'CSF' ) ;
211         END IF;
212 
213         IF p_index_type  <> 'WOM' THEN
214           IF ( p_index_type =  'MAT'  AND l_table NOT LIKE 'CSF_WOM%' ) THEN
215              l_index_name := l_index_name || l_data_set_name;
216              l_table := l_table || l_data_set_name ||'_V';
217           ELSIF l_table  NOT LIKE 'CSF_WOM%' THEN
218              l_index_name := l_index_name || l_data_set_name;
219              l_table := l_table || l_data_set_name;
220           END IF;
221         END IF;
222 
223             DROP_INDEX ( l_applsys_schema, l_app_short_name, l_table , l_index_name, errbuf, retcode );
224 
225     END LOOP;
226 
227     CLOSE CSF_GET_SPATIAL_INDEXES;
228 
229 EXCEPTION
230     WHEN NO_DATA_FOUND
231     THEN
232        CLOSE CSF_GET_SPATIAL_INDEXES;
233        retcode := 1;
234        errbuf := SQLERRM;
235        put_stream(g_log,'DROP_INDEXES PROCEDURE HAS FAILED FOR '|| p_index_type || 'TYPE INDEXES' ||  SQLERRM);
236        put_stream(g_output,'DROP_INDEXES PROCEDURE HAS FAILED FOR '|| p_index_type || 'TYPE INDEXES' ||  SQLERRM);
237        RAISE FND_API.G_EXC_ERROR;
238 
239     WHEN OTHERS
240     THEN
241        retcode := 1;
242        errbuf := SQLERRM;
243        put_stream(g_log,'DROP_INDEXES PROCEDURE HAS FAILED '||SQLCODE||'-'|| SQLERRM);
244        CLOSE CSF_GET_SPATIAL_INDEXES;
245        RAISE FND_API.G_EXC_ERROR;
246 
247 END DROP_INDEXES;
248 
249 
250 PROCEDURE REFRESH_MAT_VIEW (
251          p_mv_name      in              VARCHAR2 ,
252          errbuf         OUT NOCOPY      VARCHAR2 ,
253          retcode        OUT NOCOPY      VARCHAR2 )
254 IS
255 BEGIN
256     DBMS_MVIEW.REFRESH(p_mv_name);
257     put_stream(g_output, 'Refresh of ' || p_mv_name || ' is successfull ');
258     put_stream(g_log, 'Refresh of ' || p_mv_name || ' is successfull ');
259 
260 EXCEPTION
261      WHEN OTHERS THEN
262        retcode := 1;
263        errbuf := SQLERRM;
264        put_stream(g_output, 'Refresh of ' || p_mv_name || ' failed : ' ||SQLCODE||'-'|| SQLERRM );
265        put_stream(g_log, 'Refresh of ' || p_mv_name || ' failed : ' || SQLCODE||'-'||SQLERRM );
266        RAISE FND_API.G_EXC_ERROR;
267 END REFRESH_MAT_VIEW;
268 
269 PROCEDURE REFRESH_MAT_VIEWS(
270       p_data_set_name IN             VARCHAR2,
271       errbuf         OUT NOCOPY      VARCHAR2,
272       retcode        OUT NOCOPY      VARCHAR2 )
273 IS
274     l_data_set_name        VARCHAR2(40);
275 BEGIN
276 
277     put_stream(g_log, '  ' );
278     put_stream(g_log, 'Start of Procedure REFRESH_MAT_VIEWS ' );
279     put_stream(g_log, '================================================ ' );
280 
281     l_data_set_name  := p_data_set_name;
282 
283     REFRESH_MAT_VIEW('CSF_MD_ADM_BOUNDS_MAT' ||l_data_set_name || '_V'   , errbuf, retcode);
284     REFRESH_MAT_VIEW('CSF_MD_HYDROS_MAT'     ||l_data_set_name || '_V'   , errbuf, retcode);
285     REFRESH_MAT_VIEW('CSF_MD_LAND_USES_MAT'  ||l_data_set_name || '_V'   , errbuf, retcode);
286     REFRESH_MAT_VIEW('CSF_MD_POIS_MAT'       ||l_data_set_name || '_V'   , errbuf, retcode);
287     REFRESH_MAT_VIEW('CSF_MD_RAIL_SEGS_MAT'  ||l_data_set_name || '_V'   , errbuf, retcode);
288     REFRESH_MAT_VIEW('CSF_MD_RD_SEGS_FUN0'   ||l_data_set_name || '_V'   , errbuf, retcode);
289     REFRESH_MAT_VIEW('CSF_MD_RD_SEGS_FUN1'   ||l_data_set_name || '_V'   , errbuf, retcode);
290     REFRESH_MAT_VIEW('CSF_MD_RD_SEGS_FUN2'   ||l_data_set_name || '_V'   , errbuf, retcode);
291     REFRESH_MAT_VIEW('CSF_MD_RD_SEGS_FUN3'   ||l_data_set_name || '_V'   , errbuf, retcode);
292     REFRESH_MAT_VIEW('CSF_MD_RD_SEGS_FUN4'   ||l_data_set_name || '_V'   , errbuf, retcode);
293     -- WOM mat view is common for all the datasets.
294     REFRESH_MAT_VIEW('CSF_WOM_ROAD_HIWAY_MAT_V' , errbuf, retcode);
295 
296     put_stream(g_log, 'The procedure REFRESH_MAT_VIEWS has completed successfully');
297     put_stream(g_log, 'Materialized view refresh is successful');
298 
299   EXCEPTION
300     WHEN OTHERS THEN
301          put_stream(g_output,'REFRESH_MAT_VIEW PROCEDURE HAS FAILED' ||SQLCODE||'-'|| SQLERRM );
302          put_stream(g_log, 'REFRESH_MAT_VIEW PROCEDURE HAS FAILED'  || SQLCODE||'-'||SQLERRM);
303          retcode := 1;
304          errbuf := SQLERRM;
305          RAISE FND_API.G_EXC_ERROR;
306          --RETURN ;
307 END REFRESH_MAT_VIEWS;
308 
309 PROCEDURE COMPUTE_STATISTICS(
310       p_data_set_name IN             VARCHAR2,
311       errbuf         OUT NOCOPY      VARCHAR2,
312       retcode        OUT NOCOPY      VARCHAR2 )
313 IS
314     l_data_set_name        VARCHAR2(40);
315 BEGIN
316 
317     put_stream(g_log, '  ' );
318     put_stream(g_log, 'Start of Procedure COMPUTE_STATISTICS ' );
319     put_stream(g_log, '================================================ ' );
320 
321     l_data_set_name  := p_data_set_name;
322 
323     -- Delete the statistics on Map tables.
324     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_LF_BLOCKS'               ||l_data_set_name, 'DELETE');
325     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_LF_NAMES'                ||l_data_set_name, 'DELETE');
326     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_LF_PLACES'               ||l_data_set_name, 'DELETE');
327     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_LF_PLACE_NAMES'          ||l_data_set_name, 'DELETE');
328     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_LF_PLACE_POSTCS'         ||l_data_set_name, 'DELETE');
329     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_LF_POIS'                 ||l_data_set_name, 'DELETE');
330     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_LF_POI_NAMES'            ||l_data_set_name, 'DELETE');
331     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_LF_POSTCODES'            ||l_data_set_name, 'DELETE');
332     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_LF_ROADSEGMENTS'         ||l_data_set_name, 'DELETE');
333     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_LF_ROADSEGM_NAMES'       ||l_data_set_name, 'DELETE');
334     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_LF_ROADSEGM_PLACES'      ||l_data_set_name, 'DELETE');
335     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_LF_ROADSEGM_POSTS'       ||l_data_set_name, 'DELETE');
336 
337     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_MD_ADM_BOUNDS'           ||l_data_set_name, 'DELETE');
338     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_MD_HYDROS'               ||l_data_set_name, 'DELETE');
339     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_MD_LAND_USES'            ||l_data_set_name, 'DELETE');
340     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_MD_NAMES'                ||l_data_set_name, 'DELETE');
341     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_MD_POIS'                 ||l_data_set_name, 'DELETE');
342     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_MD_POI_NM_ASGNS'         ||l_data_set_name, 'DELETE');
343     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_MD_RAIL_SEGS'            ||l_data_set_name, 'DELETE');
344     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_MD_RDSEG_NM_ASGNS'       ||l_data_set_name, 'DELETE');
345     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_MD_RD_SEGS'              ||l_data_set_name, 'DELETE');
346     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_SDM_CTRY_PROFILES'       ||l_data_set_name, 'DELETE');
347     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_SPATIAL_STAT_M'          ||l_data_set_name, 'DELETE');
348     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_SPATIAL_STAT_TILES_M'    ||l_data_set_name, 'DELETE');
349     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_SPATIAL_STREET_TYPES_M'  ||l_data_set_name, 'DELETE');
350     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_SPATIAL_VER_M'           ||l_data_set_name, 'DELETE');
351 
352     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_TDS_BINARY_MAPS'         ||l_data_set_name, 'DELETE');
353     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_TDS_BINARY_TILES'        ||l_data_set_name, 'DELETE');
354     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_TDS_CONDITIONS'          ||l_data_set_name, 'DELETE');
355     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_TDS_COND_SEGS'           ||l_data_set_name, 'DELETE');
356     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_TDS_INTERVALS'           ||l_data_set_name, 'DELETE');
357     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_TDS_NODES'               ||l_data_set_name, 'DELETE');
358     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_TDS_RDBLCK_INTVLS'       ||l_data_set_name, 'DELETE');
359     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_TDS_RDBLCK_SGMNTS'       ||l_data_set_name, 'DELETE');
360     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_TDS_ROADBLOCKS'          ||l_data_set_name, 'DELETE');
364 
361     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_TDS_SEGMENTS'            ||l_data_set_name, 'DELETE');
362     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_TDS_SEGM_NODES'          ||l_data_set_name, 'DELETE');
363     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_TDS_TILES'               ||l_data_set_name, 'DELETE');
365     put_stream(g_log, 'Deleted the statistics on all spatial tables');
366 
367     -- Re-compute the statistics on Map tables.
368     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_LF_BLOCKS'               ||l_data_set_name, 'COMPUTE');
369     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_LF_NAMES'                ||l_data_set_name, 'COMPUTE');
370     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_LF_PLACES'               ||l_data_set_name, 'COMPUTE');
371     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_LF_PLACE_NAMES'          ||l_data_set_name, 'COMPUTE');
372     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_LF_PLACE_POSTCS'         ||l_data_set_name, 'COMPUTE');
373     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_LF_POIS'                 ||l_data_set_name, 'COMPUTE');
374     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_LF_POI_NAMES'            ||l_data_set_name, 'COMPUTE');
375     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_LF_POSTCODES'            ||l_data_set_name, 'COMPUTE');
376     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_LF_ROADSEGMENTS'         ||l_data_set_name, 'COMPUTE');
377     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_LF_ROADSEGM_NAMES'       ||l_data_set_name, 'COMPUTE');
378     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_LF_ROADSEGM_PLACES'      ||l_data_set_name, 'COMPUTE');
379     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_LF_ROADSEGM_POSTS'       ||l_data_set_name, 'COMPUTE');
380 
381     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_MD_ADM_BOUNDS'           ||l_data_set_name, 'COMPUTE');
382     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_MD_HYDROS'               ||l_data_set_name, 'COMPUTE');
383     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_MD_LAND_USES'            ||l_data_set_name, 'COMPUTE');
384     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_MD_NAMES'                ||l_data_set_name, 'COMPUTE');
385     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_MD_POIS'                 ||l_data_set_name, 'COMPUTE');
386     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_MD_POI_NM_ASGNS'         ||l_data_set_name, 'COMPUTE');
387     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_MD_RAIL_SEGS'            ||l_data_set_name, 'COMPUTE');
388     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_MD_RDSEG_NM_ASGNS'       ||l_data_set_name, 'COMPUTE');
389     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_MD_RD_SEGS'              ||l_data_set_name, 'COMPUTE');
390     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_SDM_CTRY_PROFILES'       ||l_data_set_name, 'COMPUTE');
391     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_SPATIAL_STAT_M'          ||l_data_set_name, 'COMPUTE');
392     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_SPATIAL_STAT_TILES_M'    ||l_data_set_name, 'COMPUTE');
393     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_SPATIAL_STREET_TYPES_M'  ||l_data_set_name, 'COMPUTE');
394     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_SPATIAL_VER_M'           ||l_data_set_name, 'COMPUTE');
395 
396     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_TDS_BINARY_MAPS'         ||l_data_set_name, 'COMPUTE');
397     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_TDS_BINARY_TILES'        ||l_data_set_name, 'COMPUTE');
398     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_TDS_CONDITIONS'          ||l_data_set_name, 'COMPUTE');
399     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_TDS_COND_SEGS'           ||l_data_set_name, 'COMPUTE');
400     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_TDS_INTERVALS'           ||l_data_set_name, 'COMPUTE');
401     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_TDS_NODES'               ||l_data_set_name, 'COMPUTE');
402     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_TDS_RDBLCK_INTVLS'       ||l_data_set_name, 'COMPUTE');
403     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_TDS_RDBLCK_SGMNTS'       ||l_data_set_name, 'COMPUTE');
404     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_TDS_ROADBLOCKS'          ||l_data_set_name, 'COMPUTE');
405     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_TDS_SEGMENTS'            ||l_data_set_name, 'COMPUTE');
406     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_TDS_SEGM_NODES'          ||l_data_set_name, 'COMPUTE');
407     DBMS_DDL.analyze_object('TABLE', 'CSF', 'CSF_TDS_TILES'               ||l_data_set_name, 'COMPUTE');
408 
409     put_stream(g_log, 'Re-computed the statistics on spatial tables');
410     put_stream(g_output, 'Re-computed the statistics on spatial tables');
411     put_stream(g_log, 'The procedure COMPUTE_STATISTICS has completed successfully');
412 
413 EXCEPTION
414         WHEN OTHERS THEN
415         retcode := 1;
416         errbuf := SQLERRM;
417         put_stream(g_output, 'COMPUTE_STATISTICS PROCEDURE HAS FAILED' ||SQLCODE||'-'|| SQLERRM);
418         put_stream(g_log, 'COMPUTE_STATISTICS PROCEDURE HAS FAILED' || SQLCODE||'-'||SQLERRM);
419         RAISE FND_API.G_EXC_ERROR;
420 
421 END COMPUTE_STATISTICS;
422 
423 PROCEDURE CHECK_TABLE_ROW_COUNT(
424       p_data_set_name IN             VARCHAR2,
425       errbuf         OUT NOCOPY      VARCHAR2,
426       retcode        OUT NOCOPY      VARCHAR2 )
427 IS
428 
429    /* CURSOR csf_spatial_stat_m_cur
430    IS
431    SELECT table_name, num_records
432    FROM csf_spatial_stat_m; */
433 
434    TYPE csf_spatial_stat_ref_cur IS REF CURSOR;
435    csf_spatial_stat_m_cur csf_spatial_stat_ref_cur;
436 
437    CURSOR TAB_Cur (p_table_name TAB.TNAME%TYPE)
438    IS
439    SELECT COUNT(1)
440    FROM TAB
441    WHERE TNAME = p_table_name;
442 
443    TYPE row_count_refcur IS REF CURSOR;
444    row_count_cur        row_count_refcur;
445    v_table_name VARCHAR2 (100);
446    v_record_count NUMBER (10);
447    v_actual_record_count NUMBER (10);
448    v_query VARCHAR2 (2000);
449    v_table_exists NUMBER(10);
450    flagCount NUMBER (10) DEFAULT 0;
451    l_data_set_name        VARCHAR2(40);
452 
453 BEGIN
454 
455     put_stream(g_log, '  ' );
459     l_data_set_name  := p_data_set_name;
456     put_stream(g_log, 'Start of Procedure CHECK_TABLE_ROW_COUNT ' );
457     put_stream(g_log, '================================================ ' );
458 
460 
461    v_query := 'SELECT table_name, num_records FROM csf_spatial_stat_m' || l_data_set_name ;
462    OPEN csf_spatial_stat_m_cur for v_query;
463    LOOP
464       FETCH csf_spatial_stat_m_cur
465       INTO v_table_name, v_record_count;
466       EXIT WHEN csf_spatial_stat_m_cur%NOTFOUND;
467 
468       v_table_name := v_table_name || l_data_set_name;
469 
470       OPEN TAB_Cur(v_table_name);
471       FETCH TAB_Cur INTO v_table_exists;
472       CLOSE TAB_Cur;
473 
474       IF v_table_exists = 0  THEN
475          put_stream(g_log, 'TABLE ' || v_table_name || ' DOES NOT EXIST');
476       ELSE
477          v_query := 'SELECT COUNT(1) FROM ' || v_table_name;
478 
479          OPEN row_count_cur for v_query;
480          FETCH row_count_cur INTO v_actual_record_count;
481          CLOSE row_count_cur;
482          --dbms_output.put_line(v_table_name || ' Actual Count: '|| v_actual_record_count|| 'Table Count: '|| v_record_count );
483          IF v_actual_record_count <> v_record_count  THEN
484             put_stream(g_log, v_table_name || ' is not having correct count. Actual Count: '|| v_actual_record_count|| 'Table Count: '|| v_record_count );
485             --dbms_output.put_line(v_table_name || ' is not having correct count. Actual Count: '|| v_actual_record_count|| 'Table Count: '|| v_record_count );
486             flagCount := flagCount + 1;
487          END IF;
488       END IF;
489     END LOOP;
490     CLOSE csf_spatial_stat_m_cur;
491 
492     IF flagCount = 0   THEN
493        put_stream(g_log, 'NO MISMATCH IN TABLE ROW COUNT -- ALL THE TABLES HAVE BEEN LOADED PROPERLY ');
494        put_stream(g_output, 'NO MISMATCH IN TABLE ROW COUNT -- ALL THE TABLES HAVE BEEN LOADED PROPERLY ');
495        --dbms_output.put_line('NO MISMATCH IN TABLE ROW COUNT -- ALL THE TABLES HAVE BEEN LOADED PROPERLY ');
496     ELSE
497       put_stream(g_log, 'FOUND MISMATCH IN TABLE ROW COUNT');
498       put_stream(g_output, 'FOUND MISMATCH IN TABLE ROW COUNT');
499       RAISE FND_API.G_EXC_ERROR;
500     END IF;
501 
502     put_stream(g_log, 'The procedure CHECK_TABLE_ROW_COUNT has completed successfully');
503     --dbms_output.put_line('The procedure CHECK_TABLE_ROW_COUNT has completed successfully');
504  EXCEPTION
505    WHEN NO_DATA_FOUND
506    THEN
507       CLOSE csf_spatial_stat_m_cur;
508       retcode := 1;
509       errbuf := SQLERRM;
510       put_stream(g_log,'CHECK_TABLE_ROW_COUNT PROCEDURE HAS FAILED '|| SQLCODE||'-'||SQLERRM);
511       put_stream(g_output,'CHECK_TABLE_ROW_COUNT PROCEDURE HAS FAILED '|| SQLCODE||'-'||SQLERRM);
512       RAISE FND_API.G_EXC_ERROR;
513 
514    WHEN OTHERS
515    THEN
516       CLOSE csf_spatial_stat_m_cur;
517       retcode := 1;
518       errbuf := SQLERRM;
519       put_stream(g_log,'CHECK_TABLE_ROW_COUNT PROCEDURE HAS FAILED '||SQLCODE||'-'|| SQLERRM);
520       put_stream(g_output,'CHECK_TABLE_ROW_COUNT PROCEDURE HAS FAILED '||SQLCODE||'-'|| SQLERRM);
521       RAISE FND_API.G_EXC_ERROR;
522 
523 END CHECK_TABLE_ROW_COUNT;
524 
525 PROCEDURE VALIDATE_BLOB_SIZE(
526       p_data_set_name IN             VARCHAR2,
527       errbuf         OUT NOCOPY      VARCHAR2,
528       retcode        OUT NOCOPY      VARCHAR2 )
529 IS
530     TYPE VALIDATE_BLOB_SIZE_CUR_REF IS REF CURSOR;
531     VALIDATE_BLOB_SIZE_CUR VALIDATE_BLOB_SIZE_CUR_REF;
532 
533     TYPE CSF_SPATIAL_STAT_LENGTH_REF IS REF CURSOR;
534     CSF_SPATIAL_STAT_LENGTH_CHECK CSF_SPATIAL_STAT_LENGTH_REF;
535 
536     v_query1 VARCHAR2 (2000);
537     v_query2 VARCHAR2 (2000);
538 
539     v_binary_tile_id          NUMBER(10);
540     v_segment_lob_size        NUMBER(10);
541     v_node_lob_size           NUMBER(10);
542     v_actual_segment_lob_size NUMBER(10);
543     v_actual_node_lob_size    NUMBER(10);
544     flagSegment               NUMBER(10) DEFAULT 0;
545     flagNode                  NUMBER(10) DEFAULT 0;
546 
547     l_data_set_name        VARCHAR2(40);
548 BEGIN
549 
550     put_stream(g_log, '  ' );
551     put_stream(g_log, 'Start of Procedure VALIDATE_BLOB_SIZE ' );
552     put_stream(g_log, '================================================ ' );
553 
554     l_data_set_name  := p_data_set_name;
555 
556     v_query1 := 'SELECT BINARY_TILE_ID, SEGMENT_LOB_SIZE, NODE_LOB_SIZE
557                  FROM CSF_SPATIAL_STAT_TILES_M' || l_data_set_name;
558 
559     OPEN CSF_SPATIAL_STAT_LENGTH_CHECK FOR v_query1;
560     LOOP
561     FETCH CSF_SPATIAL_STAT_LENGTH_CHECK INTO v_binary_tile_id, v_segment_lob_size, v_node_lob_size;
562       EXIT
563       WHEN CSF_SPATIAL_STAT_LENGTH_CHECK%NOTFOUND;
564 
565       v_query2 := 'SELECT dbms_lob.getlength(SEGMENTS), dbms_lob.getlength(NODES)
566                    FROM CSF_TDS_BINARY_TILES'|| l_data_set_name ||
567                  ' WHERE BINARY_TILE_ID  = ' || v_binary_tile_id ;
568 
569       OPEN VALIDATE_BLOB_SIZE_CUR FOR v_query2;
570       FETCH VALIDATE_BLOB_SIZE_CUR INTO v_actual_segment_lob_size, v_actual_node_lob_size;
571       CLOSE VALIDATE_BLOB_SIZE_CUR;
572 
573       IF v_actual_segment_lob_size <> v_segment_lob_size THEN
574         put_stream(g_log,' SEGMENT SIZE DOESNOT MATCH FOR BINARY TILE ID ' || v_binary_tile_id );
575         put_stream(g_log,'Truncate CSF_TDS_BINARY_TILES table and reload it with Navteq import software using BLOBImport group');
576         put_stream(g_output,' SEGMENT SIZE DOESNOT MATCH FOR BINARY TILE ID ' || v_binary_tile_id );
577         put_stream(g_output,'Truncate CSF_TDS_BINARY_TILES table and reload it with Navteq import software using BLOBImport group');
578         flagSegment := flagSegment + 1;
579       END IF;
580 
581       IF v_actual_node_lob_size <> v_node_lob_size THEN
585         put_stream(g_output,'Truncate CSF_TDS_BINARY_TILES table and reload it with Navteq import software using BLOBImport group');
582         put_stream(g_log,' NODE SIZE DOESNOT MATCH FOR BINARY TILE ID ' || v_binary_tile_id );
583         put_stream(g_log,'Truncate CSF_TDS_BINARY_TILES table and reload it with Navteq import software using BLOBImport group');
584         put_stream(g_output,' NODE SIZE DOESNOT MATCH FOR BINARY TILE ID ' || v_binary_tile_id );
586         flagNode := flagNode + 1;
587       END IF;
588 
589     END LOOP;
590 
591     IF flagSegment = 0 THEN
592       put_stream(g_log, ' NO MISMATCH FOR SEGMENT ');
593       put_stream(g_output, ' NO MISMATCH FOR SEGMENT ');
594       --dbms_output.put_line(' NO MISMATCH FOR SEGMENT ');
595     ELSE
596         put_stream(g_log, 'FOUND MISMATCH FOR SEGMENT ');
597         put_stream(g_log, 'FOUND MISMATCH FOR SEGMENT ');
598         RAISE FND_API.G_EXC_ERROR;
599     END IF;
600 
601     IF flagNode = 0 THEN
602       put_stream(g_log, ' NO MISMATCH FOR NODE ');
603       put_stream(g_output, ' NO MISMATCH FOR NODE ');
604       --dbms_output.put_line(' NO MISMATCH FOR NODE ');
605     ELSE
606         put_stream(g_log, 'FOUND MISMATCH FOR NODE ');
607         put_stream(g_log, 'FOUND MISMATCH FOR NODE ');
608         RAISE FND_API.G_EXC_ERROR;
609     END IF;
610 
611    CLOSE CSF_SPATIAL_STAT_LENGTH_CHECK;
612 
613     put_stream(g_log, 'The procedure VALIDATE_BLOB_SIZE has completed successfully');
614 
615  EXCEPTION
616    WHEN NO_DATA_FOUND
617    THEN
618       CLOSE CSF_SPATIAL_STAT_LENGTH_CHECK;
619       CLOSE VALIDATE_BLOB_SIZE_CUR;
620       retcode := 1;
621       errbuf := SQLERRM;
622       put_stream(g_log,'VALIDATE_BLOB_SIZE PROCEDURE HAS FAILED '||SQLCODE||'-'|| SQLERRM);
623       put_stream(g_output,'VALIDATE_BLOB_SIZE PROCEDURE HAS FAILED '||SQLCODE||'-'|| SQLERRM);
624       RAISE FND_API.G_EXC_ERROR;
625 
626    WHEN OTHERS
627    THEN
628       CLOSE CSF_SPATIAL_STAT_LENGTH_CHECK;
629       CLOSE VALIDATE_BLOB_SIZE_CUR;
630       retcode := 1;
631       errbuf := SQLERRM;
632       put_stream(g_log,'VALIDATE_BLOB_SIZE PROCEDURE HAS FAILED '||SQLCODE||'-'|| SQLERRM);
633       put_stream(g_output,'VALIDATE_BLOB_SIZE PROCEDURE HAS FAILED '||SQLCODE||'-'|| SQLERRM);
634       RAISE FND_API.G_EXC_ERROR;
635 
636 END VALIDATE_BLOB_SIZE;
637 
638 PROCEDURE CHECK_INDEX_VALIDITY(
639       p_data_set_name IN             VARCHAR2,
640       p_index_type   IN              VARCHAR2,
641       p_status       OUT NOCOPY      VARCHAR2,
642       errbuf         OUT NOCOPY      VARCHAR2,
643       retcode        OUT NOCOPY      VARCHAR2 )
644 IS
645   CURSOR csf_index_stat_validity_check (p_owner in VARCHAR2,
646                                          p_index_name in VARCHAR2)
647   IS
648   SELECT status, domidx_opstatus
649   FROM all_indexes
650   WHERE owner = p_owner
651     AND (index_name LIKE 'CSF_MD%' OR index_name LIKE 'CSF_LF%' OR index_name LIKE 'CSF_TDS%' OR INDEX_NAME like 'CSF_WOM%')
652     AND index_name = p_index_name;
653 
654   CURSOR CSF_GET_SPATIAL_INDEXES (p_index_type   in  VARCHAR2)
655   IS
656   SELECT INDEX_NAME
657   FROM CSF_SPATIAL_INDEX_STAT_M
658   WHERE index_type = p_index_type AND index_type<>'MAT';
659 
660   l_index_name        VARCHAR2(100);
661   l_status            VARCHAR2(20);
662   l_domidx_opstatus   VARCHAR2(20);
663   l_app_short_name    VARCHAR2(20);
664   l_flagInvalidCount  REAL DEFAULT 0;
665 
666   l_data_set_name        VARCHAR2(40);
667 
668 BEGIN
669 
670     OPEN CSF_GET_SPATIAL_INDEXES (p_index_type);
671     LOOP
672         FETCH CSF_GET_SPATIAL_INDEXES INTO l_index_name;
673         EXIT WHEN CSF_GET_SPATIAL_INDEXES%NOTFOUND;
674 
675         l_data_set_name  := p_data_set_name;
676 
677         IF l_index_name NOT LIKE  'CSF_WOM%' THEN
678             l_index_name := l_index_name || l_data_set_name;
679         END IF;
680 
681 
682         IF l_index_name IS NOT NULL THEN
683 
684             -- Materialized view indexes are part of APPS schema and all other indexes are part of CSF schema.
685             IF p_index_type  = 'MAT' THEN
686                 l_app_short_name := upper( 'APPS' ) ;
687             ELSE
688                 l_app_short_name := upper( 'CSF' ) ;
689             END IF;
690 
691             OPEN  csf_index_stat_validity_check(l_app_short_name,l_index_name);
692             FETCH  csf_index_stat_validity_check INTO l_status, l_domidx_opstatus ;
693                 IF csf_index_stat_validity_check%NOTFOUND THEN
694                    l_flagInvalidCount := l_flagInvalidCount + 1;
695                    put_stream(g_log,' INDEX ' || l_index_name || ' NOT FOUND' );
696                END IF;--IF csf_index_stat_validity_check%NOTFOUND
697 
698              --  IF l_status IS NOT NULL AND l_domidx_opstatus IS NOT NULL THEN
699                    IF l_index_name LIKE 'CSF_MD_%' THEN
700                       IF NOT(l_domidx_opstatus = NULL OR l_domidx_opstatus = 'VALID') THEN
701                          l_flagInvalidCount := l_flagInvalidCount + 1;
702                          put_stream(g_log,' DOMIDX_OPSTATUS: INVALID FOR INDEX: ' || l_index_name);
703                        END IF;
704                     END IF;
705 
706                     IF NOT(l_status = 'VALID') THEN
707                          put_stream(g_log,' STATUS: INVALID FOR INDEX: ' || l_index_name);
708                          l_flagInvalidCount := l_flagInvalidCount + 1;
709                     END IF;
710               -- END IF; -- l_status IS NOT NULL AND l_domidx_opstatus IS NOT NULL
711         END IF; -- l_index_name IS NOT NULL AND l_index_type IS NOT NULL
712         CLOSE csf_index_stat_validity_check;
713     END LOOP;
714     IF l_flagInvalidCount = 0 THEN
718     END IF;
715       put_stream(g_log,' ALL '|| p_index_type || ' INDEXES ARE VALID ');
716       put_stream(g_output,' ALL '|| p_index_type || ' INDEXES ARE VALID ');
717       p_status := 0;
719     CLOSE CSF_GET_SPATIAL_INDEXES;
720 
721 EXCEPTION
722    WHEN NO_DATA_FOUND
723    THEN
724        CLOSE CSF_INDEX_STAT_VALIDITY_CHECK;
725        CLOSE CSF_GET_SPATIAL_INDEXES;
726        retcode := 1;
727        errbuf := SQLERRM;
728        put_stream(g_log,'CHECK_INDEX_VALIDIDTY PROCEDURE HAS FAILED FOR ' || p_index_type || ' INDEXES' ||SQLCODE||'-'|| SQLERRM);
729        RAISE FND_API.G_EXC_ERROR;
730 
731    WHEN OTHERS
732    THEN
733        retcode := 1;
734        errbuf := SQLERRM;
735        put_stream(g_log,'CHECK_INDEX_VALIDIDTY PROCEDURE HAS FAILED '||SQLCODE||'-'|| SQLERRM);
736        CLOSE CSF_INDEX_STAT_VALIDITY_CHECK;
737        CLOSE CSF_GET_SPATIAL_INDEXES;
738        RAISE FND_API.G_EXC_ERROR;
739 
740 END CHECK_INDEX_VALIDITY;
741 
742 PROCEDURE RECREATE_INVALID_INDEXES(
743       p_data_set_name IN             VARCHAR2,
744       p_tablespace   IN              VARCHAR2,
745       p_index_type   IN              VARCHAR2,
746       errbuf         OUT NOCOPY      VARCHAR2,
747       retcode        OUT NOCOPY      VARCHAR2 )
748 IS
749   CURSOR csf_index_stat_validity_check (p_owner in VARCHAR2,
750                                          p_index_name in VARCHAR2)
751   IS
752   SELECT status, domidx_opstatus
753   FROM all_indexes
754   WHERE owner = p_owner
755     AND (index_name LIKE 'CSF_MD%' OR index_name LIKE 'CSF_LF%' OR index_name LIKE 'CSF_TDS%' OR INDEX_NAME like 'CSF_WOM%')
756     AND index_name = p_index_name;
757 
758   CURSOR CSF_GET_SPATIAL_INDEXES (p_index_type   in  VARCHAR2)
759   IS
760   SELECT INDEX_NAME
761   FROM CSF_SPATIAL_INDEX_STAT_M
762   WHERE index_type = p_index_type AND index_type<>'MAT';
763 
764   l_index_name        VARCHAR2(100);
765   l_index_name_temp  VARCHAR2(100);
766   l_status            VARCHAR2(20);
767   l_domidx_opstatus   VARCHAR2(20);
768   l_app_short_name    VARCHAR2(20);
769   l_flagInvalidCount  REAL DEFAULT 0;
770   l_data_set_name        VARCHAR2(40);
771 BEGIN
772 
773     OPEN CSF_GET_SPATIAL_INDEXES (p_index_type);
774     LOOP
775         FETCH CSF_GET_SPATIAL_INDEXES INTO l_index_name;
776         EXIT WHEN CSF_GET_SPATIAL_INDEXES%NOTFOUND;
777 
778         l_data_set_name  := p_data_set_name;
779 
780         IF l_index_name NOT LIKE 'CSF_WOM%' THEN
781            l_index_name_temp := l_index_name || l_data_set_name;
782         END IF;
783 
784         IF l_index_name IS NOT NULL THEN
785 
786             -- Materialized view indexes are part of APPS schema and all other indexes are part of CSF schema.
787             IF p_index_type  = 'MAT' THEN
788                 l_app_short_name := upper( 'APPS' ) ;
789             ELSE
790                 l_app_short_name := upper( 'CSF' ) ;
791             END IF;
792 
793             OPEN  csf_index_stat_validity_check(l_app_short_name,l_index_name_temp);
794             FETCH  csf_index_stat_validity_check INTO l_status, l_domidx_opstatus ;
795                 IF csf_index_stat_validity_check%NOTFOUND THEN
796                    l_flagInvalidCount := l_flagInvalidCount + 1;
797                    put_stream(g_log,' INDEX ' || l_index_name_temp || ' NOT FOUND' );
798                    put_stream(g_log,'Recreating the  INDEX ' || l_index_name_temp );
799                    RECREATE_INDEX(l_data_set_name,l_index_name,p_tablespace,p_index_type, errbuf,retcode);
800                END IF;--IF csf_index_stat_validity_check%NOTFOUND
801 
802                IF l_status IS NOT NULL AND l_domidx_opstatus IS NOT NULL THEN
803                    IF l_index_name_temp LIKE 'CSF_MD_%' THEN
804                       IF NOT(l_domidx_opstatus = NULL OR l_domidx_opstatus = 'VALID') THEN
805                          l_flagInvalidCount := l_flagInvalidCount + 1;
806                          put_stream(g_log,' DOMIDX_OPSTATUS: INVALID FOR INDEX: ' || l_index_name_temp);
807                          put_stream(g_log,' Recreating the index : '  || l_index_name_temp);
808                          RECREATE_INDEX(l_data_set_name,l_index_name,p_tablespace,p_index_type,errbuf,retcode);
809                        END IF;
810                     END IF;
811 
812                     IF NOT(l_status = 'VALID') THEN
813                          put_stream(g_log,' STATUS: INVALID FOR INDEX: ' || l_index_name_temp);
814                          l_flagInvalidCount := l_flagInvalidCount + 1;
815                          put_stream(g_log,' Recreating the index : '  || l_index_name_temp);
816                          RECREATE_INDEX(l_data_set_name,l_index_name,p_tablespace,p_index_type,errbuf,retcode);
817                     END IF;
818                END IF; -- l_status IS NOT NULL AND l_domidx_opstatus IS NOT NULL
819         END IF; -- l_index_name IS NOT NULL AND l_index_type IS NOT NULL
820         CLOSE csf_index_stat_validity_check;
821     END LOOP;
822     IF l_flagInvalidCount = 0 THEN
823       put_stream(g_log,' ALL '|| p_index_type || ' INDEXES ARE VALID ');
824     END IF;
825     CLOSE CSF_GET_SPATIAL_INDEXES;
826 
827 EXCEPTION
828    WHEN NO_DATA_FOUND
829    THEN
830        CLOSE CSF_INDEX_STAT_VALIDITY_CHECK;
831        CLOSE CSF_GET_SPATIAL_INDEXES;
832        retcode := 1;
833        errbuf := SQLERRM;
834        put_stream(g_log,'RECREATE_INVALID_INDEXES PROCEDURE HAS FAILED FOR ' || p_index_type || ' INDEXES' ||SQLCODE||'-'|| SQLERRM);
835        put_stream(g_output,'RECREATE_INVALID_INDEXES PROCEDURE HAS FAILED FOR ' || p_index_type || ' INDEXES' ||SQLCODE||'-'|| SQLERRM);
836        RAISE FND_API.G_EXC_ERROR;
837 
838    WHEN OTHERS
839    THEN
840        retcode := 1;
841        errbuf := SQLERRM;
845        CLOSE CSF_GET_SPATIAL_INDEXES;
842        put_stream(g_log,'RECREATE_INVALID_INDEXES PROCEDURE HAS FAILED '||SQLCODE||'-'|| SQLERRM);
843        put_stream(g_output,'RECREATE_INVALID_INDEXES PROCEDURE HAS FAILED '||SQLCODE||'-'|| SQLERRM);
844        CLOSE CSF_INDEX_STAT_VALIDITY_CHECK;
846        RAISE FND_API.G_EXC_ERROR;
847 
848 END RECREATE_INVALID_INDEXES;
849 
850 
851 PROCEDURE CREATE_INDEXES(
852       p_data_set_name IN             VARCHAR2,
853       p_tablespace   in              VARCHAR2,
854       p_index_type   in              VARCHAR2,
855       errbuf         OUT NOCOPY      VARCHAR2,
856       retcode        OUT NOCOPY      VARCHAR2 )
857 IS
858 
859    CURSOR CSF_GET_SPATIAL_INDEXES (p_index_type   in  VARCHAR2)
860    IS
861      SELECT INDEX_NAME,
862             table_name ,
863             index_columns,
864             index_create_sql
865      FROM  CSF_SPATIAL_INDEX_STAT_M
866      WHERE index_type = p_index_type AND index_type<>'MAT';
867 
868     l_index_name        VARCHAR2(100);
869     l_applsys_schema    VARCHAR2(10);
870     l_app_short_name    VARCHAR2(20);
871     l_table             VARCHAR2(60);
872     l_index             VARCHAR2(60);
873     l_columns_arr       CHAR30_ARR;
874     l_columns_str       VARCHAR2(200);
875     l_column            VARCHAR2(60);
876     l_tablespace        VARCHAR2(60);
877     l_create_sql        VARCHAR2(4000);
878     l_data_set_name     VARCHAR2(40);
879     i                   NUMBER;
880 
881 BEGIN
882 
883     l_applsys_schema := upper( 'APPS' ) ;
884 
885     -- Creating indexes in APPS schema as the Materialized views
886     -- are created as part of APPS schema.
887     IF p_index_type  = 'MAT' THEN
888         l_app_short_name := upper( 'APPS' ) ;
889     ELSE
890         l_app_short_name := upper( 'CSF' ) ;
891     END IF;
892 
893     l_tablespace     := upper ( p_tablespace );
894 
895     l_data_set_name  := p_data_set_name;
896 
897     OPEN CSF_GET_SPATIAL_INDEXES(p_index_type);
898     LOOP
899         FETCH CSF_GET_SPATIAL_INDEXES
900         INTO l_index_name,
901              l_table,
902              l_columns_str,
903              l_create_sql;
904         EXIT WHEN CSF_GET_SPATIAL_INDEXES%NOTFOUND;
905 
906         IF p_index_type  <> 'WOM' THEN
907           IF ( p_index_type =  'MAT'  AND l_table NOT LIKE 'CSF_WOM%' ) THEN
908             l_index_name := l_index_name || l_data_set_name;
909             l_table := l_table || l_data_set_name ||'_V';
910           ELSIF l_table  NOT LIKE 'CSF_WOM%' THEN
911             l_index_name := l_index_name || l_data_set_name;
912             l_table := l_table || l_data_set_name;
913           END IF;
914         END IF;
915 
916         IF l_index_name IS NOT NULL THEN
917             IF l_create_sql IS NOT NULL THEN
918                 select replace(l_create_sql,'l_tablespace',l_tablespace) into l_create_sql from dual;
919                 select replace(l_create_sql,'l_index',l_index_name) into l_create_sql from dual;
920                 select replace(l_create_sql,'l_table',l_table) into l_create_sql from dual;
921              END IF;
922              /* The following block converts the string of comma seperated
923                 index names to table of varchars.
924              */
925              BEGIN
926                  l_columns_arr := CHAR30_ARR();
927                  i := 1;
928                  LOOP
929                  EXIT WHEN INSTR(l_columns_str , ',') = 0 OR INSTR(l_columns_str , ',') is null;
930                    SELECT SUBSTR(l_columns_str, 1, INSTR(l_columns_str , ',')-1) INTO l_column FROM dual;
931                    SELECT SUBSTR(l_columns_str, INSTR(l_columns_str , ',')+1) INTO l_columns_str FROM dual;
932                    l_columns_arr.extend(1);
933                    l_columns_arr(i) := l_column;
934                    i := i + 1 ;
935                  END LOOP;
936                  l_columns_arr.extend(1);
937                  l_columns_arr(i) := l_columns_str;
938               END;
939 
940              CREATE_INDEX ( l_applsys_schema, l_app_short_name, l_table, l_index_name, l_columns_arr, l_create_sql, errbuf, retcode );
941 
942         END IF; -- l_index_name IS NOT NULL
943      END LOOP;
944 
945    CLOSE CSF_GET_SPATIAL_INDEXES;
946 
947 EXCEPTION
948    WHEN NO_DATA_FOUND
949    THEN
950        CLOSE CSF_GET_SPATIAL_INDEXES;
951        retcode := 1;
952        errbuf := SQLERRM;
953        put_stream(g_log,'CREATE_INDEX PROCEDURE HAS FAILED '||SQLCODE||'-'|| SQLERRM);
954        put_stream(g_output,'CREATE_INDEX PROCEDURE HAS FAILED '||SQLCODE||'-'|| SQLERRM);
955        RAISE FND_API.G_EXC_ERROR;
956 
957    WHEN OTHERS
958    THEN
959        retcode := 1;
960        errbuf := SQLERRM;
961        put_stream(g_log,'CREATE_INDEX PROCEDURE HAS FAILED '||SQLCODE||'-'|| SQLERRM);
962        put_stream(g_output,'CREATE_INDEX PROCEDURE HAS FAILED '||SQLCODE||'-'|| SQLERRM);
963        CLOSE CSF_GET_SPATIAL_INDEXES;
964        RAISE FND_API.G_EXC_ERROR;
965 
966 END CREATE_INDEXES;
967 
968 PROCEDURE RECREATE_INDEX (
969         p_data_set_name IN             VARCHAR2,
970         p_index_name   IN              VARCHAR2,
971         p_tablespace   IN              VARCHAR2,
972         p_index_type   IN              VARCHAR2,
973         errbuf         OUT NOCOPY      VARCHAR2,
974         retcode        OUT NOCOPY      VARCHAR2 )
975 IS
976    l_index_type        VARCHAR2(10);
977    l_applsys_schema    VARCHAR2(10);
978    l_app_short_name    VARCHAR2(20);
979    l_table             VARCHAR2(60);
980    l_index_name             VARCHAR2(100);
981    l_columns_arr       CHAR30_ARR;
982    l_columns_str       VARCHAR2(200);
986    i                   NUMBER;
983    l_column            VARCHAR2(60);
984    l_tablespace        VARCHAR2(60) ;
985    l_create_sql        VARCHAR2(4000);
987    l_data_set_name     VARCHAR2(40);
988 
989 BEGIN
990 
991     l_applsys_schema := upper( 'APPS' ) ;
992     l_tablespace := UPPER ( p_tablespace );
993 
994     l_data_set_name  := p_data_set_name;
995 
996     IF p_index_name IS NOT NULL THEN
997 
998       SELECT table_name,
999              index_columns,
1000              index_create_sql,
1001              index_type
1002       INTO   l_table,
1003              l_columns_str,
1004              l_create_sql,
1005              l_index_type
1006       FROM CSF_SPATIAL_INDEX_STAT_M where INDEX_NAME = p_index_name;
1007 
1008       -- Checking indexes in APPS schema as the Materialized views
1009       -- are created as part of APPS schema.
1010       IF l_index_type  = 'MAT' THEN
1011           l_app_short_name := upper( 'APPS' ) ;
1012        ELSE
1013           l_app_short_name := upper( 'CSF' ) ;
1014        END IF;
1015 
1016         IF p_index_type  <> 'WOM' THEN
1017           IF ( p_index_type =  'MAT'  AND l_table NOT LIKE 'CSF_WOM%' ) THEN
1018             l_index_name := l_index_name || l_data_set_name;
1019             l_table := l_table || l_data_set_name ||'_V';
1020           ELSIF l_table  NOT LIKE 'CSF_WOM%' THEN
1021             l_index_name := l_index_name || l_data_set_name;
1022             l_table := l_table || l_data_set_name;
1023           END IF;
1024         END IF;
1025 
1026       IF l_create_sql IS NOT NULL THEN
1027           SELECT replace(l_create_sql,'l_tablespace',l_tablespace) INTO l_create_sql FROM dual;
1028           SELECT replace(l_create_sql,'l_index',p_index_name) INTO l_create_sql FROM dual;
1029           SELECT replace(l_create_sql,'l_table',l_table) INTO l_create_sql FROM dual;
1030       END IF;
1031 
1032      /* The following block converts the string of comma seperated
1033         index names to table of varchars.
1034       */
1035        BEGIN
1036          l_columns_arr := CHAR30_ARR();
1037          i := 1;
1038          LOOP
1039          EXIT WHEN INSTR(l_columns_str , ',') = 0 OR INSTR(l_columns_str , ',') is null;
1040            SELECT SUBSTR(l_columns_str, 1, INSTR(l_columns_str , ',')-1) INTO l_column FROM dual;
1041            SELECT SUBSTR(l_columns_str, INSTR(l_columns_str , ',')+1) INTO l_columns_str FROM dual;
1042            l_columns_arr.extend(1);
1043            l_columns_arr(i) := l_column;
1044            i := i + 1 ;
1045          END LOOP;
1046          l_columns_arr.extend(1);
1047          l_columns_arr(i) := l_columns_str;
1048        END;
1049 
1050     CREATE_INDEX ( l_applsys_schema, l_app_short_name, l_table, l_index_name, l_columns_arr, l_create_sql, errbuf, retcode );
1051 
1052   END IF; --IF v_index_name IS NOT NULL
1053 
1054 EXCEPTION
1055         WHEN OTHERS THEN
1056         retcode := 1;
1057         errbuf := SQLERRM;
1058         put_stream(g_output, 'RECREATE_INDEX PROCEDURE HAS FAILED' ||SQLCODE ||'-'|| SQLERRM);
1059         put_stream(g_log, 'RECREATE_INDEX PROCEDURE HAS FAILED' ||SQLCODE ||'-'|| SQLERRM);
1060         RAISE FND_API.G_EXC_ERROR;
1061 
1062 END RECREATE_INDEX;
1063 
1064 /*   Procedure to drop route cache table.  Fix for bug : 9019583
1065 
1066      When a route is calculated by Time Distance Server (TDS), the route information is stored in CSF_TDS_ROUTE_CACHE table.
1067      When the same route details are requested by Scheduler for the second time, TDS doesn't calculate the route again and
1068      it provides the route by referring the CSF_TDS_ROUTE_CACHE table. The route details are dataset specific and cannotbe
1069      used across the datasets. When a new dataset is loaded, this table data need to be cleared.
1070 */
1071 
1072 PROCEDURE TRUNC_ROUTE_CAHCE_TABLE(
1073       p_data_set_name IN             VARCHAR2,
1074       errbuf         OUT NOCOPY      VARCHAR2,
1075       retcode        OUT NOCOPY      VARCHAR2 )
1076 IS
1077     l_data_set_name        VARCHAR2(40);
1078     l_sch                  VARCHAR2(10);
1079 BEGIN
1080 
1081     put_stream(g_log, '  ' );
1082     put_stream(g_log, 'Start of Procedure TRUNC_ROUTE_CAHCE_TABLE ' );
1083     put_stream(g_log, '================================================ ' );
1084 
1085     l_data_set_name  := p_data_set_name;
1086     l_sch := 'CSF';
1087 
1088     EXECUTE IMMEDIATE  'TRUNCATE TABLE '|| l_sch  || '.CSF_TDS_ROUTE_CACHE' || p_data_set_name;
1089 
1090     put_stream(g_log, 'The procedure TRUNC_ROUTE_CAHCE_TABLE has completed successfully');
1091     put_stream(g_log, 'Truncating table CSF_TDS_ROUTE_CACHE' || p_data_set_name||'  is successful');
1092 
1093   EXCEPTION
1094     WHEN OTHERS THEN
1095          put_stream(g_output,'TRUNC_ROUTE_CAHCE_TABLE PROCEDURE HAS FAILED' ||SQLCODE||'-'|| SQLERRM );
1096          put_stream(g_log, 'TRUNC_ROUTE_CAHCE_TABLE PROCEDURE HAS FAILED'  || SQLCODE||'-'||SQLERRM);
1097          retcode := 1;
1098          errbuf := SQLERRM;
1099          RAISE FND_API.G_EXC_ERROR;
1100          --RETURN ;
1101 END TRUNC_ROUTE_CAHCE_TABLE;
1102 
1103 END CSF_SPATIAL_DATALOAD_PVT;