[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_PCB_PKG
Source
1 PACKAGE BODY ENI_DBI_PCB_PKG AS
2 /*$Header: ENIPCBPB.pls 120.5 2006/03/23 22:33:39 pfarkade 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 ) IS
10 l_err varchar2(3000);
11 l_custom_rec BIS_QUERY_ATTRIBUTES;
12 l_err_msg VARCHAR2(500);
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(100);
25 l_item_temp VARCHAR2(100);
26 l_org_temp VARCHAR2(100);
27 l_id_column VARCHAR2(30);
28 l_order_by VARCHAR2(100);
29 l_drill VARCHAR2(30);
30 l_status VARCHAR2(30);
31 l_priority VARCHAR2(30);
32 l_reason VARCHAR2(30);
33 l_lifecycle_phase VARCHAR2(30);
34 l_currency VARCHAR2(30);
35 l_bom_type VARCHAR2(30);
36 l_type VARCHAR2(30);
37 l_manager VARCHAR2(30);
38 l_temp VARCHAR2(1000);
39 l_lob VARCHAR2(1000);
40 l_for_cat VARCHAR2(1000);
41 l_from_clause VARCHAR2(1000);
42 l_where_clause VARCHAR2(1000);
43 l_group_by_clause VARCHAR2(500);
44 l_concat_var varchar2(1000);
45 l_item_1 NUMBER;
46 l_org_1 NUMBER;
47 l_item_org_clause VARCHAR2(1000);
48 l_org_exists NUMBER;
49 BEGIN
50 ENI_DBI_UTIL_PKG.get_parameters( p_page_parameter_tbl
51 , l_period_type
52 , l_period_bitand
53 , l_view_by
54 , l_as_of_date
55 , l_prev_as_of_date
56 , l_report_start
57 , l_cur_period
58 , l_days_into_period
59 , l_comp_type
60 , l_category
61 , l_item_temp
62 , l_org_temp
63 , l_id_column
64 , l_order_by
65 , l_drill
66 , l_status
67 , l_priority
68 , l_reason
69 , l_lifecycle_phase
70 , l_currency
71 , l_bom_type
72 , l_type
73 , l_manager
74 , l_lob
75 );
76 eni_dbi_util_pkg.get_time_clauses
77 (
78 'I',
79 'edms',
80 l_period_type,
81 l_period_bitand,
82 l_as_of_date,
83 l_prev_as_of_date,
84 l_report_start,
85 l_cur_period,
86 l_days_into_period,
87 l_comp_type,
88 l_id_column,
89 l_from_clause,
90 l_where_clause,
91 l_group_by_clause,
92 'ROLLING'
93 );
94 -- set where clauses
95 Begin
96 select
97 NVL(common_assembly_item_id,assembly_item_id),
98 NVL(common_organization_id ,organization_id)
99 INTO
100 l_item,l_org
101 from
102 bom_bill_of_materials
103 where
104 assembly_item_id = l_item_temp and
105 organization_id = l_org_temp and
106 alternate_bom_designator IS NULL;
107 EXCEPTION
108 WHEN NO_DATA_FOUND THEN
109 l_item := NULL; l_org := NULL;
110 End;
111
112 IF (l_item IS NULL) THEN
113 l_item := -9999;
114 l_org := -9999;
115 ELSE -- Display the data only when the organization of the item exists
116 -- in the org_temp table. Added for bug # 3669751
117 SELECT count(*)
118 INTO l_org_exists
119 FROM eni_dbi_part_count_org_temp
120 WHERE organization_id = l_org_temp;
121 IF (l_org_exists = 0) THEN
122 l_item := -9999;
123 l_org := -9999;
124 END IF;
125 END IF;
126
127 IF (l_item_temp IS NULL OR l_item_temp = '' OR l_item_temp = 'All')
128 THEN
129 IF (l_org_temp IS NOT NULL) THEN
130 --l_item_org_clause := ' AND eiv.organization_id = '||l_org;
131 l_item_org_clause := ' AND eiv.organization_id = :ORG'; --Bug 5083920
132 -- l_item_org_clause := l_item_org_clause || ' AND pco.organization_id = '||l_org';
133 l_item_org_clause := l_item_org_clause || ' AND pco.organization_id = :ORG'; --Bug 5083920
134 ELSE
135 l_item_org_clause:= ' AND eiv.id = pco.item_org_id';
136 END IF;
137 l_item_1 := -1;
138 ELSE
139 --l_item_org_clause := ' AND pco.organization_id = '||l_org;
140 l_item_org_clause := ' AND pco.organization_id = :ORG'; --Bug 5083920
141 --l_item_org_clause := l_item_org_clause || ' AND pco.assembly_item_id = '||l_item;
142 l_item_org_clause := l_item_org_clause || ' AND pco.assembly_item_id = :ITEM'; --Bug 5083920
143 l_item_1 := l_item;
144 l_org_1 := l_org;
145 END IF;
146
147 -- return sql based on the viewby
148
149 IF substr(l_view_by, 1, 5) = 'TIME+'
150 THEN
151 IF (l_item_temp IS NULL OR l_item_temp = '' OR l_item_temp = 'All')
152 THEN
153 x_custom_sql :=' SELECT NULL AS VIEWBY,
154 NULL AS ENI_MEASURE1,
155 NULL AS ENI_MEASURE2,
156 NULL AS ENI_MEASURE4,
157 NULL AS ENI_MEASURE5,
158 NULL AS ENI_ATTRIBUTE11,
159 NULL AS ENI_ATTRIBUTE2 FROM DUAL';
160 ELSE
161 -- Setting the Order By Clause.
162 IF (UPPER(l_order_by) LIKE '%START_DATE%ASC%') THEN
163 l_order_by := 'START_DATE ASC';
164 ELSIF (UPPER(l_order_by) LIKE '%START_DATE%DESC%') THEN
165 l_order_by := 'START_DATE DESC';
166 ELSIF (UPPER(l_order_by) LIKE '%START_DATE%') THEN
167 l_order_by := 'START_DATE';
168 END IF;
169 x_custom_sql :=
170 'SELECT
171 name AS VIEWBY,
172 current_pco AS ENI_MEASURE1,
173 prior_pco AS ENI_MEASURE2,
174 current_bom_levels AS ENI_MEASURE4,
175 prior_bom_levels AS ENI_MEASURE5,
176 current_pco AS ENI_ATTRIBUTE11,
177 current_bom_levels AS ENI_ATTRIBUTE2
178 FROM ((select
179 sum(case when (t.c_end_date BETWEEN
180 pco.effectivity_date AND pco.disable_date)
181 then part_count
182 else NULL end) as current_pco,
183 sum(case when (t.p_end_date BETWEEN
184 pco.effectivity_date AND pco.disable_date)
185 then part_count
186 else NULL end) as prior_pco,
187 max(case when (t.c_end_date BETWEEN
188 pco.effectivity_date AND pco.disable_date)
189 then max_bom_level
190 else NULL end) as current_bom_levels,
191 max(case when (t.p_end_date BETWEEN
192 pco.effectivity_date AND pco.disable_date)
193 then max_bom_level
194 else NULL end) as prior_bom_levels,
195 t.name,
196 t.start_date
197 from
198 eni_dbi_part_count_mv pco,
199 ' || l_from_clause || '
200 where
201 '||l_where_clause|| '
202 '||l_item_org_clause||'
203 group by
204 t.name,t.start_date)
205 UNION ALL
206 (select NULL AS current_pco,
207 NULL AS prior_pco,
208 NULL AS current_bom_levels,
209 NULL AS prior_bom_levels,
210 t.name,
211 t.start_date
212 from
213 ' || l_from_clause || '
214 where
215 (NOT EXISTS(select * from eni_dbi_part_count_mv where
216 assembly_item_id = :LITEM
217 and organization_id = :LORG)) and ' --Bug 5083920
218 || l_where_clause ||'))
219 order by
220 '||l_order_by;
221 END IF;
222 END IF;
223 /* -- For ITEM VIEWBY
224 IF (l_view_by = 'ITEM+ENI_ITEM_ORG') THEN
225 IF (l_order_by like '%VIEW%ASC') THEN
226 l_order_by := 'VIEWBY ASC';
227 ELSIF (l_order_by like '%VIEW%DESC') THEN
228 l_order_by := 'VIEWBY DESC';
229 ELSIF (l_order_by like '%VIEW%') THEN
230 l_order_by := 'VIEWBY ASC';
231 END IF;
232 x_custom_sql :=
233 '(select
234 eiv.value as VIEWBY,
235 sum(case when ('||'&'||'BIS_CURRENT_ASOF_DATE BETWEEN
236 trunc(pco.effectivity_date) AND
237 NVL(trunc(pco.disable_date),to_date(''01-01-3000'',''DD-MM-YYYY'')))
238 then part_count
239 else NULL end) as ENI_MEASURE1,
240 sum(case when ('||'&'||'BIS_PREVIOUS_ASOF_DATE BETWEEN
241 trunc(pco.effectivity_date) AND
242 NVL(trunc(pco.disable_date),to_date(''01-01-3000'',''DD-MM-YYYY'')))
243 then part_count
244 else NULL end) as ENI_MEASURE2,
245 max(case when ('||'&'||'BIS_CURRENT_ASOF_DATE BETWEEN
246 trunc(pco.effectivity_date) AND
247 NVL(trunc(pco.disable_date),to_date(''01-01-3000'',''DD-MM-YYYY'')))
248 then max_bom_level
249 else NULL end) as ENI_MEASURE4,
250 max(case when ('||'&'||'BIS_PREVIOUS_ASOF_DATE BETWEEN
251 trunc(pco.effectivity_date) AND
252 NVL(trunc(pco.disable_date),to_date(''01-01-3000'',''DD-MM-YYYY'')))
253 then max_bom_level
254 else NULL end) as ENI_MEASURE5,
255 sum(case when ('||'&'||'BIS_CURRENT_ASOF_DATE BETWEEN
256 trunc(pco.effectivity_date) AND
257 NVL(trunc(pco.disable_date),to_date(''01-01-3000'',''DD-MM-YYYY'')))
258 then part_count
259 else NULL end) as ENI_ATTRIBUTE11,
260 max(case when ('||'&'||'BIS_CURRENT_ASOF_DATE BETWEEN
261 trunc(pco.effectivity_date) AND
262 NVL(trunc(pco.disable_date),to_date(''01-01-3000'',''DD-MM-YYYY'')))
263 then max_bom_level
264 else NULL end) as ENI_ATTRIBUTE2
265 from
266 eni_dbi_part_count_mv pco, ENI_ITEM_ORG_V eiv
267 where
268 (' || '&'|| 'BIS_CURRENT_ASOF_DATE BETWEEN
269 trunc(pco.effectivity_date) AND
270 NVL(trunc(pco.disable_date),to_date(''01-01-3000'', ''DD-MM-YYYY''))
271 OR
272 ' || '&'|| 'BIS_PREVIOUS_ASOF_DATE BETWEEN
273 trunc(pco.effectivity_date) AND
274 NVL(trunc(pco.disable_date),to_date(''01-01-3000'', ''DD-MM-YYYY'')))
275 '||l_item_org_clause||'
276 group by
277 eiv.value
278 UNION ALL
279 select eiv.value as VIEWBY,
280 NULL as ENI_MEASURE1,
281 NULL AS ENI_MEASURE2,
282 NULL AS ENI_MEASURE4,
283 NULL AS ENI_MEASURE5,
284 NULL AS ENI_ATTRIBUTE11,
285 NULL AS ENI_ATTRIBUTE2
286 from
287 eni_item_org_v eiv
288 where
289 eiv.inventory_item_id = '||l_item_1||' and
290 eiv.organization_id = '||l_org_1||' and
291 ( NOT EXISTS (select * from eni_dbi_part_count_mv pco
292 where
293 item_id = '|| l_item_1 || ' and
294 organization_id = '|| l_org_1 || ' and
295 (' || '&'|| 'BIS_CURRENT_ASOF_DATE BETWEEN
296 trunc(pco.effectivity_date) AND
297 NVL(trunc(pco.disable_date),to_date(''01-01-3000'', ''DD-MM-YYYY''))
298 OR
299 ' || '&'|| 'BIS_PREVIOUS_ASOF_DATE BETWEEN
300 trunc(pco.effectivity_date) AND
301 NVL(trunc(pco.disable_date),to_date(''01-01-3000'', ''DD-MM-YYYY''))))))
302 order by '
303 ||l_order_by;
304 END IF; */
305
306 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
307 x_custom_output := bis_query_attributes_tbl();
308
309 --Start Bug 5083920
310 x_custom_output.extend;
311 l_custom_rec.attribute_name := ':ITEM';
312 l_custom_rec.attribute_value := replace(l_item,'''');
313 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
314 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
315 x_custom_output(1) := l_custom_rec;
316
317 x_custom_output.extend;
318 l_custom_rec.attribute_name := ':ORG';
319 l_custom_rec.attribute_value := replace(l_org,'''');
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(2) := l_custom_rec;
323
324 x_custom_output.extend;
325 l_custom_rec.attribute_name := ':LITEM';
326 l_custom_rec.attribute_value := replace(l_item_1,'''');
327 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
328 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
329 x_custom_output(3) := l_custom_rec;
330
331 x_custom_output.extend;
332 l_custom_rec.attribute_name := ':LORG';
333 l_custom_rec.attribute_value := replace(l_org_1,'''');
334 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
335 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
336 x_custom_output(4) := l_custom_rec;
337
338 --End Bug 5083920
339 --Bug 5083652 -- Start Code
340
341 x_custom_output.extend;
342 l_custom_rec.attribute_name := ':PERIODTYPE';
343 l_custom_rec.attribute_value := replace(l_period_type,'''');
344 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
345 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
346 x_custom_output(5) := l_custom_rec;
347
348 x_custom_output.extend;
349 l_custom_rec.attribute_name := ':COMPARETYPE';
350 l_custom_rec.attribute_value := replace(l_comp_type,'''');
351 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
352 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
353 x_custom_output(6) := l_custom_rec;
354
355
356 x_custom_output.extend;
357 l_custom_rec.attribute_name := ':PERIODAND';
358 l_custom_rec.attribute_value := replace(l_period_bitand,'''');
359 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
360 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
361 x_custom_output(7) := l_custom_rec;
362
363 x_custom_output.extend;
364 l_custom_rec.attribute_name := ':CUR_PERIOD_ID';
365 l_custom_rec.attribute_value := replace(l_cur_period,'''');
366 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
367 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
368 x_custom_output(8) := l_custom_rec;
369
370 --Bug 5083652 -- End Code
371
372 END GET_SQL;
373
374 /* ------------------------------------------------------
375 Function : GetLabel
376 The function returns YTD/QTD/PTD/WTD/Measure Name. This function is called
377 from the PMV report and relies on cached values of variables
378 called in the package init section.
379 ------------------------------------------------------*/
380
381 FUNCTION GetLabel(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
382 , measure_label IN NUMBER)
383 RETURN VARCHAR2
384 IS
385 l_Time_Level_Value VARCHAR2(80);
386 l_View_By VARCHAR2(30);
387 l_Label VARCHAR2(10);
388 L_YTD_Label VARCHAR2(8):='YTD';
389 L_QTD_Label VARCHAR2(8):='QTD';
390 L_MTD_Label VARCHAR2(8):='MTD';
391 L_WTD_Label VARCHAR2(8):='WTD';
392 BEGIN
393 FOR i IN 1..p_page_parameter_tbl.COUNT LOOP
394 IF p_page_parameter_tbl(i).parameter_name = 'PERIOD_TYPE' THEN
395 l_Time_Level_Value:=p_page_parameter_tbl(i).parameter_value;
396 ELSIF p_page_parameter_tbl(i).parameter_name = 'VIEW_BY' THEN
397 l_View_By:=p_page_parameter_tbl(i).parameter_value;
398 END IF;
399 END LOOP;
400 IF (l_View_By LIKE '%ENI_ITEM_ORG%') THEN
401 IF l_time_level_value IS NOT NULL THEN
402 CASE (l_time_level_value)
403 WHEN 'FII_TIME_ENT_YEAR' THEN
404 l_Label:=L_YTD_Label;
405 WHEN 'FII_TIME_ENT_QTR' THEN
406 l_Label:=L_QTD_Label;
407 WHEN 'FII_TIME_ENT_PERIOD' THEN
408 l_Label:=L_MTD_Label;
409 WHEN 'FII_TIME_WEEK' THEN
410 l_Label:=L_WTD_Label;
411 END CASE;
412 ELSE
413 l_Label:='';
414 END IF;
415 ELSE
416 IF (measure_label = 1) THEN
417 l_Label := 'Part Count';
418 ELSE
419 l_Label := 'BOM Levels';
420 END IF;
421 END IF;
422 RETURN l_Label;
423 EXCEPTION
424 WHEN OTHERS THEN RETURN NULL;
425 END GetLabel;
426
427 END ENI_DBI_PCB_PKG;