[Home] [Help]
PACKAGE BODY: APPS.ISC_FS_INV_RPT_PKG
Source
1 PACKAGE body ISC_FS_INV_RPT_PKG AS
2 /*$Header: iscfsinvrptb.pls 120.3 2006/05/08 14:42:36 kreardon noship $ */
3 function get_fact_mv_name
4 ( p_report_type in varchar2
5 , p_param in bis_pmv_page_parameter_tbl
6 , p_dim_bmap in number
7 , p_custom_output in out nocopy bis_query_attributes_tbl
8 )
9 return varchar2
10 is
11 l_comparison_type varchar2(20);
12 l_period_type varchar2(20);
13 begin
14
15 l_comparison_type:=isc_fs_rpt_util_pkg.get_parameter_value(p_param, 'TIME_COMPARISON_TYPE' ); /* YEARLY or SEQUENTIAL' */
16 l_period_type:=isc_fs_rpt_util_pkg.get_parameter_value(p_param, 'PERIOD_TYPE');
17
18 if p_report_type = 'INV_VALUE_1' then
19
20 isc_fs_rpt_util_pkg.bind_group_id
21 ( p_dim_bmap
22 , p_custom_output
23 , isc_fs_rpt_util_pkg.G_INV_CATEGORY
24 , isc_fs_rpt_util_pkg.G_ITEM_ORG
25 );
26
27 return '(
28 select f.record_type,f.parent_district_id
29 ,decode(f.record_type, ''GROUP'', f.district_id, f.resource_id ) district_id
30 ,district_id_c,f.inv_category_id,f.item_org_id,f.time_id,f.period_type_id,f.onhand_value_g Inv_usg_g,f.onhand_value_sg Inv_usg_sg
31 from isc_fs_015_mv f where f.grp_id = &ISC_GRP_ID)';
32
33 elsif p_report_type = 'INV_VALUE_2' then
34
35 isc_fs_rpt_util_pkg.bind_group_id
36 ( p_dim_bmap
37 , p_custom_output
38 , isc_fs_rpt_util_pkg.G_INV_CATEGORY
39 , isc_fs_rpt_util_pkg.G_ITEM_ORG
40 );
41
42
43 return '(
44 select
45 f.record_type,f.parent_district_id
46 ,decode(f.record_type, ''GROUP'', f.district_id, f.resource_id ) district_id
47 ,district_id_c
48 ,f.inv_category_id,f.item_org_id,f.time_id,f.period_type_id,f.uonhand_value_g
49 ,f.uonhand_value_sg,f.donhand_value_g,f.donhand_value_sg,nvl(f.uonhand_value_g,0) + nvl(f.donhand_value_g,0) totalonhand_value_g
50 ,nvl(f.uonhand_value_sg,0) + nvl(f.donhand_value_sg,0) totalonhand_value_sg
51 from isc_fs_016_mv f where f.grp_id = &ISC_GRP_ID)';
52
53 elsif p_report_type = 'FS_CALENDAR'
54 and l_comparison_type = 'YEARLY'
55 and l_period_type in ('FII_TIME_ENT_YEAR','FII_TIME_ENT_QTR','FII_TIME_ENT_PERIOD','FII_TIME_WEEK') /* Only used in XTD */
56 then
57
58 return '(select decode(grouping_id(c.ent_year_id,c.ent_qtr_id,c.ent_period_id,c.week_id,c.report_date_julian),0,c.report_date_julian,1,c.week_id,3,c.ent_period_id,7,c.ent_qtr_id,15,c.ent_year_id) time_id
59 ,decode(grouping_id(c.ent_year_id,c.ent_qtr_id,c.ent_period_id,c.week_id,c.report_date_julian),0,1,1,16,3,32,7,64,15,128) period_type_id
60 ,sum(CASE WHEN (t.start_date <= &ISC_TO_DATE and t.end_date >= &ISC_TO_DATE) and t.flag = ''C'' THEN &ISC_TO_DATE - t.start_date + 1
61 WHEN (t.start_date <= &BIS_PREVIOUS_ASOF_DATE and t.end_date >= &BIS_PREVIOUS_ASOF_DATE) and t.flag=''C'' THEN &BIS_PREVIOUS_ASOF_DATE-t.start_date+1
62 WHEN (t.flag=''P'') THEN t.end_date-&BIS_PREVIOUS_ASOF_DATE ' || /* Do not need +1 because start date row "C" already has it */
63 'ELSE t.end_date-t.start_date+1 END) period_days' ||
64 ' from fii_time_day c
65 ,(select ' || /* need this row to complete current number of days of the period before current period in the trend */
66 'tt.end_date start_date, tt.end_date end_date, ''P'' flag
67 from PERIOD_TABLE tt
68 where tt.start_date <= &BIS_PREVIOUS_ASOF_DATE and tt.end_date >= &BIS_PREVIOUS_ASOF_DATE
69 union all
70 select start_date,end_date,''C'' flag from PERIOD_TABLE ttt) t
71 where c.report_date = t.start_date and t.start_date <= &ISC_TO_DATE group by t.start_date,c.ent_year_id,rollup(c.ent_qtr_id,c.ent_period_id,c.week_id,c.report_date_julian))';
72
73 elsif p_report_type = 'FS_CALENDAR' /* When comparison is Sequential, the partial current period is compared to the complete prior period (Because of the line graph). */
74 and l_comparison_type = 'SEQUENTIAL'
75 and l_period_type in ('FII_TIME_ENT_YEAR','FII_TIME_ENT_QTR','FII_TIME_ENT_PERIOD','FII_TIME_WEEK') /* Only used in XTD */
76 then
77
78 return '(
79 select
80 decode(grouping_id(c.ent_year_id, c.ent_qtr_id, c.ent_period_id, c.week_id, c.report_date_julian), 0, c.report_date_julian, 1, c.week_id, 3, c.ent_period_id, 7, c.ent_qtr_id, 15, c.ent_year_id) time_id
81 ,decode(grouping_id(c.ent_year_id, c.ent_qtr_id , c.ent_period_id, c.week_id, c.report_date_julian), 0, 1, 1, 16, 3, 32, 7, 64, 15, 128) period_type_id
82 ,sum(CASE
83 WHEN (t.start_date <= &ISC_TO_DATE and t.end_date >= &ISC_TO_DATE) THEN
84 &ISC_TO_DATE - t.start_date + 1
85 ELSE t.end_date - t.start_date + 1
86 END) period_days
87 from
88 fii_time_day c
89 ,PERIOD_TABLE t
90 where
91 c.report_date = t.start_date and t.start_date <= &ISC_TO_DATE
92 group by
93 t.start_date
94 , c.ent_year_id
95 ,rollup(c.ent_qtr_id, c.ent_period_id, c.week_id, c.report_date_julian))';
96
97 else -- should not happen!!!
98 return '';
99
100 end if;
101
102 end get_fact_mv_name;
103
104
105 procedure get_tbl_sql
106 ( p_param in bis_pmv_page_parameter_tbl
107 , x_custom_sql out nocopy varchar2
108 , x_custom_output out nocopy bis_query_attributes_tbl
109 )
110 is
111
112 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
113 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
114 l_custom_output bis_query_attributes_tbl;
115 l_curr_suffix varchar2(3);
116 l_where_clause varchar2(10000);
117 l_viewby_select varchar2(400); -- needed to be increased from 200
118 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
119 l_dim_bmap number;
120 l_comparison_type varchar2(200);
121 l_xtd varchar2(200);
122 l_col_tbl1 poa_dbi_util_pkg.poa_dbi_col_tbl;
123 l_col_tbl2 poa_dbi_util_pkg.poa_dbi_col_tbl;
124 l_mv VARCHAR2 (10000);
125 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
126 l_view_by varchar2(200);
127 l_stmt varchar2(32700);
128 l_stmt1 varchar2(5000);
129 l_stmt2 varchar2(5000);
130 l_to_date varchar2(20);
131 l_to_date_type varchar2(20);
132 l_custom_rec BIS_QUERY_ATTRIBUTES;
133
134 begin
135
136 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
137
138 /* Specifying the report parameters, The Y means it is a filter besides a view by. */
139 isc_fs_rpt_util_pkg.register_dimension_levels
140 ( l_dimension_tbl
141 , l_dim_filter_map
142 , isc_fs_rpt_util_pkg.G_INV_CATEGORY, 'Y'
143 , isc_fs_rpt_util_pkg.G_ITEM_ORG, 'Y'
144 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
145 );
146
147 isc_fs_rpt_util_pkg.check_district_filter
148 ( p_param
149 , l_dim_filter_map
150 );
151
152 isc_fs_rpt_util_pkg.process_parameters
153 ( p_param => p_param
154 , p_dimension_tbl => l_dimension_tbl
155 , p_dim_filter_map => l_dim_filter_map
156 , p_trend => 'N'
157 , p_custom_output => l_custom_output
158 , x_cur_suffix => l_curr_suffix
159 , x_where_clause => l_where_clause
160 , x_viewby_select => l_viewby_select
161 , x_join_tbl => l_join_tbl
162 , x_dim_bmap => l_dim_bmap
163 , x_comparison_type => l_comparison_type
164 , x_xtd => l_xtd
165 );
166
167 l_view_by := isc_fs_rpt_util_pkg.get_parameter_value
168 ( p_param
169 , 'VIEW_BY'
170 );
171
172 /* To support Day and rolling */
173 if l_xtd in ('DAY','WTD','MTD','QTD','YTD') then
174 l_to_date_type := 'XTD';
175 else
176 l_to_date_type := 'RLX';
177 end if;
178
179 /* The to date is the least between the as of date and last collection date */
180 if fnd_date.displaydt_to_date(bis_collection_utilities.get_last_refresh_period('ISC_FS_016_MV')) <
181 to_date(isc_fs_rpt_util_pkg.get_parameter_value( p_param, 'AS_OF_DATE'),'DD-MM-YYYY') then
182 l_to_date := to_char(fnd_date.displaydt_to_date(bis_collection_utilities.get_last_refresh_period('ISC_FS_016_MV')),'DD/MM/YYYY');
183 else
184 l_to_date := to_char(to_date(isc_fs_rpt_util_pkg.get_parameter_value( p_param, 'AS_OF_DATE'),'DD-MM-YYYY') ,'DD/MM/YYYY');
185 end if;
186
187 l_col_tbl1 := poa_dbi_util_pkg.poa_dbi_col_tbl();
188 l_col_tbl2 := poa_dbi_util_pkg.poa_dbi_col_tbl();
189 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
190
191 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1
192 , p_col_name => 'Inv_usg_' || l_curr_suffix
193 , p_alias_name => 'Inv_usg'
194 , p_grand_total => 'Y'
195 , p_prior_code => poa_dbi_util_pkg.both_priors
196 , p_to_date_type => l_to_date_type
197 );
198
199 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl2
200 , p_col_name => 'uonhand_value_' || l_curr_suffix
201 , p_alias_name => 'uonhand_value'
202 , p_grand_total => 'Y'
203 , p_prior_code => poa_dbi_util_pkg.both_priors
204 , p_to_date_type => 'XTD'
205 );
206
207 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl2
208 , p_col_name => 'donhand_value_' || l_curr_suffix
209 , p_alias_name => 'donhand_value'
210 , p_grand_total => 'Y'
211 , p_prior_code => poa_dbi_util_pkg.no_priors
212 , p_to_date_type => 'XTD'
213 );
214
215 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl2
216 , p_col_name => 'totalonhand_value_' || l_curr_suffix
217 , p_alias_name => 'totalonhand_value'
218 , p_grand_total => 'Y'
219 , p_prior_code => poa_dbi_util_pkg.both_priors
220 , p_to_date_type => 'XTD'
221 );
222
223 l_mv_tbl.extend;
224 l_mv_tbl(1).mv_name := 'MV_PLACEHOLDER1';
225 l_mv_tbl(1).mv_col := l_col_tbl1;
226 l_mv_tbl(1).mv_where := l_where_clause;
227 l_mv_tbl(1).in_join_tbls := NULL;
228 l_mv_tbl(1).use_grp_id := 'N';
229
230 l_mv_tbl.extend;
231 l_mv_tbl(2).mv_name := 'MV_PLACEHOLDER2';
232 l_mv_tbl(2).mv_col := l_col_tbl2;
233 l_mv_tbl(2).mv_where := l_where_clause;
234 l_mv_tbl(2).in_join_tbls := NULL;
235 l_mv_tbl(2).use_grp_id := 'N';
236
237 l_stmt := poa_dbi_template_pkg.union_all_status_sql
238 (p_mv => l_mv_tbl
239 ,p_join_tables => l_join_tbl
240 ,p_use_windowing => 'Y'
241 ,p_paren_count => 3
242 ,p_filter_where => '(ISC_MEASURE_1<> 0 or ISC_MEASURE_3<> 0 or ISC_MEASURE_5<> 0 or
243 ISC_MEASURE_6<> 0 or ISC_MEASURE_7<> 0 or ISC_MEASURE_9<> 0 or ISC_MEASURE_11<> 0
244 or ISC_MEASURE_12<> 0 or ISC_MEASURE_13<> 0 or ISC_MEASURE_26<> 0
245 or ISC_MEASURE_27<> 0) ) iset'
246 ,p_generate_viewby => 'Y'
247 );
248
249 l_mv := get_fact_mv_name
250 ( 'INV_VALUE_1'
251 , p_param
252 , l_dim_bmap
253 , l_custom_output
254 );
255
256 l_stmt := replace( l_stmt, 'MV_PLACEHOLDER1', l_mv );
257
258 l_mv := get_fact_mv_name
259 ( 'INV_VALUE_2'
260 , p_param
261 , l_dim_bmap
262 , l_custom_output
263 );
264
265 l_stmt := replace( l_stmt, 'MV_PLACEHOLDER2', l_mv );
266
267 l_stmt1 := SUBSTR (l_stmt,1,INSTR(l_stmt,'UNION')-1);
268 l_stmt2 := SUBSTR (l_stmt,INSTR(l_stmt,'UNION'),LENGTH(l_stmt));
269
270 /* Replacing second part of the UNION all's NESTED PATTERN bind variable for the hard coded ITD patttern: 1143 */
271 l_stmt2 := replace( l_stmt2, '&BIS_NESTED_PATTERN', 1143 );
272
273 l_stmt := l_stmt1 || l_stmt2;
274
275 l_stmt := 'select
276 ' || l_viewby_select || '
277 , ' ||
278 case l_view_by
279 when isc_fs_rpt_util_pkg.G_ITEM_ORG
280 then 'v12.description'
281 else 'null'
282 end ||'
283 ISC_ATTRIBUTE_5
284 , ISC_MEASURE_1
285 , ISC_MEASURE_26 ISC_MEASURE_2
286 , ISC_MEASURE_7
287 , ISC_MEASURE_27 ISC_MEASURE_8
288 , ISC_MEASURE_11
289 , ISC_MEASURE_30
290 , ISC_MEASURE_31
291 , ISC_MEASURE_26
292 , ISC_MEASURE_3
293 , ISC_MEASURE_5
294 , ISC_MEASURE_6
295 , ISC_MEASURE_27
296 , ISC_MEASURE_9
297 , ISC_MEASURE_12
298 , ISC_MEASURE_13
299 , ISC_MEASURE_15
300 , ISC_MEASURE_16
301 , ISC_MEASURE_17
302 , ISC_MEASURE_18
303 , ISC_MEASURE_19
304 , ISC_MEASURE_20
305 , ISC_MEASURE_21
306 , ISC_MEASURE_22
307 , ISC_MEASURE_23
308 , ISC_MEASURE_24
309 , ISC_MEASURE_25
310 , ISC_MEASURE_16 ISC_MEASURE_39
311 , ISC_MEASURE_21 ISC_MEASURE_40
312 , ' || isc_fs_rpt_util_pkg.get_district_drill_down
313 ( l_view_by
314 , 'ISC_FS_INV_DOH_TBL_REP'
315 , 'ISC_ATTRIBUTE_4' ) || '
316 from (
317 select
318 row_number() over(&ORDER_BY_CLAUSE nulls last, '|| isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || ')-1 rnk
319 , iset.*
320 from ( select * from (
321 select ' || isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || '
322 , nvl(p_Inv_usg,0) ISC_MEASURE_1
323 , nvl(c_Inv_usg,0) ISC_MEASURE_26
324 , ' || isc_fs_rpt_util_pkg.change_column
325 ( 'c_Inv_usg'
326 , 'p_Inv_usg'
327 , 'ISC_MEASURE_3' ) || '
328 , nvl(c_uonhand_value,0) ISC_MEASURE_5
329 , nvl(c_donhand_value,0) ISC_MEASURE_6
330 , nvl(p_totalonhand_value,0) ISC_MEASURE_7
331 , nvl(c_totalonhand_value,0) ISC_MEASURE_27
332 , ' || isc_fs_rpt_util_pkg.change_column
333 ( 'c_totalonhand_value'
334 , 'p_totalonhand_value'
335 , 'ISC_MEASURE_9' ) || '
336 , p_uonhand_value/(decode(p_Inv_usg, 0, null, decode(&BIS_PREVIOUS_ASOF_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1,0,null,p_Inv_usg/(&BIS_PREVIOUS_ASOF_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1)))) ISC_MEASURE_11
337 , case when &BIS_PREVIOUS_ASOF_DATE <= &ISC_TO_DATE then (case when (&BIS_PREVIOUS_ASOF_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1) < 0 then 0 else (&BIS_PREVIOUS_ASOF_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1) end)
338 else (case when (&ISC_TO_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1) < 0 then 0 else (&ISC_TO_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1) end) end ISC_MEASURE_30
339 , case when (&ISC_TO_DATE - &BIS_CURRENT_EFFECTIVE_START_DATE + 1) < 0 then 0
340 else &ISC_TO_DATE - &BIS_CURRENT_EFFECTIVE_START_DATE + 1 end ISC_MEASURE_31
341 , c_uonhand_value*(case when (&ISC_TO_DATE - &BIS_CURRENT_EFFECTIVE_START_DATE + 1) < 0 then 0 else (&ISC_TO_DATE - &BIS_CURRENT_EFFECTIVE_START_DATE + 1) end)/(decode(c_Inv_usg, 0, null, c_Inv_usg)) ISC_MEASURE_12
342 , c_uonhand_value*(case when (&ISC_TO_DATE - &BIS_CURRENT_EFFECTIVE_START_DATE + 1) < 0 then 0 else (&ISC_TO_DATE - &BIS_CURRENT_EFFECTIVE_START_DATE + 1) end)/(decode(c_Inv_usg, 0, null, c_Inv_usg)) -
343 p_uonhand_value*(&BIS_PREVIOUS_ASOF_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1)/(decode(p_Inv_usg, 0, null, p_Inv_usg)) ISC_MEASURE_13
344 , nvl(p_Inv_usg_total,0) ISC_MEASURE_15
345 , nvl(c_Inv_usg_total,0) ISC_MEASURE_16
346 , ' || isc_fs_rpt_util_pkg.change_column
347 ( 'c_Inv_usg_total'
348 , 'p_Inv_usg_total'
349 , 'ISC_MEASURE_17' ) || '
350 , nvl(c_uonhand_value_total, 0) ISC_MEASURE_18
351 , nvl(c_donhand_value_total, 0) ISC_MEASURE_19
352 , nvl(p_totalonhand_value_total, 0) ISC_MEASURE_20
353 , nvl(c_totalonhand_value_total, 0) ISC_MEASURE_21
354 , ' || isc_fs_rpt_util_pkg.change_column
355 ( 'c_totalonhand_value_total'
356 , 'p_totalonhand_value_total'
357 , 'ISC_MEASURE_22' ) || '
358 , p_uonhand_value_total*(case when &BIS_PREVIOUS_ASOF_DATE <= &ISC_TO_DATE then (case when (&BIS_PREVIOUS_ASOF_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1) < 0 then 0 else (&BIS_PREVIOUS_ASOF_DATE -
359 &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1) end) else (case when (&ISC_TO_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1) < 0 then 0 else (&ISC_TO_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1) end)
360 end)/(decode(p_Inv_usg_total, 0, null, p_Inv_usg_total)) ISC_MEASURE_23
361 , c_uonhand_value_total*(case when (&ISC_TO_DATE - &BIS_CURRENT_EFFECTIVE_START_DATE + 1) < 0 then 0 else (&ISC_TO_DATE - &BIS_CURRENT_EFFECTIVE_START_DATE + 1) end)/(decode(c_Inv_usg_total, 0, null,c_Inv_usg_total)) ISC_MEASURE_24
362 , c_uonhand_value_total*(case when (&ISC_TO_DATE - &BIS_CURRENT_EFFECTIVE_START_DATE + 1) < 0 then 0 else (&ISC_TO_DATE - &BIS_CURRENT_EFFECTIVE_START_DATE + 1) end)/(decode(c_Inv_usg_total,
363 0, null,c_Inv_usg_total)) - p_uonhand_value_total*(case when &BIS_PREVIOUS_ASOF_DATE <= &ISC_TO_DATE then (case when (&BIS_PREVIOUS_ASOF_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1) < 0 then 0
364 else (&BIS_PREVIOUS_ASOF_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1) end)
365 else (case when (&ISC_TO_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1) < 0 then 0 else (&ISC_TO_DATE - &BIS_PREVIOUS_EFFECTIVE_START_DATE + 1) end) end)/(decode(p_Inv_usg_total, 0, null, p_Inv_usg_total)) ISC_MEASURE_25
366 from (' || l_stmt;
367
368
369 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
370 -- l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
371
372 poa_dbi_util_pkg.get_custom_rolling_binds
373 ( p_custom_output => l_custom_output
374 , p_xtd => l_xtd
375 );
376
377 x_custom_output := l_custom_output;
378
379 l_custom_rec.attribute_name := '&ISC_TO_DATE';
380 l_custom_rec.attribute_value := l_to_date;
381 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
382 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.date_bind;
383
384 x_custom_output.extend;
385 x_custom_output(x_custom_output.count) := l_custom_rec;
386
387 isc_fs_rpt_util_pkg.enhance_time_join
388 ( l_stmt
389 , 'N'
390 );
391
392 x_custom_sql := l_stmt;
393
394 end get_tbl_sql;
395
396
397 function get_period_days(l_period_type varchar2, l_curr_prior varchar2) return varchar2
398 is
399 begin
400
401 if l_curr_prior = 'P' and l_period_type like '%TD' then
402 return 'p_period_days';
403 elsif l_curr_prior = 'C' and l_period_type like '%TD' then
404 return 'c_period_days';
405 elsif (l_period_type like 'RL%' or l_period_type = 'DAY') and l_curr_prior = 'P' then
406
407 /* We do not support queries in the future (after the end date of the period where last collection date
408 falls into): If the query returns periods above the end date of the period where
409 the last collection date falls into, it will return 0 days. */
410 if(l_period_type = 'RLY') then
411 return '(CASE WHEN cal_start_date > &ISC_TO_DATE THEN 0
412 ELSE 365
413 END)';
414 elsif(l_period_type = 'RLQ') then
415 return '(CASE WHEN cal_start_date > &ISC_TO_DATE THEN 0
416 ELSE 90
417 END)';
418 elsif(l_period_type = 'RLM') then
419 return '(CASE WHEN cal_start_date > &ISC_TO_DATE THEN 0
420 ELSE 30
421 END)';
422 elsif(l_period_type = 'RLW') then
423 return '(CASE WHEN cal_start_date > &ISC_TO_DATE THEN 0
424 ELSE 7
425 END)';
426 elsif(l_period_type = 'DAY') then
427 return '1';
428 end if;
429
430 elsif (l_period_type like 'RL%' or l_period_type = 'DAY') and l_curr_prior = 'C' then
431 /* In the case that the user queries the report on days after the last collection date:
432 We need to substract the days after the last collection date to the end date of the period where
433 the last collection date falls into. If the query returns periods above the end date of the period where
434 the last collection date falls into, it should return 0 days.
435 Note: ISC_TO_DATE is the least between last collection date and as of date */
436 if(l_period_type = 'RLY') then
437 return '(CASE WHEN &ISC_TO_DATE BETWEEN cal_start_date AND cal_end_date
438 THEN 365 - (cal_end_date - &ISC_TO_DATE)
439 WHEN cal_start_date > &ISC_TO_DATE THEN 0
440 ELSE 365
441 END)';
442 elsif(l_period_type = 'RLQ') then
443 return '(CASE WHEN &ISC_TO_DATE BETWEEN cal_start_date AND cal_end_date
444 THEN 90 - (cal_end_date - &ISC_TO_DATE)
445 WHEN cal_start_date > &ISC_TO_DATE THEN 0
446 ELSE 90
447 END)';
448 elsif(l_period_type = 'RLM') then
449 return '(CASE WHEN &ISC_TO_DATE BETWEEN cal_start_date AND cal_end_date
450 THEN 30 - (cal_end_date - &ISC_TO_DATE)
451 WHEN cal_start_date > &ISC_TO_DATE THEN 0
452 ELSE 30
453 END)';
454 elsif(l_period_type = 'RLW') then
455 return '(CASE WHEN &ISC_TO_DATE BETWEEN cal_start_date AND cal_end_date
456 THEN 7 - (cal_end_date - &ISC_TO_DATE)
457 WHEN cal_start_date > &ISC_TO_DATE THEN 0
458 ELSE 7
459 END)';
460 elsif(l_period_type = 'DAY') then
461 return '1';
462 end if;
463
464 end if;
465
466 end get_period_days;
467
468
469 procedure get_trd_sql
470 ( p_param in bis_pmv_page_parameter_tbl
471 , x_custom_sql out nocopy varchar2
472 , x_custom_output out nocopy bis_query_attributes_tbl
473 )
474 is
475
476 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
477 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
478 l_custom_output bis_query_attributes_tbl;
479 l_curr_suffix varchar2(3);
480 l_where_clause varchar2(10000);
481 l_viewby_select varchar2(400); -- needed to be increased from 200
482 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
483 l_dim_bmap number;
484 l_comparison_type varchar2(200);
485 l_xtd varchar2(200);
486 l_mv varchar2(10000);
487 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
488 l_col_tbl1 poa_dbi_util_pkg.poa_dbi_col_tbl;
489 l_col_tbl2 poa_dbi_util_pkg.poa_dbi_col_tbl;
490 l_col_tbl3 poa_dbi_util_pkg.poa_dbi_col_tbl;
491 l_stmt varchar2(32767);
492 l_stmt1 varchar2(15000);
493 l_stmt2 varchar2(15000);
494 l_to_date varchar2(20);
495 l_to_date_type varchar2(20);
496 l_rxtd varchar2(3);
497 l_custom_rec BIS_QUERY_ATTRIBUTES;
498
499 begin
500
501 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
502
503 /* Specifying the report parameters, The Y means it is a filter besides a view by. */
504 isc_fs_rpt_util_pkg.register_dimension_levels
505 ( l_dimension_tbl
506 , l_dim_filter_map
507 , isc_fs_rpt_util_pkg.G_INV_CATEGORY, 'Y'
508 , isc_fs_rpt_util_pkg.G_ITEM_ORG, 'Y'
509 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
510 );
511
512 isc_fs_rpt_util_pkg.check_district_filter
513 ( p_param
514 , l_dim_filter_map
515 );
516
517 isc_fs_rpt_util_pkg.process_parameters
518 ( p_param => p_param
519 , p_dimension_tbl => l_dimension_tbl
520 , p_dim_filter_map => l_dim_filter_map
521 , p_trend => 'Y'
522 , p_custom_output => l_custom_output
523 , x_cur_suffix => l_curr_suffix
524 , x_where_clause => l_where_clause
525 , x_viewby_select => l_viewby_select
526 , x_join_tbl => l_join_tbl
527 , x_dim_bmap => l_dim_bmap
528 , x_comparison_type => l_comparison_type
529 , x_xtd => l_xtd
530 );
531
532 /* To support Day and rolling */
533 if l_xtd in ('DAY','WTD','MTD','QTD','YTD') then
534 l_to_date_type := 'XTD';
535 else
536 l_to_date_type := 'RLX';
537 end if;
538
539 /* The to date is the least between the as of date and last collection date */
540 if trunc(fnd_date.displaydt_to_date(bis_collection_utilities.get_last_refresh_period('ISC_FS_016_MV'))) <
541 to_date(isc_fs_rpt_util_pkg.get_parameter_value( p_param, 'AS_OF_DATE'),'DD-MM-YYYY') then
542 l_to_date := to_char(trunc(fnd_date.displaydt_to_date(bis_collection_utilities.get_last_refresh_period('ISC_FS_016_MV'))),'DD/MM/YYYY');
543 else
544 l_to_date := to_char(to_date(isc_fs_rpt_util_pkg.get_parameter_value( p_param, 'AS_OF_DATE'),'DD-MM-YYYY') ,'DD/MM/YYYY');
545 end if;
546
547 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
548
549 if l_xtd like '%TD' then /* Do not need the first leg of the union alls if rolling or day */
550
551 l_col_tbl1 := poa_dbi_util_pkg.poa_dbi_col_tbl();
552
553 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1
554 , p_col_name => 'Period_days'
555 , p_alias_name => 'Period_days'
556 , p_grand_total => 'N'
557 , p_prior_code => poa_dbi_util_pkg.both_priors
558 , p_to_date_type => l_to_date_type
559 );
560
561 l_mv_tbl.extend;
562 l_mv_tbl(l_mv_tbl.count).mv_name := 'MV_PLACEHOLDER1';
563 l_mv_tbl(l_mv_tbl.count).mv_col := l_col_tbl1;
564 l_mv_tbl(l_mv_tbl.count).mv_where := null;
565 l_mv_tbl(l_mv_tbl.count).in_join_tbls := NULL;
566 l_mv_tbl(l_mv_tbl.count).use_grp_id := 'N';
567 l_mv_tbl(l_mv_tbl.count).mv_xtd := l_xtd;
568
569 l_mv := get_fact_mv_name
570 ( 'FS_CALENDAR'
571 , p_param
572 , l_dim_bmap
573 , l_custom_output
574 );
575
576 l_mv := replace( l_mv, 'PERIOD_TABLE', poa_dbi_util_pkg.get_calendar_table(l_xtd));
577
578 end if;
579
580 l_col_tbl2 := poa_dbi_util_pkg.poa_dbi_col_tbl();
581 l_col_tbl3 := poa_dbi_util_pkg.poa_dbi_col_tbl();
582
583 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2
584 , p_col_name => 'Inv_usg_' || l_curr_suffix
585 , p_alias_name => 'Inv_usg'
586 , p_grand_total => 'N'
587 , p_prior_code => poa_dbi_util_pkg.both_priors
588 , p_to_date_type => l_to_date_type
589 );
590
591 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl3
592 , p_col_name => 'uonhand_value_' || l_curr_suffix
593 , p_alias_name => 'uonhand_value'
594 , p_grand_total => 'N'
595 , p_prior_code => poa_dbi_util_pkg.both_priors
596 , p_to_date_type => l_to_date_type /* This affects only the select list of columns structure for getting current and prior values */
597 );
598
599 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl3
600 , p_col_name => 'totalonhand_value_' || l_curr_suffix
601 , p_alias_name => 'totalonhand_value'
602 , p_grand_total => 'N'
603 , p_prior_code => poa_dbi_util_pkg.both_priors
604 , p_to_date_type => l_to_date_type /* This affects only the select list of columns structure for getting current and prior values */
605 );
606
607 l_mv_tbl.extend;
608 l_mv_tbl(l_mv_tbl.count).mv_name := 'MV_PLACEHOLDER2';
609 l_mv_tbl(l_mv_tbl.count).mv_col := l_col_tbl2;
610 l_mv_tbl(l_mv_tbl.count).mv_where := l_where_clause;
611 l_mv_tbl(l_mv_tbl.count).in_join_tbls := NULL;
612 l_mv_tbl(l_mv_tbl.count).use_grp_id := 'N';
613 l_mv_tbl(l_mv_tbl.count).mv_xtd := l_xtd;
614
615 l_mv_tbl.extend;
616 l_mv_tbl(l_mv_tbl.count).mv_name := 'MV_PLACEHOLDER3';
617 l_mv_tbl(l_mv_tbl.count).mv_col := l_col_tbl3;
618 l_mv_tbl(l_mv_tbl.count).mv_where := l_where_clause;
619 l_mv_tbl(l_mv_tbl.count).in_join_tbls := NULL;
620 l_mv_tbl(l_mv_tbl.count).use_grp_id := 'N';
621 l_mv_tbl(l_mv_tbl.count).mv_xtd := l_xtd; /* This determines what calendar tables to use and what FII struct table to use. For XTD is the FII_TIME_ENT tables and
622 fii_time_rpt_struct_v. If rolling, it will use the Rolling calendars inline views for week, month, quarter, year and fii_time_structures */
623
624 l_stmt := poa_dbi_template_pkg.union_all_trend_sql(
625 p_mv => l_mv_tbl,
626 p_comparison_type => l_comparison_type,
627 p_filter_where => NULL
628 );
629
630 if l_xtd like '%TD' then /* The query does not have the first leg of the union alls if rolling or day */
631 l_stmt := replace( l_stmt, 'MV_PLACEHOLDER1', l_mv );
632 end if;
633
634 l_mv := get_fact_mv_name
635 ( 'INV_VALUE_1'
636 , p_param
637 , l_dim_bmap
638 , l_custom_output
639 );
640
641 l_stmt := replace( l_stmt, 'MV_PLACEHOLDER2', l_mv );
642
643 l_mv := get_fact_mv_name
644 ( 'INV_VALUE_2'
645 , p_param
646 , l_dim_bmap
647 , l_custom_output
648 );
649
650 l_stmt := replace( l_stmt, 'MV_PLACEHOLDER3', l_mv );
651
652 if l_xtd like '%TD' then
653 l_stmt1 := SUBSTR (l_stmt, 1, INSTR(l_stmt,'UNION',1,2) - 1); /* if XTD there are 2 unions */
654 l_stmt2 := SUBSTR (l_stmt, INSTR(l_stmt,'UNION',1,2), LENGTH(l_stmt));
655
656 /* Replacing third part of the UNION all's NESTED PATTERN bind variable for the hard coded ITD patttern for fii_time_rpt_struct_v: 1143 */
657 l_stmt2 := replace( l_stmt2, '&BIS_NESTED_PATTERN', '1143');
658 elsif l_xtd like 'RL%' /* If rolling */ then
659 l_stmt1 := SUBSTR (l_stmt, 1, INSTR(l_stmt,'UNION',1,1) - 1); /* if rolling there is only 1 union */
660 l_stmt2 := SUBSTR (l_stmt, INSTR(l_stmt,'UNION',1,1), LENGTH(l_stmt));
661
662 /* Replacing second part of the UNION all's NESTED PATTERN bind variable for the hard coded ITD patttern for fii_time_structures: 512 */
663 l_stmt2 := replace( l_stmt2, '&RLX_NESTED_PATTERN', '512');
664 else /* DAY */
665 l_stmt1 := SUBSTR (l_stmt, 1, INSTR(l_stmt,'UNION',1,1) - 1); /* if DAY there is only 1 union */
666 l_stmt2 := SUBSTR (l_stmt, INSTR(l_stmt,'UNION',1,1), LENGTH(l_stmt));
667
668 /* Replacing second part of the UNION all's NESTED PATTERN bind variable for the hard coded ITD patttern for fii_time_rpt_struct_v: 1143 */
669 l_stmt2 := replace( l_stmt2, '&BIS_NESTED_PATTERN', '1143');
670 end if;
671
672 l_stmt := l_stmt1 || l_stmt2;
673
674 l_stmt := 'select
675 cal_name VIEWBY
676 , nvl(p_totalonhand_value,0) ISC_MEASURE_1
677 , nvl(p_Inv_usg,0) ISC_MEASURE_7
678 , (p_uonhand_value*' || get_period_days(l_xtd ,'P') ||')/decode(p_Inv_usg,0,null,p_Inv_usg) ISC_MEASURE_11
679 , ' || get_period_days(l_xtd ,'P') || ' ISC_MEASURE_30
680 , ' || get_period_days(l_xtd ,'C') || ' ISC_MEASURE_31
681 , nvl(c_totalonhand_value,0) ISC_MEASURE_2
682 , ' || isc_fs_rpt_util_pkg.change_column
683 ( 'c_totalonhand_value'
684 , 'p_totalonhand_value'
685 , 'ISC_MEASURE_3' ) || '
686 , nvl(c_Inv_usg,0) ISC_MEASURE_8
687 , ' || isc_fs_rpt_util_pkg.change_column
688 ( 'c_Inv_usg'
689 , 'p_Inv_usg'
690 , 'ISC_MEASURE_9' ) || '
691 , (c_uonhand_value*' || get_period_days(l_xtd ,'C') ||')/decode(c_Inv_usg,0,null,c_Inv_usg) ISC_MEASURE_12
692 , (c_uonhand_value*' || get_period_days(l_xtd ,'C') ||')/decode(c_Inv_usg,0,null,c_Inv_usg) - (p_uonhand_value*' || get_period_days(l_xtd ,'P') ||')/decode(p_Inv_usg,0,null,p_Inv_usg) ISC_MEASURE_13
693 ' ||
694 isc_fs_rpt_util_pkg.get_trend_drill
695 ( l_xtd
696 , 'ISC_FS_INV_TRD_REP'
697 , 'ISC_ATTRIBUTE_4'
698 , 'ISC_ATTRIBUTE_5'
699 , p_override_end_date => 'cal_end_date'
700 ) || '
701 from
702 ' || l_stmt;
703
704 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
705 -- l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
706
707 poa_dbi_util_pkg.get_custom_rolling_binds
708 ( p_custom_output => l_custom_output
709 , p_xtd => l_xtd
710 );
711
712 x_custom_output := l_custom_output;
713
714 poa_dbi_util_pkg.get_custom_trend_binds
715 ( x_custom_output => l_custom_output
716 , p_xtd => l_xtd
717 , p_comparison_type => l_comparison_type
718 );
719
720 if l_custom_output is not null then
721 for i in 1..l_custom_output.count loop
722 x_custom_output.extend;
723 x_custom_output(x_custom_output.count) := l_custom_output(i);
724 end loop;
725 end if;
726
727 l_custom_rec.attribute_name := '&ISC_TO_DATE';
728 l_custom_rec.attribute_value := l_to_date;
729 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
730 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.date_bind;
731
732 x_custom_output.extend;
733 x_custom_output(x_custom_output.count) := l_custom_rec;
734
735 /* The poa template does not bring up the cal_end_date needed for the Day drills and current period days in rolling, so we do it */
736 l_stmt := replace( l_stmt, ', cal.start_date cal_start_date', ', cal.start_date cal_start_date, cal.end_date cal_end_date');
737 l_stmt := replace( l_stmt, ', cal_start_date', ', cal_start_date, cal_end_date');
738
739 isc_fs_rpt_util_pkg.enhance_time_join
740 ( l_stmt
741 , 'Y'
742 );
743
744 x_custom_sql := l_stmt;
745
746 end get_trd_sql;
747
748
749 END ISC_FS_INV_RPT_PKG;