[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_PAST_DUE_SUM_PKG
Source
1 PACKAGE BODY ISC_DBI_PAST_DUE_SUM_PKG AS
2 /* $Header: ISCRG77B.pls 120.1 2006/06/26 06:29: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_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_bucket VARCHAR2(10000);
16 l_customer VARCHAR2(10000);
17 l_customer_where VARCHAR2(10000);
18 l_item VARCHAR2(10000);
19 l_item_where VARCHAR2(10000);
20 l_inv_cat VARCHAR2(10000);
21 l_inv_cat_where VARCHAR2(10000);
22 l_inv_org VARCHAR2(10000);
23 l_inv_org_where VARCHAR2(10000);
24 l_item_cat_flag NUMBER; -- 0 for item, 1 for inv category
25 l_customer_flag NUMBER; -- 0 for customer level, 1 for no-customer level
26
27 l_qty VARCHAR2(10000);
28 l_lines VARCHAR2(10000);
29 l_days VARCHAR2(10000);
30
31 l_period_type VARCHAR2(240);
32 l_snapshot_taken BOOLEAN := TRUE;
33 l_as_of_date DATE;
34 l_effective_start_date DATE;
35 l_cursor_id NUMBER;
36 l_dummy NUMBER;
37 l_lang VARCHAR2(10);
38 l_custom_rec BIS_QUERY_ATTRIBUTES;
39
40 BEGIN
41
42 FOR i IN 1..p_param.COUNT
43 LOOP
44 IF( p_param(i).parameter_name= 'VIEW_BY')
45 THEN l_view_by := p_param(i).parameter_value;
46 END IF;
47
48 IF(p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS')
49 THEN l_customer := p_param(i).parameter_value;
50 END IF;
51
52 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
53 THEN l_inv_org := p_param(i).parameter_value;
54 END IF;
55
56 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_INV_CAT')
57 THEN l_inv_cat := p_param(i).parameter_value;
58 END IF;
59
60 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG')
61 THEN l_item := p_param(i).parameter_value;
62 END IF;
63
64 IF(p_param(i).parameter_name = 'AS_OF_DATE')
65 THEN l_as_of_date := to_date(p_param(i).parameter_value, 'DD-MM-YYYY');
66 END IF;
67
68 IF(p_param(i).parameter_name = 'PERIOD_TYPE')
69 THEN l_period_type := p_param(i).parameter_value;
70 END IF;
71
72 IF(p_param(i).parameter_name = 'ISC_ATTRIBUTE_3')
73 THEN l_bucket := p_param(i).parameter_id;
74 END IF;
75
76 END LOOP;
77
78 IF(l_inv_org IS NULL OR l_inv_org = 'All')
79 THEN l_inv_org_where := '
80 AND (EXISTS
81 (SELECT 1
82 FROM org_access o
83 WHERE o.responsibility_id = fnd_global.resp_id
84 AND o.resp_application_id = fnd_global.resp_appl_id
85 AND o.organization_id = mv.inv_org_id)
86 OR EXISTS
87 (SELECT 1
88 FROM mtl_parameters org
89 WHERE org.organization_id = mv.inv_org_id
90 AND NOT EXISTS
91 (SELECT 1
92 FROM org_access ora
93 WHERE org.organization_id = ora.organization_id)))';
94 ELSE l_inv_org_where := '
95 AND mv.inv_org_id = &ORGANIZATION+ORGANIZATION';
96 END IF;
97
98 IF(l_inv_cat IS NULL OR l_inv_cat = 'All')
99 THEN l_inv_cat_where := '';
100 ELSE l_inv_cat_where := '
101 AND mv.item_category_id IN (&ITEM+ENI_ITEM_INV_CAT)';
102 END IF;
103
104 IF(l_item IS NULL OR l_item = 'All')
105 THEN l_item_where := '';
106 ELSE l_item_where := '
107 AND mv.item_id IN (&ITEM+ENI_ITEM_ORG)';
108 END IF;
109
110 IF (l_customer IS NULL OR l_customer = 'All')
111 THEN l_customer_where :='';
112 IF(l_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
113 l_customer_flag := 0;
114 ELSE
115 l_customer_flag := 1; -- do not need customer id
116 END IF;
117 ELSE l_customer_where :='
118 AND mv.customer_id IN (&CUSTOMER+FII_CUSTOMERS)';
119 l_customer_flag := 0; -- customer level
120 END IF;
121
122 IF (l_item IS NULL OR l_item = 'All')
123 THEN
124 IF (l_view_by = 'ITEM+ENI_ITEM_ORG')
125 THEN l_item_cat_flag := 0; -- item
126 ELSIF (l_view_by = 'ITEM+ENI_ITEM_INV_CAT')
127 THEN l_item_cat_flag := 1; -- inventory category
128 ELSE
129 IF (l_inv_cat IS NULL OR l_inv_cat = 'All')
130 THEN l_item_cat_flag := 3; -- all
131 ELSE l_item_cat_flag := 1; -- inventory category
132 END IF;
133 END IF;
134 ELSE
135 l_item_cat_flag := 0; -- item
136 END IF;
137
138 IF (l_bucket = '' OR l_bucket IS NULL)
139 THEN l_qty := 'mv.pdue_qty';
140 l_lines := 'mv.pdue_line_cnt';
141 l_days := 'mv.days_late';
142 ELSIF(l_bucket = 1)
143 THEN l_qty := 'mv.bucket1_qty';
144 l_lines := 'mv.bucket1_line_cnt';
145 l_days := 'mv.bucket1_days_late';
146 ELSIF (l_bucket = 2)
147 THEN l_qty := 'mv.bucket2_qty';
148 l_lines := 'mv.bucket2_line_cnt';
149 l_days := 'mv.bucket2_days_late';
150 ELSIF (l_bucket = 3)
151 THEN l_qty := 'mv.bucket3_qty';
152 l_lines := 'mv.bucket3_line_cnt';
153 l_days := 'mv.bucket3_days_late';
154 ELSIF (l_bucket = 4)
155 THEN l_qty := 'mv.bucket4_qty';
156 l_lines := 'mv.bucket4_line_cnt';
157 l_days := 'mv.bucket4_days_late';
158 ELSIF (l_bucket = 5)
159 THEN l_qty := 'mv.bucket5_qty';
160 l_lines := 'mv.bucket5_line_cnt';
161 l_days := 'mv.bucket5_days_late';
162 ELSIF (l_bucket = 6)
163 THEN l_qty := 'mv.bucket6_qty';
164 l_lines := 'mv.bucket6_line_cnt';
165 l_days := 'mv.bucket6_days_late';
166 ELSIF (l_bucket = 7)
167 THEN l_qty := 'mv.bucket7_qty';
168 l_lines := 'mv.bucket7_line_cnt';
169 l_days := 'mv.bucket7_days_late';
170 ELSIF (l_bucket = 8)
171 THEN l_qty := 'mv.bucket8_qty';
172 l_lines := 'mv.bucket8_line_cnt';
173 l_days := 'mv.bucket8_days_late';
174 ELSIF (l_bucket = 9)
175 THEN l_qty := 'mv.bucket9_qty';
176 l_lines := 'mv.bucket9_line_cnt';
177 l_days := 'mv.bucket9_days_late';
178 ELSIF (l_bucket = 10)
179 THEN l_qty := 'mv.bucket10_qty';
180 l_lines := 'mv.bucket10_line_cnt';
181 l_days := 'mv.bucket10_days_late';
182 ELSE l_qty := 'mv.pdue_qty';
183 l_lines := 'mv.pdue_line_cnt';
184 l_days := 'mv.days_late';
185 END IF;
186
187 BEGIN
188
189 IF l_period_type = 'FII_TIME_ENT_YEAR'
190 THEN l_effective_start_date := FII_TIME_API.Ent_Cyr_Start(l_as_of_date);
191 ELSIF l_period_type = 'FII_TIME_ENT_QTR'
192 THEN l_effective_start_date := FII_TIME_API.Ent_Cqtr_Start(l_as_of_date);
193 ELSIF l_period_type = 'FII_TIME_ENT_PERIOD'
194 THEN l_effective_start_date := FII_TIME_API.Ent_Cper_Start(l_as_of_date);
195 ELSE -- l_period_type = 'FII_TIME_WEEK'
196 l_effective_start_date := FII_TIME_API.Cwk_Start(l_as_of_date);
197 END IF;
198
199 l_cursor_id := DBMS_SQL.Open_Cursor;
200 l_stmt := '
201 SELECT 1
202 FROM ISC_BOOK_SUM2_PDUE_F mv
203 WHERE mv.time_snapshot_date_id BETWEEN :l_effective_start_date
204 AND :l_as_of_date
205 AND rownum = 1 ';
206
207 DBMS_SQL.Parse(l_cursor_id,l_stmt,DBMS_SQL.V7);
208 DBMS_SQL.Bind_Variable(l_cursor_id,':l_effective_start_date',l_effective_start_date);
209 DBMS_SQL.Bind_Variable(l_cursor_id,':l_as_of_date',l_as_of_date);
210
211 l_dummy := DBMS_SQL.Execute(l_cursor_id);
212
213 IF DBMS_SQL.Fetch_Rows(l_cursor_id) = 0 -- no snapshot taken
214 THEN l_snapshot_taken := FALSE;
215 ELSE l_snapshot_taken := TRUE;
216 END IF;
217
218 DBMS_SQL.Close_Cursor(l_cursor_id);
219
220 EXCEPTION WHEN OTHERS
221 THEN
222 DBMS_SQL.Close_Cursor(l_cursor_id);
223 l_snapshot_taken := TRUE;
224
225 END;
226
227 l_lang := USERENV('LANG');
228
229 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
230 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
231
232 -- SQL statement generation: SELECT
233
234 IF NOT(l_snapshot_taken)
235 THEN l_sql_stmt := '
236 SELECT 0 VIEWBY,
237 0 VIEWBYID,
238 0 ISC_ATTRIBUTE_2,
239 0 ISC_ATTRIBUTE_4, -- description
240 0 ISC_ATTRIBUTE_5, -- UOM
241 0 ISC_MEASURE_2, -- past due quantity
242 0 ISC_MEASURE_1, -- line count
243 0 ISC_MEASURE_11, -- change line count
244 0 ISC_MEASURE_4, -- line count - prior
245 0 ISC_MEASURE_5, -- avg days late
246 0 ISC_MEASURE_6, -- gd total line count
247 0 ISC_MEASURE_9, -- gd total avg days late
248 0 ISC_MEASURE_10, -- gd total change line count
249 0 ISC_MEASURE_12, -- KPI past due schedule lines
250 0 ISC_MEASURE_13, -- KPI past due schedule lines - prior
251 0 ISC_MEASURE_3, -- obsolete from DBI 5.0
252 0 ISC_MEASURE_7, -- obsolete from DBI 5.0
253 0 CURRENCY -- obsolete from DBI 5.0
254 FROM dual
255 WHERE 1 = 2 /* No snapshot has been taken during this period*/';
256
257 ELSE
258
259 l_formula_sql :=
260 'c.line_cnt ISC_MEASURE_1, -- line count
261 c.quantity ISC_MEASURE_2, -- past due quantity
262 c.prev_line_cnt ISC_MEASURE_4, -- line count - prior
263 c.avg_days_late ISC_MEASURE_5, -- avg days late
264 sum(c.line_cnt) over () ISC_MEASURE_6, -- gd total line count
265 sum(c.days_late) over()
266 / decode( sum(c.line_cnt) over(),0,
267 NULL,
268 sum(c.line_cnt) over())
269 ISC_MEASURE_9, -- gd total avg days late
270 (sum(c.line_cnt) over () - sum(c.prev_line_cnt) over ())
271 / decode( sum(c.prev_line_cnt) over(),0,
272 NULL,
273 abs(sum(c.prev_line_cnt) over())) * 100
274 ISC_MEASURE_10, -- gd total change past due lines
275 (c.line_cnt - c.prev_line_cnt)
276 / decode( c.prev_line_cnt,0,
277 NULL,
278 abs(c.prev_line_cnt))* 100 ISC_MEASURE_11, -- change past due lines
279 c.line_cnt ISC_MEASURE_12, -- KPI past due schedule lines
280 c.prev_line_cnt ISC_MEASURE_13, -- KPI past due schedule lines - prior
281 sum(c.line_cnt) over () ISC_MEASURE_3, -- KPI past due schedule lines - grand total
282 sum(c.prev_line_cnt) over () ISC_MEASURE_7, -- KPI past due schedule lines - prior grand total
283 null CURRENCY -- obsolete';
284
285 l_inner_sql :=
286 ' sum(decode(mv.time_snapshot_date_id, a.day,
287 '||l_qty||', 0)) QUANTITY,
288 sum(decode(mv.time_snapshot_date_id, a.day,
289 '||l_lines||', 0)) LINE_CNT,
290 sum(decode(mv.time_snapshot_date_id, b.day,
291 '||l_lines||', 0)) PREV_LINE_CNT,
292 sum(decode(mv.time_snapshot_date_id, a.day,
293 '||l_days||', 0)) DAYS_LATE,
294 sum(decode(mv.time_snapshot_date_id, a.day,
295 '||l_days||', 0))
296 / decode( sum(decode(mv.time_snapshot_date_id, a.day,
297 '||l_lines||', 0)), 0,
298 NULL,
299 sum(decode( mv.time_snapshot_date_id, a.day,
300 '||l_lines||', 0)) ) AVG_DAYS_LATE
301 FROM (SELECT max(time_snapshot_date_id) DAY
302 FROM ISC_DBI_FM_0006_MV mv
303 WHERE mv.time_snapshot_date_id BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
304 AND &BIS_CURRENT_ASOF_DATE
305 ) a,
306 (SELECT max(time_snapshot_date_id) DAY
307 FROM ISC_DBI_FM_0006_MV mv
308 WHERE mv.time_snapshot_date_id BETWEEN &BIS_PREVIOUS_EFFECTIVE_START_DATE
309 AND &BIS_PREVIOUS_ASOF_DATE
310 ) b,
311 ISC_DBI_FM_0006_MV mv
312 WHERE mv.time_snapshot_date_id IN (a.day, b.day)
313 AND mv.item_cat_flag = :ISC_ITEM_CAT_FLAG
314 AND mv.customer_flag = :ISC_CUSTOMER_FLAG
315 AND '||l_lines||' > 0'
319 ISC_MEASURE_6,ISC_MEASURE_9,ISC_MEASURE_10,ISC_MEASURE_12,
316 ||l_inv_org_where||l_inv_cat_where||l_item_where||l_customer_where;
317
318 l_outer_sql:= 'ISC_MEASURE_2,ISC_MEASURE_1,ISC_MEASURE_11,ISC_MEASURE_4,ISC_MEASURE_5,
320 ISC_MEASURE_13,ISC_MEASURE_3,ISC_MEASURE_7,CURRENCY';
321
322 IF l_view_by = 'CUSTOMER+FII_CUSTOMERS' THEN
323 l_sql_stmt := '
324 SELECT cust.value VIEWBY,
325 cust.id VIEWBYID,
326 cust.value ISC_ATTRIBUTE_2,
327 null ISC_ATTRIBUTE_4,
328 null ISC_ATTRIBUTE_5,
329 '||l_outer_sql||'
330 FROM (SELECT (rank() over (&ORDER_BY_CLAUSE NULLS LAST,customer_id))-1 rnk,
331 customer_id,
332 '||l_outer_sql||'
333 FROM (SELECT customer_id,
334 '||l_formula_sql||'
335 FROM (SELECT mv.customer_id CUSTOMER_ID,
336 '||l_inner_sql||'
337 GROUP BY mv.customer_id) c)) a,
338 FII_CUSTOMERS_V cust
339 WHERE a.customer_id = cust.id
340 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
341 ORDER BY rnk';
342
343 ELSIF l_view_by = 'ORGANIZATION+ORGANIZATION' THEN
344 l_sql_stmt := '
345 SELECT org.name VIEWBY,
346 org.organization_id VIEWBYID,
347 org.name ISC_ATTRIBUTE_2,
348 null ISC_ATTRIBUTE_4,
349 null ISC_ATTRIBUTE_5,
350 '||l_outer_sql||'
351 FROM (SELECT (rank() over (&ORDER_BY_CLAUSE NULLS LAST,inv_org_id))-1 rnk,
352 inv_org_id,
353 '||l_outer_sql||'
354 FROM (SELECT inv_org_id,
355 '||l_formula_sql||'
356 FROM (SELECT mv.inv_org_id INV_ORG_ID,
357 '||l_inner_sql||'
358 GROUP BY mv.inv_org_id) c)) a,
359 HR_ALL_ORGANIZATION_UNITS_TL org
360 WHERE a.inv_org_id = org.organization_id
361 AND org.language = :ISC_LANG
362 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
363 ORDER BY rnk';
364
365 ELSIF l_view_by = 'ITEM+ENI_ITEM_ORG' THEN
366 l_sql_stmt := '
367 SELECT items.value VIEWBY,
368 items.id VIEWBYID,
369 items.value ISC_ATTRIBUTE_2,
370 items.description ISC_ATTRIBUTE_4, -- item description
371 mtl.unit_of_measure ISC_ATTRIBUTE_5, -- UOM
372 '||l_outer_sql||'
373 FROM (SELECT (rank() over (&ORDER_BY_CLAUSE NULLS LAST,item_id))-1 rnk,
374 item_id,
375 uom,
376 '||l_outer_sql||'
377 FROM (SELECT item_id,
378 uom,
379 '||l_formula_sql||'
380 FROM (SELECT mv.item_id ITEM_ID,
381 mv.uom UOM,
382 '||l_inner_sql||'
383 GROUP BY mv.item_id, mv.uom) c)) a,
384 ENI_ITEM_ORG_V items,
385 MTL_UNITS_OF_MEASURE_TL mtl
386 WHERE a.item_id = items.id
387 AND a.uom = mtl.uom_code
388 AND mtl.language = :ISC_LANG
389 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
390 ORDER BY rnk';
391
392 ELSE -- l_view_by = 'ITEM+ENI_ITEM_INV_CAT'
393 l_sql_stmt := '
394 SELECT eni.value VIEWBY,
395 eni.id VIEWBYID,
396 eni.value ISC_ATTRIBUTE_2,
397 null ISC_ATTRIBUTE_4,
398 null ISC_ATTRIBUTE_5,
399 '||l_outer_sql||'
400 FROM (SELECT (rank() over (&ORDER_BY_CLAUSE NULLS LAST,item_category_id))-1 rnk,
401 item_category_id,
402 '||l_outer_sql||'
403 FROM (SELECT item_category_id,
404 '||l_formula_sql||'
405 FROM (SELECT mv.item_category_id ITEM_CATEGORY_ID,
406 '||l_inner_sql||'
407 GROUP BY mv.item_category_id) c)) a,
408 ENI_ITEM_INV_CAT_V eni
409 WHERE a.item_category_id = eni.id
410 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
411 ORDER BY rnk';
412
413 END IF;
414
415 END IF;
416
417 x_custom_sql := l_sql_stmt;
418
419 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
420 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
421 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
422 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
423 x_custom_output.extend;
424 x_custom_output(1) := l_custom_rec;
425
426 l_custom_rec.attribute_name := ':ISC_CUSTOMER_FLAG';
427 l_custom_rec.attribute_value := to_char(l_customer_flag);
428 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
429 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
430 x_custom_output.extend;
431 x_custom_output(2) := l_custom_rec;
432
433 l_custom_rec.attribute_name := ':ISC_LANG';
434 l_custom_rec.attribute_value := l_lang;
435 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
436 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
437 x_custom_output.extend;
438 x_custom_output(3) := l_custom_rec;
439
440 END Get_Sql;
441
442 END ISC_DBI_PAST_DUE_SUM_PKG ;
443