[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_PLAN_RES_UT_PKG
Source
1 PACKAGE BODY ISC_DBI_PLAN_RES_UT_PKG AS
2 /* $Header: ISCRGAUB.pls 120.1 2006/06/26 06:37:55 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_res VARCHAR2(32000);
17 l_res_group VARCHAR2(32000);
18 l_res_dept VARCHAR2(32000);
19 l_res_where VARCHAR2(32000);
20 l_res_group_where VARCHAR2(32000);
21 l_res_dept_where VARCHAR2(32000);
22 l_res_gp_flag NUMBER; -- 0 for resource, 1 for dept,2 for res.group, 3 for all
23 l_time_from DATE;
24 l_period_type_id NUMBER;
25 l_lang varchar2(10);
26 l_viewby_id varchar2(100);
27 l_custom_rec BIS_QUERY_ATTRIBUTES ;
28
29
30
31 BEGIN
32
33 FOR i IN 1..p_param.COUNT
34 LOOP
35 IF (p_param(i).parameter_name = 'PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT')
36 THEN l_plan := p_param(i).parameter_value;
37 END IF;
38
39 IF (p_param(i).parameter_name = 'PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2')
40 THEN l_plan2 := p_param(i).parameter_value;
41 END IF;
42
43 IF( p_param(i).parameter_name= 'VIEW_BY')
44 THEN l_view_by := p_param(i).parameter_value;
45 END IF;
46
47 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
48 THEN l_org := p_param(i).parameter_value;
49 END IF;
50
51 IF(p_param(i).parameter_name = 'PERIOD_TYPE') THEN
52 l_period_type := p_param(i).parameter_value;
53 END IF;
54
55 IF(p_param(i).parameter_name = 'RESOURCE+ENI_RESOURCE') THEN
56 l_res := p_param(i).parameter_value;
57 END IF;
58
59 IF(p_param(i).parameter_name = 'RESOURCE+ENI_RESOURCE_GROUP') THEN
60 l_res_group := p_param(i).parameter_value;
61 END IF;
62
63 IF(p_param(i).parameter_name = 'RESOURCE+ENI_RESOURCE_DEPARTMENT') THEN
64 l_res_dept := p_param(i).parameter_value;
65 END IF;
66
67 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM') THEN
68 l_time_from := p_param(i).period_date;
69 END IF;
70
71 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM') THEN
72 l_time_from := p_param(i).period_date;
73 END IF;
74
75 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM') THEN
76 l_time_from := p_param(i).period_date;
77 END IF;
78
79
80 END LOOP;
81
82
83 IF ( l_org IS NULL OR l_org = 'All' ) THEN
84 l_org_where := '
85 AND (EXISTS
86 (SELECT 1
87 FROM org_access o
88 WHERE o.responsibility_id = fnd_global.resp_id
89 AND o.resp_application_id = fnd_global.resp_appl_id
90 AND o.organization_id = f.organization_id)
91 OR EXISTS
92 (SELECT 1
93 FROM mtl_parameters org
94 WHERE org.organization_id = f.organization_id
95 AND NOT EXISTS
96 (SELECT 1
97 FROM org_access ora
98 WHERE org.organization_id = ora.organization_id)))';
99
100 ELSE
101 l_org_where := '
102 AND f.organization_id =(&ORGANIZATION+ORGANIZATION)';
103 END IF;
104
105
106 IF ( l_res IS NULL OR l_res = 'All' ) THEN
107 l_res_where :='';
108 ELSE
109 l_res_where := '
110 AND f.resource_id in (&RESOURCE+ENI_RESOURCE)';
111 END IF;
112
113
114 IF ( l_res_group IS NULL OR l_res_group = 'All' )
115 THEN l_res_group_where := '';
116 ELSE l_res_group_where := '
117 AND f.resource_group_id in (&RESOURCE+ENI_RESOURCE_GROUP)';
118 END IF;
119
120 IF ( l_res_dept IS NULL OR l_res_dept = 'All' )
121 THEN l_res_dept_where := '';
122 ELSE l_res_dept_where := '
123 AND f.department_id in (&RESOURCE+ENI_RESOURCE_DEPARTMENT)';
124 END IF;
125
126
127 IF (l_view_by = 'RESOURCE+ENI_RESOURCE')
128 THEN l_res_gp_flag := 0; -- resource
129
130 ELSIF (l_view_by = 'RESOURCE+ENI_RESOURCE_DEPARTMENT') THEN
131 IF ((l_res IS NULL OR l_res = 'All') AND (l_res_group IS NULL OR l_res_group='All'))
132 THEN l_res_gp_flag := 1; -- dept
133 ELSE
134 l_res_gp_flag :=0;
135 END IF;
136
137 ELSIF (l_view_by = 'RESOURCE+ENI_RESOURCE_GROUP') THEN
138 IF ((l_res IS NULL OR l_res = 'All') AND (l_res_dept IS NULL OR l_res_dept='All'))
139 THEN l_res_gp_flag := 2; -- res.group
140 ELSE
141 l_res_gp_flag :=0;
142 END IF;
143
144 ELSE -- view by org
145 IF ((l_res IS NULL OR l_res = 'All') AND (l_res_dept IS NULL OR l_res_dept='All')
146 AND (l_res_group IS NULL OR l_res_group='All'))
147 THEN l_res_gp_flag := 3; -- all
148
149 ELSIF((l_res IS NULL OR l_res = 'All') AND (l_res_dept IS NULL OR l_res_dept='All'))
150 THEN l_res_gp_flag := 2; -- res.group
151
152 ELSIF((l_res IS NULL OR l_res = 'All') AND (l_res_group IS NULL OR l_res_group='All'))
153 THEN l_res_gp_flag :=1; --dept
154
155 ELSE
156 l_res_gp_flag :=0; --resource
157 END IF;
158
159 END IF;
160
161
162 l_lang := USERENV('LANG');
163
164 IF l_view_by = 'ORGANIZATION+ORGANIZATION' THEN
165 l_viewby_id :='organization_id';
166
167 ELSIF l_view_by = 'RESOURCE+ENI_RESOURCE_GROUP' THEN
168 l_viewby_id :='resource_group_id';
169
170 ELSIF l_view_by = 'RESOURCE+ENI_RESOURCE_DEPARTMENT' THEN
171 l_viewby_id :='department_id';
172
173 ELSIF l_view_by = 'RESOURCE+ENI_RESOURCE' THEN
174 l_viewby_id :='resource_id';
175
176 END if;
177
178
179 IF l_period_type = 'FII_TIME_ENT_YEAR' THEN
180 l_period_type_id := 128;
181
182 ELSIF (l_period_type='FII_TIME_ENT_QTR') THEN
183 l_period_type_id := 64;
184
185 ELSE
186 l_period_type_id := 32;
187
188 END IF;
189
190
191 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
192 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
193
194 IF (l_plan IS NULL OR l_plan2 IS NULL)
195 THEN l_stmt := '
196 SELECT 0 VIEWBY,
197 0 VIEWBYID,
198 0 ISC_MEASURE_7,
199 0 ISC_MEASURE_8,
200 0 ISC_MEASURE_1,
201 0 ISC_MEASURE_2,
202 0 ISC_MEASURE_3,
203 0 ISC_MEASURE_4,
204 0 ISC_MEASURE_5,
205 0 ISC_MEASURE_6,
206 0 ISC_MEASURE_9,
207 0 ISC_MEASURE_10
208 FROM dual
209 WHERE 1 = 2 /* PLAN_SNAPSHOT dimension has not been populated */';
210 ELSE
211
212 l_inner_sql:='ISC_MEASURE_7,ISC_MEASURE_8,ISC_MEASURE_1,ISC_MEASURE_2,
213 ISC_MEASURE_3, ISC_MEASURE_4,ISC_MEASURE_5,
214 ISC_MEASURE_4-ISC_MEASURE_5 ISC_MEASURE_6,
215 ISC_MEASURE_9,ISC_MEASURE_10
216 FROM(select (rank() over (&ORDER_BY_CLAUSE nulls last,viewby_id))-1 rnk,
217 viewby_id,
218 ISC_MEASURE_1,ISC_MEASURE_2,ISC_MEASURE_3,
219 sum(ISC_MEASURE_7) over()/decode(sum(ISC_MEASURE_8) over(),0,null,
220 sum(ISC_MEASURE_8)over())*100 ISC_MEASURE_4,
221 sum(comp_required_hr_total) over()/decode(sum(comp_available_hr_total) over(),0,null,
222 sum(comp_available_hr_total)over())*100 ISC_MEASURE_5,
223 ISC_MEASURE_7, ISC_MEASURE_8,
224 sum(ISC_MEASURE_7) over () ISC_MEASURE_9,
225 sum(ISC_MEASURE_8) over () ISC_MEASURE_10
226 FROM(select s.viewby_id,
227 sum(s.plan_required_hr)/decode(sum(s.plan_available_hr),0,null,
228 sum(s.plan_available_hr))*100 ISC_MEASURE_1,
229 sum(s.comp_required_hr)/decode(sum(s.comp_available_hr),0,null,
230 sum(s.comp_available_hr))*100 ISC_MEASURE_2,
231 (sum(s.plan_required_hr)/decode(sum(s.plan_available_hr),0,null,
232 sum(s.plan_available_hr)))*100-
233 (sum(s.comp_required_hr)/decode(sum(s.comp_available_hr),0,null,
234 sum(s.comp_available_hr)))*100 ISC_MEASURE_3,
235 sum(s.plan_required_hr) ISC_MEASURE_7,
236 sum(s.plan_available_hr) ISC_MEASURE_8,
237 sum(s.comp_required_hr) comp_required_hr_total,
238 sum(s.comp_available_hr) comp_available_hr_total
239 FROM
240 (SELECT f.'||l_viewby_id||' VIEWBY_ID,
241 decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
242 sum(f.required_hours),null) plan_required_hr,
243 decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
244 sum(f.available_hours),null) plan_available_hr,
245 decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
246 sum(f.required_hours),null) comp_required_hr,
247 decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
248 sum(f.available_hours),null) comp_available_hr
249 FROM
250 ISC_DBI_PM_0002_MV f
251 WHERE f.start_date = :ISC_CUR_START
252 AND f.period_type_id = :ISC_PERIOD_TYPE_ID
253 AND f.res_gp_flag =:ISC_RES_GP_FLAG
254 AND f.snapshot_id in (&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2)'
255 ||l_org_where||l_res_where||l_res_group_where||l_res_dept_where||
256 'GROUP BY f.'||l_viewby_id||',f.snapshot_id) s
257 GROUP BY s.viewby_id)
258 WHERE (ISC_MEASURE_7<>0 OR ISC_MEASURE_8<>0)
259 OR (comp_required_hr_total <>0 OR comp_available_hr_total <>0)) a,';
260
261
262 IF l_view_by = 'ORGANIZATION+ORGANIZATION' THEN
263 l_stmt := 'SELECT org.name VIEWBY,
264 org.organization_id VIEWBYID,
265 '||l_inner_sql||'
266 HR_ALL_ORGANIZATION_UNITS_TL org
267 WHERE org.organization_id = a.viewby_id
268 AND org.language = :ISC_LANG
269 AND ((a.rnk between &START_INDEX and &END_INDEX) OR &END_INDEX=-1)
270 ORDER BY rnk';
271
272 ELSIF l_view_by = 'RESOURCE+ENI_RESOURCE' THEN
273 l_stmt :='SELECT res.value VIEWBY,
274 res.id VIEWBYID,
275 '||l_inner_sql||'
276 ENI_RESOURCE_V res
277 WHERE a.viewby_id = res.id
278 AND ((a.rnk between &START_INDEX and &END_INDEX) OR &END_INDEX=-1)
279 ORDER BY rnk';
280
281 ELSIF l_view_by = 'RESOURCE+ENI_RESOURCE_DEPARTMENT' THEN
282 l_stmt := 'SELECT res.value VIEWBY,
283 res.id VIEWBYID,
284 '||l_inner_sql||'
285 ENI_RESOURCE_DEPARTMENT_V res
286 WHERE a.viewby_id = res.id
287 AND ((a.rnk between &START_INDEX and &END_INDEX) OR &END_INDEX=-1)
288 ORDER BY rnk';
289
290 ELSE -- l_view_by = 'RESOURCE+ENI_RESOURCE_GROUP'
291 l_stmt := 'SELECT res.value VIEWBY,
292 res.id VIEWBYID,
293 '||l_inner_sql||'
294 ENI_RESOURCE_GROUP_V res
295 WHERE a.viewby_id = res.id
296 AND ((a.rnk between &START_INDEX and &END_INDEX) OR &END_INDEX=-1)
297 ORDER BY rnk';
298 END IF;
299 END IF;
300
301 x_custom_sql := l_stmt;
302
303 l_custom_rec.attribute_name := ':ISC_PERIOD_TYPE_ID';
304 l_custom_rec.attribute_value := to_char(l_period_type_id);
305 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
306 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
307 x_custom_output.EXTEND;
308 x_custom_output(1) := l_custom_rec;
309
310 l_custom_rec.attribute_name := ':ISC_CUR_START';
311 l_custom_rec.attribute_value := to_char(l_time_from,'DD/MM/YYYY');
312 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
313 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
314 x_custom_output.EXTEND;
315 x_custom_output(2) := l_custom_rec;
316
317 l_custom_rec.attribute_name := ':ISC_LANG';
318 l_custom_rec.attribute_value := l_lang;
319 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
320 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
321 x_custom_output.EXTEND;
322 x_custom_output(3) := l_custom_rec;
323
324 l_custom_rec.attribute_name := ':ISC_RES_GP_FLAG';
325 l_custom_rec.attribute_value := to_char(l_res_gp_flag);
326 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
327 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
328 x_custom_output.EXTEND;
329 x_custom_output(4) := l_custom_rec;
330
331
332 END get_sql;
333
334 END ISC_DBI_PLAN_RES_UT_PKG ;
335