[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_PRC_T_PKG
Source
1 PACKAGE BODY eni_dbi_prc_t_pkg AS
2 /*$Header: ENIPRTPB.pls 120.0 2005/05/26 19:38:53 appldev noship $*/
3
4 PROCEDURE get_sql
5 (
6 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
7 x_custom_sql OUT NOCOPY VARCHAR2,
8 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
9 )
10 IS
11
12 l_period_type VARCHAR2(100);
13 l_period_bitand NUMBER;
14 l_view_by VARCHAR2(100);
15 l_as_of_date DATE;
16 l_prev_as_of_date DATE;
17 l_report_start DATE;
18 l_cur_period NUMBER;
19 l_days_into_period NUMBER;
20 l_comp_type VARCHAR2(100);
21 l_category VARCHAR2(100);
22 l_item VARCHAR2(5000);
23 l_org VARCHAR2(5000);
24 l_item_org VARCHAR2(5000);
25 l_id_column VARCHAR2(100);
26 l_order_by VARCHAR2(100);
27 l_drill VARCHAR2(10);
28 l_status VARCHAR2(100);
29 l_priority VARCHAR2(100);
30 l_reason VARCHAR2(100);
31 l_lifecycle_phase VARCHAR2(100);
32 l_currency VARCHAR2(100);
33 l_bom_type VARCHAR2(100);
34 l_type VARCHAR2(100);
35 l_manager VARCHAR2(100);
36 l_lob VARCHAR2(100);
37
38 l_from_clause VARCHAR2(1000);
39 l_where_clause VARCHAR2(1000) := NULL;
40 l_group_by_clause VARCHAR2(500);
41
42 l_comp_where VARCHAR2(100);
43
44 l_err_msg VARCHAR2(100);
45
46 l_table VARCHAR2(100);
47
48 -- The record structure for bind variable values
49 l_custom_rec BIS_QUERY_ATTRIBUTES;
50
51 l_view_by_col VARCHAR2(100);
52 l_group_by_col VARCHAR2(100);
53 l_lookup VARCHAR2(100);
54 l_summary VARCHAR2(100);
55 l_oex_columns VARCHAR2(1000) := NULL;
56 l_drill_params VARCHAR2(400);
57 l_drill_to_other_expenses VARCHAR2(500);
58 l_revenue VARCHAR2(100);
59 l_cogs VARCHAR2(100);
60 l_expense VARCHAR2(100);
61 l_currency_value VARCHAR2(100);
62
63 BEGIN
64
65 l_revenue := 'rev_amount';
66 l_cogs := 'cogs_amount';
67 l_expense := 'exp_amount';
68
69
70 for i in 1..p_page_parameter_tbl.COUNT
71 LOOP
72 IF ((p_page_parameter_tbl(i).parameter_name = 'ITEM+ENI_ITEM')
73 OR (p_page_parameter_tbl(i).parameter_name = 'ITEM+ENI_ITEM_ORG')) THEN
74 l_item_org := p_page_parameter_tbl(i).parameter_id;
75 EXIT;
76 END IF;
77 END LOOP;
78
79 eni_dbi_util_pkg.get_parameters
80 (
81 p_page_parameter_tbl,
82 l_period_type,
83 l_period_bitand,
84 l_view_by,
85 l_as_of_date,
86 l_prev_as_of_date,
87 l_report_start,
88 l_cur_period,
89 l_days_into_period,
90 l_comp_type,
91 l_category,
92 l_item,
93 l_org,
94 l_id_column,
95 l_order_by,
96 l_drill,
97 l_status,
98 l_priority,
99 l_reason,
100 l_lifecycle_phase,
101 l_currency,
102 l_bom_type,
103 l_type,
104 l_manager,
105 l_lob
106 );
107
108 l_currency_value := eni_dbi_util_pkg.get_curr_sec;
109
110 -- l_currency := TRIM(both '''' from l_currency);
111
112 IF (l_currency = l_currency_value) THEN
113 l_revenue := 'rev_sec_amount';
114 l_cogs := 'cogs_sec_amount';
115 l_expense := 'exp_sec_amount';
116 END IF;
117
118 l_drill_to_other_expenses := 'decode(ENI_MEASURE13, 0, NULL, NULL, NULL,''pFunctionName=ENI_DBI_OEX_R' ||
119 '&' || 'VIEW_BY_NAME=VIEW_BY_ID' ||
120 '&' || 'VIEW_BY=LOB+FII_LOB' || '&' || 'pParamIds=Y'') ';
121 l_summary := 'edps2mv';
122 l_oex_columns := '
123 , SUM
124 (
125 case when ftrs.report_date = t.c_end_date
126 then
127 ' ||l_summary|| '.' || l_expense || '
128 else
129 NULL
130 end
131 )
132 AS ENI_MEASURE13
133 , SUM
134 (
135 case when ftrs.report_date = t.p_end_date
136 then
137 ' ||l_summary|| '.' || l_expense || '
138 else
139 NULL
140 end
141 )
142 AS ENI_MEASURE14 ';
143 IF (l_item IS NULL AND l_category IS NULL) THEN
144 /* l_from_clause := ' (select * from eni_dbi_prc_sum2_mv ' ||
145 ' where marker = 1) edps2mv'; */
146 l_from_clause := 'eni_dbi_prc_sum2_mv edps2mv';
147 l_where_clause := 'edps2mv.marker = 1 and
148 edps2mv.time_id (+) = ftrs.time_id';
149 ELSIF (l_item IS NULL AND l_category IS NOT NULL) THEN
150 l_oex_columns := ' , null AS ENI_MEASURE13, null AS ENI_MEASURE14 ';
151 /* l_from_clause := ' (select * from eni_dbi_prc_sum2_mv edps2mv ' ||
152 ' where edps2mv.marker = 2 ' ||
153 ' AND edps2mv.product_category_id = '|| l_category ||' ) edps2mv'; */
154 l_from_clause := 'eni_dbi_prc_sum2_mv edps2mv';
155 l_where_clause := 'edps2mv.marker = 2 and
156 edps2mv.time_id (+) = ftrs.time_id and
157 edps2mv.product_category_id = :PRODUCT_CATEGORY';
158 ELSIF (l_item IS NOT NULL AND l_category IS NULL) THEN
159 l_summary := 'edps1mv';
160 l_oex_columns := ' , null AS ENI_MEASURE13, null AS ENI_MEASURE14 ';
161 /* l_from_clause := ' (select * from eni_dbi_prc_sum1_mv edps1mv ' ||
162 ' where edps1mv.item_org_id IN (' || l_item_org || '))edps1mv ' ; */
163 l_from_clause := 'eni_dbi_prc_sum1_mv edps1mv';
164 l_where_clause := 'edps1mv.time_id (+) = ftrs.time_id and
165 edps1mv.item_org_id IN ('|| '&' || 'ITEM+ENI_ITEM)';
166 ELSIF (l_item IS NOT NULL AND l_category IS NOT NULL) THEN
167 l_summary := 'edps1mv';
168 l_oex_columns := ' , null AS ENI_MEASURE13, null AS ENI_MEASURE14 ';
169 /* l_from_clause := ' (select * from eni_dbi_prc_sum1_mv edps1mv ' ||
170 ' , eni_denorm_hierarchies edh ' ||
171 ' where edps1mv.item_org_id IN (' || l_item_org || ')' ||
172 ' AND edh.parent_id = '|| l_category ||
173 ' AND edh.child_id = edps1mv.product_category_id ) edps1mv'; */
174 l_from_clause := ' eni_dbi_prc_sum1_mv edps1mv, eni_denorm_hierarchies edh ';
175 l_where_clause := 'edps1mv.time_id (+) = ftrs.time_id and ' ||
176 ' edps1mv.item_org_id IN (' || '&' || 'ITEM+ENI_ITEM)' ||
177 ' AND edh.parent_id = :PRODUCT_CATEGORY' ||
178 ' AND edh.child_id = edps1mv.product_category_id ';
179 END IF;
180 IF UPPER(l_order_by) LIKE '%START_DATE%ASC' THEN
181 l_order_by := 'start_date asc' ;
182 ELSIF UPPER(l_order_by) LIKE '%START_DATE%DESC' THEN
183 l_order_by := 'start_date desc' ;
184 ELSIF UPPER(l_order_by) LIKE '%START_DATE%' THEN
185 l_order_by := 'start_date asc' ;
186 END IF;
187
188 IF l_comp_type = 'SEQUENTIAL' THEN
189 l_comp_where := 'AND c.start_date = p.end_date + 1';
190 ELSIF l_period_type = 'FII_TIME_WEEK' THEN
191 l_comp_where := 'AND c.week_id = p.week_id + 10000';
192 ELSIF l_period_type = 'FII_TIME_ENT_PERIOD' THEN
193 l_comp_where := 'AND c.ent_period_id = p.ent_period_id + 1000';
194 ELSIF l_period_type = 'FII_TIME_ENT_QTR' THEN
195 l_comp_where := 'AND c.ent_qtr_id = p.ent_qtr_id + 10';
196 ELSIF l_period_type = 'FII_TIME_ENT_YEAR' THEN
197 l_comp_where := 'AND c.ent_year_id = p.ent_year_id + 1';
198 END IF;
199
200 l_drill_params := '&' || 'VIEW_BY=LOB+FII_LOB';
201
202 x_custom_sql :=
203 '
204 SELECT
205 date_name AS VIEWBY
206 , SUM(ENI_MEASURE1) AS ENI_MEASURE1
207 , SUM(ENI_MEASURE2) AS ENI_MEASURE2
208 , SUM(ENI_MEASURE7) AS ENI_MEASURE7
209 , SUM(ENI_MEASURE8) AS ENI_MEASURE8
210 , SUM(ENI_MEASURE11) AS ENI_MEASURE11
211 , SUM(ENI_MEASURE17) AS ENI_MEASURE17
212 , SUM(ENI_MEASURE10) AS ENI_MEASURE10
213 , SUM(ENI_MEASURE13) AS ENI_MEASURE13
214 , SUM(ENI_MEASURE14) AS ENI_MEASURE14
215 , SUM(ENI_MEASURE16) AS ENI_MEASURE16
216 , SUM(ENI_MEASURE43) AS ENI_MEASURE43
217 , SUM(ENI_MEASURE47) AS ENI_MEASURE47
218 FROM
219 (
220 SELECT
221 date_name
222 , start_date -- start_date
223 , ENI_MEASURE1 -- current revenue
224 , ENI_MEASURE2 -- prior revenue
225 , ENI_MEASURE7 -- current cogs
226 , ENI_MEASURE8 -- prior cogs
227 , ((ENI_MEASURE2 - ENI_MEASURE8)
228 /decode(ENI_MEASURE2, 0, null, ENI_MEASURE2))*100
229 AS ENI_MEASURE11 -- prior gross margin
230 , ((ENI_MEASURE2 - ENI_MEASURE8 - ENI_MEASURE14)
231 /decode(ENI_MEASURE2, 0, null, ENI_MEASURE2))*100
232 AS ENI_MEASURE17 -- prior product margin
233 , ((ENI_MEASURE1 - ENI_MEASURE7)
234 /decode(ENI_MEASURE1, 0, null, ENI_MEASURE1))*100
235 AS ENI_MEASURE10 -- current gross margin
236 , ENI_MEASURE13 -- current other expenses
237 , ENI_MEASURE14 -- prior other expenses
238 , ((ENI_MEASURE1 - ENI_MEASURE7 - ENI_MEASURE13)
239 /decode(ENI_MEASURE1, 0, null, ENI_MEASURE1))*100
240 AS ENI_MEASURE16 -- current product margin
241 , NULL AS ENI_MEASURE43 -- drill across url for other expenses
242 -- Removed the drill for the bug # 3659784
243 , NVL(ENI_MEASURE7,0)+NVL(ENI_MEASURE13,0)
244 AS ENI_MEASURE47 -- for Costs(COGS+Expenses) on graph 1
245 FROM
246 (
247 SELECT
248 t.name AS date_name
249 ,t.start_date as start_date
250 ,SUM(
251 case when ftrs.report_date = t.c_end_date
252 then ' ||l_summary|| '.' || l_revenue || '
253 else NULL
254 end
255 ) AS ENI_MEASURE1
256 ,SUM(
257 case when ftrs.report_date = t.p_end_date
258 then ' ||l_summary|| '.' || l_revenue || '
259 else 0
260 end
261 ) AS ENI_MEASURE2
262 ,SUM(
263 case when ftrs.report_date = t.c_end_date
264 then ' ||l_summary|| '.' || l_cogs || '
265 else NULL
266 end
267 ) AS ENI_MEASURE7
268 ,SUM(
269 case when ftrs.report_date = t.p_end_date
270 then ' ||l_summary|| '.' || l_cogs || '
271 else 0
272 end
273 ) AS ENI_MEASURE8
274 '||l_oex_columns||'
275 ,SUM
276 (
277 case when ftrs.report_date = t.c_end_date
278 then NVL(((' ||l_summary|| '.' || l_revenue || ' - ' ||l_summary|| '.' || l_cogs || '
279 - ' ||l_summary|| '.' || l_expense || ')/decode(' ||l_summary|| '.' || l_revenue || ', 0, null, ' ||l_summary|| '.' || l_revenue || '))*100,0)
280 else NULL
281 end
282 ) AS ENI_MEASURE16
283 ,SUM
284 (
285 case when ftrs.report_date = t.p_end_date
286 then ((' ||l_summary|| '.' || l_revenue || ' - ' ||l_summary|| '.' || l_cogs || '
287 - ' ||l_summary|| '.' || l_expense || ')/decode(' ||l_summary|| '.' || l_revenue || ', 0, null, ' ||l_summary|| '.' || l_revenue || '))*100
288 else 0
289 end
290 ) AS ENI_MEASURE17
291 , DECODE( SUM( case when ftrs.report_date = t.c_end_date
292 then ' ||l_summary|| '.' || l_expense || '
293 else NULL end),
294 0, null, ''pFunctionName=ENI_DBI_OEX_R'||l_drill_params||''') -- drill across url for other expenses
295 AS ENI_MEASURE43
296 FROM
297 ' || l_from_clause || '
298 , fii_time_rpt_struct ftrs
299 , (
300 SELECT
301 c.name,
302 c.'||l_id_column||',
303 c.start_date AS start_date,
304 (case when '|| '&' || 'BIS_CURRENT_ASOF_DATE < c.end_date
305 then '|| '&' || 'BIS_CURRENT_ASOF_DATE else c.end_date end ) AS c_end_date,
306 (case when '|| '&' || 'BIS_PREVIOUS_ASOF_DATE < p.end_date
307 then '|| '&' || 'BIS_PREVIOUS_ASOF_DATE else p.end_date end ) AS p_end_date
308 FROM
309 ' || l_period_type ||' c, ' || l_period_type || ' p
310 WHERE
311 c.start_date >= ' || '&' || 'BIS_CURRENT_REPORT_START_DATE
312 AND c.'||l_id_column||' <= :PERIOD_ID ' || '
313 AND p.start_date >= ' || '&' || 'BIS_PREVIOUS_REPORT_START_DATE
314 ' || l_comp_where || '
315 ) t
316 WHERE
317 (
318 t.c_end_date = ftrs.report_date
319 OR t.p_end_date = ftrs.report_date
320 )
321 AND BITAND(ftrs.record_type_id, &' || 'BIS_NESTED_PATTERN) = ftrs.record_type_id
322 AND ' || l_where_clause || '
323 GROUP BY
324 t.name,t.start_date,t.c_end_date
325 )
326 UNION ALL
327 SELECT
328 c.name AS date_name
329 , c.start_date AS start_date
330 , NULL AS ENI_MEASURE1
331 , NULL AS ENI_MEASURE2
332 , NULL AS ENI_MEASURE7
333 , NULL AS ENI_MEASURE8
334 , NULL AS ENI_MEASURE11
335 , NULL AS ENI_MEASURE17
336 , NULL AS ENI_MEASURE10
337 , NULL AS ENI_MEASURE13
338 , NULL AS ENI_MEASURE14
339 , NULL AS ENI_MEASURE16
340 , NULL AS ENI_MEASURE43
341 , NULL AS ENI_MEASURE47
342 FROM
343 ' || l_period_type ||' c, ' || l_period_type || ' p
344 WHERE
345 c.start_date >= ' || '&' || 'BIS_CURRENT_REPORT_START_DATE
346 AND c.'||l_id_column||' <= :PERIOD_ID' || '
347 AND p.start_date >= ' || '&' || 'BIS_PREVIOUS_REPORT_START_DATE
348 ' || l_comp_where || '
349 )
350 GROUP BY date_name,start_date
351 ORDER BY
352 ' || l_order_by ;
353
354
355 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
356 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
357
358 x_custom_output.extend;
359 l_custom_rec.attribute_name := ':PERIOD_ID';
360 l_custom_rec.attribute_value := l_cur_period;
361 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
362 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
363 x_custom_output.extend;
364 x_custom_output(1) := l_custom_rec;
365
366 IF (l_category is not null ) THEN
367 x_custom_output.extend;
368 l_custom_rec.attribute_name := ':PRODUCT_CATEGORY';
369 l_custom_rec.attribute_value := TRIM(BOTH '''' FROM l_category);
370 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
371 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
372 x_custom_output.extend;
373 x_custom_output(2) := l_custom_rec;
374 END IF;
375
376 END get_sql;
377
378 END eni_dbi_prc_t_pkg;