1 PACKAGE BODY BIM_PMV_DBI_UTL_PKG AS
2 /*$Header: bimvutlb.pls 120.2.12010000.2 2008/10/08 05:04:03 annsrini ship $ */
3
4 PROCEDURE get_viewby_id (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
5 l_viewby_id in OUT NOCOPY NUMBER)
6 IS
7 BEGIN
8 IF (p_page_parameter_tbl.count > 0) THEN
9 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
10 IF( p_page_parameter_tbl(i).parameter_name= 'BIM_PARAMETER4') THEN
11 l_viewby_id := p_page_parameter_tbl(i).parameter_value;
12 END IF;
13 END LOOP;
14 END IF;
15 COMMIT;
16 END get_viewby_id;
17
18 PROCEDURE get_bim_page_params (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
19 l_as_of_date OUT NOCOPY DATE,
20 l_period_type in OUT NOCOPY VARCHAR2,
21 l_record_type_id OUT NOCOPY NUMBER,
22 l_comp_type OUT NOCOPY VARCHAR2,
23 l_country in OUT NOCOPY VARCHAR2,
24 l_view_by in OUT NOCOPY VARCHAR2,
25 l_cat_id in OUT NOCOPY VARCHAR2,
26 l_campaign_id in OUT NOCOPY VARCHAR2,
27 l_currency in OUT NOCOPY VARCHAR2 ,
28 l_col_id in OUT NOCOPY NUMBER,
29 l_area in OUT NOCOPY VARCHAR2,
30 l_media in OUT NOCOPY VARCHAR2,
31 l_report_name in OUT NOCOPY VARCHAR2
32 )
33 IS
34
35 l_sql_errm VARCHAR2(32000);
36
37 BEGIN
38
39 IF (p_page_parameter_tbl.count > 0) THEN
40 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
41
42 /*INSERT INTO bim_param_test values(p_page_parameter_tbl(i).parameter_name,
43 p_page_parameter_tbl(i).parameter_value,
44 p_page_parameter_tbl(i).parameter_id);*/
45
46 IF p_page_parameter_tbl(i).parameter_name = 'PERIOD_TYPE' THEN
47 l_period_type := p_page_parameter_tbl(i).parameter_value;
48 END IF;
49 IF p_page_parameter_tbl(i).parameter_name= 'AS_OF_DATE' THEN
50 l_as_of_date := trunc(sysdate);
51 END IF;
52
53 IF p_page_parameter_tbl(i).parameter_name= 'TIME_COMPARISON_TYPE' THEN
54 l_comp_type := p_page_parameter_tbl(i).parameter_value;
55 END IF;
56
57 IF( p_page_parameter_tbl(i).parameter_name= 'VIEW_BY') THEN
58 l_view_by := p_page_parameter_tbl(i).parameter_value;
59 if l_view_by is null then l_view_by := 'CAMPAIGN+CAMPAIGN';
60 end if;
61 END IF;
62
63 IF ( p_page_parameter_tbl(i).parameter_name= 'ITEM+ENI_ITEM_VBH_CAT') THEN
64 l_cat_id := p_page_parameter_tbl(i).parameter_id;
65 END IF;
66
67 IF ( p_page_parameter_tbl(i).parameter_name= 'CAMPAIGN+CAMPAIGN') THEN
68 l_campaign_id := p_page_parameter_tbl(i).parameter_id;
69 END IF;
70 IF ( p_page_parameter_tbl(i).parameter_name= 'CURRENCY+FII_CURRENCIES') THEN
71 l_currency := p_page_parameter_tbl(i).parameter_id;
72 END IF;
73
74 IF( p_page_parameter_tbl(i).parameter_name= 'BIM_PARAMETER1') THEN
75 l_col_id := p_page_parameter_tbl(i).parameter_value;
76 END IF;
77
78 IF( p_page_parameter_tbl(i).parameter_name= 'BIM_PARAMETER2') THEN
79 l_report_name := p_page_parameter_tbl(i).parameter_value;
80 END IF;
81
82 IF( p_page_parameter_tbl(i).parameter_name= 'MEDIA+MEDIA') THEN
83 l_media := p_page_parameter_tbl(i).parameter_id;
84 END IF;
85
86 IF( p_page_parameter_tbl(i).parameter_name= 'GEOGRAPHY+AREA') THEN
87 l_area := p_page_parameter_tbl(i).parameter_id;
88 IF (l_area IS NULL)
89 THEN l_area := '-1';
90 END IF;
91 END IF;
92
93 IF p_page_parameter_tbl(i).parameter_name= 'GEOGRAPHY+COUNTRY' THEN
94 l_country := p_page_parameter_tbl(i).parameter_id;
95 IF (l_country = '''ALL''')
96 THEN l_country := 'N';
97 END IF;
98
99 IF (l_country IS NULL)
100 THEN l_country := 'N';
101 END IF;
102 IF (instr(l_country,'''') >=0) THEN
103 l_country := replace(l_country, '''','');
104 END IF;
105 END IF;
106
107 END LOOP;
108 END IF;
109
110 IF l_comp_type IS NULL THEN l_comp_type := 'YEARLY'; END IF;
111
112 IF l_period_type IS NULL THEN l_period_type := 'FII_TIME_WEEK'; END IF;
113
114 IF l_country IS NULL THEN l_country := 'N'; END IF;
115
116 -- Retrieve l_period_type info using CASE
117
118 CASE l_period_type
119 WHEN 'FII_TIME_WEEK' THEN l_record_type_id := 11;
120 WHEN 'FII_TIME_ENT_PERIOD' THEN l_record_type_id := 23;
121 WHEN 'FII_TIME_ENT_QTR' THEN l_record_type_id := 55;
122 WHEN 'FII_TIME_ENT_YEAR' THEN l_record_type_id := 119;
123 ELSE l_record_type_id := 11;
124 END CASE;
125
126 /*INSERT INTO bim_param_test values('get_bim_page_params success',
127 nvl(l_comp_type,'NULL'),nvl(l_period_type,'NULL'),
128 DBMS_UTILITY.get_time,l_country,NULL,null);
129 COMMIT;
130 */
131 EXCEPTION
132 WHEN OTHERS THEN
133 l_sql_errm := SQLERRM;
134 /*INSERT INTO bim_param_test values('get_bim_page_params excpetion',
135 nvl(l_comp_type,'NULL'),nvl(l_period_type,'NULL'),
136 DBMS_UTILITY.get_time,l_country,l_sql_errm,null);
137 COMMIT;
138 */
139 END get_bim_page_params;
140
141 PROCEDURE get_bim_page_sgmt_params (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
142 p_as_of_date OUT NOCOPY DATE,
143 p_period_type IN OUT NOCOPY VARCHAR2,
144 p_record_type_id OUT NOCOPY NUMBER,
145 p_view_by IN OUT NOCOPY VARCHAR2,
146 p_cat_id IN OUT NOCOPY VARCHAR2,
147 p_sgmt_id IN OUT NOCOPY VARCHAR2,
148 p_currency IN OUT NOCOPY VARCHAR2,
149 p_url_metric IN OUT NOCOPY VARCHAR2,
150 p_url_viewby IN OUT NOCOPY VARCHAR2,
151 p_url_viewbyid IN OUT NOCOPY VARCHAR2
152 )
153 is
154 BEGIN
155
156 IF (p_page_parameter_tbl.count > 0) THEN
157 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last
158 LOOP
159
160 IF p_page_parameter_tbl(i).parameter_name = 'PERIOD_TYPE' THEN
161
162 p_period_type := p_page_parameter_tbl(i).parameter_value;
163
164 END IF;
165
166 IF p_page_parameter_tbl(i).parameter_name= 'AS_OF_DATE' THEN
167
168 p_as_of_date := trunc(sysdate);
169
170 END IF;
171 IF( p_page_parameter_tbl(i).parameter_name= 'VIEW_BY') THEN
172
173 p_view_by := p_page_parameter_tbl(i).parameter_value;
174
175 IF p_view_by IS NULL THEN
176
177 p_view_by := 'TARGET SEGMENT+TARGET SEGMENT';
178
179 END IF;
180
181 END IF;
182
183 IF ( p_page_parameter_tbl(i).parameter_name= 'ITEM+ENI_ITEM_VBH_CAT') THEN
184
185 p_cat_id := p_page_parameter_tbl(i).parameter_id;
186
187 /*IF p_cat_id = '''''' THEN
188 p_cat_id := NULL;
189
190 END IF;*/
191
192 END IF;
193
194 IF ( p_page_parameter_tbl(i).parameter_name= 'TARGET SEGMENT+TARGET SEGMENT') THEN
195
196 p_sgmt_id := p_page_parameter_tbl(i).parameter_id;
197
198 /*IF p_sgmt_id = '''''' THEN
199 p_sgmt_id := NULL;
200 END IF;*/
201
202 END IF;
203 IF ( p_page_parameter_tbl(i).parameter_name= 'CURRENCY+FII_CURRENCIES') THEN
204
205 p_currency := p_page_parameter_tbl(i).parameter_id;
206
207 END IF;
208
209 IF p_page_parameter_tbl(i).parameter_name = 'BIM_PARAMETER1' THEN
210
211 p_url_metric := p_page_parameter_tbl(i).parameter_value;
212
213 END IF;
214
215 IF p_page_parameter_tbl(i).parameter_name = 'BIM_PARAMETER3' THEN
216
217 p_url_viewby := p_page_parameter_tbl(i).parameter_value;
218
219 END IF;
220
221 IF p_page_parameter_tbl(i).parameter_name = 'BIM_PARAMETER2' THEN
222
223 p_url_viewbyid := p_page_parameter_tbl(i).parameter_value;
224
225 END IF;
226
227 END LOOP;
228 END IF;
229
230 IF p_period_type IS NULL THEN
231
232 p_period_type := 'FII_TIME_WEEK';
233
234 END IF;
235
236
237 -- Retrieve p_period_type info using CASE
238
239 CASE p_period_type
240 WHEN 'FII_TIME_WEEK' THEN p_record_type_id := 11;
241 WHEN 'FII_TIME_ENT_PERIOD' THEN p_record_type_id := 23;
242 WHEN 'FII_TIME_ENT_QTR' THEN p_record_type_id := 55;
243 WHEN 'FII_TIME_ENT_YEAR' THEN p_record_type_id := 119;
244 ELSE p_record_type_id := 11;
245 END CASE;
246 END;
247
248
249 FUNCTION Current_Period_Start_Date( l_as_of_date DATE,
250 l_period_type VARCHAR2) RETURN DATE IS
251
252 l_date date;
253
254 BEGIN
255
256 CASE l_period_type
257 WHEN 'FII_TIME_WEEK' THEN l_date := FII_TIME_API.Cwk_Start(l_as_of_date);
258 WHEN 'FII_TIME_ENT_PERIOD' THEN l_date := FII_TIME_API.Ent_Cper_Start(l_as_of_date);
259 WHEN 'FII_TIME_ENT_QTR' THEN l_date := FII_TIME_API.Ent_Cqtr_Start(l_as_of_date);
260 WHEN 'FII_TIME_ENT_YEAR' THEN l_date := FII_TIME_API.Ent_Cyr_Start(l_as_of_date);
261 ELSE l_date := FII_TIME_API.Ent_Cqtr_Start(l_as_of_date);
262 END CASE;
263
264 RETURN l_date;
265 END;
266
267 FUNCTION Previous_Period_Start_Date(l_as_of_date DATE,
268 l_period_type VARCHAR2,
269 l_comp_type VARCHAR2) RETURN DATE IS
270 l_prev_date date;
271 l_date date;
272
273 BEGIN
274
275 l_prev_date := Previous_Period_Asof_Date(l_as_of_date, l_period_type, l_comp_type);
276
277 l_date := Current_Period_Start_Date(l_prev_date, l_period_type);
278
279 RETURN l_date;
280
281 EXCEPTION
282 WHEN OTHERS THEN
283 RETURN BIS_COMMON_PARAMETERS.Get_Global_Start_Date;
284 END;
285
286
287 FUNCTION Current_Report_Start_Date( l_as_of_date DATE,
288 l_period_type VARCHAR2) RETURN DATE IS
289
290 l_date DATE;
291 l_curr_year NUMBER;
292 l_curr_qtr NUMBER;
293 l_curr_period NUMBER;
294 l_week_start_date DATE;
295
296 BEGIN
297
298 IF(l_period_type = 'FII_TIME_ENT_YEAR' )
299 THEN
300 SELECT sequence
301 INTO l_curr_year
302 FROM fii_time_ent_year
303 WHERE l_as_of_date BETWEEN start_date AND end_date;
304
305 SELECT start_date
306 INTO l_date
307 FROM fii_time_ent_year
308 WHERE sequence = l_curr_year - 3;
309
310 END IF;
311
312 IF(l_period_type = 'FII_TIME_ENT_QTR' )
313 THEN
314 SELECT sequence,
315 ent_year_id
316 INTO l_curr_qtr, l_curr_year
317 FROM fii_time_ent_qtr
318 WHERE l_as_of_date BETWEEN start_date AND end_date;
319
320 IF(l_curr_qtr = 4)
321 THEN l_date := FII_TIME_API.Ent_Cyr_Start(l_as_of_date);
322 ELSE
323 SELECT start_date
324 INTO l_date
325 FROM fii_time_ent_qtr
326 WHERE sequence = l_curr_qtr + 1
327 AND ent_year_id = l_curr_year - 1;
328 END IF;
329 END IF;
330
331 IF(l_period_type = 'FII_TIME_ENT_PERIOD' )
332 THEN
333 SELECT p.sequence, q.ent_year_id
334 INTO l_curr_period, l_curr_year
335 FROM fii_time_ent_period p, fii_time_ent_qtr q
336 WHERE p.ent_qtr_id=q.ent_qtr_id
337 AND l_as_of_date BETWEEN p.start_date AND p.end_date;
338
339 SELECT p.start_date
340 INTO l_date
341 FROM fii_time_ent_period p, fii_time_ent_qtr q
342 WHERE p.ent_qtr_id = q.ent_qtr_id
343 AND p.sequence = l_curr_period + 1
344 AND q.ent_year_id = l_curr_year - 1;
345
346 /*INSERT INTO bim_param_test values('AOD:'||l_as_of_date,
347 'currperiod:'||l_curr_period,
348 'return date'||l_date,DBMS_UTILITY.get_time,NULL,NULL,null);
349 COMMIT;
350 */ END IF;
351
352 IF(l_period_type = 'FII_TIME_WEEK')
353 THEN
354 SELECT start_date
355 INTO l_week_start_date
356 FROM fii_time_week
357 WHERE l_as_of_date BETWEEN start_date AND end_date;
358
359 SELECT start_date
360 INTO l_date
361 FROM fii_time_week
362 WHERE start_date = l_week_start_date - 7 * 12;
363
364 END IF;
365
366 RETURN l_date;
367
368 EXCEPTION
369 WHEN OTHERS
370 THEN RETURN BIS_COMMON_PARAMETERS.Get_Global_Start_Date;
371
372 END;
373
374 FUNCTION Previous_Report_Start_Date(l_as_of_date DATE,
375 l_period_type VARCHAR2,
376 l_comp_type VARCHAR2) RETURN DATE IS
377
378 l_prev_date date;
379 l_date date;
380
381 BEGIN
382
383 l_prev_date := Previous_Period_Asof_Date(l_as_of_date, l_period_type, l_comp_type);
384
385 l_date := Current_Report_Start_Date(l_prev_date, l_period_type);
386
387 RETURN l_date;
388
389 END;
390
391
392 FUNCTION Previous_Period_Asof_Date( l_as_of_date DATE,
393 l_period_type VARCHAR2,
394 l_comp_type VARCHAR2) RETURN DATE IS
395
396 l_date date;
397
398 BEGIN
399
400 IF (l_comp_type = 'YEARLY') THEN
401 CASE l_period_type
402 WHEN 'FII_TIME_WEEK' THEN
403 l_date := FII_TIME_API.sd_lyswk(l_as_of_date);
404 WHEN 'FII_TIME_ENT_PERIOD' THEN
405 l_date := FII_TIME_API.ent_sd_lysper_end(l_as_of_date);
406 WHEN 'FII_TIME_ENT_QTR' THEN
407 l_date := FII_TIME_API.ent_sd_lysqtr_end(l_as_of_date);
408 WHEN 'FII_TIME_ENT_YEAR' THEN
409 l_date := FII_TIME_API.ent_sd_lyr_end(l_as_of_date);
410 ELSE
411 l_date := FII_TIME_API.ent_sd_lysqtr_end(l_as_of_date);
412 END CASE;
413 ELSIF (l_comp_type = 'SEQUENTIAL') THEN
414 CASE l_period_type
415 WHEN 'FII_TIME_WEEK' THEN
416 l_date := FII_TIME_API.sd_pwk(l_as_of_date);
417 WHEN 'FII_TIME_ENT_PERIOD' THEN
418 l_date := FII_TIME_API.ent_sd_pper_end(l_as_of_date);
419 WHEN 'FII_TIME_ENT_QTR' THEN
420 l_date := FII_TIME_API.ent_sd_pqtr_end(l_as_of_date);
421 WHEN 'FII_TIME_ENT_YEAR' THEN
422 l_date := FII_TIME_API.ent_sd_lyr_end(l_as_of_date);
423 ELSE
424 l_date := FII_TIME_API.ent_sd_pqtr_end(l_as_of_date);
425 END CASE;
426 END IF;
427
428 RETURN l_date;
429
430 EXCEPTION
431 WHEN OTHERS
432 THEN RETURN BIS_COMMON_PARAMETERS.Get_Global_Start_Date - 1; /* making sure it's < current_report_date */
433 END;
434
435 -- -------------------------------------------------------------------
436 -- Name: bil_pyr_end
437 -- Desc: Returns previous enterprise year end date.
438 -- Output: Previous Enterprise year end date.
439 -- --------------------------------------------------------------------
440 Function bil_pyr_end(as_of_date date, num_periods number) return DATE is
441 l_date date;
442 begin
443 select end_date
444 into l_date
448 from fii_time_ent_year
445 from fii_time_ent_year
446 where sequence =
447 (select sequence - num_periods
449 where as_of_date between start_date and end_date);
450
451 return l_date;
452 end;
453
454
455 -- -------------------------------------------------------------------
456 -- Name: bil_pper_end
457 -- Desc: Returns previous enterprise period end date.
458 -- Output: Previous Enterprise Period end date
459 -- --------------------------------------------------------------------
460 Function bil_pper_end(as_of_date date) return DATE is -- will get lastDay of prior''s prior as-of-date period
461 l_date date;
462 l_curr_period number;
463 l_curr_year number;
464 begin
465 select p.sequence, q.ent_year_id
466 into l_curr_period, l_curr_year
467 from fii_time_ent_period p, fii_time_ent_qtr q
468 where p.ent_qtr_id=q.ent_qtr_id
469 and as_of_date between p.start_date and p.end_date;
470
471 select p.end_date
472 into l_date
473 from fii_time_ent_period p, fii_time_ent_qtr q
474 where p.ent_qtr_id=q.ent_qtr_id
475 and p.sequence= l_curr_period
476 and q.ent_year_id= l_curr_year-2;
477
478
479 return l_date;
480 end;
481
482 -- -------------------------------------------------------------------
483 -- Name: bil_pqtr_end
484 -- Desc: Returns previous enterprise quarter end date.
485 -- Output: Previous enterprise quarter end date.
486 -- --------------------------------------------------------------------
487 Function bil_pqtr_end(as_of_date date) return DATE is
488 l_date date;
489 l_curr_qtr number;
490 l_curr_year number;
491 begin
492 /*
493 select sequence, ent_year_id
494 into l_curr_qtr, l_curr_year
495 from fii_time_ent_qtr
496 where as_of_date between start_date and end_date;
497
498 select end_date
499 into l_date
500 from fii_time_ent_qtr
501 where sequence=l_curr_qtr
502 and ent_year_id=l_curr_year-2;
503 */
504 select qtr2.end_date
505 into l_date
506 from fii_time_ent_qtr qtr1, fii_time_ent_qtr qtr2
507 where as_of_date between qtr1.start_date and qtr1.end_date
508 and qtr2.sequence = qtr1.sequence and qtr2.ent_year_id=qtr1.ent_year_id-2;
509
510
511 return l_date;
512 end;
513
514 PROCEDURE GET_TREND_PARAMS( p_page_period_type IN VARCHAR2,
515 p_comp_type IN VARCHAR2,
516 p_curr_as_of_date IN DATE,
517 p_table_name OUT NOCOPY VARCHAR2,
518 p_column_name OUT NOCOPY VARCHAR2,
519 p_curr_start_date OUT NOCOPY DATE,
520 p_prev_start_date OUT NOCOPY DATE,
521 p_prev_end_date OUT NOCOPY DATE,
522 p_series_name OUT NOCOPY VARCHAR2,
523 p_time_ids OUT NOCOPY VARCHAR2
524 )
525 IS
526 BEGIN
527 CASE
528 WHEN p_page_period_type = 'FII_TIME_ENT_YEAR' then
529 p_table_name := 'fii_time_ent_year';
530 p_column_name := 'ent_year_id';
531 p_time_ids := 'ent_year_id,ent_year_id';
532 -- p_curr_start_date := bil_pyr_end(p_curr_as_of_date, 4);
533 -- p_series_name := 'TO_CHAR(MOD(ent_year_id,100),''FM00'')';
534
535 WHEN p_page_period_type = 'FII_TIME_ENT_QTR' then
536 p_table_name := 'fii_time_ent_qtr';
537 p_column_name := 'ent_qtr_id';
538 p_time_ids := 'ent_year_id,ent_qtr_id';
539 -- p_curr_start_date := bil_pqtr_end(p_curr_as_of_date); --8 continuous quarters
540 -- p_series_name := 'FND_GLOBAL.LOCAL_CHR(81)||sequence||FND_GLOBAL.LOCAL_CHR(45)||TO_CHAR(MOD(ent_year_id,100),''FM00'')';
541 /* IF p_comp_type = 'YEARLY' then
542 p_curr_start_date := (fii_time_api.ent_lysqtr_end(p_curr_as_of_date)+1); -- lastDay+1 of prior's as-of-date period
543 p_prev_start_date := (fii_time_api.ent_lysqtr_end(p_curr_start_date)+1); -- lastDay+1 of prior''s prior as-of-date period
544 END IF; */
545
546 WHEN p_page_period_type = 'FII_TIME_ENT_PERIOD' then
547 p_table_name := 'fii_time_ent_period';
548 p_column_name := 'ent_period_id';
549 p_time_ids := 'ent_year_id,ent_period_id';
550 --l_curr_end_date := fii_time_api.ent_cper_end(p_curr_as_of_date); -- use &BIS_CURRENT_EFFECTIVE_END_DATE
551 -- p_curr_start_date := fii_time_api.ent_lysper_end(p_curr_as_of_date);
552 -- p_series_name := 'TO_CHAR(end_date,''Mon'')';
553
554 /* IF p_comp_type = 'YEARLY' then
555 p_curr_start_date := (fii_time_api.ent_lysper_end(p_curr_as_of_date)+1); --Last year same Enterprise period start date
556 p_prev_start_date := (bil_pper_end(p_curr_as_of_date)+1); -- lastDay+1 of prior''s prior as-of-date period
557 END IF; */
558
559 ELSE
560 --WHEN p_page_period_type = 'FII_TIME_WEEK' then
561 p_table_name := 'fii_time_week';
562 p_column_name := 'week_id';
563 p_time_ids := 'week_id,week_id';
564 -- p_curr_start_date := (fii_time_api.cwk_end(p_curr_as_of_date) - 91); -- use (as_of_date -13*7)
565 -- p_series_name := 'TO_CHAR(end_date,''DDMon'')';
566
567 /* IF p_comp_type = 'YEARLY' then
571 END CASE;
568 --p_curr_start_date := (fii_time_api.cwk_end(p_curr_as_of_date) - 91);
569 p_prev_start_date := (fii_time_api.lyswk_end(p_curr_as_of_date)-91);
570 END IF; */
572
573 /* IF (p_comp_type = 'YEARLY') THEN
574 CASE p_page_period_type
575 WHEN 'FII_TIME_WEEK' THEN
576 p_prev_end_date := FII_TIME_API.sd_lyswk(p_curr_as_of_date);
577 WHEN 'FII_TIME_ENT_PERIOD' THEN
578 p_prev_end_date := FII_TIME_API.ent_sd_lysper_end(p_curr_as_of_date);
579 WHEN 'FII_TIME_ENT_QTR' THEN
580 p_prev_end_date := FII_TIME_API.ent_sd_lysqtr_end(p_curr_as_of_date);
581 WHEN 'FII_TIME_ENT_YEAR' THEN
582 p_prev_end_date := FII_TIME_API.ent_sd_lyr_end(p_curr_as_of_date);
583 ELSE
584 p_prev_end_date := FII_TIME_API.ent_sd_lysqtr_end(p_curr_as_of_date);
585 END CASE;
586 ELSIF (p_comp_type = 'SEQUENTIAL') THEN
587 CASE p_page_period_type
588 WHEN 'FII_TIME_WEEK' THEN
589 p_prev_end_date := FII_TIME_API.sd_pwk(p_curr_as_of_date);
590 WHEN 'FII_TIME_ENT_PERIOD' THEN
591 p_prev_end_date := FII_TIME_API.ent_sd_pper_end(p_curr_as_of_date);
592 WHEN 'FII_TIME_ENT_QTR' THEN
593 p_prev_end_date := FII_TIME_API.ent_sd_pqtr_end(p_curr_as_of_date);
594 WHEN 'FII_TIME_ENT_YEAR' THEN
595 p_prev_end_date := FII_TIME_API.ent_sd_lyr_end(p_curr_as_of_date);
596 ELSE
597 p_prev_end_date := FII_TIME_API.ent_sd_pqtr_end(p_curr_as_of_date);
598 END CASE;
599 END IF; */
600
601 END GET_TREND_PARAMS;
602
603 FUNCTION GET_COLUMN_A(p_name IN VARCHAR2) RETURN VARCHAR2
604
605 IS
606 l_meaning Varchar2(50);
607 BEGIN
608 select al.meaning into l_meaning
609 from bim_r_code_definitions bc ,
610 as_sales_lead_ranks_vl al
611 where bc.object_def = al.rank_id
612 and bc.object_type = 'RANK_DBI' and column_name = p_name;
613 Return l_meaning;
614 END ;
615
616
617
618 function get_rpl_label(p_name in varchar2,pld in varchar2) return varchar2
619
620 is
621 l_rev Varchar2(50);
622 l_period varchar2(50);
623 l_per_lead varchar2(50);
624 BEGIN
625
626 /* PLD */
627 l_per_lead:='';
628
629 SELECT MEANING into l_rev
630 FROM FND_LOOKUP_VALUES
631 WHERE LOOKUP_TYPE = 'BIM_REVENUE'
632 AND lookup_code=fnd_profile.VALUE('BIM_REVENUE')
633 AND language = USERENV('LANG');
634
635
636 IF (pld ='N' AND (p_name = 'PTD' OR p_name = 'TOTAL') ) THEN
637
638 /* to get PTD revenue and Total revenue*/
639
640
641 SELECT MEANING into l_period
642 FROM FND_LOOKUP_VALUES
643 WHERE LOOKUP_TYPE = 'BIM_GEN_LOOKUP'
644 AND lookup_code=p_name
645 AND language = USERENV('LANG');
646
647 return l_period||' '||l_rev;
648
649 END IF;
650
651
652 if pld ='PLD' AND p_name ='N' THEN
653
654 /* to get <<revenue>> per lead and Total <<revenue>> per lead */
655
656 SELECT MEANING into l_per_lead
657 FROM FND_LOOKUP_VALUES
658 WHERE LOOKUP_TYPE = 'BIM_GEN_LOOKUP'
659 AND lookup_code=pld
660 AND language = USERENV('LANG');
661
662 return l_rev||' '||l_per_lead;
663
664 END IF;
665
666
667 if pld ='PLD' AND (p_name = 'PTD' OR p_name = 'TOTAL') THEN
668
669 /* to get PTD <<revenue>> per lead and Total <<revenue>> per lead */
670
671 SELECT MEANING into l_period
672 FROM FND_LOOKUP_VALUES
673 WHERE LOOKUP_TYPE = 'BIM_GEN_LOOKUP'
674 AND lookup_code=p_name
675 AND language = USERENV('LANG');
676
677
678
679 SELECT MEANING into l_per_lead
680 FROM FND_LOOKUP_VALUES
681 WHERE LOOKUP_TYPE = 'BIM_GEN_LOOKUP'
682 AND lookup_code=pld
683 AND language = USERENV('LANG');
684
685 return l_period||' '||l_rev||' '||l_per_lead;
686
687 END IF;
688
689 if pld ='FORE' and p_name ='N' THEN
690
691 /* to get <<revenue>> Forecast */
692
693 SELECT MEANING into l_per_lead
694 FROM FND_LOOKUP_VALUES
695 WHERE LOOKUP_TYPE = 'BIM_GEN_LOOKUP'
696 AND lookup_code=pld
697 AND language = USERENV('LANG');
698
699 return l_rev||' '||l_per_lead;
700
701 END IF;
702
703 if pld ='VAR' and p_name ='N' THEN
704
705 /* to get <<revenue>> variance */
706
707 SELECT MEANING into l_per_lead
708 FROM FND_LOOKUP_VALUES
709 WHERE LOOKUP_TYPE = 'BIM_GEN_LOOKUP'
710 AND lookup_code=pld
711 AND language = USERENV('LANG');
712
713 return l_rev||' '||l_per_lead ;
714
715 END IF;
716
717
718 end;
719
720
721
722
723 FUNCTION GET_LOOKUP_VALUE (code in varchar2) return VARCHAR2 IS
724
725 l_meaning varchar2(100) ;
726
727 CURSOR c_rid (code varchar2) IS
728 SELECT MEANING
729 FROM fnd_lookup_values
730 WHERE lookup_type = 'BIM_GEN_LOOKUP'
731 AND lookup_code =code
732 AND language = USERENV('LANG');
733
734 BEGIN
735
736 OPEN c_rid(code);
737 FETCH c_rid INTO l_meaning;
738 CLOSE c_rid;
739 return l_meaning;
740
741 END GET_LOOKUP_VALUE;
742
743
744 FUNCTION GET_CONTEXT_VIEWBY (code in varchar2) return VARCHAR2 IS
745
746 l_meaning varchar2(100) ;
747
748 CURSOR c_rid (code varchar2) IS
749 select a.name from bis_levels_tl a,bis_levels b
750 where
751 a.level_id = b.level_id
752 and b.short_name =code
753 and a.language=USERENV('LANG');
754
755 BEGIN
756
757 OPEN c_rid(code);
758 FETCH c_rid INTO l_meaning;
759 CLOSE c_rid;
760 return l_meaning;
761
762 END GET_CONTEXT_VIEWBY;
763
764
765
766 END BIM_PMV_DBI_UTL_PKG;
[Home] [Help]