eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.HRI_OLTP_PMV_TRN_BCKT_GRAPH

Source


1 PACKAGE BODY hri_oltp_pmv_trn_bckt_graph AS
2 /* $Header: hrioptbg.pkb 120.1 2005/10/26 07:56:40 jrstewar noship $ */
3 
4 g_rtn   VARCHAR2(30) := '
5 ';
6 
7 PROCEDURE get_sql_bckt_perf
8        (p_page_parameter_tbl  IN  BIS_PMV_PAGE_PARAMETER_TBL,
9         x_custom_sql          OUT NOCOPY VARCHAR2,
10         x_custom_output       OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
11 
12   l_custom_rec         BIS_QUERY_ATTRIBUTES;
13   l_security_clause    VARCHAR2(4000);
14   l_SQLText            VARCHAR2(4000);
15 
16 /* Parameter values */
17   l_parameter_rec         hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
18   l_bind_tab              hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
19 
20 /* Pre-calculations */
21   l_trend_table        VARCHAR2(4000);
22   l_projection_periods NUMBER;
23   l_previous_periods   NUMBER;
24 
25 /* Dynamic SQL Controls */
26   l_trend_sql_params   hri_oltp_pmv_query_trend.trend_sql_params_type;
27   l_trend_sql          VARCHAR2(10000);
28 
29 BEGIN
30 
31 /* Initialize table/record variables */
32   l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
33   x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
34 
35 /* Get security clause for Manager based security */
36   l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
37 
38 /* Get common parameter values */
39   hri_oltp_pmv_util_param.get_parameters_from_table
40         (p_page_parameter_tbl  => p_page_parameter_tbl,
41          p_parameter_rec       => l_parameter_rec,
42          p_bind_tab            => l_bind_tab);
43 
44 /* Get the number of periods to display */
45   hri_oltp_pmv_query_time.get_period_binds
46    (p_page_period_type => l_parameter_rec.page_period_type
47    ,p_page_comp_type => l_parameter_rec.time_comparison_type
48    ,o_previous_periods => l_previous_periods
49    ,o_projection_periods => l_projection_periods);
50 
51 /* Get the trend sql */
52   l_trend_sql_params.bind_format  := 'PMV';
53   l_trend_sql_params.include_sep  := 'Y';
54   l_trend_sql_params.bucket_dim   := 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X';
55   l_trend_sql := hri_oltp_pmv_query_trend.get_sql
56                   (p_parameter_rec => l_parameter_rec,
57                    p_bind_tab => l_bind_tab,
58                    p_trend_sql_params => l_trend_sql_params,
59                    p_calling_module => 'HRI_OLTP_PMV_TRN_BCKT_GRAPH.GET_SQL_BCKT_PERF');
60 
61 l_SQLText :=
62 'SELECT -- Turnover with Performance Band Trend
63  qry.period_as_of_date  VIEWBYID
64 ,qry.period_as_of_date  VIEWBY
65 ,qry.period_order       HRI_P_ORDER_BY_1
66 ,DECODE(qry.period_sep_hdc, 0, 0,
67         (qry.period_sep_hdc_b3/qry.period_sep_hdc)*100)
68                         HRI_P_MEASURE1
69 ,DECODE(qry.period_sep_hdc, 0, 0,
70         (qry.period_sep_hdc_b2/qry.period_sep_hdc)*100)
71                         HRI_P_MEASURE2
72 ,DECODE(qry.period_sep_hdc, 0, 0,
73         (qry.period_sep_hdc_b1/qry.period_sep_hdc)*100)
74                         HRI_P_MEASURE3
75 ,DECODE(qry.period_sep_hdc, 0, 0,
76         (qry.period_sep_hdc_na/qry.period_sep_hdc)*100)
77                         HRI_P_MEASURE4
78 ,to_char(qry.period_as_of_date, ''DD/MM/YYYY'')
79                         HRI_P_CHAR1_GA
80 FROM
81  (' || l_trend_sql || ')  qry
82 WHERE 1=1
83 ' || l_security_clause || '
84 ORDER BY qry.period_order';
85 
86   x_custom_sql := l_sqltext;
87 
88   l_custom_rec.attribute_name := ':TIME_PERIOD_TYPE';
89   l_custom_rec.attribute_value := l_parameter_rec.page_period_type;
90   l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
91   l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
92   x_custom_output.extend;
93   x_custom_output(1) := l_custom_rec;
94 
95   l_custom_rec.attribute_name := ':TIME_COMPARISON_TYPE';
96   l_custom_rec.attribute_value := l_parameter_rec.time_comparison_type;
97   l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
98   l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
99   x_custom_output.extend;
100   x_custom_output(2) := l_custom_rec;
101 
102   l_custom_rec.attribute_name := ':TIME_PERIOD_NUMBER';
103   l_custom_rec.attribute_value := l_previous_periods;
104   l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
105   l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
106   x_custom_output.extend;
107   x_custom_output(3) := l_custom_rec;
108 
109 END get_sql_bckt_perf;
110 
111 PROCEDURE get_sql_rnk_jfn_graph
112      (p_page_parameter_tbl  IN BIS_PMV_PAGE_PARAMETER_TBL,
113       x_custom_sql          OUT NOCOPY VARCHAR2,
114       x_custom_output       OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
115 
116   l_SQLText               VARCHAR2(32767) ;
117   l_custom_rec            BIS_QUERY_ATTRIBUTES;
118   l_security_clause           VARCHAR2(4000);
119 
120 /* Parameter values */
121   l_parameter_rec         hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
122   l_bind_tab              hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
123 
124 /* Dynamic SQL Controls */
125   l_wcnt_chg_fact_params hri_bpl_fact_sup_wcnt_chg_sql.wcnt_chg_fact_param_type;
126   l_wcnt_chg_fact_sql    VARCHAR2(10000);
127 
128 /* Drill URLs */
129   l_drill_url1           VARCHAR2(1000);
130 
131 BEGIN
132 
133 /* Initialize out parameters */
134   l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
135   x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
136 
137 /* Get common parameter values */
138   hri_oltp_pmv_util_param.get_parameters_from_table
139         (p_page_parameter_tbl  => p_page_parameter_tbl,
140          p_parameter_rec       => l_parameter_rec,
141          p_bind_tab            => l_bind_tab);
142 
143 /* Get security clause for Manager based security */
144   l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
145 
146 /* Setup any drill url[s] */
147   l_drill_url1 := 'pFunctionName=HRI_P_WMV_TRN_SUP_DTL&' ||
148                   'VIEW_BY_NAME=VIEW_BY_ID&' ||
149                   'pParamIds=Y';
150 
151 /* Get SQL for workforce changes fact */
152   l_wcnt_chg_fact_params.bind_format := 'PMV';
153   l_wcnt_chg_fact_params.include_comp := 'Y';
154   l_wcnt_chg_fact_params.include_sep := 'Y';
155   l_wcnt_chg_fact_sql := hri_bpl_fact_sup_wcnt_chg_sql.get_sql
156    (p_parameter_rec   => l_parameter_rec,
157     p_bind_tab        => l_bind_tab,
158     p_wcnt_chg_params => l_wcnt_chg_fact_params,
159     p_calling_module => 'HRI_OLTP_PMV_TRN_BCKT_GRAPH.GET_SQL_RNK_JFN_GRAPH');
160 
161 /* Return AK Sql To PMV */
162  l_SQLText    :=
163 'SELECT -- Terminations by Job Function (Top 5)
164  qry.vby_id                 VIEWBYID
165 ,DECODE(qry.grp_id,
166           ''NA_OTHERS'', ''' || hri_oltp_view_message.get_others_msg || ''',
167         cl.value)           VIEWBY
168 ,qry.curr_separation_hdc   HRI_P_MEASURE1
169 ,DECODE(qry.grp_id, ''NA_OTHERS'', '''', ''' || l_drill_url1 || ''')
170                             HRI_P_DRILL_URL1
171 ,qry.comp_separation_hdc   HRI_P_MEASURE2
172 ,DECODE(qry.comp_separation_hdc, 0, NULL,
173         100 * (qry.curr_separation_hdc - qry.comp_separation_hdc) /
174          qry.comp_separation_hdc)
175                             HRI_P_MEASURE1_MP
176 FROM
177  ' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
178         (l_parameter_rec.view_by).viewby_table || ' cl
179 ,(SELECT' || g_rtn ||
180 /* Bug 4068969 - added grp_id and corrected logic for vby_id and order_by */
181 '   DECODE(SIGN(:HRI_NO_SEGMENTS_TO_SHOW - rnk),
182             -1, :HRI_NO_SEGMENTS_TO_SHOW + 1,
183           rnk)         order_by
184   ,DECODE(SIGN(:HRI_NO_SEGMENTS_TO_SHOW - rnk),
185             -1, ''NA_EDW'',
186           rnked_metrics.vby_id)   vby_id
187   ,DECODE(SIGN(:HRI_NO_SEGMENTS_TO_SHOW - rnk),
188             -1, ''NA_OTHERS'',
189           rnked_metrics.vby_id)   grp_id
190   ,SUM(rnked_metrics.curr_separation_hdc)  curr_separation_hdc
191   ,SUM(rnked_metrics.comp_separation_hdc)  comp_separation_hdc
192   FROM
193    (SELECT
194      cube.vby_id
195     ,cube.curr_separation_hdc
196     ,cube.comp_separation_hdc' || g_rtn ||
197 /* Bug 3068969 - Order by descending terminations (curr and prev) to prevent */
198 /* outermost filter removing ranked lines and still displaying OTHERS */
199 '    ,RANK() OVER (ORDER BY cube.curr_separation_hdc  DESC NULLS LAST,
200                            cube.comp_separation_hdc  DESC NULLS LAST,
201                            cube.vby_id) AS RNK
202     FROM
203      (' || l_wcnt_chg_fact_sql || ') cube
204    ) rnked_metrics
205   GROUP BY
206    DECODE(SIGN(:HRI_NO_SEGMENTS_TO_SHOW - rnk),
207             -1, :HRI_NO_SEGMENTS_TO_SHOW + 1,
208           rnk)
209   ,DECODE(SIGN(:HRI_NO_SEGMENTS_TO_SHOW - rnk),
210             -1, ''NA_EDW'',
211           rnked_metrics.vby_id)
212   ,DECODE(SIGN(:HRI_NO_SEGMENTS_TO_SHOW - rnk),
213             -1, ''NA_OTHERS'',
214           rnked_metrics.vby_id)
215  ) qry
216 WHERE qry.vby_id = cl.id
217 AND (qry.curr_separation_hdc + qry.comp_separation_hdc) > 0 ' || g_rtn
218 || l_security_clause || g_rtn
219 || 'ORDER BY qry.order_by';
220 
221  x_custom_sql := l_SQLText;
222 
223 /* Binds Will be inserted Below */
224 
225   l_custom_rec.attribute_name := ':HRI_NO_SEGMENTS_TO_SHOW';
226   l_custom_rec.attribute_value := 5;
227   l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
228   l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
229   x_custom_output.extend;
230   x_custom_output(1) := l_custom_rec;
231 
232 END get_sql_rnk_jfn_graph;
233 
234 PROCEDURE get_sql_rnk_rsn_graph
235        (p_page_parameter_tbl  IN BIS_PMV_PAGE_PARAMETER_TBL,
236         x_custom_sql          OUT NOCOPY VARCHAR2,
237         x_custom_output       OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
238 
239   l_SQLText              VARCHAR2(32767) ;
240   l_custom_rec           BIS_QUERY_ATTRIBUTES;
241 
242 /* Parameter values */
243   l_parameter_rec        hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
244   l_bind_tab             hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
245 
246   l_security_clause      VARCHAR2(4000);
247 
248   l_drill_url1           VARCHAR2(1000);
249 
250 /* Dynamic SQL Controls */
251   l_wcnt_chg_fact_params hri_bpl_fact_sup_wcnt_chg_sql.wcnt_chg_fact_param_type;
252   l_wcnt_chg_fact_sql    VARCHAR2(10000);
253 
254 BEGIN
255 /* Initialize out parameters */
256   l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
257   x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
258 
259 /* Get common parameter values */
260   hri_oltp_pmv_util_param.get_parameters_from_table
261         (p_page_parameter_tbl  => p_page_parameter_tbl,
262          p_parameter_rec       => l_parameter_rec,
263          p_bind_tab            => l_bind_tab);
264 
265 /* Get security clause for Manager based security */
266   l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
267 
268 /* Hard code voluntary separations only */
269   l_bind_tab('HRI_WRKACTVT+HRI_WAC_SEPCAT_X').pmv_bind_string := '''SEP_VOL''';
270   l_bind_tab('HRI_WRKACTVT+HRI_WAC_SEPCAT_X').sql_bind_string := '''SEP_VOL''';
271 
272 /* Setup any drill url[s] */
273   l_drill_url1 := 'pFunctionName=HRI_P_WMV_TRN_SUP_DTL&' ||
274                    'VIEW_BY_NAME=VIEW_BY_ID&' ||
275                    'HRI_P_WAC_SEPCAT_CN=SEP_VOL&' ||
276                    'pParamIds=Y';
277 
278 /* Set view by to leaving reason */
279   l_parameter_rec.view_by := 'HRI_REASON+HRI_RSN_SEP_X';
280 
281 /* Get SQL for workforce changes fact */
282   l_wcnt_chg_fact_params.bind_format := 'PMV';
283   l_wcnt_chg_fact_params.include_comp := 'Y';
284   l_wcnt_chg_fact_params.include_sep := 'Y';
285   l_wcnt_chg_fact_params.bucket_dim := 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X';
286   l_wcnt_chg_fact_sql := hri_bpl_fact_sup_wcnt_chg_sql.get_sql
287    (p_parameter_rec   => l_parameter_rec,
288     p_bind_tab        => l_bind_tab,
289     p_wcnt_chg_params => l_wcnt_chg_fact_params,
290     p_calling_module  => 'HRI_OLTP_PMV_TRN_BCKT_GRAPH.GET_SQL_RNK_RSN_GRAPH');
291 
292 /* Return AK Sql To PMV */
293  l_SQLText    :=
294 'SELECT
295  -- Terminations by Leaving Reason (Top 5)
296  qry.vby_id                VIEWBYID
297 ,DECODE(qry.grp_id,
298           ''NA_OTHERS'', ''' || hri_oltp_view_message.get_others_msg || ''',
299         cl.value)           VIEWBY
300 ,qry.curr_separation_hdc    HRI_P_MEASURE1
301 ,DECODE(qry.grp_id, ''NA_OTHERS'', '''', ''' || l_drill_url1 ||''')
302                             HRI_P_DRILL_URL1
303 ,qry.comp_separation_hdc    HRI_P_MEASURE2
304 ,DECODE(qry.curr_separation_hdc, 0, NULL,
305         ((qry.curr_separation_hdc - qry.comp_separation_hdc) /
306          qry.curr_separation_hdc) * 100)
307                             HRI_P_MEASURE1_MP
308 FROM
309  ' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
310        (l_parameter_rec.view_by).viewby_table || '  cl
311 ,(SELECT' || g_rtn ||
312 /* Bug 4068969 - added grp_id and corrected logic for vby_id and order_by */
313 '   DECODE(SIGN(:HRI_NO_SEGMENTS_TO_SHOW - rnked_metrics.rnk),
314             -1, :HRI_NO_SEGMENTS_TO_SHOW + 1,
315           rnk)                              order_by
316   ,DECODE(SIGN(:HRI_NO_SEGMENTS_TO_SHOW - rnked_metrics.rnk),
317             -1, ''NA_EDW'',
318           rnked_metrics.vby_id)             vby_id
319   ,DECODE(SIGN(:HRI_NO_SEGMENTS_TO_SHOW - rnked_metrics.rnk),
320             -1, ''NA_OTHERS'',
321           rnked_metrics.vby_id)             grp_id
322   ,SUM(rnked_metrics.curr_separation_hdc)  curr_separation_hdc
323   ,SUM(rnked_metrics.comp_separation_hdc)  comp_separation_hdc
324   FROM
325    (SELECT
326      cube.vby_id
327     ,cube.curr_separation_hdc
328     ,cube.comp_separation_hdc' || g_rtn ||
329 /* Bug 3068969 - Order by descending terminations (curr and prev) to prevent */
330 /* outermost filter removing ranked lines and still displaying OTHERS */
331 '    ,RANK() OVER (ORDER BY cube.curr_separation_hdc  DESC NULLS LAST,
332                            cube.comp_separation_hdc  DESC NULLS LAST,
333                            cube.vby_id) AS RNK
334     FROM
335      (' || l_wcnt_chg_fact_sql || ')  cube
336    ) RNKED_METRICS
337   GROUP BY
338    DECODE(SIGN(:HRI_NO_SEGMENTS_TO_SHOW - rnked_metrics.rnk),
339             -1, :HRI_NO_SEGMENTS_TO_SHOW + 1,
340           rnk)
341   ,DECODE(SIGN(:HRI_NO_SEGMENTS_TO_SHOW - rnked_metrics.rnk),
342             -1, ''NA_EDW'',
343           rnked_metrics.vby_id)
344   ,DECODE(SIGN(:HRI_NO_SEGMENTS_TO_SHOW - rnked_metrics.rnk),
345             -1, ''NA_OTHERS'',
346           rnked_metrics.vby_id)
347  ) QRY
348 WHERE qry.vby_id = cl.id
349 AND (qry.curr_separation_hdc + qry.comp_separation_hdc) > 0' || g_rtn
350 || l_security_clause || g_rtn ||
351 'ORDER BY qry.order_by';
352 
353   x_custom_sql := l_SQLText;
354 
355 /* Binds Will be inserted Below */
356 
357   l_custom_rec.attribute_name := ':HRI_NO_SEGMENTS_TO_SHOW';
358   l_custom_rec.attribute_value := 5;
359   l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
360   l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
361   x_custom_output.extend;
362   x_custom_output(1) := l_custom_rec;
363 
364 
365 END get_sql_rnk_rsn_graph;
366 
367 END hri_oltp_pmv_trn_bckt_graph;