PAY RTI - Employer Payment Summary Output (EPS) File

             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