[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_PLAN_INV_T_TREND_PKG
Source
1 PACKAGE BODY ISC_DBI_PLAN_INV_T_TREND_PKG AS
2 /* $Header: ISCRGARB.pls 115.4 2004/01/30 07:57:06 chu noship $ */
3
4
5 PROCEDURE GET_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,x_custom_sql OUT NOCOPY VARCHAR2,
6 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
7
8 l_stmt VARCHAR2(32000);
9 l_plan VARCHAR2(10000);
10 l_plan2 VARCHAR2(10000);
11 l_org VARCHAR2(32000);
12 l_org_where VARCHAR2(32000);
13 l_period_type VARCHAR2(1000);
14 l_period_id VARCHAR2(1000);
15 l_inv_cat VARCHAR2(32000);
16 l_item VARCHAR2(32000);
17 l_inv_cat_where VARCHAR2(32000);
18 l_item_where VARCHAR2(32000);
19 l_item_cat_flag NUMBER; -- 0 for item, 1 for inv.cat, 3 for all
20 l_union_flag NUMBER:=0; -- for inventory turns report
21 l_time_from DATE;
22 l_time_to DATE;
23 l_cur_start DATE;
24 l_cur_end DATE;
25 l_pre_start DATE;
26 l_pre_end DATE;
27 l_period_type_id NUMBER:=32; --only select month buckets
28 l_loop NUMBER;
29 l_mon_num NUMBER; -- number of months in the selected period
30 l_custom_rec BIS_QUERY_ATTRIBUTES ;
31
32
33
34 BEGIN
35
36 FOR i IN 1..p_param.COUNT
37 LOOP
38 IF (p_param(i).parameter_name = 'PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT')
39 THEN l_plan := p_param(i).parameter_value;
40 END IF;
41
42 IF (p_param(i).parameter_name = 'PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2')
43 THEN l_plan2 := p_param(i).parameter_value;
44 END IF;
45
46 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
47 THEN l_org := p_param(i).parameter_value;
48 END IF;
49
50 IF(p_param(i).parameter_name = 'PERIOD_TYPE') THEN
51 l_period_type := p_param(i).parameter_value;
52 END IF;
53
54 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_INV_CAT') THEN
55 l_inv_cat := p_param(i).parameter_value;
56 END IF;
57
58 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG') THEN
59 l_item := p_param(i).parameter_value;
60 END IF;
61
62 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM') THEN
63 l_time_from := p_param(i).period_date;
64 END IF;
65
66 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_TO') THEN
67 l_time_to := p_param(i).period_date;
68 END IF;
69
70 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM') THEN
71 l_time_from := p_param(i).period_date;
72 END IF;
73
74 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_TO') THEN
75 l_time_to := p_param(i).period_date;
76 END IF;
77
78 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM') THEN
79 l_time_from := p_param(i).period_date;
80 END IF;
81
82 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_TO') THEN
83 l_time_to := p_param(i).period_date;
84 END IF;
85
86
87 END LOOP;
88
89
90 IF ( l_org IS NULL OR l_org = 'All' ) THEN
91 l_org_where := '
92 AND (EXISTS
93 (SELECT 1
94 FROM org_access o
95 WHERE o.responsibility_id = fnd_global.resp_id
96 AND o.resp_application_id = fnd_global.resp_appl_id
97 AND o.organization_id = f.organization_id)
98 OR EXISTS
99 (SELECT 1
100 FROM mtl_parameters org
101 WHERE org.organization_id = f.organization_id
102 AND NOT EXISTS
103 (SELECT 1
104 FROM org_access ora
105 WHERE org.organization_id = ora.organization_id)))';
106
107 ELSE
108 l_org_where := '
109 AND f.organization_id =(&ORGANIZATION+ORGANIZATION)';
110 END IF;
111
112
113 IF ( l_inv_cat IS NULL OR l_inv_cat = 'All' ) THEN
114 l_inv_cat_where :='';
115 ELSE
116 l_inv_cat_where := '
117 AND f.inv_category_id in (&ITEM+ENI_ITEM_INV_CAT)';
118 END IF;
119
120
121 IF ( l_item IS NULL OR l_item = 'All' )
122 THEN l_item_where := '';
123 ELSE l_item_where := '
124 AND f.item_id in (&ITEM+ENI_ITEM_ORG)';
125 END IF;
126
127 IF((l_inv_cat IS NULL OR l_inv_cat = 'All' ) AND ( l_item IS NULL OR l_item = 'All'))
128 THEN l_item_cat_flag := 3; -- no grouping on item dimension
129
130 ELSE
131 IF (l_item IS NULL OR l_item = 'All')
132 THEN l_item_cat_flag := 1; -- inv, category
133 ELSE l_item_cat_flag := 0; -- item is needed
134 END IF;
135 END IF;
136
137 IF l_period_type = 'FII_TIME_ENT_YEAR' THEN
138 l_loop :=3;
139 l_period_id := 'ent_year_id';
140 l_mon_num :=12;
141
142 ELSIF (l_period_type='FII_TIME_ENT_QTR') THEN
143 l_loop := 7;
144 l_period_id := 'ent_qtr_id';
145 l_mon_num :=3;
146
147 ELSE
148 l_loop :=11;
149 l_period_id := 'ent_period_id';
150 l_mon_num :=1;
151
152 END IF;
153
154
155 l_cur_start := l_time_from;
156 l_cur_end := l_time_from;
157
158 FOR i IN 1..l_loop
159 LOOP
160 l_cur_end := FII_TIME_API.next_period_end_date(l_cur_end, l_period_type);
161 END LOOP;
162
163 l_pre_start := FII_TIME_API.ent_pper_start(l_cur_start); -- get the previous month
164 l_pre_end := FII_TIME_API.ent_pper_end(l_cur_end); -- get the previous month
165
166
167 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
168 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
169
170 IF (l_plan IS NULL OR l_plan2 IS NULL)
171 THEN l_stmt := '
172 SELECT 0 ISC_MEASURE_1,
173 0 ISC_MEASURE_2,
174 0 ISC_MEASURE_3
175 FROM dual
176 WHERE 1 = 2 /* PLAN_SNAPSHOT dimension has not been populated */';
177 ELSE
178
179 l_stmt:='SELECT a.name VIEWBY,
180 sum(plan_inv_turns) ISC_MEASURE_1,
181 sum(comp_inv_turns) ISC_MEASURE_2,
182 sum(plan_inv_turns)-sum(comp_inv_turns) ISC_MEASURE_3
183 FROM(
184 SELECT fii.name,
185 fii.start_date,
186 s.period_id,
187 decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
188 sum(s.mds)*365/(fii.end_date - fii.start_date +1)/
189 decode(sign(sum(s.begin_inv+s.end_inv)),0,null,-1,null,
190 sum(s.begin_inv+s.end_inv)/2/:ISC_MON_NUM),null) plan_inv_turns,
191 decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
192 sum(s.mds)*365/(fii.end_date - fii.start_date +1)/
193 decode(sign(sum(s.begin_inv+s.end_inv)),0,null,-1,null,
194 sum(s.begin_inv+s.end_inv)/2/:ISC_MON_NUM),null) comp_inv_turns
195 FROM
196 (SELECT dates.'||l_period_id||' PERIOD_ID,
197 f.snapshot_id PLAN_ID,
198 sum(decode(dates.period_type,''P'',f.inventory_cost,0)) begin_inv,
199 sum(decode(dates.period_type,''C'',f.inventory_cost,0)) end_inv,
200 sum(decode(dates.period_type,''C'',f.mds_cost,0)) mds
201 FROM
202 (SELECT fii.start_date REPORT_DATE,
203 fii.start_date START_DATE,
204 ent_period_id ENT_PERIOD_ID,
205 ent_qtr_id ENT_QTR_ID,
206 ent_year_id ENT_YEAR_ID,
207 ''C'' PERIOD_TYPE
208 FROM FII_TIME_ENT_PERIOD fii
209 WHERE fii.start_date between :ISC_CUR_START and :ISC_CUR_END
210 UNION ALL
211 SELECT pre.start_date REPORT_DATE,
212 cur.start_date START_DATE,
213 cur.period_id ENT_PERIOD_ID,
214 cur.qtr_id ENT_QTR_ID,
215 cur.year_id ENT_YEAR_ID,
216 ''P'' PERIOD_TYPE
217 FROM
218 (SELECT fii.start_date START_DATE,
219 rownum ID
220 FROM FII_TIME_ENT_PERIOD fii
221 WHERE fii.start_date between :ISC_PRE_START and :ISC_PRE_END
222 ORDER by fii.start_date DESC) pre,
223 (SELECT fii.start_date START_DATE,
224 rownum ID,
225 ent_period_id PERIOD_ID,
226 ent_qtr_id QTR_ID,
227 ent_year_id YEAR_ID
228 FROM FII_TIME_ENT_PERIOD fii
229 WHERE fii.start_date between :ISC_CUR_START and :ISC_CUR_END
230 ORDER by fii.start_date DESC) cur
231 WHERE cur.id = pre.id(+)) dates,
232 ISC_DBI_PM_0001_MV f
233 WHERE f.start_date = dates.report_date
234 AND f.period_type_id = :ISC_PERIOD_TYPE_ID
235 AND f.union1_flag <> :ISC_UNION_FLAG
236 AND f.item_cat_flag = :ISC_ITEM_CAT_FLAG
237 AND f.snapshot_id in (&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2)
238 '||l_org_where||l_inv_cat_where||l_item_where||
239 'GROUP BY dates.'||l_period_id||',f.snapshot_id) s,
240 '|| l_period_type ||' fii
241 WHERE fii.'||l_period_id||' = s.period_id(+)
242 AND fii.start_date BETWEEN :ISC_CUR_START and :ISC_CUR_END
243 GROUP BY fii.name,fii.start_date,fii.end_date,s.period_id,s.plan_id) a
244 GROUP BY a.name, a.start_date
245 ORDER BY a.start_date';
246
247 END IF;
248
249 x_custom_sql := l_stmt;
250
251 l_custom_rec.attribute_name := ':ISC_PERIOD_TYPE_ID';
252 l_custom_rec.attribute_value := to_char(l_period_type_id);
253 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
254 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
255 x_custom_output.EXTEND;
256 x_custom_output(1) := l_custom_rec;
257
258 l_custom_rec.attribute_name := ':ISC_CUR_START';
259 l_custom_rec.attribute_value := to_char(l_cur_start,'DD/MM/YYYY');
260 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
261 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
262 x_custom_output.EXTEND;
263 x_custom_output(2) := l_custom_rec;
264
265 l_custom_rec.attribute_name := ':ISC_CUR_END';
266 l_custom_rec.attribute_value := to_char(l_cur_end,'DD/MM/YYYY');
267 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
268 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
269 x_custom_output.EXTEND;
270 x_custom_output(3) := l_custom_rec;
271
272 l_custom_rec.attribute_name := ':ISC_PRE_START';
273 l_custom_rec.attribute_value := to_char(l_pre_start,'DD/MM/YYYY');
274 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
275 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
276 x_custom_output.EXTEND;
277 x_custom_output(4) := l_custom_rec;
278
279 l_custom_rec.attribute_name := ':ISC_PRE_END';
280 l_custom_rec.attribute_value := to_char(l_pre_end,'DD/MM/YYYY');
281 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
282 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
283 x_custom_output.EXTEND;
284 x_custom_output(5) := l_custom_rec;
285
286 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
287 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
288 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
289 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
290 x_custom_output.EXTEND;
291 x_custom_output(6) := l_custom_rec;
292
293 l_custom_rec.attribute_name := ':ISC_UNION_FLAG';
294 l_custom_rec.attribute_value := to_char(l_union_flag);
295 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
296 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
297 x_custom_output.EXTEND;
298 x_custom_output(7) := l_custom_rec;
299
300 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
301 l_custom_rec.attribute_value := 'TIME+'||l_period_type;
302 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
303 x_custom_output.EXTEND;
304 x_custom_output(8) := l_custom_rec;
305
306 l_custom_rec.attribute_name := ':ISC_MON_NUM';
307 l_custom_rec.attribute_value := to_char(l_mon_num);
308 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
309 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
310 x_custom_output.EXTEND;
311 x_custom_output(9) := l_custom_rec;
312
313 END get_sql;
314
315 END ISC_DBI_PLAN_INV_T_TREND_PKG ;
316