eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PA_TIMELINE_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 22


    SELECT item_quantity
    FROM pa_forecast_items
    WHERE assignment_id = p_assignment_id
    AND item_date BETWEEN l_start_date and l_end_date
    AND delete_flag IN ('Y', 'N');

Line: 37


  SELECT start_date, end_date
  INTO l_start_date, l_end_date
  FROM pa_project_assignments
  WHERE assignment_id = p_assignment_id;

Line: 60


   PA_PROJECT_ASSIGNMENTS_PKG.update_row(
     			 p_assignment_id     => p_assignment_id,
           p_assignment_effort => l_sum,
           x_return_status     => x_return_status);

Line: 109


     SELECT distinct res.resource_id resource_id
       FROM pa_resources_denorm res
       WHERE upper(res.resource_name) BETWEEN  upper(p_start_resource_name) AND upper(p_end_resource_name)
--       AND   NVL(res.resource_org_id,NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),
--       ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10))), -99)) =
--       NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),
--       ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10))),-99)
       UNION ALL
       SELECT  distinct res.resource_id resource_id
         FROM pa_resources_denorm res
         WHERE     res.resource_id = p_resource_id;

Line: 247


     SELECT   pares.resource_id resource_id,
       trunc(start_date_time) start_date,
       NVL(trunc(end_date_time), TO_DATE('12/31/4712','MM/DD/YYYY')) end_date
       FROM    jtf_cal_resource_assign jtf_res,pa_resources pares
       WHERE  jtf_res.resource_id = pares.jtf_resource_id
       AND  jtf_res.resource_type_code = 'RS_EMPLOYEE'
       AND  jtf_res.calendar_id = p_calendar_id
       AND  jtf_res.primary_calendar_flag = 'Y'
     UNION ALL
     SELECT res.resource_id,
            resdenorm.resource_effective_start_date start_date,
            TO_DATE('12/31/4712','MM/DD/YYYY') end_date
     from pa_resources res, pa_resources_denorm resdenorm
     where res.jtf_resource_id is null
     and res.resource_id = resdenorm.resource_id
     and res.resource_type_id = 101 -- Bug 4370196
     ;

Line: 306


PROCEDURE Delete_Timeline(p_assignment_id  IN   NUMBER,
                          x_return_status  OUT  NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
                          x_msg_count      OUT  NOCOPY NUMBER, --File.Sql.39 bug 4440895
                          x_msg_data       OUT  NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
IS
  l_start_date DATE;

Line: 317


  PA_FORECASTITEM_PVT.Delete_FI (p_assignment_id  => p_assignment_id ,
                      x_return_status  =>  x_return_status,
                      x_msg_count      =>  x_msg_count,
                      x_msg_data       =>  x_msg_data);

Line: 324


  select start_date, end_date, resource_id
  into l_start_date, l_end_date, l_resource_id
  from pa_project_assignments
  where assignment_id = p_assignment_id;

Line: 347


       p_procedure_name => 'Delete_Timeline');

Line: 350


END Delete_Timeline;

Line: 424


            p_delete_flag       IN VARCHAR2 DEFAULT 'Y',
            x_return_status     OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
            x_msg_count         OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
            x_msg_data          OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
IS

  g_availability_cal_period VARCHAR2(15) := FND_PROFILE.VALUE('PA_AVAILABILITY_CAL_PERIOD');

Line: 448


  pa_timeline_util.debug('p_delete_flag: ' || p_delete_flag);

Line: 455


  IF p_delete_flag = 'Y' then
    PA_TIME_CHART_PKG.delete_row(x_return_status => x_return_status,
                                 x_msg_count     => x_msg_count,
                                 x_msg_data      => x_msg_data);

Line: 461


  pa_timeline_util.debug('After delete row');

Line: 488


          INSERT INTO pa_time_chart_temp (
select
'RESOURCE' time_chart_record_type,
resource_id row_label_id,
item_date start_date,
item_date end_date,
global_exp_period_end_date week_end_date,
'MONTH' scale_type,
decode(availability_flag,
  'Y', decode(sign(capacity_quantity*g_res_capacity_percentage-availability_quantity), 1, 0, availability_quantity),
  'N', decode(sign(capacity_quantity*g_overcommitment_percentage-overcommitment_quantity), 1, 0, overcommitment_quantity)) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items, pa_timeline_colors col
where resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and forecast_item_type = 'U'
and item_date between p_start_date and p_end_date
and delete_flag = 'N'
and (availability_flag = 'Y' or overcommitment_flag = 'Y')
and col.lookup_code = decode(availability_flag, 'Y', 'AVAILABLE', 'N', 'OVERCOMMITTED')
UNION ALL
select
'RESOURCE' time_chart_record_type,
fi.resource_id row_label_id,
fi.item_date start_date,
fi.item_date end_date,
fi.global_exp_period_end_date week_end_date,
'MONTH' scale_type,
fi.item_quantity quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and col.lookup_type =  'TIMELINE_STATUS' -- Added  for Bug 5079783
and fi.forecast_item_type = 'A'
and fi.item_date between p_start_date and p_end_date
and fi.delete_flag = 'N'
and fi.assignment_id = asgn.assignment_id
and col.lookup_code = decode(asgn.assignment_type,
                 'STAFFED_ADMIN_ASSIGNMENT',decode(asgmt_sys_status_code,
                                                   'STAFFED_ASGMT_CONF',
                                                   'CONFIRMED_ADMIN',
                                                   'STAFFED_ASGMT_PROV'),
                 'STAFFED_ASSIGNMENT', decode(asgmt_sys_status_code,
                                              'STAFFED_ASGMT_CONF',
                                              'STAFFED_ASGMT_CONF',
                                              'STAFFED_ASGMT_PROV'))
);

Line: 555


          INSERT INTO pa_time_chart_temp (
select
'RESOURCE' time_chart_record_type,
fi.resource_id row_label_id,
fi.item_date start_date,
fi.item_date end_date,
fi.global_exp_period_end_date week_end_date,
'MONTH' scale_type,
fi.item_quantity quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.forecast_item_type = 'A'
and fi.item_date between p_start_date and p_end_date
and fi.delete_flag = 'N'
and fi.assignment_id = asgn.assignment_id
and col.lookup_code = decode(asgn.assignment_type,
                 'STAFFED_ADMIN_ASSIGNMENT',decode(asgmt_sys_status_code,
                                                   'STAFFED_ASGMT_CONF',
                                                   'CONFIRMED_ADMIN',
                                                   'STAFFED_ASGMT_PROV'),
                 'STAFFED_ASSIGNMENT', decode(asgmt_sys_status_code,
                                              'STAFFED_ASGMT_CONF',
                                              'STAFFED_ASGMT_CONF',
                                              'STAFFED_ASGMT_PROV'))
);

Line: 593


          INSERT INTO pa_time_chart_temp (
select 'RESOURCE' time_chart_record_type,
resource_id row_label_id,
global_exp_period_end_date-6 start_date,
global_exp_period_end_date end_date,
global_exp_period_end_date week_end_date,
'THREE_MONTH' scale_type,
sum(decode(availability_flag,
      'Y', decode(sign((capacity_quantity*g_res_capacity_percentage)-availability_quantity), 1, 0, availability_quantity),
      'N', decode(sign((capacity_quantity*g_overcommitment_percentage)-overcommitment_quantity), 1, 0, overcommitment_quantity))) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items, pa_timeline_colors col
where resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and item_date between p_start_date and p_end_date
and delete_flag = 'N'
and col.lookup_code = decode(availability_flag, 'Y', 'AVAILABLE', 'N', 'OVERCOMMITTED')
and (availability_flag = 'Y' or overcommitment_flag = 'Y')
GROUP BY resource_id,
global_exp_period_end_date,
col.file_name,
col.render_priority
UNION ALL
select 'RESOURCE' time_chart_record_type,
fi.resource_id row_label_id,
fi.global_exp_period_end_date-6 start_date,
fi.global_exp_period_end_date end_date,
fi.global_exp_period_end_date week_end_date,
'THREE_MONTH' scale_type,
sum(fi.item_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.forecast_item_type = 'A'
and fi.item_date between p_start_date and p_end_date
and fi.delete_flag = 'N'
and fi.assignment_id = asgn.assignment_id
and col.lookup_code = decode(asgn.assignment_type,
                 'STAFFED_ADMIN_ASSIGNMENT',decode(asgmt_sys_status_code,
                                                   'STAFFED_ASGMT_CONF',
                                                   'CONFIRMED_ADMIN',
                                                   'STAFFED_ASGMT_PROV'),
                 'STAFFED_ASSIGNMENT', decode(asgmt_sys_status_code,
                                              'STAFFED_ASGMT_CONF',
                                              'STAFFED_ASGMT_CONF',
                                              'STAFFED_ASGMT_PROV'))
GROUP BY fi.resource_id,
fi.global_exp_period_end_date,
fi.forecast_item_type,
col.file_name,
col.render_priority
);

Line: 666


          INSERT INTO pa_time_chart_temp (
select 'RESOURCE' time_chart_record_type,
fi.resource_id row_label_id,
fi.global_exp_period_end_date-6 start_date,
fi.global_exp_period_end_date end_date,
fi.global_exp_period_end_date week_end_date,
'THREE_MONTH' scale_type,
sum(fi.item_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.forecast_item_type = 'A'
and fi.item_date between p_start_date and p_end_date
and fi.delete_flag = 'N'
and fi.assignment_id = asgn.assignment_id
and col.lookup_code = decode(asgn.assignment_type,
                 'STAFFED_ADMIN_ASSIGNMENT',decode(asgmt_sys_status_code,
                                                   'STAFFED_ASGMT_CONF',
                                                   'CONFIRMED_ADMIN',
                                                   'STAFFED_ASGMT_PROV'),
                 'STAFFED_ASSIGNMENT', decode(asgmt_sys_status_code,
                                              'STAFFED_ASGMT_CONF',
                                              'STAFFED_ASGMT_CONF',
                                              'STAFFED_ASGMT_PROV'))
GROUP BY fi.resource_id,
fi.global_exp_period_end_date,
fi.forecast_item_type,
col.file_name,
col.render_priority
);

Line: 708


          INSERT INTO pa_time_chart_temp (
select 'RESOURCE' time_chart_record_type,
fi.resource_id row_label_id,
fi.item_date start_date,
fi.item_date end_date,
fi.global_exp_period_end_date week_end_date,
'MONTH' scale_type,
decode(fi_week.availability_flag, 'Y', fi.availability_quantity, 'N', fi.overcommitment_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col,
(select resource_id,
  global_exp_period_end_date week_end_date,
  decode(sign(sum(capacity_quantity)*g_res_capacity_percentage-sum(availability_quantity)),1, 'N', 'Y') availability_flag,
  decode(sign(sum(capacity_quantity)*g_overcommitment_percentage-sum(overcommitment_quantity)), 1, 'N',
         0, decode(sum(overcommitment_quantity), 0, 'N', 'Y'),
         -1, 'Y') overcommitment_flag,
  forecast_item_type,
  delete_flag
  from pa_forecast_items
  where item_date between p_start_date and p_end_date
  group by resource_id,
  global_exp_period_end_date,
  forecast_item_type,
  delete_flag) fi_week
where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.resource_id = fi_week.resource_id
and fi.item_date between p_start_date and p_end_date
and fi.global_exp_period_end_date = fi_week.week_end_date
and fi.forecast_item_type = 'U'
and fi.forecast_item_type = fi_week.forecast_item_type
and fi.delete_flag = 'N'
and fi.delete_flag = fi_week.delete_flag
and (fi.availability_quantity > 0 or fi.overcommitment_quantity > 0)
and (fi_week.availability_flag = 'Y' or fi_week.overcommitment_flag = 'Y')
and col.lookup_code = decode(fi_week.availability_flag, 'Y', 'AVAILABLE', 'N', 'OVERCOMMITTED')
UNION ALL
select 'RESOURCE' time_chart_record_type,
fi.resource_id row_label_id,
fi.item_date start_date,
fi.item_date end_date,
fi.global_exp_period_end_date week_end_date,
'MONTH' scale_type,
fi.item_quantity quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.forecast_item_type = 'A'
and fi.item_date between p_start_date and p_end_date
and fi.delete_flag = 'N'
and fi.assignment_id = asgn.assignment_id
and col.lookup_code = decode(asgn.assignment_type,
                 'STAFFED_ADMIN_ASSIGNMENT',decode(asgmt_sys_status_code,
                                                   'STAFFED_ASGMT_CONF',
                                                   'CONFIRMED_ADMIN',
                                                   'STAFFED_ASGMT_PROV'),
                 'STAFFED_ASSIGNMENT', decode(asgmt_sys_status_code,
                                              'STAFFED_ASGMT_CONF',
                                              'STAFFED_ASGMT_CONF',
                                              'STAFFED_ASGMT_PROV'))
);

Line: 790


          INSERT INTO pa_time_chart_temp (
select 'RESOURCE' time_chart_record_type,
fi.resource_id row_label_id,
fi.item_date start_date,
fi.item_date end_date,
fi.global_exp_period_end_date week_end_date,
'MONTH' scale_type,
fi.item_quantity quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.forecast_item_type = 'A'
and fi.item_date between p_start_date and p_end_date
and fi.delete_flag = 'N'
and fi.assignment_id = asgn.assignment_id
and col.lookup_code = decode(asgn.assignment_type,
                 'STAFFED_ADMIN_ASSIGNMENT',decode(asgmt_sys_status_code,
                                                   'STAFFED_ASGMT_CONF',
                                                   'CONFIRMED_ADMIN',
                                                   'STAFFED_ASGMT_PROV'),
                 'STAFFED_ASSIGNMENT', decode(asgmt_sys_status_code,
                                              'STAFFED_ASGMT_CONF',
                                              'STAFFED_ASGMT_CONF',
                                              'STAFFED_ASGMT_PROV'))
);

Line: 827


          INSERT INTO pa_time_chart_temp (
select 'RESOURCE' time_chart_record_type,
fi.resource_id row_label_id,
fi.global_exp_period_end_date-6 start_date,
fi.global_exp_period_end_date end_date,
fi.global_exp_period_end_date week_end_date,
'THREE_MONTH' scale_type,
sum(decode(fi_week.availability_flag, 'Y', fi.availability_quantity, 'N', fi.overcommitment_quantity)) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col,
(select resource_id,
  global_exp_period_end_date week_end_date,
  decode(sign(sum(capacity_quantity)*g_res_capacity_percentage-sum(availability_quantity)),1, 'N', 'Y') availability_flag,
  decode(sign(sum(capacity_quantity)*g_overcommitment_percentage-sum(overcommitment_quantity)), 1, 'N',
         0, decode(sum(overcommitment_quantity), 0, 'N', 'Y'),
         -1, 'Y') overcommitment_flag,
  forecast_item_type,
  delete_flag
  from pa_forecast_items
  where item_date between p_start_date and p_end_date
  group by resource_id,
  global_exp_period_end_date,
  forecast_item_type,
  delete_flag) fi_week
where fi.resource_id in(l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.resource_id = fi_week.resource_id
and fi.item_date between p_start_date and p_end_date
and fi.global_exp_period_end_date = fi_week.week_end_date
and fi.forecast_item_type = 'U'
and fi.forecast_item_type = fi_week.forecast_item_type
and fi.delete_flag = 'N'
and fi.delete_flag = fi_week.delete_flag
and (fi.availability_quantity > 0 or fi.overcommitment_quantity > 0)
and (fi_week.availability_flag = 'Y' or fi_week.overcommitment_flag = 'Y')
and col.lookup_code = decode(fi_week.availability_flag, 'Y', 'AVAILABLE', 'N', 'OVERCOMMITTED')
GROUP BY fi.resource_id,
fi.global_exp_period_end_date,
fi_week.availability_flag,
col.file_name,
col.render_priority
UNION ALL
select 'RESOURCE' time_chart_record_type,
fi.resource_id row_label_id,
fi.global_exp_period_end_date-6 start_date,
fi.global_exp_period_end_date end_date,
fi.global_exp_period_end_date week_end_date,
'THREE_MONTH' scale_type,
sum(fi.item_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.forecast_item_type = 'A'
and fi.item_date between p_start_date and p_end_date
and fi.delete_flag = 'N'
and fi.assignment_id = asgn.assignment_id
and col.lookup_code = decode(asgn.assignment_type,
                 'STAFFED_ADMIN_ASSIGNMENT',decode(asgmt_sys_status_code,
                                                   'STAFFED_ASGMT_CONF',
                                                   'CONFIRMED_ADMIN',
                                                   'STAFFED_ASGMT_PROV'),
                 'STAFFED_ASSIGNMENT', decode(asgmt_sys_status_code,
                                              'STAFFED_ASGMT_CONF',
                                              'STAFFED_ASGMT_CONF',
                                              'STAFFED_ASGMT_PROV'))
GROUP BY fi.resource_id,
fi.global_exp_period_end_date,
fi.forecast_item_type,
col.file_name,
col.render_priority
);

Line: 919


          INSERT INTO pa_time_chart_temp (
select 'RESOURCE' time_chart_record_type,
fi.resource_id row_label_id,
fi.global_exp_period_end_date-6 start_date,
fi.global_exp_period_end_date end_date,
fi.global_exp_period_end_date week_end_date,
'THREE_MONTH' scale_type,
sum(fi.item_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
where fi.resource_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.forecast_item_type = 'A'
and fi.item_date between p_start_date and p_end_date
and fi.delete_flag = 'N'
and fi.assignment_id = asgn.assignment_id
and col.lookup_code = decode(asgn.assignment_type,
                 'STAFFED_ADMIN_ASSIGNMENT',decode(asgmt_sys_status_code,
                                                   'STAFFED_ASGMT_CONF',
                                                   'CONFIRMED_ADMIN',
                                                   'STAFFED_ASGMT_PROV'),
                 'STAFFED_ASSIGNMENT', decode(asgmt_sys_status_code,
                                              'STAFFED_ASGMT_CONF',
                                              'STAFFED_ASGMT_CONF',
                                              'STAFFED_ASGMT_PROV'))
GROUP BY fi.resource_id,
fi.global_exp_period_end_date,
fi.forecast_item_type,
col.file_name,
col.render_priority
);

Line: 964


          INSERT INTO pa_time_chart_temp (
select
decode(fi.availability_flag, 'Y', 'AVAILABLE', 'N', 'OVERCOMMITTED')time_chart_record_type,
decode(fi.availability_flag, 'Y', -99, 'N', -100) row_label_id,
fi.item_date start_date,
fi.item_date end_date,
fi.global_exp_period_end_date week_end_date,
'MONTH' scale_type,
decode (fi.availability_flag,
  'Y', decode(sign(fi.availability_quantity-fi.capacity_quantity*g_res_capacity_percentage), -1, 0, fi.availability_quantity),
  'N', decode(sign(fi.overcommitment_quantity-fi.capacity_quantity*g_overcommitment_percentage), -1, 0, fi.overcommitment_quantity)) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col
where fi.resource_id = p_resource_id
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.forecast_item_type = 'U'
and fi.item_date between p_start_date and p_end_date
and fi.delete_flag = 'N'
and (fi.availability_flag = 'Y' or fi.overcommitment_flag = 'Y')
and col.lookup_code = decode(fi.availability_flag, 'Y', 'AVAILABLE', 'N', 'OVERCOMMITTED')
UNION ALL
select
'ASSIGNMENT' time_chart_record_type,
fi.assignment_id row_label_id,fi.item_date start_date,
fi.item_date end_date,
fi.global_exp_period_end_date week_end_date,
'MONTH' scale_type,
fi.item_quantity quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
where fi.resource_id = p_resource_id
and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.assignment_id = asgn.assignment_id
and fi.forecast_item_type = 'A'
and fi.delete_flag = 'N'
and fi.item_date between p_start_date and p_end_date
and col.lookup_code = decode(asgn.assignment_type,
  'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                                'STAFFED_ASGMT_CONF',
                                'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
  'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_PROV'))
);

Line: 1022


          INSERT INTO pa_time_chart_temp (
select
'ASSIGNMENT' time_chart_record_type,
fi.assignment_id row_label_id,fi.item_date start_date,
fi.item_date end_date,
fi.global_exp_period_end_date week_end_date,
'MONTH' scale_type,
fi.item_quantity quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
where fi.resource_id = p_resource_id
and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.assignment_id = asgn.assignment_id
and fi.forecast_item_type = 'A'
and fi.delete_flag = 'N'
and fi.item_date between p_start_date and p_end_date
and col.lookup_code = decode(asgn.assignment_type,
  'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                                'STAFFED_ASGMT_CONF',
                                'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
  'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_PROV'))
);

Line: 1059


          INSERT INTO pa_time_chart_temp (
select
decode(fi.availability_flag, 'Y', 'AVAILABLE', 'N', 'OVERCOMMITTED') time_chart_record_type,
decode(fi.availability_flag, 'Y', -99, 'N', -100) row_label_id,
fi.global_exp_period_end_date - 6 start_date,
fi.global_exp_period_end_date end_date,
fi.global_exp_period_end_date week_end_date,
'THREE_MONTH' scale_type,
sum(decode(fi.availability_flag,
      'Y', decode(sign(fi.capacity_quantity*g_res_capacity_percentage-fi.availability_quantity), 1, 0, fi.availability_quantity),
      'N', decode(sign(fi.capacity_quantity*g_overcommitment_percentage-fi.overcommitment_quantity), 1, 0, fi.overcommitment_quantity))) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col
where fi.resource_id = p_resource_id
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.forecast_item_type = 'U'
and fi.item_date between p_start_date and p_end_date
and fi.delete_flag = 'N'
and col.lookup_code = decode(fi.availability_flag, 'Y', 'AVAILABLE', 'N', 'OVERCOMMITTED')
and (fi.availability_flag = 'Y' or fi.overcommitment_flag = 'Y')
GROUP BY fi.assignment_id,
fi.global_exp_period_end_date,
fi.availability_flag,
col.file_name,
col.render_priority
UNION ALL
select
'ASSIGNMENT' time_chart_record_type,
fi.assignment_id row_label_id,
fi.global_exp_period_end_date-6 start_date,
fi.global_exp_period_end_date end_date,
fi.global_exp_period_end_date week_end_date,
'THREE_MONTH' scale_type,
sum(fi.item_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
where fi.resource_id = p_resource_id
and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.assignment_id = asgn.assignment_id
and fi.forecast_item_type = 'A'
and fi.delete_flag = 'N'
and fi.item_date between p_start_date and p_end_date
and col.lookup_code = decode(asgn.assignment_type,
  'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                                'STAFFED_ASGMT_CONF',
                                'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
  'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_PROV'))
GROUP BY fi.assignment_id,
fi.global_exp_period_end_date,
col.file_name,
col.render_priority
);

Line: 1127


          INSERT INTO pa_time_chart_temp (
select
'ASSIGNMENT' time_chart_record_type,
fi.assignment_id row_label_id,
fi.global_exp_period_end_date-6 start_date,
fi.global_exp_period_end_date end_date,
fi.global_exp_period_end_date week_end_date,
'THREE_MONTH' scale_type,
sum(fi.item_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
where fi.resource_id = p_resource_id
and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.assignment_id = asgn.assignment_id
and fi.forecast_item_type = 'A'
and fi.delete_flag = 'N'
and fi.item_date between p_start_date and p_end_date
and col.lookup_code = decode(asgn.assignment_type,
  'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                                'STAFFED_ASGMT_CONF',
                                'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
  'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_PROV'))
GROUP BY fi.assignment_id,
fi.global_exp_period_end_date,
col.file_name,
col.render_priority
);

Line: 1170


          INSERT INTO pa_time_chart_temp (
select
decode(fi_week.availability_flag, 'Y', 'AVAILABLE', 'N', 'OVERCOMMITTED') time_chart_record_type,
decode(fi_week.availability_flag, 'Y', -99, 'N', -100) row_label_id,
fi.item_date start_date,
fi.item_date end_date,
fi_week.week_end_date,
'MONTH' scale_type,
decode(fi_week.availability_flag, 'Y', fi.availability_quantity, 'N', fi.overcommitment_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col,
(select resource_id,
  global_exp_period_end_date week_end_date,
  decode(sign(sum(capacity_quantity)*g_res_capacity_percentage-sum(availability_quantity)),1, 'N', 'Y') availability_flag,
  decode(sign(sum(capacity_quantity)*g_overcommitment_percentage-sum(overcommitment_quantity)), 1, 'N',
         0, decode(sum(overcommitment_quantity), 0, 'N', 'Y'),
         -1, 'Y') overcommitment_flag,
  forecast_item_type,
  delete_flag
  from pa_forecast_items
  where item_date between p_start_date and p_end_date
  group by resource_id,
  global_exp_period_end_date,
  forecast_item_type,
  delete_flag) fi_week
where fi.resource_id = p_resource_id
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.resource_id = fi_week.resource_id
and fi.item_date between p_start_date and p_end_date
and fi.global_exp_period_end_date = fi_week.week_end_date
and fi.forecast_item_type = 'U'
and fi.forecast_item_type = fi_week.forecast_item_type
and fi.delete_flag = 'N'
and fi.delete_flag = fi_week.delete_flag
and (fi.availability_quantity > 0 or fi.overcommitment_quantity > 0)
and (fi_week.availability_flag = 'Y' or fi_week.overcommitment_flag = 'Y')
and col.lookup_code = decode(fi_week.availability_flag, 'Y', 'AVAILABLE', 'N', 'OVERCOMMITTED')
UNION ALL
select
'ASSIGNMENT' time_chart_record_type,
fi.assignment_id row_label_id,
fi.item_date start_date,
fi.item_date end_date,
fi.global_exp_period_end_date week_end_date,
'MONTH' scale_type,
fi.item_quantity quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
where fi.resource_id = p_resource_id
and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.assignment_id = asgn.assignment_id
and fi.forecast_item_type = 'A'
and fi.delete_flag = 'N'
and fi.item_date between p_start_date and p_end_date
and col.lookup_code = decode(asgn.assignment_type,
  'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                                'STAFFED_ASGMT_CONF',
                                'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
  'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_PROV'))
);

Line: 1247


          INSERT INTO pa_time_chart_temp (
select
'ASSIGNMENT' time_chart_record_type,
fi.assignment_id row_label_id,
fi.item_date start_date,
fi.item_date end_date,
fi.global_exp_period_end_date week_end_date,
'MONTH' scale_type,
fi.item_quantity quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
where fi.resource_id = p_resource_id
and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.assignment_id = asgn.assignment_id
and fi.forecast_item_type = 'A'
and fi.delete_flag = 'N'
and fi.item_date between p_start_date and p_end_date
and col.lookup_code = decode(asgn.assignment_type,
  'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                                'STAFFED_ASGMT_CONF',
                                'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
  'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_PROV'))
);

Line: 1286


          INSERT INTO pa_time_chart_temp (
select
decode(fi_week.availability_flag, 'Y', 'AVAILABLE', 'N', 'OVERCOMMITTED') time_chart_record_type,
decode(fi_week.availability_flag, 'Y', -99, 'N', -100) row_label_id,
fi.global_exp_period_end_date-6 start_date,
fi.global_exp_period_end_date end_date,
fi.global_exp_period_end_date week_end_date,
'THREE_MONTH' scale_type,
sum(decode(fi_week.availability_flag, 'Y', fi.availability_quantity, 'N', fi.overcommitment_quantity)) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col,
(select resource_id,
  global_exp_period_end_date week_end_date,
  decode(sign(sum(capacity_quantity)*g_res_capacity_percentage-sum(availability_quantity)),1, 'N', 'Y') availability_flag,
  decode(sign(sum(capacity_quantity)*g_overcommitment_percentage-sum(overcommitment_quantity)), 1, 'N',
         0, decode(sum(overcommitment_quantity), 0, 'N', 'Y'),
         -1, 'Y') overcommitment_flag,
  forecast_item_type,
  delete_flag
  from pa_forecast_items
  where item_date between p_start_date and p_end_date
  group by resource_id,
  global_exp_period_end_date,
  forecast_item_type,
  delete_flag) fi_week
where fi.resource_id = p_resource_id
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.resource_id = fi_week.resource_id
and fi.item_date between p_start_date and p_end_date
and fi.global_exp_period_end_date = fi_week.week_end_date
and fi.forecast_item_type = 'U'
and fi.forecast_item_type = fi_week.forecast_item_type
and fi.delete_flag = 'N'
and fi.delete_flag = fi_week.delete_flag
and (fi.availability_quantity > 0 or fi.overcommitment_quantity > 0)
and (fi_week.availability_flag = 'Y' or fi_week.overcommitment_flag = 'Y')
and col.lookup_code = decode(fi_week.availability_flag, 'Y', 'AVAILABLE', 'N', 'OVERCOMMITTED')
GROUP BY fi.resource_id,
fi.global_exp_period_end_date,
fi_week.availability_flag,
col.file_name,
col.render_priority
UNION ALL
select
'ASSIGNMENT' time_chart_record_type,
fi.assignment_id row_label_id,
fi.global_exp_period_end_date-6 start_date,
fi.global_exp_period_end_date end_date,
fi.global_exp_period_end_date week_end_date,
'THREE_MONTH' scale_type,
sum(fi.item_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
where fi.resource_id = p_resource_id
and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.assignment_id = asgn.assignment_id
and fi.forecast_item_type = 'A'
and fi.delete_flag = 'N'
and fi.item_date between p_start_date and p_end_date
and col.lookup_code = decode(asgn.assignment_type,
  'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                                'STAFFED_ASGMT_CONF',
                                'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
  'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_PROV'))
group by fi.assignment_id,
fi.global_exp_period_end_date,
col.render_priority,
col.file_name
);

Line: 1372


          INSERT INTO pa_time_chart_temp (
select
'ASSIGNMENT' time_chart_record_type,
fi.assignment_id row_label_id,
fi.global_exp_period_end_date-6 start_date,
fi.global_exp_period_end_date end_date,
fi.global_exp_period_end_date week_end_date,
'THREE_MONTH' scale_type,
sum(fi.item_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
where fi.resource_id = p_resource_id
and fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.assignment_id = asgn.assignment_id
and fi.forecast_item_type = 'A'
and fi.delete_flag = 'N'
and fi.item_date between p_start_date and p_end_date
and col.lookup_code = decode(asgn.assignment_type,
  'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                                'STAFFED_ASGMT_CONF',
                                'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
  'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_PROV'))
group by fi.assignment_id,
fi.global_exp_period_end_date,
col.render_priority,
col.file_name
);

Line: 1419


          INSERT INTO pa_time_chart_temp (
select
'OVERCOMMITTED' time_chart_record_type,
-100 row_label_id,
fi.item_date start_date,
fi.item_date end_date,
fi.global_exp_period_end_date week_end_date,
'MONTH' scale_type,
(fi_assigned.assigned_quantity-fi.capacity_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col,
(select resource_id,
 sum(item_quantity) assigned_quantity,
 item_date,
 delete_flag
 from
 (select fi1.resource_id,
  fi1.item_quantity,
  fi1.item_date,
  fi1.delete_flag
  from pa_forecast_items fi1, pa_project_assignments asgn, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
  where (fi1.assignment_id = p_assignment_id
      or fi1.assignment_id in
      (select conflict_assignment_id
       from pa_assignment_conflict_hist
       where assignment_id = p_assignment_id
       and conflict_group_id = p_conflict_group_id
       and self_conflict_flag = 'N'
       and intra_txn_conflict_flag = 'Y'))
  and fi1.assignment_id = asgn.assignment_id
  and asgn.assignment_id = sch.assignment_id
  and asgn.apprvl_status_code NOT IN ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
  and fi1.item_date between sch.start_date and sch.end_date
  and sch.status_code = a.project_status_code
  and a.wf_success_status_code = b.project_status_code
  and b.project_system_status_code = 'STAFFED_ASGMT_CONF'
  and fi1.forecast_item_type = 'A'
  UNION ALL
  select fi2.resource_id,
  item_quantity,
  fi2.item_date,
  fi2.delete_flag
  from pa_forecast_items fi2, pa_project_assignments asgn, pa_assignment_conflict_hist hist
  where fi2.assignment_id = asgn.assignment_id
  and fi2.assignment_id = hist.conflict_assignment_id
  and hist.conflict_group_id = p_conflict_group_id
  and hist.assignment_id = p_assignment_id
  and hist.self_conflict_flag = 'N'
  and fi2.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
  and ((asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED') and hist.intra_txn_conflict_flag = 'Y')
        or hist.intra_txn_conflict_flag = 'N')
  and fi2.forecast_item_type = 'A'
  UNION ALL
  select fi2.resource_id,
  item_quantity,
  fi2.item_date,
  fi2.delete_flag
  from pa_forecast_items fi2, pa_project_assignments asgn
  where fi2.assignment_id = p_assignment_id
  and fi2.assignment_id = asgn.assignment_id
  and fi2.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
  and asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
  and fi2.forecast_item_type = 'A'
  )
  group by resource_id, item_date, delete_flag
)FI_ASSIGNED
where fi.forecast_item_type = 'U'
and ((fi_assigned.assigned_quantity-fi.capacity_quantity*(1+G_OVERCOMMITMENT_PERCENTAGE) >= 0 and G_OVERCOMMITMENT_PERCENTAGE > 0)
   or(fi_assigned.assigned_quantity-fi.capacity_quantity > 0 and G_OVERCOMMITMENT_PERCENTAGE = 0))
and fi.delete_flag = 'N'
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.delete_flag = fi_assigned.delete_flag
and fi.item_date between p_start_date and p_end_date
and fi.item_date = fi_assigned.item_date
and fi.resource_id = p_resource_id
and fi.resource_id = fi_assigned.resource_id
and col.lookup_code = 'OVERCOMMITTED'
UNION ALL
select
'ASSIGNMENT' time_chart_record_type,
fi.assignment_id row_label_id,fi.item_date start_date,
fi.item_date end_date,
fi.global_exp_period_end_date week_end_date,
'MONTH' scale_type,
fi.item_quantity quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
where fi.resource_id = p_resource_id
and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25)))
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.assignment_id = asgn.assignment_id
and fi.forecast_item_type = 'A'
and fi.delete_flag = 'N'
and fi.item_date between p_start_date and p_end_date
and col.lookup_code = decode(asgn.assignment_type,
  'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                                'STAFFED_ASGMT_CONF',
                                'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
  'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_PROV'))
);

Line: 1534


          INSERT INTO pa_time_chart_temp (
select
'ASSIGNMENT' time_chart_record_type,
fi.assignment_id row_label_id,fi.item_date start_date,
fi.item_date end_date,
fi.global_exp_period_end_date week_end_date,
'MONTH' scale_type,
fi.item_quantity quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
where fi.resource_id = p_resource_id
and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25)))
and fi.assignment_id = asgn.assignment_id
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.forecast_item_type = 'A'
and fi.delete_flag = 'N'
and fi.item_date between p_start_date and p_end_date
and col.lookup_code = decode(asgn.assignment_type,
  'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                                'STAFFED_ASGMT_CONF',
                                'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
  'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_PROV'))
);

Line: 1571


          INSERT INTO pa_time_chart_temp (
select
'OVERCOMMITTED' time_chart_record_type,
-100 row_label_id,
fi.global_exp_period_end_date - 6 start_date,
fi.global_exp_period_end_date end_date,
fi.global_exp_period_end_date week_end_date,
'THREE_MONTH' scale_type,
sum(fi_assigned.assigned_quantity-fi.capacity_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col,
(select resource_id,
 sum(item_quantity) assigned_quantity,
 item_date,
 delete_flag
 from
 (select fi1.resource_id,
  fi1.item_quantity,
  fi1.item_date,
  fi1.delete_flag
  from pa_forecast_items fi1, pa_project_assignments asgn, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
  where (fi1.assignment_id = p_assignment_id
      or fi1.assignment_id in
      (select conflict_assignment_id
       from pa_assignment_conflict_hist
       where assignment_id = p_assignment_id
       and conflict_group_id = p_conflict_group_id
       and self_conflict_flag = 'N'
       and intra_txn_conflict_flag = 'Y'))
  and fi1.assignment_id = asgn.assignment_id
  and asgn.assignment_id = sch.assignment_id
  and asgn.apprvl_status_code NOT IN ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
  and fi1.item_date between sch.start_date and sch.end_date
  and sch.status_code = a.project_status_code
  and a.wf_success_status_code = b.project_status_code
  and b.project_system_status_code = 'STAFFED_ASGMT_CONF'
  and fi1.forecast_item_type = 'A'
  UNION ALL
  select fi2.resource_id,
  item_quantity,
  fi2.item_date,
  fi2.delete_flag
  from pa_forecast_items fi2, pa_project_assignments asgn, pa_assignment_conflict_hist hist
  where fi2.assignment_id = asgn.assignment_id
  and fi2.assignment_id = hist.conflict_assignment_id
  and hist.conflict_group_id = p_conflict_group_id
  and hist.assignment_id = p_assignment_id
  and hist.self_conflict_flag = 'N'
  and fi2.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
  and ((asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED') and hist.intra_txn_conflict_flag = 'Y')
        or hist.intra_txn_conflict_flag = 'N')
  and fi2.forecast_item_type = 'A'
  UNION ALL
  select fi2.resource_id,
  item_quantity,
  fi2.item_date,
  fi2.delete_flag
  from pa_forecast_items fi2, pa_project_assignments asgn
  where fi2.assignment_id = p_assignment_id
  and fi2.assignment_id = asgn.assignment_id
  and fi2.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
  and asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
  and fi2.forecast_item_type = 'A'
  )
  group by resource_id, item_date, delete_flag
)FI_ASSIGNED
where forecast_item_type = 'U'
and ((fi_assigned.assigned_quantity-fi.capacity_quantity*(1+G_OVERCOMMITMENT_PERCENTAGE) >= 0 and G_OVERCOMMITMENT_PERCENTAGE > 0)
   or(fi_assigned.assigned_quantity-fi.capacity_quantity > 0 and G_OVERCOMMITMENT_PERCENTAGE = 0))
and fi.delete_flag = 'N'
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.delete_flag = fi_assigned.delete_flag
and fi.item_date between p_start_date and p_end_date
and fi.item_date = fi_assigned.item_date
and fi.resource_id = p_resource_id
and fi.resource_id = fi_assigned.resource_id
and col.lookup_code = 'OVERCOMMITTED'
GROUP BY fi.resource_id,
fi.global_exp_period_end_date,
col.file_name,
col.render_priority
UNION ALL
select
'ASSIGNMENT' time_chart_record_type,
fi.assignment_id row_label_id,
fi.global_exp_period_end_date-6 start_date,
fi.global_exp_period_end_date end_date,
fi.global_exp_period_end_date week_end_date,
'THREE_MONTH' scale_type,
sum(fi.item_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
where fi.resource_id = p_resource_id
and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25)))
and fi.assignment_id = asgn.assignment_id
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.forecast_item_type = 'A'
and fi.delete_flag = 'N'
and fi.item_date between p_start_date and p_end_date
and col.lookup_code = decode(asgn.assignment_type,
  'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                                'STAFFED_ASGMT_CONF',
                                'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
  'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_PROV'))
GROUP BY fi.assignment_id,
fi.global_exp_period_end_date,
col.file_name,
col.render_priority
);

Line: 1695


          INSERT INTO pa_time_chart_temp (
select
'ASSIGNMENT' time_chart_record_type,
fi.assignment_id row_label_id,
fi.global_exp_period_end_date-6 start_date,
fi.global_exp_period_end_date end_date,
fi.global_exp_period_end_date week_end_date,
'THREE_MONTH' scale_type,
sum(fi.item_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
where fi.resource_id = p_resource_id
and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25)))
and fi.assignment_id = asgn.assignment_id
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.forecast_item_type = 'A'
and fi.delete_flag = 'N'
and fi.item_date between p_start_date and p_end_date
and col.lookup_code = decode(asgn.assignment_type,
  'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                                'STAFFED_ASGMT_CONF',
                                'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
  'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_PROV'))
GROUP BY fi.assignment_id,
fi.global_exp_period_end_date,
col.file_name,
col.render_priority
);

Line: 1737


          INSERT INTO pa_time_chart_temp (
select
'OVERCOMMITTED' time_chart_record_type,
-100 row_label_id,
fi.item_date start_date,
fi.item_date end_date,
fi. global_exp_period_end_date week_end_date,
'MONTH' scale_type,
(fi_assigned.assigned_quantity-fi.capacity_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col,
(select resource_id,
   sum(item_quantity) assigned_quantity,
   item_date,
   delete_flag
   from
   (select fi1.resource_id,
   item_quantity,
   fi1.item_date,
   fi1.delete_flag
   from pa_forecast_items fi1, pa_project_assignments asgn, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
   where (fi1.assignment_id = p_assignment_id
      or fi1.assignment_id in
      (select conflict_assignment_id
       from pa_assignment_conflict_hist
       where assignment_id = p_assignment_id
       and conflict_group_id = p_conflict_group_id
       and self_conflict_flag = 'N'
       and intra_txn_conflict_flag = 'Y'))
   and fi1.assignment_id = asgn.assignment_id
   and asgn.assignment_id = sch.assignment_id
   and asgn.apprvl_status_code NOT IN ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
   and fi1.item_date between sch.start_date and sch.end_date
   and sch.status_code = a.project_status_code
   and a.wf_success_status_code = b.project_status_code
   and b.project_system_status_code = 'STAFFED_ASGMT_CONF'
   and forecast_item_type = 'A'
   UNION ALL
   select fi2.resource_id,
   item_quantity,
   fi2.item_date,
   fi2.delete_flag
   from pa_forecast_items fi2, pa_project_assignments asgn, pa_assignment_conflict_hist hist
   where fi2.assignment_id = asgn.assignment_id
   and fi2.assignment_id = hist.conflict_assignment_id
   and hist.conflict_group_id = p_conflict_group_id
   and hist.assignment_id = p_assignment_id
   and hist.self_conflict_flag = 'N'
   and fi2.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
   and ((asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED') and hist.intra_txn_conflict_flag = 'Y')
           or hist.intra_txn_conflict_flag = 'N')
   and fi2.forecast_item_type = 'A'
   UNION ALL
   select fi2.resource_id,
   item_quantity,
   fi2.item_date,
   fi2.delete_flag
   from pa_forecast_items fi2, pa_project_assignments asgn
   where fi2.assignment_id = p_assignment_id
   and fi2.assignment_id = asgn.assignment_id
   and fi2.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
   and asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
   and fi2.forecast_item_type = 'A'
   )
   group by resource_id, item_date, delete_flag
)FI_ASSIGNED,
(select fi_week_capacity.resource_id,
    decode(sign((fi_week_assigned.assigned_quantity-fi_week_capacity.capacity_quantity)-fi_week_capacity.capacity_quantity*G_OVERCOMMITMENT_PERCENTAGE),
    -1, 'N',
    0, decode(fi_week_assigned.assigned_quantity-fi_week_capacity.capacity_quantity, 0, 'N', 'Y'),
    1, 'Y') overcom_flag,
    fi_week_capacity.global_exp_period_end_date,
    fi_week_capacity.delete_flag
    from
   (select resource_id,
      sum(item_quantity) assigned_quantity,
      global_exp_period_end_date,
      delete_flag
      from
     (select fi3.resource_id,
       fi3.item_quantity,
       fi3.global_exp_period_end_date,
       fi3.delete_flag
       from pa_forecast_items fi3, pa_project_assignments asgn, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
       where (fi3.assignment_id = p_assignment_id
       or fi3.assignment_id in
        (select conflict_assignment_id
         from pa_assignment_conflict_hist
         where assignment_id = p_assignment_id
         and conflict_group_id = p_conflict_group_id
         and self_conflict_flag = 'N'
         and intra_txn_conflict_flag = 'Y'))
       and fi3.assignment_id = asgn.assignment_id
       and asgn.assignment_id = sch.assignment_id
       and asgn.apprvl_status_code NOT IN ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
       and fi3.item_date between sch.start_date and sch.end_date
       and sch.status_code = a.project_status_code
       and a.wf_success_status_code = b.project_status_code
       and b.project_system_status_code = 'STAFFED_ASGMT_CONF'
       and fi3.forecast_item_type = 'A'
       UNION ALL
       select fi4.resource_id,
       fi4.item_quantity,
       fi4.global_exp_period_end_date,
       fi4.delete_flag
       from pa_forecast_items fi4, pa_project_assignments asgn, pa_assignment_conflict_hist hist
       where fi4.assignment_id = asgn.assignment_id
       and fi4.assignment_id = hist.conflict_assignment_id
       and hist.conflict_group_id = p_conflict_group_id
       and hist.assignment_id = p_assignment_id
       and hist.self_conflict_flag = 'N'
       and fi4.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
       and ((asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED') and hist.intra_txn_conflict_flag = 'Y')
          or hist.intra_txn_conflict_flag = 'N')
       and fi4.forecast_item_type = 'A'
       UNION ALL
       select fi4.resource_id,
       item_quantity,
       fi4.global_exp_period_end_date,
       fi4.delete_flag
       from pa_forecast_items fi4, pa_project_assignments asgn
       where fi4.assignment_id = p_assignment_id
       and fi4.assignment_id = asgn.assignment_id
       and fi4.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
       and asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
       and fi4.forecast_item_type = 'A')
     group by resource_id, global_exp_period_end_date, delete_flag) FI_WEEK_ASSIGNED,
     (select resource_id,
      sum(capacity_quantity) capacity_quantity,
      global_exp_period_end_date,
      delete_flag
      from pa_forecast_items
      where forecast_item_type = 'U'
      group by resource_id, global_exp_period_end_date, delete_flag) FI_WEEK_CAPACITY
   where fi_week_capacity.resource_id = fi_week_assigned.resource_id
   and fi_week_capacity.global_exp_period_end_date = fi_week_assigned.global_exp_period_end_date
   and fi_week_capacity.delete_flag = fi_week_assigned.delete_flag
)FI_WEEK
where fi.resource_id = p_resource_id
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.resource_id = fi_assigned.resource_id
and fi_assigned.resource_id = fi_week.resource_id
and fi.forecast_item_type = 'U'
and fi.delete_flag = 'N'
and fi.delete_flag = fi_assigned.delete_flag
and fi_assigned.delete_flag = fi_week.delete_flag
and fi.item_date between p_start_date and p_end_date
and fi.item_date = fi_assigned.item_date
and fi.global_exp_period_end_date = fi_week.global_exp_period_end_date
and fi_week.overcom_flag = 'Y'
and col.lookup_code = 'OVERCOMMITTED'
UNION ALL
select
'ASSIGNMENT' time_chart_record_type,
fi.assignment_id row_label_id,
fi.item_date start_date,
fi.item_date end_date,
fi.global_exp_period_end_date week_end_date,
'MONTH' scale_type,
fi.item_quantity quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
where fi.resource_id = p_resource_id
and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25)))
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.assignment_id = asgn.assignment_id
and fi.forecast_item_type = 'A'
and fi.delete_flag = 'N'
and fi.item_date between p_start_date and p_end_date
and col.lookup_code = decode(asgn.assignment_type,
  'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                                'STAFFED_ASGMT_CONF',
                                'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
  'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_PROV'))
);

Line: 1927


          INSERT INTO pa_time_chart_temp (
select
'ASSIGNMENT' time_chart_record_type,
fi.assignment_id row_label_id,
fi.item_date start_date,
fi.item_date end_date,
fi.global_exp_period_end_date week_end_date,
'MONTH' scale_type,
fi.item_quantity quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
where fi.resource_id = p_resource_id
and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25)))
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.assignment_id = asgn.assignment_id
and fi.forecast_item_type = 'A'
and fi.delete_flag = 'N'
and fi.item_date between p_start_date and p_end_date
and col.lookup_code = decode(asgn.assignment_type,
  'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                                'STAFFED_ASGMT_CONF',
                                'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
  'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_PROV'))
);

Line: 1965


          INSERT INTO pa_time_chart_temp (
select
'OVERCOMMITTED' time_chart_record_type,
-100 row_label_id,
fi_week.global_exp_period_end_date-6 start_date,
fi_week.global_exp_period_end_date end_date,
fi_week.global_exp_period_end_date week_end_date,
'THREE_MONTH' scale_type,
fi_week.overcom_quantity quantity,
col.render_priority,
col.file_name color_file_name
from pa_timeline_colors col,
(select
   fi_week_capacity.resource_id,
   decode(sign((fi_week_assigned.assigned_quantity-fi_week_capacity.capacity_quantity)-fi_week_capacity.capacity_quantity*G_OVERCOMMITMENT_PERCENTAGE),
   -1, 0, fi_week_assigned.assigned_quantity-fi_week_capacity.capacity_quantity) overcom_quantity,
   fi_week_capacity.global_exp_period_end_date,
   fi_week_capacity.delete_flag
   from
   (select resource_id,
      sum(item_quantity) assigned_quantity,
      global_exp_period_end_date,
      delete_flag
      from
     (select fi3.resource_id,
       fi3.item_quantity,
       fi3.global_exp_period_end_date,
       fi3.delete_flag
       from pa_forecast_items fi3, pa_project_assignments asgn, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
       where (fi3.assignment_id = p_assignment_id
       or fi3.assignment_id in
        (select conflict_assignment_id
         from pa_assignment_conflict_hist
         where assignment_id = p_assignment_id
         and conflict_group_id = p_conflict_group_id
         and self_conflict_flag = 'N'
         and intra_txn_conflict_flag = 'Y'))
       and fi3.assignment_id = asgn.assignment_id
       and asgn.assignment_id = sch.assignment_id
       and asgn.apprvl_status_code NOT IN ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
       and fi3.item_date between sch.start_date and sch.end_date
       and sch.status_code = a.project_status_code
       and a.wf_success_status_code = b.project_status_code
       and b.project_system_status_code = 'STAFFED_ASGMT_CONF'
       and fi3.forecast_item_type = 'A'
       UNION ALL
       select fi4.resource_id,
       fi4.item_quantity,
       fi4.global_exp_period_end_date,
       fi4.delete_flag
       from pa_forecast_items fi4, pa_project_assignments asgn, pa_assignment_conflict_hist hist
       where fi4.assignment_id = asgn.assignment_id
       and fi4.assignment_id = hist.conflict_assignment_id
       and hist.conflict_group_id = p_conflict_group_id
       and hist.assignment_id = p_assignment_id
       and hist.self_conflict_flag = 'N'
       and fi4.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
       and ((asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED') and hist.intra_txn_conflict_flag = 'Y')
          or hist.intra_txn_conflict_flag = 'N')
       and fi4.forecast_item_type = 'A'
       UNION ALL
       select fi4.resource_id,
       item_quantity,
       fi4.global_exp_period_end_date,
       fi4.delete_flag
       from pa_forecast_items fi4, pa_project_assignments asgn
       where fi4.assignment_id = p_assignment_id
       and fi4.assignment_id = asgn.assignment_id
       and fi4.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
       and asgn.apprvl_status_code in ('ASGMT_APPRVL_APPROVED', 'ASGMT_APPRVL_REJECTED')
       and fi4.forecast_item_type = 'A')
       group by resource_id, global_exp_period_end_date, delete_flag) FI_WEEK_ASSIGNED,
       (select resource_id,
        sum(capacity_quantity) capacity_quantity,
        global_exp_period_end_date,
        delete_flag
        from pa_forecast_items
        where forecast_item_type = 'U'
        group by resource_id, global_exp_period_end_date, delete_flag) FI_WEEK_CAPACITY
   where fi_week_capacity.resource_id = fi_week_assigned.resource_id
   and fi_week_capacity.global_exp_period_end_date = fi_week_assigned.global_exp_period_end_date
   and fi_week_capacity.delete_flag = fi_week_assigned.delete_flag
)FI_WEEK
where fi_week.resource_id = p_resource_id
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi_week.delete_flag = 'N'
and fi_week.overcom_quantity > 0
and fi_week.global_exp_period_end_date between p_start_date and p_end_date
and col.lookup_code = 'OVERCOMMITTED'
UNION ALL
select
'ASSIGNMENT' time_chart_record_type,
fi.assignment_id row_label_id,
fi.global_exp_period_end_date-6 start_date,
fi.global_exp_period_end_date end_date,
fi.global_exp_period_end_date week_end_date,
'THREE_MONTH' scale_type,
sum(fi.item_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
where fi.resource_id = p_resource_id
and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25)))
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.assignment_id = asgn.assignment_id
and fi.forecast_item_type = 'A'
and fi.delete_flag = 'N'
and fi.item_date between p_start_date and p_end_date
and col.lookup_code = decode(asgn.assignment_type,
  'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                                'STAFFED_ASGMT_CONF',
                                'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
  'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_PROV'))
group by fi.assignment_id,
fi.global_exp_period_end_date,
col.render_priority,
col.file_name
);

Line: 2096


          INSERT INTO pa_time_chart_temp (
select
'ASSIGNMENT' time_chart_record_type,
fi.assignment_id row_label_id,
fi.global_exp_period_end_date-6 start_date,
fi.global_exp_period_end_date end_date,
fi.global_exp_period_end_date week_end_date,
'THREE_MONTH' scale_type,
sum(fi.item_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi, pa_timeline_colors col, pa_project_assignments asgn
where fi.resource_id = p_resource_id
and (fi.assignment_id = p_assignment_id or fi.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25)))
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi.assignment_id = asgn.assignment_id
and fi.forecast_item_type = 'A'
and fi.delete_flag = 'N'
and fi.item_date between p_start_date and p_end_date
and col.lookup_code = decode(asgn.assignment_type,
  'STAFFED_ADMIN_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                                'STAFFED_ASGMT_CONF',
                                'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
  'STAFFED_ASSIGNMENT', decode(fi.asgmt_sys_status_code,
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_CONF',
                          'STAFFED_ASGMT_PROV'))
group by fi.assignment_id,
fi.global_exp_period_end_date,
col.render_priority,
col.file_name
);

Line: 2143


          INSERT INTO pa_time_chart_temp  (
select
decode(fi1.forecast_item_type, 'A', 'ASSIGNMENT', 'R', 'REQUIREMENT') time_chart_record_type,
fi1.assignment_id row_label_id,
fi1.item_date start_date,
fi1.item_date end_date,
fi1.global_exp_period_end_date week_end_date,
'MONTH' scale_type,
fi1.item_quantity quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
where fi1.forecast_item_type in ('A', 'R')
and fi1.delete_flag = 'N'
and fi1.item_date between p_start_date and p_end_date
and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi1.assignment_id = asgn.assignment_id
and fi1.delete_flag = 'N'
and col.lookup_code = decode(asgn.assignment_type,
                   'STAFFED_ADMIN_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
                                                      'STAFFED_ASGMT_CONF',
                                                      'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
                   'STAFFED_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
                                                'STAFFED_ASGMT_CONF',
                                                'STAFFED_ASGMT_CONF',
                                                'STAFFED_ASGMT_PROV'),
                   'OPEN_ASSIGNMENT',  'OPEN_ASGMT')
/* Commenting for bug 3280808
UNION ALL
 select
'ASSIGNMENT' time_chart_record_type,
fi1.assignment_id row_label_id,
fi1.item_date start_date,
fi1.item_date end_date,
fi1.global_exp_period_end_date week_end_date,
'MONTH' scale_type,
fi_admin.item_quantity quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi1, pa_timeline_colors col,
(select fi.resource_id,
   fi.assignment_id,
   fi.global_exp_period_end_date week_end_date,
   fi.item_date,
   fi.item_quantity,
   fi.forecast_item_type,
   fi.delete_flag
   from pa_forecast_items fi, pa_project_assignments asgn
   where fi.assignment_id = asgn.assignment_id
   and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
   and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
where fi1.item_date between p_start_date and p_end_date
and fi1.forecast_item_type = 'A'
and fi1.forecast_item_type = fi_admin.forecast_item_type
and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and fi1.resource_id = fi_admin.resource_id
and fi1.item_date = fi_admin.item_date
and fi1.delete_flag = 'N'
and fi1.delete_flag = fi_admin.delete_flag
and col.lookup_code = 'CONFIRMED_ADMIN'
End of commenting for bug 3280808 */
UNION ALL
select
'ASSIGNMENT' time_chart_record_type,
fi1.assignment_id row_label_id,
fi1.item_date start_date,
fi1.item_date end_date,
fi1.global_exp_period_end_date week_end_date,
'MONTH' scale_type,
fi_overcom.overcommitment_quantity quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi1, pa_timeline_colors col,
(select fi.resource_id,
   fi.item_date,
   fi.delete_flag,
   decode(sign(fi.capacity_quantity*g_overcommitment_percentage-fi.overcommitment_quantity), 1, 0, fi.overcommitment_quantity) overcommitment_quantity
   from pa_forecast_items fi
   where fi.forecast_item_type = 'U' ) fi_overcom
where fi1.resource_id = fi_overcom.resource_id
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi1.forecast_item_type = 'A'
and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and fi1.item_date between p_start_date and p_end_date
and fi1.item_date = fi_overcom.item_date
and fi1.delete_flag = 'N'
and fi1.delete_flag = fi_overcom.delete_flag
and col.lookup_code = 'OVERCOMMITTED'
and fi_overcom.overcommitment_quantity > 0
);

Line: 2260


          INSERT INTO pa_time_chart_temp  (
select
decode(fi1.forecast_item_type, 'A', 'ASSIGNMENT', 'R', 'REQUIREMENT') time_chart_record_type,
fi1.assignment_id row_label_id,
fi1.item_date start_date,
fi1.item_date end_date,
fi1.global_exp_period_end_date week_end_date,
'MONTH' scale_type,
fi1.item_quantity quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
where fi1.forecast_item_type in ('A', 'R')
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi1.delete_flag = 'N'
and fi1.item_date between p_start_date and p_end_date
and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and fi1.assignment_id = asgn.assignment_id
and fi1.delete_flag = 'N'
and col.lookup_code = decode(asgn.assignment_type,
                   'STAFFED_ADMIN_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
                                                      'STAFFED_ASGMT_CONF',
                                                      'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
                   'STAFFED_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
                                                'STAFFED_ASGMT_CONF',
                                                'STAFFED_ASGMT_CONF',
                                                'STAFFED_ASGMT_PROV'),
                   'OPEN_ASSIGNMENT',  'OPEN_ASGMT')
/* Commenting for bug 3280808
UNION ALL
 select
'ASSIGNMENT' time_chart_record_type,
fi1.assignment_id row_label_id,
fi1.item_date start_date,
fi1.item_date end_date,
fi1.global_exp_period_end_date week_end_date,
'MONTH' scale_type,
fi_admin.item_quantity quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi1, pa_timeline_colors col,
(select fi.resource_id,
   fi.assignment_id,
   fi.global_exp_period_end_date week_end_date,
   fi.item_date,
   fi.item_quantity,
   fi.forecast_item_type,
   fi.delete_flag
   from pa_forecast_items fi, pa_project_assignments asgn
   where fi.assignment_id = asgn.assignment_id
   and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
   and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
where fi1.item_date between p_start_date and p_end_date
and fi1.forecast_item_type = 'A'
and fi1.forecast_item_type = fi_admin.forecast_item_type
and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and fi1.resource_id = fi_admin.resource_id
and fi1.item_date = fi_admin.item_date
and fi1.delete_flag = 'N'
and fi1.delete_flag = fi_admin.delete_flag
and col.lookup_code = 'CONFIRMED_ADMIN'
End of commenting for bug 3280808 */
);

Line: 2341


          INSERT INTO pa_time_chart_temp (
select
decode(fi1.forecast_item_type, 'A', 'ASSIGNMENT', 'R', 'REQUIREMENT') time_chart_record_type,
fi1.assignment_id row_label_id,
fi1.global_exp_period_end_date-6 start_date,
fi1.global_exp_period_end_date end_date,
fi1.global_exp_period_end_date week_end_date,
'THREE_MONTH' scale_type,
sum(fi1.item_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
where fi1.forecast_item_type in ('A', 'R')
and fi1.delete_flag = 'N'
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi1.item_date between p_start_date and p_end_date
and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and fi1.assignment_id = asgn.assignment_id
and col.lookup_code = decode(asgn.assignment_type,
                   'STAFFED_ADMIN_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
                                                      'STAFFED_ASGMT_CONF',
                                                      'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
                   'STAFFED_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
                                                'STAFFED_ASGMT_CONF',
                                                'STAFFED_ASGMT_CONF',
                                                'STAFFED_ASGMT_PROV'),
                   'OPEN_ASSIGNMENT',  'OPEN_ASGMT')
group by fi1.assignment_id,
fi1.global_exp_period_end_date,
fi1.forecast_item_type,
col.file_name,
col.render_priority
/* Commenting for bug 3280808
UNION ALL
 select
'ASSIGNMENT' time_chart_record_type,
fi1.assignment_id row_label_id,
trunc(fi1.global_exp_period_end_date)-6 start_date,
trunc(fi1.global_exp_period_end_date) end_date,
trunc(fi1.global_exp_period_end_date) week_end_date,
'THREE_MONTH' scale_type,
sum(fi_admin.item_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi1, pa_timeline_colors col,
(select fi.resource_id,
   fi.assignment_id,
   fi.global_exp_period_end_date week_end_date,
   fi.item_date,
   fi.item_quantity,
   fi.forecast_item_type,
   fi.delete_flag
   from pa_forecast_items fi, pa_project_assignments asgn
   where fi.assignment_id = asgn.assignment_id
   and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
   and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
where fi1.resource_id = fi_admin.resource_id
and fi1.forecast_item_type = 'A'
and fi1.forecast_item_type = fi_admin.forecast_item_type
and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and fi1.item_date between p_start_date and p_end_date
and fi1.item_date = fi_admin.item_date
and fi1.delete_flag = 'N'
and fi1.delete_flag = fi_admin.delete_flag
and col.lookup_code = 'CONFIRMED_ADMIN'
group by fi1.assignment_id,
fi1.global_exp_period_end_date,
fi1.forecast_item_type,
col.file_name,
col.render_priority
End of commenting for bug bug 3280808*/
UNION ALL
select
'ASSIGNMENT' time_chart_record_type,
fi1.assignment_id row_label_id,
fi1.global_exp_period_end_date-6 start_date,
fi1.global_exp_period_end_date end_date,
fi1.global_exp_period_end_date week_end_date,
'THREE_MONTH' scale_type,
sum(fi_overcom.overcommitment_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi1, pa_timeline_colors col,
(select fi.resource_id,
   fi.item_date,
   fi.delete_flag,
   decode(sign(fi.capacity_quantity*g_overcommitment_percentage-fi.overcommitment_quantity), 1, 0, fi.overcommitment_quantity) overcommitment_quantity
   from pa_forecast_items fi
   where fi.forecast_item_type = 'U' ) fi_overcom
where fi1.resource_id = fi_overcom.resource_id
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi1.forecast_item_type = 'A'
and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and fi1.item_date between p_start_date and p_end_date
and fi1.item_date = fi_overcom.item_date
and fi1.delete_flag = 'N'
and fi1.delete_flag = fi_overcom.delete_flag
and col.lookup_code = 'OVERCOMMITTED'
and fi_overcom.overcommitment_quantity > 0
group by fi1.assignment_id,
fi1.global_exp_period_end_date,
col.file_name,
col.render_priority
);

Line: 2471


          INSERT INTO pa_time_chart_temp (
select
decode(fi1.forecast_item_type, 'A', 'ASSIGNMENT', 'R', 'REQUIREMENT') time_chart_record_type,
fi1.assignment_id row_label_id,
fi1.global_exp_period_end_date-6 start_date,
fi1.global_exp_period_end_date end_date,
fi1.global_exp_period_end_date week_end_date,
'THREE_MONTH' scale_type,
sum(fi1.item_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
where fi1.forecast_item_type in ('A', 'R')
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi1.delete_flag = 'N'
and fi1.item_date between p_start_date and p_end_date
and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and fi1.assignment_id = asgn.assignment_id
and col.lookup_code = decode(asgn.assignment_type,
                   'STAFFED_ADMIN_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
                                                      'STAFFED_ASGMT_CONF',
                                                      'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
                   'STAFFED_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
                                                'STAFFED_ASGMT_CONF',
                                                'STAFFED_ASGMT_CONF',
                                                'STAFFED_ASGMT_PROV'),
                   'OPEN_ASSIGNMENT',  'OPEN_ASGMT')
group by fi1.assignment_id,
fi1.global_exp_period_end_date,
fi1.forecast_item_type,
col.file_name,
col.render_priority
/* Commenting for bug 3280808
UNION ALL
 select
'ASSIGNMENT' time_chart_record_type,
fi1.assignment_id row_label_id,
trunc(fi1.global_exp_period_end_date)-6 start_date,
trunc(fi1.global_exp_period_end_date) end_date,
trunc(fi1.global_exp_period_end_date) week_end_date,
'THREE_MONTH' scale_type,
sum(fi_admin.item_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi1, pa_timeline_colors col,
(select fi.resource_id,
   fi.assignment_id,
   fi.global_exp_period_end_date week_end_date,
   fi.item_date,
   fi.item_quantity,
   fi.forecast_item_type,
   fi.delete_flag
   from pa_forecast_items fi, pa_project_assignments asgn
   where fi.assignment_id = asgn.assignment_id
   and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
   and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
where fi1.resource_id = fi_admin.resource_id
and fi1.forecast_item_type = 'A'
and fi1.forecast_item_type = fi_admin.forecast_item_type
and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and fi1.item_date between p_start_date and p_end_date
and fi1.item_date = fi_admin.item_date
and fi1.delete_flag = 'N'
and fi1.delete_flag = fi_admin.delete_flag
and col.lookup_code = 'CONFIRMED_ADMIN'
group by fi1.assignment_id,
fi1.global_exp_period_end_date,
fi1.forecast_item_type,
col.file_name,
col.render_priority
End of commenting for bug bug 3280808*/
);

Line: 2570


            INSERT INTO pa_time_chart_temp  (
select
decode(fi1.forecast_item_type, 'A', 'ASSIGNMENT', 'R', 'REQUIREMENT') time_chart_record_type,
fi1.assignment_id row_label_id,
fi1.item_date start_date,
fi1.item_date end_date,
fi1.global_exp_period_end_date week_end_date,
'MONTH' scale_type,
fi1.item_quantity quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
where fi1.forecast_item_type in ('A', 'R')
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi1.delete_flag = 'N'
and fi1.item_date between p_start_date and p_end_date
and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and fi1.assignment_id = asgn.assignment_id
and fi1.delete_flag = 'N'
and col.lookup_code = decode(asgn.assignment_type,
                   'STAFFED_ADMIN_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
                                                      'STAFFED_ASGMT_CONF',
                                                      'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
                   'STAFFED_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
                                                'STAFFED_ASGMT_CONF',
                                                'STAFFED_ASGMT_CONF',
                                                'STAFFED_ASGMT_PROV'),
                   'OPEN_ASSIGNMENT',  'OPEN_ASGMT')
/*Commenting for bug 3280808
UNION ALL
select
'ASSIGNMENT' time_chart_record_type,
fi1.assignment_id row_label_id,
fi1.item_date start_date,
fi1.item_date end_date,
fi1.global_exp_period_end_date week_end_date,
'MONTH' scale_type,
fi_admin.item_quantity quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi1, pa_timeline_colors col,
(select fi.resource_id,
   fi.assignment_id,
   fi.global_exp_period_end_date week_end_date,
   fi.item_date,
   fi.item_quantity,
   fi.forecast_item_type,
   fi.delete_flag
   from pa_forecast_items fi, pa_project_assignments asgn
   where fi.assignment_id = asgn.assignment_id
   and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
   and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
where fi1.item_date between p_start_date and p_end_date
and fi1.forecast_item_type = 'A'
and fi1.forecast_item_type = fi_admin.forecast_item_type
and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and fi1.resource_id = fi_admin.resource_id
and fi1.item_date = fi_admin.item_date
and fi1.delete_flag = 'N'
and fi1.delete_flag = fi_admin.delete_flag
and col.lookup_code = 'CONFIRMED_ADMIN'
End of commenting for bug 3280808 */
UNION ALL
select
'ASSIGNMENT' time_chart_record_type,
fi1.assignment_id row_label_id,
fi1.item_date start_date,
fi1.item_date end_date,
fi1.global_exp_period_end_date week_end_date,
'MONTH' scale_type,
fi_overcom.overcommitment_quantity quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi1, pa_timeline_colors col,
(select fi.resource_id,
   fi.item_date,
   fi.delete_flag,
   decode(fi_week.overcommitment_flag, 'Y', fi.overcommitment_quantity, 'N', 0) overcommitment_quantity
   from pa_forecast_items fi,
-- Added below for Bug# 6524548
     (select paf.resource_id,
        paf.global_exp_period_end_date,
        decode(sign(sum(paf.capacity_quantity)*g_overcommitment_percentage-sum(paf.overcommitment_quantity)),
1, 'N',
         0, decode(sum(paf.overcommitment_quantity), 0, 'N', 'Y'),
     --(select resource_id,
     --   global_exp_period_end_date,
     --   decode(sign(sum(capacity_quantity)*g_overcommitment_percentage-sum(overcommitment_quantity)), 1, 'N',
     --    0, decode(sum(overcommitment_quantity), 0, 'N', 'Y'),
-- End for Bug# 6524548
         -1, 'Y') overcommitment_flag,
-- Added below for Bug# 6524548
        paf.forecast_item_type,
        paf.delete_flag
      from pa_forecast_items paf,
           PA_PROJECT_ASSIGNMENTS PAP
     where PAF.RESOURCE_ID = PAP.RESOURCE_ID
       AND PAF.DELETE_FLAG = 'N'
       AND PAP.ASSIGNMENT_ID IN ( l_row_label_id_tbl(1), l_row_label_id_tbl(2),
                                  l_row_label_id_tbl(3), l_row_label_id_tbl(4),
				  l_row_label_id_tbl(5), l_row_label_id_tbl(6),
				  l_row_label_id_tbl(7), l_row_label_id_tbl(8),
                                  l_row_label_id_tbl(9), l_row_label_id_tbl(10),
				  l_row_label_id_tbl(11),l_row_label_id_tbl(12),
				  l_row_label_id_tbl(13),l_row_label_id_tbl(14),
                                  l_row_label_id_tbl(15),l_row_label_id_tbl(16),
				  l_row_label_id_tbl(17),l_row_label_id_tbl(18),
				  l_row_label_id_tbl(19),l_row_label_id_tbl(20),
                                  l_row_label_id_tbl(21),l_row_label_id_tbl(22),
				  l_row_label_id_tbl(23),l_row_label_id_tbl(24),
				  l_row_label_id_tbl(25) )
            AND PAF.item_date between p_start_date and p_end_date
      group by PAF.resource_id, PAF.global_exp_period_end_date, PAF.forecast_item_type, PAF.delete_flag)fi_week
        --forecast_item_type,
        --delete_flag
      --from pa_forecast_items
      --where item_date between p_start_date and p_end_date
      --group by resource_id, global_exp_period_end_date, forecast_item_type, delete_flag)fi_week
-- End for Bug# 6524548
   where fi.resource_id = fi_week.resource_id
   and fi.global_exp_period_end_date = fi_week.global_exp_period_end_date
   and fi.forecast_item_type = 'U'
   and fi.forecast_item_type = fi_week.forecast_item_type
   and fi.delete_flag = fi_week.delete_flag) fi_overcom
where fi1.resource_id = fi_overcom.resource_id
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi1.forecast_item_type = 'A'
and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and fi1.item_date between p_start_date and p_end_date
and fi1.item_date = fi_overcom.item_date
and fi1.delete_flag = 'N'
and fi1.delete_flag = fi_overcom.delete_flag
and col.lookup_code = 'OVERCOMMITTED'
and fi_overcom.overcommitment_quantity > 0
);

Line: 2731


            INSERT INTO pa_time_chart_temp  (
select
decode(fi1.forecast_item_type, 'A', 'ASSIGNMENT', 'R', 'REQUIREMENT') time_chart_record_type,
fi1.assignment_id row_label_id,
fi1.item_date start_date,
fi1.item_date end_date,
fi1.global_exp_period_end_date week_end_date,
'MONTH' scale_type,
fi1.item_quantity quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
where fi1.forecast_item_type in ('A', 'R')
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi1.delete_flag = 'N'
and fi1.item_date between p_start_date and p_end_date
and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and fi1.assignment_id = asgn.assignment_id
and fi1.delete_flag = 'N'
and col.lookup_code = decode(asgn.assignment_type,
                   'STAFFED_ADMIN_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
                                                      'STAFFED_ASGMT_CONF',
                                                      'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
                   'STAFFED_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
                                                'STAFFED_ASGMT_CONF',
                                                'STAFFED_ASGMT_CONF',
                                                'STAFFED_ASGMT_PROV'),
                   'OPEN_ASSIGNMENT',  'OPEN_ASGMT')
/*Commenting for bug 3280808
UNION ALL
select
'ASSIGNMENT' time_chart_record_type,
fi1.assignment_id row_label_id,
fi1.item_date start_date,
fi1.item_date end_date,
fi1.global_exp_period_end_date week_end_date,
'MONTH' scale_type,
fi_admin.item_quantity quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi1, pa_timeline_colors col,
(select fi.resource_id,
   fi.assignment_id,
   fi.global_exp_period_end_date week_end_date,
   fi.item_date,
   fi.item_quantity,
   fi.forecast_item_type,
   fi.delete_flag
   from pa_forecast_items fi, pa_project_assignments asgn
   where fi.assignment_id = asgn.assignment_id
   and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
   and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
where fi1.item_date between p_start_date and p_end_date
and fi1.forecast_item_type = 'A'
and fi1.forecast_item_type = fi_admin.forecast_item_type
and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and fi1.resource_id = fi_admin.resource_id
and fi1.item_date = fi_admin.item_date
and fi1.delete_flag = 'N'
and fi1.delete_flag = fi_admin.delete_flag
and col.lookup_code = 'CONFIRMED_ADMIN'
End of commenting for bug 3280808 */
);

Line: 2820


            INSERT INTO pa_time_chart_temp  (
select
decode(fi1.forecast_item_type, 'A', 'ASSIGNMENT', 'R', 'REQUIREMENT') time_chart_record_type,
fi1.assignment_id row_label_id,
fi1.global_exp_period_end_date-6 start_date,
fi1.global_exp_period_end_date end_date,
fi1.global_exp_period_end_date week_end_date,
'THREE_MONTH' scale_type,
sum(fi1.item_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
where fi1.forecast_item_type in ('A', 'R')
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi1.delete_flag = 'N'
and fi1.item_date between p_start_date and p_end_date
and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and fi1.assignment_id = asgn.assignment_id
and col.lookup_code = decode(asgn.assignment_type,
                   'STAFFED_ADMIN_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
                                                      'STAFFED_ASGMT_CONF',
                                                      'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
                   'STAFFED_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
                                                'STAFFED_ASGMT_CONF',
                                                'STAFFED_ASGMT_CONF',
                                                'STAFFED_ASGMT_PROV'),
                   'OPEN_ASSIGNMENT',  'OPEN_ASGMT')
group by fi1.assignment_id,
fi1.global_exp_period_end_date,
fi1.forecast_item_type,
col.file_name,
col.render_priority
/* Commenting for bug 3280808
UNION ALL
select
'ASSIGNMENT' time_chart_record_type,
fi1.assignment_id row_label_id,
trunc(fi1.global_exp_period_end_date)-6 start_date,
trunc(fi1.global_exp_period_end_date) end_date,
trunc(fi1.global_exp_period_end_date) week_end_date,
'THREE_MONTH' scale_type,
sum(fi_admin.item_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi1, pa_timeline_colors col,
(select fi.resource_id,
   fi.assignment_id,
   fi.global_exp_period_end_date week_end_date,
   fi.item_date,
   fi.item_quantity,
   fi.forecast_item_type,
   fi.delete_flag
   from pa_forecast_items fi, pa_project_assignments asgn
   where fi.assignment_id = asgn.assignment_id
   and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
   and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
where fi1.resource_id = fi_admin.resource_id
and fi1.forecast_item_type = 'A'
and fi1.forecast_item_type = fi_admin.forecast_item_type
and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and fi1.item_date between p_start_date and p_end_date
and fi1.item_date = fi_admin.item_date
and fi1.delete_flag = 'N'
and fi1.delete_flag = fi_admin.delete_flag
and col.lookup_code = 'CONFIRMED_ADMIN'
group by fi1.assignment_id,
fi1.global_exp_period_end_date,
fi1.forecast_item_type,
col.file_name,
col.render_priority
End of Commenting for bug 3280808 */
UNION ALL
select
'ASSIGNMENT' time_chart_record_type,
fi1.assignment_id row_label_id,
fi1.global_exp_period_end_date-6 start_date,
fi1.global_exp_period_end_date end_date,
fi1.global_exp_period_end_date week_end_date,
'THREE_MONTH' scale_type,
sum(fi_overcom.overcommitment_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi1, pa_timeline_colors col,
(select fi.resource_id,
   fi.item_date,
   fi.delete_flag,
   decode(fi_week.overcommitment_flag, 'Y', fi.overcommitment_quantity, 'N', 0) overcommitment_quantity
   from pa_forecast_items fi,
-- Added below for Bug# 6524548
     (select PAF.resource_id,
        PAF.global_exp_period_end_date,
        decode(sign(sum(PAF.capacity_quantity)*g_overcommitment_percentage-sum(PAF.overcommitment_quantity)),
1, 'N',
         0, decode(sum(PAF.overcommitment_quantity), 0, 'N', 'Y'),
     --(select resource_id,
     --   global_exp_period_end_date,
     --   decode(sign(sum(capacity_quantity)*g_overcommitment_percentage-sum(overcommitment_quantity)), 1, 'N',
     --    0, decode(sum(overcommitment_quantity), 0, 'N', 'Y'),
-- End for Bug# 6524548
         -1, 'Y') overcommitment_flag,
  -- Added below for Bug# 6524548
        PAF.forecast_item_type,
        PAF.delete_flag
      from pa_forecast_items PAF,
           PA_PROJECT_ASSIGNMENTS PAP
     where PAF.RESOURCE_ID = PAP.RESOURCE_ID
       AND PAF.DELETE_FLAG = 'N'
       AND PAP.ASSIGNMENT_ID IN ( l_row_label_id_tbl(1), l_row_label_id_tbl(2),
                                  l_row_label_id_tbl(3), l_row_label_id_tbl(4),
				  l_row_label_id_tbl(5), l_row_label_id_tbl(6),
				  l_row_label_id_tbl(7), l_row_label_id_tbl(8),
                                  l_row_label_id_tbl(9), l_row_label_id_tbl(10),
				  l_row_label_id_tbl(11),l_row_label_id_tbl(12),
				  l_row_label_id_tbl(13),l_row_label_id_tbl(14),
                                  l_row_label_id_tbl(15),l_row_label_id_tbl(16),
				  l_row_label_id_tbl(17),l_row_label_id_tbl(18),
				  l_row_label_id_tbl(19),l_row_label_id_tbl(20),
                                  l_row_label_id_tbl(21),l_row_label_id_tbl(22),
				  l_row_label_id_tbl(23),l_row_label_id_tbl(24),
				  l_row_label_id_tbl(25) )
	AND PAF.item_date between p_start_date and p_end_date
      group by PAF.resource_id, PAF.global_exp_period_end_date, PAF.forecast_item_type, PAF.delete_flag)fi_week
        --forecast_item_type,
        --delete_flag
      --from pa_forecast_items
      --where item_date between p_start_date and p_end_date
      --group by resource_id, global_exp_period_end_date, forecast_item_type, delete_flag)fi_week
   -- End for Bug# 6524548
   where fi.resource_id = fi_week.resource_id
   and fi.global_exp_period_end_date = fi_week.global_exp_period_end_date
   and fi.forecast_item_type = 'U'
   and fi.forecast_item_type = fi_week.forecast_item_type
   and fi.delete_flag = fi_week.delete_flag) fi_overcom
where fi1.resource_id = fi_overcom.resource_id
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi1.forecast_item_type = 'A'
and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and fi1.item_date between p_start_date and p_end_date
and fi1.item_date = fi_overcom.item_date
and fi1.delete_flag = 'N'
and fi1.delete_flag = fi_overcom.delete_flag
and col.lookup_code = 'OVERCOMMITTED'
and fi_overcom.overcommitment_quantity > 0
group by fi1.assignment_id,
fi1.global_exp_period_end_date,
col.file_name,
col.render_priority
);

Line: 2994


            INSERT INTO pa_time_chart_temp  (
select
decode(fi1.forecast_item_type, 'A', 'ASSIGNMENT', 'R', 'REQUIREMENT') time_chart_record_type,
fi1.assignment_id row_label_id,
fi1.global_exp_period_end_date-6 start_date,
fi1.global_exp_period_end_date end_date,
fi1.global_exp_period_end_date week_end_date,
'THREE_MONTH' scale_type,
sum(fi1.item_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi1, pa_timeline_colors col, pa_project_assignments asgn
where fi1.forecast_item_type in ('A', 'R')
and col.lookup_type =  'TIMELINE_STATUS' -- Added for Bug 5079783
and fi1.delete_flag = 'N'
and fi1.item_date between p_start_date and p_end_date
and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and fi1.assignment_id = asgn.assignment_id
and col.lookup_code = decode(asgn.assignment_type,
                   'STAFFED_ADMIN_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
                                                      'STAFFED_ASGMT_CONF',
                                                      'CONFIRMED_ADMIN', 'STAFFED_ASGMT_PROV'),
                   'STAFFED_ASSIGNMENT', decode(fi1.asgmt_sys_status_code,
                                                'STAFFED_ASGMT_CONF',
                                                'STAFFED_ASGMT_CONF',
                                                'STAFFED_ASGMT_PROV'),
                   'OPEN_ASSIGNMENT',  'OPEN_ASGMT')
group by fi1.assignment_id,
fi1.global_exp_period_end_date,
fi1.forecast_item_type,
col.file_name,
col.render_priority
/* Commenting for bug 3280808
UNION ALL
select
'ASSIGNMENT' time_chart_record_type,
fi1.assignment_id row_label_id,
trunc(fi1.global_exp_period_end_date)-6 start_date,
trunc(fi1.global_exp_period_end_date) end_date,
trunc(fi1.global_exp_period_end_date) week_end_date,
'THREE_MONTH' scale_type,
sum(fi_admin.item_quantity) quantity,
col.render_priority,
col.file_name color_file_name
from pa_forecast_items fi1, pa_timeline_colors col,
(select fi.resource_id,
   fi.assignment_id,
   fi.global_exp_period_end_date week_end_date,
   fi.item_date,
   fi.item_quantity,
   fi.forecast_item_type,
   fi.delete_flag
   from pa_forecast_items fi, pa_project_assignments asgn
   where fi.assignment_id = asgn.assignment_id
   and asgn.assignment_type = 'STAFFED_ADMIN_ASSIGNMENT'
   and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF') fi_admin
where fi1.resource_id = fi_admin.resource_id
and fi1.forecast_item_type = 'A'
and fi1.forecast_item_type = fi_admin.forecast_item_type
and fi1.assignment_id in (l_row_label_id_tbl(1), l_row_label_id_tbl(2),
l_row_label_id_tbl(3), l_row_label_id_tbl(4),l_row_label_id_tbl(5),
l_row_label_id_tbl(6), l_row_label_id_tbl(7),l_row_label_id_tbl(8),
l_row_label_id_tbl(9), l_row_label_id_tbl(10),l_row_label_id_tbl(11),
l_row_label_id_tbl(12), l_row_label_id_tbl(13),l_row_label_id_tbl(14),
l_row_label_id_tbl(15), l_row_label_id_tbl(16),l_row_label_id_tbl(17),
l_row_label_id_tbl(18), l_row_label_id_tbl(19),l_row_label_id_tbl(20),
l_row_label_id_tbl(21), l_row_label_id_tbl(22),l_row_label_id_tbl(23),
l_row_label_id_tbl(24), l_row_label_id_tbl(25))
and fi1.item_date between p_start_date and p_end_date
and fi1.item_date = fi_admin.item_date
and fi1.delete_flag = 'N'
and fi1.delete_flag = fi_admin.delete_flag
and col.lookup_code = 'CONFIRMED_ADMIN'
group by fi1.assignment_id,
fi1.global_exp_period_end_date,
fi1.forecast_item_type,
col.file_name,
col.render_priority
End of Commenting for bug 3280808 */
);