1 Package Body irc_irt_upd as
2 /* $Header: irirtrhi.pkb 120.0 2005/07/26 15:10 mbocutt noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_irt_upd.'; -- Global package name
9 g_posting_url boolean;
10 g_redirection_url boolean;
11 --
12 -- ----------------------------------------------------------------------------
13 -- |------------------------------< update_dml >------------------------------|
14 -- ----------------------------------------------------------------------------
15 -- {Start Of Comments}
16 --
17 -- Description:
18 -- This procedure controls the actual dml update logic. The processing of
19 -- this procedure is:
20 -- 1) Increment the object_version_number by 1 if the object_version_number
21 -- is defined as an attribute for this entity.
22 -- 2) To set and unset the g_api_dml status as required (as we are about to
23 -- perform dml).
24 -- 3) To update the specified row in the schema using the primary key in
25 -- the predicates.
26 -- 4) To trap any constraint violations that may have occurred.
27 -- 5) To raise any other errors.
28 --
29 -- Prerequisites:
30 -- This is an internal private procedure which must be called from the upd
31 -- procedure.
32 --
33 -- In Parameters:
34 -- A Pl/Sql record structre.
35 --
36 -- Post Success:
37 -- The specified row will be updated in the schema.
38 --
39 -- Post Failure:
40 -- On the update dml failure it is important to note that we always reset the
41 -- g_api_dml status to false.
42 -- If a check, unique or parent integrity constraint violation is raised the
43 -- constraint_error procedure will be called.
44 -- If any other error is reported, the error will be raised after the
45 -- g_api_dml status is reset.
46 --
47 -- Developer Implementation Notes:
48 -- The update 'set' attribute list should be modified if any of your
49 -- attributes are not updateable.
50 --
51 -- Access Status:
52 -- Internal Row Handler Use Only.
53 --
54 -- {End Of Comments}
55 -- ----------------------------------------------------------------------------
56 Procedure update_dml
57 (p_rec in out nocopy irc_irt_shd.g_rec_type
58 ) is
59 --
60 l_proc varchar2(72) := g_package||'update_dml';
61 l_redirection_url clob;
62 l_posting_url clob;
63 --
64 Begin
65 hr_utility.set_location('Entering:'||l_proc, 5);
66 --
67 --
68 --
69 --
70 -- Update the irc_all_recruiting_sites_tl Row
71 --
72 update irc_all_recruiting_sites_tl
73 set
74 recruiting_site_id = p_rec.recruiting_site_id
75 ,language = p_rec.language
76 ,source_lang = p_rec.source_lang
77 ,site_name = p_rec.site_name
78 ,redirection_url = redirection_url
79 ,posting_url = posting_url
80 where recruiting_site_id = p_rec.recruiting_site_id
81 and language = p_rec.language
82 returning redirection_url,posting_url into l_redirection_url,l_posting_url;
83 --
84 if (g_redirection_url
85 and dbms_lob.getlength(l_redirection_url)<=32767
86 and dbms_lob.instr(l_redirection_url,p_rec.redirection_url)<>1)
87 then
88 hr_utility.set_location(l_proc, 10);
89 dbms_lob.trim(l_redirection_url,0);
90 dbms_lob.write(l_redirection_url
91 ,length(p_rec.redirection_url)
92 ,1
93 ,p_rec.redirection_url);
94 end if;
95 --
96 hr_utility.set_location(' Leaving:'||l_proc, 20);
97 --
98 if (g_posting_url
99 and dbms_lob.getlength(l_posting_url)<=32767
100 and dbms_lob.instr(l_posting_url,p_rec.posting_url)<>1)
101 then
102 hr_utility.set_location(l_proc, 30);
103 dbms_lob.trim(l_posting_url,0);
104 dbms_lob.write(l_posting_url
105 ,length(p_rec.posting_url)
106 ,1
107 ,p_rec.posting_url);
108 end if;
109 --
110 hr_utility.set_location(' Leaving:'||l_proc, 40);
111 --
112 Exception
113 When hr_api.check_integrity_violated Then
114 -- A check constraint has been violated
115 --
116 irc_irt_shd.constraint_error
117 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
118 When hr_api.parent_integrity_violated Then
119 -- Parent integrity has been violated
120 --
121 irc_irt_shd.constraint_error
122 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
123 When hr_api.unique_integrity_violated Then
124 -- Unique integrity has been violated
125 --
126 irc_irt_shd.constraint_error
127 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
128 When Others Then
129 --
130 Raise;
131 End update_dml;
132 --
133 -- ----------------------------------------------------------------------------
134 -- |------------------------------< pre_update >------------------------------|
135 -- ----------------------------------------------------------------------------
136 -- {Start Of Comments}
137 --
138 -- Description:
139 -- This private procedure contains any processing which is required before
140 -- the update dml.
141 --
142 -- Prerequisites:
143 -- This is an internal procedure which is called from the upd procedure.
144 --
145 -- In Parameters:
146 -- A Pl/Sql record structure.
147 --
148 -- Post Success:
149 -- Processing continues.
150 --
151 -- Post Failure:
152 -- If an error has occurred, an error message and exception wil be raised
153 -- but not handled.
154 --
155 -- Developer Implementation Notes:
156 -- Any pre-processing required before the update dml is issued should be
157 -- coded within this procedure. It is important to note that any 3rd party
158 -- maintenance should be reviewed before placing in this procedure.
159 --
160 -- Access Status:
161 -- Internal Row Handler Use Only.
162 --
163 -- {End Of Comments}
164 -- ----------------------------------------------------------------------------
165 Procedure pre_update
166 (p_rec in irc_irt_shd.g_rec_type
167 ) is
168 --
169 l_proc varchar2(72) := g_package||'pre_update';
170 --
171 Begin
172 hr_utility.set_location('Entering:'||l_proc, 5);
173 --
174 hr_utility.set_location(' Leaving:'||l_proc, 10);
175 End pre_update;
176 --
177 -- ----------------------------------------------------------------------------
178 -- |-----------------------------< post_update >------------------------------|
179 -- ----------------------------------------------------------------------------
180 -- {Start Of Comments}
181 --
182 -- Description:
183 -- This private procedure contains any processing which is required after
184 -- the update dml.
185 --
186 -- Prerequisites:
187 -- This is an internal procedure which is called from the upd procedure.
188 --
189 -- In Parameters:
190 -- A Pl/Sql record structure.
191 --
192 -- Post Success:
193 -- Processing continues.
194 --
195 -- Post Failure:
196 -- If an error has occurred, an error message and exception will be raised
197 -- but not handled.
198 --
199 -- Developer Implementation Notes:
200 -- Any post-processing required after the update dml is issued should be
201 -- coded within this procedure. It is important to note that any 3rd party
202 -- maintenance should be reviewed before placing in this procedure.
203 --
204 -- Access Status:
205 -- Internal Row Handler Use Only.
206 --
207 -- {End Of Comments}
208 -- ----------------------------------------------------------------------------
209 Procedure post_update
210 (p_rec in irc_irt_shd.g_rec_type
211 ) is
212 --
213 l_proc varchar2(72) := g_package||'post_update';
214 --
215 Begin
216 hr_utility.set_location('Entering:'||l_proc, 5);
217 begin
218 --
219 irc_irt_rku.after_update
220 (p_recruiting_site_id
221 => p_rec.recruiting_site_id
222 ,p_language
223 => p_rec.language
224 ,p_source_lang
225 => p_rec.source_lang
226 ,p_site_name
227 => p_rec.site_name
228 ,p_redirection_url
229 => p_rec.redirection_url
230 ,p_posting_url
231 => p_rec.posting_url
232 ,p_source_lang_o
233 => irc_irt_shd.g_old_rec.source_lang
234 ,p_site_name_o
235 => irc_irt_shd.g_old_rec.site_name
236 ,p_redirection_url_o
237 => irc_irt_shd.g_old_rec.redirection_url
238 ,p_posting_url_o
239 => irc_irt_shd.g_old_rec.posting_url
240 );
241 --
242 exception
243 --
244 when hr_api.cannot_find_prog_unit then
245 --
246 hr_api.cannot_find_prog_unit_error
247 (p_module_name => 'IRC_ALL_RECRUITING_SITES_TL'
248 ,p_hook_type => 'AU');
249 --
250 end;
251 --
252 hr_utility.set_location(' Leaving:'||l_proc, 10);
253 End post_update;
254 --
255 -- ----------------------------------------------------------------------------
256 -- |-----------------------------< convert_defs >-----------------------------|
257 -- ----------------------------------------------------------------------------
258 -- {Start Of Comments}
259 --
260 -- Description:
261 -- The Convert_Defs procedure has one very important function:
262 -- It must return the record structure for the row with all system defaulted
263 -- values converted into its corresponding parameter value for update. When
264 -- we attempt to update a row through the Upd process , certain
265 -- parameters can be defaulted which enables flexibility in the calling of
266 -- the upd process (e.g. only attributes which need to be updated need to be
267 -- specified). For the upd process to determine which attributes
268 -- have NOT been specified we need to check if the parameter has a reserved
269 -- system default value. Therefore, for all parameters which have a
270 -- corresponding reserved system default mechanism specified we need to
271 -- check if a system default is being used. If a system default is being
272 -- used then we convert the defaulted value into its corresponding attribute
273 -- value held in the g_old_rec data structure.
274 --
275 -- Prerequisites:
276 -- This private function can only be called from the upd process.
277 --
278 -- In Parameters:
279 -- A Pl/Sql record structure.
280 --
281 -- Post Success:
282 -- The record structure will be returned with all system defaulted parameter
283 -- values converted into its current row attribute value.
284 --
285 -- Post Failure:
286 -- No direct error handling is required within this function. Any possible
287 -- errors within this procedure will be a PL/SQL value error due to
288 -- conversion of datatypes or data lengths.
289 --
290 -- Developer Implementation Notes:
291 -- None.
292 --
293 -- Access Status:
294 -- Internal Row Handler Use Only.
295 --
296 -- {End Of Comments}
297 -- ----------------------------------------------------------------------------
298 Procedure convert_defs
299 (p_rec in out nocopy irc_irt_shd.g_rec_type
300 ) is
301 --
302 Begin
303 --
304 -- We must now examine each argument value in the
305 -- p_rec plsql record structure
306 -- to see if a system default is being used. If a system default
307 -- is being used then we must set to the 'current' argument value.
308 --
309 If (p_rec.source_lang = hr_api.g_varchar2) then
310 p_rec.source_lang :=
311 irc_irt_shd.g_old_rec.source_lang;
312 End If;
313 If (p_rec.site_name = hr_api.g_varchar2) then
314 p_rec.site_name :=
315 irc_irt_shd.g_old_rec.site_name;
316 End If;
317 If (p_rec.redirection_url = hr_api.g_varchar2) then
318 p_rec.redirection_url :=
319 irc_irt_shd.g_old_rec.redirection_url;
320 g_redirection_url := false;
321 Else
322 g_redirection_url := true;
323 End If;
324 If (p_rec.posting_url = hr_api.g_varchar2) then
325 p_rec.posting_url :=
326 irc_irt_shd.g_old_rec.posting_url;
327 g_posting_url := false;
328 Else
329 g_posting_url := true;
330 End If;
331 --
332 End convert_defs;
333 --
334 -- ----------------------------------------------------------------------------
335 -- |---------------------------------< upd >----------------------------------|
336 -- ----------------------------------------------------------------------------
337 Procedure upd
338 (p_rec in out nocopy irc_irt_shd.g_rec_type
339 ) is
340 --
341 l_proc varchar2(72) := g_package||'upd';
342 --
343 Begin
344 hr_utility.set_location('Entering:'||l_proc, 5);
345 --
346 -- We must lock the row which we need to update.
347 --
348 irc_irt_shd.lck
349 (p_rec.recruiting_site_id
350 ,p_rec.language
351 );
352 --
353 -- 1. During an update system defaults are used to determine if
354 -- arguments have been defaulted or not. We must therefore
355 -- derive the full record structure values to be updated.
356 --
357 -- 2. Call the supporting update validate operations.
358 --
359 convert_defs(p_rec);
360 irc_irt_bus.update_validate
361 (p_rec
362 );
363 --
364 -- Call to raise any errors on multi-message list
365 hr_multi_message.end_validation_set;
366 --
367 -- Call the supporting pre-update operation
368 --
369 irc_irt_upd.pre_update(p_rec);
370 --
371 -- Update the row.
372 --
373 irc_irt_upd.update_dml(p_rec);
374 --
375 -- Call the supporting post-update operation
376 --
377 irc_irt_upd.post_update
378 (p_rec
379 );
380 --
381 -- Call to raise any errors on multi-message list
382 hr_multi_message.end_validation_set;
383 End upd;
384 --
385 -- ----------------------------------------------------------------------------
386 -- |---------------------------------< upd >----------------------------------|
387 -- ----------------------------------------------------------------------------
388 Procedure upd
389 (p_recruiting_site_id in number
390 ,p_language in varchar2
391 ,p_source_lang in varchar2 default hr_api.g_varchar2
392 ,p_site_name in varchar2 default hr_api.g_varchar2
393 ,p_redirection_url in varchar2 default hr_api.g_varchar2
394 ,p_posting_url in varchar2 default hr_api.g_varchar2
395 ) is
396 --
397 l_rec irc_irt_shd.g_rec_type;
398 l_proc varchar2(72) := g_package||'upd';
399 --
400 Begin
401 hr_utility.set_location('Entering:'||l_proc, 5);
402 --
403 -- Call conversion function to turn arguments into the
404 -- l_rec structure.
405 --
406 l_rec :=
407 irc_irt_shd.convert_args
408 (p_recruiting_site_id
409 ,p_language
410 ,p_source_lang
411 ,p_site_name
412 ,p_redirection_url
413 ,p_posting_url
414 );
415 --
416 -- Having converted the arguments into the
417 -- plsql record structure we call the corresponding record
418 -- business process.
419 --
420 irc_irt_upd.upd
421 (l_rec
422 );
423 --
424 --
425 hr_utility.set_location(' Leaving:'||l_proc, 10);
426 End upd;
427 --
428 -- ----------------------------------------------------------------------------
429 -- |------------------------------< upd_tl >----------------------------------|
430 -- ----------------------------------------------------------------------------
431 Procedure upd_tl
432 (p_language_code in varchar2
433 ,p_recruiting_site_id in number
434 ,p_site_name in varchar2 default hr_api.g_varchar2
435 ,p_redirection_url in varchar2 default hr_api.g_varchar2
436 ,p_posting_url in varchar2 default hr_api.g_varchar2
437 ) is
438 --
439 -- Cursor to obtain the translation rows where the language or
440 -- source_lang match the specified language.
441 --
442 cursor csr_upd_langs is
443 select irt.language
444 from irc_all_recruiting_sites_tl irt
445 where irt.recruiting_site_id = p_recruiting_site_id
446 and p_language_code in (irt.language
447 ,irt.source_lang);
448 --
449 l_proc varchar2(72) := g_package||'upd_tl';
450 --
451 Begin
452 hr_utility.set_location('Entering:'||l_proc,10);
453 --
454 -- Update the translated values for every matching row
455 -- setting SOURCE_LANG to the specified language.
456 --
457 for l_lang in csr_upd_langs loop
458 irc_irt_upd.upd
459 (p_recruiting_site_id => p_recruiting_site_id
460 ,p_language => l_lang.language
461 ,p_source_lang => p_language_code
462 ,p_site_name => p_site_name
463 ,p_redirection_url => p_redirection_url
464 ,p_posting_url => p_posting_url
465 );
466 end loop;
467 --
468 hr_utility.set_location(' Leaving:'||l_proc,20);
469 End upd_tl;
470 --
471 end irc_irt_upd;