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.
PACKAGE BODY: APPS.ISC_DBI_PLAN_INV_TURN_PKG

Source


1 PACKAGE BODY ISC_DBI_PLAN_INV_TURN_PKG AS
2 /* $Header: ISCRGAQB.pls 120.1 2006/06/26 06:34:16 abhdixi 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_inner_sql		VARCHAR2(32000);
12   l_view_by		VARCHAR2(32000);
13   l_org 		VARCHAR2(32000);
14   l_org_where     	VARCHAR2(32000);
15   l_period_type		VARCHAR2(1000);
16   l_inv_cat		VARCHAR2(32000);
17   l_item		VARCHAR2(32000);
18   l_inv_cat_where	VARCHAR2(32000);
19   l_item_where		VARCHAR2(32000);
20   l_item_cat_flag	NUMBER; -- 0 for item, 1 for inv.cat, 3 for all
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_lang		varchar2(10);
28   l_viewby_id		varchar2(100);
29   l_mon_num		NUMBER; -- number of months in the selected period
30   l_custom_rec 		BIS_QUERY_ATTRIBUTES ;
31   l_curr		VARCHAR2(10000);
32   l_curr_g		VARCHAR2(15) := '''FII_GLOBAL1''';
33   l_curr_g1		VARCHAR2(15) := '''FII_GLOBAL2''';
34   l_curr_suffix         VARCHAR2(15);
35 
36 BEGIN
37 
38   FOR i IN 1..p_param.COUNT
39   LOOP
40     IF (p_param(i).parameter_name = 'PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT')
41       THEN l_plan := p_param(i).parameter_value;
42     END IF;
43 
44     IF (p_param(i).parameter_name = 'PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2')
45       THEN l_plan2 := p_param(i).parameter_value;
46     END IF;
47 
48     IF( p_param(i).parameter_name= 'VIEW_BY')
49       THEN l_view_by := p_param(i).parameter_value;
50     END IF;
51 
52     IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
53       THEN l_org :=  p_param(i).parameter_value;
54     END IF;
55 
56     IF(p_param(i).parameter_name = 'PERIOD_TYPE') THEN
57        l_period_type :=  p_param(i).parameter_value;
58     END IF;
59 
60     IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_INV_CAT') THEN
61        l_inv_cat :=  p_param(i).parameter_value;
62     END IF;
63 
64     IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG') THEN
65        l_item :=  p_param(i).parameter_value;
66     END IF;
67 
68     IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM') THEN
69        l_time_from :=  p_param(i).period_date;
70     END IF;
71 
72     IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_TO') THEN
73        l_time_to :=  p_param(i).period_date;
74     END IF;
75 
76     IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM') THEN
77        l_time_from :=  p_param(i).period_date;
78     END IF;
79 
80     IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_TO') THEN
81        l_time_to :=  p_param(i).period_date;
82     END IF;
83 
84     IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM') THEN
85        l_time_from :=  p_param(i).period_date;
86     END IF;
87 
88     IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_TO') THEN
89        l_time_to :=  p_param(i).period_date;
90     END IF;
91 
92     IF (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
93       THEN l_curr := p_param(i).parameter_id;
94     END IF;
95 
96   END LOOP;
97 
98   IF (l_curr = l_curr_g)
99     THEN
100     	l_curr_suffix := '_g';
101     ELSIF (l_curr = l_curr_g1)
102 	THEN
103 		l_curr_suffix := '_g1';
104 	ELSE
105 		l_curr_suffix := '';
106   END IF;
107 
108   IF ( l_org IS NULL OR l_org = 'All' )
109     THEN l_org_where := '
110 	AND (EXISTS
111 		(SELECT 1
112 		FROM org_access o
113 		WHERE o.responsibility_id = fnd_global.resp_id
114 		AND o.resp_application_id = fnd_global.resp_appl_id
115 		AND o.organization_id = f.organization_id)
116 		OR EXISTS
117 		(SELECT 1
118 		FROM mtl_parameters org
119 		WHERE org.organization_id = f.organization_id
120 		AND NOT EXISTS
121 			(SELECT 1
122 			FROM org_access ora
123 			WHERE org.organization_id = ora.organization_id)))';
124 
125     ELSE l_org_where := '
126 		AND f.organization_id =(&ORGANIZATION+ORGANIZATION)';
127   END IF;
128 
129 
130   IF ( l_inv_cat IS NULL OR l_inv_cat = 'All' ) THEN
131     l_inv_cat_where :='';
132   ELSE
133     l_inv_cat_where := '
134 	AND f.inv_category_id in (&ITEM+ENI_ITEM_INV_CAT)';
135   END IF;
136 
137 
138   IF ( l_item IS NULL OR l_item = 'All' )
139   THEN l_item_where := '';
140   ELSE l_item_where := '
141 	AND f.item_id in (&ITEM+ENI_ITEM_ORG)';
142   END IF;
143 
144   IF (l_item IS NULL OR l_item = 'All')
145     THEN
146       IF (l_view_by = 'ITEM+ENI_ITEM_ORG')
147 	THEN l_item_cat_flag := 0; -- item
148       ELSIF (l_view_by = 'ITEM+ENI_ITEM_INV_CAT')
149         THEN l_item_cat_flag := 1; -- category
150       ELSE
151 	IF (l_inv_cat IS NULL OR l_inv_cat = 'All')
152 	  THEN l_item_cat_flag := 3; -- all
153 	ELSE l_item_cat_flag := 1; -- category
154 	END IF;
155       END IF;
156   ELSE
157     l_item_cat_flag := 0; -- item
158   END IF;
159 
160 
161   l_cur_start := l_time_from;
162   l_cur_end := l_time_to;
163   l_pre_start := FII_TIME_API.ent_pper_start(l_time_from); -- get the previous month
164   l_pre_end := FII_TIME_API.ent_pper_end(l_time_to); -- get the previous month
165 
166 
167   l_lang := USERENV('LANG');
168 
169 
170  IF l_period_type = 'FII_TIME_ENT_YEAR' THEN
171    l_mon_num :=12;
172 
173   ELSIF (l_period_type='FII_TIME_ENT_QTR') THEN
174    l_mon_num :=3;
175 
176   ELSE
177    l_mon_num :=1;
178 
179   END IF;
180 
181 
182   IF l_view_by = 'ORGANIZATION+ORGANIZATION' THEN
183     l_viewby_id :='organization_id';
184 
185   ELSIF l_view_by = 'ITEM+ENI_ITEM_ORG' THEN
186     l_viewby_id :='item_id';
187 
188   ELSIF l_view_by = 'ITEM+ENI_ITEM_INV_CAT' THEN
189     l_viewby_id :='inv_category_id';
190 
191   END if;
192 
193 
194   l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
195   x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
196 
197 
198   IF (l_plan IS NULL OR l_plan2 IS NULL)
199     THEN l_stmt := '
200 SELECT	0	VIEWBY,
201 	0	VIEWBYID,
202 	0	ISC_ATTRIBUTE_1,
203 	0 	ISC_MEASURE_7,
204 	0 	ISC_MEASURE_8,
205 	0 	ISC_MEASURE_1,
206 	0 	ISC_MEASURE_2,
207 	0 	ISC_MEASURE_3,
208 	0 	ISC_MEASURE_4,
209 	0 	ISC_MEASURE_5,
210 	0 	ISC_MEASURE_6,
211 	0 	ISC_MEASURE_9,
212 	0 	ISC_MEASURE_10
213   FROM	dual
214  WHERE	1 = 2 /* PLAN_SNAPSHOT dimension has not been populated */';
215     ELSE
216 
217   l_inner_sql:='ISC_MEASURE_7,ISC_MEASURE_8,ISC_MEASURE_1,ISC_MEASURE_2,ISC_MEASURE_3,
218 		ISC_MEASURE_4,ISC_MEASURE_5,
219 		ISC_MEASURE_4-ISC_MEASURE_5 ISC_MEASURE_6,
220 		ISC_MEASURE_9, ISC_MEASURE_10
221 	FROM(select (rank() over (&ORDER_BY_CLAUSE nulls last,viewby_id))-1 rnk,
222 		viewby_id,
223 		ISC_MEASURE_1,ISC_MEASURE_2,ISC_MEASURE_3,
224 		sum(ISC_MEASURE_7) over ()*365/(:ISC_CUR_END - :ISC_CUR_START+1)/
225 		decode(sign(sum(ISC_MEASURE_8) over()),0,null,-1,null,
226 		sum(ISC_MEASURE_8) over()) 				ISC_MEASURE_4,
227 		sum(comp_mds) over ()*365/(:ISC_CUR_END - :ISC_CUR_START+1)/
228 		decode(sign(sum(comp_avg_inv) over()),0,null,-1,null,
229 		sum(comp_avg_inv) over())				 ISC_MEASURE_5,
230 		ISC_MEASURE_7,ISC_MEASURE_8,
231 		sum(ISC_MEASURE_7) over ()				ISC_MEASURE_9,
232 		sum(ISC_MEASURE_8) over ()				ISC_MEASURE_10
233 	FROM (select viewby_id,
234 		sum(plan_inv_turns)					ISC_MEASURE_1,
235 		sum(comp_inv_turns)					ISC_MEASURE_2,
236 		sum(plan_inv_turns)-sum(comp_inv_turns)			ISC_MEASURE_3,
237 		sum(plan_mds_total)					ISC_MEASURE_7,
238 		sum(plan_avg_inv)					ISC_MEASURE_8,
239 		sum(comp_mds_total)					comp_mds,
240 		sum(comp_avg_inv)					comp_avg_inv
241 	FROM(	SELECT s.viewby_id,
242 		decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
243 		sum(s.mds)*365/(:ISC_CUR_END - :ISC_CUR_START+1)/
244 		decode(sign(sum(s.begin_inv+s.end_inv)),0,null,-1,null,
245 		sum(s.begin_inv+s.end_inv)/2/:ISC_MON_NUM),null) 		plan_inv_turns,
246 		decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
247 		sum(s.begin_inv+s.end_inv)/2/:ISC_MON_NUM,null) 		plan_avg_inv,
248 		decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
249 		sum(s.mds)*365/(:ISC_CUR_END - :ISC_CUR_START+1)/
250 		decode(sign(sum(s.begin_inv+s.end_inv)),0,null,-1,null,
251 		sum(s.begin_inv+s.end_inv)/2/:ISC_MON_NUM),null) 		comp_inv_turns,
252 		decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
253 		sum(s.mds),null)						plan_mds_total,
254 		decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
255 		sum(s.mds),null)						comp_mds_total,
256 		decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
257 		sum(s.begin_inv+s.end_inv)/2/:ISC_MON_NUM,null)			comp_avg_inv
258 		FROM
259 		(SELECT f.'||l_viewby_id||'	VIEWBY_ID,
260 		dates.start_date		PERIOD,
261 		f.snapshot_id			PLAN_ID,
262 		sum(decode(dates.period_type,''P'',f.inventory_cost'||l_curr_suffix||',0))	begin_inv,
263 		sum(decode(dates.period_type,''C'',f.inventory_cost'||l_curr_suffix||',0))	end_inv,
264 		sum(decode(dates.period_type,''C'',f.mds_cost'||l_curr_suffix||',0))	mds
265 		FROM
266 		(SELECT fii.start_date	START_DATE,
267 			fii.start_date  REPORT_DATE,
268 			''C''		PERIOD_TYPE
269 		 FROM FII_TIME_ENT_PERIOD fii
270 		 WHERE fii.start_date between :ISC_CUR_START and :ISC_CUR_END
271 		UNION ALL
272 		SELECT 	cur.start_date	start_date,
273 			pre.start_date  report_date,
274 			''P''		period_type
275 		FROM
276 		(SELECT fii.start_date	START_DATE,
277 		 rownum			ID
278 		 FROM FII_TIME_ENT_PERIOD fii
279 		 WHERE fii.start_date between :ISC_PRE_START and :ISC_PRE_END
280 		 ORDER by fii.start_date DESC)		pre,
281 		(SELECT fii.start_date	START_DATE,
282 		 rownum			ID
283 		 FROM FII_TIME_ENT_PERIOD fii
284 		 WHERE fii.start_date between :ISC_CUR_START and :ISC_CUR_END
285 		 ORDER by fii.start_date DESC)		cur
286 		WHERE cur.id = pre.id(+))	dates,
287 		ISC_DBI_PM_0001_MV f
288 		WHERE f.start_date = dates.report_date
289 		AND f.period_type_id = 32
290 		AND f.union1_flag <> 0
291 		AND item_cat_flag = :ISC_ITEM_CAT_FLAG
292 		AND f.snapshot_id in (&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2)
293 		'||l_org_where||l_inv_cat_where||l_item_where||
294 		'GROUP BY f.'||l_viewby_id||',dates.start_date,f.snapshot_id) s
295 		GROUP BY s.viewby_id,s.plan_id) c
296 		GROUP BY c.viewby_id)
297 		WHERE (ISC_MEASURE_7 <>0 OR ISC_MEASURE_8 <>0)
298 		OR (comp_mds <> 0 OR comp_avg_inv <>0)) a,';
299 
300   IF l_view_by = 'ORGANIZATION+ORGANIZATION' THEN
301     l_stmt := 'SELECT org.name				VIEWBY,
302 		org.organization_id			VIEWBYID,
303 		null					ISC_ATTRIBUTE_1,
304 		'||l_inner_sql||'
305 		HR_ALL_ORGANIZATION_UNITS_TL org
306 		WHERE org.organization_id = a.viewby_id
307 		AND org.language = :ISC_LANG
308 		AND ((a.rnk between &START_INDEX and &END_INDEX) OR &END_INDEX=-1)
309 		ORDER BY rnk';
310 
311   ELSIF l_view_by = 'ITEM+ENI_ITEM_ORG' THEN
312     l_stmt :='SELECT items.value	VIEWBY,
313 		items.id		VIEWBYID,
314 		items.description	ISC_ATTRIBUTE_1,
315 		'||l_inner_sql||'
316 		ENI_ITEM_ORG_V items
317   		WHERE a.viewby_id = items.id
318 		AND((a.rnk between &START_INDEX and &END_INDEX) OR &END_INDEX=-1)
319 		ORDER BY rnk';
320 
321   ELSIF l_view_by = 'ITEM+ENI_ITEM_INV_CAT' THEN
322     l_stmt := 'SELECT 	ecat.value 	VIEWBY,
323 		ecat.id			VIEWBYID,
324 		null			ISC_ATTRIBUTE_1,
325 		'||l_inner_sql||'
326  		ENI_ITEM_INV_CAT_V 	ecat
327 		WHERE a.viewby_id = ecat.id
328 		AND ((a.rnk between &START_INDEX and &END_INDEX) OR &END_INDEX=-1)
329   		ORDER BY rnk';
330   END IF;
331   END IF;
332 
333   x_custom_sql := l_stmt;
334 
335   l_custom_rec.attribute_name := ':ISC_CUR_START';
336   l_custom_rec.attribute_value := to_char(l_cur_start,'DD/MM/YYYY');
337   l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
338   l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
339   x_custom_output.EXTEND;
340   x_custom_output(1) := l_custom_rec;
341 
342   l_custom_rec.attribute_name := ':ISC_CUR_END';
343   l_custom_rec.attribute_value := to_char(l_cur_end,'DD/MM/YYYY');
344   l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
345   l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
346   x_custom_output.EXTEND;
347   x_custom_output(2) := l_custom_rec;
348 
349   l_custom_rec.attribute_name := ':ISC_PRE_START';
350   l_custom_rec.attribute_value := to_char(l_pre_start,'DD/MM/YYYY');
351   l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
352   l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
353   x_custom_output.EXTEND;
354   x_custom_output(3) := l_custom_rec;
355 
356   l_custom_rec.attribute_name := ':ISC_PRE_END';
357   l_custom_rec.attribute_value := to_char(l_pre_end,'DD/MM/YYYY');
358   l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
359   l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
360   x_custom_output.EXTEND;
361   x_custom_output(4) := l_custom_rec;
362 
363   l_custom_rec.attribute_name := ':ISC_LANG';
364   l_custom_rec.attribute_value := l_lang;
365   l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
366   l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
367   x_custom_output.EXTEND;
368   x_custom_output(5) := l_custom_rec;
369 
370   l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
371   l_custom_rec.attribute_value := to_char(l_item_cat_flag);
372   l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
373   l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
374   x_custom_output.EXTEND;
375   x_custom_output(6) := l_custom_rec;
376 
377   l_custom_rec.attribute_name := ':ISC_MON_NUM';
378   l_custom_rec.attribute_value := to_char(l_mon_num);
379   l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
380   l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
381   x_custom_output.EXTEND;
382   x_custom_output(7) := l_custom_rec;
383 
384 
385 END get_sql;
386 
387 END ISC_DBI_PLAN_INV_TURN_PKG ;
388