[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_PROD_SCRAP_PKG
Source
1 PACKAGE BODY opi_dbi_prod_scrap_pkg AS
2 /*$Header: OPIDRSCRAPB.pls 120.0 2005/05/24 17:21:21 appldev noship $ */
3
4
5 /*++++++++++++++++++++++++++++++++++++++++*/
6 /* Function and procedure declarations in this file but not in spec*/
7 /*++++++++++++++++++++++++++++++++++++++++*/
8
9 FUNCTION get_status_sel_clause (p_view_by_dim IN VARCHAR2, p_period_type in VARCHAR2,p_org in varchar2)
10 RETURN VARCHAR2;
11
12 PROCEDURE get_qty_columns (p_dim_name VARCHAR2,
13 p_description OUT NOCOPY VARCHAR2,
14 p_uom OUT NOCOPY VARCHAR2,
15 p_qty1 OUT NOCOPY VARCHAR2,
16 p_qty2 OUT NOCOPY VARCHAR2,
17 p_qty3 OUT NOCOPY VARCHAR2);
18
19
20 FUNCTION get_trend_sel_clause(p_view_by_dim IN VARCHAR2, p_url IN VARCHAR2)
21 return VARCHAR2;
22
23 /*----------------------------------------*/
24
25 /*
26 Report query Function for viewby = Item, Org, Cat.
27 */
28 PROCEDURE scrap_status_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
29 x_custom_sql OUT NOCOPY VARCHAR2,
30 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
31 IS
32 l_query VARCHAR2(15000);
33 l_view_by VARCHAR2(120);
34 l_view_by_col VARCHAR2 (120);
35 l_xtd VARCHAR2(10);
36 l_comparison_type VARCHAR2(1) := 'Y';
37 l_cur_suffix VARCHAR2(2);
38 l_custom_sql VARCHAR2 (10000);
39 l_period_type VARCHAR2(255) := NULL;
40
41 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
42 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
43
44 l_where_clause VARCHAR2 (2000);
45 l_mv VARCHAR2 (30);
46 l_org varchar(30);
47 l_item_cat_flag varchar2(1) := '0';
48
49 l_custom_rec BIS_QUERY_ATTRIBUTES;
50
51 BEGIN
52
53 -- clear out the tables.
54 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
55 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
56
57 FOR i IN 1..p_param.COUNT
58 LOOP
59 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
60 THEN l_org := p_param(i).parameter_id;
61 END IF;
62 IF(p_param(i).parameter_name = 'PERIOD_TYPE')
63 THEN l_period_type := p_param(i).parameter_value;
64 END IF;
65 END LOOP;
66 -- get all the query parameters
67 opi_dbi_rpt_util_pkg.process_parameters (p_param,
68 l_view_by,
69 l_view_by_col,
70 l_comparison_type,
71 l_xtd,
72 l_cur_suffix,
73 l_where_clause,
74 l_mv,
75 l_join_tbl,
76 l_item_cat_flag,
77 'N',
78 'OPI',
79 '6.0',
80 '',
81 'SCR',
82 'ITEM_CAT');
83
84 -- The measure columns that need to be aggregated are
85 -- production_val_<b/g>, scrap_val_<b/g>
86 -- If viewing by item as, then sum up
87 -- production_qty, scrap_qty
88 poa_dbi_util_pkg.add_column (l_col_tbl,
89 'production_val_' || l_cur_suffix,
90 'production_val');
91
92 poa_dbi_util_pkg.add_column (l_col_tbl,
93 'scrap_val_' || l_cur_suffix,
94 'scrap_val');
95
96 -- Quantity columns are only needed for Item viewby.
97 IF (l_view_by = 'ITEM+ENI_ITEM_ORG') THEN
98
99
100 poa_dbi_util_pkg.add_column (l_col_tbl,
101 'production_qty',
102 'production_qty');
103
104 poa_dbi_util_pkg.add_column (l_col_tbl,
105 'scrap_qty',
106 'scrap_qty');
107 END IF;
108
109 -- construct the query
110 l_query := get_status_sel_clause (l_view_by, l_period_type,l_org)
111 || ' from ((
112 ' || poa_dbi_template_pkg.status_sql (l_mv,
113 l_where_clause,
114 l_join_tbl,
115 'N',
116 l_col_tbl,
117 'N');
118
119 -- prepare output for bind variables
120 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
121 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
122
123 -- set the basic bind variables for the status SQL
124 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
125
126 -- Passing OPI_ITEM_CAT_FLAG to PMV
127 l_custom_rec.attribute_name := ':OPI_ITEM_CAT_FLAG';
128 l_custom_rec.attribute_value := l_item_cat_flag;
129 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
130 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
131 x_custom_output.extend;
132 x_custom_output(x_custom_output.count) := l_custom_rec;
133
134 x_custom_sql := l_query;
135
136 END scrap_status_sql;
137
138
139 /*
140 Outer main query for viewby = item, org, cat
141 */
142
143 FUNCTION get_status_sel_clause(p_view_by_dim IN VARCHAR2, p_period_type in VARCHAR2, p_org in varchar2)
144 return VARCHAR2
145 IS
146
147 l_sel_clause varchar2(4500);
148 l_view_by_col_name varchar2(60);
149 l_description varchar2(30);
150 l_uom varchar2(30);
151 l_qty1 varchar2(35);
152 l_qty2 varchar2(35);
153 l_qty3 varchar2(200);
154
155 BEGIN
156
157 -- Main Outer query
158
159 -- Column to get view by column name
160 l_view_by_col_name := opi_dbi_rpt_util_pkg.get_view_by_col_name
161 (p_view_by_dim);
162
163 -- Quantity columns for
164 get_qty_columns (p_view_by_dim, l_description, l_uom, l_qty1,
165 l_qty2, l_qty3);
166
167 -- Outer select clause
168 l_sel_clause :=
169 'SELECT
170 ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
171 || l_view_by_col_name || ' OPI_ATTRIBUTE1,
172 ' || l_description || ' OPI_ATTRIBUTE2,
173 ' || l_uom || ' OPI_ATTRIBUTE3,';
174
175 IF ((p_view_by_dim = 'ITEM+ENI_ITEM_ORG') AND (p_period_type = 'FII_TIME_WEEK' OR p_period_type = 'FII_TIME_ENT_PERIOD') AND (UPPER(p_org)<>'ALL')) THEN
176 l_sel_clause := l_sel_clause || ' ''pFunctionName=OPI_DBI_PROD_SCRAP_JOB_DTL_REP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_ORG&pParamIds=Y'' OPI_ATTRIBUTE4 ,';
177 ELSE
178 l_sel_clause := l_sel_clause || 'NULL OPI_ATTRIBUTE4 ,';
179 END IF;
180
181 l_sel_clause := l_sel_clause || l_qty1 || ' OPI_MEASURE1,
182 ' || l_qty2 || ' OPI_MEASURE2,
183 ' || l_qty3 || ' OPI_MEASURE3,
184 ' || opi_dbi_rpt_util_pkg.nvl_str ('oset.p_scrap_val')
185 || ' OPI_MEASURE4,
186 ' || opi_dbi_rpt_util_pkg.nvl_str ('oset.c_scrap_val')
187 || ' OPI_MEASURE5,
188 ' || opi_dbi_rpt_util_pkg.change_str ('oset.c_scrap_val',
189 'oset.p_scrap_val',
190 'oset.p_scrap_val',
191 'OPI_MEASURE6') || ',
192 ' || opi_dbi_rpt_util_pkg.nvl_str ('oset.c_production_val')
193 || ' OPI_MEASURE7,
194 ' || opi_dbi_rpt_util_pkg.percent_str ('oset.p_scrap_val',
195 'oset.p_production_val',
196 'OPI_MEASURE8') || ',
197 ' || opi_dbi_rpt_util_pkg.percent_str ('oset.c_scrap_val',
198 'oset.c_production_val',
199 'OPI_MEASURE9') || ',
200 ' || opi_dbi_rpt_util_pkg.change_pct_str ('oset.c_scrap_val',
201 'oset.c_production_val',
202 'oset.p_scrap_val',
203 'oset.p_production_val',
204 'OPI_MEASURE10') || ',
205 ' || opi_dbi_rpt_util_pkg.nvl_str ('oset.c_scrap_val_total')
206 || ' OPI_MEASURE11,
207 ' || opi_dbi_rpt_util_pkg.change_str ('oset.c_scrap_val_total',
208 'oset.p_scrap_val_total',
209 'oset.p_scrap_val_total',
210 'OPI_MEASURE12') || ',
211 ' || opi_dbi_rpt_util_pkg.nvl_str ('oset.c_production_val_total')
212 || ' OPI_MEASURE13,
213 ' || opi_dbi_rpt_util_pkg.percent_str ('oset.c_scrap_val_total',
214 'oset.c_production_val_total',
215 'OPI_MEASURE14') || ',
216 ' || opi_dbi_rpt_util_pkg.change_pct_str ('oset.c_scrap_val_total',
217 'oset.c_production_val_total',
218 'oset.p_scrap_val_total',
219 'oset.p_production_val_total',
220 'OPI_MEASURE15') || ',
221 ' || opi_dbi_rpt_util_pkg.percent_str ('oset.c_scrap_val',
222 'oset.c_production_val',
223 'OPI_MEASURE16') || ',
224 ' || opi_dbi_rpt_util_pkg.percent_str ('oset.p_scrap_val',
225 'oset.p_production_val',
226 'OPI_MEASURE17') || ',
227 ' || opi_dbi_rpt_util_pkg.percent_str ('oset.c_scrap_val_total',
228 'oset.c_production_val_total',
229 'OPI_MEASURE18') || ',
230 ' || opi_dbi_rpt_util_pkg.percent_str ('oset.p_scrap_val_total',
231 'oset.p_production_val_total',
232 'OPI_MEASURE19') ;
233
234 RETURN l_sel_clause;
235
236 END get_status_sel_clause;
237
238
239 /*
240 For viewby = item, get the quantity columns that have to be displayed.
241 For all other viewby values, there is no quantity to display.
242 */
243 PROCEDURE get_qty_columns (p_dim_name VARCHAR2,
244 p_description OUT NOCOPY VARCHAR2,
245 p_uom OUT NOCOPY VARCHAR2,
246 p_qty1 OUT NOCOPY VARCHAR2,
247 p_qty2 OUT NOCOPY VARCHAR2,
248 p_qty3 OUT NOCOPY VARCHAR2)
249 IS
250 l_description varchar2(30);
251 l_uom varchar2(30);
252
253 BEGIN
254 CASE
255 WHEN p_dim_name = 'ITEM+ENI_ITEM_ORG' THEN
256 BEGIN
257 p_description := 'v.description';
258 p_uom := 'v2.unit_of_measure';
259 p_qty1 := opi_dbi_rpt_util_pkg.nvl_str ('oset.c_scrap_qty');
260 p_qty2 := opi_dbi_rpt_util_pkg.nvl_str
261 ('oset.c_production_qty');
262 p_qty3 := opi_dbi_rpt_util_pkg.percent_str
263 ('oset.c_scrap_qty',
264 'oset.c_production_qty',
265 '');
266 END;
267 ELSE
268 BEGIN
269 p_description := 'null';
270 p_uom := 'null';
271 p_qty1 := 'null';
272 p_qty2 := 'null';
273 p_qty3 := 'null';
274 END;
275 END CASE;
276 END get_qty_columns;
277
278
279 /*
280 Report query for viewby = time
281 */
282
283 PROCEDURE scrap_trend_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
284 x_custom_sql OUT NOCOPY VARCHAR2,
285 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
286 IS
287 l_query VARCHAR2(15000);
288 l_view_by VARCHAR2(120);
289 l_view_by_col VARCHAR2 (120);
290 l_xtd varchar2(10);
291 l_comparison_type VARCHAR2(1) := 'Y';
292 l_cur_suffix VARCHAR2(2);
293 l_custom_sql VARCHAR2(4000);
294 l_mv VARCHAR2 (30);
295 l_where_clause VARCHAR2 (4000) := '';
296
297 l_item_cat_flag VARCHAR2(1) := '0';
298
299 l_custom_rec BIS_QUERY_ATTRIBUTES;
300
301 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
302 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
303
304 BEGIN
305
306 -- clear out the tables.
307 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
308 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
309
310 -- get all the query parameters
311 opi_dbi_rpt_util_pkg.process_parameters (p_param,
312 l_view_by,
313 l_view_by_col,
314 l_comparison_type,
315 l_xtd,
316 l_cur_suffix,
317 l_where_clause,
318 l_mv,
319 l_join_tbl,
323 '6.0',
320 l_item_cat_flag,
321 'Y',
322 'OPI',
324 '',
325 'SCR',
326 'ITEM_CAT');
327
328 -- The measure columns that need to be aggregated are
329 -- production_val_<b/g>, scrap_val_<b/g>
330 -- If viewing by item as, then sum up
331 -- production_qty, scrap_qty.
332 -- No Grand totals required.
333 poa_dbi_util_pkg.add_column (l_col_tbl,
334 'production_val_' || l_cur_suffix,
335 'production_val',
336 'N');
337 poa_dbi_util_pkg.add_column (l_col_tbl,
338 'scrap_val_' || l_cur_suffix,
339 'scrap_val',
340 'N');
341
342 -- Joining Outer and Inner Query
343 l_query := get_trend_sel_clause(l_view_by, null) ||
344 ' from ' ||
345 poa_dbi_template_pkg.trend_sql (
346 l_xtd,
347 l_comparison_type,
348 l_mv,
349 l_where_clause,
350 l_col_tbl,
351 'N');
352
353
354
355 -- Prepare PMV bind variables
356 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
357 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
358
359 -- get all the basic binds used by POA queries
360 -- Do this before adding any of our binds, since the procedure
361 -- reinitializes the output table
362 poa_dbi_util_pkg.get_custom_trend_binds (l_xtd, l_comparison_type,
363 x_custom_output);
364
365 -- put the custom OPI binds in
366 l_custom_rec.attribute_name := ':OPI_ITEM_CAT_FLAG';
367 l_custom_rec.attribute_value := l_item_cat_flag;
368 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
369 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
370 x_custom_output.extend;
371 x_custom_output(x_custom_output.count) := l_custom_rec;
372
373 x_custom_sql := l_query;
374
375
376 END scrap_trend_sql;
377
378 /*
379 The outer main query for the trend SQL.
380 */
381 FUNCTION get_trend_sel_clause(p_view_by_dim IN VARCHAR2, p_url IN VARCHAR2)
382 return VARCHAR2
383 IS
384
385 l_sel_clause varchar2(4500);
386
387 BEGIN
388
389 -- Main Outer query
390
391 l_sel_clause :=
392 'SELECT
393 ' || ' cal.name VIEWBY,
394 ' || opi_dbi_rpt_util_pkg.nvl_str ('iset.p_scrap_val')
395 || ' OPI_MEASURE1,
396 ' || opi_dbi_rpt_util_pkg.nvl_str ('iset.c_scrap_val')
397 || ' OPI_MEASURE2,
398 ' || opi_dbi_rpt_util_pkg.change_str ('iset.c_scrap_val',
399 'iset.p_scrap_val',
400 'iset.p_scrap_val',
401 'OPI_MEASURE3') || ',
402 ' || opi_dbi_rpt_util_pkg.nvl_str ('iset.c_production_val')
403 || ' OPI_MEASURE4,
404 ' || opi_dbi_rpt_util_pkg.percent_str ('iset.p_scrap_val',
405 'iset.p_production_val',
406 'OPI_MEASURE5') || ',
407 ' || opi_dbi_rpt_util_pkg.percent_str ('iset.c_scrap_val',
408 'iset.c_production_val',
409 'OPI_MEASURE6') || ',
413 'iset.p_production_val',
410 ' || opi_dbi_rpt_util_pkg.change_pct_str ('iset.c_scrap_val',
411 'iset.c_production_val',
412 'iset.p_scrap_val',
414 'OPI_MEASURE7');
415 RETURN l_sel_clause;
416
417 END get_trend_sel_clause;
418
419
420 END opi_dbi_prod_scrap_pkg;