1 PACKAGE BODY IBW_BI_UTL_PVT AS
2 /* $Header: ibwbutlb.pls 120.5 2005/10/19 02:39 gjothiku noship $ */
3
4 /**********************************************************************************************
5 * FUNCTION : GET_LOOKUPS_MNG *
6 * PURPOSE : This function is used to return FND lookup meaning *
7 * given a FND lookup type and FND lookup code *
8 * PRARAMETERS : *
9 * p_lkp_type varchar2 IN This is used to get thr lookup type *
10 * p_lkp_code varchar2 IN This is used to get the lookup code *
11 * RETURN : *
12 * VARCHAR2 - Lookup meaning *
13 **********************************************************************************************/
14
15 FUNCTION GET_LOOKUPS_MNG(p_lkp_type IN varchar2,p_lkp_code in varchar2)
16 return VARCHAR2 IS
17
18 l_meaning varchar2(100);
19 --FND Logging
20 l_full_path VARCHAR2(50);
21 gaflog_value VARCHAR2(10);
22
23 CURSOR c_meaning (lkp_type varchar2,lkp_code varchar2) IS
24 SELECT
25 meaning
26 FROM
27 fnd_lookups
28 WHERE
29 lookup_type = lkp_type AND
30 lookup_code = lkp_code;
31
32 BEGIN
33 --FND Logging
34 l_full_path := 'ibw.plsql.ibwbutlb.get_lookups_mng';
35 --Profile is : FND: Debug Log Enabled and FND: Debug Log Level for Log Level
36 gaflog_value := fnd_profile.value('AFLOG_ENABLED');
37
38 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
39 fnd_log.string(fnd_log.level_statement,l_full_path,' BEGIN : p_lkp_type '|| p_lkp_type || ' p_lkp_code '|| p_lkp_code);
40 END IF;
41
42 OPEN c_meaning(p_lkp_type,p_lkp_code);
43 FETCH c_meaning INTO l_meaning;
44 CLOSE c_meaning;
45
46 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
47 fnd_log.string(fnd_log.level_statement,l_full_path,' END : Lookup Meaning = ' || l_meaning );
48 END IF;
49
50 return l_meaning;
51
52 EXCEPTION
53 WHEN OTHERS THEN
54 if (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) then
55 fnd_log.string(fnd_log.level_unexpected,l_full_path,SQLERRM);
56 end if;
57 return null;
58
59 END GET_LOOKUPS_MNG;
60
61 /**********************************************************************************************
62 * FUNCTION : GTTL *
63 * PURPOSE : This function is used to return dynamic graph titles *
64 * non trend reports based on the view by selected *
65 * PRARAMETERS : *
66 * p_lkp_code varchar2 IN This is used to get the lookup code *
67 * p_region_code varchar2 IN This is used to get thr AK region code *
68 * p_view_by varchar2 IN This is used to get the view by selected *
69 * RETURN : *
70 * VARCHAR2 - Graph title *
71 **********************************************************************************************/
72
73 FUNCTION GTTL (p_lkp_code IN varchar2,p_region_code IN varchar2,p_view_by IN varchar2)
74 return VARCHAR2 IS
75
76 l_first VARCHAR2(100);
77 l_attr_code VARCHAR2(100);
78 l_last VARCHAR2(100);
79 l_region_code VARCHAR2(100);
80 --FND Logging
81 l_full_path VARCHAR2(50);
82 gaflog_value VARCHAR2(10);
83
84 CURSOR c_last (regioncode varchar2,attributecode varchar2) IS
85 select ATTRIBUTE_LABEL_LONG
86 from ak_region_items_vl
87 where region_code = regioncode
88 and attribute_code = attributecode;
89 BEGIN
90
91 --FND Logging
92 l_full_path := 'ibw.plsql.ibwbutlb.get_grph_ttl';
93 --Profile is : FND: Debug Log Enabled and FND: Debug Log Level for Log Level
94 gaflog_value := fnd_profile.value('AFLOG_ENABLED');
95
96 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
97 fnd_log.string(fnd_log.level_statement,l_full_path,' BEGIN : p_region_code '|| p_region_code || ' p_lkp_code '|| p_lkp_code || ' p_view_by '|| p_view_by);
98 END IF;
99
100 -- Based on the short name passed as parameter the appropriate region code is initialised in l_region_code
101 -- This is done mainly to reduce the length of the long label in Ak region items from
102 -- where this procedure is called.
103
104 IF p_region_code = 'PAGEINT'
105 THEN
106 l_region_code := 'IBW_BI_PAGE_INTEREST';
107
108 ELSIF p_region_code = 'CSTACTV'
109 THEN
110 l_region_code := 'IBW_BI_CUST_ACTY';
111
112 ELSIF p_region_code = 'CMPANLY'
113 THEN
114 l_region_code := 'IBW_BI_CAMP_ANALYSIS';
115
116 ELSIF p_region_code = 'WEBREF'
117 THEN
118 l_region_code := 'IBW_BI_WEB_REF_ANALYSIS';
119
120 ELSIF p_region_code = 'WEBPROD'
121 THEN
122 l_region_code := 'IBW_BI_PROD_INT';
123
124 END IF;
125
126 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
127 fnd_log.string(fnd_log.level_statement,l_full_path,' l_region_code '|| l_region_code );
128 END IF;
129
130 -- The lookup meaning gives the first part of the graph title
131
132 l_first := GET_LOOKUPS_MNG('IBW_GEN_LOOKUP',p_lkp_code);
133
134 -- Based on the view by the attribute is initialised in l_attr_code
135
136 IF( p_view_by = 'SITE+SITE')
137 THEN
138 l_attr_code := 'IBE_MSITE_ID'; -- Changed from IBW_SITE to IBE_MSITE_ID
139
140 ELSIF( p_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS')
141 THEN
142 l_attr_code := 'IBW_CUST_CLASS';
143
144 ELSIF( p_view_by = 'CUSTOMER+PROSPECT')
145 THEN
146 l_attr_code := 'IBW_PROSPECT';
147
148 ELSIF( p_view_by = 'ITEM+ENI_ITEM_VBH_CAT')
149 THEN
150 l_attr_code := 'IBE_PROD_CATG'; -- Changed IBW_PROD_CATG to IBE_PROD_CATG
151
152 ELSIF( p_view_by = 'IBW_WEB_ANALYTICS_GROUP1+ENI_ITEM') -- Fix for Issue # 19 in Bug # 4636308
153 THEN
154 l_attr_code := 'IBW_PROD';
155
156 ELSIF( p_view_by = 'IBW_REFERRAL_CATEGORY+IBW_REF_CAT')
157 THEN
158 l_attr_code := 'IBW_REFERRAL';
159
160 ELSIF( p_view_by = 'CAMPAIGN+CAMPAIGN')
161 THEN
162 l_attr_code := 'IBW_CAMPAIGN';
163
164 ELSIF( p_view_by = 'IBW_PAGE+IBW_SITE_AREAS')
165 THEN
166 l_attr_code := 'IBW_SITE_AREA';
167
168 ELSIF( p_view_by = 'IBW_PAGE+IBW_PAGES')
169 THEN
170 l_attr_code := 'IBW_PAGE';
171
172 END IF;
173
174 OPEN c_last(l_region_code,l_attr_code);
175 FETCH c_last INTO l_last;
176 CLOSE c_last;
177
178 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
179 fnd_log.string(fnd_log.level_statement,l_full_path,' END : l_first '|| l_first || ' l_last '|| l_last);
180 END IF;
181
182 return l_first||' '||l_last;
183
184 EXCEPTION
185 WHEN OTHERS THEN
186 if (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) then
187 fnd_log.string(fnd_log.level_unexpected,l_full_path,SQLERRM);
188 end if;
189 return null;
190
191 END GTTL;
192
193 /***********************************************************************************************
194 * PROCEDURE : GET_PAGE_PARAMETERS *
195 * PURPOSE : This procedure is used to get the parameters selected in the *
196 * parameter portlet *
197 * PRARAMETERS : *
198 * p_pmv_parameters BIS_PMV_PAGE_PARAMETER_TBL IN This table of records *
199 * is used to get the *
200 * paremeters in teh form of *
201 * (name,id,value) per record *
202 * x_period_type varchar2 OUT Period type *
203 * x_site varchar2 OUT Site *
204 * x_currency_code varchar2 OUT Currency code *
205 * x_site_area varchar2 OUT Site Area *
206 * x_page varchar2 OUT Page *
207 * x_referral varchar2 OUT Referral Category *
208 * x_prod_cat varchar2 OUT Product Category *
209 * x_prod varchar2 OUT Product *
210 * x_cust_class varchar2 OUT Customer Classification *
211 * x_cust varchar2 OUT Customer *
212 * x_campaign varchar2 OUT Campaign *
213 * x_view_by varchar2 OUT View By *
214 * *
215 ************************************************************************************************/
216
217 PROCEDURE GET_PAGE_PARAMETERS(p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_TBL,
218 x_period_type OUT NOCOPY VARCHAR2,
219 x_site OUT NOCOPY VARCHAR2,
220 x_currency_code OUT NOCOPY VARCHAR2,
221 x_site_area OUT NOCOPY VARCHAR2,
222 x_page OUT NOCOPY VARCHAR2,
223 x_referral OUT NOCOPY VARCHAR2,
224 x_prod_cat OUT NOCOPY VARCHAR2,
225 x_prod OUT NOCOPY VARCHAR2,
226 x_cust_class OUT NOCOPY VARCHAR2,
227 x_cust OUT NOCOPY VARCHAR2,
228 x_campaign OUT NOCOPY VARCHAR2,
229 x_view_by OUT NOCOPY VARCHAR2)
230 AS
231
232 l_parameter_name varchar2(1000);
233 --FND Logging
234 l_full_path VARCHAR2(50);
235 gaflog_value VARCHAR2(10);
236
237 BEGIN
238
239 --FND Logging
240 l_full_path := 'ibw.plsql.ibwbutlb.get_page_parameters';
241 --Profile is : FND: Debug Log Enabled and FND: Debug Log Level for Log Level
242 gaflog_value := fnd_profile.value('AFLOG_ENABLED');
243
244 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
245 fnd_log.string(fnd_log.level_statement,l_full_path,' BEGIN : ');
246 END IF;
247
248 FOR i IN p_pmv_parameters.FIRST..p_pmv_parameters.LAST
249 LOOP
250 l_parameter_name := p_pmv_parameters(i).parameter_name;
251
252
253 IF( l_parameter_name = 'IBW_WEB_ANALYTICS_GROUP1+FII_CURRENCIES')
254 THEN
255 x_currency_code := p_pmv_parameters(i).parameter_id;
256
257 ELSIF( l_parameter_name = 'PERIOD_TYPE')
258 THEN
259 x_period_type := p_pmv_parameters(i).parameter_value;
260
261 ELSIF( l_parameter_name = 'SITE+SITE')
262 THEN
263 x_site := p_pmv_parameters(i).parameter_value;
264
265 ELSIF( l_parameter_name = 'IBW_PAGE+IBW_SITE_AREAS')
266 THEN
267 x_site_area := p_pmv_parameters(i).parameter_value;
268
269 ELSIF( l_parameter_name = 'IBW_PAGE+IBW_PAGES')
270 THEN
271 x_page := p_pmv_parameters(i).parameter_value;
272
273 ELSIF( l_parameter_name = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS')
274 THEN
275 x_cust_class := p_pmv_parameters(i).parameter_value;
276
277 ELSIF( l_parameter_name = 'CUSTOMER+PROSPECT')
278 THEN
279 x_cust := p_pmv_parameters(i).parameter_value;
280
281 ELSIF( l_parameter_name = 'IBW_REFERRAL_CATEGORY+IBW_REF_CAT')
282 THEN
283 x_referral := p_pmv_parameters(i).parameter_value;
284
285 ELSIF( l_parameter_name = 'ITEM+ENI_ITEM_VBH_CAT')
286 THEN
287 x_prod_cat := p_pmv_parameters(i).parameter_value;
288
289 ELSIF( l_parameter_name = 'IBW_WEB_ANALYTICS_GROUP1+ENI_ITEM')
290 THEN
291 x_prod := p_pmv_parameters(i).parameter_value;
292
293 ELSIF( l_parameter_name = 'CAMPAIGN+CAMPAIGN')
294 THEN
295 x_campaign := p_pmv_parameters(i).parameter_value;
296
297 ELSIF ( l_parameter_name = 'VIEW_BY')
298 THEN
299 x_view_by := p_pmv_parameters(i).parameter_value;
300
301 END IF;
302 END LOOP;
303 IF gaflog_value ='Y' AND (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
304 fnd_log.string(fnd_log.level_statement,l_full_path,' END : x_period_type ' || x_period_type ||
305 ' x_currency_code '|| x_currency_code || ' x_site '|| x_site ||' x_site_area '|| x_site_area ||
306 ' x_page '|| x_page || ' x_cust_class '|| x_cust_class || ' x_cust '|| x_cust || ' x_referral ' ||
307 x_referral ||' x_prod_cat ' || x_prod_cat || ' x_prod '|| x_prod || ' x_campaign ' || x_campaign || ' x_view_by '||
308 x_view_by);
309 END IF;
310
311 EXCEPTION
312 WHEN OTHERS THEN
313 if (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) then
314 fnd_log.string(fnd_log.level_unexpected,l_full_path,SQLERRM);
315 end if;
316
317 END GET_PAGE_PARAMETERS;
318
319 END IBW_BI_UTL_PVT;
[Home] [Help]