[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_IVA_T_PKG
Source
1 PACKAGE BODY eni_dbi_iva_t_pkg AS
2 /*$Header: ENIIVTPB.pls 120.1.12000000.2 2007/02/22 08:52:07 lparihar ship $*/
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(100);
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(1000);
41
42 l_comp_where VARCHAR2(100);
43
44 l_summary VARCHAR2(100);
45
46 l_err_msg VARCHAR2(100);
47
48 -- The record structure for bind variable values
49 l_custom_rec BIS_QUERY_ATTRIBUTES;
50
51 l_curr_suffix VARCHAR2(20);
52 l_g_curr_prim CONSTANT VARCHAR2(15) := '''FII_GLOBAL1''';
53 l_g_curr_sec CONSTANT VARCHAR2(15) := '''FII_GLOBAL2''';
54
55 BEGIN
56
57 for i in 1..p_page_parameter_tbl.COUNT
58 LOOP
59 IF ((p_page_parameter_tbl(i).parameter_name = 'ITEM+ENI_ITEM')
60 OR (p_page_parameter_tbl(i).parameter_name = 'ITEM+ENI_ITEM_ORG')) THEN
61
62 l_item_org := p_page_parameter_tbl(i).parameter_id;
63
64 END IF;
65 END LOOP;
66
67 eni_dbi_util_pkg.get_parameters(
68 p_page_parameter_tbl,
69 l_period_type,
70 l_period_bitand,
71 l_view_by,
72 l_as_of_date,
73 l_prev_as_of_date,
74 l_report_start,
75 l_cur_period,
76 l_days_into_period,
77 l_comp_type,
78 l_category,
79 l_item,
80 l_org,
81 l_id_column,
82 l_order_by,
83 l_drill,
84 l_status,
85 l_priority,
86 l_reason,
87 l_lifecycle_phase,
88 l_currency,
89 l_bom_type,
90 l_type,
91 l_manager,
92 l_lob
93 );
94
95 l_where_clause := NULL;
96
97 IF (l_item IS NULL AND l_category IS NULL) THEN
98
99 l_from_clause := 'eni_dbi_inv_sum_mv edismv';
100 l_where_clause := ' edismv.marker = 1 AND edismv.time_id (+) = ftrs.time_id ';
101
102 ELSIF (l_item IS NULL AND l_category IS NOT NULL) THEN
103
104 l_from_clause := 'eni_dbi_inv_sum_mv edismv';
105 /**Bug 5843937*/
106 l_where_clause := ' edismv.organization_id IS NOT NULL AND edismv.marker = 2 AND edismv.time_id (+) = ftrs.time_id ';
107 l_where_clause := l_where_clause || 'AND edismv.product_category_id = :CATEGORY_ID '; -- || l_category; Bug 5083662
108
109 ELSIF (l_item IS NOT NULL AND l_category IS NULL) THEN
110
111 l_from_clause := 'eni_dbi_inv_sum_mv edismv';
112 l_where_clause := ' edismv.marker = 3 AND edismv.time_id (+) = ftrs.time_id ';
113 --Discussed with Lakshaman and replacing the IN clause with a join (for l_item_org)
114 l_where_clause := l_where_clause || ' AND edismv.item_org_id = :ITEM_ORG '; -- || l_item_org || Bug 5083662
115
116 ELSIF (l_item IS NOT NULL AND l_category IS NOT NULL) THEN
117
118 l_from_clause := 'eni_dbi_inv_sum_mv edismv, eni_denorm_hierarchies edh';
119 l_where_clause := ' edismv.marker = 3 AND edismv.time_id (+) = ftrs.time_id ';
120 --Discussed with Lakshaman and replacing the IN clause with a join (for l_item_org)
121 l_where_clause := l_where_clause || ' AND edismv.item_org_id = :ITEM_ORG '; -- || l_item_org || Bug 5083662
122 l_where_clause := l_where_clause || ' AND edh.parent_id = :CATEGORY_ID '|| --|| l_category || Bug 5083662
123 ' AND edismv.product_category_id = edh.child_id ';
124
125 END IF;
126
127 IF UPPER(l_order_by) LIKE '%START_DATE%ASC' THEN
128 l_order_by := 'start_date asc' ;
129 ELSIF UPPER(l_order_by) LIKE '%START_DATE%DESC' THEN
130 l_order_by := 'start_date desc' ;
131 ELSIF UPPER(l_order_by) LIKE '%START_DATE%' THEN
132 l_order_by := 'start_date asc' ;
133 END IF;
134
135 IF l_comp_type = 'SEQUENTIAL' THEN
136 l_comp_where := 'AND c.start_date = p.end_date + 1';
137 ELSIF l_period_type = 'FII_TIME_WEEK' THEN
138 l_comp_where := 'AND c.week_id = p.week_id + 10000';
139 ELSIF l_period_type = 'FII_TIME_ENT_PERIOD' THEN
140 l_comp_where := 'AND c.ent_period_id = p.ent_period_id + 1000';
141 ELSIF l_period_type = 'FII_TIME_ENT_QTR' THEN
142 l_comp_where := 'AND c.ent_qtr_id = p.ent_qtr_id + 10';
143 ELSIF l_period_type = 'FII_TIME_ENT_YEAR' THEN
144 l_comp_where := 'AND c.ent_year_id = p.ent_year_id + 1';
145 END IF;
146
147 l_curr_suffix :=
148 CASE l_currency
149 WHEN l_g_curr_sec THEN 'sg' -- secondary global currency
150 ELSE 'g' -- primary global currency (default)
151 END;
152
153 /*
154 Bug : 3258092
155 Desc: Values computed for XTD instead of ITD. Values stored in base table are instaneous not summary
156
157 Bug: 3123997
158 Inv Total, InTransit Value and WIP Value mustbe N/A if the item doesn't exist
159 Issue: NVL(edismv.xxxx,0)
160 Fix : edismv.xxxx
161 removed the 'else' clause as well
162 */
163
164 x_custom_sql := '
165 SELECT
166 date_name AS VIEWBY
167 , SUM(ENI_MEASURE1) AS ENI_MEASURE1
168 , SUM(ENI_MEASURE50) AS ENI_MEASURE50
169 , SUM(ENI_MEASURE2) AS ENI_MEASURE2
170 , SUM(ENI_MEASURE4) AS ENI_MEASURE4
171 , SUM(ENI_MEASURE5) AS ENI_MEASURE5
172 , SUM(ENI_MEASURE7) AS ENI_MEASURE7
173 , SUM(ENI_MEASURE8) AS ENI_MEASURE8
174 , SUM(ENI_MEASURE10) AS ENI_MEASURE10
175 , SUM(ENI_MEASURE11) AS ENI_MEASURE11
176 , SUM(ENI_MEASURE14) AS ENI_MEASURE14
177 , SUM(ENI_MEASURE15) AS ENI_MEASURE15
178 , SUM(ENI_MEASURE16) AS ENI_MEASURE16
179 FROM
180 (
181 (
182 SELECT
183 t.name AS date_name,
184 t.start_date AS start_date,
185 SUM
186 (
187 case when ftrs.report_date = t.c_end_date
188 THEN edismv.inv_total_value_'||l_curr_suffix||'
189 ELSE 0
190 END
191 ) AS ENI_MEASURE1,
192 SUM
193 (
194 case when ftrs.report_date = t.c_end_date
195 THEN edismv.inv_total_value_'||l_curr_suffix||'
196 ELSE 0
197 END
198 ) AS ENI_MEASURE50,
199 SUM
200 (
201 case when ftrs.report_date = t.p_end_date
202 THEN edismv.inv_total_value_'||l_curr_suffix||'
203 ELSE 0
204 END
205 ) AS ENI_MEASURE2,
206 SUM
207 (
208 case when ftrs.report_date = t.c_end_date
209 THEN edismv.onhand_value_'||l_curr_suffix||'
210 ELSE 0
211 END
212 ) AS ENI_MEASURE4,
213 SUM
214 (
215 case when ftrs.report_date = t.p_end_date
216 THEN edismv.onhand_value_'||l_curr_suffix||'
217 ELSE 0
218 END
219 ) AS ENI_MEASURE5,
220 SUM
221 (
222 case when ftrs.report_date = t.c_end_date
223 THEN edismv.intransit_value_'||l_curr_suffix||'
224 ELSE 0
225 END
226 ) AS ENI_MEASURE7,
227 SUM
228 (
229 case when ftrs.report_date = t.p_end_date
230 THEN edismv.intransit_value_'||l_curr_suffix||'
231 ELSE 0
232 END
233 ) AS ENI_MEASURE8,
234 SUM
235 (
236 case when ftrs.report_date = t.c_end_date
237 THEN edismv.wip_value_'||l_curr_suffix||'
238 ELSE 0
239 END
240 ) AS ENI_MEASURE10,
241 SUM
242 (
243 case when ftrs.report_date = t.p_end_date
244 THEN edismv.wip_value_'||l_curr_suffix||'
245 ELSE 0
246 END
247 ) AS ENI_MEASURE11,
248 10 AS ENI_MEASURE14,
249 11 AS ENI_MEASURE15,
250 12 AS ENI_MEASURE16
251 FROM
252 ' || l_from_clause ||' , fii_time_rpt_struct ftrs,
253 (
254 SELECT
255 c.name,
256 c.'||l_id_column||',
257 c.start_date AS start_date,
258 (case when '|| '&' || 'BIS_CURRENT_ASOF_DATE < c.end_date
259 then '|| '&' || 'BIS_CURRENT_ASOF_DATE else c.end_date end ) AS c_end_date,
260 (case when '|| '&' || 'BIS_PREVIOUS_ASOF_DATE < p.end_date
261 then '|| '&' || 'BIS_PREVIOUS_ASOF_DATE else p.end_date end ) AS p_end_date
262 FROM
263 ' || l_period_type ||' c, ' || l_period_type || ' p
264 WHERE
265 c.start_date >= ' || '&' || 'BIS_CURRENT_REPORT_START_DATE
266 AND c.'||l_id_column||' <= :CUR_PERIOD_ID '|| --Bug 5083662
267 'AND p.start_date >= ' || '&' || 'BIS_PREVIOUS_REPORT_START_DATE
268 ' || l_comp_where || '
269 ) t
270 WHERE
271 ' || l_where_clause || '
272 AND (
273 t.c_end_date = ftrs.report_date
274 OR t.p_end_date = ftrs.report_date
275 )
276 AND BITAND(ftrs.record_type_id, 1143) = ftrs.record_type_id
277 GROUP BY
278 t.name,t.start_date,t.c_end_date
279 )
280 UNION ALL
281 (
282 SELECT
283 c.name AS date_name
284 , c.start_date AS start_date
285 , NULL AS ENI_MEASURE1
286 , NULL AS ENI_MEASURE50
287 , NULL AS ENI_MEASURE2
288 , NULL AS ENI_MEASURE4
289 , NULL AS ENI_MEASURE5
290 , NULL AS ENI_MEASURE7
291 , NULL AS ENI_MEASURE8
292 , NULL AS ENI_MEASURE10
293 , NULL AS ENI_MEASURE11
294 , NULL AS ENI_MEASURE14
295 , NULL AS ENI_MEASURE15
296 , NULL AS ENI_MEASURE16
297 FROM
298 ' || l_period_type ||' c, ' || l_period_type || ' p
299 WHERE
300 c.start_date >= ' || '&' || 'BIS_CURRENT_REPORT_START_DATE
301 AND c.'||l_id_column||' <= :CUR_PERIOD_ID '|| --Bug 5083662
302 'AND p.start_date >= ' || '&' || 'BIS_PREVIOUS_REPORT_START_DATE
303 ' || l_comp_where || '
304 )
305 )
306 GROUP BY date_name,start_date
307 ORDER BY
308 ' || l_order_by;
309
310 --Bug 5083662 : Added Bind Parameters
311 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
312 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
313
314 x_custom_output.extend;
315 l_custom_rec.attribute_name := ':CATEGORY_ID';
316 l_custom_rec.attribute_value := REPLACE(l_category,'''');
317 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
318 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
319 x_custom_output(1) := l_custom_rec;
320
321 x_custom_output.extend;
322 l_custom_rec.attribute_name := ':CUR_PERIOD_ID';
323 l_custom_rec.attribute_value := REPLACE(l_cur_period,'''');
324 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
325 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
326 x_custom_output(2) := l_custom_rec;
327
328 x_custom_output.extend;
329 l_custom_rec.attribute_name := ':ITEM_ORG';
330 l_custom_rec.attribute_value := REPLACE(l_item_org,'''');
331 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
332 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
333 x_custom_output(3) := l_custom_rec;
334
335 EXCEPTION
336
337 WHEN OTHERS THEN
338 NULL;
339
340 END get_sql;
341
342 END eni_dbi_iva_t_pkg;