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