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_DAYS_SHIP_PKG

Source


1 PACKAGE BODY ISC_DBI_DAYS_SHIP_PKG AS
2 /* $Header: ISCRG70B.pls 120.1 2006/05/03 03:27:24 abhdixi noship $ */
3 
4 PROCEDURE Get_Sql (	p_param		IN		BIS_PMV_PAGE_PARAMETER_TBL,
5 			x_custom_sql	OUT NOCOPY	VARCHAR2,
6 			x_custom_output	OUT NOCOPY	BIS_QUERY_ATTRIBUTES_TBL) IS
7 
8   l_stmt 			VARCHAR2(10000);
9   l_formula_sql			VARCHAR2(10000);
10   l_inner_sql			VARCHAR2(10000);
11   l_outer_sql			VARCHAR2(10000);
12   l_inv_org 			VARCHAR2(10000);
13   l_inv_org_where     		VARCHAR2(10000);
14   l_item			VARCHAR2(10000);
15   l_item_where			VARCHAR2(10000);
16   l_inv_cat			VARCHAR2(10000);
17   l_inv_cat_where		VARCHAR2(10000);
18   l_view_by			VARCHAR2(120);
19   l_customer			VARCHAR2(10000);
20   l_customer_where		VARCHAR2(10000);
21   l_item_cat_flag		NUMBER; -- 0 for item, 1 for inv category
22   l_customer_flag		NUMBER; -- 0 for customer level, 1 for no-customer level
23   l_agg_level			NUMBER;
24   l_lang  			VARCHAR2(10);
25   l_custom_rec			BIS_QUERY_ATTRIBUTES;
26   l_period_type			VARCHAR2(30);
27 
28 BEGIN
29 
30   FOR i IN 1..p_param.COUNT
31   LOOP
32     IF( p_param(i).parameter_name= 'VIEW_BY')
33       THEN l_view_by := p_param(i).parameter_value;
34     END IF;
35 
36     IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
37       THEN l_inv_org :=  p_param(i).parameter_value;
38     END IF;
39 
40     IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_INV_CAT')
41       THEN l_inv_cat :=  p_param(i).parameter_value;
42     END IF;
43 
44     IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG')
45       THEN l_item :=  p_param(i).parameter_value;
46     END IF;
47 
48     IF(p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS')
49       THEN l_customer :=  p_param(i).parameter_value;
50     END IF;
51 
52     IF(p_param(i).parameter_name = 'PERIOD_TYPE')
53       THEN l_period_type :=  p_param(i).parameter_value;
54     END IF;
55 
56   END LOOP;
57 
58   IF(l_inv_org IS NULL OR l_inv_org = 'All')
59     THEN l_inv_org_where :=  '
60 	AND (EXISTS
61 		(SELECT 1
62 		FROM org_access o
63 		WHERE o.responsibility_id = fnd_global.resp_id
64 		AND o.resp_application_id = fnd_global.resp_appl_id
65 		AND o.organization_id = f.inv_org_id)
66 	OR EXISTS
67 		(SELECT 1
68 		FROM mtl_parameters org
69 		WHERE org.organization_id = f.inv_org_id
70 		AND NOT EXISTS
71 			(SELECT 1
72 			FROM org_access ora
73 			WHERE org.organization_id = ora.organization_id)))';
74 
75     ELSE l_inv_org_where :=  '
76 	    AND f.inv_org_id = &ORGANIZATION+ORGANIZATION';
77   END IF;
78 
79   IF (l_customer IS NULL OR l_customer = 'All')
80     THEN l_customer_where :='';
81     IF(l_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
82        l_customer_flag := 0;
83     ELSE
84        l_customer_flag := 1; -- do not need customer id
85     END IF;
86   ELSE l_customer_where :='
87 	AND f.customer_id IN (&CUSTOMER+FII_CUSTOMERS)';
88        l_customer_flag := 0; -- customer level
89   END IF;
90 
91   IF (l_inv_cat IS NULL OR l_inv_cat = 'All')
92     THEN l_inv_cat_where := '';
93     ELSE l_inv_cat_where := '
94 	    AND f.item_category_id IN (&ITEM+ENI_ITEM_INV_CAT)';
95   END IF;
96 
97   IF (l_item IS NULL OR l_item = 'All')
98     THEN l_item_where := '';
99     ELSE l_item_where := '
100 	    AND f.item_id IN (&ITEM+ENI_ITEM_ORG)';
101   END IF;
102 
103   IF (l_item IS NULL OR l_item = 'All')
104     THEN
105       IF (l_view_by = 'ITEM+ENI_ITEM_ORG')
106 	THEN l_item_cat_flag := 0; -- item
107       ELSIF (l_view_by = 'ITEM+ENI_ITEM_INV_CAT')
108         THEN l_item_cat_flag := 1; -- inventory category
109       ELSE
110 	IF (l_inv_cat IS NULL OR l_inv_cat = 'All')
111 	  THEN l_item_cat_flag := 3; -- all
112 	ELSE l_item_cat_flag := 1; -- inventory category
113 	END IF;
114       END IF;
115   ELSE
116     l_item_cat_flag := 0; -- item
117   END IF;
118 
119   CASE
120     WHEN (l_item_cat_flag = 0 and l_customer_flag = 0) THEN l_agg_level := 0;
121     WHEN (l_item_cat_flag = 1 and l_customer_flag = 0) THEN l_agg_level := 4;
122     WHEN (l_item_cat_flag = 3 and l_customer_flag = 0) THEN l_agg_level := 2;
123     WHEN (l_item_cat_flag = 0 and l_customer_flag = 1) THEN l_agg_level := 1;
124     WHEN (l_item_cat_flag = 1 and l_customer_flag = 1) THEN l_agg_level := 5;
125     WHEN (l_item_cat_flag = 3 and l_customer_flag = 1) THEN l_agg_level := 3;
126   END CASE;
127 
128   l_lang := USERENV('LANG');
129 
130   l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
131   x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
132 
133   l_formula_sql := 'c.curr_booked_to_ship_days 				ISC_MEASURE_1,
134 	c.curr_shipped_line_cnt 				ISC_MEASURE_2,
135 	c.prev_booked_to_ship_days				ISC_MEASURE_3,
136 	c.prev_shipped_line_cnt					ISC_MEASURE_4,
137 	c.curr_booked_to_ship_days
138 	  / decode( c.curr_shipped_line_cnt,0,
139 		    NULL,
140 		    c.curr_shipped_line_cnt)			ISC_MEASURE_5, -- Days Ship
141 	sum(c.curr_booked_to_ship_days) over ()
142 	  / decode( sum(c.curr_shipped_line_cnt) over (),0,
143 		    NULL,
144 		    sum(c.curr_shipped_line_cnt) over ())	ISC_MEASURE_6, -- Gd Total for Days
145  	CASE WHEN sum(c.curr_shipped_line_cnt) over() = 0 THEN to_number(NULL)
146 	     WHEN sum(c.prev_shipped_line_cnt) over() = 0 THEN to_number(NULL)
147 	     ELSE ((sum(c.curr_booked_to_ship_days) over ()
148 		     / sum(c.curr_shipped_line_cnt) over ()
149 		   - sum(c.prev_booked_to_ship_days) over ()
150 		     / sum(c.prev_shipped_line_cnt) over ())
151 		  ) END
152 								ISC_MEASURE_7, -- Gd Total for Change
153 	CASE WHEN c.curr_shipped_line_cnt = 0 THEN to_number(NULL)
154 	     WHEN c.prev_shipped_line_cnt = 0 THEN to_number(NULL)
155 	     ELSE ((c.curr_booked_to_ship_days/c.curr_shipped_line_cnt
156 		    - c.prev_booked_to_ship_days/c.prev_shipped_line_cnt)
157 		   ) END
158 								ISC_MEASURE_8, -- Days Ship Change
159 	c.curr_booked_to_ship_days
160 	  / decode( c.curr_shipped_line_cnt,0,
161 		    NULL,
162 		    c.curr_shipped_line_cnt)			ISC_MEASURE_9, -- KPI Days Ship
163 	c.prev_booked_to_ship_days
164 	  / decode( c.prev_shipped_line_cnt,0,
165 		    NULL,
166 		    c.prev_shipped_line_cnt)			ISC_MEASURE_10, -- KPI Days Ship - Prior
167 	sum(c.curr_booked_to_ship_days) over ()
168 	  / decode( sum(c.curr_shipped_line_cnt) over (),0,
169 		    NULL,
170 		    sum(c.curr_shipped_line_cnt) over ())	ISC_MEASURE_11, -- KPI Gd Total for Days
171 	sum(c.prev_booked_to_ship_days) over ()
172 	  / decode( sum(c.prev_shipped_line_cnt) over (),0,
173 		    NULL,
174 		    sum(c.prev_shipped_line_cnt) over ())	ISC_MEASURE_12, -- KPI Gd Total for Days Prior
175 	c.prev_booked_to_ship_days
176 	  / decode( c.prev_shipped_line_cnt,0,
177 		    NULL,
178 		    c.prev_shipped_line_cnt)			ISC_MEASURE_14, -- Compare to measure for KPI change
179 	sum(c.prev_booked_to_ship_days) over ()
180 	  / decode( sum(c.prev_shipped_line_cnt) over (),0,
181 		    NULL,
182 		    sum(c.prev_shipped_line_cnt) over ())	ISC_MEASURE_15, -- Gd total Compare to measure for KPI change
183 	null						CURRENCY -- obsolete from DBI 5.0';
184 
185 IF(l_period_type = 'FII_TIME_DAY')
186 THEN
187 
188   l_inner_sql := '	sum(decode(to_date(f.time_id,''j''), &BIS_CURRENT_ASOF_DATE,
189 			   f.book_to_ship_days, 0))	CURR_BOOKED_TO_SHIP_DAYS,
190 		sum(decode(to_date(f.time_id,''j''), &BIS_PREVIOUS_ASOF_DATE,
191 			   f.book_to_ship_days, 0))	PREV_BOOKED_TO_SHIP_DAYS,
192 		sum(decode(to_date(f.time_id,''j''), &BIS_CURRENT_ASOF_DATE,
193 			   f.shipped_line_cnt, 0))	CURR_SHIPPED_LINE_CNT,
194 		sum(decode(to_date(f.time_id,''j''), &BIS_PREVIOUS_ASOF_DATE,
195 			   f.shipped_line_cnt, 0))	PREV_SHIPPED_LINE_CNT
196      	   FROM ISC_DBI_FM_0000_MV			f
197      	  WHERE f.time_id in (to_char(&BIS_CURRENT_ASOF_DATE,''j''),
198 	  to_char(&BIS_PREVIOUS_ASOF_DATE,''j''))
199 	    AND f.period_type_id = 1
200 	    AND f.agg_level = :ISC_AGG_LEVEL';
201 ELSE
202   l_inner_sql := '	sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
203 			   f.book_to_ship_days, 0))	CURR_BOOKED_TO_SHIP_DAYS,
204 		sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
205 			   f.book_to_ship_days, 0))	PREV_BOOKED_TO_SHIP_DAYS,
206 		sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
207 			   f.shipped_line_cnt, 0))	CURR_SHIPPED_LINE_CNT,
208 		sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
209 			   f.shipped_line_cnt, 0))	PREV_SHIPPED_LINE_CNT
210      	   FROM ISC_DBI_FM_0000_MV			f,
211 		FII_TIME_RPT_STRUCT_V			cal
212      	  WHERE f.time_id = cal.time_id
213 	    AND f.agg_level = :ISC_AGG_LEVEL
214 	    AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
215 	    AND cal.period_type_id = f.period_type_id
216 	    AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id';
217 END IF;
218 
219   l_inner_sql := l_inner_sql||l_inv_org_where
220 		||l_inv_cat_where
221 		||l_item_where
222 		||l_customer_where;
223 
224   l_outer_sql:= 'ISC_MEASURE_1,ISC_MEASURE_2,ISC_MEASURE_5,ISC_MEASURE_6,ISC_MEASURE_3,
225 	ISC_MEASURE_4,ISC_MEASURE_8,ISC_MEASURE_7,ISC_MEASURE_9,ISC_MEASURE_10,
226 	ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_14,ISC_MEASURE_15,CURRENCY';
227 
228 -- Construction of the SQL statement here
229 
230   IF l_view_by = 'ITEM+ENI_ITEM_ORG'
231     THEN l_stmt := '
232 SELECT	items.value					 	VIEWBY,
233 	items.id						VIEWBYID,
234 	items.value					  	ISC_ATTRIBUTE_2,
235 	items.description  					ISC_ATTRIBUTE_3,
236 	'||l_outer_sql||'
237 FROM (SELECT (rank() over (&ORDER_BY_CLAUSE NULLS LAST,item_id))-1 rnk,item_id,
238 	'||l_outer_sql||'
239   FROM (SELECT item_id,
240 	'||l_formula_sql||'
241    FROM	(SELECT f.item_id				ITEM_ID,
242 	'||l_inner_sql||'
243 	GROUP BY f.item_id) c)
244 	WHERE ISC_MEASURE_5 IS NOT NULL OR ISC_MEASURE_8 IS NOT NULL) a,
245 	ENI_ITEM_ORG_V 			items
246   WHERE a.item_id = items.id
247     AND	((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
248   ORDER BY rnk';
249 
250   ELSIF l_view_by = 'ORGANIZATION+ORGANIZATION'
251     THEN l_stmt := '
252 SELECT	org.name 						VIEWBY,
253 	org.organization_id					VIEWBYID,
254 	org.name						ISC_ATTRIBUTE_2,
255 	null							ISC_ATTRIBUTE_3,
256 	'||l_outer_sql||'
257 FROM (SELECT (rank() over (&ORDER_BY_CLAUSE NULLS LAST,inv_org_id))-1 rnk,inv_org_id,
258 	'||l_outer_sql||'
259   FROM (SELECT inv_org_id,
260 	'||l_formula_sql||'
261    FROM	(SELECT f.inv_org_id				INV_ORG_ID,
262 	'||l_inner_sql||'
263 	GROUP BY f.inv_org_id) c)
264 	WHERE ISC_MEASURE_5 IS NOT NULL OR ISC_MEASURE_8 IS NOT NULL) a,
265 	HR_ALL_ORGANIZATION_UNITS_TL 	org
266   WHERE org.organization_id = a.inv_org_id
267     AND org.language = :ISC_LANG
268     AND	((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
269   ORDER BY rnk';
270 
271   ELSIF l_view_by = 'CUSTOMER+FII_CUSTOMERS'
272     THEN l_stmt := '
273 SELECT	cust.value						VIEWBY,
274 	cust.id							VIEWBYID,
275 	cust.value						ISC_ATTRIBUTE_2,
276 	null							ISC_ATTRIBUTE_3,
277 	'||l_outer_sql||'
278 FROM (SELECT (rank() over (&ORDER_BY_CLAUSE NULLS LAST,customer_id))-1 rnk,customer_id,
279 	'||l_outer_sql||'
280   FROM (SELECT customer_id,
281 	'||l_formula_sql||'
282    FROM	(SELECT f.customer_id				CUSTOMER_ID,
283 	'||l_inner_sql||'
284 	GROUP BY f.customer_id)	c)
285 	WHERE ISC_MEASURE_5 IS NOT NULL OR ISC_MEASURE_8 IS NOT NULL) a,
286 	FII_CUSTOMERS_V			cust
287   WHERE a.customer_id = cust.id
288     AND	((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
289   ORDER BY rnk';
290 
291   ELSE -- l_view_by = 'ITEM+ENI_ITEM_INV_CAT'
292     l_stmt := '
293 SELECT	eni.value 						VIEWBY,
294 	eni.id				 			VIEWBYID,
295 	eni.value					  	ISC_ATTRIBUTE_2,
296 	null							ISC_ATTRIBUTE_3,
297 	'||l_outer_sql||'
298 FROM (SELECT (rank() over (&ORDER_BY_CLAUSE NULLS LAST,item_category_id))-1 rnk,item_category_id,
299 	'||l_outer_sql||'
300   FROM (SELECT item_category_id,
301 	'||l_formula_sql||'
302    FROM	(SELECT f.item_category_id			ITEM_CATEGORY_ID,
303 	'||l_inner_sql||'
304 	GROUP BY f.item_category_id) c)
305 	WHERE ISC_MEASURE_5 IS NOT NULL OR ISC_MEASURE_8 IS NOT NULL) a,
306 	ENI_ITEM_INV_CAT_V		eni
307   WHERE a.item_category_id = eni.id
308     AND	((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
309   ORDER BY rnk';
310   END IF;
311 
312   x_custom_sql := l_stmt;
313 
314   l_custom_rec.attribute_name := ':ISC_AGG_LEVEL';
315   l_custom_rec.attribute_value := to_char(l_agg_level);
316   l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
317   l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
318   x_custom_output.extend;
319   x_custom_output(1) := l_custom_rec;
320 
321   l_custom_rec.attribute_name := ':ISC_LANG';
322   l_custom_rec.attribute_value := l_lang;
323   l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
324   l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
325   x_custom_output.extend;
326   x_custom_output(2) := l_custom_rec;
327 
328 END Get_Sql;
329 
330 END ISC_DBI_DAYS_SHIP_PKG ;
331