DBA Data[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;