[Home] [Help]
PACKAGE BODY: APPS.IBE_BI_CART_ORD_PVT
Source
1 PACKAGE BODY IBE_BI_CART_ORD_PVT AS
2 /* $Header: IBEVBICARTORDB.pls 120.6 2006/06/26 08:57:42 gjothiku ship $ */
3 PROCEDURE GET_CART_ORD_PORT_SQL(
4 p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
5 x_custom_sql OUT NOCOPY VARCHAR2,
6 x_custom_output OUT NOCOPY bis_query_attributes_TBL
7 ) IS
8 l_custom_sql VARCHAR2(4000);
9 l_parameter_name VARCHAR2(3200);
10 l_asof_date DATE;
11 l_prev_date DATE;
12 l_record_type_id NUMBER;
13 l_period_type VARCHAR2(3200);
14 l_comparison_type VARCHAR2(3200);
15 l_currency_code VARCHAR2(3200);
16 l_minisite VARCHAR2(3200);
17 l_minisite_id VARCHAR2(3200);
18 l_msiteFilter VARCHAR2(1000);
19 l_tableList VARCHAR2(1000);
20 l_custom_rec BIS_QUERY_ATTRIBUTES;
21 dbg_msg VARCHAR2(3200);
22 l_global_primary VARCHAR2(15);
23 l_global_secondary VARCHAR2(15);
24 BEGIN
25 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
26 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_cart_ord_pvt.get_cart_ord_port_sql.begin','BEGIN');
27 END IF;
28
29 --initializing the variables
30
31 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
32 l_global_primary := '''FII_GLOBAL1''';
33 l_global_secondary := '''FII_GLOBAL2''';
34
35 FOR i IN p_pmv_parameters.FIRST..p_pmv_parameters.LAST
36 LOOP
37 l_parameter_name := p_pmv_parameters(i).parameter_name ;
38
39 IF( l_parameter_name = 'AS_OF_DATE')
40 THEN
41 l_asof_date :=
42 TO_DATE(p_pmv_parameters(i).parameter_value,'DD/MM/YYYY');
43 ELSIF( l_parameter_name = 'IBW_WEB_ANALYTICS_GROUP1+FII_CURRENCIES')
44 THEN
45 l_currency_code := p_pmv_parameters(i).parameter_id;
46 ELSIF( l_parameter_name = 'PERIOD_TYPE')
47 THEN
48 l_period_type := p_pmv_parameters(i).parameter_value;
49 ELSIF( l_parameter_name = 'TIME_COMPARISON_TYPE')
50 THEN
51 l_comparison_type := p_pmv_parameters(i).parameter_value;
52 ELSIF( l_parameter_name = 'SITE+SITE')
53 THEN
54 l_minisite := p_pmv_parameters(i).parameter_value;
55 l_minisite_id := p_pmv_parameters(i).parameter_id;
56 END IF;
57 END LOOP;
58
59 l_record_type_id := IBE_BI_PMV_UTIL_PVT.GET_RECORD_TYPE_ID(l_period_type);
60
61
62 l_prev_date := IBE_BI_PMV_UTIL_PVT.GET_PREV_DATE(
63 p_asof_date => l_asof_date,
64 p_period_type => l_period_type,
65 p_comparison_type => l_comparison_type );
66
67 dbg_msg := 'AS_OF_DATE:'||l_asof_date||','||'CURR_CODE:'||l_currency_code||
68 ','||'PERIOD_TYPE:'||l_period_type||','||'COMPARISION_TYPE:'||
69 l_comparison_type||','||'MINISITE:'||l_minisite||','||'MINISITE_ID:'||
70 l_minisite_id||','||'RECORD_TYPE_ID:'||l_record_type_id||','||'PREV_DATE:'||
71 l_prev_date;
72 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
73 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_cart_ord_pvt.get_cart_ord_port_sql.parameters',dbg_msg);
74 END IF;
75 if (trim(l_minisite_id) is null) then
76 --Change in View done for Bug#:4654974. Issue#12 by narao
77 --The Site Dimension now refers to IBW_BI_MSITE_DIMN_V
78 --And hence the MSITES View is changed from IBE_BI_MSITES_V to IBW_BI_MSITE_DIMN_V.
79 l_tableList := ' IBE_BI_CART_ORD_MV FACT,'||
80 -- ' IBE_BI_MSITES_V MSITES,'||
81 ' IBW_BI_MSITE_DIMN_V MSITES,'||
82 ' FII_TIME_RPT_STRUCT_V CAL';
83
84 l_msiteFilter :=' AND MSITES.ID = FACT.MINISITE_ID';
85
86 else
87 l_tableList := ' IBE_BI_CART_ORD_MV FACT,'||
88 ' FII_TIME_RPT_STRUCT_V CAL';
89
90 l_msiteFilter := ' AND FACT.MINISITE_ID in (&SITE+SITE)';
91
92 end if;
93
94 /**************************************************************************/
95 /* IBE_ATTR1 : Category */
96 /* IBE_VAL1 : Count */
97 /* IBE_VAL2 : Amount */
98 /* IBE_VAL3 : Change(Calculated in the AK region) */
99 /* IBE_VAL6 : Prior Order Amount */
100 /* IBE_VAL4 : Lines */
101 /* IBE_VAL5 : Conversion Ratio */
102 /**************************************************************************/
103
104 /*
105 Changes done for Bug:4772549
106 DECODE(OLD_AMT,0,NULL,((CUR_AMT - OLD_AMT)/OLD_AMT)*100) IBE_VAL3
107 is changed to
108
109 OLD_AMT IBE_VAL6
110 */
111
112 -- Added nvl to the outer most select clause as part of bug 5253591
113
114 l_custom_sql :='SELECT MEANING IBE_ATTR1,nvl(TOT_COUNT,0) IBE_VAL1,nvl(CUR_AMT,0) IBE_VAL2,'||
115 ' nvl(OLD_AMT,0) IBE_VAL6,nvl(NO_LINES,0) IBE_VAL4,'||
116 ' DECODE(nvl(TOT_COUNT,0),0,NULL,(CON_ORD/TOT_COUNT)*100) IBE_VAL5 from('||
117 ' SELECT decode(flkup.lookup_code,''IBE_ORD_ASSISTED'', 3,'||
118 ' ''IBE_ORD_UNASSISTED'',4,'||
119 ' ''IBE_ORD_TOTAL'',5,'||
120 ' ''IBE_ORD_CAMPAIGN'',7,'||
121 ' ''IBE_ORD_QUOTING'',9,'||
122 ' ''IBE_QOT_ASSISTED'', 0,'||
123 ' ''IBE_QOT_UNASSISTED'',1,'||
124 ' ''IBE_QOT_TOTAL'',2,'||
125 ' ''IBE_QOT_CAMPAIGN'',6,'||
126 ' ''IBE_QOT_QUOTING'',8 ) OB,'||
127 ' flkup.MEANING, SUM(TOT_COUNT) TOT_COUNT, SUM(NEW_VALUE) CUR_AMT,'||
128 ' SUM(OLD_VALUE) OLD_AMT,SUM(NO_LINES) NO_LINES,'||
129 ' SUM(con_ord) CON_ORD'||
130 ' FROM ('||
131 ' SELECT MEASURE_TYPE ,'||
132 ' SUM(CASE WHEN CAL.report_date = :l_asof_date'||
133 ' THEN Tot_Count else 0 end) TOT_COUNT ,'||
134 ' SUM(CASE WHEN CAL.report_date = :l_asof_date'||
135 ' THEN decode(:l_currency_code,:l_global_primary,Amount_g,:l_global_secondary,Amount_g1,CURRENCY_CD_F,Amount_f)'||
136 ' ELSE 0 END) new_value,'||
137 ' SUM(CASE WHEN CAL.report_date = :l_prev_date'||
138 ' THEN decode(:l_currency_code,:l_global_primary,Amount_g,:l_global_secondary,Amount_g1,CURRENCY_CD_F,Amount_f)'||
139 ' ELSE 0 END) old_value,'||
140 ' SUM(CASE WHEN CAL.report_date = :l_asof_date'||
141 ' THEN FACT.NO_LINES else 0 end) NO_LINES,'||
142 ' SUM(CASE WHEN CAL.report_date = :l_asof_date'||
143 ' THEN con_ord'||
144 ' ELSE NULL END) con_ord'||
145 ' FROM '||l_tableList||
146 ' WHERE CAL.calendar_id = -1'||
147 ' AND FACT.Time_Id = CAL.Time_Id'||
148 ' AND FACT.Period_Type_id = CAL.Period_Type_Id'||
149 ' AND REPORT_DATE IN (:l_asof_date,:l_prev_date)'||l_msiteFilter||
150 ' AND BITAND(CAL.Record_Type_Id, :l_record_type_id) = CAL.Record_Type_Id'||
151 ' GROUP BY MEASURE_TYPE) FACT1, '||
152 ' FND_LOOKUPS flkup'||
153 ' WHERE'||
154 ' flkup.LOOKUP_TYPE = ''IBE_BI_MEASURES'''||
155 ' AND flkup.LOOKUP_CODE = FACT1.MEASURE_TYPE(+)'||
156 ' GROUP BY flkup.meaning,decode(flkup.lookup_code,''IBE_ORD_ASSISTED'', 3,'||
157 '''IBE_ORD_UNASSISTED'',4,'||
158 '''IBE_ORD_TOTAL'',5,'||
159 '''IBE_ORD_CAMPAIGN'',7,'||
160 '''IBE_ORD_QUOTING'',9,'||
161 '''IBE_QOT_ASSISTED'', 0,'||
162 '''IBE_QOT_UNASSISTED'',1,'||
163 '''IBE_QOT_TOTAL'',2,'||
164 '''IBE_QOT_CAMPAIGN'',6,'||
165 '''IBE_QOT_QUOTING'',8 )) ORDER BY OB';
166
167 x_custom_sql := l_custom_sql;
168
169 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
170 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_cart_ord_pvt.get_cart_ord_port_sql.query',l_custom_sql);
171 END IF;
172
173 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
174 x_custom_output.Extend(6);
175
176 l_custom_rec.attribute_name := ':l_asof_date' ;
177 l_custom_rec.attribute_value:= to_char(l_asof_date,'dd/mm/yyyy');
178 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
179 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
180
181 x_custom_output(1) := l_custom_rec;
182
183 l_custom_rec.attribute_name := ':l_prev_date' ;
184 l_custom_rec.attribute_value:= to_char(l_prev_date,'dd/mm/yyyy');
185 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
186 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
187
188 x_custom_output(2) := l_custom_rec;
189
190 l_custom_rec.attribute_name := ':l_currency_code' ;
191 l_custom_rec.attribute_value:= l_currency_code;
192 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
193 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
194
195 x_custom_output(3) := l_custom_rec;
196
197
198 l_custom_rec.attribute_name := ':l_record_type_id' ;
199 l_custom_rec.attribute_value:= l_record_type_id;
200 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
201 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
202
203 x_custom_output(4) := l_custom_rec;
204
205 l_custom_rec.attribute_name := ':l_global_primary' ;
206 l_custom_rec.attribute_value:= l_global_primary;
207 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
208 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
209
210 x_custom_output(5) := l_custom_rec;
211
212 l_custom_rec.attribute_name := ':l_global_secondary' ;
213 l_custom_rec.attribute_value:= l_global_secondary;
214 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
215 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
216
217 x_custom_output(6) := l_custom_rec;
218
219 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
220 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'ibe.plsql.dbi.ibe_bi_cart_ord_pvt.get_cart_ord_port_sql.end','END');
221 END IF;
222
223 END GET_CART_ORD_PORT_SQL;
224
225 END IBE_BI_CART_ORD_PVT;