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_PKG

Source


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