1 package BIS_PMV_QUERY_PVT AUTHID CURRENT_USER AS
2 /* $Header: BISVQUES.pls 115.26 2003/01/10 01:38:25 serao noship $ */
3 -- dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=pls \
4 -- dbdrv: checkfile:~PROD:~PATH:~FILE
5 --
6 -- To modify this template, edit file PKGSPEC.TXT in TEMPLATE
7 -- directory of SQL Navigator
8 --
9 -- Purpose: Briefly explain the functionality of the package
10 --
11 -- MODIFICATION HISTORY
12 -- Person Date Comments
13 -- --------- ------ ------------------------------------------
14 -- Enter package declarations as shown below
15
16 QUERY_STR_KEY CHAR(4) := 'QSTR';
17 VIEW_BY_KEY CHAR(6) := 'VIEWBY';
18 ORDER_BY_KEY CHAR(7) := 'ORDERBY';
19
20 procedure getQuerySQL(p_region_code in VARCHAR2,
21 p_function_name in VARCHAR2,
22 p_user_id in VARCHAR2,
23 p_session_id in VARCHAR2,
24 p_resp_id in VARCHAR2,
25 p_page_id in VARCHAR2 DEFAULT NULL,
26 p_schedule_id in VARCHAR2 DEFAULT NULL,
27 p_sort_attribute in VARCHAR2 DEFAULT NULL,
28 p_sort_direction in VARCHAR2 DEFAULT NULL,
29 p_source in VARCHAR2 DEFAULT 'REPORT',
30 p_lower_bound IN INTEGER DEFAULT 1,
31 p_upper_bound IN INTEGER DEFAULT -1,
32 x_sql out NOCOPY VARCHAR2,
33 x_target_alias out NOCOPY VARCHAR2,
34 x_has_target out NOCOPY varchar2,
35 x_viewby_table out NOCOPY VARCHAR2,
36 x_return_status out NOCOPY VARCHAR2,
37 x_msg_count out NOCOPY NUMBER,
38 x_msg_data out NOCOPY VARCHAR2,
39 x_bind_variables in OUT NOCOPY VARCHAR2,
40 x_plsql_bind_variables in OUT NOCOPY VARCHAR2,
41 x_bind_indexes in OUT NOCOPY VARCHAR2,
42 x_bind_datatypes IN OUT NOCOPY VARCHAR2,
43 x_view_by_value OUT NOCOPY VARCHAR2
44 );
45
46 procedure getQuery(p_region_code in VARCHAR2,
47 p_function_name in VARCHAR2,
48 p_user_id in VARCHAR2,
49 p_session_id in VARCHAR2,
50 p_resp_id in VARCHAR2,
51 p_page_id in VARCHAR2 DEFAULT NULL,
52 p_schedule_id in VARCHAR2 DEFAULT NULL,
53 p_sort_attribute in VARCHAR2 DEFAULT NULL,
54 p_sort_direction in VARCHAR2 DEFAULT NULL,
55 p_source in varchar2 DEFAULT 'REPORT',
56 p_customization_code in varchar2 DEFAULT NULL,
57 p_lower_bound IN INTEGER DEFAULT 1,
58 p_upper_bound IN INTEGER DEFAULT -1,
59 x_sql out NOCOPY VARCHAR2,
60 x_target_alias out NOCOPY VARCHAR2,
61 x_has_target out NOCOPY varchar2,
62 x_viewby_table out NOCOPY varchar2,
63 x_return_status out NOCOPY VARCHAR2,
64 x_msg_count out NOCOPY NUMBER,
65 x_msg_data out NOCOPY VARCHAR2,
66 x_bind_variables out NOCOPY VARCHAR2,
67 x_plsql_bind_variables out NOCOPY VARCHAR2,
68 x_bind_indexes out NOCOPY VARCHAR2,
69 x_bind_datatypes OUT NOCOPY VARCHAR2,
70 x_view_by_value OUT NOCOPY VARCHAR2
71 );
72
73 function GET_NORMAL_SELECT(p_ak_region_item_rec in BIS_PMV_METADATA_PVT.AK_REGION_ITEM_REC) return varchar2;
74 function APPLY_DATA_FORMAT(p_ak_region_item_rec in BIS_PMV_METADATA_PVT.AK_REGION_ITEM_REC) return varchar2;
75 function GET_CALCULATE_SELECT(p_ak_region_item_rec in BIS_PMV_METADATA_PVT.AK_REGION_ITEM_REC
76 ,p_parameter_tbl in BIS_PMV_PARAMETERS_PVT.PARAMETER_TBL_TYPE
77 ,p_base_column_tbl in out NOCOPY BISVIEWER.t_char
78 ,p_aggregation_tbl in out NOCOPY BISVIEWER.t_char) return varchar2;
79 function REPLACE_FORMULA(p_ak_region_item_rec in BIS_PMV_METADATA_PVT.AK_REGION_ITEM_REC
80 ,p_parameter_tbl in BIS_PMV_PARAMETERS_PVT.PARAMETER_TBL_TYPE
81 ,p_base_column_tbl in out NOCOPY BISVIEWER.t_char
82 ,p_aggregation_tbl in out NOCOPY BISVIEWER.t_Char) return varchar2;
83 procedure GET_TARGET_SELECT(p_user_session_rec in BIS_PMV_SESSION_PVT.SESSION_REC_TYPE,
84 p_ak_region_item_rec in BIS_PMV_METADATA_PVT.AK_REGION_ITEM_REC,
85 p_parameter_tbl in BIS_PMV_PARAMETERS_PVT.PARAMETER_TBL_TYPE,
86 p_report_type in VARCHAR2,
87 p_plan_id in VARCHAR2,
88 p_viewby_dimension in VARCHAR2,
89 p_viewby_attribute2 in VARCHAR2,
90 p_viewby_id_name in VARCHAR2,
91 p_time_from_description in VARCHAR2,
92 p_time_to_description in VARCHAR2,
93 x_target_select out NOCOPY VARCHAR2,
94 x_no_target out NOCOPY boolean,
95 x_bind_variables in OUT NOCOPY VARCHAR2,
96 --x_bind_indexes in OUT NOCOPY VARCHAR2,
97 x_bind_count in out NOCOPY number
98 );
99 function GET_DIMENSION_WHERE(p_parameter_rec in BIS_PMV_PARAMETERS_PVT.PARAMETER_REC_TYPE,
100 p_save_region_item_rec in BIS_PMV_METADATA_PVT.SAVE_REGION_ITEM_REC,
101 p_ak_region_rec in BIS_PMV_METADATA_PVT.AK_REGION_REC,
102 p_org_dimension_level in VARCHAR2,
103 p_org_dimension_level_value in VARCHAR2,
104 p_viewby_dimension in VARCHAR2,
105 p_time_id_name in VARCHAR2,
106 p_time_value_name in VARCHAR2,
107 p_region_code in VARCHAR2,
108 p_TM_alias in VARCHAR2,
109 x_bind_variables in OUT NOCOPY VARCHAR2,
110 --x_bind_indexes in OUT NOCOPY VARCHAR2,
111 x_bind_count in out NOCOPY number) return varchar2;
112 function GET_TIME_WHERE(p_parameter_rec in BIS_PMV_PARAMETERS_PVT.PARAMETER_REC_TYPE,
113 p_save_region_item_rec in BIS_PMV_METADATA_PVT.SAVE_REGION_ITEM_REC,
114 p_ak_region_rec in BIS_PMV_METADATA_PVT.AK_REGION_REC,
115 p_org_dimension_level in VARCHAR2,
116 p_org_dimension_level_value in VARCHAR2,
117 p_viewby_dimension in VARCHAR2,
118 p_time_id_name in VARCHAR2,
119 p_time_value_name in VARCHAR2,
120 p_region_code in VARCHAR2,
121 p_TM_alias in VARCHAR2,
122 x_bind_variables in OUT NOCOPY VARCHAR2,
123 --x_bind_indexes in OUT NOCOPY VARCHAR2,
124 x_bind_count in out NOCOPY number) return varchar2;
125 function GET_TIME_LABEL_WHERE(p_parameter_description in VARCHAR2,
126 p_time_value_name in VARCHAR2,
127 p_TM_alias in VARCHAR2,
128 x_bind_variables in OUT NOCOPY VARCHAR2,
129 --x_bind_indexes in OUT NOCOPY VARCHAR2,
130 x_bind_count in out NOCOPY number) return varchar2;
131 function GET_NON_TIME_WHERE(p_parameter_rec in BIS_PMV_PARAMETERS_PVT.PARAMETER_REC_TYPE,
132 p_save_region_item_rec in BIS_PMV_METADATA_PVT.SAVE_REGION_ITEM_REC,
133 p_source in varchar2 default null,
134 x_bind_variables in OUT NOCOPY VARCHAR2,
135 --x_bind_indexes in OUT NOCOPY VARCHAR2,
136 x_bind_count in out NOCOPY number) return varchar2;
137 function GET_NON_DIMENSION_WHERE(p_parameter_rec in BIS_PMV_PARAMETERS_PVT.PARAMETER_REC_TYPE,
138 p_save_region_item_rec in BIS_PMV_METADATA_PVT.SAVE_REGION_ITEM_REC,
139 x_bind_variables in OUT NOCOPY VARCHAR2,
140 --x_bind_indexes in OUT NOCOPY VARCHAR2,
141 x_bind_count in out NOCOPY number) return varchar2;
142 function GET_LOV_WHERE(p_parameter_tbl in BIS_PMV_PARAMETERS_PVT.PARAMETER_TBL_TYPE,
143 p_where_clause in VARCHAR2,
144 p_region_code in VARCHAR2 ) return varchar2;
145 function GET_GROUP_BY(p_disable_viewby in VARCHAR2,
146 p_viewby_id_name in VARCHAR2,
147 p_viewby_value_name in VARCHAR2,
148 p_viewby_dimension in VARCHAR2,
149 p_viewby_dimension_level in VARCHAR2,
150 p_extra_groupby in VARCHAR2,
151 p_user_groupby in VARCHAR2,
152 p_user_orderby in VARCHAR2,
153 p_no_target in BOOLEAN DEFAULT TRUE) return varchar2;
154 function GET_ORDER_BY(p_disable_viewby in VARCHAR2,
155 p_sort_attribute in VARCHAR2,
156 p_sort_direction in VARCHAR2,
157 p_viewby_dimension in VARCHAR2,
158 p_viewby_dimension_level in VARCHAR2,
159 p_default_sort_attribute in VARCHAR2,
160 p_user_orderby in VARCHAR2) return varchar2;
161 function GET_USER_STRING(p_user_string in VARCHAR2) return varchar2;
162 procedure sort
163 (pSortNameTbl in out NOCOPY BISVIEWER.t_char
164 ,pSortValueTbl in out NOCOPY BISVIEWER.t_Char
165 );
166 procedure get_customized_order_by(p_viewby in varchar2,
167 p_attribute_code in varchar2,
168 p_region_code in varchar2,
169 p_user_id in varchar2,
170 p_customization_code in varchar2,
171 p_main_order_by in out NOCOPY varchar2,
172 p_first_order_by in out NOCOPY varchar2,
173 p_second_order_by in out NOCOPY varchar2);
174 procedure get_custom_sql ( p_source in varchar2 DEFAULT 'REPORT',
175 pAKRegionRec in BIS_PMV_METADATA_PVT.AK_REGION_REC,
176 pParameterTbl in BIS_PMV_PARAMETERS_PVT.PARAMETER_TBL_TYPE,
177 pUserSession in BIS_PMV_SESSION_PVT.SESSION_REC_TYPE,
178 p_sort_attribute in VARCHAR2 DEFAULT NULL,
179 p_sort_direction in VARCHAR2 DEFAULT NULL,
180 p_viewby_attribute2 IN VARCHAR2,
181 p_viewby_dimension IN VARCHAR2,
182 p_viewby_dimension_level IN VARCHAR2,
183 p_lower_bound IN INTEGER DEFAULT 1,
184 p_upper_bound IN INTEGER DEFAULT -1,
185 x_sql_string out NOCOPY VARCHAR2,
186 x_bind_variables out NOCOPY VARCHAR2,
187 x_plsql_bind_variables out NOCOPY VARCHAR2,
188 x_bind_indexes out NOCOPY VARCHAR2,
189 x_bind_datatypes OUT NOCOPY VARCHAR2,
190 x_return_Status out NOCOPY VARCHAR2,
191 x_msg_data OUT NOCOPY varchar2,
192 x_msg_count OUT NOCOPY NUMBER,
193 x_view_by_value OUT NOCOPY VARCHAR2);
194
195 FUNCTION getParameterAcronym (
196 p_lookup_type IN VARCHAR2,
197 p_Parameter_name IN VARCHAR2
198 ) RETURN VARCHAR2 ;
199
200 PROCEDURE substitute_lov_where(
201 pUserSession_rec IN BIS_PMV_SESSION_PVT.SESSION_REC_TYPE,
202 pSchedule_id IN VARCHAR2,
203 pSource In VARCHAR2 DEFAULT'REPORT',
204 x_lov_where IN OUT NOCOPY VARCHAR2,
205 x_return_status out NOCOPY VARCHAR2,
206 x_msg_count out NOCOPY NUMBER,
207 x_msg_data out NOCOPY VARCHAR2
208 ) ;
209
210 procedure replace_with_bind_variables
211 (p_search_string in varchar2,
212 p_bind_value in varchar2,
213 p_bind_datatype IN number DEFAULT 2,
214 p_initial_index in number,
215 p_bind_function in varchar2 default null,
216 p_bind_to_date in varchar2 default 'N',
217 p_original_sql in varchar2,
218 x_custom_sql in out NOCOPY varchar2,
219 x_bind_variables in out NOCOPY varchar2,
220 x_plsql_bind_variables in out NOCOPY varchar2,
221 x_bind_indexes in out NOCOPY varchar2,
222 x_bind_datatypes IN OUT NOCOPY VARCHAR2,
223 x_bind_count in out NOCOPY number);
224 procedure replace_product_binds
225 (pUserSession in BIS_PMV_SESSION_PVT.SESSION_REC_TYPE,
226 p_original_sql IN VARCHAR2,
227 p_custom_output IN BIS_QUERY_ATTRIBUTES_TBL,
228 x_custom_sql IN OUT NOCOPY VARCHAR2,
229 x_bind_variables IN OUT NOCOPY VARCHAR2,
230 x_plsql_bind_variables IN OUT NOCOPY VARCHAR2,
231 x_bind_indexes IN OUT NOCOPY VARCHAR2,
232 x_bind_Datatypes IN OUT NOCOPY VARCHAR2,
233 x_bind_count IN OUT NOCOPY NUMBER,
234 x_view_by_value OUT NOCOPY VARCHAR2
235 );
236
237 end BIS_PMV_QUERY_PVT;
[Home] [Help]