[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_SAM_PRF_COM_PKG
Source
1 package body isc_dbi_sam_prf_com_pkg as
2 /* $Header: ISCRGCCB.pls 120.6 2006/02/20 14:14:40 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_view_by varchar2(32000);
9 l_sgid varchar2(32000);
10 l_agree varchar2(32000);
11 l_class varchar2(32000);
12 l_cust varchar2(32000);
13 l_curr varchar2(32000);
14 l_period_type varchar2(32000);
15 l_curr_suffix varchar2(32000);
16 l_period_str varchar2(32000);
17 l_sg_sg number;
18 l_sg_res number;
19 l_sg_where varchar2(32000);
20 l_agree_where varchar2(32000);
21 l_class_where varchar2(32000);
22 l_cust_where varchar2(32000);
23 l_agree_needed boolean;
24 l_class_needed boolean;
25 l_cust_needed boolean;
26 l_agg_level number;
27 l_sg_drill_str varchar2(32000);
28 l_class_drill_str varchar2(32000);
29 l_col_drill_str varchar2(32000);
30 l_viewby_col_str varchar2(32000);
31 l_viewby_select_str varchar2(32000);
32 l_viewbyid_select_str varchar2(32000);
33 l_dim_join_str varchar2(32000);
34 l_query varchar2(32000);
35 l_custom_rec bis_query_attributes;
36
37 begin
38
39 -- Get all necessary parameters from PMV
40 for i in 1..p_param.count loop
41
42 if (p_param(i).parameter_name = 'VIEW_BY') then
43 l_view_by := p_param(i).parameter_value;
44 end if;
45
46 if (p_param(i).parameter_name = 'ORGANIZATION+JTF_ORG_SALES_GROUP') then
47 l_sgid := p_param(i).parameter_id;
48 end if;
49
50 if (p_param(i).parameter_name = 'ISC_AGREEMENT_TYPE+ISC_AGREEMENT_TYPE') then
51 l_agree := p_param(i).parameter_id;
52 end if;
53
54 if (p_param(i).parameter_name = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS') then
55 l_class := p_param(i).parameter_id;
56 end if;
57
58 if (p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS') then
59 l_cust := p_param(i).parameter_id;
60 end if;
61
62 if (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES') then
63 l_curr := p_param(i).parameter_id;
64 end if;
65
66 if (p_param(i).parameter_name = 'PERIOD_TYPE') then
67 l_period_type := p_param(i).parameter_value;
68 end if;
69
70 end loop;
71
72 if (l_curr = '''FII_GLOBAL1''') then
73 l_curr_suffix := 'g';
74 else -- (l_curr = '''FII_GLOBAL2''')
75 l_curr_suffix := 'g1';
76 end if;
77
78 if (l_period_type = 'FII_TIME_ENT_YEAR') then
79 l_period_str := 'yr';
80 elsif (l_period_type = 'FII_TIME_ENT_QTR') then
81 l_period_str := 'qr';
82 elsif (l_period_type = 'FII_TIME_ENT_PERIOD') then
83 l_period_str := 'pd';
84 else -- (l_period_type = 'FII_TIME_WEEK')
85 l_period_str := 'wk';
86 end if;
87
88 -- Figure out where clauses
89 l_sg_sg := to_number(replace(substr(l_sgid,instr(l_sgid,'.') + 1),''''));
90 l_sg_res := to_number(replace(substr(l_sgid,1,instr(l_sgid,'.') - 1),''''));
91
92 if (l_sg_res is null) then -- when a sales group is chosen
93 l_col_drill_str := 'null ISC_ATTRIBUTE_7,
94 null ISC_ATTRIBUTE_8,
95 null ISC_ATTRIBUTE_9,
96 null ISC_ATTRIBUTE_10,';
97 if (l_view_by = 'ORGANIZATION+JTF_ORG_SALES_GROUP') then
98 l_sg_where := ' and f.parent_grp_id = (&ORGANIZATION+JTF_ORG_SALES_GROUP) and f.grp_marker <> ''TOP GROUP'''; -- exclude the top groups when VB=SG
99 else -- other view bys
100 l_sg_where := ' and f.sales_grp_id = (&ORGANIZATION+JTF_ORG_SALES_GROUP) and f.resource_id is null';
101 end if;
102 else -- when the LOV parameter is a Salesrep (no need to go through the SG hierarchy)
103 l_col_drill_str := '''pFunctionName=ISC_DBI_SAM_TOP_AGR&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' ISC_ATTRIBUTE_7,
104 ''pFunctionName=ISC_DBI_SAM_EXG_AGR&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' ISC_ATTRIBUTE_8,
105 ''pFunctionName=ISC_DBI_SAM_TRM_AGR&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' ISC_ATTRIBUTE_9,
106 ''pFunctionName=ISC_DBI_SAM_EXD_AGR&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' ISC_ATTRIBUTE_10,';
107 l_sg_where := ' and f.sales_grp_id = :ISC_SG and f.resource_id = :ISC_RES';
108 end if;
109
110 if (l_agree is null) then
111 l_agree_where := '';
112 else
113 l_agree_where := ' and f.agreement_type_id in (&ISC_AGREEMENT_TYPE+ISC_AGREEMENT_TYPE)';
114 end if;
115
116 if (l_class is null) then
117 l_class_where := '';
118 else
119 l_class_where := ' and f.class_code in (&FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS)';
120 end if;
121
122 if (l_cust is null) then
123 l_cust_where := '';
124 else
125 l_cust_where := ' and f.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
126 end if;
127
128 -- Figure out agg_level flag value
129 l_agree_needed := false;
130 l_class_needed := false;
131 l_cust_needed := false;
132
133 if (l_view_by = 'ISC_AGREEMENT_TYPE+ISC_AGREEMENT_TYPE' or l_agree is not null) then
134 l_agree_needed := true;
135 end if;
136
137 if (l_view_by = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS' or l_class is not null) then
138 l_class_needed := true;
139 end if;
140
141 if (l_view_by = 'CUSTOMER+FII_CUSTOMERS' or l_cust is not null) then
142 l_cust_needed := true;
143 end if;
144
145 case
146 when ( l_agree_needed and l_class_needed and l_cust_needed) then l_agg_level := 0;
147 when ( l_agree_needed and l_class_needed and not l_cust_needed) then l_agg_level := 2;
148 when ( l_agree_needed and not l_class_needed and l_cust_needed) then l_agg_level := 0;
149 when (not l_agree_needed and l_class_needed and l_cust_needed) then l_agg_level := 1;
150 when ( l_agree_needed and not l_class_needed and not l_cust_needed) then l_agg_level := 4;
151 when (not l_agree_needed and l_class_needed and not l_cust_needed) then l_agg_level := 3;
152 when (not l_agree_needed and not l_class_needed and l_cust_needed) then l_agg_level := 1;
153 when (not l_agree_needed and not l_class_needed and not l_cust_needed) then l_agg_level := 5;
154 end case;
155
156 -- Figure out pieces of strings to fill in the query
157 if (l_view_by = 'ORGANIZATION+JTF_ORG_SALES_GROUP') then
158 l_sg_drill_str := 'decode(oset.resource_id, null, ''pFunctionName=ISC_DBI_SAM_PRF_COM&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'', null)';
159 l_class_drill_str := 'null';
160 l_col_drill_str := 'decode(oset.resource_id, null, null, ''pFunctionName=ISC_DBI_SAM_TOP_AGR&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'') ISC_ATTRIBUTE_7,
161 decode(oset.resource_id, null, null, ''pFunctionName=ISC_DBI_SAM_EXG_AGR&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'') ISC_ATTRIBUTE_8,
162 decode(oset.resource_id, null, null, ''pFunctionName=ISC_DBI_SAM_TRM_AGR&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'') ISC_ATTRIBUTE_9,
163 decode(oset.resource_id, null, null, ''pFunctionName=ISC_DBI_SAM_EXD_AGR&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'') ISC_ATTRIBUTE_10,';
164 l_viewby_col_str := 'resource_id, sales_grp_id';
165 l_viewby_select_str := 'decode(oset.resource_id, null, g.group_name, r.resource_name)';
166 l_viewbyid_select_str := 'decode(oset.resource_id, null, to_char(oset.sales_grp_id), oset.resource_id||''.''||oset.sales_grp_id)';
167 l_dim_join_str := ' jtf_rs_groups_vl g, jtf_rs_resource_extns_vl r
168 where oset.sales_grp_id = g.group_id
169 and oset.resource_id = r.resource_id (+) ';
170 elsif (l_view_by = 'ISC_AGREEMENT_TYPE+ISC_AGREEMENT_TYPE') then
171 l_sg_drill_str := 'null';
172 l_class_drill_str := 'null';
173 l_viewby_col_str := 'agreement_type_id';
174 l_viewby_select_str := 'v.value';
175 l_viewbyid_select_str := 'v.id';
176 l_dim_join_str := ' isc_agreement_type_v v
177 where oset.agreement_type_id = v.id ';
178 elsif (l_view_by = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS') then
179 l_sg_drill_str := 'null';
180 l_class_drill_str := '''pFunctionName=ISC_DBI_SAM_PRF_COM&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMERS&pParamIds=Y''';
181 l_viewby_col_str := 'class_code';
182 l_viewby_select_str := 'v.value';
183 l_viewbyid_select_str := 'v.id';
184 l_dim_join_str := ' fii_partner_mkt_class_v v
185 where oset.class_code = v.id ';
186 else -- (l_view_by = 'CUSTOMER+FII_CUSTOMERS')
187 l_sg_drill_str := 'null';
188 l_class_drill_str := 'null';
189 l_viewby_col_str := 'customer_id';
190 l_viewby_select_str := 'v.value';
191 l_viewbyid_select_str := 'v.id';
192 l_dim_join_str := ' fii_customers_v v
193 where oset.customer_id = v.id ';
194 end if;
195
196 l_query := '
197 select '||l_viewby_select_str||' VIEWBY,
198 '||l_viewbyid_select_str||' VIEWBYID,
199 '||l_sg_drill_str||' ISC_ATTRIBUTE_1,
200 '||l_class_drill_str||' ISC_ATTRIBUTE_6,
201 '||l_col_drill_str||'
202 ISC_MEASURE_5,ISC_MEASURE_6,ISC_MEASURE_7,ISC_MEASURE_8,ISC_MEASURE_9,ISC_MEASURE_10,
203 ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13,ISC_MEASURE_14,ISC_MEASURE_15,ISC_MEASURE_16,ISC_MEASURE_17,ISC_MEASURE_18,ISC_MEASURE_19,ISC_MEASURE_20,
204 ISC_MEASURE_21,ISC_MEASURE_22,ISC_MEASURE_23,ISC_MEASURE_24,ISC_MEASURE_25,ISC_MEASURE_26,ISC_MEASURE_27,ISC_MEASURE_28,ISC_MEASURE_29,ISC_MEASURE_30,
205 ISC_MEASURE_32,ISC_MEASURE_33
206 from (select '||l_viewby_col_str||',
207 (rank() over (&ORDER_BY_CLAUSE nulls last, '||l_viewby_col_str||'))-1 rnk,
208 ISC_MEASURE_5,ISC_MEASURE_6,ISC_MEASURE_7,ISC_MEASURE_8,ISC_MEASURE_9,ISC_MEASURE_10,
209 ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13,ISC_MEASURE_14,ISC_MEASURE_15,ISC_MEASURE_16,ISC_MEASURE_17,ISC_MEASURE_18,ISC_MEASURE_19,ISC_MEASURE_20,
210 ISC_MEASURE_21,ISC_MEASURE_22,ISC_MEASURE_23,ISC_MEASURE_24,ISC_MEASURE_25,ISC_MEASURE_26,ISC_MEASURE_27,ISC_MEASURE_28,ISC_MEASURE_29,ISC_MEASURE_30,
211 ISC_MEASURE_32,ISC_MEASURE_33
212 from (select '||l_viewby_col_str||',
213 c_ta ISC_MEASURE_5,
214 c_to ISC_MEASURE_6,
215 c_exg ISC_MEASURE_7,
216 c_ego ISC_MEASURE_8,
217 c_trm ISC_MEASURE_9,
218 c_tmo ISC_MEASURE_10,
219 c_exp ISC_MEASURE_11,
220 c_epo ISC_MEASURE_12,
221 sum(c_ta)over() ISC_MEASURE_13,
222 sum(c_to)over() ISC_MEASURE_14,
223 sum(c_exg)over() ISC_MEASURE_15,
224 sum(c_ego)over() ISC_MEASURE_16,
225 sum(c_trm)over() ISC_MEASURE_17,
226 sum(c_tmo)over() ISC_MEASURE_18,
227 sum(c_exp)over() ISC_MEASURE_19,
228 sum(c_epo)over() ISC_MEASURE_20,
229 c_to ISC_MEASURE_21,
230 c_ta-c_to ISC_MEASURE_22,
231 c_ego ISC_MEASURE_23,
232 c_exg-c_ego ISC_MEASURE_24,
233 c_tmo ISC_MEASURE_25,
234 c_trm-c_tmo ISC_MEASURE_26,
235 c_epo ISC_MEASURE_27,
236 c_exp-c_epo ISC_MEASURE_28,
237 c_ego ISC_MEASURE_29,
238 p_ego ISC_MEASURE_30,
239 sum(c_ego)over() ISC_MEASURE_32,
240 sum(p_ego)over() ISC_MEASURE_33
241 from (
242 select '||l_viewby_col_str||',
243 sum(c_ta1)+sum(c_ta2)-sum(c_ta3) c_ta,
244 sum(c_ta1)+sum(c_ta2)-sum(c_ta3)-sum(c_to1)+sum(c_to2) c_to,
245 sum(p_ta1)+sum(p_ta2)-sum(p_ta3)-sum(p_to1)+sum(p_to2) p_to,
246 sum(c_exg1)-sum(c_exg2)-sum(c_exg3)+sum(c_exg4)-sum(c_exg5) c_exg,
247 sum(c_exg1)-sum(c_exg2)-sum(c_exg3)+sum(c_exg4)-sum(c_exg5)-sum(c_ego1)+sum(c_ego2)+sum(c_ego3)-sum(c_ego4)+sum(c_ego5) c_ego,
248 sum(p_exg1)-sum(p_exg2)-sum(p_exg3)+sum(p_exg4)-sum(p_exg5)-sum(p_ego1)+sum(p_ego2)+sum(p_ego3)-sum(p_ego4)+sum(p_ego5) p_ego,
249 sum(c_trm1) c_trm,
250 sum(c_trm1)-sum(c_tmo1)-sum(c_tmo2) c_tmo,
251 sum(c_exp1)-sum(c_exp2) c_exp,
252 sum(c_exp1)-sum(c_exp2)-sum(c_epo1)+sum(c_epo2)-sum(c_epo3) c_epo
253 from (
254 select '||l_viewby_col_str||',
255 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) c_ta1,
256 0 c_ta2, 0 c_ta3,
257 0 c_to1, 0 c_to2,
258 decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) p_ta1,
259 0 p_ta2, 0 p_ta3, 0 p_to1, 0 p_to2,
260 0 c_exg1, 0 c_exg2, 0 c_exg3, 0 c_exg4, 0 c_exg5,
261 0 c_ego1, 0 c_ego2, 0 c_ego3, 0 c_ego4, 0 c_ego5,
262 0 p_exg1, 0 p_exg2, 0 p_exg3, 0 p_exg4, 0 p_exg5,
263 0 p_ego1, 0 p_ego2, 0 p_ego3, 0 p_ego4, 0 p_ego5,
264 0 c_trm1,
265 0 c_tmo1, 0 c_tmo2,
266 0 c_exp1, 0 c_exp2,
267 0 c_epo1, 0 c_epo2, 0 c_epo3
268 from isc_sam_003_mv f, -- active balance
269 fii_time_day t
270 where t.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
271 and t.ent_year_id = f.ent_year_id
272 and f.agg_level = :ISC_AGG_LEVEL
273 '||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
274 union all
275 select '||l_viewby_col_str||',
276 0 c_ta1,
277 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) c_ta2,
278 0 c_ta3,
279 0 c_to1, 0 c_to2,
280 0 p_ta1,
281 decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) p_ta2,
282 0 p_ta3, 0 p_to1, 0 p_to2,
283 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
284 0 c_trm1,
285 0 c_tmo1, 0 c_tmo2,
286 0 c_exp1, 0 c_exp2,
287 0 c_epo1, 0 c_epo2, 0 c_epo3
288 from isc_sam_001_mv f, -- activation
289 fii_time_rpt_struct_v t
290 where f.time_id = t.time_id
291 and t.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
292 and bitand(t.record_type_id, 119) = t.record_type_id
293 and f.agg_level = :ISC_AGG_LEVEL
294 '||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
295 union all
296 select '||l_viewby_col_str||',
297 0 c_ta1, 0 c_ta2,
301 decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) p_ta3,
298 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) c_ta3,
299 0 c_to1, 0 c_to2,
300 0 p_ta1, 0 p_ta2,
302 0 p_to1, 0 p_to2,
303 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
304 0 c_trm1,
305 0 c_tmo1, 0 c_tmo2,
306 0 c_exp1, 0 c_exp2,
307 0 c_epo1, 0 c_epo2, 0 c_epo3
308 from isc_sam_004_mv f, -- effective end
309 fii_time_rpt_struct_v t
310 where f.time_id = t.time_id
311 and t.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
312 and bitand(t.record_type_id, 119) = t.record_type_id
313 and f.agg_level = :ISC_AGG_LEVEL
314 '||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
315 union all
316 select '||l_viewby_col_str||',
317 0 c_ta1, 0 c_ta2, 0 c_ta3,
318 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.fulfill_out_f_ee_amt_'||l_curr_suffix||',0), 0) c_to1,
319 0 c_to2,
320 0 p_ta1, 0 p_ta2, 0 p_ta3,
321 decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.fulfill_out_f_ee_amt_'||l_curr_suffix||',0), 0) p_to1,
322 0 p_to2,
323 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
324 0 c_trm1,
325 0 c_tmo1, 0 c_tmo2,
326 0 c_exp1, 0 c_exp2,
327 0 c_epo1, 0 c_epo2, 0 c_epo3
328 from isc_sam_005_mv f, -- fulfilled
329 fii_time_rpt_struct_v t
330 where f.time_id = t.time_id
331 and t.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
332 and bitand(t.record_type_id, 1143) = t.record_type_id
333 and f.agg_level = :ISC_AGG_LEVEL
334 '||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
335 union all
336 select '||l_viewby_col_str||',
337 0 c_ta1, 0 c_ta2, 0 c_ta3,
338 0 c_to1,
339 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.fulfill_out_f_ee_amt_'||l_curr_suffix||',0), 0) c_to2,
340 0 p_ta1, 0 p_ta2, 0 p_ta3, 0 p_to1,
341 decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.fulfill_out_f_ee_amt_'||l_curr_suffix||',0), 0) p_to2,
342 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
343 0 c_trm1,
344 0 c_tmo1, 0 c_tmo2,
345 0 c_exp1, 0 c_exp2,
346 0 c_epo1, 0 c_epo2, 0 c_epo3
347 from isc_sam_004_mv f, -- effective end
348 fii_time_rpt_struct_v t
349 where f.time_id = t.time_id
350 and t.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
351 and bitand(t.record_type_id, 1143) = t.record_type_id
352 and f.agg_level = :ISC_AGG_LEVEL
353 '||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
354 union all
355 select '||l_viewby_col_str||',
356 0 c_ta1, 0 c_ta2, 0 c_ta3,
357 0 c_to1, 0 c_to2,
358 0 p_ta1, 0 p_ta2, 0 p_ta3, 0 p_to1, 0 p_to2,
362 0 c_exg3, 0 c_exg4, 0 c_exg5,
359 decode(t.report_date, &BIS_CURRENT_EFFECTIVE_END_DATE, nvl(f.commit_calc1_t'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_exg1,
360 decode(t.report_date, &BIS_CURRENT_ASOF_DATE,
361 nvl(f.commit_calc1_t'||l_period_str||'_amt_'||l_curr_suffix||',0)-nvl(f.commit_t_e_t'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_exg2,
363 decode(t.report_date, &BIS_CURRENT_EFFECTIVE_END_DATE, nvl(f.fulfill_out_calc2_t'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_ego1,
364 decode(t.report_date, &BIS_CURRENT_ASOF_DATE,
365 nvl(f.fulfill_out_calc2_t'||l_period_str||'_amt_'||l_curr_suffix||',0)-nvl(f.fulfill_out_f_t_e_t'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_ego2,
366 0 c_ego3, 0 c_ego4, 0 c_ego5,
367 decode(t.report_date, &BIS_PREVIOUS_EFFECTIVE_END_DATE, nvl(f.commit_calc1_t'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) p_exg1,
368 decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE,
369 nvl(f.commit_calc1_t'||l_period_str||'_amt_'||l_curr_suffix||',0)-nvl(f.commit_t_e_t'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) p_exg2,
370 0 p_exg3, 0 p_exg4, 0 p_exg5,
371 decode(t.report_date, &BIS_PREVIOUS_EFFECTIVE_END_DATE, nvl(f.fulfill_out_calc2_t'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) p_ego1,
372 decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE,
373 nvl(f.fulfill_out_calc2_t'||l_period_str||'_amt_'||l_curr_suffix||',0)-nvl(f.fulfill_out_f_t_e_t'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) p_ego2,
374 0 p_ego3, 0 p_ego4, 0 p_ego5,
375 0 c_trm1,
376 0 c_tmo1, 0 c_tmo2,
377 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_e_t_amt_'||l_curr_suffix||',0), 0) c_exp1,
378 0 c_exp2,
379 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.fulfill_out_f_e_t_amt_'||l_curr_suffix||',0), 0) c_epo1,
380 0 c_epo2, 0 c_epo3
381 from isc_sam_000_mv f, -- expiration
382 fii_time_rpt_struct_v t
383 where f.time_id = t.time_id
384 and t.report_date in (&BIS_CURRENT_EFFECTIVE_END_DATE,&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_EFFECTIVE_END_DATE,&BIS_PREVIOUS_ASOF_DATE)
385 and bitand(t.record_type_id, &BIS_NESTED_PATTERN) = t.record_type_id
386 and f.agg_level = :ISC_AGG_LEVEL
387 '||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
388 union all
389 select '||l_viewby_col_str||',
390 0 c_ta1, 0 c_ta2, 0 c_ta3,
391 0 c_to1, 0 c_to2,
392 0 p_ta1, 0 p_ta2, 0 p_ta3, 0 p_to1, 0 p_to2,
393 0 c_exg1, 0 c_exg2,
394 decode(t.report_date, &BIS_CURRENT_EFFECTIVE_END_DATE, nvl(f.commit_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_exg3,
395 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_exg4,
396 0 c_exg5,
397 0 c_ego1, 0 c_ego2, 0 c_ego3, 0 c_ego4, 0 c_ego5,
398 0 p_exg1, 0 p_exg2,
399 decode(t.report_date, &BIS_PREVIOUS_EFFECTIVE_END_DATE, nvl(f.commit_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) p_exg3,
400 decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.commit_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) p_exg4,
401 0 p_exg5,
402 0 p_ego1, 0 p_ego2, 0 p_ego3, 0 p_ego4, 0 p_ego5,
403 0 c_trm1,
404 0 c_tmo1, 0 c_tmo2,
405 0 c_exp1, 0 c_exp2,
406 0 c_epo1, 0 c_epo2, 0 c_epo3
407 from isc_sam_001_mv f, -- activation
408 fii_time_rpt_struct_v t
409 where f.time_id = t.time_id
410 and t.report_date in (&BIS_CURRENT_EFFECTIVE_END_DATE,&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_EFFECTIVE_END_DATE,&BIS_PREVIOUS_ASOF_DATE)
411 and bitand(t.record_type_id, &BIS_NESTED_PATTERN) = t.record_type_id
412 and f.agg_level = :ISC_AGG_LEVEL
413 '||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
414 union all
415 select '||l_viewby_col_str||',
416 0 c_ta1, 0 c_ta2, 0 c_ta3,
417 0 c_to1, 0 c_to2,
418 0 p_ta1, 0 p_ta2, 0 p_ta3, 0 p_to1, 0 p_to2,
419 0 c_exg1, 0 c_exg2, 0 c_exg3, 0 c_exg4,
420 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_t_e_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_exg5,
421 0 c_ego1, 0 c_ego2, 0 c_ego3, 0 c_ego4,
422 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.fulfill_out_f_t_e_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_ego5,
423 0 p_exg1, 0 p_exg2, 0 p_exg3, 0 p_exg4,
424 decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.commit_t_e_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) p_exg5,
425 0 p_ego1, 0 p_ego2, 0 p_ego3, 0 p_ego4,
426 decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.fulfill_out_f_t_e_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) p_ego5,
427 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) c_trm1,
428 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.fulfill_out_f_t_amt_'||l_curr_suffix||',0), 0) c_tmo1,
429 0 c_tmo2,
430 0 c_exp1,
431 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_e_t_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_exp2,
432 0 c_epo1,
433 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.fulfill_out_ef_t_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_epo2,
434 0 c_epo3
435 from isc_sam_002_mv f, -- termination
436 fii_time_rpt_struct_v t
437 where f.time_id = t.time_id
438 and t.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
439 and bitand(t.record_type_id, &BIS_NESTED_PATTERN) = t.record_type_id
440 and f.agg_level = :ISC_AGG_LEVEL
441 '||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
442 union all
443 select '||l_viewby_col_str||',
444 0 c_ta1, 0 c_ta2, 0 c_ta3,
445 0 c_to1, 0 c_to2,
446 0 p_ta1, 0 p_ta2, 0 p_ta3, 0 p_to1, 0 p_to2,
447 0 c_exg1, 0 c_exg2, 0 c_exg3, 0 c_exg4, 0 c_exg5,
448 0 c_ego1, 0 c_ego2,
449 decode(t.report_date, &BIS_CURRENT_EFFECTIVE_END_DATE, nvl(f.fulfill_out_f_et_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_ego3,
450 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.fulfill_out_f_et_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_ego4,
451 0 c_ego5,
452 0 p_exg1, 0 p_exg2, 0 p_exg3, 0 p_exg4, 0 p_exg5,
453 0 p_ego1, 0 p_ego2,
454 decode(t.report_date, &BIS_PREVIOUS_EFFECTIVE_END_DATE, nvl(f.fulfill_out_f_et_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) p_ego3,
455 decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.fulfill_out_f_et_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) p_ego4,
456 0 p_ego5,
457 0 c_trm1,
458 0 c_tmo1,
459 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.fulfill_out_t_f_t'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_tmo2,
460 0 c_exp1, 0 c_exp2,
461 0 c_epo1, 0 c_epo2,
462 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.fulfill_out_e_ft_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_epo3
463 from isc_sam_005_mv f, -- fulfilled
464 fii_time_rpt_struct_v t
465 where f.time_id = t.time_id
466 and t.report_date in (&BIS_CURRENT_EFFECTIVE_END_DATE,&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_EFFECTIVE_END_DATE,&BIS_PREVIOUS_ASOF_DATE)
467 and bitand(t.record_type_id, &BIS_NESTED_PATTERN) = t.record_type_id
468 and f.agg_level = :ISC_AGG_LEVEL
469 '||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
470 ) oset
471 group by '||l_viewby_col_str||'
472 ) ) where isc_measure_5 <> 0
473 or isc_measure_6 <> 0
474 or isc_measure_7 <> 0
475 or isc_measure_8 <> 0
476 or isc_measure_9 <> 0
477 or isc_measure_10 <> 0
478 or isc_measure_11 <> 0
479 or isc_measure_12 <> 0
480 ) oset,
481 '||l_dim_join_str||'
482 and ((oset.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
483 order by oset.rnk
484 ';
485
486 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
487 x_custom_output := bis_query_attributes_tbl();
488
489 l_custom_rec.attribute_name := ':ISC_AGG_LEVEL';
490 l_custom_rec.attribute_value := l_agg_level;
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_SG';
497 l_custom_rec.attribute_value := to_char(l_sg_sg);
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;
501 x_custom_output(x_custom_output.count) := l_custom_rec;
502
503 l_custom_rec.attribute_name := ':ISC_RES';
504 l_custom_rec.attribute_value := to_char(l_sg_res);
505 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
506 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
507 x_custom_output.extend;
508 x_custom_output(x_custom_output.count) := l_custom_rec;
509
510 x_custom_sql := l_query;
511
512
513 end get_sql;
514
515 end isc_dbi_sam_prf_com_pkg ;
516