[Home] [Help]
PACKAGE: APPS.OKI_DBI_UTIL_PVT
Source
1 PACKAGE oki_dbi_util_pvt AS
2 /* $Header: OKIRDBIS.pls 120.0 2005/05/30 03:43:08 appldev noship $ */
3
4 g_oper_unit_bmap CONSTANT INTEGER := 1;
5 g_sales_grp_bmap CONSTANT INTEGER := 2;
6 g_sales_rep_bmap CONSTANT INTEGER := 4;
7 g_sitem_bmap CONSTANT INTEGER := 8;
8 g_prd_ctgy_bmap CONSTANT INTEGER := 16;
9 g_cncl_reason_bmap CONSTANT INTEGER := 32;
10 g_customer_bmap CONSTANT INTEGER := 64;
11 g_trm_reason_bmap CONSTANT INTEGER := 128;
12 g_cust_class_bmap CONSTANT INTEGER := 256;
13
14 g_sales_grp_dim VARCHAR2 (100) := 'ORGANIZATION+JTF_ORG_SALES_GROUP';
15 g_sales_rep_dim VARCHAR2 (100) := 'OKI_RESOURCE+SALESREP';
16 g_oper_unit_dim VARCHAR2 (100) := 'ORGANIZATION+FII_OPERATING_UNITS';
17 g_sitem_dim VARCHAR2 (100) := 'ITEM+ENI_ITEM';
18 g_prod_ctgy_dim VARCHAR2 (100) := 'ITEM+ENI_ITEM_PROD_LEAF_CAT';
19 g_cncl_reason_dim VARCHAR2 (100) := 'OKI_STATUS+CNCL_REASON';
20 g_time_mth_dim VARCHAR2 (100) := 'TIME+FII_TIME_ENT_PERIOD';
21 g_time_qtr_dim VARCHAR2 (100) := 'TIME+FII_TIME_ENT_QTR';
22 g_time_year_dim VARCHAR2 (100) := 'TIME+FII_TIME_ENT_YEAR';
23 g_customer_dim VARCHAR2 (100) := 'CUSTOMER+FII_CUSTOMERS';
24 g_trm_reason_dim VARCHAR2 (100) := 'OKI_STATUS+TERM_REASON';
25 g_cust_class_dim VARCHAR2 (100) := 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS';
26
27 g_sales_group_context NUMBER (1);
28
29 g_param bis_pmv_page_parameter_tbl;
30 g_trend VARCHAR2 (100);
31 g_view_by VARCHAR2 (100);
32 g_mv_set VARCHAR2 (100);
33
34
35 g_rs_group_id NUMBER;
36 g_resource_id NUMBER;
37 g_itemid NUMBER;
38 g_invorgid NUMBER;
39
40 TYPE oki_dbi_mv_bmap_rec IS RECORD (
41 mv_name VARCHAR2 (2000)
42 , mv_bmap NUMBER
43 );
44
45 TYPE oki_dbi_mv_bmap_tbl IS TABLE OF oki_dbi_mv_bmap_rec;
46
47 FUNCTION current_period_start_date (
48 as_of_date IN DATE
49 , period_type IN VARCHAR2)
50 RETURN DATE;
51
52 FUNCTION current_period_end_date (
53 as_of_date IN DATE
54 , period_type IN VARCHAR2)
55 RETURN DATE;
56
57 FUNCTION previous_period_start_date (
58 as_of_date IN DATE
59 , period_type IN VARCHAR2
60 , comparison_type IN VARCHAR2)
61 RETURN DATE;
62
63 FUNCTION current_report_start_date (
64 as_of_date IN DATE
65 , period_type IN VARCHAR2)
66 RETURN DATE;
67
68 FUNCTION previous_report_start_date (
69 as_of_date IN DATE
70 , period_type IN VARCHAR2
71 , comparison_type IN VARCHAR2)
72 RETURN DATE;
73
74 FUNCTION previous_period_asof_date (
75 as_of_date IN DATE
76 , period_type IN VARCHAR2
77 , comparison_type IN VARCHAR2)
78 RETURN DATE;
79
80 FUNCTION get_dbi_params (
81 region_id IN VARCHAR2)
82 RETURN VARCHAR2;
83
84 FUNCTION get_sec_profile
85 RETURN NUMBER;
86
87 FUNCTION get_org_where (
88 p_name IN VARCHAR2
89 , p_org IN VARCHAR2)
90 RETURN VARCHAR2;
91
92 FUNCTION get_global_currency
93 RETURN VARCHAR2;
94
95 FUNCTION get_display_currency (
96 p_currency_code IN VARCHAR2
97 , p_selected_operating_unit IN VARCHAR2)
98 RETURN VARCHAR2;
99
100 PROCEDURE get_parameter_values (
101 p_param IN bis_pmv_page_parameter_tbl
102 , p_view_by OUT NOCOPY VARCHAR2
103 , p_period_type OUT NOCOPY VARCHAR2
104 , p_org OUT NOCOPY VARCHAR2
105 , p_comparison_type OUT NOCOPY VARCHAR2
106 , p_xtd OUT NOCOPY VARCHAR2
107 , p_as_of_date OUT NOCOPY DATE
108 , p_cur_suffix OUT NOCOPY VARCHAR2
109 , p_pattern OUT NOCOPY NUMBER
110 , p_period_type_id OUT NOCOPY NUMBER
111 , p_period_type_code OUT NOCOPY VARCHAR2);
112
113 PROCEDURE get_drill_across_param_val (
114 p_param IN bis_pmv_page_parameter_tbl
115 , p_attribute_code_num1 OUT NOCOPY NUMBER
116 , p_attribute_code_num2 OUT NOCOPY NUMBER
117 , p_attribute_code_num3 OUT NOCOPY NUMBER
118 , p_attribute_code_num4 OUT NOCOPY NUMBER
119 , p_attribute_code_num5 OUT NOCOPY NUMBER
120 , p_attribute_code_char1 OUT NOCOPY VARCHAR2
121 , p_attribute_code_char2 OUT NOCOPY VARCHAR2
122 , p_attribute_code_char3 OUT NOCOPY VARCHAR2
123 , p_attribute_code_char4 OUT NOCOPY VARCHAR2
124 , p_attribute_code_char5 OUT NOCOPY VARCHAR2);
125
126 PROCEDURE process_parameters (
127 p_param IN bis_pmv_page_parameter_tbl
128 , p_view_by OUT NOCOPY VARCHAR2
129 , p_view_by_col_name OUT NOCOPY VARCHAR2
130 , p_comparison_type OUT NOCOPY VARCHAR2
131 , p_xtd OUT NOCOPY VARCHAR2
132 , p_as_of_date OUT NOCOPY DATE
133 , p_prev_as_of_date OUT NOCOPY DATE
134 , p_cur_suffix OUT NOCOPY VARCHAR2
135 , p_nested_pattern OUT NOCOPY NUMBER
136 , p_where_clause OUT NOCOPY VARCHAR2
137 , p_mv OUT NOCOPY VARCHAR2
138 , p_join_tbl OUT NOCOPY poa_dbi_util_pkg.poa_dbi_join_tbl
139 , p_period_type OUT NOCOPY VARCHAR2
140 , p_trend IN VARCHAR2
141 , p_func_area IN VARCHAR2
142 , p_version IN VARCHAR2
143 , p_role IN VARCHAR2
144 , p_mv_set IN VARCHAR2
145 -- , p_rpt_type IN VARCHAR2
146 , p_rg_where IN VARCHAR2);
147
148 PROCEDURE init_dim_map (
149 p_dim_map OUT NOCOPY poa_dbi_util_pkg.poa_dbi_dim_map
150 , p_func_area IN VARCHAR2
151 , p_version IN VARCHAR2
152 , p_mv_set IN VARCHAR2);
153
154 FUNCTION get_mv (
155 p_dim_bmap IN NUMBER
156 , p_func_area IN VARCHAR2
157 , p_version IN VARCHAR2
158 , p_mv_set IN VARCHAR2)
159 RETURN VARCHAR2;
160
161 FUNCTION get_col_name (
162 dim_name VARCHAR2
163 , p_func_area IN VARCHAR2
164 , p_version IN VARCHAR2)
165 RETURN VARCHAR2;
166
167 FUNCTION get_prodcat_where
168 RETURN VARCHAR2;
169
170 FUNCTION get_rg_sec_where (
171 p_rg_value IN VARCHAR2
172 , p_rg_col IN VARCHAR2
173 , p_view_by IN VARCHAR2)
174 RETURN VARCHAR2;
175
176 FUNCTION get_where_clauses(
177 p_dim_map poa_dbi_util_pkg.poa_dbi_dim_map
178 , p_trend in VARCHAR2
179 , p_view_by in VARCHAR2
180 , p_mv_set in VARCHAR2)
181 RETURN VARCHAR2;
182
183 FUNCTION get_security_where_clauses (
184 p_dim_map poa_dbi_util_pkg.poa_dbi_dim_map
185 , p_func_area IN VARCHAR2
186 , p_version IN VARCHAR2
187 , p_role IN VARCHAR2
188 , p_view_by IN VARCHAR2
189 , p_rg_where IN VARCHAR2
190 , p_param IN bis_pmv_page_parameter_tbl)
191 RETURN VARCHAR2;
192
193 PROCEDURE get_join_info (
194 p_view_by IN VARCHAR2
195 , p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map
196 , x_join_tbl OUT NOCOPY poa_dbi_util_pkg.poa_dbi_join_tbl
197 , p_func_area IN VARCHAR2
198 , p_version IN VARCHAR2);
199
200 FUNCTION get_table (
201 dim_name VARCHAR2
202 , p_func_area IN VARCHAR2
203 , p_version IN VARCHAR2)
204 RETURN VARCHAR2;
205
206 FUNCTION get_viewby_select_clause (
207 p_viewby IN VARCHAR2
208 , p_func_area IN VARCHAR2
209 , p_version IN VARCHAR2)
210 RETURN VARCHAR2;
211
212 FUNCTION get_cur_suffix (
213 p_cur_suffix IN VARCHAR2)
214 RETURN VARCHAR2;
215
216 FUNCTION get_period_type_code (
217 p_xtd IN VARCHAR2)
218 RETURN VARCHAR2;
219
220 PROCEDURE add_join_table (
221 p_join_tbl IN OUT NOCOPY poa_dbi_util_pkg.poa_dbi_join_tbl
222 , p_column_name IN VARCHAR2
223 , p_table_name IN VARCHAR2
224 , p_table_alias IN VARCHAR2
225 , p_fact_column IN VARCHAR2
226 , p_dim_outer_join IN VARCHAR2 := 'N'
227 , p_additional_where_clause IN VARCHAR2);
228
229 PROCEDURE join_rpt_where (
230 p_join_tbl IN OUT NOCOPY poa_dbi_util_pkg.poa_dbi_join_tbl
231 , p_func_area IN VARCHAR2
232 , p_version IN VARCHAR2
233 , p_role IN VARCHAR2
234 , p_mv_set IN VARCHAR2);
235
236 FUNCTION add_measures (
237 measure1 IN VARCHAR2
238 , measure2 IN VARCHAR2)
239 RETURN VARCHAR2;
240
241 FUNCTION subtract_measures (
242 measure1 IN VARCHAR2
243 , measure2 IN VARCHAR2)
244 RETURN VARCHAR2;
245
246 PROCEDURE get_custom_trend_binds (
247 p_xtd IN VARCHAR2
248 , p_comparison_type IN VARCHAR2
249 , x_custom_output OUT NOCOPY bis_query_attributes_tbl);
250
251 PROCEDURE get_custom_status_binds (
252 x_custom_output OUT NOCOPY bis_query_attributes_tbl);
253
254 PROCEDURE get_bis_bucket_binds (
255 x_custom_output IN OUT NOCOPY bis_query_attributes_tbl,
256 x_bis_bucket IN bis_bucket_pub.BIS_BUCKET_REC_TYPE);
257
258 FUNCTION get_default_portlet_param (
259 p_region_code IN VARCHAR2)
260 RETURN VARCHAR2;
261 FUNCTION get_view_by (
262 p_param in BIS_PMV_PAGE_PARAMETER_TBL)
263 RETURN VARCHAR2;
264
265 FUNCTION get_param_id (
266 p_param IN bis_pmv_page_parameter_tbl,
267 p_param_name IN VARCHAR2 )
268 RETURN VARCHAR2;
269
270 FUNCTION two_way_join ( sel_clause VARCHAR2,
271 query1 VARCHAR2,
272 query2 varchar2,
273 join_column1 varchar2,
274 join_column2 varchar2)
275 return varchar2;
276
277 FUNCTION get_sg_id RETURN VARCHAR2;
278
279 FUNCTION change_clause(cur_col IN VARCHAR2, prior_col IN VARCHAR2, change_type IN VARCHAR2 := 'NP', prod in VARCHAR2 := 'OKI') RETURN VARCHAR2;
280
281
282 FUNCTION get_nested_cols (
283 p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl
284 ,period_type IN VARCHAR2
285 ,P_TREND in varchar2 ) RETURN VARCHAR2;
286
287 FUNCTION get_itd_where (
288 p_mv_name IN VARCHAR2
289 , p_trend IN VARCHAR2 )
290 RETURN VARCHAR2;
291
292 FUNCTION get_xtd_where (
293 p_mv_name IN VARCHAR2
294 , p_trend IN VARCHAR2
295 , p_type IN VARCHAR2
296 ,p_pattern in VARCHAR2 := NULL) RETURN VARCHAR2 ;
297
298 FUNCTION get_dtl_param_where( p_param IN bis_pmv_page_parameter_tbl)
299 RETURN VARCHAR2;
300
301 END oki_dbi_util_pvt ;