1 Package Body pqh_tca_upd as
2 /* $Header: pqtcarhi.pkb 120.3 2011/04/28 09:35:17 sidsaxen ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_tca_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_tca_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_txn_category_attributes Row
61 --
62 update pqh_txn_category_attributes
63 set
64 txn_category_attribute_id = p_rec.txn_category_attribute_id,
65 attribute_id = p_rec.attribute_id,
66 transaction_category_id = p_rec.transaction_category_id,
67 value_set_id = p_rec.value_set_id,
68 object_version_number = p_rec.object_version_number,
69 transaction_table_route_id = p_rec.transaction_table_route_id,
70 form_column_name = p_rec.form_column_name,
71 identifier_flag = p_rec.identifier_flag,
72 list_identifying_flag = p_rec.list_identifying_flag,
73 member_identifying_flag = p_rec.member_identifying_flag,
74 refresh_flag = p_rec.refresh_flag,
75 select_flag = p_rec.select_flag,
76 value_style_cd = p_rec.value_style_cd
77 where txn_category_attribute_id = p_rec.txn_category_attribute_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_tca_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_tca_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_tca_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_tca_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_tca_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_tca_rku.after_update
187 (
188 p_txn_category_attribute_id =>p_rec.txn_category_attribute_id
189 ,p_attribute_id =>p_rec.attribute_id
190 ,p_transaction_category_id =>p_rec.transaction_category_id
191 ,p_value_set_id =>p_rec.value_set_id
192 ,p_object_version_number =>p_rec.object_version_number
193 ,p_transaction_table_route_id =>p_rec.transaction_table_route_id
194 ,p_form_column_name =>p_rec.form_column_name
195 ,p_identifier_flag =>p_rec.identifier_flag
196 ,p_list_identifying_flag =>p_rec.list_identifying_flag
197 ,p_member_identifying_flag =>p_rec.member_identifying_flag
198 ,p_refresh_flag =>p_rec.refresh_flag
199 ,p_select_flag =>p_rec.select_flag
200 ,p_value_style_cd =>p_rec.value_style_cd
201 ,p_effective_date =>p_effective_date
202 ,p_attribute_id_o =>pqh_tca_shd.g_old_rec.attribute_id
203 ,p_transaction_category_id_o =>pqh_tca_shd.g_old_rec.transaction_category_id
204 ,p_value_set_id_o =>pqh_tca_shd.g_old_rec.value_set_id
205 ,p_object_version_number_o =>pqh_tca_shd.g_old_rec.object_version_number
206 ,p_transaction_table_route_id_o =>pqh_tca_shd.g_old_rec.transaction_table_route_id
207 ,p_form_column_name_o =>pqh_tca_shd.g_old_rec.form_column_name
208 ,p_identifier_flag_o =>pqh_tca_shd.g_old_rec.identifier_flag
209 ,p_list_identifying_flag_o =>pqh_tca_shd.g_old_rec.list_identifying_flag
210 ,p_member_identifying_flag_o =>pqh_tca_shd.g_old_rec.member_identifying_flag
211 ,p_refresh_flag_o =>pqh_tca_shd.g_old_rec.refresh_flag
212 ,p_select_flag_o =>pqh_tca_shd.g_old_rec.select_flag
213 ,p_value_style_cd_o =>pqh_tca_shd.g_old_rec.value_style_cd
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_txn_category_attributes'
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
244 -- specified). For the upd process to determine which attributes
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
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_tca_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.attribute_id = hr_api.g_number) then
289 p_rec.attribute_id :=
290 pqh_tca_shd.g_old_rec.attribute_id;
291 End If;
292 If (p_rec.transaction_category_id = hr_api.g_number) then
293 p_rec.transaction_category_id :=
294 pqh_tca_shd.g_old_rec.transaction_category_id;
295 End If;
296 If (p_rec.value_set_id = hr_api.g_number) then
297 p_rec.value_set_id :=
298 pqh_tca_shd.g_old_rec.value_set_id;
299 End If;
300 If (p_rec.transaction_table_route_id = hr_api.g_number) then
301 p_rec.transaction_table_route_id :=
302 pqh_tca_shd.g_old_rec.transaction_table_route_id;
303 End If;
304 If (p_rec.form_column_name = hr_api.g_varchar2) then
305 p_rec.form_column_name :=
306 pqh_tca_shd.g_old_rec.form_column_name;
307 End If;
308 If (p_rec.identifier_flag = hr_api.g_varchar2) then
309 p_rec.identifier_flag :=
310 pqh_tca_shd.g_old_rec.identifier_flag;
311 End If;
312 If (p_rec.list_identifying_flag = hr_api.g_varchar2) then
313 p_rec.list_identifying_flag :=
314 pqh_tca_shd.g_old_rec.list_identifying_flag;
315 End If;
316 If (p_rec.member_identifying_flag = hr_api.g_varchar2) then
317 p_rec.member_identifying_flag :=
318 pqh_tca_shd.g_old_rec.member_identifying_flag;
319 End If;
320 If (p_rec.refresh_flag = hr_api.g_varchar2) then
321 p_rec.refresh_flag :=
322 pqh_tca_shd.g_old_rec.refresh_flag;
323 End If;
324 If (p_rec.select_flag = hr_api.g_varchar2) then
325 p_rec.select_flag :=
326 pqh_tca_shd.g_old_rec.select_flag;
327 End If;
328 If (p_rec.value_style_cd = hr_api.g_varchar2) then
329 p_rec.value_style_cd :=
330 pqh_tca_shd.g_old_rec.value_style_cd;
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_tca_shd.g_rec_type,
344 p_delete_attr_ranges_flag in varchar2
345 ) is
346 --
347 l_proc varchar2(72) := g_package||'upd';
348 --
349 Begin
350 hr_utility.set_location('Entering:'||l_proc, 5);
351 --
352 -- We must lock the row which we need to update.
353 --
354 pqh_tca_shd.lck
355 (
356 p_rec.txn_category_attribute_id,
357 p_rec.object_version_number
358 );
359 --
360 -- 1. During an update system defaults are used to determine if
361 -- arguments have been defaulted or not. We must therefore
362 -- derive the full record structure values to be updated.
363 --
364 -- 2. Call the supporting update validate operations.
365 --
366 convert_defs(p_rec);
367 pqh_tca_bus.update_validate(p_rec
368 ,p_effective_date,p_delete_attr_ranges_flag);
369 --
370 -- Call the supporting pre-update operation
371 --
372 pre_update(p_rec);
373 --
374 -- Update the row.
375 --
376 update_dml(p_rec);
377 --
378 -- Call the supporting post-update operation
379 --
380 post_update(
381 p_effective_date,p_rec);
382 End upd;
383 --
384 -- ----------------------------------------------------------------------------
385 -- |---------------------------------< upd >----------------------------------|
386 -- ----------------------------------------------------------------------------
387 Procedure upd
388 (
389 p_effective_date in date,
390 p_txn_category_attribute_id in number,
391 p_attribute_id in number default hr_api.g_number,
392 p_transaction_category_id in number default hr_api.g_number,
393 p_value_set_id in number default hr_api.g_number,
394 p_object_version_number in out nocopy number,
395 p_transaction_table_route_id in number default hr_api.g_number,
396 p_form_column_name in varchar2 default hr_api.g_varchar2,
397 p_identifier_flag in varchar2 default hr_api.g_varchar2,
398 p_list_identifying_flag in varchar2 default hr_api.g_varchar2,
399 p_member_identifying_flag in varchar2 default hr_api.g_varchar2,
400 p_refresh_flag in varchar2 default hr_api.g_varchar2,
401 p_select_flag in varchar2 default hr_api.g_varchar2,
402 p_value_style_cd in varchar2 default hr_api.g_varchar2
403 ,p_delete_attr_ranges_flag in varchar2 default hr_api.g_varchar2
404 ) is
405 --
406 l_rec pqh_tca_shd.g_rec_type;
407 l_proc varchar2(72) := g_package||'upd';
408 --
409 Begin
410 hr_utility.set_location('Entering:'||l_proc, 5);
411 --
412 -- Call conversion function to turn arguments into the
413 -- l_rec structure.
414 --
415 l_rec :=
416 pqh_tca_shd.convert_args
417 (
418 p_txn_category_attribute_id,
419 p_attribute_id,
420 p_transaction_category_id,
421 p_value_set_id,
422 p_object_version_number,
423 p_transaction_table_route_id,
424 p_form_column_name,
425 p_identifier_flag,
426 p_list_identifying_flag,
427 p_member_identifying_flag,
428 p_refresh_flag,
429 p_select_flag,
430 p_value_style_cd
431 );
432 --
433 -- Having converted the arguments into the
434 -- plsql record structure we call the corresponding record
435 -- business process.
436 --
437 upd(
438 p_effective_date,l_rec,p_delete_attr_ranges_flag);
439 p_object_version_number := l_rec.object_version_number;
440 --
441 hr_utility.set_location(' Leaving:'||l_proc, 10);
442 End upd;
443 --
444 end pqh_tca_upd;
[Home] [Help]