[Home] [Help]
PACKAGE BODY: APPS.AMS_DCF_TITLE
Source
1 PACKAGE BODY ams_dcf_title AS
2 /* $Header: amsvdtlb.pls 120.1 2011/11/18 10:40:27 rsatyava 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 FUNCTION get_currency (p_parameters IN varchar2 default null) return varchar2
15 IS
16 vScaleByCode varchar2(80);
17 vScaleByMeaning varchar2(80);
18 vIn varchar2(80);
19 vFor varchar2(80);
20 vCurrency varchar2(80);
21
22 BEGIN
23 vIn := ams_utility_pvt.get_lookup_meaning('AMS_IO_OTHER','IN');
24 vFor := ams_utility_pvt.get_lookup_meaning('AMS_IO_OTHER','FOR');
25 vCurrency := fnd_profile.VALUE('AMS_DEFAULT_CURR_CODE');
26 vScaleByCode := jtfb_dcf.get_parameter_value(p_parameters, 'P_SCALE_BY');
27 if (vScaleByCode <> '1') then
28 select meaning into vScaleByMeaning from ams_lookups where lookup_type ='AMS_IO_SCALE_BY' and lookup_code = vScaleByCode;
29 else
30 vScaleByMeaning := '';
31 end if;
32
33 return (' (' ||vIn|| ' '||vScaleByMeaning|| ' '||vCurrency||') ' || vFor||' ');
34
35 EXCEPTION
36 WHEN OTHERS THEN
37 return (' ');
38 null;
39 END get_currency;
40
41 FUNCTION print_kpi_bin_title (p_parameters IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
42 is
43 vPeriod varchar2(80);
44 xPeriod varchar2(80);
45
46 BEGIN
47 vPeriod := jtfb_dcf.get_parameter_value(p_parameters, 'P_PERIOD_TYPE');
48 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;
49 return (xPeriod);
50 --return ('The data reported is for Region: ' || xRegion || ', Country: ' || xCountry || ', Business Unit: ' || xBusinessUnit || ', Activity Type: ' || xActivityType || ', Campaign Status: ' || xCampaignStatus || ' aggregated by ' || xAggregateBy);
51 EXCEPTION
52 WHEN OTHERS THEN
53 -- dbms_output.put_line(sqlerrm(sqlcode));
54 null;
55 END print_kpi_bin_title;
56
57 -- Create a parameter for holding the report title.
58
59 FUNCTION print_kpi_report_title (p_parameters IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
60 is
61 vContext varchar2(80);
62 xContext varchar2(80);
63 vRepName varchar2(80);
64 vPeriod varchar2(80);
65 xPeriod varchar2(80);
66 vFor varchar2(80);
67 vCompCode varchar2(80);
68
69 BEGIN
70 vCompCode := jtfb_dcf.get_parameter_value(p_parameters, 'DCF.componentCode');
71 select
72 meaning into vRepName
73 from
74 fnd_lookup_values
75 where
76 lookup_type= 'BIM_DBC_DCF_TITLES'
77 and lookup_code = vCompCode
78 and language = userenv('LANG');
79 vContext := jtfb_dcf.get_parameter_value(p_parameters, 'pContext');
80 vPeriod := jtfb_dcf.get_parameter_value(p_parameters, 'P_PERIOD_TYPE');
81 vFor := ams_utility_pvt.get_lookup_meaning('AMS_IO_OTHER','FOR');
82 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;
83
84 return (vRepName || get_currency(p_parameters) || xPeriod);
85 EXCEPTION
86 WHEN OTHERS THEN
87 return (vRepName);
88 -- dbms_output.put_line(sqlerrm(sqlcode));
89 null;
90 END print_kpi_report_title;
91
92 FUNCTION print_hom_report_nc_title (p_parameters IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
93 IS
94 vPeriod varchar2(80);
95 xRetString varchar2(100);
96 vRepName varchar2(80);
97 vFor varchar2(80);
98 vCompCode varchar2(80);
99
100 BEGIN
101 vCompCode := jtfb_dcf.get_parameter_value(p_parameters, 'DCF.componentCode');
102 select
103 meaning into vRepName
104 from
105 fnd_lookup_values
106 where
107 lookup_type= 'BIM_DBC_DCF_TITLES'
108 and lookup_code = vCompCode
109 and language = userenv('LANG');
110 vPeriod := jtfb_dcf.get_parameter_value(p_parameters, 'P_PERIOD');
111 --vRepName := jtfb_dcf.get_parameter_value(p_parameters, 'P_REPORT_NAME');
112 vFor := ams_utility_pvt.get_lookup_meaning('AMS_IO_OTHER','FOR');
113 xRetString := '';
114 if (vPeriod is not NULL) then
115 select substr(vPeriod,0,INSTR(vPeriod,'|')-1) into xRetString from dual;
116 xRetString := ' '||vFor||' ' || xRetString;
117 end if;
118 return (vRepName || xRetString);
119
120 EXCEPTION
121 WHEN OTHERS THEN
122 -- dbms_output.put_line(sqlerrm(sqlcode));
123 return (vRepName);
124 null;
125 END print_hom_report_nc_title;
126
127
128 FUNCTION print_hom_report_title (p_parameters IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
129 IS
130 vPeriod varchar2(80);
131 xRetString varchar2(100);
132 vRepName varchar2(80);
133 --vCurrency varchar2(80);
134 vCompCode varchar2(80);
135
136 BEGIN
137 vCompCode := jtfb_dcf.get_parameter_value(p_parameters, 'DCF.componentCode');
138 select
139 meaning into vRepName
140 from
141 fnd_lookup_values
142 where
143 lookup_type= 'BIM_DBC_DCF_TITLES'
144 and lookup_code = vCompCode
145 and language = userenv('LANG');
146 vPeriod := jtfb_dcf.get_parameter_value(p_parameters, 'P_PERIOD');
147 --vRepName := jtfb_dcf.get_parameter_value(p_parameters, 'P_REPORT_NAME');
148 xRetString := '';
149 --select fnd_profile.VALUE('AMS_DEFAULT_CURR_CODE') into vCurrency from dual;
150 if (vPeriod is not NULL) then
151 select substr(vPeriod,0,INSTR(vPeriod,'|')-1) into xRetString from dual;
152 --xRetString := ' - ' || xRetString;
153 end if;
154 --return (vRepName ||' (in '||vCurrency||')'|| xRetString);
155 return (vRepName || get_currency(p_parameters) || xRetString);
156
157 EXCEPTION
158 WHEN OTHERS THEN
159 -- dbms_output.put_line(sqlerrm(sqlcode));
160 return (vRepName);
161 null;
162 END print_hom_report_title;
163
164 FUNCTION print_reg_bin_title_kpi (p_parameters IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
165 is
166 vRepName varchar2(80);
167 vDefRepName varchar2(80);
168 xRetString varchar2(100);
169 vIn varchar2(80);
170 vFor varchar2(80);
171 vCurrency varchar2(80);
172 vScaleByCode varchar2(80);
173 vScaleByMeaning varchar2(80);
174 vCompCode varchar2(80);
175
176 BEGIN
177 vCompCode := jtfb_dcf.get_parameter_value(p_parameters, 'DCF.componentCode');
178 select
179 meaning into vRepName
180 from
181 fnd_lookup_values
182 where
183 lookup_type= 'BIM_DBC_DCF_TITLES'
184 and lookup_code = vCompCode
185 and language = userenv('LANG');
186 vIn := ams_utility_pvt.get_lookup_meaning('AMS_IO_OTHER','IN');
187 vCurrency := fnd_profile.VALUE('AMS_DEFAULT_CURR_CODE');
188 vScaleByCode := jtfb_dcf.get_parameter_value(p_parameters, 'P_SCALE_BY');
189 --vRepName := jtfb_dcf.get_parameter_value(p_parameters, 'P_TITLE_NAME_KP');
190 vDefRepName := 'Response to Lead';
191 if (vScaleByCode <> '1') then
192 select meaning into vScaleByMeaning from ams_lookups
193 where lookup_type ='AMS_IO_SCALE_BY' and lookup_code = vScaleByCode;
194 else
195 vScaleByMeaning := '';
196 end if;
197
198 return (vRepName||' (' ||vIn|| ' '||vScaleByMeaning|| ' '||vCurrency||') ');
199 EXCEPTION
200 WHEN OTHERS THEN
201 return (vDefRepName);
202 null;
203 END print_reg_bin_title_kpi;
204
205 FUNCTION print_reg_bin_title_mb (p_parameters IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
206 is
207 vRepName varchar2(80);
208 vDefRepName varchar2(80);
209 xRetString varchar2(100);
210 vIn varchar2(80);
211 vFor varchar2(80);
212 vCurrency varchar2(80);
213 vScaleByCode varchar2(80);
214 vScaleByMeaning varchar2(80);
215 vCompCode varchar2(80);
216
217 BEGIN
218 vCompCode := jtfb_dcf.get_parameter_value(p_parameters, 'DCF.componentCode');
219 select
220 meaning into vRepName
221 from
222 fnd_lookup_values
223 where
224 lookup_type= 'BIM_DBC_DCF_TITLES'
225 and lookup_code = vCompCode
226 and language = userenv('LANG');
227 vIn := ams_utility_pvt.get_lookup_meaning('AMS_IO_OTHER','IN');
228 vCurrency := fnd_profile.VALUE('AMS_DEFAULT_CURR_CODE');
229 vScaleByCode := jtfb_dcf.get_parameter_value(p_parameters, 'P_SCALE_BY');
230 --vRepName := jtfb_dcf.get_parameter_value(p_parameters, 'P_TITLE_NAME_MB');
231 vDefRepName := 'Marketing Budgets';
232 if (vScaleByCode <> '1') then
233 select meaning into vScaleByMeaning from ams_lookups
234 where lookup_type ='AMS_IO_SCALE_BY' and lookup_code = vScaleByCode;
235 else
236 vScaleByMeaning := '';
237 end if;
238
239 return (vRepName||' (' ||vIn|| ' '||vScaleByMeaning|| ' '||vCurrency||') ');
240 EXCEPTION
241 WHEN OTHERS THEN
242 return (vDefRepName);
243 null;
244 END print_reg_bin_title_mb;
245
246 FUNCTION print_reg_bin_title_ce (p_parameters IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
247 is
248 vRepName varchar2(80);
249 vDefRepName varchar2(80);
250 xRetString varchar2(100);
251 vIn varchar2(80);
252 vFor varchar2(80);
253 vCurrency varchar2(80);
254 vScaleByCode varchar2(80);
255 vScaleByMeaning varchar2(80);
256 vCompCode varchar2(80);
257
258 BEGIN
259 vCompCode := jtfb_dcf.get_parameter_value(p_parameters, 'DCF.componentCode');
260 select
261 meaning into vRepName
262 from
263 fnd_lookup_values
264 where
265 lookup_type= 'BIM_DBC_DCF_TITLES'
266 and lookup_code = vCompCode
267 and language = userenv('LANG');
268 vIn := ams_utility_pvt.get_lookup_meaning('AMS_IO_OTHER','IN');
269 vCurrency := fnd_profile.VALUE('AMS_DEFAULT_CURR_CODE');
270 vScaleByCode := jtfb_dcf.get_parameter_value(p_parameters, 'P_SCALE_BY');
271 --vRepName := jtfb_dcf.get_parameter_value(p_parameters, 'P_TITLE_NAME_CE');
272 vDefRepName := 'Campaign Effectiveness';
273 if (vScaleByCode <> '1') then
274 select meaning into vScaleByMeaning from ams_lookups
275 where lookup_type ='AMS_IO_SCALE_BY' and lookup_code = vScaleByCode;
276 else
277 vScaleByMeaning := '';
278 end if;
279
280 return (vRepName||' (' ||vIn|| ' '||vScaleByMeaning|| ' '||vCurrency||') ');
281
282 EXCEPTION
283 WHEN OTHERS THEN
284 return (vDefRepName);
285 null;
286 END print_reg_bin_title_ce;
287
288 FUNCTION print_reg_bin_title_ee (p_parameters IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
289 is
290 vRepName varchar2(80);
291 vDefRepName varchar2(80);
292 xRetString varchar2(100);
293 vIn varchar2(80);
294 vFor varchar2(80);
295 vCurrency varchar2(80);
296 vScaleByCode varchar2(80);
297 vScaleByMeaning varchar2(80);
298 vCompCode varchar2(80);
299
300 BEGIN
301 vCompCode := jtfb_dcf.get_parameter_value(p_parameters, 'DCF.componentCode');
302 select
303 meaning into vRepName
304 from
305 fnd_lookup_values
306 where
307 lookup_type= 'BIM_DBC_DCF_TITLES'
308 and lookup_code = vCompCode
309 and language = userenv('LANG');
310 vIn := ams_utility_pvt.get_lookup_meaning('AMS_IO_OTHER','IN');
311 vCurrency := fnd_profile.VALUE('AMS_DEFAULT_CURR_CODE');
312 vScaleByCode := jtfb_dcf.get_parameter_value(p_parameters, 'P_SCALE_BY');
313 --vRepName := jtfb_dcf.get_parameter_value(p_parameters, 'P_TITLE_NAME_EE');
314 vDefRepName := 'Event Effectiveness';
315 if (vScaleByCode <> '1') then
316 select meaning into vScaleByMeaning from ams_lookups
317 where lookup_type ='AMS_IO_SCALE_BY' and lookup_code = vScaleByCode;
318 else
319 vScaleByMeaning := '';
320 end if;
321
322 return (vRepName||' (' ||vIn|| ' '||vScaleByMeaning|| ' '||vCurrency||') ');
323 EXCEPTION
324 WHEN OTHERS THEN
325 return (vDefRepName);
326 null;
327 END print_reg_bin_title_ee;
328
329
330
331 FUNCTION print_reg_report_title (p_parameters IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
332 is
333 vContext varchar2(80);
334 xContext varchar2(80);
335 vRepName varchar2(80);
336 vSource varchar2(80);
337 vCurrency varchar2(80);
338 xRem varchar2(80);
339 xYear varchar2(80);
340 xQtr varchar2(80);
341 xMonth varchar2(80);
342 xDisplayType varchar2(80);
343 xPeriod varchar2(80);
344 xRetString varchar2(100);
345 vDefPeriod varchar2(80);
346 vTo varchar2(80);
347 vCompCode varchar2(80);
348
349
350
351 BEGIN
352 vCompCode := jtfb_dcf.get_parameter_value(p_parameters, 'DCF.componentCode');
353 select
354 meaning into vRepName
355 from
356 fnd_lookup_values
357 where
358 lookup_type= 'BIM_DBC_DCF_TITLES'
359 and lookup_code = vCompCode
360 and language = userenv('LANG');
361 vContext := jtfb_dcf.get_parameter_value(p_parameters, 'pContext');
362 --vRepName := jtfb_dcf.get_parameter_value(p_parameters, 'P_REPORT_NAME');
363 vSource := jtfb_dcf.get_parameter_value(p_parameters, 'P_BIN_NAME');
364 if vSource not in ('N','NOT_FOUND') then
365 if vCompcode in ('AMS_REP_CAMP_BY_LEAD_OPPO','AMS_REP_CAMP_BY_BUDGET_AMT','AMS_REP_CAMPAIGNS_BY_ORDER','AMS_CAMP_BY_ACTIVITY') then
366 vSource :='AMS_BIN_CAMP_EFFECTIVENESS';
367 elsif vCompcode in ('AMS_BUDGET_TOTAL_AMT','AMS_BUDGET_ACTIVITY','AMS_REP_BUD_BY_CAMPAIGN','AMS_REP_BUD_BY_BU','AMS_GRAPH_BGT_UTL_BY_BU','AMS_GRAPH_BGT_UTL_BY_CAT') then
368 vSource :='AMS_BIN_MARKETING_BUDGETS';
369 elsif vCompcode in ('AMS_REP_EVENT_BY_REGISTRAN','AMS_REP_EVENT_BY_LEAD_OPPO','AMS_REP_EVENT_BY_BUD_AMT','AMS_REP_EVENT_BY_EVENT_TYP') then
370 vSource :='AMS_BIN_EVENT_EFFECTIVENES';
371 elsif vCompcode in ( 'AMS_REP_MARK_ACTI','AMS_REP_MARK_CAMP') then
372 vSource :='AMS_BIN_MARKET_ACTIV';
373 end if;
374 end if;
375 -- vSource := jtfb_dcf.get_parameter_value(p_parameters, 'P_BIN_NAME');
376 vDefPeriod := jtfb_dcf.get_parameter_value(p_parameters, 'P_DEF_PERIOD');
377 vTo := ams_utility_pvt.get_lookup_meaning('AMS_IO_OTHER','TO');
378
379 xRetString := 'x';
380 xDisplayType := 'Z';
381 select fnd_profile.VALUE('AMS_DEFAULT_CURR_CODE') into vCurrency from dual;
382 if (vDefPeriod is not NULL) then
383 select substr(vDefPeriod,0,INSTR(vDefPeriod,'-',10)-1) into xRetString from dual;
384 select year, qtr into xYear, xQtr
385 from bim_r_fd_dim_sum_mv where year||'-'||qtr = xRetString and rownum < 2;
386 xRetString := xQtr;
387 end if;
388
389 if (vContext is not NULL) then
390 if (vSource = 'AMS_BIN_MARKETING_BUDGETS') then
391 select year, qtr,month,display_type into xYear, xQtr, xMonth,xDisplayType
392 from bim_r_fd_dim_sum_mv where year||'-'||qtr||'-'||month||'-'||display_type = vContext
393 and rownum < 2;
394 if (xDisplayType <> 'Z') then
395 select to_char(start_date,fnd_profile.VALUE('ICX_DATE_FORMAT_MASK')) ||' ' ||vTo||' '||
396 to_char(end_date,fnd_profile.VALUE('ICX_DATE_FORMAT_MASK')) into xPeriod
397 from bim_r_periods where calc_type = 'ROLLING' and period_type = xDisplayType;
398 xRetString := ' ' || xDisplayType || ' ('||xPeriod ||')';
399 else
400 xRetString := xYear;
401 if (xQtr <> 'N') then
402 xRetString := xQtr;
403 end if;
404 if (xMonth <> 'N') then
405 xRetString := xMonth;
406 end if;
407 end if;
408 elsif (vSource = 'AMS_BIN_MARKET_ACTIV') then
409 select year, qtr,month,display_type into xYear, xQtr, xMonth,xDisplayType
410 from bim_r_fd_dim_sum_mv where year||'-'||qtr||'-'||month||'-'||display_type = vContext
411 and rownum < 2;
412 if (xDisplayType <> 'Z') then
413 select to_char(start_date,fnd_profile.VALUE('ICX_DATE_FORMAT_MASK')) ||' ' ||vTo||' '||
414 to_char(end_date,fnd_profile.VALUE('ICX_DATE_FORMAT_MASK')) into xPeriod
415 from bim_r_periods where calc_type = 'ROLLING' and period_type = xDisplayType;
416 xRetString := ' ' || xDisplayType || ' ('||xPeriod ||')';
417 else
418 xRetString := xYear;
419 if (xQtr <> 'N') then
420 xRetString := xQtr;
421 end if;
422 if (xMonth <> 'N') then
423 xRetString := xMonth;
424 end if;
425 end if;
426 elsif (vSource = 'AMS_BIN_EVENT_EFFECTIVENES') then
427 select year, qtr,month into xYear, xQtr, xMonth
428 from BIM_R_EVEN_DIM_SUM_MV where year||'-'||qtr||'-'||month = vContext and rownum < 2;
429 xRetString := xYear;
430 if (xQtr <> 'N') then
431 xRetString := xQtr;
432 end if;
433 if (xMonth <> 'N') then
434 xRetString := xMonth;
435 end if;
436 elsif (vSource = 'AMS_BIN_CAMP_EFFECTIVENESS') then
437 select year, qtr,month into xYear, xQtr, xMonth
438 from BIM_R_CAMP_DIM_SUM_MV where year||'-'||qtr||'-'||month = vContext and rownum < 2;
439 xRetString := xYear;
440 if (xQtr <> 'N') then
441 xRetString := xQtr;
442 end if;
443 if (xMonth <> 'N') then
444 xRetString := xMonth;
445 end if;
446 end if;
447 end if;
448 --if (xRetString <> 'x') then
449 -- xRetString := ' - '||xRetString;
450 --else
451 -- xRetString := '';
452 --end if;
453 --return (vRepName ||' (in '||vCurrency||')'|| xRetString);
454
455 if (xRetString = 'x') then
456 xRetString := '';
457 end if;
458 return (vRepName || get_currency(p_parameters) || xRetString);
459 EXCEPTION
460 WHEN OTHERS THEN
461 -- dbms_output.put_line(sqlerrm(sqlcode));
462 --return (vRepName ||' '|| xRetString );
463 return (vRepName);
464 null;
465 END print_reg_report_title;
466
467 -- Here need to take care of the fourth parameter.
468 FUNCTION print_reg_incremental_title (p_parameters IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
469 is
470 vContext varchar2(80);
471 vRepName varchar2(80);
472 xIncr varchar2(80);
473 xPeriod varchar2(80);
474 vCompCode varchar2(80);
475
476 BEGIN
477 vCompCode := jtfb_dcf.get_parameter_value(p_parameters, 'DCF.componentCode');
478 select
479 meaning into vRepName
480 from
481 fnd_lookup_values
482 where
483 lookup_type= 'BIM_DBC_DCF_TITLES'
484 and lookup_code = vCompCode
485 and language = userenv('LANG');
486 vContext := jtfb_dcf.get_parameter_value(p_parameters, 'pContext');
487 --vRepName := jtfb_dcf.get_parameter_value(p_parameters, 'P_REPORT_NAME');
488
489 select substr(vContext,INSTR(vContext,'-',-1)+1) into xIncr from dual;
490
491 select start_date ||'-'|| end_date into xPeriod from bim_r_periods where calc_type = 'ROLLING' and period_type = xIncr;
492
493 return (vRepName||' '||xPeriod );
494 EXCEPTION
495 WHEN OTHERS THEN
496 -- dbms_output.put_line(sqlerrm(sqlcode));
497 null;
498 END print_reg_incremental_title;
499
500
501
502 FUNCTION print_mktg_activities_title (p_parameters IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
503 is
504 vPeriod varchar2(80);
505 vRepName varchar2(80);
506 xCount varchar2(80);
507 vCompCode varchar2(80);
508
509 BEGIN
510 vCompCode := jtfb_dcf.get_parameter_value(p_parameters, 'DCF.componentCode');
511 select
512 meaning into vRepName
513 from
514 fnd_lookup_values
515 where
516 lookup_type= 'BIM_DBC_DCF_TITLES'
517 and lookup_code = vCompCode
518 and language = userenv('LANG');
519 vPeriod := jtfb_dcf.get_parameter_value(p_parameters, 'P_AGGREGATE_BY');
520 --vReportName := jtfb_dcf.get_parameter_value(p_parameters, 'P_REP_NAME');
521 if (vPeriod = 'INCREMENT') then
522 select to_char(current_count_value,'999,999,999,999') into xCount from bim_r_camp_act_bin_mv a
523 where a.aggregate_by = 'INCREMENT'
524 and a.year is not null
525 and a.qtr is not null
526 and a.month is not null
527 AND a.display_type is not null and rownum < 2;
528 else
529 select /*+ index_desc(A, BIM_R_CAMP_ACT_BIN_MV_N4) */
530 TO_CHAR(A.current_count_value,'999,999,999,999') into xCount
531 from BIM_R_CAMP_ACT_BIN_MV A
532 where A.AGGREGATE_BY = 'MONTH' AND A.YEAR IS NOT NULL
533 AND A.QTR IS NOT NULL AND A.MONTH IS NOT NULL
534 AND A.DISPLAY_TYPE IS NOT NULL
535 and rownum <2;
536 --select to_char(a.cv,'999,999,999,999') into xCount
537 -- from (select current_count_value cv from bim_r_camp_act_bin_mv a
538 -- where a.aggregate_by = 'MONTH'
539 -- and a.year is not null
540 -- and a.qtr is not null
541 -- and a.month is not null
542 -- AND a.display_type is not null
543 -- order by a.year desc, a.qtr desc, a.month_order desc) a
544 -- where rownum < 2;
545 end if;
546 return (vRepName || ' - '|| xCount);
547
548 EXCEPTION
549 WHEN OTHERS THEN
550 -- dbms_output.put_line(sqlerrm(sqlcode));
551 return (vRepName);
552 null;
553 END print_mktg_activities_title;
554
555 FUNCTION print_currency(p_parameters IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
556 is
557 vRepName varchar2(80);
558 vIn varchar2(80);
559 vFor varchar2(80);
560 vCurrency varchar2(80);
561 BEGIN
562 vRepName := jtfb_dcf.get_parameter_value(p_parameters, 'P_REPORT_NAME');
563 vIn := ams_utility_pvt.get_lookup_meaning('AMS_IO_OTHER','IN');
564 vFor := ams_utility_pvt.get_lookup_meaning('AMS_IO_OTHER','FOR');
565 vCurrency := fnd_profile.VALUE('AMS_DEFAULT_CURR_CODE');
566 return (vRepName||' (' ||vIn|| ' '||vCurrency||') ' );
567
568 EXCEPTION
569 WHEN OTHERS THEN
570 -- dbms_output.put_line(sqlerrm(sqlcode));
571 return (vRepName);
572 null;
573 END print_currency;
574
575
576 FUNCTION print_reg_report_nc_title (p_parameters IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
577 is
578 vContext varchar2(80);
579 xContext varchar2(80);
580 vRepName varchar2(80);
581 vSource varchar2(80);
582 xRem varchar2(80);
583 xYear varchar2(80);
584 xQtr varchar2(80);
585 xMonth varchar2(80);
586 xDisplayType varchar2(80);
587 xPeriod varchar2(80);
588 xRetString varchar2(100);
589 vDefPeriod varchar2(80);
590 vfor varchar2(80);
591 vTo varchar2(80);
592 vCompCode varchar2(80);
593
594
595 BEGIN
596 vCompCode := jtfb_dcf.get_parameter_value(p_parameters, 'DCF.componentCode');
597 select
598 meaning into vRepName
599 from
600 fnd_lookup_values
601 where
602 lookup_type= 'BIM_DBC_DCF_TITLES'
603 and lookup_code = vCompCode
604 and language = userenv('LANG');
605 vContext := jtfb_dcf.get_parameter_value(p_parameters, 'pContext');
606 --vRepName := jtfb_dcf.get_parameter_value(p_parameters, 'P_REPORT_NAME');
607 vSource := jtfb_dcf.get_parameter_value(p_parameters, 'P_BIN_NAME');
608 if vSource not in ('NOT_FOUND','N') then
609 if vCompCode in ('AMS_GRAPH_EVENT_BY_ATTEND','AMS_GRAPH_EVENT_BY_LEAD','AMS_REP_EVENT_BY_REGISTRAN') then
610 vSource := 'AMS_BIN_EVENT_EFFECTIVENES';
611 elsif vCompCode in ('AMS_GRAPH_BGT_UTL_BY_BU','AMS_GRAPH_BGT_UTL_BY_CAT') then
612 vSource :='AMS_BIN_MARKETING_BUDGETS';
613 elsif vCompCode in ('AMS_GRAPH_CAMP_BY_LEADS','AMS_GRAPH_CAMP_BY_OPPOR') then
614 vSource :='AMS_BIN_CAMP_EFFECTIVENESS';
615 end if;
616 end if;
617 vDefPeriod := jtfb_dcf.get_parameter_value(p_parameters, 'P_DEF_PERIOD');
618 vTo := ams_utility_pvt.get_lookup_meaning('AMS_IO_OTHER','TO');
619
620 xRetString := 'x';
621 if (vDefPeriod is not NULL) then
622 select substr(vDefPeriod,0,INSTR(vDefPeriod,'-',10)-1) into xRetString from dual;
623 select year, qtr into xYear, xQtr
624 from bim_r_fd_dim_sum_mv where year||'-'||qtr = xRetString and rownum < 2;
625 xRetString := xQtr;
626 end if;
627
628 if (vContext is not NULL) then
629 if (vSource = 'AMS_BIN_MARKETING_BUDGETS') then
630 select year, qtr,month,display_type into xYear, xQtr, xMonth,xDisplayType
631 from bim_r_fd_dim_sum_mv where year||'-'||qtr||'-'||month||'-'||display_type = vContext
632 and rownum < 2;
633 if (xDisplayType <> 'Z') then
634 select to_char(start_date,fnd_profile.VALUE('ICX_DATE_FORMAT_MASK')) ||' ' ||vTo||' '||
635 to_char(end_date,fnd_profile.VALUE('ICX_DATE_FORMAT_MASK')) into xPeriod
636 from bim_r_periods where calc_type = 'ROLLING' and period_type = xDisplayType;
637 xRetString := ' ' || xDisplayType || ' ('||xPeriod ||')';
638 else
639 xRetString := xYear;
640 if (xQtr <> 'N') then
641 xRetString := xQtr;
642 end if;
643 if (xMonth <> 'N') then
644 xRetString := xMonth;
645 end if;
646 end if;
647 elsif (vSource = 'AMS_BIN_MARKET_ACTIV') then
648 select year, qtr,month,display_type into xYear, xQtr, xMonth,xDisplayType
649 from bim_r_fd_dim_sum_mv where year||'-'||qtr||'-'||month||'-'||display_type = vContext
650 and rownum < 2;
651 if (xDisplayType <> 'Z') then
652 select to_char(start_date,fnd_profile.VALUE('ICX_DATE_FORMAT_MASK')) ||' ' ||vTo||' '||
653 to_char(end_date,fnd_profile.VALUE('ICX_DATE_FORMAT_MASK')) into xPeriod
654 from bim_r_periods where calc_type = 'ROLLING' and period_type = xDisplayType;
655 xRetString := ' ' || xDisplayType || ' ('||xPeriod ||')';
656 else
657 xRetString := xYear;
658 if (xQtr <> 'N') then
659 xRetString := xQtr;
660 end if;
661 if (xMonth <> 'N') then
662 xRetString := xMonth;
663 end if;
664 end if;
665 elsif (vSource = 'AMS_BIN_EVENT_EFFECTIVENES') then
666 select year, qtr,month into xYear, xQtr, xMonth
667 from BIM_R_EVEN_DIM_SUM_MV where year||'-'||qtr||'-'||month = vContext and rownum < 2;
668 xRetString := xYear;
669 if (xQtr <> 'N') then
670 xRetString := xQtr;
671 end if;
672 if (xMonth <> 'N') then
673 xRetString := xMonth;
674 end if;
675 elsif (vSource = 'AMS_BIN_CAMP_EFFECTIVENESS') then
676 select year, qtr,month into xYear, xQtr, xMonth
677 from BIM_R_CAMP_DIM_SUM_MV where year||'-'||qtr||'-'||month = vContext and rownum < 2;
678 xRetString := xYear;
679 if (xQtr <> 'N') then
680 xRetString := xQtr;
681 end if;
682 if (xMonth <> 'N') then
683 xRetString := xMonth;
684 end if;
685 end if;
686 end if;
687 vFor := ams_utility_pvt.get_lookup_meaning('AMS_IO_OTHER','FOR');
688 if (xRetString <> 'x') then
689 xRetString := ' '||vfor||' '||xRetString;
690 else
691 xRetString := '';
692 end if;
693 return (vRepName || xRetString);
694 EXCEPTION
695 WHEN OTHERS THEN
696 return (vRepName);
697 null;
698
699 END print_reg_report_nc_title;
700
701 END;