[Home] [Help]
PACKAGE BODY: APPS.ISC_DEPOT_RPT_UTIL_PKG
Source
1 PACKAGE BODY ISC_DEPOT_RPT_UTIL_PKG AS
2 -- $Header: iscdepotutilb.pls 120.1 2006/01/26 19:22:21 kreardon noship $
3
4 -- List of mv sets and their corresponding reports.
5 -- 'BKLG' - Repair Order Backlog,Repair Order Backlog Trend,Repair Order Completion,
6 -- Repair Order Completion Trend
7 -- 'BKLDTL1' - Repair Order Backlog detail
8 -- 'BKLDTL2' - Repair Order Past Due Detail
9 -- 'BKLAGN1' - Repair Order Past Due Aging
10 -- 'CMPAGN1' - Repair Order Late Completion Aging
11 -- 'CMPDTL1' - Repair Order Completion Detail
12 -- 'CMPDTL2' - Repair Order Late Completion Detail
13 -- 'COSTS' - Repair Order Cost Summary,Repair Order Cost Summary Trend
14 -- 'CHARGES' - Repair Order Charges Summary, Repair Order Charges Summary Trend
15 -- 'MARGIN' - Repair Order Margin, Repair Order Margin Trend,Repair Order Margin Summary,
16 -- Repair Order Margin Summary Trend
17 -- 'MTTR' - Mean Time To Repair Status & Trend Reports
18 -- 'SRVC' - Repair Order Service Code Summary Report
19 -- 'MRGDTL' -
20 -- 'MDTL' - Mean Time To Repair Detail Report
21
22 -- Local Functions
23 PROCEDURE init_dim_map ( p_mv_set IN VARCHAR2,
24 x_dim_map out NOCOPY poa_dbi_util_pkg.poa_dbi_dim_map);
25
26 PROCEDURE get_join_info (p_view_by IN varchar2,
27 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map,
28 x_join_tbl OUT NOCOPY poa_dbi_util_pkg.POA_DBI_JOIN_TBL,
29 p_mv_set IN VARCHAR2,
30 p_category_flag IN VARCHAR2 );
31
32 PROCEDURE get_mv (p_mv_set IN VARCHAR2,
33 p_pcategory_flag IN VARCHAR2, -- to know if product category is selected or not.
34 p_rtype_flag IN VARCHAR2, -- to know if repair type is selected or not.
35 p_view_by IN VARCHAR2,
36 p_dim_bmap IN NUMBER,
37 x_mv_type OUT NOCOPY VARCHAR2,
38 x_mv OUT NOCOPY VARCHAR2) ;
39 PROCEDURE bind_low_high
40 (
41 p_range_id IN NUMBER
42 , p_short_name IN varchar2
43 , p_dim_level IN varchar2
44 , p_low IN varchar2
45 , p_high IN varchar2
46 , p_custom_output IN OUT nocopy bis_query_attributes_tbl);
47
48 PROCEDURE GET_BUCKET_RANGE_ID (p_parameter_id IN VARCHAR2 ,
49 x_bucket_range_ids_tbl OUT NOCOPY bucket_range_typ);
50
51 PROCEDURE get_additional_whereclause( p_mv_set IN VARCHAR2
52 ,p_param IN BIS_PMV_PAGE_PARAMETER_TBL
53 ,p_pcategory_flag IN VARCHAR2
54 ,p_rtype_flag IN VARCHAR2
55 ,p_repair_type IN VARCHAR2
56 ,p_bucket_flag IN VARCHAR2
57 ,x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
58 ,x_where_clause OUT NOCOPY VARCHAR2);
59
60 -- process_parameters
61 -- Generic routine to process the parameters passed in from the PMV
62 -- page.
63 -- Points of note:
64 -- Date Author Action
65 -- 02-Aug-2004 Vijay Babu Gandhi created.
66 PROCEDURE process_parameters (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
67 x_view_by OUT NOCOPY VARCHAR2,
68 x_view_by_col_name OUT NOCOPY VARCHAR2,
69 x_comparison_type OUT NOCOPY VARCHAR2,
70 x_xtd OUT NOCOPY VARCHAR2,
71 x_cur_suffix OUT NOCOPY VARCHAR2,
72 x_where_clause OUT NOCOPY VARCHAR2,
73 x_mv OUT NOCOPY VARCHAR2,
74 x_join_tbl OUT NOCOPY poa_dbi_util_pkg.poa_dbi_join_tbl,
75 x_mv_type OUT NOCOPY VARCHAR2,
76 x_aggregation_flag OUT NOCOPY NUMBER,
77 p_mv_set IN VARCHAR2,
78 p_trend IN VARCHAR2,
79 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
80
81 IS
82 l_dim_map poa_dbi_util_pkg.poa_dbi_dim_map;
83 l_dim_bmap NUMBER := 0;
84 l_as_of_date DATE;
85 l_prev_as_of_date DATE;
86 l_not_used NUMBER;
87 l_pcategory_flag VARCHAR2(1);
88 l_rtype_flag VARCHAR2(1);
89 l_bucket_selected_flag VARCHAR2(1);
90 l_additional_where_clause VARCHAR2(10000);
91 l_err_stage VARCHAR2(32767);
92 l_repair_type VARCHAR2(100);
93 l_debug_mode VARCHAR2(1);
94 l_module_name ISC_DEPOT_RPT_UTIL_PKG.g_module_name_typ%type;
95
96 BEGIN
97 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
98 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
99 l_pcategory_flag := 'N';
100 l_rtype_flag := 'N';
101 l_bucket_selected_flag := 'N';
102
103 -- initialize the dimension map with all the required dimensions.
104 init_dim_map ( p_mv_set => p_mv_set,
105 x_dim_map => l_dim_map);
106
107 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
108 l_err_stage:='Before Calling Get Parameter Values' ;
109 write('BIS_ISC_DEPOT_UTIL : ',l_err_stage,C_DEBUG_LEVEL);
110 END IF;
111
112 -- To find out if Product Category is selected or not.
113 FOR i in 1..p_param.COUNT LOOP
114 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT' AND
115 p_param(i).parameter_id is NOT null) THEN
116 l_pcategory_flag := 'Y';
117 l_dim_map('ITEM+ENI_ITEM_VBH_CAT').col_name:= 'imm_child_id';
118 END IF;
119
120 -- -1 and -2 represent top node repair type
121 IF( p_param(i).parameter_name = 'BIV_REPAIR_TYPE+BIV_REPAIR_TYPE' AND
122 p_param(i).parameter_id is not null) THEN
123 l_repair_type := replace(p_param(i).parameter_id,'''',null);
124
125 IF (instr(l_repair_type,'-1') <> 0 AND instr(l_repair_type,'-2') <> 0 ) THEN
126 l_rtype_flag := 'N'; -- This value is used till get_mv
127 ELSIF (instr(l_repair_type,'-1') <> 0 OR instr(l_repair_type,'-2') <> 0 ) THEN
128 l_rtype_flag := 'Y'; -- This value is used till get_mv
129 ELSE
130 l_dim_map('BIV_REPAIR_TYPE+BIV_REPAIR_TYPE').generate_where_clause := 'Y';
131 END IF;
132 END IF;
133
134 -- To find out if the bucket parameter is selected or not.
135 IF(p_param(i).parameter_name = 'BIV_DR_BACKLOG_BUCKET+BIV_DR_BACKLOG_BUCKET' and p_param(i).parameter_id is not null) THEN
136 l_bucket_selected_flag := 'Y';
137 END IF;
138 END LOOP;
139
140 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
141 l_err_stage:='Before Calling Get Parameter Values 1' ;
142 write('BIS_ISC_DEPOT_UTIL : ',l_err_stage,C_DEBUG_LEVEL);
143 END IF;
144 -- Get the various parameter values from the utility package.
145 -- This package will also compute the l_dim_bmap
146 poa_dbi_util_pkg.get_parameter_values (p_param => p_param, --IN
147 p_dim_map => l_dim_map, --IN
148 p_view_by => x_view_by, --OUT
149 p_comparison_type => x_comparison_type, --OUT
150 p_xtd => x_xtd, --OUT
151 p_as_of_date => l_as_of_date, --OUT
152 p_prev_as_of_date => l_prev_as_of_date, --OUT
153 p_cur_suffix => x_cur_suffix, --OUT
154 p_nested_pattern => l_not_used, --OUT
155 p_dim_bmap => l_dim_bmap); --OUT
156
157 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
158 l_err_stage:='Before Calling Get Parameter Values 3' ;
159 write('BIS_ISC_DEPOT_UTIL : ',l_err_stage,C_DEBUG_LEVEL);
160 END IF;
161 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
162 l_err_stage:='After Calling Get Parameter Values' || ' l_dim_bmap -> ' || l_dim_bmap
163 || '; x_view_by -> ' || x_view_by ;
164 write('BIS_ISC_DEPOT_UTIL : ',l_err_stage,C_DEBUG_LEVEL);
165 END IF;
166
167 -- Find out the view by column name
168
169 IF ( l_dim_map.exists(x_view_by)) THEN
170 x_view_by_col_name := l_dim_map(x_view_by).col_name;
171 END IF;
172
173 -- get the join info for the view by dimension.
174 IF ( x_view_by_col_name IS NOT NULL) THEN
175 get_join_info (p_view_by => x_view_by,
176 p_dim_map => l_dim_map,
177 x_join_tbl => x_join_tbl,
178 p_mv_set => p_mv_set,
179 p_category_flag => l_pcategory_flag );
180 END IF;
181
182 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
183 l_err_stage := 'After Calling Get join info';
184 write('BIS_ISC_DEPOT_UTIL : ',l_err_stage,C_DEBUG_LEVEL);
185 END IF;
186
187
188 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
189 l_err_stage := 'After looping through p_params';
190 write('BIS_ISC_DEPOT_UTIL : ',l_err_stage,C_DEBUG_LEVEL);
191 END IF;
192
193
194 -- Get the MV we need to join to.
195 get_mv (p_mv_set => p_mv_set,
196 p_pcategory_flag => l_pcategory_flag,
197 p_rtype_flag => l_rtype_flag,
198 p_view_by => x_view_by,
199 p_dim_bmap => l_dim_bmap,
200 x_mv_type => x_mv_type,
201 x_mv => x_mv);
202
203 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
204 l_err_stage := substr(x_mv,0,2900);
205 l_err_stage:= 'MV -> ' || l_err_stage;
206 write('BIS_ISC_DEPOT_UTIL : ',l_err_stage,C_DEBUG_LEVEL);
207 l_err_stage := 'x_mv_type = ' || x_mv_type;
208 write('BIS_ISC_DEPOT_UTIL : ',l_err_stage,C_DEBUG_LEVEL);
209 END IF;
210
211 -- Get the aggregation flag
212 IF(p_mv_set IN ('BKLG','COSTS','CHARGES','MARGIN','MTTR','SRVC','CMPAGN1','BKLDUP1','BKLDUP2','BKLAGN1')) THEN
213 x_aggregation_flag := get_agg_flag(p_mv_set,l_dim_bmap, x_mv_type);
214 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
215 l_err_stage := 'x_aggregation_flag = '|| x_aggregation_flag;
216 write('BIS_ISC_DEPOT_UTIL : ',l_err_stage,C_DEBUG_LEVEL);
217 END IF;
218 END IF;
219
220 -- Get the dimension level specific where clauses
221 x_where_clause := poa_dbi_util_pkg.get_where_clauses (p_dim_map => l_dim_map,
222 p_trend => p_trend);
223
224 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
225 l_err_stage:= 'x_where_clause before ''additional where clause'' -> ' || x_where_clause;
226 write('BIS_ISC_DEPOT_UTIL : ',l_err_stage,C_DEBUG_LEVEL);
227 END IF;
228
229 -- Attach the where clause for bucket parameter
230 get_additional_whereclause( p_mv_set => p_mv_set
231 ,p_param => p_param
232 ,p_pcategory_flag => l_pcategory_flag
233 ,p_rtype_flag => l_rtype_flag
234 ,p_repair_type => l_repair_type
235 ,p_bucket_flag => l_bucket_selected_flag
236 ,x_custom_output => x_custom_output
237 ,x_where_clause => l_additional_where_clause);
238
239 x_where_clause := x_where_clause || l_additional_where_clause;
240
241 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
242 l_err_stage:= 'x_where_clause -> ' || x_where_clause;
243 write('BIS_ISC_DEPOT_UTIL : ',l_err_stage,C_DEBUG_LEVEL);
244 END IF;
245 EXCEPTION
246 WHEN OTHERS THEN
247
248 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
249 l_err_stage:= l_err_stage || ' --->' || SQLERRM;
250 write('BIS_ISC_DEPOT_UTIL : ',l_err_stage,C_DEBUG_LEVEL);
251 END IF;
252 RAISE;
253 END process_parameters;
254
255 -- Setting up list of dimensions to track
256 -- init_dim_map
257 -- Initialize the dimension map with all needed dimensions.
258
259 -- This function needs to keep track of all possible dimensions
260 -- the DBI 7.1 reports are interested in. The POA utility package
261 -- get_parameter_values functions looks at the parameter table
262 -- passed in by PMV. For parameters names for which it finds a
263 -- matching key in this dimension map table, it records the value.
264 -- In other words, if the dimension map does not have an entry for
265 -- BIV_REPAIR_ORGANIZATION+BIV_REPAIR_ORGANIZATION, then PMV's organization parameter
266 -- will never be recorded.
267
268
269 PROCEDURE init_dim_map ( p_mv_set IN VARCHAR2,
270 x_dim_map out NOCOPY poa_dbi_util_pkg.poa_dbi_dim_map)
271 IS
272
273 l_dim_rec poa_dbi_util_pkg.poa_dbi_dim_rec;
274
275 BEGIN
276
277 -- Organzation dimension level
278 l_dim_rec.col_name := 'repair_organization_id';
279 l_dim_rec.view_by_table := 'BIV_DBI_RO_ORG_V';
280 l_dim_rec.bmap := C_ORG_BMAP;
281 l_dim_rec.generate_where_clause := 'Y';
282 x_dim_map('BIV_REPAIR_ORGANIZATION+BIV_REPAIR_ORGANIZATION') := l_dim_rec;
283
284 -- Category dimension level
285 l_dim_rec.col_name := 'product_category_id';
286 l_dim_rec.view_by_table := 'eni_item_vbh_nodes_v';
287 l_dim_rec.bmap := C_CATEGORY_BMAP;
288 l_dim_rec.generate_where_clause := 'N';
289 x_dim_map('ITEM+ENI_ITEM_VBH_CAT') := l_dim_rec;
290
291 -- Item dimension level
292 -- For detail reports facts are accessed. There item-org-id column is not there.
293 l_dim_rec.col_name := 'item_org_id';
294 l_dim_rec.view_by_table := 'eni_item_v';
295 l_dim_rec.bmap := C_ITEM_BMAP;
296 l_dim_rec.generate_where_clause := 'Y';
297 x_dim_map('ITEM+ENI_ITEM') := l_dim_rec;
298
299 -- Customer dimension level
300 l_dim_rec.col_name := 'customer_id';
301 l_dim_rec.view_by_table := 'aso_bi_prospect_v';
302 l_dim_rec.bmap := C_CUSTOMER_BMAP;
303 l_dim_rec.generate_where_clause := 'Y';
304 x_dim_map('CUSTOMER+PROSPECT') := l_dim_rec;
305
306 -- Repair Type dimension level
307 l_dim_rec.col_name := 'repair_type_id';
308 l_dim_rec.view_by_table := 'biv_dbi_repair_types_v';
309 l_dim_rec.bmap := C_REPAIR_TYPE_BMAP;
310 l_dim_rec.generate_where_clause := 'N';
311 x_dim_map('BIV_REPAIR_TYPE+BIV_REPAIR_TYPE') := l_dim_rec;
312
313 -- Service Code dimension level
314 l_dim_rec.col_name := 'service_code_id';
315 l_dim_rec.view_by_table := 'BIV_DBI_SERVICE_CODES_V';
316 l_dim_rec.generate_where_clause := 'Y';
317 l_dim_rec.bmap := 0;
318 x_dim_map('BIV_SERVICE_CODE+BIV_SERVICE_CODE') := l_dim_rec;
319
320 END init_dim_map;
321
322 /* Function: get_join_info
323 */
324 PROCEDURE get_join_info (p_view_by IN varchar2,
325 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map,
326 x_join_tbl OUT NOCOPY poa_dbi_util_pkg.POA_DBI_JOIN_TBL,
327 p_mv_set IN VARCHAR2,
328 p_category_flag IN VARCHAR2)
329 IS
330 l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
331 l_stmt_id NUMBER;
332 BEGIN
333
334 -- reinitialize the join table
335 x_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
336
337 -- The view by table
338 l_join_rec.table_name := p_dim_map(p_view_by).view_by_table;
339 l_join_rec.table_alias := 'v';
340 l_join_rec.fact_column := p_dim_map(p_view_by).col_name;
341 l_join_rec.column_name := 'id';
342
343
344 IF (p_view_by = 'ITEM+ENI_ITEM_VBH_CAT') THEN
345 l_join_rec.additional_where_clause := ' v.parent_id = v.child_id ';
346 IF(p_category_flag = 'Y') THEN
347 l_join_rec.fact_column := 'imm_child_id ';
348 l_join_rec.inner_alias := 'v';
349 END IF;
350 END IF;
351
352
353 IF (p_view_by = 'BIV_REPAIR_ORGANIZATION+BIV_REPAIR_ORGANIZATION') THEN
354 l_join_rec.dim_outer_join := 'Y';
355 ELSE
356 l_join_rec.dim_outer_join := 'N';
357 END IF;
358
359 -- Add the join table
360 x_join_tbl.extend;
361 x_join_tbl(x_join_tbl.count) := l_join_rec;
362
363 END get_join_info;
364
365 -- Functions to get the MV
366 -- Gets the MV for the rack concerned.
367
368 PROCEDURE get_mv (p_mv_set IN VARCHAR2,
369 p_pcategory_flag IN VARCHAR2,
370 p_rtype_flag IN VARCHAR2,
371 p_view_by IN VARCHAR2,
372 p_dim_bmap IN NUMBER,
373 x_mv_type OUT NOCOPY VARCHAR2,
374 x_mv OUT NOCOPY VARCHAR2)
375 IS
376 l_where_clause VARCHAR2 (150);
377 l_err_stage VARCHAR2(3001);
378 l_debug_mode VARCHAR2(1);
379 l_module_name ISC_DEPOT_RPT_UTIL_PKG.g_module_name_typ%type;
380
381 BEGIN
382
383 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
384 l_err_stage := 'inside get_mv p_mv_set = ' || p_mv_set ;
385 write('BIS_ISC_DEPOT_UTIL : ',l_err_stage,C_DEBUG_LEVEL);
386 END IF;
387
388 -- In case of Detail Report, CSD_REPAIR_TYPES_VL is always selected.
389 IF (p_rtype_flag = 'Y' or p_mv_set in('MRGN_DTL','MDTL','BKLDTL1','BKLDTL2',
390 'CMPDTL1','CMPDTL2' )) THEN
391 x_mv := 'CSD_REPAIR_TYPES_VL CRT ,' || fnd_global.newline;
392 END IF;
393
394 IF ( p_pcategory_flag = 'Y' ) THEN -- to check if product category is selected
395 x_mv_type := 'INLINE';
396 x_mv := x_mv || 'ENI_DENORM_HIERARCHIES V, ' || fnd_global.newline ||
397 'MTL_DEFAULT_CATEGORY_SETS M , ' || fnd_global.newline;
398
399 -- The following table and where condition would be for the Margin Detail report.
400 -- Since the Detail reports refer to the fact tables and product category is not available in the
401 -- fact, we join with the ENI_OLTP_ITEM_STAR.
402 IF (p_mv_set = 'MRGN_DTL' or p_mv_set = 'MDTL' OR p_mv_set = 'CMPDTL2' or p_mv_set = 'CMPDTL1') THEN
403 x_mv := x_mv || 'ENI_OLTP_ITEM_STAR ITEMS, ';
404 END IF;
405 END IF;
406
407 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
408 l_err_stage := 'after rtype_flag and pcat_flag x_mv = ' || x_mv;
409 write('BIS_ISC_DEPOT_UTIL : ',l_err_stage,C_DEBUG_LEVEL);
410 END IF;
411
412
413 IF (p_mv_set = 'BKLG') THEN
414 IF (bitand(p_dim_bmap,16) = 16 OR p_pcategory_flag = 'Y' ) THEN -- for checking if item is selected or it is in view by
415 x_mv := x_mv || ' ISC_DR_BKLG_01_MV';
416 x_mv_type := 'BASE';
417 ELSE
418 x_mv := x_mv || ' ISC_DR_BKLG_02_MV';
419 x_mv_type := 'ROOT';
420 END IF;
421
422 ELSIF (p_mv_set = 'BKLDUP1') THEN
423 x_mv_type := 'BASE';
424
425 ELSIF (p_mv_set = 'BKLDUP2') THEN
426 IF (bitand(p_dim_bmap,16) = 16 OR p_pcategory_flag = 'Y' ) THEN -- for checking if item is selected or it is in view by
427 x_mv_type := 'BASE';
428 ELSE
429 x_mv_type := 'ROOT';
430 END IF;
431
432 ELSIF (p_mv_set = 'COSTS') THEN
433 IF (bitand(p_dim_bmap,16) = 16 OR p_pcategory_flag = 'Y' ) THEN -- for checking if item is selected or it is in view by
434 x_mv := x_mv || ' ISC_DR_COSTS_01_MV';
435 x_mv_type := 'BASE';
436 ELSE
437 x_mv := x_mv || 'ISC_DR_COSTS_02_MV';
438 x_mv_type := 'ROOT';
439 END IF;
440 ELSIF (p_mv_set = 'CHARGES') THEN
441 IF (bitand(p_dim_bmap,16) = 16 OR p_pcategory_flag = 'Y' ) THEN -- for checking if item is selected or it is in view by
442 x_mv := x_mv || ' ISC_DR_CHARGES_01_MV';
443 x_mv_type := 'BASE';
444 ELSE
445 x_mv := x_mv || 'ISC_DR_CHARGES_02_MV';
446 x_mv_type := 'ROOT';
447 END IF;
448 ELSIF (p_mv_set = 'MARGIN') THEN
449 IF (bitand(p_dim_bmap,16) = 16 OR p_pcategory_flag = 'Y' ) THEN -- for checking if item is selected or it is in view by
450 x_mv := x_mv || ' ISC_DR_MRGN_01_V';
451 x_mv_type := 'BASE';
452 ELSE
453 x_mv := x_mv || 'ISC_DR_MRGN_02_V';
454 x_mv_type := 'ROOT';
455 END IF;
456
457 -- Mean Time To Repair Status/ Trend Reports
458 ELSIF (p_mv_set = 'MTTR') THEN
459 IF (bitand(p_dim_bmap,16) = 16 OR p_pcategory_flag = 'Y' ) THEN -- for checking if item is selected or it is in view by
460 x_mv := x_mv || ' ISC_DR_MTTR_01_MV';
461 x_mv_type := 'BASE';
462 ELSE
463 x_mv := x_mv || 'ISC_DR_MTTR_02_MV';
464 x_mv_type := 'ROOT';
465 END IF;
466
467 ELSIF (p_mv_set = 'SRVC') THEN
468 x_mv := x_mv || 'ISC_DR_SERVICE_CODE_MV';
469 END IF;
470
471 END get_mv;
472
473 -- get_agg_flag
474 -- Generic routine to get the appropriate aggregation_flag for the selected parameters
475 -- Points of note:
476 -- Function performs a bitand of p_dim_bmap with each vaue in p_mv_lvl_tbl
477 -- If the result is same as the value return it
478 -- If no record satisfies the check, return the most granular level
479 -- Date Author Action
480 -- 02-Aug-2004 Vijay Babu Gandhi created.
481
482 FUNCTION get_agg_flag (p_mv_set IN VARCHAR2,p_dim_bmap IN NUMBER
483 ,p_mv_type IN VARCHAR2)
484 RETURN NUMBER
485 IS
486 l_mv_agg_tbl ISC_DEPOT_RPT_UTIL_PKG.mv_agg_tbl_typ;
487 BEGIN
488
489 IF (p_mv_type = 'ROOT') THEN
490 l_mv_agg_tbl(1) := 4;
491 l_mv_agg_tbl(2) := 5;
492 l_mv_agg_tbl(3) := 6;
493 l_mv_agg_tbl(4) := 7;
494 l_mv_agg_tbl(5) := 14;
495 l_mv_agg_tbl(5) := 15;
496 ELSE
497 l_mv_agg_tbl(1) := 3;
498 l_mv_agg_tbl(2) := 7;
499 l_mv_agg_tbl(3) := 11;
500 l_mv_agg_tbl(4) := 15;
501 l_mv_agg_tbl(5) := 23;
502 l_mv_agg_tbl(6) := 31;
503 END IF;
504
505 IF nvl(l_mv_agg_tbl.count, -1) > 0 THEN
506 FOR cntr IN l_mv_agg_tbl.FIRST .. l_mv_agg_tbl.LAST LOOP
507 IF bitand(l_mv_agg_tbl(cntr), p_dim_bmap) = p_dim_bmap
508 THEN
509 RETURN l_mv_agg_tbl(cntr);
510 END IF;
511 END LOOP;
512 END IF;
513 END get_agg_flag ;
514
515 PROCEDURE GET_ADDITIONAL_WHERECLAUSE( p_mv_set IN VARCHAR2
516 ,p_param IN BIS_PMV_PAGE_PARAMETER_TBL
517 ,p_pcategory_flag IN VARCHAR2
518 ,p_rtype_flag IN VARCHAR2
519 ,p_repair_type IN VARCHAR2
520 ,p_bucket_flag IN VARCHAR2
521 ,x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
522 ,x_where_clause OUT NOCOPY VARCHAR2)
523 IS
524 l_parameter_id VARCHAR2(20);
525 l_bucket_short_name BIS_BUCKET.SHORT_NAME%TYPE;
526
527 BEGIN
528 IF (p_mv_set IN('BKLG','COSTS','CHARGES','MARGIN','MTTR','SRVC','CMPAGN1','BKLAGN1')) THEN
529 x_where_clause := x_where_clause || ' and fact.aggregation_flag = :AGGREGATION_FLAG' ;
530 END IF;
531
532 IF p_pcategory_flag = 'Y' THEN
533 -- For Margin Detail Report eni_oltp_item_star.vbh_category_id is used.
534 IF (p_mv_set IN ('MRGN_DTL', 'MDTL', 'CMPDTL2', 'CMPDTL1')) THEN
535 x_where_clause := x_where_clause || ' and m.functional_area_id = 11 ' || fnd_global.newline
536 || ' and v.object_id = m.category_set_id ' || fnd_global.newline
537 || ' and v.dbi_flag = ''Y'' ' || fnd_global.newline
538 || ' and v.object_type = ''CATEGORY_SET'' ' || fnd_global.newline
539 || ' and items.vbh_category_id = v.child_id ' || fnd_global.newline
540 || ' and v.parent_id = &ITEM+ENI_ITEM_VBH_CAT ' || fnd_global.newline
541 || ' and fact.item_org_id = items.id ' ;
542 ELSE
543 x_where_clause := x_where_clause || ' and m.functional_area_id = 11 ' || fnd_global.newline
544 || ' and v.object_id = m.category_set_id ' || fnd_global.newline
545 || ' and v.dbi_flag = ''Y'' ' || fnd_global.newline
546 || ' and v.object_type = ''CATEGORY_SET'' ' || fnd_global.newline
547 || ' and fact.product_category_id = v.child_id ' || fnd_global.newline
548 || ' and v.parent_id = &ITEM+ENI_ITEM_VBH_CAT ';
549 END IF;
550 END IF;
551
552 -- This condition needs to be there isf it is a detail report or if repair type is selected.
553 IF (p_rtype_flag = 'Y' OR p_mv_set in('MDTL','MRGN_DTL','CMPDTL2','CMPDTL1','BKLDTL1','BKLDTL2')) THEN
554 x_where_clause := x_where_clause || ' and crt.repair_type_id = fact.repair_type_id' ;
555 END IF;
556
557 IF (p_rtype_flag = 'Y' ) THEN
558 IF (instr(p_repair_type,',') <> 0) THEN
559 x_where_clause := x_where_clause || ' and ( fact.repair_type_id IN (&BIV_REPAIR_TYPE+BIV_REPAIR_TYPE) ' ;
560 IF (instr(p_repair_type,'-1') <> 0 ) THEN
561 x_where_clause := x_where_clause || ' or crt.repair_type_ref <> ''RF'' ' ;
562 END IF;
563 IF (instr(p_repair_type,'-2') <> 0 ) THEN
564 x_where_clause := x_where_clause || ' or crt.repair_type_ref = ''RF'' ' ;
565 END IF;
566 x_where_clause := x_where_clause || ' )' ;
567 ELSIF(instr(p_repair_type,'-1') <> 0) THEN
568 x_where_clause := x_where_clause || ' AND crt.repair_type_ref <> ''RF'' ' ;
569 ELSIF (instr(p_repair_type,'-2') <> 0) THEN
570 x_where_clause := x_where_clause || ' AND crt.repair_type_ref = ''RF'' ' ;
571 END IF;
572 END IF;
573
574 IF (p_bucket_flag = 'Y') THEN
575 IF(p_mv_set = 'BKLDTL1') THEN
576 FOR i in 1..p_param.COUNT LOOP
577 IF(p_param(i).parameter_name = 'BIV_DR_BACKLOG_BUCKET+BIV_DR_BACKLOG_BUCKET' ) THEN
578 l_parameter_id := replace(p_param(i).parameter_id,'''',null);
579 END IF;
580 END LOOP;
581
582 IF(l_parameter_id = '-1' ) THEN -- past due is selected
583 x_where_clause := x_where_clause || ' and fact.past_due_flag = ''Y'' ';
584 ELSIF(l_parameter_id = '9999' ) THEN -- Not promised is selected
585 x_where_clause := x_where_clause || ' and fact.promise_date is null ';
586 ELSE
587 x_where_clause := x_where_clause || ' AND ( ';
588 GET_BUCKET_WHERE_CLAUSE (p_param => p_param,
589 p_dim_level => 'BIV_DR_BACKLOG_BUCKET+BIV_DR_BACKLOG_BUCKET',
590 p_bucket_short_name => 'ISC_DEPOT_DAYS_UNTIL_PROM',
591 p_col_name => 'fact.days_until_promised',
592 x_where_clause => x_where_clause ,
593 x_custom_output => x_custom_output);
594 END IF;
595 ELSIF(p_mv_set = 'BKLDTL2') THEN
596 x_where_clause := x_where_clause || ' AND ( ';
597 GET_BUCKET_WHERE_CLAUSE (p_param => p_param,
598 p_dim_level => 'BIV_DR_BACKLOG_BUCKET+BIV_DR_BACKLOG_BUCKET',
599 p_bucket_short_name => 'ISC_DEPOT_BKLG_CMP_AGING',
600 p_col_name => 'fact.past_due_days',
601 x_where_clause => x_where_clause ,
602 x_custom_output => x_custom_output);
603 ELSIF(p_mv_set = 'CMPDTL2') THEN
604 x_where_clause := x_where_clause || ' AND ( ';
605 GET_BUCKET_WHERE_CLAUSE (p_param => p_param,
606 p_dim_level => 'BIV_DR_BACKLOG_BUCKET+BIV_DR_BACKLOG_BUCKET',
607 p_bucket_short_name => 'ISC_DEPOT_BKLG_CMP_AGING',
608 p_col_name => '(dbi_date_closed - fact.promise_date)',
609 x_where_clause => x_where_clause ,
610 x_custom_output => x_custom_output);
611 ELSIF (p_mv_set = 'MDTL') THEN
612 x_where_clause := x_where_clause || ' AND ( ';
613 GET_BUCKET_WHERE_CLAUSE (p_param => p_param,
614 p_dim_level => 'BIV_DR_BACKLOG_BUCKET+BIV_DR_BACKLOG_BUCKET',
615 p_bucket_short_name => 'ISC_DEPOT_MTTR' ,
616 p_col_name => 'fact1.time_to_repair',
617 x_where_clause => x_where_clause ,
618 x_custom_output => x_custom_output);
619 END IF;
620
621 END IF;
622
623 END GET_ADDITIONAL_WHERECLAUSE;
624
625 PROCEDURE GET_BUCKET_WHERE_CLAUSE (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
626 p_dim_level IN VARCHAR2,
627 p_bucket_short_name IN BIS_BUCKET.SHORT_NAME%TYPE,
628 p_col_name IN VARCHAR2,
629 x_where_clause IN OUT NOCOPY VARCHAR2,
630 x_custom_output OUT NOCOPY bis_query_attributes_tbl)
631 IS
632
633 l_parameter_id VARCHAR2(20);
634 l_length NUMBER;
635 l_position NUMBER;
636 l_range_id NUMBER;
637 p_bucket_range_ids_tbl bucket_range_typ;
638
639 BEGIN
640
641 FOR i in 1..p_param.COUNT LOOP
642 IF(p_param(i).parameter_name = p_dim_level ) THEN
643 l_parameter_id := replace(p_param(i).parameter_id,'''',null);
644 END IF;
645 END LOOP;
646
647 IF (l_parameter_id IS NOT NULL) THEN
648
649 GET_BUCKET_RANGE_ID (p_parameter_id => l_parameter_id,
650 x_bucket_range_ids_tbl => p_bucket_range_ids_tbl);
651
652 FOR i IN p_bucket_range_ids_tbl.FIRST .. p_bucket_range_ids_tbl.LAST LOOP
653
654 x_where_clause := x_where_clause ||'((&RANGE'||p_bucket_range_ids_tbl(i)||'_LOW is null OR ' || p_col_name || ' >= &RANGE'||p_bucket_range_ids_tbl(i)||'_LOW)' || fnd_global.newline ||
655 ' AND (&RANGE'||p_bucket_range_ids_tbl(i)||'_HIGH is null OR '|| p_col_name || ' < &RANGE'||p_bucket_range_ids_tbl(i)||'_HIGH))';
656 bind_low_high( p_range_id => p_bucket_range_ids_tbl(i)
657 ,p_short_name => p_bucket_short_name
658 ,p_dim_level => 'BIV_DR_BACKLOG_BUCKET+BIV_DR_BACKLOG_BUCKET'
659 ,p_low => '&RANGE'||p_bucket_range_ids_tbl(i)||'_LOW'
660 ,p_high => '&RANGE'||p_bucket_range_ids_tbl(i)||'_HIGH'
661 ,p_custom_output => x_custom_output);
662 IF (i <> p_bucket_range_ids_tbl.LAST) THEN
663 x_where_clause := x_where_clause || ' OR ';
664 END IF;
665
666 END LOOP;
667
668 x_where_clause := x_where_clause || ' ) ';
669
670 END IF;
671
672 END GET_BUCKET_WHERE_CLAUSE;
673
674 PROCEDURE GET_BUCKET_RANGE_ID (p_parameter_id IN VARCHAR2 ,
675 x_bucket_range_ids_tbl OUT NOCOPY bucket_range_typ)
676 IS
677 l_string VARCHAR2(20);
678 l_string1 VARCHAR2(20);
679 l_length NUMBER;
680 l_position NUMBER;
681 x_count NUMBER;
682
683 BEGIN
684
685 l_string := p_parameter_id;
686 x_count := 1;
687
688 WHILE ( l_string IS NOT NULL ) LOOP
689 l_length := LENGTH(l_string);
690 l_position := INSTR(l_string,',');
691 IF (l_position = 0) THEN
692 x_bucket_range_ids_tbl(x_count) := l_string;
693 l_string := NULL;
694 ELSE
695 l_string1 := SUBSTR(l_string, 1, l_position - 1);
696 x_bucket_range_ids_tbl(x_count) := l_string1;
697 l_string := SUBSTR(l_string,l_position+1,l_length);
698 END IF;
699 x_count := x_count + 1;
700 END LOOP;
701
702 END GET_BUCKET_RANGE_ID;
703
704 PROCEDURE bind_low_high
705 (
706 p_range_id IN NUMBER
707 , p_short_name IN varchar2
708 , p_dim_level IN varchar2
709 , p_low IN varchar2
710 , p_high IN varchar2
711 , p_custom_output IN OUT nocopy bis_query_attributes_tbl
712 )
713 IS
714 l_range_low number;
715 l_range_high number;
716
717 l_bucket_rec bis_bucket_pub.bis_bucket_rec_type;
718 l_return_status varchar2(3);
719 l_error_tbl bis_utilities_pub.error_tbl_type;
720
721 l_custom_rec BIS_QUERY_ATTRIBUTES;
722
723 BEGIN
724
725 bis_bucket_pub.retrieve_bis_bucket
726 ( p_short_name => p_short_name
727 , x_bis_bucket_rec => l_bucket_rec
728 , x_return_status => l_return_status
729 , x_error_tbl => l_error_tbl
730 );
731
732 if l_return_status = 'S' then
733
734 if p_range_id = 1 then
735 l_range_low := l_bucket_rec.range1_low;
736 l_range_high := l_bucket_rec.range1_high;
737 elsif p_range_id = 2 then
738 l_range_low := l_bucket_rec.range2_low;
739 l_range_high := l_bucket_rec.range2_high;
740 elsif p_range_id = 3 then
741 l_range_low := l_bucket_rec.range3_low;
742 l_range_high := l_bucket_rec.range3_high;
743 elsif p_range_id = 4 then
744 l_range_low := l_bucket_rec.range4_low;
745 l_range_high := l_bucket_rec.range4_high;
746 elsif p_range_id = 5 then
747 l_range_low := l_bucket_rec.range5_low;
748 l_range_high := l_bucket_rec.range5_high;
749 elsif p_range_id = 6 then
750 l_range_low := l_bucket_rec.range6_low;
751 l_range_high := l_bucket_rec.range6_high;
752 elsif p_range_id = 7 then
753 l_range_low := l_bucket_rec.range7_low;
754 l_range_high := l_bucket_rec.range7_high;
755 elsif p_range_id = 8 then
756 l_range_low := l_bucket_rec.range8_low;
757 l_range_high := l_bucket_rec.range8_high;
758 elsif p_range_id = 9 then
759 l_range_low := l_bucket_rec.range9_low;
760 l_range_high := l_bucket_rec.range9_high;
761 elsif p_range_id = 10 then
762 l_range_low := l_bucket_rec.range10_low;
763 l_range_high := l_bucket_rec.range10_high;
764 end if;
765 end if;
766
767 if p_custom_output is null then
768 p_custom_output := bis_query_attributes_tbl();
769 end if;
770
771 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
772
773 l_custom_rec.attribute_name := p_low;
774 l_custom_rec.attribute_value := l_range_low;
775 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
776 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
777 p_custom_output.extend;
778 p_custom_output(p_custom_output.count) := l_custom_rec;
779
780 l_custom_rec.attribute_name := p_high;
781 l_custom_rec.attribute_value := l_range_high;
782 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
783 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
784 p_custom_output.extend;
785 p_custom_output(p_custom_output.count) := l_custom_rec;
786
787 END bind_low_high;
788
789 FUNCTION GET_VIEWBY_SELECT_CLAUSE (p_viewby IN VARCHAR2)
790 RETURN VARCHAR2
791 IS
792 l_viewby_sel VARCHAR2(200);
793 BEGIN
794 if p_viewby = 'BIV_REPAIR_TYPE+BIV_REPAIR_TYPE' then
795 l_viewby_sel := 'v.value2 VIEWBY, ' || fnd_global.newline || 'v.id VIEWBYID, ';
796 ELSE
797 l_viewby_sel := 'v.value VIEWBY, ' || fnd_global.newline || 'v.id VIEWBYID, ';
798 END IF;
799
800 return l_viewby_sel;
801
802 END get_viewby_select_clause;
803
804 -- write
805 -- Generic routine for debug purpose
806 -- Date Author Action
807 -- 02-Aug-2004 Vijay Babu Gandhi created.
808
809 PROCEDURE write (p_module IN VARCHAR2,
810 p_err_stage IN VARCHAR2,
811 p_debug_level IN INTEGER)
812 IS
813 l_stmt_id VARCHAR2(3999);
814 l_log_level NUMBER;
815 l_debug_mode VARCHAR2(1);
816 l_module_name ISC_DEPOT_RPT_UTIL_PKG.g_module_name_typ%type;
817
818 BEGIN
819
820 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
821 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
822 l_log_level := FND_PROFILE.value('AFLOG_LEVEL');
823
824 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' and p_debug_level >= l_log_level THEN
825 FND_LOG.STRING(p_debug_level,p_Module,p_err_stage);
826 END IF;
827 END write;
828
829 FUNCTION get_repair_order_url
830 RETURN VARCHAR2
831 IS
832 l_repair_order_url varchar2 (500);
833 BEGIN
834 l_repair_order_url := '''pFunctionName=CSD_REPAIR_ORDER_SUMMARY&pParamIds=Y''';
835 RETURN l_repair_order_url;
836 END get_repair_order_url ;
837
838 FUNCTION get_service_request_url
839 RETURN VARCHAR2
840 IS
841 l_service_request_url varchar2 (500);
842 BEGIN
843 l_service_request_url :='''pFunctionName=CSZ_SR_UP_RO_FN' ||
844 '&cszReadOnlySRPageMode=REGULARREADONLY' ||
845 -- the following 2 parameters are no longer required (R12)
846 -- '&cszReadOnlySRRetURL=null' ||
847 -- '&cszReadOnlySRRetLabel=.' ||
848 '&OAPB=BIV_DBI_SR_BRAND'||
849 '&cszIncidentId=''';
850 RETURN l_service_request_url;
851 END get_service_request_url ;
852
853
854 END ISC_DEPOT_RPT_UTIL_PKG;