[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_FPT_PKG
Source
1 PACKAGE BODY ENI_DBI_FPT_PKG AS
2 /* $Header: ENIFPTPB.pls 120.0 2005/05/26 19:36:28 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_period_type VARCHAR2(10000);
10 l_mv1 VARCHAR2(100);
11 l_mv2 VARCHAR2(100);
12 l_flags_where VARCHAR2(1000);
13 l_inv_org VARCHAR2(10000);
14 l_inv_org_where VARCHAR2(10000);
15 l_prod VARCHAR2(10000);
16 l_prod_where VARCHAR2(10000);
17 l_prod_cat VARCHAR2(10000);
18 l_prod_cat_from VARCHAR2(10000);
19 l_prod_cat_where VARCHAR2(10000);
20 l_cust VARCHAR2(10000);
21 l_cust_where VARCHAR2(10000);
22
23 l_curr_suffix VARCHAR2(10);
24 -- l_curr VARCHAR2(15) := 'NOT PASSED IN';
25
26 l_all_prods BOOLEAN;
27 l_all_prod_cats BOOLEAN;
28 l_all_custs BOOLEAN;
29
30 l_item_cat_flag NUMBER; -- 0 for product, 1 for product category, 3 for no grouping on item dimension
31 l_cust_flag NUMBER; -- 0 for customer and 1 for no customer selected
32
33 l_order_by VARCHAR2(250);
34 l_custom_rec BIS_QUERY_ATTRIBUTES;
35
36 BEGIN
37 l_period_type := 'TEST';
38 FOR i IN 1..p_param.COUNT LOOP
39 CASE p_param(i).parameter_name
40 WHEN 'PERIOD_TYPE' THEN l_period_type := p_param(i).parameter_value;
41 WHEN 'ITEM+ENI_ITEM_VBH_CAT' THEN l_prod_cat := p_param(i).parameter_value;
42 WHEN 'ITEM+ENI_ITEM' THEN l_prod := p_param(i).parameter_value;
43 WHEN 'CUSTOMER+FII_CUSTOMERS' THEN l_cust := p_param(i).parameter_value;
44 WHEN 'ORDERBY' THEN l_order_by := p_param(i).parameter_value;
45 WHEN 'CURRENCY+FII_CURRENCIES' THEN
46 l_curr_suffix :=
47 CASE p_param(i).parameter_id
48 WHEN eni_dbi_util_pkg.get_curr_prim THEN 'g' -- primary global currency
49 WHEN eni_dbi_util_pkg.get_curr_sec THEN 'g1' -- secondary global currency
50 ELSE 'f' -- functional currency
51 END;
52 ELSE null;
53 END CASE;
54 END LOOP;
55
56 IF l_order_by like '%DESC%' THEN
57 l_order_by := ' DESC';
58 ELSE
59 l_order_by := ' ASC';
60 END IF;
61
62 l_all_prods := (l_prod IS NULL OR l_prod = '' OR l_prod = 'All');
63 l_all_prod_cats := (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All');
64 l_all_custs := (l_cust IS NULL OR l_cust = '' OR l_cust = 'All');
65
66 IF ( l_all_prods AND
67 l_all_custs )
68 THEN
69
70 l_mv1 := 'ISC_DBI_CFM_009_MV';
71 l_mv2 := 'ISC_DBI_CFM_011_MV';
72
73 l_flags_where := '
74 AND fact.inv_org_flag = 1';
75 -- look at rollups across all inv_org_ids
76
77 IF l_all_prod_cats THEN
78 l_prod_cat_where := '
79 AND fact.top_node_flag = ''Y'' '; -- no cat specified, so examine top nodes only
80 ELSE
81 l_prod_cat_where := '
82 AND fact.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)'; -- cat specified
83 END IF;
84 ELSE
85 l_mv1 := 'ISC_DBI_CFM_000_MV';
86 l_mv2 := 'ISC_DBI_CFM_002_MV';
87 l_flags_where := '
88 AND fact.item_cat_flag = :ENI_ITEM_CAT_FLAG
89 AND fact.customer_flag = :ENI_CUST_FLAG';
90
91 IF l_all_prod_cats THEN
92 l_prod_cat_from := '';
93 l_prod_cat_where := '';
94 ELSE
95 l_prod_cat_from := '
96 , ENI_DENORM_HIERARCHIES eni_cat
97 , MTL_DEFAULT_CATEGORY_SETS mdcs';
98 l_prod_cat_where := '
99 AND fact.item_category_id = eni_cat.child_id
100 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
101 AND eni_cat.dbi_flag = ''Y''
102 AND eni_cat.object_type = ''CATEGORY_SET''
103 AND eni_cat.object_id = mdcs.category_set_id
104 AND mdcs.functional_area_id = 11';
105 END IF;
106
107 IF l_all_prods
108 THEN l_prod_where := '';
109 ELSE l_prod_where := '
110 AND fact.master_item_id IN (&ITEM+ENI_ITEM)';
111 END IF;
112
113 IF l_all_custs THEN
114 l_cust_where := '';
115 l_cust_flag := 1;
116 ELSE
117 l_cust_where := '
118 AND fact.customer_id IN (&CUSTOMER+FII_CUSTOMERS)';
119 l_cust_flag := 0;
120 END IF;
121
122 IF l_all_prods THEN
123 IF l_all_prod_cats
124 THEN l_item_cat_flag := 3; -- category
125 ELSE l_item_cat_flag := 1; -- all
126 END IF;
127 ELSE
128 l_item_cat_flag := 0; -- product
129 END IF;
130
131 END IF;
132
133 l_stmt := '
134 SELECT fii.name VIEWBY
135 , nvl(s.prev_booked_value, 0) ENI_MEASURE1 -- book prior
136 , nvl(s.curr_booked_value, 0) ENI_MEASURE2 -- book
137 , (s.curr_booked_value-s.prev_booked_value)
138 / decode(s.prev_booked_value, 0, NULL,
139 abs(s.prev_booked_value)) * 100 ENI_MEASURE3 -- book change
140 , nvl(s.prev_fulfill_value, 0) ENI_MEASURE4 -- fulf prior
141 , nvl(s.curr_fulfill_value, 0) ENI_MEASURE5 -- fulf
142 , (s.curr_fulfill_value-s.prev_fulfill_value)
143 / decode(s.prev_fulfill_value, 0, NULL,
144 abs(s.prev_fulfill_value)) * 100 ENI_MEASURE6 -- fulf change
145 , s.prev_booked_value
146 / decode(s.prev_fulfill_value, 0, NULL,
147 s.prev_fulfill_value) ENI_MEASURE7 -- book to fulf r prior
148 , s.curr_booked_value
149 / decode(s.curr_fulfill_value, 0, NULL,
150 s.curr_fulfill_value) ENI_MEASURE8 -- book to fulf r
151 , s.curr_booked_value
152 / decode(s.curr_fulfill_value, 0, NULL,
153 s.curr_fulfill_value) -
154 s.prev_booked_value
155 / decode(s.prev_fulfill_value, 0, NULL,
156 s.prev_fulfill_value) ENI_MEASURE9 -- book to fulf r change
157 FROM (SELECT start_date START_DATE
158 , sum(curr_booked_value) CURR_BOOKED_VALUE
159 , sum(prev_booked_value) PREV_BOOKED_VALUE
160 , sum(curr_fulfill_value) CURR_FULFILL_VALUE
161 , sum(prev_fulfill_value) PREV_FULFILL_VALUE
162 FROM
163 (SELECT dates.start_date START_DATE
164 , fact.inv_org_id INV_ORG
165 , decode(dates.period, ''C'',
166 nvl(fact.booked_amt_'||l_curr_suffix||',0), 0) CURR_BOOKED_VALUE
167 , decode(dates.period, ''P'',
168 nvl(fact.booked_amt_'||l_curr_suffix||',0), 0) PREV_BOOKED_VALUE
169 , 0 CURR_FULFILL_VALUE
170 , 0 PREV_FULFILL_VALUE
171 FROM ( SELECT fii.start_date START_DATE
172 , ''C'' PERIOD
173 , least(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
174 FROM '||l_period_type||' fii
175 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
176 AND &BIS_CURRENT_ASOF_DATE
177 UNION ALL
178 SELECT p2.start_date START_DATE
179 , ''P'' PERIOD
180 , p1.report_date REPORT_DATE
181 FROM (SELECT
182 least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE,
183 rownum ID
184 FROM '||l_period_type||' fii
185 WHERE fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
186 AND &BIS_PREVIOUS_ASOF_DATE
187 ORDER BY fii.start_date DESC ) p1,
188 (SELECT fii.start_date START_DATE,
189 rownum ID
190 FROM '||l_period_type||' fii
191 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
192 AND &BIS_CURRENT_ASOF_DATE
193 ORDER BY fii.start_date DESC ) p2
194 WHERE p1.id(+) = p2.id
195 ) dates
196 , '||l_mv1||' fact
197 , FII_TIME_RPT_STRUCT cal'||l_prod_cat_from||'
198 WHERE cal.report_date = dates.report_date
199 AND fact.time_id = cal.time_id
200 AND fact.period_type_id = cal.period_type_id'
201 ||l_flags_where||'
202 AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'
203 ||l_prod_cat_where
204 ||l_prod_where
205 ||l_cust_where||'
206 UNION ALL
207 SELECT dates.start_date START_DATE
208 , fact.inv_org_id INV_ORG
209 , 0 CURR_BOOKED_VALUE
210 , 0 PREV_BOOKED_VALUE
211 , decode(dates.period, ''C'',
212 nvl(fact.fulfilled_amt_'||l_curr_suffix||',0), 0) CURR_FULFILL_VALUE
213 , decode(dates.period, ''P'',
214 nvl(fact.fulfilled_amt_'||l_curr_suffix||',0), 0) PREV_FULFILL_VALUE
215 FROM ( SELECT fii.start_date START_DATE
216 , ''C'' PERIOD
217 , least(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
218 FROM '||l_period_type||' fii
219 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
220 AND &BIS_CURRENT_ASOF_DATE
221 UNION ALL
222 SELECT p2.start_date START_DATE
223 , ''P'' PERIOD
224 , p1.report_date REPORT_DATE
225 FROM (SELECT
226 least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE
227 , rownum ID
228 FROM '||l_period_type||' fii
229 WHERE fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
230 AND &BIS_PREVIOUS_ASOF_DATE
231 ORDER BY fii.start_date DESC ) p1,
232 (SELECT fii.start_date START_DATE
233 , rownum ID
234 FROM '||l_period_type||' fii
235 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
236 AND &BIS_CURRENT_ASOF_DATE
237 ORDER BY fii.start_date DESC ) p2
238 WHERE p1.id(+) = p2.id
239 ) dates
240 , '||l_mv2||' fact
241 , FII_TIME_RPT_STRUCT cal'||l_prod_cat_from||'
242 WHERE cal.report_date = dates.report_date
243 AND fact.time_id = cal.time_id
244 AND fact.period_type_id = cal.period_type_id'
245 ||l_flags_where||'
246 AND fact.return_flag = 0
247 AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'
248 ||l_prod_cat_where
249 ||l_prod_where
250 ||l_cust_where||' )
251 -- WHERE '||l_inv_org_where||'
252 GROUP BY start_date) s,
253 '||l_period_type||' fii
254 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
255 AND &BIS_CURRENT_ASOF_DATE
256 AND fii.start_date = s.start_date(+)
257 ORDER BY fii.start_date ' || l_order_by;
258 -- || ' -- CURR: ' || l_curr;
259
260 x_custom_sql := l_stmt;
261
262 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
263 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
264
265 l_custom_rec.attribute_name := ':ENI_ITEM_CAT_FLAG';
266 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
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(1) := l_custom_rec;
271
272 l_custom_rec.attribute_name := ':ENI_CUST_FLAG';
273 l_custom_rec.attribute_value := to_char(l_cust_flag);
274 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
275 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
276 x_custom_output.extend;
277 x_custom_output(2) := l_custom_rec;
278
279 END Get_Sql;
280
281 END ENI_DBI_FPT_PKG;