[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_SCRAP_REASON_RPT_PKG
Source
1 PACKAGE BODY OPI_DBI_SCRAP_REASON_RPT_PKG AS
2 /*$Header: OPIDSBRRPTB.pls 120.0 2005/09/18 22:10 sberi noship $ */
3 FUNCTION GET_SCRAP_REASON_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 /* -------------------------------------------------------------------------------------------
9 Procedure Name: GET_SCRAP_REASON_SQL
10 Parameters : p_param(IN parameter), x_custom_sql (OUT parameter)
11 Purpose : This procedure calls process parameters of the OPI util package to get things
12 like MV name, aggregation flag, View By and p_param (the parameter portlet).
13 It also forms the report query by calling the function GET_SCRAP_REASON_SEL_
14 CLAUSE
15 ----------------------------------------------------------------------------------------------
16 */
17 PROCEDURE GET_SCRAP_REASON_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
18 x_custom_sql OUT NOCOPY VARCHAR2,
19 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
20 IS
21 l_query VARCHAR2(15000);
22 l_view_by VARCHAR2(120);
23 l_view_by_col VARCHAR2 (120);
24 l_xtd VARCHAR2(10);
25 l_comparison_type VARCHAR2(1);
26 l_cur_suffix VARCHAR2(5);
27 l_custom_sql VARCHAR2 (10000);
28 l_subinv_val VARCHAR2 (120) := NULL;
29 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
30 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
31 l_where_clause VARCHAR2 (2000);
32 l_mv VARCHAR2 (30);
33 l_aggregation_level_flag VARCHAR2(10);
34 l_custom_rec BIS_QUERY_ATTRIBUTES;
35 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
36
37 BEGIN
38 -- initialization block
39 l_comparison_type := 'Y';
40 l_aggregation_level_flag := '0';
41
42 -- clear out the column and Join info tables.
43 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
44 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
45 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
46
47 -- get all the query parameters
48 opi_dbi_rpt_util_pkg.process_parameters (
49 p_param => p_param,
50 p_view_by => l_view_by,
51 p_view_by_col_name => l_view_by_col,
52 p_comparison_type => l_comparison_type,
53 p_xtd => l_xtd,
54 p_cur_suffix => l_cur_suffix,
55 p_where_clause => l_where_clause,
56 p_mv => l_mv,
57 p_join_tbl => l_join_tbl,
58 p_mv_level_flag => l_aggregation_level_flag,
59 p_trend => 'N',
60 p_func_area => 'OPI',
61 p_version => '8.0',
62 p_role => '',
63 p_mv_set => 'SBR',
64 p_mv_flag_type => 'TRX_REASON_LEVEL');
65 -- Add measure columns that need to be aggregated
66
67 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
68 p_col_name =>'scrap_val_' || l_cur_suffix,
69 p_alias_name =>'scrap_val',
70 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
71 p_grand_total => 'Y',
72 p_to_date_type => 'XTD'
73 );
74
75 l_query := GET_SCRAP_REASON_SEL_CLAUSE (p_view_by_dim => l_view_by,
76 p_join_tbl => l_join_tbl)
77 || ' from
78 ' || poa_dbi_template_pkg.status_sql (p_fact_name => l_mv,
79 p_where_clause => l_where_clause,
80 p_join_tables => l_join_tbl,
81 p_use_windowing => 'Y',
82 p_col_name => l_col_tbl,
83 p_use_grpid => 'N',
84 p_paren_count => 3,
85 p_filter_where => NULL,
86 p_generate_viewby => 'Y',
87 p_in_join_tables => NULL);
88 -- prepare output for bind variables
89 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
90 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
91 -- set the basic bind variables for the status SQL
92 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
93
94 -- Passing OPI_AGGREGATION_LEVEL_FLAGS to PMV
95 l_custom_rec.attribute_name := ':OPI_TRX_REASON_FLAG';
96 l_custom_rec.attribute_value := l_aggregation_level_flag;
97 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
98 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
99 x_custom_output.extend;
100 x_custom_output(x_custom_output.count) := l_custom_rec;
101 commit;
102 x_custom_sql := l_query;
103 END GET_SCRAP_REASON_SQL;
104
105 /*
106 ----------------------------------------------------------------------------------------------
107 Function Name: GET_SCRAP_REASON_SEL_CLAUSE
108 Parameters : p_view_by_dim(IN parameter), p_join_tbl (IN parameter)
109 Purpose : This function helps in constructing the report query of the Scrap By Reason
110 Report. It defines each attribute and measure and how we would source them
111 in our query.
112 ----------------------------------------------------------------------------------------------
113 */
114
115 FUNCTION GET_SCRAP_REASON_SEL_CLAUSE(p_view_by_dim IN VARCHAR2, p_join_tbl IN
116 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
117 RETURN VARCHAR2
118 IS
119 l_sel_clause VARCHAR2(15000);
120 l_view_by_col_name VARCHAR2(120);
121 l_view_by_fact_col VARCHAR2(400);
122 BEGIN
123 l_view_by_col_name := opi_dbi_rpt_util_pkg.get_view_by_col_name
124 (p_view_by_dim);
125 l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
126 (p_join_tbl);
127 l_sel_clause :=
128 'SELECT
129 ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim) || fnd_global.newline;
130
131 l_sel_clause := l_sel_clause ||
132 ' OPI_MEASURE1
133 ,OPI_MEASURE2
134 ,OPI_MEASURE3
135 ,OPI_MEASURE4
136 ,OPI_MEASURE5
137 ,OPI_MEASURE6
138 ,OPI_MEASURE7'|| fnd_global.newline;
139
140 l_sel_clause := l_sel_clause ||
141 'FROM ( SELECT
142 rank() over (&ORDER_BY_CLAUSE nulls last '||', '||l_view_by_fact_col||') - 1 rnk
143 ,'||l_view_by_fact_col;
144
145 l_sel_clause := l_sel_clause ||
146 ',OPI_MEASURE1
147 ,OPI_MEASURE2
148 ,OPI_MEASURE3
149 ,OPI_MEASURE4
150 ,OPI_MEASURE5
151 ,OPI_MEASURE6
152 ,OPI_MEASURE7'|| fnd_global.newline;
153
154 l_sel_clause := l_sel_clause ||
155 'FROM ( SELECT ' || fnd_global.newline ||
156 l_view_by_fact_col || fnd_global.newline ||
157 ',' || opi_dbi_rpt_util_pkg.nvl_str (
158 p_str => 'p_scrap_val',
159 p_default_val => 0) || ' OPI_MEASURE1,
160 ' || opi_dbi_rpt_util_pkg.nvl_str (
161 p_str => 'c_scrap_val',
162 p_default_val => 0) || ' OPI_MEASURE2,
163 ' || opi_dbi_rpt_util_pkg.change_str (
164 p_new_numerator => 'c_scrap_val',
165 p_old_numerator => 'p_scrap_val',
166 p_denominator => 'p_scrap_val',
167 p_measure_name => 'OPI_MEASURE3')|| ',
168 ' || opi_dbi_rpt_util_pkg.percent_str(
169 p_numerator => 'c_scrap_val',
170 p_denominator => 'c_scrap_val_total',
171 p_measure_name => 'OPI_MEASURE4')|| ',
172 ' || opi_dbi_rpt_util_pkg.nvl_str (
173 p_str => 'c_scrap_val_total',
174 p_default_val => 0) || ' OPI_MEASURE5,
175 ' || opi_dbi_rpt_util_pkg.change_str (
176 p_new_numerator => 'c_scrap_val_total',
177 p_old_numerator => 'p_scrap_val_total',
178 p_denominator => 'p_scrap_val_total',
179 p_measure_name => 'OPI_MEASURE6')|| ',
180 ' || opi_dbi_rpt_util_pkg.percent_str(
181 p_numerator => 'c_scrap_val_total',
182 p_denominator => 'c_scrap_val_total',
183 p_measure_name => 'OPI_MEASURE7');
184
185 RETURN l_sel_clause;
186
187 END GET_SCRAP_REASON_SEL_CLAUSE;
188
189 END OPI_DBI_SCRAP_REASON_RPT_PKG;