[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_DAYS_SHIP_AGING_PKG
Source
1 PACKAGE BODY ISC_DBI_DAYS_SHIP_AGING_PKG AS
2 /* $Header: ISCRG93B.pls 120.3 2006/06/26 06:24:41 abhdixi noship $ */
3
4 PROCEDURE Get_Sql ( p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
5 x_custom_sql OUT NOCOPY VARCHAR2,
6 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
7
8 l_stmt VARCHAR2(10000);
9 l_inv_org VARCHAR2(10000);
10 l_inv_org_where VARCHAR2(10000);
11 l_item VARCHAR2(10000);
12 l_item_where VARCHAR2(10000);
13 l_inv_cat VARCHAR2(10000);
14 l_inv_cat_where VARCHAR2(10000);
15 l_customer VARCHAR2(10000);
16 l_customer_where VARCHAR2(10000);
17 l_item_cat_flag NUMBER; -- 0 for item, 1 for inv category
18 l_customer_flag NUMBER; -- 0 for customer level, 1 for no-customer level
19 l_agg_level NUMBER;
20
21 l_row_line_cnts VARCHAR2(10000);
22 l_bucket_rec bis_bucket_pub.BIS_BUCKET_REC_TYPE;
23 l_error_tbl bis_utilities_pub.ERROR_TBL_TYPE;
24 l_status VARCHAR2(10000);
25
26 l_custom_rec BIS_QUERY_ATTRIBUTES;
27 l_period_type VARCHAR2(30);
28
29 BEGIN
30
31 FOR i IN 1..p_param.COUNT
32 LOOP
33 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
34 THEN l_inv_org := p_param(i).parameter_value;
35 END IF;
36
37 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_INV_CAT')
38 THEN l_inv_cat := p_param(i).parameter_value;
39 END IF;
40
41 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG')
42 THEN l_item := p_param(i).parameter_value;
43 END IF;
44
45 IF(p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS')
46 THEN l_customer := p_param(i).parameter_value;
47 END IF;
48
49 IF(p_param(i).parameter_name = 'PERIOD_TYPE')
50 THEN l_period_type := p_param(i).parameter_value;
51 END IF;
52
53 END LOOP;
54
55 IF(l_inv_org IS NULL OR l_inv_org = 'All')
56 THEN l_inv_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 l_inv_org_where := '
73 AND f.inv_org_id = &ORGANIZATION+ORGANIZATION';
74 END IF;
75
76 IF (l_customer IS NULL OR l_customer = 'All')
77 THEN l_customer_where :='';
78 l_customer_flag := 1; -- do not need customer id
79 ELSE l_customer_where :='
80 AND f.customer_id IN (&CUSTOMER+FII_CUSTOMERS)';
81 l_customer_flag := 0; -- customer level
82 END IF;
83
84 IF (l_inv_cat IS NULL OR l_inv_cat = 'All')
85 THEN l_inv_cat_where := '';
86 ELSE l_inv_cat_where := '
87 AND f.item_category_id IN (&ITEM+ENI_ITEM_INV_CAT)';
88 END IF;
89
90 IF (l_item IS NULL OR l_item = 'All')
91 THEN l_item_where := '';
92 ELSE l_item_where := '
93 AND f.item_id IN (&ITEM+ENI_ITEM_ORG)';
94 END IF;
95
96 IF((l_inv_cat IS NULL OR l_inv_cat = 'All') AND (l_item IS NULL OR l_item = 'All'))
97 THEN l_item_cat_flag := 3; -- no grouping on item dimension
98 ELSE
99 IF (l_item IS NULL OR l_item = 'All')
100 THEN l_item_cat_flag := 1; -- inventory category
101 ELSE l_item_cat_flag := 0; -- item
102 END IF;
103 END IF;
104
105 CASE
106 WHEN (l_item_cat_flag = 0 and l_customer_flag = 0) THEN l_agg_level := 0;
107 WHEN (l_item_cat_flag = 1 and l_customer_flag = 0) THEN l_agg_level := 4;
108 WHEN (l_item_cat_flag = 3 and l_customer_flag = 0) THEN l_agg_level := 2;
109 WHEN (l_item_cat_flag = 0 and l_customer_flag = 1) THEN l_agg_level := 1;
110 WHEN (l_item_cat_flag = 1 and l_customer_flag = 1) THEN l_agg_level := 5;
111 WHEN (l_item_cat_flag = 3 and l_customer_flag = 1) THEN l_agg_level := 3;
112 END CASE;
113
114 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
115 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
116
117 -- Retrieve record to get bucket labels
118 bis_bucket_pub.RETRIEVE_BIS_BUCKET('ISC_DBI_DAYS_SHIP_AGING', l_bucket_rec, l_status, l_error_tbl);
119
120 -- Returns a single row containing the current and previous line counts for each bucket
121
122 IF(l_period_type = 'FII_TIME_DAY')
123 THEN
124 l_row_line_cnts :='
125 (SELECT sum(decode(to_date(f.time_id,''j''), &BIS_CURRENT_ASOF_DATE,
126 f.bucket1_line_cnt, 0)) CURR1,
127 sum(decode(to_date(f.time_id,''j''), &BIS_CURRENT_ASOF_DATE,
128 f.bucket2_line_cnt, 0)) CURR2,
129 sum(decode(to_date(f.time_id,''j''), &BIS_CURRENT_ASOF_DATE,
130 f.bucket3_line_cnt, 0)) CURR3,
131 sum(decode(to_date(f.time_id,''j''), &BIS_CURRENT_ASOF_DATE,
132 f.bucket4_line_cnt, 0)) CURR4,
133 sum(decode(to_date(f.time_id,''j''), &BIS_CURRENT_ASOF_DATE,
134 f.bucket5_line_cnt, 0)) CURR5,
135 sum(decode(to_date(f.time_id,''j''), &BIS_CURRENT_ASOF_DATE,
136 f.bucket6_line_cnt, 0)) CURR6,
137 sum(decode(to_date(f.time_id,''j''), &BIS_CURRENT_ASOF_DATE,
138 f.bucket7_line_cnt, 0)) CURR7,
139 sum(decode(to_date(f.time_id,''j''), &BIS_CURRENT_ASOF_DATE,
140 f.bucket8_line_cnt, 0)) CURR8,
141 sum(decode(to_date(f.time_id,''j''), &BIS_CURRENT_ASOF_DATE,
142 f.bucket9_line_cnt, 0)) CURR9,
143 sum(decode(to_date(f.time_id,''j''), &BIS_CURRENT_ASOF_DATE,
144 f.bucket10_line_cnt, 0)) CURR10,
145 sum(decode(to_date(f.time_id,''j''), &BIS_PREVIOUS_ASOF_DATE,
146 f.bucket1_line_cnt, 0)) PREV1,
147 sum(decode(to_date(f.time_id,''j''), &BIS_PREVIOUS_ASOF_DATE,
148 f.bucket2_line_cnt, 0)) PREV2,
149 sum(decode(to_date(f.time_id,''j''), &BIS_PREVIOUS_ASOF_DATE,
150 f.bucket3_line_cnt, 0)) PREV3,
151 sum(decode(to_date(f.time_id,''j''), &BIS_PREVIOUS_ASOF_DATE,
152 f.bucket4_line_cnt, 0)) PREV4,
153 sum(decode(to_date(f.time_id,''j''), &BIS_PREVIOUS_ASOF_DATE,
154 f.bucket5_line_cnt, 0)) PREV5,
155 sum(decode(to_date(f.time_id,''j''), &BIS_PREVIOUS_ASOF_DATE,
156 f.bucket6_line_cnt, 0)) PREV6,
157 sum(decode(to_date(f.time_id,''j''), &BIS_PREVIOUS_ASOF_DATE,
158 f.bucket7_line_cnt, 0)) PREV7,
159 sum(decode(to_date(f.time_id,''j''), &BIS_PREVIOUS_ASOF_DATE,
160 f.bucket8_line_cnt, 0)) PREV8,
161 sum(decode(to_date(f.time_id,''j''), &BIS_PREVIOUS_ASOF_DATE,
162 f.bucket9_line_cnt, 0)) PREV9,
163 sum(decode(to_date(f.time_id,''j''), &BIS_PREVIOUS_ASOF_DATE,
164 f.bucket10_line_cnt, 0)) PREV10
165 FROM ISC_DBI_FM_0000_MV f
166 WHERE f.time_id in
167 (to_char(&BIS_CURRENT_ASOF_DATE,''j''),to_char(&BIS_PREVIOUS_ASOF_DATE,''j''))
168 AND f.period_type_id = 1
169 AND f.agg_level = :ISC_AGG_LEVEL';
170 ELSE
171 l_row_line_cnts :='
172 (SELECT sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
173 f.bucket1_line_cnt, 0)) CURR1,
174 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
175 f.bucket2_line_cnt, 0)) CURR2,
176 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
177 f.bucket3_line_cnt, 0)) CURR3,
178 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
179 f.bucket4_line_cnt, 0)) CURR4,
180 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
181 f.bucket5_line_cnt, 0)) CURR5,
182 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
183 f.bucket6_line_cnt, 0)) CURR6,
184 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
185 f.bucket7_line_cnt, 0)) CURR7,
186 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
187 f.bucket8_line_cnt, 0)) CURR8,
188 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
189 f.bucket9_line_cnt, 0)) CURR9,
190 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
191 f.bucket10_line_cnt, 0)) CURR10,
192 sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
193 f.bucket1_line_cnt, 0)) PREV1,
194 sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
195 f.bucket2_line_cnt, 0)) PREV2,
196 sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
197 f.bucket3_line_cnt, 0)) PREV3,
198 sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
199 f.bucket4_line_cnt, 0)) PREV4,
200 sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
201 f.bucket5_line_cnt, 0)) PREV5,
202 sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
203 f.bucket6_line_cnt, 0)) PREV6,
204 sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
205 f.bucket7_line_cnt, 0)) PREV7,
206 sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
207 f.bucket8_line_cnt, 0)) PREV8,
208 sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
209 f.bucket9_line_cnt, 0)) PREV9,
210 sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
211 f.bucket10_line_cnt, 0)) PREV10
212 FROM ISC_DBI_FM_0000_MV f,
213 FII_TIME_RPT_STRUCT_V cal
214 WHERE f.time_id = cal.time_id
215 AND f.agg_level = :ISC_AGG_LEVEL
216 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
217 AND cal.period_type_id = f.period_type_id
218 AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id';
219 END IF;
220
221 l_row_line_cnts := l_row_line_cnts||l_inv_org_where
222 ||l_inv_cat_where
223 ||l_item_where
224 ||l_customer_where||'),';
225
226 -- Construction of the SQL statement here
227
228 l_stmt := '
229 SELECT bucket ISC_ATTRIBUTE_2,
230 bucket_type ISC_ATTRIBUTE_3,
231 sum(c.prev_shipped_line_cnt) ISC_MEASURE_2, -- Lines Shipped - prior
232 sum(c.curr_shipped_line_cnt) ISC_MEASURE_1, -- Lines Shipped
233 (sum(c.curr_shipped_line_cnt) - sum(c.prev_shipped_line_cnt))
234 / decode( sum(c.prev_shipped_line_cnt),0,
235 NULL,
236 abs(sum(c.prev_shipped_line_cnt))) * 100
237 ISC_MEASURE_3, -- Lines Shipped Change
238 sum(c.curr_shipped_line_cnt)
239 / decode ( sum(sum(c.curr_shipped_line_cnt)) over (), 0,
240 NULL,
241 sum(sum(c.curr_shipped_line_cnt)) over ()) * 100
242 ISC_MEASURE_4, -- Percent of Total
243 sum(sum(c.curr_shipped_line_cnt)) over ()
244 ISC_MEASURE_5, -- Gd Total for Lines Shipped
245 (sum(sum(c.curr_shipped_line_cnt)) over ()
246 - sum(sum(c.prev_shipped_line_cnt)) over ())
247 / decode( sum(sum(c.prev_shipped_line_cnt)) over (),0,
248 NULL,
249 abs(sum(sum(c.prev_shipped_line_cnt)) over())) * 100
250 ISC_MEASURE_6, -- Gd Total for Change
251 sum(sum(c.curr_shipped_line_cnt)) over ()
252 / decode ( sum(sum(c.curr_shipped_line_cnt)) over (), 0,
253 NULL,
254 sum(sum(c.curr_shipped_line_cnt)) over ()) * 100
255 ISC_MEASURE_7 -- Gd Total for Percent of Total
256 FROM (SELECT decode(rownum,
257 1, :ISC_R1,
258 2, :ISC_R2,
259 3, :ISC_R3,
260 4, :ISC_R4,
261 5, :ISC_R5,
262 6, :ISC_R6,
263 7, :ISC_R7,
264 8, :ISC_R8,
265 9, :ISC_R9,
266 10, :ISC_R10,
267 null) BUCKET,
271 2, CURR2,
268 rownum BUCKET_TYPE,
269 decode(rownum,
270 1, CURR1,
272 3, CURR3,
273 4, CURR4,
274 5, CURR5,
275 6, CURR6,
276 7, CURR7,
277 8, CURR8,
278 9, CURR9,
279 10, CURR10,
280 null) CURR_SHIPPED_LINE_CNT,
281 decode(rownum,
282 1, PREV1,
283 2, PREV2,
284 3, PREV3,
285 4, PREV4,
286 5, PREV5,
287 6, PREV6,
288 7, PREV7,
289 8, PREV8,
290 9, PREV9,
291 10, PREV10,
292 null) PREV_SHIPPED_LINE_CNT
293 FROM'
294 ||l_row_line_cnts||'
295 (SELECT 1 FROM DUAL -- dummy table with 10 rows
296 UNION ALL SELECT 1 FROM DUAL
297 UNION ALL SELECT 1 FROM DUAL
298 UNION ALL SELECT 1 FROM DUAL
299 UNION ALL SELECT 1 FROM DUAL
300 UNION ALL SELECT 1 FROM DUAL
301 UNION ALL SELECT 1 FROM DUAL
302 UNION ALL SELECT 1 FROM DUAL
303 UNION ALL SELECT 1 FROM DUAL
304 UNION ALL SELECT 1 FROM DUAL)
305 ) c
306 WHERE BUCKET IS NOT NULL
307 GROUP BY BUCKET_TYPE,BUCKET
308 ORDER BY BUCKET_TYPE';
309
310 x_custom_sql := l_stmt;
311
312 l_custom_rec.attribute_name := ':ISC_AGG_LEVEL';
313 l_custom_rec.attribute_value := to_char(l_agg_level);
314 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
315 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
316 x_custom_output.extend;
317 x_custom_output(1) := l_custom_rec;
318
319 l_custom_rec.attribute_name := ':ISC_R1';
320 l_custom_rec.attribute_value := l_bucket_rec.range1_name;
321 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
322 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
323 x_custom_output.extend;
324 x_custom_output(2) := l_custom_rec;
325
326 l_custom_rec.attribute_name := ':ISC_R2';
327 l_custom_rec.attribute_value := l_bucket_rec.range2_name;
328 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
329 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
330 x_custom_output.extend;
331 x_custom_output(3) := l_custom_rec;
332
333 l_custom_rec.attribute_name := ':ISC_R3';
337 x_custom_output.extend;
334 l_custom_rec.attribute_value := l_bucket_rec.range3_name;
335 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
336 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
338 x_custom_output(4) := l_custom_rec;
339
340 l_custom_rec.attribute_name := ':ISC_R4';
341 l_custom_rec.attribute_value := l_bucket_rec.range4_name;
342 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
343 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
344 x_custom_output.extend;
345 x_custom_output(5) := l_custom_rec;
346
347 l_custom_rec.attribute_name := ':ISC_R5';
348 l_custom_rec.attribute_value := l_bucket_rec.range5_name;
349 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
350 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
351 x_custom_output.extend;
352 x_custom_output(6) := l_custom_rec;
353
354 l_custom_rec.attribute_name := ':ISC_R6';
355 l_custom_rec.attribute_value := l_bucket_rec.range6_name;
356 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
357 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
358 x_custom_output.extend;
359 x_custom_output(7) := l_custom_rec;
360
361 l_custom_rec.attribute_name := ':ISC_R7';
362 l_custom_rec.attribute_value := l_bucket_rec.range7_name;
363 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
364 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
365 x_custom_output.extend;
366 x_custom_output(8) := l_custom_rec;
367
368 l_custom_rec.attribute_name := ':ISC_R8';
372 x_custom_output.extend;
369 l_custom_rec.attribute_value := l_bucket_rec.range8_name;
370 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
371 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
373 x_custom_output(9) := l_custom_rec;
374
375 l_custom_rec.attribute_name := ':ISC_R9';
376 l_custom_rec.attribute_value := l_bucket_rec.range9_name;
377 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
378 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
379 x_custom_output.extend;
380 x_custom_output(10) := l_custom_rec;
381
382 l_custom_rec.attribute_name := ':ISC_R10';
383 l_custom_rec.attribute_value := l_bucket_rec.range10_name;
384 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
385 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
386 x_custom_output.extend;
387 x_custom_output(11) := l_custom_rec;
388
389 END Get_Sql;
390
391 END ISC_DBI_DAYS_SHIP_AGING_PKG ;
392