The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE UPDATE_PATH (
p_short_name IN VARCHAR2,
p_domain IN VARCHAR2,
p_old_str IN varchar2,
p_new_str IN OUT NOCOPY varchar2,
x_msg IN OUT NOCOPY CLOB
--p_isNewTable IN NUMBER
) IS
--str_count NUMBER ;
SELECT document_id, dm_folder_path old_path, REPLACE(dm_folder_path, c_old_str, c_new_str) new_path
FROM
FND_DOCUMENTS
WHERE
dm_folder_path IS NOT NULL AND dm_node= repos_id
AND dm_folder_path LIKE c_old_str ||'%';
SELECT document_id, dm_folder_path old_path,
REPLACE(
REPLACE(c_new_str, 'first.last',SUBSTR(dm_folder_path, 2, (INSTR(dm_folder_path, '-Public')-2))),
'',UPPER(SUBSTR(dm_folder_path, 2, 1))) || SubStr(dm_folder_path, InStr(dm_folder_path, '-Public')+7) new_path
FROM
FND_DOCUMENTS
WHERE
dm_folder_path IS NOT NULL
AND dm_node= repos_id
AND dm_folder_path LIKE '/%-Public%'
AND NOT dm_folder_path LIKE '/%/%-Public%'
union
SELECT document_id, dm_folder_path old_path,
REPLACE(
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)))),
'',
UPPER(SUBSTR(dm_folder_path, Length('/AllPublic/Users/Users-_/')+1, 1))
)|| SubStr(dm_folder_path, InStr(dm_folder_path, '-Public')+7) new_path
FROM
FND_DOCUMENTS
WHERE
dm_folder_path IS NOT NULL
AND dm_node= repos_id
AND dm_folder_path LIKE '/AllPublic/Users/Users-_/%-Public%' ;
SELECT doc.document_id, doc.dm_folder_path old_path,
REPLACE(dm_folder_path,c_old_str, c_new_str )||'/'||REPLACE(tl.file_name,'-Public','') new_path
from
fnd_documents doc,
fnd_documents_tl tl
where
doc.document_id = tl.document_id
AND dm_node = repos_id
AND tl.LANGUAGE ='US'
AND (dm_folder_path like c_old_str OR dm_folder_path like '/AllPublic/Users/Users-_')
AND tl.file_name LIKE '%-Public'
union
SELECT document_id, dm_folder_path old_path,
REPLACE(
dm_folder_path,
(c_old_str ||
(SUBSTR(dm_folder_path, Length(c_old_str)+1,(InStr(SUBSTR(dm_folder_path, Length(c_old_str)),'-Public') -2)))||'-Public'
),
(c_new_str ||
(SUBSTR(dm_folder_path, Length(c_old_str)+1,(InStr(SUBSTR(dm_folder_path, Length(c_old_str)),'-Public') -2)))
||(SUBSTR(dm_folder_path, Length(c_old_str)+1,(InStr(SUBSTR(dm_folder_path, Length(c_old_str)),'-Public') -2)))||'-Public'
)
) new_path
from
fnd_documents
where
dm_node = repos_id
AND dm_folder_path like c_old_str||'%-Public%'
AND not dm_folder_path like c_old_str||'/%/%-Public%' ;
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
from
fnd_documents doc,
fnd_documents_tl tl
where
doc.document_id = tl.document_id
AND dm_node = repos_id
AND tl.LANGUAGE ='US'
AND tl.file_name in ('Workspaces' ,'SharedFolders')
AND doc.dm_folder_path = c_old_str ;
select fnd_global.local_chr(10) into newline_char from dual ;
SELECT id, DAV_URL, SERVICE_URL INTO repos_id,old_dav_url, old_service_url FROM dom_repositories WHERE short_name = p_short_name ;
select INSTR(old_dav_url, '/files/content') , INSTR(old_dav_url, '/content') , INSTR(old_dav_url, '/content/dav') into idx1, idx2, idx3 from dual ;
UPDATE dom_repositories SET
DAV_URL = REPLACE(DAV_URL,'files/content','content/dav')
WHERE id = repos_id ;
UPDATE dom_repositories SET
DAV_URL = REPLACE(DAV_URL,'content','content/dav')
WHERE id = repos_id ;
select INSTR(old_service_url, '/files/app') , INSTR(old_service_url, '/app') , INSTR(old_service_url, '/content/app') into idx1, idx2, idx3 from dual ;
UPDATE dom_repositories SET
SERVICE_URL = REPLACE(SERVICE_URL,'files/app','content/app')
WHERE id = repos_id ;
UPDATE dom_repositories SET
SERVICE_URL = REPLACE(SERVICE_URL,'app','content/app')
WHERE id = repos_id ;
SELECT REPLACE(p_new_str , 'DomainName', p_domain ) into p_new_str FROM dual ;
UPDATE fnd_documents SET dm_folder_path = new_path
WHERE document_id = doc_id ;
UPDATE ENG_ATTACHMENT_CHANGES SET source_path = new_path , FILE_NAME = 'Libraries'
WHERE SOURCE_DOCUMENT_ID = doc_id ;
UPDATE fnd_documents_tl SET file_name = 'Libraries'
WHERE document_id = doc_id ;
UPDATE fnd_documents SET dm_folder_path = new_path
WHERE document_id = doc_id ;
UPDATE ENG_ATTACHMENT_CHANGES SET source_path = new_path
WHERE SOURCE_DOCUMENT_ID = doc_id ;
UPDATE fnd_documents SET dm_folder_path = new_path
WHERE document_id = doc_id ;
UPDATE ENG_ATTACHMENT_CHANGES SET source_path = new_path
WHERE SOURCE_DOCUMENT_ID = doc_id ;
UPDATE fnd_documents SET dm_folder_path = new_path
WHERE document_id = doc_id ;
UPDATE ENG_ATTACHMENT_CHANGES SET source_path = new_path
WHERE SOURCE_DOCUMENT_ID = doc_id ;
x_msg := x_msg || newline_char || '*** Folder path updated successfully for path : ' || p_old_str || ' -> '|| p_new_str || ' ***' ;
SELECT document_id, dm_folder_path old_path, REPLACE(dm_folder_path, c_old_str, c_new_str) new_path
FROM
FND_DOCUMENTS
WHERE
dm_folder_path IS NOT NULL AND dm_node= repos_id
AND dm_folder_path LIKE c_old_str ||'%'
AND document_id = p_doc_id ;
SELECT document_id, dm_folder_path old_path,
REPLACE(
REPLACE(c_new_str, 'first.last',SUBSTR(dm_folder_path, 2, (INSTR(dm_folder_path, '-Public')-2))),
'',UPPER(SUBSTR(dm_folder_path, 2, 1))) || SubStr(dm_folder_path, InStr(dm_folder_path, '-Public')+7) new_path
FROM
FND_DOCUMENTS
WHERE
dm_folder_path IS NOT NULL
AND dm_node= repos_id
AND dm_folder_path LIKE '/%-Public%'
AND NOT dm_folder_path LIKE '/%/%-Public%'
AND document_id = p_doc_id
union
SELECT document_id, dm_folder_path old_path,
REPLACE(
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)))),
'',
UPPER(SUBSTR(dm_folder_path, Length('/AllPublic/Users/Users-_/')+1, 1))
)|| SubStr(dm_folder_path, InStr(dm_folder_path, '-Public')+7) new_path
FROM
FND_DOCUMENTS
WHERE
dm_folder_path IS NOT NULL
AND dm_node= repos_id
AND dm_folder_path LIKE '/AllPublic/Users/Users-_/%-Public%'
AND document_id = p_doc_id ;
SELECT doc.document_id, doc.dm_folder_path old_path,
REPLACE(dm_folder_path,c_old_str, c_new_str )||'/'||REPLACE(tl.file_name,'-Public','') new_path
from
fnd_documents doc,
fnd_documents_tl tl
where
doc.document_id = tl.document_id
AND dm_node = repos_id
AND tl.LANGUAGE ='US'
AND (dm_folder_path like c_old_str OR dm_folder_path like '/AllPublic/Users/Users-_')
AND tl.file_name LIKE '%-Public'
AND doc.document_id = p_doc_id
union
SELECT document_id, dm_folder_path old_path,
REPLACE(
dm_folder_path,
(c_old_str ||
(SUBSTR(dm_folder_path, Length(c_old_str)+1,(InStr(SUBSTR(dm_folder_path, Length(c_old_str)),'-Public') -2)))||'-Public'
),
(c_new_str ||
(SUBSTR(dm_folder_path, Length(c_old_str)+1,(InStr(SUBSTR(dm_folder_path, Length(c_old_str)),'-Public') -2)))
||(SUBSTR(dm_folder_path, Length(c_old_str)+1,(InStr(SUBSTR(dm_folder_path, Length(c_old_str)),'-Public') -2)))||'-Public'
)
) new_path
from
fnd_documents
where
dm_node = repos_id
AND dm_folder_path like c_old_str||'%-Public%'
AND not dm_folder_path like c_old_str||'/%/%-Public%'
AND document_id = p_doc_id ;
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
from
fnd_documents doc,
fnd_documents_tl tl
where
doc.document_id = tl.document_id
AND dm_node = repos_id
AND tl.LANGUAGE ='US'
AND tl.file_name in ('Workspaces' ,'SharedFolders')
AND doc.dm_folder_path = c_old_str
AND doc.document_id = p_doc_id ;
select fnd_global.local_chr(10) into newline_char from dual ;
SELECT id, DAV_URL, SERVICE_URL INTO repos_id,old_dav_url, old_service_url FROM dom_repositories WHERE short_name = p_short_name ;
SELECT REPLACE(p_new_str , 'DomainName', p_domain ) into p_new_str FROM dual ;