[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_SHIP_LATE_PKG
Source
1 PACKAGE BODY ISC_DBI_SHIP_LATE_PKG AS
2 /* $Header: ISCRG67B.pls 120.1 2006/06/26 06:20:08 abhdixi noship $ */
3
4 PROCEDURE GET_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,x_custom_sql OUT NOCOPY VARCHAR2,
5 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
6
7 l_formula_sql VARCHAR2(32000);
8 l_inner_sql VARCHAR2(32000);
9 l_outer_sql VARCHAR2(32000);
10 l_stmt VARCHAR2(32000);
11 l_view_by VARCHAR2(32000);
12 l_org VARCHAR2(32000);
13 l_org_where VARCHAR2(32000);
14 l_item VARCHAR2(32000);
15 l_item_where VARCHAR2(32000);
16 l_inv_cat VARCHAR2(32000);
17 l_inv_cat_where VARCHAR2(32000);
18 l_cust VARCHAR2(32000);
19 l_cust_where VARCHAR2(32000);
20 l_lang VARCHAR2(10);
21 l_item_cat_flag NUMBER; -- 0 for item and 1 for inv. category
22 l_cust_flag NUMBER; -- 0 for customer level, 1 for no-customer level
23 l_agg_level NUMBER;
24 l_custom_rec BIS_QUERY_ATTRIBUTES ;
25 l_const NUMBER:=0; -- used for the zero row filter
26
27 BEGIN
28 FOR i IN 1..p_param.COUNT
29 LOOP
30
31 IF( p_param(i).parameter_name= 'VIEW_BY')
32 THEN l_view_by := p_param(i).parameter_value;
33 END IF;
34
35 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
36 THEN l_org := p_param(i).parameter_value;
37 END IF;
38
39 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_INV_CAT') THEN
40 l_inv_cat := p_param(i).parameter_value;
41 END IF;
42
43 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG') THEN
44 l_item := p_param(i).parameter_value;
45 END IF;
46
47 IF(p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS') THEN
48 l_cust := p_param(i).parameter_value;
49 END IF;
50
51
52 END LOOP;
53
54
55 IF ( l_org IS NULL OR l_org = 'All' ) THEN
56 l_org_where := '
57 AND (EXISTS
58 (SELECT 1
59 FROM org_access o
60 WHERE o.responsibility_id = fnd_global.resp_id
61 AND o.resp_application_id = fnd_global.resp_appl_id
62 AND o.organization_id = f.inv_org_id)
63 OR EXISTS
64 (SELECT 1
65 FROM mtl_parameters org
66 WHERE org.organization_id = f.inv_org_id
67 AND NOT EXISTS
68 (SELECT 1
69 FROM org_access ora
70 WHERE org.organization_id = ora.organization_id)))';
71
72 ELSE
73 l_org_where := '
74 AND f.inv_org_id = &ORGANIZATION+ORGANIZATION';
75 END IF;
76
77 IF ( l_inv_cat IS NULL OR l_inv_cat = 'All' ) THEN
78 l_inv_cat_where :='';
79 ELSE
80 l_inv_cat_where := '
81 AND f.item_category_id in (&ITEM+ENI_ITEM_INV_CAT)';
82 END IF;
83
84 IF ( l_item IS NULL OR l_item = 'All' ) THEN
85 l_item_where :='';
86 ELSE
87 l_item_where := '
88 AND f.item_id in (&ITEM+ENI_ITEM_ORG)';
89 END IF;
90
91
92 IF (l_cust IS NULL OR l_cust = 'All') THEN
93 l_cust_where:='';
94
95 IF(l_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
96 l_cust_flag := 0;
97 ELSE
98 l_cust_flag := 1; -- do not need customer id
99 END IF;
100 ELSE
101 l_cust_where :='
102 AND f.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
103 l_cust_flag := 0; -- customer level
104 END IF;
105
106 IF (l_item IS NULL OR l_item = 'All')
107 THEN
108 IF (l_view_by = 'ITEM+ENI_ITEM_ORG')
109 THEN l_item_cat_flag := 0; -- item
110 ELSIF (l_view_by = 'ITEM+ENI_ITEM_INV_CAT')
111 THEN l_item_cat_flag := 1; -- category
112 ELSE
113 IF (l_inv_cat IS NULL OR l_inv_cat = 'All')
114 THEN l_item_cat_flag := 3; -- all
115 ELSE l_item_cat_flag := 1; -- category
116 END IF;
117 END IF;
118 ELSE
119 l_item_cat_flag := 0; -- item
120 END IF;
121
122 CASE
123 WHEN (l_item_cat_flag = 0 and l_cust_flag = 0) THEN l_agg_level := 0;
124 WHEN (l_item_cat_flag = 1 and l_cust_flag = 0) THEN l_agg_level := 4;
125 WHEN (l_item_cat_flag = 3 and l_cust_flag = 0) THEN l_agg_level := 2;
126 WHEN (l_item_cat_flag = 0 and l_cust_flag = 1) THEN l_agg_level := 1;
127 WHEN (l_item_cat_flag = 1 and l_cust_flag = 1) THEN l_agg_level := 5;
128 WHEN (l_item_cat_flag = 3 and l_cust_flag = 1) THEN l_agg_level := 3;
129 END CASE;
130
131 l_lang := USERENV('LANG');
132
133 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
134 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
135
136 l_formula_sql :='
137 a.late_line_cnt ISC_MEASURE_1,
138 a.late_line_cnt/decode(a.shipped_line_cnt,
139 0,null,a.shipped_line_cnt)*100 ISC_MEASURE_2,
140 null ISC_MEASURE_3, -- obsoleted 5.0 item
141 a.days_late/ decode(a.late_line_cnt,
142 0,null,a.late_line_cnt) ISC_MEASURE_4,
143 a.book_to_ship_days/ decode(a.late_line_cnt,
144 0,null,a.late_line_cnt) ISC_MEASURE_5,
145 sum(a.late_line_cnt) over() ISC_MEASURE_6,
146 sum(a.late_line_cnt) over()/
147 decode(sum(a.shipped_line_cnt) over(),
148 0,null,sum(a.shipped_line_cnt) over())*100 ISC_MEASURE_7,
149 sum(a.days_late) over()/
150 decode(sum(a.late_line_cnt) over(),
151 0,null,sum(a.late_line_cnt) over()) ISC_MEASURE_8,
152 sum(a.book_to_ship_days) over()/
153 decode(sum(a.late_line_cnt) over(),
154 0,null,sum(a.late_line_cnt) over()) ISC_MEASURE_9,
155 a.late_qty ISC_MEASURE_10,
156 null CURRENCY -- obsoleted item from DBI 5.0';
157
158 l_outer_sql:= 'ISC_MEASURE_10,ISC_MEASURE_1,ISC_MEASURE_2,ISC_MEASURE_3,ISC_MEASURE_4,ISC_MEASURE_5,
159 ISC_MEASURE_6,ISC_MEASURE_7,ISC_MEASURE_8,ISC_MEASURE_9,
160 CURRENCY';
161
162
163 l_inner_sql:='sum(shipped_line_cnt) SHIPPED_LINE_CNT,
164 sum(late_line_cnt) LATE_LINE_CNT,
165 sum(days_late) DAYS_LATE,
166 sum(book_to_ship_days_late) BOOK_TO_SHIP_DAYS,
167 sum(shipped_late_qty) LATE_QTY
168 FROM ISC_DBI_FM_0000_MV f,
169 FII_TIME_RPT_STRUCT_V cal
170 WHERE f.time_id = cal.time_id
171 AND f.agg_level = :ISC_AGG_LEVEL
172 AND cal.report_date = &BIS_CURRENT_ASOF_DATE
173 AND cal.period_type_id = f.period_type_id
174 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) = cal.record_type_id'
175 ||l_org_where||l_inv_cat_where||l_item_where||l_cust_where;
176
177
178 IF l_view_by = 'ITEM+ENI_ITEM_INV_CAT' THEN
179 l_stmt := ' SELECT eni.value VIEWBY,
180 eni.id VIEWBYID,
181 eni.id ISC_ATTRIBUTE_2,
182 null ISC_ATTRIBUTE_3,
183 null ISC_ATTRIBUTE_4,
184 '||l_outer_sql||'
185 FROM(select (rank() over (&ORDER_BY_CLAUSE nulls last,item_category_id))-1 rnk,
186 item_category_id,
187 '||l_outer_sql||'
188 FROM (select item_category_id,
189 '||l_formula_sql||'
190 FROM (SELECT f.item_category_id ITEM_CATEGORY_ID,
191 '||l_inner_sql||'
192 group by f.item_category_id) a)
193 WHERE ISC_MEASURE_1<>:ISC_CONST) c,
194 ENI_ITEM_INV_CAT_V eni
195 WHERE c.item_category_id = eni.id
196 AND ((c.rnk between &START_INDEX and &END_INDEX) OR(&END_INDEX = -1))
197 ORDER BY rnk';
198
199 ELSIF l_view_by = 'ORGANIZATION+ORGANIZATION' THEN
200 l_stmt := 'SELECT org.name VIEWBY,
201 org.organization_id VIEWBYID,
202 org.organization_id ISC_ATTRIBUTE_2,
203 null ISC_ATTRIBUTE_3,
204 null ISC_ATTRIBUTE_4,
205 '||l_outer_sql||'
206 FROM(select
207 (rank() over (&ORDER_BY_CLAUSE nulls last,inv_org_id))-1 rnk,
208 inv_org_id,
209 '||l_outer_sql||'
210 FROM (select inv_org_id,
211 '||l_formula_sql||'
212 FROM (SELECT f.inv_org_id INV_ORG_ID,
213 '||l_inner_sql||'
214 group by f.inv_org_id) a)
215 WHERE ISC_MEASURE_1<>:ISC_CONST) c,
216 HR_ALL_ORGANIZATION_UNITS_TL org
217 WHERE org.organization_id = c.inv_org_id
218 AND org.language = :ISC_LANG
219 AND ((c.rnk between &START_INDEX and &END_INDEX) OR(&END_INDEX = -1))
220 ORDER BY rnk' ;
221
222 ELSIF l_view_by = 'ITEM+ENI_ITEM_ORG' THEN
223 l_stmt := 'SELECT items.value VIEWBY,
224 items.id VIEWBYID,
225 items.id ISC_ATTRIBUTE_2,
226 items.description ISC_ATTRIBUTE_3,
227 uom.unit_of_measure ISC_ATTRIBUTE_4,
228 '||l_outer_sql||'
229 FROM(select (rank() over (&ORDER_BY_CLAUSE nulls last,item_id))-1 rnk,
230 item_id,uom,
231 '||l_outer_sql||'
232 FROM (select item_id, uom,
233 '||l_formula_sql||'
234 FROM (SELECT f.item_id ITEM_ID,f.uom UOM,
235 '||l_inner_sql||'
236 group by f.item_id,f.uom) a)
237 WHERE ISC_MEASURE_1<>:ISC_CONST) c,
238 ENI_ITEM_ORG_V items,
239 MTL_UNITS_OF_MEASURE_TL uom
240 WHERE c.item_id = items.id
241 AND uom.uom_code = c.uom
242 AND uom.language = :ISC_LANG
243 AND ((c.rnk between &START_INDEX and &END_INDEX) OR(&END_INDEX = -1))
244 ORDER BY rnk';
245
246 ELSE -- l_view_by = 'CUSTOMER+FII_CUSTOMERS'
247 l_stmt := 'SELECT cust.value VIEWBY,
248 cust.id VIEWBYID,
249 cust.id ISC_ATTRIBUTE_2,
250 null ISC_ATTRIBUTE_3,
251 null ISC_ATTRIBUTE_4,
252 '||l_outer_sql||'
253 FROM(select (rank() over (&ORDER_BY_CLAUSE nulls last,customer_id))-1 rnk,
254 customer_id,
255 '||l_outer_sql||'
256 FROM (select customer_id,
257 '||l_formula_sql||'
258 FROM (SELECT f.customer_id CUSTOMER_ID,
259 '||l_inner_sql||'
260 group by f.customer_id) a)
261 WHERE ISC_MEASURE_1<>:ISC_CONST) c,
262 FII_CUSTOMERS_V cust
263 WHERE c.customer_id = cust.id
264 AND ((c.rnk between &START_INDEX and &END_INDEX) OR(&END_INDEX = -1))
265 ORDER BY rnk';
266
267 END IF;
268
269 x_custom_sql := l_stmt;
270
271 l_custom_rec.attribute_name := ':ISC_AGG_LEVEL';
272 l_custom_rec.attribute_value := to_char(l_agg_level);
273 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
274 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
275 x_custom_output.EXTEND;
276 x_custom_output(1) := l_custom_rec;
277
278 l_custom_rec.attribute_name := ':ISC_LANG';
279 l_custom_rec.attribute_value := l_lang;
280 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
281 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
282 x_custom_output.EXTEND;
283 x_custom_output(2) := l_custom_rec;
284
285 l_custom_rec.attribute_name := ':ISC_CONST';
286 l_custom_rec.attribute_value := l_const;
287 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
288 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
289 x_custom_output.EXTEND;
290 x_custom_output(3) := l_custom_rec;
291
292
293 END get_sql;
294
295 END ISC_DBI_SHIP_LATE_PKG ;
296