[Home] [Help]
PACKAGE BODY: APPS.BIV_DBI_TMPL_UTIL
Source
1 package body biv_dbi_tmpl_util as
2 /* $Header: bivsrvrutlb.pls 120.1 2006/06/27 10:06:50 asparama noship $ */
3
4 g_REQUEST_TYPE_BMAP number := 1;
5 g_CATEGORY_BMAP number := 2;
6 g_PRODUCT_BMAP number := 4;
7 g_SEVERITY_BMAP number := 8;
8 g_STATUS_BMAP number := 16;
9 g_CHANNEL_BMAP number := 32;
10 g_RESOLUTION_BMAP number := 64;
11 g_CUSTOMER_BMAP number := 128;
12 g_ASSIGNMENT_BMAP number := 256;
13 g_AGING_BMAP number := 512;
14 g_BACKLOG_TYPE_BMAP number := 1024;
15 g_RES_STATUS_BMAP number := 2048;
16
17 -- get_col_name function returns the name of the column
18 -- from the FACT table that is needed to join to
19 -- the DIMENSION table based on the VIEW BY selected (p_dim_name)
20 function get_fact_col_name
21 ( p_dim_name in varchar2
22 , p_product_cat in varchar2 default null )
23 return varchar2 is
24 begin
25 return (case p_dim_name
26 when g_REQUEST_TYPE then 'incident_type_id'
27 when g_CATEGORY then
28 case
29 when p_product_cat is null then 'vbh_parent_category_id'
30 else 'vbh_child_category_id'
31 end
32 when g_PRODUCT then 'product_id'
33 when g_SEVERITY then 'incident_severity_id'
34 when g_STATUS then 'incident_status_id'
35 when g_CHANNEL then 'sr_creation_channel'
36 when g_RESOLUTION then 'resolution_code'
37 when g_CUSTOMER then 'customer_id'
38 when g_ASSIGNMENT then 'owner_group_id'
39 when g_AGING then 'aging_id'
40 when g_RES_STATUS then 'resolved_flag'
41 else ''
42 end);
43 end get_fact_col_name;
44
45 -- get_table function returns the name of the DIMENSION table or
46 -- view (or inline view) that needs to be joined to based on the
47 -- VIEW BY select (p_dim_name)
48 function get_dim_table_name
49 ( p_dim_name in varchar2 )
50 return varchar2 is
51 begin
52 return (case p_dim_name
53 when g_REQUEST_TYPE then '(select incident_type_id id, name value from cs_incident_types_tl where userenv(''LANG'') = language)'
54 when g_CATEGORY then 'eni_item_vbh_nodes_v'
55 when g_PRODUCT then 'eni_item_v'
56 when g_SEVERITY then 'biv_severities_v'
57 when g_STATUS then 'biv_statuses_v'
58 when g_CHANNEL then 'biv_channels_v'
59 when g_RESOLUTION then 'biv_resolutions_v'
60 when g_CUSTOMER then 'aso_bi_prospect_v'
61 when g_ASSIGNMENT then '(select group_id id, group_name value from jtf_rs_groups_tl where userenv(''LANG'') = language)'
62 when g_AGING then '(select id, value from biv_bucket_aging_v where short_name=''BIV_DBI_BACKLOG_AGING'')'
63 when g_RES_STATUS then 'biv_dbi_res_status_v'
64 else ''
65 end);
66
67 end get_dim_table_name;
68
69 -- get_vb_aging_columns function returns the decoded columns
70 -- needed when backlog report is view by Aging
71 function get_vb_aging_columns
72 return varchar2
73 is
74 begin
75 return '
76 , a.id aging_id
77 , decode(a.id,1,f.backlog_age_b1
78 ,2,f.backlog_age_b2
79 ,3,f.backlog_age_b3
80 ,4,f.backlog_age_b4
81 ,5,f.backlog_age_b5
82 ,6,f.backlog_age_b6
83 ,7,f.backlog_age_b7
84 ,8,f.backlog_age_b8
85 ,9,f.backlog_age_b9
86 ,10,f.backlog_age_b10
87 ,null) backlog_count
88 , decode(a.id,1,f.total_backlog_age_b1
89 ,2,f.total_backlog_age_b2
90 ,3,f.total_backlog_age_b3
91 ,4,f.total_backlog_age_b4
92 ,5,f.total_backlog_age_b5
93 ,6,f.total_backlog_age_b6
94 ,7,f.total_backlog_age_b7
95 ,8,f.total_backlog_age_b8
96 ,9,f.total_backlog_age_b9
97 ,10,f.total_backlog_age_b10
98 ,null) total_backlog_age
99 , decode(a.id,1,f.escalated_age_b1
100 ,2,f.escalated_age_b2
101 ,3,f.escalated_age_b3
102 ,4,f.escalated_age_b4
103 ,5,f.escalated_age_b5
104 ,6,f.escalated_age_b6
105 ,7,f.escalated_age_b7
106 ,8,f.escalated_age_b8
107 ,9,f.escalated_age_b9
108 ,10,f.escalated_age_b10
109 ,null) escalated_count
110 , decode(a.id,1,f.unowned_age_b1
111 ,2,f.unowned_age_b2
112 ,3,f.unowned_age_b3
113 ,4,f.unowned_age_b4
114 ,5,f.unowned_age_b5
115 ,6,f.unowned_age_b6
116 ,7,f.unowned_age_b7
117 ,8,f.unowned_age_b8
118 ,9,f.unowned_age_b9
119 ,10,f.unowned_age_b10
120 ,null) unowned_count';
121 end get_vb_aging_columns;
122
123 -- get_fact_mv_name function returns the name of the FACT table
124 -- based on report type (p_report_type) and parameter bitmap (p_bmap)
125 function get_fact_mv_name
126 ( p_report_type in varchar2
127 , p_bmap in number
128 , p_xtd in varchar2
129 )
130 return varchar2 is
131 begin
132 if p_report_type = 'ACTIVITY' then
133 if bitand(p_bmap,g_PRODUCT_BMAP) = g_PRODUCT_BMAP or
134 bitand(p_bmap,g_CUSTOMER_BMAP) = g_CUSTOMER_BMAP or
135 bitand(p_bmap,g_ASSIGNMENT_BMAP) = g_ASSIGNMENT_BMAP or
136 bitand(p_bmap,g_CHANNEL_BMAP) = g_CHANNEL_BMAP then
137 if bitand(p_bmap,g_CATEGORY_BMAP) = g_CATEGORY_BMAP then
138 return '(
139 select
140 v.top_node_flag vbh_top_node_flag, v.parent_id vbh_parent_category_id, v.imm_child_id vbh_child_category_id
141 , f.time_id, f.period_type_id, f.incident_type_id, f.product_id, f.incident_severity_id
142 , f.customer_id, f.owner_group_id, f.sr_creation_channel
143 , f.first_opened_count
144 , f.reopened_count
145 , f.closed_count
146 from
147 biv_act_sum_mv f
148 , eni_denorm_hierarchies v
149 , mtl_default_category_sets m
150 where
151 m.functional_area_id = 11
152 and v.object_id = m.category_set_id
153 and v.dbi_flag = ''Y''
154 and v.object_type = ''CATEGORY_SET''
155 and f.vbh_category_id = v.child_id
156 and f.grp_id = ' || get_grp_id(p_bmap) || '
157 )';
158 else
159 return '(select * from biv_act_sum_mv where grp_id = ' ||
160 get_grp_id(p_bmap) || ')';
161 end if;
162 else
163 return 'biv_act_h_sum_mv';
164 end if;
165
166 elsif p_report_type = 'CLOSED' then
167 if bitand(p_bmap,g_PRODUCT_BMAP) = g_PRODUCT_BMAP or
168 bitand(p_bmap,g_CUSTOMER_BMAP) = g_CUSTOMER_BMAP or
169 bitand(p_bmap,g_ASSIGNMENT_BMAP) = g_ASSIGNMENT_BMAP or
170 bitand(p_bmap,g_CHANNEL_BMAP) = g_CHANNEL_BMAP or
171 bitand(p_bmap,g_RESOLUTION_BMAP) = g_RESOLUTION_BMAP then
172 if bitand(p_bmap,g_CATEGORY_BMAP) = g_CATEGORY_BMAP then
173 return '(
174 select
175 v.top_node_flag vbh_top_node_flag, v.parent_id vbh_parent_category_id, v.imm_child_id vbh_child_category_id
176 , f.time_id, f.period_type_id, f.incident_type_id, f.product_id, f.incident_severity_id
177 , f.customer_id, f.owner_group_id, f.sr_creation_channel, f.resolution_code
178 , f.closed_count
179 , f.total_time_to_close
180 , f.time_to_close_b1
181 , f.time_to_close_b2
182 , f.time_to_close_b3
183 , f.time_to_close_b4
184 , f.time_to_close_b5
185 , f.time_to_close_b6
186 , f.time_to_close_b7
187 , f.time_to_close_b8
188 , f.time_to_close_b9
189 , f.time_to_close_b10
190 from
191 biv_clo_sum_mv f
192 , eni_denorm_hierarchies v
193 , mtl_default_category_sets m
194 where
195 m.functional_area_id = 11
196 and v.object_id = m.category_set_id
197 and v.dbi_flag = ''Y''
198 and v.object_type = ''CATEGORY_SET''
199 and f.vbh_category_id = v.child_id
200 and f.grp_id = ' || get_grp_id(p_bmap) || '
201 )';
202 else
203 return '(select * from biv_clo_sum_mv where grp_id = ' ||
204 get_grp_id(p_bmap) || ')';
205 end if;
206 else
207 return 'biv_clo_h_sum_mv';
208 end if;
209
210 elsif p_report_type = 'RESOLVED' then
211 if bitand(p_bmap,g_PRODUCT_BMAP) = g_PRODUCT_BMAP or
212 bitand(p_bmap,g_CUSTOMER_BMAP) = g_CUSTOMER_BMAP or
213 bitand(p_bmap,g_ASSIGNMENT_BMAP) = g_ASSIGNMENT_BMAP or
214 bitand(p_bmap,g_CHANNEL_BMAP) = g_CHANNEL_BMAP or
215 bitand(p_bmap,g_RESOLUTION_BMAP) = g_RESOLUTION_BMAP then
216 if bitand(p_bmap,g_CATEGORY_BMAP) = g_CATEGORY_BMAP then
217 return '(
218 select
219 v.top_node_flag vbh_top_node_flag, v.parent_id vbh_parent_category_id, v.imm_child_id vbh_child_category_id
220 , f.time_id, f.period_type_id, f.incident_type_id, f.product_id, f.incident_severity_id
221 , f.customer_id, f.owner_group_id, f.sr_creation_channel, f.resolution_code
222 , f.resolution_count
223 , f.total_time_to_resolution
224 , f.time_to_resolution_b1
225 , f.time_to_resolution_b2
226 , f.time_to_resolution_b3
227 , f.time_to_resolution_b4
228 , f.time_to_resolution_b5
229 , f.time_to_resolution_b6
230 , f.time_to_resolution_b7
231 , f.time_to_resolution_b8
232 , f.time_to_resolution_b9
233 , f.time_to_resolution_b10
234 from
235 biv_res_sum_mv f
236 , eni_denorm_hierarchies v
237 , mtl_default_category_sets m
238 where
239 m.functional_area_id = 11
240 and v.object_id = m.category_set_id
241 and v.dbi_flag = ''Y''
242 and v.object_type = ''CATEGORY_SET''
243 and f.vbh_category_id = v.child_id
244 and f.grp_id = ' || get_grp_id(p_bmap) || '
245 )';
246 else
247 return '(select * from biv_res_sum_mv where grp_id = ' ||
248 get_grp_id(p_bmap) || ')';
249 end if;
250 else
251 return 'biv_res_h_sum_mv';
252 end if;
253
254 elsif p_report_type = 'BACKLOG' then
255 -- XTD Model
256 IF( p_xtd IN ('DAY','WTD','MTD','QTD','YTD') ) THEN
257 if bitand(p_bmap,g_PRODUCT_BMAP) = g_PRODUCT_BMAP or
258 bitand(p_bmap,g_CUSTOMER_BMAP) = g_CUSTOMER_BMAP or
259 bitand(p_bmap,g_ASSIGNMENT_BMAP) = g_ASSIGNMENT_BMAP or
260 bitand(p_bmap,g_STATUS_BMAP) = g_STATUS_BMAP then
261 if bitand(p_bmap,g_CATEGORY_BMAP) = g_CATEGORY_BMAP then
262 return '(
263 select
264 v.top_node_flag vbh_top_node_flag, v.parent_id vbh_parent_category_id, v.imm_child_id vbh_child_category_id , f.*
265 from
266 biv_bac_sum_mv f
267 , eni_denorm_hierarchies v
268 , mtl_default_category_sets m
269 where
270 m.functional_area_id = 11
271 and v.object_id = m.category_set_id
272 and v.dbi_flag = ''Y''
273 and v.object_type = ''CATEGORY_SET''
274 and f.vbh_category_id = v.child_id
275 and f.grp_id = ' || get_grp_id( p_bmap ) || '
276 )';
277 else
278 return '( select * from biv_bac_sum_mv f where grp_id = ' || get_grp_id( p_bmap ) || ')';
279 end if;
280 else
281 return 'biv_bac_h_sum_mv';
282 end if;
283 ELSE
284 -- Rolling Model
285 if bitand(p_bmap,g_PRODUCT_BMAP) = g_PRODUCT_BMAP or
286 bitand(p_bmap,g_CUSTOMER_BMAP) = g_CUSTOMER_BMAP or
287 bitand(p_bmap,g_ASSIGNMENT_BMAP) = g_ASSIGNMENT_BMAP or
288 bitand(p_bmap,g_STATUS_BMAP) = g_STATUS_BMAP then
289 if bitand(p_bmap,g_CATEGORY_BMAP) = g_CATEGORY_BMAP then
290 return '(
291 select
292 v.top_node_flag vbh_top_node_flag, v.parent_id vbh_parent_category_id, v.imm_child_id vbh_child_category_id
293 , f.incident_type_id, f.product_id, f.incident_severity_id
294 , f.customer_id, f.owner_group_id, f.incident_status_id, f.resolved_flag
295 , f.backlog_count
296 , f.escalated_count
297 , f.unowned_count
298 , c.report_date
299 from
300 biv_bac_sum_mv f
301 , eni_denorm_hierarchies v
302 , mtl_default_category_sets m
303 , fii_time_structures c
304 where
305 m.functional_area_id = 11
306 and v.object_id = m.category_set_id
307 and v.dbi_flag = ''Y''
308 and v.object_type = ''CATEGORY_SET''
309 and f.vbh_category_id = v.child_id
310 and f.grp_id = ' || get_grp_id( p_bmap ) || '
311 and f.time_id = c.time_id
312 and f.period_type_id = c.period_type_id
313 and bitand(c.record_type_id,512) = 512
314 )';
315 else
316 return '(
317 select
318 f.incident_type_id, f.product_id, f.incident_severity_id
319 , f.customer_id, f.owner_group_id, f.incident_status_id
320 , f.resolved_flag
321 , f.backlog_count
322 , f.escalated_count
323 , f.unowned_count
324 , c.report_date
325 from
326 biv_bac_sum_mv f
327 , fii_time_structures c
328 where grp_id = ' || get_grp_id( p_bmap ) || '
329 and f.time_id = c.time_id
330 and f.period_type_id = c.period_type_id
331 and bitand(c.record_type_id,512) = 512
332 )';
333 end if;
334 else
335 return '(
336 select
337 f.vbh_top_node_flag, f.vbh_parent_category_id, f.vbh_child_category_id
338 , f.incident_type_id, f.incident_severity_id
339 , f.resolved_flag
340 , f.backlog_count
341 , f.escalated_count
342 , f.unowned_count
343 , c.report_date
344 from
345 biv_bac_h_sum_mv f
346 , fii_time_structures c
347 where
348 f.time_id = c.time_id
349 and f.period_type_id = c.period_type_id
350 and bitand(c.record_type_id,512) = 512
351 )';
352 end if;
353 END IF; -- End of XTD Vs Rolloing
354
355 elsif p_report_type = 'BACKLOG_AGE' then
356 if bitand(p_bmap,g_PRODUCT_BMAP) = g_PRODUCT_BMAP or
357 bitand(p_bmap,g_CUSTOMER_BMAP) = g_CUSTOMER_BMAP or
358 bitand(p_bmap,g_ASSIGNMENT_BMAP) = g_ASSIGNMENT_BMAP or
359 bitand(p_bmap,g_STATUS_BMAP) = g_STATUS_BMAP then
360 if bitand(p_bmap,g_CATEGORY_BMAP) = g_CATEGORY_BMAP then
361 return '(
362 select
363 v.top_node_flag vbh_top_node_flag, v.parent_id vbh_parent_category_id, v.imm_child_id vbh_child_category_id
364 , f.report_date, f.incident_type_id, f.product_id, f.incident_severity_id
365 , f.customer_id, f.owner_group_id, f.incident_status_id, f.resolved_flag
366 , f.backlog_count
367 , f.total_backlog_age
368 , f.backlog_age_b1
369 , f.backlog_age_b2
370 , f.backlog_age_b3
371 , f.backlog_age_b4
372 , f.backlog_age_b5
373 , f.backlog_age_b6
374 , f.backlog_age_b7
375 , f.backlog_age_b8
376 , f.backlog_age_b9
377 , f.backlog_age_b10
378 , f.escalated_count
379 , f.total_escalated_age
380 , f.escalated_age_b1
381 , f.escalated_age_b2
382 , f.escalated_age_b3
383 , f.escalated_age_b4
384 , f.escalated_age_b5
385 , f.escalated_age_b6
386 , f.escalated_age_b7
387 , f.escalated_age_b8
388 , f.escalated_age_b9
389 , f.escalated_age_b10
390 , f.unowned_count
391 , f.total_unowned_age
392 , f.unowned_age_b1
393 , f.unowned_age_b2
394 , f.unowned_age_b3
395 , f.unowned_age_b4
396 , f.unowned_age_b5
397 , f.unowned_age_b6
398 , f.unowned_age_b7
402 from
399 , f.unowned_age_b8
400 , f.unowned_age_b9
401 , f.unowned_age_b10
403 biv_bac_age_sum_f f
404 , eni_denorm_hierarchies v
405 , mtl_default_category_sets m
406 where
407 m.functional_area_id = 11
408 and v.object_id = m.category_set_id
409 and v.dbi_flag = ''Y''
410 and v.object_type = ''CATEGORY_SET''
411 and f.vbh_category_id = v.child_id
412 and f.grp_id = ' || get_grp_id( p_bmap ) || '
413 )';
414 else
415 return '(select * from biv_bac_age_sum_f where grp_id = ' ||
416 get_grp_id( p_bmap ) || ')';
417 end if;
418 else
419 return 'biv_b_age_h_sum_mv';
420 end if;
421 elsif p_report_type = 'BACKLOG_DETAIL' then
422 return '(
423 select
424 f.backlog_date_from
425 , f.backlog_date_to
426 , f.incident_id
427 , f.incident_type_id
428 , nvl(s.master_id,s.id) product_id
429 , f.incident_severity_id
430 , f.customer_id
431 , f.owner_group_id
432 , f.incident_status_id
433 , f.incident_date
434 , f.resolved_flag
435 , f.escalated_date
436 , f.unowned_date
437 , (&AGE_CURRENT_ASOF_DATE + &AGE_CURRENT_ASOF_DATE_TIME) - f.incident_date age' ||
438 case when bitand(p_bmap,g_CATEGORY_BMAP) = g_CATEGORY_BMAP then
439 '
440 , v.top_node_flag vbh_top_node_flag
441 , v.parent_id vbh_parent_category_id
442 , v.imm_child_id vbh_child_category_id'
443 else null
444 end || '
445 from
446 biv_dbi_backlog_sum_f f
447 , eni_oltp_item_star s' ||
448 case when bitand(p_bmap,g_CATEGORY_BMAP) = g_CATEGORY_BMAP then
449 '
450 , eni_denorm_hierarchies v
451 , mtl_default_category_sets m'
452 else null
453 end || '
454 where
455 f.inventory_item_id = s.inventory_item_id
456 and f.inv_organization_id = s.organization_id' ||
457 case when bitand(p_bmap,g_CATEGORY_BMAP) = g_CATEGORY_BMAP then
458 '
459 and m.functional_area_id = 11
460 and v.object_id = m.category_set_id
461 and v.dbi_flag = ''Y''
462 and v.object_type = ''CATEGORY_SET''
463 and s.vbh_category_id = v.child_id'
464 else null
465 end || '
466 and (&AGE_CURRENT_ASOF_DATE + &AGE_CURRENT_ASOF_DATE_TIME) - f.incident_date >= 0
467 )';
468
469 elsif p_report_type = 'CLOSED_DETAIL' then
470 return '(
471 select
472 f.report_date closed_date
473 , f.incident_id
474 , f.incident_type_id
475 , nvl(s.master_id,s.id) product_id
476 , f.incident_severity_id
477 , f.customer_id
478 , f.owner_group_id
479 , f.sr_creation_channel
480 , f.resolution_code
481 , f.time_to_close age' ||
482 case when bitand(p_bmap,g_CATEGORY_BMAP) = g_CATEGORY_BMAP then
483 '
484 , v.top_node_flag vbh_top_node_flag
485 , v.parent_id vbh_parent_category_id
486 , v.imm_child_id vbh_child_category_id'
487 else null
488 end || '
489 from
490 biv_dbi_closed_sum_f f
491 , eni_oltp_item_star s' ||
492 case when bitand(p_bmap,g_CATEGORY_BMAP) = g_CATEGORY_BMAP then
493 '
494 , eni_denorm_hierarchies v
495 , mtl_default_category_sets m'
496 else null
497 end || '
498 where
499 f.inventory_item_id = s.inventory_item_id
500 and f.inv_organization_id = s.organization_id' ||
501 case when bitand(p_bmap,g_CATEGORY_BMAP) = g_CATEGORY_BMAP then
502 '
503 and m.functional_area_id = 11
504 and v.object_id = m.category_set_id
505 and v.dbi_flag = ''Y''
506 and v.object_type = ''CATEGORY_SET''
507 and s.vbh_category_id = v.child_id'
508 else null
509 end || '
510 and f.reopened_date is null
511 and f.time_to_close >= 0
512 )';
513
514 elsif p_report_type = 'RESOLVED_DETAIL' then
515 return '(
516 select
517 f.report_date resolved_date
518 , f.incident_id
519 , f.incident_type_id
520 , nvl(s.master_id,s.id) product_id
521 , f.incident_severity_id
522 , f.customer_id
523 , f.owner_group_id
524 , f.sr_creation_channel
525 , f.resolution_code
526 , f.time_to_resolution age' ||
527 case when bitand(p_bmap,g_CATEGORY_BMAP) = g_CATEGORY_BMAP then
528 '
529 , v.top_node_flag vbh_top_node_flag
530 , v.parent_id vbh_parent_category_id
531 , v.imm_child_id vbh_child_category_id'
532 else null
533 end || '
534 from
535 biv_dbi_resolution_sum_f f
536 , eni_oltp_item_star s' ||
537 case when bitand(p_bmap,g_CATEGORY_BMAP) = g_CATEGORY_BMAP then
538 '
539 , eni_denorm_hierarchies v
540 , mtl_default_category_sets m'
541 else null
542 end || '
543 where
544 f.inventory_item_id = s.inventory_item_id
545 and f.inv_organization_id = s.organization_id' ||
546 case when bitand(p_bmap,g_CATEGORY_BMAP) = g_CATEGORY_BMAP then
547 '
548 and m.functional_area_id = 11
549 and v.object_id = m.category_set_id
550 and v.dbi_flag = ''Y''
551 and v.object_type = ''CATEGORY_SET''
555 and f.time_to_resolution >= 0
552 and s.vbh_category_id = v.child_id'
553 else null
554 end || '
556 )';
557 end if;
558
559 return '';
560 end get_fact_mv_name;
561
562 procedure init_dim_map
563 ( x_dim_map out nocopy poa_DBI_UTIL_PKG.poa_dbi_dim_map
564 , p_report_type in varchar2 -- 'ACTIVITY','CLOSED','BACKLOG','BACKLOG_AGE'
565 -- 'BACKLOG_DETAIL', 'CLOSED_DETAIL', 'RESOLVED'
566 -- , 'RESOLVED_DETAIL'
567 , p_product_cat in varchar2
568 ) is
569 l_dim_rec poa_DBI_UTIL_PKG.poa_dbi_dim_rec;
570 begin
571 -- Request Type
572 l_dim_rec.col_name := get_fact_col_name(g_REQUEST_TYPE);
573 l_dim_rec.view_by_table := get_dim_table_name(g_REQUEST_TYPE);
574 l_dim_rec.bmap := G_REQUEST_TYPE_BMAP;
575 x_dim_map(g_REQUEST_TYPE) := l_dim_rec;
576
577 -- Product Category
578 l_dim_rec.col_name := get_fact_col_name(g_CATEGORY, p_product_cat);
579 l_dim_rec.view_by_table := get_dim_table_name(g_CATEGORY);
580 l_dim_rec.bmap := G_CATEGORY_BMAP;
581 x_dim_map(g_CATEGORY) := l_dim_rec;
582
583 -- Product
584 l_dim_rec.col_name := get_fact_col_name(g_PRODUCT);
585 l_dim_rec.view_by_table := get_dim_table_name(g_PRODUCT);
586 l_dim_rec.bmap := G_PRODUCT_BMAP;
587 x_dim_map(g_PRODUCT) := l_dim_rec;
588
589 -- Severity
590 l_dim_rec.col_name := get_fact_col_name(g_SEVERITY);
591 l_dim_rec.view_by_table := get_dim_table_name(g_SEVERITY);
592 l_dim_rec.bmap := G_SEVERITY_BMAP;
593 x_dim_map(g_SEVERITY) := l_dim_rec;
594
595 -- Status
596 if p_report_type in ('BACKLOG','BACKLOG_AGE','BACKLOG_DETAIL') then
597 l_dim_rec.col_name := get_fact_col_name(g_STATUS);
598 l_dim_rec.view_by_table := get_dim_table_name(g_STATUS);
599 l_dim_rec.bmap := G_STATUS_BMAP;
600 x_dim_map(g_STATUS) := l_dim_rec;
601 end if;
602
603 -- Channel
604 if p_report_type in ( 'ACTIVITY', 'CLOSED','CLOSED_DETAIL', 'RESOLVED','RESOLVED_DETAIL' ) then
605 l_dim_rec.col_name := get_fact_col_name(g_CHANNEL);
606 l_dim_rec.view_by_table := get_dim_table_name(g_CHANNEL);
607 l_dim_rec.bmap := G_CHANNEL_BMAP;
608 x_dim_map(g_CHANNEL) := l_dim_rec;
609 end if;
610
611 -- Resolution
612 if p_report_type in ( 'CLOSED','CLOSED_DETAIL','RESOLVED' ,'RESOLVED_DETAIL' ) then
613 l_dim_rec.col_name := get_fact_col_name(g_RESOLUTION);
614 l_dim_rec.view_by_table := get_dim_table_name(g_RESOLUTION);
615 l_dim_rec.bmap := G_RESOLUTION_BMAP;
616 x_dim_map(g_RESOLUTION) := l_dim_rec;
617 end if;
618
619 -- Customer
620 l_dim_rec.col_name := get_fact_col_name(g_CUSTOMER);
621 l_dim_rec.view_by_table := get_dim_table_name(g_CUSTOMER);
622 l_dim_rec.bmap := G_CUSTOMER_BMAP;
623 x_dim_map(g_CUSTOMER) := l_dim_rec;
624
625 -- Assignment Group
626 l_dim_rec.col_name := get_fact_col_name(g_ASSIGNMENT);
627 l_dim_rec.view_by_table := get_dim_table_name(g_ASSIGNMENT);
628 l_dim_rec.bmap := G_ASSIGNMENT_BMAP;
629 x_dim_map(g_ASSIGNMENT) := l_dim_rec;
630
631 if p_report_type in ( 'BACKLOG_DETAIL', 'CLOSED_DETAIL', 'RESOLVED_DETAIL' ) then
632 l_dim_rec.col_name := '<replace this>';
633 l_dim_rec.view_by_table := get_dim_table_name(g_AGING);
634 l_dim_rec.bmap := g_AGING_BMAP;
635 x_dim_map(g_AGING) := l_dim_rec;
636 end if;
637
638 -- Backlog Type
639 if p_report_type = 'BACKLOG_AGE' then
640 l_dim_rec.col_name := get_fact_col_name(g_BACKLOG_TYPE);
641 l_dim_rec.view_by_table := get_dim_table_name(g_BACKLOG_TYPE);
642 l_dim_rec.bmap := g_BACKLOG_TYPE_BMAP;
643 -- don't register Backlog Type as we deal with this in the
644 -- outer query
645 --x_dim_map(g_BACKLOG_TYPE) := l_dim_rec;
646 end if;
647
648 -- Resolution Status
649 l_dim_rec.col_name := get_fact_col_name(g_RES_STATUS);
650 l_dim_rec.view_by_table := get_dim_table_name(g_RES_STATUS);
651 l_dim_rec.bmap := g_RES_STATUS_BMAP;
652 x_dim_map(g_RES_STATUS) := l_dim_rec;
653
654
655 end init_dim_map;
656
657 function get_join_info
658 ( p_view_by in varchar2
659 , p_dim_map in poa_DBI_UTIL_PKG.poa_dbi_dim_map
660 )
661 return poa_DBI_UTIL_PKG.poa_dbi_join_tbl
662 is
663 l_join_rec poa_DBI_UTIL_PKG.poa_dbi_join_rec;
664 l_join_tbl poa_DBI_UTIL_PKG.poa_dbi_join_tbl;
665 begin
666 -- reinitialize the join table
667 l_join_tbl := poa_DBI_UTIL_PKG.poa_dbi_join_tbl();
668
669 -- If the view by column is not in the bitmap, then
670 -- there is nothing to join to. Can this ever be true?
671 if (not p_dim_map.exists(p_view_by)) then
672 return l_join_tbl;
673 end if;
674
675 -- Otherwise, join to a table
676 -- The view by table
677 l_join_rec.table_name := p_dim_map(p_view_by).view_by_table;
678 l_join_rec.table_alias := 'v';
679
680 -- the fact column to join to
681 l_join_rec.fact_column := p_dim_map(p_view_by).col_name;
682
683 --
684 if p_view_by = g_CATEGORY then
685 l_join_rec.additional_where_clause := 'v.parent_id = v.child_id';
686 end if;
687
688 -- depending on the dimension level, select the appropriate
689 -- join table column name
693 when g_CATEGORY then 'id'
690 l_join_rec.column_name :=
691 (case p_view_by
692 when g_REQUEST_TYPE then 'id'
694 when g_PRODUCT then 'id'
695 when g_SEVERITY then 'id'
696 when g_STATUS then 'id'
697 when g_CHANNEL then 'id'
698 when g_RESOLUTION then 'id'
699 when g_CUSTOMER then 'id'
700 when g_ASSIGNMENT then 'id'
701 when g_AGING then 'id'
702 when g_RES_STATUS then 'id'
703 else ''
704 end);
705
706 --l_join_rec.dim_outer_join := 'Y';
707
708 -- Add the join table
709 l_join_tbl.extend;
710 l_join_tbl(l_join_tbl.count) := l_join_rec;
711
712 return l_join_tbl;
713
714 END get_join_info;
715
716 function get_type_sec_where_clause
717 return varchar2
718 is
719 begin
720 return biv_dbi_tmpl_sec.get_type_sec_where_clause;
721 end get_type_sec_where_clause;
722
723 function get_product_category
724 ( p_param in bis_pmv_page_parameter_tbl )
725 return varchar2
726 is
727 begin
728 for i in 1..p_param.count loop
729 if p_param(i).parameter_name = g_CATEGORY then
730 return replace(p_param(i).parameter_id,'''',null);
731 end if;
732 end loop;
733 return null;
734 end get_product_category;
735
736 procedure get_detail_join_info
737 ( p_report_type in varchar2
738 , x_join_from out nocopy varchar2
739 , x_join_where out nocopy varchar2
740 )
741 is
742
743 l_dim_map poa_DBI_UTIL_PKG.poa_dbi_dim_map;
744 l_alias varchar2(10);
745 l_dim varchar2(50);
746
747 begin
748
749 init_dim_map(l_dim_map, p_report_type, 'All');
750
751 l_dim := l_dim_map.first;
752 loop
753 exit when l_dim is null;
754
755 if l_dim = g_REQUEST_TYPE then
756 l_alias := 'rt';
757 elsif l_dim = g_PRODUCT then
758 l_alias := 'pr';
759 elsif l_dim = g_SEVERITY then
760 l_alias := 'sv';
761 elsif l_dim = g_STATUS then
762 l_alias := 'st';
763 elsif l_dim = g_CHANNEL then
764 l_alias := 'ch';
765 elsif l_dim = g_RESOLUTION then
766 l_alias := 're';
767 elsif l_dim = g_CUSTOMER then
768 l_alias := 'cu';
769 elsif l_dim = g_ASSIGNMENT then
770 l_alias := 'ag';
771 elsif l_dim = g_RES_STATUS then
772 l_alias := 'rs';
773 else
774 l_alias := null;
775 end if;
776
777 -- added l_alias <> rs condition to avoid joins to resolved dimension
778 if (l_alias is not null and l_alias <> 'rs') then
779 x_join_from := x_join_from || '
780 , ' || l_dim_map(l_dim).view_by_table || ' ' || l_alias;
781
782 x_join_where := x_join_where || '
783 and fact.' || l_dim_map(l_dim).col_name || ' = ' || l_alias || '.id';
784
785 end if;
786
787 l_dim := l_dim_map.next(l_dim);
788 end loop;
789
790 end get_detail_join_info;
791
792 -- ------------------------------------------------------------
793 -- PUBLIC PROCEDURES ARE HERE
794 -- ------------------------------------------------------------
795
796 procedure process_parameters
797 ( p_param in bis_pmv_page_parameter_tbl
798 , p_report_type in varchar2 -- 'ACTIVITY','CLOSED','BACKLOG','BACKLOG_AGE'
799 , p_trend in varchar2
800 , x_view_by out nocopy varchar2
801 , x_view_by_col_name out nocopy varchar2
802 , x_comparison_type out nocopy varchar2
803 , x_xtd out nocopy varchar2
804 , x_where_clause out nocopy varchar2
805 , x_mv out nocopy varchar2
806 , x_join_tbl out nocopy poa_DBI_UTIL_PKG.poa_dbi_join_tbl
807 , x_as_of_date out nocopy date
808 ) is
809
810 l_dim_map poa_DBI_UTIL_PKG.poa_dbi_dim_map;
811 l_as_of_date date;
812 l_prev_as_of_date date;
813 l_nested_pattern number;
814 l_cur_suffix varchar2(1);
815 l_dim_bmap number := 0;
816 l_view_by varchar2(50);
817 l_where_clause varchar2(10000);
818 l_mv_name varchar2(10000);
819 l_product_cat varchar2(1000);
820
821 begin
822
823 l_product_cat := get_product_category(p_param);
824
825 init_dim_map(l_dim_map, p_report_type, l_product_cat);
826
827 poa_DBI_UTIL_PKG.get_parameter_values
828 ( p_param => p_param
829 , p_dim_map => l_dim_map
830 , p_view_by => l_view_by
831 , p_comparison_type => x_comparison_type
832 , p_xtd => x_xtd
833 , p_as_of_date => x_as_of_date
834 , p_prev_as_of_date => l_prev_as_of_date
835 , p_cur_suffix => l_cur_suffix
836 , p_nested_pattern => l_nested_pattern
837 , p_dim_bmap => l_dim_bmap
838 );
839
840 l_mv_name := get_fact_mv_name(p_report_type, l_dim_bmap,x_xtd);
841
845 x_view_by_col_name := 'vbh_parent_category_id';
842 if (l_dim_map.exists(l_view_by)) then
843 if l_dim_map(l_view_by).col_name = 'vbh_child_category_id' and
844 l_product_cat is null then
846 else
847 x_view_by_col_name := l_dim_map(l_view_by).col_name;
848 end if;
849 end if;
850
851 x_view_by := l_view_by;
852
853 l_where_clause := poa_DBI_UTIL_PKG.get_where_clauses(l_dim_map, p_trend);
854
855 if l_mv_name like '%_h_sum_mv%' or
856 l_mv_name like '%vbh_top_node_flag%' then
857 if l_product_cat is null then
858 l_where_clause := l_where_clause || ' and fact.vbh_top_node_flag = ''Y''';
859 else
860 l_where_clause := replace(l_where_clause, 'vbh_child_category_id', 'vbh_parent_category_id');
861 end if;
862 end if;
863
864 l_where_clause := l_where_clause || get_type_sec_where_clause;
865
866 x_where_clause := l_where_clause;
867
868 x_join_tbl := get_join_info(l_view_by, l_dim_map);
869
870 -- no longer need to do this as poa_DBI_UTIL_PKG.get_parameter_values
871 -- does it correctly.
872 --
873 -- x_xtd := get_period_type(p_param);
874
875 x_mv := l_mv_name;
876
877
878 end process_parameters;
879
880 function get_period_type
881 ( p_param in bis_pmv_page_parameter_tbl )
882 return varchar2
883 is
884 begin
885 for i in 1..p_param.count loop
886 if p_param(i).parameter_name = 'PERIOD_TYPE' then
887 return case
888 when p_param(i).parameter_value = 'FII_ROLLING_WEEK' then
889 'RLW'
890 when p_param(i).parameter_value = 'FII_ROLLING_MONTH' then
891 'RLM'
892 when p_param(i).parameter_value = 'FII_ROLLING_QTR' then
893 'RLQ'
894 when p_param(i).parameter_value = 'FII_ROLLING_YEAR' then
895 'RLY'
896 else
897 ''
898 end;
899 end if;
900 end loop;
901 return null;
902 end get_period_type;
903
904 function get_backlog_type
905 ( p_param in bis_pmv_page_parameter_tbl )
906 return varchar2
907 is
908 begin
909 for i in 1..p_param.count loop
910 if p_param(i).parameter_name = g_BACKLOG_TYPE then
911 return replace(p_param(i).parameter_id,'''',null);
912 end if;
913 end loop;
914 return null;
915 end get_backlog_type;
916
917 function get_bucket_query
918 ( p_column_name_base in varchar2
919 , p_column_number in number
920 , p_alias_base in varchar2
921 , p_total_flag in varchar2
922 , p_backlog_col in varchar2
923 )
924 return varchar2
925 is
926 begin
927 if p_backlog_col is null then
928 return ', nvl(' || p_column_name_base || '_b' || p_column_number ||
929 case p_total_flag
930 when 'Y' then '_total'
931 end || ',0) ' ||
932 p_alias_base || '_B' || p_column_number;
933 end if;
934 return ', ' || rate_column( p_column_name_base || '_b' || p_column_number ||
935 case p_total_flag
936 when 'Y' then '_total'
937 end
938 , p_backlog_col ||
939 case p_total_flag
940 when 'Y' then '_total'
941 end
942 , p_alias_base || '_B' || p_column_number
943 );
944
945 end get_bucket_query;
946
947 function get_bucket_outer_query
948 ( p_bucket_rec in bis_bucket_pub.bis_bucket_rec_type
949 , p_column_name_base in varchar2
950 , p_alias_base in varchar2
951 , p_total_flag in varchar2 default 'N'
952 , p_backlog_col in varchar2 default null
953 )
954 return varchar2
955 is
956 l_query varchar2(10000);
957 begin
958 if p_bucket_rec.range1_name is not null then
959 l_query := get_bucket_query(p_column_name_base,1,p_alias_base,p_total_flag,p_backlog_col);
960 end if;
961 if p_bucket_rec.range2_name is not null then
962 l_query := l_query || fnd_global.newline ||
963 get_bucket_query(p_column_name_base,2,p_alias_base,p_total_flag,p_backlog_col);
964 end if;
965 if p_bucket_rec.range3_name is not null then
966 l_query := l_query || fnd_global.newline ||
967 get_bucket_query(p_column_name_base,3,p_alias_base,p_total_flag,p_backlog_col);
968 end if;
969 if p_bucket_rec.range4_name is not null then
970 l_query := l_query || fnd_global.newline ||
974 l_query := l_query || fnd_global.newline ||
971 get_bucket_query(p_column_name_base,4,p_alias_base,p_total_flag,p_backlog_col);
972 end if;
973 if p_bucket_rec.range5_name is not null then
975 get_bucket_query(p_column_name_base,5,p_alias_base,p_total_flag,p_backlog_col);
976 end if;
977 if p_bucket_rec.range6_name is not null then
978 l_query := l_query || fnd_global.newline ||
979 get_bucket_query(p_column_name_base,6,p_alias_base,p_total_flag,p_backlog_col);
980 end if;
981 if p_bucket_rec.range7_name is not null then
982 l_query := l_query || fnd_global.newline ||
983 get_bucket_query(p_column_name_base,7,p_alias_base,p_total_flag,p_backlog_col);
984 end if;
985 if p_bucket_rec.range8_name is not null then
986 l_query := l_query || fnd_global.newline ||
987 get_bucket_query(p_column_name_base,8,p_alias_base,p_total_flag,p_backlog_col);
988 end if;
989 if p_bucket_rec.range9_name is not null then
990 l_query := l_query || fnd_global.newline ||
991 get_bucket_query(p_column_name_base,9,p_alias_base,p_total_flag,p_backlog_col);
992 end if;
993 if p_bucket_rec.range10_name is not null then
994 l_query := l_query || fnd_global.newline ||
995 get_bucket_query(p_column_name_base,10,p_alias_base,p_total_flag,p_backlog_col);
996 end if;
997 return l_query;
998 end get_bucket_outer_query;
999
1000 procedure add_bucket_inner_query
1001 ( p_short_name in varchar2
1002 , p_col_tbl in out nocopy poa_DBI_UTIL_PKG.poa_dbi_col_tbl
1003 , p_col_name in varchar2
1004 , p_alias_name in varchar2
1005 , p_grand_total in varchar2
1006 , p_prior_code in varchar2
1007 , p_to_date_type in varchar2
1008 , x_bucket_rec out nocopy bis_bucket_pub.bis_bucket_rec_type
1009 )
1010 is
1011 l_bucket_rec bis_bucket_pub.bis_bucket_rec_type;
1012 l_return_status varchar2(3);
1013 l_error_tbl bis_utilities_pub.error_tbl_type;
1014 begin
1015 bis_bucket_pub.retrieve_bis_bucket
1016 ( p_short_name => p_short_name
1017 , x_bis_bucket_rec => l_bucket_rec
1018 , x_return_status => l_return_status
1019 , x_error_tbl => l_error_tbl
1020 );
1021 if l_return_status = 'S' then
1022 if l_bucket_rec.range1_name is not null then
1023 poa_DBI_UTIL_PKG.add_column
1024 ( p_col_tbl => p_col_tbl
1025 , p_col_name => p_col_name || '_b1'
1026 , p_alias_name => p_alias_name || '_b1'
1027 , p_to_date_type => p_to_date_type
1028 , p_prior_code => p_prior_code
1029 );
1030 end if;
1031 if l_bucket_rec.range2_name is not null then
1032 poa_DBI_UTIL_PKG.add_column
1033 ( p_col_tbl => p_col_tbl
1034 , p_col_name => p_col_name || '_b2'
1035 , p_alias_name => p_alias_name || '_b2'
1036 , p_to_date_type => p_to_date_type
1037 , p_prior_code => p_prior_code
1038 );
1039 end if;
1040 if l_bucket_rec.range3_name is not null then
1041 poa_DBI_UTIL_PKG.add_column
1042 ( p_col_tbl => p_col_tbl
1043 , p_col_name => p_col_name || '_b3'
1044 , p_alias_name => p_alias_name || '_b3'
1045 , p_to_date_type => p_to_date_type
1046 , p_prior_code => p_prior_code
1047 );
1048 end if;
1049 if l_bucket_rec.range4_name is not null then
1050 poa_DBI_UTIL_PKG.add_column
1051 ( p_col_tbl => p_col_tbl
1052 , p_col_name => p_col_name || '_b4'
1053 , p_alias_name => p_alias_name || '_b4'
1054 , p_to_date_type => p_to_date_type
1055 , p_prior_code => p_prior_code
1056 );
1057 end if;
1058 if l_bucket_rec.range5_name is not null then
1059 poa_DBI_UTIL_PKG.add_column
1060 ( p_col_tbl => p_col_tbl
1061 , p_col_name => p_col_name || '_b5'
1062 , p_alias_name => p_alias_name || '_b5'
1063 , p_to_date_type => p_to_date_type
1064 , p_prior_code => p_prior_code
1065 );
1066 end if;
1067 if l_bucket_rec.range6_name is not null then
1068 poa_DBI_UTIL_PKG.add_column
1069 ( p_col_tbl => p_col_tbl
1070 , p_col_name => p_col_name || '_b6'
1071 , p_alias_name => p_alias_name || '_b6'
1075 end if;
1072 , p_to_date_type => p_to_date_type
1073 , p_prior_code => p_prior_code
1074 );
1076 if l_bucket_rec.range7_name is not null then
1077 poa_DBI_UTIL_PKG.add_column
1078 ( p_col_tbl => p_col_tbl
1079 , p_col_name => p_col_name || '_b7'
1080 , p_alias_name => p_alias_name || '_b7'
1081 , p_to_date_type => p_to_date_type
1082 , p_prior_code => p_prior_code
1083 );
1084 end if;
1085 if l_bucket_rec.range8_name is not null then
1086 poa_DBI_UTIL_PKG.add_column
1087 ( p_col_tbl => p_col_tbl
1088 , p_col_name => p_col_name || '_b8'
1089 , p_alias_name => p_alias_name || '_b8'
1090 , p_to_date_type => p_to_date_type
1091 , p_prior_code => p_prior_code
1092 );
1093 end if;
1094 if l_bucket_rec.range9_name is not null then
1095 poa_DBI_UTIL_PKG.add_column
1096 ( p_col_tbl => p_col_tbl
1097 , p_col_name => p_col_name || '_b9'
1098 , p_alias_name => p_alias_name || '_b9'
1099 , p_to_date_type => p_to_date_type
1100 , p_prior_code => p_prior_code
1101 );
1102 end if;
1103 if l_bucket_rec.range10_name is not null then
1104 poa_DBI_UTIL_PKG.add_column
1105 ( p_col_tbl => p_col_tbl
1106 , p_col_name => p_col_name || '_b10'
1107 , p_alias_name => p_alias_name || '_b10'
1108 , p_to_date_type => p_to_date_type
1109 , p_prior_code => p_prior_code
1110 );
1111 end if;
1112 end if;
1113 x_bucket_rec := l_bucket_rec;
1114 end add_bucket_inner_query;
1115
1116 function get_view_by_col_name
1117 ( p_dim_name in varchar2 )
1118 return varchar2
1119 is
1120 l_col_name varchar2(60);
1121 begin
1122 return (case p_dim_name
1123 when g_REQUEST_TYPE then 'v.value'
1124 when g_CATEGORY then 'v.value'
1125 when g_PRODUCT then 'v.value'
1126 when g_SEVERITY then 'v.value'
1127 when g_STATUS then 'v.value'
1128 when g_CHANNEL then 'v.value'
1129 when g_RESOLUTION then 'v.value'
1130 when g_CUSTOMER then 'v.value'
1131 when g_ASSIGNMENT then 'v.value'
1132 when g_AGING then 'v.value'
1133 when g_RES_STATUS then 'v.value'
1134 else ''
1135 end);
1136 end get_view_by_col_name;
1137
1138 function get_category_drill_down
1139 ( p_view_by_name in varchar2
1140 , p_function_name in varchar2
1141 , p_column_alias in varchar2 default 'BIV_ATTRIBUTE4' )
1142 return varchar2
1143 is
1144 begin
1145 if p_view_by_name = g_CATEGORY then
1146 return 'decode(v.leaf_node_flag, ''Y''' ||
1147 ',''pFunctionName=' || p_function_name || '&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM&pParamIds=Y''' ||
1148 ',''pFunctionName=' || p_function_name || '&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y''' ||
1149 ') ' || p_column_alias;
1150 end if;
1151
1152 return 'null ' || p_column_alias;
1153
1154 end get_category_drill_down;
1155
1156 -- this is a wrapper to poa_dbi_util_pkg.change_clause
1157 function change_column
1158 ( p_current_column in varchar2
1159 , p_prior_column in varchar2
1160 , p_column_alias in varchar2
1161 , p_percent in varchar2 default 'Y'
1162 ) return varchar2
1163 is
1164 begin
1165 if p_percent = 'Y' then
1169 -- return poa_DBI_UTIL_PKG.change_clause('nvl('||p_current_column||',0)',p_prior_column,'X') ||
1166 return poa_DBI_UTIL_PKG.change_clause(p_current_column,p_prior_column) ||
1167 ' ' || p_column_alias;
1168 end if;
1170 return poa_DBI_UTIL_PKG.change_clause(p_current_column,p_prior_column,'X') ||
1171 ' ' || p_column_alias;
1172 end change_column;
1173
1174 -- this is a wrapper to poa_dbi_util_pkg.rate_clause
1175 function rate_column
1176 ( p_numerator in varchar2
1177 , p_denominator in varchar2
1178 , p_column_alias in varchar2
1179 , p_percent in varchar2 default 'Y'
1180 ) return varchar2
1181 is
1182 begin
1183 return poa_DBI_UTIL_PKG.rate_clause( p_numerator
1184 , p_denominator
1185 , case p_percent
1186 when 'Y' then 'P'
1187 else 'NP'
1188 end ) ||
1189 ' ' || p_column_alias;
1190 end rate_column;
1191
1192 function dump_parameters
1193 ( p_param in bis_pmv_page_parameter_tbl )
1194 return varchar2
1195 is
1196 l_stmt varchar2(10000);
1197 begin
1198 l_stmt := '
1199 /*
1200 ';
1201 for i in 1..p_param.count loop
1202 l_stmt := l_stmt || '"' || p_param(i).parameter_name ||
1203 ',' || p_param(i).parameter_value ||
1204 ',' || p_param(i).parameter_id ||
1205 ',' || p_param(i).dimension ||
1206 ',' || p_param(i).period_date ||
1207 '"
1208 ';
1209 end loop;
1210 l_stmt := l_stmt || '*/';
1211 return l_stmt;
1212 end dump_parameters;
1213
1214 function dump_binds
1215 ( p_custom_output in bis_query_attributes_tbl)
1216 return varchar2
1217 IS
1218 l_stmt varchar2(10000);
1219 BEGIN
1220 l_stmt := '
1221 /*
1222 ';
1223 for i in 1..p_custom_output.count loop
1224 l_stmt := l_stmt || '"' || p_custom_output(i).attribute_name ||
1225 ',' || p_custom_output(i).attribute_value ||
1226 ',' || p_custom_output(i).attribute_type ||
1227 ',' || p_custom_output(i).attribute_data_type ||
1228 '"
1229 ';
1230 end loop;
1231 l_stmt := l_stmt || '*/';
1232 return l_stmt;
1233 END;
1234
1235
1236 procedure override_order_by
1237 ( p_view_by in varchar2
1238 , p_param in bis_pmv_page_parameter_tbl
1239 , p_stmt in out nocopy varchar2
1240 )
1241 is
1242 l_orderby varchar2(1000);
1243 begin
1244
1245 if p_view_by = g_SEVERITY or
1246 p_view_by = g_AGING then
1247
1248 for i in 1..p_param.count loop
1249 if p_param(i).parameter_name = 'ORDERBY' then
1250 l_orderby := p_param(i).parameter_value;
1251 exit;
1252 end if;
1253 end loop;
1254
1255 if l_orderby like '%VIEWBY%' then
1256 if l_orderby like '% DESC%' then
1257 l_orderby := 'ORDER BY ' || case p_view_by
1258 when g_SEVERITY then
1259 'v.importance_level'
1260 else
1261 'v.id'
1262 end
1263 || ' DESC, NLSSORT(VIEWBY,''NLS_SORT=BINARY'') DESC';
1264 else
1265 l_orderby := 'ORDER BY ' || case p_view_by
1266 when g_SEVERITY then
1267 'v.importance_level'
1268 else
1269 'v.id'
1270 end
1271 || ' ASC, NLSSORT(VIEWBY,''NLS_SORT=BINARY'') ASC';
1272 end if;
1273 p_stmt := replace(p_stmt,'&ORDER_BY_CLAUSE',l_orderby);
1274 end if;
1275
1276 end if;
1277
1278 end override_order_by;
1279
1280 function get_balance_fact
1281 ( p_mv in varchar2
1282 )
1283 return varchar2
1284 is
1285
1286 l_collection_date_inc date;
1287 l_collection_date_ini date;
1288 l_collection_date date;
1289 l_collection_name varchar2(50);
1290 l_mv_name varchar2(50);
1291 l_mv_date date;
1292
1293 begin
1294
1295 l_collection_date_inc := fnd_date.displaydt_to_date
1296 ( bis_collection_utilities.get_last_refresh_period('BIV_DBI_COLLECTION'));
1297
1298 l_collection_date_ini := fnd_date.displaydt_to_date
1299 ( bis_collection_utilities.get_last_refresh_period('BIV_DBI_COLLECT_INIT_BACKLOG'));
1300
1301 if l_collection_date_inc > l_collection_date_ini then
1302 l_collection_date := l_collection_date_inc;
1303 l_collection_name := 'BIV_DBI_COLLECTION';
1304 else
1305 l_collection_date := l_collection_date_ini;
1306 l_collection_name := 'BIV_DBI_COLLECT_INIT_BACKLOG';
1307 end if;
1308
1312
1309 return l_collection_name;
1310
1311 end get_balance_fact;
1313 function get_trace_file_name
1314 return varchar2
1315 is
1316
1317 l_trace_file_name varchar2(200);
1318
1319 begin
1320 select
1321 '/* ' ||
1322 lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc'
1323 || ' */'
1324 into l_trace_file_name
1325 from
1326 ( select
1327 p.spid
1328 from
1329 sys.v_$mystat m,
1330 sys.v_$session s,
1331 sys.v_$process p
1332 where
1333 m.statistic# = 1 and
1334 s.sid = m.sid and
1335 p.addr = s.paddr
1336 ) p,
1337 ( select
1338 t.instance
1339 from
1340 sys.v_$thread t,
1341 sys.v_$parameter v
1342 where
1343 v.name = 'thread' and
1344 (
1345 v.value = 0 or
1346 t.thread# = to_number(v.value)
1347 )
1348 ) i;
1349
1350 return l_trace_file_name;
1351
1352 end get_trace_file_name;
1353
1354 function drill_detail
1355 ( p_function_name in varchar2
1356 , p_bucket_number in number
1357 , p_bucket_name in varchar2
1358 , p_base_alias in varchar2
1359 ) return varchar2
1360 is
1361 l_base_function varchar2(500) := '
1362 , ''pFunctionName=' || p_function_name || '&pParamIds=Y' ||
1363 '&BUCKET_AGING+SERVICE_DISTRIBUTION=';
1364 begin
1365
1366 if p_bucket_number <> 0 and
1367 p_bucket_name is null then
1368 return null;
1369 end if;
1370
1371 return '
1372 , ''pFunctionName=' || p_function_name ||
1373 case
1374 when p_bucket_number = 0 then
1375 null
1376 else
1377 '&SERVICE_DISTRIBUTION=' || p_bucket_number
1378 end ||
1379 '&VIEW_BY_NAME=VIEW_BY_ID' ||
1380 '&pParamIds=Y'' ' ||
1381 case
1382 when p_bucket_number = 0 then
1383 p_base_alias
1384 else
1385 p_base_alias || '_B' || p_bucket_number
1386 end;
1387
1388 end drill_detail;
1389
1390 function bucket_detail_drill
1391 ( p_function_name in varchar2
1392 , p_bucket_rec in bis_bucket_pub.bis_bucket_rec_type
1393 , p_base_alias in varchar2
1394 ) return varchar2
1395 is
1396 begin
1397 return
1398 drill_detail(p_function_name, 1, p_bucket_rec.range1_name, p_base_alias) ||
1399 drill_detail(p_function_name, 2, p_bucket_rec.range2_name, p_base_alias) ||
1400 drill_detail(p_function_name, 3, p_bucket_rec.range3_name, p_base_alias) ||
1401 drill_detail(p_function_name, 4, p_bucket_rec.range4_name, p_base_alias) ||
1402 drill_detail(p_function_name, 5, p_bucket_rec.range5_name, p_base_alias) ||
1403 drill_detail(p_function_name, 6, p_bucket_rec.range6_name, p_base_alias) ||
1404 drill_detail(p_function_name, 7, p_bucket_rec.range7_name, p_base_alias) ||
1405 drill_detail(p_function_name, 8, p_bucket_rec.range8_name, p_base_alias) ||
1406 drill_detail(p_function_name, 9, p_bucket_rec.range9_name, p_base_alias) ||
1407 drill_detail(p_function_name, 10, p_bucket_rec.range10_name, p_base_alias);
1408 end bucket_detail_drill;
1409
1410 procedure get_detail_page_function
1411 ( x_function_name out nocopy varchar2
1412 , x_sr_id_parameter out nocopy varchar2
1413 )
1414 is
1415
1416 l_drill_option constant varchar2(10) := '11.5.10';
1417 -- note change the above to 11.5.9 to revert to
1418 -- 11.5.9 drill to SR Quick View
1419
1420 begin
1421
1422 if l_drill_option = '11.5.10' then
1423 -- 11.5.10 code ----
1424 x_function_name := 'CSZ_SR_UP_RO_FN' ||
1425 '&cszReadOnlySRPageMode=REGULARREADONLY' ||
1426 -- '&cszReadOnlySRRetURL=null' ||
1427 -- '&cszReadOnlySRRetLabel=.' ||
1428 '&OAPB=BIV_DBI_SR_BRAND';
1429
1430 -- note: we are providing empty parameter values for
1431 -- cszReadOnlySRRetURL and cszReadOnlySRRetLabel
1432 -- as PMV does not currently provide us with the ability
1433 -- to return both of these.
1434 -- the result is that no return link will be rendered on
1435 -- OA page.
1436
1437 x_sr_id_parameter := 'cszIncidentId';
1438 --
1439
1440 else
1441
1442 -- 11.5.9 code -----
1443 x_function_name := 'BIV_DBI_SR_DETAIL_OA';
1444 x_sr_id_parameter := 'pUpd=N&SR_ID';
1445 --
1446
1447 end if;
1448
1449 end get_detail_page_function;
1450
1451 procedure bind_yes_no
1452 ( p_yes in varchar2
1453 , p_no in varchar2
1454 , p_custom_output in out nocopy bis_query_attributes_tbl
1455 )
1456 is
1457
1458 cursor c_yes_no is
1459 -- the max function enables the to rows to be returned via a single fetch
1460 select
1461 max(decode(lookup_code,'Y',meaning,null))
1462 , max(decode(lookup_code,'N',meaning,null))
1463 from fnd_lookup_values
1464 where lookup_type = 'YES_NO'
1465 and view_application_id = 0
1466 and language = userenv('LANG');
1467
1468 l_yes varchar2(80);
1469 l_no varchar2(80);
1470
1471 l_custom_rec BIS_QUERY_ATTRIBUTES;
1472
1473 begin
1474
1475 open c_yes_no;
1476 fetch c_yes_no into l_yes, l_no;
1477 close c_yes_no;
1478
1479 if p_custom_output is null then
1480 p_custom_output := bis_query_attributes_tbl();
1481 end if;
1482
1483 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1484
1485 l_custom_rec.attribute_name := p_yes;
1486 l_custom_rec.attribute_value := l_yes;
1490 p_custom_output(p_custom_output.count) := l_custom_rec;
1487 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1488 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1489 p_custom_output.extend;
1491
1492 l_custom_rec.attribute_name := p_no;
1493 l_custom_rec.attribute_value := l_no;
1494 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1495 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1496 p_custom_output.extend;
1497 p_custom_output(p_custom_output.count) := l_custom_rec;
1498
1499 end bind_yes_no;
1500
1501 procedure bind_low_high
1502 ( p_param in bis_pmv_page_parameter_tbl
1503 , p_short_name in varchar2
1504 , p_low in varchar2
1505 , p_high in varchar2
1506 , p_custom_output in out nocopy bis_query_attributes_tbl
1507 )
1508 is
1509
1510 l_range_low number;
1511 l_range_high number;
1512
1513 l_range_id number;
1514 l_bucket_rec bis_bucket_pub.bis_bucket_rec_type;
1515 l_return_status varchar2(3);
1516 l_error_tbl bis_utilities_pub.error_tbl_type;
1517
1518 l_custom_rec BIS_QUERY_ATTRIBUTES;
1519
1520 begin
1521
1522 for i in 1..p_param.count loop
1523 if p_param(i).parameter_name = g_AGING then
1524 l_range_id := replace(p_param(i).parameter_id,'''',null);
1525 end if;
1526 end loop;
1527
1528 if l_range_id is null then
1529 return;
1530 end if;
1531
1532 bis_bucket_pub.retrieve_bis_bucket
1533 ( p_short_name => p_short_name
1534 , x_bis_bucket_rec => l_bucket_rec
1535 , x_return_status => l_return_status
1536 , x_error_tbl => l_error_tbl
1537 );
1538
1539 if l_return_status = 'S' then
1540
1541 if l_range_id = 1 then
1542 l_range_low := l_bucket_rec.range1_low;
1543 l_range_high := l_bucket_rec.range1_high;
1544 elsif l_range_id = 2 then
1545 l_range_low := l_bucket_rec.range2_low;
1546 l_range_high := l_bucket_rec.range2_high;
1547 elsif l_range_id = 3 then
1548 l_range_low := l_bucket_rec.range3_low;
1549 l_range_high := l_bucket_rec.range3_high;
1550 elsif l_range_id = 4 then
1551 l_range_low := l_bucket_rec.range4_low;
1552 l_range_high := l_bucket_rec.range4_high;
1553 elsif l_range_id = 5 then
1554 l_range_low := l_bucket_rec.range5_low;
1555 l_range_high := l_bucket_rec.range5_high;
1556 elsif l_range_id = 6 then
1557 l_range_low := l_bucket_rec.range6_low;
1558 l_range_high := l_bucket_rec.range6_high;
1559 elsif l_range_id = 7 then
1560 l_range_low := l_bucket_rec.range7_low;
1561 l_range_high := l_bucket_rec.range7_high;
1562 elsif l_range_id = 8 then
1563 l_range_low := l_bucket_rec.range8_low;
1564 l_range_high := l_bucket_rec.range8_high;
1565 elsif l_range_id = 9 then
1566 l_range_low := l_bucket_rec.range9_low;
1567 l_range_high := l_bucket_rec.range9_high;
1568 elsif l_range_id = 10 then
1569 l_range_low := l_bucket_rec.range10_low;
1570 l_range_high := l_bucket_rec.range10_high;
1571 end if;
1572 end if;
1573
1574 if p_custom_output is null then
1575 p_custom_output := bis_query_attributes_tbl();
1576 end if;
1577
1578 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1579
1580 l_custom_rec.attribute_name := p_low;
1581 l_custom_rec.attribute_value := l_range_low;
1582 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1583 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1584 p_custom_output.extend;
1585 p_custom_output(p_custom_output.count) := l_custom_rec;
1586
1587 l_custom_rec.attribute_name := p_high;
1588 l_custom_rec.attribute_value := l_range_high;
1589 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1590 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1591 p_custom_output.extend;
1592 p_custom_output(p_custom_output.count) := l_custom_rec;
1593
1594 end bind_low_high;
1595
1596 procedure process_parameters
1597 ( p_param in bis_pmv_page_parameter_tbl
1598 , p_report_type in varchar2 -- 'BACKLOG_DETAIL', 'CLOSED_DETAIL', 'RESOLVED_DETAIL'
1599 , x_where_clause out nocopy varchar2
1600 , x_xtd out nocopy varchar2
1601 , x_mv out nocopy varchar2
1602 , x_join_from out nocopy varchar2
1603 , x_join_where out nocopy varchar2
1604 , x_join_tbl out nocopy poa_DBI_UTIL_PKG.poa_dbi_join_tbl
1605 , x_as_of_date out nocopy date
1606 )
1607 is
1608
1609 l_view_by varchar2(500);
1610 l_view_by_col_name varchar2(500);
1611 l_comparison_type varchar2(100);
1612 l_xtd varchar2(100);
1613
1614 begin
1615 process_parameters
1616 ( p_param => p_param
1617 , p_report_type => p_report_type
1618 , p_trend => 'N'
1619 , x_view_by => l_view_by -- ignore
1620 , x_view_by_col_name => l_view_by_col_name -- ignore
1621 , x_comparison_type => l_comparison_type -- ignore
1622 , x_xtd => x_xtd -- ignore
1623 , x_where_clause => x_where_clause
1624 , x_mv => x_mv
1625 , x_join_tbl => x_join_tbl
1626 , x_as_of_date => x_as_of_date
1627 );
1628
1629 get_detail_join_info
1630 ( p_report_type => p_report_type
1631 , x_join_from => x_join_from
1632 , x_join_where => x_join_where
1633 );
1634
1635 end process_parameters;
1636
1637 function get_order_by
1638 ( p_param in bis_pmv_page_parameter_tbl )
1639 return varchar2
1640 is
1641
1642 begin
1643 for i in 1..p_param.count loop
1647 end loop;
1644 if p_param(i).parameter_name = 'ORDERBY' then
1645 return p_param(i).parameter_value;
1646 end if;
1648 return null;
1649 end get_order_by;
1650
1651 procedure bind_age_dates
1652 ( p_param in bis_pmv_page_parameter_tbl
1653 , p_current_name in varchar2
1654 , p_prior_name in varchar2
1655 , p_custom_output in out nocopy bis_query_attributes_tbl
1656 )
1657 is
1658
1659 l_period_type varchar2(200);
1660 l_comparison_type varchar2(200);
1661 l_current_date date;
1662 l_current_date_time number;
1663 l_prior_date date;
1664
1665 l_custom_rec BIS_QUERY_ATTRIBUTES;
1666
1667 begin
1668
1669 for i in 1..p_param.count loop
1670 if p_param(i).parameter_name = 'PERIOD_TYPE' then
1671 l_period_type := p_param(i).parameter_value;
1672 elsif p_param(i).parameter_name = 'TIME_COMPARISON_TYPE' then
1673 l_comparison_type := p_param(i).parameter_value;
1674 end if;
1675 if l_period_type is not null and
1676 l_comparison_type is not null then
1677 exit;
1678 end if;
1679 end loop;
1680
1681 select max(report_date)
1682 into l_current_date
1683 from biv_dbi_backlog_age_dates;
1684
1685 l_current_date_time := l_current_date - trunc(l_current_date);
1686 l_current_date := trunc(l_current_date);
1687
1688 if l_comparison_type = 'YEARLY' then
1689 l_prior_date := add_months(l_current_date,-12);
1690 else
1691 l_prior_date := l_current_date - case l_period_type
1692 when 'FII_ROLLING_WEEK' then 7
1693 when 'FII_ROLLING_MONTH' then 30
1694 when 'FII_ROLLING_QTR' then 90
1695 when 'FII_ROLLING_YEAR' then 365
1696 else 0 -- catchall
1697 end;
1698 end if;
1699
1700 if p_custom_output is null then
1701 p_custom_output := bis_query_attributes_tbl();
1702 end if;
1703
1704 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
1705
1706 l_custom_rec.attribute_name := p_current_name;
1707 l_custom_rec.attribute_value := to_char(l_current_date,'dd/mm/yyyy');
1708 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1709 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.date_bind;
1710 p_custom_output.extend;
1711 p_custom_output(p_custom_output.count) := l_custom_rec;
1712
1713 l_custom_rec.attribute_name := '&AGE_CURRENT_ASOF_DATE_TIME';
1714 l_custom_rec.attribute_value := l_current_date_time;
1715 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1716 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
1717 p_custom_output.extend;
1718 p_custom_output(p_custom_output.count) := l_custom_rec;
1719
1720 l_custom_rec.attribute_name := p_prior_name;
1721 l_custom_rec.attribute_value := to_char(l_prior_date,'dd/mm/yyyy');
1722 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1723 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.date_bind;
1724 p_custom_output.extend;
1725 p_custom_output(p_custom_output.count) := l_custom_rec;
1726
1727 end bind_age_dates;
1728
1729 function get_grp_id
1730 ( p_bmap in number )
1731 return number
1732 is
1733 l_grp_id number;
1734 begin
1735 case
1736 when bitand(p_bmap,g_RESOLUTION_BMAP) = g_RESOLUTION_BMAP and
1737 not ( bitand(p_bmap,g_CUSTOMER_BMAP) = g_CUSTOMER_BMAP or
1738 bitand(p_bmap,g_CHANNEL_BMAP) = g_CHANNEL_BMAP or
1739 bitand(p_bmap,g_STATUS_BMAP) = g_STATUS_BMAP or
1740 bitand(p_bmap,g_ASSIGNMENT_BMAP) = g_ASSIGNMENT_BMAP or
1741 bitand(p_bmap,g_PRODUCT_BMAP) = g_PRODUCT_BMAP ) then
1742 l_grp_id := 6;
1743 when bitand(p_bmap,g_CHANNEL_BMAP) = g_CHANNEL_BMAP and
1744 not ( bitand(p_bmap,g_CUSTOMER_BMAP) = g_CUSTOMER_BMAP or
1745 bitand(p_bmap,g_RESOLUTION_BMAP) = g_RESOLUTION_BMAP or
1746 bitand(p_bmap,g_STATUS_BMAP) = g_STATUS_BMAP or
1747 bitand(p_bmap,g_ASSIGNMENT_BMAP) = g_ASSIGNMENT_BMAP or
1748 bitand(p_bmap,g_PRODUCT_BMAP) = g_PRODUCT_BMAP ) then
1749 l_grp_id := 5;
1750 when bitand(p_bmap,g_STATUS_BMAP) = g_STATUS_BMAP and
1751 not ( bitand(p_bmap,g_CUSTOMER_BMAP) = g_CUSTOMER_BMAP or
1752 bitand(p_bmap,g_RESOLUTION_BMAP) = g_RESOLUTION_BMAP or
1753 bitand(p_bmap,g_CHANNEL_BMAP) = g_CHANNEL_BMAP or
1754 bitand(p_bmap,g_ASSIGNMENT_BMAP) = g_ASSIGNMENT_BMAP or
1755 bitand(p_bmap,g_PRODUCT_BMAP) = g_PRODUCT_BMAP ) then
1756 l_grp_id := 4;
1757 when bitand(p_bmap,g_ASSIGNMENT_BMAP) = g_ASSIGNMENT_BMAP and
1758 not ( bitand(p_bmap,g_CUSTOMER_BMAP) = g_CUSTOMER_BMAP or
1759 bitand(p_bmap,g_RESOLUTION_BMAP) = g_RESOLUTION_BMAP or
1760 bitand(p_bmap,g_CHANNEL_BMAP) = g_CHANNEL_BMAP or
1761 bitand(p_bmap,g_STATUS_BMAP) = g_STATUS_BMAP or
1762 bitand(p_bmap,g_PRODUCT_BMAP) = g_PRODUCT_BMAP ) then
1763 l_grp_id := 3;
1764 when bitand(p_bmap,g_CUSTOMER_BMAP) = g_CUSTOMER_BMAP and
1765 not ( bitand(p_bmap,g_STATUS_BMAP) = g_STATUS_BMAP or
1766 bitand(p_bmap,g_RESOLUTION_BMAP) = g_RESOLUTION_BMAP or
1767 bitand(p_bmap,g_CHANNEL_BMAP) = g_CHANNEL_BMAP or
1768 bitand(p_bmap,g_ASSIGNMENT_BMAP) = g_ASSIGNMENT_BMAP or
1769 bitand(p_bmap,g_PRODUCT_BMAP) = g_PRODUCT_BMAP ) then
1770 l_grp_id := 2;
1771 when bitand(p_bmap,g_PRODUCT_BMAP) = g_PRODUCT_BMAP and
1772 not ( bitand(p_bmap,g_STATUS_BMAP) = g_STATUS_BMAP or
1773 bitand(p_bmap,g_RESOLUTION_BMAP) = g_RESOLUTION_BMAP or
1777 l_grp_id := 1;
1774 bitand(p_bmap,g_CHANNEL_BMAP) = g_CHANNEL_BMAP or
1775 bitand(p_bmap,g_ASSIGNMENT_BMAP) = g_ASSIGNMENT_BMAP or
1776 bitand(p_bmap,g_CUSTOMER_BMAP) = g_CUSTOMER_BMAP ) then
1778 else
1779 l_grp_id := 0;
1780 end case;
1781 return l_grp_id;
1782 end get_grp_id;
1783
1784 end biv_dbi_tmpl_util;