1 package body isc_dbi_sam_agr_trend_pkg as
2 /* $Header: ISCRGCFB.pls 120.0 2005/08/30 13:42:44 scheung 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_period_type varchar2(32000);
9 l_comparison_type varchar2(32000);
10 l_sgid varchar2(32000);
11 l_agree varchar2(32000);
12 l_class varchar2(32000);
13 l_cust varchar2(32000);
14 l_curr varchar2(32000);
15 l_curr_suffix varchar2(32000);
16 l_period_str varchar2(32000);
17 l_lag number;
18 l_sg_sg number;
19 l_sg_res number;
20 l_sg_where varchar2(32000);
21 l_agree_where varchar2(32000);
22 l_class_where varchar2(32000);
23 l_cust_where varchar2(32000);
24 l_agree_needed boolean;
25 l_class_needed boolean;
26 l_cust_needed boolean;
27 l_agg_level number;
28 l_query varchar2(32000);
29 l_custom_rec bis_query_attributes;
30
31
32 begin
33
34 -- Get all necessary parameters from PMV
35 for i in 1..p_param.count loop
36
37 if (p_param(i).parameter_name = 'PERIOD_TYPE') then
38 l_period_type := p_param(i).parameter_value;
39 end if;
40
41 if (p_param(i).parameter_name = 'TIME_COMPARISON_TYPE') then
42 l_comparison_type := p_param(i).parameter_value;
43 end if;
44
45 if (p_param(i).parameter_name = 'ORGANIZATION+JTF_ORG_SALES_GROUP') then
46 l_sgid := p_param(i).parameter_id;
47 end if;
48
49 if (p_param(i).parameter_name = 'ISC_AGREEMENT_TYPE+ISC_AGREEMENT_TYPE') then
50 l_agree := p_param(i).parameter_id;
51 end if;
52
53 if (p_param(i).parameter_name = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS') then
54 l_class := p_param(i).parameter_id;
55 end if;
56
57 if (p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS') then
58 l_cust := p_param(i).parameter_id;
59 end if;
60
61 if (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES') then
62 l_curr := p_param(i).parameter_id;
63 end if;
64
65 end loop;
66
67 if (l_curr = '''FII_GLOBAL1''') then
68 l_curr_suffix := 'g';
69 else -- (l_curr = '''FII_GLOBAL2''')
70 l_curr_suffix := 'g1';
71 end if;
72
73 if (l_period_type = 'FII_TIME_ENT_YEAR') then
74 l_period_str := 'yr';
75 elsif (l_period_type = 'FII_TIME_ENT_QTR') then
76 l_period_str := 'qr';
77 elsif (l_period_type = 'FII_TIME_ENT_PERIOD') then
78 l_period_str := 'pd';
79 else -- (l_period_type = 'FII_TIME_WEEK')
80 l_period_str := 'wk';
81 end if;
82
83 case
84 when (l_period_type = 'FII_TIME_WEEK' and l_comparison_type = 'SEQUENTIAL') then l_lag := 1;
85 when (l_period_type = 'FII_TIME_WEEK' and l_comparison_type = 'YEARLY') then l_lag := 13;
86 when (l_period_type = 'FII_TIME_ENT_PERIOD' and l_comparison_type = 'SEQUENTIAL') then l_lag := 1;
87 when (l_period_type = 'FII_TIME_ENT_PERIOD' and l_comparison_type = 'YEARLY') then l_lag := 12;
88 when (l_period_type = 'FII_TIME_ENT_QTR' and l_comparison_type = 'SEQUENTIAL') then l_lag := 1;
89 when (l_period_type = 'FII_TIME_ENT_QTR' and l_comparison_type = 'YEARLY') then l_lag := 4;
90 when (l_period_type = 'FII_TIME_ENT_YEAR' and l_comparison_type = 'SEQUENTIAL') then l_lag := 1;
91 when (l_period_type = 'FII_TIME_ENT_YEAR' and l_comparison_type = 'YEARLY') then l_lag := 1;
92 end case;
93
94 -- Figure out where clauses
95 l_sg_sg := to_number(replace(substr(l_sgid,instr(l_sgid,'.') + 1),''''));
96 l_sg_res := to_number(replace(substr(l_sgid,1,instr(l_sgid,'.') - 1),''''));
97
98 if (l_sg_res is null) then -- when a sales group is chosen
99 l_sg_where := ' and f.sales_grp_id = (&ORGANIZATION+JTF_ORG_SALES_GROUP) and f.resource_id is null';
100 else -- when the LOV parameter is a Salesrep (no need to go through the SG hierarchy)
101 l_sg_where := ' and f.sales_grp_id = :ISC_SG and f.resource_id = :ISC_RES';
102 end if;
103
104 if (l_agree is null) then
105 l_agree_where := '';
106 else
107 l_agree_where := ' and f.agreement_type_id in (&ISC_AGREEMENT_TYPE+ISC_AGREEMENT_TYPE)';
108 end if;
109
110 if (l_class is null) then
111 l_class_where := '';
112 else
113 l_class_where := ' and f.class_code in (&FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS)';
114 end if;
115
116 if (l_cust is null) then
117 l_cust_where := '';
118 else
119 l_cust_where := ' and f.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
120 end if;
121
122 -- Figure out agg_level flag value
123 l_agree_needed := false;
124 l_class_needed := false;
125 l_cust_needed := false;
126
127 if (l_agree is not null) then
128 l_agree_needed := true;
129 end if;
130
131 if (l_class is not null) then
132 l_class_needed := true;
133 end if;
134
135 if (l_cust is not null) then
136 l_cust_needed := true;
137 end if;
138
139 case
140 when ( l_agree_needed and l_class_needed and l_cust_needed) then l_agg_level := 0;
141 when ( l_agree_needed and l_class_needed and not l_cust_needed) then l_agg_level := 2;
142 when ( l_agree_needed and not l_class_needed and l_cust_needed) then l_agg_level := 0;
143 when (not l_agree_needed and l_class_needed and l_cust_needed) then l_agg_level := 1;
144 when ( l_agree_needed and not l_class_needed and not l_cust_needed) then l_agg_level := 4;
145 when (not l_agree_needed and l_class_needed and not l_cust_needed) then l_agg_level := 3;
146 when (not l_agree_needed and not l_class_needed and l_cust_needed) then l_agg_level := 1;
147 when (not l_agree_needed and not l_class_needed and not l_cust_needed) then l_agg_level := 5;
148 end case;
149
150 l_query := '
151 select
152 cal_name VIEWBY,
153 nvl(c_new,0) ISC_MEASURE_5,
154 (c_new-p_new)/decode(p_new,0,null,abs(p_new))*100 ISC_MEASURE_6,
155 nvl(c_exp,0) ISC_MEASURE_7,
156 (c_exp-p_exp)/decode(p_exp,0,null,abs(p_exp))*100 ISC_MEASURE_8,
157 nvl(c_trm,0) ISC_MEASURE_9,
158 (c_trm-p_trm)/decode(p_trm,0,null,abs(p_trm))*100 ISC_MEASURE_10,
159 nvl(c_act,0) ISC_MEASURE_11,
160 (c_act-p_act)/decode(p_act,0,null,abs(p_act))*100 ISC_MEASURE_12,
161 nvl(c_new,0) ISC_MEASURE_13,
162 nvl(c_exp,0) ISC_MEASURE_14,
163 nvl(c_trm,0) ISC_MEASURE_15,
164 nvl(c_act,0) ISC_MEASURE_16
165 from
166 (select
167 cal_name, cal_start_date,
168 sum(c_new) c_new,
169 sum(p_new) p_new,
170 sum(c_exp) c_exp,
171 sum(p_exp) p_exp,
172 sum(c_trm) c_trm,
173 sum(p_trm) p_trm,
174 sum(c_act) c_act,
175 sum(p_act) p_act
176 from
177 (
178 select
179 cal.name cal_name,
180 cal.start_date cal_start_date,
181 c_new,
182 p_new,
183 0 c_exp,
184 0 p_exp,
185 0 c_trm,
186 0 p_trm,
187 0 c_act,
188 0 p_act
189 from
190 (select
191 n.start_date,
192 sum(case when (n.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
193 and i.report_date = least(n.end_date, &BIS_CURRENT_ASOF_DATE))
194 then new_amt else null end) c_new,
195 lag(sum(case when (n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE
196 and i.report_date = least(n.end_date, &BIS_PREVIOUS_ASOF_DATE))
197 then new_amt else null end), :ISC_LAG) over (order by n.start_date) p_new
198 from
199 (
200 select n.start_date,
201 n.report_date ,
202 sum(f.commit_amt_'||l_curr_suffix||') new_amt
203 from isc_sam_001_mv f, -- activation
204 (select /*+ NO_MERGE */ n.time_id,
205 n.record_type_id,
206 n.period_type_id,
207 n.report_date,
208 cal.start_date,
209 cal.end_date
210 from '||l_period_type||' cal,
211 fii_time_rpt_struct_v n
212 where cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
213 and n.report_date in (least(cal.end_date, &BIS_CURRENT_ASOF_DATE), &BIS_PREVIOUS_ASOF_DATE)
214 and n.report_date between cal.start_date and cal.end_date
215 and bitand(n.record_type_id, &BIS_NESTED_PATTERN) = n.record_type_id) n
216 where f.time_id = n.time_id
217 and f.agg_level = :ISC_AGG_LEVEL
218 '||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
219 group by n.start_date, n.report_date) i, '||l_period_type||' n
220 where i.start_date (+) = n.start_date
221 and n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
222 group by n.start_date) iset, '||l_period_type||' cal
223 where cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
224 and cal.start_date = iset.start_date(+)
225 union all
226 select
227 cal.name cal_name,
228 cal.start_date cal_start_date,
229 0 c_new,
230 0 p_new,
231 c_exp,
232 p_exp,
233 0 c_trm,
234 0 p_trm,
235 0 c_act,
236 0 p_act
237 from
238 (select
239 n.start_date,
240 sum(case when (n.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
241 and i.report_date = least(n.end_date, &BIS_CURRENT_ASOF_DATE))
242 then exp_amt else null end) c_exp,
243 lag(sum(case when (n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE
244 and i.report_date = least(n.end_date, &BIS_PREVIOUS_ASOF_DATE))
245 then exp_amt else null end), :ISC_LAG) over (order by n.start_date) p_exp
246 from
247 (select n.start_date,
248 n.report_date ,
249 sum(f.commit_e_t_amt_'||l_curr_suffix||') exp_amt
250 from isc_sam_000_mv f, -- expiration
251 (select /*+ NO_MERGE */ n.time_id,
252 n.record_type_id,
253 n.period_type_id,
254 n.report_date,
255 cal.start_date,
256 cal.end_date
257 from '||l_period_type||' cal,
258 fii_time_rpt_struct_v n
259 where cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
260 and n.report_date in (least(cal.end_date, &BIS_CURRENT_ASOF_DATE), &BIS_PREVIOUS_ASOF_DATE)
261 and n.report_date between cal.start_date and cal.end_date
262 and bitand(n.record_type_id, &BIS_NESTED_PATTERN) = n.record_type_id) n
263 where f.time_id = n.time_id
264 and f.agg_level = :ISC_AGG_LEVEL
265 '||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
266 group by n.start_date, n.report_date) i, '||l_period_type||' n
267 where i.start_date (+) = n.start_date
268 and n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
269 group by n.start_date) iset, '||l_period_type||' cal
270 where cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
271 and cal.start_date = iset.start_date(+)
272 union all
273 select
274 cal.name cal_name,
275 cal.start_date cal_start_date,
276 0 c_new,
277 0 p_new,
278 -c_exp,
279 -p_exp,
280 c_trm,
281 p_trm,
282 0 c_act,
283 0 p_act
284 from
285 (select
286 n.start_date,
287 sum(case when (n.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
288 and i.report_date = least(n.end_date, &BIS_CURRENT_ASOF_DATE))
289 then exp_amt else null end) c_exp,
290 lag(sum(case when (n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE
291 and i.report_date = least(n.end_date, &BIS_PREVIOUS_ASOF_DATE))
292 then exp_amt else null end), :ISC_LAG) over (order by n.start_date) p_exp,
293 sum(case when (n.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
294 and i.report_date = least(n.end_date, &BIS_CURRENT_ASOF_DATE))
295 then trm_amt else null end) c_trm,
296 lag(sum(case when (n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE
297 and i.report_date = least(n.end_date, &BIS_PREVIOUS_ASOF_DATE))
298 then trm_amt else null end), :ISC_LAG) over (order by n.start_date) p_trm
299 from
300 (select n.start_date,
301 n.report_date ,
302 sum(f.commit_e_t_e'||l_period_str||'_amt_'||l_curr_suffix||') exp_amt,
303 sum(f.commit_amt_'||l_curr_suffix||') trm_amt
304 from isc_sam_002_mv f, -- termination
305 (select /*+ NO_MERGE */ n.time_id,
306 n.record_type_id,
307 n.period_type_id,
308 n.report_date,
309 cal.start_date,
310 cal.end_date
311 from '||l_period_type||' cal,
312 fii_time_rpt_struct_v n
313 where cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
314 and n.report_date in (least(cal.end_date, &BIS_CURRENT_ASOF_DATE), &BIS_PREVIOUS_ASOF_DATE)
315 and n.report_date between cal.start_date and cal.end_date
316 and bitand(n.record_type_id, &BIS_NESTED_PATTERN) = n.record_type_id) n
317 where f.time_id = n.time_id
318 and f.agg_level = :ISC_AGG_LEVEL
319 '||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
320 group by n.start_date, n.report_date) i, '||l_period_type||' n
321 where i.start_date (+) = n.start_date
322 and n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
323 group by n.start_date) iset, '||l_period_type||' cal
324 where cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
325 and cal.start_date = iset.start_date(+)
326 union all
327 select
328 cal.name cal_name,
329 cal.start_date cal_start_date,
330 0 c_new,
331 0 p_new,
332 0 c_exp,
333 0 p_exp,
334 0 c_trm,
335 0 p_trm,
336 c_act,
337 p_act
338 from
339 (select
340 n.start_date,
341 sum(case when (n.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
342 and i.report_date = least(n.end_date, &BIS_CURRENT_ASOF_DATE))
343 then act_amt else null end) c_act,
344 lag(sum(case when (n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE
345 and i.report_date = least(n.end_date, &BIS_PREVIOUS_ASOF_DATE))
346 then act_amt else null end), :ISC_LAG) over (order by n.start_date) p_act
347 from
348 (
349 select n.start_date,
350 n.report_date ,
351 sum(f.commit_amt_'||l_curr_suffix||') act_amt
352 from isc_sam_003_mv f, -- active balance
353 (select /*+ NO_MERGE */ n.ent_year_id,
354 n.report_date,
355 cal.start_date,
356 cal.end_date
357 from '||l_period_type||' cal,
358 fii_time_day n
359 where cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
360 and n.report_date in (least(cal.end_date, &BIS_CURRENT_ASOF_DATE), &BIS_PREVIOUS_ASOF_DATE)
361 and n.report_date between cal.start_date and cal.end_date) n
362 where f.ent_year_id = n.ent_year_id
363 and f.agg_level = :ISC_AGG_LEVEL
364 '||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
365 group by n.start_date, n.report_date) i, '||l_period_type||' n
366 where i.start_date (+) = n.start_date
367 and n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
368 group by n.start_date) iset, '||l_period_type||' cal
369 where cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
370 and cal.start_date = iset.start_date(+)
371 union all
372 select
373 cal.name cal_name,
374 cal.start_date cal_start_date,
375 0 c_new,
376 0 p_new,
377 0 c_exp,
378 0 p_exp,
379 0 c_trm,
380 0 p_trm,
381 c_act,
382 p_act
383 from
384 (select
385 n.start_date,
386 sum(case when (n.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
387 and i.report_date = least(n.end_date, &BIS_CURRENT_ASOF_DATE))
388 then act_amt else null end) c_act,
389 lag(sum(case when (n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE
390 and i.report_date = least(n.end_date, &BIS_PREVIOUS_ASOF_DATE))
391 then act_amt else null end), :ISC_LAG) over (order by n.start_date) p_act
392 from
393 (
394 select n.start_date,
395 n.report_date ,
396 sum(f.commit_amt_'||l_curr_suffix||') act_amt
397 from isc_sam_001_mv f, -- activation
398 (select /*+ NO_MERGE */ n.time_id,
399 n.record_type_id,
400 n.period_type_id,
401 n.report_date,
402 cal.start_date,
403 cal.end_date
404 from '||l_period_type||' cal,
405 fii_time_rpt_struct_v n
406 where cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
407 and n.report_date in (least(cal.end_date, &BIS_CURRENT_ASOF_DATE), &BIS_PREVIOUS_ASOF_DATE)
408 and n.report_date between cal.start_date and cal.end_date
409 and bitand(n.record_type_id, 119) = n.record_type_id) n
410 where f.time_id = n.time_id
411 and f.agg_level = :ISC_AGG_LEVEL
415 and n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
412 '||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
413 group by n.start_date, n.report_date) i, '||l_period_type||' n
414 where i.start_date (+) = n.start_date
416 group by n.start_date) iset, '||l_period_type||' cal
417 where cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
418 and cal.start_date = iset.start_date(+)
419 union all
420 select
421 cal.name cal_name,
422 cal.start_date cal_start_date,
423 0 c_new,
424 0 p_new,
425 0 c_exp,
426 0 p_exp,
427 0 c_trm,
428 0 p_trm,
429 -c_act,
430 -p_act
431 from
432 (select
433 n.start_date,
434 sum(case when (n.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
435 and i.report_date = least(n.end_date, &BIS_CURRENT_ASOF_DATE))
436 then act_amt else null end) c_act,
437 lag(sum(case when (n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE
438 and i.report_date = least(n.end_date, &BIS_PREVIOUS_ASOF_DATE))
439 then act_amt else null end), :ISC_LAG) over (order by n.start_date) p_act
440 from
441 (
442 select n.start_date,
443 n.report_date ,
444 sum(f.commit_amt_'||l_curr_suffix||') act_amt
445 from isc_sam_004_mv f, -- effective end
446 (select /*+ NO_MERGE */ n.time_id,
447 n.record_type_id,
448 n.period_type_id,
449 n.report_date,
450 cal.start_date,
451 cal.end_date
452 from '||l_period_type||' cal,
453 fii_time_rpt_struct_v n
454 where cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
455 and n.report_date in (least(cal.end_date, &BIS_CURRENT_ASOF_DATE), &BIS_PREVIOUS_ASOF_DATE)
456 and n.report_date between cal.start_date and cal.end_date
457 and bitand(n.record_type_id, 119) = n.record_type_id) n
458 where f.time_id = n.time_id
459 and f.agg_level = :ISC_AGG_LEVEL
460 '||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
461 group by n.start_date, n.report_date) i, '||l_period_type||' n
462 where i.start_date (+) = n.start_date
463 and n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
464 group by n.start_date) iset, '||l_period_type||' cal
465 where cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
466 and cal.start_date = iset.start_date(+)
467 )
468 group by cal_name, cal_start_date) uset
469 order by cal_start_date
470 ';
471
472 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
473 x_custom_output := bis_query_attributes_tbl();
474
475 l_custom_rec.attribute_name := ':ISC_AGG_LEVEL';
476 l_custom_rec.attribute_value := l_agg_level;
477 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
478 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
479 x_custom_output.EXTEND;
480 x_custom_output(x_custom_output.count) := l_custom_rec;
481
482 l_custom_rec.attribute_name := ':ISC_LAG';
483 l_custom_rec.attribute_value := l_lag;
484 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
485 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
486 x_custom_output.EXTEND;
487 x_custom_output(x_custom_output.count) := l_custom_rec;
488
489 l_custom_rec.attribute_name := ':ISC_SG';
490 l_custom_rec.attribute_value := to_char(l_sg_sg);
491 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
492 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
493 x_custom_output.extend;
494 x_custom_output(x_custom_output.count) := l_custom_rec;
495
496 l_custom_rec.attribute_name := ':ISC_RES';
497 l_custom_rec.attribute_value := to_char(l_sg_res);
501 x_custom_output(x_custom_output.count) := l_custom_rec;
498 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
499 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
500 x_custom_output.extend;
502
503 x_custom_sql := l_query;
504
505 end get_sql;
506
507 end isc_dbi_sam_agr_trend_pkg;
[Home] [Help]