[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_SAM_AGR_SUM_PKG
Source
1 package body isc_dbi_sam_agr_sum_pkg as
2 /* $Header: ISCRGCBB.pls 120.8 2005/12/15 16:54:32 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 null ISC_ATTRIBUTE_11,
98 null ISC_ATTRIBUTE_12,';
99 if (l_view_by = 'ORGANIZATION+JTF_ORG_SALES_GROUP') then
100 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
101 else -- other view bys
102 l_sg_where := ' and f.sales_grp_id = (&ORGANIZATION+JTF_ORG_SALES_GROUP) and f.resource_id is null';
103 end if;
104 else -- when the LOV parameter is a Salesrep (no need to go through the SG hierarchy)
105 l_col_drill_str := '''pFunctionName=ISC_DBI_SAM_NEW_AGR&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' ISC_ATTRIBUTE_7,
106 ''pFunctionName=ISC_DBI_SAM_NEW_AGR&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' ISC_ATTRIBUTE_8,
107 ''pFunctionName=ISC_DBI_SAM_EXD_AGR&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' ISC_ATTRIBUTE_9,
108 ''pFunctionName=ISC_DBI_SAM_TRM_AGR&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' ISC_ATTRIBUTE_10,
109 ''pFunctionName=ISC_DBI_SAM_TOP_AGR&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' ISC_ATTRIBUTE_11,
110 ''pFunctionName=ISC_DBI_SAM_EXG_AGR&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' ISC_ATTRIBUTE_12,';
111 l_sg_where := ' and f.sales_grp_id = :ISC_SG and f.resource_id = :ISC_RES';
112 end if;
113
114 if (l_agree is null) then
115 l_agree_where := '';
116 else
117 l_agree_where := ' and f.agreement_type_id in (&ISC_AGREEMENT_TYPE+ISC_AGREEMENT_TYPE)';
118 end if;
119
120 if (l_class is null) then
121 l_class_where := '';
122 else
123 l_class_where := ' and f.class_code in (&FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS)';
124 end if;
125
126 if (l_cust is null) then
127 l_cust_where := '';
128 else
129 l_cust_where := ' and f.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
130 end if;
131
132 -- Figure out agg_level flag value
133 l_agree_needed := false;
134 l_class_needed := false;
135 l_cust_needed := false;
136
137 if (l_view_by = 'ISC_AGREEMENT_TYPE+ISC_AGREEMENT_TYPE' or l_agree is not null) then
138 l_agree_needed := true;
139 end if;
140
141 if (l_view_by = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS' or l_class is not null) then
142 l_class_needed := true;
143 end if;
144
145 if (l_view_by = 'CUSTOMER+FII_CUSTOMERS' or l_cust is not null) then
146 l_cust_needed := true;
147 end if;
148
149 case
150 when ( l_agree_needed and l_class_needed and l_cust_needed) then l_agg_level := 0;
151 when ( l_agree_needed and l_class_needed and not l_cust_needed) then l_agg_level := 2;
152 when ( l_agree_needed and not l_class_needed and l_cust_needed) then l_agg_level := 0;
153 when (not l_agree_needed and l_class_needed and l_cust_needed) then l_agg_level := 1;
154 when ( l_agree_needed and not l_class_needed and not l_cust_needed) then l_agg_level := 4;
155 when (not l_agree_needed and l_class_needed and not l_cust_needed) then l_agg_level := 3;
156 when (not l_agree_needed and not l_class_needed and l_cust_needed) then l_agg_level := 1;
157 when (not l_agree_needed and not l_class_needed and not l_cust_needed) then l_agg_level := 5;
158 end case;
159
160 -- Figure out pieces of strings to fill in the query
161 if (l_view_by = 'ORGANIZATION+JTF_ORG_SALES_GROUP') then
162 l_sg_drill_str := 'decode(oset.resource_id, null, ''pFunctionName=ISC_DBI_SAM_AGR_SUM&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'', null)';
163 l_class_drill_str := 'null';
164 l_col_drill_str := 'decode(oset.resource_id, null, null, ''pFunctionName=ISC_DBI_SAM_NEW_AGR&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'') ISC_ATTRIBUTE_7,
165 decode(oset.resource_id, null, null, ''pFunctionName=ISC_DBI_SAM_NEW_AGR&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'') ISC_ATTRIBUTE_8,
166 decode(oset.resource_id, null, null, ''pFunctionName=ISC_DBI_SAM_EXD_AGR&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'') ISC_ATTRIBUTE_9,
167 decode(oset.resource_id, null, null, ''pFunctionName=ISC_DBI_SAM_TRM_AGR&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'') ISC_ATTRIBUTE_10,
168 decode(oset.resource_id, null, null, ''pFunctionName=ISC_DBI_SAM_TOP_AGR&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'') ISC_ATTRIBUTE_11,
169 decode(oset.resource_id, null, null, ''pFunctionName=ISC_DBI_SAM_EXG_AGR&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'') ISC_ATTRIBUTE_12,';
170 l_viewby_col_str := 'resource_id, sales_grp_id';
171 l_viewby_select_str := 'decode(oset.resource_id, null, g.group_name, r.resource_name)';
172 l_viewbyid_select_str := 'decode(oset.resource_id, null, to_char(oset.sales_grp_id), oset.resource_id||''.''||oset.sales_grp_id)';
173 l_dim_join_str := ' jtf_rs_groups_vl g, jtf_rs_resource_extns_vl r
174 where oset.sales_grp_id = g.group_id
175 and oset.resource_id = r.resource_id (+) ';
176
177 elsif (l_view_by = 'ISC_AGREEMENT_TYPE+ISC_AGREEMENT_TYPE') then
178 l_sg_drill_str := 'null';
179 l_class_drill_str := 'null';
180 l_viewby_col_str := 'agreement_type_id';
181 l_viewby_select_str := 'v.value';
182 l_viewbyid_select_str := 'v.id';
183 l_dim_join_str := ' isc_agreement_type_v v
184 where oset.agreement_type_id = v.id ';
185
186 elsif (l_view_by = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS') then
187 l_sg_drill_str := 'null';
188 l_class_drill_str := '''pFunctionName=ISC_DBI_SAM_AGR_SUM&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMERS&pParamIds=Y''';
189 l_viewby_col_str := 'class_code';
190 l_viewby_select_str := 'v.value';
191 l_viewbyid_select_str := 'v.id';
192 l_dim_join_str := ' fii_partner_mkt_class_v v
193 where oset.class_code = v.id ';
194
195 else -- (l_view_by = 'CUSTOMER+FII_CUSTOMERS')
196 l_sg_drill_str := 'null';
197 l_class_drill_str := 'null';
198 l_viewby_col_str := 'customer_id';
199 l_viewby_select_str := 'v.value';
200 l_viewbyid_select_str := 'v.id';
201 l_dim_join_str := ' fii_customers_v v
202 where oset.customer_id = v.id ';
203 end if;
204
205 l_query := '
206 select '||l_viewby_select_str||' VIEWBY,
207 '||l_viewbyid_select_str||' VIEWBYID,
208 '||l_sg_drill_str||' ISC_ATTRIBUTE_6,
209 '||l_col_drill_str||'
210 '||l_class_drill_str||' ISC_ATTRIBUTE_13,
211 ISC_MEASURE_7,ISC_MEASURE_8,ISC_MEASURE_9,ISC_MEASURE_10,ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13,ISC_MEASURE_14,ISC_MEASURE_15,
212 ISC_MEASURE_16,ISC_MEASURE_17,ISC_MEASURE_18,ISC_MEASURE_19,ISC_MEASURE_20,ISC_MEASURE_21,ISC_MEASURE_22,ISC_MEASURE_23,ISC_MEASURE_24,ISC_MEASURE_25,
213 ISC_MEASURE_26,ISC_MEASURE_27,ISC_MEASURE_28,ISC_MEASURE_29,ISC_MEASURE_30,ISC_MEASURE_31,ISC_MEASURE_32,ISC_MEASURE_33,ISC_MEASURE_34,ISC_MEASURE_35,
214 ISC_MEASURE_36,ISC_MEASURE_38,ISC_MEASURE_39,ISC_MEASURE_40,ISC_MEASURE_41,ISC_MEASURE_43,ISC_MEASURE_44,ISC_MEASURE_45,
215 ISC_MEASURE_46,ISC_MEASURE_48,ISC_MEASURE_49,ISC_MEASURE_50,ISC_MEASURE_51,ISC_MEASURE_53,ISC_MEASURE_54,ISC_MEASURE_55,
216 ISC_MEASURE_56,ISC_MEASURE_58,ISC_MEASURE_59,ISC_MEASURE_60,ISC_MEASURE_61,ISC_MEASURE_63,ISC_MEASURE_64
217 from (select '||l_viewby_col_str||',
218 (rank() over (&ORDER_BY_CLAUSE nulls last, '||l_viewby_col_str||'))-1 rnk,
219 ISC_MEASURE_7,ISC_MEASURE_8,ISC_MEASURE_9,ISC_MEASURE_10,ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13,ISC_MEASURE_14,ISC_MEASURE_15,
220 ISC_MEASURE_16,ISC_MEASURE_17,ISC_MEASURE_18,ISC_MEASURE_19,ISC_MEASURE_20,
221 ISC_MEASURE_21,ISC_MEASURE_22,ISC_MEASURE_23,ISC_MEASURE_24,ISC_MEASURE_25,
222 ISC_MEASURE_26,ISC_MEASURE_27,ISC_MEASURE_28,ISC_MEASURE_29,ISC_MEASURE_30,
223 ISC_MEASURE_31,ISC_MEASURE_32,ISC_MEASURE_33,ISC_MEASURE_34,ISC_MEASURE_35,
224 ISC_MEASURE_36,ISC_MEASURE_38,ISC_MEASURE_39,ISC_MEASURE_40,
225 ISC_MEASURE_41,ISC_MEASURE_43,ISC_MEASURE_44,ISC_MEASURE_45,
226 ISC_MEASURE_46,ISC_MEASURE_48,ISC_MEASURE_49,ISC_MEASURE_50,
227 ISC_MEASURE_51,ISC_MEASURE_53,ISC_MEASURE_54,ISC_MEASURE_55,
228 ISC_MEASURE_56,ISC_MEASURE_58,ISC_MEASURE_59,ISC_MEASURE_60,
229 ISC_MEASURE_61,ISC_MEASURE_63,ISC_MEASURE_64
230 from (select '||l_viewby_col_str||',
231 c_ba ISC_MEASURE_7,
232 (c_ba-p_ba)/decode(p_ba,0,null,abs(p_ba)) * 100 ISC_MEASURE_8,
233 c_nc ISC_MEASURE_9,
234 c_new ISC_MEASURE_10,
235 (c_new-p_new)/decode(p_new,0,null,abs(p_new)) * 100 ISC_MEASURE_11,
236 c_exp ISC_MEASURE_12,
237 (c_exp-p_exp)/decode(p_exp,0,null,abs(p_exp)) * 100 ISC_MEASURE_13,
238 c_trm ISC_MEASURE_14,
239 (c_trm-p_trm)/decode(p_trm,0,null,abs(p_trm)) * 100 ISC_MEASURE_15,
240 p_ta ISC_MEASURE_16,
241 c_ta ISC_MEASURE_17,
242 c_ta ISC_MEASURE_18,
243 (c_ta-p_ta)/decode(p_ta,0,null,abs(p_ta)) * 100 ISC_MEASURE_19,
244 c_exg ISC_MEASURE_20,
245 (c_exg-p_exg)/decode(p_exg,0,null,abs(p_exg)) * 100 ISC_MEASURE_21,
246 ct_ba ISC_MEASURE_22,
247 (ct_ba-pt_ba)/decode(pt_ba,0,null,abs(pt_ba)) * 100 ISC_MEASURE_23,
248 ct_nc ISC_MEASURE_24,
249 ct_new ISC_MEASURE_25,
250 (ct_new-pt_new)/decode(pt_new,0,null,abs(pt_new)) * 100 ISC_MEASURE_26,
251 ct_exp ISC_MEASURE_27,
252 (ct_exp-pt_exp)/decode(pt_exp,0,null,abs(pt_exp)) * 100 ISC_MEASURE_28,
253 ct_trm ISC_MEASURE_29,
254 (ct_trm-pt_trm)/decode(pt_trm,0,null,abs(pt_trm)) * 100 ISC_MEASURE_30,
255 ct_ta ISC_MEASURE_31,
256 (ct_ta-pt_ta)/decode(pt_ta,0,null,abs(pt_ta)) * 100 ISC_MEASURE_32,
257 ct_exg ISC_MEASURE_33,
258 (ct_exg-pt_exg)/decode(pt_exg,0,null,abs(pt_exg)) * 100 ISC_MEASURE_34,
259 c_ba ISC_MEASURE_35,
260 p_ba ISC_MEASURE_36,
261 ct_ba ISC_MEASURE_38,
262 pt_ba ISC_MEASURE_39,
263 c_new ISC_MEASURE_40,
264 p_new ISC_MEASURE_41,
265 ct_new ISC_MEASURE_43,
266 pt_new ISC_MEASURE_44,
267 c_exp ISC_MEASURE_45,
268 p_exp ISC_MEASURE_46,
269 ct_exp ISC_MEASURE_48,
270 pt_exp ISC_MEASURE_49,
271 c_trm ISC_MEASURE_50,
272 p_trm ISC_MEASURE_51,
273 ct_trm ISC_MEASURE_53,
274 pt_trm ISC_MEASURE_54,
275 c_ta ISC_MEASURE_55,
276 p_ta ISC_MEASURE_56,
277 ct_ta ISC_MEASURE_58,
278 pt_ta ISC_MEASURE_59,
279 c_exg ISC_MEASURE_60,
280 p_exg ISC_MEASURE_61,
281 ct_exg ISC_MEASURE_63,
282 pt_exg ISC_MEASURE_64
283 from (
284 select '||l_viewby_col_str||',
285 sum(c_ba1)+sum(c_ba2)-sum(c_ba3) c_ba,
286 sum(p_ba1)+sum(p_ba2)-sum(p_ba3) p_ba,
287 sum(c_ta1)+sum(c_ta2)-sum(c_ta3) c_ta,
288 sum(p_ta1)+sum(p_ta2)-sum(p_ta3) p_ta,
289 sum(c_nc) c_nc,
290 sum(c_new1) c_new,
291 sum(p_new1) p_new,
292 sum(c_exp1)-sum(c_exp2) c_exp,
293 sum(p_exp1)-sum(p_exp2) p_exp,
294 sum(c_trm1) c_trm,
295 sum(p_trm1) p_trm,
296 sum(c_exg1)-sum(c_exg2)-sum(c_exg3)+sum(c_exg4)-sum(c_exg5)+sum(c_exg6) c_exg,
297 sum(p_exg1)-sum(p_exg2)-sum(p_exg3)+sum(p_exg4)-sum(p_exg5)+sum(p_exg6) p_exg,
298 sum(sum(c_ba1))over()+sum(sum(c_ba2))over()-sum(sum(c_ba3))over() ct_ba,
299 sum(sum(p_ba1))over()+sum(sum(p_ba2))over()-sum(sum(p_ba3))over() pt_ba,
300 sum(sum(c_ta1))over()+sum(sum(c_ta2))over()-sum(sum(c_ta3))over() ct_ta,
301 sum(sum(p_ta1))over()+sum(sum(p_ta2))over()-sum(sum(p_ta3))over() pt_ta,
302 sum(sum(c_nc)) over() ct_nc,
303 sum(sum(c_new1))over() ct_new,
304 sum(sum(p_new1))over() pt_new,
305 sum(sum(c_exp1))over()-sum(sum(c_exp2))over() ct_exp,
306 sum(sum(p_exp1))over()-sum(sum(p_exp2))over() pt_exp,
307 sum(sum(c_trm1))over() ct_trm,
308 sum(sum(p_trm1))over() pt_trm,
309 sum(sum(c_exg1))over()-sum(sum(c_exg2))over()-sum(sum(c_exg3))over()+sum(sum(c_exg4))over()
310 - sum(sum(c_exg5))over()+sum(sum(c_exg6))over() ct_exg,
311 sum(sum(p_exg1))over()-sum(sum(p_exg2))over()-sum(sum(p_exg3))over()+sum(sum(p_exg4))over()
312 - sum(sum(p_exg5))over()+sum(sum(p_exg6))over() pt_exg
313 from (
314 select '||l_viewby_col_str||',
315 decode(t.report_date, &BIS_CURRENT_EFFECTIVE_START_DATE-1, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) c_ba1,
316 decode(t.report_date, &BIS_PREVIOUS_EFFECTIVE_START_DATE-1, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) p_ba1,
317 0 c_ba2, 0 p_ba2, 0 c_ba3, 0 p_ba3,
318 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) c_ta1,
319 decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) p_ta1,
320 0 c_ta2, 0 p_ta2, 0 c_ta3, 0 p_ta3, 0 c_nc, 0 c_new1, 0 p_new1, 0 c_exp1, 0 p_exp1, 0 c_exp2,
321 0 p_exp2, 0 c_trm1, 0 p_trm1, 0 c_exg1, 0 p_exg1, 0 c_exg2, 0 p_exg2, 0 c_exg3, 0 p_exg3, 0 c_exg4,
322 0 p_exg4, 0 c_exg5, 0 p_exg5, 0 c_exg6, 0 p_exg6
323 from isc_sam_003_mv f, -- active balance
324 fii_time_day t
325 where t.report_date in (&BIS_CURRENT_EFFECTIVE_START_DATE-1,&BIS_PREVIOUS_EFFECTIVE_START_DATE-1,
326 &BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
327 and t.ent_year_id = f.ent_year_id
328 and f.agg_level = :ISC_AGG_LEVEL
329 '||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
330 union all
331 select '||l_viewby_col_str||',
332 0 c_ba1, 0 p_ba1, 0 c_ba2, 0 p_ba2, 0 c_ba3, 0 p_ba3, 0 c_ta1, 0 p_ta1,
333 0 c_ta2, 0 p_ta2, 0 c_ta3, 0 p_ta3, 0 c_nc, 0 c_new1, 0 p_new1,
334 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_e_t_amt_'||l_curr_suffix||',0), 0) c_exp1,
335 decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.commit_e_t_amt_'||l_curr_suffix||',0), 0) p_exp1,
336 0 c_exp2, 0 p_exp2, 0 c_trm1, 0 p_trm1,
337 decode(t.report_date, &BIS_CURRENT_EFFECTIVE_END_DATE,
338 nvl(f.commit_calc1_t'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_exg1,
339 decode(t.report_date, &BIS_PREVIOUS_EFFECTIVE_END_DATE,
340 nvl(f.commit_calc1_t'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) p_exg1,
341 decode(t.report_date, &BIS_CURRENT_ASOF_DATE,
342 nvl(f.commit_calc1_t'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_exg2,
343 decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE,
344 nvl(f.commit_calc1_t'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) p_exg2,
345 0 c_exg3, 0 p_exg3, 0 c_exg4, 0 p_exg4, 0 c_exg5, 0 p_exg5,
346 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_t_e_t'||l_period_str||'_amt_'||l_curr_suffix||',0),0) c_exp6,
347 decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.commit_t_e_t'||l_period_str||'_amt_'||l_curr_suffix||',0),0) p_exp6
348 from isc_sam_000_mv f, -- expiration
349 fii_time_rpt_struct_v t
350 where f.time_id = t.time_id
351 and t.report_date in (&BIS_CURRENT_EFFECTIVE_END_DATE,&BIS_PREVIOUS_EFFECTIVE_END_DATE,
352 &BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
356 union all
353 and bitand(t.record_type_id, &BIS_NESTED_PATTERN) = t.record_type_id
354 and f.agg_level = :ISC_AGG_LEVEL
355 '||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
357 select '||l_viewby_col_str||',
358 0 c_ba1, 0 p_ba1, 0 c_ba2, 0 p_ba2, 0 c_ba3, 0 p_ba3, 0 c_ta1, 0 p_ta1,
359 0 c_ta2, 0 p_ta2, 0 c_ta3, 0 p_ta3,
360 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.new_agr_cnt,0), 0) c_nc,
361 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) c_new1,
362 decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) p_new1,
363 0 c_exp1, 0 p_exp1, 0 c_exp2, 0 p_exp2, 0 c_trm1, 0 p_trm1, 0 c_exg1, 0 p_exg1, 0 c_exg2, 0 p_exg2,
364 decode(t.report_date, &BIS_CURRENT_EFFECTIVE_END_DATE,
365 nvl(f.commit_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_exg3,
366 decode(t.report_date, &BIS_PREVIOUS_EFFECTIVE_END_DATE,
367 nvl(f.commit_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) p_exg3,
368 decode(t.report_date, &BIS_CURRENT_ASOF_DATE,
369 nvl(f.commit_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) c_exg4,
370 decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE,
371 nvl(f.commit_e'||l_period_str||'_amt_'||l_curr_suffix||',0), 0) p_exg4,
372 0 c_exg5, 0 p_exg5, 0 c_exg6, 0 p_exg6
373 from isc_sam_001_mv f, -- activation
374 fii_time_rpt_struct_v t
375 where f.time_id = t.time_id
376 and t.report_date in (&BIS_CURRENT_EFFECTIVE_END_DATE,&BIS_PREVIOUS_EFFECTIVE_END_DATE,
377 &BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
378 and bitand(t.record_type_id, &BIS_NESTED_PATTERN) = t.record_type_id
379 and f.agg_level = :ISC_AGG_LEVEL
380 '||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
381 union all
382 select '||l_viewby_col_str||',
383 0 c_ba1, 0 p_ba1,
384 decode(t.report_date, &BIS_CURRENT_EFFECTIVE_START_DATE-1, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) c_ba2,
385 decode(t.report_date, &BIS_PREVIOUS_EFFECTIVE_START_DATE-1, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) p_ba2,
386 0 c_ba3, 0 p_ba3, 0 c_ta1, 0 p_ta1,
387 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) c_ta2,
388 decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) p_ta2,
389 0 c_ta3, 0 p_ta3, 0 c_nc, 0 c_new1, 0 p_new1, 0 c_exp1, 0 p_exp1, 0 c_exp2, 0 p_exp2, 0 c_trm1, 0 p_trm1,
390 0 c_exg1, 0 p_exg1, 0 c_exg2, 0 p_exg2, 0 c_exg3, 0 p_exg3, 0 c_exg4, 0 p_exg4, 0 c_exg5, 0 p_exg5,
391 0 c_exg6, 0 p_exg6
392 from isc_sam_001_mv f, -- activation
393 fii_time_rpt_struct_v t
394 where f.time_id = t.time_id
395 and t.report_date in (&BIS_CURRENT_EFFECTIVE_START_DATE-1,&BIS_PREVIOUS_EFFECTIVE_START_DATE-1,
396 &BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
397 and bitand(t.record_type_id, 119) = t.record_type_id
398 and f.agg_level = :ISC_AGG_LEVEL
399 '||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
400 union all
401 select '||l_viewby_col_str||',
402 0 c_ba1, 0 p_ba1, 0 c_ba2, 0 p_ba2, 0 c_ba3, 0 p_ba3, 0 c_ta1, 0 p_ta1,
403 0 c_ta2, 0 p_ta2, 0 c_ta3, 0 p_ta3, 0 c_nc, 0 c_new1, 0 p_new1, 0 c_exp1, 0 p_exp1,
404 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,
405 decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.commit_e_t_e'||l_period_str||'_amt_'||l_curr_suffix||',0),0) p_exp2,
406 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) c_trm1,
407 decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) p_trm1,
408 0 c_exg1, 0 p_exg1, 0 c_exg2, 0 p_exg2, 0 c_exg3, 0 p_exg3, 0 c_exg4, 0 p_exg4,
409 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,
410 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,
411 0 c_exg6, 0 p_exg6
412 from isc_sam_002_mv f, -- termination
413 fii_time_rpt_struct_v t
414 where f.time_id = t.time_id
415 and t.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
416 and bitand(t.record_type_id, &BIS_NESTED_PATTERN) = t.record_type_id
417 and f.agg_level = :ISC_AGG_LEVEL
418 '||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
419 union all
420 select '||l_viewby_col_str||',
421 0 c_ba1, 0 p_ba1, 0 c_ba2, 0 p_ba2,
422 decode(t.report_date, &BIS_CURRENT_EFFECTIVE_START_DATE-1, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) c_ba3,
423 decode(t.report_date, &BIS_PREVIOUS_EFFECTIVE_START_DATE-1, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) p_ba3,
424 0 c_ta1, 0 p_ta1, 0 c_ta2, 0 p_ta2,
425 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) c_ta3,
426 decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.commit_amt_'||l_curr_suffix||',0), 0) p_ta3,
427 0 c_nc, 0 c_new1, 0 p_new1, 0 c_exp1, 0 p_exp1, 0 c_exp2, 0 p_exp2, 0 c_trm1, 0 p_trm1, 0 c_exg1, 0 p_exg1,
428 0 c_exg2, 0 p_exg2, 0 c_exg3, 0 p_exg3, 0 c_exg4, 0 p_exg4, 0 c_exg5, 0 p_exg5, 0 c_exg6, 0 p_exg6
429 from isc_sam_004_mv f, -- effective_end
430 fii_time_rpt_struct_v t
431 where f.time_id = t.time_id
432 and t.report_date in (&BIS_CURRENT_EFFECTIVE_START_DATE-1,&BIS_PREVIOUS_EFFECTIVE_START_DATE-1,
433 &BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
434 and bitand(t.record_type_id, 119) = t.record_type_id
435 and f.agg_level = :ISC_AGG_LEVEL
436 '||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
437 ) oset
438 group by '||l_viewby_col_str||'
439 ) ) where isc_measure_7 <> 0
440 or isc_measure_8 <> 0
441 or isc_measure_9 <> 0
442 or isc_measure_10 <> 0
443 or isc_measure_11 <> 0
444 or isc_measure_12 <> 0
445 or isc_measure_13 <> 0
446 or isc_measure_14 <> 0
447 or isc_measure_15 <> 0
448 or isc_measure_18 <> 0
449 or isc_measure_19 <> 0
450 or isc_measure_20 <> 0
451 or isc_measure_21 <> 0
452 ) oset,
453 '||l_dim_join_str||'
454 and ((oset.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
455 order by oset.rnk
456 ';
457
458 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
459 x_custom_output := bis_query_attributes_tbl();
460
461 l_custom_rec.attribute_name := ':ISC_AGG_LEVEL';
462 l_custom_rec.attribute_value := l_agg_level;
463 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
464 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
465 x_custom_output.EXTEND;
466 x_custom_output(x_custom_output.count) := l_custom_rec;
467
468 l_custom_rec.attribute_name := ':ISC_SG';
469 l_custom_rec.attribute_value := to_char(l_sg_sg);
470 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
471 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
472 x_custom_output.extend;
473 x_custom_output(x_custom_output.count) := l_custom_rec;
474
475 l_custom_rec.attribute_name := ':ISC_RES';
476 l_custom_rec.attribute_value := to_char(l_sg_res);
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 x_custom_sql := l_query;
483
484
485 end get_sql;
486
487 function ttl1 (p_param in bis_pmv_page_parameter_tbl) return varchar2 is
488
489 l_view_by varchar2(10000);
490 l_title varchar2(10000);
491
492 begin
493
494 for i in 1..p_param.count loop
495
496 if (p_param(i).parameter_name = 'VIEW_BY') then
497 l_view_by := p_param(i).parameter_value;
498 end if;
499
500 end loop;
501
502 if (l_view_by = 'ORGANIZATION+JTF_ORG_SALES_GROUP') then
503 l_title := fnd_message.get_string('ISC','ISC_DBI_TITLE1_SALES_GROUP');
504 elsif (l_view_by = 'ISC_AGREEMENT_TYPE+ISC_AGREEMENT_TYPE') then
505 l_title := fnd_message.get_string('ISC','ISC_DBI_TITLE1_AGREEMENT_TYPE');
506 elsif (l_view_by = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS') then
507 l_title := fnd_message.get_string('ISC','ISC_DBI_TITLE1_CUST_CLASS');
508 elsif (l_view_by = 'CUSTOMER+FII_CUSTOMERS') then
509 l_title := fnd_message.get_string('ISC','ISC_DBI_TITLE1_CUSTOMER');
510 end if;
511
512 return l_title;
513
514 end ttl1;
515
516 function ttl2 (p_param in bis_pmv_page_parameter_tbl) return varchar2 is
517
518 l_view_by varchar2(10000);
519 l_title varchar2(10000);
520
521 begin
522
523 for i in 1..p_param.count loop
524
525 if (p_param(i).parameter_name = 'VIEW_BY') then
526 l_view_by := p_param(i).parameter_value;
527 end if;
528
529 end loop;
530
531 if (l_view_by = 'ORGANIZATION+JTF_ORG_SALES_GROUP') then
532 l_title := fnd_message.get_string('ISC','ISC_DBI_TITLE2_SALES_GROUP');
533 elsif (l_view_by = 'ISC_AGREEMENT_TYPE+ISC_AGREEMENT_TYPE') then
534 l_title := fnd_message.get_string('ISC','ISC_DBI_TITLE2_AGREEMENT_TYPE');
535 elsif (l_view_by = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS') then
536 l_title := fnd_message.get_string('ISC','ISC_DBI_TITLE2_CUST_CLASS');
537 elsif (l_view_by = 'CUSTOMER+FII_CUSTOMERS') then
538 l_title := fnd_message.get_string('ISC','ISC_DBI_TITLE2_CUSTOMER');
539 end if;
540
541 return l_title;
542
543 end ttl2;
544
545
546 end isc_dbi_sam_agr_sum_pkg;
547