[Home] [Help]
PACKAGE BODY: APPS.FII_PSI_FUNDS_AVAIL_SUM_PKG
Source
1 PACKAGE BODY FII_PSI_FUNDS_AVAIL_SUM_PKG AS
2 /* $Header: FIIPSIFAB.pls 120.13 2007/10/08 08:37:35 arcdixit ship $ */
3
4 PROCEDURE GET_FUNDS_AVAIL_SUM
5 (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
6 funds_avail_sql out NOCOPY VARCHAR2,
7 funds_avail_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
8
9 -- declaration section
10 sqlstmt VARCHAR2(32000); --Bug 6157086
11 sqlstmt_temp VARCHAR2(25000);
12 l_outer_sql_b VARCHAR2(6000);
13 l_outer_sql_e VARCHAR2(200);
14 l_inner_sql_sys VARCHAR2(5000);
15 l_inner_sql_sys_agg VARCHAR2(5000);
16 l_inner_sql_sys_nonagg VARCHAR2(5000);
17 l_union_sql VARCHAR2(20);
18 l_inner_sql_nonsys VARCHAR2(6000);
19 l_inner_sql_nonsys_agg VARCHAR2(8000);
20 l_inner_sql_nonsys_nonagg VARCHAR2(8000);
21 l_insert_sql_b VARCHAR2(2000);
22 l_schema_name VARCHAR2(10);
23 l_sort_order VARCHAR2(50);
24
25 l_aggrt_gt_is_empty VARCHAR2(1);
26 l_non_aggrt_gt_is_empty VARCHAR2(1);
27 l_aggrt_viewby_id VARCHAR2(50);
28 l_non_aggrt_viewby_id VARCHAR2(50);
29 l_snap_aggrt_viewby_id VARCHAR2(30);
30 l_as_of_date DATE;
31 l_page_period_type VARCHAR2(100);
32 l_time_comp VARCHAR2(20);
33
34
35 l_xtd VARCHAR2(3);
36 l_compare_to VARCHAR2(30);
37
38 l_amount_type VARCHAR2(3);
39
40 l_cat_decode VARCHAR2(500) := ' ';
41 l_fud1_decode VARCHAR2(500) := ' ';
42
43 l_view_by VARCHAR2(100);
44 l_fud1_id VARCHAR2(30);
45 l_fud2_id VARCHAR2(30);
46 l_if_leaf_flag VARCHAR2(1);
47 l_snapshot VARCHAR2(1);
48
49 l_ud1_enabled_flag VARCHAR2(1);
50 l_fud1_from VARCHAR2(1200);
51 l_fud1_where VARCHAR2(1200);
52
53 l_enabled_flag VARCHAR2(1);
54 l_fud2_from VARCHAR2(1200);
55 l_fud2_where VARCHAR2(1200);
56 l_prim_or_sec VARCHAR2(10);
57 l_trend_sum_mv_commitment VARCHAR2(30);
58 l_trend_sum_mv_obligated VARCHAR2(30);
59 l_trend_sum_mv_other VARCHAR2(30);
60
61 BEGIN
62 fii_ea_util_pkg.reset_globals;
63 fii_ea_util_pkg.get_parameters(p_page_parameter_tbl);
64
65 fii_ea_util_pkg.get_viewby_id(l_aggrt_viewby_id, l_snap_aggrt_viewby_id, l_non_aggrt_viewby_id);
66 fii_ea_util_pkg.populate_security_gt_tables(l_aggrt_gt_is_empty, l_non_aggrt_gt_is_empty);
67
68 l_as_of_date := fii_ea_util_pkg.g_as_of_date;
69 l_page_period_type := fii_ea_util_pkg.g_page_period_type;
70 l_amount_type := fii_ea_util_pkg.g_amount_type;
71 l_time_comp := fii_ea_util_pkg.g_time_comp;
72 l_view_by := fii_ea_util_pkg.g_view_by;
73 l_fud1_id := fii_ea_util_pkg.g_fud1_id;
74 l_fud2_id := fii_ea_util_pkg.g_fud2_id;
75 l_snapshot := fii_ea_util_pkg.g_snapshot;
76
77 /*l_xtd and l_compare_to are apart of the column names of the mvs in the pmv sql*/
78 IF l_page_period_type = 'FII_TIME_ENT_PERIOD' THEN
79 l_xtd := 'MTD';
80 ELSIF l_page_period_type = 'FII_TIME_ENT_QTR' THEN
81 l_xtd := 'QTD';
82 ELSIF l_page_period_type = 'FII_TIME_ENT_YEAR' THEN
83 l_xtd := 'YTD';
84 END IF;
85
86 IF l_amount_type = 'PTD' THEN l_amount_type := 'MTD'; END IF;
87
88 IF l_amount_type = 'YTD' OR l_time_comp = 'SEQUENTIAL' THEN
89 l_compare_to := 'PRIOR_' || l_amount_type;
90 ELSE
91 l_compare_to := 'LAST_YEAR_' || l_amount_type;
92 END IF;
93
94 /*If the query needs to hit the full mvs for aggregated and nonaggregated nodes,
95 then the query becomes too lengthy and the gt table fii_psi_pmv_gt is used.
96 To report the results correctly, need to know sort order.*/
97 IF l_snapshot = 'Y' OR
98 l_aggrt_gt_is_empty = 'Y' OR l_non_aggrt_gt_is_empty = 'Y' THEN
99 l_sort_order := '';
100 ELSE
101 l_sort_order := ' f.sort_order SORT_ORDER, ';
102 END IF;
103
104 /* Per Bug 4099419, if A has children B and C and budgets are uploaded against A, the
105 report should display A(Direct), B and C when A is chosen or when All is chosen and
106 A is the only node granted access to a user. To display the direct record, use the self
107 record for A to find budgets against A in the base mv. This is only for financial
108 category and user defined dimension 1. */
109 IF l_view_by = 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
110 fii_ea_util_pkg.check_if_leaf(fii_ea_util_pkg.g_category_id);
111 l_cat_decode :=
112 ' and fin_hier.parent_fin_cat_id = DECODE(fin_hier.parent_fin_cat_id, :CATEGORY_ID,
113 fin_hier.child_fin_cat_id, fin_hier.parent_fin_cat_id)';
114 l_if_leaf_flag := fii_ea_util_pkg.g_fin_cat_is_leaf;
115 ELSIF l_view_by = 'FII_USER_DEFINED+FII_USER_DEFINED_1' THEN
116 fii_ea_util_pkg.check_if_leaf(fii_ea_util_pkg.g_udd1_id);
117 l_fud1_decode :=
118 ' and fud1_hier.parent_value_id = DECODE(fud1_hier.parent_value_id, :UDD1_ID,
119 fud1_hier.child_value_id, fud1_hier.parent_value_id)';
120 l_if_leaf_flag := fii_ea_util_pkg.g_ud1_is_leaf;
121 ELSE
122 l_if_leaf_flag := 'Y';
123 END IF;
124
125 /* Bug 5883351: Find out if user defined dimension 1 is enabled or not */
126 SELECT dbi_enabled_flag INTO l_ud1_enabled_flag
127 FROM fii_financial_dimensions
128 WHERE dimension_short_name = 'FII_USER_DEFINED_1';
129
130 /*Remove join to fii_udd2_hierarchies if fud2 is disabled, or fud2 is all
131 and the viewby dimension is not fud2. */
132
133 SELECT dbi_enabled_flag INTO l_enabled_flag
134 FROM fii_financial_dimensions
135 WHERE dimension_short_name = 'FII_USER_DEFINED_2';
136
137 IF l_enabled_flag = 'N' OR (l_fud2_id = 'All'
138 AND l_view_by <> 'FII_USER_DEFINED+FII_USER_DEFINED_2')
139 THEN
140 l_fud2_from := ' ';
141 l_fud2_where := ' ';
142 ELSE
143 l_fud2_from := ' fii_udd2_hierarchies fud2_hier, ';
144 IF l_snapshot = 'Y' then
145 l_fud2_where := ' AND fud2_hier.parent_value_id = gt.fud2_id
146 AND fud2_hier.child_value_id = f.fud2_id ';
147 ELSE
148 l_fud2_where := ' AND fud2_hier.parent_value_id = inner_inline_view.fud2_id
149 AND fud2_hier.child_value_id = f.fud2_id ';
150 END IF;
151
152 END IF;
153
154
155 /*Appended to query if query is too long (needs to hit the full mvs for aggregated
156 and nonaggregated nodes.*/
157 l_insert_sql_b :=
158 'INSERT INTO FII_PSI_PMV_GT(
159 VIEWBY,
160 VIEWBYID,
161 SORT_ORDER,
162 FII_PSI_AVAIL_C,
163 FII_PSI_PRIOR_AVAIL_C,
164 FII_PSI_GT_AVAIL_C,
165 FII_PSI_GT_PRIOR_AVAIL_C,
166 FII_PSI_PCNT_AVAIL_C,
167 FII_PSI_PRIOR_PCNT_AVAIL_C,
168 FII_PSI_GT_PCNT_AVAIL_C,
169 FII_PSI_GT_PRIOR_PCNT_AVAIL_C,
170 FII_PSI_BUDGET_C,
171 FII_PSI_PRIOR_BUDGET_C,
172 FII_PSI_GT_BUDGET_C,
173 FII_PSI_GT_PRIOR_BUDGET_C,
174 FII_PSI_ENCUMBRANCES_C,
175 FII_PSI_PRIOR_ENCUMBRANCES_C,
176 FII_PSI_GT_ENCUMBRANCES_C,
177 FII_PSI_GT_PRIOR_ENCUM_C,
178 FII_PSI_COMMITTED_C_KPI,
179 FII_PSI_PRIOR_COMMITTED_C,
180 FII_PSI_GT_COMMITTED_C_KPI,
181 FII_PSI_GT_PRIOR_COMMITTED_C,
182 FII_PSI_OBLIGATED_C_KPI,
183 FII_PSI_PRIOR_OBLIGATED_C,
184 FII_PSI_GT_OBLIGATED_C_KPI,
185 FII_PSI_GT_PRIOR_OBLIGATED_C,
186 FII_PSI_OTHERS_C_KPI,
187 FII_PSI_PRIOR_OTHERS_C,
188 FII_PSI_GT_OTHERS_C_KPI,
189 FII_PSI_GT_PRIOR_OTHERS_C,
190 FII_PSI_ACTUALS_C,
191 FII_PSI_PRIOR_ACTUALS_C,
192 FII_PSI_GT_ACTUALS_C,
193 FII_PSI_GT_PRIOR_ACTUALS_C,
194 FII_PSI_BUDGET_A,
195 FII_PSI_ENCUMBRANCES_A,
196 FII_PSI_ACTUALS_A,
197 FII_PSI_GT_BUDGET_A,
198 FII_PSI_GT_ENCUMBRANCES_A,
199 FII_PSI_GT_ACTUALS_A,
200 FII_PSI_COMP_DRILL,
201 FII_PSI_CC_DRILL,
202 FII_PSI_CAT_DRILL,
203 FII_PSI_PROJECT_DRILL,
204 FII_PSI_UD2_DRILL)';
205
206 /*Outer query used in all cases*/
207 l_outer_sql_b :=
208 'SELECT DECODE(:G_ID, f.VIEWBYID, DECODE(''' || l_if_leaf_flag || ''', ''Y'',
209 f.VIEWBY, f.VIEWBY||'' ''||:DIR_MSG),
210 f.VIEWBY) VIEWBY,
211 f.VIEWBYID VIEWBYID, ' || l_sort_order || '
212 DECODE(:G_ID, f.VIEWBYID, DECODE(''' || l_if_leaf_flag || ''', ''Y'',
213 FII_PSI_BUDGET_C - FII_PSI_ENCUMBRANCES_C - FII_PSI_ACTUALS_C, NULL),
214 FII_PSI_BUDGET_C - FII_PSI_ENCUMBRANCES_C - FII_PSI_ACTUALS_C) FII_PSI_AVAIL_C,
215 FII_PSI_PRIOR_BUDGET_C - FII_PSI_PRIOR_ENCUMBRANCES_C - FII_PSI_PRIOR_ACTUALS_C FII_PSI_PRIOR_AVAIL_C,
216 SUM(FII_PSI_BUDGET_C - FII_PSI_ENCUMBRANCES_C - FII_PSI_ACTUALS_C) OVER () FII_PSI_GT_AVAIL_C,
217 SUM(FII_PSI_PRIOR_BUDGET_C - FII_PSI_PRIOR_ENCUMBRANCES_C - FII_PSI_PRIOR_ACTUALS_C) OVER () FII_PSI_GT_PRIOR_AVAIL_C,
218 DECODE(:G_ID, f.VIEWBYID, DECODE(''' || l_if_leaf_flag || ''', ''Y'',
219 ((FII_PSI_BUDGET_C - FII_PSI_ENCUMBRANCES_C - FII_PSI_ACTUALS_C) / NULLIF(FII_PSI_BUDGET_C,0)) * 100, NULL),
220 ((FII_PSI_BUDGET_C - FII_PSI_ENCUMBRANCES_C - FII_PSI_ACTUALS_C) / NULLIF(FII_PSI_BUDGET_C,0)) * 100) FII_PSI_PCNT_AVAIL_C,
221 ((FII_PSI_PRIOR_BUDGET_C - FII_PSI_PRIOR_ENCUMBRANCES_C - FII_PSI_PRIOR_ACTUALS_C)
222 / NULLIF(FII_PSI_PRIOR_BUDGET_C,0)) * 100 FII_PSI_PRIOR_PCNT_AVAIL_C,
223 ((SUM(FII_PSI_BUDGET_C - FII_PSI_ENCUMBRANCES_C - FII_PSI_ACTUALS_C) OVER ()) /
224 (NULLIF(SUM(FII_PSI_BUDGET_C) OVER (),0))) * 100 FII_PSI_GT_PCNT_AVAIL_C,
225 ((SUM(FII_PSI_PRIOR_BUDGET_C - FII_PSI_PRIOR_ENCUMBRANCES_C - FII_PSI_PRIOR_ACTUALS_C) OVER ()) /
226 (NULLIF(SUM(FII_PSI_PRIOR_BUDGET_C) OVER (),0))) * 100 FII_PSI_GT_PRIOR_PCNT_AVAIL_C,
227 FII_PSI_BUDGET_C,
228 FII_PSI_PRIOR_BUDGET_C,
229 SUM(FII_PSI_BUDGET_C) OVER () FII_PSI_GT_BUDGET_C,
230 SUM(FII_PSI_PRIOR_BUDGET_C) OVER () FII_PSI_GT_PRIOR_BUDGET_C,
231 DECODE(:G_ID, f.VIEWBYID, DECODE(''' || l_if_leaf_flag || ''', ''Y'',
232 FII_PSI_ENCUMBRANCES_C, NULL),
233 FII_PSI_ENCUMBRANCES_C) FII_PSI_ENCUMBRANCES_C,
234 FII_PSI_PRIOR_ENCUMBRANCES_C,
235 SUM(FII_PSI_ENCUMBRANCES_C) OVER () FII_PSI_GT_ENCUMBRANCES_C,
236 SUM(FII_PSI_PRIOR_ENCUMBRANCES_C) OVER () FII_PSI_GT_PRIOR_ENCUM_C,
237 FII_PSI_COMMITTED_C_KPI,
238 FII_PSI_PRIOR_COMMITTED_C,
239 SUM(FII_PSI_COMMITTED_C_KPI) OVER () FII_PSI_GT_COMMITTED_C_KPI,
240 SUM(FII_PSI_PRIOR_COMMITTED_C) OVER () FII_PSI_GT_PRIOR_COMMITTED_C,
241 FII_PSI_OBLIGATED_C_KPI,
242 FII_PSI_PRIOR_OBLIGATED_C,
243 SUM(FII_PSI_OBLIGATED_C_KPI) OVER () FII_PSI_GT_OBLIGATED_C_KPI,
244 SUM(FII_PSI_PRIOR_OBLIGATED_C) OVER () FII_PSI_GT_PRIOR_OBLIGATED_C,
245 FII_PSI_OTHERS_C_KPI,
246 FII_PSI_PRIOR_OTHERS_C,
247 SUM(FII_PSI_OTHERS_C_KPI) OVER () FII_PSI_GT_OTHERS_C_KPI,
248 SUM(FII_PSI_PRIOR_OTHERS_C) OVER () FII_PSI_GT_PRIOR_OTHERS_C,
249 DECODE(:G_ID, f.VIEWBYID, DECODE(''' || l_if_leaf_flag || ''', ''Y'',
250 FII_PSI_ACTUALS_C, NULL),
251 FII_PSI_ACTUALS_C) FII_PSI_ACTUALS_C,
252 FII_PSI_PRIOR_ACTUALS_C,
253 SUM(FII_PSI_ACTUALS_C) OVER () FII_PSI_GT_ACTUALS_C,
254 SUM(FII_PSI_PRIOR_ACTUALS_C) OVER () FII_PSI_GT_PRIOR_ACTUALS_C,
255 FII_PSI_BUDGET_A,
256 DECODE(:G_ID, f.VIEWBYID, DECODE(''' || l_if_leaf_flag || ''', ''Y'',
257 FII_PSI_ENCUMBRANCES_A, NULL),
258 FII_PSI_ENCUMBRANCES_A) FII_PSI_ENCUMBRANCES_A,
259 DECODE(:G_ID, f.VIEWBYID, DECODE(''' || l_if_leaf_flag || ''', ''Y'',
260 FII_PSI_ACTUALS_A, NULL),
261 FII_PSI_ACTUALS_A) FII_PSI_ACTUALS_A,
262 SUM(FII_PSI_BUDGET_A) OVER () FII_PSI_GT_BUDGET_A,
263 SUM(FII_PSI_ENCUMBRANCES_A) OVER () FII_PSI_GT_ENCUMBRANCES_A,
264 SUM(FII_PSI_ACTUALS_A) OVER () FII_PSI_GT_ACTUALS_A,
265 DECODE((SELECT is_leaf_flag FROM fii_company_hierarchies
266 WHERE parent_company_id = f.VIEWBYID and child_company_id = f.VIEWBYID), ''Y'', '''',
267 ''pFunctionName=FII_PSI_FUNDS_AVAIL_SUM_C&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=FII_COMPANIES+FII_COMPANIES&pParamIds=Y'') FII_PSI_COMP_DRILL,
268 DECODE((SELECT is_leaf_flag FROM fii_cost_ctr_hierarchies
269 WHERE parent_cc_id = f.VIEWBYID and child_cc_id = f.VIEWBYID), ''Y'', '''',
270 ''pFunctionName=FII_PSI_FUNDS_AVAIL_SUM_C&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+HRI_CL_ORGCC&pParamIds=Y'') FII_PSI_CC_DRILL,
271 DECODE((SELECT is_leaf_flag FROM fii_fin_item_leaf_hiers
272 WHERE parent_fin_cat_id = f.VIEWBYID and child_fin_cat_id = f.VIEWBYID), ''Y'', '''',
273 DECODE(:G_ID, f.VIEWBYID, '''',
274 ''pFunctionName=FII_PSI_FUNDS_AVAIL_SUM_C&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=FINANCIAL ITEM+GL_FII_FIN_ITEM&pParamIds=Y'')) FII_PSI_CAT_DRILL,
275 DECODE((SELECT is_leaf_flag FROM fii_udd1_hierarchies
276 WHERE parent_value_id = f.VIEWBYID and child_value_id = f.VIEWBYID), ''Y'', '''',
277 DECODE(:G_ID, f.VIEWBYID, '''',
278 ''pFunctionName=FII_PSI_FUNDS_AVAIL_SUM_C&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=FII_USER_DEFINED+FII_USER_DEFINED_1&pParamIds=Y'')) FII_PSI_PROJECT_DRILL,
279 DECODE((SELECT is_leaf_flag FROM fii_udd2_hierarchies
280 WHERE parent_value_id = f.VIEWBYID and child_value_id = f.VIEWBYID), ''Y'', '''',
281 ''pFunctionName=FII_PSI_FUNDS_AVAIL_SUM_C&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=FII_USER_DEFINED+FII_USER_DEFINED_2&pParamIds=Y'') FII_PSI_UD2_DRILL
282 FROM (';
283
284 l_outer_sql_e :=
285 ') f
286 ORDER BY NVL(f.sort_order, 999999) asc,
287 NVL(FII_PSI_BUDGET_C, -999999) desc,
288 NVL(f.VIEWBY, 999999) asc';
289
290 /*Only append union if the query needs to hit aggregated and nonaggregated nodes.*/
291 IF l_aggrt_gt_is_empty ='Y' or l_non_aggrt_gt_is_empty ='Y' THEN
292 l_union_sql := ' ';
293 ELSE
294 l_union_sql := ' UNION ALL ';
295 END IF;
296
297
298 /*Need to hit the snapshot mvs.*/
299 IF l_snapshot = 'Y' THEN
300 l_inner_sql_sys :=
301 ' gt.viewby VIEWBY,
302 gt.sort_order SORT_ORDER,
303 NVL(SUM(CASE WHEN f.posted_date < :ASOF_DATE THEN BUDGET_CUR_' || l_amount_type || ' ELSE 0 END), 0) FII_PSI_BUDGET_C,
304 NVL(SUM(CASE WHEN f.posted_date < :PREVIOUS_ASOF_DATE THEN BUDGET_' || l_compare_to || ' ELSE 0 END), 0) FII_PSI_PRIOR_BUDGET_C,
305 NVL(SUM(CASE WHEN f.posted_date < :ASOF_DATE THEN COMMITMENTS_CUR_' || l_amount_type || ' ELSE 0 END), 0)
306 + NVL(SUM(CASE WHEN f.posted_date < :ASOF_DATE THEN OBLIGATIONS_CUR_' || l_amount_type || ' ELSE 0 END), 0)
307 + NVL(SUM(CASE WHEN f.posted_date < :ASOF_DATE THEN OTHERS_CUR_' || l_amount_type || ' ELSE 0 END), 0) FII_PSI_ENCUMBRANCES_C,
308 NVL(SUM(CASE WHEN f.posted_date < :PREVIOUS_ASOF_DATE THEN COMMITMENTS_' || l_compare_to || ' ELSE 0 END), 0)
309 + NVL(SUM(CASE WHEN f.posted_date < :PREVIOUS_ASOF_DATE THEN OBLIGATIONS_' || l_compare_to || ' ELSE 0 END), 0)
310 + NVL(SUM(CASE WHEN f.posted_date < :PREVIOUS_ASOF_DATE THEN OTHERS_' || l_compare_to || ' ELSE 0 END), 0) FII_PSI_PRIOR_ENCUMBRANCES_C,
311 NVL(SUM(CASE WHEN f.posted_date < :ASOF_DATE THEN COMMITMENTS_CUR_' || l_amount_type || ' ELSE 0 END), 0) FII_PSI_COMMITTED_C_KPI,
312 NVL(SUM(CASE WHEN f.posted_date < :PREVIOUS_ASOF_DATE THEN COMMITMENTS_' || l_compare_to || ' ELSE 0 END), 0) FII_PSI_PRIOR_COMMITTED_C,
313 NVL(SUM(CASE WHEN f.posted_date < :ASOF_DATE THEN OBLIGATIONS_CUR_' || l_amount_type || ' ELSE 0 END), 0) FII_PSI_OBLIGATED_C_KPI,
314 NVL(SUM(CASE WHEN f.posted_date < :PREVIOUS_ASOF_DATE THEN OBLIGATIONS_' || l_compare_to || ' ELSE 0 END), 0) FII_PSI_PRIOR_OBLIGATED_C,
315 NVL(SUM(CASE WHEN f.posted_date < :ASOF_DATE THEN OTHERS_CUR_' || l_amount_type || ' ELSE 0 END), 0) FII_PSI_OTHERS_C_KPI,
316 NVL(SUM(CASE WHEN f.posted_date < :PREVIOUS_ASOF_DATE THEN OTHERS_' || l_compare_to || ' ELSE 0 END), 0) FII_PSI_PRIOR_OTHERS_C,
317 NVL(SUM(ACTUAL_CUR_' || l_amount_type || '), 0) FII_PSI_ACTUALS_C,
318 NVL(SUM(ACTUAL_' || l_compare_to || '), 0) FII_PSI_PRIOR_ACTUALS_C,
319 NVL(SUM(BUDGET_CUR_' || l_xtd || '), 0) FII_PSI_BUDGET_A,
320 NVL(SUM(COMMITMENTS_CUR_' || l_xtd || '), 0)
321 + NVL(SUM(OBLIGATIONS_CUR_' || l_xtd || '), 0)
322 + NVL(SUM(OTHERS_CUR_' || l_xtd || '), 0) FII_PSI_ENCUMBRANCES_A,
323 NVL(SUM(ACTUAL_CUR_' || l_xtd || '), 0) FII_PSI_ACTUALS_A';
324
325 /*Query fii_gl_snap_sum_f_p_v if aggregated nodes need to be displayed or no nodes.*/
326 IF (l_aggrt_gt_is_empty = 'N') OR
327 (l_aggrt_gt_is_empty = 'Y' AND l_non_aggrt_gt_is_empty = 'Y') THEN
328
329 -- Bug 5883351: Find out fud1 where clause if ud1 is enabled/ALL or view by = ud1
330 IF l_ud1_enabled_flag = 'N' THEN
331 l_fud1_where := '';
332 ELSIF (l_fud1_id = 'All'
333 AND l_view_by <> 'FII_USER_DEFINED+FII_USER_DEFINED_1') THEN
334 l_fud1_where := 'AND f.parent_fud1_id = -999 ';
335 ELSE
336 l_fud1_where := 'AND f.parent_fud1_id = gt.parent_fud1_id
337 AND f.fud1_id = gt.fud1_id ';
338 END IF;
339
340 l_inner_sql_sys_agg :=
341 ' SELECT /*+ index(f fii_gl_snap_sum_f_n1) */
342 ' || l_snap_aggrt_viewby_id || ' VIEWBYID,
343 ' || l_inner_sql_sys || '
344 FROM fii_gl_snap_sum_f_p_v f,
345 ' || l_fud2_from || '
346 fii_pmv_aggrt_gt gt
347 WHERE f.parent_company_id = gt.parent_company_id
348 AND f.company_id = gt.company_id
349 AND f.parent_cost_center_id = gt.parent_cc_id
350 AND f.cost_center_id = gt.cc_id
351 AND f.parent_fin_category_id = gt.parent_fin_category_id
352 AND f.fin_category_id = gt.fin_category_id
353 ' || l_fud1_where || '
354 ' || l_fud2_where || '
355 GROUP BY ' || l_snap_aggrt_viewby_id || ', gt.viewby, gt.sort_order';
356 ELSE
357 l_inner_sql_sys_agg := ' ';
358 END IF;
359
360 /*Only query fii_gl_snap_f_p_v if non-aggregated nodes need to be displayed.*/
361 IF l_non_aggrt_gt_is_empty = 'N' THEN
362
363 -- Bug 5883351: Find out fud1 from and where clause if ud1 is enabled/ALL or view by = ud1
364 IF l_ud1_enabled_flag = 'N' OR (l_fud1_id = 'All'
365 AND l_view_by <> 'FII_USER_DEFINED+FII_USER_DEFINED_1') THEN
366 l_fud1_from := '';
367 l_fud1_where := '';
368 ELSE
369 l_fud1_from := ' fii_udd1_hierarchies fud1_hier, ';
370 l_fud1_where := 'AND f.fud1_id = fud1_hier.child_value_id ' || l_fud1_decode || '
371 AND fud1_hier.parent_value_id = gt.fud1_id ';
372 END IF;
373
374 l_inner_sql_sys_nonagg :=
375 ' SELECT /*+ index(f fii_gl_snap_f_n1) */
376 ' || l_non_aggrt_viewby_id || ' VIEWBYID,
377 ' || l_inner_sql_sys || '
378 FROM fii_gl_snap_f_p_v f,
379 fii_company_hierarchies co_hier,
380 fii_cost_ctr_hierarchies cc_hier,
381 fii_fin_item_leaf_hiers fin_hier,
382 ' || l_fud1_from || '
383 ' || l_fud2_from || '
384 fii_pmv_non_aggrt_gt gt
385 WHERE f.company_id = co_hier.child_company_id
386 AND co_hier.parent_company_id = gt.company_id
387 AND f.cost_center_id = cc_hier.child_cc_id
388 AND cc_hier.parent_cc_id = gt.cost_center_id
389 AND f.fin_category_id = fin_hier.child_fin_cat_id ' || l_cat_decode || '
390 AND fin_hier.parent_fin_cat_id = gt.fin_category_id
391 ' || l_fud1_where || '
392 ' || l_fud2_where || '
393 GROUP BY ' || l_non_aggrt_viewby_id || ', gt.viewby, gt.sort_order';
394 ELSE
395 l_inner_sql_sys_nonagg := ' ';
396 END IF;
397
398 sqlstmt := l_outer_sql_b || l_inner_sql_sys_agg
399 || l_union_sql
400 || l_inner_sql_sys_nonagg
401 || l_outer_sql_e;
402
403 FII_EA_UTIL_PKG.bind_variable(
404 p_sqlstmt => sqlstmt,
405 p_page_parameter_tbl => p_page_parameter_tbl,
406 p_sql_output => funds_avail_sql,
407 p_bind_output_table => funds_avail_output);
408
409 ELSE /*system date is not chosen. need to hit full mvs.*/
410
411 /* currency views on top of fii_gl_trend_sum_mv don't have posted_date column,
412 so, we're hitting fii_gl_trend_sum_mv directly by using the below mentioned work-around */
413
414 IF fii_ea_util_pkg.g_if_trend_sum_mv = 'Y' THEN
415
416 l_prim_or_sec := 'PRIM_';
417 l_trend_sum_mv_commitment := 'COMMITTED_AMOUNT_PRIM';
418 l_trend_sum_mv_obligated := 'OBLIGATED_AMOUNT_PRIM';
419 l_trend_sum_mv_other := 'OTHER_AMOUNT_PRIM';
420 l_aggrt_viewby_id := REPLACE(l_aggrt_viewby_id, 'company_id', 'company_dim_id');
421 l_aggrt_viewby_id := REPLACE(l_aggrt_viewby_id, 'cost_center_id', 'cost_center_dim_id');
422 l_aggrt_viewby_id := REPLACE(l_aggrt_viewby_id, 'fin_category_id', 'fin_category_dim_id');
423 ELSE
424 l_trend_sum_mv_commitment := 'COMMITMENTS_G';
425 l_trend_sum_mv_obligated := 'OBLIGATIONS_G';
426 l_trend_sum_mv_other := 'OTHERS_G';
427 END IF;
428
429 l_inner_sql_nonsys :=
430 ' inner_inline_view.viewby VIEWBY,
431 inner_inline_view.sort_order SORT_ORDER,
432 NVL(SUM(CASE WHEN inner_inline_view.report_date = :BOUNDARY_END
433 AND BITAND(inner_inline_view.record_type_id, :AMOUNT_TYPE_BITAND) = :AMOUNT_TYPE_BITAND
434 AND f.posted_date <= :ASOF_DATE
435 THEN '||l_prim_or_sec||'BUDGET_G ELSE 0 END), 0) FII_PSI_BUDGET_C,
436 NVL(SUM(CASE WHEN inner_inline_view.report_date = :PRIOR_BOUNDARY_END
437 AND BITAND(inner_inline_view.record_type_id, :AMOUNT_TYPE_BITAND) = :AMOUNT_TYPE_BITAND
438 AND f.posted_date <= :PREVIOUS_ASOF_DATE
439 THEN '||l_prim_or_sec||'BUDGET_G ELSE 0 END), 0) FII_PSI_PRIOR_BUDGET_C,
440 NVL(SUM(CASE WHEN inner_inline_view.report_date = :BOUNDARY_END
441 AND BITAND(inner_inline_view.record_type_id, :AMOUNT_TYPE_BITAND) = :AMOUNT_TYPE_BITAND
442 AND f.posted_date <= :ASOF_DATE
443 THEN '||l_trend_sum_mv_commitment||' + '||l_trend_sum_mv_obligated||' + '||l_trend_sum_mv_other||' ELSE 0 END), 0) FII_PSI_ENCUMBRANCES_C,
444 NVL(SUM(CASE WHEN inner_inline_view.report_date = :PRIOR_BOUNDARY_END
445 AND BITAND(inner_inline_view.record_type_id, :AMOUNT_TYPE_BITAND) = :AMOUNT_TYPE_BITAND
446 AND f.posted_date <= :PREVIOUS_ASOF_DATE
447 THEN '||l_trend_sum_mv_commitment||' + '||l_trend_sum_mv_obligated||' + '||l_trend_sum_mv_other||' ELSE 0 END), 0) FII_PSI_PRIOR_ENCUMBRANCES_C,
448 NVL(SUM(CASE WHEN inner_inline_view.report_date = :BOUNDARY_END
449 AND BITAND(inner_inline_view.record_type_id, :AMOUNT_TYPE_BITAND) = :AMOUNT_TYPE_BITAND
450 AND f.posted_date <= :ASOF_DATE
451 THEN '||l_trend_sum_mv_commitment||' ELSE 0 END), 0) FII_PSI_COMMITTED_C_KPI,
452 NVL(SUM(CASE WHEN inner_inline_view.report_date = :PRIOR_BOUNDARY_END
453 AND BITAND(inner_inline_view.record_type_id, :AMOUNT_TYPE_BITAND) = :AMOUNT_TYPE_BITAND
454 AND f.posted_date <= :PREVIOUS_ASOF_DATE
455 THEN '||l_trend_sum_mv_commitment||' ELSE 0 END), 0) FII_PSI_PRIOR_COMMITTED_C,
456 NVL(SUM(CASE WHEN inner_inline_view.report_date = :BOUNDARY_END
457 AND BITAND(inner_inline_view.record_type_id, :AMOUNT_TYPE_BITAND) = :AMOUNT_TYPE_BITAND
458 AND f.posted_date <= :ASOF_DATE
459 THEN '||l_trend_sum_mv_obligated||' ELSE 0 END), 0) FII_PSI_OBLIGATED_C_KPI,
460 NVL(SUM(CASE WHEN inner_inline_view.report_date = :PRIOR_BOUNDARY_END
461 AND BITAND(inner_inline_view.record_type_id, :AMOUNT_TYPE_BITAND) = :AMOUNT_TYPE_BITAND
462 AND f.posted_date <= :PREVIOUS_ASOF_DATE
463 THEN '||l_trend_sum_mv_obligated||' ELSE 0 END), 0) FII_PSI_PRIOR_OBLIGATED_C,
464 NVL(SUM(CASE WHEN inner_inline_view.report_date = :BOUNDARY_END
465 AND BITAND(inner_inline_view.record_type_id, :AMOUNT_TYPE_BITAND) = :AMOUNT_TYPE_BITAND
466 AND f.posted_date <= :ASOF_DATE
467 THEN '||l_trend_sum_mv_other||' ELSE 0 END), 0) FII_PSI_OTHERS_C_KPI,
468 NVL(SUM(CASE WHEN inner_inline_view.report_date = :PRIOR_BOUNDARY_END
469 AND BITAND(inner_inline_view.record_type_id, :AMOUNT_TYPE_BITAND) = :AMOUNT_TYPE_BITAND
470 AND f.posted_date <= :PREVIOUS_ASOF_DATE
471 THEN '||l_trend_sum_mv_other||' ELSE 0 END), 0) FII_PSI_PRIOR_OTHERS_C,
472 NVL(SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
473 AND BITAND(inner_inline_view.record_type_id, :AMOUNT_TYPE_BITAND) = :AMOUNT_TYPE_BITAND
474 THEN '||l_prim_or_sec||'ACTUAL_G ELSE 0 END), 0) FII_PSI_ACTUALS_C,
475 NVL(SUM(CASE WHEN inner_inline_view.report_date = :PREVIOUS_ASOF_DATE
476 AND BITAND(inner_inline_view.record_type_id, :AMOUNT_TYPE_BITAND) = :AMOUNT_TYPE_BITAND
477 THEN '||l_prim_or_sec||'ACTUAL_G ELSE 0 END), 0) FII_PSI_PRIOR_ACTUALS_C,
478 NVL(SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
479 AND BITAND(inner_inline_view.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND
480 THEN '||l_prim_or_sec||'BUDGET_G ELSE 0 END), 0) FII_PSI_BUDGET_A,
481 NVL(SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
482 AND BITAND(inner_inline_view.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND
483 THEN '||l_trend_sum_mv_commitment||' + '||l_trend_sum_mv_obligated||' + '||l_trend_sum_mv_other||' ELSE 0 END), 0) FII_PSI_ENCUMBRANCES_A,
484 NVL(SUM(CASE WHEN inner_inline_view.report_date = :ASOF_DATE
485 AND BITAND(inner_inline_view.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND
486 THEN '||l_prim_or_sec||'ACTUAL_G ELSE 0 END), 0) FII_PSI_ACTUALS_A';
487
488 IF fii_ea_util_pkg.g_if_trend_sum_mv = 'Y' THEN
489
490 l_inner_sql_nonsys_agg :=
491 ' SELECT ' || l_aggrt_viewby_id || ' VIEWBYID,
492 ' || l_inner_sql_nonsys || '
493 FROM fii_gl_trend_sum_mv f,
494 (SELECT /*+ NO_MERGE cardinality(gt 1) */ *
495 FROM fii_time_structures cal,
496 fii_pmv_aggrt_gt gt
497 WHERE report_date in (:BOUNDARY_END,:PRIOR_BOUNDARY_END, :ASOF_DATE,:PREVIOUS_ASOF_DATE)
498 AND (BITAND(cal.record_type_id, :AMOUNT_TYPE_BITAND) = :AMOUNT_TYPE_BITAND
499 OR BITAND(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND)) inner_inline_view
500 WHERE f.time_id = inner_inline_view.time_id
501 AND f.period_type_id = inner_inline_view.period_type_id
502 AND f.parent_company_dim_id = inner_inline_view.parent_company_id
503 AND f.company_dim_id = inner_inline_view.company_id
504 AND f.parent_cost_center_dim_id = inner_inline_view.parent_cc_id
505 AND f.cost_center_dim_id = inner_inline_view.cc_id
506 AND f.parent_fin_category_dim_id = inner_inline_view.parent_fin_category_id
507 AND f.fin_category_dim_id = inner_inline_view.fin_category_id
508 GROUP BY ' || l_aggrt_viewby_id || ', inner_inline_view.viewby, inner_inline_view.sort_order';
509
510 ELSIF (l_aggrt_gt_is_empty = 'N') OR
511 (l_aggrt_gt_is_empty = 'Y' AND l_non_aggrt_gt_is_empty = 'Y')THEN
512 /*Query fii_gl_agrt_sum_p_v if aggregated nodes need to be displayed or no nodes.*/
513 l_inner_sql_nonsys_agg :=
514 ' SELECT /*+ index(f fii_gl_agrt_sum_mv_n1) */
515 ' || l_aggrt_viewby_id || ' VIEWBYID,
516 ' || l_inner_sql_nonsys || '
517 FROM fii_gl_agrt_sum_mv_p_v f,
518 ' || l_fud2_from || '
519 (SELECT /*+ NO_MERGE cardinality(gt 1) */ *
520 FROM fii_time_structures cal,
521 fii_pmv_aggrt_gt gt
522 WHERE report_date in (:BOUNDARY_END,:PRIOR_BOUNDARY_END, :ASOF_DATE,:PREVIOUS_ASOF_DATE)
523 AND (BITAND(cal.record_type_id, :AMOUNT_TYPE_BITAND) = :AMOUNT_TYPE_BITAND
524 OR BITAND(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND)) inner_inline_view
525 WHERE f.time_id = inner_inline_view.time_id
526 AND f.period_type_id = inner_inline_view.period_type_id
527 AND f.parent_company_id = inner_inline_view.parent_company_id
528 AND f.company_id = inner_inline_view.company_id
529 AND f.parent_cost_center_id = inner_inline_view.parent_cc_id
530 AND f.cost_center_id = inner_inline_view.cc_id
531 AND f.parent_fin_category_id = inner_inline_view.parent_fin_category_id
532 AND f.fin_category_id = inner_inline_view.fin_category_id
533 AND f.parent_fud1_id = inner_inline_view.parent_fud1_id
534 AND f.fud1_id = inner_inline_view.fud1_id
535 ' || l_fud2_where || '
536 GROUP BY ' || l_aggrt_viewby_id || ', inner_inline_view.viewby, inner_inline_view.sort_order';
537 ELSE
538 l_inner_sql_nonsys_agg := ' ';
539 END IF;
540
541 /*Only query fii_gl_base_map_mv_p_v if non-aggregated nodes need to be displayed.*/
542 IF l_non_aggrt_gt_is_empty = 'N' THEN
543 l_inner_sql_nonsys_nonagg :=
544 '
545 SELECT /*+ index(f fii_gl_base_map_mv_n1) */ ' || l_non_aggrt_viewby_id || ' VIEWBYID,
546 ' || l_inner_sql_nonsys || '
547 FROM fii_gl_base_map_mv_p_v f,
548 fii_company_hierarchies co_hier,
549 fii_cost_ctr_hierarchies cc_hier,
550 fii_fin_item_leaf_hiers fin_hier,
551 fii_udd1_hierarchies fud1_hier,
552 ' || l_fud2_from || '
553 (SELECT /*+ NO_MERGE cardinality(gt 1) */ *
554 FROM fii_time_structures cal,
555 fii_pmv_non_aggrt_gt gt
556 WHERE report_date in (:BOUNDARY_END, :PRIOR_BOUNDARY_END, :ASOF_DATE, :PREVIOUS_ASOF_DATE)
557 AND (BITAND(cal.record_type_id, :AMOUNT_TYPE_BITAND) = :AMOUNT_TYPE_BITAND
558 OR BITAND(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND)) inner_inline_view
559 WHERE f.time_id = inner_inline_view.time_id
560 AND f.period_type_id = inner_inline_view.period_type_id
561 AND f.company_id = co_hier.child_company_id
562 AND co_hier.parent_company_id = inner_inline_view.company_id
563 AND f.cost_center_id = cc_hier.child_cc_id
564 AND cc_hier.parent_cc_id = inner_inline_view.cost_center_id
565 AND f.fin_category_id = fin_hier.child_fin_cat_id ' || l_cat_decode || '
566 AND fin_hier.parent_fin_cat_id = inner_inline_view.fin_category_id
567 AND f.fud1_id = fud1_hier.child_value_id ' || l_fud1_decode || '
568 AND fud1_hier.parent_value_id = inner_inline_view.fud1_id
569 ' || l_fud2_where || '
570 GROUP BY ' || l_non_aggrt_viewby_id || ', inner_inline_view.viewby, inner_inline_view.sort_order';
571 ELSE
572 l_inner_sql_nonsys_nonagg := ' ';
573 END IF;
574
575
576 /*query does not involve a union so it is below length limit*/
577 IF l_aggrt_gt_is_empty = 'Y' OR l_non_aggrt_gt_is_empty = 'Y' THEN
578
579 sqlstmt := l_outer_sql_b || l_inner_sql_nonsys_agg
580 || l_union_sql
581 || l_inner_sql_nonsys_nonagg
582 || l_outer_sql_e;
583
584 FII_EA_UTIL_PKG.bind_variable(
585 p_sqlstmt => sqlstmt,
586 p_page_parameter_tbl => p_page_parameter_tbl,
587 p_sql_output => funds_avail_sql,
588 p_bind_output_table => funds_avail_output);
589
590 ELSE /*query involves a union so query exceedes length limit and need to use the gt table.*/
591
592 l_schema_name := FII_UTIL.get_schema_name('FII');
593 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema_name||'.FII_PSI_PMV_GT';
594
595 sqlstmt_temp := l_insert_sql_b ||
596 l_outer_sql_b || l_inner_sql_nonsys_agg
597 || l_union_sql
598 || l_inner_sql_nonsys_nonagg
599 || l_outer_sql_e;
600
601 sqlstmt_temp := REPLACE(sqlstmt_temp, ':DIR_MSG', to_char(fii_ea_util_pkg.g_dir_msg));
602 sqlstmt_temp := REPLACE(sqlstmt_temp, ':BOUNDARY_END', '''' || to_char(fii_ea_util_pkg.g_boundary_end, 'DD/MM/YYYY') || '''');
603 sqlstmt_temp := REPLACE(sqlstmt_temp, ':AMOUNT_TYPE_BITAND', to_char(fii_ea_util_pkg.g_amount_type_BITAND));
604 sqlstmt_temp := REPLACE(sqlstmt_temp, ':PRIOR_BOUNDARY_END', '''' || to_char(fii_ea_util_pkg.g_prior_boundary_end, 'DD/MM/YYYY') || '''');
605 sqlstmt_temp := REPLACE(sqlstmt_temp, ':ASOF_DATE', '''' || to_char(fii_ea_util_pkg.g_as_of_date, 'DD/MM/YYYY') || '''');
606 sqlstmt_temp := REPLACE(sqlstmt_temp, ':PREVIOUS_ASOF_DATE', '''' || to_char(fii_ea_util_pkg.g_previous_asof_date, 'DD/MM/YYYY') || '''');
607 sqlstmt_temp := REPLACE(sqlstmt_temp, ':ACTUAL_BITAND', to_char(fii_ea_util_pkg.g_actual_BITAND));
608
609 EXECUTE IMMEDIATE(sqlstmt_temp);
610
611 funds_avail_sql :=
612 'SELECT VIEWBY,
613 VIEWBYID,
614 FII_PSI_AVAIL_C,
615 FII_PSI_PRIOR_AVAIL_C,
616 FII_PSI_GT_AVAIL_C,
617 FII_PSI_GT_PRIOR_AVAIL_C,
618 FII_PSI_PCNT_AVAIL_C,
619 FII_PSI_PRIOR_PCNT_AVAIL_C,
620 FII_PSI_GT_PCNT_AVAIL_C,
621 FII_PSI_GT_PRIOR_PCNT_AVAIL_C,
622 FII_PSI_BUDGET_C,
623 FII_PSI_PRIOR_BUDGET_C,
624 FII_PSI_GT_BUDGET_C,
625 FII_PSI_GT_PRIOR_BUDGET_C,
626 FII_PSI_ENCUMBRANCES_C,
627 FII_PSI_PRIOR_ENCUMBRANCES_C,
628 FII_PSI_GT_ENCUMBRANCES_C,
629 FII_PSI_GT_PRIOR_ENCUM_C,
630 FII_PSI_COMMITTED_C_KPI,
631 FII_PSI_PRIOR_COMMITTED_C,
632 FII_PSI_GT_COMMITTED_C_KPI,
633 FII_PSI_GT_PRIOR_COMMITTED_C,
634 FII_PSI_OBLIGATED_C_KPI,
635 FII_PSI_PRIOR_OBLIGATED_C,
636 FII_PSI_GT_OBLIGATED_C_KPI,
637 FII_PSI_GT_PRIOR_OBLIGATED_C,
638 FII_PSI_OTHERS_C_KPI,
639 FII_PSI_PRIOR_OTHERS_C,
640 FII_PSI_GT_OTHERS_C_KPI,
641 FII_PSI_GT_PRIOR_OTHERS_C,
642 FII_PSI_ACTUALS_C,
643 FII_PSI_PRIOR_ACTUALS_C,
644 FII_PSI_GT_ACTUALS_C,
645 FII_PSI_GT_PRIOR_ACTUALS_C,
646 FII_PSI_BUDGET_A,
647 FII_PSI_ENCUMBRANCES_A,
648 FII_PSI_ACTUALS_A,
649 FII_PSI_GT_BUDGET_A,
650 FII_PSI_GT_ENCUMBRANCES_A,
651 FII_PSI_GT_ACTUALS_A,
652 FII_PSI_COMP_DRILL,
653 FII_PSI_CC_DRILL,
654 FII_PSI_CAT_DRILL,
655 FII_PSI_PROJECT_DRILL,
656 FII_PSI_UD2_DRILL
657 FROM FII_PSI_PMV_GT
658 ORDER BY NVL(SORT_ORDER, 999999) asc,
659 NVL(FII_PSI_BUDGET_C, -999999) desc,
660 NVL(VIEWBY, 999999) asc';
661
662 END IF;
663
664 END IF;
665
666 END GET_FUNDS_AVAIL_SUM;
667
668 END FII_PSI_FUNDS_AVAIL_SUM_PKG;