1 Package Body pqh_ctl_upd as
2 /* $Header: pqctlrhi.pkb 120.2 2011/04/28 09:40:38 sidsaxen ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_ctl_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_ctl_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 --
56 --
57 -- Update the pqh_transaction_categories_tl Row
58 --
59 update pqh_transaction_categories_tl
60 set
61 transaction_category_id = p_rec.transaction_category_id,
62 name = p_rec.name,
63 language = p_rec.language,
64 source_lang = p_rec.source_lang
65 where transaction_category_id = p_rec.transaction_category_id
66 and language = p_rec.language;
67 --
68 --
69 hr_utility.set_location(' Leaving:'||l_proc, 10);
70 --
71 Exception
72 When hr_api.check_integrity_violated Then
73 -- A check constraint has been violated
74 pqh_ctl_shd.constraint_error
75 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
76 When hr_api.parent_integrity_violated Then
77 -- Parent integrity has been violated
78 pqh_ctl_shd.constraint_error
79 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
80 When hr_api.unique_integrity_violated Then
81 -- Unique integrity has been violated
82 pqh_ctl_shd.constraint_error
83 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
84 When Others Then
85 Raise;
86 End update_dml;
87 --
88 -- ----------------------------------------------------------------------------
89 -- |------------------------------< pre_update >------------------------------|
90 -- ----------------------------------------------------------------------------
91 -- {Start Of Comments}
92 --
93 -- Description:
94 -- This private procedure contains any processing which is required before
95 -- the update dml.
96 --
97 -- Prerequisites:
98 -- This is an internal procedure which is called from the upd procedure.
99 --
100 -- In Parameters:
101 -- A Pl/Sql record structre.
102 --
103 -- Post Success:
104 -- Processing continues.
105 --
106 -- Post Failure:
107 -- If an error has occurred, an error message and exception will be raised
108 -- but not handled.
109 --
110 -- Developer Implementation Notes:
111 -- Any pre-processing required before the update dml is issued should be
112 -- coded within this procedure. It is important to note that any 3rd party
113 -- maintenance should be reviewed before placing in this procedure.
114 --
115 -- Access Status:
116 -- Internal Row Handler Use Only.
117 --
118 -- {End Of Comments}
119 -- ----------------------------------------------------------------------------
120 Procedure pre_update(p_rec in pqh_ctl_shd.g_rec_type) is
121 --
122 l_proc varchar2(72) := g_package||'pre_update';
123 --
124 Begin
125 hr_utility.set_location('Entering:'||l_proc, 5);
126 --
127 hr_utility.set_location(' Leaving:'||l_proc, 10);
128 End pre_update;
129 --
130 -- ----------------------------------------------------------------------------
131 -- |-----------------------------< post_update >------------------------------|
132 -- ----------------------------------------------------------------------------
133 -- {Start Of Comments}
134 --
135 -- Description:
136 -- This private procedure contains any processing which is required after the
137 -- update dml.
138 --
139 -- Prerequisites:
140 -- This is an internal procedure which is called from the upd procedure.
141 --
142 -- In Parameters:
143 -- A Pl/Sql record structre.
144 --
145 -- Post Success:
146 -- Processing continues.
147 --
148 -- Post Failure:
149 -- If an error has occurred, an error message and exception will be raised
150 -- but not handled.
151 --
152 -- Developer Implementation Notes:
153 -- Any post-processing required after the update dml is issued should be
154 -- coded within this procedure. It is important to note that any 3rd party
155 -- maintenance should be reviewed before placing in this procedure.
156 --
157 -- Access Status:
158 -- Internal Row Handler Use Only.
159 --
160 -- {End Of Comments}
161 -- ----------------------------------------------------------------------------
162 Procedure post_update(p_rec in pqh_ctl_shd.g_rec_type) is
163 --
164 l_proc varchar2(72) := g_package||'post_update';
165 --
166 Begin
167 hr_utility.set_location('Entering:'||l_proc, 5);
168 --
169 --
170 -- Start of API User Hook for post_update.
171 --
172 begin
173 --
174 pqh_ctl_rku.after_update
175 (
176 p_transaction_category_id =>p_rec.transaction_category_id
177 ,p_name =>p_rec.name
178 ,p_language =>p_rec.language
179 ,p_source_lang =>p_rec.source_lang
180 ,p_name_o =>pqh_ctl_shd.g_old_rec.name
181 ,p_language_o =>pqh_ctl_shd.g_old_rec.language
182 ,p_source_lang_o =>pqh_ctl_shd.g_old_rec.source_lang
183 );
184 --
185 exception
186 --
187 when hr_api.cannot_find_prog_unit then
188 --
189 hr_api.cannot_find_prog_unit_error
190 (p_module_name => 'pqh_transaction_categories_tl'
191 ,p_hook_type => 'AU');
192 --
193 end;
194 --
195 -- End of API User Hook for post_update.
196 --
197 --
198 hr_utility.set_location(' Leaving:'||l_proc, 10);
199 End post_update;
200 --
201 -- ----------------------------------------------------------------------------
202 -- |-----------------------------< convert_defs >-----------------------------|
203 -- ----------------------------------------------------------------------------
204 -- {Start Of Comments}
205 --
206 -- Description:
207 -- The Convert_Defs procedure has one very important function:
208 -- It must return the record structure for the row with all system defaulted
209 -- values converted into its corresponding parameter value for update. When
210 -- we attempt to update a row through the Upd process , certain
211 -- parameters can be defaulted which enables flexibility in the calling of
212 -- the upd process (e.g. only attributes which need to be updated need to be
213 -- specified). For the upd process to determine which attributes
214 -- have NOT been specified we need to check if the parameter has a reserved
215 -- system default value. Therefore, for all parameters which have a
216 -- corresponding reserved system default mechanism specified we need to
217 -- check if a system default is being used. If a system default is being
218 -- used then we convert the defaulted value into its corresponding attribute
219 -- value held in the g_old_rec data structure.
220 --
221 -- Prerequisites:
222 -- This private function can only be called from the upd process.
223 --
224 -- In Parameters:
225 -- A Pl/Sql record structre.
226 --
227 -- Post Success:
228 -- The record structure will be returned with all system defaulted parameter
229 -- values converted into its current row attribute value.
230 --
231 -- Post Failure:
232 -- No direct error handling is required within this function. Any possible
233 -- errors within this procedure will be a PL/SQL value error due to conversion
234 -- of datatypes or data lengths.
235 --
236 -- Developer Implementation Notes:
237 -- None.
238 --
239 -- Access Status:
240 -- Internal Row Handler Use Only.
241 --
242 -- {End Of Comments}
243 -- ----------------------------------------------------------------------------
244 Procedure convert_defs(p_rec in out nocopy pqh_ctl_shd.g_rec_type) is
245 --
246 l_proc varchar2(72) := g_package||'convert_defs';
247 --
248 Begin
249 --
250 hr_utility.set_location('Entering:'||l_proc, 5);
251 --
252 -- We must now examine each argument value in the
253 -- p_rec plsql record structure
254 -- to see if a system default is being used. If a system default
255 -- is being used then we must set to the 'current' argument value.
256 --
257 If (p_rec.name = hr_api.g_varchar2) then
258 p_rec.name :=
259 pqh_ctl_shd.g_old_rec.name;
260 End If;
261 If (p_rec.source_lang = hr_api.g_varchar2) then
262 p_rec.source_lang :=
263 pqh_ctl_shd.g_old_rec.source_lang;
264 End If;
265 --
266 hr_utility.set_location(' Leaving:'||l_proc, 10);
267 --
268 End convert_defs;
269 --
270 -- ----------------------------------------------------------------------------
271 -- |---------------------------------< upd >----------------------------------|
272 -- ----------------------------------------------------------------------------
273 Procedure upd
274 (
275 p_rec in out nocopy pqh_ctl_shd.g_rec_type
276 ) is
277 --
278 l_proc varchar2(72) := g_package||'upd';
279 --
280 Begin
281 hr_utility.set_location('Entering:'||l_proc, 5);
282 --
283 -- We must lock the row which we need to update.
284 --
285 pqh_ctl_shd.lck
286 (
287 p_rec.transaction_category_id,
288 p_rec.language
289 );
290 --
291 -- 1. During an update system defaults are used to determine if
292 -- arguments have been defaulted or not. We must therefore
293 -- derive the full record structure values to be updated.
294 --
295 -- 2. Call the supporting update validate operations.
296 --
297 convert_defs(p_rec);
298 pqh_ctl_bus.update_validate(p_rec);
299 --
300 -- Call the supporting pre-update operation
301 --
302 pre_update(p_rec);
303 --
304 -- Update the row.
305 --
306 update_dml(p_rec);
307 --
308 -- Call the supporting post-update operation
309 --
310 post_update(p_rec);
311 End upd;
312 --
313 -- ----------------------------------------------------------------------------
314 -- |---------------------------------< upd >----------------------------------|
315 -- ----------------------------------------------------------------------------
316 Procedure upd
317 (
318 p_transaction_category_id in number,
319 p_name in varchar2 default hr_api.g_varchar2,
320 p_language in varchar2,
321 p_source_lang in varchar2 default hr_api.g_varchar2
322 ) is
323 --
324 l_rec pqh_ctl_shd.g_rec_type;
325 l_proc varchar2(72) := g_package||'upd';
326 --
327 Begin
328 hr_utility.set_location('Entering:'||l_proc, 5);
329 --
330 -- Call conversion function to turn arguments into the
331 -- l_rec structure.
332 --
333 l_rec :=
334 pqh_ctl_shd.convert_args
335 (
336 p_transaction_category_id,
337 p_name,
338 p_language,
339 p_source_lang
340 );
341 --
342 -- Having converted the arguments into the
343 -- plsql record structure we call the corresponding record
344 -- business process.
345 --
346 upd(l_rec);
347 --
348 --
349 hr_utility.set_location(' Leaving:'||l_proc, 10);
350 End upd;
351 --
352 --
353 -- ----------------------------------------------------------------------------
354 -- |------------------------------< upd_tl >----------------------------------|
355 -- ----------------------------------------------------------------------------
356 --
357 procedure upd_tl ( p_language_code in varchar2,
358 p_transaction_category_id in number,
359 p_name in varchar2 ) is
360 --
361 cursor csr_upd_langs is
362 select ctl.language
363 from pqh_transaction_categories_tl ctl
364 where ctl.transaction_category_id = p_transaction_category_id
365 AND p_language_code in (ctl.language,ctl.source_lang);
366 --
367 l_proc varchar2(72) := g_package||'upd_tl';
368 --
369 begin
370 --
371 hr_utility.set_location(' Entering:'||l_proc, 10);
372 --
373 for l_lang in csr_upd_langs loop
374 --
375 --
376 upd
377 (p_transaction_category_id => p_transaction_category_id,
378 p_name => p_name,
379 p_language => l_lang.language,
380 p_source_lang => p_language_code) ;
381 --
382 --
383 end loop;
384 --
385 hr_utility.set_location(' Leaving:'||l_proc, 20);
386 --
387 end upd_tl;
388 --
389 -- ----------- Translate Row -------------------------------------------
390 --
391 Procedure translate_row (
392 p_short_name in varchar2,
393 p_name in varchar2,
394 p_owner in varchar2) is
395 --
396 l_transaction_category_id pqh_transaction_categories.transaction_category_id%type;
397 --
398 --
399 l_created_by pqh_transaction_categories.created_by%TYPE;
400 l_last_updated_by pqh_transaction_categories.last_updated_by%TYPE;
401 l_creation_date pqh_transaction_categories.creation_date%TYPE;
402 l_last_update_date pqh_transaction_categories.last_update_date%TYPE;
403 l_last_update_login pqh_transaction_categories.last_update_login%TYPE;
404 --
405 --
406 Cursor c1 is
407 Select transaction_category_id
408 From pqh_transaction_categories_vl
409 Where short_name = p_short_name;
410 --
411 begin
412 --
413 open c1;
414 fetch c1 into l_transaction_category_id ;
415 close c1;
416 --
417 --
418 --
419 -- populate WHO columns
420 --
421 /**
422 if p_owner = 'SEED' then
423 l_created_by := 1;
424 l_last_updated_by := -1;
425 else
426 l_created_by := 0;
427 l_last_updated_by := 0;
428 end if;
429 **/
430
431 l_last_updated_by := fnd_load_util.owner_id(p_owner);
432 l_created_by := fnd_load_util.owner_id(p_owner);
433 l_creation_date := sysdate;
434 l_last_update_date := sysdate;
435 l_last_update_login := 0;
436
437 --
438 update pqh_transaction_categories_tl
439 set name = p_name ,
440 last_updated_by = l_last_updated_by,
441 last_update_date = l_last_update_date,
442 last_update_login = l_last_update_login,
443 source_lang = USERENV('LANG')
444 where USERENV('LANG') in (language,source_lang)
445 and transaction_category_id = l_transaction_category_id ;
446 --
447 end translate_row;
448 --
449 --
450 end pqh_ctl_upd;
[Home] [Help]