[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_RETURN_REASON_PKG
Source
1 PACKAGE BODY ISC_DBI_RETURN_REASON_PKG AS
2 /* $Header: ISCRGADB.pls 120.1 2006/06/26 07:00:24 abhdixi noship $ */
3
4
5 PROCEDURE GET_SQL( p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
6 x_custom_sql OUT NOCOPY VARCHAR2,
7 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
8
9 l_stmt VARCHAR2(32000);
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_currency VARCHAR2(20);
22 l_item_cat_flag NUMBER; -- 0 for product and 1 for product category
23 l_cust_flag NUMBER; -- 0 for customer and 1 for no customer selected
24 l_return_amt VARCHAR2(20);
25 l_g_currency VARCHAR2(15) := '''FII_GLOBAL1''';
26 l_g1_currency VARCHAR2(15) := '''FII_GLOBAL2''';
27 l_custom_rec BIS_QUERY_ATTRIBUTES ;
28
29 BEGIN
30 FOR i IN 1..p_param.COUNT
31 LOOP
32
33 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
34 THEN l_org := p_param(i).parameter_value;
35 END IF;
36
37 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT') THEN
38 l_prod_cat := p_param(i).parameter_value;
39 END IF;
40
41 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG') THEN
42 l_prod := p_param(i).parameter_value;
43 END IF;
44
45 IF(p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS') THEN
46 l_cust := p_param(i).parameter_value;
47 END IF;
48
49 IF(p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
50 THEN l_currency := p_param(i).parameter_id;
51 END IF;
52
53 IF(p_param(i).parameter_name = 'ORDER_ITEM_RETURN_REASON+ORDER_ITEM_RETURN_REASON')
54 THEN l_ret_reason := p_param(i).parameter_id;
55 END IF;
56
57 END LOOP;
58
59 IF(l_currency = l_g_currency)
60 THEN l_return_amt := 'returned_amt_g';
61 ELSIF (l_currency = l_g1_currency)
62 THEN l_return_amt := 'returned_amt_g1';
63 ELSE l_return_amt := 'returned_amt_f';
64 END IF;
65
66 IF (l_prod IS NULL OR l_prod = '' OR l_prod = 'All')
67 THEN
68 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
69 THEN l_item_cat_flag := 3; -- all
70 ELSE l_item_cat_flag := 1; -- category
71 END IF;
72 ELSE
73 l_item_cat_flag := 0; -- product
74 END IF;
75
76 IF (l_org IS NULL OR l_org = '' OR l_org = 'All') THEN
77 l_org_where := '
78 AND (EXISTS
79 (SELECT 1
80 FROM org_access o
81 WHERE o.responsibility_id = fnd_global.resp_id
82 AND o.resp_application_id = fnd_global.resp_appl_id
83 AND o.organization_id = mv.inv_org_id)
84 OR EXISTS
85 (SELECT 1
86 FROM mtl_parameters org
87 WHERE org.organization_id = mv.inv_org_id
88 AND NOT EXISTS
89 (SELECT 1
90 FROM org_access ora
91 WHERE org.organization_id = ora.organization_id)))';
92 ELSE
93 l_org_where := '
94 AND mv.inv_org_id = &ORGANIZATION+ORGANIZATION';
95 END IF;
96
97 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
98 THEN
99 l_prod_cat_from := '';
100 l_prod_cat_where := '';
101 ELSE
102 l_prod_cat_from := ',
103 ENI_DENORM_HIERARCHIES eni_cat,
104 MTL_DEFAULT_CATEGORY_SETS mdcs';
105 l_prod_cat_where := '
106 AND mv.item_category_id = eni_cat.child_id
107 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
108 AND eni_cat.dbi_flag = ''Y''
109 AND eni_cat.object_type = ''CATEGORY_SET''
110 AND eni_cat.object_id = mdcs.category_set_id
111 AND mdcs.functional_area_id = 11';
112 END IF;
113
114 IF ( l_prod IS NULL OR l_prod = '' OR l_prod = 'All' )
115 THEN l_prod_where := '';
116 ELSE l_prod_where := '
117 AND mv.item_id in (&ITEM+ENI_ITEM_ORG)';
118 END IF;
119
120 IF (l_cust IS NULL OR l_cust = '' OR l_cust = 'All')
121 THEN
122 l_cust_where:='';
123 l_cust_flag := 1; -- all customers and not viewed by customer
124 ELSE
125 l_cust_where :='
126 AND mv.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
127 l_cust_flag := 0; -- customer selected
128 END IF;
129
130 IF ( l_ret_reason IS NULL OR l_ret_reason = '' OR l_ret_reason = 'All' )
131 THEN l_ret_reason_where := '';
132 ELSE l_ret_reason_where := '
133 AND mv.return_reason IN (&ORDER_ITEM_RETURN_REASON+ORDER_ITEM_RETURN_REASON)';
134 END IF;
135
136 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
137 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
138
139 l_stmt := '
140 SELECT ret.value VIEWBY, -- return reason
141 ret.id VIEWBYID,
142 c.prev_return ISC_MEASURE_7, -- return value (prior)
143 c.curr_return ISC_MEASURE_1, -- return value
144 (c.curr_return - c.prev_return)
145 / decode(c.prev_return, 0, NULL,
146 abs(c.prev_return)) * 100 ISC_MEASURE_2, -- change (return value),
147 c.curr_return
148 / decode(sum(c.curr_return) over(), 0, NULL,
149 sum(c.curr_return) over())
150 * 100 ISC_MEASURE_3, -- Percent of Total
151 c.lines_cnt ISC_MEASURE_4, -- lines affected
152 sum(c.curr_return) over() ISC_MEASURE_5, -- grand total for return value
153 (sum(c.curr_return) over() - sum(c.prev_return) over())
154 / decode(sum(c.prev_return) over(), 0, NULL,
155 abs(sum(c.prev_return) over()))
156 * 100 ISC_MEASURE_6, -- grand total for return value change
157 sum(c.lines_cnt) over() ISC_MEASURE_8 -- grand total for lines affected
158 FROM (SELECT mv.return_reason REASON,
159 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
160 mv.'||l_return_amt||', 0)) CURR_RETURN,
161 sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
162 mv.'||l_return_amt||', 0)) PREV_RETURN,
163 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
164 mv.lines_cnt, 0)) LINES_CNT
165 FROM ISC_DBI_CFM_007_MV mv,
166 FII_TIME_RPT_STRUCT_V cal'
167 ||l_prod_cat_from||'
168 WHERE mv.time_id = cal.time_id
169 AND mv.period_type_id = cal.period_type_id
170 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) = cal.record_type_id
171 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
172 AND mv.customer_flag = :ISC_CUST_FLAG
173 AND mv.item_cat_flag = :ISC_ITEM_CAT_FLAG
174 AND mv.return_reason_flag = 0'
175 ||l_org_where||l_prod_cat_where||l_prod_where||l_cust_where||l_ret_reason_where||'
176 GROUP BY mv.return_reason) c,
177 BIS_ORDER_ITEM_RET_REASON_V ret
178 WHERE c.reason = ret.id
179 &ORDER_BY_CLAUSE NULLS LAST';
180
181 x_custom_sql := l_stmt;
182
183 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
184 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
185 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
186 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
187 x_custom_output.EXTEND;
188 x_custom_output(1) := l_custom_rec;
189
190 l_custom_rec.attribute_name := ':ISC_CUST_FLAG';
191 l_custom_rec.attribute_value := to_char(l_cust_flag);
192 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
193 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
194 x_custom_output.EXTEND;
195 x_custom_output(2) := l_custom_rec;
196
197 END get_sql;
198
199 END ISC_DBI_RETURN_REASON_PKG;
200