[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_PDUE_SCH_AGING_PKG
Source
1 PACKAGE BODY ISC_DBI_PDUE_SCH_AGING_PKG AS
2 /* $Header: ISCRGA6B.pls 120.0 2005/05/25 17:13:57 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_period_type VARCHAR2(10000);
11 l_mv1 VARCHAR2(100);
12 l_flags_where VARCHAR2(10000);
13 l_inv_org VARCHAR2(10000);
14 l_inv_org_where VARCHAR2(10000);
15 l_prod VARCHAR2(10000);
16 l_prod_where VARCHAR2(10000);
17 l_prod_cat VARCHAR2(10000);
18 l_prod_cat_from VARCHAR2(10000);
19 l_prod_cat_where VARCHAR2(10000);
20 l_cust VARCHAR2(10000);
21 l_cust_where VARCHAR2(10000);
22 l_curr VARCHAR2(10000);
23 l_curr_g VARCHAR2(15);
24 l_curr_g1 VARCHAR2(15);
25 l_curr_suffix VARCHAR2(120);
26 l_item_cat_flag NUMBER;
27 l_cust_flag NUMBER;
28 l_snapshot_taken BOOLEAN := TRUE;
29 l_as_of_date DATE;
30 l_effective_start_date DATE;
31 l_cursor_id NUMBER;
32 l_dummy NUMBER;
33 l_bucket_rec bis_bucket_pub.BIS_BUCKET_REC_TYPE;
34 l_error_tbl bis_utilities_pub.ERROR_TBL_TYPE;
35 l_status VARCHAR2(10000);
36 l_custom_rec BIS_QUERY_ATTRIBUTES;
37
38 BEGIN
39
40 l_curr_g := '''FII_GLOBAL1''';
41 l_curr_g1 := '''FII_GLOBAL2''';
42
43 FOR i IN 1..p_param.COUNT
44 LOOP
45 IF (p_param(i).parameter_name = 'AS_OF_DATE')
46 THEN l_as_of_date := to_date(p_param(i).parameter_value, 'DD-MM-YYYY');
47 END IF;
48
49 IF (p_param(i).parameter_name = 'PERIOD_TYPE')
50 THEN l_period_type := p_param(i).parameter_value;
51 END IF;
52
53 IF (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
54 THEN l_curr := p_param(i).parameter_id;
55 END IF;
56
57 IF (p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
58 THEN l_inv_org := p_param(i).parameter_value;
59 END IF;
60
61 IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT')
62 THEN l_prod_cat := p_param(i).parameter_value;
63 END IF;
64
65 IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG')
66 THEN l_prod := p_param(i).parameter_value;
67 END IF;
68
69 IF (p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS')
70 THEN l_cust := p_param(i).parameter_value;
71 END IF;
72 END LOOP;
73
74 IF (l_curr = l_curr_g)
75 THEN l_curr_suffix := 'g';
76 ELSIF (l_curr = l_curr_g1)
77 THEN l_curr_suffix :='g1';
78 ELSE l_curr_suffix := 'f';
79 END IF;
80
81 IF (l_inv_org IS NULL OR l_inv_org = '' OR l_inv_org = 'All')
82 THEN l_inv_org_where := '
83 AND (EXISTS
84 (SELECT 1
85 FROM org_access o
86 WHERE o.responsibility_id = fnd_global.resp_id
87 AND o.resp_application_id = fnd_global.resp_appl_id
88 AND o.organization_id = fact.inv_org_id)
89 OR EXISTS
90 (SELECT 1
91 FROM mtl_parameters org
92 WHERE org.organization_id = fact.inv_org_id
93 AND NOT EXISTS
94 (SELECT 1
95 FROM org_access ora
96 WHERE org.organization_id = ora.organization_id)))';
97 ELSE l_inv_org_where := '
98 AND fact.inv_org_id = &ORGANIZATION+ORGANIZATION';
99 END IF;
100
101 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
102 THEN
103 l_prod_cat_from := '';
104 l_prod_cat_where := '';
105 ELSE
106 l_prod_cat_from := ',
107 ENI_DENORM_HIERARCHIES eni_cat,
108 MTL_DEFAULT_CATEGORY_SETS mdcs';
109 l_prod_cat_where := '
110 AND fact.item_category_id = eni_cat.child_id
111 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
112 AND eni_cat.dbi_flag = ''Y''
113 AND eni_cat.object_type = ''CATEGORY_SET''
114 AND eni_cat.object_id = mdcs.category_set_id
115 AND mdcs.functional_area_id = 11';
116 END IF;
117
118 IF (l_prod IS NULL OR l_prod = '' OR l_prod = 'All')
119 THEN l_prod_where := '';
120 ELSE l_prod_where := '
121 AND fact.item_id IN (&ITEM+ENI_ITEM_ORG)';
122 END IF;
123
124 IF (l_cust IS NULL OR l_cust = '' OR l_cust = 'All')
125 THEN
126 l_cust_where := '';
127 l_cust_flag := 1;
128 ELSE
129 l_cust_where := '
130 AND fact.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
131 l_cust_flag := 0;
132 END IF;
133
134 IF (l_prod IS NULL OR l_prod = '' OR l_prod = 'All')
135 THEN
136 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
137 THEN l_item_cat_flag := 3; -- category
138 ELSE l_item_cat_flag := 1; -- all
139 END IF;
140 ELSE
141 l_item_cat_flag := 0; -- product
142 END IF;
143
144 BEGIN
145
146 IF l_period_type = 'FII_TIME_ENT_YEAR'
147 THEN l_effective_start_date := FII_TIME_API.Ent_Cyr_Start(l_as_of_date);
148 ELSIF l_period_type = 'FII_TIME_ENT_QTR'
149 THEN l_effective_start_date := FII_TIME_API.Ent_Cqtr_Start(l_as_of_date);
150 ELSIF l_period_type = 'FII_TIME_ENT_PERIOD'
151 THEN l_effective_start_date := FII_TIME_API.Ent_Cper_Start(l_as_of_date);
152 ELSE -- l_period_type = 'FII_TIME_WEEK'
153 l_effective_start_date := FII_TIME_API.Cwk_Start(l_as_of_date);
154 END IF;
155
156 l_cursor_id := DBMS_SQL.Open_Cursor;
157 l_stmt := '
158 SELECT 1
159 FROM ISC_DBI_CFM_008_MV fact
160 WHERE fact.time_snapshot_date_id BETWEEN :l_effective_start_date
161 AND :l_as_of_date
162 AND rownum = 1 ';
163
164 DBMS_SQL.Parse(l_cursor_id,l_stmt,DBMS_SQL.V7);
165 DBMS_SQL.Bind_Variable(l_cursor_id,':l_effective_start_date',l_effective_start_date);
166 DBMS_SQL.Bind_Variable(l_cursor_id,':l_as_of_date',l_as_of_date);
167
168 l_dummy := DBMS_SQL.Execute(l_cursor_id);
169
170 IF DBMS_SQL.Fetch_Rows(l_cursor_id) = 0 -- no snapshot taken
171 THEN l_snapshot_taken := FALSE;
172 ELSE l_snapshot_taken := TRUE;
173 END IF;
174
175 DBMS_SQL.Close_Cursor(l_cursor_id);
176
177 EXCEPTION WHEN OTHERS
178 THEN
179 DBMS_SQL.Close_Cursor(l_cursor_id);
180 l_snapshot_taken := TRUE;
181
182 END;
183
184 -- Retrieve record to get bucket labels
185 bis_bucket_pub.RETRIEVE_BIS_BUCKET('ISC_DBI_PDUE_AGING', l_bucket_rec, l_status, l_error_tbl);
186
187 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
188 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
189
190 IF ((l_prod IS NULL OR l_prod = '' OR l_prod = 'All') AND
191 (l_cust IS NULL OR l_cust = '' OR l_cust = 'All'))
192 THEN
193 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
194 THEN
195 l_prod_cat_from := '';
196 l_prod_cat_where := '
197 AND fact.top_node_flag = ''Y''';
198 ELSE
199 l_prod_cat_from := '';
200 l_prod_cat_where := '
201 AND fact.parent_id = &ITEM+ENI_ITEM_VBH_CAT';
202 END IF;
203 l_mv1 := 'ISC_DBI_CFM_012_MV';
204 l_flags_where := '';
205 ELSE
206 l_mv1 := 'ISC_DBI_CFM_008_MV';
207 l_flags_where := '
208 AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
209 AND fact.customer_flag = :ISC_CUST_FLAG';
210 END IF;
211
212 IF NOT (l_snapshot_taken)
213 THEN l_stmt := '
214 SELECT 0 ISC_ATTRIBUTE_2,
215 0 ISC_ATTRIBUTE_3,
216 0 ISC_MEASURE_1,
217 0 ISC_MEASURE_2,
218 0 ISC_MEASURE_3,
219 0 ISC_MEASURE_4,
220 0 ISC_MEASURE_5,
221 0 ISC_MEASURE_6,
222 0 ISC_MEASURE_7,
223 0 ISC_MEASURE_8,
224 0 ISC_MEASURE_9
225 FROM dual
226 WHERE 1 = 2 -- no snapshot taken in the current period';
227 ELSE
228 l_stmt := '
229 SELECT c.bucket ISC_ATTRIBUTE_2, -- bucket name
230 c.bucket_type ISC_ATTRIBUTE_3, -- bucket type
231 c.curr_line_cnt ISC_MEASURE_1, -- pdue line cnt
232 c.prev_pdue_value ISC_MEASURE_2, -- pdue prior
233 c.curr_pdue_value ISC_MEASURE_3, -- pdue
234 (c.curr_pdue_value - c.prev_pdue_value)
235 / decode(c.prev_pdue_value, 0, NULL,
236 abs(c.prev_pdue_value)) * 100 ISC_MEASURE_4, -- pdue change
237 c.curr_pdue_value
238 / decode(sum(c.curr_pdue_value) over (), 0, NULL,
239 abs(sum(c.curr_pdue_value) over ())) * 100 ISC_MEASURE_5, -- pdue % of total
240 sum(c.curr_line_cnt) over () ISC_MEASURE_6, -- gd total pdue line cnt
241 sum(c.curr_pdue_value) over () ISC_MEASURE_7, -- gd total pdue
242 (sum(c.curr_pdue_value) over () - sum(c.prev_pdue_value) over ())
243 / decode(sum(c.prev_pdue_value) over (), 0, NULL,
244 abs(sum(c.prev_pdue_value) over ())) * 100 ISC_MEASURE_8, -- gd total pdue change
245 sum(c.curr_pdue_value) over ()
246 / decode(sum(c.curr_pdue_value) over (), 0, NULL,
247 abs(sum(c.curr_pdue_value) over ())) * 100 ISC_MEASURE_9 -- gd total pdue % of total
248 FROM
249 (SELECT decode(rownum,
250 1, :ISC_R1,
251 2, :ISC_R2,
252 3, :ISC_R3,
253 4, :ISC_R4,
254 5, :ISC_R5,
255 6, :ISC_R6,
256 7, :ISC_R7,
257 8, :ISC_R8,
258 9, :ISC_R9,
259 10, :ISC_R10,
260 NULL) BUCKET,
261 rownum BUCKET_TYPE,
262 decode(rownum,
263 1, m.curr_line_cnt_1,
264 2, m.curr_line_cnt_2,
265 3, m.curr_line_cnt_3,
266 4, m.curr_line_cnt_4,
267 5, m.curr_line_cnt_5,
268 6, m.curr_line_cnt_6,
269 7, m.curr_line_cnt_7,
270 8, m.curr_line_cnt_8,
271 9, m.curr_line_cnt_9,
272 10, m.curr_line_cnt_10,
273 NULL) CURR_LINE_CNT,
274 decode(rownum,
275 1, m.curr_pdue_value_1,
276 2, m.curr_pdue_value_2,
277 3, m.curr_pdue_value_3,
278 4, m.curr_pdue_value_4,
279 5, m.curr_pdue_value_5,
280 6, m.curr_pdue_value_6,
281 7, m.curr_pdue_value_7,
282 8, m.curr_pdue_value_8,
283 9, m.curr_pdue_value_9,
284 10, m.curr_pdue_value_10,
285 NULL) CURR_PDUE_VALUE,
286 decode(rownum,
287 1, m.prev_pdue_value_1,
288 2, m.prev_pdue_value_2,
289 3, m.prev_pdue_value_3,
290 4, m.prev_pdue_value_4,
291 5, m.prev_pdue_value_5,
292 6, m.prev_pdue_value_6,
293 7, m.prev_pdue_value_7,
294 8, m.prev_pdue_value_8,
295 9, m.prev_pdue_value_9,
296 10, m.prev_pdue_value_10,
297 NULL) PREV_PDUE_VALUE
298 FROM (SELECT sum(decode(fact.time_snapshot_date_id, a.day,
299 fact.bucket1_line_cnt, 0)) CURR_LINE_CNT_1,
300 sum(decode(fact.time_snapshot_date_id, a.day,
301 fact.bucket2_line_cnt, 0)) CURR_LINE_CNT_2,
302 sum(decode(fact.time_snapshot_date_id, a.day,
303 fact.bucket3_line_cnt, 0)) CURR_LINE_CNT_3,
304 sum(decode(fact.time_snapshot_date_id, a.day,
305 fact.bucket4_line_cnt, 0)) CURR_LINE_CNT_4,
306 sum(decode(fact.time_snapshot_date_id, a.day,
307 fact.bucket5_line_cnt, 0)) CURR_LINE_CNT_5,
308 sum(decode(fact.time_snapshot_date_id, a.day,
309 fact.bucket6_line_cnt, 0)) CURR_LINE_CNT_6,
310 sum(decode(fact.time_snapshot_date_id, a.day,
311 fact.bucket7_line_cnt, 0)) CURR_LINE_CNT_7,
312 sum(decode(fact.time_snapshot_date_id, a.day,
313 fact.bucket8_line_cnt, 0)) CURR_LINE_CNT_8,
314 sum(decode(fact.time_snapshot_date_id, a.day,
315 fact.bucket9_line_cnt, 0)) CURR_LINE_CNT_9,
316 sum(decode(fact.time_snapshot_date_id, a.day,
317 fact.bucket10_line_cnt, 0)) CURR_LINE_CNT_10,
318 sum(decode(fact.time_snapshot_date_id, a.day,
319 fact.bucket1_pdue_amt_'||l_curr_suffix||', 0)) CURR_PDUE_VALUE_1,
320 sum(decode(fact.time_snapshot_date_id, a.day,
321 fact.bucket2_pdue_amt_'||l_curr_suffix||', 0)) CURR_PDUE_VALUE_2,
322 sum(decode(fact.time_snapshot_date_id, a.day,
323 fact.bucket3_pdue_amt_'||l_curr_suffix||', 0)) CURR_PDUE_VALUE_3,
324 sum(decode(fact.time_snapshot_date_id, a.day,
325 fact.bucket4_pdue_amt_'||l_curr_suffix||', 0)) CURR_PDUE_VALUE_4,
326 sum(decode(fact.time_snapshot_date_id, a.day,
327 fact.bucket5_pdue_amt_'||l_curr_suffix||', 0)) CURR_PDUE_VALUE_5,
328 sum(decode(fact.time_snapshot_date_id, a.day,
329 fact.bucket6_pdue_amt_'||l_curr_suffix||', 0)) CURR_PDUE_VALUE_6,
330 sum(decode(fact.time_snapshot_date_id, a.day,
331 fact.bucket7_pdue_amt_'||l_curr_suffix||', 0)) CURR_PDUE_VALUE_7,
332 sum(decode(fact.time_snapshot_date_id, a.day,
333 fact.bucket8_pdue_amt_'||l_curr_suffix||', 0)) CURR_PDUE_VALUE_8,
334 sum(decode(fact.time_snapshot_date_id, a.day,
335 fact.bucket9_pdue_amt_'||l_curr_suffix||', 0)) CURR_PDUE_VALUE_9,
336 sum(decode(fact.time_snapshot_date_id, a.day,
337 fact.bucket10_pdue_amt_'||l_curr_suffix||', 0)) CURR_PDUE_VALUE_10,
338 sum(decode(fact.time_snapshot_date_id, b.day,
339 fact.bucket1_pdue_amt_'||l_curr_suffix||', 0)) PREV_PDUE_VALUE_1,
340 sum(decode(fact.time_snapshot_date_id, b.day,
341 fact.bucket2_pdue_amt_'||l_curr_suffix||', 0)) PREV_PDUE_VALUE_2,
342 sum(decode(fact.time_snapshot_date_id, b.day,
343 fact.bucket3_pdue_amt_'||l_curr_suffix||', 0)) PREV_PDUE_VALUE_3,
344 sum(decode(fact.time_snapshot_date_id, b.day,
345 fact.bucket4_pdue_amt_'||l_curr_suffix||', 0)) PREV_PDUE_VALUE_4,
346 sum(decode(fact.time_snapshot_date_id, b.day,
347 fact.bucket5_pdue_amt_'||l_curr_suffix||', 0)) PREV_PDUE_VALUE_5,
348 sum(decode(fact.time_snapshot_date_id, b.day,
349 fact.bucket6_pdue_amt_'||l_curr_suffix||', 0)) PREV_PDUE_VALUE_6,
350 sum(decode(fact.time_snapshot_date_id, b.day,
351 fact.bucket7_pdue_amt_'||l_curr_suffix||', 0)) PREV_PDUE_VALUE_7,
352 sum(decode(fact.time_snapshot_date_id, b.day,
353 fact.bucket8_pdue_amt_'||l_curr_suffix||', 0)) PREV_PDUE_VALUE_8,
354 sum(decode(fact.time_snapshot_date_id, b.day,
355 fact.bucket9_pdue_amt_'||l_curr_suffix||', 0)) PREV_PDUE_VALUE_9,
356 sum(decode(fact.time_snapshot_date_id, b.day,
357 fact.bucket10_pdue_amt_'||l_curr_suffix||', 0)) PREV_PDUE_VALUE_10
358 FROM (SELECT max(time_snapshot_date_id) day
359 FROM '||l_mv1||' fact
360 WHERE fact.time_snapshot_date_id BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
361 AND &BIS_CURRENT_ASOF_DATE
362 ) a,
363 (SELECT max(time_snapshot_date_id) day
364 FROM '||l_mv1||' fact
365 WHERE fact.time_snapshot_date_id BETWEEN &BIS_PREVIOUS_EFFECTIVE_START_DATE
366 AND &BIS_PREVIOUS_ASOF_DATE
367 ) b,
368 '||l_mv1||' fact'||l_prod_cat_from||'
369 WHERE fact.time_snapshot_date_id IN (a.day, b.day)'
370 ||l_flags_where||'
371 AND fact.late_schedule_flag = 1'
372 ||l_inv_org_where
373 ||l_prod_cat_where
374 ||l_prod_where
375 ||l_cust_where
376 ||') m,
377 (SELECT 1 FROM DUAL -- dummy table with 10 rows
378 UNION ALL SELECT 1 FROM DUAL
379 UNION ALL SELECT 1 FROM DUAL
380 UNION ALL SELECT 1 FROM DUAL
381 UNION ALL SELECT 1 FROM DUAL
382 UNION ALL SELECT 1 FROM DUAL
383 UNION ALL SELECT 1 FROM DUAL
384 UNION ALL SELECT 1 FROM DUAL
385 UNION ALL SELECT 1 FROM DUAL
386 UNION ALL SELECT 1 FROM DUAL) ) c
387 WHERE c.bucket IS NOT NULL
388 ORDER BY c.bucket_type';
389 END IF;
390
391 x_custom_sql := l_stmt;
392
393 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
394 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
395 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
396 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
397 x_custom_output.extend;
398 x_custom_output(1) := l_custom_rec;
399
400 l_custom_rec.attribute_name := ':ISC_CUST_FLAG';
401 l_custom_rec.attribute_value := to_char(l_cust_flag);
402 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
403 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
404 x_custom_output.extend;
405 x_custom_output(2) := l_custom_rec;
406
407 l_custom_rec.attribute_name := ':ISC_R1';
408 l_custom_rec.attribute_value := l_bucket_rec.range1_name;
409 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
410 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
411 x_custom_output.extend;
412 x_custom_output(3) := l_custom_rec;
413
414 l_custom_rec.attribute_name := ':ISC_R2';
415 l_custom_rec.attribute_value := l_bucket_rec.range2_name;
416 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
417 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
418 x_custom_output.extend;
419 x_custom_output(4) := l_custom_rec;
420
421 l_custom_rec.attribute_name := ':ISC_R3';
422 l_custom_rec.attribute_value := l_bucket_rec.range3_name;
423 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
424 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
425 x_custom_output.extend;
426 x_custom_output(5) := l_custom_rec;
427
428 l_custom_rec.attribute_name := ':ISC_R4';
429 l_custom_rec.attribute_value := l_bucket_rec.range4_name;
430 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
431 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
432 x_custom_output.extend;
433 x_custom_output(6) := l_custom_rec;
434
435 l_custom_rec.attribute_name := ':ISC_R5';
436 l_custom_rec.attribute_value := l_bucket_rec.range5_name;
437 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
438 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
439 x_custom_output.extend;
440 x_custom_output(7) := l_custom_rec;
441
442 l_custom_rec.attribute_name := ':ISC_R6';
443 l_custom_rec.attribute_value := l_bucket_rec.range6_name;
444 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
445 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
446 x_custom_output.extend;
447 x_custom_output(8) := l_custom_rec;
448
449 l_custom_rec.attribute_name := ':ISC_R7';
450 l_custom_rec.attribute_value := l_bucket_rec.range7_name;
451 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
452 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
453 x_custom_output.extend;
454 x_custom_output(9) := l_custom_rec;
455
456 l_custom_rec.attribute_name := ':ISC_R8';
457 l_custom_rec.attribute_value := l_bucket_rec.range8_name;
458 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
459 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
460 x_custom_output.extend;
461 x_custom_output(10) := l_custom_rec;
462
463 l_custom_rec.attribute_name := ':ISC_R9';
464 l_custom_rec.attribute_value := l_bucket_rec.range9_name;
465 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
466 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
467 x_custom_output.extend;
468 x_custom_output(11) := l_custom_rec;
469
470 l_custom_rec.attribute_name := ':ISC_R10';
471 l_custom_rec.attribute_value := l_bucket_rec.range10_name;
472 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
473 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
474 x_custom_output.extend;
475 x_custom_output(12) := l_custom_rec;
476
477 END Get_Sql;
478
479 END ISC_DBI_PDUE_SCH_AGING_PKG;