[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_PURGE_DEL_RECORD
Source
1 PACKAGE BODY igs_ps_purge_del_record AS
2 /* $Header: IGSPS88B.pls 120.1 2005/06/29 05:12:28 appldev ship $ */
3
4
5 PROCEDURE purge_igs_ps_ofr_opt_all AS
6 /***********************************************************************************************
7
8 Created By : Shtatiko
9 Date Created By: 19-FEB-2003
10 Purpose : To purge logically deleted records from the table IGS_PS_OFR_OPT_ALL.
11 If any child record exists for a logically deleted record then that
12 record will be updated such that it becomes active else it will be deleted.
13
14 Known limitations,enhancements,remarks:
15 Change History
16 Who When What
17 ********************************************************************************************** */
18 CURSOR c_del_recs IS
19 SELECT rowid, a.*
20 FROM igs_ps_ofr_opt_all a
21 WHERE delete_flag = 'Y';
22
23 rec_del_recs c_del_recs%ROWTYPE;
24
25 l_b_purged BOOLEAN ;
26 l_c_message VARCHAR2 (4000);
27 l_n_rec_count NUMBER(10);
28
29 BEGIN
30 -- Create a Savepoint for Rollback
31 SAVEPOINT igs_ps_ofr_opt_all ;
32
33 fnd_file.put_line ( fnd_file.LOG, ' ' );
34 fnd_message.set_name ( 'IGS', 'IGS_PS_OFR_OPT_PURGE' );
35 fnd_file.put_line ( fnd_file.LOG, fnd_message.get );
36
37 l_n_rec_count := 0;
38 FOR rec_del_recs IN c_del_recs LOOP
39 l_b_purged := TRUE;
40 l_n_rec_count := l_n_rec_count + 1;
41 BEGIN
42 -- Call before_dml of the TBH to know whether the record can physically be deleted or not.
43 igs_ps_ofr_opt_pkg.before_dml (
44 p_action => 'VALIDATE_DELETE' ,
45 x_rowid => rec_del_recs.rowid ,
46 x_course_cd => rec_del_recs.course_cd ,
47 x_version_number => rec_del_recs.version_number ,
48 x_cal_type => rec_del_recs.cal_type ,
49 x_location_cd => rec_del_recs.location_cd ,
50 x_attendance_mode => rec_del_recs.attendance_mode ,
51 x_attendance_type => rec_del_recs.attendance_type ,
52 x_coo_id => rec_del_recs.coo_id ,
53 x_forced_location_ind => rec_del_recs.forced_location_ind ,
54 x_forced_att_mode_ind => rec_del_recs.forced_att_mode_ind ,
55 x_forced_att_type_ind => rec_del_recs.forced_att_type_ind ,
56 x_time_limitation => rec_del_recs.time_limitation ,
57 x_enr_officer_person_id => rec_del_recs.enr_officer_person_id ,
58 x_attribute_category => rec_del_recs.attribute_category ,
59 x_attribute1 => rec_del_recs.attribute1 ,
60 x_attribute2 => rec_del_recs.attribute2 ,
61 x_attribute3 => rec_del_recs.attribute3 ,
62 x_attribute4 => rec_del_recs.attribute4 ,
63 x_attribute5 => rec_del_recs.attribute5 ,
64 x_attribute6 => rec_del_recs.attribute6 ,
65 x_attribute7 => rec_del_recs.attribute7 ,
66 x_attribute8 => rec_del_recs.attribute8 ,
67 x_attribute9 => rec_del_recs.attribute9 ,
68 x_attribute10 => rec_del_recs.attribute10 ,
69 x_attribute11 => rec_del_recs.attribute11 ,
70 x_attribute12 => rec_del_recs.attribute12 ,
71 x_attribute13 => rec_del_recs.attribute13 ,
72 x_attribute14 => rec_del_recs.attribute14 ,
73 x_attribute15 => rec_del_recs.attribute15 ,
74 x_attribute16 => rec_del_recs.attribute16 ,
75 x_attribute17 => rec_del_recs.attribute17 ,
76 x_attribute18 => rec_del_recs.attribute18 ,
77 x_attribute19 => rec_del_recs.attribute19 ,
78 x_attribute20 => rec_del_recs.attribute20 ,
79 x_creation_date => rec_del_recs.creation_date ,
80 x_created_by => rec_del_recs.created_by ,
81 x_last_update_date => rec_del_recs.last_update_date ,
82 x_last_updated_by => rec_del_recs.last_updated_by ,
83 x_last_update_login => rec_del_recs.last_update_login ,
84 x_org_id => rec_del_recs.org_id ,
85 x_program_length => rec_del_recs.program_length ,
86 x_program_length_measurement => rec_del_recs.program_length_measurement
87 );
88
89 EXCEPTION
90 WHEN OTHERS THEN
91 -- Exception means that there are childs for the record in question.
92 -- So deletion cannot be done and moreover, delete_flag is to be made 'N'
93 -- to make it active record.
94 l_b_purged := FALSE;
95
96 -- Ignore the message stacked by TBH so delete the message from the stack.
97 IGS_GE_MSG_STACK.DELETE_MSG ;
98
99 -- Update the delete_flag to 'N'
100 igs_ps_ofr_opt_pkg.update_row (
101 x_rowid => rec_del_recs.rowid ,
102 x_course_cd => rec_del_recs.course_cd ,
103 x_version_number => rec_del_recs.version_number ,
104 x_cal_type => rec_del_recs.cal_type ,
105 x_location_cd => rec_del_recs.location_cd ,
106 x_attendance_mode => rec_del_recs.attendance_mode ,
107 x_attendance_type => rec_del_recs.attendance_type ,
108 x_coo_id => rec_del_recs.coo_id ,
109 x_forced_location_ind => rec_del_recs.forced_location_ind ,
110 x_forced_att_mode_ind => rec_del_recs.forced_att_mode_ind ,
111 x_forced_att_type_ind => rec_del_recs.forced_att_type_ind ,
112 x_time_limitation => rec_del_recs.time_limitation ,
113 x_enr_officer_person_id => rec_del_recs.enr_officer_person_id ,
114 x_attribute_category => rec_del_recs.attribute_category ,
115 x_attribute1 => rec_del_recs.attribute1 ,
116 x_attribute2 => rec_del_recs.attribute2 ,
117 x_attribute3 => rec_del_recs.attribute3 ,
118 x_attribute4 => rec_del_recs.attribute4 ,
119 x_attribute5 => rec_del_recs.attribute5 ,
120 x_attribute6 => rec_del_recs.attribute6 ,
121 x_attribute7 => rec_del_recs.attribute7 ,
122 x_attribute8 => rec_del_recs.attribute8 ,
123 x_attribute9 => rec_del_recs.attribute9 ,
124 x_attribute10 => rec_del_recs.attribute10 ,
125 x_attribute11 => rec_del_recs.attribute11 ,
126 x_attribute12 => rec_del_recs.attribute12 ,
127 x_attribute13 => rec_del_recs.attribute13 ,
128 x_attribute14 => rec_del_recs.attribute14 ,
129 x_attribute15 => rec_del_recs.attribute15 ,
130 x_attribute16 => rec_del_recs.attribute16 ,
131 x_attribute17 => rec_del_recs.attribute17 ,
132 x_attribute18 => rec_del_recs.attribute18 ,
133 x_attribute19 => rec_del_recs.attribute19 ,
134 x_attribute20 => rec_del_recs.attribute20 ,
135 x_program_length => rec_del_recs.program_length ,
136 x_program_length_measurement => rec_del_recs.program_length_measurement,
137 x_delete_flag => 'N'
138 );
139 END;
140
141 IF l_b_purged THEN
142 igs_ps_ofr_opt_pkg.delete_row ( x_rowid => rec_del_recs.rowid );
143 END IF;
144
145 -- Log the details of record and status of purging.
146 fnd_file.put_line ( fnd_file.LOG, ' ');
147 fnd_file.put_line ( fnd_file.LOG, ' ');
148 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'PROGRAM_CD' )
149 || ' : ' || rec_del_recs.course_cd );
150 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'VERSION_NUMBER' )
151 || ' : ' || rec_del_recs.version_number );
152 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'CAL_TYPE' )
153 || ' : ' || rec_del_recs.cal_type );
154 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'LOCATION_CD' )
155 || ' : ' || rec_del_recs.location_cd );
156 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'ATTENDANCE_MODE' )
157 || ' : ' || rec_del_recs.attendance_mode );
158 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'ATTENDANCE_TYPE' )
159 || ' : ' || rec_del_recs.attendance_type );
160 IF l_b_purged THEN
161 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'DELETE_FLAG' )
162 || ' : ' || igs_ge_gen_004.genp_get_lookup ( 'TIMESLOT_ALPHABET', 'Y' ) );
163 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'STATUS' )
164 || ' : ' || igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'PURGED' ) );
165 ELSE
166 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'DELETE_FLAG' )
167 || ' : ' || igs_ge_gen_004.genp_get_lookup ( 'TIMESLOT_ALPHABET', 'N' ) );
168 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'STATUS' )
169 || ' : ' || igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'UPDATED' ) );
170 END IF;
171
172 END LOOP;
173
174 -- Log the number of records processed.
175 fnd_message.set_name ( 'IGS', 'IGS_AD_TOT_REC_PRC' );
176 fnd_message.set_token ( 'RCOUNT', l_n_rec_count );
177 fnd_file.put_line ( fnd_file.LOG, ' ' );
178 fnd_file.put_line ( fnd_file.LOG, fnd_message.get );
179
180 EXCEPTION /* This exception handling is at table level */
181 WHEN OTHERS THEN
182 -- If any exception occurs, whole transaction should be rolled off.
183 ROLLBACK TO igs_ps_ofr_opt_all;
184
185 -- We have to log message stacked by TBH, if any. Otherwise, we will log the generic unhandled exception.
186 l_c_message := fnd_message.get ;
187 IF ( l_c_message IS NOT NULL ) THEN
188 fnd_file.put_line ( fnd_file.LOG, l_c_message );
189 ELSE
190 fnd_message.set_name ( 'IGS', 'IGS_GE_UNHANDLED_EXP' );
191 fnd_message.set_token ( 'NAME', 'purge_igs_ps_ofr_opt_all' );
192 fnd_file.put_line ( fnd_file.LOG, fnd_message.get || ' : ' || SQLERRM );
193 END IF;
194
195 END purge_igs_ps_ofr_opt_all; /* End Of Processing IGS_PS_OFR_OPT_ALL records */
196
197
198
199 PROCEDURE purge_igs_ps_unit_ofr_pat_all AS
200 /***********************************************************************************************
201
202 Created By : sarakshi
203 Date Created By: 13-Jul-2004
204 Purpose : To purge logically deleted records from the table IGS_PS_UNIT_OFR_PAT_ALL.
205 If any child record exists for a logically deleted record then that
206 record will be updated such that it becomes active else it will be deleted.
207
208 Known limitations,enhancements,remarks:
209 Change History
210 Who When What
211
212 ********************************************************************************************** */
213 CURSOR c_del_recs IS
214 SELECT a.rowid, a.*
215 FROM igs_ps_unit_ofr_pat_all a
216 WHERE a.delete_flag = 'Y';
217 rec_del_recs c_del_recs%ROWTYPE;
218
219 CURSOR c_alt_code (cp_cal_type igs_ca_inst_all.cal_type%TYPE,
220 cp_sequence_number igs_ca_inst_all.sequence_number%TYPE) IS
221 SELECT alternate_code
222 FROM igs_ca_inst_all
223 WHERE cal_type = cp_cal_type
224 AND sequence_number = cp_sequence_number;
225
226 l_c_alt_code igs_ca_inst_all.alternate_code%TYPE;
227 l_b_purged BOOLEAN ;
228 l_c_message VARCHAR2 (4000);
229 l_n_rec_count NUMBER(10);
230
231 BEGIN
232 -- Create a Savepoint for Rollback
233 SAVEPOINT igs_ps_unit_ofr_pat_all ;
234
235 fnd_file.put_line ( fnd_file.LOG, ' ' );
236 fnd_message.set_name ( 'IGS', 'IGS_PS_UOP_PURGE' );
237 fnd_file.put_line ( fnd_file.LOG, fnd_message.get );
238
239 l_n_rec_count := 0;
240 FOR rec_del_recs IN c_del_recs LOOP
241 l_b_purged := TRUE;
242 l_n_rec_count := l_n_rec_count + 1;
243 BEGIN
244 -- Call before_dml of the TBH to know whether the record can physically be deleted or not.
245 igs_ps_unit_ofr_pat_pkg.before_dml(
246 p_action => 'VALIDATE_DELETE' ,
247 x_rowid => rec_del_recs.rowid,
248 x_unit_cd => rec_del_recs.unit_cd,
249 x_version_number => rec_del_recs.version_number,
250 x_cal_type => rec_del_recs.cal_type,
251 x_ci_sequence_number => rec_del_recs.ci_sequence_number,
252 x_ci_start_dt => rec_del_recs.ci_start_dt,
253 x_ci_end_dt => rec_del_recs.ci_end_dt,
254 x_waitlist_allowed => rec_del_recs.waitlist_allowed,
255 x_max_students_per_waitlist => rec_del_recs.max_students_per_waitlist,
256 x_creation_date => rec_del_recs.creation_date,
257 x_created_by => rec_del_recs.created_by,
258 x_last_update_date => rec_del_recs.last_update_date,
259 x_last_updated_by => rec_del_recs.last_updated_by,
260 x_last_update_login => rec_del_recs.last_update_login,
261 x_org_id => rec_del_recs.org_id,
262 x_delete_flag => rec_del_recs.delete_flag ,
263 x_abort_flag => rec_del_recs.abort_flag
264 );
265
266 EXCEPTION
267 WHEN OTHERS THEN
268 -- Exception means that there are childs for the record in question.
269 -- So deletion cannot be done and moreover, delete_flag is to be made 'N'
270 -- to make it active record.
271 l_b_purged := FALSE;
272
273 -- Ignore the message stacked by TBH so delete the message from the stack.
274 IGS_GE_MSG_STACK.DELETE_MSG ;
275
276 -- Update the delete_flag to 'N'
277 igs_ps_unit_ofr_pat_pkg.update_row (
278 x_rowid => rec_del_recs.rowid ,
279 x_unit_cd => rec_del_recs.unit_cd,
280 x_version_number => rec_del_recs.version_number,
281 x_ci_sequence_number => rec_del_recs.ci_sequence_number,
282 x_cal_type => rec_del_recs.cal_type,
283 x_ci_start_dt => rec_del_recs.ci_start_dt,
284 x_ci_end_dt => rec_del_recs.ci_end_dt,
285 x_waitlist_allowed => rec_del_recs.waitlist_allowed,
286 x_max_students_per_waitlist => rec_del_recs.max_students_per_waitlist,
287 x_delete_flag => 'N',
288 x_abort_flag => rec_del_recs.abort_flag
289 );
290 END;
291
292 IF l_b_purged THEN
293 igs_ps_unit_ofr_pat_pkg.delete_row ( x_rowid => rec_del_recs.rowid );
294 END IF;
295
296 -- Log the details of record and status of purging.
297 fnd_file.put_line ( fnd_file.LOG, ' ');
298 fnd_file.put_line ( fnd_file.LOG, ' ');
299 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'UNIT_CD' )
300 || ' : ' || rec_del_recs.unit_cd );
301 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'VERSION_NUMBER' )
302 || ' : ' || rec_del_recs.version_number );
303 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'CAL_TYPE' )
304 || ' : ' || rec_del_recs.cal_type );
305 OPEN c_alt_code(rec_del_recs.cal_type,rec_del_recs.ci_sequence_number);
306 FETCH c_alt_code INTO l_c_alt_code;
307 CLOSE c_alt_code;
308 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'ALT_CODE' )
309 || ' : ' || l_c_alt_code );
310 IF l_b_purged THEN
311 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'DELETE_FLAG' )
312 || ' : ' || igs_ge_gen_004.genp_get_lookup ( 'TIMESLOT_ALPHABET', 'Y' ) );
313 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'STATUS' )
314 || ' : ' || igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'PURGED' ) );
315 ELSE
316 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'DELETE_FLAG' )
317 || ' : ' || igs_ge_gen_004.genp_get_lookup ( 'TIMESLOT_ALPHABET', 'N' ) );
318 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'STATUS' )
319 || ' : ' || igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'UPDATED' ) );
320 END IF;
321
322 END LOOP;
323
324 -- Log the number of records processed.
325 fnd_message.set_name ( 'IGS', 'IGS_AD_TOT_REC_PRC' );
326 fnd_message.set_token ( 'RCOUNT', l_n_rec_count );
327 fnd_file.put_line ( fnd_file.LOG, ' ' );
328 fnd_file.put_line ( fnd_file.LOG, fnd_message.get );
329
330 EXCEPTION /* This exception handling is at table level */
331 WHEN OTHERS THEN
332 -- If any exception occurs, whole transaction should be rolled off.
333 ROLLBACK TO igs_ps_unit_ofr_pat_all;
334
335 -- We have to log message stacked by TBH, if any. Otherwise, we will log the generic unhandled exception.
336 l_c_message := fnd_message.get ;
337 IF ( l_c_message IS NOT NULL ) THEN
338 fnd_file.put_line ( fnd_file.LOG, l_c_message );
339 ELSE
340 fnd_message.set_name ( 'IGS', 'IGS_GE_UNHANDLED_EXP' );
341 fnd_message.set_token ( 'NAME', 'purge_igs_ps_unit_ofr_pat_all' );
342 fnd_file.put_line ( fnd_file.LOG, fnd_message.get || ' : ' || SQLERRM );
343 END IF;
344
345 END purge_igs_ps_unit_ofr_pat_all; /* End Of Processing IGS_PS_UNIT_OFR_PAT_ALL records */
346
347 PROCEDURE purge_ps_records AS
348 /***********************************************************************************************
349
350 Created By : Shtatiko
351 Date Created By: 19-FEB-2003
352 Purpose : To purge logically deleted records from the tables, where logical delete
353 functionality is being used. If any child record exists for a logically deleted
354 record then that record will be updated such that it becomes active.
355 This package is specific for Program Structure and Planning module only i.e.,
356 this will process only tables of PSP module. The process will currently be
357 purging/activating logically deleted records for IGS_PS_OFR_OPT_ALL table.
358
359 Known limitations,enhancements,remarks:
360 Change History
361 Who When What
362
363 ********************************************************************************************** */
364
365
366 BEGIN
367
368 -- This sub-process should take care of all the PS tables which have delete_flag.
369 -- Processing of each table is independent in the sense that processing of each table
370 -- should be treated as one transaction. So exceptions raised in processing of one table
371 -- should not affect the processing of another table. So COMMIT, and ROLLBACK are at table level
372 -- instead of at the module level.
373
374 -- Begin Processing Records of IGS_PS_OFR_OPT_ALL table.
375 purge_igs_ps_ofr_opt_all;
376
377
378 -- Begin Processing Records of IGS_PS_UNIT_OFR_PAT_ALL table.
379 purge_igs_ps_unit_ofr_pat_all;
380
381
382 EXCEPTION /* This exception handling is at Module Level */
383 WHEN OTHERS THEN
384 fnd_message.set_name ( 'IGS', 'IGS_GE_UNHANDLED_EXP' );
385 fnd_message.set_token ( 'NAME', 'purge_ps_records' );
386 fnd_file.put_line ( fnd_file.LOG, fnd_message.get || ' : ' || SQLERRM );
387
388 END purge_ps_records;
389
390 END igs_ps_purge_del_record;