1 Package Body irc_ipt_upd as
2 /* $Header: iriptrhi.pkb 120.0 2005/07/26 15:10:09 mbocutt noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_ipt_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
55 (p_rec in out nocopy irc_ipt_shd.g_rec_type
56 ) is
57 --
58 l_proc varchar2(72) := g_package||'update_dml';
59 --
60 Begin
61 hr_utility.set_location('Entering:'||l_proc, 5);
62 --
63 --
64 --
65 --
66 -- Update the irc_posting_contents_tl Row
67 --
68 update irc_posting_contents_tl
69 set
70 posting_content_id = p_rec.posting_content_id
71 ,language = p_rec.language
72 ,source_language = p_rec.source_language
73 ,name = p_rec.name
74 ,org_name = p_rec.org_name
75 ,org_description = empty_clob()
76 ,job_title = p_rec.job_title
77 ,brief_description = empty_clob()
78 ,detailed_description = empty_clob()
79 ,job_requirements = empty_clob()
80 ,additional_details = empty_clob()
81 ,how_to_apply = empty_clob()
82 ,image_url = empty_clob()
83 ,image_url_alt = empty_clob()
84 where posting_content_id = p_rec.posting_content_id
85 and language = p_rec.language;
86
87 irc_ipt_shd.clob_dml(p_rec => p_rec
88 ,p_api_updating => true);
89 --
90 --
91 --
92 hr_utility.set_location(' Leaving:'||l_proc, 10);
93 --
94 Exception
95 When hr_api.check_integrity_violated Then
96 -- A check constraint has been violated
97 --
98 irc_ipt_shd.constraint_error
99 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
100 When hr_api.parent_integrity_violated Then
101 -- Parent integrity has been violated
102 --
103 irc_ipt_shd.constraint_error
104 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
105 When hr_api.unique_integrity_violated Then
106 -- Unique integrity has been violated
107 --
108 irc_ipt_shd.constraint_error
109 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
110 When Others Then
111 --
112 Raise;
113 End update_dml;
114 --
115 -- ----------------------------------------------------------------------------
116 -- |------------------------------< pre_update >------------------------------|
117 -- ----------------------------------------------------------------------------
118 -- {Start Of Comments}
119 --
120 -- Description:
121 -- This private procedure contains any processing which is required before
122 -- the update dml.
123 --
124 -- Prerequisites:
125 -- This is an internal procedure which is called from the upd procedure.
126 --
127 -- In Parameters:
128 -- A Pl/Sql record structure.
129 --
130 -- Post Success:
131 -- Processing continues.
132 --
133 -- Post Failure:
134 -- If an error has occurred, an error message and exception wil be raised
135 -- but not handled.
136 --
137 -- Developer Implementation Notes:
138 -- Any pre-processing required before the update dml is issued should be
139 -- coded within this procedure. It is important to note that any 3rd party
140 -- maintenance should be reviewed before placing in this procedure.
141 --
142 -- Access Status:
143 -- Internal Row Handler Use Only.
144 --
145 -- {End Of Comments}
146 -- ----------------------------------------------------------------------------
147 Procedure pre_update
148 (p_rec in irc_ipt_shd.g_rec_type
149 ) is
150 --
151 l_proc varchar2(72) := g_package||'pre_update';
152 --
153 Begin
154 hr_utility.set_location('Entering:'||l_proc, 5);
155 --
156 hr_utility.set_location(' Leaving:'||l_proc, 10);
157 End pre_update;
158 --
159 -- ----------------------------------------------------------------------------
160 -- |-----------------------------< post_update >------------------------------|
161 -- ----------------------------------------------------------------------------
162 -- {Start Of Comments}
163 --
164 -- Description:
165 -- This private procedure contains any processing which is required after
166 -- the update dml.
167 --
168 -- Prerequisites:
169 -- This is an internal procedure which is called from the upd procedure.
170 --
171 -- In Parameters:
172 -- A Pl/Sql record structure.
173 --
174 -- Post Success:
175 -- Processing continues.
176 --
177 -- Post Failure:
178 -- If an error has occurred, an error message and exception will be raised
179 -- but not handled.
180 --
181 -- Developer Implementation Notes:
182 -- Any post-processing required after the update dml is issued should be
183 -- coded within this procedure. It is important to note that any 3rd party
184 -- maintenance should be reviewed before placing in this procedure.
185 --
186 -- Access Status:
187 -- Internal Row Handler Use Only.
188 --
189 -- {End Of Comments}
190 -- ----------------------------------------------------------------------------
191 Procedure post_update
192 (p_rec in irc_ipt_shd.g_rec_type
193 ) is
194 --
195 l_proc varchar2(72) := g_package||'post_update';
196 --
197 Begin
198 hr_utility.set_location('Entering:'||l_proc, 5);
199 begin
200 --
201 irc_ipt_rku.after_update
202 (p_posting_content_id
203 => p_rec.posting_content_id
204 ,p_language
205 => p_rec.language
206 ,p_source_language
207 => p_rec.source_language
208 ,p_name
209 => p_rec.name
210 ,p_org_name
211 => p_rec.org_name
212 ,p_org_description
213 => p_rec.org_description
214 ,p_job_title
215 => p_rec.job_title
216 ,p_brief_description
217 => p_rec.brief_description
218 ,p_detailed_description
219 => p_rec.detailed_description
220 ,p_job_requirements
221 => p_rec.job_requirements
222 ,p_additional_details
223 => p_rec.additional_details
224 ,p_how_to_apply
225 => p_rec.how_to_apply
226 ,p_benefit_info
227 => p_rec.benefit_info
228 ,p_image_url
229 => p_rec.image_url
230 ,p_image_url_alt
231 => p_rec.image_url_alt
232 ,p_source_language_o
233 => irc_ipt_shd.g_old_rec.source_language
234 ,p_name_o
235 => irc_ipt_shd.g_old_rec.name
236 ,p_org_name_o
237 => irc_ipt_shd.g_old_rec.org_name
238 ,p_org_description_o
239 => irc_ipt_shd.g_old_rec.org_description
240 ,p_job_title_o
241 => irc_ipt_shd.g_old_rec.job_title
242 ,p_brief_description_o
243 => irc_ipt_shd.g_old_rec.brief_description
244 ,p_detailed_description_o
245 => irc_ipt_shd.g_old_rec.detailed_description
246 ,p_job_requirements_o
247 => irc_ipt_shd.g_old_rec.job_requirements
248 ,p_additional_details_o
249 => irc_ipt_shd.g_old_rec.additional_details
250 ,p_how_to_apply_o
251 => irc_ipt_shd.g_old_rec.how_to_apply
252 ,p_benefit_info_o
253 => irc_ipt_shd.g_old_rec.benefit_info
254 ,p_image_url_o
255 => irc_ipt_shd.g_old_rec.image_url
256 ,p_image_url_alt_o
257 => irc_ipt_shd.g_old_rec.image_url_alt
258 );
259 --
260 exception
261 --
262 when hr_api.cannot_find_prog_unit then
263 --
264 hr_api.cannot_find_prog_unit_error
265 (p_module_name => 'IRC_POSTING_CONTENTS_TL'
266 ,p_hook_type => 'AU');
267 --
268 end;
269 --
270 hr_utility.set_location(' Leaving:'||l_proc, 10);
271 End post_update;
272 --
273 -- ----------------------------------------------------------------------------
274 -- |-----------------------------< convert_defs >-----------------------------|
275 -- ----------------------------------------------------------------------------
276 -- {Start Of Comments}
277 --
278 -- Description:
279 -- The Convert_Defs procedure has one very important function:
280 -- It must return the record structure for the row with all system defaulted
281 -- values converted into its corresponding parameter value for update. When
282 -- we attempt to update a row through the Upd process , certain
283 -- parameters can be defaulted which enables flexibility in the calling of
284 -- the upd process (e.g. only attributes which need to be updated need to be
285 -- specified). For the upd process to determine which attributes
286 -- have NOT been specified we need to check if the parameter has a reserved
287 -- system default value. Therefore, for all parameters which have a
288 -- corresponding reserved system default mechanism specified we need to
289 -- check if a system default is being used. If a system default is being
290 -- used then we convert the defaulted value into its corresponding attribute
291 -- value held in the g_old_rec data structure.
292 --
293 -- Prerequisites:
294 -- This private function can only be called from the upd process.
295 --
296 -- In Parameters:
297 -- A Pl/Sql record structure.
298 --
299 -- Post Success:
300 -- The record structure will be returned with all system defaulted parameter
301 -- values converted into its current row attribute value.
302 --
303 -- Post Failure:
304 -- No direct error handling is required within this function. Any possible
305 -- errors within this procedure will be a PL/SQL value error due to
306 -- conversion of datatypes or data lengths.
307 --
308 -- Developer Implementation Notes:
309 -- None.
310 --
311 -- Access Status:
312 -- Internal Row Handler Use Only.
313 --
314 -- {End Of Comments}
315 -- ----------------------------------------------------------------------------
316 Procedure convert_defs
317 (p_rec in out nocopy irc_ipt_shd.g_rec_type
318 ) is
319 --
320 Begin
321 --
322 -- We must now examine each argument value in the
323 -- p_rec plsql record structure
324 -- to see if a system default is being used. If a system default
325 -- is being used then we must set to the 'current' argument value.
326 --
327 If (p_rec.source_language = hr_api.g_varchar2) then
328 p_rec.source_language :=
329 irc_ipt_shd.g_old_rec.source_language;
330 End If;
331 If (p_rec.name = hr_api.g_varchar2) then
332 p_rec.name :=
333 irc_ipt_shd.g_old_rec.name;
334 End If;
335 If (p_rec.org_name = hr_api.g_varchar2) then
336 p_rec.org_name :=
337 irc_ipt_shd.g_old_rec.org_name;
338 End If;
339 If (p_rec.org_description = hr_api.g_varchar2) then
340 p_rec.org_description :=
341 irc_ipt_shd.g_old_rec.org_description;
342 else
343 irc_ipt_shd.g_org_description_upd := true;
344 End If;
345 If (p_rec.job_title = hr_api.g_varchar2) then
346 p_rec.job_title :=
347 irc_ipt_shd.g_old_rec.job_title;
348 End If;
349 If (p_rec.brief_description = hr_api.g_varchar2) then
350 p_rec.brief_description :=
351 irc_ipt_shd.g_old_rec.brief_description;
352 else
353 irc_ipt_shd.g_brief_description_upd := true;
354 End If;
355 If (p_rec.detailed_description = hr_api.g_varchar2) then
356 p_rec.detailed_description :=
357 irc_ipt_shd.g_old_rec.detailed_description;
358 else
359 irc_ipt_shd.g_detailed_description_upd := true;
360 End If;
361 If (p_rec.job_requirements = hr_api.g_varchar2) then
362 p_rec.job_requirements :=
363 irc_ipt_shd.g_old_rec.job_requirements;
364 else
365 irc_ipt_shd.g_job_requirements_upd := true;
366 End If;
367 If (p_rec.additional_details = hr_api.g_varchar2) then
368 p_rec.additional_details :=
369 irc_ipt_shd.g_old_rec.additional_details;
370 else
371 irc_ipt_shd.g_additional_details_upd := true;
372 End If;
373 If (p_rec.how_to_apply = hr_api.g_varchar2) then
374 p_rec.how_to_apply :=
375 irc_ipt_shd.g_old_rec.how_to_apply;
376 else
377 irc_ipt_shd.g_how_to_apply_upd := true;
378 End If;
379 If (p_rec.benefit_info = hr_api.g_varchar2) then
380 p_rec.benefit_info :=
381 irc_ipt_shd.g_old_rec.benefit_info;
382 else
383 irc_ipt_shd.g_benefit_info_upd := true;
384 End If;
385 If (p_rec.image_url = hr_api.g_varchar2) then
386 p_rec.image_url :=
387 irc_ipt_shd.g_old_rec.image_url;
388 else
389 irc_ipt_shd.g_image_url_upd := true;
390 End If;
391 If (p_rec.image_url_alt = hr_api.g_varchar2) then
392 p_rec.image_url_alt :=
393 irc_ipt_shd.g_old_rec.image_url_alt;
394 else
395 irc_ipt_shd.g_image_url_alt_upd := true;
396 End If;
397 --
398 End convert_defs;
399 --
400 -- ----------------------------------------------------------------------------
401 -- |---------------------------------< upd >----------------------------------|
402 -- ----------------------------------------------------------------------------
403 Procedure upd
404 (p_rec in out nocopy irc_ipt_shd.g_rec_type
405 ) is
406 --
407 l_proc varchar2(72) := g_package||'upd';
408 --
409 Begin
410 hr_utility.set_location('Entering:'||l_proc, 5);
411 --
412 -- We must lock the row which we need to update.
413 --
414 irc_ipt_shd.lck
415 (p_rec.posting_content_id
416 ,p_rec.language
417 );
418 --
419 -- 1. During an update system defaults are used to determine if
420 -- arguments have been defaulted or not. We must therefore
421 -- derive the full record structure values to be updated.
422 --
423 -- 2. Call the supporting update validate operations.
424 --
425 convert_defs(p_rec);
426 irc_ipt_bus.update_validate
427 (p_rec
428 );
429 hr_multi_message.end_validation_set;
430 --
431 -- Call the supporting pre-update operation
432 --
433 irc_ipt_upd.pre_update(p_rec);
434 --
435 -- Update the row.
436 --
437 irc_ipt_upd.update_dml(p_rec);
438 --
439 -- Call the supporting post-update operation
440 --
441 irc_ipt_upd.post_update
442 (p_rec
443 );
444 hr_multi_message.end_validation_set;
445 End upd;
446 --
447 -- ----------------------------------------------------------------------------
448 -- |---------------------------------< upd >----------------------------------|
449 -- ----------------------------------------------------------------------------
450 Procedure upd
451 (p_posting_content_id in number
452 ,p_language in varchar2
453 ,p_source_language in varchar2 default hr_api.g_varchar2
454 ,p_name in varchar2 default hr_api.g_varchar2
455 ,p_org_name in varchar2 default hr_api.g_varchar2
456 ,p_org_description in varchar2 default hr_api.g_varchar2
457 ,p_job_title in varchar2 default hr_api.g_varchar2
458 ,p_brief_description in varchar2 default hr_api.g_varchar2
459 ,p_detailed_description in varchar2 default hr_api.g_varchar2
460 ,p_job_requirements in varchar2 default hr_api.g_varchar2
461 ,p_additional_details in varchar2 default hr_api.g_varchar2
462 ,p_how_to_apply in varchar2 default hr_api.g_varchar2
463 ,p_benefit_info in varchar2 default hr_api.g_varchar2
464 ,p_image_url in varchar2 default hr_api.g_varchar2
465 ,p_image_url_alt in varchar2 default hr_api.g_varchar2
466 ) is
467 --
468 l_rec irc_ipt_shd.g_rec_type;
469 l_proc varchar2(72) := g_package||'upd';
470 --
471 Begin
472 hr_utility.set_location('Entering:'||l_proc, 5);
473 --
474 -- Call conversion function to turn arguments into the
475 -- l_rec structure.
476 --
477 l_rec :=
478 irc_ipt_shd.convert_args
479 (p_posting_content_id
480 ,p_language
481 ,p_source_language
482 ,p_name
483 ,p_org_name
484 ,p_org_description
485 ,p_job_title
486 ,p_brief_description
487 ,p_detailed_description
488 ,p_job_requirements
489 ,p_additional_details
490 ,p_how_to_apply
491 ,p_benefit_info
492 ,p_image_url
493 ,p_image_url_alt
494 );
495 --
496 -- Having converted the arguments into the
497 -- plsql record structure we call the corresponding record
498 -- business process.
499 --
500 irc_ipt_upd.upd
501 (l_rec
502 );
503 --
504 --
505 hr_utility.set_location(' Leaving:'||l_proc, 10);
506 End upd;
507 --
508 -- ----------------------------------------------------------------------------
509 -- |------------------------------< upd_tl >----------------------------------|
510 -- ----------------------------------------------------------------------------
511 Procedure upd_tl
512 (p_language_code in varchar2
513 ,p_posting_content_id in number
514 ,p_name in varchar2 default hr_api.g_varchar2
515 ,p_org_name in varchar2 default hr_api.g_varchar2
516 ,p_org_description in varchar2 default hr_api.g_varchar2
517 ,p_job_title in varchar2 default hr_api.g_varchar2
518 ,p_brief_description in varchar2 default hr_api.g_varchar2
519 ,p_detailed_description in varchar2 default hr_api.g_varchar2
520 ,p_job_requirements in varchar2 default hr_api.g_varchar2
521 ,p_additional_details in varchar2 default hr_api.g_varchar2
522 ,p_how_to_apply in varchar2 default hr_api.g_varchar2
523 ,p_benefit_info in varchar2 default hr_api.g_varchar2
524 ,p_image_url in varchar2 default hr_api.g_varchar2
525 ,p_image_url_alt in varchar2 default hr_api.g_varchar2
526 ) is
527 --
528 -- Cursor to obtain the translation rows where the language or
529 -- source_lang match the specified language.
530 --
531 cursor csr_upd_langs is
532 select ipt.language
533 from irc_posting_contents_tl ipt
534 where ipt.posting_content_id = p_posting_content_id
535 and p_language_code in (ipt.language
536 ,ipt.source_language);
537 --
538 l_proc varchar2(72) := g_package||'upd_tl';
539 --
540 Begin
541 hr_utility.set_location('Entering:'||l_proc,10);
542 --
543 -- Update the translated values for every matching row
544 -- setting SOURCE_LANG to the specified language.
545 --
546 for l_lang in csr_upd_langs loop
547 irc_ipt_upd.upd
548 (p_posting_content_id => p_posting_content_id
549 ,p_language => l_lang.language
550 ,p_source_language => p_language_code
551 ,p_name => p_name
552 ,p_org_name => p_org_name
553 ,p_org_description => p_org_description
554 ,p_job_title => p_job_title
555 ,p_brief_description => p_brief_description
556 ,p_detailed_description => p_detailed_description
557 ,p_job_requirements => p_job_requirements
558 ,p_additional_details => p_additional_details
559 ,p_how_to_apply => p_how_to_apply
560 ,p_benefit_info => p_benefit_info
561 ,p_image_url => p_image_url
562 ,p_image_url_alt => p_image_url_alt
563 );
564 end loop;
565 --
566 hr_utility.set_location(' Leaving:'||l_proc,20);
567 End upd_tl;
568 --
569 end irc_ipt_upd;