[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_REV_CUM_TREND_PKG
Source
1 PACKAGE BODY ISC_DBI_REV_CUM_TREND_PKG AS
2 /* $Header: ISCRGBNB.pls 120.2 2006/06/26 07:05:23 abhdixi noship $ */
3
4 PROCEDURE GET_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,x_custom_sql OUT NOCOPY VARCHAR2,
5 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
6
7 l_stmt VARCHAR2(32000);
8 l_period_type VARCHAR2(32000);
9 l_sgid VARCHAR2(32000);
10 l_sg_where VARCHAR2(32000);
11 l_prod_cat VARCHAR2(32000);
12 l_prod_cat_from VARCHAR2(32000);
13 l_prod_cat_where VARCHAR2(32000);
14 l_cust VARCHAR2(32000);
15 l_cust_where VARCHAR2(32000);
16 l_class VARCHAR2(32000);
17 l_class_where VARCHAR2(32000);
18 l_sg_sg NUMBER;
19 l_sg_res NUMBER;
20 l_item_cat_flag NUMBER;
21 l_cust_flag NUMBER; -- 0 for customer, 1 for cust class, 3 for all
22 l_flags VARCHAR2(32000);
23 l_mv VARCHAR2(100);
24 l_curr VARCHAR2(10000);
25 l_curr_suffix VARCHAR2(120);
26 l_invalid_curr BOOLEAN;
27 l_custom_rec BIS_QUERY_ATTRIBUTES;
28
29 l_as_of_date DATE;
30 l_prev_asof DATE;
31 l_adjust1 VARCHAR2(100);
32 l_adjust2 VARCHAR2(100);
33 l_id VARCHAR2(200); -- l_id and l_id2 are parts of WHERE clause,
34 l_id2 VARCHAR2(200); -- through which we restrict the date
35 l_day_id1 NUMBER; -- l_day_id1 and l_day_id2 are used to store day-ids
36 l_day_id2 NUMBER; -- for start date and end date of the period
37 l_curr_start DATE;
38 l_curr_end DATE;
39 l_prior_start DATE;
40 l_prior_end DATE;
41 l_temp DATE;
42
43 BEGIN
44
45 fii_gl_util_pkg.reset_globals;
46 fii_gl_util_pkg.get_parameters(p_param);
47
48 l_invalid_curr := FALSE;
49
50 FOR i IN 1..p_param.COUNT
51 LOOP
52
53 IF(p_param(i).parameter_name = 'AS_OF_DATE')
54 THEN l_as_of_date := to_date(p_param(i).parameter_value, 'DD-MM-YYYY');
55 END IF;
56
57 IF(p_param(i).parameter_name = 'BIS_PREVIOUS_ASOF_DATE')
58 THEN l_prev_asof := to_date(p_param(i).parameter_value, 'DD-MM-YYYY');
59 END IF;
60
61 IF (p_param(i).parameter_name = 'PERIOD_TYPE')
62 THEN l_period_type := p_param(i).parameter_value;
63 END IF;
64
65 IF(p_param(i).parameter_name = 'ORGANIZATION+JTF_ORG_SALES_GROUP') THEN
66 l_sgid := p_param(i).parameter_id;
67 END IF;
68
69 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT') THEN
70 l_prod_cat := p_param(i).parameter_id;
71 END IF;
72
73 IF(p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS') THEN
74 l_cust := p_param(i).parameter_id;
75 END IF;
76
77 IF(p_param(i).parameter_name = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS') THEN
78 l_class := p_param(i).parameter_id;
79 END IF;
80
81 IF (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
82 THEN l_curr := p_param(i).parameter_id;
83 END IF;
84
85 END LOOP;
86
87 IF (l_curr = '''FII_GLOBAL1''')
88 THEN l_curr_suffix := 'g';
89 ELSIF (l_curr = '''FII_GLOBAL2''')
90 THEN l_curr_suffix := 'g1';
91 ELSE
92 l_invalid_curr := TRUE;
93 END IF;
94
95 IF l_period_type = 'FII_TIME_ENT_YEAR' THEN
96 l_adjust1 := NULL;
97 l_adjust2 := NULL;
98 l_curr_start := fii_time_api.ent_cyr_start(l_as_of_date);
99 l_curr_end := fii_time_api.ent_cyr_end(l_as_of_date);
100 l_prior_start := fii_time_api.ent_cyr_start(l_prev_asof);
101 l_prior_end := fii_time_api.ent_cyr_end(l_prev_asof);
102 l_temp := fii_time_api.ent_cper_end(l_as_of_date);
103
104 ELSIF l_period_type = 'FII_TIME_ENT_QTR' THEN
105 l_adjust1 := ':ISC_CURR_START-:ISC_CURR_END';
106 l_adjust2 := ':ISC_PRIOR_START-:ISC_PRIOR_END';
107 l_curr_start := fii_time_api.ent_cqtr_start(l_as_of_date);
108 l_curr_end := fii_time_api.ent_cqtr_end(l_as_of_date);
109 l_prior_start := fii_time_api.ent_cqtr_start(l_prev_asof);
110 l_prior_end := fii_time_api.ent_cqtr_end(l_prev_asof);
111 l_temp := NULL;
112
113 ELSIF l_period_type = 'FII_TIME_ENT_PERIOD' THEN
114 l_adjust1 := '1';
115 l_adjust2 := '1';
116 l_curr_start := fii_time_api.ent_cper_start(l_as_of_date);
117 l_curr_end := fii_time_api.ent_cper_end(l_as_of_date);
118 l_prior_start := fii_time_api.ent_cper_start(l_prev_asof);
119 l_prior_end := fii_time_api.ent_cper_end(l_prev_asof);
120 l_temp := NULL;
121
122 ELSE -- l_period_type = 'FII_TIME_WEEK'
123 l_adjust1 := '1';
124 l_adjust2 := '1';
125 l_curr_start := fii_time_api.cwk_start(l_as_of_date);
126 l_curr_end := fii_time_api.cwk_end(l_as_of_date);
127 l_prior_start := fii_time_api.cwk_start(l_prev_asof);
128 l_prior_end := fii_time_api.cwk_end(l_prev_asof);
129 l_temp := NULL;
130
131 END IF;
132
133 SELECT report_date_julian INTO l_day_id1 FROM fii_time_day WHERE report_date = l_curr_start;
134 SELECT report_date_julian INTO l_day_id2 FROM fii_time_day WHERE report_date = l_curr_end;
135 l_id := '(g.report_date_julian between '||l_day_id1|| ' and ' ||l_day_id2 ||')';
136
137 SELECT report_date_julian INTO l_day_id1 FROM fii_time_day WHERE report_date = l_prior_start;
138 SELECT report_date_julian INTO l_day_id2 FROM fii_time_day WHERE report_date = l_prior_end;
139 l_id2 := '(g.report_date_julian between '||l_day_id1|| ' and ' ||l_day_id2 ||')';
140
141 l_sg_sg := to_number(replace(substr(l_sgid,instr(l_sgid,'.') + 1),''''));
142 l_sg_res := to_number(replace(substr(l_sgid,1,instr(l_sgid,'.') - 1),''''));
143
144 IF (l_sg_res IS NULL) -- when a sales group is chosen
145 THEN
146 l_sg_where := '
147 AND f.sales_grp_id = (&ORGANIZATION+JTF_ORG_SALES_GROUP)
148 AND f.resource_id IS NULL';
149 ELSE -- when the LOV parameter is a SRep (no need to go through the SG hierarchy MV
150 l_sg_where := '
151 AND f.sales_grp_id = :ISC_SG
152 AND f.resource_id = :ISC_RES';
153 END IF;
154
155 IF (l_cust IS NULL)
156 THEN
157 l_cust_where := '';
158 IF (l_class IS NULL)
159 THEN l_cust_flag := 3; -- all
160 ELSE l_cust_flag := 1; -- customer classification
161 END IF;
162 ELSE
163 l_cust_where := '
164 AND f.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
165 l_cust_flag := 0; -- customer
166 END IF;
167
168 IF (l_class IS NULL) THEN
169 l_class_where:='';
170 ELSE
171 l_class_where :='
172 AND f.class_code in (&FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS)';
173 END IF;
174
175
176 IF (l_cust IS NULL AND l_class IS NULL) THEN -- use double rollup without cust
177 l_flags := '';
178 l_mv := 'ISC_DBI_SCR_002_MV';
179 l_prod_cat_from := ''; -- do not need to join to denorm table
180 IF (l_prod_cat IS NULL) THEN
181 l_prod_cat_where :='
182 AND f.cat_top_node_flag = ''Y''';
183 ELSE -- view by sales group, prod.cat selected
184 l_prod_cat_where :='
185 AND f.item_category_id IN (&ITEM+ENI_ITEM_VBH_CAT)';
186 END IF;
187
188 ELSE -- use single rollup with customer dimension
189 l_flags := '
190 AND f.item_cat_flag = :ISC_ITEM_CAT_FLAG
191 AND f.customer_flag = :ISC_CUST';
192 l_mv := 'ISC_DBI_SCR_001_MV';
193 IF (l_prod_cat IS NULL)
194 THEN l_prod_cat_from := '';
195 l_prod_cat_where := '';
196 ELSE
197 l_prod_cat_from := ',
198 ENI_DENORM_HIERARCHIES eni_cat,
199 MTL_DEFAULT_CATEGORY_SETS mdcs';
200 l_prod_cat_where := '
201 AND f.item_category_id = eni_cat.child_id
202 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
203 AND eni_cat.dbi_flag = ''Y''
204 AND eni_cat.object_type = ''CATEGORY_SET''
205 AND eni_cat.object_id = mdcs.category_set_id
206 AND mdcs.functional_area_id = 11';
207 END IF;
208 END IF;
209
210 IF (l_prod_cat IS NULL)
211 THEN l_item_cat_flag := 1; -- All
212 ELSE l_item_cat_flag := 0; -- Product Category
213 END IF;
214
215
216 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
217 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
218
219 IF (l_invalid_curr)
220 THEN l_stmt := '
221 /* Unsupported currency */
222 SELECT 0 ISC_MEASURE_2,
223 0 ISC_MEASURE_1,
224 0 ISC_MEASURE_4,
225 0 ISC_MEASURE_3
226
227 FROM dual
228 WHERE 1 = 2';
229
230 ELSE
231
232 /* For period type = Year:
233 - 1st inner sql gives the prior year bookings & revenue
234 - 2nd inner sql gives the bookings & revenue of all completed months in the current year
235 - 3rd inner sql gives NULL bookings & revenue for the months ranging from
236 current month to the end of current year
237
238 For period type = Week / Month / Quarter:
239 - 1st inner sql gives current period bookings & revenue
240 - 2nd inner sql gives the prior period bookings & revenue
241 */
242
243 IF l_period_type = 'FII_TIME_ENT_YEAR' THEN
244 l_stmt := '
245 SELECT SUBSTR(month_name,1,3) VIEWBY,
246 SUM(P_NET_BOOK) OVER (ORDER BY FII_EFFECTIVE_NUM ROWS UNBOUNDED PRECEDING) ISC_MEASURE_2,
247 CASE WHEN c_net_book IS NULL THEN to_number(NULL)
248 ELSE SUM(C_NET_BOOK) OVER (ORDER BY FII_EFFECTIVE_NUM ROWS UNBOUNDED PRECEDING)
249 END ISC_MEASURE_1,
250 SUM(P_REV) OVER (ORDER BY FII_EFFECTIVE_NUM ROWS UNBOUNDED PRECEDING) ISC_MEASURE_4,
251 CASE WHEN c_rev IS NULL THEN to_number(NULL)
252 ELSE SUM(C_REV) OVER (ORDER BY FII_EFFECTIVE_NUM ROWS UNBOUNDED PRECEDING)
253 END ISC_MEASURE_3
254
255 FROM
256 (SELECT MAX(month_name) MONTH_NAME,
257 FII_EFFECTIVE_NUM FII_EFFECTIVE_NUM,
258 SUM(C_NET_BOOK) C_NET_BOOK,
259 SUM(P_NET_BOOK) P_NET_BOOK,
260 SUM(C_REV) C_REV,
261 SUM(P_REV) P_REV
262 FROM (
263 SELECT per.sequence FII_EFFECTIVE_NUM,
264 per.name MONTH_NAME,
265 per.ent_period_id ID,
266 NULL C_NET_BOOK,
267 (CASE WHEN per.end_date <= :ISC_PRIOR_END
268 THEN f.net_booked_amt_'||l_curr_suffix||'
269 ELSE to_number(NULL) END
270 ) P_NET_BOOK,
271 NULL C_REV,
272 (CASE WHEN per.end_date <= :ISC_PRIOR_END
273 THEN f.recognized_amt_'||l_curr_suffix||'
274 ELSE to_number(NULL) END
275 ) P_REV
276 FROM FII_TIME_ENT_PERIOD per,
277 '||l_mv||' f'
278 ||l_prod_cat_from||'
279 WHERE per.ent_period_id = f.time_id
280 AND per.start_date >= :ISC_PRIOR_START
281 AND per.end_date <= :ISC_PRIOR_END
282 AND f.period_type_id = 32'
283 ||l_flags
284 ||l_sg_where||l_prod_cat_where||l_cust_where||l_class_where||'
285 UNION ALL
286 (
287 SELECT per.sequence FII_EFFECTIVE_NUM,
288 per.name MONTH_NAME,
289 per.ent_period_id ID,
290 (CASE WHEN per.start_date >= :ISC_CURR_START
291 AND per.end_date < &BIS_CURRENT_ASOF_DATE
292 THEN f.net_booked_amt_'||l_curr_suffix||'
293 ELSE to_number(NULL) END
294 ) C_NET_BOOK,
295 0 P_NET_BOOK,
296 (CASE WHEN per.start_date >= :ISC_CURR_START
297 AND per.end_date < &BIS_CURRENT_ASOF_DATE
298 THEN f.recognized_amt_'||l_curr_suffix||'
299 ELSE to_number(NULL) END
300 ) C_REV,
301 0 P_REV
302 FROM FII_TIME_ENT_PERIOD per,
303 '||l_mv||' f'
304 ||l_prod_cat_from||'
305 WHERE per.ent_period_id = f.time_id
306 AND per.start_date >= :ISC_CURR_START
307 AND per.end_date < &BIS_CURRENT_ASOF_DATE
308 AND f.period_type_id = 32'
309 ||l_flags
310 ||l_sg_where||l_prod_cat_where||l_cust_where||l_class_where||'
311 UNION ALL
312 SELECT per.sequence FII_EFFECTIVE_NUM,
313 per.name MONTH_NAME,
314 per.ent_period_id ID,
315 f.net_booked_amt_'||l_curr_suffix||' C_NET_BOOK,
316 0 P_NET_BOOK,
317 f.recognized_amt_'||l_curr_suffix||' C_REV,
318 0 P_REV
319 FROM FII_TIME_RPT_STRUCT_V cal,
320 FII_TIME_ENT_PERIOD per,
321 '||l_mv||' f'
322 ||l_prod_cat_from||'
323 WHERE cal.time_id = f.time_id
324 AND cal.report_date between per.start_date and per.end_date
325 AND cal.report_date = &BIS_CURRENT_ASOF_DATE
326 AND bitand(cal.record_type_id, 23) = cal.record_type_id'
327 ||l_flags
328 ||l_sg_where||l_prod_cat_where||l_cust_where||l_class_where||'
329 UNION ALL
330 SELECT per.sequence FII_EFFECTIVE_NUM,
331 per.name MONTH_NAME,
332 per.ent_period_id ID,
333 CASE WHEN per.end_date > :ISC_TEMP
334 THEN to_number(NULL)
335 ELSE 0 END C_NET_BOOK,
336 0 P_NET_BOOK,
337 CASE WHEN per.end_date > :ISC_TEMP
338 THEN to_number(NULL)
339 ELSE 0 END C_REV,
340 0 P_REV
341 FROM FII_TIME_ENT_PERIOD per
342 WHERE per.start_date >= :ISC_CURR_START
343 AND per.end_date <= :ISC_CURR_END
344 ))
345 GROUP BY FII_EFFECTIVE_NUM
346 ORDER BY FII_EFFECTIVE_NUM
347 )';
348
349 ELSE
350 l_stmt := '
351 SELECT days VIEWBY,
352 SUM(DECODE(SIGN(report_date - &BIS_CURRENT_ASOF_DATE),
353 1, NULL, C_NET_BOOK)) ISC_MEASURE_1,
354 SUM(DECODE(SIGN(report_date - :ISC_PRIOR_END),
355 1, NULL, P_NET_BOOK)) ISC_MEASURE_2,
356 SUM(DECODE(SIGN(report_date - &BIS_CURRENT_ASOF_DATE),
357 1, NULL, C_REV)) ISC_MEASURE_3,
358 SUM(DECODE(SIGN(report_date - :ISC_PRIOR_END),
359 1, NULL, P_REV)) ISC_MEASURE_4
360 FROM (
361 SELECT g.report_date - :ISC_CURR_START + to_number('||l_adjust1||')
362 DAYS,
363 report_date,
364 NVL(SUM(SUM(f.c_book_xtd)) OVER
365 (ORDER BY g.report_date - :ISC_CURR_START + to_number('||l_adjust1||')
366 ROWS UNBOUNDED PRECEDING),0) C_NET_BOOK,
367 0 P_NET_BOOK,
368 NVL(SUM(SUM(f.c_rev_xtd)) OVER
369 (ORDER BY g.report_date - :ISC_CURR_START + to_number('||l_adjust1||')
370 ROWS UNBOUNDED PRECEDING),0) C_REV,
371 0 P_REV
372 FROM FII_TIME_DAY g,
373 (SELECT time_id,
374 net_booked_amt_'||l_curr_suffix||' C_BOOK_XTD,
375 recognized_amt_'||l_curr_suffix||' C_REV_XTD
376 FROM '||l_mv||' f'
377 ||l_prod_cat_from||'
378 WHERE f.period_type_id (+) = 1'
379 ||l_flags
380 ||l_sg_where||l_prod_cat_where||l_cust_where||l_class_where||'
381 ) f
382 WHERE g.report_date_julian = f.time_id (+)
383 AND '||l_id||'
384 GROUP BY g.report_date - :ISC_CURR_START + to_number('||l_adjust1||'),
385 report_date
386 UNION ALL
387 SELECT g.report_date - :ISC_PRIOR_START + to_number('||l_adjust2||')
388 DAYS,
389 report_date,
390 to_number(NULL) C_NET_BOOK,
391 NVL(SUM(SUM(f.p_book_xtd)) OVER
392 (ORDER BY g.report_date - :ISC_PRIOR_START + to_number('||l_adjust2||')
393 ROWS UNBOUNDED PRECEDING),0) P_NET_BOOK,
394 to_number(NULL) C_REV,
395 NVL(SUM(SUM(f.p_rev_xtd)) OVER
396 (ORDER BY g.report_date-:ISC_PRIOR_START+to_number('||l_adjust2||')
397 ROWS UNBOUNDED PRECEDING),0) P_REV
398 FROM FII_TIME_DAY g,
399 (SELECT time_id,
400 net_booked_amt_'||l_curr_suffix||' P_BOOK_XTD,
401 recognized_amt_'||l_curr_suffix||' P_REV_XTD
402 FROM '||l_mv||' f'
403 ||l_prod_cat_from||'
404 WHERE f.period_type_id (+) = 1'
405 ||l_flags
406 ||l_sg_where||l_prod_cat_where||l_cust_where||l_class_where||'
407 ) f
408 WHERE g.report_date_julian = f.time_id (+)
409 AND '||l_id2||'
410 GROUP BY g.report_date - :ISC_PRIOR_START + to_number('||l_adjust2||'),
411 report_date
412 )
413 GROUP BY days
414 ORDER BY days';
415 END IF;
416
417 END IF;
418
419 x_custom_sql := l_stmt;
420
421 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.View_By_Value;
422 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
423 IF l_period_type = 'FII_TIME_ENT_YEAR' THEN
424 l_custom_rec.attribute_value := 'TIME+FII_TIME_ENT_PERIOD';
425 ELSE -- l_period_type = 'FII_TIME_ENT_QTR', 'FII_TIME_ENT_PERIOD', 'FII_TIME_WEEK'
426 l_custom_rec.attribute_value := 'TIME+FII_TIME_DAY';
427 END IF;
428 x_custom_output.extend;
429 x_custom_output(1) := l_custom_rec;
430
431 l_custom_rec.attribute_name := ':ISC_CUST';
432 l_custom_rec.attribute_value := l_cust_flag;
433 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
434 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
435 x_custom_output.EXTEND;
436 x_custom_output(2) := l_custom_rec;
437
438 l_custom_rec.attribute_name := ':ISC_SG';
439 l_custom_rec.attribute_value := to_char(l_sg_sg);
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(3) := l_custom_rec;
444
445 l_custom_rec.attribute_name := ':ISC_RES';
446 l_custom_rec.attribute_value := to_char(l_sg_res);
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(4) := l_custom_rec;
451
452 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
453 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
454 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
455 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
456 x_custom_output.extend;
457 x_custom_output(5) := l_custom_rec;
458
459 l_custom_rec.attribute_name := ':ISC_CURR_START';
460 l_custom_rec.attribute_value := to_char(l_curr_start,'DD-MM-YYYY');
461 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
462 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.date_bind;
463 x_custom_output.extend;
464 x_custom_output(6) := l_custom_rec;
465
466 l_custom_rec.attribute_name := ':ISC_CURR_END';
467 l_custom_rec.attribute_value := to_char(l_curr_end,'DD-MM-YYYY');
468 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
469 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.date_bind;
470 x_custom_output.extend;
471 x_custom_output(7) := l_custom_rec;
472
473 l_custom_rec.attribute_name := ':ISC_PRIOR_START';
474 l_custom_rec.attribute_value := to_char(l_prior_start,'DD-MM-YYYY');
475 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
476 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.date_bind;
477 x_custom_output.extend;
478 x_custom_output(8) := l_custom_rec;
479
480 l_custom_rec.attribute_name := ':ISC_PRIOR_END';
481 l_custom_rec.attribute_value := to_char(l_prior_end,'DD-MM-YYYY');
482 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
483 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.date_bind;
484 x_custom_output.extend;
485 x_custom_output(9) := l_custom_rec;
486
487 l_custom_rec.attribute_name := ':ISC_TEMP';
488 l_custom_rec.attribute_value := to_char(l_temp,'DD-MM-YYYY');
489 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
490 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.date_bind;
491 x_custom_output.extend;
492 x_custom_output(10) := l_custom_rec;
493
494 END get_sql;
495
496 END ISC_DBI_REV_CUM_TREND_PKG;