[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_EMC_VBAG_PRTLT_PKG
Source
1 PACKAGE BODY BIX_PMV_EMC_VBAG_PRTLT_PKG AS
2 /*$Header: bixevbap.plb 115.13 2003/11/22 01:46:31 djambula 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_as_of_date DATE;
11 l_period_type varchar2(2000);
12 l_record_type_id NUMBER;
13 l_comp_type varchar2(2000);
14 l_account varchar2(32000);
15 l_sql_errm varchar2(32000);
16 l_period_from DATE;
17 l_period_to DATE;
18 l_where_clause varchar2(32000);
19 l_other_account VARCHAR2(1000);
20 l_custom_rec BIS_QUERY_ATTRIBUTES := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
21 l_classification VARCHAR2(32000);
22 l_view_by varchar2(1000);
23 l_row_type varchar2(10) := 'AC';
24
25 BEGIN
26 --
27 --Initialize p_custom_output
28 --
29 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
30
31 bix_pmv_dbi_utl_pkg.get_emc_page_params (p_page_parameter_tbl,
32 l_as_of_date,
33 l_period_type,
34 l_record_type_id,
35 l_comp_type,
36 l_account,
37 l_classification,
38 l_view_by
39 );
40
41
42 -- If the account is not 'All'
43
44 IF l_account IS NOT NULL THEN
45 l_where_clause := 'AND email_account_id IN (:l_account) ';
46 END IF;
47
48
49 IF l_classification IS NOT NULL THEN
50 l_where_clause := l_where_clause || ' AND email_classification_id IN (:l_classification) ';
51 END IF;
52
53 l_other_account := FND_MESSAGE.GET_STRING('BIX','BIX_PMV_ALLACCT');
54
55 IF l_other_account IS NULL OR l_other_account = 'BIX_PMV_ALLACCT'
56 THEN
57 l_other_account := 'Others';
58 END IF;
59
60 l_sqltext := '
61 SELECT DECODE(greatest(RANKING,10),10,EMC_ACCOUNT,:l_other_account) BIX_EMC_ACCOUNT,
62 NVL(SUM(CURR_RCVD),0) BIX_EMC_RCVD,
63 NVL(SUM(CURR_REPLD),0) BIX_EMC_REPLD
64 FROM (
65 SELECT account.value EMC_ACCOUNT,
66 sum(EMAILS_OFFERED_IN_PERIOD) CURR_RCVD,
67 NVL(sum(EMAILS_REPLIED_IN_PERIOD),0) + NVL(SUM(EMAILS_AUTO_REPLIED_IN_PERIOD),0) CURR_REPLD,
68 RANK() OVER (ORDER BY nvl(sum(EMAILS_OFFERED_IN_PERIOD),0) DESC,
69 account.value
70 ) RANKING
71 FROM bix_email_details_mv fact,
72 fii_time_rpt_struct cal,
73 bix_email_accounts_v account
74 WHERE fact.time_id = cal.time_id
75 AND fact.row_type = :l_row_type
76 AND fact.period_type_id = cal.period_type_id
77 AND cal.report_date = &BIS_CURRENT_ASOF_DATE
78 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id
79 AND fact.email_account_id = account.id ';
80 l_sqltext := l_sqltext || l_where_clause || ' GROUP BY account.value
81 )
82 GROUP BY DECODE(greatest(RANKING,10),10,EMC_ACCOUNT,:l_other_account),
83 DECODE(greatest(RANKING,10),10,RANKING,11),
84 DECODE(greatest(RANKING,10),10,1,2)
85 ORDER BY DECODE(greatest(RANKING,10),10,1,2),
86 DECODE(greatest(RANKING,10),10,RANKING,11), 1 ';
87
88 p_custom_sql := l_sqltext;
89
90 l_custom_rec.attribute_name := ':l_other_account' ;
91 l_custom_rec.attribute_value:= l_other_account;
92 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
93 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
94
95 p_custom_output.Extend();
96 p_custom_output(p_custom_output.count) := l_custom_rec;
97
98 IF ( l_account IS NOT NULL) THEN
99 l_custom_rec.attribute_name := ':l_account' ;
100 l_custom_rec.attribute_value:= l_account;
101 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
102 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
103
104 p_custom_output.Extend();
105 p_custom_output(p_custom_output.count) := l_custom_rec;
106 END IF;
107
108 IF ( l_classification IS NOT NULL) THEN
109 l_custom_rec.attribute_name := ':l_classification' ;
110 l_custom_rec.attribute_value:= l_classification;
111 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
112 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
113
114 p_custom_output.Extend();
115 p_custom_output(p_custom_output.count) := l_custom_rec;
116 END IF;
117
118 l_custom_rec.attribute_name := ':l_row_type' ;
119 l_custom_rec.attribute_value:= l_row_type;
120 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
121 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
122
123 p_custom_output.Extend();
124 p_custom_output(p_custom_output.count) := l_custom_rec;
125
126 EXCEPTION
127 WHEN OTHERS THEN
128 NULL;
129 END GET_SQL;
130 END BIX_PMV_EMC_VBAG_PRTLT_PKG;