The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(upper(SUBSTR(trim(hoi.org_information3),1,35)),' ') l_EMPLOYERS_NAME,
NVL(upper(SUBSTR(trim(hoi.org_information7),1,35)),' ') l_econ_number,
NVL(upper(SUBSTR(hoi.org_information6,1,35)),' ') l_acc_ref_no,
NVL(substr(TRIM(HOI.ORG_INFORMATION19),1,1),'N') L_SERVICE_PROVIDER,
NVL(TRIM(HOI.ORG_INFORMATION16),'Partial') L_SMP_RECOVERY,
NVL(TRIM(HOI.ORG_INFORMATION17),'None') L_SSP_RECOVERY,
TRIM(HOI.ORG_INFORMATION18) L_APPROVER_ID
FROM hr_organization_information hoi
WHERE hoi.organization_id = g_business_group_id
AND hoi.org_information_context = 'Tax Details References'
AND NVL(HOI.ORG_INFORMATION10,'UK') = 'UK'
AND hoi.org_information1 = g_paye_reference;
SELECT MAX(DECODE(GLOBAL_NAME,P_GLOBAL_NAME1,GLOBAL_VALUE,NULL)) L_SMP_SAP_RATE,
MAX(DECODE(GLOBAL_NAME,p_global_name2,GLOBAL_VALUE,NULL)) l_ssp_rate,
MAX(DECODE(GLOBAL_NAME,p_global_name3,GLOBAL_VALUE,NULL)) l_nic_rate
FROM FF_GLOBALS_F
WHERE GLOBAL_NAME IN (p_global_name1, p_global_name2,p_global_name3)
AND L_EFF_START_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
SELECT defined_balance_id
FROM pay_defined_balances db,
pay_balance_types b,
pay_balance_dimensions d
WHERE b.balance_name = p_balance_name
AND d.dimension_name = p_dimension_name
AND db.balance_type_id = b.balance_type_id
AND db.balance_dimension_id = d.balance_dimension_id;
SELECT 1
FROM PAY_GB_EPS_DETAILS
WHERE EFFECTIVE_DATE = l_eff_start_date
AND EMP_PAYE_REFERENCE = p_paye_reference
AND BUSINESS_GROUP_ID = p_business_group_id;
SELECT
/*+ USE_NL(paa, pact, ptp) */
to_number(SUBSTR(MAX(lpad(paa.action_sequence,15,'0')
|| paa.assignment_action_id),16)) lat_assignment_action_id,
MAX(pact.effective_date) effective_date,
paa.assignment_id
FROM pay_assignment_actions paa,
pay_payroll_actions pact
WHERE paa.payroll_action_id = pact.payroll_action_id
AND pact.action_type IN ('Q','R','B','I','V')
AND paa.action_status IN ('C','S')
AND pact.effective_date BETWEEN l_date_soy AND l_eff_start_date
AND pact.payroll_id IN
(
-- Effective date, Business_group_id, PAYE reference, tax_year_start and Tax_year_end
-- To fetch all payrolls belonging to this PAYE reference.
SELECT DISTINCT papf.payroll_id
FROM pay_all_payrolls_f papf,
hr_soft_coding_keyflex flex,
per_time_periods ptp
WHERE papf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND flex.segment1 = p_paye_reference
AND flex.segment10 IS NOT NULL
AND papf.business_group_id = p_business_group_id
AND ptp.payroll_id = papf.payroll_id
AND l_eff_start_date BETWEEN papf.effective_start_date AND papf.effective_end_date
AND ptp.regular_payment_date BETWEEN l_date_soy AND l_date_eoy
)
GROUP BY assignment_id;
select PROGRAM_APPLICATION_ID,CONCURRENT_PROGRAM_ID,LAST_UPDATE_DATE from fnd_concurrent_requests
where request_id = FND_GLOBAL.CONC_REQUEST_ID;
select EMPLOYEE_ID INTO L_CREATER_ID FRom FND_USER
WHERE USER_ID = FND_GLOBAL.USER_ID;
pay_gb_eps.INSERT_EPS_RECORD ( L_EFF_START_DATE, P_PAYE_REFERENCE, G_ACC_REF_NO, p_business_group_id, G_EMPLOYERS_NAME, G_ECON_NUMBER, G_RELATED_TAX_YEAR, G_SMP_SAP_RATE, G_SSP_RATE, G_NIC_RATE, G_SERVICE_PROVIDER,
l_smp/100, l_sap/100, l_spp/100, l_aspp/100, l_ssp/100, L_NIC_SMP/100, l_nic_sap/100, l_nic_spp/100, l_nic_aspp/100, L_NIC_SSP/100, 'Saved', L_CREATER_ID, G_APPROVER_ID ,
FND_GLOBAL.CONC_REQUEST_ID,l_request_rec.PROGRAM_APPLICATION_ID,l_request_rec.CONCURRENT_PROGRAM_ID,l_request_rec.LAST_UPDATE_DATE);
HR_UTILITY.TRACE(' EPS record insert complete ');
SELECT defined_balance_id
FROM pay_defined_balances db,
pay_balance_types b,
pay_balance_dimensions d
WHERE b.balance_name = p_balance_name
AND d.dimension_name = p_dimension_name
AND db.balance_type_id = b.balance_type_id
AND db.balance_dimension_id = d.balance_dimension_id
AND db.legislation_code = 'GB'
AND b.legislation_code = 'GB'
AND d.legislation_code = 'GB';
SELECT NVL(TRIM(HOI.ORG_INFORMATION16),'None') L_SMP_RECOVERY
FROM hr_organization_information hoi
WHERE hoi.organization_id = g_business_group_id
AND hoi.org_information_context = 'Tax Details References'
AND NVL(HOI.ORG_INFORMATION10,'UK') = 'UK'
AND hoi.org_information1 = g_paye_reference;
select effective_date into l_eff_date from pay_assignment_actions paa, pay_payroll_actions ppa where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = p_assignment_action_id;
select business_group_id, payroll_id into g_business_group_id , l_payroll_id
from pay_payroll_Actions ppa,
pay_assignment_Actions paa
where paa.PAYROLL_ACTION_ID = ppa.payroll_Action_id
and paa.assignment_action_id = p_assignment_action_id;
select SEGMENT1 into g_paye_reference from hr_soft_coding_keyflex hr , pay_payrolls_f p
where hr.SOFT_CODING_KEYFLEX_ID = p.SOFT_CODING_KEYFLEX_ID
and p.payroll_id = l_payroll_id
and l_eff_date between effective_start_date and effective_end_date;
select global_value into l_rec_rate from ff_globals_f where global_name = 'SMP_RECOVERY_PERCENTAGE' and legislation_code = 'GB' and
l_eff_date between effective_start_date and effective_end_date;
select global_value into l_nic_rate from ff_globals_f where global_name = 'NIC_COMPENSATION_RATE_ON_SMP' and legislation_code = 'GB' and
l_eff_date between effective_start_date and effective_end_date;
select element_name into l_element_name from pay_element_types_f where element_type_id = p_element_type_id
and legislation_code = 'GB' and
l_eff_date between effective_start_date and effective_end_date;