[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT level_of_pay GAP_LEVEL, MIN(absence_date) START_DATE,
MAX(absence_date) END_DATE, SUM(DURATION) DURATION,
SUM(DURATION_IN_HOURS) DURATION_IN_HOURS
FROM pqp_gap_daily_absences
WHERE gap_absence_plan_id = p_gap_absence_plan_id
GROUP BY level_of_pay
HAVING level_of_pay LIKE '%BAND%'
OR level_of_pay LIKE 'NOBANDMIN'
ORDER BY level_of_pay ;
SELECT level_of_entitlement GAP_LEVEL, MIN(absence_date) START_DATE,
MAX(absence_date) END_DATE,SUM(DURATION) DURATION,
SUM(DURATION_IN_HOURS) DURATION_IN_HOURS
FROM pqp_gap_daily_absences
WHERE gap_absence_plan_id = p_gap_absence_plan_id
GROUP BY level_of_entitlement
HAVING level_of_entitlement LIKE '%BAND%'
OR level_of_entitlement LIKE 'WAITINGDAY'
ORDER BY level_of_entitlement ;
SELECT gap_level GAP_LEVEL,gap_duration_summary_id GAP_DURATION_SUMMARY_ID,
object_version_number OBJECT_VERSION_NUMBER,'D' ACTION_TYPE
FROM pqp_gap_duration_summary
WHERE summary_type = p_summary_type AND
gap_absence_plan_id = p_gap_absence_plan_id ;
SELECT service.date_start
FROM per_all_assignments_f assign
,per_periods_of_service service
WHERE p_balance_effective_date BETWEEN assign.effective_start_date
AND assign.effective_end_date
AND assign.assignment_id = p_assignment_id
AND service.period_of_service_id (+) = assign.period_of_service_id;
INSERT INTO pqp_gap_daily_absences
(gap_daily_absence_id --NOT NULL NUMBER(15)
,gap_absence_plan_id --NOT NULL NUMBER(15)
,absence_date --NOT NULL DATE
,work_pattern_day_type --NOT NULL VARCHAR2(30)
,level_of_entitlement --NOT NULL VARCHAR2(30)
,level_of_pay --NOT NULL VARCHAR2(30)
,duration --NOT NULL NUMBER(11,5)
,duration_in_hours -- NUMER(8,5) -- added
,working_days_per_week
,fte
-- ,last_updated_by -- NUMBER(15)
-- ,last_update_date -- DATE
-- ,created_by -- NUMBER(15)
-- ,creation_date -- DATE
,object_version_number -- NUMBER(15)
)
VALUES
(pqp_gap_daily_absences_s.NEXTVAL
,l_gap_absence_plan_ids(i) --NOT NULL NUMBER(15)
,l_absence_dates(i) --NOT NULL DATE
,l_work_pattern_day_types(i) --NOT NULL VARCHAR2(30)
,l_level_of_entitlements(i) --NOT NULL VARCHAR2(30)
,l_level_of_payments(i) --NOT NULL VARCHAR2(30)
,l_durations(i) --NOT NULL NUMBER(11,5)
,l_durations_in_hours(i) -- NUMBER(8,5)
,l_working_days_per_week(i)
,l_fte(i)
-- ,l_last_updated_by -- NUMBER(15)
-- ,l_last_update_date -- DATE
-- ,l_created_by -- NUMBER(15)
-- ,l_creation_date -- DATE
,l_object_version_numbers(i) -- NUMBER(15)
);
pqp_gds_api.update_duration_summary
(p_gap_duration_summary_id => p_absence_summary_tbl(i).gap_duration_summary_id
,p_date_start => p_absence_summary_tbl(i).date_start
,p_date_end => p_absence_summary_tbl(i).date_end
,p_assignment_id => p_absence_summary_tbl(i).assignment_id
,p_gap_absence_plan_id => p_absence_summary_tbl(i).gap_absence_plan_id
,p_duration_in_days => p_absence_summary_tbl(i).duration_in_days
,p_duration_in_hours => p_absence_summary_tbl(i).duration_in_hours
,p_summary_type => p_absence_summary_tbl(i).summary_type
,p_gap_level => p_absence_summary_tbl(i).gap_level
,p_object_version_number => p_absence_summary_tbl(i).object_version_number
);
PROCEDURE update_duration_summary
(p_gap_absence_plan_id IN NUMBER
,p_assignment_id IN NUMBER
)
IS
l_proc_name VARCHAR2(61) := g_package_name||'update_duration_summary';
pqp_gds_api.delete_duration_summary
(p_gap_duration_summary_id
=>l_ent_summary_existing_rows(l).gap_duration_summary_id
,p_object_version_number
=>l_ent_summary_existing_rows(l).object_version_number
);
pqp_gds_api.delete_duration_summary
(p_gap_duration_summary_id
=>l_pay_summary_existing_rows(l).gap_duration_summary_id
,p_object_version_number
=>l_pay_summary_existing_rows(l).object_version_number
);
l_ent_summary_existing_rows.DELETE;
l_pay_summary_existing_rows.DELETE;
l_duration_summary.DELETE;
END update_duration_summary ;
,p_update IN BOOLEAN
)
IS
l_proc_name VARCHAR2(61) := g_package_name||'write_absence_summary';
IF p_update
THEN
update_duration_summary
(p_gap_absence_plan_id => p_gap_absence_plan_id
,p_assignment_id => p_assignment_id
);
PROCEDURE delete_absence_plan_details
(p_assignment_id IN NUMBER -- unused
,p_business_group_id IN NUMBER -- unused
,p_plan_id IN NUMBER
,p_absence_id IN NUMBER
,p_delete_start_date IN DATE
,p_delete_end_date IN DATE
,p_error_code OUT NOCOPY NUMBER
,p_message OUT NOCOPY VARCHAR2
)
IS
CURSOR csr_gap_dur_sum_rows(p_gap_absence_plan_id NUMBER)
IS
SELECT gap_duration_summary_id ,
object_version_number
FROM pqp_gap_duration_summary
WHERE gap_absence_plan_id = p_gap_absence_plan_id ;
'delete_absence_plan_details';
debug(p_delete_start_date);
debug(p_delete_end_date);
DELETE
FROM pqp_gap_daily_absences gda
WHERE gda.gap_absence_plan_id = l_gap_absence_plan.gap_absence_plan_id
AND gda.absence_date
BETWEEN NVL(p_delete_start_date,gda.absence_date)
AND NVL(p_delete_end_date,gda.absence_date);
debug('pqp_gap_daily_absences rows deleted.');
DELETE
FROM pqp_gap_absence_plans gap
WHERE gap.gap_absence_plan_id = l_gap_absence_plan.gap_absence_plan_id;
pqp_gds_api.delete_duration_summary
(p_gap_duration_summary_id
=> l_gap_dur_sum_rows.gap_duration_summary_id
,p_object_version_number
=> l_gap_dur_sum_rows.object_version_number
);
debug('pqp_gap_absence_plans rows deleted.');
ELSE -- there are still some daily absences left ie was a partial delete
-- a partial delete takes place when an end date has been changed
-- such that it is less than the last daily absence date
-- if it was a partial delete then p_delete_start_date must have been
-- supplied in which case the new last gap daily absence date would
-- p_delete_start_date - 1
pqp_gap_upd.upd
(p_effective_date => p_delete_start_date - 1
,p_gap_absence_plan_id => l_gap_absence_plan.gap_absence_plan_id
,p_object_version_number => l_gap_absence_plan.object_version_number
,p_assignment_id => p_assignment_id
,p_absence_attendance_id => p_absence_id
,p_pl_id => p_plan_id
,p_last_gap_daily_absence_date => p_delete_start_date - 1
);
update_duration_summary
(p_gap_absence_plan_id => l_gap_absence_plan.gap_absence_plan_id
,p_assignment_id => p_assignment_id
);
END delete_absence_plan_details;
SELECT abs_information1 -- fraction of start day
,abs_information2 -- fraction of end day
,abs_information3 -- UOM of the fraction
FROM per_absence_attendances
WHERE abs_information_category = 'GB_PQP_OSP_OMP_PART_DAYS'
AND absence_attendance_id = p_absence_attendance_id;
SELECT gda.level_of_entitlement level_of_entitlement
,SUM(gda.duration) sum_of_duration
,SUM(gda.duration_in_hours) sum_of_duration_in_hours
,SUM(gda.duration/gda.working_days_per_week) sum_of_duration_per_week
,SUM(gda.duration_in_hours/fte) sum_of_fte_hours
FROM pqp_gap_absence_plans gap
,ben_pl_f pln
,pqp_gap_daily_absences gda
WHERE gap.assignment_id IN -- automatically makes the assignment list distinct
(SELECT other_asg.assignment_id
FROM per_all_assignments_f this_asg
,per_all_assignments_f other_asg
WHERE this_asg.assignment_id = p_assignment_id
AND other_asg.person_id = this_asg.person_id
AND other_asg.primary_flag = 'Y'
AND other_asg.period_of_service_id = this_asg.period_of_service_id
)
AND gda.gap_absence_plan_id = gap.gap_absence_plan_id
AND pln.pl_id = gap.pl_id
AND p_range_to_date
BETWEEN pln.effective_start_date AND pln.effective_end_date
AND pln.pl_typ_id = p_pl_typ_id
AND gda.absence_date
BETWEEN p_range_from_date AND p_range_to_date
GROUP BY level_of_entitlement;
SELECT asg.person_id
INTO l_person_id
FROM per_all_assignments_f asg
WHERE asg.assignment_id = p_assignment_id
AND ROWNUM < 2;
SELECT gda.level_of_entitlement level_of_entitlement
,SUM(gda.duration) sum_of_duration
,SUM(gda.duration_in_hours) sum_of_duration_in_hours
,SUM(gda.duration/gda.working_days_per_week) sum_of_duration_per_week
,SUM(gda.duration_in_hours/fte) sum_of_fte_hours
FROM pqp_gap_absence_plans gap
,ben_pl_f pln
,pqp_gap_daily_absences gda
WHERE gap.assignment_id IN -- automatically makes the assignment list distinct
(SELECT asg.assignment_id
FROM per_all_assignments_f asg
WHERE asg.person_id = l_person_id
AND asg.primary_flag = 'Y'
)
AND gda.gap_absence_plan_id = gap.gap_absence_plan_id
AND pln.pl_id = gap.pl_id
AND p_range_to_date
BETWEEN pln.effective_start_date AND pln.effective_end_date
AND pln.pl_typ_id = p_pl_typ_id
AND gda.absence_date
BETWEEN p_range_from_date AND p_range_to_date
GROUP BY level_of_entitlement;
SELECT gda.level_of_entitlement level_of_entitlement
,SUM(gda.duration) sum_of_duration
,SUM(gda.duration_in_hours) sum_of_duration_in_hours
,SUM(gda.duration/gda.working_days_per_week) sum_of_duration_per_week
-- LG/PT
,SUM(gda.duration_in_hours/fte) sum_of_fte_hours
FROM pqp_gap_absence_plans gap
,ben_pl_f pln
,pqp_gap_daily_absences gda
WHERE gap.assignment_id = p_assignment_id
AND gda.gap_absence_plan_id = gap.gap_absence_plan_id
AND pln.pl_id = gap.pl_id
AND p_range_to_date
BETWEEN pln.effective_start_date AND pln.effective_end_date
AND pln.pl_typ_id = p_pl_typ_id
AND gda.absence_date BETWEEN p_range_from_date
AND p_range_to_date
GROUP BY level_of_entitlement;
SELECT
DECODE(p_scheme_period_overlap_rule
,'NC',abs.date_end+1
,'FC',abs.date_start
)
FROM pqp_gap_absence_plans gap
,per_absence_attendances abs
,ben_pl_f pln
WHERE gap.assignment_id = p_assignment_id --an absence for this assignment
AND pln.pl_typ_id = p_pl_typ_id --which is relevant, ie enrolled
AND gap.pl_id = pln.pl_id --into a plan of atleast the same
--plan type as the current one
AND abs.absence_attendance_id = gap.absence_attendance_id
AND abs.date_start < p_scheme_start_date --and which starts before
AND abs.date_end >= p_scheme_start_date-- and ends on or after the
-- scheme start date.
;
SELECT *
FROM hr_lookups hrl
WHERE hrl.lookup_type = p_lookup_type ;
PROCEDURE update_absence_plan_details
(p_assignment_id IN NUMBER
,p_person_id IN NUMBER
,p_business_group_id IN NUMBER
,p_absence_id IN NUMBER
,p_absence_date_start IN DATE
,p_absence_date_end IN DATE
,p_pl_id IN NUMBER
,p_pl_typ_id IN NUMBER
,p_element_type_id IN NUMBER
,p_update_start_date IN DATE
,p_update_end_date IN DATE
,p_output_type IN ff_exec.outputs_t
,p_error_code OUT NOCOPY NUMBER
,p_message OUT NOCOPY VARCHAR2
)
IS
l_absence_start_date DATE;
'update_absence_plan_details';
debug(p_update_start_date);
debug(p_update_end_date);
delete_absence_plan_details
(p_assignment_id => p_assignment_id
,p_business_group_id => p_business_group_id
,p_plan_id => p_pl_id
,p_absence_id => p_absence_id
,p_delete_start_date => l_gap_absence_plan.last_gap_daily_absence_date
,p_delete_end_date => l_gap_absence_plan.last_gap_daily_absence_date
,p_error_code => l_error_code
,p_message => l_error_message
);
delete_absence_plan_details
(p_assignment_id => p_assignment_id
,p_business_group_id => p_business_group_id
,p_plan_id => p_pl_id
,p_absence_id => p_absence_id
,p_delete_start_date => l_absence_end_date
,p_delete_end_date => l_gap_absence_plan.last_gap_daily_absence_date
,p_error_code => l_error_code
,p_message => l_error_message
);
delete_absence_plan_details
(p_assignment_id => p_assignment_id
,p_business_group_id => p_business_group_id
,p_plan_id => p_pl_id
,p_absence_id => p_absence_id
,p_delete_start_date => l_absence_end_date+1
,p_delete_end_date => l_gap_absence_plan.last_gap_daily_absence_date
,p_error_code => l_error_code
,p_message => l_error_message
);
END update_absence_plan_details;
l_update_summary BOOLEAN;
l_update_summary := FALSE ;
l_update_summary := TRUE ;
,p_update => l_update_summary
);
SELECT contract_type
FROM pqp_assignment_attributes_f
WHERE business_group_id = p_business_group_id
AND assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date ;
select NVL(SUM(gda.duration),0)
from pqp_gap_daily_absences gda
,pqp_gap_absence_plans gap
,ben_pl_f pl
where pl.pl_id = gap.pl_id
and pl.pl_typ_id = p_pl_typ_id
and gap.gap_absence_plan_id = gda.gap_absence_plan_id
and gap.assignment_id = p_assignment_id
and gda.level_of_pay = 'NOBAND'
and gda.absence_date between p_period_start_date
and (p_period_end_date - 1); -- bug 7110645
select NVL(SUM(gda.duration),0)
from pqp_gap_daily_absences gda
,pqp_gap_absence_plans gap
,ben_pl_f pl
where gap.assignment_id IN
-- automatically makes the assignment list distinct
(SELECT other_asg.assignment_id
FROM per_all_assignments_f this_asg
,per_all_assignments_f other_asg
WHERE this_asg.assignment_id = p_assignment_id
AND other_asg.person_id = this_asg.person_id
AND other_asg.primary_flag = 'Y'
AND other_asg.period_of_service_id = this_asg.period_of_service_id
)
and pl.pl_id = gap.pl_id
and pl.pl_typ_id = p_pl_typ_id
and gap.gap_absence_plan_id = gda.gap_absence_plan_id
and gda.level_of_pay = 'NOBAND'
and gda.absence_date between p_period_start_date
and (p_period_end_date - 1) ; -- bug 7110645
select NVL(SUM(gda.duration),0)
from pqp_gap_daily_absences gda
,pqp_gap_absence_plans gap
,ben_pl_f pl
where gap.assignment_id IN
-- automatically makes the assignment list distinct
(SELECT asg.assignment_id
FROM per_all_assignments_f asg
WHERE asg.person_id = p_person_id
AND asg.primary_flag = 'Y'
)
and pl.pl_id = gap.pl_id
and pl.pl_typ_id = p_pl_typ_id
and gap.gap_absence_plan_id = gda.gap_absence_plan_id
and gda.level_of_pay = 'NOBAND'
and gda.absence_date between p_period_start_date
and (p_period_end_date - 1); -- bug 7110645
SELECT asg.person_id
INTO l_person_id
FROM per_all_assignments_f asg
WHERE asg.assignment_id = p_assignment_id
AND ROWNUM < 2;