[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_WMS_PTS_TREND_PKG
Source
1 PACKAGE BODY ISC_DBI_WMS_PTS_TREND_PKG AS
2 /*$Header: ISCRGBSB.pls 120.0 2005/05/25 17:16:54 appldev noship $
3 /*----------------------------------------------------
4 Declare PRIVATE procedures and functions for package
5 -----------------------------------------------------*/
6
7 /* No subinventory dimension --> need all measures
8 from isc_wms_000_mv and isc_wms_001_mv */
9 FUNCTION get_sel_clause1(p_view_by_dim IN VARCHAR2)
10 RETURN VARCHAR2;
11
12 /* Subinventory dimension --> don't need to retrieve pick release measures,
13 only measures from isc_wms_001_mv */
14 FUNCTION get_sel_clause2(p_view_by_dim IN VARCHAR2)
15 RETURN VARCHAR2;
16
17 /*------------------------------------------------------
18 Trend Query for Pick Release To Ship Cycle Time Trend
19 -----------------------------------------------------*/
20 PROCEDURE get_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
21 x_custom_sql OUT NOCOPY VARCHAR2,
22 x_custom_output OUT NOCOPY
23 BIS_QUERY_ATTRIBUTES_TBL)
24 IS
25 l_query VARCHAR2(32767);
26 l_view_by VARCHAR2(120);
27 l_view_by_col VARCHAR2(120);
28 l_xtd VARCHAR2(10);
29 l_comparison_type VARCHAR2(1);
30 l_cur_suffix VARCHAR2(10);
31 l_custom_sql VARCHAR2(10000);
32
33 l_mv_tbl poa_dbi_util_pkg.POA_DBI_MV_TBL;
34 l_col_tbl1 poa_dbi_util_pkg.POA_DBI_COL_TBL;
35 l_col_tbl2 poa_dbi_util_pkg.POA_DBI_COL_TBL;
36 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
37 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
38 l_where_clause1 VARCHAR2 (2000);
39 l_where_clause2 VARCHAR2 (2000);
40 l_mv1 VARCHAR2 (30);
41 l_mv2 VARCHAR2 (30);
42
43 l_aggregation_level_flag1 VARCHAR2(10);
44 l_aggregation_level_flag2 VARCHAR2(10);
45
46 l_custom_rec BIS_QUERY_ATTRIBUTES;
47
48 BEGIN
49
50 -- initialization block
51 l_comparison_type := 'Y';
52 l_aggregation_level_flag1 := '0';
53 l_aggregation_level_flag2 := '0';
54
55 -- clear out the tables.
56 l_mv_tbl := poa_dbi_util_pkg.POA_DBI_MV_TBL ();
57 l_col_tbl1 := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
58 l_col_tbl2 := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
59 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
60
61 -- get all the query parameters
62
63 isc_dbi_sutil_pkg.process_parameters (
64 p_param => p_param,
65 p_view_by => l_view_by,
66 p_view_by_col_name => l_view_by_col,
67 p_comparison_type => l_comparison_type,
68 p_xtd => l_xtd,
69 p_cur_suffix => l_cur_suffix,
70 p_where_clause => l_where_clause2,
71 p_mv => l_mv2,
72 p_join_tbl => l_join_tbl,
73 p_mv_level_flag => l_aggregation_level_flag2,
74 p_trend => 'Y',
75 p_func_area => 'ISC',
76 p_version => '7.1',
77 p_role => '',
78 p_mv_set => 'RS2',
79 p_mv_flag_type => 'FLAG4',
80 p_in_join_tbl => l_in_join_tbl);
81
82 -- Add measure columns that need to be aggregated
83 -- No Grand totals required.
84 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2,
85 p_col_name => 'ship_confirm_cnt',
86 p_alias_name => 'ship_confirm_cnt',
87 p_grand_total => 'N',
88 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
89 p_to_date_type => 'RLX');
90
91 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2,
92 p_col_name => 'release_to_ship',
93 p_alias_name => 'release_to_ship',
94 p_grand_total => 'N',
95 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
96 p_to_date_type => 'RLX');
97
98 IF (l_where_clause2 NOT LIKE '%fact.subinventory%') THEN
99 isc_dbi_sutil_pkg.process_parameters (
100 p_param => p_param,
101 p_view_by => l_view_by,
102 p_view_by_col_name => l_view_by_col,
103 p_comparison_type => l_comparison_type,
104 p_xtd => l_xtd,
105 p_cur_suffix => l_cur_suffix,
106 p_where_clause => l_where_clause1,
107 p_mv => l_mv1,
108 p_join_tbl => l_join_tbl,
109 p_mv_level_flag => l_aggregation_level_flag1,
110 p_trend => 'Y',
111 p_func_area => 'ISC',
112 p_version => '7.1',
113 p_role => '',
114 p_mv_set => 'RS1',
115 p_mv_flag_type => 'FLAG5',
116 p_in_join_tbl => l_in_join_tbl);
117
118 -- Add measure columns that need to be aggregated
119 -- No Grand totals required.
120 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1,
121 p_col_name => 'pick_release_cnt',
122 p_alias_name => 'pick_release_cnt',
123 p_grand_total => 'N',
124 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
125 p_to_date_type => 'RLX');
126 END IF;
127
128 -- Merge Outer and Inner Query
129 IF (l_where_clause2 NOT LIKE '%fact.subinventory%') THEN
130
131 l_mv_tbl.extend;
132 l_mv_tbl(1).mv_name := l_mv1;
133 l_mv_tbl(1).mv_col := l_col_tbl1;
134 l_mv_tbl(1).mv_where := l_where_clause1;
135 l_mv_tbl(1).in_join_tbls := NULL;
136 l_mv_tbl(1).use_grp_id := 'N';
137 l_mv_tbl(1).mv_xtd := l_xtd;
138
139 l_mv_tbl.extend;
140 l_mv_tbl(2).mv_name := l_mv2;
141 l_mv_tbl(2).mv_col := l_col_tbl2;
142 l_mv_tbl(2).mv_where := l_where_clause2;
143 l_mv_tbl(2).in_join_tbls := NULL;
144 l_mv_tbl(2).use_grp_id := 'N';
145 l_mv_tbl(2).mv_xtd := l_xtd;
146
147 l_query := get_sel_clause1(l_view_by) ||
148 ' from ' ||
149 poa_dbi_template_pkg.union_all_trend_sql (
150 p_mv => l_mv_tbl,
151 p_comparison_type => l_comparison_type,
152 p_filter_where => NULL);
153
154 ELSE
155 l_query := get_sel_clause2(l_view_by) ||
156 ' from ' ||
157 poa_dbi_template_pkg.trend_sql (
158 p_xtd => l_xtd,
159 p_comparison_type => l_comparison_type,
160 p_fact_name => l_mv2,
161 p_where_clause => l_where_clause2,
162 p_col_name => l_col_tbl2,
163 p_use_grpid => 'N');
164
165 END IF;
166
167
168 -- Prepare PMV bind variables
169 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
170 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
171
172 -- get all the basic binds used by POA queries
173 -- Do this before adding any of our binds, since the procedure
174 -- reinitializes the output table
175 poa_dbi_util_pkg.get_custom_trend_binds (
176 p_xtd => l_xtd,
177 p_comparison_type => l_comparison_type,
178 x_custom_output => x_custom_output);
179
180 poa_dbi_util_pkg.get_custom_rolling_binds(x_custom_output,l_xtd);
181
182 -- Passing ISC_AGG_FLAGS to PMV
183
184 IF (l_where_clause2 NOT LIKE '%fact.subinventory%') THEN
185 l_custom_rec.attribute_name := ':ISC_AGG_FLAG2';
186 l_custom_rec.attribute_value := l_aggregation_level_flag1;
187 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
188 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
189 x_custom_output.extend;
190 x_custom_output(x_custom_output.count) := l_custom_rec;
191 END IF;
192
193 l_custom_rec.attribute_name := ':ISC_AGG_FLAG';
194 l_custom_rec.attribute_value := l_aggregation_level_flag2;
195 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
196 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
197 x_custom_output.extend;
198 x_custom_output(x_custom_output.count) := l_custom_rec;
199
200 x_custom_sql := l_query;
201
202 END get_sql;
203
204 /*--------------------------------------------------
205 Function: get_sel_clause1
206 Description: builds the outer select clause
207 ---------------------------------------------------*/
208
209 FUNCTION get_sel_clause1 (p_view_by_dim IN VARCHAR2)
210 RETURN VARCHAR2
211 IS
212 l_sel_clause varchar2(7500);
213 BEGIN
214
215 -- Main Outer query
216
217 l_sel_clause :=
218 'SELECT
219 cal_name VIEWBY,
220 p_pick_release_cnt ISC_MEASURE_1,
221 c_pick_release_cnt ISC_MEASURE_2,
222 ' || isc_dbi_sutil_pkg.change_str (
223 p_new_numerator => 'c_pick_release_cnt',
224 p_old_numerator => 'p_pick_release_cnt',
225 p_denominator => 'p_pick_release_cnt',
226 p_measure_name => 'ISC_MEASURE_3') || ',
227 p_ship_confirm_cnt ISC_MEASURE_4,
228 c_ship_confirm_cnt ISC_MEASURE_5,
229 ' || isc_dbi_sutil_pkg.change_str (
230 p_new_numerator => 'c_ship_confirm_cnt',
231 p_old_numerator => 'p_ship_confirm_cnt',
232 p_denominator => 'p_ship_confirm_cnt',
233 p_measure_name => 'ISC_MEASURE_6') || ',
234 CASE WHEN p_ship_confirm_cnt = 0 THEN to_number (NULL)
235 ELSE (p_release_to_ship*24/p_ship_confirm_cnt)
236 END ISC_MEASURE_7,
237 CASE WHEN c_ship_confirm_cnt = 0 THEN to_number (NULL)
238 ELSE (c_release_to_ship*24/c_ship_confirm_cnt)
239 END ISC_MEASURE_8,
240 CASE WHEN c_ship_confirm_cnt = 0 THEN to_number(NULL)
241 WHEN p_ship_confirm_cnt = 0 THEN to_number(NULL)
242 ELSE ((c_release_to_ship*24/c_ship_confirm_cnt
243 - p_release_to_ship*24/p_ship_confirm_cnt))
244 END ISC_MEASURE_9
245 ';
246
247 RETURN l_sel_clause;
248
249 END get_sel_clause1;
250
251
252 /*--------------------------------------------------
253 Function: get_sel_clause2
254 Description: builds the outer select clause
255 ---------------------------------------------------*/
256
257 FUNCTION get_sel_clause2 (p_view_by_dim IN VARCHAR2)
258 RETURN VARCHAR2
259 IS
260 l_sel_clause varchar2(7500);
261 BEGIN
262
263 -- Main Outer query
264
265 l_sel_clause :=
266 'SELECT
267 cal.name VIEWBY,
268 NULL ISC_MEASURE_1,
269 NULL ISC_MEASURE_2,
270 NULL ISC_MEASURE_3,
271 p_ship_confirm_cnt ISC_MEASURE_4,
272 c_ship_confirm_cnt ISC_MEASURE_5,
273 ' || isc_dbi_sutil_pkg.change_str (
274 p_new_numerator => 'c_ship_confirm_cnt',
275 p_old_numerator => 'p_ship_confirm_cnt',
276 p_denominator => 'p_ship_confirm_cnt',
277 p_measure_name => 'ISC_MEASURE_6') || ',
278 CASE WHEN p_ship_confirm_cnt = 0 THEN to_number (NULL)
279 ELSE (p_release_to_ship*24/p_ship_confirm_cnt)
280 END ISC_MEASURE_7,
281 CASE WHEN c_ship_confirm_cnt = 0 THEN to_number (NULL)
282 ELSE (c_release_to_ship*24/c_ship_confirm_cnt)
283 END ISC_MEASURE_8,
284 CASE WHEN c_ship_confirm_cnt = 0 THEN to_number(NULL)
285 WHEN p_ship_confirm_cnt = 0 THEN to_number(NULL)
286 ELSE ((c_release_to_ship*24/c_ship_confirm_cnt
287 - p_release_to_ship*24/p_ship_confirm_cnt))
288 END ISC_MEASURE_9
289 ';
290
291 RETURN l_sel_clause;
292
293 END get_sel_clause2;
294
295 END ISC_DBI_WMS_PTS_TREND_PKG;