[Home] [Help]
PACKAGE BODY: APPS.FII_GL_REV_PROD
Source
1 PACKAGE BODY fii_gl_rev_prod AS
2 /* $Header: FIIGLRPB.pls 120.15 2005/07/18 06:27:56 hpoddar noship $ */
3
4 PROCEDURE get_rev_by_prod (p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
5 rev_by_prod_sql out NOCOPY VARCHAR2, rev_by_prod_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
6 IS
7
8 sqlstmt VARCHAR2(10000);
9 l_prod_join VARCHAR2(200) := NULL;
10
11 BEGIN
12 fii_gl_util_pkg.reset_globals;
13 fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
14 fii_gl_util_pkg.get_bitmasks;
15 fii_gl_util_pkg.g_fin_type := 'R';
16 fii_gl_util_pkg.get_mgr_pmv_sql;
17
18 IF (fii_gl_util_pkg.g_prod_id is not NULL) and (upper(fii_gl_util_pkg.g_prod_id) <> upper('All')) THEN
19 l_prod_join :='
20 and prod.parent_id = &ITEM+ENI_ITEM_VBH_CAT';
21 ELSE
22 l_prod_join :='
23 and prod.top_node_flag = ''Y''';
24 END IF;
25
26 /*****************************************************************************
27 * FII_MEASURE1 = Lower Level Product
28 * FII_CAL2 = Product id
29 * FII_MEASURE2 = Current amounts
30 * FII_MEASURE3 = Prior amounts
31 * FII_MEASURE5 = Forecast amounts
32 * FII_MEASURE6 = Current amounts for Pie Chart (a hidden column in the table)
33 * FII_MEASURE9 = Grand Total of Current amounts
34 * FII_MEASURE10 = Grand Total of Prior amounts
35 * FII_MEASURE11 = Grand Total of Forecast amounts
36 * FII_MEASURE12 = Budget amounts
37 * FII_ATTRIBUTE2 = Prior Total amounts
38 *****************************************************************************/
39 IF fii_gl_util_pkg.g_mgr_id = -99999 THEN /* Done for bug 3875336 */
40
41 sqlstmt := 'SELECT NULL FII_MEASURE1,
42 NULL FII_CAL2,
43 NULL FII_MEASURE2,
44 NULL FII_MEASURE3,
45 NULL FII_MEASURE5,
46 NULL FII_MEASURE6,
47 NULL FII_MEASURE12,
48 NULL FII_ATTRIBUTE2,
49 NULL FII_MEASURE9,
50 NULL FII_MEASURE10,
51 NULL FII_MEASURE11
52 FROM DUAL
53 WHERE 1=2';
54
55 ELSE
56
57 sqlstmt := '
58 select
59 f.value FII_MEASURE1,
60 f.id FII_CAL2,
61 sum(CY_ACTUAL) FII_MEASURE2,
62 sum(PY_ACTUAL) FII_MEASURE3,
63 sum(CY_FORECAST) FII_MEASURE5,
64 sum(CY_ACTUAL) FII_MEASURE6,
65 sum(CY_BUDGET) FII_MEASURE12,
66 sum(PY_SPER_END) FII_ATTRIBUTE2,
67 sum(sum(CY_ACTUAL)) over() FII_MEASURE9,
68 sum(sum(PY_ACTUAL)) over() FII_MEASURE10,
69 sum(sum(CY_FORECAST)) over() FII_MEASURE11
70 from (select prod.value VALUE,
71 prod.id ID,
72 sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
73 then f.actual_g
74 else to_number(NULL) end) CY_ACTUAL,
75 sum(case when bitand(cal.record_type_id, :FORECAST_PERIOD_TYPE) = cal.record_type_id
76 then f.forecast_g
77 else to_number(NULL) end) CY_FORECAST,
78 sum(case when bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id
79 then f.budget_g
80 else to_number(NULL) end) CY_BUDGET,
81 to_number(NULL) PY_ACTUAL,
82 to_number(NULL) PY_SPER_END
83 from FII_TIME_RPT_STRUCT cal,
84 eni_item_vbh_nodes_v prod,
85 fii_gl_prd_v'||fii_gl_util_pkg.g_global_curr_view||' f,
86 fii_fin_item_hierarchies cat,
87 fii_cc_mgr_hierarchies h
88 where h.mgr_id = &HRI_PERSON+HRI_PER_USRDR_H
89 and h.emp_id = f.manager_id
90 and f.time_id = cal.time_id
91 and cat.parent_fin_cat_id in (select fin_category_id from fii_fin_cat_type_assgns where FIN_CAT_TYPE_CODE = ''R'' and TOP_NODE_FLAG = ''Y'')
92 and f.period_type_id = cal.period_type_id
93 and cat.child_fin_cat_id = f.fin_category_id
94 and f.product_category_id = prod.child_id'||l_prod_join||'
95 and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
96 and cal.report_date = &BIS_CURRENT_ASOF_DATE
97 group by prod.value, prod.id
98
99 union all
100
101 select prod.value VALUE,
102 prod.id ID,
103 to_number(NULL) CY_ACTUAL,
104 to_number(NULL) CY_FORECAST,
105 to_number(NULL) CY_BUDGET,
106 sum(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
107 then f.actual_g
108 else to_number(NULL) end) PY_ACTUAL,
109 sum(case when bitand(cal.record_type_id, :ENT_PERIOD_TYPE) = cal.record_type_id
110 then f.actual_g
111 else to_number(NULL) end) PY_SPER_END
112 from FII_TIME_RPT_STRUCT cal,
113 eni_item_vbh_nodes_v prod,
114 fii_gl_prd_v'||fii_gl_util_pkg.g_global_curr_view||' f,
115 fii_fin_item_hierarchies cat,
116 fii_cc_mgr_hierarchies h
117 where h.mgr_id = &HRI_PERSON+HRI_PER_USRDR_H
118 and h.emp_id = f.manager_id
119 and f.time_id = cal.time_id
120 and f.period_type_id = cal.period_type_id
121 and cat.parent_fin_cat_id in (select fin_category_id from fii_fin_cat_type_assgns where FIN_CAT_TYPE_CODE = ''R'' and TOP_NODE_FLAG = ''Y'')
122 and cat.child_fin_cat_id = f.fin_category_id
123 and f.product_category_id = prod.child_id'||l_prod_join||'
124 and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
125 and cal.report_date = &BIS_PREVIOUS_ASOF_DATE
126 group by prod.value, prod.id) f
127 group by f.value, f.id
128 ';
129
130 END IF;
131
132 fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, rev_by_prod_sql, rev_by_prod_output);
133
134 END get_rev_by_prod;
135
136 END fii_gl_rev_prod;