1 Package Body pqh_dfs_upd as
2 /* $Header: pqdfsrhi.pkb 115.11 2003/04/02 20:02:02 srajakum noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_dfs_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 -- 3) To update the specified row in the schema using the primary key in
21 -- the predicates.
22 -- 4) To trap any constraint violations that may have occurred.
23 -- 5) 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}
50 l_proc varchar2(72) := g_package||'update_dml';
47 -- ----------------------------------------------------------------------------
48 Procedure update_dml(p_rec in out nocopy pqh_dfs_shd.g_rec_type) is
49 --
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_dflt_fund_srcs Row
61 --
62 update pqh_dflt_fund_srcs
63 set
64 dflt_fund_src_id = p_rec.dflt_fund_src_id,
65 dflt_budget_element_id = p_rec.dflt_budget_element_id,
66 dflt_dist_percentage = p_rec.dflt_dist_percentage,
67 object_version_number = p_rec.object_version_number,
68 cost_allocation_keyflex_id = p_rec.cost_allocation_keyflex_id ,
69 project_id = p_rec.project_id ,
70 award_id = p_rec.award_id ,
71 task_id = p_rec.task_id ,
72 expenditure_type = p_rec.expenditure_type ,
73 organization_id = p_rec.organization_id
74 where dflt_fund_src_id = p_rec.dflt_fund_src_id;
75 --
76 --
77 hr_utility.set_location(' Leaving:'||l_proc, 10);
78 --
79 Exception
80 When hr_api.check_integrity_violated Then
81 -- A check constraint has been violated
82 pqh_dfs_shd.constraint_error
83 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
84 When hr_api.parent_integrity_violated Then
85 -- Parent integrity has been violated
86 pqh_dfs_shd.constraint_error
87 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
88 When hr_api.unique_integrity_violated Then
89 -- Unique integrity has been violated
90 pqh_dfs_shd.constraint_error
91 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
92 When Others Then
93 Raise;
94 End update_dml;
95 --
96 -- ----------------------------------------------------------------------------
97 -- |------------------------------< pre_update >------------------------------|
98 -- ----------------------------------------------------------------------------
99 -- {Start Of Comments}
100 --
101 -- Description:
102 -- This private procedure contains any processing which is required before
103 -- the update dml.
104 --
105 -- Prerequisites:
106 -- This is an internal procedure which is called from the upd procedure.
107 --
108 -- In Parameters:
109 -- A Pl/Sql record structre.
110 --
111 -- Post Success:
112 -- Processing continues.
113 --
114 -- Post Failure:
115 -- If an error has occurred, an error message and exception will be raised
116 -- but not handled.
117 --
118 -- Developer Implementation Notes:
119 -- Any pre-processing required before the update dml is issued should be
120 -- coded within this procedure. It is important to note that any 3rd party
121 -- maintenance should be reviewed before placing in this procedure.
122 --
123 -- Access Status:
124 -- Internal Row Handler Use Only.
125 --
126 -- {End Of Comments}
127 -- ----------------------------------------------------------------------------
128 Procedure pre_update(p_rec in pqh_dfs_shd.g_rec_type) is
129 --
130 l_proc varchar2(72) := g_package||'pre_update';
131 --
132 Begin
133 hr_utility.set_location('Entering:'||l_proc, 5);
134 --
135 hr_utility.set_location(' Leaving:'||l_proc, 10);
136 End pre_update;
137 --
138 -- ----------------------------------------------------------------------------
139 -- |-----------------------------< post_update >------------------------------|
140 -- ----------------------------------------------------------------------------
141 -- {Start Of Comments}
142 --
143 -- Description:
144 -- This private procedure contains any processing which is required after the
145 -- update dml.
146 --
147 -- Prerequisites:
148 -- This is an internal procedure which is called from the upd procedure.
149 --
150 -- In Parameters:
151 -- A Pl/Sql record structre.
152 --
153 -- Post Success:
154 -- Processing continues.
155 --
156 -- Post Failure:
157 -- If an error has occurred, an error message and exception will be raised
158 -- but not handled.
159 --
160 -- Developer Implementation Notes:
161 -- Any post-processing required after the update dml is issued should be
162 -- coded within this procedure. It is important to note that any 3rd party
163 -- maintenance should be reviewed before placing in this procedure.
164 --
165 -- Access Status:
166 -- Internal Row Handler Use Only.
167 --
168 -- {End Of Comments}
169 -- ----------------------------------------------------------------------------
170 Procedure post_update(p_rec in pqh_dfs_shd.g_rec_type) is
171 --
172 l_proc varchar2(72) := g_package||'post_update';
173 --
174 -- check if sum of percentage is 100
175 --
176 l_sum number(15,2) := 0;
177
178 cursor csr_element(p_dflt_budget_element_id in number) is
179 select SUM(NVL(dflt_dist_percentage,0))
180 from pqh_dflt_fund_srcs
181 where dflt_budget_element_id = p_dflt_budget_element_id;
182
183
184 Begin
185 hr_utility.set_location('Entering:'||l_proc, 5);
186 --
187 --
188 open csr_element(p_dflt_budget_element_id => p_rec.dflt_budget_element_id);
189 fetch csr_element into l_sum;
190 close csr_element;
191
192 if l_sum > 100 then
193 -- sum cannot be more then 100
197 --
194 --
195 hr_utility.set_message(8302,'PQH_WKS_INVALID_SRCS_SUM');
196 hr_utility.raise_error;
198 end if;
199 --
200 -- Start of API User Hook for post_update.
201 --
202 begin
203 --
204 pqh_dfs_rku.after_update
205 (
206 p_dflt_fund_src_id =>p_rec.dflt_fund_src_id
207 ,p_dflt_budget_element_id =>p_rec.dflt_budget_element_id
208 ,p_dflt_dist_percentage =>p_rec.dflt_dist_percentage
209 ,p_project_id =>p_rec.project_id
210 ,p_award_id =>p_rec.award_id
211 ,p_task_id =>p_rec.task_id
212 ,p_expenditure_type =>p_rec.expenditure_type
213 ,p_organization_id =>p_rec.organization_id
214 ,p_object_version_number =>p_rec.object_version_number
215 ,p_cost_allocation_keyflex_id =>p_rec.cost_allocation_keyflex_id
216 ,p_dflt_budget_element_id_o =>pqh_dfs_shd.g_old_rec.dflt_budget_element_id
217 ,p_dflt_dist_percentage_o =>pqh_dfs_shd.g_old_rec.dflt_dist_percentage
218 ,p_project_id_o =>pqh_dfs_shd.g_old_rec.project_id
219 ,p_award_id_o =>pqh_dfs_shd.g_old_rec.award_id
220 ,p_task_id_o =>pqh_dfs_shd.g_old_rec.task_id
221 ,p_expenditure_type_o =>pqh_dfs_shd.g_old_rec.expenditure_type
222 ,p_organization_id_o =>pqh_dfs_shd.g_old_rec.organization_id
223 ,p_object_version_number_o =>pqh_dfs_shd.g_old_rec.object_version_number
224 ,p_cost_allocation_keyflex_id_o =>pqh_dfs_shd.g_old_rec.cost_allocation_keyflex_id
225 );
226 --
227 exception
228 --
229 when hr_api.cannot_find_prog_unit then
230 --
231 hr_api.cannot_find_prog_unit_error
232 (p_module_name => 'pqh_dflt_fund_srcs'
233 ,p_hook_type => 'AU');
234 --
235 end;
236 --
237 -- End of API User Hook for post_update.
238 --
239 --
240 hr_utility.set_location(' Leaving:'||l_proc, 10);
241 End post_update;
242 --
243 -- ----------------------------------------------------------------------------
244 -- |-----------------------------< convert_defs >-----------------------------|
245 -- ----------------------------------------------------------------------------
246 -- {Start Of Comments}
247 --
248 -- Description:
249 -- The Convert_Defs procedure has one very important function:
250 -- It must return the record structure for the row with all system defaulted
251 -- values converted into its corresponding parameter value for update. When
252 -- we attempt to update a row through the Upd process , certain
253 -- parameters can be defaulted which enables flexibility in the calling of
254 -- the upd process (e.g. only attributes which need to be updated need to be
255 -- specified). For the upd process to determine which attributes
256 -- have NOT been specified we need to check if the parameter has a reserved
257 -- system default value. Therefore, for all parameters which have a
258 -- corresponding reserved system default mechanism specified we need to
259 -- check if a system default is being used. If a system default is being
260 -- used then we convert the defaulted value into its corresponding attribute
261 -- value held in the g_old_rec data structure.
262 --
263 -- Prerequisites:
264 -- This private function can only be called from the upd process.
265 --
266 -- In Parameters:
267 -- A Pl/Sql record structre.
268 --
269 -- Post Success:
270 -- The record structure will be returned with all system defaulted parameter
271 -- values converted into its current row attribute value.
272 --
273 -- Post Failure:
274 -- No direct error handling is required within this function. Any possible
275 -- errors within this procedure will be a PL/SQL value error due to conversion
276 -- of datatypes or data lengths.
277 --
278 -- Developer Implementation Notes:
279 -- None.
280 --
281 -- Access Status:
282 -- Internal Row Handler Use Only.
283 --
284 -- {End Of Comments}
285 -- ----------------------------------------------------------------------------
286 Procedure convert_defs(p_rec in out nocopy pqh_dfs_shd.g_rec_type) is
287 --
288 l_proc varchar2(72) := g_package||'convert_defs';
289 --
290 Begin
291 --
292 hr_utility.set_location('Entering:'||l_proc, 5);
293 --
294 -- We must now examine each argument value in the
295 -- p_rec plsql record structure
296 -- to see if a system default is being used. If a system default
297 -- is being used then we must set to the 'current' argument value.
298 --
299 If (p_rec.dflt_budget_element_id = hr_api.g_number) then
300 p_rec.dflt_budget_element_id :=
301 pqh_dfs_shd.g_old_rec.dflt_budget_element_id;
302 End If;
303 If (p_rec.dflt_dist_percentage = hr_api.g_number) then
304 p_rec.dflt_dist_percentage :=
305 pqh_dfs_shd.g_old_rec.dflt_dist_percentage;
306 End If;
307 If (p_rec.cost_allocation_keyflex_id = hr_api.g_number) then
308 p_rec.cost_allocation_keyflex_id :=
309 pqh_dfs_shd.g_old_rec.cost_allocation_keyflex_id;
310 End If;
311 If (p_rec.project_id = hr_api.g_number) then
312 p_rec.project_id :=
313 pqh_dfs_shd.g_old_rec.project_id;
314 End If;
315 If (p_rec.award_id = hr_api.g_number) then
316 p_rec.award_id :=
317 pqh_dfs_shd.g_old_rec.award_id;
318 End If;
319 If (p_rec.task_id = hr_api.g_number) then
320 p_rec.task_id :=
321 pqh_dfs_shd.g_old_rec.task_id;
322 End If;
323 If (p_rec.expenditure_type = hr_api.g_varchar2) then
324 p_rec.expenditure_type :=
325 pqh_dfs_shd.g_old_rec.expenditure_type;
326 End If;
327 If (p_rec.organization_id = hr_api.g_number) then
328 p_rec.organization_id :=
329 pqh_dfs_shd.g_old_rec.organization_id;
330 End If;
331 --
332 hr_utility.set_location(' Leaving:'||l_proc, 10);
333 --
334 End convert_defs;
335 --
336 -- ----------------------------------------------------------------------------
337 -- |---------------------------------< upd >----------------------------------|
338 -- ----------------------------------------------------------------------------
339 Procedure upd
340 (
341 p_rec in out nocopy pqh_dfs_shd.g_rec_type
342 ) is
343 --
344 l_proc varchar2(72) := g_package||'upd';
345 --
346 Begin
347 hr_utility.set_location('Entering:'||l_proc, 5);
348 --
349 -- We must lock the row which we need to update.
350 --
351 pqh_dfs_shd.lck
352 (
353 p_rec.dflt_fund_src_id,
354 p_rec.object_version_number
355 );
356 --
357 -- 1. During an update system defaults are used to determine if
358 -- arguments have been defaulted or not. We must therefore
359 -- derive the full record structure values to be updated.
360 --
361 -- 2. Call the supporting update validate operations.
362 --
363 convert_defs(p_rec);
364 pqh_dfs_bus.update_validate(p_rec);
365 --
366 -- Call the supporting pre-update operation
367 --
368 pre_update(p_rec);
369 --
370 -- Update the row.
371 --
372 update_dml(p_rec);
373 --
374 -- Call the supporting post-update operation
375 --
376 post_update(p_rec);
377 End upd;
378 --
379 -- ----------------------------------------------------------------------------
380 -- |---------------------------------< upd >----------------------------------|
381 -- ----------------------------------------------------------------------------
382 Procedure upd
383 (
384 p_dflt_fund_src_id in number,
385 p_dflt_budget_element_id in number default hr_api.g_number,
386 p_dflt_dist_percentage in number default hr_api.g_number,
387 p_project_id in number default hr_api.g_number,
388 p_award_id in number default hr_api.g_number,
389 p_task_id in number default hr_api.g_number,
390 p_expenditure_type in varchar2 default hr_api.g_varchar2,
391 p_organization_id in number default hr_api.g_number,
392 p_object_version_number in out nocopy number,
393 p_cost_allocation_keyflex_id in number default hr_api.g_number
394 ) is
395 --
396 l_rec pqh_dfs_shd.g_rec_type;
397 l_proc varchar2(72) := g_package||'upd';
398 --
399 Begin
400 hr_utility.set_location('Entering:'||l_proc, 5);
401 --
402 -- Call conversion function to turn arguments into the
403 -- l_rec structure.
404 --
405 l_rec :=
406 pqh_dfs_shd.convert_args
407 (
408 p_dflt_fund_src_id,
409 p_dflt_budget_element_id,
410 p_dflt_dist_percentage,
411 p_project_id,
412 p_award_id,
413 p_task_id,
414 p_expenditure_type,
415 p_organization_id,
416 p_object_version_number,
417 p_cost_allocation_keyflex_id
418 );
419 --
420 -- Having converted the arguments into the
421 -- plsql record structure we call the corresponding record
422 -- business process.
423 --
424 upd(l_rec);
425 p_object_version_number := l_rec.object_version_number;
426 --
427 hr_utility.set_location(' Leaving:'||l_proc, 10);
428 End upd;
429 --
430 end pqh_dfs_upd;
[Home] [Help]