[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_EMC_ERPT_PRTLT_PKG
Source
1 PACKAGE BODY BIX_PMV_EMC_ERPT_PRTLT_PKG AS
2 /*$Header: bixerptp.plb 120.0 2005/05/25 17:15:29 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_as_of_date DATE;
11 l_period_type varchar2(2000);
12 l_record_type_id NUMBER;
13 l_comp_type varchar2(2000);
14 l_account varchar2(32000);
15 l_sql_errm varchar2(32000);
16 l_period_from DATE;
17 l_period_to DATE;
18 l_where_clause varchar2(32000);
19 l_other_account VARCHAR2(1000);
20 l_start_date DATE;
21 l_end_date DATE;
22 l_max_collect_date VARCHAR2(30);
23 l_period_start_date DATE;
24 l_agent_cost NUMBER;
25 l_application_id NUMBER := 680;
26 l_classification VARCHAR2(32000);
27 l_view_by varchar2(1000);
28 l_row_type varchar2(10) := 'AC';
29
30 l_custom_rec BIS_QUERY_ATTRIBUTES := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
31
32 BEGIN
33
34 /*IF (FND_PROFILE.DEFINED('BIX_DM_AGENT_COST')) THEN
35 l_agent_cost := TO_NUMBER(FND_PROFILE.VALUE('BIX_DM_AGENT_COST'));
36 ELSE
37 l_agent_cost := 0;
38 END IF;
39 */
40 --
41 --Initialize p_custom_output
42 --
43 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
44
45 bix_pmv_dbi_utl_pkg.get_emc_page_params (p_page_parameter_tbl,
46 l_as_of_date,
47 l_period_type,
48 l_record_type_id,
49 l_comp_type,
50 l_account,
51 l_classification,
52 l_view_by
53 );
54
55
56 -- If the account is not 'All'
57
58 IF l_account IS NOT NULL THEN
59 l_where_clause := 'AND email_account_id IN (:l_account) ';
60 END IF;
61
62
63 IF l_classification IS NOT NULL THEN
64 l_where_clause := l_where_clause || ' AND email_classification_id IN (:l_classification) ';
65 END IF;
66
67
68 l_other_account := FND_MESSAGE.GET_STRING('BIX','BIX_PMV_ALLACCT');
69
70 IF l_other_account IS NULL OR l_other_account = 'BIX_PMV_ALLACCT'
71 THEN
72 l_other_account := 'Others';
73 END IF;
74
75
76 l_sqltext := '
77 SELECT DECODE(greatest(RANKING,10),10,account.value,:l_other_account) BIX_EMC_ACCOUNT,
78 round(nvl(SUM(MSGSGOAL),0)*100/decode(SUM(TOT_REPLD),0,NULL,SUM(TOT_REPLD)),1) BIX_EMC_MSGSGOAL,
79 nvl(SUM(SUM(MSGSGOAL)) over(),0)*100/
80 decode(SUM(SUM(TOT_REPLD)) over(),0,NULL,sum(SUM(TOT_REPLD)) over()) BIX_PMV_TOTAL1,
81 round(nvl(sum(AUTO_REPLD),0)*100/
82 decode(SUM(TOT_REPLD),0,NULL,SUM(TOT_REPLD)),1) BIX_EMC_AUTO_RPLD_RATE,
83 round(nvl(sum(sum(AUTO_REPLD)) over(),0)*100/
84 decode(SUM(SUM(TOT_REPLD)) OVER(),0,NULL,SUM(SUM(TOT_REPLD)) OVER()),1) BIX_PMV_TOTAL6,
85 ROUND(NVL(SUM(TRFD),0)*100/DECODE(SUM(RESOLVED),0,NULL,SUM(RESOLVED)),1) BIX_EMC_TRANRATIO,
86 ROUND(NVL(SUM(SUM(TRFD)) OVER(),0)*100/
87 DECODE(SUM(SUM(RESOLVED)) OVER(),0,NULL,SUM(SUM(RESOLVED)) OVER()),1) BIX_PMV_TOTAL7,
88 round(nvl(sum(CRTIME),0)/(3600*decode(SUM(TOT_REPLD),0,NULL,SUM(TOT_REPLD))),1) BIX_EMC_CRTIME,
89 round(nvl(sum(sum(CRTIME)) over(),0)/
90 (3600*decode(SUM(SUM(TOT_REPLD)) over(),0,NULL,SUM(SUM(TOT_REPLD)) over())),1) BIX_PMV_TOTAL3,
91 round(nvl(sum(ARTIME),0)/(3600*decode(SUM(REPLD),0,NULL,SUM(REPLD))),1) BIX_EMC_ARTIME,
92 round(nvl(SUM(SUM(ARTIME)) over(),0)/(3600*decode(
93 SUM(SUM(REPLD)) over(),0,NULL,SUM(SUM(REPLD)) over())),1) BIX_PMV_TOTAL4,
94 nvl(SUM(ONEDONE),0)*100/decode(SUM(THREADS),0,NULL,SUM(THREADS)) BIX_EMC_ONE_DONE,
95 nvl(SUM(SUM(ONEDONE)) over(),0)*100/
96 decode(SUM(SUM(THREADS)) over(),0,NULL,SUM(SUM(THREADS)) over()) BIX_PMV_TOTAL5,
97 --Start 001. Changes for adding columns for delete rate and its Grand Total
98 (NVL(SUM(curr_deleted),0)+ NVL(SUM(curr_auto_deleted),0))/ DECODE(SUM(curr_completed),0,NULL,SUM(curr_completed))*100 BIX_EMC_DELETE_RATE,
99 (NVL(SUM(SUM(curr_deleted)) OVER(),0) + NVL(SUM(SUM(curr_auto_deleted)) OVER(),0) )/
100 DECODE(SUM(SUM(curr_completed)) OVER(),0,NULL,SUM(SUM(curr_completed)) OVER())*100 BIX_EMC_DELETE_TOTAL
101 --End 001. Changes for adding columns for delete rate and its Grand Total
102 FROM (
103 SELECT email_account_id ACCOUNT_ID,
104 sum(EMAILS_RPLD_BY_GOAL_IN_PERIOD) MSGSGOAL,
105 sum(EMAILS_REPLIED_IN_PERIOD) REPLD,
106 sum(EMAILS_AUTO_REPLIED_IN_PERIOD) AUTO_REPLD,
107 NVL(SUM(EMAILS_REPLIED_IN_PERIOD),0) + NVL(SUM(EMAILS_AUTO_REPLIED_IN_PERIOD),0) TOT_REPLD,
108 sum(EMAILS_RSL_AND_TRFD_IN_PERIOD) TRFD,
109 NVL(SUM(EMAILS_REPLIED_IN_PERIOD),0) + NVL(SUM(EMAILS_DELETED_IN_PERIOD),0) RESOLVED,
110 sum(EMAIL_RESP_TIME_IN_PERIOD) CRTIME,
111 sum(AGENT_RESP_TIME_IN_PERIOD) ARTIME,
112 sum(ONE_RSLN_IN_PERIOD) ONEDONE,
113 sum(INTERACTION_THREADS_IN_PERIOD) THREADS,
114 sum(sum(EMAILS_REPLIED_IN_PERIOD)) over() TOTALREPLD,
115 RANK() OVER (ORDER BY nvl(sum(EMAILS_RPLD_BY_GOAL_IN_PERIOD)/
116 DECODE(NVL(sum(EMAILS_REPLIED_IN_PERIOD),0) + NVL(SUM(EMAILS_AUTO_REPLIED_IN_PERIOD),0),0,
117 NULL,
118 NVL(sum(EMAILS_REPLIED_IN_PERIOD),0) + NVL(SUM(EMAILS_AUTO_REPLIED_IN_PERIOD),0)),0) DESC,
119 account.value
120 ) RANKING,
121 --Start 001. Changes for adding columns for delete rate and its Grand Total
122 SUM(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_DELETED_IN_PERIOD)) CURR_DELETED,
123 SUM(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_AUTO_DELETED_IN_PERIOD)) CURR_AUTO_DELETED,
124 NVL(EMAILS_REPLIED_IN_PERIOD,0) + NVL(EMAILS_DELETED_IN_PERIOD,0) + NVL(EMAILS_AUTO_UPTD_SR_IN_PERIOD,0) +
125 NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0) + NVL(EMAILS_AUTO_DELETED_IN_PERIOD,0) + NVL(EMAILS_AUTO_RESOLVED_IN_PERIOD,0))) curr_completed
126 --End 001. Changes for adding columns for delete rate and its Grand Total
127 FROM bix_email_details_mv fact,
128 fii_time_rpt_struct cal, bix_email_accounts_v account
129 WHERE fact.time_id = cal.time_id
130 AND fact.row_type = :l_row_type
131 AND fact.email_account_id = account.id
132 AND fact.period_type_id = cal.period_type_id
133 AND cal.report_date = &BIS_CURRENT_ASOF_DATE
134 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id ';
135 l_sqltext := l_sqltext || l_where_clause || '
136 GROUP BY email_account_id, account.value
137 ) email,
138 bix_email_accounts_v account
139 WHERE email.account_id = account.id
140 GROUP BY DECODE(greatest(RANKING,10),10,RANKING,11),
141 DECODE(greatest(RANKING,10),10,account.value,:l_other_account)
142 ORDER BY DECODE(greatest(RANKING,10),10,RANKING,11),
143 DECODE(greatest(RANKING,10),10,account.value,:l_other_account) ';
144
145 p_custom_sql := l_sqltext;
146
147
148 l_custom_rec.attribute_name := ':l_other_account' ;
149 l_custom_rec.attribute_value:= l_other_account;
150 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
151 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
152
153 p_custom_output.Extend();
154 p_custom_output(p_custom_output.count) := l_custom_rec;
155
156 l_custom_rec.attribute_name := ':l_application_id';
157 l_custom_rec.attribute_value:= l_application_id;
158 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
159 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
160
161 p_custom_output.EXTEND;
162 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
163
164 IF ( l_account IS NOT NULL) THEN
165 l_custom_rec.attribute_name := ':l_account' ;
166 l_custom_rec.attribute_value:= l_account;
167 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
168 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
169
170 p_custom_output.Extend();
171 p_custom_output(p_custom_output.count) := l_custom_rec;
172 END IF;
173
174 IF ( l_classification IS NOT NULL) THEN
175 l_custom_rec.attribute_name := ':l_classification' ;
176 l_custom_rec.attribute_value:= l_classification;
177 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
178 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
179
180 p_custom_output.Extend();
181 p_custom_output(p_custom_output.count) := l_custom_rec;
182 END IF;
183
184 l_custom_rec.attribute_name := ':l_row_type' ;
185 l_custom_rec.attribute_value:= l_row_type;
186 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
187 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
188
189 p_custom_output.Extend();
190 p_custom_output(p_custom_output.count) := l_custom_rec;
191
192 EXCEPTION
193 WHEN OTHERS THEN
194 --lsql_errm := SQLERRM;
195 NULL;
196 END GET_SQL;
197 END BIX_PMV_EMC_ERPT_PRTLT_PKG;