[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
115.19 Modified g_select_clause to use new package function
per_us_hr_utility_pkg.derive_single_race. This deals with people of
Two or More race also.
Commented the variables and queries declared for usage with Two or More races.
04-OCT-2011 nvelaga 115.20 13020321 Changed the data type of variable l_report_date from DATE to VARCHAR2(20).
Replaced the condition to_char(l_report_date) with
TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'') in g_from_where_clause,
g_ft_effective_dates and g_nh_effective_dates.
15-MAR-2012 nkjaladi 115.21 13610842/ Modified generate_xml_data to modify
13610922 text of g_nh_effective_dates so that
new hires are counted correctly and also
formation of l_query_text to fetch
the full time employee count correctly
as it was using the fixed employee
category instead of the customer
defined employee category.
21-MAR-2012 agarai 115.22 13645315 Increased the size of l_bg_name from 100
to 300 in the procedure generate_footer_xml_data.
07-MAY-2012 agarai 115.23 14034810 Added a check in g_from_clause to exclude
employees having EEO4A ethnic origin data.
Added l_eeo4_ethnic in generate_xml_data
procedure to include employees having
EEO4 ethnic origin data in the count of
employees in each job function.
****************************************************************************/
-- Added for bug 7218995
g_fp_regulars VARCHAR2(2000);
g_select_clause VARCHAR2(10000);
g_tmraces_select_clause VARCHAR2(10000);
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 (''FR'')'
||' 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 (''FR'')'
||' 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 (''FR'')'
||' AND ass.employment_category NOT IN ('|| g_fp_regulars ||')'
||g_group_order_by;
g_tmr_nh_sql := 'SELECT hl.lookup_code job_category_code,
hl.meaning job_category_name,'
|| g_tmraces_select_clause || g_tmraces_where_clause || g_nh_effective_dates
||' AND job.job_information7 in (' || p_job_codes || ')'
||' AND hl.lookup_code = :1 '
-- ||' AND ass.employment_category in (''FR'')'
||' AND ass.employment_category in ('||g_fp_regulars ||')'
|| g_group_order_by;
g_tmr_ft_emp_sql := 'SELECT hl.lookup_code job_category_code,
hl.meaning job_category_name,'
|| g_tmraces_select_clause || g_tmraces_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 (''FR'')'
||' AND ass.employment_category in ('|| g_fp_regulars ||')'
|| g_group_order_by;
g_tmr_oft_sql := 'SELECT hl.lookup_code job_category_code,
hl.meaning job_category_name,'
|| g_tmraces_select_clause || g_tmraces_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 (''FR'')'
||' 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 meaning
FROM hr_lookups
WHERE lookup_type = 'US_EEO4_JOB_FUNCTIONS'
AND lookup_code = l_lookup_code;
g_select_clause := ' count(decode(per_us_hr_utility_pkg.derive_single_race(peo.person_id),1,decode(peo.sex,''M'',1,null),null)) no_cons_wmale_emps,
count(decode(per_us_hr_utility_pkg.derive_single_race(peo.person_id),2,decode(peo.sex,''M'',1,null),null)) no_cons_bmale_emps,
count(decode(per_us_hr_utility_pkg.derive_single_race(peo.person_id),3,decode(peo.sex,''M'',1,null),null)) no_cons_hmale_emps,
count(decode(per_us_hr_utility_pkg.derive_single_race(peo.person_id),4,decode(peo.sex,''M'',1,null),5,decode(peo.sex,''M'',1,null),null)) no_cons_amale_emps,
count(decode(per_us_hr_utility_pkg.derive_single_race(peo.person_id),6,decode(peo.sex,''M'',1,null),null)) no_cons_imale_emps,
count(decode(per_us_hr_utility_pkg.derive_single_race(peo.person_id),1,decode(peo.sex,''F'',1,null),null)) no_cons_wfemale_emps,
count(decode(per_us_hr_utility_pkg.derive_single_race(peo.person_id),2,decode(peo.sex,''F'',1,null),null)) no_cons_bfemale_emps,
count(decode(per_us_hr_utility_pkg.derive_single_race(peo.person_id),3,decode(peo.sex,''F'',1,null),null)) no_cons_hfemale_emps,
count(decode(per_us_hr_utility_pkg.derive_single_race(peo.person_id),4,decode(peo.sex,''F'',1,null),5,decode(peo.sex,''F'',1,null),null)) no_cons_afemale_emps,
count(decode(per_us_hr_utility_pkg.derive_single_race(peo.person_id),6,decode(peo.sex,''F'',1,null),null)) no_cons_ifemale_emps ';
g_select_clause := ' count(decode(peo.per_information1,''1'',decode(peo.sex,''M'',1,null),null)) no_cons_wmale_emps,
count(decode(peo.per_information1,''2'',decode(peo.sex,''M'',1,null),null)) no_cons_bmale_emps,
count(decode(peo.per_information1,''3'',decode(peo.sex,''M'',1,null),null)) no_cons_hmale_emps,
count(decode(peo.per_information1,''4'',decode(peo.sex,''M'',1,null),''5'',decode(peo.sex,''M'',1,null),null)) no_cons_amale_emps,
count(decode(peo.per_information1,''6'',decode(peo.sex,''M'',1,null),null)) no_cons_imale_emps,
count(decode(peo.per_information1,''1'',decode(peo.sex,''F'',1,null),null)) no_cons_wfemale_emps,
count(decode(peo.per_information1,''2'',decode(peo.sex,''F'',1,null),null)) no_cons_bfemale_emps,
count(decode(peo.per_information1,''3'',decode(peo.sex,''F'',1,null),null)) no_cons_hfemale_emps,
count(decode(peo.per_information1,''4'',decode(peo.sex,''F'',1,null),''5'',decode(peo.sex,''F'',1,null),null)) no_cons_afemale_emps,
count(decode(peo.per_information1,''6'',decode(peo.sex,''F'',1,null),null)) no_cons_ifemale_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''
--added for bug 14034810
AND (NOT 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
)
)';
g_tmraces_select_clause := ' count(decode(pei.pei_information5,''1'',decode(peo.sex,''M'',1,null),null)) no_tmraces_wmale_emps,
count(decode(pei.pei_information5,''2'',decode(peo.sex,''M'',1,null),null)) no_tmraces_bmale_emps,
count(decode(pei.pei_information5,''3'',decode(peo.sex,''M'',1,null),''9'',decode(peo.sex,''M'',1,null),null)) no_tmraces_hmale_emps,
count(decode(pei.pei_information5,''4'',decode(peo.sex,''M'',1,null),''5'',decode(peo.sex,''M'',1,null),null)) no_tmraces_amale_emps,
count(decode(pei.pei_information5,''6'',decode(peo.sex,''M'',1,null),null)) no_tmraces_imale_emps,
count(decode(pei.pei_information5,''1'',decode(peo.sex,''F'',1,null),null)) no_tmraces_wfemale_emps,
count(decode(pei.pei_information5,''2'',decode(peo.sex,''F'',1,null),null)) no_tmraces_bfemale_emps,
count(decode(pei.pei_information5,''3'',decode(peo.sex,''F'',1,null),''9'',decode(peo.sex,''F'',1,null),null)) no_tmraces_hfemale_emps,
count(decode(pei.pei_information5,''4'',decode(peo.sex,''F'',1,null),''5'',decode(peo.sex,''F'',1,null),null)) no_tmraces_afemale_emps,
count(decode(pei.pei_information5,''6'',decode(peo.sex,''F'',1,null),null)) no_tmraces_ifemale_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_char(l_report_date) || '''
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 SYSDATE BETWEEN date_from AND NVL(date_to,SYSDATE) )
AND ass.assignment_status_type_id = past.assignment_status_type_id
AND peo.current_employee_flag = ''Y''
AND ass.assignment_type = ''E''';
l_query_text := 'select count(1) l_ft_emp_count ' || g_from_where_clause
|| g_ft_effective_dates || ' AND ass.employment_category in (''FR'')';
AND (SELECT date_start
FROM per_periods_of_service
WHERE period_of_service_id = ass.period_of_service_id)
BETWEEN (add_months(TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY''), -12) + 1)
AND TO_DATE(''' || l_report_date || ''', ''DD-MM-YYYY'') ';*/
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_eeo4_ethnic := ' AND ( 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
)
)';
l_query_text := 'select count(1) l_ft_emp_count ' || g_from_where_clause|| l_eeo4_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_eeo4_ethnic || g_ft_effective_dates || ' group by job.job_information7 ';
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(name,' ') jurisdiction_name,
NVL(address_line_1,' ')||' '||NVL(address_line_2,' ')
||' '||NVL(address_line_3,' ') address,
NVL(town_or_city,' ') town_or_city,
NVL(region_1,' ') county,
NVL(region_2,' ')||' '||NVL(postal_code,' ') state_zip
FROM hr_all_organization_units
WHERE business_group_id = g_business_group_id
and 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 pei_information5 from per_people_extra_info table
*/
/* Commented for bug#11736960 starts
source_cursor := dbms_sql.open_cursor;
SELECT lookup_code, meaning, description
FROM hr_lookups
WHERE lookup_type = 'US_EEO4_JOB_CATEGORIES'
ORDER BY lookup_code;
select pei_information5 from per_people_extra_info table
*/
/* Commented for bug#11736960 starts
source_cursor := dbms_sql.open_cursor;
SELECT lookup_code, meaning, description
FROM hr_lookups
WHERE lookup_type = 'US_EEO4_JOB_CATEGORIES'
ORDER BY lookup_code;
select pei_information5 from per_people_extra_info table
*/
/* Commented for bug#11736960 starts
source_cursor := dbms_sql.open_cursor;
SELECT lookup_code, meaning, description
FROM hr_lookups
WHERE lookup_type = 'US_EEO4_JOB_CATEGORIES'
ORDER BY lookup_code;