[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_CURR_VAR_RPT_PKG
Source
1 PACKAGE BODY opi_dbi_curr_var_rpt_pkg
2 /*$Header: OPIDCUVRPTB.pls 120.0 2005/05/24 19:02:18 appldev noship $ */
3 as
4
5 FUNCTION get_status_sel_clause(p_view_by_dim in VARCHAR2, p_org in varchar2) return VARCHAR2;
6
7 FUNCTION status_sql( p_fact_name in varchar2,
8 p_where_clause in varchar2,
9 p_join_tables in poa_dbi_util_pkg.POA_DBI_JOIN_TBL,
10 p_use_windowing in varchar2,
11 p_col_name in poa_dbi_util_pkg.POA_DBI_COL_TBL
12 ) RETURN varchar2;
13
14 PROCEDURE get_qty_columns(p_dim_name varchar2, p_description OUT NOCOPY varchar2, p_uom OUT NOCOPY varchar2, p_qty OUT NOCOPY varchar2);
15
16
17
18 PROCEDURE curr_status_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
19 x_custom_sql OUT NOCOPY VARCHAR2,
20 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
21 IS
22 l_query VARCHAR2(15000);
23 l_view_by VARCHAR2(120);
24 l_view_by_col VARCHAR2 (120);
25 l_as_of_date DATE;
26 l_prev_as_of_date DATE;
27 l_xtd VARCHAR2(10);
28 l_comparison_type VARCHAR2(1) := 'Y';
29 l_nested_pattern NUMBER;
30 l_cur_suffix VARCHAR2(2);
31 l_custom_sql VARCHAR2 (10000);
32 l_period_type VARCHAR2(255) := NULL;
33
34 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
35 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
36
37 l_where_clause VARCHAR2 (2000);
38 l_mv VARCHAR2 (30);
39 l_org varchar(30);
40
41 l_item_cat_flag varchar2(1) := '0';
42
43 l_custom_rec BIS_QUERY_ATTRIBUTES;
44
45 BEGIN
46
47 -- clear out the tables.
48 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
49 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
50 FOR i IN 1..p_param.COUNT
51 LOOP
52 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
53 THEN l_org := p_param(i).parameter_id;
54 END IF;
55
56 END LOOP;
57
58 -- get all the query parameters
59 opi_dbi_rpt_util_pkg.process_parameters (p_param,
60 l_view_by,
61 l_view_by_col,
62 l_comparison_type,
63 l_xtd,
64 l_cur_suffix,
65 l_where_clause,
66 l_mv,
67 l_join_tbl,
68 l_item_cat_flag,
69 'N',
70 'OPI',
71 '6.0',
72 '',
73 'CUV',
74 'NONE');
75
76
77 -- The measure columns that need to be aggregated are
78 -- STANDARD_VALUE_<b/g>, ACTUAL_VALUE_<b/g>
79 -- If viewing by item as, then sum up
80 -- ACTUAL_PRD_QTY
81 poa_dbi_util_pkg.add_column (l_col_tbl,
82 'STANDARD_VALUE_' || l_cur_suffix,
83 'STANDARD_VALUE');
84
85 poa_dbi_util_pkg.add_column (l_col_tbl,
86 'ACTUAL_VALUE_' || l_cur_suffix,
87 'ACTUAL_VALUE');
88
89 -- Quantity columns are only needed for Item viewby.
90 IF (l_view_by = 'ITEM+ENI_ITEM_ORG') THEN
91
92 poa_dbi_util_pkg.add_column (l_col_tbl,
93 'ACTUAL_PRD_QTY',
94 'ACTUAL_PRD_QTY');
95
96 END IF;
97
98
99 -- construct the query
100 l_query := get_status_sel_clause (l_view_by, l_org)
101 || ' from ((
102 ' || status_sql ('OPI_DBI_CURR_UNREC_VAR_F',
103 l_where_clause,
104 l_join_tbl,
105 'N',
106 l_col_tbl
107 );
108
109
110 -- prepare output for bind variables
111 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
112 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
113
114 -- set the basic bind variables for the status SQL
115 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
116
117 -- Passing OPI_ITEM_CAT_FLAG to PMV
118 l_custom_rec.attribute_name := ':OPI_ITEM_CAT_FLAG';
119 l_custom_rec.attribute_value := l_item_cat_flag;
120 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
121 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
122 x_custom_output.extend;
123 x_custom_output(x_custom_output.count) := l_custom_rec;
124
125 commit;
126
127 x_custom_sql := l_query;
128
129 end curr_status_sql;
130
131
132
133 FUNCTION get_status_sel_clause(p_view_by_dim in VARCHAR2, p_org in varchar2) return VARCHAR2
134 IS
135
136 l_sel_clause varchar2(4500);
137 l_view_by_col_name varchar2(60);
138 l_description varchar2(30);
139 l_uom varchar2(30);
140 l_qty varchar2(35);
141 l_id varchar2(30);
142
143 BEGIN
144
145 /* Main Outer query */
146
147 -- Column to get view by column name
148 l_view_by_col_name := opi_dbi_rpt_util_pkg.get_view_by_col_name(p_view_by_dim);
149
150 -- Quantity column
151 get_qty_columns(p_view_by_dim, l_description, l_uom, l_qty);
152
153 l_sel_clause :=
154 'SELECT
155 '|| opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
156 || l_view_by_col_name || ' OPI_ATTRIBUTE1,
157 '|| l_description || ' OPI_ATTRIBUTE2,
158 '|| l_uom || ' OPI_ATTRIBUTE3,';
159
160 IF ((p_view_by_dim = 'ITEM+ENI_ITEM_ORG') AND (UPPER(p_org)<>'ALL')) THEN
161 l_sel_clause := l_sel_clause || ' ''pFunctionName=OPI_DBI_OPEN_JOB_DTL_REP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_ORG&pParamIds=Y'' OPI_ATTRIBUTE4 ,';
162 ELSE
163 l_sel_clause := l_sel_clause || 'NULL OPI_ATTRIBUTE4 ,';
164 END IF;
165
166 l_sel_clause := l_sel_clause || '
167 oset.C_STANDARD_VALUE OPI_MEASURE1,
168 oset.C_ACTUAL_VALUE OPI_MEASURE2,
169 '|| l_qty || ' OPI_MEASURE3,
170 oset.C_ACTUAL_VALUE - oset.C_STANDARD_VALUE OPI_MEASURE4,
171 decode(oset.C_STANDARD_VALUE,0, to_number(null), ((oset.C_ACTUAL_VALUE - oset.C_STANDARD_VALUE)/oset.C_STANDARD_VALUE)*100) OPI_MEASURE5,
172 oset.C_STANDARD_VALUE_TOTAL OPI_MEASURE6,
173 oset.C_ACTUAL_VALUE_TOTAL OPI_MEASURE7,
174 oset.C_ACTUAL_VALUE_TOTAL - oset.C_STANDARD_VALUE_TOTAL OPI_MEASURE8,
175 CASE WHEN oset.C_STANDARD_VALUE_TOTAL = 0 THEN to_number(NULL)
176 ELSE ((oset.C_ACTUAL_VALUE_TOTAL - oset.C_STANDARD_VALUE_TOTAL)/oset.C_STANDARD_VALUE_TOTAL)*100 END OPI_MEASURE9 ';
177
178 return l_sel_clause;
179
180 END get_status_sel_clause;
181
182
183
184 PROCEDURE get_qty_columns(p_dim_name varchar2, p_description OUT NOCOPY varchar2, p_uom OUT NOCOPY varchar2, p_qty OUT NOCOPY varchar2)
185 IS
186 l_description varchar2(30);
187 l_uom varchar2(30);
188 l_qty varchar2(30);
189 BEGIN
190 CASE
191 WHEN p_dim_name = 'ITEM+ENI_ITEM_ORG' THEN
192 BEGIN
193 p_description := 'v.description';
194 p_uom := 'v2.unit_of_measure';
195 p_qty := 'oset.C_ACTUAL_PRD_QTY';
196 END;
197 ELSE
198 BEGIN
199 p_description := 'null';
200 p_uom := 'null';
201 p_qty := 'null';
202 END;
203 END CASE;
204 END get_qty_columns;
205
206
207
208 FUNCTION status_sql(p_fact_name in varchar2,
209 p_where_clause in varchar2,
210 p_join_tables in poa_dbi_util_pkg.POA_DBI_JOIN_TBL,
211 p_use_windowing in varchar2,
212 p_col_name in poa_dbi_util_pkg.POA_DBI_COL_TBL
213 ) RETURN varchar2
214 IS
215 l_query varchar2(10000);
216 l_col_names varchar2(10000);
217 l_group_and_sel_clause varchar2(10000);
218 l_from_clause varchar2(10000);
219 l_where_clause varchar2(10000);
220 l_grpid_clause varchar2(200);
221 l_compute_prior VARCHAR2(1) := 'N';
222 l_compute_prev_prev VARCHAR(1) := 'N';
223
224 BEGIN
225
226 l_group_and_sel_clause := ' fact.' || p_join_tables(1).fact_column ;
227 l_from_clause := p_join_tables(1).table_name || ' ' || p_join_tables(1).table_alias;
228
229 l_where_clause := 'oset.' || p_join_tables(1).fact_column || '=' ||
230 p_join_tables(1).table_alias || '.' || p_join_tables(1).column_name;
231
232 if (p_join_tables(1).dim_outer_join = 'Y') then
233 l_where_clause := l_where_clause || '(+)';
234 end if;
235
236 if (p_join_tables(1).additional_where_clause is NOT NULL) then
237 l_where_clause := l_where_clause || ' and ' || p_join_tables(1).additional_where_clause;
238 end if;
239
240
241 FOR i IN 2 .. p_join_tables.COUNT
242 LOOP
243 l_group_and_sel_clause := l_group_and_sel_clause || ', fact.'
244 || p_join_tables(i).fact_column;
245 l_from_clause := l_from_clause || ', ' || p_join_tables(i).table_name ||
246 ' ' || p_join_tables(i).table_alias;
247
248
249 l_where_clause := l_where_clause || ' and oset.'
250 || p_join_tables(i).fact_column || '=' ||
251 p_join_tables(i).table_alias || '.' || p_join_tables(i).column_name;
252 if (p_join_tables(i).dim_outer_join = 'Y') then
253 l_where_clause := l_where_clause || '(+)';
254 end if;
255 if(p_join_tables(i).additional_where_clause is NOT NULL) then
256 l_where_clause := l_where_clause || ' and ' || p_join_tables(i).additional_where_clause;
257 end if;
258 END LOOP;
259
260 FOR i IN 1 .. p_col_name.COUNT
261 LOOP
262 l_col_names := l_col_names || ',
263 sum(' || p_col_name(i).column_name || ') c_' || p_col_name(i).column_alias;
264
265 if(p_col_name(i).grand_total = 'Y') then
266 l_col_names := l_col_names || ',
267 sum(sum('
268 || p_col_name(i).column_name || ')) over () c_'
269 || p_col_name(i).column_alias || '_total ';
270 end if;
271 END LOOP;
272
273
274 l_query := '(select ' || l_group_and_sel_clause || l_col_names
275 || '
276 from ' || p_fact_name || ' fact
277 where 1=1 '
278 || p_where_clause;
279
280
281 l_query := l_query || '
282 group by ' || l_group_and_sel_clause || ') ) ) oset,
283 ' || l_from_clause || '
284 where ' || l_where_clause;
285
286 if(p_use_windowing = 'Y') then
287 l_query := l_query || '
288 and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)';
289 end if;
290
291 l_query := l_query || '
292 &ORDER_BY_CLAUSE nulls last';
293
294 return l_query;
295
296 end status_sql;
297
298
299 end opi_dbi_curr_var_rpt_pkg;