[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_UCC_PKG
Source
1 PACKAGE BODY ENI_DBI_UCC_PKG AS
2 /* $Header: ENIUCCPB.pls 120.0 2005/05/26 19:33:13 appldev noship $ */
3
4
5 -- Returns query for the Cost by Cost Element report
6 PROCEDURE get_sql ( 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) IS
9
10 -- SQL statement for report
11 l_sql_stmt VARCHAR2(32000);
12
13 l_period_type VARCHAR2(40);
14 l_period_bitand NUMBER;
15 l_view_by VARCHAR2(200);
16 l_as_of_date DATE;
17 l_prev_as_of_date DATE;
18 l_report_start DATE;
19 l_cur_period NUMBER;
20 l_days_into_period NUMBER;
21 l_comp_type VARCHAR2(100);
22 l_category VARCHAR2(100);
23 l_item VARCHAR2(100);
24 l_org VARCHAR2(30);
25 l_id_column VARCHAR2(30);
26 l_order_by VARCHAR2(100);
27 l_drill VARCHAR2(30);
28 l_custom_rec BIS_QUERY_ATTRIBUTES;
29 l_err_msg VARCHAR2(100);
30 l_from_clause varchar2(1000);
31 l_where_clause varchar2(1000);
32 l_group_by_clause varchar2(1000);
33
34 l_comp_where VARCHAR2(100);
35 l_view_by_column VARCHAR2(30);
36 l_item_where VARCHAR2(100);
37
38 l_status varchar2(40);
39 l_priority varchar2(40);
40 l_reason varchar2(40);
41 l_lifecycle_phase varchar2(40);
42 l_currency varchar2(40);
43 l_currency_rate VARCHAR2(30);
44 l_bom_type varchar2(40);
45 l_type varchar2(40);
46 l_manager varchar2(40);
47 l_lob varchar2(100);
48 x varchar2(200);
49 y varchar2(200);
50
51 x_mtl_cost number;
52 x_mo_cost number;
53 x_res_cost number;
54 x_osp_cost number;
55 x_ovhd_cost number;
56 x_unit_cost number;
57 x_exists number;
58
59 x_prev_mtl_cost number;
60 x_prev_mo_cost number;
61 x_prev_res_cost number;
62 x_prev_osp_cost number;
63 x_prev_ovhd_cost number;
64 x_prev_unit_cost number;
65 x_prev_exists number;
66 l_total_pct_change number;
67
68
69 l_org_where VARCHAR2(1000);
70 l_cat_where VARCHAR2(1000);
71
72 BEGIN
73 ENI_DBI_UTIL_PKG.get_parameters( p_page_parameter_tbl
74 , l_period_type
75 , l_period_bitand
76 , l_view_by
77 , l_as_of_date
78 , l_prev_as_of_date
79 , l_report_start
80 , l_cur_period
81 , l_days_into_period
82 , l_comp_type
83 , l_category
84 , l_item
85 , l_org
86 , l_id_column
87 , l_order_by
88 , l_drill
89 , l_status
90 , l_priority
91 , l_reason
92 , l_lifecycle_phase
93 , l_currency
94 , l_bom_type
95 , l_type
96 , l_manager
97 , l_lob
98 );
99
100 -- Set currency rate based on currency chosen by user
101
102 l_currency_rate :=
103 CASE l_currency
104 WHEN ENI_DBI_UTIL_PKG.get_curr_sec
105 THEN 'secondary_currency_rate' -- secondary global currency
106 ELSE 'primary_currency_rate' -- primary global currency (default)
107 END;
108
109 if ((l_org IS NULL OR l_org = '' OR l_org = 'All') or (l_item IS NULL OR l_item = '' OR l_item = 'All')) then
110 l_sql_stmt := 'select null VIEWBY,
111 null ENI_MEASURE1, -- elemental_cost for table
112 null ENI_MEASURE2, -- change for table
113 null ENI_MEASURE3, -- pct_of_total for table and graph
114 null ENI_MEASURE4,
115 null ENI_MEASURE5,
116 null ENI_MEASURE6
117 from sys.dual' ;
118 else
119
120 l_period_type := '''' || l_period_type || '''';
121 l_comp_type := '''' || l_comp_type || '''';
122
123 l_sql_stmt := 'select cost_element VIEWBY,
124 decode(cost_element_id, 1, curr_mtl_cost,
125 2, curr_mtl_ovhd_cost,
126 3, curr_res_cost,
127 4, curr_osp_cost,
128 5, curr_ovhd_cost) ENI_MEASURE1,
129 decode(cost_element_id, 1,
130 100*(curr_mtl_cost-prev_mtl_cost)/abs(prev_mtl_cost),
131 2, 100*(curr_mtl_ovhd_cost-prev_mtl_ovhd_cost)/abs(prev_mtl_ovhd_cost),
132 3, 100*(curr_res_cost-prev_res_cost)/abs(prev_res_cost),
133 4, 100*(curr_osp_cost-prev_osp_cost)/abs(prev_osp_cost),
134 5, 100*(curr_ovhd_cost-prev_ovhd_cost)/abs(prev_ovhd_cost)) ENI_MEASURE2,
135 decode(cost_element_id, 1, 100*(curr_mtl_cost/curr_item_cost_for_div),
136 2, 100*(curr_mtl_ovhd_cost/curr_item_cost_for_div),
137 3, 100*(curr_res_cost/curr_item_cost_for_div),
138 4, 100*(curr_osp_cost/curr_item_cost_for_div),
139 5, 100*(curr_ovhd_cost/curr_item_cost_for_div)) ENI_MEASURE3,
140 curr_item_cost ENI_MEASURE4,
141 Round(100*(curr_item_cost-prev_item_cost_for_div)/abs(prev_item_cost_for_div),2) ENI_MEASURE5,
142 decode(curr_item_cost,null,null,0,null,100) ENI_MEASURE6
143 from (select report_period_start_date,
144 sum(case when curr_or_prior_period = ''C''
145 then material_cost * ' || l_currency_rate || '
146 else null
147 end) curr_mtl_cost,
148 sum(case when curr_or_prior_period = ''P''
149 then decode(material_cost,0,null,
150 material_cost * ' || l_currency_rate || ')
151 else null
152 end) prev_mtl_cost,
153 sum(case when curr_or_prior_period = ''C''
154 then material_overhead_cost * ' || l_currency_rate || '
155 else null
156 end) curr_mtl_ovhd_cost,
157 sum(case when curr_or_prior_period = ''P''
158 then decode(material_overhead_cost,0,null,
159 material_overhead_cost * ' || l_currency_rate || ')
160 else null
161 end) prev_mtl_ovhd_cost,
162 sum(case when curr_or_prior_period = ''C''
163 then resource_cost * ' || l_currency_rate || '
164 else null
165 end) curr_res_cost,
166 sum(case when curr_or_prior_period = ''P''
167 then decode(resource_cost,0,null,
168 resource_cost * ' || l_currency_rate || ')
169 else null
170 end) prev_res_cost,
171 sum(case when curr_or_prior_period = ''C''
172 then outside_processing_cost * ' || l_currency_rate || '
173 else null
174 end) curr_osp_cost,
175 sum(case when curr_or_prior_period = ''P''
176 then decode(outside_processing_cost,0,null,
177 outside_processing_cost * ' || l_currency_rate || ')
178 else null
179 end) prev_osp_cost,
180 sum(case when curr_or_prior_period = ''C''
181 then overhead_cost * ' || l_currency_rate || '
182 else null
183 end) curr_ovhd_cost,
184 sum(case when curr_or_prior_period = ''P''
185 then decode(overhead_cost,0,null,
186 overhead_cost * ' || l_currency_rate || ')
187 else null
188 end) prev_ovhd_cost,
189 sum(case when curr_or_prior_period = ''C''
190 then decode(item_cost,0,null,
191 item_cost * ' || l_currency_rate || ')
192 else null
193 end) curr_item_cost_for_div,
194 sum(case when curr_or_prior_period = ''C''
195 then item_cost * ' || l_currency_rate || '
196 else null
197 end) curr_item_cost,
198 sum(case when curr_or_prior_period = ''P''
199 then decode(item_cost,0,null,
200 item_cost * ' || l_currency_rate || ')
201 else null
202 end) prev_item_cost_for_div,
203 sum(case when curr_or_prior_period = ''P''
204 then item_cost * ' || l_currency_rate || '
205 else null
206 end) prev_item_cost
207 from
208 (select t.*, cost.*,
209 rank() over
210 (partition by t.curr_or_prior_period, t.report_period_start_date
211 order by effective_date DESC) r
212 from
213 (select ''C'' AS curr_or_prior_period,
214 &' || 'BIS_CURRENT_ASOF_DATE + offset + start_date_offset
215 AS report_period_start_date,
216 &' || 'BIS_CURRENT_ASOF_DATE + offset + start_date_offset
217 AS period_start_date,
218 &' || 'BIS_CURRENT_ASOF_DATE + offset AS period_end_date
219 from fii_time_rolling_offsets
220 where period_type = :l_period_type
221 AND comparison_type = :l_comp_type
222 and offset = 0
223 union all
224 select ''P'' AS curr_or_prior_period,
225 &' || 'BIS_CURRENT_ASOF_DATE + offset + start_date_offset
226 AS report_period_start_date,
227 &' || 'BIS_PREVIOUS_ASOF_DATE + offset + start_date_offset
228 AS period_start_date,
229 &' || 'BIS_PREVIOUS_ASOF_DATE + offset AS period_end_date
230 from fii_time_rolling_offsets
231 where period_type = :l_period_type
232 AND comparison_type = :l_comp_type
233 and offset = 0) t,
234 eni_dbi_item_cost_f cost
235 where cost.inventory_item_id = :l_item
236 and cost.organization_id = :l_org
237 and cost.effective_date <= period_end_date) cost
238 where r=1
239 group by report_period_start_date),
240 cst_cost_elements cost_elements '
241 || '&' || 'ORDER_BY_CLAUSE';
242 end if;
243
244 x_custom_sql := l_sql_stmt;
245
246 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
247 x_custom_output := bis_query_attributes_tbl();
248
249 x_custom_output.extend;
250 l_custom_rec.attribute_name := ':l_item';
251 l_custom_rec.attribute_value := replace(l_item,'''');
252 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
253 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
254 x_custom_output(1) := l_custom_rec;
255
256 x_custom_output.extend;
257 l_custom_rec.attribute_name := ':l_org';
258 l_custom_rec.attribute_value := replace(l_org,'''');
259 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
260 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
261 x_custom_output(2) := l_custom_rec;
262
263 x_custom_output.extend;
264 l_custom_rec.attribute_name := ':l_period_type';
265 l_custom_rec.attribute_value := replace(l_period_type,'''');
266 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
267 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
268 x_custom_output(3) := l_custom_rec;
269
270 x_custom_output.extend;
271 l_custom_rec.attribute_name := ':l_comp_type';
272 l_custom_rec.attribute_value := replace(l_comp_type,'''');
273 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
274 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
275 x_custom_output(4) := l_custom_rec;
276
277
278 END get_sql;
279
280
281
282 END ENI_DBI_UCC_PKG;