[Home] [Help]
PACKAGE BODY: APPS.IRC_ISC_SHD
Source
1 Package Body irc_isc_shd as
2 /* $Header: iriscrhi.pkb 120.0 2005/07/26 15:11:17 mbocutt noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_isc_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------< return_api_dml_status >-------------------------|
12 -- ----------------------------------------------------------------------------
13 Function return_api_dml_status Return Boolean Is
14 --
15 Begin
16 --
17 Return (nvl(g_api_dml, false));
18 --
19 End return_api_dml_status;
20 --
21 -- ----------------------------------------------------------------------------
22 -- |---------------------------< constraint_error >---------------------------|
23 -- ----------------------------------------------------------------------------
24 Procedure constraint_error
25 (p_constraint_name in all_constraints.constraint_name%TYPE
26 ) Is
27 --
28 l_proc varchar2(72) := g_package||'constraint_error';
29 --
30 Begin
31 --
32 If (p_constraint_name = 'IRC_SEARCH_CRITERIA_FK1') Then
33 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
34 fnd_message.set_token('PROCEDURE', l_proc);
35 fnd_message.set_token('STEP','5');
36 fnd_message.raise_error;
37 ElsIf (p_constraint_name = 'IRC_SEARCH_CRITERIA_FK2') Then
38 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
39 fnd_message.set_token('PROCEDURE', l_proc);
40 fnd_message.set_token('STEP','10');
41 fnd_message.raise_error;
42 ElsIf (p_constraint_name = 'IRC_SEARCH_CRITERIA_PK') Then
43 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
44 fnd_message.set_token('PROCEDURE', l_proc);
45 fnd_message.set_token('STEP','15');
46 fnd_message.raise_error;
47 ElsIf (p_constraint_name = 'SEARCH_CRITERIA_ID_PK') Then
48 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
49 fnd_message.set_token('PROCEDURE', l_proc);
50 fnd_message.set_token('STEP','20');
51 fnd_message.raise_error;
52 Else
53 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
54 fnd_message.set_token('PROCEDURE', l_proc);
55 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
56 fnd_message.raise_error;
57 End If;
58 --
59 End constraint_error;
60 --
61 -- ----------------------------------------------------------------------------
62 -- |-----------------------------< api_updating >-----------------------------|
63 -- ----------------------------------------------------------------------------
64 Function api_updating
65 (p_search_criteria_id in number
66 ,p_object_version_number in number
67 )
68 Return Boolean Is
69 --
70 --
71 -- Cursor selects the 'current' row from the HR Schema
72 --
73 Cursor C_Sel1 is
74 select
75 search_criteria_id
76 ,object_id
77 ,object_type
78 ,search_name
79 ,search_type
80 ,location
81 ,distance_to_location
82 ,geocode_location
83 ,geocode_country
84 ,derived_location
85 ,location_id
86 ,isc.geometry.sdo_point.x
87 ,isc.geometry.sdo_point.y
88 ,employee
89 ,contractor
90 ,employment_category
91 ,keywords
92 ,travel_percentage
93 ,min_salary
94 ,max_salary
95 ,salary_currency
96 ,salary_period
97 ,match_competence
98 ,match_qualification
99 ,job_title
100 ,department
101 ,professional_area
102 ,work_at_home
103 ,min_qual_level
104 ,max_qual_level
105 ,use_for_matching
106 ,description
107 ,''
108 ,attribute_category
109 ,attribute1
110 ,attribute2
111 ,attribute3
112 ,attribute4
113 ,attribute5
114 ,attribute6
115 ,attribute7
116 ,attribute8
117 ,attribute9
118 ,attribute10
119 ,attribute11
120 ,attribute12
121 ,attribute13
122 ,attribute14
123 ,attribute15
124 ,attribute16
125 ,attribute17
126 ,attribute18
127 ,attribute19
128 ,attribute20
129 ,attribute21
130 ,attribute22
131 ,attribute23
132 ,attribute24
133 ,attribute25
134 ,attribute26
135 ,attribute27
136 ,attribute28
137 ,attribute29
138 ,attribute30
139 ,isc_information_category
140 ,isc_information1
141 ,isc_information2
142 ,isc_information3
143 ,isc_information4
144 ,isc_information5
145 ,isc_information6
146 ,isc_information7
147 ,isc_information8
148 ,isc_information9
149 ,isc_information10
150 ,isc_information11
151 ,isc_information12
152 ,isc_information13
153 ,isc_information14
154 ,isc_information15
155 ,isc_information16
156 ,isc_information17
157 ,isc_information18
158 ,isc_information19
159 ,isc_information20
160 ,isc_information21
161 ,isc_information22
162 ,isc_information23
163 ,isc_information24
164 ,isc_information25
165 ,isc_information26
166 ,isc_information27
167 ,isc_information28
168 ,isc_information29
169 ,isc_information30
170 ,object_version_number
171 ,date_posted
172 from irc_search_criteria isc
173 where search_criteria_id = p_search_criteria_id;
174 --
175 l_fct_ret boolean;
176 --
177 Begin
178 --
179 If (p_search_criteria_id is null and
180 p_object_version_number is null
181 ) Then
182 --
183 -- One of the primary key arguments is null therefore we must
184 -- set the returning function value to false
185 --
186 l_fct_ret := false;
187 Else
188 If (p_search_criteria_id
189 = irc_isc_shd.g_old_rec.search_criteria_id and
190 p_object_version_number
191 = irc_isc_shd.g_old_rec.object_version_number
192 ) Then
193 --
194 -- The g_old_rec is current therefore we must
195 -- set the returning function to true
196 --
197 l_fct_ret := true;
198 Else
199 --
200 -- Select the current row into g_old_rec
201 --
202 Open C_Sel1;
203 Fetch C_Sel1 Into irc_isc_shd.g_old_rec;
204 If C_Sel1%notfound Then
205 Close C_Sel1;
206 --
207 -- The primary key is invalid therefore we must error
208 --
209 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
210 fnd_message.raise_error;
211 End If;
212 Close C_Sel1;
213 -- Convert description from clob to varchar
214 irc_isc_shd.g_old_rec.description
215 := dbms_lob.substr(irc_isc_shd.g_old_rec.description_c);
216
217 If (p_object_version_number
218 <> irc_isc_shd.g_old_rec.object_version_number) Then
219 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
220 fnd_message.raise_error;
221 End If;
222 l_fct_ret := true;
223 End If;
224 End If;
225 Return (l_fct_ret);
226 --
227 End api_updating;
228 --
229 -- ----------------------------------------------------------------------------
230 -- |---------------------------------< lck >----------------------------------|
231 -- ----------------------------------------------------------------------------
232 Procedure lck
233 (p_search_criteria_id in number
234 ,p_object_version_number in number
235 ) is
236 --
237 -- Cursor selects the 'current' row from the HR Schema
238 --
239 Cursor C_Sel1 is
240 select
241 search_criteria_id
242 ,object_id
243 ,object_type
244 ,search_name
245 ,search_type
246 ,location
247 ,distance_to_location
248 ,geocode_location
249 ,geocode_country
250 ,derived_location
251 ,location_id
252 ,isc.geometry.sdo_point.x
253 ,isc.geometry.sdo_point.y
254 ,employee
255 ,contractor
256 ,employment_category
257 ,keywords
258 ,travel_percentage
259 ,min_salary
260 ,max_salary
261 ,salary_currency
262 ,salary_period
263 ,match_competence
264 ,match_qualification
265 ,job_title
266 ,department
267 ,professional_area
268 ,work_at_home
269 ,min_qual_level
270 ,max_qual_level
271 ,use_for_matching
272 ,description
273 ,''
274 ,attribute_category
275 ,attribute1
276 ,attribute2
277 ,attribute3
278 ,attribute4
279 ,attribute5
280 ,attribute6
281 ,attribute7
282 ,attribute8
283 ,attribute9
284 ,attribute10
285 ,attribute11
286 ,attribute12
287 ,attribute13
288 ,attribute14
289 ,attribute15
290 ,attribute16
291 ,attribute17
292 ,attribute18
293 ,attribute19
294 ,attribute20
295 ,attribute21
296 ,attribute22
297 ,attribute23
298 ,attribute24
299 ,attribute25
300 ,attribute26
301 ,attribute27
302 ,attribute28
303 ,attribute29
304 ,attribute30
305 ,isc_information_category
306 ,isc_information1
307 ,isc_information2
308 ,isc_information3
309 ,isc_information4
310 ,isc_information5
311 ,isc_information6
312 ,isc_information7
313 ,isc_information8
314 ,isc_information9
315 ,isc_information10
316 ,isc_information11
317 ,isc_information12
318 ,isc_information13
319 ,isc_information14
320 ,isc_information15
321 ,isc_information16
322 ,isc_information17
323 ,isc_information18
324 ,isc_information19
325 ,isc_information20
326 ,isc_information21
327 ,isc_information22
328 ,isc_information23
329 ,isc_information24
330 ,isc_information25
331 ,isc_information26
332 ,isc_information27
333 ,isc_information28
334 ,isc_information29
335 ,isc_information30
336 ,object_version_number
337 ,date_posted
338 from irc_search_criteria isc
339 where search_criteria_id = p_search_criteria_id
340 for update nowait;
341 --
342 l_proc varchar2(72) := g_package||'lck';
343 --
344 Begin
345 hr_utility.set_location('Entering:'||l_proc, 5);
346 --
347 hr_api.mandatory_arg_error
348 (p_api_name => l_proc
349 ,p_argument => 'SEARCH_CRITERIA_ID'
350 ,p_argument_value => p_search_criteria_id
351 );
352 hr_utility.set_location(l_proc,6);
353 hr_api.mandatory_arg_error
354 (p_api_name => l_proc
355 ,p_argument => 'OBJECT_VERSION_NUMBER'
356 ,p_argument_value => p_object_version_number
357 );
358 --
359 Open C_Sel1;
360 Fetch C_Sel1 Into irc_isc_shd.g_old_rec;
361 If C_Sel1%notfound then
362 Close C_Sel1;
363 --
364 -- The primary key is invalid therefore we must error
365 --
366 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
367 fnd_message.raise_error;
368 End If;
369 Close C_Sel1;
370 -- Convert description from clob to varchar
371 irc_isc_shd.g_old_rec.description
372 := dbms_lob.substr(irc_isc_shd.g_old_rec.description_c);
373
374 If (p_object_version_number
375 <> irc_isc_shd.g_old_rec.object_version_number) Then
376 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
377 fnd_message.raise_error;
378 End If;
379 --
380 hr_utility.set_location(' Leaving:'||l_proc, 10);
381 --
382 -- We need to trap the ORA LOCK exception
383 --
384 Exception
385 When HR_Api.Object_Locked then
386 --
387 -- The object is locked therefore we need to supply a meaningful
388 -- error message.
389 --
390 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
391 fnd_message.set_token('TABLE_NAME', 'irc_search_criteria');
392 fnd_message.raise_error;
393 End lck;
394 --
395 -- ----------------------------------------------------------------------------
396 -- |-----------------------------< convert_args >-----------------------------|
397 -- ----------------------------------------------------------------------------
398 Function convert_args
399 (p_search_criteria_id in number
400 ,p_object_id in number
401 ,p_object_type in varchar2
402 ,p_search_name in varchar2
403 ,p_search_type in varchar2
404 ,p_location in varchar2
405 ,p_distance_to_location in varchar2
406 ,p_geocode_location in varchar2
407 ,p_geocode_country in varchar2
408 ,p_derived_location in varchar2
409 ,p_location_id in number
410 ,p_longitude in number
411 ,p_latitude in number
412 ,p_employee in varchar2
413 ,p_contractor in varchar2
414 ,p_employment_category in varchar2
415 ,p_keywords in varchar2
416 ,p_travel_percentage in number
417 ,p_min_salary in number
418 ,p_max_salary in number
419 ,p_salary_currency in varchar2
420 ,p_salary_period in varchar2
421 ,p_match_competence in varchar2
422 ,p_match_qualification in varchar2
423 ,p_job_title in varchar2
424 ,p_department in varchar2
425 ,p_professional_area in varchar2
426 ,p_work_at_home in varchar2
427 ,p_min_qual_level in number
428 ,p_max_qual_level in number
429 ,p_use_for_matching in varchar2
430 ,p_description in varchar2
431 ,p_attribute_category in varchar2
432 ,p_attribute1 in varchar2
433 ,p_attribute2 in varchar2
434 ,p_attribute3 in varchar2
435 ,p_attribute4 in varchar2
436 ,p_attribute5 in varchar2
437 ,p_attribute6 in varchar2
438 ,p_attribute7 in varchar2
439 ,p_attribute8 in varchar2
440 ,p_attribute9 in varchar2
441 ,p_attribute10 in varchar2
442 ,p_attribute11 in varchar2
443 ,p_attribute12 in varchar2
444 ,p_attribute13 in varchar2
445 ,p_attribute14 in varchar2
446 ,p_attribute15 in varchar2
447 ,p_attribute16 in varchar2
448 ,p_attribute17 in varchar2
449 ,p_attribute18 in varchar2
450 ,p_attribute19 in varchar2
451 ,p_attribute20 in varchar2
452 ,p_attribute21 in varchar2
453 ,p_attribute22 in varchar2
454 ,p_attribute23 in varchar2
455 ,p_attribute24 in varchar2
456 ,p_attribute25 in varchar2
457 ,p_attribute26 in varchar2
458 ,p_attribute27 in varchar2
459 ,p_attribute28 in varchar2
460 ,p_attribute29 in varchar2
461 ,p_attribute30 in varchar2
462 ,p_isc_information_category in varchar2
463 ,p_isc_information1 in varchar2
464 ,p_isc_information2 in varchar2
465 ,p_isc_information3 in varchar2
466 ,p_isc_information4 in varchar2
467 ,p_isc_information5 in varchar2
468 ,p_isc_information6 in varchar2
469 ,p_isc_information7 in varchar2
470 ,p_isc_information8 in varchar2
471 ,p_isc_information9 in varchar2
472 ,p_isc_information10 in varchar2
473 ,p_isc_information11 in varchar2
474 ,p_isc_information12 in varchar2
475 ,p_isc_information13 in varchar2
476 ,p_isc_information14 in varchar2
477 ,p_isc_information15 in varchar2
478 ,p_isc_information16 in varchar2
479 ,p_isc_information17 in varchar2
480 ,p_isc_information18 in varchar2
481 ,p_isc_information19 in varchar2
482 ,p_isc_information20 in varchar2
483 ,p_isc_information21 in varchar2
484 ,p_isc_information22 in varchar2
485 ,p_isc_information23 in varchar2
486 ,p_isc_information24 in varchar2
487 ,p_isc_information25 in varchar2
488 ,p_isc_information26 in varchar2
489 ,p_isc_information27 in varchar2
490 ,p_isc_information28 in varchar2
491 ,p_isc_information29 in varchar2
492 ,p_isc_information30 in varchar2
493 ,p_object_version_number in number
494 ,p_date_posted in varchar2
495 )
496 Return g_rec_type is
497 --
498 l_rec g_rec_type;
499 --
500 Begin
501 --
502 -- Convert arguments into local l_rec structure.
503 --
504 l_rec.search_criteria_id := p_search_criteria_id;
505 l_rec.object_id := p_object_id;
506 l_rec.object_type := p_object_type;
507 l_rec.search_name := p_search_name;
508 l_rec.search_type := p_search_type;
509 l_rec.location := p_location;
510 l_rec.distance_to_location := p_distance_to_location;
511 l_rec.geocode_location := p_geocode_location;
512 l_rec.geocode_country := p_geocode_country;
513 l_rec.derived_location := p_derived_location;
514 l_rec.location_id := p_location_id;
515 l_rec.longitude := p_longitude;
516 l_rec.latitude := p_latitude;
517 l_rec.employee := p_employee;
518 l_rec.contractor := p_contractor;
519 l_rec.employment_category := p_employment_category;
520 l_rec.keywords := p_keywords;
521 l_rec.travel_percentage := p_travel_percentage;
522 l_rec.min_salary := p_min_salary;
523 l_rec.max_salary := p_max_salary;
524 l_rec.salary_currency := p_salary_currency;
525 l_rec.salary_period := p_salary_period;
526 l_rec.match_competence := p_match_competence;
527 l_rec.match_qualification := p_match_qualification;
528 l_rec.job_title := p_job_title;
529 l_rec.department := p_department;
530 l_rec.professional_area := p_professional_area;
531 l_rec.work_at_home := p_work_at_home;
532 l_rec.min_qual_level := p_min_qual_level;
533 l_rec.max_qual_level := p_max_qual_level;
534 l_rec.use_for_matching := p_use_for_matching;
535 l_rec.description := p_description;
536 l_rec.attribute_category := p_attribute_category;
537 l_rec.attribute1 := p_attribute1;
538 l_rec.attribute2 := p_attribute2;
539 l_rec.attribute3 := p_attribute3;
540 l_rec.attribute4 := p_attribute4;
541 l_rec.attribute5 := p_attribute5;
542 l_rec.attribute6 := p_attribute6;
543 l_rec.attribute7 := p_attribute7;
544 l_rec.attribute8 := p_attribute8;
545 l_rec.attribute9 := p_attribute9;
546 l_rec.attribute10 := p_attribute10;
547 l_rec.attribute11 := p_attribute11;
548 l_rec.attribute12 := p_attribute12;
549 l_rec.attribute13 := p_attribute13;
550 l_rec.attribute14 := p_attribute14;
551 l_rec.attribute15 := p_attribute15;
552 l_rec.attribute16 := p_attribute16;
553 l_rec.attribute17 := p_attribute17;
554 l_rec.attribute18 := p_attribute18;
555 l_rec.attribute19 := p_attribute19;
556 l_rec.attribute20 := p_attribute20;
557 l_rec.attribute21 := p_attribute21;
558 l_rec.attribute22 := p_attribute22;
559 l_rec.attribute23 := p_attribute23;
560 l_rec.attribute24 := p_attribute24;
561 l_rec.attribute25 := p_attribute25;
562 l_rec.attribute26 := p_attribute26;
563 l_rec.attribute27 := p_attribute27;
564 l_rec.attribute28 := p_attribute28;
565 l_rec.attribute29 := p_attribute29;
566 l_rec.attribute30 := p_attribute30;
567 l_rec.isc_information_category := p_isc_information_category;
568 l_rec.isc_information1 := p_isc_information1;
569 l_rec.isc_information2 := p_isc_information2;
570 l_rec.isc_information3 := p_isc_information3;
571 l_rec.isc_information4 := p_isc_information4;
572 l_rec.isc_information5 := p_isc_information5;
573 l_rec.isc_information6 := p_isc_information6;
574 l_rec.isc_information7 := p_isc_information7;
575 l_rec.isc_information8 := p_isc_information8;
576 l_rec.isc_information9 := p_isc_information9;
577 l_rec.isc_information10 := p_isc_information10;
578 l_rec.isc_information11 := p_isc_information11;
579 l_rec.isc_information12 := p_isc_information12;
580 l_rec.isc_information13 := p_isc_information13;
581 l_rec.isc_information14 := p_isc_information14;
582 l_rec.isc_information15 := p_isc_information15;
583 l_rec.isc_information16 := p_isc_information16;
584 l_rec.isc_information17 := p_isc_information17;
585 l_rec.isc_information18 := p_isc_information18;
586 l_rec.isc_information19 := p_isc_information19;
587 l_rec.isc_information20 := p_isc_information20;
588 l_rec.isc_information21 := p_isc_information21;
589 l_rec.isc_information22 := p_isc_information22;
590 l_rec.isc_information23 := p_isc_information23;
591 l_rec.isc_information24 := p_isc_information24;
592 l_rec.isc_information25 := p_isc_information25;
593 l_rec.isc_information26 := p_isc_information26;
594 l_rec.isc_information27 := p_isc_information27;
595 l_rec.isc_information28 := p_isc_information28;
596 l_rec.isc_information29 := p_isc_information29;
597 l_rec.isc_information30 := p_isc_information30;
598 l_rec.object_version_number := p_object_version_number;
599 l_rec.date_posted := p_date_posted;
600 --
601 -- Return the plsql record structure.
602 --
603 Return(l_rec);
604 --
605 End convert_args;
606 --
607 end irc_isc_shd;