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_TTSP_PVT

Source


1 PACKAGE BODY CSF_SPATIAL_TTSP_PVT AS
2 /* $Header: CSFVTTSPB.pls 120.2 2011/06/20 17:56:14 srguntur noship $ */
3 
4   PROCEDURE ttsp_plugin(p_directory VARCHAR2) IS
5     l_dir VARCHAR2(100) := 'TTS_NAVTEQ_2008';
6   BEGIN
7     EXECUTE IMMEDIATE 'CREATE  OR REPLACE directory ' || l_dir || ' as ' || p_directory;
8 
9   END;
10 
11   /*   The following procedure is used to print the log messages.*/
12   PROCEDURE dbgl(p_msg_data VARCHAR2) IS
13     i pls_integer;
14     l_msg VARCHAR2(300);
15   BEGIN
16     i := 1;
17     LOOP
18       l_msg := SUBSTR(p_msg_data,   i,   255);
19       EXIT  WHEN l_msg IS NULL;
20 
21       EXECUTE IMMEDIATE g_debug_p USING l_msg;
22       i := i + 255;
23     END LOOP;
24   END dbgl;
25 
26   /*  The following procedure is used to print the log messages.  */
27   PROCEDURE put_stream(p_handle IN NUMBER,
28                        p_msg_data IN VARCHAR2)
29   IS
30   BEGIN
31 
32     IF p_handle = 0 THEN
33       dbgl(p_msg_data);
34       ELSIF p_handle = -1 THEN
35         IF g_debug THEN
36           dbgl(p_msg_data);
37         END IF;
38       ELSE
39         fnd_file.PUT_LINE(p_handle, p_msg_data);
40       END IF;
41     --dbms_output.put_line(p_msg_data);
42   END put_stream;
43 
44   /*The following procedure is used to print the log messages.*/
45 
46   /*PROCEDURE CSF_LOG( p_handle IN NUMBER,l_log IN VARCHAR2 )
47   IS
48   BEGIN
49     dbms_output.put_line( l_log );
50   EXCEPTION
51     WHEN OTHERS THEN
52       RETURN;
53   END CSF_LOG; */
54 
55    /*
56     The following steps list the basic activities involved in using the Data Pump API.
57     The steps are presented in the order in which the activities would generally be performed:
58       1. Execute the DBMS_DATAPUMP.OPEN procedure to create a Data Pump job and its infrastructure.
59       2. Define any parameters for the job.
60       3. Start the job.
61       4. Optionally, monitor the job until it completes.
62       5. Optionally, detach from the job and reattach at a later time.
63       6. Optionally, stop the job.
64       7. Optionally, restart the job, if desired.
65   */
66   PROCEDURE DATAPUMP_EXPORT(
67      p_dmp_file       IN   VARCHAR2,
68      p_table_space  IN   VARCHAR2,
69      p_data_set_name  IN   VARCHAR2,
70      errbuf OUT nocopy VARCHAR2,
71      retcode OUT nocopy VARCHAR2)
72   IS
73     l_dp_handle       NUMBER;                         -- Data Pump job handle
74     l_dmp_file        VARCHAR2(100) ;                 -- The dump file name.
75     l_log_file        VARCHAR2(100) ;                 -- The log file name.
76     l_data_file_dir   VARCHAR2(240) ;                 -- Data files directory
77     l_table_space   VARCHAR2(100);                 -- The data file name string.
78     l_data_file        VARCHAR2(1000);                -- The data file name .
79     l_job_state       VARCHAR2(30);                   -- To keep track of DATAPUMP job state.
80     l_lerror          ku$_LogEntry;                   -- For WIP and error messages.
81     l_job_status      ku$_JobStatus;                  -- The job status from get_status.
82     l_job_desc        ku$_JobDesc;                    -- The job description from get_status.
83     l_status          ku$_Status;                     -- The status object returned by get_status
84     l_ind             NUMBER;                         -- Loop index
85     l_percent_done    NUMBER;                         -- Percentage of job complete
86     l_str_pos         NUMBER;                         -- String starting position
87     l_str_len         NUMBER;                         -- String length for output
88     i                 NUMBER;                         -- Loop index
89 
90     l_instance_name VARCHAR2(100);
91     l_job_name VARCHAR2(100);
92     l_date DATE;
93     l_temp VARCHAR2(10);
94   BEGIN
95 
96     put_stream(g_log, '  ' );
97 
98     put_stream(g_log, 'Start of Procedure DATAPUMP_EXPORT ' );
99 
100     put_stream(g_log, '====================================' );
101 
102     l_dmp_file  := p_dmp_file;
103 
104     l_table_space := p_table_space;
105 
106     SELECT SYSDATE INTO l_date FROM dual;
107 
108     SELECT instance_name INTO l_instance_name from v$instance;
109 
110     l_job_name := 'EXPORT_JOB' || TO_CHAR(l_date,'MISS');
111 
112     l_log_file := 'EXPORT_' || TO_CHAR(l_date,'DDMMYY_HH24MISS') || '_' || l_instance_name|| '_'  || p_data_set_name || '.log';
113 
114     put_stream(g_log, 'DATAPUMP EXPORT JOB NAME : ' || l_job_name);
115 
116     put_stream(g_log, 'DATAPUMP EXPORT LOG FILE NAME : ' || l_log_file );
117 
118     --  1. Execute the DBMS_DATAPUMP.OPEN procedure to create a Data Pump job and its infrastructure.
119     l_dp_handle := DBMS_DATAPUMP.open(
120                             operation   => 'EXPORT',
121                             job_mode    => 'TRANSPORTABLE',
122                             remote_link => NULL,
123                             job_name    => l_job_name,
124                             version     => 'LATEST');
125 
126     put_stream(g_log,'   STEP 1 done - Create an handle for DATAPUMP TRANSPORTABLE EXPORT JOB');
127 
128     -- 2. Define any parameters for the job.
129     -- 2.1 Add dump file parameter
130     DBMS_DATAPUMP.add_file(
131                             handle    => l_dp_handle,
132                             filename  => l_dmp_file,
133                             directory => g_directory_name,
134                             filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
135 
136     put_stream(g_log,'   STEP 2.1 done - Set the dump file parameter');
137 
138     --2.2 Add log file parameter.
139     DBMS_DATAPUMP.add_file(
140                             handle    => l_dp_handle,
141                             filename  => l_log_file,
142                             directory => g_directory_name,
143                             filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
144 
145     put_stream(g_log,'   STEP 2.2 done - Set the log file parameter');
146 
147     -- 2.3 Add TTS_FULL_CHECK parameter
148     DBMS_DATAPUMP.set_parameter(
149                              handle => l_dp_handle,
150                              name=>'TTS_FULL_CHECK',
151                              value=>1);
152 
153     put_stream(g_log,'   STEP 2.3 done - Set TTS_FULL_CHECK parameter');
154 
155     DBMS_DATAPUMP.metadata_filter(
156                              handle => l_dp_handle,
157                              Name => 'TABLESPACE_EXPR',
158                              value => 'IN (''' || l_table_space || ''')' );
159 
160     put_stream(g_log,'     STEP 2.4 - Set TABLESPACE_EXPR IN ('''|| l_table_space || ''')');
161 	-- 3 . Start the DATAPUMP job.
162     DBMS_DATAPUMP.start_job(l_dp_handle);
163 
164     put_stream(g_log, '   STEP 3 done - Start the EXPORT Job');
165 
166     -- 4. Optionally wait for the job to complete.
167     -- In the following loop, the job is monitored until it completes.
168     l_percent_done := 0;
169 
173 
170     l_job_state := 'UNDEFINED';
171 
172     while (l_job_state <> 'COMPLETED') and (l_job_state <> 'STOPPED') loop
174       DBMS_DATAPUMP.GET_STATUS(
175                           l_dp_handle,
176                           dbms_datapump.ku$_status_job_error +
177                           dbms_datapump.ku$_status_job_status +
178                           dbms_datapump.ku$_status_wip,
179                           -1,
180                           l_job_state,
181                           l_status);
182 
183       l_job_status := l_status.job_status;
184 
185       -- If the percentage done changed, display the new value.
186       IF l_job_status.percent_done <> l_percent_done THEN
187 
188         put_stream(g_log,'            *** Job percent done = ' || to_char(l_job_status.percent_done));
189 
190         l_percent_done := l_job_status.percent_done;
191 
192      END IF;
193 
194     END LOOP;
195 
196     put_stream(g_log, '   STEP 4 done - wait for the job to complete.');
197 
198     put_stream(g_log,'  Final job state = ' || l_job_state );
199 
200     DBMS_DATAPUMP.detach(l_dp_handle);
201 
202     put_stream(g_log, '   STEP 5 done - Export job is done');
203 
204     put_stream(g_log, 'End of Procedure DATAPUMP_EXPORT : Export job is completed successfully ' );
205 
206     put_stream(g_log, 'Please view the datapump log file to view the list of datafiles. ' );
207 
208     put_stream(g_log, '======================================================================= ' );
209 
210   EXCEPTION
211 
212     WHEN OTHERS THEN
213 
214       put_stream(g_log,  'Exception in Data Pump export job . Exception Details : ');
215 
216       put_stream(g_output, 'Exception in Data Pump export job . Exception Details : ');
217 
218       dbms_datapump.get_status(
219                               l_dp_handle,
220                               dbms_datapump.ku$_status_job_error,
221                               0,
222                               l_job_state,
223                               l_status);
224 
225       IF ( bitand(l_status.mask,dbms_datapump.ku$_status_job_error) <> 0 ) THEN
226 
227         l_lerror := l_status.error;
228 
229         IF l_lerror IS NOT NULL THEN
230 
231           l_ind := l_lerror.FIRST;
232 
233           WHILE l_ind IS NOT NULL LOOP
234 
235             l_str_pos := 1;
236 
237             l_str_len := LENGTH(l_lerror(l_ind).LogText);
238 
239             IF l_str_len > 255 THEN
240 
241               l_str_len := 255;
242 
243             END IF;
244 
245             WHILE l_str_len > 0 LOOP
246 
247               put_stream(g_log, substr(l_lerror(l_ind).LogText,l_str_pos,l_str_len));
248 
249               put_stream(g_output, substr(l_lerror(l_ind).LogText,l_str_pos,l_str_len));
250 
251               l_str_pos := l_str_pos + 255;
252 
253               l_str_len := LENGTH(l_lerror(l_ind).LogText) + 1 - l_str_pos;
254 
255             END LOOP; -- WHILE l_str_len > 0
256 
257             l_ind := l_lerror.NEXT(l_ind);
258 
259           END LOOP;  -- WHILE l_ind IS NOT NULL
260 
261         END IF;  -- IF l_lerror IS NOT NULL
262 
263       END IF; -- IF ( bitand(l_status.mask,dbms_datapump.ku$_status_job_error) <> 0 )
264 
265       -- Optionally STOP the job incase of exception.
266       DBMS_DATAPUMP.stop_job(
267                        handle => l_dp_handle,
268                        immediate => 1,
269                        keep_master => 0);
270 
271       DBMS_DATAPUMP.detach(l_dp_handle);
272 
273       retcode := 1;
274 
275       errbuf := sqlerrm;
276 
277       put_stream(g_output, 'DATAPUMP_EXPORT PROCEDURE HAS FAILED' ||SQLCODE||'-'|| SQLERRM);
278 
279       put_stream(g_output, 'Please view the datapump log for more details');
280 
281       put_stream(g_log, 'DATAPUMP_EXPORT PROCEDURE HAS FAILED' || SQLCODE||'-'||SQLERRM);
282 
283       RAISE FND_API.G_EXC_ERROR;
284 
285   END DATAPUMP_EXPORT;
286 
287   /*
288     The following steps list the basic activities involved in using the Data Pump API.
289     The steps are presented in the order in which the activities would generally be performed:
290       1. Execute the DBMS_DATAPUMP.OPEN procedure to create a Data Pump job and its infrastructure.
291       2. Define any parameters for the job.
292       3. Start the job.
293       4. Optionally, monitor the job until it completes.
294       5. Optionally, detach from the job and reattach at a later time.
295       6. Optionally, stop the job.
296       7. Optionally, restart the job, if desired.
297   */
298   PROCEDURE DATAPUMP_IMPORT(
299      p_dmp_file       IN   VARCHAR2,
300      p_data_file_dir  IN   VARCHAR2,
301      p_data_file_str  IN   VARCHAR2,
302      p_data_set_name  IN   VARCHAR2,
303      errbuf OUT nocopy VARCHAR2,
304      retcode OUT nocopy VARCHAR2)
305   IS
306     l_dp_handle       NUMBER;                         -- Data Pump job handle
307     l_dmp_file        VARCHAR2(100) ;                 -- The dump file name.
308     l_log_file        VARCHAR2(100) ;                 -- The log file name.
309     l_data_file_dir   VARCHAR2(240) ;                 -- Data files directory
310     l_data_file_str   VARCHAR2(1000);                 -- The data file name string.
311     l_data_file        VARCHAR2(1000);                -- The data file name .
312     l_job_state       VARCHAR2(30);                   -- To keep track of DATAPUMP job state.
313     l_lerror          ku$_LogEntry;                   -- For WIP and error messages.
314     l_job_status      ku$_JobStatus;                  -- The job status from get_status.
318     l_percent_done    NUMBER;                         -- Percentage of job complete
315     l_job_desc        ku$_JobDesc;                    -- The job description from get_status.
316     l_status          ku$_Status;                     -- The status object returned by get_status
317     l_ind             NUMBER;                         -- Loop index
319     l_str_pos         NUMBER;                         -- String starting position
320     l_str_len         NUMBER;                         -- String length for output
321     i                 NUMBER;                         -- Loop index
322 
323     l_instance_name VARCHAR2(100);
324     l_job_name VARCHAR2(100);
325     l_date DATE;
326     l_temp VARCHAR2(10);
330 
327   BEGIN
328 
329     put_stream(g_log, '  ' );
331     put_stream(g_log, 'Start of Procedure DATAPUMP_IMPORT ' );
332 
333     put_stream(g_log, '====================================' );
334 
335     l_dmp_file  := p_dmp_file;
336 
337     l_data_file_str := p_data_file_str;
338 
339     SELECT SYSDATE INTO l_date FROM dual;
340 
341     SELECT instance_name INTO l_instance_name from v$instance;
342 
343     l_job_name := 'IMPORT_JOB' || TO_CHAR(l_date,'MISS');
344 
345     l_log_file := 'IMPORT_' || TO_CHAR(l_date,'DDMMYY_HH24MISS') || '_' || l_instance_name|| '_'  || p_data_set_name || '.log';
346 
347     put_stream(g_log, 'DATAPUMP JOB NAME : ' || l_job_name);
348 
349     put_stream(g_log, 'DATAPUMP LOG FILE NAME : ' || l_log_file );
350 
351     --  1. Execute the DBMS_DATAPUMP.OPEN procedure to create a Data Pump job and its infrastructure.
352     l_dp_handle := DBMS_DATAPUMP.open(
353                             operation   => 'IMPORT',
354                             job_mode    => 'TRANSPORTABLE',
355                             remote_link => NULL,
356                             job_name    => l_job_name,
357                             version     => 'LATEST');
358 
359     put_stream(g_log,'   STEP 1 done - Create an handle for DATAPUMP TRANSPORTABLE IMPORT JOB');
360 
361     -- 2. Define any parameters for the job.
362     -- 2.1 Add dump file parameter
363     DBMS_DATAPUMP.add_file(
364                             handle    => l_dp_handle,
365                             filename  => l_dmp_file,
366                             directory => g_directory_name,
367                             filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
368 
369     put_stream(g_log,'   STEP 2.1 done - Set the dump file parameter');
370 
371     --2.2 Add log file parameter.
372     DBMS_DATAPUMP.add_file(
373                             handle    => l_dp_handle,
374                             filename  => l_log_file,
375                             directory => g_directory_name,
376                             filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
377 
378     put_stream(g_log,'   STEP 2.2 done - Set the log file parameter');
379 
380 --    -- 2.3 Add TTS_FULL_CHECK parameter
381 --    DBMS_DATAPUMP.set_parameter(
382 --                            handle => l_dp_handle,
383 --                            name=>'TTS_FULL_CHECK',
384 --                            value=>1);
385 --
386 --    put_stream(g_log,'   STEP 2.3 done - Set the TTS_FULL_CHECK parameter');
387 
388     -- 2.4 Add data file parameter
389     put_stream(g_log,'   STEP 2.3 - Set TABLESPACE_DATAFILE parameter');
390 
391     /* The following block converts the string of comma seperated data filenames to file names
392        and sets the TABLESPACE_DATAFILE
393     */
394     BEGIN
395 
396       l_data_file_dir := p_data_file_dir;
397 
398       SELECT SUBSTR(l_data_file_dir, length(l_data_file_dir)) INTO l_temp  FROM dual;
399 
400       IF l_temp <> '/' THEN
401         l_data_file_dir := l_data_file_dir || '/';
402       END IF;
403 
404       i := 1;
405 
406       LOOP
407 
411 
408         EXIT WHEN INSTR(l_data_file_str , ',') = 0 OR INSTR(l_data_file_str , ',') is null;
409 
410         SELECT SUBSTR(l_data_file_str, 1, INSTR(l_data_file_str , ',')-1) INTO l_data_file FROM dual;
412         SELECT SUBSTR(l_data_file_str, INSTR(l_data_file_str , ',')+1) INTO l_data_file_str FROM dual;
413 
414         DBMS_DATAPUMP.SET_PARAMETER(
415                                   handle => l_dp_handle,
416                                   name=>'TABLESPACE_DATAFILE',
417                                   value=> l_data_file_dir || l_data_file);
418 
419         put_stream(g_log,'     STEP 2.3.' || i ||' - Set TABLESPACE_DATAFILE = ' || l_data_file_dir ||l_data_file );
420 
421         i := i + 1 ;
422 
423       END LOOP;
424 
425       DBMS_DATAPUMP.SET_PARAMETER(
426                                 handle => l_dp_handle,
427                                 name=>'TABLESPACE_DATAFILE',
428                                 value=>l_data_file_dir || l_data_file_str);
429 
430       put_stream(g_log,'     STEP 2.3.' || i ||' - Set TABLESPACE_DATAFILE = '|| l_data_file_dir ||l_data_file_str );
431 
432     END;
433 
434     -- 3 . Start the DATAPUMP job.
435     DBMS_DATAPUMP.start_job(l_dp_handle);
436 
437     put_stream(g_log, '   STEP 3 done - Start the Job');
438 
439     -- 4. Optionally wait for the job to complete.
440     -- In the following loop, the job is monitored until it completes.
441     l_percent_done := 0;
442 
443     l_job_state := 'UNDEFINED';
444 
445     while (l_job_state <> 'COMPLETED') and (l_job_state <> 'STOPPED') loop
446 
447       DBMS_DATAPUMP.GET_STATUS(
448                           l_dp_handle,
449                           dbms_datapump.ku$_status_job_error +
450                           dbms_datapump.ku$_status_job_status +
451                           dbms_datapump.ku$_status_wip,
452                           -1,
453                           l_job_state,
454                           l_status);
455 
456       l_job_status := l_status.job_status;
457 
458       -- If the percentage done changed, display the new value.
459       IF l_job_status.percent_done <> l_percent_done THEN
460         put_stream(g_log,'           *** Job percent done = ' || to_char(l_job_status.percent_done));
461         l_percent_done := l_job_status.percent_done;
462       end if;
463     end loop;
464 
465     put_stream(g_log, '   STEP 4 done - wait for the job to complete.');
466 
467     put_stream(g_log, 'Final job state = ' || l_job_state );
468 
469     DBMS_DATAPUMP.detach(l_dp_handle);
470 
471     put_stream(g_log, '   STEP 5 done - Export job is done');
472 
473     put_stream(g_log, 'End of Procedure DATAPUMP_IMPORT : Export job is completed successfully ' );
474     put_stream(g_log, '======================================================================= ' );
475 
476   EXCEPTION
477 
478     WHEN OTHERS THEN
479 
480       put_stream(g_log,  'Exception in Data Pump job . Exception Details : ');
481 
482       put_stream(g_output, 'Exception in Data Pump job . Exception Details : ');
483 
484       dbms_datapump.get_status(
485                               l_dp_handle,
486                               dbms_datapump.ku$_status_job_error,
487                               0,
488                               l_job_state,
489                               l_status);
490 
491       IF ( bitand(l_status.mask,dbms_datapump.ku$_status_job_error) <> 0 ) THEN
492 
493         l_lerror := l_status.error;
494 
498 
495         IF l_lerror IS NOT NULL THEN
496 
497           l_ind := l_lerror.FIRST;
499           WHILE l_ind IS NOT NULL LOOP
500 
501             l_str_pos := 1;
502 
503             l_str_len := LENGTH(l_lerror(l_ind).LogText);
504 
505             IF l_str_len > 255 THEN
506 
507               l_str_len := 255;
508 
509             END IF;
510 
511             WHILE l_str_len > 0 LOOP
512 
513               put_stream(g_log, substr(l_lerror(l_ind).LogText,l_str_pos,l_str_len));
514 
515               put_stream(g_output, substr(l_lerror(l_ind).LogText,l_str_pos,l_str_len));
516 
520 
517               l_str_pos := l_str_pos + 255;
518 
519               l_str_len := LENGTH(l_lerror(l_ind).LogText) + 1 - l_str_pos;
521             END LOOP; -- WHILE l_str_len > 0
522 
523             l_ind := l_lerror.NEXT(l_ind);
524 
525           END LOOP;  -- WHILE l_ind IS NOT NULL
526 
527         END IF;  -- IF l_lerror IS NOT NULL
528 
529       END IF; -- IF ( bitand(l_status.mask,dbms_datapump.ku$_status_job_error) <> 0 )
530 
531       -- Optionally STOP the job incase of exception.
532       DBMS_DATAPUMP.stop_job(
533                        handle => l_dp_handle,
534                        immediate => 1,
535                        keep_master => 0);
536 
537       DBMS_DATAPUMP.detach(l_dp_handle);
538 
539       retcode := 1;
540 
541       errbuf := sqlerrm;
542 
543       put_stream(g_output, 'DATAPUMP_IMPORT PROCEDURE HAS FAILED' ||SQLCODE||'-'|| SQLERRM);
544 
545       put_stream(g_log, 'DATAPUMP_IMPORT PROCEDURE HAS FAILED' || SQLCODE||'-'||SQLERRM);
546 
547       RAISE FND_API.G_EXC_ERROR;
548 
549   END DATAPUMP_IMPORT;
550 
551  /* Creates a DATABASE DIRECOTRY*/
552   PROCEDURE CREATE_DIRECTORY(
553      p_directory_path VARCHAR2,
554      errbuf OUT nocopy VARCHAR2,
555      retcode OUT nocopy VARCHAR2)
556   IS
557   BEGIN
558 
559     put_stream(g_log, '  ' );
560 
561     put_stream(g_log, 'Start of Procedure CREATE_DIRECTORY ' );
562 
563     put_stream(g_log, '====================================' );
564 
565     EXECUTE IMMEDIATE 'CREATE  OR REPLACE directory ' || g_directory_name || ' as ''' || p_directory_path ||'''';
566 
567     --EXECUTE IMMEDIATE 'GRANT READ, WRITE ON DIRECTORY TTS_NAVTEQ_2008 TO CSF';
568 
569     IF (dbms_lob.fileexists(bfilename(g_directory_name, '.')) = 1 )  THEN
570 
571       put_stream(g_output, 'The specified file path exists');
572 
573     ELSE
574 
575       put_stream(g_log, ' The specified file path does not exist ');
576 
577       RAISE FND_API.G_EXC_ERROR;
578 
579     END IF;
580 
581     put_stream(g_log, 'End of Procedure CREATE_DIRECTORY : Directory created successfully ' );
582 
583     put_stream(g_log, '=================================+ ' );
584 
585   EXCEPTION
586     WHEN others THEN
587 
588       retcode := 1;
589 
590       errbuf := sqlerrm;
591 
592       put_stream(g_output, 'CREATE_DIRECTORY PROCEDURE HAS FAILED' ||SQLCODE||'-'|| SQLERRM);
593 
594       put_stream(g_log, 'CREATE_DIRECTORY PROCEDURE HAS FAILED' || SQLCODE||'-'||SQLERRM);
595 
596       RAISE FND_API.G_EXC_ERROR;
597 
598   END CREATE_DIRECTORY;
599 
600   /* Initializes all spatial indexes in a tablespace that was transported to another database.(For APPS user only.) */
601   PROCEDURE INITIALIZE_INDEXES_FOR_TTS(
602      errbuf OUT nocopy VARCHAR2,
603      retcode OUT nocopy VARCHAR2)
604   IS
605   BEGIN
606     put_stream(g_log, '  ' );
607 
608     put_stream(g_log, 'Start of Procedure INITIALIZE_INDEXES_FOR_TTS : Initializing Spatial Indexes for APPS user. ' );
609 
610     put_stream(g_log, '================================================ ' );
611 
612     SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS;
613 
614     put_stream(g_log, 'End of Procedure INITIALIZE_INDEXES_FOR_TTS : Initialized Spatial Indexes for APPS user. ' );
615 
616     put_stream(g_log, '================================================ ' );
617 
618   EXCEPTION
619 
620     WHEN others THEN
621 
622       retcode := 1;
623 
624       errbuf := sqlerrm;
625 
626       put_stream(g_output, 'INITIALIZE_INDEXES_FOR_TTS PROCEDURE HAS FAILED' ||SQLCODE||'-'|| SQLERRM);
627 
628       put_stream(g_log, 'INITIALIZE_INDEXES_FOR_TTS PROCEDURE HAS FAILED' || SQLCODE||'-'||SQLERRM);
629 
630       RAISE FND_API.G_EXC_ERROR;
631 
632   END INITIALIZE_INDEXES_FOR_TTS;
633 
634  /* Prepares a tablespace to be transported to another database, so that
635     spatial indexes will be preserved during the transport operation.*/
636  PROCEDURE PREPARE_INDEXES_FOR_TTS(
637      p_table_space  IN VARCHAR2,
638      errbuf OUT nocopy VARCHAR2,
639      retcode OUT nocopy VARCHAR2)
640   IS
641   BEGIN
642     put_stream(g_log, '  ' );
643 
644     put_stream(g_log, 'Start of Procedure PREPARE_INDEXES_FOR_TTS : Initializing Spatial Indexes for APPS user. ' );
645 
646     put_stream(g_log, '================================================ ' );
647 
648     SDO_UTIL.PREPARE_FOR_TTS(p_table_space);
649 
650     put_stream(g_log, 'End of Procedure PREPARE_INDEXES_FOR_TTS : Initialized Spatial Indexes for APPS user. ' );
651 
652     put_stream(g_log, '================================================ ' );
653 
654   EXCEPTION
655 
656     WHEN others THEN
657 
658       retcode := 1;
659 
660       errbuf := sqlerrm;
661 
662       put_stream(g_output, 'PREPARE_INDEXES_FOR_TTS PROCEDURE HAS FAILED' ||SQLCODE||'-'|| SQLERRM);
663 
664       put_stream(g_log, 'PREPARE_INDEXES_FOR_TTS PROCEDURE HAS FAILED' || SQLCODE||'-'||SQLERRM);
665 
666       RAISE FND_API.G_EXC_ERROR;
667 
668   END PREPARE_INDEXES_FOR_TTS;
669 
670  /*  DBMS_STATS.EXPORT_TABLE_STATS Procedure
671   ==================================
672    This procedure retrieves statistics for all tables of table space being exported and stores them in the user stat table. */
673   PROCEDURE EXPORT_TABLE_STATS(
674      p_data_set_name IN             VARCHAR2,
675      errbuf OUT nocopy VARCHAR2,
676      retcode OUT nocopy VARCHAR2)
677   IS
678 
679     l_data_set_name        VARCHAR2(40);
680 
684 
681     l_table_suffix          VARCHAR2(40);
682 
683   BEGIN
685     put_stream(g_log, '  ' );
686 
687     put_stream(g_log, 'Start of Procedure EXPORT_TABLE_STATS ' );
688 
689     put_stream(g_log, '================================================ ' );
690 
691     l_data_set_name  := p_data_set_name;
692 
696       l_data_set_name := '';
693     IF l_data_set_name = 'NONE' THEN
694 
695       l_table_suffix := '_COMMON';
697 
698     ELSE
699 
700       l_table_suffix := '_' || l_data_set_name;
701 
702       l_data_set_name := '_' || l_data_set_name;
703 
704     END IF;
705 
706 
707     /*  DBMS_STATS.EXPORT_TABLE_STATS Procedure
708     ==================================
709      This procedure retrieves statistics for a particular table and stores them in the user stat table.
710     Cascade results in all index and column stats associated with the specified table being exported as well.   */
711 
712     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_LF_BLOCKS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
713 
714     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_LF_NAMES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
715 
716     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_LF_PLACES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
717 
718     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_LF_PLACE_NAMES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
719 
720     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_LF_PLACE_POSTCS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
721 
722     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_LF_POIS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
723 
724     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_LF_POI_NAMES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
725 
726     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_LF_POSTCODES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
727 
728     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_LF_ROADSEGMENTS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
729 
730     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_LF_ROADSEGM_NAMES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
731 
732     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_LF_ROADSEGM_PLACES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
733 
734     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_LF_ROADSEGM_POSTS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
735 
736     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_MD_ADM_BOUNDS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
737 
738     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_MD_HYDROS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
739 
740     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_MD_LAND_USES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
741 
742     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_MD_NAMES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
743 
744     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_MD_POIS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
745 
749 
746     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_MD_POI_NM_ASGNS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
747 
748     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_MD_RAIL_SEGS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
750     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_MD_RDSEG_NM_ASGNS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
751 
752     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_MD_RD_SEGS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
753 
754     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_TDS_BINARY_MAPS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
755 
756     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_TDS_BINARY_TILES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
757 
758     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_TDS_CONDITIONS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
759 
760     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_TDS_COND_SEGS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
761 
762     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_TDS_INTERVALS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
763 
764     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_TDS_NODES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
765 
766     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_TDS_RDBLCK_INTVLS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
767 
768     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_TDS_RDBLCK_SGMNTS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
769 
770     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_TDS_ROADBLOCKS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
771 
772     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_TDS_SEGMENTS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
773 
774     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_TDS_SEGM_NODES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
775 
776     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_TDS_TILES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
777 
778     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_SDM_CTRY_PROFILES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
779 
780     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_SPATIAL_STAT_M' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
781 
782     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_SPATIAL_VER_M' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
783 
784     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_SPATIAL_STAT_TILES_M' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
785 
786     DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_SPATIAL_STREET_TYPES_M' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM');
787 
788     IF l_data_set_name = 'NONE' THEN
789 
790       DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_WOM_AREA_COUNTRY', STATTAB => 'TTSP_STATS_COMMON', CASCADE => TRUE, STATOWN => 'SYSTEM');
791 
792       DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_WOM_AREA_LAKE', STATTAB => 'TTSP_STATS_COMMON', CASCADE => TRUE, STATOWN => 'SYSTEM');
793 
794       DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_WOM_AREA_OCEAN', STATTAB => 'TTSP_STATS_COMMON', CASCADE => TRUE, STATOWN => 'SYSTEM');
795 
796       DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_WOM_AREA_STATE', STATTAB => 'TTSP_STATS_COMMON', CASCADE => TRUE, STATOWN => 'SYSTEM');
797 
798       DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_WOM_BOUNDARY_COUNTRY', STATTAB => 'TTSP_STATS_COMMON', CASCADE => TRUE, STATOWN => 'SYSTEM');
799 
800       DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_WOM_BOUNDARY_STATE', STATTAB => 'TTSP_STATS_COMMON', CASCADE => TRUE, STATOWN => 'SYSTEM');
801 
802       DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_WOM_CITY_CAPITAL', STATTAB => 'TTSP_STATS_COMMON', CASCADE => TRUE, STATOWN => 'SYSTEM');
803 
804       DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_WOM_CITY_METROPOLIS', STATTAB => 'TTSP_STATS_COMMON', CASCADE => TRUE, STATOWN => 'SYSTEM');
805 
806       DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_WOM_CITY_SMALL', STATTAB => 'TTSP_STATS_COMMON', CASCADE => TRUE, STATOWN => 'SYSTEM');
807 
808       DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_WOM_AREA_LANDUSE', STATTAB => 'TTSP_STATS_COMMON', CASCADE => TRUE, STATOWN => 'SYSTEM');
809 
810       DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_WOM_POI', STATTAB => 'TTSP_STATS_COMMON', CASCADE => TRUE, STATOWN => 'SYSTEM');
811 
812       DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_WOM_ROAD_HIGHWAY', STATTAB => 'TTSP_STATS_COMMON', CASCADE => TRUE, STATOWN => 'SYSTEM');
813 
814       DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_WOM__MD_META_BOUNDING_RECT', STATTAB => 'TTSP_STATS_COMMON', CASCADE => TRUE, STATOWN => 'SYSTEM');
815 
816     END IF;
817 
818     put_stream(g_log, 'Imported Table Statistics for all the tables ' );
819 
823 
820     put_stream(g_log, 'End of Procedure EXPORT_TABLE_STATS for  ' || p_data_set_name );
821 
822     put_stream(g_log, '================================================ ' );
824   EXCEPTION
825 
826     WHEN others THEN
827 
828       retcode := 1;
829 
830       errbuf := sqlerrm;
831 
832       put_stream(g_output, 'EXPORT_TABLE_STATS PROCEDURE HAS FAILED' ||SQLCODE||'-'|| SQLERRM);
833 
834       put_stream(g_log, 'EXPORT_TABLE_STATS PROCEDURE HAS FAILED' || SQLCODE||'-'||SQLERRM);
835 
836       RAISE FND_API.G_EXC_ERROR;
837 
838   END EXPORT_TABLE_STATS;
839 
840 
841   /*  DBMS_STATS.IMPORT_TABLE_STATS Procedure
842   ==================================
843   This procedure retrieves statistics for a all spatial tables from the user statistics table identified
844   by stattab and stores them in the dictionary.  */
845   PROCEDURE IMPORT_TABLE_STATS(
846      p_data_set_name IN             VARCHAR2,
847      errbuf OUT nocopy VARCHAR2,
848      retcode OUT nocopy VARCHAR2)
849   IS
850 
851     l_data_set_name        VARCHAR2(40);
852 
853     l_table_suffix          VARCHAR2(40);
854 
855   BEGIN
856 
857     put_stream(g_log, '  ' );
858 
859     put_stream(g_log, 'Start of Procedure IMPORT_TABLE_STATS ' );
860 
861     put_stream(g_log, '================================================ ' );
862 
863     l_data_set_name  := p_data_set_name;
864 
865     IF l_data_set_name = 'NONE' THEN
866 
867       l_table_suffix := '_COMMON';
868       l_data_set_name := '';
869 
870     ELSE
871 
872       l_table_suffix := '_' || l_data_set_name;
873 
874       l_data_set_name := '_' || l_data_set_name;
875 
876     END IF;
877 
878 
879     /*  DBMS_STATS.IMPORT_TABLE_STATS Procedure
880     ==================================
881      This procedure retrieves statistics for a particular table from the user statistics table identified
882     by stattab and stores them in the dictionary. Cascade results in all index statistics associated with
883     the specified table being imported as well.    */
884 
885     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_LF_BLOCKS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
886 
887     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_LF_NAMES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
888 
889     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_LF_PLACES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
890 
891     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_LF_PLACE_NAMES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
892 
893     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_LF_PLACE_POSTCS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
894 
895     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_LF_POIS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
896 
897     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_LF_POI_NAMES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
898 
899     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_LF_POSTCODES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
900 
901     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_LF_ROADSEGMENTS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
902 
906 
903     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_LF_ROADSEGM_NAMES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
904 
905     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_LF_ROADSEGM_PLACES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
907     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_LF_ROADSEGM_POSTS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
908 
909     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_MD_ADM_BOUNDS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
910 
911     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_MD_HYDROS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
912 
916 
913     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_MD_LAND_USES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
914 
915     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_MD_NAMES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
917     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_MD_POIS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
918 
919     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_MD_POI_NM_ASGNS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
920 
921     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_MD_RAIL_SEGS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
922 
923     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_MD_RDSEG_NM_ASGNS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
924 
925     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_MD_RD_SEGS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
926 
927     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_TDS_BINARY_MAPS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
928 
929     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_TDS_BINARY_TILES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
930 
931     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_TDS_CONDITIONS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
932 
933     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_TDS_COND_SEGS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
934 
935     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_TDS_INTERVALS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
936 
937     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_TDS_NODES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
938 
939     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_TDS_RDBLCK_INTVLS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
940 
941     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_TDS_RDBLCK_SGMNTS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
942 
943     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_TDS_ROADBLOCKS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
944 
945     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_TDS_SEGMENTS' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
946 
947     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_TDS_SEGM_NODES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
948 
949     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_TDS_TILES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
950 
951     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_SDM_CTRY_PROFILES' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
952 
953     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_SPATIAL_STAT_M' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
954 
955     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_SPATIAL_VER_M' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
956 
957     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_SPATIAL_STAT_TILES_M' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
958 
959     DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_SPATIAL_STREET_TYPES_M' || l_data_set_name, STATTAB => 'TTSP_STATS' || l_table_suffix, CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
960 
961     IF l_data_set_name = 'NONE' THEN
962 
963       DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_WOM_AREA_COUNTRY', STATTAB => 'TTSP_STATS_COMMON', CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
964 
965       DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_WOM_AREA_LAKE', STATTAB => 'TTSP_STATS_COMMON', CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
966 
967       DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_WOM_AREA_OCEAN', STATTAB => 'TTSP_STATS_COMMON', CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
968 
969       DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_WOM_AREA_STATE', STATTAB => 'TTSP_STATS_COMMON', CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
970 
971       DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_WOM_BOUNDARY_COUNTRY', STATTAB => 'TTSP_STATS_COMMON', CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
972 
973       DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_WOM_BOUNDARY_STATE', STATTAB => 'TTSP_STATS_COMMON', CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
974 
975       DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_WOM_CITY_CAPITAL', STATTAB => 'TTSP_STATS_COMMON', CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
976 
980 
977       DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_WOM_CITY_METROPOLIS', STATTAB => 'TTSP_STATS_COMMON', CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
978 
979       DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_WOM_CITY_SMALL', STATTAB => 'TTSP_STATS_COMMON', CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
981       DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_WOM_AREA_LANDUSE', STATTAB => 'TTSP_STATS_COMMON', CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
982 
983       DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_WOM_POI', STATTAB => 'TTSP_STATS_COMMON', CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
984 
985       DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_WOM_ROAD_HIGHWAY', STATTAB => 'TTSP_STATS_COMMON', CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
986 
987       DBMS_STATS.IMPORT_TABLE_STATS (OWNNAME => 'CSF',TABNAME => 'CSF_WOM__MD_META_BOUNDING_RECT', STATTAB => 'TTSP_STATS_COMMON', CASCADE => TRUE, STATOWN => 'SYSTEM',FORCE => TRUE);
988 
989     END IF;
990 
991     put_stream(g_log, 'Imported Table Statistics for all the tables ' );
992 
993     put_stream(g_log, 'End of Procedure IMPORT_TABLE_STATS for  ' || p_data_set_name );
994 
995     put_stream(g_log, '================================================ ' );
996 
997   EXCEPTION
998 
999     WHEN others THEN
1000 
1001       retcode := 1;
1002 
1003       errbuf := sqlerrm;
1004 
1005       put_stream(g_output, 'IMPORT_TABLE_STATS PROCEDURE HAS FAILED' ||SQLCODE||'-'|| SQLERRM);
1006 
1007       put_stream(g_log, 'IMPORT_TABLE_STATS PROCEDURE HAS FAILED' || SQLCODE||'-'||SQLERRM);
1008 
1009       RAISE FND_API.G_EXC_ERROR;
1010 
1011   END IMPORT_TABLE_STATS;
1012 
1013   /* The following procedure Creates the synonyms for TTSP objects in APPS schema*/
1014   PROCEDURE CREATE_SYNONYMS(
1015      p_data_set_name IN             VARCHAR2,
1016      errbuf          OUT nocopy     VARCHAR2,
1017      retcode         OUT nocopy     VARCHAR2)
1018   IS
1019 
1020    l_data_set_name        VARCHAR2(40);
1021 
1022    l_schema    VARCHAR2(10);
1023 
1024   BEGIN
1025 
1026     put_stream(g_log, '  ' );
1027 
1028     put_stream(g_log, 'Start of Procedure CREATE_SYNONYMS for  ' || p_data_set_name );
1029 
1030     put_stream(g_log, '================================================ ' );
1031 
1032     l_data_set_name  := p_data_set_name;
1033 
1034     l_schema  := 'CSF';
1035 
1036     IF l_data_set_name = 'NONE' THEN
1037 
1038       l_data_set_name := ' ';
1039 
1040     ELSE
1041 
1042       l_data_set_name := '_' || l_data_set_name;
1043 
1044     END IF;
1045 
1046     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_LF_BLOCKS' || l_data_set_name || ' FOR ' || l_schema || '.CSF_LF_BLOCKS' || l_data_set_name ;
1047 
1048     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_LF_NAMES' || l_data_set_name || ' FOR ' || l_schema || '.CSF_LF_NAMES' || l_data_set_name ;
1049 
1050     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_LF_PLACES' || l_data_set_name || ' FOR ' || l_schema || '.CSF_LF_PLACES' || l_data_set_name ;
1051 
1052     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_LF_PLACE_NAMES' || l_data_set_name || ' FOR ' || l_schema || '.CSF_LF_PLACE_NAMES' || l_data_set_name ;
1053 
1054     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_LF_PLACE_POSTCS' || l_data_set_name || ' FOR ' || l_schema || '.CSF_LF_PLACE_POSTCS' || l_data_set_name ;
1055 
1056     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_LF_POIS' || l_data_set_name || ' FOR ' || l_schema || '.CSF_LF_POIS' || l_data_set_name ;
1057 
1058     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_LF_POI_NAMES' || l_data_set_name || ' FOR ' || l_schema || '.CSF_LF_POI_NAMES' || l_data_set_name ;
1059 
1060     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_LF_POSTCODES' || l_data_set_name || ' FOR ' || l_schema || '.CSF_LF_POSTCODES' || l_data_set_name ;
1061 
1062     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_LF_ROADSEGMENTS' || l_data_set_name || ' FOR ' || l_schema || '.CSF_LF_ROADSEGMENTS' || l_data_set_name ;
1063 
1064     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_LF_ROADSEGM_NAMES' || l_data_set_name || ' FOR ' || l_schema || '.CSF_LF_ROADSEGM_NAMES' || l_data_set_name ;
1065 
1066     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_LF_ROADSEGM_PLACES' || l_data_set_name || ' FOR ' || l_schema || '.CSF_LF_ROADSEGM_PLACES' || l_data_set_name ;
1067 
1068     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_LF_ROADSEGM_POSTS' || l_data_set_name || ' FOR ' || l_schema || '.CSF_LF_ROADSEGM_POSTS' || l_data_set_name ;
1069 
1070     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_MD_ADM_BOUNDS' || l_data_set_name || ' FOR ' || l_schema || '.CSF_MD_ADM_BOUNDS' || l_data_set_name ;
1071 
1072     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_MD_HYDROS' || l_data_set_name || ' FOR ' || l_schema || '.CSF_MD_HYDROS' || l_data_set_name ;
1073 
1074     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_MD_LAND_USES' || l_data_set_name || ' FOR ' || l_schema || '.CSF_MD_LAND_USES' || l_data_set_name ;
1075 
1079 
1076     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_MD_NAMES' || l_data_set_name || ' FOR ' || l_schema || '.CSF_MD_NAMES' || l_data_set_name ;
1077 
1078     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_MD_POIS' || l_data_set_name || ' FOR ' || l_schema || '.CSF_MD_POIS' || l_data_set_name ;
1080     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_MD_POI_NM_ASGNS' || l_data_set_name ||  ' FOR ' || l_schema || '.CSF_MD_POI_NM_ASGNS' || l_data_set_name ;
1081 
1082     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_MD_RAIL_SEGS' || l_data_set_name || ' FOR ' || l_schema || '.CSF_MD_RAIL_SEGS' || l_data_set_name ;
1083 
1084     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_MD_RDSEG_NM_ASGNS' || l_data_set_name || ' FOR ' || l_schema || '.CSF_MD_RDSEG_NM_ASGNS' || l_data_set_name ;
1085 
1086     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_MD_RD_SEGS' || l_data_set_name || ' FOR ' || l_schema || '.CSF_MD_RD_SEGS' || l_data_set_name ;
1087 
1088     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_TDS_BINARY_MAPS' || l_data_set_name || ' FOR ' || l_schema || '.CSF_TDS_BINARY_MAPS' || l_data_set_name ;
1089 
1090     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_TDS_BINARY_TILES' || l_data_set_name || ' FOR ' || l_schema || '.CSF_TDS_BINARY_TILES' || l_data_set_name ;
1091 
1092     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_TDS_CONDITIONS' || l_data_set_name || ' FOR ' || l_schema || '.CSF_TDS_CONDITIONS' || l_data_set_name ;
1093 
1094     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_TDS_COND_SEGS' || l_data_set_name || ' FOR ' || l_schema || '.CSF_TDS_COND_SEGS' || l_data_set_name ;
1095 
1096     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_TDS_INTERVALS' || l_data_set_name || ' FOR ' || l_schema || '.CSF_TDS_INTERVALS' || l_data_set_name ;
1097 
1101 
1098     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_TDS_NODES' || l_data_set_name || ' FOR ' || l_schema || '.CSF_TDS_NODES' || l_data_set_name ;
1099 
1100     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_TDS_RDBLCK_INTVLS' || l_data_set_name || ' FOR ' || l_schema || '.CSF_TDS_RDBLCK_INTVLS' || l_data_set_name ;
1102     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_TDS_RDBLCK_SGMNTS' || l_data_set_name || ' FOR ' || l_schema || '.CSF_TDS_RDBLCK_SGMNTS' || l_data_set_name ;
1103 
1104     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_TDS_ROADBLOCKS' || l_data_set_name || ' FOR ' || l_schema || '.CSF_TDS_ROADBLOCKS' || l_data_set_name ;
1105 
1106     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_TDS_SEGMENTS' || l_data_set_name || ' FOR ' || l_schema || '.CSF_TDS_SEGMENTS' || l_data_set_name ;
1107 
1108     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_TDS_SEGM_NODES' || l_data_set_name||  ' FOR ' || l_schema || '.CSF_TDS_SEGM_NODES' || l_data_set_name ;
1109 
1110     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_TDS_TILES' || l_data_set_name || ' FOR ' || l_schema || '.CSF_TDS_TILES' || l_data_set_name ;
1111 
1112     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_SDM_CTRY_PROFILES' || l_data_set_name||  ' FOR ' || l_schema || '.CSF_SDM_CTRY_PROFILES' || l_data_set_name ;
1113 
1114     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_SPATIAL_STAT_M' || l_data_set_name || ' FOR ' || l_schema || '.CSF_SPATIAL_STAT_M' || l_data_set_name ;
1115 
1116     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_SPATIAL_VER_M' || l_data_set_name || ' FOR ' || l_schema || '.CSF_SPATIAL_VER_M' || l_data_set_name ;
1117 
1118     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_SPATIAL_STAT_TILES_M' || l_data_set_name || ' FOR ' || l_schema || '.CSF_SPATIAL_STAT_TILES_M' || l_data_set_name ;
1119 
1120     EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_SPATIAL_STREET_TYPES_M' || l_data_set_name || ' FOR ' || l_schema || '.CSF_SPATIAL_STREET_TYPES_M' || l_data_set_name ;
1121 
1122     IF (p_data_set_name = 'NONE' OR l_data_set_name = ' ') THEN
1123 
1124       EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_WOM_AREA_COUNTRY FOR ' || l_schema || '.CSF_WOM_AREA_COUNTRY' ;
1125 
1126       EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_WOM_AREA_LAKE FOR ' || l_schema || '.CSF_WOM_AREA_LAKE' ;
1127 
1128       EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_WOM_AREA_OCEAN FOR ' || l_schema || '.CSF_WOM_AREA_OCEAN' ;
1132       EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_WOM_BOUNDARY_COUNTRY FOR ' || l_schema || '.CSF_WOM_BOUNDARY_COUNTRY' ;
1129 
1130       EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_WOM_AREA_STATE FOR ' || l_schema || '.CSF_WOM_AREA_STATE' ;
1131 
1133 
1134       EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_WOM_BOUNDARY_STATE FOR ' || l_schema || '.CSF_WOM_BOUNDARY_STATE' ;
1135 
1136       EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_WOM_CITY_CAPITAL FOR ' || l_schema || '.CSF_WOM_CITY_CAPITAL' ;
1137 
1138       EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_WOM_CITY_METROPOLIS FOR ' || l_schema || '.CSF_WOM_CITY_METROPOLIS' ;
1139 
1140       EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_WOM_CITY_SMALL FOR ' || l_schema || '.CSF_WOM_CITY_SMALL' ;
1141 
1142       EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_WOM_AREA_LANDUSE FOR ' || l_schema || '.CSF_WOM_AREA_LANDUSE' ;
1143 
1144       EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_WOM_POI FOR ' || l_schema || '.CSF_WOM_POI' ;
1145 
1146       EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_WOM_ROAD_HIGHWAY FOR ' || l_schema || '.CSF_WOM_ROAD_HIGHWAY' ;
1147 
1148       EXECUTE IMMEDIATE 'CREATE  OR REPLACE SYNONYM CSF_WOM__MD_META_BOUNDING_RECT FOR ' || l_schema || '.CSF_WOM__MD_META_BOUNDING_RECT' ;
1149 
1150     END IF;
1151 
1152     put_stream(g_log, 'All the SYNONYMS are created. ' );
1153 
1154     put_stream(g_log, 'End of Procedure CREATE_SYNONYMS for  ' || p_data_set_name );
1155 
1156     put_stream(g_log, '================================================ ' );
1157 
1158     EXCEPTION
1159       WHEN others THEN
1160 
1161         retcode := 1;
1162 
1163         errbuf := sqlerrm;
1164 
1165         put_stream(g_output, 'CREATE_SYNONYMS PROCEDURE HAS FAILED' ||SQLCODE||'-'|| SQLERRM);
1166 
1167         put_stream(g_log, 'CREATE_SYNONYMS PROCEDURE HAS FAILED' || SQLCODE||'-'||SQLERRM);
1168 
1169         RAISE FND_API.G_EXC_ERROR;
1170 
1171   END CREATE_SYNONYMS;
1172 
1173   PROCEDURE DROP_TABLE( p_table_name IN VARCHAR2)
1174   IS
1175   BEGIN
1176     EXECUTE IMMEDIATE 'DROP TABLE ' || p_table_name;
1177 
1178     put_stream(g_log, p_table_name || ' dropped');
1179 
1180   EXCEPTION
1181 
1182     WHEN OTHERS THEN
1183 
1184       IF SQLCODE = -00942 THEN
1185 
1186         put_stream(g_log, p_table_name || ' does not exist');
1187 
1188       ELSE
1189 
1190         put_stream(g_output, 'DROP_TABLE PROCEDURE HAS FAILED' ||SQLCODE||'-'|| SQLERRM);
1191 
1192         put_stream(g_log, 'DROP_TABLE PROCEDURE HAS FAILED' || SQLCODE||'-'||SQLERRM);
1193 
1194         RAISE FND_API.G_EXC_ERROR;
1195 
1196       END IF;
1197 
1198   END DROP_TABLE;
1199 
1200   PROCEDURE DROP_SPATIAL_TABLES( p_data_set_name IN VARCHAR2,
1201                                  errbuf    OUT nocopy     VARCHAR2,
1202                                  retcode   OUT nocopy     VARCHAR2 )
1203   IS
1204    l_data_set_name        VARCHAR2(40);
1205 
1206    l_schema    VARCHAR2(10);
1207 
1208   BEGIN
1209 
1210     put_stream(g_log, '  ' );
1211 
1212     put_stream(g_log, 'Start of Procedure DROP_SPATIAL_TABLES for  ' || p_data_set_name );
1213 
1214     put_stream(g_log, '================================================ ' );
1215 
1216     l_data_set_name    := p_data_set_name ;
1217 
1218     l_schema  := 'CSF';
1219 
1220     IF l_data_set_name = 'NONE' THEN
1221 
1222       l_data_set_name := '';
1223 
1224     ELSE
1225 
1226       l_data_set_name := '_' || l_data_set_name;
1227 
1228     END IF;
1229 
1230     DROP_TABLE( l_schema || '.CSF_LF_BLOCKS' || l_data_set_name);
1231 
1232     DROP_TABLE( l_schema || '.CSF_LF_NAMES' || l_data_set_name);
1233 
1234     DROP_TABLE( l_schema || '.CSF_LF_PLACES' || l_data_set_name);
1235 
1236     DROP_TABLE( l_schema || '.CSF_LF_PLACE_NAMES' || l_data_set_name);
1237 
1238     DROP_TABLE( l_schema || '.CSF_LF_PLACE_POSTCS' || l_data_set_name);
1239 
1240     DROP_TABLE( l_schema || '.CSF_LF_POIS' || l_data_set_name);
1241 
1242     DROP_TABLE( l_schema || '.CSF_LF_POI_NAMES' || l_data_set_name);
1243 
1244     DROP_TABLE( l_schema || '.CSF_LF_POSTCODES' || l_data_set_name);
1245 
1246     DROP_TABLE( l_schema || '.CSF_LF_ROADSEGMENTS' || l_data_set_name);
1247 
1248     DROP_TABLE( l_schema || '.CSF_LF_ROADSEGM_NAMES' || l_data_set_name);
1249 
1250     DROP_TABLE( l_schema || '.CSF_LF_ROADSEGM_PLACES' || l_data_set_name);
1251 
1252     DROP_TABLE( l_schema || '.CSF_LF_ROADSEGM_POSTS' || l_data_set_name);
1253 
1254     DROP_TABLE( l_schema || '.CSF_MD_ADM_BOUNDS' || l_data_set_name);
1255 
1256     DROP_TABLE( l_schema || '.CSF_MD_HYDROS' || l_data_set_name);
1257 
1258     DROP_TABLE( l_schema || '.CSF_MD_LAND_USES' || l_data_set_name);
1259 
1260     DROP_TABLE( l_schema || '.CSF_MD_NAMES' || l_data_set_name);
1261 
1262     DROP_TABLE( l_schema || '.CSF_MD_POIS' || l_data_set_name);
1263 
1264     DROP_TABLE( l_schema || '.CSF_MD_POI_NM_ASGNS' || l_data_set_name);
1265 
1266     DROP_TABLE( l_schema || '.CSF_MD_RAIL_SEGS' || l_data_set_name);
1267 
1268     DROP_TABLE( l_schema || '.CSF_MD_RDSEG_NM_ASGNS' || l_data_set_name);
1269 
1270     DROP_TABLE( l_schema || '.CSF_MD_RD_SEGS' || l_data_set_name);
1271 
1272     DROP_TABLE( l_schema || '.CSF_TDS_BINARY_MAPS' || l_data_set_name);
1273 
1274     DROP_TABLE( l_schema || '.CSF_TDS_BINARY_TILES' || l_data_set_name);
1275 
1276     DROP_TABLE( l_schema || '.CSF_TDS_CONDITIONS' || l_data_set_name);
1277 
1278     DROP_TABLE( l_schema || '.CSF_TDS_COND_SEGS' || l_data_set_name);
1279 
1280     DROP_TABLE( l_schema || '.CSF_TDS_INTERVALS' || l_data_set_name);
1281 
1282     DROP_TABLE( l_schema || '.CSF_TDS_NODES' || l_data_set_name);
1283 
1287 
1284     DROP_TABLE( l_schema || '.CSF_TDS_RDBLCK_INTVLS' || l_data_set_name);
1285 
1286     DROP_TABLE( l_schema || '.CSF_TDS_RDBLCK_SGMNTS' || l_data_set_name);
1288     DROP_TABLE( l_schema || '.CSF_TDS_ROADBLOCKS' || l_data_set_name);
1289 
1290     DROP_TABLE( l_schema || '.CSF_TDS_SEGMENTS' || l_data_set_name);
1291 
1292     DROP_TABLE( l_schema || '.CSF_TDS_SEGM_NODES' || l_data_set_name);
1293 
1294     DROP_TABLE( l_schema || '.CSF_TDS_TILES' || l_data_set_name);
1295 
1296     DROP_TABLE( l_schema || '.CSF_SDM_CTRY_PROFILES' || l_data_set_name);
1297 
1298     DROP_TABLE( l_schema || '.CSF_SPATIAL_STAT_M' || l_data_set_name);
1299 
1300     DROP_TABLE( l_schema || '.CSF_SPATIAL_VER_M' || l_data_set_name);
1301 
1302     DROP_TABLE( l_schema || '.CSF_SPATIAL_STAT_TILES_M' || l_data_set_name);
1303 
1304     DROP_TABLE( l_schema || '.CSF_SPATIAL_STREET_TYPES_M' || l_data_set_name);
1305 
1306     IF (p_data_set_name = 'NONE' OR l_data_set_name = '') THEN
1307 
1308       DROP_TABLE( l_schema || '.CSF_WOM_AREA_COUNTRY');
1309 
1310       DROP_TABLE( l_schema || '.CSF_WOM_AREA_LAKE');
1311 
1312       DROP_TABLE( l_schema || '.CSF_WOM_AREA_OCEAN');
1313 
1314       DROP_TABLE( l_schema || '.CSF_WOM_AREA_STATE');
1315 
1316       DROP_TABLE( l_schema || '.CSF_WOM_BOUNDARY_COUNTRY');
1317 
1318       DROP_TABLE( l_schema || '.CSF_WOM_BOUNDARY_STATE');
1319 
1320       DROP_TABLE( l_schema || '.CSF_WOM_CITY_CAPITAL');
1321 
1322       DROP_TABLE( l_schema || '.CSF_WOM_CITY_METROPOLIS');
1323 
1324       DROP_TABLE( l_schema || '.CSF_WOM_CITY_SMALL');
1325 
1326       DROP_TABLE( l_schema || '.CSF_WOM_AREA_LANDUSE');
1327 
1328       DROP_TABLE( l_schema || '.CSF_WOM_POI');
1329 
1330       DROP_TABLE( l_schema || '.CSF_WOM_ROAD_HIGHWAY');
1331 
1332       DROP_TABLE( l_schema || '.CSF_WOM__MD_META_BOUNDING_RECT');
1333 
1334     END IF;
1335 
1336     put_stream(g_log, 'All the spatial tables are dropped. ' );
1337 
1338     put_stream(g_log, 'End of Procedure DROP_SPATIAL_TABLES for  ' || p_data_set_name );
1339 
1340     put_stream(g_log, '================================================ ' );
1341 
1342     EXCEPTION
1343       WHEN others THEN
1344 
1345         retcode := 1;
1346 
1347         errbuf := sqlerrm;
1348 
1349         put_stream(g_output, 'DROP_SPATIAL_TABLES PROCEDURE HAS FAILED' ||SQLCODE||'-'|| SQLERRM);
1350 
1351         put_stream(g_log, 'DROP_SPATIAL_TABLES PROCEDURE HAS FAILED' || SQLCODE||'-'||SQLERRM);
1352 
1353         RAISE FND_API.G_EXC_ERROR;
1354 
1355   END DROP_SPATIAL_TABLES;
1356 
1357   PROCEDURE DROP_MATERIALIZED_VIEW( p_mv_name IN VARCHAR2)
1358   IS
1359   BEGIN
1360 
1361     EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW ' || p_mv_name;
1362 
1363     put_stream(g_log, p_mv_name || ' dropped');
1364 
1365   EXCEPTION
1366 
1367     WHEN OTHERS THEN
1368 
1369       IF SQLCODE = -12003 THEN
1370 
1371         put_stream(g_log, p_mv_name || ' does not exist');
1372 
1373       ELSE
1374 
1375         put_stream(g_output, 'DROP_MATERIALIZED_VIEW PROCEDURE HAS FAILED' ||SQLCODE||'-'|| SQLERRM);
1376 
1377         put_stream(g_log, 'DROP_MATERIALIZED_VIEW PROCEDURE HAS FAILED' || SQLCODE||'-'||SQLERRM);
1378 
1379         RAISE FND_API.G_EXC_ERROR;
1380 
1381       END IF;
1382 
1383   END DROP_MATERIALIZED_VIEW;
1384 
1385   PROCEDURE DROP_MATERIALIZED_VIEWS( p_data_set_name IN VARCHAR2,
1386                                      errbuf    OUT nocopy     VARCHAR2,
1387                                      retcode   OUT nocopy     VARCHAR)
1388   IS
1389     l_data_set_name        VARCHAR2(40);
1390 	l_schema               VARCHAR2(10);
1391   BEGIN
1392 
1393     put_stream(g_log, '  ' );
1394 
1395     put_stream(g_log, 'Start of Procedure DROP_MATERIALIZED_VIEWS for  ' || p_data_set_name );
1396 
1397     put_stream(g_log, '================================================ ' );
1398 
1399 	l_schema := 'APPS';
1400 
1401     l_data_set_name    := p_data_set_name ;
1402 
1403     IF l_data_set_name = 'NONE' THEN
1404 
1405       l_data_set_name := '';
1406 
1407     ELSE
1408 
1409       l_data_set_name := '_' || l_data_set_name;
1410 
1411     END IF;
1412 
1413     /*DROP_MATERIALIZED_VIEW(l_schema || '.CSF_MD_RD_SEGS_FUN4'|| l_data_set_name || '_V');
1414 
1415     DROP_MATERIALIZED_VIEW(l_schema || '.CSF_MD_RD_SEGS_FUN3'|| l_data_set_name || '_V');
1416 
1417     DROP_MATERIALIZED_VIEW(l_schema || '.CSF_MD_HYDROS_MAT'|| l_data_set_name || '_V');
1418 
1419     DROP_MATERIALIZED_VIEW(l_schema || '.CSF_MD_ADM_BOUNDS_MAT'|| l_data_set_name || '_V');
1420 
1421     DROP_MATERIALIZED_VIEW(l_schema || '.CSF_MD_LAND_USES_MAT'|| l_data_set_name || '_V');
1422 
1423     DROP_MATERIALIZED_VIEW(l_schema || '.CSF_MD_POIS_MAT'|| l_data_set_name || '_V');
1424 
1425     DROP_MATERIALIZED_VIEW(l_schema || '.CSF_MD_RAIL_SEGS_MAT'|| l_data_set_name || '_V');
1426 
1427     DROP_MATERIALIZED_VIEW(l_schema || '.CSF_MD_RD_SEGS_FUN0'|| l_data_set_name || '_V');
1428 
1429     DROP_MATERIALIZED_VIEW(l_schema || '.CSF_MD_RD_SEGS_FUN1'|| l_data_set_name || '_V');
1430 
1431     DROP_MATERIALIZED_VIEW(l_schema || '.CSF_MD_RD_SEGS_FUN2'|| l_data_set_name || '_V');
1432 
1433     IF (p_data_set_name = 'NONE' OR l_data_set_name = '') THEN
1434 
1435        DROP_MATERIALIZED_VIEW(l_schema || '.CSF_WOM_ROAD_HIWAY_MAT_V');
1436 
1437     END IF;    */
1441     put_stream(g_log, 'End of Procedure DROP_MATERIALIZED_VIEWS for  ' || p_data_set_name );
1438 
1439     put_stream(g_log, 'All the MATERIALIZED VIEWS are dropped. ' );
1440 
1442 
1443     put_stream(g_log, '================================================ ' );
1444 
1445     EXCEPTION
1446 
1447       WHEN others THEN
1448 
1449         retcode := 1;
1450 
1451         errbuf := sqlerrm;
1452 
1453         put_stream(g_output, 'DROP_MATERIALIZED_VIEWS PROCEDURE HAS FAILED' ||SQLCODE||'-'|| SQLERRM);
1454 
1455         put_stream(g_log, 'DROP_MATERIALIZED_VIEWS PROCEDURE HAS FAILED' || SQLCODE||'-'||SQLERRM);
1456 
1457         RAISE FND_API.G_EXC_ERROR;
1458 
1459   END DROP_MATERIALIZED_VIEWS;
1460 
1461   PROCEDURE DROP_TTSP_STATS(   p_data_set_name    IN VARCHAR2,
1462                                errbuf    OUT nocopy     VARCHAR2,
1463                                retcode   OUT nocopy     VARCHAR)
1464   IS
1465 	l_schema    VARCHAR2(10);
1466   BEGIN
1467 
1468     put_stream(g_log, '  ' );
1469 
1470     put_stream(g_log, 'Start of Procedure DROP_TTSP_STATS for  ' || p_data_set_name );
1471 
1472     put_stream(g_log, '================================================ ' );
1473 
1474     l_schema  := 'APPS';
1475 
1476     DROP_TABLE(l_schema || '.SDO_INDEX_TTS_METADATA$');
1477 
1478     l_schema  := 'CSF';
1479 
1480     DROP_TABLE(l_schema || '.SDO_INDEX_TTS_METADATA$');
1481     --EXECUTE IMMEDIATE 'DROP TABLESPACE '||p_tablespace ||' INCLUDING CONTENTS';
1482 
1483     put_stream(g_log, 'TTSP Statistical information tables dropped for APPS and CSF user' );
1484 
1485     put_stream(g_log, 'End of Procedure DROP_TTSP_STATS for  ' || p_data_set_name );
1486 
1487     put_stream(g_log, '================================================ ' );
1488 
1489     EXCEPTION
1490       WHEN others THEN
1491 
1492         retcode := 1;
1493 
1494         errbuf := sqlerrm;
1495 
1496         put_stream(g_output, 'DROP_TTSP_STATS PROCEDURE HAS FAILED' ||SQLCODE||'-'|| SQLERRM);
1497 
1498         put_stream(g_log, 'DROP_TTSP_STATS PROCEDURE HAS FAILED' || SQLCODE||'-'||SQLERRM);
1499 
1500         RAISE FND_API.G_EXC_ERROR;
1501 
1502   END DROP_TTSP_STATS;
1503 
1504  END CSF_SPATIAL_TTSP_PVT;
1505