1 package isc_fs_rpt_util_pkg
2 /* $Header: iscfsrptutils.pls 120.2 2006/04/12 20:45:40 kreardon noship $ */
3 as
4
5 -- the following are constants that define the actual (or pseudo)
6 -- dimension levels used in reports.
7 -- these constants are used internally and should be use externally
8 -- rather than making direct reference to the dimension+dimension_level
9
10 G_CURRENCY constant varchar2(50) := 'CURRENCY+FII_CURRENCIES';
11 G_CATEGORY constant varchar2(50) := 'ITEM+ENI_ITEM_VBH_CAT';
12 G_PRODUCT constant varchar2(50) := 'ITEM+ENI_ITEM';
13 G_CUSTOMER constant varchar2(50) := 'CUSTOMER+PROSPECT';
14 G_DISTRICT constant varchar2(50) := 'ORGANIZATION+JTF_ORG_SALES_GROUP';
15 G_DISTANCE_UOM constant varchar2(50) := 'BIV_FS_DISTANCE_UOM+BIV_FS_DISTANCE_UOM_LVL';
16 G_TASK_TYPE constant varchar2(50) := 'BIV_FS_TASK_TYPE+BIV_FS_TASK_TYPE_LVL';
17 G_BACKLOG_AGING_DISTRIB constant varchar2(50) := 'BIV_FS_DISTRIB+BIV_FS_BACKLOG_AGING';
18 G_TIME_TO_RES_DISTRIB constant varchar2(50) := 'BIV_FS_DISTRIB+BIV_FS_TIME_TO_RES';
19 G_TRVL_DIST_DISTRIB constant varchar2(50) := 'BIV_FS_DISTRIB+BIV_FS_TRVL_DIST';
20 G_TRVL_DIST_VAR_DISTRIB constant varchar2(50) := 'BIV_FS_DISTRIB+BIV_FS_TRVL_DIST_VAR';
21 G_TRVL_TIME_DISTRIB constant varchar2(50) := 'BIV_FS_DISTRIB+BIV_FS_TRVL_TIME';
22 G_TRVL_TIME_VAR_DISTRIB constant varchar2(50) := 'BIV_FS_DISTRIB+BIV_FS_TRVL_TIME_VAR';
23 G_INV_CATEGORY constant varchar2(50) := 'ITEM+ENI_ITEM_INV_CAT';
24 G_ITEM_ORG constant varchar2(50) := 'ITEM+ENI_ITEM_ORG';
25 G_SEVERITY constant varchar2(50) := 'SEVERITY+SEVERITY';
26
27 -- pseudo
28 G_ACTIVITY_EVENT constant varchar2(50) := 'TASK_ACTIVITY_EVENT';
29 G_TASK_OWNER constant varchar2(50) := 'TASK_OWNER';
30 G_BACKLOG_STATUS constant varchar2(50) := 'BIV_FS_DISTRIB+BIV_FS_BACKLOG_STATUS';
31 G_TASK_STATUS constant varchar2(50) := 'TASK_STATUS';
32 G_SR_STATUS constant varchar2(50) := 'SR_STATUS';
33 G_SR_TYPE constant varchar2(50) := 'SR_TYPE';
34 G_SR_OWNER constant varchar2(50) := 'SR_OWNER';
35 G_TASK_ASSIGNEE constant varchar2(50) := 'TASK_ASSIGNEE';
36 G_TASK_ADDRESS constant varchar2(50) := 'TASK_ADDRESS';
37
38 -- short form of G_DISTRICT
39 G_DISTRICT_SHORT constant varchar2(50) := 'ISC_FS_DISTRICT';
40
41 G_CURRENCY_BMAP constant number := 1;
42 G_CATEGORY_BMAP constant number := 2;
43 G_PRODUCT_BMAP constant number := 4;
44 G_CUSTOMER_BMAP constant number := 8;
45 G_DISTRICT_BMAP constant number := 16;
46 G_DISTANCE_UOM_BMAP constant number := 32;
47 G_TASK_TYPE_BMAP constant number := 64;
48 G_BACKLOG_AGING_DISTRIB_BMAP constant number := 128;
49 G_TIME_TO_RES_DISTRIB_BMAP constant number := 256;
50 G_TRVL_DIST_DISTRIB_BMAP constant number := 512;
51 G_TRVL_DIST_VAR_DISTRIB_BMAP constant number := 1024;
52 G_TRVL_TIME_DISTRIB_BMAP constant number := 2048;
53 G_TRVL_TIME_VAR_DISTRIB_BMAP constant number := 4096;
54 G_INV_CATEGORY_BMAP constant number := 8192;
55 G_ITEM_ORG_BMAP constant number := 16384;
56 G_SEVERITY_BMAP constant number := 32768;
57
58 -- pseudo
59 G_ACTIVITY_EVENT_BMAP constant number := 65536;
60 G_TASK_OWNER_BMAP constant number := 131072;
61 G_BACKLOG_STATUS_BMAP constant number := 262144;
62 G_TASK_STATUS_BMAP constant number := 524288;
63 G_SR_STATUS_BMAP constant number := 1048576;
64 G_SR_TYPE_BMAP constant number := 2097152;
65 G_SR_OWNER_BMAP constant number := 4194304;
66 G_TASK_ASSIGNEE_BMAP constant number := 8388608;
67 G_TASK_ADDRESS_BMAP constant number := 16777216;
68
69 -- this record type is used to store all of the details for using a
70 -- dimension level, it contains the join to table, join conditions
71 -- for both dimension table and fact table as well as the columns
72 -- to be displayed in a viewby, including ID and also the fact
73 -- column to be used to filter for this dimension.
74 type t_dimension_rec is record
75 ( dim_bmap number -- this is used to determine parameters/view by entered
76 , dim_table_name varchar2(500) -- this is the dimension table to join to
77 , dim_table_alias varchar2(200) -- this is the alias that this table will use
78 , dim_outer_join varchar2(2) -- indicates if join is outer join
79 , dim_col_name1 varchar2(200) -- this is the first join column in dim table
80 , oset_col_name1 varchar2(200) -- this is the first join table in the "oset"
81 , dim_col_name2 varchar2(200) -- this is the second join column in dim table
82 , oset_col_name2 varchar2(200) -- this is the second join table in the "oset"
83 , dim_col_name3 varchar2(200) -- this is the third join column in dim table
84 , oset_col_name3 varchar2(200) -- this is the third join table in the "oset"
85 , additional_where_clause varchar2(1000) -- any additional where clause needed
86 , viewby_col_name varchar2(200) -- this is the dimension table column to be displayed
87 , viewby_id_col_name varchar2(200) -- this is the dimension table ID column
88 , viewby_id_unassigned varchar2(30) -- this is the default value for ID when outer joined
89 , fact_filter_col_name varchar2(200) -- this is fact column to be used in where clause
90 );
91
92 type t_dimension_tbl is table of t_dimension_rec index by varchar2(200);
93
94 -- this record type is used to store the column defintions requiried for
95 -- producing detail reports. it is populated by "add_detail_column".
96 type t_detail_column_rec is record
97 ( dimension_level varchar2(200)
98 , dim_level_col_name varchar2(200)
99 , fact_col_name varchar2(200)
100 , fact_col_total varchar2(200)
101 , column_key varchar2(200)
102 );
103
104 type t_detail_column_tbl is table of t_detail_column_rec index by varchar2(200);
105
106 -- this procedure allows the report to register the
107 -- dimension level parameters that it is interested in
108 -- the parameters G_ORGANIZATION and G_DEPARTMENT will automatically
109 -- be registers for all reports via process parameters, here you
110 -- specify report specific parameters. Up to 10 parameters
111 -- can be registered at a time, this procedure can be called
112 -- as many times as needed, however once should be enough for
113 -- most reports.
114 -- p_dimensionN: the dimension level using it's logical name
115 -- p_filter_flagN: "Y" indicate if the dimension level is a
116 -- filtering parameter or "N" to indicate it is view by/detail only.
117 procedure register_dimension_levels
118 ( x_dimension_tbl in out nocopy t_dimension_tbl
119 , x_dim_filter_map in out nocopy poa_dbi_util_pkg.poa_dbi_dim_map
120 , p_dimension1 in varchar2
121 , p_filter_flag1 in varchar2
122 , p_dimension2 in varchar2 default null
123 , p_filter_flag2 in varchar2 default null
124 , p_dimension3 in varchar2 default null
125 , p_filter_flag3 in varchar2 default null
126 , p_dimension4 in varchar2 default null
127 , p_filter_flag4 in varchar2 default null
128 , p_dimension5 in varchar2 default null
129 , p_filter_flag5 in varchar2 default null
130 , p_dimension6 in varchar2 default null
131 , p_filter_flag6 in varchar2 default null
132 , p_dimension7 in varchar2 default null
133 , p_filter_flag7 in varchar2 default null
134 , p_dimension8 in varchar2 default null
135 , p_filter_flag8 in varchar2 default null
136 , p_dimension9 in varchar2 default null
137 , p_filter_flag9 in varchar2 default null
138 , p_dimension10 in varchar2 default null
139 , p_filter_flag10 in varchar2 default null
140 );
141
142 -- this procedure is a wrapper to poa_dbi_util_pkg.get_parameter_values
143 -- which processes the parameters. it also provides out variables
144 -- that can be used directly by your report or are needed to be
145 -- passed in to subsequent procedures/functions.
146 --
147 -- p_param: the parameter table passed into your report from PMV.
148 -- p_dimension_tbl: this is x_dimension_tbl returned from
149 -- register_dimension_levels
150 -- p_dim_filter_map: this is x_dim_filter_map returned from
151 -- register_dimension_levels
152 -- p_trend: "Y" for a trend report, "N" for a non-trend report and "D" for
153 -- detail report. "K" for current only non-trend reports.
154 -- p_custom_output: this returns custom bind values that your report will
155 -- need such as the &ISC_UNASSIGNED.
156 -- x_cur_suffix: returns "b", "g" or "sg" based on the currency parameter,
157 -- needed for curreny reports to determine base or global
158 -- currency column to use.
159 -- x_where_clause: returns the where clause to be passed into
160 -- poa_dbi_template_pkg.status_sql,
161 -- poa_dbi_template_pkg.trend_sql or detail_sql.
162 -- x_viewby_select: returns the select statement columns for a view by
163 -- report, included the fully qualified VIEWBY and
164 -- VIEWBY_ID columns.
165 -- x_join_tbl: returns the dimension join table to be passed into
166 -- poa_dbi_template_pkg.status_sql,
167 -- poa_dbi_template_pkg.trend_sql or detail_sql.
168 -- x_dim_bmap: returns a bitmap number of parameters entered and
169 -- view by selected, used for call to bind_group_id
170 -- x_comparison_type: returns the comparison type parameter value,
171 -- needed so can be passed into
172 -- poa_dbi_template_pkg.trend_sql
173 -- x_xtd: returns a short coded representation of the selected
174 -- period type, needed so can be passed into
175 -- poa_dbi_template_pkg.trend_sql
176 -- x_uom_suffix: returns "km" or "mi" based on the distance UOM parameter,
177 -- needed for travel reports to determine distance UOM
178 -- column to use.
179 procedure process_parameters
180 ( p_param in bis_pmv_page_parameter_tbl
181 , p_dimension_tbl in out nocopy t_dimension_tbl
182 , p_dim_filter_map in out nocopy poa_dbi_util_pkg.poa_dbi_dim_map
183 , p_trend in varchar2
184 , p_custom_output in out nocopy bis_query_attributes_tbl
185 , x_cur_suffix out nocopy varchar2
186 , x_where_clause out nocopy varchar2
187 , x_viewby_select out nocopy varchar2
188 , x_join_tbl out nocopy poa_dbi_util_pkg.poa_dbi_join_tbl
189 , x_dim_bmap out nocopy number
190 , x_comparison_type out nocopy varchar2
191 , x_xtd out nocopy varchar2
192 , x_uom_suffix out nocopy varchar2
193 );
194
195 -- this is an overload of process_parameters that does not return
196 -- x_uom_suffix.
197 procedure process_parameters
198 ( p_param in bis_pmv_page_parameter_tbl
199 , p_dimension_tbl in out nocopy t_dimension_tbl
200 , p_dim_filter_map in out nocopy poa_dbi_util_pkg.poa_dbi_dim_map
201 , p_trend in varchar2
202 , p_custom_output in out nocopy bis_query_attributes_tbl
203 , x_cur_suffix out nocopy varchar2
204 , x_where_clause out nocopy varchar2
205 , x_viewby_select out nocopy varchar2
206 , x_join_tbl out nocopy poa_dbi_util_pkg.poa_dbi_join_tbl
207 , x_dim_bmap out nocopy number
208 , x_comparison_type out nocopy varchar2
209 , x_xtd out nocopy varchar2
210 );
211
212 -- this is a utility function that returns the value for the first
213 -- occurance of a named parameter.
214 -- this is used internally and may also be used externally for
215 -- returning the value of any named parameter (e.g. 'ORDERBY')
216 function get_parameter_value
217 ( p_param in bis_pmv_page_parameter_tbl
218 , p_parameter_name in varchar2
219 )
220 return varchar2;
221
222 -- this is a utility function that returns the ID for the first
223 -- occurance of a named parameter.
224 -- this is used internally and may also be used externally for
225 -- returning the value of any named parameter (e.g. G_COST_ELEMENT)
226 -- by default "All" will be replaced with null unless
227 -- p_no_replace_all = Y
228 function get_parameter_id
229 ( p_param in bis_pmv_page_parameter_tbl
230 , p_parameter_name in varchar2
231 , p_no_replace_all in varchar2 default null
232 )
233 return varchar2;
234
235 -- this procedure is used by detail reports to define a column that
236 -- they want to be displayed. it is similar in concept to
237 -- poa_dbi_util_pkg.add_column which is used for adding measure columns
238 -- to a summary report.
239 -- this procedure can add both attribute (text) columns from a table
240 -- to be joined to, or an attribute (text) or measure column
241 -- from the fact table.
242 -- this procedure can be called for each item to be included in the
243 -- select list of the detail report (excludes say URLs)
244 --
245 -- to select a column from a dimension, provide a value for p_dimension_level
246 -- and optionally provide a value for p_dim_level_col_name (if not
247 -- provided, then the "viewby_col_name" from the dimension level will
248 -- be used). by defining the dimension level here, all of the
249 -- necessary "from" and "where" clauses will be built by detail_sql.
250 --
251 -- to select a column from the fact, provide a value for p_fact_col_name
252 -- and optionally "Y" for p_fact_col_total.
253 --
254 -- for both dimension and fact columns you need to provide a value for
255 -- p_fact_col_total, this is the key that you will use to extract the
256 -- column in your select statement using get_detail_column.
257 -- note: if you set p_fact_col_total = "Y" their will automatically
258 -- be a key of p_column_key || '_total' also created for you.
259 procedure add_detail_column
260 ( p_detail_col_tbl in out nocopy t_detail_column_tbl
261 , p_dimension_tbl in t_dimension_tbl
262 , p_dimension_level in varchar2 default null
263 , p_dim_level_col_name in varchar2 default null
264 , p_fact_col_name in varchar2 default null
265 , p_fact_col_total in varchar2 default null
266 , p_column_key in varchar2
267 );
268
269 -- this function is used by detail reports to return the fully
270 -- build column, including table alias and nvl() as necessary,
271 -- for you to use in your select statement.
272 -- the column is extracted based on the key value p_column_key
273 -- (or p_column_key || '_total') that you defined with add_detail_column.
274 -- you can optionally provide a value for p_alias and this will be
275 -- appended.
276 function get_detail_column
277 ( p_detail_col_tbl in t_detail_column_tbl
278 , p_column_key in varchar2
279 , p_alias in varchar2 default null
280 )
281 return varchar2;
282
283 -- this function is used to build the "from" and "where" clauses
284 -- of a detail report that does require any aggregation. for
285 -- aggregated detail reports you should be using poa_dbi_template_pkg
286 -- procedures. it also includes the order by and optionally the
287 -- ranking logic for "window" reports.
288 --
289 -- p_detail_col_tbl: this is the detail column table that you
293 -- p_mv_name: this is the MV name returned from process_parameters.
290 -- populated with calls to add_detail_column.
291 -- p_dimension_tbl: this is the dimension table that you populated
292 -- with get_dimension_tbl and used in add_detail_column.
294 -- p_where_clause: this is the where clause returned from process_parameters.
295 -- p_rank_order: this is the ordering clause for the rank function,
296 -- only provide this parameter if you are using a "window" report.
297 -- p_filter_where: optionally add any restrictions to the inner most
298 -- where clause.
299 function detail_sql
300 ( p_detail_col_tbl in t_detail_column_tbl
301 , p_dimension_tbl in t_dimension_tbl
302 , p_mv_name in varchar2
303 , p_where_clause in varchar2
304 , p_rank_order in varchar2 default null
305 , p_filter_where in varchar2 default null
306 , p_override_date_clause in varchar2 default null
307 )
308 return varchar2;
309
310 -- this function is a wrapper to poa_dbi_util_pkg.change_clause
311 -- p_current_column: the current measure column
312 -- p_prior_column: the prior period measure column
313 -- p_column_alias: optional provide the alias that you
314 -- want to have appended.
315 -- p_percent: set this to "Y" for percent change or "N" for absolute
316 -- change, the default is percent change
317 function change_column
318 ( p_current_column in varchar2
319 , p_prior_column in varchar2
320 , p_column_alias in varchar2
321 , p_percent in varchar2 default null -- treated as 'Y'
322 ) return varchar2;
323
324 -- this function is a wrapper to poa_dbi_util_pkg.rate_clause
325 -- p_current_column: the numerator measure column
326 -- p_prior_column: the denominator measure column
327 -- p_column_alias: optional provide the alias that you
328 -- want to have appended.
329 -- p_percent: set this to "Y" for percent or "N" for ratio,
330 -- the default is percent
331 function rate_column
332 ( p_numerator in varchar2
333 , p_denominator in varchar2
334 , p_column_alias in varchar2
335 , p_percent in varchar2 default null -- treated as 'Y'
336 ) return varchar2;
337
338 -- this procedure calculates the grp_id value for an MV
339 -- that implements grouping sets and adds the value as a bind variable.
340 -- p_dim_bmap: the dimension level bitmap based on entered parameters
341 -- and selected view by returned from process_parameters
342 -- p_custom_output: this returns custom bind values that your report will
343 -- need such as the &ISC_GRP_ID.
344 -- p_columnN: up to 8 column names can be passed in, use the logical
345 -- name for the dimension level. the order of these columns
346 -- is important and must match the grouping_id clause in the
347 -- corresponding MV defintion.
348 procedure bind_group_id
349 ( p_dim_bmap in number
350 , p_custom_output in out nocopy bis_query_attributes_tbl
351 , p_column1 in varchar2 default null
352 , p_column2 in varchar2 default null
353 , p_column3 in varchar2 default null
354 , p_column4 in varchar2 default null
355 , p_column5 in varchar2 default null
356 , p_column6 in varchar2 default null
357 , p_column7 in varchar2 default null
358 , p_column8 in varchar2 default null
359 );
360
361 -- this function returns a snippet of where clause code that can be included
362 -- in your MV inline view text
363 -- p_fact_alias: the alias you have assigned to your fact table/MV, typically
364 -- the will be "fact"
365 -- p_org_id: the value of the user selected org id. the actual value is not
366 -- important. if the user has selected an value (other than All)
367 -- then this function will return null as the main query will
368 -- filter on organization_id anyway.
369 function get_sec_where_clause
370 ( p_fact_alias in varchar2
371 , p_org_id in varchar2
372 )
373 return varchar2;
374
375 -- this function returns a comments (/* */) dump of the
376 -- contents of p_param, showing parameter name, value, id,
377 -- dimension etc.
378 -- this can be used during development to validate that the
379 -- parameters PMV is passing are as you would expect.
380 -- note: the function should not be included in any code
381 -- released as it will cause a unique sql statement to be
382 -- generated each time, it is for development testing only.
383 function dump_parameters
384 ( p_param in bis_pmv_page_parameter_tbl )
385 return varchar2;
386
387 -- this function allows you to create a second (or Nth) view by
388 -- in a report, for example asset downtime view by asset plus
389 -- criticality and returns the select list column for you to use.
390 -- p_view_by: the dimension level using it's logical name
391 -- p_dimension_tbl: this is x_dimension_tbl returned from
392 -- register_dimension_levels
393 -- p_join_tbl: this is x_join_tbl returned from process_parameters
394 function add_view_by
395 ( p_view_by in varchar2
396 , p_dimension_tbl in t_dimension_tbl
397 , p_join_tbl in out nocopy poa_dbi_util_pkg.poa_dbi_join_tbl
398 )
399 return varchar2;
400
401 -- this procedure is a noop
402 procedure check_district_filter
403 ( p_param in bis_pmv_page_parameter_tbl
404 , p_dim_map in out nocopy poa_dbi_util_pkg.poa_dbi_dim_map
405 );
406
407 -- this function returns the portion of the inner query select stmt
408 -- that can be used by window queries. It is based on the code used
409 -- in poa_dbi_template_pkg.get_group_and_sel_clause which is unfortunately
410 -- not public.
411 -- p_join_tables: this is x_join_tbl returned from process_parameters
412 function get_inner_select_col
413 ( p_join_tables in poa_dbi_util_pkg.poa_dbi_join_tbl
414 )
415 return varchar2;
416
417 -- this function returns the drill down URL for product category
418 -- it returns NULL unless p_view_by is G_CATEGORY
419 -- p_view_by: pass the report view by
420 -- p_function_name: function name of the report to drill to
421 -- p_column_alias: optionally pass the column alias to be appended to
422 -- return URL
423 function get_category_drill_down
424 ( p_view_by in varchar2
425 , p_function_name in varchar2
426 , p_column_alias in varchar2 default null
427 )
428 return varchar2;
429
430 -- this function returns the drill down URL for district
431 -- it returns NULL unless p_view_by is G_DISTRICT
432 -- NULL will also be returned if the row value is for any value under
433 -- the Unassigned district, or the row value is for a group at the technician
434 -- level (occurs when owner/assignee is a group and not an individual).
435 -- p_view_by: pass the report view by, if the view by is G_DISTRICT
436 -- logic will be applied to check value of district_id_c
437 -- p_function_name: function name of the report to drill to
438 -- p_column_alias: optionally pass the column alias to be appended to
439 -- return URL
440 function get_district_drill_down
441 ( p_view_by in varchar2
442 , p_function_name in varchar2
443 , p_column_alias in varchar2 default null
444 )
445 return varchar2;
446
447 -- this function returns the drill to service request detail URL
448 -- p_sr_id_col: the column name/alias in the report query that contains
449 -- the incident_id value
450 function get_sr_detail_page_function
451 ( p_sr_id_col in varchar2
452 )
453 return varchar2;
454
455 -- this procedures adds any custom bind variable value to p_custom_output
456 -- p_custom_output: this returns custom bind values, it will be initialized
457 -- only if NULL
458 -- p_parameter_name: the name of the bind variable, this needs to match
459 -- the string used in your report query. you should include
460 -- the prefix of "&" etc
461 -- p_parameter_data_type: this procedure does not validate this parameter,
462 -- however it should be one of the following constants:
463 -- BIS_PMV_PARAMETERS_PUB.INTEGER_BIND
464 -- BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND
465 -- BIS_PMV_PARAMETERS_PUB.DATE_BIND
466 -- BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND
467 -- BIS_PMV_PARAMETERS_PUB.CHARACTER_BIND
468 -- BIS_PMV_PARAMETERS_PUB.MESSAGE_BIND
469 -- p_parameter_value: this is the value to be used for the bind value, it needs
470 -- to be compatible with p_parameter_data_type, for DATE_BIND
471 -- pass date as DD/MM/YYYY
472 procedure add_custom_bind_parameter
473 ( p_custom_output in out nocopy bis_query_attributes_tbl
474 , p_parameter_name in varchar2
475 , p_parameter_data_type in varchar2
476 , p_parameter_value in varchar2
477 );
478
479 -- this procedure adds bind variable values to p_custom_output for
480 -- the low and high values for a bucket set
481 -- p_param: the parameter table passed into your report from PMV.
482 -- p_param_name: the parameter name that you need to check the value
483 -- of to get the low and high values
484 -- p_short_name: the bucket set short name
485 -- p_custom_output: this returns custom bind values, it will be initialized
486 -- only if NULL
487 -- p_low_token: optionally provide the bind variable name to use for low
488 -- value, defaults to &ISC_FS_LOW
489 -- p_high_token: optionally provide the bind variable name to use for low
490 -- value, defaults to &ISC_FS_HIGH
491 procedure bind_low_high
492 ( p_param in bis_pmv_page_parameter_tbl
493 , p_param_name in varchar2
494 , p_short_name in varchar2
495 , p_custom_output in out nocopy bis_query_attributes_tbl
496 , p_low_token in varchar2 default null
497 , p_high_token in varchar2 default null
498 );
499
500 -- this function returns the URL to enable drill on view by date for trend
501 -- reports. it returns to columns for inclusion in the select list, first
502 -- for WTD and second for RLW. One, other or both can be returned as NULL.
503 -- p_xtd: period type code (WTD,RLW are the important ones)
504 -- p_function_name: function name of the report to drill to
505 -- p_alias_wtd: column alias to be used for WTD function URL
506 -- p_alias_rlw: column alias to be used for RLW function URL
507 -- p_override_end_date: optionally provide the column in the query that
508 -- contains the date to be passed, default cal.end_date
509 function get_trend_drill
510 ( p_xtd in varchar2
511 , p_function_name in varchar2
512 , p_alias_wtd in varchar2
513 , p_alias_rlw in varchar2
514 , p_override_end_date in varchar2 default null
515 )
516 return varchar2;
517
518 -- this function returns the default parameter values for a
519 -- web portlet provider (dashboard region)
520 -- p_region_code: default parameter values are based on region code
521 function default_params
522 ( p_region_code in varchar2
523 )
524 return varchar2;
525
526 -- this procedure modifies the POA template generated
527 -- join between fact table and inline view using fii_time_rpt_struct_v
528 -- it also removes the extra table (inline view) added to the report query
529 -- to allows distict_id_c to be included in the group by clause
530 -- p_query: the query string generated by the POA template
531 -- p_trend: Y or N to indicate if the query is for a trend report or not
532 procedure enhance_time_join
533 ( p_query in out nocopy varchar2
534 , p_trend in varchar2
535 );
536
537 -- this function returns the drill to task detail URL
538 -- p_task_id_col: the column name/alias in the report query that contains
542 )
539 -- the task_id value
540 function get_task_detail_page_function
541 ( p_task_id_col in varchar2
543 return varchar2;
544
545 -- this function returns the drill to detail URL string for
546 -- a measure column.
547 -- p_view_by: pass the report view by, if the view by is G_DISTRICT
548 -- logic will be applied to check value of district_id_c
549 -- before return URL
550 -- p_check_column_name: pass the base column name that will to have
551 -- it's value checked
552 -- p_function_name: pass the function name to be used in the URL
553 -- p_column_alias: optionally pass the column alias to be appended to
554 -- return URL
555 -- p_extra_params: optionally pass any extra parameters you need to
556 -- be appended to the return URL
557 -- p_check_column: optionally pass "Y" to have base column name checked
558 -- (NULL/0), if NULL then assumed to be "N". This may
559 -- toogle to assumed "Y" if ER 4532901 is approved.
560 -- p_check_resource: optionally pass "Y" to have have drill enabled only
561 -- for resource level rows when view by G_DISTRICT
562 function get_detail_drill_down
563 ( p_view_by in varchar2
564 , p_check_column_name in varchar2
565 , p_function_name in varchar2
566 , p_column_alias in varchar2 default null
567 , p_extra_params in varchar2 default null
568 , p_check_column in varchar2 default null
569 , p_check_resource in varchar2 default null
570 )
571 return varchar2;
572
573 -- this function returns a drill to detail URL string for
574 -- all of the valid bucket measure columns. it internally
575 -- calls get_detail_drill_down for each column appending the
576 -- bucket number to p_extra_params
577 -- p_function_name: pass the function name to be used in the URL
578 -- p_check_column_name: pass the base column name that will to have
579 -- it's value checked
580 -- p_column_alias: pass the base column alias to be appended to
581 -- return URL, the bucket number will be appended
582 -- p_extra_params: pass any extra parameters you need to
583 -- be appended to the return URL, the bucket number will be
584 -- appended
585 -- p_check_column: optionally pass "Y" to have base column name checked
586 -- (NULL/0), if NULL then assumed to be "N". This may
587 -- toogle to assumed "Y" if ER 4532901 is approved.
588 -- p_check_resource: optionally pass "Y" to have have drill enabled only
589 -- for resource level rows when view by G_DISTRICT
590 function get_bucket_drill_down
591 ( p_bucket_rec in bis_bucket_pub.bis_bucket_rec_type
592 , p_view_by in varchar2
593 , p_check_column_name in varchar2
594 , p_function_name in varchar2
595 , p_column_alias in varchar2
596 , p_extra_params in varchar2
597 , p_check_column in varchar2 default null
598 , p_check_resource in varchar2 default null
599 )
600 return varchar2;
601
602 -- this function returns Y or N to indicate whether the current district
603 -- parameter (id) is a leaf node (or resource) or non-leaf node.
604 -- It returns Y if the district is a leaf node district or a resource,
605 -- otherwise returns N.
606 -- p_param: the parameter table passed into your report from PMV.
607 function is_district_leaf_node
608 ( p_param in bis_pmv_page_parameter_tbl
609 )
610 return varchar2;
611
612 end isc_fs_rpt_util_pkg;
[Home] [Help]