[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_GAIN_HIRE_SUPJOB
Source
1 PACKAGE BODY hri_oltp_pmv_gain_hire_supjob AS
2 /* $Header: hriopgh.pkb 120.2 2005/07/20 02:08:16 cbridge noship $ */
3 --
4 PROCEDURE get_sql2(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
5 x_custom_sql OUT NOCOPY VARCHAR2,
6 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
7
8 l_sql_text VARCHAR2(32000);
9 l_security_clause VARCHAR2(4000);
10 l_custom_rec BIS_QUERY_ATTRIBUTES;
11
12 /* Parameter values */
13 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
14 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
15
16 BEGIN
17
18 /* Initialize out parameters */
19 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
20 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
21
22 /* Get common parameter values */
23 hri_oltp_pmv_util_param.get_parameters_from_table
24 (p_page_parameter_tbl => p_page_parameter_tbl,
25 p_parameter_rec => l_parameter_rec,
26 p_bind_tab => l_bind_tab);
27
28 /* Get security clause for Manager based security */
29 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
30
31 /* Formulate query SQL */
32 l_sql_text :=
33 'SELECT -- Hires Salary Variance Top 10
34 rnk.diff_rnk HRI_P_ORDER_BY_1
35 ,hri_bpl_job.get_job_display_name
36 (job.id
37 ,job.business_group_id
38 ,job.value) || '' ('' || bgr.org_information9 || '')''
39 HRI_P_JOB_CN
40 ,rnk.new_hire_hdc HRI_P_MEASURE1
41 ,rnk.new_hire_avg_sal HRI_P_MEASURE2
42 ,rnk.curr_emp_avg_sal HRI_P_MEASURE3
43 ,rnk.diff_pct HRI_P_MEASURE4
44 FROM
45 hri_dbi_cl_job_n_v job
46 ,hr_organization_information bgr
47 ,(SELECT
48 diff.job_id
49 ,diff.new_hire_hdc
50 ,diff.new_hire_sal
51 ,diff.new_hire_avg_sal
52 ,diff.curr_emp_hdc
53 ,diff.curr_emp_sal
54 ,diff.curr_emp_avg_sal
55 ,diff.diff_pct
56 ,RANK() OVER (ORDER BY ABS(diff.diff_pct) DESC, diff.curr_emp_sal, diff.job_id) diff_rnk
57 FROM
58
59 (SELECT
60 averages.job_id job_id
61 ,averages.new_hire_hdc new_hire_hdc
62 ,averages.new_hire_sal new_hire_sal
63 ,averages.new_hire_avg_sal new_hire_avg_sal
64 ,averages.curr_emp_hdc curr_emp_hdc
65 ,averages.curr_emp_sal curr_emp_sal
66 ,averages.curr_emp_avg_sal curr_emp_avg_sal
67 ,DECODE(averages.curr_emp_avg_sal,
68 0, 0,
69 (averages.new_hire_avg_sal - averages.curr_emp_avg_sal) * 100 /
70 averages.curr_emp_avg_sal) diff_pct
71 FROM
72 (SELECT
73 hire_sal.job_id job_id
74 ,hire_sal.new_hire_hdc new_hire_hdc
75 ,hire_sal.new_hire_sal new_hire_sal
76 ,DECODE(hire_sal.new_hire_hdc, 0, 0, hire_sal.new_hire_sal / hire_sal.new_hire_hdc)
77 new_hire_avg_sal
78 ,curr_emp.total_headcount - hire_sal.new_hire_hdc
79 curr_emp_hdc
80 ,curr_emp.total_salary - hire_sal.new_hire_sal
81 curr_emp_sal
82 ,DECODE(curr_emp.total_headcount - hire_sal.new_hire_hdc,
83 0,TO_NUMBER(NULL),
84 (curr_emp.total_salary - hire_sal.new_hire_sal) /
85 (curr_emp.total_headcount - hire_sal.new_hire_hdc))
86 curr_emp_avg_sal
87 FROM
88 (SELECT /*+ NO_MERGE */
89 sub_job.job_id
90 ,SUM(sub_job.total_headcount) total_headcount
91 ,SUM(DECODE(sub_job.anl_slry_currency,
92 :GLOBAL_CURRENCY, sub_job.total_anl_slry,
93 hri_bpl_currency.convert_currency_amount
94 (sub_job.anl_slry_currency
95 ,:GLOBAL_CURRENCY
96 ,&BIS_CURRENT_EFFECTIVE_END_DATE
97 ,sub_job.total_anl_slry
98 ,:GLOBAL_RATE))) total_salary
99 FROM
100 hri_mdp_sup_wrkfc_job_mv sub_job
101 WHERE sub_job.supervisor_person_id = &HRI_PERSON+HRI_PER_USRDR_H
102 AND sub_job.anl_slry_currency <> ''NA_EDW''
103 AND &BIS_CURRENT_EFFECTIVE_END_DATE BETWEEN effective_start_date
104 AND effective_end_date
105 AND job_id <> -1
106 GROUP BY
107 sub_job.job_id) curr_emp
108 ,(SELECT /*+ NO_MERGE */
109 hire.job_id job_id
110 ,SUM(hire.headcount_value) new_hire_hdc
111 ,SUM(DECODE(hire.currency,
112 :GLOBAL_CURRENCY, hire.salary,
113 hri_bpl_currency.convert_currency_amount
114 (hire.currency
115 ,:GLOBAL_CURRENCY
116 ,&BIS_CURRENT_EFFECTIVE_END_DATE
117 ,hire.salary
118 ,:GLOBAL_RATE))) new_hire_sal
119 FROM
120 hri_mdp_sup_gain_hire_mv hire
121 WHERE hire.supervisor_id = &HRI_PERSON+HRI_PER_USRDR_H
122 AND hire.effective_date BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
123 AND &BIS_CURRENT_EFFECTIVE_END_DATE
124 GROUP BY
125 hire.job_id) hire_sal
126 WHERE curr_emp.job_id = hire_sal.job_id
127 AND hire_sal.new_hire_hdc > 0
128 ) averages
129 ) diff
130 ) rnk
131 WHERE rnk.job_id = job.id
132 AND rnk.diff_rnk <= 10
133 AND bgr.organization_id = job.business_group_id
134 AND bgr.org_information_context = ''Business Group Information''
135 ' || l_security_clause || '
136 &ORDER_BY_CLAUSE ';
137 --
138 x_custom_sql := l_SQL_Text;
139
140 l_custom_rec.attribute_name := ':GLOBAL_CURRENCY';
141 l_custom_rec.attribute_value := l_parameter_rec.currency_code;
142 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
143 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
144 x_custom_output.extend;
145 x_custom_output(1) := l_custom_rec;
146
147 l_custom_rec.attribute_name := ':GLOBAL_RATE';
148 l_custom_rec.attribute_value := l_parameter_rec.rate_type;
149 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
150 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
151 x_custom_output.extend;
152 x_custom_output(2) := l_custom_rec;
153 --
154 END get_sql2;
155 --
156 END hri_oltp_pmv_gain_hire_supjob;