[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_RVT_PKG
Source
1 PACKAGE BODY ENI_DBI_RVT_PKG AS
2 /* $Header: ENIRVTPB.pls 120.0 2005/05/26 19:32:38 appldev noship $ */
3
4 PROCEDURE GET_SQL( p_param IN BIS_PMV_PAGE_PARAMETER_TBL
5 , x_custom_sql OUT NOCOPY VARCHAR2
6 , x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL ) IS
7
8 l_stmt VARCHAR2(32000);
9 l_flags_where VARCHAR2(1000);
10 l_org VARCHAR2(32000);
11 l_org_where VARCHAR2(32000);
12 l_prod VARCHAR2(32000);
13 l_prod_where VARCHAR2(32000);
14 l_prod_cat VARCHAR2(32000);
15 l_prod_cat_from VARCHAR2(32000);
16 l_prod_cat_where VARCHAR2(32000);
17 l_cust VARCHAR2(32000);
18 l_cust_where VARCHAR2(32000);
19 l_ret_reason VARCHAR2(32000);
20 l_ret_reason_where VARCHAR2(32000);
21 l_period_type VARCHAR2(32000);
22 l_temp VARCHAR2(480);
23 l_sql_stmt VARCHAR2(32000);
24
25 l_curr_suffix VARCHAR2(10);
26
27 l_item_cat_flag NUMBER; -- 0 for product, 1 for product category, 3 for no grouping on item dimension
28 l_cust_flag NUMBER; -- 0 for customer and 1 for no customer selected
29 l_reason_flag NUMBER; -- 0 for reason and 1 for all reasons
30 l_mv VARCHAR2(10);
31 l_custom_rec BIS_QUERY_ATTRIBUTES;
32
33 l_all_prods BOOLEAN;
34 l_all_prod_cats BOOLEAN;
35 l_all_custs BOOLEAN;
36 l_all_reasons BOOLEAN;
37
38 l_time_comp_type VARCHAR2(32000);
39 l_time_comp_where VARCHAR2(32000);
40
41 l_order_by VARCHAR2(200);
42 l_period_id_col VARCHAR2(100);
43 l_period_diff NUMBER;
44 BEGIN
45
46 FOR i IN 1..p_param.COUNT LOOP
47 CASE p_param(i).parameter_name
48 WHEN 'PERIOD_TYPE' THEN l_period_type := p_param(i).parameter_value;
49 WHEN 'ITEM+ENI_ITEM_VBH_CAT' THEN l_prod_cat := p_param(i).parameter_value;
50 WHEN 'ITEM+ENI_ITEM' THEN l_prod := p_param(i).parameter_value;
51 WHEN 'CUSTOMER+FII_CUSTOMERS' THEN l_cust := p_param(i).parameter_value;
52 WHEN 'ORDER_ITEM_RETURN_REASON+ORDER_ITEM_RETURN_REASON' THEN l_ret_reason := p_param(i).parameter_id;
53 WHEN 'ORDERBY' THEN l_order_by := p_param(i).parameter_value;
54 WHEN 'TIME_COMPARISON_TYPE' THEN l_time_comp_type := p_param(i).parameter_value;
55 WHEN 'CURRENCY+FII_CURRENCIES' THEN
56 l_curr_suffix :=
57 CASE p_param(i).parameter_id
58 WHEN eni_dbi_util_pkg.get_curr_prim THEN 'g' -- primary global currency
59 WHEN eni_dbi_util_pkg.get_curr_sec THEN 'g1' -- secondary global currency
60 ELSE 'f' -- functional currency
61 END;
62 ELSE NULL;
63 END CASE;
64 END LOOP;
65
66 l_all_prods := (l_prod IS NULL OR l_prod = '' OR l_prod = 'All');
67 l_all_prod_cats := (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All');
68 l_all_custs := (l_cust IS NULL OR l_cust = '' OR l_cust = 'All');
69 l_all_reasons := (l_ret_reason IS NULL OR l_ret_reason = '' OR l_ret_reason = 'All');
70
71 IF( /* l_all_prod_cats
72 AND */ l_all_prods
73 AND l_all_custs
74 AND l_all_reasons )
75 THEN
76 l_mv := '011';
77 l_flags_where := '
78 AND fact.inv_org_flag = 1
79 AND fact.return_flag = 1';
80 -- look at return rollups across all inv_org_ids
81
82 IF l_all_prod_cats THEN
83 l_prod_cat_where := '
84 AND fact.top_node_flag = ''Y'' '; -- no cat specified, so examine top nodes only
85 ELSE
86 l_prod_cat_where := '
87 AND fact.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)'; -- cat specified
88 END IF;
89 ELSE
90 /* Seems like there's no reason to use anything but 007 in this case */
91 l_mv := '007';
92 l_flags_where := '
93 AND fact.item_cat_flag = :ENI_ITEM_CAT_FLAG
94 AND fact.customer_flag = :ENI_CUST_FLAG
95 AND fact.return_reason_flag = :ENI_REASON_FLAG';
96
97 IF l_all_prod_cats THEN
98 l_prod_cat_from := '';
99 l_prod_cat_where := '';
100 ELSE
101 l_prod_cat_from := '
102 , ENI_DENORM_HIERARCHIES eni_cat
103 , MTL_DEFAULT_CATEGORY_SETS mdcs';
104 l_prod_cat_where := '
105 AND NVL( fact.item_category_id(+), -1 ) = eni_cat.child_id
106 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
107 AND eni_cat.dbi_flag = ''Y''
108 AND eni_cat.object_type = ''CATEGORY_SET''
109 AND eni_cat.object_id = mdcs.category_set_id
110 AND mdcs.functional_area_id = 11';
111 END IF;
112
113 IF l_all_prods THEN
114 l_prod_where := '';
115 ELSE
116 l_prod_where := '
117 AND fact.master_item_id IN (&ITEM+ENI_ITEM)';
118 END IF;
119
120 IF l_all_custs THEN
121 l_cust_where:='';
122 l_cust_flag := 1; -- all customers
123 ELSE
124 l_cust_where :='
125 AND fact.customer_id IN (&CUSTOMER+FII_CUSTOMERS)';
126 l_cust_flag := 0; -- customer selected
127 END IF;
128
129 l_item_cat_flag :=
130 CASE
131 WHEN ( l_all_prods AND l_all_prod_cats ) THEN 3 -- all
132 WHEN ( l_all_prods AND NOT l_all_prod_cats ) THEN 1 -- category
133 ELSE 0 -- product
134 END;
135
136 IF l_all_reasons THEN
137 l_reason_flag := 1;
138 ELSE
139 l_reason_flag := 0;
140 l_ret_reason_where := '
141 AND fact.return_reason IN (&ORDER_ITEM_RETURN_REASON+ORDER_ITEM_RETURN_REASON)';
142 END IF;
143 END IF;
144
145 -- The standard is that trend reports are only sortable by time ...
146 l_order_by := 'ORDER BY t.'
147 || CASE
148 WHEN ( instr( l_order_by, 'DESC' ) <> 0 ) THEN 'start_date DESC'
149 ELSE 'start_date ASC'
150 END;
151 /* TODO: Make the util package incorporate the above in an understandable/commented way */
152
153 IF ('SEQUENTIAL' = l_time_comp_type) THEN
154 l_time_comp_where := 'p.end_date (+) = c.start_date - 1';
155 ELSE
156 CASE l_period_type
157 WHEN 'FII_TIME_WEEK' THEN
158 l_period_id_col := 'week_id';
159 l_period_diff := 10000;
160 WHEN 'FII_TIME_ENT_PERIOD' THEN
161 l_period_id_col := 'ent_period_id';
162 l_period_diff := 1000;
163 WHEN 'FII_TIME_ENT_QTR' THEN
164 l_period_id_col := 'ent_qtr_id';
165 l_period_diff := 10;
166 WHEN 'FII_TIME_ENT_YEAR' THEN
167 l_period_id_col := 'ent_year_id';
168 l_period_diff := 1;
169 END CASE;
170 l_time_comp_where := 'p.'||l_period_id_col||' (+) = c.'||l_period_id_col||' - '||l_period_diff;
171 END IF;
172
173 l_stmt := '
174 SELECT t.name VIEWBY
175 , s.cur_return_amt ENI_MEASURE1 -- curr return value
176 , s.pre_return_amt ENI_MEASURE2 -- prev return value
177 , (s.cur_return_amt - s.pre_return_amt)
178 / decode( s.pre_return_amt,0, NULL,
179 abs(s.pre_return_amt)) * 100 ENI_MEASURE3 -- return value change
180 FROM ( SELECT dates.start_date START_DATE
181 , sum(decode(dates.period, ''C'', fact.returned_amt_'||l_curr_suffix||', 0)) CUR_RETURN_AMT
182 , sum(decode(dates.period, ''P'', fact.returned_amt_'||l_curr_suffix||', 0)) PRE_RETURN_AMT
183 FROM ( SELECT c.start_date START_DATE
184 , ''C'' PERIOD
185 , least(c.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
186 FROM '||l_period_type||' c
187 WHERE c.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
188 AND &BIS_CURRENT_ASOF_DATE
189 UNION ALL
190 SELECT c.start_date START_DATE
191 , ''P'' PERIOD
192 , least(p.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE
193 FROM '||l_period_type||' p, '||l_period_type||' c
194 WHERE ( p.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
195 AND &BIS_PREVIOUS_ASOF_DATE )
196 AND ( c.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
197 AND &BIS_CURRENT_ASOF_DATE )
198 AND '||l_time_comp_where||'
199 ) dates
200 , ISC_DBI_CFM_'||l_mv||'_MV fact
201 , FII_TIME_RPT_STRUCT cal'
202 ||l_prod_cat_from||'
203 WHERE cal.report_date = dates.report_date
204 AND bitand( cal.record_type_id, &BIS_NESTED_PATTERN ) = cal.record_type_id
205 AND fact.time_id = cal.time_id
206 AND fact.period_type_id = cal.period_type_id'
207 ||l_flags_where||l_org_where||l_prod_cat_where||l_prod_where||l_cust_where||l_ret_reason_where||'
208 GROUP BY dates.start_date ) s
209 , '||l_period_type||' t
210 WHERE t.start_date = s.start_date(+)
211 AND t.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
212 AND &BIS_CURRENT_ASOF_DATE
213 ' || l_order_by;
214
215 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
216 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
217 x_custom_sql := l_stmt;
218
219 l_custom_rec.attribute_name := ':ENI_ITEM_CAT_FLAG';
220 l_custom_rec.attribute_value := to_char( l_item_cat_flag );
221 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
222 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
223 x_custom_output.EXTEND;
224 x_custom_output(1) := l_custom_rec;
225
226 l_custom_rec.attribute_name := ':ENI_CUST_FLAG';
227 l_custom_rec.attribute_value := to_char( l_cust_flag );
228 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
229 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
230 x_custom_output.EXTEND;
231 x_custom_output(2) := l_custom_rec;
232
233 l_custom_rec.attribute_name := ':ENI_REASON_FLAG';
234 l_custom_rec.attribute_value := to_char( l_reason_flag );
235 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
236 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
237 x_custom_output.EXTEND;
238 x_custom_output(3) := l_custom_rec;
239
240 END get_sql;
241
242 END ENI_DBI_RVT_PKG ;
243