[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
hlc.loc_information14 house_number
,hlc.loc_information15 house_number_add
,hlc.address_line_1 address_1
,hlc.address_line_2 address_2
,hlc.address_line_3 address_3
,hlc.region_1 street_name
,hlc.postal_code postcode
,hr_general.decode_lookup('HR_NL_CITY',hlc.town_or_city) city
,hlc.country country
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 count(*) from
hr_All_organization_units hou,
hr_organization_information hoi
where hou.organization_id = p_org_id
and hoi.organization_id = hou.organization_id
and hoi.org_information_context = 'CLASS'
and hoi.org_information1 = 'NL_PAYEE_OVERRIDE'
and hoi.org_information2='Y';
SELECT substr(style,1,35) style
FROM per_addresses pas
WHERE pas.person_id = p_payee_id
AND pas.primary_flag = 'Y'
AND p_effective_date BETWEEN pas.date_from
AND nvl(pas.date_to,to_date('31/12/4712','DD/MM/YYYY'));
SELECT pad.add_information13 house_number
,pad.add_information14 house_no_add
,pad.region_1 street_name
,pad.address_line1 address_line1
,pad.address_line2 address_line2
,pad.address_line3 address_line3
,pad.postal_code postcode
,hr_general.decode_lookup('HR_NL_CITY',pad.town_or_city) city
,pad.country country
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.loc_information14 house_number
,hlc.loc_information15 house_number_add
,hlc.address_line_1 address_1
,hlc.address_line_2 address_2
,hlc.address_line_3 address_3
,hlc.region_1 street_name
,hlc.postal_code postcode
,hr_general.decode_lookup('HR_NL_CITY',hlc.town_or_city) city
,hlc.country country
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 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 'Y'
FROM ff_formulas_f ff
,ff_compiled_info_f ffci
WHERE ff.formula_id = ffci.formula_id
AND ff.effective_start_date = ffci.effective_start_date
AND ff.effective_end_date = ffci.effective_end_date
AND ff.formula_id = p_formula_id
AND ff.business_group_id = p_business_group_id
AND p_effective_date BETWEEN ff.effective_start_date
AND ff.effective_end_date;
SELECT ff.formula_id
FROM ff_formulas_f ff
WHERE ff.formula_name = p_formula_name
AND ff.business_group_id = p_business_group_id
AND p_effective_date BETWEEN ff.effective_start_date
AND ff.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
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 = 'NL_ORG_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.prl_information_category = 'NL' and
papf.prl_information1 = 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
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.prl_information1 = papf.prl_information1
AND ppf.prl_information_category = 'NL'
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 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.SEGMENT10, pea.SEGMENT9
);