[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_CURR_PROD_DEL_RPT_PKG
Source
1 PACKAGE BODY OPI_DBI_CURR_PROD_DEL_RPT_PKG AS
2 /*$Header: OPIDCPDRPTB.pls 120.1 2005/08/11 02:42 sberi noship $ */
3 FUNCTION GET_CURR_PROD_DEL_SEL_CLAUSE(p_view_by_dim IN VARCHAR2, p_join_tbl IN
4 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
5 RETURN VARCHAR2;
6
7 /* -------------------------------------------------------------------------------------------
8 Procedure Name: GET_CURR_PROD_DEL_SQL
9 Parameters : p_param(IN parameter), x_custom_sql (OUT parameter)
10 Purpose : This procedure calls process parameters of the OPI util package to get things
11 like MV name, aggregation flag, View By and p_param (the parameter portlet).
12 It also forms the report query by calling the function GET_CURR_PROD_DEL_SEL_
13 CLAUSE
14 ----------------------------------------------------------------------------------------------
15 */
16 PROCEDURE GET_CURR_PROD_DEL_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
17 x_custom_sql OUT NOCOPY VARCHAR2,
18 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
19 IS
20 l_query VARCHAR2(15000);
21 l_view_by VARCHAR2(120);
22 l_view_by_col VARCHAR2 (120);
23 l_xtd VARCHAR2(10);
24 l_comparison_type VARCHAR2(1);
25 l_cur_suffix VARCHAR2(5);
26 l_custom_sql VARCHAR2 (10000);
27 l_subinv_val VARCHAR2 (120) := NULL;
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 l_where_clause VARCHAR2 (2000);
31 l_mv VARCHAR2 (30);
32 l_aggregation_level_flag VARCHAR2(10);
33 l_custom_rec BIS_QUERY_ATTRIBUTES;
34 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
35
36 BEGIN
37 -- initialization block
38 l_comparison_type := 'Y';
39 l_aggregation_level_flag := '0';
40
41 -- clear out the column and Join info tables.
42 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
43 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
44 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
45
46 -- get all the query parameters
47 opi_dbi_rpt_util_pkg.process_parameters (
48 p_param => p_param,
49 p_view_by => l_view_by,
50 p_view_by_col_name => l_view_by_col,
51 p_comparison_type => l_comparison_type,
52 p_xtd => l_xtd,
53 p_cur_suffix => l_cur_suffix,
54 p_where_clause => l_where_clause,
55 p_mv => l_mv,
56 p_join_tbl => l_join_tbl,
57 p_mv_level_flag => l_aggregation_level_flag,
58 p_trend => 'N',
59 p_func_area => 'OPI',
60 p_version => '8.0',
61 p_role => '',
62 p_mv_set => 'CPD',
63 p_mv_flag_type => 'ITEM_CAT');
64 -- Add measure columns that need to be aggregated
65
66 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
67 p_col_name => 'late_jobs_cnt' ,
68 p_alias_name => 'late_jobs_cnt',
69 p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
70 p_grand_total => 'Y',
71 p_to_date_type => 'NA'
72 );
73 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
74 p_col_name =>'late_jobs_val_' || l_cur_suffix,
75 p_alias_name =>'late_jobs_val',
76 p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
77 p_grand_total => 'Y',
78 p_to_date_type => 'NA'
79 );
80 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
81 p_col_name => 'open_jobs_cnt',
82 p_alias_name => 'open_jobs_cnt',
83 p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
84 p_grand_total => 'Y',
85 p_to_date_type => 'NA'
86 );
87 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
88 p_col_name =>'open_jobs_val_' || l_cur_suffix,
89 p_alias_name =>'open_jobs_val',
90 p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
91 p_grand_total => 'Y',
92 p_to_date_type => 'NA'
93 );
94
95 -- Quantity columns are only needed for Item viewby.
96 IF (l_view_by = 'ITEM+ENI_ITEM_ORG') THEN
97 --{
98 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
99 p_col_name => 'late_jobs_qty' ,
100 p_alias_name => 'late_jobs_qty',
101 p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
102 p_grand_total => 'N',
103 p_to_date_type => 'NA'
104 );
105 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
106 p_col_name => 'open_jobs_qty' ,
107 p_alias_name => 'open_jobs_qty',
108 p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
109 p_grand_total => 'N',
110 p_to_date_type => 'NA'
111 );
112 --}
113 END IF;
114 l_query := GET_CURR_PROD_DEL_SEL_CLAUSE (p_view_by_dim => l_view_by,
115 p_join_tbl => l_join_tbl)
116 || ' from
117 ' || poa_dbi_template_pkg.status_sql (p_fact_name => l_mv,
118 p_where_clause => l_where_clause,
119 p_join_tables => l_join_tbl,
120 p_use_windowing => 'Y',
121 p_col_name => l_col_tbl,
122 p_use_grpid => 'N',
123 p_paren_count => 3,
124 p_filter_where => NULL,
125 p_generate_viewby => 'Y',
126 p_in_join_tables => NULL);
127 -- prepare output for bind variables
128 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
129 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
130 -- set the basic bind variables for the status SQL
131 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
132
133 -- Passing OPI_AGGREGATION_LEVEL_FLAGS to PMV
134 l_custom_rec.attribute_name := ':OPI_ITEM_CAT_FLAG';
135 l_custom_rec.attribute_value := l_aggregation_level_flag;
136 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
137 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
138 x_custom_output.extend;
139 x_custom_output(x_custom_output.count) := l_custom_rec;
140 commit;
141 x_custom_sql := l_query;
142 END GET_CURR_PROD_DEL_SQL;
143
144 /*
145 ----------------------------------------------------------------------------------------------
146 Function Name: GET_CURR_PROD_DEL_SEL_CLAUSE
147 Parameters : p_view_by_dim(IN parameter), p_join_tbl (IN parameter)
148 Purpose : This function helps in constructing the report query of the Current Production
149 Report. It defines each attribute and measure and how we would source them
150 in our query.
151 ----------------------------------------------------------------------------------------------
152 */
153
154 FUNCTION GET_CURR_PROD_DEL_SEL_CLAUSE(p_view_by_dim IN VARCHAR2, p_join_tbl IN
155 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
156 RETURN VARCHAR2
157 IS
158 l_sel_clause VARCHAR2(15000);
159 l_view_by_col_name VARCHAR2(120);
160 l_description VARCHAR2(30);
161 l_uom VARCHAR2(30);
162 l_view_by_fact_col VARCHAR2(400);
163 BEGIN
164 l_description := 'null';
165 l_uom := 'null';
166 l_view_by_col_name := opi_dbi_rpt_util_pkg.get_view_by_col_name
167 (p_view_by_dim);
168 l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
169 (p_join_tbl);
170
171 opi_dbi_rpt_util_pkg.get_viewby_item_columns (p_view_by_dim, l_description, l_uom);
172
173
174 l_sel_clause :=
175 'SELECT
176 ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim) || fnd_global.newline ||
177 l_description || ' OPI_ATTRIBUTE1,
178 ' || l_uom || ' OPI_ATTRIBUTE2';
179
180 l_sel_clause := l_sel_clause ||
181 ',OPI_MEASURE1
182 ,OPI_MEASURE2
183 ,OPI_MEASURE3
184 ,OPI_MEASURE4
185 ,OPI_MEASURE5
186 ,OPI_MEASURE6
187 ,OPI_MEASURE7
188 ,OPI_MEASURE8
189 ,OPI_MEASURE11
190 ,OPI_MEASURE12
191 ,OPI_MEASURE13
192 ,OPI_MEASURE14
193 ,OPI_MEASURE15
194 ,OPI_MEASURE16'|| fnd_global.newline;
195
196 l_sel_clause := l_sel_clause ||
197 'FROM ( SELECT
198 rank() over (&ORDER_BY_CLAUSE nulls last '||', '||l_view_by_fact_col||') - 1 rnk
199 ,'||l_view_by_fact_col;
200
201 l_sel_clause := l_sel_clause ||
202 ',OPI_MEASURE1
203 ,OPI_MEASURE2
204 ,OPI_MEASURE3
205 ,OPI_MEASURE4
206 ,OPI_MEASURE5
207 ,OPI_MEASURE6
208 ,OPI_MEASURE7
209 ,OPI_MEASURE8
210 ,OPI_MEASURE11
211 ,OPI_MEASURE12
212 ,OPI_MEASURE13
213 ,OPI_MEASURE14
214 ,OPI_MEASURE15
215 ,OPI_MEASURE16'|| fnd_global.newline;
216
217 l_sel_clause := l_sel_clause ||
218 'FROM ( SELECT ' || fnd_global.newline ||
219 l_view_by_fact_col || fnd_global.newline ||
220 ',' || opi_dbi_rpt_util_pkg.nvl_str (
221 p_str => 'c_late_jobs_cnt',
222 p_default_val => 0) || ' OPI_MEASURE1, '|| fnd_global.newline;
223
224 IF (p_view_by_dim = 'ITEM+ENI_ITEM_ORG') THEN
225 --{
226 l_sel_clause := l_sel_clause ||
227 opi_dbi_rpt_util_pkg.nvl_str (
228 p_str => 'c_late_jobs_qty',
229 p_default_val => 0) || ' OPI_MEASURE2, '|| fnd_global.newline;
230 --}
231 ELSE
232 --{
233 l_sel_clause := l_sel_clause || 'NULL OPI_MEASURE2, '|| fnd_global.newline;
234 --}
235 END IF;
236
237 l_sel_clause := l_sel_clause ||
238 opi_dbi_rpt_util_pkg.nvl_str (
239 p_str => 'c_late_jobs_val',
240 p_default_val => 0) || ' OPI_MEASURE3,
241 ' || opi_dbi_rpt_util_pkg.nvl_str (
242 p_str => 'c_open_jobs_cnt',
243 p_default_val => 0) || ' OPI_MEASURE4,'|| fnd_global.newline;
244
245 IF (p_view_by_dim = 'ITEM+ENI_ITEM_ORG') THEN
246 --{
247 l_sel_clause := l_sel_clause ||
248 opi_dbi_rpt_util_pkg.nvl_str (
249 p_str => 'c_open_jobs_qty',
250 p_default_val => 0) || ' OPI_MEASURE5,'|| fnd_global.newline;
251 --}
252 ELSE
253 --{
254 l_sel_clause := l_sel_clause || 'NULL OPI_MEASURE5, '|| fnd_global.newline;
255 --}
256 END IF;
257
258 l_sel_clause := l_sel_clause ||
259 opi_dbi_rpt_util_pkg.nvl_str (
260 p_str => 'c_open_jobs_val',
261 p_default_val => 0) || ' OPI_MEASURE6,
262 ' || opi_dbi_rpt_util_pkg.percent_str(
263 p_numerator => 'c_late_jobs_cnt',
264 p_denominator => 'c_open_jobs_cnt',
265 p_measure_name => 'OPI_MEASURE7') || ',
266 ' || opi_dbi_rpt_util_pkg.percent_str(
267 p_numerator => 'c_late_jobs_val',
268 p_denominator => 'c_open_jobs_val',
269 p_measure_name => 'OPI_MEASURE8') || ',
270 ' || opi_dbi_rpt_util_pkg.nvl_str (
271 p_str => 'c_late_jobs_cnt_total',
272 p_default_val => 0) || ' OPI_MEASURE11,
273 ' || opi_dbi_rpt_util_pkg.nvl_str (
274 p_str => 'c_late_jobs_val_total',
275 p_default_val => 0) || ' OPI_MEASURE12,
276 ' || opi_dbi_rpt_util_pkg.nvl_str (
277 p_str => 'c_open_jobs_cnt_total',
278 p_default_val => 0) || ' OPI_MEASURE13,
279 ' || opi_dbi_rpt_util_pkg.nvl_str (
280 p_str => 'c_open_jobs_val_total',
281 p_default_val => 0) || ' OPI_MEASURE14,
282 ' || opi_dbi_rpt_util_pkg.percent_str(
283 p_numerator => 'c_late_jobs_cnt_total',
284 p_denominator => 'c_open_jobs_cnt_total',
285 p_measure_name => 'OPI_MEASURE15') || ',
286 ' || opi_dbi_rpt_util_pkg.percent_str(
287 p_numerator => 'c_late_jobs_val_total',
288 p_denominator => 'c_open_jobs_val_total',
289 p_measure_name => 'OPI_MEASURE16') ;
290
291 RETURN l_sel_clause;
292 END GET_CURR_PROD_DEL_SEL_CLAUSE;
293
294 END OPI_DBI_CURR_PROD_DEL_RPT_PKG;