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_FULF_PERF_T_PKG

Source


1 PACKAGE BODY ISC_DBI_FULF_PERF_T_PKG AS
2 /* $Header: ISCRG99B.pls 120.0 2005/05/25 17:39:05 appldev noship $ */
3 
4 
5 
6 PROCEDURE Get_Sql (	p_param		IN		BIS_PMV_PAGE_PARAMETER_TBL,
7 			x_custom_sql	OUT NOCOPY	VARCHAR2,
8 			x_custom_output	OUT NOCOPY	BIS_QUERY_ATTRIBUTES_TBL) IS
9 
10   l_stmt			VARCHAR2(10000);
11   l_measures			VARCHAR2(10000);
12   l_select_stmt			VARCHAR2(10000);
13   l_union_select_stmt		VARCHAR2(10000);
14   l_union_group_by_stmt		VARCHAR2(10000);
15   l_inner_select_stmt		VARCHAR2(10000);
16   l_where_stmt			VARCHAR2(10000);
17   l_inv_org			VARCHAR2(10000);
18   l_inv_org_where		VARCHAR2(10000);
19   l_prod			VARCHAR2(10000);
20   l_prod_where			VARCHAR2(10000);
21   l_prod_cat			VARCHAR2(10000);
22   l_prod_cat_from		VARCHAR2(10000);
23   l_prod_cat_where		VARCHAR2(10000);
24   l_cust			VARCHAR2(10000);
25   l_cust_where			VARCHAR2(10000);
26   l_mv1				VARCHAR2(10000);
27   l_mv2				VARCHAR2(10000);
28   l_flags_where			VARCHAR2(10000);
29   l_curr			VARCHAR2(10000);
30   l_curr_suffix			VARCHAR2(120);
31   l_view_by			VARCHAR2(120);
32   l_lang			VARCHAR2(10);
33   l_item_cat_flag		NUMBER;
34   l_cust_flag			NUMBER;
35 
36   l_custom_rec			BIS_QUERY_ATTRIBUTES;
37 
38 BEGIN
39 
40   l_lang := userenv('LANG');
41 
42   FOR i IN 1..p_param.COUNT
43   LOOP
44     IF (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
45       THEN l_curr := p_param(i).parameter_id;
46     END IF;
47 
48     IF (p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
49       THEN l_inv_org := p_param(i).parameter_value;
50     END IF;
51 
52     IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT')
53       THEN l_prod_cat := p_param(i).parameter_value;
54     END IF;
55 
56     IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG')
57       THEN l_prod := p_param(i).parameter_value;
58     END IF;
59 
60     IF (p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS')
61       THEN l_cust := p_param(i).parameter_value;
62     END IF;
63 
64     IF (p_param(i).parameter_name = 'VIEW_BY')
65       THEN l_view_by := p_param(i).parameter_value;
66     END IF;
67   END LOOP;
68 
69   IF (l_curr = '''FII_GLOBAL1''')
70     THEN l_curr_suffix := 'g';
71   ELSIF (l_curr = '''FII_GLOBAL2''')
72     THEN l_curr_suffix :='g1';
73     ELSE l_curr_suffix := 'f';
74   END IF;
75 
76   IF (l_inv_org IS NULL OR l_inv_org = '' OR l_inv_org = 'All')
77     THEN l_inv_org_where := '(EXISTS
78 		(SELECT 1
79 		FROM org_access o
80 		WHERE o.responsibility_id = fnd_global.resp_id
81 		AND o.resp_application_id = fnd_global.resp_appl_id
82 		AND o.organization_id = inv_org)
83 	OR EXISTS
84 		(SELECT 1
85 		FROM mtl_parameters org
86 		WHERE org.organization_id = inv_org
87 		AND NOT EXISTS
88 			(SELECT 1
89 			FROM org_access ora
90 			WHERE org.organization_id = ora.organization_id)))';
91     ELSE l_inv_org_where := 'inv_org = &ORGANIZATION+ORGANIZATION';
92   END IF;
93 
94   IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
95     THEN
96       IF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT')
97 	THEN
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.top_node_flag = ''Y''
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 	ELSE
109 	  l_prod_cat_from := '';
110 	  l_prod_cat_where := '';
111       END IF;
112     ELSE
113       l_prod_cat_from := ',
114 		ENI_DENORM_HIERARCHIES		eni_cat,
115 		MTL_DEFAULT_CATEGORY_SETS	mdcs';
116       l_prod_cat_where := '
117 	    AND fact.item_category_id = eni_cat.child_id
118 	    AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
119 	    AND	eni_cat.dbi_flag = ''Y''
120 	    AND	eni_cat.object_type = ''CATEGORY_SET''
121 	    AND eni_cat.object_id = mdcs.category_set_id
122 	    AND	mdcs.functional_area_id = 11';
123   END IF;
124 
125   IF (l_prod IS NULL OR l_prod = '' OR l_prod = 'All')
126     THEN l_prod_where := '';
127     ELSE l_prod_where := '
128 	    AND fact.item_id IN (&ITEM+ENI_ITEM_ORG)';
129   END IF;
130 
131   IF (l_cust IS NULL OR l_cust = '' OR l_cust = 'All')
132     THEN
133       l_cust_where := '';
134       IF (l_view_by = 'CUSTOMER+FII_CUSTOMERS')
135         THEN l_cust_flag := 0;
136 	ELSE l_cust_flag := 1;
137       END IF;
138     ELSE
139       l_cust_where := '
140 	    AND fact.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
141       l_cust_flag := 0;
142   END IF;
143 
144   IF (l_prod IS NULL OR l_prod = '' OR l_prod = 'All')
145     THEN
146       IF (l_view_by = 'ITEM+ENI_ITEM_ORG')
147 	THEN l_item_cat_flag := 0; -- product
148       ELSIF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT')
149         THEN l_item_cat_flag := 1; -- category
150       ELSE
151 	IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_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; -- product
158   END IF;
159 
160   l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
161   x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
162 
163   l_measures := 'ISC_MEASURE_1,ISC_MEASURE_2,ISC_MEASURE_3,ISC_MEASURE_4,ISC_MEASURE_5,
164 		 ISC_MEASURE_6,ISC_MEASURE_7,ISC_MEASURE_8,ISC_MEASURE_9,ISC_MEASURE_10,
165 		 ISC_MEASURE_11,ISC_MEASURE_12';
166 
167   IF l_view_by = 'ITEM+ENI_ITEM_ORG'
168     THEN l_select_stmt := '
169  SELECT	items.value						VIEWBY,
170 	NULL							ISC_ATTRIBUTE_3, -- drill across url
171 	items.description					ISC_ATTRIBUTE_4, -- item description
172 	'||l_measures||'
173    FROM
174 (SELECT	(rank() over (&ORDER_BY_CLAUSE nulls last, item_id)) - 1		rnk,
175 	item_id,
176 	'||l_measures||'
177    FROM
178 (SELECT	c.item_id,';
179 	l_inner_select_stmt := '
180 		 SELECT	fact.item_id					ITEM_ID,';
181 	l_union_select_stmt := '
182 		 SELECT item_id						ITEM_ID,';
183 	l_union_group_by_stmt := '
184 		GROUP BY item_id';
185 	l_where_stmt := '
186 	ENI_ITEM_ORG_V			items
187   WHERE a.item_id = items.id
188     AND	((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
189   ORDER BY rnk';
190 
191   ELSIF l_view_by = 'ORGANIZATION+ORGANIZATION'
192     THEN l_select_stmt := '
193  SELECT	org.name						VIEWBY,
194 	NULL							ISC_ATTRIBUTE_3, -- drill across url
195 	NULL							ISC_ATTRIBUTE_4, -- item description
196 	'||l_measures||'
197    FROM
198 (SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, inv_org_id)) - 1		rnk,
199 	inv_org_id,
200 	'||l_measures||'
201    FROM
202 (SELECT	c.inv_org_id,	';
203 	l_inner_select_stmt := '
204 		 SELECT	fact.inv_org_id					INV_ORG_ID,';
205 	l_union_select_stmt := '
206 		 SELECT inv_org_id					INV_ORG_ID,';
207 	l_union_group_by_stmt := '
208 		GROUP BY inv_org_id';
209 	l_where_stmt := '
210 	HR_ALL_ORGANIZATION_UNITS_TL	org
211   WHERE a.inv_org_id = org.organization_id
212     AND org.language = :ISC_LANG
213     AND	((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
214   ORDER BY rnk';
215 
216   ELSIF l_view_by = 'CUSTOMER+FII_CUSTOMERS'
217     THEN l_select_stmt := '
218  SELECT	cust.value						VIEWBY,
219 	NULL							ISC_ATTRIBUTE_3, -- drill across url
220 	NULL							ISC_ATTRIBUTE_4, -- item description
221 	'||l_measures||'
222    FROM
223 (SELECT	(rank() over (&ORDER_BY_CLAUSE nulls last, customer_id)) - 1		rnk,
224 	customer_id,
225 	'||l_measures||'
226    FROM
227 (SELECT	c.customer_id,	';
228 	l_inner_select_stmt := '
229 		 SELECT	fact.customer_id				CUSTOMER_ID,';
230 	l_union_select_stmt := '
231 		 SELECT customer_id					CUSTOMER_ID,';
232 	l_union_group_by_stmt := '
233 		GROUP BY customer_id';
234 	l_where_stmt := '
235 	FII_CUSTOMERS_V			cust
236   WHERE a.customer_id = cust.id
237     AND	((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
238   ORDER BY rnk';
239 
240   ELSE -- l_view_by = 'ITEM+ENI_ITEM_VBH_CAT'
241     l_select_stmt := '
242  SELECT	ecat.value					VIEWBY,
243 	ecat.id						VIEWBYID,
244 	decode(ecat.leaf_node_flag, ''Y'',
245 		''pFunctionName=ISC_DBI_FULF_PERF_T&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_ORG&pParamIds=Y'',
246 		''pFunctionName=ISC_DBI_FULF_PERF_T&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'')
247 								ISC_ATTRIBUTE_3, -- drill across url
248 	NULL							ISC_ATTRIBUTE_4, -- item description
249 	'||l_measures||'
250    FROM
251 (SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, item_category_id)) - 1	rnk,
252 	item_category_id,
253 	'||l_measures||'
254    FROM
255 (SELECT	c.item_category_id,	';
256   IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
257     THEN
258 	l_inner_select_stmt := '
259 		 SELECT	eni_cat.parent_id				ITEM_CATEGORY_ID,';
260     ELSE
261 	l_inner_select_stmt := '
262 		 SELECT	eni_cat.imm_child_id				ITEM_CATEGORY_ID,';
263   END IF;
264 	l_union_select_stmt := '
265 		 SELECT item_category_id				ITEM_CATEGORY_ID,';
266 	l_union_group_by_stmt := '
267 		GROUP BY item_category_id';
268 	l_where_stmt := '
269 	ENI_ITEM_VBH_NODES_V		ecat
270   WHERE a.item_category_id = ecat.id
271     AND	ecat.parent_id = ecat.child_id
272     AND	((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
273   ORDER BY rnk';
274   END IF;
275 
276   IF ((l_view_by = 'ITEM+ENI_ITEM_VBH_CAT' OR l_view_by = 'ORGANIZATION+ORGANIZATION') AND
277       (l_prod IS NULL OR l_prod = '' OR l_prod = 'All') AND
278       (l_cust IS NULL OR l_cust = '' OR l_cust = 'All'))
279     THEN
280     	IF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') THEN
281           IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
282 	    THEN
283 	      l_inner_select_stmt := '
284 		 SELECT	fact.parent_id					ITEM_CATEGORY_ID,';
285 	    ELSE
286 	      l_inner_select_stmt := '
287 		 SELECT fact.imm_child_id				ITEM_CATEGORY_ID,';
288 	  END IF;
289         END IF;
290 
291         IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
292 	  THEN
293 	    l_prod_cat_from := '';
294 	    l_prod_cat_where := '
295 		    AND fact.top_node_flag = ''Y''';
296 	  ELSE
297 	    l_prod_cat_from := '';
298 	    l_prod_cat_where := '
299 		    AND fact.parent_id = &ITEM+ENI_ITEM_VBH_CAT';
300 	END IF;
301 	l_mv1 := 'ISC_DBI_CFM_016_MV';
302 	l_mv2 := 'ISC_DBI_CFM_017_MV';
303 	l_flags_where := '';
304     ELSE
305 	l_mv1 := 'ISC_DBI_CFM_004_MV';
306 	l_mv2 := 'ISC_DBI_CFM_005_MV';
307 	l_flags_where := '
308 	    AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
309 	    AND fact.customer_flag = :ISC_CUST_FLAG';
310   END IF;
311 
312   l_stmt := l_select_stmt || '
313 	c.curr_booked_value					ISC_MEASURE_1, -- book
314 	(c.curr_booked_value-c.prev_booked_value)
315 	  / decode(c.prev_booked_value, 0, NULL,
316 		   abs(c.prev_booked_value)) * 100		ISC_MEASURE_2, -- book change
317 	c.curr_fulfill_value					ISC_MEASURE_3, -- fulf
318 	(c.curr_fulfill_value-c.prev_fulfill_value)
319 	  / decode(c.prev_fulfill_value, 0, NULL,
320 		   abs(c.prev_fulfill_value)) * 100		ISC_MEASURE_4, -- fulf change
321 	c.curr_booked_value
322 	  / decode(c.curr_fulfill_value, 0, NULL,
323 		   c.curr_fulfill_value)			ISC_MEASURE_5, -- book to fulf r
324 	c.curr_booked_value
325 	  / decode(c.curr_fulfill_value, 0, NULL,
326 		   c.curr_fulfill_value) -
327 	c.prev_booked_value
328 	  / decode(c.prev_fulfill_value, 0, NULL,
329 		   c.prev_fulfill_value)			ISC_MEASURE_6, -- book to fulf r change
330 	sum(c.curr_booked_value) over ()			ISC_MEASURE_7, -- gd total book
331 	(sum(c.curr_booked_value) over () - sum(c.prev_booked_value) over ())
332 	  / decode(sum(c.prev_booked_value) over (), 0, NULL,
333 		   abs(sum(c.prev_booked_value) over ()))
334 	  * 100							ISC_MEASURE_8, -- gd total book change
335 	sum(c.curr_fulfill_value) over ()			ISC_MEASURE_9, -- gd total fulf
336 	(sum(c.curr_fulfill_value) over () - sum(c.prev_fulfill_value) over ())
337 	  / decode(sum(c.prev_fulfill_value) over (), 0, NULL,
338 		   abs(sum(c.prev_fulfill_value) over ()))
339 	  * 100							ISC_MEASURE_10, -- gd total fulf change
340 	sum(c.curr_booked_value) over ()
341 	  / decode(sum(c.curr_fulfill_value) over (), 0, NULL,
342 		   sum(c.curr_fulfill_value) over ())		ISC_MEASURE_11, -- gd total book to fulf r
343 	sum(c.curr_booked_value) over ()
344 	  / decode(sum(c.curr_fulfill_value) over (), 0, NULL,
345 		   sum(c.curr_fulfill_value) over ()) -
346 	sum(c.prev_booked_value) over ()
347 	  / decode(sum(c.prev_fulfill_value) over (), 0, NULL,
348 		   sum(c.prev_fulfill_value) over ())		ISC_MEASURE_12 -- gd total book to fulf r change
349 	   FROM	('||l_union_select_stmt||'
350 		sum(curr_booked_value)					CURR_BOOKED_VALUE,
351 		sum(prev_booked_value)					PREV_BOOKED_VALUE,
352 		sum(curr_fulfill_value)					CURR_FULFILL_VALUE,
353 		sum(prev_fulfill_value)					PREV_FULFILL_VALUE
354 	   FROM ('||l_inner_select_stmt||'
355 		fact.inv_org_id						INV_ORG,
356 		decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
357 			   fact.booked_amt_'||l_curr_suffix||', 0)	CURR_BOOKED_VALUE,
358 		decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
359 			   fact.booked_amt_'||l_curr_suffix||', 0)	PREV_BOOKED_VALUE,
360 		0							CURR_FULFILL_VALUE,
361 		0							PREV_FULFILL_VALUE
362 	   FROM '||l_mv1||'			fact,
363 		FII_TIME_RPT_STRUCT_V		cal'||l_prod_cat_from||'
364 	  WHERE fact.time_id = cal.time_id'||l_flags_where||'
365 	    AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
366 	    AND cal.period_type_id = fact.period_type_id
367 	    AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'
368 		||l_prod_cat_where
369 		||l_prod_where
370 		||l_cust_where||'
371 		UNION ALL
372 		'||l_inner_select_stmt||'
373 		fact.inv_org_id						INV_ORG,
374 		0							CURR_BOOKED_VALUE,
375 		0							PREV_BOOKED_VALUE,
376 		decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
377 			   fact.fulfilled_amt_'||l_curr_suffix||', 0)	CURR_FULFILL_VALUE,
378 		decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
379 			   fact.fulfilled_amt_'||l_curr_suffix||', 0)	PREV_FULFILL_VALUE
380 	   FROM '||l_mv2||'			fact,
381 		FII_TIME_RPT_STRUCT_V		cal'||l_prod_cat_from||'
382 	  WHERE fact.time_id = cal.time_id'||l_flags_where||'
383 	    AND	fact.return_flag = 0
384 	    AND fact.internal_flag = 0
385 	    AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
386 	    AND cal.period_type_id = fact.period_type_id
387 	    AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'
388 		||l_prod_cat_where
389 		||l_prod_where
390 		||l_cust_where||')
391 	  WHERE	'||l_inv_org_where
392 		||l_union_group_by_stmt||')	c))	a,'
393 		||l_where_stmt;
394 
395   x_custom_sql := l_stmt;
396 
397   l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
398   l_custom_rec.attribute_value := to_char(l_item_cat_flag);
399   l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
400   l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
401   x_custom_output.extend;
402   x_custom_output(1) := l_custom_rec;
403 
404   l_custom_rec.attribute_name := ':ISC_CUST_FLAG';
405   l_custom_rec.attribute_value := to_char(l_cust_flag);
406   l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
407   l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
408   x_custom_output.extend;
409   x_custom_output(2) := l_custom_rec;
410 
411   l_custom_rec.attribute_name := ':ISC_LANG';
412   l_custom_rec.attribute_value := l_lang;
413   l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
414   l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
415   x_custom_output.EXTEND;
416   x_custom_output(3) := l_custom_rec;
417 
418 END Get_Sql;
419 
420 END ISC_DBI_FULF_PERF_T_PKG;