PACKAGE BODY: APPS.BIX_PMV_AI_OUTGR_PRTLT_PKG

Source

1 PACKAGE BODY BIX_PMV_AI_OUTGR_PRTLT_PKG AS
2 /*$Header: bixioutp.plb 120.0 2005/05/25 17:15:59 appldev noship $ */
3 
4 PROCEDURE GET_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
5                   p_custom_sql         OUT NOCOPY VARCHAR2,
6                   p_custom_output      OUT NOCOPY bis_query_attributes_TBL
7                   )
8 AS
9  l_sqltext		VARCHAR2(32000) ;
10  l_where_clause		VARCHAR2(1000) ;
11  l_mv			VARCHAR2 (240);
12  l_view_by_select	VARCHAR2(500) ;
13  l_comp_type				VARCHAR2(500) ;
14  l_xtd						VARCHAR2(500) ;
15  l_join_tbl		poa_dbi_util_pkg.poa_dbi_join_tbl;
16  l_func_area		VARCHAR2(5);
17  l_mv_set		VARCHAR2(3);
18  l_version		VARCHAR2(3);
19  l_timetype		VARCHAR2(3);
20  l_filter_where         VARCHAR2 (2000);
21  l_view_by		VARCHAR2(120);
22 
23 BEGIN
24  l_func_area	:= 'IOUTP';
25  l_mv_set	:= 'ITM';
26  l_version	:= NULL;
27  l_timetype	:= 'XTD';
28 
29 p_custom_output  := BIS_QUERY_ATTRIBUTES_TBL();
30 
31 -- Get the parameters
32 
33 l_join_tbl                 := poa_dbi_util_pkg.poa_dbi_join_tbl ();
34 
35 bix_pmv_dbi_utl_pkg.process_parameters
36 ( p_param               => p_page_parameter_tbl
37 , p_trend		=> 'N'
38 , p_func_area		=> l_func_area
39 , p_version             => l_version
40 , p_mv_set              => l_mv_set
41 , p_where_clause        => l_where_clause
42 , p_mv                  => l_mv
43 , p_join_tbl            => l_join_tbl
44 , p_comp_type           => l_comp_type
45 , p_xtd					=> l_xtd
46 , p_view_by_select      => l_view_by_select
47 , p_view_by		=> l_view_by
48 );
49 
50 l_sqltext := '
51          SELECT meaning BIX_PMV_AI_OUTCOME,
52 	           sum(pper) BIX_PMV_AI_PPER,
53 			 sum(cper) BIX_PMV_AI_CPER
54          FROM (
55              SELECT lookup.meaning meaning,
56                     nvl(sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
57                            decode(lookup.lookup_code,
58                                       ''SR'',   AGENT_SR_CREATED,
59                                       ''LEAD'', AGENT_LEADS_CREATED,
60                                       ''OPP'',  AGENT_OPPORTUNITIES_CREATED,
61                                    0),
62                      0)),0) PPER,
63                     nvl(sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
64                            decode(lookup.lookup_code,
65                                       ''SR'',   AGENT_SR_CREATED,
66                                       ''LEAD'', AGENT_LEADS_CREATED,
67                                       ''OPP'',  AGENT_OPPORTUNITIES_CREATED,
68                                    0),
69                      0)),0) CPER
70               FROM bix_ai_call_details_mv fact,
71                    fii_time_rpt_struct cal,
72                    (
73                     select lookup_code,meaning
74                     from fnd_lookup_values_vl
75                     where lookup_type = :l_lookup_type
76                     ) lookup
77               WHERE fact.time_id        = cal.time_id
78               AND   fact.period_type_id = cal.period_type_id
79               AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) =
80                                         cal.record_type_id
81               AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE,
82                                       &BIS_PREVIOUS_ASOF_DATE) ';
83 
84 l_sqltext := l_sqltext || l_where_clause ||
85              '
86 		    GROUP BY lookup.meaning
87 		    UNION ALL
88 		    SELECT meaning, 0, 0
89 		    from fnd_lookup_values_vl
90 		    where lookup_type = :l_lookup_type
91 		    )
92            GROUP BY meaning '
93                 ;
94 p_custom_sql:=l_sqltext;
95 bix_pmv_dbi_utl_pkg.get_bind_vars (p_custom_output,p_func_area => l_func_area);
96 
97 
98 EXCEPTION
99 	WHEN OTHERS THEN
100 	RAISE;
101 END GET_SQL;
102 END BIX_PMV_AI_OUTGR_PRTLT_PKG;