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_BACKLOG_PDUE_PKG

Source


1 PACKAGE BODY ISC_DBI_BACKLOG_PDUE_PKG AS
2 /* $Header: ISCRGA2B.pls 120.0 2005/05/25 17:29:57 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(32000);
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_mv_book			VARCHAR2(100);
18   l_mv_fulf			VARCHAR2(100);
19   l_flags_where			VARCHAR2(1000);
20   l_flags_where2		VARCHAR2(1000);
21   l_inv_org			VARCHAR2(10000);
22   l_inv_org_where		VARCHAR2(10000);
23   l_prod			VARCHAR2(10000);
24   l_prod_where			VARCHAR2(10000);
25   l_prod_cat			VARCHAR2(10000);
26   l_prod_cat_from		VARCHAR2(10000);
27   l_prod_cat_where		VARCHAR2(10000);
28   l_cust			VARCHAR2(10000);
29   l_cust_where			VARCHAR2(10000);
30   l_curr			VARCHAR2(10000);
31   l_curr_suffix			VARCHAR2(120);
32   l_view_by			VARCHAR2(120);
33   l_lang			VARCHAR2(10);
34   l_item_cat_flag		NUMBER;
35   l_cust_flag			NUMBER;
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 = 'All')
77     THEN l_inv_org_where := '
78 	AND (EXISTS
79 		(SELECT 1
80 		FROM org_access o
81 		WHERE o.responsibility_id = fnd_global.resp_id
82 		AND o.resp_application_id = fnd_global.resp_appl_id
83 		AND o.organization_id = fact.inv_org_id)
84 	OR EXISTS
85 		(SELECT 1
86 		FROM mtl_parameters org
87 		WHERE org.organization_id = fact.inv_org_id
88 		AND NOT EXISTS
89 			(SELECT 1
90 			FROM org_access ora
91 			WHERE org.organization_id = ora.organization_id)))';
92     ELSE l_inv_org_where := '
93 	    AND fact.inv_org_id = &ORGANIZATION+ORGANIZATION';
94   END IF;
95 
96   IF (l_prod_cat IS NULL OR l_prod_cat = 'All')
97     THEN
98       IF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT')
99 	THEN
100 	  l_prod_cat_from := ',
101 		ENI_DENORM_HIERARCHIES		eni_cat,
102 		MTL_DEFAULT_CATEGORY_SETS	mdcs';
103 	  l_prod_cat_where := '
104 	    AND fact.item_category_id = eni_cat.child_id
105 	    AND eni_cat.top_node_flag = ''Y''
106 	    AND	eni_cat.dbi_flag = ''Y''
107 	    AND eni_cat.object_type = ''CATEGORY_SET''
108 	    AND	eni_cat.object_id = mdcs.category_set_id
109 	    AND	mdcs.functional_area_id = 11';
110 	ELSE
111 	  l_prod_cat_from := '';
112 	  l_prod_cat_where := '';
113       END IF;
114     ELSE
115       l_prod_cat_from := ',
116 		ENI_DENORM_HIERARCHIES		eni_cat,
117 		MTL_DEFAULT_CATEGORY_SETS	mdcs';
118       l_prod_cat_where := '
119 	    AND fact.item_category_id = eni_cat.child_id
120 	    AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
121 	    AND	eni_cat.dbi_flag = ''Y''
122 	    AND	eni_cat.object_type = ''CATEGORY_SET''
123 	    AND eni_cat.object_id = mdcs.category_set_id
124 	    AND	mdcs.functional_area_id = 11';
125   END IF;
126 
127   IF (l_prod IS NULL OR l_prod = 'All')
128     THEN l_prod_where := '';
129     ELSE l_prod_where := '
130 	    AND fact.item_id IN (&ITEM+ENI_ITEM_ORG)';
131   END IF;
132 
133   IF (l_cust IS NULL OR l_cust = 'All')
134     THEN
135       l_cust_where := '';
136       IF (l_view_by = 'CUSTOMER+FII_CUSTOMERS')
137         THEN l_cust_flag := 0;
138 	ELSE l_cust_flag := 1;
139       END IF;
140     ELSE
141       l_cust_where := '
142 	    AND fact.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
143       l_cust_flag := 0;
144   END IF;
145 
146   IF (l_prod IS NULL OR l_prod = 'All')
147     THEN
148       IF (l_view_by = 'ITEM+ENI_ITEM_ORG')
149 	THEN l_item_cat_flag := 0; -- product
150       ELSIF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT')
151         THEN l_item_cat_flag := 1; -- category
152       ELSE
153 	IF (l_prod_cat IS NULL OR l_prod_cat = 'All')
154 	  THEN l_item_cat_flag := 3; -- all
155 	  ELSE l_item_cat_flag := 1; -- category
156 	END IF;
157       END IF;
158   ELSE
159     l_item_cat_flag := 0; -- product
160   END IF;
161 
162   l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
163   x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
164 
165   l_measures := 'ISC_MEASURE_1,ISC_MEASURE_2,ISC_MEASURE_3,ISC_MEASURE_4,ISC_MEASURE_5,
166 	ISC_MEASURE_6,ISC_MEASURE_7,ISC_MEASURE_8,ISC_MEASURE_9,ISC_MEASURE_10,
167 	ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13,ISC_MEASURE_14,ISC_MEASURE_15,
168 	ISC_MEASURE_16,ISC_MEASURE_17,ISC_MEASURE_18,ISC_MEASURE_19,ISC_MEASURE_20,
169 	ISC_MEASURE_22,ISC_MEASURE_23';
170 
171   IF l_view_by = 'ITEM+ENI_ITEM_ORG'
172     THEN l_select_stmt := '
173  SELECT	items.value			VIEWBY,
174 	items.id			VIEWBYID,
175 	NULL				ISC_ATTRIBUTE_3, -- drill across url
176 	items.description		ISC_ATTRIBUTE_4, -- item description
177 	mtl.unit_of_measure		ISC_ATTRIBUTE_5, -- item uom
178 	'||l_measures||'
179    FROM
180 (SELECT	(rank() over (&ORDER_BY_CLAUSE nulls last, item_id)) - 1	rnk,
181 	item_id,
182 	uom,
183 	'||l_measures||'
184    FROM
185 (SELECT	c.item_id,
186 	c.uom,		';
187 	l_inner_select_stmt := '
188 		 SELECT	fact.item_id		ITEM_ID,
189 			fact.uom		UOM,';
190 	l_union_select_stmt := '
191 		 SELECT item_id			ITEM_ID,
192 			uom			UOM,';
193 	l_union_group_by_stmt := '
194 		GROUP BY item_id, uom';
195 	l_where_stmt := '
196 	ENI_ITEM_ORG_V			items,
197 	MTL_UNITS_OF_MEASURE_TL		mtl
198   WHERE a.item_id = items.id
199     AND	a.uom = mtl.uom_code
200     AND	mtl.language = :ISC_LANG
201     AND	((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
202   ORDER BY rnk';
203 
204   ELSIF l_view_by = 'ORGANIZATION+ORGANIZATION'
205     THEN l_select_stmt := '
206  SELECT	org.name			VIEWBY,
207 	org.organization_id		VIEWBYID,
208 	NULL				ISC_ATTRIBUTE_3, -- drill across url
209 	NULL				ISC_ATTRIBUTE_4, -- item description
210 	NULL				ISC_ATTRIBUTE_5, -- item uom
211 	'||l_measures||'
212    FROM
213 (SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, inv_org_id)) - 1	rnk,
214 	inv_org_id,
215 	'||l_measures||'
216    FROM
217 (SELECT	c.inv_org_id,	';
218 	l_inner_select_stmt := '
219 		 SELECT	fact.inv_org_id		INV_ORG_ID,';
220 	l_union_select_stmt := '
221 		 SELECT inv_org_id		INV_ORG_ID,';
222 	l_union_group_by_stmt := '
223 		GROUP BY inv_org_id';
224 	l_where_stmt := '
225 	HR_ALL_ORGANIZATION_UNITS_TL	org
226   WHERE a.inv_org_id = org.organization_id
227     AND org.language = :ISC_LANG
228     AND	((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
229   ORDER BY rnk';
230 
231   ELSIF l_view_by = 'CUSTOMER+FII_CUSTOMERS'
232     THEN l_select_stmt := '
233  SELECT	cust.value			VIEWBY,
234 	cust.id				VIEWBYID,
235 	NULL				ISC_ATTRIBUTE_3, -- drill across url
236 	NULL				ISC_ATTRIBUTE_4, -- item description
237 	NULL				ISC_ATTRIBUTE_5, -- item uom
238 	'||l_measures||'
239    FROM
240 (SELECT	(rank() over (&ORDER_BY_CLAUSE nulls last, customer_id)) - 1	rnk,
241 	customer_id,
242 	'||l_measures||'
243    FROM
244 (SELECT	c.customer_id,	';
245 	l_inner_select_stmt := '
246 		 SELECT	fact.customer_id	CUSTOMER_ID,';
247 	l_union_select_stmt := '
248 		 SELECT customer_id		CUSTOMER_ID,';
249 	l_union_group_by_stmt := '
250 		GROUP BY customer_id';
251 	l_where_stmt := '
252 	FII_CUSTOMERS_V			cust
253   WHERE a.customer_id = cust.id
254     AND	((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
255   ORDER BY rnk';
256 
257   ELSE -- l_view_by = 'ITEM+ENI_ITEM_VBH_CAT'
258     l_select_stmt := '
259  SELECT	ecat.value			VIEWBY,
260 	ecat.id				VIEWBYID,
261 	decode(ecat.leaf_node_flag, ''Y'',
262 		''pFunctionName=ISC_DBI_BACKLOG_PDUE&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_ORG&pParamIds=Y'',
263 		''pFunctionName=ISC_DBI_BACKLOG_PDUE&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'')
264 					ISC_ATTRIBUTE_3, -- drill across url
265 	NULL				ISC_ATTRIBUTE_4, -- item description
266 	NULL				ISC_ATTRIBUTE_5, -- item uom
267 	'||l_measures||'
268    FROM
269 (SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, item_category_id)) - 1	rnk,
270 	item_category_id,
271 	'||l_measures||'
272    FROM
273 (SELECT	c.item_category_id,	';
274   IF (l_prod_cat IS NULL OR l_prod_cat = 'All')
275     THEN
276 	l_inner_select_stmt := '
277 		 SELECT	eni_cat.parent_id	ITEM_CATEGORY_ID,';
278     ELSE
279 	l_inner_select_stmt := '
280 		 SELECT	eni_cat.imm_child_id	ITEM_CATEGORY_ID,';
281   END IF;
282 	l_union_select_stmt := '
283 		 SELECT item_category_id	ITEM_CATEGORY_ID,';
284 	l_union_group_by_stmt := '
285 		GROUP BY item_category_id';
286 	l_where_stmt := '
287 	ENI_ITEM_VBH_NODES_V		ecat
288   WHERE a.item_category_id = ecat.id
289     AND	ecat.parent_id = ecat.child_id
290     AND	((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
291   ORDER BY rnk';
292   END IF;
293 
294   IF ((l_view_by = 'ITEM+ENI_ITEM_VBH_CAT' OR l_view_by = 'ORGANIZATION+ORGANIZATION') AND
295       (l_prod IS NULL OR l_prod = 'All') AND
296       (l_cust IS NULL OR l_cust = 'All'))
297     THEN
298     	IF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') THEN
299 	  IF (l_prod_cat IS NULL OR l_prod_cat = 'All')
300 	    THEN
301 	      l_inner_select_stmt := '
302 		 SELECT	fact.parent_id		ITEM_CATEGORY_ID,';
303 	    ELSE
304 	      l_inner_select_stmt := '
305 		 SELECT fact.imm_child_id	ITEM_CATEGORY_ID,';
306 	  END IF;
307 	END IF;
308 
309 	IF (l_prod_cat IS NULL OR l_prod_cat = 'All')
310 	  THEN
311 	    l_prod_cat_from := '';
312 	    l_prod_cat_where := '
313 		    AND	fact.top_node_flag = ''Y''';
314 	  ELSE
315 	    l_prod_cat_from := '';
316 	    l_prod_cat_where := '
317 		    AND fact.parent_id = &ITEM+ENI_ITEM_VBH_CAT';
318 	END IF;
319 	l_mv1 := 'ISC_DBI_CFM_001_MV';
320 	l_mv_book := 'ISC_DBI_CFM_009_MV';
321 	l_mv_fulf := 'ISC_DBI_CFM_011_MV';
322 	l_mv2 := 'ISC_DBI_CFM_012_MV';
323 	l_flags_where := '';
324 	l_flags_where2 := '
325 	    AND fact.inv_org_flag = 0';
326     ELSE
327 	l_mv1 := 'ISC_DBI_CFM_010_MV';
328 	l_mv_book := 'ISC_DBI_CFM_000_MV';
329 	l_mv_fulf := 'ISC_DBI_CFM_002_MV';
330 	l_mv2 := 'ISC_DBI_CFM_008_MV';
331 	l_flags_where := '
332 	    AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
333 	    AND fact.customer_flag = :ISC_CUST_FLAG';
334 	l_flags_where2 := '
335 	    AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
336 	    AND fact.customer_flag = :ISC_CUST_FLAG';
337   END IF;
338 
339   l_stmt := l_select_stmt || '
340 	c.curr_bklg_qty						ISC_MEASURE_1, -- bklg qty
341 	c.curr_bklg_value					ISC_MEASURE_2, -- bklg
342 	(c.curr_bklg_value - c.prev_bklg_value)
343 	  / decode(c.prev_bklg_value, 0, NULL,
344 		   abs(c.prev_bklg_value)) * 100		ISC_MEASURE_3, -- bklg change
345 	c.curr_bklg_value
346 	  / decode(sum(c.curr_bklg_value) over (), 0, NULL,
347 		   sum(c.curr_bklg_value) over ()) * 100	ISC_MEASURE_4, -- bklg % of total
348 	c.curr_pdue_qty						ISC_MEASURE_5, -- pdue qty
349 	c.curr_pdue_value					ISC_MEASURE_6, -- pdue
350 	(c.curr_pdue_value - c.prev_pdue_value)
351 	  / decode(c.prev_pdue_value, 0, NULL,
352 		   abs(c.prev_pdue_value)) * 100		ISC_MEASURE_7, -- pdue change
353 	c.curr_pdue_value
354 	  / decode(sum(c.curr_pdue_value) over (), 0, NULL,
355 		   sum(c.curr_pdue_value) over ()) * 100	ISC_MEASURE_8, -- pdue % of total
356 	sum(c.curr_bklg_value) over ()				ISC_MEASURE_9, -- gd total bklg
357 	(sum(c.curr_bklg_value) over () - sum(c.prev_bklg_value) over ())
358 	  / decode(sum(c.prev_bklg_value) over (), 0, NULL,
359 		   abs(sum(c.prev_bklg_value) over ())) * 100	ISC_MEASURE_10, -- gd total bklg change
360 	sum(c.curr_bklg_value) over ()
361 	  / decode(sum(c.curr_bklg_value) over (), 0, NULL,
362 		   sum(c.curr_bklg_value) over ()) * 100	ISC_MEASURE_11, -- gd total bklg % of total
363 	sum(c.curr_pdue_value) over ()				ISC_MEASURE_12, -- gd total pdue
364 	(sum(c.curr_pdue_value) over () - sum(c.prev_pdue_value) over ())
365 	  / decode(sum(c.prev_pdue_value) over (), 0, NULL,
366 		   abs(sum(c.prev_pdue_value) over ())) * 100	ISC_MEASURE_13, -- gd total pdue change
367 	sum(c.curr_pdue_value) over ()
368 	  / decode(sum(c.curr_pdue_value) over (), 0, NULL,
369 		   sum(c.curr_pdue_value) over ()) * 100	ISC_MEASURE_14, -- gd total pdue % of total
370 	c.curr_bklg_value					ISC_MEASURE_15, -- KPI bklg
371 	c.prev_bklg_value					ISC_MEASURE_16, -- KPI bklg prior
372 	c.curr_pdue_value					ISC_MEASURE_17, -- KPI pdue
373 	c.prev_pdue_value					ISC_MEASURE_18, -- KPI pdue prior
374 	sum(c.curr_bklg_value) over ()				ISC_MEASURE_19, -- gd total KPI bklg
375 	sum(c.prev_bklg_value) over ()				ISC_MEASURE_20, -- gd total KPI bklg prior
376 	sum(c.curr_pdue_value) over ()				ISC_MEASURE_22,	-- gd total KPI pdue
377 	sum(c.prev_pdue_value) over ()				ISC_MEASURE_23	-- gd.total KPI pdue prior
378 	   FROM	('||l_union_select_stmt||'
379 		sum(y_bklg_qty) + sum(book_qty_ytd)
380 		  - sum(fulf_qty_ytd)		CURR_BKLG_QTY,
381 		sum(c_y_bklg) + sum(c_book_ytd)
382 		  - sum(c_fulf_ytd)		CURR_BKLG_VALUE,
383 		sum(p_y_bklg) + sum(p_book_ytd)
384 		  - sum(p_fulf_ytd)		PREV_BKLG_VALUE,
385 		sum(curr_pdue_qty)		CURR_PDUE_QTY,
386 		sum(curr_pdue_value)		CURR_PDUE_VALUE,
387 		sum(prev_pdue_value)		PREV_PDUE_VALUE
388 	   FROM /* Compute year backlog balance */
389 		('||l_inner_select_stmt||'
390 		decode(time.report_date, &BIS_CURRENT_ASOF_DATE,
391 			fact.bklog_qty, 0)	Y_BKLG_QTY,
392 		0				BOOK_QTY_YTD,
393 		0				FULF_QTY_YTD,
394 		decode(time.report_date, &BIS_CURRENT_ASOF_DATE,
395 			fact.bklg_amt_'||l_curr_suffix||', 0)	C_Y_BKLG,
396 		decode(time.report_date, &BIS_PREVIOUS_ASOF_DATE,
397 			fact.bklg_amt_'||l_curr_suffix||', 0)	P_Y_BKLG,
398 		0				C_BOOK_YTD,
399 		0				P_BOOK_YTD,
400 		0				C_FULF_YTD,
401 		0				P_FULF_YTD,
402 		NULL				CURR_PDUE_QTY,
403 		NULL				CURR_PDUE_VALUE,
404 		NULL				PREV_PDUE_VALUE
405 	   FROM '||l_mv1||'		fact,
406 		FII_TIME_DAY			time'||l_prod_cat_from||'
407 	  WHERE time.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
408 	    AND time.ent_year_start_date = fact.time_snapshot_date_id'
409 	    	||l_flags_where
410 		||l_inv_org_where
411 		||l_prod_cat_where
412 		||l_prod_where
413 		||l_cust_where||'
414 	UNION ALL /* Computer YTD net Booking */'
415 		||l_inner_select_stmt||'
416 		0				Y_BKLG_QTY,
420 		0				C_Y_BKLG,
417 		decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
418 			fact.booked_qty2, 0)	BOOK_QTY_YTD,
419 		0				FULF_QTY_YTD,
421 		0				P_Y_BKLG,
422 		decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
423 		       fact.booked_amt2_'||l_curr_suffix||', 0)	C_BOOK_YTD,
424 		decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
425 		       fact.booked_amt2_'||l_curr_suffix||', 0)	P_BOOK_YTD,
426 		0				C_FULF_YTD,
427 		0				P_FULF_YTD,
428 		NULL				CURR_PDUE_QTY,
429 		NULL				CURR_PDUE_VALUE,
430 		NULL				PREV_PDUE_VALUE
431 	   FROM '||l_mv_book||'		fact,
432 		FII_TIME_RPT_STRUCT_V		cal'||l_prod_cat_from||'
433 	  WHERE	cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
434 	    AND fact.time_id = cal.time_id
435 	    AND fact.period_type_id = cal.period_type_id
436 	    AND bitand(cal.record_type_id, 119) = cal.record_type_id'
437 	    	||l_flags_where2
438 		||l_inv_org_where
439 		||l_prod_cat_where
440 		||l_prod_where
441 		||l_cust_where||'
442 	UNION ALL /* Computer YTD net Fulfillment */'
443 		||l_inner_select_stmt||'
444 		0				Y_BKLG_QTY,
445 		0				BOOK_QTY_YTD,
446 		decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
447 			fact.booked_qty, 0)	FULF_QTY_YTD,
448 		0				C_Y_BKLG,
449 		0				P_Y_BKLG,
450 		0				C_BOOK_YTD,
451 		0				P_BOOK_YTD,
452 		decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
453 			fact.booked_amt_'||l_curr_suffix||', 0)	C_FULF_YTD,
454 		decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
455 			fact.booked_amt_'||l_curr_suffix||', 0)	P_FULF_YTD,
456 		NULL				CURR_PDUE_QTY,
457 		NULL				CURR_PDUE_VALUE,
458 		NULL				PREV_PDUE_VALUE
459 	   FROM '||l_mv_fulf||'		fact,
460 		FII_TIME_RPT_STRUCT_V		cal'||l_prod_cat_from||'
461 	  WHERE	cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
462 	    AND fact.time_id = cal.time_id
463 	    AND fact.period_type_id = cal.period_type_id
464 	    AND bitand(cal.record_type_id, 119) = cal.record_type_id'
465 	    	||l_flags_where2
466 		||l_inv_org_where
467 		||l_prod_cat_where
468 		||l_prod_where
469 		||l_cust_where||'
470 	UNION ALL'
471 		||l_inner_select_stmt||'
472 		0				Y_BKLG_QTY,
473 		0				BOOK_QTY_YTD,
474 		0				FULF_QTY_YTD,
475 		0				C_Y_BKLG,
476 		0				P_Y_BKLG,
477 		0				C_BOOK_YTD,
478 		0				P_BOOK_YTD,
479 		0				C_FULF_YTD,
480 		0				P_FULF_YTD,
481 		decode(fact.time_snapshot_date_id, a.day,
482 		       fact.pdue_qty, NULL)	CURR_PDUE_QTY,
483 		decode(fact.time_snapshot_date_id, a.day,
484 		       fact.pdue_amt_'||l_curr_suffix||', NULL)	CURR_PDUE_VALUE,
488 		   FROM	'||l_mv2||'			fact
485 		decode(fact.time_snapshot_date_id, b.day,
486 		       fact.pdue_amt_'||l_curr_suffix||', NULL)	PREV_PDUE_VALUE
487 	   FROM (SELECT max(time_snapshot_date_id)		day
489 		  WHERE	fact.time_snapshot_date_id BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
490 						       AND &BIS_CURRENT_ASOF_DATE
491 					)	a,
492 		(SELECT max(time_snapshot_date_id)		day
493 		   FROM	'||l_mv2||'			fact
494 		  WHERE	fact.time_snapshot_date_id BETWEEN &BIS_PREVIOUS_EFFECTIVE_START_DATE
495 						       AND &BIS_PREVIOUS_ASOF_DATE
496 					)	b,
497 		'||l_mv2||'		fact'||l_prod_cat_from||'
498 	  WHERE fact.time_snapshot_date_id IN (a.day, b.day)
499 	    AND fact.late_schedule_flag = 1'
500 		||l_flags_where
501 		||l_inv_org_where
502 		||l_prod_cat_where
503 		||l_prod_where
504 		||l_cust_where||')'	-- end of A UNION ALL B
505 		||l_union_group_by_stmt||')	c))	a,'
506 		||l_where_stmt;
507 
508   x_custom_sql := l_stmt;
509 
510   l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
511   l_custom_rec.attribute_value := to_char(l_item_cat_flag);
512   l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
513   l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
514   x_custom_output.extend;
515   x_custom_output(1) := l_custom_rec;
516 
517   l_custom_rec.attribute_name := ':ISC_CUST_FLAG';
518   l_custom_rec.attribute_value := to_char(l_cust_flag);
519   l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
520   l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
521   x_custom_output.extend;
522   x_custom_output(2) := l_custom_rec;
523 
524   l_custom_rec.attribute_name := ':ISC_LANG';
525   l_custom_rec.attribute_value := l_lang;
529   x_custom_output(3) := l_custom_rec;
526   l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
527   l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
528   x_custom_output.EXTEND;
530 
531 END Get_Sql;
532 
533 END ISC_DBI_BACKLOG_PDUE_PKG;