List of functions available in per_extract_utility package
List of functions available in per_extract_utility package

 1,177 total views

In this article we will look into the list of functions available in per_extract_utility seeded pl/sql package which will be referred in User Entity sql queries.

Want to learn Fusion Technical tools? Checkout this article

There are set of seeded functions in the per_extract_utility package which are being referred in the User Entity SQL queries. These functions can be used in out BI Report queries as well, however we need to be aware of the inputs and outputs of these functions in order to use them.

As you all know we do not have access to create or view packages in Cloud HCM. However we can get the list of functions available in the package but not the body of the package where the entire sql lies.

You can get these list of functions in a package by running the below SQL Query:

select text from all_source
where name = upper('per_extract_utility')

Here is the complete list of functions that are present in per_extract_utility package.

Functions in per_extract_utility Package:

-- ----------------------------------------------------------------------------
" -- | Get Absence Reason							      |"
-- ----------------------------------------------------------------------------
FUNCTION get_absence_reason (p_abs_attendance_reason_id IN NUMBER)
RETURN varchar2;
-- ----------------------------------------------------------------------------
" -- | Get Territory Name							   |"
-- ----------------------------------------------------------------------------
FUNCTION get_territory_name(p_territory_code VARCHAR2)
RETURN VARCHAR2;
-- ----------------------------------------------------------------------------
" -- | Get Column Value of the Given column name 						   |"
-- ----------------------------------------------------------------------------
FUNCTION get_by_primary_key(p_tab_or_view_name VARCHAR2,p_primary_key_name VARCHAR2, p_primary_key_value NUMBER, p_return_column_name VARCHAR2)
RETURN VARCHAR2;
-- ----------------------------------------------------------------------------
-- | Get Column Value of the Given column name with DE |
-- ----------------------------------------------------------------------------
FUNCTION get_by_primary_key_de(p_tab_or_view_name VARCHAR2,p_primary_key_name VARCHAR2, p_primary_key_value NUMBER, p_return_column_name VARCHAR2,p_date_effective DATE)
RETURN VARCHAR2;
-- ----------------------------------------------------------------------------
-- | Get Look Up Meaning |
-- ----------------------------------------------------------------------------
FUNCTION get_decoded_lookup(p_lookup_type VARCHAR2,p_lookup_code VARCHAR2)
RETURN VARCHAR2;
-- ----------------------------------------------------------------------------
-- | Get Look Up Description |
-- ----------------------------------------------------------------------------
FUNCTION get_decoded_lookup_description(p_lookup_type VARCHAR2,p_lookup_code VARCHAR2)
RETURN VARCHAR2;
-- ----------------------------------------------------------------------------
-- | Get Look Up Enabled Flag |
-- ----------------------------------------------------------------------------
FUNCTION get_decoded_lookup_enabled(p_lookup_type VARCHAR2,p_lookup_code VARCHAR2)
RETURN VARCHAR2;
-- ----------------------------------------------------------------------------
-- | Get User Person Type Value Using Assignment ID |
-- ----------------------------------------------------------------------------
FUNCTION get_user_person_type(p_assignment_id NUMBER,p_date_effective DATE)
RETURN VARCHAR2;
-- ----------------------------------------------------------------------------
-- | Get User Standard Working Hours Using Assignment ID |
-- ----------------------------------------------------------------------------
FUNCTION get_standard_working_hours(p_assignment_id NUMBER,p_date_effective DATE)
RETURN NUMBER;
-- ----------------------------------------------------------------------------
-- | Get Tree Version Name using Tree Version ID |
-- ----------------------------------------------------------------------------
FUNCTION get_tree_version_name(p_tree_version_id VARCHAR2)
RETURN VARCHAR2;
-- ----------------------------------------------------------------------------
-- | Get Organization Names Hierarchy  |
-- ----------------------------------------------------------------------------
FUNCTION get_org_name_hierarchy(p_pk1_value VARCHAR2,p_tree_structure_code VARCHAR2,p_tree_code VARCHAR2,p_tree_version_id VARCHAR2,p_date_effective DATE,p_distance number)
RETURN VARCHAR2;
-- ----------------------------------------------------------------------------
-- | Get Territory Names Hierarchy  |
-- ----------------------------------------------------------------------------
FUNCTION get_territory_name_hierarchy(p_pk1_value VARCHAR2,p_tree_structure_code VARCHAR2,p_tree_code VARCHAR2,p_tree_version_id VARCHAR2,p_distance number)
RETURN VARCHAR2;
-- ----------------------------------------------------------------------------
-- | Get Department Names Hierarchy  |
-- ----------------------------------------------------------------------------
FUNCTION get_dep_name_hierarchy(p_pk1_value VARCHAR2,p_tree_structure_code VARCHAR2,p_tree_code VARCHAR2,p_tree_version_id VARCHAR2,p_date_effective DATE,p_distance number)
RETURN VARCHAR2;
-- ----------------------------------------------------------------------------
-- | Get Position Names Hierarchy  |
-- ----------------------------------------------------------------------------
FUNCTION get_pos_name_hierarchy(p_pk1_value VARCHAR2,p_tree_structure_code VARCHAR2,p_tree_code VARCHAR2,p_tree_version_id VARCHAR2,p_date_effective DATE,p_distance number)
RETURN VARCHAR2;
-- ----------------------------------------------------------------------------
-- | Get Manager Hierarchy  |
-- ----------------------------------------------------------------------------
FUNCTION get_manager_hierarchy(p_person_id VARCHAR2,p_date_effective DATE,p_manager_level number)
RETURN VARCHAR2;
-- ----------------------------------------------------------------------------
-- | Get Email ID  |
-- ----------------------------------------------------------------------------
FUNCTION get_person_email_id(p_person_id VARCHAR2,p_date_effective DATE,p_email_type varchar2)
RETURN VARCHAR2;
-- ----------------------------------------------------------------------------
-- | Get Maximum Re Hire date Using Assignment ID |
-- ----------------------------------------------------------------------------
FUNCTION get_max_rehire_date(p_assignment_id NUMBER,p_action_code VARCHAR2)
RETURN DATE;
-- ----------------------------------------------------------------------------
-- | Get Minimum Re Hire date Using Assignment ID |
-- ----------------------------------------------------------------------------
FUNCTION get_min_rehire_date(p_assignment_id NUMBER,p_action_code VARCHAR2)
RETURN DATE;
-- ----------------------------------------------------------------------------
-- | Get Maximum Work Relation ship date Using Person ID |
-- ----------------------------------------------------------------------------
FUNCTION get_max_workrelationship_date(p_person_id NUMBER)
RETURN DATE;
-- ----------------------------------------------------------------------------
-- | Get Minimum Work Relation ship date Using Person ID |
-- ----------------------------------------------------------------------------
FUNCTION get_min_workrelationship_date(p_person_id NUMBER)
RETURN DATE;
-- ----------------------------------------------------------------------------
-- | Get Address Type Using Address Id and Effective Date |
-- ----------------------------------------------------------------------------
FUNCTION get_address_type(p_address_id NUMBER,p_effective_date DATE)
RETURN VARCHAR2;
-- ----------------------------------------------------------------------------
-- | Get Work Schedule Exepction Using Assignment Id and Effective Date |
-- ----------------------------------------------------------------------------
FUNCTION get_work_sch_exception_code(p_assignment_id NUMBER,p_effective_date DATE)
RETURN VARCHAR2;
-- ----------------------------------------------------------------------------
-- | Get Effective Sequence Using Assignment id |
-- ----------------------------------------------------------------------------
FUNCTION get_next_assignment_sequence(p_assignment_id NUMBER,p_effective_date DATE)
RETURN NUMBER;
-- ----------------------------------------------------------------------------
" -- | Get Given Atribute Column Value of FND_LOOKUP_VALUES_B  						   |"
-- ----------------------------------------------------------------------------
FUNCTION get_fnd_lookup_values_flex(p_lookup_type VARCHAR2,p_lookup_code VARCHAR2, p_return_column_name VARCHAR2)
RETURN VARCHAR2;
-- ----------------------------------------------------------------------------
-- | Get Legislative Data Group Name Using Assignment id |
-- ----------------------------------------------------------------------------
FUNCTION get_ldg_name(p_assignment_id NUMBER,p_effective_date DATE)
RETURN VARCHAR2;
-- ----------------------------------------------------------------------------
-- | Get Last Rehire Date Using Person Id  |
-- ----------------------------------------------------------------------------
FUNCTION get_latest_rehire_date(p_person_id NUMBER,p_effective_date DATE)
RETURN DATE;
-- ----------------------------------------------------------------------------
-- | Get Last Termination Date Using Person Id  |
-- ----------------------------------------------------------------------------
FUNCTION get_latest_termination_date(p_person_id NUMBER,p_effective_date DATE)
RETURN DATE;
-- ----------------------------------------------------------------------------
-- | Get future Rehire Date Using Person Id  |
-- ----------------------------------------------------------------------------
FUNCTION get_future_rehire_date(p_person_id NUMBER,p_effective_date DATE)
RETURN DATE;
-- ----------------------------------------------------------------------------
-- | Get Image Id Using Person ID and primary Flag  |
-- ----------------------------------------------------------------------------
FUNCTION get_person_image_id(p_person_id NUMBER,p_primary_flag VARCHAR2)
RETURN NUMBER;
-- ----------------------------------------------------------------------------
" -- | Is Give Person Is also an Employee							      |"
-- ----------------------------------------------------------------------------
FUNCTION is_person_an_employee (p_person_id IN NUMBER,p_effective_date DATE)
RETURN varchar2;
-- ----------------------------------------------------------------------------
" -- | Get Primary NID Number Using Primary NID ID 							      |"
-- ----------------------------------------------------------------------------
FUNCTION get_national_identifier_number (p_national_identifier_id IN NUMBER)
RETURN varchar2;
-- ----------------------------------------------------------------------------
" -- | Get System GUID  							      |"
-- ----------------------------------------------------------------------------
FUNCTION get_system_guid
RETURN varchar2;
-- ----------------------------------------------------------------------------
" -- | Get System DateTime							      |"
-- ----------------------------------------------------------------------------
FUNCTION get_system_date_and_time
RETURN varchar2;
-- ----------------------------------------------------------------------------
" -- | Get Object GUID							      |"
-- ----------------------------------------------------------------------------
FUNCTION get_object_guid(p_object_name VARCHAR2,p_object_id IN NUMBER)
RETURN varchar2;
-- ----------------------------------------------------------------------------
" -- | Get set code							   |"
-- ----------------------------------------------------------------------------
FUNCTION get_set_code(p_set_id NUMBER)
RETURN VARCHAR2;
-- ----------------------------------------------------------------------------
" -- | Get set name							   |"
-- ----------------------------------------------------------------------------
FUNCTION get_set_name(p_set_id NUMBER)
RETURN VARCHAR2;
-- ----------------------------------------------------------------------------
" -- | Get Delivery Method Values		      |"
-- ----------------------------------------------------------------------------
FUNCTION get_per_del_method_values (p_del_fk_id IN NUMBER,p_del_method IN VARCHAR, p_column_name IN VARCHAR)
RETURN varchar2;
-- ----------------------------------------------------------------------------
-- | Get Location Email
-- --------------------------------------------------------------------------
FUNCTION get_location_email(p_location_id NUMBER, p_effective_date DATE)
RETURN VARCHAR2;
/* =====================================================================
Name    : get_location_BU
---------------------------------------------------------------------*/
FUNCTION get_location_bu(p_set_id NUMBER, p_effective_date DATE)
RETURN VARCHAR2;
-- ---------------------------------------------------------------------------
-- get_hcm_ess_job_def
-- ---------------------------------------------------------------------------
-- Description
--   Returns the ess hcm job def path
-- ---------------------------------------------------------------------------
FUNCTION get_hcm_ess_job_def RETURN VARCHAR2 DETERMINISTIC;
-- ---------------------------------------------------------------------------
-- get_hcm_ess_job_def_parent
-- ---------------------------------------------------------------------------
-- Description
--   Returns the ess hcm job def path
-- ---------------------------------------------------------------------------
FUNCTION get_hcm_ess_job_def_parent RETURN VARCHAR2 DETERMINISTIC;
-- ---------------------------------------------------------------------------
-- get_level
-- ---------------------------------------------------------------------------
-- Description
--   returns the hierarchy level of a Block to create order in FSM Export
-- ---------------------------------------------------------------------------
FUNCTION get_level
(p_report_block_id IN number) RETURN number;
-- ----------------------------------------------------------------------------
-- | Set the flow description |
-- ----------------------------------------------------------------------------
PROCEDURE set_flow_description
(pBaseFlowName VARCHAR2,
pDescription VARCHAR2);
-- ----------------------------------------------------------------------------
-- | Get User Person Type Value Using Assignment ID |
-- ----------------------------------------------------------------------------
FUNCTION get_user_person_type(p_assignment_id NUMBER,p_date_effective DATE, p_sequence NUMBER)
RETURN VARCHAR2;
-- ----------------------------------------------------------------------------
-- | Get User Standard Working Hours Using Assignment ID |
-- ----------------------------------------------------------------------------
FUNCTION get_standard_working_hours(p_assignment_id NUMBER,p_date_effective DATE, p_sequence NUMBER)
RETURN NUMBER;
-- ----------------------------------------------------------------------------
-- | Get User Standard Working Hours Frequency Using Assignment ID |
-- ----------------------------------------------------------------------------
FUNCTION get_standard_working_frequency(p_assignment_id NUMBER,p_date_effective DATE)
RETURN VARCHAR2 DETERMINISTIC;
-- ---------------------------------------------------------------------------
-- get_person_children_count
-- ---------------------------------------------------------------------------
-- Description
--   returns the total number of children for person
-- ---------------------------------------------------------------------------
FUNCTION get_person_children_count
(p_person_id NUMBER, p_contact_type VARCHAR2, p_start_dependent_age NUMBER, p_end_dependent_age NUMBER, p_statutory_dependent VARCHAR2, p_date_effective DATE) RETURN number;
-- ----------------------------------------------------------------------------
-- | Get Parameter Value By Flow Instance |
-- ----------------------------------------------------------------------------
FUNCTION get_param_by_flow_instance(p_instance_name VARCHAR2, p_ess_parameter_name VARCHAR2)
RETURN VARCHAR2;
-- ----------------------------------------------------------------------------
-- | Get Parameter Value By Payroll Action |
-- ----------------------------------------------------------------------------
FUNCTION get_param_by_payroll_action(p_payroll_action_id NUMBER, p_ess_parameter_name VARCHAR2)
RETURN VARCHAR2;
-- ----------------------------------------------------------------------------
FUNCTION get_global_language  RETURN varchar2;
-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
-- | Get TRANSFER status for the Worker|
-- ----------------------------------------------------------------------------
FUNCTION get_person_transfer_status(p_assignment_id NUMBER,p_date DATE)
RETURN VARCHAR2;
-- ----------------------------------------------------------------------------
-- | Get Extended Look Up Code |
-- ----------------------------------------------------------------------------
FUNCTION get_decoded_ext_lookup_code(p_lookup_type VARCHAR2,p_lookup_code VARCHAR2, p_legislation_code VARCHAR2)
RETURN VARCHAR2;
-- ----------------------------------------------------------------------------
-- | Get Extended Look Up Meaning |
-- ----------------------------------------------------------------------------
FUNCTION get_decoded_ext_lookup_meaning(p_lookup_type VARCHAR2,p_lookup_code VARCHAR2, p_legislation_code VARCHAR2)
RETURN VARCHAR2;

We don’t have access to the source code of these functions in Cloud HCM. However if you have any on-premise EBS application access, you can get the source code from that instance. For most of the pl/sql packages code has been taken from EBS only.. so you can refer them to get an idea of what exactly the functions are doing.

Here is the source code for this package taken from an EBS instance:

DROP PACKAGE BODY FUSION.PER_EXTRACT_UTILITY;

CREATE OR REPLACE PACKAGE BODY FUSION.per_extract_utility AS
/*$Header: fusionapps/hcm/per/db/plsql/per_extract_utility.pkb /st_fusionapps_11.1.8hcm/8 2014/11/27 21:59:23 jiafu Exp $*/

  -- package variables
  g_package  VARCHAR2(33) := 'per_extract_utility.';

-- ----------------------------------------------------------------------------
-- | Get Absence Reason
-- ----------------------------------------------------------------------------

 FUNCTION get_absence_reason (p_abs_attendance_reason_id IN NUMBER)
    RETURN varchar2 is
    l_proc          VARCHAR2(80) := g_package || 'get_absence_reason';
    l_reason_name   VARCHAR2(240) := '';

    CURSOR  absence_reason(p_abs_attendance_reason_id NUMBER) IS
    select  abs_reason.name -- hl.meaning
    from per_abs_attendance_reasons  abs_reason,hcm_lookups hl
    where
    abs_attendance_reason_id = p_abs_attendance_reason_id and  hl.lookup_type (+) = 'ABSENCE_REASON' and
    hl.lookup_code (+) = abs_reason.name;

    BEGIN
      hr_utility.set_location ('Entering:'|| l_proc, 10);
       OPEN absence_reason(p_abs_attendance_reason_id);
        fetch  absence_reason into l_reason_name;
       CLOSE absence_reason;
      hr_utility.set_location ('Leaving:' || l_proc,20);
      RETURN l_reason_name;

EXCEPTION
  WHEN OTHERS THEN
    IF(absence_reason%ISOPEN) THEN
      CLOSE absence_reason;
    END IF;

    END get_absence_reason;


/* =====================================================================
  Name    : get_territory_name
  Purpose : To get NLS_TERRITORY (Territory Name) if TERRITORY_CODE is given,
  Currently supported Object Types:
  TERRITORY_CODE -- Direct
  NLS_TERRITORY -- Derive Person Id from 'FND_TERRITORIES_VL'
  Returns : Returns country name
  ---------------------------------------------------------------------*/


FUNCTION get_territory_name(p_territory_code VARCHAR2)
RETURN VARCHAR2 IS

l_territory_name VARCHAR2(100) DEFAULT NULL;

CURSOR csr_get_territory_name_by_code(l_territory_code VARCHAR2) IS
select TERRITORY_SHORT_NAME from FND_TERRITORIES_VL tt where tt.TERRITORY_CODE = l_territory_code;

BEGIN

OPEN csr_get_territory_name_by_code(p_territory_code);
FETCH csr_get_territory_name_by_code into l_territory_name;
CLOSE csr_get_territory_name_by_code;
--select TERRITORY_SHORT_NAME into l_territory_name from FND_TERRITORIES_VL where TERRITORY_CODE = p_territory_code;
RETURN l_territory_name;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_territory_name_by_code%ISOPEN) THEN
      CLOSE csr_get_territory_name_by_code;
    END IF;

END get_territory_name;



/* =====================================================================
  Name    : get_by_primary_key
  Purpose : get column value of the given column name from given table,
  ---------------------------------------------------------------------*/


FUNCTION get_by_primary_key(p_tab_or_view_name VARCHAR2,p_primary_key_name VARCHAR2, p_primary_key_value NUMBER, p_return_column_name VARCHAR2)
RETURN VARCHAR2 IS

TYPE dynamic_cursor  IS REF CURSOR;
v_cursor    dynamic_cursor;
v_stmt_str VARCHAR2(2000);
l_return_value VARCHAR2(200) DEFAULT null;

BEGIN
v_stmt_str := 'SELECT ' || p_return_column_name || ' FROM ' || p_tab_or_view_name || ' WHERE ' || p_primary_key_name || ' = ' || p_primary_key_value;
OPEN v_cursor FOR v_stmt_str;
FETCH  v_cursor INTO l_return_value;
CLOSE v_cursor;

return l_return_value;

EXCEPTION
  WHEN OTHERS THEN
    IF(v_cursor%ISOPEN) THEN
      CLOSE v_cursor;
    END IF;

END get_by_primary_key;


/* =====================================================================
  Name    : get_by_primary_key_de
  Purpose : get column value of the given column name from given table,
            Using Date Effective
  ---------------------------------------------------------------------*/


FUNCTION get_by_primary_key_de(p_tab_or_view_name VARCHAR2,p_primary_key_name VARCHAR2, p_primary_key_value NUMBER, p_return_column_name VARCHAR2,p_date_effective DATE)
RETURN VARCHAR2 IS

TYPE dynamic_cursor  IS REF CURSOR;
v_cursor    dynamic_cursor;
v_stmt_str VARCHAR2(2000);
l_return_value VARCHAR2(200) DEFAULT null;

BEGIN
v_stmt_str := 'SELECT ' || p_return_column_name || ' FROM ' || p_tab_or_view_name || ' WHERE ' || p_primary_key_name || ' = ' || p_primary_key_value || ' AND ( :date_effective between effective_start_date and effective_end_date)' ;
OPEN v_cursor FOR v_stmt_str USING p_date_effective;
FETCH  v_cursor INTO l_return_value;
CLOSE v_cursor;

return l_return_value;

EXCEPTION
  WHEN OTHERS THEN
    IF(v_cursor%ISOPEN) THEN
      CLOSE v_cursor;
    END IF;

END get_by_primary_key_de;



/* =====================================================================

  ---------------------------------------------------------------------*/

FUNCTION get_decoded_lookup(p_lookup_type VARCHAR2,p_lookup_code VARCHAR2)
RETURN VARCHAR2 IS

l_lookup_meaning VARCHAR2(100) DEFAULT NULL;

CURSOR csr_get_lookup_meaning(l_lookup_type VARCHAR2,l_lookup_code VARCHAR2) IS
select meaning from hcm_lookups where lookup_type = l_lookup_type and lookup_code = l_lookup_code;
BEGIN

OPEN csr_get_lookup_meaning(p_lookup_type,p_lookup_code);
FETCH csr_get_lookup_meaning into l_lookup_meaning;
CLOSE csr_get_lookup_meaning;

RETURN l_lookup_meaning;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_lookup_meaning%ISOPEN) THEN
      CLOSE csr_get_lookup_meaning;
    END IF;

END get_decoded_lookup;



/* =====================================================================
  Get Look up Description
  ---------------------------------------------------------------------*/

FUNCTION get_decoded_lookup_description(p_lookup_type VARCHAR2,p_lookup_code VARCHAR2)
RETURN VARCHAR2 IS

l_lookup_description VARCHAR2(100) DEFAULT NULL;

CURSOR csr_get_lookup_description(l_lookup_type VARCHAR2,l_lookup_code VARCHAR2) IS
select description from hcm_lookups where lookup_type = l_lookup_type and lookup_code = l_lookup_code;
BEGIN

OPEN csr_get_lookup_description(p_lookup_type,p_lookup_code);
FETCH csr_get_lookup_description into l_lookup_description;
CLOSE csr_get_lookup_description;

RETURN l_lookup_description;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_lookup_description%ISOPEN) THEN
      CLOSE csr_get_lookup_description;
    END IF;

END get_decoded_lookup_description;




/* =====================================================================
  Get Look up Enabled Flag
  ---------------------------------------------------------------------*/

FUNCTION get_decoded_lookup_enabled(p_lookup_type VARCHAR2,p_lookup_code VARCHAR2)
RETURN VARCHAR2 IS

l_lookup_enabled_flag VARCHAR2(100) DEFAULT NULL;

CURSOR csr_get_lookup_enabled(l_lookup_type VARCHAR2,l_lookup_code VARCHAR2) IS
select enabled_flag from hcm_lookups where lookup_type = l_lookup_type and lookup_code = l_lookup_code;
BEGIN

OPEN csr_get_lookup_enabled(p_lookup_type,p_lookup_code);
FETCH csr_get_lookup_enabled into l_lookup_enabled_flag;
CLOSE csr_get_lookup_enabled;

RETURN l_lookup_enabled_flag;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_lookup_enabled%ISOPEN) THEN
      CLOSE csr_get_lookup_enabled;
    END IF;

END get_decoded_lookup_enabled;


/* =====================================================================
  Get Person User Type
  ---------------------------------------------------------------------*/

FUNCTION get_user_person_type(p_assignment_id NUMBER,p_date_effective DATE)
RETURN VARCHAR2 IS

l_user_person_type VARCHAR2(100) DEFAULT NULL;

CURSOR csr_get_user_person_type(l_assignment_id NUMBER,l_date_effective DATE) IS

select
  pptype.user_person_type
from
  per_all_assignments_m asg,
  per_person_types_vl pptype
where
  asg.assignment_id = l_assignment_id
  AND asg.person_type_id = pptype.person_type_id
  AND TRUNC(l_date_effective) BETWEEN asg.effective_start_date AND asg.effective_end_date;

BEGIN

OPEN csr_get_user_person_type(p_assignment_id,p_date_effective);
FETCH csr_get_user_person_type into l_user_person_type;
CLOSE csr_get_user_person_type;

RETURN l_user_person_type;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_user_person_type%ISOPEN) THEN
      CLOSE csr_get_user_person_type;
    END IF;

END get_user_person_type;

/* =====================================================================
  Get Standard Working Hours
  ---------------------------------------------------------------------*/

FUNCTION get_standard_working_hours(p_assignment_id NUMBER,p_date_effective DATE)
RETURN NUMBER IS

l_standard_working_hours number default -1;

l_position_id number default -1;
l_organization_id number default -1;
l_legal_entity_id number default -1;
l_business_group_id number default -1;

CURSOR csr_get_all_ids(l_assignment_id NUMBER,l_date_effective DATE) IS

select
  position_id,organization_id,legal_entity_id,business_group_id
from
  per_all_assignments_m asg
where
  asg.assignment_id = l_assignment_id
  and trunc(l_date_effective) between asg.effective_start_date and asg.effective_end_date
  and asg.effective_latest_change = 'Y';

CURSOR csr_get_swh_using_position_id(p_position_id NUMBER,l_date_effective DATE) IS

select
  working_hours
from
  HR_ALL_POSITIONS_F
where
  position_id = p_position_id
  and trunc(l_date_effective) between effective_start_date and effective_end_date;

-- either organization or legal employer or business group id should be used
CURSOR csr_get_swh_using_other_ids(p_org_le_bg_id NUMBER,l_date_effective DATE) IS

select
  ORG_INFORMATION_NUMBER1
from
  hr_organization_information_f
where
  organization_id = p_org_le_bg_id
  and org_information_context = 'PER_WORK_DAY_INFO'
  and trunc(l_date_effective) between effective_start_date and effective_end_date;

BEGIN

OPEN csr_get_all_ids(p_assignment_id,p_date_effective);
FETCH csr_get_all_ids into l_position_id,l_organization_id,l_legal_entity_id,l_business_group_id;
CLOSE csr_get_all_ids;

if(l_position_id <> -1 and l_position_id is not null) then

  OPEN csr_get_swh_using_position_id(l_position_id,p_date_effective);
  FETCH csr_get_swh_using_position_id into l_standard_working_hours;
  CLOSE csr_get_swh_using_position_id;

  if (l_standard_working_hours <> -1 and l_standard_working_hours <> 0  and l_standard_working_hours is not null) then
    RETURN l_standard_working_hours;
  end if;

end if;

if(l_organization_id <> -1 and l_organization_id is not null) then

  OPEN csr_get_swh_using_other_ids(l_organization_id,p_date_effective);
  FETCH csr_get_swh_using_other_ids into l_standard_working_hours;
  CLOSE csr_get_swh_using_other_ids;

  if (l_standard_working_hours <> -1 and l_standard_working_hours <> 0  and l_standard_working_hours is not null) then
    RETURN l_standard_working_hours;
  end if;
end if;

if(l_legal_entity_id <> -1 and l_legal_entity_id is not null) then

  OPEN csr_get_swh_using_other_ids(l_legal_entity_id,p_date_effective);
  FETCH csr_get_swh_using_other_ids into l_standard_working_hours;
  CLOSE csr_get_swh_using_other_ids;

  if (l_standard_working_hours <> -1 and l_standard_working_hours <> 0  and l_standard_working_hours is not null) then
    RETURN l_standard_working_hours;
  end if;
end if;

if(l_business_group_id <> -1 and l_business_group_id is not null) then

  OPEN csr_get_swh_using_other_ids(l_business_group_id,p_date_effective);
  FETCH csr_get_swh_using_other_ids into l_standard_working_hours;
  CLOSE csr_get_swh_using_other_ids;

  if (l_standard_working_hours <> -1 and l_standard_working_hours <> 0  and l_standard_working_hours is not null) then
    RETURN l_standard_working_hours;
  end if;
end if;

RETURN l_standard_working_hours;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_all_ids%ISOPEN) THEN
      CLOSE csr_get_all_ids;
    END IF;

    IF(csr_get_swh_using_position_id%ISOPEN) THEN
      CLOSE csr_get_swh_using_position_id;
    END IF;

    IF(csr_get_swh_using_other_ids%ISOPEN) THEN
      CLOSE csr_get_swh_using_other_ids;
    END IF;

RETURN l_standard_working_hours;

END get_standard_working_hours;


/* =====================================================================
  Get Tree Version Name
  ---------------------------------------------------------------------*/

FUNCTION get_tree_version_name(p_tree_version_id VARCHAR2)
RETURN VARCHAR2 IS

l_tree_version_name VARCHAR2(100) DEFAULT NULL;

CURSOR csr_get_tree_version_name(l_tree_version_id VARCHAR2) IS
select tree_version_name from fnd_tree_version_vl where tree_version_id  = l_tree_version_id;

BEGIN

OPEN csr_get_tree_version_name(p_tree_version_id);
FETCH csr_get_tree_version_name into l_tree_version_name;
CLOSE csr_get_tree_version_name;

RETURN l_tree_version_name;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_tree_version_name%ISOPEN) THEN
      CLOSE csr_get_tree_version_name;
    END IF;

END get_tree_version_name;


/* =====================================================================
  Get Organization Names Hierarchy
  ---------------------------------------------------------------------*/

FUNCTION get_org_name_hierarchy(p_pk1_value VARCHAR2,p_tree_structure_code VARCHAR2,p_tree_code VARCHAR2,p_tree_version_id VARCHAR2,p_date_effective DATE,p_distance number)
RETURN VARCHAR2 IS

l_org_names_hierarchy VARCHAR2(1000) DEFAULT NULL;


CURSOR csr_get_org_name_hierarchy(l_pk1_value VARCHAR2,l_tree_structure_code VARCHAR2,l_tree_code VARCHAR2,l_tree_version_id VARCHAR2,l_date_effective DATE,l_distance number) IS
SELECT
    listagg (haou.name, '^') WITHIN GROUP (ORDER BY distance desc)
FROM
    per_org_tree_node_rf   pgtn_rf,
    hr_all_organization_units_f_vl haou
WHERE to_number(pgtn_rf.ancestor_pk1_value) = haou.organization_id
    and trunc(l_date_effective) between haou.effective_start_date and haou.effective_end_date
    and to_number(pk1_value) = to_number(l_pk1_value)
    and tree_structure_code = l_tree_structure_code
    and tree_code = l_tree_code
    and tree_version_id = l_tree_version_id
    and distance >= l_distance;

BEGIN


OPEN csr_get_org_name_hierarchy(p_pk1_value ,p_tree_structure_code ,p_tree_code ,p_tree_version_id ,p_date_effective,p_distance);
FETCH csr_get_org_name_hierarchy into l_org_names_hierarchy;
CLOSE csr_get_org_name_hierarchy;


RETURN l_org_names_hierarchy;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_org_name_hierarchy%ISOPEN) THEN
      CLOSE csr_get_org_name_hierarchy;
    END IF;

END get_org_name_hierarchy;



/* =====================================================================
  Get Territory Names Hierarchy
  ---------------------------------------------------------------------*/

FUNCTION get_territory_name_hierarchy(p_pk1_value VARCHAR2,p_tree_structure_code VARCHAR2,p_tree_code VARCHAR2,p_tree_version_id VARCHAR2,p_distance number)
RETURN VARCHAR2 IS

l_territory_names_hierarchy VARCHAR2(1000) DEFAULT NULL;

CURSOR csr_territory_name_hierarchy(l_pk1_value VARCHAR2,l_tree_structure_code VARCHAR2,l_tree_code VARCHAR2,l_tree_version_id VARCHAR2,l_distance number) IS
SELECT
  listagg (ft.territory_short_name, '^') WITHIN GROUP (ORDER BY distance desc)
FROM  per_geo_tree_node_rf   pgtn_rf,
      fnd_territories_vl     ft
WHERE pgtn_rf.ancestor_pk1_value = ft.territory_code
    and pk1_value = to_number(l_pk1_value)
    and tree_structure_code = l_tree_structure_code
    and tree_code = l_tree_code
    and tree_version_id = l_tree_version_id
    and distance >= l_distance;

BEGIN


OPEN csr_territory_name_hierarchy(p_pk1_value ,p_tree_structure_code ,p_tree_code ,p_tree_version_id,p_distance);
FETCH csr_territory_name_hierarchy into l_territory_names_hierarchy;
CLOSE csr_territory_name_hierarchy;


RETURN l_territory_names_hierarchy;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_territory_name_hierarchy%ISOPEN) THEN
      CLOSE csr_territory_name_hierarchy;
    END IF;

RETURN l_territory_names_hierarchy;
END get_territory_name_hierarchy;

/* =====================================================================
  Get Department Names Hierarchy
  ---------------------------------------------------------------------*/

FUNCTION get_dep_name_hierarchy(p_pk1_value VARCHAR2,p_tree_structure_code VARCHAR2,p_tree_code VARCHAR2,p_tree_version_id VARCHAR2,p_date_effective DATE,p_distance number)
RETURN VARCHAR2 IS

l_dep_names_hierarchy VARCHAR2(1000) DEFAULT NULL;


CURSOR csr_dep_org_name_hierarchy(l_pk1_value VARCHAR2,l_tree_structure_code VARCHAR2,l_tree_code VARCHAR2,l_tree_version_id VARCHAR2,l_date_effective DATE,l_distance number) IS
SELECT
    listagg (haou.name, '^') WITHIN GROUP (ORDER BY distance desc)
FROM
    per_dept_tree_node_rf    pgtn_rf,
    hr_all_organization_units_f_vl haou
WHERE pgtn_rf.ancestor_pk1_value = haou.organization_id
    and trunc(l_date_effective) between haou.effective_start_date and haou.effective_end_date
    and pk1_value = to_number(l_pk1_value)
    and tree_structure_code = l_tree_structure_code
    and tree_code = l_tree_code
    and tree_version_id = l_tree_version_id
    and distance >= l_distance;

BEGIN


OPEN csr_dep_org_name_hierarchy(p_pk1_value ,p_tree_structure_code ,p_tree_code ,p_tree_version_id ,p_date_effective,p_distance);
FETCH csr_dep_org_name_hierarchy into l_dep_names_hierarchy;
CLOSE csr_dep_org_name_hierarchy;


RETURN l_dep_names_hierarchy;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_dep_org_name_hierarchy%ISOPEN) THEN
      CLOSE csr_dep_org_name_hierarchy;
    END IF;

END get_dep_name_hierarchy;

/* =====================================================================
  Get Position Names Hierarchy
  ---------------------------------------------------------------------*/

FUNCTION get_pos_name_hierarchy(p_pk1_value VARCHAR2,p_tree_structure_code VARCHAR2,p_tree_code VARCHAR2,p_tree_version_id VARCHAR2,p_date_effective DATE,p_distance number)
RETURN VARCHAR2 IS

l_pos_names_hierarchy VARCHAR2(1000) DEFAULT NULL;


CURSOR csr_pos_name_hierarchy(l_pk1_value VARCHAR2,l_tree_structure_code VARCHAR2,l_tree_code VARCHAR2,l_tree_version_id VARCHAR2,l_date_effective DATE,l_distance number) IS
SELECT
    listagg (hapv.name, '^') WITHIN GROUP (ORDER BY distance desc) x
FROM  per_pos_tree_node_rf   pgtn_rf,
        HR_ALL_POSITIONS_F_VL hapv
WHERE pgtn_rf.ancestor_pk1_value = hapv.position_id
    and trunc(l_date_effective) between hapv.effective_start_date and hapv.effective_end_date
    and pk1_value = to_number(l_pk1_value)
    and tree_structure_code = l_tree_structure_code
    and tree_code = l_tree_code
    and tree_version_id = l_tree_version_id
    and distance >= l_distance;


BEGIN


OPEN csr_pos_name_hierarchy(p_pk1_value ,p_tree_structure_code ,p_tree_code ,p_tree_version_id ,p_date_effective,p_distance);
FETCH csr_pos_name_hierarchy into l_pos_names_hierarchy;
CLOSE csr_pos_name_hierarchy;


RETURN l_pos_names_hierarchy;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_pos_name_hierarchy%ISOPEN) THEN
      CLOSE csr_pos_name_hierarchy;
    END IF;

END get_pos_name_hierarchy;


/* =====================================================================
  Get Manager Hierarchy
  ---------------------------------------------------------------------*/

FUNCTION get_manager_hierarchy(p_person_id VARCHAR2,p_date_effective DATE,p_manager_level number)
RETURN VARCHAR2 IS

l_manager_hierarchy VARCHAR2(1000) DEFAULT NULL;


CURSOR csr_manager_hierarchy(l_person_id VARCHAR2,l_date_effective DATE,l_manager_level number) IS
select
  listagg (ppnf.full_name, '^') WITHIN GROUP (ORDER BY mng.manager_level desc)
from
  PER_MANAGER_HRCHY_DN mng,
  per_person_names_f_v ppnf
where
  mng.manager_id = ppnf.person_id
  and trunc(l_date_effective) between mng.effective_start_date and mng.effective_end_date
  and trunc(l_date_effective) between ppnf.effective_start_date and ppnf.effective_end_date
  and mng.person_id = l_person_id
  and mng.manager_level <= l_manager_level;

BEGIN


OPEN csr_manager_hierarchy(p_person_id,p_date_effective,p_manager_level);
FETCH csr_manager_hierarchy into l_manager_hierarchy;
CLOSE csr_manager_hierarchy;


RETURN l_manager_hierarchy;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_manager_hierarchy%ISOPEN) THEN
      CLOSE csr_manager_hierarchy;
    END IF;

END get_manager_hierarchy;

/* =====================================================================
  Get Person Email ID
  ---------------------------------------------------------------------*/

FUNCTION get_person_email_id(p_person_id VARCHAR2,p_date_effective DATE,p_email_type varchar2)
RETURN VARCHAR2 IS

l_email_id VARCHAR2(1000) DEFAULT NULL;


CURSOR csr_get_email_id(l_person_id VARCHAR2,l_date_effective DATE,l_email_type varchar2) IS
  select email_address from per_email_addresses pea
  where person_id = l_person_id
  and email_type = l_email_type
  and TRUNC(l_date_effective) BETWEEN pea.date_from AND NVL(pea.date_to, TRUNC(l_date_effective))
  and rownum = 1;

BEGIN


OPEN csr_get_email_id(p_person_id,p_date_effective,p_email_type);
FETCH csr_get_email_id into l_email_id;
CLOSE csr_get_email_id;


RETURN l_email_id;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_email_id%ISOPEN) THEN
      CLOSE csr_get_email_id;
    END IF;

END get_person_email_id;


/* =====================================================================
  Get Maximum Rehire Date
  ---------------------------------------------------------------------*/

FUNCTION get_max_rehire_date(p_assignment_id NUMBER,p_action_code VARCHAR2)
RETURN DATE IS

l_hire_date DATE DEFAULT NULL;

CURSOR csr_get_max_rehire_date(l_assignment_id NUMBER,l_action_code VARCHAR2) IS
select
  max(asg.effective_start_date)
from
  PER_ALL_ASSIGNMENTS_M asg,
  per_action_occurrences pao,
  per_actions_vl pav
where
  asg.assignment_id = l_assignment_id
  and asg.action_occurrence_id = pao.action_occurrence_id(+)
  and pao.action_id = pav.action_id(+)
  and pav.action_type_code = l_action_code;

BEGIN

OPEN csr_get_max_rehire_date(p_assignment_id,p_action_code);
FETCH csr_get_max_rehire_date into l_hire_date;
CLOSE csr_get_max_rehire_date;

RETURN l_hire_date;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_max_rehire_date%ISOPEN) THEN
      CLOSE csr_get_max_rehire_date;
    END IF;

END get_max_rehire_date;

/* =====================================================================
  Get Minimum Rehire Date
  ---------------------------------------------------------------------*/

FUNCTION get_min_rehire_date(p_assignment_id NUMBER,p_action_code VARCHAR2)
RETURN DATE IS

l_hire_date DATE DEFAULT NULL;

CURSOR csr_get_min_rehire_date(l_assignment_id NUMBER,l_action_code VARCHAR2) IS
select
  min(asg.effective_start_date)
from
  PER_ALL_ASSIGNMENTS_M asg,
  per_action_occurrences pao,
  per_actions_vl pav
where
  asg.assignment_id = l_assignment_id
  and asg.action_occurrence_id = pao.action_occurrence_id(+)
  and pao.action_id = pav.action_id(+)
  and pav.action_type_code = l_action_code;

BEGIN

OPEN csr_get_min_rehire_date(p_assignment_id,p_action_code);
FETCH csr_get_min_rehire_date into l_hire_date;
CLOSE csr_get_min_rehire_date;

RETURN l_hire_date;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_min_rehire_date%ISOPEN) THEN
      CLOSE csr_get_min_rehire_date;
    END IF;

END get_min_rehire_date;

/* =====================================================================
  Get Maximum Work Relation Ship Date
  ---------------------------------------------------------------------*/

FUNCTION get_max_workrelationship_date(p_person_id NUMBER)
RETURN DATE IS

l_date_start DATE DEFAULT NULL;

CURSOR csr_get_max_wrs_date(l_person_id NUMBER) IS
select
  max(date_start)
from
  per_periods_of_service
where
  person_id = l_person_id;

BEGIN

OPEN csr_get_max_wrs_date(p_person_id);
FETCH csr_get_max_wrs_date into l_date_start;
CLOSE csr_get_max_wrs_date;

RETURN l_date_start;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_max_wrs_date%ISOPEN) THEN
      CLOSE csr_get_max_wrs_date;
    END IF;

END get_max_workrelationship_date;

/* =====================================================================
  Get Minimum Work Relation Ship Date
  ---------------------------------------------------------------------*/

FUNCTION get_min_workrelationship_date(p_person_id NUMBER)
RETURN DATE IS

l_date_start DATE DEFAULT NULL;

CURSOR csr_get_min_wrs_date(l_person_id NUMBER) IS
select
  min(date_start)
from
  per_periods_of_service
where
  person_id = l_person_id;

BEGIN

OPEN csr_get_min_wrs_date(p_person_id);
FETCH csr_get_min_wrs_date into l_date_start;
CLOSE csr_get_min_wrs_date;

RETURN l_date_start;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_min_wrs_date%ISOPEN) THEN
      CLOSE csr_get_min_wrs_date;
    END IF;

END get_min_workrelationship_date;

/* =====================================================================
  Get Address Type Using Address Id and Effective Date
  ---------------------------------------------------------------------*/

FUNCTION get_address_type(p_address_id NUMBER,p_effective_date DATE)
RETURN VARCHAR2 IS

l_address_type VARCHAR2(40) DEFAULT NULL;

CURSOR csr_get_address_type(l_address_id NUMBER,l_effective_date DATE) IS
select
  address_type
from
  per_person_addr_usages_f
where
  address_id = l_address_id
  and trunc(l_effective_date) between effective_start_date and effective_end_date;

BEGIN
OPEN csr_get_address_type(p_address_id,p_effective_date);
FETCH csr_get_address_type into l_address_type;
CLOSE csr_get_address_type;

RETURN l_address_type;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_address_type%ISOPEN) THEN
      CLOSE csr_get_address_type;
    END IF;

RETURN l_address_type;

END get_address_type;

/* =====================================================================
  Get Work Schedule Exepction Using Assignment Id and Effective Date
  ---------------------------------------------------------------------*/

FUNCTION get_work_sch_exception_code(p_assignment_id NUMBER,p_effective_date DATE)
RETURN VARCHAR2 IS

l_work_sch_exception_code VARCHAR2(240) DEFAULT NULL;

CURSOR csr_get_worksch_exception_code(l_assignment_id NUMBER,l_effective_date DATE) IS

SELECT
  PSE.exception_code
FROM
  PER_SCHEDULE_ASSIGNMENTS PSA,
  PER_SCHEDULE_EXCEPTIONS PSE
WHERE
  PSE.SCHEDULE_ASSIGNMENT_ID = PSA.SCHEDULE_ASSIGNMENT_ID
  AND PSE.EXCEPTION_TYPE = 'EVENTCATEGORY'
  AND PSA.RESOURCE_TYPE = 'ASSIGN'
  and PSA.resource_id = l_assignment_id
  and trunc(l_effective_date) between start_date and end_date;

BEGIN
OPEN csr_get_worksch_exception_code(p_assignment_id,p_effective_date);
FETCH csr_get_worksch_exception_code into l_work_sch_exception_code;
CLOSE csr_get_worksch_exception_code;

RETURN l_work_sch_exception_code;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_worksch_exception_code%ISOPEN) THEN
      CLOSE csr_get_worksch_exception_code;
    END IF;

RETURN l_work_sch_exception_code;

END get_work_sch_exception_code;


/* =====================================================================
  Get Effective Sequence Using Assignment id
  ---------------------------------------------------------------------*/

FUNCTION get_next_assignment_sequence(p_assignment_id NUMBER,p_effective_date DATE)
RETURN NUMBER IS

l_next_assignment_sequence NUMBER DEFAULT 0;

CURSOR csr_get_next_asg_sequence(l_assignment_id NUMBER,l_effective_date DATE) IS
select
  effective_sequence
from
  per_all_assignments_m
where
  assignment_id = l_assignment_id
  and effective_start_date = l_effective_date
  and effective_latest_change = 'Y';

BEGIN

OPEN csr_get_next_asg_sequence(p_assignment_id,p_effective_date);
FETCH csr_get_next_asg_sequence into l_next_assignment_sequence;
CLOSE csr_get_next_asg_sequence;

RETURN l_next_assignment_sequence + 1;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_next_asg_sequence%ISOPEN) THEN
      CLOSE csr_get_next_asg_sequence;
    END IF;

RETURN l_next_assignment_sequence + 1;
END get_next_assignment_sequence ;


/* =====================================================================
  Name    : get_fnd_lookup_values_flex
  Purpose :
  ---------------------------------------------------------------------*/


FUNCTION get_fnd_lookup_values_flex(p_lookup_type VARCHAR2,p_lookup_code VARCHAR2, p_return_column_name VARCHAR2)
RETURN VARCHAR2 IS

TYPE dynamic_cursor  IS REF CURSOR;
v_cursor    dynamic_cursor;
v_stmt_str VARCHAR2(2000);
l_return_value VARCHAR2(200) DEFAULT null;

BEGIN
v_stmt_str := 'SELECT ' || p_return_column_name || ' FROM FND_LOOKUP_VALUES_B WHERE lookup_type = ' || '''' || p_lookup_type || '''' || ' and lookup_code = ' || '''' || p_lookup_code || '''' ;
OPEN v_cursor FOR v_stmt_str;
FETCH  v_cursor INTO l_return_value;
CLOSE v_cursor;

return l_return_value;

EXCEPTION
  WHEN OTHERS THEN
    IF(v_cursor%ISOPEN) THEN
      CLOSE v_cursor;
    END IF;

return l_return_value;

END get_fnd_lookup_values_flex;

/* =====================================================================
  Name    : get_ldg_name
  Purpose :
  ---------------------------------------------------------------------*/

FUNCTION get_ldg_name(p_assignment_id NUMBER,p_effective_date DATE)
RETURN VARCHAR2 IS

l_ldg_id NUMBER DEFAULT 0;
l_ldg_name VARCHAR2(200) DEFAULT null;

CURSOR csr_get_ldg_id(l_assignment_id NUMBER,l_effective_date DATE) IS
select
	pprd.legislative_data_group_id
from
  per_all_assignments_f    paaf,
  pay_payroll_assignments  ppa,
  pay_pay_relationships_dn pprd
  where  paaf.assignment_id          = l_assignment_id
  and paaf.assignment_id          = ppa.hr_assignment_id
  and ppa.payroll_relationship_id = pprd.payroll_relationship_id
  and trunc(l_effective_date) between paaf.effective_start_date  and paaf.effective_end_date
  and trunc(l_effective_date) between pprd.start_date and pprd.end_date
  and trunc(l_effective_date) between ppa.start_date  and ppa.end_date;

CURSOR csr_get_ldg_name(l_legislative_data_group_id NUMBER) IS
select
	ldg.name
from
	PER_LEGISLATIVE_DATA_GROUPS_VL ldg
where
	ldg.legislative_data_group_id = l_legislative_data_group_id;

BEGIN

OPEN csr_get_ldg_id(p_assignment_id,p_effective_date);
FETCH csr_get_ldg_id into l_ldg_id;
CLOSE csr_get_ldg_id;

OPEN csr_get_ldg_name(l_ldg_id);
FETCH csr_get_ldg_name into l_ldg_name;
CLOSE csr_get_ldg_name;

RETURN l_ldg_name;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_ldg_id%ISOPEN) THEN
      CLOSE csr_get_ldg_id;
    END IF;
    IF(csr_get_ldg_name%ISOPEN) THEN
      CLOSE csr_get_ldg_name;
    END IF;

RETURN l_ldg_name;

END get_ldg_name ;



/* =====================================================================
  Name    : get_latest_rehire_date
  Purpose :
  ---------------------------------------------------------------------*/

FUNCTION get_latest_rehire_date(p_person_id NUMBER,p_effective_date DATE)
RETURN DATE IS

l_latest_rehire_date DATE DEFAULT NULL;

-- OLD
--CURSOR csr_get_latest_rehire_date(l_person_id NUMBER,l_effective_date DATE) IS
--select
--  max(asg.effective_start_date)
--from
--  per_all_assignments_m asg
--where
--  asg.person_id = l_person_id
--  and asg.effective_start_date < l_effective_date
--  and asg.action_code in ('HIRE','REHIRE')
--  and asg.assignment_type = 'E';

--SRENUKUN
CURSOR csr_get_latest_rehire_date(l_person_id NUMBER,l_effective_date DATE) IS
select
  max(asg.effective_start_date)
from
  per_all_assignments_m asg,
  per_action_occurrences pao,
  per_actions_vl pav
where
  asg.person_id = l_person_id
  and asg.effective_start_date <= l_effective_date
  and asg.action_occurrence_id = pao.action_occurrence_id(+)
  and pao.action_id = pav.action_id(+)
  and pav.action_type_code in ('EMPL_ADD_EMP','EMPL_REHIRE')
  and asg.assignment_type = 'E';

BEGIN

OPEN csr_get_latest_rehire_date(p_person_id,p_effective_date);
FETCH csr_get_latest_rehire_date into l_latest_rehire_date;
CLOSE csr_get_latest_rehire_date;

return l_latest_rehire_date;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_latest_rehire_date%ISOPEN) THEN
      CLOSE csr_get_latest_rehire_date;
    END IF;

return l_latest_rehire_date;

END get_latest_rehire_date ;



/* =====================================================================
  Name    : get_latest_termination_date
  Purpose :
  ---------------------------------------------------------------------*/

FUNCTION get_latest_termination_date(p_person_id NUMBER,p_effective_date DATE)
RETURN DATE IS

l_latest_termination_date DATE DEFAULT NULL;

-- OLD
--CURSOR csr_get_latest_term_date(l_person_id NUMBER,l_effective_date DATE) IS
--select
--  max(asg.effective_start_date)
--from
--  per_all_assignments_m asg
--where
--  asg.person_id = l_person_id
--  and asg.effective_start_date < l_effective_date
--  and asg.action_code in ('RESIGNATION')
--  and asg.assignment_type = 'E';

--SRENUKUN
CURSOR csr_get_latest_term_date(l_person_id NUMBER,l_effective_date DATE) IS
select
  max(asg.effective_start_date)
from
  per_all_assignments_m asg,
  per_action_occurrences pao,
  per_actions_vl pav
where
  asg.person_id = l_person_id
  and asg.effective_start_date <= l_effective_date
  and asg.action_occurrence_id = pao.action_occurrence_id(+)
  and pao.action_id = pav.action_id(+)
  and pav.action_type_code = 'EMPL_TERMINATE'
  and asg.assignment_type = 'E';

BEGIN

OPEN csr_get_latest_term_date(p_person_id,p_effective_date);
FETCH csr_get_latest_term_date into l_latest_termination_date;
CLOSE csr_get_latest_term_date;

return l_latest_termination_date;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_latest_term_date%ISOPEN) THEN
      CLOSE csr_get_latest_term_date;
    END IF;

return l_latest_termination_date;

END get_latest_termination_date ;


/* =====================================================================
  Name    : get_future_rehire_date
  Purpose :
  ---------------------------------------------------------------------*/

FUNCTION get_future_rehire_date(p_person_id NUMBER,p_effective_date DATE)
RETURN DATE IS

l_latest_rehire_date DATE DEFAULT NULL;

-- OLD
--CURSOR csr_get_latest_rehire_date(l_person_id NUMBER,l_effective_date DATE) IS
--select
--  max(asg.effective_start_date)
--from
--  per_all_assignments_m asg
--where
--  asg.person_id = l_person_id
--  and asg.effective_start_date < l_effective_date
--  and asg.action_code in ('HIRE','REHIRE')
--  and asg.assignment_type = 'E';

--SRENUKUN
CURSOR csr_get_latest_rehire_date(l_person_id NUMBER,l_effective_date DATE) IS
select
  min(asg.effective_start_date)
from
  per_all_assignments_m asg,
  per_action_occurrences pao,
  per_actions_vl pav
where
  asg.person_id = l_person_id
  and asg.effective_start_date > l_effective_date
  and asg.action_occurrence_id = pao.action_occurrence_id(+)
  and pao.action_id = pav.action_id(+)
  and pav.action_type_code in ('EMPL_ADD_EMP','EMPL_REHIRE')
  and asg.assignment_type = 'E';

BEGIN

OPEN csr_get_latest_rehire_date(p_person_id,p_effective_date);
FETCH csr_get_latest_rehire_date into l_latest_rehire_date;
CLOSE csr_get_latest_rehire_date;

return l_latest_rehire_date;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_latest_rehire_date%ISOPEN) THEN
      CLOSE csr_get_latest_rehire_date;
    END IF;

return l_latest_rehire_date;

END get_future_rehire_date ;

/* =====================================================================
  Name    : get_person_image_id
  Purpose : To Get Image Id of a given person
  ---------------------------------------------------------------------*/

FUNCTION get_person_image_id(p_person_id NUMBER,p_primary_flag VARCHAR2)
RETURN NUMBER IS

l_person_image_id NUMBER DEFAULT -1;

CURSOR csr_get_person_image_id(l_person_id NUMBER,l_primary_flag VARCHAR2) IS
SELECT
pi.IMAGE_ID
FROM
per_images pi where pi.person_id = l_person_id
and pi.primary_flag = l_primary_flag
order by IMAGE_ID desc;

BEGIN

OPEN csr_get_person_image_id(p_person_id,p_primary_flag);
FETCH csr_get_person_image_id into l_person_image_id;
CLOSE csr_get_person_image_id;

RETURN l_person_image_id;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_person_image_id%ISOPEN) THEN
      CLOSE csr_get_person_image_id;
    END IF;

RETURN l_person_image_id;

END get_person_image_id;


/* =====================================================================
  Name    : is_person_an_employee
  Purpose : To know whether given person is also an employee or not
  ---------------------------------------------------------------------*/

FUNCTION is_person_an_employee (p_person_id IN NUMBER,p_effective_date DATE)
RETURN varchar2 is

l_count number default 0;
BEGIN

  select count(*) into l_count from per_all_assignments_m where
  person_id = p_person_id
  and TRUNC(p_effective_date) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
  and ASSIGNMENT_TYPE =  'E'
  and EFFECTIVE_LATEST_CHANGE = 'Y'
  and ASSIGNMENT_STATUS_TYPE = 'ACTIVE';

if(l_count <> 0) then
  return 'Y';
end if;

return 'N';

END is_person_an_employee;

/* =====================================================================
  Name    : get_national_identifier_number
  purpose : To get person national identifier number given nid id
  ---------------------------------------------------------------------*/

FUNCTION get_national_identifier_number (p_national_identifier_id IN NUMBER)
RETURN varchar2 is
l_national_identifier_number varchar2(240) default null;

CURSOR csr_get_nid_number(l_national_identifier_id NUMBER) IS
SELECT national_identifier_number FROM  per_national_identifiers WHERE national_identifier_id= l_national_identifier_id;

begin

OPEN csr_get_nid_number(p_national_identifier_id);
FETCH csr_get_nid_number into l_national_identifier_number;
CLOSE csr_get_nid_number;

RETURN l_national_identifier_number;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_nid_number%ISOPEN) THEN
      CLOSE csr_get_nid_number;
    END IF;

return l_national_identifier_number;

END get_national_identifier_number;


/* =====================================================================
  Name    : get_system_guid
  Purpose : to get the system guid
  ---------------------------------------------------------------------*/

FUNCTION get_system_guid
RETURN varchar2 is
l_system_guid varchar2(240) default null;
BEGIN
select SYS_GUID() into l_system_guid from dual;
return l_system_guid;
EXCEPTION
 WHEN OTHERS THEN
return l_system_guid;
END get_system_guid;


/* =====================================================================
  Name    : get_system_date_and_time
  Purpose : to get the system date and time
  ---------------------------------------------------------------------*/

FUNCTION get_system_date_and_time
RETURN varchar2 is
l_system_datetime varchar2(240) default null;
BEGIN

SELECT fnd_date.date_to_canonical(sysdate) into l_system_datetime from dual;

return l_system_datetime;
EXCEPTION
 WHEN OTHERS THEN
return l_system_datetime;
END get_system_date_and_time;

/* =====================================================================
  Name    : get_object_guid
  Purpose : to get the object guid
  ---------------------------------------------------------------------*/

FUNCTION get_object_guid(p_object_name VARCHAR2,p_object_id IN NUMBER)
RETURN varchar2 is
l_object_guid varchar2(240) default null;
k_object_name varchar2(240) default null;


CURSOR csr_get_object_guid(l_object_name VARCHAR2,l_object_id NUMBER) IS
SELECT SOURCE_ID FROM FUSION.HRC_LOADER_BATCH_KEY_MAP WHERE OBJECT_NAME =l_object_name AND TARGET_ID =l_object_id;

BEGIN

k_object_name :=p_object_name;

if(p_object_name='DEPARTMENT') then
k_object_name:='DEPARTMENT_VO';
elsif(p_object_name='FUN_BUSINESS_UNIT') then
k_object_name:='BUSINESS_UNIT_VO';
elsif(p_object_name='HCM_LEMP') then
k_object_name:='LEGAL_EMPLOYER_VO';
elsif(p_object_name='HCM_TRU') then
k_object_name:='ESTABLISHMENT_VO';
elsif(p_object_name='ENTERPRISE') then
k_object_name:='ENTERPRISE_VO';
END IF;

OPEN csr_get_object_guid(k_object_name ,p_object_id );
FETCH csr_get_object_guid into l_object_guid;
CLOSE csr_get_object_guid;

return l_object_guid;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_object_guid%ISOPEN) THEN
      CLOSE csr_get_object_guid;
    END IF;

return l_object_guid;

END get_object_guid;

/* =====================================================================
  Name    : get_SET_CODE
  Purpose : To get SET CODE if SET ID is given,
  Currently supported Object Types:

  Returns : Returns SET CODE
  ---------------------------------------------------------------------*/


FUNCTION get_set_code(p_set_id NUMBER)
RETURN VARCHAR2 IS

l_set_code VARCHAR2(100) DEFAULT NULL;

CURSOR csr_get_set_code(l_set_id NUMBER) IS
select SET_CODE from FND_SETID_SETS_VL setvl where setvl.set_id = l_set_id;

BEGIN

OPEN csr_get_set_code(p_set_id);
FETCH csr_get_set_code into l_set_code;
CLOSE csr_get_set_code;
RETURN l_set_code;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_set_code%ISOPEN) THEN
      CLOSE csr_get_set_code;
    END IF;

RETURN l_set_code;

END get_set_code;

/* =====================================================================
  Name    : get_SET_name
  Purpose : To get SET name if SET ID is given,
  Currently supported Object Types:

  Returns : Returns SET name
  ---------------------------------------------------------------------*/


FUNCTION get_set_name(p_set_id NUMBER)
RETURN VARCHAR2 IS

l_set_name VARCHAR2(100) DEFAULT NULL;

CURSOR csr_get_set_name(l_set_id NUMBER) IS
select SET_name from FND_SETID_SETS_VL setvl where setvl.set_id = l_set_id;

BEGIN

OPEN csr_get_set_name(p_set_id);
FETCH csr_get_set_name into l_set_name;
CLOSE csr_get_set_name;
RETURN l_set_name;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_set_name%ISOPEN) THEN
      CLOSE csr_get_set_name;
    END IF;

RETURN l_set_name;

END get_set_name;

/* =====================================================================
  Name    : get_per_del_method_values
  Purpose : to get the delivery methods
  ---------------------------------------------------------------------*/


FUNCTION get_per_del_method_values (p_del_fk_id IN NUMBER,p_del_method IN VARCHAR, p_column_name IN VARCHAR)
RETURN varchar2 IS

l_out_value varchar2(240) default null;

BEGIN

IF p_del_fk_id IS NULL THEN
  RETURN NULL;
END IF;

IF (p_del_method = 'PHONE' AND p_column_name <> 'email_address') THEN
  l_out_value := get_by_primary_key('per_phones','PHONE_ID', p_del_fk_id,p_column_name);
ELSIF (p_del_method = 'EML' AND p_column_name = 'email_address') THEN
  l_out_value := get_by_primary_key('per_email_addresses','email_address_id', p_del_fk_id,p_column_name);
END IF;

RETURN l_out_value;

END get_per_del_method_values;



/* =====================================================================
  Name    : get_location_email_address
  Purpose : To get location email for a hr location id
  Currently supported Object Types:
  Returns : Returns Locatoin Email Address
---------------------------------------------------------------------*/
FUNCTION get_location_email(p_location_id NUMBER, p_effective_date DATE)
RETURN VARCHAR2 IS

l_location_email VARCHAR2(240) DEFAULT NULL;

CURSOR csr_get_location_email(l_location_id NUMBER, l_effective_date DATE) IS
  select email_address from
  hr_locations_all_f_vl hrloc
  where hrloc.location_id = l_location_id
  and trunc(l_effective_date) between hrloc.effective_start_date and hrloc.effective_end_date;

BEGIN
OPEN csr_get_location_email(p_location_id,p_effective_date);
FETCH csr_get_location_email into l_location_email;
CLOSE csr_get_location_email;
RETURN l_location_email;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_location_email%ISOPEN) THEN
      CLOSE csr_get_location_email;
    END IF;

RETURN l_location_email;
END get_location_email;



/* =====================================================================
  Name    : get_location_BU
  Purpose : To get location BU associated with location setid
  Returns : comma separated BU Name list attached to location setid
---------------------------------------------------------------------*/
FUNCTION get_location_bu(p_set_id NUMBER, p_effective_date DATE)
RETURN VARCHAR2 IS

l_location_bu VARCHAR2(1000) DEFAULT NULL;
t_location_bu VARCHAR2(250) DEFAULT NULL;
l_count  NUMBER;

CURSOR csr_get_location_bu(l_set_id NUMBER, l_effective_date DATE) IS
select org.name from FND_SETID_ASSIGNMENTS fset, hr_organization_v org where fset.determinant_type = 'BU'  and fset.determinant_value = org.organization_id
and org.classification_code = 'FUN_BUSINESS_UNIT'
and reference_group_name = 'PER_LOCATIONS'
and fset.set_id = l_set_id and trunc(l_effective_date) between org.effective_start_date and org.effective_end_date
order by org.name;

BEGIN
l_count := 1;

OPEN csr_get_location_bu(p_set_id,p_effective_date);

LOOP
FETCH csr_get_location_bu into t_location_bu;
EXIT WHEN csr_get_location_bu%NOTFOUND;

if l_count = 1 then
l_location_bu := t_location_bu;
else
l_location_bu := l_location_bu || ',' || t_location_bu;
end if;

l_count := l_count+1;

END LOOP;

CLOSE csr_get_location_bu;

RETURN l_location_bu;

EXCEPTION
  WHEN OTHERS THEN
    IF(csr_get_location_bu%ISOPEN) THEN
      CLOSE csr_get_location_bu;
    END IF;

RETURN l_location_bu;
END get_location_bu;

END per_extract_utility;

Some Sample usecases of these functions:

NVL ( hr_general.decode_lookup ('EMP_CAT', asg.EMPLOYMENT_CATEGORY), 'XX' ) EMPL_CAT
NVL ( hr_general.decode_lookup ('EMPLOYEE_CATG', paam.EMPLOYEE_CATEGORY), ' ' ) WORKER_CATEGORY
hr_general.decode_lookup('FREQUENCY', paaf.frequency) frequency_meaning
per_extract_utility.get_by_primary_key_de('hr_all_organization_units_f_vl', 'organization_id', par.organization_id, 'name', par.START_DATE) Department_Name
per_extract_utility.get_standard_working_frequency(PAAM.ASSIGNMENT_ID,PAAM.EFFECTIVE_START_DATE)
per_extract_utility.get_by_primary_key_de('hr_all_organization_units_f_vl', 'organization_id', paam.legal_entity_id, 'name', paam.effective_start_date) pay_company
NVL(FF_USER_TABLES_PKG.GET_TABLE_VALUE_ENT (1, SYSDATE,'HHSC_HR_R_42_POSITION_HIERARCHY','GRADE_LADDER',PGLFT.NAME),'N')
PER_EXTRACT_UTILITY.GET_DECODED_LOOKUP('PAY_PROC_PERIOD_TYPE', PDCDF1.DIR_INFORMATION_CHAR4)

Hope these helps when you have requirement to have these fields on the BI output.

Tip: Using these functions from the packages will save multiple table joins in the main query and it is easy to use these functions.

Join our fastest growing technology social groups to interact with experts in the Fusion space and get your queries resolved – Telegram group, Facebook Page and LinkedIn Group

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

Leave a Reply