[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_REV_BOOK_TREND_PKG
Source
1 PACKAGE BODY ISC_DBI_REV_BOOK_TREND_PKG AS
2 /* $Header: ISCRGBHB.pls 120.0 2005/05/25 17:36:38 appldev noship $ */
3
4 PROCEDURE GET_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,x_custom_sql OUT NOCOPY VARCHAR2,
5 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
6
7 l_stmt VARCHAR2(32000);
8 l_period_type VARCHAR2(32000);
9 l_rev_book VARCHAR2(32000);
10 l_sgid VARCHAR2(32000);
11 l_sg_where VARCHAR2(32000);
12 l_prod_cat VARCHAR2(32000);
13 l_prod_cat_from VARCHAR2(32000);
14 l_prod_cat_where VARCHAR2(32000);
15 l_cust VARCHAR2(32000);
16 l_cust_where VARCHAR2(32000);
17 l_class VARCHAR2(32000);
18 l_class_where VARCHAR2(32000);
19 l_sg_sg NUMBER;
20 l_sg_res NUMBER;
21 l_item_cat_flag NUMBER;
22 l_cust_flag NUMBER := 0; -- 0 for customer, 1 for cust class, 3 for all
23 l_flags VARCHAR2(32000);
24 l_mv VARCHAR2(100);
25 l_curr VARCHAR2(10000);
26 l_curr_g VARCHAR2(15) := '''FII_GLOBAL1''';
27 l_curr_g1 VARCHAR2(15) := '''FII_GLOBAL2''';
28 l_curr_suffix VARCHAR2(120);
29 l_invalid_curr BOOLEAN := FALSE;
30 l_custom_rec BIS_QUERY_ATTRIBUTES;
31
32
33 BEGIN
34
35 FOR i IN 1..p_param.COUNT
36 LOOP
37
38 IF (p_param(i).parameter_name = 'PERIOD_TYPE')
39 THEN l_period_type := p_param(i).parameter_value;
40 END IF;
41
42 IF(p_param(i).parameter_name = 'ORGANIZATION+JTF_ORG_SALES_GROUP') THEN
43 l_sgid := p_param(i).parameter_id;
44 END IF;
45
46 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT') THEN
47 l_prod_cat := p_param(i).parameter_id;
48 END IF;
49
50 IF(p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS') THEN
51 l_cust := p_param(i).parameter_id;
52 END IF;
53
54 IF(p_param(i).parameter_name = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS') THEN
55 l_class := p_param(i).parameter_id;
56 END IF;
57
58 IF (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
59 THEN l_curr := p_param(i).parameter_id;
60 END IF;
61 END LOOP;
62
63 IF (l_curr = l_curr_g)
64 THEN l_curr_suffix := 'g';
65 ELSIF (l_curr = l_curr_g1)
66 THEN l_curr_suffix := 'g1';
67 ELSE
68 l_invalid_curr := TRUE;
69 END IF;
70
71 IF l_period_type = 'FII_TIME_ENT_YEAR'
72 THEN l_rev_book := 'booked_rev_yr_'||l_curr_suffix;
73 ELSIF l_period_type = 'FII_TIME_ENT_QTR'
74 THEN l_rev_book := 'booked_rev_qr_'||l_curr_suffix;
75 ELSIF l_period_type = 'FII_TIME_ENT_PERIOD'
76 THEN l_rev_book := 'booked_rev_pe_'||l_curr_suffix;
77 ELSE -- l_period_type = 'FII_TIME_WEEK'
78 l_rev_book := 'booked_rev_wk_'||l_curr_suffix;
79 END IF;
80
81 l_sg_sg := to_number(replace(substr(l_sgid,instr(l_sgid,'.') + 1),''''));
82 l_sg_res := to_number(replace(substr(l_sgid,1,instr(l_sgid,'.') - 1),''''));
83
84 IF (l_sg_res IS NULL) -- when a sales group is chosen
85 THEN
86 l_sg_where := '
87 AND f.sales_grp_id = (&ORGANIZATION+JTF_ORG_SALES_GROUP)
88 AND f.resource_id IS NULL';
89 ELSE -- when the LOV parameter is a SRep (no need to go through the SG hierarchy MV
90 l_sg_where := '
91 AND f.sales_grp_id = :ISC_SG
92 AND f.resource_id = :ISC_RES';
93 END IF;
94
95 IF (l_cust IS NULL)
96 THEN
97 l_cust_where := '';
98 IF (l_class IS NULL)
99 THEN l_cust_flag := 3; -- all
100 ELSE l_cust_flag := 1; -- customer classification
101 END IF;
102 ELSE
103 l_cust_where := '
104 AND f.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
105 l_cust_flag := 0; -- customer
106 END IF;
107
108 IF (l_class IS NULL) THEN
109 l_class_where:='';
110 ELSE
111 l_class_where :='
112 AND f.class_code in (&FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS)';
113 END IF;
114
115 IF (l_cust IS NULL AND l_class IS NULL) THEN -- use double rollup without cust
116 l_flags := '';
117 l_mv := 'ISC_DBI_SCR_002_MV';
118 l_prod_cat_from := ''; -- do not need to join to denorm table
119 IF (l_prod_cat IS NULL) THEN
120 l_prod_cat_where :='
121 AND f.cat_top_node_flag = ''Y''';
122 ELSE -- view by sales group, prod.cat selected
123 l_prod_cat_where :='
124 AND f.item_category_id IN (&ITEM+ENI_ITEM_VBH_CAT)';
125 END IF;
126
127 ELSE -- use single rollup with customer dimension
128 l_flags := '
129 AND f.item_cat_flag = :ISC_ITEM_CAT_FLAG
130 AND f.customer_flag = :ISC_CUST';
131 l_mv := 'ISC_DBI_SCR_001_MV';
132 IF (l_prod_cat IS NULL)
133 THEN l_prod_cat_from := '';
134 l_prod_cat_where := '';
135 ELSE
136 l_prod_cat_from := ',
137 ENI_DENORM_HIERARCHIES eni_cat,
138 MTL_DEFAULT_CATEGORY_SETS mdcs';
139 l_prod_cat_where := '
140 AND f.item_category_id = eni_cat.child_id
141 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
142 AND eni_cat.dbi_flag = ''Y''
143 AND eni_cat.object_type = ''CATEGORY_SET''
144 AND eni_cat.object_id = mdcs.category_set_id
145 AND mdcs.functional_area_id = 11';
146 END IF;
147 END IF;
148
149 IF (l_prod_cat IS NULL)
150 THEN l_item_cat_flag := 1; -- All
151 ELSE l_item_cat_flag := 0; -- Product Category
152 END IF;
153
154 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
155 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
156
157 IF (l_invalid_curr)
158 THEN l_stmt := '
159 /* Unsupported currency */
160 SELECT 0 ISC_MEASURE_1,
161 0 ISC_MEASURE_2,
162 0 ISC_MEASURE_3,
163 0 ISC_MEASURE_4,
164 0 ISC_MEASURE_5,
165 0 ISC_MEASURE_6
166 FROM dual
167 WHERE 1 = 2';
168
169 ELSE
170
171 l_stmt := '
172 SELECT fii.name VIEWBY,
173 nvl(s.c_book_xtd, 0) ISC_MEASURE_1, -- Net Booked
174 (s.c_book_xtd - s.p_book_xtd)
175 / decode(s.p_book_xtd, 0, NULL,
176 abs(s.p_book_xtd)) * 100 ISC_MEASURE_2, -- Change (Net Booked)
177 nvl(s.c_rev_rec_xtd, 0) ISC_MEASURE_3, -- Revenue
178 (s.c_rev_rec_xtd - s.p_rev_rec_xtd)
179 / decode(s.p_rev_rec_xtd, 0, NULL,
180 abs(s.p_rev_rec_xtd)) * 100 ISC_MEASURE_4, -- Change (Revenue)
181 nvl(s.c_rev_book_xtd, 0) ISC_MEASURE_5, -- Revenue Booked this Period
182 (s.c_rev_book_xtd - s.p_rev_book_xtd)
183 / decode(s.p_rev_book_xtd, 0, null,
184 abs(s.p_rev_book_xtd)) *100 ISC_MEASURE_6 -- Change (Rev Booked this Per)
185 FROM (SELECT dates.start_date START_DATE,
186 sum(decode(dates.period, ''C'',
187 nvl(net_booked_amt_'||l_curr_suffix||', 0), 0)) C_BOOK_XTD,
188 sum(decode(dates.period, ''P'',
189 nvl(net_booked_amt_'||l_curr_suffix||', 0), 0)) P_BOOK_XTD,
190 sum(decode(dates.period, ''C'',
191 nvl(recognized_amt_'||l_curr_suffix||', 0), 0)) C_REV_REC_XTD,
192 sum(decode(dates.period, ''P'',
193 nvl(recognized_amt_'||l_curr_suffix||', 0), 0)) P_REV_REC_XTD,
194 sum(decode(dates.period, ''C'',
195 nvl('||l_rev_book||', 0), 0)) C_REV_BOOK_XTD,
196 sum(decode(dates.period, ''P'',
197 nvl('||l_rev_book||', 0), 0)) P_REV_BOOK_XTD
198 FROM (SELECT fii.start_date START_DATE,
199 ''C'' PERIOD,
200 least(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
201 FROM '||l_period_type||' fii
202 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
203 AND &BIS_CURRENT_ASOF_DATE
204 UNION ALL
205 SELECT p2.start_date START_DATE,
206 ''P'' PERIOD,
207 p1.report_date REPORT_DATE
208 FROM (SELECT least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE,
209 rownum ID
210 FROM '||l_period_type||' fii
211 WHERE fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
212 AND &BIS_PREVIOUS_ASOF_DATE
213 ORDER BY fii.start_date DESC) p1,
214 (SELECT fii.start_date START_DATE,
215 rownum ID
216 FROM '||l_period_type||' fii
217 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
218 AND &BIS_CURRENT_ASOF_DATE
219 ORDER BY fii.start_date DESC) p2
220 WHERE p1.id(+) = p2.id) dates,
221 '||l_mv||' f,
222 FII_TIME_RPT_STRUCT_V cal'
223 ||l_prod_cat_from||'
224 WHERE cal.report_date = dates.report_date
225 AND f.time_id = cal.time_id
226 AND f.period_type_id = cal.period_type_id
227 AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'
228 ||l_flags
229 ||l_sg_where||l_prod_cat_where||l_cust_where||l_class_where||'
230 GROUP BY start_date) s,
231 '||l_period_type||' fii
232 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
233 AND &BIS_CURRENT_ASOF_DATE
234 AND fii.start_date = s.start_date(+)
235 ORDER BY fii.start_date';
236
237 END IF;
238
239 x_custom_sql := l_stmt;
240
241 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.View_By_Value;
242 l_custom_rec.attribute_value := 'TIME+'||l_period_type;
243 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
244 x_custom_output.extend;
245 x_custom_output(1) := l_custom_rec;
246
247 l_custom_rec.attribute_name := ':ISC_CUST';
248 l_custom_rec.attribute_value := l_cust_flag;
249 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
250 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
251 x_custom_output.EXTEND;
252 x_custom_output(2) := l_custom_rec;
253
254 l_custom_rec.attribute_name := ':ISC_SG';
255 l_custom_rec.attribute_value := to_char(l_sg_sg);
256 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
257 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
258 x_custom_output.extend;
259 x_custom_output(3) := l_custom_rec;
260
261 l_custom_rec.attribute_name := ':ISC_RES';
262 l_custom_rec.attribute_value := to_char(l_sg_res);
263 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
264 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
265 x_custom_output.extend;
266 x_custom_output(4) := l_custom_rec;
267
268 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
269 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
270 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
271 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
272 x_custom_output.extend;
273 x_custom_output(5) := l_custom_rec;
274
275 END get_sql;
276
277 END ISC_DBI_REV_BOOK_TREND_PKG;