[Home] [Help]
PACKAGE BODY: APPS.ISC_FS_TASK_ACT_BAC_RPT_PKG
Source
1 package body isc_fs_task_act_bac_rpt_pkg
2 /* $Header: iscfstkabrptb.pls 120.3 2006/04/12 20:46:04 kreardon noship $ */
3 as
4
5 g_detail_rep_func constant varchar2(50) := 'ISC_FS_TASK_BAC_STATUS_RPT_REP';
6 g_task_rep_func constant varchar2(50) := 'ISC_FS_TASK_ACT_BAC_TBL_REP';
7
8 function get_detail_drill
9 ( p_view_by in varchar2
10 , p_col_alias in varchar2
11 )
12 return varchar2
13 is
14 l_column_name varchar2(30);
15 begin
16
17 return
18 case
19 when p_view_by in ( isc_fs_rpt_util_pkg.G_PRODUCT
20 , isc_fs_rpt_util_pkg.G_CUSTOMER
21 , isc_fs_rpt_util_pkg.G_TASK_TYPE ) then
22 '''pFunctionName=' || g_detail_rep_func ||
23 '&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'''
24 when p_view_by = isc_fs_rpt_util_pkg.G_DISTRICT then
25 'decode(''-1'',&'|| isc_fs_rpt_util_pkg.G_DISTRICT_SHORT || ',null,decode(oset.record_type,''RESOURCE'',''pFunctionName=' || g_detail_rep_func ||
26 '&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'',null))'
27 else
28 'null'
29 end || ' ' || p_col_alias;
30
31 end get_detail_drill;
32
33 procedure get_tbl_sql
34 ( p_param in bis_pmv_page_parameter_tbl
35 , x_custom_sql out nocopy varchar2
36 , x_custom_output out nocopy bis_query_attributes_tbl
37 )
38 is
39
40 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
41 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
42 l_custom_output bis_query_attributes_tbl;
43 l_curr_suffix varchar2(3);
44 l_where_clause varchar2(10000);
45 l_viewby_select varchar2(400); -- needed to be increased from 200
46 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
47 l_dim_bmap number;
48 l_comparison_type varchar2(200);
49 l_xtd varchar2(200);
50 l_mv varchar2(10000);
51 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
52 l_view_by varchar2(200);
53 l_product varchar2(50);
54 l_stmt varchar2(32700);
55 l_to_date_type varchar2(200);
56
57 l_union_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
58 l_union_mv_rec poa_dbi_util_pkg.poa_dbi_mv_rec;
59
60 begin
61
62 isc_fs_rpt_util_pkg.register_dimension_levels
63 ( l_dimension_tbl
64 , l_dim_filter_map
65 , isc_fs_rpt_util_pkg.G_CATEGORY, 'Y'
66 , isc_fs_rpt_util_pkg.G_PRODUCT, 'Y'
67 , isc_fs_rpt_util_pkg.G_CUSTOMER, 'Y'
68 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
69 , isc_fs_rpt_util_pkg.G_TASK_TYPE, 'Y'
70 );
71
72 isc_fs_rpt_util_pkg.check_district_filter
73 ( p_param
74 , l_dim_filter_map
75 );
76
77 isc_fs_rpt_util_pkg.process_parameters
78 ( p_param => p_param
79 , p_dimension_tbl => l_dimension_tbl
80 , p_dim_filter_map => l_dim_filter_map
81 , p_trend => 'N'
82 , p_custom_output => l_custom_output
83 , x_cur_suffix => l_curr_suffix
84 , x_where_clause => l_where_clause
85 , x_viewby_select => l_viewby_select
86 , x_join_tbl => l_join_tbl
87 , x_dim_bmap => l_dim_bmap
88 , x_comparison_type => l_comparison_type
89 , x_xtd => l_xtd
90 );
91
92 if l_xtd in ('DAY','WTD','MTD','QTD','YTD') then
93 l_to_date_type := 'XTD';
94 else
95 l_to_date_type := 'RLX';
96 end if;
97
98 l_view_by := isc_fs_rpt_util_pkg.get_parameter_value
99 ( p_param
100 , 'VIEW_BY'
101 );
102
103 if l_view_by = isc_fs_rpt_util_pkg.G_PRODUCT then
104 l_product := 'v4.description ISC_ATTRIBUTE_2';
105 else
106 l_product := 'null ISC_ATTRIBUTE_2';
107 end if;
108
109 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
110
111 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
112 , p_col_name => 'backlog_count'
113 , p_alias_name => 'backlog'
114 , p_to_date_type => 'BAL'
115 , p_prior_code => poa_dbi_util_pkg.OPENING_PRIOR_CURR
116 );
117
118 l_union_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();
119 l_union_mv_rec.mv_name := 'ISC_FS_MV_PLACEHOLDER_1'; -- poa restrict this to 300 characters so we use a placeholder, see below
120 l_union_mv_rec.mv_col := l_col_tbl;
121 l_union_mv_rec.mv_where := l_where_clause;
122 l_union_mv_rec.in_join_tbls := null;
123 l_union_mv_rec.use_grp_id := 'N';
124 l_union_mv_tbl.extend();
125 l_union_mv_tbl(l_union_mv_tbl.count) := l_union_mv_rec;
126
127 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
128
129 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
130 , p_col_name => 'first_opened'
131 , p_alias_name => 'first_opened'
132 , p_to_date_type => l_to_date_type
133 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
134 );
135
136 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
137 , p_col_name => 'reopened'
138 , p_alias_name => 'reopened'
139 , p_to_date_type => l_to_date_type
140 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
141 );
142
143 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
144 , p_col_name => 'opened'
145 , p_alias_name => 'opened'
146 , p_to_date_type => l_to_date_type
147 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
148 );
149
150 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
151 , p_col_name => 'closed'
152 , p_alias_name => 'closed'
153 , p_to_date_type => l_to_date_type
154 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
155 );
156
157 l_union_mv_rec.mv_name := 'ISC_FS_MV_PLACEHOLDER_2'; -- poa restrict this to 300 characters so we use a placeholder, see below
158 l_union_mv_rec.mv_col := l_col_tbl;
159 l_union_mv_rec.mv_where := l_where_clause;
160 l_union_mv_rec.in_join_tbls := null;
161 l_union_mv_rec.use_grp_id := 'N';
162 l_union_mv_tbl.extend();
163 l_union_mv_tbl(l_union_mv_tbl.count) := l_union_mv_rec;
164
165 l_stmt := poa_dbi_template_pkg.union_all_status_sql
166 ( p_mv => l_union_mv_tbl
167 , p_join_tables => l_join_tbl
168 , p_use_windowing => 'Y'
169 , p_paren_count => 3
170 , p_filter_where => '(isc_measure_1 >0 or isc_measure_2>0 or isc_measure_4>0 or isc_measure_7>0 or isc_measure_8>0) ) iset '
171 , p_generate_viewby => 'Y'
172 );
173
174 l_mv := isc_fs_task_backlog_rpt_pkg.get_fact_mv_name
175 ( 'TASK_BACKLOG_STATUS'
176 , p_param
177 , l_dim_bmap
178 , l_custom_output
179 );
180
181 l_stmt := replace( l_stmt, 'ISC_FS_MV_PLACEHOLDER_1', l_mv );
182
183 l_mv := isc_fs_task_activity_rpt_pkg.get_fact_mv_name
184 ( 'TASK_ACTIVITY'
185 , p_param
186 , l_dim_bmap
187 , l_custom_output
188 );
189
190 l_stmt := replace( l_stmt, 'ISC_FS_MV_PLACEHOLDER_2', l_mv );
191
192 l_stmt := 'select
193 ' || l_viewby_select || '
194 , ISC_MEASURE_1
195 , ISC_MEASURE_2
196 , ISC_MEASURE_3
197 , ISC_MEASURE_4
198 , ISC_MEASURE_2 ISC_MEASURE_9
199 , ISC_MEASURE_5
200 , ISC_MEASURE_6
201 , ISC_MEASURE_7
202 , ISC_MEASURE_8
203 , ISC_MEASURE_21
204 , ISC_MEASURE_22
205 , ISC_MEASURE_23
206 , ISC_MEASURE_24
207 , ISC_MEASURE_25
208 , ISC_MEASURE_26
209 , ISC_MEASURE_27
210 , ISC_MEASURE_28
211 , ' || l_product || '
212 , ' || isc_fs_rpt_util_pkg.get_district_drill_down
213 ( l_view_by
214 , g_task_rep_func
215 , 'ISC_ATTRIBUTE_3' ) || '
216 , ' || isc_fs_rpt_util_pkg.get_category_drill_down
217 ( l_view_by
218 , g_task_rep_func
219 , 'ISC_ATTRIBUTE_4' ) || '
220 , ' || get_detail_drill( l_view_by, 'ISC_ATTRIBUTE_5' ) || '
221 from (
222 select
223 row_number() over(&ORDER_BY_CLAUSE nulls last, '|| isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || ')-1 rnk
224 , iset.*
225 from ( select * from (
226 select ' || isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || '
227 , nvl(p_backlog,0) ISC_MEASURE_1
228 , nvl(c_backlog,0) ISC_MEASURE_2
229 , ' || isc_fs_rpt_util_pkg.change_column
230 ( 'c_backlog'
231 , 'p_backlog'
232 , 'ISC_MEASURE_3' ) || '
233 , nvl(o_backlog,0) ISC_MEASURE_4
234 , nvl(c_first_opened,0) ISC_MEASURE_5
235 , nvl(c_reopened,0) ISC_MEASURE_6
236 , nvl(c_opened,0) ISC_MEASURE_7
237 , nvl(c_closed,0) ISC_MEASURE_8
238 , nvl(p_backlog_total,0) ISC_MEASURE_21
239 , nvl(c_backlog_total,0) ISC_MEASURE_22
240 , ' || isc_fs_rpt_util_pkg.change_column
241 ( 'c_backlog_total'
242 , 'p_backlog_total'
243 , 'ISC_MEASURE_23' ) || '
244 , nvl(o_backlog_total,0) ISC_MEASURE_24
245 , nvl(c_first_opened_total,0) ISC_MEASURE_25
246 , nvl(c_reopened_total,0) ISC_MEASURE_26
247 , nvl(c_opened_total,0) ISC_MEASURE_27
248 , nvl(c_closed_total,0) ISC_MEASURE_28
249 from (' || l_stmt;
250
251 isc_fs_rpt_util_pkg.enhance_time_join
252 ( l_stmt
253 , 'N'
254 );
255
256 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
257 -- l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
258
259 x_custom_sql := l_stmt;
260
261 poa_dbi_util_pkg.get_custom_balance_binds
262 ( p_custom_output => l_custom_output
263 , p_balance_fact => isc_fs_task_act_bac_etl_pkg.g_object_name
264 , p_xtd => l_xtd
265 );
266
267 poa_dbi_util_pkg.get_custom_rolling_binds
268 ( p_custom_output => l_custom_output
269 , p_xtd => l_xtd
270 );
271
272 x_custom_output := l_custom_output;
273
274 end get_tbl_sql;
275
276 end isc_fs_task_act_bac_rpt_pkg;