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_TREND_PKG

Source


1 PACKAGE BODY ISC_DBI_DAYS_SHIP_TREND_PKG AS
2 /* $Header: ISCRG71B.pls 120.3 2006/05/03 03:08:39 achandak noship $ */
3 
4 
5 PROCEDURE Get_Sql (	p_param		IN		BIS_PMV_PAGE_PARAMETER_TBL,
6 			x_custom_sql	OUT NOCOPY	VARCHAR2,
7 			x_custom_output	OUT NOCOPY	BIS_QUERY_ATTRIBUTES_TBL) IS
8 
9   l_stmt 			VARCHAR2(10000);
10   l_period_type			VARCHAR2(10000);
11   l_inv_org 			VARCHAR2(10000);
12   l_inv_org_where     		VARCHAR2(10000);
13   l_item			VARCHAR2(10000);
14   l_item_where			VARCHAR2(10000);
15   l_inv_cat			VARCHAR2(10000);
16   l_inv_cat_where		VARCHAR2(10000);
17   l_customer			VARCHAR2(10000);
18   l_customer_where		VARCHAR2(10000);
19   l_item_cat_flag		NUMBER; -- 0 for item, 1 for inv category
20   l_customer_flag		NUMBER; -- 0 for customer level, 1 for no-customer level
21   l_agg_level			NUMBER;
22 
23   l_custom_rec			BIS_QUERY_ATTRIBUTES;
24   l_att_2                VARCHAR2(255);
25 BEGIN
26 
27   FOR i IN 1..p_param.COUNT
28   LOOP
29 
30     IF(p_param(i).parameter_name = 'PERIOD_TYPE')
31       THEN l_period_type := p_param(i).parameter_value;
32     END IF;
33 
34     IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
35       THEN l_inv_org :=  p_param(i).parameter_value;
36     END IF;
37 
38     IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_INV_CAT')
39       THEN l_inv_cat :=  p_param(i).parameter_value;
40     END IF;
41 
42     IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG')
43       THEN l_item :=  p_param(i).parameter_value;
44     END IF;
45 
46     IF(p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS')
47       THEN l_customer :=  p_param(i).parameter_value;
48     END IF;
49 
50   END LOOP;
51 
52   IF(l_inv_cat IS NULL OR l_inv_cat = 'All')
53     THEN l_inv_cat_where := '';
54     ELSE l_inv_cat_where := '
55 	AND fact.item_category_id IN (&ITEM+ENI_ITEM_INV_CAT)';
56   END IF;
57 
58   IF(l_item IS NULL OR l_item = 'All')
59     THEN l_item_where := '';
60     ELSE l_item_where := '
61 	AND fact.item_id IN (&ITEM+ENI_ITEM_ORG)';
62   END IF;
63 
64   IF (l_inv_org IS NULL OR l_inv_org = 'All')
65     THEN l_inv_org_where := '
66 	AND (EXISTS
67 		(SELECT 1
68 		FROM org_access o
69 		WHERE o.responsibility_id = fnd_global.resp_id
70 		AND o.resp_application_id = fnd_global.resp_appl_id
71 		AND o.organization_id = fact.inv_org_id)
72 	OR EXISTS
73 		(SELECT 1
74 		FROM mtl_parameters org
75 		WHERE org.organization_id = fact.inv_org_id
76 		AND NOT EXISTS
77 			(SELECT 1
78 			FROM org_access ora
79 			WHERE org.organization_id = ora.organization_id)))';
80 
81     ELSE l_inv_org_where :=  '
82 	AND fact.inv_org_id = &ORGANIZATION+ORGANIZATION';
83   END IF;
84 
85   IF (l_customer IS NULL OR l_customer = 'All')
86     THEN l_customer_where :='';
87 	 l_customer_flag := 1; -- do not need customer id
88     ELSE l_customer_where :='
89 	AND fact.customer_id IN (&CUSTOMER+FII_CUSTOMERS)';
90 	 l_customer_flag := 0; -- customer level
91   END IF;
92 
93   IF((l_inv_cat IS NULL OR l_inv_cat = 'All') AND (l_item IS NULL OR l_item = 'All'))
94     THEN l_item_cat_flag := 3;  -- no grouping on item dimension
95     ELSE
96       IF (l_item IS NULL OR l_item = 'All')
97 	THEN l_item_cat_flag := 1; -- inventory category
98     	ELSE l_item_cat_flag := 0; -- item
99       END IF;
100   END IF;
101 
102   CASE
103     WHEN (l_item_cat_flag = 0 and l_customer_flag = 0) THEN l_agg_level := 0;
104     WHEN (l_item_cat_flag = 1 and l_customer_flag = 0) THEN l_agg_level := 4;
105     WHEN (l_item_cat_flag = 3 and l_customer_flag = 0) THEN l_agg_level := 2;
106     WHEN (l_item_cat_flag = 0 and l_customer_flag = 1) THEN l_agg_level := 1;
107     WHEN (l_item_cat_flag = 1 and l_customer_flag = 1) THEN l_agg_level := 5;
108     WHEN (l_item_cat_flag = 3 and l_customer_flag = 1) THEN l_agg_level := 3;
109   END CASE;
110 
111   l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
112   x_custom_output := bis_query_attributes_tbl();
113 
114   If l_period_type = 'FII_TIME_WEEK' then
115      l_att_2 := '''AS_OF_DATE=''||'|| 'to_char(fii1.end_date,''DD/MM/YYYY'')' || '||''&pFunctionName=ISC_DBI_DAYS_SHIP_TREND&TIME+FII_TIME_DAY=TIME+FII_TIME_DAY&pParameters=pParamIds@Y''';
116   else
117      l_att_2 := 'NULL ';
118   end if;
119 
120   If l_period_type = 'FII_TIME_DAY' then
121 	l_stmt := 'SELECT	fii1.start_date  VIEWBY, 	fii1.start_date	ISC_ATTRIBUTE_2, '
122 	 || l_att_2 ||  ' ISC_ATTRIBUTE_3, nvl(s.curr_book_to_ship_days,0) 	ISC_MEASURE_1,
123      	nvl(s.curr_shipped_line_cnt,0)		ISC_MEASURE_2,
124         nvl(s.prev_book_to_ship_days,0) /
125            decode(nvl(s.prev_shipped_line_cnt,0), 0, NULL,
126                   abs(nvl(s.prev_shipped_line_cnt,0)))   ISC_MEASURE_5,
127 	nvl(s.prev_book_to_ship_days,0)		ISC_MEASURE_3,
128 	nvl(s.prev_shipped_line_cnt,0)		ISC_MEASURE_4,
129         nvl(s.curr_book_to_ship_days,0) /
130            decode(nvl(s.curr_shipped_line_cnt,0), 0, NULL,
131                   abs(nvl(s.curr_shipped_line_cnt,0)))   ISC_MEASURE_6,
132         nvl(s.curr_book_to_ship_days,0) /
133            decode(nvl(s.curr_shipped_line_cnt,0), 0, NULL,
134                   abs(nvl(s.curr_shipped_line_cnt,0))) -
135         nvl(s.prev_book_to_ship_days,0) /
136            decode(nvl(s.prev_shipped_line_cnt,0), 0, NULL,
137                   abs(nvl(s.prev_shipped_line_cnt,0)))   ISC_MEASURE_7,
138 	null					CURRENCY,  -- obsolete
139         null					ISC_CALC_ITEM_2,  -- obsolete
140         null					ISC_CALC_ITEM_1,  -- obsolete
141         null					ISC_CALC_ITEM_3  -- obsolete
142    FROM	(SELECT	dates.start_date				START_DATE,
143 		sum(decode(dates.period, ''C'',
144 			nvl(fact.book_to_ship_days,0), 0))	CURR_BOOK_TO_SHIP_DAYS,
145 		sum(decode(dates.period, ''P'',
146 			nvl(fact.book_to_ship_days,0), 0))	PREV_BOOK_TO_SHIP_DAYS,
147 		sum(decode(dates.period, ''C'',
148 			nvl(fact.shipped_line_cnt,0), 0))	CURR_SHIPPED_LINE_CNT,
149 		sum(decode(dates.period, ''P'',
150 			nvl(fact.shipped_line_cnt,0), 0))	PREV_SHIPPED_LINE_CNT
151 	   FROM	(SELECT	fii1.start_date					START_DATE,
152 			''C''						PERIOD,
153 			least(fii1.end_date, &BIS_CURRENT_ASOF_DATE)	REPORT_DATE
154 		   FROM	'||l_period_type||'	fii1
155 		  WHERE	fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
156 					   AND &BIS_CURRENT_ASOF_DATE
157 		UNION ALL
158 		 SELECT	p2.start_date					START_DATE,
159 			''P''						PERIOD,
160 			p1.report_date					REPORT_DATE
161 		   FROM	(SELECT	least(fii1.end_date, &BIS_PREVIOUS_ASOF_DATE)	REPORT_DATE,
162 				rownum						ID
163 			   FROM	'||l_period_type||'	fii1
164 			  WHERE	fii1.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
165 						   AND &BIS_PREVIOUS_ASOF_DATE
166 			  ORDER BY fii1.start_date DESC) p1,
167 			(SELECT	fii1.start_date					START_DATE,
168 				rownum						ID
169 			   FROM	'||l_period_type||'	fii1
170 			  WHERE	fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
171 						   AND &BIS_CURRENT_ASOF_DATE
172 			  ORDER BY fii1.start_date DESC) p2
173 		  WHERE	p1.id(+) = p2.id)			dates,
174 		ISC_DBI_FM_0000_MV 				fact
175 	   WHERE fact.agg_level = :ISC_AGG_LEVEL
176 		AND fact.time_id = to_char(dates.report_date,''j'')
177 		AND fact.period_type_id = 1 '
178 		||l_inv_org_where
179 		||l_inv_cat_where
180 		||l_item_where
181 		||l_customer_where||'
182 	GROUP BY dates.start_date)	s,
183 	'||l_period_type||'		fii1
184   WHERE	fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
185 			   AND &BIS_CURRENT_ASOF_DATE
186     AND	fii1.start_date = s.start_date(+)
187      ORDER BY fii1.start_date';
188   else
189      l_stmt := 'SELECT	fii1.NAME   VIEWBY, fii1.NAME ISC_ATTRIBUTE_2, '
190      	|| l_att_2 || ' ISC_ATTRIBUTE_3, nvl(s.curr_book_to_ship_days,0) 	ISC_MEASURE_1,
191      	     	nvl(s.curr_shipped_line_cnt,0)		ISC_MEASURE_2,
192         nvl(s.prev_book_to_ship_days,0) /
193            decode(nvl(s.prev_shipped_line_cnt,0), 0, NULL,
194                   abs(nvl(s.prev_shipped_line_cnt,0)))   ISC_MEASURE_5,
195 	nvl(s.prev_book_to_ship_days,0)		ISC_MEASURE_3,
196 	nvl(s.prev_shipped_line_cnt,0)		ISC_MEASURE_4,
197         nvl(s.curr_book_to_ship_days,0) /
198            decode(nvl(s.curr_shipped_line_cnt,0), 0, NULL,
199                   abs(nvl(s.curr_shipped_line_cnt,0)))   ISC_MEASURE_6,
200         nvl(s.curr_book_to_ship_days,0) /
201            decode(nvl(s.curr_shipped_line_cnt,0), 0, NULL,
202                   abs(nvl(s.curr_shipped_line_cnt,0))) -
203         nvl(s.prev_book_to_ship_days,0) /
204            decode(nvl(s.prev_shipped_line_cnt,0), 0, NULL,
205                   abs(nvl(s.prev_shipped_line_cnt,0)))   ISC_MEASURE_7,
206 	null					CURRENCY,  -- obsolete
207         null					ISC_CALC_ITEM_2,  -- obsolete
208         null					ISC_CALC_ITEM_1,  -- obsolete
209         null					ISC_CALC_ITEM_3  -- obsolete
210    FROM	(SELECT	dates.start_date				START_DATE,
211 		sum(decode(dates.period, ''C'',
212 			nvl(fact.book_to_ship_days,0), 0))	CURR_BOOK_TO_SHIP_DAYS,
213 		sum(decode(dates.period, ''P'',
214 			nvl(fact.book_to_ship_days,0), 0))	PREV_BOOK_TO_SHIP_DAYS,
215 		sum(decode(dates.period, ''C'',
216 			nvl(fact.shipped_line_cnt,0), 0))	CURR_SHIPPED_LINE_CNT,
217 		sum(decode(dates.period, ''P'',
218 			nvl(fact.shipped_line_cnt,0), 0))	PREV_SHIPPED_LINE_CNT
219 	   FROM	(SELECT	fii1.start_date					START_DATE,
220 			''C''						PERIOD,
221 			least(fii1.end_date, &BIS_CURRENT_ASOF_DATE)	REPORT_DATE
222 		   FROM	'||l_period_type||'	fii1
223 		  WHERE	fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
224 					   AND &BIS_CURRENT_ASOF_DATE
225 		UNION ALL
226 		 SELECT	p2.start_date					START_DATE,
227 			''P''						PERIOD,
228 			p1.report_date					REPORT_DATE
229 		   FROM	(SELECT	least(fii1.end_date, &BIS_PREVIOUS_ASOF_DATE)	REPORT_DATE,
230 				rownum						ID
231 			   FROM	'||l_period_type||'	fii1
232 			  WHERE	fii1.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
233 						   AND &BIS_PREVIOUS_ASOF_DATE
234 			  ORDER BY fii1.start_date DESC) p1,
235 			(SELECT	fii1.start_date					START_DATE,
236 				rownum						ID
237 			   FROM	'||l_period_type||'	fii1
238 			  WHERE	fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
239 						   AND &BIS_CURRENT_ASOF_DATE
240 			  ORDER BY fii1.start_date DESC) p2
241 		  WHERE	p1.id(+) = p2.id)			dates,
242 		ISC_DBI_FM_0000_MV 				fact,
243 		FII_TIME_RPT_STRUCT_V				cal
244 	  WHERE	cal.report_date = dates.report_date
245 	    AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id
246 	    AND fact.time_id = cal.time_id
247 	    AND fact.period_type_id = cal.period_type_id
248 	    AND fact.agg_level = :ISC_AGG_LEVEL'
249 		||l_inv_org_where
250 		||l_inv_cat_where
251 		||l_item_where
252 		||l_customer_where||'
253 	GROUP BY dates.start_date)	s,
254 	'||l_period_type||'		fii1
255   WHERE	fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
256 			   AND &BIS_CURRENT_ASOF_DATE
257     AND	fii1.start_date = s.start_date(+)
258      ORDER BY fii1.start_date';
259 end if;
260 
261 
262   x_custom_sql := l_stmt;
263 
264   l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.View_By_Value;
265   l_custom_rec.attribute_value := 'TIME+'||l_period_type;
266   l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
267   x_custom_output.extend;
268   x_custom_output(1) := l_custom_rec;
269 
270   l_custom_rec.attribute_name := ':ISC_AGG_LEVEL';
271   l_custom_rec.attribute_value := to_char(l_agg_level);
272   l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.Bind_Type;
273   l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.Integer_Bind;
274   x_custom_output.extend;
275   x_custom_output(2) := l_custom_rec;
276 
277 END Get_Sql;
278 END ISC_DBI_DAYS_SHIP_TREND_PKG;