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_PLAN_RM_PKG

Source


1 PACKAGE BODY ISC_DBI_PLAN_RM_PKG AS
2 /* $Header: ISCRGAIB.pls 120.0 2005/05/25 17:26:52 appldev 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_plan			VARCHAR2(10000);
11   l_plan2			VARCHAR2(10000);
12   l_outer_sql			VARCHAR2(10000);
13   l_formula_sql			VARCHAR2(10000);
14   l_inner_sql			VARCHAR2(10000);
15   l_inner_select_stmt		VARCHAR2(10000);
16   l_inner_group_by_stmt		VARCHAR2(10000);
17   l_flags_where 		VARCHAR2(1000);
18   l_mv				VARCHAR2(100);
19   l_qty_select			VARCHAR2(10000);
20   l_view_by			VARCHAR2(10000);
21   l_org				VARCHAR2(10000);
22   l_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_period_type 		VARCHAR2(10000);
29   l_period_type_id		NUMBER;
30   l_time_from			DATE;
31   l_item_cat_flag		NUMBER;
32   l_lang			VARCHAR2(10);
33   l_union1_flag			NUMBER := 0;
34   l_row_filter			VARCHAR2(10000);
35   l_custom_rec			BIS_QUERY_ATTRIBUTES;
36   l_curr			VARCHAR2(10000);
37   l_curr_g			VARCHAR2(15) := '''FII_GLOBAL1''';
38   l_curr_g1			VARCHAR2(15) := '''FII_GLOBAL2''';
39   l_curr_suffix                 VARCHAR2(15);
40 
41 BEGIN
42 
43   FOR i IN 1..p_param.COUNT
44   LOOP
45     IF (p_param(i).parameter_name = 'PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT')
46       THEN l_plan := p_param(i).parameter_value;
47     END IF;
48 
49     IF (p_param(i).parameter_name = 'PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2')
50       THEN l_plan2 := p_param(i).parameter_value;
51     END IF;
52 
53     IF (p_param(i).parameter_name = 'VIEW_BY')
54       THEN l_view_by := p_param(i).parameter_value;
55     END IF;
56 
57     IF(p_param(i).parameter_name = 'PERIOD_TYPE')
58       THEN l_period_type := p_param(i).parameter_value;
59     END IF;
60 
61     IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM')
62       THEN l_time_from :=  p_param(i).period_date;
63     END IF;
64 
65     IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM')
66       THEN l_time_from :=  p_param(i).period_date;
67     END IF;
68 
69     IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM')
70       THEN l_time_from :=  p_param(i).period_date;
71     END IF;
72 
73     IF (p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
74       THEN l_org := p_param(i).parameter_value;
75     END IF;
76 
77     IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT')
78       THEN l_prod_cat := p_param(i).parameter_value;
79     END IF;
80 
81     IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG')
82       THEN l_prod := p_param(i).parameter_value;
83     END IF;
84 
85     IF (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
86       THEN l_curr := p_param(i).parameter_id;
87     END IF;
88 
89   END LOOP;
90 
91   IF (l_curr = l_curr_g)
92     THEN
93     	l_curr_suffix := '_g';
94     ELSIF (l_curr = l_curr_g1)
95 	THEN
96 		l_curr_suffix := '_g1';
97 	ELSE
98     		l_curr_suffix := '';
99   END IF;
100 
101   IF (l_org IS NULL OR l_org = 'All')
102     THEN l_org_where := '
103 	    AND (EXISTS
104 		  (SELECT 1
105 		   FROM org_access o
106 		   WHERE o.responsibility_id = fnd_global.resp_id
107 		   AND o.resp_application_id = fnd_global.resp_appl_id
108 		   AND o.organization_id = f.organization_id)
109 	     	 OR EXISTS
110 		  (SELECT 1
111 		   FROM mtl_parameters org
112 		   WHERE org.organization_id = f.organization_id
113 		   AND NOT EXISTS
114 			    (SELECT 1
115 			     FROM org_access ora
116 			     WHERE org.organization_id = ora.organization_id)))';
117     ELSE l_org_where := '
118 	    AND f.organization_id = &ORGANIZATION+ORGANIZATION';
119   END IF;
120 
121   IF (l_prod_cat IS NULL OR l_prod_cat = 'All')
122     THEN
123       IF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT')
124 	THEN
125 	  l_prod_cat_from := ',
126 		ENI_DENORM_HIERARCHIES		eni_cat,
127 		MTL_DEFAULT_CATEGORY_SETS	mdcs';
128 	  l_prod_cat_where := '
129 	    AND f.vbh_category_id = eni_cat.child_id
130 	    AND eni_cat.top_node_flag = ''Y''
131 	    AND	eni_cat.dbi_flag = ''Y''
132 	    AND eni_cat.object_type = ''CATEGORY_SET''
133 	    AND	eni_cat.object_id = mdcs.category_set_id
134 	    AND	mdcs.functional_area_id = 11';
135 	ELSE
136 	  l_prod_cat_from := '';
137 	  l_prod_cat_where := '';
138       END IF;
139     ELSE
140       l_prod_cat_from := ',
141 		ENI_DENORM_HIERARCHIES		eni_cat,
142 		MTL_DEFAULT_CATEGORY_SETS	mdcs';
143       l_prod_cat_where := '
144 	    AND f.vbh_category_id = eni_cat.child_id
145 	    AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
146 	    AND	eni_cat.dbi_flag = ''Y''
147 	    AND	eni_cat.object_type = ''CATEGORY_SET''
148 	    AND eni_cat.object_id = mdcs.category_set_id
149 	    AND	mdcs.functional_area_id = 11';
150   END IF;
151 
152   IF (l_prod IS NULL OR l_prod = 'All')
153     THEN l_prod_where := '';
154     ELSE l_prod_where := '
155 	    AND f.item_id IN (&ITEM+ENI_ITEM_ORG)';
156   END IF;
157 
158   IF (l_prod IS NULL OR l_prod = 'All')
159     THEN
160       IF (l_view_by = 'ITEM+ENI_ITEM_ORG')
161 	THEN l_item_cat_flag := 0; -- product
162       ELSIF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT')
163         THEN l_item_cat_flag := 2; -- category
164       ELSE
165 	IF (l_prod_cat IS NULL OR l_prod_cat = 'All')
166 	  THEN l_item_cat_flag := 3; -- all
167 	  ELSE l_item_cat_flag := 2; -- category
168 	END IF;
169       END IF;
170   ELSE
171     l_item_cat_flag := 0; -- product
172   END IF;
173 
174   IF (l_period_type = 'FII_TIME_ENT_PERIOD') THEN
175 	l_period_type_id := 32;
176   ELSIF (l_period_type = 'FII_TIME_ENT_QTR') THEN
177 	l_period_type_id := 64;
178   ELSE -- l_period_type = 'FII_TIME_ENT_YEAR'
179 	l_period_type_id := 128;
180   END IF;
181 
182   l_lang := USERENV('LANG');
183 
184   l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
185   x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
186 
187   IF (l_plan IS NULL OR l_plan2 IS NULL)
188     THEN l_stmt := '
189 SELECT	0	VIEWBY,
190 	0	VIEWBYID,
191 	0	ISC_ATTRIBUTE_1,
192 	0	ISC_ATTRIBUTE_2,
193 	0	ISC_ATTRIBUTE_3,
194 	0 	ISC_MEASURE_1,
195 	0 	ISC_MEASURE_2,
196 	0 	ISC_MEASURE_3,
197 	0 	ISC_MEASURE_4,
198 	0 	ISC_MEASURE_5,
199 	0 	ISC_MEASURE_6,
200 	0 	ISC_MEASURE_7,
201 	0 	ISC_MEASURE_8,
202 	0 	ISC_MEASURE_9,
203 	0 	ISC_MEASURE_10,
204 	0 	ISC_MEASURE_11,
205 	0 	ISC_MEASURE_12,
206 	0 	ISC_MEASURE_13,
207 	0 	ISC_MEASURE_14,
208 	0 	ISC_MEASURE_15,
209 	0 	ISC_MEASURE_16,
210 	0 	ISC_MEASURE_17,
211 	0 	ISC_MEASURE_18,
212 	0 	ISC_MEASURE_19,
213 	0 	ISC_MEASURE_20,
214 	0 	ISC_MEASURE_21,
215 	0 	ISC_MEASURE_22,
216 	0 	ISC_MEASURE_23,
217 	0 	ISC_MEASURE_24
218   FROM	dual
219  WHERE	1 = 2 /* PLAN_SNAPSHOT dimension has not been populated */';
220     ELSE
221 
222   IF ((l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') AND
223       (l_prod_cat IS NULL OR l_prod_cat = 'All'))
224     THEN
225 	l_inner_select_stmt := 'SELECT eni_cat.parent_id			VBH_CATEGORY_ID,';
226 	l_inner_group_by_stmt := '
227 	      GROUP BY eni_cat.parent_id';
228     ELSE
229 	l_inner_select_stmt := 'SELECT eni_cat.imm_child_id			VBH_CATEGORY_ID,';
230 	l_inner_group_by_stmt := '
231 	      GROUP BY eni_cat.imm_child_id';
232   END IF;
233 
234   IF ((l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') AND
235       (l_prod_cat IS NULL OR l_prod_cat = 'All') AND
236       (l_prod IS NULL OR l_prod = 'All'))
237     THEN
238 	l_inner_select_stmt := 'SELECT f.parent_id			VBH_CATEGORY_ID,';
239 	l_inner_group_by_stmt := '
240 	      GROUP BY f.parent_id';
241 	l_prod_cat_from := '';
242 	l_prod_cat_where := '';
243 	l_mv := 'ISC_DBI_PM_0003_MV';
244 	l_flags_where := '';
245     ELSE
246   	l_mv := 'ISC_DBI_PM_0001_MV';
247   	l_flags_where := '
248 	    AND f.item_cat_flag = :ISC_ITEM_CAT_FLAG
249 	    AND f.union1_flag <> :ISC_UNION1_FLAG';
250   END IF;
251 
252   IF l_view_by = 'ITEM+ENI_ITEM_ORG'
253     THEN l_qty_select := '
254 		sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
255 			   f.mds_quantity, 0))		QTY,';
256     ELSE l_qty_select := '
257 		null					QTY,';
258   END IF;
259 
260 -- Filter out rows with only 0 or N/A
261   l_row_filter := '
262 	WHERE (ISC_MEASURE_2 IS NOT NULL AND ISC_MEASURE_2 <> 0)
263 	   OR (ISC_MEASURE_3 IS NOT NULL AND ISC_MEASURE_3 <> 0)
264 	   OR (ISC_MEASURE_5 IS NOT NULL AND ISC_MEASURE_5 <> 0)
265 	   OR (ISC_MEASURE_6 IS NOT NULL AND ISC_MEASURE_6 <> 0)';
266 
267   l_outer_sql:= 'ISC_MEASURE_1, ISC_MEASURE_2, ISC_MEASURE_3, ISC_MEASURE_4, ISC_MEASURE_5, ISC_MEASURE_6,
268 	ISC_MEASURE_7, ISC_MEASURE_8, ISC_MEASURE_9, ISC_MEASURE_10, ISC_MEASURE_11, ISC_MEASURE_12,
269 	ISC_MEASURE_13, ISC_MEASURE_14, ISC_MEASURE_15, ISC_MEASURE_16, ISC_MEASURE_17, ISC_MEASURE_18,
270 	ISC_MEASURE_19, ISC_MEASURE_20, ISC_MEASURE_21, ISC_MEASURE_22, ISC_MEASURE_23, ISC_MEASURE_24';
271 
272   l_formula_sql := '
273 	c.qty					ISC_MEASURE_1, -- Shipment Quantity
274 	c.rev					ISC_MEASURE_2, -- Revenue
275 	c.comp_rev				ISC_MEASURE_3, -- Compare Plan (Revenue)
276 	(c.rev - c.comp_rev)			ISC_MEASURE_4, -- Variance (Revenue)
277 	c.cost					ISC_MEASURE_5, -- Cost
278 	c.comp_cost				ISC_MEASURE_6, -- Compare Plan (Cost)
279 	(c.cost - c.comp_cost)			ISC_MEASURE_7, -- Variance (Cost)
280 	(c.rev - c.cost)			ISC_MEASURE_8, -- Margin
281 	(c.comp_rev - c.comp_cost)		ISC_MEASURE_9, -- Compare Plan (Margin)
282 	(c.rev - c.cost)
283 	  - (c.comp_rev - c.comp_cost)		ISC_MEASURE_10, -- Variance (Margin)
284 	(c.rev - c.cost)
285 	 / decode(c.rev,0,NULL,c.rev) * 100	ISC_MEASURE_11, -- Margin Percent
286 	(c.comp_rev - c.comp_cost) / decode(c.comp_rev,0,NULL,c.comp_rev) * 100
287 						ISC_MEASURE_12, -- Compare Plan (Margin Percent)
288 	((c.rev - c.cost) / decode(c.rev,0,NULL,c.rev) * 100)
289 	  - ((c.comp_rev - c.comp_cost) / decode(c.comp_rev,0,NULL,c.comp_rev) * 100)
290 						ISC_MEASURE_13, -- Variance (Margin Percent)
291 	sum(c.rev) over ()			ISC_MEASURE_14, -- Grand Total - Revenue
292 	sum((c.rev - c.comp_rev)) over ()	ISC_MEASURE_15, -- Grand Total - Variance (Revenue)
293 	sum(c.cost) over ()			ISC_MEASURE_16, -- Grand Total - Cost
294 	sum(c.cost - c.comp_cost) over ()	ISC_MEASURE_17, -- Grand Total - Variance (Cost)
295 	sum((c.rev - c.cost)) over ()		ISC_MEASURE_18, -- Grand Total - Margin
296 	sum((c.rev - c.cost) - (c.comp_rev - c.comp_cost)) over ()
297 						ISC_MEASURE_19, -- Grand Total - Variance (Margin)
298 	sum(c.rev - c.cost) over ()
299 	 / decode(sum(c.rev) over (),0,NULL,sum(c.rev) over ()) * 100
300 						ISC_MEASURE_20, -- Grand Total - Margin Percent
301 	(sum(c.rev - c.cost) over ()
302 	   / decode(sum(c.rev) over (),0,NULL,sum(c.rev) over ()) * 100)
303 	 - (sum(c.comp_rev - c.comp_cost) over ()
304 	     / decode(sum(c.comp_rev) over (),0,NULL,sum(c.comp_rev) over ()) * 100)
305 						ISC_MEASURE_21, -- Grand Total - Variance (Margin Percent)
306 	sum(c.comp_rev)	over ()			ISC_MEASURE_22, -- Grand Total - Compare Revenue
307 	sum(c.comp_rev - c.comp_cost) over ()	ISC_MEASURE_23, -- Grand Total - Compare Margin
308 	sum(c.comp_rev - c.comp_cost) over ()
309 	 / decode(sum(c.comp_rev) over (),0,NULL,sum(c.comp_rev) over ()) * 100
310 						ISC_MEASURE_24 -- Grand Total - Compare Margin Percent';
311 
312   l_inner_sql := l_qty_select||'
313 		sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
314 			   f.mds_price'||l_curr_suffix||', 0))		REV,
315 		decode(&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2, -1, null,
316 			sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
317 				   f.mds_price'||l_curr_suffix||', 0)))	COMP_REV,
318 		sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
319 			   f.mds_cost'||l_curr_suffix||', 0))		COST,
320 		decode(&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2, -1, null,
321 			sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
322 				   f.mds_cost'||l_curr_suffix||', 0)))	COMP_COST
323 	   FROM	'||l_mv||'		f'
324 		||l_prod_cat_from||'
325 	  WHERE	f.start_date = :ISC_TIME_FROM
326 	    AND	f.period_type_id =:ISC_PERIOD_TYPE_ID
327 	    AND f.snapshot_id IN (&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2)'
328 		||l_flags_where
329 		||l_org_where
330 		||l_prod_cat_where
331 		||l_prod_where;
332 
333   IF l_view_by = 'ITEM+ENI_ITEM_ORG'
334     THEN l_stmt := '
335 SELECT	items.value		VIEWBY,
336 	items.id		VIEWBYID,
337 	null		 	ISC_ATTRIBUTE_1, -- drill across URL
338 	items.description	ISC_ATTRIBUTE_2, -- Description
339 	mtl.unit_of_measure	ISC_ATTRIBUTE_3, -- UOM
340 	'||l_outer_sql||'
341   FROM	(SELECT	(rank() over (&ORDER_BY_CLAUSE NULLS LAST,item_id))-1 RNK,
342 	item_id, uom,
343 	'||l_outer_sql||'
344     FROM (SELECT c.item_id, c.uom,'
345 	 ||l_formula_sql||'
346      FROM (SELECT f.item_id				ITEM_ID,
347 		  f.uom_code				UOM,'
348 	   ||l_inner_sql||'
349 	      GROUP BY f.item_id, f.uom_code) c)'
350 		||l_row_filter||'
351 	   OR (ISC_MEASURE_1 IS NOT NULL AND ISC_MEASURE_1 <> 0)
352 	)			a,
353 	ENI_ITEM_ORG_V		items,
354 	MTL_UNITS_OF_MEASURE_TL	mtl
355   WHERE a.item_id = items.id
356     AND a.uom = mtl.uom_code
357     AND mtl.language = :ISC_LANG
358     AND	((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
359   ORDER BY rnk';
360 
361   ELSIF l_view_by = 'ORGANIZATION+ORGANIZATION'
362     THEN l_stmt := '
363 SELECT	org.name 		VIEWBY,
364 	org.organization_id	VIEWBYID,
365 	null			ISC_ATTRIBUTE_1, -- drill across URL
366 	null			ISC_ATTRIBUTE_2, -- Description
367 	null			ISC_ATTRIBUTE_3, -- UOM
368 	'||l_outer_sql||'
369   FROM	(SELECT	(rank() over (&ORDER_BY_CLAUSE NULLS LAST,organization_id))-1 RNK,
370 	organization_id,
371 	'||l_outer_sql||'
372     FROM (SELECT c.organization_id,'
373 	  ||l_formula_sql||'
374    	FROM (SELECT f.organization_id			ORGANIZATION_ID,'
375 	      ||l_inner_sql||'
376 	      GROUP BY f.organization_id) c)'
377 		||l_row_filter||'
378 	)				a,
379 	HR_ALL_ORGANIZATION_UNITS_TL	org
380   WHERE org.organization_id = a.organization_id
381     AND org.language = :ISC_LANG
382     AND	((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
383   ORDER BY rnk';
384 
385   ELSE -- l_view_by = 'ITEM+ENI_ITEM_VBH_CAT'
386     l_stmt := '
387 SELECT	eni.value 		VIEWBY,
388 	eni.id			VIEWBYID,
389 	decode(eni.leaf_node_flag, ''Y'',
390 		''pFunctionName=ISC_DBI_PLAN_RM&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_ORG&pParamIds=Y'',
391 		''pFunctionName=ISC_DBI_PLAN_RM&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'')
392 				ISC_ATTRIBUTE_1, -- drill across URL
393 	null			ISC_ATTRIBUTE_2, -- Description
394 	null			ISC_ATTRIBUTE_3, -- UOM
395 	'||l_outer_sql||'
396   FROM	(SELECT	(rank() over (&ORDER_BY_CLAUSE NULLS LAST,vbh_category_id))-1 RNK,
397 	vbh_category_id,
398 	'||l_outer_sql||'
399     FROM (SELECT c.vbh_category_id,'
400 	  ||l_formula_sql||'
401    	FROM ('||l_inner_select_stmt
402 	       ||l_inner_sql
403 	       ||l_inner_group_by_stmt||') c)'
404 		||l_row_filter||'
405 	)			a,
406 	ENI_ITEM_VBH_NODES_V	eni
407   WHERE a.vbh_category_id = eni.id
408     AND	eni.parent_id = eni.child_id
409     AND	((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
410   ORDER BY rnk';
411 
412   END IF;
413   END IF;
414 
415   x_custom_sql := l_stmt;
416 
417   l_custom_rec.attribute_name := ':ISC_LANG';
418   l_custom_rec.attribute_value := l_lang;
419   l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
420   l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
421   x_custom_output.extend;
422   x_custom_output(1) := l_custom_rec;
423 
424   l_custom_rec.attribute_name := ':ISC_PERIOD_TYPE_ID';
425   l_custom_rec.attribute_value := to_char(l_period_type_id);
426   l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
427   l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
428   x_custom_output.extend;
429   x_custom_output(2) := l_custom_rec;
430 
431   l_custom_rec.attribute_name := ':ISC_TIME_FROM';
432   l_custom_rec.attribute_value := to_char(l_time_from,'DD/MM/YYYY');
433   l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
434   l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.date_bind;
435   x_custom_output.extend;
436   x_custom_output(3) := l_custom_rec;
437 
438   l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
439   l_custom_rec.attribute_value := to_char(l_item_cat_flag);
440   l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.Bind_Type;
441   l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.Integer_Bind;
442   x_custom_output.extend;
443   x_custom_output(4) := l_custom_rec;
444 
445   l_custom_rec.attribute_name := ':ISC_UNION1_FLAG';
446   l_custom_rec.attribute_value := to_char(l_union1_flag);
447   l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.Bind_Type;
448   l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.Integer_Bind;
449   x_custom_output.extend;
450   x_custom_output(5) := l_custom_rec;
451 
452 END Get_Sql;
453 
454 END ISC_DBI_PLAN_RM_PKG ;
455