1 Package Body per_ose_del as
2 /* $Header: peoserhi.pkb 120.2.12000000.1 2007/01/22 00:38:55 appldev ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_ose_del.'; -- Global package name
9 --
13 -- -------------------------------------------------------------------------------
10 --
11 -- -------------------------------------------------------------------------------
12 -- |---------------------------< chk_org_in_hierarchy >----------------------------|
14 Procedure chk_org_in_hierarchy
15 (p_org_structure_version_id
16 in per_org_structure_elements.org_structure_version_id%TYPE
17 ,p_organization_id
18 in per_org_structure_elements.organization_id_child%TYPE
19 ,p_exists_in_hierarchy in out nocopy VARCHAR2
20 ) is
21 --
22 --
23 begin
24 --
25 -- Is the currently displayed organization in the hierarchy?
26 -- i.e. check to see if org is a child (likely) or the top parent only (unlikely)
27 -- (by checking all children we have already tested all other parents, except the top node)
28 --
29 p_exists_in_hierarchy := 'N';
30
31 select 'Y'
32 into p_exists_in_hierarchy
33 from sys.dual
34 where exists ( select null
35 from per_org_structure_elements ose
36 where ose.org_structure_version_id = p_org_structure_version_id
37 and ose.organization_id_child = p_organization_id);
38
39 if p_exists_in_hierarchy <> 'Y' then
40 --
41 -- conditionally perform check to see if org is not duplicate of the top org
42 --
43 select 'Y'
44 into p_exists_in_hierarchy
45 from sys.dual
46 where exists ( select null
47 from per_org_structure_elements es1
48 where es1.org_structure_version_id = p_org_structure_version_id
49 and es1.organization_id_parent = p_organization_id
50 and p_organization_id not in (select organization_id_child
51 from per_org_structure_elements es
52 where es.org_structure_version_id = p_org_structure_version_id));
53
54 end if;
55 --
56 --
57 exception
58 when others then
59 null;
60 end chk_org_in_hierarchy;
61 --
62 -- ----------------------------------------------------------------------------
63 -- |------------------------------< delete_dml >------------------------------|
64 -- ----------------------------------------------------------------------------
65 -- {Start Of Comments}
66 --
67 -- Description:
68 -- This procedure controls the actual dml delete logic. The functions of
69 -- this procedure are as follows:
70 -- 1) To set and unset the g_api_dml status as required (as we are about to
71 -- perform dml).
72 -- 2) To delete the specified row from the schema using the primary key in
73 -- the predicates.
74 -- 3) To trap any constraint violations that may have occurred.
75 -- 4) To raise any other errors.
76 --
77 -- Prerequisites:
78 -- This is an internal private procedure which must be called from the del
79 -- procedure.
80 --
81 -- In Parameters:
82 -- A Pl/Sql record structre.
83 --
84 -- Post Success:
85 -- The specified row will be delete from the schema.
86 --
87 -- Post Failure:
88 -- On the delete dml failure it is important to note that we always reset the
89 -- g_api_dml status to false.
90 -- If a child integrity constraint violation is raised the
91 -- constraint_error procedure will be called.
92 -- If any other error is reported, the error will be raised after the
93 -- g_api_dml status is reset.
94 --
95 -- Developer Implementation Notes:
96 -- None.
97 --
98 -- Access Status:
99 -- Internal Row Handler Use Only.
100 --
101 -- {End Of Comments}
102 -- ----------------------------------------------------------------------------
103 Procedure delete_dml
104 (p_rec in per_ose_shd.g_rec_type
105 ) is
106 --
107 l_proc varchar2(72) := g_package||'delete_dml';
108 --
109 Begin
110 hr_utility.set_location('Entering:'||l_proc, 5);
111 --
112 per_ose_shd.g_api_dml := true; -- Set the api dml status
113 --
114 -- Delete the per_org_structure_elements row.
115 --
116 delete from per_org_structure_elements
117 where org_structure_element_id = p_rec.org_structure_element_id;
118 --
119 per_ose_shd.g_api_dml := false; -- Unset the api dml status
120 --
121 hr_utility.set_location(' Leaving:'||l_proc, 10);
122 --
123 Exception
124 When hr_api.child_integrity_violated then
125 -- Child integrity has been violated
126 per_ose_shd.g_api_dml := false; -- Unset the api dml status
127 per_ose_shd.constraint_error
128 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
129 When Others Then
130 per_ose_shd.g_api_dml := false; -- Unset the api dml status
131 Raise;
132 End delete_dml;
133 --
134 -- ----------------------------------------------------------------------------
135 -- |------------------------------< pre_delete >------------------------------|
136 -- ----------------------------------------------------------------------------
137 -- {Start Of Comments}
138 --
139 -- Description:
140 -- This private procedure contains any processing which is required before
141 -- the delete dml.
142 --
143 -- Prerequisites:
144 -- This is an internal procedure which is called from the del procedure.
145 --
146 -- In Parameters:
150 -- Processing continues.
147 -- A Pl/Sql record structre.
148 --
149 -- Post Success:
151 --
152 -- Post Failure:
153 -- If an error has occurred, an error message and exception will be raised
154 -- but not handled.
155 --
156 -- Developer Implementation Notes:
157 -- Any pre-processing required before the delete dml is issued should be
158 -- coded within this procedure. It is important to note that any 3rd party
159 -- maintenance should be reviewed before placing in this procedure.
160 --
161 -- Access Status:
162 -- Internal Row Handler Use Only.
163 --
164 -- {End Of Comments}
165 -- ----------------------------------------------------------------------------
166 Procedure pre_delete(p_rec in per_ose_shd.g_rec_type) is
167 --
168 l_proc varchar2(72) := g_package||'pre_delete';
169 --
170 Begin
171 hr_utility.set_location('Entering:'||l_proc, 5);
172 --
173 hr_utility.set_location(' Leaving:'||l_proc, 10);
174 End pre_delete;
175 --
176 -- ----------------------------------------------------------------------------
177 -- |-----------------------------< post_delete >------------------------------|
178 -- ----------------------------------------------------------------------------
179 -- {Start Of Comments}
180 --
181 -- Description:
182 -- This private procedure contains any processing which is required after the
183 -- delete dml.
184 --
185 -- Prerequistes:
186 -- This is an internal procedure which is called from the del procedure.
187 --
188 -- In Parameters:
189 -- A Pl/Sql record structure.
190 -- The parameter p_exists_in_hierarchy has been removed (bug fix 3205553)
191 --
192 -- Post Success:
193 -- Processing continues.
194 --
195 -- Post Failure:
196 -- If an error has occurred, an error message and exception will be raised
197 -- but not handled.
198 --
199 -- Developer Implementation Notes:
200 -- Any post-processing required after the delete dml is issued should be
201 -- coded within this procedure. It is important to note that any 3rd party
202 -- maintenance should be reviewed before placing in this procedure.
203 --
204 -- Access Status:
205 -- Internal Row Handler Use Only.
206 --
207 -- {End Of Comments}
208 -- -----------------------------------------------------------------------------
209 Procedure post_delete(p_rec in per_ose_shd.g_rec_type
210 -- ,p_exists_in_hierarchy in out nocopy varchar2 --bug 3205553
211 ) is
212 --
213 l_proc varchar2(72) := g_package||'post_delete';
214 --
215 Begin
216 hr_utility.set_location('Entering:'||l_proc, 5);
217 begin
218 --
219 /* Removed the call to chk_org_in_hierarchy as
220 p_exists_in_hierarchy parameter is no longer used in
221 delete_hierarchy_element api -- Bug 3205553*/
222
223 per_ose_rkd.after_delete
224 (p_org_structure_element_id
225 => p_rec.org_structure_element_id
226 ,p_business_group_id_o
227 => per_ose_shd.g_old_rec.business_group_id
228 ,p_organization_id_parent_o
229 => per_ose_shd.g_old_rec.organization_id_parent
230 ,p_org_structure_version_id_o
231 => per_ose_shd.g_old_rec.org_structure_version_id
232 ,p_organization_id_child_o
233 => per_ose_shd.g_old_rec.organization_id_child
234 ,p_request_id_o
235 => per_ose_shd.g_old_rec.request_id
236 ,p_program_application_id_o
237 => per_ose_shd.g_old_rec.program_application_id
238 ,p_program_id_o
239 => per_ose_shd.g_old_rec.program_id
240 ,p_program_update_date_o
241 => per_ose_shd.g_old_rec.program_update_date
242 ,p_object_version_number_o
243 => per_ose_shd.g_old_rec.object_version_number
244 ,p_pos_control_enabled_flag_o
245 => per_ose_shd.g_old_rec.position_control_enabled_flag
246 );
247 --
248 exception
249 --
250 when hr_api.cannot_find_prog_unit then
251 --
252 hr_api.cannot_find_prog_unit_error
253 (p_module_name => 'PER_ORG_STRUCTURE_ELEMENTS'
254 ,p_hook_type => 'AD');
255 --
256 end;
257 --
258 hr_utility.set_location(' Leaving:'||l_proc, 10);
259 End post_delete;
260 --
261 -- ----------------------------------------------------------------------------
262 -- |---------------------------------< del >----------------------------------|
263 -- ----------------------------------------------------------------------------
264 -- The parameter p_exists_in_hierarchy has been removed (bug fix 3205553)
265 --
266 Procedure del
267 (p_rec in per_ose_shd.g_rec_type
268 ,p_hr_installed in VARCHAR2
269 ,p_pa_installed in VARCHAR2
270 ,p_chk_children_exist in VARCHAR2
271 --,p_exists_in_hierarchy in out nocopy VARCHAR2 --bug 3205553
272 ) is
273 --
274 l_proc varchar2(72) := g_package||'del';
275 --
276 Begin
277 hr_utility.set_location('Entering:'||l_proc, 5);
278 --
279 -- We must lock the row which we need to delete.
280 --
281 per_ose_shd.lck
282 (p_rec.org_structure_element_id
283 ,p_rec.object_version_number
284 );
285 --
286 -- Call the supporting delete validate operation
287 --
288 per_ose_bus.delete_validate
289 (p_rec => per_ose_shd.g_old_rec
290 ,p_hr_installed => p_hr_installed
291 ,p_pa_installed => p_pa_installed
292 ,p_chk_children_exist => p_chk_children_exist
293 );
294 --
298 --
295 -- Call the supporting pre-delete operation
296 --
297 per_ose_del.pre_delete(p_rec);
299 -- Delete the row.
300 --
301 per_ose_del.delete_dml(p_rec);
302 --
303 -- Call the supporting post-delete operation
304 --
305 per_ose_del.post_delete
306 (per_ose_shd.g_old_rec
307 -- ,p_exists_in_hierarchy => p_exists_in_hierarchy --Bug 3205553
308 );
309 --
310 End del;
311 --
312 -- ----------------------------------------------------------------------------
313 -- |---------------------------------< del >----------------------------------|
314 -- ----------------------------------------------------------------------------
315 -- The parameter p_exists_in_hierarchy has been removed (bug fix 3205553)
316 --
317 Procedure del
318 (p_org_structure_element_id in number
319 ,p_object_version_number in number
320 ,p_hr_installed in VARCHAR2
321 ,p_pa_installed in VARCHAR2
322 ,p_chk_children_exist in VARCHAR2
323 --,p_exists_in_hierarchy in out nocopy VARCHAR2 --bug 3205553
324 ) is
325 --
326 l_rec per_ose_shd.g_rec_type;
327 l_proc varchar2(72) := g_package||'del';
328 --
329 Begin
330 hr_utility.set_location('Entering:'||l_proc, 5);
331 --
332 -- As the delete procedure accepts a plsql record structure we do need to
333 -- convert the arguments into the record structure.
334 -- We don't need to call the supplied conversion argument routine as we
338 l_rec.object_version_number := p_object_version_number;
335 -- only need a few attributes.
336 --
337 l_rec.org_structure_element_id := p_org_structure_element_id;
339 --
340 -- Having converted the arguments into the per_ose_rec
341 -- plsql record structure we must call the corresponding entity
342 -- business process
343 --
344 per_ose_del.del(p_rec => l_rec
345 ,p_hr_installed => p_hr_installed
346 ,p_pa_installed => p_pa_installed
347 ,p_chk_children_exist => p_chk_children_exist
348 -- ,p_exists_in_hierarchy => p_exists_in_hierarchy -- Bug 3205553
349 );
350 --
351 hr_utility.set_location(' Leaving:'||l_proc, 10);
352 End del;
353 --
354 end per_ose_del;
[Home] [Help]