====== 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>