[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_PLAN_RES_UT_TREND_PKG
Source
1 PACKAGE BODY ISC_DBI_PLAN_RES_UT_TREND_PKG AS
2 /* $Header: ISCRGAVB.pls 115.4 2004/04/23 23:36:41 chu 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_org VARCHAR2(32000);
12 l_org_where VARCHAR2(32000);
13 l_period_type VARCHAR2(1000);
14 l_res VARCHAR2(32000);
15 l_res_group VARCHAR2(32000);
16 l_res_dept VARCHAR2(32000);
17 l_res_where VARCHAR2(32000);
18 l_res_group_where VARCHAR2(32000);
19 l_res_dept_where VARCHAR2(32000);
20 l_res_gp_flag NUMBER; -- 0 for resource, 1 for dept,2 for res.group, 3 for all
21 l_time_from DATE;
22 l_time_to DATE;
23 l_cur_start DATE;
24 l_cur_end DATE;
25 l_period_type_id NUMBER;
26 l_loop NUMBER;
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 = 'ORGANIZATION+ORGANIZATION')
44 THEN l_org := p_param(i).parameter_value;
45 END IF;
46
47 IF(p_param(i).parameter_name = 'PERIOD_TYPE') THEN
48 l_period_type := p_param(i).parameter_value;
49 END IF;
50
51 IF(p_param(i).parameter_name = 'RESOURCE+ENI_RESOURCE') THEN
52 l_res := p_param(i).parameter_value;
53 END IF;
54
55 IF(p_param(i).parameter_name = 'RESOURCE+ENI_RESOURCE_GROUP') THEN
56 l_res_group := p_param(i).parameter_value;
57 END IF;
58
59 IF(p_param(i).parameter_name = 'RESOURCE+ENI_RESOURCE_DEPARTMENT') THEN
60 l_res_dept := p_param(i).parameter_value;
61 END IF;
62
63 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM') THEN
64 l_time_from := p_param(i).period_date;
65 END IF;
66
67 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_TO') THEN
68 l_time_to := 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_QTR_TO') THEN
76 l_time_to := p_param(i).period_date;
77 END IF;
78
79 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM') THEN
80 l_time_from := p_param(i).period_date;
81 END IF;
82
83 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_TO') THEN
84 l_time_to := p_param(i).period_date;
85 END IF;
86
87
88 END LOOP;
89
90
91 IF ( l_org IS NULL OR l_org = 'All' ) THEN
92 l_org_where := '
93 AND (EXISTS
94 (SELECT 1
95 FROM org_access o
96 WHERE o.responsibility_id = fnd_global.resp_id
97 AND o.resp_application_id = fnd_global.resp_appl_id
98 AND o.organization_id = f.organization_id)
99 OR EXISTS
100 (SELECT 1
101 FROM mtl_parameters org
102 WHERE org.organization_id = f.organization_id
103 AND NOT EXISTS
104 (SELECT 1
105 FROM org_access ora
106 WHERE org.organization_id = ora.organization_id)))';
107
108 ELSE
109 l_org_where := '
110 AND f.organization_id =(&ORGANIZATION+ORGANIZATION)';
111 END IF;
112
113
114 IF ( l_res IS NULL OR l_res = 'All' ) THEN
115 l_res_where :='';
116 ELSE
117 l_res_where := '
118 AND f.resource_id in (&RESOURCE+ENI_RESOURCE)';
119 END IF;
120
121
122 IF ( l_res_group IS NULL OR l_res_group = 'All' )
123 THEN l_res_group_where := '';
124 ELSE l_res_group_where := '
125 AND f.resource_group_id in (&RESOURCE+ENI_RESOURCE_GROUP)';
126 END IF;
127
128 IF ( l_res_dept IS NULL OR l_res_dept = 'All' )
129 THEN l_res_dept_where := '';
130 ELSE l_res_dept_where := '
131 AND f.department_id in (&RESOURCE+ENI_RESOURCE_DEPARTMENT)';
132 END IF;
133
134
135 IF((l_res IS NULL OR l_res = 'All') AND (l_res_dept IS NULL OR l_res_dept='All')
136 AND (l_res_group IS NULL OR l_res_group='All'))
137 THEN l_res_gp_flag := 3; -- all
138 ELSIF ((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
141 ELSIF((l_res IS NULL OR l_res = 'All') AND (l_res_group IS NULL OR l_res_group='All'))
142 THEN l_res_gp_flag :=1; --dept
143 ELSE
144 l_res_gp_flag :=0; -- resource
145 END IF;
146
147
148 IF l_period_type = 'FII_TIME_ENT_YEAR' THEN
149 l_loop :=3;
150 l_period_type_id := 128;
151
152 ELSIF (l_period_type='FII_TIME_ENT_QTR') THEN
153 l_loop := 7;
154 l_period_type_id := 64;
155
156 ELSE
157 l_loop :=11;
158 l_period_type_id := 32;
159
160 END IF;
161
162
163 l_cur_start := l_time_from;
164 l_cur_end := l_time_from;
165
166 FOR i IN 1..l_loop
167 LOOP
168 l_cur_end := FII_TIME_API.next_period_end_date(l_cur_end, l_period_type);
169 END LOOP;
170
171
172 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
173 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
174
175 IF (l_plan IS NULL OR l_plan2 IS NULL)
176 THEN l_stmt := '
177 SELECT 0 ISC_MEASURE_1,
178 0 ISC_MEASURE_2,
179 0 ISC_MEASURE_3
180 FROM dual
181 WHERE 1 = 2 /* PLAN_SNAPSHOT dimension has not been populated */';
182 ELSE
183
184 l_stmt:='SELECT s.period_name VIEWBY,
185 sum(s.plan_required_hr)/decode(sum(s.plan_available_hr),
186 0,null,sum(s.plan_available_hr))*100 ISC_MEASURE_1,
187 sum(s.comp_required_hr)/decode(sum(s.comp_available_hr),
188 0,null,sum(s.comp_available_hr))*100 ISC_MEASURE_2,
189 (sum(s.plan_required_hr)/decode(sum(s.plan_available_hr),
190 0,null,sum(s.plan_available_hr)))*100-
191 (sum(s.comp_required_hr)/decode(sum(s.comp_available_hr),
192 0,null,sum(s.comp_available_hr)))*100 ISC_MEASURE_3
193 FROM(
194 SELECT time.start_date PERIOD_ID,
195 time.name PERIOD_NAME,
196 decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
197 sum(f.required_hours),null) plan_required_hr,
198 decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
199 sum(f.available_hours),null) plan_available_hr,
200 decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
201 sum(f.required_hours),null) comp_required_hr,
202 decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
203 sum(f.available_hours),null) comp_available_hr
204 FROM
205 (SELECT start_date,name
206 FROM '||l_period_type||'
207 WHERE start_date between :ISC_CUR_START and :ISC_CUR_END) time
208 LEFT OUTER JOIN
209 ISC_DBI_PM_0002_MV f
210 ON f.start_date = time.start_date
211 AND f.period_type_id = :ISC_PERIOD_TYPE_ID
212 AND f.res_gp_flag = :ISC_RES_GP_FLAG
213 AND f.snapshot_id in (&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2)
214 '||l_org_where||l_res_group_where||l_res_dept_where||l_res_where||
215 'GROUP BY time.name,time.start_date,f.snapshot_id) s
216 GROUP BY s.period_name,s.period_id
217 ORDER BY s.period_id';
218
219 END IF;
220
221 x_custom_sql := l_stmt;
222
223 l_custom_rec.attribute_name := ':ISC_PERIOD_TYPE_ID';
224 l_custom_rec.attribute_value := to_char(l_period_type_id);
225 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
226 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
227 x_custom_output.EXTEND;
228 x_custom_output(1) := l_custom_rec;
229
230 l_custom_rec.attribute_name := ':ISC_CUR_START';
231 l_custom_rec.attribute_value := to_char(l_cur_start,'DD/MM/YYYY');
232 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
233 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
234 x_custom_output.EXTEND;
235 x_custom_output(2) := l_custom_rec;
236
237 l_custom_rec.attribute_name := ':ISC_CUR_END';
238 l_custom_rec.attribute_value := to_char(l_cur_end,'DD/MM/YYYY');
239 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
240 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
241 x_custom_output.EXTEND;
242 x_custom_output(3) := l_custom_rec;
243
244 l_custom_rec.attribute_name := ':ISC_RES_GP_FLAG';
245 l_custom_rec.attribute_value := to_char(l_res_gp_flag);
246 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
247 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
248 x_custom_output.EXTEND;
249 x_custom_output(4) := l_custom_rec;
250
251 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
252 l_custom_rec.attribute_value := 'TIME+'||l_period_type;
253 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
254 x_custom_output.EXTEND;
255 x_custom_output(5) := l_custom_rec;
256
257 END get_sql;
258
259 END ISC_DBI_PLAN_RES_UT_TREND_PKG ;
260