[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_PLAN_CB_SUM_PKG
Source
1 PACKAGE BODY ISC_DBI_PLAN_CB_SUM_PKG AS
2 /* $Header: ISCRGAMB.pls 120.0 2005/05/25 17:37:09 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_outer_sql VARCHAR2(10000);
13 l_formula_sql VARCHAR2(10000);
14 l_inner_sql VARCHAR2(10000);
15 l_view_by VARCHAR2(10000);
16 l_org VARCHAR2(10000);
17 l_org_where VARCHAR2(10000);
18 l_item VARCHAR2(10000);
19 l_item_where VARCHAR2(10000);
20 l_inv_cat VARCHAR2(10000);
21 l_inv_cat_where VARCHAR2(10000);
22 l_period_type VARCHAR2(10000);
23 l_period_type_id NUMBER;
24 l_time_from DATE;
25 l_lang VARCHAR2(10);
26 l_item_cat_flag NUMBER;
27 l_union1_flag NUMBER := 0;
28 l_row_filter VARCHAR2(10000);
29 l_custom_rec BIS_QUERY_ATTRIBUTES;
30 l_curr VARCHAR2(10000);
31 l_curr_g VARCHAR2(15) := '''FII_GLOBAL1''';
32 l_curr_g1 VARCHAR2(15) := '''FII_GLOBAL2''';
33 l_col1 VARCHAR2(100);
34 l_col2 VARCHAR2(100);
35 l_col3 VARCHAR2(100);
36
37
38 BEGIN
39
40 FOR i IN 1..p_param.COUNT
41 LOOP
42 IF (p_param(i).parameter_name = 'PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT')
43 THEN l_plan := p_param(i).parameter_value;
44 END IF;
45
46 IF (p_param(i).parameter_name = 'PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2')
47 THEN l_plan2 := p_param(i).parameter_value;
48 END IF;
49
50 IF (p_param(i).parameter_name = 'VIEW_BY')
51 THEN l_view_by := p_param(i).parameter_value;
52 END IF;
53
54 IF(p_param(i).parameter_name = 'PERIOD_TYPE')
55 THEN l_period_type := p_param(i).parameter_value;
56 END IF;
57
58 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM')
59 THEN l_time_from := p_param(i).period_date;
60 END IF;
61
62 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM')
63 THEN l_time_from := p_param(i).period_date;
64 END IF;
65
66 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM')
67 THEN l_time_from := p_param(i).period_date;
68 END IF;
69
70 IF (p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
71 THEN l_org := p_param(i).parameter_value;
72 END IF;
73
74 IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_INV_CAT')
75 THEN l_inv_cat := p_param(i).parameter_value;
76 END IF;
77
78 IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG')
79 THEN l_item := p_param(i).parameter_value;
80 END IF;
81
82 IF (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
83 THEN l_curr := p_param(i).parameter_id;
84 END IF;
85
86 END LOOP;
87
88 IF (l_curr = l_curr_g)
89 THEN
90 l_col1 := 'pro_cost_g';
91 l_col2 := 'carrying_cost_g';
92 l_col3 := 'pur_cost_g';
93 ELSIF (l_curr = l_curr_g1)
94 THEN
95 l_col1 := 'pro_cost_g1';
96 l_col2 := 'carrying_cost_g1';
97 l_col3 := 'pur_cost_g1';
98 ELSE
99 l_col1 := 'production_cost';
100 l_col2 := 'carrying_cost';
101 l_col3 := 'purchasing_cost';
102 END IF;
103
104 IF (l_org IS NULL OR l_org = 'All')
105 THEN l_org_where := '
106 AND (EXISTS
107 (SELECT 1
108 FROM org_access o
109 WHERE o.responsibility_id = fnd_global.resp_id
110 AND o.resp_application_id = fnd_global.resp_appl_id
111 AND o.organization_id = f.organization_id)
112 OR EXISTS
113 (SELECT 1
114 FROM mtl_parameters org
115 WHERE org.organization_id = f.organization_id
116 AND NOT EXISTS
117 (SELECT 1
118 FROM org_access ora
119 WHERE org.organization_id = ora.organization_id)))';
120 ELSE l_org_where := '
121 AND f.organization_id = &ORGANIZATION+ORGANIZATION';
122 END IF;
123
124 IF(l_inv_cat IS NULL OR l_inv_cat = 'All')
125 THEN l_inv_cat_where := '';
126 ELSE l_inv_cat_where := '
127 AND f.inv_category_id IN (&ITEM+ENI_ITEM_INV_CAT)';
128 END IF;
129
130 IF(l_item IS NULL OR l_item = 'All')
131 THEN l_item_where := '';
132 ELSE l_item_where := '
133 AND f.item_id IN (&ITEM+ENI_ITEM_ORG)';
134 END IF;
135
136 IF (l_period_type = 'FII_TIME_ENT_PERIOD') THEN
137 l_period_type_id := 32;
138 ELSIF (l_period_type = 'FII_TIME_ENT_QTR') THEN
139 l_period_type_id := 64;
140 ELSE -- l_period_type = 'FII_TIME_ENT_YEAR'
141 l_period_type_id := 128;
142 END IF;
143
144 IF (l_item IS NULL OR l_item = 'All')
145 THEN
146 IF (l_view_by = 'ITEM+ENI_ITEM_ORG')
147 THEN l_item_cat_flag := 0; -- item
148 ELSIF (l_view_by = 'ITEM+ENI_ITEM_INV_CAT')
149 THEN l_item_cat_flag := 1; -- inventory category
150 ELSE
151 IF (l_inv_cat IS NULL OR l_inv_cat = 'All')
152 THEN l_item_cat_flag := 3; -- all
153 ELSE l_item_cat_flag := 1; -- inventory category
154 END IF;
155 END IF;
156 ELSE
157 l_item_cat_flag := 0; -- item
158 END IF;
159
160
161 l_lang := USERENV('LANG');
162
163 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
164 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
165
166 IF (l_plan IS NULL OR l_plan2 IS NULL)
167 THEN l_stmt := '
168 SELECT 0 VIEWBY,
169 0 VIEWBYID,
170 0 ISC_ATTRIBUTE_1,
171 0 ISC_MEASURE_1,
172 0 ISC_MEASURE_2,
173 0 ISC_MEASURE_3,
174 0 ISC_MEASURE_4,
175 0 ISC_MEASURE_5,
176 0 ISC_MEASURE_6,
177 0 ISC_MEASURE_7,
178 0 ISC_MEASURE_8,
179 0 ISC_MEASURE_9,
180 0 ISC_MEASURE_10,
181 0 ISC_MEASURE_11,
182 0 ISC_MEASURE_12,
183 0 ISC_MEASURE_13,
184 0 ISC_MEASURE_14,
185 0 ISC_MEASURE_15,
186 0 ISC_MEASURE_16,
187 0 ISC_MEASURE_17,
188 0 ISC_MEASURE_18,
189 0 ISC_MEASURE_19,
190 0 ISC_MEASURE_20
191 FROM dual
192 WHERE 1 = 2 /* PLAN_SNAPSHOT dimension has not been populated */';
193 ELSE
194
195 -- Filter out rows with only 0 or N/A
196 l_row_filter := '
197 WHERE (ISC_MEASURE_1 IS NOT NULL AND ISC_MEASURE_1 <> 0)
198 OR (ISC_MEASURE_2 IS NOT NULL AND ISC_MEASURE_2 <> 0)
199 OR (ISC_MEASURE_4 IS NOT NULL AND ISC_MEASURE_4 <> 0)
200 OR (ISC_MEASURE_5 IS NOT NULL AND ISC_MEASURE_5 <> 0)
201 OR (ISC_MEASURE_7 IS NOT NULL AND ISC_MEASURE_7 <> 0)
202 OR (ISC_MEASURE_8 IS NOT NULL AND ISC_MEASURE_8 <> 0)';
203
204 l_outer_sql:= 'ISC_MEASURE_1, ISC_MEASURE_2, ISC_MEASURE_3, ISC_MEASURE_4, ISC_MEASURE_5,
205 ISC_MEASURE_6, ISC_MEASURE_7, ISC_MEASURE_8, ISC_MEASURE_9, ISC_MEASURE_10,
206 ISC_MEASURE_11, ISC_MEASURE_12, ISC_MEASURE_13, ISC_MEASURE_14, ISC_MEASURE_15,
207 ISC_MEASURE_16, ISC_MEASURE_17, ISC_MEASURE_18, ISC_MEASURE_19, ISC_MEASURE_20';
208
209 l_formula_sql := 'c.prod ISC_MEASURE_1, -- Production Cost
210 c.comp_prod ISC_MEASURE_2, -- Compare Plan (Production Cost)
211 (c.prod - c.comp_prod) ISC_MEASURE_3, -- Variance (Production Cost)
212 c.carry ISC_MEASURE_4, -- Carrying Cost
213 c.comp_carry ISC_MEASURE_5, -- Compare Plan (Carrying Cost)
214 (c.carry - c.comp_carry) ISC_MEASURE_6, -- Variance (Carrying Cost)
215 c.purch ISC_MEASURE_7, -- Purchasing Cost
216 c.comp_purch ISC_MEASURE_8, -- Compare Plan (Purchasing Cost)
217 (c.purch - c.comp_purch) ISC_MEASURE_9, -- Variance (Purchasing Cost)
218 (c.prod + c.carry + c.purch) ISC_MEASURE_10, -- Combined Cost
219 (c.comp_prod + c.comp_carry + c.comp_purch)
220 ISC_MEASURE_11, -- Compare Plan (Combined Cost)
221 (c.prod + c.carry + c.purch) - (c.comp_prod + c.comp_carry + c.comp_purch)
222 ISC_MEASURE_12, -- Variance (Combined Cost)
223 sum(c.prod) over () ISC_MEASURE_13, -- Grand Total - Production Cost
224 sum(c.prod - c.comp_prod) over () ISC_MEASURE_14, -- Grand Total - Variance (Production Cost)
225 sum(c.carry) over () ISC_MEASURE_15, -- Grand Total - Carrying Cost
226 sum(c.carry - c.comp_carry) over () ISC_MEASURE_16, -- Grand Total - Variance (Carrying Cost)
227 sum(c.purch) over () ISC_MEASURE_17, -- Grand Total - Purchasing Cost
228 sum(c.purch - c.comp_purch) over () ISC_MEASURE_18, -- Grand Total - Variance (Purchasing Cost)
229 sum(c.prod + c.carry + c.purch) over () ISC_MEASURE_19, -- Grand Total - Combined Cost
230 sum((c.prod + c.carry + c.purch) - (c.comp_prod + c.comp_carry + c.comp_purch)) over ()
231 ISC_MEASURE_20 -- Grand Total - Variance (Combined Cost)';
232
233 l_inner_sql := 'sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
234 f.'||l_col1||', 0)) PROD,
235 decode(&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2, -1, null,
236 sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
237 f.'||l_col1||', 0))) COMP_PROD,
238 sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
239 f.'||l_col2||', 0)) CARRY,
240 decode(&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2, -1, null,
241 sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
242 f.'||l_col2||', 0))) COMP_CARRY,
243 sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
244 f.'||l_col3||', 0)) PURCH,
245 decode(&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2, -1, null,
246 sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
247 f.'||l_col3||', 0))) COMP_PURCH
248 FROM ISC_DBI_PM_0001_MV f
249 WHERE f.start_date = :ISC_TIME_FROM
250 AND f.period_type_id =:ISC_PERIOD_TYPE_ID
251 AND f.item_cat_flag = :ISC_ITEM_CAT_FLAG
252 AND f.union1_flag <> :ISC_UNION1_FLAG
253 AND f.snapshot_id IN (&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2)'
254 ||l_org_where
255 ||l_inv_cat_where
256 ||l_item_where;
257
258
259 IF l_view_by = 'ITEM+ENI_ITEM_ORG'
260 THEN l_stmt := '
261 SELECT items.value VIEWBY,
262 items.id VIEWBYID,
263 items.description ISC_ATTRIBUTE_1, -- Description
264 '||l_outer_sql||'
265 FROM (SELECT (rank() over (&ORDER_BY_CLAUSE NULLS LAST,item_id))-1 RNK,
266 item_id,
267 '||l_outer_sql||'
268 FROM (SELECT item_id,
269 '||l_formula_sql||'
270 FROM (SELECT f.item_id ITEM_ID,
271 '||l_inner_sql||'
272 GROUP BY f.item_id) c)'
273 ||l_row_filter||'
274 ) a,
275 ENI_ITEM_ORG_V items
276 WHERE a.item_id = items.id
277 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
278 ORDER BY rnk';
279
280 ELSIF l_view_by = 'ORGANIZATION+ORGANIZATION'
281 THEN l_stmt := '
282 SELECT org.name VIEWBY,
283 org.organization_id VIEWBYID,
284 null ISC_ATTRIBUTE_1, -- Description
285 '||l_outer_sql||'
286 FROM (SELECT (rank() over (&ORDER_BY_CLAUSE NULLS LAST,organization_id))-1 RNK,
287 organization_id,
288 '||l_outer_sql||'
289 FROM (SELECT organization_id,
290 '||l_formula_sql||'
291 FROM (SELECT f.organization_id ORGANIZATION_ID,
292 '||l_inner_sql||'
293 GROUP BY f.organization_id) c)'
294 ||l_row_filter||'
295 ) a,
296 HR_ALL_ORGANIZATION_UNITS_TL org
297 WHERE org.organization_id = a.organization_id
298 AND org.language = :ISC_LANG
299 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
303 l_stmt := '
300 ORDER BY rnk';
301
302 ELSE -- l_view_by = 'ITEM+ENI_ITEM_INV_CAT'
304 SELECT eni.value VIEWBY,
305 eni.id VIEWBYID,
306 null ISC_ATTRIBUTE_1, -- Description
307 '||l_outer_sql||'
308 FROM (SELECT (rank() over (&ORDER_BY_CLAUSE NULLS LAST,inv_category_id))-1 RNK,
309 inv_category_id,
310 '||l_outer_sql||'
311 FROM (SELECT inv_category_id,
312 '||l_formula_sql||'
313 FROM (SELECT f.inv_category_id INV_CATEGORY_ID,
314 '||l_inner_sql||'
315 GROUP BY f.inv_category_id) c)'
316 ||l_row_filter||'
317 ) a,
318 ENI_ITEM_INV_CAT_V eni
319 WHERE a.inv_category_id = eni.id
320 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
321 ORDER BY rnk';
322
323 END IF;
324 END IF;
325
326 x_custom_sql := l_stmt;
327
328 l_custom_rec.attribute_name := ':ISC_LANG';
329 l_custom_rec.attribute_value := l_lang;
330 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
331 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
332 x_custom_output.extend;
333 x_custom_output(1) := l_custom_rec;
334
335 l_custom_rec.attribute_name := ':ISC_PERIOD_TYPE_ID';
336 l_custom_rec.attribute_value := to_char(l_period_type_id);
337 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
338 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
339 x_custom_output.extend;
340 x_custom_output(2) := l_custom_rec;
341
342 l_custom_rec.attribute_name := ':ISC_TIME_FROM';
343 l_custom_rec.attribute_value := to_char(l_time_from,'DD/MM/YYYY');
344 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
345 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.date_bind;
346 x_custom_output.extend;
347 x_custom_output(3) := l_custom_rec;
348
349 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
350 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
351 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
352 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
353 x_custom_output.extend;
354 x_custom_output(4) := l_custom_rec;
355
356 l_custom_rec.attribute_name := ':ISC_UNION1_FLAG';
357 l_custom_rec.attribute_value := to_char(l_union1_flag);
358 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.Bind_Type;
359 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.Integer_Bind;
360 x_custom_output.extend;
361 x_custom_output(5) := l_custom_rec;
362
363 END Get_Sql;
364
365 END ISC_DBI_PLAN_CB_SUM_PKG ;
366