[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_OT_ARR_RT_TR_PKG
Source
1 PACKAGE BODY ISC_DBI_OT_ARR_RT_TR_PKG AS
2 /*$Header: ISCRGBXB.pls 120.1 2006/06/26 06:45:27 abhdixi noship $
3 /*----------------------------------------------------
4 Declare PRIVATE procedures and functions for package
5 -----------------------------------------------------*/
6 /* On-Time Arrival Rate Trend */
7 FUNCTION get_trd_sel_clause(p_view_by_dim IN VARCHAR2)
8 RETURN VARCHAR2;
9
10
11 /*------------------------------------------------
12 On-Time Arrival Rate Trend Function
13 -------------------------------------------------*/
14 PROCEDURE get_trd_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
15 x_custom_sql OUT NOCOPY VARCHAR2,
16 x_custom_output OUT NOCOPY
17 BIS_QUERY_ATTRIBUTES_TBL)
18 IS
19 l_query VARCHAR2(32767);
20 l_view_by VARCHAR2(120);
21 l_view_by_col VARCHAR2 (120);
22 l_xtd VARCHAR2(10);
23 l_comparison_type VARCHAR2(1);
24 l_cur_suffix VARCHAR2(10);
25 l_currency VARCHAR2(10);
26 l_custom_sql VARCHAR2 (10000);
27
28 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
29 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
30 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
31
32 l_where_clause VARCHAR2 (2000);
33 l_mv VARCHAR2 (30);
34
35 l_aggregation_level_flag VARCHAR2(10);
36
37 l_custom_rec BIS_QUERY_ATTRIBUTES;
38
39 BEGIN
40
41 -- initialization block
42 l_comparison_type := 'Y';
43 l_aggregation_level_flag := '0';
44
45 -- clear out the tables.
46 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
47 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
48 x_custom_sql := l_query;
49
50 -- get all the query parameters
51 isc_dbi_sutil_pkg.process_parameters (
52 p_param => p_param,
53 p_view_by => l_view_by,
54 p_view_by_col_name => l_view_by_col,
55 p_comparison_type => l_comparison_type,
56 p_xtd => l_xtd,
57 p_cur_suffix => l_cur_suffix,
58 p_where_clause => l_where_clause,
59 p_mv => l_mv,
60 p_join_tbl => l_join_tbl,
61 p_mv_level_flag =>l_aggregation_level_flag,
62 p_trend => 'Y',
63 p_func_area => 'ISC',
64 p_version => '7.1',
65 p_role => '',
66 p_mv_set => 'BX1',
67 p_mv_flag_type => 'FLAG2',
68 p_in_join_tbl => l_in_join_tbl);
69
70 -- Add measure columns that need to be aggregated
71 -- No Grand totals required.
72 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
73 p_col_name => 'trip_arrivals',
74 p_alias_name => 'trip_arrivals',
75 p_grand_total => 'N',
76 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
77 p_to_date_type => 'XTD');
78
79 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
80 p_col_name => 'stop_arrivals',
81 p_alias_name => 'stop_arrivals',
82 p_grand_total => 'N',
83 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
84 p_to_date_type => 'XTD');
85
86 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
87 p_col_name => 'on_time_stop_arrivals',
88 p_alias_name => 'ot_arrivals',
89 p_grand_total => 'N',
90 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
91 p_to_date_type => 'XTD');
92
93 -- Merge Outer and Inner Query
94 l_query := get_trd_sel_clause(l_view_by) ||
95 ' from ' ||
96 poa_dbi_template_pkg.trend_sql (
97 p_xtd => l_xtd,
98 p_comparison_type => l_comparison_type,
99 p_fact_name => l_mv,
100 p_where_clause => l_where_clause,
101 p_col_name => l_col_tbl,
102 p_use_grpid => 'N');
103
104
105 -- Prepare PMV bind variables
106 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
107 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
108
109 -- get all the basic binds used by POA queries
110 -- Do this before adding any of our binds, since the procedure
111 -- reinitializes the output table
112 poa_dbi_util_pkg.get_custom_trend_binds (
113 p_xtd => l_xtd,
114 p_comparison_type => l_comparison_type,
115 x_custom_output => x_custom_output);
116
117 -- Passing ISC_AGGREGATION_LEVEL_FLAG to PMV
118 l_custom_rec.attribute_name := ':ISC_AGG_FLAG';
119 l_custom_rec.attribute_value := l_aggregation_level_flag;
120 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
121 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
122 x_custom_output.extend;
123 x_custom_output(x_custom_output.count) := l_custom_rec;
124
125 x_custom_sql := l_query;
126
127 END get_trd_sql;
128
129 /*--------------------------------------------------
130 Function: get_trd_sel_clause
131 Description: builds the outer select clause
132 ---------------------------------------------------*/
133
134 FUNCTION get_trd_sel_clause (p_view_by_dim IN VARCHAR2)
135 RETURN VARCHAR2
136 IS
137 l_sel_clause varchar2(7500);
138 BEGIN
139
140 -- Main Outer query
141
142 l_sel_clause :=
143 'SELECT
144 ' || ' cal.name VIEWBY,
145 nvl(c_trip_arrivals,0) ISC_MEASURE_1,
146 ' || isc_dbi_sutil_pkg.change_str (
147 p_new_numerator => 'c_trip_arrivals',
148 p_old_numerator => 'p_trip_arrivals',
149 p_denominator => 'p_trip_arrivals',
150 p_measure_name => 'ISC_MEASURE_2') || ', -- Trip Arrivals Change
151 nvl(p_stop_arrivals,0) ISC_MEASURE_3,
152 nvl(c_stop_arrivals,0) ISC_MEASURE_4,
153 ' || isc_dbi_sutil_pkg.change_str (
154 p_new_numerator => 'c_stop_arrivals',
155 p_old_numerator => 'p_stop_arrivals',
156 p_denominator => 'p_stop_arrivals',
157 p_measure_name => 'ISC_MEASURE_5') || ', -- Trip Stop Arrivals Change
158 nvl(p_ot_arrivals,0) ISC_MEASURE_8,
159 nvl(c_ot_arrivals,0) ISC_MEASURE_6,
160 ' || isc_dbi_sutil_pkg.change_str (
161 p_new_numerator => 'c_trip_arrivals',
162 p_old_numerator => 'p_trip_arrivals',
163 p_denominator => 'p_trip_arrivals',
164 p_measure_name => 'ISC_MEASURE_7') || ', -- On-Time Trip Stop Arrivals Change
165 ' || isc_dbi_sutil_pkg.rate_str (
166 p_numerator => 'p_ot_arrivals',
167 p_denominator => 'p_stop_arrivals',
168 p_rate_type => 'PERCENT',
169 p_measure_name => 'ISC_MEASURE_9') || ', -- OT Arrival Rate Prior
170 ' || isc_dbi_sutil_pkg.rate_str (
171 p_numerator => 'c_ot_arrivals',
172 p_denominator => 'c_stop_arrivals',
173 p_rate_type => 'PERCENT',
174 p_measure_name => 'ISC_MEASURE_10') || ', -- OT Arrival Rate
175 ' || isc_dbi_sutil_pkg.change_rate_str (
176 p_new_numerator => 'c_ot_arrivals',
177 p_new_denominator => 'c_stop_arrivals',
178 p_old_numerator => 'p_ot_arrivals',
179 p_old_denominator => 'p_stop_arrivals',
180 p_rate_type => 'PERCENT',
181 p_measure_name => 'ISC_MEASURE_11'); -- OT Arrival Rate Change
182
183 RETURN l_sel_clause;
184
185 END get_trd_sel_clause;
186
187 END ISC_DBI_OT_ARR_RT_TR_PKG;