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