[Home] [Help]
PACKAGE BODY: APPS.IRC_IDT_SHD
Source
1 Package Body irc_idt_shd as
2 /* $Header: iridtrhi.pkb 120.0 2005/07/26 15:07:27 mbocutt noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_idt_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< constraint_error >---------------------------|
12 -- ----------------------------------------------------------------------------
13 Procedure constraint_error
14 (p_constraint_name in all_constraints.constraint_name%TYPE
15 ) Is
16 --
17 l_proc varchar2(72) := g_package||'constraint_error';
18 --
19 Begin
20 --
21 If (p_constraint_name = 'IRC_DEFAULT_POSTINGS_TL_PK') Then
22 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
23 fnd_message.set_token('PROCEDURE', l_proc);
24 fnd_message.set_token('STEP','5');
25 fnd_message.raise_error;
26 Else
27 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
28 fnd_message.set_token('PROCEDURE', l_proc);
29 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
30 fnd_message.raise_error;
31 End If;
32 --
33 End constraint_error;
34 --
35 -- ----------------------------------------------------------------------------
36 -- |-----------------------------< api_updating >-----------------------------|
37 -- ----------------------------------------------------------------------------
38 Function api_updating
39 (p_default_posting_id in number
40 ,p_language in varchar2
41 ) Return Boolean Is
42 --
43 --
44 -- Cursor selects the 'current' row from the HR Schema
45 --
46 Cursor C_Sel1 is
47 select
48 default_posting_id
49 ,language
50 ,source_language
51 ,org_name
52 ,org_description
53 ,''
54 ,job_title
55 ,brief_description
56 ,''
57 ,detailed_description
58 ,''
59 ,job_requirements
60 ,''
61 ,additional_details
62 ,''
63 ,how_to_apply
64 ,''
65 ,image_url
66 ,''
67 ,image_url_alt
68 ,''
69 from irc_default_postings_tl
70 where default_posting_id = p_default_posting_id
71 and language = p_language;
72 --
73 l_fct_ret boolean;
74 --
75 Begin
76 --
77 If (p_default_posting_id is null or
78 p_language is null
79 ) Then
80 --
81 -- One of the primary key arguments is null therefore we must
82 -- set the returning function value to false
83 --
84 l_fct_ret := false;
85 Else
86 If (p_default_posting_id
87 = irc_idt_shd.g_old_rec.default_posting_id and
88 p_language
89 = irc_idt_shd.g_old_rec.language
90 ) Then
91 --
92 -- The g_old_rec is current therefore we must
93 -- set the returning function to true
94 --
95 l_fct_ret := true;
96 Else
97 --
98 -- Select the current row into g_old_rec
99 --
100 Open C_Sel1;
101 Fetch C_Sel1 Into irc_idt_shd.g_old_rec;
102 If C_Sel1%notfound Then
103 Close C_Sel1;
104 --
105 -- The primary key is invalid therefore we must error
106 --
107 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
108 fnd_message.raise_error;
109 End If;
110 Close C_Sel1;
111 --
112 irc_idt_shd.g_old_rec.org_description
113 :=dbms_lob.substr(irc_idt_shd.g_old_rec.org_description_c);
114 irc_idt_shd.g_old_rec.brief_description
115 :=dbms_lob.substr(irc_idt_shd.g_old_rec.brief_description_c);
116 irc_idt_shd.g_old_rec.detailed_description
117 :=dbms_lob.substr(irc_idt_shd.g_old_rec.detailed_description_c);
118 irc_idt_shd.g_old_rec.job_requirements
119 :=dbms_lob.substr(irc_idt_shd.g_old_rec.job_requirements_c);
120 irc_idt_shd.g_old_rec.additional_details
121 :=dbms_lob.substr(irc_idt_shd.g_old_rec.additional_details_c);
122 irc_idt_shd.g_old_rec.how_to_apply
123 :=dbms_lob.substr(irc_idt_shd.g_old_rec.how_to_apply_c);
124 irc_idt_shd.g_old_rec.image_url
125 :=dbms_lob.substr(irc_idt_shd.g_old_rec.image_url_c);
126 irc_idt_shd.g_old_rec.image_url_alt
127 :=dbms_lob.substr(irc_idt_shd.g_old_rec.image_url_alt_c);
128 l_fct_ret := true;
129 End If;
130 End If;
131 Return (l_fct_ret);
132 --
133 End api_updating;
134 --
135 -- ----------------------------------------------------------------------------
136 -- |---------------------------------< lck >----------------------------------|
137 -- ----------------------------------------------------------------------------
138 Procedure lck
139 (p_default_posting_id in number
140 ,p_language in varchar2
141 ) is
142 --
143 -- Cursor selects the 'current' row from the HR Schema
144 --
145 Cursor C_Sel1 is
146 select
147 default_posting_id
148 ,language
149 ,source_language
150 ,org_name
151 ,org_description
152 ,''
153 ,job_title
154 ,brief_description
155 ,''
156 ,detailed_description
157 ,''
158 ,job_requirements
159 ,''
160 ,additional_details
161 ,''
162 ,how_to_apply
163 ,''
164 ,image_url
165 ,''
166 ,image_url_alt
167 ,''
168 from irc_default_postings_tl
169 where default_posting_id = p_default_posting_id
170 and language = p_language
171 for update nowait;
172 --
173 l_proc varchar2(72) := g_package||'lck';
174 --
175 Begin
176 hr_utility.set_location('Entering:'||l_proc, 5);
177 --
178 hr_api.mandatory_arg_error
179 (p_api_name => l_proc
180 ,p_argument => 'DEFAULT_POSTING_ID'
181 ,p_argument_value => p_default_posting_id
182 );
183 hr_utility.set_location(l_proc,6);
184 hr_api.mandatory_arg_error
185 (p_api_name => l_proc
186 ,p_argument => 'LANGUAGE'
187 ,p_argument_value => p_language
188 );
189 --
190 Open C_Sel1;
191 Fetch C_Sel1 Into irc_idt_shd.g_old_rec;
192 If C_Sel1%notfound then
193 Close C_Sel1;
194 --
195 -- The primary key is invalid therefore we must error
196 --
197 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
198 fnd_message.raise_error;
199 End If;
200 Close C_Sel1;
201 irc_idt_shd.g_old_rec.org_description
202 :=dbms_lob.substr(irc_idt_shd.g_old_rec.org_description_c);
203 irc_idt_shd.g_old_rec.brief_description
204 :=dbms_lob.substr(irc_idt_shd.g_old_rec.brief_description_c);
205 irc_idt_shd.g_old_rec.detailed_description
206 :=dbms_lob.substr(irc_idt_shd.g_old_rec.detailed_description_c);
207 irc_idt_shd.g_old_rec.job_requirements
208 :=dbms_lob.substr(irc_idt_shd.g_old_rec.job_requirements_c);
209 irc_idt_shd.g_old_rec.additional_details
210 :=dbms_lob.substr(irc_idt_shd.g_old_rec.additional_details_c);
211 irc_idt_shd.g_old_rec.how_to_apply
212 :=dbms_lob.substr(irc_idt_shd.g_old_rec.how_to_apply_c);
213 irc_idt_shd.g_old_rec.image_url
214 :=dbms_lob.substr(irc_idt_shd.g_old_rec.image_url_c);
215 irc_idt_shd.g_old_rec.image_url_alt
216 :=dbms_lob.substr(irc_idt_shd.g_old_rec.image_url_alt_c);
217 --
218 --
219 hr_utility.set_location(' Leaving:'||l_proc, 10);
220 --
221 -- We need to trap the ORA LOCK exception
222 --
223 Exception
224 When HR_Api.Object_Locked then
225 --
226 -- The object is locked therefore we need to supply a meaningful
227 -- error message.
228 --
229 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
230 fnd_message.set_token('TABLE_NAME', 'irc_default_postings_tl');
231 fnd_message.raise_error;
232 End lck;
233 --
234 -- ----------------------------------------------------------------------------
235 -- |----------------------------< add_language >------------------------------|
236 -- ----------------------------------------------------------------------------
237 --
238 Procedure add_language is
239 begin
240 delete from IRC_DEFAULT_POSTINGS_TL T
241 where not exists
242 (select NULL
243 from IRC_DEFAULT_POSTINGS B
244 where B.DEFAULT_POSTING_ID = T.DEFAULT_POSTING_ID
245 );
246
247 update IRC_DEFAULT_POSTINGS_TL T set (
248 ORG_NAME,
249 ORG_DESCRIPTION,
250 JOB_TITLE,
251 BRIEF_DESCRIPTION,
252 DETAILED_DESCRIPTION,
253 JOB_REQUIREMENTS,
254 ADDITIONAL_DETAILS,
255 HOW_TO_APPLY
256 ) = (select
257 B.ORG_NAME,
258 B.ORG_DESCRIPTION,
259 B.JOB_TITLE,
260 B.BRIEF_DESCRIPTION,
261 B.DETAILED_DESCRIPTION,
262 B.JOB_REQUIREMENTS,
263 B.ADDITIONAL_DETAILS,
264 B.HOW_TO_APPLY
265 from IRC_DEFAULT_POSTINGS_TL B
266 where B.DEFAULT_POSTING_ID = T.DEFAULT_POSTING_ID
267 and B.LANGUAGE = T.SOURCE_LANGUAGE)
268 where (
269 T.DEFAULT_POSTING_ID,
270 T.LANGUAGE
271 ) in (select
272 SUBT.DEFAULT_POSTING_ID,
273 SUBT.LANGUAGE
274 from IRC_DEFAULT_POSTINGS_TL SUBB, IRC_DEFAULT_POSTINGS_TL SUBT
275 where SUBB.DEFAULT_POSTING_ID = SUBT.DEFAULT_POSTING_ID
276 and SUBB.LANGUAGE = SUBT.SOURCE_LANGUAGE
277 and (SUBB.ORG_NAME <> SUBT.ORG_NAME
278 or (SUBB.ORG_NAME is null and SUBT.ORG_NAME is not null)
279 or (SUBB.ORG_NAME is not null and SUBT.ORG_NAME is null)
280 or dbms_lob.compare(SUBB.ORG_DESCRIPTION, SUBT.ORG_DESCRIPTION) = 0
281 or (SUBB.ORG_DESCRIPTION is null and SUBT.ORG_DESCRIPTION is not null)
282 or (SUBB.ORG_DESCRIPTION is not null and SUBT.ORG_DESCRIPTION is null)
283 or SUBB.JOB_TITLE <> SUBT.JOB_TITLE
284 or (SUBB.JOB_TITLE is null and SUBT.JOB_TITLE is not null)
285 or (SUBB.JOB_TITLE is not null and SUBT.JOB_TITLE is null)
286 or dbms_lob.compare(SUBB.BRIEF_DESCRIPTION, SUBT.BRIEF_DESCRIPTION) = 0
287 or (SUBB.BRIEF_DESCRIPTION is null and SUBT.BRIEF_DESCRIPTION is not null)
288 or (SUBB.BRIEF_DESCRIPTION is not null and SUBT.BRIEF_DESCRIPTION is null)
289 or dbms_lob.compare(SUBB.DETAILED_DESCRIPTION, SUBT.DETAILED_DESCRIPTION) = 0
290 or (SUBB.DETAILED_DESCRIPTION is null and SUBT.DETAILED_DESCRIPTION is not null)
291 or (SUBB.DETAILED_DESCRIPTION is not null and SUBT.DETAILED_DESCRIPTION is null)
292 or dbms_lob.compare(SUBB.JOB_REQUIREMENTS, SUBT.JOB_REQUIREMENTS) = 0
293 or (SUBB.JOB_REQUIREMENTS is null and SUBT.JOB_REQUIREMENTS is not null)
294 or (SUBB.JOB_REQUIREMENTS is not null and SUBT.JOB_REQUIREMENTS is null)
295 or dbms_lob.compare(SUBB.ADDITIONAL_DETAILS, SUBT.ADDITIONAL_DETAILS) = 0
296 or (SUBB.ADDITIONAL_DETAILS is null and SUBT.ADDITIONAL_DETAILS is not null)
297 or (SUBB.ADDITIONAL_DETAILS is not null and SUBT.ADDITIONAL_DETAILS is null)
298 or dbms_lob.compare(SUBB.HOW_TO_APPLY, SUBT.HOW_TO_APPLY) = 0
299 or (SUBB.HOW_TO_APPLY is null and SUBT.HOW_TO_APPLY is not null)
300 or (SUBB.HOW_TO_APPLY is not null and SUBT.HOW_TO_APPLY is null)
301 ));
302
303 insert into IRC_DEFAULT_POSTINGS_TL (
304 HOW_TO_APPLY,
305 IMAGE_URL,
306 IMAGE_URL_ALT,
307 SOURCE_LANGUAGE,
308 DEFAULT_POSTING_ID,
309 JOB_REQUIREMENTS,
310 ADDITIONAL_DETAILS,
311 DETAILED_DESCRIPTION,
312 ORG_NAME,
313 ORG_DESCRIPTION,
314 JOB_TITLE,
315 BRIEF_DESCRIPTION,
316 LANGUAGE
317 ) select
318 B.HOW_TO_APPLY,
319 B.IMAGE_URL,
320 B.IMAGE_URL_ALT,
321 B.SOURCE_LANGUAGE,
322 B.DEFAULT_POSTING_ID,
323 B.JOB_REQUIREMENTS,
324 B.ADDITIONAL_DETAILS,
325 B.DETAILED_DESCRIPTION,
326 B.ORG_NAME,
327 B.ORG_DESCRIPTION,
328 B.JOB_TITLE,
329 B.BRIEF_DESCRIPTION,
330 L.LANGUAGE_CODE
331 from IRC_DEFAULT_POSTINGS_TL B, FND_LANGUAGES L
332 where L.INSTALLED_FLAG in ('I', 'B')
333 and B.LANGUAGE = userenv('LANG')
334 and not exists
335 (select NULL
336 from IRC_DEFAULT_POSTINGS_TL T
337 where T.DEFAULT_POSTING_ID = B.DEFAULT_POSTING_ID
338 and T.LANGUAGE = L.LANGUAGE_CODE);
339 end ADD_LANGUAGE;
340
341 --
342 -- ----------------------------------------------------------------------------
343 -- |-----------------------------< convert_args >-----------------------------|
344 -- ----------------------------------------------------------------------------
345 Function convert_args
346 (p_default_posting_id in number
347 ,p_language in varchar2
348 ,p_source_language in varchar2
349 ,p_org_name in varchar2
350 ,p_org_description in varchar2
351 ,p_job_title in varchar2
352 ,p_brief_description in varchar2
353 ,p_detailed_description in varchar2
354 ,p_job_requirements in varchar2
355 ,p_additional_details in varchar2
356 ,p_how_to_apply in varchar2
357 ,p_image_url in varchar2
358 ,p_image_url_alt in varchar2
359 )
360 Return g_rec_type is
361 --
362 l_rec g_rec_type;
363 --
364 Begin
365 --
366 -- Convert arguments into local l_rec structure.
367 --
368 l_rec.default_posting_id := p_default_posting_id;
369 l_rec.language := p_language;
370 l_rec.source_language := p_source_language;
371 l_rec.org_name := p_org_name;
372 l_rec.org_description := p_org_description;
373 l_rec.job_title := p_job_title;
374 l_rec.brief_description := p_brief_description;
375 l_rec.detailed_description := p_detailed_description;
376 l_rec.job_requirements := p_job_requirements;
377 l_rec.additional_details := p_additional_details;
378 l_rec.how_to_apply := p_how_to_apply;
379 l_rec.image_url := p_image_url;
380 l_rec.image_url_alt := p_image_url_alt;
381 --
382 -- Return the plsql record structure.
383 --
384 Return(l_rec);
385 --
386 End convert_args;
387 --
388 -- ----------------------------------------------------------------------------
389 -- |------------------------------< clob_dml >--------------------------------|
390 -- ----------------------------------------------------------------------------
391 Procedure clob_dml
392 (p_rec in out nocopy irc_idt_shd.g_rec_type
393 ,p_api_updating boolean
394 ) is
395 --
396 l_proc varchar2(72) := g_package||'clob_dml';
397 --
398 cursor get_rec is
399 select
400 org_description
401 ,brief_description
402 ,detailed_description
403 ,job_requirements
404 ,additional_details
405 ,how_to_apply
406 ,image_url
407 ,image_url_alt
408 from irc_default_postings_tl
409 where default_posting_id = p_rec.default_posting_id
410 and language = p_rec.language;
411 --
412 l_org_description clob;
413 l_brief_description clob;
414 l_detailed_description clob;
415 l_job_requirements clob;
419 l_image_url_alt clob;
416 l_additional_details clob;
417 l_how_to_apply clob;
418 l_image_url clob;
420 --
421 l_api_updating boolean;
422
423 Begin
424 hr_utility.set_location('Entering:'||l_proc, 5);
425 --
426 open get_rec;
427 fetch get_rec into
428 l_org_description
429 ,l_brief_description
430 ,l_detailed_description
431 ,l_job_requirements
432 ,l_additional_details
433 ,l_how_to_apply
434 ,l_image_url
435 ,l_image_url_alt;
436 if get_rec%notfound then
437 close get_rec;
438 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
439 fnd_message.raise_error;
440 end if;
441
442 close get_rec;
443 --
444 l_api_updating:=p_api_updating;
445 --
446 hr_utility.set_location(l_proc, 10);
447 --
448 if (g_org_description_upd
449 and l_api_updating
450 and dbms_lob.getlength(l_org_description)<=32767
451 and dbms_lob.instr(l_org_description,p_rec.org_description)<>1)
452 or (not l_api_updating
453 and p_rec.org_description is not null) then
454 dbms_lob.trim(l_org_description,0);
455 dbms_lob.write(l_org_description
456 ,length(p_rec.org_description)
457 ,1
458 ,p_rec.org_description);
459 end if;
460 --
461 hr_utility.set_location(l_proc, 20);
462 --
463 if (g_brief_description_upd
464 and l_api_updating
465 and dbms_lob.getlength(l_brief_description)<=32767
466 and dbms_lob.instr(l_brief_description,p_rec.brief_description)<>1)
467 or (not l_api_updating
468 and p_rec.brief_description is not null) then
469 dbms_lob.trim(l_brief_description,0);
470 dbms_lob.write(l_brief_description
471 ,length(p_rec.brief_description)
472 ,1
473 ,p_rec.brief_description);
474 end if;
475 --
476 hr_utility.set_location(l_proc, 30);
477 --
478 if (g_detailed_description_upd
479 and l_api_updating
480 and dbms_lob.getlength(l_detailed_description)<=32767
481 and dbms_lob.instr(l_detailed_description,p_rec.detailed_description)<>1)
482 or (not l_api_updating
483 and p_rec.detailed_description is not null) then
484 dbms_lob.trim(l_detailed_description,0);
485 dbms_lob.write(l_detailed_description
486 ,length(p_rec.detailed_description)
487 ,1
488 ,p_rec.detailed_description);
489 end if;
490 --
491 hr_utility.set_location(l_proc, 40);
492 --
493 if (g_job_requirements_upd
494 and l_api_updating
495 and dbms_lob.getlength(l_job_requirements)<=32767
496 and dbms_lob.instr(l_job_requirements,p_rec.job_requirements)<>1)
497 or (not l_api_updating
498 and p_rec.job_requirements is not null) then
499 dbms_lob.trim(l_job_requirements,0);
500 dbms_lob.write(l_job_requirements
501 ,length(p_rec.job_requirements)
502 ,1
503 ,p_rec.job_requirements);
504 end if;
505 --
506 hr_utility.set_location(l_proc, 50);
507 --
508 if (g_additional_details_upd
509 and l_api_updating
510 and dbms_lob.getlength(l_additional_details)<=32767
511 and dbms_lob.instr(l_additional_details,p_rec.additional_details)<>1)
512 or (not l_api_updating
513 and p_rec.additional_details is not null) then
514 dbms_lob.trim(l_additional_details,0);
515 dbms_lob.write(l_additional_details
516 ,length(p_rec.additional_details)
517 ,1
518 ,p_rec.additional_details);
519 end if;
520 --
521 hr_utility.set_location(l_proc, 60);
522 --
523 if (g_how_to_apply_upd
524 and l_api_updating
525 and dbms_lob.getlength(l_how_to_apply)<=32767
526 and dbms_lob.instr(l_how_to_apply,p_rec.how_to_apply)<>1)
527 or (not l_api_updating
528 and p_rec.how_to_apply is not null) then
529 dbms_lob.trim(l_how_to_apply,0);
530 dbms_lob.write(l_how_to_apply
531 ,length(p_rec.how_to_apply)
532 ,1
533 ,p_rec.how_to_apply);
534 end if;
535 --
536 hr_utility.set_location(l_proc, 70);
537 --
538 if (g_image_url_upd
539 and l_api_updating
540 and dbms_lob.getlength(l_image_url)<=32767
541 and dbms_lob.instr(l_image_url,p_rec.image_url)<>1)
542 or (not l_api_updating
543 and p_rec.image_url is not null) then
544 dbms_lob.trim(l_image_url,0);
545 dbms_lob.write(l_image_url
546 ,length(p_rec.image_url)
547 ,1
548 ,p_rec.image_url);
549 end if;
550 --
551 hr_utility.set_location(l_proc, 80);
552 --
553 if (g_image_url_alt_upd
554 and l_api_updating
555 and dbms_lob.getlength(l_image_url_alt)<=32767
556 and dbms_lob.instr(l_image_url_alt,p_rec.image_url_alt)<>1)
557 or (not l_api_updating
558 and p_rec.image_url_alt is not null) then
559 dbms_lob.trim(l_image_url_alt,0);
560 dbms_lob.write(l_image_url_alt
564 end if;
561 ,length(p_rec.image_url_alt)
562 ,1
563 ,p_rec.image_url_alt);
565 --
566 hr_utility.set_location(' Leaving:'||l_proc, 100);
567 --
568 Exception
569 When hr_api.check_integrity_violated Then
570 -- A check constraint has been violated
571 --
572 irc_idt_shd.constraint_error
576 --
573 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
574 When hr_api.parent_integrity_violated Then
575 -- Parent integrity has been violated
577 irc_idt_shd.constraint_error
578 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
579 When hr_api.unique_integrity_violated Then
580 -- Unique integrity has been violated
581 --
582 irc_idt_shd.constraint_error
583 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
584 When Others Then
585 --
586 Raise;
587 End clob_dml;
588 --
589 end irc_idt_shd;