[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_select_clause VARCHAR2(10000);
SELECT pei.pei_information1,
pei.pei_information2,
pei.pei_information3,
pei.pei_information4,
pei.pei_information5,
pei.pei_information6
FROM per_people_extra_info pei
WHERE pei.person_id = cp_person_id
AND pei.information_type = 'US_EEO4A_ETHNIC_ORIGIN';
g_nh_sql := 'SELECT hl.lookup_code job_category_code,
hl.meaning job_category_name,'
|| g_select_clause || g_from_where_clause || g_nh_effective_dates
||' AND job.job_information7 in (' || p_job_codes || ')'
||' AND hl.lookup_code = :1 '
||' AND ass.employment_category in ('|| g_fp_regulars ||')'
|| g_group_order_by;
g_ft_emp_sql := 'SELECT hl.lookup_code job_category_code,
hl.meaning job_category_name,'
||g_select_clause || g_from_where_clause|| g_ft_effective_dates
||' AND job.job_information7 in ( ' || p_job_codes || ' ) '
||' AND hl.lookup_code = :1 '
|| p_dynamic_where
||' AND ass.employment_category in ('|| g_fp_regulars ||')'
|| g_group_order_by;
g_oft_sql := 'SELECT hl.lookup_code job_category_code,
hl.meaning job_category_name,'
||g_select_clause||g_from_where_clause||g_ft_effective_dates
||' AND job.job_information7 in ( ' || p_job_codes || ' )'
||' AND hl.lookup_code = :1 '
||' AND ass.employment_category NOT IN ('|| g_fp_regulars ||')'
||g_group_order_by;
SELECT lookup_code, meaning, description
FROM hr_lookups
WHERE lookup_type = 'US_EEO4_JOB_CATEGORIES'
ORDER BY lookup_code;
SELECT lookup_code, meaning, description
FROM hr_lookups
WHERE lookup_type = 'US_EEO4_JOB_CATEGORIES'
ORDER BY lookup_code;
SELECT lookup_code, meaning, description
FROM hr_lookups
WHERE lookup_type = 'US_EEO4_JOB_CATEGORIES'
ORDER BY lookup_code;
SELECT name
FROM hr_organization_units
WHERE organization_id = g_business_group_id
AND business_group_id = g_business_group_id;
SELECT NVL(hou.name,' ') jurisdiction_name,
NVL(hl.address_line_1,' ')||' '||NVL(hl.address_line_2,' ')
||' '||NVL(hl.address_line_3,' ') address,
NVL(hl.town_or_city,' ') town_or_city,
NVL(hl.region_1,' ') county,
NVL(hl.region_2,' ')||' '||NVL(hl.postal_code,' ') state_zip
FROM hr_all_organization_units hou,
hr_locations hl
WHERE hou.location_id = hl.location_id
AND hou.business_group_id = g_business_group_id
AND hou.organization_id = g_business_group_id;
SELECT NVL(org_information1,' ') cert_officer_name,
NVL(org_information2,' ') cert_officer_title,
NVL(org_information3,' ') contact_name,
NVL(org_information4,' ') contact_title,
NVL(org_information5,' ')
||' '|| NVL(org_information6,' ') contact_address,
NVL(org_information7,' ')
||' '|| NVL(org_information8,' ')
||' '|| NVL(org_information9,' ') contact_city_state_zip,
NVL(org_information10,' ') contact_telephone,
NVL(org_information12,' ') control_number,
NVL(org_information15, ' ') email,
NVL(org_information14, ' ') fax
FROM hr_organization_information
WHERE org_information_context = 'EEO_REPORT'
AND organization_id = g_business_group_id;
SELECT lookup_code, meaning, description
FROM hr_lookups
WHERE lookup_type = 'US_EEO4_JOB_CATEGORIES'
ORDER BY lookup_code;
SELECT lookup_code, meaning, description
FROM hr_lookups
WHERE lookup_type = 'US_EEO4_JOB_CATEGORIES'
ORDER BY lookup_code;
SELECT meaning
FROM hr_lookups
WHERE lookup_type = 'US_EEO4_JOB_FUNCTIONS'
AND lookup_code = l_lookup_code;
g_select_clause :='count(decode(per_us_eeo4a_pkg.derive_single_race(peo.person_id),1,decode(peo.sex,''M'',1,null),null)) no_cons_wmale_emps,
count(decode(per_us_eeo4a_pkg.derive_single_race(peo.person_id),2,decode(peo.sex,''M'',1,null),null)) no_cons_hmale_emps,
count(decode(per_us_eeo4a_pkg.derive_single_race(peo.person_id),3,decode(peo.sex,''M'',1,null),null)) no_cons_cmale_emps,
count(decode(per_us_eeo4a_pkg.derive_single_race(peo.person_id),4,decode(peo.sex,''M'',1,null),null)) no_cons_fmale_emps,
count(decode(per_us_eeo4a_pkg.derive_single_race(peo.person_id),5,decode(peo.sex,''M'',1,null),null)) no_cons_jmale_emps,
count(decode(per_us_eeo4a_pkg.derive_single_race(peo.person_id),6,decode(peo.sex,''M'',1,null),null)) no_cons_omale_emps,
count(decode(per_us_eeo4a_pkg.derive_single_race(peo.person_id),1,decode(peo.sex,''F'',1,null),null)) no_cons_wfemale_emps,
count(decode(per_us_eeo4a_pkg.derive_single_race(peo.person_id),2,decode(peo.sex,''F'',1,null),null)) no_cons_hfemale_emps,
count(decode(per_us_eeo4a_pkg.derive_single_race(peo.person_id),3,decode(peo.sex,''F'',1,null),null)) no_cons_cfemale_emps,
count(decode(per_us_eeo4a_pkg.derive_single_race(peo.person_id),4,decode(peo.sex,''F'',1,null),null)) no_cons_ffemale_emps,
count(decode(per_us_eeo4a_pkg.derive_single_race(peo.person_id),5,decode(peo.sex,''F'',1,null),null)) no_cons_jfemale_emps,
count(decode(per_us_eeo4a_pkg.derive_single_race(peo.person_id),6,decode(peo.sex,''F'',1,null),null)) no_cons_ofemale_emps ';
AND ppp.change_date = ( SELECT MAX(change_date)
FROM per_pay_proposals pro
WHERE ppp.assignment_id = pro.assignment_id
AND pro.change_date <= TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'')
AND pro.approved = ''Y'' )
AND ass.organization_id IN (SELECT organization_id
FROM hr_all_organization_units
WHERE business_group_id = ' || p_business_group_id || '
AND TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'') BETWEEN date_from AND NVL(date_to,TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'')) )
AND ass.assignment_status_type_id = past.assignment_status_type_id
AND peo.current_employee_flag = ''Y''
AND ass.assignment_type = ''E''
AND (NOT EXISTS (SELECT ''X''
FROM per_people_extra_info ppei
WHERE ppei.person_id = peo.person_id
AND ppei.information_type = ''US_ETHNIC_ORIGIN''
AND(DECODE(ppei.pei_information1, ''Y'', 1,0)
+ DECODE(ppei.pei_information2, ''Y'', 1,0)
+ DECODE(ppei.pei_information3, ''Y'', 1,0)
+ DECODE(ppei.pei_information4, ''Y'', 1,0)
+ DECODE(ppei.pei_information5, ''Y'', 1,0)
+ DECODE(ppei.pei_information6, ''Y'', 1,0)
) >= 1
)
)';
AND EXISTS (SELECT 1
FROM per_periods_of_service pps
WHERE pps.period_of_service_id = ass.period_of_service_id
AND pps.person_id = peo.person_id
AND pps.date_start BETWEEN (add_months(TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY''), -12) + 1)
AND TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'')) ';
l_eeo4a_ethnic := ' AND ( EXISTS (SELECT ''X''
FROM per_people_extra_info ppei
WHERE ppei.person_id = peo.person_id
AND ppei.information_type = ''US_EEO4A_ETHNIC_ORIGIN''
AND(DECODE(ppei.pei_information1, ''Y'', 1,0)
+ DECODE(ppei.pei_information2, ''Y'', 1,0)
+ DECODE(ppei.pei_information3, ''Y'', 1,0)
+ DECODE(ppei.pei_information4, ''Y'', 1,0)
+ DECODE(ppei.pei_information5, ''Y'', 1,0)
+ DECODE(ppei.pei_information6, ''Y'', 1,0)
) = 1
)
)';
l_query_text := 'select count(1) l_ft_emp_count ' || g_from_where_clause || l_eeo4a_ethnic
|| g_ft_effective_dates || ' AND ass.employment_category in ('||g_fp_regulars||')';
l_query_text := 'Select job.job_information7 l_function_code,
count(1) l_function_count ' || g_from_where_clause || l_eeo4a_ethnic || g_ft_effective_dates || ' group by job.job_information7 ';