1 Package Body per_ast_del as
2 /* $Header: peastrhi.pkb 120.7.12010000.2 2008/10/20 14:11:39 kgowripe ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_ast_del.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------------< delete_dml >------------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- This procedure controls the actual dml delete logic. The functions of
17 -- this procedure are as follows:
18 -- l) To delete the specified row from the schema using the primary key in
19 -- the predicates.
20 -- 2) To trap any constraint violations that may have occurred.
21 -- 3) To raise any other errors.
22 --
23 -- Pre Conditions:
24 -- This is an internal private procedure which must be called from the del
25 -- procedure.
26 --
27 -- In Parameters:
28 -- A Pl/Sql record structre.
29 --
30 -- Post Success:
31 -- The specified row will be delete from the schema.
32 --
33 -- Post Failure:
34 -- If a child integrity constraint violation is raised the
35 -- constraint_error procedure will be called.
36 --
37 -- Developer Implementation Notes:
38 -- None.
39 --
40 -- Access Status:
41 -- Internal Table Handler Use Only.
42 --
43 -- {End Of Comments}
44 -- ----------------------------------------------------------------------------
45 Procedure delete_dml(p_rec in per_ast_shd.g_rec_type) is
46 --
47 l_proc varchar2(72) := g_package||'delete_dml';
48 --
49 Begin
50 hr_utility.set_location('Entering:'||l_proc, 5);
51 --
52 -- Delete the rows in per_competence_elements
53 -- which relate to this assesment_type.
54 -- This will allow a cascade delete from the api
55 -- There is a pigs ear relationship so this will find all the necessary rows
56 /*
57 Changes for Bug#6522890. Broken this into 2 sql's for improving performance.
58 delete from per_competence_elements
59 where competence_element_id in
60 (select competence_element_id from per_competence_elements cel1
61 where cel1.assessment_type_id = p_rec.assessment_type_id
62 Or parent_competence_element_id in
63 (select competence_element_id
64 from per_competence_elements cel2
65 where cel2.assessment_type_id = p_rec.assessment_type_id)
66 );
67 */
68 DELETE FROM per_competence_elements
69 WHERE
70 competence_element_id IN (SELECT competence_element_id
71 FROM per_competence_elements cel1
72 WHERE cel1.assessment_type_id = p_rec.assessment_type_id);
73
74 DELETE FROM per_competence_elements
75 WHERE
76 parent_competence_element_id IN (SELECT competence_element_id
77 FROM per_competence_elements cel2
78 WHERE cel2.assessment_type_id = p_rec.assessment_type_id );
79 -- Delete the per_assessment_types row.
80 --
81 delete from per_assessment_types
82 where assessment_type_id = p_rec.assessment_type_id;
83 --
84 hr_utility.set_location(' Leaving:'||l_proc, 10);
85 --
86 Exception
87 When hr_api.child_integrity_violated then
88 -- Child integrity has been violated
89 per_ast_shd.constraint_error
90 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
91 When Others Then
92 Raise;
93 End delete_dml;
94 --
95 -- ----------------------------------------------------------------------------
96 -- |------------------------------< pre_delete >------------------------------|
97 -- ----------------------------------------------------------------------------
98 -- {Start Of Comments}
99 --
100 -- Description:
101 -- This private procedure contains any processing which is required before
102 -- the delete dml.
103 --
104 -- Pre Conditions:
105 -- This is an internal procedure which is called from the del procedure.
106 --
107 -- In Parameters:
108 -- A Pl/Sql record structre.
109 --
110 -- Post Success:
111 -- Processing continues.
112 --
113 -- Post Failure:
114 -- If an error has occurred, an error message and exception will be raised
115 -- but not handled.
116 --
117 -- Developer Implementation Notes:
118 -- Any pre-processing required before the delete dml is issued should be
119 -- coded within this procedure. It is important to note that any 3rd party
120 -- maintenance should be reviewed before placing in this procedure.
121 --
122 -- Access Status:
123 -- Internal Table Handler Use Only.
124 --
125 -- {End Of Comments}
126 -- ----------------------------------------------------------------------------
127 Procedure pre_delete(p_rec in per_ast_shd.g_rec_type) is
128 --
129 l_proc varchar2(72) := g_package||'pre_delete';
130 --
131 Begin
132 hr_utility.set_location('Entering:'||l_proc, 5);
133 --
134 hr_utility.set_location(' Leaving:'||l_proc, 10);
135 End pre_delete;
136 --
137 -- ----------------------------------------------------------------------------
138 -- |-----------------------------< post_delete >------------------------------|
139 -- ----------------------------------------------------------------------------
140 -- {Start Of Comments}
141 --
142 -- Description:
143 -- This private procedure contains any processing which is required after the
144 -- delete dml.
145 --
146 -- Pre Conditions:
147 -- This is an internal procedure which is called from the del procedure.
148 --
149 -- In Parameters:
150 -- A Pl/Sql record structre.
151 --
152 -- Post Success:
153 -- Processing continues.
154 --
155 -- Post Failure:
156 -- If an error has occurred, an error message and exception will be raised
157 -- but not handled.
158 --
159 -- Developer Implementation Notes:
160 -- Any post-processing required after the delete dml is issued should be
161 -- coded within this procedure. It is important to note that any 3rd party
162 -- maintenance should be reviewed before placing in this procedure.
163 --
164 -- Access Status:
165 -- Internal table Handler Use Only.
166 --
167 -- {End Of Comments}
168 -- ----------------------------------------------------------------------------
169 Procedure post_delete(p_rec in per_ast_shd.g_rec_type) is
170 --
171 l_proc varchar2(72) := g_package||'post_delete';
172 --
173 Begin
174 hr_utility.set_location('Entering:'||l_proc, 5);
175 --
176 -- This is a hook point and the user hook for post_delete is called here.
177 --
178 begin
179 per_ast_rkd.after_delete (
180 p_assessment_type_id => p_rec.assessment_type_id ,
181 p_business_group_id_o => per_ast_shd.g_old_rec.business_group_id ,
182 p_object_version_number_o => per_ast_shd.g_old_rec.object_version_number,
183 p_name_o => per_ast_shd.g_old_rec.name ,
184 p_date_to_o => per_ast_shd.g_old_rec.date_to ,
185 p_date_from_o => per_ast_shd.g_old_rec.date_from ,
186 p_assessment_classification_o =>
187 per_ast_shd.g_old_rec.assessment_classification ,
188 p_display_assessment_comment_o =>
189 per_ast_shd.g_old_rec.display_assessment_comments,
190 p_description_o => per_ast_shd.g_old_rec.description ,
191 p_rating_scale_comment_o => per_ast_shd.g_old_rec.rating_scale_comment,
192 p_weighting_scale_comment_o =>
193 per_ast_shd.g_old_rec.weighting_scale_comment ,
194 p_comments_o => per_ast_shd.g_old_rec.comments ,
195 p_instructions_o => per_ast_shd.g_old_rec.instructions ,
196 p_line_score_formula_o => per_ast_shd.g_old_rec.line_score_formula ,
197 p_total_score_formula_o => per_ast_shd.g_old_rec.total_score_formula ,
198 p_weighting_classification_o =>
199 per_ast_shd.g_old_rec.weighting_classification ,
200 p_rating_scale_id_o => per_ast_shd.g_old_rec.rating_scale_id ,
201 p_weighting_scale_id_o => per_ast_shd.g_old_rec.weighting_scale_id ,
202 p_attribute_category_o => per_ast_shd.g_old_rec.attribute_category ,
203 p_attribute1_o => per_ast_shd.g_old_rec.attribute1 ,
204 p_attribute2_o => per_ast_shd.g_old_rec.attribute2 ,
205 p_attribute3_o => per_ast_shd.g_old_rec.attribute3 ,
206 p_attribute4_o => per_ast_shd.g_old_rec.attribute4 ,
207 p_attribute5_o => per_ast_shd.g_old_rec.attribute5 ,
208 p_attribute6_o => per_ast_shd.g_old_rec.attribute6 ,
209 p_attribute7_o => per_ast_shd.g_old_rec.attribute7 ,
210 p_attribute8_o => per_ast_shd.g_old_rec.attribute8 ,
211 p_attribute9_o => per_ast_shd.g_old_rec.attribute9 ,
212 p_attribute10_o => per_ast_shd.g_old_rec.attribute10 ,
213 p_attribute11_o => per_ast_shd.g_old_rec.attribute11 ,
214 p_attribute12_o => per_ast_shd.g_old_rec.attribute12 ,
215 p_attribute13_o => per_ast_shd.g_old_rec.attribute13 ,
216 p_attribute14_o => per_ast_shd.g_old_rec.attribute14 ,
217 p_attribute15_o => per_ast_shd.g_old_rec.attribute15 ,
218 p_attribute16_o => per_ast_shd.g_old_rec.attribute16 ,
219 p_attribute17_o => per_ast_shd.g_old_rec.attribute17 ,
220 p_attribute18_o => per_ast_shd.g_old_rec.attribute18 ,
221 p_attribute19_o => per_ast_shd.g_old_rec.attribute19 ,
222 p_attribute20_o => per_ast_shd.g_old_rec.attribute20 ,
223 p_type_o => per_ast_shd.g_old_rec.type,
224 p_line_score_formula_id_o => per_ast_shd.g_old_rec.line_score_formula_id,
225 p_default_job_competencies_o => per_ast_shd.g_old_rec.default_job_competencies,
226 p_available_flag_o => per_ast_shd.g_old_rec.available_flag);
227 exception
228 when hr_api.cannot_find_prog_unit then
229 hr_api.cannot_find_prog_unit_error
230 ( p_module_name => 'PER_ASSESSMENT_TYPES'
231 ,p_hook_type => 'AD'
232 );
233 end;
234 -- End of API User Hook for post_delete.
235 --
236 hr_utility.set_location(' Leaving:'||l_proc, 10);
237 End post_delete;
238 --
239 -- ----------------------------------------------------------------------------
240 -- |---------------------------------< del >----------------------------------|
241 -- ----------------------------------------------------------------------------
242 Procedure del
243 (
244 p_rec in per_ast_shd.g_rec_type,
245 p_validate in boolean default false
246 ) is
247 --
248 l_proc varchar2(72) := g_package||'del';
249 --
250 Begin
251 hr_utility.set_location('Entering:'||l_proc, 5);
252 --
253 -- Determine if the business process is to be validated.
254 --
255 If p_validate then
256 --
257 -- Issue the savepoint.
258 --
259 SAVEPOINT del_per_ast;
260 End If;
261 --
262 -- We must lock the row which we need to delete.
263 --
264 per_ast_shd.lck
265 (
266 p_rec.assessment_type_id,
267 p_rec.object_version_number
268 );
269 --
270 -- Call the supporting delete validate operation
271 --
272 per_ast_bus.delete_validate(p_rec);
273 --
274 -- Call the supporting pre-delete operation
275 --
276 pre_delete(p_rec);
277 --
278 -- Delete the row.
279 --
280 delete_dml(p_rec);
281 --
282 -- Call the supporting post-delete operation
283 --
284 post_delete(p_rec);
285 --
286 -- If we are validating then raise the Validate_Enabled exception
287 --
288 If p_validate then
289 Raise HR_Api.Validate_Enabled;
290 End If;
291 --
292 hr_utility.set_location(' Leaving:'||l_proc, 10);
293 Exception
294 When HR_Api.Validate_Enabled Then
295 --
296 -- As the Validate_Enabled exception has been raised
297 -- we must rollback to the savepoint
298 --
299 ROLLBACK TO del_per_ast;
300 End del;
301 --
302 -- ----------------------------------------------------------------------------
303 -- |---------------------------------< del >----------------------------------|
304 -- ----------------------------------------------------------------------------
305 Procedure del
306 (
307 p_assessment_type_id in number,
308 p_object_version_number in number,
309 p_validate in boolean default false
310 ) is
311 --
312 l_rec per_ast_shd.g_rec_type;
313 l_proc varchar2(72) := g_package||'del';
314 --
315 Begin
316 hr_utility.set_location('Entering:'||l_proc, 5);
317 --
318 -- As the delete procedure accepts a plsql record structure we do need to
319 -- convert the arguments into the record structure.
320 -- We don't need to call the supplied conversion argument routine as we
321 -- only need a few attributes.
322 --
323 l_rec.assessment_type_id:= p_assessment_type_id;
324 l_rec.object_version_number := p_object_version_number;
325 --
326 -- Having converted the arguments into the per_ast_rec
327 -- plsql record structure we must call the corresponding entity
328 -- business process
329 --
330 del(l_rec, p_validate);
331 --
332 hr_utility.set_location(' Leaving:'||l_proc, 10);
333 End del;
334 --
335 end per_ast_del;
[Home] [Help]