[Home] [Help]
PACKAGE BODY: APPS.PA_TIMELINE_PVT
Source
1 PACKAGE BODY PA_TIMELINE_PVT as
2 /* $Header: PARLPVTB.pls 120.12.12020000.2 2012/07/19 09:58:05 admarath ship $ */
3
4 -- Procedure level variable declaration.
5
6
7 -- Procedure : create_timeline (Overloaded)
8 -- Purpose : This is overloaded procedure for creating timeline records for
9 -- a new assignment. It generates forecast items and computes
10 -- assignment effort.
11 PROCEDURE Create_Timeline (p_assignment_id IN NUMBER,
12 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
13 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
14 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
15 IS
16
17 l_start_date DATE;
18 l_end_date DATE;
19
20 /*
21 CURSOR c1 IS
22 SELECT item_quantity
23 FROM pa_forecast_items
24 WHERE assignment_id = p_assignment_id
25 AND item_date BETWEEN l_start_date and l_end_date
26 AND delete_flag IN ('Y', 'N');
27
28 -- TYPE NumVarrayType IS VARRAY(200) OF NUMBER;
29 -- l_quantity_arr NumVarrayType := NumVarrayType();
30 */
31
32 l_sum NUMBER :=0;
33
34 BEGIN
35 x_return_status := FND_API.G_RET_STS_SUCCESS;
36
37 SELECT start_date, end_date
38 INTO l_start_date, l_end_date
39 FROM pa_project_assignments
40 WHERE assignment_id = p_assignment_id;
41
42 -- Generate forecast items.
43 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
44 PA_FORECASTITEM_PVT.Create_Forecast_Item(
45 p_assignment_id => p_assignment_id,
46 p_start_date => l_start_date,
47 p_end_date => l_end_date,
48 p_process_mode => 'GENERATE',
49 x_return_status => x_return_status,
50 x_msg_count => x_msg_count,
51 x_msg_data => x_msg_data);
52 END IF;
53
54 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
55 -- Compute assignment effort and update pa_project_assignments table.
56
57 l_sum := PA_SCHEDULE_UTILS.get_num_hours(
58 p_project_id => null,
59 p_assignment_id => p_assignment_id);
60 PA_PROJECT_ASSIGNMENTS_PKG.update_row(
61 p_assignment_id => p_assignment_id,
62 p_assignment_effort => l_sum,
63 x_return_status => x_return_status);
64
65 END IF;
66
67 EXCEPTION
68 WHEN OTHERS THEN
69 x_msg_count := 1;
70 x_msg_data := sqlerrm;
71 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
72 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_TIMELINE_PVT',
73 p_procedure_name => 'Create_Timeline assignment');
74 RAISE;
75
76 END create_timeline;
77
78
79 -- Procedure : Create_Timeline (Overloaded)
80 -- Purpose : This is overloaded function for creating timeline records
81 -- from resource id or resoure name. This will be called from
82 -- concurrent program.
83 PROCEDURE Create_Timeline (p_start_resource_name IN VARCHAR2,
84 p_end_resource_name IN VARCHAR2,
85 p_resource_id IN NUMBER,
86 p_start_date IN DATE,
87 p_end_date IN DATE,
88 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
89 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
90 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
91
92 ld_start_date DATE;
93 ld_end_date DATE; -- End date */
94
95 -- 2001305: Local variables for fix.
96 ld_fi_start_date DATE;
97
98 pi_commit_size NUMBER :=1000;
99 li_commit_size NUMBER:=0;
100
101 -- 2001305: Adding resource_effective_start_date to cursor,
102 -- so that we can pass this value to fi generation when p_start_date is null.
103
104 -- Made this cursor simpler since timeline code was obsoleted.
105
106 -- MOAC Changes : Bug 4363092: In R12 Rebuild Timeline Program should process all resources across OUs
107 -- Hence removing the client_info filter
108 CURSOR cur_res_det IS
109 SELECT distinct res.resource_id resource_id
110 FROM pa_resources_denorm res
111 WHERE upper(res.resource_name) BETWEEN upper(p_start_resource_name) AND upper(p_end_resource_name)
112 -- AND NVL(res.resource_org_id,NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),
113 -- ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10))), -99)) =
114 -- NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),
115 -- ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10))),-99)
116 UNION ALL
117 SELECT distinct res.resource_id resource_id
118 FROM pa_resources_denorm res
119 WHERE res.resource_id = p_resource_id;
120
121
122 cur_res_det_rec cur_res_det%ROWTYPE;
123
124 g_TimelineProfileSetup PA_TIMELINE_GLOB.TimelineProfileSetup;
125 AVAILABILITY_DURATION NUMBER;
126 l_no_timeline_to_create EXCEPTION;
127 l_debug_mode VARCHAR2(20) := 'N';
128
129 BEGIN
130 x_return_status := FND_API.G_RET_STS_SUCCESS;
131
132 -- Add the following two lines so that debug messages get written to the log file.
133 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
134 IF l_debug_mode = 'Y' THEN -- Added for bug 4345291
135 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
136 END IF;
137
138 pa_timeline_util.debug(' Entering Create_Timeline for resource id or resource names ');
139 g_TimelineProfileSetup := PA_TIMELINE_UTIL.get_timeline_profile_setup;
140 availability_duration := nvl(g_TimelineProfileSetup.availability_duration,0);
141
142 -- 2196924: If dates are null or greater than avail period,
143 -- then default to availability period.
144
145 if (availability_duration = 0) then
146 pa_timeline_util.debug('availability_duration = 0');
147 raise l_no_timeline_to_create;
148 end if;
149
150 ld_start_date := NVL(p_start_date, ADD_MONTHS(sysdate, -12));
151 ld_end_date := NVL(p_end_date, ADD_MONTHS(sysdate, availability_duration * (12)));
152
153 if (ld_start_date < ADD_MONTHS(sysdate, -12)) then
154 ld_start_date := ADD_MONTHS(sysdate, -12);
155 end if;
156
157 if (ld_end_date > ADD_MONTHS(sysdate, availability_duration * (12))) then
158 ld_end_date := ADD_MONTHS(sysdate, availability_duration * (12));
159 end if;
160
161 if (ld_start_date > ld_end_date) then
162 raise l_no_timeline_to_create;
163 end if;
164
165 /* 2196924: No longer necessary to find end date of week for timeline.
166 ld_start_date := PA_TIMELINE_UTIL.Get_Week_End_Date(
167 p_org_id=>-99,
168 p_given_date=>ld_start_date) - 6;
169
170 ld_end_date := PA_TIMELINE_UTIL.Get_Week_End_Date(
171 p_org_id=>-99,
172 p_given_date=>ld_end_date);
173 */
174
175 li_commit_size :=1;
176
177 FOR cur_res_det_rec IN cur_res_det LOOP
178 pa_timeline_util.debug(' Processing Resource ID ' || to_char(cur_res_det_rec.resource_id));
179
180
181 pa_timeline_util.debug('Calling forecast item generation');
182
183 -- Generate forecast items.
184 PA_FORECASTITEM_PVT.Create_Forecast_Item(
185 p_resource_id => cur_res_det_rec.resource_id,
186 p_start_date => ld_start_date,
187 p_end_date => ld_end_date,
188 p_process_mode => 'GENERATE',
189 x_return_status => x_return_status,
190 x_msg_count => x_msg_count,
191 x_msg_data => x_msg_data);
192 pa_timeline_util.debug('End calling forecast item generation: 10');
193
194 -- jmarques (1569373):
195 -- When this procedure is called from a concurrent process,
196 -- we partially commit the data. If p_start_resource_name is not
197 -- null, then this means that the procedure is called from
198 -- a concurrent process.
199 if (p_start_resource_name is not null) then
200 li_commit_size := NVL(li_commit_size,0) +1;
201
202 IF pi_commit_size = li_commit_size THEN
203 commit;
204 li_commit_size :=0;
205 END IF;
206 END IF;
207
208 END LOOP;
209
210 EXCEPTION
211 WHEN l_no_timeline_to_create THEN
212 -- There is no timeline to create
213 pa_timeline_util.debug('no timeline to create');
214
215 WHEN OTHERS THEN
216 x_msg_count := 1;
217 x_msg_data := sqlerrm;
218 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
219 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_TIMELINE_PVT',
220 p_procedure_name => 'Create_Timeline Resource');
221 RAISE;
222
223 END Create_Timeline;
224
225
226 -- Procedure : Create_Timeline (Overloaded)
227 -- Purpose : The purpose of this procedure to create timeline records for
228 -- resource which attached with given calendar id's. This will be
229 -- called from Calendar concurrent program.
230 PROCEDURE Create_Timeline(p_calendar_id IN NUMBER,
231 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
232 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
233 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
234
235 -- Get the resource which belong to the calendar
236
237 -- jmarques: 1786935: Modified cursor to include resource_type_code
238 -- since resource_id is not unique.
239
240 -- 1965289: Work around for CRM bug (cannot create future dated resources).
241 -- Added UNION, so that we always recreate timeline for future dated
242 -- resources even if they are not associated with p_calendar_id.
243 -- It is too much impact to find those resources which have
244 -- p_calendar_id as the default calendar.
245
246 CURSOR cur_clndar IS
247 SELECT pares.resource_id resource_id,
248 trunc(start_date_time) start_date,
249 NVL(trunc(end_date_time), TO_DATE('12/31/4712','MM/DD/YYYY')) end_date
250 FROM jtf_cal_resource_assign jtf_res,pa_resources pares
251 WHERE jtf_res.resource_id = pares.jtf_resource_id
252 AND jtf_res.resource_type_code = 'RS_EMPLOYEE'
253 AND jtf_res.calendar_id = p_calendar_id
254 AND jtf_res.primary_calendar_flag = 'Y'
255 UNION ALL
256 SELECT res.resource_id, resdenorm.resource_effective_start_date start_date,
257 TO_DATE('12/31/4712','MM/DD/YYYY') end_date from pa_resources res,
258 pa_resources_denorm resdenorm, HR_ORGANIZATION_INFORMATION_V hoi
259 where res.jtf_resource_id is null
260 and res.resource_id=resdenorm.resource_id and res.resource_type_id = 101 -- Bug 4370196
261 and p_calendar_id = hoi.ORG_INFORMATION1 and resdenorm.resource_org_id = hoi.organization_id -- Bug#14017517 Start
262 and hoi.org_information_context = 'Resource Defaults'
263 and resdenorm.resource_effective_start_date = (select max(resource_effective_start_date)
264 from pa_resources_denorm where resource_id=resdenorm.resource_id ); -- Bug#14017517 end
265
266 li_commit_size NUMBER:=1;
267 pi_commit_size NUMBER :=1000;
268
269 BEGIN
270 x_return_status := FND_API.G_RET_STS_SUCCESS;
271 pa_timeline_util.debug('Start Date and Time ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH:MM:SS'));
272
273 FOR cur_clndar_rec IN cur_clndar LOOP
274
275 -- This call will rebuild the resource forecast items.
276 create_timeline(p_start_resource_name=>NULL,
277 p_end_resource_name =>NULL,
278 p_resource_id=>cur_clndar_rec.resource_id,
279 p_start_date=>cur_clndar_rec.start_date,
280 p_end_date =>cur_clndar_rec.end_date,
281 x_return_status=>x_return_status,
282 x_msg_count=>x_msg_count,
283 x_msg_data=>x_msg_data);
284
285 IF li_commit_size >= pi_commit_size THEN
286 COMMIT;
287 li_commit_size :=1;
288 END IF;
289 li_commit_size := li_commit_size +1;
290
291 END LOOP;
292
293 pa_timeline_util.debug('End Date and Time ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH:MM:SS'));
294
295 EXCEPTION
296 WHEN OTHERS THEN
297 x_msg_count := 1;
298 x_msg_data := sqlerrm;
299 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
300 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_TIMELINE_PVT',
301 p_procedure_name => 'Create_Timeline Calendar');
302 RAISE;
303
304 END Create_Timeline;
305
306
307 PROCEDURE Delete_Timeline(p_assignment_id IN NUMBER,
308 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
309 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
310 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
311 IS
312 l_start_date DATE;
313 l_end_date DATE;
314 l_resource_id NUMBER;
315 BEGIN
316 x_return_status := FND_API.G_RET_STS_SUCCESS;
317
318 PA_FORECASTITEM_PVT.Delete_FI (p_assignment_id => p_assignment_id ,
319 x_return_status => x_return_status,
320 x_msg_count => x_msg_count,
321 x_msg_data => x_msg_data);
322
323 -- Regenerate Resource FIs for the period
324 IF (x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
325 select start_date, end_date, resource_id
326 into l_start_date, l_end_date, l_resource_id
327 from pa_project_assignments
328 where assignment_id = p_assignment_id;
329
330 if (l_resource_id is not null) then
331 PA_FORECASTITEM_PVT.Create_Forecast_Item(
332 p_resource_id => l_resource_id,
333 p_start_date => l_start_date,
334 p_end_date => l_end_date,
335 p_process_mode => 'GENERATE',
336 x_return_status => x_return_status,
337 x_msg_count => x_msg_count,
338 x_msg_data => x_msg_data);
339 end if;
340 end if;
341
342 EXCEPTION
343 WHEN OTHERS THEN
344 x_msg_count := 1;
345 x_msg_data := sqlerrm;
346 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
347 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_TIMELINE_PVT',
348 p_procedure_name => 'Delete_Timeline');
349 RAISE;
350
351 END Delete_Timeline;
352
353
354 -- Procedure : copy_open_asgn_timeline
355 -- Purpose : Copy timeline data, pa_timeline_row_label and
356 -- pa_proj_asgn_time_chart, for a newly created open
357 -- assignment whose timeline has NOT been built.
358 -- Currently, the only API calling this is
359 -- PA_SCHEDULE_PVT.create_opn_asg_schedule.
360 -- Input parameters
361 -- Parameters Type Required Description
362 -- P_assignment_id NUMBER YES Id of the newly created open assignment
363 -- P_assignment_source_id NUMBER YES Id of the source open assignment from which timeline data are copied
364
365 PROCEDURE copy_open_asgn_timeline (p_assignment_id_tbl IN PA_ASSIGNMENTS_PUB.assignment_id_tbl_type,
366 p_assignment_source_id IN NUMBER,
367 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
368 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
369 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
370 IS
371
372 BEGIN
373 x_return_status := FND_API.G_RET_STS_SUCCESS;
374
375 PA_FORECASTITEM_PVT.copy_requirement_fi(p_requirement_id_tbl => p_assignment_id_tbl,
376 p_requirement_source_id => p_assignment_source_id,
377 x_return_status => x_return_status,
378 x_msg_count => x_msg_count,
379 x_msg_data => x_msg_data);
380
381 EXCEPTION
382 WHEN OTHERS THEN
383 x_msg_count := 1;
384 x_msg_data := sqlerrm;
385 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
386 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_TIMELINE_PVT',
387 p_procedure_name => 'Copy_Open_Asgn_Timeline');
388 RAISE;
389
390 END copy_open_asgn_timeline;
391
392
393 -- Procedure : populate_time_chart_table
394 -- Purpose : Populates global temp table PA_TIME_CHART_TEMP with
395 -- time chart records. The procedure is called from
396 -- front end to display timeline.
397 -- Input parameters
398 -- Parameters Required Description
399 -- p_timeline_type Yes The type of time chart records to create.
400 -- Valid values include: 'ProjectAssignments',
401 -- 'ResourceSchedules', 'ResourceAssignments',
402 -- 'ResourceOvercommitmentCalc'.
403 -- p_row_label_id_tbl Yes For Team List: assignment_id
404 -- For Resource List: resource_id
405 -- For Resource Details: assignment_id
406 -- p_resource_id No Required for Resource Details timeline
407 -- p_start_date Yes The start date of the time chart.
408 -- p_end_date Yes The end date of the time chart.
409 -- p_scale_type Yes The scale type of the time chart. Valid
410 -- values include: 'THREE_MONTH', 'MONTH'.
411 -- p_delete_flag No If 'Y', all records are deleted from
412 -- PA_TIME_CHART_TEMP before creating new records.
413 -- Otherwise, no records are deleted.
414 -- x_return_status Yes Return status code.
415 -- x_msg_count Yes Message count.
416 -- x_msg_data Yes Message data.
417 PROCEDURE populate_time_chart_table (p_timeline_type IN VARCHAR2,
418 p_row_label_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
419 p_resource_id IN NUMBER DEFAULT NULL,
420 p_conflict_group_id IN NUMBER DEFAULT NULL,
421 p_assignment_id IN NUMBER DEFAULT NULL,
422 p_start_date IN DATE,
423 p_end_date IN DATE,
424 p_scale_type IN VARCHAR2,
425 p_delete_flag IN VARCHAR2 DEFAULT 'Y',
426 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
427 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
428 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
429 IS
430
431 g_availability_cal_period VARCHAR2(15) := FND_PROFILE.VALUE('PA_AVAILABILITY_CAL_PERIOD');
432 g_res_capacity_percentage NUMBER := FND_NUMBER.CANONICAL_TO_NUMBER(FND_PROFILE.VALUE('PA_RES_CAPACITY_PERCENTAGE'))/100;
433 g_overcommitment_percentage NUMBER := FND_NUMBER.CANONICAL_TO_NUMBER(FND_PROFILE.VALUE('PA_OVERCOMMITMENT_PERCENTAGE'))/100;
434 l_row_label_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
435 l_count NUMBER;
436 prm_license VARCHAR(1);
437
438 BEGIN
439 x_return_status := FND_API.G_RET_STS_SUCCESS;
440 pa_timeline_util.debug('************************************');
441 pa_timeline_util.debug('Entering populate_time_chart_table');
442 pa_timeline_util.debug('p_timeline_type: '|| p_timeline_type);
443 pa_timeline_util.debug('p_resource_id: ' || p_resource_id);
444 pa_timeline_util.debug('p_conflict_group_id: '|| p_conflict_group_id);
445 pa_timeline_util.debug('p_assignment_id: '|| p_assignment_id);
446 pa_timeline_util.debug('p_start_date: ' || p_start_date);
447 pa_timeline_util.debug('p_end_date: ' || p_end_date);
448 pa_timeline_util.debug('p_scale_type: ' || p_scale_type);
449 pa_timeline_util.debug('p_delete_flag: ' || p_delete_flag);
450 pa_timeline_util.debug('************************************');
451
452 pa_timeline_util.debug('g_res_capacity_percentage =' || g_res_capacity_percentage);
453 pa_timeline_util.debug('g_overcommitment_percentage = '|| g_overcommitment_percentage);
454 pa_timeline_util.debug('g_availability_cal_period = '||g_availability_cal_period);
455
456 IF p_delete_flag = 'Y' then
457 PA_TIME_CHART_PKG.delete_row(x_return_status => x_return_status,
458 x_msg_count => x_msg_count,
459 x_msg_data => x_msg_data);
460 END IF;
461
462 pa_timeline_util.debug('After delete row');
463
464 l_count := p_row_label_id_tbl.COUNT;
465 pa_timeline_util.debug('Count = '|| l_count);
466 prm_license := PA_INSTALL.IS_PRM_LICENSED();
467
468 IF l_count > 0 THEN
469 FOR j IN 1 .. (TRUNC(l_count/25)+1) LOOP
470 pa_timeline_util.debug('Enter For loop');
471
472 IF j < TRUNC(l_count/25) +1 THEN
473 FOR i IN 1 .. 25 LOOP
474 l_row_label_id_tbl(i) := p_row_label_id_tbl((j-1)*25+i);
475 END LOOP;
476 ELSE
477 FOR i IN 1.. MOD(l_count, 25) LOOP
478 l_row_label_id_tbl(i) := p_row_label_id_tbl((j-1)*25+i);
479 pa_timeline_util.debug('l_row_label_id = '|| l_row_label_id_tbl(i));
480 END LOOP;
481 FOR i IN (MOD(l_count, 25)+1) .. 25 LOOP
482 l_row_label_id_tbl(i):= null;
483 pa_timeline_util.debug('l_row_label_id = '|| l_row_label_id_tbl(i));
484 END LOOP;
485 END IF;
486
487 IF p_timeline_type = 'ResourceSchedules' THEN
488 IF g_availability_cal_period = 'DAILY' and p_scale_type = 'MONTH' and prm_license = 'Y' THEN
489 INSERT INTO pa_time_chart_temp (
490 select
491 'RESOURCE' time_chart_record_type,
492 resource_id row_label_id,
493 item_date start_date,
494 item_date end_date,
495 global_exp_period_end_date week_end_date,
496 'MONTH' scale_type,
497 decode(availability_flag,
498 'Y', decode(sign(capacity_quantity*g_res_capacity_percentage-availability_quantity), 1, 0, availability_quantity),
499 'N', decode(sign(capacity_quantity*g_overcommitment_percentage-overcommitment_quantity), 1, 0, 0, 0, overcommitment_quantity)) quantity, --For Bug 8812042
500 col.render_priority,
501 col.file_name color_file_name
502 from pa_forecast_items, pa_timeline_colors col
503 where resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
504 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
505 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
506 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
507 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
508 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
509 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
510 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
511 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
512 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
513 and error_flag in ('N','Y') --Bug#9479220
514 and forecast_item_type = 'U'
515 and item_date between p_start_date and p_end_date
516 and delete_flag = 'N'
517 and (availability_flag = 'Y' or overcommitment_flag = 'Y')
518 and col.lookup_code = decode(availability_flag, 'Y', 'AVAILABLE', 'N',
519 decode(sign(overcommitment_quantity - capacity_quantity*g_overcommitment_percentage), 1, 'OVERCOMMITTED', 0, 'AVAILABLE')) --For Bug 8812042
520 UNION ALL
521 select
522 'RESOURCE' time_chart_record_type,
523 fi.resource_id row_label_id,
524 fi.item_date start_date,
525 fi.item_date end_date,
526 fi.global_exp_period_end_date week_end_date,
527 'MONTH' scale_type,
528 fi.item_quantity quantity,
529 col.render_priority,
530 col.file_name color_file_name
531 from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
532 where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
533 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
534 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
535 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
536 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
537 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
538 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
539 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
540 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
541 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
542 and fi.error_flag in ('N','Y') --Bug#9479220
543 and fi.forecast_item_type = 'A'
544 and fi.item_date between p_start_date and p_end_date
545 and fi.delete_flag = 'N'
546 and fi.assignment_id = asgn.assignment_id
547 and col.lookup_code = decode(asgn.assignment_type,
548 'STAFFED_ADMIN_ASSIGNMENT',decode(asgmt_sys_status_code,
549 'STAFFED_ASGMT_CONF',
550 'CONFIRMED_ADMIN',
551 'STAFFED_ASGMT_PROV'),
552 'STAFFED_ASSIGNMENT', decode(asgmt_sys_status_code,
553 'STAFFED_ASGMT_CONF',
554 'STAFFED_ASGMT_CONF',
555 'STAFFED_ASGMT_PROV'))
556 );
557
558 ELSIF g_availability_cal_period = 'DAILY' and p_scale_type = 'MONTH' and prm_license <> 'Y' THEN
559 INSERT INTO pa_time_chart_temp (
560 select
561 'RESOURCE' time_chart_record_type,
562 fi.resource_id row_label_id,
563 fi.item_date start_date,
564 fi.item_date end_date,
565 fi.global_exp_period_end_date week_end_date,
566 'MONTH' scale_type,
567 fi.item_quantity quantity,
568 col.render_priority,
569 col.file_name color_file_name
570 from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
571 where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
572 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
573 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
574 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
575 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
576 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
577 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
578 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
579 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
580 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
581 and fi.error_flag in ('N','Y') --Bug#9479220
582 and fi.forecast_item_type = 'A'
583 and fi.item_date between p_start_date and p_end_date
584 and fi.delete_flag = 'N'
585 and fi.assignment_id = asgn.assignment_id
586 and col.lookup_code = decode(asgn.assignment_type,
587 'STAFFED_ADMIN_ASSIGNMENT',decode(asgmt_sys_status_code,
588 'STAFFED_ASGMT_CONF',
589 'CONFIRMED_ADMIN',
590 'STAFFED_ASGMT_PROV'),
591 'STAFFED_ASSIGNMENT', decode(asgmt_sys_status_code,
592 'STAFFED_ASGMT_CONF',
593 'STAFFED_ASGMT_CONF',
594 'STAFFED_ASGMT_PROV'))
595 );
596
597 ELSIF g_availability_cal_period = 'DAILY' AND p_scale_type = 'THREE_MONTH' and prm_license = 'Y' THEN
598 INSERT INTO pa_time_chart_temp (
599 select 'RESOURCE' time_chart_record_type,
600 resource_id row_label_id,
601 global_exp_period_end_date-6 start_date,
602 global_exp_period_end_date end_date,
603 global_exp_period_end_date week_end_date,
604 'THREE_MONTH' scale_type,
605 sum(decode(availability_flag,
606 'Y', decode(sign((capacity_quantity*g_res_capacity_percentage)-availability_quantity), 1, 0, availability_quantity),
607 'N', decode(sign((capacity_quantity*g_overcommitment_percentage)-overcommitment_quantity), 1, 0, 0, 0, overcommitment_quantity))) quantity, --For Bug 8812042
608 col.render_priority,
609 col.file_name color_file_name
610 from pa_forecast_items, pa_timeline_colors col
611 where resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
612 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
613 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
614 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
615 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
616 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
617 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
618 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
619 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
620 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
621 and error_flag in ('N','Y') --Bug#9479220
622 and item_date between p_start_date and p_end_date
623 and delete_flag = 'N'
624 and col.lookup_code = decode(availability_flag, 'Y', 'AVAILABLE', 'N',
625 decode(sign(overcommitment_quantity - capacity_quantity*g_overcommitment_percentage), 1, 'OVERCOMMITTED', 0, 'AVAILABLE')) --For Bug 8812042
626 and (availability_flag = 'Y' or overcommitment_flag = 'Y')
627 GROUP BY resource_id,
628 global_exp_period_end_date,
629 col.file_name,
630 col.render_priority
631 UNION ALL
632 select 'RESOURCE' time_chart_record_type,
633 fi.resource_id row_label_id,
634 fi.global_exp_period_end_date-6 start_date,
635 fi.global_exp_period_end_date end_date,
636 fi.global_exp_period_end_date week_end_date,
637 'THREE_MONTH' scale_type,
638 sum(fi.item_quantity) quantity,
639 col.render_priority,
640 col.file_name color_file_name
641 from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
642 where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
643 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
644 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
645 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
646 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
647 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
648 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
649 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
650 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
651 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
652 and fi.error_flag in ('N','Y') --Bug#9479220
653 and fi.forecast_item_type = 'A'
654 and fi.item_date between p_start_date and p_end_date
655 and fi.delete_flag = 'N'
656 and fi.assignment_id = asgn.assignment_id
657 and col.lookup_code = decode(asgn.assignment_type,
658 'STAFFED_ADMIN_ASSIGNMENT',decode(asgmt_sys_status_code,
659 'STAFFED_ASGMT_CONF',
660 'CONFIRMED_ADMIN',
661 'STAFFED_ASGMT_PROV'),
662 'STAFFED_ASSIGNMENT', decode(asgmt_sys_status_code,
663 'STAFFED_ASGMT_CONF',
664 'STAFFED_ASGMT_CONF',
665 'STAFFED_ASGMT_PROV'))
666 GROUP BY fi.resource_id,
667 fi.global_exp_period_end_date,
668 fi.forecast_item_type,
669 col.file_name,
670 col.render_priority
671 );
672
673 ELSIF g_availability_cal_period = 'DAILY' AND p_scale_type = 'THREE_MONTH' and prm_license <> 'Y' THEN
674 INSERT INTO pa_time_chart_temp (
675 select 'RESOURCE' time_chart_record_type,
676 fi.resource_id row_label_id,
677 fi.global_exp_period_end_date-6 start_date,
678 fi.global_exp_period_end_date end_date,
679 fi.global_exp_period_end_date week_end_date,
680 'THREE_MONTH' scale_type,
681 sum(fi.item_quantity) quantity,
682 col.render_priority,
683 col.file_name color_file_name
684 from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
685 where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
686 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
687 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
688 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
689 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
690 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
691 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
692 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
693 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
694 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
695 and fi.error_flag in ('N','Y') --Bug#9479220
696 and fi.forecast_item_type = 'A'
697 and fi.item_date between p_start_date and p_end_date
698 and fi.delete_flag = 'N'
699 and fi.assignment_id = asgn.assignment_id
700 and col.lookup_code = decode(asgn.assignment_type,
701 'STAFFED_ADMIN_ASSIGNMENT',decode(asgmt_sys_status_code,
702 'STAFFED_ASGMT_CONF',
703 'CONFIRMED_ADMIN',
704 'STAFFED_ASGMT_PROV'),
705 'STAFFED_ASSIGNMENT', decode(asgmt_sys_status_code,
706 'STAFFED_ASGMT_CONF',
707 'STAFFED_ASGMT_CONF',
708 'STAFFED_ASGMT_PROV'))
709 GROUP BY fi.resource_id,
710 fi.global_exp_period_end_date,
711 fi.forecast_item_type,
712 col.file_name,
713 col.render_priority
714 );
715
716 ELSIF g_availability_cal_period = 'WEEKLY' AND p_scale_type = 'MONTH' and prm_license = 'Y' THEN
717 INSERT INTO pa_time_chart_temp (
718 select 'RESOURCE' time_chart_record_type,
719 fi.resource_id row_label_id,
720 fi.item_date start_date,
721 fi.item_date end_date,
722 fi.global_exp_period_end_date week_end_date,
723 'MONTH' scale_type,
724 decode(fi_week.availability_flag, 'Y', fi.availability_quantity, 'N', fi.overcommitment_quantity) quantity,
725 col.render_priority,
726 col.file_name color_file_name
727 from pa_forecast_items fi, pa_timeline_colors col,
728 (select resource_id,
729 global_exp_period_end_date week_end_date,
730 decode(sign(sum(capacity_quantity)*g_res_capacity_percentage-sum(availability_quantity)),1, 'N', 'Y') availability_flag,
731 decode(sign(sum(capacity_quantity)*g_overcommitment_percentage-sum(overcommitment_quantity)), 1, 'N',
732 0, decode(sum(overcommitment_quantity), 0, 'N', 'Y'),
733 -1, 'Y') overcommitment_flag,
734 forecast_item_type,
735 delete_flag
736 from pa_forecast_items
737 where item_date between p_start_date and p_end_date
738 group by resource_id,
739 global_exp_period_end_date,
740 forecast_item_type,
741 delete_flag) fi_week
742 where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
743 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
744 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
745 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
746 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
747 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
748 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
749 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
750 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
751 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
752 and fi.error_flag in ('N','Y') --Bug#9479220
753 and fi.resource_id = fi_week.resource_id
754 and fi.item_date between p_start_date and p_end_date
755 and fi.global_exp_period_end_date = fi_week.week_end_date
756 and fi.forecast_item_type = 'U'
757 and fi.forecast_item_type = fi_week.forecast_item_type
758 and fi.delete_flag = 'N'
759 and fi.delete_flag = fi_week.delete_flag
760 and (fi.availability_quantity > 0 or fi.overcommitment_quantity > 0)
761 and (fi_week.availability_flag = 'Y' or fi_week.overcommitment_flag = 'Y')
762 and col.lookup_code = decode(fi_week.availability_flag, 'Y', 'AVAILABLE', 'N', 'OVERCOMMITTED')
763 UNION ALL
764 select 'RESOURCE' time_chart_record_type,
765 fi.resource_id row_label_id,
766 fi.item_date start_date,
767 fi.item_date end_date,
768 fi.global_exp_period_end_date week_end_date,
769 'MONTH' scale_type,
770 fi.item_quantity quantity,
771 col.render_priority,
772 col.file_name color_file_name
773 from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
774 where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
775 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
776 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
777 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
778 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
779 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
780 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
781 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
782 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
783 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
784 and fi.error_flag in ('N','Y') --Bug#9479220
785 and fi.forecast_item_type = 'A'
786 and fi.item_date between p_start_date and p_end_date
787 and fi.delete_flag = 'N'
788 and fi.assignment_id = asgn.assignment_id
789 and col.lookup_code = decode(asgn.assignment_type,
790 'STAFFED_ADMIN_ASSIGNMENT',decode(asgmt_sys_status_code,
791 'STAFFED_ASGMT_CONF',
792 'CONFIRMED_ADMIN',
793 'STAFFED_ASGMT_PROV'),
794 'STAFFED_ASSIGNMENT', decode(asgmt_sys_status_code,
795 'STAFFED_ASGMT_CONF',
796 'STAFFED_ASGMT_CONF',
797 'STAFFED_ASGMT_PROV'))
798 );
799
800 ELSIF g_availability_cal_period = 'WEEKLY' AND p_scale_type = 'MONTH' and prm_license <> 'Y' THEN
801 INSERT INTO pa_time_chart_temp (
802 select 'RESOURCE' time_chart_record_type,
803 fi.resource_id row_label_id,
804 fi.item_date start_date,
805 fi.item_date end_date,
806 fi.global_exp_period_end_date week_end_date,
807 'MONTH' scale_type,
808 fi.item_quantity quantity,
809 col.render_priority,
810 col.file_name color_file_name
811 from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
812 where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
813 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
814 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
815 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
816 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
817 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
818 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
819 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
820 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
821 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
822 and fi.error_flag in ('N','Y') --Bug#9479220
823 and fi.forecast_item_type = 'A'
824 and fi.item_date between p_start_date and p_end_date
825 and fi.delete_flag = 'N'
826 and fi.assignment_id = asgn.assignment_id
827 and col.lookup_code = decode(asgn.assignment_type,
828 'STAFFED_ADMIN_ASSIGNMENT',decode(asgmt_sys_status_code,
829 'STAFFED_ASGMT_CONF',
830 'CONFIRMED_ADMIN',
831 'STAFFED_ASGMT_PROV'),
832 'STAFFED_ASSIGNMENT', decode(asgmt_sys_status_code,
833 'STAFFED_ASGMT_CONF',
834 'STAFFED_ASGMT_CONF',
835 'STAFFED_ASGMT_PROV'))
836 );
837
838 ELSIF g_availability_cal_period = 'WEEKLY' AND p_scale_type = 'THREE_MONTH' and prm_license = 'Y' THEN
839 INSERT INTO pa_time_chart_temp (
840 select 'RESOURCE' time_chart_record_type,
841 fi.resource_id row_label_id,
842 fi.global_exp_period_end_date-6 start_date,
843 fi.global_exp_period_end_date end_date,
844 fi.global_exp_period_end_date week_end_date,
845 'THREE_MONTH' scale_type,
846 sum(decode(fi_week.availability_flag, 'Y', fi.availability_quantity, 'N', fi.overcommitment_quantity)) quantity,
847 col.render_priority,
848 col.file_name color_file_name
849 from pa_forecast_items fi, pa_timeline_colors col,
850 (select resource_id,
851 global_exp_period_end_date week_end_date,
852 decode(sign(sum(capacity_quantity)*g_res_capacity_percentage-sum(availability_quantity)),1, 'N', 'Y') availability_flag,
853 decode(sign(sum(capacity_quantity)*g_overcommitment_percentage-sum(overcommitment_quantity)), 1, 'N',
854 0, decode(sum(overcommitment_quantity), 0, 'N', 'Y'),
855 -1, 'Y') overcommitment_flag,
856 forecast_item_type,
857 delete_flag
858 from pa_forecast_items
859 where item_date between p_start_date and p_end_date
860 group by resource_id,
861 global_exp_period_end_date,
862 forecast_item_type,
863 delete_flag) fi_week
864 where fi.resource_id in(l_row_label_id_tbl(1), l_row_label_id_tbl(2),
865 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
866 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
867 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
868 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
869 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
870 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
871 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
872 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
873 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
874 and fi.error_flag in ('N','Y') --Bug#9479220
875 and fi.resource_id = fi_week.resource_id
876 and fi.item_date between p_start_date and p_end_date
877 and fi.global_exp_period_end_date = fi_week.week_end_date
878 and fi.forecast_item_type = 'U'
879 and fi.forecast_item_type = fi_week.forecast_item_type
880 and fi.delete_flag = 'N'
881 and fi.delete_flag = fi_week.delete_flag
882 and (fi.availability_quantity > 0 or fi.overcommitment_quantity > 0)
883 and (fi_week.availability_flag = 'Y' or fi_week.overcommitment_flag = 'Y')
884 and col.lookup_code = decode(fi.availability_flag, 'Y', 'AVAILABLE', 'N',
885 decode(sign(fi.overcommitment_quantity - fi.capacity_quantity*g_overcommitment_percentage), 1, 'OVERCOMMITTED', 0, 'AVAILABLE')) --For Bug 8812042
886 GROUP BY fi.resource_id,
887 fi.global_exp_period_end_date,
888 fi_week.availability_flag,
889 col.file_name,
890 col.render_priority
891 UNION ALL
892 select 'RESOURCE' time_chart_record_type,
893 fi.resource_id row_label_id,
894 fi.global_exp_period_end_date-6 start_date,
895 fi.global_exp_period_end_date end_date,
896 fi.global_exp_period_end_date week_end_date,
897 'THREE_MONTH' scale_type,
898 sum(fi.item_quantity) quantity,
899 col.render_priority,
900 col.file_name color_file_name
901 from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
902 where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
903 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
904 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
905 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
906 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
907 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
908 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
909 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
910 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
911 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
912 and fi.error_flag in ('N','Y') --Bug#9479220
913 and fi.forecast_item_type = 'A'
914 and fi.item_date between p_start_date and p_end_date
915 and fi.delete_flag = 'N'
916 and fi.assignment_id = asgn.assignment_id
917 and col.lookup_code = decode(asgn.assignment_type,
918 'STAFFED_ADMIN_ASSIGNMENT',decode(asgmt_sys_status_code,
919 'STAFFED_ASGMT_CONF',
920 'CONFIRMED_ADMIN',
921 'STAFFED_ASGMT_PROV'),
922 'STAFFED_ASSIGNMENT', decode(asgmt_sys_status_code,
923 'STAFFED_ASGMT_CONF',
924 'STAFFED_ASGMT_CONF',
925 'STAFFED_ASGMT_PROV'))
926 GROUP BY fi.resource_id,
927 fi.global_exp_period_end_date,
928 fi.forecast_item_type,
929 col.file_name,
930 col.render_priority
931 );
932
933 ELSIF g_availability_cal_period = 'WEEKLY' AND p_scale_type = 'THREE_MONTH' and prm_license <> 'Y' THEN
934 INSERT INTO pa_time_chart_temp (
935 select 'RESOURCE' time_chart_record_type,
936 fi.resource_id row_label_id,
937 fi.global_exp_period_end_date-6 start_date,
938 fi.global_exp_period_end_date end_date,
939 fi.global_exp_period_end_date week_end_date,
940 'THREE_MONTH' scale_type,
941 sum(fi.item_quantity) quantity,
942 col.render_priority,
943 col.file_name color_file_name
944 from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
945 where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
946 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
947 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
948 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
949 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
950 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
951 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
952 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
953 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
954 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
955 and fi.error_flag in ('N','Y') --Bug#9479220
956 and fi.forecast_item_type = 'A'
957 and fi.item_date between p_start_date and p_end_date
958 and fi.delete_flag = 'N'
959 and fi.assignment_id = asgn.assignment_id
960 and col.lookup_code = decode(asgn.assignment_type,
961 'STAFFED_ADMIN_ASSIGNMENT',decode(asgmt_sys_status_code,
962 'STAFFED_ASGMT_CONF',
963 'CONFIRMED_ADMIN',
964 'STAFFED_ASGMT_PROV'),
965 'STAFFED_ASSIGNMENT', decode(asgmt_sys_status_code,
966 'STAFFED_ASGMT_CONF',
967 'STAFFED_ASGMT_CONF',
968 'STAFFED_ASGMT_PROV'))
969 GROUP BY fi.resource_id,
970 fi.global_exp_period_end_date,
971 fi.forecast_item_type,
972 col.file_name,
973 col.render_priority
974 );
975 END IF;
976
977 -- Resource Details Timeline
978 ELSIF p_timeline_type = 'ResourceAssignments' THEN
979 IF g_availability_cal_period = 'DAILY' and p_scale_type = 'MONTH' and prm_license = 'Y' THEN
980 INSERT INTO pa_time_chart_temp (
981 select
982 decode(fi.availability_flag, 'Y', 'AVAILABLE', 'N', 'OVERCOMMITTED')time_chart_record_type,
983 decode(fi.availability_flag, 'Y', -99, 'N', -100) row_label_id,
984 fi.item_date start_date,
985 fi.item_date end_date,
986 fi.global_exp_period_end_date week_end_date,
987 'MONTH' scale_type,
988 decode (fi.availability_flag,
989 'Y', decode(sign(fi.availability_quantity-fi.capacity_quantity*g_res_capacity_percentage), -1, 0, fi.availability_quantity),
990 'N', decode(sign(fi.overcommitment_quantity-fi.capacity_quantity*g_overcommitment_percentage), -1, 0, 0, 0, fi.overcommitment_quantity)) quantity, --Bug 8627070
991 col.render_priority,
992 col.file_name color_file_name
993 from pa_forecast_items fi, pa_timeline_colors col
994 where fi.resource_id = p_resource_id
995 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
996 and fi.error_flag in ('N','Y') --Bug#9479220
997 and fi.forecast_item_type = 'U'
998 and fi.item_date between p_start_date and p_end_date
999 and fi.delete_flag = 'N'
1000 and (fi.availability_flag = 'Y' or fi.overcommitment_flag = 'Y')
1001 and col.lookup_code = decode(availability_flag, 'Y', 'AVAILABLE', 'N',
1002 decode(sign(overcommitment_quantity - capacity_quantity*g_overcommitment_percentage), 1, 'OVERCOMMITTED', 0, 'AVAILABLE')) --For Bug 8812042
1003 UNION ALL
1004 select
1005 'ASSIGNMENT' time_chart_record_type,
1006 fi.assignment_id row_label_id,fi.item_date start_date,
1007 fi.item_date end_date,
1008 fi.global_exp_period_end_date week_end_date,
1009 'MONTH' scale_type,
1010 fi.item_quantity quantity,
1011 col.render_priority,
1012 col.file_name color_file_name
1013 from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1014 where fi.resource_id = p_resource_id
1015 and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1016 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1017 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1018 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
1019 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
1020 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
1021 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
1022 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
1023 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
1024 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
1025 and fi.error_flag in ('N','Y') --Bug#9479220
1026 and fi.assignment_id = asgn.assignment_id
1027 and fi.forecast_item_type = 'A'
1028 and fi.delete_flag = 'N'
1029 and fi.item_date between p_start_date and p_end_date
1030 and col.lookup_code = decode(asgn.assignment_type,
1031 'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1032 'STAFFED_ASGMT_CONF',
1033 'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
1034 'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1035 'STAFFED_ASGMT_CONF',
1036 'STAFFED_ASGMT_CONF',
1037 'STAFFED_ASGMT_PROV'))
1038 );
1039
1040 ELSIF g_availability_cal_period = 'DAILY' and p_scale_type = 'MONTH' and prm_license <> 'Y' THEN
1041 INSERT INTO pa_time_chart_temp (
1042 select
1043 'ASSIGNMENT' time_chart_record_type,
1044 fi.assignment_id row_label_id,fi.item_date start_date,
1045 fi.item_date end_date,
1046 fi.global_exp_period_end_date week_end_date,
1047 'MONTH' scale_type,
1048 fi.item_quantity quantity,
1049 col.render_priority,
1050 col.file_name color_file_name
1051 from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1052 where fi.resource_id = p_resource_id
1053 and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1054 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1055 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1056 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
1057 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
1058 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
1059 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
1060 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
1061 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
1062 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
1063 and fi.error_flag in ('N','Y') --Bug#9479220
1064 and fi.assignment_id = asgn.assignment_id
1065 and fi.forecast_item_type = 'A'
1066 and fi.delete_flag = 'N'
1067 and fi.item_date between p_start_date and p_end_date
1068 and col.lookup_code = decode(asgn.assignment_type,
1069 'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1070 'STAFFED_ASGMT_CONF',
1071 'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
1072 'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1073 'STAFFED_ASGMT_CONF',
1074 'STAFFED_ASGMT_CONF',
1075 'STAFFED_ASGMT_PROV'))
1076 );
1077
1078 ELSIF g_availability_cal_period = 'DAILY' AND p_scale_type = 'THREE_MONTH' and prm_license = 'Y' THEN
1079 INSERT INTO pa_time_chart_temp (
1080 select
1081 decode(fi.availability_flag, 'Y', 'AVAILABLE', 'N', 'OVERCOMMITTED') time_chart_record_type,
1082 decode(fi.availability_flag, 'Y', -99, 'N', -100) row_label_id,
1083 fi.global_exp_period_end_date - 6 start_date,
1084 fi.global_exp_period_end_date end_date,
1085 fi.global_exp_period_end_date week_end_date,
1086 'THREE_MONTH' scale_type,
1087 sum(decode(fi.availability_flag,
1088 'Y', decode(sign(fi.capacity_quantity*g_res_capacity_percentage-fi.availability_quantity), 1, 0, fi.availability_quantity),
1089 'N', decode(sign(fi.capacity_quantity*g_overcommitment_percentage-fi.overcommitment_quantity), 1, 0, 0, 0, fi.overcommitment_quantity))) quantity, --Bug 8627070
1090 col.render_priority,
1091 col.file_name color_file_name
1092 from pa_forecast_items fi, pa_timeline_colors col
1093 where fi.resource_id = p_resource_id
1094 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
1095 and fi.error_flag in ('N','Y') --Bug#9479220
1096 and fi.forecast_item_type = 'U'
1097 and fi.item_date between p_start_date and p_end_date
1098 and fi.delete_flag = 'N'
1099 and col.lookup_code = decode(availability_flag, 'Y', 'AVAILABLE', 'N',
1100 decode(sign(overcommitment_quantity - capacity_quantity*g_overcommitment_percentage), 1, 'OVERCOMMITTED', 0, 'AVAILABLE')) --For Bug 8812042
1101 and (fi.availability_flag = 'Y' or fi.overcommitment_flag = 'Y')
1102 GROUP BY fi.assignment_id,
1103 fi.global_exp_period_end_date,
1104 fi.availability_flag,
1105 col.file_name,
1106 col.render_priority
1107 UNION ALL
1108 select
1109 'ASSIGNMENT' time_chart_record_type,
1110 fi.assignment_id row_label_id,
1111 fi.global_exp_period_end_date-6 start_date,
1112 fi.global_exp_period_end_date end_date,
1113 fi.global_exp_period_end_date week_end_date,
1114 'THREE_MONTH' scale_type,
1115 sum(fi.item_quantity) quantity,
1116 col.render_priority,
1117 col.file_name color_file_name
1118 from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1119 where fi.resource_id = p_resource_id
1120 and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1121 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1122 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1123 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
1124 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
1125 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
1126 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
1127 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
1128 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
1129 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
1130 and fi.error_flag in ('N','Y') --Bug#9479220
1131 and fi.assignment_id = asgn.assignment_id
1132 and fi.forecast_item_type = 'A'
1133 and fi.delete_flag = 'N'
1134 and fi.item_date between p_start_date and p_end_date
1135 and col.lookup_code = decode(asgn.assignment_type,
1136 'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1137 'STAFFED_ASGMT_CONF',
1138 'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
1139 'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1140 'STAFFED_ASGMT_CONF',
1141 'STAFFED_ASGMT_CONF',
1142 'STAFFED_ASGMT_PROV'))
1143 GROUP BY fi.assignment_id,
1144 fi.global_exp_period_end_date,
1145 col.file_name,
1146 col.render_priority
1147 );
1148
1149 ELSIF g_availability_cal_period = 'DAILY' AND p_scale_type = 'THREE_MONTH' and prm_license <> 'Y' THEN
1150 INSERT INTO pa_time_chart_temp (
1151 select
1152 'ASSIGNMENT' time_chart_record_type,
1153 fi.assignment_id row_label_id,
1154 fi.global_exp_period_end_date-6 start_date,
1155 fi.global_exp_period_end_date end_date,
1156 fi.global_exp_period_end_date week_end_date,
1157 'THREE_MONTH' scale_type,
1158 sum(fi.item_quantity) quantity,
1159 col.render_priority,
1160 col.file_name color_file_name
1161 from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1162 where fi.resource_id = p_resource_id
1163 and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1164 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1165 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1166 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
1167 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
1168 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
1169 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
1170 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
1171 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
1172 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
1173 and fi.error_flag in ('N','Y') --Bug#9479220
1174 and fi.assignment_id = asgn.assignment_id
1175 and fi.forecast_item_type = 'A'
1176 and fi.delete_flag = 'N'
1177 and fi.item_date between p_start_date and p_end_date
1178 and col.lookup_code = decode(asgn.assignment_type,
1179 'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1180 'STAFFED_ASGMT_CONF',
1181 'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
1182 'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1183 'STAFFED_ASGMT_CONF',
1184 'STAFFED_ASGMT_CONF',
1185 'STAFFED_ASGMT_PROV'))
1186 GROUP BY fi.assignment_id,
1187 fi.global_exp_period_end_date,
1188 col.file_name,
1189 col.render_priority
1190 );
1191
1192 ELSIF g_availability_cal_period = 'WEEKLY' AND p_scale_type = 'MONTH' and prm_license = 'Y' THEN
1193 pa_timeline_util.debug('Resource Details: weekly month');
1194 INSERT INTO pa_time_chart_temp (
1195 select
1196 decode(fi_week.availability_flag, 'Y', 'AVAILABLE', 'N', 'OVERCOMMITTED') time_chart_record_type,
1197 decode(fi_week.availability_flag, 'Y', -99, 'N', -100) row_label_id,
1198 fi.item_date start_date,
1199 fi.item_date end_date,
1200 fi_week.week_end_date,
1201 'MONTH' scale_type,
1202 decode(fi_week.availability_flag, 'Y', fi.availability_quantity, 'N', fi.overcommitment_quantity) quantity,
1203 col.render_priority,
1204 col.file_name color_file_name
1205 from pa_forecast_items fi, pa_timeline_colors col,
1206 (select resource_id,
1207 global_exp_period_end_date week_end_date,
1208 decode(sign(sum(capacity_quantity)*g_res_capacity_percentage-sum(availability_quantity)),1, 'N', 'Y') availability_flag,
1209 decode(sign(sum(capacity_quantity)*g_overcommitment_percentage-sum(overcommitment_quantity)), 1, 'N',
1210 0, decode(sum(overcommitment_quantity), 0, 'N', 'Y'),
1211 -1, 'Y') overcommitment_flag,
1212 forecast_item_type,
1213 delete_flag
1214 from pa_forecast_items
1215 where item_date between p_start_date and p_end_date
1216 group by resource_id,
1217 global_exp_period_end_date,
1218 forecast_item_type,
1219 delete_flag) fi_week
1220 where fi.resource_id = p_resource_id
1221 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
1222 and fi.error_flag in ('N','Y') --Bug#9479220
1223 and fi.resource_id = fi_week.resource_id
1224 and fi.item_date between p_start_date and p_end_date
1225 and fi.global_exp_period_end_date = fi_week.week_end_date
1226 and fi.forecast_item_type = 'U'
1227 and fi.forecast_item_type = fi_week.forecast_item_type
1228 and fi.delete_flag = 'N'
1229 and fi.delete_flag = fi_week.delete_flag
1230 and (fi.availability_quantity > 0 or fi.overcommitment_quantity > 0)
1231 and (fi_week.availability_flag = 'Y' or fi_week.overcommitment_flag = 'Y')
1232 and col.lookup_code = decode(fi.availability_flag, 'Y', 'AVAILABLE', 'N',
1233 decode(sign(fi.overcommitment_quantity - fi.capacity_quantity*g_overcommitment_percentage), 1, 'OVERCOMMITTED', 0, 'AVAILABLE')) --For Bug 8812042
1234 UNION ALL
1235 select
1236 'ASSIGNMENT' time_chart_record_type,
1237 fi.assignment_id row_label_id,
1238 fi.item_date start_date,
1239 fi.item_date end_date,
1240 fi.global_exp_period_end_date week_end_date,
1241 'MONTH' scale_type,
1242 fi.item_quantity quantity,
1243 col.render_priority,
1244 col.file_name color_file_name
1245 from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1246 where fi.resource_id = p_resource_id
1247 and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1248 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1249 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1250 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
1251 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
1252 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
1253 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
1254 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
1255 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
1256 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
1257 and fi.error_flag in ('N','Y') --Bug#9479220
1258 and fi.assignment_id = asgn.assignment_id
1259 and fi.forecast_item_type = 'A'
1260 and fi.delete_flag = 'N'
1261 and fi.item_date between p_start_date and p_end_date
1262 and col.lookup_code = decode(asgn.assignment_type,
1263 'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1264 'STAFFED_ASGMT_CONF',
1265 'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
1266 'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1267 'STAFFED_ASGMT_CONF',
1268 'STAFFED_ASGMT_CONF',
1269 'STAFFED_ASGMT_PROV'))
1270 );
1271
1272 ELSIF g_availability_cal_period = 'WEEKLY' AND p_scale_type = 'MONTH' and prm_license <> 'Y' THEN
1273 pa_timeline_util.debug('Resource Details: weekly month');
1274 INSERT INTO pa_time_chart_temp (
1275 select
1276 'ASSIGNMENT' time_chart_record_type,
1277 fi.assignment_id row_label_id,
1278 fi.item_date start_date,
1279 fi.item_date end_date,
1280 fi.global_exp_period_end_date week_end_date,
1281 'MONTH' scale_type,
1282 fi.item_quantity quantity,
1283 col.render_priority,
1284 col.file_name color_file_name
1285 from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1286 where fi.resource_id = p_resource_id
1287 and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1288 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1289 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1290 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
1291 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
1292 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
1293 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
1294 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
1295 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
1296 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
1297 and fi.error_flag in ('N','Y') --Bug#9479220
1298 and fi.assignment_id = asgn.assignment_id
1299 and fi.forecast_item_type = 'A'
1300 and fi.delete_flag = 'N'
1301 and fi.item_date between p_start_date and p_end_date
1302 and col.lookup_code = decode(asgn.assignment_type,
1303 'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1304 'STAFFED_ASGMT_CONF',
1305 'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
1306 'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1307 'STAFFED_ASGMT_CONF',
1308 'STAFFED_ASGMT_CONF',
1309 'STAFFED_ASGMT_PROV'))
1310 );
1311
1312 ELSIF g_availability_cal_period = 'WEEKLY' AND p_scale_type = 'THREE_MONTH' and prm_license = 'Y' THEN
1313 pa_timeline_util.debug('Resource Details: weekly | three_month');
1314 INSERT INTO pa_time_chart_temp (
1315 select
1316 decode(fi_week.availability_flag, 'Y', 'AVAILABLE', 'N', 'OVERCOMMITTED') time_chart_record_type,
1317 decode(fi_week.availability_flag, 'Y', -99, 'N', -100) row_label_id,
1318 fi.global_exp_period_end_date-6 start_date,
1319 fi.global_exp_period_end_date end_date,
1320 fi.global_exp_period_end_date week_end_date,
1321 'THREE_MONTH' scale_type,
1322 sum(decode(fi_week.availability_flag, 'Y', fi.availability_quantity, 'N', fi.overcommitment_quantity)) quantity,
1323 col.render_priority,
1324 col.file_name color_file_name
1325 from pa_forecast_items fi, pa_timeline_colors col,
1326 (select resource_id,
1327 global_exp_period_end_date week_end_date,
1328 decode(sign(sum(capacity_quantity)*g_res_capacity_percentage-sum(availability_quantity)),1, 'N', 'Y') availability_flag,
1329 decode(sign(sum(capacity_quantity)*g_overcommitment_percentage-sum(overcommitment_quantity)), 1, 'N',
1330 0, decode(sum(overcommitment_quantity), 0, 'N', 'Y'),
1331 -1, 'Y') overcommitment_flag,
1332 forecast_item_type,
1333 delete_flag
1334 from pa_forecast_items
1335 where item_date between p_start_date and p_end_date
1336 group by resource_id,
1337 global_exp_period_end_date,
1338 forecast_item_type,
1339 delete_flag) fi_week
1340 where fi.resource_id = p_resource_id
1341 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
1342 and fi.error_flag in ('N','Y') --Bug#9479220
1343 and fi.resource_id = fi_week.resource_id
1344 and fi.item_date between p_start_date and p_end_date
1345 and fi.global_exp_period_end_date = fi_week.week_end_date
1346 and fi.forecast_item_type = 'U'
1347 and fi.forecast_item_type = fi_week.forecast_item_type
1348 and fi.delete_flag = 'N'
1349 and fi.delete_flag = fi_week.delete_flag
1350 and (fi.availability_quantity > 0 or fi.overcommitment_quantity > 0)
1351 and (fi_week.availability_flag = 'Y' or fi_week.overcommitment_flag = 'Y')
1352 and col.lookup_code = decode(fi.availability_flag, 'Y', 'AVAILABLE', 'N',
1353 decode(sign(fi.overcommitment_quantity - fi.capacity_quantity*g_overcommitment_percentage), 1, 'OVERCOMMITTED', 0, 'AVAILABLE')) --For Bug 8812042
1354 GROUP BY fi.resource_id,
1355 fi.global_exp_period_end_date,
1356 fi_week.availability_flag,
1357 col.file_name,
1358 col.render_priority
1359 UNION ALL
1360 select
1361 'ASSIGNMENT' time_chart_record_type,
1362 fi.assignment_id row_label_id,
1363 fi.global_exp_period_end_date-6 start_date,
1364 fi.global_exp_period_end_date end_date,
1365 fi.global_exp_period_end_date week_end_date,
1366 'THREE_MONTH' scale_type,
1367 sum(fi.item_quantity) quantity,
1368 col.render_priority,
1369 col.file_name color_file_name
1370 from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1371 where fi.resource_id = p_resource_id
1372 and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1373 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1374 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1375 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
1376 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
1377 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
1378 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
1379 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
1380 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
1381 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
1382 and fi.error_flag in ('N','Y') --Bug#9479220
1383 and fi.assignment_id = asgn.assignment_id
1384 and fi.forecast_item_type = 'A'
1385 and fi.delete_flag = 'N'
1386 and fi.item_date between p_start_date and p_end_date
1387 and col.lookup_code = decode(asgn.assignment_type,
1388 'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1389 'STAFFED_ASGMT_CONF',
1390 'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
1391 'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1392 'STAFFED_ASGMT_CONF',
1393 'STAFFED_ASGMT_CONF',
1394 'STAFFED_ASGMT_PROV'))
1395 group by fi.assignment_id,
1396 fi.global_exp_period_end_date,
1397 col.render_priority,
1398 col.file_name
1399 );
1400
1401 ELSIF g_availability_cal_period = 'WEEKLY' AND p_scale_type = 'THREE_MONTH' and prm_license <> 'Y' THEN
1402 pa_timeline_util.debug('Resource Details: weekly | three_month');
1403 INSERT INTO pa_time_chart_temp (
1404 select
1405 'ASSIGNMENT' time_chart_record_type,
1406 fi.assignment_id row_label_id,
1407 fi.global_exp_period_end_date-6 start_date,
1408 fi.global_exp_period_end_date end_date,
1409 fi.global_exp_period_end_date week_end_date,
1410 'THREE_MONTH' scale_type,
1411 sum(fi.item_quantity) quantity,
1412 col.render_priority,
1413 col.file_name color_file_name
1414 from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1415 where fi.resource_id = p_resource_id
1416 and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1417 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1418 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1419 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
1420 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
1421 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
1422 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
1423 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
1424 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
1425 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
1426 and fi.error_flag in ('N','Y') --Bug#9479220
1427 and fi.assignment_id = asgn.assignment_id
1428 and fi.forecast_item_type = 'A'
1429 and fi.delete_flag = 'N'
1430 and fi.item_date between p_start_date and p_end_date
1431 and col.lookup_code = decode(asgn.assignment_type,
1432 'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1433 'STAFFED_ASGMT_CONF',
1434 'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
1435 'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1436 'STAFFED_ASGMT_CONF',
1437 'STAFFED_ASGMT_CONF',
1438 'STAFFED_ASGMT_PROV'))
1439 group by fi.assignment_id,
1440 fi.global_exp_period_end_date,
1441 col.render_priority,
1442 col.file_name
1443 );
1444
1445
1446 END IF;
1447
1448 -- Resource Overcommitment Timeline
1449 ELSIF p_timeline_type = 'ResourceOvercommitmentCalc' THEN
1450 IF g_availability_cal_period = 'DAILY' and p_scale_type = 'MONTH' and prm_license = 'Y' THEN
1451 INSERT INTO pa_time_chart_temp (
1452 select
1453 'OVERCOMMITTED' time_chart_record_type,
1454 -100 row_label_id,
1455 fi.item_date start_date,
1456 fi.item_date end_date,
1457 fi.global_exp_period_end_date week_end_date,
1458 'MONTH' scale_type,
1459 (fi_assigned.assigned_quantity-fi.capacity_quantity) quantity,
1460 col.render_priority,
1461 col.file_name color_file_name
1462 from pa_forecast_items fi, pa_timeline_colors col,
1463 (select resource_id,
1464 sum(item_quantity) assigned_quantity,
1465 item_date,
1466 delete_flag
1467 from
1468 (select fi1.resource_id,
1469 fi1.item_quantity,
1470 fi1.item_date,
1471 fi1.delete_flag
1472 from pa_forecast_items fi1, pa_project_assignments asgn, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
1473 where (fi1.assignment_id = p_assignment_id
1474 or fi1.assignment_id in
1475 (select conflict_assignment_id
1476 from pa_assignment_conflict_hist
1477 where assignment_id = p_assignment_id
1478 and conflict_group_id = p_conflict_group_id
1479 and self_conflict_flag = 'N'
1480 and intra_txn_conflict_flag = 'Y'))
1481 and fi1.assignment_id = asgn.assignment_id
1482 and asgn.assignment_id = sch.assignment_id
1483 and asgn.apprvl_status_code NOT IN ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
1484 and fi1.item_date between sch.start_date and sch.end_date
1485 and sch.status_code = a.project_status_code
1486 and a.wf_success_status_code = b.project_status_code
1487 and b.project_system_status_code = 'STAFFED_ASGMT_CONF'
1488 and fi1.forecast_item_type = 'A'
1489 UNION ALL
1490 select fi2.resource_id,
1491 item_quantity,
1492 fi2.item_date,
1493 fi2.delete_flag
1494 from pa_forecast_items fi2, pa_project_assignments asgn, pa_assignment_conflict_hist hist
1495 where fi2.assignment_id = asgn.assignment_id
1496 and fi2.assignment_id = hist.conflict_assignment_id
1497 and hist.conflict_group_id = p_conflict_group_id
1498 and hist.assignment_id = p_assignment_id
1499 and hist.self_conflict_flag = 'N'
1500 and fi2.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
1501 and ((asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED') and hist.intra_txn_conflict_flag = 'Y')
1502 or hist.intra_txn_conflict_flag = 'N')
1503 and fi2.forecast_item_type = 'A'
1504 UNION ALL
1505 select fi2.resource_id,
1506 item_quantity,
1507 fi2.item_date,
1508 fi2.delete_flag
1509 from pa_forecast_items fi2, pa_project_assignments asgn
1510 where fi2.assignment_id = p_assignment_id
1511 and fi2.assignment_id = asgn.assignment_id
1512 and fi2.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
1513 and asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
1514 and fi2.forecast_item_type = 'A'
1515 )
1516 group by resource_id, item_date, delete_flag
1517 )FI_ASSIGNED
1518 where fi.forecast_item_type = 'U'
1519 and ((fi_assigned.assigned_quantity-fi.capacity_quantity*(1+G_OVERCOMMITMENT_PERCENTAGE) > 0 and G_OVERCOMMITMENT_PERCENTAGE > 0)
1520 or(fi_assigned.assigned_quantity-fi.capacity_quantity > 0 and G_OVERCOMMITMENT_PERCENTAGE = 0)) -- Bug 8627070
1521 and fi.delete_flag = 'N'
1522 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
1523 and fi.error_flag in ('N','Y') --Bug#9479220
1524 and fi.delete_flag = fi_assigned.delete_flag
1525 and fi.item_date between p_start_date and p_end_date
1526 and fi.item_date = fi_assigned.item_date
1527 and fi.resource_id = p_resource_id
1528 and fi.resource_id = fi_assigned.resource_id
1529 and col.lookup_code = 'OVERCOMMITTED'
1530 UNION ALL
1531 select
1532 'ASSIGNMENT' time_chart_record_type,
1533 fi.assignment_id row_label_id,fi.item_date start_date,
1534 fi.item_date end_date,
1535 fi.global_exp_period_end_date week_end_date,
1536 'MONTH' scale_type,
1537 fi.item_quantity quantity,
1538 col.render_priority,
1539 col.file_name color_file_name
1540 from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1541 where fi.resource_id = p_resource_id
1542 and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1543 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1544 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1545 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
1546 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
1547 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
1548 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
1549 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
1550 l_row_label_id_tbl(24), l_row_label_id_tbl(25)))
1551 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
1552 and fi.error_flag in ('N','Y') --Bug#9479220
1553 and fi.assignment_id = asgn.assignment_id
1554 and fi.forecast_item_type = 'A'
1555 and fi.delete_flag = 'N'
1556 and fi.item_date between p_start_date and p_end_date
1557 and col.lookup_code = decode(asgn.assignment_type,
1558 'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1559 'STAFFED_ASGMT_CONF',
1560 'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
1561 'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1562 'STAFFED_ASGMT_CONF',
1563 'STAFFED_ASGMT_CONF',
1564 'STAFFED_ASGMT_PROV'))
1565 );
1566
1567 ELSIF g_availability_cal_period = 'DAILY' and p_scale_type = 'MONTH' and prm_license <> 'Y' THEN
1568 INSERT INTO pa_time_chart_temp (
1569 select
1570 'ASSIGNMENT' time_chart_record_type,
1571 fi.assignment_id row_label_id,fi.item_date start_date,
1572 fi.item_date end_date,
1573 fi.global_exp_period_end_date week_end_date,
1574 'MONTH' scale_type,
1575 fi.item_quantity quantity,
1576 col.render_priority,
1577 col.file_name color_file_name
1578 from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1579 where fi.resource_id = p_resource_id
1580 and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1581 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1582 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1583 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
1584 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
1585 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
1586 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
1587 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
1588 l_row_label_id_tbl(24), l_row_label_id_tbl(25)))
1589 and fi.assignment_id = asgn.assignment_id
1590 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
1591 and fi.error_flag in ('N','Y') --Bug#9479220
1592 and fi.forecast_item_type = 'A'
1593 and fi.delete_flag = 'N'
1594 and fi.item_date between p_start_date and p_end_date
1595 and col.lookup_code = decode(asgn.assignment_type,
1596 'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1597 'STAFFED_ASGMT_CONF',
1598 'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
1599 'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1600 'STAFFED_ASGMT_CONF',
1601 'STAFFED_ASGMT_CONF',
1602 'STAFFED_ASGMT_PROV'))
1603 );
1604
1605 ELSIF g_availability_cal_period = 'DAILY' and p_scale_type = 'THREE_MONTH' and prm_license = 'Y' THEN
1606 INSERT INTO pa_time_chart_temp (
1607 select
1608 'OVERCOMMITTED' time_chart_record_type,
1609 -100 row_label_id,
1610 fi.global_exp_period_end_date - 6 start_date,
1611 fi.global_exp_period_end_date end_date,
1612 fi.global_exp_period_end_date week_end_date,
1613 'THREE_MONTH' scale_type,
1614 sum(fi_assigned.assigned_quantity-fi.capacity_quantity) quantity,
1615 col.render_priority,
1616 col.file_name color_file_name
1617 from pa_forecast_items fi, pa_timeline_colors col,
1618 (select resource_id,
1619 sum(item_quantity) assigned_quantity,
1620 item_date,
1621 delete_flag
1622 from
1623 (select fi1.resource_id,
1624 fi1.item_quantity,
1625 fi1.item_date,
1626 fi1.delete_flag
1627 from pa_forecast_items fi1, pa_project_assignments asgn, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
1628 where (fi1.assignment_id = p_assignment_id
1629 or fi1.assignment_id in
1630 (select conflict_assignment_id
1631 from pa_assignment_conflict_hist
1632 where assignment_id = p_assignment_id
1633 and conflict_group_id = p_conflict_group_id
1634 and self_conflict_flag = 'N'
1635 and intra_txn_conflict_flag = 'Y'))
1636 and fi1.assignment_id = asgn.assignment_id
1637 and asgn.assignment_id = sch.assignment_id
1638 and asgn.apprvl_status_code NOT IN ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
1639 and fi1.item_date between sch.start_date and sch.end_date
1640 and sch.status_code = a.project_status_code
1641 and a.wf_success_status_code = b.project_status_code
1642 and b.project_system_status_code = 'STAFFED_ASGMT_CONF'
1643 and fi1.forecast_item_type = 'A'
1644 UNION ALL
1645 select fi2.resource_id,
1646 item_quantity,
1647 fi2.item_date,
1648 fi2.delete_flag
1649 from pa_forecast_items fi2, pa_project_assignments asgn, pa_assignment_conflict_hist hist
1650 where fi2.assignment_id = asgn.assignment_id
1651 and fi2.assignment_id = hist.conflict_assignment_id
1652 and hist.conflict_group_id = p_conflict_group_id
1653 and hist.assignment_id = p_assignment_id
1654 and hist.self_conflict_flag = 'N'
1655 and fi2.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
1656 and ((asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED') and hist.intra_txn_conflict_flag = 'Y')
1657 or hist.intra_txn_conflict_flag = 'N')
1658 and fi2.forecast_item_type = 'A'
1659 UNION ALL
1660 select fi2.resource_id,
1661 item_quantity,
1662 fi2.item_date,
1663 fi2.delete_flag
1664 from pa_forecast_items fi2, pa_project_assignments asgn
1665 where fi2.assignment_id = p_assignment_id
1666 and fi2.assignment_id = asgn.assignment_id
1667 and fi2.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
1668 and asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
1669 and fi2.forecast_item_type = 'A'
1670 )
1671 group by resource_id, item_date, delete_flag
1672 )FI_ASSIGNED
1673 where forecast_item_type = 'U'
1674 and ((fi_assigned.assigned_quantity-fi.capacity_quantity*(1+G_OVERCOMMITMENT_PERCENTAGE) > 0 and G_OVERCOMMITMENT_PERCENTAGE > 0)
1675 or(fi_assigned.assigned_quantity-fi.capacity_quantity > 0 and G_OVERCOMMITMENT_PERCENTAGE = 0)) -- Bug 8627070
1676 and fi.delete_flag = 'N'
1677 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
1678 and fi.error_flag in ('N','Y') --Bug#9479220
1679 and fi.delete_flag = fi_assigned.delete_flag
1680 and fi.item_date between p_start_date and p_end_date
1681 and fi.item_date = fi_assigned.item_date
1682 and fi.resource_id = p_resource_id
1683 and fi.resource_id = fi_assigned.resource_id
1684 and col.lookup_code = 'OVERCOMMITTED'
1685 GROUP BY fi.resource_id,
1686 fi.global_exp_period_end_date,
1687 col.file_name,
1688 col.render_priority
1689 UNION ALL
1690 select
1691 'ASSIGNMENT' time_chart_record_type,
1692 fi.assignment_id row_label_id,
1693 fi.global_exp_period_end_date-6 start_date,
1694 fi.global_exp_period_end_date end_date,
1695 fi.global_exp_period_end_date week_end_date,
1696 'THREE_MONTH' scale_type,
1697 sum(fi.item_quantity) quantity,
1698 col.render_priority,
1699 col.file_name color_file_name
1700 from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1701 where fi.resource_id = p_resource_id
1702 and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1703 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1704 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1705 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
1706 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
1707 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
1708 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
1709 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
1710 l_row_label_id_tbl(24), l_row_label_id_tbl(25)))
1711 and fi.assignment_id = asgn.assignment_id
1712 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
1713 and fi.error_flag in ('N','Y') --Bug#9479220
1714 and fi.forecast_item_type = 'A'
1715 and fi.delete_flag = 'N'
1716 and fi.item_date between p_start_date and p_end_date
1717 and col.lookup_code = decode(asgn.assignment_type,
1718 'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1719 'STAFFED_ASGMT_CONF',
1720 'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
1721 'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1722 'STAFFED_ASGMT_CONF',
1723 'STAFFED_ASGMT_CONF',
1724 'STAFFED_ASGMT_PROV'))
1725 GROUP BY fi.assignment_id,
1726 fi.global_exp_period_end_date,
1727 col.file_name,
1728 col.render_priority
1729 );
1730
1731 ELSIF g_availability_cal_period = 'DAILY' and p_scale_type = 'THREE_MONTH' and prm_license <> 'Y' THEN
1732 INSERT INTO pa_time_chart_temp (
1733 select
1734 'ASSIGNMENT' time_chart_record_type,
1735 fi.assignment_id row_label_id,
1736 fi.global_exp_period_end_date-6 start_date,
1737 fi.global_exp_period_end_date end_date,
1738 fi.global_exp_period_end_date week_end_date,
1739 'THREE_MONTH' scale_type,
1740 sum(fi.item_quantity) quantity,
1741 col.render_priority,
1742 col.file_name color_file_name
1743 from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1744 where fi.resource_id = p_resource_id
1745 and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1746 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1747 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1748 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
1749 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
1750 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
1751 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
1752 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
1753 l_row_label_id_tbl(24), l_row_label_id_tbl(25)))
1754 and fi.assignment_id = asgn.assignment_id
1755 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
1756 and fi.error_flag in ('N','Y') --Bug#9479220
1757 and fi.forecast_item_type = 'A'
1758 and fi.delete_flag = 'N'
1759 and fi.item_date between p_start_date and p_end_date
1760 and col.lookup_code = decode(asgn.assignment_type,
1761 'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1762 'STAFFED_ASGMT_CONF',
1763 'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
1764 'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1765 'STAFFED_ASGMT_CONF',
1766 'STAFFED_ASGMT_CONF',
1767 'STAFFED_ASGMT_PROV'))
1768 GROUP BY fi.assignment_id,
1769 fi.global_exp_period_end_date,
1770 col.file_name,
1771 col.render_priority
1772 );
1773
1774 ELSIF g_availability_cal_period = 'WEEKLY' and p_scale_type = 'MONTH' and prm_license = 'Y' THEN
1775 INSERT INTO pa_time_chart_temp (
1776 select
1777 'OVERCOMMITTED' time_chart_record_type,
1778 -100 row_label_id,
1779 fi.item_date start_date,
1780 fi.item_date end_date,
1781 fi. global_exp_period_end_date week_end_date,
1782 'MONTH' scale_type,
1783 (fi_assigned.assigned_quantity-fi.capacity_quantity) quantity,
1784 col.render_priority,
1785 col.file_name color_file_name
1786 from pa_forecast_items fi, pa_timeline_colors col,
1787 (select resource_id,
1788 sum(item_quantity) assigned_quantity,
1789 item_date,
1790 delete_flag
1791 from
1792 (select fi1.resource_id,
1793 item_quantity,
1794 fi1.item_date,
1795 fi1.delete_flag
1796 from pa_forecast_items fi1, pa_project_assignments asgn, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
1797 where (fi1.assignment_id = p_assignment_id
1798 or fi1.assignment_id in
1799 (select conflict_assignment_id
1800 from pa_assignment_conflict_hist
1801 where assignment_id = p_assignment_id
1802 and conflict_group_id = p_conflict_group_id
1803 and self_conflict_flag = 'N'
1804 and intra_txn_conflict_flag = 'Y'))
1805 and fi1.assignment_id = asgn.assignment_id
1806 and asgn.assignment_id = sch.assignment_id
1807 and asgn.apprvl_status_code NOT IN ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
1808 and fi1.item_date between sch.start_date and sch.end_date
1809 and sch.status_code = a.project_status_code
1810 and a.wf_success_status_code = b.project_status_code
1811 and b.project_system_status_code = 'STAFFED_ASGMT_CONF'
1812 and forecast_item_type = 'A'
1813 UNION ALL
1814 select fi2.resource_id,
1815 item_quantity,
1816 fi2.item_date,
1817 fi2.delete_flag
1818 from pa_forecast_items fi2, pa_project_assignments asgn, pa_assignment_conflict_hist hist
1819 where fi2.assignment_id = asgn.assignment_id
1820 and fi2.assignment_id = hist.conflict_assignment_id
1821 and hist.conflict_group_id = p_conflict_group_id
1822 and hist.assignment_id = p_assignment_id
1823 and hist.self_conflict_flag = 'N'
1824 and fi2.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
1825 and ((asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED') and hist.intra_txn_conflict_flag = 'Y')
1826 or hist.intra_txn_conflict_flag = 'N')
1827 and fi2.forecast_item_type = 'A'
1828 UNION ALL
1829 select fi2.resource_id,
1830 item_quantity,
1831 fi2.item_date,
1832 fi2.delete_flag
1833 from pa_forecast_items fi2, pa_project_assignments asgn
1834 where fi2.assignment_id = p_assignment_id
1835 and fi2.assignment_id = asgn.assignment_id
1836 and fi2.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
1837 and asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
1838 and fi2.forecast_item_type = 'A'
1839 )
1840 group by resource_id, item_date, delete_flag
1841 )FI_ASSIGNED,
1842 (select fi_week_capacity.resource_id,
1843 decode(sign((fi_week_assigned.assigned_quantity-fi_week_capacity.capacity_quantity)-fi_week_capacity.capacity_quantity*G_OVERCOMMITMENT_PERCENTAGE),
1844 -1, 'N',
1845 0, decode(fi_week_assigned.assigned_quantity-fi_week_capacity.capacity_quantity, 0, 'N', 'Y'),
1846 1, 'Y') overcom_flag,
1847 fi_week_capacity.global_exp_period_end_date,
1848 fi_week_capacity.delete_flag
1849 from
1850 (select resource_id,
1851 sum(item_quantity) assigned_quantity,
1852 global_exp_period_end_date,
1853 delete_flag
1854 from
1855 (select fi3.resource_id,
1856 fi3.item_quantity,
1857 fi3.global_exp_period_end_date,
1858 fi3.delete_flag
1859 from pa_forecast_items fi3, pa_project_assignments asgn, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
1860 where (fi3.assignment_id = p_assignment_id
1861 or fi3.assignment_id in
1862 (select conflict_assignment_id
1863 from pa_assignment_conflict_hist
1864 where assignment_id = p_assignment_id
1865 and conflict_group_id = p_conflict_group_id
1866 and self_conflict_flag = 'N'
1867 and intra_txn_conflict_flag = 'Y'))
1868 and fi3.assignment_id = asgn.assignment_id
1869 and asgn.assignment_id = sch.assignment_id
1870 and asgn.apprvl_status_code NOT IN ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
1871 and fi3.item_date between sch.start_date and sch.end_date
1872 and sch.status_code = a.project_status_code
1873 and a.wf_success_status_code = b.project_status_code
1874 and b.project_system_status_code = 'STAFFED_ASGMT_CONF'
1875 and fi3.forecast_item_type = 'A'
1876 UNION ALL
1877 select fi4.resource_id,
1878 fi4.item_quantity,
1879 fi4.global_exp_period_end_date,
1880 fi4.delete_flag
1881 from pa_forecast_items fi4, pa_project_assignments asgn, pa_assignment_conflict_hist hist
1882 where fi4.assignment_id = asgn.assignment_id
1883 and fi4.assignment_id = hist.conflict_assignment_id
1884 and hist.conflict_group_id = p_conflict_group_id
1885 and hist.assignment_id = p_assignment_id
1886 and hist.self_conflict_flag = 'N'
1887 and fi4.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
1888 and ((asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED') and hist.intra_txn_conflict_flag = 'Y')
1889 or hist.intra_txn_conflict_flag = 'N')
1890 and fi4.forecast_item_type = 'A'
1891 UNION ALL
1892 select fi4.resource_id,
1893 item_quantity,
1894 fi4.global_exp_period_end_date,
1895 fi4.delete_flag
1896 from pa_forecast_items fi4, pa_project_assignments asgn
1897 where fi4.assignment_id = p_assignment_id
1898 and fi4.assignment_id = asgn.assignment_id
1899 and fi4.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
1900 and asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
1901 and fi4.forecast_item_type = 'A')
1902 group by resource_id, global_exp_period_end_date, delete_flag) FI_WEEK_ASSIGNED,
1903 (select resource_id,
1904 sum(capacity_quantity) capacity_quantity,
1905 global_exp_period_end_date,
1906 delete_flag
1907 from pa_forecast_items
1908 where forecast_item_type = 'U'
1909 group by resource_id, global_exp_period_end_date, delete_flag) FI_WEEK_CAPACITY
1910 where fi_week_capacity.resource_id = fi_week_assigned.resource_id
1911 and fi_week_capacity.global_exp_period_end_date = fi_week_assigned.global_exp_period_end_date
1912 and fi_week_capacity.delete_flag = fi_week_assigned.delete_flag
1913 )FI_WEEK
1914 where fi.resource_id = p_resource_id
1915 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
1916 and fi.error_flag in ('N','Y') --Bug#9479220
1917 and fi.resource_id = fi_assigned.resource_id
1918 and fi_assigned.resource_id = fi_week.resource_id
1919 and fi.forecast_item_type = 'U'
1920 and fi.delete_flag = 'N'
1921 and fi.delete_flag = fi_assigned.delete_flag
1922 and fi_assigned.delete_flag = fi_week.delete_flag
1923 and fi.item_date between p_start_date and p_end_date
1924 and fi.item_date = fi_assigned.item_date
1925 and fi.global_exp_period_end_date = fi_week.global_exp_period_end_date
1926 and fi_week.overcom_flag = 'Y'
1927 and col.lookup_code = 'OVERCOMMITTED'
1928 UNION ALL
1929 select
1930 'ASSIGNMENT' time_chart_record_type,
1931 fi.assignment_id row_label_id,
1932 fi.item_date start_date,
1933 fi.item_date end_date,
1934 fi.global_exp_period_end_date week_end_date,
1935 'MONTH' scale_type,
1936 fi.item_quantity quantity,
1937 col.render_priority,
1938 col.file_name color_file_name
1939 from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1940 where fi.resource_id = p_resource_id
1941 and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1942 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1943 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1944 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
1945 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
1946 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
1947 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
1948 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
1949 l_row_label_id_tbl(24), l_row_label_id_tbl(25)))
1950 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
1951 and fi.error_flag in ('N','Y') --Bug#9479220
1952 and fi.assignment_id = asgn.assignment_id
1953 and fi.forecast_item_type = 'A'
1954 and fi.delete_flag = 'N'
1955 and fi.item_date between p_start_date and p_end_date
1956 and col.lookup_code = decode(asgn.assignment_type,
1957 'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1958 'STAFFED_ASGMT_CONF',
1959 'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
1960 'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1961 'STAFFED_ASGMT_CONF',
1962 'STAFFED_ASGMT_CONF',
1963 'STAFFED_ASGMT_PROV'))
1964 );
1965
1966 ELSIF g_availability_cal_period = 'WEEKLY' and p_scale_type = 'MONTH' and prm_license <> 'Y' THEN
1967 INSERT INTO pa_time_chart_temp (
1968 select
1969 'ASSIGNMENT' time_chart_record_type,
1970 fi.assignment_id row_label_id,
1971 fi.item_date start_date,
1972 fi.item_date end_date,
1973 fi.global_exp_period_end_date week_end_date,
1974 'MONTH' scale_type,
1975 fi.item_quantity quantity,
1976 col.render_priority,
1977 col.file_name color_file_name
1978 from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
1979 where fi.resource_id = p_resource_id
1980 and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
1981 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
1982 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
1983 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
1984 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
1985 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
1986 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
1987 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
1988 l_row_label_id_tbl(24), l_row_label_id_tbl(25)))
1989 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
1990 and fi.error_flag in ('N','Y') --Bug#9479220
1991 and fi.assignment_id = asgn.assignment_id
1992 and fi.forecast_item_type = 'A'
1993 and fi.delete_flag = 'N'
1994 and fi.item_date between p_start_date and p_end_date
1995 and col.lookup_code = decode(asgn.assignment_type,
1996 'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
1997 'STAFFED_ASGMT_CONF',
1998 'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
1999 'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
2000 'STAFFED_ASGMT_CONF',
2001 'STAFFED_ASGMT_CONF',
2002 'STAFFED_ASGMT_PROV'))
2003 );
2004
2005 ELSIF g_availability_cal_period = 'WEEKLY' and p_scale_type = 'THREE_MONTH' and prm_license = 'Y' THEN
2006 INSERT INTO pa_time_chart_temp (
2007 select
2008 'OVERCOMMITTED' time_chart_record_type,
2009 -100 row_label_id,
2010 fi_week.global_exp_period_end_date-6 start_date,
2011 fi_week.global_exp_period_end_date end_date,
2012 fi_week.global_exp_period_end_date week_end_date,
2013 'THREE_MONTH' scale_type,
2014 fi_week.overcom_quantity quantity,
2015 col.render_priority,
2016 col.file_name color_file_name
2017 from pa_timeline_colors col,
2018 (select
2019 fi_week_capacity.resource_id,
2020 decode(sign((fi_week_assigned.assigned_quantity-fi_week_capacity.capacity_quantity)-fi_week_capacity.capacity_quantity*G_OVERCOMMITMENT_PERCENTAGE),
2021 -1, 0, fi_week_assigned.assigned_quantity-fi_week_capacity.capacity_quantity) overcom_quantity,
2022 fi_week_capacity.global_exp_period_end_date,
2023 fi_week_capacity.delete_flag
2024 from
2025 (select resource_id,
2026 sum(item_quantity) assigned_quantity,
2027 global_exp_period_end_date,
2028 delete_flag
2029 from
2030 (select fi3.resource_id,
2031 fi3.item_quantity,
2032 fi3.global_exp_period_end_date,
2033 fi3.delete_flag
2034 from pa_forecast_items fi3, pa_project_assignments asgn, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
2035 where (fi3.assignment_id = p_assignment_id
2036 or fi3.assignment_id in
2037 (select conflict_assignment_id
2038 from pa_assignment_conflict_hist
2039 where assignment_id = p_assignment_id
2040 and conflict_group_id = p_conflict_group_id
2041 and self_conflict_flag = 'N'
2042 and intra_txn_conflict_flag = 'Y'))
2043 and fi3.assignment_id = asgn.assignment_id
2044 and asgn.assignment_id = sch.assignment_id
2045 and asgn.apprvl_status_code NOT IN ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
2046 and fi3.item_date between sch.start_date and sch.end_date
2047 and sch.status_code = a.project_status_code
2048 and a.wf_success_status_code = b.project_status_code
2049 and b.project_system_status_code = 'STAFFED_ASGMT_CONF'
2050 and fi3.forecast_item_type = 'A'
2051 UNION ALL
2052 select fi4.resource_id,
2053 fi4.item_quantity,
2054 fi4.global_exp_period_end_date,
2055 fi4.delete_flag
2056 from pa_forecast_items fi4, pa_project_assignments asgn, pa_assignment_conflict_hist hist
2057 where fi4.assignment_id = asgn.assignment_id
2058 and fi4.assignment_id = hist.conflict_assignment_id
2059 and hist.conflict_group_id = p_conflict_group_id
2060 and hist.assignment_id = p_assignment_id
2061 and hist.self_conflict_flag = 'N'
2062 and fi4.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
2063 and ((asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED') and hist.intra_txn_conflict_flag = 'Y')
2064 or hist.intra_txn_conflict_flag = 'N')
2065 and fi4.forecast_item_type = 'A'
2066 UNION ALL
2067 select fi4.resource_id,
2068 item_quantity,
2069 fi4.global_exp_period_end_date,
2070 fi4.delete_flag
2071 from pa_forecast_items fi4, pa_project_assignments asgn
2072 where fi4.assignment_id = p_assignment_id
2073 and fi4.assignment_id = asgn.assignment_id
2074 and fi4.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
2075 and asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
2076 and fi4.forecast_item_type = 'A')
2077 group by resource_id, global_exp_period_end_date, delete_flag) FI_WEEK_ASSIGNED,
2078 (select resource_id,
2079 sum(capacity_quantity) capacity_quantity,
2080 global_exp_period_end_date,
2081 delete_flag
2082 from pa_forecast_items
2083 where forecast_item_type = 'U'
2084 group by resource_id, global_exp_period_end_date, delete_flag) FI_WEEK_CAPACITY
2085 where fi_week_capacity.resource_id = fi_week_assigned.resource_id
2086 and fi_week_capacity.global_exp_period_end_date = fi_week_assigned.global_exp_period_end_date
2087 and fi_week_capacity.delete_flag = fi_week_assigned.delete_flag
2088 )FI_WEEK
2089 where fi_week.resource_id = p_resource_id
2090 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2091 and fi_week.delete_flag = 'N'
2092 and fi_week.overcom_quantity > 0
2093 and fi_week.global_exp_period_end_date between p_start_date and p_end_date
2094 and col.lookup_code = 'OVERCOMMITTED'
2095 UNION ALL
2096 select
2097 'ASSIGNMENT' time_chart_record_type,
2098 fi.assignment_id row_label_id,
2099 fi.global_exp_period_end_date-6 start_date,
2100 fi.global_exp_period_end_date end_date,
2101 fi.global_exp_period_end_date week_end_date,
2102 'THREE_MONTH' scale_type,
2103 sum(fi.item_quantity) quantity,
2104 col.render_priority,
2105 col.file_name color_file_name
2106 from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
2107 where fi.resource_id = p_resource_id
2108 and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2109 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
2110 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
2111 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
2112 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
2113 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
2114 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
2115 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
2116 l_row_label_id_tbl(24), l_row_label_id_tbl(25)))
2117 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2118 and fi.error_flag in ('N','Y') --Bug#9479220
2119 and fi.assignment_id = asgn.assignment_id
2120 and fi.forecast_item_type = 'A'
2121 and fi.delete_flag = 'N'
2122 and fi.item_date between p_start_date and p_end_date
2123 and col.lookup_code = decode(asgn.assignment_type,
2124 'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
2125 'STAFFED_ASGMT_CONF',
2126 'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
2127 'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
2128 'STAFFED_ASGMT_CONF',
2129 'STAFFED_ASGMT_CONF',
2130 'STAFFED_ASGMT_PROV'))
2131 group by fi.assignment_id,
2132 fi.global_exp_period_end_date,
2133 col.render_priority,
2134 col.file_name
2135 );
2136
2137 ELSIF g_availability_cal_period = 'WEEKLY' and p_scale_type = 'THREE_MONTH' and prm_license <> 'Y' THEN
2138 INSERT INTO pa_time_chart_temp (
2139 select
2140 'ASSIGNMENT' time_chart_record_type,
2141 fi.assignment_id row_label_id,
2142 fi.global_exp_period_end_date-6 start_date,
2143 fi.global_exp_period_end_date end_date,
2144 fi.global_exp_period_end_date week_end_date,
2145 'THREE_MONTH' scale_type,
2146 sum(fi.item_quantity) quantity,
2147 col.render_priority,
2148 col.file_name color_file_name
2149 from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
2150 where fi.resource_id = p_resource_id
2151 and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2152 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
2153 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
2154 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
2155 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
2156 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
2157 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
2158 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
2159 l_row_label_id_tbl(24), l_row_label_id_tbl(25)))
2160 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2161 and fi.error_flag in ('N','Y') --Bug#9479220
2162 and fi.assignment_id = asgn.assignment_id
2163 and fi.forecast_item_type = 'A'
2164 and fi.delete_flag = 'N'
2165 and fi.item_date between p_start_date and p_end_date
2166 and col.lookup_code = decode(asgn.assignment_type,
2167 'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
2168 'STAFFED_ASGMT_CONF',
2169 'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
2170 'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
2171 'STAFFED_ASGMT_CONF',
2172 'STAFFED_ASGMT_CONF',
2173 'STAFFED_ASGMT_PROV'))
2174 group by fi.assignment_id,
2175 fi.global_exp_period_end_date,
2176 col.render_priority,
2177 col.file_name
2178 );
2179 END IF;
2180
2181
2182 -- Team List Timeline.
2183 ELSIF p_timeline_type = 'ProjectAssignments' THEN
2184
2185 IF g_availability_cal_period = 'DAILY' and p_scale_type = 'MONTH' and prm_license = 'Y' THEN
2186 INSERT INTO pa_time_chart_temp (
2187 select
2188 decode(fi1.forecast_item_type, 'A', 'ASSIGNMENT', 'R', 'REQUIREMENT') time_chart_record_type,
2189 fi1.assignment_id row_label_id,
2190 fi1.item_date start_date,
2191 fi1.item_date end_date,
2192 fi1.global_exp_period_end_date week_end_date,
2193 'MONTH' scale_type,
2194 fi1.item_quantity quantity,
2195 col.render_priority,
2196 col.file_name color_file_name
2197 from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
2198 where fi1.forecast_item_type in ('A', 'R')
2199 and fi1.delete_flag = 'N'
2200 and fi1.item_date between p_start_date and p_end_date
2201 and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2202 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
2203 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
2204 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
2205 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
2206 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
2207 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
2208 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
2209 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
2210 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2211 and fi1.error_flag in ('N','Y') --Bug#9479220
2212 and fi1.assignment_id = asgn.assignment_id
2213 and fi1.delete_flag = 'N'
2214 and col.lookup_code = decode(asgn.assignment_type,
2215 'STAFFED_ADMIN_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
2216 'STAFFED_ASGMT_CONF',
2217 'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
2218 'STAFFED_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
2219 'STAFFED_ASGMT_CONF',
2220 'STAFFED_ASGMT_CONF',
2221 'STAFFED_ASGMT_PROV'),
2222 'OPEN_ASSIGNMENT', 'OPEN_ASGMT')
2223 /* Commenting for bug 3280808
2224 UNION ALL
2225 select
2226 'ASSIGNMENT' time_chart_record_type,
2227 fi1.assignment_id row_label_id,
2228 fi1.item_date start_date,
2229 fi1.item_date end_date,
2230 fi1.global_exp_period_end_date week_end_date,
2231 'MONTH' scale_type,
2232 fi_admin.item_quantity quantity,
2233 col.render_priority,
2234 col.file_name color_file_name
2235 from pa_forecast_items fi1, pa_timeline_colors col,
2236 (select fi.resource_id,
2237 fi.assignment_id,
2238 fi.global_exp_period_end_date week_end_date,
2239 fi.item_date,
2240 fi.item_quantity,
2241 fi.forecast_item_type,
2242 fi.delete_flag
2243 from pa_forecast_items fi, pa_project_assignments asgn
2244 where fi.assignment_id = asgn.assignment_id
2245 and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
2246 and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
2247 where fi1.item_date between p_start_date and p_end_date
2248 and fi1.forecast_item_type = 'A'
2249 and fi1.forecast_item_type = fi_admin.forecast_item_type
2250 and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2251 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
2252 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
2253 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
2254 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
2255 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
2256 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
2257 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
2258 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
2259 and fi1.resource_id = fi_admin.resource_id
2260 and fi1.item_date = fi_admin.item_date
2261 and fi1.delete_flag = 'N'
2262 and fi1.delete_flag = fi_admin.delete_flag
2263 and col.lookup_code = 'CONFIRMED_ADMIN'
2264 End of commenting for bug 3280808 */
2265 UNION ALL
2266 select
2267 'ASSIGNMENT' time_chart_record_type,
2268 fi1.assignment_id row_label_id,
2269 fi1.item_date start_date,
2270 fi1.item_date end_date,
2271 fi1.global_exp_period_end_date week_end_date,
2272 'MONTH' scale_type,
2273 fi_overcom.overcommitment_quantity quantity,
2274 col.render_priority,
2275 col.file_name color_file_name
2276 from pa_forecast_items fi1, pa_timeline_colors col,
2277 (select fi.resource_id,
2278 fi.item_date,
2279 fi.delete_flag,
2280 decode(sign(fi.capacity_quantity*g_overcommitment_percentage-fi.overcommitment_quantity), 1, 0, 0, 0, fi.overcommitment_quantity) overcommitment_quantity --For Bug 8812042
2281 from pa_forecast_items fi
2282 where fi.forecast_item_type = 'U' ) fi_overcom
2283 where fi1.resource_id = fi_overcom.resource_id
2284 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2285 and fi1.error_flag in ('N','Y') --Bug#9479220
2286 and fi1.forecast_item_type = 'A'
2287 and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2288 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
2289 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
2290 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
2291 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
2292 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
2293 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
2294 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
2295 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
2296 and fi1.item_date between p_start_date and p_end_date
2297 and fi1.item_date = fi_overcom.item_date
2298 and fi1.delete_flag = 'N'
2299 and fi1.delete_flag = fi_overcom.delete_flag
2300 and col.lookup_code = 'OVERCOMMITTED'
2301 and fi_overcom.overcommitment_quantity > 0
2302 );
2303
2304 ELSIF g_availability_cal_period = 'DAILY' and p_scale_type = 'MONTH' and prm_license <> 'Y' THEN
2305 INSERT INTO pa_time_chart_temp (
2306 select
2307 decode(fi1.forecast_item_type, 'A', 'ASSIGNMENT', 'R', 'REQUIREMENT') time_chart_record_type,
2308 fi1.assignment_id row_label_id,
2309 fi1.item_date start_date,
2310 fi1.item_date end_date,
2311 fi1.global_exp_period_end_date week_end_date,
2312 'MONTH' scale_type,
2313 fi1.item_quantity quantity,
2314 col.render_priority,
2315 col.file_name color_file_name
2316 from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
2317 where fi1.forecast_item_type in ('A', 'R')
2318 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2319 and fi1.error_flag in ('N','Y') --Bug#9479220
2320 and fi1.delete_flag = 'N'
2321 and fi1.item_date between p_start_date and p_end_date
2322 and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2323 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
2324 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
2325 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
2326 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
2327 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
2328 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
2329 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
2330 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
2331 and fi1.assignment_id = asgn.assignment_id
2332 and fi1.delete_flag = 'N'
2333 and col.lookup_code = decode(asgn.assignment_type,
2334 'STAFFED_ADMIN_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
2335 'STAFFED_ASGMT_CONF',
2336 'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
2337 'STAFFED_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
2338 'STAFFED_ASGMT_CONF',
2339 'STAFFED_ASGMT_CONF',
2340 'STAFFED_ASGMT_PROV'),
2341 'OPEN_ASSIGNMENT', 'OPEN_ASGMT')
2342 /* Commenting for bug 3280808
2343 UNION ALL
2344 select
2345 'ASSIGNMENT' time_chart_record_type,
2346 fi1.assignment_id row_label_id,
2347 fi1.item_date start_date,
2348 fi1.item_date end_date,
2349 fi1.global_exp_period_end_date week_end_date,
2350 'MONTH' scale_type,
2351 fi_admin.item_quantity quantity,
2352 col.render_priority,
2353 col.file_name color_file_name
2354 from pa_forecast_items fi1, pa_timeline_colors col,
2355 (select fi.resource_id,
2356 fi.assignment_id,
2357 fi.global_exp_period_end_date week_end_date,
2358 fi.item_date,
2359 fi.item_quantity,
2360 fi.forecast_item_type,
2361 fi.delete_flag
2362 from pa_forecast_items fi, pa_project_assignments asgn
2363 where fi.assignment_id = asgn.assignment_id
2364 and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
2365 and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
2366 where fi1.item_date between p_start_date and p_end_date
2367 and fi1.forecast_item_type = 'A'
2368 and fi1.forecast_item_type = fi_admin.forecast_item_type
2369 and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2370 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
2371 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
2372 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
2373 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
2374 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
2375 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
2376 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
2377 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
2378 and fi1.resource_id = fi_admin.resource_id
2379 and fi1.item_date = fi_admin.item_date
2380 and fi1.delete_flag = 'N'
2381 and fi1.delete_flag = fi_admin.delete_flag
2382 and col.lookup_code = 'CONFIRMED_ADMIN'
2383 End of commenting for bug 3280808 */
2384 );
2385
2386 ELSIF g_availability_cal_period = 'DAILY' and p_scale_type = 'THREE_MONTH' and prm_license = 'Y' THEN
2387 INSERT INTO pa_time_chart_temp (
2388 select
2389 decode(fi1.forecast_item_type, 'A', 'ASSIGNMENT', 'R', 'REQUIREMENT') time_chart_record_type,
2390 fi1.assignment_id row_label_id,
2391 fi1.global_exp_period_end_date-6 start_date,
2392 fi1.global_exp_period_end_date end_date,
2393 fi1.global_exp_period_end_date week_end_date,
2394 'THREE_MONTH' scale_type,
2395 sum(fi1.item_quantity) quantity,
2396 col.render_priority,
2397 col.file_name color_file_name
2398 from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
2399 where fi1.forecast_item_type in ('A', 'R')
2400 and fi1.delete_flag = 'N'
2401 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2402 and fi1.error_flag in ('N','Y') --Bug#9479220
2403 and fi1.item_date between p_start_date and p_end_date
2404 and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2405 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
2406 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
2407 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
2408 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
2409 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
2410 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
2411 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
2412 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
2413 and fi1.assignment_id = asgn.assignment_id
2414 and col.lookup_code = decode(asgn.assignment_type,
2415 'STAFFED_ADMIN_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
2416 'STAFFED_ASGMT_CONF',
2417 'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
2418 'STAFFED_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
2419 'STAFFED_ASGMT_CONF',
2420 'STAFFED_ASGMT_CONF',
2421 'STAFFED_ASGMT_PROV'),
2422 'OPEN_ASSIGNMENT', 'OPEN_ASGMT')
2423 group by fi1.assignment_id,
2424 fi1.global_exp_period_end_date,
2425 fi1.forecast_item_type,
2426 col.file_name,
2427 col.render_priority
2428 /* Commenting for bug 3280808
2429 UNION ALL
2430 select
2431 'ASSIGNMENT' time_chart_record_type,
2432 fi1.assignment_id row_label_id,
2433 trunc(fi1.global_exp_period_end_date)-6 start_date,
2434 trunc(fi1.global_exp_period_end_date) end_date,
2435 trunc(fi1.global_exp_period_end_date) week_end_date,
2436 'THREE_MONTH' scale_type,
2437 sum(fi_admin.item_quantity) quantity,
2438 col.render_priority,
2439 col.file_name color_file_name
2440 from pa_forecast_items fi1, pa_timeline_colors col,
2441 (select fi.resource_id,
2442 fi.assignment_id,
2443 fi.global_exp_period_end_date week_end_date,
2444 fi.item_date,
2445 fi.item_quantity,
2446 fi.forecast_item_type,
2447 fi.delete_flag
2448 from pa_forecast_items fi, pa_project_assignments asgn
2449 where fi.assignment_id = asgn.assignment_id
2450 and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
2451 and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
2452 where fi1.resource_id = fi_admin.resource_id
2453 and fi1.forecast_item_type = 'A'
2454 and fi1.forecast_item_type = fi_admin.forecast_item_type
2455 and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2456 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
2457 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
2458 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
2459 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
2460 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
2461 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
2462 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
2463 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
2464 and fi1.item_date between p_start_date and p_end_date
2465 and fi1.item_date = fi_admin.item_date
2466 and fi1.delete_flag = 'N'
2467 and fi1.delete_flag = fi_admin.delete_flag
2468 and col.lookup_code = 'CONFIRMED_ADMIN'
2469 group by fi1.assignment_id,
2470 fi1.global_exp_period_end_date,
2471 fi1.forecast_item_type,
2472 col.file_name,
2473 col.render_priority
2474 End of commenting for bug bug 3280808*/
2475 UNION ALL
2476 select
2477 'ASSIGNMENT' time_chart_record_type,
2478 fi1.assignment_id row_label_id,
2479 fi1.global_exp_period_end_date-6 start_date,
2480 fi1.global_exp_period_end_date end_date,
2481 fi1.global_exp_period_end_date week_end_date,
2482 'THREE_MONTH' scale_type,
2483 sum(fi_overcom.overcommitment_quantity) quantity,
2484 col.render_priority,
2485 col.file_name color_file_name
2486 from pa_forecast_items fi1, pa_timeline_colors col,
2487 (select fi.resource_id,
2488 fi.item_date,
2489 fi.delete_flag,
2490 decode(sign(fi.capacity_quantity*g_overcommitment_percentage-fi.overcommitment_quantity), 1, 0, 0, 0, fi.overcommitment_quantity) overcommitment_quantity --For Bug 8812042
2491 from pa_forecast_items fi
2492 where fi.forecast_item_type = 'U' ) fi_overcom
2493 where fi1.resource_id = fi_overcom.resource_id
2494 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2495 and fi1.error_flag in ('N','Y') --Bug#9479220
2496 and fi1.forecast_item_type = 'A'
2497 and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2498 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
2499 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
2500 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
2501 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
2502 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
2503 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
2504 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
2505 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
2506 and fi1.item_date between p_start_date and p_end_date
2507 and fi1.item_date = fi_overcom.item_date
2508 and fi1.delete_flag = 'N'
2509 and fi1.delete_flag = fi_overcom.delete_flag
2510 and col.lookup_code = 'OVERCOMMITTED'
2511 and fi_overcom.overcommitment_quantity > 0
2512 group by fi1.assignment_id,
2513 fi1.global_exp_period_end_date,
2514 col.file_name,
2515 col.render_priority
2516 );
2517
2518 ELSIF g_availability_cal_period = 'DAILY' and p_scale_type = 'THREE_MONTH' and prm_license <> 'Y' THEN
2519 INSERT INTO pa_time_chart_temp (
2520 select
2521 decode(fi1.forecast_item_type, 'A', 'ASSIGNMENT', 'R', 'REQUIREMENT') time_chart_record_type,
2522 fi1.assignment_id row_label_id,
2523 fi1.global_exp_period_end_date-6 start_date,
2524 fi1.global_exp_period_end_date end_date,
2525 fi1.global_exp_period_end_date week_end_date,
2526 'THREE_MONTH' scale_type,
2527 sum(fi1.item_quantity) quantity,
2528 col.render_priority,
2529 col.file_name color_file_name
2530 from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
2531 where fi1.forecast_item_type in ('A', 'R')
2532 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2533 and fi1.error_flag in ('N','Y') --Bug#9479220
2534 and fi1.delete_flag = 'N'
2535 and fi1.item_date between p_start_date and p_end_date
2536 and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2537 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
2538 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
2539 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
2540 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
2541 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
2542 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
2543 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
2544 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
2545 and fi1.assignment_id = asgn.assignment_id
2546 and col.lookup_code = decode(asgn.assignment_type,
2547 'STAFFED_ADMIN_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
2548 'STAFFED_ASGMT_CONF',
2549 'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
2550 'STAFFED_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
2551 'STAFFED_ASGMT_CONF',
2552 'STAFFED_ASGMT_CONF',
2553 'STAFFED_ASGMT_PROV'),
2554 'OPEN_ASSIGNMENT', 'OPEN_ASGMT')
2555 group by fi1.assignment_id,
2556 fi1.global_exp_period_end_date,
2557 fi1.forecast_item_type,
2558 col.file_name,
2559 col.render_priority
2560 /* Commenting for bug 3280808
2561 UNION ALL
2562 select
2563 'ASSIGNMENT' time_chart_record_type,
2564 fi1.assignment_id row_label_id,
2565 trunc(fi1.global_exp_period_end_date)-6 start_date,
2566 trunc(fi1.global_exp_period_end_date) end_date,
2567 trunc(fi1.global_exp_period_end_date) week_end_date,
2568 'THREE_MONTH' scale_type,
2569 sum(fi_admin.item_quantity) quantity,
2570 col.render_priority,
2571 col.file_name color_file_name
2572 from pa_forecast_items fi1, pa_timeline_colors col,
2573 (select fi.resource_id,
2574 fi.assignment_id,
2575 fi.global_exp_period_end_date week_end_date,
2576 fi.item_date,
2577 fi.item_quantity,
2578 fi.forecast_item_type,
2579 fi.delete_flag
2580 from pa_forecast_items fi, pa_project_assignments asgn
2581 where fi.assignment_id = asgn.assignment_id
2582 and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
2583 and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
2584 where fi1.resource_id = fi_admin.resource_id
2585 and fi1.forecast_item_type = 'A'
2586 and fi1.forecast_item_type = fi_admin.forecast_item_type
2587 and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2588 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
2589 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
2590 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
2591 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
2592 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
2593 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
2594 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
2595 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
2596 and fi1.item_date between p_start_date and p_end_date
2597 and fi1.item_date = fi_admin.item_date
2598 and fi1.delete_flag = 'N'
2599 and fi1.delete_flag = fi_admin.delete_flag
2600 and col.lookup_code = 'CONFIRMED_ADMIN'
2601 group by fi1.assignment_id,
2602 fi1.global_exp_period_end_date,
2603 fi1.forecast_item_type,
2604 col.file_name,
2605 col.render_priority
2606 End of commenting for bug bug 3280808*/
2607 );
2608
2609
2610 ELSIF g_availability_cal_period = 'WEEKLY' and p_scale_type = 'MONTH' and prm_license = 'Y' THEN
2611 -----------------------------------------------------------------
2612 -- Bug Reference : 6524548
2613 -- We are restricting the Index Scan (ROW_ID) on PA_FORECAST_ITEMS
2614 -- By supplying the Resource Ids for the assignments by joining
2615 -- PA_PROJECT_ASSIGNMENTS and Further filtering on DELETE FLAG ('N')
2616 -- As DELETE_FLAG having value 'Y' are records which are eligible
2617 -- purge and we need not scan them too.
2618 ------------------------------------------------------------------
2619 INSERT INTO pa_time_chart_temp (
2620 select
2621 decode(fi1.forecast_item_type, 'A', 'ASSIGNMENT', 'R', 'REQUIREMENT') time_chart_record_type,
2622 fi1.assignment_id row_label_id,
2623 fi1.item_date start_date,
2624 fi1.item_date end_date,
2625 fi1.global_exp_period_end_date week_end_date,
2626 'MONTH' scale_type,
2627 fi1.item_quantity quantity,
2628 col.render_priority,
2629 col.file_name color_file_name
2630 from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
2631 where fi1.forecast_item_type in ('A', 'R')
2632 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2633 and fi1.error_flag in ('N','Y') --Bug#9479220
2634 and fi1.delete_flag = 'N'
2635 and fi1.item_date between p_start_date and p_end_date
2636 and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2637 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
2638 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
2639 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
2640 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
2641 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
2642 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
2643 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
2644 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
2645 and fi1.assignment_id = asgn.assignment_id
2646 and fi1.delete_flag = 'N'
2647 and col.lookup_code = decode(asgn.assignment_type,
2648 'STAFFED_ADMIN_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
2649 'STAFFED_ASGMT_CONF',
2650 'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
2651 'STAFFED_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
2652 'STAFFED_ASGMT_CONF',
2653 'STAFFED_ASGMT_CONF',
2654 'STAFFED_ASGMT_PROV'),
2655 'OPEN_ASSIGNMENT', 'OPEN_ASGMT')
2656 /*Commenting for bug 3280808
2657 UNION ALL
2658 select
2659 'ASSIGNMENT' time_chart_record_type,
2660 fi1.assignment_id row_label_id,
2661 fi1.item_date start_date,
2662 fi1.item_date end_date,
2663 fi1.global_exp_period_end_date week_end_date,
2664 'MONTH' scale_type,
2665 fi_admin.item_quantity quantity,
2666 col.render_priority,
2667 col.file_name color_file_name
2668 from pa_forecast_items fi1, pa_timeline_colors col,
2669 (select fi.resource_id,
2670 fi.assignment_id,
2671 fi.global_exp_period_end_date week_end_date,
2672 fi.item_date,
2673 fi.item_quantity,
2674 fi.forecast_item_type,
2675 fi.delete_flag
2676 from pa_forecast_items fi, pa_project_assignments asgn
2677 where fi.assignment_id = asgn.assignment_id
2678 and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
2679 and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
2680 where fi1.item_date between p_start_date and p_end_date
2681 and fi1.forecast_item_type = 'A'
2682 and fi1.forecast_item_type = fi_admin.forecast_item_type
2683 and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2684 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
2685 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
2686 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
2687 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
2688 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
2689 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
2690 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
2691 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
2692 and fi1.resource_id = fi_admin.resource_id
2693 and fi1.item_date = fi_admin.item_date
2694 and fi1.delete_flag = 'N'
2695 and fi1.delete_flag = fi_admin.delete_flag
2696 and col.lookup_code = 'CONFIRMED_ADMIN'
2697 End of commenting for bug 3280808 */
2698 UNION ALL
2699 select
2700 'ASSIGNMENT' time_chart_record_type,
2701 fi1.assignment_id row_label_id,
2702 fi1.item_date start_date,
2703 fi1.item_date end_date,
2704 fi1.global_exp_period_end_date week_end_date,
2705 'MONTH' scale_type,
2706 fi_overcom.overcommitment_quantity quantity,
2707 col.render_priority,
2708 col.file_name color_file_name
2709 from pa_forecast_items fi1, pa_timeline_colors col,
2710 (select fi.resource_id,
2711 fi.item_date,
2712 fi.delete_flag,
2713 decode(fi_week.overcommitment_flag, 'Y', fi.overcommitment_quantity, 'N', 0) overcommitment_quantity
2714 from pa_forecast_items fi,
2715 -- Added below for Bug# 6524548
2716 (select paf.resource_id,
2717 paf.global_exp_period_end_date,
2718 decode(sign(sum(paf.capacity_quantity)*g_overcommitment_percentage-sum(paf.overcommitment_quantity)),
2719 1, 'N',
2720 0, decode(sum(paf.overcommitment_quantity), 0, 'N', 'Y'),
2721 --(select resource_id,
2722 -- global_exp_period_end_date,
2723 -- decode(sign(sum(capacity_quantity)*g_overcommitment_percentage-sum(overcommitment_quantity)), 1, 'N',
2724 -- 0, decode(sum(overcommitment_quantity), 0, 'N', 'Y'),
2725 -- End for Bug# 6524548
2726 -1, 'Y') overcommitment_flag,
2727 -- Added below for Bug# 6524548
2728 paf.forecast_item_type,
2729 paf.delete_flag
2730 from pa_forecast_items paf,
2731 PA_PROJECT_ASSIGNMENTS PAP
2732 where PAF.RESOURCE_ID = PAP.RESOURCE_ID
2733 AND PAF.DELETE_FLAG = 'N'
2734 AND PAP.ASSIGNMENT_ID IN ( l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2735 l_row_label_id_tbl(3), l_row_label_id_tbl(4),
2736 l_row_label_id_tbl(5), l_row_label_id_tbl(6),
2737 l_row_label_id_tbl(7), l_row_label_id_tbl(8),
2738 l_row_label_id_tbl(9), l_row_label_id_tbl(10),
2739 l_row_label_id_tbl(11),l_row_label_id_tbl(12),
2740 l_row_label_id_tbl(13),l_row_label_id_tbl(14),
2741 l_row_label_id_tbl(15),l_row_label_id_tbl(16),
2742 l_row_label_id_tbl(17),l_row_label_id_tbl(18),
2743 l_row_label_id_tbl(19),l_row_label_id_tbl(20),
2744 l_row_label_id_tbl(21),l_row_label_id_tbl(22),
2745 l_row_label_id_tbl(23),l_row_label_id_tbl(24),
2746 l_row_label_id_tbl(25) )
2747 AND PAF.item_date between p_start_date and p_end_date
2748 group by PAF.resource_id, PAF.global_exp_period_end_date, PAF.forecast_item_type, PAF.delete_flag)fi_week
2749 --forecast_item_type,
2750 --delete_flag
2751 --from pa_forecast_items
2752 --where item_date between p_start_date and p_end_date
2753 --group by resource_id, global_exp_period_end_date, forecast_item_type, delete_flag)fi_week
2754 -- End for Bug# 6524548
2755 where fi.resource_id = fi_week.resource_id
2756 and fi.global_exp_period_end_date = fi_week.global_exp_period_end_date
2757 and fi.forecast_item_type = 'U'
2758 and fi.forecast_item_type = fi_week.forecast_item_type
2759 and fi.delete_flag = fi_week.delete_flag) fi_overcom
2760 where fi1.resource_id = fi_overcom.resource_id
2761 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2762 and fi1.error_flag in ('N','Y') --Bug#9479220
2763 and fi1.forecast_item_type = 'A'
2764 and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2765 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
2766 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
2767 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
2768 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
2769 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
2770 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
2771 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
2772 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
2773 and fi1.item_date between p_start_date and p_end_date
2774 and fi1.item_date = fi_overcom.item_date
2775 and fi1.delete_flag = 'N'
2776 and fi1.delete_flag = fi_overcom.delete_flag
2777 and col.lookup_code = 'OVERCOMMITTED'
2778 and fi_overcom.overcommitment_quantity > 0
2779 );
2780
2781 ELSIF g_availability_cal_period = 'WEEKLY' and p_scale_type = 'MONTH' and prm_license <> 'Y' THEN
2782 INSERT INTO pa_time_chart_temp (
2783 select
2784 decode(fi1.forecast_item_type, 'A', 'ASSIGNMENT', 'R', 'REQUIREMENT') time_chart_record_type,
2785 fi1.assignment_id row_label_id,
2786 fi1.item_date start_date,
2787 fi1.item_date end_date,
2788 fi1.global_exp_period_end_date week_end_date,
2789 'MONTH' scale_type,
2790 fi1.item_quantity quantity,
2791 col.render_priority,
2792 col.file_name color_file_name
2793 from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
2794 where fi1.forecast_item_type in ('A', 'R')
2795 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2796 and fi1.error_flag in ('N','Y') --Bug#9479220
2797 and fi1.delete_flag = 'N'
2798 and fi1.item_date between p_start_date and p_end_date
2799 and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2800 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
2801 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
2802 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
2803 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
2804 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
2805 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
2806 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
2807 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
2808 and fi1.assignment_id = asgn.assignment_id
2809 and fi1.delete_flag = 'N'
2810 and col.lookup_code = decode(asgn.assignment_type,
2811 'STAFFED_ADMIN_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
2812 'STAFFED_ASGMT_CONF',
2813 'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
2814 'STAFFED_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
2815 'STAFFED_ASGMT_CONF',
2816 'STAFFED_ASGMT_CONF',
2817 'STAFFED_ASGMT_PROV'),
2818 'OPEN_ASSIGNMENT', 'OPEN_ASGMT')
2819 /*Commenting for bug 3280808
2820 UNION ALL
2821 select
2822 'ASSIGNMENT' time_chart_record_type,
2823 fi1.assignment_id row_label_id,
2824 fi1.item_date start_date,
2825 fi1.item_date end_date,
2826 fi1.global_exp_period_end_date week_end_date,
2827 'MONTH' scale_type,
2828 fi_admin.item_quantity quantity,
2829 col.render_priority,
2830 col.file_name color_file_name
2831 from pa_forecast_items fi1, pa_timeline_colors col,
2832 (select fi.resource_id,
2833 fi.assignment_id,
2834 fi.global_exp_period_end_date week_end_date,
2835 fi.item_date,
2836 fi.item_quantity,
2837 fi.forecast_item_type,
2838 fi.delete_flag
2839 from pa_forecast_items fi, pa_project_assignments asgn
2840 where fi.assignment_id = asgn.assignment_id
2841 and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
2842 and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
2843 where fi1.item_date between p_start_date and p_end_date
2844 and fi1.forecast_item_type = 'A'
2845 and fi1.forecast_item_type = fi_admin.forecast_item_type
2846 and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2847 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
2848 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
2849 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
2850 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
2851 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
2852 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
2853 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
2854 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
2855 and fi1.resource_id = fi_admin.resource_id
2856 and fi1.item_date = fi_admin.item_date
2857 and fi1.delete_flag = 'N'
2858 and fi1.delete_flag = fi_admin.delete_flag
2859 and col.lookup_code = 'CONFIRMED_ADMIN'
2860 End of commenting for bug 3280808 */
2861 );
2862
2863 ELSIF g_availability_cal_period = 'WEEKLY' and p_scale_type = 'THREE_MONTH' and prm_license = 'Y' THEN
2864 -----------------------------------------------------------------
2865 -- Bug Reference : 6524548
2866 -- We are restricting the Index Scan (ROW_ID) on PA_FORECAST_ITEMS
2867 -- By supplying the Resource Ids for the assignments by joining
2868 -- PA_PROJECT_ASSIGNMENTS and Further filtering on DELETE FLAG ('N')
2869 -- As DELETE_FLAG having value 'Y' are records which are eligible
2870 -- purge and we need not scan them too.
2871 ------------------------------------------------------------------
2872 INSERT INTO pa_time_chart_temp (
2873 select
2874 decode(fi1.forecast_item_type, 'A', 'ASSIGNMENT', 'R', 'REQUIREMENT') time_chart_record_type,
2875 fi1.assignment_id row_label_id,
2876 fi1.global_exp_period_end_date-6 start_date,
2877 fi1.global_exp_period_end_date end_date,
2878 fi1.global_exp_period_end_date week_end_date,
2879 'THREE_MONTH' scale_type,
2880 sum(fi1.item_quantity) quantity,
2881 col.render_priority,
2882 col.file_name color_file_name
2883 from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
2884 where fi1.forecast_item_type in ('A', 'R')
2885 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
2886 and fi1.error_flag in ('N','Y') --Bug#9479220
2887 and fi1.delete_flag = 'N'
2888 and fi1.item_date between p_start_date and p_end_date
2889 and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2890 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
2891 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
2892 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
2893 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
2894 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
2895 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
2896 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
2897 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
2898 and fi1.assignment_id = asgn.assignment_id
2899 and col.lookup_code = decode(asgn.assignment_type,
2900 'STAFFED_ADMIN_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
2901 'STAFFED_ASGMT_CONF',
2902 'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
2903 'STAFFED_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
2904 'STAFFED_ASGMT_CONF',
2905 'STAFFED_ASGMT_CONF',
2906 'STAFFED_ASGMT_PROV'),
2907 'OPEN_ASSIGNMENT', 'OPEN_ASGMT')
2908 group by fi1.assignment_id,
2909 fi1.global_exp_period_end_date,
2910 fi1.forecast_item_type,
2911 col.file_name,
2912 col.render_priority
2913 /* Commenting for bug 3280808
2914 UNION ALL
2915 select
2916 'ASSIGNMENT' time_chart_record_type,
2917 fi1.assignment_id row_label_id,
2918 trunc(fi1.global_exp_period_end_date)-6 start_date,
2919 trunc(fi1.global_exp_period_end_date) end_date,
2920 trunc(fi1.global_exp_period_end_date) week_end_date,
2921 'THREE_MONTH' scale_type,
2922 sum(fi_admin.item_quantity) quantity,
2923 col.render_priority,
2924 col.file_name color_file_name
2925 from pa_forecast_items fi1, pa_timeline_colors col,
2926 (select fi.resource_id,
2927 fi.assignment_id,
2928 fi.global_exp_period_end_date week_end_date,
2929 fi.item_date,
2930 fi.item_quantity,
2931 fi.forecast_item_type,
2932 fi.delete_flag
2933 from pa_forecast_items fi, pa_project_assignments asgn
2934 where fi.assignment_id = asgn.assignment_id
2935 and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
2936 and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
2937 where fi1.resource_id = fi_admin.resource_id
2938 and fi1.forecast_item_type = 'A'
2939 and fi1.forecast_item_type = fi_admin.forecast_item_type
2940 and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2941 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
2942 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
2943 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
2944 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
2945 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
2946 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
2947 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
2948 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
2949 and fi1.item_date between p_start_date and p_end_date
2950 and fi1.item_date = fi_admin.item_date
2951 and fi1.delete_flag = 'N'
2952 and fi1.delete_flag = fi_admin.delete_flag
2953 and col.lookup_code = 'CONFIRMED_ADMIN'
2954 group by fi1.assignment_id,
2955 fi1.global_exp_period_end_date,
2956 fi1.forecast_item_type,
2957 col.file_name,
2958 col.render_priority
2959 End of Commenting for bug 3280808 */
2960 UNION ALL
2961 select
2962 'ASSIGNMENT' time_chart_record_type,
2963 fi1.assignment_id row_label_id,
2964 fi1.global_exp_period_end_date-6 start_date,
2965 fi1.global_exp_period_end_date end_date,
2966 fi1.global_exp_period_end_date week_end_date,
2967 'THREE_MONTH' scale_type,
2968 sum(fi_overcom.overcommitment_quantity) quantity,
2969 col.render_priority,
2970 col.file_name color_file_name
2971 from pa_forecast_items fi1, pa_timeline_colors col,
2972 (select fi.resource_id,
2973 fi.item_date,
2974 fi.delete_flag,
2975 decode(fi_week.overcommitment_flag, 'Y', fi.overcommitment_quantity, 'N', 0) overcommitment_quantity
2976 from pa_forecast_items fi,
2977 -- Added below for Bug# 6524548
2978 (select PAF.resource_id,
2979 PAF.global_exp_period_end_date,
2980 decode(sign(sum(PAF.capacity_quantity)*g_overcommitment_percentage-sum(PAF.overcommitment_quantity)),
2981 1, 'N',
2982 0, decode(sum(PAF.overcommitment_quantity), 0, 'N', 'Y'),
2983 --(select resource_id,
2984 -- global_exp_period_end_date,
2985 -- decode(sign(sum(capacity_quantity)*g_overcommitment_percentage-sum(overcommitment_quantity)), 1, 'N',
2986 -- 0, decode(sum(overcommitment_quantity), 0, 'N', 'Y'),
2987 -- End for Bug# 6524548
2988 -1, 'Y') overcommitment_flag,
2989 -- Added below for Bug# 6524548
2990 PAF.forecast_item_type,
2991 PAF.delete_flag
2992 from pa_forecast_items PAF,
2993 PA_PROJECT_ASSIGNMENTS PAP
2994 where PAF.RESOURCE_ID = PAP.RESOURCE_ID
2995 AND PAF.DELETE_FLAG = 'N'
2996 AND PAP.ASSIGNMENT_ID IN ( l_row_label_id_tbl(1), l_row_label_id_tbl(2),
2997 l_row_label_id_tbl(3), l_row_label_id_tbl(4),
2998 l_row_label_id_tbl(5), l_row_label_id_tbl(6),
2999 l_row_label_id_tbl(7), l_row_label_id_tbl(8),
3000 l_row_label_id_tbl(9), l_row_label_id_tbl(10),
3001 l_row_label_id_tbl(11),l_row_label_id_tbl(12),
3002 l_row_label_id_tbl(13),l_row_label_id_tbl(14),
3003 l_row_label_id_tbl(15),l_row_label_id_tbl(16),
3004 l_row_label_id_tbl(17),l_row_label_id_tbl(18),
3005 l_row_label_id_tbl(19),l_row_label_id_tbl(20),
3006 l_row_label_id_tbl(21),l_row_label_id_tbl(22),
3007 l_row_label_id_tbl(23),l_row_label_id_tbl(24),
3008 l_row_label_id_tbl(25) )
3009 AND PAF.item_date between p_start_date and p_end_date
3010 group by PAF.resource_id, PAF.global_exp_period_end_date, PAF.forecast_item_type, PAF.delete_flag)fi_week
3011 --forecast_item_type,
3012 --delete_flag
3013 --from pa_forecast_items
3014 --where item_date between p_start_date and p_end_date
3015 --group by resource_id, global_exp_period_end_date, forecast_item_type, delete_flag)fi_week
3016 -- End for Bug# 6524548
3017 where fi.resource_id = fi_week.resource_id
3018 and fi.global_exp_period_end_date = fi_week.global_exp_period_end_date
3019 and fi.forecast_item_type = 'U'
3020 and fi.forecast_item_type = fi_week.forecast_item_type
3021 and fi.delete_flag = fi_week.delete_flag) fi_overcom
3022 where fi1.resource_id = fi_overcom.resource_id
3023 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
3024 and fi1.error_flag in ('N','Y') --Bug#9479220
3025 and fi1.forecast_item_type = 'A'
3026 and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
3027 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
3028 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
3029 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
3030 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
3031 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
3032 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
3033 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
3034 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
3035 and fi1.item_date between p_start_date and p_end_date
3036 and fi1.item_date = fi_overcom.item_date
3037 and fi1.delete_flag = 'N'
3038 and fi1.delete_flag = fi_overcom.delete_flag
3039 and col.lookup_code = 'OVERCOMMITTED'
3040 and fi_overcom.overcommitment_quantity > 0
3041 group by fi1.assignment_id,
3042 fi1.global_exp_period_end_date,
3043 col.file_name,
3044 col.render_priority
3045 );
3046
3047 ELSIF g_availability_cal_period = 'WEEKLY' and p_scale_type = 'THREE_MONTH' and prm_license <> 'Y' THEN
3048 INSERT INTO pa_time_chart_temp (
3049 select
3050 decode(fi1.forecast_item_type, 'A', 'ASSIGNMENT', 'R', 'REQUIREMENT') time_chart_record_type,
3051 fi1.assignment_id row_label_id,
3052 fi1.global_exp_period_end_date-6 start_date,
3053 fi1.global_exp_period_end_date end_date,
3054 fi1.global_exp_period_end_date week_end_date,
3055 'THREE_MONTH' scale_type,
3056 sum(fi1.item_quantity) quantity,
3057 col.render_priority,
3058 col.file_name color_file_name
3059 from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
3060 where fi1.forecast_item_type in ('A', 'R')
3061 and col.lookup_type = 'TIMELINE_STATUS' -- Added for Bug 5079783
3062 and fi1.error_flag in ('N','Y') --Bug#9479220
3063 and fi1.delete_flag = 'N'
3064 and fi1.item_date between p_start_date and p_end_date
3065 and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
3066 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
3067 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
3068 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
3069 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
3070 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
3071 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
3072 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
3073 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
3074 and fi1.assignment_id = asgn.assignment_id
3075 and col.lookup_code = decode(asgn.assignment_type,
3076 'STAFFED_ADMIN_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
3077 'STAFFED_ASGMT_CONF',
3078 'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
3079 'STAFFED_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
3080 'STAFFED_ASGMT_CONF',
3081 'STAFFED_ASGMT_CONF',
3082 'STAFFED_ASGMT_PROV'),
3083 'OPEN_ASSIGNMENT', 'OPEN_ASGMT')
3084 group by fi1.assignment_id,
3085 fi1.global_exp_period_end_date,
3086 fi1.forecast_item_type,
3087 col.file_name,
3088 col.render_priority
3089 /* Commenting for bug 3280808
3090 UNION ALL
3091 select
3092 'ASSIGNMENT' time_chart_record_type,
3093 fi1.assignment_id row_label_id,
3094 trunc(fi1.global_exp_period_end_date)-6 start_date,
3095 trunc(fi1.global_exp_period_end_date) end_date,
3096 trunc(fi1.global_exp_period_end_date) week_end_date,
3097 'THREE_MONTH' scale_type,
3098 sum(fi_admin.item_quantity) quantity,
3099 col.render_priority,
3100 col.file_name color_file_name
3101 from pa_forecast_items fi1, pa_timeline_colors col,
3102 (select fi.resource_id,
3103 fi.assignment_id,
3104 fi.global_exp_period_end_date week_end_date,
3105 fi.item_date,
3106 fi.item_quantity,
3107 fi.forecast_item_type,
3108 fi.delete_flag
3109 from pa_forecast_items fi, pa_project_assignments asgn
3110 where fi.assignment_id = asgn.assignment_id
3111 and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
3112 and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
3113 where fi1.resource_id = fi_admin.resource_id
3114 and fi1.forecast_item_type = 'A'
3115 and fi1.forecast_item_type = fi_admin.forecast_item_type
3116 and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
3117 l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
3118 l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
3119 l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
3120 l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
3121 l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
3122 l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
3123 l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
3124 l_row_label_id_tbl(24), l_row_label_id_tbl(25))
3125 and fi1.item_date between p_start_date and p_end_date
3126 and fi1.item_date = fi_admin.item_date
3127 and fi1.delete_flag = 'N'
3128 and fi1.delete_flag = fi_admin.delete_flag
3129 and col.lookup_code = 'CONFIRMED_ADMIN'
3130 group by fi1.assignment_id,
3131 fi1.global_exp_period_end_date,
3132 fi1.forecast_item_type,
3133 col.file_name,
3134 col.render_priority
3135 End of Commenting for bug 3280808 */
3136 );
3137
3138 END IF; -- End of Team List
3139
3140 END IF;
3141
3142 END LOOP;
3143 END IF;
3144
3145 x_return_status := FND_API.G_RET_STS_SUCCESS;
3146 EXCEPTION
3147 WHEN OTHERS THEN
3148 x_msg_count := 1;
3149 x_msg_data := sqlerrm;
3150 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3151 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_TIMELINE_PVT',
3152 p_procedure_name => 'Poplulate_Time_Chart_Table');
3153 RAISE;
3154
3155 END populate_time_chart_table;
3156
3157
3158 END PA_TIMELINE_PVT;