[Home] [Help]
PACKAGE BODY: APPS.HZ_IMP_PURGE_PKG
Source
1 PACKAGE BODY HZ_IMP_PURGE_PKG AS
2 /*$Header: ARHLPRGB.pls 115.6 2004/05/04 00:56:38 wawong noship $ */
3 PROCEDURE Purge_Batch( errbuf OUT NOCOPY VARCHAR2,
4 retcode OUT NOCOPY VARCHAR2,
5 p_batch_id IN VARCHAR2
6 ) IS
7 i NUMBER;
8 l_error_message VARCHAR2(2000);
9 l_status HZ_IMP_BATCH_SUMMARY.MAIN_CONC_STATUS%TYPE;
10 BEGIN
11
12 /* Check for 8i database */
13 BEGIN
14 SELECT REPLACE(substr(version, 1, instr(version, '.', 1, 3)),'.')
15 INTO i
16 FROM v$instance;
17
18 IF i >= 920 then
19 NULL;
20 ELSE
21 RAISE FND_API.G_EXC_ERROR;
22 END IF;
23
24 EXCEPTION
25 WHEN FND_API.G_EXC_ERROR THEN
26 ROLLBACK WORK;
27 FND_MESSAGE.SET_NAME( 'AR', 'HZ_IMP_DB_VER_CHECK' );
28 FND_MSG_PUB.ADD;
29 FND_MSG_PUB.Reset;
30 FOR i IN 1..FND_MSG_PUB.Count_Msg LOOP
31 l_error_message := FND_MSG_PUB.Get( p_msg_index => i,
32 p_encoded => FND_API.G_FALSE);
33 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_error_message );
34 FND_FILE.PUT_LINE(FND_FILE.log, l_error_message );
35 END LOOP;
36 retcode := 2;
37 return;
38 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
39 ROLLBACK WORK;
40 FND_FILE.put_line(fnd_file.log,'Unexpected error occured ');
41 FND_FILE.put_line(fnd_file.log,sqlerrm);
42 retcode := 2;
43 return;
44 WHEN OTHERS THEN
45 ROLLBACK WORK;
46 FND_FILE.put_line(fnd_file.log,sqlerrm);
47 Retcode := 2;
48 return;
49 END;
50
51 /* Batch is Processing */
52 BEGIN
53 SELECT main_conc_status INTO l_status
54 FROM hz_imp_batch_summary
55 WHERE batch_id = p_batch_id;
56
57 IF l_status = 'PROCESSING' THEN
58 RAISE FND_API.G_EXC_ERROR;
59 END IF;
60 EXCEPTION
61 WHEN FND_API.G_EXC_ERROR THEN
62 ROLLBACK WORK;
63 FND_FILE.put_line(fnd_file.log,'Error : You cannot purge a batch when a batch is being processed.');
64 retcode := 2;
65 return;
66 WHEN OTHERS THEN
67 ROLLBACK WORK;
68 FND_FILE.put_line(fnd_file.log,sqlerrm);
69 retcode := 2;
70 return;
71 END;
72 FND_FILE.put_line(fnd_file.log,' Purge Starts ... ');
73
74 -- DQM Tables
75 FND_FILE.put_line(fnd_file.log,' Purging DQM Tables ... ');
76 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_INT_DEDUP_RESULTS (+) ');
77
78 DELETE HZ_IMP_INT_DEDUP_RESULTS WHERE batch_id = p_batch_id;
79 COMMIT;
80
81 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_INT_DEDUP_RESULTS (-) ');
82 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_DUP_PARTIES (+) ');
83
84 DELETE HZ_IMP_DUP_PARTIES WHERE batch_id = p_batch_id;
85 COMMIT;
86
87 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_DUP_PARTIES (-) ');
88 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_DUP_DETAILS (+) ');
89
90 DELETE HZ_IMP_DUP_DETAILS WHERE batch_id = p_batch_id;
91 COMMIT;
92
93 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_DUP_DETAILS (-) ');
94 FND_FILE.put_line(fnd_file.log,' Purged DQM Tables ... ');
95
96 -- Interface Tables
97 FND_FILE.put_line(fnd_file.log,' Purging Interface Tables ... ');
98 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_PARTIES_INT(+) ');
99
100 DELETE HZ_IMP_PARTIES_INT WHERE batch_id = p_batch_id;
101 COMMIT;
102
103 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_PARTIES_INT(-) ');
104 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_ADDRESSES_INT(+) ');
105
106 DELETE HZ_IMP_ADDRESSES_INT WHERE batch_id = p_batch_id;
107 COMMIT;
108
109 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_ADDRESSES_INT(-) ');
110 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_ADDRESSUSES_INT(+) ');
111
112 DELETE HZ_IMP_ADDRESSUSES_INT WHERE batch_id = p_batch_id;
113 COMMIT;
114
115 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_ADDRESSUSES_INT(-) ');
116 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_CONTACTPTS_INT(+) ');
117
118 DELETE HZ_IMP_CONTACTPTS_INT WHERE batch_id = p_batch_id;
119 COMMIT;
120
121 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_CONTACTPTS_INT(-) ');
122 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_RELSHIPS_INT(+) ');
123
124 DELETE HZ_IMP_RELSHIPS_INT WHERE batch_id = p_batch_id;
125 COMMIT;
126
127 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_RELSHIPS_INT(-) ');
128 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_CONTACTS_INT(+) ');
129
130 DELETE HZ_IMP_CONTACTS_INT WHERE batch_id = p_batch_id;
131 COMMIT;
132
133 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_CONTACTS_INT(-) ');
134 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_CONTACTROLES_INT(+) ');
135
136 DELETE HZ_IMP_CONTACTROLES_INT WHERE batch_id = p_batch_id;
137 COMMIT;
138
139 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_CONTACTROLES_INT(-) ');
140 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_CREDITRTNGS_INT(+) ');
141
142 DELETE HZ_IMP_CREDITRTNGS_INT WHERE batch_id = p_batch_id;
143 COMMIT;
144
145 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_CREDITRTNGS_INT(-) ');
146 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_CLASSIFICS_INT(+) ');
147
148 DELETE HZ_IMP_CLASSIFICS_INT WHERE batch_id = p_batch_id;
149 COMMIT;
150
151 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_CLASSIFICS_INT(-) ');
152 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_FINNUMBERS_INT(+) ');
153
154 DELETE HZ_IMP_FINNUMBERS_INT WHERE batch_id = p_batch_id;
155 COMMIT;
156
157 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_FINNUMBERS_INT(-) ');
158 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_FINREPORTS_INT(+) ');
159
160 DELETE HZ_IMP_FINREPORTS_INT WHERE batch_id = p_batch_id;
161 COMMIT;
162
163 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_FINREPORTS_INT(-) ');
164 FND_FILE.put_line(fnd_file.log,' Purged Interface Tables ... ');
165
166
167
168 --Staging Tables
169 FND_FILE.put_line(fnd_file.log,' Purging Staging Tables ... ');
170 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_PARTIES_SG (+)');
171
172 DELETE HZ_IMP_PARTIES_SG WHERE batch_id = p_batch_id;
173 COMMIT;
174
175 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_PARTIES_SG (-)');
176 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_ADDRESSES_SG (+)');
177
178 DELETE HZ_IMP_ADDRESSES_SG WHERE batch_id = p_batch_id;
179 COMMIT;
180
181 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_ADDRESSES_SG (-)');
182 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_ADDRESSUSES_SG (+)');
183
184 DELETE HZ_IMP_ADDRESSUSES_SG WHERE batch_id = p_batch_id;
185 COMMIT;
186
187 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_ADDRESSUSES_SG (-)');
188 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_CONTACTPTS_SG (+)');
189
190 DELETE HZ_IMP_CONTACTPTS_SG WHERE batch_id = p_batch_id;
191 COMMIT;
192
193 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_CONTACTPTS_SG (-)');
194 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_RELSHIPS_SG (+)');
195
196 DELETE HZ_IMP_RELSHIPS_SG WHERE batch_id = p_batch_id;
197 COMMIT;
198
199 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_RELSHIPS_SG (-)');
200 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_CONTACTS_SG (+)');
201
202 DELETE HZ_IMP_CONTACTS_SG WHERE batch_id = p_batch_id;
203 COMMIT;
204
205 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_CONTACTS_SG (-)');
206 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_CONTACTROLES_SG (+)');
207
208 DELETE HZ_IMP_CONTACTROLES_SG WHERE batch_id = p_batch_id;
209 COMMIT;
210
211 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_CONTACTROLES_SG (-)');
212 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_CREDITRTNGS_SG (+)');
213
214 DELETE HZ_IMP_CREDITRTNGS_SG WHERE batch_id = p_batch_id;
215 COMMIT;
216
217 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_CREDITRTNGS_SG (-)');
218 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_CLASSIFICS_SG (+)');
219
220 DELETE HZ_IMP_CLASSIFICS_SG WHERE batch_id = p_batch_id;
221 COMMIT;
222
223 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_CLASSIFICS_SG (-)');
224 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_FINNUMBERS_SG (+)');
225
226 DELETE HZ_IMP_FINNUMBERS_SG WHERE batch_id = p_batch_id;
227 COMMIT;
228
229 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_FINNUMBERS_SG (-)');
230 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_FINREPORTS_SG (+)');
231
232 DELETE HZ_IMP_FINREPORTS_SG WHERE batch_id = p_batch_id;
233 COMMIT;
234
235 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_FINREPORTS_SG (-)');
236 FND_FILE.put_line(fnd_file.log,' Purged Staging Tables ... ');
237
238 -- Error Tables
239 FND_FILE.put_line(fnd_file.log,' Purging Error Tables ... ');
240 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_ERRORS (+)');
241
242 DELETE HZ_IMP_ERRORS WHERE batch_id = p_batch_id;
243 COMMIT;
244
245 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_ERRORS (-)');
246 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_TMP_ERRORS (+)');
247
248 DELETE HZ_IMP_TMP_ERRORS WHERE batch_id = p_batch_id;
249 COMMIT;
250
251 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_TMP_ERRORS (-)');
252 FND_FILE.put_line(fnd_file.log,' Purged Error Tables ... ');
253
254 -- Work Units Table
255 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_WORK_UNITS (+)');
256 DELETE HZ_IMP_WORK_UNITS WHERE batch_id = p_batch_id;
257 COMMIT;
258
259 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_WORK_UNITS (-)');
260
261 -- Bug No 3310475
262 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_OSR_CHANGE (+)');
263 DELETE HZ_IMP_OSR_CHANGE WHERE batch_id = p_batch_id;
264 COMMIT;
265 FND_FILE.put_line(fnd_file.log,' Purge HZ_IMP_OSR_CHANGE (-)');
266 FND_FILE.put_line(fnd_file.log,' Purge Ends ... ');
267
268 FND_FILE.put_line(fnd_file.log,' Update hz_imp_batch_summary table (+)');
269 -- Update hz_imp_batch_summary table.
270 UPDATE HZ_IMP_BATCH_SUMMARY
271 SET BATCH_STATUS = 'PURGED',
272 PURGE_DATE = SYSDATE,
273 PURGED_BY_USER_ID = HZ_UTILITY_V2PUB.user_id
274 WHERE batch_id = p_batch_id;
275 COMMIT;
276
277 FND_FILE.put_line(fnd_file.log,' Update hz_imp_batch_summary table (-)');
278 EXCEPTION
279 WHEN OTHERS THEN
280 ROLLBACK WORK;
281 FND_FILE.put_line(fnd_file.log,sqlerrm);
282 retcode := 2;
283 return;
284 END Purge_Batch ;
285
286 END HZ_IMP_PURGE_PKG;