[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_PDUE_PRM_SUM_PKG
Source
1 PACKAGE BODY ISC_DBI_PDUE_PRM_SUM_PKG AS
2 /* $Header: ISCRGA7B.pls 120.0 2005/05/25 17:29:12 appldev 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(10000);
10 l_measures VARCHAR2(10000);
11 l_select_stmt VARCHAR2(10000);
12 l_inner_select_stmt VARCHAR2(10000);
13 l_inner_group_by_stmt VARCHAR2(10000);
14 l_where_stmt VARCHAR2(10000);
15 l_period_type VARCHAR2(10000);
16 l_inv_org VARCHAR2(10000);
17 l_inv_org_where VARCHAR2(10000);
18 l_prod VARCHAR2(10000);
19 l_prod_where VARCHAR2(10000);
20 l_prod_cat VARCHAR2(10000);
21 l_prod_cat_from VARCHAR2(10000);
22 l_prod_cat_where VARCHAR2(10000);
23 l_cust VARCHAR2(10000);
24 l_cust_where VARCHAR2(10000);
25 l_curr VARCHAR2(10000);
26 l_curr_suffix VARCHAR2(120);
27 l_view_by VARCHAR2(120);
28 l_bucket VARCHAR2(120);
29 l_pdue_qty VARCHAR2(120);
30 l_pdue_amt VARCHAR2(120);
31 l_days_late VARCHAR2(120);
32 l_line_cnt VARCHAR2(120);
33 l_lang VARCHAR2(10);
34 l_mv VARCHAR2(10000);
35 l_flags_where VARCHAR2(10000);
36 l_item_cat_flag NUMBER;
37 l_cust_flag NUMBER;
38 l_snapshot_taken BOOLEAN;
39 l_as_of_date DATE;
40 l_effective_start_date DATE;
41 l_cursor_id NUMBER;
42 l_dummy NUMBER;
43 l_custom_rec BIS_QUERY_ATTRIBUTES;
44
45 BEGIN
46
47 l_lang := userenv('LANG');
48
49 FOR i IN 1..p_param.COUNT
50 LOOP
51 IF (p_param(i).parameter_name = 'AS_OF_DATE')
52 THEN l_as_of_date := to_date(p_param(i).parameter_value, 'DD-MM-YYYY');
53 END IF;
54
55 IF (p_param(i).parameter_name = 'PERIOD_TYPE')
56 THEN l_period_type := p_param(i).parameter_value;
57 END IF;
58
59 IF (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
60 THEN l_curr := p_param(i).parameter_id;
61 END IF;
62
63 IF (p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
64 THEN l_inv_org := p_param(i).parameter_value;
65 END IF;
66
67 IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT')
68 THEN l_prod_cat := p_param(i).parameter_value;
69 END IF;
70
71 IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG')
72 THEN l_prod := p_param(i).parameter_value;
73 END IF;
74
75 IF (p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS')
76 THEN l_cust := p_param(i).parameter_value;
77 END IF;
78
79 IF (p_param(i).parameter_name = 'VIEW_BY')
80 THEN l_view_by := p_param(i).parameter_value;
81 END IF;
82
83 IF (p_param(i).parameter_name = 'ISC_ATTRIBUTE_6')
84 THEN l_bucket := p_param(i).parameter_id;
85 END IF;
86 END LOOP;
87
88 IF (l_curr = '''FII_GLOBAL1''')
89 THEN l_curr_suffix := 'g';
90 ELSIF (l_curr = '''FII_GLOBAL2''')
91 THEN l_curr_suffix :='g1';
92 ELSE l_curr_suffix := 'f';
93 END IF;
94
95 IF (l_inv_org IS NULL OR l_inv_org = '' OR l_inv_org = 'All')
96 THEN l_inv_org_where := '
97 AND (EXISTS
98 (SELECT 1
99 FROM org_access o
100 WHERE o.responsibility_id = fnd_global.resp_id
101 AND o.resp_application_id = fnd_global.resp_appl_id
102 AND o.organization_id = fact.inv_org_id)
103 OR EXISTS
104 (SELECT 1
105 FROM mtl_parameters org
106 WHERE org.organization_id = fact.inv_org_id
107 AND NOT EXISTS
108 (SELECT 1
109 FROM org_access ora
110 WHERE org.organization_id = ora.organization_id)))';
111 ELSE l_inv_org_where := '
112 AND fact.inv_org_id = &ORGANIZATION+ORGANIZATION';
113 END IF;
114
115 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
116 THEN
117 IF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT')
118 THEN
119 l_prod_cat_from := ',
120 ENI_DENORM_HIERARCHIES eni_cat,
121 MTL_DEFAULT_CATEGORY_SETS mdcs';
122 l_prod_cat_where := '
123 AND fact.item_category_id = eni_cat.child_id
124 AND eni_cat.top_node_flag = ''Y''
125 AND eni_cat.dbi_flag = ''Y''
126 AND eni_cat.object_type = ''CATEGORY_SET''
127 AND eni_cat.object_id = mdcs.category_set_id
128 AND mdcs.functional_area_id = 11';
129 ELSE
130 l_prod_cat_from := '';
131 l_prod_cat_where := '';
132 END IF;
133 ELSE
134 l_prod_cat_from := ',
135 ENI_DENORM_HIERARCHIES eni_cat,
136 MTL_DEFAULT_CATEGORY_SETS mdcs';
137 l_prod_cat_where := '
138 AND fact.item_category_id = eni_cat.child_id
139 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
140 AND eni_cat.dbi_flag = ''Y''
141 AND eni_cat.object_type = ''CATEGORY_SET''
142 AND eni_cat.object_id = mdcs.category_set_id
143 AND mdcs.functional_area_id = 11';
144 END IF;
145
146 IF (l_prod IS NULL OR l_prod = '' OR l_prod = 'All')
147 THEN l_prod_where := '';
148 ELSE l_prod_where := '
149 AND fact.item_id IN (&ITEM+ENI_ITEM_ORG)';
150 END IF;
151
152 IF (l_cust IS NULL OR l_cust = '' OR l_cust = 'All')
153 THEN
154 l_cust_where := '';
155 IF (l_view_by = 'CUSTOMER+FII_CUSTOMERS')
156 THEN l_cust_flag := 0;
157 ELSE l_cust_flag := 1;
158 END IF;
159 ELSE
160 l_cust_where := '
161 AND fact.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
162 l_cust_flag := 0;
163 END IF;
164
165 IF (l_prod IS NULL OR l_prod = '' OR l_prod = 'All')
166 THEN
167 IF (l_view_by = 'ITEM+ENI_ITEM_ORG')
168 THEN l_item_cat_flag := 0; -- product
169 ELSIF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT')
170 THEN l_item_cat_flag := 1; -- category
171 ELSE
172 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
173 THEN l_item_cat_flag := 3; -- all
174 ELSE l_item_cat_flag := 1; -- category
175 END IF;
176 END IF;
177 ELSE
178 l_item_cat_flag := 0; -- product
179 END IF;
180
181 IF (l_bucket IS NULL OR l_bucket = '')
182 THEN
183 l_pdue_qty := 'pdue_qty';
184 l_pdue_amt := 'pdue_amt_'||l_curr_suffix;
185 l_days_late := 'days_late_promise';
186 l_line_cnt := 'pdue_line_cnt';
187 ELSE
188 l_pdue_qty := 'bucket'||l_bucket||'_qty_p';
189 l_pdue_amt := 'bucket'||l_bucket||'_pdue_amt_'||l_curr_suffix||'_p';
190 l_days_late := 'bucket'||l_bucket||'_days_late_p';
191 l_line_cnt := 'bucket'||l_bucket||'_line_cnt_p';
192 END IF;
193
194 BEGIN
195
196 IF l_period_type = 'FII_TIME_ENT_YEAR'
197 THEN l_effective_start_date := FII_TIME_API.Ent_Cyr_Start(l_as_of_date);
198 ELSIF l_period_type = 'FII_TIME_ENT_QTR'
199 THEN l_effective_start_date := FII_TIME_API.Ent_Cqtr_Start(l_as_of_date);
200 ELSIF l_period_type = 'FII_TIME_ENT_PERIOD'
201 THEN l_effective_start_date := FII_TIME_API.Ent_Cper_Start(l_as_of_date);
202 ELSE -- l_period_type = 'FII_TIME_WEEK'
203 l_effective_start_date := FII_TIME_API.Cwk_Start(l_as_of_date);
204 END IF;
205
206 l_cursor_id := DBMS_SQL.Open_Cursor;
207 l_stmt := '
208 SELECT 1
209 FROM ISC_DBI_CFM_008_MV fact
210 WHERE fact.time_snapshot_date_id BETWEEN :l_effective_start_date
211 AND :l_as_of_date
212 AND rownum = 1 ';
213
214 DBMS_SQL.Parse(l_cursor_id,l_stmt,DBMS_SQL.V7);
215 DBMS_SQL.Bind_Variable(l_cursor_id,':l_effective_start_date',l_effective_start_date);
216 DBMS_SQL.Bind_Variable(l_cursor_id,':l_as_of_date',l_as_of_date);
217
218 l_dummy := DBMS_SQL.Execute(l_cursor_id);
219
220 IF DBMS_SQL.Fetch_Rows(l_cursor_id) = 0 -- no snapshot taken
221 THEN l_snapshot_taken := FALSE;
222 ELSE l_snapshot_taken := TRUE;
223 END IF;
224
225 DBMS_SQL.Close_Cursor(l_cursor_id);
226
227 EXCEPTION WHEN OTHERS
228 THEN
229 DBMS_SQL.Close_Cursor(l_cursor_id);
230 l_snapshot_taken := TRUE;
231
232 END;
233
234 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
235 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
236
237 l_measures := 'ISC_MEASURE_1,ISC_MEASURE_2,ISC_MEASURE_3,ISC_MEASURE_4,ISC_MEASURE_5,
238 ISC_MEASURE_6,ISC_MEASURE_7,ISC_MEASURE_8,ISC_MEASURE_9';
239
240 IF l_view_by = 'ITEM+ENI_ITEM_ORG'
241 THEN l_select_stmt := '
242 SELECT items.value VIEWBY,
243 items.id VIEWBYID,
244 NULL ISC_ATTRIBUTE_3, -- drill across url
245 items.description ISC_ATTRIBUTE_4, -- item description
246 mtl.unit_of_measure ISC_ATTRIBUTE_5, -- item uom
247 '||l_measures||'
248 FROM
249 (SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, item_id)) - 1 rnk,
250 item_id,
251 uom,
252 '||l_measures||'
253 FROM
254 (SELECT c.item_id,
255 c.uom, ';
256 l_inner_select_stmt := '
257 SELECT fact.item_id ITEM_ID,
258 fact.uom UOM,';
259 l_inner_group_by_stmt := '
260 GROUP BY fact.item_id, fact.uom';
261 l_where_stmt := '
262 ENI_ITEM_ORG_V items,
263 MTL_UNITS_OF_MEASURE_TL mtl
264 WHERE a.item_id = items.id
265 AND a.uom = mtl.uom_code
266 AND mtl.language = :ISC_LANG
267 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))';
268
269 ELSIF l_view_by = 'ORGANIZATION+ORGANIZATION'
270 THEN l_select_stmt := '
271 SELECT org.name VIEWBY,
272 org.organization_id VIEWBYID,
273 NULL ISC_ATTRIBUTE_3, -- drill across url
274 NULL ISC_ATTRIBUTE_4, -- item description
275 NULL ISC_ATTRIBUTE_5, -- item uom
276 '||l_measures||'
277 FROM
278 (SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, inv_org_id)) - 1 rnk,
279 inv_org_id,
280 '||l_measures||'
281 FROM
282 (SELECT c.inv_org_id, ';
283 l_inner_select_stmt := '
284 SELECT fact.inv_org_id INV_ORG_ID,';
285 l_inner_group_by_stmt := '
286 GROUP BY fact.inv_org_id';
287 l_where_stmt := '
288 HR_ALL_ORGANIZATION_UNITS_TL org
289 WHERE a.inv_org_id = org.organization_id
290 AND org.language = :ISC_LANG
291 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))';
292
293 ELSIF l_view_by = 'ITEM+ENI_ITEM_VBH_CAT'
294 THEN l_select_stmt := '
295 SELECT ecat.value VIEWBY,
299 ''pFunctionName=ISC_DBI_PDUE_PRM_SUM&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'')
296 ecat.id VIEWBYID,
297 decode(ecat.leaf_node_flag, ''Y'',
298 ''pFunctionName=ISC_DBI_PDUE_PRM_SUM&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_ORG&pParamIds=Y'',
300 ISC_ATTRIBUTE_3, -- drill across url
301 NULL ISC_ATTRIBUTE_4, -- item description
302 NULL ISC_ATTRIBUTE_5, -- item uom
303 '||l_measures||'
304 FROM
305 (SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, item_category_id)) - 1 rnk,
306 item_category_id,
307 '||l_measures||'
308 FROM
309 (SELECT c.item_category_id, ';
310 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
311 THEN
312 l_inner_select_stmt := '
313 SELECT eni_cat.parent_id ITEM_CATEGORY_ID,';
314 l_inner_group_by_stmt := '
315 GROUP BY eni_cat.parent_id';
316 ELSE
317 l_inner_select_stmt := '
318 SELECT eni_cat.imm_child_id ITEM_CATEGORY_ID,';
319 l_inner_group_by_stmt := '
320 GROUP BY eni_cat.imm_child_id';
321 END IF;
322 l_where_stmt := '
323 ENI_ITEM_VBH_NODES_V ecat
324 WHERE a.item_category_id = ecat.id
325 AND ecat.parent_id = ecat.child_id
326 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))';
327
328 ELSE -- l_view_by = 'CUSTOMER+FII_CUSTOMERS'
329 l_select_stmt := '
330 SELECT cust.value VIEWBY,
331 cust.id VIEWBYID,
332 NULL ISC_ATTRIBUTE_3, -- drill across url
333 NULL ISC_ATTRIBUTE_4, -- item description
334 NULL ISC_ATTRIBUTE_5, -- item uom
335 '||l_measures||'
336 FROM
337 (SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, customer_id)) - 1 rnk,
338 customer_id,
339 '||l_measures||'
340 FROM
341 (SELECT c.customer_id, ';
342 l_inner_select_stmt := '
343 SELECT fact.customer_id CUSTOMER_ID,';
344 l_inner_group_by_stmt := '
345 GROUP BY fact.customer_id';
346 l_where_stmt := '
347 FII_CUSTOMERS_V cust
348 WHERE a.customer_id = cust.id
349 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))';
350 END IF;
351
352 IF ((l_view_by = 'ITEM+ENI_ITEM_VBH_CAT' OR l_view_by = 'ORGANIZATION+ORGANIZATION') AND
353 (l_prod IS NULL OR l_prod = 'All') AND
354 (l_cust IS NULL OR l_cust = 'All'))
355 THEN
356 IF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') THEN
357 IF (l_prod_cat IS NULL OR l_prod_cat = 'All')
358 THEN
359 l_inner_select_stmt := '
360 SELECT fact.parent_id ITEM_CATEGORY_ID,';
361 l_inner_group_by_stmt := '
362 GROUP BY fact.parent_id';
363 ELSE
364 l_inner_select_stmt := '
365 SELECT fact.imm_child_id ITEM_CATEGORY_ID,';
366 l_inner_group_by_stmt := '
367 GROUP BY fact.imm_child_id';
368 END IF;
369 END IF;
370
371 IF (l_prod_cat IS NULL OR l_prod_cat = 'All')
372 THEN
373 l_prod_cat_from := '';
374 l_prod_cat_where := '
375 AND fact.top_node_flag = ''Y''';
376 ELSE
377 l_prod_cat_from := '';
378 l_prod_cat_where := '
379 AND fact.parent_id = &ITEM+ENI_ITEM_VBH_CAT';
380 END IF;
381 l_mv := 'ISC_DBI_CFM_012_MV';
382 l_flags_where := '';
383 ELSE
384 l_mv := 'ISC_DBI_CFM_008_MV';
385 l_flags_where := '
386 AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
387 AND fact.customer_flag = :ISC_CUST_FLAG';
388 END IF;
389
390 IF NOT (l_snapshot_taken)
391 THEN l_stmt := '
392 SELECT 0 VIEWBY,
393 0 ISC_ATTRIBUTE_3,
394 0 ISC_ATTRIBUTE_4,
395 0 ISC_ATTRIBUTE_5,
396 0 ISC_MEASURE_1,
397 0 ISC_MEASURE_2,
398 0 ISC_MEASURE_3,
399 0 ISC_MEASURE_4,
400 0 ISC_MEASURE_5,
401 0 ISC_MEASURE_6,
402 0 ISC_MEASURE_7,
403 0 ISC_MEASURE_8,
404 0 ISC_MEASURE_9
405 FROM dual
406 WHERE 1 = 2 -- no snapshot taken in the current period';
407 ELSE
408 l_stmt := l_select_stmt || '
409 c.curr_pdue_qty ISC_MEASURE_1, -- pdue qty
410 c.curr_pdue_value ISC_MEASURE_2, -- pdue
411 (c.curr_pdue_value - c.prev_pdue_value)
412 / decode(c.prev_pdue_value, 0, NULL,
413 abs(c.prev_pdue_value)) * 100 ISC_MEASURE_3, -- pdue change
414 c.curr_line_cnt ISC_MEASURE_4, -- pdue line cnt
415 c.curr_days_late
416 / decode(c.curr_line_cnt, 0, NULL,
417 c.curr_line_cnt) ISC_MEASURE_5, -- pdue avg days late
418 sum(c.curr_pdue_value) over () ISC_MEASURE_6, -- gd total pdue
419 (sum(c.curr_pdue_value) over () - sum(c.prev_pdue_value) over ())
420 / decode(sum(c.prev_pdue_value) over (), 0, NULL,
421 abs(sum(c.prev_pdue_value) over ())) * 100 ISC_MEASURE_7, -- gd total pdue change
422 sum(c.curr_line_cnt) over () ISC_MEASURE_8, -- gd total pdue line cnt
423 sum(c.curr_days_late) over ()
424 / decode(sum(c.curr_line_cnt) over (), 0, NULL,
425 sum(c.curr_line_cnt) over ()) ISC_MEASURE_9 -- gd total avg days late
426 FROM ('||l_inner_select_stmt||'
427 sum(decode(fact.time_snapshot_date_id, a.day,
428 fact.'||l_pdue_qty||', 0)) CURR_PDUE_QTY,
429 sum(decode(fact.time_snapshot_date_id, a.day,
430 fact.'||l_pdue_amt||', 0)) CURR_PDUE_VALUE,
431 sum(decode(fact.time_snapshot_date_id, b.day,
432 fact.'||l_pdue_amt||', 0)) PREV_PDUE_VALUE,
433 sum(decode(fact.time_snapshot_date_id, a.day,
434 fact.'||l_days_late||', 0)) CURR_DAYS_LATE,
438 FROM '||l_mv||' fact
435 sum(decode(fact.time_snapshot_date_id, a.day,
436 fact.'||l_line_cnt||', 0)) CURR_LINE_CNT
437 FROM (SELECT max(time_snapshot_date_id) day
439 WHERE fact.time_snapshot_date_id BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
440 AND &BIS_CURRENT_ASOF_DATE
441 ) a,
442 (SELECT max(time_snapshot_date_id) day
443 FROM '||l_mv||' fact
444 WHERE fact.time_snapshot_date_id BETWEEN &BIS_PREVIOUS_EFFECTIVE_START_DATE
445 AND &BIS_PREVIOUS_ASOF_DATE
446 ) b,
447 '||l_mv||' fact'||l_prod_cat_from||'
448 WHERE fact.time_snapshot_date_id IN (a.day, b.day)
449 AND fact.late_promise_flag = 1'||l_flags_where||'
450 AND fact.'||l_line_cnt||' <> 0'
451 ||l_inv_org_where
452 ||l_prod_cat_where
453 ||l_prod_where
454 ||l_cust_where
455 ||l_inner_group_by_stmt||') c)) a,'
456 ||l_where_stmt||'
457 ORDER BY rnk';
458 END IF;
459
460 x_custom_sql := l_stmt;
461
462 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
463 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
464 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
465 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
466 x_custom_output.extend;
467 x_custom_output(1) := l_custom_rec;
468
469 l_custom_rec.attribute_name := ':ISC_CUST_FLAG';
470 l_custom_rec.attribute_value := to_char(l_cust_flag);
471 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
472 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
473 x_custom_output.extend;
474 x_custom_output(2) := l_custom_rec;
475
476 l_custom_rec.attribute_name := ':ISC_LANG';
477 l_custom_rec.attribute_value := l_lang;
478 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
479 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
480 x_custom_output.EXTEND;
481 x_custom_output(3) := l_custom_rec;
482
483 END Get_Sql;
484
485 END ISC_DBI_PDUE_PRM_SUM_PKG;