1 Package Body pqh_ttl_upd as
2 /* $Header: pqttlrhi.pkb 120.1 2005/08/06 13:18:05 srajakum noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_ttl_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_ttl_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_templates_tl Row
58 --
59 update pqh_templates_tl
60 set
61 template_id = p_rec.template_id,
62 template_name = p_rec.template_name,
63 language = p_rec.language,
64 source_lang = p_rec.source_lang
65 where template_id = p_rec.template_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_ttl_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_ttl_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_ttl_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_ttl_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_ttl_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_ttl_rku.after_update
175 (
176 p_template_id =>p_rec.template_id
177 ,p_template_name =>p_rec.template_name
178 ,p_language =>p_rec.language
179 ,p_source_lang =>p_rec.source_lang
180 ,p_template_name_o =>pqh_ttl_shd.g_old_rec.template_name
181 ,p_language_o =>pqh_ttl_shd.g_old_rec.language
182 ,p_source_lang_o =>pqh_ttl_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_templates_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_ttl_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.template_name = hr_api.g_varchar2) then
258 p_rec.template_name :=
259 pqh_ttl_shd.g_old_rec.template_name;
260 End If;
261 If (p_rec.source_lang = hr_api.g_varchar2) then
262 p_rec.source_lang :=
263 pqh_ttl_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_ttl_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_ttl_shd.lck
286 (
287 p_rec.template_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_ttl_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_template_id in number,
319 p_template_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_ttl_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_ttl_shd.convert_args
335 (
336 p_template_id,
337 p_template_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
358 Procedure upd_tl
359 (
360 p_template_id in number,
361 p_language_code in varchar2,
362 p_template_name in varchar2 default hr_api.g_varchar2
363 )is
364
365 Cursor csr_upd_langs is
366 select ttl.language
367 from pqh_templates_tl ttl
368 where ttl.template_id = p_template_id
369 AND p_language_code in (ttl.language,ttl.source_lang);
370 --
371 l_proc varchar2(72) := g_package||'upd_tl';
372 --
373 begin
374 --
375 hr_utility.set_location(' Entering:'||l_proc, 10);
376 --
377 for l_lang in csr_upd_langs loop
378 --
379 --
380 upd
381 (p_template_id => p_template_id,
382 p_template_name => p_template_name,
383 p_language => l_lang.language,
384 p_source_lang => p_language_code) ;
385 --
386 --
387 end loop;
388 --
389 hr_utility.set_location(' Leaving:'||l_proc, 20);
390 --
391 end upd_tl;
392
393
394 -- ----------- Translate Row -------------------------------------------
395 --
396 Procedure translate_row (
397 p_short_name in varchar2,
398 p_template_name in varchar2 ,
399 p_owner in varchar2
400 ) is
401
402 l_template_id pqh_templates.template_id%type := 0;
403 --
404 --
405 l_created_by pqh_templates.created_by%TYPE;
406 l_last_updated_by pqh_templates.last_updated_by%TYPE;
407 l_creation_date pqh_templates.creation_date%TYPE;
408 l_last_update_date pqh_templates.last_update_date%TYPE;
409 l_last_update_login pqh_templates.last_update_login%TYPE;
410 --
411 --
412 Cursor c1 is
413 select template_id
414 from pqh_templates
415 where short_name = p_short_name ;
416 --
417 --
418 BEGIN
419 --
420 --
421 Open c1;
422 Fetch c1 into l_template_id;
423 Close c1;
424 --
425 --
426 -- populate WHO columns
427 --
428 /**
429 if p_owner = 'SEED' then
430 l_created_by := 1;
431 l_last_updated_by := -1;
432 else
433 l_created_by := 0;
434 l_last_updated_by := 0;
435 end if;
436 **/
437 l_last_updated_by := fnd_load_util.owner_id(p_owner);
438 l_created_by := fnd_load_util.owner_id(p_owner);
439 l_creation_date := sysdate;
440 l_last_update_date := sysdate;
441 l_last_update_login := 0;
442 --
443 -- update the tl table
444 --
445 UPDATE pqh_templates_tl
446 SET template_name = p_template_name,
447 last_updated_by = l_last_updated_by,
448 last_update_date = l_last_update_date,
449 last_update_login = l_last_update_login,
450 source_lang = userenv('LANG')
451 WHERE template_id = l_template_id
452 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
453 --
454 end translate_row;
455 --
456 --
457 end pqh_ttl_upd;
[Home] [Help]