Show pageOld revisionsBacklinksExport to PDFFold/unfold allBack to top This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong. ====== pears.NetITSP60 ====== <WRAP center round info> Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace. </WRAP> ===== Original SQL ===== <code sql> create procedure "pears"."NetITSP60"( in "pWebUserID" char(20),in "pPersonID" char(20) ) result( "itsp60id" varchar(20), "personid" varchar(20), "surname" varchar(50), "forename" varchar(50), "ni" char(9), "payrollnumber" varchar(40), "PreviousEmploymentPay" decimal(10,2), "PreviousEmploymentTax" decimal(10,2), "ThisEmploymentPay" decimal(10,2), "ThisEmploymentTax" decimal(10,2), "Dummy1" varchar(10), "TotalPayForYear" decimal(10,2), "TotalTaxForYear" decimal(10,2), "FinalTaxCodeForYear" varchar(20), "NICTableLetter1" char(1), "LELEarnings1" decimal(10,2), "PEEarnings1" decimal(10,2), "UELEarnings1" decimal(10,2), "EmployerContributions1" decimal(10,2), "dummy2" varchar(10), "NICTableLetter2" char(1), "LELEarnings2" decimal(10,2), "PEEarnings2" decimal(10,2), "UELEarnings2" decimal(10,2), "EmployerContributions2" decimal(10,2), "dummy3" varchar(10), "NICTableLetter3" char(1), "LELEarnings3" decimal(10,2), "PEEarnings3" decimal(10,2), "UELEarnings3" decimal(10,2), "EmployerContributions3" decimal(10,2), "dummy4" varchar(10), "NICTableLetter4" char(1), "LELEarnings4" decimal(10,2), "PEEarnings4" decimal(10,2), "UELEarnings4" decimal(10,2), "EmployerContributions4" decimal(10,2), "dummy5" varchar(10), "StatutoryMaternityPay" decimal(10,2), "StatutoryPaternityPay" decimal(10,2), "StatutorySharedParentalPay" decimal(10,2), "StatutoryAdoptionPay" decimal(10,2), "StudentLoanDeductions" decimal(10,2), "EmployerAddress" varchar(300), "EmployerPayeReference" varchar(40), "EmployeeAddress" varchar(300) ) // IQXWeb // ITS60 Proc begin set "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID"); select "I"."itsp60id", "I"."personid", "I"."surname", "I"."forename", "I"."ni", "I"."payrollnumber", "I"."PreviousEmploymentPay", "I"."PreviousEmploymentTax", "I"."ThisEmploymentPay", "I"."ThisEmploymentTax", "I"."Dummy1", "I"."TotalPayForYear", "I"."TotalTaxForYear", "I"."FinalTaxCodeForYear", "I"."NICTableLetter1", "I"."LELEarnings1", "I"."PEEarnings1", "I"."UELEarnings1", "I"."EmployerContributions1", "I"."dummy2", "I"."NICTableLetter2", "I"."LELEarnings2", "I"."PEEarnings2", "I"."UELEarnings2", "I"."EmployerContributions2", "I"."dummy3", "I"."NICTableLetter3", "I"."LELEarnings3", "I"."PEEarnings3", "I"."UELEarnings3", "I"."EmployerContributions3", "I"."dummy4", "I"."NICTableLetter4", "I"."LELEarnings4", "I"."PEEarnings4", "I"."UELEarnings4", "I"."EmployerContributions4", "I"."dummy5", "I"."StatutoryMaternityPay", "I"."StatutoryPaternityPay", "I"."StatutorySharedParentalPay", "I"."StatutoryAdoptionPay", "I"."StudentLoanDeductions", "I"."EmployerAddress", "I"."EmployerPayeReference", "I"."EmployeeAddress" from "ITSP60" as "I" join "person" as "p" on "replace"("p"."ni",' ','') = "I"."ni" where "p"."personid" = "pPersonID" end /* DOC 2017-10-06 PC modify to link on National Insurance, to allow more than one profile link to p45's 2019-07-08 GJ Updated to work with all the changes in iqxWEB 1.4.0 */ /* TEST SELECT * FROM NETITSP60('test.candidate', 'XXXXXXXXXXXXXXXXXXXX'); EXPECT EOF; */ go COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetITSP60" IS {create PROCEDURE pears."NetITSP60"( in "pWebUserID" char(20), in "pPersonID" char(20) ) RESULT( "itsp60id" VARCHAR(20), "personid" VARCHAR(20), "surname" VARCHAR(50), "forename" VARCHAR(50), "ni" CHAR(9), "payrollnumber" VARCHAR(40), "PreviousEmploymentPay" DECIMAL(10,2), "PreviousEmploymentTax" DECIMAL(10,2), "ThisEmploymentPay" DECIMAL(10,2), "ThisEmploymentTax" DECIMAL(10,2), "Dummy1" VARCHAR(10), "TotalPayForYear" DECIMAL(10,2), "TotalTaxForYear" DECIMAL(10,2), "FinalTaxCodeForYear" VARCHAR(20), "NICTableLetter1" CHAR(1), "LELEarnings1" DECIMAL(10,2), "PEEarnings1" DECIMAL(10,2), "UELEarnings1" DECIMAL(10,2), "EmployerContributions1" DECIMAL(10,2), "dummy2" VARCHAR(10), "NICTableLetter2" CHAR(1), "LELEarnings2" DECIMAL(10,2), "PEEarnings2" DECIMAL(10,2), "UELEarnings2" DECIMAL(10,2), "EmployerContributions2" DECIMAL(10,2), "dummy3" VARCHAR(10), "NICTableLetter3" CHAR(1), "LELEarnings3" DECIMAL(10,2), "PEEarnings3" DECIMAL(10,2), "UELEarnings3" DECIMAL(10,2), "EmployerContributions3" DECIMAL(10,2), "dummy4" VARCHAR(10), "NICTableLetter4" CHAR(1), "LELEarnings4" DECIMAL(10,2), "PEEarnings4" DECIMAL(10,2), "UELEarnings4" DECIMAL(10,2), "EmployerContributions4" DECIMAL(10,2), "dummy5" VARCHAR(10), "StatutoryMaternityPay" DECIMAL(10,2), "StatutoryPaternityPay" DECIMAL(10,2), "StatutorySharedParentalPay" DECIMAL(10,2), "StatutoryAdoptionPay" DECIMAL(10,2), "StudentLoanDeductions" DECIMAL(10,2), "EmployerAddress" VARCHAR(300), "EmployerPayeReference" VARCHAR(40), "EmployeeAddress" VARCHAR(300) ) // IQXWeb // ITS60 Proc BEGIN SET pPersonID=NetCandidateValidPersonID(pWebUserID,pPersonID); SELECT I.itsp60id, I.personid, I.surname, I.forename, I.ni, I.payrollnumber, I.PreviousEmploymentPay, I.PreviousEmploymentTax, I.ThisEmploymentPay, I.ThisEmploymentTax, I.Dummy1, I.TotalPayForYear, I.TotalTaxForYear, I.FinalTaxCodeForYear, I.NICTableLetter1, I.LELEarnings1, I.PEEarnings1, I.UELEarnings1, I.EmployerContributions1, I.dummy2, I.NICTableLetter2, I.LELEarnings2, I.PEEarnings2, I.UELEarnings2, I.EmployerContributions2, I.dummy3, I.NICTableLetter3, I.LELEarnings3, I.PEEarnings3, I.UELEarnings3, I.EmployerContributions3, I.dummy4, I.NICTableLetter4, I.LELEarnings4, I.PEEarnings4, I.UELEarnings4, I.EmployerContributions4, I.dummy5, I.StatutoryMaternityPay, I.StatutoryPaternityPay, I.StatutorySharedParentalPay, I.StatutoryAdoptionPay, I.StudentLoanDeductions, I.EmployerAddress, I.EmployerPayeReference, I.EmployeeAddress FROM ITSP60 I INNER JOIN person p ON replace(p.ni,' ','') = I.ni WHERE p.personid = pPersonID END /* DOC 2017-10-06 PC modify to link on National Insurance, to allow more than one profile link to p45's 2019-07-08 GJ Updated to work with all the changes in iqxWEB 1.4.0 */ /* TEST SELECT * FROM NETITSP60('test.candidate', 'XXXXXXXXXXXXXXXXXXXX'); EXPECT EOF; */ } </code> database/procedures/pears_netitsp60.txt Last modified: 2026/08/07 19:24by 127.0.0.1
127.0.0.1