1 Package body hxc_tkgq_upd as
2 /* $Header: hxctkgqrhi.pkb 120.2 2005/09/23 09:33:26 rchennur noship $ */
3 --
4 g_package varchar2(33) := ' hxc_tkgq_upd.'; -- Global package name
5 g_debug boolean := hr_utility.debug_enabled;
6 -- ----------------------------------------------------------------------------
7 -- |------------------------------< update_dml >------------------------------|
8 -- ----------------------------------------------------------------------------
9 --
10 -- Description:
11 -- This procedure controls the actual dml update logic. The processing of
12 -- this procedure is:
13 -- 1) Increment the object_version_number by 1 if the object_version_number
14 -- is defined as an attribute for this entity.
15 -- 2) To set and unset the g_api_dml status as required (as we are about to
16 -- perform dml).
17 -- 3) To update the specified row in the schema using the primary key in
18 -- the predicates.
19 -- 4) To trap any constraint violations that may have occurred.
20 -- 5) To raise any other errors.
21 --
22 -- Prerequisites:
23 -- This is an internal private procedure which must be called from the upd
24 -- procedure.
25 --
26 -- In Parameters:
27 -- A Pl/Sql record structre.
28 --
29 -- Post Success:
30 -- The specified row will be updated in the schema.
31 --
32 -- Post Failure:
33 -- On the update dml failure it is important to note that we always reset the
34 -- g_api_dml status to false.
35 -- If a check, unique or parent integrity constraint violation is raised the
36 -- constraint_error procedure will be called.
37 -- If any other error is reported, the error will be raised after the
38 -- g_api_dml status is reset.
39 --
40 -- Developer Implementation Notes:
41 -- The update 'set' attribute list should be modified if any of your
42 -- attributes are not updateable.
43 --
44 -- Access Status:
45 -- Internal Row Handler Use Only.
46 --
47 -- ----------------------------------------------------------------------------
48 Procedure update_dml
49 (p_rec in out nocopy hxc_tkgq_shd.g_rec_type
50 ) is
51 --
52 l_proc varchar2(72) ;
53 --
54 Begin
55
56 if g_debug then
57 l_proc := g_package||'update_dml';
58 hr_utility.set_location('Entering:'||l_proc, 5);
59 end if;
60 --
61 -- Increment the object version
62 p_rec.object_version_number := p_rec.object_version_number + 1;
63 --
64 hxc_tkgq_shd.g_api_dml := true; -- Set the api dml status
65 --
66 -- Update the hxc_tk_group_queries Row
67 --
68 update hxc_tk_group_queries
69 set
70 tk_group_query_id = p_rec.tk_group_query_id
71 ,tk_group_id = p_rec.tk_group_id
72 ,group_query_name = p_rec.group_query_name
73 ,include_exclude = p_rec.include_exclude
74 ,system_user = p_rec.system_user
75 ,object_version_number = p_rec.object_version_number
76 ,last_updated_by = fnd_global.user_id
77 ,last_update_date = sysdate
78 ,last_update_login = fnd_global.login_id
79
80 where tk_group_query_id = p_rec.tk_group_query_id;
81 --
82 hxc_tkgq_shd.g_api_dml := false; -- Unset the api dml status
83 --
84 if g_debug then
85 hr_utility.set_location(' Leaving:'||l_proc, 10);
86 end if;
87 --
88 Exception
89 When hr_api.check_integrity_violated Then
90 -- A check constraint has been violated
91 hxc_tkgq_shd.g_api_dml := false; -- Unset the api dml status
92 hxc_tkgq_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 hxc_tkgq_shd.g_api_dml := false; -- Unset the api dml status
97 hxc_tkgq_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 hxc_tkgq_shd.g_api_dml := false; -- Unset the api dml status
102 hxc_tkgq_shd.constraint_error
103 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
104 When Others Then
105 hxc_tkgq_shd.g_api_dml := false; -- Unset the api dml status
106 Raise;
107 End update_dml;
108 --
109 -- ----------------------------------------------------------------------------
110 -- |------------------------------< pre_update >------------------------------|
111 -- ----------------------------------------------------------------------------
112 --
113 -- Description:
114 -- This private procedure contains any processing which is required before
115 -- the update dml.
116 --
117 -- Prerequisites:
118 -- This is an internal procedure which is called from the upd procedure.
119 --
120 -- In Parameters:
121 -- A Pl/Sql record structure.
122 --
123 -- Post Success:
124 -- Processing continues.
125 --
126 -- Post Failure:
127 -- If an error has occurred, an error message and exception wil be raised
128 -- but not handled.
129 --
130 -- Developer Implementation Notes:
131 -- Any pre-processing required before the update dml is issued should be
132 -- coded within this procedure. It is important to note that any 3rd party
133 -- maintenance should be reviewed before placing in this procedure.
134 --
135 -- Access Status:
136 -- Internal Row Handler Use Only.
137 --
138 -- ----------------------------------------------------------------------------
139 Procedure pre_update
140 (p_rec in hxc_tkgq_shd.g_rec_type
141 ) is
142 --
143 l_proc varchar2(72) ;
144 --
145 Begin
146
147 if g_debug then
148 l_proc := g_package||'pre_update';
149 hr_utility.set_location('Entering:'||l_proc, 5);
150 hr_utility.set_location(' Leaving:'||l_proc, 10);
151 end if;
152 End pre_update;
153 --
154 -- ----------------------------------------------------------------------------
155 -- |-----------------------------< post_update >------------------------------|
156 -- ----------------------------------------------------------------------------
157 --
158 -- Description:
159 -- This private procedure contains any processing which is required after the
160 -- 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 -- ----------------------------------------------------------------------------
184 Procedure post_update
185 (p_rec in hxc_tkgq_shd.g_rec_type
186 ) is
187 --
188 l_proc varchar2(72) ;
189 --
190 Begin
191
192 if g_debug then
193 l_proc := g_package||'post_update';
194 hr_utility.set_location('Entering:'||l_proc, 5);
195 end if;
196 begin
197 --
198 hxc_tkgq_rku.after_update
199 (p_tk_group_query_id
200 => p_rec.tk_group_query_id
201 ,p_tk_group_id
202 => p_rec.tk_group_id
203 ,p_group_query_name
204 => p_rec.group_query_name
205 ,p_include_exclude
206 => p_rec.include_exclude
207 ,p_system_user
208 => p_rec.system_user
209 ,p_object_version_number
210 => p_rec.object_version_number
211 ,p_tk_group_id_o
212 => hxc_tkgq_shd.g_old_rec.tk_group_id
213 ,p_group_query_name_o
214 => hxc_tkgq_shd.g_old_rec.group_query_name
215 ,p_include_exclude_o
216 => hxc_tkgq_shd.g_old_rec.include_exclude
217 ,p_system_user_o
218 => hxc_tkgq_shd.g_old_rec.system_user
219 ,p_object_version_number_o
220 => hxc_tkgq_shd.g_old_rec.object_version_number
221 );
222 --
223 exception
224 --
225 when hr_api.cannot_find_prog_unit then
226 --
227 hr_api.cannot_find_prog_unit_error
228 (p_module_name => 'HXC_TK_GROUP_QUERY'
229 ,p_hook_type => 'AU');
230 --
231 end;
232 --
233 if g_debug then
234 hr_utility.set_location(' Leaving:'||l_proc, 10);
235 end if;
236 End post_update;
237 --
238 -- ----------------------------------------------------------------------------
239 -- |-----------------------------< convert_defs >-----------------------------|
240 -- ----------------------------------------------------------------------------
241 --
242 -- Description:
243 -- The Convert_Defs procedure has one very important function:
244 -- It must return the record structure for the row with all system defaulted
245 -- values converted into its corresponding parameter value for update. When
246 -- we attempt to update a row through the Upd process , certain
247 -- parameters can be defaulted which enables flexibility in the calling of
248 -- the upd process (e.g. only attributes which need to be updated need to be
249 -- specified). For the upd process to determine which attributes
250 -- have NOT been specified we need to check if the parameter has a reserved
251 -- system default value. Therefore, for all parameters which have a
252 -- corresponding reserved system default mechanism specified we need to
253 -- check if a system default is being used. If a system default is being
254 -- used then we convert the defaulted value into its corresponding attribute
255 -- value held in the g_old_rec data structure.
256 --
257 -- Prerequisites:
258 -- This private function can only be called from the upd process.
259 --
260 -- In Parameters:
261 -- A Pl/Sql record structure.
262 --
263 -- Post Success:
264 -- The record structure will be returned with all system defaulted parameter
265 -- values converted into its current row attribute value.
266 --
267 -- Post Failure:
268 -- No direct error handling is required within this function. Any possible
269 -- errors within this procedure will be a PL/SQL value error due to
270 -- conversion of datatypes or data lengths.
271 --
272 -- Developer Implementation Notes:
273 -- None.
274 --
275 -- Access Status:
276 -- Internal Row Handler Use Only.
277 --
278 -- ----------------------------------------------------------------------------
279 Procedure convert_defs
280 (p_rec in out nocopy hxc_tkgq_shd.g_rec_type
281 ) is
282 --
283 Begin
284 --
285 -- We must now examine each argument value in the
286 -- p_rec plsql record structure
287 -- to see if a system default is being used. If a system default
288 -- is being used then we must set to the 'current' argument value.
289 --
290 If (p_rec.group_query_name = hr_api.g_varchar2) then
291 p_rec.group_query_name :=
292 hxc_tkgq_shd.g_old_rec.group_query_name;
293 End If;
294 If (p_rec.tk_group_id = hr_api.g_number) then
295 p_rec.tk_group_id :=
296 hxc_tkgq_shd.g_old_rec.tk_group_id;
297 End If;
298 If (p_rec.include_exclude = hr_api.g_varchar2) then
299 p_rec.include_exclude :=
300 hxc_tkgq_shd.g_old_rec.include_exclude;
301 End If;
302 If (p_rec.system_user = hr_api.g_varchar2) then
303 p_rec.system_user :=
304 hxc_tkgq_shd.g_old_rec.system_user;
305 End If;
306
307 --
308 End convert_defs;
309 -- ----------------------------------------------------------------------------
310 -- |----------------------------------< upd >---------------------------------|
311 -- ----------------------------------------------------------------------------
312 --
313 -- Description:
314 -- This procedure is the record interface for the update
315 -- process for the specified entity. The role of this process is
316 -- to update a fully validated row for the HR schema passing back
317 -- to the calling process, any system generated values (e.g.
318 -- object version number attribute). This process is the main
319 -- backbone of the upd business process. The processing of this
320 -- procedure is as follows:
321 -- 1) The row to be updated is locked and selected into the record
322 -- structure g_old_rec.
323 -- 2) Because on update parameters which are not part of the update do not
324 -- have to be defaulted, we need to build up the updated row by
325 -- converting any system defaulted parameters to their corresponding
326 -- value.
327 -- 3) The controlling validation process update_validate is then executed
328 -- which will execute all private and public validation business rule
329 -- processes.
330 -- 4) The pre_update process is then executed which enables any
331 -- logic to be processed before the update dml process is executed.
332 -- 5) The update_dml process will physical perform the update dml into the
333 -- specified entity.
334 -- 6) The post_update process is then executed which enables any
335 -- logic to be processed after the update dml process.
336 --
337 -- Prerequisites:
338 -- The main parameters to the business process have to be in the record
339 -- format.
340 --
341 -- In Parameters:
342 --
343 -- Post Success:
344 -- The specified row will be fully validated and updated for the specified
345 -- entity without being committed.
346 --
347 -- Post Failure:
348 -- If an error has occurred, an error message will be raised.
349 --
350 -- Developer Implementation Notes:
351 -- None.
352 --
353 -- Access Status:
354 -- Internal Development Use Only.
355 --
356 -- ----------------------------------------------------------------------------
357 Procedure upd
358 (p_rec in out nocopy hxc_tkgq_shd.g_rec_type
359 ) is
360 --
361 l_proc varchar2(72) ;
362 --
363 Begin
364 g_debug :=hr_utility.debug_enabled;
365 if g_debug then
366 l_proc := g_package||'upd';
367 hr_utility.set_location('Entering:'||l_proc, 5);
368 end if;
369 --
370 -- We must lock the row which we need to update.
371 --
372 hxc_tkgq_shd.lck
373 (p_rec.tk_group_query_id
374 ,p_rec.object_version_number
375 );
376 --
377 -- 1. During an update system defaults are used to determine if
378 -- arguments have been defaulted or not. We must therefore
379 -- derive the full record structure values to be updated.
380 --
381 -- 2. Call the supporting update validate operations.
382 --
383 convert_defs(p_rec);
384 hxc_tkgq_bus.update_validate
385 (p_rec
386 );
387 --
388 -- Call the supporting pre-update operation
389 --
390 hxc_tkgq_upd.pre_update(p_rec);
391 --
392 -- Update the row.
393 --
394 hxc_tkgq_upd.update_dml(p_rec);
395 --
396 -- Call the supporting post-update operation
397 --
398 hxc_tkgq_upd.post_update
399 (p_rec
400 );
401 End upd;
402 --
403 -- ----------------------------------------------------------------------------
404 -- |---------------------------------< upd >----------------------------------|
405 -- ----------------------------------------------------------------------------
406 --
407 -- Description:
408 -- This procedure is the attribute interface for the update
409 -- process for the specified entity and is the outermost layer. The role
410 -- of this process is to update a fully validated row into the HR schema
411 -- passing back to the calling process, any system generated values
412 -- (e.g. object version number attributes). The processing of this
413 -- procedure is as follows:
414 -- 1) The attributes are converted into a local record structure by
415 -- calling the convert_args function.
416 -- 2) After the conversion has taken place, the corresponding record upd
417 -- interface process is executed.
418 -- 3) OUT parameters are then set to their corresponding record attributes.
419 --
420 -- Prerequisites:
421 --
422 -- In Parameters:
423 --
424 -- Post Success:
425 -- A fully validated row will be updated for the specified entity
426 -- without being committed.
427 --
428 -- Post Failure:
429 -- If an error has occurred, an error message will be raised.
430 --
431 -- Developer Implementation Notes:
432 -- None.
433 --
434 -- Access Status:
435 -- Internal Development Use Only.
436 --
437 -- ----------------------------------------------------------------------------
438 Procedure upd
439 (p_tk_group_query_id in number
440 ,p_tk_group_id in number
441 ,p_object_version_number in out nocopy number
442 ,p_group_query_name in varchar2
443 ,p_include_exclude in varchar2
444 ,p_system_user in varchar2
445 ) is
446 --
447 l_rec hxc_tkgq_shd.g_rec_type;
448 l_proc varchar2(72) ;
449 --
450 Begin
451 g_debug :=hr_utility.debug_enabled;
452 if g_debug then
453 l_proc := g_package||'upd';
454 hr_utility.set_location('Entering:'||l_proc, 5);
455 end if;
456 --
457 -- Call conversion function to turn arguments into the
458 -- l_rec structure.
459 --
460 l_rec :=
461 hxc_tkgq_shd.convert_args
462 (p_tk_group_query_id
463 ,p_tk_group_id
464 ,p_group_query_name
465 ,p_include_exclude
466 ,p_system_user
467 ,p_object_version_number
468 );
469 --
470 -- Having converted the arguments into the
471 -- plsql record structure we call the corresponding record
472 -- business process.
473 --
474 hxc_tkgq_upd.upd
475 (l_rec
476 );
477 p_object_version_number := l_rec.object_version_number;
478 --
479 if g_debug then
480 hr_utility.set_location(' Leaving:'||l_proc, 10);
481 end if;
482 End upd;
483 --
484 end hxc_tkgq_upd;