[Home] [Help]
PACKAGE BODY: APPS.FII_EA_PAGE_PKG
Source
1 PACKAGE BODY fii_ea_page_pkg AS
2 /* $Header: FIIEAPAGEB.pls 120.10.12000000.2 2007/04/16 06:52:03 dhmehra ship $ */
3
4 PROCEDURE get_exp (
5 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, exp_ana_page_sql out NOCOPY VARCHAR2,
6 exp_ana_page_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
7
8 sqlstmt VARCHAR2(30000);
9
10 BEGIN
11 fii_ea_util_pkg.reset_globals;
12 fii_ea_util_pkg.g_fin_cat_type := 'OE';
13
14 sqlstmt := fii_ea_page_pkg.get_revexp(p_page_parameter_tbl);
15
16 fii_ea_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, exp_ana_page_sql, exp_ana_page_output);
17
18 END get_exp;
19
20
21
22 FUNCTION get_revexp (p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL) return VARCHAR2 IS
23
24 sqlstmt VARCHAR2(30000);
25 p_aggrt_viewby_id VARCHAR2(30);
26 p_nonaggrt_viewby_id VARCHAR2(30);
27 p_snap_aggrt_viewby_id VARCHAR2(30);
28 p_aggrt_gt_is_empty VARCHAR2(1);
29 p_non_aggrt_gt_is_empty VARCHAR2(1);
30 l_xtd_drill_url VARCHAR2(300);
31 l_prior VARCHAR2(300);
32 l_budget_decode VARCHAR2(300); -- Since we can load budget only against category AND fud1 summary nodes,
33 -- this local variable appends a check to agrt MV AND base map MV queries, so that budget is checked only for xTD period.
34 -- Budget loaded for prior xTD should not result in any unwanted record, having 0/NA in all columns..
35 l_if_leaf_flag VARCHAR2(1); -- local var to denote, if category or fud1 param chosen to run the report is a leaf or not..
36 l_change VARCHAR2(300);
37 l_gt_change VARCHAR2(500);
38 -- Added for enhancement 4269343
39 l_drill_source VARCHAR2(40);
40 l_prior_g VARCHAR2(10000);
41
42 BEGIN
43
44 fii_ea_util_pkg.get_parameters(p_page_parameter_tbl);
45
46 fii_ea_util_pkg.get_viewby_id(p_aggrt_viewby_id, p_snap_aggrt_viewby_id, p_nonaggrt_viewby_id);
47
48 fii_ea_util_pkg.populate_security_gt_tables(p_aggrt_gt_is_empty, p_non_aggrt_gt_is_empty);
49
50 CASE fii_ea_util_pkg.g_time_comp
51
52 WHEN 'BUDGET' THEN
53 l_prior_g := 'SUM(DECODE(inner_inline_view.report_date, :ASOF_DATE,
54 (CASE WHEN bitand(inner_inline_view.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND
55 THEN f.budget_g ELSE NULL END))) FII_EA_PRIOR_XTD_EXP_G';
56
57 l_prior := 'NULL FII_EA_PRIOR_XTD_EXP';
58
59 l_change := 'NULL FII_EA_CHANGE,';
60
61 l_gt_change := 'NULL FII_EA_GT_CHANGE,';
62
63 WHEN 'FORECAST' THEN
64 l_prior_g := 'SUM(DECODE(inner_inline_view.report_date, :ASOF_DATE,
65 (CASE WHEN bitand(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
66 THEN f.forecast_g ELSE NULL END))) FII_EA_PRIOR_XTD_EXP_G';
67
68 l_prior := 'NULL FII_EA_PRIOR_XTD_EXP';
69
70 l_change := 'NULL FII_EA_CHANGE,';
71
72 l_gt_change := 'NULL FII_EA_GT_CHANGE,';
73
74 ELSE
75 l_prior_g := 'SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_ASOF_DATE,
76 (CASE WHEN bitand(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
77 THEN f.actual_g ELSE NULL END))) FII_EA_PRIOR_XTD_EXP_G';
78
79 l_prior := 'SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_ASOF_DATE,
80 (CASE WHEN bitand(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
81 THEN f.actual_g ELSE NULL END))) FII_EA_PRIOR_XTD_EXP';
82
83 l_change := 'SUM(((FII_EA_XTD_EXP-FII_EA_PRIOR_XTD_EXP)/ABS(NULLIF(FII_EA_PRIOR_XTD_EXP,0)))*100) FII_EA_CHANGE,';
84
85 l_gt_change := '(SUM(SUM(FII_EA_XTD_EXP)) over() -
86 SUM(SUM(FII_EA_PRIOR_XTD_EXP)) over()) /
87 ABS(NULLIF(SUM(SUM(FII_EA_PRIOR_XTD_EXP)) over(),0)) * 100 FII_EA_GT_CHANGE,';
88 END CASE;
89
90 IF fii_ea_util_pkg.g_view_by = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
91
92 fii_ea_util_pkg.check_if_leaf(fii_ea_util_pkg.g_category_id);
93 l_if_leaf_flag := fii_ea_util_pkg.g_fin_cat_is_leaf;
94
95 -- This issue was found during testing of fix for bug 4127077. Since these variables are used to
96 -- check for loading of budgets against summary nodes, we don't need to append
97 -- l_budget_decode to the main sql, when we choose a leaf category node.
98
99 IF l_if_leaf_flag = 'N' THEN
100 l_budget_decode := 'AND f.fin_category_id = DECODE(:G_ID, f.fin_category_id,
101 DECODE(f.time_id,:TIME_ID, f.fin_category_id,-99999),f.fin_category_id)';
102 END IF;
103 ELSE
104 l_if_leaf_flag := 'Y';
105 END IF;
106
107 l_xtd_drill_url := 'pFunctionName=FII_EA_EXP_TREND_DTL&VIEW_BY=TIME+FII_TIME_ENT_PERIOD&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
108
109 -- Done for enhancement 4269343
110 -- Depending upon the drill source, i.e. Funds Management Page OR Expense Analysis page, the drill source
111 -- changes accordingly
112
113 -- Added check for FII_EA_EXP_BY_COMP_PORT AK region
114
115
116 SELECT DECODE(fii_ea_util_pkg.g_region_code,'FII_EA_PAGE','FII_EA_EXP_SUM','FII_EA_EXP_BY_COMP_PORT','FII_EA_EXP_SUM','FII_PSI_EXP_SUM')
117 INTO l_drill_source
118 FROM DUAL;
119
120 sqlstmt :='
121 SELECT DECODE(:G_ID, inline_view.viewby_id,DECODE('''||l_if_leaf_flag||''',''Y'',
122 inline_view.viewby, inline_view.viewby||'' ''||:DIR_MSG),
123 inline_view.viewby) VIEWBY,
124 inline_view.viewby_id VIEWBYID,
125 SUM(FII_EA_PRIOR_XTD_EXP_G) FII_EA_PRIOR_XTD_EXP_G,
126 SUM(FII_EA_PRIOR_TOTAL_G) FII_EA_PRIOR_TOTAL_G,
127 SUM(FII_EA_XTD_EXP) FII_EA_XTD_EXP,
128 NULL FII_EA_CURR_TOTAL_G,
129 SUM(FII_EA_PRIOR_XTD_EXP) FII_EA_PRIOR_XTD_EXP,
130 '||l_change||'
131 SUM(FII_EA_BUDGET) FII_EA_BUDGET,
132 SUM(FII_EA_FORECAST) FII_EA_FORECAST,
133 SUM(SUM(FII_EA_XTD_EXP)) OVER () FII_EA_GT_XTD_EXP,
134 SUM(SUM(FII_EA_PRIOR_XTD_EXP)) OVER () FII_EA_GT_PRIOR_XTD_EXP,
135 '||l_gt_change||'
136 SUM(SUM(FII_EA_BUDGET)) OVER () FII_EA_GT_BUDGET,
137 SUM(SUM(FII_EA_XTD_EXP)) OVER () /
138 NULLIF(SUM(SUM(FII_EA_BUDGET)) OVER (),0) * 100 FII_EA_GT_PCNT_BUDGET,
139 SUM(SUM(FII_EA_FORECAST)) OVER () FII_EA_GT_FORECAST,
140 SUM(SUM(FII_EA_XTD_EXP)) OVER () /
141 NULLIF(SUM(SUM(FII_EA_FORECAST)) OVER (),0) * 100 FII_EA_GT_PCNT_FORECAST,
142
143 DECODE((SELECT is_leaf_flag
144 FROM fii_company_hierarchies
145 WHERE parent_company_id = inline_view.viewby_id
146 AND child_company_id = inline_view.viewby_id),
147 ''Y'',
148 '''',
149 ''pFunctionName='||l_drill_source||'&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y'') FII_EA_COMP_DRILL,
150
151 DECODE((SELECT is_leaf_flag
152 FROM fii_cost_ctr_hierarchies
153 WHERE parent_cc_id = inline_view.viewby_id
154 AND child_cc_id = inline_view.viewby_id),
155 ''Y'',
156 '''',
157 ''pFunctionName='||l_drill_source||'&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y'') FII_EA_CC_DRILL,
158 DECODE((SELECT is_leaf_flag
159 FROM fii_fin_item_leaf_hiers
160 WHERE parent_fin_cat_id = inline_view.viewby_id
161 AND child_fin_cat_id = inline_view.viewby_id),
162 ''Y'',
163 '''',
164 DECODE(:G_ID, inline_view.viewby_id,'''',
165 ''pFunctionName='||l_drill_source||'&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y'')) FII_EA_CAT_DRILL,
166
167 DECODE(SUM(FII_EA_XTD_EXP),0,'''',DECODE(NVL(SUM(FII_EA_XTD_EXP),-999999),-999999,'''','''||l_xtd_drill_url||''')) FII_EA_XTD_DRILL,
168 DECODE(SUM(FII_EA_XTD_EXP),0,'''',DECODE(NVL(SUM(FII_EA_XTD_EXP),-999999),-999999,'''','''||l_xtd_drill_url||''')) FII_EA_XTD_PIE_DRILL
169
170 FROM
171 (
172 SELECT '||p_aggrt_viewby_id||' viewby_id,
173 inner_inline_view.viewby viewby,
174 inner_inline_view.sort_order sort_order,
175 '||l_prior_g||',
176 SUM(DECODE(inner_inline_view.report_date, :PRIOR_PERIOD_END,
177 (CASE WHEN bitand(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
178 THEN f.actual_g ELSE NULL END))) FII_EA_PRIOR_TOTAL_G,
179 SUM(DECODE(inner_inline_view.report_date, :ASOF_DATE,
180 (CASE WHEN bitand(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
181 THEN f.actual_g ELSE NULL END))) FII_EA_XTD_EXP,
182 '||l_prior||',
183 SUM(DECODE(inner_inline_view.report_date, :ASOF_DATE,
184 (CASE WHEN bitand(inner_inline_view.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND
185 THEN f.budget_g ELSE NULL END) ) ) FII_EA_BUDGET,
186 SUM(DECODE(inner_inline_view.report_date, :ASOF_DATE,
187 (CASE WHEN bitand(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
188 THEN f.forecast_g ELSE NULL END) ) ) FII_EA_FORECAST
189
190 FROM fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,
191 ( SELECT /*+ NO_MERGE cardinality(gt 1) */ *
192 FROM fii_time_structures cal,
193 fii_pmv_aggrt_gt gt
194 WHERE report_date in (:ASOF_DATE, :PREVIOUS_ASOF_DATE, :PRIOR_PERIOD_END)
195 AND ( BITAND(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND OR
196 BITAND(cal.record_type_id, :BUDGET_BITAND) = :BUDGET_BITAND OR
197 BITAND(cal.record_type_id, :FORECAST_BITAND) = :FORECAST_BITAND
198 )
199 ) inner_inline_view
200
201 WHERE f.time_id = inner_inline_view.time_id
202 AND f.period_type_id = inner_inline_view.period_type_id
203 AND f.parent_company_id = inner_inline_view.parent_company_id
204 AND f.company_id = inner_inline_view.company_id
205 AND f.parent_cost_center_id = inner_inline_view.parent_cc_id
206 AND f.cost_center_id = inner_inline_view.cc_id
207 AND f.parent_fin_category_id = inner_inline_view.parent_fin_category_id
208 AND f.fin_category_id = inner_inline_view.fin_category_id
209 '||l_budget_decode||'
210
211 GROUP BY '||p_aggrt_viewby_id||', inner_inline_view.viewby, inner_inline_view.sort_order
212
213 ) inline_view
214
215 GROUP BY inline_view.viewby, inline_view.viewby_id, inline_view.sort_order
216
217 ORDER BY NVL(inline_view.sort_order,999999) asc, NVL(FII_EA_XTD_EXP, -999999999) DESC';
218
219 RETURN sqlstmt;
220
221 END get_revexp;
222
223 END fii_ea_page_pkg;
224