1 Package Body pqh_wks_upd as
2 /* $Header: pqwksrhi.pkb 120.0 2005/05/29 03:01:44 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_wks_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 update the specified row in the schema using the primary key in
21 -- the predicates.
22 -- 3) To trap any constraint violations that may have occurred.
23 -- 4) To raise any other errors.
24 --
25 -- Prerequisites:
26 -- This is an internal private procedure which must be called from the upd
27 -- procedure.
28 --
29 -- In Parameters:
30 -- A Pl/Sql record structre.
31 --
32 -- Post Success:
33 -- The specified row will be updated in the schema.
34 --
35 -- Post Failure:
36 -- If a check, unique or parent integrity constraint violation is raised the
37 -- constraint_error procedure will be called.
38 --
39 -- Developer Implementation Notes:
40 -- The update 'set' attribute list should be modified if any of your
41 -- attributes are not updateable.
42 --
43 -- Access Status:
44 -- Internal Row Handler Use Only.
45 --
46 -- {End Of Comments}
47 -- ----------------------------------------------------------------------------
48 Procedure update_dml(p_rec in out nocopy pqh_wks_shd.g_rec_type) is
49 --
50 l_proc varchar2(72) := g_package||'update_dml';
51 --
52 Begin
53 hr_utility.set_location('Entering:'||l_proc, 5);
54 --
55 -- Increment the object version
56 --
57 p_rec.object_version_number := p_rec.object_version_number + 1;
58 --
59 --
60 -- Update the pqh_worksheets Row
61 --
62 update pqh_worksheets
63 set
64 worksheet_id = p_rec.worksheet_id,
65 budget_id = p_rec.budget_id,
66 worksheet_name = p_rec.worksheet_name,
67 version_number = p_rec.version_number,
68 action_date = p_rec.action_date,
69 date_from = p_rec.date_from,
70 date_to = p_rec.date_to,
71 worksheet_mode_cd = p_rec.worksheet_mode_cd,
72 transaction_status = p_rec.transaction_status,
73 object_version_number = p_rec.object_version_number,
74 budget_version_id = p_rec.budget_version_id,
75 propagation_method = p_rec.propagation_method,
76 wf_transaction_category_id = p_rec.wf_transaction_category_id
77 where worksheet_id = p_rec.worksheet_id;
78 --
79 --
80 hr_utility.set_location(' Leaving:'||l_proc, 10);
81 --
82 Exception
83 When hr_api.check_integrity_violated Then
84 -- A check constraint has been violated
85 pqh_wks_shd.constraint_error
86 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
87 When hr_api.parent_integrity_violated Then
88 -- Parent integrity has been violated
89 pqh_wks_shd.constraint_error
90 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
91 When hr_api.unique_integrity_violated Then
92 -- Unique integrity has been violated
93 pqh_wks_shd.constraint_error
94 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
95 When Others Then
96 Raise;
97 End update_dml;
98 --
99 -- ----------------------------------------------------------------------------
100 -- |------------------------------< pre_update >------------------------------|
101 -- ----------------------------------------------------------------------------
102 -- {Start Of Comments}
103 --
104 -- Description:
105 -- This private procedure contains any processing which is required before
106 -- the update dml.
107 --
108 -- Prerequisites:
109 -- This is an internal procedure which is called from the upd procedure.
110 --
111 -- In Parameters:
112 -- A Pl/Sql record structre.
113 --
114 -- Post Success:
115 -- Processing continues.
116 --
117 -- Post Failure:
118 -- If an error has occurred, an error message and exception will be raised
119 -- but not handled.
120 --
121 -- Developer Implementation Notes:
122 -- Any pre-processing required before the update dml is issued should be
123 -- coded within this procedure. It is important to note that any 3rd party
124 -- maintenance should be reviewed before placing in this procedure.
125 --
126 -- Access Status:
127 -- Internal Row Handler Use Only.
128 --
129 -- {End Of Comments}
130 -- ----------------------------------------------------------------------------
131 Procedure pre_update(p_rec in pqh_wks_shd.g_rec_type) is
132 --
133 l_proc varchar2(72) := g_package||'pre_update';
134 --
135 Begin
136 hr_utility.set_location('Entering:'||l_proc, 5);
137 --
138 hr_utility.set_location(' Leaving:'||l_proc, 10);
139 End pre_update;
140 --
141 -- ----------------------------------------------------------------------------
142 -- |-----------------------------< post_update >------------------------------|
143 -- ----------------------------------------------------------------------------
144 -- {Start Of Comments}
145 --
146 -- Description:
147 -- This private procedure contains any processing which is required after the
148 -- update dml.
149 --
150 -- Prerequisites:
151 -- This is an internal procedure which is called from the upd procedure.
152 --
153 -- In Parameters:
154 -- A Pl/Sql record structre.
155 --
156 -- Post Success:
157 -- Processing continues.
158 --
159 -- Post Failure:
160 -- If an error has occurred, an error message and exception will be raised
161 -- but not handled.
162 --
163 -- Developer Implementation Notes:
164 -- Any post-processing required after the update dml is issued should be
165 -- coded within this procedure. It is important to note that any 3rd party
166 -- maintenance should be reviewed before placing in this procedure.
167 --
168 -- Access Status:
169 -- Internal Row Handler Use Only.
170 --
171 -- {End Of Comments}
172 -- ----------------------------------------------------------------------------
173 Procedure post_update(
174 p_effective_date in date,p_rec in pqh_wks_shd.g_rec_type) is
175 --
176 l_proc varchar2(72) := g_package||'post_update';
177 --
178 Begin
179 hr_utility.set_location('Entering:'||l_proc, 5);
180 --
181 --
182 -- Start of API User Hook for post_update.
183 --
184 begin
185 --
186 pqh_wks_rku.after_update
187 (
188 p_worksheet_id =>p_rec.worksheet_id
189 ,p_budget_id =>p_rec.budget_id
190 ,p_worksheet_name =>p_rec.worksheet_name
191 ,p_version_number =>p_rec.version_number
192 ,p_action_date =>p_rec.action_date
193 ,p_date_from =>p_rec.date_from
194 ,p_date_to =>p_rec.date_to
195 ,p_worksheet_mode_cd =>p_rec.worksheet_mode_cd
196 ,p_transaction_status =>p_rec.transaction_status
197 ,p_object_version_number =>p_rec.object_version_number
198 ,p_budget_version_id =>p_rec.budget_version_id
199 ,p_propagation_method =>p_rec.propagation_method
200 ,p_effective_date =>p_effective_date
201 ,p_wf_transaction_category_id =>p_rec.wf_transaction_category_id
202 ,p_budget_id_o =>pqh_wks_shd.g_old_rec.budget_id
203 ,p_worksheet_name_o =>pqh_wks_shd.g_old_rec.worksheet_name
204 ,p_version_number_o =>pqh_wks_shd.g_old_rec.version_number
205 ,p_action_date_o =>pqh_wks_shd.g_old_rec.action_date
206 ,p_date_from_o =>pqh_wks_shd.g_old_rec.date_from
207 ,p_date_to_o =>pqh_wks_shd.g_old_rec.date_to
208 ,p_worksheet_mode_cd_o =>pqh_wks_shd.g_old_rec.worksheet_mode_cd
209 ,p_transaction_status_o =>pqh_wks_shd.g_old_rec.transaction_status
210 ,p_object_version_number_o =>pqh_wks_shd.g_old_rec.object_version_number
211 ,p_budget_version_id_o =>pqh_wks_shd.g_old_rec.budget_version_id
212 ,p_propagation_method_o =>pqh_wks_shd.g_old_rec.propagation_method
213 ,p_wf_transaction_category_id_o =>pqh_wks_shd.g_old_rec.wf_transaction_category_id
214 );
215 --
216 exception
217 --
218 when hr_api.cannot_find_prog_unit then
219 --
220 hr_api.cannot_find_prog_unit_error
221 (p_module_name => 'pqh_worksheets'
222 ,p_hook_type => 'AU');
223 --
224 end;
225 --
226 -- End of API User Hook for post_update.
227 --
228 --
229 hr_utility.set_location(' Leaving:'||l_proc, 10);
230 End post_update;
231 --
232 -- ----------------------------------------------------------------------------
233 -- |-----------------------------< convert_defs >-----------------------------|
234 -- ----------------------------------------------------------------------------
235 -- {Start Of Comments}
236 --
237 -- Description:
238 -- The Convert_Defs procedure has one very important function:
239 -- It must return the record structure for the row with all system defaulted
240 -- values converted into its corresponding parameter value for update. When
241 -- we attempt to update a row through the Upd process , certain
242 -- parameters can be defaulted which enables flexibility in the calling of
243 -- the upd process (e.g. only attributes which need to be updated need to be
244 -- specified). For the upd process to determine which attributes
245 -- have NOT been specified we need to check if the parameter has a reserved
246 -- system default value. Therefore, for all parameters which have a
247 -- corresponding reserved system default mechanism specified we need to
248 -- check if a system default is being used. If a system default is being
249 -- used then we convert the defaulted value into its corresponding attribute
250 -- value held in the g_old_rec data structure.
251 --
252 -- Prerequisites:
253 -- This private function can only be called from the upd process.
254 --
255 -- In Parameters:
256 -- A Pl/Sql record structre.
257 --
258 -- Post Success:
259 -- The record structure will be returned with all system defaulted parameter
260 -- values converted into its current row attribute value.
261 --
262 -- Post Failure:
263 -- No direct error handling is required within this function. Any possible
264 -- errors within this procedure will be a PL/SQL value error due to conversion
265 -- of datatypes or data lengths.
266 --
267 -- Developer Implementation Notes:
268 -- None.
269 --
270 -- Access Status:
271 -- Internal Row Handler Use Only.
272 --
273 -- {End Of Comments}
274 -- ----------------------------------------------------------------------------
275 Procedure convert_defs(p_rec in out nocopy pqh_wks_shd.g_rec_type) is
276 --
277 l_proc varchar2(72) := g_package||'convert_defs';
278 --
279 Begin
280 --
281 hr_utility.set_location('Entering:'||l_proc, 5);
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.budget_id = hr_api.g_number) then
289 p_rec.budget_id :=
290 pqh_wks_shd.g_old_rec.budget_id;
291 End If;
292 If (p_rec.worksheet_name = hr_api.g_varchar2) then
293 p_rec.worksheet_name :=
294 pqh_wks_shd.g_old_rec.worksheet_name;
295 End If;
296 If (p_rec.version_number = hr_api.g_number) then
297 p_rec.version_number :=
298 pqh_wks_shd.g_old_rec.version_number;
299 End If;
300 If (p_rec.action_date = hr_api.g_date) then
301 p_rec.action_date :=
302 pqh_wks_shd.g_old_rec.action_date;
303 End If;
304 If (p_rec.date_from = hr_api.g_date) then
305 p_rec.date_from :=
306 pqh_wks_shd.g_old_rec.date_from;
307 End If;
308 If (p_rec.date_to = hr_api.g_date) then
309 p_rec.date_to :=
310 pqh_wks_shd.g_old_rec.date_to;
311 End If;
312 If (p_rec.worksheet_mode_cd = hr_api.g_varchar2) then
313 p_rec.worksheet_mode_cd :=
314 pqh_wks_shd.g_old_rec.worksheet_mode_cd;
315 End If;
316 If (p_rec.transaction_status = hr_api.g_varchar2) then
317 p_rec.transaction_status :=
318 pqh_wks_shd.g_old_rec.transaction_status;
319 End If;
320 If (p_rec.budget_version_id = hr_api.g_number) then
321 p_rec.budget_version_id :=
322 pqh_wks_shd.g_old_rec.budget_version_id;
323 End If;
324 If (p_rec.propagation_method = hr_api.g_varchar2) then
325 p_rec.propagation_method :=
326 pqh_wks_shd.g_old_rec.propagation_method;
327 End If;
328 If (p_rec.wf_transaction_category_id = hr_api.g_number) then
329 p_rec.wf_transaction_category_id :=
330 pqh_wks_shd.g_old_rec.wf_transaction_category_id;
331 End If;
332 --
333 hr_utility.set_location(' Leaving:'||l_proc, 10);
334 --
335 End convert_defs;
336 --
337 -- ----------------------------------------------------------------------------
338 -- |---------------------------------< upd >----------------------------------|
339 -- ----------------------------------------------------------------------------
340 Procedure upd
341 (
342 p_effective_date in date,
343 p_rec in out nocopy pqh_wks_shd.g_rec_type
344 ) is
345 --
346 l_proc varchar2(72) := g_package||'upd';
347 --
348 Begin
349 hr_utility.set_location('Entering:'||l_proc, 5);
350 --
351 -- We must lock the row which we need to update.
352 --
353 pqh_wks_shd.lck
354 (
355 p_rec.worksheet_id,
356 p_rec.object_version_number
357 );
358 --
359 -- 1. During an update system defaults are used to determine if
360 -- arguments have been defaulted or not. We must therefore
361 -- derive the full record structure values to be updated.
362 --
363 -- 2. Call the supporting update validate operations.
364 --
365 convert_defs(p_rec);
366 pqh_wks_bus.update_validate(p_rec
367 ,p_effective_date);
368 --
369 -- Call the supporting pre-update operation
370 --
371 pre_update(p_rec);
372 --
373 -- Update the row.
374 --
375 update_dml(p_rec);
376 --
377 -- Call the supporting post-update operation
378 --
379 post_update(
380 p_effective_date,p_rec);
381 End upd;
382 --
383 -- ----------------------------------------------------------------------------
384 -- |---------------------------------< upd >----------------------------------|
385 -- ----------------------------------------------------------------------------
386 Procedure upd
387 (
388 p_effective_date in date,
389 p_worksheet_id in number,
390 p_budget_id in number default hr_api.g_number,
391 p_worksheet_name in varchar2 default hr_api.g_varchar2,
392 p_version_number in number default hr_api.g_number,
393 p_action_date in date default hr_api.g_date,
394 p_date_from in date default hr_api.g_date,
395 p_date_to in date default hr_api.g_date,
396 p_worksheet_mode_cd in varchar2 default hr_api.g_varchar2,
397 p_transaction_status in varchar2 default hr_api.g_varchar2,
398 p_object_version_number in out nocopy number,
399 p_budget_version_id in number default hr_api.g_number,
400 p_propagation_method in varchar2 default hr_api.g_varchar2,
401 p_wf_transaction_category_id in number
402 ) is
403 --
404 l_rec pqh_wks_shd.g_rec_type;
405 l_proc varchar2(72) := g_package||'upd';
406 --
407 Begin
408 hr_utility.set_location('Entering:'||l_proc, 5);
409 --
410 -- Call conversion function to turn arguments into the
411 -- l_rec structure.
412 --
413 l_rec :=
414 pqh_wks_shd.convert_args
415 (
416 p_worksheet_id,
417 p_budget_id,
418 p_worksheet_name,
419 p_version_number,
420 p_action_date,
421 p_date_from,
422 p_date_to,
423 p_worksheet_mode_cd,
424 p_transaction_status,
425 p_object_version_number,
426 p_budget_version_id,
427 p_propagation_method,
428 p_wf_transaction_category_id
429 );
430 --
431 -- Having converted the arguments into the
432 -- plsql record structure we call the corresponding record
433 -- business process.
434 --
435 upd(
436 p_effective_date,l_rec);
437 p_object_version_number := l_rec.object_version_number;
438 --
439 hr_utility.set_location(' Leaving:'||l_proc, 10);
440 End upd;
441 --
442 end pqh_wks_upd;
[Home] [Help]