[Home] [Help]
PACKAGE BODY: APPS.HR_CAED_SS
Source
1 PACKAGE BODY HR_CAED_SS
2 /* $Header: hrcaedw.pkb 120.1 2005/09/21 16:47:12 svittal noship $*/
3 AS
4
5 g_package varchar2(30) := 'HR_CAED_SS';
6
7 PROCEDURE Create_transaction(
8 p_item_type IN WF_ITEMS.ITEM_TYPE%TYPE ,
9 p_item_key IN WF_ITEMS.ITEM_KEY%TYPE ,
10 p_act_id IN NUMBER ,
11 p_transaction_id IN OUT NOCOPY NUMBER ,
12 p_transaction_step_id IN OUT NOCOPY NUMBER,
13 p_login_person_id IN NUMBER ) IS
14
15
16 l_proc varchar2(200) := g_package || 'Create_transaction';
17 ln_transaction_id NUMBER ;
18 ln_transaction_step_id NUMBER ;
19 lv_result VARCHAR2(100) ;
20 ltt_trans_obj_vers_num hr_util_web.g_varchar2_tab_type;
21 lv_activity_name wf_item_activity_statuses_v.activity_name%TYPE;
22 lv_creator_person_id per_all_people_f.person_id%TYPE;
23 ln_ovn NUMBER ;
24
25 BEGIN
26
27 hr_utility.set_location(' Entering:' || l_proc,5);
28 hr_util_misc_web.validate_session(p_person_id => lv_creator_person_id);
29
30 ln_transaction_id := hr_transaction_ss.get_transaction_id
31 (p_Item_Type => p_item_type
32 ,p_Item_Key => p_item_key);
33
34
35 IF ln_transaction_id IS NULL
36 THEN
37
38 hr_utility.set_location(l_proc,10);
39 hr_transaction_ss.start_transaction
40 (itemtype => p_item_type
41 ,itemkey => p_item_key
42 ,actid => p_act_id
43 ,funmode => 'RUN'
44 ,p_login_person_id=>p_login_person_id
45 ,result => lv_result);
46
47 ln_transaction_id := hr_transaction_ss.get_transaction_id
48 (p_item_type => p_item_type
49 ,p_item_key => p_item_key);
50
51 END IF; -- now we have a valid txn id , let's find out txn steps
52
53
54 get_transaction_step(
55 p_transaction_id=>ln_transaction_id ,
56 p_Item_Type => p_item_type,
57 p_Item_Key => p_item_key,
58 p_transaction_step_id => ln_transaction_step_id ) ;
59
60
61 IF ln_transaction_step_id IS NULL THEN
62
63 hr_utility.set_location(l_proc,15);
64
65 --There is no transaction step for this transaction.
66 --Create a step within this new transaction
67
68 hr_transaction_api.create_transaction_step(
69 p_validate => false
70 ,p_creator_person_id => p_login_person_id
71 ,p_transaction_id => ln_transaction_id
72 ,p_api_name => 'HR_CAED_SS.PROCESS_API'
73 ,p_Item_Type => p_item_type
74 ,p_Item_Key => p_item_key
75 ,p_activity_id => p_act_id
76 ,p_transaction_step_id => ln_transaction_step_id
77 ,p_object_version_number =>ln_ovn ) ;
78
79 END IF;
80
81 -- write activity name to txn table
82 hr_transaction_api.set_varchar2_value (
83 p_transaction_step_id =>ln_transaction_step_id,
84 p_person_id => lv_creator_person_id ,
85 p_name => 'p_activity_name' ,
86 p_value =>'HR_CAED' ) ;
87
88 hr_transaction_api.set_varchar2_value (
89 p_transaction_step_id =>ln_transaction_step_id,
90 p_person_id => lv_creator_person_id ,
91 p_name => 'P_REVIEW_ACTID' ,
92 p_value =>p_act_id ) ;
93
94
95
96 p_transaction_id := ln_transaction_id ;
97 p_transaction_step_id := ln_transaction_step_id ;
98
99
100 hr_utility.set_location(' Leaving:' || l_proc,20);
101
102 EXCEPTION
103
104 WHEN OTHERS THEN
105 hr_utility.trace(' hr_caed.create_transaction: ' || SQLERRM);
106 hr_utility.set_location(' Leaving:' || l_proc,555);
107
108 raise ;
109 return ;
110 commit ;
111 END create_transaction ;
112
113 PROCEDURE write_transaction (
114 p_transaction_step_id NUMBER ,
115 p_login_person_id NUMBER ,
116 p_emp_person_id NUMBER ,
117 p_action VARCHAR2,
118 p_granted_emp_name VARCHAR2 DEFAULT NULL ,
119 p_granted_emp_id NUMBER DEFAULT NULL,
120 p_granted_user_id NUMBER DEFAULT NULL,
121 p_deleted_emp_name VARCHAR2 DEFAULT NULL ,
122 p_deleted_emp_id NUMBER DEFAULT NULL ,
123 p_deleted_emp_user_id NUMBER DEFAULT NULL ,
124 p_review_proc_call VARCHAR2 DEFAULT NULL ) IS
125
126 l_proc varchar2(200) := g_package || 'write_transaction';
127 lv_creator_person_id per_all_people_f.person_id%TYPE;
128
129 BEGIN
130
131
132 hr_utility.set_location(' Entering:' || l_proc,5);
133 hr_util_misc_web.validate_session(p_person_id => lv_creator_person_id);
134
135
136
137 hr_transaction_api.set_varchar2_value (
138 p_transaction_step_id =>p_transaction_step_id,
139 p_person_id => lv_creator_person_id ,
140 p_name => 'P_REVIEW_PROC_CALL' ,
141 p_value =>p_review_proc_call ) ;
142
143
144 hr_transaction_api.set_varchar2_value (
145 p_transaction_step_id =>p_transaction_step_id,
146 p_person_id => lv_creator_person_id ,
147 p_name => 'p_action_type' ,
148 p_value =>p_action ) ;
149
150
151 hr_transaction_api.set_varchar2_value (
152 p_transaction_step_id =>p_transaction_step_id,
153 p_person_id => lv_creator_person_id ,
154 p_name => 'p_granted_emp_name' ,
155 p_value =>p_granted_emp_name ) ;
156
157 hr_transaction_api.set_varchar2_value (
158 p_transaction_step_id =>p_transaction_step_id,
159 p_person_id => lv_creator_person_id ,
160 p_name => 'p_deleted_emp_name' ,
161 p_value =>p_deleted_emp_name ) ;
162
163 hr_transaction_api.set_number_value (
164 p_transaction_step_id =>p_transaction_step_id,
165 p_person_id => lv_creator_person_id ,
166 p_name => 'p_granted_emp_id' ,
167 p_value =>p_granted_emp_id ) ;
168
169 hr_transaction_api.set_number_value (
170 p_transaction_step_id =>p_transaction_step_id,
171 p_person_id => lv_creator_person_id ,
172 p_name => 'p_login_person_id' ,
173 p_value =>p_login_person_id ) ;
174
175
176 hr_transaction_api.set_number_value (
177 p_transaction_step_id =>p_transaction_step_id,
178 p_person_id => lv_creator_person_id ,
179 p_name => 'p_emp_person_id' ,
180 p_value =>p_emp_person_id ) ;
181
182
183 hr_transaction_api.set_number_value (
184 p_transaction_step_id =>p_transaction_step_id,
185 p_person_id => lv_creator_person_id ,
186 p_name => 'p_granted_user_id' ,
187 p_value =>p_granted_user_id ) ;
188
189 hr_transaction_api.set_number_value (
190 p_transaction_step_id =>p_transaction_step_id,
191 p_person_id => lv_creator_person_id ,
192 p_name => 'p_deleted_emp_id' ,
193 p_value =>p_deleted_emp_id ) ;
194
195 hr_transaction_api.set_number_value (
196 p_transaction_step_id =>p_transaction_step_id,
197 p_person_id => lv_creator_person_id ,
198 p_name => 'p_deleted_emp_user_id' ,
199 p_value =>p_deleted_emp_user_id ) ;
200
201 hr_transaction_api.set_varchar2_value (
202 p_transaction_step_id =>p_transaction_step_id,
203 p_person_id => lv_creator_person_id ,
204 p_name => 'P_REVIEW_PROC_CALL' ,
205 p_value =>p_review_proc_call ) ;
206
207
208 hr_utility.set_location(' Leaving:' || l_proc,10);
209
210 EXCEPTION
211 WHEN OTHERS THEN
212 hr_utility.trace(' HR_CAED_SS.write_transaction ' || SQLERRM );
213 hr_utility.set_location(' Leaving:' || l_proc,555);
214 raise ;
215
216 END WRITE_TRANSACTION ;
217
218 PROCEDURE get_transaction_step(
219 p_transaction_id in NUMBER ,
220 p_item_type in varchar2,
221 p_item_key in varchar2,
222 p_transaction_step_id out nocopy NUMBER ) IS
223
224 l_proc varchar2(200) := g_package || 'get_transaction_step';
225
226
227 cursor c_txn_steps is
228 select hats.transaction_step_id
229 from hr_api_transaction_steps hats
230 where hats.item_type = p_item_type
231 and hats.transaction_id = p_transaction_id
232 and hats.item_key = p_item_key ;
233
234 ln_transaction_step_id NUMBER := NULL ;
235
236 BEGIN
237
238 hr_utility.set_location(' Entering:' || l_proc,5);
239 open c_txn_steps ;
240 fetch c_txn_steps into ln_transaction_step_id ;
241 close c_txn_steps ;
242 p_transaction_step_id := ln_transaction_step_id ;
243
244 -- Reset OUT parameters for nocopy.
245
246 hr_utility.set_location(' Leaving:' || l_proc,10);
247 EXCEPTION
248 WHEN OTHERS THEN
249 hr_utility.set_location(' Leaving:' || l_proc,555);
250 p_transaction_step_id := null;
251 RAISE;
252
253 END get_transaction_step ;
254
255 --***************************************************************************
256 -- procedure wrapper to call grant access procedure
257 --***************************************************************************
258 PROCEDURE grant_access (
259 p_validate in number default 1
260 ,p_item_type in varchar2
261 ,p_item_key in varchar2
262 ,p_actid in number
263 ,p_emp_person_id in number
264 ,p_login_person_id in number
265 ,p_action_type in varchar2
266 ,p_granted_emp_name in varchar2 default null
267 ,p_granted_emp_id in number default null
268 ,p_granted_user_id in number default null
269 ,p_review_proc_call in varchar2 default null
270 ,p_transaction_id out nocopy number
271 ,p_transaction_step_id out nocopy number)
272 IS
273
274 l_proc varchar2(200) := g_package || 'grant_access';
275 ln_transaction_id number default null;
276 ln_transaction_step_id number default null;
277
278
279 BEGIN
280
281 hr_utility.set_location(' Entering:' || l_proc,5);
282 ---------------------------------------------------------------------------
283 -- p_validate = 1 means validate mode, do not update to the database.
284 -- p_validate = 0 means non-validate mode, update to the database.
285 ---------------------------------------------------------------------------
286 IF p_validate = 1 THEN
287 hr_utility.set_location(l_proc,10);
288 savepoint grant_access ;
289 END IF ;
290
291 hr_security_internal.grant_access_to_person (
292 p_person_id=> p_emp_person_id,
293 p_granted_user_id => p_granted_user_id ) ;
294
295 IF p_validate = 1 THEN
296 hr_utility.set_location(l_proc,15);
297 ROLLBACK to grant_access ;
298 END IF ;
299
300 -- Successfully calling api in validate mode, now call create_transaction
301 -- before saving data to transaction table.
302 create_transaction (
303 p_item_type => p_item_type
304 ,p_item_key => p_item_key
305 ,p_act_id => p_actid
306 ,p_login_person_id => p_login_person_id
307 ,p_transaction_id => ln_transaction_id
308 ,p_transaction_step_id => ln_transaction_step_id
309 );
310
311 -- Now call write_transaction to save to the transaction table.
312 write_transaction (
313 p_transaction_step_id => ln_transaction_step_id
314 ,p_login_person_id => p_login_person_id
315 ,p_emp_person_id => p_emp_person_id
316 ,p_action => p_action_type
317 ,p_granted_emp_name => p_granted_emp_name
318 ,p_granted_emp_id => p_granted_emp_id
319 ,p_granted_user_id => p_granted_user_id
320 ,p_review_proc_call => p_review_proc_call
321 );
322
323 p_transaction_id := ln_transaction_id;
324 p_transaction_step_id := ln_transaction_step_id;
325
326 hr_utility.set_location(' Leaving:' || l_proc,20);
327
328
329 EXCEPTION
330 WHEN OTHERS THEN
331 hr_utility.set_location(' Leaving:' || l_proc,555);
332 RAISE ;
333 END grant_access ;
334
335
336 --*****************************************************************************
337 -- Revoke Access
338 --*****************************************************************************
339 PROCEDURE revoke_access (
340 p_validate in number default 1
341 ,p_item_type in varchar2
342 ,p_item_key in varchar2
343 ,p_actid in number
344 ,p_emp_person_id in number
345 ,p_login_person_id in number
346 ,p_action_type in varchar2
347 ,p_granted_user_id in number
348 ,p_deleted_emp_name in varchar2 default null
349 ,p_deleted_emp_id in number default null
350 ,p_deleted_emp_user_id in number default null
351 ,p_review_proc_call in varchar2 default null
352 ,p_transaction_id out nocopy number
353 ,p_transaction_step_id out nocopy number)
354 IS
355
356 l_proc varchar2(200) := g_package || 'revoke_access';
357 ln_transaction_id number default null;
358 ln_transaction_step_id number default null;
359
360 BEGIN
361
362 hr_utility.set_location(' Entering:' || l_proc,5);
363 ---------------------------------------------------------------------------
364 -- p_validate = 1 means validate mode, do not update to the database.
365 -- p_validate = 0 means non-validate mode, update to the database.
366 ---------------------------------------------------------------------------
367 IF p_validate = 1 THEN
368 hr_utility.set_location(l_proc,10);
369 savepoint revoke_access ;
370 END IF ;
371
372 hr_security_internal.revoke_access_from_person (
373 p_person_id => p_emp_person_id ,
374 p_granted_user_id => p_granted_user_id ) ;
375
376 IF p_validate = 1 THEN
377 hr_utility.set_location(l_proc,15);
378 ROLLBACK to revoke_access ;
379 END IF ;
380
381 -- Successfully calling api in validate mode, now call create_transaction
382 -- before saving data to transaction table.
383 create_transaction (
384 p_item_type => p_item_type
385 ,p_item_key => p_item_key
386 ,p_act_id => p_actid
387 ,p_login_person_id => p_login_person_id
388 ,p_transaction_id => ln_transaction_id
389 ,p_transaction_step_id => ln_transaction_step_id
390 );
391
392 -- Now call write_transaction to save to the transaction table.
393 write_transaction (
394 p_transaction_step_id => ln_transaction_step_id
395 ,p_login_person_id => p_login_person_id
396 ,p_emp_person_id => p_emp_person_id
397 ,p_action => p_action_type
398 ,p_deleted_emp_name => p_deleted_emp_name
399 ,p_deleted_emp_id => p_deleted_emp_id
400 ,p_deleted_emp_user_id => p_deleted_emp_user_id
401 ,p_review_proc_call => p_review_proc_call
402 );
403
404 p_transaction_id := ln_transaction_id;
405 p_transaction_step_id := ln_transaction_step_id;
406
407 hr_utility.set_location(' Leaving:' || l_proc,20);
408
409
410 EXCEPTION
411 WHEN OTHERS THEN
412 hr_utility.set_location(' Leaving:' || l_proc,555);
413 RAISE ;
414 END revoke_access ;
415
416
417 --****************************************************************************
418 -- Process_api is invoked after final approval in Workflow.
419 --****************************************************************************
420 Procedure process_api (
421 p_transaction_step_id IN
422 hr_api_transaction_steps.transaction_step_id%type,
423 p_validate BOOLEAN default FALSE ) IS
424
425 l_proc varchar2(200) := g_package || 'process_api';
426 lv_action_type VARCHAR2(30) ;
427 ln_granted_emp_id NUMBER ;
428 ln_emp_person_id NUMBER ;
429 ln_granted_user_id NUMBER ;
430 ln_deleted_emp_id NUMBER ;
431 ln_deleted_emp_user_id NUMBER ;
432
433 BEGIN
434
435 hr_utility.set_location(' Entering:' || l_proc,5);
436 lv_action_type := hr_transaction_api.get_varchar2_value(
437 p_transaction_step_id => p_transaction_step_id,
438 p_name =>'p_action_type');
439
440 ln_granted_emp_id := hr_transaction_api.get_number_value(
441 p_transaction_step_id => p_transaction_step_id,
442 p_name =>'p_granted_emp_id');
443
444 ln_emp_person_id := hr_transaction_api.get_number_value(
445 p_transaction_step_id => p_transaction_step_id,
446 p_name =>'p_emp_person_id');
447
448 ln_granted_user_id := hr_transaction_api.get_number_value(
449 p_transaction_step_id => p_transaction_step_id,
450 p_name =>'p_granted_user_id');
451
452 ln_deleted_emp_id := hr_transaction_api.get_number_value(
453 p_transaction_step_id => p_transaction_step_id,
454 p_name =>'p_deleted_emp_id');
455
456 ln_deleted_emp_user_id := hr_transaction_api.get_number_value(
457 p_transaction_step_id =>
458 p_transaction_step_id,
462 -- now we have the values from transaction table , we need to call api
459 p_name =>'p_deleted_emp_user_id');
460
461
463
464 IF lv_action_type = 'Insert'
465 THEN
466 IF p_validate
467 THEN
468 hr_utility.set_location(l_proc,10);
469 savepoint grant_access ;
470 END IF ;
471 hr_utility.set_location(l_proc,15);
472 hr_security_internal.grant_access_to_person (
473 p_person_id => ln_emp_person_id,
474 p_granted_user_id => ln_granted_user_id ) ;
475
476 IF p_validate
477 THEN
478 hr_utility.set_location(l_proc,20);
479 ROLLBACK to grant_access ;
480 END IF ;
481 END IF;
482
483
484 IF lv_action_type = 'Delete'
485 THEN
486 IF p_validate
487 THEN
488 hr_utility.set_location(l_proc,25);
489 savepoint revoke_access ;
490 END IF ;
491
492 hr_utility.set_location(l_proc,30);
493 hr_security_internal.revoke_access_from_person (
494 p_person_id => ln_emp_person_id,
495 p_granted_user_id => ln_deleted_emp_user_id) ;
496
497 IF p_validate
498 THEN
499 hr_utility.set_location(l_proc,35);
500 ROLLBACK to revoke_access ;
501 END IF ;
502 END IF;
503
504
505 hr_utility.set_location(' Leaving:' || l_proc,40);
506
507 EXCEPTION
508 WHEN OTHERS THEN
509 hr_utility.set_location(' Leaving:' || l_proc,555);
510 RAISE ;
511
512 END PROCESS_API;
513
514 END ;
515