[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT_CLAUSE VARCHAR2(2000) := 'count(peo.person_id) cons_total_category_emps,
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),''7'',
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),''7'',
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_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 = ' || TO_CHAR(P_BUSINESS_GROUP_ID) || '
AND SYSDATE BETWEEN date_from AND NVL(date_to,SYSDATE) )
AND ass.assignment_status_type_id = ast1.assignment_status_type_id
AND peo.current_employee_flag = ''Y''
AND ass.assignment_type = ''E''';
L_NH_EFFECTIVE_DATES VARCHAR2(1000) := 'AND (SELECT date_start
FROM per_periods_of_service
WHERE period_of_service_id = ass.period_of_service_id) BETWEEN ''' || TO_CHAR(ADD_MONTHS(L_REPORT_DATE
,-12) + 1) || ''' AND ''' || TO_CHAR(L_REPORT_DATE) || ''' ';
L_QUERY_TEXT := 'Select count(1) l_ft_emp_count' || FROM_WHERE_CLAUSE || L_FT_EFFECTIVE_DATES || 'AND ass.employment_category in ' || L_FRC;
L_QUERY_TEXT := 'Select job.job_information7 l_function_code,
count(1) l_function_count ' || FROM_WHERE_CLAUSE || L_FT_EFFECTIVE_DATES || ' group by job.job_information7 ';
P_EEO4_QUERY := 'SELECT job.job_information7 Job_function_code,
PQH_PQHEEO4_XMLP_PKG.CP_1_P CP_1,
PQH_PQHEEO4_XMLP_PKG.CF_TOTAL_TITLEFORMULA0005(DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
''FR'', ''1. FULL-TIME EMPLOYEES'',
''2. OTHER THAN FULL-TIME EMPLOYEES'')) CF_TOTAL_TITLE,
PQH_PQHEEO4_XMLP_PKG.CF_SET_FUNCTION_DESCFORMULA(job.job_information7) CF_set_function_desc,
DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
''FR'', ''1. FULL-TIME EMPLOYEES'',
''2. OTHER THAN FULL-TIME EMPLOYEES'') Employment_Category,
hl.lookup_code job_category_code,
hl.meaning Job_category_name,
pqh_salary_range_pkg.get_salary_range(
pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor) Salary_range, ' || SELECT_CLAUSE || FROM_WHERE_CLAUSE || L_FT_EFFECTIVE_DATES || '
AND job.job_information7 IN (' || L_DYNAMIC_WHERE || ')
GROUP BY job.job_information7,
DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
''FR'', ''1. FULL-TIME EMPLOYEES'',
''2. OTHER THAN FULL-TIME EMPLOYEES''),
hl.lookup_code ,
hl.meaning ,
pqh_salary_range_pkg.get_salary_range(
pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor)
UNION
SELECT job.job_information7,
PQH_PQHEEO4_XMLP_PKG.CP_1_P CP_1,
PQH_PQHEEO4_XMLP_PKG.CF_TOTAL_TITLEFORMULA0005(''3. NEW HIRE DURING FISCAL YEAR - PERMANENT FULL TIME ONLY'') CF_TOTAL_TITLE,
PQH_PQHEEO4_XMLP_PKG.CF_SET_FUNCTION_DESCFORMULA(job.job_information7) CF_set_function_desc,
''3. NEW HIRE DURING FISCAL YEAR - PERMANENT FULL TIME ONLY'',
hl.lookup_code job_category_code,
hl.meaning Job_category_name,
'' '', ' || SELECT_CLAUSE || FROM_WHERE_CLAUSE || L_NH_EFFECTIVE_DATES || '
AND ass.employment_category in ' || L_FRC || '
AND job.job_information7 IN (' || L_DYNAMIC_WHERE || ')
GROUP BY job.job_information7,
hl.lookup_code,
hl.meaning
UNION
SELECT ''XX'',
PQH_PQHEEO4_XMLP_PKG.CP_1_P CP_1,
PQH_PQHEEO4_XMLP_PKG.CF_TOTAL_TITLEFORMULA0005(DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
''FR'', ''1. FULL-TIME EMPLOYEES'',
''2. OTHER THAN FULL-TIME EMPLOYEES'')) CF_TOTAL_TITLE,
PQH_PQHEEO4_XMLP_PKG.CF_SET_FUNCTION_DESCFORMULA(''XX'') CF_set_function_desc,
DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
''FR'', ''1. FULL-TIME EMPLOYEES'',
''2. OTHER THAN FULL-TIME EMPLOYEES'') ,
hl.lookup_code ,
hl.meaning ,
pqh_salary_range_pkg.get_salary_range(
pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor), ' || SELECT_CLAUSE || FROM_WHERE_CLAUSE || L_FT_EFFECTIVE_DATES || '
AND job.job_information7 NOT IN (' || L_DYNAMIC_WHERE || ')
GROUP BY
DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
''FR'', ''1. FULL-TIME EMPLOYEES'',
''2. OTHER THAN FULL-TIME EMPLOYEES''),
hl.lookup_code ,
hl.meaning ,
pqh_salary_range_pkg.get_salary_range(
pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor)
UNION
SELECT ''XX'',
PQH_PQHEEO4_XMLP_PKG.CP_1_P CP_1,
PQH_PQHEEO4_XMLP_PKG.CF_TOTAL_TITLEFORMULA0005(''3. NEW HIRE DURING FISCAL YEAR - PERMANENT FULL TIME ONLY'') CF_TOTAL_TITLE,
PQH_PQHEEO4_XMLP_PKG.CF_SET_FUNCTION_DESCFORMULA(''XX'') CF_set_function_desc,
''3. NEW HIRE DURING FISCAL YEAR - PERMANENT FULL TIME ONLY'',
hl.lookup_code job_category_code,
hl.meaning Job_category_name,
'' '', ' || SELECT_CLAUSE || FROM_WHERE_CLAUSE || L_NH_EFFECTIVE_DATES || '
AND ass.employment_category in ' || L_FRC || '
AND job.job_information7 NOT IN (' || L_DYNAMIC_WHERE || ')
GROUP BY
hl.lookup_code,
hl.meaning ';
P_EEO4_QUERY := 'SELECT job.job_information7 Job_function_code,
PQH_PQHEEO4_XMLP_PKG.CP_1_P CP_1,
PQH_PQHEEO4_XMLP_PKG.CF_TOTAL_TITLEFORMULA0005(DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
''FR'', ''1. FULL-TIME EMPLOYEES'',
''2. OTHER THAN FULL-TIME EMPLOYEES'')) CF_TOTAL_TITLE,
PQH_PQHEEO4_XMLP_PKG.CF_SET_FUNCTION_DESCFORMULA(job.job_information7) CF_set_function_desc,
DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
''FR'', ''1. FULL-TIME EMPLOYEES'',
''2. OTHER THAN FULL-TIME EMPLOYEES'') Employment_Category,
hl.lookup_code job_category_code,
hl.meaning Job_category_name,
pqh_salary_range_pkg.get_salary_range(
pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor) Salary_range, ' || SELECT_CLAUSE || FROM_WHERE_CLAUSE || L_FT_EFFECTIVE_DATES || '
AND job.job_information7 IN (' || L_DYNAMIC_WHERE || ')
GROUP BY job.job_information7,
DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
''FR'', ''1. FULL-TIME EMPLOYEES'',
''2. OTHER THAN FULL-TIME EMPLOYEES''),
hl.lookup_code ,
hl.meaning ,
pqh_salary_range_pkg.get_salary_range(
pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor)
UNION
SELECT job.job_information7,
PQH_PQHEEO4_XMLP_PKG.CP_1_P CP_1,
PQH_PQHEEO4_XMLP_PKG.CF_TOTAL_TITLEFORMULA0005(''3. NEW HIRE DURING FISCAL YEAR - PERMANENT FULL TIME ONLY'') CF_TOTAL_TITLE,
PQH_PQHEEO4_XMLP_PKG.CF_SET_FUNCTION_DESCFORMULA(job.job_information7) CF_set_function_desc,
''3. NEW HIRE DURING FISCAL YEAR - PERMANENT FULL TIME ONLY'',
hl.lookup_code job_category_code,
hl.meaning Job_category_name,
'' '', ' || SELECT_CLAUSE || FROM_WHERE_CLAUSE || L_NH_EFFECTIVE_DATES || '
AND job.job_information7 IN (' || L_DYNAMIC_WHERE || ')
AND ass.employment_category in ' || L_FRC || '
GROUP BY job.job_information7,
hl.lookup_code,
hl.meaning
UNION
SELECT ''XX'' ,
PQH_PQHEEO4_XMLP_PKG.CP_1_P CP_1,
PQH_PQHEEO4_XMLP_PKG.CF_TOTAL_TITLEFORMULA0005(DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
''FR'', ''1. FULL-TIME EMPLOYEES'',
''2. OTHER THAN FULL-TIME EMPLOYEES'')) CF_TOTAL_TITLE,
PQH_PQHEEO4_XMLP_PKG.CF_SET_FUNCTION_DESCFORMULA(''XX'') CF_set_function_desc,
DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
''FR'', ''1. FULL-TIME EMPLOYEES'',
''2. OTHER THAN FULL-TIME EMPLOYEES'') ,
hl.lookup_code ,
hl.meaning ,
pqh_salary_range_pkg.get_salary_range(
pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor) , ' || SELECT_CLAUSE || FROM_WHERE_CLAUSE || L_FT_EFFECTIVE_DATES || '
GROUP BY
DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
''FR'', ''1. FULL-TIME EMPLOYEES'',
''2. OTHER THAN FULL-TIME EMPLOYEES''),
hl.lookup_code ,
hl.meaning ,
pqh_salary_range_pkg.get_salary_range(
pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor)
UNION
SELECT ''XX'',
PQH_PQHEEO4_XMLP_PKG.CP_1_P CP_1,
PQH_PQHEEO4_XMLP_PKG.CF_TOTAL_TITLEFORMULA0005(''3. NEW HIRE DURING FISCAL YEAR - PERMANENT FULL TIME ONLY'') CF_TOTAL_TITLE,
PQH_PQHEEO4_XMLP_PKG.CF_SET_FUNCTION_DESCFORMULA(''XX'') CF_set_function_desc,
''3. NEW HIRE DURING FISCAL YEAR - PERMANENT FULL TIME ONLY'',
hl.lookup_code job_category_code,
hl.meaning Job_category_name,
'' '', ' || SELECT_CLAUSE || FROM_WHERE_CLAUSE || L_NH_EFFECTIVE_DATES || '
AND ass.employment_category in ' || L_FRC || '
GROUP BY
hl.lookup_code,
hl.meaning ';
SELECT
MEANING
FROM
HR_LOOKUPS
WHERE LOOKUP_TYPE = 'US_EEO4_JOB_FUNCTIONS'
AND LOOKUP_CODE = JOB_FUNCTION_CODE;