[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT calendar_id,
start_date,
end_date,
Monday_hours,
Tuesday_hours,
Wednesday_hours,
Thursday_hours,
Friday_hours,
Saturday_hours,
Sunday_hours
BULK COLLECT INTO
l_calendar_id_tbl ,
l_start_date_tbl ,
l_end_date_tbl ,
l_Monday_hours_tbl ,
l_Tuesday_hours_tbl ,
l_Wednesday_hours_tbl ,
l_Thursday_hours_tbl ,
l_Friday_hours_tbl ,
l_Saturday_hours_tbl ,
l_Sunday_hours_tbl
FROM PA_SCHEDULES sch
WHERE sch.assignment_id = p_assignment_id
ORDER BY sch.start_date;
-- This cursor will select the schedule records of the passing calendar
/* Bug - 1846658- Following lines are commented to incorporate the BULK SELECT to enhance the performance*/
-- CURSOR C1 IS SELECT calendar_id,start_date,end_date,Monday_hours,Tuesday_hours,Wednesday_hours,Thursday_hours,
-- Friday_hours,Saturday_hours,Sunday_hours
-- FROM PA_SCHEDULES sch
-- WHERE sch.calendar_id = p_calendar_id
-- AND sch.schedule_type_code = 'CALENDAR'
-- AND ( ( p_start_date BETWEEN sch.start_date AND sch.end_date)
-- OR ( p_end_date BETWEEN sch.start_date AND sch.end_date)
-- OR ( p_start_date < sch.start_date AND p_end_date > sch.end_date) ) ;
/* Bug - 1846658- Following lines are added to incorporate the BULK SELECT to enhance the performance*/
TYPE calendar_id_tbl IS TABLE OF PA_SCHEDULES.calendar_id%TYPE
INDEX BY BINARY_INTEGER;
/* Bug - 1846658- Following lines are commented to incorporate the BULK SELECT to enhance the performance*/
-- FOR v_c1 IN C1 LOOP
-- l_curr_schedule_rec(1).start_date := v_c1.start_date;
/* Bug - 1846658- Following lines are added to incorporate the BULK SELECT to enhance the performance*/
SELECT calendar_id,
start_date,
end_date,
Monday_hours,
Tuesday_hours,
Wednesday_hours,
Thursday_hours,
Friday_hours,
Saturday_hours,
Sunday_hours
BULK COLLECT INTO
l_calendar_id_tbl ,
l_start_date_tbl ,
l_end_date_tbl ,
l_Monday_hours_tbl ,
l_Tuesday_hours_tbl ,
l_Wednesday_hours_tbl ,
l_Thursday_hours_tbl ,
l_Friday_hours_tbl ,
l_Saturday_hours_tbl ,
l_Sunday_hours_tbl
FROM PA_SCHEDULES sch
WHERE sch.calendar_id = p_calendar_id
AND sch.schedule_type_code = 'CALENDAR'
AND ( ( p_start_date BETWEEN sch.start_date AND sch.end_date)
OR ( p_end_date BETWEEN sch.start_date AND sch.end_date)
OR ( p_start_date < sch.start_date AND p_end_date > sch.end_date) )
ORDER BY sch.start_date;
PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,p_start_date =>p_start_date,
p_end_date =>l_out_schedule_rec(l_I).start_date -1 ,p_monday_hours =>0.00,p_tuesday_hours =>0.00,
p_wednesday_hours =>0.00,p_thursday_hours =>0.00,p_friday_hours =>0.00,p_saturday_hours =>0.00,
p_sunday_hours =>0.00,x_return_status => l_x_return_status,x_msg_count => x_msg_count,
x_msg_data =>x_msg_data);
PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,
p_start_date =>l_out_schedule_rec(l_J).end_date + 1 , p_end_date => p_end_date,p_monday_hours =>0.00,
p_tuesday_hours =>0.00, p_wednesday_hours =>0.00,p_thursday_hours =>0.00,p_friday_hours =>0.00,
p_saturday_hours =>0.00,p_sunday_hours =>0.00,
x_return_status => l_x_return_status,x_msg_count => x_msg_count,x_msg_data =>x_msg_data);
-- This cursor will select only those records of passing assignment which are open or staffed.
-- 1561861 Added 'STAFFED_ADMIN_ASSIGNMENT' to the where clause'.
/* Bug - 1846658- Following lines are commented to incorporate the BULK SELECT to enhance the performance*/
-- CURSOR C1 IS SELECT schedule_id, calendar_id,
-- assignment_id,project_id,schedule_type_code,status_code,
-- system_status_code,start_date,end_date,Monday_hours,Tuesday_hours,
-- Wednesday_hours,Thursday_hours,
-- Friday_hours,Saturday_hours,Sunday_hours
-- FROM PA_SCHEDULES_V sch
-- WHERE sch.assignment_id = p_assignment_id
-- AND sch.schedule_type_code IN
-- ('OPEN_ASSIGNMENT','STAFFED_ASSIGNMENT', 'STAFFED_ADMIN_ASSIGNMENT')
-- AND ( ( p_start_date BETWEEN sch.start_date AND sch.end_date)
-- OR ( p_end_date BETWEEN sch.start_date AND sch.end_date)
-- OR ( p_start_date < sch.start_date AND p_end_date > sch.end_date) )
-- ORDER BY start_date;
/* Bug - 1846658- Following lines are added to incorporate the BULK SELECT to enhance the performance*/
TYPE schedule_id_tbl IS TABLE OF PA_SCHEDULES_V.schedule_id%TYPE
INDEX BY BINARY_INTEGER;
SELECT calendar_id, min(start_date), max(end_date)
FROM PA_SCHEDULES
WHERE assignment_id = x_assignment_id
AND schedule_type_code='OPEN_ASSIGNMENT'
GROUP BY Calendar_id;
/* Bug - 1846658- Following lines are commented to incorporate the BULK SELECT to enhance the performance*/
-- FOR v_c1 IN C1 LOOP
-- l_curr_schedule_rec(1).assignment_id := v_c1.assignment_id;
/* Bug - 1846658- Following lines are added to incorporate the BULK SELECT to enhance the performance*/
SELECT schedule_id,
calendar_id,
assignment_id,
project_id,
schedule_type_code,
status_code,
system_status_code,
start_date,
end_date,
Monday_hours,
Tuesday_hours,
Wednesday_hours,
Thursday_hours,
Friday_hours,
Saturday_hours,
Sunday_hours
BULK COLLECT INTO
l_schedule_id_tbl,
l_calendar_id_tbl,
l_assignment_id_tbl,
l_project_id_tbl,
l_schedule_type_code_tbl,
l_status_code_tbl ,
l_system_status_code_tbl,
l_start_date_tbl,
l_end_date_tbl,
l_Monday_hours_tbl,
l_Tuesday_hours_tbl,
l_Wednesday_hours_tbl,
l_Thursday_hours_tbl,
l_Friday_hours_tbl,
l_Saturday_hours_tbl,
l_Sunday_hours_tbl
FROM PA_SCHEDULES_V sch
WHERE sch.assignment_id = p_assignment_id
AND sch.schedule_type_code IN
('OPEN_ASSIGNMENT','STAFFED_ASSIGNMENT', 'STAFFED_ADMIN_ASSIGNMENT')
AND ( ( p_start_date BETWEEN sch.start_date AND sch.end_date)
OR ( p_end_date BETWEEN sch.start_date AND sch.end_date)
OR ( p_start_date < sch.start_date AND p_end_date > sch.end_date) )
ORDER BY start_date;
PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,
p_start_date =>p_start_date,
p_end_date =>l_out_schedule_rec(l_I).start_date -1,
p_monday_hours =>0.00,
p_tuesday_hours =>0.00,
p_wednesday_hours =>0.00,
p_thursday_hours =>0.00,
p_friday_hours =>0.00,
p_saturday_hours =>0.00,
p_sunday_hours =>0.00,
x_return_status => l_x_return_status,
x_msg_count => x_msg_count,
x_msg_data =>x_msg_data);
PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,
p_start_date =>l_out_schedule_rec(l_J).end_date + 1,
p_end_date => p_end_date,
p_monday_hours =>0.00,
p_tuesday_hours =>0.00,
p_wednesday_hours =>0.00,
p_thursday_hours =>0.00,
p_friday_hours =>0.00,
p_saturday_hours =>0.00,
p_sunday_hours =>0.00,
x_return_status => l_x_return_status,
x_msg_count => x_msg_count,
x_msg_data =>x_msg_data);
-- This cursor will select the schedule records corresponding to the passing assignment id
-- 1561861 Added 'STAFFED_ADMIN_ASSIGNMENT' to the where clause.
/* Bug - 1846658- Following lines are commented to incorporate the BULK SELECT to enhance the performance*/
-- CURSOR C1 IS SELECT schedule_id, calendar_id,
-- assignment_id,project_id,schedule_type_code,status_code,
-- system_status_code,start_date,end_date,Monday_hours,Tuesday_hours,Wednesday_hours,
-- Thursday_hours,
-- Friday_hours,Saturday_hours,Sunday_hours
-- FROM PA_SCHEDULES_V sch
-- WHERE sch.assignment_id = p_assignment_id
-- AND sch.schedule_type_code IN
-- ('OPEN_ASSIGNMENT','STAFFED_ASSIGNMENT', 'STAFFED_ADMIN_ASSIGNMENT')
-- ORDER BY start_date;
/* Bug - 1846658- Following lines are added to incorporate the BULK SELECT to enhance the performance*/
TYPE schedule_id_tbl IS TABLE OF PA_SCHEDULES_V.schedule_id%TYPE
INDEX BY BINARY_INTEGER;
/* Bug - 1846658- Following lines are commented to incorporate the BULK SELECT to enhance the performance*/
-- FOR v_c1 IN C1 LOOP
--
-- l_curr_schedule_rec(1).assignment_id := v_c1.assignment_id;
/* Bug - 1846658- Following lines are added to incorporate the BULK SELECT to enhance the performance*/
SELECT schedule_id,
calendar_id,
assignment_id,
project_id,
schedule_type_code,
status_code,
system_status_code,
start_date,
end_date,
Monday_hours,
Tuesday_hours,
Wednesday_hours,
Thursday_hours,
Friday_hours,
Saturday_hours,
Sunday_hours
BULK COLLECT INTO
l_schedule_id_tbl,
l_calendar_id_tbl,
l_assignment_id_tbl,
l_project_id_tbl,
l_schedule_type_code_tbl,
l_status_code_tbl ,
l_system_status_code_tbl,
l_start_date_tbl,
l_end_date_tbl,
l_Monday_hours_tbl,
l_Tuesday_hours_tbl,
l_Wednesday_hours_tbl,
l_Thursday_hours_tbl,
l_Friday_hours_tbl,
l_Saturday_hours_tbl,
l_Sunday_hours_tbl
FROM PA_SCHEDULES_V sch
WHERE sch.assignment_id = p_assignment_id
AND sch.schedule_type_code IN ('OPEN_ASSIGNMENT','STAFFED_ASSIGNMENT', 'STAFFED_ADMIN_ASSIGNMENT')
ORDER BY start_date;
CURSOR C1 IS SELECT calendar_id,trunc(start_date_time) start_date,
NVL(trunc(end_date_time),TO_DATE('01/01/2050','MM/DD/YYYY')) end_date
FROM jtf_cal_resource_assign jtf_res
WHERE jtf_res.resource_id = l_t_resource_id
AND jtf_res.primary_calendar_flag = 'Y'
AND jtf_res.calendar_id > 0
AND jtf_res.resource_type_code = 'RS_EMPLOYEE'
AND ( ( l_tc_start_date BETWEEN trunc(jtf_res.start_date_time) AND
nvl(trunc(jtf_res.end_date_time),l_tc_end_date))
OR ( l_tc_end_date BETWEEN jtf_res.start_date_time AND
nvl(trunc(jtf_res.end_date_time),l_tc_end_date))
OR ( l_tc_start_date < jtf_res.start_date_time AND
l_tc_end_date > nvl(trunc(jtf_res.end_date_time),l_tc_end_date)) )
order by start_date;
select resource_organization_id, min(resource_effective_start_date)
from pa_resources_denorm
where resource_id = x_prm_resource_id
group by resource_organization_id;
select distinct NVL(resource_id,-99)
into l_resource_id
from pa_project_parties
where project_party_id = p_source_id;
select resource_id
into l_prm_resource_id
from pa_project_parties
where project_party_id = p_source_id;
select resource_organization_id, resource_effective_start_date
into l_resource_organization_id, l_temp_start_date
from pa_resources_denorm
where resource_effective_start_date =
(select min(res1.resource_effective_start_date)
from pa_resources_denorm res1
where res1.resource_id = l_prm_resource_id
and res1.resource_effective_start_date >= trunc(sysdate))
and resource_id = l_prm_resource_id;
PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,p_start_date =>l_tc_start_date,
p_end_date =>l_out_schedule_rec(l_I).start_date -1 ,p_monday_hours =>0.00,p_tuesday_hours =>0.00,
p_wednesday_hours =>0.00,p_thursday_hours =>0.00,p_friday_hours =>0.00,p_saturday_hours =>0.00,
p_sunday_hours =>0.00,x_return_status => l_x_return_status,x_msg_count => x_msg_count,x_msg_data =>x_msg_data);
PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,
p_start_date =>l_out_schedule_rec(l_J).end_date + 1 , p_end_date => l_tc_end_date,p_monday_hours =>0.00,
p_tuesday_hours =>0.00, p_wednesday_hours =>0.00,p_thursday_hours =>0.00,p_friday_hours =>0.00,
p_saturday_hours =>0.00,p_sunday_hours =>0.00,
x_return_status => l_x_return_status,x_msg_count => x_msg_count,x_msg_data =>x_msg_data);
SELECT MIN(start_date_time),MAX(NVL(end_date_time,TO_DATE('01/01/2050','MM/DD/YYYY')))
INTO l_temp_start_date,l_temp_end_date
FROM jtf_cal_resource_assign
WHERE jtf_cal_resource_assign.resource_id = l_t_resource_id
AND jtf_cal_resource_assign.calendar_id > 0
AND jtf_cal_resource_assign.resource_type_code = 'RS_EMPLOYEE'
AND jtf_cal_resource_assign.primary_calendar_flag = 'Y';
PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,
p_start_date =>l_last_end_date + 1, p_end_date =>v_c1.start_date -1 ,
p_monday_hours =>0.00,p_tuesday_hours =>0.00,p_wednesday_hours =>0.00,
p_thursday_hours =>0.00,p_friday_hours =>0.00,p_saturday_hours =>0.00,
p_sunday_hours =>0.00,
x_return_status => l_x_return_status,x_msg_count =>
x_msg_count,x_msg_data =>x_msg_data);
l_cur_schedule_rec.delete;
PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,p_start_date =>l_tc_start_date,
p_end_date =>l_out_schedule_rec(l_I).start_date -1 ,p_monday_hours =>0.00,p_tuesday_hours =>0.00,
p_wednesday_hours =>0.00,p_thursday_hours =>0.00,p_friday_hours =>0.00,p_saturday_hours =>0.00,
p_sunday_hours =>0.00,x_return_status => l_x_return_status,x_msg_count => x_msg_count,x_msg_data =>x_msg_data);
PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,
p_start_date =>l_out_schedule_rec(l_J).end_date + 1 , p_end_date => l_tc_end_date,p_monday_hours =>0.00,
p_tuesday_hours =>0.00, p_wednesday_hours =>0.00,p_thursday_hours =>0.00,p_friday_hours =>0.00,
p_saturday_hours =>0.00,p_sunday_hours =>0.00,
x_return_status => l_x_return_status,x_msg_count => x_msg_count,x_msg_data =>x_msg_data);
SELECT rou.resource_effective_start_date,
NVL(rou.resource_effective_end_date,SYSDATE)
BULK COLLECT INTO
l_ResStartDateTab,l_ResEndDateTab
FROM pa_resources_denorm rou
WHERE rou.resource_id= l_resource_id
AND NVL(rou.resource_effective_end_date,SYSDATE) >=
l_cap_first_start_date
AND rou.resource_effective_start_date <= l_cap_last_end_date
ORDER BY rou.resource_effective_start_date;
-- cap records, then insert a record in the beginning to indicate
-- this hole.
IF (l_cap_first_start_date < l_res_first_start_date) THEN
l_resstartdatetab(l_resstartdatetab.first-1) := l_cap_first_start_date-10;
-- cap records, then insert a record in the end to indicate
-- this hole.
IF (l_cap_last_end_date > l_res_last_end_date) THEN
l_resstartdatetab(l_resstartdatetab.last+1) := l_cap_last_end_date+1;
l_ins_sch_record_tab PA_SCHEDULE_GLOB.ScheduleTabTyp; -- variable used for storing the records kept for insertion
-- Calling the SCHEDULE UTILS api which will append the record if it marked for insertion
IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
PA_SCHEDULE_UTILS.Add_Schedule_Rec_Tab(p_chg_sch_record_tab,l_I,l_I,l_ins_sch_record_tab,
l_x_return_status,x_msg_count,x_msg_data);
-- Applying the changes according to their status i.e. insert,update or delete
IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
PA_SCHEDULE_PKG.Insert_Rows(l_ins_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
PA_SCHEDULE_PKG.Update_Rows(l_upd_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
PA_SCHEDULE_PKG.Delete_Rows(p_del_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
PA_SCHEDULE_UTILS.update_sch_rec_tab(x_sch_record_tab,
p_change_type_code => 'I',
x_return_status => l_x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
PA_SCHEDULE_UTILS.update_sch_rec_tab(x_sch_record_tab,
p_project_id => p_sch_except_record.project_id,
p_schedule_type_code => p_sch_except_record.schedule_type_code,
p_assignment_id => p_sch_except_record.assignment_id,
p_assignment_status_code => p_sch_record.assignment_status_code,
x_return_status => l_x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT assignment_type
INTO l_t_asgn_type
FROM pa_project_assignments
where assignment_id = p_sch_except_record.assignment_id;
SELECT project_party_id
INTO l_t_team_player_id
FROM pa_project_assignments
WHERE assignment_id = p_sch_except_record.assignment_id;
SELECT resource_id
INTO l_t_resource_id
FROM pa_project_assignments
WHERE assignment_id = p_sch_except_record.assignment_id;
SELECT calendar_id
INTO l_t_calendar_id
FROM pa_project_assignments
where assignment_id = p_sch_except_record.assignment_id;
SELECT calendar_id
INTO l_t_calendar_id
FROM pa_project_assignments
where assignment_id = p_sch_except_record.assignment_id;
SELECT project_party_id
INTO l_t_team_player_id
FROM pa_project_assignments
WHERE assignment_id = p_sch_except_record.assignment_id;
SELECT resource_id
INTO l_t_resource_id
FROM pa_project_assignments
WHERE assignment_id = p_sch_except_record.assignment_id;
SELECT calendar_id
INTO l_t_calendar_id
FROM pa_projects_all
WHERE project_id = p_sch_except_record.project_id;
PA_SCHEDULE_UTILS.update_sch_rec_tab(x_sch_record_tab,
p_project_id => p_sch_except_record.project_id,
p_schedule_type_code => p_sch_except_record.schedule_type_code,
p_assignment_id => p_sch_except_record.assignment_id,
p_calendar_id => p_sch_except_record.calendar_id,
p_assignment_status_code => p_sch_record.assignment_status_code,
x_return_status => l_x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT project_party_id , resource_calendar_percent,calendar_id,calendar_type
INTO l_t_team_player_id,l_t_res_cal_percent,l_t_calendar_id,l_t_calendar_type
FROM pa_project_assignments
WHERE assignment_id = p_sch_except_record.assignment_id;
PA_SCHEDULE_UTILS.update_sch_rec_tab(x_sch_record_tab,
p_project_id => p_sch_except_record.project_id,
p_schedule_type_code => p_sch_except_record.schedule_type_code,
p_assignment_id => p_sch_except_record.assignment_id,
p_calendar_id => p_sch_except_record.calendar_id,
p_assignment_status_code => p_sch_except_record.assignment_status_code,
x_return_status => l_x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
select
rowid,
calendar_id,
schedule_id,
schedule_type_code,
status_code,
start_date,
end_date,
monday_hours,
tuesday_hours,
wednesday_hours,
thursday_hours,
friday_hours,
saturday_hours,
sunday_hours
from pa_schedules
where project_id = p_project_id and
assignment_id = p_assignment_id
order by start_date;
PA_SCHEDULE_PKG.Update_Rows(l_final_sch_rec_tab,l_x_return_status,x_msg_count,x_msg_data);
PA_SCHEDULE_PKG.Delete_Rows(l_del_sch_rec_tab,l_x_return_status,x_msg_count,x_msg_data);
PA_SCHEDULE_UTILS.update_sch_rec_tab(x_sch_record_tab,
p_project_id => p_sch_except_record.project_id,
p_schedule_type_code => p_sch_except_record.schedule_type_code,
p_assignment_id => p_sch_except_record.assignment_id,
p_calendar_id => p_sch_except_record.calendar_id,
p_assignment_status_code => p_sch_record.assignment_status_code,
x_return_status => l_x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
PA_SCHEDULE_UTILS.update_sch_rec_tab(x_sch_record_tab,
p_project_id => p_sch_except_record.project_id,
p_schedule_type_code => p_sch_except_record.schedule_type_code,
p_assignment_id => p_sch_except_record.assignment_id,
p_calendar_id => p_sch_except_record.calendar_id,
P_monday_hours => p_sch_record.Monday_hours,
P_Tuesday_hours => p_sch_record.Tuesday_hours,
P_Wednesday_hours => p_sch_record.Wednesday_hours,
P_Thursday_hours => p_sch_record.Thursday_hours,
P_Friday_hours => p_sch_record.Friday_hours,
P_Saturday_hours => p_sch_record.Saturday_hours,
P_Sunday_hours => p_sch_record.Sunday_hours,
x_return_status => l_x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
PA_SCHEDULE_UTILS.update_except_record(px_except_record => l_temp_except_rec,
p_start_date => p_sch_except_record.start_date,
p_end_date => l_temp_p_sch_record_tab( l_temp_first).start_date -1,
x_return_status => l_x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
PA_SCHEDULE_UTILS.log_message(2,'inside exp_start_date > sch_start_date AND MARKING SHIFT as DELETE');
PA_SCHEDULE_UTILS.log_message(2,'inside exp_end_date <= sch_end_date AND MARKING DELETE ');
-- Mark remaining shifts as delete. */
IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
PA_SCHEDULE_UTILS.mark_del_sch_rec_tab ( l_temp_p_sch_record_tab.next(l_I),
l_temp_p_sch_record_tab.last,
l_temp_p_sch_record_tab,
l_x_return_status,
x_msg_count,
x_msg_data );
PA_SCHEDULE_UTILS.update_except_record(px_except_record => l_temp_except_rec,
p_start_date => l_temp_p_sch_record_tab(l_I).end_date +1 ,
p_end_date => p_sch_except_record.end_date,
x_return_status => l_x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
PA_SCHEDULE_UTILS.update_except_record(px_except_record => l_temp_except_rec,
p_start_date => l_chg_exp_start_date,
p_end_date => l_chg_exp_end_date,
x_return_status => l_x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
-- This will be used to update the assignments table
-- if there is only one status .
l_J NUMBER; -- To store the first record of the chg_tr_sch_rec_tab
PA_PROJECT_ASSIGNMENTS_PKG.Update_Row(
p_record_version_number => p_record_version_number,
p_assignment_id => l_t_assignment_id,
p_start_date => nvl(l_t_start_date,FND_API.G_MISS_DATE),
p_end_date => nvl(l_t_end_date,FND_API.G_MISS_DATE),
p_multiple_status_flag => l_t_multi_flag,
p_status_code => l_curr_status,
p_assignment_effort =>
pa_schedule_utils.get_num_hours(l_t_project_id, l_t_assignment_id),
x_return_status => l_x_return_status );
PA_PROJECT_ASSIGNMENTS_PKG.Update_Row(
p_record_version_number => p_record_version_number,
p_assignment_id => l_t_assignment_id,
p_assignment_effort =>
pa_schedule_utils.get_num_hours(l_t_project_id, l_t_assignment_id),
x_return_status => l_x_return_status );
-- If assignment record is successfully updated then call procedure to update pa project parties.
--
-- jmarques (1590046): Added STAFFED_ADMIN_ASSIGNMENT check to if statement.
IF ( ( l_x_return_status = FND_API.G_RET_STS_SUCCESS ) AND
( (chg_tr_sch_rec_tab(l_J).schedule_type_code = 'STAFFED_ASSIGNMENT') OR
(chg_tr_sch_rec_tab(l_J).schedule_type_code = 'STAFFED_ADMIN_ASSIGNMENT') )
) THEN
SELECT
proj_part.PROJECT_ROLE_ID,
proj_part.RESOURCE_SOURCE_ID,
proj_part.PROJECT_PARTY_ID,
proj_part.RESOURCE_ID,
proj_part.RECORD_VERSION_NUMBER,
proj_asgn.START_DATE,
proj_asgn.END_DATE
INTO
l_t_project_role_id,
l_t_resource_source_id,
l_t_project_party_id,
l_t_resource_id,
l_t_record_version_number,
l_t_asgn_start_date,
l_t_asgn_end_date
FROM pa_project_parties proj_part,
pa_project_assignments proj_asgn
WHERE proj_asgn.PROJECT_PARTY_ID = proj_part.PROJECT_PARTY_ID
AND proj_asgn.ASSIGNMENT_ID = l_t_assignment_id;
pa_project_parties_pvt.UPDATE_PROJECT_PARTY(
P_VALIDATE_ONLY => 'F',
P_OBJECT_ID => chg_tr_sch_rec_tab(l_J).project_id,
P_OBJECT_TYPE => 'PA_PROJECTS',
P_PROJECT_ROLE_ID => l_t_project_role_id,
P_RESOURCE_TYPE_ID => 101,
P_RESOURCE_SOURCE_ID => l_t_resource_source_id,
P_RESOURCE_ID => l_t_resource_id,
P_START_DATE_ACTIVE => l_t_start_date ,
P_END_DATE_ACTIVE => l_t_end_date ,
P_SCHEDULED_FLAG => 'Y',
P_RECORD_VERSION_NUMBER => l_t_record_version_number,
P_CALLING_MODULE => 'ASSIGNMENT',
P_PROJECT_END_DATE => NULL,
P_PROJECT_ID => chg_tr_sch_rec_tab(l_J).project_id,
P_PROJECT_PARTY_ID => l_t_project_party_id,
P_ASSIGNMENT_ID => l_t_assignment_id,
P_ASSIGN_RECORD_VERSION_NUMBER => p_record_version_number,
--X_ASSIGNMENT_ID => l_t_assignment_id, * Commented for Bug Fix: 4537865
X_ASSIGNMENT_ID => l_new_t_assignment_id, -- Added for bug fix: 4537865
X_WF_TYPE => l_wf_type,
X_WF_ITEM_TYPE => l_wf_item_type,
X_WF_PROCESS => l_wf_process,
X_RETURN_STATUS => l_x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data );
select least(min(start_date), p_start_date), greatest(max(end_date), p_end_date)
from pa_budget_lines a, pa_resource_assignments b
where a.resource_assignment_id = b.resource_assignment_id
and b.project_assignment_id = p_project_assignment_id
and b.project_id = p_project_id
and b.budget_version_id = p_budget_version_id
and ((a.start_date between p_start_date and p_end_date) OR
(a.end_date between p_start_date and p_end_date) OR
(p_start_date between a.start_date and a.end_date) OR
(p_end_date between a.start_date and a.end_date))
and b.ta_display_flag = 'Y';
select least(min(start_date), p_proj_start_date), greatest(max(end_date), p_proj_end_date) from
--select min(start_date, p_proj_start_date), max(end_date, p_proj_end_date) from
pa_budget_lines a, pa_tmp_task_assign_ids b where
(a.start_date between (p_proj_start_date) and (p_proj_end_date)) OR
(a.end_date between (p_proj_start_date) and (p_proj_end_date)) OR
((p_proj_start_date) between a.start_date and a.end_date) OR
((p_proj_end_date) between a.start_date and a.end_date)
AND a.resource_assignment_id = b.resource_assignment_id;
Select project_assignment_id from
PA_TMP_TASK_ASSIGN_IDS where project_assignment_id = p_project_assignment_id;
SELECT ALIAS INTO l_alias_name FROM
pa_resource_list_members WHERE RESOURCE_LIST_MEMBER_ID = (SELECT RESOURCE_LIST_MEMBER_ID
FROM pa_resource_assignments WHERE resource_assignment_id = p_task_assignment_id_tbl(1));
PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API update_sch_rec_tab ....');
PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API update_sch_rec_tab ....'||l_x_return_status);
PA_SCHEDULE_UTILS.update_sch_rec_tab(l_new_schedule_tab_rec,
p_project_id => p_project_id,
p_calendar_id => p_calendar_id,
p_schedule_type_code => 'OPEN_ASSIGNMENT',
p_assignment_id => p_assignment_id,
p_assignment_status_code => p_assignment_status_code,
x_return_status => l_x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API update_sch_rec_tab .....');
PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API insert_rows ....');
-- Inserting the schedule in the PA_SCHEDULE table
PA_SCHEDULE_PKG.insert_rows(l_new_schedule_tab_rec,
l_x_return_status,
x_msg_count,
x_msg_data,
l_total_hours -- Bug 5126919
);
PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API insert_rows ....');
l_assignment_id_tbl.DELETE(l_assignment_id_tbl.FIRST);
-- Update the passed schedule table of record for creating the schedule for open assignment
PA_SCHEDULE_UTILS.update_sch_rec_tab(
px_sch_record_tab => l_current_sch_rec_tab,
p_assignment_id => l_assignment_id_tbl(l_counter).assignment_id,
x_return_status => l_x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
-- Inserting the schedule in the PA_SCHEDULE table
PA_SCHEDULE_PKG.insert_rows(
p_sch_record_tab => l_x_sch_rec_tab,
x_return_status => l_x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API insert_rows ....');
PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API update_sch_rec_tab ....');
-- Update the passed schedule record with p_assignment_status_code
PA_SCHEDULE_UTILS.update_sch_rec_tab(
px_sch_record_tab => l_current_sch_rec_tab,
p_schedule_type_code => 'OPEN_ASSIGNMENT',
p_assignment_id => l_assignment_id_tbl(l_counter).assignment_id,
p_assignment_status_code => p_assignment_status_code,
x_return_status => l_x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API update_sch_rec_tab .....');
PA_SCHEDULE_PKG.insert_rows(
p_sch_record_tab => l_x_sch_rec_tab,
x_return_status => l_x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API insert_rows ....');
l_assignment_id_tbl.DELETE(l_assignment_id_tbl.FIRST);
PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API update_sch_rec_tab ....');
-- Update the passed schedule record with assignment_id.
PA_SCHEDULE_UTILS.update_sch_rec_tab(
px_sch_record_tab => l_current_sch_rec_tab,
p_assignment_id => l_assignment_id_tbl(l_counter).assignment_id,
x_return_status => l_x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API update_sch_rec_tab .....');
PA_SCHEDULE_PKG.insert_rows(
p_sch_record_tab => l_x_sch_rec_tab,
x_return_status => l_x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API insert_rows ....');
SELECT start_date, end_date,calendar_id
into l_req_start_date, l_req_end_date, l_calendar_id
from pa_project_assignments
where assignment_id=p_open_assignment_id;
PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API update_sch_rec_tab ....');
PA_SCHEDULE_UTILS.update_sch_rec_tab(l_new_schedule_tab,
P_project_id => p_project_id,
p_schedule_type_code => 'STAFFED_ASSIGNMENT',
p_calendar_id => p_calendar_id,
p_assignment_id => p_assignment_id,
p_assignment_status_code => p_assignment_status_code,
x_return_status => l_x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API update_sch_rec_tab ....');
PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API insert_rows ....');
-- Inserting the record in PA_SCHEDULES table
PA_SCHEDULE_PKG.insert_rows(
l_new_schedule_tab,
l_x_return_status,
x_msg_count,
x_msg_data,
l_total_hours -- Bug 5126919
);
PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API insert_rows ....');
PROCEDURE delete_asgn_schedules ( p_assignment_id IN NUMBER,
p_perm_delete IN VARCHAR2 := FND_API.G_TRUE,
p_change_id IN NUMBER := null,
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
BEGIN
-- Storing the value for error tracking
l_x_return_status := FND_API.G_RET_STS_SUCCESS;
DELETE pa_schedules
WHERE assignment_id = p_assignment_id;
DELETE pa_schedule_exceptions
WHERE assignment_id = p_assignment_id;
-- Delete entire exception history if p_perm_delete
-- Otherwise, just delete exceptions with change_id >= p_change_id
if FND_API.TO_BOOLEAN(p_perm_delete) then
DELETE pa_schedule_except_history
WHERE assignment_id = p_assignment_id;
DELETE pa_schedule_except_history
WHERE assignment_id = p_assignment_id
and change_id >= p_change_id;
-- Delete entire schedules history if p_perm_delete.
if FND_API.TO_BOOLEAN(p_perm_delete) then
DELETE pa_schedules_history
WHERE assignment_id = p_assignment_id;
-- Calling the Timeline api to delete the timeline records
-- for the assignment
PA_TIMELINE_PVT.DELETE_TIMELINE (p_assignment_id =>p_assignment_id ,
x_return_status =>l_x_return_status ,
x_msg_count =>x_msg_count ,
x_msg_data =>x_msg_data );
p_procedure_name => 'delete_asgn_schedules');
END delete_asgn_schedules;
PROCEDURE update_sch_wf_success(
p_assignment_id IN NUMBER,
p_record_version_number 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_next_status_code pa_project_assignments.status_code%type;
SELECT schedule_id, status_code, start_date, end_date
FROM pa_schedules
WHERE assignment_id = p_assignment_id
ORDER BY start_date;
SELECT project_id, calendar_id,
assignment_type, start_date, end_date
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
PA_SCHEDULE_UTILS.log_message(1,'Start of the update_sch_wf_success API');
SELECT min(start_date), max(end_date)
INTO l_start_date, l_end_date
FROM pa_schedules
where assignment_id= p_assignment_id;
SELECT COUNT(*)
INTO l_count
FROM pa_schedules
WHERE assignment_id = p_assignment_id;
update pa_schedules_history
set last_approved_flag = 'N'
where assignment_id = p_assignment_id
and last_approved_flag = 'Y';
PA_SCHEDULE_UTILS.log_message(1,'End of the update_sch_wf_success API ... '
|| l_x_return_status);
PA_SCHEDULE_UTILS.log_message(1,'ERROR in update_sch_wf_success API ..'||sqlerrm);
p_procedure_name => 'update_sch_wf_success');
END update_sch_wf_success;
PROCEDURE update_sch_wf_failure(
p_assignment_id IN NUMBER,
p_record_version_number 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_next_status_code pa_project_assignments.status_code%type;
SELECT schedule_id, status_code, start_date, end_date
FROM pa_schedules
WHERE assignment_id = p_assignment_id
ORDER BY start_date;
SELECT project_id, calendar_id,
assignment_type, start_date, end_date
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
PA_SCHEDULE_UTILS.log_message(1,'Start of the update_sch_wf_failure API');
SELECT COUNT(*)
INTO l_count
FROM pa_schedules
WHERE assignment_id = p_assignment_id;
PA_SCHEDULE_UTILS.log_message(1,'End of the update_sch_wf_failure API ... ');
PA_SCHEDULE_UTILS.log_message(1,'ERROR in update_sch_wf_failure API ..'||sqlerrm);
p_procedure_name => 'update_sch_wf_failure');
END update_sch_wf_failure;
select schedule_id, calendar_id, assignment_id, project_id,
schedule_type_code, status_code, start_date, end_date, monday_hours,
tuesday_hours, wednesday_hours, thursday_hours, friday_hours,
saturday_hours, sunday_hours, change_id, last_approved_flag, request_id,
program_application_id, program_id, program_update_date, creation_date,
created_by, last_update_date, last_update_by, last_update_login
from pa_schedules_history
where assignment_id = p_assignment_id
and last_approved_flag = 'Y';
-- Delete schedules for the assignment in order to insert new assignment
-- records.
delete_asgn_schedules(
p_assignment_id => p_assignment_id,
p_perm_delete => FND_API.G_FALSE,
p_change_id => p_change_id,
x_return_status => l_x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
-- Insert row into PA_SCHEDULES
pa_schedule_pkg.insert_rows (
p_calendar_id => rec.calendar_id,
p_assignment_id => rec.assignment_id ,
p_project_id => rec.project_id ,
p_schedule_type_code => rec.schedule_type_code,
p_assignment_status_code => rec.status_code ,
p_start_date => rec.start_date ,
p_end_date => rec.end_date ,
p_monday_hours => rec.monday_hours ,
p_tuesday_hours => rec.tuesday_hours ,
p_wednesday_hours => rec.wednesday_hours ,
p_thursday_hours => rec.thursday_hours,
p_friday_hours => rec.friday_hours ,
p_saturday_hours => rec.saturday_hours,
p_sunday_hours => rec.sunday_hours ,
x_return_status => l_x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data);
-- Call create_timeline and delete records from schedule history
-- if we inserted any rows.
if l_index <> 0 then
delete pa_schedules_history
where assignment_id = p_assignment_id
and last_approved_flag = 'Y';
PROCEDURE update_history_table(
p_assignment_id IN NUMBER,
p_change_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_index NUMBER;
select assignment_id
from pa_schedules_history
where assignment_id = p_assignment_id
and change_id = p_change_id;
select schedule_id, calendar_id, assignment_id, project_id,
schedule_type_code, status_code, start_date, end_date, monday_hours,
tuesday_hours, wednesday_hours, thursday_hours, friday_hours,
saturday_hours, sunday_hours, creation_date, created_by, last_update_date,
last_update_by, last_update_login, request_id, program_application_id,
program_id, program_update_date
from pa_schedules
where assignment_id = p_assignment_id;
PA_SCHEDULE_UTILS.log_message(1,'Start of the update_history_table API');
-- then update table.
open c1;
insert into pa_schedules_history
( schedule_id, calendar_id, assignment_id, project_id,
schedule_type_code, status_code, start_date, end_date, monday_hours,
tuesday_hours, wednesday_hours, thursday_hours, friday_hours,
saturday_hours, sunday_hours, change_id, last_approved_flag,
creation_date, created_by, last_update_date, last_update_by,
last_update_login, request_id, program_application_id, program_id,
program_update_date)
values
( rec.schedule_id, rec.calendar_id, rec.assignment_id, rec.project_id,
rec.schedule_type_code, rec.status_code, rec.start_date, rec.end_date,
rec.monday_hours, rec.tuesday_hours, rec.wednesday_hours,
rec.thursday_hours, rec.friday_hours, rec.saturday_hours,
rec.sunday_hours, p_change_id, 'Y', rec.creation_date, rec.created_by,
rec.last_update_date, rec.last_update_by, rec.last_update_login,
rec.request_id, rec.program_application_id, rec.program_id,
rec.program_update_date);
PA_SCHEDULE_UTILS.log_message(1,'End of the update_history_table API ... ');
PA_SCHEDULE_UTILS.log_message(1,'ERROR in update_history_table API ..'||sqlerrm);
p_procedure_name => 'update_history_table');
END update_history_table;
PROCEDURE update_asgmt_changed_items_tab
( p_assignment_id IN NUMBER
,p_populate_mode IN VARCHAR2 := 'SAVED'
,p_change_id IN NUMBER
,p_exception_type_code IN VARCHAR2 := NULL
,p_start_date IN DATE := NULL
,p_end_date IN DATE := NULL
,p_requirement_status_code IN VARCHAR2 := NULL
,p_assignment_status_code IN VARCHAR2 := NULL
,p_start_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := NULL
,p_end_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := NULL
,p_monday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
,p_tuesday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
,p_wednesday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
,p_thursday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
,p_friday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
,p_saturday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
,p_sunday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
,p_non_working_day_flag IN VARCHAR2 := 'N'
,p_change_hours_type_code IN VARCHAR2 := NULL
,p_hrs_per_day IN NUMBER := NULL
,p_calendar_percent IN NUMBER := NULL
,p_change_calendar_type_code IN VARCHAR2 := NULL
,p_change_calendar_name IN VARCHAR2 := NULL
,p_change_calendar_id IN NUMBER := NULL
,p_duration_shift_type_code IN VARCHAR2 := NULL
,p_duration_shift_unit_code IN VARCHAR2 := NULL
,p_number_of_shift IN NUMBER := NULL
,x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
IS
l_changed_item_name pa_asgmt_changed_items.changed_item_name%TYPE;
l_insert_schedule_change BOOLEAN :=TRUE;
SELECT exception_type_code, start_date, end_date, calendar_id,
status_code, resource_calendar_percent, non_working_day_flag,
change_hours_type_code, nvl(monday_hours,0) monday_hours , nvl(tuesday_hours,0) tuesday_hours,
nvl(wednesday_hours,0) wednesday_hours, nvl(thursday_hours,0) thursday_hours, nvl(friday_hours,0) friday_hours, nvl(saturday_hours,0) saturday_hours,
nvl(sunday_hours,0) sunday_hours, change_calendar_type_code, change_calendar_id
FROM pa_schedule_except_history
WHERE assignment_id = p_assignment_id
AND change_id = p_change_id;
Select start_date, end_date
from pa_project_assignments
where assignment_id = p_assignment_id;
SELECT apprvl_status_code
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
SELECT prj.calendar_id
FROM pa_projects_all prj,
pa_project_assignments asmt
WHERE asmt.assignment_id = p_assignment_id
AND prj.project_id = asmt.project_id;
l_insert_schedule_change = FALSE) THEN
NULL;
IF (p_populate_mode = 'ASSIGNMENT_UPDATED' AND (p_exception_type_code='CHANGE_DURATION'
OR p_exception_type_code='SHIFT_DURATION' OR p_exception_type_code = 'DURATION_PATTERN_SHIFT') ) THEN
SELECT DECODE(p_start_date, null, temp_start_date, p_start_date),
DECODE(p_end_date, null, temp_end_date, p_end_date)
INTO l_start_date,
l_end_date
FROM DUAL;
l_insert_schedule_change := FALSE;
INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, date_range, old_value, new_value)
VALUES (p_assignment_id, l_changed_item_name, l_date_range, l_old_value, l_new_value);
IF (p_populate_mode = 'SCHEDULE_UPDATED') THEN
IF ((p_exception_type_code='CHANGE_DURATION' OR p_exception_type_code='SHIFT_DURATION' OR p_exception_type_code = 'DURATION_PATTERN_SHIFT') AND
l_insert_schedule_change = FALSE ) THEN
null;
INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, date_range, old_value, new_value)
VALUES (p_assignment_id, l_changed_item_name, l_date_range, l_old_value, l_new_value);
SELECT DECODE(p_start_date, null, temp_start_date, p_start_date),
DECODE(p_end_date, null, temp_end_date, p_end_date)
INTO l_start_date,
l_end_date
FROM DUAL;
SELECT calendar_id
INTO l_project_calendar_id
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
SELECT assignment_type
INTO l_assignment_type
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, date_range, old_value, new_value)
VALUES (p_assignment_id, l_changed_item_name, l_date_range, l_old_value, l_new_value);
END IF; -- IF (p_populate_mode = 'SCHEDULE_UPDATED')
p_procedure_name => 'update_asgmt_changed_items_tab');
END update_asgmt_changed_items_tab;
SELECT resource_id, start_date, end_date
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
select distinct fi.assignment_id
BULK COLLECT INTO l_assignment_id_tbl
from pa_forecast_items fi,
(select resource_id,
sum(item_quantity) total_assigned_quantity,
item_date,
delete_flag,
forecast_item_type
from pa_forecast_items fi1, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
where (fi1.assignment_id = p_assignment_id or asgmt_sys_status_code = 'STAFFED_ASGMT_CONF' )
and fi1.assignment_id = sch.assignment_id
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'
-- Added for bug 9039642
and fi1.delete_flag = 'N'
and fi1.forecast_item_type = 'A'
and fi1.resource_id = l_resource_id
and fi1.item_date between l_start_date AND l_end_date
group by resource_id, item_date, delete_flag, forecast_item_type
)fi_assigned,
(select resource_id,
capacity_quantity,
item_date,
delete_flag
from pa_forecast_items
where forecast_item_type = 'U'
)fi_capacity
where fi.assignment_id <> p_assignment_id
and fi.resource_id = l_resource_id
and fi.resource_id = fi_capacity.resource_id
and fi_capacity.resource_id = fi_assigned.resource_id
and fi_capacity.resource_id = l_resource_id -- Bug 4918687 SQL ID 14905966
and fi.item_date BETWEEN l_start_date AND l_end_date
and fi.item_date = fi_capacity.item_date
and fi_capacity.item_date = fi_assigned.item_date
and ((fi_capacity.capacity_quantity*(1+G_OVERCOMMITMENT_PERCENTAGE) - fi_assigned.total_assigned_quantity <= 0 and G_OVERCOMMITMENT_PERCENTAGE > 0)
or (fi_capacity.capacity_quantity - fi_assigned.total_assigned_quantity < 0 and G_OVERCOMMITMENT_PERCENTAGE = 0))
and fi.delete_flag = 'N'
and fi.delete_flag = fi_capacity.delete_flag
and fi_capacity.delete_flag = fi_assigned.delete_flag
and fi.forecast_item_type = 'A'
and fi.forecast_item_type = fi_assigned.forecast_item_type
and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF';
select distinct fi.assignment_id
BULK COLLECT INTO l_assignment_id_tbl
from pa_forecast_items fi,
(select resource_id,
sum(item_quantity) total_assigned_quantity,
GLOBAL_EXP_PERIOD_END_DATE week_end_date,
delete_flag,
forecast_item_type
from pa_forecast_items fi1, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
where (fi1.assignment_id = p_assignment_id or asgmt_sys_status_code = 'STAFFED_ASGMT_CONF' )
and fi1.item_date between l_start_date and l_end_date
and fi1.assignment_id = sch.assignment_id
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'
group by resource_id, GLOBAL_EXP_PERIOD_END_DATE, delete_flag, forecast_item_type
)fi_assigned,
(select resource_id,
sum(capacity_quantity) capacity_quantity,
GLOBAL_EXP_PERIOD_END_DATE week_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_capacity
where fi.assignment_id <> p_assignment_id
and fi.resource_id = l_resource_id
and fi.resource_id = fi_capacity.resource_id
and fi_capacity.resource_id = fi_assigned.resource_id
and fi.item_date between l_start_date and l_end_date
and fi.GLOBAL_EXP_PERIOD_END_DATE = fi_capacity.week_end_date
and fi_capacity.week_end_date = fi_assigned.week_end_date
and ((fi_capacity.capacity_quantity*(1+G_OVERCOMMITMENT_PERCENTAGE) - fi_assigned.total_assigned_quantity <= 0 and G_OVERCOMMITMENT_PERCENTAGE > 0)
or (fi_capacity.capacity_quantity - fi_assigned.total_assigned_quantity < 0 and G_OVERCOMMITMENT_PERCENTAGE = 0))
and fi.delete_flag = 'N'
and fi.delete_flag = fi_capacity.delete_flag
and fi_capacity.delete_flag = fi_assigned.delete_flag
and fi.forecast_item_type = 'A'
and fi.forecast_item_type = fi_assigned.forecast_item_type
and fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF';
PA_ASGN_CONFLICT_HIST_PKG.insert_rows(p_conflict_group_id => p_conflict_group_id,
p_assignment_id => p_assignment_id,
p_conflict_assignment_id_tbl => l_assignment_id_tbl,
p_resolve_conflict_action_code => p_resolve_conflict_action_code,
p_processed_flag => 'N',
x_conflict_group_id => x_conflict_group_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT assignment_id
FROM pa_mass_txn_asgmt_success_v
WHERE item_type = p_item_type
AND item_key = p_item_key;
SELECT resource_id, start_date, end_date
INTO l_resource_id, l_start_date, l_end_date
FROM pa_project_assignments
WHERE assignment_id = v_c1.assignment_id;
select distinct fi.assignment_id,
decode (mass.assignment_id, null, 'N', 'Y') intra_txn_conflict_flag
BULK COLLECT INTO l_assignment_id_tbl, l_intra_txn_conflict_flag_tbl
from pa_forecast_items fi, pa_mass_txn_asgmt_success_v mass, pa_schedules sch, pa_project_statuses a, pa_project_statuses b,
(select resource_id,
sum(item_quantity) total_assigned_quantity,
item_date,
delete_flag,
forecast_item_type
from
(select resource_id,
item_quantity,
item_date,
delete_flag,
forecast_item_type
from pa_forecast_items fi1, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
where (fi1.assignment_id in (select assignment_id from pa_mass_txn_asgmt_success_v where item_type = p_item_type and item_key = p_item_key) or asgmt_sys_status_code = 'STAFFED_ASGMT_CONF' )
and fi1.assignment_id = sch.assignment_id
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'
UNION ALL
select resource_id,
item_quantity,
item_date,
delete_flag,
forecast_item_type
from pa_forecast_items
where asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
and assignment_id not in (select assignment_id from pa_mass_txn_asgmt_success_v where item_type = p_item_type and item_key = p_item_key))
group by resource_id, item_date, delete_flag, forecast_item_type
)FI_ASSIGNED,
(select resource_id,
capacity_quantity,
item_date,
delete_flag
from pa_forecast_items
where forecast_item_type = 'U'
)fi_capacity
where fi.assignment_id <> v_c1.assignment_id
and fi.resource_id = l_resource_id
and fi.resource_id = fi_capacity.resource_id
and fi_capacity.resource_id = fi_assigned.resource_id
and fi.item_date BETWEEN l_start_date AND l_end_date
and fi.item_date = fi_capacity.item_date
and fi_capacity.item_date = fi_assigned.item_date
and ((fi_capacity.capacity_quantity*(1+G_OVERCOMMITMENT_PERCENTAGE) - fi_assigned.total_assigned_quantity <= 0 and G_OVERCOMMITMENT_PERCENTAGE > 0)
or (fi_capacity.capacity_quantity - fi_assigned.total_assigned_quantity < 0 and G_OVERCOMMITMENT_PERCENTAGE = 0))
and fi.delete_flag = 'N'
and fi.delete_flag = fi_capacity.delete_flag
and fi_capacity.delete_flag = fi_assigned.delete_flag
and fi.forecast_item_type = 'A'
and fi.forecast_item_type = fi_assigned.forecast_item_type
and fi.assignment_id = mass.assignment_id(+)
and mass.item_type(+) = p_item_type
and mass.item_key(+) = p_item_key
and (fi.assignment_id in (select assignment_id from pa_mass_txn_asgmt_success_v where item_type = p_item_type and item_key = p_item_key) or fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF')
and fi.assignment_id = sch.assignment_id
and fi.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';
select distinct fi.assignment_id,
decode (mass.assignment_id, null, 'N', 'Y') intra_txn_flag
BULK COLLECT INTO l_assignment_id_tbl, l_intra_txn_conflict_flag_tbl
from pa_forecast_items fi, pa_mass_txn_asgmt_success_v mass, pa_schedules sch, pa_project_statuses a, pa_project_statuses b,
(select resource_id,
sum(item_quantity) total_assigned_quantity,
GLOBAL_EXP_PERIOD_END_DATE,
delete_flag,
forecast_item_type
from
(select resource_id,
item_quantity,
GLOBAL_EXP_PERIOD_END_DATE,
delete_flag,
forecast_item_type
from pa_forecast_items fi1, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
where (fi1.assignment_id in (select assignment_id from pa_mass_txn_asgmt_success_v where item_type = p_item_type and item_key = p_item_key) or asgmt_sys_status_code = 'STAFFED_ASGMT_CONF' )
and fi1.item_date between l_start_date and l_end_date
and fi1.assignment_id = sch.assignment_id
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'
UNION ALL
select resource_id,
item_quantity,
GLOBAL_EXP_PERIOD_END_DATE,
delete_flag,
forecast_item_type
from pa_forecast_items
where asgmt_sys_status_code = 'STAFFED_ASGMT_CONF'
and item_date between l_start_date and l_end_date
and assignment_id not in (select assignment_id from pa_mass_txn_asgmt_success_v where item_type = p_item_type and item_key = p_item_key))
group by resource_id, GLOBAL_EXP_PERIOD_END_DATE, delete_flag, forecast_item_type
)FI_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_capacity
where fi.assignment_id <> v_c1.assignment_id
and fi.resource_id = l_resource_id
and fi.resource_id = fi_capacity.resource_id
and fi_capacity.resource_id = fi_assigned.resource_id
and fi.item_date BETWEEN l_start_date AND l_end_date
and fi.GLOBAL_EXP_PERIOD_END_DATE = fi_capacity.GLOBAL_EXP_PERIOD_END_DATE
and fi_capacity.GLOBAL_EXP_PERIOD_END_DATE = fi_assigned.GLOBAL_EXP_PERIOD_END_DATE
and ((fi_capacity.capacity_quantity*(1+G_OVERCOMMITMENT_PERCENTAGE) - fi_assigned.total_assigned_quantity <= 0 and G_OVERCOMMITMENT_PERCENTAGE > 0)
or (fi_capacity.capacity_quantity - fi_assigned.total_assigned_quantity < 0 and G_OVERCOMMITMENT_PERCENTAGE = 0))
and fi.delete_flag = 'N'
and fi.delete_flag = fi_capacity.delete_flag
and fi_capacity.delete_flag = fi_assigned.delete_flag
and fi.forecast_item_type = 'A'
and fi.forecast_item_type = fi_assigned.forecast_item_type
and fi.assignment_id = mass.assignment_id(+)
and mass.item_type(+) = p_item_type
and mass.item_key(+) = p_item_key
and (fi.assignment_id in (select assignment_id from pa_mass_txn_asgmt_success_v where item_type = p_item_type and item_key = p_item_key) or fi.asgmt_sys_status_code = 'STAFFED_ASGMT_CONF')
and fi.assignment_id = sch.assignment_id
and fi.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';
select v_c1.assignment_id, 'N'
bulk collect into l_assignment_id_tbl, l_intra_txn_conflict_flag_tbl
from dual;
PA_SCHEDULE_UTILS.debug('Before insert_rows into conflict history');
PA_ASGN_CONFLICT_HIST_PKG.insert_rows(p_conflict_group_id => l_conflict_group_id,
p_assignment_id => v_c1.assignment_id,
p_conflict_assignment_id_tbl => l_assignment_id_tbl,
p_resolve_conflict_action_code => p_resolve_conflict_action_code,
p_intra_txn_conflict_flag_tbl => l_intra_txn_conflict_flag_tbl,
p_processed_flag => 'N',
--x_conflict_group_id => l_conflict_group_id, * commented for bug: 4537865
x_conflict_group_id => l_new_conflict_group_id, -- added for bug fix: 4537865
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
PA_SCHEDULE_UTILS.debug('After insert_rows into conflict history');
SELECT fi_assigned.item_quantity, fi_assigned.item_date from
(select
resource_id,
item_quantity,
item_date,
asgmt_sys_status_code,
delete_flag
from pa_forecast_items fi, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
where fi.assignment_id = p_assignment_id
and fi.assignment_id = sch.assignment_id
and fi.item_date between sch.start_date and sch.end_date
and forecast_item_type = 'A'
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'
)fi_assigned,
(select resource_id,
capacity_quantity capacity_quantity,
item_date,
delete_flag
from pa_forecast_items
where forecast_item_type = 'U'
)fi_capacity
where fi_assigned.resource_id = p_resource_id
and fi_assigned.resource_id = fi_capacity.resource_id
and fi_assigned.item_date between p_start_date and p_end_date
and fi_assigned.item_date = fi_capacity.item_date
and ((fi_capacity.capacity_quantity*(1+G_OVERCOMMITMENT_PERCENTAGE) - fi_assigned.item_quantity <= 0 and G_OVERCOMMITMENT_PERCENTAGE > 0)
or (fi_capacity.capacity_quantity - fi_assigned.item_quantity < 0 and G_OVERCOMMITMENT_PERCENTAGE = 0))
and fi_assigned.delete_flag = 'N'
and fi_assigned.delete_flag = fi_capacity.delete_flag;
SELECT fi_assigned.weekly_quantity, fi_assigned.week_end_date
from
(select resource_id,
sum(item_quantity) weekly_quantity,
GLOBAL_EXP_PERIOD_END_DATE week_end_date,
delete_flag
from pa_forecast_items fi, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
where fi.assignment_id = p_assignment_id
and fi.assignment_id = sch.assignment_id
and item_date between l_week_start_date and l_week_end_date
and item_date between sch.start_date and sch.end_date
and forecast_item_type = 'A'
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'
group by resource_id, GLOBAL_EXP_PERIOD_END_DATE, delete_flag
)fi_assigned,
(select resource_id,
sum(capacity_quantity) capacity_quantity,
GLOBAL_EXP_PERIOD_END_DATE week_end_date,
delete_flag
from pa_forecast_items
where forecast_item_type = 'U'
and item_date between l_week_start_date and l_week_end_date
group by resource_id, GLOBAL_EXP_PERIOD_END_DATE, delete_flag
)fi_capacity
where fi_assigned.resource_id = p_resource_id
and fi_assigned.resource_id = fi_capacity.resource_id
and fi_assigned.week_end_date = fi_capacity.week_end_date
and ((fi_capacity.capacity_quantity*(1+G_OVERCOMMITMENT_PERCENTAGE) - fi_assigned.weekly_quantity <= 0 and G_OVERCOMMITMENT_PERCENTAGE > 0)
or (fi_capacity.capacity_quantity - fi_assigned.weekly_quantity < 0 and G_OVERCOMMITMENT_PERCENTAGE = 0))
and fi_assigned.delete_flag = 'N'
and fi_assigned.delete_flag = fi_capacity.delete_flag;
SELECT DISTINCT resolve_conflicts_action_code
FROM pa_assignment_conflict_hist
WHERE conflict_group_id = p_conflict_group_id
AND assignment_id = p_assignment_id
AND processed_flag = 'N';
SELECT conflict_assignment_id,
decode(asgn.MASS_WF_IN_PROGRESS_FLAG,
'Y', decode(hist.intra_txn_conflict_flag, 'N', 'Y', 'N'),
decode(asgn.apprvl_status_code, 'ASGMT_APPRVL_SUBMITTED', 'Y', 'N')) locking_flag
FROM pa_assignment_conflict_hist hist, pa_project_assignments asgn
WHERE hist.conflict_assignment_id = asgn.assignment_id
AND hist.conflict_group_id = p_conflict_group_id
AND hist.assignment_id = p_assignment_id
AND hist.processed_flag = 'N'
AND hist.resolve_conflicts_action_code = 'REMOVE_CONFLICTS'
AND hist.self_conflict_flag = 'N';
PA_ASGN_CONFLICT_HIST_PKG.update_rows(p_conflict_group_id => p_conflict_group_id,
p_assignment_id => p_assignment_id,
p_processed_flag => 'Y',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT resource_id, start_date, end_date
INTO l_resource_id, l_asn_start_date, l_asn_end_date
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
SELECT DISTINCT conf.conflict_assignment_id,
fi.item_date,
fi.GLOBAL_EXP_PERIOD_END_DATE,
fi.item_quantity,
fi_overcom.overcommitment_quantity
BULK COLLECT INTO l_assignment_id_tbl, l_item_date_tbl, l_week_end_date_tbl, l_item_quantity_tbl, l_overcom_quantity_tbl
FROM pa_forecast_items fi,
pa_assignment_conflict_hist conf,
(SELECT
resource_id,
item_date,
DECODE(sign(capacity_quantity*G_OVERCOMMITMENT_PERCENTAGE-overcommitment_quantity), 1, 0, overcommitment_quantity) overcommitment_quantity,
delete_flag
FROM pa_forecast_items
WHERE forecast_item_type = 'U'
) fi_overcom
WHERE fi.resource_id = l_resource_id
AND fi.resource_id = fi_overcom.resource_id
AND fi.item_date between l_conflict_start_date AND l_conflict_end_date
AND fi.item_date = fi_overcom.item_date
AND fi.delete_flag = 'N'
AND fi.delete_flag = fi_overcom.delete_flag
AND fi.forecast_item_type = 'A'
AND fi.assignment_id = conf.conflict_assignment_id
AND conf.assignment_id = p_assignment_id
AND conf.conflict_group_id = p_conflict_group_id
AND conf.resolve_conflicts_action_code = 'REMOVE_CONFLICTS'
AND conf.self_conflict_flag = 'N'
AND fi_overcom.overcommitment_quantity > 0
ORDER BY fi.item_date, fi.item_quantity asc;
SELECT DISTINCT fi.assignment_id,
fi.item_date,
fi.GLOBAL_EXP_PERIOD_END_DATE,
fi.item_quantity,
fi_overcom.overcommitment_quantity
BULK COLLECT INTO l_assignment_id_tbl, l_item_date_tbl, l_week_end_date_tbl, l_item_quantity_tbl, l_overcom_quantity_tbl
FROM pa_forecast_items fi, pa_assignment_conflict_hist conf,
(SELECT
resource_id,
overcommitment_quantity,
item_date,
delete_flag
from pa_forecast_items
where forecast_item_type = 'U'
)fi_overcom,
(SELECT
resource_id,
GLOBAL_EXP_PERIOD_END_DATE week_end_date,
decode(sign(sum(capacity_quantity)*G_OVERCOMMITMENT_PERCENTAGE-sum(overcommitment_quantity)), 1, 0, sum(overcommitment_quantity)) overcommitment_quantity,
delete_flag
FROM pa_forecast_items
WHERE forecast_item_type = 'U'
AND item_date BETWEEN l_conflict_start_date AND l_conflict_end_date
GROUP BY resource_id, GLOBAL_EXP_PERIOD_END_DATE, delete_flag
) fi_week
WHERE fi.resource_id = l_resource_id
AND fi.resource_id = fi_overcom.resource_id
AND fi_overcom.resource_id = fi_week.resource_id
AND fi.item_date BETWEEN l_conflict_start_date AND l_conflict_end_date
AND fi.item_date = fi_overcom.item_date
AND fi.GLOBAL_EXP_PERIOD_END_DATE = fi_week.week_end_date
AND fi.delete_flag = 'N'
AND fi.delete_flag = fi_overcom.delete_flag
AND fi_overcom.delete_flag = fi_week.delete_flag
AND fi.forecast_item_type = 'A'
AND fi.assignment_id = conf.conflict_assignment_id
AND conf.assignment_id = p_assignment_id
AND conf.conflict_group_id = p_conflict_group_id
AND conf.resolve_conflicts_action_code = 'REMOVE_CONFLICTS'
AND conf.self_conflict_flag = 'N'
AND fi_week.overcommitment_quantity > 0
ORDER BY fi.item_date, fi.item_quantity asc;
insert_work_pattern_tab(p_cur_work_pattern_tbl => l_cur_work_pattern_tbl,
x_work_pattern_tbl => l_work_pattern_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
l_cur_work_pattern_tbl.DELETE;
update_work_pattern_record(p_overcom_quantity => l_cur_overcom_quantity,
p_count => l_count,
p_item_date => l_cur_item_date,
x_work_pattern_tbl => l_cur_work_pattern_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
insert_work_pattern_record(p_assignment_id => l_assignment_id_tbl(j),
p_item_quantity => l_item_quantity_tbl(j),
p_item_date => l_item_date_tbl(j),
p_week_end_date => l_week_end_date_tbl(j),
x_work_pattern_tbl => l_cur_work_pattern_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
insert_work_pattern_record(p_assignment_id => l_assignment_id_tbl(j),
p_item_quantity => l_item_quantity_tbl(j),
p_item_date => l_item_date_tbl(j),
p_week_end_date => l_week_end_date_tbl(j),
x_work_pattern_tbl => l_cur_work_pattern_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
update_work_pattern_record(p_overcom_quantity => l_cur_overcom_quantity,
p_count => l_count,
p_item_date => l_cur_item_date,
x_work_pattern_tbl => l_cur_work_pattern_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
insert_work_pattern_tab(p_cur_work_pattern_tbl => l_cur_work_pattern_tbl,
x_work_pattern_tbl => l_work_pattern_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
l_cur_work_pattern_tbl.DELETE;
SELECT null, project_id, calendar_id, assignment_type,start_date, end_date
INTO l_record_version_number, l_project_id, l_calendar_id, l_assignment_type, l_asgn_start_date, l_asgn_end_date
FROM pa_project_assignments
WHERE assignment_id = v_c3.conflict_assignment_id;
PA_ASGN_CONFLICT_HIST_PKG.update_rows(p_conflict_group_id => p_conflict_group_id,
p_assignment_id => p_assignment_id,
p_processed_flag => 'Y',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
PROCEDURE insert_work_pattern_record( p_assignment_id IN NUMBER,
p_item_quantity IN NUMBER,
p_item_date IN DATE,
p_week_end_date IN DATE,
x_work_pattern_tbl IN OUT NOCOPY WorkPatternTabTyp,
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_count NUMBER :=0;
PA_SCHEDULE_UTILS.debug('After insert_work_pattern_record: work_pattern = '||
x_work_pattern_tbl(l_count).monday_hours||';'||
p_procedure_name => 'insert_work_pattern_record');
END insert_work_pattern_record;
PROCEDURE update_work_pattern_record(p_overcom_quantity IN NUMBER,
p_count IN NUMBER,
p_item_date IN DATE,
x_work_pattern_tbl IN OUT NOCOPY WorkPatternTabTyp,
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_week_day VARCHAR2(15);
PA_SCHEDULE_UTILS.debug('After update_work_pattern_record: work_pattern = '||
x_work_pattern_tbl(j).monday_hours||';'||
p_procedure_name => 'insert_work_pattern_record');
END update_work_pattern_record;
PROCEDURE insert_work_pattern_tab(p_cur_work_pattern_tbl IN WorkPatternTabTyp,
x_work_pattern_tbl IN OUT NOCOPY WorkPatternTabTyp,
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_cur NUMBER;
PA_SCHEDULE_UTILS.debug('Inside insert table: x_work_pattern_tbl(i).end_date = '|| x_work_pattern_tbl(i).end_date);
p_procedure_name => 'insert_work_pattern_tab');
END insert_work_pattern_tab;
SELECT DISTINCT asgn.project_id, proj.name, proj.segment1, proj.person_name, proj.carrying_out_organization_name, proj.customer_name
FROM pa_project_assignments asgn, pa_assignment_conflict_hist hist, pa_project_lists_v proj
WHERE asgn.assignment_id = hist.assignment_id
AND hist.conflict_group_id = p_conflict_group_id
AND asgn.project_id = proj.project_id;
SELECT asgn.project_id, proj.name, proj.segment1, proj.carrying_out_organization_id
FROM pa_project_assignments asgn, pa_assignment_conflict_hist hist, pa_projects_all proj
WHERE asgn.assignment_id = hist.assignment_id
AND hist.conflict_group_id = p_conflict_group_id
AND asgn.project_id = proj.project_id
AND ROWNUM = 1;
SELECT DISTINCT asgn.project_id, proj.name, proj.segment1
FROM pa_project_assignments asgn, pa_assignment_conflict_hist hist, pa_projects_all proj
WHERE asgn.assignment_id = hist.conflict_assignment_id
AND hist.conflict_group_id = p_conflict_group_id
AND asgn.project_id = proj.project_id
AND hist.self_conflict_flag = 'N';
SELECT assignment_id
FROM pa_assignment_conflict_hist
WHERE conflict_group_id = p_conflict_group_id
AND self_conflict_flag = 'Y'
AND resolve_conflicts_action_code = 'REMOVE_CONFLICTS';
SELECT organ.name
FROM
hr_all_organization_units_tl organ
WHERE
organ.organization_id = c_organization_id AND
organ.LANGUAGE = USERENV('LANG');
SELECT PA_PRM_WF_ITEM_KEY_S.nextval
INTO l_item_key
FROM DUAL;
PA_WORKFLOW_UTILS.Insert_WF_Processes
(p_wf_type_code => 'OVERCOMMITMENT'
,p_item_type => l_item_type
,p_item_key => l_item_key
,p_entity_key1 => to_char(l_source_proj_id)
,p_entity_key2 => to_char(l_conflict_proj_id)
,p_description => NULL
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack
);
PA_WORKFLOW_UTILS.Insert_WF_Processes
(p_wf_type_code => 'OVERCOMMITMENT'
,p_item_type => l_item_type
,p_item_key => l_item_key
,p_entity_key1 => to_char(l_source_proj_id)
,p_entity_key2 => to_char(l_conflict_proj_id)
,p_description => NULL
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack
);
SELECT PA_PRM_WF_ITEM_KEY_S.nextval
INTO l_item_key
FROM DUAL;
PA_WORKFLOW_UTILS.Insert_WF_Processes
(p_wf_type_code => 'OVERCOMMITMENT'
,p_item_type => l_item_type
,p_item_key => l_item_key
,p_entity_key1 => to_char(l_source_proj_id)
,p_entity_key2 => 'SELF_OVERCOMMITMENT'
,p_description => NULL
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack
);
SELECT assignment_id
FROM pa_assignment_conflict_hist
WHERE conflict_group_id = p_conflict_group_id
AND resolve_conflicts_action_code = 'REMOVE_CONFLICTS'
AND self_conflict_flag = 'N'
AND processed_flag = 'N';
SELECT processed_flag
FROM pa_assignment_conflict_hist
WHERE conflict_group_id = p_conflict_group_id
AND assignment_id = p_assignment_id
AND resolve_conflicts_action_code = 'REMOVE_CONFLICTS'
AND self_conflict_flag = 'N'
AND processed_flag = 'Y';
SELECT DISTINCT conf.assignment_id, null record_version_number, asgn.assignment_type, asgn.start_date, asgn.end_date
FROM pa_assignment_conflict_hist conf, pa_project_assignments asgn
WHERE conf.conflict_group_id = p_conflict_group_id
AND conf.assignment_id = asgn.assignment_id
AND conf.resolve_conflicts_action_code = 'CANCEL_TXN_ITEM'
AND conf.processed_flag = 'N';
PA_ASGN_CONFLICT_HIST_PKG.update_rows(p_conflict_group_id => p_conflict_group_id,
p_assignment_id => v_c1.assignment_id,
p_processed_flag => 'Y',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT DISTINCT assignment_id
FROM pa_assignment_conflict_hist
WHERE conflict_group_id = p_conflict_group_id
AND resolve_conflicts_action_code = 'REVERT_TXN_ITEM'
AND processed_flag = 'N';
PA_ASGN_CONFLICT_HIST_PKG.update_rows(p_conflict_group_id => p_conflict_group_id,
p_assignment_id => v_c1.assignment_id,
p_processed_flag => 'Y',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT COUNT(DISTINCT assignment_id)
INTO x_assignment_count
FROM pa_assignment_conflict_hist
WHERE conflict_group_id = p_conflict_group_id;
SELECT assignment_id
FROM pa_assignment_conflict_hist
WHERE conflict_group_id = p_conflict_group_id
AND resolve_conflicts_action_code = 'NOTIFY_IF_CONFLICT';
SELECT hist.assignment_id
FROM pa_assignment_conflict_hist hist, pa_project_assignments asgn
WHERE hist.assignment_id = asgn.assignment_id
AND hist.conflict_group_id = p_conflict_group_id
AND asgn.apprvl_status_code = 'ASGMT_APPRVL_WORKING';
SELECT a.start_date, a.end_date, NVL(a.quantity,0),
c.planning_start_date, c.planning_end_date -- 4367912
FROM pa_budget_lines a,
-- pa_projects_all b, -- Bug 5086869
pa_resource_assignments c
WHERE a.resource_assignment_id = c.resource_assignment_id
AND c.resource_assignment_id = l_task_assignment_id;
-- Loop through l_summation_tbl to update hours column
FOR summation_counter IN l_summation_index_first .. l_summation_index_last LOOP
IF l_summation_tbl(summation_counter).schedule_date BETWEEN l_period_start_date AND l_period_end_date THEN
IF l_summation_tbl(summation_counter).working_day = 1 THEN
l_summation_tbl(summation_counter).hours := l_summation_tbl(summation_counter).hours + l_hours_per_day;
SELECT calendar_id, calendar_type, start_date, end_date
FROM pa_assignments_history
WHERE assignment_id = p_assignment_id
AND last_approved_flag = 'Y';
SELECT calendar_id, calendar_type, start_date, end_date
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
SELECT to_char(trunc(monday_hours,2)) mon_hours,
to_char(trunc(tuesday_hours,2)) tue_hours,
to_char(trunc(wednesday_hours,2)) wed_hours,
to_char(trunc(thursday_hours,2)) thu_hours,
to_char(trunc(friday_hours,2)) fri_hours,
to_char(trunc(saturday_hours,2)) sat_hours,
to_char(trunc(sunday_hours,2)) sun_hours,
status_code,
start_date,
end_date
FROM pa_schedules_history
WHERE assignment_id = p_assignment_id
AND last_approved_flag = 'Y'
AND ( (start_date <= p_start_date AND end_date >= p_end_date)
OR (start_date >= p_start_date AND end_date <= p_end_date)
OR (start_date <= p_start_date AND p_start_date <= end_date)
OR (start_date <= p_end_date AND p_end_date <= end_date));
SELECT to_char(trunc(monday_hours,2)) mon_hours,
to_char(trunc(tuesday_hours,2)) tue_hours,
to_char(trunc(wednesday_hours,2)) wed_hours,
to_char(trunc(thursday_hours,2)) thu_hours,
to_char(trunc(friday_hours,2)) fri_hours,
to_char(trunc(saturday_hours,2)) sat_hours,
to_char(trunc(sunday_hours,2)) sun_hours,
status_code,
start_date,
end_date
FROM pa_schedules
WHERE assignment_id = p_assignment_id
AND ( (start_date <= p_start_date AND end_date >= p_end_date)
OR (start_date >= p_start_date AND end_date <= p_end_date)
OR (start_date <= p_start_date AND p_start_date <= end_date)
OR (start_date <= p_end_date AND p_end_date <= end_date));
SELECT apprvl_status_code
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
SELECT project_status_name
INTO l_old_value_text
FROM pa_project_statuses
WHERE project_status_code = v_c2.status_code;
SELECT meaning
INTO l_old_value_text
FROM pa_lookups
WHERE lookup_type = 'PA_SCH_UPDATE_TOP'
AND lookup_code = 'PA_MULTIPLE';
SELECT project_status_name
INTO l_old_value_text
FROM pa_project_statuses
WHERE project_status_code = v_c2_current.status_code;
SELECT meaning
INTO l_old_value_text
FROM pa_lookups
WHERE lookup_type = 'PA_SCH_UPDATE_TOP'
AND lookup_code = 'PA_MULTIPLE';
SELECT meaning
INTO l_old_value_text
FROM pa_lookups
WHERE lookup_type = 'PA_SCH_UPDATE_TOP'
AND lookup_code = 'PA_MULTIPLE';
SELECT meaning
INTO l_old_value_text
FROM pa_lookups
WHERE lookup_type = 'PA_SCH_UPDATE_TOP'
AND lookup_code = 'PA_MULTIPLE';
SELECT meaning
INTO l_old_value_text
FROM pa_lookups
WHERE lookup_type = 'PA_SCH_UPDATE_TOP'
AND lookup_code = 'PA_MULTIPLE';
SELECT meaning INTO l_old_value_text
FROM pa_lookups
WHERE lookup_type = 'PA_SCH_UPDATE_TOP'
AND lookup_code = 'PA_MULTIPLE';
SELECT project_status_name
INTO l_new_value_text
FROM pa_project_statuses
WHERE project_status_code = p_new_status_code;
l_non_working_day_flag := get_ak_attribute_label('PA_SCH_UPDATE_TOP','PA_INCLUDE_NON_WORKING');
l_new_calendar_name := get_ak_attribute_label('PA_SCH_UPDATE_TOP','PA_RESOURCE_CALENDAR');
SELECT calendar_name
INTO l_new_calendar_name
FROM jtf_calendars_vl
WHERE calendar_id = p_new_calendar_id;
SELECT calendar_name
INTO l_new_calendar_name
FROM jtf_calendars_vl
WHERE calendar_id = p_new_change_calendar_id;
SELECT start_date, end_date
INTO l_start_date, l_end_date
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
SELECT COUNT(*)
INTO l_count
FROM (
select DISTINCT fi.item_date
from pa_forecast_items fi,
(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,
(select capacity_quantity,
item_date,
delete_flag,
resource_id
from pa_forecast_items
where forecast_item_type = 'U'
)fi_capacity
where fi.resource_id = p_resource_id
and fi.resource_id = fi_capacity.resource_id
and fi_capacity.resource_id = fi_assigned.resource_id
and fi.assignment_id = p_assignment_id
and fi.item_date BETWEEN l_start_date and l_end_date
and fi.item_date = fi_capacity.item_date
and fi_capacity.item_date = fi_assigned.item_date
and ((fi_capacity.capacity_quantity*(1+G_OVERCOMMITMENT_PERCENTAGE) - fi_assigned.assigned_quantity <= 0 and G_OVERCOMMITMENT_PERCENTAGE > 0)
or (fi_capacity.capacity_quantity - fi_assigned.assigned_quantity < 0 and G_OVERCOMMITMENT_PERCENTAGE = 0))
and fi.delete_flag = 'N'
and fi.delete_flag = fi_capacity.delete_flag
and fi_capacity.delete_flag = fi_assigned.delete_flag
and fi.forecast_item_type = 'A'
);
SELECT COUNT(*)
INTO l_count
FROM (
select DISTINCT
fi.item_date
from pa_forecast_items fi,
(select
resource_id,
sum(item_quantity) total_assigned_quantity,
GLOBAL_EXP_PERIOD_END_DATE week_end_date,
delete_flag,
forecast_item_type
from pa_forecast_items fi1, 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 conflict_group_id = p_conflict_group_id
and assignment_id = p_assignment_id
and self_conflict_flag = 'N'))
and fi1.assignment_id = sch.assignment_id
and item_date BETWEEN l_start_date AND l_end_date
and 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'
group by resource_id, GLOBAL_EXP_PERIOD_END_DATE, forecast_item_type, delete_flag
)fi_assigned,
(select resource_id,
sum(capacity_quantity) capacity_quantity,
GLOBAL_EXP_PERIOD_END_DATE week_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_capacity
where fi.resource_id = p_resource_id
and fi.resource_id = fi_capacity.resource_id
and fi_capacity.resource_id = fi_assigned.resource_id
and fi.item_date BETWEEN l_start_date AND l_end_date
and fi.GLOBAL_EXP_PERIOD_END_DATE = fi_capacity.week_end_date
and fi_capacity.week_end_date = fi_assigned.week_end_date
and ((fi_capacity.capacity_quantity*(1+G_OVERCOMMITMENT_PERCENTAGE) - fi_assigned.total_assigned_quantity <= 0 and G_OVERCOMMITMENT_PERCENTAGE > 0)
or (fi_capacity.capacity_quantity - fi_assigned.total_assigned_quantity < 0 and G_OVERCOMMITMENT_PERCENTAGE = 0))
and fi.delete_flag = 'N'
and fi.delete_flag = fi_capacity.delete_flag
and fi_capacity.delete_flag = fi_assigned.delete_flag
and fi.forecast_item_type = 'A'
and fi.forecast_item_type = fi_assigned.forecast_item_type
and fi.assignment_id = p_assignment_id
);
SELECT conflict_group_id, assignment_id
FROM pa_assignment_conflict_hist
WHERE conflict_group_id = p_conflict_group_id
AND assignment_id = p_assignment_id;
SELECT meaning
INTO l_result
FROM pa_lookups
WHERE lookup_type = 'CONFLICT_EXISTS'
AND lookup_code = 'NO';
SELECT meaning
INTO l_result
FROM pa_lookups
WHERE lookup_type = 'CONFLICT_EXISTS'
AND lookup_code = 'YES';
SELECT resolve_conflicts_action_code
FROM pa_assignment_conflict_hist
WHERE conflict_group_id = p_conflict_group_id
AND assignment_id = p_assignment_id;
SELECT meaning
INTO l_result
FROM pa_lookups
WHERE lookup_type = 'RESOLVE_CONFLICTS_ACTION_CODE'
AND lookup_code = v_c1.resolve_conflicts_action_code;
SELECT DISTINCT asgn.project_id
FROM pa_project_assignments asgn, pa_assignment_conflict_hist hist
WHERE asgn.assignment_id = hist.conflict_assignment_id
AND hist.conflict_group_id = p_conflict_group_id
AND hist.assignment_id = p_assignment_id;
SELECT self_conflict_flag
FROM pa_assignment_conflict_hist
WHERE conflict_group_id = p_conflict_group_id
AND assignment_id = p_assignment_id
AND self_conflict_flag = 'Y'
AND resolve_conflicts_action_code = 'REMOVE_CONFLICTS';
SELECT meaning
INTO l_attribute_label
FROM pa_lookups
WHERE lookup_type = p_region_code
AND lookup_code = p_attribute_code;