[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_SHIP_OT_TREND_PKG
Source
1 PACKAGE BODY ISC_DBI_SHIP_OT_TREND_PKG AS
2 /* $Header: ISCRG92B.pls 120.0 2005/05/25 17:21:57 appldev 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
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
51 IF ( l_org IS NULL OR l_org = 'All' ) THEN
52 l_org_where := 'WHERE (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 = inv_org_id)
58 OR EXISTS
59 (SELECT 1
60 FROM mtl_parameters org
61 WHERE org.organization_id = 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 := 'WHERE inv_org_id = &ORGANIZATION+ORGANIZATION';
69 END IF;
70
71 IF ( l_inv_cat IS NULL OR l_inv_cat = 'All' ) THEN
72 l_inv_cat_where :='';
73 ELSE
74 l_inv_cat_where := '
75 AND fact.item_category_id in (&ITEM+ENI_ITEM_INV_CAT)';
76 END IF;
77
78 IF ( l_item IS NULL OR l_item = 'All' )
79 THEN l_item_where := '';
80 ELSE l_item_where := '
81 AND fact.item_id in (&ITEM+ENI_ITEM_ORG)';
82 END IF;
83
84 IF((l_inv_cat IS NULL OR l_inv_cat = 'All' ) AND ( l_item IS NULL OR l_item = 'All'))
85 THEN l_item_cat_flag := 3; -- no grouping on item dimension
86
87 ELSE
88 IF (l_item IS NULL OR l_item = 'All')
89 THEN l_item_cat_flag := 1; -- inv, category
90 ELSE l_item_cat_flag := 0; -- item is needed
91 END IF;
92 END IF;
93
94 IF (l_cust IS NULL OR l_cust = 'All') THEN
95 l_cust_where:='';
96 l_cust_flag := 1; -- do not need customer id
97 ELSE
98 l_cust_where :='
99 AND fact.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
100 l_cust_flag := 0; -- customer level
101 END IF;
102
103 CASE
104 WHEN (l_item_cat_flag = 0 and l_cust_flag = 0) THEN l_agg_level := 0;
105 WHEN (l_item_cat_flag = 1 and l_cust_flag = 0) THEN l_agg_level := 4;
106 WHEN (l_item_cat_flag = 3 and l_cust_flag = 0) THEN l_agg_level := 2;
107 WHEN (l_item_cat_flag = 0 and l_cust_flag = 1) THEN l_agg_level := 1;
108 WHEN (l_item_cat_flag = 1 and l_cust_flag = 1) THEN l_agg_level := 5;
109 WHEN (l_item_cat_flag = 3 and l_cust_flag = 1) THEN l_agg_level := 3;
110 END CASE;
111
112 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
113 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
114
115 l_SQLText := 'SELECT fii.name VIEWBY,
116 nvl(sum(s.early_line_cnt),0) ISC_MEASURE_1,
117 nvl(sum(s.early_line_cnt),0)/
118 decode(sum(s.shipped_line_cnt),0,null,
119 sum(s.shipped_line_cnt))*100 ISC_MEASURE_2,
120 nvl(sum(s.on_time_line_cnt),0) ISC_MEASURE_3,
121 nvl(sum(s.on_time_line_cnt),0)/
122 decode(sum(s.shipped_line_cnt),0,null,
123 sum(s.shipped_line_cnt))*100 ISC_MEASURE_4,
124 nvl(sum(s.late_line_cnt),0) ISC_MEASURE_5,
125 nvl(sum(s.early_line_cnt),0)/
126 decode(sum(s.shipped_line_cnt),0,null,
127 sum(s.shipped_line_cnt))*100 ISC_MEASURE_10,--duplicate row for graph
128 nvl(sum(s.on_time_line_cnt),0)/
129 decode(sum(s.shipped_line_cnt),0,null,
130 sum(s.shipped_line_cnt))*100 ISC_MEASURE_11, --duplicate row for graph
131 nvl(sum(s.late_line_cnt),0)/
132 decode(sum(s.shipped_line_cnt),0,null,
133 sum(s.shipped_line_cnt))*100 ISC_MEASURE_6,
134 nvl(sum(s.shipped_line_cnt),0) ISC_MEASURE_7,
135 nvl(sum(s.scheduled_line_cnt),0) ISC_MEASURE_8,
136 nvl(sum(s.shipped_line_cnt),0)/
137 decode(sum(scheduled_line_cnt),0,null,
138 sum(scheduled_line_cnt))*100 ISC_MEASURE_9
139 FROM (SELECT start_date,early_line_cnt,on_time_line_cnt,
140 late_line_cnt,shipped_line_cnt,scheduled_line_cnt
141 FROM (SELECT dates.start_date START_DATE,
142 fact.early_line_cnt EARLY_LINE_CNT,
143 fact.on_time_line_cnt ON_TIME_LINE_CNT,
144 fact.late_line_cnt LATE_LINE_CNT,
145 fact.shipped_line_cnt SHIPPED_LINE_CNT,
146 0 SCHEDULED_LINE_CNT,
147 fact.inv_org_id INV_ORG_ID
148 FROM (SELECT fii.start_date START_DATE,
149 least(fii.end_date, &BIS_CURRENT_ASOF_DATE) CURR_DAY
150 FROM '||l_period_type||' fii
151 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
152 AND &BIS_CURRENT_ASOF_DATE
153 ORDER BY fii.start_date DESC) dates,
154 ISC_DBI_FM_0000_MV fact,
155 FII_TIME_RPT_STRUCT_V cal
156 WHERE fact.agg_level = :ISC_AGG_LEVEL
157 AND cal.report_date IN (dates.curr_day)
158 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) = cal.record_type_id
159 AND fact.time_id = cal.time_id
160 AND fact.period_type_id = cal.period_type_id'
161 ||l_inv_cat_where||l_item_where||l_cust_where||'
162 UNION ALL
163 SELECT dates.start_date START_DATE,
164 0 EARLY_LINE_CNT,
165 0 ON_TIME_LINE_CNT,
166 0 LATE_LINE_CNT,
167 0 SHIPPED_LINE_CNT,
168 schedule_line_cnt SCHEDULED_LINE_CNT,
169 fact.inv_org_id INV_ORG_ID
170 FROM (SELECT fii.start_date START_DATE,
171 least(fii.end_date,&BIS_CURRENT_ASOF_DATE) CURR_DAY
172 FROM '||l_period_type||' fii
173 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
174 AND &BIS_CURRENT_ASOF_DATE
175 ORDER BY fii.start_date DESC) dates,
176 ISC_DBI_FM_0001_MV fact,
177 FII_TIME_RPT_STRUCT_V cal
178 WHERE fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
179 AND fact.customer_flag = :ISC_CUST_FLAG
180 AND cal.report_date = dates.curr_day
181 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) = cal.record_type_id
182 AND fact.time_id = cal.time_id
183 AND fact.period_type_id = cal.period_type_id'
184 ||l_inv_cat_where||l_item_where||l_cust_where||')
185 '||l_org_where||')s,
186 '|| l_period_type ||' fii
187 WHERE fii.start_date = s.start_date(+)
188 AND fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
189 AND &BIS_CURRENT_ASOF_DATE
190 GROUP BY fii.name,fii.start_date
191 ORDER BY fii.start_date';
192
193 x_custom_sql := l_SQLText;
194
195 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
196 l_custom_rec.attribute_value := 'TIME+'||l_period_type;
197 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
198 x_custom_output.EXTEND;
199 x_custom_output(1) := l_custom_rec;
200
201 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
202 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
203 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
204 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
205 x_custom_output.EXTEND;
206 x_custom_output(2) := l_custom_rec;
207
208 l_custom_rec.attribute_name := ':ISC_CUST_FLAG';
209 l_custom_rec.attribute_value := to_char(l_cust_flag);
210 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
211 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
212 x_custom_output.EXTEND;
213 x_custom_output(3) := l_custom_rec;
214
215 l_custom_rec.attribute_name := ':ISC_AGG_LEVEL';
216 l_custom_rec.attribute_value := to_char(l_agg_level);
217 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
218 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
219 x_custom_output.EXTEND;
220 x_custom_output(4) := l_custom_rec;
221
222
223 END GET_SQL;
224
225 END ISC_DBI_SHIP_OT_TREND_PKG;
226