[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_EMC_ORR_RPT_PKG
Source
1 PACKAGE BODY BIX_PMV_EMC_ORR_RPT_PKG AS
2 /*$Header: bixeorrb.plb 120.0 2005/05/25 17:19:29 appldev noship $ */
3
4 PROCEDURE GET_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
5 p_sql_text 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_as_of_date DATE;
12 l_period_type varchar2(2000);
13 l_comp_type varchar2(2000);
14 l_sql_errm varchar2(32000);
15 l_agent_cost NUMBER;
16 l_custom_rec BIS_QUERY_ATTRIBUTES;
17 l_record_type_id NUMBER ;
18 l_account VARCHAR2(32000);
19 l_start_date DATE;
20 l_end_date DATE;
21 l_period_from DATE;
22 l_period_to DATE;
23 l_dummy_cust NUMBER;
24 l_max_collect_date VARCHAR2(100);
25 l_period_start_date DATE;
26 l_unident_string VARCHAR2(100);
27 l_application_id NUMBER := 680;
28 l_classification VARCHAR2(32000);
29 l_view_by varchar2(1000);
30 l_row_type varchar2(10) := 'ACORR';
31 l_unknown varchar2(1000);
32 l_outcome_filter NUMBER := -1;
33 l_subtotal varchar2(1000);
34 BEGIN
35
36 --
37 --Initialize p_custom_output
38 --
39
40 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
41 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
42
43 -- Get the parameters
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
59 l_unknown := FND_MESSAGE.GET_STRING('BIX','BIX_PMV_UNKNOWN');
60
61 IF l_unknown IS NULL OR l_unknown = 'BIX_PMV_UNKNOWN' THEN
62 l_unknown := 'Unknown';
63 END IF;
64
65
66 l_subtotal := FND_MESSAGE.GET_STRING('BIX','BIX_PMV_SUBTOTAL');
67
68 IF l_subtotal IS NULL OR l_subtotal = 'BIX_PMV_SUBTOTAL' THEN
69 l_subtotal := 'Subtotal';
70 END IF;
71
72
73 IF l_account IS NOT NULL THEN
74 l_where_clause := 'AND email_account_id IN (:l_account) ';
75 END IF;
76
77
78 IF l_classification IS NOT NULL THEN
79 l_where_clause := l_where_clause || ' AND email_classification_id IN (:l_classification) ';
80 END IF;
81 /*
82 l_sqltext :=
83 'SELECT ''Reply'' BIX_EMC_OUTCOME,
84 ''Email Sent'' BIX_EMC_RESULT,
85 ''General Inquiry'' BIX_EMC_REASON,
86 7390 BIX_EMC_COUNT,
87 7390 BIX_PMV_TOTAL1,
88 8.5 BIX_EMC_PERTOTAL1,
89 100 BIX_PMV_TOTAL2,
90 4 BIX_EMC_COUNTCHANGE,
91 4 BIX_PMV_TOTAL3
92 FROM DUAL';
93
94 p_sql_text := l_sqltext;
95
96 */
97
98 --
99 --Two grouping sets - one by O,R,R and one by just O - this is for the
100 --SUBTOTAL. Grouping set will produce a binary number of 0s if the column
101 --is present and binary of 1 if the column is not present.
102 --
103
104 -- Start 001. Changed the query to add up only the subtotals instead of both - the subtotals and individual values
105
106 l_sqltext :=
107 'SELECT * FROM(
108 SELECT
109 outcome.outcome_code BIX_EMC_OUTCOME,
110 result.result_code BIX_EMC_RESULT,
111 reason.reason_code BIX_EMC_REASON,
112 NVL(curr_count,0) BIX_EMC_COUNT,
113 NVL(SUM(curr_count) OVER(),0) BIX_PMV_TOTAL1,
114 NVL(curr_count,0) * 100/DECODE(curr_outcount,0,NULL,curr_outcount) BIX_EMC_PERTOTAL1,
115 (NVL(curr_count,0) * 100/DECODE(curr_outcount,0,NULL,curr_outcount)) -
116 (NVL(prev_count,0) * 100/DECODE(prev_outcount,0,NULL,prev_outcount)) BIX_EMC_COUNTCHANGE
117 FROM
118 (
119 SELECT outcome_id,
120 result_id,
121 reason_id,
122 grouping_id(outcome_id,result_id,reason_id) g_id,
123 SUM(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_ORR_COUNT_IN_PERIOD,NULL)) curr_count,
124 SUM(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_ORR_COUNT_IN_PERIOD,NULL)) prev_count,
125 sum(SUM(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_ORR_COUNT_IN_PERIOD,NULL)))
126 over (partition by outcome_id) curr_outcount,
127 sum(SUM(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_ORR_COUNT_IN_PERIOD,NULL)))
128 over (partition by outcome_id) prev_outcount
129 FROM bix_email_details_mv fact,
130 fii_time_rpt_struct cal
131 WHERE fact.time_id = cal.time_id
132 AND fact.row_type = :l_row_type
133 AND fact.period_type_id = cal.period_type_id
134 AND fact.outcome_id != :l_outcome_filter
135 AND cal.report_date IN ( &BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
136 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id
137 ' || l_where_clause ||
138 ' GROUP BY
139 outcome_id,result_id,reason_id
140 ) fact, jtf_ih_outcomes_vl outcome,jtf_ih_results_vl result,jtf_ih_reasons_vl reason
141 WHERE fact.outcome_id = outcome.outcome_id(+)
142 AND fact.result_id = result.result_id(+)
143 AND fact.reason_id = reason.reason_id(+)
144 ORDER BY outcome.outcome_code,g_id,result.result_code,reason.reason_code
145 ) --start 002
146 WHERE ABS(NVL(bix_emc_count,0))+ABS(NVL(bix_emc_countchange,0)) != 0 ';
147 --end 002
148
149 /******
150
151 l_sqltext :=
152 ' SELECT
153 DECODE(g_id,3,:l_subtotal,outcome.outcome_code) BIX_EMC_OUTCOME,
154 DECODE(g_id,3,NULL,NVL(result.result_code,:l_unknown)) BIX_EMC_RESULT,
155 DECODE(g_id,3,NULL,NVL(reason.reason_code,:l_unknown )) BIX_EMC_REASON,
156 NVL(curr_count,0) BIX_EMC_COUNT,
157 NVL(SUM(curr_count) OVER(),0) BIX_PMV_TOTAL1,
158 NVL(curr_count,0) * 100/DECODE(SUM(curr_count) OVER(),0,NULL,SUM(curr_count) OVER()) BIX_EMC_PERTOTAL1,
159 NVL(curr_count,0) * 100/DECODE(SUM(curr_count) OVER(),0,NULL,SUM(curr_count) OVER()) -
160 NVL(prev_count,0) * 100/DECODE(SUM(prev_count) OVER(),0,NULL,SUM(prev_count) OVER()) BIX_EMC_COUNTCHANGE
161 FROM
162 (
163 SELECT outcome_id,
164 result_id,
165 reason_id,
166 grouping_id(outcome_id,result_id,reason_id) g_id,
167 SUM(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_ORR_COUNT_IN_PERIOD,NULL)) curr_count,
168 SUM(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_ORR_COUNT_IN_PERIOD,NULL)) prev_count
169 FROM bix_email_details_mv fact,
170 fii_time_rpt_struct cal
171 WHERE fact.time_id = cal.time_id
172 AND fact.row_type = :l_row_type
173 AND fact.period_type_id = cal.period_type_id
174 AND fact.outcome_id != :l_outcome_filter
175 AND cal.report_date IN ( &BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
176 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id
177 ' || l_where_clause ||
178 ' GROUP BY
179 GROUPING SETS
180 (
181 ( outcome_id,result_id,reason_id),
182 ( outcome_id)
183 )
184 ) fact, jtf_ih_outcomes_vl outcome,jtf_ih_results_vl result,jtf_ih_reasons_vl reason
185 WHERE fact.outcome_id = outcome.outcome_id(+)
186 AND fact.result_id = result.result_id(+)
187 AND fact.reason_id = reason.reason_id(+) ';
188
189 ******/
190
191
192 --End 001
193
194
195
196 p_sql_text := l_sqltext;
197
198
199 -- insert Period Type ID bind variable
200
201 l_custom_rec.attribute_name := ':l_period_type_id' ;
202 l_custom_rec.attribute_value:= 1;
203 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
204 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
205
206 p_custom_output.Extend();
207 p_custom_output(p_custom_output.count) := l_custom_rec;
208
209 l_custom_rec.attribute_name := ':l_outcome_filter' ;
210 l_custom_rec.attribute_value:= l_outcome_filter;
211 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
212 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
213
214 p_custom_output.Extend();
215 p_custom_output(p_custom_output.count) := l_custom_rec;
216
217 -- Insert account Bind Variable
218
219 IF ( l_account IS NOT NULL) THEN
220 l_custom_rec.attribute_name := ':l_account' ;
221 l_custom_rec.attribute_value:= l_account;
222 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
223 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
224
225 p_custom_output.Extend();
226 p_custom_output(p_custom_output.count) := l_custom_rec;
227 END IF;
228
229
230 IF ( l_classification IS NOT NULL) THEN
231 l_custom_rec.attribute_name := ':l_classification' ;
232 l_custom_rec.attribute_value:= l_classification;
233 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
234 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
235
236 p_custom_output.Extend();
237 p_custom_output(p_custom_output.count) := l_custom_rec;
238 END IF;
239
240 l_custom_rec.attribute_name := ':l_row_type' ;
241 l_custom_rec.attribute_value:= l_row_type;
242 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
243 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
244
245 p_custom_output.Extend();
246 p_custom_output(p_custom_output.count) := l_custom_rec;
247
248
249 l_custom_rec.attribute_name := ':l_subtotal' ;
250 l_custom_rec.attribute_value:= l_subtotal;
251 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
252 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
253
254 p_custom_output.Extend();
255 p_custom_output(p_custom_output.count) := l_custom_rec;
256
257
258 l_custom_rec.attribute_name := ':l_unknown' ;
259 l_custom_rec.attribute_value:= l_unknown;
260 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
261 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
262
263 p_custom_output.Extend();
264 p_custom_output(p_custom_output.count) := l_custom_rec;
265
266
267 l_custom_rec.attribute_name := ':l_space' ;
268 l_custom_rec.attribute_value:= ' ';
269 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
270 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
271
272 p_custom_output.Extend();
273 p_custom_output(p_custom_output.count) := l_custom_rec;
274
275
276
277
278
279
280
281 EXCEPTION
282 WHEN OTHERS THEN
283 NULL;
284 END GET_SQL;
285 END BIX_PMV_EMC_ORR_RPT_PKG;