[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_TS_ARR_PERF_TR_PKG
Source
1 PACKAGE BODY ISC_DBI_TS_ARR_PERF_TR_PKG AS
2 /*$Header: ISCRGBPB.pls 120.1 2006/06/26 06:47:49 abhdixi noship $
3 /*----------------------------------------------------
4 Declare PRIVATE procedures and functions for package
5 -----------------------------------------------------*/
6 /* Trend Report */
7 FUNCTION get_trd_sel_clause(p_view_by_dim IN VARCHAR2)
8 RETURN VARCHAR2;
9
10
11 /*----------------------------------------
12 Trend Report 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_xtd1 VARCHAR2(10);
23 l_xtd2 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_tbl1 poa_dbi_util_pkg.POA_DBI_COL_TBL;
30 l_col_tbl2 poa_dbi_util_pkg.POA_DBI_COL_TBL;
31 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
32 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
33
34 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
35
36 l_where_clause VARCHAR2 (2000);
37 l_mv1 VARCHAR2 (30);
38 l_mv2 VARCHAR2 (30);
39
40 l_aggregation_level_flag1 VARCHAR2(10);
41 l_aggregation_level_flag2 VARCHAR2(10);
42
43 l_custom_rec BIS_QUERY_ATTRIBUTES;
44
45 BEGIN
46
47 -- initialization block
48 l_comparison_type := 'Y';
49 l_aggregation_level_flag1 := '0';
50 l_aggregation_level_flag2 := '0';
51
52 -- clear out the tables.
53 l_col_tbl1 := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
54 l_col_tbl2 := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
55 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
56 x_custom_sql := l_query;
57
58 -- get all the query parameters
59 isc_dbi_sutil_pkg.process_parameters (
60 p_param => p_param,
61 p_view_by => l_view_by,
62 p_view_by_col_name => l_view_by_col,
63 p_comparison_type => l_comparison_type,
64 p_xtd => l_xtd1,
65 p_cur_suffix => l_cur_suffix,
66 p_where_clause => l_where_clause,
67 p_mv => l_mv1,
68 p_join_tbl => l_join_tbl,
69 p_mv_level_flag => l_aggregation_level_flag1,
70 p_trend => 'Y',
71 p_func_area => 'ISC',
72 p_version => '7.1',
73 p_role => '',
74 p_mv_set => 'BP1',
75 p_mv_flag_type => 'FLAG2',
76 p_in_join_tbl => l_in_join_tbl);
77
78 -- Add measure columns that need to be aggregated
79 -- No Grand totals required.
80
81 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1,
82 p_col_name => 'stop_arrivals',
83 p_alias_name => 'stop_arrivals',
84 p_grand_total => 'N',
85 p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
86 p_to_date_type => 'XTD');
87
88 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1,
89 p_col_name => 'early_stop_arrivals',
90 p_alias_name => 'early_arrivals',
91 p_grand_total => 'N',
92 p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
93 p_to_date_type => 'XTD');
94
95 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1,
96 p_col_name => 'late_stop_arrivals',
97 p_alias_name => 'late_arrivals',
98 p_grand_total => 'N',
99 p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
100 p_to_date_type => 'XTD');
101
102 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1,
103 p_col_name => 'on_time_stop_arrivals',
104 p_alias_name => 'ot_arrivals',
105 p_grand_total => 'N',
106 p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
107 p_to_date_type => 'XTD');
108
109 isc_dbi_sutil_pkg.process_parameters (
110 p_param => p_param,
111 p_view_by => l_view_by,
112 p_view_by_col_name => l_view_by_col,
113 p_comparison_type => l_comparison_type,
114 p_xtd => l_xtd2,
115 p_cur_suffix => l_cur_suffix,
116 p_where_clause => l_where_clause,
117 p_mv => l_mv2,
118 p_join_tbl => l_join_tbl,
119 p_mv_level_flag => l_aggregation_level_flag2,
120 p_trend => 'Y',
121 p_func_area => 'ISC',
122 p_version => '7.1',
123 p_role => '',
124 p_mv_set => 'BP2',
125 p_mv_flag_type => 'FLAG2',
126 p_in_join_tbl => l_in_join_tbl);
127
128 -- Add measure columns that need to be aggregated
129 -- No Grand totals required.
130 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2,
131 p_col_name => 'planned_stop_arrivals',
132 p_alias_name => 'plan_arrivals',
133 p_grand_total => 'N',
134 p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
135 p_to_date_type => 'XTD');
136
137
138 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
139
140 l_mv_tbl.extend;
141 l_mv_tbl(1).mv_name := l_mv1;
142 l_mv_tbl(1).mv_col := l_col_tbl1;
143 l_mv_tbl(1).mv_where := l_where_clause;
144 l_mv_tbl(1).in_join_tbls := NULL;
145 l_mv_tbl(1).use_grp_id := 'N';
146 l_mv_tbl(1).mv_xtd := l_xtd1;
147
148 l_mv_tbl.extend;
149 l_mv_tbl(2).mv_name := l_mv2;
150 l_mv_tbl(2).mv_col := l_col_tbl2;
151 l_mv_tbl(2).mv_where := l_where_clause;
152 l_mv_tbl(2).in_join_tbls := NULL;
153 l_mv_tbl(2).use_grp_id := 'N';
154 l_mv_tbl(2).mv_xtd := l_xtd2;
155
156 -- Merge Outer and Inner Query
157 l_query := get_trd_sel_clause(l_view_by) ||
158 ' from ' ||
159 poa_dbi_template_pkg.union_all_trend_sql (
160 p_mv => l_mv_tbl,
161 p_comparison_type => l_comparison_type,
162
163 p_filter_where => NULL
164 );
165
166
167 -- Prepare PMV bind variables
168 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
169 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
170
171 -- get all the basic binds used by POA queries
172 -- Do this before adding any of our binds, since the procedure
173 -- reinitializes the output table
174 poa_dbi_util_pkg.get_custom_trend_binds (
175 p_xtd => l_xtd2,
176 p_comparison_type => l_comparison_type,
177 x_custom_output => x_custom_output);
178
179 -- Passing ISC_AGGREGATION_LEVEL_FLAG to PMV
180 l_custom_rec.attribute_name := ':ISC_AGG_FLAG';
181 l_custom_rec.attribute_value := l_aggregation_level_flag1;
182 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
183 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
184 x_custom_output.extend;
185 x_custom_output(x_custom_output.count) := l_custom_rec;
186
187 x_custom_sql := l_query;
188
189 END get_trd_sql;
190
191 /*--------------------------------------------------
192 Function: get_trd_sel_clause
193 Description: builds the outer select clause
194 ---------------------------------------------------*/
195
196 FUNCTION get_trd_sel_clause (p_view_by_dim IN VARCHAR2)
197 RETURN VARCHAR2
198 IS
199 l_sel_clause varchar2(7500);
200 BEGIN
201
202 -- Main Outer query
203
204 l_sel_clause :=
205 'SELECT
206 ' || ' cal_name VIEWBY,
207 nvl(c_ot_arrivals,0) ISC_MEASURE_1,
208 ' || isc_dbi_sutil_pkg.rate_str (
209 p_numerator => 'c_ot_arrivals',
210 p_denominator => 'c_stop_arrivals',
211 p_rate_type => 'PERCENT',
212 p_measure_name => 'ISC_MEASURE_2') || ', -- OT Arrival Rate
213 nvl(c_late_arrivals,0) ISC_MEASURE_3,
214 ' || isc_dbi_sutil_pkg.rate_str (
215 p_numerator => 'c_late_arrivals',
216 p_denominator => 'c_stop_arrivals',
217 p_rate_type => 'PERCENT',
218 p_measure_name => 'ISC_MEASURE_4') || ', -- Late Arrival Rate
219 nvl(c_early_arrivals,0) ISC_MEASURE_5,
220 ' || isc_dbi_sutil_pkg.rate_str (
221 p_numerator => 'c_early_arrivals',
222 p_denominator => 'c_stop_arrivals',
223 p_rate_type => 'PERCENT',
224 p_measure_name => 'ISC_MEASURE_6') || ', -- Early Arrival Rate
225 nvl(c_stop_arrivals,0) ISC_MEASURE_7,
226 nvl(c_plan_arrivals,0) ISC_MEASURE_8,
227 ' || isc_dbi_sutil_pkg.rate_str (
228 p_numerator => 'c_stop_arrivals',
229 p_denominator => 'c_plan_arrivals',
230 p_rate_type => 'PERCENT',
231 p_measure_name => 'ISC_MEASURE_9'); -- Trip Stop Arrivals to Plan
232
233 RETURN l_sel_clause;
234
235 END get_trd_sel_clause;
236
237 END ISC_DBI_TS_ARR_PERF_TR_PKG;