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