1 PACKAGE jtf_region_pub AUTHID CURRENT_USER AS
2 /* $Header: jtfregns.pls 120.2 2005/10/25 05:24:34 psanyal ship $ */
3
4 TYPE ak_region_item is record (
5 attribute_label_long ak_region_items_vl.attribute_label_long%type -- varchar2(50)
6 , attribute_label_short ak_region_items_vl.attribute_label_short%type -- varchar2(30)
7 , column_name varchar2(30)
8 , data_type ak_region_items_vl.data_type%type -- varchar2(30)
9 , attribute_name ak_region_items_vl.attribute_name%type -- varchar2(30)
10 , attribute_code ak_region_items_vl.attribute_code%type -- varchar2(30)
11 , attribute_description ak_region_items_vl.attribute_description%type -- varchar2(2000)
12 , display_value_length ak_region_items_vl.display_value_length%type -- number
13 , lov_region_code ak_region_items_vl.lov_region_code%type -- varchar2(30)
14 , node_display_flag ak_region_items_vl.node_display_flag%type -- varchar2(1)
15 , node_query_flag ak_region_items_vl.node_query_flag%type -- varchar2(1)
16 );
17
18 TYPE ak_item_rec is record (
19 value_id number
20 ,column_name varchar2(75)
21 );
22
23 TYPE bind_rec IS record (
24 name varchar2(30)
25 ,value varchar2(240)
26 );
27
28 TYPE result_rec IS record (
29 value1 varchar2(240),
30 value2 varchar2(240),
31 value3 varchar2(240),
32 value4 varchar2(240),
33 value5 varchar2(240),
34 value6 varchar2(240),
35 value7 varchar2(240),
36 value8 varchar2(240),
37 value9 varchar2(240),
38 value10 varchar2(240),
39 value11 varchar2(240),
40 value12 varchar2(240),
41 value13 varchar2(240),
42 value14 varchar2(240),
43 value15 varchar2(240),
44 value16 varchar2(240),
45 value17 varchar2(240),
46 value18 varchar2(240),
47 value19 varchar2(240),
48 value20 varchar2(240),
49 value21 varchar2(240),
50 value22 varchar2(240),
51 value23 varchar2(240),
52 value24 varchar2(240),
53 value25 varchar2(240),
54 value26 varchar2(240),
55 value27 varchar2(240),
56 value28 varchar2(240),
57 value29 varchar2(240),
58 value30 varchar2(240),
59 value31 varchar2(240),
60 value32 varchar2(240),
61 value33 varchar2(240),
62 value34 varchar2(240),
63 value35 varchar2(240),
64 value36 varchar2(240),
65 value37 varchar2(240),
66 value38 varchar2(240),
67 value39 varchar2(240),
68 value40 varchar2(240),
69 value41 varchar2(240),
70 value42 varchar2(240),
71 value43 varchar2(240),
72 value44 varchar2(240),
73 value45 varchar2(240),
74 value46 varchar2(240),
75 value47 varchar2(240),
76 value48 varchar2(240),
77 value49 varchar2(240),
78 value50 varchar2(240),
79 value51 varchar2(240),
80 value52 varchar2(240),
81 value53 varchar2(240),
82 value54 varchar2(240),
83 value55 varchar2(240),
84 value56 varchar2(240),
85 value57 varchar2(240),
86 value58 varchar2(240),
87 value59 varchar2(240),
88 value60 varchar2(240),
89 value61 varchar2(240),
90 value62 varchar2(240),
91 value63 varchar2(240),
92 value64 varchar2(240),
93 value65 varchar2(240),
94 value66 varchar2(240),
95 value67 varchar2(240),
96 value68 varchar2(240),
97 value69 varchar2(240),
98 value70 varchar2(240),
99 value71 varchar2(240),
100 value72 varchar2(240),
101 value73 varchar2(240),
102 value74 varchar2(240),
103 value75 varchar2(240),
104 value76 varchar2(240),
105 value77 varchar2(240),
106 value78 varchar2(240),
107 value79 varchar2(240),
108 value80 varchar2(240),
109 value81 varchar2(240),
110 value82 varchar2(240),
111 value83 varchar2(240),
112 value84 varchar2(240),
113 value85 varchar2(240),
114 value86 varchar2(240),
115 value87 varchar2(240),
116 value88 varchar2(240),
117 value89 varchar2(240),
118 value90 varchar2(240),
119 value91 varchar2(240),
120 value92 varchar2(240),
121 value93 varchar2(240),
122 value94 varchar2(240),
123 value95 varchar2(240),
124 value96 varchar2(240),
125 value97 varchar2(240),
126 value98 varchar2(240),
127 value99 varchar2(240),
128 value100 varchar2(240)
129 );
130
131 TYPE ak_result_table is table of result_rec INDEX BY BINARY_INTEGER;
132
133 TYPE ak_item_rec_table is table of ak_item_rec INDEX BY BINARY_INTEGER;
134
135 TYPE ak_bind_table is table of bind_rec INDEX BY BINARY_INTEGER;
136
137 TYPE ak_region_items_table is table of ak_region_item INDEX BY BINARY_INTEGER;
138
139 -- these 3 are used by the get_regions procedure
140 type short_varchar2_table is table of varchar2(80) index by binary_integer;
141 type long_varchar2_table is table of varchar2(2000) index by binary_integer;
142 type number_table is table of number index by binary_integer;
143
144 -- the region is potentially a function of:
145 -- region_code, app_id, resp_id
146 -- but almost always, it does NOT vary based on the resp_id.
147 -- this function tests to see whether changing the resp_id can
148 -- possibly change the contents, given a region_code and app_id.
149 -- return 1 if true, else 0.
150 --
151 -- if the answer is '0', then we can optimize caching by bundling
152 -- all requests for region_code, app_id in one bucket.
153
154 function ever_varies_based_on_resp_id(
155 p_region_code varchar2,
156 p_application_id number) return number;
157
158 -- the get_regions procedure is used to load many regions in 1 round trip
159 --
160 -- the inputs are:
161 -- p_get_region_codes: an array of region_code strings
162 -- p_get_application_id
163 -- p_get_responsibility_ids: a table of respids
164 -- p_skip_column_name boolean: if this is true, then we don't fetch the
165 -- column names in any of the region items in any of the regions
166 -- which this returns. (just as we do in the get_region() procedure
167 -- when the region name is prepended with 'JTT_IGNORE_COLUMN_NAME_'.
168 --
169 -- This will return bulk data representing all region_codes times all
170 -- respids. We also do a special test to notice cases where (region_code,
171 -- appid, respid) is the same region for all respids (which is referred to
172 -- as a 'respid-invariant region'), and is denoted by a null value in the
173 -- corresponding p_ret_resp_ids table.
174 --
175 -- If the given p_get_responsibility_ids is empty, then we assume that we
176 -- should use all the respids of the given responsibilty. If the
177 -- p_get_region_codes is empty, then we assume that we should use all region
178 -- codes for the given appid (region data are striped by appid). We
179 -- ignore any region_codes in p_get_region_codes for which there is
180 -- no valid region in the database.
181 --
182 -- the p_lang variable is simply populated with the language of the
183 -- connection in which this procedure runs (i.e. what's returned from
184 -- select userenv('lang') from dual;
185 --
186 -- All 6 of the returned 'out tables' are of the same length, and correspond
187 -- item per item. The p_ret_object_name, p_ret_region_name, and
188 -- p_ret_region_description columns are only populated for the first row.
189 --
190 -- Here's an example of the returned data, corresponding to the 6
191 -- OUT tables.
192 --
193 -- REG_CODE_01 10012 name01 reg_name_01 reg_descr_01 <reg_item_01>
194 -- REG_CODE_01 10012 null null null <reg_item_02>
195 -- REG_CODE_01 10012 null null null <reg_item_03>
196 -- REG_CODE_01 10012 null null null <reg_item_04>
197 -- REG_CODE_01 10013 name02 reg_name_02 reg_descr_02 <reg_item_05>
198 -- REG_CODE_01 10013 null null null <reg_item_06>
199 -- REG_CODE_01 10013 null null null <reg_item_07>
200 -- REG_CODE_02 null name03 reg_name_03 reg_descr_03 <reg_item_08>
201 -- REG_CODE_02 null null null null <reg_item_09>
202 -- REG_CODE_02 null null null null <reg_item_10>
203 -- REG_CODE_02 null null null null <reg_item_11>
204 -- REG_CODE_02 null null null null <reg_item_12>
205 -- REG_CODE_03 10012 name03 reg_name_03 reg_descr_03 <reg_item_13>
206 -- REG_CODE_03 10012 null null null <reg_item_14>
207 -- REG_CODE_03 10013 name03 reg_name_03 reg_descr_03 <reg_item_15>
208 -- REG_CODE_03 10012 null null null <reg_item_16>
209 --
210 -- From which we can see:
211 -- => Region with code 'REG_CODE_01' does exist for respid 10012 and
212 -- 10013 under the given application_id. Futhermore,
213 -- (REG_CODE_01, <given_appid>, 10012) has 4 region_items
214 -- (REG_CODE_01, <given_appid>, 10013) has 3 region_items
215 -- => Region with code 'REG_CODE_02' does exist under the
216 -- given application_id, and its contents to NOT vary based on
217 -- RESPID!
218 -- => Region with code 'REG_CODE_03' does exist for respid 10012 and 10013
219 -- under the given application_id. It contains 2 region items in either
220 -- case.
221
222 procedure get_regions(p_get_region_codes short_varchar2_table,
223 p_get_application_id number,
224 p_get_responsibility_ids number_table,
225 p_skip_column_name boolean,
226 p_lang OUT NOCOPY /* file.sql.39 change */ varchar2,
227 p_ret_region_codes OUT NOCOPY /* file.sql.39 change */ short_varchar2_table,
228 p_ret_resp_ids OUT NOCOPY /* file.sql.39 change */ number_table,
229 p_ret_object_name OUT NOCOPY /* file.sql.39 change */ short_varchar2_table,
230 p_ret_region_name OUT NOCOPY /* file.sql.39 change */ short_varchar2_table,
231 p_ret_region_description OUT NOCOPY /* file.sql.39 change */ long_varchar2_table,
232 p_ret_region_items_table OUT NOCOPY /* file.sql.39 change */ jtf_region_pub.ak_region_items_table);
233
234 -- Gets the region info, possibly with items excluded based on the appid and
235 -- respid.
236 --
237 -- If your p_region_name is prepended with 'JTT_IGNORE_COLUMN_NAME_', then we
238 -- don't bother to fetch the 'column_name' attributes in the region_items.
239 -- This is a performance enhancment; most clients don't need the data and so
240 -- we were fetching it for no reason. So, if you want this performance
241 -- enhancement, and you used to call:
242 --
243 -- jtf_region_pub.get_region(regnname, appid, ...)
244 --
245 -- then you can call:
246 --
247 -- jtf_region_pub.get_region('JTT_IGNORE_COLUMN_NAME_' || regnname,
248 -- ppid, ...)
249 --
250 -- to get the new behavior.
251
252 PROCEDURE get_region(
253 p_region_code in varchar2
254 , p_application_id in number
255 , p_responsibility_id in number
256 , p_object_name OUT NOCOPY /* file.sql.39 change */ varchar2
257 , p_region_name OUT NOCOPY /* file.sql.39 change */ varchar2
258 , p_region_description OUT NOCOPY /* file.sql.39 change */ varchar2
259 , p_region_items_table OUT NOCOPY /* file.sql.39 change */ ak_region_items_table
260 );
261
262 FUNCTION get_region_item_name (
263 p_attribute_code in varchar2
264 , p_region_code in varchar2
265 ) RETURN VARCHAR2;
266
267 PROCEDURE ak_query(
268 p_application_id in number
269 , p_region_code in varchar2
270 , p_where_clause in varchar2
271 , p_order_by_clause in varchar2
272 , p_responsibility_id in number
273 , p_user_id in number
274 , p_range_low in number default 0
275 , p_range_high in number default null
276 , p_max_rows IN OUT NOCOPY /* file.sql.39 change */ number
277 , p_where_binds in ak_bind_table
278 , p_ak_item_rec_table OUT NOCOPY /* file.sql.39 change */ ak_item_rec_table
279 , p_ak_result_table OUT NOCOPY /* file.sql.39 change */ ak_result_table
280 );
281
282 END jtf_region_pub;
[Home] [Help]