[Home] [Help]
PACKAGE BODY: APPS.ISC_DEPOT_COMPLETION_PKG
Source
1 PACKAGE BODY ISC_DEPOT_COMPLETION_PKG AS
2 --$Header: iscdepotcomprqb.pls 120.0 2005/05/25 17:25:22 appldev noship $
3
4 FUNCTION GET_COMPLETION_TBL_SEL_CLAUSE (p_view_by_dim IN VARCHAR2
5 ,p_view_by_col IN VARCHAR2)
6 RETURN VARCHAR2;
7
8
9 FUNCTION GET_COMPLETION_TRD_SEL_CLAUSE (p_view_by_dim IN VARCHAR2)
10 RETURN VARCHAR2;
11
12 FUNCTION GET_DRILL_ACROSS (p_view_by_dim IN VARCHAR2,p_function_name IN VARCHAR2)
13 RETURN VARCHAR2;
14
15 PROCEDURE GET_COMPLETION_TBL_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
16 x_custom_sql OUT NOCOPY VARCHAR2,
17 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
18 IS
19 l_query ISC_DEPOT_RPT_UTIL_PKG.g_query_typ%type;
20 l_view_by ISC_DEPOT_RPT_UTIL_PKG.g_view_by_typ%type;
21 l_view_by_col ISC_DEPOT_RPT_UTIL_PKG.g_view_by_col_typ%type;
22 l_xtd ISC_DEPOT_RPT_UTIL_PKG.g_xtd_typ%type;
23 l_comparison_type VARCHAR2(1);
24 l_cur_suffix VARCHAR2(2);
25 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
26 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
27 l_where_clause ISC_DEPOT_RPT_UTIL_PKG.g_where_clause_typ%type;
28 l_mv ISC_DEPOT_RPT_UTIL_PKG.g_mv_typ%type;
29 l_mv_set VARCHAR2(50);
30 l_aggregation_flag NUMBER;
31 l_custom_rec BIS_QUERY_ATTRIBUTES;
32 l_mv_type VARCHAR2(10);
33 l_err_stage VARCHAR2(32767);
34 l_debug_mode VARCHAR2(1);
35 l_module_name ISC_DEPOT_RPT_UTIL_PKG.g_module_name_typ%type ;
36
37 BEGIN
38 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
39 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
40 l_comparison_type := 'Y';
41
42 -- clear out the tables.
43 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
44 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
45
46 -- get all the query parameters
47 ISC_DEPOT_RPT_UTIL_PKG.process_parameters (p_param => p_param,
48 x_view_by => l_view_by,
49 x_view_by_col_name => l_view_by_col,
50 x_comparison_type => l_comparison_type,
51 x_xtd => l_xtd,
52 x_cur_suffix => l_cur_suffix,
53 x_where_clause => l_where_clause,
54 x_mv => l_mv,
55 x_join_tbl => l_join_tbl,
56 x_mv_type => l_mv_type,
57 x_aggregation_flag => l_aggregation_flag,
58 p_trend => 'N',
59 p_mv_set => 'BKLG',
60 x_custom_output => x_custom_output);
61
62 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
63 l_err_stage:='After calling DRM_DBI_RPT_UTIL_PKG.process_parameters';
64 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_COMPLETION_TBL : ' ,l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
65 END IF;
66
67 -- Add measure columns that need to be aggregated
68 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
69 p_col_name => 'close_count' ,
70 p_alias_name => 'completed_count',
71 p_grand_total => 'Y',
72 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
73 p_to_date_type => 'XTD');
74
75 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
76 p_col_name => 'COMPLETE_WITH_PROMISE_DATE_CNT' ,
77 p_alias_name => 'cmplt_with_prom_dt',
78 p_grand_total => 'Y',
79 p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
80 p_to_date_type => 'XTD');
81
82 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
83 p_col_name => 'late_complete_count' ,
84 p_alias_name => 'late_complete_count',
85 p_grand_total => 'Y',
86 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
87 p_to_date_type => 'XTD');
88
89 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
90 p_col_name => 'days_late' ,
91 p_alias_name => 'days_late',
92 p_grand_total => 'Y',
93 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
94 p_to_date_type => 'XTD');
95
96
97
98 l_query := GET_COMPLETION_TBL_SEL_CLAUSE ( p_view_by_dim => l_view_by
99 ,p_view_by_col => l_view_by_col )
100 || ' from
101 ' || poa_dbi_template_pkg.status_sql (p_fact_name => l_mv,
102 p_where_clause => l_where_clause,
103 p_join_tables => l_join_tbl,
104 p_use_windowing => 'Y',
105 p_col_name => l_col_tbl,
106 p_use_grpid => 'N',
107 p_paren_count => 3,
108 p_filter_where => ' (BIV_MEASURE1 > 0 or BIV_MEASURE11 > 0 ) ',
109 p_generate_viewby => 'Y',
110 p_in_join_tables => NULL);
111
112 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
113 l_err_stage:='The query is : ' || l_query;
114 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_COMPLETION_TBL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
115 END IF;
116
117 -- prepare output for bind variables
118 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
119 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
120
121 -- set the basic bind variables for the status SQL
122 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
123
124 -- Passing AGGREGATION_LEVEL_FLAG to PMV
125 l_custom_rec.attribute_name := ':AGGREGATION_FLAG';
126 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
127 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
128 l_custom_rec.attribute_value := l_aggregation_flag;
129 x_custom_output.extend;
130 x_custom_output(x_custom_output.count) := l_custom_rec;
131
132 x_custom_sql := l_query;
133
134 EXCEPTION
135
136 WHEN OTHERS THEN
137 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
138 l_err_stage:='The exception is : ' || SQLERRM;
139 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_COMPLETION_TBL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
140 END IF;
141
142 END GET_COMPLETION_TBL_SQL;
143
144
145 FUNCTION GET_COMPLETION_TBL_SEL_CLAUSE( p_view_by_dim IN VARCHAR2
146 ,p_view_by_col IN VARCHAR2)
147 RETURN VARCHAR2
148 IS
149
150 l_sel_clause VARCHAR2(8000);
151 l_view_by_col_name VARCHAR2(120);
152 l_description VARCHAR2(30);
153 l_drill_across_rep_1 VARCHAR2(500);
154 l_drill_across_rep_2 VARCHAR2(500);
155
156 BEGIN
157 l_description := 'null';
158 l_drill_across_rep_1 := 'null' ;
159 l_drill_across_rep_2 := 'null' ;
160
161 -- Item Description for item view by
162 l_drill_across_rep_1 := get_drill_across (p_view_by_dim => p_view_by_dim, p_function_name =>'ISC_DEPOT_COMPLETION_TBL_REP');
163 IF (p_view_by_dim IN ('ITEM+ENI_ITEM')) THEN
164 l_description := ' v. description ';
165 END IF;
166
167 IF (p_view_by_dim IN ('ITEM+ENI_ITEM', 'CUSTOMER+PROSPECT')) THEN
168 l_drill_across_rep_2 := '''pFunctionName=ISC_DEPOT_COMP_DTL_TBL_REP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM&pParamIds=Y''';
169 END IF;
170
171 l_sel_clause :=
172 'SELECT '|| ISC_DEPOT_RPT_UTIL_PKG.get_viewby_select_clause (p_view_by_dim) || fnd_global.newline ||
173 l_description || ' BIV_ATTRIBUTE1
174 ,BIV_MEASURE1
175 ,BIV_MEASURE11
176 ,BIV_MEASURE2
177 ,BIV_MEASURE3
178 ,BIV_MEASURE4
179 ,BIV_MEASURE5
180 ,BIV_MEASURE12
181 ,BIV_MEASURE6
182 ,BIV_MEASURE7
183 ,BIV_MEASURE13
184 ,BIV_MEASURE8
185 ,BIV_MEASURE21
186 ,BIV_MEASURE22
187 ,BIV_MEASURE23
188 ,BIV_MEASURE24
189 ,BIV_MEASURE25
190 ,BIV_MEASURE26
191 ,BIV_MEASURE27
192 ,BIV_MEASURE28
193 ,BIV_MEASURE29
194 ,BIV_MEASURE30
195 ,BIV_MEASURE31
196 ,BIV_MEASURE32
197 ,BIV_MEASURE33
198 ,BIV_MEASURE34
199 ,BIV_MEASURE35
200 ,BIV_MEASURE36
201 ,' || l_drill_across_rep_1 || ' BIV_DYNAMIC_URL1
202 ,BIV_DYNAMIC_URL_2 ' || fnd_global.newline ||
203 'FROM ( SELECT
204 rank() over (&ORDER_BY_CLAUSE'||' nulls last, '||p_view_by_col||' ) - 1 rnk
205 ,'||p_view_by_col||'
206 ,BIV_MEASURE1
207 ,BIV_MEASURE11
208 ,BIV_MEASURE2
209 ,BIV_MEASURE3
210 ,BIV_MEASURE4
211 ,BIV_MEASURE5
212 ,BIV_MEASURE12
213 ,BIV_MEASURE6
214 ,BIV_MEASURE7
215 ,BIV_MEASURE13
216 ,BIV_MEASURE8
217 ,BIV_MEASURE21
218 ,BIV_MEASURE22
219 ,BIV_MEASURE23
220 ,BIV_MEASURE24
221 ,BIV_MEASURE25
222 ,BIV_MEASURE26
223 ,BIV_MEASURE27
224 ,BIV_MEASURE28
225 ,BIV_MEASURE29
226 ,BIV_MEASURE30
227 ,BIV_MEASURE31
228 ,BIV_MEASURE32
229 ,BIV_MEASURE33
230 ,BIV_MEASURE34
231 ,BIV_MEASURE35
232 ,BIV_MEASURE36
233 ,BIV_DYNAMIC_URL_2 ' || fnd_global.newline ||
234 ' FROM ( SELECT ' || fnd_global.newline ||
235 p_view_by_col || fnd_global.newline ||
236 ',' || 'NVL(c_completed_count,0) BIV_MEASURE1 ' || fnd_global.newline ||
237 ',' || 'NVL(p_completed_count,0) BIV_MEASURE11 ' || fnd_global.newline ||
238 ',' || poa_dbi_util_pkg.change_clause( cur_col => 'c_completed_count'
239 ,prior_col => 'p_completed_count'
240 ,change_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
241 || ' BIV_MEASURE2' || fnd_global.newline ||
242 ',' || 'NVL(c_cmplt_with_prom_dt,0) BIV_MEASURE3 ' || fnd_global.newline ||
243 ',' || 'NVL(c_late_complete_count,0) BIV_MEASURE4 ' || fnd_global.newline ||
244 ',' || poa_dbi_util_pkg.rate_clause( numerator => 'c_late_complete_count'
245 ,denominator => 'c_completed_count'
246 ,rate_type => 'P') -- 'P' for Percent ; 'NP' for non percent
247 || 'BIV_MEASURE5' || fnd_global.newline ||
248 ',' || poa_dbi_util_pkg.rate_clause( numerator => 'p_late_complete_count'
249 ,denominator => 'p_completed_count'
253 p_new_denominator => 'c_completed_count',
250 ,rate_type => 'P') -- 'P' for Percent ; 'NP' for non percent
251 || ' BIV_MEASURE12' || fnd_global.newline ||
252 ',' || OPI_DBI_RPT_UTIL_PKG.change_pct_str(p_new_numerator => 'c_late_complete_count',
254 p_old_numerator => 'p_late_complete_count',
255 p_old_denominator => 'p_completed_count',
256 p_measure_name => 'BIV_MEASURE6')
257 || fnd_global.newline ||
258 ',' || poa_dbi_util_pkg.rate_clause( numerator => 'c_days_late'
259 ,denominator => 'c_late_complete_count'
260 ,rate_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
261 || ' BIV_MEASURE7' || fnd_global.newline ||
262 ',' || poa_dbi_util_pkg.rate_clause( numerator => 'p_days_late'
263 ,denominator => 'p_late_complete_count'
264 ,rate_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
265 || ' BIV_MEASURE13' || fnd_global.newline ||
266 ',' || poa_dbi_util_pkg.rate_clause( numerator => 'c_days_late'
267 ,denominator => 'c_late_complete_count'
268 ,rate_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
269 || ' - '
270 || poa_dbi_util_pkg.rate_clause( numerator => 'p_days_late'
271 ,denominator => 'p_late_complete_count'
272 ,rate_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
273 || ' BIV_MEASURE8' || fnd_global.newline ||
274 ',' || 'NVL(c_completed_count_total,0) BIV_MEASURE21 ' || fnd_global.newline ||
275 ',' || poa_dbi_util_pkg.change_clause( cur_col => 'c_completed_count_total'
276 ,prior_col => 'p_completed_count_total'
277 ,change_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
278 || 'BIV_MEASURE22' || fnd_global.newline ||
279 ',' || 'NVL(c_cmplt_with_prom_dt_total,0) BIV_MEASURE23 ' || fnd_global.newline ||
280 ',' || 'NVL(c_late_complete_count_total,0) BIV_MEASURE24 ' || fnd_global.newline ||
281 ',' || poa_dbi_util_pkg.rate_clause( numerator => 'c_late_complete_count_total'
282 ,denominator => 'c_completed_count_total'
283 ,rate_type => 'P') -- 'P' for Percent ; 'NP' for non percent
284 || 'BIV_MEASURE25' || fnd_global.newline ||
285 ',' || OPI_DBI_RPT_UTIL_PKG.change_pct_str(p_new_numerator => 'c_late_complete_count_total',
286 p_new_denominator => 'c_completed_count_total',
287 p_old_numerator => 'p_late_complete_count_total',
288 p_old_denominator => 'p_completed_count_total',
289 p_measure_name => 'BIV_MEASURE26')
290 || fnd_global.newline ||
291 ',' || poa_dbi_util_pkg.rate_clause( numerator => 'c_days_late_total'
292 ,denominator => 'c_late_complete_count_total'
293 ,rate_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
294 || ' BIV_MEASURE27' || fnd_global.newline ||
295 ',' || poa_dbi_util_pkg.rate_clause( numerator => 'c_days_late_total'
296 ,denominator => 'c_late_complete_count_total'
297 ,rate_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
298 || ' - '
299 || poa_dbi_util_pkg.rate_clause( numerator => 'p_days_late_total'
300 ,denominator => 'p_late_complete_count_total'
301 ,rate_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
302 || ' BIV_MEASURE28' || fnd_global.newline ||
303 ',' || 'NVL(c_completed_count,0) BIV_MEASURE29 ' || fnd_global.newline ||
304 ',' || 'NVL(p_completed_count,0) BIV_MEASURE30 ' || fnd_global.newline ||
305 ',' || 'NVL(c_completed_count_total,0) BIV_MEASURE31 ' || fnd_global.newline ||
306 ',' || 'NVL(p_completed_count_total,0) BIV_MEASURE32 ' || fnd_global.newline ||
307 ',' || poa_dbi_util_pkg.rate_clause( numerator => 'c_late_complete_count'
308 ,denominator => 'c_completed_count'
309 ,rate_type => 'P') -- 'P' for Percent ; 'NP' for non percent
310 || 'BIV_MEASURE33' || fnd_global.newline ||
311 ',' || poa_dbi_util_pkg.rate_clause( numerator => 'p_late_complete_count'
312 ,denominator => 'p_completed_count'
313 ,rate_type => 'P') -- 'P' for Percent ; 'NP' for non percent
317 ,rate_type => 'P') -- 'P' for Percent ; 'NP' for non percent
314 || 'BIV_MEASURE34' || fnd_global.newline ||
315 ',' || poa_dbi_util_pkg.rate_clause( numerator => 'c_late_complete_count_total'
316 ,denominator => 'c_completed_count_total'
318 || 'BIV_MEASURE35' || fnd_global.newline ||
319 ',' || poa_dbi_util_pkg.rate_clause( numerator => 'p_late_complete_count_total'
320 ,denominator => 'p_completed_count_total'
321 ,rate_type => 'P') -- 'P' for Percent ; 'NP' for non percent
322 || 'BIV_MEASURE36' || fnd_global.newline ||
323 ',' || l_drill_across_rep_2 || ' BIV_DYNAMIC_URL_2 ' || fnd_global.newline;
324 RETURN l_sel_clause;
325
326 END GET_COMPLETION_TBL_SEL_CLAUSE;
327
328 PROCEDURE GET_COMPLETION_TRD_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
329 x_custom_sql OUT NOCOPY VARCHAR2,
330 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
331 IS
332
333 l_query ISC_DEPOT_RPT_UTIL_PKG.g_query_typ%type;
334 l_view_by ISC_DEPOT_RPT_UTIL_PKG.g_view_by_typ%type;
335 l_view_by_col ISC_DEPOT_RPT_UTIL_PKG.g_view_by_col_typ%type;
336 l_xtd ISC_DEPOT_RPT_UTIL_PKG.g_xtd_typ%type;
337 l_comparison_type VARCHAR2(1);
338 l_cur_suffix VARCHAR2(2);
339 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
340 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
341 l_where_clause ISC_DEPOT_RPT_UTIL_PKG.g_where_clause_typ%type;
342 l_mv ISC_DEPOT_RPT_UTIL_PKG.g_mv_typ%type;
343 l_mv_set VARCHAR2(50);
344 l_aggregation_flag NUMBER;
345 l_custom_rec BIS_QUERY_ATTRIBUTES;
346 l_mv_type VARCHAR2(10);
347 l_err_stage VARCHAR2(32767);
348 l_debug_mode VARCHAR2(1);
349 l_module_name ISC_DEPOT_RPT_UTIL_PKG.g_module_name_typ%type ;
350
351 BEGIN
352 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
353 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
354 l_comparison_type := 'Y';
355
356 -- clear out the tables.
357 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
358 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
359
360 -- get all the query parameters
361 ISC_DEPOT_RPT_UTIL_PKG.process_parameters (p_param => p_param,
362 x_view_by => l_view_by,
363 x_view_by_col_name => l_view_by_col,
364 x_comparison_type => l_comparison_type,
365 x_xtd => l_xtd,
366 x_cur_suffix => l_cur_suffix,
367 x_where_clause => l_where_clause,
368 x_mv => l_mv,
369 x_join_tbl => l_join_tbl,
370 x_mv_type => l_mv_type,
371 x_aggregation_flag => l_aggregation_flag,
372 p_trend => 'Y',
373 p_mv_set => 'BKLG',
374 x_custom_output => x_custom_output);
375
376
377 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
378 l_err_stage:='After calling DRM_DBI_RPT_UTIL_PKG.process_parameters';
379 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_COMPLETION_TRD : ' ,l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
380 END IF;
381
382 -- Add measure columns that need to be aggregated
383 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
384 p_col_name => 'close_count' ,
385 p_alias_name => 'completed_count',
386 p_grand_total => 'N',
387 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
388 p_to_date_type => 'XTD');
389
390 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
391 p_col_name => 'late_complete_count' ,
392 p_alias_name => 'late_complete_count',
393 p_grand_total => 'N',
394 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
395 p_to_date_type => 'XTD');
396
397 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
398 p_col_name => 'days_late' ,
399 p_alias_name => 'days_late',
400 p_grand_total => 'N',
401 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
402 p_to_date_type => 'XTD');
403
404 l_query := GET_COMPLETION_TRD_SEL_CLAUSE (l_view_by)
405 || ' from
406 ' || poa_dbi_template_pkg.trend_sql(p_xtd => l_xtd,
407 p_comparison_type => l_comparison_type,
408 p_fact_name => l_mv,
412 p_in_join_tables => NULL);
409 p_where_clause => l_where_clause,
410 p_col_name => l_col_tbl,
411 p_use_grpid => 'N',
413
414 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
415 l_err_stage:='The query is : ' || l_query;
416 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_COMPLETION_TBL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
417 END IF;
418
419 -- prepare output for bind variables
420 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
421 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
422
423 -- set the basic bind variables for the status SQL
424 poa_dbi_util_pkg.get_custom_trend_binds (p_xtd => l_xtd,
425 p_comparison_type => l_comparison_type,
426 x_custom_output => x_custom_output);
427
428 -- Passing AGGREGATION_LEVEL_FLAG to PMV
429 l_custom_rec.attribute_name := ':AGGREGATION_FLAG';
430 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
431 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
432 l_custom_rec.attribute_value := l_aggregation_flag;
433 x_custom_output.extend;
434 x_custom_output(x_custom_output.count) := l_custom_rec;
435
436 x_custom_sql := l_query;
437 EXCEPTION
438
439 WHEN OTHERS THEN
440 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
441 l_err_stage:='The exception is : ' || SQLERRM;
442 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_COMPLETION_TRD : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
443 END IF;
444
445 END GET_COMPLETION_TRD_SQL;
446
447 FUNCTION GET_COMPLETION_TRD_SEL_CLAUSE(p_view_by_dim IN VARCHAR2)
448 RETURN VARCHAR2
449 IS
450
451 l_sel_clause VARCHAR2(8000);
452 l_view_by_col_name VARCHAR2(120);
453 l_description VARCHAR2(30);
454
455 BEGIN
456 l_description := 'null';
457
458 l_sel_clause :=
459 'SELECT cal.name VIEWBY ' || fnd_global.newline ||
460 ',' || 'NVL(iset.c_completed_count,0) BIV_MEASURE1 ' || fnd_global.newline ||
461 ',' || 'NVL(iset.p_completed_count,0) BIV_MEASURE11 ' || fnd_global.newline ||
462 ',' || poa_dbi_util_pkg.change_clause( cur_col => 'c_completed_count'
463 ,prior_col => 'p_completed_count'
464 ,change_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
465 || ' BIV_MEASURE2' || fnd_global.newline ||
466 ',' || 'NVL(iset.c_late_complete_count,0) BIV_MEASURE3 ' || fnd_global.newline ||
467 ',' || poa_dbi_util_pkg.rate_clause( numerator => 'c_late_complete_count'
468 ,denominator => 'c_completed_count'
469 ,rate_type => 'P') -- 'P' for Percent ; 'NP' for non percent
470 || 'BIV_MEASURE4' || fnd_global.newline ||
471 ',' || poa_dbi_util_pkg.rate_clause( numerator => 'p_late_complete_count'
472 ,denominator => 'p_completed_count'
473 ,rate_type => 'P') -- 'P' for Percent ; 'NP' for non percent
474 || ' BIV_MEASURE12' || fnd_global.newline ||
475 ',' || OPI_DBI_RPT_UTIL_PKG.change_pct_str(p_new_numerator => 'c_late_complete_count',
476 p_new_denominator => 'c_completed_count',
477 p_old_numerator => 'p_late_complete_count',
478 p_old_denominator => 'p_completed_count',
479 p_measure_name => 'BIV_MEASURE5')
480 || fnd_global.newline ||
481 ',' || poa_dbi_util_pkg.rate_clause( numerator => 'c_days_late'
482 ,denominator => 'c_late_complete_count'
483 ,rate_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
484 || ' BIV_MEASURE6' || fnd_global.newline ||
485 ',' || poa_dbi_util_pkg.rate_clause( numerator => 'p_days_late'
486 ,denominator => 'p_late_complete_count'
487 ,rate_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
488 || ' BIV_MEASURE13' || fnd_global.newline ||
489 ',' || poa_dbi_util_pkg.rate_clause( numerator => 'c_days_late'
490 ,denominator => 'c_late_complete_count'
491 ,rate_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
492 || ' - '
493 || poa_dbi_util_pkg.rate_clause( numerator => 'p_days_late'
494 ,denominator => 'p_late_complete_count'
495 ,rate_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
496 || ' BIV_MEASURE7' || fnd_global.newline;
497 RETURN l_sel_clause;
498
499 END GET_COMPLETION_TRD_SEL_CLAUSE;
500
501
505 IS
502 PROCEDURE GET_COMPLETION_DTL_TBL_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
503 x_custom_sql OUT NOCOPY VARCHAR2,
504 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
506
507 l_query ISC_DEPOT_RPT_UTIL_PKG.g_query_typ%type;
508 l_view_by ISC_DEPOT_RPT_UTIL_PKG.g_view_by_typ%type;
509 l_view_by_col ISC_DEPOT_RPT_UTIL_PKG.g_view_by_col_typ%type;
510 l_xtd ISC_DEPOT_RPT_UTIL_PKG.g_xtd_typ%type;
511 l_comparison_type VARCHAR2(1);
512 l_cur_suffix VARCHAR2(1);
513 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
514 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
515 l_where_clause ISC_DEPOT_RPT_UTIL_PKG.g_where_clause_typ%type;
516 l_mv ISC_DEPOT_RPT_UTIL_PKG.g_mv_typ%type;
517 l_mv_set VARCHAR2(50);
518 l_aggregation_flag NUMBER;
519 l_custom_rec BIS_QUERY_ATTRIBUTES;
520 l_mv_type VARCHAR2(10);
521 l_err_stage VARCHAR2(32767);
522 l_debug_mode VARCHAR2(1);
523 l_module_name ISC_DEPOT_RPT_UTIL_PKG.g_module_name_typ%type ;
524
525 BEGIN
526 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
527 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
528
529 -- clear out the tables.
530 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
531 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
532
533 -- get all the query parameters
534 ISC_DEPOT_RPT_UTIL_PKG.process_parameters (p_param => p_param,
535 x_view_by => l_view_by,
536 x_view_by_col_name => l_view_by_col,
537 x_comparison_type => l_comparison_type,
538 x_xtd => l_xtd,
539 x_cur_suffix => l_cur_suffix,
540 x_where_clause => l_where_clause,
541 x_mv => l_mv,
542 x_join_tbl => l_join_tbl,
543 x_mv_type => l_mv_type,
544 x_aggregation_flag => l_aggregation_flag,
545 p_trend => 'N',
546 p_mv_set => 'CMPDTL1',
547 x_custom_output => x_custom_output);
548
549 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
550 l_err_stage:='After calling DRM_DBI_RPT_UTIL_PKG.process_parameters';
551 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_COMPLETION_DTL : ' ,l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
552 END IF;
553
554 l_query :=
555 'SELECT
556 BIV_ATTRIBUTE1
557 ,BIV_ATTRIBUTE2
558 ,BIV_ATTRIBUTE3
559 ,BIV_ATTRIBUTE4
560 ,BIV_ATTRIBUTE5
561 ,BIV_ATTRIBUTE6
562 ,BIV_MEASURE1
563 ,BIV_ATTRIBUTE7
564 ,BIV_ATTRIBUTE8
565 ,BIV_DATE1
566 ,BIV_DATE2
567 ,BIV_MEASURE2
568 ,BIV_MEASURE3
569 ,''pFunctionName=CSD_RO_DETAILS&csdInvOrgId=''||BIV_MEASURE3||''&csdRepairLineId=''||BIV_MEASURE2 BIV_DYNAMIC_URL1
570 ,' || ISC_DEPOT_RPT_UTIL_PKG.get_service_request_url || ' || BIV_ATTRIBUTE8 BIV_DYNAMIC_URL2
571 FROM (
572 SELECT
573 rank() over (&ORDER_BY_CLAUSE nulls last,BIV_ATTRIBUTE1) - 1 rnk
574 ,BIV_ATTRIBUTE1
575 ,BIV_ATTRIBUTE2
576 ,BIV_ATTRIBUTE3
577 ,BIV_ATTRIBUTE4
578 ,BIV_ATTRIBUTE5
579 ,BIV_ATTRIBUTE6
580 ,BIV_MEASURE1
581 ,BIV_ATTRIBUTE7
582 ,BIV_ATTRIBUTE8
583 ,BIV_DATE1
584 ,BIV_DATE2
585 ,BIV_MEASURE2
586 ,BIV_MEASURE3
587 FROM (
588 SELECT repair_number BIV_ATTRIBUTE1 ' || fnd_global.newline ||
589 ',' || ' incident_number BIV_ATTRIBUTE2 ' || fnd_global.newline ||
590 ',' || ' crt.name BIV_ATTRIBUTE3 ' || fnd_global.newline ||
591 ',' || ' eiov.value BIV_ATTRIBUTE4 ' || fnd_global.newline ||
592 ',' || ' eiov.description BIV_ATTRIBUTE5 ' || fnd_global.newline ||
593 ',' || ' mum.unit_of_measure BIV_ATTRIBUTE6 ' || fnd_global.newline ||
594 ',' || ' quantity BIV_MEASURE1 ' || fnd_global.newline ||
595 ',' || ' serial_number BIV_ATTRIBUTE7 ' || fnd_global.newline ||
596 ',' || ' incident_id BIV_ATTRIBUTE8 ' || fnd_global.newline ||
597 ',' || ' promise_date BIV_DATE1 ' || fnd_global.newline ||
598 ',' || ' date_closed BIV_DATE2 ' || fnd_global.newline ||
599 ',' || ' fact.repair_line_id BIV_MEASURE2 ' || fnd_global.newline ||
600 ',' || ' fact.master_organization_id BIV_MEASURE3 ' || fnd_global.newline
601 || ' from ' || fnd_global.newline
602 || l_mv || fnd_global.newline
603 || ' ISC_DR_REPAIR_ORDERS_F fact, ' || fnd_global.newline
607 || ' WHERE dbi_date_closed between &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_ASOF_DATE ' || fnd_global.newline
604 || ' ENI_ITEM_V EIOV, ' || fnd_global.newline
605 || ' FND_LOOKUPS FL, ' || fnd_global.newline
606 || ' MTL_UNITS_OF_MEASURE_VL MUM ' || fnd_global.newline
608 || ' AND FL.LOOKUP_TYPE = ''CSD_REPAIR_STATUS'' ' || fnd_global.newline
609 || ' AND FL.LOOKUP_CODE = fact.status ' || fnd_global.newline
610 || ' AND FACT.item_org_id = eiov.id ' || fnd_global.newline
611 || ' AND mum.uom_code = fact.uom_code ' || fnd_global.newline
612 || l_where_clause || fnd_global.newline
613 || ' ) ) where (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
614 ORDER BY rnk' || fnd_global.newline ;
615
616 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
617 l_err_stage:='The query is : ' || l_query;
618 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_COMPLETION_DTL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
619 END IF;
620 x_custom_sql := l_query;
621
622 EXCEPTION
623
624 WHEN OTHERS THEN
625 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
626 l_err_stage:='The exception is : ' || SQLERRM;
627 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_COMPLETION_DTL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
628 END IF;
629
630 END GET_COMPLETION_DTL_TBL_SQL;
631
632 PROCEDURE GET_LAT_COMP_DTL_TBL_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
633 x_custom_sql OUT NOCOPY VARCHAR2,
634 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
635 IS
636
637 l_query ISC_DEPOT_RPT_UTIL_PKG.g_query_typ%type;
638 l_view_by ISC_DEPOT_RPT_UTIL_PKG.g_view_by_typ%type;
639 l_view_by_col ISC_DEPOT_RPT_UTIL_PKG.g_view_by_col_typ%type;
640 l_xtd ISC_DEPOT_RPT_UTIL_PKG.g_xtd_typ%type;
641 l_comparison_type VARCHAR2(1);
642 l_cur_suffix VARCHAR2(1);
643 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
644 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
645 l_where_clause ISC_DEPOT_RPT_UTIL_PKG.g_where_clause_typ%type;
646 l_mv ISC_DEPOT_RPT_UTIL_PKG.g_mv_typ%type;
647 l_mv_set VARCHAR2(50);
648 l_aggregation_flag NUMBER;
649 l_custom_rec BIS_QUERY_ATTRIBUTES;
650 l_mv_type VARCHAR2(10);
651 l_err_stage VARCHAR2(32767);
652 l_debug_mode VARCHAR2(1);
653 l_module_name ISC_DEPOT_RPT_UTIL_PKG.g_module_name_typ%type ;
654
655 BEGIN
656 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
657 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
658
659 -- clear out the tables.
660 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
661 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
662
663 -- get all the query parameters
664 ISC_DEPOT_RPT_UTIL_PKG.process_parameters (p_param => p_param,
665 x_view_by => l_view_by,
666 x_view_by_col_name => l_view_by_col,
667 x_comparison_type => l_comparison_type,
668 x_xtd => l_xtd,
669 x_cur_suffix => l_cur_suffix,
670 x_where_clause => l_where_clause,
671 x_mv => l_mv,
672 x_join_tbl => l_join_tbl,
673 x_mv_type => l_mv_type,
674 x_aggregation_flag => l_aggregation_flag,
675 p_trend => 'N',
676 p_mv_set => 'CMPDTL2',
677 x_custom_output => x_custom_output);
678
679 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
680 l_err_stage:='After calling DRM_DBI_RPT_UTIL_PKG.process_parameters';
681 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_LAT_COMP_DTL : ' ,l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
682 END IF;
683
684 l_query :=
685 'SELECT
686 BIV_ATTRIBUTE1
687 ,BIV_ATTRIBUTE2
688 ,BIV_ATTRIBUTE3
689 ,BIV_ATTRIBUTE4
690 ,BIV_ATTRIBUTE5
691 ,BIV_ATTRIBUTE6
692 ,BIV_MEASURE1
693 ,BIV_ATTRIBUTE7
694 ,BIV_ATTRIBUTE8
695 ,BIV_DATE1
696 ,BIV_DATE2
697 ,BIV_MEASURE2
698 ,BIV_MEASURE3
699 ,BIV_MEASURE4
700 ,''pFunctionName=CSD_RO_DETAILS&csdInvOrgId=''||BIV_MEASURE4||''&csdRepairLineId=''||BIV_MEASURE3 BIV_DYNAMIC_URL1
701 ,' || ISC_DEPOT_RPT_UTIL_PKG.get_service_request_url || ' || BIV_ATTRIBUTE8 BIV_DYNAMIC_URL2
702 FROM (
703 SELECT
704 rank() over (&ORDER_BY_CLAUSE nulls last,BIV_ATTRIBUTE1 ) - 1 rnk
705 ,BIV_ATTRIBUTE1
706 ,BIV_ATTRIBUTE2
707 ,BIV_ATTRIBUTE3
708 ,BIV_ATTRIBUTE4
712 ,BIV_ATTRIBUTE7
709 ,BIV_ATTRIBUTE5
710 ,BIV_ATTRIBUTE6
711 ,BIV_MEASURE1
713 ,BIV_ATTRIBUTE8
714 ,BIV_DATE1
715 ,BIV_DATE2
716 ,BIV_MEASURE2
717 ,BIV_MEASURE3
718 ,BIV_MEASURE4
719 FROM (
720 SELECT repair_number BIV_ATTRIBUTE1 ' || fnd_global.newline ||
721 ',' || ' incident_number BIV_ATTRIBUTE2 ' || fnd_global.newline ||
722 ',' || ' crt.name BIV_ATTRIBUTE3 ' || fnd_global.newline ||
723 ',' || ' eiov.value BIV_ATTRIBUTE4 ' || fnd_global.newline ||
724 ',' || ' eiov.description BIV_ATTRIBUTE5 ' || fnd_global.newline ||
725 ',' || ' mum.unit_of_measure BIV_ATTRIBUTE6 ' || fnd_global.newline ||
726 ',' || ' quantity BIV_MEASURE1 ' || fnd_global.newline ||
727 ',' || ' serial_number BIV_ATTRIBUTE7 ' || fnd_global.newline ||
728 ',' || ' incident_id BIV_ATTRIBUTE8 ' || fnd_global.newline ||
729 ',' || ' promise_date BIV_DATE1 ' || fnd_global.newline ||
730 ',' || ' date_closed BIV_DATE2 ' || fnd_global.newline ||
731 ',' || ' trunc(date_closed) - trunc(promise_date) BIV_MEASURE2 ' || fnd_global.newline ||
732 ',' || ' fact.repair_line_id BIV_MEASURE3 ' || fnd_global.newline ||
733 ',' || ' fact.master_organization_id BIV_MEASURE4 ' || fnd_global.newline
734 || ' from ' || fnd_global.newline
735 || l_mv || fnd_global.newline
736 || ' ISC_DR_REPAIR_ORDERS_F fact, ' || fnd_global.newline
737 || ' ENI_ITEM_V EIOV, ' || fnd_global.newline
738 || ' FND_LOOKUPS FL, ' || fnd_global.newline
739 || ' MTL_UNITS_OF_MEASURE_VL MUM ' || fnd_global.newline
740 || ' WHERE dbi_date_closed between &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_ASOF_DATE ' || fnd_global.newline
741 || ' AND dbi_date_closed > fact.promise_date ' || fnd_global.newline
742 || ' AND FL.LOOKUP_TYPE = ''CSD_REPAIR_STATUS'' ' || fnd_global.newline
743 || ' AND FL.LOOKUP_CODE = fact.status ' || fnd_global.newline
744 || ' AND FACT.item_org_id = eiov.id ' || fnd_global.newline
745 || ' AND mum.uom_code = fact.uom_code ' || fnd_global.newline
746 || l_where_clause || fnd_global.newline
747 || ' ) ) where (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
748 ORDER BY rnk' || fnd_global.newline ;
749 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
750 l_err_stage:='The query is : ' || l_query;
751 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_LAT_COMP_DTL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
752 END IF;
753 x_custom_sql := l_query;
754
755 EXCEPTION
756 WHEN OTHERS THEN
757 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
758 l_err_stage:='The exception is : ' || SQLERRM;
759 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_LAT_COMP_DTL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
760 END IF;
761
762 END GET_LAT_COMP_DTL_TBL_SQL;
763
764 PROCEDURE GET_LAT_COMP_AGNG_TBL_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
765 x_custom_sql OUT NOCOPY VARCHAR2,
766 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
767 IS
768
769 l_query ISC_DEPOT_RPT_UTIL_PKG.g_query_typ%type;
770 l_view_by ISC_DEPOT_RPT_UTIL_PKG.g_view_by_typ%type;
771 l_view_by_col ISC_DEPOT_RPT_UTIL_PKG.g_view_by_col_typ%type;
772 l_xtd ISC_DEPOT_RPT_UTIL_PKG.g_xtd_typ%type;
773 l_comparison_type VARCHAR2(1);
774 l_cur_suffix VARCHAR2(1);
775 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
776 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
777 l_where_clause ISC_DEPOT_RPT_UTIL_PKG.g_where_clause_typ%type;
778 l_mv ISC_DEPOT_RPT_UTIL_PKG.g_mv_typ%type;
779 l_mv_set VARCHAR2(50);
780 l_aggregation_flag NUMBER;
781 l_custom_rec BIS_QUERY_ATTRIBUTES;
782 l_mv_type VARCHAR2(10);
783 l_err_stage VARCHAR2(32767);
784 l_function_name VARCHAR2(30);
785 l_debug_mode VARCHAR2(1);
786 l_module_name ISC_DEPOT_RPT_UTIL_PKG.g_module_name_typ%type ;
787
788 BEGIN
789 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
790 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
791
792 -- clear out the tables.
793 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
794 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
795
796 -- get all the query parameters
797 ISC_DEPOT_RPT_UTIL_PKG.process_parameters (p_param => p_param,
798 x_view_by => l_view_by,
799 x_view_by_col_name => l_view_by_col,
800 x_comparison_type => l_comparison_type,
801 x_xtd => l_xtd,
802 x_cur_suffix => l_cur_suffix,
806 x_mv_type => l_mv_type,
803 x_where_clause => l_where_clause,
804 x_mv => l_mv,
805 x_join_tbl => l_join_tbl,
807 x_aggregation_flag => l_aggregation_flag,
808 p_trend => 'N',
809 p_mv_set => 'CMPAGN1',
810 x_custom_output => x_custom_output);
811
812 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
813 l_err_stage:='After calling DRM_DBI_RPT_UTIL_PKG.process_parameters';
814 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_COMPLETION_TBL : ' ,l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
815 END IF;
816
817 l_query := ' SELECT range_name BIV_ATTRIBUTE1 ' || fnd_global.newline ||
818 ' ,nvl(c_LATE_COMPLETE_COUNT,0) BIV_MEASURE1 ' || fnd_global.newline ||
819 ' ,nvl(p_LATE_COMPLETE_COUNT,0) BIV_MEASURE11 ' || fnd_global.newline ||
820 ' ,' || poa_dbi_util_pkg.change_clause(cur_col => 'c_LATE_COMPLETE_COUNT'
821 ,prior_col => 'p_LATE_COMPLETE_COUNT'
822 ,change_type => 'NP') || 'BIV_MEASURE2' || fnd_global.newline ||
823 ' ,'|| poa_dbi_util_pkg.rate_clause(numerator => 'c_LATE_COMPLETE_COUNT'
824 ,denominator => 'c_LATE_COMPLETE_COUNT_TOTAL'
825 ,rate_type => 'P') || 'BIV_MEASURE3' || fnd_global.newline ||
826 ' ,nvl(c_LATE_COMPLETE_COUNT_total,0) BIV_MEASURE21 ' || fnd_global.newline ||
827 ' ,'|| poa_dbi_util_pkg.change_clause(cur_col => 'c_LATE_COMPLETE_COUNT_total'
828 ,prior_col => 'p_LATE_COMPLETE_COUNT_total'
829 ,change_type => 'NP') || 'BIV_MEASURE22' || fnd_global.newline ||
830 ' ,'|| poa_dbi_util_pkg.rate_clause(numerator => 'c_LATE_COMPLETE_COUNT_TOTAL'
831 ,denominator => 'c_LATE_COMPLETE_COUNT_TOTAL'
832 ,rate_type => 'P') || 'BIV_MEASURE23' || fnd_global.newline ||
833 -- ',' || 'ISC_DEPOT_COMPLETION_PKG.GET_BUCKET_DRILL_ACROSS_URL(''ISC_DEPOT_LAT_COMP_DTL_TBL_REP'', bucket_number)' || ' BIV_DYNAMIC_URL1 ' ||
834 ',' || '''pFunctionName=ISC_DEPOT_LAT_COMP_DTL_TBL_REP&pParamIds=Y&BIV_ATTRIBUTE1=-1&BIV_DR_BACKLOG_BUCKET+BIV_DR_BACKLOG_BUCKET=''|| bucket_number ||''''' || ' BIV_DYNAMIC_URL1 ' ||
835 ' FROM (select sum (decode(cal.report_date, &BIS_CURRENT_ASOF_DATE, ' || fnd_global.newline ||
836 ' decode (buckets.bucket_number, 1, days_late_age_b1 ' || fnd_global.newline ||
837 ' ,2, days_late_age_b2 ' || fnd_global.newline ||
838 ' ,3, days_late_age_b3 ' || fnd_global.newline ||
839 ' ,4, days_late_age_b4 ' || fnd_global.newline ||
840 ' ,5, days_late_age_b5 ' || fnd_global.newline ||
841 ' ,6, days_late_age_b6 ' || fnd_global.newline ||
842 ' ,7, days_late_age_b7 ' || fnd_global.newline ||
843 ' ,8, days_late_age_b8 ' || fnd_global.newline ||
844 ' ,9, days_late_age_b9 ' || fnd_global.newline ||
845 ' ,10, days_late_age_b10 ))) c_LATE_COMPLETE_COUNT ' || fnd_global.newline ||
846 ' ,sum (decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE, ' || fnd_global.newline ||
847 ' decode (buckets.bucket_number, 1, days_late_age_b1 ' || fnd_global.newline ||
848 ' ,2, days_late_age_b2 ' || fnd_global.newline ||
849 ' ,3, days_late_age_b3 ' || fnd_global.newline ||
850 ' ,4, days_late_age_b4 ' || fnd_global.newline ||
851 ' ,5, days_late_age_b5 ' || fnd_global.newline ||
852 ' ,6, days_late_age_b6 ' || fnd_global.newline ||
853 ' ,7, days_late_age_b7 ' || fnd_global.newline ||
854 ' ,8, days_late_age_b8 ' || fnd_global.newline ||
855 ' ,9, days_late_age_b9 ' || fnd_global.newline ||
856 ' ,10, days_late_age_b10 ))) p_LATE_COMPLETE_COUNT ' || fnd_global.newline ||
857 -- ' ,0 c_LATE_COMPLETE_COUNT_total , 0 p_LATE_COMPLETE_COUNT_total ' ||
858 ' ,sum (sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE, ' || fnd_global.newline ||
859 ' decode (buckets.bucket_number, 1, days_late_age_b1 ' || fnd_global.newline ||
860 ' ,2, days_late_age_b2 ' || fnd_global.newline ||
864 ' ,6, days_late_age_b6 ' || fnd_global.newline ||
861 ' ,3, days_late_age_b3 ' || fnd_global.newline ||
862 ' ,4, days_late_age_b4 ' || fnd_global.newline ||
863 ' ,5, days_late_age_b5 ' || fnd_global.newline ||
865 ' ,7, days_late_age_b7 ' || fnd_global.newline ||
866 ' ,8, days_late_age_b8 ' || fnd_global.newline ||
867 ' ,9, days_late_age_b9 ' || fnd_global.newline ||
868 ' ,10, days_late_age_b10 )))) over () c_LATE_COMPLETE_COUNT_total ' || fnd_global.newline ||
869 ' ,sum (sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE, ' || fnd_global.newline ||
870 ' decode (buckets.bucket_number, 1, days_late_age_b1 ' || fnd_global.newline ||
871 ' ,2, days_late_age_b2 ' || fnd_global.newline ||
872 ' ,3, days_late_age_b3 ' || fnd_global.newline ||
873 ' ,4, days_late_age_b4 ' || fnd_global.newline ||
874 ' ,5, days_late_age_b5 ' || fnd_global.newline ||
875 ' ,6, days_late_age_b6 ' || fnd_global.newline ||
876 ' ,7, days_late_age_b7 ' || fnd_global.newline ||
877 ' ,8, days_late_age_b8 ' || fnd_global.newline ||
878 ' ,9, days_late_age_b9 ' || fnd_global.newline ||
879 ' ,10, days_late_age_b10 )))) over () p_LATE_COMPLETE_COUNT_total ' || fnd_global.newline ||
880 ' ,range_name ' || fnd_global.newline ||
881 ' ,buckets.bucket_number ' || fnd_global.newline ||
882 ' from (';
883 FOR i in 1..10 LOOP
884 l_query := l_query ||
885 'SELECT '|| i || ' bucket_number, ' || fnd_global.newline ||
886 ' bbct.range'|| i ||'_name range_name, ' || fnd_global.newline ||
887 ' bbc.range' || i || '_low range_low, ' || fnd_global.newline ||
888 ' bbc.range' || i || '_high range_high ' || fnd_global.newline ||
889 'FROM bis_bucket_customizations bbc, ' || fnd_global.newline ||
890 ' bis_bucket bb, ' || fnd_global.newline ||
891 ' bis_bucket_customizations_tl bbct ' || fnd_global.newline ||
892 'WHERE short_name = ''ISC_DEPOT_BKLG_CMP_AGING'' ' || fnd_global.newline ||
893 ' and bb.bucket_id = bbc.bucket_id ' || fnd_global.newline ||
894 ' and nvl(bbc.range' || i || '_low,bbc.range' || i || '_high) is not null' || fnd_global.newline ||
895 ' and bbct.language =USERENV(''LANG'') ' || fnd_global.newline ||
896 ' and bbC.id = bbct.id '|| fnd_global.newline;
897 IF i <> 10 THEN
898 l_query := l_query || 'UNION ALL ' || fnd_global.newline;
899 ELSE
900 l_query := l_query || ') buckets, ' || fnd_global.newline;
901 END IF;
902 END LOOP;
903
904 IF l_mv_type = 'ROOT' THEN
905 l_mv := l_mv ||
906 ' ISC_DR_BKLG_02_MV fact' || fnd_global.newline;
907 ELSE
908 l_mv := l_mv ||
909 ' ISC_DR_BKLG_01_MV fact' || fnd_global.newline;
910 END IF;
911 l_query := l_query || l_mv ||
912 ',fii_time_rpt_struct_v cal' || fnd_global.newline ||
913 ' where fact.time_id = cal.time_id' || fnd_global.newline ||
914 ' and cal.report_date in (&BIS_PREVIOUS_ASOF_DATE,&BIS_CURRENT_ASOF_DATE)' || fnd_global.newline ||
915 ' and bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id ' || fnd_global.newline ||
916 l_where_clause || fnd_global.newline ||
917 ' group by range_name, buckets.bucket_number order by buckets.bucket_number)';
918
919 -- prepare output for bind variables
920 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
921 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
922
923 -- set the basic bind variables for the status SQL
924 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
925 -- Passing AGGREGATION_LEVEL_FLAG to PMV
926 l_custom_rec.attribute_name := ':AGGREGATION_FLAG';
927 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
928 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
929 l_custom_rec.attribute_value := l_aggregation_flag;
930 x_custom_output.extend;
931 x_custom_output(x_custom_output.count) := l_custom_rec;
932
933
934 x_custom_sql := l_query;
935 EXCEPTION
936
937 WHEN OTHERS THEN
938 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
939 l_err_stage:='The exception is : ' || SQLERRM;
940 ISC_DEPOT_RPT_UTIL_PKG.write('BIS_ISC_DEPOT_BACKLOG_DTL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
941 END IF;
942
943 END GET_LAT_COMP_AGNG_TBL_SQL;
944
945 FUNCTION get_drill_across (p_view_by_dim IN VARCHAR2,p_function_name IN VARCHAR2)
946 RETURN VARCHAR2
947 IS
948 l_drill_across varchar2 (500);
949 BEGIN
950 l_drill_across := 'NULL';
951
952 IF (p_view_by_dim = 'ITEM+ENI_ITEM_VBH_CAT') THEN
953 l_drill_across := 'decode(v.leaf_node_flag, ''Y'',
954 ''pFunctionName='|| p_function_name ||'&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM&pParamIds=Y'',
955 ''pFunctionName='|| p_function_name ||'&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'') ';
956 END IF;
957 RETURN l_drill_across;
958 END get_drill_across ;
959
960
961 FUNCTION GET_BUCKET_DRILL_ACROSS_URL (p_function_name VARCHAR2,
962 p_bucket_number NUMBER)
963 RETURN VARCHAR2
964 IS
965 l_drill_across_rep_1 VARCHAR2(500);
966 BEGIN
967 l_drill_across_rep_1 := 'null' ;
968 l_drill_across_rep_1 := 'pFunctionName=' || p_function_name || '&SERVICE_DISTRIBUTION=';
969 l_drill_across_rep_1 := l_drill_across_rep_1 || p_bucket_number || '&pParamIds=Y';
970
971 RETURN l_drill_across_rep_1;
972 END GET_BUCKET_DRILL_ACROSS_URL ;
973
974 END ISC_DEPOT_COMPLETION_PKG;