[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_EMC_EVAT_PRTLT_PKG
Source
1 PACKAGE BODY BIX_PMV_EMC_EVAT_PRTLT_PKG AS
2 /*$Header: bixevatp.plb 115.14 2003/11/22 01:46:30 djambula 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_classification VARCHAR2(32000);
25 l_view_by varchar2(1000);
26 l_row_type varchar2(10) := 'AC';
27
28 l_custom_rec BIS_QUERY_ATTRIBUTES := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
29
30 BEGIN
31
32 --
33 --Initialize p_custom_output
34 --
35 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
36
37 bix_pmv_dbi_utl_pkg.get_emc_page_params (p_page_parameter_tbl,
38 l_as_of_date,
39 l_period_type,
40 l_record_type_id,
41 l_comp_type,
42 l_account,
43 l_classification ,
44 l_view_by
45 );
46
47 -- If the account is not 'All'
48
49 IF l_account IS NOT NULL THEN
50 l_where_clause := 'AND email_account_id IN (:l_account) ';
51 END IF;
52
53
54 IF l_classification IS NOT NULL THEN
55 l_where_clause := l_where_clause || ' AND email_classification_id IN (:l_classification) ';
56 END IF;
57
58
59 l_period_start_date := BIX_PMV_DBI_UTL_PKG.period_start_date(l_as_of_date,l_period_type);
60
61 /* Get the MAX date for which data is collected in Email Summary table */
62
63 BIS_COLLECTION_UTILITIES.get_last_refresh_dates('BIX_EMAIL_DETAILS_F',
64 l_start_date,
65 l_end_date,
66 l_period_from,
67 l_period_to
68 );
69
70 --
71 --If the data is not collected till AS OF DATE
72 --then get the accumulated measures from the MAX collected DATE time bucket
73 --if the max collect date falls within the current period
74 --
75
76 IF l_period_to BETWEEN l_period_start_date AND l_as_of_date
77 THEN
78 l_max_collect_date := TO_CHAR(l_period_to,'DD/MM/YYYY');
79 ELSE
80 l_max_collect_date := TO_CHAR(l_as_of_date,'DD/MM/YYYY');
81 END IF;
82
83 l_other_account := FND_MESSAGE.GET_STRING('BIX','BIX_PMV_ALLACCT');
84
85 IF l_other_account IS NULL OR l_other_account = 'BIX_PMV_ALLACCT'
86 THEN
87 l_other_account := 'Others';
88 END IF;
89
90 l_sqltext := '
91 SELECT DECODE(greatest(RANKING,10),10,account.value,:l_other_account ) BIX_EMC_ACCOUNT,
92 nvl(sum(CURR_RCVD),0) BIX_EMC_RCVD,
93 nvl(SUM(SUM(CURR_RCVD)) over(),0) BIX_PMV_TOTAL1,
94 nvl(sum(curr_composed),0) BIX_EMC_COMPOSED,
95 nvl(SUM(sum(curr_composed)) OVER(),0) BIX_PMV_TOTAL2,
96 sum(CURR_REPLD) BIX_EMC_REPLD,
97 SUM(sum(CURR_REPLD)) over() BIX_PMV_TOTAL3,
98 sum(CURR_DEL) BIX_EMC_DELETED,
99 SUM(sum(CURR_DEL)) over() BIX_PMV_TOTAL4,
100 nvl(sum(curr_trfd) ,0) BIX_EMC_TRANOUT,
101 nvl(SUM(sum(curr_trfd)) OVER(),0) BIX_PMV_TOTAL6,
102 nvl(sum(CURR_BACKLOG),0) BIX_EMC_BACKLOG,
103 nvl(SUM(sum(CURR_BACKLOG)) over(),0) BIX_PMV_TOTAL5,
104 nvl(sum(CURR_SR),0) BIX_EMC_SR,
105 nvl(SUM(sum(CURR_SR)) over(),0) BIX_PMV_TOTAL8,
106 nvl(sum(curr_leads),0) BIX_EMC_LEADS,
107 nvl(SUM(sum(curr_leads)) OVER(),0) BIX_PMV_TOTAL9
108 FROM (
109 SELECT email_account_id ACCOUNT_ID,
110 sum(EMAILS_OFFERED_IN_PERIOD) CURR_RCVD,
111 sum(EMAILS_COMPOSED_IN_PERIOD) CURR_COMPOSED,
112 NVL(sum(EMAILS_REPLIED_IN_PERIOD),0) + NVL(SUM(EMAILS_AUTO_REPLIED_IN_PERIOD),0) CURR_REPLD,
113 NVL(sum(EMAILS_DELETED_IN_PERIOD),0) + NVL(SUM(EMAILS_AUTO_DELETED_IN_PERIOD),0) CURR_DEL,
114 sum(EMAILS_RSL_AND_TRFD_IN_PERIOD) CURR_TRFD,
115 sum(SR_CREATED_IN_PERIOD) CURR_SR,
116 sum(LEADS_CREATED_IN_PERIOD) CURR_LEADS,
117 RANK() OVER (ORDER BY sum(nvl(EMAILS_OFFERED_IN_PERIOD,0)) DESC, vl.value) RANKING
118 FROM bix_email_details_mv fact,
119 fii_time_rpt_struct cal, bix_email_accounts_v vl
120 WHERE fact.time_id = cal.time_id
121 AND fact.row_type = :l_row_type
122 AND fact.period_type_id = cal.period_type_id
123 AND cal.report_date = &BIS_CURRENT_ASOF_DATE
124 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id
125 AND vl.id = fact.email_account_id ';
126 l_sqltext := l_sqltext || l_where_clause || '
127 GROUP BY email_account_id, vl.value
128 ) summ,
129 (
130 SELECT email_account_id ACCOUNT_ID,
131 SUM(NVL(ACCUMULATED_OPEN_EMAILS,0) +
132 NVL(ACCUMULATED_EMAILS_IN_QUEUE,0)
133 ) CURR_BACKLOG
134 FROM bix_email_details_mv
135 WHERE time_id = to_char(:l_max_collect_date,''J'')
136 AND row_type = :l_row_type
137 AND period_type_id = :l_period_type_id ';
138 l_sqltext := l_sqltext || l_where_clause || '
139 GROUP BY email_account_id
140 ) accu,
141 bix_email_accounts_v account
142 WHERE summ.account_id = accu.account_id (+)
143 AND summ.account_id = account.id
144 GROUP BY DECODE(greatest(RANKING,10),10,account.value,:l_other_account),
145 DECODE(greatest(RANKING,10),10,RANKING,11)
146 ORDER BY DECODE(greatest(RANKING,10),10,RANKING,11) ';
147
148 p_custom_sql := l_sqltext;
149
150 p_custom_output.EXTEND();
151 l_custom_rec.attribute_name := ':l_max_collect_date';
152 l_custom_rec.attribute_value:= l_max_collect_date;
153 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
154 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
155 p_custom_output(p_custom_output.count) := l_custom_rec;
156
157 p_custom_output.EXTEND();
158 l_custom_rec.attribute_name := ':l_period_type_id' ;
159 l_custom_rec.attribute_value:= 1;
160 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
161 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
162 p_custom_output(p_custom_output.count) := l_custom_rec;
163
164 p_custom_output.EXTEND();
165 l_custom_rec.attribute_name := ':l_other_account' ;
166 l_custom_rec.attribute_value:= l_other_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 p_custom_output(p_custom_output.count) := l_custom_rec;
170
171 IF ( l_account IS NOT NULL) THEN
172 l_custom_rec.attribute_name := ':l_account' ;
173 l_custom_rec.attribute_value:= l_account;
174 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
175 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
176
177 p_custom_output.Extend();
178 p_custom_output(p_custom_output.count) := l_custom_rec;
179 END IF;
180
181 IF ( l_classification IS NOT NULL) THEN
182 l_custom_rec.attribute_name := ':l_classification' ;
183 l_custom_rec.attribute_value:= l_classification;
184 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
185 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
186
187 p_custom_output.Extend();
188 p_custom_output(p_custom_output.count) := l_custom_rec;
189 END IF;
190
191 l_custom_rec.attribute_name := ':l_row_type' ;
192 l_custom_rec.attribute_value:= l_row_type;
193 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
194 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
195
196 p_custom_output.Extend();
197 p_custom_output(p_custom_output.count) := l_custom_rec;
198
199 EXCEPTION
200 WHEN OTHERS THEN
201 --lsql_errm := SQLERRM;
202 NULL;
203 END GET_SQL;
204 END BIX_PMV_EMC_EVAT_PRTLT_PKG;