[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_CFM_PKG
Source
1 PACKAGE BODY ENI_DBI_CFM_PKG AS
2 /* $Header: ENICFMPB.pls 120.0 2005/05/26 19:35:02 appldev 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_stmt VARCHAR2(10000);
9 l_measures VARCHAR2(10000);
10 l_select_stmt VARCHAR2(10000);
11 l_union_select_stmt VARCHAR2(10000);
12 l_union_group_by_stmt VARCHAR2(10000);
13 l_inner_select_stmt VARCHAR2(10000);
14 l_where_stmt VARCHAR2(10000);
15 l_mv1 VARCHAR2(100);
16 l_mv2 VARCHAR2(100);
17 l_flags_where VARCHAR2(1000);
18 l_inv_org VARCHAR2(10000);
19 l_inv_org_where VARCHAR2(10000);
20 l_prod VARCHAR2(10000);
21 l_prod_where VARCHAR2(10000);
22 l_prod_cat VARCHAR2(10000);
23 l_prod_cat_from VARCHAR2(10000);
24 l_prod_cat_where VARCHAR2(10000);
25 l_cust VARCHAR2(10000);
26 l_cust_where VARCHAR2(10000);
27 l_curr VARCHAR2(10000);
28 l_curr_suffix VARCHAR2(10);
29 l_view_by VARCHAR2(120);
30 l_lang VARCHAR2(10);
31 l_item_cat_flag NUMBER;
32 l_cust_flag NUMBER;
33 l_view_by_flag NUMBER;
34
35 l_open_url1 VARCHAR2(1000);
36 l_open_url2 VARCHAR2(1000);
37
38 l_all_prods BOOLEAN;
39 l_all_prod_cats BOOLEAN;
40 l_all_custs BOOLEAN;
41
42 l_vb_prod_cat BOOLEAN;
43 l_vb_prod BOOLEAN;
44 l_vb_org BOOLEAN;
45 l_vb_cust BOOLEAN;
46
47 l_custom_rec BIS_QUERY_ATTRIBUTES;
48
49 BEGIN
50
51 l_lang := userenv('LANG');
52
53 FOR i IN 1..p_param.COUNT LOOP
54 CASE p_param(i).parameter_name
55 WHEN 'VIEW_BY' THEN l_view_by := p_param(i).parameter_value;
56 WHEN 'ITEM+ENI_ITEM_VBH_CAT' THEN l_prod_cat := p_param(i).parameter_value;
57 WHEN 'ITEM+ENI_ITEM' THEN l_prod := p_param(i).parameter_value;
58 WHEN 'CUSTOMER+FII_CUSTOMERS' THEN l_cust := p_param(i).parameter_value;
59 WHEN 'CURRENCY+FII_CURRENCIES' THEN
60 l_curr_suffix :=
61 CASE p_param(i).parameter_id
62 WHEN eni_dbi_util_pkg.get_curr_prim THEN 'g' -- primary global currency
63 WHEN eni_dbi_util_pkg.get_curr_sec THEN 'g1' -- secondary global currency
64 ELSE 'f' -- functional currency
65 END;
66 ELSE null;
67 END CASE;
68 END LOOP;
69
70 l_all_prods := (l_prod IS NULL OR l_prod = '' OR l_prod = 'All');
71 l_all_prod_cats := (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All');
72 l_all_custs := (l_cust IS NULL OR l_cust = '' OR l_cust = 'All');
73
74 l_vb_prod_cat := (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT' );
75 l_vb_prod := (l_view_by = 'ITEM+ENI_ITEM' );
76 l_vb_cust := (l_view_by = 'CUSTOMER+FII_CUSTOMERS' );
77 l_vb_org := (l_view_by = 'ORGANIZATION+ORGANIZATION');
78
79 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
80 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
81
82 l_measures := '
83 , ENI_MEASURE1, ENI_MEASURE2, ENI_MEASURE3, ENI_MEASURE4, ENI_MEASURE5
84 , ENI_MEASURE6, ENI_MEASURE7, ENI_MEASURE8, ENI_MEASURE9, ENI_MEASURE10
85 , ENI_MEASURE11, ENI_MEASURE12, ENI_MEASURE13, ENI_MEASURE14
86 , ENI_MEASURE15, ENI_MEASURE16, ENI_MEASURE17, ENI_MEASURE18
87 , ENI_MEASURE19, ENI_MEASURE20, ENI_MEASURE21, ENI_MEASURE22
88 , ENI_MEASURE24, ENI_MEASURE25 ';
89 l_open_url1 :=
90 '''pFunctionName=ENI_DBI_CFM_R&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM&pParamIds=Y''';
91 l_open_url2 :=
92 '''pFunctionName=ENI_DBI_CFM_R&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y''';
93
94
95 /* This portion of the code sets up spaghetti pieces for particular viewbys */
96 CASE
97 WHEN l_vb_prod THEN -- +====================== PRODUCT =========================+
98 l_select_stmt := '
99 SELECT items.value VIEWBY
100 , NULL ENI_ATTRIBUTE3 -- drill across url
101 , items.description ENI_ATTRIBUTE4 -- item description
102 -- , mtl.unit_of_measure ENI_ATTRIBUTE_5 -- item uom'
103 || l_measures ||'
104 FROM
105 ( SELECT ( rank() over (&ORDER_BY_CLAUSE nulls last, item_id) ) - 1 rnk
106 , item_id'
107 || l_measures ||'
108 FROM
109 ( SELECT c.item_id ';
110
111 l_inner_select_stmt := ' SELECT fact.master_item_id ITEM_ID';
112 l_union_select_stmt := ' SELECT item_id ITEM_ID';
113 l_union_group_by_stmt := ' GROUP BY item_id';
114
115 l_where_stmt := '
116 , ENI_ITEM_V items
117 WHERE a.item_id = items.id
118 AND ( (a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1) )
119 &ORDER_BY_CLAUSE NULLS LAST';
120
121 WHEN l_vb_org THEN -- +=================== ORGANIZATION =======================+
122 l_select_stmt := '
123 SELECT org.name VIEWBY
124 , NULL ENI_ATTRIBUTE3 -- drill across url
125 , NULL ENI_ATTRIBUTE4 -- item description
126 -- , NULL ISC_ATTRIBUTE_5 -- item uom'
127 || l_measures ||'
128 FROM
129 ( SELECT ( rank() over (&ORDER_BY_CLAUSE nulls last, inv_org_id) ) - 1 rnk
130 , inv_org_id'
131 || l_measures ||'
132 FROM
133 ( SELECT c.inv_org_id ';
134
135 l_inner_select_stmt := ' SELECT fact.inv_org_id INV_ORG_ID';
136 l_union_select_stmt := ' SELECT inv_org_id INV_ORG_ID';
137 l_union_group_by_stmt := ' GROUP BY inv_org_id';
138
139 l_where_stmt := '
140 , HR_ALL_ORGANIZATION_UNITS_TL org
141 WHERE a.inv_org_id = org.organization_id
142 AND org.language = :ENI_LANG
143 AND ( (a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1) )
144 &ORDER_BY_CLAUSE NULLS LAST';
145
146 WHEN l_vb_cust THEN -- +===================== CUSTOMER =========================+
147 l_select_stmt := '
148 SELECT cust.value VIEWBY
149 , NULL ENI_ATTRIBUTE3 -- drill across url
150 , NULL ENI_ATTRIBUTE4 -- item description
151 -- , NULL ISC_ATTRIBUTE_5 -- item uom'
152 || l_measures ||'
153 FROM
154 ( SELECT ( rank() over (&ORDER_BY_CLAUSE nulls last, customer_id) ) - 1 rnk
155 , customer_id'
156 || l_measures ||'
157 FROM
158 ( SELECT c.customer_id ';
159
160 l_inner_select_stmt := ' SELECT fact.customer_id CUSTOMER_ID';
161 l_union_select_stmt := ' SELECT customer_id CUSTOMER_ID';
162 l_union_group_by_stmt := ' GROUP BY customer_id';
163 l_where_stmt := '
164 , FII_CUSTOMERS_V cust
165 WHERE a.customer_id = cust.id
166 AND ( (a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1) )
167 &ORDER_BY_CLAUSE NULLS LAST';
168
169 WHEN l_vb_prod_cat THEN -- +================== PRODUCT CAT =========================+
170 l_select_stmt := '
171 SELECT eni_vbh.value VIEWBY
172 , eni_vbh.id VIEWBYID
173 , decode( eni_vbh.leaf_node_flag, ''Y''
174 , '|| l_open_url1 ||'
175 , '|| l_open_url2 ||' ) ENI_ATTRIBUTE3 -- drill across url
176 , NULL ENI_ATTRIBUTE4 -- item description
177 -- , NULL ISC_ATTRIBUTE_5 -- item uom'
178 || l_measures ||'
179 FROM
180 ( SELECT ( rank() over (&ORDER_BY_CLAUSE nulls last, item_category_id) ) - 1 rnk
181 , item_category_id'
182 || l_measures ||'
183 FROM
184 ( SELECT c.item_category_id ';
185
186 IF l_all_prod_cats THEN
187 l_inner_select_stmt := ' SELECT eni_cat.parent_id ITEM_CATEGORY_ID ';
188 ELSE
189 l_inner_select_stmt := ' SELECT eni_cat.imm_child_id ITEM_CATEGORY_ID ';
190 END IF;
191
192 l_union_select_stmt := ' SELECT item_category_id ITEM_CATEGORY_ID ';
193 l_union_group_by_stmt := ' GROUP BY item_category_id';
194 l_where_stmt := '
195 , ENI_ITEM_VBH_NODES_V eni_vbh
196 WHERE a.item_category_id = eni_vbh.id
197 AND eni_vbh.parent_id = eni_vbh.child_id
198 AND ( (a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1) )
199 &ORDER_BY_CLAUSE NULLS LAST';
200
201 END CASE;
202
203 IF( l_all_prods
204 AND l_all_custs
205 AND ( l_vb_org OR l_vb_prod_cat ) )
206 THEN
207 l_mv1 := 'ISC_DBI_CFM_009_MV';
208 l_mv2 := 'ISC_DBI_CFM_011_MV';
209 l_flags_where := '';
210
211 IF l_vb_prod_cat THEN
212 l_inner_select_stmt := ' SELECT fact.parent_id ITEM_CATEGORY_ID ';
213 IF l_all_prod_cats THEN
214 l_prod_cat_from := '';
215 l_prod_cat_where := '
216 AND fact.top_node_flag = ''Y''
217 AND fact.inv_org_flag = 1 ';
218 ELSE
219 l_prod_cat_from := '
220 , ENI_DENORM_HIERARCHIES eni_cat
221 , MTL_DEFAULT_CATEGORY_SETS mdcs';
222 l_prod_cat_where := '
223 AND fact.inv_org_flag = 1
224 AND fact.parent_id = eni_cat.child_id
225 AND eni_cat.dbi_flag = ''Y''
226 AND eni_cat.object_type = ''CATEGORY_SET''
227 AND eni_cat.object_id = mdcs.category_set_id
228 AND mdcs.functional_area_id = 11
229 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
230 AND ( ( eni_cat.leaf_node_flag = ''Y''
231 AND eni_cat.parent_id = eni_cat.child_id)
232 OR ( eni_cat.imm_child_id = eni_cat.child_id
233 AND eni_cat.parent_id <> child_id) )';
234 END IF;
235 ELSIF l_vb_org THEN
236 l_prod_cat_from := '';
237 l_prod_cat_where := '
238 AND fact.inv_org_flag = 0
239 AND '|| CASE
240 WHEN l_all_prod_cats THEN 'fact.top_node_flag = ''Y'' '
241 ELSE 'fact.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT) '
242 END;
243 END IF;
244 ELSE
245 l_mv1 := 'ISC_DBI_CFM_000_MV';
246 l_mv2 := 'ISC_DBI_CFM_002_MV';
247 l_flags_where := '
248 AND fact.item_cat_flag = :ENI_ITEM_CAT_FLAG
249 AND fact.customer_flag = :ENI_CUST_FLAG';
250
251 l_prod_cat_from := '
252 , ENI_DENORM_HIERARCHIES eni_cat
253 , MTL_DEFAULT_CATEGORY_SETS mdcs';
254
255 IF( l_all_prod_cats ) THEN
256 IF l_vb_prod_cat THEN -- all top-node categories
257 l_prod_cat_where := '
258 AND fact.item_category_id = eni_cat.child_id
259 AND eni_cat.top_node_flag = ''Y''
260 AND eni_cat.dbi_flag = ''Y''
261 AND eni_cat.object_type = ''CATEGORY_SET''
262 AND eni_cat.object_id = mdcs.category_set_id
263 AND mdcs.functional_area_id = 11';
264 ELSE
265 l_prod_cat_from := '';
266 l_prod_cat_where := '';
267 END IF;
268 ELSE -- category specified
269 l_prod_cat_where := '
270 AND fact.item_category_id = eni_cat.child_id
271 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
272 AND eni_cat.dbi_flag = ''Y''
273 AND eni_cat.object_type = ''CATEGORY_SET''
274 AND eni_cat.object_id = mdcs.category_set_id
275 AND mdcs.functional_area_id = 11';
276 END IF;
277
278 -- ITEM AND ITEM CATEGORY
279 IF l_all_prods THEN
280 l_prod_where := '';
281
282 l_item_cat_flag := CASE -- order matters
283 WHEN l_vb_prod THEN 4 -- rollup on master item
284 WHEN l_vb_prod_cat THEN 1 -- rollup on category
285 WHEN l_all_prod_cats THEN 3 -- all product categories
286 ELSE 1
287 END;
288 ELSE
289 l_prod_where := '
290 AND fact.master_item_id IN (&ITEM+ENI_ITEM)';
291
292 IF l_vb_prod THEN
293 l_item_cat_flag := 4;
294 ELSE
295 l_item_cat_flag := 0;
296 END IF;
297 END IF;
298
299 -- CUSTOMER
300 IF l_all_custs THEN
301 l_cust_where := '';
302 l_cust_flag := CASE
303 WHEN l_vb_cust THEN 0 -- customers selected
304 ELSE 1 -- all customers & not viewed by customer
305 END;
306 ELSE
307 l_cust_where := ' AND fact.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
308 l_cust_flag := 0; -- customer selected
309 END IF;
310 END IF;
311
312 l_stmt := l_select_stmt || '
313 , c.curr_booked_qty ENI_MEASURE1 -- book qty
314 , c.curr_booked_value ENI_MEASURE2 -- book
315 , ( c.curr_booked_value-c.prev_booked_value)
316 / decode( c.prev_booked_value, 0, NULL
317 , abs(c.prev_booked_value)) * 100 ENI_MEASURE3 -- book change
318 , c.curr_fulfill_qty ENI_MEASURE4 -- fulf qty
319 , c.curr_fulfill_value ENI_MEASURE5 -- fulf
320 , (c.curr_fulfill_value-c.prev_fulfill_value)
321 / decode( c.prev_fulfill_value, 0, NULL
322 , abs(c.prev_fulfill_value)) * 100 ENI_MEASURE6 -- fulf change
323 , c.curr_booked_value
324 / decode( c.curr_fulfill_value, 0, NULL
325 , c.curr_fulfill_value) ENI_MEASURE7 -- book to fulf r
326 , c.curr_booked_value
327 / decode( c.curr_fulfill_value, 0, NULL
328 , c.curr_fulfill_value)
329 - c.prev_booked_value
330 / decode( c.prev_fulfill_value, 0, NULL
331 , c.prev_fulfill_value) ENI_MEASURE8 -- book to fulf r change
332 , sum(c.curr_booked_value) over () ENI_MEASURE9 -- gd total book
333 , ( sum(c.curr_booked_value) over () - sum(c.prev_booked_value) over () )
334 / decode( sum( c.prev_booked_value ) over (), 0, NULL
335 , abs( sum(c.prev_booked_value) over () ) )
336 * 100 ENI_MEASURE10 -- gd total book change
337 , sum(c.curr_fulfill_value) over () ENI_MEASURE11 -- gd total fulf
338 , (sum(c.curr_fulfill_value) over () - sum(c.prev_fulfill_value) over ())
339 / decode( sum(c.prev_fulfill_value) over (), 0, NULL
340 , abs( sum(c.prev_fulfill_value) over () ) )
341 * 100 ENI_MEASURE12 -- gd total fulf change
342 , sum(c.curr_booked_value) over ()
343 / decode( sum(c.curr_fulfill_value) over (), 0, NULL
344 , sum(c.curr_fulfill_value) over () ) ENI_MEASURE13 -- gd total book to fulf r
345 , sum(c.curr_booked_value) over ()
346 / decode( sum(c.curr_fulfill_value) over (), 0, NULL
347 , sum(c.curr_fulfill_value) over () )
348 - sum(c.prev_booked_value) over ()
349 / decode( sum(c.prev_fulfill_value) over (), 0, NULL
350 , sum(c.prev_fulfill_value) over () ) ENI_MEASURE14 -- gd total book to fulf r change
351 , c.curr_booked_value ENI_MEASURE15 -- KPI book
352 , c.prev_booked_value ENI_MEASURE16 -- KPI book prior
353 , c.curr_fulfill_value ENI_MEASURE17 -- KPI fulf
354 , c.prev_fulfill_value ENI_MEASURE18 -- KPI fulf prior
355 , c.curr_booked_value
356 / decode( c.curr_fulfill_value, 0, NULL
357 , c.curr_fulfill_value ) ENI_MEASURE19 -- KPI book to fulf r
358 , c.prev_booked_value
359 / decode( c.prev_fulfill_value, 0, NULL
360 , c.prev_fulfill_value ) ENI_MEASURE20 -- KPI book to fulf r prior
361 , sum(c.curr_booked_value) over () ENI_MEASURE21 -- KPI gd total book value
362 , sum(c.prev_booked_value) over () ENI_MEASURE22 -- KPI gd total book prior
363 , c.prev_booked_value
364 / decode( c.prev_fulfill_value, 0, NULL
365 , c.prev_fulfill_value ) ENI_MEASURE24 -- KPI book to fulf r prior
366 , sum(c.prev_booked_value) over ()
367 / decode( sum(c.prev_fulfill_value) over (), 0, NULL
368 , sum(c.prev_fulfill_value) over () ) ENI_MEASURE25 -- KPI gd total book to fulf r prior
369 FROM ('||l_union_select_stmt||'
370 , sum(curr_booked_qty) CURR_BOOKED_QTY
371 , sum(curr_booked_value) CURR_BOOKED_VALUE
372 , sum(prev_booked_value) PREV_BOOKED_VALUE
373 , sum(curr_fulfill_qty) CURR_FULFILL_QTY
374 , sum(curr_fulfill_value) CURR_FULFILL_VALUE
375 , sum(prev_fulfill_value) PREV_FULFILL_VALUE
376 FROM ('||l_inner_select_stmt||'
377 , fact.inv_org_id INV_ORG
378 , decode( cal.report_date, &BIS_CURRENT_ASOF_DATE
379 , fact.booked_qty, 0) CURR_BOOKED_QTY
380 , decode( cal.report_date, &BIS_CURRENT_ASOF_DATE
381 , fact.booked_amt_'||l_curr_suffix||', 0) CURR_BOOKED_VALUE
382 , decode( cal.report_date, &BIS_PREVIOUS_ASOF_DATE
383 , fact.booked_amt_'||l_curr_suffix||', 0) PREV_BOOKED_VALUE
384 , 0 CURR_FULFILL_QTY
385 , 0 CURR_FULFILL_VALUE
386 , 0 PREV_FULFILL_VALUE
387 FROM '||l_mv1||' fact
388 , FII_TIME_RPT_STRUCT_V cal'
389 ||l_prod_cat_from||'
390 WHERE fact.time_id = cal.time_id'
391 || l_flags_where||'
392 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
393 AND cal.period_type_id = fact.period_type_id
394 AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'
395 || l_prod_cat_where
396 || l_prod_where
397 || l_cust_where||'
398 UNION ALL
399 '||l_inner_select_stmt||'
400 , fact.inv_org_id INV_ORG
401 , 0 CURR_BOOKED_QTY
402 , 0 CURR_BOOKED_VALUE
403 , 0 PREV_BOOKED_VALUE
404 , decode( cal.report_date, &BIS_CURRENT_ASOF_DATE
405 , fact.fulfilled_qty, 0) CURR_FULFILL_QTY
406 , decode( cal.report_date, &BIS_CURRENT_ASOF_DATE
407 , fact.fulfilled_amt_'||l_curr_suffix||', 0) CURR_FULFILL_VALUE
408 , decode( cal.report_date, &BIS_PREVIOUS_ASOF_DATE
409 , fact.fulfilled_amt_'||l_curr_suffix||', 0) PREV_FULFILL_VALUE
410 FROM '||l_mv2||' fact
411 , FII_TIME_RPT_STRUCT_V cal'
412 ||l_prod_cat_from||'
413 WHERE fact.time_id = cal.time_id'
414 || l_flags_where||'
415 AND fact.return_flag = 0
416 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
417 AND cal.period_type_id = fact.period_type_id
418 AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'
419 || l_prod_cat_where
420 || l_prod_where
421 || l_cust_where
422 || ')'
423 -- WHERE '||l_inv_org_where
424 ||l_union_group_by_stmt||') c) ) a'
425 ||l_where_stmt;
426
427 x_custom_sql := l_stmt;
428
429 l_custom_rec.attribute_name := ':ENI_ITEM_CAT_FLAG';
430 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
431 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
432 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
433 x_custom_output.EXTEND;
434 x_custom_output(1) := l_custom_rec;
435
436 l_custom_rec.attribute_name := ':ENI_CUST_FLAG';
437 l_custom_rec.attribute_value := to_char(l_cust_flag);
438 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
439 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
440 x_custom_output.EXTEND;
441 x_custom_output(2) := l_custom_rec;
442
443 l_custom_rec.attribute_name := ':ENI_LANG';
444 l_custom_rec.attribute_value := l_lang;
445 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
446 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
447 x_custom_output.EXTEND;
448 x_custom_output(3) := l_custom_rec;
449
450 END Get_Sql;
451
452 END ENI_DBI_CFM_PKG;