select
nvl(UPPER(hoi.org_information11),' ') SENDER_ID,
:P_TEST_SUBMISSION TEST_INDICATOR,
:P_REQUEST_ID REQUEST_ID,
lpad(:P_TAX_MONTH,2,'0') TAX_MONTH,
decode(PAY_GB_RTI_UTIL.get_payroll_version('EPS'), ' ', '0', PAY_GB_RTI_UTIL.get_payroll_version('EPS')) PAYROLL_PROD_VER,
employer_name EMPLOYER_NAME,
nvl(upper(substr(ltrim(substr(pged.emp_paye_reference,4,11),'/'),1,10)),' ') EMPLOYER_PAYE_REF,
(lpad(substr(pged.account_office_number,0,instr(pged.account_office_number,'P')-1),3,0)
|| 'P'
|| substr(pged.account_office_number,instr(pged.account_office_number,'P')+1,1)
|| lpad(substr(pged.account_office_number,instr(pged.account_office_number,'P')+2,
length(pged.account_office_number)-3-(instr(pged.account_office_number,'P')-1)),7,0)
|| substr(pged.account_office_number,length(pged.account_office_number),1)
) EMPLOYER_AO_REF,
lpad(substr(pged.emp_paye_reference,0,instr(pged.emp_paye_reference,'/')-1),3,0) HMRC_OFFICE_NO,
related_tax_year TAX_YEAR,
decode(related_tax_year, '2015', 'EPS15' ,'2016', 'EPS16' , '2017', 'EPS17') EPS_YEAR,
payment_due NO_PAYMENT_PERIOD,
nvl(adv_from_hmrc,0)*100 ADV_FROM_HMRC,
(smp_amt_recovered_calc)*100 SMP,
(ospp_amt_recovered_calc)*100 OSPP,
(sap_amt_recovered_calc)*100 SAP,
(aspp_amt_recovered_calc)*100 ASPP,
(nic_comp_smp_calc)*100 NIC_SMP,
(nic_comp_ospp_calc)*100 NIC_OSPP,
(nic_comp_sap_calc)*100 NIC_SAP,
(nic_comp_aspp_calc)*100 NIC_ASPP,
nvl(cis_deductions_stuff,0)*100 CIS_DEDUCTIONS,
final_submission_ceased FINAL_SUB_CEASED,
final_submission_year FINAL_SUB_YEAR,
econ_number ECON,
tax_free_pymt FREE_TAX_PAY,
exp_ben_others EXP_BENEFIT,
emp_outside_uk EMP_OUTSIDE_UK,
emp_third_party EMP_THIRD_PARTY,
p11d_due P11D_DUE,
service_company SERVICE_COMPANY,
to_char(no_payment_date_from,'YYYYMMDD') NO_PAY_DATE_FROM,
to_char(no_payment_date_to,'YYYYMMDD') NO_PAY_DATE_TO,
to_char(period_of_inactivity_from,'YYYYMMDD') PERIOD_OF_INACTIVITY_FROM,
to_char(period_of_inactivity_to,'YYYYMMDD') PERIOD_OF_INACTIVITY_TO,
to_char(date_scheme_ceased,'YYYYMMDD') DATE_SCHEME_CEASED,
employment_allow_ind EMP_ALLOW_IND,
account_holder_name ACC_HOLDER_NAME,
account_number ACC_NUMBER,
branch_sort_code SORT_CODE,
building_society_ref BLD_SOC_REF,
substr(report_additional_dtls,10,1) REPORT_EMP_ALLW_FLAG,
substr(report_additional_dtls,20,1) REPORT_ACC_DET_FLAG
from pay_gb_eps_details pged,hr_organization_information hoi
where emp_paye_reference = :P_PAYE_REFERENCE
and effective_date = :P_EFFECTIVE_DATE
and business_group_id = :P_BUSINESS_GROUP_ID
and eps_record_id = :P_EPS_RECORD_ID
and hoi.org_information_context = 'Tax Details References'
and nvl(hoi.org_information10,'UK') = 'UK'
and hoi.org_information1 = :P_PAYE_REFERENCE
and hoi.ORGANIZATION_ID = :P_BUSINESS_GROUP_ID |
