Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
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; */ }