[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_RETURN_DETAIL_PKG
Source
1 PACKAGE BODY ISC_DBI_RETURN_DETAIL_PKG AS
2 /* $Header: ISCRGAEB.pls 120.1 2005/10/17 12:33:42 hprathur 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_measures VARCHAR2(32000);
11 l_org VARCHAR2(32000);
12 l_org_where VARCHAR2(32000);
13 l_prod VARCHAR2(32000);
14 l_prod_where VARCHAR2(32000);
15 l_prod_cat VARCHAR2(32000);
16 l_prod_cat_from VARCHAR2(32000);
17 l_prod_cat_where VARCHAR2(32000);
18 l_ret_reason VARCHAR2(32000);
19 l_ret_reason_where VARCHAR2(32000);
20 l_cust VARCHAR2(32000);
21 l_cust_where VARCHAR2(32000);
22 l_lang VARCHAR2(10);
23 l_g_currency VARCHAR2(15) := '''FII_GLOBAL1''';
24 l_g1_currency VARCHAR2(15) := '''FII_GLOBAL2''';
25 l_currency VARCHAR2(20);
26 l_return_amt VARCHAR2(20);
27 l_custom_rec BIS_QUERY_ATTRIBUTES;
28
29 BEGIN
30
31 l_lang := userenv('LANG');
32
33 FOR i IN 1..p_param.COUNT
34 LOOP
35
36 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
37 THEN l_org := p_param(i).parameter_value;
38 END IF;
39
40 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT')
41 THEN l_prod_cat := p_param(i).parameter_value;
42 END IF;
43
44 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG')
45 THEN l_prod := p_param(i).parameter_value;
46 END IF;
47
48 IF(p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS')
49 THEN l_cust := p_param(i).parameter_value;
50 END IF;
51
52 IF(p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
53 THEN l_currency := p_param(i).parameter_id;
54 END IF;
55
56 IF(p_param(i).parameter_name = 'ORDER_ITEM_RETURN_REASON+ORDER_ITEM_RETURN_REASON')
57 THEN l_ret_reason := p_param(i).parameter_id;
58 END IF;
59
60 END LOOP;
61
62 IF(l_currency = l_g_currency)
63 THEN l_return_amt := 'returned_amt_g';
64 ELSIF (l_currency = l_g1_currency)
65 THEN l_return_amt := 'returned_amt_g1';
66 ELSE l_return_amt := 'returned_amt_f';
67 END IF;
68
69
70 IF (l_org IS NULL OR l_org = '' OR l_org = 'All')
71 THEN l_org_where := '
72 AND (EXISTS
73 (SELECT 1
74 FROM org_access o
75 WHERE o.responsibility_id = fnd_global.resp_id
76 AND o.resp_application_id = fnd_global.resp_appl_id
77 AND o.organization_id = mv.inv_org_id)
78 OR EXISTS
79 (SELECT 1
80 FROM mtl_parameters org
81 WHERE org.organization_id = mv.inv_org_id
82 AND NOT EXISTS
83 (SELECT 1
84 FROM org_access ora
85 WHERE org.organization_id = ora.organization_id)))';
86 ELSE l_org_where := '
87 AND mv.inv_org_id = &ORGANIZATION+ORGANIZATION';
88 END IF;
89
90 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
91 THEN
92 l_prod_cat_from := '';
93 l_prod_cat_where := '';
94 ELSE
95 l_prod_cat_from := ',
96 ENI_DENORM_HIERARCHIES eni_cat,
97 MTL_DEFAULT_CATEGORY_SETS mdcs';
98 l_prod_cat_where := '
99 AND mv.item_category_id = eni_cat.child_id
100 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
101 AND eni_cat.dbi_flag = ''Y''
102 AND eni_cat.object_type = ''CATEGORY_SET''
103 AND eni_cat.object_id = mdcs.category_set_id
104 AND mdcs.functional_area_id = 11';
105 END IF;
106
107 IF ( l_prod IS NULL OR l_prod = '' OR l_prod = 'All' )
108 THEN l_prod_where := '';
109 ELSE l_prod_where := '
110 AND mv.item_id IN (&ITEM+ENI_ITEM_ORG)';
111 END IF;
112
113 IF (l_cust IS NULL OR l_cust = '' OR l_cust = 'All')
114 THEN l_cust_where := '';
115 ELSE l_cust_where := '
116 AND mv.customer_id IN (&CUSTOMER+FII_CUSTOMERS)';
117 END IF;
118
119 IF ( l_ret_reason IS NULL OR l_ret_reason = '' OR l_ret_reason = 'All' )
120 THEN l_ret_reason_where := '';
121 ELSE l_ret_reason_where := '
122 AND mv.return_reason IN (&ORDER_ITEM_RETURN_REASON+ORDER_ITEM_RETURN_REASON)';
123 END IF;
124
125 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
126 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
127
128 l_stmt := '
129 SELECT ISC_ATTRIBUTE_7,ISC_ATTRIBUTE_3, ISC_ATTRIBUTE_4,
130 org.name ISC_ATTRIBUTE_8,
131 cust.value ISC_ATTRIBUTE_5,
132 ISC_ATTRIBUTE_6, ISC_MEASURE_1, ISC_MEASURE_2
133 FROM
134 (SELECT (rank() over (&ORDER_BY_CLAUSE, isc_attribute_7, isc_attribute_4)) - 1 rnk,
135 customer_id,
136 inv_org_id,
137 ISC_ATTRIBUTE_3, ISC_ATTRIBUTE_4, ISC_ATTRIBUTE_6, ISC_ATTRIBUTE_7,
138 ISC_MEASURE_1, ISC_MEASURE_2
139 FROM
140 (SELECT mv.customer_id CUSTOMER_ID,
141 mv.inv_org_id INV_ORG_ID,
142 mv.order_number ISC_ATTRIBUTE_3,
143 mv.line_number ISC_ATTRIBUTE_4,
144 mv.time_fulfilled_date_id ISC_ATTRIBUTE_6,
145 mv.header_id ISC_ATTRIBUTE_7,
146 mv.'||l_return_amt||' ISC_MEASURE_1,
147 sum(mv.'||l_return_amt||') over() ISC_MEASURE_2
148 FROM ISC_DBI_CFM_003_MV mv'
149 ||l_prod_cat_from||'
150 WHERE mv.time_fulfilled_date_id BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
151 AND &BIS_CURRENT_ASOF_DATE'
152 ||l_org_where
153 ||l_prod_cat_where
154 ||l_prod_where
155 ||l_cust_where
156 ||l_ret_reason_where||')) a,
157 FII_CUSTOMERS_V cust,
158 HR_ALL_ORGANIZATION_UNITS_TL org
159 WHERE a.customer_id = cust.id
160 AND a.inv_org_id = org.organization_id
161 AND org.language = :ISC_LANG
162 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
163 ORDER BY rnk';
164
165 x_custom_sql := l_stmt;
166
167 l_custom_rec.attribute_name := ':ISC_LANG';
168 l_custom_rec.attribute_value := l_lang;
169 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
170 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
171 x_custom_output.EXTEND;
172 x_custom_output(1) := l_custom_rec;
173
174 END get_sql;
175
176 END ISC_DBI_RETURN_DETAIL_PKG ;
177