[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_schedule
( p_project_id IN NUMBER
,p_mass_update_flag IN VARCHAR2 := FND_API.G_FALSE
,p_exception_type_code IN VARCHAR2
,p_record_version_number IN NUMBER := NULL
,p_assignment_id IN NUMBER := NULL
,p_assignment_id_array IN SYSTEM.PA_NUM_TBL_TYPE := NULL
,p_change_start_date IN DATE := NULL
,p_change_end_date IN DATE := NULL
,p_requirement_status_code IN VARCHAR2 := NULL
,p_assignment_status_code IN VARCHAR2 := NULL
,p_monday_hours IN NUMBER := NULL
,p_tuesday_hours IN NUMBER := NULL
,p_wednesday_hours IN NUMBER := NULL
,p_thursday_hours IN NUMBER := NULL
,p_friday_hours IN NUMBER := NULL
,p_saturday_hours IN NUMBER := NULL
,p_sunday_hours IN NUMBER := 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
,p_last_row_flag IN VARCHAR2 := 'Y'
,p_change_start_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := NULL
,p_change_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_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
,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_err_msg_code fnd_new_messages.message_name%TYPE;
l_updated_calendar_id NUMBER;
PA_DEBUG.init_err_stack('PA_SCHEDULE_PUB.update_schedule');
SAVEPOINT SCH_PUB_UPDATE_SCH;
p_mass_update_flag = FND_API.G_TRUE) ) THEN
PA_UTILS.Add_Message ('PA', 'PA_SCH_ASGN_STATUS_NULL');
IF (p_mass_update_flag = FND_API.G_TRUE AND p_last_row_flag = 'Y') THEN
--start the mass WF
PA_MASS_ASGMT_TRX.Start_Mass_Asgmt_Trx_Wf(
p_mode => PA_MASS_ASGMT_TRX.G_MASS_UPDATE_SCHEDULE
,p_action => PA_MASS_ASGMT_TRX.G_SAVE
,p_project_id => p_project_id
,p_exception_type_code => p_exception_type_code
,p_assignment_id_tbl => p_assignment_id_array
,p_change_start_date => p_change_start_date
,p_change_end_date => p_change_end_date
,p_change_rqmt_status_code => p_requirement_status_code
,p_change_asgmt_status_code => p_assignment_status_code
,p_change_start_date_tbl => p_change_start_date_tbl
,p_change_end_date_tbl => p_change_end_date_tbl
,p_monday_hours_tbl => p_monday_hours_tbl
,p_tuesday_hours_tbl => p_tuesday_hours_tbl
,p_wednesday_hours_tbl => p_wednesday_hours_tbl
,p_thursday_hours_tbl => p_thursday_hours_tbl
,p_friday_hours_tbl => p_friday_hours_tbl
,p_saturday_hours_tbl => p_saturday_hours_tbl
,p_sunday_hours_tbl => p_sunday_hours_tbl
,p_non_working_day_flag => p_non_working_day_flag
,p_change_hours_type_code => p_change_hours_type_code
,p_hrs_per_day => p_hrs_per_day
,p_calendar_percent => p_calendar_percent
,p_change_calendar_type_code => p_change_calendar_type_code
,p_change_calendar_name => p_change_calendar_name
,p_change_calendar_id => p_change_calendar_id
,p_duration_shift_type_code => p_duration_shift_type_code
,p_duration_shift_unit_code => p_duration_shift_unit_code
,p_num_of_shift => p_number_of_shift
,x_return_status => l_x_return_status );
ELSIF (p_mass_update_flag = FND_API.G_FALSE) THEN
-- call execute_update_schedule procedure for single schedule update.
pa_schedule_utils.debug('pa.plsql.pa_schedule_pub', 'Calling single_update_schedule', 6);
single_update_schedule (
p_project_id => p_project_id
,p_exception_type_code => p_exception_type_code
,p_record_version_number => p_record_version_number
,p_assignment_id => p_assignment_id
,p_change_start_date => p_change_start_date
,p_change_end_date => p_change_end_date
,p_assignment_status_code => p_assignment_status_code
,p_monday_hours => p_monday_hours
,p_tuesday_hours => p_tuesday_hours
,p_wednesday_hours => p_wednesday_hours
,p_thursday_hours => p_thursday_hours
,p_friday_hours => p_friday_hours
,p_saturday_hours => p_saturday_hours
,p_sunday_hours => p_sunday_hours
,p_non_working_day_flag => p_non_working_day_flag
,p_change_hours_type_code => p_change_hours_type_code
,p_hrs_per_day => p_hrs_per_day
,p_calendar_percent => p_calendar_percent
,p_change_calendar_type_code => p_change_calendar_type_code
--,p_change_calendar_name => p_change_calendar_name
,p_change_calendar_id => l_change_calendar_id
,p_duration_shift_type_code => p_duration_shift_type_code
,p_duration_shift_unit_code => p_duration_shift_unit_code
,p_number_of_shift => p_number_of_shift
,p_last_row_flag => p_last_row_flag
,p_init_msg_list => FND_API.G_TRUE
,p_commit => FND_API.G_FALSE
,x_return_status => l_x_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
ROLLBACK TO SCH_PUB_UPDATE_SCH;
,p_procedure_name => 'execute_update_schedule'
,p_error_text => x_msg_data ); -- 4537865
END update_schedule;
PROCEDURE single_update_schedule
( p_project_id IN NUMBER
,p_exception_type_code IN VARCHAR2
,p_record_version_number IN NUMBER := NULL
,p_assignment_id IN NUMBER := NULL
,p_change_start_date IN DATE := NULL
,p_change_end_date IN DATE := NULL
,p_assignment_status_code IN VARCHAR2 := NULL
,p_monday_hours IN NUMBER := NULL
,p_tuesday_hours IN NUMBER := NULL
,p_wednesday_hours IN NUMBER := NULL
,p_thursday_hours IN NUMBER := NULL
,p_friday_hours IN NUMBER := NULL
,p_saturday_hours IN NUMBER := NULL
,p_sunday_hours IN NUMBER := 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
,p_last_row_flag IN VARCHAR2 := 'Y'
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,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_err_msg_code fnd_new_messages.message_name%TYPE;
l_updated_calendar_id NUMBER;
SELECT assignment_type,
status_code,
calendar_id,
start_date,
end_date
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
PA_DEBUG.init_err_stack('PA_SCHEDULE_PUB.single_update_schedule');
SAVEPOINT SCH_PUB_SINGLE_UPDATE_SCH;
ROLLBACK TO SCH_PUB_SINGLE_UPDATE_SCH;
,p_procedure_name => 'single_update_schedule',
p_error_text => x_msg_data ); -- 4537865
END single_update_schedule;
PROCEDURE mass_update_schedule
( p_project_id IN NUMBER
,p_exception_type_code IN VARCHAR2
,p_assignment_id_array IN SYSTEM.PA_NUM_TBL_TYPE
,p_change_start_date IN DATE := NULL
,p_change_end_date IN DATE := NULL
,p_change_rqmt_status_code IN VARCHAR2 := NULL
,p_change_asgmt_status_code IN VARCHAR2 := NULL
,p_change_start_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := NULL
,p_change_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
,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,x_success_assignment_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE /* Added NOCOPY for bug#2674619 */
,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_err_msg_code fnd_new_messages.message_name%TYPE;
l_updated_calendar_id NUMBER;
SELECT assignment_type,
status_code,
resource_id
FROM pa_project_assignments
WHERE assignment_id = l_assignment_id;
PA_DEBUG.init_err_stack('PA_SCHEDULE_PUB.mass_update_schedule');
SAVEPOINT SCH_PUB_MASS_UPDATE_SCH;
single_update_schedule (
p_project_id => p_project_id
,p_exception_type_code => p_exception_type_code
,p_record_version_number => NULL
,p_assignment_id => p_assignment_id_array(i)
,p_change_start_date => p_change_start_date_tbl(j)
,p_change_end_date => p_change_end_date_tbl(j)
,p_assignment_status_code => l_change_asgmt_status_code
,p_monday_hours => p_monday_hours_tbl(j)
,p_tuesday_hours => p_tuesday_hours_tbl(j)
,p_wednesday_hours => p_wednesday_hours_tbl(j)
,p_thursday_hours => p_thursday_hours_tbl(j)
,p_friday_hours => p_friday_hours_tbl(j)
,p_saturday_hours => p_saturday_hours_tbl(j)
,p_sunday_hours => p_sunday_hours_tbl(j)
,p_non_working_day_flag => p_non_working_day_flag
,p_change_hours_type_code => p_change_hours_type_code
,p_hrs_per_day => p_hrs_per_day
,p_calendar_percent => p_calendar_percent
,p_change_calendar_type_code => p_change_calendar_type_code
--,p_change_calendar_name => p_change_calendar_name
,p_change_calendar_id => p_change_calendar_id
,p_duration_shift_type_code => p_duration_shift_type_code
,p_duration_shift_unit_code => p_duration_shift_unit_code
,p_number_of_shift => p_number_of_shift
,p_last_row_flag => l_last_row_flag
,p_init_msg_list => FND_API.G_TRUE
,p_commit => FND_API.G_FALSE
,x_return_status => l_x_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
single_update_schedule (
p_project_id => p_project_id
,p_exception_type_code => p_exception_type_code
,p_record_version_number => NULL
,p_assignment_id => p_assignment_id_array(i)
,p_change_start_date => p_change_start_date
,p_change_end_date => p_change_end_date
,p_assignment_status_code => l_change_asgmt_status_code
,p_monday_hours => NULL
,p_tuesday_hours => NULL
,p_wednesday_hours => NULL
,p_thursday_hours => NULL
,p_friday_hours => NULL
,p_saturday_hours => NULL
,p_sunday_hours => NULL
,p_non_working_day_flag => p_non_working_day_flag
,p_change_hours_type_code => p_change_hours_type_code
,p_hrs_per_day => p_hrs_per_day
,p_calendar_percent => p_calendar_percent
,p_change_calendar_type_code => p_change_calendar_type_code
--,p_change_calendar_name => p_change_calendar_name
,p_change_calendar_id => p_change_calendar_id
,p_duration_shift_type_code => p_duration_shift_type_code
,p_duration_shift_unit_code => p_duration_shift_unit_code
,p_number_of_shift => p_number_of_shift
,p_last_row_flag => l_last_row_flag
,p_init_msg_list => FND_API.G_TRUE
,p_commit => FND_API.G_FALSE
,x_return_status => l_x_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
END IF; -- end if for calling single_update_schedule
ROLLBACK TO SCH_PUB_MASS_UPDATE_SCH;
p_source_type2 => PA_MASS_ASGMT_TRX.G_MASS_UPDATE_SCHEDULE,
p_source_identifier1 => PA_MASS_ASGMT_TRX.G_WORKFLOW_ITEM_TYPE,
p_source_identifier2 => PA_MASS_ASGMT_TRX.G_WORKFLOW_ITEM_KEY,
p_context1 => p_project_id,
p_context2 => p_assignment_id_array(i),
p_context3 => NULL,
p_commit => FND_API.G_TRUE,
x_return_status => l_return_status);
p_source_type2 => PA_MASS_ASGMT_TRX.G_MASS_UPDATE_SCHEDULE,
p_source_identifier1 => PA_MASS_ASGMT_TRX.G_WORKFLOW_ITEM_TYPE,
p_source_identifier2 => PA_MASS_ASGMT_TRX.G_WORKFLOW_ITEM_KEY,
p_context1 => p_project_id,
p_context2 => p_assignment_id_array(i),
p_context3 => NULL,
p_commit => FND_API.G_TRUE,
x_return_status => l_return_status);
,p_procedure_name => 'mass_update_schedule'
,p_error_text => x_msg_data ); -- 4537865
END mass_update_schedule;
SELECT a.person_id, b.resource_id
FROM pa_resource_txn_attributes a, pa_project_assignments b
WHERE a.resource_id = b.resource_id
AND b.assignment_id = p_assignment_id;
SELECT pps.project_status_name
FROM pa_projects_all ppa, pa_project_statuses pps
WHERE ppa.project_id = p_project_id
AND ppa.project_status_code = pps.project_status_code;
SELECT project_status_name
INTO l_assignment_status_name
FROM pa_project_statuses
WHERE project_status_code = p_assignment_status_code;
,p_calling_mode => 'UPDATE'
,p_project_id => p_project_id
,p_person_id => l_resource_source_id
,x_error_message_code => l_error_message_code
,x_return_status => x_return_status);
-- Insert row to PA_SCHEDULE_EXCEPTIONS
--
PA_SCHEDULE_UTILS.log_message(1,'Start of the change_duration API ..... ');
PA_SCH_EXCEPT_PKG.Insert_Rows(
p_calendar_id => p_calendar_id
,p_assignment_id => p_assignment_id
,p_project_id => p_project_id
,p_schedule_type_code => p_assignment_type
,p_assignment_status_code => p_assignment_status_code
,p_exception_type_code => p_exception_type_code
,p_start_date => l_start_date
,p_end_date => l_end_date
,p_duration_shift_type_code => p_duration_shift_type_code
,p_duration_shift_unit_code => p_duration_shift_unit_code
,p_number_of_shift => p_number_of_shift
,x_exception_id => l_exception_id
,x_return_status => l_x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
PA_CANDIDATE_UTILS.Update_No_Of_Active_Candidates (
p_assignment_id => p_assignment_id,
x_return_status => l_x_return_status);
PA_SCH_EXCEPT_PKG.Insert_Rows(
p_calendar_id => p_calendar_id ,
p_assignment_id => p_assignment_id ,
p_project_id => p_project_id ,
p_schedule_type_code => p_assignment_type ,
p_assignment_status_code => p_assignment_status_code ,
p_exception_type_code => 'CHANGE_HOURS' ,
p_start_date => p_start_date ,
p_end_date => p_end_date ,
p_resource_calendar_percent=> p_calendar_percent ,
p_non_working_day_flag => p_non_working_day_flag ,
p_change_hours_type_code => p_change_hours_type_code ,
p_change_calendar_type_code => p_change_calendar_type_code ,
-- p_change_calendar_name => p_change_calendar_name ,
p_change_calendar_id => p_change_calendar_id ,
p_monday_hours => p_hrs_per_day ,
p_tuesday_hours => p_hrs_per_day ,
p_wednesday_hours => p_hrs_per_day ,
p_thursday_hours => p_hrs_per_day ,
p_friday_hours => p_hrs_per_day ,
p_saturday_hours => p_hrs_per_day ,
p_sunday_hours => p_hrs_per_day ,
x_exception_id => l_t_exception_id ,
x_return_status => l_x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data );
pa_schedule_utils.debug('pa.plsql.pa_schedule_pub', 'Calling pa_sch_except_pkg.insert_rows', 6);
PA_SCH_EXCEPT_PKG.Insert_Rows(
p_calendar_id => p_calendar_id ,
p_assignment_id => p_assignment_id ,
p_project_id => p_project_id ,
p_schedule_type_code => p_assignment_type ,
p_exception_type_code => 'CHANGE_WORK_PATTERN' ,
p_start_date => p_start_date ,
p_end_date => p_end_date ,
p_monday_hours => l_p_monday_hours ,
p_tuesday_hours => l_p_tuesday_hours ,
p_wednesday_hours => l_p_wednesday_hours ,
p_thursday_hours => l_p_thursday_hours ,
p_friday_hours => l_p_friday_hours ,
p_saturday_hours => l_p_saturday_hours ,
p_sunday_hours => l_p_sunday_hours ,
x_exception_id => l_t_exception_id ,
x_return_status => l_x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data );
SELECT pps.project_status_name
FROM pa_projects_all ppa, pa_project_statuses pps
WHERE ppa.project_id = p_project_id
AND ppa.project_status_code = pps.project_status_code;
SELECT project_status_name
INTO l_assignment_status_name
FROM pa_project_statuses
WHERE project_status_code = p_assignment_status_code;
PA_SCH_EXCEPT_PKG.Insert_Rows(
p_calendar_id => p_calendar_id ,
p_assignment_id => p_assignment_id ,
p_project_id => p_project_id ,
p_schedule_type_code => p_assignment_type ,
p_assignment_status_code => p_assignment_status_code ,
p_exception_type_code => 'CHANGE_STATUS' ,
p_start_date => p_start_date ,
p_end_date => p_end_date ,
x_exception_id => l_exception_id ,
x_return_status => l_x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data );
-- If the user select calendar name only then taking the calendar id
IF (p_calendar_id IS NULL ) THEN
BEGIN
SELECT calendar_id
INTO l_calendar_id
FROM jtf_calendars_vl
WHERE calendar_name = p_calendar_name;
PA_SCH_EXCEPT_PKG.Insert_Rows(
p_calendar_id => l_calendar_id ,
p_assignment_id => p_assignment_id ,
p_project_id => p_project_id ,
p_schedule_type_code => p_assignment_type ,
p_exception_type_code => 'CHANGE_CALENDAR' ,
p_start_date => p_start_date ,
p_end_date => p_end_date ,
x_exception_id => l_t_exception_id ,
x_return_status => l_x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data );
-- This cursor will select the distinct assignment id
CURSOR csr_sch_excp IS
SELECT distinct a.assignment_id, b.record_version_number, b.project_id
FROM pa_schedule_exceptions a , pa_project_assignments b
WHERE a.assignment_id = b.assignment_id
ORDER by a.assignment_id;
SELECT calendar_id,
schedule_exception_id,
assignment_id,
project_id,
status_code,
schedule_type_code,
exception_type_code,
resource_calendar_percent,
non_working_day_flag,
change_hours_type_code,
change_calendar_type_code,
-- change_calendar_name,
change_calendar_id,
duration_shift_type_code,
duration_shift_unit_code,
number_of_shift,
start_date,
end_date,
Monday_hours,
Tuesday_hours,
Wednesday_hours,
Thursday_hours,
Friday_hours,
saturday_hours,
Sunday_hours
FROM pa_schedule_exceptions
WHERE assignment_id = p_assignment_id
AND ((p_exception_id IS NULL) OR
(schedule_exception_id = p_exception_id))
ORDER BY schedule_exception_id;
pa_assignment_approval_pvt.update_approval_status(
p_assignment_id => p_assignment_id,
p_action_code => PA_ASSIGNMENT_APPROVAL_PUB.g_update_action,
p_record_version_number => p_record_version_number,
x_record_version_number => l_record_version_number,
x_change_id => l_change_id,
x_apprvl_status_code => l_temp_status_code,
x_return_status => l_x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
l_tr_sch_rec_tab.delete;
-- seperating the deleted or non deleted record i.e inserted or updated
PA_SCHEDULE_UTILS.sep_del_sch_rec_tab(l_out_tr_sch_rec_tab,
l_del_tr_sch_rec_tab,
l_chg_tr_sch_rec_tab,
l_x_return_status,
x_msg_count,
x_msg_data
);
PA_SCHEDULE_UTILS.log_message(1,'after delete seperate (change ) : ',l_chg_tr_sch_rec_tab );
PA_SCHEDULE_UTILS.log_message(1,'after delete seperate (delete ) : ',l_del_tr_sch_rec_tab );
PA_SCHEDULE_UTILS.log_message(1,'FINAL (delete ) : ',l_del_tr_sch_rec_tab );
select start_date, end_date, resource_id, record_version_number -- Unilog Selected record_version_number too
into l_old_start_date, l_old_end_date, l_resource_id, l_record_version_number_wo_chg
from pa_project_assignments
where assignment_id = p_assignment_id;
PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API delete_rows ....');
PA_SCH_EXCEPT_PKG.delete_rows(l_sch_except_record_tab,
l_x_return_status,
x_msg_count,
x_msg_data
);
PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API delete_rows ..');
-- inserting the rows from schedule except history table
IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
IF (FND_API.TO_BOOLEAN(NVL(l_save_to_hist,FND_API.G_TRUE))) THEN
PA_SCH_EXCEPT_HIST_PKG.insert_rows(
l_sch_except_record_tab,
l_change_id,
l_x_return_status,
x_msg_count,
x_msg_data
);
PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API insert_rows ....');
-- Inserting the records in PA_SCHEDULES table
PA_SCHEDULE_PKG.insert_rows(l_x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
/** Added call to update_wp_calendar for all projects **/
-- Start Of Bug No :4666318
--Commented for Bug No:4666318
-- This functionality was originally introduced in FP.K .But It was removed in FP.M.
/* PA_PROJECT_STRUCTURE_PVT1.update_all_wp_calendar
( p_calendar_id => p_calendar_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);*/
SELECT carrying_out_organization_id
INTO l_t_carrying_out_org_id
FROM PA_PROJECTS_ALL
WHERE project_id = p_project_id;
PA_SCHEDULE_UTILS.log_message(1,'before select on hr_org.... ');
--SELECT TO_NUMBER(hr1.org_information2) ,
SELECT TO_NUMBER(hr1.org_information1) ,
cal1.calendar_name
INTO x_calendar_id ,
x_calendar_name
FROM hr_organization_information hr1,
jtf_calendars_vl cal1
--WHERE cal1.calendar_id = TO_NUMBER(hr1.org_information2) BUG 3530529
-- WHERE TO_CHAR(cal1.calendar_id) = hr1.org_information2
WHERE TO_CHAR(cal1.calendar_id) = hr1.org_information1
AND hr1.organization_id = l_t_carrying_out_org_id
--AND hr1.org_information_context = 'Exp Organization Defaults';
PA_SCHEDULE_UTILS.log_message(1,'after select on hr_org.... ');
PA_SCHEDULE_UTILS.log_message(1,'inside no data found for select on hr_org.... ');
PA_SCHEDULE_UTILS.log_message(1,'ERROR while excuting select on hr_org.... '||sqlerrm);
SELECT
calendar_name
INTO x_calendar_name
FROM jtf_calendars_vl
WHERE calendar_id = TO_NUMBER(l_temp_calendar_id);
PA_SCHEDULE_UTILS.log_message(1,'ERROR while excuting select on jtf_calendars_vl.... '||sqlerrm);
-- This cursor will select only those records which are matching in the start and end calendar name
-- or coming between them
CURSOR C1 IS SELECT calendar_id,calendar_name
FROM JTF_CALENDARS_VL
WHERE calendar_name BETWEEN l_t_start_calendar_name AND l_t_end_calendar_name;
SELECT MIN(calendar_name)
INTO l_t_start_calendar_name
FROM JTF_CALENDARS_VL;
SELECT MAX(calendar_name)
INTO l_t_end_calendar_name
FROM JTF_CALENDARS_VL;
SELECT 1
INTO l_I
FROM JTF_CALENDARS_B
WHERE calendar_id=v_c1.calendar_id
FOR UPDATE ;
DELETE FROM PA_SCHEDULES
WHERE calendar_id = v_c1.calendar_id
AND schedule_type_code = 'CALENDAR';
-- Inserting the calendars in session level temp table to pupulate the report
INSERT INTO PA_CAL_GEN_STATUS_TEMP(calendar_id,calendar_name,generate_status_flag,message_code)
VALUES(v_c1.calendar_id,v_c1.calendar_name,l_flag,l_code);
l_update_work_zero_start_date DATE;
l_update_work_zero_end_date DATE;
-- l_update_work_zero_start_date : Start date for the Gap created in the assignment
-- l_update_work_zero_end_date : End date for the Gap created in the assignment
-- x_call_timeline_st_date : The start date from which timeline should be regenerated.
-- x_call_timeline_end_date : The end date till which timeline should be regenerated.
IF ((p_start_date BETWEEN p_asgn_start_date AND p_asgn_end_date) AND (p_end_date BETWEEN p_asgn_start_date AND p_asgn_end_date)) THEN
-- Changes are Within Assignment Date Range
PA_SCHEDULE_UTILS.log_message(1,'Changes are Within Assignment Date Range');
l_update_work_zero_start_date := l_actual_end_date+1;
l_update_work_zero_end_date := p_asgn_start_date-1;
IF l_update_work_zero_end_date >= l_update_work_zero_start_date THEN
l_call_cng_work_patt_out_range := true;
x_call_timeline_end_date := l_update_work_zero_end_date;
-- l_update_work_zero_start_date is 15-Oct-2003 and l_update_work_zero_end_date is 19-Oct-2003
-- for which change_work_pattern should be called with all 0 hours in monday to sunday
-- x_call_timeline_st_date, and x_call_timeline_end_date for which forecast should be regenerated
-- Example 2 Results : l_new_assgn_start_date is 04-Oct-2003 and l_new_assgn_end_date is 10-Nov-2003
-- So now new assignment date is 04-Oct-2003 to 10-Nov-2003 for which change_duration should be called.
-- l_update_work_zero_start_date is 15-Oct-2003 and l_update_work_zero_end_date is 14-Oct-2003
-- so change_work_pattern should not be called with all 0 hours in monday to sunday. Hence
-- l_call_cng_work_patt_out_range will remain false
-- x_call_timeline_st_date, and x_call_timeline_end_date for which forecast should be regenerated
/* Commented and moved above for Bug 4183479
FOR i IN (l_actual_start_date-p_start_date+1) .. p_hours_table.LAST LOOP
IF p_hours_table(i) IS NULL THEN
l_hours_table(i-(l_actual_start_date-p_start_date+1)+1) := 0; -- Bug 3234786 : To make sure that it starts from 1
l_update_work_zero_start_date := p_asgn_end_date+1;
l_update_work_zero_end_date := l_actual_start_date-1;
IF l_update_work_zero_end_date >= l_update_work_zero_start_date THEN
l_call_cng_work_patt_out_range := true;
x_call_timeline_st_date := l_update_work_zero_start_date;
-- l_update_work_zero_start_date is 11-Nov-2003 and l_update_work_zero_end_date is 14-Nov-2003
-- for which change_work_pattern should be called with all 0 hours in monday to sunday
-- Example 2 Results : l_new_assgn_start_date is 20-Oct-2003 and l_new_assgn_end_date is 25-Nov-2003
-- So now new assignment date is 04-Oct-2003 to 10-Nov-2003 for which change_duration should be called.
-- l_update_work_zero_start_date is 15-Nov-2003 and l_update_work_zero_end_date is 14-Nov-2003
-- so change_work_pattern should not be called with all 0 hours in monday to sunday. Hence
-- l_call_cng_work_patt_out_range will remain false
/* Commented and moved above for Bug 4183479
FOR i IN p_hours_table.FIRST .. (l_actual_end_date-p_start_date+1) LOOP
IF p_hours_table(i) IS NULL THEN
l_hours_table(i) := 0;
PA_SCHEDULE_UTILS.log_message(1,'l_update_work_zero_start_date='||l_update_work_zero_start_date);
PA_SCHEDULE_UTILS.log_message(1,'l_update_work_zero_end_date='||l_update_work_zero_end_date);
p_start_date => l_update_work_zero_start_date ,
p_end_date => l_update_work_zero_end_date ,
p_monday_hours => 0 ,
p_tuesday_hours => 0 ,
p_wednesday_hours => 0 ,
p_thursday_hours => 0 ,
p_friday_hours => 0 ,
p_saturday_hours => 0 ,
p_sunday_hours => 0 ,
p_asgn_start_date => l_new_assgn_start_date ,
p_asgn_end_date => l_new_assgn_end_date ,
p_init_msg_list => FND_API.G_FALSE ,
p_last_row_flag => 'Y' , --Changed 'N' to 'Y' for Bug 4165970.
p_generate_timeline_flag => 'N' ,
x_return_status => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data) ;
select decode(l_global_week_start_day,1,1,2,0,3,6,4,5,5,4,6,3,7,2,0) into l_days_to_inc from dual;
p_start_date => l_update_work_zero_start_date ,
p_end_date => l_update_work_zero_end_date ,
p_monday_hours => 0 ,
p_tuesday_hours => 0 ,
p_wednesday_hours => 0 ,
p_thursday_hours => 0 ,
p_friday_hours => 0 ,
p_saturday_hours => 0 ,
p_sunday_hours => 0 ,
p_asgn_start_date => l_new_assgn_start_date ,
p_asgn_end_date => l_new_assgn_end_date ,
p_init_msg_list => FND_API.G_FALSE ,
p_last_row_flag => 'N' ,
p_generate_timeline_flag => 'N' ,
x_return_status => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data) ;
-- assignment_effort also has to be updated.
-- For performance we can call create_forecast_item, but we need to add/subtract
-- the extra effort. This can be done later.
IF ((l_call_change_work_pattern = true) OR(l_call_change_duration = true)) THEN
PA_TIMELINE_PVT.Create_Timeline(
p_assignment_id => p_assignment_id ,
x_return_status => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data) ;
p_delete_flag IN VARCHAR2 := 'Y' ,
x_show_start_date OUT NOCOPY DATE , --File.Sql.39 bug 4440895
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
AS
l_work_pattern_table WORK_PATTERN_TAB_TYPE;
SELECT
project_id,
project_name,
assignment_name,
start_date,
end_date,
status_name,
assignment_id,
resource_id,
status_code,
record_version_number,
assignment_type,
calendar_id,
calendar_type,
project_role_name,
apprvl_status_name,
assignment_effort,
assignment_duration,
project_system_status_code,
--decode(decode(assignment_type, 'STAFFED_ASSIGNMENT', pa_security_pvt.check_user_privilege
--('PA_ASN_SCHEDULE_ED', 'PA_PROJECTS', project_id), 'STAFFED_ADMIN_ASSIGNMENT',
-- pa_security_pvt.check_user_privilege('PA_ADM_ASN_SCHEDULE_ED', 'PA_PROJECTS',project_id)),'Y',1,0) read_only_flag
-- 1 read_only_flag -- Here we are selecting read_only_flag as 0, actual value will be poulated Java side bcoz it does caching
DECODE(mass_wf_in_progress_flag, 'Y', 1,
DECODE(pending_approval_flag, 'Y', 1,
DECODE(apprvl_status_code, 'ASGMT_APPRVL_CANCELED', 1, -- Bug 3235731
DECODE(status_code, null, 0, -- 3235675 This is needed as is_asgmt_allow_stus_ctl_check returns N if status_code is null
DECODE(pa_assignment_utils.is_asgmt_allow_stus_ctl_check(status_code, project_id, 'N'), 'N', 1, 0))))) read_only_flag
FROM pa_project_assignments_v asgn
WHERE asgn.resource_id = l_res_id
AND (
((p_assgn_range_start_date IS NOT NULL AND p_assgn_range_end_date IS NOT NULL)
AND
(((asgn.start_date between p_assgn_range_start_date AND p_assgn_range_end_date)OR(asgn.end_date between p_assgn_range_start_date AND p_assgn_range_end_date))
OR
((p_assgn_range_start_date between asgn.start_date AND asgn.end_date)OR(p_assgn_range_end_date between asgn.start_date AND asgn.end_date))
)
)
OR -- Get all assignments excpet those who are end dated before p_assgn_range_start_date
( p_assgn_range_start_date IS NOT NULL AND p_assgn_range_end_date IS NULL AND asgn.end_date >= p_assgn_range_start_date
)
OR -- Get all assignments excpet those who are started after p_assgn_range_end_date
( p_assgn_range_start_date IS NULL AND p_assgn_range_end_date IS NOT NULL AND asgn.start_date <= p_assgn_range_end_date
)
)
--AND asgn.status_code=nvl(p_status_code, asgn.status_code) 3235675 This is not needed, Also this is incorrect if status_code is null
AND 'STAFFED_ASGMT_CANCEL' <> nvl(project_system_status_code, 'XYZ') -- Bug 3235731
ORDER BY resource_id, assignment_id; -- This is very important. Logic is woven depending on this order
SELECT
item_date,
capacity_quantity quantity,
--capacity_quantity-(decode(availability_flag,
-- 'Y', decode(sign(capacity_quantity-availability_quantity), 1, 0, availability_quantity),
-- 'N', decode(sign(capacity_quantity-overcommitment_quantity), 1, 0, overcommitment_quantity))) quantity,
resource_id,
forecast_item_type,
-1 assignment_id
FROM pa_forecast_items
WHERE resource_id = l_res_id
AND forecast_item_type = 'U'
AND item_date between l_actual_display_start_date and l_actual_display_start_date+14
AND delete_flag = 'N'
UNION ALL
SELECT
fi.item_date,
fi.item_quantity quantity,
fi.resource_id,
fi.forecast_item_type,
asgn.assignment_id
FROM pa_project_assignments asgn,
pa_forecast_items fi
WHERE asgn.resource_id = l_res_id
AND fi.resource_id = l_res_id
AND fi.delete_flag = 'N'
AND fi.item_date between l_actual_display_start_date and l_actual_display_start_date+13
AND fi.forecast_item_type = 'A'
AND fi.assignment_id = asgn.assignment_id
AND (
((p_assgn_range_start_date IS NOT NULL AND p_assgn_range_end_date IS NOT NULL)
AND
(((asgn.start_date between p_assgn_range_start_date AND p_assgn_range_end_date)OR(asgn.end_date between p_assgn_range_start_date AND p_assgn_range_end_date))
OR
((p_assgn_range_start_date between asgn.start_date AND asgn.end_date)OR(p_assgn_range_end_date between asgn.start_date AND asgn.end_date))
)
)
OR -- Get all assignments excpet those who are end dated before p_assgn_range_start_date
( p_assgn_range_start_date IS NOT NULL AND p_assgn_range_end_date IS NULL AND asgn.end_date >= p_assgn_range_start_date
)
OR -- Get all assignments excpet those who are started after p_assgn_range_end_date
( p_assgn_range_start_date IS NULL AND p_assgn_range_end_date IS NOT NULL AND asgn.start_date <= p_assgn_range_end_date
)
)
--AND asgn.status_code=nvl(p_status_code, asgn.status_code) 3235675 This is not needed, Also this is incorrect if status_code is null
ORDER BY resource_id, assignment_id, item_date, forecast_item_type desc;
PA_SCHEDULE_UTILS.log_message(1,'p_delete_flag='||p_delete_flag);
Select (to_number(to_char((to_date('01-01-1950','dd-mm-yyyy')+(l_global_week_start_day - 1)),'D')))
into l_global_week_start_day_new
from dual;
SELECT next_day(p_display_start_date,decode(l_global_week_start_day,1,'SUNDAY',2,'MONDAY',3,'TUESDAY',4,'WEDNESDAY',5,'THURSDAY',6,'FRIDAY',7,'SATURDAY'))-7
INTO l_actual_display_start_date
FROM dual;*/
SELECT to_char(p_display_start_date,'D') INTO l_display_start_day FROM dual;
IF p_delete_flag = 'Y' then
DELETE FROM pa_work_pattern_temp_table;
SELECT meaning into l_capacity_label from pa_lookups where lookup_type='PA_CAPC_AVL_LABELS' and lookup_code ='CAPACITY';
SELECT meaning into l_availability_label from pa_lookups where lookup_type='PA_CAPC_AVL_LABELS' and lookup_code ='AVAILABILITY';
SELECT MOD(l_where_to_place_counter,10)
INTO l_counter_mod
FROM dual;
PA_SCHEDULE_UTILS.log_message(1,'Inserting data in pa_work_pattern_temp_table');
INSERT INTO pa_work_pattern_temp_table
(
PROJECT_ID ,
PROJECT_NAME,
ASSIGNMENT_NAME,
START_DATE,
END_DATE,
STATUS_NAME,
ASSIGNMENT_ID,
RESOURCE_ID,
STATUS_CODE,
RECORD_VERSION_NUMBER,
ASSIGNMENT_TYPE,
CALENDAR_ID,
CALENDAR_TYPE,
PROJECT_ROLE_NAME,
APPRVL_STATUS_NAME,
ASSIGNMENT_EFFORT,
ASSIGNMENT_DURATION,
PROJECT_SYSTEM_STATUS_CODE,
DAY1,
DAY2,
DAY3,
DAY4,
DAY5,
DAY6,
DAY7,
DAY8,
DAY9,
DAY10,
DAY11,
DAY12,
DAY13,
DAY14,
QTY1,
QTY2,
QTY3,
QTY4,
QTY5,
QTY6,
QTY7,
QTY8,
QTY9,
QTY10,
QTY11,
QTY12,
QTY13,
QTY14,
row_type_code,
read_only_flag)
values
(
l_work_pattern_table(j).l_PROJECT_ID ,
l_work_pattern_table(j).l_PROJECT_NAME,
l_work_pattern_table(j).l_ASSIGNMENT_NAME,
l_work_pattern_table(j).l_START_DATE,
l_work_pattern_table(j).l_END_DATE,
l_work_pattern_table(j).l_STATUS_NAME,
l_work_pattern_table(j).l_ASSIGNMENT_ID,
l_work_pattern_table(j).l_RESOURCE_ID,
l_work_pattern_table(j).l_STATUS_CODE,
l_work_pattern_table(j).l_RECORD_VERSION_NUMBER,
l_work_pattern_table(j).l_ASSIGNMENT_TYPE,
l_work_pattern_table(j).l_CALENDAR_ID,
l_work_pattern_table(j).l_CALENDAR_TYPE,
l_work_pattern_table(j).l_PROJECT_ROLE_NAME,
l_work_pattern_table(j).l_APPRVL_STATUS_NAME,
l_work_pattern_table(j).l_ASSIGNMENT_EFFORT,
l_work_pattern_table(j).l_ASSIGNMENT_DURATION,
l_work_pattern_table(j).l_PROJECT_SYSTEM_STATUS_CODE,
l_work_pattern_table(j).l_DAY1,
l_work_pattern_table(j).l_DAY2,
l_work_pattern_table(j).l_DAY3,
l_work_pattern_table(j).l_DAY4,
l_work_pattern_table(j).l_DAY5,
l_work_pattern_table(j).l_DAY6,
l_work_pattern_table(j).l_DAY7,
l_work_pattern_table(j).l_DAY8,
l_work_pattern_table(j).l_DAY9,
l_work_pattern_table(j).l_DAY10,
l_work_pattern_table(j).l_DAY11,
l_work_pattern_table(j).l_DAY12,
l_work_pattern_table(j).l_DAY13,
l_work_pattern_table(j).l_DAY14,
l_work_pattern_table(j).l_QTY1,
l_work_pattern_table(j).l_QTY2,
l_work_pattern_table(j).l_QTY3,
l_work_pattern_table(j).l_QTY4,
l_work_pattern_table(j).l_QTY5,
l_work_pattern_table(j).l_QTY6,
l_work_pattern_table(j).l_QTY7,
l_work_pattern_table(j).l_QTY8,
l_work_pattern_table(j).l_QTY9,
l_work_pattern_table(j).l_QTY10,
l_work_pattern_table(j).l_QTY11,
l_work_pattern_table(j).l_QTY12,
l_work_pattern_table(j).l_QTY13,
l_work_pattern_table(j).l_QTY14,
l_work_pattern_table(j).l_row_type_code,
l_work_pattern_table(j).l_read_only_flag) ;