[Home] [Help]
PACKAGE BODY: APPS.AMS_DCF_LEADS_TITLE
Source
1 PACKAGE BODY AMS_DCF_LEADS_TITLE AS
2 /* $Header: amsvldsb.pls 115.3 2002/09/26 01:24:37 snallapa ship $*/
3 --
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12 -- Enter procedure, function bodies as shown below
13
14
15 FUNCTION print_kpi_bin_title (p_parameters IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
16 is
17 vPeriod varchar2(80);
18 xPeriod varchar2(80);
19
20 BEGIN
21 vPeriod := jtfb_dcf.get_parameter_value(p_parameters, 'P_PERIOD');
22 select to_char(start_date,'mm-dd') ||' to '|| to_char(end_date,'mm-dd') into xPeriod from bim_r_periods where calc_type = 'FIXED' and period_type = vPeriod;
23 return (xPeriod);
24 EXCEPTION
25 WHEN OTHERS THEN
26 null;
27 END print_kpi_bin_title;
28
29 -- Create a parameter for holding the report title.
30
31 FUNCTION print_kpi_report_title (p_parameters IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
32 is
33 vContext varchar2(80);
34 xContext varchar2(80);
35 vRepName varchar2(80);
36 vPeriod varchar2(80);
37 xPeriod varchar2(80);
38 vFor varchar2(80);
39 vCompCode varchar2(80);
40
41 BEGIN
42 vCompCode := jtfb_dcf.get_parameter_value(p_parameters,'DCF.componentCode');
43 select
44 meaning into vRepName
45 from
46 fnd_lookup_values
47 where
48 lookup_type= 'BIM_DBC_DCF_TITLES'
49 and lookup_code = vCompCode
50 and language = userenv('LANG');
51 vContext := jtfb_dcf.get_parameter_value(p_parameters, 'pContext');
52 -- vRepName := jtfb_dcf.get_parameter_value(p_parameters, 'P_REPORT_NAME');
53 vPeriod := jtfb_dcf.get_parameter_value(p_parameters, 'P_PERIOD');
54 vFor := ams_utility_pvt.get_lookup_meaning('AMS_IO_OTHER','FOR');
55 select to_char(start_date,'mm-dd') ||' to '|| to_char(end_date,'mm-dd') into xPeriod from bim_r_periods where calc_type = 'FIXED' and period_type = vPeriod;
56
57 return (vRepName||' '||vFor||' '|| xPeriod);
58 EXCEPTION
59 WHEN OTHERS THEN
60 return (vRepName);
61 null;
62 END print_kpi_report_title;
63
64 FUNCTION print_reg_bin_title (p_parameters IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
65 is
66 vRepName varchar2(80);
67 xRetString varchar2(100);
68 vIn varchar2(80);
69 vScaleByCode varchar2(80);
70 vScaleByMeaning varchar2(80);
71 vCompCode varchar2(80);
72
73 BEGIN
74 vCompCode := jtfb_dcf.get_parameter_value(p_parameters,'DCF.componentCode');
75 select
76 meaning into vRepName
77 from
78 fnd_lookup_values
79 where
80 lookup_type= 'BIM_DBC_DCF_TITLES'
81 and lookup_code = vCompCode
82 and language = userenv('LANG');
83 vIn := ams_utility_pvt.get_lookup_meaning('AMS_IO_OTHER','IN');
84 vScaleByCode := jtfb_dcf.get_parameter_value(p_parameters, 'P_SCALE_BY');
85 --vRepName := jtfb_dcf.get_parameter_value(p_parameters, 'P_BIN_NAME');
86 if (vScaleByCode <> '1') then
87 select meaning into vScaleByMeaning from ams_lookups
88 where lookup_type ='AMS_IO_SCALE_BY' and lookup_code = vScaleByCode;
89 else
90 vScaleByMeaning := '';
91 end if;
92
93 return (vRepName||' (' ||vIn|| ' '||vScaleByMeaning||') ');
94 EXCEPTION
95 WHEN OTHERS THEN
96 return (vRepName);
97 null;
98 END print_reg_bin_title;
99
100
101 FUNCTION print_reg_bin_title_ls (p_parameters IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
102 is
103 vRepName varchar2(80);
104 xRetString varchar2(100);
105 vIn varchar2(80);
106 vScaleByCode varchar2(80);
107 vScaleByMeaning varchar2(80);
108 vCompCode varchar2(80);
109
110 BEGIN
111 vCompCode := jtfb_dcf.get_parameter_value(p_parameters,'DCF.componentCode');
112 select
113 meaning into vRepName
114 from
115 fnd_lookup_values
116 where
117 lookup_type= 'BIM_DBC_DCF_TITLES'
118 and lookup_code = vCompCode
119 and language = userenv('LANG');
120 vIn := ams_utility_pvt.get_lookup_meaning('AMS_IO_OTHER','IN');
121 vScaleByCode := jtfb_dcf.get_parameter_value(p_parameters, 'P_SCALE_BY');
122 --vRepName := jtfb_dcf.get_parameter_value(p_parameters, 'P_BIN_NAME_LS');
123 if (vScaleByCode <> '1') then
124 select meaning into vScaleByMeaning from ams_lookups
125 where lookup_type ='AMS_IO_SCALE_BY' and lookup_code = vScaleByCode;
126 return (vRepName||' (' ||vIn|| ' '||vScaleByMeaning||') ');
127 else
128 return (vRepName);
129 end if;
130
131 EXCEPTION
132 WHEN OTHERS THEN
133 return (vRepName);
134 null;
135 END print_reg_bin_title_ls;
136
137 FUNCTION print_reg_bin_title_lq (p_parameters IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
138 is
139 vRepName varchar2(80);
140 xRetString varchar2(100);
141 vIn varchar2(80);
142 vScaleByCode varchar2(80);
143 vScaleByMeaning varchar2(80);
144 vCompCode varchar2(80);
145
146 BEGIN
147 vCompCode := jtfb_dcf.get_parameter_value(p_parameters,'DCF.componentCode');
148 select
149 meaning into vRepName
150 from
151 fnd_lookup_values
152 where
153 lookup_type= 'BIM_DBC_DCF_TITLES'
154 and lookup_code = vCompCode
155 and language = userenv('LANG');
156 vIn := ams_utility_pvt.get_lookup_meaning('AMS_IO_OTHER','IN');
157 vScaleByCode := jtfb_dcf.get_parameter_value(p_parameters, 'P_SCALE_BY');
158 --vRepName := jtfb_dcf.get_parameter_value(p_parameters, 'P_BIN_NAME_LQ');
159 if (vScaleByCode <> '1') then
160 select meaning into vScaleByMeaning from ams_lookups
161 where lookup_type ='AMS_IO_SCALE_BY' and lookup_code = vScaleByCode;
162 return (vRepName||' (' ||vIn|| ' '||vScaleByMeaning||') ');
163 else
164 return (vRepName);
165 end if;
166
167 EXCEPTION
168 WHEN OTHERS THEN
169 return (vRepName);
170 null;
171 END print_reg_bin_title_lq;
172
173 FUNCTION print_reg_bin_title_ws (p_parameters IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
174 is
175 vRepName varchar2(80);
176 xRetString varchar2(100);
177 vIn varchar2(80);
178 vScaleByCode varchar2(80);
179 vScaleByMeaning varchar2(80);
180 vCompCode varchar2(80);
181
182 BEGIN
183 vCompCode := jtfb_dcf.get_parameter_value(p_parameters,'DCF.componentCode');
184 select
185 meaning into vRepName
186 from
187 fnd_lookup_values
188 where
189 lookup_type= 'BIM_DBC_DCF_TITLES'
190 and lookup_code = vCompCode
191 and language = userenv('LANG');
192 vIn := ams_utility_pvt.get_lookup_meaning('AMS_IO_OTHER','IN');
193 vScaleByCode := jtfb_dcf.get_parameter_value(p_parameters, 'P_SCALE_BY');
194 --vRepName := jtfb_dcf.get_parameter_value(p_parameters, 'P_BIN_NAME_WS');
195 if (vScaleByCode <> '1') then
196 select meaning into vScaleByMeaning from ams_lookups
197 where lookup_type ='AMS_IO_SCALE_BY' and lookup_code = vScaleByCode;
198 return (vRepName||' (' ||vIn|| ' '||vScaleByMeaning||') ');
199 else
200 return (vRepName);
201 end if;
202
203 EXCEPTION
204 WHEN OTHERS THEN
205 return (vRepName);
206 null;
207 END print_reg_bin_title_ws;
208
209 FUNCTION print_reg_bin_title_is (p_parameters IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
210 is
211 vRepName varchar2(80);
212 xRetString varchar2(100);
213 vIn varchar2(80);
214 vScaleByCode varchar2(80);
215 vScaleByMeaning varchar2(80);
216 vCompCode varchar2(80);
217
218 BEGIN
219 vCompCode := jtfb_dcf.get_parameter_value(p_parameters,'DCF.componentCode');
220 select
221 meaning into vRepName
222 from
223 fnd_lookup_values
224 where
225 lookup_type= 'BIM_DBC_DCF_TITLES'
226 and lookup_code = vCompCode
227 and language = userenv('LANG');
228 vIn := ams_utility_pvt.get_lookup_meaning('AMS_IO_OTHER','IN');
229 vScaleByCode := jtfb_dcf.get_parameter_value(p_parameters, 'P_SCALE_BY');
230 --vRepName := jtfb_dcf.get_parameter_value(p_parameters, 'P_BIN_NAME_IS');
231 if (vScaleByCode <> '1') then
232 select meaning into vScaleByMeaning from ams_lookups
233 where lookup_type ='AMS_IO_SCALE_BY' and lookup_code = vScaleByCode;
234 return (vRepName||' (' ||vIn|| ' '||vScaleByMeaning||') ');
235 else
236 return (vRepName);
237 end if;
238
239 EXCEPTION
240 WHEN OTHERS THEN
241 return (vRepName);
242 null;
243 END print_reg_bin_title_is;
244
245 FUNCTION print_reg_bin_title_wr (p_parameters IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
246 is
247 vRepName varchar2(80);
248 xRetString varchar2(100);
249 vIn varchar2(80);
250 vScaleByCode varchar2(80);
251 vScaleByMeaning varchar2(80);
252 vCompCode varchar2(80);
253
254 BEGIN
255 vCompCode := jtfb_dcf.get_parameter_value(p_parameters,'DCF.componentCode');
256 select
257 meaning into vRepName
258 from
259 fnd_lookup_values
260 where
261 lookup_type= 'BIM_DBC_DCF_TITLES'
262 and lookup_code = vCompCode
263 and language = userenv('LANG');
264 vIn := ams_utility_pvt.get_lookup_meaning('AMS_IO_OTHER','IN');
265 vScaleByCode := jtfb_dcf.get_parameter_value(p_parameters, 'P_SCALE_BY');
266 --vRepName := jtfb_dcf.get_parameter_value(p_parameters, 'P_BIN_NAME_WR');
267 if (vScaleByCode <> '1') then
268 select meaning into vScaleByMeaning from ams_lookups
269 where lookup_type ='AMS_IO_SCALE_BY' and lookup_code = vScaleByCode;
270 return (vRepName||' (' ||vIn|| ' '||vScaleByMeaning||') ');
271 else
272 return (vRepName);
273 end if;
274
275 EXCEPTION
276 WHEN OTHERS THEN
277 return (vRepName);
278 null;
279 END print_reg_bin_title_wr;
280
281
282 FUNCTION print_reg_report_title (p_parameters IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
283 is
284 vContext varchar2(80);
285 xContext varchar2(80);
286 vRepName varchar2(80);
287 xRem varchar2(80);
288 xYear varchar2(80);
289 xQtr varchar2(80);
290 xMonth varchar2(80);
291 xDisplayType varchar2(80);
292 xPeriod varchar2(80);
293 xRetString varchar2(100);
294 vDefPeriod varchar2(80);
295 vPeriod varchar2(80);
296 vTo varchar2(80);
297 vFor varchar2(80);
298 vIn varchar2(80);
299 vScaleByCode varchar2(80);
300 vScaleByMeaning varchar2(80);
301 vCompCode varchar2(80);
302
303
304 BEGIN
305 vCompCode := jtfb_dcf.get_parameter_value(p_parameters,'DCF.componentCode');
306 select
307 meaning into vRepName
308 from
309 fnd_lookup_values
310 where
311 lookup_type= 'BIM_DBC_DCF_TITLES'
312 and lookup_code = vCompCode
313 and language = userenv('LANG');
314 vContext := jtfb_dcf.get_parameter_value(p_parameters, 'pContext');
315 --vRepName := jtfb_dcf.get_parameter_value(p_parameters, 'P_REPORT_NAME');
316 vDefPeriod := jtfb_dcf.get_parameter_value(p_parameters, 'P_DEF_PERIOD');
317 vPeriod := jtfb_dcf.get_parameter_value(p_parameters, 'P_PERIOD');
318 vScaleByCode := jtfb_dcf.get_parameter_value(p_parameters, 'P_SCALE_BY');
319 --vTo := ams_utility_pvt.get_lookup_meaning('AMS_IO_OTHER','TO');
320 vFor := ams_utility_pvt.get_lookup_meaning('AMS_IO_OTHER','FOR');
321
322 xRetString := 'x';
323 xDisplayType := 'Z';
324 if (vContext is NULL OR vContext = 'NOT_FOUND' OR vContext = '') then
325 vContext := vDefPeriod;
326 end if;
327 if (vContext = 'LEAD_COVERAGE' OR vContext = 'LEAD_TO_OPP') THEN
328 vContext := 'N-N-N-'||vPeriod;
329 end if;
330
331 if (vContext is not NULL) then
332 select year, qtr,month,display_type into xYear, xQtr, xMonth, xDisplayType
333 from bim_r_lead_qu_bin_mv where year||'-'||qtr||'-'||month||'-'||display_type = vContext
334 and rownum < 2;
335 if (xDisplayType <> 'Z') then
336 select to_char(start_date,fnd_profile.VALUE('ICX_DATE_FORMAT_MASK')) ||' ' ||vTo||' '||
337 to_char(end_date,fnd_profile.VALUE('ICX_DATE_FORMAT_MASK')) into xPeriod
338 from bim_r_periods where calc_type = 'ROLLING' and period_type = xDisplayType;
339 xRetString := ' ' || xDisplayType || ' ('||xPeriod ||')';
340 else
341 xRetString := xYear;
342 if (xQtr <> 'N') then
343 xRetString := xQtr;
344 end if;
345 if (xMonth <> 'N') then
346 xRetString := xMonth;
347 end if;
348 end if;
349 end if;
350
351 if (xRetString = 'x') then
352 xRetString := '';
353 end if;
354 if (vScaleByCode <> '1') then
355 select meaning into vScaleByMeaning from ams_lookups
356 where lookup_type ='AMS_IO_SCALE_BY' and lookup_code = vScaleByCode;
360 end if;
357 return (vRepName||' (' ||vIn|| ' '||vScaleByMeaning||') '||vFor||' '|| xRetString);
358 else
359 return (vRepName||' '||vFor||' '|| xRetString);
361 EXCEPTION
362 WHEN OTHERS THEN
363 return (vRepName);
364 null;
365 END print_reg_report_title;
366
367 END;