[Home] [Help]
PACKAGE BODY: APPS.FRM_REPOSITORY_MAINTENANCE
Source
1 PACKAGE BODY FRM_REPOSITORY_MAINTENANCE AS
2 /* $Header: frmrepmaintenanceb.pls 120.2 2010/12/08 09:38:29 rgurusam noship $ */
3 --------------------------------------------------------------------------------
4 -- PACKAGE: FRM_REPOSITORY_MAINTENANCE --
5 -- --
6 -- DESCRIPTION: --
7 -- --
8 -- MODIFICATION HISTORY --
9 -- Date Username Description --
10 -- 19-APR-2010 DHVENKAT Created --
11 -- 29-Nov-2010 RGURUSAM Bug 8333050 - Support to overwrite existing reports--
12 -- Introduced PL/SQL Procedure DELETE_DOCUMENT_TIMEFRAME
13 -- to delete a report of a time frame from document --
14 --------------------------------------------------------------------------------
15
16
17 --------------------------------------------------------------------------------
18 -- PROCEDURE: DELETE_MENU_ENTRIES --
19 -- --
20 -- DESCRIPTION: Procedure to delete menu entries --
21 -- --
22 -- MODIFICATION HISTORY --
23 -- Date Username Description --
24 -- 19-APR-2010 DHVENKAT CREATED --
25 --------------------------------------------------------------------------------
26 PROCEDURE DELETE_MENU_ENTRIES(P_MENU_ID IN NUMBER,
27 P_ENTRY_SEQ IN NUMBER)
28 IS
29 emesg VARCHAR2(250);
30 BEGIN
31
32 FND_MENU_ENTRIES_PKG.DELETE_ROW(P_MENU_ID,P_ENTRY_SEQ);
33
34 EXCEPTION
35 WHEN NO_DATA_FOUND THEN
36 emesg := SQLERRM;
37 ROLLBACK;
38
39 END DELETE_MENU_ENTRIES;
40
41 --------------------------------------------------------------------------------
42 -- PROCEDURE: DELETE_FORM_ENTRIES --
43 -- --
44 -- DESCRIPTION: Procedure to delete menu entries --
45 -- --
46 -- MODIFICATION HISTORY --
47 -- Date Username Description --
48 -- 19-APR-2010 DHVENKAT CREATED --
49 --------------------------------------------------------------------------------
50 PROCEDURE DELETE_FORM_ENTRIES(P_FUNC_ID IN NUMBER)
51 IS
52 emesg VARCHAR2(250);
53 BEGIN
54
55 FND_FORM_FUNCTIONS_PKG.DELETE_ROW(P_FUNC_ID);
56 EXCEPTION
57 WHEN NO_data_FOUND THEN
58 emesg := SQLERRM;
59 ROLLBACK;
60
61 END DELETE_FORM_ENTRIES;
62
63
64 --------------------------------------------------------------------------------
65 -- PROCEDURE: DELETE_MENUFORM_ENTRIES --
66 -- --
67 -- DESCRIPTION: Procedure to delete form entries --
68 -- --
69 -- MODIFICATION HISTORY --
70 -- Date Username Description --
71 -- 19-APR-2010 DHVENKAT CREATED --
72 --------------------------------------------------------------------------------
73 PROCEDURE DELETE_MENUFORM_ENTRIES(P_DOCUMENT_ID IN NUMBER)
74 IS
75 emesg VARCHAR2(250);
76 DOCUMENT_PARAM1 VARCHAR2(30);
77 DOCUMENT_PARAM2 VARCHAR2(30);
78 P_FUNC_ID NUMBER;
79 P_MENU_ID NUMBER;
80 P_MENU_SEQ NUMBER;
81 BEGIN
82
83 DOCUMENT_PARAM1 := 'documentId=' || P_DOCUMENT_ID;
84 DOCUMENT_PARAM2 := 'documentId=' || P_DOCUMENT_ID || '&%';
85
86 BEGIN
87 SELECT
88 FormFunctions.FUNCTION_ID,
89 MenuEntries.MENU_ID,
90 MenuEntries.ENTRY_SEQUENCE
91 INTO P_FUNC_ID, P_MENU_ID, P_MENU_SEQ
92 FROM
93 FND_MENU_ENTRIES MenuEntries,
94 FND_FORM_FUNCTIONS FormFunctions
95 WHERE
96 MenuEntries.FUNCTION_ID = FormFunctions.FUNCTION_ID AND
97 FormFunctions.WEB_HTML_CALL='OA.jsp?page=/oracle/apps/frm/report/display/webui/ReportDisplayPG' AND
98 (FormFunctions.PARAMETERS = DOCUMENT_PARAM1 OR FormFunctions.PARAMETERS LIKE DOCUMENT_PARAM2);
99
100 DELETE_MENU_ENTRIES (P_MENU_ID, P_MENU_SEQ);
101
102 DELETE_FORM_ENTRIES (P_FUNC_ID);
103
104 EXCEPTION
105 WHEN NO_DATA_FOUND THEN NULL;
106 END;
107
108 EXCEPTION
109 WHEN NO_data_FOUND THEN
110 emesg := SQLERRM;
111 ROLLBACK;
112
113 END DELETE_MENUFORM_ENTRIES;
114
115
116 --------------------------------------------------------------------------------
117 -- PROCEDURE: DELETE_MARKED_ENTRIES --
118 -- --
119 -- DESCRIPTION: Delete all rows marked for delete --
120 -- --
121 -- MODIFICATION HISTORY --
122 -- Date Username Description --
123 -- 19-APR-2010 DHVENKAT CREATED --
124 --------------------------------------------------------------------------------
125 PROCEDURE DELETE_MARKED_ENTRIES(P_USER_ID IN NUMBER)
126 IS
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
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
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
148
149 -- Delete entries from Repository Lobs
150 DELETE FROM FRM_REPOSITORY_LOBS WHERE FILE_ID IN (SELECT FILE_ID FROM FRM_DOCUMENT_DETAILS WHERE END_DATE <= SYSDATE);
151
152
153 -- STEP 3 --
154 -- Select Document_ID those marked for delete to remove corresponding menu / form entries
155 -- Loop through the list and remove menu / form entries
156 FOR DOCUMENT_ROW IN (SELECT DOCUMENT_ID FROM FRM_DOCUMENTS_VL WHERE ARCHIVED_FLAG = 'N' AND END_DATE <= SYSDATE) LOOP
157 DELETE_MENUFORM_ENTRIES(DOCUMENT_ROW.DOCUMENT_ID);
158 END LOOP;
159 -- End Loop
160
161 DELETE FROM FRM_DOCUMENTS_VL WHERE ARCHIVED_FLAG = 'N' AND END_DATE <= SYSDATE ;
162
163
164 -- STEP 4 --
165 DELETE FROM FRM_DOC_PUB_OPTIONS WHERE ARCHIVED_FLAG = 'N' AND END_DATE <= SYSDATE;
166
167
168 -- STEP 5 --
169 DELETE FROM FRM_DOC_REVIEWERS WHERE ARCHIVED_FLAG = 'N' AND END_DATE <= SYSDATE;
170
171
172 -- STEP 6 --
173 DELETE FROM FRM_DOCUMENT_DETAILS WHERE ARCHIVED_FLAG = 'N' AND END_DATE <= SYSDATE;
174
175
176 -- STEP 7 --
177 DELETE FROM FRM_DIRECTORY_VL WHERE ARCHIVED_FLAG = 'N' AND END_DATE <= SYSDATE;
178
179 EXCEPTION
180 WHEN OTHERS THEN
181 emesg := SQLERRM;
182 ROLLBACK;
183
184 COMMIT;
185
186 END DELETE_MARKED_ENTRIES;
187
188 --------------------------------------------------------------------------------
189 -- PROCEDURE: DELETE_DOCUMENT_TIMEFRAME --
190 -- --
191 -- DESCRIPTION: Deletes report of a timeframe in the document. This --
192 -- procedure is used to replace a report for a timeframe--
193 -- in the document when publishing a report with replace--
194 -- option. It retains the form functions created, menu --
195 -- entries as they will be used to access the newly --
196 -- created document. --
197 -- --
198 -- --
199 -- MODIFICATION HISTORY --
200 -- Date Username Description --
201 -- 26-NOV-2010 RGURUSAM CREATED --
202 --------------------------------------------------------------------------------
203 PROCEDURE DELETE_DOCUMENT_TIMEFRAME(P_DOCUMENT_ID IN NUMBER, P_TIMEFRAME IN VARCHAR2)
204 IS
205 EMESG VARCHAR2(250);
206 BEGIN
207
208 -- Delete document, timeframe entry from Repository Lobs
209 DELETE FROM FRM_REPOSITORY_LOBS WHERE FILE_ID IN (SELECT FILE_ID FROM FRM_DOCUMENT_DETAILS WHERE DOCUMENT_ID = P_DOCUMENT_ID AND TIMEFRAME = P_TIMEFRAME);
210
211 -- Delete document, timeframe publishing options.
212 DELETE FROM FRM_DOC_PUB_OPTIONS WHERE DOCUMENT_ID = P_DOCUMENT_ID AND TIMEFRAME = P_TIMEFRAME;
213
214 -- Delete document, timeframe entry reviewers details.
215 DELETE FROM FRM_DOC_REVIEWERS WHERE DOCUMENT_ID = P_DOCUMENT_ID AND TIMEFRAME = P_TIMEFRAME;
216
217 -- Dlete document, timeframe entry details.
218 DELETE FROM FRM_DOCUMENT_DETAILS WHERE DOCUMENT_ID = P_DOCUMENT_ID AND TIMEFRAME = P_TIMEFRAME;
219
220 EXCEPTION
221 WHEN OTHERS THEN
222 EMESG := SQLERRM;
223 ROLLBACK;
224
225 END DELETE_DOCUMENT_TIMEFRAME;
226
227 END FRM_REPOSITORY_MAINTENANCE;