[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT l_end_date - l_start_date
INTO l_days
FROM dual;
SELECT pps.date_start
FROM per_periods_of_service pps
,per_all_assignments_f asg
WHERE pps.period_of_service_id = asg.period_of_service_id
AND trunc(p_calculation_date) BETWEEN trunc(asg.effective_start_date)
AND trunc(asg.effective_end_date)
AND asg.assignment_id = p_assignment_id
AND not exists (SELECT 1 FROM pqp_service_history_periods shp
WHERE trunc(pps.date_start) BETWEEN trunc(shp.start_date)
AND trunc(shp.end_date)
AND shp.assignment_id = p_assignment_id
AND shp.continuous_service = NVL(p_continuous_service, shp.continuous_service));
SELECT ADD_MONTHS(p_start_date,(p_period_years * 12))
INTO l_end_date
FROM dual;
SELECT l_end_date - p_start_date
INTO l_days
FROM dual;
SELECT NULL
FROM per_periods_of_service pps
,per_all_assignments_f asg
WHERE pps.period_of_service_id = asg.period_of_service_id
AND trunc(pps.date_start) between trunc(p_start_date)
and trunc(p_end_date)
AND trunc(p_calculation_date) between trunc(asg.effective_start_date)
and trunc(asg.effective_end_date)
AND asg.assignment_id = p_assignment_id ;
SELECT start_date
,LEAST(trunc(end_date), trunc(p_calculation_date)) end_date
,DECODE(LEAST(trunc(end_date), trunc(p_calculation_date)), trunc(end_date),
period_years, NULL) period_years
,DECODE(LEAST(trunc(end_date), trunc(p_calculation_date)), trunc(end_date),
period_days, NULL) period_days
FROM pqp_service_history_periods shp1
WHERE shp1.assignment_id = p_assignment_id
AND trunc(shp1.start_date) <= trunc(p_calculation_date)
AND not exists (SELECT 1 FROM pqp_service_history_periods shp2
WHERE shp2.assignment_id = shp1.assignment_id
AND shp2.service_history_period_id <> shp1.service_history_period_id
AND (trunc(shp2.start_date) between trunc(shp1.start_date)
and trunc(shp1.end_date)
or trunc(shp2.end_date) between trunc(shp1.start_date)
and trunc(shp1.end_date)
or trunc(shp1.start_date) between trunc(shp2.start_date)
and trunc(shp2.end_date)
or trunc(shp1.end_date) between trunc(shp2.start_date)
and trunc(shp2.end_date)));
SELECT MIN(shp1.start_date) start_date
,LEAST(trunc(MAX(shp1.end_date)),trunc(p_calculation_date)) end_date
FROM pqp_service_history_periods shp1
WHERE shp1.assignment_id = p_assignment_id
AND trunc(shp1.start_date) <= trunc(p_calculation_date)
AND exists (SELECT 1 FROM pqp_service_history_periods shp2
WHERE shp2.assignment_id = shp1.assignment_id
AND shp2.service_history_period_id <> shp1.service_history_period_id
AND (trunc(shp2.start_date) between trunc(shp1.start_date)
and trunc(shp1.end_date)
or trunc(shp2.end_date) between trunc(shp1.start_date)
and trunc(shp1.end_date)
or trunc(shp1.start_date) between trunc(shp2.start_date)
and trunc(shp2.end_date)
or trunc(shp1.end_date) between trunc(shp2.start_date)
and trunc(shp2.end_date)));
SELECT start_date
,end_date
,period_years
,period_days
FROM pqp_service_history_periods shp1
WHERE shp1.assignment_id = p_assignment_id;
SELECT start_date
,LEAST(trunc(end_date), trunc(p_calculation_date)) end_date
,DECODE(LEAST(trunc(end_date), trunc(p_calculation_date)), trunc(end_date),
period_years, NULL) period_years
,DECODE(LEAST(trunc(end_date), trunc(p_calculation_date)), trunc(end_date),
period_days, NULL) period_days
FROM pqp_service_history_periods shp1
WHERE shp1.assignment_id = p_assignment_id
AND trunc(shp1.start_date) <= trunc(p_calculation_date)
AND shp1.continuous_service = 'Y'
AND not exists (SELECT 1 FROM pqp_service_history_periods shp2
WHERE shp2.assignment_id = shp1.assignment_id
AND shp2.continuous_service = 'Y'
AND shp2.service_history_period_id <> shp1.service_history_period_id
AND (trunc(shp2.start_date) between trunc(shp1.start_date)
and trunc(shp1.end_date)
or trunc(shp2.end_date) between trunc(shp1.start_date)
and trunc(shp1.end_date)
or trunc(shp1.start_date) between trunc(shp2.start_date)
and trunc(shp2.end_date)
or trunc(shp1.end_date) between trunc(shp2.start_date)
and trunc(shp2.end_date)));
SELECT MIN(shp1.start_date) start_date
,LEAST(trunc(MAX(shp1.end_date)), trunc(p_calculation_date)) end_date
FROM pqp_service_history_periods shp1
WHERE shp1.assignment_id = p_assignment_id
AND trunc(shp1.start_date) <= trunc(p_calculation_date)
AND shp1.continuous_service = 'Y'
AND exists (SELECT 1 FROM pqp_service_history_periods shp2
WHERE shp2.assignment_id = shp1.assignment_id
AND shp2.continuous_service = 'Y'
AND shp2.service_history_period_id <> shp1.service_history_period_id
AND (trunc(shp2.start_date) between trunc(shp1.start_date)
and trunc(shp1.end_date)
or trunc(shp2.end_date) between trunc(shp1.start_date)
and trunc(shp1.end_date)
or trunc(shp1.start_date) between trunc(shp2.start_date)
and trunc(shp2.end_date)
or trunc(shp1.end_date) between trunc(shp2.start_date)
and trunc(shp2.end_date)));
SELECT start_date
,end_date
,period_years
,period_days
FROM pqp_service_history_periods shp1
WHERE shp1.assignment_id = p_assignment_id
AND shp1.continuous_service = 'Y';
SELECT (l_end_date - l_start_date)
INTO l_tot_days
FROM dual;
SELECT TRUNC(MONTHS_BETWEEN(l_end_date, l_start_date)/12)
INTO l_years
FROM dual;
SELECT ADD_MONTHS(l_start_date, l_years * 12)
INTO l_new_start_date
FROM dual;
SELECT l_end_date - l_new_start_date
INTO l_days
FROM dual;