1 PACKAGE BODY BIX_PMV_EMC_EVAG_PRTLT_PKG AS
2 /*$Header: bixevagp.plb 120.0 2005/05/25 17:17:44 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_previous_report_start_date DATE;
17 l_current_report_start_date DATE;
18 l_previous_as_of_date DATE;
19 l_period_type_id NUMBER;
20 l_time_id_column VARCHAR2(1000);
21 l_custom_rec BIS_QUERY_ATTRIBUTES := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
22 l_classification VARCHAR2(32000);
23 l_where_clause VARCHAR2(32000);
24 l_view_by varchar2(1000);
25 l_row_type varchar2(10) := 'AC';
26
27 BEGIN
28 --
29 --Initialize p_custom_output
30 --
31 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
32
33 bix_pmv_dbi_utl_pkg.get_emc_page_params (p_page_parameter_tbl,
34 l_as_of_date,
35 l_period_type,
36 l_record_type_id,
37 l_comp_type,
38 l_account,
39 l_classification,
40 l_view_by
41 );
42
43
44 -- If the account is not 'All'
45
46 IF l_account IS NOT NULL THEN
47 l_where_clause := 'AND email_account_id IN (:l_account) ';
48 END IF;
49
50
51 IF l_classification IS NOT NULL THEN
52 l_where_clause := l_where_clause || ' AND email_classification_id IN (:l_classification) ';
53 END IF;
54
55
56 IF ( l_comp_type = 'YEARLY' AND l_period_type <> 'FII_TIME_ENT_YEAR' )
57 THEN
58 --
59 --If it enters here it means the comparison is for Week, Month or Quarter
60 --and it is a Year over Year comparison.
61 --
62 l_sqltext := '
63 SELECT fii2.name VIEWBY,
64 nvl(sum(CURR_RCVD),0) BIX_EMC_RCVD,
65 sum(CURR_REPLD) BIX_EMC_REPLD
66 FROM
67 ( SELECT fii604.sequence SEQUENCE,
68 SUM( CASE when
69 (
70 fii604.start_date between &BIS_CURRENT_REPORT_START_DATE
71 and &BIS_CURRENT_ASOF_DATE
72 and cal.report_date = least(fii604.end_date,&BIS_CURRENT_ASOF_DATE)
73 )
74 then
75 EMAILS_OFFERED_IN_PERIOD
76 else
77 NULL
78 end
79 ) CURR_RCVD,
80 SUM( CASE when
81 (
82 fii604.start_date between &BIS_CURRENT_REPORT_START_DATE
83 and &BIS_CURRENT_ASOF_DATE
84 and cal.report_date = least(fii604.end_date,&BIS_CURRENT_ASOF_DATE)
85 )
86 then
87 NVL(EMAILS_REPLIED_IN_PERIOD ,0) + NVL(EMAILS_AUTO_REPLIED_IN_PERIOD,0)
88 else
89 NULL
90 end
91 ) CURR_REPLD
92 FROM '||l_period_type||' fii604,
93 bix_email_details_mv eml,
94 fii_time_rpt_struct cal
95 WHERE eml.time_id = cal.time_id
96 AND eml.row_type = :l_row_type
97 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id
98 AND fii604.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND
99 &BIS_CURRENT_ASOF_DATE
100 AND cal.report_date = least(fii604.end_date, &BIS_CURRENT_ASOF_DATE)
101 AND cal.period_type_id = eml.period_type_id ';
102 l_sqltext := l_sqltext || l_where_clause || ' GROUP BY fii604.sequence
103 ) summ, '
104 ||l_period_type||' fii2
105 WHERE fii2.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
106 AND &BIS_CURRENT_ASOF_DATE
107 AND fii2.sequence = summ.sequence (+)
108 GROUP BY fii2.name, fii2.start_date, summ.sequence
109 ORDER BY fii2.start_date ';
110
111 ELSE
112 --
113 --If it reaches here it means it is either a Sequential comparison for
114 --week, month or quarter OR it is a YEAR period type. For YEAR period type
115 --it does not matter whether it is a Y/Y comparison or a Sequential comparison
116 --as both will be treated the same.
117 --
118 l_sqltext := '
119 SELECT fii604.name VIEWBY,
120 nvl(sum(CURR_RCVD),0) BIX_EMC_RCVD,
121 sum(CURR_REPLD) BIX_EMC_REPLD
122 FROM
123 ( SELECT fii1.name NAME,
124 sum(EMAILS_OFFERED_IN_PERIOD) CURR_RCVD,
125 NVL(sum(EMAILS_REPLIED_IN_PERIOD),0) + NVL(SUM(EMAILS_AUTO_REPLIED_IN_PERIOD),0) CURR_REPLD
126 FROM '||l_period_type||' fii1,
127 bix_email_details_mv eml,
128 fii_time_rpt_struct cal
129 WHERE eml.time_id = cal.time_id
130 AND eml.row_type = :l_row_type
131 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id
132 AND fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND
133 &BIS_CURRENT_ASOF_DATE
134 AND cal.report_date = least(&BIS_CURRENT_ASOF_DATE,fii1.end_date)
135 AND cal.period_type_id = eml.period_type_id ';
136 l_sqltext := l_sqltext || l_where_clause || ' GROUP BY fii1.name
137 ) curr, ' ||
138 l_period_type || ' fii604
139 WHERE fii604.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
140 AND &BIS_CURRENT_ASOF_DATE
141 AND fii604.name = curr.name (+)
142 GROUP BY fii604.name,fii604.start_date
143 ORDER BY fii604.start_date
144 ';
145
146 END IF;
147
148 p_custom_sql := l_sqltext;
149
150 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
151 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
152 l_custom_rec.attribute_value := 'TIME+'||l_period_type;
153
154 p_custom_output.EXTEND();
155 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
156
157 IF ( l_account IS NOT NULL) THEN
158 l_custom_rec.attribute_name := ':l_account' ;
159 l_custom_rec.attribute_value:= l_account;
160 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
161 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
162
163 p_custom_output.Extend();
164 p_custom_output(p_custom_output.count) := l_custom_rec;
165 END IF;
166
167 IF ( l_classification IS NOT NULL) THEN
168 l_custom_rec.attribute_name := ':l_classification' ;
169 l_custom_rec.attribute_value:= l_classification;
170 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
171 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
172
173 p_custom_output.Extend();
174 p_custom_output(p_custom_output.count) := l_custom_rec;
175 END IF;
176
177 l_custom_rec.attribute_name := ':l_row_type' ;
178 l_custom_rec.attribute_value:= l_row_type;
179 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
180 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
181
182 p_custom_output.Extend();
183 p_custom_output(p_custom_output.count) := l_custom_rec;
184
185 EXCEPTION
186 WHEN OTHERS THEN
187 NULL;
188 END GET_SQL;
189 END BIX_PMV_EMC_EVAG_PRTLT_PKG;
[Home] [Help]