[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_AI_CBMTGR_PRTLT_PKG
Source
1 PACKAGE BODY BIX_PMV_AI_CBMTGR_PRTLT_PKG AS
2 /*$Header: bixicmtp.plb 120.0 2005/05/25 17:22:50 appldev noship $ */
3
4 PROCEDURE GET_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
5 p_custom_sql OUT NOCOPY VARCHAR2,
6 p_custom_output OUT NOCOPY bis_query_attributes_TBL
7 )
8 AS
9 l_sqltext VARCHAR2(32000) ;
10 l_where_clause VARCHAR2(1000) ;
11 l_mv VARCHAR2 (240);
12 l_view_by_select VARCHAR2(500) ;
13 l_comp_type VARCHAR2(500) ;
14 l_xtd VARCHAR2(500) ;
15 l_view_by VARCHAR2(120) ;
16 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
17 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
18 l_func_area CONSTANT VARCHAR2(5) := 'ICMTP';
19 l_mv_set CONSTANT VARCHAR2(3) := 'ITM';
20 l_version VARCHAR2(3):=NULL;
21 l_timetype CONSTANT VARCHAR2(3) := 'XTD';
22 l_filter_where VARCHAR2 (2000);
23
24 BEGIN
25
26 -- Get the parameters
27 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
28
29 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
30 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl ();
31
32
33 bix_pmv_dbi_utl_pkg.process_parameters
34 ( p_param => p_page_parameter_tbl
35 , p_trend => 'N'
36 , p_func_area => l_func_area
37 , p_version => l_version
38 , p_mv_set => l_mv_set
39 , p_where_clause => l_where_clause
40 , p_mv => l_mv
41 , p_join_tbl => l_join_tbl
42 , p_comp_type => l_comp_type
43 , p_xtd => l_xtd
44 , p_view_by_select => l_view_by_select
45 , p_view_by => l_view_by
46 );
47
48 -- Populate col table with regular columns
49
50 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
51 , p_col_name => 'CALL_CALLS_HANDLED_TOTAL'
52 , p_grand_total => 'N'
53 , p_alias_name => 'HANDLED'
54 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
55 , p_to_date_type => l_timetype
56 );
57
58 l_sqltext:= poa_dbi_template_pkg.status_sql (
59 p_fact_name => l_mv
60 , p_where_clause => l_where_clause
61 , p_filter_where => l_filter_where
62 , p_join_tables => l_join_tbl
63 , p_use_windowing => 'N'
64 , p_col_name => l_col_tbl
65 , p_use_grpid => 'N'
66 , p_paren_count => 3
67 , p_generate_viewby => 'N');
68
69 l_sqltext :=' SELECT decode(media_item_type, ''TELE_INB'',:l_inbound,''TELE_DIRECT'', :l_inbound,
70 ''TELE_MANUAL'', :l_dialed,''TELE_WEB_CALLBACK'', :l_webcall,''UNSOLICITED'', :l_unsolicited,
71 media_item_type) BIX_PMV_AI_MITYPE, nvl(sum(P_HANDLED),0) BIX_PMV_AI_PPER, nvl(sum(C_HANDLED),0) BIX_PMV_AI_CPER
72 from ((SELECT orderby orderby, name media_item_type,0 c_handled,0 p_handled FROM (
73 select 1 orderby, ''TELE_INB'' name from dual UNION ALL
74 select 2 orderby, ''TELE_DIRECT'' name from dual UNION ALL
75 select 3 orderby, ''TELE_WEB_CALLBACK''name from dual UNION ALL
76 select 4 orderby, ''TELE_MANUAL'' name from dual UNION ALL
77 select 5 orderby, ''UNSOLICITED'' name from dual ) types
78 )
79 UNION ALL (
80 select 0 orderby, media_item_type,c_handled, p_handled
81 from'||l_sqltext||
82 'GROUP BY decode(media_item_type, ''TELE_INB'',:l_inbound,''TELE_DIRECT'', :l_inbound,
83 ''TELE_MANUAL'', :l_dialed,''TELE_WEB_CALLBACK'', :l_webcall,''UNSOLICITED'', :l_unsolicited,
84 media_item_type)
85 ORDER BY SUM(ORDERBY)';
86
87
88 p_custom_sql:=l_sqltext;
89 bix_pmv_dbi_utl_pkg.get_bind_vars (p_custom_output,p_func_area => l_func_area);
90
91 /* l_sqltext := '
92 SELECT mediatype BIX_PMV_AI_MITYPE,
93 nvl(sum(pper),0) BIX_PMV_AI_PPER,
94 nvl(sum(cper),0) BIX_PMV_AI_CPER
95 FROM (
96 SELECT 0 orderby,
97 decode(mv.media_item_type, ''TELE_INB'',:l_inbound,
98 ''TELE_DIRECT'', :l_direct,
99 ''TELE_MANUAL'', :l_dialed,
100 ''TELE_WEB_CALLBACK'', :l_webcall,
101 ''UNSOLICITED'', :l_unsolicited,
102 mv.media_item_type) MEDIATYPE,
103 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
104 CALL_CALLS_OFFERED_TOTAL,0)) PPER,
105 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
106 CALL_CALLS_OFFERED_TOTAL,0)) CPER
107 FROM bix_ai_call_details_mv mv,
108 fii_time_rpt_struct cal
109 WHERE mv.time_id = cal.time_id
110 AND mv.row_type = :l_row_type
111 AND mv.period_type_id = cal.period_type_id
112 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) =
113 cal.record_type_id
114 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE,
115 &BIS_PREVIOUS_ASOF_DATE) ';
116 l_sqltext := l_sqltext || l_call_where_clause ||
117 '
118 GROUP BY mv.media_item_type
119 UNION ALL
120 SELECT orderby orderby, name MEDIATYPE,
121 0 PPER,
122 0 CPER
123 FROM (select 1 orderby, :l_inbound name from dual UNION ALL
124 select 2 orderby, :l_direct name from dual UNION ALL
125 select 3 orderby, :l_webcall name from dual UNION ALL
126 select 4 orderby, :l_manual name from dual UNION ALL
127 select 5 orderby, :l_unsolicited name from dual
128 ) types
129 ) GROUP BY mediatype ORDER BY sum(orderby) ' ;
130
131
132 */
133 EXCEPTION
134 WHEN OTHERS THEN
135 RAISE;
136 END GET_SQL;
137 END BIX_PMV_AI_CBMTGR_PRTLT_PKG;