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