1 package isc_maint_rpt_util_pkg
2 /* $Header: iscmaintrptutils.pls 120.1 2005/09/13 05:37:43 nbhamidi 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 G_ORGANIZATION constant varchar2(50) := 'ORGANIZATION+ORGANIZATION';
10 G_CURRENCY constant varchar2(50) := 'CURRENCY+FII_CURRENCIES';
11 G_DEPARTMENT constant varchar2(50) := 'RESOURCE+ENI_RESOURCE_DEPARTMENT';
12 G_ASSET_GROUP constant varchar2(50) := 'BIV_MAINT_ASSET+BIV_MAINT_ASSET_GROUP_LVL';
13 G_ASSET_NUMBER constant varchar2(50) := 'BIV_MAINT_ASSET+BIV_MAINT_ASSET_NUMBER_LVL';
14 G_ACTIVITY constant varchar2(50) := 'BIV_MAINT_ACTIVITY+BIV_MAINT_ACTIVITY_LVL';
15 G_COST_CATEGORY constant varchar2(50) := 'BIV_MAINT_CST_CATEGORY+BIV_MAINT_CST_CATEGORY_LVL';
16 G_COST_ELEMENT constant varchar2(50) := 'BIV_MAINT_CST_ELEMENT+BIV_MAINT_CST_ELEMENT_LVL';
17 G_WORK_ORDER_TYPE constant varchar2(55) := 'BIV_MAINT_WK_ORDER_TYPE+BIV_MAINT_WK_ORDER_TYPE_LVL';
18 G_LATE_CMPL_AGING constant varchar2(50) := 'BIV_MAINT_AGING+BIV_MAINT_LATE_COMP_AGING';
19 G_PAST_DUE_AGING constant varchar2(50) := 'BIV_MAINT_AGING+BIV_MAINT_PAST_DUE_AGING';
20 G_ASSET_CATEGORY constant varchar2(50) := 'BIV_MAINT_ASSET+BIV_MAINT_ASSET_CATEGORY_LVL';
21 G_ASSET_CRITICALITY constant varchar2(60) := 'BIV_MAINT_ASSET_CRITICALITY+BIV_MAINT_ASSET_CRITICAL_LVL';
22 G_REQUEST_TYPE constant varchar2(50) := 'BIV_MAINT_REQUEST_TYPE+BIV_MAINT_REQUEST_TYPE_LVL';
23 G_REQ_CMPL_AGING constant varchar2(50) := 'BIV_MAINT_AGING+BIV_MAINT_REQ_COMP_AGING';
24 G_WORK_ORDER_STATUS constant varchar2(60) := 'BIV_MAINT_WK_ORDER_STATUS+BIV_MAINT_WK_ORDER_STATUS_LVL';
25 G_RESOURCE constant varchar2(50) := 'RESOURCE+ENI_RESOURCE';
26
27
28 -- these is not a real dimension level but exist for joins in detail reports
29 G_WIP_ENTITIES constant varchar2(50) := 'ISC_MAINT_WIP_ENTITIES';
30 G_ESTIMATED constant varchar2(50) := 'ISC_MAINT_ESTIMATED_COST_VALUE+LOOKUP';
31 G_REQUESTS constant varchar2(50) := 'ISC_MAINT_REQUESTS';
32 G_REQUEST_SEVERITIES constant varchar2(50) := 'ISC_MAINT_REQUEST_SEVERITIES';
33
34 -- this record type is used to store all of the details for using a
35 -- dimension level, it contains the join to table, join conditions
36 -- for both dimension table and fact table as well as the columns
37 -- to be displayed in a viewby, including ID and also the fact
38 -- column to be used to filter for this dimension.
39 type t_dimension_rec is record
40 ( dim_bmap number -- this is used to determine parameters/view by entered
41 , dim_table_name varchar2(500) -- this is the dimension table to join to
42 , dim_table_alias varchar2(200) -- this is the alias that this table will use
43 , dim_outer_join varchar2(2) -- indicates if join is outer join
44 , dim_col_name1 varchar2(200) -- this is the first join column in dim table
45 , oset_col_name1 varchar2(200) -- this is the first join table in the "oset"
46 , dim_col_name2 varchar2(200) -- this is the second join column in dim table
47 , oset_col_name2 varchar2(200) -- this is the second join table in the "oset"
48 , dim_col_name3 varchar2(200) -- this is the third join column in dim table
49 , oset_col_name3 varchar2(200) -- this is the third join table in the "oset"
50 , additional_where_clause varchar2(1000) -- any additional where clause needed
51 , viewby_col_name varchar2(200) -- this is the dimension table column to be displayed
52 , viewby_id_col_name varchar2(200) -- this is the dimension table ID column
53 , viewby_id_unassigned varchar2(30) -- this is the default value for ID when outer joined
54 , fact_filter_col_name varchar2(200) -- this is fact column to be used in where clause
55 );
56
57 type t_dimension_tbl is table of t_dimension_rec index by varchar2(200);
58
59 -- this record type is used to store the column defintions requiried for
60 -- producing detail reports. it is populated by "add_detail_column".
61 type t_detail_column_rec is record
62 ( dimension_level varchar2(200)
63 , dim_level_col_name varchar2(200)
64 , fact_col_name varchar2(200)
65 , fact_col_total varchar2(200)
66 , column_key varchar2(200)
67 );
68
69 type t_detail_column_tbl is table of t_detail_column_rec index by varchar2(200);
70
71 -- this procedure allows the report to register the
72 -- dimension level parameters that it is interested in
73 -- the parameters G_ORGANIZATION and G_DEPARTMENT will automatically
74 -- be registers for all reports via process parameters, here you
75 -- specify report specific parameters. Up to 10 parameters
76 -- can be registered at a time, this procedure can be called
77 -- as many times as needed, however once should be enough for
78 -- most reports.
79 -- p_dimensionN: the dimension level using it's logical name
80 -- p_filter_flagN: "Y" indicate if the dimension level is a
81 -- filtering parameter or "N" to indicate it is view by/detail only.
82 procedure register_dimension_levels
83 ( x_dimension_tbl in out nocopy t_dimension_tbl
84 , x_dim_filter_map in out nocopy poa_dbi_util_pkg.poa_dbi_dim_map
85 , p_dimension1 in varchar2
86 , p_filter_flag1 in varchar2
87 , p_dimension2 in varchar2 default null
88 , p_filter_flag2 in varchar2 default null
89 , p_dimension3 in varchar2 default null
90 , p_filter_flag3 in varchar2 default null
91 , p_dimension4 in varchar2 default null
92 , p_filter_flag4 in varchar2 default null
93 , p_dimension5 in varchar2 default null
94 , p_filter_flag5 in varchar2 default null
95 , p_dimension6 in varchar2 default null
96 , p_filter_flag6 in varchar2 default null
97 , p_dimension7 in varchar2 default null
98 , p_filter_flag7 in varchar2 default null
99 , p_dimension8 in varchar2 default null
100 , p_filter_flag8 in varchar2 default null
101 , p_dimension9 in varchar2 default null
102 , p_filter_flag9 in varchar2 default null
103 , p_dimension10 in varchar2 default null
104 , p_filter_flag10 in varchar2 default null
105 );
106
107 -- this procedure is a wrapper to poa_dbi_util_pkg.get_parameter_values
108 -- which processes the parameters. it also provides out variables
109 -- that can be used directly by your report or are needed to be
110 -- passed in to subsequent procedures/functions.
111 --
112 -- p_param: the parameter table passed into your report from PMV.
113 -- p_dimension_tbl: this is x_dimension_tbl returned from
114 -- register_dimension_levels
115 -- p_dim_filter_map: this is x_dim_filter_map returned from
116 -- register_dimension_levels
117 -- p_trend: "Y" for a trend report, "N" for a non-trend report and "D" for
118 -- detail report. "K" for current only non-trend reports.
119 -- p_custom_output: this returns custom bind values that your report will
120 -- need such as the &ISC_UNASSIGNED.
121 -- x_cur_suffix: returns "b", "g" or "sg" based on the currency parameter,
122 -- needed for curreny reports to determine base or global
123 -- currency column to use.
124 -- x_where_clause: returns the where clause to be passed into
125 -- poa_dbi_template_pkg.status_sql,
126 -- poa_dbi_template_pkg.trend_sql or detail_sql.
127 -- x_viewby_select: returns the select statement columns for a view by
128 -- report, included the fully qualified VIEWBY and
129 -- VIEWBY_ID columns.
130 -- x_join_tbl: returns the dimension join table to be passed into
131 -- poa_dbi_template_pkg.status_sql,
132 -- poa_dbi_template_pkg.trend_sql or detail_sql.
133 -- x_dim_bmap: returns a bitmap number of parameters entered and
134 -- view by selected, used for call to bind_group_id
135 -- x_comparison_type: returns the comparison type parameter value,
136 -- needed so can be passed into
137 -- poa_dbi_template_pkg.trend_sql
138 -- x_xtd: returns a short coded representation of the selected
139 -- period type, needed so can be passed into
140 -- poa_dbi_template_pkg.trend_sql
141 procedure process_parameters
142 ( p_param in bis_pmv_page_parameter_tbl
143 , p_dimension_tbl in out nocopy t_dimension_tbl
144 , p_dim_filter_map in out nocopy poa_dbi_util_pkg.poa_dbi_dim_map
145 , p_trend in varchar2
146 , p_custom_output in out nocopy bis_query_attributes_tbl
147 , x_cur_suffix out nocopy varchar2
148 , x_where_clause out nocopy varchar2
149 , x_viewby_select out nocopy varchar2
150 , x_join_tbl out nocopy poa_dbi_util_pkg.poa_dbi_join_tbl
151 , x_dim_bmap out nocopy number
152 , x_comparison_type out nocopy varchar2
153 , x_xtd out nocopy varchar2
154 );
155
156 -- this is a utility function that returns the value for the first
157 -- occurance of a named parameter.
158 -- this is used internally and may also be used externally for
159 -- returning the value of any named parameter (e.g. 'ORDERBY')
160 function get_parameter_value
161 ( p_param in bis_pmv_page_parameter_tbl
162 , p_parameter_name in varchar2
163 )
164 return varchar2;
165
166 -- this is a utility function that returns the ID for the first
167 -- occurance of a named parameter.
168 -- this is used internally and may also be used externally for
169 -- returning the value of any named parameter (e.g. G_COST_ELEMENT)
170 -- by default "All" will be replaced with null unless
171 -- p_no_replace_all = Y
172 function get_parameter_id
173 ( p_param in bis_pmv_page_parameter_tbl
174 , p_parameter_name in varchar2
175 , p_no_replace_all in varchar2 default null
176 )
177 return varchar2;
178
179 -- this procedure is used by detail reports to define a column that
180 -- they want to be displayed. it is similar in concept to
181 -- poa_dbi_util_pkg.add_column which is used for adding measure columns
182 -- to a summary report.
183 -- this procedure can add both attribute (text) columns from a table
184 -- to be joined to, or an attribute (text) or measure column
185 -- from the fact table.
186 -- this procedure can be called for each item to be included in the
187 -- select list of the detail report (excludes say URLs)
188 --
189 -- to select a column from a dimension, provide a value for p_dimension_level
190 -- and optionally provide a value for p_dim_level_col_name (if not
191 -- provided, then the "viewby_col_name" from the dimension level will
192 -- be used). by defining the dimension level here, all of the
193 -- necessary "from" and "where" clauses will be built by detail_sql.
194 --
195 -- to select a column from the fact, provide a value for p_fact_col_name
196 -- and optionally "Y" for p_fact_col_total.
197 --
198 -- for both dimension and fact columns you need to provide a value for
199 -- p_fact_col_total, this is the key that you will use to extract the
200 -- column in your select statement using get_detail_column.
201 -- note: if you set p_fact_col_total = "Y" their will automatically
202 -- be a key of p_column_key || '_total' also created for you.
203 procedure add_detail_column
204 ( p_detail_col_tbl in out nocopy t_detail_column_tbl
205 , p_dimension_tbl in t_dimension_tbl
206 , p_dimension_level in varchar2 default null
207 , p_dim_level_col_name in varchar2 default null
208 , p_fact_col_name in varchar2 default null
209 , p_fact_col_total in varchar2 default null
210 , p_column_key in varchar2
211 );
212
213 -- this function is used by detail reports to return the fully
214 -- build column, including table alias and nvl() as necessary,
215 -- for you to use in your select statement.
216 -- the column is extracted based on the key value p_column_key
217 -- (or p_column_key || '_total') that you defined with add_detail_column.
218 -- you can optionally provide a value for p_alias and this will be
219 -- appended.
220 function get_detail_column
221 ( p_detail_col_tbl in t_detail_column_tbl
222 , p_column_key in varchar2
223 , p_alias in varchar2 default null
224 )
225 return varchar2;
226
227 -- this function is used to build the "from" and "where" clauses
228 -- of a detail report that does require any aggregation. for
229 -- aggregated detail reports you should be using poa_dbi_template_pkg
230 -- procedures. it also includes the order by and optionally the
231 -- ranking logic for "window" reports.
232 --
233 -- p_detail_col_tbl: this is the detail column table that you
234 -- populated with calls to add_detail_column.
235 -- p_dimension_tbl: this is the dimension table that you populated
236 -- with get_dimension_tbl and used in add_detail_column.
237 -- p_mv_name: this is the MV name returned from process_parameters.
238 -- p_where_clause: this is the where clause returned from process_parameters.
239 -- p_rank_order: this is the ordering clause for the rank function,
240 -- only provide this parameter if you are using a "window" report.
241 -- p_filter_where: optionally add any restrictions to the inner most
242 -- where clause.
243 function detail_sql
244 ( p_detail_col_tbl in t_detail_column_tbl
245 , p_dimension_tbl in t_dimension_tbl
246 , p_mv_name in varchar2
247 , p_where_clause in varchar2
248 , p_rank_order in varchar2 default null
249 , p_filter_where in varchar2 default null
250 , p_override_date_clause in varchar2 default null
251 )
252 return varchar2;
253
254 -- this function is a wrapper to poa_dbi_util_pkg.change_clause
255 -- p_current_column: the current measure column
256 -- p_prior_column: the prior period measure column
257 -- p_column_alias: optional provide the alias that you
258 -- want to have appended.
259 -- p_percent: set this to "Y" for percent change or "N" for absolute
260 -- change, the default is percent change
261 function change_column
262 ( p_current_column in varchar2
263 , p_prior_column in varchar2
264 , p_column_alias in varchar2
265 , p_percent in varchar2 default 'Y'
266 ) return varchar2;
267
268 -- this function is a wrapper to poa_dbi_util_pkg.rate_clause
269 -- p_current_column: the numerator measure column
270 -- p_prior_column: the denominator measure column
271 -- p_column_alias: optional provide the alias that you
272 -- want to have appended.
273 -- p_percent: set this to "Y" for percent or "N" for ratio,
274 -- the default is percent
275 function rate_column
276 ( p_numerator in varchar2
277 , p_denominator in varchar2
278 , p_column_alias in varchar2
279 , p_percent in varchar2 default 'Y'
280 ) return varchar2;
281
282 -- this procedure calculates the grp_id value for an MV
283 -- that implements grouping sets and adds the value as a bind variable.
284 -- p_dim_bmap: the dimension level bitmap based on entered parameters
285 -- and selected view by returned from process_parameters
286 -- p_custom_output: this returns custom bind values that your report will
287 -- need such as the &ISC_GRP_ID.
288 -- p_columnN: up to 8 column names can be passed in, use the logical
289 -- name for the dimension level. the order of these columns
290 -- is important and must match the grouping_id clause in the
291 -- corresponding MV defintion.
292 procedure bind_group_id
293 ( p_dim_bmap in number
294 , p_custom_output in out nocopy bis_query_attributes_tbl
295 , p_column1 in varchar2 default null
296 , p_column2 in varchar2 default null
297 , p_column3 in varchar2 default null
298 , p_column4 in varchar2 default null
299 , p_column5 in varchar2 default null
300 , p_column6 in varchar2 default null
301 , p_column7 in varchar2 default null
302 , p_column8 in varchar2 default null
303 );
304
305 -- this function returns a snippet of where clause code that can be included
306 -- in your MV inline view text
307 -- p_fact_alias: the alias you have assigned to your fact table/MV, typically
308 -- the will be "fact"
309 -- p_org_id: the value of the user selected org id. the actual value is not
310 -- important. if the user has selected an value (other than All)
311 -- then this function will return null as the main query will
312 -- filter on organization_id anyway.
313 function get_sec_where_clause
314 ( p_fact_alias in varchar2
315 , p_org_id in varchar2
316 )
317 return varchar2;
318
319 -- this function returns a comments (/* */) dump of the
320 -- contents of p_param, showing parameter name, value, id,
321 -- dimension etc.
322 -- this can be used during development to validate that the
323 -- parameters PMV is passing are as you would expect.
324 -- note: the function should not be included in any code
325 -- released as it will cause a unique sql statement to be
326 -- generated each time, it is for development testing only.
327 function dump_parameters
328 ( p_param in bis_pmv_page_parameter_tbl )
329 return varchar2;
330
331 -- this function allows you to create a second (or Nth) view by
332 -- in a report, for example asset downtime view by asset plus
333 -- criticality and returns the select list column for you to use.
334 -- p_view_by: the dimension level using it's logical name
335 -- p_dimension_tbl: this is x_dimension_tbl returned from
336 -- register_dimension_levels
337 -- p_join_tbl: this is x_join_tbl returned from process_parameters
338 function add_view_by
339 ( p_view_by in varchar2
340 , p_dimension_tbl in t_dimension_tbl
341 , p_join_tbl in out nocopy poa_dbi_util_pkg.poa_dbi_join_tbl
342 )
343 return varchar2;
344
345 -- this function returns the select list element for rendering the
346 -- drill to detail URL for work order read only.
347 -- by default it assumes that:
348 -- organization_id will be available as oset.organization_id and
349 -- wip_entity_id will be available as oset.work_order_id
350 -- you need to pass a value for p_org_id_column and p_wo_id_column
351 -- if you have something different.
352 function get_drill_detail
353 ( p_column_alias in varchar2
354 , p_org_id_column in varchar2 default null
355 , p_wo_id_column in varchar2 default null
356 )
357 return varchar2;
358
359
360 -- this function returns the portion of the inner query select stmt
361 -- that can be used by window queries. It is based on the code used
362 -- in poa_dbi_template_pkg.get_group_and_sel_clause which is
363 -- not public.
364 function get_inner_select_col
365 (p_join_tables in poa_dbi_util_pkg.poa_dbi_join_tbl
366 ) return varchar2;
367
368
369 --This function is to return the
370 --asset group curresponding to the
371 --asset number
372 -- input parameters are the p_view_by (asset_number)
373 -- and the dimension table to locate the asset_number dimension parameters
374 function add_asset_group_column
375 ( p_view_by in varchar2
376 , p_dimension_tbl in t_dimension_tbl
377 )
378 return varchar2;
379
380
381 end isc_maint_rpt_util_pkg;
[Home] [Help]