eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.ISC_MAINT_WO_BACKLOG_RPT_PKG dependencies on ISC_MAINT_RPT_UTIL_PKG

Line 20: l_org_id := isc_maint_rpt_util_pkg.get_parameter_id

16: l_view_by varchar2(200);
17:
18: begin
19:
20: l_org_id := isc_maint_rpt_util_pkg.get_parameter_id
21: ( p_param
22: , isc_maint_rpt_util_pkg.G_ORGANIZATION
23: );
24:

Line 22: , isc_maint_rpt_util_pkg.G_ORGANIZATION

18: begin
19:
20: l_org_id := isc_maint_rpt_util_pkg.get_parameter_id
21: ( p_param
22: , isc_maint_rpt_util_pkg.G_ORGANIZATION
23: );
24:
25: if p_report_type = 'WORK_ORDER_BACKLOG' then
26:

Line 27: l_view_by := isc_maint_rpt_util_pkg.get_parameter_value

23: );
24:
25: if p_report_type = 'WORK_ORDER_BACKLOG' then
26:
27: l_view_by := isc_maint_rpt_util_pkg.get_parameter_value
28: ( p_param
29: , 'VIEW_BY'
30: );
31:

Line 32: isc_maint_rpt_util_pkg.bind_group_id( p_dim_bmap

28: ( p_param
29: , 'VIEW_BY'
30: );
31:
32: isc_maint_rpt_util_pkg.bind_group_id( p_dim_bmap
33: , p_custom_output
34: /* , isc_maint_rpt_util_pkg.G_DEPARTMENT Removed rollup from MV */
35: , isc_maint_rpt_util_pkg.G_ACTIVITY
36: , isc_maint_rpt_util_pkg.G_ASSET_GROUP

Line 34: /* , isc_maint_rpt_util_pkg.G_DEPARTMENT Removed rollup from MV */

30: );
31:
32: isc_maint_rpt_util_pkg.bind_group_id( p_dim_bmap
33: , p_custom_output
34: /* , isc_maint_rpt_util_pkg.G_DEPARTMENT Removed rollup from MV */
35: , isc_maint_rpt_util_pkg.G_ACTIVITY
36: , isc_maint_rpt_util_pkg.G_ASSET_GROUP
37: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER
38: /* , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE Removed rollup from MV */

Line 35: , isc_maint_rpt_util_pkg.G_ACTIVITY

31:
32: isc_maint_rpt_util_pkg.bind_group_id( p_dim_bmap
33: , p_custom_output
34: /* , isc_maint_rpt_util_pkg.G_DEPARTMENT Removed rollup from MV */
35: , isc_maint_rpt_util_pkg.G_ACTIVITY
36: , isc_maint_rpt_util_pkg.G_ASSET_GROUP
37: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER
38: /* , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE Removed rollup from MV */
39: );

Line 36: , isc_maint_rpt_util_pkg.G_ASSET_GROUP

32: isc_maint_rpt_util_pkg.bind_group_id( p_dim_bmap
33: , p_custom_output
34: /* , isc_maint_rpt_util_pkg.G_DEPARTMENT Removed rollup from MV */
35: , isc_maint_rpt_util_pkg.G_ACTIVITY
36: , isc_maint_rpt_util_pkg.G_ASSET_GROUP
37: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER
38: /* , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE Removed rollup from MV */
39: );
40:

Line 37: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER

33: , p_custom_output
34: /* , isc_maint_rpt_util_pkg.G_DEPARTMENT Removed rollup from MV */
35: , isc_maint_rpt_util_pkg.G_ACTIVITY
36: , isc_maint_rpt_util_pkg.G_ASSET_GROUP
37: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER
38: /* , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE Removed rollup from MV */
39: );
40:
41: return ' (select

Line 38: /* , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE Removed rollup from MV */

34: /* , isc_maint_rpt_util_pkg.G_DEPARTMENT Removed rollup from MV */
35: , isc_maint_rpt_util_pkg.G_ACTIVITY
36: , isc_maint_rpt_util_pkg.G_ASSET_GROUP
37: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER
38: /* , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE Removed rollup from MV */
39: );
40:
41: return ' (select
42: time_id

Line 47: when isc_maint_rpt_util_pkg.G_DEPARTMENT then 'decode(department_id,-1,-1,organization_id) organization_id '

43: , period_type_id
44: , grp_id
45: , ' ||
46: case l_view_by
47: when isc_maint_rpt_util_pkg.G_DEPARTMENT then 'decode(department_id,-1,-1,organization_id) organization_id '
48: when isc_maint_rpt_util_pkg.G_ASSET_GROUP then 'decode(asset_group_id,-1,-1,organization_id) organization_id '
49: when isc_maint_rpt_util_pkg.G_ASSET_NUMBER then 'decode(instance_id,-1,-1,organization_id) organization_id ' /* replaced asset_number with instance_id */
50: when isc_maint_rpt_util_pkg.G_ACTIVITY then 'decode(activity_id,-1,-1,organization_id) organization_id '
51: else 'organization_id'

Line 48: when isc_maint_rpt_util_pkg.G_ASSET_GROUP then 'decode(asset_group_id,-1,-1,organization_id) organization_id '

44: , grp_id
45: , ' ||
46: case l_view_by
47: when isc_maint_rpt_util_pkg.G_DEPARTMENT then 'decode(department_id,-1,-1,organization_id) organization_id '
48: when isc_maint_rpt_util_pkg.G_ASSET_GROUP then 'decode(asset_group_id,-1,-1,organization_id) organization_id '
49: when isc_maint_rpt_util_pkg.G_ASSET_NUMBER then 'decode(instance_id,-1,-1,organization_id) organization_id ' /* replaced asset_number with instance_id */
50: when isc_maint_rpt_util_pkg.G_ACTIVITY then 'decode(activity_id,-1,-1,organization_id) organization_id '
51: else 'organization_id'
52: end ||'

Line 49: when isc_maint_rpt_util_pkg.G_ASSET_NUMBER then 'decode(instance_id,-1,-1,organization_id) organization_id ' /* replaced asset_number with instance_id */

45: , ' ||
46: case l_view_by
47: when isc_maint_rpt_util_pkg.G_DEPARTMENT then 'decode(department_id,-1,-1,organization_id) organization_id '
48: when isc_maint_rpt_util_pkg.G_ASSET_GROUP then 'decode(asset_group_id,-1,-1,organization_id) organization_id '
49: when isc_maint_rpt_util_pkg.G_ASSET_NUMBER then 'decode(instance_id,-1,-1,organization_id) organization_id ' /* replaced asset_number with instance_id */
50: when isc_maint_rpt_util_pkg.G_ACTIVITY then 'decode(activity_id,-1,-1,organization_id) organization_id '
51: else 'organization_id'
52: end ||'
53: , organization_id organization_id_c

Line 50: when isc_maint_rpt_util_pkg.G_ACTIVITY then 'decode(activity_id,-1,-1,organization_id) organization_id '

46: case l_view_by
47: when isc_maint_rpt_util_pkg.G_DEPARTMENT then 'decode(department_id,-1,-1,organization_id) organization_id '
48: when isc_maint_rpt_util_pkg.G_ASSET_GROUP then 'decode(asset_group_id,-1,-1,organization_id) organization_id '
49: when isc_maint_rpt_util_pkg.G_ASSET_NUMBER then 'decode(instance_id,-1,-1,organization_id) organization_id ' /* replaced asset_number with instance_id */
50: when isc_maint_rpt_util_pkg.G_ACTIVITY then 'decode(activity_id,-1,-1,organization_id) organization_id '
51: else 'organization_id'
52: end ||'
53: , organization_id organization_id_c
54: , ' ||

Line 56: when isc_maint_rpt_util_pkg.G_ASSET_NUMBER then 'decode(instance_id,-1,-1,asset_group_id) asset_group_id ' /* replaced asset_number with instance_id */

52: end ||'
53: , organization_id organization_id_c
54: , ' ||
55: case l_view_by
56: when isc_maint_rpt_util_pkg.G_ASSET_NUMBER then 'decode(instance_id,-1,-1,asset_group_id) asset_group_id ' /* replaced asset_number with instance_id */
57: else 'asset_group_id'
58: end ||'
59: , instance_id /* replaced asset_number with instance_id */
60: , activity_id

Line 76: and ' || isc_maint_rpt_util_pkg.get_sec_where_clause( 'fact', l_org_id )

72: where fact.grp_id = &ISC_GRP_ID ' ||
73: case
74: when l_org_id is null then
75: '
76: and ' || isc_maint_rpt_util_pkg.get_sec_where_clause( 'fact', l_org_id )
77: end || '
78: )';
79:
80: elsif p_report_type = 'WORK_ORDER_PAST_DUE_AGING' then

Line 82: isc_maint_rpt_util_pkg.bind_group_id( p_dim_bmap

78: )';
79:
80: elsif p_report_type = 'WORK_ORDER_PAST_DUE_AGING' then
81:
82: isc_maint_rpt_util_pkg.bind_group_id( p_dim_bmap
83: , p_custom_output
84: , isc_maint_rpt_util_pkg.G_DEPARTMENT
85: , isc_maint_rpt_util_pkg.G_ASSET_GROUP
86: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER

Line 84: , isc_maint_rpt_util_pkg.G_DEPARTMENT

80: elsif p_report_type = 'WORK_ORDER_PAST_DUE_AGING' then
81:
82: isc_maint_rpt_util_pkg.bind_group_id( p_dim_bmap
83: , p_custom_output
84: , isc_maint_rpt_util_pkg.G_DEPARTMENT
85: , isc_maint_rpt_util_pkg.G_ASSET_GROUP
86: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER
87: , isc_maint_rpt_util_pkg.G_ACTIVITY
88: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE

Line 85: , isc_maint_rpt_util_pkg.G_ASSET_GROUP

81:
82: isc_maint_rpt_util_pkg.bind_group_id( p_dim_bmap
83: , p_custom_output
84: , isc_maint_rpt_util_pkg.G_DEPARTMENT
85: , isc_maint_rpt_util_pkg.G_ASSET_GROUP
86: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER
87: , isc_maint_rpt_util_pkg.G_ACTIVITY
88: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE
89: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS

Line 86: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER

82: isc_maint_rpt_util_pkg.bind_group_id( p_dim_bmap
83: , p_custom_output
84: , isc_maint_rpt_util_pkg.G_DEPARTMENT
85: , isc_maint_rpt_util_pkg.G_ASSET_GROUP
86: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER
87: , isc_maint_rpt_util_pkg.G_ACTIVITY
88: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE
89: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS
90: );

Line 87: , isc_maint_rpt_util_pkg.G_ACTIVITY

83: , p_custom_output
84: , isc_maint_rpt_util_pkg.G_DEPARTMENT
85: , isc_maint_rpt_util_pkg.G_ASSET_GROUP
86: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER
87: , isc_maint_rpt_util_pkg.G_ACTIVITY
88: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE
89: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS
90: );
91:

Line 88: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE

84: , isc_maint_rpt_util_pkg.G_DEPARTMENT
85: , isc_maint_rpt_util_pkg.G_ASSET_GROUP
86: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER
87: , isc_maint_rpt_util_pkg.G_ACTIVITY
88: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE
89: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS
90: );
91:
92: return ' (select

Line 89: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS

85: , isc_maint_rpt_util_pkg.G_ASSET_GROUP
86: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER
87: , isc_maint_rpt_util_pkg.G_ACTIVITY
88: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE
89: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS
90: );
91:
92: return ' (select
93: organization_id

Line 112: and ' || isc_maint_rpt_util_pkg.get_sec_where_clause( 'fact', l_org_id )

108: where fact.grp_id = &ISC_GRP_ID ' ||
109: case
110: when l_org_id is null then
111: '
112: and ' || isc_maint_rpt_util_pkg.get_sec_where_clause( 'fact', l_org_id )
113: end || '
114: )';
115:
116: elsif p_report_type = 'WORK_ORDER_BACKLOG_DTL' then

Line 145: and ' || isc_maint_rpt_util_pkg.get_sec_where_clause( 'f', l_org_id )

141: ' ||
142: case
143: when l_org_id is null then
144: '
145: and ' || isc_maint_rpt_util_pkg.get_sec_where_clause( 'f', l_org_id )
146: end || '
147: ) ';
148:
149: elsif p_report_type = 'WORK_ORDER_PAST_DUE_DTL' then

Line 200: and ' || isc_maint_rpt_util_pkg.get_sec_where_clause( 'f', l_org_id )

196: and f.SCHEDULED_COMPLETION_DATE < trunc(coll.last_update_date) ' ||
197: case
198: when l_org_id is null then
199: '
200: and ' || isc_maint_rpt_util_pkg.get_sec_where_clause( 'f', l_org_id )
201: end || '
202: ) ';
203:
204:

Line 218: l_dimension_tbl isc_maint_rpt_util_pkg.t_dimension_tbl;

214: , x_custom_output out nocopy bis_query_attributes_tbl
215: )
216: is
217:
218: l_dimension_tbl isc_maint_rpt_util_pkg.t_dimension_tbl;
219: l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
220: l_dim_bmap number;
221:
222: l_comparison_type varchar2(200);

Line 250: isc_maint_rpt_util_pkg.register_dimension_levels

246: -- clear out the tables.
247: l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
248: l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
249:
250: isc_maint_rpt_util_pkg.register_dimension_levels
251: ( l_dimension_tbl
252: , l_dim_filter_map
253: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
254: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'

Line 253: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'

249:
250: isc_maint_rpt_util_pkg.register_dimension_levels
251: ( l_dimension_tbl
252: , l_dim_filter_map
253: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
254: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
255: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
256: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
257: );

Line 254: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'

250: isc_maint_rpt_util_pkg.register_dimension_levels
251: ( l_dimension_tbl
252: , l_dim_filter_map
253: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
254: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
255: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
256: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
257: );
258:

Line 255: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'

251: ( l_dimension_tbl
252: , l_dim_filter_map
253: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
254: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
255: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
256: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
257: );
258:
259: isc_maint_rpt_util_pkg.process_parameters

Line 256: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'

252: , l_dim_filter_map
253: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
254: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
255: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
256: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
257: );
258:
259: isc_maint_rpt_util_pkg.process_parameters
260: ( p_param => p_param

Line 259: isc_maint_rpt_util_pkg.process_parameters

255: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
256: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
257: );
258:
259: isc_maint_rpt_util_pkg.process_parameters
260: ( p_param => p_param
261: , p_dimension_tbl => l_dimension_tbl
262: , p_dim_filter_map => l_dim_filter_map
263: , p_trend => 'N'

Line 306: l_as_of_date:= to_date(isc_maint_rpt_util_pkg.get_parameter_value(p_param, 'AS_OF_DATE'),'DD/MM/YYYY');

302: );
303:
304:
305:
306: l_as_of_date:= to_date(isc_maint_rpt_util_pkg.get_parameter_value(p_param, 'AS_OF_DATE'),'DD/MM/YYYY');
307: select nvl(max(trunc(last_update_date)),trunc(l_as_of_date)-1) into l_last_collection_date from isc_maint_work_orders_f coll
308: where coll.Organization_id = -99 and coll.Work_Order_id = -99 and coll.Entity_Type = -1;
309:
310: case

Line 328: if(isc_maint_rpt_util_pkg.get_parameter_id(p_param,'VIEW_BY')) =

324: l_drill_across2:='null';
325: end case;
326:
327: /* if view by is asset number then add the asset_group column */
328: if(isc_maint_rpt_util_pkg.get_parameter_id(p_param,'VIEW_BY')) =
329: isc_maint_rpt_util_pkg.G_ASSET_NUMBER then
330: l_asset_grp_column := isc_maint_rpt_util_pkg.add_asset_group_column(isc_maint_rpt_util_pkg.G_ASSET_NUMBER,l_dimension_tbl);
331: else
332: l_asset_grp_column :='NULL';

Line 329: isc_maint_rpt_util_pkg.G_ASSET_NUMBER then

325: end case;
326:
327: /* if view by is asset number then add the asset_group column */
328: if(isc_maint_rpt_util_pkg.get_parameter_id(p_param,'VIEW_BY')) =
329: isc_maint_rpt_util_pkg.G_ASSET_NUMBER then
330: l_asset_grp_column := isc_maint_rpt_util_pkg.add_asset_group_column(isc_maint_rpt_util_pkg.G_ASSET_NUMBER,l_dimension_tbl);
331: else
332: l_asset_grp_column :='NULL';
333: end if ;

Line 330: l_asset_grp_column := isc_maint_rpt_util_pkg.add_asset_group_column(isc_maint_rpt_util_pkg.G_ASSET_NUMBER,l_dimension_tbl);

326:
327: /* if view by is asset number then add the asset_group column */
328: if(isc_maint_rpt_util_pkg.get_parameter_id(p_param,'VIEW_BY')) =
329: isc_maint_rpt_util_pkg.G_ASSET_NUMBER then
330: l_asset_grp_column := isc_maint_rpt_util_pkg.add_asset_group_column(isc_maint_rpt_util_pkg.G_ASSET_NUMBER,l_dimension_tbl);
331: else
332: l_asset_grp_column :='NULL';
333: end if ;
334:

Line 360: select row_number() over(&ORDER_BY_CLAUSE, '|| isc_maint_rpt_util_pkg.get_inner_select_col(l_join_tbl) || ')-1 rnk

356: , BIV_MEASURE17
357: , BIV_MEASURE18 , '
358: || l_asset_grp_column ||' BIV_MEASURE30
359: from ( /* calculate the rank on the sorting column in the inline view */
360: select row_number() over(&ORDER_BY_CLAUSE, '|| isc_maint_rpt_util_pkg.get_inner_select_col(l_join_tbl) || ')-1 rnk
361: , iset.*
362: from ( select * from (
363: select ' || isc_maint_rpt_util_pkg.get_inner_select_col(l_join_tbl) || '
364: , nvl(p_num_created - nvl(p_num_completion,0),0) BIV_MEASURE1

Line 363: select ' || isc_maint_rpt_util_pkg.get_inner_select_col(l_join_tbl) || '

359: from ( /* calculate the rank on the sorting column in the inline view */
360: select row_number() over(&ORDER_BY_CLAUSE, '|| isc_maint_rpt_util_pkg.get_inner_select_col(l_join_tbl) || ')-1 rnk
361: , iset.*
362: from ( select * from (
363: select ' || isc_maint_rpt_util_pkg.get_inner_select_col(l_join_tbl) || '
364: , nvl(p_num_created - nvl(p_num_completion,0),0) BIV_MEASURE1
365: , nvl(c_num_created - nvl(c_num_completion,0),0) BIV_MEASURE2' || '
366: , ' ||
367: isc_maint_rpt_util_pkg.change_column

Line 367: isc_maint_rpt_util_pkg.change_column

363: select ' || isc_maint_rpt_util_pkg.get_inner_select_col(l_join_tbl) || '
364: , nvl(p_num_created - nvl(p_num_completion,0),0) BIV_MEASURE1
365: , nvl(c_num_created - nvl(c_num_completion,0),0) BIV_MEASURE2' || '
366: , ' ||
367: isc_maint_rpt_util_pkg.change_column
368: ( 'c_num_created - nvl(c_num_completion,0)'
369: , '(p_num_created - nvl(p_num_completion,0))'
370: , 'BIV_MEASURE3' ) || ' /* Change Backlog */
371: , decode(sign(p_num_past_due - nvl(p_num_past_due_cmpl,0)), -1, 0, nvl(p_num_past_due - nvl(p_num_past_due_cmpl,0),0)) BIV_MEASURE4

Line 374: isc_maint_rpt_util_pkg.change_column

370: , 'BIV_MEASURE3' ) || ' /* Change Backlog */
371: , decode(sign(p_num_past_due - nvl(p_num_past_due_cmpl,0)), -1, 0, nvl(p_num_past_due - nvl(p_num_past_due_cmpl,0),0)) BIV_MEASURE4
372: , decode(sign(c_num_past_due - nvl(c_num_past_due_cmpl,0)), -1, 0, nvl(c_num_past_due - nvl(c_num_past_due_cmpl,0),0)) BIV_MEASURE5' || '
373: , ' ||
374: isc_maint_rpt_util_pkg.change_column
375: ( 'decode(sign(c_num_past_due - nvl(c_num_past_due_cmpl,0)), -1, 0, nvl(c_num_past_due - nvl(c_num_past_due_cmpl,0),0))'
376: , 'decode(sign(p_num_past_due - nvl(p_num_past_due_cmpl,0)), -1, 0, nvl(p_num_past_due - nvl(p_num_past_due_cmpl,0),0))'
377: , 'BIV_MEASURE6' ) || ' /* Change Past Due */
378: , ' ||

Line 379: isc_maint_rpt_util_pkg.rate_column

375: ( 'decode(sign(c_num_past_due - nvl(c_num_past_due_cmpl,0)), -1, 0, nvl(c_num_past_due - nvl(c_num_past_due_cmpl,0),0))'
376: , 'decode(sign(p_num_past_due - nvl(p_num_past_due_cmpl,0)), -1, 0, nvl(p_num_past_due - nvl(p_num_past_due_cmpl,0),0))'
377: , 'BIV_MEASURE6' ) || ' /* Change Past Due */
378: , ' ||
379: isc_maint_rpt_util_pkg.rate_column
380: ( 'decode(sign(p_num_past_due - nvl(p_num_past_due_cmpl,0)), -1, 0, nvl(p_num_past_due - nvl(p_num_past_due_cmpl,0),0))'
381: , 'p_num_created - nvl(p_num_completion,0)'
382: , 'BIV_MEASURE7'
383: , 'Y' ) || '

Line 385: isc_maint_rpt_util_pkg.rate_column

381: , 'p_num_created - nvl(p_num_completion,0)'
382: , 'BIV_MEASURE7'
383: , 'Y' ) || '
384: /* Prior Past Due percent */, ' ||
385: isc_maint_rpt_util_pkg.rate_column
386: ( 'decode(sign(c_num_past_due - nvl(c_num_past_due_cmpl,0)), -1, 0, nvl(c_num_past_due - nvl(c_num_past_due_cmpl,0),0))'
387: , '(c_num_created - nvl(c_num_completion,0))'
388: , 'BIV_MEASURE8'
389: , 'Y' ) || '

Line 391: isc_maint_rpt_util_pkg.change_column

387: , '(c_num_created - nvl(c_num_completion,0))'
388: , 'BIV_MEASURE8'
389: , 'Y' ) || '
390: /* Past Due percent */, ' ||
391: isc_maint_rpt_util_pkg.change_column
392: ( isc_maint_rpt_util_pkg.rate_column
393: ( 'decode(sign(c_num_past_due - nvl(c_num_past_due_cmpl,0)), -1, 0, nvl(c_num_past_due - nvl(c_num_past_due_cmpl,0),0))'
394: , '(c_num_created - nvl(c_num_completion,0))'
395: , ''

Line 392: ( isc_maint_rpt_util_pkg.rate_column

388: , 'BIV_MEASURE8'
389: , 'Y' ) || '
390: /* Past Due percent */, ' ||
391: isc_maint_rpt_util_pkg.change_column
392: ( isc_maint_rpt_util_pkg.rate_column
393: ( 'decode(sign(c_num_past_due - nvl(c_num_past_due_cmpl,0)), -1, 0, nvl(c_num_past_due - nvl(c_num_past_due_cmpl,0),0))'
394: , '(c_num_created - nvl(c_num_completion,0))'
395: , ''
396: , 'Y' )

Line 397: , isc_maint_rpt_util_pkg.rate_column

393: ( 'decode(sign(c_num_past_due - nvl(c_num_past_due_cmpl,0)), -1, 0, nvl(c_num_past_due - nvl(c_num_past_due_cmpl,0),0))'
394: , '(c_num_created - nvl(c_num_completion,0))'
395: , ''
396: , 'Y' )
397: , isc_maint_rpt_util_pkg.rate_column
398: ( 'decode(sign(p_num_past_due - nvl(p_num_past_due_cmpl,0)), -1, 0, nvl(p_num_past_due - nvl(p_num_past_due_cmpl,0),0))'
399: , '(p_num_created - nvl(p_num_completion,0))'
400: , ''
401: , 'Y' )

Line 407: isc_maint_rpt_util_pkg.change_column

403: , 'N' ) || ' /* Past Due Percent Change */
404: , nvl(p_num_created_total - nvl(p_num_completion_total,0),0) BIV_MEASURE21
405: , nvl(c_num_created_total - nvl(c_num_completion_total,0),0) BIV_MEASURE13' || '
406: , ' ||
407: isc_maint_rpt_util_pkg.change_column
408: ( 'c_num_created_total - nvl(c_num_completion_total,0)'
409: , '(p_num_created_total - nvl(p_num_completion_total,0))'
410: , 'BIV_MEASURE14' ) || ' /* Grand Total - Backlog Change */
411: , nvl(decode(sign(c_num_past_due_total - nvl(c_num_past_due_cmpl_total,0)), -1, 0, nvl(c_num_past_due_total - nvl(c_num_past_due_cmpl_total,0),0)),0) BIV_MEASURE15' || '

Line 413: isc_maint_rpt_util_pkg.change_column

409: , '(p_num_created_total - nvl(p_num_completion_total,0))'
410: , 'BIV_MEASURE14' ) || ' /* Grand Total - Backlog Change */
411: , nvl(decode(sign(c_num_past_due_total - nvl(c_num_past_due_cmpl_total,0)), -1, 0, nvl(c_num_past_due_total - nvl(c_num_past_due_cmpl_total,0),0)),0) BIV_MEASURE15' || '
412: , ' ||
413: isc_maint_rpt_util_pkg.change_column
414: ( 'decode(sign(c_num_past_due_total - nvl(c_num_past_due_cmpl_total,0)), -1, 0, nvl(c_num_past_due_total - nvl(c_num_past_due_cmpl_total,0),0)) '
415: , 'decode(sign(p_num_past_due_total - nvl(p_num_past_due_cmpl_total,0)), -1, 0, nvl(p_num_past_due_total - nvl(p_num_past_due_cmpl_total,0),0)) '
416: , 'BIV_MEASURE16' ) || ' /* Grand Total - Past Due Change */
417: , ' ||

Line 418: isc_maint_rpt_util_pkg.rate_column

414: ( 'decode(sign(c_num_past_due_total - nvl(c_num_past_due_cmpl_total,0)), -1, 0, nvl(c_num_past_due_total - nvl(c_num_past_due_cmpl_total,0),0)) '
415: , 'decode(sign(p_num_past_due_total - nvl(p_num_past_due_cmpl_total,0)), -1, 0, nvl(p_num_past_due_total - nvl(p_num_past_due_cmpl_total,0),0)) '
416: , 'BIV_MEASURE16' ) || ' /* Grand Total - Past Due Change */
417: , ' ||
418: isc_maint_rpt_util_pkg.rate_column
419: ( 'decode(sign(p_num_past_due_total - nvl(p_num_past_due_cmpl_total,0)), -1, 0, nvl(p_num_past_due_total - nvl(p_num_past_due_cmpl_total,0),0))'
420: , '(p_num_created_total - nvl(p_num_completion_total,0))'
421: , 'BIV_MEASURE22'
422: , 'Y' ) || '/* Grand Total - Prior Past Due percent */, ' ||

Line 423: isc_maint_rpt_util_pkg.rate_column

419: ( 'decode(sign(p_num_past_due_total - nvl(p_num_past_due_cmpl_total,0)), -1, 0, nvl(p_num_past_due_total - nvl(p_num_past_due_cmpl_total,0),0))'
420: , '(p_num_created_total - nvl(p_num_completion_total,0))'
421: , 'BIV_MEASURE22'
422: , 'Y' ) || '/* Grand Total - Prior Past Due percent */, ' ||
423: isc_maint_rpt_util_pkg.rate_column
424: ( 'decode(sign(c_num_past_due_total - nvl(c_num_past_due_cmpl_total,0)), -1, 0, nvl(c_num_past_due_total - nvl(c_num_past_due_cmpl_total,0),0))'
425: , '(c_num_created_total - nvl(c_num_completion_total,0))'
426: , 'BIV_MEASURE17'
427: , 'Y' ) || '

Line 429: isc_maint_rpt_util_pkg.change_column

425: , '(c_num_created_total - nvl(c_num_completion_total,0))'
426: , 'BIV_MEASURE17'
427: , 'Y' ) || '
428: /* Grand Total - Past Due percent */, ' ||
429: isc_maint_rpt_util_pkg.change_column
430: ( isc_maint_rpt_util_pkg.rate_column
431: ( 'decode(sign(c_num_past_due_total - nvl(c_num_past_due_cmpl_total,0)), -1, 0, nvl(c_num_past_due_total - nvl(c_num_past_due_cmpl_total,0),0))'
432: , '(c_num_created_total - nvl(c_num_completion_total,0))'
433: , ''

Line 430: ( isc_maint_rpt_util_pkg.rate_column

426: , 'BIV_MEASURE17'
427: , 'Y' ) || '
428: /* Grand Total - Past Due percent */, ' ||
429: isc_maint_rpt_util_pkg.change_column
430: ( isc_maint_rpt_util_pkg.rate_column
431: ( 'decode(sign(c_num_past_due_total - nvl(c_num_past_due_cmpl_total,0)), -1, 0, nvl(c_num_past_due_total - nvl(c_num_past_due_cmpl_total,0),0))'
432: , '(c_num_created_total - nvl(c_num_completion_total,0))'
433: , ''
434: , 'Y' )

Line 435: , isc_maint_rpt_util_pkg.rate_column

431: ( 'decode(sign(c_num_past_due_total - nvl(c_num_past_due_cmpl_total,0)), -1, 0, nvl(c_num_past_due_total - nvl(c_num_past_due_cmpl_total,0),0))'
432: , '(c_num_created_total - nvl(c_num_completion_total,0))'
433: , ''
434: , 'Y' )
435: , isc_maint_rpt_util_pkg.rate_column
436: ( 'decode(sign(p_num_past_due_total - nvl(p_num_past_due_cmpl_total,0)), -1, 0, nvl(p_num_past_due_total - nvl(p_num_past_due_cmpl_total,0),0))'
437: , '(p_num_created_total - nvl(p_num_completion_total,0))'
438: , ''
439: , 'Y' )

Line 458: -- l_stmt := l_stmt || isc_maint_rpt_util_pkg.dump_parameters(p_param);

454:
455: l_stmt := replace(l_stmt,'&BIS_NESTED_PATTERN', '1143');
456:
457: /* the next line can be used to dump the contents of the PMV parameters as comments into stmt */
458: -- l_stmt := l_stmt || isc_maint_rpt_util_pkg.dump_parameters(p_param);
459:
460: x_custom_output := l_custom_output;
461:
462: x_custom_sql := l_stmt;

Line 475: l_dimension_tbl isc_maint_rpt_util_pkg.t_dimension_tbl;

471: , x_custom_output out nocopy bis_query_attributes_tbl
472: )
473: is
474:
475: l_dimension_tbl isc_maint_rpt_util_pkg.t_dimension_tbl;
476: l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
477: l_dim_bmap number;
478: l_comparison_type varchar2(200);
479: l_xtd varchar2(200);

Line 500: isc_maint_rpt_util_pkg.register_dimension_levels

496: -- clear out the tables.
497: l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
498: l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
499:
500: isc_maint_rpt_util_pkg.register_dimension_levels
501: ( l_dimension_tbl
502: , l_dim_filter_map
503: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
504: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'

Line 503: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'

499:
500: isc_maint_rpt_util_pkg.register_dimension_levels
501: ( l_dimension_tbl
502: , l_dim_filter_map
503: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
504: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
505: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
506: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
507: );

Line 504: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'

500: isc_maint_rpt_util_pkg.register_dimension_levels
501: ( l_dimension_tbl
502: , l_dim_filter_map
503: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
504: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
505: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
506: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
507: );
508:

Line 505: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'

501: ( l_dimension_tbl
502: , l_dim_filter_map
503: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
504: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
505: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
506: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
507: );
508:
509: isc_maint_rpt_util_pkg.process_parameters

Line 506: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'

502: , l_dim_filter_map
503: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
504: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
505: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
506: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
507: );
508:
509: isc_maint_rpt_util_pkg.process_parameters
510: ( p_param => p_param

Line 509: isc_maint_rpt_util_pkg.process_parameters

505: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
506: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
507: );
508:
509: isc_maint_rpt_util_pkg.process_parameters
510: ( p_param => p_param
511: , p_dimension_tbl => l_dimension_tbl
512: , p_dim_filter_map => l_dim_filter_map
513: , p_trend => 'Y'

Line 566: isc_maint_rpt_util_pkg.change_column

562: , null BIV_ATTRIBUTE1
563: , nvl(iset.p_num_created - nvl(iset.p_num_completion,0),0) BIV_MEASURE1
564: , nvl(iset.c_num_created - nvl(iset.c_num_completion,0),0) BIV_MEASURE2' || '
565: , ' ||
566: isc_maint_rpt_util_pkg.change_column
567: ( 'iset.c_num_created - nvl(iset.c_num_completion,0)'
568: , '(iset.p_num_created - nvl(iset.p_num_completion,0))'
569: , 'BIV_MEASURE3' ) || ' /* Change Backlog */
570: , decode(sign(iset.p_num_past_due - nvl(iset.p_num_past_due_cmpl,0)), -1, 0, nvl(iset.p_num_past_due - nvl(iset.p_num_past_due_cmpl,0),0)) BIV_MEASURE5

Line 573: isc_maint_rpt_util_pkg.change_column

569: , 'BIV_MEASURE3' ) || ' /* Change Backlog */
570: , decode(sign(iset.p_num_past_due - nvl(iset.p_num_past_due_cmpl,0)), -1, 0, nvl(iset.p_num_past_due - nvl(iset.p_num_past_due_cmpl,0),0)) BIV_MEASURE5
571: , decode(sign(iset.c_num_past_due - nvl(iset.c_num_past_due_cmpl,0)), -1, 0, nvl(iset.c_num_past_due - nvl(iset.c_num_past_due_cmpl,0),0)) BIV_MEASURE6' || '
572: , ' ||
573: isc_maint_rpt_util_pkg.change_column
574: ( 'decode(sign(iset.c_num_past_due - nvl(iset.c_num_past_due_cmpl,0)), -1, 0, nvl(iset.c_num_past_due - nvl(iset.c_num_past_due_cmpl,0),0))'
575: , 'decode(sign(iset.p_num_past_due - nvl(iset.p_num_past_due_cmpl,0)), -1, 0, nvl(iset.p_num_past_due - nvl(iset.p_num_past_due_cmpl,0),0))'
576: , 'BIV_MEASURE7' ) || ' /* Change Past Due */
577: , ' ||

Line 578: isc_maint_rpt_util_pkg.rate_column

574: ( 'decode(sign(iset.c_num_past_due - nvl(iset.c_num_past_due_cmpl,0)), -1, 0, nvl(iset.c_num_past_due - nvl(iset.c_num_past_due_cmpl,0),0))'
575: , 'decode(sign(iset.p_num_past_due - nvl(iset.p_num_past_due_cmpl,0)), -1, 0, nvl(iset.p_num_past_due - nvl(iset.p_num_past_due_cmpl,0),0))'
576: , 'BIV_MEASURE7' ) || ' /* Change Past Due */
577: , ' ||
578: isc_maint_rpt_util_pkg.rate_column
579: ( 'decode(sign(iset.p_num_past_due - nvl(iset.p_num_past_due_cmpl,0)), -1, 0, nvl(iset.p_num_past_due - nvl(iset.p_num_past_due_cmpl,0),0))'
580: , 'iset.p_num_created - nvl(iset.p_num_completion,0)'
581: , 'BIV_MEASURE9'
582: , 'Y' ) || '

Line 584: isc_maint_rpt_util_pkg.rate_column

580: , 'iset.p_num_created - nvl(iset.p_num_completion,0)'
581: , 'BIV_MEASURE9'
582: , 'Y' ) || '
583: /* Prior Past Due percent */, ' ||
584: isc_maint_rpt_util_pkg.rate_column
585: ( 'decode(sign(iset.c_num_past_due - nvl(iset.c_num_past_due_cmpl,0)), -1, 0, nvl(iset.c_num_past_due - nvl(iset.c_num_past_due_cmpl,0),0))'
586: , '(iset.c_num_created - nvl(iset.c_num_completion,0))'
587: , 'BIV_MEASURE10'
588: , 'Y' ) || '

Line 590: isc_maint_rpt_util_pkg.change_column

586: , '(iset.c_num_created - nvl(iset.c_num_completion,0))'
587: , 'BIV_MEASURE10'
588: , 'Y' ) || '
589: /* Past Due percent */, ' ||
590: isc_maint_rpt_util_pkg.change_column
591: ( isc_maint_rpt_util_pkg.rate_column
592: ( 'decode(sign(iset.c_num_past_due - nvl(iset.c_num_past_due_cmpl,0)), -1, 0, nvl(iset.c_num_past_due - nvl(iset.c_num_past_due_cmpl,0),0))'
593: , '(iset.c_num_created - nvl(iset.c_num_completion,0))'
594: , ''

Line 591: ( isc_maint_rpt_util_pkg.rate_column

587: , 'BIV_MEASURE10'
588: , 'Y' ) || '
589: /* Past Due percent */, ' ||
590: isc_maint_rpt_util_pkg.change_column
591: ( isc_maint_rpt_util_pkg.rate_column
592: ( 'decode(sign(iset.c_num_past_due - nvl(iset.c_num_past_due_cmpl,0)), -1, 0, nvl(iset.c_num_past_due - nvl(iset.c_num_past_due_cmpl,0),0))'
593: , '(iset.c_num_created - nvl(iset.c_num_completion,0))'
594: , ''
595: , 'Y' )

Line 596: , isc_maint_rpt_util_pkg.rate_column

592: ( 'decode(sign(iset.c_num_past_due - nvl(iset.c_num_past_due_cmpl,0)), -1, 0, nvl(iset.c_num_past_due - nvl(iset.c_num_past_due_cmpl,0),0))'
593: , '(iset.c_num_created - nvl(iset.c_num_completion,0))'
594: , ''
595: , 'Y' )
596: , isc_maint_rpt_util_pkg.rate_column
597: ( 'decode(sign(iset.p_num_past_due - nvl(iset.p_num_past_due_cmpl,0)), -1, 0, nvl(iset.p_num_past_due - nvl(iset.p_num_past_due_cmpl,0),0))'
598: , '(iset.p_num_created - nvl(iset.p_num_completion,0))'
599: , ''
600: , 'Y' )

Line 615: -- l_stmt := l_stmt || isc_maint_rpt_util_pkg.dump_parameters(p_param);

611: );
612:
613: l_stmt := replace(l_stmt,'&BIS_NESTED_PATTERN', '1143');
614: /* the next line can be used to dump the contents of the PMV parameters as comments into stmt */
615: -- l_stmt := l_stmt || isc_maint_rpt_util_pkg.dump_parameters(p_param);
616:
617: x_custom_sql := l_stmt;
618:
619: x_custom_output := l_custom_output;

Line 643: l_dimension_tbl isc_maint_rpt_util_pkg.t_dimension_tbl;

639: x_custom_sql OUT NOCOPY VARCHAR2,
640: x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
641: IS
642:
643: l_dimension_tbl isc_maint_rpt_util_pkg.t_dimension_tbl;
644: l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
645: l_dim_bmap number;
646: l_comparison_type varchar2(200);
647: l_xtd varchar2(200);

Line 665: l_detail_col_tbl isc_maint_rpt_util_pkg.t_detail_column_tbl;

661: l_asc_desc varchar2(100);
662:
663: l_viewby_select varchar2(200);
664:
665: l_detail_col_tbl isc_maint_rpt_util_pkg.t_detail_column_tbl;
666:
667:
668: begin
669:

Line 674: isc_maint_rpt_util_pkg.register_dimension_levels

670: -- clear out the tables.
671: l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
672: l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
673:
674: isc_maint_rpt_util_pkg.register_dimension_levels
675: ( l_dimension_tbl
676: , l_dim_filter_map
677: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
678: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'

Line 677: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'

673:
674: isc_maint_rpt_util_pkg.register_dimension_levels
675: ( l_dimension_tbl
676: , l_dim_filter_map
677: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
678: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
679: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
680: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
681: , isc_maint_rpt_util_pkg.G_PAST_DUE_AGING, 'Y'

Line 678: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'

674: isc_maint_rpt_util_pkg.register_dimension_levels
675: ( l_dimension_tbl
676: , l_dim_filter_map
677: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
678: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
679: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
680: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
681: , isc_maint_rpt_util_pkg.G_PAST_DUE_AGING, 'Y'
682: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS, 'Y'

Line 679: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'

675: ( l_dimension_tbl
676: , l_dim_filter_map
677: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
678: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
679: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
680: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
681: , isc_maint_rpt_util_pkg.G_PAST_DUE_AGING, 'Y'
682: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS, 'Y'
683: );

Line 680: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'

676: , l_dim_filter_map
677: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
678: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
679: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
680: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
681: , isc_maint_rpt_util_pkg.G_PAST_DUE_AGING, 'Y'
682: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS, 'Y'
683: );
684:

Line 681: , isc_maint_rpt_util_pkg.G_PAST_DUE_AGING, 'Y'

677: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
678: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
679: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
680: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
681: , isc_maint_rpt_util_pkg.G_PAST_DUE_AGING, 'Y'
682: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS, 'Y'
683: );
684:
685: isc_maint_rpt_util_pkg.process_parameters

Line 682: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS, 'Y'

678: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
679: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
680: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
681: , isc_maint_rpt_util_pkg.G_PAST_DUE_AGING, 'Y'
682: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS, 'Y'
683: );
684:
685: isc_maint_rpt_util_pkg.process_parameters
686: ( p_param => p_param

Line 685: isc_maint_rpt_util_pkg.process_parameters

681: , isc_maint_rpt_util_pkg.G_PAST_DUE_AGING, 'Y'
682: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS, 'Y'
683: );
684:
685: isc_maint_rpt_util_pkg.process_parameters
686: ( p_param => p_param
687: , p_dimension_tbl => l_dimension_tbl
688: , p_dim_filter_map => l_dim_filter_map
689: , p_trend => 'D'

Line 707: l_order_by := isc_maint_rpt_util_pkg.get_parameter_value

703: , l_dim_bmap
704: , l_custom_output
705: );
706:
707: l_order_by := isc_maint_rpt_util_pkg.get_parameter_value
708: ( p_param
709: , 'ORDERBY'
710: );
711:

Line 735: isc_maint_rpt_util_pkg.add_detail_column

731: l_asc_desc ||
732: 'nulls last, organization_id, work_order_id';
733:
734:
735: isc_maint_rpt_util_pkg.add_detail_column
736: ( p_detail_col_tbl => l_detail_col_tbl
737: , p_dimension_tbl => l_dimension_tbl
738: , p_fact_col_name => 'work_order_id'
739: , p_fact_col_total => 'N'

Line 743: isc_maint_rpt_util_pkg.add_detail_column

739: , p_fact_col_total => 'N'
740: , p_column_key => 'work_order_id'
741: );
742:
743: isc_maint_rpt_util_pkg.add_detail_column
744: ( p_detail_col_tbl => l_detail_col_tbl
745: , p_dimension_tbl => l_dimension_tbl
746: , p_fact_col_name => 'work_order_name'
747: , p_fact_col_total => 'N'

Line 751: isc_maint_rpt_util_pkg.add_detail_column

747: , p_fact_col_total => 'N'
748: , p_column_key => 'work_order_name'
749: );
750:
751: isc_maint_rpt_util_pkg.add_detail_column
752: ( p_detail_col_tbl => l_detail_col_tbl
753: , p_dimension_tbl => l_dimension_tbl
754: , p_dimension_level => isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE
755: , p_column_key => 'work_order_type'

Line 754: , p_dimension_level => isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE

750:
751: isc_maint_rpt_util_pkg.add_detail_column
752: ( p_detail_col_tbl => l_detail_col_tbl
753: , p_dimension_tbl => l_dimension_tbl
754: , p_dimension_level => isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE
755: , p_column_key => 'work_order_type'
756: );
757:
758: isc_maint_rpt_util_pkg.add_detail_column

Line 758: isc_maint_rpt_util_pkg.add_detail_column

754: , p_dimension_level => isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE
755: , p_column_key => 'work_order_type'
756: );
757:
758: isc_maint_rpt_util_pkg.add_detail_column
759: ( p_detail_col_tbl => l_detail_col_tbl
760: , p_dimension_tbl => l_dimension_tbl
761: , p_dimension_level => isc_maint_rpt_util_pkg.G_ASSET_NUMBER
762: , p_column_key => 'asset_number'

Line 761: , p_dimension_level => isc_maint_rpt_util_pkg.G_ASSET_NUMBER

757:
758: isc_maint_rpt_util_pkg.add_detail_column
759: ( p_detail_col_tbl => l_detail_col_tbl
760: , p_dimension_tbl => l_dimension_tbl
761: , p_dimension_level => isc_maint_rpt_util_pkg.G_ASSET_NUMBER
762: , p_column_key => 'asset_number'
763: );
764:
765: isc_maint_rpt_util_pkg.add_detail_column

Line 765: isc_maint_rpt_util_pkg.add_detail_column

761: , p_dimension_level => isc_maint_rpt_util_pkg.G_ASSET_NUMBER
762: , p_column_key => 'asset_number'
763: );
764:
765: isc_maint_rpt_util_pkg.add_detail_column
766: ( p_detail_col_tbl => l_detail_col_tbl
767: , p_dimension_tbl => l_dimension_tbl
768: , p_dimension_level => isc_maint_rpt_util_pkg.G_ASSET_GROUP
769: , p_column_key => 'asset_group'

Line 768: , p_dimension_level => isc_maint_rpt_util_pkg.G_ASSET_GROUP

764:
765: isc_maint_rpt_util_pkg.add_detail_column
766: ( p_detail_col_tbl => l_detail_col_tbl
767: , p_dimension_tbl => l_dimension_tbl
768: , p_dimension_level => isc_maint_rpt_util_pkg.G_ASSET_GROUP
769: , p_column_key => 'asset_group'
770: );
771:
772: isc_maint_rpt_util_pkg.add_detail_column

Line 772: isc_maint_rpt_util_pkg.add_detail_column

768: , p_dimension_level => isc_maint_rpt_util_pkg.G_ASSET_GROUP
769: , p_column_key => 'asset_group'
770: );
771:
772: isc_maint_rpt_util_pkg.add_detail_column
773: ( p_detail_col_tbl => l_detail_col_tbl
774: , p_dimension_tbl => l_dimension_tbl
775: , p_dimension_level => isc_maint_rpt_util_pkg.G_ACTIVITY
776: , p_column_key => 'activity'

Line 775: , p_dimension_level => isc_maint_rpt_util_pkg.G_ACTIVITY

771:
772: isc_maint_rpt_util_pkg.add_detail_column
773: ( p_detail_col_tbl => l_detail_col_tbl
774: , p_dimension_tbl => l_dimension_tbl
775: , p_dimension_level => isc_maint_rpt_util_pkg.G_ACTIVITY
776: , p_column_key => 'activity'
777: );
778:
779: isc_maint_rpt_util_pkg.add_detail_column

Line 779: isc_maint_rpt_util_pkg.add_detail_column

775: , p_dimension_level => isc_maint_rpt_util_pkg.G_ACTIVITY
776: , p_column_key => 'activity'
777: );
778:
779: isc_maint_rpt_util_pkg.add_detail_column
780: ( p_detail_col_tbl => l_detail_col_tbl
781: , p_dimension_tbl => l_dimension_tbl
782: , p_fact_col_name => 'status_name'
783: , p_fact_col_total => 'N'

Line 787: isc_maint_rpt_util_pkg.add_detail_column

783: , p_fact_col_total => 'N'
784: , p_column_key => 'status_type'
785: );
786:
787: isc_maint_rpt_util_pkg.add_detail_column
788: ( p_detail_col_tbl => l_detail_col_tbl
789: , p_dimension_tbl => l_dimension_tbl
790: , p_dimension_level => isc_maint_rpt_util_pkg.G_DEPARTMENT
791: , p_column_key => 'department'

Line 790: , p_dimension_level => isc_maint_rpt_util_pkg.G_DEPARTMENT

786:
787: isc_maint_rpt_util_pkg.add_detail_column
788: ( p_detail_col_tbl => l_detail_col_tbl
789: , p_dimension_tbl => l_dimension_tbl
790: , p_dimension_level => isc_maint_rpt_util_pkg.G_DEPARTMENT
791: , p_column_key => 'department'
792: );
793:
794: isc_maint_rpt_util_pkg.add_detail_column

Line 794: isc_maint_rpt_util_pkg.add_detail_column

790: , p_dimension_level => isc_maint_rpt_util_pkg.G_DEPARTMENT
791: , p_column_key => 'department'
792: );
793:
794: isc_maint_rpt_util_pkg.add_detail_column
795: ( p_detail_col_tbl => l_detail_col_tbl
796: , p_dimension_tbl => l_dimension_tbl
797: , p_fact_col_name => 'SCHEDULED_START_DATE'
798: , p_fact_col_total => 'N'

Line 802: isc_maint_rpt_util_pkg.add_detail_column

798: , p_fact_col_total => 'N'
799: , p_column_key => 'SCHEDULED_START_DATE'
800: );
801:
802: isc_maint_rpt_util_pkg.add_detail_column
803: ( p_detail_col_tbl => l_detail_col_tbl
804: , p_dimension_tbl => l_dimension_tbl
805: , p_fact_col_name => 'SCHEDULED_COMPLETION_DATE'
806: , p_fact_col_total => 'N'

Line 810: isc_maint_rpt_util_pkg.add_detail_column

806: , p_fact_col_total => 'N'
807: , p_column_key => 'SCHEDULED_COMPLETION_DATE'
808: );
809:
810: isc_maint_rpt_util_pkg.add_detail_column
811: ( p_detail_col_tbl => l_detail_col_tbl
812: , p_dimension_tbl => l_dimension_tbl
813: , p_fact_col_name => 'LAST_COLLECTION_DATE'
814: , p_fact_col_total => 'N'

Line 818: isc_maint_rpt_util_pkg.add_detail_column

814: , p_fact_col_total => 'N'
815: , p_column_key => 'LAST_COLLECTION_DATE'
816: );
817:
818: isc_maint_rpt_util_pkg.add_detail_column
819: ( p_detail_col_tbl => l_detail_col_tbl
820: , p_dimension_tbl => l_dimension_tbl
821: , p_fact_col_name => 'Past_due_days'
822: , p_fact_col_total => 'N'

Line 829: , ' || isc_maint_rpt_util_pkg.get_detail_column

825:
826: l_stmt := 'select
827: oset.work_order_name BIV_ATTRIBUTE1
828: , null BIV_ATTRIBUTE2 ' || '
829: , ' || isc_maint_rpt_util_pkg.get_detail_column
830: (l_detail_col_tbl,'work_order_type','BIV_ATTRIBUTE3') || '
831: , ' || isc_maint_rpt_util_pkg.get_detail_column
832: (l_detail_col_tbl,'asset_number','BIV_ATTRIBUTE4') || '
833: , ' || isc_maint_rpt_util_pkg.get_detail_column

Line 831: , ' || isc_maint_rpt_util_pkg.get_detail_column

827: oset.work_order_name BIV_ATTRIBUTE1
828: , null BIV_ATTRIBUTE2 ' || '
829: , ' || isc_maint_rpt_util_pkg.get_detail_column
830: (l_detail_col_tbl,'work_order_type','BIV_ATTRIBUTE3') || '
831: , ' || isc_maint_rpt_util_pkg.get_detail_column
832: (l_detail_col_tbl,'asset_number','BIV_ATTRIBUTE4') || '
833: , ' || isc_maint_rpt_util_pkg.get_detail_column
834: (l_detail_col_tbl,'asset_group','BIV_ATTRIBUTE5') || '
835: , ' || isc_maint_rpt_util_pkg.get_detail_column

Line 833: , ' || isc_maint_rpt_util_pkg.get_detail_column

829: , ' || isc_maint_rpt_util_pkg.get_detail_column
830: (l_detail_col_tbl,'work_order_type','BIV_ATTRIBUTE3') || '
831: , ' || isc_maint_rpt_util_pkg.get_detail_column
832: (l_detail_col_tbl,'asset_number','BIV_ATTRIBUTE4') || '
833: , ' || isc_maint_rpt_util_pkg.get_detail_column
834: (l_detail_col_tbl,'asset_group','BIV_ATTRIBUTE5') || '
835: , ' || isc_maint_rpt_util_pkg.get_detail_column
836: (l_detail_col_tbl,'activity','BIV_ATTRIBUTE6') || '
837: , oset.status_type BIV_ATTRIBUTE7' || '

Line 835: , ' || isc_maint_rpt_util_pkg.get_detail_column

831: , ' || isc_maint_rpt_util_pkg.get_detail_column
832: (l_detail_col_tbl,'asset_number','BIV_ATTRIBUTE4') || '
833: , ' || isc_maint_rpt_util_pkg.get_detail_column
834: (l_detail_col_tbl,'asset_group','BIV_ATTRIBUTE5') || '
835: , ' || isc_maint_rpt_util_pkg.get_detail_column
836: (l_detail_col_tbl,'activity','BIV_ATTRIBUTE6') || '
837: , oset.status_type BIV_ATTRIBUTE7' || '
838: , ' || isc_maint_rpt_util_pkg.get_detail_column
839: (l_detail_col_tbl,'department','BIV_ATTRIBUTE9') || '

Line 838: , ' || isc_maint_rpt_util_pkg.get_detail_column

834: (l_detail_col_tbl,'asset_group','BIV_ATTRIBUTE5') || '
835: , ' || isc_maint_rpt_util_pkg.get_detail_column
836: (l_detail_col_tbl,'activity','BIV_ATTRIBUTE6') || '
837: , oset.status_type BIV_ATTRIBUTE7' || '
838: , ' || isc_maint_rpt_util_pkg.get_detail_column
839: (l_detail_col_tbl,'department','BIV_ATTRIBUTE9') || '
840: , oset.SCHEDULED_START_DATE BIV_ATTRIBUTE10
841: , oset.SCHEDULED_COMPLETION_DATE BIV_ATTRIBUTE11
842: , oset.Past_due_days BIV_ATTRIBUTE12 ' || '

Line 844: , ' || isc_maint_rpt_util_pkg.get_drill_detail('BIV_ATTRIBUTE8') || '

840: , oset.SCHEDULED_START_DATE BIV_ATTRIBUTE10
841: , oset.SCHEDULED_COMPLETION_DATE BIV_ATTRIBUTE11
842: , oset.Past_due_days BIV_ATTRIBUTE12 ' || '
843: , null BIV_ATTRIBUTE13
844: , ' || isc_maint_rpt_util_pkg.get_drill_detail('BIV_ATTRIBUTE8') || '
845: from
846: ' || isc_maint_rpt_util_pkg.detail_sql
847: ( p_detail_col_tbl => l_detail_col_tbl
848: , p_dimension_tbl => l_dimension_tbl

Line 846: ' || isc_maint_rpt_util_pkg.detail_sql

842: , oset.Past_due_days BIV_ATTRIBUTE12 ' || '
843: , null BIV_ATTRIBUTE13
844: , ' || isc_maint_rpt_util_pkg.get_drill_detail('BIV_ATTRIBUTE8') || '
845: from
846: ' || isc_maint_rpt_util_pkg.detail_sql
847: ( p_detail_col_tbl => l_detail_col_tbl
848: , p_dimension_tbl => l_dimension_tbl
849: , p_mv_name => l_mv
850: , p_where_clause => l_where_clause

Line 856: -- l_stmt := l_stmt || isc_maint_rpt_util_pkg.dump_parameters(p_param);

852: , p_override_date_clause => '1 = 1 '
853: );
854:
855: /* the next line can be used to dump the contents of the PMV parameters as comments into stmt */
856: -- l_stmt := l_stmt || isc_maint_rpt_util_pkg.dump_parameters(p_param);
857:
858: x_custom_sql := l_stmt;
859:
860: x_custom_output := l_custom_output;

Line 871: l_dimension_tbl isc_maint_rpt_util_pkg.t_dimension_tbl;

867: x_custom_sql OUT NOCOPY VARCHAR2,
868: x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
869: is
870:
871: l_dimension_tbl isc_maint_rpt_util_pkg.t_dimension_tbl;
872: l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
873: l_dim_bmap number;
874:
875: l_comparison_type varchar2(200);

Line 894: l_detail_col_tbl isc_maint_rpt_util_pkg.t_detail_column_tbl;

890: l_asc_desc varchar2(100);
891:
892: l_viewby_select varchar2(200);
893:
894: l_detail_col_tbl isc_maint_rpt_util_pkg.t_detail_column_tbl;
895:
896: begin
897:
898: -- clear out the tables.

Line 902: isc_maint_rpt_util_pkg.register_dimension_levels

898: -- clear out the tables.
899: l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
900: l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
901:
902: isc_maint_rpt_util_pkg.register_dimension_levels
903: ( l_dimension_tbl
904: , l_dim_filter_map
905: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
906: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'

Line 905: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'

901:
902: isc_maint_rpt_util_pkg.register_dimension_levels
903: ( l_dimension_tbl
904: , l_dim_filter_map
905: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
906: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
907: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
908: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
909: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS, 'Y'

Line 906: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'

902: isc_maint_rpt_util_pkg.register_dimension_levels
903: ( l_dimension_tbl
904: , l_dim_filter_map
905: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
906: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
907: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
908: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
909: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS, 'Y'
910: );

Line 907: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'

903: ( l_dimension_tbl
904: , l_dim_filter_map
905: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
906: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
907: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
908: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
909: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS, 'Y'
910: );
911:

Line 908: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'

904: , l_dim_filter_map
905: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
906: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
907: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
908: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
909: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS, 'Y'
910: );
911:
912: isc_maint_rpt_util_pkg.process_parameters

Line 909: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS, 'Y'

905: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
906: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
907: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
908: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
909: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS, 'Y'
910: );
911:
912: isc_maint_rpt_util_pkg.process_parameters
913: ( p_param => p_param

Line 912: isc_maint_rpt_util_pkg.process_parameters

908: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
909: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS, 'Y'
910: );
911:
912: isc_maint_rpt_util_pkg.process_parameters
913: ( p_param => p_param
914: , p_dimension_tbl => l_dimension_tbl
915: , p_dim_filter_map => l_dim_filter_map
916: , p_trend => 'D'

Line 935: l_order_by := isc_maint_rpt_util_pkg.get_parameter_value

931: , l_custom_output
932: );
933:
934:
935: l_order_by := isc_maint_rpt_util_pkg.get_parameter_value
936: ( p_param
937: , 'ORDERBY'
938: );
939:

Line 961: isc_maint_rpt_util_pkg.add_detail_column

957: l_asc_desc ||
958: 'nulls last, organization_id, work_order_id';
959:
960:
961: isc_maint_rpt_util_pkg.add_detail_column
962: ( p_detail_col_tbl => l_detail_col_tbl
963: , p_dimension_tbl => l_dimension_tbl
964: , p_fact_col_name => 'work_order_id'
965: , p_fact_col_total => 'N'

Line 969: isc_maint_rpt_util_pkg.add_detail_column

965: , p_fact_col_total => 'N'
966: , p_column_key => 'work_order_id'
967: );
968:
969: isc_maint_rpt_util_pkg.add_detail_column
970: ( p_detail_col_tbl => l_detail_col_tbl
971: , p_dimension_tbl => l_dimension_tbl
972: , p_fact_col_name => 'work_order_name'
973: , p_fact_col_total => 'N'

Line 977: isc_maint_rpt_util_pkg.add_detail_column

973: , p_fact_col_total => 'N'
974: , p_column_key => 'work_order_name'
975: );
976:
977: isc_maint_rpt_util_pkg.add_detail_column
978: ( p_detail_col_tbl => l_detail_col_tbl
979: , p_dimension_tbl => l_dimension_tbl
980: , p_dimension_level => isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE
981: , p_column_key => 'work_order_type'

Line 980: , p_dimension_level => isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE

976:
977: isc_maint_rpt_util_pkg.add_detail_column
978: ( p_detail_col_tbl => l_detail_col_tbl
979: , p_dimension_tbl => l_dimension_tbl
980: , p_dimension_level => isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE
981: , p_column_key => 'work_order_type'
982: );
983:
984: isc_maint_rpt_util_pkg.add_detail_column

Line 984: isc_maint_rpt_util_pkg.add_detail_column

980: , p_dimension_level => isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE
981: , p_column_key => 'work_order_type'
982: );
983:
984: isc_maint_rpt_util_pkg.add_detail_column
985: ( p_detail_col_tbl => l_detail_col_tbl
986: , p_dimension_tbl => l_dimension_tbl
987: , p_dimension_level => isc_maint_rpt_util_pkg.G_ASSET_NUMBER
988: , p_column_key => 'asset_number'

Line 987: , p_dimension_level => isc_maint_rpt_util_pkg.G_ASSET_NUMBER

983:
984: isc_maint_rpt_util_pkg.add_detail_column
985: ( p_detail_col_tbl => l_detail_col_tbl
986: , p_dimension_tbl => l_dimension_tbl
987: , p_dimension_level => isc_maint_rpt_util_pkg.G_ASSET_NUMBER
988: , p_column_key => 'asset_number'
989: );
990:
991: isc_maint_rpt_util_pkg.add_detail_column

Line 991: isc_maint_rpt_util_pkg.add_detail_column

987: , p_dimension_level => isc_maint_rpt_util_pkg.G_ASSET_NUMBER
988: , p_column_key => 'asset_number'
989: );
990:
991: isc_maint_rpt_util_pkg.add_detail_column
992: ( p_detail_col_tbl => l_detail_col_tbl
993: , p_dimension_tbl => l_dimension_tbl
994: , p_dimension_level => isc_maint_rpt_util_pkg.G_ASSET_GROUP
995: , p_column_key => 'asset_group'

Line 994: , p_dimension_level => isc_maint_rpt_util_pkg.G_ASSET_GROUP

990:
991: isc_maint_rpt_util_pkg.add_detail_column
992: ( p_detail_col_tbl => l_detail_col_tbl
993: , p_dimension_tbl => l_dimension_tbl
994: , p_dimension_level => isc_maint_rpt_util_pkg.G_ASSET_GROUP
995: , p_column_key => 'asset_group'
996: );
997:
998: isc_maint_rpt_util_pkg.add_detail_column

Line 998: isc_maint_rpt_util_pkg.add_detail_column

994: , p_dimension_level => isc_maint_rpt_util_pkg.G_ASSET_GROUP
995: , p_column_key => 'asset_group'
996: );
997:
998: isc_maint_rpt_util_pkg.add_detail_column
999: ( p_detail_col_tbl => l_detail_col_tbl
1000: , p_dimension_tbl => l_dimension_tbl
1001: , p_dimension_level => isc_maint_rpt_util_pkg.G_ACTIVITY
1002: , p_column_key => 'activity'

Line 1001: , p_dimension_level => isc_maint_rpt_util_pkg.G_ACTIVITY

997:
998: isc_maint_rpt_util_pkg.add_detail_column
999: ( p_detail_col_tbl => l_detail_col_tbl
1000: , p_dimension_tbl => l_dimension_tbl
1001: , p_dimension_level => isc_maint_rpt_util_pkg.G_ACTIVITY
1002: , p_column_key => 'activity'
1003: );
1004:
1005: isc_maint_rpt_util_pkg.add_detail_column

Line 1005: isc_maint_rpt_util_pkg.add_detail_column

1001: , p_dimension_level => isc_maint_rpt_util_pkg.G_ACTIVITY
1002: , p_column_key => 'activity'
1003: );
1004:
1005: isc_maint_rpt_util_pkg.add_detail_column
1006: ( p_detail_col_tbl => l_detail_col_tbl
1007: , p_dimension_tbl => l_dimension_tbl
1008: , p_fact_col_name => 'status_name'
1009: , p_fact_col_total => 'N'

Line 1013: isc_maint_rpt_util_pkg.add_detail_column

1009: , p_fact_col_total => 'N'
1010: , p_column_key => 'status_type'
1011: );
1012:
1013: isc_maint_rpt_util_pkg.add_detail_column
1014: ( p_detail_col_tbl => l_detail_col_tbl
1015: , p_dimension_tbl => l_dimension_tbl
1016: , p_dimension_level => isc_maint_rpt_util_pkg.G_DEPARTMENT
1017: , p_column_key => 'department'

Line 1016: , p_dimension_level => isc_maint_rpt_util_pkg.G_DEPARTMENT

1012:
1013: isc_maint_rpt_util_pkg.add_detail_column
1014: ( p_detail_col_tbl => l_detail_col_tbl
1015: , p_dimension_tbl => l_dimension_tbl
1016: , p_dimension_level => isc_maint_rpt_util_pkg.G_DEPARTMENT
1017: , p_column_key => 'department'
1018: );
1019:
1020: isc_maint_rpt_util_pkg.add_detail_column

Line 1020: isc_maint_rpt_util_pkg.add_detail_column

1016: , p_dimension_level => isc_maint_rpt_util_pkg.G_DEPARTMENT
1017: , p_column_key => 'department'
1018: );
1019:
1020: isc_maint_rpt_util_pkg.add_detail_column
1021: ( p_detail_col_tbl => l_detail_col_tbl
1022: , p_dimension_tbl => l_dimension_tbl
1023: , p_fact_col_name => 'SCHEDULED_START_DATE'
1024: , p_fact_col_total => 'N'

Line 1028: isc_maint_rpt_util_pkg.add_detail_column

1024: , p_fact_col_total => 'N'
1025: , p_column_key => 'SCHEDULED_START_DATE'
1026: );
1027:
1028: isc_maint_rpt_util_pkg.add_detail_column
1029: ( p_detail_col_tbl => l_detail_col_tbl
1030: , p_dimension_tbl => l_dimension_tbl
1031: , p_fact_col_name => 'SCHEDULED_COMPLETION_DATE'
1032: , p_fact_col_total => 'N'

Line 1039: , ' || isc_maint_rpt_util_pkg.get_detail_column

1035:
1036: l_stmt := 'select
1037: oset.work_order_name BIV_ATTRIBUTE1
1038: , null BIV_ATTRIBUTE2 ' || '
1039: , ' || isc_maint_rpt_util_pkg.get_detail_column
1040: (l_detail_col_tbl,'work_order_type','BIV_ATTRIBUTE3') || '
1041: , ' || isc_maint_rpt_util_pkg.get_detail_column
1042: (l_detail_col_tbl,'asset_number','BIV_ATTRIBUTE4') || '
1043: , ' || isc_maint_rpt_util_pkg.get_detail_column

Line 1041: , ' || isc_maint_rpt_util_pkg.get_detail_column

1037: oset.work_order_name BIV_ATTRIBUTE1
1038: , null BIV_ATTRIBUTE2 ' || '
1039: , ' || isc_maint_rpt_util_pkg.get_detail_column
1040: (l_detail_col_tbl,'work_order_type','BIV_ATTRIBUTE3') || '
1041: , ' || isc_maint_rpt_util_pkg.get_detail_column
1042: (l_detail_col_tbl,'asset_number','BIV_ATTRIBUTE4') || '
1043: , ' || isc_maint_rpt_util_pkg.get_detail_column
1044: (l_detail_col_tbl,'asset_group','BIV_ATTRIBUTE5') || '
1045: , ' || isc_maint_rpt_util_pkg.get_detail_column

Line 1043: , ' || isc_maint_rpt_util_pkg.get_detail_column

1039: , ' || isc_maint_rpt_util_pkg.get_detail_column
1040: (l_detail_col_tbl,'work_order_type','BIV_ATTRIBUTE3') || '
1041: , ' || isc_maint_rpt_util_pkg.get_detail_column
1042: (l_detail_col_tbl,'asset_number','BIV_ATTRIBUTE4') || '
1043: , ' || isc_maint_rpt_util_pkg.get_detail_column
1044: (l_detail_col_tbl,'asset_group','BIV_ATTRIBUTE5') || '
1045: , ' || isc_maint_rpt_util_pkg.get_detail_column
1046: (l_detail_col_tbl,'activity','BIV_ATTRIBUTE6') || '
1047: , status_type BIV_ATTRIBUTE7' || '

Line 1045: , ' || isc_maint_rpt_util_pkg.get_detail_column

1041: , ' || isc_maint_rpt_util_pkg.get_detail_column
1042: (l_detail_col_tbl,'asset_number','BIV_ATTRIBUTE4') || '
1043: , ' || isc_maint_rpt_util_pkg.get_detail_column
1044: (l_detail_col_tbl,'asset_group','BIV_ATTRIBUTE5') || '
1045: , ' || isc_maint_rpt_util_pkg.get_detail_column
1046: (l_detail_col_tbl,'activity','BIV_ATTRIBUTE6') || '
1047: , status_type BIV_ATTRIBUTE7' || '
1048: , ' || isc_maint_rpt_util_pkg.get_detail_column
1049: (l_detail_col_tbl,'department','BIV_ATTRIBUTE9') || '

Line 1048: , ' || isc_maint_rpt_util_pkg.get_detail_column

1044: (l_detail_col_tbl,'asset_group','BIV_ATTRIBUTE5') || '
1045: , ' || isc_maint_rpt_util_pkg.get_detail_column
1046: (l_detail_col_tbl,'activity','BIV_ATTRIBUTE6') || '
1047: , status_type BIV_ATTRIBUTE7' || '
1048: , ' || isc_maint_rpt_util_pkg.get_detail_column
1049: (l_detail_col_tbl,'department','BIV_ATTRIBUTE9') || '
1050: , oset.SCHEDULED_START_DATE BIV_ATTRIBUTE10
1051: , oset.SCHEDULED_COMPLETION_DATE BIV_ATTRIBUTE11' || '
1052: , ' || isc_maint_rpt_util_pkg.get_drill_detail('BIV_ATTRIBUTE8') || '

Line 1052: , ' || isc_maint_rpt_util_pkg.get_drill_detail('BIV_ATTRIBUTE8') || '

1048: , ' || isc_maint_rpt_util_pkg.get_detail_column
1049: (l_detail_col_tbl,'department','BIV_ATTRIBUTE9') || '
1050: , oset.SCHEDULED_START_DATE BIV_ATTRIBUTE10
1051: , oset.SCHEDULED_COMPLETION_DATE BIV_ATTRIBUTE11' || '
1052: , ' || isc_maint_rpt_util_pkg.get_drill_detail('BIV_ATTRIBUTE8') || '
1053: from
1054: ' || isc_maint_rpt_util_pkg.detail_sql
1055: ( p_detail_col_tbl => l_detail_col_tbl
1056: , p_dimension_tbl => l_dimension_tbl

Line 1054: ' || isc_maint_rpt_util_pkg.detail_sql

1050: , oset.SCHEDULED_START_DATE BIV_ATTRIBUTE10
1051: , oset.SCHEDULED_COMPLETION_DATE BIV_ATTRIBUTE11' || '
1052: , ' || isc_maint_rpt_util_pkg.get_drill_detail('BIV_ATTRIBUTE8') || '
1053: from
1054: ' || isc_maint_rpt_util_pkg.detail_sql
1055: ( p_detail_col_tbl => l_detail_col_tbl
1056: , p_dimension_tbl => l_dimension_tbl
1057: , p_mv_name => l_mv
1058: , p_where_clause => l_where_clause

Line 1065: -- l_stmt := l_stmt || isc_maint_rpt_util_pkg.dump_parameters(p_param);

1061: );
1062:
1063:
1064: /* the next line can be used to dump the contents of the PMV parameters as comments into stmt */
1065: -- l_stmt := l_stmt || isc_maint_rpt_util_pkg.dump_parameters(p_param);
1066:
1067: x_custom_sql := l_stmt;
1068:
1069: x_custom_output := l_custom_output;

Line 1081: l_dimension_tbl isc_maint_rpt_util_pkg.t_dimension_tbl;

1077: , x_custom_sql out nocopy varchar2
1078: , x_custom_output out nocopy bis_query_attributes_tbl
1079: )
1080: as
1081: l_dimension_tbl isc_maint_rpt_util_pkg.t_dimension_tbl;
1082: l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
1083: l_dim_bmap number;
1084: l_comparison_type varchar2(200);
1085: l_xtd varchar2(200);

Line 1103: l_detail_col_tbl isc_maint_rpt_util_pkg.t_detail_column_tbl;

1099: l_viewby_select varchar2(200);
1100:
1101: l_orderby varchar2(40);
1102:
1103: l_detail_col_tbl isc_maint_rpt_util_pkg.t_detail_column_tbl;
1104:
1105: begin
1106:
1107: -- clear out the tables.

Line 1111: isc_maint_rpt_util_pkg.register_dimension_levels

1107: -- clear out the tables.
1108: l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
1109: l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
1110:
1111: isc_maint_rpt_util_pkg.register_dimension_levels
1112: ( l_dimension_tbl
1113: , l_dim_filter_map
1114: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
1115: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'

Line 1114: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'

1110:
1111: isc_maint_rpt_util_pkg.register_dimension_levels
1112: ( l_dimension_tbl
1113: , l_dim_filter_map
1114: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
1115: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
1116: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
1117: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
1118: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS, 'Y'

Line 1115: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'

1111: isc_maint_rpt_util_pkg.register_dimension_levels
1112: ( l_dimension_tbl
1113: , l_dim_filter_map
1114: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
1115: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
1116: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
1117: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
1118: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS, 'Y'
1119: , isc_maint_rpt_util_pkg.G_PAST_DUE_AGING, 'N'

Line 1116: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'

1112: ( l_dimension_tbl
1113: , l_dim_filter_map
1114: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
1115: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
1116: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
1117: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
1118: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS, 'Y'
1119: , isc_maint_rpt_util_pkg.G_PAST_DUE_AGING, 'N'
1120: );

Line 1117: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'

1113: , l_dim_filter_map
1114: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
1115: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
1116: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
1117: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
1118: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS, 'Y'
1119: , isc_maint_rpt_util_pkg.G_PAST_DUE_AGING, 'N'
1120: );
1121:

Line 1118: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS, 'Y'

1114: , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
1115: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
1116: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
1117: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
1118: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS, 'Y'
1119: , isc_maint_rpt_util_pkg.G_PAST_DUE_AGING, 'N'
1120: );
1121:
1122: isc_maint_rpt_util_pkg.process_parameters

Line 1119: , isc_maint_rpt_util_pkg.G_PAST_DUE_AGING, 'N'

1115: , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
1116: , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
1117: , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
1118: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS, 'Y'
1119: , isc_maint_rpt_util_pkg.G_PAST_DUE_AGING, 'N'
1120: );
1121:
1122: isc_maint_rpt_util_pkg.process_parameters
1123: ( p_param => p_param

Line 1122: isc_maint_rpt_util_pkg.process_parameters

1118: , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS, 'Y'
1119: , isc_maint_rpt_util_pkg.G_PAST_DUE_AGING, 'N'
1120: );
1121:
1122: isc_maint_rpt_util_pkg.process_parameters
1123: ( p_param => p_param
1124: , p_dimension_tbl => l_dimension_tbl
1125: , p_dim_filter_map => l_dim_filter_map
1126: , p_trend => 'D'

Line 1144: isc_maint_rpt_util_pkg.add_detail_column

1140: , l_dim_bmap
1141: , l_custom_output
1142: );
1143:
1144: isc_maint_rpt_util_pkg.add_detail_column
1145: ( p_detail_col_tbl => l_detail_col_tbl
1146: , p_dimension_tbl => l_dimension_tbl
1147: , p_dimension_level => isc_maint_rpt_util_pkg.G_PAST_DUE_AGING
1148: , p_column_key => 'bucket_name'

Line 1147: , p_dimension_level => isc_maint_rpt_util_pkg.G_PAST_DUE_AGING

1143:
1144: isc_maint_rpt_util_pkg.add_detail_column
1145: ( p_detail_col_tbl => l_detail_col_tbl
1146: , p_dimension_tbl => l_dimension_tbl
1147: , p_dimension_level => isc_maint_rpt_util_pkg.G_PAST_DUE_AGING
1148: , p_column_key => 'bucket_name'
1149: );
1150:
1151: isc_maint_rpt_util_pkg.add_detail_column

Line 1151: isc_maint_rpt_util_pkg.add_detail_column

1147: , p_dimension_level => isc_maint_rpt_util_pkg.G_PAST_DUE_AGING
1148: , p_column_key => 'bucket_name'
1149: );
1150:
1151: isc_maint_rpt_util_pkg.add_detail_column
1152: ( p_detail_col_tbl => l_detail_col_tbl
1153: , p_dimension_tbl => l_dimension_tbl
1154: , p_fact_col_name => 'sum(c_num_pastdue)'
1155: , p_fact_col_total => 'Y'

Line 1163: ' || isc_maint_rpt_util_pkg.get_detail_column

1159: l_stmt := 'select
1160: ''pFunctionName=ISC_MAINT_PAST_DUE_DTL_RPT_REP'' ||
1161: ''&VIEW_BY_NAME=VIEW_BY_ID'' ||
1162: ''&pParamIds=Y'' BIV_ATTRIBUTE1,
1163: ' || isc_maint_rpt_util_pkg.get_detail_column
1164: (l_detail_col_tbl,'bucket_name','VIEWBY') || '
1165: , id VIEWBYID
1166: , nvl(oset.c_num_pastdue,0) BIV_MEASURE1' || '
1167: , ' ||

Line 1168: isc_maint_rpt_util_pkg.rate_column

1164: (l_detail_col_tbl,'bucket_name','VIEWBY') || '
1165: , id VIEWBYID
1166: , nvl(oset.c_num_pastdue,0) BIV_MEASURE1' || '
1167: , ' ||
1168: isc_maint_rpt_util_pkg.rate_column
1169: ( 'oset.c_num_pastdue'
1170: , 'avg(oset.c_num_pastdue_total) over()'
1171: , 'BIV_MEASURE2'
1172: , 'Y' ) || '

Line 1176: isc_maint_rpt_util_pkg.rate_column

1172: , 'Y' ) || '
1173: /* Percent of Total */ ' || '
1174: , nvl(avg(oset.c_num_pastdue_total) over(),0) BIV_MEASURE3' || '
1175: , ' ||
1176: isc_maint_rpt_util_pkg.rate_column
1177: ( 'avg(oset.c_num_pastdue_total) over()'
1178: , 'avg(oset.c_num_pastdue_total) over()'
1179: , 'BIV_MEASURE4'
1180: , 'Y' ) || '

Line 1184: ' || isc_maint_rpt_util_pkg.detail_sql

1180: , 'Y' ) || '
1181: /* Grand Total Percent of Total */ ' || '
1182: , null BIV_ATTRIBUTE11
1183: from
1184: ' || isc_maint_rpt_util_pkg.detail_sql
1185: ( p_detail_col_tbl => l_detail_col_tbl
1186: , p_dimension_tbl => l_dimension_tbl
1187: , p_mv_name => l_mv
1188: , p_where_clause => l_where_clause

Line 1195: -- l_stmt := l_stmt || isc_maint_rpt_util_pkg.dump_parameters(p_param);

1191: , p_override_date_clause => '1 = 1 '
1192: );
1193:
1194: /* the next line can be used to dump the contents of the PMV parameters as comments into stmt */
1195: -- l_stmt := l_stmt || isc_maint_rpt_util_pkg.dump_parameters(p_param);
1196:
1197: l_orderby := 'ORDER BY VIEWBYID ASC';
1198:
1199: l_stmt := replace(l_stmt,'&ORDER_BY_CLAUSE',l_orderby);