[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_WMV_SAL_CTR_SUP
Source
1 PACKAGE BODY HRI_OLTP_PMV_WMV_SAL_CTR_SUP AS
2 /* $Header: hriopwsc.pkb 120.3 2006/01/10 07:22:43 cbridge noship $ */
3
4 g_rtn VARCHAR2(30) := '
5 ';
6
7 --* AK SQL For Headcount and Salary by Country *
8 --* AK Region : HRI_P_WMV_SAL_CTR_SUP *
9 --****************************************************************************
10 --
11 PROCEDURE GET_SQL2(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
12 ,x_custom_sql OUT NOCOPY VARCHAR2
13 ,x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
14
15 l_SQLText VARCHAR2(32000);
16 l_security_clause VARCHAR2(4000);
17 l_custom_rec BIS_QUERY_ATTRIBUTES ;
18
19 /* Parameter values */
20 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
21 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
22
23 /* Pre-calculations */
24 l_drill_url VARCHAR2(1000);
25
26 /* translation values */
27 l_others_string VARCHAR2(80);
28
29 /* Dynamic SQL controls */
30 l_wrkfc_fact_params hri_bpl_fact_sup_wrkfc_sql.wrkfc_fact_param_type;
31 l_wrkfc_fact_sql VARCHAR2(10000);
32
33 BEGIN
34 /* Initialize out parameters */
35 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
36 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
37
38 /* Get security clause for Manager based security */
39 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
40
41 /* Get common parameter values */
42 hri_oltp_pmv_util_param.get_parameters_from_table
43 (p_page_parameter_tbl => p_page_parameter_tbl,
44 p_parameter_rec => l_parameter_rec,
45 p_bind_tab => l_bind_tab);
46
47 /* translate 'others' string */
48 l_others_string := hri_oltp_view_message.get_others_msg;
49
50 /* Set l_drill_url to null to turn off drill to regions report */
51 l_drill_url := '' ; -- bug 3696662, turned off drill url for Oracle GSI.
52
53 -- 'pFunctionName=HRI_P_WMV_SAL_RGN_SUP&' ||
54 -- 'HRI_P_GEO_CTY_CN=HRI_P_GEO_CTY_CN&' ||
55 -- 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
56 -- 'pParamIds=Y';
57
58 /* Force view by country */
59 l_parameter_rec.view_by := 'GEOGRAPHY+COUNTRY';
60
61 /* Get SQL for workforce fact */
62 l_wrkfc_fact_params.bind_format := 'PMV';
63 l_wrkfc_fact_params.include_comp := 'Y';
64 l_wrkfc_fact_params.include_hdc := 'Y';
65 l_wrkfc_fact_params.include_sal := 'Y';
66 l_wrkfc_fact_sql := hri_bpl_fact_sup_wrkfc_sql.get_sql
67 (p_parameter_rec => l_parameter_rec,
68 p_bind_tab => l_bind_tab,
69 p_wrkfc_params => l_wrkfc_fact_params,
70 p_calling_module => 'HRI_OLTP_PMV_WMV_SAL_CTR_SUP.GET_SQL2');
71
72 l_SQLText :=
73 'SELECT -- Headcount and Salary by Country
74 tab.order_by HRI_P_ORDER_BY_1
75 ,DECODE(tab.vby_id,''NA_OTHERS'',''' || l_others_string || ''', ctr.value)
76 HRI_P_MEASURE1
77 ,NVL(tab.wmv_curr,0) HRI_P_WMV_SUM_MV
78 ,NVL(tab.wmv_prev,0) HRI_P_WMV_SUM_PREV_MV
79 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
80 (p_previous_col => 'tab.wmv_prev',
81 p_current_col => 'tab.wmv_curr') || '
82 HRI_P_WMV_CHNG_PCT_SUM_MV
83 ,NVL(tab.sal_curr,0) HRI_P_MEASURE2
84 ,NVL(tab.sal_prev,0) HRI_P_MEASURE3
85 ,tab.avg_sal HRI_P_MEASURE4
86 ,tab.avg_sal_prev HRI_P_MEASURE5
87 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
88 (p_previous_col => 'tab.avg_sal_prev',
89 p_current_col => 'tab.avg_sal') || '
90 HRI_P_MEASURE6
91 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
92 (p_previous_col => 'DECODE(tab.tot_wmv_prev, 0, NULL,
93 tab.tot_sal_prev/tab.tot_wmv_prev)',
94 p_current_col => 'DECODE(SUM(tab.wmv_curr) over(), 0, NULL,
95 (SUM(tab.sal_curr) over() / SUM(tab.wmv_curr) over()))') || '
96 HRI_P_GRAND_TOTAL1
97 ,DECODE(sum(tab.wmv_curr) over(),
98 0, sum(tab.avg_sal) over (),
99 sum(tab.sal_curr) over ()/sum(tab.wmv_curr) over ())
100 HRI_P_GRAND_TOTAL2
101 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
102 (p_previous_col => 'tab.tot_wmv_prev',
103 p_current_col => '(sum(tab.wmv_curr) over ())') || '
104 HRI_P_GRAND_TOTAL3
105 ,DECODE(tab.vby_id,''NA_OTHERS'',''' || l_others_string || ''', tab.country_code) HRI_P_GEO_CTY_CN
106 ,DECODE(tab.vby_id,''NA_OTHERS'',''''
107 , ''' || l_drill_url || ''')
108 HRI_P_CHAR1_GA
109 FROM
110 hri_dbi_cl_geo_country_v ctr
111 ,(SELECT' || g_rtn ||
112 /* Bug 4068969 - Order by rank putting OTHERS group last */
113 ' DECODE(SUM(wmv_curr),
114 NULL, to_number(NULL),
115 DECODE(SIGN(:HRI_NO_COUNTRIES_TO_SHOW - rnk),
116 -1, :HRI_NO_COUNTRIES_TO_SHOW + 1,
117 rnk)) order_by' || g_rtn ||
118 /* Use country name for all except OTHERS group */
119 ' ,DECODE(SIGN(:HRI_NO_COUNTRIES_TO_SHOW - rnk),
120 -1, ''NA_OTHERS'',
121 country) vby_id' || g_rtn ||
122 /* Use unassigned country to join to the country view */
123 ' ,DECODE(SIGN(:HRI_NO_COUNTRIES_TO_SHOW - rnk),
124 -1, ''NA_EDW'',
125 country) country_code
126 ,SUM(wmv_curr) wmv_curr
127 ,SUM(wmv_prev) wmv_prev
128 ,SUM(sal_curr) sal_curr
129 ,SUM(avg_sal) avg_sal
130 ,SUM(sal_prev) sal_prev
131 ,tot_wmv_prev tot_wmv_prev
132 ,MAX(tot_sal_prev) tot_sal_prev
133 ,DECODE(SUM(wmv_prev),
134 0, NULL,
135 SUM(sal_prev) / SUM(wmv_prev))
136 avg_sal_prev
137 FROM
138 (SELECT' || g_rtn ||
139 /* Bug 4068969 - Rank by descending headcount, descending average salary, */
140 /* then ascending country name */
141 ' (RANK() OVER (ORDER BY curr_hdc_end DESC NULLS LAST
142 , bc.vby_id))
143 rnk
144 ,bc.vby_id country
145 ,bc.curr_hdc_end wmv_curr
146 ,bc.comp_hdc_end wmv_prev
147 ,bc.curr_sal_end sal_curr
148 ,DECODE(bc.curr_hdc_end,
149 0, NULL,
150 bc.curr_sal_end / bc.curr_hdc_end)
151 avg_sal
152 ,bc.comp_sal_end sal_prev
153 ,SUM(bc.comp_total_hdc_end) OVER () tot_wmv_prev
154 ,SUM(bc.comp_total_sal_end) OVER () tot_sal_prev
155 FROM
156 (' || l_wrkfc_fact_sql || ') bc
157 WHERE (bc.curr_hdc_end > 0
158 OR bc.comp_hdc_end > 0
159 OR bc.curr_sal_end > 0
160 OR bc.comp_sal_end > 0)
161 ) qry
162 GROUP BY
163 DECODE(SIGN(:HRI_NO_COUNTRIES_TO_SHOW - rnk),
164 -1, :HRI_NO_COUNTRIES_TO_SHOW + 1,
165 rnk)
166 ,DECODE(SIGN(:HRI_NO_COUNTRIES_TO_SHOW - rnk),
167 -1, ''NA_OTHERS'',
168 country)
169 ,DECODE(SIGN(:HRI_NO_COUNTRIES_TO_SHOW - rnk),
170 -1, ''NA_EDW'',
171 country)
172 ,tot_wmv_prev
173 ) tab
174 WHERE tab.country_code = ctr.id
175 ' || l_security_clause || '
176 ORDER BY
177 HRI_P_ORDER_BY_1
178 ,HRI_P_MEASURE4
179 ,HRI_P_MEASURE1';
180
181 x_custom_sql := l_SQLText;
182
183 l_custom_rec.attribute_name := ':HRI_NO_COUNTRIES_TO_SHOW';
184 l_custom_rec.attribute_value := g_no_countries_to_show;
185 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
186 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
187 x_custom_output.extend;
188 x_custom_output(1) := l_custom_rec;
189
190 l_custom_rec.attribute_name := ':GLOBAL_CURRENCY';
191 l_custom_rec.attribute_value := l_parameter_rec.currency_code;
192 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
193 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
194 x_custom_output.extend;
195 x_custom_output(2) := l_custom_rec;
196
197 l_custom_rec.attribute_name := ':GLOBAL_RATE';
198 l_custom_rec.attribute_value := l_parameter_rec.rate_type;
199 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
200 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
201 x_custom_output.extend;
202 x_custom_output(3) := l_custom_rec;
203
204 END get_sql2;
205
206 END HRI_OLTP_PMV_WMV_SAL_CTR_SUP;