[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_PLAN_RM_TREND_PKG
Source
1 PACKAGE BODY ISC_DBI_PLAN_RM_TREND_PKG AS
2 /* $Header: ISCRGAJB.pls 120.0 2005/05/25 17:25:55 appldev noship $ */
3
4
5 PROCEDURE Get_Sql ( p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
6 x_custom_sql OUT NOCOPY VARCHAR2,
7 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
8
9 l_stmt VARCHAR2(10000);
10 l_plan VARCHAR2(10000);
11 l_plan2 VARCHAR2(10000);
12 l_org VARCHAR2(10000);
13 l_org_where VARCHAR2(10000);
14 l_prod VARCHAR2(10000);
15 l_prod_where VARCHAR2(10000);
16 l_prod_cat VARCHAR2(10000);
17 l_prod_cat_from VARCHAR2(10000);
18 l_prod_cat_where VARCHAR2(10000);
19 l_period_type VARCHAR2(10000);
20 l_period_type_id NUMBER;
21 l_time_from DATE;
22 l_rpt_end_date DATE;
23 l_loop NUMBER;
24 l_item_cat_flag NUMBER;
25 l_union1_flag NUMBER := 0;
26 l_mv VARCHAR2(100);
27 l_flags_where VARCHAR2(1000);
28 l_custom_rec BIS_QUERY_ATTRIBUTES;
29 l_curr VARCHAR2(10000);
30 l_curr_g VARCHAR2(15) := '''FII_GLOBAL1''';
31 l_curr_g1 VARCHAR2(15) := '''FII_GLOBAL2''';
32 l_curr_suffix VARCHAR2(15);
33
34 BEGIN
35
36 FOR i IN 1..p_param.COUNT
37 LOOP
38 IF (p_param(i).parameter_name = 'PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT')
39 THEN l_plan := p_param(i).parameter_value;
40 END IF;
41
42 IF (p_param(i).parameter_name = 'PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2')
43 THEN l_plan2 := p_param(i).parameter_value;
44 END IF;
45
46 IF(p_param(i).parameter_name = 'PERIOD_TYPE')
47 THEN l_period_type := p_param(i).parameter_value;
48 END IF;
49
50 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM')
51 THEN l_time_from := p_param(i).period_date;
52 END IF;
53
54 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM')
55 THEN l_time_from := p_param(i).period_date;
56 END IF;
57
58 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM')
59 THEN l_time_from := p_param(i).period_date;
60 END IF;
61
62 IF (p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
63 THEN l_org := p_param(i).parameter_value;
64 END IF;
65
66 IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT')
67 THEN l_prod_cat := p_param(i).parameter_value;
68 END IF;
69
70 IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG')
71 THEN l_prod := p_param(i).parameter_value;
72 END IF;
73
74 IF (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
75 THEN l_curr := p_param(i).parameter_id;
76 END IF;
77
78 END LOOP;
79
80 IF (l_curr = l_curr_g)
81 THEN
82 l_curr_suffix := '_g';
83 ELSIF (l_curr = l_curr_g1)
84 THEN
85 l_curr_suffix := '_g1';
86 ELSE
87 l_curr_suffix := '';
88 END IF;
89
90 IF (l_org IS NULL OR l_org = 'All')
91 THEN l_org_where := '
92 AND (EXISTS
93 (SELECT 1
94 FROM org_access o
95 WHERE o.responsibility_id = fnd_global.resp_id
96 AND o.resp_application_id = fnd_global.resp_appl_id
97 AND o.organization_id = f.organization_id)
98 OR EXISTS
99 (SELECT 1
100 FROM mtl_parameters org
101 WHERE org.organization_id = f.organization_id
102 AND NOT EXISTS
103 (SELECT 1
104 FROM org_access ora
105 WHERE org.organization_id = ora.organization_id)))';
106 ELSE l_org_where := '
107 AND f.organization_id = &ORGANIZATION+ORGANIZATION';
108 END IF;
109
110 IF (l_prod_cat IS NULL OR l_prod_cat = 'All')
111 THEN
112 l_prod_cat_from := '';
113 l_prod_cat_where := '';
114 ELSE
115 l_prod_cat_from := ',
116 ENI_DENORM_HIERARCHIES eni_cat,
117 MTL_DEFAULT_CATEGORY_SETS mdcs';
118 l_prod_cat_where := '
119 AND f.vbh_category_id = eni_cat.child_id
120 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
121 AND eni_cat.dbi_flag = ''Y''
122 AND eni_cat.object_type = ''CATEGORY_SET''
123 AND eni_cat.object_id = mdcs.category_set_id
124 AND mdcs.functional_area_id = 11';
125 END IF;
126
127 IF (l_prod IS NULL OR l_prod = 'All')
128 THEN l_prod_where := '';
129 ELSE l_prod_where := '
130 AND f.item_id IN (&ITEM+ENI_ITEM_ORG)';
131 END IF;
132
133 IF (l_prod IS NULL OR l_prod = 'All')
134 THEN
135 IF (l_prod_cat IS NULL OR l_prod_cat = 'All')
136 THEN l_item_cat_flag := 3; -- all
137 ELSE l_item_cat_flag := 2; -- category
138 END IF;
139 ELSE
140 l_item_cat_flag := 0; -- product
141 END IF;
142
143 IF (l_period_type = 'FII_TIME_ENT_PERIOD') THEN
144 l_period_type_id := 32;
145 l_loop := 11;
146 ELSIF (l_period_type = 'FII_TIME_ENT_QTR') THEN
147 l_period_type_id := 64;
148 l_loop := 7;
149 ELSE -- l_period_type = 'FII_TIME_ENT_YEAR'
150 l_period_type_id := 128;
151 l_loop := 3;
152 END IF;
153
154 -- Get report end date
155 l_rpt_end_date := l_time_from;
156
157 FOR i IN 1..l_loop
158 LOOP
159 l_rpt_end_date := FII_TIME_API.next_period_end_date(l_rpt_end_date, l_period_type);
160 END LOOP;
161
162 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
163 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
164
165 IF (l_plan IS NULL OR l_plan2 IS NULL)
166 THEN l_stmt := '
167 SELECT 0 ISC_MEASURE_1,
168 0 ISC_MEASURE_2,
169 0 ISC_MEASURE_3,
170 0 ISC_MEASURE_4,
171 0 ISC_MEASURE_5,
172 0 ISC_MEASURE_6,
173 0 ISC_MEASURE_7,
174 0 ISC_MEASURE_8,
175 0 ISC_MEASURE_9,
176 0 ISC_MEASURE_10,
177 0 ISC_MEASURE_11,
178 0 ISC_MEASURE_12
179 FROM dual
180 WHERE 1 = 2 /* PLAN_SNAPSHOT dimension has not been populated */';
181 ELSE
182
183 IF ((l_prod_cat IS NULL OR l_prod_cat = 'All') AND (l_prod IS NULL OR l_prod = 'All'))
184 THEN
185 l_mv := 'ISC_DBI_PM_0003_MV';
186 l_flags_where := '';
187 ELSE
188 l_mv := 'ISC_DBI_PM_0001_MV';
189 l_flags_where := '
190 AND f.item_cat_flag = :ISC_ITEM_CAT_FLAG
191 AND f.union1_flag <> :ISC_UNION1_FLAG';
192 END IF;
193
194 l_stmt := '
195 SELECT fii.name VIEWBY,
196 nvl(c.rev,0) ISC_MEASURE_1, -- Revenue
197 nvl(c.comp_rev,0) ISC_MEASURE_2, -- Compare Plan (Revenue)
198 nvl((c.rev - c.comp_rev),0) ISC_MEASURE_3, -- Variance (Revenue)
199 nvl(c.cost,0) ISC_MEASURE_4, -- Cost
200 nvl(c.comp_cost,0) ISC_MEASURE_5, -- Compare Plan (Cost)
201 nvl((c.cost - c.comp_cost),0) ISC_MEASURE_6, -- Variance (Cost)
202 nvl((c.rev - c.cost),0) ISC_MEASURE_7, -- Margin
203 nvl((c.comp_rev - c.comp_cost),0)
204 ISC_MEASURE_8, -- Compare Plan (Margin)
205 nvl(((c.rev - c.cost) - (c.comp_rev - c.comp_cost)),0)
206 ISC_MEASURE_9, -- Variance (Margin)
207 (c.rev - c.cost) / decode(c.rev,0,NULL,c.rev) * 100
208 ISC_MEASURE_10, -- Margin Percent
209 (c.comp_rev - c.comp_cost) / decode(c.comp_rev,0,NULL,c.comp_rev) * 100
210 ISC_MEASURE_11, -- Compare Plan (Margin Percent)
211 ((c.rev - c.cost) / decode(c.rev,0,NULL,c.rev) * 100)
212 - ((c.comp_rev - c.comp_cost) / decode(c.comp_rev,0,NULL,c.comp_rev) * 100)
213 ISC_MEASURE_12 -- Variance (Margin Percent)
214 FROM (SELECT f.start_date START_DATE,
215 sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
216 f.mds_price'||l_curr_suffix||', 0)) REV,
217 decode(&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2, -1, null,
218 sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
219 f.mds_price'||l_curr_suffix||', 0))) COMP_REV,
220 sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
221 f.mds_cost'||l_curr_suffix||', 0)) COST,
222 decode(&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2, -1, null,
223 sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
224 f.mds_cost'||l_curr_suffix||', 0))) COMP_COST
225 FROM '||l_mv||' f'
226 ||l_prod_cat_from||'
227 WHERE f.start_date BETWEEN :ISC_TIME_FROM AND :ISC_RPT_END_DATE
228 AND f.period_type_id = :ISC_PERIOD_TYPE_ID
229 AND f.snapshot_id IN (&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2)'
230 ||l_flags_where
231 ||l_org_where||l_prod_cat_where||l_prod_where||'
232 GROUP BY f.start_date) c,
233 '||l_period_type||' fii
234 WHERE fii.start_date BETWEEN :ISC_TIME_FROM AND :ISC_RPT_END_DATE
235 AND fii.start_date = c.start_date(+)
236 ORDER BY fii.start_date';
237
238 END IF;
239
240 x_custom_sql := l_stmt;
241
242 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
243 l_custom_rec.attribute_value := 'TIME+'||l_period_type;
244 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
245 x_custom_output.extend;
246 x_custom_output(1) := l_custom_rec;
247
248 l_custom_rec.attribute_name := ':ISC_PERIOD_TYPE_ID';
249 l_custom_rec.attribute_value := to_char(l_period_type_id);
250 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
251 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
252 x_custom_output.extend;
253 x_custom_output(2) := l_custom_rec;
254
255 l_custom_rec.attribute_name := ':ISC_TIME_FROM';
256 l_custom_rec.attribute_value := to_char(l_time_from,'DD/MM/YYYY');
257 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
258 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.date_bind;
259 x_custom_output.extend;
260 x_custom_output(3) := l_custom_rec;
261
262 l_custom_rec.attribute_name := ':ISC_RPT_END_DATE';
263 l_custom_rec.attribute_value := to_char(l_rpt_end_date,'DD/MM/YYYY');
264 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
265 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.date_bind;
266 x_custom_output.extend;
267 x_custom_output(4) := l_custom_rec;
268
269 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
270 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
271 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
272 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
273 x_custom_output.extend;
274 x_custom_output(5) := l_custom_rec;
275
276 l_custom_rec.attribute_name := ':ISC_UNION1_FLAG';
277 l_custom_rec.attribute_value := to_char(l_union1_flag);
278 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.Bind_Type;
279 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.Integer_Bind;
280 x_custom_output.extend;
281 x_custom_output(6) := l_custom_rec;
282
283 END Get_Sql;
284
285 END ISC_DBI_PLAN_RM_TREND_PKG ;
286