[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_MFG_VAR_RPT_PKG
Source
1 PACKAGE BODY opi_dbi_mfg_var_rpt_pkg
2 /*$Header: OPIDMCVRPTB.pls 120.0 2005/05/24 17:46:20 appldev noship $ */
3 as
4
5 FUNCTION get_status_sel_clause(p_view_by_dim in VARCHAR2, p_period_type in VARCHAR2, p_org in VARCHAR2) return VARCHAR2;
6 FUNCTION get_trend_sel_clause return VARCHAR2;
7 PROCEDURE get_qty_columns(p_dim_name varchar2, p_description OUT NOCOPY varchar2, p_uom OUT NOCOPY varchar2, p_qty OUT NOCOPY varchar2);
8
9
10 PROCEDURE mfg_status_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
11 x_custom_sql OUT NOCOPY VARCHAR2,
12 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
13 IS
14
15 l_query VARCHAR2(15000);
16 l_view_by VARCHAR2(120);
17 l_view_by_col VARCHAR2 (120);
18 l_as_of_date DATE;
19 l_prev_as_of_date DATE;
20 l_xtd VARCHAR2(10);
21 l_comparison_type VARCHAR2(1) := 'Y';
22 l_nested_pattern NUMBER;
23 l_cur_suffix VARCHAR2(2);
24 l_custom_sql VARCHAR2 (10000);
25 l_period_type VARCHAR2(255) := NULL;
26 l_org VARCHAR2(255) := NULL;
27
28 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
29 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
30
31 l_where_clause VARCHAR2 (2000);
32 l_mv VARCHAR2 (30);
33
34 l_item_cat_flag varchar2(1) := '0';
35
36 l_custom_rec BIS_QUERY_ATTRIBUTES;
37
38 BEGIN
39
40 -- clear out the tables.
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 FOR i IN 1..p_param.COUNT
45 LOOP
46 IF(p_param(i).parameter_name = 'PERIOD_TYPE')
47 THEN l_period_type := p_param(i).parameter_value;
48 END IF;
49 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
50 THEN l_org := p_param(i).parameter_id;
51 END IF;
52 END LOOP;
53
54
55 -- get all the query parameters
56 opi_dbi_rpt_util_pkg.process_parameters (p_param,
57 l_view_by,
58 l_view_by_col,
59 l_comparison_type,
60 l_xtd,
61 l_cur_suffix,
62 l_where_clause,
63 l_mv,
64 l_join_tbl,
65 l_item_cat_flag,
66 'N',
67 'OPI',
68 '6.0',
69 '',
70 'MCV',
71 'ITEM_CAT');
72
73 -- The measure columns that need to be aggregated are
74 -- Std_value_<b/g>, Act_value_<b/g>
75 -- If viewing by item, then sum up
76 -- Actual_Qty_Completed
77 poa_dbi_util_pkg.add_column (l_col_tbl,
78 'Std_value_' || l_cur_suffix,
79 'Std_value');
80
81 poa_dbi_util_pkg.add_column (l_col_tbl,
82 'Act_value_' || l_cur_suffix,
83 'Act_value');
84
85
86 -- Quantity columns are only needed for Item viewby.
87 IF (l_view_by = 'ITEM+ENI_ITEM_ORG') THEN
88
89 poa_dbi_util_pkg.add_column (l_col_tbl,
90 'Actual_Qty_Completed',
91 'Act_Qty_Compl');
92 END IF;
93
94 -- construct the query
95 l_query := get_status_sel_clause (l_view_by, l_period_type, l_org)
96 || ' from ((
97 ' || poa_dbi_template_pkg.status_sql (l_mv,
98 l_where_clause,
99 l_join_tbl,
100 'N',
101 l_col_tbl,
102 'N');
103
104 -- prepare output for bind variables
105 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
106 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
107
108 -- set the basic bind variables for the status SQL
109 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
110
111 -- Passing OPI_ITEM_CAT_FLAG to PMV
112 l_custom_rec.attribute_name := ':OPI_ITEM_CAT_FLAG';
113 l_custom_rec.attribute_value := l_item_cat_flag;
114 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
115 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
116 x_custom_output.extend;
117 x_custom_output(x_custom_output.count) := l_custom_rec;
118
119 commit;
120
121 x_custom_sql := l_query;
122
123 end mfg_status_sql;
124
125
126 FUNCTION get_status_sel_clause(p_view_by_dim in VARCHAR2, p_period_type in VARCHAR2, p_org in VARCHAR2) return VARCHAR2
127 IS
128
129 l_sel_clause varchar2(4500);
130 l_view_by_col_name varchar2(60);
131 l_description varchar2(30);
132 l_uom varchar2(30);
133 l_qty varchar2(35);
134
135 BEGIN
136
137 /* Main Outer query */
138
139 -- Column to get view by column name
140 l_view_by_col_name := opi_dbi_rpt_util_pkg.get_view_by_col_name(p_view_by_dim);
141
142 get_qty_columns(p_view_by_dim, l_description, l_uom, l_qty);
143
144 l_sel_clause :=
145 'SELECT
146 ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
147 || l_view_by_col_name || ' OPI_ATTRIBUTE1,
148 '|| l_description || ' OPI_ATTRIBUTE2,
149 '|| l_uom || ' OPI_ATTRIBUTE3,';
150
151 IF ((p_view_by_dim = 'ITEM+ENI_ITEM_ORG') AND (p_period_type = 'FII_TIME_WEEK' OR p_period_type =
152 'FII_TIME_ENT_PERIOD') AND (UPPER(p_org)<>'ALL')) THEN
153 l_sel_clause := l_sel_clause || ' ''pFunctionName=OPI_DBI_MFG_CST_JOB_DTL_REP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_ORG&pParamIds=Y'' OPI_ATTRIBUTE4 ,';
154 ELSE
155 l_sel_clause := l_sel_clause || 'NULL OPI_ATTRIBUTE4 ,';
156 END IF;
157
158 l_sel_clause := l_sel_clause ||
159 'oset.C_STD_VALUE OPI_MEASURE1,
160 oset.C_ACT_VALUE OPI_MEASURE2,
161 oset.P_STD_VALUE OPI_MEASURE3,
162 oset.P_ACT_VALUE OPI_MEASURE4,
163 '|| l_qty || ' OPI_MEASURE5,
164 oset.P_ACT_VALUE - oset.P_STD_VALUE OPI_MEASURE20,
165 oset.C_ACT_VALUE - oset.C_STD_VALUE OPI_MEASURE6,
166 CASE WHEN oset.P_ACT_VALUE - oset.P_STD_VALUE = 0 THEN to_number(NULL)
167 ELSE (((oset.C_ACT_VALUE - oset.C_STD_VALUE) - (oset.P_ACT_VALUE - oset.P_STD_VALUE))/ABS(oset.P_ACT_VALUE - oset.P_STD_VALUE))*100 END OPI_MEASURE7,
168 decode(oset.P_STD_VALUE, 0, to_number(null), ((oset.P_ACT_VALUE - oset.P_STD_VALUE)/oset.P_STD_VALUE)*100) OPI_MEASURE21,
169 decode(oset.C_STD_VALUE, 0, to_number(null), ((oset.C_ACT_VALUE - oset.C_STD_VALUE)/oset.C_STD_VALUE)*100) OPI_MEASURE8,
170 CASE WHEN oset.C_STD_VALUE = 0 THEN to_number(NULL)
171 WHEN oset.P_STD_VALUE = 0 THEN to_number(NULL)
172 ELSE (((oset.C_ACT_VALUE - oset.C_STD_VALUE)/oset.C_STD_VALUE)*100 - ((oset.P_ACT_VALUE - oset.P_STD_VALUE)/oset.P_STD_VALUE)*100) END OPI_MEASURE9,
173 oset.C_STD_VALUE_TOTAL OPI_MEASURE10,
174 oset.C_ACT_VALUE_TOTAL OPI_MEASURE11,
175 oset.C_ACT_VALUE_TOTAL - oset.C_STD_VALUE_TOTAL OPI_MEASURE12,
176 CASE WHEN oset.P_ACT_VALUE_TOTAL - oset.P_STD_VALUE_TOTAL = 0 THEN to_number(NULL)
177 ELSE (((oset.C_ACT_VALUE_TOTAL - oset.C_STD_VALUE_TOTAL) - (oset.P_ACT_VALUE_TOTAL - oset.P_STD_VALUE_TOTAL))/ABS(oset.P_ACT_VALUE_TOTAL - oset.P_STD_VALUE_TOTAL))*100 END OPI_MEASURE13,
178 CASE WHEN oset.C_STD_VALUE_TOTAL = 0 THEN to_number(NULL)
179 ELSE ((oset.C_ACT_VALUE_TOTAL - oset.C_STD_VALUE_TOTAL)/oset.C_STD_VALUE_TOTAL)*100 END OPI_MEASURE14,
180 CASE WHEN oset.C_STD_VALUE_TOTAL = 0 THEN to_number(NULL)
181 WHEN oset.P_STD_VALUE_TOTAL = 0 THEN to_number(NULL)
182 ELSE ((oset.C_ACT_VALUE_TOTAL - oset.C_STD_VALUE_TOTAL)*100)/(oset.C_STD_VALUE_TOTAL) -
183 ((oset.P_ACT_VALUE_TOTAL - oset.P_STD_VALUE_TOTAL)*100)/(oset.P_STD_VALUE_TOTAL) END OPI_MEASURE15,
184 decode(oset.C_STD_VALUE,0, to_number(null), ((oset.C_ACT_VALUE - oset.C_STD_VALUE)/oset.C_STD_VALUE)*100) OPI_MEASURE16,
185 decode(oset.P_STD_VALUE,0, to_number(null), ((oset.P_ACT_VALUE - oset.P_STD_VALUE)/oset.P_STD_VALUE)*100) OPI_MEASURE17,
186 CASE WHEN oset.C_STD_VALUE_TOTAL = 0 THEN to_number(NULL)
187 ELSE ((oset.C_ACT_VALUE_TOTAL - oset.C_STD_VALUE_TOTAL)/oset.C_STD_VALUE_TOTAL)*100 END OPI_MEASURE18,
188 CASE WHEN oset.P_STD_VALUE_TOTAL = 0 THEN to_number(NULL)
189 ELSE ((oset.P_ACT_VALUE_TOTAL - oset.P_STD_VALUE_TOTAL)/oset.P_STD_VALUE_TOTAL)*100 END OPI_MEASURE19 ';
190
191 return l_sel_clause;
192
193 END get_status_sel_clause;
194
195
196 PROCEDURE get_qty_columns(p_dim_name varchar2, p_description OUT NOCOPY varchar2, p_uom OUT NOCOPY varchar2, p_qty OUT NOCOPY varchar2)
197 IS
198 l_description varchar2(30);
199 l_uom varchar2(30);
200 l_qty varchar2(30);
201 BEGIN
202 CASE
203 WHEN p_dim_name = 'ITEM+ENI_ITEM_ORG' THEN
204 BEGIN
205 p_description := 'v.description';
206 p_uom := 'v2.unit_of_measure';
207 p_qty := 'oset.C_Act_Qty_Compl';
208 END;
209 ELSE
210 BEGIN
211 p_description := 'null';
212 p_uom := 'null';
213 p_qty := 'null';
214 END;
215 END CASE;
216 END get_qty_columns;
217
218
219 PROCEDURE mfg_trend_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
220 x_custom_sql OUT NOCOPY VARCHAR2,
221 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
222 IS
223 l_query VARCHAR2(15000);
224 l_view_by VARCHAR2(120);
225 l_view_by_col VARCHAR2 (120);
226 l_as_of_date DATE;
227 l_prev_as_of_date DATE;
228 l_xtd varchar2(10);
229 l_comparison_type VARCHAR2(1) := 'Y';
230 l_nested_pattern NUMBER;
231 l_cur_suffix VARCHAR2(2);
232 l_custom_sql VARCHAR2(4000);
233 l_mv VARCHAR2 (30);
234 l_where_clause VARCHAR2 (4000) := '';
235
236 l_item_cat_flag VARCHAR2(1) := '0';
237
238 l_custom_rec BIS_QUERY_ATTRIBUTES;
239
240 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
241 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
242
243 BEGIN
244
245 -- clear out the tables.
246 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
247 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
248
249 -- get all the query parameters
250 opi_dbi_rpt_util_pkg.process_parameters (p_param,
251 l_view_by,
252 l_view_by_col,
253 l_comparison_type,
254 l_xtd,
255 l_cur_suffix,
256 l_where_clause,
257 l_mv,
258 l_join_tbl,
259 l_item_cat_flag,
260 'Y',
261 'OPI',
262 '6.0',
263 '',
264 'MCV',
265 'ITEM_CAT');
266
267
268 -- The measure columns that need to be aggregated are
269 -- Std_value_<b/g>, Act_value_<b/g>
270 -- No Grand totals required.
271 poa_dbi_util_pkg.add_column (l_col_tbl,
272 'Std_value_' || l_cur_suffix,
273 'Std_value',
274 'N');
275 poa_dbi_util_pkg.add_column (l_col_tbl,
276 'Act_value_' || l_cur_suffix,
277 'Act_value',
278 'N');
279
280
281 -- Joining Outer and Inner Query
282 l_query := get_trend_sel_clause ||
283 ' from ' ||
284 poa_dbi_template_pkg.trend_sql (
285 l_xtd,
286 l_comparison_type,
287 l_mv,
288 l_where_clause,
289 l_col_tbl,
290 'N');
291
292
293 -- Prepare PMV bind variables
294 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
295 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
296
297 -- get all the basic binds used by POA queries
298 -- Do this before adding any of our binds, since the procedure
299 -- reinitializes the output table
300 poa_dbi_util_pkg.get_custom_trend_binds (l_xtd, l_comparison_type,
301 x_custom_output);
302
303 -- put the custom OPI binds in
304 l_custom_rec.attribute_name := ':OPI_ITEM_CAT_FLAG';
305 l_custom_rec.attribute_value := l_item_cat_flag;
306 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
307 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
308 x_custom_output.extend;
309 x_custom_output(x_custom_output.count) := l_custom_rec;
310
311 commit;
312
313 x_custom_sql := l_query;
314
315 END mfg_trend_sql;
316
317
318 FUNCTION get_trend_sel_clause return VARCHAR2
319 IS
320 l_sel_clause varchar2(4000);
321 BEGIN
322
323 l_sel_clause :=
324 'Select
325 cal.name VIEWBY,
326 iset.C_STD_VALUE OPI_MEASURE1,
327 iset.C_ACT_VALUE OPI_MEASURE2,
328 iset.P_STD_VALUE OPI_MEASURE3,
329 iset.P_ACT_VALUE OPI_MEASURE4,
330 iset.P_ACT_VALUE - iset.P_STD_VALUE OPI_MEASURE5,
331 iset.C_ACT_VALUE - iset.C_STD_VALUE OPI_MEASURE6,
332 CASE WHEN iset.P_ACT_VALUE - iset.P_STD_VALUE = 0 THEN to_number(NULL)
333 ELSE (((iset.C_ACT_VALUE - iset.C_STD_VALUE) - (iset.P_ACT_VALUE - iset.P_STD_VALUE))/ABS(iset.P_ACT_VALUE - iset.P_STD_VALUE))*100 END OPI_MEASURE7,
334 decode(iset.P_STD_VALUE, 0, to_number(null), ((iset.P_ACT_VALUE - iset.P_STD_VALUE)/iset.P_STD_VALUE)*100) OPI_MEASURE8,
335 decode(iset.C_STD_VALUE, 0, to_number(null), ((iset.C_ACT_VALUE - iset.C_STD_VALUE)/iset.C_STD_VALUE)*100) OPI_MEASURE9,
336 CASE WHEN iset.C_STD_VALUE = 0 THEN to_number(NULL)
337 WHEN iset.P_STD_VALUE = 0 THEN to_number(NULL)
338 ELSE ((iset.C_ACT_VALUE - iset.C_STD_VALUE)/iset.C_STD_VALUE)*100 - ((iset.P_ACT_VALUE - iset.P_STD_VALUE)/iset.P_STD_VALUE)*100 END OPI_MEASURE10 ';
339
340 return l_sel_clause;
341
342 END get_trend_sel_clause ;
343
344
345 end opi_dbi_mfg_var_rpt_pkg;