[Home] [Help]
PACKAGE BODY: APPS.GMO_PURGE
Source
1 PACKAGE BODY GMO_PURGE AS
2 /* $Header: GMOVPRGB.pls 120.0 2005/09/21 06:09 bchopra noship $ */
3
4 /* Procedure to clean GMO Device Read tables */
5 PROCEDURE PURGE_DEVICE_DATA(P_END_DATE IN DATE,
6 P_TRUNCATE_TABLE IN VARCHAR2,
7 P_COMMIT IN VARCHAR2)
8 IS
9 INVALID_DATE_EXCEPTION EXCEPTION;
10 INVALID_PARAM_EXCEPTION EXCEPTION;
11 BEGIN
12
13 IF (P_END_DATE > sysdate) THEN
14 fnd_message.set_name('GMO', 'GMO_PURGE_FUTURE_END_DATE');
15 FND_MSG_PUB.ADD;
16 RAISE INVALID_DATE_EXCEPTION;
17 END IF;
18
19 /* Validate if end data is null and request is not for truncate */
20 IF(P_END_DATE IS NULL AND P_TRUNCATE_TABLE= GMO_CONSTANTS_GRP.NO) THEN
21 fnd_message.set_name('GMO', 'GMO_INVALID_INPUT_PARAM');
22 FND_MSG_PUB.ADD;
23 RAISE INVALID_PARAM_EXCEPTION;
24 END IF ;
25
26 IF(P_TRUNCATE_TABLE = GMO_CONSTANTS_GRP.YES) THEN
27 execute immediate 'truncate table GMO_DEVICE_RESPONSES_T';
28 execute immediate 'truncate table GMO_DEVICE_REQUESTS_T';
29 RETURN;
30 END IF;
31
32
33 DELETE GMO_DEVICE_RESPONSES_T
34 WHERE REQUEST_ID IN
35 (SELECT REQUEST_ID
36 FROM GMO_DEVICE_REQUESTS_T
37 WHERE CREATION_DATE <= P_END_DATE );
38
39 DELETE FROM GMO_DEVICE_REQUESTS_T WHERE CREATION_DATE <= P_END_DATE;
40
41 IF(P_COMMIT = GMO_CONSTANTS_GRP.YES) THEN
42 COMMIT;
43 END IF;
44 EXCEPTION
45 WHEN OTHERS THEN
46 FND_MESSAGE.SET_NAME('GMO','GMO_PURGE_DEVICE_DB_ERR');
47 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
48 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
49 FND_MSG_PUB.ADD;
50 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
51 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_purge.purge_device_data', FALSE);
52 end if;
53 RAISE;
54
55 END PURGE_DEVICE_DATA;
56
57
58 /* Procedure to clean GMO Instruction tables */
59 PROCEDURE PURGE_INSTRUCTION_DATA(P_END_DATE IN DATE,
60 P_TRUNCATE_TABLE IN VARCHAR2,
61 P_COMMIT IN VARCHAR2)
62 IS
63 INVALID_DATE_EXCEPTION EXCEPTION;
64 INVALID_PARAM_EXCEPTION EXCEPTION;
65 BEGIN
66
67 IF (P_END_DATE > sysdate) THEN
68 fnd_message.set_name('GMO', 'GMO_PURGE_FUTURE_END_DATE');
69 FND_MSG_PUB.ADD;
70 RAISE INVALID_DATE_EXCEPTION;
71 END IF;
72
73 /* Validate if end data is null and request is not for truncate */
74 IF(P_END_DATE IS NULL AND P_TRUNCATE_TABLE = GMO_CONSTANTS_GRP.NO) THEN
75 fnd_message.set_name('GMO', 'GMO_INVALID_INPUT_PARAM');
76 FND_MSG_PUB.ADD;
77 RAISE INVALID_PARAM_EXCEPTION;
78 END IF ;
79
80 IF(P_TRUNCATE_TABLE = GMO_CONSTANTS_GRP.YES) THEN
81 /* Truncate Definition temporary tables */
82 execute immediate 'truncate table GMO_INSTR_APPR_DEFN_T';
83 execute immediate 'truncate table GMO_INSTR_DEFN_T';
84 execute immediate 'truncate table GMO_INSTR_SET_DEFN_T';
85 /* Truncate Instance temporary tables */
86 execute immediate 'truncate table GMO_INSTR_TASK_INSTANCE_T';
87 execute immediate 'truncate table GMO_INSTR_EREC_INSTANCE_T';
88 execute immediate 'truncate table GMO_INSTR_INSTANCE_T';
89 execute immediate 'truncate table GMO_INSTR_SET_INSTANCE_T';
90 /* Truncate Attributes table in the end */
91 execute immediate 'truncate table GMO_INSTR_ATTRIBUTES_T';
92 RETURN;
93 END IF;
94
95 /* Delete Definition temporary tables */
96 delete GMO_INSTR_APPR_DEFN_T where LAST_UPDATE_DATE <= P_END_DATE;
97 delete GMO_INSTR_DEFN_T where LAST_UPDATE_DATE <= P_END_DATE;
98 delete GMO_INSTR_SET_DEFN_T where LAST_UPDATE_DATE <= P_END_DATE;
99
100 /* Truncate Instance temporary tables */
101 delete GMO_INSTR_TASK_INSTANCE_T where LAST_UPDATE_DATE <= P_END_DATE;
102 delete GMO_INSTR_EREC_INSTANCE_T where LAST_UPDATE_DATE <= P_END_DATE;
103 delete GMO_INSTR_INSTANCE_T where LAST_UPDATE_DATE <= P_END_DATE;
104 delete GMO_INSTR_SET_INSTANCE_T where LAST_UPDATE_DATE <= P_END_DATE;
105
106 /* Truncate Attributes table in the end */
107 delete GMO_INSTR_ATTRIBUTES_T where LAST_UPDATE_DATE <= P_END_DATE;
108
109 IF(P_COMMIT = GMO_CONSTANTS_GRP.YES) THEN
110 COMMIT;
111 END IF;
112
113 EXCEPTION
114 WHEN OTHERS THEN
115 FND_MESSAGE.SET_NAME('GMO','GMO_PURGE_INSTR_DB_ERR');
116 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
117 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
118 FND_MSG_PUB.ADD;
119 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
120 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_purge.purge_instruction_data', FALSE);
121 end if;
122 RAISE;
123 END PURGE_INSTRUCTION_DATA;
124
125 /* Procedure to clean GMO temporary tables */
126 PROCEDURE PURGE_ALL(P_END_DATE IN DATE,
127 P_TRUNCATE_TABLE IN VARCHAR2,
128 P_COMMIT IN VARCHAR2)
129 IS
130 INVALID_DATE_EXCEPTION EXCEPTION;
131 INVALID_PARAM_EXCEPTION EXCEPTION;
132 BEGIN
133
134 IF (P_END_DATE > sysdate) THEN
135 fnd_message.set_name('GMO', 'GMO_PURGE_FUTURE_END_DATE');
136 FND_MSG_PUB.ADD;
137 RAISE INVALID_DATE_EXCEPTION;
138 END IF;
139
140 /* Validate if end data is null and request is not for truncate */
141 IF(P_END_DATE IS NULL AND P_TRUNCATE_TABLE = GMO_CONSTANTS_GRP.NO) THEN
142 fnd_message.set_name('GMO', 'GMO_INVALID_INPUT_PARAM');
143 FND_MSG_PUB.ADD;
144 RAISE INVALID_PARAM_EXCEPTION;
145 END IF ;
146
147
148 PURGE_DEVICE_DATA(P_END_DATE => P_END_DATE,
149 P_TRUNCATE_TABLE => P_TRUNCATE_TABLE,
150 P_COMMIT => GMO_CONSTANTS_GRP.NO);
151
152
153 PURGE_INSTRUCTION_DATA(P_END_DATE => P_END_DATE,
154 P_TRUNCATE_TABLE => P_TRUNCATE_TABLE,
155 P_COMMIT => GMO_CONSTANTS_GRP.NO);
156
157 IF(P_COMMIT = GMO_CONSTANTS_GRP.YES) THEN
158 COMMIT;
159 END IF;
160
161 EXCEPTION
162 WHEN OTHERS THEN
163 FND_MESSAGE.SET_NAME('GMO','GMO_PURGE_ALL_DB_ERR');
164 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
165 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
166 FND_MSG_PUB.ADD;
167 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
168 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_purge.purge_all', FALSE);
169 end if;
170 RAISE;
171 END PURGE_ALL;
172
173 --this procedure is used to clean up temp data from Concurrent Program
174 PROCEDURE PURGE_ALL(ERRBUF OUT NOCOPY VARCHAR2,
175 RETCODE OUT NOCOPY VARCHAR2,
176 P_MODULE_NAME IN VARCHAR2 DEFAULT NULL,
177 P_AGE IN VARCHAR2 ,
178 P_TRUNCATE_TABLE IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.NO)
179 IS
180 l_end_date date;
181 l_err_msg VARCHAR2(4000);
182 BEGIN
183
184 fnd_file.put_line(fnd_file.output, fnd_message.get_string('GMO', 'GMO_PURGE_TOTAL_START') );
185
186 fnd_message.set_name('GMO', 'GMO_PURGE_PARAMETERS');
187 fnd_message.set_token( 'MODULE', P_MODULE_NAME);
188 fnd_message.set_token( 'AGE', P_AGE);
189 fnd_message.set_token( 'TRUNCATE_TABLE', P_TRUNCATE_TABLE);
190 fnd_file.put_line(fnd_file.output, fnd_message.get);
191
192 l_end_date := sysdate - to_number(p_age);
193
194 IF(P_MODULE_NAME is null) THEN
195 PURGE_ALL(P_END_DATE => l_end_date,
196 P_TRUNCATE_TABLE => P_TRUNCATE_TABLE,
197 P_COMMIT => GMO_CONSTANTS_GRP.YES);
198 ELSIF (P_MODULE_NAME = 'DEVICE') THEN
199 PURGE_DEVICE_DATA(P_END_DATE => l_end_date,
200 P_TRUNCATE_TABLE => P_TRUNCATE_TABLE,
201 P_COMMIT => GMO_CONSTANTS_GRP.YES);
202 ELSIF (P_MODULE_NAME='INSTRUCTION') THEN
203 PURGE_INSTRUCTION_DATA(P_END_DATE => l_end_date,
204 P_TRUNCATE_TABLE => P_TRUNCATE_TABLE,
205 P_COMMIT => GMO_CONSTANTS_GRP.YES);
206 END IF;
207
208 fnd_message.set_name('GMO', 'GMO_PURGE_TOTAL_END');
209 fnd_file.put_line(fnd_file.output, fnd_message.get);
210
211 RETCODE := '0';
212 ERRBUF := '';
213 exception
214 when others then
215 -- Retrieve error message into errbuf
216 l_err_msg := fnd_message.get;
217 if (l_err_msg is not null) then
218 errbuf := l_err_msg;
219 else
220 errbuf := sqlerrm;
221 end if;
222
223 -- Return 2 for error.
224 retcode := '2';
225
226 END PURGE_ALL;
227
228 END GMO_PURGE;