[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_OEX_PKG
Source
1 PACKAGE BODY eni_dbi_oex_pkg AS
2 /*$Header: ENIOEXPB.pls 120.2 2006/03/23 04:39:16 pgopalar 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(100);
23 l_org VARCHAR2(100);
24 l_id_column VARCHAR2(100);
25 l_order_by VARCHAR2(100);
26 l_drill VARCHAR2(100);
27 l_status VARCHAR2(100);
28 l_priority VARCHAR2(100);
29 l_reason VARCHAR2(100);
30 l_lifecycle_phase VARCHAR2(100);
31 l_currency VARCHAR2(100);
32 l_bom_type VARCHAR2(100);
33 l_type VARCHAR2(100);
34 l_manager VARCHAR2(100);
35 l_lob VARCHAR2(100);
36
37 l_from_clause VARCHAR2(1000);
38 l_where_clause VARCHAR2(1000);
39 l_group_by_clause VARCHAR2(500);
40 l_lookup_group VARCHAR2(500);
41
42 l_summary VARCHAR2(100);
43 l_view_by_col VARCHAR2(100);
44 l_group_by_col VARCHAR2(100);
45 l_lookup_select VARCHAR2(100);
46 l_lookup VARCHAR2(100);
47 l_drill_to_cat_url VARCHAR2(500);
48
49 l_err_msg VARCHAR2(100);
50 l_cbo_hint VARCHAR2(100);
51 /* PERF FIX:
52 added due to CBO performing a FTS of FTRS; please check explain plans when modifying the
53 generated SQL */
54
55 -- The record structure for bind variable values
56 l_custom_rec BIS_QUERY_ATTRIBUTES;
57
58 l_currency_column varchar2(30);
59 l_currency_string varchar2(100);
60 BEGIN
61 eni_dbi_util_pkg.get_parameters
62 (
63 p_page_parameter_tbl,
64 l_period_type,
65 l_period_bitand,
66 l_view_by,
67 l_as_of_date,
68 l_prev_as_of_date,
69 l_report_start,
70 l_cur_period,
71 l_days_into_period,
72 l_comp_type,
73 l_category,
74 l_item,
75 l_org,
76 l_id_column,
77 l_order_by,
78 l_drill,
79 l_status,
80 l_priority,
81 l_reason,
82 l_lifecycle_phase,
83 l_currency,
84 l_bom_type,
85 l_type,
86 l_manager,
87 l_lob
88 );
89
90 /* To provide secondary currency support */
91 l_currency := TRIM(both '''' from l_currency);
92 l_currency_string := eni_dbi_util_pkg.get_curr_prim;
93 l_currency_string := TRIM(both '''' from l_currency_string);
94
95 IF l_currency = l_currency_string THEN
96 l_currency_column := 'PRIM_ACTUAL_G';
97 ELSE
98 l_currency_column := 'SEC_ACTUAL_G';
99 END IF;
100 /* To provide secondary currency support */
101
102 eni_dbi_util_pkg.get_time_clauses
103 (
104 'A',
105 'fgbm',
106 l_period_type,
107 l_period_bitand,
108 l_as_of_date,
109 l_prev_as_of_date,
110 l_report_start,
111 l_cur_period,
112 l_days_into_period,
113 l_comp_type,
114 l_id_column,
115 l_from_clause,
116 l_where_clause,
117 l_group_by_clause
118 );
119
120
121 l_where_clause := NULL;
122
123 IF l_view_by = 'LOB+FII_LOB' THEN
124
125 l_summary := 'eni_dbi_gl_base_sum_mv';
126
127 l_view_by_col := 'lookupv.value AS VIEWBY, lookupv.id VIEWBYID ';
128 l_group_by_col := 'lookupv.value, lookupv.id ';
129 l_lookup := 'fii_lob_v lookupv';
130 l_where_clause := l_where_clause ||
131 ' AND fgbm.line_of_business_id = lookupv.id ' ||
132 ' AND fgbm.line_of_business_id = lookupv.parent_id ' ||
133 ' AND fgbm.gid = 0';
134 -- using fgbm.line_of_business_id => no FTS in explain plan
135 l_drill_to_cat_url := 'NULL';
136
137 IF l_category IS NOT NULL THEN
138 l_where_clause := l_where_clause ||
139 ' AND fgbm.product_category_id = :CATEGORY_ID '|| --|| l_category || Bug 5083699
140 ' AND fgbm.marker = 1';
141
142 ELSE -- l_category IS NULL THEN
143 l_where_clause := l_where_clause ||
144 ' AND fgbm.marker = 3';
145 END IF;
146
147 ELSIF l_view_by = 'ITEM+ENI_ITEM_VBH_CAT' THEN
148
149 l_summary := 'eni_dbi_gl_base_sum_c_mv';
150 l_cbo_hint := '/*+ LEADING (ftrs) */';
151
152 l_view_by_col := 'lookupv.value AS VIEWBY, lookupv.id VIEWBYID, lookupv.leaf_node_flag ';
153 l_group_by_col := 'lookupv.value, lookupv.id, lookupv.leaf_node_flag ';
154 l_lookup := 'eni_item_vbh_nodes_v lookupv';
155 l_where_clause := l_where_clause ||
156 ' AND fgbm.CHILD_PROD_CAT_ID = lookupv.id ' ||
157 ' AND fgbm.CHILD_PROD_CAT_ID = lookupv.parent_id ';
158 -- using fgbm.CHILD_PROD_CAT_ID => no FTS in explain plan
159 l_drill_to_cat_url := '
160 decode(leaf_node_flag, ''Y'',
161 NULL,
162 ''pFunctionName=ENI_DBI_OEX_R' || '&' || 'VIEW_BY_NAME=VIEW_BY_ID' || '&' || 'VIEW_BY=ITEM+ENI_ITEM_VBH_CAT' || '&' || 'pParamIds=Y'') ';
163
164 IF l_category IS NOT NULL THEN
165 -- viewing at the category level
166
167 l_where_clause := l_where_clause ||
168 ' AND fgbm.marker = 2 AND fgbm.parent_prod_cat_id = :CATEGORY_ID '; --|| l_category; Bug 5083699
169 ELSE -- l_category IS NULL THEN
170
171 l_where_clause := l_where_clause ||
172 ' AND fgbm.parent_prod_cat_id = -2' ||
173 ' AND fgbm.marker = 1 ';
174 END IF;
175 END IF;
176
177 x_custom_sql :=
178 ' SELECT '|| l_cbo_hint ||'
179 VIEWBY
180 ,VIEWBYID
181 , ' || l_drill_to_cat_url || ' AS ENI_ATTRIBUTE2
182 ,ENI_MEASURE1
183 ,ENI_MEASURE2
184 ,ENI_MEASURE4
185 ,ENI_MEASURE5
186 FROM
187 (
188 SELECT ' || l_view_by_col || ',
189 SUM
190 (
191 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
192 then
193 ' || l_currency_column || '
194 else
195 0
196 end
197 ) AS ENI_MEASURE1,
198 SUM
199 (
200 case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
201 then
202 ' || l_currency_column || '
203 else
204 0
205 end
206 ) AS ENI_MEASURE2,
207
208 SUM
209 (
210 SUM
211 (
212 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
213 then
214 ' || l_currency_column || '
215 else
216 0
217 end
218 )
219 ) OVER() AS ENI_MEASURE4,
220
221 SUM
222 (
223 SUM
224 (
225 case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
226 then
227 ' || l_currency_column || '
228 else
229 0
230 end
231 )
232 ) OVER() AS ENI_MEASURE5
233 FROM
234 ' || l_summary || ' fgbm,
235 fii_time_rpt_struct ftrs,
236 ' || l_lookup || '
237 WHERE
238 fgbm.time_id = ftrs.time_id
239 AND
240 (
241 ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
242 OR ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
243 )
244 AND BITAND(ftrs.record_type_id, ' || '&' || 'BIS_NESTED_PATTERN) = ftrs.record_type_id
245 ' || l_where_clause || '
246 GROUP BY
247 ' || l_group_by_col || '
248 )
249 ORDER BY
250 ' || l_order_by;
251
252 --Bug 5083699 : Added Bind Parameter for CategoryId
253 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
254 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
255
256 l_custom_rec.attribute_name := ':CATEGORY_ID';
257 l_custom_rec.attribute_value := replace(l_category,'''');
258 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
259 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
260 x_custom_output.extend;
261 x_custom_output(1) := l_custom_rec;
262
263 --Bug 5083652 -- Start Code
264
265 x_custom_output.extend;
266 l_custom_rec.attribute_name := ':PERIODTYPE';
267 l_custom_rec.attribute_value := REPLACE(l_period_type,'''');
268 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
269 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
270 x_custom_output(2) := l_custom_rec;
271
272 x_custom_output.extend;
273 l_custom_rec.attribute_name := ':COMPARETYPE';
274 l_custom_rec.attribute_value := REPLACE(l_comp_type,'''');
275 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
276 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
277 x_custom_output(3) := l_custom_rec;
278
279
280 x_custom_output.extend;
281 l_custom_rec.attribute_name := ':PERIODAND';
282 l_custom_rec.attribute_value := REPLACE(l_period_bitand,'''');
283 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
284 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
285 x_custom_output(4) := l_custom_rec;
286
287 x_custom_output.extend;
288 l_custom_rec.attribute_name := ':CUR_PERIOD_ID';
289 l_custom_rec.attribute_value := REPLACE(l_cur_period,'''');
290 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
291 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
292 x_custom_output(5) := l_custom_rec;
293
294 --Bug 5083652 -- End Code
295 END get_sql;
296
297 END eni_dbi_oex_pkg;