1 PACKAGE poa_dbi_util_pkg AS
2 /* $Header: poadbiutils.pls 120.2 2005/09/08 15:39:29 nnewadka noship $ */
3
4 NO_PRIORS CONSTANT INTEGER := 1;
5 COL_PRIOR_ONLY CONSTANT INTEGER := 2;
6 BOTH_PRIORS CONSTANT INTEGER := 3;
7 PREV_PREV CONSTANT INTEGER := 4;
8 OPENING_PRIOR_CURR CONSTANT INTEGER := 5;
9
10 ---Begin MOAC changes
11 g_org_id NUMBER ;
12 g_sec_profile_id NUMBER ;
13 ---End MOAC changes
14
15 TYPE poa_dbi_join_rec is RECORD(column_name VARCHAR2(200),
16 /* column to join, e.g. id */
17 table_name VARCHAR2(500),
18 /* table to join e.g. poa_items_v */
19 table_alias VARCHAR(200),
20 /* alias of table, i.e. v */
21 fact_column VARCHAR2(200),
22 /* column selected from fact, e.g. item_id */
23 fact_column2 VARCHAR2(200),
24 /* column selected from fact in second part of union all queries. If not populated,
25 then fact_column is used for both parts of
26 the union. Not used in non-union
27 queries. */
28 inner_alias VARCHAR(200),
29 /* alias of table to select column
30 from in innermost select
31 (if left empty, the inner_alias
32 will be 'fact') */
33 dim_outer_join VARCHAR2(2),
34 /* whether it is an outer join or not */
35 additional_where_clause varchar2(1000));
36 /* any additional conditions on the join (such as a tl condition) */
37
38 TYPE poa_dbi_join_tbl is TABLE of poa_dbi_join_rec;
39
40 TYPE poa_dbi_col_list is TABLE OF VARCHAR2(200);
41
42 TYPE poa_dbi_union_query_rec is RECORD(in_union_sel VARCHAR2(5000),
43 template_sql VARCHAR2(20000));
44
45 TYPE poa_dbi_union_query_tbl is TABLE OF poa_dbi_union_query_rec;
46
47
48 TYPE poa_dbi_col_calc_rec is RECORD(column_name VARCHAR2(300),
49 alias_begin VARCHAR2(200),
50 alias_end VARCHAR2(20),
51 calc_begin VARCHAR2(50),
52 calc_end VARCHAR2(50),
53 date_decode_begin VARCHAR2(200),
54 date_decode_end VARCHAR2(50));
55
56 TYPE poa_dbi_col_calc_tbl is TABLE of poa_dbi_col_calc_rec;
57
58 TYPE poa_dbi_in_join_rec is RECORD(table_name VARCHAR2(400),
59 /* table to join e.g. per_organization_list*/
60 table_alias VARCHAR(200),
61 aggregated_flag varchar2(1));
62 /* added specifically for company, cost_center and user defined dimensions*/
63
64 TYPE poa_dbi_in_join_tbl is TABLE of poa_dbi_in_join_rec;
65
66 TYPE poa_dbi_dim_rec IS RECORD(value VARCHAR2(3) := 'All',
67 col_name VARCHAR2(64),
68 bmap NUMBER,
69 view_by_table VARCHAR2(500),
70 generate_where_clause VARCHAR2(1) := 'Y');
71 /* if generate_where_clause = 'Y' then the
72 function get_where_clauses will add in a where clause
73 for that dimension. Set generate_where_clause to 'N'
74 if you need to create a more complicate where for a
75 dimension, or if you do not want a where at all. */
76
77 /* possible values for to_date_type:
78 XTD = period to date
79 XED = period to end of period (i.e. entire period)
80 YTD = year to date
81 ITD = inception to date
82 RLX = Rolling period
83 BAL = Balance
84 NA = for reports with no as-of date or period type.
85 */
86 TYPE poa_dbi_col_rec is RECORD(column_name VARCHAR2(100),
87 column_alias VARCHAR2(64),
88 to_date_type varchar2(3),
89 grand_total VARCHAR2(1),
90 prior_code VARCHAR2(2));
91
92 TYPE poa_dbi_col_tbl is TABLE of poa_dbi_col_rec;
93
94 TYPE poa_dbi_dim_tbl is TABLE of varchar2(100);
95
96 TYPE poa_dbi_dim_map IS TABLE OF poa_dbi_dim_rec
97 INDEX BY VARCHAR2(75);
98 TYPE poa_dbi_filter_tbl is TABLE of VARCHAR2(100);
99
100 TYPE poa_dbi_flex_filter_rec IS RECORD (measure_name VARCHAR2(100),
101 modifier VARCHAR2(100));
102 TYPE poa_dbi_flex_filter_tbl IS TABLE OF poa_dbi_flex_filter_rec;
103
104 TYPE poa_dbi_mv_bmap_rec is RECORD(mv_name VARCHAR2(32),
105 mv_bmap NUMBER);
106
107 TYPE poa_dbi_mv_bmap_tbl is TABLE of poa_dbi_mv_bmap_rec;
108
109 TYPE poa_dbi_mv_rec is RECORD( mv_name VARCHAR2(300),
110 mv_col poa_dbi_util_pkg.poa_dbi_col_tbl ,
111 mv_where VARCHAR2(4000),
112 in_join_tbls poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL,
113 mv_hint VARCHAR2(1000) /* used only by union all trend sql*/,
114 use_grp_id VARCHAR2(10),
115 mv_xtd VARCHAR2(50) /* used only by union all trend sql */);
116 TYPE poa_dbi_mv_tbl is TABLE of poa_dbi_mv_rec;
117
118
119 FUNCTION get_calendar_table
120 ( period_type in varchar2
121 , p_include_prior in varchar2 := 'Y'
122 , p_include_opening in varchar2 := 'N'
123 , p_called_by_union in varchar2 := 'N'
124 )
125 return varchar2;
126
127 FUNCTION get_nested_pattern(period_type IN varchar2) return number;
128
129 FUNCTION get_nested_period_type_id(period_type IN varchar2) return number;
130
131 FUNCTION get_sec_profile return number;
132
133 FUNCTION get_fnd_user_profile RETURN NUMBER;
134
135 FUNCTION get_fnd_employee_profile RETURN NUMBER;
136
137 FUNCTION bitor(x in number,y in number) return number;
138
139 PROCEDURE refresh (p_mv_name IN VARCHAR2);
140
141 FUNCTION get_filter_where(p_cols in POA_DBI_FILTER_TBL)
142 return VARCHAR2;
143
144 FUNCTION get_filter_where(p_cols in POA_DBI_FLEX_FILTER_TBL)
145 return VARCHAR2;
146
147 PROCEDURE get_parameter_values(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
148 p_dim_map in out NOCOPY poa_dbi_dim_map,
149 p_view_by out NOCOPY VARCHAR2,
150 p_comparison_type out NOCOPY VARCHAR2,
151 p_xtd out NOCOPY VARCHAR2,
152 p_as_of_date out NOCOPY DATE,
153 p_prev_as_of_date out NOCOPY DATE,
154 p_cur_suffix out NOCOPY VARCHAR2,
155 p_nested_pattern out NOCOPY NUMBER,
156 p_dim_bmap in out NOCOPY NUMBER);
157
158 PROCEDURE get_drill_param_values(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
159 p_dim_map in out nocopy poa_dbi_dim_map,
160 p_cur_suffix out NOCOPY VARCHAR2);
161
162 FUNCTION get_where_clauses(p_dim_map poa_dbi_dim_map, p_trend in VARCHAR2) RETURN VARCHAR2;
163
164 PROCEDURE add_column(p_col_tbl IN OUT NOCOPY poa_dbi_col_tbl,
165 p_col_name IN VARCHAR2,
166 p_alias_name IN VARCHAR2,
167 p_grand_total IN VARCHAR2 := 'Y',
168 p_prior_code IN NUMBER := BOTH_PRIORS,
169 p_to_date_type IN VARCHAR2 := 'XTD');
170
171 PROCEDURE add_bucket_columns(p_short_name in varchar2
172 , p_col_tbl in out nocopy poa_dbi_util_pkg.poa_dbi_col_tbl
173 , p_col_name in varchar2
174 , p_alias_name in varchar2
175 , x_bucket_rec out nocopy bis_bucket_pub.bis_bucket_rec_type
176 , p_grand_total in varchar2 := 'Y'
177 , p_prior_code in varchar2 := BOTH_PRIORS
178 , p_to_date_type in varchar2 := 'XTD'
179 );
180
181 function get_bucket_outer_query
182 ( p_bucket_rec in bis_bucket_pub.bis_bucket_rec_type
183 , p_col_name in varchar2
184 , p_alias_name in varchar2
185 , p_prefix in varchar2
186 , p_suffix in varchar2
187 , p_total_flag in varchar2 default 'N'
188 )return varchar2;
189
190 function get_bucket_drill_url
191 ( p_bucket_rec in bis_bucket_pub.bis_bucket_rec_type
192 , p_alias_name in varchar2
193 , p_prefix in varchar2
194 , p_suffix in varchar2
195 , p_add_bucket_num in varchar2
196 )
197 return varchar2;
198
199
200 /* NP -> non-percentage. otherwise returns percent change formula */
201 FUNCTION change_clause(cur_col IN VARCHAR2, prior_col IN VARCHAR2, change_type IN VARCHAR2 := 'NP')
202 RETURN VARCHAR2;
203
204 FUNCTION rate_clause(numerator IN VARCHAR2, denominator IN VARCHAR2, rate_type IN VARCHAR2 := 'P') RETURN VARCHAR2;
205
206 FUNCTION get_commodity_sec_where(p_commodity_value VARCHAR2, p_trend IN VARCHAR2 :='N') return VARCHAR2;
207
208 FUNCTION get_in_commodity_sec_where(p_commodity_value VARCHAR2, p_trend IN VARCHAR2 :='N') return VARCHAR2;
209
210 FUNCTION get_ou_sec_where(p_ou_value VARCHAR2, p_ou_fact_col VARCHAR2, p_trend IN VARCHAR2 :='N') return VARCHAR2;
211
212 FUNCTION get_in_ou_sec_where(p_ou_value VARCHAR2, p_ou_fact_col VARCHAR2, p_use_bind IN VARCHAR2 :='Y') return VARCHAR2;
213
214 PROCEDURE get_custom_trend_binds
215 ( p_xtd in varchar2
216 , p_comparison_type in varchar2
217 , x_custom_output out nocopy bis_query_attributes_tbl
218 , p_opening_balance in varchar2 := 'N'
219 );
220
221 PROCEDURE get_custom_status_binds(x_custom_output IN OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL);
222
223 FUNCTION get_trend_lag(p_xtd IN varchar2, p_comparison_type IN varchar2) return number;
224
225 -- get_report_start_date function returns the BIS bind variable
226 -- substituton text for use by status_sql and trend_sql for rolling periods
227 FUNCTION get_report_start_date
228 ( p_period_type varchar2
229 , p_prior varchar2 := 'N'
230 )
231 return varchar2;
232
233 -- get_custom_balance_binds procedure updates the bis_query_attributes_tbl
234 -- with the bind variables/values needed for balance reports
235 PROCEDURE get_custom_balance_binds
236 ( p_custom_output in out nocopy bis_query_attributes_tbl
237 , p_balance_fact in varchar2
238 , p_xtd in varchar2 := null
239 );
240
241 -- get_custom_rolling_binds procedure updates the bis_query_attributes_tbl
242 -- with the bind variables/values needed for rolling period reports
243 -- will be unnecessary when fii/bis provide this functionality.
244 PROCEDURE get_custom_rolling_binds
245 ( p_custom_output in out nocopy bis_query_attributes_tbl
246 , p_xtd in varchar2
247 );
248
249 procedure bind_low_high
250 ( p_param in bis_pmv_page_parameter_tbl
251 , p_short_name in varchar2
252 , p_dim_level in varchar2
253 , p_low in varchar2
254 , p_high in varchar2
255 , p_custom_output in out nocopy bis_query_attributes_tbl
256 );
257
258 -- Procedure to seed Bind variables for day level reporting
259 PROCEDURE get_custom_day_binds(p_custom_output IN OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL,
260 p_as_of_date IN DATE,
261 p_comparison_type IN VARCHAR2);
262
263 FUNCTION get_in_supplier_sec_where(p_supplier_value IN VARCHAR2) RETURN VARCHAR2;
264 ---Begin MOAC changes
265 FUNCTION get_ou_org_id RETURN NUMBER;
266 ---End MOAC changes
267
268 --Begin changes for spend trend graph
269 FUNCTION get_rolling_inline_view
270 RETURN VARCHAR2 ;
271 --End changes for spend trend graph
272 END poa_dbi_util_pkg;
[Home] [Help]