1 PACKAGE BODY hri_bpl_trend_wrkfc_abs_sql AS
2 /* $Header: hribtwfabs.pkb 120.3 2005/11/17 03:45 cbridge noship $ */
3 --
4 g_rtn VARCHAR2(30) := '
5 ';
6 --
7 --
8 /******************************************************************************/
9 /* PROCESS FLOW */
10 /* ============ */
11 /* This package formulates the fact query for the absence calculation over */
12 /* a set of trend periods. The functions that return the trend SQL for */
13 /* headcount and absences are called and the resulting queries joined */
14 /* into a single query. The measure column list is also merged. */
15 /* */
16 /* Absence calculation */
17 /* -------------------- */
18 /* If the absence calculation uses headcount at the start and end of the */
19 /* period then the headcount function is called twice to get the headcount */
20 /* total at each end of the period. */
21 /* */
22 /* The headcount-for-absence calculation is used and the result put in a */
23 /* new column period_hdc_abs. */
24 /* */
25 /* Return SQL */
26 /* ---------- */
27 /* The resulting SQL returned looks like: */
28 /* */
29 /* SELECT */
30 /* Period Id (Date) */
31 /* Period Order */
32 /* Measure columns (merged) */
33 /* FROM */
34 /* Absences by Trend Period */
35 /* Headcount at Trend Period End */
36 /* Headcount at Trend Period Start (if required) */
37 /* WHERE */
38 /* Join fact tables on period id */
39 /* */
40 /* */
41 /******************************************************************************/
42 --
43 -- -------------------------------------------------------------------------
44 -- This procedure returns the trend inner SQL for absence and the list of
45 -- measure columns used
46 -- -------------------------------------------------------------------------
47 --
48 PROCEDURE get_sql
49 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
50 p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
51 p_trend_sql_params IN hri_oltp_pmv_query_trend.TREND_SQL_PARAMS_TYPE,
52 p_fact_sql OUT NOCOPY VARCHAR2,
53 p_measure_columns OUT NOCOPY hri_oltp_pmv_query_trend.trend_measure_cols_type)
54 IS
55 --
56 -- Stores the trend SQL for absence
57 --
58 l_absence_sql VARCHAR2(32767);
59 l_abs_measure_cols hri_oltp_pmv_query_trend.trend_measure_cols_type;
60 --
61 -- Stores the trend SQL for headcount
62 --
63 l_headcount_sql VARCHAR2(32767);
64 l_hdc_measure_cols hri_oltp_pmv_query_trend.trend_measure_cols_type;
65 --
66 -- Stores the trend SQL for headcount at period start
67 --
68 l_headcount_start_sql VARCHAR2(32767);
69 l_hdc_start_cols hri_oltp_pmv_query_trend.trend_measure_cols_type;
70 --
71 -- Stores the absence SQL
72 --
73 l_bucket_tab hri_mtdt_dim_lvl.dim_lvl_buckets_tabtype;
74 l_sql_select VARCHAR2(10000);
75 --
76 l_trend_sql_params hri_oltp_pmv_query_trend.TREND_SQL_PARAMS_TYPE;
77 l_index PLS_INTEGER := 0;
78 l_trn_calc_mth VARCHAR2(30);
79 l_use_snapshot BOOLEAN;
80 --
81 BEGIN
82 --
83 -- Fetch the trend SQL for termination
84 --
85 hri_bpl_trend_abs_sql.get_sql
86 (p_parameter_rec => p_parameter_rec,
87 p_bind_tab => p_bind_tab,
88 p_trend_sql_params => p_trend_sql_params,
89 p_date_join_type => 'PERIOD_END',
90 p_fact_sql => l_absence_sql,
91 p_measure_columns => l_abs_measure_cols,
92 p_use_snapshot => l_use_snapshot); -- note snapshoting support not in first release.
93 --
94 -- Fetch the trend SQL for headcount
95 --
96 -- Set the parameter for including headcount for
97 -- absence calculation using turnover style query
98 --
99 l_trend_sql_params := p_trend_sql_params;
100 l_trend_sql_params.include_hdc := 'Y';
101 l_trend_sql_params.include_hdc_trn := 'Y'; -- use trn for abs start headcount
102 hri_bpl_trend_wrkfc_sql.get_sql
103 (p_parameter_rec => p_parameter_rec,
104 p_bind_tab => p_bind_tab,
105 p_trend_sql_params => l_trend_sql_params,
106 p_date_join_type => 'PERIOD_END',
107 p_fact_sql => l_headcount_sql,
108 p_measure_columns => l_hdc_measure_cols,
109 p_use_snapshot => l_use_snapshot);
110 --
111 -- Combine the measure columns into a master column list
112 -- also adding the absence columns into the select clause
113 --
114 l_index := 0;
115 FOR i IN l_hdc_measure_cols.FIRST..l_hdc_measure_cols.LAST LOOP
116 l_index := l_index + 1;
117 --
118 -- Relabel headcount measures as headcount for absence calculation
119 --
120 p_measure_columns(l_index) := REPLACE(l_hdc_measure_cols(i), 'hdc', 'hdc_abs');
121 END LOOP;
122
123 FOR j IN l_abs_measure_cols.FIRST..l_abs_measure_cols.LAST LOOP
124 l_index := l_index + 1;
125 p_measure_columns(l_index) := l_abs_measure_cols(j);
126 /* Add absence measure columns to select list */
127 l_sql_select := l_sql_select || ',abs.' || l_abs_measure_cols(j) || g_rtn;
128 END LOOP;
129 --
130 -- Format the SQL differently depending on the turnover calculation method
131 -- and whether or not snapshot MVs are available
132 --
133 IF ((fnd_profile.value('HR_TRNVR_CALC_MTHD') = 'WMV_STARTENDAVG') AND
134 NOT l_use_snapshot) THEN
135
136 -- Start/end average required, no snapshots available
137 -- SQL returned needs to join to workforce fact twice to get the
138 -- headcount at start and end of each period
139
140 /* Get the SQL for headcount at period start */
141 hri_bpl_trend_wrkfc_sql.get_sql
142 (p_parameter_rec => p_parameter_rec,
143 p_bind_tab => p_bind_tab,
144 p_trend_sql_params => p_trend_sql_params,
145 p_date_join_type => 'PERIOD_START',
146 p_fact_sql => l_headcount_start_sql,
147 p_measure_columns => l_hdc_start_cols,
148 p_use_snapshot => l_use_snapshot);
149
150 /* Add the headcount for absence calculation columns */
151 FOR i IN l_hdc_measure_cols.FIRST..l_hdc_measure_cols.LAST LOOP
152 -- Replace headcount measure with headcount for absence calculation
153 IF (INSTR(l_hdc_measure_cols(i), 'hdc') > 0) THEN
154 l_sql_select := l_sql_select ||
155 ',(wmv.' || l_hdc_measure_cols(i) || ' + ' ||
156 'NVL(wmv_start.' || l_hdc_measure_cols(i) || ', 0)) / 2 ' ||
157 REPLACE(l_hdc_measure_cols(i), 'hdc', 'hdc_abs') || g_rtn;
158 -- Add other measures
159 ELSE
160 l_sql_select := l_sql_select || ',wmv.' || l_hdc_measure_cols(i) || g_rtn;
161 END IF;
162 END LOOP;
163
164 --
165 -- Form the absence and headcount (turnover style) SQL
166 --
167 p_fact_sql :=
168 'SELECT
169 abs.period_as_of_date
170 ,abs.period_order' || g_rtn ||
171 l_sql_select ||
172 'FROM
173 ('||l_absence_sql ||') abs
174 ,('||l_headcount_sql ||') wmv
175 ,('||l_headcount_start_sql ||') wmv_start
176 WHERE abs.period_as_of_date = wmv.period_as_of_date
177 AND wmv.period_as_of_date = wmv_start.period_as_of_date (+)';
178
179 ELSE
180
181 IF (fnd_profile.value('HR_TRNVR_CALC_MTHD') = 'WMV_STARTENDAVG') THEN
182
183 -- Snapshot fact available
184 -- Period Headcount start/end included in single snapshot
185
186 /* Add the headcount for absence (turnover style) calculation columns */
187 FOR i IN l_hdc_measure_cols.FIRST..l_hdc_measure_cols.LAST LOOP
188 -- Replace headcount start with headcount for absence calculation
189 IF (INSTR(l_hdc_measure_cols(i), 'hdc') > 0) THEN
190 l_sql_select := l_sql_select ||
191 ',(wmv.' || l_hdc_measure_cols(i) || ' + wmv.' ||
192 REPLACE(l_hdc_measure_cols(i), 'hdc', 'hdc_start') || ') / 2 ' ||
193 REPLACE(l_hdc_measure_cols(i), 'hdc', 'hdc_abs') || g_rtn;
194 -- Add non-headcount columns
195 ELSE
196 l_sql_select := l_sql_select || ',wmv.' || l_hdc_measure_cols(i) || g_rtn;
197 END IF;
198 END LOOP;
199
200 ELSE
201
202 -- Only headcount at period end is required for the absence calculation
203 -- Snapshot/standard MVs have similar SQL format
204
205 /* Add the headcount for absence (turnover style) calculation columns */
206 FOR i IN l_hdc_measure_cols.FIRST..l_hdc_measure_cols.LAST LOOP
207 l_sql_select := l_sql_select ||
208 ',wmv.' || l_hdc_measure_cols(i) || ' ' ||
209 REPLACE(l_hdc_measure_cols(i), 'hdc', 'hdc_abs') || g_rtn;
210 END LOOP;
211 END IF;
212
213 --
214 -- Form the absence and headcount (turnover style) SQL
215 --
216 p_fact_sql :=
217 'SELECT
218 wmv.period_as_of_date
219 ,wmv.period_order' || g_rtn ||
220 l_sql_select ||
221 'FROM
222 ('||l_absence_sql||') abs
223 ,('||l_headcount_sql ||') wmv
224 WHERE abs.period_as_of_date (+) = wmv.period_as_of_date';
225 --
226 END IF;
227 --
228 END get_sql;
229 --
230 END hri_bpl_trend_wrkfc_abs_sql;
[Home] [Help]