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.DOM_REPOS_MIG_PKG

Source


1 PACKAGE BODY DOM_REPOS_MIG_PKG AS
2 /* $Header: DOMVRUGB.pls 120.1 2006/09/04 16:17:27 rkhasa noship $ */
3 
4 PROCEDURE UPDATE_PATH (
5   p_short_name IN VARCHAR2,
6   p_domain IN VARCHAR2,
7   p_old_str IN varchar2,
8   p_new_str IN OUT NOCOPY varchar2,
9   x_msg IN OUT NOCOPY CLOB
10   --p_isNewTable IN NUMBER
11 ) IS
12     --str_count NUMBER ;
13     doc_id NUMBER;
14     old_path VARCHAR2(1000);
15     new_path VARCHAR2(1000);
16     repos_id NUMBER ;
17     dot_index NUMBER ;    --used to check if old string is of type firs.last
18     newline_char VARCHAR2(2) ;
19     old_dav_url varchar2(240);
20     old_service_url varchar2(240);
21     idx1 number;
22     idx2 number;
23     idx3 number;
24 
25 
26     --this cursor will give us document_id, old path, new path for each set of path replacement strings
27     CURSOR doc_cur( c_old_str VARCHAR2, c_new_str VARCHAR2) IS
28         SELECT document_id,  dm_folder_path old_path, REPLACE(dm_folder_path, c_old_str, c_new_str) new_path
29         FROM
30           FND_DOCUMENTS
31         WHERE
32           dm_folder_path IS NOT NULL AND dm_node= repos_id
33           AND dm_folder_path LIKE  c_old_str ||'%';
34 
35  -- this cursor takes care of folder paths with user names
36     CURSOR doc_cur2( c_old_str VARCHAR2, c_new_str VARCHAR2) IS
37         SELECT document_id,  dm_folder_path old_path,
38         REPLACE(
39             REPLACE(c_new_str, 'first.last',SUBSTR(dm_folder_path, 2, (INSTR(dm_folder_path, '-Public')-2))),
40             '<f>',UPPER(SUBSTR(dm_folder_path, 2, 1))) || SubStr(dm_folder_path, InStr(dm_folder_path, '-Public')+7) new_path
41         FROM
42           FND_DOCUMENTS
43         WHERE
44           dm_folder_path IS NOT NULL
45           AND dm_node= repos_id
46           AND dm_folder_path LIKE   '/%-Public%'
47           AND NOT dm_folder_path LIKE '/%/%-Public%'
48     union
49     SELECT document_id,  dm_folder_path old_path,
50         REPLACE(
51             REPLACE(c_new_str, 'first.last', SUBSTR(dm_folder_path, Length('/AllPublic/Users/Users-_/')+1, (INSTR(dm_folder_path, '-Public')-(Length('/AllPublic/Users/Users-_/')+1)))),
52             '<f>',
53             UPPER(SUBSTR(dm_folder_path, Length('/AllPublic/Users/Users-_/')+1, 1))
54         )|| SubStr(dm_folder_path, InStr(dm_folder_path, '-Public')+7)  new_path
55         FROM
56           FND_DOCUMENTS
57         WHERE
58           dm_folder_path IS NOT NULL
59           AND dm_node= repos_id
60           AND dm_folder_path LIKE   '/AllPublic/Users/Users-_/%-Public%'  ;
61 
62 
63     CURSOR doc_cur3(c_old_str VARCHAR2, c_new_str VARCHAR2 ) IS
64         SELECT doc.document_id,  doc.dm_folder_path old_path,
65         REPLACE(dm_folder_path,c_old_str, c_new_str )||'/'||REPLACE(tl.file_name,'-Public','') new_path
66         from
67             fnd_documents  doc,
68             fnd_documents_tl tl
69         where
70           doc.document_id = tl.document_id
71           AND dm_node = repos_id
72           AND tl.LANGUAGE ='US'
73           AND (dm_folder_path like c_old_str OR dm_folder_path like '/AllPublic/Users/Users-_')
74           AND tl.file_name LIKE '%-Public'
75         union
76          SELECT document_id,  dm_folder_path old_path,
77               REPLACE(
78               dm_folder_path,
79                 (c_old_str ||
80                 (SUBSTR(dm_folder_path, Length(c_old_str)+1,(InStr(SUBSTR(dm_folder_path, Length(c_old_str)),'-Public') -2)))||'-Public'
81                 ),
82                (c_new_str ||
83                 (SUBSTR(dm_folder_path, Length(c_old_str)+1,(InStr(SUBSTR(dm_folder_path, Length(c_old_str)),'-Public') -2)))
84                 ||(SUBSTR(dm_folder_path, Length(c_old_str)+1,(InStr(SUBSTR(dm_folder_path, Length(c_old_str)),'-Public') -2)))||'-Public'
85                )
86                 ) new_path
87         from
88             fnd_documents
89         where
90           dm_node = repos_id
91           AND dm_folder_path like c_old_str||'%-Public%'
92           AND not dm_folder_path like c_old_str||'/%/%-Public%' ;
93 
94     CURSOR doc_cur4(c_old_str VARCHAR2, c_new_str VARCHAR2 ) IS
95         SELECT doc.document_id,  doc.dm_folder_path old_path, REPLACE(dm_folder_path,c_old_str, c_new_str ) new_path, tl.file_name file_name
96         from
97             fnd_documents  doc,
98             fnd_documents_tl tl
99         where
100           doc.document_id = tl.document_id
101           AND dm_node = repos_id
102           AND tl.LANGUAGE ='US'
103           AND tl.file_name in ('Workspaces' ,'SharedFolders')
104           AND doc.dm_folder_path = c_old_str  ;
105     --end of declaration section
106 
107     BEGIN
108           --  newline_char := '\n' ;
109            select fnd_global.local_chr(10) into newline_char from dual ;
110 
111            SELECT id, DAV_URL, SERVICE_URL INTO repos_id,old_dav_url, old_service_url FROM dom_repositories WHERE short_name = p_short_name ;
112            x_msg := newline_char || newline_char ||' ######## Repository ID:' || repos_id ;
113 
114           --updating dav_url
115            select INSTR(old_dav_url, '/files/content') , INSTR(old_dav_url, '/content') , INSTR(old_dav_url, '/content/dav')  into idx1, idx2, idx3   from dual ;
116 
117            if(idx1 > 1 and  idx3 = 0 ) THEN
118                 UPDATE dom_repositories SET
119                     DAV_URL = REPLACE(DAV_URL,'files/content','content/dav')
120                 WHERE id = repos_id ;
121 
122            elsif(idx2 > 1 and  idx3 = 0 ) THEN
123                 UPDATE dom_repositories SET
124                     DAV_URL = REPLACE(DAV_URL,'content','content/dav')
125                 WHERE id = repos_id ;
126         end if ;
127         --updating service_url
128             select INSTR(old_service_url, '/files/app') , INSTR(old_service_url, '/app') , INSTR(old_service_url, '/content/app')  into idx1, idx2, idx3   from dual ;
129            if(idx1 > 1 and  idx3 = 0 ) THEN
130                 UPDATE dom_repositories SET
131                     SERVICE_URL = REPLACE(SERVICE_URL,'files/app','content/app')
132                 WHERE id = repos_id ;
133 
134            elsif(idx2 > 1 and  idx3 = 0 ) THEN
135                 UPDATE dom_repositories SET
136                     SERVICE_URL = REPLACE(SERVICE_URL,'app','content/app')
137                 WHERE id = repos_id ;
138         end if ;
139 
140         IF(p_old_str IS NOT NULL) THEN
141                     x_msg :=  x_msg ||newline_char || p_old_str || '  -- '|| p_new_str;
142                  SELECT REPLACE(p_new_str , 'DomainName', p_domain ) into p_new_str FROM dual ;
143 
144                  IF(p_old_str = '/AllPublic' ) THEN
145                     x_msg :=  x_msg ||newline_char || 'cursor 4' ;
146                     FOR l_row IN  doc_cur4(p_old_str, p_new_str)
147                      LOOP
148                         doc_id := l_row.document_id ;
149                         old_path:= l_row.old_path ;
150                         new_path:= l_row.new_path ;
151                         x_msg := x_msg || newline_char || 'DocID :' || doc_id || '-  ' || old_path || ' -> ' || new_path ;
152                         UPDATE fnd_documents SET  dm_folder_path = new_path
153                         WHERE document_id = doc_id ;
154                         --updating eng_attachment_changes
155                         UPDATE ENG_ATTACHMENT_CHANGES SET source_path = new_path , FILE_NAME = 'Libraries'
156                         WHERE SOURCE_DOCUMENT_ID = doc_id ;
157 
158                         --changing file name
159                         x_msg := x_msg || newline_char || 'DocID :' || doc_id || '   changing file_name  for language code US, from '|| l_row.file_name || ' to Libraries '  ;
160                         UPDATE fnd_documents_tl SET  file_name = 'Libraries'
161                         WHERE document_id = doc_id ;
162 
163                      END LOOP;
164                  End IF;
165 
166                  IF(p_old_str = '/AllPublic/Workspaces' or p_old_str = '/AllPublic/SharedFolders' ) THEN
167                     x_msg :=  x_msg ||newline_char || 'cursor 3' ;
168                     FOR l_row IN  doc_cur3(p_old_str, p_new_str)
169                      LOOP
170                         doc_id := l_row.document_id ;
171                         old_path:= l_row.old_path ;
172                         new_path:= l_row.new_path ;
173                         x_msg := x_msg || newline_char || 'DocID :' || doc_id || '-  ' || old_path || ' -> ' || new_path ;
174                         UPDATE fnd_documents SET  dm_folder_path = new_path
175                         WHERE document_id = doc_id ;
176                         --updating eng_attachment_changes
177                         UPDATE ENG_ATTACHMENT_CHANGES SET source_path = new_path
178                         WHERE SOURCE_DOCUMENT_ID = doc_id ;
179 
180                      END LOOP;
181 
182                  ELSIF( INSTR(p_old_str,'.') >0 ) THEN
183                     x_msg :=  x_msg ||newline_char || 'cursor 2' ;
184                     FOR l_row IN  doc_cur2(p_old_str, p_new_str)
185                      LOOP
186                         doc_id := l_row.document_id ;
187                         old_path:= l_row.old_path ;
188                         new_path:= l_row.new_path ;
189                         x_msg := x_msg || newline_char || 'DocID :' || doc_id || '-  ' || old_path || ' -> ' || new_path ;
190                         UPDATE fnd_documents SET  dm_folder_path = new_path
191                         WHERE document_id = doc_id ;
192                         --updating eng_attachment_changes
193                         UPDATE ENG_ATTACHMENT_CHANGES SET source_path = new_path
194                         WHERE SOURCE_DOCUMENT_ID = doc_id ;
195                      END LOOP;
196 
197                 ELSE
198                     x_msg :=  x_msg ||newline_char || 'cursor 1' ;
199                      FOR l_row IN  doc_cur(p_old_str, p_new_str)
200                      LOOP
201                         doc_id := l_row.document_id ;
202                         old_path:= l_row.old_path ;
203                         new_path:= l_row.new_path ;
204                         x_msg := x_msg || newline_char || 'DocID :' || doc_id || '-  ' || old_path || ' -> ' || new_path ;
205                         UPDATE fnd_documents SET  dm_folder_path = new_path
206                         WHERE document_id = doc_id ;
207                         --updating eng_attachment_changes
208                         UPDATE ENG_ATTACHMENT_CHANGES SET source_path = new_path
209                         WHERE SOURCE_DOCUMENT_ID = doc_id ;
210                      END LOOP;
211                 END IF;
212         END IF;
213             x_msg := x_msg || newline_char || '*** Folder path updated successfully  for path : ' || p_old_str || ' -> '|| p_new_str || '  ***' ;
214     EXCEPTION
215         WHEN NO_DATA_FOUND THEN
216               x_msg := x_msg || newline_char ||  '*** Script failed : Invalid repository short name ***' ;
217               x_msg := x_msg || newline_char || 'RollBack Done' ;
218               rollback;
219         WHEN OTHERS THEN
220           x_msg := x_msg || newline_char || newline_char ||' *** Script failed with error error is :' || SQLERRM || '***';
221           x_msg := x_msg || newline_char || 'RollBack Done' ;
222           rollback;
223     END;
224 
225 
226 
227 PROCEDURE GET_NEW_PATH (
228   p_short_name IN VARCHAR2,
229   p_domain IN VARCHAR2,
230   p_old_str IN varchar2,
231   p_new_str IN OUT NOCOPY varchar2,
232   p_doc_id IN Number,
233   x_msg IN OUT NOCOPY varchar2
234   --p_isNewTable IN NUMBER
235 ) IS
236     --str_count NUMBER ;
237     doc_id NUMBER;
238     old_path VARCHAR2(1000);
239     new_path VARCHAR2(1000);
240     repos_id NUMBER ;
241     dot_index NUMBER ;    --used to check if old string is of type firs.last
242     newline_char VARCHAR2(2) ;
243     old_dav_url varchar2(240);
244     old_service_url varchar2(240);
245     idx1 number;
246     idx2 number;
247     idx3 number;
248 
249 
250     --this cursor will give us document_id, old path, new path for each set of path replacement strings
254           FND_DOCUMENTS
251     CURSOR doc_cur( c_old_str VARCHAR2, c_new_str VARCHAR2) IS
252         SELECT document_id,  dm_folder_path old_path, REPLACE(dm_folder_path, c_old_str, c_new_str) new_path
253         FROM
255         WHERE
256           dm_folder_path IS NOT NULL AND dm_node= repos_id
257           AND dm_folder_path LIKE  c_old_str ||'%'
258           AND document_id = p_doc_id ;
259 
260  -- this cursor takes care of folder paths with user names
261     CURSOR doc_cur2( c_old_str VARCHAR2, c_new_str VARCHAR2) IS
262         SELECT document_id,  dm_folder_path old_path,
263         REPLACE(
264             REPLACE(c_new_str, 'first.last',SUBSTR(dm_folder_path, 2, (INSTR(dm_folder_path, '-Public')-2))),
265             '<f>',UPPER(SUBSTR(dm_folder_path, 2, 1))) || SubStr(dm_folder_path, InStr(dm_folder_path, '-Public')+7) new_path
266         FROM
267           FND_DOCUMENTS
268         WHERE
269           dm_folder_path IS NOT NULL
270           AND dm_node= repos_id
271           AND dm_folder_path LIKE   '/%-Public%'
272           AND NOT dm_folder_path LIKE '/%/%-Public%'
273           AND document_id = p_doc_id
274     union
275     SELECT document_id,  dm_folder_path old_path,
276         REPLACE(
277             REPLACE(c_new_str, 'first.last', SUBSTR(dm_folder_path, Length('/AllPublic/Users/Users-_/')+1, (INSTR(dm_folder_path, '-Public')-(Length('/AllPublic/Users/Users-_/')+1)))),
278             '<f>',
279             UPPER(SUBSTR(dm_folder_path, Length('/AllPublic/Users/Users-_/')+1, 1))
280         )|| SubStr(dm_folder_path, InStr(dm_folder_path, '-Public')+7)  new_path
281         FROM
282           FND_DOCUMENTS
283         WHERE
284           dm_folder_path IS NOT NULL
285           AND dm_node= repos_id
286           AND dm_folder_path LIKE   '/AllPublic/Users/Users-_/%-Public%'
287           AND document_id = p_doc_id ;
288 
289 
290     CURSOR doc_cur3(c_old_str VARCHAR2, c_new_str VARCHAR2 ) IS
291         SELECT doc.document_id,  doc.dm_folder_path old_path,
292         REPLACE(dm_folder_path,c_old_str, c_new_str )||'/'||REPLACE(tl.file_name,'-Public','') new_path
293         from
294             fnd_documents  doc,
295             fnd_documents_tl tl
296         where
297           doc.document_id = tl.document_id
298           AND dm_node = repos_id
299           AND tl.LANGUAGE ='US'
300           AND (dm_folder_path like c_old_str OR dm_folder_path like '/AllPublic/Users/Users-_')
301           AND tl.file_name LIKE '%-Public'
302           AND doc.document_id = p_doc_id
303         union
304          SELECT document_id,  dm_folder_path old_path,
305               REPLACE(
306               dm_folder_path,
307                 (c_old_str ||
308                 (SUBSTR(dm_folder_path, Length(c_old_str)+1,(InStr(SUBSTR(dm_folder_path, Length(c_old_str)),'-Public') -2)))||'-Public'
309                 ),
310                (c_new_str ||
311                 (SUBSTR(dm_folder_path, Length(c_old_str)+1,(InStr(SUBSTR(dm_folder_path, Length(c_old_str)),'-Public') -2)))
312                 ||(SUBSTR(dm_folder_path, Length(c_old_str)+1,(InStr(SUBSTR(dm_folder_path, Length(c_old_str)),'-Public') -2)))||'-Public'
313                )
314                 ) new_path
315         from
316             fnd_documents
317         where
318           dm_node = repos_id
319           AND dm_folder_path like c_old_str||'%-Public%'
320           AND not dm_folder_path like c_old_str||'/%/%-Public%'
321           AND document_id = p_doc_id ;
322 
323     CURSOR doc_cur4(c_old_str VARCHAR2, c_new_str VARCHAR2 ) IS
324         SELECT doc.document_id,  doc.dm_folder_path old_path, REPLACE(dm_folder_path,c_old_str, c_new_str ) new_path, tl.file_name file_name
325         from
326             fnd_documents  doc,
327             fnd_documents_tl tl
328         where
329           doc.document_id = tl.document_id
330           AND dm_node = repos_id
331           AND tl.LANGUAGE ='US'
332           AND tl.file_name in ('Workspaces' ,'SharedFolders')
333           AND doc.dm_folder_path = c_old_str
334           AND doc.document_id = p_doc_id ;
335     --end of declaration section
336 
337     BEGIN
338           --  newline_char := '\n' ;
339            select fnd_global.local_chr(10) into newline_char from dual ;
340 
341            SELECT id, DAV_URL, SERVICE_URL INTO repos_id,old_dav_url, old_service_url FROM dom_repositories WHERE short_name = p_short_name ;
342            x_msg := newline_char || newline_char ||' ######## Repository ID:' || repos_id ;
343 
344 
345         IF(p_old_str IS NOT NULL) THEN
346                     x_msg :=  x_msg ||newline_char || p_old_str || '  -- '|| p_new_str;
347                  SELECT REPLACE(p_new_str , 'DomainName', p_domain ) into p_new_str FROM dual ;
348 
349                  IF(p_old_str = '/AllPublic' ) THEN
350                     x_msg :=  x_msg ||newline_char || 'cursor 4' ;
351                     FOR l_row IN  doc_cur4(p_old_str, p_new_str)
352                      LOOP
353                         doc_id := l_row.document_id ;
354                         old_path:= l_row.old_path ;
355                         new_path:= l_row.new_path ;
356                         x_msg := x_msg || newline_char || 'DocID :' || doc_id || '-  ' || old_path || ' -> ' || new_path ;
357 
358                         --changing file name
359                         x_msg := x_msg || newline_char || 'DocID :' || doc_id || '   changing file_name  for language code US, from '|| l_row.file_name || ' to Libraries '  ;
360 
361                      END LOOP;
362                  End IF;
363 
364                  IF(p_old_str = '/AllPublic/Workspaces' or p_old_str = '/AllPublic/SharedFolders' ) THEN
365                     x_msg :=  x_msg ||newline_char || 'cursor 3' ;
366                     FOR l_row IN  doc_cur3(p_old_str, p_new_str)
367                      LOOP
368                         doc_id := l_row.document_id ;
369                         old_path:= l_row.old_path ;
370                         new_path:= l_row.new_path ;
371                         x_msg := x_msg || newline_char || 'DocID :' || doc_id || '-  ' || old_path || ' -> ' || new_path ;
372                      END LOOP;
373 
374                  ELSIF( INSTR(p_old_str,'.') >0 ) THEN
375                     x_msg :=  x_msg ||newline_char || 'cursor 2' ;
376                     FOR l_row IN  doc_cur2(p_old_str, p_new_str)
377                      LOOP
378                         doc_id := l_row.document_id ;
379                         old_path:= l_row.old_path ;
380                         new_path:= l_row.new_path ;
381                         x_msg := x_msg || newline_char || 'DocID :' || doc_id || '-  ' || old_path || ' -> ' || new_path ;
382                      END LOOP;
383 
384                 ELSE
385                     x_msg :=  x_msg ||newline_char || 'cursor 1' ;
386                      FOR l_row IN  doc_cur(p_old_str, p_new_str)
387                      LOOP
388                         doc_id := l_row.document_id ;
389                         old_path:= l_row.old_path ;
390                         new_path:= l_row.new_path ;
391                         x_msg := x_msg || newline_char || 'DocID :' || doc_id || '-  ' || old_path || ' -> ' || new_path ;
392                      END LOOP;
393                 END IF;
394         END IF;
395         x_msg := new_path ;
396             --x_msg := x_msg || newline_char || '*** Folder path updated successfully  for path : ' || p_old_str || ' -> '|| p_new_str || '  ***' ;
397     EXCEPTION
398         WHEN NO_DATA_FOUND THEN
399               x_msg := 'NO_DATA_FOUND' ;
400               rollback;
401         WHEN OTHERS THEN
402           x_msg := x_msg || newline_char || newline_char ||' *** Script failed with error error is :' || SQLERRM || '***';
403           rollback;
404     END;
405 
406 END DOM_REPOS_MIG_PKG;