[Home] [Help]
PACKAGE BODY: APPS.ISC_DEPOT_BACKLOG_PKG
Source
1 PACKAGE BODY ISC_DEPOT_BACKLOG_PKG AS
2 --$Header: iscdepotbklgrqb.pls 120.1 2005/08/25 05:20:41 visgupta noship $
3
4 FUNCTION GET_DRILL_ACROSS (p_view_by_dim IN VARCHAR2,p_function_name IN VARCHAR2)
5 RETURN VARCHAR2;
6
7 FUNCTION GET_BACKLOG_TBL_SEL_CLAUSE ( p_view_by_dim IN VARCHAR2
8 ,p_view_by_col IN VARCHAR2)
9 RETURN VARCHAR2;
10
11 FUNCTION GET_BACKLOG_TRD_SEL_CLAUSE (p_view_by_dim IN VARCHAR2)
12 RETURN VARCHAR2;
13
14 FUNCTION GET_DAYS_UNTIL_PROM_SEL_CLAUSE( p_view_by_dim IN VARCHAR2
15 , p_bucket_rec IN bis_bucket_pub.bis_bucket_rec_type
16 ,p_view_by_col IN VARCHAR2)
17 RETURN VARCHAR2;
18
19 PROCEDURE GET_BACKLOG_TBL_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
20 x_custom_sql OUT NOCOPY VARCHAR2,
21 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
22 IS
23 l_query ISC_DEPOT_RPT_UTIL_PKG.g_query_typ%type;
24 l_view_by ISC_DEPOT_RPT_UTIL_PKG.g_view_by_typ%type;
25 l_view_by_col ISC_DEPOT_RPT_UTIL_PKG.g_view_by_col_typ%type;
26 l_xtd ISC_DEPOT_RPT_UTIL_PKG.g_xtd_typ%type;
27 l_comparison_type VARCHAR2(1) ;
28 l_cur_suffix VARCHAR2(2);
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_where_clause ISC_DEPOT_RPT_UTIL_PKG.g_where_clause_typ%type;
32 l_mv ISC_DEPOT_RPT_UTIL_PKG.g_mv_typ%type;
33 l_mv_set VARCHAR2(50);
34 l_aggregation_flag NUMBER;
35 l_custom_rec BIS_QUERY_ATTRIBUTES;
36 l_mv_type VARCHAR2(10);
37 l_err_stage VARCHAR2(32767);
38 l_debug_mode VARCHAR2(1);
39 l_module_name ISC_DEPOT_RPT_UTIL_PKG.g_module_name_typ%type ;
40 l_last_refresh_date DATE;
41
42 BEGIN
43 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
44 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
45 l_comparison_type := 'Y';
46 l_last_refresh_date := bis_submit_requestset.get_last_refreshdate('REPORT','APPS','ISC_DEPOT_BACKLOG_TBL') ;
47
48 -- clear out the tables.
49 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
50 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
51
52 -- get all the query parameters
53 ISC_DEPOT_RPT_UTIL_PKG.process_parameters ( p_param => p_param,
54 x_view_by => l_view_by,
55 x_view_by_col_name => l_view_by_col,
56 x_comparison_type => l_comparison_type,
57 x_xtd => l_xtd,
58 x_cur_suffix => l_cur_suffix,
59 x_where_clause => l_where_clause,
60 x_mv => l_mv,
61 x_join_tbl => l_join_tbl,
62 x_mv_type => l_mv_type,
63 x_aggregation_flag => l_aggregation_flag,
64 p_trend => 'N',
65 p_mv_set => 'BKLG',
66 x_custom_output => x_custom_output);
67
68 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
69 l_err_stage:='After calling DRM_DBI_RPT_UTIL_PKG.process_parameters';
70 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_BACKLOG_TBL : ' ,l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
71 END IF;
72
73 -- Add measure columns that need to be aggregated
74 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
75 p_col_name => '(OPEN_COUNT - CLOSE_COUNT)' ,
76 p_alias_name => 'backlog',
77 p_grand_total => 'Y',
78 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
79 p_to_date_type => 'YTD');
80
81 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
82 p_col_name => '(PAST_DUE_OPEN_COUNT - LATE_COMPLETE_COUNT)' ,
83 p_alias_name => 'past_due',
84 p_grand_total => 'Y',
85 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
86 p_to_date_type => 'YTD');
87
88
89
90 l_query := GET_BACKLOG_TBL_SEL_CLAUSE ( p_view_by_dim => l_view_by
91 ,p_view_by_col => l_view_by_col)
92 || ' from
93 ' || poa_dbi_template_pkg.status_sql (p_fact_name => l_mv,
94 p_where_clause => l_where_clause,
95 p_join_tables => l_join_tbl,
96 p_use_windowing => 'Y',
97 p_col_name => l_col_tbl,
98 p_use_grpid => 'N',
99 p_paren_count => 3,
100 p_filter_where => ' (BIV_MEASURE1 > 0 or BIV_MEASURE11 > 0 or BIV_MEASURE12 > 0 or BIV_MEASURE3 > 0)',
101 p_generate_viewby => 'Y',
102 p_in_join_tables => NULL);
103
104 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
105 l_err_stage:='The query is : ' || l_query;
106 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_BACKLOG_TBL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
107 END IF;
108
109 -- prepare output for bind variables
110 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
111 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
112
113 -- set the basic bind variables for the status SQL
114 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
115
116 -- Passing AGGREGATION_LEVEL_FLAG to PMV
117 l_custom_rec.attribute_name := ':AGGREGATION_FLAG';
118 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
119 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
120 l_custom_rec.attribute_value := l_aggregation_flag;
121 x_custom_output.extend;
122 x_custom_output(x_custom_output.count) := l_custom_rec;
123
124 -- Passing AGGREGATION_LEVEL_FLAG to PMV
125 l_custom_rec.attribute_name := '&YTD_NESTED_PATTERN';
126 l_custom_rec.attribute_value := 1143;
127 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
128 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
129 x_custom_output.extend;
130 x_custom_output(x_custom_output.count) := l_custom_rec;
131
132 -- Passing last refresh date to PMV
133 l_custom_rec.attribute_name := ':LAST_REFRESH_DATE';
134 l_custom_rec.attribute_value := to_char(l_last_refresh_date,'DD/MM/YYYY');
135 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
136 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
137 x_custom_output.extend;
138 x_custom_output(x_custom_output.count) := l_custom_rec;
139
140 x_custom_sql := l_query;
141
142 EXCEPTION
143
144 WHEN OTHERS THEN
145 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
146 l_err_stage:=' The exception is : ' || SQLERRM;
147 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_BACKLOG_TBL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
148 END IF;
149
150 END get_backlog_tbl_sql;
151
152
153 FUNCTION GET_BACKLOG_TBL_SEL_CLAUSE( p_view_by_dim IN VARCHAR2
154 ,p_view_by_col IN VARCHAR2)
155 RETURN VARCHAR2
156 IS
157
158 l_sel_clause VARCHAR2(8000);
159 l_view_by_col_name VARCHAR2(120);
160 l_description VARCHAR2(30);
161 l_drill_across_rep_1 VARCHAR2(500);
162 l_drill_across_rep_2 VARCHAR2(500);
163 l_drill_across_rep_3 VARCHAR2(500);
164
165 BEGIN
166
167 l_description := 'null';
168 l_drill_across_rep_1 := 'null' ;
169 l_drill_across_rep_2 := 'null' ;
170 l_drill_across_rep_3 := 'null' ;
171
172 -- Item Description for item view by
173 l_drill_across_rep_1 := get_drill_across (p_view_by_dim => p_view_by_dim, p_function_name =>'ISC_DEPOT_BACKLOG_TBL_REP');
174 l_drill_across_rep_2 := '''pFunctionName=ISC_DEPOT_BACKLOG_DTL_TBL_REP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM&pParamIds=Y''';
175 l_drill_across_rep_3 := '''pFunctionName=ISC_DEPOT_PAST_DUE_DTL_TBL_REP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM&pParamIds=Y''';
176 IF (p_view_by_dim = 'ITEM+ENI_ITEM') THEN
177 l_description := ' v. description ';
178 END IF;
179 l_sel_clause :=
180 'SELECT '|| ISC_DEPOT_RPT_UTIL_PKG.get_viewby_select_clause (p_view_by_dim) || fnd_global.newline ||
181 l_description || ' BIV_ATTRIBUTE1
182 ,BIV_MEASURE1
183 ,BIV_MEASURE11
184 ,BIV_MEASURE2
185 ,BIV_MEASURE12
186 ,BIV_MEASURE3
187 ,BIV_MEASURE4
188 ,BIV_MEASURE13
189 ,BIV_MEASURE5
190 ,BIV_MEASURE6
191 ,BIV_MEASURE21
192 ,BIV_MEASURE22
193 ,BIV_MEASURE23
194 ,BIV_MEASURE24
195 ,BIV_MEASURE25
196 ,BIV_MEASURE26
197 ,BIV_MEASURE27
198 ,BIV_MEASURE28
199 ,BIV_MEASURE29
200 ,BIV_MEASURE30
201 ,BIV_MEASURE31
202 ,BIV_MEASURE32
203 ,BIV_MEASURE33
204 ,BIV_MEASURE34
205 , ' || l_drill_across_rep_1 || ' BIV_DYNAMIC_URL1 ' || fnd_global.newline ||
206 ',(case when :LAST_REFRESH_DATE <= &BIS_CURRENT_ASOF_DATE THEN ' || l_drill_across_rep_2 || ' ELSE NULL END ) BIV_DYNAMIC_URL2 ' || fnd_global.newline ||
207 ',(case when :LAST_REFRESH_DATE = &BIS_CURRENT_ASOF_DATE THEN ' || l_drill_across_rep_3 || ' ELSE NULL END ) BIV_DYNAMIC_URL3 ' || fnd_global.newline ||
208 'FROM ( SELECT
209 rank() over (&ORDER_BY_CLAUSE'||' nulls last, '||p_view_by_col||' ) - 1 rnk
210 ,'||p_view_by_col||'
211 ,BIV_MEASURE1
212 ,BIV_MEASURE11
213 ,BIV_MEASURE2
214 ,BIV_MEASURE12
215 ,BIV_MEASURE3
216 ,BIV_MEASURE4
217 ,BIV_MEASURE13
218 ,BIV_MEASURE5
219 ,BIV_MEASURE6
220 ,BIV_MEASURE21
221 ,BIV_MEASURE22
222 ,BIV_MEASURE23
223 ,BIV_MEASURE24
224 ,BIV_MEASURE25
225 ,BIV_MEASURE26
226 ,BIV_MEASURE27
227 ,BIV_MEASURE28
228 ,BIV_MEASURE29
229 ,BIV_MEASURE30
230 ,BIV_MEASURE31
231 ,BIV_MEASURE32
232 ,BIV_MEASURE33
233 ,BIV_MEASURE34 ' || fnd_global.newline ||
234 ' FROM ( SELECT ' || fnd_global.newline ||
235 p_view_by_col || fnd_global.newline ||
236 ',' || 'NVL(c_backlog,0) BIV_MEASURE1 ' || fnd_global.newline ||
237 ',' || 'NVL(p_backlog,0) BIV_MEASURE11 ' || fnd_global.newline ||
238 ',' || poa_dbi_util_pkg.change_clause( cur_col => 'c_backlog'
239 ,prior_col => 'p_backlog'
240 ,change_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
241 || 'BIV_MEASURE2' || fnd_global.newline ||
242 ',' || 'NVL(c_past_due,0) BIV_MEASURE3 ' || fnd_global.newline ||
243 ',' || 'NVL(p_past_due,0) BIV_MEASURE12 ' || fnd_global.newline ||
244 ',' || poa_dbi_util_pkg.change_clause( cur_col => 'c_past_due'
245 ,prior_col => 'p_past_due'
246 ,change_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
247 || 'BIV_MEASURE4' || fnd_global.newline ||
248 ',' || poa_dbi_util_pkg.rate_clause( numerator => 'c_past_due'
249 ,denominator => 'c_backlog'
250 ,rate_type => 'P') -- 'P' for Percent ; 'NP' for non percent
251 || 'BIV_MEASURE5' || fnd_global.newline ||
252 ',' || poa_dbi_util_pkg.rate_clause( numerator => 'p_past_due'
253 ,denominator => 'p_backlog'
254 ,rate_type => 'P') -- 'P' for Percent ; 'NP' for non percent
255 || 'BIV_MEASURE13' || fnd_global.newline ||
256 ',' || OPI_DBI_RPT_UTIL_PKG.change_pct_str(p_new_numerator => 'c_past_due',
257 p_new_denominator => 'c_backlog',
258 p_old_numerator => 'p_past_due',
259 p_old_denominator => 'p_backlog',
260 p_measure_name => 'BIV_MEASURE6')
261 || fnd_global.newline || fnd_global.newline ||
262 ',' || 'NVL(c_backlog_total,0) BIV_MEASURE21 ' || fnd_global.newline ||
263 ',' || poa_dbi_util_pkg.change_clause( cur_col => 'c_backlog_total'
264 ,prior_col => 'p_backlog_total'
265 ,change_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
266 || 'BIV_MEASURE22' || fnd_global.newline ||
267 ',' || 'NVL(c_past_due_total,0) BIV_MEASURE23 ' || fnd_global.newline ||
268 ',' || poa_dbi_util_pkg.change_clause( cur_col => 'c_past_due_total'
269 ,prior_col => 'p_past_due_total'
270 ,change_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
271 || 'BIV_MEASURE24' || fnd_global.newline ||
272 ',' || poa_dbi_util_pkg.rate_clause( numerator => 'c_past_due_total'
273 ,denominator => 'c_backlog_total'
274 ,rate_type => 'P') -- 'P' for Percent ; 'NP' for non percent
275 || 'BIV_MEASURE25' || fnd_global.newline ||
276 ',' || OPI_DBI_RPT_UTIL_PKG.change_pct_str(p_new_numerator => 'c_past_due_total',
277 p_new_denominator => 'c_backlog_total',
278 p_old_numerator => 'p_past_due_total',
279 p_old_denominator => 'p_backlog_total',
280 p_measure_name => 'BIV_MEASURE26')
281 || fnd_global.newline || fnd_global.newline ||
282 ',' || 'NVL(c_backlog,0) BIV_MEASURE27 ' || fnd_global.newline ||
283 ',' || 'NVL(p_backlog,0) BIV_MEASURE28 ' || fnd_global.newline ||
284 ',' || 'NVL(c_backlog_total,0) BIV_MEASURE29 ' || fnd_global.newline ||
285 ',' || 'NVL(p_backlog_total,0) BIV_MEASURE30 ' || fnd_global.newline ||
286 ',' || poa_dbi_util_pkg.rate_clause( numerator => 'c_past_due'
287 ,denominator => 'c_backlog'
288 ,rate_type => 'P') -- 'P' for Percent ; 'NP' for non percent
289 || 'BIV_MEASURE31' || fnd_global.newline ||
290 ',' || poa_dbi_util_pkg.rate_clause( numerator => 'p_past_due'
291 ,denominator => 'p_backlog'
292 ,rate_type => 'P') -- 'P' for Percent ; 'NP' for non percent
293 || 'BIV_MEASURE32' || fnd_global.newline ||
294 ',' || poa_dbi_util_pkg.rate_clause( numerator => 'c_past_due_total'
295 ,denominator => 'c_backlog_total'
296 ,rate_type => 'P') -- 'P' for Percent ; 'NP' for non percent
297 || 'BIV_MEASURE33' || fnd_global.newline ||
298 ',' || poa_dbi_util_pkg.rate_clause( numerator => 'p_past_due_total'
299 ,denominator => 'p_backlog_total'
300 ,rate_type => 'P') -- 'P' for Percent ; 'NP' for non percent
301 || 'BIV_MEASURE34' || fnd_global.newline ;
302
303 RETURN l_sel_clause;
304
305 END GET_BACKLOG_TBL_SEL_CLAUSE;
306
307 PROCEDURE GET_BACKLOG_TRD_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
308 x_custom_sql OUT NOCOPY VARCHAR2,
309 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
310 IS
311
312 l_query ISC_DEPOT_RPT_UTIL_PKG.g_query_typ%type;
313 l_view_by ISC_DEPOT_RPT_UTIL_PKG.g_view_by_typ%type;
314 l_view_by_col ISC_DEPOT_RPT_UTIL_PKG.g_view_by_col_typ%type;
318 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
315 l_xtd ISC_DEPOT_RPT_UTIL_PKG.g_xtd_typ%type;
316 l_comparison_type VARCHAR2(1);
317 l_cur_suffix VARCHAR2(2);
319 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
320 l_where_clause ISC_DEPOT_RPT_UTIL_PKG.g_where_clause_typ%type;
321 l_mv ISC_DEPOT_RPT_UTIL_PKG.g_mv_typ%type;
322 l_mv_set VARCHAR2(50);
323 l_aggregation_flag NUMBER;
324 l_custom_rec BIS_QUERY_ATTRIBUTES;
325 l_mv_type VARCHAR2(10);
326 l_err_stage VARCHAR2(32767);
327 l_debug_mode VARCHAR2(1);
328 l_module_name ISC_DEPOT_RPT_UTIL_PKG.g_module_name_typ%type ;
329
330 BEGIN
331 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
332 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
333 l_comparison_type := 'Y';
334
335 -- clear out the tables.
336 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
337 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
338
339 -- get all the query parameters
340 ISC_DEPOT_RPT_UTIL_PKG.process_parameters (p_param => p_param,
341 x_view_by => l_view_by,
342 x_view_by_col_name => l_view_by_col,
343 x_comparison_type => l_comparison_type,
344 x_xtd => l_xtd,
345 x_cur_suffix => l_cur_suffix,
346 x_where_clause => l_where_clause,
347 x_mv => l_mv,
348 x_join_tbl => l_join_tbl,
349 x_mv_type => l_mv_type,
350 x_aggregation_flag => l_aggregation_flag,
351 p_trend => 'Y',
352 p_mv_set => 'BKLG',
353 x_custom_output => x_custom_output);
354
355
356 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
357 l_err_stage:='After calling DRM_DBI_RPT_UTIL_PKG.process_parameters';
358 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_BACKLOG_TRD : ' ,l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
359 END IF;
360
361 -- Add measure columns that need to be aggregated
362 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
363 p_col_name => '(OPEN_COUNT - CLOSE_COUNT)' ,
364 p_alias_name => 'backlog',
365 p_grand_total => 'N',
366 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
367 p_to_date_type => 'YTD');
368
369 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
370 p_col_name => '(PAST_DUE_OPEN_COUNT - LATE_COMPLETE_COUNT)' ,
371 p_alias_name => 'past_due',
372 p_grand_total => 'N',
373 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
374 p_to_date_type => 'YTD');
375
376 l_query := GET_BACKLOG_TRD_SEL_CLAUSE (l_view_by)
377 || ' from
378 ' || poa_dbi_template_pkg.trend_sql(p_xtd => l_xtd,
379 p_comparison_type => l_comparison_type,
380 p_fact_name => l_mv,
381 p_where_clause => l_where_clause,
382 p_col_name => l_col_tbl,
383 p_use_grpid => 'N',
384 p_in_join_tables => NULL);
385
386 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
387 l_err_stage:='The query is : ' || l_query;
388 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_BACKLOG_TBL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
389 END IF;
390
391 -- prepare output for bind variables
392 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
393 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
394
395 -- set the basic bind variables for the status SQL
396 poa_dbi_util_pkg.get_custom_trend_binds (p_xtd => l_xtd,
397 p_comparison_type => l_comparison_type,
398 x_custom_output => x_custom_output);
399
400 -- Passing AGGREGATION_LEVEL_FLAG to PMV
401 l_custom_rec.attribute_name := ':AGGREGATION_FLAG';
402 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
403 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
404 l_custom_rec.attribute_value := l_aggregation_flag;
405 x_custom_output.extend;
406 x_custom_output(x_custom_output.count) := l_custom_rec;
407
408 -- Passing AGGREGATION_LEVEL_FLAG to PMV
409 l_custom_rec.attribute_name := '&YTD_NESTED_PATTERN';
410 l_custom_rec.attribute_value := 1143;
411 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
412 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
413 x_custom_output.extend;
414 x_custom_output(x_custom_output.count) := l_custom_rec;
415
419 WHEN OTHERS THEN
416 x_custom_sql := l_query;
417 EXCEPTION
418
420 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
421 l_err_stage:= 'The exception is : ' || SQLERRM;
422 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_BACKLOG_TRD : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
423 END IF;
424
425 END GET_BACKLOG_TRD_SQL;
426
427 FUNCTION GET_BACKLOG_TRD_SEL_CLAUSE(p_view_by_dim IN VARCHAR2)
428 RETURN VARCHAR2
429 IS
430
431 l_sel_clause VARCHAR2(8000);
432 l_view_by_col_name VARCHAR2(120);
433 l_description VARCHAR2(30);
434 l_drill_across_rep_1 VARCHAR2(50);
435
436 BEGIN
437 l_description := 'null';
438 l_drill_across_rep_1 := 'null' ;
439
440 l_sel_clause :=
441 'SELECT cal.name VIEWBY ' || fnd_global.newline ||
442 ',' || 'NVL(iset.c_backlog,0) BIV_MEASURE1 ' || fnd_global.newline ||
443 ',' || 'NVL(iset.p_backlog,0) BIV_MEASURE11 ' || fnd_global.newline ||
444 ',' || poa_dbi_util_pkg.change_clause( cur_col => 'c_backlog'
445 ,prior_col => 'p_backlog'
446 ,change_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
447 || 'BIV_MEASURE2' || fnd_global.newline ||
448 ',' || 'NVL(iset.c_past_due,0) BIV_MEASURE3 ' || fnd_global.newline ||
449 ',' || 'NVL(iset.p_past_due,0) BIV_MEASURE12 ' || fnd_global.newline ||
450 ',' || poa_dbi_util_pkg.change_clause( cur_col => 'c_past_due'
451 ,prior_col => 'p_past_due'
452 ,change_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
453 || 'BIV_MEASURE4' || fnd_global.newline ||
454 ',' || poa_dbi_util_pkg.rate_clause( numerator => 'c_past_due'
455 ,denominator => 'c_backlog'
456 ,rate_type => 'P') -- 'P' for Percent ; 'NP' for non percent
457 || 'BIV_MEASURE5' || fnd_global.newline ||
458 ',' || poa_dbi_util_pkg.rate_clause( numerator => 'p_past_due'
459 ,denominator => 'p_backlog'
460 ,rate_type => 'P') -- 'P' for Percent ; 'NP' for non percent
461 || 'BIV_MEASURE13' || fnd_global.newline ||
462 ',' || OPI_DBI_RPT_UTIL_PKG.change_pct_str(p_new_numerator => 'c_past_due',
463 p_new_denominator => 'c_backlog',
464 p_old_numerator => 'p_past_due',
465 p_old_denominator => 'p_backlog',
466 p_measure_name => 'BIV_MEASURE6')
467 || fnd_global.newline;
468
469
470 RETURN l_sel_clause;
471
472 END GET_BACKLOG_TRD_SEL_CLAUSE;
473
474 PROCEDURE GET_BACKLOG_DTL_TBL_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
475 x_custom_sql OUT NOCOPY VARCHAR2,
476 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
477 IS
478
479 l_query ISC_DEPOT_RPT_UTIL_PKG.g_query_typ%type;
480 l_view_by ISC_DEPOT_RPT_UTIL_PKG.g_view_by_typ%type;
481 l_view_by_col ISC_DEPOT_RPT_UTIL_PKG.g_view_by_col_typ%type;
482 l_xtd ISC_DEPOT_RPT_UTIL_PKG.g_xtd_typ%type;
483 l_comparison_type VARCHAR2(1);
484 l_cur_suffix VARCHAR2(1);
485 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
486 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
487 l_where_clause ISC_DEPOT_RPT_UTIL_PKG.g_where_clause_typ%type;
488 l_mv ISC_DEPOT_RPT_UTIL_PKG.g_mv_typ%type;
489 l_mv_set VARCHAR2(50);
490 l_aggregation_flag NUMBER;
491 l_custom_rec BIS_QUERY_ATTRIBUTES;
492 l_mv_type VARCHAR2(10);
493 l_err_stage VARCHAR2(32767);
494 l_debug_mode VARCHAR2(1);
495 l_module_name ISC_DEPOT_RPT_UTIL_PKG.g_module_name_typ%type ;
496
497 BEGIN
498 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
499 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
500
501 -- clear out the tables.
502 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
503 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
504
505 -- get all the query parameters
506 ISC_DEPOT_RPT_UTIL_PKG.process_parameters (p_param => p_param,
507 x_view_by => l_view_by,
508 x_view_by_col_name => l_view_by_col,
509 x_comparison_type => l_comparison_type,
510 x_xtd => l_xtd,
511 x_cur_suffix => l_cur_suffix,
512 x_where_clause => l_where_clause,
513 x_mv => l_mv,
514 x_join_tbl => l_join_tbl,
515 x_mv_type => l_mv_type,
516 x_aggregation_flag => l_aggregation_flag,
520
517 p_trend => 'Y',
518 p_mv_set => 'BKLDTL1',
519 x_custom_output => x_custom_output);
521 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
522 l_err_stage:='After calling DRM_DBI_RPT_UTIL_PKG.process_parameters';
523 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_BACKLOG_DTL : ' ,l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
524 END IF;
525
526 l_query :=
527 'SELECT
528 BIV_ATTRIBUTE1
529 ,BIV_ATTRIBUTE2
530 ,BIV_ATTRIBUTE3
531 ,BIV_ATTRIBUTE4
532 ,BIV_ATTRIBUTE5
533 ,BIV_ATTRIBUTE6
534 ,BIV_MEASURE1
535 ,BIV_ATTRIBUTE7
536 ,BIV_ATTRIBUTE8
537 ,BIV_ATTRIBUTE9
538 ,BIV_DATE1
539 ,BIV_MEASURE2
540 ,BIV_MEASURE3
541 ,''pFunctionName=CSD_RO_DETAILS&csdInvOrgId=''||BIV_MEASURE3||''&csdRepairLineId=''||BIV_MEASURE2 BIV_DYNAMIC_URL1
542 ,' || ISC_DEPOT_RPT_UTIL_PKG.get_service_request_url || ' || BIV_ATTRIBUTE9 BIV_DYNAMIC_URL2
543 FROM (
544 SELECT
545 rank() over (&ORDER_BY_CLAUSE nulls last,BIV_ATTRIBUTE1) - 1 rnk
546 ,BIV_ATTRIBUTE1
547 ,BIV_ATTRIBUTE2
548 ,BIV_ATTRIBUTE3
549 ,BIV_ATTRIBUTE4
550 ,BIV_ATTRIBUTE5
551 ,BIV_ATTRIBUTE6
552 ,BIV_MEASURE1
553 ,BIV_ATTRIBUTE7
554 ,BIV_ATTRIBUTE8
555 ,BIV_ATTRIBUTE9
556 ,BIV_DATE1
557 ,BIV_MEASURE2
558 ,BIV_MEASURE3
559 FROM (
560 SELECT repair_number BIV_ATTRIBUTE1 ' || fnd_global.newline ||
561 ',' || ' incident_number BIV_ATTRIBUTE2 ' || fnd_global.newline ||
562 ',' || ' crt.name BIV_ATTRIBUTE3 ' || fnd_global.newline ||
563 ',' || ' eiov.value BIV_ATTRIBUTE4 ' || fnd_global.newline ||
564 ',' || ' eiov.description BIV_ATTRIBUTE5 ' || fnd_global.newline ||
565 ',' || ' mum.unit_of_measure BIV_ATTRIBUTE6 ' || fnd_global.newline ||
566 ',' || ' quantity BIV_MEASURE1 ' || fnd_global.newline ||
567 ',' || ' fact.repair_line_id BIV_MEASURE2 ' || fnd_global.newline ||
568 ',' || ' fact.master_organization_id BIV_MEASURE3 ' || fnd_global.newline ||
569 ',' || ' serial_number BIV_ATTRIBUTE7 ' || fnd_global.newline ||
570 ',' || ' fl.meaning BIV_ATTRIBUTE8 ' || fnd_global.newline ||
571 ',' || ' incident_id BIV_ATTRIBUTE9 '|| fnd_global.newline ||
572 ',' || ' promise_date BIV_DATE1 ' || fnd_global.newline
573 || ' from ' || fnd_global.newline
574 || l_mv
575 || ' ISC_DR_CURR_01_MV fact, ' || fnd_global.newline
576 || ' ENI_ITEM_V EIOV, ' || fnd_global.newline
577 || ' CSD_FLOW_STATUSES_B CFSB, ' || fnd_global.newline
578 || ' FND_LOOKUPS FL, ' || fnd_global.newline
579 || ' MTL_UNITS_OF_MEASURE_VL MUM ' || fnd_global.newline
580 -- Mapped fact.flow_status_id to CFSB table which will be mapped to FND_LOOKUPS
581 || ' WHERE FL.LOOKUP_TYPE = ''CSD_REPAIR_FLOW_STATUS'' ' || fnd_global.newline
582 || ' AND FL.LOOKUP_CODE = CFSB.flow_status_code ' || fnd_global.newline
583 || ' AND CFSB.flow_status_id = fact.flow_status_id ' || fnd_global.newline
584 || ' AND FACT.item_org_id = eiov.id ' || fnd_global.newline
585 || ' AND mum.uom_code = fact.uom_code '|| fnd_global.newline
586 || l_where_clause
587 || ' ) ) where (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
588 ORDER BY rnk' || fnd_global.newline ;
589
590 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
591 l_err_stage:='The query is : ' || l_query;
592 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_BACKLOG_DTL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
593 END IF;
594
595 x_custom_sql := l_query;
596 EXCEPTION
597
598 WHEN OTHERS THEN
599 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
600 l_err_stage:='The exception is : ' || SQLERRM;
601 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_BACKLOG_DTL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
602 END IF;
603
604 END get_backlog_dtl_tbl_sql;
605
606 PROCEDURE GET_PAST_DUE_AGNG_TBL_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
607 x_custom_sql OUT NOCOPY VARCHAR2,
608 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
609 IS
610
611 l_query ISC_DEPOT_RPT_UTIL_PKG.g_query_typ%type;
612 l_view_by ISC_DEPOT_RPT_UTIL_PKG.g_view_by_typ%type;
613 l_view_by_col ISC_DEPOT_RPT_UTIL_PKG.g_view_by_col_typ%type;
614 l_xtd ISC_DEPOT_RPT_UTIL_PKG.g_xtd_typ%type;
615 l_comparison_type VARCHAR2(1);
616 l_cur_suffix VARCHAR2(1);
617 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
618 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
619 l_where_clause ISC_DEPOT_RPT_UTIL_PKG.g_where_clause_typ%type;
620 l_mv ISC_DEPOT_RPT_UTIL_PKG.g_mv_typ%type;
621 l_mv_set VARCHAR2(50);
622 l_aggregation_flag NUMBER;
623 l_custom_rec BIS_QUERY_ATTRIBUTES;
624 l_mv_type VARCHAR2(10);
625 l_err_stage VARCHAR2(32767);
626 l_debug_mode VARCHAR2(1);
627 l_module_name ISC_DEPOT_RPT_UTIL_PKG.g_module_name_typ%type ;
628
629 BEGIN
630 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
631 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
632
633 -- clear out the tables.
634 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
635 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
636
637 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
638 l_err_stage:='get past due aging report calling process parameters';
639 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_BACKLOG_DTL : ' ,l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
640 END IF;
641
642 -- get all the query parameters
643 ISC_DEPOT_RPT_UTIL_PKG.process_parameters (p_param => p_param,
644 x_view_by => l_view_by,
645 x_view_by_col_name => l_view_by_col,
646 x_comparison_type => l_comparison_type,
647 x_xtd => l_xtd,
648 x_cur_suffix => l_cur_suffix,
649 x_where_clause => l_where_clause,
650 x_mv => l_mv,
651 x_join_tbl => l_join_tbl,
652 x_mv_type => l_mv_type,
653 x_aggregation_flag => l_aggregation_flag,
654 p_trend => 'N',
655 p_mv_set => 'BKLAGN1',
656 x_custom_output => x_custom_output);
657
658
659 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
660 l_err_stage:='After calling DRM_DBI_RPT_UTIL_PKG.process_parameters';
661 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_BACKLOG_DTL : ' ,l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
662 END IF;
663
664 l_query :=
665 ' SELECT range_name BIV_ATTRIBUTE1 ' || fnd_global.newline ||
666 ' ,nvl(past_due_count,0) BIV_MEASURE1 ' || fnd_global.newline ||
667 ' ,' || poa_dbi_util_pkg.rate_clause(numerator => 'past_due_count'
668 ,denominator => 'past_due_count_total'
669 ,rate_type => 'P') || ' BIV_MEASURE2' || fnd_global.newline ||
670 ' ,nvl(past_due_count_total,0) BIV_MEASURE21 ' || fnd_global.newline ||
671 ' ,' || poa_dbi_util_pkg.rate_clause(numerator => 'past_due_count_total'
672 ,denominator => 'past_due_count_total'
673 ,rate_type => 'P') || ' BIV_MEASURE22' || fnd_global.newline ||
674 ' ,' || '''pFunctionName=ISC_DEPOT_PAST_DUE_DTL_TBL_REP&pParamIds=Y&BIV_ATTRIBUTE1=-1&BIV_DR_BACKLOG_BUCKET+BIV_DR_BACKLOG_BUCKET=''|| bucket_number ||''''' || ' BIV_ATTRIBUTE10' ||
675
676 ' FROM (' || fnd_global.newline ||
677 ' SELECT range_name ' || fnd_global.newline ||
678 ' ,bucket_number ' || fnd_global.newline ||
679 ' ,sum (decode (buckets.bucket_number, 1, past_due_age_b1 ' || fnd_global.newline ||
680 ' ,2, past_due_age_b2 ' || fnd_global.newline ||
681 ' ,3, past_due_age_b3 ' || fnd_global.newline ||
682 ' ,4, past_due_age_b4 ' || fnd_global.newline ||
683 ' ,5, past_due_age_b5 ' || fnd_global.newline ||
684 ' ,6, past_due_age_b6 ' || fnd_global.newline ||
685 ' ,7, past_due_age_b7 ' || fnd_global.newline ||
686 ' ,8, past_due_age_b8 ' || fnd_global.newline ||
687 ' ,9, past_due_age_b9 ' || fnd_global.newline ||
688 ' ,10, past_due_age_b10 ) ) past_due_count ' || fnd_global.newline ||
689 ' ,sum(sum(decode (buckets.bucket_number, 1, past_due_age_b1 ' || fnd_global.newline ||
690 ' ,2, past_due_age_b2 ' || fnd_global.newline ||
691 ' ,3, past_due_age_b3 ' || fnd_global.newline ||
692 ' ,4, past_due_age_b4 ' || fnd_global.newline ||
693 ' ,5, past_due_age_b5 ' || fnd_global.newline ||
694 ' ,6, past_due_age_b6 ' || fnd_global.newline ||
695 ' ,7, past_due_age_b7 ' || fnd_global.newline ||
696 ' ,8, past_due_age_b8 ' || fnd_global.newline ||
697 ' ,9, past_due_age_b9 ' || fnd_global.newline ||
698 ' ,10, past_due_age_b10 ) ) ) over () past_due_count_total ' || fnd_global.newline ||
699 ' from ' || fnd_global.newline ||
700 l_mv || fnd_global.newline ||
701 ' ISC_DR_CURR_02_MV fact, ' || fnd_global.newline ||
702 ' (';
703
704 FOR i in 1..10 LOOP
705 l_query := l_query ||
709 ' bbc.range' || i || '_high range_high ' || fnd_global.newline ||
706 'SELECT '|| i || ' bucket_number, ' || fnd_global.newline ||
707 ' bbct.range'|| i ||'_name range_name, ' || fnd_global.newline ||
708 ' bbc.range' || i || '_low range_low, ' || fnd_global.newline ||
710 'FROM bis_bucket_customizations bbc, ' || fnd_global.newline ||
711 ' bis_bucket bb, ' || fnd_global.newline ||
712 ' bis_bucket_customizations_tl bbct ' || fnd_global.newline ||
713 'WHERE short_name = ''ISC_DEPOT_BKLG_CMP_AGING'' ' || fnd_global.newline ||
714 ' and bb.bucket_id = bbc.bucket_id ' || fnd_global.newline ||
715 ' and nvl(bbc.range' || i || '_low,bbc.range' || i || '_high) is not null' || fnd_global.newline ||
716 ' and bbct.language =USERENV(''LANG'') ' || fnd_global.newline ||
717 ' and bbC.id = bbct.id '|| fnd_global.newline;
718 IF i <> 10 THEN
719 l_query := l_query || 'UNION ALL ' || fnd_global.newline;
720 ELSE
721 l_query := l_query || ') buckets ' || fnd_global.newline;
722 END IF;
723 END LOOP;
724
725 IF (l_where_clause is NOT NULL ) THEN
726 l_query := l_query || ' where 1=1 ' || l_where_clause;
727 END IF;
728
729 l_query := l_query || ' group by range_name,bucket_number order by bucket_number ) ';
730
731 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
732 l_err_stage:='The query is : ' || l_query;
733 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_BACKLOG_DTL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
734 END IF;
735 -- prepare output for bind variables
736 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
737 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
738
739 -- set the basic bind variables for the status SQL
740 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
741
742 -- Passing AGGREGATION_LEVEL_FLAG to PMV
743 l_custom_rec.attribute_name := ':AGGREGATION_FLAG';
744 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
745 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
746 l_custom_rec.attribute_value := l_aggregation_flag;
747 x_custom_output.extend;
748 x_custom_output(x_custom_output.count) := l_custom_rec;
749
750
751 x_custom_sql := l_query;
752 EXCEPTION
753
754 WHEN OTHERS THEN
755 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
756 l_err_stage:='The exception is : ' || SQLERRM;
757 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_BACKLOG_DTL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
758 END IF;
759
760 END GET_PAST_DUE_AGNG_TBL_SQL;
761
762 PROCEDURE GET_PAST_DUE_DTL_TBL_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
763 x_custom_sql OUT NOCOPY VARCHAR2,
764 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
765 IS
766
767 l_query ISC_DEPOT_RPT_UTIL_PKG.g_query_typ%type;
768 l_view_by ISC_DEPOT_RPT_UTIL_PKG.g_view_by_typ%type;
769 l_view_by_col ISC_DEPOT_RPT_UTIL_PKG.g_view_by_col_typ%type;
770 l_xtd ISC_DEPOT_RPT_UTIL_PKG.g_xtd_typ%type;
771 l_comparison_type VARCHAR2(1);
772 l_cur_suffix VARCHAR2(1);
773 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
774 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
775 l_where_clause ISC_DEPOT_RPT_UTIL_PKG.g_where_clause_typ%type;
776 l_mv ISC_DEPOT_RPT_UTIL_PKG.g_mv_typ%type;
777 l_mv_set VARCHAR2(50);
778 l_aggregation_flag NUMBER;
779 l_custom_rec BIS_QUERY_ATTRIBUTES;
780 l_mv_type VARCHAR2(10);
781 l_err_stage VARCHAR2(32767);
782 l_debug_mode VARCHAR2(1);
783 l_module_name ISC_DEPOT_RPT_UTIL_PKG.g_module_name_typ%type ;
784
785 BEGIN
786 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
787 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
788
789 -- clear out the tables.
790 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
791 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
792
793 -- get all the query parameters
794 ISC_DEPOT_RPT_UTIL_PKG.process_parameters (p_param => p_param,
795 x_view_by => l_view_by,
796 x_view_by_col_name => l_view_by_col,
797 x_comparison_type => l_comparison_type,
798 x_xtd => l_xtd,
799 x_cur_suffix => l_cur_suffix,
800 x_where_clause => l_where_clause,
801 x_mv => l_mv,
802 x_join_tbl => l_join_tbl,
803 x_mv_type => l_mv_type,
804 x_aggregation_flag => l_aggregation_flag,
805 p_trend => 'Y',
806 p_mv_set => 'BKLDTL2',
807 x_custom_output => x_custom_output);
808
809
810 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
811 l_err_stage:='After calling DRM_DBI_RPT_UTIL_PKG.process_parameters';
815 l_query :=
812 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_BACKLOG_DTL : ' ,l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
813 END IF;
814
816 'SELECT
817 BIV_ATTRIBUTE1
818 ,BIV_ATTRIBUTE2
819 ,BIV_ATTRIBUTE3
820 ,BIV_ATTRIBUTE4
821 ,BIV_ATTRIBUTE5
822 ,BIV_ATTRIBUTE6
823 ,BIV_MEASURE1
824 ,BIV_ATTRIBUTE7
825 ,BIV_ATTRIBUTE8
826 ,BIV_ATTRIBUTE9
827 ,BIV_DATE1
828 ,BIV_MEASURE2
829 ,BIV_MEASURE3
830 ,BIV_MEASURE4
831 ,''pFunctionName=CSD_RO_DETAILS&csdInvOrgId=''||BIV_MEASURE4||''&csdRepairLineId=''||BIV_MEASURE3 BIV_DYNAMIC_URL1
832 ,' || ISC_DEPOT_RPT_UTIL_PKG.get_service_request_url || ' || BIV_ATTRIBUTE9 BIV_DYNAMIC_URL2
833 FROM (
834 SELECT
835 rank() over (&ORDER_BY_CLAUSE nulls last,BIV_ATTRIBUTE1, BIV_MEASURE3) - 1 rnk
836 ,BIV_ATTRIBUTE1
837 ,BIV_ATTRIBUTE2
838 ,BIV_ATTRIBUTE3
839 ,BIV_ATTRIBUTE4
840 ,BIV_ATTRIBUTE5
841 ,BIV_ATTRIBUTE6
842 ,BIV_MEASURE1
843 ,BIV_ATTRIBUTE7
844 ,BIV_ATTRIBUTE8
845 ,BIV_ATTRIBUTE9
846 ,BIV_DATE1
847 ,BIV_MEASURE2
848 ,BIV_MEASURE3
849 ,BIV_MEASURE4
850 FROM (
851 SELECT repair_number BIV_ATTRIBUTE1 ' || fnd_global.newline ||
852 ',' || ' incident_number BIV_ATTRIBUTE2 ' || fnd_global.newline ||
853 ',' || ' crt.name BIV_ATTRIBUTE3 ' || fnd_global.newline ||
854 ',' || ' eiov.value BIV_ATTRIBUTE4 ' || fnd_global.newline ||
855 ',' || ' eiov.description BIV_ATTRIBUTE5 ' || fnd_global.newline ||
856 ',' || ' fact.repair_line_id BIV_MEASURE3 ' || fnd_global.newline ||
857 ',' || ' fact.master_organization_id BIV_MEASURE4 ' || fnd_global.newline ||
858 ',' || ' mum.unit_of_measure BIV_ATTRIBUTE6 ' || fnd_global.newline ||
859 ',' || ' quantity BIV_MEASURE1 ' || fnd_global.newline ||
860 ',' || ' serial_number BIV_ATTRIBUTE7 ' || fnd_global.newline ||
861 ',' || ' fl.meaning BIV_ATTRIBUTE8 ' || fnd_global.newline ||
862 ',' || ' incident_id BIV_ATTRIBUTE9 ' || fnd_global.newline ||
863 ',' || ' promise_date BIV_DATE1 ' || fnd_global.newline ||
864 ',' || ' PAST_DUE_DAYS BIV_MEASURE2 ' || fnd_global.newline
865 || ' from ' || fnd_global.newline
866 || l_mv || fnd_global.newline
867 || ' ISC_DR_CURR_01_MV fact, ' || fnd_global.newline
868 || ' ENI_ITEM_V EIOV, ' || fnd_global.newline
869 || ' CSD_FLOW_STATUSES_B CFSB, ' || fnd_global.newline
870 || ' FND_LOOKUPS FL, ' || fnd_global.newline
871 || ' MTL_UNITS_OF_MEASURE_VL MUM ' || fnd_global.newline
872 -- Mapped fact.flow_status_id to CFSB table which will be mapped to FND_LOOKUPS
873 || ' WHERE FL.LOOKUP_TYPE = ''CSD_REPAIR_FLOW_STATUS'' ' || fnd_global.newline
874 || ' AND CFSB.flow_status_id = fact.flow_status_id ' || fnd_global.newline
875 || ' AND FL.LOOKUP_CODE = CFSB.flow_status_code ' || fnd_global.newline
876 || ' AND FACT.item_org_id = eiov.id ' || fnd_global.newline
877 || ' AND FACT.past_due_flag = ''Y'' ' || fnd_global.newline
878 || ' AND mum.uom_code = fact.uom_code '
879 || l_where_clause
880 || ' ) ) where (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
881 ORDER BY rnk' || fnd_global.newline ;
882
883 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
884 l_err_stage:='The query is : ' || l_query;
885 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_BACKLOG_DTL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
886 END IF;
887
888 x_custom_sql := l_query;
889 EXCEPTION
890
891 WHEN OTHERS THEN
892 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
893 l_err_stage:='The exception is : ' || SQLERRM;
894 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_BACKLOG_DTL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
895 END IF;
896
897 END GET_PAST_DUE_DTL_TBL_SQL;
898
899 PROCEDURE GET_DAYS_UNTIL_PROM_TBL_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
900 x_custom_sql OUT NOCOPY VARCHAR2,
901 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
902 IS
903 l_query ISC_DEPOT_RPT_UTIL_PKG.g_query_typ%type;
904 l_view_by ISC_DEPOT_RPT_UTIL_PKG.g_view_by_typ%type;
905 l_view_by_col ISC_DEPOT_RPT_UTIL_PKG.g_view_by_col_typ%type;
906 l_xtd ISC_DEPOT_RPT_UTIL_PKG.g_xtd_typ%type;
907 l_comparison_type VARCHAR2(1);
908 l_cur_suffix VARCHAR2(1);
909 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
910 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
911 l_where_clause ISC_DEPOT_RPT_UTIL_PKG.g_where_clause_typ%type;
912 l_mv ISC_DEPOT_RPT_UTIL_PKG.g_mv_typ%type;
913 l_mv_set VARCHAR2(50);
914 l_custom_rec BIS_QUERY_ATTRIBUTES;
915 l_mv_type VARCHAR2(10);
916 l_err_stage VARCHAR2(32767);
917 l_bucket_rec bis_bucket_pub.bis_bucket_rec_type;
918 l_agg_flag_1 NUMBER; -- for isc_dr_curr_02_mv
919 l_agg_flag_2 NUMBER; -- for isc_dr_bklg_01_mv,isc_dr_bklg_02_mv
920 l_debug_mode VARCHAR2(1);
921 l_module_name ISC_DEPOT_RPT_UTIL_PKG.g_module_name_typ%type ;
922
923 BEGIN
924 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
925 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
926 l_agg_flag_1 := 0; -- for isc_dr_curr_02_mv
930 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
927 l_agg_flag_2 := 0; -- for isc_dr_bklg_01_mv,isc_dr_bklg_02_mv
928
929 -- clear out the tables.
931 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
932
933 -- get all the query parameters
934 ISC_DEPOT_RPT_UTIL_PKG.process_parameters ( p_param => p_param,
935 x_view_by => l_view_by,
936 x_view_by_col_name => l_view_by_col,
937 x_comparison_type => l_comparison_type,
938 x_xtd => l_xtd,
939 x_cur_suffix => l_cur_suffix,
940 x_where_clause => l_where_clause,
941 x_mv => l_mv,
942 x_join_tbl => l_join_tbl,
943 x_mv_type => l_mv_type,
944 x_aggregation_flag => l_agg_flag_1,
945 p_trend => 'N',
946 p_mv_set => 'BKLDUP1',
947 x_custom_output => x_custom_output);
948
949 ISC_DEPOT_RPT_UTIL_PKG.process_parameters ( p_param => p_param,
950 x_view_by => l_view_by,
951 x_view_by_col_name => l_view_by_col,
952 x_comparison_type => l_comparison_type,
953 x_xtd => l_xtd,
954 x_cur_suffix => l_cur_suffix,
955 x_where_clause => l_where_clause,
956 x_mv => l_mv,
957 x_join_tbl => l_join_tbl,
958 x_mv_type => l_mv_type,
959 x_aggregation_flag => l_agg_flag_2,
960 p_trend => 'N',
961 p_mv_set => 'BKLDUP2',
962 x_custom_output => x_custom_output);
963
964 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
965 l_err_stage:='After calling DRM_DBI_RPT_UTIL_PKG.process_parameters';
966 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_BACKLOG_TBL : ' ,l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
967 l_err_stage:='l_view_by = ' || l_view_by || 'l_view_by_col = ' || l_view_by_col;
968 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_BACKLOG_TBL : ' ,l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
969 l_err_stage:='l_mv_type = ' || l_mv_type || 'l_where_clause = ' || l_where_clause;
970 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_BACKLOG_TBL : ' ,l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
971 END IF;
972
973 l_mv := l_mv || '( SELECT ' || fnd_global.newline;
974 IF ( l_mv_type = 'ROOT' AND l_view_by = 'ITEM+ENI_ITEM_VBH_CAT' ) THEN
975 l_mv := l_mv ||
976 ' EDH.PARENT_ID PRODUCT_CATEGORY_ID' || fnd_global.newline;
977 ELSIF l_mv_type = 'ROOT' THEN
978 l_mv := l_mv ||
979 ' FACT.PRODUCT_CATEGORY_ID' || fnd_global.newline;
980 ELSE
981 l_mv := l_mv ||
982 ' FACT.ITEM_ORG_ID' || fnd_global.newline ||
983 ' ,FACT.PRODUCT_CATEGORY_ID' || fnd_global.newline;
984 END IF;
985 l_mv := l_mv ||
986 ' ,FACT.REPAIR_ORGANIZATION_ID' || fnd_global.newline ||
987 ' ,FACT.REPAIR_TYPE_ID' || fnd_global.newline ||
988 ' ,FACT.CUSTOMER_ID' || fnd_global.newline ||
989 ' ,backlog_count c_backlog' || fnd_global.newline ||
990 ' ,0 p_backlog' || fnd_global.newline ||
991 ' ,not_promised_count not_promised ' || fnd_global.newline ||
992 ' ,past_due_count past_due' || fnd_global.newline ||
993 ' ,days_until_promised_b1 days_until_promised_b1' || fnd_global.newline ||
994 ' ,days_until_promised_b2 days_until_promised_b2' || fnd_global.newline ||
995 ' ,days_until_promised_b3 days_until_promised_b3' || fnd_global.newline ||
996 ' ,days_until_promised_b4 days_until_promised_b4' || fnd_global.newline ||
997 ' ,days_until_promised_b5 days_until_promised_b5' || fnd_global.newline ||
998 ' ,days_until_promised_b6 days_until_promised_b6' || fnd_global.newline ||
999 ' ,days_until_promised_b7 days_until_promised_b7' || fnd_global.newline ||
1000 ' ,days_until_promised_b8 days_until_promised_b8' || fnd_global.newline ||
1001 ' ,days_until_promised_b9 days_until_promised_b9' || fnd_global.newline ||
1002 ' ,days_until_promised_b10 days_until_promised_b10' || fnd_global.newline ||
1003 ' FROM ISC_DR_CURR_02_MV fact' || fnd_global.newline;
1004 IF ( l_mv_type = 'ROOT' AND l_view_by = 'ITEM+ENI_ITEM_VBH_CAT' ) THEN
1005 l_mv := l_mv || ' ,ENI_DENORM_HIERARCHIES EDH,MTL_DEFAULT_CATEGORY_SETS MDCS ' || fnd_global.newline ||
1006 ' WHERE FACT.PRODUCT_CATEGORY_ID = EDH.CHILD_ID ' || fnd_global.newline ||
1007 ' AND EDH.TOP_NODE_FLAG = ''Y'' ' || fnd_global.newline ||
1008 ' AND EDH.OBJECT_TYPE = ''CATEGORY_SET'' ' || fnd_global.newline ||
1012 ' AND MDCS.FUNCTIONAL_AREA_ID = 11' || fnd_global.newline;
1009 ' AND EDH.OBJECT_ID = MDCS.CATEGORY_SET_ID ' || fnd_global.newline ||
1010 ' AND fact.aggregation_flag = :aggregation_flag_1' || fnd_global.newline ||
1011 ' AND EDH.DBI_FLAG = ''Y'' ' || fnd_global.newline ||
1013 ELSE
1014 l_mv := l_mv || ' WHERE fact.aggregation_flag = :aggregation_flag_1';
1015 END IF;
1016
1017 l_mv := l_mv || ' UNION ALL' || fnd_global.newline ||
1018 ' SELECT ' || fnd_global.newline;
1019
1020 IF l_mv_type <> 'ROOT' THEN
1021 l_mv := l_mv || ' FACT.ITEM_ORG_ID, ' || fnd_global.newline;
1022 END IF;
1023
1024 l_mv := l_mv || ' FACT.PRODUCT_CATEGORY_ID' || fnd_global.newline ||
1025 ' ,FACT.REPAIR_ORGANIZATION_ID' || fnd_global.newline ||
1026 ' ,FACT.REPAIR_TYPE_ID' || fnd_global.newline ||
1027 ' ,FACT.CUSTOMER_ID' || fnd_global.newline ||
1028 ' ,0 c_backlog' || fnd_global.newline ||
1029 ' ,(open_count - close_count) p_backlog' || fnd_global.newline ||
1030 ' ,0 not_promised_count' || fnd_global.newline ||
1031 ' ,0 past_due' || fnd_global.newline ||
1032 ' ,0 days_until_promised_b1' || fnd_global.newline ||
1033 ' ,0 days_until_promised_b2' || fnd_global.newline ||
1034 ' ,0 days_until_promised_b3' || fnd_global.newline ||
1035 ' ,0 days_until_promised_b4' || fnd_global.newline ||
1036 ' ,0 days_until_promised_b5' || fnd_global.newline ||
1037 ' ,0 days_until_promised_b6' || fnd_global.newline ||
1038 ' ,0 days_until_promised_b7' || fnd_global.newline ||
1039 ' ,0 days_until_promised_b8' || fnd_global.newline ||
1040 ' ,0 days_until_promised_b9' || fnd_global.newline ||
1041 ' ,0 days_until_promised_b10' || fnd_global.newline;
1042 IF l_mv_type = 'ROOT' THEN
1043 l_mv := l_mv ||
1044 ' FROM ISC_DR_BKLG_02_MV fact' || fnd_global.newline;
1045 ELSE
1046 l_mv := l_mv ||
1047 ' FROM ISC_DR_BKLG_01_MV fact' || fnd_global.newline;
1048 END IF;
1049 l_mv := l_mv ||
1050 ' ,FII_TIME_RPT_STRUCT_V CAL' || fnd_global.newline ||
1051 ' WHERE fact.time_id = cal.time_id' || fnd_global.newline ||
1052 ' AND cal.report_date in &BIS_PREVIOUS_ASOF_DATE' || fnd_global.newline ||
1053 ' AND fact.aggregation_flag = :aggregation_flag_2' || fnd_global.newline ||
1054 ' AND bitand(cal.record_type_id, 1143) = cal.record_type_id) ';
1055
1056 -- Add measure columns that need to be aggregated
1057
1058 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1059 p_col_name => 'c_backlog' ,
1060 p_alias_name => 'c_backlog',
1061 p_grand_total => 'Y',
1062 p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
1063 p_to_date_type => 'ITD');
1064
1065 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1066 p_col_name => 'not_promised' ,
1067 p_alias_name => 'not_promised',
1068 p_grand_total => 'Y',
1069 p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
1070 p_to_date_type => 'ITD');
1071
1072 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1073 p_col_name => 'p_backlog' ,
1074 p_alias_name => 'p_backlog',
1075 p_grand_total => 'Y',
1076 p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
1077 p_to_date_type => 'ITD');
1078
1079 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1080 p_col_name => 'PAST_DUE' ,
1081 p_alias_name => 'past_due',
1082 p_grand_total => 'Y',
1083 p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
1084 p_to_date_type => 'ITD');
1085
1086 poa_dbi_util_pkg.add_bucket_columns( p_short_name => 'ISC_DEPOT_DAYS_UNTIL_PROM'
1087 , p_col_tbl => l_col_tbl
1088 , p_col_name => 'DAYS_UNTIL_PROMISED'
1089 , p_alias_name => 'DUP_DISTRIBUTION'
1090 , p_grand_total => 'Y'
1091 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
1092 , p_to_date_type => 'ITD'
1093 , x_bucket_rec => l_bucket_rec
1094 );
1095
1096
1097 -- There is an issue in POA util. Whenever all the measures are not XTD it does not append and 1 = 1
1098 -- and rather does not expect an AND in where clause.
1099 l_where_clause := ' 1 = 1 ' || l_where_clause;
1100
1101 l_query := GET_DAYS_UNTIL_PROM_SEL_CLAUSE (l_view_by,l_bucket_rec,l_view_by_col)
1102 || ' from
1103 ' || poa_dbi_template_pkg.status_sql (p_fact_name => l_mv,
1107 p_col_name => l_col_tbl,
1104 p_where_clause => l_where_clause,
1105 p_join_tables => l_join_tbl,
1106 p_use_windowing => 'Y',
1108 p_use_grpid => 'N',
1109 p_paren_count => 3,
1110 p_filter_where => ' (BIV_MEASURE2 > 0 or BIV_MEASURE1> 0 ) ',
1111 p_generate_viewby => 'Y',
1112 p_in_join_tables => NULL);
1113
1114 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
1115 l_err_stage:='The query is : ' || l_query;
1116 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_BACKLOG_TBL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
1117 END IF;
1118
1119 -- prepare output for bind variables
1120 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
1121 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1122
1123 -- set the basic bind variables for the status SQL
1124 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
1125
1126 -- Passing AGGREGATION_LEVEL_FLAG to PMV
1127 l_custom_rec.attribute_name := ':aggregation_flag_1';
1128 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1129 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1130 l_custom_rec.attribute_value := l_agg_flag_1;
1131 x_custom_output.extend;
1132 x_custom_output(x_custom_output.count) := l_custom_rec;
1133
1134 -- Passing AGGREGATION_LEVEL_FLAG to PMV
1135 l_custom_rec.attribute_name := ':aggregation_flag_2';
1136 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1137 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1138 l_custom_rec.attribute_value := l_agg_flag_2;
1139 x_custom_output.extend;
1140 x_custom_output(x_custom_output.count) := l_custom_rec;
1141
1142 x_custom_sql := l_query;
1143
1144 EXCEPTION
1145 WHEN OTHERS THEN
1146 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
1147 l_err_stage:='The exception is : ' || SQLERRM;
1148 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_BACKLOG_TBL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
1149 END IF;
1150 l_err_stage:='The exception is : ' || SQLERRM;
1151 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_BACKLOG_TBL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
1152
1153 END GET_DAYS_UNTIL_PROM_tbl_sql;
1154
1155
1156 FUNCTION GET_DAYS_UNTIL_PROM_SEL_CLAUSE( p_view_by_dim IN VARCHAR2
1157 , p_bucket_rec IN bis_bucket_pub.bis_bucket_rec_type
1158 ,p_view_by_col IN VARCHAR2)
1159 RETURN VARCHAR2
1160 IS
1161
1162 l_sel_clause VARCHAR2(8000);
1163 l_view_by_col_name VARCHAR2(120);
1164 l_description VARCHAR2(30);
1165 l_drill_across_rep_1 VARCHAR2(500);
1166
1167 BEGIN
1168
1169 l_description := 'null';
1170 l_drill_across_rep_1 := 'null' ;
1171
1172 -- Item Description for item view by
1173 l_drill_across_rep_1 := get_drill_across (p_view_by_dim => p_view_by_dim, p_function_name =>'ISC_DEPOT_DUP_TBL_REP');
1174 IF (p_view_by_dim = 'ITEM+ENI_ITEM') THEN
1175 l_description := ' v. description ';
1176 END IF;
1177 l_sel_clause :=
1178 'SELECT '|| ISC_DEPOT_RPT_UTIL_PKG.get_viewby_select_clause (p_view_by_dim) || fnd_global.newline ||
1179 l_description || ' BIV_ATTRIBUTE1 ';
1180
1181 l_sel_clause := l_sel_clause ||
1182 ',BIV_MEASURE2
1183 ,BIV_MEASURE1
1184 ,BIV_MEASURE3
1185 ,BIV_MEASURE4
1186 ,BIV_MEASURE6
1187 ,BIV_MEASURE11
1188 ,BIV_MEASURE13
1189 ,BIV_MEASURE14
1190 ,BIV_MEASURE16';
1191
1192 l_sel_clause := l_sel_clause || poa_dbi_util_pkg.get_bucket_outer_query( p_bucket_rec => p_bucket_rec
1193 , p_col_name => 'BIV_MEASURE5'
1194 , p_alias_name => 'BIV_MEASURE5'
1195 , p_total_flag => 'N'
1196 , p_prefix => NULL
1197 , p_suffix => NULL
1198 ) || fnd_global.newline ;
1199
1200 l_sel_clause := l_sel_clause || poa_dbi_util_pkg.get_bucket_outer_query( p_bucket_rec => p_bucket_rec
1201 , p_col_name => 'BIV_MEASURE15'
1202 , p_alias_name => 'BIV_MEASURE15'
1203 , p_total_flag => 'N'
1204 , p_prefix => NULL
1205 , p_suffix => NULL
1206 ) || fnd_global.newline ;
1207
1208 l_sel_clause := l_sel_clause || ' ,'|| l_drill_across_rep_1 ||' BIV_DYNAMIC_URL1' || fnd_global.newline;
1209
1210 l_sel_clause := l_sel_clause || poa_dbi_util_pkg.get_bucket_outer_query( p_bucket_rec => p_bucket_rec
1211 , p_col_name => 'BIV_ATTRIBUTE10'
1212 , p_alias_name => 'BIV_ATTRIBUTE10'
1216 ) || fnd_global.newline ;
1213 , p_total_flag => 'N'
1214 , p_prefix => NULL
1215 , p_suffix => NULL
1217 l_sel_clause := l_sel_clause ||
1218 'FROM ( SELECT
1219 rank() over (&ORDER_BY_CLAUSE'||' nulls last, '||p_view_by_col||') - 1 rnk
1220 ,'||p_view_by_col;
1221
1222
1223 l_sel_clause := l_sel_clause ||
1224 ',BIV_MEASURE2
1225 ,BIV_MEASURE1
1226 ,BIV_MEASURE3
1227 ,BIV_MEASURE4
1228 ,BIV_MEASURE6
1229 ,BIV_MEASURE11
1230 ,BIV_MEASURE13
1231 ,BIV_MEASURE14
1232 ,BIV_MEASURE16';
1233
1234 l_sel_clause := l_sel_clause || poa_dbi_util_pkg.get_bucket_outer_query( p_bucket_rec => p_bucket_rec
1235 , p_col_name => 'BIV_MEASURE5'
1236 , p_alias_name => 'BIV_MEASURE5'
1237 , p_total_flag => 'N'
1238 , p_prefix => NULL
1239 , p_suffix => NULL
1240 ) || fnd_global.newline ;
1241
1242 l_sel_clause := l_sel_clause || poa_dbi_util_pkg.get_bucket_outer_query( p_bucket_rec => p_bucket_rec
1243 , p_col_name => 'BIV_MEASURE15'
1244 , p_alias_name => 'BIV_MEASURE15'
1245 , p_total_flag => 'N'
1246 , p_prefix => NULL
1247 , p_suffix => NULL
1248 ) || fnd_global.newline ;
1249
1250 l_sel_clause := l_sel_clause || poa_dbi_util_pkg.get_bucket_outer_query( p_bucket_rec => p_bucket_rec
1251 , p_col_name => 'BIV_ATTRIBUTE10'
1252 , p_alias_name => 'BIV_ATTRIBUTE10'
1253 , p_total_flag => 'N'
1254 , p_prefix => NULL
1255 , p_suffix => NULL
1256 ) || fnd_global.newline ;
1257 l_sel_clause := l_sel_clause ||
1258 'FROM (
1259 SELECT ' || p_view_by_col || fnd_global.newline ||
1260 ',' || ' NVL(c_p_backlog,0) BIV_MEASURE2 ' || fnd_global.newline ||
1261 ',' || ' NVL(c_c_backlog,0) BIV_MEASURE1 ' || fnd_global.newline ||
1262 ',' || poa_dbi_util_pkg.change_clause( cur_col => 'c_c_backlog'
1263 ,prior_col => 'c_p_backlog'
1264 ,change_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
1265 || ' BIV_MEASURE3' || fnd_global.newline ||
1266 ',' || 'NVL(c_past_due,0) BIV_MEASURE4 ' || fnd_global.newline ;
1267 l_sel_clause := l_sel_clause ||
1268 poa_dbi_util_pkg.get_bucket_outer_query( p_bucket_rec => p_bucket_rec
1269 , p_col_name => 'c_DUP_DISTRIBUTION'
1270 , p_alias_name => 'BIV_MEASURE5'
1271 , p_total_flag => 'N'
1272 , p_prefix => NULL
1273 , p_suffix => NULL
1274 ) || fnd_global.newline ;
1275 l_sel_clause := l_sel_clause ||
1276 ',' || ' NVL(c_not_promised,0) BIV_MEASURE6 ' || fnd_global.newline ||
1277 ',' || ' NVL(c_c_backlog_total,0) BIV_MEASURE11 ' || fnd_global.newline ||
1278 ',' || poa_dbi_util_pkg.change_clause( cur_col => 'c_c_backlog_total'
1279 ,prior_col => 'c_p_backlog_total'
1280 ,change_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
1281 || ' BIV_MEASURE13' || fnd_global.newline ||
1282 ',' || ' NVL(c_past_due_total,0) BIV_MEASURE14 ' || fnd_global.newline ;
1283 l_sel_clause := l_sel_clause ||
1284 poa_dbi_util_pkg.get_bucket_outer_query( p_bucket_rec => p_bucket_rec
1285 , p_col_name => 'c_DUP_DISTRIBUTION'
1286 , p_alias_name => 'BIV_MEASURE15'
1287 , p_total_flag => 'Y'
1288 , p_prefix => NULL
1289 , p_suffix => NULL
1290 ) || fnd_global.newline ;
1291 l_sel_clause := l_sel_clause ||
1292 ',' || ' NVL(c_not_promised_total,0) BIV_MEASURE16 ' || fnd_global.newline ;
1293
1294 l_sel_clause := l_sel_clause ||
1295 poa_dbi_util_pkg.get_bucket_drill_url( p_bucket_rec
1296 , 'BIV_ATTRIBUTE10'
1297 ,'''pFunctionName=ISC_DEPOT_BACKLOG_DTL_TBL_REP&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&BIV_ATTRIBUTE1=-1&BIV_DR_BACKLOG_BUCKET+BIV_DR_BACKLOG_BUCKET='
1298 , ''''
1299 , p_add_bucket_num => 'Y');
1300
1301 RETURN l_sel_clause;
1302
1303 END GET_DAYS_UNTIL_PROM_SEL_CLAUSE;
1304
1305 FUNCTION get_drill_across (p_view_by_dim IN VARCHAR2,p_function_name IN VARCHAR2)
1306 RETURN VARCHAR2
1307 IS
1308 l_drill_across varchar2 (500);
1309 BEGIN
1310 l_drill_across := 'NULL';
1311 IF (p_view_by_dim = 'ITEM+ENI_ITEM_VBH_CAT') THEN
1312 l_drill_across := 'decode(v.leaf_node_flag, ''Y'',
1313 ''pFunctionName='|| p_function_name ||'&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM&pParamIds=Y'',
1314 ''pFunctionName='|| p_function_name ||'&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'') ';
1315 END IF;
1316 RETURN l_drill_across;
1317 END get_drill_across ;
1318
1319 END ISC_DEPOT_BACKLOG_PKG;