[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_WMV_HR_CTR
Source
1 PACKAGE BODY hri_oltp_pmv_wmv_hr_ctr AS
2 /* $Header: hriopwhc.pkb 120.7 2006/01/11 06:29:52 jrstewar noship $ */
3
4 g_rtn VARCHAR2(30) := '
5 ';
6
7 -- *********************************
8 -- * AK SQL For HR Staff Ratio KPI *
9 -- * AK Region : HRI_K_WMV_HR *
10 -- *********************************
11 --
12 PROCEDURE get_kpi
13 (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
14 x_custom_sql OUT NOCOPY VARCHAR2,
15 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
16
17 l_SQLText VARCHAR2(32000);
18 l_security_clause VARCHAR2(4000);
19 l_custom_rec BIS_QUERY_ATTRIBUTES ;
20
21 /* Parameter values */
22 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
23 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
24
25 /* Dynamic SQL controls */
26 l_wrkfc_fact_params hri_bpl_fact_sup_wrkfc_sql.wrkfc_fact_param_type;
27 l_wrkfc_fact_sql VARCHAR2(10000);
28
29 BEGIN
30
31 /* Initialize out parameters */
32 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
33 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
34
35 /* Get common parameter values */
36 hri_oltp_pmv_util_param.get_parameters_from_table
37 (p_page_parameter_tbl => p_page_parameter_tbl,
38 p_parameter_rec => l_parameter_rec,
39 p_bind_tab => l_bind_tab);
40
41 /* Force view by manager */
42 l_parameter_rec.view_by := 'HRI_PERSON+HRI_PER_USRDR_H';
43
44 /* Force usage of CHO: Named User Profile Option as the Manager bind */
45 l_bind_tab('HRI_PERSON+HRI_PER_USRDR_H').pmv_bind_string :=
46 'NVL(hri_bpl_security.get_apps_signin_person_id, -1)';
47
48 /* Get SQL for workforce fact */
49 l_wrkfc_fact_params.bind_format := 'PMV';
50 l_wrkfc_fact_params.include_comp := 'Y';
51 l_wrkfc_fact_params.include_hdc := 'Y';
52 l_wrkfc_fact_params.kpi_mode := 'Y';
53 l_wrkfc_fact_params.bucket_dim := 'JOB+PRIMARY_JOB_ROLE';
54 l_wrkfc_fact_sql := hri_bpl_fact_sup_wrkfc_sql.get_sql
55 (p_parameter_rec => l_parameter_rec,
56 p_bind_tab => l_bind_tab,
57 p_wrkfc_params => l_wrkfc_fact_params,
58 p_calling_module => 'HRI_OLTP_PMV_WMV_HR_CTR_SUP.GET_KPI');
59
60 l_SQLText :=
61 'SELECT -- HR Staff KPIs
62 vby.id VIEWBYID
63 ,vby.value VIEWBY
64 ,tab.curr_hdc_end HRI_P_MEASURE1
65 ,tab.comp_hdc_end HRI_P_MEASURE2
66 ,tab.curr_hdc_hr HRI_P_MEASURE3
67 ,tab.comp_hdc_hr HRI_P_MEASURE4
68 ,DECODE(tab.curr_hdc_hr,
69 0, to_number(null),
70 tab.curr_hdc_end / tab.curr_hdc_hr)
71 HRI_P_MEASURE5
72 ,DECODE(tab.comp_hdc_hr,
73 0, to_number(null),
74 tab.comp_hdc_end / tab.comp_hdc_hr)
75 HRI_P_MEASURE6
76 ,tab.curr_hdc_end HRI_P_GRAND_TOTAL1
77 ,tab.comp_hdc_end HRI_P_GRAND_TOTAL2
78 ,tab.curr_hdc_hr HRI_P_GRAND_TOTAL3
79 ,tab.comp_hdc_hr HRI_P_GRAND_TOTAL4
80 ,DECODE(tab.curr_hdc_hr,
81 0, to_number(null),
82 tab.curr_hdc_end / tab.curr_hdc_hr)
83 HRI_P_GRAND_TOTAL5
84 ,DECODE(tab.comp_hdc_hr,
85 0, to_number(null),
86 tab.comp_total_hdc_end / tab.comp_hdc_hr)
87 HRI_P_GRAND_TOTAL6
88 FROM
89 hri_dbi_cl_per_n_v vby
90 ,(' || l_wrkfc_fact_sql || ') tab
91 WHERE tab.vby_id = vby.id
92 AND &BIS_CURRENT_ASOF_DATE BETWEEN vby.effective_start_date
93 AND vby.effective_end_date ';
94
95 x_custom_sql := l_SQLText;
96
97 END GET_KPI;
98
99
100 --* AK SQL For HR Staff Ratio by Country *
101 --* AK Region : HRI_OLTP_PMV_WMV_HR_CTR_SUP *
102 --****************************************************************************
103 --
104 PROCEDURE GET_SQL2(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
105 ,x_custom_sql OUT NOCOPY VARCHAR2
106 ,x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
107
108 l_SQLText VARCHAR2(32000);
109 l_security_clause VARCHAR2(4000);
110 l_custom_rec BIS_QUERY_ATTRIBUTES ;
111
112 /* Parameter values */
113 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
114 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
115
116 /* Pre-calculations */
117 l_drill_url VARCHAR2(1000);
118
119 /* translation values */
120 l_others_string VARCHAR2(80);
121
122 /* Dynamic SQL controls */
123 l_wrkfc_fact_params hri_bpl_fact_sup_wrkfc_sql.wrkfc_fact_param_type;
124 l_wrkfc_fact_sql VARCHAR2(10000);
125
126 BEGIN
127 /* Initialize out parameters */
128 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
129 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
130
131 /* Get common parameter values */
132 hri_oltp_pmv_util_param.get_parameters_from_table
133 (p_page_parameter_tbl => p_page_parameter_tbl,
134 p_parameter_rec => l_parameter_rec,
135 p_bind_tab => l_bind_tab);
136
137 /* Translate 'others' string */
138 l_others_string := hri_oltp_view_message.get_others_msg;
139
140 /* Set l_drill_url to null to turn off drill to regions report */
141 l_drill_url := '' ;
142
143 /* Force view by country */
144 l_parameter_rec.view_by := 'GEOGRAPHY+COUNTRY';
145
146 /* Force usage of CHO: Named User Profile Option as the Manager bind */
147 l_bind_tab('HRI_PERSON+HRI_PER_USRDR_H').pmv_bind_string :=
148 'NVL(hri_bpl_security.get_apps_signin_person_id, -1)';
149
150 /* Get SQL for workforce fact */
151 l_wrkfc_fact_params.bind_format := 'PMV';
152 l_wrkfc_fact_params.include_comp := 'Y';
153 l_wrkfc_fact_params.include_hdc := 'Y';
154 l_wrkfc_fact_params.bucket_dim := 'JOB+PRIMARY_JOB_ROLE';
155 l_wrkfc_fact_sql := hri_bpl_fact_sup_wrkfc_sql.get_sql
156 (p_parameter_rec => l_parameter_rec,
157 p_bind_tab => l_bind_tab,
158 p_wrkfc_params => l_wrkfc_fact_params,
159 p_calling_module => 'HRI_OLTP_PMV_WMV_HR_CTR_SUP.GET_SQL2');
160
161 l_SQLText :=
162 'SELECT -- HR Staff Ratio by Country
163 vby.value HRI_P_GEO_CTY_CN
164 ,tab.curr_hdc_end HRI_P_MEASURE1
165 ,tab.comp_hdc_end HRI_P_MEASURE2
166 ,tab.curr_hdc_hr HRI_P_MEASURE3
167 ,tab.comp_hdc_hr HRI_P_MEASURE4
168 ,ROUND(DECODE(tab.curr_hdc_hr,
169 0, to_number(null),
170 tab.curr_hdc_end / tab.curr_hdc_hr),0)
171 HRI_P_MEASURE5
172 ,DECODE(tab.comp_hdc_hr,
173 0, to_number(null),
174 tab.comp_hdc_end / tab.comp_hdc_hr)
175 HRI_P_MEASURE6
176 ,SUM(tab.curr_hdc_end) OVER () HRI_P_GRAND_TOTAL1
177 ,tab.comp_total_hdc_end HRI_P_GRAND_TOTAL2
178 ,SUM(tab.curr_hdc_hr) OVER () HRI_P_GRAND_TOTAL3
179 ,tab.comp_total_hdc_hr HRI_P_GRAND_TOTAL4
180 ,DECODE(SUM(tab.curr_hdc_hr) OVER (),
181 0, to_number(null),
182 SUM(tab.curr_hdc_end) OVER () / SUM(tab.curr_hdc_hr) OVER ())
183 HRI_P_GRAND_TOTAL5
184 ,DECODE(SUM(tab.comp_total_hdc_hr) OVER (),
185 0, to_number(null),
186 SUM(tab.comp_total_hdc_end) OVER () / SUM(tab.comp_total_hdc_hr) OVER ())
187 HRI_P_GRAND_TOTAL6
188 ,0 - tab.curr_hdc_end HRI_P_ORDER_BY_1
189 ,tab.vby_id HRI_P_CHAR1_GA
190 FROM
191 hri_dbi_cl_geo_country_v vby
192 ,(' || l_wrkfc_fact_sql || ') tab
193 WHERE vby.id = tab.vby_id
194 ORDER BY ' || hri_oltp_pmv_util_pkg.set_default_order_by
195 (p_order_by_clause => l_parameter_rec.order_by);
196
197 x_custom_sql := '-- View by: ' || l_parameter_rec.view_by || g_rtn || l_SQLText;
198
199 END get_sql2;
200
201
202 END hri_oltp_pmv_wmv_hr_ctr;