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