1 Package Body per_pce_upd as
2 /* $Header: pepcerhi.pkb 120.1 2006/10/18 09:19:34 grreddy noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package VARCHAR2(33) := ' per_pce_upd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------------< update_dml >------------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- This procedure controls the actual dml update logic. The processing of
17 -- this procedure IS:
18 -- 1) Increment the object_version_number by 1 if the object_version_number
19 -- IS defined as an attribute for this entity.
20 -- 2) To set and unset the g_api_dml status as required (as we are about to
21 -- perform dml).
22 -- 3) To update the specified row IN the schema using the primary key IN
23 -- the predicates.
24 -- 4) To trap any constraint violations that may have occurred.
25 -- 5) To raise any other errors.
26 --
27 -- Prerequisites:
28 -- This IS an internal private procedure which must be called from the upd
29 -- procedure.
30 --
31 -- In Parameters:
32 -- A Pl/Sql record structre.
33 --
34 -- Post Success:
35 -- The specified row will be updated IN the schema.
36 --
37 -- Post Failure:
38 -- On the update dml failure it IS important to note that we always reset the
39 -- g_api_dml status to false.
40 -- If a check, unique or parent integrity constraint violation IS raised the
41 -- constraint_error procedure will be called.
42 -- If any other error IS reported, the error will be raised after the
43 -- g_api_dml status IS reset.
44 --
45 -- Developer Implementation Notes:
46 -- The update 'set' attribute list should be modified if any of your
47 -- attributes are not updateable.
48 --
49 -- Access Status:
50 -- Internal Row Handler Use Only.
51 --
52 -- {END Of Comments}
53 -- ----------------------------------------------------------------------------
54 PROCEDURE update_dml
55 (p_rec IN OUT NOCOPY per_pce_shd.g_rec_type
56 ) IS
57 --
58 l_proc VARCHAR2(72) := g_package||'update_dml';
59 --
60 BEGIN
61 hr_utility.set_location('Entering:'||l_proc, 5);
62 --
63 -- Increment the object version
64 p_rec.object_version_number := p_rec.object_version_number + 1;
65 --
66 --
67 --
68 -- Update the per_cagr_entitlements Row
69 --
70 update per_cagr_entitlements
71 set
72 cagr_entitlement_id = p_rec.cagr_entitlement_id
73 ,cagr_entitlement_item_id = p_rec.cagr_entitlement_item_id
74 ,collective_agreement_id = p_rec.collective_agreement_id
75 ,status = p_rec.status
76 ,end_date = p_rec.end_date
77 ,formula_criteria = p_rec.formula_criteria
78 ,formula_id = p_rec.formula_id
79 ,units_of_measure = p_rec.units_of_measure
80 ,message_level = p_rec.message_level
81 ,object_version_number = p_rec.object_version_number
82 where cagr_entitlement_id = p_rec.cagr_entitlement_id;
83 --
84 --
85 --
86 hr_utility.set_location(' Leaving:'||l_proc, 10);
87 --
88 EXCEPTION
89 WHEN hr_api.check_integrity_violated THEN
90 -- A check constraint has been violated
91 --
92 per_pce_shd.constraint_error
93 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
94 WHEN hr_api.parent_integrity_violated THEN
95 -- Parent integrity has been violated
96 --
97 per_pce_shd.constraint_error
98 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
99 WHEN hr_api.unique_integrity_violated THEN
100 -- Unique integrity has been violated
101 --
102 per_pce_shd.constraint_error
103 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
104 WHEN Others THEN
105 --
106 RAISE;
107 END update_dml;
108 --
109 -- ----------------------------------------------------------------------------
110 -- |------------------------------< pre_update >------------------------------|
111 -- ----------------------------------------------------------------------------
112 -- {Start Of Comments}
113 --
114 -- Description:
115 -- This private procedure contains any processing which IS required before
116 -- the update dml.
117 --
118 -- Prerequisites:
119 -- This IS an internal procedure which IS called from the upd procedure.
120 --
121 -- In Parameters:
122 -- A Pl/Sql record structure.
123 --
124 -- Post Success:
125 -- Processing continues.
126 --
127 -- Post Failure:
128 -- If an error has occurred, an error message and exception wil be raised
129 -- but not handled.
130 --
131 -- Developer Implementation Notes:
132 -- Any pre-processing required before the update dml IS issued should be
133 -- coded within this procedure. It IS important to note that any 3rd party
134 -- maintenance should be reviewed before placing IN this procedure.
135 --
136 -- Access Status:
137 -- Internal Row Handler Use Only.
138 --
139 -- {END Of Comments}
140 -- ----------------------------------------------------------------------------
141 PROCEDURE pre_update
142 (p_rec IN per_pce_shd.g_rec_type
143 ) IS
144 --
145 l_proc VARCHAR2(72) := g_package||'pre_update';
146 --
147 BEGIN
148 hr_utility.set_location('Entering:'||l_proc, 5);
149 --
150 hr_utility.set_location(' Leaving:'||l_proc, 10);
151 END pre_update;
152 --
153 -- ----------------------------------------------------------------------------
154 -- |-----------------------------< post_update >------------------------------|
155 -- ----------------------------------------------------------------------------
156 -- {Start Of Comments}
157 --
158 -- Description:
159 -- This private procedure contains any processing which IS required after
160 -- the update dml.
161 --
162 -- Prerequisites:
163 -- This IS an internal procedure which IS called from the upd procedure.
164 --
165 -- In Parameters:
166 -- A Pl/Sql record structure.
167 --
168 -- Post Success:
169 -- Processing continues.
170 --
171 -- Post Failure:
172 -- If an error has occurred, an error message and exception will be raised
173 -- but not handled.
174 --
175 -- Developer Implementation Notes:
176 -- Any post-processing required after the update dml IS issued should be
177 -- coded within this procedure. It IS important to note that any 3rd party
178 -- maintenance should be reviewed before placing IN this procedure.
179 --
180 -- Access Status:
181 -- Internal Row Handler Use Only.
182 --
183 -- {END Of Comments}
184 -- ----------------------------------------------------------------------------
185 PROCEDURE post_update
186 (p_effective_date IN DATE
187 ,p_rec IN per_pce_shd.g_rec_type
188 ) IS
189 --
190 l_proc VARCHAR2(72) := g_package||'post_update';
191 --
192 BEGIN
193 hr_utility.set_location('Entering:'||l_proc, 5);
194 begin
195 --
196 per_pce_rku.after_update
197 (p_effective_date => p_effective_date
198 ,p_cagr_entitlement_id => p_rec.cagr_entitlement_id
199 ,p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
200 ,p_collective_agreement_id => p_rec.collective_agreement_id
201 ,p_start_date => p_rec.start_date
202 ,p_end_date => p_rec.end_date
203 ,p_status => p_rec.status
204 ,p_formula_criteria => p_rec.formula_criteria
205 ,p_formula_id => p_rec.formula_id
206 ,p_units_of_measure => p_rec.units_of_measure
207 ,p_message_level => p_rec.message_level
208 ,p_object_version_number => p_rec.object_version_number
209 ,p_cagr_entitlement_item_id_o => per_pce_shd.g_old_rec.cagr_entitlement_item_id
210 ,p_collective_agreement_id_o => per_pce_shd.g_old_rec.collective_agreement_id
211 ,p_start_date_o => per_pce_shd.g_old_rec.start_date
212 ,p_end_date_o => per_pce_shd.g_old_rec.end_date
213 ,p_status_o => per_pce_shd.g_old_rec.status
214 ,p_formula_criteria_o => per_pce_shd.g_old_rec.formula_criteria
215 ,p_formula_id_o => per_pce_shd.g_old_rec.formula_id
216 ,p_units_of_measure_o => per_pce_shd.g_old_rec.units_of_measure
217 ,p_message_level_o => per_pce_shd.g_old_rec.message_level
218 ,p_object_version_number_o => per_pce_shd.g_old_rec.object_version_number
219 );
220 --
221 exception
222 --
223 when hr_api.cannot_find_prog_unit then
224 --
225 hr_api.cannot_find_prog_unit_error
226 (p_module_name => 'PER_CAGR_ENTITLEMENTS'
227 ,p_hook_type => 'AU');
228 --
229 end;
230 --
231 hr_utility.set_location(' Leaving:'||l_proc, 10);
232 END post_update;
233 --
234 -- ----------------------------------------------------------------------------
235 -- |-----------------------------< convert_defs >-----------------------------|
236 -- ----------------------------------------------------------------------------
237 -- {Start Of Comments}
238 --
239 -- Description:
240 -- The Convert_Defs procedure has one very important function:
241 -- It must return the record structure for the row with all system defaulted
242 -- values converted into its corresponding parameter value for update. WHEN
243 -- we attempt to update a row through the Upd process , certain
244 -- parameters can be defaulted which enables flexibility IN the calling of
245 -- the upd process (e.g. only attributes which need to be updated need to be
246 -- specified). For the upd process to determine which attributes
247 -- have NOT been specified we need to check if the parameter has a reserved
248 -- system DEFAULT value. Therefore, for all parameters which have a
249 -- corresponding reserved system DEFAULT mechanism specified we need to
250 -- check if a system DEFAULT IS being used. If a system DEFAULT IS being
251 -- used then we convert the defaulted value into its corresponding attribute
252 -- value held IN the g_old_rec data structure.
253 --
254 -- Prerequisites:
255 -- This private function can only be called from the upd process.
256 --
257 -- In Parameters:
258 -- A Pl/Sql record structure.
259 --
260 -- Post Success:
261 -- The record structure will be returned with all system defaulted parameter
262 -- values converted into its current row attribute value.
263 --
264 -- Post Failure:
265 -- No direct error handling IS required within this function. Any possible
266 -- errors within this procedure will be a PL/SQL value error due to
267 -- conversion of datatypes or data lengths.
268 --
269 -- Developer Implementation Notes:
270 -- None.
271 --
272 -- Access Status:
273 -- Internal Row Handler Use Only.
274 --
275 -- {END Of Comments}
276 -- ----------------------------------------------------------------------------
277 PROCEDURE convert_defs
278 (p_rec IN OUT NOCOPY per_pce_shd.g_rec_type
279 ) IS
280 --
281 BEGIN
282 --
283 -- We must now examine each argument value IN the
284 -- p_rec plsql record structure
285 -- to see if a system DEFAULT IS being used. If a system DEFAULT
286 -- IS being used then we must set to the 'current' argument value.
287 --
288 If (p_rec.cagr_entitlement_item_id = hr_api.g_number) then
289 p_rec.cagr_entitlement_item_id :=
290 per_pce_shd.g_old_rec.cagr_entitlement_item_id;
291 END If;
292 If (p_rec.collective_agreement_id = hr_api.g_number) then
293 p_rec.collective_agreement_id :=
294 per_pce_shd.g_old_rec.collective_agreement_id;
295 END If;
296 If (p_rec.start_date = hr_api.g_date) then
297 p_rec.start_date :=
298 per_pce_shd.g_old_rec.start_date;
299 END If;
300 If (p_rec.end_date = hr_api.g_date) then
301 p_rec.end_date :=
302 per_pce_shd.g_old_rec.end_date;
303 END If;
304 If (p_rec.status = hr_api.g_VARCHAR2) then
305 p_rec.status :=
306 per_pce_shd.g_old_rec.status;
307 END If;
308 If (p_rec.formula_criteria = hr_api.g_VARCHAR2) then
309 p_rec.formula_criteria :=
310 per_pce_shd.g_old_rec.formula_criteria;
311 END If;
312 If (p_rec.formula_id = hr_api.g_number) then
313 p_rec.formula_id :=
314 per_pce_shd.g_old_rec.formula_id;
315 END If;
316 If (p_rec.units_of_measure = hr_api.g_VARCHAR2) then
317 p_rec.units_of_measure := per_pce_shd.g_old_rec.units_of_measure;
318 END If;
319 --
320 IF (p_rec.message_level = hr_api.g_VARCHAR2) then
321 p_rec.message_level := per_pce_shd.g_old_rec.message_level;
322 END IF;
323 --
324 END convert_defs;
325 --
326 -- ----------------------------------------------------------------------------
327 -- |---------------------------------< upd >----------------------------------|
328 -- ----------------------------------------------------------------------------
329 PROCEDURE upd
330 (p_effective_date IN DATE
331 ,p_rec IN OUT NOCOPY per_pce_shd.g_rec_type
332 ) IS
333 --
334 l_proc VARCHAR2(72) := g_package||'upd';
335 --
336 BEGIN
337 hr_utility.set_location('Entering:'||l_proc, 5);
338 --
339 -- We must lock the row which we need to update.
340 --
341 per_pce_shd.lck
342 (p_rec.cagr_entitlement_id
343 ,p_rec.object_version_number
344 );
345 --
346 -- 1. During an update system defaults are used to determine if
347 -- arguments have been defaulted or not. We must therefore
348 -- derive the full record structure values to be updated.
349 --
350 -- 2. Call the supporting update validate operations.
351 --
352 convert_defs(p_rec);
353 --
354 per_pce_bus.update_validate
355 (p_effective_date
356 ,p_rec
357 );
358 --
359 -- Call the supporting pre-update operation
360 --
361 per_pce_upd.pre_update(p_rec);
362 --
363 -- Update the row.
364 --
365 per_pce_upd.update_dml(p_rec);
366 --
367 -- Call the supporting post-update operation
368 --
369 per_pce_upd.post_update
370 (p_effective_date
371 ,p_rec
372 );
373 END upd;
374 --
375 -- ----------------------------------------------------------------------------
376 -- |---------------------------------< upd >----------------------------------|
377 -- ----------------------------------------------------------------------------
378 PROCEDURE upd
379 (p_effective_date IN DATE
380 ,p_cagr_entitlement_id IN NUMBER
381 ,p_object_version_number IN OUT NOCOPY NUMBER
382 ,p_cagr_entitlement_item_id IN NUMBER DEFAULT hr_api.g_number
383 ,p_collective_agreement_id IN NUMBER DEFAULT hr_api.g_number
384 ,p_status IN VARCHAR2 DEFAULT hr_api.g_VARCHAR2
385 ,p_end_date IN DATE DEFAULT hr_api.g_date
386 ,p_formula_criteria IN VARCHAR2 DEFAULT hr_api.g_VARCHAR2
387 ,p_formula_id IN NUMBER DEFAULT hr_api.g_number
388 ,p_units_of_measure IN VARCHAR2 DEFAULT hr_api.g_VARCHAR2
389 ,p_message_level IN VARCHAR2 DEFAULT hr_api.g_VARCHAR2
390 ) IS
391 --
392 l_rec per_pce_shd.g_rec_type;
393 l_proc VARCHAR2(72) := g_package||'upd';
394 --
395 BEGIN
396 --
397 hr_utility.set_location('Entering:'||l_proc, 5);
398 --
399 -- Call conversion function to turn arguments into the
400 -- l_rec structure.
401 --
402 l_rec :=
403 per_pce_shd.convert_args
404 (p_cagr_entitlement_id
405 ,p_cagr_entitlement_item_id
406 ,p_collective_agreement_id
407 ,NULL -- p_start_date
408 ,p_end_date
409 ,p_status
410 ,p_formula_criteria
411 ,p_formula_id
412 ,p_units_of_measure
413 ,p_message_level
414 ,p_object_version_number
415 );
416 --
417 -- Having converted the arguments into the
418 -- plsql record structure we call the corresponding record
419 -- business process.
420 --
421 per_pce_upd.upd
422 (p_effective_date
423 ,l_rec
424 );
425 p_object_version_number := l_rec.object_version_number;
426 --
427 hr_utility.set_location(' Leaving:'||l_proc, 10);
428 END upd;
429 --
430 end per_pce_upd;
[Home] [Help]