[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_sch_record_tab.delete;
PROCEDURE update_sch_rec_tab ( px_sch_record_tab IN OUT NOCOPY PA_SCHEDULE_GLOB.ScheduleTabTyp,
p_project_id IN NUMBER DEFAULT NULL,
p_calendar_id IN NUMBER DEFAULT NULL,
p_assignment_id IN NUMBER DEFAULT NULL,
p_schedule_type_code IN VARCHAR2 DEFAULT NULL,
p_assignment_status_code IN VARCHAR2 DEFAULT NULL,
p_system_status_code IN VARCHAR2 DEFAULT NULL,
p_start_date IN DATE DEFAULT NULL,
p_end_date IN DATE DEFAULT NULL,
p_monday_hours IN NUMBER DEFAULT NULL,
p_tuesday_hours IN NUMBER DEFAULT NULL,
p_wednesday_hours IN NUMBER DEFAULT NULL,
p_thursday_hours IN NUMBER DEFAULT NULL,
p_friday_hours IN NUMBER DEFAULT NULL,
p_saturday_hours IN NUMBER DEFAULT NULL,
p_sunday_hours IN NUMBER DEFAULT NULL,
p_change_type_code IN VARCHAR2 DEFAULT 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
l_iidx NUMBER;
p_procedure_name => 'update_sch_rec_tab');
END update_sch_rec_tab;
PROCEDURE update_except_record( px_except_record IN OUT NOCOPY PA_SCHEDULE_GLOB.SchExceptRecord,
p_project_id IN NUMBER DEFAULT NULL,
p_calendar_id IN NUMBER DEFAULT NULL,
p_assignment_id IN NUMBER DEFAULT NULL,
p_schedule_type_code IN VARCHAR2 DEFAULT NULL,
p_assignment_status_code IN VARCHAR2 DEFAULT NULL,
p_start_date IN DATE DEFAULT NULL,
p_end_date IN DATE DEFAULT NULL,
p_resource_calendar_percent IN NUMBER DEFAULT NULL,
p_non_working_day_flag IN VARCHAR2 DEFAULT NULL,
p_change_hours_type_code IN VARCHAR2 DEFAULT NULL,
p_monday_hours IN NUMBER DEFAULT NULL,
p_tuesday_hours IN NUMBER DEFAULT NULL,
p_wednesday_hours IN NUMBER DEFAULT NULL,
p_thursday_hours IN NUMBER DEFAULT NULL,
p_friday_hours IN NUMBER DEFAULT NULL,
p_saturday_hours IN NUMBER DEFAULT NULL,
p_sunday_hours IN NUMBER DEFAULT 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
x_return_status := FND_API.G_RET_STS_SUCCESS;
p_procedure_name => 'update_except_record');
END update_except_record;
SELECT sum(item_quantity)
INTO l_num_hours
FROM pa_forecast_items
WHERE assignment_id = p_assignment_id
AND delete_flag = 'N';
CURSOR C1 IS SELECT calendar_id
FROM jtf_cal_resource_assign jtf_res
WHERE jtf_res.resource_id = l_jtf_res_id
AND jtf_res.resource_type_code = 'RS_EMPLOYEE'
AND jtf_res.primary_calendar_flag = 'Y'
AND jtf_res.calendar_id > 0
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)) ) ;
select resource_organization_id
into l_resource_organization_id
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 = p_resource_id)
and resource_id = p_resource_id;
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_jtf_res_id
AND jtf_cal_resource_assign.resource_type_code = 'RS_EMPLOYEE'
AND jtf_cal_resource_assign.calendar_id > 0
AND jtf_cal_resource_assign.primary_calendar_flag = 'Y';
SELECT resource_id
INTO l_resource_id
FROM PA_RESOURCES_DENORM
WHERE person_id = p_person_id
AND rownum = 1;
SELECT jtf_cal.calendar_name
INTO l_calendar_name
FROM jtf_cal_resource_assign jtf_cal_res,
jtf_calendars_vl jtf_cal,
pa_resources res
WHERE res.resource_id = l_resource_id
AND jtf_cal_res.resource_id = res.jtf_resource_id
AND jtf_cal_res.resource_type_code = 'RS_EMPLOYEE'
AND jtf_cal_res.primary_calendar_flag = 'Y'
AND jtf_cal_res.calendar_id > 0
AND p_date BETWEEN jtf_cal_res.start_date_time
AND nvl(jtf_cal_res.end_date_time, p_date+1)
AND jtf_cal_res.calendar_id = jtf_cal.calendar_id;
SELECT status_code
FROM pa_schedules
WHERE assignment_id = p_assignment_id;
SELECT calendar_id, start_date_time, end_date_time
FROM (
SELECT jtf_res.calendar_id calendar_id,
NVL(jtf_res.start_date_time, l_start_date) start_date_time,
NVL(jtf_res.end_date_time, l_end_date) end_date_time
FROM jtf_cal_resource_assign jtf_res, pa_resources pa_res
WHERE pa_res.resource_id = p_resource_id
and jtf_res.resource_id = pa_res.jtf_resource_id
and jtf_res.resource_type_code = 'RS_EMPLOYEE'
and jtf_res.calendar_id > -1
and jtf_res.primary_calendar_flag = 'Y'
and NVL(jtf_res.start_date_time,l_start_date) <= l_end_date
and NVL(jtf_res.end_date_time,l_end_date) >= l_start_date
and p_resource_id is not null
UNION ALL
SELECT jtf_res.calendar_id calendar_id,
NVL(jtf_res.start_date_time, l_start_date) start_date_time,
NVL(jtf_res.end_date_time, l_end_date) end_date_time
FROM jtf_cal_resource_assign jtf_res
WHERE jtf_res.resource_id = p_jtf_resource_id
and jtf_res.resource_type_code = 'RS_EMPLOYEE'
and jtf_res.calendar_id > -1
and jtf_res.primary_calendar_flag = 'Y'
and NVL(jtf_res.start_date_time,l_start_date) <= l_end_date
and NVL(jtf_res.end_date_time,l_end_date) >= l_start_date
and p_jtf_resource_id is not null)
order by start_date_time;
select trunc(min(start_date)) min_start_date,
trunc(max(end_date)) max_end_date,
max(monday_hours) + max(tuesday_hours) + max(wednesday_hours) +
max(thursday_hours) + max(friday_hours) + max(saturday_hours) +
max(sunday_hours) max_total_hours
from pa_schedules
where schedule_type_code = 'CALENDAR'
and calendar_id = p_calendar_id;