APPS.DOM_REPOS_MIG_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 4

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 ;

Line: 28

        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 ||'%';

Line: 37

        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%'  ;

Line: 64

        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%' ;

Line: 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
        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  ;

Line: 109

           select fnd_global.local_chr(10) into newline_char from dual ;

Line: 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 ;

Line: 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 ;

Line: 118

                UPDATE dom_repositories SET
                    DAV_URL = REPLACE(DAV_URL,'files/content','content/dav')
                WHERE id = repos_id ;

Line: 123

                UPDATE dom_repositories SET
                    DAV_URL = REPLACE(DAV_URL,'content','content/dav')
                WHERE id = repos_id ;

Line: 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 ;

Line: 130

                UPDATE dom_repositories SET
                    SERVICE_URL = REPLACE(SERVICE_URL,'files/app','content/app')
                WHERE id = repos_id ;

Line: 135

                UPDATE dom_repositories SET
                    SERVICE_URL = REPLACE(SERVICE_URL,'app','content/app')
                WHERE id = repos_id ;

Line: 142

                 SELECT REPLACE(p_new_str , 'DomainName', p_domain ) into p_new_str FROM dual ;

Line: 152

                        UPDATE fnd_documents SET  dm_folder_path = new_path
                        WHERE document_id = doc_id ;

Line: 155

                        UPDATE ENG_ATTACHMENT_CHANGES SET source_path = new_path , FILE_NAME = 'Libraries'
                        WHERE SOURCE_DOCUMENT_ID = doc_id ;

Line: 160

                        UPDATE fnd_documents_tl SET  file_name = 'Libraries'
                        WHERE document_id = doc_id ;

Line: 174

                        UPDATE fnd_documents SET  dm_folder_path = new_path
                        WHERE document_id = doc_id ;

Line: 177

                        UPDATE ENG_ATTACHMENT_CHANGES SET source_path = new_path
                        WHERE SOURCE_DOCUMENT_ID = doc_id ;

Line: 190

                        UPDATE fnd_documents SET  dm_folder_path = new_path
                        WHERE document_id = doc_id ;

Line: 193

                        UPDATE ENG_ATTACHMENT_CHANGES SET source_path = new_path
                        WHERE SOURCE_DOCUMENT_ID = doc_id ;

Line: 205

                        UPDATE fnd_documents SET  dm_folder_path = new_path
                        WHERE document_id = doc_id ;

Line: 208

                        UPDATE ENG_ATTACHMENT_CHANGES SET source_path = new_path
                        WHERE SOURCE_DOCUMENT_ID = doc_id ;

Line: 213

            x_msg := x_msg || newline_char || '*** Folder path updated successfully  for path : ' || p_old_str || ' -> '|| p_new_str || '  ***' ;

Line: 252

        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 ;

Line: 262

        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 ;

Line: 291

        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 ;

Line: 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
        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 ;

Line: 339

           select fnd_global.local_chr(10) into newline_char from dual ;

Line: 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 ;

Line: 347

                 SELECT REPLACE(p_new_str , 'DomainName', p_domain ) into p_new_str FROM dual ;