[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_AO_OUTGR_PRTLT_PKG
Source
1 PACKAGE BODY BIX_PMV_AO_OUTGR_PRTLT_PKG AS
2 /*$Header: bixooutp.plb 115.2 2004/03/08 07:38:00 pubalasu 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_sql_errm varchar2(32000);
15 l_previous_report_start_date DATE;
16 l_current_report_start_date DATE;
17 l_previous_as_of_date DATE;
18 l_period_type_id NUMBER;
19 l_time_id_column VARCHAR2(1000);
20 l_goal NUMBER;
21 --added for campaign, schedule and source code
22 l_campaign_id varchar2(3000);
23 l_schedule_id varchar2(3000);
24 l_source_code_id varchar2(3000);
25 l_campaign_where_clause VARCHAR2(3000);
26 l_schedule_where_clause VARCHAR2(3000);
27 l_source_code_where_clause VARCHAR2(3000);
28 l_agent_group varchar2(3000);
29 l_call_where_clause VARCHAR2(3000);
30 l_session_where_clause VARCHAR2(3000);
31 l_call_center VARCHAR2(3000);
32 l_view_by VARCHAR2(3000);
33
34 l_custom_rec BIS_QUERY_ATTRIBUTES := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
35
36 BEGIN
37 --
38 --Initialize p_custom_output
39 --
40 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
41
42 -- Get the parameters
43
44
45 BIX_PMV_DBI_UTL_PKG.get_ao_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_call_center,
51 l_campaign_id,
52 l_schedule_id,
53 l_source_code_id,
54 l_agent_group,
55 l_view_by
56 );
57
58
59 IF l_call_center IS NOT NULL THEN
60 l_call_where_clause := ' AND mv.server_group_id IN (:l_call_center) ';
61 END IF;
62 IF l_campaign_id IS NOT NULL THEN
63 l_campaign_where_clause := ' AND mv.campaign_id IN (:l_campaign_id) ';
64 END IF;
65 IF l_schedule_id IS NOT NULL THEN
66 l_schedule_where_clause := ' AND mv.schedule_id IN (:l_schedule_id) ';
67 END IF;
68 IF l_source_code_id IS NOT NULL THEN
69 l_source_code_where_clause := ' AND mv.campaign_id in (select source_code_for_id from
70 ams_source_codes where source_code_id IN (:l_source_code_id) and arc_source_code_for=''CAMP'' and active_flag=''Y'' ) ';
71
72 END IF;
73
74 l_sqltext := '
75 SELECT meaning BIX_PMV_AO_OUTCOME,
76 sum(pper) BIX_PMV_AO_PPER,
77 sum(cper) BIX_PMV_AO_CPER
78 FROM (
79 SELECT lookup.meaning meaning,
80 nvl(sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
81 decode(lookup.lookup_code,
82 ''SR'', AGENT_SR_CREATED,
83 ''LEAD'', AGENT_LEADS_CREATED,
84 ''OPP'', AGENT_OPPORTUNITIES_CREATED,
85 0),
86 0)),0) PPER,
87 nvl(sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
88 decode(lookup.lookup_code,
89 ''SR'', AGENT_SR_CREATED,
90 ''LEAD'', AGENT_LEADS_CREATED,
91 ''OPP'', AGENT_OPPORTUNITIES_CREATED,
92 0),
93 0)),0) CPER
94 FROM bix_ao_call_details_mv mv,
95 fii_time_rpt_struct cal,
96 (
97 select lookup_code,meaning
98 from fnd_lookup_values_vl
99 where lookup_type = :l_lookup_type
100 ) lookup
101 WHERE mv.time_id = cal.time_id
102 AND mv.row_type = :l_row_type
103 AND mv.period_type_id = cal.period_type_id
104 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) =
105 cal.record_type_id
106 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE,
107 &BIS_PREVIOUS_ASOF_DATE) ';
108
109 l_sqltext := l_sqltext || l_call_where_clause ||l_source_code_where_clause||
110 '
111 GROUP BY lookup.meaning
112 UNION ALL
113 SELECT meaning, 0, 0
114 from fnd_lookup_values_vl
115 where lookup_type = :l_lookup_type
116 )
117 GROUP BY meaning '
118 ;
119 l_sqltext:=replace(replace(replace(replace(replace(l_sqltext,
120 ' ',' '),' ',' '),' ',' '),' ',' '),' ',' ');
121 p_custom_sql := l_sqltext;
122
123 l_custom_rec.attribute_name := ':l_row_type';
124 l_custom_rec.attribute_value:= 'C';
125 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
126 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
127
128 p_custom_output.Extend();
129 p_custom_output(p_custom_output.count) := l_custom_rec;
130
131 IF l_call_center IS NOT NULL
132 THEN
133 l_custom_rec.attribute_name := ':l_call_center';
134 l_custom_rec.attribute_value:= l_call_center;
135 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
136 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
137
138 p_custom_output.Extend();
139 p_custom_output(p_custom_output.count) := l_custom_rec;
140 END IF;
141
142 IF l_campaign_id IS NOT NULL
143 THEN
144 l_custom_rec.attribute_name := ':l_campaign_id';
145 l_custom_rec.attribute_value:= l_campaign_id;
146 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
147 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
148
149 p_custom_output.Extend();
150 p_custom_output(p_custom_output.count) := l_custom_rec;
151 END IF;
152
153 IF l_schedule_id IS NOT NULL
154 THEN
155 l_custom_rec.attribute_name := ':l_schedule_id';
156 l_custom_rec.attribute_value:= l_schedule_id;
157 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
158 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
159
160 p_custom_output.Extend();
161 p_custom_output(p_custom_output.count) := l_custom_rec;
162 END IF;
163 IF l_source_code_id IS NOT NULL
164 THEN
165 l_custom_rec.attribute_name := ':l_source_code_id';
166 l_custom_rec.attribute_value:= l_source_code_id;
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
175 l_custom_rec.attribute_name := ':l_lookup_type';
176 l_custom_rec.attribute_value:= 'BIX_PMV_AO_OUTCOMES';
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
183
184 EXCEPTION
185 WHEN OTHERS THEN
186 NULL;
187 END GET_SQL;
188 END BIX_PMV_AO_OUTGR_PRTLT_PKG;
189