[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'SELECT -- Hires Salary Variance Status
rnk.diff_rnk HRI_P_ORDER_BY_1
,hri_bpl_job.get_job_display_name
(job.id
,job.business_group_id
,job.value) || '' ('' || bgr.org_information9 || '')''
HRI_P_JOB_CN
,rnk.new_hire_hdc HRI_P_MEASURE1
,rnk.curr_emp_hdc HRI_P_MEASURE5
,rnk.new_hire_avg_sal HRI_P_MEASURE2
,rnk.curr_emp_avg_sal HRI_P_MEASURE3
,rnk.diff_pct HRI_P_MEASURE4
FROM
hri_dbi_cl_job_n_v job
,hr_organization_information bgr
,(SELECT
diff.job_id
,diff.new_hire_hdc
,diff.new_hire_sal
,diff.new_hire_avg_sal
,diff.curr_emp_hdc
,diff.curr_emp_sal
,diff.curr_emp_avg_sal
,diff.diff_pct
,RANK() OVER (ORDER BY ABS(diff.diff_pct) DESC, diff.curr_emp_sal, diff.job_id) diff_rnk
FROM
(SELECT
averages.job_id job_id
,averages.new_hire_hdc new_hire_hdc
,averages.new_hire_sal new_hire_sal
,averages.new_hire_avg_sal new_hire_avg_sal
,averages.curr_emp_hdc curr_emp_hdc
,averages.curr_emp_sal curr_emp_sal
,averages.curr_emp_avg_sal curr_emp_avg_sal
,DECODE(averages.curr_emp_avg_sal,
0, 0,
(averages.new_hire_avg_sal - averages.curr_emp_avg_sal) * 100 /
averages.curr_emp_avg_sal) diff_pct
FROM
(SELECT
hire_sal.job_id job_id
,hire_sal.new_hire_hdc new_hire_hdc
,hire_sal.new_hire_sal new_hire_sal
,DECODE(hire_sal.new_hire_hdc, 0, 0, hire_sal.new_hire_sal / hire_sal.new_hire_hdc)
new_hire_avg_sal
,curr_emp.total_headcount - hire_sal.new_hire_hdc
curr_emp_hdc
,curr_emp.total_salary - hire_sal.new_hire_sal
curr_emp_sal
,DECODE(curr_emp.total_headcount - hire_sal.new_hire_hdc,
0, 0,
(curr_emp.total_salary - hire_sal.new_hire_sal) /
(curr_emp.total_headcount - hire_sal.new_hire_hdc))
curr_emp_avg_sal
FROM
(SELECT /*+ NO_MERGE */
sub_job.job_id
,SUM(sub_job.total_headcount) total_headcount
,SUM(DECODE(sub_job.anl_slry_currency,
:GLOBAL_CURRENCY, sub_job.total_anl_slry,
hri_bpl_currency.convert_currency_amount
(sub_job.anl_slry_currency
,:GLOBAL_CURRENCY
,&BIS_CURRENT_EFFECTIVE_END_DATE
,sub_job.total_anl_slry
,:GLOBAL_RATE))) total_salary
FROM
hri_mdp_sup_wrkfc_job_mv sub_job
WHERE sub_job.supervisor_person_id = &HRI_PERSON+HRI_PER_USRDR_H
AND sub_job.anl_slry_currency <> ''NA_EDW''
AND &BIS_CURRENT_EFFECTIVE_END_DATE BETWEEN effective_start_date
AND effective_end_date
GROUP BY
sub_job.job_id) curr_emp
,(SELECT /*+ NO_MERGE */
hire.job_id job_id
,SUM(hire.headcount_value) new_hire_hdc
,SUM(DECODE(hire.currency,
:GLOBAL_CURRENCY, hire.salary,
hri_bpl_currency.convert_currency_amount
(hire.currency
,:GLOBAL_CURRENCY
,&BIS_CURRENT_EFFECTIVE_END_DATE
,hire.salary
,:GLOBAL_RATE))) new_hire_sal
FROM
hri_mdp_sup_gain_hire_mv hire
WHERE hire.supervisor_id = &HRI_PERSON+HRI_PER_USRDR_H
AND hire.effective_date BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
AND &BIS_CURRENT_EFFECTIVE_END_DATE
GROUP BY
hire.job_id) hire_sal
WHERE curr_emp.job_id = hire_sal.job_id
AND hire_sal.new_hire_hdc > 0
) averages
) diff
) rnk
WHERE rnk.job_id = job.id
AND bgr.organization_id = job.business_group_id
AND bgr.org_information_context = ''Business Group Information''
' || l_security_clause || '
&ORDER_BY_CLAUSE ';