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.
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.
Table of Contents
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.
