[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_REV_BB_TREND_PKG
Source
1 PACKAGE BODY ISC_DBI_REV_BB_TREND_PKG AS
2 /* $Header: ISCRGBCB.pls 120.1 2006/06/26 07:13:26 abhdixi 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 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_suffix VARCHAR2(120);
27 l_invalid_curr BOOLEAN;
28 l_custom_rec BIS_QUERY_ATTRIBUTES;
29
30
31 BEGIN
32
33 l_invalid_curr := FALSE;
34
35
36 FOR i IN 1..p_param.COUNT
37 LOOP
38
39 IF (p_param(i).parameter_name = 'PERIOD_TYPE')
40 THEN l_period_type := p_param(i).parameter_value;
41 END IF;
42
43 IF(p_param(i).parameter_name = 'ORGANIZATION+JTF_ORG_SALES_GROUP') THEN
44 l_sgid := p_param(i).parameter_id;
45 END IF;
46
47 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT') THEN
48 l_prod_cat := p_param(i).parameter_id;
49 END IF;
50
51 IF(p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS') THEN
52 l_cust := p_param(i).parameter_id;
53 END IF;
54
55 IF(p_param(i).parameter_name = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS') THEN
56 l_class := p_param(i).parameter_id;
57 END IF;
58
59 IF (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
60 THEN l_curr := p_param(i).parameter_id;
61 END IF;
62
63 END LOOP;
64
65 IF (l_curr = '''FII_GLOBAL1''')
66 THEN l_curr_suffix := 'g';
67 ELSIF (l_curr = '''FII_GLOBAL2''')
68 THEN l_curr_suffix := 'g1';
69 ELSE
70 l_invalid_curr := TRUE;
71 END IF;
72
73 IF l_period_type = 'FII_TIME_ENT_YEAR'
74 THEN l_rev_book := 'booked_rev_yr_'||l_curr_suffix;
75 ELSIF l_period_type = 'FII_TIME_ENT_QTR'
76 THEN l_rev_book := 'booked_rev_qr_'||l_curr_suffix;
77 ELSIF l_period_type = 'FII_TIME_ENT_PERIOD'
78 THEN l_rev_book := 'booked_rev_pe_'||l_curr_suffix;
79 ELSE -- l_period_type = 'FII_TIME_WEEK'
80 l_rev_book := 'booked_rev_wk_'||l_curr_suffix;
81 END IF;
82
83 l_sg_sg := to_number(replace(substr(l_sgid,instr(l_sgid,'.') + 1),''''));
84 l_sg_res := to_number(replace(substr(l_sgid,1,instr(l_sgid,'.') - 1),''''));
85
86 IF (l_sg_res IS NULL) -- when a sales group is chosen
87 THEN
88 l_sg_where := '
89 AND f.sales_grp_id = (&ORGANIZATION+JTF_ORG_SALES_GROUP)
90 AND f.resource_id IS NULL';
91 ELSE -- when the LOV parameter is a SRep (no need to go through the SG hierarchy MV
92 l_sg_where := '
93 AND f.sales_grp_id = :ISC_SG
94 AND f.resource_id = :ISC_RES';
95 END IF;
96
97 IF (l_cust IS NULL)
98 THEN
99 l_cust_where := '';
100 IF (l_class IS NULL)
101 THEN l_cust_flag := 3; -- all
102 ELSE l_cust_flag := 1; -- customer classification
103 END IF;
104 ELSE
105 l_cust_where := '
106 AND f.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
107 l_cust_flag := 0; -- customer
108 END IF;
109
110 IF (l_class IS NULL) THEN
111 l_class_where:='';
112 ELSE
113 l_class_where :='
114 AND f.class_code in (&FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS)';
115 END IF;
116
117 IF (l_cust IS NULL AND l_class IS NULL) THEN -- use double rollup without cust
118 l_flags := '';
119 l_mv := 'ISC_DBI_SCR_002_MV';
120 l_prod_cat_from := ''; -- do not need to join to denorm table
121 IF (l_prod_cat IS NULL) THEN
122 l_prod_cat_where :='
123 AND f.cat_top_node_flag = ''Y''';
124 ELSE -- view by sales group, prod.cat selected
125 l_prod_cat_where :='
126 AND f.item_category_id IN (&ITEM+ENI_ITEM_VBH_CAT)';
127 END IF;
128
129 ELSE -- use single rollup with customer dimension
130 l_flags := '
131 AND f.item_cat_flag = :ISC_ITEM_CAT_FLAG
132 AND f.customer_flag = :ISC_CUST';
133 l_mv := 'ISC_DBI_SCR_001_MV';
134 IF (l_prod_cat IS NULL)
135 THEN l_prod_cat_from := '';
136 l_prod_cat_where := '';
137 ELSE
138 l_prod_cat_from := ',
139 ENI_DENORM_HIERARCHIES eni_cat,
140 MTL_DEFAULT_CATEGORY_SETS mdcs';
141 l_prod_cat_where := '
142 AND f.item_category_id = eni_cat.child_id
143 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
144 AND eni_cat.dbi_flag = ''Y''
145 AND eni_cat.object_type = ''CATEGORY_SET''
146 AND eni_cat.object_id = mdcs.category_set_id
147 AND mdcs.functional_area_id = 11';
148 END IF;
149 END IF;
150
151 IF (l_prod_cat IS NULL)
152 THEN l_item_cat_flag := 1; -- All
153 ELSE l_item_cat_flag := 0; -- Product Category
154 END IF;
155
156
157 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
158 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
159
160 IF (l_invalid_curr)
161 THEN l_stmt := '
162 /* Unsupported currency */
163 SELECT 0 ISC_MEASURE_2,
164 0 ISC_MEASURE_1,
165 0 ISC_MEASURE_3,
166 0 ISC_MEASURE_8,
167 0 ISC_MEASURE_7,
168 0 ISC_MEASURE_9,
169 0 ISC_MEASURE_10,
170 0 ISC_MEASURE_11,
171 0 ISC_MEASURE_12,
172 0 ISC_MEASURE_5,
173 0 ISC_MEASURE_4,
174 0 ISC_MEASURE_6
175
176 FROM dual
177 WHERE 1 = 2';
178
179 ELSE
180
181 l_stmt := '
182 SELECT fii.name VIEWBY,
183 nvl(s.p_net_book, 0) ISC_MEASURE_2, -- Prior (Net Booked)
184 nvl(s.c_net_book, 0) ISC_MEASURE_1, -- Net Booked
185 (s.c_net_book - s.p_net_book)
186 / decode(s.p_net_book, 0, NULL,
187 abs(s.p_net_book)) * 100 ISC_MEASURE_3, -- Change (Net Booked)
188 nvl(s.p_rev_rec, 0) ISC_MEASURE_8, -- Prior (Revenue)
189 nvl(s.c_rev_rec, 0) ISC_MEASURE_7, -- Revenue
190 (s.c_rev_rec - s.p_rev_rec)
191 / decode(s.p_rev_rec, 0, NULL,
192 abs(s.p_rev_rec)) * 100 ISC_MEASURE_9, -- Change (Revenue)
193 nvl(s.p_rev_book, 0) ISC_MEASURE_10, -- Prior (Rev Booked this Per)
194 nvl(s.c_rev_book, 0) ISC_MEASURE_11, -- Revenue Booked this Period
195 (s.c_rev_book - s.p_rev_book)
196 / decode(s.p_rev_book, 0, null,
197 abs(s.p_rev_book)) *100 ISC_MEASURE_12, -- Change (Rev Booked this Per)
198 nvl(s.p_rev_backlog, 0) ISC_MEASURE_5, -- Prior (Revenue Backlog)
199 nvl(s.c_rev_backlog, 0) ISC_MEASURE_4, -- Revenue Backlog
200 (s.c_rev_backlog - s.p_rev_backlog)
201 / decode(s.p_rev_backlog, 0, NULL,
202 abs(s.p_rev_backlog)) * 100 ISC_MEASURE_6 /* Change (Revenue Backlog) */
203
204 FROM (SELECT start_date START_DATE,
205 sum(c_book_xtd) C_NET_BOOK,
206 sum(p_book_xtd) P_NET_BOOK,
207 sum(c_backlog) + sum(c_defer_rev) C_REV_BACKLOG,
208 sum(p_backlog) + sum(p_defer_rev) P_REV_BACKLOG,
209 sum(c_rev_rec_xtd) C_REV_REC,
210 sum(p_rev_rec_xtd) P_REV_REC,
211 sum(c_rev_book_xtd) C_REV_BOOK,
212 sum(p_rev_book_xtd) P_REV_BOOK
213 FROM /* Compute XTD components */
214 (SELECT dates.start_date START_DATE,
215 decode(dates.period, ''C'',
216 nvl(net_booked_amt_'||l_curr_suffix||', 0), 0) C_BOOK_XTD,
217 decode(dates.period, ''P'',
218 nvl(net_booked_amt_'||l_curr_suffix||', 0), 0) P_BOOK_XTD,
219 decode(dates.period, ''C'',
220 nvl(recognized_amt_'||l_curr_suffix||', 0), 0) C_REV_REC_XTD,
221 decode(dates.period, ''P'',
222 nvl(recognized_amt_'||l_curr_suffix||', 0), 0) P_REV_REC_XTD,
223 decode(dates.period, ''C'',
224 nvl('||l_rev_book||', 0), 0) C_REV_BOOK_XTD,
225 decode(dates.period, ''P'',
226 nvl('||l_rev_book||', 0), 0) P_REV_BOOK_XTD,
227 0 C_BACKLOG,
228 0 P_BACKLOG,
229 0 C_DEFER_REV,
230 0 P_DEFER_REV
231 FROM (SELECT fii.start_date START_DATE,
232 ''C'' PERIOD,
233 least(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
234 FROM '||l_period_type||' fii
235 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
236 AND &BIS_CURRENT_ASOF_DATE
237 UNION ALL
238 SELECT p2.start_date START_DATE,
239 ''P'' PERIOD,
240 p1.report_date REPORT_DATE
241 FROM (SELECT least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE,
242 rownum ID
243 FROM '||l_period_type||' fii
244 WHERE fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
245 AND &BIS_PREVIOUS_ASOF_DATE
246 ORDER BY fii.start_date DESC) p1,
247 (SELECT fii.start_date START_DATE,
248 rownum ID
249 FROM '||l_period_type||' fii
250 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
251 AND &BIS_CURRENT_ASOF_DATE
252 ORDER BY fii.start_date DESC) p2
253 WHERE p1.id(+) = p2.id) dates,
254 '||l_mv||' f,
255 FII_TIME_RPT_STRUCT_V cal'
256 ||l_prod_cat_from||'
257 WHERE cal.report_date = dates.report_date
258 AND f.time_id = cal.time_id
259 AND f.period_type_id = cal.period_type_id
260 AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'
261 ||l_flags
262 ||l_sg_where||l_prod_cat_where||l_cust_where||l_class_where||'
263 UNION ALL /* Compute ITD components */
264 SELECT dates.start_date START_DATE,
265 0 C_BOOK_XTD,
266 0 P_BOOK_XTD,
267 0 C_REV_REC_XTD,
268 0 P_REV_REC_XTD,
269 0 C_REV_BOOK_XTD,
270 0 P_REV_BOOK_XTD,
271 decode(dates.period, ''C'',
272 nvl(backlog_amt_'||l_curr_suffix||', 0), 0) C_BACKLOG,
273 decode(dates.period, ''P'',
274 nvl(backlog_amt_'||l_curr_suffix||', 0), 0) P_BACKLOG,
275 decode(dates.period, ''C'',
276 nvl(deferred_amt_'||l_curr_suffix||', 0), 0) C_DEFER_REV,
277 decode(dates.period, ''P'',
278 nvl(deferred_amt_'||l_curr_suffix||', 0), 0) P_DEFER_REV
279 FROM (SELECT fii.start_date START_DATE,
280 ''C'' PERIOD,
281 least(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
282 FROM '||l_period_type||' fii
283 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
284 AND &BIS_CURRENT_ASOF_DATE
285 UNION ALL
286 SELECT p2.start_date START_DATE,
287 ''P'' PERIOD,
288 p1.report_date REPORT_DATE
289 FROM (SELECT least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE,
290 rownum ID
291 FROM '||l_period_type||' fii
292 WHERE fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
293 AND &BIS_PREVIOUS_ASOF_DATE
294 ORDER BY fii.start_date DESC) p1,
295 (SELECT fii.start_date START_DATE,
296 rownum ID
297 FROM '||l_period_type||' fii
298 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
299 AND &BIS_CURRENT_ASOF_DATE
300 ORDER BY fii.start_date DESC) p2
301 WHERE p1.id(+) = p2.id) dates,
302 '||l_mv||' f,
303 FII_TIME_RPT_STRUCT_V cal'
304 ||l_prod_cat_from||'
305 WHERE cal.report_date = dates.report_date
306 AND f.time_id = cal.time_id
307 AND f.period_type_id = cal.period_type_id
308 AND bitand(cal.record_type_id, 1143) = cal.record_type_id'
309 ||l_flags
310 ||l_sg_where||l_prod_cat_where||l_cust_where||l_class_where||')
311 GROUP BY start_date) s,
312 '||l_period_type||' fii
313 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
314 AND &BIS_CURRENT_ASOF_DATE
315 AND fii.start_date = s.start_date(+)
316 ORDER BY fii.start_date';
317
318 END IF;
319
320 x_custom_sql := l_stmt;
321
322 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.View_By_Value;
323 l_custom_rec.attribute_value := 'TIME+'||l_period_type;
324 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
325 x_custom_output.extend;
326 x_custom_output(1) := l_custom_rec;
327
328 l_custom_rec.attribute_name := ':ISC_CUST';
329 l_custom_rec.attribute_value := l_cust_flag;
330 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
331 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
332 x_custom_output.EXTEND;
333 x_custom_output(2) := l_custom_rec;
334
335 l_custom_rec.attribute_name := ':ISC_SG';
336 l_custom_rec.attribute_value := to_char(l_sg_sg);
337 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
338 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
339 x_custom_output.extend;
340 x_custom_output(3) := l_custom_rec;
341
342 l_custom_rec.attribute_name := ':ISC_RES';
343 l_custom_rec.attribute_value := to_char(l_sg_res);
344 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
345 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
346 x_custom_output.extend;
347 x_custom_output(4) := l_custom_rec;
348
349 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
350 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
351 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
352 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
353 x_custom_output.extend;
354 x_custom_output(5) := l_custom_rec;
355
356 END get_sql;
357
358 END ISC_DBI_REV_BB_TREND_PKG;