[Home] [Help]
PACKAGE BODY: APPS.FII_PSI_ENCUM_SUM_PKG
Source
1 PACKAGE BODY fii_psi_encum_sum_pkg AS
2 /* $Header: FIIPSIENB.pls 120.6.12000000.2 2007/04/16 06:54:25 dhmehra ship $ */
3
4 PROCEDURE get_encum_sum(
5 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
6 p_enc_sum_sql OUT NOCOPY VARCHAR2,
7 p_enc_sum_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
8 ) IS
9
10 sqlstmt VARCHAR2(20000); -- Variable that stores the final SQL query
11
12 l_aggrt_viewby_id VARCHAR2(240); -- Variable to store viewby_id when using aggregate mv
13 l_nonaggrt_viewby_id VARCHAR2(240); -- Variable to store viewby_id when using nonaggregate mv
14 l_aggrt_gt_is_empty VARCHAR2(240); -- Variable to check if fii_pmv_aggrt_gt is empty
15 l_non_aggrt_gt_is_empty VARCHAR2(240); -- Variable to check if fii_pmv_non_aggrt_gt is empty
16 l_roll_column VARCHAR2(240); -- Variable to append rolling period expression (ytd or qtd) based on period type chosen
17 l_xtd_column VARCHAR2(240); -- Variable to append rolling period expression (ytd or qtd or mtd) based on period type chosen
18
19 l_query_start VARCHAR2(10000); -- Variable to store the prefix part of final query
20 l_fii_gl_agrt_sum_mv VARCHAR2(10000); -- Variable to store the part of query that hits fii_gl_agrt_sum_mv_p_v
21 l_fii_gl_base_map_mv VARCHAR2(10000); -- Variable to store the part of query that hits fii_gl_base_map_mv_p_v
22 l_trend_sum_mv_sql VARCHAR2(10000); -- Variable to store the part of query that hits fii_gl_trend_sum_mv_p_v
23 l_query_end VARCHAR2(10000); -- Variable to store the suffix part of final query
24
25 p_snap_aggrt_viewby_id VARCHAR2(30); /* Added for Bug 4199668*/
26
27 /*Bug 4192505: Variables intitialized*/
28 l_fud2_enabled_flag VARCHAR2(1);
29 l_fud2_where VARCHAR2(300);
30 l_fud2_snap_where VARCHAR2(300);
31 l_fud2_from VARCHAR2(100);
32
33 /* Bug 4190997: Variables Defined */
34
35 l_xtd_drill_url VARCHAR2(300);
36
37 BEGIN
38
39 /* Clear global parameters AND read the new parameters */
40 -- Sets all g_% variables to its default values
41 fii_ea_util_pkg.reset_globals;
42
43 -- Reads the parameters from the parameter portlet
44 fii_ea_util_pkg.get_parameters( p_page_parameter_tbl);
45
46 -- Sets fin_cat_type to Operating Expenses(OE) as Encumbrances are part of OE
47 fii_ea_util_pkg.g_fin_cat_type := 'OE';
48
49 -- Gets the viewby_id
50 fii_ea_util_pkg.get_viewby_id(l_aggrt_viewby_id, p_snap_aggrt_viewby_id, l_nonaggrt_viewby_id);
51
52 -- Populates the security related global temporary tables
53 fii_ea_util_pkg.populate_security_gt_tables(l_aggrt_gt_is_empty, l_non_aggrt_gt_is_empty);
54
55 -- Initialise the global variables to set FII_PREVIOUS_ONE_DATE, etc.
56 fii_ea_util_pkg.get_rolling_period();
57
58 -- Decision ytd, qtd, mtd based on the period type chosen
59 CASE fii_ea_util_pkg.g_page_period_type
60
61 WHEN 'FII_TIME_ENT_YEAR' THEN
62 l_roll_column := 'qtd';
63 l_xtd_column := 'ytd' ;
64
65 WHEN 'FII_TIME_ENT_QTR' THEN
66 l_roll_column := 'mtd';
67 l_xtd_column := 'qtd' ;
68
69 WHEN 'FII_TIME_ENT_PERIOD' THEN
70 l_roll_column := 'mtd';
71 l_xtd_column := 'mtd' ;
72
73 END CASE;
74
75 /* Bug 4192505 Start */
76 SELECT dbi_enabled_flag INTO l_fud2_enabled_flag
77 FROM fii_financial_dimensions
78 WHERE dimension_short_name = 'FII_USER_DEFINED_2';
79
80 IF l_fud2_enabled_flag = 'Y' THEN
81
82 IF fii_ea_util_pkg.g_view_by = 'FII_USER_DEFINED+FII_USER_DEFINED_2' THEN
83
84 l_fud2_from := ' fii_udd2_hierarchies fud2_hier, ';
85
86 l_fud2_snap_where := ' and fud2_hier.parent_value_id = gt.fud2_id
87 and fud2_hier.child_value_id = f.fud2_id ';
88
89 l_fud2_where := ' and fud2_hier.parent_value_id = inner_inline_view.fud2_id
90 and fud2_hier.child_value_id = f.fud2_id ';
91
92 ELSIF fii_ea_util_pkg.g_fud2_id <> 'All' THEN
93
94 l_fud2_from := ' fii_udd2_hierarchies fud2_hier, ';
95
96 l_fud2_snap_where := ' and fud2_hier.parent_value_id = gt.fud2_id
97 and fud2_hier.child_value_id = f.fud2_id ';
98
99 l_fud2_where := ' and fud2_hier.parent_value_id = inner_inline_view.fud2_id
100 and fud2_hier.child_value_id = f.fud2_id ';
101 END IF;
102 END IF;
103 /* Bug 4192505 End */
104
105 /* Bug 4190997 Start */
106 IF fii_ea_util_pkg.g_view_by = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
107 fii_ea_util_pkg.check_if_leaf(fii_ea_util_pkg.g_category_id);
108 ELSIF fii_ea_util_pkg.g_view_by = 'FII_USER_DEFINED+FII_USER_DEFINED_1' THEN
109 fii_ea_util_pkg.check_if_leaf(fii_ea_util_pkg.g_udd1_id);
110 END IF;
111 /* Bug 4190997 End */
112
113 -- Constructing drilldown URL
114 l_xtd_drill_url := 'pFunctionName=FII_PSI_ENC_TREND_DTL&VIEW_BY=TIME+FII_TIME_ENT_PERIOD&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
115
116 l_query_start := '
117 -- Final Query Header
118 SELECT inline_view.viewby VIEWBY,
119 inline_view.viewby_id VIEWBYID,
120 SUM(FII_PSI_XTD) FII_PSI_XTD,
121 SUM(FII_PSI_COMMITMENTS) FII_PSI_COMMITMENTS,
122 SUM(FII_PSI_OBLIGATIONS) FII_PSI_OBLIGATIONS,
123 SUM(FII_PSI_OTHER) FII_PSI_OTHER,
124
125 SUM(FII_PSI_HIST_COL1) FII_PSI_HIST_COL1,
126 SUM(FII_PSI_HIST_COL2) FII_PSI_HIST_COL2,
127 SUM(FII_PSI_HIST_COL3) FII_PSI_HIST_COL3,
128 SUM(FII_PSI_HIST_COL4) FII_PSI_HIST_COL4,
129 DECODE(SUM(FII_PSI_XTD), 0, NULL, NULL, NULL, '''|| l_xtd_drill_url||''') FII_PSI_XTD_DRILL,
130 DECODE((SELECT is_leaf_flag
131 FROM fii_company_hierarchies
132 WHERE parent_company_id = inline_view.viewby_id
133 and child_company_id = inline_view.viewby_id),
134 ''Y'',
135 '''',
136 ''pFunctionName=FII_PSI_ENCUM_SUM&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=FII_COMPANIES+FII_COMPANIES&pParamIds=Y'') FII_PSI_COMP_DRILL,
137
138 DECODE((SELECT is_leaf_flag
139 FROM fii_cost_ctr_hierarchies
140 WHERE parent_cc_id = inline_view.viewby_id
141 and child_cc_id = inline_view.viewby_id),
142 ''Y'',
143 '''',
144 ''pFunctionName=FII_PSI_ENCUM_SUM&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+HRI_CL_ORGCC&pParamIds=Y'') FII_PSI_CC_DRILL,
145 DECODE((SELECT is_leaf_flag
146 FROM fii_fin_item_leaf_hiers
147 WHERE parent_fin_cat_id = inline_view.viewby_id
148 and child_fin_cat_id = inline_view.viewby_id),
149 ''Y'',
150 '''',
151 -- Additional DECODE added for bug 4190997
152 DECODE(:G_ID, inline_view.viewby_id,'''',
153 ''pFunctionName=FII_PSI_ENCUM_SUM&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=FINANCIAL ITEM+GL_FII_FIN_ITEM&pParamIds=Y'')) FII_PSI_CAT_DRILL,
154 DECODE((SELECT is_leaf_flag
155 FROM fii_udd1_hierarchies
156 WHERE parent_value_id = inline_view.viewby_id
157 and child_value_id = inline_view.viewby_id),
158 ''Y'',
159 '''',
160 -- Additional DECODE added for bug 4190997
161 DECODE(:G_ID, inline_view.viewby_id,'''',
162 ''pFunctionName=FII_PSI_ENCUM_SUM&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=FII_USER_DEFINED+FII_USER_DEFINED_1&pParamIds=Y'')) FII_PSI_PROJECT_DRILL,
163 DECODE((SELECT is_leaf_flag
164 FROM fii_udd2_hierarchies
165 WHERE parent_value_id = inline_view.viewby_id
166 and child_value_id = inline_view.viewby_id),
167 ''Y'',
168 '''',
169 ''pFunctionName=FII_PSI_ENCUM_SUM&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=FII_USER_DEFINED+FII_USER_DEFINED_2&pParamIds=Y'') FII_PSI_UD2_DRILL,
170
171 SUM(SUM(FII_PSI_COMMITMENTS)) OVER () FII_PSI_GT_COMMITMENTS,
172 SUM(SUM(FII_PSI_OBLIGATIONS)) OVER () FII_PSI_GT_OBLIGATIONS,
173 SUM(SUM(FII_PSI_OTHER)) OVER () FII_PSI_GT_OTHER,
174 SUM(SUM(FII_PSI_XTD)) OVER () FII_PSI_GT_XTD,
175 SUM(SUM(FII_PSI_HIST_COL1)) OVER () FII_PSI_GT_HIST_COL1,
176 SUM(SUM(FII_PSI_HIST_COL2)) OVER () FII_PSI_GT_HIST_COL2,
177 SUM(SUM(FII_PSI_HIST_COL3)) OVER () FII_PSI_GT_HIST_COL3,
178 SUM(SUM(FII_PSI_HIST_COL4)) OVER () FII_PSI_GT_HIST_COL4
179
180 FROM
181 ( ';
182
183 l_fii_gl_agrt_sum_mv := '
184 -- This part of the query gets executed if the nodes selected are aggregated nodes
185 SELECT /*+ index(f fii_gl_agrt_sum_mv_n1) */
186 '||l_aggrt_viewby_id||' viewby_id, inner_inline_view.viewby viewby, inner_inline_view.sort_order sort_order,
187
188 SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
189 (CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.commitments_g ELSE NULL END) ) ) FII_PSI_COMMITMENTS,
190 SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
191 (CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.obligations_g ELSE NULL END) ) ) FII_PSI_OBLIGATIONS,
192 SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
193 (CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.others_g ELSE NULL END) ) ) FII_PSI_OTHER,
194 SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
195 (CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.commitments_g ELSE NULL END) ) )
196 + SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
197 (CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.obligations_g ELSE NULL END) ) )
198 + SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
199 (CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.others_g ELSE NULL END) ) ) FII_PSI_XTD,
200
201 SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_THREE_END_DATE,
202 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.commitments_g ELSE NULL END) ) )
203 + SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_THREE_END_DATE,
204 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.obligations_g ELSE NULL END) ) )
205 + SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_THREE_END_DATE,
206 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.others_g ELSE NULL END) ) ) FII_PSI_HIST_COL1,
207 SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_TWO_END_DATE,
208 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.commitments_g ELSE NULL END) ) )
209 + SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_TWO_END_DATE,
210 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.obligations_g ELSE NULL END) ) )
211 + SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_TWO_END_DATE,
212 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.others_g ELSE NULL END) ) ) FII_PSI_HIST_COL2,
213 SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_ONE_END_DATE,
214 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.commitments_g ELSE NULL END) ) )
215 + SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_ONE_END_DATE,
216 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.obligations_g ELSE NULL END) ) )
217 + SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_ONE_END_DATE,
218 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.others_g ELSE NULL END) ) ) FII_PSI_HIST_COL3,
219 SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
220 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.commitments_g ELSE NULL END) ) )
221 + SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
222 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.obligations_g ELSE NULL END) ) )
223 + SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
224 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.others_g ELSE NULL END) ) ) FII_PSI_HIST_COL4
225
226 FROM fii_gl_agrt_sum_mv_p_v f,
227 '||l_fud2_from||'
228 (
229 -- This part of the query joins the fii_time_structures with fii_pmv_aggrt_gt
230 SELECT /*+ NO_MERGE cardinality(gt 1) */ *
231 FROM fii_time_structures cal, fii_pmv_aggrt_gt gt
232 WHERE report_date IN (
233 :PREVIOUS_ONE_END_DATE,
234 :PREVIOUS_TWO_END_DATE,
235 :PREVIOUS_THREE_END_DATE,
236 :BUD_ASOF_DATE
237 )
238 AND (
239 BITAND(cal.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
240 OR
241 BITAND(cal.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND
242 )
243 ) inner_inline_view
244
245 WHERE f.time_id = inner_inline_view.time_id
246 AND f.period_type_id = inner_inline_view.period_type_id
247 AND f.parent_company_id = inner_inline_view.parent_company_id
248 AND f.company_id = inner_inline_view.company_id
249 AND f.parent_cost_center_id = inner_inline_view.parent_cc_id
250 AND f.cost_center_id = inner_inline_view.cc_id
251 AND f.parent_fin_category_id = inner_inline_view.parent_fin_category_id
252 AND f.fin_category_id = inner_inline_view.fin_category_id
253 AND f.parent_fud1_id = inner_inline_view.parent_fud1_id
254 AND f.fud1_id = inner_inline_view.fud1_id
255 '||l_fud2_where||'
256
257 GROUP BY '||l_aggrt_viewby_id||', inner_inline_view.viewby, inner_inline_view.sort_order ';
258
259 l_fii_gl_base_map_mv := '
260 --This part of the query gets executed if the nodes selected are non aggregated nodes
261 SELECT /*+ index(f fii_gl_base_map_mv_n1) */ '||l_nonaggrt_viewby_id||' viewby_id, inner_inline_view.viewby viewby, inner_inline_view.sort_order sort_order,
262
263 SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
264 (CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.commitments_g ELSE NULL END) ) ) FII_PSI_COMMITMENTS,
265 SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
266 (CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.obligations_g ELSE NULL END) ) ) FII_PSI_OBLIGATIONS,
267 SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
268 (CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.others_g ELSE NULL END) ) ) FII_PSI_OTHER,
269 SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
270 (CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.commitments_g ELSE NULL END) ) )
271 + SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
272 (CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.obligations_g ELSE NULL END) ) )
273 + SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
274 (CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.others_g ELSE NULL END) ) ) FII_PSI_XTD,
275
276 SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_THREE_END_DATE,
277 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.commitments_g ELSE NULL END) ) )
278 + SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_THREE_END_DATE,
279 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.obligations_g ELSE NULL END) ) )
280 + SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_THREE_END_DATE,
281 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.others_g ELSE NULL END) ) ) FII_PSI_HIST_COL1,
282 SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_TWO_END_DATE,
283 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.commitments_g ELSE NULL END) ) )
284 + SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_TWO_END_DATE,
285 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.obligations_g ELSE NULL END) ) )
286 + SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_TWO_END_DATE,
287 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.others_g ELSE NULL END) ) ) FII_PSI_HIST_COL2,
288 SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_ONE_END_DATE,
289 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.commitments_g ELSE NULL END) ) )
290 + SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_ONE_END_DATE,
291 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.obligations_g ELSE NULL END) ) )
292 + SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_ONE_END_DATE,
293 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.others_g ELSE NULL END) ) ) FII_PSI_HIST_COL3,
294 SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
295 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.commitments_g ELSE NULL END) ) )
296 + SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
297 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.obligations_g ELSE NULL END) ) )
298 + SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
299 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.others_g ELSE NULL END) ) ) FII_PSI_HIST_COL4
300
301 FROM fii_gl_base_map_mv_p_v f,
302 fii_company_hierarchies co_hier,
303 fii_cost_ctr_hierarchies cc_hier,
304 fii_fin_item_leaf_hiers fin_hier,
305 fii_udd1_hierarchies fud1_hier,
306 '||l_fud2_from||'
307 (
308 -- This part of the query joins the fii_time_structures with fii_pmv_non_aggrt_gt
309 SELECT /*+ NO_MERGE cardinality(gt 1) */ *
310 FROM fii_time_structures cal, fii_pmv_non_aggrt_gt gt
311 WHERE report_date IN (
312 :PREVIOUS_ONE_END_DATE,
313 :PREVIOUS_TWO_END_DATE,
314 :PREVIOUS_THREE_END_DATE,
315 :BUD_ASOF_DATE
316 )
317 AND
318 (
319 BITAND(cal.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
320 OR
321 BITAND(cal.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND
322 )
323 ) inner_inline_view
324
325 WHERE f.time_id = inner_inline_view.time_id
326 AND f.period_type_id=inner_inline_view.period_type_id
327 AND f.company_id = co_hier.child_company_id
328 AND f.cost_center_id = cc_hier.child_cc_id
329 AND f.fin_category_id = fin_hier.child_fin_cat_id
330 AND f.fud1_id = fud1_hier.child_value_id
331 AND co_hier.parent_company_id = inner_inline_view.company_id
332 AND cc_hier.parent_cc_id = inner_inline_view.cost_center_id
333 AND fin_hier.parent_fin_cat_id = inner_inline_view.fin_category_id
334 AND fud1_hier.parent_value_id = inner_inline_view.fud1_id
335 '||l_fud2_where||'
336 GROUP BY '||l_nonaggrt_viewby_id||', inner_inline_view.viewby, inner_inline_view.sort_order ';
337
338 l_trend_sum_mv_sql := '
339 -- query formed by hitting fii_gl_trend_sum_mv_p_v
340
341 SELECT '||l_aggrt_viewby_id||' viewby_id,
342 inner_inline_view.viewby viewby,
343 inner_inline_view.sort_order sort_order,
344 SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
345 (CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.commitments_g ELSE NULL END) ) ) FII_PSI_COMMITMENTS,
346 SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
347 (CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.obligations_g ELSE NULL END) ) ) FII_PSI_OBLIGATIONS,
348 SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
349 (CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.others_g ELSE NULL END) ) ) FII_PSI_OTHER,
350 SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
351 (CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.commitments_g ELSE NULL END) ) )
352 + SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
353 (CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.obligations_g ELSE NULL END) ) )
354 + SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
355 (CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.others_g ELSE NULL END) ) ) FII_PSI_XTD,
356
357 SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_THREE_END_DATE,
358 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.commitments_g ELSE NULL END) ) )
359 + SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_THREE_END_DATE,
360 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.obligations_g ELSE NULL END) ) )
361 + SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_THREE_END_DATE,
362 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.others_g ELSE NULL END) ) ) FII_PSI_HIST_COL1,
363 SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_TWO_END_DATE,
364 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.commitments_g ELSE NULL END) ) )
365 + SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_TWO_END_DATE,
366 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.obligations_g ELSE NULL END) ) )
367 + SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_TWO_END_DATE,
368 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.others_g ELSE NULL END) ) ) FII_PSI_HIST_COL2,
369 SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_ONE_END_DATE,
370 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.commitments_g ELSE NULL END) ) )
371 + SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_ONE_END_DATE,
372 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.obligations_g ELSE NULL END) ) )
373 + SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_ONE_END_DATE,
374 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.others_g ELSE NULL END) ) ) FII_PSI_HIST_COL3,
375 SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
376 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.commitments_g ELSE NULL END) ) )
377 + SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
378 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.obligations_g ELSE NULL END) ) )
379 + SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
380 (CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.others_g ELSE NULL END) ) ) FII_PSI_HIST_COL4
381
382 FROM fii_gl_trend_sum_mv_p_v f,
383 (
384 -- This part of the query joins the fii_time_structures with fii_pmv_aggrt_gt
385 SELECT /*+ NO_MERGE cardinality(gt 1) */ *
386 FROM fii_time_structures cal, fii_pmv_aggrt_gt gt
387 WHERE report_date IN (
388 :PREVIOUS_ONE_END_DATE,
389 :PREVIOUS_TWO_END_DATE,
390 :PREVIOUS_THREE_END_DATE,
391 :BUD_ASOF_DATE
392 )
393 AND (
394 BITAND(cal.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
395 OR
396 BITAND(cal.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND
397 )
398 ) inner_inline_view
399
400 WHERE f.time_id = inner_inline_view.time_id
401 AND f.period_type_id = inner_inline_view.period_type_id
402 AND f.parent_company_id = inner_inline_view.parent_company_id
403 AND f.company_id = inner_inline_view.company_id
404 AND f.parent_cost_center_id = inner_inline_view.parent_cc_id
405 AND f.cost_center_id = inner_inline_view.cc_id
406 AND f.parent_fin_category_id = inner_inline_view.parent_fin_category_id
407 AND f.fin_category_id = inner_inline_view.fin_category_id
408
409 GROUP BY '||l_aggrt_viewby_id||', inner_inline_view.viewby, inner_inline_view.sort_order ';
410
411 l_query_end := '
412 -- Final Query Header
413 ) inline_view
414
415 GROUP BY inline_view.viewby, inline_view.viewby_id, inline_view.sort_order
416 ORDER BY NVL(inline_view.sort_order,999999) asc, NVL(FII_PSI_XTD, -999999999) desc';
417
418 -- Adding the Final Query Header
419 sqlstmt := l_query_start;
420
421 IF fii_ea_util_pkg.g_if_trend_sum_mv = 'Y' THEN
422 -- Appending the part of query that hits fii_gl_trend_sum_mv_p_v
423 sqlstmt := sqlstmt || l_trend_sum_mv_sql;
424 ELSIF l_aggrt_gt_is_empty = 'N' then
425 -- Appending the part of query that hits fii_gl_agrt_sum_mv_p_v
426 sqlstmt := sqlstmt || l_fii_gl_agrt_sum_mv;
427 IF l_non_aggrt_gt_is_empty = 'N' then
428 -- Appending the part of query that hits fii_gl_base_map_mv_p_v
429 sqlstmt := sqlstmt || ' UNION ALL ' || l_fii_gl_base_map_mv;
430 END IF;
431 ELSIF l_non_aggrt_gt_is_empty = 'N' then
432 -- Appending the part of query that hits fii_gl_base_map_mv_p_v
433 sqlstmt := sqlstmt || l_fii_gl_base_map_mv;
434 ELSE
435 -- Default case
436 -- Appending the part of query that hits fii_gl_agrt_sum_mv_p_v
437 sqlstmt := sqlstmt || l_fii_gl_agrt_sum_mv;
438 END IF;
439
440 -- Appending the Final Query Footer
441 sqlstmt := sqlstmt || l_query_end;
442
443
444 -- Calling the bind_variable API
445 fii_ea_util_pkg.bind_variable(
446 p_sqlstmt => sqlstmt,
447 p_Page_parameter_tbl => p_page_parameter_tbl,
448 p_sql_output => p_enc_sum_sql,
449 p_bind_output_table => p_enc_sum_output
450 );
451
452
453 END get_encum_sum;
454
455 PROCEDURE get_encum_sum_port(
456 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
457 p_enc_sum_sql OUT NOCOPY VARCHAR2,
458 p_enc_sum_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
459 ) IS
460
461 sqlstmt VARCHAR2(20000); -- Variable that stores the final SQL query
462
463 l_aggrt_viewby_id VARCHAR2(240); -- Variable to store viewby_id when using aggregate mv
464 l_nonaggrt_viewby_id VARCHAR2(240); -- Variable to store viewby_id when using nonaggregate mv
465 l_aggrt_gt_is_empty VARCHAR2(240); -- Variable to check if fii_pmv_aggrt_gt is empty
466 l_non_aggrt_gt_is_empty VARCHAR2(240); -- Variable to check if fii_pmv_non_aggrt_gt is empty
467 l_xtd_column VARCHAR2(240); -- Variable to append rolling period expression (ytd or qtd or mtd) based on period type chosen
468
469 l_query_start VARCHAR2(10000); -- Variable to store the prefix part of final query
470 l_fii_gl_agrt_sum_mv VARCHAR2(10000); -- Variable to store the part of query that hits fii_gl_agrt_sum_mv_p_v
471 l_fii_gl_base_map_mv VARCHAR2(10000); -- Variable to store the part of query that hits fii_gl_base_map_mv_p_v
472 l_trend_sum_mv_sql VARCHAR2(10000); -- Variable to store the part of query that hits fii_gl_trend_sum_mv_p_v
473 l_query_end VARCHAR2(10000); -- Variable to store the suffix part of final query
474
475 p_snap_aggrt_viewby_id VARCHAR2(30); /* Added for Bug 4199668*/
476
477 /*Bug 4192505: Variables intitialized*/
478 l_fud2_enabled_flag VARCHAR2(1);
479 l_fud2_where VARCHAR2(300);
480 l_fud2_snap_where VARCHAR2(300);
481 l_fud2_from VARCHAR2(100);
482
483 /* Bug 4190997: Variables Defined */
484 l_xtd_drill_url VARCHAR2(300);
485
486 BEGIN
487
488 /* Clear global parameters AND read the new parameters */
489 -- Sets all g_% variables to its default values
490 fii_ea_util_pkg.reset_globals;
491
492 -- Reads the parameters from the parameter portlet
493 fii_ea_util_pkg.get_parameters( p_page_parameter_tbl);
494
495 -- Sets fin_cat_type to Operating Expenses(OE) as Encumbrances are part of OE
496 fii_ea_util_pkg.g_fin_cat_type := 'OE';
497
498 -- Gets the viewby_id
499 fii_ea_util_pkg.get_viewby_id(l_aggrt_viewby_id, p_snap_aggrt_viewby_id, l_nonaggrt_viewby_id);
500
501 -- Populates the security related global temporary tables
502 fii_ea_util_pkg.populate_security_gt_tables(l_aggrt_gt_is_empty, l_non_aggrt_gt_is_empty);
503
504 -- Initialise the global variables to set FII_PREVIOUS_ONE_DATE, etc.
505 fii_ea_util_pkg.get_rolling_period();
506
507 -- Decision ytd, qtd, mtd based on the period type chosen
508 CASE fii_ea_util_pkg.g_page_period_type
509
510 WHEN 'FII_TIME_ENT_YEAR' THEN
511 l_xtd_column := 'ytd' ;
512
513 WHEN 'FII_TIME_ENT_QTR' THEN
514 l_xtd_column := 'qtd' ;
515
516 WHEN 'FII_TIME_ENT_PERIOD' THEN
517 l_xtd_column := 'mtd' ;
518
519 END CASE;
520
521 /* Bug 4192505 Start */
522 SELECT dbi_enabled_flag INTO l_fud2_enabled_flag
523 FROM fii_financial_dimensions
524 WHERE dimension_short_name = 'FII_USER_DEFINED_2';
525
526 IF l_fud2_enabled_flag = 'Y' THEN
527
528 IF fii_ea_util_pkg.g_view_by = 'FII_USER_DEFINED+FII_USER_DEFINED_2' THEN
529
530 l_fud2_from := ' fii_udd2_hierarchies fud2_hier, ';
531
532 l_fud2_snap_where := ' and fud2_hier.parent_value_id = gt.fud2_id
533 and fud2_hier.child_value_id = f.fud2_id ';
534
535 l_fud2_where := ' and fud2_hier.parent_value_id = inner_inline_view.fud2_id
536 and fud2_hier.child_value_id = f.fud2_id ';
537
538 ELSIF fii_ea_util_pkg.g_fud2_id <> 'All' THEN
539
540 l_fud2_from := ' fii_udd2_hierarchies fud2_hier, ';
541
542 l_fud2_snap_where := ' and fud2_hier.parent_value_id = gt.fud2_id
543 and fud2_hier.child_value_id = f.fud2_id ';
544
545 l_fud2_where := ' and fud2_hier.parent_value_id = inner_inline_view.fud2_id
546 and fud2_hier.child_value_id = f.fud2_id ';
547 END IF;
548 END IF;
549 /* Bug 4192505 End */
550
551 /* Bug 4190997 Start */
552 IF fii_ea_util_pkg.g_view_by = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
553 fii_ea_util_pkg.check_if_leaf(fii_ea_util_pkg.g_category_id);
554 ELSIF fii_ea_util_pkg.g_view_by = 'FII_USER_DEFINED+FII_USER_DEFINED_1' THEN
555 fii_ea_util_pkg.check_if_leaf(fii_ea_util_pkg.g_udd1_id);
556 END IF;
557 /* Bug 4190997 End */
558
559 -- Constructing drilldown URL
560 l_xtd_drill_url := 'pFunctionName=FII_PSI_ENC_TREND_DTL&VIEW_BY=TIME+FII_TIME_ENT_PERIOD&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
561
562 l_query_start := '
563 -- Final Query Header
564 SELECT inline_view.viewby VIEWBY,
565 inline_view.viewby_id VIEWBYID,
566 SUM(FII_PSI_XTD) FII_PSI_XTD,
567 SUM(FII_PSI_COMMITMENTS) FII_PSI_COMMITMENTS,
568 SUM(FII_PSI_OBLIGATIONS) FII_PSI_OBLIGATIONS,
569 SUM(FII_PSI_OTHER) FII_PSI_OTHER,
570 DECODE(SUM(FII_PSI_XTD), 0, NULL, NULL, NULL, '''|| l_xtd_drill_url||''') FII_PSI_XTD_DRILL,
571 DECODE((SELECT is_leaf_flag
572 FROM fii_company_hierarchies
573 WHERE parent_company_id = inline_view.viewby_id
574 AND child_company_id = inline_view.viewby_id),
575 ''Y'',
576 '''',
577 ''pFunctionName=FII_PSI_ENCUM_SUM&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=FII_COMPANIES+FII_COMPANIES&pParamIds=Y'') FII_PSI_COMP_DRILL,
578 SUM(SUM(FII_PSI_COMMITMENTS)) OVER () FII_PSI_GT_COMMITMENTS,
579 SUM(SUM(FII_PSI_OBLIGATIONS)) OVER () FII_PSI_GT_OBLIGATIONS,
580 SUM(SUM(FII_PSI_OTHER)) OVER () FII_PSI_GT_OTHER,
581 SUM(SUM(FII_PSI_XTD)) OVER () FII_PSI_GT_XTD
582
583 FROM
584 ( ';
585
586 l_fii_gl_agrt_sum_mv := '
587 -- This part of the query gets executed if the nodes selected are aggregated nodes
588
589 SELECT /*+ index(f fii_gl_agrt_sum_mv_n1) */
590 '||l_aggrt_viewby_id||' viewby_id,
591 inner_inline_view.viewby viewby,
592 inner_inline_view.sort_order sort_order,
593 SUM(f.commitments_g) FII_PSI_COMMITMENTS,
594 SUM(f.obligations_g) FII_PSI_OBLIGATIONS,
595 SUM(f.others_g) FII_PSI_OTHER,
596 SUM(f.commitments_g) + SUM(f.obligations_g) + SUM(f.others_g) FII_PSI_XTD
597
598 FROM fii_gl_agrt_sum_mv_p_v f,
599 '||l_fud2_from||'
600 (
601 -- This part of the query joins the fii_time_structures with fii_pmv_aggrt_gt
602 SELECT /*+ NO_MERGE cardinality(gt 1) */ *
603 FROM fii_time_structures cal, fii_pmv_aggrt_gt gt
604 WHERE report_date = :BUD_ASOF_DATE
605 AND (BITAND(cal.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND)
606 ) inner_inline_view
607
608 WHERE f.time_id = inner_inline_view.time_id
609 AND f.period_type_id = inner_inline_view.period_type_id
610 AND f.parent_company_id = inner_inline_view.parent_company_id
611 AND f.company_id = inner_inline_view.company_id
612 AND f.parent_cost_center_id = inner_inline_view.parent_cc_id
613 AND f.cost_center_id = inner_inline_view.cc_id
614 AND f.parent_fin_category_id = inner_inline_view.parent_fin_category_id
615 AND f.fin_category_id = inner_inline_view.fin_category_id
616 AND f.parent_fud1_id = inner_inline_view.parent_fud1_id
617 AND f.fud1_id = inner_inline_view.fud1_id
618 '||l_fud2_where||'
619
620 GROUP BY '||l_aggrt_viewby_id||', inner_inline_view.viewby, inner_inline_view.sort_order ';
621
622 l_fii_gl_base_map_mv := '
623 --This part of the query gets executed if the nodes selected are non aggregated nodes
624
625 SELECT /*+ index(f fii_gl_base_map_mv_n1) */
626 '||l_nonaggrt_viewby_id||' viewby_id,
627 inner_inline_view.viewby viewby,
628 inner_inline_view.sort_order sort_order,
629 SUM(f.commitments_g) FII_PSI_COMMITMENTS,
630 SUM(f.obligations_g) FII_PSI_OBLIGATIONS,
631 SUM(f.others_g) FII_PSI_OTHER,
632 SUM(f.commitments_g) + SUM(f.obligations_g) + SUM(f.others_g) FII_PSI_XTD
633
634 FROM fii_gl_base_map_mv_p_v f,
635 fii_company_hierarchies co_hier,
636 fii_cost_ctr_hierarchies cc_hier,
637 fii_fin_item_leaf_hiers fin_hier,
638 fii_udd1_hierarchies fud1_hier,
639 '||l_fud2_from||'
640 (
641 -- This part of the query joins the fii_time_structures with fii_pmv_non_aggrt_gt
642 SELECT /*+ NO_MERGE cardinality(gt 1) */ *
643 FROM fii_time_structures cal, fii_pmv_non_aggrt_gt gt
644 WHERE report_date = :BUD_ASOF_DATE
645 AND ( BITAND(cal.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND)
646 ) inner_inline_view
647
648 WHERE f.time_id = inner_inline_view.time_id
649 AND f.period_type_id=inner_inline_view.period_type_id
650 AND f.company_id = co_hier.child_company_id
651 AND f.cost_center_id = cc_hier.child_cc_id
652 AND f.fin_category_id = fin_hier.child_fin_cat_id
653 AND f.fud1_id = fud1_hier.child_value_id
654 AND co_hier.parent_company_id = inner_inline_view.company_id
655 AND cc_hier.parent_cc_id = inner_inline_view.cost_center_id
656 AND fin_hier.parent_fin_cat_id = inner_inline_view.fin_category_id
657 AND fud1_hier.parent_value_id = inner_inline_view.fud1_id
658 '||l_fud2_where||'
659
660 GROUP BY '||l_nonaggrt_viewby_id||', inner_inline_view.viewby, inner_inline_view.sort_order ';
661
662 l_trend_sum_mv_sql := '
663 -- query that hits fii_gl_trend_sum_mv_p_v
664
665 SELECT '||l_aggrt_viewby_id||' viewby_id,
666 inner_inline_view.viewby viewby,
667 inner_inline_view.sort_order sort_order,
668 SUM(f.commitments_g) FII_PSI_COMMITMENTS,
669 SUM(f.obligations_g) FII_PSI_OBLIGATIONS,
670 SUM(f.others_g) FII_PSI_OTHER,
671 SUM(f.commitments_g) + SUM(f.obligations_g) + SUM(f.others_g) FII_PSI_XTD
672
673 FROM fii_gl_trend_sum_mv_p_v f,
674 (
675 -- This part of the query joins the fii_time_structures with fii_pmv_aggrt_gt
676 SELECT /*+ NO_MERGE cardinality(gt 1) */ *
677 FROM fii_time_structures cal,
678 fii_pmv_aggrt_gt gt
679 WHERE report_date = :BUD_ASOF_DATE
680 AND ( BITAND(cal.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND)
681 ) inner_inline_view
682
683 WHERE f.time_id = inner_inline_view.time_id
684 AND f.period_type_id = inner_inline_view.period_type_id
685 AND f.parent_company_id = inner_inline_view.parent_company_id
686 AND f.company_id = inner_inline_view.company_id
687 AND f.parent_cost_center_id = inner_inline_view.parent_cc_id
688 AND f.cost_center_id = inner_inline_view.cc_id
689 AND f.parent_fin_category_id = inner_inline_view.parent_fin_category_id
690 AND f.fin_category_id = inner_inline_view.fin_category_id
691
692 GROUP BY '||l_aggrt_viewby_id||', inner_inline_view.viewby, inner_inline_view.sort_order ';
693
694 l_query_end := '
695 -- Final Query Header
696 ) inline_view
697
698 GROUP BY inline_view.viewby, inline_view.viewby_id, inline_view.sort_order
699 ORDER BY NVL(inline_view.sort_order,999999) asc, NVL(FII_PSI_XTD, -999999999) desc';
700
701 -- Adding the Final Query Header
702 sqlstmt := l_query_start;
703
704 IF fii_ea_util_pkg.g_if_trend_sum_mv = 'Y' THEN
705 -- Appending the part of query that hits fii_gl_trend_sum_mv_p_v
706 sqlstmt := sqlstmt || l_trend_sum_mv_sql;
707 ELSIF l_aggrt_gt_is_empty = 'N' then
708 -- Appending the part of query that hits fii_gl_agrt_sum_mv_p_v
709 sqlstmt := sqlstmt || l_fii_gl_agrt_sum_mv;
710 IF l_non_aggrt_gt_is_empty = 'N' then
711 -- Appending the part of query that hits fii_gl_base_map_mv_p_v
712 sqlstmt := sqlstmt || ' UNION ALL ' || l_fii_gl_base_map_mv;
713 END IF;
714 ELSIF l_non_aggrt_gt_is_empty = 'N' then
715 -- Appending the part of query that hits fii_gl_base_map_mv_p_v
716 sqlstmt := sqlstmt || l_fii_gl_base_map_mv;
717 ELSE
718 -- Default case
719 -- Appending the part of query that hits fii_gl_agrt_sum_mv_p_v
720 sqlstmt := sqlstmt || l_fii_gl_agrt_sum_mv;
721 END IF;
722
723 -- Appending the Final Query Footer
724 sqlstmt := sqlstmt || l_query_end;
725
726 -- Calling the bind_variable API
727 fii_ea_util_pkg.bind_variable(
728 p_sqlstmt => sqlstmt,
729 p_Page_parameter_tbl => p_page_parameter_tbl,
730 p_sql_output => p_enc_sum_sql,
731 p_bind_output_table => p_enc_sum_output
732 );
733
734
735 END get_encum_sum_port;
736
737 END fii_psi_encum_sum_pkg;
738