1 PACKAGE body hr_person_info_util_ss AS
2 /* $Header: hrperuts.pkb 120.1.12000000.2 2007/04/09 14:33:51 dbatra ship $*/
3
4 -- Global variables
5 g_package constant varchar2(75):='HR_PERSON_INFO_UTIL_SS.';
6 g_data_error exception;
7 g_flex_struct hr_dflex_utility.l_ignore_dfcode_varray := hr_dflex_utility.l_ignore_dfcode_varray(); -- 4644909
8 --
9 -- ---------------------------------------------------------------------------
10 -- --------------------- < check_pending_approval_items> ---------------------
11 -- ---------------------------------------------------------------------------
12 -- Purpose: This procedure will check whether there are any pending approval
13 -- items in workflow for a given process name and process section
14 -- name. If the p_process_name parameter is passed in, then this
15 -- procedure will be searching for Personal Information pending
16 -- approval items. This procedure returns three output parameters.
17 --
18 --
19 -- Input Parameters:
20 -- p_item_type - required.
21 -- p_process_name - required. It is the process name coded in the FND Form
22 -- function parameters.
23 -- p_api_name - This api_name in conjunction with the p_item_type and
24 -- p_item_key parameters will uniquely identify the
25 -- activity_id which was used to save recs to the trans
26 -- table.
27 -- p_result_code - This result code is used in collaboration with the derived
28 -- activity_id to check for pending approval items.
29 -- If it is null,it will be defaulted to 'SUBMIT_FOR_APPROVAL'
30 -- result code.
31 --
32 -- p_current_person_id - the person_id for whom the action is to be performed.
33 --
34 -- p_address_context - this is used by Address section in the Personal
35 -- Information Overview page. Since primary and secondary
36 -- address use the same api name, we need this parameter
37 -- to indicate further filtering by transaction values for
38 -- "P_PRIMARY_FLAG" equals to "Y" or "N".
39 -- Valid values are "PRIMARY" or "SECONDARY".
40 --
41 -- Output Parameters:
42 -- 1) p_multiple_item_keys - this parameter will have a value only if p_trans_rec_count >1.
43 -- If not it is null.
44 -- 2) p_pending_item_key - this parameter will return the pending item key value.
45 --
46 -- Inovked by: Java code.
47 -- ---------------------------------------------------------------------------
48 PROCEDURE check_pending_approval_items (
49 p_item_type in varchar2
50 ,p_process_name in varchar2
51 ,p_api_name in varchar2
52 ,p_result_code in varchar2 default null
53 ,p_current_person_id in number
54 ,p_address_context in varchar2 default null
55 ,p_multiple_item_keys out nocopy varchar2
56 ,p_pending_item_key out nocopy varchar2
57 )is
58
59 ln_trans_rec_count number default 0;
60 ln_count number default 0;
61 ln_trans_step_id number default null;
62 ln_trans_obj_vers_num hr_api_transaction_steps.object_version_number%type
63 default null;
64 ltt_active_wf_items hr_workflow_service.active_wf_trans_items_list;
65 ltt_trans_step_ids hr_util_web.g_varchar2_tab_type;
66 ltt_trans_obj_vers_nums hr_util_web.g_varchar2_tab_type;
67 ln_trans_step_rows NUMBER ;
68 lv_multiple_trans_step_ids varchar2(32000) default null;
69 lv_trans_step_ids varchar2(32000) default null;
70 ln_actid wf_item_attribute_values.number_value%type
71 default null;
72 ln_total_count number default 0;
73 ln_index number default 0;
74 -- vbala 01/11
75 lv_item_key varchar2(240) default null;
76 lv_multiple_item_keys varchar2(32000) default null;
77 lv_redirect_item_key varchar2(240) default null;
78 -- vbala 01/11
79 l_address_context varchar2(800) default null;
80 l_api_name varchar2(1000) default null;
81 l_process_section varchar2(500) default null;
82 l_section_context varchar2(400) default null;
83
84
85 BEGIN
86 --
87 -- ------------------------------------------------------------------
88 -- Check if there are any transactions waiting to be approved.
89 ---------------------------------------------------------------------------
90 -- 1) Find all item keys which have a status of "ACTIVE" for p_process_name
91 -- and the result code is the result code passed in or the default
92 -- 'SUBMIT_FOR_APPROVAL' result code.
93 -- 2) Return the count in p_trans_rec_count and p_multiple_trans_step_ids if
94 -- there is multiple transactions steps found. Otherwise,if there is only
95 -- 1 transaction step, return the value in p_trans_step_id output parm.
96 -- There can be defunct workflow processes. Therefore, we must
97 -- match active processes with transaction tables.
98 ---------------------------------------------------------------------------
99 --
100 -- The following function will return a PL/SQL table which has following
101 -- fields : Item Key, Activity ID, Result Code
102 --
103 l_api_name := p_api_name;
104 l_address_context := p_address_context;
105
106 WHILE (instr(l_api_name, '|') <> 0) LOOP
107 l_process_section := substr(l_api_name, 1, instr(l_api_name, '|')-1);
108 l_section_context := substr(l_address_context, 1, instr(l_address_context, '|')-1);
109 lv_multiple_item_keys := 'null';
110 lv_redirect_item_key := 'null';
111
112 IF (l_section_context = 'null') THEN
113 l_section_context := null;
114 END IF;
115 IF (l_process_section <> 'DISPLAY_OFF') THEN
116 ltt_active_wf_items := hr_workflow_service.get_active_wf_items
117 (p_item_type => p_item_type
118 ,p_process_name => p_process_name
119 ,p_current_person_id => p_current_person_id
120 ,p_api_name => l_process_section
121 ,p_activity_result_code => p_result_code
122 ,p_address_context => l_section_context
123 );
124 END IF;
125 l_api_name := substr(l_api_name, instr(l_api_name, '|')+1);
126 l_address_context := substr(l_address_context, instr(l_address_context, '|')+1);
127 ln_count := ltt_active_wf_items.count;
128 IF ln_count > 0
129 THEN
130 IF ln_count = 1
131 THEN
132 -- vbala 01/11
133 lv_multiple_item_keys := null;
134 lv_item_key := ltt_active_wf_items(1).active_item_key;
135 lv_redirect_item_key := ltt_active_wf_items(1).active_item_key;
136 -- vbala 01/11
137 ELSE
138 FOR j in 1..ln_count
139 LOOP
140 -- vbala 01/11
141 lv_multiple_item_keys := lv_trans_step_ids ||
142 ltt_active_wf_items(j).active_item_key || ',';
143 -- vbala 01/11
144 ln_total_count := ln_total_count + 1;
145 END LOOP;
146 --
147 -- now remove the last comma
148 -- vbala 01/11
149 lv_multiple_item_keys := rtrim(lv_multiple_item_keys, ',');
150 lv_item_key := null;
151 lv_redirect_item_key := ltt_active_wf_items(1).active_item_key;
152 -- vbala 01/11
153 END IF;
154 END IF;
155 --
156 -- vbala 01/11
157 p_multiple_item_keys := p_multiple_item_keys||lv_multiple_item_keys||'|';
158 p_pending_item_key := p_pending_item_key||lv_redirect_item_key||'|';
159 -- vbala 01/11
160 END LOOP;
161
162 END check_pending_approval_items;
163 --
164 -- ----------------------------------------------------------------------------
165 -- |-------------------------------< process_action >----------------------------|
166 -- ----------------------------------------------------------------------------
167 -- Purpose: This procedure will be called from the overview page, which will route
168 -- for the process section (e.g:- BASIC_DETAILS or MAIN_ADDRESS) to either
169 -- type of change or update page, depending on the link on the overview page.
170 --
171 -- Parameters:
172 -- Input
173 -- p_item_type - required. It is the item type for the workflow process.
174 -- p_item_key - required. It is the item key for the workflow process.
175 -- p_actid - required. It is the item key for the workflow process.
176 -- p_funcmode - required. It is the func mode for the workflow process.
177
178 -- Output Parameters:
179 -- 1) p_resultout - will populate the result code for the activity
180 -- --------------------------------------------------------------------------
181 PROCEDURE process_action
182 (p_item_type in varchar2
183 ,p_item_key in varchar2
184 ,p_actid in number
185 ,p_funcmode in varchar2
186 ,p_resultout out nocopy varchar2) is
187 --
188 lv_perinfo_process_section varchar2(50);
189 lv_perinfo_action_type varchar2(50);
190 lv_trans_submit varchar2(20);
191
192 ln_transaction_id number;
193
194 begin
195
196 -- get the itemattr TRAN_SUBMIT
197 lv_trans_submit := wf_engine.GetItemAttrText(itemtype => p_item_type,
198 itemkey => p_item_key,
199 aname => 'TRAN_SUBMIT');
200
201 -- Delete the transaction only if not from RETURN FOR CORRECTION
202 if (p_funcmode = 'RUN') AND (lv_trans_submit <> 'C') then
203
204 -- 22-May-2003: Bug 2967944: NS: Fetching transactionId from Hr_api_transactions
205 -- as new transaction are already deleted when cancel is pressed on any page (with V5 changes).
206 -- but WF attribute still exists, thus the api call to rollback fails with the following error
207 -- "ORA-20001: The primary key specified is invalid"
208
209 -- Clear the transaction before proceeding further, as this is required to support
210 -- the browser back button.
211 ln_transaction_id := pqh_ss_workflow.get_transaction_id(
212 p_itemtype => p_item_type
213 ,p_itemkey => p_item_key );
214 IF (ln_transaction_id is not null) THEN
215 hr_transaction_api.rollback_transaction(
216 p_transaction_id => ln_transaction_id);
217 END IF;
218
219 /* hr_transaction_web.rollback_transaction(itemtype => p_item_type
220 ,itemkey => p_item_key
221 ,actid => p_actid
222 ,funmode => p_funcmode
223 ,result => p_resultout ); */
224
225 -- also we need to nullify item attribute 'TRANSACTION_ID' in work flow engine
226 wf_engine.SetItemAttrText(itemtype => p_item_type,
227 itemkey => p_item_key,
228 aname => 'TRANSACTION_ID',
229 avalue => null);
230
231 end if;
232 -- set the result to DEFAULT: to stall the workflow engine
233 p_resultout := 'DEFAULT';
234
235 lv_perinfo_process_section := wf_engine.GetItemAttrText(itemtype => p_item_type,
236 itemkey => p_item_key,
237 aname => gv_wf_process_sect_attr_name);
238 lv_perinfo_action_type := wf_engine.GetItemAttrText(itemtype => p_item_type,
239 itemkey => p_item_key,
240 aname => gv_wf_action_type_attr_name);
241
242 if(lv_perinfo_process_section = gv_basic_details_sect) then
243 if (lv_perinfo_action_type = gv_update_attr_value) then
244 p_resultout := gv_update_basic_details_value;
245 elsif (lv_perinfo_action_type = gv_view_future_changes_value or
246 lv_perinfo_action_type = gv_view_pending_approval_value) then
247 p_resultout := gv_view_basic_details_value;
248 end if;
249 end if;
250
251 if(lv_perinfo_process_section = gv_main_address_sect) then
252 if (lv_perinfo_action_type = gv_update_attr_value) then
253 p_resultout := gv_update_main_address_value;
254 elsif (lv_perinfo_action_type = gv_add_attr_value) then
255 p_resultout := gv_add_main_address_value;
256 elsif (lv_perinfo_action_type = gv_view_future_changes_value or
257 lv_perinfo_action_type = gv_view_pending_approval_value) then
258 p_resultout := gv_view_main_address_value;
259 end if;
260 end if;
261
262 if(lv_perinfo_process_section = gv_secondary_address_sect) then
263 if (lv_perinfo_action_type = gv_update_attr_value) then
264 p_resultout := gv_update_second_address_value;
265 elsif (lv_perinfo_action_type = gv_add_attr_value) then
266 p_resultout := gv_add_second_address_value;
267 elsif (lv_perinfo_action_type = gv_view_future_changes_value or
268 lv_perinfo_action_type = gv_view_pending_approval_value) then
269 p_resultout := gv_view_second_address_value;
270 end if;
271 end if;
272
273 --
274 -- PB : For conatcts module
275 --
276 if(lv_perinfo_process_section = gv_contacts_sect) then
277 if (lv_perinfo_action_type = gv_add_upd_attr_value) then
278 p_resultout := gv_add_upd_contacts_value;
279 elsif (lv_perinfo_action_type = gv_del_attr_value) then
280 p_resultout := gv_del_contacts_value;
281 end if;
282 end if;
283 return;
284
285 if p_funcmode = 'CANCEL' then
286 p_resultout := 'COMPLETE:';
287 return;
288 end if;
289 exception
290 when others then
291 wf_core.Context
292 (g_package, 'process_action', p_item_type, p_item_key, to_char(p_actid), p_funcmode);
293 raise;
294 END process_action;
295
296 -- ----------------------------------------------------------------------------
297 -- |-------------------------------< is_duplicate_person >-----------------------|
298 -- ----------------------------------------------------------------------------
299 -- Purpose: This procedure can be used to check if there are any duplicate persons
300 -- this will internally call hr_generl2.is_duplicate_person function.
301 --
302 -- Parameters:
303 -- Input
304 -- p_first_name - required.
305 -- p_last_name - required.
306 -- p_national_identifier - required.
307 -- p_date_of_birth - required.
308
309 -- Output Parameters:
310 -- 1) p_resultout - will have a value of 0 for flase
311 -- 1 for true
312 -- ---------------------------------------------------------------------------
313
314 PROCEDURE is_duplicate_person
315 (p_first_name in varchar2
316 ,p_last_name in varchar2
317 ,p_national_identifier in varchar2
318 ,p_date_of_birth in date
319 ,p_effective_date in date
320 ,p_resultout out nocopy number
321 ) is
322
323
324 isDuplicate boolean := false;
325 l_session_date DATE;
326 l_full_name VARCHAR2(240);
327 l_duplicate_flag VARCHAR2(4);
328
329 BEGIN
330
331 -- Get the session date
332 BEGIN
333 SELECT se.effective_date
334 INTO l_session_date
335 FROM fnd_sessions se
336 WHERE se.session_id =USERENV('sessionid');
337 EXCEPTION
338 WHEN NO_DATA_FOUND THEN
339 -- insert the effective date into session to be used
340 -- by is_duplicate_person
341 IF(l_session_date is null) THEN
342 hr_utility.fnd_insert(p_effective_date);
343 END IF;
344 END;
345
346 -- Bug Fix : 2948405.
347 if fnd_profile.value('HR_CROSS_BUSINESS_GROUP') = 'N' then
348 hr_person.derive_full_name(p_first_name => p_first_name,
349 p_middle_names => null,
350 p_last_name => p_last_name,
351 p_known_as => null,
352 p_title => null,
353 p_suffix => null,
354 p_pre_name_adjunct => null,
355 p_date_of_birth => p_date_of_birth,
356 p_person_id => null,
357 p_business_group_id => fnd_global.per_business_group_id,
358 p_full_name => l_full_name,
359 p_duplicate_flag => l_duplicate_flag );
360
361 if l_duplicate_flag = 'Y' then
362 isDuplicate := true;
363 end if;
364
365 else
366
367 isDuplicate := hr_general2.is_duplicate_person(p_first_name,
368 p_last_name,
369 p_national_identifier,
370 p_date_of_birth);
371
372 end if;
373
374 -- call the hr_java_conv_util_ss to get the number out of boolean
375 -- to be passed to java
376
377 p_resultout := hr_java_conv_util_ss.get_number(isDuplicate);
378
379 EXCEPTION
380 WHEN OTHERS THEN
381 raise;
382
383 END is_duplicate_person;
384
385 --
386 --
387 -- Fix 2091186 Start
388 -- ----------------------------------------------------------------------------
389 -- |--------------------< create_ignore_df_validation >-----------------------|
390 -- ----------------------------------------------------------------------------
391 -- Purpose: To add descriptive flex field to ignorable list.
392 -- Parameters:
393 -- Input
394 -- p_flex_name - required.
395 -- ----------------------------------------------------------------------------
396
397 PROCEDURE create_ignore_df_validation
398 ( p_flex_name varchar2
399 ) IS
400
401 l_flex_code varchar2(40) := NULL;
402 -- l_add_struct hr_dflex_utility.l_ignore_dfcode_varray := hr_dflex_utility.l_ignore_dfcode_varray(); --4644909
403
404 BEGIN
405 g_flex_struct.extend(1);
406 g_flex_struct(g_flex_struct.count) := p_flex_name;
407
408 /* l_add_struct.extend(1);
409 l_add_struct(l_add_struct.count) := p_flex_name; */ -- 4644909
410 --
411 hr_dflex_utility.create_ignore_df_validation(p_rec => g_flex_struct);
412
413 END create_ignore_df_validation;
414 --
415 -- ----------------------------------------------------------------------------
416 -- |--------------------< remove_ignore_df_validation >-----------------------|
417 -- ----------------------------------------------------------------------------
418 -- Purpose: To remove descriptive flex field validation.
419 -- Parameters:
420 -- Input
421 -- none.
422 -- ----------------------------------------------------------------------------
423
424 PROCEDURE remove_ignore_df_validation
425 IS
426
427 BEGIN
428 g_flex_struct.delete; -- 4644909
429 hr_dflex_utility.remove_ignore_df_validation;
430 END remove_ignore_df_validation;
431 --
432 -- Fix 2091186 End
433
434 -- |--------------------< get_trns_employee_number >-----------------------|
435 -- ----------------------------------------------------------------------------
436 -- Purpose: To get the employee number stored in the basic details step. This
437 -- procedure will be called from assignment and supervisor wrappers to get the
438 -- employee number while hiring an applicant.
439 -- Parameters:
440 -- Input
441 -- none.
442 -- ----------------------------------------------------------------------------
443
444 PROCEDURE get_trns_employee_number
445 ( p_item_type varchar2
446 ,p_item_key varchar2
447 ,p_employee_number out nocopy varchar2
448 )IS
449
450 l_api_names hr_util_web.g_varchar2_tab_type;
451 l_trans_step_rows NUMBER ;
452 l_trans_step_ids hr_util_web.g_varchar2_tab_type;
453 l_transaction_step_id
454 hr_api_transaction_steps.transaction_step_id%type default null;
455 l_employee_number varchar2(30) default null;
456
457 BEGIN
458
459 hr_transaction_api.get_transaction_step_info
460 (p_item_type => p_item_type
461 ,p_item_key => p_item_key
462 ,p_transaction_step_id => l_trans_step_ids
463 ,p_api_name => l_api_names
464 ,p_rows => l_trans_step_rows);
465 --
466
467 FOR i in 0..l_trans_step_rows-1 LOOP
468 IF(l_api_names(i) = 'HR_PROCESS_PERSON_SS.PROCESS_API') THEN
469 l_transaction_step_id := l_trans_step_ids(i);
470 END IF;
471 END LOOP;
472
473 l_employee_number := hr_transaction_api.get_varchar2_value
474 (p_transaction_step_id => l_transaction_step_id
475 ,p_name => 'P_EMPLOYEE_NUMBER');
476
477 p_employee_number := l_employee_number;
478
479
480 END get_trns_employee_number;
481 --
482
483 procedure check_ni_unique(
484 p_national_identifier in varchar2 default null
485 ,p_business_group_id in number
486 ,p_person_id in number
487 ,p_ni_duplicate_warn_or_err out nocopy varchar2) is
488
489 l_warning boolean default false;
490 l_warning_or_error varchar2(20);
491
492 begin
493
494 hr_utility.clear_message();
495 hr_utility.clear_warning();
496
497 l_warning_or_error := fnd_profile.value('PER_NI_UNIQUE_ERROR_WARNING');
498 if l_warning_or_error is null then
499 l_warning_or_error:= 'WARNING';
500 end if;
501
502 if p_national_identifier is not null then
503
504 hr_ni_chk_pkg.check_ni_unique(p_national_identifier => p_national_identifier
505 ,p_person_id => p_person_id
506 ,p_business_group_id => p_business_group_id
507 ,p_raise_error_or_warning => l_warning_or_error);
508
509
510 l_warning := hr_utility.check_warning();
511 if l_warning then
512 p_ni_duplicate_warn_or_err := 'WARNING';
513 else
514 p_ni_duplicate_warn_or_err := 'NONE';
515 end if;
516 end if;
517
518 exception
519 when others then
520 if not l_warning then
521 p_ni_duplicate_warn_or_err := 'ERROR';
522 raise;
523 end if;
524 end check_ni_unique;
525 --
526
527 procedure validate_national_identifier(
528 p_national_identifier VARCHAR2,
529 p_birth_date DATE,
530 p_gender VARCHAR2,
531 p_event VARCHAR2 default 'WHEN-VALIDATE-RECORD',
532 p_person_id NUMBER,
533 p_business_group_id NUMBER,
534 p_legislation_code VARCHAR2,
535 p_effective_date DATE,
536 p_warning OUT NOCOPY VARCHAR2,
537 p_person_type_id NUMBER default NULL,
538 p_region_of_birth VARCHAR2 default NULL,
539 p_country_of_birth VARCHAR2 default NULL) IS
540
541 l_warning boolean default false;
542 l_out_warning varchar2(20);
543 l_out_ni per_all_people_f.national_identifier%type;
544 begin
545
546 if p_national_identifier is not null then
547
548 l_out_ni := hr_ni_chk_pkg.validate_national_identifier(
549 p_national_identifier => p_national_identifier
550 ,p_birth_date => p_birth_date
551 ,p_gender => p_gender
552 ,p_person_id => p_person_id
553 ,p_business_group_id => p_business_group_id
554 ,p_legislation_code => p_legislation_code
555 ,p_session_date => p_effective_date
556 ,p_warning => l_out_warning
557 ,p_person_type_id => p_person_type_id
558 ,p_region_of_birth => p_region_of_birth
559 ,p_country_of_birth => p_country_of_birth);
560
561 l_warning := l_out_warning = 'Y';
562 if l_warning then
563 p_warning := 'WARNING';
564 else
565 p_warning := 'NONE';
566 end if;
567 end if;
568
569 exception
570 when others then
571 if not l_warning then
572 p_warning := 'ERROR';
573 raise;
574 end if;
575
576
577 end validate_national_identifier;
578
579
580 END hr_person_info_util_ss;
581 --
[Home] [Help]