select
nvl(UPPER(hoi.org_information11),' ') SENDER_ID,
:P_TEST_SUBMISSION TEST_INDICATOR,
:P_REQUEST_ID REQUEST_ID,
decode(PAY_GB_EOY_MAGTAPE.get_payroll_version, ' ', '0', PAY_GB_EOY_MAGTAPE.get_payroll_version) 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,
payment_due NO_PAYMENT_PERIOD,
nvl(adv_from_hmrc,0)*100 ADV_FROM_HMRC,
(ssp_amt_recovered_calc)*100 SSP,
(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,
nvl(nic_holiday_val,0)*100 NIC_HOLIDAY,
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
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 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 |
