[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
updates
09 Feb 06 K Thampan 4891953 Fixed performance bug.
23-AUG-06 K Thampan 5482199 Statutory changes for 2007
19-SEP-06 K Thampan 5547703 Amend sap_control to call generate_payments
with insert-mode if absence is > 0 and
also change csr_check_if_existing_entries
not to reference from per_absence_attendances
table
19-OCT-06 K Thampan 5604330 Amended function entitled_to_sap to
check for employee date of death before creating
'Some work was done' stoppage.
Also update check_death to create stoppage based
on the 'rolling 7-day week' not the next Sunday.
02-AUG-07 P Balu 6271407 Changed the To_Date function to fnd_date.canonical_to_date
function in the cursor csr_existing_entries.
14-NOV-08 Nagabushan 7563477 Changed the fnd_date.canonical_to_date function to
fnd_date.chardate_to_date function in the cursor csr_existing_entries.
*/
--------------------------------------------------------------------------------
g_package varchar2(33) := ' ssp_sap_pkg.'; -- Global package name
select absence.absence_attendance_id,
absence.date_start,
nvl (absence.date_end, hr_general.end_of_time) date_end,
absence.date_notification,
absence.accept_late_notification_flag
from
per_absence_attendances ABSENCE
where absence.maternity_id = p_maternity_id
order by absence.date_start;
select maternity.person_id,
maternity.due_date,
ssp_sap_pkg.MATCHING_WEEK_OF_ADOPTION (MATCHING_DATE) MW,
MATCHING_DATE MATCHING_DATE,
PLACEMENT_DATE PLACEMENT_DATE,
DISRUPTED_PLACEMENT_DATE DISRUPTED_PLACEMENT_DATE,
maternity.maternity_id,
maternity.actual_birth_date,
maternity.live_birth_flag,
maternity.start_date_with_new_employer,
maternity.MPP_start_date APP_start_date,
maternity.notification_of_birth_date,
maternity.start_date_maternity_allowance,
maternity.pay_SMP_as_lump_sum pay_SAP_as_lump_sum,
person.date_of_death,
service.date_start,
nvl (service.final_process_date, hr_general.end_of_time) FINAL_PROCESS_DATE
from
ssp_maternities MATERNITY,
per_all_people_f PERSON,
per_periods_of_service SERVICE
where
person.person_id = maternity.person_id
and person.person_id = service.person_id
and maternity.maternity_id = p_maternity_id
and nvl(service.actual_termination_date+1,service.date_start)
between person.effective_start_date
and person.effective_end_date
and service.date_start = (select max(serv.date_start)
from per_periods_of_service serv
where serv.person_id = person.person_id);
select 1
from ssp_maternities
where person_id = p_person_id;
select average_earnings_amount
from ssp_earnings_calculations
where person_id = person.person_id
and effective_date = l_effective_date;
select count (*)
from per_absence_attendances
where person_id = person.person_id
and maternity_id = p_maternity_id;
select /*+ ORDERED use_nl(paa,paaf,etype,entry) */
entry.element_entry_id,
entry.effective_start_date
from per_all_assignments_f PAAF,
pay_element_types_f ETYPE,
pay_element_entries_f ENTRY
where PAAF.person_id = person.person_id
and ETYPE.element_name = c_SAP_element_name
and ETYPE.legislation_code = 'GB'
and ENTRY.element_type_id = ETYPE.element_type_id
and entry.creator_type = c_SAP_creator_type
and entry.creator_id = p_maternity_id
and entry.assignment_id = PAAF.assignment_id
and not exists (
--
-- Do not select entries which have already had reversal action
-- taken against them because they are effectively cancelled out.
--
select 1
from pay_element_entries_f ENTRY2
where entry.element_entry_id=entry2.target_entry_id
and entry.assignment_id = entry2.assignment_id)
--
and not exists (
--
-- Do not select reversal entries
--
select 1
from pay_element_links_f LINK,
pay_element_types_f TYPE
where link.element_link_id = entry.element_link_id
and entry.effective_start_date between link.effective_start_date and link.effective_end_date
and link.element_type_id = type.element_type_id
and link.effective_start_date between type.effective_start_date and type.effective_end_date
and type.element_name = c_SAP_Corr_element_name);
select stoppage_id
from ssp_stoppages
where user_entered <>'Y'
and override_stoppage <> 'Y'
and maternity_id = p_maternity_id;
select 1
from per_periods_of_service
where person_id = person.person_id
and date_start <= ssp_sap_pkg.continuous_employment_date
(person.matching_date)
and nvl (actual_termination_date, hr_general.end_of_time)
>= person.MW;
select ppf.date_of_death
from per_all_people_f ppf
where ppf.person_id = person.person_id
and ppf.date_of_death is not null;
select to_char(person.app_start_date, 'DAY')
from dual;
select *
from ssp_medicals
where maternity_id = woman.maternity_id
and evidence_status = 'CURRENT';
-- create the entry as planned; if there is, then we must update the
select entry.element_entry_id,
entry.element_link_id,
entry.assignment_id,
entry.effective_start_date,
entry.effective_end_date,
-- if in future we get two different rates then a decode can be added here
l_high_rate RATE,
--BUG 6271407 begin
/* to_date (ssp_smp_support_pkg.value
(entry.element_entry_id,
ssp_sap_pkg.c_week_commencing_name),
'DD-MON-YYYY') WEEK_COMMENCING,*/
--BUG 7563477 begin
/* fnd_date.canonical_to_date(ssp_smp_support_pkg.value
(entry.element_entry_id,
ssp_sap_pkg.c_week_commencing_name)) WEEK_COMMENCING,*/
fnd_date.chardate_to_date(ssp_smp_support_pkg.value
(entry.element_entry_id,
ssp_sap_pkg.c_week_commencing_name)) WEEK_COMMENCING,
--BUG 7563477 end
--BUG 6271407 end
to_number(ssp_smp_support_pkg.value (entry.element_entry_id,
ssp_sap_pkg.c_amount_name)) AMOUNT,
to_number(ssp_smp_support_pkg.value (entry.element_entry_id,
ssp_sap_pkg.c_recoverable_amount_name)) RECOVERABLE_AMOUNT
from pay_element_entries_f ENTRY,
per_all_assignments_f asg
where creator_type = c_SAP_creator_type
and creator_id = p_maternity_id
and asg.person_id = person.person_id
and asg.assignment_id = entry.assignment_id
and entry.effective_start_date between asg.effective_start_date
and asg.effective_end_date
and not exists (
--
-- Do not select entries which have already had reversal action taken
-- against them because they are effectively cancelled out.
--
select 1
from pay_element_entries_f ENTRY2
where entry.element_entry_id= entry2.target_entry_id
and entry.assignment_id = entry2.assignment_id)
--
and not exists (
--
-- Do not select reversal entries
--
select 1
from pay_element_links_f LINK,
pay_element_types_f TYPE
where link.element_link_id = entry.element_link_id
and entry.effective_start_date between link.effective_start_date
and link.effective_end_date
and link.element_type_id = type.element_type_id
and link.effective_start_date between type.effective_start_date
and type.effective_end_date
and type.element_name = c_SAP_Corr_element_name);
select count (*)
from per_absence_attendances
where person_id = person.person_id
and maternity_id = p_maternity_id;
select stoppage_id
from ssp_stoppages
where user_entered <>'Y'
and override_stoppage <> 'Y'
and maternity_id = p_maternity_id;
and ssp_smp_pkg.g_smp_update = 'N')
or (old_entry.effective_start_date
= hypothetical_entry.effective_start_date (entry_number)
and old_entry.week_commencing
= hypothetical_entry.week_commencing (entry_number)
and not hypothetical_entry.stopped (entry_number) = 'TRUE'
and ssp_smp_pkg.g_smp_update = 'Y'));
hr_utility.trace (l_proc||' unprocessed - update it');
hr_entry_api.update_element_entry (
p_dt_update_mode => 'CORRECTION',
p_session_date => old_entry.effective_start_date,
p_element_entry_id => old_entry.element_entry_id,
p_input_value_id1 => g_SAP_element.rate_id,
p_input_value_id2 => g_SAP_element.amount_id,
p_input_value_id3 => g_SAP_element.recoverable_amount_id,
p_entry_value1=> hypothetical_entry.rate (entry_number),
p_entry_value2=> hypothetical_entry.amount(entry_number),
p_entry_value3=>
hypothetical_entry.recoverable_amount (entry_number));
hr_entry_api.delete_element_entry (
p_dt_delete_mode => 'ZAP',
p_session_date => old_entry.effective_start_date,
p_element_entry_id => old_entry.element_entry_id);
' Inserting CORRECTION entry for week commencing ' ||
to_char (old_entry.week_commencing));
hr_entry_api.insert_element_entry (
p_effective_start_date=> old_entry.effective_start_date,
p_effective_end_date => old_entry.effective_end_date,
p_element_entry_id => l_dummy,
p_target_entry_id => old_entry.element_entry_id,
p_assignment_id => old_entry.assignment_id,
p_element_link_id => old_entry.element_link_id,
p_creator_type => c_SAP_creator_type,
p_creator_id => p_maternity_id,
p_entry_type => c_SAP_entry_type,
p_input_value_id1=> g_SAP_correction_element.rate_id,
p_input_value_id2=> g_SAP_correction_element.week_commencing_id,
p_input_value_id3=> g_SAP_correction_element.amount_id,
p_input_value_id4=> g_SAP_correction_element.recoverable_amount_id,
p_entry_value1=> old_entry.rate,
-- p_entry_value2=> old_entry.week_commencing,
p_entry_value2 => to_char(old_entry.week_commencing,'DD-MON-YYYY'),
p_entry_value3=> old_entry.amount * -1,
p_entry_value4=> old_entry.recoverable_amount * -1);
hr_utility.trace('Deleting an absence so don''t insert entries');
hr_entry_api.insert_element_entry (
p_effective_start_date =>
hypothetical_entry.effective_start_date (new_entry),
p_effective_end_date =>
hypothetical_entry.effective_end_date (new_entry),
p_element_entry_id => l_dummy,
p_assignment_id => hypothetical_entry.assignment_id (new_entry),
p_element_link_id => hypothetical_entry.element_link_id (new_entry),
p_creator_type => c_SAP_creator_type,
p_creator_id => p_maternity_id,
p_entry_type => c_SAP_entry_type,
p_input_value_id1 => g_SAP_element.rate_id,
p_input_value_id2 => g_SAP_element.week_commencing_id,
p_input_value_id3 => g_SAP_element.amount_id,
p_input_value_id4 => g_SAP_element.recoverable_amount_id,
p_entry_value1 => hypothetical_entry.rate (new_entry),
-- p_entry_value2 => hypothetical_entry.week_commencing (new_entry),
p_entry_value2 => to_char(hypothetical_entry.week_commencing(new_entry),'DD-MON-YYYY'),
p_entry_value3 => hypothetical_entry.amount (new_entry),
p_entry_value4 =>
hypothetical_entry.recoverable_amount (new_entry));
select 1
from ssp_stoppages STP,
ssp_withholding_reasons WRE
where stp.override_stoppage <> 'Y'
--
-- and the stoppage ovelaps the period or the stoppage is for
-- death and is prior to the period
--
and ((wre.reason <> employee_died
and stp.withhold_from <= p_end_date
and nvl (stp.withhold_to, hr_general.end_of_time)
>= p_start_date)
--
or (wre.reason = employee_died
and stp.withhold_from <= p_end_date))
--
and stp.maternity_id = p_maternity_id
and stp.reason_id = wre.reason_id;
select meaning
from hr_lookups
where lookup_type = 'SAP_RATES'
and lookup_code = p_rate_band;
select 1
from ssp_maternities
where maternity_id = p_maternity_id;
select /*+ ORDERED use_nl(paa,paaf,etype,entry) */
entry.element_entry_id,
entry.effective_start_date
from per_absence_attendances PAA,
per_all_assignments_f PAAF,
pay_element_entries_f entry
where PAA.maternity_id = p_maternity_id
and PAAF.person_id = PAA.person_id
and entry.creator_type = 'M'
and entry.creator_id = p_maternity_id
and entry.assignment_id = paaf.assignment_id;
select count(*)
from ssp_maternities mat,
per_absence_attendances ab
where mat.maternity_id = p_maternity_id
and ab.person_id = mat.person_id
and ab.maternity_id = mat.maternity_id;
hr_entry_api.delete_element_entry (
p_dt_delete_mode => 'ZAP',
p_session_date => obsolete.effective_start_date,
p_element_entry_id => obsolete.element_entry_id);
select mpp_start_date, due_date
from ssp_maternities
where maternity_id = p_maternity_id;
select to_number(element_information3)
from pay_element_types_f
where element_name = c_SAP_element_name
and l_due_date between effective_start_date and effective_end_date;