[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_PLAN_OT_SHIP_PKG
Source
1 PACKAGE BODY ISC_DBI_PLAN_OT_SHIP_PKG AS
2 /* $Header: ISCRGASB.pls 120.1 2006/06/26 06:35:58 abhdixi noship $ */
3
4
5 PROCEDURE GET_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,x_custom_sql OUT NOCOPY VARCHAR2,
6 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
7
8 l_stmt VARCHAR2(32000);
9 l_plan VARCHAR2(10000);
10 l_plan2 VARCHAR2(10000);
11 l_inner_sql VARCHAR2(32000);
12 l_view_by VARCHAR2(32000);
13 l_org VARCHAR2(32000);
14 l_org_where VARCHAR2(32000);
15 l_period_type VARCHAR2(1000);
16 l_inv_cat VARCHAR2(32000);
17 l_item VARCHAR2(32000);
18 l_inv_cat_where VARCHAR2(32000);
19 l_item_where VARCHAR2(32000);
20 l_item_cat_flag NUMBER; -- 0 for item, 1 for inv.cat, 3 for all
21 l_time_from DATE;
22 l_period_type_id NUMBER;
23 l_lang varchar2(10);
24 l_viewby_id varchar2(100);
25 l_custom_rec BIS_QUERY_ATTRIBUTES ;
26
27
28
29 BEGIN
30
31 FOR i IN 1..p_param.COUNT
32 LOOP
33 IF (p_param(i).parameter_name = 'PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT')
34 THEN l_plan := p_param(i).parameter_value;
35 END IF;
36
37 IF (p_param(i).parameter_name = 'PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2')
38 THEN l_plan2 := p_param(i).parameter_value;
39 END IF;
40
41 IF( p_param(i).parameter_name= 'VIEW_BY')
42 THEN l_view_by := p_param(i).parameter_value;
43 END IF;
44
45 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
46 THEN l_org := p_param(i).parameter_value;
47 END IF;
48
49 IF(p_param(i).parameter_name = 'PERIOD_TYPE') THEN
50 l_period_type := p_param(i).parameter_value;
51 END IF;
52
53 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_INV_CAT') THEN
54 l_inv_cat := p_param(i).parameter_value;
55 END IF;
56
57 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG') THEN
58 l_item := p_param(i).parameter_value;
59 END IF;
60
61 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM') THEN
62 l_time_from := p_param(i).period_date;
63 END IF;
64
65 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM') THEN
66 l_time_from := p_param(i).period_date;
67 END IF;
68
69 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM') THEN
70 l_time_from := p_param(i).period_date;
71 END IF;
72
73
74 END LOOP;
75
76
77 IF ( l_org IS NULL OR l_org = 'All' ) THEN
78 l_org_where := '
79 AND (EXISTS
80 (SELECT 1
81 FROM org_access o
82 WHERE o.responsibility_id = fnd_global.resp_id
83 AND o.resp_application_id = fnd_global.resp_appl_id
84 AND o.organization_id = f.organization_id)
85 OR EXISTS
86 (SELECT 1
87 FROM mtl_parameters org
88 WHERE org.organization_id = f.organization_id
89 AND NOT EXISTS
90 (SELECT 1
91 FROM org_access ora
92 WHERE org.organization_id = ora.organization_id)))';
93
94 ELSE
95 l_org_where := '
96 AND f.organization_id =(&ORGANIZATION+ORGANIZATION)';
97 END IF;
98
99
100 IF ( l_inv_cat IS NULL OR l_inv_cat = 'All' ) THEN
101 l_inv_cat_where :='';
102 ELSE
103 l_inv_cat_where := '
104 AND f.inv_category_id in (&ITEM+ENI_ITEM_INV_CAT)';
105 END IF;
106
107
108 IF ( l_item IS NULL OR l_item = 'All' )
109 THEN l_item_where := '';
110 ELSE l_item_where := '
111 AND f.item_id in (&ITEM+ENI_ITEM_ORG)';
112 END IF;
113
114 IF (l_item IS NULL OR l_item = 'All')
115 THEN
116 IF (l_view_by = 'ITEM+ENI_ITEM_ORG')
117 THEN l_item_cat_flag := 0; -- item
118 ELSIF (l_view_by = 'ITEM+ENI_ITEM_INV_CAT')
119 THEN l_item_cat_flag := 1; -- category
120 ELSE
121 IF (l_inv_cat IS NULL OR l_inv_cat = 'All')
122 THEN l_item_cat_flag := 3; -- all
123 ELSE l_item_cat_flag := 1; -- category
124 END IF;
125 END IF;
126 ELSE
127 l_item_cat_flag := 0; -- item
128 END IF;
129
130
131
132
133 l_lang := USERENV('LANG');
134
135 IF l_view_by = 'ORGANIZATION+ORGANIZATION' THEN
136 l_viewby_id :='organization_id';
137
138 ELSIF l_view_by = 'ITEM+ENI_ITEM_ORG' THEN
139 l_viewby_id :='item_id';
140
141 ELSIF l_view_by = 'ITEM+ENI_ITEM_INV_CAT' THEN
142 l_viewby_id :='inv_category_id';
143
144 END if;
145
146
147 IF l_period_type = 'FII_TIME_ENT_YEAR' THEN
148 l_period_type_id := 128;
149
150 ELSIF (l_period_type='FII_TIME_ENT_QTR') THEN
151 l_period_type_id := 64;
152
153 ELSE
154 l_period_type_id := 32;
155
156 END IF;
157
158
159 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
160 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
161
162
163 IF (l_plan IS NULL OR l_plan2 IS NULL)
164 THEN l_stmt := '
165 SELECT 0 VIEWBY,
166 0 VIEWBYID,
167 0 ISC_ATTRIBUTE_1,
168 0 ISC_MEASURE_7,
169 0 ISC_MEASURE_8,
170 0 ISC_MEASURE_1,
171 0 ISC_MEASURE_2,
172 0 ISC_MEASURE_3,
173 0 ISC_MEASURE_4,
174 0 ISC_MEASURE_5,
175 0 ISC_MEASURE_6,
176 0 ISC_MEASURE_9,
177 0 ISC_MEASURE_10
178 FROM dual
179 WHERE 1 = 2 /* PLAN_SNAPSHOT dimension has not been populated */';
180 ELSE
181
182 l_inner_sql:='ISC_MEASURE_7,ISC_MEASURE_8,ISC_MEASURE_1,ISC_MEASURE_2,ISC_MEASURE_3,
183 ISC_MEASURE_4,ISC_MEASURE_5,
184 ISC_MEASURE_4-ISC_MEASURE_5 ISC_MEASURE_6,
185 ISC_MEASURE_9,ISC_MEASURE_10
186 FROM(select (rank() over (&ORDER_BY_CLAUSE nulls last,viewby_id))-1 rnk,
187 viewby_id,
188 ISC_MEASURE_1,ISC_MEASURE_2,ISC_MEASURE_3,
189 sum(ISC_MEASURE_7) over ()/decode(sum(ISC_MEASURE_8) over(),0,null,
190 sum(ISC_MEASURE_8) over())*100 ISC_MEASURE_4,
191 sum(comp_ontime_total) over ()/decode(sum(comp_total_lines) over(),0,null,
192 sum(comp_total_lines) over())*100 ISC_MEASURE_5,
193 ISC_MEASURE_7, ISC_MEASURE_8,
194 sum(ISC_MEASURE_7) over () ISC_MEASURE_9,
195 sum(ISC_MEASURE_8) over () ISC_MEASURE_10
196 FROM(select s.viewby_id,
197 sum(s.plan_ontime_lines)/decode(sum(s.plan_total_lines),0,null,
198 sum(s.plan_total_lines))*100 ISC_MEASURE_1,
199 sum(s.comp_ontime_lines)/decode(sum(s.comp_total_lines),0,null,
200 sum(s.comp_total_lines))*100 ISC_MEASURE_2,
201 (sum(s.plan_ontime_lines)/decode(sum(s.plan_total_lines),0,null,
202 sum(s.plan_total_lines)))*100-
203 (sum(s.comp_ontime_lines)/decode(sum(s.comp_total_lines),0,null,
204 sum(s.comp_total_lines)))*100 ISC_MEASURE_3,
205 sum(s.plan_ontime_lines) ISC_MEASURE_7,
206 sum(s.plan_total_lines) ISC_MEASURE_8,
207 sum(s.comp_ontime_lines) comp_ontime_total,
208 sum(s.comp_total_lines) comp_total_lines
209 FROM
210 (SELECT f.'||l_viewby_id||' VIEWBY_ID,
211 decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
212 sum(f.total_lines)-sum(f.late_lines),null) plan_ontime_lines,
213 decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
214 sum(f.total_lines),null) plan_total_lines,
215 decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
216 sum(f.total_lines)-sum(f.late_lines),null) comp_ontime_lines,
217 decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
218 sum(f.total_lines),null) comp_total_lines
219 FROM
220 ISC_DBI_PM_0001_MV f
221 WHERE f.start_date = :ISC_CUR_START
222 AND f.period_type_id = :ISC_PERIOD_TYPE_ID
223 AND f.union2_flag <> 0
224 AND f.item_cat_flag = :ISC_ITEM_CAT_FLAG
225 AND f.snapshot_id in (&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2)
226 '||l_org_where||l_inv_cat_where||l_item_where||
227 'GROUP BY f.'||l_viewby_id||',f.snapshot_id) s
228 GROUP BY s.viewby_id)
229 WHERE (ISC_MEASURE_7 <>0 OR ISC_MEASURE_8 <>0)
230 OR (comp_ontime_total <>0 OR comp_total_lines <>0)) a,';
231
232 IF l_view_by = 'ORGANIZATION+ORGANIZATION' THEN
233 l_stmt := 'SELECT org.name VIEWBY,
234 org.organization_id VIEWBYID,
235 null ISC_ATTRIBUTE_1,
236 '||l_inner_sql||'
237 HR_ALL_ORGANIZATION_UNITS_TL org
238 WHERE org.organization_id = a.viewby_id
239 AND org.language = :ISC_LANG
240 AND ((a.rnk between &START_INDEX and &END_INDEX) OR &END_INDEX=-1)
241 ORDER BY rnk';
242
243 ELSIF l_view_by = 'ITEM+ENI_ITEM_ORG' THEN
244 l_stmt :='SELECT items.value VIEWBY,
245 items.id VIEWBYID,
246 items.description ISC_ATTRIBUTE_1,
247 '||l_inner_sql||'
248 ENI_ITEM_ORG_V items
249 WHERE a.viewby_id = items.id
250 AND ((a.rnk between &START_INDEX and &END_INDEX) OR &END_INDEX=-1)
251 ORDER BY rnk';
252
253 ELSIF l_view_by = 'ITEM+ENI_ITEM_INV_CAT' THEN
254 l_stmt := 'SELECT ecat.value VIEWBY,
255 ecat.id VIEWBYID,
256 null ISC_ATTRIBUTE_1,
257 '||l_inner_sql||'
258 ENI_ITEM_INV_CAT_V ecat
259 WHERE a.viewby_id = ecat.id
260 AND ((a.rnk between &START_INDEX and &END_INDEX) OR &END_INDEX=-1)
261 ORDER BY rnk';
262 END IF;
263 END IF;
264
265 x_custom_sql := l_stmt;
266
267 l_custom_rec.attribute_name := ':ISC_PERIOD_TYPE_ID';
268 l_custom_rec.attribute_value := to_char(l_period_type_id);
269 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
270 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
271 x_custom_output.EXTEND;
272 x_custom_output(1) := l_custom_rec;
273
274 l_custom_rec.attribute_name := ':ISC_CUR_START';
275 l_custom_rec.attribute_value := to_char(l_time_from,'DD/MM/YYYY');
276 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
277 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
278 x_custom_output.EXTEND;
279 x_custom_output(2) := l_custom_rec;
280
281 l_custom_rec.attribute_name := ':ISC_LANG';
282 l_custom_rec.attribute_value := l_lang;
283 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
284 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
285 x_custom_output.EXTEND;
286 x_custom_output(3) := l_custom_rec;
287
288 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
289 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
290 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
291 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
292 x_custom_output.EXTEND;
293 x_custom_output(4) := l_custom_rec;
294
295
296 END get_sql;
297
298 END ISC_DBI_PLAN_OT_SHIP_PKG ;
299