[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
P_PROGRAM_UPDATE_DATE DATE
P_EFFECTIVE_DATE DATE
Notes :
************************************************************************/
PROCEDURE validate_nodes( P_BUSINESS_GROUP_ID in NUMBER
,P_ENTITY_ID in VARCHAR2
,P_HIERARCHY_VERSION_ID in NUMBER
,P_NODE_TYPE in VARCHAR2
,P_SEQ in NUMBER
,P_PARENT_HIERARCHY_NODE_ID in NUMBER
,P_REQUEST_ID in NUMBER
,P_PROGRAM_APPLICATION_ID in NUMBER
,P_PROGRAM_ID in NUMBER
,P_PROGRAM_UPDATE_DATE in DATE
,P_EFFECTIVE_DATE in DATE )
IS
CURSOR c_active_hier( cp_bus_grp_id number
,cp_hier_ver_id number
,cp_eff_date date ) IS
select 1
from per_gen_hierarchy_versions
where business_group_id = cp_bus_grp_id
and hierarchy_version_id = cp_hier_ver_id
and cp_eff_date between date_from and nvl(date_to,cp_eff_date)
and status = 'A';
select 1
from per_gen_hierarchy_nodes pghn
,per_gen_hierarchy_versions pghv
,per_gen_hierarchy pgh
where pghn.business_group_id = cp_bus_grp_id
and pghn.node_type = cp_node_type
and pghn.entity_id = cp_entity_id
and pghv.business_group_id = cp_bus_grp_id
and pghv.hierarchy_version_id = pghn.hierarchy_version_id
and cp_eff_date between pghv.date_from and nvl(pghv.date_to,cp_eff_date)
and pghv.status = 'A'
and pgh.business_group_id = cp_bus_grp_id
and pgh.hierarchy_id = pghv.hierarchy_id
and pgh.type = 'MEXICO HRMS';
select 1
from per_gen_hierarchy_nodes
where business_group_id = cp_bus_grp_id
and hierarchy_version_id = cp_hier_ver_id
and node_type = cp_node_type
and nvl(parent_hierarchy_node_id, -999) = nvl(cp_par_hier_node_id, -999)
and entity_id = cp_entity_id;
P_PROGRAM_UPDATE_DATE DATE
P_EFFECTIVE_DATE DATE
Notes :
************************************************************************/
PROCEDURE create_default_location( P_HIERARCHY_NODE_ID in NUMBER
,P_BUSINESS_GROUP_ID in NUMBER
,P_ENTITY_ID in VARCHAR2
,P_HIERARCHY_VERSION_ID in NUMBER
,P_NODE_TYPE in VARCHAR2
,P_SEQ in NUMBER
,P_PARENT_HIERARCHY_NODE_ID in NUMBER
,P_REQUEST_ID in NUMBER
,P_PROGRAM_APPLICATION_ID in NUMBER
,P_PROGRAM_ID in NUMBER
,P_PROGRAM_UPDATE_DATE in DATE
,P_EFFECTIVE_DATE in DATE )
IS
CURSOR c_get_loc_id( cp_bus_grp_id number
,cp_org_id number) IS
select location_id
from hr_organization_units
where business_group_id = cp_bus_grp_id
and organization_id = cp_org_id;
,p_program_update_date => p_program_update_date
,p_object_version_number => ln_ovn
,p_effective_date => p_effective_date );
Name : delete_nodes
Purpose : This procedure checks following stuff before deleting any
record from the hierarchy.
- DO NOT DELETE RECORD WHEN CHILD EXISTS
- DO NOT DELETE LOCATION WHEN IT IS ASSOICATED TO AN
ASSIGNMENT FOR ANY TIME PERIOD.
Arguments : IN
P_HIERARCHY_NODE_ID NUMBER
P_OBJECT_VERSION_NUMBER NUMBER
Notes :
************************************************************************/
-- PROCEDURE delete_nodes( P_HIERARCHY_NODE_ID in NUMBER
-- ,P_OBJECT_VERSION_NUMBER in NUMBER)
-- IS
--
-- CURSOR c_child_node_exists( cp_hier_node_id number
-- ,cp_ovn number) IS
-- select 1
-- from per_gen_hierarchy_nodes
-- where parent_hierarchy_node_id = cp_hier_node_id
-- and object_version_number = cp_ovn;
Name : update_nodes
Purpose : This procedure restrict to update any node value when
hierarchy is 'Active'.
Arguments : IN
P_HIERARCHY_NODE_ID NUMBER
P_ENTITY_ID VARCHAR2
P_NODE_TYPE VARCHAR2
P_SEQ NUMBER
P_PARENT_HIERARCHY_NODE_ID NUMBER
P_REQUEST_ID NUMBER
P_PROGRAM_APPLICATION_ID NUMBER
P_PROGRAM_ID NUMBER
P_PROGRAM_UPDATE_DATE DATE
P_OBJECT_VERSION_NUMBER NUMBER
P_EFFECTIVE_DATE DATE
Notes :
************************************************************************/
PROCEDURE update_nodes( P_HIERARCHY_NODE_ID in NUMBER
,P_ENTITY_ID in VARCHAR2
,P_NODE_TYPE in VARCHAR2
,P_SEQ in NUMBER
,P_PARENT_HIERARCHY_NODE_ID in NUMBER
,P_REQUEST_ID in NUMBER
,P_PROGRAM_APPLICATION_ID in NUMBER
,P_PROGRAM_ID in NUMBER
,P_PROGRAM_UPDATE_DATE in DATE
,P_OBJECT_VERSION_NUMBER in NUMBER
,P_EFFECTIVE_DATE in DATE )
IS
CURSOR c_get_node_val( cp_hier_node_id number ) IS
select business_group_id, hierarchy_version_id
from per_gen_hierarchy_nodes
where hierarchy_node_id = cp_hier_node_id;
select 1
from per_gen_hierarchy_versions
where business_group_id = cp_bus_grp_id
and hierarchy_version_id = cp_hier_ver_id
and cp_eff_date between date_from and nvl(date_to,cp_eff_date)
and status = 'A';
hr_utility.trace('Entering: PER_MX_GEN_HIER_VALID.UPDATE_NODES');
hr_utility.trace('Update any node is not allowed.');
hr_utility.trace('Leaving: PER_MX_GEN_HIER_VALID.UPDATE_NODES');
END update_nodes;
Name : update_hier_versions
Purpose : This procedure checks MX LEGAL EMPLOYER and MX GRE nodes
whether that exists in any other active hierachy
when hierachy is changed from 'Inactive' status to
'Active' status.
Arguments : IN
P_HIERARCHY_VERSION_ID NUMBER
P_VERSION_NUMBER NUMBER
P_DATE_FROM DATE
P_DATE_TO DATE
P_STATUS VARCHAR2
P_VALIDATE_FLAG VARCHAR2
P_REQUEST_ID NUMBER
P_PROGRAM_APPLICATION_ID NUMBER
P_PROGRAM_ID NUMBER
P_PROGRAM_UPDATE_DATE DATE
P_OBJECT_VERSION_NUMBER NUMBER
P_EFFECTIVE_DATE DATE
Notes :
************************************************************************/
PROCEDURE update_hier_versions( P_HIERARCHY_VERSION_ID in NUMBER
,P_VERSION_NUMBER in NUMBER
,P_DATE_FROM in DATE
,P_DATE_TO in DATE
,P_STATUS in VARCHAR2
,P_VALIDATE_FLAG in VARCHAR2
,P_REQUEST_ID in NUMBER
,P_PROGRAM_APPLICATION_ID in NUMBER
,P_PROGRAM_ID in NUMBER
,P_PROGRAM_UPDATE_DATE in DATE
,P_OBJECT_VERSION_NUMBER in NUMBER
,P_EFFECTIVE_DATE in DATE )
IS
CURSOR c_hier_status( cp_hier_ver_id number
,cp_version_no number ) IS
select business_group_id, status
from per_gen_hierarchy_versions
where hierarchy_version_id = cp_hier_ver_id
and version_number = cp_version_no;
select node_type, entity_id
from per_gen_hierarchy_nodes
where business_group_id = cp_bus_grp_id
and hierarchy_version_id = cp_hier_ver_id
and node_type in ( 'MX LEGAL EMPLOYER', 'MX GRE' );
select 1
from per_gen_hierarchy_nodes pghn
,per_gen_hierarchy_versions pghv
,per_gen_hierarchy pgh
where pghn.business_group_id = cp_bus_grp_id
and pghn.node_type = cp_node_type
and pghn.entity_id = cp_entity_id
and pghv.business_group_id = cp_bus_grp_id
and pghv.hierarchy_version_id = pghn.hierarchy_version_id
and cp_eff_date between pghv.date_from and nvl(pghv.date_to,cp_eff_date)
and pghv.status = 'A'
and pgh.business_group_id = cp_bus_grp_id
and pgh.hierarchy_id = pghv.hierarchy_id
and pgh.type = 'MEXICO HRMS';
hr_utility.trace('Entering: PER_MX_GEN_HIER_VALID.UPDATE_HIER_VERSIONS');
hr_utility.trace('Leaving: PER_MX_GEN_HIER_VALID.UPDATE_HIER_VERSIONS');
END update_hier_versions;