[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(distinct asg.assignment_id)
into l_cnt_emps
FROM per_all_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_periods_of_service pos
,per_all_people_f ppf
WHERE asg.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND pos.period_of_service_id = asg.period_of_service_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND trunc(ppa.date_earned,'MM') = trunc(last_day(to_date('01-' || p_month || '-' ||p_year,'DD-MM-YYYY')), 'MM')
AND trunc(last_day(to_date('01-' || p_month || '-' || p_year,'DD-MM-YYYY')),'MM') between asg.effective_start_date and asg.effective_end_date
AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND hscl.segment1 = to_char(p_employer_id)
AND ppf.person_id = asg.person_id
AND last_day(to_date('01-' || p_month || '-' ||p_year,'DD-MM-YYYY')) between ppf.effective_start_date and ppf.effective_end_date
AND ppf.per_information18 = (SELECT org_information1
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND org_information_context = 'AE_BG_DETAILS');
select count(distinct asg.assignment_id), NVL(sum(ppv.value),0)
into l_cnt_emp_paid, l_amt_paid
from per_all_assignments_f asg,
pay_assignment_actions paa,
pay_payroll_actions ppa,
PAY_PRE_PAYMENTS_V ppv,
hr_soft_coding_keyflex hscl,
per_periods_of_service pos,
per_all_people_f ppf
where asg.assignment_id = paa.assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and pos.period_of_service_id = asg.period_of_service_id
and paa.action_status = 'C'
and ppa.action_type in ('P','U')
and ppa.action_status = 'C'
and trunc(ppa.date_earned,'MM') = trunc(last_day(to_date('01-' || p_month || '-' || p_year,'DD-MM-YYYY')), 'MM')
and trunc(last_day(to_date('01-' || p_month || '-' ||p_year,'DD-MM-YYYY')),'MM') between asg.effective_start_date and asg.effective_end_date
and hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
and hscl.segment1 = to_char(p_employer_id)
and ppf.person_id = asg.person_id
and last_day(to_date('01-' || p_month || '-' ||p_year,'DD-MM-YYYY')) between ppf.effective_start_date and ppf.effective_end_date
and ppf.per_information18 = (SELECT org_information1
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND org_information_context = 'AE_BG_DETAILS')
and paa.assignment_action_id = ppv.prepay_action_id
and ppv.business_group_id = p_business_group_id
and ppv.value > 0
and 'Paid' =ALL (select ppv1.status
from PAY_PRE_PAYMENTS_V ppv1
where ppv1.prepay_action_id = paa.assignment_action_id);