1 PACKAGE BODY hr_new_user_reg_ss AS
2 /* $Header: hrregwrs.pkb 120.8.12020000.6 2012/12/14 05:37:19 sudedas ship $*/
3
4 -- This package is for the new user registration process
5
6 -- Package Variables
7 --
8 g_package varchar2(33) := 'HR_NEW_USER_REG_SS.';
9 g_update_object_version varchar2(30) := 'update_object_version';
10
11 FUNCTION is_Assignment_SFL(
12 p_transaction_step_id in number)
13 RETURN boolean is
14
15 l_review_proc_call varchar2(4000);
16 l_save_mode varchar2(2000);
17
18 BEGIN
19
20 l_save_mode := hr_transaction_api.get_varchar2_value
21 (p_transaction_step_id => p_transaction_step_id
22 ,p_name => 'P_SAVE_MODE');
23
24 l_review_proc_call:= hr_transaction_api.get_varchar2_value
25 (p_transaction_step_id => p_transaction_step_id
26 ,p_name => 'P_REVIEW_PROC_CALL');
27
28 RETURN (l_save_mode = 'SAVE_FOR_LATER') and (l_review_proc_call = 'HrAssignment');
29
30 END is_Assignment_SFL;
31
32 -- ----------------------------------------------------------------------------
33 -- |---------------------------< processNewUserTransaction >-------------------------|
34 -- ----------------------------------------------------------------------------
35 --
36 -- This procedure creates a person using the data in the
37 -- transaction tables if it doesn't find one.
38
39 procedure processNewUserTransaction
40 (WfItemType in varchar2,
41 WfItemKey in varchar2,
42 PersonId in out nocopy varchar2,
43 AssignmentId in out nocopy varchar2) is
44
45 cursor csr_person is select person_id from per_all_people_f where person_id = PersonId;
46
47 cursor csr_txn_step_id is
48 select trs.transaction_step_id
49 from hr_api_transaction_steps trs
50 where trs.transaction_id = hr_transaction_ss.get_transaction_id
51 (WfItemType ,WfItemKey )
52 and trs.api_name ='HR_PROCESS_PERSON_SS.PROCESS_API';
53
54 --Added for the Bug 14158934
55 cursor csr_txn_status is
56 select status from hr_api_transactions hrt
57 where hrt.transaction_id = hr_transaction_ss.get_transaction_id(WfItemType ,WfItemKey);
58
59 l_effective_date date;
60 l_session_c number;
61 l_transaction_step_id number;
62 l_business_grp_Id number;
63
64 l_newUserPersonId per_all_people_f.person_id%type;
65 l_result varchar2(100);
66 l_proc varchar2(72) := g_package||'processNewUserTransaction';
67 --Added for the Bug 14158934
68 l_prof varchar2(10);
69 l_status varchar2(10);
70 begin
71
72 hr_utility.set_location('Entering:'|| l_proc, 5);
73 --Fix for the Bug 14158934 Starts
74 open csr_txn_status;
75 fetch csr_txn_status into l_status;
76 close csr_txn_status;
77
78 if upper(l_status ) = 'AC' then
79 l_prof := fnd_profile.value ('PER_NI_UNIQUE_ERROR_WARNING');
80 fnd_profile.put('PER_NI_UNIQUE_ERROR_WARNING',null);
81 end if;
82 --Fix for the Bug 14158934 ends
83
84
85 open csr_person;
86 fetch csr_person into l_newUserPersonId;
87
88 -- if the person Id is not in the database then a dummy person was created and rolled back in the database.
89 -- need to recreat the person again.
90
91 if csr_person%notfound then
92 hr_utility.set_location('in side if csr_person%notfound :'|| l_proc, 5);
93 /*
94 hr_transaction_web.commit_transaction
95 (itemtype=>WfItemType,
96 itemkey=>WfItemKey,
97 actid=>'12345', -- can hard code this to anything this is not being used inside the call
98 funmode=>'RUN', -- the necessary mode to run the api in commit mode
99 result=>l_result); -- though we are getting the error results back we are not using them.
100 -- the commit process will call Process_api of hr_process_person_ss which
101 */
102 /* Replaced commit_transaction with process_selected_transaction, commit transaction will
103 call all the steps for a given item key. However we need only person and address apis
104 are
105 */
106
107 --ignore the emp number generation for newhire flow
108 hr_new_user_reg_ss.g_ignore_emp_generation := 'YES';
109
110 --bug 5665820
111 if(hr_general.get_business_group_id is null) then
112
113 open csr_txn_step_id;
114 fetch csr_txn_step_id into l_transaction_step_id;
115 close csr_txn_step_id;
116
117 l_business_grp_Id :=hr_transaction_api.get_number_value
118 (p_transaction_step_id => l_transaction_step_id
119 ,p_name => 'P_BUSINESS_GROUP_ID');
120
121 fnd_profile.put('PER_BUSINESS_GROUP_ID',l_business_grp_Id);
122
123 end if;
124 --bug 5665820
125
126 --bug 7459817
127 l_effective_date := to_date(wf_engine.getitemattrtext
128 (itemtype => WfItemType
129 ,itemkey => WfItemKey
130 ,aname => 'P_EFFECTIVE_DATE'),'RRRR-MM-DD');
131 begin
132 select 1 into l_session_c from fnd_sessions where session_id=userenv('sessionid');
133 exception
134 when no_data_found then
135 insert into fnd_sessions(session_id,effective_date) values(userenv('sessionid'),l_effective_date);
136 end;
137
138 process_selected_transaction
139 (p_item_type => WfItemType,
140 p_item_key => WfItemKey);
141
142 -- the commit process will call Process_api of hr_process_person_ss which
143 -- creates a person and returns us the person id and assignment_id.
144
145 PersonId := to_char(hr_process_person_ss.g_person_id);
146 AssignmentId := to_char(hr_process_person_ss.g_assignment_id);
147 end if;
148 -- close the cursor
149 close csr_person;
150
151 --Added for the Bug 14158934
152 if upper(l_status ) = 'AC' then
153 fnd_profile.put('PER_NI_UNIQUE_ERROR_WARNING',l_prof);
154 end if;
155
156 hr_utility.set_location('Leaving:'|| l_proc, 10);
157
158 EXCEPTION
159 WHEN OTHERS THEN
160 PersonId := null;
161 AssignmentId := null;
162 raise;
163
164 end processNewUserTransaction;
165
166 procedure process_selected_transaction
167 (p_item_type in varchar2
168 ,p_item_key in varchar2
169 ,p_ignore_warnings in varchar2 default 'Y'
170 ,p_validate in boolean default false
171 ,p_update_object_version in varchar2 default 'N'
172 ,p_effective_date in varchar2 default null
173 ,p_api_name in varchar2 default null
174 ,p_source in varchar2 default null) is
175 -- --------------------------------------------------------------------------
176 -- declare local variables
177 -- --------------------------------------------------------------------------
178 l_transaction_id hr_api_transactions.transaction_id%type;
179 l_application_error boolean := false;
180 l_object_version_error boolean := false;
181 l_obj_fatal_error boolean := false;
182 l_warning_error boolean := false;
183 l_ignore_warnings boolean;
184 l_obj_api_name varchar2(200);
185 l_api_error_name varchar2(200);
186
187 -- Generic Cursor
188 cursor csr_trs is
189 select trs.transaction_step_id
190 ,trs.api_name
191 ,trs.item_type
192 ,trs.item_key
193 ,trs.activity_id
194 ,trs.creator_person_id
195 from hr_api_transaction_steps trs
196 where trs.transaction_id = l_transaction_id
197 and trs.api_name in ('HR_PROCESS_PERSON_SS.PROCESS_API',
198 'HR_PROCESS_ADDRESS_SS.PROCESS_API',
199 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API',
200 'HR_PROCESS_CONTACT_SS.PROCESS_CREATE_CONTACT_API')
201 order by trs.processing_order,trs.transaction_step_id;
202
203 --Selective cursor
204 cursor csr_sel_trs is
205 select trs.transaction_step_id
206 ,trs.api_name
207 ,trs.item_type
208 ,trs.item_key
209 ,trs.activity_id
210 ,trs.creator_person_id
211 from hr_api_transaction_steps trs
212 where trs.transaction_id = l_transaction_id
213 and trs.api_name = p_api_name
214 order by trs.processing_order;
215
216 begin
217 -- set the ignore warnings flag
218 if upper(p_ignore_warnings) = 'Y' then
219 l_ignore_warnings := true;
220 else
221 l_ignore_warnings := false;
222 end if;
223 -- get the transaction id
224 l_transaction_id := hr_transaction_ss.get_transaction_id
225 (p_item_type => p_item_type
226 ,p_item_key => p_item_key);
227 if l_transaction_id is null then
228 return;
229 end if;
230
231
232 hr_process_person_ss.g_person_id := null;
233 hr_process_person_ss.g_assignment_id := null;
234
235 if(p_api_name is not null) then
236 -- process selected api step
237 for csr_sel in csr_sel_trs loop
238 begin
239 hr_transaction_ss.process_web_api_call
240 (p_transaction_step_id => csr_sel.transaction_step_id
241 ,p_api_name => csr_sel.api_name
242 ,p_validate => p_validate);
243 exception
244 when hr_utility.hr_error then
245 -- an application error has been raised. set the error flag
246 -- to indicate an application error
247 -- the error message should of been set already
248 hr_message.provide_error;
249 l_api_error_name := hr_message.last_message_name;
250 if l_api_error_name = 'HR_7155_OBJECT_INVALID' then
251 l_obj_fatal_error := true;
252 exit;
253 else
254 l_application_error := true;
255
256 -------------------------------------------------------------------
257 -- 05/09/2002 Bug 2356339 Fix Begins
258 -- We need to exit the loop here when there is an application error.
259 -- This will happen when apporval is required and the final approver
260 -- approved the change. When the Workflow responsibility to approve
261 -- the transaction has no Security Profile attached, and the
262 -- Business Group profile option is null and Cross Business Group
263 -- equals to 'N', you will get an Application Error after the final
264 -- approver approves the transaction. This problem usually happens
265 -- in New Hire or Applicant Hire whereby the new employee created
266 -- is not in the per_person_list table. In hr_process_person_ss.
267 -- process_api, it call hr_employee_api.create_employee which
268 -- eventually will call dt_api.return_min_start_date. This
269 -- dt_api.return_min_start_date accesses the per_people_f secured
270 -- view, you will get HR_7182_DT_NO_MIN_MAX_ROWS error with the
271 -- following error text:
272 -- No DateTrack row found in table per_people_f.
273 -- When that happens, the l_application_error is set to true. However,
274 -- if there is no Exit statement, this code will continue to call
275 -- the next transaction step. Each of the subsequent step will fail
276 -- with an error until the last step is called and the error from
277 -- the last step will overwrite the initial real error message.
278 -- Without the exit statement, it will be very difficult to pinpoint
279 -- the location where the real problem occurred.
280 ---------------------------------------------------------------------
281
282 EXIT; -- Bug 2356339 Fix
283
284 -- 05/09/2002 Bug 2356339 Fix Ends
285
286 end if;
287 raise;
288 when others then
289 -- a system error has occurred so raise it to stop
290 -- processing of the transaction steps
291 raise;
292 end;
293 end loop;
294 else
295 -- select each transaction steps to process
296 for i in csr_trs loop
297 begin
298 -- call the API for the transaction step
299 -- don't call the assignment step, if coming back from
300 -- SFL and SFL is done on Assignment page
301
302 --ignore employee number generation for newhire SS flow
303 hr_new_user_reg_ss.g_ignore_emp_generation := 'YES';
304
305 if (i.api_name <> 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API') OR
306 ((i.api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API') and
307 (NOT(p_source is not null and p_source = 'Assignment')) and
308 (NOT is_Assignment_SFL(i.transaction_step_id))) then
309 if p_update_object_version = 'Y' then
310 -- update object version for each step
311 l_obj_api_name := substr(i.api_name,1, instr(i.api_name,'.'));
312 l_obj_api_name := l_obj_api_name || g_update_object_version;
313 hr_transaction_ss.process_web_api_call
314 (p_transaction_step_id => i.transaction_step_id
315 ,p_api_name => l_obj_api_name
316 ,p_extra_parameter_name => 'p_login_person_id'
317 ,p_extra_parameter_value => i.creator_person_id
318 ,p_validate => false);
319 elsif p_effective_date is not null then
320 --validate api with the new p_effective_date
321 hr_transaction_ss.process_web_api_call
322 (p_transaction_step_id => i.transaction_step_id
323 ,p_api_name => i.api_name
324 ,p_extra_parameter_name => 'p_effective_date'
325 ,p_extra_parameter_value => p_effective_date
326 ,p_validate => p_validate);
327 else
328 --validate api
329 hr_transaction_ss.process_web_api_call
330 (p_transaction_step_id => i.transaction_step_id
331 ,p_api_name => i.api_name
332 ,p_validate => p_validate);
333 end if;
334 end if;
335 -- do we ignore any warnings which may have been set?
336 if not l_ignore_warnings then
337 -- check to see if any warnings have been set
338 if (not l_warning_error) and
339 hr_emp_error_utility.exists_warning_text
340 (p_item_type => i.item_type
341 ,p_item_key => i.item_key
342 ,p_actid => i.activity_id) then
343 -- set the warning flag to true
344 l_warning_error := true;
345 end if;
346 end if;
347 exception
348 when hr_utility.hr_error then
349 -- an application error has been raised. set the error flag
350 -- to indicate an application error
351 -- the error message should of been set already
352 hr_message.provide_error;
353 l_api_error_name := hr_message.last_message_name;
354 if l_api_error_name = 'HR_7155_OBJECT_INVALID' then
355 l_obj_fatal_error := true;
356 exit;
357 --if i.api_name = 'BEN_PROCESS_COMPENSATION_W.PROCESS_API' then
358 -- fnd_message.set_name('PER','HR_FATAL_OBJECT_ERROR');
359 -- l_obj_fatal_error := true;
360 -- exit;
361 --end if;
362 else
363 l_application_error := true;
364
365 -------------------------------------------------------------------
366 -- 05/09/2002 Bug 2356339 Fix Begins
367 -- We need to exit the loop here when there is an application error.
368 -- This will happen when apporval is required and the final approver
369 -- approved the change. When the Workflow responsibility to approve
370 -- the transaction has no Security Profile attached, and the
371 -- Business Group profile option is null and Cross Business Group
372 -- equals to 'N', you will get an Application Error after the final
373 -- approver approves the transaction. This problem usually happens
374 -- in New Hire or Applicant Hire whereby the new employee created
375 -- is not in the per_person_list table. In hr_process_person_ss.
376 -- process_api, it call hr_employee_api.create_employee which
377 -- eventually will call dt_api.return_min_start_date. This
378 -- dt_api.return_min_start_date accesses the per_people_f secured
379 -- view, you will get HR_7182_DT_NO_MIN_MAX_ROWS error with the
380 -- following error text:
381 -- No DateTrack row found in table per_people_f.
382 -- When that happens, the l_application_error is set to true. However,
383 -- if there is no Exit statement, this code will continue to call
384 -- the next transaction step. Each of the subsequent step will fail
385 -- with an error until the last step is called and the error from
386 -- the last step will overwrite the initial real error message.
387 -- Without the exit statement, it will be very difficult to debug the
388 -- location when the real problem occurs.
389 ---------------------------------------------------------------------
390
391 EXIT; -- Bug 2356339 Fix
392
393 -- 05/09/2002 Bug 2356339 Fix Ends
394
395 end if;
396 raise;
397 when others then
398 -- a system error has occurred so raise it to stop
399 -- processing of the transaction steps
400 raise;
401 end;
402 end loop;
403 end if;
404
405 -- check to see if any application errors where raised
406 if l_obj_fatal_error then
407 fnd_message.set_name('PER','HR_FATAL_OBJECT_ERROR');
408 raise hr_utility.hr_error;
409 elsif l_object_version_error then
410 fnd_message.set_name('PER','HR_7155_OBJECT_INVALID');
411 raise hr_utility.hr_error;
412 elsif l_application_error or l_warning_error then
413 raise hr_utility.hr_error;
414 end if;
415
416 exception
417 when others then
418 -- an application error, warning or system error was raised so
419 -- keep raising it so the calling process must handle it
420 raise;
421 end process_selected_transaction;
422
423 procedure processExEmpTransaction
424 (WfItemType in varchar2,
425 WfItemKey in varchar2,
426 PersonId in out nocopy varchar2,
427 AssignmentId in out nocopy varchar2,
428 p_error_message out nocopy long,
429 p_source in varchar2 default null) is
430
431 l_person_id number;
432
433 cursor csr_person is select person_id,person_type_id from per_all_people_f where person_id = l_person_id
434 and CURRENT_EMPLOYEE_FLAG = 'Y';
435
436
437 l_newUserPersonId per_all_people_f.person_id%type;
438 l_person_type_id number;
439 l_object_version_number number;
440 l_asg_object_version_number number;
441 l_per_effective_start_date date;
442 l_per_effective_end_date date;
443 l_assignment_sequence number;
444 l_assignment_number varchar2(250);
445 l_assign_payroll_warning boolean;
446 l_proc varchar2(72) := g_package||'processExEmpTransaction';
447 l_transaction_id hr_api_transactions.transaction_id%type;
448 l_date Date;
449 begin
450 hr_utility.set_location('Entering:'|| l_proc, 5);
451 l_person_id := PersonId;
452 l_date := to_date(wf_engine.GetItemAttrText(WfItemType,WfItemKey,'P_EFFECTIVE_DATE'),'RRRR-MM-DD');
453 l_date := nvl(l_date,trunc(sysdate));
454 open csr_person;
455 fetch csr_person into l_newUserPersonId,l_person_type_id;
456
457 l_transaction_id := hr_transaction_ss.get_transaction_id
458 (p_item_type => WfItemType
459 ,p_item_key => WfItemKey);
460
461 if l_transaction_id is null then
462 begin
463 savepoint ex_emp_process;
464 if csr_person%found then
465 begin
466 select max(object_version_number) into l_object_version_number from per_all_people_f
467 where person_id = l_person_id
468 and l_date between effective_start_date and effective_end_date; --Added the condition as a part of bug#13739532
469 exception
470 when others then
471 null;
472 end;
473 hr_employee_api.re_hire_ex_employee
474 (p_validate => false
475 ,p_hire_date => l_date
476 ,p_person_id => l_person_id
477 ,p_per_object_version_number => l_object_version_number
478 ,p_person_type_id => l_person_type_id
479 ,p_rehire_reason => null
480 ,p_assignment_id => AssignmentId
481 ,p_asg_object_version_number => l_asg_object_version_number
482 ,p_per_effective_start_date => l_per_effective_start_date
483 ,p_per_effective_end_date => l_per_effective_end_date
484 ,p_assignment_sequence => l_assignment_sequence
485 ,p_assignment_number => l_assignment_number
486 ,p_assign_payroll_warning => l_assign_payroll_warning
487 );
488 end if;
489 PersonId := to_char(l_person_id);
490 AssignmentId := to_char(AssignmentId);
491 rollback to ex_emp_process;
492 EXCEPTION
493 WHEN OTHERS THEN
494 hr_utility.set_location('Exception:'|| l_proc, 10);
495 PersonId := null;
496 AssignmentId := null;
497 p_error_message := hr_utility.get_message;
498 rollback to ex_emp_process;
499 end;
500 else
501 process_selected_transaction
502 (p_item_type => WfItemType,
503 p_item_key => WfItemKey,
504 p_source => p_source);
505 PersonId := to_char(hr_process_person_ss.g_person_id);
506 AssignmentId := to_char(hr_process_person_ss.g_assignment_id);
507
508 end if;
509 close csr_person;
510
511 hr_utility.set_location('Leaving:'|| l_proc, 10);
512 EXCEPTION
513 WHEN OTHERS THEN
514 hr_utility.set_location('Exception:'|| l_proc, 10);
515 PersonId := null;
516 AssignmentId := null;
517 p_error_message := hr_utility.get_message;
518
519 end processExEmpTransaction;
520
521 FUNCTION get_ppg_kff_structcode(
522 p_bg_id in varchar2,
523 WfItemType in varchar2,
524 WfItemKey in varchar2)
525 RETURN varchar2 is
526
527 l_struct_code varchar2(100);
528 l_transaction_step_id number;
529 l_bg_id varchar2(20);
530
531 cursor struct_code_csr(bg_id varchar2) is
532 select fs.id_flex_structure_code people_grp_f_struct_code
533 from hr_organization_information oi,
534 fnd_id_flex_structures fs
535 where oi.organization_id = bg_id
536 and oi.org_information_context = 'Business Group Information'
537 and oi.org_information5 = fs.id_flex_num(+)
538 and fs.id_flex_code(+) = 'GRP'
539 and fs.application_id(+) = 801;
540
541 cursor csr_txn_step_id is
542 select trs.transaction_step_id
543 from hr_api_transaction_steps trs
544 where trs.transaction_id = hr_transaction_ss.get_transaction_id
545 (WfItemType ,WfItemKey )
546 and trs.api_name ='HR_PROCESS_PERSON_SS.PROCESS_API';
547
548 BEGIN
549
550 l_bg_id := p_bg_id;
551
552 if(p_bg_id is null or p_bg_id = '') then
553
554 open csr_txn_step_id;
555 fetch csr_txn_step_id into l_transaction_step_id;
556
557 if csr_txn_step_id % found then
558 l_bg_id :=hr_transaction_api.get_number_value
559 (p_transaction_step_id => l_transaction_step_id
560 ,p_name => 'P_BUSINESS_GROUP_ID');
561
562 if l_bg_id is null then
563 return null;
564 end if;
565
566 end if;
567
568 close csr_txn_step_id;
569
570 end if;
571
572 open struct_code_csr(l_bg_id);
573 fetch struct_code_csr into l_struct_code;
574
575 if struct_code_csr%found then
576 return l_struct_code;
577 else
578 return '';
579 end if;
580
581 Exception
582 when others then
583 raise;
584 END get_ppg_kff_structcode;
585
586 end hr_new_user_reg_ss;
[Home] [Help]