[Home] [Help]
PACKAGE BODY: APPS.FII_AR_BILL_ACT_TREND_PKG
Source
1 PACKAGE BODY FII_AR_BILL_ACT_TREND_PKG AS
2 /* $Header: FIIARDBIBATB.pls 120.3.12000000.2 2007/04/09 20:22:27 vkazhipu ship $ */
3
4 -- This package will provide sql statements to retrieve data for Billing Activity
5
6
7 FUNCTION get_view_by return VARCHAR2 IS
8 BEGIN
9
10 IF (fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK') THEN
11 return FND_MESSAGE.GET_STRING('FII','FII_AR_DBI_WEEK');
12 ELSIF (fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_PERIOD') THEN
13 return FND_MESSAGE.GET_STRING('FII','FII_AR_DBI_MONTH');
14 ELSIF (fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') THEN
15 return FND_MESSAGE.GET_STRING('FII','FII_AR_DBI_QUARTER');
16 ELSE
17 return FND_MESSAGE.GET_STRING('FII','FII_AR_DBI_YEAR');
18 END IF;
19
20 END get_view_by;
21
22
23 PROCEDURE get_billing_act_trend (
24 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, bill_act_trend_sql out NOCOPY VARCHAR2,
25 bill_trend_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
26
27 l_sql_stmt VARCHAR2(30000);
28
29 l_org_where VARCHAR2(30);
30 l_industry_where VARCHAR2(240);
31 l_where_clause VARCHAR2(1000);
32 l_child_party_where VARCHAR2(60);
33
34
35 l_time_dim_ltc VARCHAR2(120);
36 l_period_type VARCHAR2(30);
37
38 l_start_date VARCHAR2(30);
39 l_end_date VARCHAR2(30);
40
41 BEGIN
42
43
44 /* Reset Global Variables */
45 fii_ar_util_pkg.reset_globals;
46
47 /* Get the parameters that the user has selected */
48 fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
49
50 /* Populate the dimension combination(s) that the user has access to */
51 fii_ar_util_pkg.populate_summary_gt_tables;
52
53
54
55 IF (fii_ar_util_pkg.g_party_id <> '-111') THEN
56 l_child_party_where := ' AND f.party_id = cal.party_id ';
57 END IF;
58
59
60 IF fii_ar_util_pkg.g_industry_id <> '-111' THEN
61
62 l_industry_where := ' and f.class_code=cal.class_code and f.class_category=cal.class_category';
63
64 END IF;
65
66 l_org_where := ' and f.org_id=cal.org_id';
67
68 -- The below mentioned variable will make the code easy to understand.
69 l_where_clause := l_child_party_where||l_org_where||l_industry_where;
70
71 /* Find out which time dimension level table needs to be hit.
72 This is based on the period type chosen */
73 l_period_type := fii_ar_util_pkg.g_page_period_type;
74
75
76 IF ( l_period_type = 'FII_TIME_WEEK') THEN
77 l_time_dim_ltc := 'FII_TIME_WEEK';
78 ELSIF (l_period_type = 'FII_TIME_ENT_PERIOD') THEN
79 l_time_dim_ltc := 'FII_TIME_ENT_PERIOD';
80 ELSIF (l_period_type = 'FII_TIME_ENT_QTR') THEN
81 l_time_dim_ltc := 'FII_TIME_ENT_QTR';
82 ELSE
83 l_time_dim_ltc := 'FII_TIME_ENT_YEAR';
84 END IF;
85
86
87 l_sql_stmt := 'SELECT per.name VIEWBY,
88 per.name FII_AR_VIEWBY,
89 TO_CHAR(per.end_date,''DD/MM/YYYY'') FII_AR_PERIOD_END_DATE,
90 SUM(FII_AR_BILL_ACT_AMT) FII_AR_BILL_ACT_AMT,
91 SUM(FII_AR_BILL_ACT_COUNT) FII_AR_BILL_ACT_COUNT,
92 SUM(FII_AR_BILL_ACT_AMT_PRIOR) FII_AR_BILL_ACT_AMT_PRIOR ,
93 SUM(FII_AR_BILL_ACT_COUNT_PRIOR) FII_AR_BILL_ACT_COUNT_PRIOR ,
94 DECODE(nvl(SUM(FII_AR_BILL_ACT_AMT),0),0,NULL,
95 DECODE(SIGN(per.end_date - :ASOF_DATE),1,
96 ''&pFunctionName=FII_AR_BILL_ACT_TRX_CLASS&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'',
97 ''AS_OF_DATE=FII_AR_PERIOD_END_DATE&pFunctionName=FII_AR_BILL_ACT_TRX_CLASS&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'')) FII_AR_BILL_ACT_AMT_DRILL
98 FROM ( SELECT cal.name NAME ,
99 cal.end_date END_DATE,
100 cal.start_date START_DATE,
101 cal.sequence SEQUENCE, ';
102
103 IF l_period_type = 'FII_TIME_ENT_YEAR' THEN
104 l_sql_stmt := l_sql_stmt ||' f.inv_ba_amount+f.dm_ba_amount+f.cb_ba_amount
105 +f.br_ba_amount+f.dep_ba_amount + f.cm_ba_amount FII_AR_BILL_ACT_AMT,
106 f.inv_ba_count+f.dm_ba_count+f.cb_ba_count
107 +f.br_ba_count+f.dep_ba_count + f.cm_ba_count FII_AR_BILL_ACT_COUNT,
108 NULL FII_AR_BILL_ACT_AMT_PRIOR,
109 NULL FII_AR_BILL_ACT_COUNT_PRIOR ';
110 l_start_date := ' :SD_PRIOR ';
111
112 ELSE
113 l_sql_stmt := l_sql_stmt ||' CASE WHEN cal.report_date >= :SD_SDATE THEN
114 f.inv_ba_amount+f.dm_ba_amount+f.cb_ba_amount
115 +f.br_ba_amount+f.dep_ba_amount + f.cm_ba_amount
116 ELSE
117 NULL
118 END FII_AR_BILL_ACT_AMT,
119 CASE WHEN cal.report_date >= :SD_SDATE THEN
120 f.inv_ba_count+f.dm_ba_count+f.cb_ba_count
121 +f.br_ba_count+f.dep_ba_count + f.cm_ba_count
122 ELSE
123 NULL
124 END FII_AR_BILL_ACT_COUNT, ';
125 IF fii_ar_util_pkg.g_time_comp = 'SEQUENTIAL' THEN
126 l_sql_stmt := l_sql_stmt || 'NULL FII_AR_BILL_ACT_AMT_PRIOR,
127 NULL FII_AR_BILL_ACT_COUNT_PRIOR ';
128 l_start_date := ' :SD_PRIOR ';
129 ELSE
130 l_sql_stmt := l_sql_stmt ||' CASE WHEN cal.report_date < :SD_SDATE THEN
131 f.inv_ba_amount+f.dm_ba_amount+f.cb_ba_amount
132 +f.br_ba_amount+f.dep_ba_amount + f.cm_ba_amount
133 ELSE
134 NULL
135 END FII_AR_BILL_ACT_AMT_PRIOR,
136 CASE WHEN cal.report_date < :SD_SDATE THEN
137 f.inv_ba_count+f.dm_ba_count+f.cb_ba_count
138 +f.br_ba_count+f.dep_ba_count + f.cm_ba_count
139 ELSE
140 NULL
141 END FII_AR_BILL_ACT_COUNT_PRIOR ';
142 END IF;
143 l_start_date := ' :SD_PRIOR_PRIOR ';
144 END IF;
145
146 l_sql_stmt:= l_sql_stmt || ' FROM fii_ar_billing_act'||fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix ||' f,
147 (SELECT /*+no_merge INDEX(cal FII_TIME_STRUCTURES_N1) leading(gt) cardinality(gt 1)*/ *
148 FROM fii_ar_summary_gt gt,
149 fii_time_structures cal,
150 '||l_time_dim_ltc||' per
151 WHERE
152 BITAND(cal.record_type_id, :BITAND) = :BITAND
153 and per.end_date = cal.report_date
154 and per.start_date < ';
155
156 IF fii_ar_util_pkg.g_as_of_date = fii_ar_util_pkg.g_curr_per_end THEN
157 l_end_date := ' :ASOF_DATE';
158 ELSE
159 l_end_date := ' :CURR_PERIOD_START';
160 END IF;
161
162 l_sql_stmt := l_sql_stmt || l_end_date || ' AND per.start_date >= '|| l_start_date ||') cal ';
163
164 l_sql_stmt := l_sql_stmt ||' WHERE f.time_id=cal.time_id
165 AND f.period_type_id=cal.period_type_id ' ||l_where_clause;
166
167
168 IF fii_ar_util_pkg.g_as_of_date <> fii_ar_util_pkg.g_curr_per_end THEN
169 l_sql_stmt := l_sql_stmt||' UNION ALL
170 SELECT per.name NAME,
171 per.end_date END_DATE,
172 per.start_date START_DATE,
173 per.sequence SEQUENCE,
174 f.inv_ba_amount+f.dm_ba_amount+f.cb_ba_amount
175 +f.br_ba_amount+f.dep_ba_amount + f.cm_ba_amount FII_AR_BILL_ACT_AMT,
176 f.inv_ba_count+f.dm_ba_count+f.cb_ba_count
177 +f.br_ba_count+f.dep_ba_count + f.cm_ba_count FII_AR_BILL_ACT_COUNT,
178 NULL FII_AR_BILL_ACT_AMT_PRIOR,
179 NULL FII_AR_BILL_ACT_COUNT_PRIOR
180 FROM '||l_time_dim_ltc ||' per,
181 fii_ar_billing_act'||fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix ||' f,
182 ( SELECT /*+no_merge leading(gt) cardinality(gt 1)*/ *
183 FROM fii_time_structures cal,
184 fii_ar_summary_gt gt
185 WHERE cal.report_date = :ASOF_DATE
186 AND BITAND(cal.record_type_id, :BITAND) = :BITAND ) cal
187 WHERE f.time_id=cal.time_id
188 AND f.period_type_id=cal.period_type_id
189 AND per.end_date = :CURR_PERIOD_END '|| l_where_clause;
190 END IF;
191
192 l_sql_stmt := l_sql_stmt|| ' ) inline_view , '||l_time_dim_ltc||' per
193 WHERE per.start_date > :SD_PRIOR
194 AND per.start_date <= :ASOF_DATE
195 AND per.sequence=inline_view.sequence(+)
196 GROUP BY per.name,per.end_date,per.start_date
197 ORDER BY per.start_date ';
198
199 /* Pass back the pmv sql along with bind variables to PMV */
200 fii_ar_util_pkg.bind_variable(l_sql_stmt, p_page_parameter_tbl, bill_act_trend_sql, bill_trend_output);
201
202
203
204
205 END get_billing_act_trend;
206
207
208
209 END ;
210