1 package body hxc_tbb_upd as
2 /* $Header: hxctbbrhi.pkb 120.6.12010000.1 2008/07/28 11:19:46 appldev ship $ */
3
4 -- --------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- --------------------------------------------------------------------------
7
8 g_package varchar2(33) := ' hxc_tbb_upd.'; -- global package name
9
10 g_debug boolean := hr_utility.debug_enabled;
11
12 -- --------------------------------------------------------------------------
13 -- |------------------------------< update_dml >----------------------------|
14 -- --------------------------------------------------------------------------
15 -- {Start Of Comments}
16 --
17 -- Description:
18 -- This procedure controls the actual dml update logic. The processing of
19 -- this procedure is:
20 -- 1) Increment the object_version_number by 1 if the object_version_number
21 -- is defined as an attribute for this entity.
22 -- 2) To set and unset the g_api_dml status as required (as we are about to
23 -- perform dml).
24 -- 3) To update the specified row in the schema using the primary key in
25 -- the predicates.
26 -- 4) To trap any constraint violations that may have occurred.
27 -- 5) To raise any other errors.
28 --
29 -- Prerequisites:
30 -- This is an internal private procedure which must be called from the upd
31 -- procedure.
32 --
33 -- In Parameters:
34 -- A Pl/Sql record structre.
35 --
36 -- Post Success:
37 -- The specified row will be updated in the schema.
38 --
39 -- Post Failure:
40 -- On the update dml failure it is important to note that we always reset the
41 -- g_api_dml status to false.
42 -- if a check, unique or parent integrity constraint violation is raised the
43 -- constraint_error procedure will be called.
44 -- if any other error is reported, the error will be raised after the
45 -- g_api_dml status is reset.
46 --
47 -- Developer Implementation Notes:
48 -- The update 'set' attribute list should be modified if any of your
49 -- attributes are not updateable.
50 --
51 -- Access Status:
52 -- Internal Row Handler Use Only.
53 --
54 -- {end Of Comments}
55 -- --------------------------------------------------------------------------
56 procedure update_dml
57 (p_rec in out nocopy hxc_tbb_shd.g_rec_type
58 ) is
59
60 l_proc varchar2(72);
61
62 begin
63
64
65
66 if g_debug then
67 l_proc := g_package||'update_dml';
68 hr_utility.set_location('Entering:'||l_proc, 5);
69 end if;
70
71 -- increment the object version
72
73 p_rec.object_version_number := p_rec.object_version_number + 1;
74
75 -- update the hxc_time_building_blocks row
76
77 update hxc_time_building_blocks
78 set
79 time_building_block_id = p_rec.time_building_block_id
80 ,type = p_rec.type
81 ,measure = p_rec.measure
82 ,unit_of_measure = p_rec.unit_of_measure
83 ,start_time = p_rec.start_time
84 ,stop_time = p_rec.stop_time
85 ,parent_building_block_id = p_rec.parent_building_block_id
86 ,parent_building_block_ovn = p_rec.parent_building_block_ovn
87 ,scope = p_rec.scope
88 ,object_version_number = p_rec.object_version_number
89 ,approval_status = p_rec.approval_status
90 ,resource_id = p_rec.resource_id
91 ,resource_type = p_rec.resource_type
92 ,approval_style_id = p_rec.approval_style_id
93 ,comment_text = p_rec.comment_text
94 ,application_set_id = p_rec.application_set_id
95 ,data_set_id = p_rec.data_set_id
96 ,translation_display_key = p_rec.translation_display_key
97 ,last_updated_by = fnd_global.user_id
98 ,last_update_date = sysdate
99 ,last_update_login = fnd_global.login_id
100
101 where time_building_block_id = p_rec.time_building_block_id;
102
103 if g_debug then
104 hr_utility.set_location(' Leaving:'||l_proc, 10);
105 end if;
106
107 exception
108 when hr_api.check_integrity_violated then
109 -- a check constraint has been violated
110 hxc_tbb_shd.constraint_error
111 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
112 when hr_api.parent_integrity_violated then
113 -- parent integrity has been violated
114 hxc_tbb_shd.constraint_error
115 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
116 when hr_api.unique_integrity_violated then
117 -- unique integrity has been violated
118 hxc_tbb_shd.constraint_error
119 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
120 when others then
121 raise;
122
123 end update_dml;
124
125 -- --------------------------------------------------------------------------
126 -- |------------------------------< pre_update >----------------------------|
127 -- --------------------------------------------------------------------------
128 -- {Start Of Comments}
129 --
130 -- Description:
131 -- This private procedure contains any processing which is required before
132 -- the update dml.
133 --
134 -- Prerequisites:
135 -- This is an internal procedure which is called from the upd procedure.
136 --
137 -- In Parameters:
138 -- A Pl/Sql record structure.
139 --
140 -- Post Success:
141 -- Processing continues.
142 --
143 -- Post Failure:
144 -- if an error has occurred, an error message and exception wil be raised
145 -- but not handled.
146 --
147 -- Developer Implementation Notes:
148 -- Any pre-processing required before the update dml is issued should be
149 -- coded within this procedure. It is important to note that any 3rd party
150 -- maintenance should be reviewed before placing in this procedure.
151 --
152 -- Access Status:
153 -- Internal Row Handler Use Only.
154 --
155 -- {end Of Comments}
156 -- --------------------------------------------------------------------------
157 procedure pre_update
158 (p_rec in hxc_tbb_shd.g_rec_type
159 ) is
160
161 l_proc varchar2(72);
162
163 begin
164
165
166
167 if g_debug then
168 l_proc := g_package||'pre_update';
169 hr_utility.set_location('Entering:'||l_proc, 5);
170
171 hr_utility.set_location(' Leaving:'||l_proc, 10);
172 end if;
173
174 end pre_update;
175
176 -- --------------------------------------------------------------------------
177 -- |-----------------------------< post_update >----------------------------|
178 -- --------------------------------------------------------------------------
179 -- {Start Of Comments}
180 --
181 -- Description:
182 -- This private procedure contains any processing which is required after the
183 -- update dml.
184 --
185 -- Prerequisites:
186 -- This is an internal procedure which is called from the upd procedure.
187 --
188 -- In Parameters:
189 -- A Pl/Sql record structure.
190 --
191 -- Post Success:
192 -- Processing continues.
193 --
194 -- Post Failure:
195 -- if an error has occurred, an error message and exception will be raised
196 -- but not handled.
197 --
198 -- Developer Implementation Notes:
199 -- Any post-processing required after the update dml is issued should be
200 -- coded within this procedure. It is important to note that any 3rd party
201 -- maintenance should be reviewed before placing in this procedure.
202 --
203 -- Access Status:
204 -- Internal Row Handler Use Only.
205 --
206 -- {end Of Comments}
207 -- --------------------------------------------------------------------------
208 procedure post_update
209 (p_effective_date in date
210 ,p_rec in hxc_tbb_shd.g_rec_type
211 ) is
212
213 l_proc varchar2(72);
214
215 begin
216
217
218
219 if g_debug then
220 l_proc := g_package||'post_update';
221 hr_utility.set_location('Entering:'||l_proc, 5);
222 end if;
223 begin
224
225 hxc_tbb_rku.after_update
226 (p_effective_date
227 => p_effective_date
228 ,p_time_building_block_id
229 => p_rec.time_building_block_id
230 ,p_measure
231 => p_rec.measure
232 ,p_unit_of_measure
233 => p_rec.unit_of_measure
234 ,p_start_time
235 => p_rec.start_time
236 ,p_stop_time
237 => p_rec.stop_time
238 ,p_parent_building_block_id
239 => p_rec.parent_building_block_id
240 ,p_parent_building_block_ovn
241 => p_rec.parent_building_block_ovn
242 ,p_object_version_number
243 => p_rec.object_version_number
244 ,p_approval_status
245 => p_rec.approval_status
246 ,p_approval_style_id
247 => p_rec.approval_style_id
248 ,p_date_from
249 => p_rec.date_from
250 ,p_date_to
251 => p_rec.date_to
252 ,p_comment_text
253 => p_rec.comment_text
254 ,p_application_set_id
255 => p_rec.application_set_id
256 ,p_translation_display_key
257 => p_rec.translation_display_key
258 ,p_type_o
259 => hxc_tbb_shd.g_old_rec.type
260 ,p_measure_o
261 => hxc_tbb_shd.g_old_rec.measure
262 ,p_unit_of_measure_o
263 => hxc_tbb_shd.g_old_rec.unit_of_measure
264 ,p_start_time_o
265 => hxc_tbb_shd.g_old_rec.start_time
266 ,p_stop_time_o
267 => hxc_tbb_shd.g_old_rec.stop_time
268 ,p_parent_building_block_id_o
269 => hxc_tbb_shd.g_old_rec.parent_building_block_id
270 ,p_parent_building_block_ovn_o
271 => hxc_tbb_shd.g_old_rec.parent_building_block_ovn
272 ,p_scope_o
273 => hxc_tbb_shd.g_old_rec.scope
274 ,p_object_version_number_o
275 => hxc_tbb_shd.g_old_rec.object_version_number
276 ,p_approval_status_o
277 => hxc_tbb_shd.g_old_rec.approval_status
278 ,p_resource_id_o
279 => hxc_tbb_shd.g_old_rec.resource_id
280 ,p_resource_type_o
281 => hxc_tbb_shd.g_old_rec.resource_type
282 ,p_approval_style_id_o
283 => hxc_tbb_shd.g_old_rec.approval_style_id
284 ,p_date_from_o
285 => hxc_tbb_shd.g_old_rec.date_from
286 ,p_date_to_o
287 => hxc_tbb_shd.g_old_rec.date_to
288 ,p_comment_text_o
289 => hxc_tbb_shd.g_old_rec.comment_text
290 ,p_application_set_id_o
291 => hxc_tbb_shd.g_old_rec.application_set_id
292 ,p_translation_display_key_o
293 => hxc_tbb_shd.g_old_rec.translation_display_key
294 );
295
296 exception
297
298 when hr_api.cannot_find_prog_unit then
299 hr_api.cannot_find_prog_unit_error
300 (p_module_name => 'HXC_TIME_BUILDING_BLOCKS'
301 ,p_hook_type => 'AU');
302
303 end;
304
305 if g_debug then
306 hr_utility.set_location(' Leaving:'||l_proc, 10);
307 end if;
308
309 end post_update;
310
311 -- --------------------------------------------------------------------------
312 -- |-----------------------------< convert_defs >---------------------------|
313 -- --------------------------------------------------------------------------
314 -- {Start Of Comments}
315 --
316 -- Description:
317 -- The Convert_Defs procedure has one very important function:
318 -- It must return the record structure for the row with all system defaulted
319 -- values converted into its corresponding parameter value for update. When
320 -- we attempt to update a row through the Upd process , certain
321 -- parameters can be defaulted which enables flexibility in the calling of
322 -- the upd process (e.g. only attributes which need to be updated need to be
323 -- specified). For the upd process to determine which attributes
324 -- have NOT been specified we need to check if the parameter has a reserved
325 -- system default value. Therefore, for all parameters which have a
326 -- corresponding reserved system default mechanism specified we need to
327 -- check if a system default is being used. If a system default is being
328 -- used then we convert the defaulted value into its corresponding attribute
329 -- value held in the g_old_rec data structure.
330 --
331 -- Prerequisites:
332 -- This private function can only be called from the upd process.
333 --
334 -- In Parameters:
335 -- A Pl/Sql record structure.
336 --
337 -- Post Success:
338 -- The record structure will be returned with all system defaulted parameter
339 -- values converted into its current row attribute value.
340 --
341 -- Post Failure:
342 -- No direct error handling is required within this function. Any possible
343 -- errors within this procedure will be a PL/SQL value error due to
344 -- conversion of datatypes or data lengths.
345 --
346 -- Developer Implementation Notes:
347 -- None.
348 --
349 -- Access Status:
350 -- Internal Row Handler Use Only.
351 --
352 -- {end Of Comments}
353 -- --------------------------------------------------------------------------
354 procedure convert_defs
355 (p_rec in out nocopy hxc_tbb_shd.g_rec_type
356 ) is
357
358 begin
359
360 -- we must now examine each argument value in the
361 -- p_rec plsql record structure
362 -- to see if a system default is being used. if a system default
363 -- is being used then we must set to the 'current' argument value.
364
365 if (p_rec.type = hr_api.g_varchar2) then
366 p_rec.type :=
367 hxc_tbb_shd.g_old_rec.type;
368 end if;
369
370 if (p_rec.measure = hr_api.g_number) then
371 p_rec.measure :=
372 hxc_tbb_shd.g_old_rec.measure;
373 end if;
374
375 if (p_rec.unit_of_measure = hr_api.g_varchar2) then
376 p_rec.unit_of_measure :=
377 hxc_tbb_shd.g_old_rec.unit_of_measure;
378 end if;
379
380 if (p_rec.start_time = hr_api.g_date) then
381 p_rec.start_time :=
382 hxc_tbb_shd.g_old_rec.start_time;
383 end if;
384
385 if (p_rec.stop_time = hr_api.g_date) then
386 p_rec.stop_time :=
387 hxc_tbb_shd.g_old_rec.stop_time;
388 end if;
389
390 if (p_rec.parent_building_block_id = hr_api.g_number) then
391 p_rec.parent_building_block_id :=
392 hxc_tbb_shd.g_old_rec.parent_building_block_id;
393 end if;
394
395 if (p_rec.scope = hr_api.g_varchar2) then
396 p_rec.scope :=
397 hxc_tbb_shd.g_old_rec.scope;
398 end if;
399
400 if (p_rec.approval_status = hr_api.g_varchar2) then
401 p_rec.approval_status :=
402 hxc_tbb_shd.g_old_rec.approval_status;
403 end if;
404
405 if (p_rec.resource_id = hr_api.g_number) then
406 p_rec.resource_id :=
407 hxc_tbb_shd.g_old_rec.resource_id;
408 end if;
409
410 if (p_rec.resource_type = hr_api.g_varchar2) then
411 p_rec.resource_type :=
412 hxc_tbb_shd.g_old_rec.resource_type;
413 end if;
414
415 if (p_rec.approval_style_id = hr_api.g_number) then
416 p_rec.approval_style_id :=
417 hxc_tbb_shd.g_old_rec.approval_style_id;
418 end if;
419
420 if (p_rec.date_from = hr_api.g_date) then
421 p_rec.date_from :=
422 hxc_tbb_shd.g_old_rec.date_from;
423 end if;
424
425 if (p_rec.date_to = hr_api.g_date) then
426 p_rec.date_to :=
427 hxc_tbb_shd.g_old_rec.date_to;
428 end if;
429
433 end if;
430 if (p_rec.comment_text = hr_api.g_varchar2) then
431 p_rec.comment_text :=
432 hxc_tbb_shd.g_old_rec.comment_text;
434
435 if (p_rec.data_set_id = hr_api.g_number) then
436 p_rec.data_set_id :=
437 hxc_tbb_shd.g_old_rec.data_set_id;
438 end if;
439
440
441 end convert_defs;
442
443 -- --------------------------------------------------------------------------
444 -- |---------------------------------< upd >--------------------------------|
445 -- --------------------------------------------------------------------------
446 procedure upd
447 (p_effective_date in date
448 ,p_rec in out nocopy hxc_tbb_shd.g_rec_type
449 ) is
450
451 l_proc varchar2(72);
452
453 begin
454
455 g_debug := hr_utility.debug_enabled;
456
457 if g_debug then
458 l_proc := g_package||'upd';
459 hr_utility.set_location('Entering:'||l_proc, 5);
460 end if;
461
462 -- we must lock the row which we need to update.
463
464 hxc_tbb_shd.lck
465 (p_rec.time_building_block_id
466 ,p_rec.object_version_number
467 );
468
469 -- 1. During an update system defaults are used to determine if
470 -- arguments have been defaulted or not. We must therefore
471 -- derive the full record structure values to be updated.
472 --
473 -- 2. Call the supporting update validate operations.
474
475 convert_defs(p_rec);
476 hxc_tbb_bus.update_validate
477 (p_effective_date
478 ,p_rec
479 );
480
481 -- call the supporting pre-update operation
482
483 hxc_tbb_upd.pre_update(p_rec);
484
485 -- update the row.
486
487 hxc_tbb_upd.update_dml(p_rec);
488
489 -- call the supporting post-update operation
490
491 hxc_tbb_upd.post_update
492 (p_effective_date
493 ,p_rec
494 );
495
496 end upd;
497
498 -- --------------------------------------------------------------------------
499 -- |---------------------------------< upd >--------------------------------|
500 -- --------------------------------------------------------------------------
501 procedure upd
502 (p_effective_date in date
503 ,p_time_building_block_id in number
504 ,p_object_version_number in out nocopy number
505 ,p_approval_status in varchar2 default hr_api.g_varchar2
506 ,p_measure in number default hr_api.g_number
507 ,p_unit_of_measure in varchar2 default hr_api.g_varchar2
508 ,p_start_time in date default hr_api.g_date
509 ,p_stop_time in date default hr_api.g_date
510 ,p_parent_building_block_id in number default hr_api.g_number
511 ,p_parent_building_block_ovn in number default hr_api.g_number
512 ,p_approval_style_id in number default hr_api.g_number
513 ,p_date_from in date default hr_api.g_date
514 ,p_date_to in date default hr_api.g_date
515 ,p_comment_text in varchar2 default hr_api.g_varchar2
516 ,p_application_set_id in number default hr_api.g_number
517 ,p_data_set_id in number default hr_api.g_number
518 ,p_translation_display_key in varchar2 default hr_api.g_varchar2
519 ) is
520
521 l_rec hxc_tbb_shd.g_rec_type;
522 l_proc varchar2(72);
523
524 begin
525
526 g_debug := hr_utility.debug_enabled;
527
528 if g_debug then
529 l_proc := g_package||'upd';
530 hr_utility.set_location('Entering:'||l_proc, 5);
531 end if;
532
533 -- call conversion function to turn arguments into the
534 -- l_rec structure.
535
536 l_rec :=
537 hxc_tbb_shd.convert_args
538 (p_time_building_block_id
539 ,hr_api.g_varchar2
540 ,p_measure
541 ,p_unit_of_measure
542 ,p_start_time
543 ,p_stop_time
544 ,p_parent_building_block_id
545 ,p_parent_building_block_ovn
546 ,hr_api.g_varchar2
547 ,p_object_version_number
548 ,p_approval_status
549 ,hr_api.g_number
550 ,hr_api.g_varchar2
551 ,p_approval_style_id
552 ,null
553 ,null
554 ,p_comment_text
555 ,p_application_set_id
556 ,p_data_set_id
557 ,p_translation_display_key
558 );
559
560 -- having converted the arguments into the
561 -- plsql record structure we call the corresponding record
562 -- business process.
563
564 hxc_tbb_upd.upd
565 (p_effective_date
566 ,l_rec
567 );
568 p_object_version_number := l_rec.object_version_number;
569
570 if g_debug then
571 hr_utility.set_location(' Leaving:'||l_proc, 10);
572 end if;
573
574 end upd;
575
576 end hxc_tbb_upd;