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_PERF_PKG

Source


1 PACKAGE BODY ISC_DBI_PLAN_PERF_PKG AS
2 /* $Header: ISCRGAPB.pls 120.1 2006/06/01 06:37:09 achandak noship $ */
3 
4 
5 PROCEDURE GET_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,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_plan		VARCHAR2(10000);
10   l_plan2		VARCHAR2(10000);
11   l_inner_sql	 	VARCHAR2(32000);
12   l_org 		VARCHAR2(300);
13   l_org_where     	VARCHAR2(32000);
14   l_period_type		VARCHAR2(1000);
15   l_cur_start		DATE;
16   l_cur_end		DATE;
17   l_pre_start		DATE;
18   l_pre_end		DATE;
19   l_time_from		DATE;
20   l_time_to		DATE;
21   l_mon_period_id	NUMBER:=32; --only select month buckets
22   l_period_type_id	NUMBER;
23   l_lang		varchar2(10);
24   l_item_cat_flag	NUMBER:=3; -- no grouping on item dimension
25   l_res_gp_flag		NUMBER :=3; -- no grouping on resource dimension
26   l_union_flag		NUMBER:=0; -- for inventory turns report
27   l_mon_num		NUMBER; -- number of months in the selected period
28   l_custom_rec 		BIS_QUERY_ATTRIBUTES ;
29 
30 
31 
32 BEGIN
33 
34   FOR i IN 1..p_param.COUNT
35   LOOP
36     IF (p_param(i).parameter_name = 'PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT')
37       THEN l_plan := p_param(i).parameter_value;
38     END IF;
39 
40     IF (p_param(i).parameter_name = 'PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2')
41       THEN l_plan2 := p_param(i).parameter_value;
42     END IF;
43 
44    IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
45       THEN l_org :=  p_param(i).parameter_value;
46     END IF;
47 
48     IF(p_param(i).parameter_name = 'PERIOD_TYPE') THEN
49        l_period_type :=  p_param(i).parameter_value;
50     END IF;
51 
52     IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM') THEN
53        l_time_from :=  p_param(i).period_date;
54     END IF;
55 
56     IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_TO') THEN
57        l_time_to :=  p_param(i).period_date;
58     END IF;
59 
60     IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM') THEN
61        l_time_from :=  p_param(i).period_date;
62     END IF;
63 
64     IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_TO') THEN
65        l_time_to :=  p_param(i).period_date;
66     END IF;
67 
68     IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM') THEN
69        l_time_from :=  p_param(i).period_date;
70     END IF;
71 
72     IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_TO') THEN
73        l_time_to :=  p_param(i).period_date;
74     END IF;
75 
76 
77   END LOOP;
78 
79 
80     IF ( l_org IS NULL OR l_org = 'All' ) THEN
81     l_org_where := '
82 	WHERE (EXISTS
83 		(SELECT 1
84 		FROM org_access o
85 		WHERE o.responsibility_id = fnd_global.resp_id
86 		AND o.resp_application_id = fnd_global.resp_appl_id
87 		AND o.organization_id = c.viewby_id)
88 		OR EXISTS
89 		(SELECT 1
90 		FROM mtl_parameters org
91 		WHERE org.organization_id = c.viewby_id
92 		AND NOT EXISTS
93 			(SELECT 1
94 			FROM org_access ora
95 			WHERE org.organization_id = ora.organization_id)))';
96 
97   ELSE
98     l_org_where := '
99 		WHERE c.viewby_id = (&ORGANIZATION+ORGANIZATION)';
100   END IF;
101 
102 
103   IF l_period_type = 'FII_TIME_ENT_YEAR' THEN
104    l_period_type_id := 128;
105    l_mon_num :=12;
106 
107   ELSIF (l_period_type='FII_TIME_ENT_QTR') THEN
108    l_period_type_id := 64;
109    l_mon_num :=3;
110 
111   ELSE
112    l_period_type_id := 32;
113    l_mon_num :=1;
114 
115   END IF;
116 
117 
118   l_cur_start := l_time_from;
119   l_cur_end := l_time_to;
120   l_pre_start := FII_TIME_API.ent_pper_start(l_time_from); -- get the previous month
121   l_pre_end := FII_TIME_API.ent_pper_end(l_time_to); -- get the previous month
122 
123   l_lang := USERENV('LANG');
124 
125   l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
126   x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
127 
128 
129   IF (l_plan IS NULL OR l_plan2 IS NULL)
130     THEN l_stmt := '
131 SELECT	0	VIEWBY,
132 	0	VIEWBYID,
133 	0 	ISC_MEASURE_1,
134 	0 	ISC_MEASURE_2,
135 	0 	ISC_MEASURE_3,
136 	0 	ISC_MEASURE_4,
137 	0 	ISC_MEASURE_5,
138 	0 	ISC_MEASURE_6,
139 	0 	ISC_MEASURE_7,
140 	0 	ISC_MEASURE_8,
141 	0 	ISC_MEASURE_9,
142 	0 	ISC_MEASURE_10,
143 	0 	ISC_MEASURE_11,
144 	0 	ISC_MEASURE_12,
145 	0 	ISC_MEASURE_13,
146 	0 	ISC_MEASURE_14,
147 	0 	ISC_MEASURE_15,
148 	0 	ISC_MEASURE_16,
149 	0 	ISC_MEASURE_17,
150 	0 	ISC_MEASURE_18
151   FROM	dual
152  WHERE	1 = 2 /* PLAN_SNAPSHOT dimension has not been populated */';
153     ELSE
154 
155     l_inner_sql := 'ISC_MEASURE_1,ISC_MEASURE_2,ISC_MEASURE_3, ISC_MEASURE_4,ISC_MEASURE_5,
156 		ISC_MEASURE_4-ISC_MEASURE_5 ISC_MEASURE_6,ISC_MEASURE_7,ISC_MEASURE_8,
157 		ISC_MEASURE_9,ISC_MEASURE_10,ISC_MEASURE_11,ISC_MEASURE_10-ISC_MEASURE_11 ISC_MEASURE_12,
158 		ISC_MEASURE_13,ISC_MEASURE_14,ISC_MEASURE_15,
159 		ISC_MEASURE_16,ISC_MEASURE_17,ISC_MEASURE_16-ISC_MEASURE_17 ISC_MEASURE_18
160 	FROM(select (rank() over (&ORDER_BY_CLAUSE nulls last,viewby_id))-1 rnk,
161 		viewby_id,
162 		ISC_MEASURE_1,ISC_MEASURE_2,ISC_MEASURE_3,
163 		sum(plan_mds) over ()*365/(:ISC_CUR_END - :ISC_CUR_START+1)/
164 		decode(sign(sum(plan_begin_inv) over()+sum(plan_end_inv) over()),0,null,-1,null,
165 		(sum(plan_begin_inv) over()+sum(plan_end_inv) over())/2/:ISC_MON_NUM) ISC_MEASURE_4,
166 		sum(comp_mds) over ()*365/(:ISC_CUR_END - :ISC_CUR_START+1)/
167 		decode(sign(sum(comp_begin_inv) over()+sum(comp_end_inv) over()),0,null,-1,null,
168 		(sum(comp_begin_inv) over()+sum(comp_end_inv) over())/2/:ISC_MON_NUM) ISC_MEASURE_5,
169 		ISC_MEASURE_7,ISC_MEASURE_8,ISC_MEASURE_9,
170 		sum(plan_ontime_total) over ()/decode(sum(plan_total_lines) over(),0,null,
171 		sum(plan_total_lines) over())*100	ISC_MEASURE_10,
172 		sum(comp_ontime_total) over ()/decode(sum(comp_total_lines) over(),0,null,
173 		sum(comp_total_lines) over())*100	ISC_MEASURE_11,
174 		ISC_MEASURE_13,ISC_MEASURE_14,ISC_MEASURE_15,
175 		sum(plan_required_hr_total) over()/decode(sum(plan_available_hr_total) over(),0,null,
176 			sum(plan_available_hr_total)over())*100	ISC_MEASURE_16,
177 		sum(comp_required_hr_total) over()/decode(sum(comp_available_hr_total) over(),0,null,
178 			sum(comp_available_hr_total)over())*100	ISC_MEASURE_17
179 		FROM (select c.viewby_id,
180 		sum(c.plan_inv_turns)					ISC_MEASURE_1,
181 		sum(c.comp_inv_turns)					ISC_MEASURE_2,
182 		sum(c.plan_inv_turns)-sum(comp_inv_turns)		ISC_MEASURE_3,
183 		sum(c.plan_mds_total)					plan_mds,
184 		sum(c.plan_begin_inv_total)				plan_begin_inv,
185 		sum(c.plan_end_inv_total)				plan_end_inv,
186 		sum(c.comp_mds_total)					comp_mds,
187 		sum(c.comp_begin_inv_total)				comp_begin_inv,
188 		sum(c.comp_end_inv_total)				comp_end_inv,
189 		sum(c.plan_ontime_lines)/decode(sum(c.plan_total_lines),0,null,
190 			sum(c.plan_total_lines))*100			ISC_MEASURE_7,
191 		sum(c.comp_ontime_lines)/decode(sum(c.comp_total_lines),0,null,
192 			sum(c.comp_total_lines))*100			ISC_MEASURE_8,
193 		(sum(c.plan_ontime_lines)/decode(sum(c.plan_total_lines),0,null,
194 			sum(c.plan_total_lines)))*100-
195 		(sum(c.comp_ontime_lines)/decode(sum(c.comp_total_lines),0,null,
196 			sum(c.comp_total_lines)))*100			ISC_MEASURE_9,
197 		sum(c.plan_ontime_lines) 				plan_ontime_total,
198 		sum(c.plan_total_lines)					plan_total_lines,
199 		sum(c.comp_ontime_lines)				comp_ontime_total,
200 		sum(c.comp_total_lines)					comp_total_lines,
201 		sum(c.plan_required_hr)/decode(sum(c.plan_available_hr),0,null,
202 			sum(c.plan_available_hr))*100			ISC_MEASURE_13,
203 		sum(c.comp_required_hr)/decode(sum(c.comp_available_hr),0,null,
204 			sum(c.comp_available_hr))*100			ISC_MEASURE_14,
205 		(sum(c.plan_required_hr)/decode(sum(c.plan_available_hr),0,null,
206 			sum(c.plan_available_hr)))*100-
207 		(sum(c.comp_required_hr)/decode(sum(c.comp_available_hr),0,null,
208 			sum(c.comp_available_hr)))*100			ISC_MEASURE_15,
209 		sum(c.plan_required_hr) 				plan_required_hr_total,
210 		sum(c.plan_available_hr) 				plan_available_hr_total,
211 		sum(c.comp_required_hr)					comp_required_hr_total,
212 		sum(c.comp_available_hr) 				comp_available_hr_total
213 		FROM (
214 		SELECT s.viewby_id,
215 		decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
216 		sum(s.mds)*365/(:ISC_CUR_END - :ISC_CUR_START+1)/
217 		decode(sign(sum(s.begin_inv+s.end_inv)),0,null,-1,null,
218 		sum(s.begin_inv+s.end_inv)/2/:ISC_MON_NUM),null) 	plan_inv_turns,
219 		decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
220 		sum(s.mds)*365/(:ISC_CUR_END - :ISC_CUR_START+1)/
221 		decode(sign(sum(s.begin_inv+s.end_inv)),0,null,-1,null,
222 		sum(s.begin_inv+s.end_inv)/2/:ISC_MON_NUM),null) 	comp_inv_turns,
223 		decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
224 		sum(s.mds),null)					plan_mds_total,
225 		decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
226 		sum(s.begin_inv),null)					plan_begin_inv_total,
227 		decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
228 		sum(s.end_inv),null)					plan_end_inv_total,
229 		decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
230 		sum(s.mds),null)					comp_mds_total,
231 		decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
232 		sum(s.begin_inv),null)					comp_begin_inv_total,
233 		decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
234 		sum(s.end_inv),null)					comp_end_inv_total,
235 		null		plan_ontime_lines,
236 		null		plan_total_lines,
237 		null		comp_ontime_lines,
238 		null		comp_total_lines,
239 		null		plan_required_hr,
243 		FROM
240 		null		plan_available_hr,
241 		null		comp_required_hr,
242 		null		comp_available_hr
244 		(SELECT f.organization_id	VIEWBY_ID,
245 		dates.start_date		PERIOD,
246 		f.snapshot_id			PLAN_ID,
247 		sum(decode(dates.period_type,''P'',f.inventory_cost_g,0))	begin_inv,
248 		sum(decode(dates.period_type,''C'',f.inventory_cost_g,0))	end_inv,
249 		sum(decode(dates.period_type,''C'',f.mds_cost_g,0))	mds
250 		FROM
251 		(SELECT fii.start_date	START_DATE,
252 			fii.start_date  REPORT_DATE,
253 			''C''		PERIOD_TYPE
254 		 FROM FII_TIME_ENT_PERIOD fii
255 		 WHERE fii.start_date between :ISC_CUR_START and :ISC_CUR_END
256 		UNION ALL
257 		SELECT 	cur.start_date	start_date,
258 			pre.start_date  report_date,
259 			''P''		period_type
260 		FROM
261 		(SELECT fii.start_date	START_DATE,
262 		 rownum			ID
263 		 FROM FII_TIME_ENT_PERIOD fii
264 		 WHERE fii.start_date between :ISC_PRE_START and :ISC_PRE_END
265 		 ORDER by fii.start_date DESC)		pre,
266 		(SELECT fii.start_date	START_DATE,
267 		 rownum			ID
268 		 FROM FII_TIME_ENT_PERIOD fii
269 		 WHERE fii.start_date between :ISC_CUR_START and :ISC_CUR_END
270 		 ORDER by fii.start_date DESC)		cur
271 		WHERE cur.id = pre.id(+))	dates,
272 		ISC_DBI_PM_0001_MV f
273 		WHERE f.start_date = dates.report_date
274 		AND f.period_type_id = :ISC_MON_PERIOD_ID
275 		AND f.union1_flag <>:ISC_UNION_FLAG
276 		AND f.item_cat_flag = :ISC_ITEM_CAT_FLAG
277 		AND f.snapshot_id in (&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2)
278 		GROUP BY f.organization_id,dates.start_date,f.snapshot_id) s
279 		GROUP BY s.viewby_id,s.plan_id
280 		UNION ALL
281 		SELECT f.organization_id	VIEWBY_ID,
282 		null	plan_inv_turns,
283 		null	comp_inv_turns,
284 		null	plan_mds_total,
285 		null	plan_begin_inv_total,
286 		null	plan_end_inv_total,
287 		null	comp_mds_total,
288 		null	comp_begin_inv_total,
289 		null	comp_end_inv_total,
290 		decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
291 		sum(f.total_lines)-sum(f.late_lines),null)		plan_ontime_lines,
292 		decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
293 		sum(f.total_lines),null)				plan_total_lines,
294 		decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
295 		sum(f.total_lines)-sum(f.late_lines),null)		comp_ontime_lines,
296 		decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
297 		sum(f.total_lines),null)				comp_total_lines,
298 		null		plan_required_hr,
299 		null		plan_available_hr,
300 		null		comp_required_hr,
301 		null		comp_available_hr
302 		FROM
303 		ISC_DBI_PM_0001_MV f
304 		WHERE f.start_date = :ISC_CUR_START
305 		AND f.period_type_id = :ISC_PERIOD_TYPE_ID
306 		AND f.union2_flag <>:ISC_UNION_FLAG
307 		AND f.item_cat_flag = :ISC_ITEM_CAT_FLAG
308 		AND f.snapshot_id in (&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2)
309 		GROUP BY f.organization_id,f.snapshot_id
310 		UNION ALL
311 		SELECT f.organization_id	VIEWBY_ID,
312 		null	plan_inv_turns,
313 		null	comp_inv_turns,
314 		null	plan_mds_total,
315 		null	plan_begin_inv_total,
316 		null	plan_end_inv_total,
317 		null	comp_mds_total,
318 		null	comp_begin_inv_total,
319 		null	comp_end_inv_total,
320 		null	plan_ontime_lines,
321 		null	plan_total_lines,
322 		null	comp_ontime_lines,
323 		null	comp_total_lines,
324 		decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
325 		sum(f.required_hours),null)	plan_required_hr,
326 		decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
327 		sum(f.available_hours),null)	plan_available_hr,
328 		decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
329 		sum(f.required_hours),null)	comp_required_hr,
333 		ISC_DBI_PM_0002_MV f
330 		decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
331 		sum(f.available_hours),null)	comp_available_hr
332 		FROM
334 		WHERE f.start_date = :ISC_CUR_START
335 		AND f.period_type_id = :ISC_PERIOD_TYPE_ID
336 		AND f.res_gp_flag =:ISC_RES_GP_FLAG
337 		AND f.snapshot_id in (&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2)
338 		GROUP BY f.organization_id,f.snapshot_id) c
339 		'||l_org_where||'
340 		GROUP BY c.viewby_id)
341 		WHERE (ISC_MEASURE_1 is not null OR ISC_MEASURE_2 is not null
342 			OR ISC_MEASURE_7 is not null OR ISC_MEASURE_7 is not null
343 			OR ISC_MEASURE_13 is not null OR ISC_MEASURE_14 is not null)) a,';
344 
345   l_stmt := 'SELECT org.name				VIEWBY,
346 		org.organization_id			VIEWBYID,
347 		'||l_inner_sql||'
348 		HR_ALL_ORGANIZATION_UNITS_TL org
349 		WHERE org.organization_id = a.viewby_id
350 		AND org.language = :ISC_LANG
351 		AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX=-1))
352 		ORDER BY rnk';
353 
354   END IF;
355 
356   x_custom_sql := l_stmt;
357 
358   l_custom_rec.attribute_name := ':ISC_PERIOD_TYPE_ID';
359   l_custom_rec.attribute_value := to_char(l_period_type_id);
360   l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
361   l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
362   x_custom_output.EXTEND;
363   x_custom_output(1) := l_custom_rec;
364 
365   l_custom_rec.attribute_name := ':ISC_CUR_START';
366   l_custom_rec.attribute_value := to_char(l_cur_start,'DD/MM/YYYY');
367   l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
368   l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
369   x_custom_output.EXTEND;
370   x_custom_output(2) := l_custom_rec;
371 
372   l_custom_rec.attribute_name := ':ISC_CUR_END';
373   l_custom_rec.attribute_value := to_char(l_cur_end,'DD/MM/YYYY');
374   l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
375   l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
376   x_custom_output.EXTEND;
377   x_custom_output(3) := l_custom_rec;
378 
379   l_custom_rec.attribute_name := ':ISC_PRE_START';
380   l_custom_rec.attribute_value := to_char(l_pre_start,'DD/MM/YYYY');
381   l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
382   l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
383   x_custom_output.EXTEND;
384   x_custom_output(4) := l_custom_rec;
385 
386   l_custom_rec.attribute_name := ':ISC_PRE_END';
387   l_custom_rec.attribute_value := to_char(l_pre_end,'DD/MM/YYYY');
388   l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
389   l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
390   x_custom_output.EXTEND;
391   x_custom_output(5) := l_custom_rec;
392 
393 
394   l_custom_rec.attribute_name := ':ISC_LANG';
395   l_custom_rec.attribute_value := l_lang;
396   l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
397   l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
398   x_custom_output.EXTEND;
399   x_custom_output(6) := l_custom_rec;
400 
401   l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
402   l_custom_rec.attribute_value := to_char(l_item_cat_flag);
403   l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
404   l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
405   x_custom_output.EXTEND;
406   x_custom_output(7) := l_custom_rec;
407 
408   l_custom_rec.attribute_name := ':ISC_UNION_FLAG';
409   l_custom_rec.attribute_value := to_char(l_union_flag);
410   l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
411   l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
412   x_custom_output.EXTEND;
413   x_custom_output(8) := l_custom_rec;
414 
415   l_custom_rec.attribute_name := ':ISC_RES_GP_FLAG';
416   l_custom_rec.attribute_value := to_char(l_res_gp_flag);
417   l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
418   l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
419   x_custom_output.EXTEND;
420   x_custom_output(9) := l_custom_rec;
421 
422   l_custom_rec.attribute_name := ':ISC_MON_PERIOD_ID';
423   l_custom_rec.attribute_value := to_char(l_mon_period_id);
424   l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
425   l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
426   x_custom_output.EXTEND;
427   x_custom_output(10) := l_custom_rec;
428 
429   l_custom_rec.attribute_name := ':ISC_MON_NUM';
430   l_custom_rec.attribute_value := to_char(l_mon_num);
431   l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
432   l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
433   x_custom_output.EXTEND;
434   x_custom_output(11) := l_custom_rec;
435 
436 END get_sql;
437 
438 END ISC_DBI_PLAN_PERF_PKG ;
439