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_SHIP_PERF_PKG

Source


1 PACKAGE BODY ISC_DBI_SHIP_PERF_PKG AS
2 /* $Header: ISCRG65B.pls 120.2 2006/06/26 06:09:10 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_formula_sql		VARCHAR2(32000);
9   l_inner_sql		VARCHAR2(32000);
10   l_outer_sql		VARCHAR2(32000);
11   l_stmt 		VARCHAR2(32000);
12   l_view_by		VARCHAR2(32000);
13   l_org 		VARCHAR2(32000);
14   l_org_where     	VARCHAR2(32000);
15   l_item		VARCHAR2(32000);
16   l_item_where		VARCHAR2(32000);
17   l_inv_cat		VARCHAR2(32000);
18   l_inv_cat_where	VARCHAR2(32000);
19   l_item_cat_flag	NUMBER; -- 0 for product and 1 for product category
20   l_cust_flag		NUMBER; -- 0 for customer level, 1 for no-customer level
21   l_agg_level		NUMBER;
22   l_cust		VARCHAR2(32000);
23   l_cust_where		VARCHAR2(32000);
24   l_custom_rec 		BIS_QUERY_ATTRIBUTES ;
25   l_lang		varchar2(10);
26   l_period_type		varchar2(30);
27 
28 
29 BEGIN
30 
31   FOR i IN 1..p_param.COUNT
32   LOOP
33     IF( p_param(i).parameter_name= 'VIEW_BY')
34       THEN l_view_by := p_param(i).parameter_value;
35     END IF;
36 
37     IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
38       THEN l_org :=  p_param(i).parameter_value;
39     END IF;
40 
41     IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_INV_CAT') THEN
42        l_inv_cat :=  p_param(i).parameter_value;
43     END IF;
44 
45     IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG') THEN
46        l_item :=  p_param(i).parameter_value;
47     END IF;
48 
49     IF(p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS') THEN
50        l_cust :=  p_param(i).parameter_value;
51     END IF;
52 
53     IF(p_param(i).parameter_name = 'PERIOD_TYPE') THEN
54        l_period_type :=  p_param(i).parameter_value;
55     END IF;
56 
57   END LOOP;
58 
59 
60   IF ( l_org IS NULL OR l_org = 'All' ) THEN
61     l_org_where := '
62 	AND (EXISTS
63 		(SELECT 1
64 		FROM org_access o
65 		WHERE o.responsibility_id = fnd_global.resp_id
66 		AND o.resp_application_id = fnd_global.resp_appl_id
67 		AND o.organization_id = f.inv_org_id)
68 		OR EXISTS
69 		(SELECT 1
70 		FROM mtl_parameters org
71 		WHERE org.organization_id = f.inv_org_id
72 		AND NOT EXISTS
73 			(SELECT 1
74 			FROM org_access ora
75 			WHERE org.organization_id = ora.organization_id)))';
76 
77   ELSE
78     l_org_where := '
79 		AND f.inv_org_id = &ORGANIZATION+ORGANIZATION';
80   END IF;
81 
82 
83   IF ( l_inv_cat IS NULL OR l_inv_cat = 'All' ) THEN
84     l_inv_cat_where :='';
85   ELSE
86     l_inv_cat_where := '
87 	AND f.item_category_id in (&ITEM+ENI_ITEM_INV_CAT)';
88   END IF;
89 
90 
91   IF ( l_item IS NULL OR l_item = 'All' )
92   THEN l_item_where := '';
93   ELSE l_item_where := '
94 		AND f.item_id in (&ITEM+ENI_ITEM_ORG)';
95   END IF;
96 
97   IF (l_cust IS NULL OR l_cust = 'All') THEN
98     l_cust_where:='';
99 
100     IF(l_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
101        l_cust_flag := 0;
102     ELSE
103        l_cust_flag := 1; -- do not need customer id
104     END IF;
105   ELSE
106     l_cust_where :='
107 	AND f.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
108     l_cust_flag := 0; -- customer level
109   END IF;
110 
111 
112   IF (l_item IS NULL OR l_item = 'All')
113     THEN
114       IF (l_view_by = 'ITEM+ENI_ITEM_ORG')
115 	THEN l_item_cat_flag := 0; -- item
116       ELSIF (l_view_by = 'ITEM+ENI_ITEM_INV_CAT')
117         THEN l_item_cat_flag := 1; -- category
118       ELSE
119 	IF (l_inv_cat IS NULL OR l_inv_cat = 'All')
120 	  THEN l_item_cat_flag := 3; -- all
121 	ELSE l_item_cat_flag := 1; -- category
122 	END IF;
123       END IF;
124   ELSE
125     l_item_cat_flag := 0; -- item
126   END IF;
127 
128   CASE
129     WHEN (l_item_cat_flag = 0 and l_cust_flag = 0) THEN l_agg_level := 0;
130     WHEN (l_item_cat_flag = 1 and l_cust_flag = 0) THEN l_agg_level := 4;
131     WHEN (l_item_cat_flag = 3 and l_cust_flag = 0) THEN l_agg_level := 2;
132     WHEN (l_item_cat_flag = 0 and l_cust_flag = 1) THEN l_agg_level := 1;
133     WHEN (l_item_cat_flag = 1 and l_cust_flag = 1) THEN l_agg_level := 5;
134     WHEN (l_item_cat_flag = 3 and l_cust_flag = 1) THEN l_agg_level := 3;
135   END CASE;
136 
137 
138   l_lang := USERENV('LANG');
139 
140   l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
141   x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
142 
143   l_formula_sql := '
144 	c.curr_shipped					ISC_MEASURE_1,
145 	c.prev_shipped					ISC_MEASURE_2,
146 	(c.curr_shipped-c.prev_shipped)/
147 		decode(c.prev_shipped,0, null,
148 			abs(c.prev_shipped))*100	ISC_MEASURE_3,-- lines shipped change
149         c.curr_late_schedule/
150 		decode(c.curr_shipped,0,null,
151 			c.curr_shipped)*100		ISC_MEASURE_4, -- lines late to schedule
152         c.prev_late_schedule/
153 		decode(c.prev_shipped,0,null,
154 			c.prev_shipped)*100		ISC_MEASURE_5, -- prev. lines late to schedule
155 	(c.curr_late_schedule/
156 		decode(c.curr_shipped,0,null,
157 			c.curr_shipped)*100)-
158 		(c.prev_late_schedule/
159 		decode(c.prev_shipped,0,null,
160 			c.prev_shipped)*100)		ISC_MEASURE_6, -- lines late to schedule change
161         c.curr_late_promise/
162 		decode(c.curr_shipped,0,null,
163 			c.curr_shipped)*100		ISC_MEASURE_7, -- lines late to promise
164 	c.prev_late_promise/
165 		decode(c.prev_shipped,0,null,
166 			c.prev_shipped)*100		ISC_MEASURE_8, -- prev. late to promise
167 	(c.curr_late_promise/
168 		decode(c.curr_shipped,0,null,
169 			c.curr_shipped)*100)-
170 		(c.prev_late_promise/
171 		decode(c.prev_shipped,0,null,
172 			c.prev_shipped)*100)		ISC_MEASURE_9, -- line late to promise change
173 	sum(c.curr_shipped) over ()			ISC_MEASURE_10, -- grand total lines shipped
174 	(sum(c.curr_shipped) over()-sum(c.prev_shipped) over())/
175 		decode(sum(c.prev_shipped) over (),
176 		0,null,abs(sum(c.prev_shipped) over()))*100	ISC_MEASURE_11, -- grand total lines shipped change
177  	sum(c.curr_late_schedule) over()/
178 		decode(sum(c.curr_shipped) over(),
179 		0,null,sum(c.curr_shipped) over()) *100	ISC_MEASURE_12, -- grand total lines late to schedule
180 	(sum(c.curr_late_schedule) over()/
181 		decode(sum(c.curr_shipped) over(),
182 		0,null,sum(c.curr_shipped) over()))*100
183 	-(sum(c.prev_late_schedule) over()/
184 		decode(sum(c.prev_shipped) over(),
185 		0,null,sum(c.prev_shipped) over()))*100	ISC_MEASURE_13, -- grand total lines late to schedule change
186 	sum(c.curr_late_promise) over()/
187 		decode(sum(c.curr_shipped) over(),
188 		0,null,sum(c.curr_shipped) over()) *100	ISC_MEASURE_14, -- grand total lines late to promise
189 	(sum(c.curr_late_promise) over()/
190 		decode(sum(c.curr_shipped) over(),
191 		0,null,sum(c.curr_shipped) over()))*100
192 	-(sum(c.prev_late_promise) over()/
193 		decode(sum(c.prev_shipped) over(),
194 		0,null,sum(c.prev_shipped) over()))*100	ISC_MEASURE_15, -- grand total lines late to promise change
195 	(sum(c.prev_late_schedule) over()/
196 		decode(sum(c.prev_shipped) over(),
197 		0,null,sum(c.prev_shipped) over()))*100	ISC_MEASURE_16, -- grand total lines late to schedule prior(KPI)
198 	(sum(c.prev_late_promise) over()/
199 		decode(sum(c.prev_shipped) over(),
200 		0,null,sum(c.prev_shipped) over()))*100	ISC_MEASURE_17, -- grand total lines late to promise prior(KPI)
201 	c.curr_shipped					ISC_MEASURE_18, -- lines shipped for KPI
202         c.curr_late_schedule/
203 		decode(c.curr_shipped,0,null,
204 			c.curr_shipped)*100		ISC_MEASURE_19, -- Lines late to schedule KPI,
205         c.curr_late_promise/
206 		decode(c.curr_shipped,0,null,
207 			c.curr_shipped)*100		ISC_MEASURE_20, -- Lines late to promise KPI
208 	c.qty_shipped					ISC_MEASURE_21,
209  	sum(c.curr_late_schedule) over()/
210 		decode(sum(c.curr_shipped) over(),
211 		0,null,sum(c.curr_shipped) over()) *100	ISC_MEASURE_22, -- grand total lines late to schedule KPI
212 	sum(c.curr_late_promise) over()/
213 		decode(sum(c.curr_shipped) over(),
214 		0,null,sum(c.curr_shipped) over()) *100	ISC_MEASURE_23, -- grand total lines late to promise KPI
215 	sum(c.curr_shipped) over ()			ISC_MEASURE_24, -- grand total lines shipped KPI
216 	sum(c.prev_shipped) over ()			ISC_MEASURE_25, -- grand total lines shipped prev KPI
217 	null						CURRENCY	-- obsolted items from DBI 5.0';
218 IF(l_period_type = 'FII_TIME_DAY')
219 -- Day reporting
220 THEN
221   l_inner_sql:= 'sum(decode(to_date(f.time_id,''j''),&BIS_CURRENT_ASOF_DATE,f.shipped_line_cnt,0))	CURR_SHIPPED,
222 		sum(decode(to_date(f.time_id,''j''),&BIS_PREVIOUS_ASOF_DATE,f.shipped_line_cnt,0))	PREV_SHIPPED,
223 		sum(decode(to_date(f.time_id,''j''),&BIS_CURRENT_ASOF_DATE,f.late_line_cnt,0))		CURR_LATE_SCHEDULE,
224 		sum(decode(to_date(f.time_id,''j''),&BIS_PREVIOUS_ASOF_DATE,f.late_line_cnt,0))		PREV_LATE_SCHEDULE,
225 		sum(decode(to_date(f.time_id,''j''),&BIS_CURRENT_ASOF_DATE,f.late_line_promise_cnt,0))	CURR_LATE_PROMISE,
226 		sum(decode(to_date(f.time_id,''j''),&BIS_PREVIOUS_ASOF_DATE,f.late_line_promise_cnt,0))	PREV_LATE_PROMISE,
227 		sum(decode(to_date(f.time_id,''j''),&BIS_CURRENT_ASOF_DATE,f.shipped_qty, 0))		QTY_SHIPPED
228         FROM ISC_DBI_FM_0000_MV		f
229      	WHERE f.time_id in
230 	(to_char(&BIS_CURRENT_ASOF_DATE,''j''),to_char(&BIS_PREVIOUS_ASOF_DATE,''j''))
231 	AND f.period_type_id = 1
232 	AND f.agg_level = :ISC_AGG_LEVEL';
233 
234 ELSE
235 -- For Week, Month, Quarter, Year reporting
236   l_inner_sql:= 'sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,f.shipped_line_cnt,0))	CURR_SHIPPED,
237 		sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,f.shipped_line_cnt,0))	PREV_SHIPPED,
238 		sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,f.late_line_cnt,0))		CURR_LATE_SCHEDULE,
239 		sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,f.late_line_cnt,0))		PREV_LATE_SCHEDULE,
240 		sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,f.late_line_promise_cnt,0))	CURR_LATE_PROMISE,
241 		sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,f.late_line_promise_cnt,0))	PREV_LATE_PROMISE,
242 		sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,f.shipped_qty, 0))		QTY_SHIPPED
243         FROM ISC_DBI_FM_0000_MV		f,
244 		FII_TIME_RPT_STRUCT_V	cal
245      	WHERE f.time_id = cal.time_id
246 	AND f.agg_level = :ISC_AGG_LEVEL
247 	AND cal.report_date in(&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
248 	AND cal.period_type_id = f.period_type_id
249 	AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) =
250 	cal.record_type_id ';
251 
252 END IF;
253 
254 l_inner_sql := l_inner_sql||l_org_where||l_inv_cat_where||l_item_where||l_cust_where;
255 
256  l_outer_sql:= 'ISC_MEASURE_21,ISC_MEASURE_1,ISC_MEASURE_2,ISC_MEASURE_3,ISC_MEASURE_4,ISC_MEASURE_5,
257 		ISC_MEASURE_6,ISC_MEASURE_7,ISC_MEASURE_8,ISC_MEASURE_9,ISC_MEASURE_10,
258 		ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13,ISC_MEASURE_14,ISC_MEASURE_15,
259 		ISC_MEASURE_16,ISC_MEASURE_17,ISC_MEASURE_18,ISC_MEASURE_19,ISC_MEASURE_20,
260 	        ISC_MEASURE_22,ISC_MEASURE_23,ISC_MEASURE_24,ISC_MEASURE_25,CURRENCY';
261 
262   IF l_view_by = 'ORGANIZATION+ORGANIZATION' THEN
263      l_stmt := 'SELECT org.name 	VIEWBY,
264 		org.organization_id	VIEWBYID,
265 		null			ISC_ATTRIBUTE_2,
266 		null			ISC_ATTRIBUTE_3,
267 		org.organization_id	ISC_ATTRIBUTE_4,
268 		'||l_outer_sql||'
269 		FROM(select
270 		(rank() over (&ORDER_BY_CLAUSE nulls last,inv_org_id))-1 rnk,
271 		inv_org_id,
272 		'||l_outer_sql||'
273 		FROM (select inv_org_id,
274 		'||l_formula_sql||'
275 		FROM (select f.inv_org_id INV_ORG_ID,
276 		'||l_inner_sql||'
277 		GROUP BY f.inv_org_id) c)) a,
278 		HR_ALL_ORGANIZATION_UNITS_TL org
279 		WHERE org.organization_id = a.inv_org_id
280 		AND org.language = :ISC_LANG
281 		AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
282   		ORDER BY rnk';
283 
284   ELSIF l_view_by = 'ITEM+ENI_ITEM_ORG' THEN
285      l_stmt := 'SELECT items.value	VIEWBY,
286 		items.id		VIEWBYID,
287 		items.id 		ISC_ATTRIBUTE_4,
288 		items.description	ISC_ATTRIBUTE_2,
289 		uom.unit_of_measure	ISC_ATTRIBUTE_3,
290 		'||l_outer_sql||'
291 		FROM(select (rank() over (&ORDER_BY_CLAUSE nulls last,item_id))-1 rnk,
292 		item_id,uom,
293 		'||l_outer_sql||'
294 		FROM (select item_id, uom,
295 		'||l_formula_sql||'
296 		FROM (select f.item_id ITEM_ID,f.uom,
297 		'||l_inner_sql||'
298 		GROUP by f.item_id,f.uom) c)) a,
299 		 ENI_ITEM_ORG_V items,
300 		 MTL_UNITS_OF_MEASURE_TL uom
301   		WHERE a.item_id = items.id
302 		AND uom.uom_code= a.uom
303 		AND uom.language = :ISC_LANG
304 		AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
305 		ORDER BY rnk';
306 
307    ELSIF l_view_by = 'ITEM+ENI_ITEM_INV_CAT' THEN
308     l_stmt := 'SELECT 	eniv.value 	VIEWBY,
309 		eniv.id			VIEWBYID,
310 		eniv.id	 		ISC_ATTRIBUTE_4,
311 		null			ISC_ATTRIBUTE_2,
312 		null			ISC_ATTRIBUTE_3,
313 		'||l_outer_sql||'
314 		FROM(select (rank() over (&ORDER_BY_CLAUSE nulls last,item_category_id))-1 rnk,
315 		item_category_id,
316 		'||l_outer_sql||'
317 		FROM (select item_category_id,
318 		'||l_formula_sql||'
319 		FROM (select f.item_category_id ITEM_CATEGORY_ID,
320 		'||l_inner_sql||'
321 		GROUP by f.item_category_id) c)) a,
322  		ENI_ITEM_INV_CAT_V 	eniv
323 		WHERE a.item_category_id = eniv.id
324 		AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
325   		ORDER BY rnk';
326 
327   ELSE	--l_view_by=Customer
328     l_stmt := 'SELECT 	cust.value	VIEWBY,
329 		cust.id			VIEWBYID,
330 		cust.id	 		ISC_ATTRIBUTE_4,
331 		null			ISC_ATTRIBUTE_2,
332 		null			ISC_ATTRIBUTE_3,
333 		'||l_outer_sql||'
334 		FROM(select (rank() over (&ORDER_BY_CLAUSE nulls last,customer_id))-1 rnk,
335 		customer_id,
336 		'||l_outer_sql||'
337 		FROM (select customer_id,
338 		'||l_formula_sql||'
339 		FROM (select f.customer_id CUSTOMER_ID,
340 		'||l_inner_sql||'
341 		GROUP by f.customer_id) c)) a,
342  		FII_CUSTOMERS_V 	cust
343 		WHERE a.customer_id = cust.id
344 		AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
345   		ORDER BY rnk';
346 
347   END IF;
348 
349   x_custom_sql := l_stmt;
350 
351   l_custom_rec.attribute_name := ':ISC_AGG_LEVEL';
352   l_custom_rec.attribute_value := to_char(l_agg_level);
353   l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
354   l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
355   x_custom_output.EXTEND;
356   x_custom_output(1) := l_custom_rec;
357 
358   l_custom_rec.attribute_name := ':ISC_LANG';
359   l_custom_rec.attribute_value := l_lang;
360   l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
361   l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
362   x_custom_output.EXTEND;
363   x_custom_output(2) := l_custom_rec;
364 
365 
366 END get_sql;
367 
368 END ISC_DBI_SHIP_PERF_PKG ;
369