[Home] [Help]
PACKAGE: APPS.AK_QUERY_PKG
Source
1 PACKAGE AK_QUERY_PKG AUTHID CURRENT_USER AS
2 /* $Header: akquerys.pls 115.17 2002/09/27 18:32:19 tshort ship $ */
3 --
4 -- Declare constants
5 --
6 MAXKEYNUM CONSTANT number := 10; -- Max number of keys
7 MAXDISPNUM CONSTANT number := 100; -- Max number of attributes
8 MAXROWNUM CONSTANT number := 2147483648; -- 2^32 / 2 (SB4)
9 --
10 -- Variable used for debugging purposes
11 --
12 sql_stmt varchar2(20000);
13 PRINT_DEBUG_ON boolean := FALSE;
14
15 TYPE key_rec IS RECORD
16 (
17 name varchar2(30),
18 is_date boolean
19 );
20
21 --
22 -- Declare record and table for passing bind variables and values
23 --
24 TYPE bind_rec IS RECORD
25 (
26 name varchar2(30),
27 value varchar2(4000)
28 );
29 TYPE bind_tab is table of bind_rec
30 index by binary_integer;
31
32 --
33 -- Declare tables for relationship definitions
34 --
35 TYPE rel_id_tab is table of number
36 index by binary_integer;
37 TYPE rel_name_tab is table of varchar2(30)
38 index by binary_integer;
39 TYPE rel_key_tab is table of key_rec
40 index by binary_integer;
41 TYPE rel_key_value_tab is table of varchar2(4000)
42 index by binary_integer;
43
44 TYPE region_rec IS RECORD
45 (
46 region_rec_id number,
47 parent_region_rec_id number,
48 flow_application_id number,
49 flow_code varchar2(30),
50 page_application_id number,
51 page_code varchar2(30),
52 region_application_id number,
53 region_code varchar2(30),
54 database_object_name varchar2(30),
55 primary_key_name varchar2(30),
56 name varchar2(80),
57 region_style varchar2(30),
58 number_of_format_columns number,
59 region_defaulting_api_pkg varchar2(30),
60 region_defaulting_api_proc varchar2(30),
61 region_validation_api_pkg varchar2(30),
62 region_validation_api_proc varchar2(30),
63 object_defaulting_api_pkg varchar2(30),
64 object_defaulting_api_proc varchar2(30),
65 object_validation_api_pkg varchar2(30),
66 object_validation_api_proc varchar2(30),
67 total_result_count number,
68 display_sequence number,
69 sql_select varchar2(5000),
70 sql_from varchar2(240),
71 sql_where varchar2(10000),
72 sql_order_by varchar2(1000)
73 );
74
75 TYPE region_bind_rec IS RECORD
76 (
77 region_rec_id number,
78 name varchar2(30),
79 value varchar2(4000)
80 );
81
82 TYPE item_rec IS RECORD
83 (
84 region_rec_id number,
85 value_id number,
86 attribute_application_id number,
87 attribute_code varchar2(30),
88 attribute_label_long varchar2(80),
89 attribute_label_length number,
90 attribute_value_length number,
91 display_value_length number,
92 display_sequence number,
93 item_style varchar2(30),
94 bold varchar2(1),
95 italic varchar2(1),
96 vertical_alignment varchar2(30),
97 horizontal_alignment varchar2(30),
98 object_attribute_flag varchar2(1),
99 secured_column varchar2(1),
100 indexed_column varchar2(1),
101 rls_column varchar2(1),
102 node_query_flag varchar2(1),
103 node_display_flag varchar2(1),
104 update_flag varchar2(1),
105 required_flag varchar2(1),
106 icx_custom_call varchar2(80),
107 region_defaulting_api_pkg varchar2(30),
108 region_defaulting_api_proc varchar2(30),
109 region_validation_api_pkg varchar2(30),
110 region_validation_api_proc varchar2(30),
111 object_defaulting_api_pkg varchar2(30),
112 object_defaulting_api_proc varchar2(30),
113 object_validation_api_pkg varchar2(30),
114 object_validation_api_proc varchar2(30),
115 lov_foreign_key_name varchar2(30),
116 lov_region_application_id number,
117 lov_region_code varchar2(30),
118 lov_attribute_application_id number,
119 lov_attribute_code varchar2(30),
120 lov_default_flag varchar2(1)
121 );
122
123 TYPE result_rec IS RECORD
124 (region_rec_id number,
125 key1 varchar2(4000),
126 key2 varchar2(4000),
127 key3 varchar2(4000),
128 key4 varchar2(4000),
129 key5 varchar2(4000),
130 key6 varchar2(4000),
131 key7 varchar2(4000),
132 key8 varchar2(4000),
133 key9 varchar2(4000),
134 key10 varchar2(4000),
135 value1 varchar2(4000),
136 value2 varchar2(4000),
137 value3 varchar2(4000),
138 value4 varchar2(4000),
139 value5 varchar2(4000),
140 value6 varchar2(4000),
141 value7 varchar2(4000),
142 value8 varchar2(4000),
143 value9 varchar2(4000),
144 value10 varchar2(4000),
145 value11 varchar2(4000),
146 value12 varchar2(4000),
147 value13 varchar2(4000),
148 value14 varchar2(4000),
149 value15 varchar2(4000),
150 value16 varchar2(4000),
151 value17 varchar2(4000),
152 value18 varchar2(4000),
153 value19 varchar2(4000),
154 value20 varchar2(4000),
155 value21 varchar2(4000),
156 value22 varchar2(4000),
157 value23 varchar2(4000),
158 value24 varchar2(4000),
159 value25 varchar2(4000),
160 value26 varchar2(4000),
161 value27 varchar2(4000),
162 value28 varchar2(4000),
163 value29 varchar2(4000),
164 value30 varchar2(4000),
165 value31 varchar2(4000),
166 value32 varchar2(4000),
167 value33 varchar2(4000),
168 value34 varchar2(4000),
169 value35 varchar2(4000),
170 value36 varchar2(4000),
171 value37 varchar2(4000),
172 value38 varchar2(4000),
173 value39 varchar2(4000),
174 value40 varchar2(4000),
175 value41 varchar2(4000),
176 value42 varchar2(4000),
177 value43 varchar2(4000),
178 value44 varchar2(4000),
179 value45 varchar2(4000),
180 value46 varchar2(4000),
181 value47 varchar2(4000),
182 value48 varchar2(4000),
183 value49 varchar2(4000),
184 value50 varchar2(4000),
185 value51 varchar2(4000),
186 value52 varchar2(4000),
187 value53 varchar2(4000),
188 value54 varchar2(4000),
189 value55 varchar2(4000),
190 value56 varchar2(4000),
191 value57 varchar2(4000),
192 value58 varchar2(4000),
193 value59 varchar2(4000),
194 value60 varchar2(4000),
195 value61 varchar2(4000),
196 value62 varchar2(4000),
197 value63 varchar2(4000),
198 value64 varchar2(4000),
199 value65 varchar2(4000),
200 value66 varchar2(4000),
201 value67 varchar2(4000),
202 value68 varchar2(4000),
203 value69 varchar2(4000),
204 value70 varchar2(4000),
205 value71 varchar2(4000),
206 value72 varchar2(4000),
207 value73 varchar2(4000),
208 value74 varchar2(4000),
209 value75 varchar2(4000),
210 value76 varchar2(4000),
211 value77 varchar2(4000),
212 value78 varchar2(4000),
213 value79 varchar2(4000),
214 value80 varchar2(4000),
215 value81 varchar2(4000),
216 value82 varchar2(4000),
217 value83 varchar2(4000),
218 value84 varchar2(4000),
219 value85 varchar2(4000),
220 value86 varchar2(4000),
221 value87 varchar2(4000),
222 value88 varchar2(4000),
223 value89 varchar2(4000),
224 value90 varchar2(4000),
225 value91 varchar2(4000),
226 value92 varchar2(4000),
227 value93 varchar2(4000),
228 value94 varchar2(4000),
229 value95 varchar2(4000),
230 value96 varchar2(4000),
231 value97 varchar2(4000),
232 value98 varchar2(4000),
233 value99 varchar2(4000),
234 value100 varchar2(4000));
235
236 --
237 -- Declare tables for return regions, items and data results
238 --
239 TYPE regions_table_type is table of region_rec
240 index by binary_integer;
241 TYPE regions_bind_table_type is table of region_bind_rec
242 index by binary_integer;
243 TYPE items_table_type is table of item_rec
244 index by binary_integer;
245 TYPE results_table_type is table of result_rec
246 index by binary_integer;
247
248 --
249 -- Globals to be used to output data
250 --
251 g_regions_table regions_table_type;
252 g_regions_bind_table regions_bind_table_type;
253 g_items_table items_table_type;
254 g_results_table results_table_type;
255
256 --
257 -- Global used as default value for parameter
258 --
259 G_BIND_TAB_NULL bind_tab;
260
261 --
262 -- Declare exec_query
263 --
264 PROCEDURE exec_query
265 (
266 p_flow_appl_id IN number default NULL,
267 p_flow_code IN varchar2 default NULL,
268 p_parent_page_appl_id IN number default NULL,
269 p_parent_page_code IN varchar2 default NULL,
270 p_parent_region_appl_id IN number,
271 p_parent_region_code IN varchar2,
272 p_parent_primary_key_name IN varchar2 default NULL,
273 p_parent_key_value1 IN varchar2 default NULL,
274 p_parent_key_value2 IN varchar2 default NULL,
275 p_parent_key_value3 IN varchar2 default NULL,
276 p_parent_key_value4 IN varchar2 default NULL,
277 p_parent_key_value5 IN varchar2 default NULL,
278 p_parent_key_value6 IN varchar2 default NULL,
279 p_parent_key_value7 IN varchar2 default NULL,
280 p_parent_key_value8 IN varchar2 default NULL,
281 p_parent_key_value9 IN varchar2 default NULL,
282 p_parent_key_value10 IN varchar2 default NULL,
283 p_child_page_appl_id IN number default NULL,
284 p_child_page_code IN varchar2 default NULL,
285 p_where_clause IN varchar2 default NULL,
286 p_order_by_clause IN varchar2 default NULL,
287 p_responsibility_id IN number default NULL,
288 p_user_id IN number default NULL,
289 p_return_parents IN varchar2 default 'T',
290 p_return_children IN varchar2 default 'T',
291 p_return_node_display_only IN varchar2 default 'F',
292 p_set_trace IN varchar2 default 'F',
293 p_range_low IN number default 0,
294 p_range_high IN number default MAXROWNUM,
295 p_where_binds IN bind_tab default G_BIND_TAB_NULL,
296 p_max_rows IN number default NULL,
297 p_use_subquery IN varchar2 default 'F'
298 );
299
300 function getSecuredWhere(
301 p_region_rec IN region_rec,
302 p_responsibility_id IN number,
303 p_user_id IN number,
304 p_where_clause IN varchar2 default NULL,
305 p_order_by_clause IN varchar2 default NULL ,
306 p_return_node_display_only IN boolean default True,
307 p_display_region IN boolean default True
308 ) return varchar2;
309
310 END AK_QUERY_PKG;