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