[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_SAM_AGR_ORD_PKG
Source
1 package body isc_dbi_sam_agr_ord_pkg as
2 /* $Header: ISCRGCDB.pls 120.6 2005/12/15 16:57:00 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_class varchar2(32000);
11 l_cust varchar2(32000);
12 l_curr varchar2(32000);
13 l_curr_suffix varchar2(32000);
14 l_sg_sg number;
15 l_sg_res number;
16 l_sg_where varchar2(32000);
17 l_class_where varchar2(32000);
18 l_cust_where varchar2(32000);
19 l_class_needed boolean;
20 l_cust_needed boolean;
21 l_agg_level number;
22 l_sg_drill_str varchar2(32000);
23 l_class_drill_str varchar2(32000);
24 l_col_drill_str varchar2(32000);
25 l_viewby_col_str varchar2(32000);
26 l_viewby_select_str varchar2(32000);
27 l_viewbyid_select_str varchar2(32000);
28 l_dim_join_str varchar2(32000);
29 l_query varchar2(32000);
30 l_custom_rec bis_query_attributes;
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 = 'VIEW_BY') then
38 l_view_by := p_param(i).parameter_value;
39 end if;
40
41 if (p_param(i).parameter_name = 'ORGANIZATION+JTF_ORG_SALES_GROUP') then
42 l_sgid := p_param(i).parameter_id;
43 end if;
44
45 if (p_param(i).parameter_name = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS') then
46 l_class := p_param(i).parameter_id;
47 end if;
48
49 if (p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS') then
50 l_cust := p_param(i).parameter_id;
51 end if;
52
53 if (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES') then
54 l_curr := p_param(i).parameter_id;
55 end if;
56
57 end loop;
58
59 if (l_curr = '''FII_GLOBAL1''') then
60 l_curr_suffix := 'g';
61 else -- (l_curr = '''FII_GLOBAL2''')
62 l_curr_suffix := 'g1';
63 end if;
64
65 -- Figure out where clauses
66 l_sg_sg := to_number(replace(substr(l_sgid,instr(l_sgid,'.') + 1),''''));
67 l_sg_res := to_number(replace(substr(l_sgid,1,instr(l_sgid,'.') - 1),''''));
68
69 if (l_sg_res is null) then -- when a sales group is chosen
70 l_col_drill_str := 'null ISC_ATTRIBUTE_5,
71 null ISC_ATTRIBUTE_7,';
72 if (l_view_by = 'ORGANIZATION+JTF_ORG_SALES_GROUP') then
73 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
74 else -- other view bys
75 l_sg_where := ' and f.sales_grp_id = (&ORGANIZATION+JTF_ORG_SALES_GROUP) and f.resource_id is null';
76 end if;
77 else -- when the LOV parameter is a Salesrep (no need to go through the SG hierarchy)
78 l_col_drill_str := '''pFunctionName=ISC_DBI_SAM_AGR_ORD_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' ISC_ATTRIBUTE_5,
79 ''pFunctionName=ISC_DBI_SAM_NAGR_ORD_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' ISC_ATTRIBUTE_7,';
80 l_sg_where := ' and f.sales_grp_id = :ISC_SG and f.resource_id = :ISC_RES';
81 end if;
82
83 if (l_class is null) then
84 l_class_where := '';
85 else
86 l_class_where := ' and f.class_code in (&FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS)';
87 end if;
88
89 if (l_cust is null) then
90 l_cust_where := '';
91 else
92 l_cust_where := ' and f.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
93 end if;
94
95 -- Figure out agg_level flag value
96 l_class_needed := false;
97 l_cust_needed := false;
98
99 if (l_view_by = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS' or l_class is not null) then
100 l_class_needed := true;
101 end if;
102
103 if (l_view_by = 'CUSTOMER+FII_CUSTOMERS' or l_cust is not null) then
104 l_cust_needed := true;
105 end if;
106
107 case
108 when ( l_class_needed and l_cust_needed) then l_agg_level := 0;
109 when ( l_class_needed and not l_cust_needed) then l_agg_level := 1;
110 when (not l_class_needed and l_cust_needed) then l_agg_level := 0;
111 when (not l_class_needed and not l_cust_needed) then l_agg_level := 3;
112 end case;
113
114 -- Figure out pieces of strings to fill in the query
115 if (l_view_by = 'ORGANIZATION+JTF_ORG_SALES_GROUP') then
116 l_sg_drill_str := 'decode(oset.resource_id, null, ''pFunctionName=ISC_DBI_SAM_AGR_ORD&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'', null)';
117 l_class_drill_str := 'null';
118 l_col_drill_str := 'decode(oset.resource_id, null, null, ''pFunctionName=ISC_DBI_SAM_AGR_ORD_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'') ISC_ATTRIBUTE_5,
119 decode(oset.resource_id, null, null, ''pFunctionName=ISC_DBI_SAM_NAGR_ORD_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'') ISC_ATTRIBUTE_7,';
120 l_viewby_col_str := 'resource_id, sales_grp_id';
121 l_viewby_select_str := 'decode(oset.resource_id, null, g.group_name, r.resource_name)';
122 l_viewbyid_select_str := 'decode(oset.resource_id, null, to_char(oset.sales_grp_id), oset.resource_id||''.''||oset.sales_grp_id)';
123 l_dim_join_str := ' jtf_rs_groups_vl g, jtf_rs_resource_extns_vl r
124 where oset.sales_grp_id = g.group_id
125 and oset.resource_id = r.resource_id (+) ';
126
127 elsif (l_view_by = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS') then
128 l_sg_drill_str := 'null';
129 l_class_drill_str := '''pFunctionName=ISC_DBI_SAM_AGR_ORD&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMERS&pParamIds=Y''';
130 l_viewby_col_str := 'class_code';
131 l_viewby_select_str := 'v.value';
132 l_viewbyid_select_str := 'v.id';
133 l_dim_join_str := ' fii_partner_mkt_class_v v
134 where oset.class_code = v.id ';
135
136 else -- (l_view_by = 'CUSTOMER+FII_CUSTOMERS')
137 l_sg_drill_str := 'null';
138 l_class_drill_str := 'null';
139 l_viewby_col_str := 'customer_id';
140 l_viewby_select_str := 'v.value';
141 l_viewbyid_select_str := 'v.id';
142 l_dim_join_str := ' fii_customers_v v
143 where oset.customer_id = v.id ';
144 end if;
145
146 l_query := '
147 select '||l_viewby_select_str||' VIEWBY,
148 '||l_viewbyid_select_str||' VIEWBYID,
149 '||l_sg_drill_str||' ISC_ATTRIBUTE_3,
150 '||l_col_drill_str||'
151 '||l_class_drill_str||' ISC_ATTRIBUTE_8,
152 ISC_MEASURE_5,ISC_MEASURE_6,ISC_MEASURE_8,ISC_MEASURE_9,
153 ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13,ISC_MEASURE_15,
154 ISC_MEASURE_16,ISC_MEASURE_18,ISC_MEASURE_19,
155 ISC_MEASURE_21,ISC_MEASURE_22,ISC_MEASURE_23,ISC_MEASURE_24,ISC_MEASURE_25,
156 ISC_MEASURE_26,ISC_MEASURE_27,ISC_MEASURE_28,ISC_MEASURE_30,
157 ISC_MEASURE_31,ISC_MEASURE_32,ISC_MEASURE_33,ISC_MEASURE_35,
158 ISC_MEASURE_36,ISC_MEASURE_37,ISC_MEASURE_38,ISC_MEASURE_40,
159 ISC_MEASURE_41
160 from (select '||l_viewby_col_str||',
161 (rank() over (&ORDER_BY_CLAUSE nulls last, '||l_viewby_col_str||'))-1 rnk,
162 ISC_MEASURE_5,ISC_MEASURE_6,ISC_MEASURE_8,ISC_MEASURE_9,
163 ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13,ISC_MEASURE_15,
164 ISC_MEASURE_16,ISC_MEASURE_18,ISC_MEASURE_19,
165 ISC_MEASURE_21,ISC_MEASURE_22,ISC_MEASURE_23,ISC_MEASURE_24,ISC_MEASURE_25,
166 ISC_MEASURE_26,ISC_MEASURE_27,ISC_MEASURE_28,ISC_MEASURE_30,
167 ISC_MEASURE_31,ISC_MEASURE_32,ISC_MEASURE_33,ISC_MEASURE_35,
168 ISC_MEASURE_36,ISC_MEASURE_37,ISC_MEASURE_38,ISC_MEASURE_40,
169 ISC_MEASURE_41
170 from (select '||l_viewby_col_str||',
171 c_agr ISC_MEASURE_5,
172 (c_agr - p_agr) / decode(p_agr,0,null,abs(p_agr)) * 100 ISC_MEASURE_6,
173 c_nagr ISC_MEASURE_8,
174 (c_nagr - p_nagr) / decode(p_nagr,0,null,abs(p_nagr)) * 100 ISC_MEASURE_9,
175 c_agr+c_nagr ISC_MEASURE_11,
176 (c_agr+c_nagr-p_agr-p_nagr) / decode(p_agr+p_nagr,0,null,abs(p_agr+p_nagr)) * 100 ISC_MEASURE_12,
177 c_nagr / decode(c_agr+c_nagr,0,null,abs(c_agr+c_nagr)) * 100 ISC_MEASURE_13,
178 ct_agr ISC_MEASURE_15,
179 (ct_agr - pt_agr) / decode(pt_agr,0,null,abs(pt_agr)) * 100 ISC_MEASURE_16,
180 ct_nagr ISC_MEASURE_18,
181 (ct_nagr - pt_nagr) / decode(pt_nagr,0,null,abs(pt_nagr)) * 100 ISC_MEASURE_19,
182 ct_agr+ct_nagr ISC_MEASURE_21,
183 (ct_agr+ct_nagr-pt_agr-pt_nagr) / decode(pt_agr+pt_nagr,0,null,abs(pt_agr+pt_nagr)) * 100 ISC_MEASURE_22,
184 ct_nagr / decode(ct_agr+ct_nagr,0,null,abs(ct_agr+ct_nagr)) * 100 ISC_MEASURE_23,
185 c_agr ISC_MEASURE_24,
186 c_agr ISC_MEASURE_25,
187 c_nagr ISC_MEASURE_26,
188 c_agr ISC_MEASURE_27,
189 p_agr ISC_MEASURE_28,
190 ct_agr ISC_MEASURE_30,
191 pt_agr ISC_MEASURE_31,
192 c_nagr ISC_MEASURE_32,
193 p_nagr ISC_MEASURE_33,
194 ct_nagr ISC_MEASURE_35,
195 pt_nagr ISC_MEASURE_36,
196 c_agr / decode(c_agr+c_nagr,0,null,abs(c_agr+c_nagr)) * 100 ISC_MEASURE_37,
197 p_agr / decode(p_agr+p_nagr,0,null,abs(p_agr+p_nagr)) * 100 ISC_MEASURE_38,
198 ct_nagr / decode(ct_agr+ct_nagr,0,null,abs(ct_agr+ct_nagr)) * 100 ISC_MEASURE_40,
199 pt_nagr / decode(pt_agr+pt_nagr,0,null,abs(pt_agr+pt_nagr)) * 100 ISC_MEASURE_41
200 from (
201 select '||l_viewby_col_str||',
202 sum(c_agr) c_agr,
203 sum(p_agr) p_agr,
204 sum(c_nagr) c_nagr,
205 sum(p_nagr) p_nagr,
206 sum(sum(c_agr))over() ct_agr,
207 sum(sum(p_agr))over() pt_agr,
208 sum(sum(c_nagr))over() ct_nagr,
209 sum(sum(p_nagr))over() pt_nagr
210 from (
211 select '||l_viewby_col_str||',
212 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.fulfill_amt_'||l_curr_suffix||',0), 0) c_agr,
213 decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.fulfill_amt_'||l_curr_suffix||',0), 0) p_agr,
214 0 c_nagr,
215 0 p_nagr
216 from isc_sam_007_mv f, -- agreement fulfilled
217 fii_time_rpt_struct_v t
218 where f.time_id = t.time_id
219 and t.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
220 and bitand(t.record_type_id, &BIS_NESTED_PATTERN) = t.record_type_id
221 and f.agg_level = :ISC_AGG_LEVEL
222 '||l_sg_where||l_class_where||l_cust_where||'
223 union all
224 select '||l_viewby_col_str||',
225 0 c_agr,
226 0 p_agr,
227 decode(t.report_date, &BIS_CURRENT_ASOF_DATE, nvl(f.fulfill_amt_'||l_curr_suffix||',0), 0) c_nagr,
228 decode(t.report_date, &BIS_PREVIOUS_ASOF_DATE, nvl(f.fulfill_amt_'||l_curr_suffix||',0), 0) p_nagr
229 from isc_sam_006_mv f, -- non-agreement fulfilled
230 fii_time_rpt_struct_v t
231 where f.time_id = t.time_id
232 and t.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
233 and bitand(t.record_type_id, &BIS_NESTED_PATTERN) = t.record_type_id
234 and f.agg_level = :ISC_AGG_LEVEL
235 '||l_sg_where||l_class_where||l_cust_where||'
236 ) oset
237 group by '||l_viewby_col_str||'
238 ) ) where isc_measure_5 <> 0
239 or isc_measure_6 <> 0
240 or isc_measure_8 <> 0
241 or isc_measure_9 <> 0
242 ) oset,
243 '||l_dim_join_str||'
244 and ((oset.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
245 order by oset.rnk
246 ';
247
248 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
249 x_custom_output := bis_query_attributes_tbl();
250
251 l_custom_rec.attribute_name := ':ISC_AGG_LEVEL';
252 l_custom_rec.attribute_value := l_agg_level;
253 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
254 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
255 x_custom_output.EXTEND;
256 x_custom_output(x_custom_output.count) := l_custom_rec;
257
258 l_custom_rec.attribute_name := ':ISC_SG';
259 l_custom_rec.attribute_value := to_char(l_sg_sg);
263 x_custom_output(x_custom_output.count) := l_custom_rec;
260 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
261 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
262 x_custom_output.extend;
264
265 l_custom_rec.attribute_name := ':ISC_RES';
266 l_custom_rec.attribute_value := to_char(l_sg_res);
267 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
268 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
269 x_custom_output.extend;
270 x_custom_output(x_custom_output.count) := l_custom_rec;
271
272 x_custom_sql := l_query;
273
274 end get_sql;
275
276 end isc_dbi_sam_agr_ord_pkg;