1 package biv_dbi_tmpl_util as
2 /* $Header: bivsrvrutls.pls 120.0 2005/05/25 10:58:40 appldev noship $ */
3
4 -- these are the codes for the dimension levels
5
6 g_REQUEST_TYPE varchar2(50) := 'BIV_REQUEST_TYPE+REQUESTTYPE';
7 g_CATEGORY varchar2(50) := 'ITEM+ENI_ITEM_VBH_CAT';
8 g_PRODUCT varchar2(50) := 'ITEM+ENI_ITEM';
9 g_SEVERITY varchar2(50) := 'SEVERITY+SEVERITY';
10 g_STATUS varchar2(50) := 'STATUS+STATUS';
11 g_CHANNEL varchar2(50) := 'BIV_CHANNEL+CHANNEL';
12 g_RESOLUTION varchar2(50) := 'RESOLUTION+RESOLUTION';
13 g_CUSTOMER varchar2(50) := 'CUSTOMER+PROSPECT';
14 g_ASSIGNMENT varchar2(50) := 'ORGANIZATION+JTF_ORG_SUPPORT_GROUP';
15 g_AGING varchar2(50) := 'BUCKET_AGING+SERVICE_DISTRIBUTION';
16 g_BACKLOG_TYPE varchar2(50) := 'BACKLOG_TYPE+BACKLOG_TYPE';
17 g_RES_STATUS varchar2(50) := 'BIV_RES_STATUS+RES_STATUS';
18
19 -- get_period_type function returns the value of the parameter value
20 -- for period_type as poa_dbi_util_pkg.get_parameter_values
21 -- does not understand rolling periods yet.
22 --
23 -- when poa_dbi_util_pkg.get_parameter_values is updated to handle
24 -- rolling periods this function will not be needed.
25 --
26 function get_period_type
27 ( p_param in bis_pmv_page_parameter_tbl )
28 return varchar2;
29
30 -- this is biv specific and should remain in this package.
31 procedure process_parameters
32 ( p_param in bis_pmv_page_parameter_tbl
33 , p_report_type in varchar2 -- 'ACTIVITY','CLOSED','BACKLOG','BACKLOG_AGE'
34 , p_trend in varchar2
35 , x_view_by out nocopy varchar2
36 , x_view_by_col_name out nocopy varchar2
37 , x_comparison_type out nocopy varchar2
38 , x_xtd out nocopy varchar2
39 , x_where_clause out nocopy varchar2
40 , x_mv out nocopy varchar2
41 , x_join_tbl out nocopy poa_DBI_UTIL_PKG.poa_dbi_join_tbl
42 , x_as_of_date out nocopy date
43 );
44
45 -- this is biv specific and should remain in this package.
46 function get_view_by_col_name
47 ( p_dim_name in varchar2 )
48 return varchar2;
49
50 -- get_category_drill_down function returns the select statement
51 -- column needed to perform drill down on product category to either
52 -- the next level down in the hierarchy or to view by product.
53 -- if p_view_by is not product category then null is returned.
54 -- this is biv specific and should remain in this package.
55 function get_category_drill_down
56 ( p_view_by_name in varchar2
57 , p_function_name in varchar2
58 , p_column_alias in varchar2 default 'BIV_ATTRIBUTE4' )
59 return varchar2;
60
61 -- get_backlog_type function returns the value of the parameter value
62 -- for backlog type as poa_dbi_util_pkg.get_parameter_values
63 -- only returns whether or not it is set and we don't want
64 -- the join/where logic from biv_dbi_template_pkg.status_sql
65 -- anyway.
66 -- this is biv specific and should remain in this package
67 function get_backlog_type
68 ( p_param in bis_pmv_page_parameter_tbl )
69 return varchar2;
70
71 -- get_bucket_outer_query function returns the columns for the
72 -- outer select statement for the buckets based on the
73 -- bucket short name
74 -- if p_backlog_col is not null then the columns will be
75 -- returned as % of backlog (distribution)
76 -- this is biv specific and should remain in this package.
77 function get_bucket_outer_query
78 ( p_bucket_rec in bis_bucket_pub.bis_bucket_rec_type
79 , p_column_name_base in varchar2
80 , p_alias_base in varchar2
81 , p_total_flag in varchar2 default 'N'
82 , p_backlog_col in varchar2 default null
83 )
84 return varchar2;
85
86 -- add_bucket_inner_query procedure adds the bucket columns to
87 -- the inner query for the buckets based on the bucket short name.
88 -- this is biv specific and should remain in this package.
89 procedure add_bucket_inner_query
90 ( p_short_name in varchar2
91 , p_col_tbl in out nocopy poa_DBI_UTIL_PKG.poa_dbi_col_tbl
92 , p_col_name in varchar2
93 , p_alias_name in varchar2
94 , p_grand_total in varchar2
95 , p_prior_code in varchar2
96 , p_to_date_type in varchar2
97 , x_bucket_rec out nocopy bis_bucket_pub.bis_bucket_rec_type
98 );
99
100 -- this is a wrapper to poa_dbi_util_pkg.change_clause.
101 -- this is biv specific and should remain in this package.
102 function change_column
103 ( p_current_column in varchar2
104 , p_prior_column in varchar2
105 , p_column_alias in varchar2
106 , p_percent in varchar2 default 'Y'
107 ) return varchar2;
108
109 -- this is a wrapper to poa_dbi_util_pkg.rate_clause.
110 -- this is biv specific and should remain in this package.
111 function rate_column
112 ( p_numerator in varchar2
113 , p_denominator in varchar2
114 , p_column_alias in varchar2
115 , p_percent in varchar2 default 'Y'
116 ) return varchar2;
117
118 -- dump_parameters function returns as a comment block the
119 -- contents of the parameters provided by PMV
120 -- note: it should only be called in debugging situations.
121 -- this is biv specific and should remain in this package.
122 function dump_parameters
123 ( p_param in bis_pmv_page_parameter_tbl )
124 return varchar2;
125
126 -- dump_binds function returns as a comment block the
127 -- contents of the bind variables provided by PMV and product team
128 -- note: it should only be called in debugging situations.
129 -- this is biv specific and should remain in this package.
130 function dump_binds
131 ( p_custom_output in bis_query_attributes_tbl)
132 return varchar2;
133
134 -- override_order_by procedure replaces the default order by
135 -- clause with one based on importance level when view by
136 -- severity and order by severity or when bucket id when view by
137 -- aging and order by aging.
138 -- this is biv specific and should remain in this package.
139 procedure override_order_by
140 ( p_view_by in varchar2
141 , p_param in bis_pmv_page_parameter_tbl
142 , p_stmt in out nocopy varchar2
143 );
144
145 -- get_balance_fact function returns the MV name to check the
146 -- last refresh details for based on the text of the mv used by
147 -- the backlog query
148 function get_balance_fact
149 ( p_mv in varchar2
150 )
151 return varchar2;
152
153 -- get_trace_file_name function returns as a comment the trace
154 -- file name that will contain the report query.
155 -- this is biv specific and should remain in this package.
156 function get_trace_file_name
157 return varchar2;
158
159 -- drill_detail function returns the drill to detail url for
160 -- either a backlog or closed column.
161 function drill_detail
162 ( p_function_name in varchar2
163 , p_bucket_number in number
164 , p_bucket_name in varchar2
165 , p_base_alias in varchar2
166 ) return varchar2;
167
168
169 -- bucket_detail_drill function returns the drill to detail urls for
170 -- either a backlog or closed distribution columns using drill_detail.
171 function bucket_detail_drill
172 ( p_function_name in varchar2
173 , p_bucket_rec in bis_bucket_pub.bis_bucket_rec_type
174 , p_base_alias in varchar2
175 ) return varchar2;
176
177 -- get_detail_page_function procedure returns the name of function
178 -- to display the details and the name of the parameter for the SR ID.
179 procedure get_detail_page_function
180 ( x_function_name out nocopy varchar2
181 , x_sr_id_parameter out nocopy varchar2
182 );
183
184 -- bind_yes_no procedure returns the translated values for Y and N
185 -- as bind variable values
186 procedure bind_yes_no
187 ( p_yes in varchar2
188 , p_no in varchar2
189 , p_custom_output in out nocopy bis_query_attributes_tbl
190 );
191
192 -- bind_low_high procedure returns the low and high values for bucket
193 -- as bind variable values
194 procedure bind_low_high
195 ( p_param in bis_pmv_page_parameter_tbl
196 , p_short_name in varchar2
197 , p_low in varchar2
198 , p_high in varchar2
199 , p_custom_output in out nocopy bis_query_attributes_tbl
200 );
201
202 -- process_parameters procedure is a wrapper to the other
203 -- process_parameters specifically for detail report queries
204 procedure process_parameters
205 ( p_param in bis_pmv_page_parameter_tbl
206 , p_report_type in varchar2 -- 'BACKLOG_DETAIL', 'CLOSED_DETAIL'
207 , x_where_clause out nocopy varchar2
208 , x_xtd out nocopy varchar2
209 , x_mv out nocopy varchar2
210 , x_join_from out nocopy varchar2
211 , x_join_where out nocopy varchar2
212 , x_join_tbl out nocopy poa_DBI_UTIL_PKG.poa_dbi_join_tbl
213 , x_as_of_date out nocopy date
214 );
215
216 -- get_order_by function returns the order by parameter
217 function get_order_by
218 ( p_param in bis_pmv_page_parameter_tbl )
219 return varchar2;
220
221 -- bind_age_dates procedure returns the current and prior date
222 -- values as bind variables for backlog aging
223 procedure bind_age_dates
224 ( p_param in bis_pmv_page_parameter_tbl
225 , p_current_name in varchar2
226 , p_prior_name in varchar2
227 , p_custom_output in out nocopy bis_query_attributes_tbl
228 );
229
230 -- get_grp_id function returns the decoded grouping set grp_id
231 -- to be used. assumes:
232 -- 6 = Resolution, 5 = Channel, 4 = Status, 3 = Assignment Grp,
233 -- 2 = Customer, 1 = Product, any combination = 0
234 function get_grp_id
235 ( p_bmap in number )
236 return number;
237
238
239 end biv_dbi_tmpl_util;
[Home] [Help]