1 PACKAGE BODY hri_bpl_trend_abs_sql AS
2 /* $Header: hribtabs.pkb 120.0 2005/09/22 07:28 cbridge noship $ */
3 --
4 --
5 g_column_select VARCHAR2(1000);
6 g_column_bucket VARCHAR2(1000);
7 g_rtn VARCHAR2(30) := '
8 ';
9 --
10 --
11 /******************************************************************************/
12 /* PROCESS FLOW */
13 /* ============ */
14 /* The main entry point is the get_sql function. The processing is as follows */
15 /* */
16 /* SELECT */
17 /* ------ */
18 /* Templates for the measure columns are set by the set_metadata function. */
19 /* Appropriate measures are then added to the select list using the template */
20 /* depending on the input trend parameter record */
21 /* */
22 /* A list of all the measure columns added is maintained and returned to the */
23 /* calling function so that outer layers of SQL can reference all the columns */
24 /* */
25 /* FROM */
26 /* ---- */
27 /* The set_fact_table function in HRI_BPL_FACT_ABS_SQL is used to determine */
28 /* the appropriate fact object. */
29 /* */
30 /* WHERE */
31 /* ----- */
32 /* set_conditions adds in any extra conditions required e.g. in top 4 */
33 /* categories a filter on the top 4 categories codes is added */
34 /* */
35 /* SQL RETURNED */
36 /* ============ */
37 /* The SQL is returned along with a list of all the measure columns in the */
38 /* SELECT list: */
39 /* */
40 /* SELECT */
41 /* Period Id (Date) */
42 /* Period Order */
43 /* Measure Columns */
44 /* FROM */
45 /* Table of periods to plot (sub-query) */
46 /* Snapshot/standard fact object */
47 /* WHERE */
48 /* Filter on selected manager */
49 /* Date filter (varies with snapshot/standard fact) */
50 /* Additional filters (e.g. top 4 countries) */
51 /* */
52 /* An outer layer of SQL is added that brings in periods with no data by */
53 /* doing a UNION ALL with the trend periods table. */
54 /* */
55 /* Note: Snapshoting not currently supported in first release of Absences */
56 /* */
57 /******************************************************************************/
58 --
59 -- Sets select column templates for accessing the workforce fact
60 --
61 PROCEDURE set_metadata IS
62 --
63 BEGIN
64 --
65 g_column_select := 'NVL(SUM(<measure>), 0)';
66 g_column_bucket :=
67 'NVL(SUM(CASE WHEN fact.<bucket> = <value>
68 THEN <measure>
69 ELSE 0
70 END), 0)';
71 --
72 END set_metadata;
73 --
74 --
75 -- -------------------------------------------------------------------------
76 -- This procedure is for future use only - applies dimension level parameter
77 -- conditions. Currently all trend reports are run from the main page which
78 -- does not have any additional parameters.
79 -- -------------------------------------------------------------------------
80 --
81 PROCEDURE analyze_parameters
82 (p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
83 p_fact_conditions OUT NOCOPY VARCHAR2,
84 p_parameter_count OUT NOCOPY PLS_INTEGER) IS
85
86 l_parameter_name VARCHAR2(100);
87
88 BEGIN
89
90 /* Initialize parameter count */
91 p_parameter_count := 0;
92
93 /* Loop through parameters that have been set */
94 l_parameter_name := p_bind_tab.FIRST;
95
96 WHILE (l_parameter_name IS NOT NULL) LOOP
97 IF (l_parameter_name = 'HRI_ABSNC+HRI_ABSNC_CAT' ) THEN
98
99 /* Dynamically set conditions for parameter */
100 p_fact_conditions := p_fact_conditions ||
101 'AND fact.' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
102 (l_parameter_name).fact_viewby_col ||
103 ' IN (' || p_bind_tab(l_parameter_name).pmv_bind_string || ')' || g_rtn;
104
105 /* Keep count of parameters set */
106 p_parameter_count := p_parameter_count + 1;
107
108 END IF;
109
110 /* Move to next parameter */
111 l_parameter_name := p_bind_tab.NEXT(l_parameter_name);
112
113 END LOOP;
114
115 END analyze_parameters;
116 --
117 -- -------------------------------------------------------------------------
118 -- This function returns a string which contains the columns in the fact
119 -- that have to be selected. The columns that are to be selected are
120 -- specified as the metadata.
121 -- -------------------------------------------------------------------------
122 --
123 PROCEDURE set_select
124 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
125 p_bucket_dim IN VARCHAR2,
126 p_include_abs_drtn_days IN VARCHAR2,
127 p_include_abs_drtn_hrs IN VARCHAR2,
128 p_include_abs_in_period IN VARCHAR2,
129 p_include_abs_ntfctn_period IN VARCHAR2,
130 p_use_snapshot IN BOOLEAN,
131 p_select_sql OUT NOCOPY VARCHAR2,
132 p_measure_columns OUT NOCOPY hri_oltp_pmv_query_trend.trend_measure_cols_type)
133 IS
134
135 -- template
136 l_column_bucket VARCHAR2(1000);
137 -- table of bucket values
138 l_bucket_tab hri_mtdt_dim_lvl.dim_lvl_buckets_tabtype;
139 --
140 -- For forming the select statement
141 --
142 l_measure_abs_drtn_days VARCHAR2(1000);
143 l_measure_abs_drtn_hrs VARCHAR2(1000);
144 l_measure_abs_drtn_in_prd VARCHAR2(1000);
145 l_measure_abs_drtn_ntf_prd VARCHAR2(1000);
146
147 l_measure_count PLS_INTEGER;
148 --
149 BEGIN
150
151 -- Initialize measure count and columns
152 l_measure_count := 0;
153 l_measure_abs_drtn_days := 'fact.abs_drtn_days';
154 l_measure_abs_drtn_hrs := 'fact.abs_drtn_hrs';
155 l_measure_abs_drtn_in_prd := 'fact.abs_start_blnc + fact.abs_nstart_blnc';
156 l_measure_abs_drtn_ntf_prd := 'fact.abs_ntfctn_days_start_blnc + fact.abs_ntfctn_days_nstart_blnc';
157
158 -- Get a pl/sql table containing the buckets for the given bucket dimension
159 IF (p_bucket_dim = 'HRI_ABSNC+HRI_ABSNC_CAT') THEN
160 l_bucket_tab := hri_mtdt_dim_lvl.g_abs_category_buckets_tab;
161 END IF;
162
163 -- Set measure columns for Absence Duration Days
164 IF (p_include_abs_drtn_days ='Y') THEN
165 p_select_sql := p_select_sql || ',' || REPLACE(g_column_select,
166 '<measure>', l_measure_abs_drtn_days) ||
167 ' period_abs_drtn_days' || g_rtn;
168 l_measure_count := l_measure_count + 1;
169 p_measure_columns(l_measure_count) := 'period_abs_drtn_days';
170
171 IF p_bucket_dim IS NOT NULL THEN
172
173 -- Set the bucket column template to use the bucket column
174 l_column_bucket := REPLACE(g_column_bucket, '<bucket>',
175 hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
176 (p_bucket_dim).fact_viewby_col);
177
178
179 /* Loop through bucket ids to add required columns */
180 FOR i IN l_bucket_tab.FIRST..l_bucket_tab.LAST LOOP
181 p_select_sql := p_select_sql || ',' ||
182 REPLACE(REPLACE(l_column_bucket,
183 '<measure>', l_measure_abs_drtn_days),
184 '<value>', l_bucket_tab(i).bucket_id_string) ||
185 ' period_abs_drtn_days_' || l_bucket_tab(i).bucket_name || g_rtn;
186 -- Add column name to measure table
187 l_measure_count := l_measure_count + 1;
188 p_measure_columns(l_measure_count) := 'period_abs_drtn_days_' ||
189 l_bucket_tab(i).bucket_name;
190 END LOOP;
191 END IF;
192 END IF;
193 --
194
195 -- Set measure columns for Absence Duration Hours
196 IF (p_include_abs_drtn_hrs ='Y') THEN
197 p_select_sql := p_select_sql || ',' || REPLACE(g_column_select,
198 '<measure>', l_measure_abs_drtn_hrs) ||
199 ' period_abs_drtn_hrs' || g_rtn;
200 l_measure_count := l_measure_count + 1;
201 p_measure_columns(l_measure_count) := 'period_abs_drtn_hrs';
202
203 IF p_bucket_dim IS NOT NULL THEN
204
205 -- Set the bucket column template to use the bucket column
206 l_column_bucket := REPLACE(g_column_bucket, '<bucket>',
207 hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
208 (p_bucket_dim).fact_viewby_col);
209
210 /* Loop through bucket ids to add required columns */
211 FOR i IN l_bucket_tab.FIRST..l_bucket_tab.LAST LOOP
212 p_select_sql := p_select_sql || ',' ||
213 REPLACE(REPLACE(l_column_bucket,
214 '<measure>', l_measure_abs_drtn_hrs),
215 '<value>', l_bucket_tab(i).bucket_id_string) ||
216 ' period_abs_drtn_hrs_' || l_bucket_tab(i).bucket_name || g_rtn;
217 -- Add column name to measure table
218 l_measure_count := l_measure_count + 1;
219 p_measure_columns(l_measure_count) := 'period_abs_drtn_hrs_' ||
220 l_bucket_tab(i).bucket_name;
221 END LOOP;
222
223 END IF;
224 END IF;
225 --
226
227 -- Set measure columns for Absence In Period
228 IF (p_include_abs_in_period ='Y') THEN
229 p_select_sql := p_select_sql || ',' || REPLACE(g_column_select,
230 '<measure>', l_measure_abs_drtn_in_prd) ||
231 ' period_abs_in_period' || g_rtn;
232 l_measure_count := l_measure_count + 1;
233 p_measure_columns(l_measure_count) := 'period_abs_in_period';
234 END IF;
235 --
236
237 -- Set measure columns for Absence Notification In Period
238 IF (p_include_abs_ntfctn_period ='Y') THEN
239 p_select_sql := p_select_sql || ',' || REPLACE(g_column_select,
240 '<measure>', l_measure_abs_drtn_ntf_prd) ||
241 ' period_abs_ntfctn_period' || g_rtn;
242 l_measure_count := l_measure_count + 1;
243 p_measure_columns(l_measure_count) := 'period_abs_ntfctn_period';
244 END IF;
245 --
246
247 END set_select;
248 --
249 -- -------------------------------------------------------------------------
250 -- This procedure returns conditions apart from the common conditions that
251 -- are present in a typical trend SQL.
252 -- -------------------------------------------------------------------------
253 --
254 PROCEDURE set_conditions(p_bucket_dim IN VARCHAR2,
255 p_fact_condition IN OUT NOCOPY VARCHAR2)
256 IS
257 --
258 --
259 BEGIN
260 --
261 -- Set the country condition only when the bucket dimension is country
262 --
263 IF p_bucket_dim = 'HRI_ABSNC+HRI_ABSNC_CAT' THEN
264 --
265 p_fact_condition := p_fact_condition ||
266 'AND fact.absence_category_code IN
267 (:ABS_CATEGORY_CODE1,
268 :ABS_CATEGORY_CODE2,
269 :ABS_CATEGORY_CODE3,
270 :ABS_CATEGORY_CODE4)' || g_rtn;
271 --
272 END IF;
273 --
274 END set_conditions;
275
276 --
277 -- -------------------------------------------------------------------------
278 -- This function returns the inner SQL that is required for genrating the
279 -- headcount trend reports
280 --
281 -- INPUT PARAMETERS:
282 -- p_parameter_rec: Parameters passed to the report
283 -- p_bind_tab: The bind strings for PMV and SQL mode
284 -- p_bind_format : SQL or PMV format
285 -- p_past_trend: Set if SQL has to be generated for past periods
286 -- p_future_trend: Set if SQL has to be generated for future periods
287 -- -------------------------------------------------------------------------
288 --
289 PROCEDURE get_sql
290 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
291 p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
292 p_trend_sql_params IN hri_oltp_pmv_query_trend.trend_sql_params_type,
293 p_date_join_type IN VARCHAR2,
294 p_fact_sql OUT NOCOPY VARCHAR2,
295 p_measure_columns OUT NOCOPY hri_oltp_pmv_query_trend.trend_measure_cols_type,
296 p_use_snapshot OUT NOCOPY BOOLEAN)
297 IS
298 --
299 l_trend_periods_tbl VARCHAR2(32767);
300 l_select_sql VARCHAR2(32767);
301 l_fact_table VARCHAR2(100);
302 l_date_join VARCHAR2(1000);
303 l_fact_condition VARCHAR2(1000);
304 l_use_snapshot BOOLEAN;
305 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
306 l_param_conditions VARCHAR2(1000);
307 l_parameter_count PLS_INTEGER;
308 --
309 BEGIN
310 -- -----------------------------------------------------------------------
311 -- FROM CLAUSE
312 -- -----------------------------------------------------------------------
313 analyze_parameters
314 (p_bind_tab => p_bind_tab,
315 p_fact_conditions => l_param_conditions,
316 p_parameter_count => l_parameter_count);
317 --
318 -- Fetch the SQL for the table of periods
319 --
320 l_trend_periods_tbl := '(' ||
321 HRI_OLTP_PMV_QUERY_TIME.get_time_clause
322 (p_past_trend => p_trend_sql_params.past_trend,
323 p_future_trend => p_trend_sql_params.future_trend) || ')';
324 --
325 -- Set the fact table
326 --
327 l_parameter_rec := p_parameter_rec;
328 l_parameter_rec.view_by := 'HRI_ABSNC+HRI_ABSNC_CAT';
329
330 hri_bpl_fact_abs_sql.set_fact_table
331 (p_parameter_rec => l_parameter_rec,
332 p_bucket_dim => p_trend_sql_params.bucket_dim,
333 p_abs_drtn_days => p_trend_sql_params.include_abs_drtn_days,
334 p_abs_drtn_hrs => p_trend_sql_params.include_abs_drtn_hrs,
335 p_abs_in_period => p_trend_sql_params.include_abs_in_period,
336 p_abs_ntfctn_period => p_trend_sql_params.include_abs_ntfctn_period,
337 p_parameter_count => 0,
338 p_single_param => NULL,
339 p_use_snapshot => l_use_snapshot,
340 p_fact_table => l_fact_table);
341 --
342 -- -----------------------------------------------------------------------
343 -- SELECT CLAUSE
344 -- -----------------------------------------------------------------------
345 --
346 -- Set the select column templates
347 --
348 set_metadata;
349 --
350 --
351 -- Fetches the column in the select clause. If p_group_by is true then
352 -- all the columns will be summed up. Common columns included in all
353 -- trend reports will not be fetched
354 --
355 set_select
356 (p_parameter_rec => l_parameter_rec,
357 p_bucket_dim => p_trend_sql_params.bucket_dim,
358 p_include_abs_drtn_days => p_trend_sql_params.include_abs_drtn_days,
359 p_include_abs_drtn_hrs => p_trend_sql_params.include_abs_drtn_hrs,
360 p_include_abs_in_period => p_trend_sql_params.include_abs_in_period,
361 p_include_abs_ntfctn_period => p_trend_sql_params.include_abs_ntfctn_period,
362 p_use_snapshot => l_use_snapshot,
363 p_select_sql => l_select_sql,
364 p_measure_columns => p_measure_columns);
365 --
366 --
367 -- -----------------------------------------------------------------------
368 -- WHERE CLAUSE
369 -- -----------------------------------------------------------------------
370 --
371 -- Get the conditions for the where clause. Common conditions included in all
372 -- trend reports will not be fetched
373 --
374 set_conditions
375 (p_bucket_dim => p_trend_sql_params.bucket_dim,
376 p_fact_condition => l_fact_condition);
377 --
378
379 -- Add direct record condition for old style fact tables
380 --
381 IF (upper(l_fact_table) = 'HRI_MDP_SUP_ABSNC_CAT_MV' ) THEN
382 l_fact_condition := l_fact_condition ||
383 'AND fact.direct_record_ind = 0' || g_rtn;
384 END IF;
385
386
387 -- Date join is equality if using snapshots
388 --
389 IF (p_use_snapshot) THEN
390 l_fact_condition := l_fact_condition ||
391 'AND fact.effective_date = tro.period_end_date
392 AND fact.comparison_type IN (''CURRENT'', ''SEQUENTIAL'', ''TREND'')
393 AND fact.period_type = &PERIOD_TYPE' || g_rtn;
394 ELSE
395 l_fact_condition := l_fact_condition ||
396 'AND fact.effective_date BETWEEN tro.period_start_date ' ||
397 'AND tro.period_end_date' || g_rtn;
398 END IF;
399
400 --
401 -- -----------------------------------------------------------------------
402 -- BUILD THE SQL
403 -- -----------------------------------------------------------------------
404 --
405 p_fact_sql :=
406 'SELECT /*+ LEADING(tro) INDEX(fact) */
407 tro.period_as_of_date
408 ,tro.period_order' || g_rtn ||
409 l_select_sql ||
410 'FROM
411 ' || l_trend_periods_tbl || ' tro
412 ,' || l_fact_table || ' fact
413 WHERE fact.supervisor_person_id = &HRI_PERSON+HRI_PER_USRDR_H' || g_rtn ||
414 l_date_join ||
415 l_fact_condition ||
416 l_param_conditions ||
417 'GROUP BY
418 tro.period_order
419 ,tro.period_as_of_date';
420
421 p_use_snapshot := l_use_snapshot;
422
423 END get_sql;
424
425 END hri_bpl_trend_abs_sql;
[Home] [Help]