[Home] [Help]
PACKAGE BODY: APPS.IGS_DA_PURGE_RQST_PKG
Source
1 PACKAGE BODY igs_da_purge_rqst_pkg AS
2 /* $Header: IGSDA11B.pls 120.1 2006/01/18 23:04:33 swaghmar noship $ */
3 /*******************************************************************************
4 Change History:
5 Who When What
6 DDEY 31-March-2003
7 1. Package Created for deleting the request by an Administrator and the
8 Student.
9 2. The Job is written to purge the Data, in the base tables based on the
10 parameters passed.
11 *******************************************************************************/
12 PROCEDURE delete_row (
13 p_batch_id IN NUMBER,
14 x_status OUT NOCOPY VARCHAR2
15 ) IS
16 /*****************************************************************************
17 Created By: Deepankar Dey
18 Date Created By: 12-11-2001
19 Purpose:
20 This procedure is to be called from the SS screens, to delete the records
21 corresponding to the
22 Known limitations,enhancements,remarks:
23 Change History
24 Who When What
25 *****************************************************************************/
26 CURSOR cur_req_stdnts IS
27 SELECT ROWID
28 FROM igs_da_req_stdnts
29 WHERE batch_id = p_batch_id ;
30 CURSOR cur_req_ftrs IS
31 SELECT ROWID
32 FROM igs_da_req_ftrs
33 WHERE batch_id = p_batch_id ;
34 CURSOR cur_req_wifs IS
35 SELECT ROWID
36 FROM igs_da_req_wif
37 WHERE batch_id = p_batch_id ;
38 CURSOR cur_req_rqst IS
39 SELECT ROWID
40 FROM igs_da_rqst
41 WHERE batch_id = p_batch_id ;
42 --
43 l_return_status VARCHAR2(1);
44 l_msg_data VARCHAR2(2000);
45 l_msg_count NUMBER;
46 --
47 BEGIN
48 --
49 IF (p_batch_id IS NULL) THEN
50 FND_MESSAGE.Set_Name ('IGS', 'IGS_DA_BTCH_ID_NOT_FND');
51 FND_MESSAGE.Set_Token ('BATCH_ID', p_batch_id);
52 FND_MSG_PUB.ADD;
53 RAISE FND_API.G_EXC_ERROR;
54 ELSE
55 --
56 FOR l_req_stdnts IN cur_req_stdnts LOOP
57 igs_da_req_stdnts_pkg.delete_row (
58 x_rowid => l_req_stdnts.ROWID
59 );
60 END LOOP;
61 --
62 FOR l_req_ftrs IN cur_req_ftrs LOOP
63 igs_da_req_ftrs_pkg.delete_row (
64 x_rowid => l_req_ftrs.ROWID,
65 x_return_status => l_return_status,
66 x_msg_data => l_msg_data,
67 x_msg_count => l_msg_count
68 );
69 END LOOP;
70 --
71 FOR l_req_wifs IN cur_req_wifs LOOP
72 igs_da_req_wif_pkg.delete_row (
73 x_rowid => l_req_wifs.ROWID,
74 x_return_status => l_return_status,
75 x_msg_data => l_msg_data,
76 x_msg_count => l_msg_count
77 );
78 END LOOP;
79 --
80 FOR l_req_rqst IN cur_req_rqst LOOP
81 igs_da_rqst_pkg.delete_row(
82 x_rowid => l_req_rqst.ROWID,
83 x_return_status => l_return_status,
84 x_msg_data => l_msg_data,
85 x_msg_count => l_msg_count
86 );
87 END LOOP;
88 --
89 x_status := 'S';
90 --
91 END IF ;
92 --
93 EXCEPTION
94 WHEN FND_API.G_EXC_ERROR THEN
95 IF (cur_req_stdnts%ISOPEN) THEN
96 CLOSE cur_req_stdnts;
97 END IF;
98 IF (cur_req_ftrs%ISOPEN) THEN
99 CLOSE cur_req_ftrs;
100 END IF;
101 IF (cur_req_wifs%ISOPEN) THEN
102 CLOSE cur_req_wifs;
103 END IF;
104 IF (cur_req_rqst%ISOPEN) THEN
105 CLOSE cur_req_rqst;
106 END IF;
107 x_status := FND_API.G_RET_STS_ERROR;
108 WHEN OTHERS THEN
109 x_status := FND_API.G_RET_STS_UNEXP_ERROR;
110 IF (cur_req_stdnts%ISOPEN) THEN
111 CLOSE cur_req_stdnts;
112 END IF;
113 IF (cur_req_ftrs%ISOPEN) THEN
114 CLOSE cur_req_ftrs;
115 END IF;
116 IF (cur_req_wifs%ISOPEN) THEN
117 CLOSE cur_req_wifs;
118 END IF;
119 IF (cur_req_rqst%ISOPEN) THEN
120 CLOSE cur_req_rqst;
121 END IF;
122 END delete_row;
123 --
124 -- Purge Requests Job
125 --
126 PROCEDURE purge_request (
127 errbuf OUT NOCOPY VARCHAR2,
128 retcode OUT NOCOPY NUMBER,
129 p_c_start_date IN VARCHAR2,
130 p_c_end_date IN VARCHAR2,
131 p_c_request_number IN NUMBER,
132 p_requestor_id IN NUMBER,
133 p_responsibility IN VARCHAR2,
134 p_request_status IN VARCHAR2,
135 p_request_type IN VARCHAR2
136 ) IS
137 /*****************************************************************************
138 Created By: Deepankar Dey
139 Date Created By: 12-11-2001
140 Purpose:
141 This procedure is used to purge the degree audit requests based on the
142 parameters passed from the Degree Audit - Purge Requests concurrent job.
143 Known limitations,enhancements,remarks:
144 Change History
145 Who When What
146 kdande 15-May-2003 Changed the cursor cur_request to have a proper join
147 for Bug# 2955477
148 swaghmar 16-Jan-2006 Bug# 4951054 - Added check for disabling UI's
149 *****************************************************************************/
150 CURSOR cur_request IS
151 SELECT distinct dar.ROWID, dar.batch_id, dar.request_type_id,
152 flv.meaning request_status_meaning,
153 pbv1.full_name requestor_full_name,
154 dar.creation_date,
155 dar.requestor_id
156 FROM igs_da_rqst dar,
157 igs_pe_person_base_v pbv1,
158 igs_da_cnfg dac,
159 igs_da_cnfg_req_typ dacr,
160 fnd_lookup_values_vl flv
161 WHERE dacr.request_type_id = dar.request_type_id
162 AND dacr.purgable_ind = 'Y'
163 AND flv.lookup_type = 'IGS_DA_RQST_STATUS'
164 AND flv.lookup_code = dar.request_status
165 AND dar.batch_id = NVL(p_c_request_number, dar.batch_id)
166 AND dar.requestor_id = NVL(p_requestor_id, dar.requestor_id)
167 AND dac.responsibility_name = NVL(p_responsibility,dac.responsibility_name)
168 AND dar.request_status = NVL(p_request_status,dar.request_status)
169 AND dacr.request_type =NVL(p_request_type,dacr.request_type)
170 AND ((p_c_start_date IS NULL)OR ( p_c_start_date IS NOT NULL AND dar.creation_date >= fnd_date.canonical_to_date (p_c_start_date)))
171 AND ((p_c_end_date IS NULL)OR ( p_c_end_date IS NOT NULL AND dar.creation_date <= fnd_date.canonical_to_date (p_c_end_date)))
172 AND dar.requestor_id = pbv1.person_id
173 AND dac.request_type_id(+) = dar.request_type_id ;
174
175 --
176 CURSOR cur_request_type (cp_request_type_id
177 igs_da_cnfg_req_typ.request_type_id%TYPE) IS
178 SELECT dacr.request_name,
179 dacr.request_type_meaning,
180 dacr.request_mode_meaning
181 FROM igs_da_cnfg_req_typ_v dacr
182 WHERE dacr.request_type_id = cp_request_type_id;
183 --
184 CURSOR cur_req_stdnts (cp_batch_id igs_da_rqst.batch_id%TYPE) IS
185 SELECT ROWID
186 FROM igs_da_req_stdnts
187 WHERE batch_id = cp_batch_id ;
188 --
189 CURSOR cur_req_ftrs (cp_batch_id igs_da_rqst.batch_id%TYPE) IS
190 SELECT ROWID
191 FROM igs_da_req_ftrs
192 WHERE batch_id = cp_batch_id ;
193 --
194 CURSOR cur_req_wifs (cp_batch_id igs_da_rqst.batch_id%TYPE) IS
195 SELECT ROWID
196 FROM igs_da_req_wif
197 WHERE batch_id = cp_batch_id ;
198 --
199 l_return_status VARCHAR2(2000);
200 l_msg_data VARCHAR2(2000);
201 l_msg_count NUMBER;
202 p_start_date DATE;
203 p_end_date DATE;
204 l_count NUMBER := 0;
205 l_cur_request_type cur_request_type%ROWTYPE;
206 --
207 BEGIN
208 --
209
210 retcode := 0;
211 IGS_GE_GEN_003.SET_ORG_ID(); -- swaghmar, bug# 4951054
212
213 SAVEPOINT s_before_delete;
214 --
215 -- Putting Messages in the Log File
216 --
217 FND_MESSAGE.Set_Name('IGS','IGS_DA_JOB');
218 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get);
219 --
220 FND_MESSAGE.Set_Name('IGS','IGS_DA_START_DT');
221 FND_MESSAGE.SET_TOKEN('START',p_c_start_date);
222 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get);
223 --
224 FND_MESSAGE.Set_Name('IGS','IGS_DA_END_DT');
225 FND_MESSAGE.SET_TOKEN('END',p_c_end_date);
226 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get);
227 --
228 FND_MESSAGE.Set_Name('IGS','IGS_DA_REQ_NUM');
229 FND_MESSAGE.SET_TOKEN('BATCH_ID',p_c_request_number);
230 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get);
231 --
232 FND_MESSAGE.Set_Name('IGS','IGS_DA_REQUESTER');
233 FND_MESSAGE.SET_TOKEN('REQ',p_requestor_id);
234 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get);
235 --
236 FND_MESSAGE.Set_Name('IGS','IGS_DA_RESP');
237 FND_MESSAGE.SET_TOKEN('RESP',p_responsibility);
238 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get);
239 --
240 FND_MESSAGE.Set_Name('IGS','IGS_DA_REQ_STATUS');
241 FND_MESSAGE.SET_TOKEN('REQ',p_request_status);
242 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get);
243 --
244 FND_MESSAGE.Set_Name('IGS','IGS_DA_REQ_TYPE');
245 FND_MESSAGE.SET_TOKEN('REQ',p_request_type);
246 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get);
247 --
248 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
249 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
250 --
251 IF (p_c_start_date IS NULL AND
252 p_c_end_date IS NULL AND
253 p_c_request_number IS NULL AND
254 p_requestor_id IS NULL AND
255 p_responsibility IS NULL AND
256 p_request_status IS NULL AND
257 p_request_type IS NULL) THEN
258 RAISE FND_API.G_EXC_ERROR;
259 ELSE
260 --
261 FOR l_cur_request IN cur_request LOOP
262 FOR l_cur_req_stdnts IN cur_req_stdnts (l_cur_request.batch_id) LOOP
263 --
264 -- Deleting records from Request Students Interface table
265 --
266 igs_da_req_stdnts_pkg.delete_row (
267 x_rowid => l_cur_req_stdnts.ROWID
268 );
269 END LOOP;
270 --
271 FOR l_cur_req_ftrs IN cur_req_ftrs(l_cur_request.batch_id) LOOP
272 --
273 -- Deleting records from Request Feature Interface table
274 --
275 igs_da_req_ftrs_pkg.delete_row (
276 x_rowid => l_cur_req_ftrs.ROWID,
277 x_return_status => l_return_status,
278 x_msg_data => l_msg_data,
279 x_msg_count => l_msg_count
280 );
281 END LOOP;
282 --
283 FOR l_cur_req_wifs IN cur_req_wifs(l_cur_request.batch_id) LOOP
284 --
285 -- Deleting records from WIF Interface table
286 --
287 igs_da_req_wif_pkg.delete_row (
288 x_rowid => l_cur_req_wifs.ROWID,
289 x_return_status => l_return_status,
290 x_msg_data => l_msg_data,
291 x_msg_count => l_msg_count
292 );
293 --
294 END LOOP;
295 --
296 -- Putting Messages in the Log File
297 --
298 OPEN cur_request_type(l_cur_request.request_type_id);
299 FETCH cur_request_type INTO l_cur_request_type;
300 CLOSE cur_request_type;
301 --
302 FND_MESSAGE.Set_Name('IGS','IGS_DA_BATCH_ID');
303 FND_MESSAGE.SET_TOKEN('BATCH_ID',l_cur_request.batch_id);
304 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get);
305 --
306 FND_MESSAGE.Set_Name('IGS','IGS_DA_REP_NAME');
307 FND_MESSAGE.SET_TOKEN('REP',l_cur_request_type.request_name);
308 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get);
309 --
310 FND_MESSAGE.Set_Name('IGS','IGS_DA_REQ_TYPE');
311 FND_MESSAGE.SET_TOKEN('REQ',l_cur_request_type.request_type_meaning);
312 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get);
313 --
314 FND_MESSAGE.Set_Name('IGS','IGS_DA_SEL_MODE');
315 FND_MESSAGE.SET_TOKEN('SEL',l_cur_request_type.request_mode_meaning);
316 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get);
317 --
318 FND_MESSAGE.Set_Name('IGS','IGS_DA_REQ_STATUS');
319 FND_MESSAGE.SET_TOKEN('REQ',l_cur_request.request_status_meaning);
320 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get);
321 --
322 FND_MESSAGE.Set_Name('IGS','IGS_DA_REQUESTER');
323 FND_MESSAGE.SET_TOKEN('REQ',l_cur_request.requestor_full_name);
324 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get);
325 --
326 FND_MESSAGE.Set_Name('IGS','IGS_DA_REQ_DT');
327 FND_MESSAGE.SET_TOKEN('REQ',l_cur_request.creation_date);
328 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get);
329 --
330 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
331 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
332 --
333 -- Incrementing the Processing Record Counter
334 --
335 l_count := l_count + 1;
336 --
337 -- Deleting records from Requests Interface table
338 igs_da_rqst_pkg.delete_row(
339 x_rowid => l_cur_request.rowid,
340 x_return_status => l_return_status,
341 x_msg_data => l_msg_data,
342 x_msg_count => l_msg_count
343 );
344 END LOOP;
345 END IF;
346 --
347 FND_MESSAGE.Set_Name ('IGS', 'IGS_DA_TOTAL_REQ');
348 FND_MESSAGE.SET_TOKEN ('COUNT', l_count);
349 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.Get);
350 --
351 EXCEPTION
352 WHEN FND_API.G_EXC_ERROR THEN
353 errbuf := FND_MESSAGE.GET_STRING('IGS','IGS_DA_NO_PARAM');
354 retcode := 2;
355 ROLLBACK TO s_before_delete;
356 WHEN OTHERS THEN
357 ROLLBACK TO s_before_delete;
358 errbuf := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
359 retcode := 2;
360 FND_MESSAGE.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
361 FND_MESSAGE.SET_TOKEN ('NAME', 'igs_da_purge_rqst_pkg.purge_request(): '
362 || SUBSTR (SQLERRM,1,80));
363 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.Get);
364 IGS_GE_MSG_STACK.ADD;
365 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
366 END purge_request;
367 END igs_da_purge_rqst_pkg;