[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_RETURN_VALUE_TREND_PKG
Source
1 PACKAGE BODY ISC_DBI_RETURN_VALUE_TREND_PKG AS
2 /* $Header: ISCRGACB.pls 120.1 2006/06/26 06:59:04 abhdixi noship $ */
3
4
5 PROCEDURE GET_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,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_return_amt VARCHAR2(20);
23 l_currency VARCHAR2(480);
24 l_temp VARCHAR2(480);
25 l_sql_stmt VARCHAR2(32000);
26 l_g_currency VARCHAR2(48);
27 l_g1_currency VARCHAR2(48);
28 l_item_cat_flag NUMBER; -- 0 for product, 1 for product category, 3 for no grouping on item dimension
29 l_cust_flag NUMBER; -- 0 for customer and 1 for no customer selected
30 l_reason_flag NUMBER; -- 0 for reason and 1 for all reasons
31 l_mv VARCHAR2(10);
32 l_custom_rec BIS_QUERY_ATTRIBUTES;
33
34 BEGIN
35
36 l_g_currency := '''FII_GLOBAL1''';
37 l_g1_currency := '''FII_GLOBAL2''';
38
39 FOR i IN 1..p_param.COUNT
40 LOOP
41
42 IF(p_param(i).parameter_name = 'PERIOD_TYPE')
43 THEN l_period_type := p_param(i).parameter_value;
44 END IF;
45
46 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
47 THEN l_org := p_param(i).parameter_value;
48 END IF;
49
50 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT') THEN
51 l_prod_cat := p_param(i).parameter_value;
52 END IF;
53
54 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG') THEN
55 l_prod := p_param(i).parameter_value;
56 END IF;
57
58 IF(p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
59 THEN l_currency := p_param(i).parameter_id;
60 END IF;
61
62 IF(p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS')
63 THEN l_cust := p_param(i).parameter_value;
64 END IF;
65
66 IF(p_param(i).parameter_name = 'ORDER_ITEM_RETURN_REASON+ORDER_ITEM_RETURN_REASON')
67 THEN l_ret_reason := p_param(i).parameter_id;
68 END IF;
69
70 END LOOP;
71
72
73 IF(l_currency = l_g_currency)
74 THEN l_return_amt := 'returned_amt_g';
75 ELSIF (l_currency = l_g1_currency)
76 THEN l_return_amt := 'returned_amt_g1';
77 ELSE l_return_amt := 'returned_amt_f';
78 END IF;
79
80 IF ( l_org IS NULL OR l_org = '' OR l_org = 'All')
81 THEN
82 l_org_where := '
83 AND (EXISTS
84 (SELECT 1
85 FROM org_access o
86 WHERE o.responsibility_id = fnd_global.resp_id
87 AND o.resp_application_id = fnd_global.resp_appl_id
88 AND o.organization_id = fact.inv_org_id)
89 OR EXISTS
90 (SELECT 1
91 FROM mtl_parameters org
92 WHERE org.organization_id = fact.inv_org_id
93 AND NOT EXISTS
94 (SELECT 1
95 FROM org_access ora
96 WHERE org.organization_id = ora.organization_id)))';
97 ELSE
98 l_org_where := '
99 AND fact.inv_org_id = &ORGANIZATION+ORGANIZATION';
100 END IF;
101
102 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
103 THEN
104 l_prod_cat_from := '';
105 l_prod_cat_where := '';
106 ELSE
107 l_prod_cat_from := ',
108 ENI_DENORM_HIERARCHIES eni_cat,
109 MTL_DEFAULT_CATEGORY_SETS mdcs';
110 l_prod_cat_where := '
111 AND fact.item_category_id = eni_cat.child_id
112 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
113 AND eni_cat.dbi_flag = ''Y''
114 AND eni_cat.object_type = ''CATEGORY_SET''
115 AND eni_cat.object_id = mdcs.category_set_id
116 AND mdcs.functional_area_id = 11';
117 END IF;
118
119 IF ( l_prod IS NULL OR l_prod = '' OR l_prod = 'All' )
120 THEN l_prod_where := '';
121 ELSE l_prod_where := '
122 AND fact.item_id in (&ITEM+ENI_ITEM_ORG)';
123 END IF;
124
125
126 IF (l_cust IS NULL OR l_cust = '' OR l_cust = 'All')
127 THEN
128 l_cust_where:='';
129 l_cust_flag := 1; -- all customers
130 ELSE
131 l_cust_where :='
132 AND fact.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
133 l_cust_flag := 0; -- customer selected
134 END IF;
135
136 IF (l_prod IS NULL OR l_prod = '' OR l_prod = 'All')
137 THEN
138 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
139 THEN l_item_cat_flag := 3; -- all
140 ELSE l_item_cat_flag := 1; -- category
141 END IF;
142 ELSE
143 l_item_cat_flag := 0; -- product
144 END IF;
145
146 IF ( l_ret_reason IS NULL OR l_ret_reason = '' OR l_ret_reason = 'All' )
147 THEN
148 l_ret_reason_where := '
149 AND fact.return_flag = 1';
150 l_mv := '002';
151 l_reason_flag := 1;
152 ELSE
153 l_ret_reason_where := '
154 AND fact.return_reason IN (&ORDER_ITEM_RETURN_REASON+ORDER_ITEM_RETURN_REASON)
155 AND fact.return_reason_flag = :ISC_REASON_FLAG';
156 l_mv := '007';
157 l_reason_flag := 0;
158 END IF;
159
160 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
161 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
162
163 IF ((l_prod IS NULL OR l_prod = '' OR l_prod = 'All') AND
164 (l_cust IS NULL OR l_cust = '' OR l_cust = 'All') AND
165 (l_ret_reason IS NULL OR l_ret_reason = '' OR l_ret_reason = 'All'))
166 THEN
167 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
168 THEN
169 l_prod_cat_from := '';
170 l_prod_cat_where := '
171 AND fact.top_node_flag = ''Y''';
172 ELSE
173 l_prod_cat_from := '';
174 l_prod_cat_where := '
175 AND fact.parent_id = &ITEM+ENI_ITEM_VBH_CAT';
176 END IF;
177 l_mv := '011';
178 l_flags_where := '
179 AND fact.inv_org_flag = 0';
180 ELSE
181 l_flags_where := '
182 AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
183 AND fact.customer_flag = :ISC_CUST_FLAG';
184 END IF;
185
186 l_stmt := '
187 SELECT fii.name VIEWBY,
188 s.pre_return_amt ISC_MEASURE_2, -- prev return value
189 s.cur_return_amt ISC_MEASURE_1, -- curr return value
190 (s.cur_return_amt - s.pre_return_amt)
191 / decode( s.pre_return_amt,0,
192 NULL,
193 abs(s.pre_return_amt)) * 100 ISC_MEASURE_3 -- return value change
194 FROM (SELECT dates.start_date START_DATE,
195 sum(decode(dates.period, ''C'', fact.'||l_return_amt||', 0)) CUR_RETURN_AMT,
196 sum(decode(dates.period, ''P'', fact.'||l_return_amt||', 0)) PRE_RETURN_AMT
197 FROM (SELECT fii.start_date START_DATE,
198 ''C'' PERIOD,
199 least(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
200 FROM '||l_period_type||' fii
201 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
202 AND &BIS_CURRENT_ASOF_DATE
203 UNION ALL
204 SELECT p2.start_date START_DATE,
205 ''P'' PERIOD,
206 p1.report_date REPORT_DATE
207 FROM (SELECT least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE,
208 rownum ID
209 FROM '||l_period_type||' fii
210 WHERE fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
211 AND &BIS_PREVIOUS_ASOF_DATE
212 ORDER BY fii.start_date DESC) p1,
213 (SELECT fii.start_date START_DATE,
214 rownum ID
215 FROM '||l_period_type||' fii
216 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
217 AND &BIS_CURRENT_ASOF_DATE
218 ORDER BY fii.start_date DESC) p2
219 WHERE p1.id(+) = p2.id) dates,
220 ISC_DBI_CFM_'||l_mv||'_MV fact,
221 FII_TIME_RPT_STRUCT_V cal'
222 ||l_prod_cat_from||'
223 WHERE cal.report_date = dates.report_date
224 AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id
225 AND fact.time_id = cal.time_id
226 AND fact.period_type_id = cal.period_type_id'
227 ||l_flags_where||l_org_where||l_prod_cat_where||l_prod_where||l_cust_where||l_ret_reason_where||'
228 GROUP BY dates.start_date) s,
229 '||l_period_type||' fii
230 WHERE fii.start_date = s.start_date(+)
231 AND fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
232 AND &BIS_CURRENT_ASOF_DATE
233 ORDER BY fii.start_date ';
234
235 x_custom_sql := l_stmt;
236
237 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
238 l_custom_rec.attribute_value := 'TIME+'||l_period_type;
239 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
240 x_custom_output.EXTEND;
241 x_custom_output(1) := l_custom_rec;
242
243 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
244 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
245 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
246 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
247 x_custom_output.EXTEND;
248 x_custom_output(2) := l_custom_rec;
249
250 l_custom_rec.attribute_name := ':ISC_CUST_FLAG';
251 l_custom_rec.attribute_value := to_char(l_cust_flag);
252 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
253 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
254 x_custom_output.EXTEND;
255 x_custom_output(3) := l_custom_rec;
256
257 l_custom_rec.attribute_name := ':ISC_REASON_FLAG';
258 l_custom_rec.attribute_value := to_char(l_reason_flag);
259 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
260 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
261 x_custom_output.EXTEND;
262 x_custom_output(4) := l_custom_rec;
263
264 END get_sql;
265
266 END ISC_DBI_RETURN_VALUE_TREND_PKG ;
267