[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select sum(actinfo.action_information12) total_pay
from pay_action_information actinfo
, pay_assignment_actions aa
, pay_payroll_actions pa
where actinfo.action_information_category = 'FR_SOE_EE_TOTALS'
and actinfo.action_context_id = aa.assignment_action_id
and aa.assignment_id = p_assignment_id
and aa.payroll_action_id = pa.payroll_action_id
and pa.effective_date
between trunc(p_last_day_paid,'MM') and last_day(p_last_day_paid)
and pa.payroll_action_id =
(select max(pa1.payroll_action_id)
from pay_payroll_actions pa1
, pay_assignment_actions aa1
, pay_action_information actinfo1
where actinfo1.action_information_category = 'FR_SOE_EE_TOTALS'
and actinfo1.action_context_id = aa.assignment_action_id
and aa1.assignment_id = p_assignment_id
and aa1.payroll_action_id = pa1.payroll_action_id
and pa1.effective_date
between trunc(p_last_day_paid,'MM') and last_day(p_last_day_paid)
)
;
Select a.assignment_id assignment_id
, p.first_name first_name
, p.last_name last_name
, estorg.name establishment_name
, estinfo.org_information2 SIRET
, estinfo.org_information3 NAF
, comporg.name company_name
--
-- Employee Address
--
, addr.address_line1 addr_road
, addr.address_line2 addr_complement
, addr.region_3 addr_small_town
, addr.postal_code addr_postal_code
, addr.town_or_city addr_town_or_city
--
-- Company Address
--
, comploc.address_line_1 compaddr_road
, comploc.address_line_2 compaddr_complement
, comploc.region_3 compaddr_small_town
, comploc.postal_code compaddr_postal_code
, comploc.town_or_city compaddr_town_or_city
--
-- Establishment Address
--
, estloc.address_line_1 estaddr_road
, estloc.address_line_2 estaddr_complement
, estloc.region_3 estaddr_small_town
, estloc.postal_code estaddr_postal_code
, estloc.town_or_city estaddr_town_or_city
--
--
--
, decode(pds.pds_information11,'LAST_DAY_WORKED'
, fnd_date.canonical_to_date(pds.pds_information10)
, pds.actual_termination_date) last_day_paid
from per_all_people_f p
, per_all_assignments_f a
, per_periods_of_service pds
, hr_organization_information estinfo
, hr_all_organization_units estorg
, hr_all_organization_units comporg
, per_addresses addr
, hr_locations comploc
, hr_locations estloc
where (l_assignment_id is not null and
a.assignment_id = l_assignment_id)
--
-- Ensure that the last day paid is between the start and end dates
-- entered as parameters
-- N.B. Last Day Paid is taken as Last Day Worked if Final Payment Schedule
-- is LAST_DAY_WORKED, otherwise Actual Termination Date.
--
and decode(pds.pds_information11,'LAST_DAY_WORKED'
, fnd_date.canonical_to_date(pds.pds_information10)
, pds.actual_termination_date)
between p_start_date and p_end_date
--
-- Use the Last Day Paid (Last Day Worked or ATD) to determine the date
-- effectivity of the Person and Assignment records
--
and decode(pds.pds_information11,'LAST_DAY_WORKED'
, fnd_date.canonical_to_date(pds.pds_information10)
, pds.actual_termination_date)
between p.effective_start_date and p.effective_end_date
--
and p.person_id = a.person_id
and decode(pds.pds_information11,'LAST_DAY_WORKED'
, fnd_date.canonical_to_date(pds.pds_information10)
, pds.actual_termination_date)
between a.effective_start_date and a.effective_end_date
and a.period_of_service_id = pds.period_of_service_id
--
-- Get Establishment Details
--
and a.establishment_id = estorg.organization_id
and estinfo.org_information_context = 'FR_ESTAB_INFO'
and estorg.organization_id = estinfo.organization_id
--
-- Get Company Details
--
and to_number(estinfo.org_information1) = comporg.organization_id
--
-- Get Person Address Details
--
and p.person_id = addr.person_id(+)
and addr.primary_flag(+) = 'Y'
--
-- Get Company Address Details
--
and comporg.location_id = comploc.location_id(+)
--
-- Get Establishment Address Details
--
and estorg.location_id = estloc.location_id(+)
order by p.last_name;
Select sum(actinfo.action_information12) total_pay
from pay_action_information actinfo
, pay_assignment_actions aa
, pay_payroll_actions pa
where actinfo.action_information_category = 'FR_SOE_EE_TOTALS'
and actinfo.action_context_id = aa.assignment_action_id
and aa.assignment_id = l_assignment_id
and aa.payroll_action_id = pa.payroll_action_id
and pa.effective_date
between trunc(p_last_day_paid,'MM') and last_day(p_last_day_paid)
and pa.effective_date =
(select max(pa1.effective_date)
from pay_payroll_actions pa1
, pay_assignment_actions aa1
where aa1.assignment_id = l_assignment_id
and aa1.payroll_action_id = pa1.payroll_action_id
and pa1.effective_date
between trunc(p_last_day_paid,'MM') and last_day(p_last_day_paid)
);
select amend.assignment_id
from hr_assignment_set_amendments amend
where amend.assignment_set_id = p_assignment_set_id
and amend.include_or_exclude = 'I';