1 Package Body hr_trn_upd as
2 /* $Header: hrtrnrhi.pkb 120.2 2005/09/21 04:59:16 hpandya noship $ */
3
4 --
5 -- ----------------------------------------------------------------------------
6 -- | Private Global Definitions |
7 -- ----------------------------------------------------------------------------
8 --
9 g_package varchar2(33) := ' hr_trn_upd.'; -- Global package name
10 --
11 -- ----------------------------------------------------------------------------
12 -- |------------------------------< update_dml >------------------------------|
13 -- ----------------------------------------------------------------------------
14 -- {Start Of Comments}
15 --
16 -- Description:
17 -- This procedure controls the actual dml update logic. The processing of
18 -- this procedure is:
19 -- 1) Increment the object_version_number by 1 if the object_version_number
20 -- is defined as an attribute for this entity.
21 -- 2) To set and unset the g_api_dml sthr_trn_updatus as required (as we are about to
22 -- perform dml).
23 -- 3) To update the specified row in the schema using the primary key in
24 -- the predicates.
25 -- 4) To trap any constraint violations that may have occurred.
26 -- 5) To raise any other errors.
27 --
28 -- Pre Conditions:
29 -- This is an internal private procedure which must be called from the upd
30 -- procedure.
31 --
32 -- In Parameters:
33 -- A Pl/Sql record structre.
34 --
35 -- Post Success:
36 -- The specified row will be updated in the schema.
37 --
38 -- Post Failure:
39 -- On the update dml failure it is important to note that we always reset the
40 -- g_api_dml status to false.
41 -- If a check, unique or parent integrity constraint violation is raised the
42 -- constraint_error procedure will be called.
43 -- If any other error is reported, the error will be raised after the
44 -- g_api_dml status is reset.
45 --
46 -- Developer Implementation Notes:
47 -- The update 'set' attribute list should be modified if any of your
48 -- attributes are not updateable.
49 --
50 -- Access Status:
51 -- Internal Table Handler Use Only.
52 --
53 -- {End Of Comments}
54 -- ----------------------------------------------------------------------------
55 Procedure update_dml(p_rec in out nocopy hr_trn_shd.g_rec_type) is
56 --
57 l_proc varchar2(72) := g_package||'update_dml';
58 --
59 Begin
60 hr_utility.set_location('Entering:'||l_proc, 5);
61 --
62 --
63 hr_trn_shd.g_api_dml := true; -- Set the api dml status
64 --
65 -- Update the hr_api_transactions Row
66 --
67 update hr_api_transactions
68 set
69 transaction_id = p_rec.transaction_id,
70 creator_person_id = p_rec.creator_person_id,
71 transaction_privilege = p_rec.transaction_privilege,
72 product_code = p_rec.product_code,
73 url = p_rec.url,
74 status = p_rec.status,
75 transaction_state = p_rec.transaction_state, --ns
76 section_display_name = p_rec.section_display_name,
77 function_id = p_rec.function_id,
78 transaction_ref_table = p_rec.transaction_ref_table,
79 transaction_ref_id = p_rec.transaction_ref_id,
80 transaction_type = p_rec.transaction_type,
81 assignment_id = p_rec.assignment_id,
82 api_addtnl_info = p_rec.api_addtnl_info,
83 selected_person_id = p_rec.selected_person_id,
84 item_type = p_rec.item_type,
85 item_key = p_rec.item_key,
86 transaction_effective_date = p_rec.transaction_effective_date,
87 process_name = p_rec.process_name,
88 plan_id = p_rec.plan_id,
89 rptg_grp_id = p_rec.rptg_grp_id,
90 effective_date_option = p_rec.effective_date_option,
91 parent_transaction_id = p_rec.parent_transaction_id,
92 relaunch_function = p_rec.relaunch_function,
93 transaction_group = p_rec.transaction_group,
94 transaction_identifier = p_rec.transaction_identifier,
95 transaction_document = p_rec.transaction_document
96
97 where transaction_id = p_rec.transaction_id;
98 --
99 -- p_plan_id, p_rptg_grp_id, p_effective_date_option added by sanej
100 --
101 hr_trn_shd.g_api_dml := false; -- Unset the api dml status
102 --
103 hr_utility.set_location(' Leaving:'||l_proc, 10);
104 --
105 Exception
106 When hr_api.check_integrity_violated Then
107 -- A check constraint has been violated
108 hr_trn_shd.g_api_dml := false; -- Unset the api dml status
109 hr_trn_shd.constraint_error
110 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
111 When hr_api.parent_integrity_violated Then
112 -- Parent integrity has been violated
113 hr_trn_shd.g_api_dml := false; -- Unset the api dml status
114 hr_trn_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 hr_trn_shd.g_api_dml := false; -- Unset the api dml status
119 hr_trn_shd.constraint_error
120 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
121 When Others Then
122 hr_trn_shd.g_api_dml := false; -- Unset the api dml status
123 Raise;
124 End update_dml;
125 --
126 -- ----------------------------------------------------------------------------
127 -- |------------------------------< pre_update >------------------------------|
128 -- ----------------------------------------------------------------------------
129 -- {Start Of Comments}
130 --
131 -- Description:
132 -- This private procedure contains any processing which is required before
133 -- the update dml.
134 --
135 -- Pre Conditions:
136 -- This is an internal procedure which is called from the upd procedure.
137 --
138 -- In Parameters:
139 -- A Pl/Sql record structre.
140 --
141 -- Post Success:
142 -- Processing continues.
143 --
144 -- Post Failure:
145 -- If an error has occurred, an error message and exception will be raised
146 -- but not handled.
147 --
148 -- Developer Implementation Notes:
149 -- Any pre-processing required before the update dml is issued should be
150 -- coded within this procedure. It is important to note that any 3rd party
151 -- maintenance should be reviewed before placing in this procedure.
152 --
153 -- Access Status:
154 -- Internal Table Handler Use Only.
155 --
156 -- {End Of Comments}
157 -- ----------------------------------------------------------------------------
158 Procedure pre_update(p_rec in hr_trn_shd.g_rec_type) is
159 --
160 l_proc varchar2(72) := g_package||'pre_update';
161 --
162 Begin
163 hr_utility.set_location('Entering:'||l_proc, 5);
164 --
165 hr_utility.set_location(' Leaving:'||l_proc, 10);
166 End pre_update;
167 --
168 -- ----------------------------------------------------------------------------
169 -- |-----------------------------< post_update >------------------------------|
170 -- ----------------------------------------------------------------------------
171 -- {Start Of Comments}
172 --
173 -- Description:
174 -- This private procedure contains any processing which is required after the
175 -- update dml.
176 --
177 -- Pre Conditions:
178 -- This is an internal procedure which is called from the upd procedure.
179 --
180 -- In Parameters:
181 -- A Pl/Sql record structre.
182 --
183 -- Post Success:
184 -- Processing continues.
185 --
186 -- Post Failure:
187 -- If an error has occurred, an error message and exception will be raised
188 -- but not handled.
189 --
190 -- Developer Implementation Notes:
191 -- Any post-processing required after the update dml is issued should be
192 -- coded within this procedure. It is important to note that any 3rd party
193 -- maintenance should be reviewed before placing in this procedure.
194 --
195 -- Access Status:
196 -- Internal Table Handler Use Only.
197 --
198 -- {End Of Comments}
199 -- ----------------------------------------------------------------------------
200 Procedure post_update(p_rec in hr_trn_shd.g_rec_type) is
201 --
202 l_proc varchar2(72) := g_package||'post_update';
203 --
204 Begin
205 hr_utility.set_location('Entering:'||l_proc, 5);
206 --
207 hr_utility.set_location(' Leaving:'||l_proc, 10);
208 End post_update;
209 --
210 -- ----------------------------------------------------------------------------
211 -- |-----------------------------< convert_defs >-----------------------------|
212 -- ----------------------------------------------------------------------------
213 -- {Start Of Comments}
214 --
215 -- Description:
216 -- The Convert_Defs procedure has one very important function:
217 -- It must return the record structure for the row with all system defaulted
218 -- values converted into its corresponding parameter value for update. When
219 -- we attempt to update a row through the Upd process , certain
220 -- parameters can be defaulted which enables flexibility in the calling of
221 -- the upd process (e.g. only attributes which need to be updated need to be
222 -- specified). For the upd process to determine which attributes
223 -- have NOT been specified we need to check if the parameter has a reserved
224 -- system default value. Therefore, for all parameters which have a
225 -- corresponding reserved system default mechanism specified we need to
226 -- check if a system default is being used. If a system default is being
227 -- used then we convert the defaulted value into its corresponding attribute
228 -- value held in the g_old_rec data structure.
229 --
230 -- Pre Conditions:
231 -- This private function can only be called from the upd process.
232 --
233 -- In Parameters:
234 -- A Pl/Sql record structre.
235 --
236 -- Post Success:
237 -- The record structure will be returned with all system defaulted parameter
238 -- values converted into its current row attribute value.
239 --
240 -- Post Failure:
241 -- No direct error handling is required within this function. Any possible
242 -- errors within this procedure will be a PL/SQL value error due to conversion
243
244 -- of datatypes or data lengths.
245 --
246 -- Developer Implementation Notes:
247 -- None.
248 --
249 -- Access Status:
250 -- Internal Table Handler Use Only.
251 --
252 -- {End Of Comments}
253 -- ----------------------------------------------------------------------------
254
255 /*
256 Procedure convert_defs(p_rec in out nocopy hr_trn_shd.g_rec_type) is
257 --
258 l_proc varchar2(72) := g_package||'convert_defs';
259 --
260 Begin
261 --
262 hr_utility.set_location('Entering:'||l_proc, 5);
263 --
264 -- We must now examine each argument value in the
265 -- p_rec plsql record structure
266 -- to see if a system default is being used. If a system default
267 -- is being used then we must set to the 'current' argument value.
268 --
269 If (p_rec.creator_person_id = hr_api.g_number) then
270 p_rec.creator_person_id :=
271 hr_trn_shd.g_old_rec.creator_person_id;
272 End If;
273 If (p_rec.transaction_privilege = hr_api.g_varchar2) then
274 p_rec.transaction_privilege :=
275 hr_trn_shd.g_old_rec.transaction_privilege;
276 End If;
277 --
278 hr_utility.set_location(' Leaving:'||l_proc, 10);
279 --
280 End convert_defs;
281 */
282
283 Procedure convert_defs(p_rec in out nocopy hr_trn_shd.g_rec_type) is
284 --
285 l_proc varchar2(72) := g_package||'convert_defs';
286 --
287 Begin
288 --
289 hr_utility.set_location('Entering:'||l_proc, 5);
290 --
291 -- We must now examine each argument value in the
292 -- p_rec plsql record structure
293 -- to see if a system default is being used. If a system default
294 -- is being used then we must set to the 'current' argument value.
295 --
296 If (p_rec.creator_person_id = hr_api.g_number) then
297 p_rec.creator_person_id :=
298 hr_trn_shd.g_old_rec.creator_person_id;
299 End If;
300 If (p_rec.transaction_privilege = hr_api.g_varchar2) then
301 p_rec.transaction_privilege :=
302 hr_trn_shd.g_old_rec.transaction_privilege;
303 End If;
304 If (p_rec.product_code = hr_api.g_varchar2) then
305 p_rec.product_code :=
306 hr_trn_shd.g_old_rec.product_code;
307 End If;
308 If (p_rec.url = hr_api.g_varchar2) then
309 p_rec.url :=
310 hr_trn_shd.g_old_rec.url;
311 End If;
312 If (p_rec.status = hr_api.g_varchar2) then
313 p_rec.status :=
314 hr_trn_shd.g_old_rec.status;
315 End If;
316 If (p_rec.section_display_name = hr_api.g_varchar2) then
317 p_rec.section_display_name :=
318 hr_trn_shd.g_old_rec.section_display_name;
319 End If;
320 If (p_rec.function_id = hr_api.g_number) then
321 p_rec.function_id :=
322 hr_trn_shd.g_old_rec.function_id;
323 End If;
324 If (p_rec.transaction_ref_table = hr_api.g_varchar2) then
325 p_rec.transaction_ref_table :=
326 hr_trn_shd.g_old_rec.transaction_ref_table;
327 End If;
328 If (p_rec.transaction_ref_id = hr_api.g_number) then
329 p_rec.transaction_ref_id :=
330 hr_trn_shd.g_old_rec.transaction_ref_id;
331 End If;
332 If (p_rec.transaction_type = hr_api.g_varchar2) then
333 p_rec.transaction_type :=
334 hr_trn_shd.g_old_rec.transaction_type;
335 End If;
336 If (p_rec.assignment_id = hr_api.g_number) then
337 p_rec.assignment_id :=
338 hr_trn_shd.g_old_rec.assignment_id;
339 End If;
340 If (p_rec.api_addtnl_info = hr_api.g_varchar2) then
341 p_rec.api_addtnl_info :=
342 hr_trn_shd.g_old_rec.api_addtnl_info;
343 End If;
344 If (p_rec.selected_person_id = hr_api.g_number) then
345 p_rec.selected_person_id :=
346 hr_trn_shd.g_old_rec.selected_person_id;
347 End If;
348 If (p_rec.item_type = hr_api.g_varchar2) then
349 p_rec.item_type :=
350 hr_trn_shd.g_old_rec.item_type;
351 End If;
352 If (p_rec.item_key = hr_api.g_varchar2) then
353 p_rec.item_key :=
354 hr_trn_shd.g_old_rec.item_key;
355 End If;
356 If (p_rec.transaction_effective_date = hr_api.g_date) then
357 p_rec.transaction_effective_date :=
358 hr_trn_shd.g_old_rec.transaction_effective_date;
359 End If;
360 If (p_rec.process_name = hr_api.g_varchar2) then
361 p_rec.process_name :=
362 hr_trn_shd.g_old_rec.process_name;
363 End If;
364 If (p_rec.plan_id = hr_api.g_number) then
365 p_rec.plan_id :=
366 hr_trn_shd.g_old_rec.plan_id;
367 End If;
368 If (p_rec.rptg_grp_id = hr_api.g_number) then
369 p_rec.rptg_grp_id :=
370 hr_trn_shd.g_old_rec.rptg_grp_id;
371 End If;
372 If (p_rec.effective_date_option = hr_api.g_varchar2) then
373 p_rec.effective_date_option :=
374 hr_trn_shd.g_old_rec.effective_date_option;
375 End If;
376 If (p_rec.parent_transaction_id = hr_api.g_number) then
377 p_rec.parent_transaction_id :=
378 hr_trn_shd.g_old_rec.parent_transaction_id;
379 End If;
380 If (p_rec.relaunch_function = hr_api.g_varchar2) then
381 p_rec.relaunch_function :=
382 hr_trn_shd.g_old_rec.relaunch_function;
383 End If;
384 If (p_rec.transaction_group = hr_api.g_varchar2) then
385 p_rec.transaction_group :=
386 hr_trn_shd.g_old_rec.transaction_group;
387 End If;
388 If (p_rec.transaction_identifier = hr_api.g_varchar2) then
389 p_rec.transaction_identifier :=
390 hr_trn_shd.g_old_rec.transaction_identifier;
391 End If;
392
393 -- If the new value is null then set it to its original value.
394 If (p_rec.transaction_document is null) then
395 p_rec.transaction_document :=
396 hr_trn_shd.g_old_rec.transaction_document;
397 End If;
398 --
399 -- plan_id, rptg_grp_id, effective_date_option added by sanej
400 --
401 --ns start
402 -- Set the transaction state to wip if it's not a new transaction
403 IF (p_rec.transaction_state = hr_api.g_varchar2 ) THEN
404 p_rec.transaction_state := hr_trn_shd.g_old_rec.transaction_state;
405 END IF;
406 --ns end
407 --
408
409 hr_utility.set_location(' Leaving:'||l_proc, 10);
410 --
411 End convert_defs;
412
413 --
414 -- ----------------------------------------------------------------------------
415 -- |---------------------------------< upd >----------------------------------|
416 -- ----------------------------------------------------------------------------
417 Procedure upd
418 (
419 p_rec in out nocopy hr_trn_shd.g_rec_type,
420 p_validate in boolean default false
421 ) is
422 --
423 l_proc varchar2(72) := g_package||'upd';
424 --
425 Begin
426 hr_utility.set_location('Entering:'||l_proc, 5);
427 --
428 -- Determine if the business process is to be validated.
429 --
430 If p_validate then
431 --
432 -- Issue the savepoint.
433 --
434 SAVEPOINT upd_hr_trn;
435 End If;
436 --
437 -- We must lock the row which we need to update.
438 --
439 hr_trn_shd.lck
440 (
441 p_rec.transaction_id
442 );
443 --
444 -- 1. During an update system defaults are used to determine if
445 -- arguments have been defaulted or not. We must therefore
446 -- derive the full record structure values to be updated.
447 --
448 -- 2. Call the supporting update validate operations.
449 --
450 convert_defs(p_rec);
451 hr_trn_bus.update_validate(p_rec);
452 --
453 -- Call the supporting pre-update operation
454 --
455 pre_update(p_rec);
456 --
457 -- Update the row.
458 --
459 update_dml(p_rec);
460 --
461 -- Call the supporting post-update operation
462 --
463 post_update(p_rec);
464 --
465 -- If we are validating then raise the Validate_Enabled exception
466 --
467 If p_validate then
468 Raise HR_Api.Validate_Enabled;
469 End If;
470 --
471 hr_utility.set_location(' Leaving:'||l_proc, 10);
472 Exception
473 When HR_Api.Validate_Enabled Then
474 --
475 -- As the Validate_Enabled exception has been raised
476 -- we must rollback to the savepoint
477 --
478 ROLLBACK TO upd_hr_trn;
479 End upd;
480 --
481 -- ----------------------------------------------------------------------------
482 -- |---------------------------------< upd >----------------------------------|
483 -- ----------------------------------------------------------------------------
484 Procedure upd
485 (
486 p_transaction_id in number,
487 p_creator_person_id in number default hr_api.g_number,
488 p_transaction_privilege in varchar2 default hr_api.g_varchar2,
489 p_validate in boolean default false
490 ) is
491 --
492 l_rec hr_trn_shd.g_rec_type;
493 l_proc varchar2(72) := g_package||'upd';
494 --
495 Begin
496 hr_utility.set_location('Entering:'||l_proc, 5);
497 --
498 -- Call conversion function to turn arguments into the
499 -- l_rec structure.
500 --
501 l_rec :=
502 hr_trn_shd.convert_args
503 (
504 p_transaction_id,
505 p_creator_person_id,
506 p_transaction_privilege
507 );
508 --
509 -- Having converted the arguments into the
510 -- plsql record structure we call the corresponding record
511 -- business process.
512 --
513 upd(l_rec, p_validate);
514 --
515 --
516 hr_utility.set_location(' Leaving:'||l_proc, 10);
517 End upd;
518 --
519 -- ----------------------------------------------------------------------------
520 -- |---------------------------------< upd >----------------------------------|
521 -- ----------------------------------------------------------------------------
522 Procedure upd
523 (
524 p_transaction_id in number,
525 p_creator_person_id in number default hr_api.g_number,
526 p_transaction_privilege in varchar2 default hr_api.g_varchar2,
527 p_validate in boolean default false,
528 p_product_code in varchar2 default hr_api.g_varchar2,
529 p_url in varchar2 default hr_api.g_varchar2,
530 p_status in varchar2,
531 p_transaction_state in varchar2 default hr_api.g_varchar2, --ns
532 p_section_display_name in varchar2 default hr_api.g_varchar2,
533 p_function_id in number default hr_api.g_number,
534 p_transaction_ref_table in varchar2 default hr_api.g_varchar2,
535 p_transaction_ref_id in number default hr_api.g_number,
536 p_transaction_type in varchar2 default hr_api.g_varchar2,
537 p_assignment_id in number default hr_api.g_number,
538 p_api_addtnl_info in varchar2 default hr_api.g_varchar2,
539 p_selected_person_id in number default hr_api.g_number,
540 p_item_type in varchar2 default hr_api.g_varchar2,
541 p_item_key in varchar2 default hr_api.g_varchar2,
542 p_transaction_effective_date in date default hr_api.g_date,
543 p_process_name in varchar2 default hr_api.g_varchar2,
544 p_plan_id in number default hr_api.g_number,
545 p_rptg_grp_id in number default hr_api.g_number,
546 p_effective_date_option in varchar2 default hr_api.g_varchar2,
547 p_creator_role in varchar2 default hr_api.g_varchar2,
548 p_last_update_role in varchar2 default hr_api.g_varchar2,
549 p_parent_transaction_id in number default hr_api.g_number,
550 p_relaunch_function in varchar2 default hr_api.g_varchar2,
551 p_transaction_group in varchar2 default hr_api.g_varchar2,
552 p_transaction_identifier in varchar2 default hr_api.g_varchar2,
553 p_transaction_document in clob default NULL
554 ) is
555 --
556 -- p_plan_id, p_rptg_grp_id, p_effective_date_option added by sanej
557 --
558 l_rec hr_trn_shd.g_rec_type;
559 l_proc varchar2(72) := g_package||'upd';
560 --
561 Begin
562 hr_utility.set_location('Entering:'||l_proc, 5);
563 --
564 -- Call conversion function to turn arguments into the
565 -- l_rec structure.
566 --
567
568
569 l_rec :=
570 hr_trn_shd.convert_args
571 (
572 p_transaction_id,
573 p_creator_person_id,
574 p_transaction_privilege,
575 p_product_code,
576 p_url,
577 p_status,
578 p_transaction_state, --ns
579 p_section_display_name,
580 p_function_id,
581 p_transaction_ref_table,
582 p_transaction_ref_id,
583 p_transaction_type,
584 p_assignment_id,
585 p_api_addtnl_info,
586 p_selected_person_id,
587 p_item_type,
588 p_item_key,
589 p_transaction_effective_date,
590 p_process_name,
591 p_plan_id,
592 p_rptg_grp_id,
593 p_effective_date_option,
594 p_creator_role,
595 p_last_update_role,
596 p_parent_transaction_id,
597 p_relaunch_function,
598 p_transaction_group,
599 p_transaction_identifier,
600 p_transaction_document
601 );
602 --
603 -- p_plan_id, p_rptg_grp_id, p_effective_date_option added by sanej
604 --
605 -- Having converted the arguments into the
606 -- plsql record structure we call the corresponding record
607 -- business process.
608 --
609 upd(l_rec, p_validate);
610 --
611 --
612 hr_utility.set_location(' Leaving:'||l_proc, 10);
613 End upd;
614 --
615
616 end hr_trn_upd;