[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_UCO_PKG
Source
1 PACKAGE BODY ENI_DBI_UCO_PKG AS
2 /* $Header: ENIUCOPB.pls 120.0 2005/05/26 19:36:32 appldev noship $ */
3
4
5
6 -- Returns query for Unit Cost report/portlet
7 PROCEDURE get_sql( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
8 , x_custom_sql OUT NOCOPY VARCHAR2
9 , x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
10
11 -- SQL statement for the report
12 l_sql_stmt VARCHAR2(4000);
13
14 l_period_type VARCHAR2(40);
15 l_period_bitand NUMBER;
16 l_view_by VARCHAR2(200);
17 l_as_of_date DATE;
18 l_prev_as_of_date DATE;
19 l_report_start DATE;
20 l_cur_period NUMBER;
21 l_days_into_period NUMBER;
22 l_comp_type VARCHAR2(100);
23 l_category VARCHAR2(100);
24 l_item VARCHAR2(100);
25 l_org VARCHAR2(30);
26 l_id_column VARCHAR2(30);
27 l_order_by VARCHAR2(100);
28 l_drill VARCHAR2(30);
29 l_custom_rec BIS_QUERY_ATTRIBUTES;
30 l_err_msg VARCHAR2(100);
31
32 l_comp_where VARCHAR2(100);
33 l_view_by_column VARCHAR2(30);
34 l_item_where VARCHAR2(1000);
35 l_org_where VARCHAR2(1000);
36 l_cat_where VARCHAR2(1000);
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_bom_type varchar2(40);
44 l_type varchar2(40);
45 l_manager varchar2(40);
46 l_lob varchar2(100);
47 l_from_clause varchar2(1000);
48 l_where_clause varchar2(1000);
49 l_group_by_clause varchar2(1000);
50 l_currency_rate VARCHAR2(30);
51 BEGIN
52
53 ENI_DBI_UTIL_PKG.get_parameters( p_page_parameter_tbl
54 , l_period_type
55 , l_period_bitand
56 , l_view_by
57 , l_as_of_date
58 , l_prev_as_of_date
59 , l_report_start
60 , l_cur_period
61 , l_days_into_period
62 , l_comp_type
63 , l_category
64 , l_item
65 , l_org
66 , l_id_column
67 , l_order_by
68 , l_drill
69 , l_status
70 , l_priority
71 , l_reason
72 , l_lifecycle_phase
73 , l_currency
74 , l_bom_type
75 , l_type
76 , l_manager
77 , l_lob
78 );
79
80 IF(l_order_by like '%DESC%')
81 THEN
82 l_order_by:=' report_period_start_date desc ';
83 ELSE
84 l_order_by:=' report_period_start_date asc ';
85 END IF;
86
87 l_period_type := '''' || l_period_type || '''';
88 l_comp_type := '''' || l_comp_type || '''';
89
90 -- Set currency rate based on currency chosen by user
91 l_currency_rate :=
92 CASE l_currency
93 WHEN ENI_DBI_UTIL_PKG.get_curr_sec
94 THEN 'secondary_currency_rate' -- secondary global currency
95 ELSE 'primary_currency_rate' -- primary global currency (default)
96 END;
97
98 -- Eventually get this string from the util package
99
100 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
101 x_custom_output := bis_query_attributes_tbl();
102
103 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
104 l_sql_stmt := 'select null VIEWBY,
105 NULL VIEWBYID,
106 null ENI_MEASURE1, -- curr mtl_cost for table
107 null ENI_MEASURE3, -- curr mtl_ovhd_cost for table
108 null ENI_MEASURE4, -- curr res_cost for table
109 null ENI_MEASURE5, -- curr osp_cost for table
110 null ENI_MEASURE6, -- curr ovhd_cost for table
111 NULL ENI_MEASURE10, -- curr unit_cost for table
112 null ENI_MEASURE9 -- pct_change for table
113 from sys.dual' ;
114 else
115 l_sql_stmt :=
116 'select name VIEWBY,
117 1 VIEWBYID,
118 curr_mtl_cost ENI_MEASURE1,
119 curr_mtl_ovhd_cost ENI_MEASURE3,
120 curr_res_cost ENI_MEASURE4,
121 curr_osp_cost ENI_MEASURE5,
122 curr_ovhd_cost ENI_MEASURE6,
123 curr_item_cost ENI_MEASURE10,
124 prev_item_cost ENI_MEASURE9
125 -- removed the calculation of change % for Bug# 3933564
126 from ( select name, report_period_start_date start_date,
127 sum(case when curr_or_prior_period = ''C''
128 then material_cost * ' || l_currency_rate || '
129 else null
130 end) curr_mtl_cost,
131 sum(case when curr_or_prior_period = ''C''
132 then material_overhead_cost * ' || l_currency_rate || '
133 else null
134 end) curr_mtl_ovhd_cost,
135 sum(case when curr_or_prior_period = ''C''
136 then resource_cost * ' || l_currency_rate || '
137 else null
138 end) curr_res_cost,
139 sum(case when curr_or_prior_period = ''C''
140 then outside_processing_cost * ' || l_currency_rate || '
141 else null
142 end) curr_osp_cost,
143 sum(case when curr_or_prior_period = ''C''
144 then overhead_cost * ' || l_currency_rate || '
145 else null
146 end) curr_ovhd_cost,
147 sum(case when curr_or_prior_period = ''C''
148 then item_cost * ' || l_currency_rate || '
149 else null
150 end) curr_item_cost,
151 sum(case when curr_or_prior_period = ''P''
152 then item_cost * ' || l_currency_rate || '
153 else null
154 end) prev_item_cost
155 from
156 (select t.*, cost.*,
157 rank() over
158 (partition by t.curr_or_prior_period, t.report_period_start_date
159 order by effective_date DESC) r
160 from
161 (select TO_CHAR(&' || 'BIS_CURRENT_ASOF_DATE + offset , ''dd-Mon-yyyy'') AS name,
162 ''C'' AS curr_or_prior_period,
163 &' || 'BIS_CURRENT_ASOF_DATE + offset + start_date_offset
164 AS report_period_start_date,
165 &' || 'BIS_CURRENT_ASOF_DATE + offset + start_date_offset
166 AS period_start_date,
167 &' || 'BIS_CURRENT_ASOF_DATE + offset AS period_end_date
168 from fii_time_rolling_offsets
169 where period_type = :l_period_type
170 AND comparison_type = :l_comp_type
171 union all
172 select TO_CHAR(&' || 'BIS_CURRENT_ASOF_DATE + offset , ''dd-Mon-yyyy'') AS name,
173 ''P'' AS curr_or_prior_period,
174 &' || 'BIS_CURRENT_ASOF_DATE + offset + start_date_offset
175 AS report_period_start_date,
176 &' || 'BIS_PREVIOUS_ASOF_DATE + offset + start_date_offset
177 AS period_start_date,
178 &' || 'BIS_PREVIOUS_ASOF_DATE + offset AS period_end_date
179 from fii_time_rolling_offsets
180 where period_type = :l_period_type
181 AND comparison_type = :l_comp_type)t,
182 eni_dbi_item_cost_f cost
183 where cost.inventory_item_id (+) = :l_item
184 and cost.organization_id (+) = :l_org
185 and cost.effective_date (+) <= period_end_date) t
186 WHERE r=1
187 GROUP BY name, report_period_start_date
188 ORDER BY ' || l_order_by || ' )';
189 --removed the binding for Bug # 3930862
190
191 end if;
192
193 x_custom_sql := l_sql_stmt;
194 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
195 x_custom_output := bis_query_attributes_tbl();
196
197 x_custom_output.extend;
198 l_custom_rec.attribute_name := ':l_item';
199 l_custom_rec.attribute_value := replace(l_item,'''');
200 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
201 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
202 x_custom_output(1) := l_custom_rec;
203
204 x_custom_output.extend;
205 l_custom_rec.attribute_name := ':l_org';
206 l_custom_rec.attribute_value := replace(l_org,'''');
207 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
208 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
209 x_custom_output(2) := l_custom_rec;
210
211 x_custom_output.extend;
212 l_custom_rec.attribute_name := ':l_period_type';
213 l_custom_rec.attribute_value := replace(l_period_type,'''');
214 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
215 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
216 x_custom_output(3) := l_custom_rec;
217
218 x_custom_output.extend;
219 l_custom_rec.attribute_name := ':l_comp_type';
220 l_custom_rec.attribute_value := replace(l_comp_type,'''');
221 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
222 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
223 x_custom_output(4) := l_custom_rec;
224
225 END get_sql;
226
227 END ENI_DBI_UCO_PKG;