[Home] [Help]
127: emesg VARCHAR2(250);
128: BEGIN
129: -- STEP 1 --
130: -- Delete entries from Archived_Lobs --
131: DELETE FROM FRM_ARCHIVED_LOBS WHERE FILE_ID IN (SELECT FILE_ID FROM FRM_DOCUMENT_DETAILS WHERE ARCHIVED_FLAG = 'N' AND END_DATE <= SYSDATE);
132:
133:
134: -- STEP 2 --
135: -- Select Document_ID those marked for archival
132:
133:
134: -- STEP 2 --
135: -- Select Document_ID those marked for archival
136: -- Loop through the list and make entry into FRM_ARCHIVED_LOBS
137: FOR DOCUMENT_ROW IN (SELECT LOBS.FILE_ID, LOBS.FILE_NAME, LOBS.FILE_CONTENT_TYPE, LOBS.FILE_DATA, LOBS.UPLOAD_DATE, LOBS.PROGRAM_NAME, LOBS.PROGRAM_TAG FROM FRM_REPOSITORY_LOBS LOBS,FRM_DOCUMENT_DETAILS DOC
138: WHERE
139: LOBS.FILE_ID = DOC.FILE_ID AND
140: DOC.ARCHIVED_FLAG = 'Y' AND
139: LOBS.FILE_ID = DOC.FILE_ID AND
140: DOC.ARCHIVED_FLAG = 'Y' AND
141: DOC.END_DATE <= SYSDATE)
142: LOOP
143: INSERT INTO FRM_ARCHIVED_LOBS
144: (FILE_ID,FILE_NAME,FILE_CONTENT_TYPE ,FILE_DATA,UPLOAD_DATE ,EXPIRATION_DATE,PROGRAM_NAME,PROGRAM_TAG ,CREATION_DATE ,CREATED_BY ,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,LAST_UPDATE_DATE ) VALUES
145: (DOCUMENT_ROW.FILE_ID ,DOCUMENT_ROW.FILE_NAME, DOCUMENT_ROW.FILE_CONTENT_TYPE, DOCUMENT_ROW.FILE_DATA, DOCUMENT_ROW.UPLOAD_DATE,'',DOCUMENT_ROW.PROGRAM_NAME, DOCUMENT_ROW.PROGRAM_TAG, SYSDATE,P_USER_ID,P_USER_ID,0,SYSDATE);
146: end loop;
147: -- End Loop