1 PACKAGE fii_ea_util_pkg AUTHID CURRENT_USER AS
2 /* $Header: FIIEAUTILS.pls 120.7 2006/07/19 06:25:46 sajgeo noship $ */
3
4 g_as_of_date DATE;
5 /* 4439400 Budget */
6 g_bud_as_of_date DATE;
7 g_previous_bud_asof_date DATE;
8 g_page_period_type VARCHAR2(100);
9 g_currency VARCHAR2(50);
10 g_view_by VARCHAR2(100);
11 g_time_comp VARCHAR2(30);
12 g_previous_asof_date DATE;
13 g_company_id VARCHAR2(30) := 'All';
14 g_parent_company_id NUMBER;
15 g_top_company_id NUMBER;
16 g_cost_center_id VARCHAR2(30) := 'All';
17 g_parent_cost_center_id NUMBER;
18 g_top_cost_center_id NUMBER;
19 g_fin_category_id VARCHAR2(30) := 'All';
20 g_parent_fin_category_id NUMBER;
21 g_fin_cat_type VARCHAR2(10) := 'OE';
22 g_ledger_id VARCHAR2(30) := 'All';
23 g_fud1_id VARCHAR2(30) := 'All';
24 g_parent_fud1_id NUMBER;
25 g_top_fud1_id NUMBER;
26 g_fud2_id VARCHAR2(30) := 'All';
27 g_parent_fud2_id NUMBER;
28 g_top_fud2_id NUMBER;
29 g_curr_view VARCHAR2(4);
30 g_actual_bitand NUMBER;
31 g_hist_actual_bitand NUMBER;
32 g_budget_bitand NUMBER;
33 g_forecast_bitand NUMBER;
34 g_previous_one_end_date DATE;
35 g_previous_two_end_date DATE;
36 g_previous_three_end_date DATE;
37 g_je_source_group VARCHAR2(30);
38 g_unassigned_id NUMBER;
39 g_year_id NUMBER;
40 g_prior_year_id NUMBER;
41 g_coaid NUMBER;
42 g_period_set_name VARCHAR2(15);
43 g_accounted_period_type VARCHAR2(15);
44 g_curr_per_start DATE;
45 g_curr_per_end DATE;
46 g_prior_per_start DATE;
47 g_prior_per_end DATE;
48 g_curr_month_start DATE;
49 g_hist_budget_bitand NUMBER;
50 g_amount_type VARCHAR2(3);
51 g_boundary VARCHAR2(1);
52 g_boundary_end DATE;
53 g_prior_boundary_end DATE;
54 g_amount_type_bitand NUMBER;
55 g_snapshot VARCHAR2(1);
56 g_maj_cat_id VARCHAR2(30) := 'All';
57 g_fin_cat_top_node_count NUMBER;
58 g_category_id NUMBER;
59 g_udd1_id NUMBER;
60 g_company_is_leaf VARCHAR2(1);
61 g_cost_center_is_leaf VARCHAR2(1);
62 g_fin_cat_is_leaf VARCHAR2(1);
63 g_ud1_is_leaf VARCHAR2(1);
64 g_ud2_is_leaf VARCHAR2(1);
65 g_dir_msg VARCHAR2(240);
66 g_min_cat_id VARCHAR2(30) := 'All';
67 g_region_code VARCHAR2(100);
68 g_sd_prior DATE;
69 g_sd_prior_prior DATE;
70 g_session_id NUMBER;
71 g_top_node_is_leaf VARCHAR2(1) := 'N';
72 g_id NUMBER;
73 g_time_id NUMBER;
74 g_aggrt_gt_record_count NUMBER;
75 g_non_aggrt_gt_record_count NUMBER;
76 g_if_trend_sum_mv VARCHAR2(1) := 'N'; -- this variable indicates, if report/portlet queries would be hitting fii_gl_trend_sum_mv or not
77 g_fin_aggregate_flag VARCHAR2(1);
78 g_ud1_aggregate_flag VARCHAR2(1);
79 g_company_count NUMBER;
80 g_cc_count NUMBER;
81 g_display_sequence NUMBER; -- global variable used to maintain consistency in display of N/A and 0 for Period = Year
82 g_curr_per_start_id NUMBER;
83 g_as_of_date_id NUMBER;
84
85 -- Added for P&L Analysis
86 g_five_yr_back DATE;
87 g_py_sday DATE;
88 g_exp_asof_date DATE;
89 g_cy_period_end DATE;
90 g_ent_pyr_end DATE;
91 g_period_type NUMBER;
92 g_actual_period_type NUMBER;
93 g_budget_period_type NUMBER;
94 g_where_period_type NUMBER;
95 g_ent_cyr_end DATE;
96 g_curr_per_sequence NUMBER;
97 g_exp_start DATE;
98 g_forecast_period_type NUMBER;
99 g_exp_begin_date DATE;
100 g_fin_type VARCHAR2(200);
101 g_fin_id NUMBER;
102 g_fincat_is_leaf VARCHAR2(1);
103 g_cat_join VARCHAR2(32000);
104 g_cat_join2 VARCHAR2(32000);
105 g_parent_fin_id NUMBER;
106
107 -- This package will provide central utilities for Expense Analysis PMV content
108
109 -- -----------------------------------------------------------------------
110 -- Name: reset_globals
111 -- Desc: This procedure is used to reset all the global variables to null.
112 -- Output: N/A.
113 -- -----------------------------------------------------------------------
114 PROCEDURE reset_globals;
115
116 -- -----------------------------------------------------------------------
117 -- Name: get_parameters
118 -- Desc: This procedure is consumed by all the reports to set the global
119 -- variables.
120 -- Obtains metadata: As of Date, Period Type, Currency, View By,
121 -- Compare To, Previous As of Date, Company id, Cost Center id,
122 -- Financial Category id, Ledger id, Financial UD1 id, Financial UD2
123 -- id, Currency view, Actual bitand mask, Budget/Forecase bitand
124 -- mask.
125 -- Output: N/A.
126 -- -----------------------------------------------------------------------
127 PROCEDURE get_parameters (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL);
128
129 -- -----------------------------------------------------------------------
130 -- Name: get_viewby_id
131 -- Desc: This procedure is used to obtain the viewby columns for aggregated
132 -- and nonaggregated nodes.
133 -- Output: p_aggrt_viewby_id returns the viewby column for aggregated
134 -- nodes, p_snap_aggrt_viewby_id returns the viewby column while hitting
135 -- snapshot table for aggregated nodes and p_nonaggrt_viewby_id returns
136 -- the viewby column for nonaggregated nodes.
137 -- -----------------------------------------------------------------------
138 PROCEDURE get_viewby_id (p_aggrt_viewby_id OUT NOCOPY VARCHAR2,
139 p_snap_aggrt_viewby_id OUT NOCOPY VARCHAR2,
140 p_nonaggrt_viewby_id OUT NOCOPY VARCHAR2);
141
142 -- -----------------------------------------------------------------------
143 -- Name: insert_into_debug_tables
144 -- Desc: This procedure is used to store fii_pmv_%_gt table's records
145 -- in debug tables to facilitate debugging.
146 -- Output: N/A
147 -- -----------------------------------------------------------------------
148
149 PROCEDURE insert_into_debug_tables;
150
151 -- -----------------------------------------------------------------------
152 -- Name: populate_security_gt_tables
153 -- Desc: This procedure is used to populate the global tables fii_pmv_aggrt_gt
154 -- and fii_pmv_non_aggrt_gt. The tables are populated with the
155 -- aggregated and non-aggregated dimension combination(s) that the
156 -- user has access to.
157 -- Output: p_aggrt_gt_is_empty returns 'N' if fii_pmv_aggrt_gt is empty
158 -- else 'Y'. p_non_aggrt_gt_is_empty returns 'N' if fii_pmv_non_aggrt_gt
159 -- is empty else 'Y'.
160 -- -----------------------------------------------------------------------
161 PROCEDURE populate_security_gt_tables (p_aggrt_gt_is_empty OUT NOCOPY VARCHAR2,
162 p_non_aggrt_gt_is_empty OUT NOCOPY VARCHAR2);
163
164 -- -----------------------------------------------------------------------
165 -- Name: get_rolling_period
166 -- Desc: This procedure is used to obtain the rolling period end dates for
167 -- Expense Summary and Expense Trend by Account Detail.
168 -- Sets global variables: g_previous_one_end_date, g_previous_two_end_date,
169 -- g_previous_three_end_date
170 -- Output: N/A.
171 -- -----------------------------------------------------------------------
172 PROCEDURE get_rolling_period;
173
174 -- -----------------------------------------------------------------------
175 -- Name: get_ledger_for_detail
176 -- Desc: This function is used to obtain the filtering condition for the
177 -- Ledger parameter for detail reports.
178 -- Output: Returns ledger where clause conditions to be concatenated with
179 -- the remaining PMV query.
180 -- -----------------------------------------------------------------------
181 FUNCTION get_ledger_for_detail RETURN VARCHAR2;
182
183 -- -----------------------------------------------------------------------
184 -- Name: get_fud1_for_detail
185 -- Desc: This function is used to obtain the filtering condition for the
186 -- Financial UD1 parameter for detail reports.
187 -- Output: Returns financial UD1 where clause conditions to be concatenated
188 -- with the remaining PMV query.
189 -- -----------------------------------------------------------------------
190 FUNCTION get_fud1_for_detail RETURN VARCHAR2;
191
192 -- -----------------------------------------------------------------------
193 -- Name: get_fud2_for_detail
194 -- Desc: This function is used to obtain the filtering condition for the
195 -- Financial UD2 parameter for detail reports.
196 -- Output: Returns financial UD2 where clause conditions to be concatenated
197 -- with the remaining PMV query.
198 -- -----------------------------------------------------------------------
199 FUNCTION get_fud2_for_detail RETURN VARCHAR2;
200
201 FUNCTION get_curr RETURN VARCHAR2;
202
203 -- -----------------------------------------------------------------------
204 -- Name: xtd
205 -- Desc: This function is used to obtain the column heading for the XTD
206 -- amount columns.
207 -- Output: Returns the column heading based on the Period Type parameter.
208 -- Either 'PTD', 'QTD', or 'YTD'.
209 -- -----------------------------------------------------------------------
210 FUNCTION xtd ( p_page_id IN VARCHAR2,
211 p_user_id IN VARCHAR2,
212 p_session_id IN VARCHAR2,
213 p_function_name IN VARCHAR2
214 ) RETURN VARCHAR2;
215
216 -- -----------------------------------------------------------------------
217 -- Name: prior_xtd
218 -- Desc: This function is used to obtain the column heading for the prior
219 -- XTD amount columns.
220 -- Output: Returns the column heading based on the Compare To parameter.
221 -- Either 'Prior XTD' or 'Budget'.
222 -- -----------------------------------------------------------------------
223 FUNCTION prior_xtd( p_page_id IN VARCHAR2,
224 p_user_id IN VARCHAR2,
225 p_session_id IN VARCHAR2,
226 p_function_name IN VARCHAR2) RETURN VARCHAR2;
227
228 -- -----------------------------------------------------------------------
229 -- Name: prior_graph
230 -- Desc: This function is used to obtain the column heading for the prior
231 -- XTD amount graph columns.
232 -- Output: Returns the column heading based on the Compare To parameter.
233 -- Either 'Prior XTD' or 'Budget' or 'Forecast'.
234 -- -----------------------------------------------------------------------
235 FUNCTION prior_graph( p_page_id IN VARCHAR2,
236 p_user_id IN VARCHAR2,
237 p_session_id IN VARCHAR2,
238 p_function_name IN VARCHAR2) RETURN VARCHAR2;
239
240 -- -----------------------------------------------------------------------
241 -- Name: get_rolling_period_label
242 -- Desc: This function is used to obtain the column heading for the rolling
243 -- period columns.
244 -- Output: Returns the column heading based on the Period Type parameter.
245 -- Either period display name/MTD or quarter display name/QTD.
246 -- -----------------------------------------------------------------------
247 FUNCTION get_rolling_period_label (p_sequence IN VARCHAR2) RETURN VARCHAR2;
248
249 -- -----------------------------------------------------------------------
250 -- Name: period_label
251 -- Desc: This function is used to obtain the column heading period name.
252 -- Output: Returns the column heading period name based on the Period Type
253 -- parameter and the input date.
254 -- -----------------------------------------------------------------------
255 FUNCTION period_label (p_as_of_date IN DATE) RETURN VARCHAR2;
256
257 -- -----------------------------------------------------------------------
258 -- Name: curr_period_label
259 -- Desc: This function is used to obtain the column heading period name.
260 -- Output: Returns the column heading period name based on the Period Type
261 -- parameter for the current period.
262 -- -----------------------------------------------------------------------
263 FUNCTION curr_period_label RETURN VARCHAR2;
264
265 -- -----------------------------------------------------------------------
266 -- Name: prior_period_label
267 -- Desc: This function is used to obtain the column heading period name.
268 -- Output: Returns the column heading period name based on the Period Type
269 -- parameter for the prior period.
270 -- -----------------------------------------------------------------------
271 FUNCTION prior_period_label RETURN VARCHAR2;
272
273 -- -----------------------------------------------------------------------
274 -- Name: change_label
275 -- Desc: This function is used to obtain the column heading for 'change'
276 -- column on EA page portlets.
277 -- Output: Returns the column heading for 'change' column on EA page
278 -- portlets based on the comparison Type parameter.
279 -- -----------------------------------------------------------------------
280 FUNCTION change_label RETURN VARCHAR2;
281
282
283 -- -----------------------------------------------------------------------
284 -- Name: get_com_name
285 -- Desc: This function is used to obtain the column heading for 'FII_EA_COL_COMPANY'
286 -- column on Expense Trend by Account Detail report.
287 -- Output: Returns the column heading for 'FII_EA_COL_COMPANY' column on
288 -- Expense Trend by Account Detail report. It could be 'Company'
289 -- or 'Fund' depending on Industry profile 'Commercial' or
290 -- 'Government'.
291 -- -----------------------------------------------------------------------
292
293 FUNCTION get_com_name RETURN VARCHAR2;
294
295 -- -----------------------------------------------------------------------
296 -- Name: check_if_leaf
297 -- Desc: This procedure is used to check whether respective
298 -- parameters chosen are leaf or not.
299 -- Output: N/A.
300 -- -----------------------------------------------------------------------
301 PROCEDURE check_if_leaf(p_id IN NUMBER);
302
303 -- -----------------------------------------------------------------------
304 -- Name: bind_variable
305 -- Desc: This procedure is used to bind all the bind variables, no literal
306 -- shall be used in any report
307 -- Output: N/A.
308 -- -----------------------------------------------------------------------
309
310 PROCEDURE bind_variable (p_sqlstmt IN Varchar2,
311 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
312 p_sql_output OUT NOCOPY Varchar2,
313 p_bind_output_table OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL);
314
315 END fii_ea_util_pkg;
[Home] [Help]