[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUBSTR(hou.name,1,20) org_name,
SUBSTR(hoi1.org_information2,1,27) company_number_1,
decode(hoi1.org_information3,'Y',1,2) c2_affiliated_14,
decode(hoi3.org_information5,'Y',1,2) gov_contract_15,
SUBSTR(hoi3.org_information4,1,20) duns_16,
decode(hoi3.org_information6,'Y',1,2) apprentices_emp_19,
SUBSTR(hoi3.org_information1,1,14) sic_20,
SUBSTR(hoi3.org_information2,1,16) naics_21,
SUBSTR(hoi2.org_information2,1,50) title_cert_off_22,
SUBSTR(hoi2.org_information1,1,50) name_cert_off_23,
SUBSTR(hoi2.org_information10,1,20) tel_num_24,
SUBSTR(hoi2.org_information14,1,10) fax_num_25,
SUBSTR(hoi2.org_information15,1,40) email_26,
hoi1.organization_id par_ent_org_id
FROM per_gen_hierarchy_nodes pgn,
hr_all_organization_units hou,
hr_organization_information hoi1,
hr_organization_information hoi2,
hr_organization_information hoi3
WHERE pgn.hierarchy_version_id = p_hierarchy_version_id
AND pgn.node_type = 'PAR'
AND hou.organization_id = p_business_group_id
AND pgn.business_group_id = p_business_group_id
AND hou.organization_id = pgn.business_group_id --BUG3646445
AND hoi3.org_information_context = 'VETS_EEO_Dup'
AND hoi3.organization_id = pgn.entity_id
AND hoi2.org_information_context = 'EEO_REPORT'
AND hoi2.organization_id = hou.organization_id
AND hoi1.org_information_context = 'EEO_Spec'
AND hoi1.organization_id = pgn.entity_id;
SELECT count('num_emps')
FROM per_all_assignments_f paf
WHERE paf.business_group_id = p_business_group_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type = 'E'
AND p_start_date > paf.effective_start_date
AND p_end_date < paf.effective_end_date
AND paf.location_id IN
(SELECT entity_id
FROM per_gen_hierarchy_nodes
WHERE hierarchy_version_id = p_hierarchy_version_id
);
SELECT
hlei1.lei_information2 unit_number_3,
UPPER(hlei1.lei_information1) unit_name_4,
UPPER(eloc.address_line_1||
' '||
eloc.address_line_2) unit_address_req_5,
UPPER(eloc.address_line_3) unit_address_6,
UPPER(eloc.town_or_city) city_7,
UPPER(eloc.region_2) state_8,
SUBSTR(eloc.postal_code,1,5) zip_code_9,
SUBSTR(eloc.postal_code,7,4) zip_code_last_4_10,
DECODE(hlei1.lei_information9,'Y',1,2) reported_last_year_11,
hlei2.lei_information6 ein_12,
DECODE(hlei1.lei_information4,'Y',1,'N',2) gov_contract_15,
hlei2.lei_information2 duns_16,
UPPER(eloc.region_1) county_17,
DECODE(hlei1.lei_information3,'Y',1,'N',2) apprentices_emp_19,
hlei2.lei_information3 sic_20,
hlei2.lei_information4 naics_21,
pghn.hierarchy_node_id,
hlei2.lei_information10 hq
FROM per_gen_hierarchy_nodes pghn,
hr_location_extra_info hlei1,
hr_location_extra_info hlei2,
hr_locations_all eloc
WHERE -- pghn.hierarchy_version_id = 2683 -- sd1
pghn.hierarchy_version_id = p_hierarchy_version_id -- 2803 sd10plus
-- pghn.hierarchy_version_id = 2823 -- Vik SD Albuquereque
AND pghn.node_type = 'EST'
AND eloc.location_id = pghn.entity_id
AND hlei1.location_id = pghn.entity_id
AND hlei1.location_id = hlei2.location_id
AND hlei1.information_type = 'EEO-1 Specific Information'
AND hlei1.lei_information_category = 'EEO-1 Specific Information'
AND hlei2.information_type = 'Establishment Information'
AND hlei2.lei_information_category = 'Establishment Information';
/* SELECT count('num_emps_at_location')
FROM per_all_assignments_f paf
WHERE paf.business_group_id = p_business_group_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type = 'E'
AND p_start_date >= paf.effective_start_date
AND p_end_date <= paf.effective_end_date
AND TO_CHAR(paf.location_id) IN
(SELECT entity_id
FROM per_gen_hierarchy_nodes pgn
WHERE
pgn.hierarchy_version_id = p_hierarchy_version_id
AND (
pgn.hierarchy_node_id = l_hierarchy_node_id
OR pgn.parent_hierarchy_node_id = l_hierarchy_node_id)
AND pgn.node_type IN ('EST','LOC')
); */
SELECT count(peo.person_id)
FROM per_all_assignments_f ass,
per_all_people_f peo,
per_jobs_vl job
WHERE peo.person_id = ass.person_id
AND peo.per_information1 is not NULL
AND job.job_information_category = 'US'
AND p_start_date <= nvl(job.date_to,p_end_date )
AND p_end_date >= job.date_from
AND job.job_information1 is not NULL
AND ass.job_id = job.job_id
AND peo.effective_start_date = (select max(peo1.effective_start_date)
from per_people_f peo1
where p_start_date <= peo1.effective_end_date
and p_end_date >= peo1.effective_start_date
and peo.person_id = peo1.person_id
and peo1.current_employee_flag = 'Y'
)
AND ass.effective_start_date = (select max(ass1.effective_start_date)
from per_all_assignments_f ass1
where p_start_date <= ass1.effective_end_date
and p_end_date >= ass1.effective_start_date
and ass.person_id = ass1.person_id
and ass1.assignment_type = 'E'
and ass1.primary_flag = 'Y'
)
AND ass.assignment_type = 'E'
AND ass.primary_flag = 'Y'
AND ass.business_group_id = p_business_group_id
AND peo.business_group_id = p_business_group_id
AND job.business_group_id = p_business_group_id
AND EXISTS (
SELECT 'X'
FROM HR_ORGANIZATION_INFORMATION HOI1,
HR_ORGANIZATION_INFORMATION HOI2
WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND hoi1.org_information_context = 'Reporting Statuses'
AND hoi1.organization_id = p_business_group_id
AND ass.employment_category = hoi2.org_information1
AND hoi2.organization_id = p_business_group_id
AND hoi2.org_information_context = 'Reporting Categories' )
AND p_start_date <= ass.effective_end_date
AND p_end_date >= ass.effective_start_date
AND TO_CHAR(ass.location_id) IN
(SELECT entity_id
FROM per_gen_hierarchy_nodes pgn
WHERE
pgn.hierarchy_version_id = p_hierarchy_version_id
AND (
pgn.hierarchy_node_id = l_hierarchy_node_id
OR pgn.parent_hierarchy_node_id = l_hierarchy_node_id)
AND pgn.node_type IN ('EST','LOC')
);
SELECT
COUNT(DECODE(peo.per_information1,'3',1)) c_hlfemale,
COUNT(DECODE(peo.per_information1,'1',1)) c_wfemale,
COUNT(DECODE(peo.per_information1,'2',1)) c_bfemale,
COUNT(DECODE(peo.per_information1,'5',1)) c_hfemale,
COUNT(DECODE(peo.per_information1,'4',1)) c_afemale,
COUNT(DECODE(peo.per_information1,'6',1)) c_ifemale,
COUNT(DECODE(peo.per_information1,'13',1)) c_tmracesfemale,
count(peo.person_id) "c_total_cat",
hrl.lookup_code lookup_code
FROM per_all_people_f peo,
per_all_assignments_f ass,
per_jobs_vl job,
hr_lookups hrl,
per_gen_hierarchy_nodes pgn_est
WHERE peo.person_id = ass.person_id
AND peo.per_information1 IS not NULL
AND peo.per_information_category = 'US'
AND job.job_information_category = 'US'
AND p_start_date <= nvl(job.date_to,p_start_date)
AND p_end_date >= job.date_from
AND job.job_information1 = hrl.lookup_code
AND hrl.lookup_type = 'US_EEO1_JOB_CATEGORIES'
AND ass.job_id = job.job_id
AND peo.effective_start_date =
(SELECT MAX(peo1.effective_start_date)
FROM per_people_f peo1
WHERE p_start_date <= peo1.effective_end_date
AND p_end_date >= peo1.effective_start_date
AND peo.person_id = peo1.person_id
AND peo1.current_employee_flag = 'Y'
)
AND ass.effective_start_date =
(SELECT MAX(ass1.effective_start_date)
FROM per_assignments_f ass1
WHERE p_start_date <= ass1.effective_end_date
AND p_end_date >= ass1.effective_start_date
AND ass.person_id = ass1.person_id
AND ass1.assignment_type = 'E'
AND ass1.primary_flag = 'Y'
)
AND ass.assignment_type = 'E'
AND ass.primary_flag = 'Y'
AND ass.business_group_id = p_business_group_id
AND peo.business_group_id = p_business_group_id
AND job.business_group_id = p_business_group_id
AND EXISTS (
SELECT 'X'
FROM hr_organization_information hoi1,
hr_organization_information hoi2
WHERE TO_CHAR(ass.assignment_status_type_id) = hoi1.org_information1
AND hoi1.org_information_context = 'Reporting Statuses'
AND hoi1.organization_id = p_business_group_id
AND ass.employment_category = hoi2.org_information1
AND hoi2.organization_id = p_business_group_id
AND hoi2.org_information_context = 'Reporting Categories'
)
AND ass.location_id = pgn_est.entity_id
AND (pgn_est.hierarchy_node_id = l_hierarchy_node_id
OR pgn_est.parent_hierarchy_node_id = l_hierarchy_node_id)
AND pgn_est.node_type IN ('EST','LOC')
AND pgn_est.hierarchy_version_id = p_hierarchy_version_id
AND pgn_est.business_group_id = p_business_group_id
AND peo.sex = 'F'
AND 1 > (SELECT count(*)
FROM per_gen_hierarchy_nodes pgn_loc
WHERE pgn_est.entity_id = pgn_loc.entity_id
AND pgn_loc.node_type = 'LOC'
AND pgn_loc.parent_hierarchy_node_id = pgn_est.hierarchy_node_id
AND pgn_loc.business_group_id = p_business_group_id)
GROUP BY hrl.lookup_code;
SELECT
COUNT(DECODE(peo.per_information1,'3',1)) c_hlmale,
COUNT(DECODE(peo.per_information1,'1',1)) c_wmale,
COUNT(DECODE(peo.per_information1,'2',1)) c_bmale,
COUNT(DECODE(peo.per_information1,'5',1)) c_hmale,
COUNT(DECODE(peo.per_information1,'4',1)) c_amale,
COUNT(DECODE(peo.per_information1,'6',1)) c_imale,
COUNT(DECODE(peo.per_information1,'13',1)) c_tmracesmale,
hrl.lookup_code lookup_code
FROM per_all_people_f peo,
per_all_assignments_f ass,
per_jobs_vl job,
hr_lookups hrl,
per_gen_hierarchy_nodes pgn_est
WHERE peo.person_id = ass.person_id
AND peo.per_information1 IS NOT NULL
AND peo.per_information_category = 'US'
AND job.job_information_category = 'US'
AND p_start_date <= NVL(job.date_to,p_start_date)
AND p_end_date >= job.date_from
AND job.job_information1 = hrl.lookup_code
AND hrl.lookup_type = 'US_EEO1_JOB_CATEGORIES'
AND ass.job_id = job.job_id
AND peo.effective_start_date =
(SELECT MAX(peo1.effective_start_date)
FROM per_people_f peo1
WHERE p_start_date <= peo1.effective_end_date
AND p_end_date >= peo1.effective_start_date
AND peo.person_id = peo1.person_id
AND peo1.current_employee_flag = 'Y'
)
AND ass.effective_start_date =
(SELECT MAX(ass1.effective_start_date)
FROM per_assignments_f ass1
WHERE p_start_date <= ass1.effective_end_date
AND p_end_date >= ass1.effective_start_date
AND ass.person_id = ass1.person_id
AND ass1.assignment_type = 'E'
AND ass1.primary_flag = 'Y'
)
AND ass.assignment_type = 'E'
AND ass.primary_flag = 'Y'
AND ass.business_group_id = P_BUSINESS_GROUP_ID
AND peo.business_group_id = P_BUSINESS_GROUP_ID
AND job.business_group_id = P_BUSINESS_GROUP_ID
AND EXISTS (
SELECT 'X'
FROM HR_ORGANIZATION_INFORMATION HOI1,
HR_ORGANIZATION_INFORMATION HOI2
WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND hoi1.org_information_context = 'Reporting Statuses'
AND hoi1.organization_id = P_BUSINESS_GROUP_ID
AND ass.employment_category = hoi2.org_information1
AND hoi2.organization_id = P_BUSINESS_GROUP_ID
AND hoi2.org_information_context = 'Reporting Categories'
)
AND ass.location_id = pgn_est.entity_id
AND (pgn_est.hierarchy_node_id = l_hierarchy_node_id
OR pgn_est.parent_hierarchy_node_id = l_hierarchy_node_id)
AND pgn_est.node_type IN ('EST','LOC')
AND pgn_est.hierarchy_version_id = p_hierarchy_version_id
AND pgn_est.business_group_id = p_business_group_id
AND peo.sex = 'M'
AND 1 > (SELECT count(*)
FROM per_gen_hierarchy_nodes pgn_loc
WHERE pgn_est.entity_id = pgn_loc.entity_id
AND pgn_loc.node_type = 'LOC'
AND pgn_loc.parent_hierarchy_node_id = pgn_est.hierarchy_node_id
AND pgn_loc.business_group_id = p_business_group_id)
GROUP BY hrl.lookup_code;
SELECT
count('all_birds_and_blokes_in_job') c_mf,
hrl.lookup_code lookup_code
FROM per_all_people_f peo,
per_all_assignments_f ass,
per_jobs_vl job,
hr_lookups hrl,
per_gen_hierarchy_nodes pgn_est
WHERE peo.person_id = ass.person_id
AND peo.per_information1 IN ('1','2','3','4','5','6','13') --BUG4410003
AND peo.per_information_category = 'US'
AND job.job_information_category = 'US'
AND p_start_date <= nvl(job.date_to,p_start_date)
AND p_end_date >= job.date_from
AND job.job_information1 = hrl.lookup_code
AND hrl.lookup_type = 'US_EEO1_JOB_CATEGORIES'
AND ass.job_id = job.job_id
AND peo.effective_start_date =
(SELECT MAX(peo1.effective_start_date)
FROM per_people_f peo1
WHERE p_start_date <= peo1.effective_end_date
AND p_end_date >= peo1.effective_start_date
AND peo.person_id = peo1.person_id
AND peo1.current_employee_flag = 'Y'
)
AND ass.effective_start_date =
(SELECT MAX(ass1.effective_start_date)
FROM per_assignments_f ass1
WHERE p_start_date <= ass1.effective_end_date
AND p_end_date >= ass1.effective_start_date
AND ass.person_id = ass1.person_id
AND ass1.assignment_type = 'E'
AND ass1.primary_flag = 'Y'
)
AND ass.assignment_type = 'E'
AND ass.primary_flag = 'Y'
AND ass.business_group_id = P_BUSINESS_GROUP_ID
AND peo.business_group_id = P_BUSINESS_GROUP_ID
AND job.business_group_id = P_BUSINESS_GROUP_ID
AND EXISTS (
SELECT 'X'
FROM HR_ORGANIZATION_INFORMATION HOI1,
HR_ORGANIZATION_INFORMATION HOI2
WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND hoi1.org_information_context = 'Reporting Statuses'
AND hoi1.organization_id = P_BUSINESS_GROUP_ID
AND ass.employment_category = hoi2.org_information1
AND hoi2.organization_id = P_BUSINESS_GROUP_ID
AND hoi2.org_information_context = 'Reporting Categories' )
AND ass.location_id = pgn_est.entity_id
AND (pgn_est.hierarchy_node_id = l_hierarchy_node_id
OR pgn_est.parent_hierarchy_node_id = l_hierarchy_node_id)
AND pgn_est.node_type IN ('EST','LOC')
AND pgn_est.hierarchy_version_id = p_hierarchy_version_id
AND pgn_est.business_group_id = p_business_group_id
AND 1 > (SELECT count(*)
FROM per_gen_hierarchy_nodes pgn_loc
WHERE pgn_est.entity_id = pgn_loc.entity_id
AND pgn_loc.node_type = 'LOC'
AND pgn_loc.parent_hierarchy_node_id = pgn_est.hierarchy_node_id
AND pgn_loc.business_group_id = p_business_group_id)
GROUP BY hrl.lookup_code;
SELECT
lei_information14 p_hlmale
,lei_information15 p_hlfemale
,lei_information4 p_wmale
,lei_information5 p_bmale
,lei_information6 p_hmale
,lei_information7 p_amale
,lei_information8 p_imale
,lei_information16 p_tmracesmale
,lei_information9 p_wfemale
,lei_information10 p_bfemale
,lei_information11 p_hfemale
,lei_information12 p_afemale
,lei_information13 p_ifemale
,lei_information17 p_tmracesfemale
,lei_information3 p_total
FROM hr_location_extra_info lei
,per_gen_hierarchy_nodes pgn
WHERE lei.lei_information1 = l_prev_year_filed
AND lei.information_type = 'EEO-1 Archive Information'
-- BUG3646445
AND lei.location_id = pgn.entity_id
AND pgn.hierarchy_node_id = l_hierarchy_node_id
AND pgn.hierarchy_version_id = p_hierarchy_version_id;
PROCEDURE insert_location_eit(p_hierarchy_node_id IN NUMBER,
p_hierarchy_version_id IN NUMBER,
p_report_year IN VARCHAR2) IS
p_update VARCHAR2(1) := 'C';
BEGIN --insert_location_eit
fnd_file.put_line(which => fnd_file.log,buff =>'insert INTO location eit ');
SELECT eloc.location_id,
eloc.location_code
INTO l_location_id,
l_location_code
FROM per_gen_hierarchy_nodes pgn,
hr_locations_all eloc
WHERE (hierarchy_node_id = p_hierarchy_node_id
or parent_hierarchy_node_id = p_hierarchy_node_id)
AND hierarchy_version_id = p_hierarchy_version_id
AND pgn.node_type = 'EST'
AND eloc.location_id = pgn.entity_id;
SELECT 'U',
location_extra_info_id
INTO p_update,
l_location_extra_info_id
FROM hr_location_extra_info
WHERE lei_information1 = p_report_year
AND lei_information_category = 'EEO-1 Archive Information'
AND location_id = l_location_id;
p_update := 'C';
IF p_update = 'U' THEN
fnd_file.put_line
(which => fnd_file.log,
buff => ' ');
buff => 'p_update '||p_update||' location_id to update IS '
||l_location_id ||' '|| l_location_code);
SELECT object_version_number
INTO l_object_version_number
FROM hr_location_extra_info
WHERE location_extra_info_id = l_location_extra_info_id;
hr_location_extra_info_api.delete_location_extra_info
(p_validate => false -- true
,p_location_extra_info_id => l_location_extra_info_id
,p_object_version_number => l_object_version_number
);
p_update := 'C';
END IF; --p_update = 'U'
IF p_update = 'C' THEN
fnd_file.put_line
(which => fnd_file.log,
buff => 'p_update '||p_update||' location_id '||l_location_id);
END IF; -- IF p_update = 'C'
SELECT count(*)
INTO l_eit_count
FROM hr_location_extra_info lei
WHERE location_id = l_location_id
AND information_type = 'EEO-1 Archive Information';
SELECT min(lei_information1)
INTO l_min_year
FROM hr_location_extra_info lei
WHERE location_id = l_location_id
AND information_type = 'EEO-1 Archive Information';
SELECT location_extra_info_id, object_version_number
INTO l_location_extra_info_id,l_object_version_number
FROM hr_location_extra_info lei
WHERE lei_information1 = l_min_year
AND information_type = 'EEO-1 Archive Information'
AND location_id = l_location_id;
hr_location_extra_info_api.delete_location_extra_info
(p_validate => false -- true
,p_location_extra_info_id => l_location_extra_info_id
,p_object_version_number => l_object_version_number
);
END insert_location_eit;
PROCEDURE insert_org_eit(p_hierarchy_node_id IN NUMBER,
p_hierarchy_version_id IN NUMBER,
p_business_group_id IN NUMBER,
p_report_year IN VARCHAR2) IS
p_update VARCHAR2(1) := 'C';
l_proc VARCHAR2(40) := g_package || 'insert_org_eit';
BEGIN--insert_org_eit
BEGIN--Local1
l_organization_id := l_org_rec.par_ent_org_id;
SELECT 'U', org_information_id
INTO p_update, l_org_information_id
FROM hr_organization_information
WHERE org_information1 = p_report_year
AND ORG_INFORMATION_CONTEXT = 'EEO_Archive'
AND organization_id = l_org_rec.par_ent_org_id;
p_update := 'C';
IF p_update = 'U' THEN
fnd_file.put_line
(which => fnd_file.log,
buff => 'p_update '||p_update||' org_id to update IS '
||l_organization_id||' IN bus grp '
||p_business_group_id);
SELECT object_version_number
INTO l_object_version_number
FROM hr_organization_information
WHERE org_information_id = l_org_information_id;
hr_organization_api.delete_org_manager
(p_validate => false -- true
,p_org_information_id => l_org_information_id
,p_object_version_number => l_object_version_number
);
p_update := 'C';
END IF; --p_update = 'U'
IF p_update = 'C' THEN
fnd_file.put_line
(which => fnd_file.log,
buff => ' ');
buff => 'p_update '||p_update||' org id '||l_organization_id);
END IF; --p_update = 'C'
SELECT count(*)
INTO l_eit_count
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND org_information_context = 'EEO_Archive';
SELECT MIN(org_information1)
INTO l_min_year
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND ORG_INFORMATION_CONTEXT = 'EEO_Archive';
SELECT org_information_id,
object_version_number
INTO l_org_information_id
,l_object_version_number
FROM hr_organization_information
WHERE org_information1 = l_min_year
AND org_information_context = 'EEO_Archive'
AND organization_id = p_business_group_id;
hr_organization_api.delete_org_manager
(p_validate => false -- true
,p_org_information_id => l_org_information_id
,p_object_version_number => l_object_version_number
);
END insert_org_eit;
insert_location_eit(p_hierarchy_node_id => l_hierarchy_node_id,
p_hierarchy_version_id => p_hierarchy_version_id,
p_report_year => l_report_year);
insert_org_eit(p_hierarchy_node_id => l_hierarchy_node_id,
p_hierarchy_version_id => p_hierarchy_version_id,
p_business_group_id => p_business_group_id,
p_report_year => l_report_year);
SELECT count('establishments')
FROM per_gen_hierarchy_nodes
WHERE node_type = 'EST'
AND hierarchy_version_id = p_hierarchy_version_id;