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_FULF_PKG

Source


1 PACKAGE BODY ISC_DBI_DAYS_FULF_PKG AS
2 /* $Header: ISCRG73B.pls 120.0 2005/05/25 17:28:20 appldev 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_measures			VARCHAR2(10000);
10   l_select_stmt			VARCHAR2(10000);
11   l_inner_select_stmt		VARCHAR2(10000);
12   l_inner_group_by_stmt		VARCHAR2(10000);
13   l_where_stmt			VARCHAR2(10000);
14   l_inv_org			VARCHAR2(10000);
15   l_inv_org_where		VARCHAR2(10000);
16   l_prod			VARCHAR2(10000);
17   l_prod_where			VARCHAR2(10000);
18   l_prod_cat			VARCHAR2(10000);
19   l_prod_cat_from		VARCHAR2(10000);
20   l_prod_cat_where		VARCHAR2(10000);
21   l_cust			VARCHAR2(10000);
22   l_cust_where			VARCHAR2(10000);
23   l_mv				VARCHAR2(10000);
24   l_flags_where			VARCHAR2(10000);
25   l_view_by			VARCHAR2(120);
26   l_lang			VARCHAR2(10);
27   l_item_cat_flag		NUMBER;
28   l_cust_flag			NUMBER;
29 
30   l_custom_rec			BIS_QUERY_ATTRIBUTES;
31 
32 BEGIN
33 
34   l_lang := userenv('LANG');
35 
36   FOR i IN 1..p_param.COUNT
37   LOOP
38     IF (p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
39       THEN l_inv_org := p_param(i).parameter_value;
40     END IF;
41 
42     IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT')
43       THEN l_prod_cat := p_param(i).parameter_value;
44     END IF;
45 
46     IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG')
47       THEN l_prod := p_param(i).parameter_value;
48     END IF;
49 
50     IF (p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS')
51       THEN l_cust := p_param(i).parameter_value;
52     END IF;
53 
54     IF (p_param(i).parameter_name = 'VIEW_BY')
55       THEN l_view_by := p_param(i).parameter_value;
56     END IF;
57   END LOOP;
58 
59   IF (l_inv_org IS NULL OR l_inv_org = '' OR l_inv_org = 'All')
60     THEN l_inv_org_where := '
61 	AND (EXISTS
62 		(SELECT 1
63 		FROM org_access o
64 		WHERE o.responsibility_id = fnd_global.resp_id
65 		AND o.resp_application_id = fnd_global.resp_appl_id
66 		AND o.organization_id = fact.inv_org_id)
67 	OR EXISTS
68 		(SELECT 1
69 		FROM mtl_parameters org
70 		WHERE org.organization_id = fact.inv_org_id
71 		AND NOT EXISTS
72 			(SELECT 1
73 			FROM org_access ora
74 			WHERE org.organization_id = ora.organization_id)))';
75     ELSE l_inv_org_where := '
76 	    AND fact.inv_org_id = &ORGANIZATION+ORGANIZATION';
77   END IF;
78 
79   IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
80     THEN
81       IF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT')
82 	THEN
83 	  l_prod_cat_from := ',
84 		ENI_DENORM_HIERARCHIES		eni_cat,
85 		MTL_DEFAULT_CATEGORY_SETS	mdcs';
86 	  l_prod_cat_where := '
87 	    AND fact.item_category_id = eni_cat.child_id
88 	    AND eni_cat.top_node_flag = ''Y''
89 	    AND	eni_cat.dbi_flag = ''Y''
90 	    AND eni_cat.object_type = ''CATEGORY_SET''
91 	    AND	eni_cat.object_id = mdcs.category_set_id
92 	    AND	mdcs.functional_area_id = 11';
93 	ELSE
94 	  l_prod_cat_from := '';
95 	  l_prod_cat_where := '';
96       END IF;
97     ELSE
98       l_prod_cat_from := ',
99 		ENI_DENORM_HIERARCHIES		eni_cat,
100 		MTL_DEFAULT_CATEGORY_SETS	mdcs';
101       l_prod_cat_where := '
102 	    AND fact.item_category_id = eni_cat.child_id
103 	    AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
104 	    AND	eni_cat.dbi_flag = ''Y''
105 	    AND	eni_cat.object_type = ''CATEGORY_SET''
106 	    AND eni_cat.object_id = mdcs.category_set_id
107 	    AND	mdcs.functional_area_id = 11';
108   END IF;
109 
110   IF (l_prod IS NULL OR l_prod = '' OR l_prod = 'All')
111     THEN l_prod_where := '';
112     ELSE l_prod_where := '
113 	    AND fact.item_id IN (&ITEM+ENI_ITEM_ORG)';
114   END IF;
115 
116   IF (l_cust IS NULL OR l_cust = '' OR l_cust = 'All')
117     THEN
118       l_cust_where := '';
119       IF (l_view_by = 'CUSTOMER+FII_CUSTOMERS')
120         THEN l_cust_flag := 0;
121 	ELSE l_cust_flag := 1;
122       END IF;
123     ELSE
124       l_cust_where := '
125 	    AND fact.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
126       l_cust_flag := 0;
127   END IF;
128 
129   IF (l_prod IS NULL OR l_prod = '' OR l_prod = 'All')
130     THEN
131       IF (l_view_by = 'ITEM+ENI_ITEM_ORG')
132 	THEN l_item_cat_flag := 0; -- product
133       ELSIF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT')
134         THEN l_item_cat_flag := 1; -- category
135       ELSE
136 	IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
137 	  THEN l_item_cat_flag := 3; -- all
138 	  ELSE l_item_cat_flag := 1; -- category
139 	END IF;
140       END IF;
141   ELSE
142     l_item_cat_flag := 0; -- product
143   END IF;
144 
145   l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
146   x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
147 
148   l_measures := 'ISC_MEASURE_1,ISC_MEASURE_2,ISC_MEASURE_3,ISC_MEASURE_4,ISC_MEASURE_5,
149 		 ISC_MEASURE_6,ISC_MEASURE_7,ISC_MEASURE_8,CURRENCY,FND_CATEGORY,FND_PRODUCT';
150 
151   IF l_view_by = 'ITEM+ENI_ITEM_ORG'
152     THEN l_select_stmt := '
153  SELECT	items.value						VIEWBY,
154 	NULL							ISC_ATTRIBUTE_2,
155 	NULL							ISC_ATTRIBUTE_3, -- drill across url
156 	items.description					ISC_ATTRIBUTE_4, -- item description
157 	'||l_measures||'
158    FROM
159 (SELECT	(rank() over (&ORDER_BY_CLAUSE nulls last, item_id)) - 1		rnk,
160 	item_id,
161 	'||l_measures||'
162    FROM
163 (SELECT	c.item_id,';
164 	l_inner_select_stmt := '
165 		 SELECT	fact.item_id					ITEM_ID,';
166 	l_inner_group_by_stmt := '
167 		GROUP BY fact.item_id';
168 	l_where_stmt := '
169 	ENI_ITEM_ORG_V			items
170   WHERE a.item_id = items.id
171     AND	((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
172   ORDER BY rnk';
173 
174   ELSIF l_view_by = 'ORGANIZATION+ORGANIZATION'
175     THEN l_select_stmt := '
176  SELECT	org.name						VIEWBY,
177 	NULL							ISC_ATTRIBUTE_2,
178 	NULL							ISC_ATTRIBUTE_3, -- drill across url
179 	NULL							ISC_ATTRIBUTE_4, -- item description
180 	'||l_measures||'
181    FROM
182 (SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, inv_org_id)) - 1		rnk,
183 	inv_org_id,
184 	'||l_measures||'
185    FROM
186 (SELECT	c.inv_org_id,	';
187 	l_inner_select_stmt := '
188 		 SELECT	fact.inv_org_id					INV_ORG_ID,';
189 	l_inner_group_by_stmt := '
190 		GROUP BY fact.inv_org_id';
191 	l_where_stmt := '
192 	HR_ALL_ORGANIZATION_UNITS_TL	org
193   WHERE a.inv_org_id = org.organization_id
194     AND org.language = :ISC_LANG
195     AND	((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
196   ORDER BY rnk';
197 
198   ELSIF l_view_by = 'CUSTOMER+FII_CUSTOMERS'
199     THEN l_select_stmt := '
200  SELECT	cust.value						VIEWBY,
201 	NULL							ISC_ATTRIBUTE_2,
202 	NULL							ISC_ATTRIBUTE_3, -- drill across url
203 	NULL							ISC_ATTRIBUTE_4, -- item description
204 	'||l_measures||'
205    FROM
206 (SELECT	(rank() over (&ORDER_BY_CLAUSE nulls last, customer_id)) - 1		rnk,
207 	customer_id,
208 	'||l_measures||'
209    FROM
210 (SELECT	c.customer_id,	';
211 	l_inner_select_stmt := '
212 		 SELECT	fact.customer_id				CUSTOMER_ID,';
213 	l_inner_group_by_stmt := '
214 		GROUP BY fact.customer_id';
215 	l_where_stmt := '
216 	FII_CUSTOMERS_V			cust
217   WHERE a.customer_id = cust.id
218     AND	((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
219   ORDER BY rnk';
220 
221   ELSE -- l_view_by = 'ITEM+ENI_ITEM_VBH_CAT'
222     l_select_stmt := '
223  SELECT	ecat.value					VIEWBY,
224 	ecat.id						VIEWBYID,
225 	NULL						ISC_ATTRIBUTE_2,
226 	decode(ecat.leaf_node_flag, ''Y'',
227 		''pFunctionName=ISC_DBI_DAYS_FULF&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_ORG&pParamIds=Y'',
228 		''pFunctionName=ISC_DBI_DAYS_FULF&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'')
229 								ISC_ATTRIBUTE_3, -- drill across url
230 	NULL							ISC_ATTRIBUTE_4, -- item description
231 	'||l_measures||'
232    FROM
233 (SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, item_category_id)) - 1	rnk,
234 	item_category_id,
235 	'||l_measures||'
236    FROM
237 (SELECT	c.item_category_id,	';
238   IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
239     THEN
240 	l_inner_select_stmt := '
241 		 SELECT	eni_cat.parent_id				ITEM_CATEGORY_ID,';
242 	l_inner_group_by_stmt := '
243 		GROUP BY eni_cat.parent_id';
244     ELSE
245 	l_inner_select_stmt := '
246 		 SELECT	eni_cat.imm_child_id				ITEM_CATEGORY_ID,';
247 	l_inner_group_by_stmt := '
248 		GROUP BY eni_cat.imm_child_id';
249   END IF;
250 	l_where_stmt := '
251 	ENI_ITEM_VBH_NODES_V		ecat
252   WHERE a.item_category_id = ecat.id
253     AND	ecat.parent_id = ecat.child_id
254     AND	((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
255   ORDER BY rnk';
256   END IF;
257 
258   IF ((l_view_by = 'ITEM+ENI_ITEM_VBH_CAT' OR l_view_by = 'ORGANIZATION+ORGANIZATION') AND
259       (l_prod IS NULL OR l_prod = '' OR l_prod = 'All') AND
260       (l_cust IS NULL OR l_cust = '' OR l_cust = 'All'))
261     THEN
262     	IF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') THEN
263           IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
264 	    THEN
265 	      l_inner_select_stmt := '
266 		 SELECT	fact.parent_id					ITEM_CATEGORY_ID,';
267 	      l_inner_group_by_stmt := '
268 		GROUP BY fact.parent_id';
269 	    ELSE
270 	      l_inner_select_stmt := '
271 		 SELECT fact.imm_child_id				ITEM_CATEGORY_ID,';
272 	      l_inner_group_by_stmt := '
273 		GROUP BY fact.imm_child_id';
274 	  END IF;
275         END IF;
276 
277         IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
278 	  THEN
279 	    l_prod_cat_from := '';
280 	    l_prod_cat_where := '
281 		    AND fact.top_node_flag = ''Y''';
282 	  ELSE
283 	    l_prod_cat_from := '';
284 	    l_prod_cat_where := '
285 		    AND fact.parent_id = &ITEM+ENI_ITEM_VBH_CAT';
286 	END IF;
287 	l_mv := 'ISC_DBI_CFM_011_MV';
288 	l_flags_where := '
289 	    AND	fact.inv_org_flag = 0';
290     ELSE
291 	l_mv := 'ISC_DBI_CFM_002_MV';
292 	l_flags_where := '
293 	    AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
294 	    AND fact.customer_flag = :ISC_CUST_FLAG';
295   END IF;
296 
297   l_stmt := l_select_stmt || '
298 	c.curr_fulf_days
299 	  / decode(c.curr_fulf_cnt, 0, NULL,
300 		   c.curr_fulf_cnt)				ISC_MEASURE_1, -- days fulf
301 	c.curr_fulf_days
302 	  / decode(c.curr_fulf_cnt, 0, NULL,
303 		   c.curr_fulf_cnt) -
304 	c.prev_fulf_days
305 	  / decode(c.prev_fulf_cnt, 0, NULL,
306 		   c.prev_fulf_cnt)				ISC_MEASURE_2, -- days fulf change
307 	sum(c.curr_fulf_days) over ()
308 	  / decode(sum(c.curr_fulf_cnt) over (), 0, NULL,
309 		   sum(c.curr_fulf_cnt) over ())		ISC_MEASURE_3, -- gd total days fulf
310 	sum(c.curr_fulf_days) over ()
311 	  / decode(sum(c.curr_fulf_cnt) over (), 0, NULL,
312 		   sum(c.curr_fulf_cnt) over ()) -
313 	sum(c.prev_fulf_days) over ()
314 	  / decode(sum(c.prev_fulf_cnt) over (), 0, NULL,
315 		   sum(c.prev_fulf_cnt) over ())		ISC_MEASURE_4, -- gd total days fulf change
316 	NULL							ISC_MEASURE_5,
317 	NULL							ISC_MEASURE_6,
318 	NULL							ISC_MEASURE_7,
319 	NULL							ISC_MEASURE_8,
320 	NULL							CURRENCY,	-- obsoleted from 5.0
321 	NULL							FND_CATEGORY,	-- obsoleted from 5.0
322 	NULL							FND_PRODUCT	-- obsoleted from 5.0
323 	   FROM	('||l_inner_select_stmt||'
324 		sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
325 			   fact.book_to_fulfill_days, 0))		CURR_FULF_DAYS,
326 		sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
327 			   fact.book_to_fulfill_days, 0))		PREV_FULF_DAYS,
328 		sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
329 			   fact.book_to_fulfill_cnt, 0))		CURR_FULF_CNT,
330 		sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
331 			   fact.book_to_fulfill_cnt, 0))		PREV_FULF_CNT
332 	   FROM '||l_mv||'			fact,
333 		FII_TIME_RPT_STRUCT_V		cal'||l_prod_cat_from||'
334 	  WHERE fact.time_id = cal.time_id
335 	    AND fact.return_flag = 0'||l_flags_where||'
336 	    AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
337 	    AND cal.period_type_id = fact.period_type_id
338 	    AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'
339 		||l_inv_org_where
340 		||l_prod_cat_where
341 		||l_prod_where
342 		||l_cust_where
343 		||l_inner_group_by_stmt||')	c)
344 	  WHERE	ISC_MEASURE_1 IS NOT NULL
345 	     OR	ISC_MEASURE_2 IS NOT NULL)	a,'
346 		||l_where_stmt;
347 
348   x_custom_sql := l_stmt;
349 
350   l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
351   l_custom_rec.attribute_value := to_char(l_item_cat_flag);
352   l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
353   l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
354   x_custom_output.extend;
355   x_custom_output(1) := l_custom_rec;
356 
357   l_custom_rec.attribute_name := ':ISC_CUST_FLAG';
358   l_custom_rec.attribute_value := to_char(l_cust_flag);
359   l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
360   l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
361   x_custom_output.extend;
362   x_custom_output(2) := l_custom_rec;
363 
364   l_custom_rec.attribute_name := ':ISC_LANG';
365   l_custom_rec.attribute_value := l_lang;
366   l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
367   l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
368   x_custom_output.EXTEND;
369   x_custom_output(3) := l_custom_rec;
370 
371 END Get_Sql;
372 
373 END ISC_DBI_DAYS_FULF_PKG;