1 PACKAGE POA_DBI_SUTIL_PKG AS
2 /* $Header: poadbisutils.pls 120.7 2006/04/18 02:02:26 sdiwakar noship $ */
3 OPER_UNIT_BMAP CONSTANT INTEGER := 1;
4 REASON_BMAP CONSTANT INTEGER := 2;
5 REC_ORG_BMAP CONSTANT INTEGER := 4;
6 BUYER_BMAP CONSTANT INTEGER := 8;
7 CLERK_BMAP CONSTANT INTEGER := 8;
8 COMMODITY_BMAP CONSTANT INTEGER := 16;
9 CATEGORY_BMAP CONSTANT INTEGER := 32;
10 SUPPLIER_BMAP CONSTANT INTEGER := 64;
11 SUPPLIER_SITE_BMAP CONSTANT INTEGER := 128;
12 ITEM_BMAP CONSTANT INTEGER := 256;
13 DOCTYPE_BMAP CONSTANT INTEGER := 512;
14 REQUESTER_BMAP CONSTANT INTEGER := 1024;
15 COMPANY_BMAP CONSTANT INTEGER := 2048;
16 COSTCTR_BMAP CONSTANT INTEGER := 4096;
17 g_as_of_date date;
18 g_previous_asof_date date;
19 g_page_period_type varchar2(100);
20
21 TYPE poa_dbi_mv_bmap_rec is RECORD(mv_name VARCHAR2(32),
22 mv_bmap NUMBER);
23
24 TYPE poa_dbi_mv_bmap_tbl is TABLE of poa_dbi_mv_bmap_rec;
25
26 TYPE POA_DBI_AGG_LEVEL_REC is RECORD (
27 agg_level NUMBER,
28 agg_bmap NUMBER
29 );
30 TYPE POA_DBI_AGG_LEVEL_TBL is TABLE OF POA_DBI_AGG_LEVEL_REC;
31 TYPE poa_dbi_filter_tbl is TABLE of VARCHAR2(100);
32 TYPE POA_DBI_SHOW_PARAM_REC IS RECORD (SHOW_FLAG VARCHAR2(1));
33 TYPE POA_DBI_SHOW_PARAM_TBL IS TABLE of POA_DBI_SHOW_PARAM_REC;
34
35 FUNCTION get_filter_where(p_cols in POA_DBI_FILTER_TBL) return VARCHAR2;
36
37
38 PROCEDURE process_parameters(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
39 p_view_by out NOCOPY VARCHAR2,
40 p_view_by_col_name out NOCOPY VARCHAR2,
41 p_view_by_value out NOCOPY VARCHAR2,
42 p_comparison_type out NOCOPY VARCHAR2,
43 p_xtd out NOCOPY VARCHAR2,
44 p_as_of_date out NOCOPY DATE,
45 p_prev_as_of_date out NOCOPY DATE,
46 p_cur_suffix out NOCOPY VARCHAR2,
47 p_nested_pattern out NOCOPY NUMBER,
48 p_where_clause out NOCOPY VARCHAR2,
49 p_mv out NOCOPY VARCHAR2,
50 p_join_tbl out NOCOPY poa_dbi_util_pkg.poa_dbi_join_tbl,
51 p_in_join_tbl out NOCOPY poa_dbi_util_pkg.poa_dbi_in_join_tbl,
52 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL,
53 p_trend IN VARCHAR2,
54 p_func_area IN VaRCHAR2,
55 p_version IN VARCHAR2,
56 p_role IN VARCHAR2,
57 p_mv_set IN VARCHAR2);
58
59 PROCEDURE drill_process_parameters(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
60 p_cur_suffix out NOCOPY VARCHAR2,
61 p_where_clause out NOCOPY VARCHAR2,
62 p_in_join_tbl out NOCOPY poa_dbi_util_pkg.poa_dbi_in_join_tbl,
63 p_func_area IN VaRCHAR2,
64 p_version IN VARCHAR2,
65 p_role IN VARCHAR2,
66 p_mv_set IN VARCHAR2);
67
68 PROCEDURE bind_reqfact_date(
69 p_custom_output IN OUT NOCOPY bis_query_attributes_tbl);
70
71 PROCEDURE init_dim_map(p_dim_map out NOCOPY poa_dbi_util_pkg.poa_dbi_dim_map,
72 p_func_area IN VARCHAR2,
73 p_version IN VARCHAR2,
74 p_role IN VARCHAR2,
75 p_mv_set IN VARCHAR2);
76
77
78 FUNCTION get_mv(p_dim_bmap IN NUMBER,
79 p_func_area in VARCHAR2,
80 p_version in VARCHAR2,
81 p_mv_set IN VARCHAR2) return VARCHAR2;
82
83 FUNCTION get_col_name(dim_name VARCHAR2, p_func_area in VARCHAR2, p_version in VARCHAR2, p_mv_set in VARCHAR2) return VARCHAR2;
84
85 FUNCTION get_security_where_clauses(p_dim_map poa_dbi_util_pkg.poa_dbi_dim_map,
86 p_func_area in VARCHAR2,
87 p_version in VARCHAR2,
88 p_role in VARCHAR2,
89 p_trend in VARCHAR2 := 'N') return VARCHAR2;
90
91 FUNCTION get_in_security_where_clauses(p_dim_map poa_dbi_util_pkg.poa_dbi_dim_map,
92 p_context_code in VARCHAR2,
93 p_func_area in VARCHAR2,
94 p_version in VARCHAR2,
95 p_role in VARCHAR2,
96 p_trend in VARCHAR2 := 'N') return VARCHAR2;
97
98 PROCEDURE get_join_info(p_view_by IN varchar2,
99 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map,
100 x_join_tbl OUT NOCOPY poa_dbi_util_pkg.POA_DBI_JOIN_TBL,
101 p_func_area IN varchar2,
102 p_version IN varchar2);
103
104 PROCEDURE populate_in_join_tbl(p_in_join_tbl out NOCOPY poa_dbi_util_pkg.poa_dbi_in_join_tbl,
105 p_param in BIS_PMV_PAGE_PARAMETER_TBL,
106 p_dim_map in POA_DBI_UTIL_PKG.POA_DBI_DIM_MAP,
107 p_context_code in VARCHAR2,
108 p_version in VARCHAR2,
109 p_mv_set in varchar2,
110 p_where_clause in out nocopy varchar2);
111
112 FUNCTION get_table(dim_name VARCHAR2, p_func_area in VARCHAR2, p_version in VARCHAR2)
113 return VARCHAR2;
114
115 FUNCTION get_viewby_select_clause(p_viewby IN VARCHAR2, p_func_area IN VARCHAR2,
116 p_version IN VARCHAR2) RETURN VARCHAR2;
117
118 FUNCTION get_fact_hint(p_mv IN VARCHAR2) RETURN VARCHAR2;
119
120 PROCEDURE populate_agg_level(
121 p_agg_lvl_tbl OUT NOCOPY POA_DBI_AGG_LEVEL_TBL,
122 p_mv_set IN VARCHAR2
123 ) ;
124
125 FUNCTION get_agg_level(p_dim_bmap IN NUMBER,
126 p_mv_set IN VARCHAR2) RETURN NUMBER;
127
128 FUNCTION get_display_category(p_category_code IN VARCHAR2,
129 p_selected_commodity IN VARCHAR2,
130 p_context_code in varchar2,
131 p_restrict_lov in varchar2 := 'Y'
132 ) RETURN VARCHAR2;
133
134 procedure get_company_sql (p_viewby in varchar2,
135 p_company_id in varchar2,
136 p_region_code in varchar2,
137 p_company_sql out nocopy varchar2,
138 p_agg_flag out nocopy varchar2);
139
140 procedure get_cost_ctr_sql (p_viewby in varchar2,
141 p_cost_center_id in varchar2,
142 p_region_code in varchar2,
143 p_cost_ctr_sql out nocopy varchar2,
144 p_agg_flag out nocopy varchar2);
145
146 FUNCTION get_display_supplier(p_supplier_id IN VARCHAR2,
147 p_context_code IN VARCHAR2
148 ) RETURN VARCHAR2;
149 FUNCTION get_display_supplier_site(p_supplier_site_id IN VARCHAR2,
150 p_context_code IN VARCHAR2
151 ) RETURN VARCHAR2;
152 FUNCTION get_display_commodity(p_commodity_id IN VARCHAR2,
153 p_context_code IN VARCHAR2
154 ) RETURN VARCHAR2;
155 FUNCTION get_display_ou(p_ou_id IN VARCHAR2,
156 p_context_code IN VARCHAR2
157 ) RETURN VARCHAR2;
158 FUNCTION get_display_com(p_id IN VARCHAR2,
159 p_parent_id IN VARCHAR2,
160 p_selected_company IN VARCHAR2
161 ) RETURN VARCHAR2;
162 FUNCTION get_display_cc(p_id IN VARCHAR2,
163 p_parent_id IN VARCHAR2,
164 p_selected_cc IN VARCHAR2
165 ) RETURN VARCHAR2;
166
167 PROCEDURE hide_parameter(p_param IN BIS_PMV_PAGE_PARAMETER_TBL, hideParameter OUT NOCOPY VARCHAR2);
168 PROCEDURE hide_parameter2(p_param IN BIS_PMV_PAGE_PARAMETER_TBL, hideParameter OUT NOCOPY VARCHAR2);
169 PROCEDURE hide_parameter3(p_param IN BIS_PMV_PAGE_PARAMETER_TBL, hideParameter OUT NOCOPY VARCHAR2);
170 PROCEDURE hide_commodity(p_param IN BIS_PMV_PAGE_PARAMETER_TBL, hideParameter OUT NOCOPY VARCHAR2);
171
172 function get_sec_context(p_param in bis_pmv_page_parameter_tbl) return varchar2;
173 function get_bis_calling_parameter(p_param in bis_pmv_page_parameter_tbl) return varchar2;
174 function get_supplier_id_ou return varchar2;
175 function get_supplier_id_sup return varchar2;
176 procedure get_parameters (p_page_parameter_tbl in bis_pmv_page_parameter_tbl);
177 function get_pri_label return varchar2;
178 function get_curr_label return varchar2;
179 procedure bind_com_cc_values( x_custom_output in out nocopy bis_query_attributes_tbl,
180 p_param in bis_pmv_page_parameter_tbl);
181 END POA_DBI_SUTIL_PKG;
[Home] [Help]