====== pears.NETITSP45 ======
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
===== Original SQL =====
create procedure "pears"."NETITSP45"( in "pWebUserID" char(20),in "pPersonID" char(20) )
result(
"itsp45id" char(20),
"personid" char(20),
"P1_LeavingDate" date,
"P1_DUMMY" char(20),
"P1_StudentLoan" char(1),
"P1_PayeOfficeNumber" char(10),
"P1_PayeReference" char(20),
"P1_LeavingTaxCode" char(10),
"P1_NI_A" char(2),
"P1_NI_B" char(2),
"P1_NI_C" char(2),
"P1_NI_D" char(2),
"P1_NI_E" char(1),
"P1_Week1" char(1),
"P1_Title" char(40),
"P1_surname" varchar(40),
"P1_WeekNo" integer,
"P1_MonthNo" integer,
"P1_FirstName" varchar(40),
"P1_TotalPayToDate1" integer,
"P1_TotalPayToDate2" char(1),
"P1_TotalPayToDate3" integer,
"P1_LeavingDate_DD" integer,
"P1_LeavingDate_MM" integer,
"P1_LeavingDate_yyyy" integer,
"P1_TotalTaxToDate1" integer,
"P1_TotalTaxToDate2" integer,
"P1_TotalTaxToDate3" char(1),
"P1_TotalEmpPay1" integer,
"P1_TotalEmpPay2" char(1),
"P1_TotalEmpPay3" integer,
"P1_PrivateAddress" varchar(300),
"P1_TotalEmpTax1" integer,
"P1_TotalEmptax2" char(1),
"P1_TotalEmpTax3" integer,
"P1_PrivatePostcode" varchar(20),
"P1_PayrollNumber" varchar(60),
"P1_Male" char(1),
"P1_Female" char(1),
"P1_EmployerNameAddress" varchar(300),
"P1_DOB_dd" char(2),
"P1_DOB_mm" char(2),
"P1_DOB_yyyy" char(4),
"P1_Date_dd" char(2),
"P1_Date_mm" char(2),
"P1_Date_yyyy" char(2),
"P1A_dummy1" char(30),
"P1A_dummy2" char(30),
"P1A_StudentLoan" char(1),
"P1A_PayeOfficeNumber" char(10),
"P1A_PayeReference" char(20),
"P1A_LeavingTaxCode" char(10),
"P1A_NI_A" char(2),
"P1A_NI_B" char(2),
"P1A_NI_C" char(2),
"P1A_NI_D" char(2),
"P1A_NI_E" char(1),
"P1A_Week1" char(1),
"P1A_Title" char(40),
"P1A_surname" varchar(40),
"P1A_WeekNo" integer,
"P1A_MonthNo" integer,
"P1A_FirstName" varchar(40),
"P1A_TotalPayToDate1" integer,
"P1A_TotalPayToDate2" char(1),
"P1A_TotalPayToDate3" integer,
"P1A_LeavingDate_DD" integer,
"P1A_LeavingDate_MM" integer,
"P1A_LeavingDate_yyyy" integer,
"P1A_TotalTaxToDate1" integer,
"P1A_TotalTaxToDate2" char(1),
"P1A_TotalTaxToDate3" integer,
"P1A_TotalEmpPay1" integer,
"P1A_TotalEmpPay2" char(1),
"P1A_TotalEmpPay3" integer,
"P1A_PrivateAddress" varchar(300),
"P1A_TotalEmpTax1" integer,
"P1A_TotalEmptax2" char(1),
"P1A_TotalEmpTax3" integer,
"P1A_PrivatePostcode" varchar(20),
"P1A_PayrollNumber" varchar(60),
"P1A_Male" char(1),
"P1A_Female" char(1),
"P1A_EmployerNameAddress" varchar(300),
"P1A_DOB_dd" char(2),
"P1A_DOB_mm" char(2),
"P1A_DOB_yyyy" char(4),
"P1A_Date_dd" char(2),
"P1A_Date_mm" char(2),
"P1A_Date_yyyy" char(2),
"P2_dummy1" char(30),
"P2_StudentLoan" char(1),
"P2_PayeOfficeNumber" char(10),
"P2_PayeReference" char(20),
"P2_LeavingTaxCode" char(10),
"P2_NI_A" char(2),
"P2_NI_B" char(2),
"P2_NI_C" char(2),
"P2_NI_D" char(2),
"P2_NI_E" char(1),
"P2_Week1" char(1),
"P2_Title" char(40),
"P2_surname" varchar(40),
"P2_WeekNo" integer,
"P2_MonthNo" integer,
"P2_FirstName" varchar(40),
"P2_TotalPayToDate1" integer,
"P2_TotalPayToDate2" char(1),
"P2_TotalPayToDate3" integer,
"P2_LeavingDate_DD" integer,
"P2_LeavingDate_MM" integer,
"P2_LeavingDate_yyyy" integer,
"P2_TotalTaxToDate1" integer,
"P2_TotalTaxToDate2" integer,
"P2_TotalTaxToDate3" char(1),
"P3_dummy1" char(30),
"P3_StudentLoan" char(1),
"P3_PayeOfficeNumber" char(10),
"P3_PayeReference" char(20),
"P3_LeavingTaxCode" char(10),
"P3_NI_A" char(2),
"P3_NI_B" char(2),
"P3_NI_C" char(2),
"P3_NI_D" char(2),
"P3_NI_E" char(1),
"P3_Week1" char(1),
"P3_Title" char(40),
"P3_surname" varchar(40),
"P3_WeekNo" integer,
"P3_MonthNo" integer,
"P3_FirstName" varchar(40),
"P3_TotalPayToDate1" integer,
"P3_TotalPayToDate2" char(1),
"P3_TotalPayToDate3" integer,
"P3_LeavingDate_DD" integer,
"P3_LeavingDate_MM" integer,
"P3_LeavingDate_yyyy" integer,
"P3_TotalTaxToDate1" integer,
"P3_TotalTaxToDate2" integer,
"P3_TotalTaxToDate3" char(1),
"P1_NI" char(9),
"P1_TotalPayToDate" decimal(10,2),
"P1_TotalTaxToDate" decimal(10,2),
"P1_TotalEmpPay" decimal(10,2),
"P1_TotalEmpTax" decimal(10,2),
"P1_DOB" date,
"P1_Date" date,
"P1A_NI" char(9),
"P1A_TotalPayToDate" decimal(10,2),
"P1A_TotalTaxToDate" decimal(10,2),
"P1A_TotalEmpPay" decimal(10,2),
"P1A_TotalEmpTax" decimal(10,2),
"P1A_DOB" date,
"P1A_Date" date,
"P1A_LeavingDate" date,
"P2_NI" char(9),
"P2_TotalPayToDate" decimal(10,2),
"P2_TotalTaxToDate" decimal(10,2),
"P2_LeavingDate" date,
"P3_NI" char(9),
"P3_TotalPayToDate" decimal(10,2),
"P3_TotalTaxToDate" decimal(10,2),
"P3_LeavingDate" date )
// IQXWeb
// ITS45 Proc
begin
set "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID");
select "I"."itsp45id",
"I"."personid",
"I"."P1_LeavingDate",
"I"."P1_DUMMY",
"I"."P1_StudentLoan",
"I"."P1_PayeOfficeNumber",
"I"."P1_PayeReference",
"I"."P1_LeavingTaxCode",
"I"."P1_NI_A",
"I"."P1_NI_B",
"I"."P1_NI_C",
"I"."P1_NI_D",
"I"."P1_NI_E",
"I"."P1_Week1",
"I"."P1_Title",
"I"."P1_surname",
"I"."P1_WeekNo",
"I"."P1_MonthNo",
"I"."P1_FirstName",
"I"."P1_TotalPayToDate1",
"I"."P1_TotalPayToDate2",
"I"."P1_TotalPayToDate3",
"I"."P1_LeavingDate_DD",
"I"."P1_LeavingDate_MM",
"I"."P1_LeavingDate_yyyy",
"I"."P1_TotalTaxToDate1",
"I"."P1_TotalTaxToDate2",
"I"."P1_TotalTaxToDate3",
"I"."P1_TotalEmpPay1",
"I"."P1_TotalEmpPay2",
"I"."P1_TotalEmpPay3",
"I"."P1_PrivateAddress",
"I"."P1_TotalEmpTax1",
"I"."P1_TotalEmptax2",
"I"."P1_TotalEmpTax3",
"I"."P1_PrivatePostcode",
"I"."P1_PayrollNumber",
"I"."P1_Male",
"I"."P1_Female",
"I"."P1_EmployerNameAddress",
"I"."P1_DOB_dd",
"I"."P1_DOB_mm",
"I"."P1_DOB_yyyy",
"I"."P1_Date_dd",
"I"."P1_Date_mm",
"I"."P1_Date_yyyy",
"I"."P1A_dummy1",
"I"."P1A_dummy2",
"I"."P1A_StudentLoan",
"I"."P1A_PayeOfficeNumber",
"I"."P1A_PayeReference",
"I"."P1A_LeavingTaxCode",
"I"."P1A_NI_A",
"I"."P1A_NI_B",
"I"."P1A_NI_C",
"I"."P1A_NI_D",
"I"."P1A_NI_E",
"I"."P1A_Week1",
"I"."P1A_Title",
"I"."P1A_surname",
"I"."P1A_WeekNo",
"I"."P1A_MonthNo",
"I"."P1A_FirstName",
"I"."P1A_TotalPayToDate1",
"I"."P1A_TotalPayToDate2",
"I"."P1A_TotalPayToDate3",
"I"."P1A_LeavingDate_DD",
"I"."P1A_LeavingDate_MM",
"I"."P1A_LeavingDate_yyyy",
"I"."P1A_TotalTaxToDate1",
"I"."P1A_TotalTaxToDate2",
"I"."P1A_TotalTaxToDate3",
"I"."P1A_TotalEmpPay1",
"I"."P1A_TotalEmpPay2",
"I"."P1A_TotalEmpPay3",
"I"."P1A_PrivateAddress",
"I"."P1A_TotalEmpTax1",
"I"."P1A_TotalEmptax2",
"I"."P1A_TotalEmpTax3",
"I"."P1A_PrivatePostcode",
"I"."P1A_PayrollNumber",
"I"."P1A_Male",
"I"."P1A_Female",
"I"."P1A_EmployerNameAddress",
"I"."P1A_DOB_dd",
"I"."P1A_DOB_mm",
"I"."P1A_DOB_yyyy",
"I"."P1A_Date_dd",
"I"."P1A_Date_mm",
"I"."P1A_Date_yyyy",
"I"."P2_dummy1",
"I"."P2_StudentLoan",
"I"."P2_PayeOfficeNumber",
"I"."P2_PayeReference",
"I"."P2_LeavingTaxCode",
"I"."P2_NI_A",
"I"."P2_NI_B",
"I"."P2_NI_C",
"I"."P2_NI_D",
"I"."P2_NI_E",
"I"."P2_Week1",
"I"."P2_Title",
"I"."P2_surname",
"I"."P2_WeekNo",
"I"."P2_MonthNo",
"I"."P2_FirstName",
"I"."P2_TotalPayToDate1",
"I"."P2_TotalPayToDate2",
"I"."P2_TotalPayToDate3",
"I"."P2_LeavingDate_DD",
"I"."P2_LeavingDate_MM",
"I"."P2_LeavingDate_yyyy",
"I"."P2_TotalTaxToDate1",
"I"."P2_TotalTaxToDate2",
"I"."P2_TotalTaxToDate3",
"I"."P3_dummy1",
"I"."P3_StudentLoan",
"I"."P3_PayeOfficeNumber",
"I"."P3_PayeReference",
"I"."P3_LeavingTaxCode",
"I"."P3_NI_A",
"I"."P3_NI_B",
"I"."P3_NI_C",
"I"."P3_NI_D",
"I"."P3_NI_E",
"I"."P3_Week1",
"I"."P3_Title",
"I"."P3_surname",
"I"."P3_WeekNo",
"I"."P3_MonthNo",
"I"."P3_FirstName",
"I"."P3_TotalPayToDate1",
"I"."P3_TotalPayToDate2",
"I"."P3_TotalPayToDate3",
"I"."P3_LeavingDate_DD",
"I"."P3_LeavingDate_MM",
"I"."P3_LeavingDate_yyyy",
"I"."P3_TotalTaxToDate1",
"I"."P3_TotalTaxToDate2",
"I"."P3_TotalTaxToDate3",
"I"."P1_NI",
"I"."P1_TotalPayToDate",
"I"."P1_TotalTaxToDate",
"I"."P1_TotalEmpPay",
"I"."P1_TotalEmpTax",
"I"."P1_DOB",
"I"."P1_Date",
"I"."P1A_NI",
"I"."P1A_TotalPayToDate",
"I"."P1A_TotalTaxToDate",
"I"."P1A_TotalEmpPay",
"I"."P1A_TotalEmpTax",
"I"."P1A_DOB",
"I"."P1A_Date",
"I"."P1A_LeavingDate",
"I"."P2_NI",
"I"."P2_TotalPayToDate",
"I"."P2_TotalTaxToDate",
"I"."P2_LeavingDate",
"I"."P3_NI",
"I"."P3_TotalPayToDate",
"I"."P3_TotalTaxToDate",
"I"."P3_LeavingDate"
from "ITSP45" as "I" join "person" as "p" on "replace"("p"."ni",' ','') = "string"("P1_NI_A","P1_NI_B","P1_NI_C","P1_NI_D","P1_NI_E")
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 NETITSP45('test.candidate', 'XXXXXXXXXXXXXXXXXXXX');
EXPECT EOF;
*/
go
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NETITSP45" IS
{create PROCEDURE pears."NETITSP45"( in "pWebUserID" char(20), in "pPersonID" char(20) )
RESULT(
"itsp45id" CHAR(20),
"personid" CHAR(20),
"P1_LeavingDate" DATE,
"P1_DUMMY" CHAR(20),
"P1_StudentLoan" CHAR(1),
"P1_PayeOfficeNumber" CHAR(10),
"P1_PayeReference" CHAR(20),
"P1_LeavingTaxCode" CHAR(10),
"P1_NI_A" CHAR(2),
"P1_NI_B" CHAR(2),
"P1_NI_C" CHAR(2),
"P1_NI_D" CHAR(2),
"P1_NI_E" CHAR(1),
"P1_Week1" CHAR(1),
"P1_Title" CHAR(40),
"P1_surname" VARCHAR(40),
"P1_WeekNo" INTEGER,
"P1_MonthNo" INTEGER,
"P1_FirstName" VARCHAR(40),
"P1_TotalPayToDate1" INTEGER,
"P1_TotalPayToDate2" CHAR(1),
"P1_TotalPayToDate3" INTEGER,
"P1_LeavingDate_DD" INTEGER,
"P1_LeavingDate_MM" INTEGER,
"P1_LeavingDate_yyyy" INTEGER,
"P1_TotalTaxToDate1" INTEGER,
"P1_TotalTaxToDate2" INTEGER,
"P1_TotalTaxToDate3" CHAR(1),
"P1_TotalEmpPay1" INTEGER,
"P1_TotalEmpPay2" CHAR(1),
"P1_TotalEmpPay3" INTEGER,
"P1_PrivateAddress" VARCHAR(300),
"P1_TotalEmpTax1" INTEGER,
"P1_TotalEmptax2" CHAR(1),
"P1_TotalEmpTax3" INTEGER,
"P1_PrivatePostcode" VARCHAR(20),
"P1_PayrollNumber" VARCHAR(60),
"P1_Male" CHAR(1),
"P1_Female" CHAR(1),
"P1_EmployerNameAddress" VARCHAR(300),
"P1_DOB_dd" CHAR(2),
"P1_DOB_mm" CHAR(2),
"P1_DOB_yyyy" CHAR(4),
"P1_Date_dd" CHAR(2),
"P1_Date_mm" CHAR(2),
"P1_Date_yyyy" CHAR(2),
"P1A_dummy1" CHAR(30),
"P1A_dummy2" CHAR(30),
"P1A_StudentLoan" CHAR(1),
"P1A_PayeOfficeNumber" CHAR(10),
"P1A_PayeReference" CHAR(20),
"P1A_LeavingTaxCode" CHAR(10),
"P1A_NI_A" CHAR(2),
"P1A_NI_B" CHAR(2),
"P1A_NI_C" CHAR(2),
"P1A_NI_D" CHAR(2),
"P1A_NI_E" CHAR(1),
"P1A_Week1" CHAR(1),
"P1A_Title" CHAR(40),
"P1A_surname" VARCHAR(40),
"P1A_WeekNo" INTEGER,
"P1A_MonthNo" INTEGER,
"P1A_FirstName" VARCHAR(40),
"P1A_TotalPayToDate1" INTEGER,
"P1A_TotalPayToDate2" CHAR(1),
"P1A_TotalPayToDate3" INTEGER,
"P1A_LeavingDate_DD" INTEGER,
"P1A_LeavingDate_MM" INTEGER,
"P1A_LeavingDate_yyyy" INTEGER,
"P1A_TotalTaxToDate1" INTEGER,
"P1A_TotalTaxToDate2" CHAR(1),
"P1A_TotalTaxToDate3" INTEGER,
"P1A_TotalEmpPay1" INTEGER,
"P1A_TotalEmpPay2" CHAR(1),
"P1A_TotalEmpPay3" INTEGER,
"P1A_PrivateAddress" VARCHAR(300),
"P1A_TotalEmpTax1" INTEGER,
"P1A_TotalEmptax2" CHAR(1),
"P1A_TotalEmpTax3" INTEGER,
"P1A_PrivatePostcode" VARCHAR(20),
"P1A_PayrollNumber" VARCHAR(60),
"P1A_Male" CHAR(1),
"P1A_Female" CHAR(1),
"P1A_EmployerNameAddress" VARCHAR(300),
"P1A_DOB_dd" CHAR(2),
"P1A_DOB_mm" CHAR(2),
"P1A_DOB_yyyy" CHAR(4),
"P1A_Date_dd" CHAR(2),
"P1A_Date_mm" CHAR(2),
"P1A_Date_yyyy" CHAR(2),
"P2_dummy1" CHAR(30),
"P2_StudentLoan" CHAR(1),
"P2_PayeOfficeNumber" CHAR(10),
"P2_PayeReference" CHAR(20),
"P2_LeavingTaxCode" CHAR(10),
"P2_NI_A" CHAR(2),
"P2_NI_B" CHAR(2),
"P2_NI_C" CHAR(2),
"P2_NI_D" CHAR(2),
"P2_NI_E" CHAR(1),
"P2_Week1" CHAR(1),
"P2_Title" CHAR(40),
"P2_surname" VARCHAR(40),
"P2_WeekNo" INTEGER,
"P2_MonthNo" INTEGER,
"P2_FirstName" VARCHAR(40),
"P2_TotalPayToDate1" INTEGER,
"P2_TotalPayToDate2" CHAR(1),
"P2_TotalPayToDate3" INTEGER,
"P2_LeavingDate_DD" INTEGER,
"P2_LeavingDate_MM" INTEGER,
"P2_LeavingDate_yyyy" INTEGER,
"P2_TotalTaxToDate1" INTEGER,
"P2_TotalTaxToDate2" INTEGER,
"P2_TotalTaxToDate3" CHAR(1),
"P3_dummy1" CHAR(30),
"P3_StudentLoan" CHAR(1),
"P3_PayeOfficeNumber" CHAR(10),
"P3_PayeReference" CHAR(20),
"P3_LeavingTaxCode" CHAR(10),
"P3_NI_A" CHAR(2),
"P3_NI_B" CHAR(2),
"P3_NI_C" CHAR(2),
"P3_NI_D" CHAR(2),
"P3_NI_E" CHAR(1),
"P3_Week1" CHAR(1),
"P3_Title" CHAR(40),
"P3_surname" VARCHAR(40),
"P3_WeekNo" INTEGER,
"P3_MonthNo" INTEGER,
"P3_FirstName" VARCHAR(40),
"P3_TotalPayToDate1" INTEGER,
"P3_TotalPayToDate2" CHAR(1),
"P3_TotalPayToDate3" INTEGER,
"P3_LeavingDate_DD" INTEGER,
"P3_LeavingDate_MM" INTEGER,
"P3_LeavingDate_yyyy" INTEGER,
"P3_TotalTaxToDate1" INTEGER,
"P3_TotalTaxToDate2" INTEGER,
"P3_TotalTaxToDate3" CHAR(1),
"P1_NI" CHAR(9),
"P1_TotalPayToDate" DECIMAL(10,2),
"P1_TotalTaxToDate" DECIMAL(10,2),
"P1_TotalEmpPay" DECIMAL(10,2),
"P1_TotalEmpTax" DECIMAL(10,2),
"P1_DOB" DATE,
"P1_Date" DATE,
"P1A_NI" CHAR(9),
"P1A_TotalPayToDate" DECIMAL(10,2),
"P1A_TotalTaxToDate" DECIMAL(10,2),
"P1A_TotalEmpPay" DECIMAL(10,2),
"P1A_TotalEmpTax" DECIMAL(10,2),
"P1A_DOB" DATE,
"P1A_Date" DATE,
"P1A_LeavingDate" DATE,
"P2_NI" CHAR(9),
"P2_TotalPayToDate" DECIMAL(10,2),
"P2_TotalTaxToDate" DECIMAL(10,2),
"P2_LeavingDate" DATE,
"P3_NI" CHAR(9),
"P3_TotalPayToDate" DECIMAL(10,2),
"P3_TotalTaxToDate" DECIMAL(10,2),
"P3_LeavingDate" DATE
)
// IQXWeb
// ITS45 Proc
BEGIN
SET pPersonID=NetCandidateValidPersonID(pWebUserID,pPersonID);
SELECT
I."itsp45id",
I."personid",
I."P1_LeavingDate",
I."P1_DUMMY",
I."P1_StudentLoan",
I."P1_PayeOfficeNumber",
I."P1_PayeReference",
I."P1_LeavingTaxCode",
I."P1_NI_A",
I."P1_NI_B",
I."P1_NI_C",
I."P1_NI_D",
I."P1_NI_E",
I."P1_Week1",
I."P1_Title",
I."P1_surname",
I."P1_WeekNo",
I."P1_MonthNo",
I."P1_FirstName",
I."P1_TotalPayToDate1",
I."P1_TotalPayToDate2",
I."P1_TotalPayToDate3",
I."P1_LeavingDate_DD",
I."P1_LeavingDate_MM",
I."P1_LeavingDate_yyyy",
I."P1_TotalTaxToDate1",
I."P1_TotalTaxToDate2",
I."P1_TotalTaxToDate3",
I."P1_TotalEmpPay1",
I."P1_TotalEmpPay2",
I."P1_TotalEmpPay3",
I."P1_PrivateAddress",
I."P1_TotalEmpTax1",
I."P1_TotalEmptax2",
I."P1_TotalEmpTax3",
I."P1_PrivatePostcode",
I."P1_PayrollNumber",
I."P1_Male",
I."P1_Female",
I."P1_EmployerNameAddress",
I."P1_DOB_dd",
I."P1_DOB_mm",
I."P1_DOB_yyyy",
I."P1_Date_dd",
I."P1_Date_mm",
I."P1_Date_yyyy",
I."P1A_dummy1",
I."P1A_dummy2",
I."P1A_StudentLoan",
I."P1A_PayeOfficeNumber",
I."P1A_PayeReference",
I."P1A_LeavingTaxCode",
I."P1A_NI_A",
I."P1A_NI_B",
I."P1A_NI_C",
I."P1A_NI_D",
I."P1A_NI_E",
I."P1A_Week1",
I."P1A_Title",
I."P1A_surname",
I."P1A_WeekNo",
I."P1A_MonthNo",
I."P1A_FirstName",
I."P1A_TotalPayToDate1",
I."P1A_TotalPayToDate2",
I."P1A_TotalPayToDate3",
I."P1A_LeavingDate_DD",
I."P1A_LeavingDate_MM",
I."P1A_LeavingDate_yyyy",
I."P1A_TotalTaxToDate1",
I."P1A_TotalTaxToDate2",
I."P1A_TotalTaxToDate3",
I."P1A_TotalEmpPay1",
I."P1A_TotalEmpPay2",
I."P1A_TotalEmpPay3",
I."P1A_PrivateAddress",
I."P1A_TotalEmpTax1",
I."P1A_TotalEmptax2",
I."P1A_TotalEmpTax3",
I."P1A_PrivatePostcode",
I."P1A_PayrollNumber",
I."P1A_Male",
I."P1A_Female",
I."P1A_EmployerNameAddress",
I."P1A_DOB_dd",
I."P1A_DOB_mm",
I."P1A_DOB_yyyy",
I."P1A_Date_dd",
I."P1A_Date_mm",
I."P1A_Date_yyyy",
I."P2_dummy1",
I."P2_StudentLoan",
I."P2_PayeOfficeNumber",
I."P2_PayeReference",
I."P2_LeavingTaxCode",
I."P2_NI_A",
I."P2_NI_B",
I."P2_NI_C",
I."P2_NI_D",
I."P2_NI_E",
I."P2_Week1",
I."P2_Title",
I."P2_surname",
I."P2_WeekNo",
I."P2_MonthNo",
I."P2_FirstName",
I."P2_TotalPayToDate1",
I."P2_TotalPayToDate2",
I."P2_TotalPayToDate3",
I."P2_LeavingDate_DD",
I."P2_LeavingDate_MM",
I."P2_LeavingDate_yyyy",
I."P2_TotalTaxToDate1",
I."P2_TotalTaxToDate2",
I."P2_TotalTaxToDate3",
I."P3_dummy1",
I."P3_StudentLoan",
I."P3_PayeOfficeNumber",
I."P3_PayeReference",
I."P3_LeavingTaxCode",
I."P3_NI_A",
I."P3_NI_B",
I."P3_NI_C",
I."P3_NI_D",
I."P3_NI_E",
I."P3_Week1",
I."P3_Title",
I."P3_surname",
I."P3_WeekNo",
I."P3_MonthNo",
I."P3_FirstName",
I."P3_TotalPayToDate1",
I."P3_TotalPayToDate2",
I."P3_TotalPayToDate3",
I."P3_LeavingDate_DD",
I."P3_LeavingDate_MM",
I."P3_LeavingDate_yyyy",
I."P3_TotalTaxToDate1",
I."P3_TotalTaxToDate2",
I."P3_TotalTaxToDate3",
I."P1_NI",
I."P1_TotalPayToDate",
I."P1_TotalTaxToDate",
I."P1_TotalEmpPay",
I."P1_TotalEmpTax",
I."P1_DOB",
I."P1_Date",
I."P1A_NI",
I."P1A_TotalPayToDate",
I."P1A_TotalTaxToDate",
I."P1A_TotalEmpPay",
I."P1A_TotalEmpTax",
I."P1A_DOB",
I."P1A_Date",
I."P1A_LeavingDate",
I."P2_NI",
I."P2_TotalPayToDate",
I."P2_TotalTaxToDate",
I."P2_LeavingDate",
I."P3_NI",
I."P3_TotalPayToDate",
I."P3_TotalTaxToDate",
I."P3_LeavingDate"
FROM
ITSP45 I INNER JOIN person p ON replace(p.ni,' ','')=string(P1_NI_A,P1_NI_B,P1_NI_C,P1_NI_D,P1_NI_E)
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 NETITSP45('test.candidate', 'XXXXXXXXXXXXXXXXXXXX');
EXPECT EOF;
*/
}