[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_OT_ARR_RT_PKG
Source
1 PACKAGE BODY ISC_DBI_OT_ARR_RT_PKG AS
2 /*$Header: ISCRGBWB.pls 120.0 2005/05/25 17:38:07 appldev noship $
3 /*----------------------------------------------------
4 Declare PRIVATE procedures and functions for package
5 -----------------------------------------------------*/
6 /* On-Time Arrival Rate Report */
7 FUNCTION get_rpt_sel_clause (p_view_by_dim IN VARCHAR2,
8 p_join_tbl IN
9 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
10 RETURN VARCHAR2;
11
12
13 /*------------------------------------------------
14 On-Time Arrival Rate Report Function
15 -------------------------------------------------*/
16 PROCEDURE get_tbl_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
17 x_custom_sql OUT NOCOPY VARCHAR2,
18 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
19 IS
20 l_query VARCHAR2(32767);
21 l_view_by VARCHAR2(120);
22 l_view_by_col VARCHAR2 (120);
23 l_xtd VARCHAR2(10);
24 l_comparison_type VARCHAR2(1);
25 l_cur_suffix VARCHAR2(10);
26 l_currency VARCHAR2(10);
27 l_custom_sql VARCHAR2 (10000);
28
29 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
30 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
31 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
32
33 l_where_clause VARCHAR2 (2000);
34 l_filter_where VARCHAR2 (240);
35 l_mv VARCHAR2 (30);
36
37 l_aggregation_level_flag VARCHAR2(10);
38
39 l_custom_rec BIS_QUERY_ATTRIBUTES;
40
41 BEGIN
42
43 -- initialization block
44 l_comparison_type := 'Y';
45 l_aggregation_level_flag := '0';
46
47 -- clear out the column and Join info tables.
48 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
49 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
50
51 -- get all the query parameters
52 isc_dbi_sutil_pkg.process_parameters (
53 p_param => p_param,
54 p_view_by => l_view_by,
55 p_view_by_col_name => l_view_by_col,
56 p_comparison_type => l_comparison_type,
57 p_xtd => l_xtd,
58 p_cur_suffix => l_cur_suffix,
59 p_where_clause => l_where_clause,
60 p_mv => l_mv,
61 p_join_tbl => l_join_tbl,
62 p_mv_level_flag => l_aggregation_level_flag,
63 p_trend => 'N',
64 p_func_area => 'ISC',
65 p_version => '7.1',
66 p_role => '',
67 p_mv_set => 'BW1',
68 p_mv_flag_type => 'FLAG2',
69 p_in_join_tbl => l_in_join_tbl);
70
71
72 -- Add measure columns that need to be aggregated
73 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
74 p_col_name => 'trip_arrivals',
75 p_alias_name => 'trip_arrivals',
76 p_grand_total => 'Y',
77 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
78 p_to_date_type => 'XTD');
79
80 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
81 p_col_name => 'stop_arrivals',
82 p_alias_name => 'stop_arrivals',
83 p_grand_total => 'Y',
84 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
85 p_to_date_type => 'XTD');
86
87 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
88 p_col_name => 'early_stop_arrivals',
89 p_alias_name => 'early_arrivals',
90 p_grand_total => 'Y',
91 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
92 p_to_date_type => 'XTD');
93
94 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
95 p_col_name => 'late_stop_arrivals',
96 p_alias_name => 'late_arrivals',
97 p_grand_total => 'Y',
98 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
99 p_to_date_type => 'XTD');
100
101 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
102 p_col_name => 'on_time_stop_arrivals',
103 p_alias_name => 'ot_arrivals',
104 p_grand_total => 'Y',
105 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
106 p_to_date_type => 'XTD');
107
108 /* Additional filter needed to avoid displaying records queried due to total values at node */
109
110 -- l_filter_where := ' ISC_MEASURE_4 IS NOT NULL AND ISC_MEASURE_1 IS NOT NULL';
111
112 -- construct the query
113 l_query := get_rpt_sel_clause (l_view_by, l_join_tbl)
114 || ' from
115 ' || poa_dbi_template_pkg.status_sql (p_fact_name => l_mv,
116 p_where_clause => l_where_clause,
117 p_join_tables => l_join_tbl,
118 p_use_windowing => 'Y',
119 p_col_name => l_col_tbl,
120 p_use_grpid => 'N',
121 p_paren_count => 3,
122 p_filter_where => l_filter_where,
123 p_generate_viewby => 'Y',
124 p_in_join_tables => NULL);
125
126 -- prepare output for bind variables
127 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
128 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
129
130 -- set the basic bind variables for the status SQL
131 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
132
133 -- Passing ISC_AGG_FLAG to PMV
134 l_custom_rec.attribute_name := ':ISC_AGG_FLAG';
135 l_custom_rec.attribute_value := l_aggregation_level_flag;
136 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
137 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
138 x_custom_output.extend;
139 x_custom_output(x_custom_output.count) := l_custom_rec;
140
141 x_custom_sql := l_query;
142
143 END get_tbl_sql;
144
145
146 /*--------------------------------------------------
147 Function: get_rtp_sel_clause
148 Description: builds the outer select clause
149 ---------------------------------------------------*/
150 FUNCTION get_rpt_sel_clause(p_view_by_dim IN VARCHAR2,
151 p_join_tbl IN
152 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
153 RETURN VARCHAR2
154 IS
155 l_sel_clause VARCHAR2(32000);
156 l_view_by_col_name VARCHAR2(60);
157 l_description VARCHAR2(30);
158 l_drill_across_rep_1 VARCHAR2(50);
159 l_drill_across_rep_2 VARCHAR2(50);
160 l_view_by_fact_col VARCHAR2(400);
161 l_drill_across VARCHAR2(1000);
162
163 BEGIN
164
165 -- initialization block
166
167 -- Column to get view by column name
168 l_view_by_col_name := isc_dbi_sutil_pkg.get_view_by_col_name (p_view_by_dim);
169
170 -- fact column view by's
171 l_view_by_fact_col := isc_dbi_sutil_pkg.get_fact_select_columns (p_join_tbl);
172
173 -- Outer select clause
174 l_sel_clause :=
175 'SELECT
176 ' || isc_dbi_sutil_pkg.get_view_by_select_clause (p_view_by_dim)
177 || 'oset.ISC_MEASURE_1 ISC_MEASURE_1,
178 ' || 'oset.ISC_MEASURE_2 ISC_MEASURE_2,
179 ' || 'oset.ISC_MEASURE_3 ISC_MEASURE_3,
180 ' || 'oset.ISC_MEASURE_4 ISC_MEASURE_4,
181 ' || 'oset.ISC_MEASURE_5 ISC_MEASURE_5,
182 ' || 'oset.ISC_MEASURE_6 ISC_MEASURE_6,
183 ' || 'oset.ISC_MEASURE_7 ISC_MEASURE_7,
184 ' || 'oset.ISC_MEASURE_8 ISC_MEASURE_8,
185 ' || 'oset.ISC_MEASURE_9 ISC_MEASURE_9,
186 ' || 'oset.ISC_MEASURE_10 ISC_MEASURE_10,
187 ' || 'oset.ISC_MEASURE_11 ISC_MEASURE_11,
188 ' || 'oset.ISC_MEASURE_12 ISC_MEASURE_12,
189 ' || 'oset.ISC_MEASURE_13 ISC_MEASURE_13,
190 ' || 'oset.ISC_MEASURE_14 ISC_MEASURE_14,
191 ' || 'oset.ISC_MEASURE_15 ISC_MEASURE_15,
192 ' || 'oset.ISC_MEASURE_16 ISC_MEASURE_16,
193 ' || 'oset.ISC_MEASURE_17 ISC_MEASURE_17,
194 ' || 'oset.ISC_MEASURE_18 ISC_MEASURE_18,
195 ' || 'oset.ISC_MEASURE_19 ISC_MEASURE_19,
196 ' || 'oset.ISC_MEASURE_20 ISC_MEASURE_20,
197 ' || 'oset.ISC_MEASURE_22 ISC_MEASURE_22
198 ' || 'FROM
199 ' || '(SELECT (rank () over
200 ' || ' (&ORDER_BY_CLAUSE nulls last,
201 ' || l_view_by_fact_col || ')) - 1 rnk,
202 ' || l_view_by_fact_col || ',
203 ' || 'ISC_MEASURE_1,ISC_MEASURE_2,ISC_MEASURE_3,ISC_MEASURE_4,ISC_MEASURE_5,
204 ISC_MEASURE_6,ISC_MEASURE_7,ISC_MEASURE_8,ISC_MEASURE_9,ISC_MEASURE_10,
205 ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13,ISC_MEASURE_14,ISC_MEASURE_15,
206 ISC_MEASURE_16,ISC_MEASURE_17,ISC_MEASURE_18,ISC_MEASURE_19,ISC_MEASURE_20,ISC_MEASURE_22
207 ' || 'FROM
208 ' || '(SELECT
209 ' || l_view_by_fact_col || ',
210 nvl(c_trip_arrivals,0) ISC_MEASURE_1,
211 nvl(p_stop_arrivals,0) ISC_MEASURE_3,
212 nvl(c_stop_arrivals,0) ISC_MEASURE_4,
213 nvl(c_ot_arrivals,0) ISC_MEASURE_6,
214 nvl(c_late_arrivals,0) ISC_MEASURE_7,
215 nvl(c_early_arrivals,0) ISC_MEASURE_8,
216 nvl(c_trip_arrivals_total,0) ISC_MEASURE_12,
217 nvl(c_stop_arrivals_total,0) ISC_MEASURE_14,
218 nvl(c_early_arrivals_total,0) ISC_MEASURE_16,
219 nvl(c_late_arrivals_total,0) ISC_MEASURE_17,
220 nvl(c_ot_arrivals_total,0) ISC_MEASURE_18,
221 ' || isc_dbi_sutil_pkg.change_str (
222 p_new_numerator => 'c_trip_arrivals',
223 p_old_numerator => 'p_trip_arrivals',
224 p_denominator => 'p_trip_arrivals',
225 p_measure_name => 'ISC_MEASURE_2') || ', -- Trip Arrivals Change
226 ' || isc_dbi_sutil_pkg.change_str (
227 p_new_numerator => 'c_stop_arrivals',
228 p_old_numerator => 'p_stop_arrivals',
229 p_denominator => 'p_stop_arrivals',
230 p_measure_name => 'ISC_MEASURE_5') || ', -- Trip Stop Arrivals Change
231 ' || isc_dbi_sutil_pkg.rate_str (
232 p_numerator => 'p_ot_arrivals',
233 p_denominator => 'p_stop_arrivals',
234 p_rate_type => 'PERCENT',
235 p_measure_name => 'ISC_MEASURE_9') || ', -- OT Arrival Rate Prior
236 ' || isc_dbi_sutil_pkg.rate_str (
237 p_numerator => 'p_ot_arrivals_total',
238 p_denominator => 'p_stop_arrivals_total',
239 p_rate_type => 'PERCENT',
240 p_measure_name => 'ISC_MEASURE_22') || ', -- Grand OT Arrival Rate Prior
241 ' || isc_dbi_sutil_pkg.rate_str (
242 p_numerator => 'c_ot_arrivals',
243 p_denominator => 'c_stop_arrivals',
244 p_rate_type => 'PERCENT',
245 p_measure_name => 'ISC_MEASURE_10') || ', -- OT Arrival Rate
246 ' || isc_dbi_sutil_pkg.change_rate_str (
247 p_new_numerator => 'c_ot_arrivals',
248 p_new_denominator => 'c_stop_arrivals',
249 p_old_numerator => 'p_ot_arrivals',
250 p_old_denominator => 'p_stop_arrivals',
251 p_rate_type => 'PERCENT',
252 p_measure_name => 'ISC_MEASURE_11') || ', -- OT Arrival Rate Change
253 ' || isc_dbi_sutil_pkg.change_str (
254 p_new_numerator => 'c_trip_arrivals_total',
255 p_old_numerator => 'p_trip_arrivals_total',
256 p_denominator => 'p_trip_arrivals_total',
257 p_measure_name => 'ISC_MEASURE_13') || ', -- Grand Total Trip Arrivals Change
258 ' || isc_dbi_sutil_pkg.change_str (
259 p_new_numerator => 'c_stop_arrivals_total',
260 p_old_numerator => 'p_stop_arrivals_total',
261 p_denominator => 'p_stop_arrivals_total',
262 p_measure_name => 'ISC_MEASURE_15') || ', -- Grand Total Trip Stop Arrivals Change
263 ' || isc_dbi_sutil_pkg.rate_str (
264 p_numerator => 'c_ot_arrivals_total',
265 p_denominator => 'c_stop_arrivals_total',
266 p_rate_type => 'PERCENT',
267 p_measure_name => 'ISC_MEASURE_19') || ', -- Grand Total OT Arrival Rate
268 ' || isc_dbi_sutil_pkg.change_rate_str (
269 p_new_numerator => 'c_ot_arrivals_total',
270 p_new_denominator => 'c_stop_arrivals_total',
271 p_old_numerator => 'p_ot_arrivals_total',
272 p_old_denominator => 'p_stop_arrivals_total',
273 p_rate_type => 'PERCENT',
274 p_measure_name => 'ISC_MEASURE_20'); -- Grand Total OT Arrival Rate Change
275
276 RETURN l_sel_clause;
277
278 END get_rpt_sel_clause;
279
280
281 END ISC_DBI_OT_ARR_RT_PKG;