[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_WRKFC_TRN_SUMMARY
Source
1 PACKAGE BODY hri_oltp_pmv_wrkfc_trn_summary AS
2 /* $Header: hriopwsm.pkb 120.5 2005/10/26 07:54:31 jrstewar noship $ */
3
4 g_rtn VARCHAR2(5) := '
5 ';
6
7 TYPE dynamic_sql_rec_type IS RECORD
8 (
9 viewby_condition VARCHAR2(100),
10 wrkfc_outer_join VARCHAR2(5),
11 -- Turnover Headcount calculation
12 hdc_trn_col_curr VARCHAR2(1000),
13 hdc_trn_col_comp VARCHAR2(1000),
14 hdc_trn_col_curr_tot VARCHAR2(1000),
15 hdc_trn_col_comp_tot VARCHAR2(1000),
16
17 -- Drill URLs
18 drill_mgr_sup VARCHAR2(1000),
19 drill_to_detail VARCHAR2(1000),
20 drill_trn_pvt VARCHAR2(1000),
21 drill_total_sal VARCHAR2(1000),
22 -- Display row condition
23 display_row_condition VARCHAR2(1000),
24 -- Order by
25 order_by VARCHAR2(1000)
26 );
27
28 /* Dynamically changes report SQL depending on parameters */
29 PROCEDURE set_dynamic_sql
30 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
31 p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
32 p_dynsql_rec OUT NOCOPY dynamic_sql_rec_type) IS
33
34 BEGIN
35
36 /* Set the order by */
37 p_dynsql_rec.order_by := hri_oltp_pmv_util_pkg.set_default_order_by
38 (p_order_by_clause => p_parameter_rec.order_by);
39
40 /* Get the profile value for the turnover calculation */
41 IF fnd_profile.value('HR_TRNVR_CALC_MTHD') = 'WMV_STARTENDAVG' THEN
42 /* Turnover lines are start/end headcount average */
43 p_dynsql_rec.hdc_trn_col_curr :=
44 'NVL((wmv.curr_hdc_end + wmv.curr_hdc_start) / 2, 0)';
45 p_dynsql_rec.hdc_trn_col_comp :=
46 'NVL((wmv.comp_hdc_end + wmv.comp_hdc_start) / 2, 0)';
47 p_dynsql_rec.hdc_trn_col_curr_tot :=
48 '(wmv.curr_hdc_end + wmv.curr_total_hdc_start) / 2';
49 p_dynsql_rec.hdc_trn_col_comp_tot :=
50 '(wmv.comp_total_hdc_end + wmv.comp_total_hdc_start) / 2';
51 ELSE
52 p_dynsql_rec.hdc_trn_col_curr := 'NVL(wmv.curr_hdc_end, 0)';
53 p_dynsql_rec.hdc_trn_col_comp := 'NVL(wmv.comp_hdc_end, 0)';
54 p_dynsql_rec.hdc_trn_col_curr_tot := 'wmv.curr_hdc_end';
55 p_dynsql_rec.hdc_trn_col_comp_tot := 'wmv.comp_total_hdc_end';
56 END IF;
57
58
59 /* Set drill URLs */
60 p_dynsql_rec.drill_trn_pvt := 'pFunctionName=HRI_P_WAC_TRN_PVT&' ||
61 'VIEW_BY=VIEW_BY_NAME&' ||
62 'VIEW_BY_NAME=VIEW_BY_ID&' ||
63 'pParamIds=Y';
64
65 p_dynsql_rec.drill_to_detail := 'pFunctionName=HRI_P_WMV_SAL_SUP_DTL&' ||
66 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
67 'VIEW_BY_NAME=VIEW_BY_ID&' ||
68 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&' ||
69 'pParamIds=Y';
70
71 IF (p_parameter_rec.view_by = 'HRI_PERSON+HRI_PER_USRDR_H') THEN
72 p_dynsql_rec.drill_mgr_sup := 'pFunctionName=HRI_P_WRKFC_TRN_SUMMARY_PVT&' ||
73 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
74 'VIEW_BY_NAME=VIEW_BY_ID&' ||
75 'pParamIds=Y';
76 p_dynsql_rec.drill_total_sal := 'pFunctionName=HRI_P_WMV_SAL_JFM_SUP&' ||
77 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
78 'VIEW_BY_NAME=VIEW_BY_ID&' ||
79 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&' ||
80 'pParamIds=Y';
81 END IF;
82
83 /* Set the display row conditions */
84 IF (p_parameter_rec.view_by = 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X' OR
85 p_parameter_rec.view_by = 'HRI_LOW+HRI_LOW_BAND_X') THEN
86
87 /* If view by is performance or length of work display all cl view rows */
88 /* regardless of whether there is any headcount or turnover */
89 p_dynsql_rec.wrkfc_outer_join := '(+)';
90
91 /* Filter if a view by parameter is set */
92 p_dynsql_rec.viewby_condition := hri_oltp_pmv_util_pkg.set_viewby_filter
93 (p_parameter_rec => p_parameter_rec,
94 p_bind_tab => p_bind_tab,
95 p_view_by_alias => 'vby');
96
97 ELSE
98
99 /* If Staff Summary by Manager */
100 IF (p_parameter_rec.bis_region_code = 'HRI_P_WRKFC_TRN_SUMMARY') THEN
101
102 /* Only display rows with headcount, salary or turnover current */
103 p_dynsql_rec.display_row_condition :=
104 'AND (a.curr_hdc_end > 0
105 OR a.curr_sal_end > 0
106 OR a.curr_trn_vol > 0
107 OR a.curr_trn_inv > 0
108 OR a.direct_ind = 1)' || g_rtn;
109
110 /* Staff Summary Status */
111 ELSE
112
113 /* Only display rows with headcount, salary or turnover current or change */
114 p_dynsql_rec.display_row_condition :=
115 'AND (a.curr_hdc_end > 0
116 OR a.curr_sal_end > 0
117 OR a.comp_hdc_end > 0
118 OR a.comp_sal_end > 0
119 OR a.curr_trn_vol > 0
120 OR a.curr_trn_inv > 0
121 OR a.comp_trn_vol > 0
122 OR a.comp_trn_inv > 0
123 OR a.direct_ind = 1)' || g_rtn;
124
125 END IF;
126
127 END IF;
128
129 /* Set any additional viewby conditions */
130 IF (p_parameter_rec.view_by = 'HRI_PERSON+HRI_PER_USRDR_H') THEN
131 p_dynsql_rec.viewby_condition :=
132 'AND &BIS_CURRENT_ASOF_DATE BETWEEN vby.start_date AND vby.end_date' || g_rtn;
133 END IF;
134
135 END set_dynamic_sql;
136
137
138 /* Entry point for staff summary pivot report SQL */
139 PROCEDURE get_sql_pvt
140 (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
141 x_custom_sql OUT NOCOPY VARCHAR2,
142 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
143
144 l_sqltext VARCHAR2(32767);
145 l_custom_rec BIS_QUERY_ATTRIBUTES;
146 l_security_clause VARCHAR2(4000);
147 l_direct_reports_string VARCHAR2(100);
148
149 /* Dynamic SQL support */
150 l_dynsql_rec dynamic_sql_rec_type;
151
152 /* Dynamic SQL Controls */
153 l_wrkfc_fact_params hri_bpl_fact_sup_wrkfc_sql.wrkfc_fact_param_type;
154 l_wrkfc_fact_sql VARCHAR2(10000);
155 l_wcnt_chg_fact_params hri_bpl_fact_sup_wcnt_chg_sql.wcnt_chg_fact_param_type;
156 l_wcnt_chg_fact_sql VARCHAR2(10000);
157
158 /* Parameter values */
159 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
160 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
161
162 /* Annualization factor for period type parameter */
163 l_calc_anl_factor NUMBER;
164
165 /* Pre-calculations for turnover total */
166 l_curr_term_vol NUMBER;
167 l_curr_term_invol NUMBER;
168 l_curr_term NUMBER;
169 l_comp_term_vol NUMBER;
170 l_comp_term_invol NUMBER;
171 l_comp_term NUMBER;
172
173 BEGIN
174
175 /* Initialize out parameters */
176 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
177 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
178
179 /* Get common parameter values */
180 hri_oltp_pmv_util_param.get_parameters_from_table
181 (p_page_parameter_tbl => p_page_parameter_tbl,
182 p_parameter_rec => l_parameter_rec,
183 p_bind_tab => l_bind_tab);
184
185 /* Get the annualization factor for the different periods */
186 l_calc_anl_factor := hri_oltp_pmv_util_pkg.calc_anl_factor
187 (p_period_type => l_parameter_rec.page_period_type);
188
189 /* Set the dynamic sql portion */
190 set_dynamic_sql(p_parameter_rec => l_parameter_rec,
191 p_bind_tab => l_bind_tab,
192 p_dynsql_rec => l_dynsql_rec);
193
194 /* Get security clause for Manager based security */
195 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
196
197 /* Get direct reports string */
198 l_direct_reports_string := hri_oltp_view_message.get_direct_reports_msg;
199
200 /* Get the turnover total by calling the supervisor-only total function */
201 /* for the portlet or the all-parameter-pivot total function for the */
202 /* pivot report - bug 4211177 */
203 IF (l_parameter_rec.bis_region_code = 'HRI_P_WRKFC_TRN_SUMMARY') THEN
204
205 /* Get current period turnover totals for supervisor from cursor */
206 hri_bpl_dbi_calc_period.calc_sup_turnover
207 (p_supervisor_id => l_parameter_rec.peo_supervisor_id,
208 p_from_date => l_parameter_rec.time_curr_start_date,
209 p_to_date => l_parameter_rec.time_curr_end_date,
210 p_period_type => l_parameter_rec.page_period_type,
211 p_comparison_type => l_parameter_rec.time_comparison_type,
212 p_total_type => 'ROLLUP',
213 p_wkth_wktyp_sk_fk => l_parameter_rec.wkth_wktyp_sk_fk,
214 p_total_trn_vol => l_curr_term_vol,
215 p_total_trn_invol => l_curr_term_invol);
216
217 /* Get previous period turnover totals for supervisor from cursor */
218 hri_bpl_dbi_calc_period.calc_sup_turnover
219 (p_supervisor_id => l_parameter_rec.peo_supervisor_id,
220 p_from_date => l_parameter_rec.time_comp_start_date,
221 p_to_date => l_parameter_rec.time_comp_end_date,
222 p_period_type => l_parameter_rec.page_period_type,
223 p_comparison_type => l_parameter_rec.time_comparison_type,
224 p_total_type => 'ROLLUP',
225 p_wkth_wktyp_sk_fk => l_parameter_rec.wkth_wktyp_sk_fk,
226 p_total_trn_vol => l_comp_term_vol,
227 p_total_trn_invol => l_comp_term_invol);
228
229 ELSE
230
231 /* Get current period turnover totals for supervisor from cursor */
232 hri_bpl_dbi_calc_period.calc_sup_term_pvt
233 (p_supervisor_id => l_parameter_rec.peo_supervisor_id,
234 p_from_date => l_parameter_rec.time_curr_start_date,
235 p_to_date => l_parameter_rec.time_curr_end_date,
236 p_bind_tab => l_bind_tab,
237 p_total_term_vol => l_curr_term_vol,
238 p_total_term_invol => l_curr_term_invol,
239 p_total_term => l_curr_term);
240
241 /* Get previous period turnover totals for supervisor from cursor */
242 hri_bpl_dbi_calc_period.calc_sup_term_pvt
243 (p_supervisor_id => l_parameter_rec.peo_supervisor_id,
244 p_from_date => l_parameter_rec.time_comp_start_date,
245 p_to_date => l_parameter_rec.time_comp_end_date,
246 p_bind_tab => l_bind_tab,
247 p_total_term_vol => l_comp_term_vol,
248 p_total_term_invol => l_comp_term_invol,
249 p_total_term => l_comp_term);
250
251 END IF;
252
253 /* Get SQL for workforce fact */
254 l_wrkfc_fact_params.bind_format := 'PMV';
255 l_wrkfc_fact_params.include_comp := 'Y';
256 l_wrkfc_fact_params.include_hdc := 'Y';
257 l_wrkfc_fact_params.include_sal := 'Y';
258 l_wrkfc_fact_params.bucket_dim := '';
259 IF (fnd_profile.value('HR_TRNVR_CALC_MTHD') = 'WMV_STARTENDAVG') THEN
260 l_wrkfc_fact_params.include_start := 'Y';
261 END IF;
262 l_wrkfc_fact_sql := hri_bpl_fact_sup_wrkfc_sql.get_sql
263 (p_parameter_rec => l_parameter_rec,
264 p_bind_tab => l_bind_tab,
265 p_wrkfc_params => l_wrkfc_fact_params,
266 p_calling_module => 'HRI_OLTP_PMV_WRKFC_TRN_SUMMARY.GET_SQL_PVT');
267
268 /* Get SQL for workforce changes fact */
269 l_wcnt_chg_fact_params.bind_format := 'PMV';
270 l_wcnt_chg_fact_params.include_comp := 'Y';
271 l_wcnt_chg_fact_params.include_sep := 'Y';
272 l_wcnt_chg_fact_params.include_sep_inv := 'Y';
273 l_wcnt_chg_fact_params.include_sep_vol := 'Y';
274 l_wcnt_chg_fact_params.bucket_dim := '';
275 l_wcnt_chg_fact_sql := hri_bpl_fact_sup_wcnt_chg_sql.get_sql
276 (p_parameter_rec => l_parameter_rec,
277 p_bind_tab => l_bind_tab,
278 p_wcnt_chg_params => l_wcnt_chg_fact_params,
279 p_calling_module => 'HRI_OLTP_PMV_WRKFC_TRN_SUMMARY.GET_SQL_PVT');
280
281 l_sqltext :=
282 'SELECT -- Workforce Summary Portlet (Gen)
283 a.id VIEWBYID
284 ,a.value VIEWBY
285 ,DECODE(a.direct_ind , 0, ''Y'', ''N'') DRILLPIVOTVB
286 ,a.curr_hdc_end HRI_P_MEASURE1
287 ,a.hdc_change_pct HRI_P_MEASURE1_MP
288 ,DECODE(a.curr_total_hdc_end, 0, 0,
289 (100 * a.curr_hdc_end) / a.curr_total_hdc_end) ' ||
290 'HRI_P_MEASURE2
291 ,a.comp_hdc_end HRI_P_MEASURE3
292 ,a.curr_sal_end HRI_P_MEASURE4
293 ,a.sal_change_pct HRI_P_MEASURE4_MP
294 ,DECODE(a.curr_total_sal_end, 0, 0,
295 100 * a.curr_sal_end / a.curr_total_sal_end) HRI_P_MEASURE5
296 ,a.comp_sal_end HRI_P_MEASURE6
297 ,a.curr_avg_sal HRI_P_MEASURE7
298 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
299 (p_previous_col => 'a.comp_avg_sal',
300 p_current_col => 'a.curr_avg_sal') || '
301 HRI_P_MEASURE7_MP
302 ,a.comp_avg_sal HRI_P_MEASURE8
303 ,a.anl_factor * 100 * a.curr_trn_vol / a.curr_trn_div HRI_P_MEASURE9
304 ,a.anl_factor * 100 * a.curr_trn_inv / a.curr_trn_div HRI_P_MEASURE10
305 ,a.anl_factor * 100 * a.curr_trn_tot / a.curr_trn_div HRI_P_MEASURE11
306 ,a.anl_factor * 100 * (a.curr_trn_tot / a.curr_trn_div -
307 a.comp_trn_tot / a.comp_trn_div) HRI_P_MEASURE11_MP
308 ,a.curr_trn_vol HRI_P_MEASURE12
309 ,a.curr_trn_inv HRI_P_MEASURE13
310 ,a.curr_trn_tot HRI_P_MEASURE14
311 ,DECODE(a.curr_total_trn_tot, 0, 0,
312 100 * a.curr_trn_tot / a.curr_total_trn_tot) HRI_P_MEASURE15
313 ,a.curr_total_hdc_end HRI_P_GRAND_TOTAL1
314 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
315 (p_previous_col => 'a.comp_total_hdc_end',
316 p_current_col => 'a.curr_total_hdc_end') || '
317 HRI_P_GRAND_TOTAL1_MP
318 ,100 HRI_P_GRAND_TOTAL2
319 ,a.comp_total_hdc_end HRI_P_GRAND_TOTAL3
320 ,a.curr_total_sal_end HRI_P_GRAND_TOTAL4
321 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
322 (p_previous_col => 'a.comp_total_sal_end',
323 p_current_col => 'a.curr_total_sal_end') || '
324 HRI_P_GRAND_TOTAL4_MP
325 ,100 HRI_P_GRAND_TOTAL5
326 ,a.comp_total_sal_end HRI_P_GRAND_TOTAL6
327 ,a.curr_total_avg_sal HRI_P_GRAND_TOTAL7
328 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
329 (p_previous_col => 'a.comp_total_avg_sal',
330 p_current_col => 'a.curr_total_avg_sal') || '
331 HRI_P_GRAND_TOTAL7_MP
332 ,a.comp_total_avg_sal HRI_P_GRAND_TOTAL8
333 ,a.anl_factor * 100 * a.curr_total_trn_vol / curr_total_trn_div HRI_P_GRAND_TOTAL9
334 ,a.anl_factor * 100 * a.curr_total_trn_inv / curr_total_trn_div HRI_P_GRAND_TOTAL10
335 ,a.anl_factor * 100 * a.curr_total_trn_tot / curr_total_trn_div HRI_P_GRAND_TOTAL11
336 ,a.anl_factor * 100 * (a.curr_total_trn_tot / curr_total_trn_div -
337 a.comp_total_trn_tot / comp_total_trn_div) HRI_P_GRAND_TOTAL11_MP' || g_rtn ||
338 /* Order by person name default sort order */
339 ',a.order_by HRI_P_ORDER_BY_1 ' || g_rtn ||
340 /* Whether the row is a supervisor rollup row */
341 ',DECODE(a.direct_ind , 0, '''', ''N'') HRI_P_SUPH_RO_CA' || g_rtn ||
342 /* Drill URLs */
343 ',DECODE(a.direct_ind,
344 0, ''' || l_dynsql_rec.drill_mgr_sup || ''',
345 1, ''' || l_dynsql_rec.drill_to_detail || ''',
346 '''') HRI_P_DRILL_URL1
347 FROM
348 (SELECT
349 tots.* ' || g_rtn ||
350 /* Headcount */
351 ' ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
352 (p_previous_col => 'tots.comp_hdc_end',
353 p_current_col => 'tots.curr_hdc_end') || '
354 hdc_change_pct' || g_rtn ||
355 /* Salary */
356 ' ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
357 (p_previous_col => 'tots.comp_sal_end',
358 p_current_col => 'tots.curr_sal_end') || '
359 sal_change_pct' || g_rtn ||
360 /* Average Salary */
361 ' ,DECODE(tots.curr_hdc_end,
362 0, to_number(null),
363 tots.curr_sal_end / tots.curr_hdc_end) curr_avg_sal
364 ,DECODE(tots.comp_hdc_end,
365 0, to_number(null),
366 tots.comp_sal_end / tots.comp_hdc_end) comp_avg_sal
367 ,DECODE(tots.curr_hdc_trn,
368 0, DECODE(tots.curr_trn_tot, 0 , 1, tots.curr_trn_tot),
369 tots.curr_hdc_trn) curr_trn_div
370 ,DECODE(tots.comp_hdc_trn,
371 0, DECODE(tots.comp_trn_tot, 0 , 1, tots.comp_trn_tot),
372 tots.comp_hdc_trn) comp_trn_div
373 ,:HRI_ANL_FACTOR anl_factor' || g_rtn ||
374 /* Grand Totals - Average Salary */
375 ',DECODE(tots.curr_total_hdc_end,
376 0, to_number(null),
377 tots.curr_total_sal_end / tots.curr_total_hdc_end) curr_total_avg_sal
378 ,DECODE(tots.comp_total_hdc_end,
379 0, to_number(null),
380 tots.comp_total_sal_end / tots.comp_total_hdc_end) comp_total_avg_sal
381 ,DECODE(tots.curr_total_hdc_trn,
382 0, DECODE(tots.curr_total_trn_tot, 0 , 1, tots.curr_total_trn_tot),
383 tots.curr_total_hdc_trn) curr_total_trn_div
384 ,DECODE(tots.comp_total_hdc_trn,
385 0, DECODE(tots.comp_total_trn_tot, 0 , 1, tots.comp_total_trn_tot),
386 tots.comp_total_hdc_trn) comp_total_trn_div
387 FROM
388 (SELECT
389 /* View by */
390 vby.id
391 ,DECODE(wmv.direct_ind,
392 1, ''' || l_direct_reports_string || ''',
393 vby.value) value
394 ,to_char(NVL(wmv.direct_ind, 0)) || vby.order_by order_by' || g_rtn ||
395 /* Indicators */
396 ' ,NVL(wmv.direct_ind, 0) direct_ind' || g_rtn ||
397 /* Headcount */
398 ' ,NVL(wmv.curr_hdc_end, 0) curr_hdc_end
399 ,NVL(wmv.comp_hdc_end, 0) comp_hdc_end' || g_rtn ||
400 /* Salary */
401 ' ,NVL(wmv.curr_sal_end, 0) curr_sal_end
402 ,NVL(wmv.comp_sal_end, 0) comp_sal_end' || g_rtn ||
403 /* Headcount for turnover calculation */
404 ' ,' || l_dynsql_rec.hdc_trn_col_curr ||
405 ' curr_hdc_trn
406 ,' || l_dynsql_rec.hdc_trn_col_comp ||
407 ' comp_hdc_trn' || g_rtn ||
408 /* Turnover */
409 ' ,NVL(trn.curr_sep_vol_hdc, 0) curr_trn_vol
410 ,NVL(trn.curr_sep_invol_hdc, 0) curr_trn_inv
411 ,NVL(trn.curr_separation_hdc, 0) curr_trn_tot
412 ,NVL(trn.comp_sep_vol_hdc, 0) comp_trn_vol
413 ,NVL(trn.comp_sep_invol_hdc, 0) comp_trn_inv
414 ,NVL(trn.comp_separation_hdc, 0) comp_trn_tot' || g_rtn ||
415 /* Grand Totals - Headcount */
416 ' ,NVL(SUM(wmv.curr_hdc_end) OVER (), 0) curr_total_hdc_end
417 ,NVL(SUM(wmv.comp_total_hdc_end) OVER (), 0) comp_total_hdc_end' || g_rtn ||
418 /* Grand Totals - Salary */
419 ' ,NVL(SUM(wmv.curr_sal_end) OVER (), 0) curr_total_sal_end
420 ,NVL(SUM(wmv.comp_total_sal_end) OVER (), 0) comp_total_sal_end' || g_rtn ||
421 /* Grand Totals - Headcount for turnover calculation */
422 ' ,NVL(SUM(' || l_dynsql_rec.hdc_trn_col_curr_tot ||
423 ') OVER (), 0) curr_total_hdc_trn
424 ,NVL(SUM(' || l_dynsql_rec.hdc_trn_col_comp_tot ||
425 ') OVER (), 0) comp_total_hdc_trn' || g_rtn ||
426 /* Grand Totals - Turnover */
427 ' ,:HRI_CURR_TRN_VOL curr_total_trn_vol
428 ,:HRI_CURR_TRN_INVOL curr_total_trn_inv
429 ,:HRI_CURR_TRN_INVOL + :HRI_CURR_TRN_VOL curr_total_trn_tot
430 ,:HRI_COMP_TRN_VOL comp_total_trn_vol
431 ,:HRI_COMP_TRN_INVOL comp_total_trn_inv
432 ,:HRI_COMP_TRN_VOL + :HRI_COMP_TRN_INVOL comp_total_trn_tot
433 FROM
434 ' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
435 (l_parameter_rec.view_by).viewby_table || ' vby,
436 (' || l_wcnt_chg_fact_sql || ') trn,
437 (' || l_wrkfc_fact_sql || ') wmv
438 WHERE wmv.vby_id = trn.vby_id (+)
439 AND wmv.vby_id ' || l_dynsql_rec.wrkfc_outer_join || ' = vby.id' || g_rtn ||
440 l_dynsql_rec.viewby_condition ||
441 ' ) tots
442 ) a
443 WHERE 1 = 1 ' || g_rtn ||
444 l_dynsql_rec.display_row_condition ||
445 l_security_clause || '
446 ORDER BY a.direct_ind, ' || l_dynsql_rec.order_by;
447
448 x_custom_sql := l_SQLText;
449
450 l_custom_rec.attribute_name := ':HRI_ANL_FACTOR';
451 l_custom_rec.attribute_value := l_calc_anl_factor;
452 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
453 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
454 x_custom_output.extend;
455 x_custom_output(1) := l_custom_rec;
456
457 l_custom_rec.attribute_name := ':GLOBAL_CURRENCY';
458 l_custom_rec.attribute_value := l_parameter_rec.currency_code;
459 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
460 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
461 x_custom_output.extend;
462 x_custom_output(2) := l_custom_rec;
463
464 l_custom_rec.attribute_name := ':GLOBAL_RATE';
465 l_custom_rec.attribute_value := l_parameter_rec.rate_type;
466 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
467 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
468 x_custom_output.extend;
469 x_custom_output(3) := l_custom_rec;
470
471 l_custom_rec.attribute_name := ':HRI_CURR_TRN_VOL';
472 l_custom_rec.attribute_value := l_curr_term_vol;
473 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
474 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
475 x_custom_output.extend;
476 x_custom_output(4) := l_custom_rec;
477
478 l_custom_rec.attribute_name := ':HRI_CURR_TRN_INVOL';
479 l_custom_rec.attribute_value := l_curr_term_invol;
480 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
481 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
482 x_custom_output.extend;
483 x_custom_output(5) := l_custom_rec;
484
485 l_custom_rec.attribute_name := ':HRI_COMP_TRN_VOL';
486 l_custom_rec.attribute_value := l_comp_term_vol;
487 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
488 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
489 x_custom_output.extend;
490 x_custom_output(6) := l_custom_rec;
491
492 l_custom_rec.attribute_name := ':HRI_COMP_TRN_INVOL';
493 l_custom_rec.attribute_value := l_comp_term_invol;
494 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
495 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
496 x_custom_output.extend;
497 x_custom_output(7) := l_custom_rec;
498
499 END get_sql_pvt;
500
501 END hri_oltp_pmv_wrkfc_trn_summary;