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