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