[Home] [Help]
PACKAGE BODY: APPS.FII_AR_SG_PROD_REV_PKG
Source
1 PACKAGE BODY fii_ar_sg_prod_rev_pkg AS
2 /* $Header: FIIARSGPRB.pls 120.1 2005/07/01 13:34:50 arcdixit noship $ */
3
4 PROCEDURE get_sg_prod_rev
5 ( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
6 get_sg_prod_rev_sql out NOCOPY VARCHAR2,
7 get_sg_prod_rev_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)IS
8
9 -- declaration section
10
11 sqlstmt VARCHAR2(10000);
12 get_sg_prod_rev_rec BIS_QUERY_ATTRIBUTES;
13 l_curr VARCHAR2(100);
14 l_sgid VARCHAR2(100);
15 l_prod_cat VARCHAR2(100);
16 l_cust VARCHAR2(100);
17 l_view_by VARCHAR2(100);
18 l_period_type VARCHAR2(100);
19 l_as_of_date DATE;
20 l_record_type_id NUMBER;
21 l_one_period_back DATE;
22 l_two_period_back DATE;
23 l_three_period_back DATE;
24 l_inner_sql VARCHAR2(10000);
25 l_sg_where VARCHAR2(240);
26 l_prod_cat_from VARCHAR2(240);
27 l_prod_cat_where VARCHAR2(1000);
28 l_cust_where VARCHAR2(240);
29 l_viewby_col VARCHAR2(200);
30 l_sg_sg NUMBER;
31 l_sg_res NUMBER;
32 l_cat_join VARCHAR2(50);
33 l_mv_to_be_used VARCHAR2(100);
34 l_curr_g VARCHAR2(15) := '''FII_GLOBAL1''';
35 l_curr_suffix VARCHAR2(120);
36 l_record_id NUMBER;
37 l_pertype_for_booked VARCHAR2(120);
38 l_cust_flag NUMBER;
39 l_item_cat_flag NUMBER;
40 l_flags VARCHAR2(120);
41
42 l_order varchar2(200);
43 l_sort varchar2(200);
44
45 BEGIN
46
47 -- Retrieve parameter info
48
49 FII_AR_Util.reset_globals;
50 FII_AR_Util.Get_Parameters(p_page_parameter_tbl);
51
52 l_curr := FII_AR_Util.p_curr;
53 l_sgid:= FII_AR_Util.p_sgid;
54 l_prod_cat := FII_AR_Util.p_prod_cat;
55 l_cust := FII_AR_Util.p_cust;
56 l_view_by := FII_AR_Util.p_view_by;
57 l_period_type := FII_AR_Util.p_period_type;
58 l_as_of_date := FII_AR_Util.p_as_of_date;
59 l_record_type_id := FII_AR_Util.p_record_type_id;
60
61
62
63
64 IF (p_page_parameter_tbl.count > 0) THEN
65 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
66 IF p_page_parameter_tbl(i).parameter_name = 'ORDERBY' THEN
67 l_order := p_page_parameter_tbl(i).parameter_value;
68 END IF;
69 END LOOP;
70 END IF;
71
72
73 -- If primary global currency chosen, then, use recognized_amt_g else use recognized_amt_g1
74
75 IF (l_curr = l_curr_g)
76 THEN l_curr_suffix := 'g';
77 ELSE l_curr_suffix := 'g1';
78 END IF;
79
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 IF l_view_by = 'ORGANIZATION+JTF_ORG_SALES_GROUP'
87 THEN
88 l_sg_where := '
89 AND f.parent_grp_id = (&ORGANIZATION+JTF_ORG_SALES_GROUP)
90 AND f.grp_marker <> ''TOP GROUP'''; -- exclude the top groups when ViewBy = Sales Group
91 ELSE -- other view bys
92 l_sg_where := '
93 AND f.sales_grp_id = (&ORGANIZATION+JTF_ORG_SALES_GROUP)
94 AND f.resource_id IS NULL';
95 END IF;
96 ELSE -- when the LOV parameter is a Sales Rep
97 l_sg_where := '
98 AND f.sales_grp_id = :FII_SG_SG
99 AND f.resource_id = :FII_SG_RES ';
100 END IF;
101
102
103 IF (l_cust IS NULL) THEN
104 l_cust_where:='';
105
106 IF(l_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
107 l_cust_flag := 0;
108 ELSE
109 l_cust_flag := 1; -- do not need customer id
110 END IF;
111 ELSE
112 l_cust_where :='
113 AND f.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
114 l_cust_flag := 0; -- customer level
115 END IF;
116
117
118 IF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT' OR l_prod_cat IS NOT NULL)
119 THEN l_item_cat_flag := 0; -- Product Category
120 ELSE l_item_cat_flag := 1; -- All
121 END IF;
122
123
124
125 IF (l_view_by <> 'CUSTOMER+FII_CUSTOMERS' AND l_cust IS NULL) THEN -- use double rollup MV without customer dimension
126
127 l_mv_to_be_used := 'ISC_DBI_SCR_002_MV';
128 IF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') THEN
129
130 l_prod_cat_from := ',
131 ENI_DENORM_HIERARCHIES eni_cat,
132 MTL_DEFAULT_CATEGORY_SETS mdcs';
133 IF (l_prod_cat IS NULL) THEN
134 l_prod_cat_where := '
135 AND f.cat_top_node_flag = ''Y''
136 AND f.item_category_id = eni_cat.imm_child_id
137 AND eni_cat.top_node_flag = ''Y''
138 AND eni_cat.dbi_flag = ''Y''
139 AND eni_cat.object_type = ''CATEGORY_SET''
140 AND eni_cat.object_id = mdcs.category_set_id
141 AND mdcs.functional_area_id = 11';
142 ELSE l_prod_cat_where := '
143 AND f.item_category_id = eni_cat.imm_child_id
144 AND ((eni_cat.leaf_node_flag = ''N'' and
145 eni_cat.child_id <> eni_cat.parent_id and imm_child_id = child_id)
146 OR (eni_cat.leaf_node_flag = ''Y''))
147 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
148 AND eni_cat.dbi_flag = ''Y''
149 AND eni_cat.object_type = ''CATEGORY_SET''
150 AND eni_cat.object_id = mdcs.category_set_id
151 AND mdcs.functional_area_id = 11';
152 END IF;
153
154 ELSE -- view by <> category
155
156 l_prod_cat_from := '';
157
158 IF (l_prod_cat IS NULL) THEN --
159 l_prod_cat_where :=' AND f.cat_top_node_flag = ''Y''';
160 ELSE -- view by sales group, product category selected
161 l_prod_cat_where :=' AND f.item_category_id IN (&ITEM+ENI_ITEM_VBH_CAT)';
162 END IF;
163
164 END IF;
165
166 ELSE -- use single rollup with customer dimension
167
168 l_flags := '
169 AND f.item_cat_flag = :FII_ITEM_CAT_FLAG
170 AND f.customer_flag = :FII_CUST ';
171
172
173 l_mv_to_be_used := 'ISC_DBI_SCR_001_MV';
174 IF (l_prod_cat IS NULL) THEN
175 IF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') THEN
176 l_prod_cat_from := ',
177 ENI_DENORM_HIERARCHIES eni_cat,
178 MTL_DEFAULT_CATEGORY_SETS mdcs';
179 l_prod_cat_where := '
180 AND f.item_category_id = eni_cat.child_id
181 AND eni_cat.top_node_flag = ''Y''
182 AND eni_cat.dbi_flag = ''Y''
183 AND eni_cat.object_type = ''CATEGORY_SET''
184 AND eni_cat.object_id = mdcs.category_set_id
185 AND mdcs.functional_area_id = 11';
186 ELSE
187 l_prod_cat_from := '';
188 l_prod_cat_where := '';
189 END IF;
190
191 ELSE -- a prod cat has been selected
192 l_prod_cat_from := ',
193 ENI_DENORM_HIERARCHIES eni_cat,
194 MTL_DEFAULT_CATEGORY_SETS mdcs';
195 l_prod_cat_where := '
196 AND f.item_category_id = eni_cat.child_id
197 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
198 AND eni_cat.dbi_flag = ''Y''
199 AND eni_cat.object_type = ''CATEGORY_SET''
200 AND eni_cat.object_id = mdcs.category_set_id
201 AND mdcs.functional_area_id = 11';
202
203 END IF;
204
205 END IF;
206
207 -- CASE statement below assigns dates to different variables(in order to go one,two or three periods back)
208
209 CASE l_period_type
210
211 WHEN 'FII_TIME_WEEK' THEN
212 l_one_period_back := NULL;
213 l_two_period_back := NULL;
214 l_three_period_back := NULL;
215 l_record_id := 1;
216 l_pertype_for_booked := 'wk';
217
218 WHEN 'FII_TIME_ENT_PERIOD' THEN
219 l_one_period_back:= NULL;
220 l_two_period_back := NULL;
221 l_three_period_back:= NULL;
222 l_record_id := 1;
223 l_pertype_for_booked := 'pe';
224
225 WHEN 'FII_TIME_ENT_QTR' THEN
226 l_one_period_back := fii_time_api.ent_pper_end (l_as_of_date);
227 l_two_period_back := fii_time_api.ent_pper_end (l_one_period_back);
228 l_three_period_back := NULL;
229 l_record_id := 64;
230 l_pertype_for_booked := 'qr';
231
232 WHEN 'FII_TIME_ENT_YEAR' THEN
233 l_one_period_back := fii_time_api.ent_pqtr_end(l_as_of_date);
234 l_two_period_back := fii_time_api.ent_pqtr_end(l_one_period_back);
235 l_three_period_back:= fii_time_api.ent_pqtr_end(l_two_period_back);
236 l_record_id := 128;
237 l_pertype_for_booked := 'yr';
238
239 END CASE;
240
241
242
243 IF (l_view_by = 'ORGANIZATION+JTF_ORG_SALES_GROUP') THEN
244
245 l_viewby_col :='resource_id, sales_grp_id';
246
247 ELSIF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') THEN
248
249 IF (l_prod_cat IS NULL) THEN
250
251 l_viewby_col := 'parent_id';
252 ElSE
253 l_viewby_col :='imm_child_id';
254 END IF;
255
256 ELSIF (l_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
257
258 l_viewby_col :='customer_id';
259 END IF;
260
261
262 -- l_order := ORDER_BY_CLAUSE;
263
264 IF INSTR(l_order,'ASC') > 0
265 THEN l_sort := 'ASC';
266 ELSE
267 l_sort := 'DESC';
268 END IF;
269
270 /*----------------------------------------------------------------------------------------------+
271 | VIEWBY - Either Sales Group / Product Category / Customer |
272 | VIEWBYID - Either sales group id / product category id / customer id |
273 | FII_SALES_GROUP_DRILL - Drill on Sales Group |
274 | FII_PROD_CAT_DRILL - Drill on Product Category |
275 | FII_HIST_COL1 - First column for historical data |
276 | FII_HIST_COL2 - Second column for historical data |
277 | FII_HIST_COL3 - Third column for historical data |
278 | FII_HIST_COL4 - Fourth column for historical data |
279 | FII_XTD_REV - Period-to-date Revenue amount |
280 | FII_PRIOR_XTD_REV - Prior Period-to-date Revenue amount |
281 | FII_XTD_BOOKED - Period-to-date Booked amount |
282 | FII_PRIOR_XTD_BOOKED - Prior Period-to-date Booked amount |
283 | FII_GT_HIST_COL1 - Grand Total of FII_HIST_COL1 |
284 | FII_GT_HIST_COL2 - Grand Total of FII_HIST_COL2 |
285 | FII_GT_HIST_COL3 - Grand Total of FII_HIST_COL3 |
286 | FII_GT_HIST_COL4 - Grand Total of FII_HIST_COL4 |
287 | FII_GT_XTD_REV - Grand Total of FII_XTD_REV |
288 | FII_GT_CHANGE - Grand Total of Change column for revenue amount |
289 | FII_GT_XTD_BOOKED - Grand Total of FII_XTD_BOOKED |
290 | FII_GT_CHANGE_BOOKED - Grand Total of Change column for Booked amount |
291 +---------------------------------------------------------------------------------------------*/
292
293 -- Construct the inner sql (to be used inside the main sql)
294
295 -- for period type week and period, we do not show historical data so no need to do any UNION ALL. Here, we just get
296 -- values for PTD revenue, prior period PTD revenue(used to calculate change),
297 -- Booked revenue for current period and booked revenue for prior period(for change calculation)
298
299 CASE l_period_type
300
301 WHEN 'FII_TIME_WEEK' THEN
302
303 l_inner_sql := ' SUM(FII_HIST_COL1) FII_HIST_COL1,
304 SUM(FII_HIST_COL2) FII_HIST_COL2,
305 SUM(FII_HIST_COL3) FII_HIST_COL3,
306 SUM(FII_HIST_COL4) FII_HIST_COL4,
307 SUM(FII_XTD_REV) FII_XTD_REV,
308 SUM(FII_CHANGE) FII_CHANGE,
309 SUM(FII_PRIOR_XTD_REV) FII_PRIOR_XTD_REV,
310 SUM(FII_XTD_BOOKED) FII_XTD_BOOKED,
311 SUM(FII_CHANGE_BOOKED) FII_CHANGE_BOOKED,
312 SUM(FII_PRIOR_XTD_BOOKED) FII_PRIOR_XTD_BOOKED,
313 SUM(FII_BOOKED_PRIOR_XTD) FII_BOOKED_PRIOR_XTD,
314 SUM(FII_BOOKED_PRIOR_CHANGE) FII_BOOKED_PRIOR_CHANGE,
315 SUM(FII_GT_HIST_COL1) FII_GT_HIST_COL1,
316 SUM(FII_GT_HIST_COL2) FII_GT_HIST_COL2,
317 SUM(FII_GT_HIST_COL3) FII_GT_HIST_COL3,
318 SUM(FII_GT_HIST_COL4) FII_GT_HIST_COL4,
319 SUM(FII_GT_XTD_REV) FII_GT_XTD_REV,
320 SUM(FII_GT_XTD_BOOKED) FII_GT_XTD_BOOKED,
321 SUM(FII_GT_CHANGE) FII_GT_CHANGE,
322 SUM(FII_GT_CHANGE_BOOKED) FII_GT_CHANGE_BOOKED,
323 SUM(FII_GT_XTD_PRIOR_BOOKED) FII_GT_XTD_PRIOR_BOOKED,
324 SUM(FII_GT_PRIOR_BOOKED_CHANGE) FII_GT_PRIOR_BOOKED_CHANGE
325 FROM(
326 SELECT '||l_viewby_col||',
327 SUM(FII_HIST_COL1) FII_HIST_COL1,
328 SUM(FII_HIST_COL2) FII_HIST_COL2,
329 SUM(FII_HIST_COL3) FII_HIST_COL3,
330 SUM(FII_HIST_COL4) FII_HIST_COL4,
331 SUM(FII_XTD_REV) FII_XTD_REV,
332 (((SUM(FII_XTD_REV) - SUM(PRIOR_REV)) / DECODE(SUM(PRIOR_REV),
333 0,NULL,SUM(PRIOR_REV))) * 100) FII_CHANGE,
334 SUM(PRIOR_REV) FII_PRIOR_XTD_REV,
335 SUM(FII_XTD_BOOKED) FII_XTD_BOOKED,
336 (((SUM(FII_XTD_BOOKED) - SUM(PRIOR_BOOKED)) / DECODE(SUM(PRIOR_BOOKED),0,
337 NULL,SUM(PRIOR_BOOKED))) * 100) FII_CHANGE_BOOKED,
338 SUM(PRIOR_BOOKED) FII_PRIOR_XTD_BOOKED,
339 (SUM(FII_XTD_REV) - SUM(FII_XTD_BOOKED)) FII_BOOKED_PRIOR_XTD,
340 ((((SUM(FII_XTD_REV) - SUM(FII_XTD_BOOKED)) - (SUM(PRIOR_REV)- SUM(PRIOR_BOOKED))) / DECODE((SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)),
341 0,NULL,(SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)))) * 100) FII_BOOKED_PRIOR_CHANGE,
342 SUM(SUM(FII_HIST_COL1)) OVER () FII_GT_HIST_COL1,
343 SUM(SUM(FII_HIST_COL2)) OVER () FII_GT_HIST_COL2,
344 SUM(SUM(FII_HIST_COL3)) OVER () FII_GT_HIST_COL3,
345 SUM(SUM(FII_HIST_COL4)) OVER () FII_GT_HIST_COL4,
346 SUM(SUM(FII_XTD_REV)) OVER () FII_GT_XTD_REV,
347 SUM(SUM(FII_XTD_BOOKED)) OVER () FII_GT_XTD_BOOKED,
348 ((SUM(SUM(FII_XTD_REV)) OVER () - SUM(SUM(PRIOR_REV)) OVER ()) / DECODE(SUM(SUM(PRIOR_REV)) OVER (),
349 0,NULL,SUM(SUM(PRIOR_REV)) OVER ()) * 100) FII_GT_CHANGE,
350 ((SUM(SUM(FII_XTD_BOOKED)) OVER () - SUM(SUM(PRIOR_BOOKED)) OVER ()) / DECODE(SUM(SUM(PRIOR_BOOKED)) OVER (),
351 0,NULL,SUM(SUM(PRIOR_BOOKED)) OVER ()) * 100) FII_GT_CHANGE_BOOKED,
352 SUM(SUM(FII_XTD_REV) - SUM(FII_XTD_BOOKED)) OVER () FII_GT_XTD_PRIOR_BOOKED,
353 (((SUM(SUM(FII_XTD_REV) - SUM(FII_XTD_BOOKED)) OVER() - SUM(SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)) OVER()) / DECODE(SUM(SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)) OVER(),
354 0,NULL,SUM(SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)) OVER())) * 100) FII_GT_PRIOR_BOOKED_CHANGE,
355 ( rank() over (ORDER BY SUM(FII_XTD_REV) '||l_sort||' nulls last, '||l_viewby_col||')) - 1 rnk
356 FROM (
357
358 SELECT '||l_viewby_col||',
359 0 FII_HIST_COL1,
360 0 FII_HIST_COL2,
361 0 FII_HIST_COL3,
362 0 FII_HIST_COL4,
363
364 SUM(CASE WHEN cal.report_date in (&BIS_CURRENT_ASOF_DATE)
365 then f.recognized_amt_'||l_curr_suffix||' ELSE 0 END) FII_XTD_REV,
366 SUM(CASE WHEN cal.report_date in (&BIS_PREVIOUS_ASOF_DATE)
367 then f.recognized_amt_'||l_curr_suffix||' ELSE 0 END) PRIOR_REV,
368 SUM(CASE WHEN cal.report_date in (&BIS_CURRENT_ASOF_DATE)
369 then f.booked_rev_wk_'||l_curr_suffix||' ELSE 0 END) FII_XTD_BOOKED,
370 SUM(CASE WHEN cal.report_date in (&BIS_PREVIOUS_ASOF_DATE)
371 then f.booked_rev_wk_'||l_curr_suffix||' ELSE 0 END) PRIOR_BOOKED
372
373 FROM '||l_mv_to_be_used ||' f,
374 fii_time_structures cal'
375 ||l_prod_cat_from||'
376
377 WHERE f.time_id = cal.time_id
378 AND f.period_type_id = cal.period_type_id
379 AND bitand(cal.record_type_id,:RECORD_TYPE_ID) = :RECORD_TYPE_ID
380 AND cal.report_date in ((&BIS_PREVIOUS_ASOF_DATE),(&BIS_CURRENT_ASOF_DATE))
381 AND f.recognized_amt_'||l_curr_suffix||' <> 0
382 '||l_flags||l_sg_where||l_prod_cat_where||l_cust_where||'
383
384 GROUP BY '||l_viewby_col||') c group by '||l_viewby_col||') f,' ;
385
386 WHEN 'FII_TIME_ENT_PERIOD' THEN
387
388 l_inner_sql := '
389 SUM(FII_HIST_COL1) FII_HIST_COL1,
390 SUM(FII_HIST_COL2) FII_HIST_COL2,
391 SUM(FII_HIST_COL3) FII_HIST_COL3,
392 SUM(FII_HIST_COL4) FII_HIST_COL4,
393 SUM(FII_XTD_REV) FII_XTD_REV,
394 SUM(FII_CHANGE) FII_CHANGE,
395 SUM(FII_PRIOR_XTD_REV) FII_PRIOR_XTD_REV,
396 SUM(FII_XTD_BOOKED) FII_XTD_BOOKED,
397 SUM(FII_CHANGE_BOOKED) FII_CHANGE_BOOKED,
398 SUM(FII_PRIOR_XTD_BOOKED) FII_PRIOR_XTD_BOOKED,
399 SUM(FII_BOOKED_PRIOR_XTD) FII_BOOKED_PRIOR_XTD,
400 SUM(FII_BOOKED_PRIOR_CHANGE) FII_BOOKED_PRIOR_CHANGE,
401 SUM(FII_GT_HIST_COL1) FII_GT_HIST_COL1,
402 SUM(FII_GT_HIST_COL2) FII_GT_HIST_COL2,
403 SUM(FII_GT_HIST_COL3) FII_GT_HIST_COL3,
404 SUM(FII_GT_HIST_COL4) FII_GT_HIST_COL4,
405 SUM(FII_GT_XTD_REV) FII_GT_XTD_REV,
406 SUM(FII_GT_XTD_BOOKED) FII_GT_XTD_BOOKED,
407 SUM(FII_GT_CHANGE) FII_GT_CHANGE,
408 SUM(FII_GT_CHANGE_BOOKED) FII_GT_CHANGE_BOOKED,
409 SUM(FII_GT_XTD_PRIOR_BOOKED) FII_GT_XTD_PRIOR_BOOKED,
410 SUM(FII_GT_PRIOR_BOOKED_CHANGE) FII_GT_PRIOR_BOOKED_CHANGE
411 FROM(
412 SELECT '||l_viewby_col||',
413 SUM(FII_HIST_COL1) FII_HIST_COL1,
414 SUM(FII_HIST_COL2) FII_HIST_COL2,
415 SUM(FII_HIST_COL3) FII_HIST_COL3,
416 SUM(FII_HIST_COL4) FII_HIST_COL4,
417 SUM(FII_XTD_REV) FII_XTD_REV,
418 (((SUM(FII_XTD_REV) - SUM(PRIOR_REV)) / DECODE(SUM(PRIOR_REV),
419 0,NULL,SUM(PRIOR_REV))) * 100) FII_CHANGE,
420 SUM(PRIOR_REV) FII_PRIOR_XTD_REV,
421 SUM(FII_XTD_BOOKED) FII_XTD_BOOKED,
422 (((SUM(FII_XTD_BOOKED) - SUM(PRIOR_BOOKED)) / DECODE(SUM(PRIOR_BOOKED),0,
423 NULL,SUM(PRIOR_BOOKED))) * 100) FII_CHANGE_BOOKED,
424 SUM(PRIOR_BOOKED) FII_PRIOR_XTD_BOOKED,
425 (SUM(FII_XTD_REV) - SUM(FII_XTD_BOOKED)) FII_BOOKED_PRIOR_XTD,
426 ((((SUM(FII_XTD_REV) - SUM(FII_XTD_BOOKED)) - (SUM(PRIOR_REV)- SUM(PRIOR_BOOKED))) / DECODE((SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)),
427 0,NULL,(SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)))) * 100) FII_BOOKED_PRIOR_CHANGE,
428 SUM(SUM(FII_HIST_COL1)) OVER () FII_GT_HIST_COL1,
429 SUM(SUM(FII_HIST_COL2)) OVER () FII_GT_HIST_COL2,
430 SUM(SUM(FII_HIST_COL3)) OVER () FII_GT_HIST_COL3,
431 SUM(SUM(FII_HIST_COL4)) OVER () FII_GT_HIST_COL4,
432 SUM(SUM(FII_XTD_REV)) OVER () FII_GT_XTD_REV,
433 SUM(SUM(FII_XTD_BOOKED)) OVER () FII_GT_XTD_BOOKED,
434 ((SUM(SUM(FII_XTD_REV)) OVER () - SUM(SUM(PRIOR_REV)) OVER ()) / DECODE(SUM(SUM(PRIOR_REV)) OVER (),
435 0,NULL,SUM(SUM(PRIOR_REV)) OVER ()) * 100) FII_GT_CHANGE,
436 ((SUM(SUM(FII_XTD_BOOKED)) OVER () - SUM(SUM(PRIOR_BOOKED)) OVER ()) / DECODE(SUM(SUM(PRIOR_BOOKED)) OVER (),
437 0,NULL,SUM(SUM(PRIOR_BOOKED)) OVER ()) * 100) FII_GT_CHANGE_BOOKED,
438 SUM(SUM(FII_XTD_REV) - SUM(FII_XTD_BOOKED)) OVER () FII_GT_XTD_PRIOR_BOOKED,
439 (((SUM(SUM(FII_XTD_REV) - SUM(FII_XTD_BOOKED)) OVER() - SUM(SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)) OVER()) / DECODE(SUM(SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)) OVER(),
440 0,NULL,SUM(SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)) OVER())) * 100) FII_GT_PRIOR_BOOKED_CHANGE,
441 ( rank() over (ORDER BY SUM(FII_XTD_REV) '||l_sort||' nulls last, '||l_viewby_col||')) - 1 rnk
442 FROM (
443
444 SELECT '||l_viewby_col||',
445 0 FII_HIST_COL1,
446 0 FII_HIST_COL2,
447 0 FII_HIST_COL3,
448 0 FII_HIST_COL4,
449 SUM(CASE WHEN cal.report_date in (&BIS_CURRENT_ASOF_DATE)
450 then f.recognized_amt_'||l_curr_suffix||' ELSE 0 END) FII_XTD_REV,
451 SUM(CASE WHEN cal.report_date in (&BIS_PREVIOUS_ASOF_DATE)
452 then f.recognized_amt_'||l_curr_suffix||' ELSE 0 END) PRIOR_REV,
453 SUM(CASE WHEN cal.report_date in (&BIS_CURRENT_ASOF_DATE)
454 then f.booked_rev_pe_'||l_curr_suffix||' ELSE 0 END) FII_XTD_BOOKED,
455 SUM(CASE WHEN cal.report_date in (&BIS_PREVIOUS_ASOF_DATE)
456 then f.booked_rev_pe_'||l_curr_suffix||' ELSE 0 END) PRIOR_BOOKED
457
458 FROM '||l_mv_to_be_used ||' f,
459 fii_time_structures cal'
460 ||l_prod_cat_from||'
461
462 WHERE f.time_id = cal.time_id
463 AND f.period_type_id = cal.period_type_id
464 AND bitand(cal.record_type_id,:RECORD_TYPE_ID) = :RECORD_TYPE_ID
465 AND cal.report_date in ((&BIS_PREVIOUS_ASOF_DATE),(&BIS_CURRENT_ASOF_DATE))
466 AND f.recognized_amt_'||l_curr_suffix||' <> 0
467 '||l_flags||l_sg_where||l_prod_cat_where||l_cust_where||'
468
469 GROUP BY '||l_viewby_col||') c group by '||l_viewby_col||') f,' ;
470
471 ELSE
472
473 -- for period type quarter and year, We use UNION ALL. First sql gives historical data while
474 -- second sql gets values for PTD revenue, prior period PTD revenue(used to calculate change),
475 -- Booked revenue for current period and booked revenue for prior period(for change calculation)
476 l_inner_sql := '
477 SUM(FII_HIST_COL1) FII_HIST_COL1,
478 SUM(FII_HIST_COL2) FII_HIST_COL2,
479 SUM(FII_HIST_COL3) FII_HIST_COL3,
480 SUM(FII_HIST_COL4) FII_HIST_COL4,
481 SUM(FII_XTD_REV) FII_XTD_REV,
482 SUM(FII_CHANGE) FII_CHANGE,
483 SUM(FII_PRIOR_XTD_REV) FII_PRIOR_XTD_REV,
484 SUM(FII_XTD_BOOKED) FII_XTD_BOOKED,
485 SUM(FII_CHANGE_BOOKED) FII_CHANGE_BOOKED,
486 SUM(FII_PRIOR_XTD_BOOKED) FII_PRIOR_XTD_BOOKED,
487 SUM(FII_BOOKED_PRIOR_XTD) FII_BOOKED_PRIOR_XTD,
488 SUM(FII_BOOKED_PRIOR_CHANGE) FII_BOOKED_PRIOR_CHANGE,
489 SUM(FII_GT_HIST_COL1) FII_GT_HIST_COL1,
490 SUM(FII_GT_HIST_COL2) FII_GT_HIST_COL2,
491 SUM(FII_GT_HIST_COL3) FII_GT_HIST_COL3,
492 SUM(FII_GT_HIST_COL4) FII_GT_HIST_COL4,
493 SUM(FII_GT_XTD_REV) FII_GT_XTD_REV,
494 SUM(FII_GT_XTD_BOOKED) FII_GT_XTD_BOOKED,
495 SUM(FII_GT_CHANGE) FII_GT_CHANGE,
496 SUM(FII_GT_CHANGE_BOOKED) FII_GT_CHANGE_BOOKED,
497 SUM(FII_GT_XTD_PRIOR_BOOKED) FII_GT_XTD_PRIOR_BOOKED,
498 SUM(FII_GT_PRIOR_BOOKED_CHANGE) FII_GT_PRIOR_BOOKED_CHANGE
499 FROM (
500 SELECT '||l_viewby_col||',
501 SUM(FII_HIST_COL1) FII_HIST_COL1,
502 SUM(FII_HIST_COL2) FII_HIST_COL2,
503 SUM(FII_HIST_COL3) FII_HIST_COL3,
504 SUM(FII_HIST_COL4) FII_HIST_COL4,
505 SUM(FII_XTD_REV) FII_XTD_REV,
506 (((SUM(FII_XTD_REV) - SUM(PRIOR_REV)) / DECODE(SUM(PRIOR_REV),
507 0,NULL,SUM(PRIOR_REV))) * 100) FII_CHANGE,
508 SUM(PRIOR_REV) FII_PRIOR_XTD_REV,
509 SUM(FII_XTD_BOOKED) FII_XTD_BOOKED,
510 (((SUM(FII_XTD_BOOKED) - SUM(PRIOR_BOOKED)) / DECODE(SUM(PRIOR_BOOKED),0,
511 NULL,SUM(PRIOR_BOOKED))) * 100) FII_CHANGE_BOOKED,
512 SUM(PRIOR_BOOKED) FII_PRIOR_XTD_BOOKED,
513 (SUM(FII_XTD_REV) - SUM(FII_XTD_BOOKED)) FII_BOOKED_PRIOR_XTD,
514 ((((SUM(FII_XTD_REV) - SUM(FII_XTD_BOOKED)) - (SUM(PRIOR_REV)- SUM(PRIOR_BOOKED))) / DECODE((SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)),
515 0,NULL,(SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)))) * 100) FII_BOOKED_PRIOR_CHANGE,
516 SUM(SUM(FII_HIST_COL1)) OVER () FII_GT_HIST_COL1,
517 SUM(SUM(FII_HIST_COL2)) OVER () FII_GT_HIST_COL2,
518 SUM(SUM(FII_HIST_COL3)) OVER () FII_GT_HIST_COL3,
519 SUM(SUM(FII_HIST_COL4)) OVER () FII_GT_HIST_COL4,
520 SUM(SUM(FII_XTD_REV)) OVER () FII_GT_XTD_REV,
521 SUM(SUM(FII_XTD_BOOKED)) OVER () FII_GT_XTD_BOOKED,
522 ((SUM(SUM(FII_XTD_REV)) OVER () - SUM(SUM(PRIOR_REV)) OVER ()) / DECODE(SUM(SUM(PRIOR_REV)) OVER (),
523 0,NULL,SUM(SUM(PRIOR_REV)) OVER ()) * 100) FII_GT_CHANGE,
524 ((SUM(SUM(FII_XTD_BOOKED)) OVER () - SUM(SUM(PRIOR_BOOKED)) OVER ()) / DECODE(SUM(SUM(PRIOR_BOOKED)) OVER (),
525 0,NULL,SUM(SUM(PRIOR_BOOKED)) OVER ()) * 100) FII_GT_CHANGE_BOOKED,
526 SUM(SUM(FII_XTD_REV) - SUM(FII_XTD_BOOKED)) OVER () FII_GT_XTD_PRIOR_BOOKED,
527 (((SUM(SUM(FII_XTD_REV) - SUM(FII_XTD_BOOKED)) OVER() - SUM(SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)) OVER()) / DECODE(SUM(SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)) OVER(),
528 0,NULL,SUM(SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)) OVER())) * 100) FII_GT_PRIOR_BOOKED_CHANGE,
529 ( rank() over (ORDER BY SUM(FII_XTD_REV) '||l_sort||' nulls last, '||l_viewby_col||')) - 1 rnk
530
531 FROM (
532 SELECT '||l_viewby_col||',
533 SUM(CASE WHEN cal.report_date in to_date(:FII_THREE_PERIOD_BACK,''DD-MM-YYYY'')
534 THEN f.recognized_amt_'||l_curr_suffix||' ELSE 0 END) FII_HIST_COL1,
535 SUM(CASE WHEN cal.report_date in to_date(:FII_TWO_PERIOD_BACK,''DD-MM-YYYY'')
536 THEN f.recognized_amt_'||l_curr_suffix||' ELSE 0 END) FII_HIST_COL2,
537 SUM(CASE WHEN cal.report_date in to_date(:FII_ONE_PERIOD_BACK,''DD-MM-YYYY'')
538 THEN f.recognized_amt_'||l_curr_suffix||' ELSE 0 END) FII_HIST_COL3,
539 SUM(CASE WHEN cal.report_date in (&BIS_CURRENT_ASOF_DATE)
540 THEN f.recognized_amt_'||l_curr_suffix||' ELSE 0 END) FII_HIST_COL4,
541 0 FII_XTD_REV,
542 0 PRIOR_REV,
543 0 FII_XTD_BOOKED,
544 0 PRIOR_BOOKED
545
546 FROM '||l_mv_to_be_used ||' f,
547 fii_time_structures cal'
548 ||l_prod_cat_from||'
549
550 WHERE f.time_id = cal.time_id
551 AND f.period_type_id = cal.period_type_id
552 AND bitand(cal.record_type_id, :FII_RECORD_ID) = :FII_RECORD_ID
553 AND cal.report_date in (to_date(:FII_THREE_PERIOD_BACK,''DD-MM-YYYY''),to_date(:FII_TWO_PERIOD_BACK,''DD-MM-YYYY''),to_date(:FII_ONE_PERIOD_BACK,''DD-MM-YYYY''),
554 (&BIS_CURRENT_ASOF_DATE))
555 AND f.recognized_amt_'||l_curr_suffix||' <> 0
556 '||l_flags||l_sg_where||l_prod_cat_where||l_cust_where||'
557
558 GROUP BY '||l_viewby_col||'
559
560 UNION ALL
561
562 SELECT '||l_viewby_col||',
563 0 FII_HIST_COL1,
564 0 FII_HIST_COL2,
565 0 FII_HIST_COL3,
566 0 FII_HIST_COL4,
567 SUM(CASE WHEN cal.report_date in (&BIS_CURRENT_ASOF_DATE)
568 then f.recognized_amt_'||l_curr_suffix||' ELSE 0 END) FII_XTD_REV,
569 SUM(CASE WHEN cal.report_date in (&BIS_PREVIOUS_ASOF_DATE)
570 then f.recognized_amt_'||l_curr_suffix||' ELSE 0 END) PRIOR_REV,
571 SUM(CASE WHEN cal.report_date in (&BIS_CURRENT_ASOF_DATE)
572 then f.booked_rev_'||l_pertype_for_booked||'_'||l_curr_suffix||' ELSE 0 END) FII_XTD_BOOKED,
573 SUM(CASE WHEN cal.report_date in (&BIS_PREVIOUS_ASOF_DATE)
574 then f.booked_rev_'||l_pertype_for_booked||'_'||l_curr_suffix||' ELSE 0 END) PRIOR_BOOKED
575 FROM '||l_mv_to_be_used ||' f,
576 fii_time_structures cal'
577 ||l_prod_cat_from||'
578
579 WHERE f.time_id = cal.time_id
580 AND f.period_type_id = cal.period_type_id
581 AND bitand(cal.record_type_id,:RECORD_TYPE_ID) = :RECORD_TYPE_ID
582 AND cal.report_date in ((&BIS_PREVIOUS_ASOF_DATE),(&BIS_CURRENT_ASOF_DATE))
583 AND f.recognized_amt_'||l_curr_suffix||' <> 0
584 '||l_flags||l_sg_where||l_prod_cat_where||l_cust_where||'
585
586 GROUP BY '||l_viewby_col||') c group by '||l_viewby_col||') f,' ;
587 END CASE;
588
589
590 IF l_view_by = 'ORGANIZATION+JTF_ORG_SALES_GROUP' THEN
591
592 sqlstmt := '
593 SELECT DECODE(f.resource_id,NULL,g.group_name,
594 r.resource_name) VIEWBY,
595 DECODE(f.resource_id,NULL,to_char(f.sales_grp_id),
596 f.resource_id||''.''||f.sales_grp_id)
597 VIEWBYID,
598 DECODE(sum(fii_xtd_rev),0,null,decode(f.resource_id, NULL,
599 ''pFunctionName=FII_AR_SG_PROD_REV&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ORGANIZATION+JTF_ORG_SALES_GROUP'',
600 NULL)) FII_SALES_GROUP_DRILL,
601 NULL FII_PROD_CAT_DRILL,
602 '||l_inner_sql||'
603 JTF_RS_GROUPS_VL g,
604 JTF_RS_RESOURCE_EXTNS_VL r
605 WHERE f.sales_grp_id = g.group_id
606 AND f.resource_id = r.resource_id(+)
607 AND ((rnk between &START_INDEX and &END_INDEX) or (&END_INDEX = -1))
608 GROUP BY f.resource_id,g.group_name,r.resource_name,f.sales_grp_id
609 &ORDER_BY_CLAUSE ' ;
610
611 ELSIF l_view_by = 'ITEM+ENI_ITEM_VBH_CAT' THEN
612
613 IF (l_prod_cat IS NULL) THEN
614 l_cat_join := 'AND f.parent_id = ecat.id';
615 ElSE
616 l_cat_join := 'AND f.imm_child_id = ecat.id';
617 END IF;
618
619 sqlstmt := '
620
621 SELECT ecat.value VIEWBY,
622 ecat.id VIEWBYID,
623 NULL FII_SALES_GROUP_DRILL, -- Drill - Sales Group
624 DECODE(SUM(fii_xtd_rev),0,NULL,DECODE(ecat.leaf_node_flag, ''Y'',NULL,
625 ''pFunctionName=FII_AR_SG_PROD_REV&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y''))
626 FII_PROD_CAT_DRILL, -- Drill - Prod Category
627 '||l_inner_sql||'
628 ENI_ITEM_VBH_NODES_V ecat
629 WHERE ecat.parent_id = ecat.child_id
630 '||l_cat_join||'
631 AND ((rnk between &START_INDEX and &END_INDEX) or (&END_INDEX = -1))
632 GROUP BY ecat.value,ecat.id,ecat.leaf_node_flag
633 &ORDER_BY_CLAUSE ';
634
635
636 ELSE -- l_view_by = 'CUSTOMER+FII_CUSTOMERS'
637
638 sqlstmt := '
639 SELECT cust.value VIEWBY,
640 cust.id VIEWBYID,
641 NULL FII_SALES_GROUP_DRILL,
642 NULL FII_PROD_CAT_DRILL, '
643 ||l_inner_sql||'
644 FII_CUSTOMERS_V cust
645 WHERE f.customer_id = cust.id
646 AND ((rnk between &START_INDEX and &END_INDEX) or (&END_INDEX = -1))
647 GROUP BY cust.value,cust.id
648 &ORDER_BY_CLAUSE ';
649
650
651 END IF;
652
653 -- Binding Section
654
655 get_sg_prod_rev_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
656 get_sg_prod_rev_output := BIS_QUERY_ATTRIBUTES_TBL();
657 get_sg_prod_rev_sql := sqlstmt;
658
659 get_sg_prod_rev_output.EXTEND;
660 get_sg_prod_rev_rec.attribute_name := ':RECORD_TYPE_ID';
661 get_sg_prod_rev_rec.attribute_value := TO_CHAR(l_record_type_id);
662 get_sg_prod_rev_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
663 get_sg_prod_rev_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
664 get_sg_prod_rev_output(get_sg_prod_rev_output.COUNT) := get_sg_prod_rev_rec;
665 get_sg_prod_rev_output.EXTEND;
666
667 get_sg_prod_rev_rec.attribute_name := ':VIEW_BY';
668 get_sg_prod_rev_rec.attribute_value := TO_CHAR(l_view_by);
669 get_sg_prod_rev_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
670 get_sg_prod_rev_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
671 get_sg_prod_rev_output(get_sg_prod_rev_output.COUNT) := get_sg_prod_rev_rec;
672 get_sg_prod_rev_output.EXTEND;
673
674 get_sg_prod_rev_rec.attribute_name := ':FII_SG_SG';
675 get_sg_prod_rev_rec.attribute_value := TO_CHAR(l_sg_sg);
676 get_sg_prod_rev_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
677 get_sg_prod_rev_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
678 get_sg_prod_rev_output(get_sg_prod_rev_output.COUNT) := get_sg_prod_rev_rec;
679 get_sg_prod_rev_output.EXTEND;
680
681 get_sg_prod_rev_rec.attribute_name := ':FII_SG_RES';
682 get_sg_prod_rev_rec.attribute_value := TO_CHAR(l_sg_res);
683 get_sg_prod_rev_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
684 get_sg_prod_rev_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
685 get_sg_prod_rev_output(get_sg_prod_rev_output.COUNT) := get_sg_prod_rev_rec;
686 get_sg_prod_rev_output.EXTEND;
687
688 get_sg_prod_rev_rec.attribute_name := ':FII_ITEM_CAT_FLAG';
689 get_sg_prod_rev_rec.attribute_value := TO_CHAR(l_item_cat_flag);
690 get_sg_prod_rev_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
691 get_sg_prod_rev_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
692 get_sg_prod_rev_output(get_sg_prod_rev_output.COUNT) := get_sg_prod_rev_rec;
693 get_sg_prod_rev_output.EXTEND;
694
695 get_sg_prod_rev_rec.attribute_name := ':FII_CUST';
696 get_sg_prod_rev_rec.attribute_value := TO_CHAR(l_cust_flag);
697 get_sg_prod_rev_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
698 get_sg_prod_rev_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
699 get_sg_prod_rev_output(get_sg_prod_rev_output.COUNT) := get_sg_prod_rev_rec;
700 get_sg_prod_rev_output.EXTEND;
701
702 get_sg_prod_rev_rec.attribute_name := ':FII_RECORD_ID';
703 get_sg_prod_rev_rec.attribute_value := TO_CHAR(l_record_id);
704 get_sg_prod_rev_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
705 get_sg_prod_rev_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
706 get_sg_prod_rev_output(get_sg_prod_rev_output.COUNT) := get_sg_prod_rev_rec;
707 get_sg_prod_rev_output.EXTEND;
708
709 get_sg_prod_rev_rec.attribute_name := ':FII_ONE_PERIOD_BACK';
710 get_sg_prod_rev_rec.attribute_value := TO_CHAR(l_one_period_back,'DD-MM-YYYY');
711 get_sg_prod_rev_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
712 get_sg_prod_rev_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
713 get_sg_prod_rev_output(get_sg_prod_rev_output.COUNT) := get_sg_prod_rev_rec;
714 get_sg_prod_rev_output.EXTEND;
715
716 get_sg_prod_rev_rec.attribute_name := ':FII_TWO_PERIOD_BACK';
717 get_sg_prod_rev_rec.attribute_value := TO_CHAR(l_two_period_back,'DD-MM-YYYY');
718 get_sg_prod_rev_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
719 get_sg_prod_rev_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
720 get_sg_prod_rev_output(get_sg_prod_rev_output.COUNT) := get_sg_prod_rev_rec;
721 get_sg_prod_rev_output.EXTEND;
722
723 get_sg_prod_rev_rec.attribute_name := ':FII_THREE_PERIOD_BACK';
724 get_sg_prod_rev_rec.attribute_value := TO_CHAR(l_three_period_back,'DD-MM-YYYY');
725 get_sg_prod_rev_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
726 get_sg_prod_rev_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
727 get_sg_prod_rev_output(get_sg_prod_rev_output.COUNT) := get_sg_prod_rev_rec;
728 get_sg_prod_rev_output.EXTEND;
729
730 get_sg_prod_rev_rec.attribute_name := ':FII_SORT';
731 get_sg_prod_rev_rec.attribute_value := TO_CHAR(l_sort);
732 get_sg_prod_rev_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
733 get_sg_prod_rev_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
734 get_sg_prod_rev_output(get_sg_prod_rev_output.COUNT) := get_sg_prod_rev_rec;
735 get_sg_prod_rev_output.EXTEND;
736
737 END get_sg_prod_rev;
738
739 END fii_ar_sg_prod_rev_pkg;