[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_QUERY_TREND
Source
1 PACKAGE BODY hri_oltp_pmv_query_trend AS
2 /* $Header: hriopqtd.pkb 120.3 2005/09/20 05:02:55 cbridge noship $ */
3
4 g_rtn VARCHAR2(30) := '
5 ';
6
7 /******************************************************************************/
8 /* PROCESS FLOW */
9 /* ============ */
10 /* This process calls a function to get SQL to retrieve the data by trend */
11 /* period. The trend period SQL query is also found, and the two merged with */
12 /* a UNION ALL / GROUP to replace any periods where there is no data. */
13 /* */
14 /* First the decision as to which function to call is made depending on the */
15 /* trend parameters passed in. */
16 /* */
17 /* Second the functions to get the fact sql and trend period sql are called. */
18 /* Note the fact sql function also returns a list of the measure columns in */
19 /* the select list. */
20 /* */
21 /* Third the merged SQL is put together, which looks like: */
22 /* */
23 /* SELECT */
24 /* Period Id (Date) */
25 /* Period Order */
26 /* SUM(Measure columns) (returned from fact sql) */
27 /* FROM */
28 /* (Fact SQL */
29 /* UNION ALL */
30 /* SELECT */
31 /* Period Id (Date) */
32 /* Period Order */
33 /* 0 (Measure columns) */
34 /* FROM */
35 /* Trend Period SQL) */
36 /* GROUP BY */
37 /* Period Id (Date) */
38 /* Period Order */
39 /* */
40 /******************************************************************************/
41
42 /* This procedure decides whether to call workforce, terminations or */
43 /* both (turnover), gets the sql for the accessing the required info */
44 /* and returns this along with a list of the measure columns used */
45 PROCEDURE get_fact_sql
46 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
47 p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
48 p_trend_sql_params IN hri_oltp_pmv_query_trend.trend_sql_params_type,
49 p_fact_sql OUT NOCOPY VARCHAR2,
50 p_measure_columns OUT NOCOPY trend_measure_cols_type)
51
52 IS
53
54 l_query_wrkfc VARCHAR2(30);
55 l_query_absence VARCHAR2(30);
56 l_query_wcnt_chg VARCHAR2(30);
57 l_use_snapshot BOOLEAN;
58
59 BEGIN
60 --
61 -- Decide which facts to call
62 --
63 IF (p_trend_sql_params.include_hdc = 'Y' OR
64 p_trend_sql_params.include_sal = 'Y') THEN
65 l_query_wrkfc := 'Y';
66 ELSE
67 l_query_wrkfc := 'N';
68 END IF;
69 IF (p_trend_sql_params.include_sep = 'Y' OR
70 p_trend_sql_params.include_sep_inv = 'Y' OR
71 p_trend_sql_params.include_sep_vol = 'Y') THEN
72 l_query_wcnt_chg := 'Y';
73 ELSE
74 l_query_wcnt_chg := 'N';
75 END IF;
76
77 IF (p_trend_sql_params.include_abs_drtn_days = 'Y' OR
78 p_trend_sql_params.include_abs_drtn_hrs = 'Y' OR
79 p_trend_sql_params.include_abs_in_period = 'Y' OR
80 p_trend_sql_params.include_abs_ntfctn_period = 'Y' )THEN
81 l_query_absence := 'Y';
82 l_use_snapshot := TRUE;
83 ELSE
84 l_query_absence := 'N';
85 END IF;
86
87 --
88 -- Depending upon the required measures call the headcount/termination
89 -- turnover trend SQL package
90 --
91 IF (l_query_wrkfc = 'Y' AND
92 l_query_wcnt_chg = 'N' AND
93 l_query_absence = 'N') THEN
94 --
95 hri_bpl_trend_wrkfc_sql.get_sql
96 (p_parameter_rec => p_parameter_rec,
97 p_bind_tab => p_bind_tab,
98 p_trend_sql_params => p_trend_sql_params,
99 p_date_join_type => 'PERIOD_END',
100 p_fact_sql => p_fact_sql,
101 p_measure_columns => p_measure_columns,
102 p_use_snapshot => l_use_snapshot);
103 --
104 ELSIF (l_query_wrkfc = 'N' AND
105 l_query_wcnt_chg = 'Y' AND
106 l_query_absence = 'N') THEN
107 --
108 hri_bpl_trend_trm_sql.get_sql
109 (p_parameter_rec => p_parameter_rec,
110 p_bind_tab => p_bind_tab,
111 p_trend_sql_params => p_trend_sql_params,
112 p_fact_sql => p_fact_sql,
113 p_measure_columns => p_measure_columns);
114 --
115 ELSIF (l_query_wrkfc = 'Y' AND
116 l_query_wcnt_chg = 'Y' AND
117 l_query_absence = 'N') THEN
118 --
119 hri_bpl_trend_trn_sql.get_sql
120 (p_parameter_rec => p_parameter_rec,
121 p_bind_tab => p_bind_tab,
122 p_trend_sql_params => p_trend_sql_params,
123 p_fact_sql => p_fact_sql,
124 p_measure_columns => p_measure_columns);
125 --
126
127 ELSIF (l_query_absence = 'Y' AND
128 l_query_wrkfc = 'N') THEN
129
130 --
131 hri_bpl_trend_abs_sql.get_sql
132 (p_parameter_rec => p_parameter_rec,
133 p_bind_tab => p_bind_tab,
134 p_trend_sql_params => p_trend_sql_params,
135 p_date_join_type => 'PERIOD_END',
136 p_fact_sql => p_fact_sql,
137 p_measure_columns => p_measure_columns,
138 p_use_snapshot => l_use_snapshot);
139 --
140 ELSIF (l_query_absence = 'Y' AND
141 l_query_wrkfc = 'Y' ) THEN
142 --
143 hri_bpl_trend_wrkfc_abs_sql.get_sql
144 (p_parameter_rec => p_parameter_rec,
145 p_bind_tab => p_bind_tab,
146 p_trend_sql_params => p_trend_sql_params,
147 p_fact_sql => p_fact_sql,
148 p_measure_columns => p_measure_columns);
149 --
150 END IF;
151 --
152 END get_fact_sql;
153
154 --
155 -- -------------------------------------------------------------------------
156 -- This function returns the SQL required for generating trend reports.
157 -- INPUT PARAMETERS:
158 -- p_parameter_rec : Parameters passed to the report
159 -- p_bind_tab : The bind strings for PMV and SQL mode
160 -- p_trend_sql_params : For passing the tredn parameters
161 -- -------------------------------------------------------------------------
162 --
163 FUNCTION get_sql
164 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
165 p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
166 p_trend_sql_params IN trend_sql_params_type,
167 p_calling_module IN VARCHAR2)
168 RETURN VARCHAR2 IS
169 --
170 l_fact_sql VARCHAR2(32767);
171 l_debug_sql VARCHAR2(32767);
172 l_return_sql VARCHAR2(32767);
173 l_debug VARCHAR2(30) := 'N';
174 l_measure_columns trend_measure_cols_type;
175 l_trend_periods_tbl VARCHAR2(10000);
176 l_fact_select VARCHAR2(10000);
177 l_union_select VARCHAR2(10000);
178 l_group_select VARCHAR2(10000);
179 --
180 BEGIN
181 --
182 -- Set the SQL to get the trend periods
183 --
184 l_trend_periods_tbl := '(' ||
185 HRI_OLTP_PMV_QUERY_TIME.get_time_clause
186 (p_past_trend => p_trend_sql_params.past_trend,
187 p_future_trend => p_trend_sql_params.future_trend) || ')';
188 --
189 -- get the sql accessing the fact cubes
190 --
191 get_fact_sql
192 (p_parameter_rec => p_parameter_rec,
193 p_bind_tab => p_bind_tab,
194 p_trend_sql_params => p_trend_sql_params,
195 p_fact_sql => l_fact_sql,
196 p_measure_columns => l_measure_columns);
197
198 --
199 -- Build SELECT list for UNION ALL
200 --
201 FOR i IN l_measure_columns.FIRST..l_measure_columns.LAST LOOP
202
203 l_fact_select := l_fact_select || ',' || l_measure_columns(i) || g_rtn;
204 l_union_select := l_union_select || ',0 ' || l_measure_columns(i) || g_rtn;
205 l_group_select := l_group_select || ',SUM(' || l_measure_columns(i) || ') ' ||
206 l_measure_columns(i) || g_rtn;
207
208 END LOOP;
209
210 --
211 -- Build return sql stmt
212 --
213 l_return_sql :=
214 'SELECT
215 grp.period_as_of_date
216 ,grp.period_order' || g_rtn ||
217 l_group_select ||
218 'FROM (' || g_rtn ||
219 l_fact_sql || g_rtn ||
220 ' UNION ALL
221 SELECT
222 tro.period_as_of_date
223 ,tro.period_order'
224 || g_rtn ||
225 l_union_select ||
226 ' FROM
227 (' || l_trend_periods_tbl || ') tro
228 ) grp
229 GROUP BY
230 grp.period_as_of_date
231 ,grp.period_order';
232
233 --
234 IF l_debug = 'Y' THEN
235 --
236 -- build sql with bind_format 'SQL' and log result
237 --
238 l_debug_sql := l_return_sql;
239 hri_oltp_pmv_util_pkg.substitute_bind_values
240 (p_bind_tab => p_bind_tab,
241 p_bind_format => 'SQL',
242 p_sql => l_debug_sql);
243 --
244 -- log debug sql and calling module
245 --
246 END IF;
247 --
248 --
249 -- Substitute binds if in SQL mode
250 --
251 IF (p_trend_sql_params.bind_format = 'SQL') THEN
252 hri_oltp_pmv_util_pkg.substitute_bind_values
253 (p_bind_tab => p_bind_tab,
254 p_bind_format => 'SQL',
255 p_sql => l_return_sql);
256 END IF;
257 --
258 RETURN l_return_sql;
259 --
260 END get_sql;
261 --
262 END hri_oltp_pmv_query_trend;
263