[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_WMV_TRN_SUMMRY
Source
1 PACKAGE BODY HRI_OLTP_PMV_WMV_TRN_SUMMRY AS
2 /* $Header: hrioptsm.pkb 120.2 2005/10/26 07:54 jrstewar noship $ */
3
4 g_rtn VARCHAR2(5) := '
5 ';
6
7 /******************************************************************************/
8 /* Turnover Pivot Setup
9 /******************************************************************************/
10
11 TYPE dynamic_sql_rec_type IS RECORD
12 (
13 -- View by small dim outer join
14 wrkfc_outer_join VARCHAR2(5),
15 -- Turnover Headcount calculation
16 hdc_trn_col_curr VARCHAR2(1000),
17 hdc_trn_col_comp VARCHAR2(1000),
18 hdc_trn_col_curr_tot VARCHAR2(1000),
19 hdc_trn_col_comp_tot VARCHAR2(1000),
20 -- Drill URLs
21 drill_mgr_sup VARCHAR2(1000),
22 drill_mgr_dir VARCHAR2(1000),
23 drill_trn_vol_dtl VARCHAR2(1000),
24 drill_trn_inv_dtl VARCHAR2(1000),
25 drill_trn_tot_dtl VARCHAR2(1000),
26 drill_total_sal VARCHAR2(1000),
27 -- Display row condition
28 display_row_condition VARCHAR2(1000),
29 view_by_filter VARCHAR2(1000)
30 );
31
32 /* Dynamically changes report SQL depending on parameters */
33 PROCEDURE set_dynamic_sql
34 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
35 p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
36 p_dynsql_rec OUT NOCOPY dynamic_sql_rec_type) IS
37
38 BEGIN
39
40 /* Get the profile value for the turnover calculation */
41 IF fnd_profile.value('HR_TRNVR_CALC_MTHD') = 'WMV_STARTENDAVG' THEN
42 /* Set the current calculation to be current start/end 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
48 p_dynsql_rec.hdc_trn_col_curr_tot :=
49 '(wmv.curr_hdc_end + wmv.curr_total_hdc_start) / 2';
50 p_dynsql_rec.hdc_trn_col_comp_tot :=
51 '(wmv.comp_total_hdc_end + wmv.comp_total_hdc_start) / 2';
52
53 ELSE
54 p_dynsql_rec.hdc_trn_col_curr := 'NVL(wmv.curr_hdc_end, 0)';
55 p_dynsql_rec.hdc_trn_col_comp := 'NVL(wmv.comp_hdc_end, 0)';
56 p_dynsql_rec.hdc_trn_col_curr_tot := 'wmv.curr_hdc_end';
57 p_dynsql_rec.hdc_trn_col_comp_tot := 'wmv.comp_total_hdc_end';
58
59 END IF;
60
61 /* Set drill URLs for constant drills */
62 p_dynsql_rec.drill_trn_vol_dtl := 'pFunctionName=HRI_P_WMV_TRN_SUP_DTL&' ||
63 'VIEW_BY_NAME=VIEW_BY_ID&' ||
64 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&' ||
65 'HRI_P_WAC_SEPCAT_CN=SEP_VOL&'||
66 'pParamIds=Y';
67 p_dynsql_rec.drill_trn_inv_dtl := 'pFunctionName=HRI_P_WMV_TRN_SUP_DTL&' ||
68 'VIEW_BY_NAME=VIEW_BY_ID&' ||
69 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&' ||
70 'HRI_P_WAC_SEPCAT_CN=SEP_INV&'||
71 'pParamIds=Y';
72 p_dynsql_rec.drill_trn_tot_dtl := 'pFunctionName=HRI_P_WMV_TRN_SUP_DTL&' ||
73 'VIEW_BY_NAME=VIEW_BY_ID&' ||
74 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&' ||
75 'pParamIds=Y';
76
77 -- ----------------------
78 -- View by Person
79 -- ----------------------
80 IF (p_parameter_rec.view_by = 'HRI_PERSON+HRI_PER_USRDR_H') THEN
81
82 /* Set drill URLs */
83 p_dynsql_rec.drill_mgr_sup := 'pFunctionName=HRI_P_WMV_TRN_SUMMARY_PVT&' ||
84 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
85 'VIEW_BY_NAME=VIEW_BY_ID&' ||
86 'pParamIds=Y';
87 p_dynsql_rec.drill_mgr_dir := 'pFunctionName=HRI_P_WMV_SAL_SUP_DTL&' ||
88 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
89 'VIEW_BY_NAME=VIEW_BY_ID&' ||
90 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&' ||
91 'pParamIds=Y';
92
93 END IF;
94
95 /* Set the display row conditions */
96 IF (p_parameter_rec.view_by = 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X' OR
97 p_parameter_rec.view_by = 'HRI_LOW+HRI_LOW_BAND_X') THEN
98
99 /* If view by is performance or length of work display all cl view rows */
100 /* regardless of whether there is any headcount or turnover */
101 p_dynsql_rec.wrkfc_outer_join := ' (+)';
102
103 /* Set the view by filter if these parameters have been set */
104 p_dynsql_rec.view_by_filter := hri_oltp_pmv_util_pkg.set_viewby_filter
105 (p_parameter_rec => p_parameter_rec,
106 p_bind_tab => p_bind_tab,
107 p_view_by_alias => 'vby');
108
109 ELSE
110
111 /* Only display rows with current headcount or turnover */
112 p_dynsql_rec.display_row_condition :=
113 'AND (a.curr_hdc_end > 0
114 OR a.curr_term_invol_hdc > 0
115 OR a.curr_term_vol_hdc > 0
116 OR a.direct_ind = 1)' || g_rtn;
117
118 END IF;
119
120 /* bug 4202907 append cl start AND end_date filter if viewby manager */
121 IF (p_parameter_rec.view_by = 'HRI_PERSON+HRI_PER_USRDR_H') THEN
122 p_dynsql_rec.view_by_filter := p_dynsql_rec.view_by_filter ||
123 'AND &BIS_CURRENT_ASOF_DATE BETWEEN vby.start_date AND vby.end_date';
124 END IF;
125
126 END set_dynamic_sql;
127
128 /******************************************************************************/
129 /* Turnover Pivot Ak Query centralized code
130 /******************************************************************************/
131
132 PROCEDURE get_sql_pvt
133 (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
134 x_custom_sql OUT NOCOPY VARCHAR2,
135 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
136
137 l_sqltext VARCHAR2(32767);
138 l_custom_rec BIS_QUERY_ATTRIBUTES;
139 l_security_clause VARCHAR2(4000);
140
141 /* Dynamic SQL support */
142 l_dynsql_rec dynamic_sql_rec_type;
143
144 /* Annualization factor for period type parameter */
145 l_calc_anl_factor NUMBER;
146
147 /* Pre-calculations for turnover total */
148 l_curr_term_vol NUMBER;
149 l_curr_term_invol NUMBER;
150 l_curr_term NUMBER;
151 l_comp_term_vol NUMBER;
152 l_comp_term_invol NUMBER;
153 l_comp_term NUMBER;
154
155 -- new code centralization new variables/structures
156 l_wcnt_chg_fact_sql VARCHAR2(32767);
157 l_wrkfc_fact_sql VARCHAR2(32767);
158 l_wrkfc_params hri_bpl_fact_sup_wrkfc_sql.wrkfc_fact_param_type;
159 l_wcnt_chg_params hri_bpl_fact_sup_wcnt_chg_sql.wcnt_chg_fact_param_type;
160
161 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
162 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
163
164 /* Messages */
165 l_direct_reports_string VARCHAR2(240);
166
167 BEGIN
168
169 /* Initialize out parameters */
170 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
171 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
172
173 /* Get common parameter values */
174 hri_oltp_pmv_util_param.get_parameters_from_table
175 (p_page_parameter_tbl => p_page_parameter_tbl,
176 p_parameter_rec => l_parameter_rec,
177 p_bind_tab => l_bind_tab);
178
179 /* Get the annualization factor for the different periods */
180 l_calc_anl_factor := hri_oltp_pmv_util_pkg.calc_anl_factor
181 (p_period_type => l_parameter_rec.page_period_type);
182
183 /* Set the dynamic sql portion */
184 set_dynamic_sql(p_parameter_rec => l_parameter_rec,
185 p_bind_tab => l_bind_tab,
186 p_dynsql_rec => l_dynsql_rec);
187
188 /* Get security clause for Manager based security */
189 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
190
191 /* Get direct reports string */
192 l_direct_reports_string := hri_oltp_view_message.get_direct_reports_msg;
193
194 /* Get current period turnover totals for supervisor from cursor */
195 hri_bpl_dbi_calc_period.calc_sup_term_pvt
196 (p_supervisor_id => l_parameter_rec.peo_supervisor_id,
197 p_from_date => l_parameter_rec.time_curr_start_date,
198 p_to_date => l_parameter_rec.time_curr_end_date,
199 p_bind_tab => l_bind_tab,
200 p_total_term_vol => l_curr_term_vol,
201 p_total_term_invol => l_curr_term_invol,
202 p_total_term => l_curr_term);
203
204 /* Get previous period turnover totals for supervisor from cursor */
205 hri_bpl_dbi_calc_period.calc_sup_term_pvt
206 (p_supervisor_id => l_parameter_rec.peo_supervisor_id,
207 p_from_date => l_parameter_rec.time_comp_start_date,
208 p_to_date => l_parameter_rec.time_comp_end_date,
209 p_bind_tab => l_bind_tab,
210 p_total_term_vol => l_comp_term_vol,
211 p_total_term_invol => l_comp_term_invol,
212 p_total_term => l_comp_term);
213
214 /* Generate the Turnover Fact SQL */
215 l_wcnt_chg_params.bind_format := 'PMV';
216 l_wcnt_chg_params.include_comp := 'Y';
217 l_wcnt_chg_params.include_sep := 'Y';
218 l_wcnt_chg_params.include_sep_inv := 'Y';
219 l_wcnt_chg_params.include_sep_vol := 'Y';
220 l_wcnt_chg_fact_sql := hri_bpl_fact_sup_wcnt_chg_sql.get_sql
221 (p_parameter_rec => l_parameter_rec,
222 p_bind_tab => l_bind_tab,
223 p_wcnt_chg_params => l_wcnt_chg_params);
224
225 /* Generate the Workforce Fact SQL */
226 l_wrkfc_params.bind_format := 'PMV';
227 l_wrkfc_params.include_hdc := 'Y';
228 l_wrkfc_params.include_comp := 'Y';
229 IF (fnd_profile.value('HR_TRNVR_CALC_MTHD') = 'WMV_STARTENDAVG') THEN
230 l_wrkfc_params.include_start := 'Y';
231 END IF;
232 l_wrkfc_fact_sql := hri_bpl_fact_sup_wrkfc_sql.get_sql
233 (p_parameter_rec => l_parameter_rec,
234 p_bind_tab => l_bind_tab,
235 p_wrkfc_params => l_wrkfc_params);
236
237 /* Set the dynamic order by from the dimension metadata */
238 l_parameter_rec.order_by := hri_oltp_pmv_util_pkg.set_default_order_by
239 (p_order_by_clause => l_parameter_rec.order_by);
240
241
242 /* Format the AK_SQL for the report UI */
243 l_sqltext :=
244 'SELECT -- Workforce Turnover Summary Portlet new code
245 a.id VIEWBYID
246 ,a.value VIEWBY
247 ,DECODE(a.direct_ind , 0, ''Y'', ''N'') DRILLPIVOTVB' || g_rtn ||
248 /* Title - Headcount */'
249 ,NVL(a.comp_hdc_end,0) HRI_P_MEASURE1
250 ,NVL(a.curr_hdc_end,0) HRI_P_MEASURE2
251 ,DECODE(a.curr_total_hdc_end, 0, 0,
252 (100 * a.curr_hdc_end) / a.curr_total_hdc_end) ' ||
253 ' HRI_P_MEASURE3' || g_rtn ||
254 /* Title - Voluntary Terminations */'
255 ,a.curr_term_vol_hdc HRI_P_MEASURE4
256 ,a.anl_factor * 100 * a.curr_term_vol_hdc / a.curr_trn_div
257 HRI_P_MEASURE5
258 ,a.anl_factor * 100 * (a.curr_term_vol_hdc / a.curr_trn_div -
259 a.comp_term_vol_hdc / a.comp_trn_div)
260 HRI_P_MEASURE6 ' || g_rtn ||
261 /* Title - InVoluntary Terminations */'
262 ,a.curr_term_invol_hdc HRI_P_MEASURE7
263 ,a.anl_factor * 100 * a.curr_term_invol_hdc / a.curr_trn_div
264 HRI_P_MEASURE8
265 ,a.anl_factor * 100 * (a.curr_term_invol_hdc / a.curr_trn_div -
266 a.comp_term_invol_hdc / a.comp_trn_div)
267 HRI_P_MEASURE9' || g_rtn ||
268 /* Title - Total Terminations */'
269 ,a.curr_termination_hdc HRI_P_MEASURE10
270 ,a.anl_factor * 100 * a.curr_termination_hdc / a.curr_trn_div
271 HRI_P_MEASURE11
272 ,a.anl_factor * 100 * (a.curr_termination_hdc / a.curr_trn_div -
273 a.comp_termination_hdc / a.comp_trn_div)
274 HRI_P_MEASURE12' || g_rtn ||
275
276 /* Title - Grand Total Headcount */'
277 ,a.comp_total_hdc_end HRI_P_GRAND_TOTAL1
278 ,a.curr_total_hdc_end HRI_P_GRAND_TOTAL2
279 ,100 HRI_P_GRAND_TOTAL3' || g_rtn ||
280 /* Title - Grand Total Voluntary Terminations */'
281 ,a.curr_total_trn_vol HRI_P_GRAND_TOTAL4
282 ,a.anl_factor * 100 * a.curr_total_trn_vol / curr_total_trn_div
283 HRI_P_GRAND_TOTAL5
284 ,a.anl_factor * 100 * (a.curr_total_trn_vol / curr_total_trn_div -
285 a.comp_total_trn_vol / comp_total_trn_div)
286 HRI_P_GRAND_TOTAL6' || g_rtn ||
287 /* Title - Grand Total InVoluntary Terminations */'
288 ,a.curr_total_trn_inv HRI_P_GRAND_TOTAL7
289 ,a.anl_factor * 100 * a.curr_total_trn_inv / curr_total_trn_div
290 HRI_P_GRAND_TOTAL8
291 ,a.anl_factor * 100 * (a.curr_total_trn_inv / curr_total_trn_div -
292 a.comp_total_trn_inv / comp_total_trn_div)
293 HRI_P_GRAND_TOTAL9' || g_rtn ||
294 /* Title - Grand Total Total Terminations */'
295 ,a.curr_total_trn_tot HRI_P_GRAND_TOTAL10
296 ,a.anl_factor * 100 * a.curr_total_trn_tot / curr_total_trn_div
297 HRI_P_GRAND_TOTAL11
298 ,a.anl_factor * 100 * (a.curr_total_trn_tot / curr_total_trn_div -
299 a.comp_total_trn_tot / comp_total_trn_div)
300 HRI_P_GRAND_TOTAL12' || g_rtn ||
301 /* Order by person name default sort order */
302 ',a.order_by HRI_P_ORDER_BY_1 ' || g_rtn ||
303 /* Whether the row is a supervisor rollup row */
304 ',DECODE(a.direct_ind , 0, '''', ''N'')
305 HRI_P_SUPH_RO_CA' || g_rtn ||
306 /* Drill URLs */
307 ',DECODE(a.direct_ind,
308 0, ''' || l_dynsql_rec.drill_mgr_sup || ''',
309 1, ''' || l_dynsql_rec.drill_mgr_dir || ''',
310 '''') HRI_P_DRILL_URL1
311 ,''' || l_dynsql_rec.drill_trn_vol_dtl || '''
312 HRI_P_DRILL_URL2
313 ,''' || l_dynsql_rec.drill_trn_inv_dtl || '''
314 HRI_P_DRILL_URL3
315 ,''' || l_dynsql_rec.drill_trn_tot_dtl || '''
316 HRI_P_DRILL_URL4
317 FROM
318 (SELECT
319 tots.* ' || g_rtn ||
320 /* Headcount change */
321 ' ,DECODE(tots.comp_hdc_end,
322 0, 0,
323 100 * (tots.curr_hdc_end - tots.comp_hdc_end) / tots.comp_hdc_end)
324 hdc_change_pct' || g_rtn ||
325 /* Terminations Factor */
326 ' ,DECODE(tots.curr_hdc_trn,
327 0, DECODE(tots.curr_termination_hdc, 0 , 1, tots.curr_termination_hdc),
328 tots.curr_hdc_trn) curr_trn_div
329 ,DECODE(tots.comp_hdc_trn,
330 0, DECODE(tots.comp_termination_hdc, 0 , 1, tots.comp_termination_hdc),
331 tots.comp_hdc_trn) comp_trn_div
332 ,:HRI_ANL_FACTOR anl_factor' || g_rtn ||
333 /* Grand Totals - Terminations */
334 ' ,DECODE(tots.curr_total_hdc_trn,
335 0, DECODE(tots.curr_total_trn_tot, 0 , 1, tots.curr_total_trn_tot),
336 tots.curr_total_hdc_trn) curr_total_trn_div
337 ,DECODE(tots.comp_total_hdc_trn,
338 0, DECODE(tots.comp_total_trn_tot, 0 , 1, tots.comp_total_trn_tot),
339 tots.comp_total_hdc_trn) comp_total_trn_div
340 FROM
341 (SELECT
342 /* View by */
343 vby.id
344 ,DECODE(wmv.direct_ind,
345 1, ''' || l_direct_reports_string || ''',
346 vby.value) value
347 ,to_char(NVL(wmv.direct_ind, 0)) || vby.order_by order_by' || g_rtn ||
348 /* Indicators */
349 ' ,NVL(wmv.direct_ind, 0) direct_ind' || g_rtn ||
350 /* Headcount */
351 ' ,NVL(wmv.curr_hdc_end, 0) curr_hdc_end
352 ,NVL(wmv.comp_hdc_end, 0) comp_hdc_end' || g_rtn ||
353 /* Headcount for turnover calculation */
354 ' ,' || l_dynsql_rec.hdc_trn_col_curr || ' curr_hdc_trn
355 ,' || l_dynsql_rec.hdc_trn_col_comp || ' comp_hdc_trn' || g_rtn ||
356 /* Turnover */
357 ' ,NVL(trn.curr_sep_vol_hdc, 0) curr_term_vol_hdc
358 ,NVL(trn.curr_sep_invol_hdc, 0) curr_term_invol_hdc
359 ,NVL(trn.curr_separation_hdc, 0) curr_termination_hdc
360 ,NVL(trn.comp_sep_vol_hdc, 0) comp_term_vol_hdc
361 ,NVL(trn.comp_sep_invol_hdc, 0) comp_term_invol_hdc
362 ,NVL(trn.comp_separation_hdc, 0) comp_termination_hdc' || g_rtn ||
363 /* Grand Totals - Headcount */
364 ' ,NVL(SUM(wmv.curr_hdc_end) OVER (), 0) curr_total_hdc_end
365 ,NVL(SUM(comp_total_hdc_end) OVER (), 0) comp_total_hdc_end' || g_rtn ||
366 /* Grand Totals - Headcount for turnover calculation */
367 ' ,NVL(SUM(' || l_dynsql_rec.hdc_trn_col_curr_tot ||
368 ') OVER (), 0) curr_total_hdc_trn
369 ,NVL(SUM(' || l_dynsql_rec.hdc_trn_col_comp_tot ||
370 ') OVER (), 0) comp_total_hdc_trn' || g_rtn ||
371 /* Grand Totals - Turnover */
372 ' ,:HRI_CURR_TERM_VOL curr_total_trn_vol
373 ,:HRI_CURR_TERM_INVOL curr_total_trn_inv
374 ,:HRI_CURR_TERM_INVOL + :HRI_CURR_TERM_VOL curr_total_trn_tot
375 ,:HRI_COMP_TERM_VOL comp_total_trn_vol
376 ,:HRI_COMP_TERM_INVOL comp_total_trn_inv
377 ,:HRI_COMP_TERM_VOL + :HRI_COMP_TERM_INVOL comp_total_trn_tot
378 FROM
379 ' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
380 (l_parameter_rec.view_by).viewby_table || ' vby' || g_rtn
381 || ',( ' || l_wrkfc_fact_sql || ' ) wmv' || g_rtn
382 || ',( ' || l_wcnt_chg_fact_sql || ' ) trn' || g_rtn
383 || 'WHERE wmv.vby_id = trn.vby_id (+)
384 AND wmv.vby_id ' || l_dynsql_rec.wrkfc_outer_join || ' = vby.id ' || g_rtn ||
385 l_dynsql_rec.view_by_filter ||
386 ' ) tots
387 ) a
388 WHERE 1 = 1 ' || g_rtn ||
389 l_dynsql_rec.display_row_condition ||
390 l_security_clause || '
391 ORDER BY ' || l_parameter_rec.order_by;
392
393 x_custom_sql := l_SQLText;
394
395 l_custom_rec.attribute_name := ':HRI_ANL_FACTOR';
396 l_custom_rec.attribute_value := l_calc_anl_factor;
397 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
398 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
399 x_custom_output.extend;
400 x_custom_output(1) := l_custom_rec;
401
402 l_custom_rec.attribute_name := ':HRI_CURR_TERM_VOL';
403 l_custom_rec.attribute_value := l_curr_term_vol;
404 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
405 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
406 x_custom_output.extend;
407 x_custom_output(2) := l_custom_rec;
408
409 l_custom_rec.attribute_name := ':HRI_CURR_TERM_INVOL';
410 l_custom_rec.attribute_value := l_curr_term_invol;
411 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
412 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
413 x_custom_output.extend;
414 x_custom_output(3) := l_custom_rec;
415
416 l_custom_rec.attribute_name := ':HRI_COMP_TERM_VOL';
417 l_custom_rec.attribute_value := l_comp_term_vol;
418 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
419 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
420 x_custom_output.extend;
421 x_custom_output(4) := l_custom_rec;
422
423 l_custom_rec.attribute_name := ':HRI_COMP_TERM_INVOL';
424 l_custom_rec.attribute_value := l_comp_term_invol;
425 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
426 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
427 x_custom_output.extend;
428 x_custom_output(5) := l_custom_rec;
429
430 END get_sql_pvt;
431
432 END hri_oltp_pmv_wmv_trn_summry;