1 Package Body irc_saved_search_criteria_api as
2 /* $Header: irissapi.pkb 120.0.12000000.1 2007/03/23 11:17:59 vboggava noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'irc_saved_search_criteria_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |--------------------------< create_search_criteria >-------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_search_criteria
13 (p_validate in boolean default false
14 ,p_vacancy_id in number
15 ,p_saved_search_criteria_id out nocopy number
16 ,p_object_version_number out nocopy number
17 ) is
18 --
19 --
20 Cursor Chk_BG_Tracking_Enabled is
21 select NVL(bginfo.ORG_INFORMATION11,'N')
22 from HR_ORGANIZATION_INFORMATION bginfo ,per_all_vacancies vac
23 where bginfo.ORG_INFORMATION_CONTEXT = 'BG Recruitment'
24 and bginfo.organization_id = vac.business_group_id
25 and vac.vacancy_id = p_vacancy_id;
26 --
27 -- out variables
28 l_saved_search_criteria_id irc_saved_search_criteria.saved_search_criteria_id%TYPE;
29 l_object_version_number irc_saved_search_criteria.object_version_number%TYPE;
30 l_proc varchar2(72) := g_package||'create_search_criteria';
31 l_cursor_ret_val varchar2(10) := 'N';
32
33 begin
34 hr_utility.set_location('Entering:'|| l_proc, 10);
35
36
37 --
38 -- Issue a savepoint
39 --
40 savepoint create_search_criteria;
41 --
42 -- Remember IN OUT parameter IN values
43 --
44
45 --
46 -- Truncate the time portion from all IN date parameters
47 --
48
49 hr_utility.set_location(l_proc, 20);
50 --
51 -- Call Before Process User Hook
52 --
53 --Comment below To be removed
54
55 hr_utility.set_location(l_proc, 30);
56 --
57 -- Validation in addition to Row Handlers
58 --
59
60 --
61 -- Process Logic
62 -- Call insert search criteria only if vacancy id is not null and
63 -- the vacancy BG has got Applicant tracking enbabled
64 If p_vacancy_id Is Not Null Then
65 Open Chk_BG_Tracking_Enabled;
66 Fetch Chk_BG_Tracking_Enabled Into l_cursor_ret_val;
67 If Chk_BG_Tracking_Enabled%found Then
68 Close Chk_BG_Tracking_Enabled;
69 End If;
70 If l_cursor_ret_val='Y' Then
71 irc_iss_ins.ins
72 (p_vacancy_id => p_vacancy_id
73 ,p_saved_search_criteria_id => l_saved_search_criteria_id
74 ,p_object_version_number => l_object_version_number
75 );
76 End If;
77
78 End If;
79
80
81
82 hr_utility.set_location(' Leaving:'||l_proc, 50);
83 --
84 -- Call After Process User Hook
85 --
86 --Comment below To be removed
87
88 --
89 -- When in validation only mode raise the Validate_Enabled exception
90 --
91 if p_validate then
92 raise hr_api.validate_enabled;
93 end if;
94 --
95 -- Set all IN OUT and OUT parameters with out values
96 --
97 p_saved_search_criteria_id := l_saved_search_criteria_id;
98 p_object_version_number := l_object_version_number;
99 --
100 hr_utility.set_location(' Leaving:'||l_proc, 70);
101 exception
102 when hr_api.validate_enabled then
103 --
104 -- As the Validate_Enabled exception has been raised
105 -- we must rollback to the savepoint
106 --
107 rollback to create_search_criteria;
108 --
109 -- Reset IN OUT parameters and set OUT parameters
110 -- (Any key or derived arguments must be set to null
111 -- when validation only mode is being used.)
112 --
113 p_saved_search_criteria_id := null;
114 p_object_version_number := null;
115 --
116 hr_utility.set_location(' Leaving:'||l_proc, 80);
117 when others then
118 --
119 -- A validation or unexpected error has occured
120 --
121 rollback to create_search_criteria;
122 --
123 -- Reset IN OUT parameters and set all
124 -- OUT parameters, including warnings, to null
125 --
126 p_saved_search_criteria_id := null;
127 p_object_version_number := null;
128 --
129 hr_utility.set_location(' Leaving:'||l_proc, 90);
130 raise;
131 end create_search_criteria;
132 --
133 -- ----------------------------------------------------------------------------
134 -- |--------------------------< update_search_criteria >-------------------|
135 -- ----------------------------------------------------------------------------
136 --
137 procedure update_search_criteria
138 (p_validate in boolean default false
139 ,p_vacancy_id in number
140 ,p_saved_search_criteria_id in out nocopy number
141 ,p_object_version_number in out nocopy number
142 ) is
143 --
144 -- Declare cursors and local variables
145 --
146 -- out variables
147 l_saved_search_criteria_id irc_saved_search_criteria.saved_search_criteria_id%TYPE;
148 l_object_version_number irc_saved_search_criteria.object_version_number%TYPE;
149 l_proc varchar2(72) := g_package||'update_search_criteria';
150
151 begin
152 hr_utility.set_location('Entering:'|| l_proc, 10);
153 --
154 -- Issue a savepoint
155 --
156 savepoint update_search_criteria;
157 --
158 -- Remember IN OUT parameter IN values
159 --
160 l_saved_search_criteria_id := p_saved_search_criteria_id;
161 l_object_version_number := p_object_version_number;
162
163 --
164 -- Truncate the time portion from all IN date parameters
165 --
166
167 hr_utility.set_location(l_proc, 20);
168 --
169 -- Call Before Process User Hook
170 --
171 begin
172 irc_saved_search_criteria_bk2.update_search_criteria_b
173 (p_vacancy_id => p_vacancy_id
174 ,p_saved_search_criteria_id => l_saved_search_criteria_id
175 ,p_object_version_number => l_object_version_number
176 );
177 exception
178 when hr_api.cannot_find_prog_unit then
179 hr_api.cannot_find_prog_unit_error
180 (p_module_name => 'update_search_criteria'
181 ,p_hook_type => 'BP'
182 );
183 end;
184 hr_utility.set_location(l_proc, 30);
185 --
186 -- Validation in addition to Row Handlers
187 --
188
189 --
190 -- Process Logic
191 --
192 irc_iss_upd.upd
193 (p_vacancy_id => p_vacancy_id
194 ,p_saved_search_criteria_id => l_saved_search_criteria_id
195 ,p_object_version_number => l_object_version_number
196 );
197 hr_utility.set_location(' Leaving:'||l_proc, 50);
198 --
199 -- Call After Process User Hook
200 --
201 begin
202 irc_saved_search_criteria_bk2.update_search_criteria_a
203 (p_vacancy_id => p_vacancy_id
204 ,p_saved_search_criteria_id => l_saved_search_criteria_id
205 ,p_object_version_number => l_object_version_number
206 );
207 exception
208 when hr_api.cannot_find_prog_unit then
209 hr_api.cannot_find_prog_unit_error
210 (p_module_name => 'update_search_criteria'
211 ,p_hook_type => 'AP'
212 );
213 end;
214 --
215 -- When in validation only mode raise the Validate_Enabled exception
216 --
217 if p_validate then
218 raise hr_api.validate_enabled;
219 end if;
220 --
221 -- Set all IN OUT and OUT parameters with out values
222 --
223 p_saved_search_criteria_id := l_saved_search_criteria_id;
224 p_object_version_number := l_object_version_number;
225 --
226 hr_utility.set_location(' Leaving:'||l_proc, 70);
227 exception
228 when hr_api.validate_enabled then
229 --
230 -- As the Validate_Enabled exception has been raised
231 -- we must rollback to the savepoint
232 --
233 rollback to update_search_criteria;
234 --
235 -- Reset IN OUT parameters and set OUT parameters
236 -- (Any key or derived arguments must be set to null
237 -- when validation only mode is being used.)
238 --
239 p_saved_search_criteria_id := p_saved_search_criteria_id;
240 p_object_version_number := p_object_version_number;
241 --
242 hr_utility.set_location(' Leaving:'||l_proc, 80);
243 when others then
244 --
245 -- A validation or unexpected error has occured
246 --
247 rollback to update_search_criteria;
248 --
249 -- Reset IN OUT parameters and set all
250 -- OUT parameters, including warnings, to null
251 --
252 p_saved_search_criteria_id := p_saved_search_criteria_id;
253 p_object_version_number := p_object_version_number;
254 --
255 hr_utility.set_location(' Leaving:'||l_proc, 90);
256 raise;
257 end update_search_criteria;
258 --
259
260 -- ----------------------------------------------------------------------------
261 -- |---------------------------< <delete_search_criteria> >------------------------------|
262 -- ----------------------------------------------------------------------------
263 --
264 procedure delete_search_criteria
265 (p_validate in boolean default false
266 ,p_vacancy_id in number
267 ,p_saved_search_criteria_id in number
268 ,p_object_version_number in number
269 ) is
270 --
271 -- Declare cursors and local variables
272 --
273 l_proc varchar2(72) := g_package||'delete_search_criteria';
274 begin
275 hr_utility.set_location('Entering:'|| l_proc, 10);
276 --
277 -- Issue a savepoint
278 --
279 savepoint delete_search_criteria;
280 --
281 -- Truncate the time portion from all IN date parameters
282 --
283 -- Call Before Process User Hook
284 --
285 begin
286 irc_saved_search_criteria_bk3.delete_search_criteria_b
287 (p_vacancy_id => p_vacancy_id
288 ,p_saved_search_criteria_id => p_saved_search_criteria_id
289 ,p_object_version_number => p_object_version_number
290 );
291 exception
292 when hr_api.cannot_find_prog_unit then
293 hr_api.cannot_find_prog_unit_error
294 (p_module_name => 'delete_search_criteria'
295 ,p_hook_type => 'BP'
296 );
297 end;
298 --
299 -- Process Logic
300 --
301 irc_iss_del.del
302 ( p_saved_search_criteria_id => p_saved_search_criteria_id
303 ,p_object_version_number => p_object_version_number
304 );
305 --
306 -- Call After Process User Hook
307 --
308 begin
309 irc_saved_search_criteria_bk3.delete_search_criteria_a
310 (p_vacancy_id => p_vacancy_id
311 ,p_saved_search_criteria_id => p_saved_search_criteria_id
312 ,p_object_version_number => p_object_version_number
313 );
314 exception
315 when hr_api.cannot_find_prog_unit then
316 hr_api.cannot_find_prog_unit_error
317 (p_module_name => 'delete_search_criteria'
318 ,p_hook_type => 'AP'
319 );
320 end;
321 --
322 -- When in validation only mode raise the Validate_Enabled exception
323 --
324 if p_validate then
325 raise hr_api.validate_enabled;
326 end if;
327 --
328 hr_utility.set_location(' Leaving:'||l_proc, 70);
329 exception
330 when hr_api.validate_enabled then
331 --
332 -- As the Validate_Enabled exception has been raised
333 -- we must rollback to the savepoint
334 --
335 rollback to delete_search_criteria;
336 --
337 hr_utility.set_location(' Leaving:'||l_proc, 80);
338 when others then
339 --
340 -- A validation or unexpected error has occured
341 --
342 rollback to delete_search_criteria;
343 --
344 hr_utility.set_location(' Leaving:'||l_proc, 90);
345 raise;
346 end delete_search_criteria;
347 --
348 end irc_saved_search_criteria_api;