[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_SHIP_PERF_TREND_PKG
Source
1 PACKAGE BODY ISC_DBI_SHIP_PERF_TREND_PKG AS
2 /* $Header: ISCRG66B.pls 120.2 2006/06/26 06:17:33 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
8 l_SQLText VARCHAR2(15000);
9 l_period_type VARCHAR2(30);
10 l_org VARCHAR2(100);
11 l_org_where VARCHAR2(500);
12 l_item VARCHAR2(32000);
13 l_item_where VARCHAR2(32000);
14 l_inv_cat VARCHAR2(32000);
15 l_inv_cat_where VARCHAR2(32000);
16 l_cust VARCHAR2(32000);
17 l_cust_where VARCHAR2(32000);
18 l_item_cat_flag NUMBER; -- 0 for product and 1 for product category
19 l_cust_flag NUMBER; -- 0 for customer level, 1 for no-customer level
20 l_agg_level NUMBER;
21 l_custom_rec BIS_QUERY_ATTRIBUTES ;
22 l_att_2 varchar(255);
23 BEGIN
24
25 FOR i IN 1..p_param.COUNT
26 LOOP
27
28 IF(p_param(i).parameter_name = 'PERIOD_TYPE')
29 THEN l_period_type := p_param(i).parameter_value;
30 END IF;
31
32 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
33 THEN l_org := p_param(i).parameter_value;
34 END IF;
35
36 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_INV_CAT') THEN
37 l_inv_cat := p_param(i).parameter_value;
38 END IF;
39
40 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG') THEN
41 l_item := p_param(i).parameter_value;
42 END IF;
43
44 IF(p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS') THEN
45 l_cust := p_param(i).parameter_value;
46 END IF;
47
48 END LOOP;
49
50 IF ( l_org IS NULL OR l_org = 'All' ) THEN
51 l_org_where := '
52 AND (EXISTS
53 (SELECT 1
54 FROM org_access o
55 WHERE o.responsibility_id = fnd_global.resp_id
56 AND o.resp_application_id = fnd_global.resp_appl_id
57 AND o.organization_id = fact.inv_org_id)
58 OR EXISTS
59 (SELECT 1
60 FROM mtl_parameters org
61 WHERE org.organization_id = fact.inv_org_id
62 AND NOT EXISTS
63 (SELECT 1
64 FROM org_access ora
65 WHERE org.organization_id = ora.organization_id)))';
66
67 ELSE
68 l_org_where := '
69 AND fact.inv_org_id = &ORGANIZATION+ORGANIZATION';
70 END IF;
71
72 IF ( l_inv_cat IS NULL OR l_inv_cat = 'All' ) THEN
73 l_inv_cat_where :='';
74 ELSE
75 l_inv_cat_where := '
76 AND fact.item_category_id in (&ITEM+ENI_ITEM_INV_CAT)';
77 END IF;
78
79 IF ( l_item IS NULL OR l_item = 'All' )
80 THEN l_item_where := '';
81 ELSE l_item_where := '
82 AND fact.item_id in (&ITEM+ENI_ITEM_ORG)';
83 END IF;
84
85 IF((l_inv_cat IS NULL OR l_inv_cat = 'All' ) AND ( l_item IS NULL OR l_item = 'All'))
86 THEN l_item_cat_flag := 3; -- no grouping on item dimension
87
88 ELSE
89 IF (l_item IS NULL OR l_item = 'All')
90 THEN l_item_cat_flag := 1; -- inv, category
91 ELSE l_item_cat_flag := 0; -- item is needed
92 END IF;
93 END IF;
94
95 IF (l_cust IS NULL OR l_cust = 'All') THEN
96 l_cust_where:='';
97 l_cust_flag := 1; -- do not need customer id
98 ELSE
99 l_cust_where :='
100 AND fact.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
101 l_cust_flag := 0; -- customer level
102 END IF;
103
104 CASE
105 WHEN (l_item_cat_flag = 0 and l_cust_flag = 0) THEN l_agg_level := 0;
106 WHEN (l_item_cat_flag = 1 and l_cust_flag = 0) THEN l_agg_level := 4;
107 WHEN (l_item_cat_flag = 3 and l_cust_flag = 0) THEN l_agg_level := 2;
108 WHEN (l_item_cat_flag = 0 and l_cust_flag = 1) THEN l_agg_level := 1;
109 WHEN (l_item_cat_flag = 1 and l_cust_flag = 1) THEN l_agg_level := 5;
110 WHEN (l_item_cat_flag = 3 and l_cust_flag = 1) THEN l_agg_level := 3;
111 END CASE;
112
113 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
114 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
115
116 If l_period_type = 'FII_TIME_WEEK' then
117 l_att_2 := '''AS_OF_DATE=''||'|| 'to_char(fii1.end_date,''DD/MM/YYYY'')' || '||''&pFunctionName=ISC_DBI_SHIP_PERF_TREND&TIME+FII_TIME_DAY=TIME+FII_TIME_DAY&pParameters=pParamIds@Y''';
118 else
119 l_att_2 := 'NULL ';
120 end if;
121
122 If l_period_type = 'FII_TIME_DAY' then
123 l_SQLText := 'SELECT fii1.start_date VIEWBY, '
124 || l_att_2 || ' ISC_ATTRIBUTE_2,
125 nvl(s.p_line_shipped, 0) ISC_MEASURE_2,
126 nvl(s.c_line_shipped, 0) ISC_MEASURE_1,
127 nvl(s.c_late_schedule, 0) ISC_MEASURE_3,
128 nvl(s.p_late_schedule, 0) ISC_MEASURE_4,
129 nvl(s.p_late_promise, 0) ISC_MEASURE_6,
130 nvl(s.c_late_promise, 0) ISC_MEASURE_5,
131 null CURRENCY -- obsoleted item from DBI 5.0
132 FROM (SELECT dates.start_date START_DATE,
133 sum(decode(dates.period, ''C'',
134 fact.shipped_line_cnt, 0)) C_LINE_SHIPPED,
135 sum(decode(dates.period, ''P'',
136 fact.shipped_line_cnt, 0)) P_LINE_SHIPPED,
137 sum(decode(dates.period, ''C'',
138 fact.late_line_cnt, 0)) C_LATE_SCHEDULE,
139 sum(decode(dates.period, ''P'',
140 fact.late_line_cnt, 0)) P_LATE_SCHEDULE,
141 sum(decode(dates.period, ''C'',
142 fact.late_line_promise_cnt, 0)) C_LATE_PROMISE,
143 sum(decode(dates.period, ''P'',
144 fact.late_line_promise_cnt, 0)) P_LATE_PROMISE
145 FROM (SELECT fii1.start_date START_DATE,
146 ''C'' PERIOD,
147 least(fii1.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
148 FROM '||l_period_type||' fii1
149 WHERE fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
150 AND &BIS_CURRENT_ASOF_DATE
151 UNION ALL
152 SELECT p1.start_date START_DATE,
153 ''P'' PERIOD,
154 p2.day REPORT_DATE
155 FROM (SELECT fii1.start_date START_DATE,
156 ROWNUM ID
157 FROM '||l_period_type||' fii1
158 WHERE fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
159 AND &BIS_CURRENT_ASOF_DATE
160 ORDER BY fii1.start_date DESC) p1,
161 (SELECT least(fii1.end_date, &BIS_PREVIOUS_ASOF_DATE) DAY,
162 ROWNUM ID
163 FROM '||l_period_type||' fii1
164 WHERE fii1.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
165 AND &BIS_PREVIOUS_ASOF_DATE
166 ORDER BY fii1.start_date DESC) p2
167 WHERE p1.id = p2.id(+)) dates,
168 ISC_DBI_FM_0000_MV fact
169 WHERE fact.agg_level = :ISC_AGG_LEVEL
170 AND fact.time_id = to_char(dates.report_date,''j'')
171 AND fact.period_type_id = 1 '
172 ||l_org_where||l_inv_cat_where||l_item_where||l_cust_where||'
173 GROUP BY dates.start_date) s,
174 '|| l_period_type ||' fii1
175 WHERE fii1.start_date = s.start_date(+)
176 AND fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
177 AND &BIS_CURRENT_ASOF_DATE
178 ORDER BY fii1.start_date';
179 else
180 l_SQLText := 'SELECT fii1.NAME VIEWBY, '
181 || l_att_2 || ' ISC_ATTRIBUTE_2,
182 nvl(s.p_line_shipped, 0) ISC_MEASURE_2,
183 nvl(s.c_line_shipped, 0) ISC_MEASURE_1,
184 nvl(s.c_late_schedule, 0) ISC_MEASURE_3,
185 nvl(s.p_late_schedule, 0) ISC_MEASURE_4,
186 nvl(s.p_late_promise, 0) ISC_MEASURE_6,
187 nvl(s.c_late_promise, 0) ISC_MEASURE_5,
188 null CURRENCY -- obsoleted item from DBI 5.0
189 FROM (SELECT dates.start_date START_DATE,
190 sum(decode(dates.period, ''C'',
191 fact.shipped_line_cnt, 0)) C_LINE_SHIPPED,
192 sum(decode(dates.period, ''P'',
193 fact.shipped_line_cnt, 0)) P_LINE_SHIPPED,
194 sum(decode(dates.period, ''C'',
195 fact.late_line_cnt, 0)) C_LATE_SCHEDULE,
196 sum(decode(dates.period, ''P'',
197 fact.late_line_cnt, 0)) P_LATE_SCHEDULE,
198 sum(decode(dates.period, ''C'',
199 fact.late_line_promise_cnt, 0)) C_LATE_PROMISE,
200 sum(decode(dates.period, ''P'',
201 fact.late_line_promise_cnt, 0)) P_LATE_PROMISE
202 FROM (SELECT fii1.start_date START_DATE,
203 ''C'' PERIOD,
204 least(fii1.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
205 FROM '||l_period_type||' fii1
206 WHERE fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
207 AND &BIS_CURRENT_ASOF_DATE
208 UNION ALL
209 SELECT p1.start_date START_DATE,
210 ''P'' PERIOD,
211 p2.day REPORT_DATE
212 FROM (SELECT fii1.start_date START_DATE,
213 ROWNUM ID
214 FROM '||l_period_type||' fii1
215 WHERE fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
216 AND &BIS_CURRENT_ASOF_DATE
217 ORDER BY fii1.start_date DESC) p1,
218 (SELECT least(fii1.end_date, &BIS_PREVIOUS_ASOF_DATE) DAY,
219 ROWNUM ID
220 FROM '||l_period_type||' fii1
221 WHERE fii1.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
222 AND &BIS_PREVIOUS_ASOF_DATE
223 ORDER BY fii1.start_date DESC) p2
224 WHERE p1.id = p2.id(+)) dates,
225 ISC_DBI_FM_0000_MV fact,
226 FII_TIME_RPT_STRUCT_V cal
227 WHERE fact.agg_level = :ISC_AGG_LEVEL
228 AND cal.report_date = dates.report_date
229 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) = cal.record_type_id
230 AND fact.time_id = cal.time_id
231 AND fact.period_type_id = cal.period_type_id'
232 ||l_org_where||l_inv_cat_where||l_item_where||l_cust_where||'
233 GROUP BY dates.start_date) s,
234 '|| l_period_type ||' fii1
235 WHERE fii1.start_date = s.start_date(+)
236 AND fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
237 AND &BIS_CURRENT_ASOF_DATE
238 ORDER BY fii1.start_date';
239 end if;
240
241 x_custom_sql := l_SQLText;
242
243 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
244 l_custom_rec.attribute_value := 'TIME+'||l_period_type;
245 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
246 x_custom_output.EXTEND;
247 x_custom_output(1) := l_custom_rec;
248
249 l_custom_rec.attribute_name := ':ISC_AGG_LEVEL';
250 l_custom_rec.attribute_value := to_char(l_agg_level);
251 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
252 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
253 x_custom_output.EXTEND;
254 x_custom_output(2) := l_custom_rec;
255
256
257 END GET_SQL;
258
259 END ISC_DBI_SHIP_PERF_TREND_PKG;
260