[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_PCM_PKG
Source
1 PACKAGE BODY ENI_DBI_PCM_PKG AS
2 /*$Header: ENIPCMPB.pls 120.3 2006/03/23 04:40:19 pgopalar noship $*/
3 PROCEDURE get_sql
4 (
5 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
6 , x_custom_sql OUT NOCOPY VARCHAR2
7 , x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
8 ) IS
9 l_err varchar2(3000);
10 l_custom_rec BIS_QUERY_ATTRIBUTES;
11 l_err_msg VARCHAR2(500);
12 l_period_type VARCHAR2(40);
13 l_period_bitand NUMBER;
14 l_view_by VARCHAR2(200);
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_item_temp VARCHAR2(100);
25 l_org_temp VARCHAR2(100);
26 l_id_column VARCHAR2(30);
27 l_order_by VARCHAR2(100);
28 l_drill VARCHAR2(30);
29 l_status VARCHAR2(30);
30 l_priority VARCHAR2(30);
31 l_reason VARCHAR2(30);
32 l_lifecycle_phase VARCHAR2(30);
33 l_currency VARCHAR2(30);
34 l_bom_type VARCHAR2(30);
35 l_type VARCHAR2(30);
36 l_manager VARCHAR2(30);
37 l_comp_where VARCHAR2(1000);
38 l_org_where VARCHAR2(1000);
39 l_cat_where VARCHAR2(1000);
40 l_item_where VARCHAR2(1000);
41 l_temp VARCHAR2(1000);
42 l_lob VARCHAR2(1000);
43 l_for_cat VARCHAR2(1000);
44 l_from_clause VARCHAR2(1000);
45 l_where_clause VARCHAR2(1000);
46 l_group_by_clause VARCHAR2(500);
47 l_concat_var varchar2(1000);
48 l_org_exists NUMBER;
49
50 BEGIN
51 ENI_DBI_UTIL_PKG.get_parameters( p_page_parameter_tbl
52 , l_period_type
53 , l_period_bitand
54 , l_view_by
55 , l_as_of_date
56 , l_prev_as_of_date
57 , l_report_start
58 , l_cur_period
59 , l_days_into_period
60 , l_comp_type
61 , l_category
62 , l_item_temp
63 , l_org_temp
64 , l_id_column
65 , l_order_by
66 , l_drill
67 , l_status
68 , l_priority
69 , l_reason
70 , l_lifecycle_phase
71 , l_currency
72 , l_bom_type
73 , l_type
74 , l_manager
75 , l_lob
76 );
77
78 eni_dbi_util_pkg.get_time_clauses
79 (
80 'I',
81 'edms',
82 l_period_type,
83 l_period_bitand,
84 l_as_of_date,
85 l_prev_as_of_date,
86 l_report_start,
87 l_cur_period,
88 l_days_into_period,
89 l_comp_type,
90 l_id_column,
91 l_from_clause,
92 l_where_clause,
93 l_group_by_clause,
94 'ROLLING'
95 );
96 Begin
97 select
98 NVL(common_assembly_item_id,assembly_item_id),
99 NVL(common_organization_id ,organization_id)
100 INTO
101 l_item,l_org
102 from
103 bom_bill_of_materials
104 where
105 assembly_item_id = l_item_temp and
106 organization_id = l_org_temp and
107 alternate_bom_designator IS NULL;
108 EXCEPTION
109 WHEN NO_DATA_FOUND THEN
110 l_item := null; l_org := null;
111 End;
112
113 IF (l_item IS NULL) THEN
114 l_item := -9999;
115 l_org := -9999;
116 ELSE -- Display the data only when the organization of the item exists
117 -- in the org_temp table. Added for bug # 3669751
118 SELECT count(*)
119 INTO l_org_exists
120 FROM eni_dbi_part_count_org_temp
121 WHERE organization_id = l_org_temp;
122 IF (l_org_exists = 0) THEN
123 l_item := -9999;
124 l_org := -9999;
125 END IF;
126 END IF;
127
128 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
129 x_custom_output := bis_query_attributes_tbl();
130
131 IF (l_item_temp IS NULL)
132 THEN
133 x_custom_sql :=' SELECT NULL AS VIEWBY,
134 NULL AS ENI_MEASURE1,
135 NULL AS ENI_MEASURE2,
136 NULL AS ENI_MEASURE4,
137 NULL AS ENI_MEASURE5 FROM DUAL ';
138 ELSE
139 -- Setting the Order By Clause.
140 IF (UPPER(l_order_by) LIKE '%START_DATE%ASC%') THEN
141 l_order_by := 'START_DATE ASC';
142 ELSIF (UPPER(l_order_by) LIKE '%START_DATE%DESC%') THEN
143 l_order_by := 'START_DATE DESC';
144 ELSIF (UPPER(l_order_by) LIKE '%START_DATE%') THEN
145 l_order_by := 'START_DATE';
146 END IF;
147 x_custom_sql :=
148 ' SELECT
149 name as VIEWBY,
150 SUM(ENI_MEASURE1) AS ENI_MEASURE1,
151 SUM(ENI_MEASURE2) AS ENI_MEASURE2,
152 SUM(ENI_MEASURE4) AS ENI_MEASURE4,
153 SUM(ENI_MEASURE5) AS ENI_MEASURE5
154 FROM
155 (
156 SELECT
157 t.start_date,
158 t.name,
159 SUM
160 (
161 CASE WHEN t.c_end_date BETWEEN pco.effectivity_date
162 AND pco.disable_date
163 THEN
164 part_count
165 ELSE
166 NULL
167 END
168 ) AS ENI_MEASURE1, -- part count current
169 SUM
170 (
171 CASE WHEN t.p_end_date BETWEEN pco.effectivity_date
172 AND pco.disable_date
173 THEN
174 part_count
175 ELSE
176 NULL
177 END
178 ) AS ENI_MEASURE2, -- part count prior
179 SUM(NULL) AS ENI_MEASURE4, -- mfg steps current
180 SUM(NULL) AS ENI_MEASURE5 -- mfg steps prior
181 FROM
182 eni_dbi_part_count_mv pco , '||l_from_clause||'
183 WHERE
184 pco.assembly_item_id = :ITEM --|| l_item || : Bug 5083568
185 AND pco.organization_id = :ORG --|| l_org || : Bug 5083568
186 AND
187 (
188 t.c_end_date BETWEEN pco.effectivity_date AND pco.disable_date
189 OR
190 t.p_end_date BETWEEN pco.effectivity_date AND pco.disable_date
191 )
192 group by t.start_date,t.name
193 UNION ALL
194 SELECT -- mfg steps
195 t.start_date,
196 t.name,
197 SUM(NULL) AS ENI_MEASURE1, -- part count current
198 SUM(NULL) AS ENI_MEASURE2, -- part count prior
199 SUM
200 (
201 CASE WHEN t.c_end_date
202 BETWEEN trunc(effectivity_date) AND
203 nvl(trunc(disable_date),t.c_end_date+1)
204 THEN
205 mfgsteps_count
206 ELSE
207 NULL
208 END
209 ) AS ENI_MEASURE4, -- mfg steps current
210 SUM
211 (
212 CASE WHEN t.p_end_date
213 BETWEEN trunc(effectivity_date) AND
214 nvl(trunc(disable_date),t.p_end_date+1)
215 THEN
216 mfgsteps_count
217 ELSE
218 NULL
219 END
220 ) AS ENI_MEASURE5 -- mfg steps prior
221 FROM
222 eni_dbi_mfg_steps_join_mv mfg , '||l_from_clause||'
223 WHERE
224 mfg.item_id = :ITEM_TEMP --|| l_item_temp || : Bug 5083568
225 AND mfg.organization_id = :ORG_TEMP --|| l_org_temp || : Bug 5083568
226 AND
227 (
228 t.c_end_date BETWEEN trunc(effectivity_date) AND
229 nvl(trunc(disable_date),t.c_end_date + 1)
230 OR
231 t.p_end_date BETWEEN trunc(effectivity_date) AND
232 nvl(trunc(disable_date),t.p_end_date + 1)
233 )
234 group by t.start_date,t.name
235 UNION ALL
236 SELECT
237 t.start_date,
238 t.name,
239 NULL AS ENI_MEASURE1, -- part count current
240 NULL AS ENI_MEASURE2, -- part count prior
241 NULL AS ENI_MEASURE4, -- mfg steps current
242 NULL AS ENI_MEASURE5 -- mfg steps prior
243 FROM
244 '||l_from_clause||'
245 WHERE
246 NOT(
247 (EXISTS(select * from eni_dbi_part_count_mv
248 where assembly_item_id = :ITEM AND --||l_item|| AND : Bug 5083568
249 organization_id = :ORG AND --||l_org|| AND : Bug 5083568
250 (t.c_end_date BETWEEN effectivity_date AND disable_date
251 OR t.p_end_date BETWEEN effectivity_date AND disable_date)))
252 OR
253 (EXISTS(select * from eni_dbi_mfg_steps_join_mv
254 where item_id = :ITEM_TEMP AND --||l_item_temp|| AND : Bug 5083568
255 organization_id = :ORG_TEMP AND --||l_org_temp|| AND : Bug 5083568
256 (t.c_end_date BETWEEN trunc(effectivity_date) AND
257 nvl(trunc(disable_date),t.c_end_date + 1)
258 OR t.p_end_date BETWEEN trunc(effectivity_date) AND
259 nvl(trunc(disable_date),t.p_end_date + 1)) ))
260 ))
261 group by start_date,name
262 order by '||l_order_by ;
263
264 -- Bug 5083568: Added the following Bind Values
265 x_custom_output.extend;
266 l_custom_rec.attribute_name := ':ITEM';
267 l_custom_rec.attribute_value := replace(l_item,'''');
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(1) := l_custom_rec;
271
272 x_custom_output.extend;
273 l_custom_rec.attribute_name := ':ORG';
274 l_custom_rec.attribute_value := replace(l_org,'''');
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(2) := l_custom_rec;
278
279 x_custom_output.extend;
280 l_custom_rec.attribute_name := ':ITEM_TEMP';
281 l_custom_rec.attribute_value := replace(l_item_temp,'''');
282 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
283 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
284 x_custom_output(3) := l_custom_rec;
285
286 x_custom_output.extend;
287 l_custom_rec.attribute_name := ':ORG_TEMP';
288 l_custom_rec.attribute_value := replace(l_org_temp,'''');
289 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
290 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
291 x_custom_output(4) := l_custom_rec;
292
293 --Bug 5083652 -- Start Code
294
295 x_custom_output.extend;
296 l_custom_rec.attribute_name := ':PERIODTYPE';
297 l_custom_rec.attribute_value := REPLACE(l_period_type,'''');
298 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
299 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
300 x_custom_output(5) := l_custom_rec;
301
302 x_custom_output.extend;
303 l_custom_rec.attribute_name := ':COMPARETYPE';
304 l_custom_rec.attribute_value := REPLACE(l_comp_type,'''');
305 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
306 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
307 x_custom_output(6) := l_custom_rec;
308
309
310 x_custom_output.extend;
311 l_custom_rec.attribute_name := ':PERIODAND';
312 l_custom_rec.attribute_value := REPLACE(l_period_bitand,'''');
313 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
314 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
315 x_custom_output(7) := l_custom_rec;
316
317 x_custom_output.extend;
318 l_custom_rec.attribute_name := ':CUR_PERIOD_ID';
319 l_custom_rec.attribute_value := REPLACE(l_cur_period,'''');
320 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
321 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
322 x_custom_output(8) := l_custom_rec;
323
324 --Bug 5083652 -- End Code
325
326 END IF;
327
328 END GET_SQL;
329 END ENI_DBI_PCM_PKG;