[Home] [Help]
PACKAGE BODY: APPS.HRI_BPL_TREND_TRN_SQL
Source
1 PACKAGE BODY hri_bpl_trend_trn_sql AS
2 /* $Header: hribttrn.pkb 120.0 2005/05/29 07:05:29 appldev noship $ */
3 --
4 g_rtn VARCHAR2(30) := '
5 ';
6 --
7 --
8 /******************************************************************************/
9 /* PROCESS FLOW */
10 /* ============ */
11 /* This package formulates the fact query for the turnover calculation over */
12 /* a set of trend periods. The functions that return the trend SQL for */
13 /* headcount and terminations are called and the resulting queries joined */
14 /* into a single query. The measure column list is also merged. */
15 /* */
16 /* Turnover calculation */
17 /* -------------------- */
18 /* If the turnover 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-turnover calculation is used and the result put in a */
23 /* new column period_hdc_trn. */
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 /* Terminations 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 turnover 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 termination
57 --
58 l_termination_sql VARCHAR2(32767);
59 l_trm_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 -- (if required for turnover calculation)
68 --
69 l_headcount_start_sql VARCHAR2(32767);
70 l_hdc_start_cols hri_oltp_pmv_query_trend.trend_measure_cols_type;
71 --
72 -- Stores the turnover SQL
73 --
74 l_bucket_tab hri_mtdt_dim_lvl.dim_lvl_buckets_tabtype;
75 l_sql_select VARCHAR2(10000);
76 --
77 l_trend_sql_params hri_oltp_pmv_query_trend.TREND_SQL_PARAMS_TYPE;
78 l_index PLS_INTEGER := 0;
79 l_trn_calc_mth VARCHAR2(30);
80 l_use_snapshot BOOLEAN;
81 --
82 BEGIN
83 --
84 -- Fetch the trend SQL for termination
85 --
86 hri_bpl_trend_trm_sql.get_sql
87 (p_parameter_rec => p_parameter_rec,
88 p_bind_tab => p_bind_tab,
89 p_trend_sql_params => p_trend_sql_params,
90 p_fact_sql => l_termination_sql,
91 p_measure_columns => l_trm_measure_cols);
92 --
93 -- Fetch the trend SQL for headcount
94 --
95 -- Set the parameter for including headcount for
96 -- turnover calculation
97 --
98 l_trend_sql_params := p_trend_sql_params;
99 l_trend_sql_params.include_hdc_trn := 'Y';
100 hri_bpl_trend_wrkfc_sql.get_sql
101 (p_parameter_rec => p_parameter_rec,
102 p_bind_tab => p_bind_tab,
103 p_trend_sql_params => l_trend_sql_params,
104 p_date_join_type => 'PERIOD_END',
105 p_fact_sql => l_headcount_sql,
106 p_measure_columns => l_hdc_measure_cols,
107 p_use_snapshot => l_use_snapshot);
108 --
109 -- Combine the measure columns into a master column list
110 -- also adding the turnover columns into the select clause
111 --
112 l_index := 0;
113 FOR i IN l_hdc_measure_cols.FIRST..l_hdc_measure_cols.LAST LOOP
114 l_index := l_index + 1;
115 --
116 -- Relabel headcount measures as headcount for turnover calculation
117 --
118 p_measure_columns(l_index) := REPLACE(l_hdc_measure_cols(i), 'hdc', 'hdc_trn');
119 END LOOP;
120 FOR j IN l_trm_measure_cols.FIRST..l_trm_measure_cols.LAST LOOP
121 l_index := l_index + 1;
122 p_measure_columns(l_index) := l_trm_measure_cols(j);
123 /* Add turnover measure columns to select list */
124 l_sql_select := l_sql_select || ',sep.' || l_trm_measure_cols(j) || g_rtn;
125 END LOOP;
126 --
127 -- Format the SQL differently depending on the turnover calculation method
128 -- and whether or not snapshot MVs are available
129 --
130 IF ((fnd_profile.value('HR_TRNVR_CALC_MTHD') = 'WMV_STARTENDAVG') AND
131 NOT l_use_snapshot) THEN
132
133 -- Start/end average required, no snapshots available
134 -- SQL returned needs to join to workforce fact twice to get the
135 -- headcount at start and end of each period
136
137 /* Get the SQL for headcount at period start */
138 hri_bpl_trend_wrkfc_sql.get_sql
139 (p_parameter_rec => p_parameter_rec,
140 p_bind_tab => p_bind_tab,
141 p_trend_sql_params => p_trend_sql_params,
142 p_date_join_type => 'PERIOD_START',
143 p_fact_sql => l_headcount_start_sql,
144 p_measure_columns => l_hdc_start_cols,
145 p_use_snapshot => l_use_snapshot);
146
147 /* Add the headcount for turnover calculation columns */
148 FOR i IN l_hdc_measure_cols.FIRST..l_hdc_measure_cols.LAST LOOP
149 -- Replace headcount measure with headcount for turnover calculation
150 IF (INSTR(l_hdc_measure_cols(i), 'hdc') > 0) THEN
151 l_sql_select := l_sql_select ||
152 ',(wmv.' || l_hdc_measure_cols(i) || ' + ' ||
153 'NVL(wmv_start.' || l_hdc_measure_cols(i) || ', 0)) / 2 ' ||
154 REPLACE(l_hdc_measure_cols(i), 'hdc', 'hdc_trn') || g_rtn;
155 -- Add other measures
156 ELSE
157 l_sql_select := l_sql_select || ',wmv.' || l_hdc_measure_cols(i) || g_rtn;
158 END IF;
159 END LOOP;
160
161 --
162 -- Form the turnover SQL
163 --
164 p_fact_sql :=
165 'SELECT
166 sep.period_as_of_date
167 ,sep.period_order' || g_rtn ||
168 l_sql_select ||
169 'FROM
170 ('||l_termination_sql ||') sep
171 ,('||l_headcount_sql ||') wmv
172 ,('||l_headcount_start_sql ||') wmv_start
173 WHERE sep.period_as_of_date = wmv.period_as_of_date
174 AND wmv.period_as_of_date = wmv_start.period_as_of_date (+)';
175
176 ELSE
177
178 IF (fnd_profile.value('HR_TRNVR_CALC_MTHD') = 'WMV_STARTENDAVG') THEN
179
180 -- Snapshot fact available
181 -- Period Headcount start/end included in single snapshot
182
183 /* Add the headcount for turnover calculation columns */
184 FOR i IN l_hdc_measure_cols.FIRST..l_hdc_measure_cols.LAST LOOP
185 -- Replace headcount start with headcount for turnover calculation
186 IF (INSTR(l_hdc_measure_cols(i), 'hdc') > 0) THEN
187 l_sql_select := l_sql_select ||
188 ',(wmv.' || l_hdc_measure_cols(i) || ' + wmv.' ||
189 REPLACE(l_hdc_measure_cols(i), 'hdc', 'hdc_start') || ') / 2 ' ||
190 REPLACE(l_hdc_measure_cols(i), 'hdc', 'hdc_trn') || g_rtn;
191 -- Add non-headcount columns
192 ELSE
193 l_sql_select := l_sql_select || ',wmv.' || l_hdc_measure_cols(i) || g_rtn;
194 END IF;
195 END LOOP;
196
197 ELSE
198
199 -- Only headcount at period end is required for the turnover calculation
200 -- Snapshot/standard MVs have similar SQL format
201
202 /* Add the headcount for turnover calculation columns */
203 FOR i IN l_hdc_measure_cols.FIRST..l_hdc_measure_cols.LAST LOOP
204 l_sql_select := l_sql_select ||
205 ',wmv.' || l_hdc_measure_cols(i) || ' ' ||
206 REPLACE(l_hdc_measure_cols(i), 'hdc', 'hdc_trn') || g_rtn;
207 END LOOP;
208 END IF;
209
210 --
211 -- Form the turnover SQL
212 --
213 p_fact_sql :=
214 'SELECT
215 sep.period_as_of_date
216 ,sep.period_order' || g_rtn ||
217 l_sql_select ||
218 'FROM
219 ('||l_termination_sql||') sep
220 ,('||l_headcount_sql ||') wmv
221 WHERE sep.period_as_of_date = wmv.period_as_of_date';
222 --
223 END IF;
224 --
225 END get_sql;
226 --
227 END hri_bpl_trend_trn_sql;