[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_BACKORDER_SUM_PKG
Source
1 PACKAGE BODY ISC_DBI_BACKORDER_SUM_PKG AS
2 /* $Header: ISCRGAYB.pls 120.2 2006/05/04 03:53:58 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_formula_sql VARCHAR2(10000);
10 l_inner_sql VARCHAR2(10000);
11 l_outer_sql VARCHAR2(10000);
12 l_sql_stmt VARCHAR2(10000);
13 l_stmt VARCHAR2(10000);
14 l_view_by VARCHAR2(10000);
15 l_customer VARCHAR2(10000);
16 l_customer_where VARCHAR2(10000);
17 l_item VARCHAR2(10000);
18 l_item_where VARCHAR2(10000);
19 l_inv_cat VARCHAR2(10000);
20 l_inv_cat_where VARCHAR2(10000);
21 l_inv_org VARCHAR2(10000);
22 l_inv_org_where VARCHAR2(10000);
23
24 l_period_type VARCHAR2(240);
25 l_snapshot_taken BOOLEAN := TRUE;
26 l_as_of_date DATE;
27 l_effective_start_date DATE;
28 l_cursor_id NUMBER;
29 l_dummy NUMBER;
30 l_lang VARCHAR2(10);
31 l_custom_rec BIS_QUERY_ATTRIBUTES;
32
33 BEGIN
34
35 FOR i IN 1..p_param.COUNT
36 LOOP
37 IF( p_param(i).parameter_name= 'VIEW_BY')
38 THEN l_view_by := p_param(i).parameter_value;
39 END IF;
40
41 IF(p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS')
42 THEN l_customer := p_param(i).parameter_value;
43 END IF;
44
45 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
46 THEN l_inv_org := p_param(i).parameter_value;
47 END IF;
48
49 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_INV_CAT')
50 THEN l_inv_cat := p_param(i).parameter_value;
51 END IF;
52
53 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG')
54 THEN l_item := p_param(i).parameter_value;
55 END IF;
56
57 IF(p_param(i).parameter_name = 'AS_OF_DATE')
58 THEN l_as_of_date := to_date(p_param(i).parameter_value, 'DD-MM-YYYY');
59 END IF;
60
61 IF(p_param(i).parameter_name = 'PERIOD_TYPE')
62 THEN l_period_type := p_param(i).parameter_value;
63 END IF;
64
65 END LOOP;
66
67 IF(l_inv_org IS NULL OR l_inv_org = 'All')
68 THEN l_inv_org_where := '
69 AND (EXISTS
70 (SELECT 1
71 FROM org_access o
72 WHERE o.responsibility_id = fnd_global.resp_id
73 AND o.resp_application_id = fnd_global.resp_appl_id
74 AND o.organization_id = mv.inv_org_id)
75 OR EXISTS
76 (SELECT 1
77 FROM mtl_parameters org
78 WHERE org.organization_id = mv.inv_org_id
79 AND NOT EXISTS
80 (SELECT 1
81 FROM org_access ora
82 WHERE org.organization_id = ora.organization_id)))';
83 ELSE l_inv_org_where := '
84 AND mv.inv_org_id = &ORGANIZATION+ORGANIZATION';
85 END IF;
86
87 IF(l_inv_cat IS NULL OR l_inv_cat = 'All')
88 THEN l_inv_cat_where := '';
89 ELSE l_inv_cat_where := '
90 AND mv.item_category_id IN (&ITEM+ENI_ITEM_INV_CAT)';
91 END IF;
92
93 IF(l_item IS NULL OR l_item = 'All')
94 THEN l_item_where := '';
95 ELSE l_item_where := '
96 AND mv.item_id IN (&ITEM+ENI_ITEM_ORG)';
97 END IF;
98
99 IF (l_customer IS NULL OR l_customer = 'All')
100 THEN l_customer_where :='';
101 ELSE l_customer_where :='
102 AND mv.customer_id IN (&CUSTOMER+FII_CUSTOMERS)';
103 END IF;
104
105 BEGIN
106
107 IF l_period_type = 'FII_TIME_ENT_YEAR'
108 THEN l_effective_start_date := FII_TIME_API.Ent_Cyr_Start(l_as_of_date);
109 ELSIF l_period_type = 'FII_TIME_ENT_QTR'
110 THEN l_effective_start_date := FII_TIME_API.Ent_Cqtr_Start(l_as_of_date);
111 ELSIF l_period_type = 'FII_TIME_ENT_PERIOD'
112 THEN l_effective_start_date := FII_TIME_API.Ent_Cper_Start(l_as_of_date);
113 ELSE -- l_period_type = 'FII_TIME_WEEK'
114 l_effective_start_date := FII_TIME_API.Cwk_Start(l_as_of_date);
115 END IF;
116
117 l_cursor_id := DBMS_SQL.Open_Cursor;
118
119 IF(l_period_type = 'FII_TIME_DAY')
120 THEN
121 l_stmt := '
122 SELECT 1
123 FROM ISC_DBI_FM_0007_MV mv
124 WHERE mv.time_snapshot_date_id = :l_as_of_date
125 AND rownum = 1 ';
126
127 DBMS_SQL.Parse(l_cursor_id,l_stmt,DBMS_SQL.V7);
128 DBMS_SQL.Bind_Variable(l_cursor_id,':l_as_of_date',l_as_of_date);
129
130 ELSE
131 l_stmt := '
132 SELECT 1
133 FROM ISC_DBI_FM_0007_MV mv
134 WHERE mv.time_snapshot_date_id BETWEEN :l_effective_start_date
135 AND :l_as_of_date
136 AND rownum = 1 ';
137
138 DBMS_SQL.Parse(l_cursor_id,l_stmt,DBMS_SQL.V7);
139 DBMS_SQL.Bind_Variable(l_cursor_id,':l_effective_start_date',l_effective_start_date);
140 DBMS_SQL.Bind_Variable(l_cursor_id,':l_as_of_date',l_as_of_date);
141 END IF;
142
143 l_dummy := DBMS_SQL.Execute(l_cursor_id);
144
145 IF DBMS_SQL.Fetch_Rows(l_cursor_id) = 0 -- no snapshot taken
146 THEN l_snapshot_taken := FALSE;
147 ELSE l_snapshot_taken := TRUE;
148 END IF;
149
150 DBMS_SQL.Close_Cursor(l_cursor_id);
151
152 EXCEPTION WHEN OTHERS
153 THEN
154 DBMS_SQL.Close_Cursor(l_cursor_id);
155 l_snapshot_taken := TRUE;
156
157 END;
158
159 l_lang := USERENV('LANG');
160
161 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
162 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
163
164 -- SQL statement generation: SELECT
165
166 IF NOT(l_snapshot_taken)
167 THEN l_sql_stmt := '
168 SELECT 0 VIEWBY,
169 0 VIEWBYID,
170 0 ISC_ATTRIBUTE_1, -- Description
171 0 ISC_ATTRIBUTE_2, -- UOM
172 0 ISC_MEASURE_1, -- Backordered Quantity
173 0 ISC_MEASURE_2, -- Backordered Lines
174 0 ISC_MEASURE_3, -- Backordered Lines - prior
175 0 ISC_MEASURE_4, -- (Backordered Lines) Change
176 0 ISC_MEASURE_5, -- Backordered Items
177 0 ISC_MEASURE_6, -- Backordered Items - prior
178 0 ISC_MEASURE_7, -- (Backordered Items) Change
179 0 ISC_MEASURE_8, -- Grand Total - Backordered Lines
180 0 ISC_MEASURE_9, -- Grand Total - (Backordered Lines) Change
181 0 ISC_MEASURE_10, -- Grand Total - Backordered Items
182 0 ISC_MEASURE_11 -- Grand Total - (Backordered Items) Change
183 FROM dual
184 WHERE 1 = 2 /* No snapshot has been taken during this period */';
185
186 ELSE
187
188 l_formula_sql :=
189 'c.quantity ISC_MEASURE_1, -- Backordered Quantity
190 c.line_cnt ISC_MEASURE_2, -- Backordered Lines
191 c.prev_line_cnt ISC_MEASURE_3, -- Backordered Lines - prior
192 (c.line_cnt - c.prev_line_cnt)
193 / decode( c.prev_line_cnt,0,
194 NULL,
195 abs(c.prev_line_cnt))* 100 ISC_MEASURE_4, -- (Backordered Lines) Change
196 c.item_cnt ISC_MEASURE_5, -- Backordered Items
197 c.prev_item_cnt ISC_MEASURE_6, -- Backordered Items - prior
198 (c.item_cnt - c.prev_item_cnt)
199 / decode( c.prev_item_cnt,0,
200 NULL,
201 abs(c.prev_item_cnt))* 100 ISC_MEASURE_7, -- (Backordered Items) Change
202 sum(c.line_cnt) over () ISC_MEASURE_8, -- Grand Total - Backordered Lines
203 (sum(c.line_cnt) over () - sum(c.prev_line_cnt) over ())
204 / decode( sum(c.prev_line_cnt) over(),0,
205 NULL,
209 (sum(c.item_cnt) over () - sum(c.prev_item_cnt) over ())
206 abs(sum(c.prev_line_cnt) over())) * 100
207 ISC_MEASURE_9, -- Grand Total - (Backordered Lines) Change
208 sum(c.item_cnt) over () ISC_MEASURE_10, -- Grand Total - Backordered Items
210 / decode( sum(c.prev_item_cnt) over(),0,
211 NULL,
212 abs(sum(c.prev_item_cnt) over())) * 100
213 ISC_MEASURE_11 -- Grand Total - (Backordered Items) Change';
214 IF(l_period_type = 'FII_TIME_DAY')
215 THEN
216 l_inner_sql :=
217 ' sum(decode(mv.time_snapshot_date_id, &BIS_CURRENT_ASOF_DATE,
218 mv.backorder_qty, 0)) QUANTITY,
219 sum(decode(mv.time_snapshot_date_id, &BIS_CURRENT_ASOF_DATE,
220 mv.backorder_line_cnt, 0)) LINE_CNT,
221 sum(decode(mv.time_snapshot_date_id, &BIS_PREVIOUS_ASOF_DATE,
222 mv.backorder_line_cnt, 0)) PREV_LINE_CNT,
223 count(distinct(decode(mv.time_snapshot_date_id,&BIS_CURRENT_ASOF_DATE,
224 mv.item_id, null))) ITEM_CNT,
225 count(distinct(decode(mv.time_snapshot_date_id,
226 &BIS_PREVIOUS_ASOF_DATE,
227 mv.item_id, null))) PREV_ITEM_CNT
228 FROM ISC_DBI_FM_0007_MV mv
229 WHERE mv.time_snapshot_date_id IN (&BIS_CURRENT_ASOF_DATE,
230 &BIS_PREVIOUS_ASOF_DATE)';
231
232 ELSE
233 l_inner_sql :=
234 ' sum(decode(mv.time_snapshot_date_id, a.day,
235 mv.backorder_qty, 0)) QUANTITY,
236 sum(decode(mv.time_snapshot_date_id, a.day,
237 mv.backorder_line_cnt, 0)) LINE_CNT,
238 sum(decode(mv.time_snapshot_date_id, b.day,
239 mv.backorder_line_cnt, 0)) PREV_LINE_CNT,
240 count(distinct(decode(mv.time_snapshot_date_id, a.day,
241 mv.item_id, null))) ITEM_CNT,
242 count(distinct(decode(mv.time_snapshot_date_id, b.day,
243 mv.item_id, null))) PREV_ITEM_CNT
244 FROM (SELECT max(time_snapshot_date_id) DAY
245 FROM ISC_DBI_FM_0007_MV mv
246 WHERE mv.time_snapshot_date_id BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
247 AND &BIS_CURRENT_ASOF_DATE
248 ) a,
249 (SELECT max(time_snapshot_date_id) DAY
250 FROM ISC_DBI_FM_0007_MV mv
251 WHERE mv.time_snapshot_date_id BETWEEN &BIS_PREVIOUS_EFFECTIVE_START_DATE
252 AND &BIS_PREVIOUS_ASOF_DATE
253 ) b,
254 ISC_DBI_FM_0007_MV mv
255 WHERE mv.time_snapshot_date_id IN (a.day, b.day)';
256 END IF;
257
258 l_inner_sql := l_inner_sql||l_inv_org_where||l_inv_cat_where||l_item_where||l_customer_where;
259
260 l_outer_sql:= 'ISC_MEASURE_1,ISC_MEASURE_2,ISC_MEASURE_3,ISC_MEASURE_4,ISC_MEASURE_5,ISC_MEASURE_6,
261 ISC_MEASURE_7,ISC_MEASURE_8,ISC_MEASURE_9,ISC_MEASURE_10,ISC_MEASURE_11';
262
263 IF l_view_by = 'CUSTOMER+FII_CUSTOMERS' THEN
264 l_sql_stmt := '
265 SELECT cust.value VIEWBY,
266 cust.id VIEWBYID,
267 null ISC_ATTRIBUTE_1, -- Description
268 null ISC_ATTRIBUTE_2, -- UOM
269 '||l_outer_sql||'
270 FROM (SELECT (rank() over (&ORDER_BY_CLAUSE NULLS LAST,customer_id))-1 rnk,
271 customer_id,
272 '||l_outer_sql||'
273 FROM (SELECT customer_id,
274 '||l_formula_sql||'
275 FROM (SELECT mv.customer_id CUSTOMER_ID,
276 '||l_inner_sql||'
277 GROUP BY mv.customer_id) c)) a,
278 FII_CUSTOMERS_V cust
279 WHERE a.customer_id = cust.id
280 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
281 ORDER BY rnk';
282
283 ELSIF l_view_by = 'ORGANIZATION+ORGANIZATION' THEN
284 l_sql_stmt := '
285 SELECT org.name VIEWBY,
286 org.organization_id VIEWBYID,
287 null ISC_ATTRIBUTE_1, -- Description
288 null ISC_ATTRIBUTE_2, -- UOM
289 '||l_outer_sql||'
290 FROM (SELECT (rank() over (&ORDER_BY_CLAUSE NULLS LAST,inv_org_id))-1 rnk,
291 inv_org_id,
292 '||l_outer_sql||'
293 FROM (SELECT inv_org_id,
294 '||l_formula_sql||'
295 FROM (SELECT mv.inv_org_id INV_ORG_ID,
296 '||l_inner_sql||'
297 GROUP BY mv.inv_org_id) c)) a,
298 HR_ALL_ORGANIZATION_UNITS_TL org
299 WHERE a.inv_org_id = org.organization_id
300 AND org.language = :ISC_LANG
301 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
302 ORDER BY rnk';
303
304 ELSIF l_view_by = 'ITEM+ENI_ITEM_ORG' THEN
305 l_sql_stmt := '
306 SELECT items.value VIEWBY,
307 items.id VIEWBYID,
308 items.description ISC_ATTRIBUTE_1, -- Description
309 mtl.unit_of_measure ISC_ATTRIBUTE_2, -- UOM
310 '||l_outer_sql||'
311 FROM (SELECT (rank() over (&ORDER_BY_CLAUSE NULLS LAST,item_id))-1 rnk,
312 item_id,
313 uom,
314 '||l_outer_sql||'
315 FROM (SELECT item_id,
316 uom,
317 '||l_formula_sql||'
318 FROM (SELECT mv.item_id ITEM_ID,
319 mv.uom UOM,
320 '||l_inner_sql||'
321 GROUP BY mv.item_id, mv.uom) c)) a,
322 ENI_ITEM_ORG_V items,
323 MTL_UNITS_OF_MEASURE_TL mtl
324 WHERE a.item_id = items.id
325 AND a.uom = mtl.uom_code
326 AND mtl.language = :ISC_LANG
327 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
328 ORDER BY rnk';
329
330 ELSE -- l_view_by = 'ITEM+ENI_ITEM_INV_CAT'
331 l_sql_stmt := '
332 SELECT eni.value VIEWBY,
333 eni.id VIEWBYID,
334 null ISC_ATTRIBUTE_1, -- Description
335 null ISC_ATTRIBUTE_2, -- UOM
336 '||l_outer_sql||'
337 FROM (SELECT (rank() over (&ORDER_BY_CLAUSE NULLS LAST,item_category_id))-1 rnk,
338 item_category_id,
339 '||l_outer_sql||'
340 FROM (SELECT item_category_id,
341 '||l_formula_sql||'
342 FROM (SELECT mv.item_category_id ITEM_CATEGORY_ID,
343 '||l_inner_sql||'
344 GROUP BY mv.item_category_id) c)) a,
345 ENI_ITEM_INV_CAT_V eni
346 WHERE a.item_category_id = eni.id
347 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
348 ORDER BY rnk';
349
353
350 END IF;
351
352 END IF;
354 x_custom_sql := l_sql_stmt;
355
356 l_custom_rec.attribute_name := ':ISC_LANG';
357 l_custom_rec.attribute_value := l_lang;
358 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
359 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
360 x_custom_output.extend;
361 x_custom_output(1) := l_custom_rec;
362
363 END Get_Sql;
364
365 END ISC_DBI_BACKORDER_SUM_PKG ;
366