<% ' MyPAYE ' Module: EmployeePensionSchemes.asp ' ' This software is the copyright of Mr T J Sheppard and may not be copied, duplicated or ' modified other than as permitted in the licence agreement. ' ' © 2012 - 2023 Mr T J Sheppard ' Lyndale House ' Church Street ' Bradwell ' Hope Valley ' S33 9HJ ' ' Tel 0845 643 5191 ' Email Support@MyPAYE.co.uk ' ' MyPAYE is a Registered Trademark of MyPAYE Ltd '------------------------------------------------------------------- ' ' Updated 09 May 2023 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' ' Description - This module display details of the Personal Pension/Freestanding AVC ' Schemes for an Employe and also allows them to be edited or a new ' Pension Scheme to be entered when called with the approriate value ' for AddNewEdit ' If AddNewEdit = "Edit", then the fields are unlocked, but populated ' with the existing data. ' If AddNewEdit = "Add", then the fields are unlocked and blank. ' If AddNewEdit is any other value, then the fields are locked. ' If AddNewEdit = "Edit" or "Add", an Update button is displayed. ' ' Amendment Log '------------------------------------------------------------------------------------------ ' Date | Name | Vers. | Description '------------------------------------------------------------------------------------------ ' 18/04/05 | TJS | 1.0 | Original ' 09/05/05 | TJS | 1.0.01 | Replaced onchange="datachanged()" with onkeydown="checkkeypress(event)" ' | on all Text fields to ensure Update/Cancel button change ' | occurs on first character changed rather than when the ' | onchange event fires on loss of focus and moved Javascript ' | code to EmployeePensionSchemesJS.asp ' 16/05/05 | TJS | 1.0.04 | Modified to cater for different CSS files depending on the ' | Agency ID setting for the Employer via session variable PathModifier ' 19/05/05 | TJS | 1.0.05 | Added code to display Pension Contributions ' | Replaced RunPayroll image button with table panel ' 26/05/05 | TJS | 1.0.08 | Corrected error where PREE field is null on new entry ' 28/05/05 | TJS | 1.0.09 | Modified to prevent user clicking more than one button ' | at a time to prevent security violation errors ' 23/06/05 | TJS | 1.0.11 | Modified to initialise Help Topic ID, for Firefox compatibility ' | and modified record counts for MySQL ' 09/08/05 | TJS | 1.0.12 | Corrected destinatation frame for reloading EmployeeList ' | and added response headers to prevent caching and ' | modified to use OpenRecordset and ExecuteSQL instead of ' | objConn.execute to enable trapping of SQL errors ' | Also modified to show Cancel instead of Close on validation errors ' 02/09/05 | TJS | 1.0.14 | Corrected location of EmployeePensionSchemesJS.asp ' 19/10/05 | TJS | 1.0.15 | Corrected maximum length of Pension Scheme name ' 24/01/06 | TJS | 1.0.20 | Modified to cater for frame changes for compatibility with EnterHoursFrames.asp ' | Also modified to allow display only and to prevent update if display only ' 01/02/06 | TJS | 1.0.21 | Modified to correctly detect Employee Pension Scheme where ' | Employer has different schemes for different payrolls and to only ' | show employee as having joined company scheme if a contribution is being paid ' 28/05/06 | TJS | 1.0.28 | Modified to correctly show AVC contribution boxes when AVC ' | allowed on company schemes and to add Directors APP checkbox ' | Also modified to close DB connection on completion, to ' | add RerunPayroll parameter in call to EmployeeDetails.asp etc ' | and modified to include ICRA content rating link ' 24/08/06 | TJS | 1.0.33 | Changed frame name used for help window from PopupWindow1 to HelpWindow ' | and modified to lock check boxes when form is meant to be read only ' 17/09/06 | TJS | 1.0.34 | Modified to prevent AddNewEdit being set to Add when displaying a ' | different tab after cancelling a new record which failed validation ' 13/02/07 | TJS | 1.1.06 | Added tabs for Job History, Training and P11D etc ' 19/06/07 | TJS | 1.2.00 | Modified to replace session variables with Session_Vars table ' 17/07/07 | TJS | 1.2.03 | Added EmployerID into sql for pay items table for added security ' 21/09/07 | TJS | 1.2.05 | Modified to ensure Additional Functions not disabled on free accounts ' | when trial period ends and modified to cater for Notes tab ' 21/11/07 | TJS | 1.2.07 | Modifed to use Agency based system name instead of MyPAYE ' 03/11/08 | TJS | 1.2.12 | Modified GetDBField to handle DirAPP as a boolean field ' 28/08/09 | TJS | 1.3.05a | Modified to cater for HR, P9D and P11D now being included in main tariff ' 25/01/10 | TJS | 1.3.12 | Modified to cater for POST when re-displayed from UpdatePrevEmploy.asp ' 26/02/10 | TJS | 1.3.14 | Modified to excluded deleted Pension Schemes ' 05/09/10 | TJS | 1.4.01 | Moved form inside fieldset to prevent extra blank space below below tabs ' 05/10/10 | TJS | 1.4.03 | Modified to cater for DispEE parameter on EmployeeList.asp ' 18/11/10 | TJS | 1.5.00 | Modified to use both root and agency CSS files when used to ' | eliminate duplicated settings in agency CSS files, plus ' | added label tags for field descriptions ' 13/12/10 | TJS | 1.5.01 | Modified to cater for Pay_Contrib_On_Parental_Pay ' 24/02/11 | TJS | 1.5.03 | Modified to cater for Tax Credits being replaced with Holidays, ' | to cater for Policy Number and to check for existing pension schemes ' | and show either the Company Scheme or a Personal Scheme if one exists ' | Also modified to ignore deleted pension pay items ' 30/06/11 | TJS | 1.7.00 | Modified to use JS_Keys in session_vars table instead of session("GetEEPJS") ' 10/07/11 | TJS | 1.7.01 | REplaced JS_Keys with individual fields to stop occasional field overflow errors ' 24/04/12 | TJS | 2.0.03 | Modified for Commpany Salary Sacrifice Pensions and corrected detection of selected Freestanding AVC schemes ' 19/07/12 | DC | 2.0.04 | Removed % from end of PercentEmployer/PercentEmployee and appended to value when displayed. ' | | | Code erroring at line 885 (now 886). ' 08/08/12 | TJS | 2.0.05 | Google Analytics added ' 09/01/13 | TJS | 3.0.00 | Modified for tab based User access permissions and for business transfer to MyPAYE Ltd ' 26/02/13 | DC | 3.0.00a| Added 'ContribAllowChanges' to setting value of 'ANE' and utilised in contribution fields. ' 31/05/13 | TJS | 3.0.06 | Modified to pass re-run payroll flag to UpdateEmployee.asp ' 15/08/13 | DC | 3.0.09 | Missed one occurance of changing AllowChanges for ContribAllowChanges. ' 01/06/14 | TJS | 4.0.00 | Modified for new styling and operation without framesets ' 15/07/14 | TJS | 4.0.01 | Added Pension AE parameter field 1, 2 and 3 ' 22/08/14 | TJS | 4.0.02 | Modified to cater for both lump sum and percentage pension pay items ' 16/09/14 | TJS | 4.0.02a | Modified to allow setting of Pension Code and Pension Due nominal codes for each scheme ' 29/09/14 | TJS | 4.0.04 | Modified to show payroll period ' 05/10/14 | TJS | 4.0.04a | Modified for Encrypted Employer ID check ' 30/10/14 | TJS | 4.0.05 | Modified display of Alert Messages to cater for RemoveHTML changes ' 08/12/14 | TJS | 4.0.08 | Modified to read AE Status from Employee_Pension_Auto_Enroll table ' 20/12/14 | TJS | 4.0.08a | Modified to cater for early AE Opt-in ' 28/01/15 | TJS | 4.0.10 | Modified to cater for Friendly Pensions and The Peoples Pension AE formats ' 19/02/15 | TJS | 4.0.11 | Modified to cater for Standard Life PensionsAE formats and corrected help window name ' 28/03/15 | TJS | 4.0.12 | Removed duplicate rsEmployer.close at line 791 ' 02/06/15 | TJS | 4.0.16 | Corrected display of Pension Scheme records when employee transferred multiple times ' 05/07/15 | TJS | 4.0.18 | Modified for Scottish Widows AE Pension ' 27/08/15 | DC | 4.0.21 | Moved "CreateASPEncrypt" to top of page so always available and removed ' | | | "ReleaseASPEncrypt" from within Integration "If". ' 23/09/15 | DC | 4.0.22 | Only displayed the AE elements if the pension scheme has "Auto_Enrolment" set, not just ' | | | reliant on the status flags etc. ' 16/10/15 | TJS | 4.0.22a | Modified to send e-conomic app identifier ' 26/11/15 | TJS | 5.0.00 | Modified to cater for separate User_Permissions table ' 03/12/15 | TJS | 5.0.01 | Modified to cater for e-conomic now being Reviso in the UK ' 03/12/15 | DC | 5.0.01a| Displayed AE elements for scheme "10" (NEST.csv). ' 06/02/16 | TJs | 5.0.03 | Modifeid to cater for Sort by option on EmployeeList.asp ' 14/03/16 | TJS | 5.0.04 | Added stopped contribution and eligible jobholder opted back in statuses ' | Also removed Payment Source field on NEST as now on Payroll options ' 26/04/16 | TJS | 5.0.08 | Modified to add extra note on salary sacrifice pensions ' 29/04/16 | TJS | 5.0.08a | Corrected display of contribution values when in re-run payroll mode ' 04/05/16 | TJS | 5.0.08b | added missing code for lump sump contributions on payroll rerun ' 18/05/16 | TJS | 5.0.08c | Corrected values for default employer and employee contribution rates (ERContribRate and EEContribRate) ' 31/05/16 | TJS | 5.0.10 | Added Master Trust, Defined Contribution and Defined Benefit Scheme Types ' 17/07/16 | TJS | 5.0.12 | Modified to cater for multiple AE pensions and to use AutoEnrolStatusConst.asp ' 20/07/16 | TJS | 5.0.12a | Added maxlength on AE Params 1,2,3 ' 26/07/16 | TJS | 5.0.12b | Modified to use Xero, KashFlow API functions for consistency ' 28/07/16 | TJS | 5.0.13 | Modified to cater for IncludeMenu, SelectedMenuItem and JavascriptField parameters on GetXeroAccountCodes function ' 16/08/16 | TJS | 5.0.13a | Modified to add mandatory indicators on provider specific fields ' 09/09/16 | TJS | 5.0.13b | Modified to pick up AutoEnrolPensionSchemeID if employee not assessed for AE ' 20/09/16 | DC | 5.0.14 | Allowed for slightly longer Liquid Accouts NL descriptions and truncated when inserting into temp sort. ' 29/09/16 } TJS | 5.0.14a | Modified to add AE parameter 4 to cater for NEST Opt Out received in Time field ' 18/10/16 | TJS | 5.0.15 | Modified to enable PensionSync for Legal & General plus The Peoples Pension plus Pension Setting Options table ' 27/10/16 | TJS | 5.0.15a | Moved code to read Liquid account code to LiquidAccountsAPIFunctions.asp include file ' 29/10/16 | TJS | 5.0.15b | Added mandatory indicator for Peoples Pension Worker Group ' 08/11/16 | TJS | 5.0.16 | Modified to use AE Pension Provider ID constants ' 24/11/16 | TJS | 5.0.16a | Modified to show Stopped Contribution date ' 28/02/17 | TJS | 5.0.18 | Modified to prevent errors if employer pension scheme is deleted ' 08/04/17 | TJS | 5.0.19 | Modified ShowPensAEField to display Opt In Time field for all providers in order to handle contribution refunds ' | and added RefundCont hidden field. Added error handling for revised integrated accounts API functions ' 10/04/17 | TJS | 5.0.20 | Corrected Accounts Integration sent when API errors occur ' 21/04/17 | DC | 5.1.00 | Changed to display % contribs to 2 decimal places. ' 25/04/17 | TJS | 5.1.00a | Corrected detection of company schemes when multiple pensions enabled ' 03/05/17 | TJS | 5.1.01 | Modified to check if Enrolment detail has been sent to Pension Provider ' 12/05/17 | TJS | 5.1.02 | Modified to cater for create Pension AE Forms function ' 30/05/17 | TJS | 5.1.02a | Modified to cater for AEStatusNotInUK ' 10/07/17 | TJS | 5.1.04 | Modified to cater for validation errors when refunding opt out contributions ' 27/07/17 | TJS | 5.1.05 | Modified to enable overriding of pension contribution refunds ' 04/08/17 | DC | 5.1.05a| Processed "Auto_Enrolment_Param_1" through ConvertEscapeChars(). ' 17/09/17 | TJS | 5.1.09 | added IsValueNegative and IsValueGtZero functions to prevent errors when re-displaying validation errors with invalid lump sum contributions ' 09/10/17 | TJS | 5.1.10 | Corrected conflict where PSID param was used for both PaySlipID and PenSchID on new employee wizard ' 13/10/17 | DC | 5.1.10a| Allowed for more than 1 status row per period, for different schemes. ' 27/10/17 | DC | 5.1.11 | Allowed for 40 chars in "AE Group". ' 13/11/17 | TJS | 5.1.11a | Modified for Zurich pensions ' 26/11/17 | TJS | 5.1.11b | Modified to display Group option for Smart Pension ' 08/01/18 | TJS | 5.1.14 | Corrected double % on Employee AVC contributions and display of AE rows when multiple pensions enabled ' 15/01/18 | TJS | 5.1.15 | Removed spurious from code ' 29/01/18 | TJS | 5.1.17 | Modified to cater for AEStatusExcludedDirector status ' 23/02/18 | DC | 5.1.19 | DC disallowed -ve Lump Sum (refund) to be for "This and Next Period" ' 26/02/18 | TJS | 5.1.19a | Modified to prevent error if account integration needs authorisation renewed ' 08/03/18 | DC | 5.1.19b| Set Lump Sum radio buttons dependant on Pens_next_Period values. ' 14/04/18 | TJS | 5.2.03 | Modified IsValueNegative and IsValueGtZero to prevent errors if input blank and to use for checks on negative lump sum contributions ' 19/05/18 | TJS | 5.2.07 | Modified to cater for Deleted field on ExactOnline_Integration, JustAccounts_Integration, KashFlow_Integration tables ' 10/06/18 | TJS | 5.3.00 | Modified call to GetXeroAccountCodes to cater for Xero Tracking Categories ' 27/06/18 | TJS | 5.3.01 | Modified to use WinHTTP.WinHTTPRequest.5.1 instead of Msxml2.ServerXMLHTTP ' 03/08/18 | TJS | 5.3.04 | Modified to cater for AEStatusOverLifetimeAllow status ' 14/09/18 | TJS | 5.4.01 | Modified Employee_Pay_Items sql to optimise use of amended indexes ' 12/07/19 | TJS | 5.5.12 | Modified for User Details menu when Payslip Delivery is employee portal login ' 24/08/19 | TJS | 5.6.00 | Changed Contributions paid Gross/Net from radio buttons to a list box to match Employer Pension page ' 18/12/19 | TJS | 5.7.03 | Added EmployerTableMenuOptionFields to rsEmployer to cater for linked account login being needed ' 26/02/20 | TJS | 5.7.04 | Modified to cater for Reviso REST API ' 07/04/20 | TJS | 5.7.08 | Modified to cater for Quickbooks Integration ' 23/04/20 | TJS | 5.7.09 | Added missing Quickbooks API include file, modified to hide QuickBooks dummy codes and use AddNominalCodes function for listbox option generation ' 30/05/20 | TJS | 5.8.00 | Modified to replace stylesheet.css, mainmenu.css and buttons-css.asp with BodyAndMenu-css.asp and other layout changes ' 25/09/20 | TJS | 5.9.00 | Corrected Percentage of Pensionable
Salary Contributions header where some characters had accidentally been deleted ' 25/02/21 | TJS | 5.9.07 | Modified remaining calls to Standard directory be https:// ' 08/05/21 | TJS | 5.9.09 | Modified to add Employee ID tip on Name header when user ID is support ' 30/06/21 | TJS | 5.9.10a | Modified to cater for NextPage parameter on GetXeroAccountCodes ' 18/08/21 | TJS | 5.9.11 | Modified to replace alert and confirm dialogues to cater for browser blocks on cross site dialogs and ' | and corrected call to GetQBAccountCodes to exclude menus ' 25/08/21 | TJS | 5.9.11a | Corrected spelling of Liquid Accounts ' 18/09/21 | TJS | 5.9.12 | Corrected call to GetExactOnlineAuthToken, GetXeroAccountCodes and GetQBAccountCodes ' | Also modified to use window.parent.DisplayAlertMsg ' 27/10/21 | TJS | 5.9.13 | Corrected call to display AlertMessage ' 99/02/22 | TJS | 5.9.16 | Modified to use ASPDotNet/UpdatePensionScheme.ashx page ' 09/05/23 | TJS | 5.9.24 | Modified to use ASPDotNet/UpdateNominalCode.ashx page ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' PageID = 86865 NoBack = False ' since form is loaded using Post when displayed from UpdatePrevEmploy.asp, need to check for this if Request.ServerVariables("request_method") = "GET" then ' TJS 25/01/10 SessionID = request.querystring("ID") SessionVarsID = RemoveHTML(request.querystring("SV")) ' TJS 19/06/07 SourceFrame = RemoveHTML(request.querystring("S")) DestFrame = RemoveHTML(request.querystring("D")) PensionSchemeID = RemoveHTML(request.querystring("PenSchID")) ' TJS 09/10/17 EmployerID = CheckURLDBIDField(RemoveHTML(request.querystring("ER")), "EmployerID") EmployeeID = CheckURLDBIDField(RemoveHTML(request.querystring("EE")), "EmployeeID") PayrollID = CheckURLDBIDField(RemoveHTML(request.querystring("PR")), "PayrollID") AddNewEdit = RemoveHTML(request.querystring("ANE")) RerunPayroll = RemoveHTML(request.querystring("RrP")) CancelPage = RemoveHTML(request.querystring("CPge")) else SessionID = request.form("ID") ' TJS 25/01/10 SessionVarsID = RemoveHTML(request.form("SV")) ' TJS 25/01/10 SourceFrame = RemoveHTML(request.form("S")) ' TJS 25/01/10 DestFrame = RemoveHTML(request.form("D")) ' TJS 25/01/10 PensionSchemeID = RemoveHTML(request.form("PenSchID")) ' TJS 25/01/10 TJS 09/10/17 EmployeeID = CheckURLDBIDField(RemoveHTML(request.form("EE")), "EmployeeID") ' TJS 25/01/10 EmployerID = CheckURLDBIDField(RemoveHTML(request.form("ER")), "EmployerID") ' TJS 25/01/10 PayrollID = CheckURLDBIDField(RemoveHTML(request.form("PR")), "PayrollID") ' TJS 25/01/10 AddNewEdit = RemoveHTML(request.form("ANE")) ' TJS 25/01/10 RerunPayroll = RemoveHTML(request.form("RrP")) ' TJS 25/01/10 CancelPage = RemoveHTML(request.form("CPge")) ' TJS 25/01/10 end if if "" & PensionSchemeID = "" then PensionSchemeID = -1 else PensionSchemeID = CheckURLDBIDField(PensionSchemeID, "PensionSchemeID") end if %> <%GetSessionVarsIDRecord(SessionVarsID) ' TJS 19/06/07%> <%' TJS 17/07/16%> <%' start of code added TJS 26/07/16%> <%' TJS 26/02/20%> <%' TJS 23/04/20%> <%' end of code added TJS 26/07/16 ' since form is loaded using Post when displayed from UpdatePrevEmploy.asp, need to check for this if Request.ServerVariables("request_method") = "GET" then ' TJS 25/01/10 FailureFields = RemoveHTML(request.querystring("FFld")) FailureMessage = RemoveHTML(request.querystring("FMsg")) AlertMessage = RemoveHTML(request.querystring("AlMsg")) else FailureFields = RemoveHTML(request.form("FFld")) ' TJS 25/01/10 FailureMessage = RemoveHTML(request.form("FMsg")) ' TJS 25/01/10 AlertMessage = RemoveHTML(request.form("AlMsg")) ' TJS 25/01/10 end if ' start of code added DC 04/08/17 function ConvertEscapeChars(StringToProcess) ConvertEscapeChars = replace(StringToProcess,"&", "&") ConvertEscapeChars = replace(ConvertEscapeChars,"<","<") ConvertEscapeChars = replace(ConvertEscapeChars,">",">") ConvertEscapeChars = replace(ConvertEscapeChars,""","""") ConvertEscapeChars = replace(ConvertEscapeChars,"'","'") ConvertEscapeChars = replace(ConvertEscapeChars, "'", "'") end function ' end of code added DC 04/08/17 Function GetDBField(FieldName, ControlName) if FailureFields <> "" and (AddNewEdit = "Add" or AddNewEdit = "Edit") then if ControlName = "ContractOut" or ControlName = "ContributionPaidGross" or _ ControlName = "DirAPP" or ControlName = "ContribOnParentalPay" or _ ControlName = "AutoEnrolment" then ' TJS 03/11/08 TJS 13/12/10 TJS 01/06/14 if RemoveHTML(request.form(ControlName)) = "ON" then ' TJS 03/11/08 TJS 25/01/10 GetDBField = -1 ' TJS 03/11/08 else GetDBField = 0 ' TJS 03/11/08 end if else GetDBField = RemoveHTML(request.form(ControlName)) ' TJS 25/01/10 end if elseif EmployerID < 0 then if ControlName = "PensCostNom" or ControlName = "PensDueNom" then ' TJS 16/09/14 GetDBField = "-1" ' TJS 16/09/14 elseif ControlName = "PREE" then GetDBField = EmployeeID else ' return a null value end if elseif NoRecordsFound then if ControlName = "PensCostNom" then ' TJS 16/09/14 GetDBField = PensionCostNominal ' TJS 16/09/14 elseif ControlName = "PensDueNom" then ' TJS 16/09/14 GetDBField = PensionDueNominal ' TJS 16/09/14 elseif ControlName = "PREE" then GetDBField = EmployeeID else ' return a null value end if elseif ControlName = "AutoEnrolStatus" then if not rsAEStatus.EOF then ' TJS 08/12/14 if not bMultiplePensionsEnabled or rsAEStatus("Pension_Scheme_ID") = cLng(PensionSchemeID) then ' TJS 25/04/17 GetDBField = rsAEStatus("Auto_Enrolment_Status") ' TJS 08/12/14 else ' return a null value end if else ' return a null value end if ' start of code added TJS 08/12/14 elseif ControlName = "AutoEnrolEnrolled" then if not rsAEStatus.EOF then if not bMultiplePensionsEnabled or rsAEStatus("Pension_Scheme_ID") = cLng(PensionSchemeID) then ' TJS 25/04/17 GetDBField = rsAEStatus("Enrolment_Date") else ' return a null value end if else ' return a null value end if elseif ControlName = "AutoEnrolPostpone" then if not rsAEStatus.EOF then if not bMultiplePensionsEnabled or rsAEStatus("Pension_Scheme_ID") = cLng(PensionSchemeID) then ' TJS 25/04/17 GetDBField = rsAEStatus("Next_Auto_Enroll_Date") else ' return a null value end if else ' return a null value end if elseif ControlName = "AutoEnrolOptIn" then if not rsAEStatus.EOF then if not bMultiplePensionsEnabled or rsAEStatus("Pension_Scheme_ID") = cLng(PensionSchemeID) then ' TJS 25/04/17 GetDBField = rsAEStatus("Enrolment_Date") else ' return a null value end if else ' return a null value end if elseif ControlName = "AutoEnrolOptOut" then if not rsAEStatus.EOF then if not bMultiplePensionsEnabled or rsAEStatus("Pension_Scheme_ID") = cLng(PensionSchemeID) then ' TJS 25/04/17 GetDBField = rsAEStatus("Opt_Out_Date") else ' return a null value end if else ' return a null value end if ' end of code added TJS 08/12/14 ' start of code added TJS 17/07/16 elseif ControlName = "AutoEnrolPensID" then if not rsAEStatus.EOF then if not bMultiplePensionsEnabled or rsAEStatus("Pension_Scheme_ID") = cLng(PensionSchemeID) then ' TJS 25/04/17 GetDBField = rsAEStatus("Pension_Scheme_ID") else GetDBField = PensionSchemeID end if else GetDBField = AutoEnrolPensionSchemeID ' TJS 09/09/16 end if ' end of code added TJS 17/07/16 elseif ControlName = "AutoEnrolParam1" then ' TJS 15/07/14 GetDBField = ConvertEscapeChars(DecryptData(rsEmplDetails("Auto_Enrolment_Param_1"))) ' TJS 15/07/14 DC 04/08/17 elseif ControlName = "AutoEnrolParam2" then ' TJS 15/07/14 GetDBField = DecryptData(rsEmplDetails("Auto_Enrolment_Param_2")) ' TJS 15/07/14 elseif ControlName = "AutoEnrolParam3" then ' TJS 20/08/14 GetDBField = DecryptData(rsEmplDetails("Auto_Enrolment_Param_3")) ' TJS 20/08/14 elseif ControlName = "AutoEnrolParam4" then ' TJS 29/09/16 GetDBField = DecryptData(rsEmplDetails("Auto_Enrolment_Param_4")) ' TJS 29/09/16 else ' return recordset value if "" & FieldName <> "" then ' TJS 10/07/17 GetDBField = rsPensDetails(FieldName) end if end if end Function '------------------------------------------------------------------------------------------------------- Function CheckValidationFailure(FieldName) if instr(1, FailureFields, ":" & FieldName & ":") = 0 then ' no failure - set control to normal CheckValidationFailure = "" ' TJS 01/06/14 else ' validation failed - set control to red outline CheckValidationFailure = "-error" ' TJS 01/06/14 end if end Function '------------------------------------------------------------------------------------------------------- ' start of code added TJS 15/07/14 Function ShowPensAEField(ByVal FieldNumber, ByRef FieldDisplayName) ShowPensAEField = false FieldDisplayName = "" if AddNewEdit <> "Add" then if PensionSchemeID >= 0 then ' TJS 17/07/16 if cBool(rsPensDetails("Auto_Enrolment")) then if FieldNumber = 1 then if PensionAEFormat = NESTPensionProviderID then ' DC 03/12/15 ' NEST pension - need Group field for Employee ShowPensAEField = true FieldDisplayName = "Group *" ' TJS 16/08/16 elseif PensionAEFormat = AegonPensionProviderID then ' Aegon pension - need Category Rule field for Employee ShowPensAEField = true FieldDisplayName = "Category Rule" elseif PensionAEFormat = PeoplesCSVPensionProviderID or PensionAEFormat = PeoplesAPIPensionProviderID then ' TJS 28/01/15 TJS 29/10/16 ' The People's Pension - need AE Worker Group ShowPensAEField = true ' TJS 28/01/15 FieldDisplayName = "AE Worker Group *" ' TJS 28/01/15 TJS 29/10/16 elseif PensionAEFormat = StdLifePensionProviderID then ' TJS 19/02/15 ' Standard Life - cater for Multi-site code ShowPensAEField = true ' TJS 19/02/15 FieldDisplayName = "Multi-site code" ' TJS 19/02/15 elseif PensionAEFormat = ScotWidPensionProviderID then ' TJS 05/07/15 ' Scottish Widows - cater for Pension provider worker group ShowPensAEField = true ' TJS 05/07/15 FieldDisplayName = "Worker Group" ' TJS 05/07/15 elseif PensionAEFormat = LandGViaPensSyncProviderID then ' TJS 18/10/16 ' Legal & General via PensionSync - cater for Pension provider worker group ShowPensAEField = true ' TJS 18/10/16 FieldDisplayName = "Contribution Group *" ' TJS 18/10/16 elseif PensionAEFormat = ZurichPensionProviderID then ' TJS 13/11/17 ' Zurich - cater for Paycentre ShowPensAEField = true ' TJS 13/11/17 FieldDisplayName = "Paycentre *" ' TJS 13/11/17 elseif PensionAEFormat = SmartPensionProviderID then ' TJS 26/11/17 ' Smart Pension - cater for optional group ShowPensAEField = true ' TJS 26/11/17 FieldDisplayName = "Group" ' TJS 26/11/17 end if elseif FieldNumber = 2 then ' NEST payment source code removed TJS 14/03/16 if PensionAEFormat = StdLifePensionProviderID then ' TJS 19/02/15 ' Standard Life - plan number ShowPensAEField = true ' TJS 19/02/15 FieldDisplayName = "Plan Number" ' TJS 19/02/15 elseif PensionAEFormat = LandGViaPensSyncProviderID then ' TJS 18/10/16 ' Legal & General via PensionSync - cater for Pension provider plan number ShowPensAEField = true ' TJS 18/10/16 FieldDisplayName = "Member Plan Number" ' TJS 18/10/16 end if elseif FieldNumber = 3 then if PensionAEFormat = NESTPensionProviderID then ' DC 03/12/15 ' NEST pension - Opt Out Ref field for Employee if GetDBField("Auto_Enrolment_Status", "AutoEnrolStatus") = AEStatusEligibleJobholderOptedOut then ShowPensAEField = true end if FieldDisplayName = "Opt Out Ref *" ' TJS 16/18/16 end if ' start of code added TJS 29/09/16 elseif FieldNumber = 4 then if "" & PensionAEFormat <> "" then ' TJS 08/04/17 ' Opt Out Received on time field for Employee if GetDBField("Auto_Enrolment_Status", "AutoEnrolStatus") = AEStatusEligibleJobholderOptedOut then ShowPensAEField = true end if FieldDisplayName = "Opt Out Form Received within Allowed Opt Out Period *" end if ' end of code added TJS 29/09/16 end if end if end if end if end Function ' end of code added TJS 15/07/14 '------------------------------------------------------------------------------------------------------- ' start of code added TJS 16/09/14 function ConvertForXML(ByVal StringToConvert) ConvertForXML = replace(StringToConvert,"&","&") ConvertForXML = replace(ConvertForXML,"<","<") ConvertForXML = replace(ConvertForXML,">",">") ConvertForXML = replace(ConvertForXML,"""",""") ConvertForXML = replace(ConvertForXML,"'","'") end function ' start of code added TJS 26/07/16 '------------------------------------------------------------------------------------------------------- function ConvertFromDB(ByVal StringToConvert) ConvertFromDB = replace(StringToConvert,"&","&") ConvertFromDB = replace(ConvertFromDB,"<","<") ConvertFromDB = replace(ConvertFromDB,">",">") ConvertFromDB = replace(ConvertFromDB,""","""") ConvertFromDB = replace(ConvertFromDB,"'","'") end function ' end of code added TJS 26/07/16 '------------------------------------------------------------------------------------------------------- ' start of code added TJS 17/09/17 function IsValueNegative(ByVal ValueToCheck) IsValueNegative = false if "" & ValueToCheck <> "" then ' TJS 14/04/18 if IsNumeric(ValueToCheck) then if CDbl(ValueToCheck) < 0 then IsValueNegative = true end if end if end if end function '------------------------------------------------------------------------------------------------------- function IsValueGtZero(ByVal ValueToCheck) IsValueGtZero = false if "" & ValueToCheck <> "" then ' TJS 14/04/18 if IsNumeric(ValueToCheck) then if CDbl(ValueToCheck) > 0 then IsValueGtZero = true end if end if end if end function ' end of code added TJS 17/09/17 '------------------------------------------------------------------------------------------------------- sub SendErrorEmail(ByVal AccountIntegration, ByVal EMailText, ByVal AlertText) if left(UCase(request.servervariables("SERVER_NAME")), 9) <> "MYPAYE-LT" then ' TJS 03/12/15 ' Read registry for IIS Version & mail server params Set WshShell = server.CreateObject("WScript.Shell") RegKey = "HKLM\Software\MyPAYE\IIS\" IISVersion = WshShell.RegRead(RegKey & "Version") SMTPSendUsing = WshShell.RegRead(RegKey & "SendUsing") SMTPPickupDirectory = WshShell.RegRead(RegKey & "PickupDirectory") SMTPServer = WshShell.RegRead(RegKey & "SMTPServer") SMTPServerPort = WshShell.RegRead(RegKey & "ServerPort") Set WshShell = nothing Set myMail = CreateObject("CDO.Message") ' ######### CODE ADDED TO WORK ON IIS 7 ############################################ if IISVersion = 7 then myMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = SMTPSendUsing if SMTPSendUsing = 1 then myMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverpickupdirectory") = SMTPPickupDirectory else myMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer myMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = SMTPServerPort end if myMail.Configuration.Fields.Update end if ' ################################################################################### myMail.Subject = "MyPAYE - " & AccountIntegration & " integration (EE Pension) error on EmployerID " & EmployerID myMail.From = "AutoSequence@MyPAYE.co.uk" myMail.To = "Support@MyPAYE.co.uk" myMail.TextBody = EMailText myMail.Send Set myMail = nothing if "" & AlertMessage <> "" then AlertMessage = AlertMessage & "\r\n" end if AlertMessage = AlertMessage & AlertText else response.write EMailText end if end sub ' end of code added TJS 16/09/14 ' start of code added TJS 26/07/16 '------------------------------------------------------------------------------------------------------- function CheckLinkedAcctsOK(AcctsIntegration, AccountCount) if (AcctsIntegration = "Xero" or AcctsIntegration = "KashFlow" or AcctsIntegration = "Reviso" or _ AcctsIntegration = "Twinfield" or AcctsIntegration = "JustAccts" or AcctsIntegration = "LiquidAccts" or _ AcctsIntegration = "ExactOnline" or AcctsIntegration = "QuickBooks") and AccountCount > 0 then ' TJS 07/04/20 CheckLinkedAcctsOK = true else CheckLinkedAcctsOK = false end if end function '------------------------------------------------------------------------------------------------------- function AccountCodeIsExpense(AcctsIntegration, AccountType) if ((AccountType = "DIRECTCOSTS" or AccountType = "OVERHEADS" or AccountType = "EXPENSE" or AccountType = "SALES") and _ AcctsIntegration = "Xero") or _ ((AccountType = "2" or AccountType = "3") and AcctsIntegration = "KashFlow") or _ (lCase(AccountType) = "profitandloss" and AcctsIntegration = "Reviso") or _ (AccountType = "P&L" and AcctsIntegration = "Twinfield") or _ (AccountType = "P&L" and AcctsIntegration = "JustAccts") or _ (AccountType = "P&L" and AcctsIntegration = "LiquidAccts") or _ (CompareNumericAccountType(AccountType, 110, 999, false, false, true) and AcctsIntegration = "ExactOnline") or _ (AccountType = "Expense" and AcctsIntegration = "QuickBooks") then ' TJS 26/02/20 TJS 07/04/20 AccountCodeIsExpense = true else AccountCodeIsExpense = false end if end function '------------------------------------------------------------------------------------------------------- function AccountCodeIsBalSheet(AcctsIntegration, AccountType) if ((AccountType = "CURRENT" or AccountType = "FIXED" or AccountType = "CURRLIAB" or AccountType = "TERMLIAB" or _ AccountType = "EQUITY" or AccountType = "BANK" or AccountType = "DEPRECIATN" or AccountType = "LIABILITY" or _ AccountType = "NONCURRENT" or AccountType = "OTHERINCOME" or AccountType = "PREPAYMENT") and AcctsIntegration = "Xero") or _ (AccountType = "5" and AcctsIntegration = "KashFlow") or _ (lCase(AccountType) = "status" and AcctsIntegration = "Reviso") or _ (AccountType = "BS" and AcctsIntegration = "Twinfield") or _ (AccountType = "BAL" and AcctsIntegration = "JustAccts") or _ (AccountType = "BAL" and AcctsIntegration = "LiquidAccts") or _ (CompareNumericAccountType(AccountType, 110, 999, true, false, false) and AcctsIntegration = "ExactOnline") or _ ((AccountType = "Asset" or AccountType = "Equity" or AccountType = "Liability" or AccountType = "Accounts Payable" or AccountType = "Bank") and AcctsIntegration = "QuickBooks") then ' TJS 25/02/20 TJS 07/04/20 AccountCodeIsBalSheet = true else AccountCodeIsBalSheet = false end if end function '------------------------------------------------------------------------------------------------------- function CompareNumericAccountType(AccountType, LowerLimit, UpperLimit, LT, EQ, GT) CompareNumericAccountType = false if IsNumeric(AccountType) then if cLng(AccountType) < LowerLimit and LT then CompareNumericAccountType = true elseif cLng(AccountType) >= LowerLimit and cLng(AccountType) <= UpperLimit and EQ then CompareNumericAccountType = true elseif cLng(AccountType) > LowerLimit and GT then CompareNumericAccountType = true end if end if end function '------------------------------------------------------------------------------------------------------- ' end of code added TJS 26/07/16 ' start of code added TJS 08/04/17 sub HandleErrors(ByVal AccountIntegration, EMailText, AlertText) ' TJS 10/04/17 if "" & EMailText <> "" then SendErrorEmail AccountsIntegration, EMailText, AlertText ' TJS 10/04/17 else if "" & AlertMessage <> "" then AlertMessage = AlertMessage & "\r\n" end if AlertMessage = AlertMessage & AlertText end if end sub '------------------------------------------------------------------------------------------------------- ' end of code added TJS 08/04/17 ' start of function created using code from below TJS 23/04/20 sub AddNominalCodes(ByRef rsNominalCodes, ByVal AccountsIntegration, ByVal AccountCount, ByVal FieldName, ByVal ControlName) if CheckLinkedAcctsOK(AccountsIntegration, AccountCount) then ' TJS 03/12/15 TJS 26/07/16%> <%if not rsNominalCodes.BOF then rsNominalCodes.movefirst end if do while not rsNominalCodes.EOF ' start of code added TJS 23/04/20 if AccountsIntegration = "QuickBooks" and left(rsNominalCodes("Nominal_Code"), 3) = "QB-" then%> <%else ' end of code added TJS 23/04/20%> <%end if rsNominalCodes.movenext loop%> <%if not rsAccountList.BOF then ' TJS 26/07/16 rsAccountList.movefirst ' TJS 26/07/16 end if do while not rsAccountList.EOF ' TJS 26/07/16 if AccountCodeIsExpense(AccountsIntegration, rsAccountList("AcctType")) then ' TJS 19/12/15 TJS 26/07/16 bCodeInUse = false if not rsNominalCodes.BOF then rsNominalCodes.movefirst end if do while not rsNominalCodes.EOF if rsNominalCodes("Nominal_Code") = rsAccountList("AcctCode") then ' TJS 26/07/16 bCodeInUse = true ' has Account Name been changed in linked system ? if rsNominalCodes("Display_Name") <> rsAccountList("AcctName") then ' TJS 26/07/16 ' yes, update our copy sql = "UPDATE " & DBSchema & "Nominal_Codes SET Display_Name = '" & ConvertQuoteChars(Left(rsAccountList("AcctName"), 50)) & "' WHERE Nominal_Code_ID = " & rsNominalCodes("Nominal_Code_ID") ' TJS 26/07/16 ExecuteSQL objConn, sql end if exit do end if rsNominalCodes.movenext loop if not bCodeInUse then ' start of code added TJS 23/04/20 if AccountsIntegration = "QuickBooks" and left(rsAccountList("AcctCode"), 3) = "QB-" then%> <%else ' end of code added TJS 23/04/20%> <%end if end if elseif AccountCodeIsBalSheet(AccountsIntegration, rsAccountList("AcctType")) then ' TJS 03/12/15 TJS 26/07/16 bCodeInUse = false if not rsNominalCodes.BOF then rsNominalCodes.movefirst end if do while not rsNominalCodes.EOF if rsNominalCodes("Nominal_Code") = rsAccountList("AcctCode") then ' TJS 26/07/16 bCodeInUse = true ' has Account Name been changed in linked system ? if rsNominalCodes("Display_Name") <> rsAccountList("AcctName") then ' TJS 26/07/16 ' yes, update our copy sql = "UPDATE " & DBSchema & "Nominal_Codes SET Display_Name = '" & ConvertQuoteChars(Left(rsAccountList("AcctName"), 50)) & "' WHERE Nominal_Code_ID = " & rsNominalCodes("Nominal_Code_ID") ' TJS 26/07/16 ExecuteSQL objConn, sql end if exit do end if rsNominalCodes.movenext loop if not bCodeInUse then ' start of code added TJS 23/04/20 if AccountsIntegration = "QuickBooks" and left(rsAccountList("AcctCode"), 3) = "QB-" then%> <%else ' end of code added TJS 23/04/20%> <%end if end if elseif (rsAccountList("AcctType") = "4" and AccountsIntegration = "KashFlow") then ' TJS 26/07/16 bCodeInUse = false if not rsNominalCodes.BOF then rsNominalCodes.movefirst end if do while not rsNominalCodes.EOF if rsNominalCodes("Nominal_Code") = rsAccountList("AcctCode") then ' TJS 26/07/16 bCodeInUse = true ' has Account Name been changed in linked system ? if rsNominalCodes("Display_Name") <> rsAccountList("AcctName") then ' TJS 26/07/16 ' yes, update our copy sql = "UPDATE " & DBSchema & "Nominal_Codes SET Display_Name = '" & ConvertQuoteChars(Left(rsAccountList("AcctName"), 50)) & "' WHERE Nominal_Code_ID = " & rsNominalCodes("Nominal_Code_ID") ' TJS 26/07/16 ExecuteSQL objConn, sql end if exit do end if rsNominalCodes.movenext loop if not bCodeInUse then ' start of code added TJS 23/04/20 if AccountsIntegration = "QuickBooks" and left(rsAccountList("AcctCode"), 3) = "QB-" then%> <%else ' end of code added TJS 23/04/20%> <%end if end if else bCodeInUse = false if not rsNominalCodes.BOF then rsNominalCodes.movefirst end if do while not rsNominalCodes.EOF if rsNominalCodes("Nominal_Code") = rsAccountList("AcctCode") then ' TJS 26/07/16 bCodeInUse = true exit do end if rsNominalCodes.movenext loop if not bCodeInUse then ' start of code added TJS 23/04/20 if AccountsIntegration = "QuickBooks" and left(rsAccountList("AcctCode"), 3) = "QB-" then%> <%else ' end of code added TJS 23/04/20%> <%end if end if end if rsAccountList.movenext ' TJS 26/07/16 loop ' TJS 26/07/16 else%> <%if rsNominalCodes.EOF and not rsNominalCodes.BOF then rsNominalCodes.movefirst end if do while not rsNominalCodes.EOF%> <%rsNominalCodes.movenext loop end if end sub '------------------------------------------------------------------------------------------------------- ' end of function created using code from below TJS 23/04/20 NoRecordsFound = False EmployerPensionSchemeID = -1 ' TJS 01/06/14 AutoEnrolPensionSchemeID = -1 ' TJS 01/06/14 if EmployerID < 0 then NoRecordsFound = True else ' start of code moved TJS 20/01/11 ' find if Employer has pension scheme for current payroll sql = "SELECT Payroll_Type, Tax_Year, Tax_Period, Last_Tax_Period, Pension_Scheme_ID, Auto_Enrol_Pension_ID, Enable_Multiple_Pensions " ' TJS 01/06/14 TJS 29/09/14 TJS 17/07/16 sql = sql & "FROM " & DBSchema & "Payrolls WHERE Employer_ID = " & EmployerID & " AND Payroll_ID = " & PayrollID set rsPayroll = OpenRecordset(objConn, sql) bMultiplePensionsEnabled = cBool(rsPayroll("Enable_Multiple_Pensions")) ' TJS 17/07/16 if bMultiplePensionsEnabled then ' TJS 17/07/16 EmployerPensionSchemeID = -1 ' TJS 17/07/16 AutoEnrolPensionSchemeID = -1 ' TJS 17/07/16 else EmployerPensionSchemeID = rsPayroll("Pension_Scheme_ID") AutoEnrolPensionSchemeID = rsPayroll("Auto_Enrol_Pension_ID") ' TJS 01/06/14 end if ' start of code added TJS 29/09/14 select case rsPayroll("Payroll_Type") case "W" if RerunPayroll = "Yes" then PayrollPeriod = "Payroll re-run of Week " & rsPayroll("Last_Tax_Period") else PayrollPeriod = "Week " & rsPayroll("Tax_Period") end if case "B" if RerunPayroll = "Yes" then PayrollPeriod = "Payroll re-run of Week " & rsPayroll("Last_Tax_Period") * 2 else PayrollPeriod = "Week " & rsPayroll("Tax_Period") * 2 end if case "F" if RerunPayroll = "Yes" then PayrollPeriod = "Payroll re-run of Week " & rsPayroll("Last_Tax_Period") * 4 else PayrollPeriod = "Week " & rsPayroll("Tax_Period") * 4 end if case "M" if RerunPayroll = "Yes" then PayrollPeriod = "Payroll re-run of Month " & rsPayroll("Last_Tax_Period") else PayrollPeriod = "Month " & rsPayroll("Tax_Period") end if case "Q" if RerunPayroll = "Yes" then PayrollPeriod = "Payroll re-run of Quarter " & rsPayroll("Last_Tax_Period") else PayrollPeriod = "Quarter " & rsPayroll("Tax_Period") end if case "A" PayrollPeriod = "Year " & rsPayroll("Tax_Year") & "/" & right(rsPayroll("Tax_Year") + 1, 2) end select ' end of code added TJS 29/09/14 ' is there an Employer Pension scheme and no pension delected for display and not adding a new scheme ? if PensionSchemeID < 0 and EmployerPensionSchemeID >= 0 and AddNewEdit <> "Add" then ' TJS 20/01/11 ' yes, display Employer scheme PensionSchemeID = EmployerPensionSchemeID ' TJS 20/01/11 end if ' is there an Auto Enrolment Pension scheme and no pension delected for display and not adding a new scheme ? if PensionSchemeID < 0 and AutoEnrolPensionSchemeID >= 0 and AddNewEdit <> "Add" then ' TJS 01/06/14 ' yes, display Auto Enrolment scheme PensionSchemeID = AutoEnrolPensionSchemeID ' TJS 01/06/14 end if ' are we displaying the Company Pension scheme ? SchemeIsCompany = false if EmployerPensionSchemeID >= 0 then if cLng(PensionSchemeID) = cLng(EmployerPensionSchemeID) then SchemeIsCompany = true end if end if if AutoEnrolPensionSchemeID >= 0 then ' TJS 01/06/14 if cLng(PensionSchemeID) = cLng(AutoEnrolPensionSchemeID) then ' TJS 01/06/14 SchemeIsCompany = true ' TJS 01/06/14 end if end if ' get count of Employee schemes (ignore Employer pension schemes) sql = "SELECT COUNT(*) FROM " & DBSchema & "Pension_Schemes WHERE Employer_ID = " & EmployerID sql = sql & " AND Employee_ID = " & EmployeeID & " AND Deleted = 0" ' TJS 26/02/10 set rsPensDetails = OpenRecordset(objConn, sql) PensionSchemeCount = cLng(rsPensDetails(0)) rsPensDetails.close ' find Employee personal pension schemes sql = "SELECT Pension_Scheme_ID, Scheme_Name, Contract_Out_Cert, Scheme_Type, Employee_Contrib_Rate, " sql = sql & "Employer_Contrib_Rate FROM " & DBSchema & "Pension_Schemes WHERE Employer_ID = " & EmployerID sql = sql & " AND Employee_ID = " & EmployeeID & " AND Deleted = 0" ' TJS 26/02/10 set rsPensList = OpenRecordset(objConn, sql) ' is there an Employee Pension scheme and no pension delected for display and not adding a new scheme ? if PensionSchemeID < 0 and PensionSchemeCount > 0 and AddNewEdit <> "Add" then ' TJS 20/01/11 ' yes, display first Employee scheme PensionSchemeID = rsPensList("Pension_Scheme_ID") ' TJS 20/01/11 end if ' end of code moved TJS 20/01/11 bHasAEContributions = false ' TJS 25/04/17 bThisAndFuture = true ' DC 08/03/18 sql = "SELECT Pay_Type_ID, Display_Name, Emplee_Pens_this_Period, Empler_Pens_this_Period, Emplee_Pens_last_Period, Empler_Pens_last_Period, " ' TJS 29/04/16 sql = sql & "Emplee_Pens_next_Period, Empler_Pens_next_Period FROM " & DBSchema & "Employee_Pay_Items WHERE Earning_or_Deduction = 'D' " ' TJS 29/04/16 DC 08/03/18 sql = sql & "AND Pension_Item = -1 AND Calculated_Pay_Item = 0 AND Employee_ID = " & EmployeeID & " AND Employer_ID = " & EmployerID sql = sql & " AND Payroll_ID = " & PayrollID & " AND Pension_Scheme_ID = " & PensionSchemeID ' TJS 17/07/07 sql = sql & " AND Pension_AVC_Item = 0 AND Deleted = 0 ORDER BY Sort_Order" ' TJS 20/01/11 set rsLumpSumPayItem = OpenRecordset(objConn, sql) if FailureFields <> "" and (AddNewEdit = "Add" or AddNewEdit = "Edit") then LumpSumPayTypeID = RemoveHTML(request.form("LumpSumPayTypeID")) ' TJS 25/01/10 LumpSumEmployer = RemoveHTML(request.form("LumpSumEmployer")) ' TJS 25/01/10 LumpSumEmployee = RemoveHTML(request.form("LumpSumEmployee")) ' TJS 25/01/10 elseif not rsLumpSumPayItem.EOF then LumpSumPayTypeID = rsLumpSumPayItem("Pay_Type_ID") ' start of code added TJS 29/04/16 if RerunPayroll = "Yes" then LumpSumEmployer = FormatCurrency(rsLumpSumPayItem("Empler_Pens_last_Period"), 2, -1, 0, 0) ' TJS 04/05/16 LumpSumEmployee = FormatCurrency(rsLumpSumPayItem("Emplee_Pens_last_Period"), 2, -1, 0, 0) ' TJS 04/05/16 if cLng(rsLumpSumPayItem("Empler_Pens_this_Period")) + cLng(rsLumpSumPayItem("Emplee_Pens_this_Period")) = 0 then ' DC 08/03/18 bThisAndFuture = false ' DC 08/03/18 end if else ' end of code added TJS 29/04/16 LumpSumEmployer = FormatCurrency(rsLumpSumPayItem("Empler_Pens_this_Period"), 2, -1, 0, 0) LumpSumEmployee = FormatCurrency(rsLumpSumPayItem("Emplee_Pens_this_Period"), 2, -1, 0, 0) if cLng(rsLumpSumPayItem("Empler_Pens_next_Period")) + cLng(rsLumpSumPayItem("Emplee_Pens_next_Period")) = 0 then ' DC 08/03/18 bThisAndFuture = false ' DC 08/03/18 end if end if else LumpSumPayTypeID = -1 LumpSumEmployer = "" LumpSumEmployee = "" end if rsLumpSumPayItem.close sql = "SELECT Pay_Type_ID, Display_Name, Emplee_Pens_this_Period, Emplee_Pens_last_Period FROM " & DBSchema & "Employee_Pay_Items " ' TJS 29/04/16 sql = sql & "WHERE Earning_or_Deduction = 'D' AND Pension_Item = -1 AND Calculated_Pay_Item = 0 AND Employer_ID = " & EmployerID ' TJS 14/09/18 sql = sql & " AND Employee_ID = " & EmployeeID & " AND Payroll_ID = " & PayrollID & " AND Pension_Scheme_ID = " sql = sql & PensionSchemeID & " AND Pension_AVC_Item = -1 AND Deleted = 0 ORDER BY Sort_Order" ' TJS 20/01/11 set rsLumpSumPayItem = OpenRecordset(objConn, sql) if FailureFields <> "" and (AddNewEdit = "Add" or AddNewEdit = "Edit") then LumpSumAVCPayTypeID = RemoveHTML(request.form("LumpSumAVCPayTypeID")) ' TJS 25/01/10 LumpSumAVCEmployee = RemoveHTML(request.form("LumpSumAVCEmployee")) ' TJS 25/01/10 elseif not rsLumpSumPayItem.EOF then LumpSumAVCPayTypeID = rsLumpSumPayItem("Pay_Type_ID") ' start of code added TJS 29/04/16 if RerunPayroll = "Yes" then LumpSumAVCEmployee = FormatCurrency(rsLumpSumPayItem("Emplee_Pens_last_Period"), 2, -1, 0, 0) else ' end of code added TJS 29/04/16 LumpSumAVCEmployee = FormatCurrency(rsLumpSumPayItem("Emplee_Pens_this_Period"), 2, -1, 0, 0) end if else LumpSumAVCPayTypeID = -1 LumpSumAVCEmployee = "" end if rsLumpSumPayItem.close set rsLumpSumPayItem = nothing sql = "SELECT Pay_Type_ID, Display_Name, Emplee_Pens_this_Period, Empler_Pens_this_Period, Emplee_Pens_last_Period, Empler_Pens_last_Period FROM " ' TJS 29/04/16 sql = sql & DBSchema & "Employee_Pay_Items WHERE Earning_or_Deduction = 'D' AND Pension_Item = -1 AND Calculated_Pay_Item = -1 AND " ' TJS 29/04/16 sql = sql & "Employer_ID = " & EmployerID & " AND Employee_ID = " & EmployeeID & " AND Payroll_ID = " & PayrollID & " AND Pension_Scheme_ID = " ' TJS 14/09/18 sql = sql & PensionSchemeID & " AND Pension_AVC_Item = 0 AND Deleted = 0 ORDER BY Sort_Order" ' TJS 20/01/11 set rsPercentPayItem = OpenRecordset(objConn, sql) if FailureFields <> "" and (AddNewEdit = "Add" or AddNewEdit = "Edit") then PercentPayTypeID = RemoveHTML(request.form("PercentPayTypeID")) ' TJS 25/01/10 PercentEmployer = RemoveHTML(request.form("PercentEmployer")) ' TJS 25/01/10 PercentEmployee = RemoveHTML(request.form("PercentEmployee")) ' TJS 25/01/10 elseif not rsPercentPayItem.EOF then PercentPayTypeID = rsPercentPayItem("Pay_Type_ID") ' start of code added TJS 29/04/16 if RerunPayroll = "Yes" then PercentEmployer = FormatNumber(rsPercentPayItem("Empler_Pens_last_Period"), 2) ' DC 21/04/17 PercentEmployee = FormatNumber(rsPercentPayItem("Emplee_Pens_last_Period"), 2) ' DC 21/04/17 else ' end of code added TJS 29/04/16 PercentEmployer = FormatNumber(rsPercentPayItem("Empler_Pens_this_Period"), 2) ' & "%" DC 19/07/12 DC 21/04/17 PercentEmployee = FormatNumber(rsPercentPayItem("Emplee_Pens_this_Period"), 2) ' & "%" DC 19/07/12 DC 21/04/17 end if else PercentPayTypeID = -1 PercentEmployer = "" PercentEmployee = "" end if rsPercentPayItem.close ' are extra functions enabled ? sql = "SELECT " & EmployerTableMenuOptionFields & ", Billing_Method, Invoice_Agency, Enable_Extra_Functions, Extra_Funct_Next_Inv, Accounts_Integration, " ' TJS 13/02/07 TJS 21/09/07 TJS 15/07/14 TJS 18/12/19 sql = sql & "Default_Nominal_Codes, Pension_Cost_Nominal, Pension_Due_Nominal, Enable_Pension_AE_Forms FROM " & DBSchema & "Employers WHERE Employer_ID = " & EmployerID ' TJS 13/02/07 TJS 16/09/14 TJS 12/05/17 set rsEmployer = OpenRecordset(objConn, sql) ' TJS 13/02/07 EnableExtraFunctions = cBool(rsEmployer("Enable_Extra_Functions")) ' TJS 13/02/07 if "" & rsEmployer("Extra_Funct_Next_Inv") <> "" then ' TJS 13/02/07 ExtraFunctNextInvoice = rsEmployer("Extra_Funct_Next_Inv") ' TJS 13/02/07 if rsEmployer("Extra_Funct_Next_Inv") < Date() and rsEmployer("Billing_Method") <> "F" and (rsEmployer("Billing_Method") <> "A" _ or (rsEmployer("Billing_Method") = "A" and not cBool(rsEmployer("Invoice_Agency")))) then ' TJS 21/09/07 EnableExtraFunctions = false ' TJS 21/09/07 else ExtraFunctNextInvoice = dateadd("d", 1, Date()) ' TJS 21/09/07 end if else ' TJS 13/02/07 ExtraFunctNextInvoice = Date() ' TJS 13/02/07 end if ' TJS 13/02/07 EmployerPensionAEFormat = "" & rsEmployer("Auto_Enrolment_Format") ' TJS 15/07/14 AccountsIntegration = "" & rsEmployer("Accounts_Integration") ' TJS 16/09/14 DefaultNominalCodes = cBool(rsEmployer("Default_Nominal_Codes")) ' TJS 16/09/14 PensionCostNominal = "" & rsEmployer("Pension_Cost_Nominal") ' TJS 16/09/14 PensionDueNominal = "" & rsEmployer("Pension_Due_Nominal") ' TJS 16/09/14 bEnablePensionAELetters = cBool(rsEmployer("Enable_Pension_AE_Forms")) ' TJS 12/05/17 sql = "SELECT Pay_Type_ID, Display_Name, Emplee_Pens_this_Period, Empler_Pens_this_Period, Emplee_Pens_last_Period, Empler_Pens_last_Period " ' TJS 29/04/16 sql = sql & "FROM " & DBSchema & "Employee_Pay_Items WHERE Earning_or_Deduction = 'D' AND Pension_Item = -1 AND Calculated_Pay_Item = -1 " ' TJS 29/04/16 sql = sql & "AND Employee_ID = " & EmployeeID & " AND Payroll_ID = " & PayrollID & " AND Employer_ID = " & EmployerID ' TJS 14/09/18 sql = sql & " AND Pension_Scheme_ID = " & PensionSchemeID & " AND Pension_AVC_Item = -1 AND Deleted = 0 ORDER BY Sort_Order" ' TJS 20/01/11 set rsPercentPayItem = OpenRecordset(objConn, sql) if FailureFields <> "" and (AddNewEdit = "Add" or AddNewEdit = "Edit") then PercentAVCPayTypeID = RemoveHTML(request.form("PercentAVCPayTypeID")) ' TJS 25/01/10 PercentAVCEmployee = RemoveHTML(request.form("PercentAVCEmployee")) ' TJS 25/01/10 elseif not rsPercentPayItem.EOF then PercentAVCPayTypeID = rsPercentPayItem("Pay_Type_ID") ' start of code added TJS 29/04/16 if RerunPayroll = "Yes" then PercentAVCEmployee = FormatNumber(rsPercentPayItem("Emplee_Pens_last_Period"), 1) & "%" else ' end of code added TJS 29/04/16 PercentAVCEmployee = FormatNumber(rsPercentPayItem("Emplee_Pens_this_Period"), 1) & "%" end if else PercentAVCPayTypeID = -1 PercentAVCEmployee = "" end if rsPercentPayItem.close set rsPercentPayItem = nothing sql = "SELECT Pension_Scheme_ID, Employee_ID, Scheme_Name, Contract_Out_Cert, Scheme_Type, Policy_Number, Contribution_paid_Gross, " ' TJS 20/01/11 sql = sql & "Employee_Contrib_Rate, Employer_Contrib_Rate, Lump_Sum_AVC_Allowed, Percentage_AVC_Allowed, Directors_APP, " sql = sql & "Pay_Contrib_On_Parental_Pay, Salary_Sacrifice_Employer_NI_Percent, Auto_Enrolment, Auto_Enrolment_Format, " ' TJS 13/12/10 TJS 24/04/12 TJS 01/06/14 TJS 15/07/14 sql = sql & "Pension_Cost_Nominal, Pension_Due_Nominal, Pay_Due_Nominal, Bank_Name, Bank_Sort_Code, Bank_Account, Bank_Account_Name FROM " sql = sql & DBSchema & "Pension_Schemes WHERE Employer_ID = " & EmployerID & " AND Pension_Scheme_ID = " & PensionSchemeID & " AND Deleted = 0" ' TJS 26/02/10 set rsPensDetails = OpenRecordset(objConn, sql) if rsPensDetails.EOF then rsPensDetails.close set rsPensDetails = nothing NoRecordsFound = True if AddNewEdit = "Edit" then AddNewEdit = "Add" end if ' start of code moved from ShowPensAEField TJS 29/09/16 else ' are AE settings defined on Employer record ? if EmployerPensionAEFormat = "" or EmployerPensionAEFormat = "-1" then ' no, get from Pension Scheme PensionAEFormat = rsPensDetails("Auto_Enrolment_Format") ' TJS 08/11/16 else ' yes PensionAEFormat = cLng(EmployerPensionAEFormat) ' TJS 08/11/16 end if ' end of code moved from ShowPensAEField TJS 29/09/16 ' start of code added TJS 25/04/17 if not SchemeIsCompany and bMultiplePensionsEnabled then if rsPensDetails("Employee_ID") < 0 then SchemeIsCompany = true end if end if if SchemeIsCompany and cBool(rsPensDetails("Auto_Enrolment")) then if "" & LumpSumEmployer <> "" then if IsValueGtZero(LumpSumEmployer) then ' TJS 17/09/17 bHasAEContributions = true end if end if if "" & LumpSumEmployee <> "" then if IsValueGtZero(LumpSumEmployee) then ' TJS 17/09/17 bHasAEContributions = true end if end if if "" & PercentEmployer <> "" then if IsValueGtZero(PercentEmployer) then ' TJS 17/09/17 bHasAEContributions = true end if end if if "" & PercentEmployee <> "" then if IsValueGtZero(PercentEmployee) then ' TJS 17/09/17 bHasAEContributions = true end if end if end if ' end of code added TJS 25/04/17 end if ' start of code added TJS 16/09/14 sql = "SELECT Nominal_Code_ID, Nominal_Code, Display_Name, Balance_Sheet_Item FROM " & DBSchema & "Nominal_Codes WHERE (Employer_ID = " if DefaultNominalCodes then sql = sql & "-1 OR Employer_ID = " end if sql = sql & EmployerID & ") ORDER BY Nominal_Code" set rsNominalCodes = OpenRecordset(objConn, sql) ' end of code added TJS 16/09/14 end if ' start of code added TJS 09/01/13 sql = "SELECT " & UserTableMenuPermissionFields & ", Edit_Employee_Pensions_Tab FROM " & DBSchema & "Users INNER JOIN " & DBSchema ' TJS 01/06/14 TJS 26/11/15 sql = sql & "User_Permissions ON Users.User_ID = User_Permissions.User_ID AND (Employee_Employer_ID = '' OR Employee_Employer_ID = '" ' TJS 26/11/15 sql = sql & EncodeEmployerID(UserPermissionsTable, EmployerID) & "') WHERE Users.User_ID = " & GetSessionVar("UserID") ' TJS 26/11/15 sql = sql & " AND User_Permissions.Deleted = 0 ORDER BY Employee_Employer_ID DESC" ' TJS 26/11/15 set rsUser = OpenRecordset(objConn, sql) ' start of code added TJS 08/12/14 and moved TJS 17/07/16 sql = "SELECT Auto_Enroll_ID, Auto_Enrolment_Status, Enrolment_Date, Opt_Out_Date, Next_Auto_Enroll_Date, Pension_Scheme_ID, Sent_To_Provider FROM " ' TJS 17/07/16 TJS 03/05/17 sql = sql & DBSchema & "Employee_Pension_Auto_Enroll WHERE Employer_ID = " & EmployerID & " AND Payroll_ID = " & PayrollID ' TJS 02/06/15 sql = sql & " AND Employee_ID = " & EmployeeID & " AND (Pension_Scheme_ID = " & PensionSchemeID ' DC 13/10/17 sql = sql & " OR Pension_Scheme_ID = -1) AND Deleted = 0 AND (Assesment_Tax_Year < " & rsPayroll("Tax_Year") & " OR " ' TJS 02/06/15 DC 13/10/17 sql = sql & "(Assesment_Tax_Year = " & rsPayroll("Tax_Year") & " AND Assesment_Tax_Period <= " if RerunPayroll = "Yes" then sql = sql & rsPayroll("Last_Tax_Period") else sql = sql & rsPayroll("Tax_Period") end if sql = sql & ")) ORDER BY Assesment_Tax_Year DESC, Assesment_Tax_Period DESC" set rsAEStatus = OpenRecordset(objConn, sql) ' end of code added TJS 08/12/14 and moved TJS 17/07/16 ' start of code added TJS 03/05/17 bEnrolmentConfirmed = false if not rsAEStatus.EOF then if "" & rsAEStatus("Auto_Enrolment_Status") = AEStatusEligibleJobholderEnroled and cBool(rsAEStatus("Sent_To_Provider")) then bEnrolmentConfirmed = true end if end if ' end of code added TJS 03/05/17 ' start of code added TJS 17/07/16 bSelectAEPens = false if bMultiplePensionsEnabled and not rsAEStatus.EOF then if (rsAEStatus("Pension_Scheme_ID") < 0 or cLng(rsAEStatus("Pension_Scheme_ID")) = cLng(PensionSchemeID)) then ' multiple pension schemes enabled and employee has AE record with either no pension selected or for currect pension ' must be company scheme SchemeIsCompany = true if rsAEStatus("Pension_Scheme_ID") < 0 then bSelectAEPens = true end if end if end if ' end of code added TJS 17/07/16 if not cBool(rsUser("Edit_Employee_Pensions_Tab")) then ReadOnly = " readonly" AllowChanges = false ContribReadOnly = " readonly" ContribAllowChanges = false ' end of code added TJS 09/01/13 elseif (AddNewEdit <> "Add" and AddNewEdit <> "Edit") or SchemeIsCompany then ReadOnly = " readonly" if SchemeIsCompany then ContribReadOnly = "" ContribAllowChanges = true else ContribReadOnly = " readonly" ContribAllowChanges = false end if AllowChanges = false if (AddNewEdit <> "Add" and AddNewEdit <> "Edit") then ' TJS 20/01/11 AllowAddNew = false ' TJS 20/01/11 else AllowAddNew = true ' TJS 20/01/11 end if else ReadOnly = "" ContribReadOnly = "" AllowChanges = true AllowAddNew = true ' TJS 20/01/11 ContribAllowChanges = true end if sql = "SELECT Title, Last_Name, Fore_Name_1, Fore_Name_2, Payroll_Ref, Employee_Type, Director_Start, Director_End, User_Login_Created, " ' TJS 12/07/19 sql = sql & "Auto_Enrolment_Param_1, Auto_Enrolment_Param_2, Auto_Enrolment_Param_3, Auto_Enrolment_Param_4, " ' TJS 20/08/14 TJS 08/12/14 TJS 02/06/15 TJs 29/09/16 sql = sql & "Orig_Employee_ID FROM " & DBSchema & "Employees WHERE Employee_ID = " & EmployeeID ' TJS 01/06/14 TJS 15/07/14 set rsEmplDetails = OpenRecordset(objConn, sql) EmployeeName = "" if "" & rsEmplDetails("Title") <> "" then EmployeeName = DecryptData(rsEmplDetails("Title")) end if if "" & rsEmplDetails("Fore_Name_1") <> "" then if EmployeeName <> "" then EmployeeName = EmployeeName & " " end if EmployeeName = EmployeeName & DecryptData(rsEmplDetails("Fore_Name_1")) end if if "" & rsEmplDetails("Fore_Name_2") <> "" then if EmployeeName <> "" then EmployeeName = EmployeeName & " " end if EmployeeName = EmployeeName & left(DecryptData(rsEmplDetails("Fore_Name_2")), 1) end if if EmployeeName <> "" then EmployeeName = EmployeeName & " " end if EmployeeName = EmployeeName & DecryptData(rsEmplDetails("Last_Name")) PayrollRef = DecryptData(rsEmplDetails("Payroll_Ref")) EmployeeType = rsEmplDetails("Employee_Type") DirectorStartDate = DecryptData(rsEmplDetails("Director_Start")) DirectorEndDate = DecryptData(rsEmplDetails("Director_End")) UserLoginExists = cBool(rsEmplDetails("User_Login_Created")) ' TJS 12/07/19%> <%' start of code added TJS 16/09/14 ' start of code moved and modified TJS 26/07/16 ' initialise variables dim AccountCount, rsAccountList AccountCount = 0 Set rsAccountList = CreateObject("ADODB.RECORDSET") rsAccountList.Fields.append "AcctCode", adVarChar, 20 rsAccountList.Fields.append "AcctName", adVarChar, 70 ' DC 20/09/16 rsAccountList.Fields.append "AcctType", adVarChar, 20 rsAccountList.Fields.append "AcctClass", adVarChar, 20 rsAccountList.Fields.append "AcctGUID", adVarChar, 40 rsAccountList.CursorType = adOpenStatic rsAccountList.Open varFields = Array("AcctCode", "AcctName", "AcctType", "AcctClass", "AcctGUID") ' end of code moved and modified TJS 26/07/16 ' Is system integrated with Xero Network ? if AccountsIntegration = "Xero" then ' yes, get link details ' start of code replaced TJS 26/07/16 GetXeroAccountCodes XOAuthOK, "Employee Pension Details", "EmployeePensionSchemesJS.asp", false, "EmplPen", "GetEEPJS", EmployerID, "EEPS", false, rsAccountList, AccountCount, _ AlertMessage, false, rsTrackingCategories, SelectedTrackingCategory, rsTrackingOptions, bExtCostCentreChng ' TJS 28/07/16 TJS 10/06/18 TJS 10/06/18 TJS 30/6/21 TJS 18/09/21 ' end of code replaced TJS 26/07/16 elseif AccountsIntegration = "KashFlow" then ' yes, initialise variables GetKashFlowAccountCodes EmployerID, false, rsAccountList, AccountCount, AlertMessage ' TJS 26/07/16 elseif AccountsIntegration = "Reviso" then ' TJS 03/12/15 ' yes, initialise variables GetRevisoAccountCodes RevisoAuthOK, "Employee Pension Details", "EmployeePensionSchemesJS.asp", false, "EmplPen", "GetEEPJS", "EEPS", "EmployeeDetails", _ EmployerID, rsAccountList, AccountCount, AlertMessage ' TJS 26/07/16 TJS 26/02/20 elseif AccountsIntegration = "Twinfield" then ' yes, initialise variables GetTwinfieldAccountCodes EmployerID, rsAccountList, AccountCount, AlertMessage ' TJS 26/07/16 ' start of code added TJS 26/07/16 elseif AccountsIntegration = "JustAccts" then ' yes, get link details sql = "SELECT JustAcct_API_URL, Access_Token_Expires, Expiry_Check_Value FROM " & DBSchema sql = sql & "JustAccounts_Integration WHERE Employer_ID = " & EmployerID & " AND Deleted = 0" ' TJS 19/05/18 set rsJustAcctLink = OpenRecordset(objConn, sql) ' is there a link record ? bJustAcctAPIActive = false if not rsJustAcctLink.EOF then ' yes, is JustAccounts URL set and Access Token up to date ? if rsJustAcctLink("JustAcct_API_URL") <> "" and CompareDates(rsJustAcctLink("Access_Token_Expires"), dateadd(rsJustAcctLink("Expiry_Check_Value"), 1, Date()), false, false, true) then bJustAcctAPIActive = true end if end if if not bJustAcctAPIActive and JAOAuthOK <> "No" then sql = "UPDATE " & DBSchema & "Session_Vars SET GetEEPJS = 'Yes' WHERE Session_Var_ID = '" & left(SessionVarsID, 15) & "'" ExecuteSQL objConn, sql GetJustAcctsAuthToken "Employee Pension Details", "JavaScript/EmployeePensionSchemesJS.asp", "EPS", "EmplPen" rsJustAcctLink.close set rsJustAcctLink = nothing SaveSessionVarsRecord SessionVarsID objConn.close set objConn = nothing response.end end if ' is there a link record ? if not rsJustAcctLink.EOF then ' yes, is JustAccounts API active ? if bJustAcctAPIActive then ' yes, get accounts list from JustAccounts set XMLSubmit = Server.CreateObject("WinHTTP.WinHTTPRequest.5.1") ' TJS 27/06/18 JustAcctGetAccountsAPI = "ID=" & SetPageSessionID(20290, DestFrame) & "&SV=" & SessionVarsID & "&S=" & DestFrame & "&D=" JustAcctGetAccountsAPI = JustAcctGetAccountsAPI & PopupWindow1 & "&ER=" & EmployerID SaveSessionVarsRecord SessionVarsID XMLSubmit.open "POST", "https://" & request.servervariables("SERVER_NAME") & "/Secure/OAuth/GetJustAcctsAccountsList.ashx", False, "", "" XMLSubmit.setRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8" on error resume next XMLSubmit.Send JustAcctGetAccountsAPI if err.number = 0 then on error goto 0 if "" & XMLSubmit.ResponseText <> "" then set XMLResponse = Server.CreateObject("Msxml2.FreeThreadedDOMDocument.3.0") XMLResponse.PreserveWhitespace = True XMLResponse.validateOnParse = True XMLResponse.LoadXML XMLSubmit.ResponseText if not XMLResponse.selectSingleNode("s:Envelope/s:Body/GetNominalsForCompanyResponse/GetNominalsForCompanyResult") is nothing then Set XMLAccounts = XMLResponse.selectNodes("s:Envelope/s:Body/GetNominalsForCompanyResponse/GetNominalsForCompanyResult/a:NominalSummary") BlankCodeSuffix = 1 for each XMLAccount in XMLAccounts AccountCount = AccountCount + 1 if cBool(XMLAccount.selectSingleNode("a:IsBank").Text) then varValues = Array(XMLAccount.selectSingleNode("a:Code").Text, XMLAccount.selectSingleNode("a:Name").Text, _ "BANK", "", "") elseif cBool(XMLAccount.selectSingleNode("a:IsPnL").Text) then varValues = Array(XMLAccount.selectSingleNode("a:Code").Text, XMLAccount.selectSingleNode("a:Name").Text, _ "P&L", "", "") else varValues = Array(XMLAccount.selectSingleNode("a:Code").Text, XMLAccount.selectSingleNode("a:Name").Text, _ "BAL", "", "") end if ' add row to memory table rsAccountList.AddNew varFields, varValues rsAccountList.Update next else SendErrorEmail "JustAccounts", JustAcctGetAccountsAPI & vbcrlf & vbcrlf & "Send to JustAccounts" & vbcrlf & vbcrlf & "Response was" & vbcrlf & vbcrlf & XMLSubmit.ResponseText, _ "Could not read your Account Codes from JustAccounts\r\n\r\nDetails of the problem have been sent to Support" end if else SendErrorEmail "JustAccounts", JustAcctGetAccountsAPI & vbcrlf & vbcrlf & "Send to JustAccounts" & vbcrlf & vbcrlf & "No response received", _ "Could not read your Account Codes from JustAccounts\r\n\r\nDetails of the problem have been sent to Support" end if else SendErrorEmail "JustAccounts", JustAcctGetAccountsAPI & vbcrlf & vbcrlf & "Send to JustAccounts" & vbcrlf & vbcrlf & "Error code was " & err.number & ", " & err.description, _ "Could not read your Account Codes from JustAccounts\r\n\r\nDetails of the problem have been sent to Support" end if end if end if elseif AccountsIntegration = "LiquidAccts" then ' yes, get link details InitialiseLiquidAcctsAPI ' TJS 25/08/21 ' start of code moved to LiquidAccountsAPIFunctions.asp TJS 27/10/16 GetLiquidAccountCodes rsAccountList, AccountCount, AlertMessage ' end of code moved to LiquidAccountsAPIFunctions.asp TJS 27/10/16 elseif AccountsIntegration = "ExactOnline" then ' no, integrated with Exact Online - get link details sql = "SELECT Integration_ID, Access_Token, Access_Token_Expires, Refresh_Token, Exact_Company_ID FROM " sql = sql & DBSchema & "ExactOnline_Integration WHERE Employer_ID = " & EmployerID & " AND Deleted = 0" ' TJS 19/05/18 set rsExactOnlineLink = OpenRecordset(objConn, sql) ' is there a link record ? bExactOnlineActive = false if not rsExactOnlineLink.EOF then ' yes, is Xero API enabled and up to date ? if "" & rsExactOnlineLink("Access_Token") <> "" then bExactOnlineActive = true end if end if if not bExactOnlineActive and EXOAuthOK <> "No" then sql = "UPDATE " & DBSchema & "Session_Vars SET GetEEPJS = 'Yes' WHERE Session_Var_ID = '" & left(SessionVarsID, 15) & "'" ExecuteSQL objConn, sql GetExactOnlineAuthToken "Employee Pension Details", "EmployeePensionSchemesJS.asp", "EEPS", "EmplPen" ' TJS 18/09/21 rsExactOnlineLink.close set rsExactOnlineLink = nothing SaveSessionVarsRecord SessionVarsID objConn.close set objConn = nothing response.end end if ' is there a link record ? if not rsExactOnlineLink.EOF then ' yes, do we have an access token ? EMailText = "" ' TJS 08/04/17 AlertText = "" ' TJS 08/04/17 if "" & rsExactOnlineLink("Access_Token") <> "" and "" & rsExactOnlineLink("Access_Token_Expires") <> "" then ' yes, is it still valid for at least another minute ? if cDate(rsExactOnlineLink("Access_Token_Expires")) < dateadd("s", 60, Now()) then ' no, need to renew it if RenewExactToken(DecryptData(rsExactOnlineLink("Refresh_Token")), rsExactOnlineLink("Integration_ID"), EMailText, AlertText) then ' TJS 08/04/17 rsExactOnlineLink.close ' get refreshed value sql = "SELECT Integration_ID, Access_Token, Access_Token_Expires, Refresh_Token, Exact_Company_ID FROM " sql = sql & DBSchema & "ExactOnline_Integration WHERE Employer_ID = " & EmployerID & " AND Deleted = 0" ' TJS 19/05/18 set rsExactOnlineLink = OpenRecordset(objConn, sql) ' start of code added TJS 08/04/17 else if EMailText <> "" then SendErrorEmail "ExactOnline", EMailText, AlertText else if "" & AlertMessage <> "" then AlertMessage = AlertMessage & "\r\n" end if AlertMessage = AlertMessage & AlertText end if end if ' end of code added TJS 08/04/17 end if end if ' yes, do we still have an access token ? if "" & rsExactOnlineLink("Access_Token") <> "" and "" & rsExactOnlineLink("Access_Token_Expires") <> "" and AlertText = "" then ' TJS 08/04/17 ' yes, is it valid for at least another minute ? if cDate(rsExactOnlineLink("Access_Token_Expires")) > dateadd("s", 60, Now()) then set XMLResponse = GetExactOnlineXMLData(DecryptData(rsExactOnlineLink("Access_Token")), "financial/GLAccounts", _ rsExactOnlineLink("Exact_Company_ID"), "?$select=ID,BalanceSide,Code,Description,Type") ' did we get an error ? if err.number = 0 then ' no, did we get a response ? if not XMLResponse is nothing then ' yes, extract Accounts list details ExtractExactOnlineAcctCodes XMLResponse, rsAccountList, varFields else SendErrorEmail "ExactOnline", "No response received", _ "Could not read your Account Codes from ExactOnline\r\nDetails of the problem have been sent to Support" end if else SendErrorEmail "ExactOnline", XMLToSend & vbcrlf & vbcrlf & "Send to ExactOnline" & vbcrlf & vbcrlf & "Error code was " & err.number & ", " & err.description, _ "Could not read your Account Codes from ExactOnline\r\n\r\nDetails of the problem have been sent to Support" end if end if end if end if rsExactOnlineLink.close set rsExactOnlineLink = nothing ' end of code aded TJS 26/07/16 ' start of code added TJS 07/04/20 elseif "" & rsEmployer("Accounts_Integration") = "QuickBooks" then ' yes, get link details GetQBAccountCodes QuickBooksAuthOK, "Employer Pension Details", "EmployeePensionSchemesJS.asp", false, "EmplPen", "GetEEPJS", "EEPS", "PayrollHome", _ EmployerID, rsAccountList, AccountCount, AlertMessage ' TJS 18/08/21 TJS 18/09/21 ' end of code added TJS 07/04/20 end if ' end of code added TJS 16/09/14 ' start of code added TJS 12/07/19 EEUserID = -1 if UserLoginExists then sql = "SELECT User_ID FROM " & DBSchema & "User_Permissions WHERE Employee_ID = '" & EncodeEmployeeID(UserPermissionsTable, EmployeeID) sql = sql & "' AND (Employee_Employer_ID = '' OR Employee_Employer_ID = '" & EncodeEmployerID(UserPermissionsTable, EmployerID) & "' AND Deleted = 0)" set rsEEUser = OpenRecordset(objConn, sql) if not rsEEUser.EOF then EEUserID = rsEEUser("User_ID") end if rsEEUser.close set rsEEUser = nothing end if ' end of code added TJS 12/07/19 response.Expires = -1500 response.Expiresabsolute = Now() - 2 response.AddHeader "pragma", "no-cache" response.AddHeader "cache-control", "no-store" response.CacheControl = "no-cache" ' start of code added TJS 30/06/11 sql = "UPDATE " & DBSchema & "Session_Vars SET GetEEPJS = 'Yes' WHERE Session_Var_ID = '" & left(SessionVarsID, 15) & "'" ' TJS 10/07/11 ExecuteSQL objConn, sql ' end of code added TJS 30/06/11 %> Employee Pension Details <%if CheckBrowserVersion("MSIE", 8, true, false, false) then ' [if LT IE 8]%> <%end if if CheckBrowserVersion("MSIE", 7, true, true, false) then ' [if lte IE 7]%> <%end if if "" & GetSessionVar("PathModifier") <> "" then%> Partner-CSS.asp" /> <%end if%> <%if bSelectAEPens then ' TJS 17/07/16 sql = "SELECT PS.Pension_Scheme_ID, Scheme_Name FROM " & DBSchema & "Payroll_Pension_Schemes PPS INNER JOIN " & DBSchema & "Pension_Schemes PS " sql = sql & "ON PPS.Pension_Scheme_ID = PS.Pension_Scheme_ID AND PPS.Employer_ID = PS.Employer_ID WHERE PPS.Employer_ID = " & EmployerID sql = sql & " AND Payroll_ID = " & PayrollID & " AND PPS.Deleted = 0 AND PS.Deleted = 0 ORDER BY Scheme_Name" set rsPayrollPens = OpenRecordset(objConn, sql)%>
action="https://<%=request.servervariables("SERVER_NAME")%>/Secure/PensionSchemeSelected.asp"> > "> ">
title="EmployeeID <%=EmployeeID%> PayrollID <%=PayrollID%>"<%end if%>>

Pension Scheme Details for <%=EmployeeName%>, Payroll Ref <%=PayrollRef%>

This employee has been assessed as <%if rsAEStatus("Auto_Enrolment_Status") = AEStatusEligibleJobholderEnroled then%>Eligible Jobholder under Auto Enrolment and marked for Enrolment into a Pension Scheme.<% elseif rsAEStatus("Auto_Enrolment_Status") = AEStatusEligibleJobholderOptedInEarly then%>Eligible Jobholder and has Opted In early to a Pension Scheme.<% elseif rsAEStatus("Auto_Enrolment_Status") = AEStatusNonEligibleJobholderOptedIn then%>Non-eligible Jobholder under Auto Enrolment, but has Opted In to a Pension Scheme.<% elseif rsAEStatus("Auto_Enrolment_Status") = AEStatusWorkerWithoutQualEarningsOptedIn then%>Worker without Qualifying Earnings under Auto Enrolment, but has Opted In to a Pension Scheme.<%end if%>
Select Pension Scheme to join

onclick="updatepension()"<%end if%>>Update Cancel

<%else%>
action="https://<%=request.servervariables("SERVER_NAME")%>/Secure/ASPDotNet/UpdatePensionScheme.ashx"> > "> <%if "" & FailureMessage <> "" then%>

<%=FailureMessage%>

<%end if%>
<%if PensionSchemeCount > 0 or EmployerPensionSchemeID >= 0 or bMultiplePensionsEnabled then ' TJS 17/07/16 ' don't lock this list box when AllowChanges is false as user still needs to be able to view all schemes%> <%end if%> rowcollapsed<%else%>rowdisplayed<%end if%>"> <%' start of code added TJS 16/09/14%> <%' end of code added TJS 16/09/14%> rowdisplayed<%else%>rowcollapsed<%end if%>"> rowdisplayed<%else%>rowcollapsed<%end if%>"> rowdisplayed<%else%>rowcollapsed<%end if%>"> rowdisplayed<%else%>rowcollapsed<%end if%>"> rowdisplayed<%else%>rowcollapsed<%end if%>"> <%' start of code TJS 18/10/16 iParamCount = 0 if ShowPensAEField(1, PensionAEFieldName) then sql = "SELECT Setting_Option_Name, Setting_Option_Value FROM " & DBSchema & "Pension_Provider_Setting_Options WHERE (Employer_ID = -1 " sql = sql & "OR Employer_ID = " & EmployerID & ") AND Pension_Provider_ID = " & PensionAEFormat & " AND (Pension_Scheme_ID = -1 OR " sql = sql & "Pension_Scheme_ID = " & PensionSchemeID & ") AND Setting_Name = 'EEParam1' AND Deleted = 0 ORDER BY Setting_Option_Name" set rsPensOptions = OpenRecordset(objConn, sql) if not rsPensOptions.EOF then do while not rsPensOptions.EOF iParamCount = iParamCount + 1 rsPensOptions.movenext loop if not rsPensOptions.BOF then rsPensOptions.movefirst end if end if end if ' end of code TJS 18/10/16%> rowdisplayed<%else%>rowcollapsed<%end if%>"> <%' start of code TJS 18/10/16 if ShowPensAEField(1, PensionAEFieldName) then rsPensOptions.close set rsPensOptions = nothing end if iParamCount = 0 if ShowPensAEField(2, PensionAEFieldName) then sql = "SELECT Setting_Option_Name, Setting_Option_Value FROM " & DBSchema & "Pension_Provider_Setting_Options WHERE (Employer_ID = -1 " sql = sql & "OR Employer_ID = " & EmployerID & ") AND Pension_Provider_ID = " & PensionAEFormat & " AND (Pension_Scheme_ID = -1 OR " sql = sql & "Pension_Scheme_ID = " & PensionSchemeID & ") AND Setting_Name = 'EEParam2' AND Deleted = 0 ORDER BY Setting_Option_Name" set rsPensOptions = OpenRecordset(objConn, sql) if not rsPensOptions.EOF then do while not rsPensOptions.EOF iParamCount = iParamCount + 1 rsPensOptions.movenext loop if not rsPensOptions.BOF then rsPensOptions.movefirst end if end if end if ' end of code TJS 18/10/16%> rowdisplayed<%else%>rowcollapsed<%end if%>"> <%' start of code TJS 18/10/16 if ShowPensAEField(2, PensionAEFieldName) then rsPensOptions.close set rsPensOptions = nothing end if iParamCount = 0 if ShowPensAEField(3, PensionAEFieldName) then sql = "SELECT Setting_Option_Name, Setting_Option_Value FROM " & DBSchema & "Pension_Provider_Setting_Options WHERE (Employer_ID = -1 " sql = sql & "OR Employer_ID = " & EmployerID & ") AND Pension_Provider_ID = " & PensionAEFormat & " AND (Pension_Scheme_ID = -1 OR " sql = sql & "Pension_Scheme_ID = " & PensionSchemeID & ") AND Setting_Name = 'EEParam3' AND Deleted = 0 ORDER BY Setting_Option_Name" set rsPensOptions = OpenRecordset(objConn, sql) if not rsPensOptions.EOF then do while not rsPensOptions.EOF iParamCount = iParamCount + 1 rsPensOptions.movenext loop if not rsPensOptions.BOF then rsPensOptions.movefirst end if end if end if ' end of code TJS 18/10/16%> rowdisplayed<%else%>rowcollapsed<%end if%>"> <%' start of code TJS 18/10/16 if ShowPensAEField(3, PensionAEFieldName) then rsPensOptions.close set rsPensOptions = nothing end if iParamCount = 0 if "" & PensionAEFormat <> "" then ' TJS 08/04/17 sql = "SELECT Setting_Option_Name, Setting_Option_Value FROM " & DBSchema & "Pension_Provider_Setting_Options WHERE (Employer_ID = -1 " sql = sql & "OR Employer_ID = " & EmployerID & ") AND Pension_Provider_ID = " & PensionAEFormat & " AND (Pension_Scheme_ID = -1 OR " sql = sql & "Pension_Scheme_ID = " & PensionSchemeID & ") AND Setting_Name = 'EEParam4' AND Deleted = 0 ORDER BY Setting_Option_Name" set rsPensOptions = OpenRecordset(objConn, sql) if not rsPensOptions.EOF then do while not rsPensOptions.EOF iParamCount = iParamCount + 1 rsPensOptions.movenext loop if not rsPensOptions.BOF then rsPensOptions.movefirst end if end if end if ' end of code TJS 18/10/16%> rowdisplayed<%else%>rowcollapsed<%end if%>"> <%' start of code TJS 18/10/16 if "" & PensionAEFormat <> "" then ' TJS 08/04/17 rsPensOptions.close set rsPensOptions = nothing end if%>
title="EmployeeID <%=EmployeeID%> PayrollID <%=PayrollID%>"<%end if%>>

Pension Scheme Details for <%=EmployeeName%>, Payroll Ref <%=PayrollRef%>
Select Pension Scheme to display
Scheme Name * <%else%>-readonly<%end if%>" name="SchemeName" onkeydown="checkkeypress(event)" maxlength="30" value="<%=GetDBField("Scheme_Name", "SchemeName")%>" tabindex="2"<%=ReadOnly%>>  
Scheme Type * help.png" width="16" height="17" alt="Item Help" onclick="gohelp(9)" style="cursor:pointer;"> <%if (EmployeeType = "L" or EmployeeType = "P" or "" & DirectorEndDate <> "") and not SchemeIsCompany then%> tabindex="4" onclick="<%if AllowChanges then%>datachanged()<%else%>undoclick(this)<%end if%>"> <%else%>   <%end if%>
Scheme Policy No. <%else%>-readonly<%end if%>" name="PolicyNumber" onkeydown="checkkeypress(event)" maxlength="30" value="<%=GetDBField("Policy_Number", "PolicyNumber")%>" tabindex="5"<%=ReadOnly%>>  ON<%end if%>" >
Contributions paid help.png" width="16" height="17" style="cursor:<%if instr(Request.ServerVariables("http_user_agent"), " MSIE ") > 0 or instr(Request.ServerVariables("http_user_agent"), "Firefox") > 0 then%>pointer<%else%>hand<%end if%>;" alt="Item Help" onclick="gohelp(9)">
 
Employer Pension Cost Nominal Code * help.png" width="16" height="17" style="cursor:pointer;" alt="Item Help" onclick="gohelp(23)">
Pension Due Nominal Code * help.png" width="16" height="17" style="cursor:pointer;" alt="Item Help" onclick="gohelp(23)">
 
Pension Scheme Bank Details
Bank Name <%else%>-readonly<%end if%>" name="BankName" maxlength="50" value="<%=GetDBField("Bank_Name", "BankName")%>" tabindex="13"<%=ReadOnly%>>  
Sort Code <%else%>-readonly<%end if%>" name="BankSortCode" placeholder="xx-xx-xx" maxlength="8" value="<%=GetDBField("Bank_Sort_Code", "BankSortCode")%>" tabindex="14"<%=ReadOnly%>> Account <%else%>-readonly<%end if%>" name="BankAccount" placeholder="xxxxxxxx" maxlength="8" value="<%=GetDBField("Bank_Account", "BankAccount")%>" tabindex="15"<%=ReadOnly%>>  
Account Name <%else%>-readonly<%end if%>" name="BankAccountName" maxlength="50" value="<%=GetDBField("Bank_Account_Name", "BankAccountName")%>" tabindex="16"<%=ReadOnly%>>  
 
Auto Enrolment Status
as at <%=PayrollPeriod%> ">
Enrolment Date * <%else%>-readonly<%end if%>" value="<%=GetDBField("Enrolment_Date", "AutoEnrolEnrolled")%>"<%=ContribReadOnly%> placeholder="dd/mm/yyyy" tabindex="18" onkeydown="checkkeypress(event)" >
Postponed Until * <%else%>-readonly<%end if%>" value="<%=GetDBField("Next_Auto_Enroll_Date", "AutoEnrolPostpone")%>"<%=ContribReadOnly%> placeholder="dd/mm/yyyy" tabindex="19" onkeydown="checkkeypress(event)" >
Opt-In Date * <%else%>-readonly<%end if%>" value="<%=GetDBField("Enrolment_Date", "AutoEnrolOptIn")%>"<%=ContribReadOnly%> placeholder="dd/mm/yyyy" tabindex="20" onkeydown="checkkeypress(event)" >
<%if "" & GetDBField("Auto_Enrolment_Status", "AutoEnrolStatus") = AEStatusEligibleJobholderOptedOut then%>Opt-Out<%else%>Stop. Contrib.<%end if%> Date * <%else%>-readonly<%end if%>" value="<%=GetDBField("Opt_Out_Date", "AutoEnrolOptOut")%>"<%=ContribReadOnly%> placeholder="dd/mm/yyyy" tabindex="21" onkeydown="checkkeypress(event)" >
<%=PensionAEFieldName%> <%' start of code TJS 18/10/16 if iParamCount > 0 then%> <%else ' end of code TJS 18/10/16%> <%else%>-readonly<%end if%>" value="<%=GetDBField("Auto_Enrolment_Param_1", "AutoEnrolParam1")%>"<%=ContribReadOnly%> maxlength="25" tabindex="22" onkeydown="checkkeypress(event)" > <%end if%>
<%=PensionAEFieldName%> <%' start of code TJS 18/10/16 if iParamCount > 0 then%> <%else ' end of code TJS 18/10/16%> <%else%>-readonly<%end if%>" value="<%=GetDBField("Auto_Enrolment_Param_2", "AutoEnrolParam2")%>"<%=ContribReadOnly%> maxlength="25" tabindex="23" onkeydown="checkkeypress(event)" > <%end if%>
<%=PensionAEFieldName%> <%' start of code TJS 18/10/16 if iParamCount > 0 then%> <%else ' end of code TJS 18/10/16%> <%else%>-readonly<%end if%>" value="<%=GetDBField("Auto_Enrolment_Param_3", "AutoEnrolParam3")%>"<%=ContribReadOnly%> maxlength="35" tabindex="24" onkeydown="checkkeypress(event)" > <%end if%>
<%=PensionAEFieldName%>  <%if iParamCount > 0 then%> <%' start of code TJS 18/10/16 else%> <%else%>-readonly<%end if%>" value="<%=GetDBField("Auto_Enrolment_Param_4", "AutoEnrolParam4")%>"<%=ContribReadOnly%> maxlength="35" tabindex="25" onkeydown="checkkeypress(event)" > <%end if if "" & LumpSumEmployee = "" then ' TJS 10/07/17 LumpSumEmployee = 0 ' TJS 10/07/17 end if if "" & LumpSumEmployer = "" then ' TJS 10/07/17 LumpSumEmployer = 0 ' TJS 10/07/17 end if ' end of code TJS 18/10/16%>Yes<%else%>No<%end if%>">
<%if SchemeIsCompany and not NoRecordsFound then ' TJS 28/02/17%> <%end if%> <%if SchemeIsCompany and not NoRecordsFound then ' TJS 28/02/17 if cBool(rsPensDetails("Lump_Sum_AVC_Allowed")) then%> <%end if if cBool(rsPensDetails("Percentage_AVC_Allowed")) then%> <%end if end if if "" & GetDBField("Scheme_Type", "SchemeType") = "A" then ' TJS 26/04/16%> <%end if%>

Pension Contributions for <%=PayrollPeriod%>
"> ">
Lump Sum Contributions
  Employer <%else%>-readonly<%end if%>" style="width:70px;" name="LumpSumEmployer" id="LumpSumEmployer" placeholder="£" maxlength="9" value="<%=LumpSumEmployer%>" tabindex="31"<%=ContribReadOnly%>> Employee<%if "" & GetDBField("Scheme_Type", "SchemeType") = "A" then ' TJS 16/09/14%> Salary Sacrifice<%end if%> <%else%>-readonly<%end if%>" style="width:70px;" name="LumpSumEmployee" id="LumpSumEmployee" placeholder="£" maxlength="9" value="<%=LumpSumEmployee%>" tabindex="32"<%=ContribReadOnly%>>
  checked<%end if%> tabindex="33" onclick="<%if not ContribAllowChanges then%>undoradio('LSCryFwdY', this)<%end if%>"> This Period checked<%end if%> tabindex="34" onclick="<%if not ContribAllowChanges then%>undoradio('LSCryFwdY', this)<%end if%>"> This and Future Periods
 
Percentage of Pensionable Salary Contributions
  Employer <%else%>-readonly<%end if%>" style="width:50px;" name="PercentEmployer" id="PercentEmployer" maxlength="9" value="<%=PercentEmployer%>%" tabindex="35"<%=ContribReadOnly%>> Employee<%if "" & GetDBField("Scheme_Type", "SchemeType") = "A" then ' TJS 16/09/14%> Salary Sacrifice<%end if%> <%else%>-readonly<%end if%>" style="width:50px;" name="PercentEmployee" id="PercentEmployee" maxlength="9" value="<%=PercentEmployee%>%" tabindex="36"<%=ContribReadOnly%>>
  This Period This and Future Periods
 
Lump Sum AVC Contributions
  Employee <%else%>-readonly<%end if%>" style="width:70px;" name="LumpSumAVCEmployee" placeholder="£" maxlength="9" value="<%=LumpSumAVCEmployee%>" tabindex="37"<%=ContribReadOnly%>>
  This Period This and Future Periods
 
Percentage of Pensionable Salary AVC Contributions
  Employee <%else%>-readonly<%end if%>" style="width:50px;" name="PercentAVCEmployee" placeholder="%" maxlength="9" value="<%=PercentAVCEmployee%>" tabindex="38"<%=ContribReadOnly%>>
  This Period This and Future Periods
NOTE With Salary Sacrifice Pension Schemes, there is no Employee Pension Contribution. Instead the Employee makes a salary sacrifice and the Employer makes the TOTAL pension contribution.
<%' start of code added TJS 12/05/17 if bEnablePensionAELetters then sql = "SELECT AE_Form_ID, Template_Type, Letter_Date, EmailSent FROM " & DBSchema & "Employee_Pension_AE_Forms EPF INNER JOIN " & DBSchema & "Pension_AE_Letter_Templates PLT " sql = sql & "ON EPF.Template_ID = PLT.Template_ID WHERE Employer_ID = " & EmployerID & " AND Employee_ID = " & EmployeeID & " AND EPF.Deleted = 0 ORDER BY Letter_Date" set rsAELetters = OpenRecordset(objConn, sql)%>
<%if not rsAELetters.EOF then%> <%iPtr = 1 do while not rsAELetters.EOF%> <%rsAELetters.movenext loop end if%>

Pension AE Letters
Date Type    
<%=rsAELetters("Letter_Date")%> <% select case rsAELetters("Template_Type") case "Enroled" response.write "Enroled" case "EnroledNoTaxRel" response.write "Enroled
(No Tax Relief)" case "NotEnroled" response.write "Not Enroled" case "Postponed" response.write "Postponed" end select%>		 )">View )"><%if cBool(rsAELetters("EmailSent")) then%>Resend<%else%>Send<%end if%> Email
<%rsAELetters.close set rsAELetters = nothing end if ' end of code added TJS 12/05/17%>

onclick="updatesubmit()" onkeypress="checkreturn(event)"<%end if%>>Update <%if "" & FailureMessage <> "" then%>Cancel<%else%>Close<%end if%>

<%end if ' start of code added TJS 12/05/17 if bEnablePensionAELetters then%>
/Standard/EmailAELetters.asp" target="AEEmail">
<%end if%> <% rsPensList.close set rsPensList = nothing if not NoRecordsFound then rsEmployer.close ' TJS 13/02/07 TJS 26/02/18 rsPensDetails.close rsNominalCodes.close ' TJS 16/09/14 rsAEStatus.close ' TJS 08/12/14 rsPayroll.close ' TJS 08/12/14 set rsEmployer = nothing ' TJS 13/02/07 TJS 26/02/18 set rsPensDetails = nothing set rsNominalCodes = nothing ' TJS 16/09/14 set rsAEStatus = nothing ' TJS 08/12/14 set rsPayroll = nothing ' TJS 08/12/14 end if rsEmplDetails.close set rsEmplDetails = nothing ReleaseASPEncrypt SaveSessionVarsRecord SessionVarsID ' TJS 19/06/07 objConn.close set objConn = nothing %>