1 Package Body hr_qsf_upd as
2 /* $Header: hrqsfrhi.pkb 120.1.12020000.3 2013/03/20 12:06:16 bmaheshw ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_qsf_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 set and unset the g_api_dml status as required (as we are about to
21 -- perform dml).
22 -- 3) To update the specified row in the schema using the primary key in
23 -- the predicates.
24 -- 4) To trap any constraint violations that may have occurred.
25 -- 5) To raise any other errors.
26 --
27 -- Prerequisites:
28 -- This is an internal private procedure which must be called from the upd
29 -- procedure.
30 --
31 -- In Parameters:
32 -- A Pl/Sql record structre.
33 --
34 -- Post Success:
35 -- The specified row will be updated in the schema.
36 --
37 -- Post Failure:
38 -- On the update dml failure it is important to note that we always reset the
39 -- g_api_dml status to false.
40 -- If a check, unique or parent integrity constraint violation is raised the
41 -- constraint_error procedure will be called.
42 -- If any other error is reported, the error will be raised after the
43 -- g_api_dml status is reset.
44 --
45 -- Developer Implementation Notes:
46 -- The update 'set' attribute list should be modified if any of your
47 -- attributes are not updateable.
48 --
49 -- Access Status:
50 -- Internal Row Handler Use Only.
51 --
52 -- {End Of Comments}
53 -- ----------------------------------------------------------------------------
54 Procedure update_dml(p_rec in out nocopy hr_qsf_shd.g_rec_type) is
55 --
56 l_proc varchar2(72) := g_package||'update_dml';
57 --
58 Begin
59 hr_utility.set_location('Entering:'||l_proc, 5);
60 --
61 -- Increment the object version
62 --
63 p_rec.object_version_number := p_rec.object_version_number + 1;
64 --
65 --
66 -- Update the hr_quest_fields Row
67 --
68 update hr_quest_fields
69 set
70 field_id = p_rec.field_id,
71 sql_text = p_rec.sql_text,
72 object_version_number = p_rec.object_version_number
73 ,name = p_rec.name
74 ,type = p_rec.type
75 ,html_text = p_rec.html_text
76 ,sql_required_flag = p_rec.sql_required_flag
77 ,validation_type = p_rec.validation_type
78 ,validation_detail = p_rec.validation_detail
79 ,display_sequence = p_rec.display_sequence
80 where field_id = p_rec.field_id;
81 --
82 --
83 hr_utility.set_location(' Leaving:'||l_proc, 10);
84 --
85 Exception
86 When hr_api.check_integrity_violated Then
87 -- A check constraint has been violated
88 hr_qsf_shd.constraint_error
89 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
90 When hr_api.parent_integrity_violated Then
91 -- Parent integrity has been violated
92 hr_qsf_shd.constraint_error
93 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
94 When hr_api.unique_integrity_violated Then
95 -- Unique integrity has been violated
96 hr_qsf_shd.constraint_error
97 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
98 When Others Then
99 Raise;
100 End update_dml;
101 --
102 -- ----------------------------------------------------------------------------
103 -- |------------------------------< pre_update >------------------------------|
104 -- ----------------------------------------------------------------------------
105 -- {Start Of Comments}
106 --
107 -- Description:
108 -- This private procedure contains any processing which is required before
109 -- the update dml.
110 --
111 -- Prerequisites:
112 -- This is an internal procedure which is called from the upd procedure.
113 --
114 -- In Parameters:
115 -- A Pl/Sql record structre.
116 --
117 -- Post Success:
118 -- Processing continues.
119 --
120 -- Post Failure:
121 -- If an error has occurred, an error message and exception will be raised
122 -- but not handled.
123 --
124 -- Developer Implementation Notes:
125 -- Any pre-processing required before the update dml is issued should be
126 -- coded within this procedure. It is important to note that any 3rd party
127 -- maintenance should be reviewed before placing in this procedure.
128 --
129 -- Access Status:
130 -- Internal Row Handler Use Only.
131 --
132 -- {End Of Comments}
133 -- ----------------------------------------------------------------------------
134 Procedure pre_update(p_rec in hr_qsf_shd.g_rec_type) is
135 --
136 l_proc varchar2(72) := g_package||'pre_update';
137 --
138 Begin
139 hr_utility.set_location('Entering:'||l_proc, 5);
140 --
141 hr_utility.set_location(' Leaving:'||l_proc, 10);
142 End pre_update;
143 --
144 -- ----------------------------------------------------------------------------
145 -- |-----------------------------< post_update >------------------------------|
146 -- ----------------------------------------------------------------------------
147 -- {Start Of Comments}
148 --
149 -- Description:
150 -- This private procedure contains any processing which is required after the
151 -- update dml.
152 --
153 -- Prerequisites:
154 -- This is an internal procedure which is called from the upd procedure.
155 --
156 -- In Parameters:
157 -- A Pl/Sql record structre.
158 --
159 -- Post Success:
160 -- Processing continues.
161 --
162 -- Post Failure:
163 -- If an error has occurred, an error message and exception will be raised
164 -- but not handled.
165 --
166 -- Developer Implementation Notes:
167 -- Any post-processing required after the update dml is issued should be
168 -- coded within this procedure. It is important to note that any 3rd party
169 -- maintenance should be reviewed before placing in this procedure.
170 --
171 -- Access Status:
172 -- Internal Row Handler Use Only.
173 --
174 -- {End Of Comments}
175 -- ----------------------------------------------------------------------------
176 Procedure post_update(p_rec in hr_qsf_shd.g_rec_type
177 ,p_effective_date in date
178 ) is
179 --
180 l_proc varchar2(72) := g_package||'post_update';
181 --
182 Begin
183 hr_utility.set_location('Entering:'||l_proc, 5);
184 --
185 begin
186 hr_qsf_rku.after_update
187 (p_field_id => p_rec.field_id
188 ,p_sql_text => p_rec.sql_text
189 ,p_object_version_number => p_rec.object_version_number
190 ,p_questionnaire_template_id_o
191 => hr_qsf_shd.g_old_rec.questionnaire_template_id
192 ,p_name_o => hr_qsf_shd.g_old_rec.name
193 ,p_type_o => hr_qsf_shd.g_old_rec.type
194 ,p_html_text_o => hr_qsf_shd.g_old_rec.html_text
195 ,p_sql_required_flag_o => hr_qsf_shd.g_old_rec.sql_required_flag
196 ,p_sql_text_o => hr_qsf_shd.g_old_rec.sql_text
197 ,p_object_version_number_o => hr_qsf_shd.g_old_rec.object_version_number
198 ,p_effective_date => p_effective_date
199 ,p_validation_type => p_rec.validation_type
200 ,p_validation_detail => p_rec.validation_detail
201 ,p_display_sequence => p_rec.display_sequence
202 );
203 exception
204 when hr_api.cannot_find_prog_unit then
205 hr_api.cannot_find_prog_unit_error
206 (p_module_name => 'HR_QUEST_FIELDS'
207 ,p_hook_type => 'AU'
208 );
209 end;
210 --
211 hr_utility.set_location(' Leaving:'||l_proc, 10);
212 End post_update;
213 --
214 -- ----------------------------------------------------------------------------
215 -- |-----------------------------< convert_defs >-----------------------------|
216 -- ----------------------------------------------------------------------------
217 -- {Start Of Comments}
218 --
219 -- Description:
220 -- The Convert_Defs procedure has one very important function:
221 -- It must return the record structure for the row with all system defaulted
222 -- values converted into its corresponding parameter value for update. When
223 -- we attempt to update a row through the Upd process , certain
224 -- parameters can be defaulted which enables flexibility in the calling of
225 -- the upd process (e.g. only attributes which need to be updated need to be
226 -- specified). For the upd process to determine which attributes
227 -- have NOT been specified we need to check if the parameter has a reserved
228 -- system default value. Therefore, for all parameters which have a
229 -- corresponding reserved system default mechanism specified we need to
230 -- check if a system default is being used. If a system default is being
231 -- used then we convert the defaulted value into its corresponding attribute
232 -- value held in the g_old_rec data structure.
233 --
234 -- Prerequisites:
235 -- This private function can only be called from the upd process.
236 --
237 -- In Parameters:
238 -- A Pl/Sql record structre.
239 --
240 -- Post Success:
241 -- The record structure will be returned with all system defaulted parameter
242 -- values converted into its current row attribute value.
243 --
244 -- Post Failure:
245 -- No direct error handling is required within this function. Any possible
246 -- errors within this procedure will be a PL/SQL value error due to conversion
247
248 -- of datatypes or data lengths.
249 --
250 -- Developer Implementation Notes:
251 -- None.
252 --
253 -- Access Status:
254 -- Internal Row Handler Use Only.
255 --
256 -- {End Of Comments}
257 -- ----------------------------------------------------------------------------
258 Procedure convert_defs(p_rec in out nocopy hr_qsf_shd.g_rec_type) is
259 --
260 l_proc varchar2(72) := g_package||'convert_defs';
261 --
262 Begin
263 --
264 hr_utility.set_location('Entering:'||l_proc, 5);
265 --
266 -- We must now examine each argument value in the
267 -- p_rec plsql record structure
268 -- to see if a system default is being used. If a system default
269 -- is being used then we must set to the 'current' argument value.
270 --
271 If (p_rec.questionnaire_template_id = hr_api.g_number) then
272 p_rec.questionnaire_template_id :=
273 hr_qsf_shd.g_old_rec.questionnaire_template_id;
274 End If;
275 If (p_rec.name = hr_api.g_varchar2) then
276 p_rec.name :=
277 hr_qsf_shd.g_old_rec.name;
278 End If;
279 If (p_rec.type = hr_api.g_varchar2) then
280 p_rec.type :=
281 hr_qsf_shd.g_old_rec.type;
282 End If;
283 If (p_rec.html_text = hr_api.g_varchar2) then
284 p_rec.html_text :=
285 hr_qsf_shd.g_old_rec.html_text;
286 End If;
287 If (p_rec.sql_required_flag = hr_api.g_varchar2) then
288 p_rec.sql_required_flag :=
289 hr_qsf_shd.g_old_rec.sql_required_flag;
290 End If;
291 If (p_rec.sql_text = hr_api.g_varchar2) then
292 p_rec.sql_text :=
293 hr_qsf_shd.g_old_rec.sql_text;
294 End If;
295
296 --
297 hr_utility.set_location(' Leaving:'||l_proc, 10);
298 --
299 End convert_defs;
300 --
301 -- ----------------------------------------------------------------------------
302 -- |---------------------------------< upd >----------------------------------|
303 -- ----------------------------------------------------------------------------
304 Procedure upd
305 (
306 p_rec in out nocopy hr_qsf_shd.g_rec_type,
307 p_effective_date in date
308 ) is
309 --
310 l_proc varchar2(72) := g_package||'upd';
311 --
312 Begin
313 hr_utility.set_location('Entering:'||l_proc, 5);
314 --
315 -- We must lock the row which we need to update.
316 --
317 hr_qsf_shd.lck
318 (
319 p_rec.field_id,
320 p_rec.object_version_number
321 );
322 --
323 -- 1. During an update system defaults are used to determine if
324 -- arguments have been defaulted or not. We must therefore
325 -- derive the full record structure values to be updated.
326 --
327 -- 2. Call the supporting update validate operations.
328 --
329 convert_defs(p_rec);
330 hr_qsf_bus.update_validate(p_rec, p_effective_date);
331 hr_multi_message.end_validation_set;
332 --
333 -- Call the supporting pre-update operation
334 --
335 pre_update(p_rec);
336 --
337 -- Update the row.
338 --
339 update_dml(p_rec);
340 --
341 -- Call the supporting post-update operation
342 --
343 post_update(p_rec, p_effective_date);
344 hr_multi_message.end_validation_set;
345 End upd;
346 --
347 -- ----------------------------------------------------------------------------
348 -- |---------------------------------< upd >----------------------------------|
349 -- ----------------------------------------------------------------------------
350 Procedure upd
351 (
352 p_field_id in number,
353 p_sql_text in varchar2 default hr_api.g_varchar2,
354 p_object_version_number in out nocopy number,
355 p_effective_date in date
356 ,p_questionnaire_template_id in number
357 ,p_name in varchar2
358 ,p_type in varchar2
359 ,p_sql_required_flag in varchar2
360 ,p_html_text in CLOB
361 ,p_validation_type in varchar2
362 ,p_validation_detail in varchar2
363 ,p_display_sequence in number
364 ) is
365 --
366 l_rec hr_qsf_shd.g_rec_type;
367 l_proc varchar2(72) := g_package||'upd';
368 --
369 Begin
370 hr_utility.set_location('Entering:'||l_proc, 5);
371 --
372 -- Call conversion function to turn arguments into the
373 -- l_rec structure.
374 --
375 l_rec :=
376 hr_qsf_shd.convert_args
377 (
378 p_field_id,
379 hr_api.g_number,
380 p_name,
381 p_type,
382 p_html_text,
383 p_sql_required_flag,
384 p_sql_text,
385 p_object_version_number
386 ,p_validation_type
387 ,p_validation_detail
388 ,p_display_sequence
389 );
390 --
391 -- Having converted the arguments into the
392 -- plsql record structure we call the corresponding record
393 -- business process.
394 --
395 upd(l_rec, p_effective_date);
396 p_object_version_number := l_rec.object_version_number;
397 --
398 hr_utility.set_location(' Leaving:'||l_proc, 10);
399 End upd;
400 --
401 end hr_qsf_upd;