[Home] [Help]
PACKAGE BODY: APPS.ISC_MAINT_RPT_UTIL_PKG
Source
1 package body isc_maint_rpt_util_pkg
2 /* $Header: iscmaintrptutilb.pls 120.2 2006/02/03 03:23:22 nbhamidi noship $ */
3 as
4
5 G_ORGANIZATION_BMAP constant number := 1;
6 G_DEPARTMENT_BMAP constant number := 2;
7 G_ASSET_GROUP_BMAP constant number := 4;
8 G_ASSET_NUMBER_BMAP constant number := 8;
9 G_ACTIVITY_BMAP constant number := 16;
10 G_COST_CATEGORY_BMAP constant number := 32;
11 G_COST_ELEMENT_BMAP constant number := 64;
12 G_WORK_ORDER_TYPE_BMAP constant number := 128;
13 G_WORK_ORDER_STATUS_BMAP constant number := 256;
14 G_WIP_ENTITIES_BMAP constant number := 512;
15 G_LATE_CMPL_AGING_BMAP constant number := 1024;
16 G_PAST_DUE_AGING_BMAP constant number := 2048;
17 G_ASSET_CATEGORY_BMAP constant number := 4096;
18 G_ASSET_CRITICALITY_BMAP constant number := 8192;
19 G_REQUEST_TYPE_BMAP constant number := 16384;
20 G_REQ_CMPL_AGING_BMAP constant number := 32768;
21 G_REQUESTS_BMAP constant number := 65536;
22 G_REQUEST_SEVERITIES_BMAP constant number := 131072;
23 G_RESOURCE_BMAP constant number := 262144;
24
25 -- ---------------------------------------------------------------------- --
26 -- P R I V A T E P R O C E D U R E S A N D F U N C T I O N S --
27 -- ---------------------------------------------------------------------- --
28
29 -- this is a private procedure called from register_dimension_levels
30 -- that adds a single dimenision level to x_dimension_tbl and optionally
31 -- x_dim_map (based on p_filter_flag)
32 procedure init_dim_map
33 ( p_dimension in varchar2
34 , p_filter_flag in varchar2
35 , x_dimension_tbl in out nocopy t_dimension_tbl
36 , x_dim_map in out nocopy poa_dbi_util_pkg.poa_dbi_dim_map
37 )
38 is
39
40 l_dimension_rec t_dimension_rec;
41 l_dim_rec poa_dbi_util_pkg.poa_dbi_dim_rec;
42
43 begin
44
45 if p_dimension = G_ORGANIZATION then
46
47 l_dimension_rec.dim_bmap := G_ORGANIZATION_BMAP;
48 l_dimension_rec.dim_table_name :=
49 '(select organization_id id, name value ' ||
50 'from hr_all_organization_units_tl ' ||
51 'where language = userenv(''LANG''))';
52 l_dimension_rec.dim_table_alias := 'v1';
53 l_dimension_rec.dim_outer_join := 'N';
54 l_dimension_rec.dim_col_name1 := 'id';
55 l_dimension_rec.oset_col_name1 := 'organization_id';
56 l_dimension_rec.viewby_col_name := 'value';
57 l_dimension_rec.viewby_id_col_name := 'id';
58 l_dimension_rec.fact_filter_col_name := 'organization_id_c';
59 x_dimension_tbl(G_ORGANIZATION) := l_dimension_rec;
60
61 elsif p_dimension = G_DEPARTMENT then
62
63 l_dimension_rec.dim_bmap := G_DEPARTMENT_BMAP;
64 l_dimension_rec.dim_table_name := 'eni_resource_department_v';
65 l_dimension_rec.dim_table_alias := 'v2';
66 l_dimension_rec.dim_outer_join := 'Y';
67 l_dimension_rec.dim_col_name1 := 'department_id';
68 l_dimension_rec.oset_col_name1 := 'department_id';
69 l_dimension_rec.dim_col_name2 := 'organization_id';
70 l_dimension_rec.oset_col_name2 := 'organization_id';
71 l_dimension_rec.viewby_col_name := 'value';
72 l_dimension_rec.viewby_id_col_name := 'id';
73 l_dimension_rec.viewby_id_unassigned := '''-1''';
74 l_dimension_rec.fact_filter_col_name := 'department_id_c';
75 x_dimension_tbl(G_DEPARTMENT) := l_dimension_rec;
76
77 elsif p_dimension = G_ASSET_GROUP then /* modified to make asset group independent of the org */
78
79 l_dimension_rec.dim_bmap := G_ASSET_GROUP_BMAP;
80 l_dimension_rec.dim_table_name :=
81 '(select ' ||
82 'star.inventory_item_id id ' ||
83 ', msi.concatenated_segments value ' ||
84 'from ' ||
85 'mtl_system_items_kfv msi, ' ||
86 'ENI_OLTP_ITEM_STAR star ' ||
87 'where msi.eam_item_type in (1,3) ' ||
88 'and msi.inventory_item_id = star.inventory_item_id ' ||
89 'and msi.organization_id = star.organization_id ' ||
90 ' group by star.inventory_item_id, msi.concatenated_segments)';
91 l_dimension_rec.dim_table_alias := 'v3';
92 l_dimension_rec.dim_outer_join := 'Y';
93 l_dimension_rec.dim_col_name1 := 'id';
94 l_dimension_rec.oset_col_name1 := 'asset_group_id';
95 l_dimension_rec.viewby_col_name := 'value';
96 l_dimension_rec.viewby_id_col_name := 'id';
97 l_dimension_rec.viewby_id_unassigned := '-1';
98 l_dimension_rec.fact_filter_col_name := 'asset_group_id_c';
99 x_dimension_tbl(G_ASSET_GROUP) := l_dimension_rec;
100
101 elsif p_dimension = G_ASSET_NUMBER then /* modified the asset_number to contain the instance_id */
102
103 l_dimension_rec.dim_bmap := G_ASSET_NUMBER_BMAP;
104 l_dimension_rec.dim_table_name :=
105 '(select ' ||
106 'cii.instance_id id ' ||
107 ', cii.instance_number value ' ||
108 ', cii.serial_number ' ||
109 ', CII.LAST_VLD_ORGANIZATION_ID ' ||
110 ', MSI.INVENTORY_ITEM_ID ' ||
111 ', MSI.CONCATENATED_SEGMENTS ASSET_GROUP '||
112 ', MP.MAINT_ORGANIZATION_ID ' ||
113 'from ' ||
114 'mtl_system_items_kfv msi ' ||
115 ', CSI_ITEM_INSTANCES CII ' ||
116 ', MTL_PARAMETERS MP '||
117 'where msi.eam_item_type in (1,3) ' ||
118 'and serial_number_control_code <> 1 '||
119 'and msi.inventory_item_id = cii.inventory_item_id '||
120 'and msi.organization_id = cii.last_vld_organization_id '||
121 'and msi.organization_id = mp.organization_id) ';
122 l_dimension_rec.dim_table_alias := 'v4';
123 l_dimension_rec.dim_outer_join := 'Y';
124 l_dimension_rec.dim_col_name1 := 'id';
125 l_dimension_rec.oset_col_name1 := 'instance_id';
126 l_dimension_rec.viewby_col_name := 'value';
127 l_dimension_rec.viewby_id_col_name := 'id';
128 l_dimension_rec.viewby_id_unassigned := '-1';
129 l_dimension_rec.fact_filter_col_name := 'instance_id_c';
130 x_dimension_tbl(G_ASSET_NUMBER) := l_dimension_rec;
131
132 elsif p_dimension = G_ACTIVITY then
133
134 l_dimension_rec.dim_bmap := G_ACTIVITY_BMAP;
135 l_dimension_rec.dim_table_name := 'biv_maint_activity_lvl_v';
136 l_dimension_rec.dim_table_name :=
137 '(select ' ||
138 'kfv.inventory_item_id || ''-'' || kfv.organization_id id ' ||
139 ', kfv.concatenated_segments || '' ('' || mp.organization_code || '')'' value ' ||
140 ', tl.description ' ||
141 ', kfv.inventory_item_id activity_id ' ||
142 ', kfv.organization_id ' ||
143 'from ' ||
144 'mtl_system_items_kfv kfv ' ||
145 ', mtl_system_items_tl tl ' ||
146 ', mtl_parameters mp ' ||
147 'where kfv.eam_item_type = 2 ' ||
148 'and kfv.inventory_item_id = tl.inventory_item_id(+) ' ||
149 'and kfv.organization_id = tl.organization_id(+) ' ||
150 'and tl.language (+) = userenv(''LANG'') ' ||
151 'and kfv.organization_id = mp.organization_id)';
152 l_dimension_rec.dim_table_alias := 'v5';
153 l_dimension_rec.dim_outer_join := 'Y';
154 l_dimension_rec.dim_col_name1 := 'activity_id';
155 l_dimension_rec.oset_col_name1 := 'activity_id';
156 l_dimension_rec.dim_col_name2 := 'organization_id';
157 l_dimension_rec.oset_col_name2 := 'organization_id';
158 l_dimension_rec.viewby_col_name := 'value';
159 l_dimension_rec.viewby_id_col_name := 'id';
160 l_dimension_rec.viewby_id_unassigned := '''-1''';
161 l_dimension_rec.fact_filter_col_name := 'activity_id_c';
162 x_dimension_tbl(G_ACTIVITY) := l_dimension_rec;
163
164 elsif p_dimension = G_COST_CATEGORY then
165
166 l_dimension_rec.dim_bmap := G_COST_CATEGORY_BMAP;
167 l_dimension_rec.dim_table_name := 'biv_maint_cst_category_lvl_v';
168 l_dimension_rec.dim_table_alias := 'v6';
169 l_dimension_rec.dim_outer_join := 'N';
170 l_dimension_rec.dim_col_name1 := 'id';
171 l_dimension_rec.oset_col_name1 := 'maint_cost_category';
172 l_dimension_rec.viewby_col_name := 'value';
173 l_dimension_rec.viewby_id_col_name := 'id';
174 l_dimension_rec.fact_filter_col_name := 'maint_cost_category';
175 x_dimension_tbl(G_COST_CATEGORY) := l_dimension_rec;
176
177 elsif p_dimension = G_WORK_ORDER_TYPE then
178
179 l_dimension_rec.dim_bmap := G_WORK_ORDER_TYPE_BMAP;
180 l_dimension_rec.dim_table_name := 'biv_maint_wk_order_type_lvl_v';
181 l_dimension_rec.dim_table_alias := 'v7';
182 l_dimension_rec.dim_outer_join := 'N';
183 l_dimension_rec.dim_col_name1 := 'id';
184 l_dimension_rec.oset_col_name1 := 'work_order_type';
185 l_dimension_rec.viewby_col_name := 'value';
186 l_dimension_rec.viewby_id_col_name := 'id';
187 l_dimension_rec.fact_filter_col_name := 'work_order_type';
188 x_dimension_tbl(G_WORK_ORDER_TYPE) := l_dimension_rec;
189
190 elsif p_dimension = G_WORK_ORDER_STATUS then /* modified to contain the view definition which contains the system
191 and user defined work order statues */
192 l_dimension_rec.dim_bmap := G_WORK_ORDER_STATUS_BMAP;
193 l_dimension_rec.dim_table_name :='biv_maint_wo_status_lvl_v'; /* bug 5002342 */
194 l_dimension_rec.dim_table_alias := 'v8';
195 l_dimension_rec.dim_outer_join := 'N';
196 l_dimension_rec.dim_col_name1 := 'id';
197 l_dimension_rec.oset_col_name1 := 'user_defined_status_id';
198 l_dimension_rec.viewby_col_name := 'value';
199 l_dimension_rec.viewby_id_col_name := 'id';
200 l_dimension_rec.fact_filter_col_name := 'user_defined_status_id';
201 x_dimension_tbl(G_WORK_ORDER_STATUS) := l_dimension_rec;
202
203 elsif p_dimension = G_WIP_ENTITIES then
204
205 l_dimension_rec.dim_bmap := G_WIP_ENTITIES_BMAP;
206 l_dimension_rec.dim_table_name := 'wip_entities';
207 l_dimension_rec.dim_table_alias := 'v9';
208 l_dimension_rec.dim_outer_join := 'N';
209 l_dimension_rec.dim_col_name1 := 'wip_entity_id';
210 l_dimension_rec.oset_col_name1 := 'work_order_id';
211 l_dimension_rec.viewby_col_name := 'wip_entity_name';
212 l_dimension_rec.viewby_id_col_name := 'null';
213 l_dimension_rec.fact_filter_col_name := 'null';
214 x_dimension_tbl(G_WIP_ENTITIES) := l_dimension_rec;
215
216 elsif p_dimension = G_LATE_CMPL_AGING then
217
218 l_dimension_rec.dim_bmap := G_LATE_CMPL_AGING_BMAP;
219 l_dimension_rec.dim_table_name := 'biv_maint_late_comp_aging_v';
220 l_dimension_rec.dim_table_alias := 'v10';
221 l_dimension_rec.dim_outer_join := 'R';
222 l_dimension_rec.dim_col_name1 := 'id';
223 l_dimension_rec.oset_col_name1 := 'bucket_num';
224 l_dimension_rec.viewby_col_name := 'value';
225 l_dimension_rec.viewby_id_col_name := 'id';
226 l_dimension_rec.viewby_id_unassigned := '''-1''';
227 l_dimension_rec.fact_filter_col_name := 'bucket_num';
228 x_dimension_tbl(G_LATE_CMPL_AGING) := l_dimension_rec;
229
230 elsif p_dimension = G_PAST_DUE_AGING then
231
232 l_dimension_rec.dim_bmap := G_PAST_DUE_AGING_BMAP;
233 l_dimension_rec.dim_table_name := 'BIV_MAINT_PAST_DUE_AGING_V';
234 l_dimension_rec.dim_table_alias := 'v11';
235 l_dimension_rec.dim_outer_join := 'R';
236 l_dimension_rec.dim_col_name1 := 'id';
237 l_dimension_rec.oset_col_name1 := 'bucket_num';
238 l_dimension_rec.viewby_col_name := 'value';
239 l_dimension_rec.viewby_id_col_name := 'id';
240 l_dimension_rec.viewby_id_unassigned := '''-1''';
241 l_dimension_rec.fact_filter_col_name := 'bucket_num';
242 x_dimension_tbl(G_PAST_DUE_AGING) := l_dimension_rec;
243
244 elsif p_dimension = G_ASSET_CATEGORY then
245
246 l_dimension_rec.dim_bmap := G_ASSET_CATEGORY_BMAP;
247 l_dimension_rec.dim_table_name := 'BIV_MAINT_ASSET_CATEGORY_LVL_V';
248 l_dimension_rec.dim_table_alias := 'v12';
249 l_dimension_rec.dim_outer_join := 'Y';
250 l_dimension_rec.dim_col_name1 := 'id';
251 l_dimension_rec.oset_col_name1 := 'category_id';
252 l_dimension_rec.viewby_col_name := 'value';
253 l_dimension_rec.viewby_id_col_name := 'id';
254 l_dimension_rec.viewby_id_unassigned := '''-1''';
255 l_dimension_rec.fact_filter_col_name := 'category_id';
256 x_dimension_tbl(G_ASSET_CATEGORY) := l_dimension_rec;
257
258 elsif p_dimension = G_ASSET_CRITICALITY then
259
260 l_dimension_rec.dim_bmap := G_ASSET_CRITICALITY_BMAP;
261 l_dimension_rec.dim_table_name := 'BIV_MAINT_ASSET_CRITICAL_LVL_V';
262 l_dimension_rec.dim_table_alias := 'v13';
266 l_dimension_rec.viewby_col_name := 'value';
263 l_dimension_rec.dim_outer_join := 'Y';
264 l_dimension_rec.dim_col_name1 := 'id';
265 l_dimension_rec.oset_col_name1 := 'asset_criticality_code';
267 l_dimension_rec.viewby_id_col_name := 'id';
268 l_dimension_rec.viewby_id_unassigned := '''-1''';
269 l_dimension_rec.fact_filter_col_name := 'asset_criticality_code';
270 x_dimension_tbl(G_ASSET_CRITICALITY) := l_dimension_rec;
271
272 elsif p_dimension = G_REQUEST_TYPE then
273
274 l_dimension_rec.dim_bmap := G_REQUEST_TYPE_BMAP;
275 l_dimension_rec.dim_table_name := 'BIV_MAINT_REQUEST_TYPE_LVL_V';
276 l_dimension_rec.dim_table_alias := 'v14';
277 l_dimension_rec.dim_outer_join := 'N';
278 l_dimension_rec.dim_col_name1 := 'id';
279 l_dimension_rec.oset_col_name1 := 'request_type';
280 l_dimension_rec.viewby_col_name := 'value';
281 l_dimension_rec.viewby_id_col_name := 'id';
282 l_dimension_rec.viewby_id_unassigned := '''-1''';
283 l_dimension_rec.fact_filter_col_name := 'request_type';
284 x_dimension_tbl(G_REQUEST_TYPE) := l_dimension_rec;
285
286 elsif p_dimension = G_REQ_CMPL_AGING then
287
288 l_dimension_rec.dim_bmap := G_REQ_CMPL_AGING_BMAP;
289 l_dimension_rec.dim_table_name := 'BIV_MAINT_REQ_COMP_AGING_V';
290 l_dimension_rec.dim_table_alias := 'v15';
291 l_dimension_rec.dim_outer_join := 'R';
292 l_dimension_rec.dim_col_name1 := 'id';
293 l_dimension_rec.oset_col_name1 := 'bucket_num';
294 l_dimension_rec.viewby_col_name := 'value';
295 l_dimension_rec.viewby_id_col_name := 'id';
296 l_dimension_rec.viewby_id_unassigned := '''-1''';
297 l_dimension_rec.fact_filter_col_name := 'bucket_num';
298 x_dimension_tbl(G_REQ_CMPL_AGING) := l_dimension_rec;
299
300 elsif p_dimension = G_REQUESTS then
301
302 l_dimension_rec.dim_bmap := G_REQUESTS_BMAP;
303 l_dimension_rec.dim_table_name :=
304 '(select ''2'' request_type' ||
305 ', incident_id maint_request_id' ||
306 ', summary description ' ||
307 'from cs_incidents_all_tl ' ||
308 'where LANGUAGE = userenv(''LANG'') ' ||
309 'union all ' ||
310 'select ''1''' ||
311 ', work_request_id' ||
312 ', description ' ||
313 'from wip_eam_work_requests ' ||
314 ')';
315 l_dimension_rec.dim_table_alias := 'v16';
316 l_dimension_rec.dim_outer_join := 'N';
317 l_dimension_rec.dim_col_name1 := 'request_type';
318 l_dimension_rec.oset_col_name1 := 'request_type';
319 l_dimension_rec.dim_col_name2 := 'maint_request_id';
320 l_dimension_rec.oset_col_name2 := 'maint_request_id';
321 l_dimension_rec.viewby_col_name := 'null';
322 l_dimension_rec.viewby_id_col_name := 'null';
323 l_dimension_rec.fact_filter_col_name := 'null';
324 x_dimension_tbl(G_REQUESTS) := l_dimension_rec;
325
326 elsif p_dimension = G_REQUEST_SEVERITIES then
327
328 l_dimension_rec.dim_bmap := G_REQUEST_SEVERITIES_BMAP;
329 l_dimension_rec.dim_table_name :=
330 '(select ''1'' request_type' ||
331 ', lookup_code request_severity_id' ||
332 ', meaning name ' ||
333 'from fnd_lookup_values ' ||
334 'where lookup_type = ''WIP_EAM_ACTIVITY_PRIORITY'' ' ||
335 'and language = userenv(''LANG'') ' ||
336 'and view_application_id = 700 ' ||
337 'and security_group_id = ' ||
338 'fnd_global.lookup_security_group(lookup_type,view_application_id) ' ||
339 'union all ' ||
340 'select ''2''' ||
341 ', to_char(incident_severity_id)' ||
342 ', name ' ||
343 'from cs_incident_severities_tl ' ||
344 'where LANGUAGE = userenv(''LANG'') ' ||
345 ')';
346 l_dimension_rec.dim_table_alias := 'v17';
347 l_dimension_rec.dim_outer_join := 'Y';
348 l_dimension_rec.dim_col_name1 := 'request_type';
349 l_dimension_rec.oset_col_name1 := 'request_type';
350 l_dimension_rec.dim_col_name2 := 'request_severity_id';
351 l_dimension_rec.oset_col_name2 := 'request_severity_id';
352 l_dimension_rec.viewby_col_name := 'null';
353 l_dimension_rec.viewby_id_col_name := 'null';
354 l_dimension_rec.fact_filter_col_name := 'null';
355 x_dimension_tbl(G_REQUEST_SEVERITIES) := l_dimension_rec;
356
357 elsif p_dimension = G_RESOURCE then
358
359 l_dimension_rec.dim_bmap := G_RESOURCE_BMAP;
360 l_dimension_rec.dim_table_name := 'ENI_RESOURCE_V';
361 l_dimension_rec.dim_table_alias := 'v18';
362 l_dimension_rec.dim_outer_join := 'N';
363 l_dimension_rec.dim_col_name1 := 'resource_id';
364 l_dimension_rec.oset_col_name1 := 'resource_id';
365 l_dimension_rec.dim_col_name2 := 'department_id';
366 l_dimension_rec.oset_col_name2 := 'department_id';
367 l_dimension_rec.dim_col_name3 := 'organization_id';
368 l_dimension_rec.oset_col_name3 := 'organization_id';
369 l_dimension_rec.viewby_col_name := 'value';
370 l_dimension_rec.viewby_id_col_name := 'id';
371 l_dimension_rec.viewby_id_unassigned := '''-1''';
372 l_dimension_rec.fact_filter_col_name := 'resource_id_c';
373 x_dimension_tbl(G_RESOURCE) := l_dimension_rec;
374 end if;
375
376 if p_filter_flag <> 'N' then
377 l_dim_rec.col_name := l_dimension_rec.fact_filter_col_name;
378 l_dim_rec.bmap := l_dimension_rec.dim_bmap;
379 x_dim_map(p_dimension) := l_dim_rec;
380 end if;
381
382 end init_dim_map;
383
384 -- this is a private procedure that adds the bind variable
385 -- &ISC_UNASSIGNED and the appropriate (translated) text.
386 -- it is called from process_parameters
387 procedure bind_unassigned
388 ( p_custom_output in out nocopy bis_query_attributes_tbl
389 ) is
390
391 l_custom_rec BIS_QUERY_ATTRIBUTES;
392
396 p_custom_output := bis_query_attributes_tbl();
393 begin
394
395 if p_custom_output is null then
397 end if;
398
399 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
400
401 l_custom_rec.attribute_name := '&ISC_UNASSIGNED' ;
402 l_custom_rec.attribute_value := fnd_message.get_string('BIS','BIS_UNASSIGNED');
403 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
404 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
405 p_custom_output.extend;
406 p_custom_output(p_custom_output.count) := l_custom_rec;
407
408 end bind_unassigned;
409
410 -- this is a private function that returns a snippet of select list
411 -- code to manage the calculation of the grp_id bitmap value.
412 -- it is called from bind_group_id
413 function add_bin_column
414 ( p_column in varchar2
415 )
416 return varchar2
417 is
418 begin
419 if p_column is null then
420 return '';
421 end if;
422 return ', ' ||
423 case
424 when p_column = G_ASSET_GROUP then
425 'case when bitand(:p_bmap,'||G_ASSET_GROUP_BMAP||') = '||G_ASSET_GROUP_BMAP||' or ' ||
426 'bitand(:p_bmap,'||G_ASSET_NUMBER_BMAP||') = '||G_ASSET_NUMBER_BMAP||' then 0 else 1 end'
427 when p_column = G_ASSET_NUMBER then
428 'case when bitand(:p_bmap,'||G_ASSET_NUMBER_BMAP||') = '||G_ASSET_NUMBER_BMAP||' then 0 else 1 end'
429 when p_column = G_DEPARTMENT then
430 'case when bitand(:p_bmap,'||G_DEPARTMENT_BMAP||') = '||G_DEPARTMENT_BMAP||' or ' ||
431 'bitand(:p_bmap,'||G_RESOURCE_BMAP||') = '||G_RESOURCE_BMAP||' then 0 else 1 end'
432 when p_column = G_ACTIVITY then
433 'case when bitand(:p_bmap,'||G_ACTIVITY_BMAP||') = '||G_ACTIVITY_BMAP||' then 0 else 1 end'
434 when p_column = G_COST_CATEGORY then
435 'case when bitand(:p_bmap,'||G_COST_CATEGORY_BMAP||') = '||G_COST_CATEGORY_BMAP||' then 0 else 1 end'
436 when p_column = G_WORK_ORDER_TYPE then
437 'case when bitand(:p_bmap,'||G_WORK_ORDER_TYPE_BMAP||') = '||G_WORK_ORDER_TYPE_BMAP||' then 0 else 1 end'
438 when p_column = G_WORK_ORDER_STATUS then
439 'case when bitand(:p_bmap,'||G_WORK_ORDER_STATUS_BMAP||') = '||G_WORK_ORDER_STATUS_BMAP||' then 0 else 1 end'
440 when p_column = G_ASSET_CRITICALITY then
441 'case when bitand(:p_bmap,'||G_ASSET_CRITICALITY_BMAP||') = '||G_ASSET_CRITICALITY_BMAP||' then 0 else 1 end'
442 when p_column = G_ASSET_CATEGORY then
443 'case when bitand(:p_bmap,'||G_ASSET_CATEGORY_BMAP||') = '||G_ASSET_CATEGORY_BMAP||' then 0 else 1 end'
444 when p_column = G_REQ_CMPL_AGING then
445 'case when bitand(:p_bmap,'||G_REQ_CMPL_AGING_BMAP||') = '||G_REQ_CMPL_AGING_BMAP||' then 0 else 1 end'
446 when p_column = G_RESOURCE then
447 'case when bitand(:p_bmap,'||G_RESOURCE_BMAP||') = '||G_RESOURCE_BMAP||' then 0 else 1 end'
448 else
449 ''
450 end;
451 end add_bin_column;
452
453 -- this is a private function, it returns a poa_dbi_join_tbl based
454 -- on p_dimension_tbl and the user selected view by (or null for a
455 -- non-viewby report).
456 -- it is called from process_parameters and detail_sql
457 function get_join_info
458 ( p_view_by in varchar2
459 , p_dimension_tbl in t_dimension_tbl
460 )
461 return poa_dbi_util_pkg.poa_dbi_join_tbl
462 is
463 l_join_rec poa_dbi_util_pkg.poa_dbi_join_rec;
464 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
465 l_dimension_rec t_dimension_rec;
466 l_key varchar2(200);
467 --
468 begin
469 -- reinitialize the join table
470 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
471
472 if p_view_by is not null then
473 if p_dimension_tbl.exists(p_view_by) then
474 l_dimension_rec := p_dimension_tbl(p_view_by);
475
476 l_join_rec.column_name := l_dimension_rec.dim_col_name1;
477 l_join_rec.table_name := l_dimension_rec.dim_table_name;
478 l_join_rec.table_alias := l_dimension_rec.dim_table_alias;
479 l_join_rec.fact_column := l_dimension_rec.oset_col_name1;
480 l_join_rec.dim_outer_join := l_dimension_rec.dim_outer_join;
481 l_join_rec.additional_where_clause := l_dimension_rec.additional_where_clause;
482
483 l_join_tbl.extend;
484 l_join_tbl(l_join_tbl.count) := l_join_rec;
485
486 if l_dimension_rec.dim_col_name2 is not null then
487 l_join_rec.column_name := l_dimension_rec.dim_col_name2;
488 l_join_rec.fact_column := l_dimension_rec.oset_col_name2;
489 l_join_rec.additional_where_clause := null;
490
491 l_join_tbl.extend;
492 l_join_tbl(l_join_tbl.count) := l_join_rec;
493
494 end if;
495
496 if l_dimension_rec.dim_col_name3 is not null then
497 l_join_rec.column_name := l_dimension_rec.dim_col_name3;
498 l_join_rec.fact_column := l_dimension_rec.oset_col_name3;
499 l_join_rec.additional_where_clause := null;
500
501 l_join_tbl.extend;
502 l_join_tbl(l_join_tbl.count) := l_join_rec;
503
504 end if;
505 end if;
506
507 else
508 l_key := p_dimension_tbl.first;
509 while l_key is not null loop
510 l_dimension_rec := p_dimension_tbl(l_key);
511
512 l_join_rec.column_name := l_dimension_rec.dim_col_name1;
513 l_join_rec.table_name := l_dimension_rec.dim_table_name;
514 l_join_rec.table_alias := l_dimension_rec.dim_table_alias;
515 l_join_rec.fact_column := l_dimension_rec.oset_col_name1;
516 l_join_rec.dim_outer_join := l_dimension_rec.dim_outer_join;
517 l_join_rec.additional_where_clause := l_dimension_rec.additional_where_clause;
518
519 l_join_tbl.extend;
523 l_join_rec.column_name := l_dimension_rec.dim_col_name2;
520 l_join_tbl(l_join_tbl.count) := l_join_rec;
521
522 if l_dimension_rec.dim_col_name2 is not null then
524 l_join_rec.fact_column := l_dimension_rec.oset_col_name2;
525 l_join_rec.additional_where_clause := null;
526
527 l_join_tbl.extend;
528 l_join_tbl(l_join_tbl.count) := l_join_rec;
529
530 end if;
531
532 if l_dimension_rec.dim_col_name3 is not null then
533 l_join_rec.column_name := l_dimension_rec.dim_col_name3;
534 l_join_rec.fact_column := l_dimension_rec.oset_col_name3;
535 l_join_rec.additional_where_clause := null;
536
537 l_join_tbl.extend;
538 l_join_tbl(l_join_tbl.count) := l_join_rec;
539
540 end if;
541
542 l_key := p_dimension_tbl.next(l_key);
543 end loop;
544
545 end if;
546
547 return l_join_tbl;
548
549 end get_join_info;
550
551 -- ---------------------------------------------------------------------- --
552 -- P U B L I C P R O C E D U R E S A N D F U N C T I O N S --
553 -- ---------------------------------------------------------------------- --
554
555 -- this is a public procedure, see package specification for it's
556 -- description
557 procedure bind_group_id
558 ( p_dim_bmap in number
559 , p_custom_output in out nocopy bis_query_attributes_tbl
560 , p_column1 in varchar2 default null
561 , p_column2 in varchar2 default null
562 , p_column3 in varchar2 default null
563 , p_column4 in varchar2 default null
564 , p_column5 in varchar2 default null
565 , p_column6 in varchar2 default null
566 , p_column7 in varchar2 default null
567 , p_column8 in varchar2 default null
568 ) is
569
570 l_custom_rec bis_query_attributes;
571 l_cur_hdl integer;
572 l_stmt varchar2(1000);
573 l_grp_id number;
574
575 begin
576
577 if p_custom_output is null then
578 p_custom_output := bis_query_attributes_tbl();
579 end if;
580
581 if p_column1 is null then
582 return;
583 end if;
584
585 l_stmt := 'select bin_to_num( 0' ||
586 add_bin_column(p_column1) ||
587 add_bin_column(p_column2) ||
588 add_bin_column(p_column3) ||
589 add_bin_column(p_column4) ||
590 add_bin_column(p_column5) ||
591 add_bin_column(p_column6) ||
592 add_bin_column(p_column7) ||
593 add_bin_column(p_column8) ||
594 ') grp_id from dual';
595
596 -- we use dbms_sql rather than execute immediate
597 -- as we don't know how many binds we need, dbms_sql
598 -- allows us to "bind by name"
599 l_cur_hdl := dbms_sql.open_cursor;
600 dbms_sql.parse(l_cur_hdl, l_stmt, dbms_sql.native);
601 dbms_sql.bind_variable(l_cur_hdl, 'p_bmap', p_dim_bmap);
602 dbms_sql.define_column(l_cur_hdl, 1, l_grp_id);
603 l_grp_id := dbms_sql.execute(l_cur_hdl);
604 if dbms_sql.fetch_rows(l_cur_hdl) > 0 then
605 dbms_sql.column_value(l_cur_hdl, 1, l_grp_id);
606 end if;
607 dbms_sql.close_cursor(l_cur_hdl);
608
609 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
610
611 l_custom_rec.attribute_name := '&ISC_GRP_ID' ;
612 l_custom_rec.attribute_value := l_grp_id;
613 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
614 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
615 p_custom_output.extend;
616 p_custom_output(p_custom_output.count) := l_custom_rec;
617
618 end bind_group_id;
619
620 -- this is a public function, see the package specification for it's
621 -- description
622 function get_sec_where_clause
623 ( p_fact_alias in varchar2
624 , p_org_id in varchar2
625 )
626 return varchar2
627 is
628 begin
629 if p_org_id is not null then
630 return '';
631 end if;
632 return '
633 ( exists
634 ( select 1
635 from org_access o
636 where o.responsibility_id = fnd_global.resp_id
637 and o.resp_application_id = fnd_global.resp_appl_id
638 and o.organization_id = ' || p_fact_alias ||'.organization_id ) or
639 exists
640 ( select 1
641 from mtl_parameters org
642 where org.organization_id = ' || p_fact_alias ||'.organization_id
643 and not exists ( select 1
644 from org_access ora
645 where org.organization_id = ora.organization_id
646 )
647 )
648 )';
649
650 end get_sec_where_clause;
651
652 -- this is a public procedure, see package specification for it's
653 -- description
654 procedure register_dimension_levels
655 ( x_dimension_tbl in out nocopy t_dimension_tbl
656 , x_dim_filter_map in out nocopy poa_dbi_util_pkg.poa_dbi_dim_map
657 , p_dimension1 in varchar2
658 , p_filter_flag1 in varchar2
659 , p_dimension2 in varchar2 default null
660 , p_filter_flag2 in varchar2 default null
661 , p_dimension3 in varchar2 default null
662 , p_filter_flag3 in varchar2 default null
663 , p_dimension4 in varchar2 default null
664 , p_filter_flag4 in varchar2 default null
665 , p_dimension5 in varchar2 default null
666 , p_filter_flag5 in varchar2 default null
667 , p_dimension6 in varchar2 default null
668 , p_filter_flag6 in varchar2 default null
669 , p_dimension7 in varchar2 default null
670 , p_filter_flag7 in varchar2 default null
674 , p_filter_flag9 in varchar2 default null
671 , p_dimension8 in varchar2 default null
672 , p_filter_flag8 in varchar2 default null
673 , p_dimension9 in varchar2 default null
675 , p_dimension10 in varchar2 default null
676 , p_filter_flag10 in varchar2 default null
677 )
678 is
679
680 begin
681
682 if p_dimension1 is not null then
683 init_dim_map( p_dimension1
684 , p_filter_flag1
685 , x_dimension_tbl
686 , x_dim_filter_map
687 );
688
689 end if;
690
691 if p_dimension2 is not null then
692 init_dim_map( p_dimension2
693 , p_filter_flag2
694 , x_dimension_tbl
695 , x_dim_filter_map
696 );
697 end if;
698
699 if p_dimension3 is not null then
700 init_dim_map( p_dimension3
701 , p_filter_flag3
702 , x_dimension_tbl
703 , x_dim_filter_map );
704 end if;
705
706 if p_dimension4 is not null then
707 init_dim_map( p_dimension4
708 , p_filter_flag4
709 , x_dimension_tbl
710 , x_dim_filter_map );
711 end if;
712
713 if p_dimension5 is not null then
714 init_dim_map( p_dimension5
715 , p_filter_flag5
716 , x_dimension_tbl
717 , x_dim_filter_map );
718 end if;
719
720 if p_dimension6 is not null then
721 init_dim_map( p_dimension6
722 , p_filter_flag6
723 , x_dimension_tbl
724 , x_dim_filter_map );
725 end if;
726
727 if p_dimension7 is not null then
728 init_dim_map( p_dimension7
729 , p_filter_flag7
730 , x_dimension_tbl
731 , x_dim_filter_map );
732 end if;
733
734 if p_dimension8 is not null then
735 init_dim_map( p_dimension8
736 , p_filter_flag8
737 , x_dimension_tbl
738 , x_dim_filter_map );
739 end if;
740
741 if p_dimension9 is not null then
742 init_dim_map( p_dimension9
743 , p_filter_flag9
744 , x_dimension_tbl
745 , x_dim_filter_map );
746 end if;
747
748 if p_dimension10 is not null then
749 init_dim_map( p_dimension10
750 , p_filter_flag10
751 , x_dimension_tbl
752 , x_dim_filter_map );
753 end if;
754
755 if not x_dimension_tbl.exists(G_ORGANIZATION) then
756 init_dim_map( G_ORGANIZATION
757 , 'Y'
758 , x_dimension_tbl
759 , x_dim_filter_map
760 );
761 end if;
762
763 if not x_dimension_tbl.exists(G_DEPARTMENT) then
764 init_dim_map( G_DEPARTMENT
765 , 'Y'
766 , x_dimension_tbl
767 , x_dim_filter_map
768 );
769 end if;
770
771 end register_dimension_levels;
772
773 -- this is a public procedure, see the package specification for it's
774 -- description
775 procedure process_parameters
776 ( p_param in bis_pmv_page_parameter_tbl
777 , p_dimension_tbl in out nocopy t_dimension_tbl
778 , p_dim_filter_map in out nocopy poa_dbi_util_pkg.poa_dbi_dim_map
779 , p_trend in varchar2
780 , p_custom_output in out nocopy bis_query_attributes_tbl
781 , x_cur_suffix out nocopy varchar2
782 , x_where_clause out nocopy varchar2
783 , x_viewby_select out nocopy varchar2
784 , x_join_tbl out nocopy poa_dbi_util_pkg.poa_dbi_join_tbl
785 , x_dim_bmap out nocopy number
786 , x_comparison_type out nocopy varchar2
787 , x_xtd out nocopy varchar2
788 ) is
789
790 l_as_of_date date;
791 l_prev_as_of_date date;
792 l_nested_pattern number;
793 l_dim_bmap number;
794 l_view_by varchar2(100);
795 l_where_clause varchar2(10000);
796
797 begin
798
799 if not p_dimension_tbl.exists(G_ORGANIZATION) then
800 register_dimension_levels
801 ( p_dimension_tbl
802 , p_dim_filter_map
803 , G_ORGANIZATION, 'Y'
804 );
805 end if;
806
807 if not p_dimension_tbl.exists(G_DEPARTMENT) then
808 register_dimension_levels
809 ( p_dimension_tbl
810 , p_dim_filter_map
811 , G_DEPARTMENT, 'Y'
812 );
813 end if;
814
815 l_dim_bmap := 0;
816
817 poa_dbi_util_pkg.get_parameter_values
818 ( p_param => p_param -- in, passed in
819 , p_dim_map => p_dim_filter_map -- in, passed in, extended by register_dimension_levels
820 , p_view_by => l_view_by -- out, used locally
821 , p_cur_suffix => x_cur_suffix -- out, returned to caller
822 , p_dim_bmap => l_dim_bmap -- out, returned to caller
823 , p_comparison_type => x_comparison_type -- out, returned to caller
824 , p_xtd => x_xtd -- out, returned to caller
825 , p_as_of_date => l_as_of_date -- out, ignored
826 , p_prev_as_of_date => l_prev_as_of_date -- out, ignored
827 , p_nested_pattern => l_nested_pattern -- out, ignored
828 );
829
830 l_where_clause := poa_dbi_util_pkg.get_where_clauses
831 ( p_dim_filter_map
832 , case
833 when p_trend = 'Y' then 'Y'
834 else 'N'
835 end
836 );
840 end if;
837
838 if p_trend = 'K' and l_where_clause is not null then
839 l_where_clause := '1=1 ' || l_where_clause;
841
842 if p_trend = 'Y' then
843 l_where_clause := l_where_clause || '
844 and fact.period_type_id = n.period_type_id ';
845 elsif p_trend = 'N' then
846 l_where_clause := l_where_clause || '
847 and fact.period_type_id = cal.period_type_id ';
848 end if;
849
850 x_where_clause := l_where_clause;
851
852 if p_trend in ('N', 'K') and
853 (p_dimension_tbl.exists(l_view_by)) then
854 x_viewby_select := case
855 when p_dimension_tbl(l_view_by).dim_outer_join = 'Y' then
856 'nvl(' ||
857 p_dimension_tbl(l_view_by).dim_table_alias ||
858 '.' || p_dimension_tbl(l_view_by).viewby_col_name ||
859 ',&ISC_UNASSIGNED)'
860 else
861 p_dimension_tbl(l_view_by).dim_table_alias ||
862 '.' || p_dimension_tbl(l_view_by).viewby_col_name
863 end ||
864 ' VIEWBY
865 ' ||
866 case
867 when p_dimension_tbl(l_view_by).viewby_id_col_name is not null then
868 case
869 when p_dimension_tbl(l_view_by).dim_outer_join = 'Y' then
870 ', nvl(' ||
871 p_dimension_tbl(l_view_by).dim_table_alias ||
872 '.' || p_dimension_tbl(l_view_by).viewby_id_col_name ||
873 ',' || p_dimension_tbl(l_view_by).viewby_id_unassigned ||
874 ')'
875 else
876 ', ' ||
877 p_dimension_tbl(l_view_by).dim_table_alias ||
878 '.' || p_dimension_tbl(l_view_by).viewby_id_col_name
879 end ||
880 ' VIEWBYID'
881 end;
882
883 end if;
884
885 if p_trend in ('N', 'D', 'K') then
886 bind_unassigned( p_custom_output );
887 end if;
888
889 x_join_tbl := get_join_info(l_view_by, p_dimension_tbl );
890
891 x_dim_bmap := l_dim_bmap;
892
893 end process_parameters;
894
895 -- this is a public function, see the package specification for it's
896 -- description
897 function get_parameter_value
898 ( p_param in bis_pmv_page_parameter_tbl
899 , p_parameter_name in varchar2
900 )
901 return varchar2
902 is
903
904 begin
905 for i in 1..p_param.count loop
906 if p_param(i).parameter_name = p_parameter_name then
907 return p_param(i).parameter_value;
908 end if;
909 end loop;
910 return null;
911 end get_parameter_value;
912
913 -- this is a public function, see the package specification for it's
914 -- description
915 function get_parameter_id
916 ( p_param in bis_pmv_page_parameter_tbl
917 , p_parameter_name in varchar2
918 , p_no_replace_all in varchar2 default null
919 )
920 return varchar2
921 is
922
923 l_param_id varchar2(1000);
924
925 begin
926 for i in 1..p_param.count loop
927 if p_param(i).parameter_name = p_parameter_name then
928 l_param_id := replace(p_param(i).parameter_id,'''',null);
929 if nvl(p_no_replace_all,'N') <> 'Y' then
930 if l_param_id = 'All' then -- mixed case is correct
931 l_param_id := null;
932 end if;
933 end if;
934 return l_param_id;
935 end if;
936 end loop;
937 return null;
938 end get_parameter_id;
939
940 -- this is a public procedure, see the package specification for it's
941 -- description
942 procedure add_detail_column
943 ( p_detail_col_tbl in out nocopy t_detail_column_tbl
944 , p_dimension_tbl in t_dimension_tbl
945 , p_dimension_level in varchar2 default null
946 , p_dim_level_col_name in varchar2 default null
947 , p_fact_col_name in varchar2 default null
948 , p_fact_col_total in varchar2 default null
949 , p_column_key in varchar2
950 )
951 is
952
953 l_detail_column_rec t_detail_column_rec;
954 l_dimension_rec t_dimension_rec;
955 begin
956
957 if p_dimension_level is not null then
958 l_dimension_rec := p_dimension_tbl(p_dimension_level);
959 l_detail_column_rec.dimension_level := p_dimension_level;
960 if p_dim_level_col_name is null then
961 l_detail_column_rec.dim_level_col_name := l_dimension_rec.dim_table_alias ||
962 '.' || l_dimension_rec.viewby_col_name;
963 else
964 l_detail_column_rec.dim_level_col_name := l_dimension_rec.dim_table_alias ||
965 '.' || p_dim_level_col_name;
966 end if;
967 if l_dimension_rec.dim_outer_join = 'Y' then
968 l_detail_column_rec.dim_level_col_name := 'nvl(' ||
969 l_detail_column_rec.dim_level_col_name ||
970 ',&ISC_UNASSIGNED)';
971 end if;
972 else
973 l_detail_column_rec.fact_col_name := p_fact_col_name;
974 l_detail_column_rec.fact_col_total := p_fact_col_total;
975 end if;
976 p_detail_col_tbl(p_column_key) := l_detail_column_rec;
977
978 end add_detail_column;
979
980 -- this is a public function, see the package specification for it's
981 -- description
982 function get_detail_column
983 ( p_detail_col_tbl in t_detail_column_tbl
987 return varchar2
984 , p_column_key in varchar2
985 , p_alias in varchar2 default null
986 )
988 is
989 begin
990 return
991 p_detail_col_tbl(p_column_key).dim_level_col_name ||
992 case
993 when p_alias is not null then
994 ' ' || p_alias
995 end;
996 end get_detail_column;
997
998 -- this is a public function, see the package specification for it's
999 -- description
1000 function detail_sql
1001 ( p_detail_col_tbl in t_detail_column_tbl
1002 , p_dimension_tbl in t_dimension_tbl
1003 , p_mv_name in varchar2
1004 , p_where_clause in varchar2
1005 , p_rank_order in varchar2 default null
1006 , p_filter_where in varchar2 default null
1007 , p_override_date_clause in varchar2 default null
1008 )
1009 return varchar2
1010 is
1011 l_detail_column_rec t_detail_column_rec;
1012 l_dimension_rec t_dimension_rec;
1013 l_dimension_tbl t_dimension_tbl;
1014 l_select_list varchar2(4000);
1015 l_fact_select_list varchar2(4000);
1016 l_key varchar2(200);
1017 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
1018 l_col_name varchar2(200);
1019 begin
1020 -- build fact select list
1021 l_key := p_detail_col_tbl.first;
1022 while l_key is not null loop
1023 l_detail_column_rec := p_detail_col_tbl(l_key);
1024 if l_detail_column_rec.dimension_level is not null then
1025 l_dimension_rec := p_dimension_tbl(l_detail_column_rec.dimension_level);
1026 l_dimension_tbl(l_detail_column_rec.dimension_level) := l_dimension_rec;
1027 l_col_name := l_dimension_rec.oset_col_name1;
1028 if l_select_list is null or
1029 l_select_list not like '%, ' || l_col_name || '%' then
1030 l_select_list := l_select_list || '
1031 , ' || l_col_name;
1032 end if;
1033 l_col_name := l_dimension_rec.oset_col_name2;
1034 if l_col_name is not null and l_select_list not like '%, '||l_col_name || '%' then
1035 l_select_list := l_select_list || '
1036 , ' || l_col_name;
1037 end if;
1038 else
1039 l_fact_select_list := l_fact_select_list ||
1040 '
1041 , ' || l_detail_column_rec.fact_col_name ||
1042 ' ' || l_key;
1043 if l_detail_column_rec.fact_col_total = 'Y' then
1044 l_fact_select_list := l_fact_select_list ||
1045 '
1046 , sum(' || l_detail_column_rec.fact_col_name || ') over()' ||
1047 ' ' || l_key || '_total';
1048 end if;
1049 end if;
1050 l_key := p_detail_col_tbl.next(l_key);
1051 end loop;
1052
1053 l_join_tbl := get_join_info( null, l_dimension_tbl );
1054
1055 return '( select
1056 ' || case
1057 when p_rank_order is null then
1058 '-1 rnk'
1059 else
1060 'rank() over(' || p_rank_order || ')-1 rnk'
1061 end || l_select_list || l_fact_select_list || '
1062 from
1063 ' || p_mv_name || ' fact
1064 where ' ||
1065 case
1066 when p_override_date_clause is not null then
1067 p_override_date_clause
1068 else
1069 'report_date between &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_ASOF_DATE'
1070 end || '
1071 ' || p_where_clause || ' ' || p_filter_where || '
1072 ) oset
1073 , ' || poa_dbi_template_pkg.get_viewby_rank_clause
1074 ( l_join_tbl
1075 , case
1076 when p_rank_order is null then 'N'
1077 else 'Y'
1078 end );
1079
1080 end detail_sql;
1081
1082 -- this is a public function, see the package specification for it's
1083 -- description
1084 function change_column
1085 ( p_current_column in varchar2
1086 , p_prior_column in varchar2
1087 , p_column_alias in varchar2
1088 , p_percent in varchar2 default 'Y'
1089 ) return varchar2
1090 is
1091 begin
1092 if p_percent = 'Y' then
1093 return poa_dbi_util_pkg.change_clause(p_current_column,p_prior_column) ||
1094 ' ' || p_column_alias;
1095 end if;
1096 -- return poa_dbi_util_pkg.change_clause('nvl('||p_current_column||',0)',p_prior_column,'X') ||
1097 return poa_dbi_util_pkg.change_clause(p_current_column,p_prior_column,'X') ||
1098 ' ' || p_column_alias;
1099 end change_column;
1100
1101 -- this is a public function, see the package specification for it's
1102 -- description
1103 function rate_column
1104 ( p_numerator in varchar2
1105 , p_denominator in varchar2
1106 , p_column_alias in varchar2
1107 , p_percent in varchar2 default 'Y'
1108 ) return varchar2
1109 is
1110 begin
1111 return poa_dbi_util_pkg.rate_clause( p_numerator
1112 , p_denominator
1113 , case p_percent
1114 when 'Y' then 'P'
1115 else 'NP'
1116 end ) ||
1117 ' ' || p_column_alias;
1118 end rate_column;
1119
1120 -- this is a public function, see the package specification for it's
1121 -- description
1122 function dump_parameters
1123 ( p_param in bis_pmv_page_parameter_tbl )
1124 return varchar2
1125 is
1126 l_stmt varchar2(10000);
1127 begin
1128 l_stmt := '
1129 /*
1130 ';
1131 for i in 1..p_param.count loop
1132 l_stmt := l_stmt || '"' || p_param(i).parameter_name ||
1133 ',' || p_param(i).parameter_value ||
1134 ',' || p_param(i).parameter_id ||
1135 ',' || p_param(i).dimension ||
1136 ',' || p_param(i).period_date ||
1137 '"
1138 ';
1139 end loop;
1140 l_stmt := l_stmt || '*/';
1141 return l_stmt;
1142 end dump_parameters;
1143
1144 function add_view_by
1145 ( p_view_by in varchar2
1146 , p_dimension_tbl in t_dimension_tbl
1147 , p_join_tbl in out nocopy poa_dbi_util_pkg.poa_dbi_join_tbl
1148 )
1149 return varchar2
1150 is
1151
1152 l_join_rec poa_dbi_util_pkg.poa_dbi_join_rec;
1153 l_dimension_rec t_dimension_rec;
1154 l_return varchar2(200);
1155
1156 begin
1157
1158 l_dimension_rec := p_dimension_tbl(p_view_by);
1159
1160 l_join_rec.column_name := l_dimension_rec.dim_col_name1;
1161 l_join_rec.table_name := l_dimension_rec.dim_table_name;
1162 l_join_rec.table_alias := l_dimension_rec.dim_table_alias;
1163 l_join_rec.fact_column := l_dimension_rec.oset_col_name1;
1164 l_join_rec.dim_outer_join := l_dimension_rec.dim_outer_join;
1165 l_join_rec.additional_where_clause := l_dimension_rec.additional_where_clause;
1166
1167 l_return := l_dimension_rec.dim_table_alias || '.' || l_dimension_rec.viewby_col_name;
1168
1169 if l_dimension_rec.dim_outer_join = 'Y' then
1170 l_return := 'nvl(' || l_return || ',&ISC_UNASSIGNED)';
1171 end if;
1172
1173 p_join_tbl.extend;
1174 p_join_tbl(p_join_tbl.count) := l_join_rec;
1175
1176 if l_dimension_rec.dim_col_name2 is not null then
1177 l_join_rec.column_name := l_dimension_rec.dim_col_name2;
1178 l_join_rec.fact_column := l_dimension_rec.oset_col_name2;
1179 l_join_rec.additional_where_clause := null;
1180
1181 p_join_tbl.extend;
1182 p_join_tbl(p_join_tbl.count) := l_join_rec;
1183 end if;
1184
1185 return l_return;
1186
1187 end add_view_by;
1188
1189 function get_drill_detail
1190 ( p_column_alias in varchar2
1191 , p_org_id_column in varchar2 default null
1192 , p_wo_id_column in varchar2 default null
1193 )
1194 return varchar2
1195 is
1196
1197 begin
1198
1199 return '''pFunctionName=EAM_WORK_RELATIONSHIP'
1200 || '&OAPB=ISC_MAINT_DRILL_BRAND' -- branding
1201 || '&dbiHideReturn=Y' -- this must be 'Y' or 'N'
1202 || '&dbiReturnUrl=' -- pass an empty parameter until PMV can provide value
1203 || '&dbiReturnText=' -- pass an empty parameter until PMV can provide value
1204 || '&WipEntityId=''||'
1205 || nvl(p_wo_id_column,'oset.work_order_id')
1206 || '||''&OrgId=''||'
1207 || nvl(p_org_id_column,'oset.organization_id')
1208 || ' ' || p_column_alias;
1209
1210 end get_drill_detail;
1211
1212 -- this is a public function, see the package specification for it's
1213 -- description
1214 function add_asset_group_column
1215 ( p_view_by in varchar2
1216 , p_dimension_tbl in t_dimension_tbl
1217 )
1218 return varchar2
1219 is
1220 l_dimension_rec t_dimension_rec;
1221 viewby varchar2(200);
1222 begin
1223 l_dimension_rec := p_dimension_tbl(p_view_by); --locate the asset_number_dimension in the dimension table
1224 viewby := l_dimension_rec.dim_table_alias||'.asset_group'; -- return the asset_group curresponding to the asset number.
1225 return viewby;
1226 end add_asset_group_column;
1227
1228 -- this is a public function, see the package specification for it's
1229 -- description
1230 function get_inner_select_col
1231 (p_join_tables in poa_dbi_util_pkg.poa_dbi_join_tbl
1232 ) return varchar2
1233 is
1234 l_select_list varchar2(500);
1235 begin
1236 for i in 1 .. p_join_tables.count loop
1237 if i > 1 then
1238 l_select_list := l_select_list || ', ';
1239 end if;
1240 l_select_list := l_select_list || p_join_tables(i).fact_column;
1241 end loop;
1242 return l_select_list;
1243 end get_inner_select_col;
1244
1245
1246
1247 end isc_maint_rpt_util_pkg;