[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT ENT.org_structure_version_id "OSV_ID"
FROM per_calendar_entries ENT
WHERE ENT.org_structure_version_id IS NOT NULL
AND EXISTS (SELECT 'x'
FROM per_cal_entry_values ENV
WHERE ENV.calendar_entry_id = ENT.calendar_entry_id
);
SELECT DISTINCT organization_id_parent
FROM per_org_structure_elements
WHERE org_structure_version_id = cp_osv_id
AND organization_id_parent NOT IN
(SELECT organization_id_child
FROM per_org_structure_elements
WHERE org_structure_version_id = cp_osv_id
);
SELECT LEVEL
,org_structure_version_id
,org_structure_element_id
,organization_id_parent
,organization_id_child
FROM per_org_structure_elements
WHERE org_structure_version_id = cp_osv_id
START WITH organization_id_parent = cp_organization_id_parent
CONNECT BY PRIOR organization_id_child = organization_id_parent;
SELECT ENT.calendar_entry_id
,ENT.org_structure_version_id
,ENV.org_structure_element_id
,ENV.organization_id
,ENV.usage_flag
,DECODE(ENV.usage_flag,
'N','COV',
'Y','EXC',
'O','OVR') "USAGE"
,ENV.cal_entry_value_id
,ENV.parent_entry_value_id
,'' "ENTRY_FLAG"
FROM per_calendar_entries ENT
,per_cal_entry_values ENV
WHERE ENT.org_structure_version_id = cp_osv_id
AND ENT.calendar_entry_id = ENV.calendar_entry_id
AND ENV.org_structure_element_id IS NOT NULL
AND ENV.organization_id IS NOT NULL
ORDER BY ENT.calendar_entry_id
-- ,DECODE(ENV.usage_flag,
-- 'N',1,
-- 'O',2,
-- 'Y',3)
;
SELECT l_level "LEVEL"
,org_structure_version_id
,org_structure_element_id
,organization_id_parent
,organization_id_child
FROM per_org_structure_elements
WHERE org_structure_version_id = cp_osv_id
AND organization_id_parent = cp_par_org_id;
SELECT cal_entry_value_id
FROM per_cal_entry_values
WHERE usage_flag = 'O'
AND calendar_entry_id = cp_calendar_entry_id
AND organization_id = cp_organization_id;
INSERT INTO per_cal_entry_org_list
(calendar_entry_id
,organization_id
,ovr_cal_entry_value_id
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
)
VALUES
(p_calendar_entry_id
,p_coverage_list(idx)
,l_ovr_id
,TRUNC(SYSDATE)
,0
,0
,0
,TRUNC(SYSDATE)
);
l_coverage_list.DELETE;
l_exclusion_list.DELETE;
l_org_hier_table.DELETE;
l_cal_event_table.DELETE;
SELECT DISTINCT ENT.hierarchy_id "GHV_ID"
FROM per_calendar_entries ENT
WHERE ENT.hierarchy_id IS NOT NULL
AND EXISTS (SELECT 'x'
FROM per_cal_entry_values ENV
WHERE ENV.calendar_entry_id = ENT.calendar_entry_id
);
SELECT hierarchy_version_id
FROM per_gen_hierarchy_versions
WHERE hierarchy_id = cp_ghv_id
AND version_number = (SELECT MAX(version_number)
FROM per_gen_hierarchy_versions
WHERE hierarchy_id = cp_ghv_id);
SELECT hierarchy_node_id
FROM per_gen_hierarchy_nodes
WHERE hierarchy_version_id = cp_ghv_id
AND parent_hierarchy_node_id IS NULL;
SELECT LEVEL
,hierarchy_version_id
,hierarchy_node_id
,entity_id
FROM per_gen_hierarchy_nodes
WHERE hierarchy_version_id = cp_ghv_id
START WITH hierarchy_node_id = cp_top_node_id
CONNECT BY PRIOR hierarchy_node_id = parent_hierarchy_node_id;
SELECT ENT.calendar_entry_id
,ENT.hierarchy_id
,ENV.hierarchy_node_id
,ENV.usage_flag
,DECODE(ENV.usage_flag,
'N','COV',
'Y','EXC',
'O','OVR') "USAGE"
,ENV.cal_entry_value_id
,ENV.parent_entry_value_id
,'' "ENTRY_FLAG"
FROM per_calendar_entries ENT
,per_cal_entry_values ENV
WHERE ENT.hierarchy_id = cp_ghv_id
AND ENT.calendar_entry_id = ENV.calendar_entry_id
AND ENV.hierarchy_node_id IS NOT NULL
ORDER BY ENT.calendar_entry_id
,DECODE(ENV.usage_flag,
'N',1,
'O',2,
'Y',3);
SELECT cal_entry_value_id
FROM per_cal_entry_values
WHERE usage_flag = 'O'
AND calendar_entry_id = cp_calendar_entry_id
AND hierarchy_node_id = cp_hierarchy_node_id;
INSERT INTO per_cal_entry_geo_list
(calendar_entry_id
,hierarchy_node_id
,ovr_cal_entry_value_id
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date
)
VALUES
(p_calendar_entry_id
,p_coverage_list(idx)
,l_ovr_id
,TRUNC(SYSDATE)
,0
,0
,0
,TRUNC(SYSDATE)
);
l_coverage_list.DELETE;
l_exclusion_list.DELETE;
DELETE FROM per_cal_entry_org_list;
DELETE FROM per_cal_entry_geo_list;
SELECT assignment_id,
organization_id, -- ORG Node Id
location_id, -- for GEO mapping
business_group_id -- for GEO mapping
FROM per_all_assignments_f
WHERE person_id = cp_person_id
AND (
(cp_assignment_id IS NOT NULL AND assignment_id = cp_assignment_id)
OR
(cp_assignment_id IS NULL AND primary_flag = 'Y')
)
AND effective_start_date <= NVL(cp_end_date, SYSDATE)
AND effective_end_date >= NVL(cp_start_date, SYSDATE);
SELECT aei_information1
FROM per_assignment_extra_info
WHERE assignment_id = cp_assignment_id
AND information_type = 'PER_GEO_HIER_NODE_MAP'
AND aei_information_category = 'PER_GEO_HIER_NODE_MAP';
SELECT lei_information1
FROM hr_location_extra_info
WHERE location_id = cp_location_id
AND information_type = 'PER_GEO_HIER_NODE_MAP'
AND lei_information_category = 'PER_GEO_HIER_NODE_MAP';
SELECT org_information9
FROM hr_organization_information
WHERE organization_id = cp_business_group_id
AND org_information_context = 'Business Group Information'
AND attribute_category = 'Business Group Information';
SELECT GHN.hierarchy_node_id
FROM per_gen_hierarchy GH,
per_gen_hierarchy_versions GHV,
per_gen_hierarchy_nodes GHN
WHERE GH.type = 'PER_CAL_GEO'
AND GH.hierarchy_id = GHV.hierarchy_id
AND GHV.version_number = 1
AND GHV.hierarchy_version_id = GHN.hierarchy_version_id
AND GHN.entity_id = cp_geo_node;
SELECT ENT.calendar_entry_id,
ENT.business_group_id,
ENT.name,
ENT.type,
ENT.start_date,
ENT.end_date,
ENT.start_hour,
ENT.end_hour,
ENT.start_min,
ENT.end_min,
ORGENT.ovr_cal_entry_value_id
FROM per_cal_entry_org_list ORGENT,
per_calendar_entries ENT
WHERE ORGENT.organization_id = cp_organization_id
AND ORGENT.calendar_entry_id = ENT.calendar_entry_id
AND ENT.type = NVL(cp_event_type, ENT.type)
AND ENT.start_date <= NVL(cp_end_date, ENT.start_date)
AND ENT.end_date >= NVL(cp_start_date, ENT.end_date);
SELECT ENT.calendar_entry_id,
ENT.business_group_id,
ENT.name,
ENT.type,
ENT.start_date,
ENT.end_date,
ENT.start_hour,
ENT.end_hour,
ENT.start_min,
ENT.end_min,
GEOENT.ovr_cal_entry_value_id
FROM per_cal_entry_geo_list GEOENT,
per_calendar_entries ENT
WHERE GEOENT.hierarchy_node_id = cp_geo_node_id
AND GEOENT.calendar_entry_id = ENT.calendar_entry_id
AND ENT.type = NVL(cp_event_type, ENT.type)
AND ENT.start_date <= NVL(cp_end_date, ENT.start_date)
AND ENT.end_date >= NVL(cp_start_date, ENT.end_date);
SELECT override_name
,override_type
FROM per_cal_entry_values
WHERE cal_entry_value_id = cp_ovr_id;
SELECT person_id
FROM per_all_assignments_f
WHERE assignment_id = cp_assignment_id;
SELECT person_identifier
FROM hz_parties
WHERE party_id = cp_party_id
AND created_by_module = 'HR API'
AND orig_system_reference = 'PER:'||person_identifier;
SELECT calendar_entry_id,
business_group_id,
name,
type,
start_date,
end_date,
start_hour,
end_hour,
start_min,
end_min
FROM per_calendar_entries
WHERE type = NVL(cp_event_type, type)
AND start_date <= NVL(cp_end_date, start_date)
AND end_date >= NVL(cp_start_date, end_date)
AND (business_group_id IS NULL
OR
(business_group_id IS NOT NULL AND
business_group_id = NVL(cp_bg_id, business_group_id)
)
);
DELETE FROM per_cal_map_cache;
INSERT INTO per_cal_map_cache
(person_id
,assignment_id
,event_name
,event_type
,start_date
,end_date
,start_hour
,end_hour
,start_minute
,end_minute
)
VALUES
(p_person_id
,p_assignment_id
,l_cal_event_obj.event_name
,l_cal_event_obj.event_type
,l_cal_event_obj.start_date
,l_cal_event_obj.end_date
,l_cal_event_obj.start_hour
,l_cal_event_obj.end_hour
,l_cal_event_obj.start_minute
,l_cal_event_obj.end_minute
);