[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_sqlstr := 'SELECT distinct asg.person_id
FROM per_periods_of_service pos,
per_assignments_f asg,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND pos.person_id = asg.person_id
AND pos.period_of_service_id = asg.period_of_service_id
AND pos.business_group_id = ppa.business_group_id
AND asg.business_group_id = ppa.business_group_id
ORDER BY asg.person_id';
SELECT act.assignment_action_id,
act.assignment_id,
ppp.pre_payment_id
FROM pay_assignment_actions act,
per_all_assignments_f asg,
pay_payroll_actions pa2,
pay_payroll_actions pa1,
pay_pre_payments ppp,
pay_org_payment_methods_f OPM,
pay_payment_types PPT,
per_all_people_f pap
WHERE pa1.payroll_action_id = p_payroll_action_id
AND pa2.consolidation_set_id = p_consolidation_id
AND pa2.payroll_id = NVL(p_payroll_id,pa2.payroll_id)
AND pa2.effective_date <= pa1.effective_date
AND pa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
AND act.payroll_action_id = pa2.payroll_action_id
AND act.action_status IN ('C','S') --10225372
AND asg.assignment_id = act.assignment_id
AND pa1.business_group_id = asg.business_group_id
AND pa1.effective_date between asg.effective_start_date and asg.effective_end_date
AND pa1.effective_date between pap.effective_start_date and pap.effective_end_date
AND pap.person_id = asg.person_id
AND pap.person_id between p_start_person_id and p_end_person_id
AND ppp.assignment_action_id = act.assignment_action_id
AND ppp.org_payment_method_id = opm.org_payment_method_id
AND opm.payment_type_id = ppt.payment_type_id
AND ppt.territory_code = 'IE'
AND ppt.payment_type_name = 'SEPA'
AND pap.person_id = NVL(p_person_id,pap.person_id)
AND (p_assignment_set_id IS NULL
OR EXISTS ( SELECT ''
FROM hr_assignment_set_amendments hr_asg
WHERE hr_asg.assignment_set_id = p_assignment_set_id
AND hr_asg.assignment_id = asg.assignment_id
))
AND NOT EXISTS (SELECT /*+ ORDERED */ NULL
FROM pay_action_interlocks pai1,
pay_assignment_actions act2,
pay_payroll_actions appa
WHERE pai1.locked_action_id = act.assignment_action_id
AND act2.assignment_action_id = pai1.locking_action_id
AND act2.payroll_action_id = appa.payroll_action_id
AND appa.action_type = 'X'
AND appa.report_type = 'SEPA');
SELECT pay_assignment_actions_s.nextval
INTO l_locking_action_id
FROM dual;
SELECT hlc.address_line_1 address_1
,hlc.address_line_2 address_2
,hlc.address_line_3 address_3
,hlc.country country
,hlc.postal_code postcode
,hlc.region_2 geo_code
FROM hr_locations hlc
,hr_organization_units hou
WHERE hou.business_group_id = p_bg_id
AND hou.organization_id = p_org_id
AND hlc.location_id = hou.location_id;
SELECT payee_type,payee_id
FROM pay_personal_payment_methods_f ppm
WHERE ppm.assignment_id = p_assignment_id
AND ppm.personal_payment_method_id = p_per_pay_method_id
AND p_date_earned BETWEEN ppm.effective_start_date AND ppm.effective_end_date
AND ppm.business_group_id = p_business_group_id
AND ppm.payee_id is NOT NULL;
SELECT pad.address_line1 address_line1
,pad.address_line2 address_line2
,pad.address_line3 address_line3
,pad.country country
,pad.postal_code postcode
,pad.region_2 geo_code
FROM per_addresses pad
WHERE pad.person_id = p_payee_id
AND pad.primary_flag = 'Y'
AND p_effective_date BETWEEN pad.date_from
AND nvl(pad.date_to,to_date('31/12/4712','DD/MM/YYYY'));
SELECT hlc.address_line_1 address_1
,hlc.address_line_2 address_2
,hlc.address_line_3 address_3
,hlc.country country
,hlc.postal_code postcode
,hlc.region_2 geo_code
FROM hr_locations hlc
,hr_organization_units hou
WHERE hou.business_group_id = p_bg_id
AND hou.organization_id = p_payee_id
AND hlc.location_id = hou.location_id;
SELECT person_id
INTO l_payee_id
FROM per_all_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND p_date_earned BETWEEN paf.effective_start_date AND paf.effective_end_date;
SELECT hou.organization_id
,pea.external_account_id
,NVL(pea.SEGMENT10,' ')
,NVL(pea.SEGMENT9,' ')
,hoi.org_information4
from hr_all_organization_units hou,
hr_organization_information hoi,
pay_org_payment_methods_f popmf,
PAY_ORG_PAY_METHOD_USAGES_F popmu,
PAY_ALL_PAYROLLS_F papf,
PAY_EXTERNAL_ACCOUNTS pea,
PAY_PAYROLL_ACTIONS ppa,
hr_soft_coding_keyflex hsck
where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
and hou.business_group_id = ppa.business_group_id
and hoi.org_information_context(+) = 'IE_PAYPATH_INFORMATION'
and hou.organization_id = hoi.organization_id
and popmu.org_payment_method_id = ppa.org_payment_method_id
and ppa.effective_date between popmu.effective_start_date and popmu.effective_end_date
and popmf.org_payment_method_id = popmu.org_payment_method_id
and popmf.business_group_id = hou.business_group_id
and ppa.effective_date between popmf.effective_start_date and popmf.effective_end_date
and papf.payroll_id = NVL(ppa.payroll_id,papf.payroll_id)
and papf.payroll_id = popmu.payroll_id
and papf.business_group_id = ppa.business_group_id
and papf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
and hsck.segment4 = hou.organization_id
and ppa.effective_date between papf.effective_start_date and papf.effective_end_date
and pea.external_account_id = popmf.external_account_id and
--restricting employer who doesn't have any assignment to pay.
EXISTS
(SELECT 1
FROM pay_assignment_actions pas
,pay_pre_payments ppp
,per_all_assignments_f paaf
,pay_all_payrolls_f ppf
,hr_soft_coding_keyflex hsck1
WHERE pas.payroll_action_id = ppa.payroll_action_id
AND ppp.pre_payment_id = pas.pre_payment_id
AND ppp.value > 0
AND paaf.assignment_id = pas.assignment_id
AND ppf.payroll_id = paaf.payroll_id
and ppf.soft_coding_keyflex_id = hsck1.soft_coding_keyflex_id
and hsck1.segment4 = hou.organization_id
AND ppa.effective_date between paaf.effective_start_date and paaf.effective_end_date
AND ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date
)
Group by hou.organization_id
,pea.external_account_id
,NVL(pea.SEGMENT10,' ')
,NVL(pea.SEGMENT9,' ')
,hoi.org_information4; */
SELECT SUM(employer_sum) er_sum,
SUM(record_count) er_count
FROM (SELECT SUM(ppp.value*100) employer_sum,
1 record_count
FROM per_all_assignments_f paf
,per_all_people_f pef
,pay_all_payrolls_f ppf
,pay_pre_payments ppp
,pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_personal_payment_methods_f ppmf
,pay_external_accounts pea
WHERE paa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
AND paa.pre_payment_id = ppp.pre_payment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND PPP.personal_payment_method_id = ppmf.personal_payment_method_id
AND paa.assignment_id = paf.assignment_id
AND paf.payroll_id = NVL(ppa.payroll_id,paf.payroll_id)
AND paf.person_id = pef.person_id
AND ppf.payroll_id = paf.payroll_id
AND ppa.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
--AND ppf.prl_information_category = 'NL'
--AND ppf.prl_information1 = p_organization_id
--and ppf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
--and hsck.segment4 = p_organization_id
AND ppp.value > 0
AND pea.external_account_id = ppmf.external_account_id
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppa.effective_date BETWEEN pef.effective_start_date
AND pef.effective_end_date
AND ppa.effective_date BETWEEN ppmf.effective_start_date
AND ppmf.effective_end_date
GROUP BY pef.person_id,
pea.SEGMENT7,
pea.SEGMENT6,
ppmf.ORG_PAYMENT_METHOD_ID
);
SELECT MEANING
FROM
FND_LOOKUP_VALUES
where
LOOKUP_TYPE = 'HR_IE_BIC_CODES'
and ENABLED_FLAG = 'Y'
and LOOKUP_CODE = p_bic_code
and LANGUAGE = 'US';