1 package body isc_dbi_sam_out_trend_pkg as
2 /* $Header: ISCRGCEB.pls 120.1 2006/04/19 17:32:54 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_out,0) ISC_MEASURE_3,
154 (c_out-p_out)/decode(p_out,0,null,abs(p_out))*100 ISC_MEASURE_4,
155 nvl(c_act,0) ISC_MEASURE_5,
156 (c_act-p_act)/decode(p_act,0,null,abs(p_act))*100 ISC_MEASURE_6,
157 nvl(c_out,0) ISC_MEASURE_7,
158 nvl(c_act,0) ISC_MEASURE_8
159 from
160 (select
161 cal_name, cal_start_date,
162 sum(c_act)+sum(c_out) c_out,
163 sum(p_act)+sum(p_out) p_out,
164 sum(c_act) c_act,
165 sum(p_act) p_act
166 from
167 (
168 select
169 cal.name cal_name,
170 cal.start_date cal_start_date,
171 0 c_out,
172 0 p_out,
173 c_act,
174 p_act
175 from
176 (select
177 n.start_date,
178 sum(case when (n.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
179 and i.report_date = least(n.end_date, &BIS_CURRENT_ASOF_DATE))
180 then act_amt else null end) c_act,
181 lag(sum(case when (n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE
182 and i.report_date = least(n.end_date, &BIS_PREVIOUS_ASOF_DATE))
183 then act_amt else null end), :ISC_LAG) over (order by n.start_date) p_act
184 from
185 (
186 select n.start_date,
187 n.report_date ,
188 sum(f.commit_amt_'||l_curr_suffix||') act_amt
189 from isc_sam_003_mv f, -- active balance
190 (select /*+ NO_MERGE */ n.ent_year_id,
191 n.report_date,
192 cal.start_date,
193 cal.end_date
194 from '||l_period_type||' cal,
195 fii_time_day n
196 where cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
197 and n.report_date in (least(cal.end_date, &BIS_CURRENT_ASOF_DATE), &BIS_PREVIOUS_ASOF_DATE)
198 and n.report_date between cal.start_date and cal.end_date) n
199 where f.ent_year_id = n.ent_year_id
200 and f.agg_level = :ISC_AGG_LEVEL
201 '||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
202 group by n.start_date, n.report_date) i, '||l_period_type||' n
203 where i.start_date (+) = n.start_date
204 and n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
205 group by n.start_date) iset, '||l_period_type||' cal
206 where cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
207 and cal.start_date = iset.start_date(+)
208 union all
209 select
210 cal.name cal_name,
211 cal.start_date cal_start_date,
212 0 c_out,
213 0 p_out,
214 c_act,
215 p_act
216 from
217 (select
218 n.start_date,
219 sum(case when (n.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
220 and i.report_date = least(n.end_date, &BIS_CURRENT_ASOF_DATE))
221 then act_amt else null end) c_act,
222 lag(sum(case when (n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE
223 and i.report_date = least(n.end_date, &BIS_PREVIOUS_ASOF_DATE))
224 then act_amt else null end), :ISC_LAG) over (order by n.start_date) p_act
225 from
226 (
227 select n.start_date,
228 n.report_date ,
229 sum(f.commit_amt_'||l_curr_suffix||') act_amt
230 from isc_sam_001_mv f, -- activation
231 (select /*+ NO_MERGE */ n.time_id,
232 n.record_type_id,
233 n.period_type_id,
234 n.report_date,
235 cal.start_date,
236 cal.end_date
237 from '||l_period_type||' cal,
238 fii_time_rpt_struct_v n
239 where cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
240 and n.report_date in (least(cal.end_date, &BIS_CURRENT_ASOF_DATE), &BIS_PREVIOUS_ASOF_DATE)
241 and n.report_date between cal.start_date and cal.end_date
242 and bitand(n.record_type_id, 119) = n.record_type_id) n
243 where f.time_id = n.time_id
244 and f.agg_level = :ISC_AGG_LEVEL
245 '||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
246 group by n.start_date, n.report_date) i, '||l_period_type||' n
247 where i.start_date (+) = n.start_date
248 and n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
249 group by n.start_date) iset, '||l_period_type||' cal
250 where cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
251 and cal.start_date = iset.start_date(+)
252 union all
253 select
254 cal.name cal_name,
255 cal.start_date cal_start_date,
256 0 c_out,
257 0 p_out,
258 -c_act,
259 -p_act
260 from
261 (select
262 n.start_date,
263 sum(case when (n.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
264 and i.report_date = least(n.end_date, &BIS_CURRENT_ASOF_DATE))
265 then act_amt else null end) c_act,
266 lag(sum(case when (n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE
267 and i.report_date = least(n.end_date, &BIS_PREVIOUS_ASOF_DATE))
268 then act_amt else null end), :ISC_LAG) over (order by n.start_date) p_act
269 from
270 (
271 select n.start_date,
272 n.report_date ,
273 sum(f.commit_amt_'||l_curr_suffix||') act_amt
274 from isc_sam_004_mv f, -- effective end
275 (select /*+ NO_MERGE */ n.time_id,
276 n.record_type_id,
277 n.period_type_id,
278 n.report_date,
279 cal.start_date,
280 cal.end_date
281 from '||l_period_type||' cal,
282 fii_time_rpt_struct_v n
283 where cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
284 and n.report_date in (least(cal.end_date, &BIS_CURRENT_ASOF_DATE), &BIS_PREVIOUS_ASOF_DATE)
285 and n.report_date between cal.start_date and cal.end_date
286 and bitand(n.record_type_id, 119) = n.record_type_id) n
287 where f.time_id = n.time_id
288 and f.agg_level = :ISC_AGG_LEVEL
289 '||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
290 group by n.start_date, n.report_date) i, '||l_period_type||' n
291 where i.start_date (+) = n.start_date
292 and n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
293 group by n.start_date) iset, '||l_period_type||' cal
294 where cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
295 and cal.start_date = iset.start_date(+)
296 union all
297 select
298 cal.name cal_name,
299 cal.start_date cal_start_date,
300 -c_out,
301 -p_out,
302 0 c_act,
303 0 p_act
304 from
305 (select
306 n.start_date,
307 sum(case when (n.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
308 and i.report_date = least(n.end_date, &BIS_CURRENT_ASOF_DATE))
309 then out_amt else null end) c_out,
310 lag(sum(case when (n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE
311 and i.report_date = least(n.end_date, &BIS_PREVIOUS_ASOF_DATE))
312 then out_amt else null end), :ISC_LAG) over (order by n.start_date) p_out
313 from
314 (
315 select n.start_date,
316 n.report_date ,
317 sum(f.fulfill_out_f_ee_amt_'||l_curr_suffix||') out_amt
318 from isc_sam_005_mv f, -- fulfilled
319 (select /*+ NO_MERGE */ n.time_id,
320 n.record_type_id,
321 n.period_type_id,
322 n.report_date,
323 cal.start_date,
324 cal.end_date
325 from '||l_period_type||' cal,
326 fii_time_rpt_struct_v n
327 where cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
328 and n.report_date in (least(cal.end_date, &BIS_CURRENT_ASOF_DATE), &BIS_PREVIOUS_ASOF_DATE)
329 and n.report_date between cal.start_date and cal.end_date
330 and bitand(n.record_type_id, 1143) = n.record_type_id) n
331 where f.time_id = n.time_id
332 and f.agg_level = :ISC_AGG_LEVEL
333 '||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
334 group by n.start_date, n.report_date) i, '||l_period_type||' n
335 where i.start_date (+) = n.start_date
336 and n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
337 group by n.start_date) iset, '||l_period_type||' cal
338 where cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
339 and cal.start_date = iset.start_date(+)
340 union all
341 select
342 cal.name cal_name,
343 cal.start_date cal_start_date,
344 c_out,
345 p_out,
346 0 c_act,
347 0 p_act
348 from
349 (select
350 n.start_date,
351 sum(case when (n.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
352 and i.report_date = least(n.end_date, &BIS_CURRENT_ASOF_DATE))
353 then out_amt else null end) c_out,
354 lag(sum(case when (n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE
355 and i.report_date = least(n.end_date, &BIS_PREVIOUS_ASOF_DATE))
356 then out_amt else null end), :ISC_LAG) over (order by n.start_date) p_out
357 from
358 (
359 select n.start_date,
360 n.report_date ,
361 sum(f.fulfill_out_f_ee_amt_'||l_curr_suffix||') out_amt
362 from isc_sam_004_mv f, -- effective end
363 (select /*+ NO_MERGE */ n.time_id,
364 n.record_type_id,
365 n.period_type_id,
366 n.report_date,
367 cal.start_date,
368 cal.end_date
369 from '||l_period_type||' cal,
370 fii_time_rpt_struct_v n
371 where cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
372 and n.report_date in (least(cal.end_date, &BIS_CURRENT_ASOF_DATE), &BIS_PREVIOUS_ASOF_DATE)
373 and n.report_date between cal.start_date and cal.end_date
374 and bitand(n.record_type_id, 1143) = n.record_type_id) n
375 where f.time_id = n.time_id
376 and f.agg_level = :ISC_AGG_LEVEL
377 '||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
378 group by n.start_date, n.report_date) i, '||l_period_type||' n
379 where i.start_date (+) = n.start_date
380 and n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
381 group by n.start_date) iset, '||l_period_type||' cal
382 where cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
383 and cal.start_date = iset.start_date(+)
384 )
385 group by cal_name, cal_start_date) uset
386 order by cal_start_date
387 ';
388
389 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
390 x_custom_output := bis_query_attributes_tbl();
391
392 l_custom_rec.attribute_name := ':ISC_AGG_LEVEL';
393 l_custom_rec.attribute_value := l_agg_level;
394 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
395 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
396 x_custom_output.EXTEND;
397 x_custom_output(x_custom_output.count) := l_custom_rec;
398
399 l_custom_rec.attribute_name := ':ISC_LAG';
400 l_custom_rec.attribute_value := l_lag;
401 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
402 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
403 x_custom_output.EXTEND;
404 x_custom_output(x_custom_output.count) := l_custom_rec;
405
406 l_custom_rec.attribute_name := ':ISC_SG';
407 l_custom_rec.attribute_value := to_char(l_sg_sg);
408 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
409 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
410 x_custom_output.extend;
411 x_custom_output(x_custom_output.count) := l_custom_rec;
412
413 l_custom_rec.attribute_name := ':ISC_RES';
414 l_custom_rec.attribute_value := to_char(l_sg_res);
415 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
416 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
417 x_custom_output.extend;
418 x_custom_output(x_custom_output.count) := l_custom_rec;
419
420 x_custom_sql := l_query;
421
422 end get_sql;
423
424 end isc_dbi_sam_out_trend_pkg;
[Home] [Help]