1 PACKAGE BODY hr_new_user_reg_ss AS
2 /* $Header: hrregwrs.pkb 120.1 2006/12/14 06:16:36 dbatra noship $*/
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
55 l_transaction_step_id number;
56 l_business_grp_Id number;
57
58 l_newUserPersonId per_all_people_f.person_id%type;
59 l_result varchar2(100);
60 l_proc varchar2(72) := g_package||'processNewUserTransaction';
61 begin
62
63 hr_utility.set_location('Entering:'|| l_proc, 5);
64
65 open csr_person;
66 fetch csr_person into l_newUserPersonId;
67
68 -- if the person Id is not in the database then a dummy person was created and rolled back in the database.
69 -- need to recreat the person again.
70
71 if csr_person%notfound then
72 hr_utility.set_location('Ranjan in side if csr_person%notfound :'|| l_proc, 5);
73 /*
74 hr_transaction_web.commit_transaction
75 (itemtype=>WfItemType,
76 itemkey=>WfItemKey,
77 actid=>'12345', -- can hard code this to anything this is not being used inside the call
78 funmode=>'RUN', -- the necessary mode to run the api in commit mode
79 result=>l_result); -- though we are getting the error results back we are not using them.
80 -- the commit process will call Process_api of hr_process_person_ss which
81 */
82 /* Replaced commit_transaction with process_selected_transaction, commit transaction will
83 call all the steps for a given item key. However we need only person and address apis
84 are
85 */
86
87 --ignore the emp number generation for newhire flow
88 hr_new_user_reg_ss.g_ignore_emp_generation := 'YES';
89
90
91 --bug 5665820
92 if(hr_general.get_xbg_profile='N' and hr_general.get_business_group_id is null) then
93
94 open csr_txn_step_id;
95 fetch csr_txn_step_id into l_transaction_step_id;
96 close csr_txn_step_id;
97
98
99
100
101 l_business_grp_Id :=hr_transaction_api.get_number_value
102 (p_transaction_step_id => l_transaction_step_id
103 ,p_name => 'P_BUSINESS_GROUP_ID');
104
105 fnd_profile.put('PER_BUSINESS_GROUP_ID',l_business_grp_Id);
106
107 end if;
108 --bug 5665820
109
110 process_selected_transaction
111 (p_item_type => WfItemType,
112 p_item_key => WfItemKey);
113
114 -- the commit process will call Process_api of hr_process_person_ss which
115 -- creates a person and returns us the person id and assignment_id.
116
117
118
119 PersonId := to_char(hr_process_person_ss.g_person_id);
120 AssignmentId := to_char(hr_process_person_ss.g_assignment_id);
121 end if;
122 -- close the cursor
123 close csr_person;
124
125 hr_utility.set_location('Leaving:'|| l_proc, 10);
126
127 EXCEPTION
128 WHEN OTHERS THEN
129 PersonId := null;
130 AssignmentId := null;
131 raise;
132
133 end processNewUserTransaction;
134
135 procedure process_selected_transaction
136 (p_item_type in varchar2
137 ,p_item_key in varchar2
138 ,p_ignore_warnings in varchar2 default 'Y'
139 ,p_validate in boolean default false
140 ,p_update_object_version in varchar2 default 'N'
141 ,p_effective_date in varchar2 default null
142 ,p_api_name in varchar2 default null) is
143 -- --------------------------------------------------------------------------
144 -- declare local variables
145 -- --------------------------------------------------------------------------
146 l_transaction_id hr_api_transactions.transaction_id%type;
147 l_application_error boolean := false;
148 l_object_version_error boolean := false;
149 l_obj_fatal_error boolean := false;
150 l_warning_error boolean := false;
151 l_ignore_warnings boolean;
152 l_obj_api_name varchar2(200);
153 l_api_error_name varchar2(200);
154
155 -- Generic Cursor
156 cursor csr_trs is
157 select trs.transaction_step_id
158 ,trs.api_name
159 ,trs.item_type
160 ,trs.item_key
161 ,trs.activity_id
162 ,trs.creator_person_id
163 from hr_api_transaction_steps trs
164 where trs.transaction_id = l_transaction_id
165 and trs.api_name in ('HR_PROCESS_PERSON_SS.PROCESS_API',
166 'HR_PROCESS_ADDRESS_SS.PROCESS_API',
167 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API',
168 'HR_PROCESS_CONTACT_SS.PROCESS_CREATE_CONTACT_API')
169 order by trs.processing_order,trs.transaction_step_id;
170
171 --Selective cursor
172 cursor csr_sel_trs is
173 select trs.transaction_step_id
174 ,trs.api_name
175 ,trs.item_type
176 ,trs.item_key
177 ,trs.activity_id
178 ,trs.creator_person_id
179 from hr_api_transaction_steps trs
180 where trs.transaction_id = l_transaction_id
181 and trs.api_name = p_api_name
182 order by trs.processing_order;
183
184 begin
185 -- set the ignore warnings flag
186 if upper(p_ignore_warnings) = 'Y' then
187 l_ignore_warnings := true;
188 else
189 l_ignore_warnings := false;
190 end if;
191 -- get the transaction id
192 l_transaction_id := hr_transaction_ss.get_transaction_id
193 (p_item_type => p_item_type
194 ,p_item_key => p_item_key);
195 if l_transaction_id is null then
196 return;
197 end if;
198
199
200 hr_process_person_ss.g_person_id := null;
201 hr_process_person_ss.g_assignment_id := null;
202
203 if(p_api_name is not null) then
204 -- process selected api step
205 for csr_sel in csr_sel_trs loop
206 begin
207 hr_transaction_ss.process_web_api_call
208 (p_transaction_step_id => csr_sel.transaction_step_id
209 ,p_api_name => csr_sel.api_name
210 ,p_validate => p_validate);
211 exception
212 when hr_utility.hr_error then
213 -- an application error has been raised. set the error flag
214 -- to indicate an application error
215 -- the error message should of been set already
216 hr_message.provide_error;
217 l_api_error_name := hr_message.last_message_name;
218 if l_api_error_name = 'HR_7155_OBJECT_INVALID' then
219 l_obj_fatal_error := true;
220 exit;
221 else
222 l_application_error := true;
223
224 -------------------------------------------------------------------
225 -- 05/09/2002 Bug 2356339 Fix Begins
226 -- We need to exit the loop here when there is an application error.
227 -- This will happen when apporval is required and the final approver
228 -- approved the change. When the Workflow responsibility to approve
229 -- the transaction has no Security Profile attached, and the
230 -- Business Group profile option is null and Cross Business Group
231 -- equals to 'N', you will get an Application Error after the final
232 -- approver approves the transaction. This problem usually happens
233 -- in New Hire or Applicant Hire whereby the new employee created
234 -- is not in the per_person_list table. In hr_process_person_ss.
235 -- process_api, it call hr_employee_api.create_employee which
236 -- eventually will call dt_api.return_min_start_date. This
237 -- dt_api.return_min_start_date accesses the per_people_f secured
238 -- view, you will get HR_7182_DT_NO_MIN_MAX_ROWS error with the
239 -- following error text:
240 -- No DateTrack row found in table per_people_f.
241 -- When that happens, the l_application_error is set to true. However,
242 -- if there is no Exit statement, this code will continue to call
243 -- the next transaction step. Each of the subsequent step will fail
244 -- with an error until the last step is called and the error from
245 -- the last step will overwrite the initial real error message.
246 -- Without the exit statement, it will be very difficult to pinpoint
247 -- the location where the real problem occurred.
248 ---------------------------------------------------------------------
249
250 EXIT; -- Bug 2356339 Fix
251
252 -- 05/09/2002 Bug 2356339 Fix Ends
253
254 end if;
255 raise;
256 when others then
257 -- a system error has occurred so raise it to stop
258 -- processing of the transaction steps
259 raise;
260 end;
261 end loop;
262 else
263 -- select each transaction steps to process
264 for i in csr_trs loop
265 begin
266 -- call the API for the transaction step
267 -- don't call the assignment step, if coming back from
268 -- SFL and SFL is done on Assignment page
269 if (i.api_name <> 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API') OR
270 ((i.api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API') and
271 (NOT is_Assignment_SFL(i.transaction_step_id))) then
272 if p_update_object_version = 'Y' then
273 -- update object version for each step
274 l_obj_api_name := substr(i.api_name,1, instr(i.api_name,'.'));
275 l_obj_api_name := l_obj_api_name || g_update_object_version;
276 hr_transaction_ss.process_web_api_call
277 (p_transaction_step_id => i.transaction_step_id
278 ,p_api_name => l_obj_api_name
279 ,p_extra_parameter_name => 'p_login_person_id'
280 ,p_extra_parameter_value => i.creator_person_id
281 ,p_validate => false);
282 elsif p_effective_date is not null then
283 --validate api with the new p_effective_date
284 hr_transaction_ss.process_web_api_call
285 (p_transaction_step_id => i.transaction_step_id
286 ,p_api_name => i.api_name
287 ,p_extra_parameter_name => 'p_effective_date'
288 ,p_extra_parameter_value => p_effective_date
289 ,p_validate => p_validate);
290 else
291 --validate api
292 hr_transaction_ss.process_web_api_call
293 (p_transaction_step_id => i.transaction_step_id
294 ,p_api_name => i.api_name
295 ,p_validate => p_validate);
296 end if;
297 end if;
298 -- do we ignore any warnings which may have been set?
299 if not l_ignore_warnings then
300 -- check to see if any warnings have been set
301 if (not l_warning_error) and
302 hr_emp_error_utility.exists_warning_text
303 (p_item_type => i.item_type
304 ,p_item_key => i.item_key
305 ,p_actid => i.activity_id) then
306 -- set the warning flag to true
307 l_warning_error := true;
308 end if;
309 end if;
310 exception
311 when hr_utility.hr_error then
312 -- an application error has been raised. set the error flag
313 -- to indicate an application error
314 -- the error message should of been set already
315 hr_message.provide_error;
316 l_api_error_name := hr_message.last_message_name;
317 if l_api_error_name = 'HR_7155_OBJECT_INVALID' then
318 l_obj_fatal_error := true;
319 exit;
320 --if i.api_name = 'BEN_PROCESS_COMPENSATION_W.PROCESS_API' then
321 -- fnd_message.set_name('PER','HR_FATAL_OBJECT_ERROR');
322 -- l_obj_fatal_error := true;
323 -- exit;
324 --end if;
325 else
326 l_application_error := true;
327
328 -------------------------------------------------------------------
329 -- 05/09/2002 Bug 2356339 Fix Begins
330 -- We need to exit the loop here when there is an application error.
331 -- This will happen when apporval is required and the final approver
332 -- approved the change. When the Workflow responsibility to approve
333 -- the transaction has no Security Profile attached, and the
334 -- Business Group profile option is null and Cross Business Group
335 -- equals to 'N', you will get an Application Error after the final
336 -- approver approves the transaction. This problem usually happens
337 -- in New Hire or Applicant Hire whereby the new employee created
338 -- is not in the per_person_list table. In hr_process_person_ss.
339 -- process_api, it call hr_employee_api.create_employee which
340 -- eventually will call dt_api.return_min_start_date. This
341 -- dt_api.return_min_start_date accesses the per_people_f secured
342 -- view, you will get HR_7182_DT_NO_MIN_MAX_ROWS error with the
343 -- following error text:
344 -- No DateTrack row found in table per_people_f.
345 -- When that happens, the l_application_error is set to true. However,
346 -- if there is no Exit statement, this code will continue to call
347 -- the next transaction step. Each of the subsequent step will fail
348 -- with an error until the last step is called and the error from
349 -- the last step will overwrite the initial real error message.
350 -- Without the exit statement, it will be very difficult to debug the
351 -- location when the real problem occurs.
352 ---------------------------------------------------------------------
353
354 EXIT; -- Bug 2356339 Fix
355
356 -- 05/09/2002 Bug 2356339 Fix Ends
357
358 end if;
359 raise;
360 when others then
361 -- a system error has occurred so raise it to stop
362 -- processing of the transaction steps
363 raise;
364 end;
365 end loop;
366 end if;
367
368 -- check to see if any application errors where raised
369 if l_obj_fatal_error then
370 fnd_message.set_name('PER','HR_FATAL_OBJECT_ERROR');
371 raise hr_utility.hr_error;
372 elsif l_object_version_error then
373 fnd_message.set_name('PER','HR_7155_OBJECT_INVALID');
374 raise hr_utility.hr_error;
375 elsif l_application_error or l_warning_error then
376 raise hr_utility.hr_error;
377 end if;
378
379 exception
380 when others then
381 -- an application error, warning or system error was raised so
382 -- keep raising it so the calling process must handle it
383 raise;
384 end process_selected_transaction;
385
386
387 end hr_new_user_reg_ss;