1 Package Body pqh_sat_upd as
2 /* $Header: pqsatrhi.pkb 120.2 2005/10/12 20:19:29 srajakum noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_sat_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 -- perform dml).
21 -- 3) To update the specified row in the schema using the primary key in
22 -- the predicates.
23 -- 4) To trap any constraint violations that may have occurred.
24 -- 5) To raise any other errors.
25 --
26 -- Prerequisites:
27 -- This is an internal private procedure which must be called from the upd
28 -- procedure.
29 --
30 -- In Parameters:
31 -- A Pl/Sql record structre.
32 --
33 -- Post Success:
34 -- The specified row will be updated in the schema.
35 --
36 -- Post Failure:
37 -- On the update dml failure it is important to note that we always reset the
38 -- If a check, unique or parent integrity constraint violation is raised the
39 -- constraint_error procedure will be called.
40 -- If any other error is reported, the error will be raised after the
41 --
42 -- Developer Implementation Notes:
43 -- The update 'set' attribute list should be modified if any of your
44 -- attributes are not updateable.
45 --
46 -- Access Status:
47 -- Internal Row Handler Use Only.
48 --
49 -- {End Of Comments}
50 -- ----------------------------------------------------------------------------
51 Procedure update_dml(p_rec in out nocopy pqh_sat_shd.g_rec_type) is
52 --
53 l_proc varchar2(72) := g_package||'update_dml';
54 --
55 Begin
56 hr_utility.set_location('Entering:'||l_proc, 5);
57 --
58 -- Increment the object version
59 --
60 p_rec.object_version_number := p_rec.object_version_number + 1;
61 --
62 --
63 -- Update the pqh_special_attributes Row
64 --
65 update pqh_special_attributes
66 set
67 special_attribute_id = p_rec.special_attribute_id,
68 txn_category_attribute_id = p_rec.txn_category_attribute_id,
69 attribute_type_cd = p_rec.attribute_type_cd,
70 key_attribute_type = p_rec.key_attribute_type,
71 enable_flag = p_rec.enable_flag,
72 flex_code = p_rec.flex_code,
73 object_version_number = p_rec.object_version_number,
74 ddf_column_name = p_rec.ddf_column_name,
75 ddf_value_column_name = p_rec.ddf_value_column_name,
76 context = p_rec.context
77 where special_attribute_id = p_rec.special_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_sat_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_sat_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_sat_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_sat_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_sat_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_sat_rku.after_update
187 (
188 p_special_attribute_id =>p_rec.special_attribute_id
189 ,p_txn_category_attribute_id =>p_rec.txn_category_attribute_id
190 ,p_attribute_type_cd =>p_rec.attribute_type_cd
191 ,p_key_attribute_type =>p_rec.key_attribute_type
192 ,p_enable_flag =>p_rec.enable_flag
193 ,p_flex_code =>p_rec.flex_code
194 ,p_object_version_number =>p_rec.object_version_number
195 ,p_ddf_column_name =>p_rec.ddf_column_name
196 ,p_ddf_value_column_name =>p_rec.ddf_value_column_name
197 ,p_context =>p_rec.context
198 ,p_effective_date =>p_effective_date
199 ,p_txn_category_attribute_id_o =>pqh_sat_shd.g_old_rec.txn_category_attribute_id
200 ,p_attribute_type_cd_o =>pqh_sat_shd.g_old_rec.attribute_type_cd
201 ,p_key_attribute_type_o =>pqh_sat_shd.g_old_rec.key_attribute_type
202 ,p_enable_flag_o =>pqh_sat_shd.g_old_rec.enable_flag
203 ,p_flex_code_o =>pqh_sat_shd.g_old_rec.flex_code
204 ,p_object_version_number_o =>pqh_sat_shd.g_old_rec.object_version_number
205 ,p_ddf_column_name_o =>pqh_sat_shd.g_old_rec.ddf_column_name
206 ,p_ddf_value_column_name_o =>pqh_sat_shd.g_old_rec.ddf_value_column_name
207 ,p_context_o =>pqh_sat_shd.g_old_rec.context
208 );
209 --
210 exception
211 --
212 when hr_api.cannot_find_prog_unit then
213 --
214 hr_api.cannot_find_prog_unit_error
215 (p_module_name => 'pqh_special_attributes'
216 ,p_hook_type => 'AU');
217 --
218 end;
219 --
220 -- End of API User Hook for post_update.
221 --
222 --
223 hr_utility.set_location(' Leaving:'||l_proc, 10);
224 End post_update;
225 --
226 -- ----------------------------------------------------------------------------
227 -- |-----------------------------< convert_defs >-----------------------------|
228 -- ----------------------------------------------------------------------------
229 -- {Start Of Comments}
230 --
231 -- Description:
232 -- The Convert_Defs procedure has one very important function:
233 -- It must return the record structure for the row with all system defaulted
234 -- values converted into its corresponding parameter value for update. When
235 -- we attempt to update a row through the Upd process , certain
236 -- parameters can be defaulted which enables flexibility in the calling of
237 -- the upd process (e.g. only attributes which need to be updated need to be
238 -- specified). For the upd process to determine which attributes
239 -- have NOT been specified we need to check if the parameter has a reserved
240 -- system default value. Therefore, for all parameters which have a
241 -- corresponding reserved system default mechanism specified we need to
242 -- check if a system default is being used. If a system default is being
243 -- used then we convert the defaulted value into its corresponding attribute
244 -- value held in the g_old_rec data structure.
245 --
246 -- Prerequisites:
247 -- This private function can only be called from the upd process.
248 --
249 -- In Parameters:
250 -- A Pl/Sql record structre.
251 --
252 -- Post Success:
253 -- The record structure will be returned with all system defaulted parameter
254 -- values converted into its current row attribute value.
255 --
256 -- Post Failure:
257 -- No direct error handling is required within this function. Any possible
258 -- errors within this procedure will be a PL/SQL value error due to conversion
259 -- of datatypes or data lengths.
260 --
261 -- Developer Implementation Notes:
262 -- None.
263 --
264 -- Access Status:
265 -- Internal Row Handler Use Only.
266 --
267 -- {End Of Comments}
268 -- ----------------------------------------------------------------------------
269 Procedure convert_defs(p_rec in out nocopy pqh_sat_shd.g_rec_type) is
270 --
271 l_proc varchar2(72) := g_package||'convert_defs';
272 --
273 Begin
274 --
275 hr_utility.set_location('Entering:'||l_proc, 5);
276 --
277 -- We must now examine each argument value in the
278 -- p_rec plsql record structure
279 -- to see if a system default is being used. If a system default
280 -- is being used then we must set to the 'current' argument value.
281 --
282 If (p_rec.txn_category_attribute_id = hr_api.g_number) then
283 p_rec.txn_category_attribute_id :=
284 pqh_sat_shd.g_old_rec.txn_category_attribute_id;
285 End If;
286 If (p_rec.attribute_type_cd = hr_api.g_varchar2) then
287 p_rec.attribute_type_cd :=
288 pqh_sat_shd.g_old_rec.attribute_type_cd;
289 End If;
290 If (p_rec.key_attribute_type = hr_api.g_varchar2) then
291 p_rec.key_attribute_type :=
292 pqh_sat_shd.g_old_rec.key_attribute_type;
293 End If;
294 If (p_rec.enable_flag = hr_api.g_varchar2) then
295 p_rec.enable_flag :=
296 pqh_sat_shd.g_old_rec.enable_flag;
297 End If;
298 If (p_rec.flex_code = hr_api.g_varchar2) then
299 p_rec.flex_code :=
300 pqh_sat_shd.g_old_rec.flex_code;
301 End If;
302 If (p_rec.ddf_column_name = hr_api.g_varchar2) then
303 p_rec.ddf_column_name :=
304 pqh_sat_shd.g_old_rec.ddf_column_name;
305 End If;
306 If (p_rec.ddf_value_column_name = hr_api.g_varchar2) then
307 p_rec.ddf_value_column_name :=
308 pqh_sat_shd.g_old_rec.ddf_value_column_name;
309 End If;
310 If (p_rec.context = hr_api.g_varchar2) then
311 p_rec.context :=
312 pqh_sat_shd.g_old_rec.context;
313 End If;
314 --
315 hr_utility.set_location(' Leaving:'||l_proc, 10);
316 --
317 End convert_defs;
318 --
319 -- ----------------------------------------------------------------------------
320 -- |---------------------------------< upd >----------------------------------|
321 -- ----------------------------------------------------------------------------
322 Procedure upd
323 (
324 p_effective_date in date,
325 p_rec in out nocopy pqh_sat_shd.g_rec_type
326 ) is
327 --
328 l_proc varchar2(72) := g_package||'upd';
329 --
330 Begin
331 hr_utility.set_location('Entering:'||l_proc, 5);
332 --
333 -- We must lock the row which we need to update.
334 --
335 pqh_sat_shd.lck
336 (
337 p_rec.special_attribute_id,
338 p_rec.object_version_number
339 );
340 --
341 -- 1. During an update system defaults are used to determine if
342 -- arguments have been defaulted or not. We must therefore
343 -- derive the full record structure values to be updated.
344 --
345 -- 2. Call the supporting update validate operations.
346 --
347 convert_defs(p_rec);
348 pqh_sat_bus.update_validate(p_rec
349 ,p_effective_date);
350 --
351 -- Call the supporting pre-update operation
352 --
353 pre_update(p_rec);
354 --
355 -- Update the row.
356 --
357 update_dml(p_rec);
358 --
359 -- Call the supporting post-update operation
360 --
361 post_update(
362 p_effective_date,p_rec);
363 End upd;
364 --
365 -- ----------------------------------------------------------------------------
366 -- |---------------------------------< upd >----------------------------------|
367 -- ----------------------------------------------------------------------------
368 Procedure upd
369 (
370 p_effective_date in date,
371 p_special_attribute_id in number,
372 p_txn_category_attribute_id in number default hr_api.g_number,
373 p_attribute_type_cd in varchar2 default hr_api.g_varchar2,
374 p_key_attribute_type in varchar2 default hr_api.g_varchar2,
375 p_enable_flag in varchar2 default hr_api.g_varchar2,
376 p_flex_code in varchar2 default hr_api.g_varchar2,
377 p_object_version_number in out nocopy number,
378 p_ddf_column_name in varchar2 default hr_api.g_varchar2,
379 p_ddf_value_column_name in varchar2 default hr_api.g_varchar2,
380 p_context in varchar2 default hr_api.g_varchar2
381 ) is
382 --
383 l_rec pqh_sat_shd.g_rec_type;
384 l_proc varchar2(72) := g_package||'upd';
385 --
386 Begin
387 hr_utility.set_location('Entering:'||l_proc, 5);
388 --
389 -- Call conversion function to turn arguments into the
390 -- l_rec structure.
391 --
392 l_rec :=
393 pqh_sat_shd.convert_args
394 (
395 p_special_attribute_id,
396 p_txn_category_attribute_id,
397 p_attribute_type_cd,
398 p_key_attribute_type,
399 p_enable_flag,
400 p_flex_code,
401 p_object_version_number,
402 p_ddf_column_name,
403 p_ddf_value_column_name,
404 p_context
405 );
406 --
407 -- Having converted the arguments into the
408 -- plsql record structure we call the corresponding record
409 -- business process.
410 --
411 upd(
412 p_effective_date,l_rec);
413 p_object_version_number := l_rec.object_version_number;
414 --
415 hr_utility.set_location(' Leaving:'||l_proc, 10);
416 End upd;
417 --
418 end pqh_sat_upd;