[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_PMS_KPI_PKG
Source
1 PACKAGE BODY eni_dbi_pms_kpi_pkg AS
2 /* $Header: ENIPMSPB.pls 120.2 2006/03/23 04:42:05 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(1000);
40
41 l_err_msg VARCHAR2(100);
42
43 -- The record structure for bind variable values
44 l_custom_rec BIS_QUERY_ATTRIBUTES;
45
46 l_view_by_col VARCHAR2(100);
47 l_group_by_col VARCHAR2(100);
48 l_lookup VARCHAR2(100);
49 l_summary VARCHAR2(100);
50
51 BEGIN
52
53 eni_dbi_util_pkg.get_parameters
54 (
55 p_page_parameter_tbl,
56 l_period_type,
57 l_period_bitand,
58 l_view_by,
59 l_as_of_date,
60 l_prev_as_of_date,
61 l_report_start,
62 l_cur_period,
63 l_days_into_period,
64 l_comp_type,
65 l_category,
66 l_item,
67 l_org,
68 l_id_column,
69 l_order_by,
70 l_drill,
71 l_status,
72 l_priority,
73 l_reason,
74 l_lifecycle_phase,
75 l_currency,
76 l_bom_type,
77 l_type,
78 l_manager,
79 l_lob
80 );
81
82 eni_dbi_util_pkg.get_time_clauses
83 (
84 'A',
85 'fgbm',
86 l_period_type,
87 l_period_bitand,
88 l_as_of_date,
89 l_prev_as_of_date,
90 l_report_start,
91 l_cur_period,
92 l_days_into_period,
93 l_comp_type,
94 l_id_column,
95 l_from_clause,
96 l_where_clause,
97 l_group_by_clause
98 );
99
100 l_where_clause := NULL;
101
102 IF l_category IS NOT NULL THEN
103
104 l_where_clause := l_where_clause ||
105 --' AND parent_prod_cat_id = ' || l_category;
106 ' AND parent_prod_cat_id = :CATEGORY'; -- Bug 5083911
107
108
109 ELSIF l_category IS NULL THEN
110
111 l_where_clause := l_where_clause ||
112 ' AND parent_prod_cat_id = -2 ';
113
114 END IF;
115
116 -- All items in a specific category
117 x_custom_sql :=
118 '
119 SELECT
120 SUM
121 (
122 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
123 then
124 fars.rev_prim_actual_g
125 else
126 0
127 end
128 ) AS ENI_MEASURE1,
129 SUM
130 (
131 case
132 when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
133 then
134 fars.rev_prim_actual_g
135 else
136 0
137 end
138 ) AS ENI_MEASURE2,
139 SUM
140 (
141 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
142 then
143 fars.cogs_value_g
144 else
145 0
146 end
147 ) AS ENI_MEASURE5,
148 SUM
149 (
150 case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
151 then
152 fars.cogs_value_g
153 else
154 0
155 end
156 ) AS ENI_MEASURE6,
157 SUM
158 (
159 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
160 then
161 fars.prim_actual_g
162 else
163 0
164 end
165 ) AS ENI_MEASURE7,
166 SUM
167 (
168 case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
169 then
170 fars.prim_actual_g
171 else
172 0
173 end
174 ) AS ENI_MEASURE8,
175 SUM
176 (
177 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
178 then
179 ((fars.rev_prim_actual_g - (cogs_value_g + prim_actual_g))
180 /decode(fars.rev_prim_actual_g, 0, null, fars.rev_prim_actual_g))*100
181 else
182 0
183 end
184 ) AS ENI_MEASURE9,
185 SUM
186 (
187 case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
188 then
189 ((fars.rev_prim_actual_g - (cogs_value_g + prim_actual_g))
190 /decode(fars.rev_prim_actual_g, 0, null, fars.rev_prim_actual_g))*100
191 else
192 0
193 end
194 ) AS ENI_MEASURE10,
195 SUM
196 (
197 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
198 then
199 fars.inv_total_value_g
200 else
201 0
202 end
203 ) AS ENI_MEASURE11,
204 SUM
205 (
206 case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
207 then
208 fars.inv_total_value_g
209 else
210 0
211 end
212 ) AS ENI_MEASURE12
213 FROM
214 eni_dbi_prc_sum_c_mv fars,
215 fii_time_rpt_struct ftrs
216 WHERE
217 fars.time_id = ftrs.time_id
218 AND
219 (
220 ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
221 OR ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
222 )
223 --AND BITAND(ftrs.record_type_id, ' || l_period_bitand || ') = ftrs.record_type_id
224 AND BITAND(ftrs.record_type_id,:PERIODAND) = ftrs.record_type_id --Bug 5083911
225 ' || l_where_clause;
226
227
228 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
229 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
230 x_custom_output.extend;
231
232 l_custom_rec.attribute_name := ':ITEM+ENI_ITEM';
233 l_custom_rec.attribute_value := 5;
234 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
235 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
236 x_custom_output.extend;
237 x_custom_output(1) := l_custom_rec;
238
239 -- Start Bug 5083911
240 x_custom_output.extend;
241 l_custom_rec.attribute_name := ':CATEGORY';
242 l_custom_rec.attribute_value :=replace(l_category,'''');
243 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
244 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
245 x_custom_output(2) := l_custom_rec;
246
247 x_custom_output.extend;
248 l_custom_rec.attribute_name := ':PERIODAND'; --Bug 5083652
249 l_custom_rec.attribute_value := replace(l_period_bitand,'''');
250 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
251 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
252 x_custom_output(3) := l_custom_rec;
253
254 -- End Bug 5083911
255
256 --Bug 5083652 -- Start Code
257
258 x_custom_output.extend;
259 l_custom_rec.attribute_name := ':PERIODTYPE';
260 l_custom_rec.attribute_value := REPLACE(l_period_type,'''');
261 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
262 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
263 x_custom_output(8) := l_custom_rec;
264
265 x_custom_output.extend;
266 l_custom_rec.attribute_name := ':COMPARETYPE';
267 l_custom_rec.attribute_value := REPLACE(l_comp_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(9) := l_custom_rec;
271
272 x_custom_output.extend;
273 l_custom_rec.attribute_name := ':CUR_PERIOD_ID';
274 l_custom_rec.attribute_value := REPLACE(l_cur_period,'''');
275 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
276 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
277 x_custom_output(11) := l_custom_rec;
278
279 --Bug 5083652 -- End Code
280
281 END get_sql;
282
283 END eni_dbi_pms_kpi_pkg;