To support this release changes have been made to the database schema. These changes may have an impact on reports that have been created using custom queries.
It is important to check your custom reports prior to upgrade to ensure they are working as expected. If you find any issues with data, the information below should assist you to make any changes required.
Database Schema Changes V12.30
To facilitate the introduction of Multi-period Payroll functionality in this release, a new intermediary database table has been added to store data that ties payees to their respective pay periods.
The introduction of the intermediary table means that any reports that rely on a join between the pre-existing rar.GrossWageBatch and rar.GrossWageBatchPayee tables will be affected. Because of that, the following statement can be used to correct the existing joins within report queries:
-- None MPP linking rar.GrossWageBatch to rar.GrossWageBatchPayee
SELECT
*
FROM rar.GrossWageBatch b
JOIN rar.GrossWageBatchPayee p
ON b.GrossWageBatch_id = p.GrossWageBatch_id
-- None MPP linking rar.GrossWageBatch to rar.GrossWageBatchPayee_History
SELECT
*
FROM rar.GrossWageBatch b
JOIN rar.GrossWageBatchPayee_History p
ON b.GrossWageBatch_id = p.GrossWageBatch_id
-------------------------------------------------------------------------------
-- MPP linking rar.GrossWageBatch to rar.GrossWageBatchPayee
--**** NOTE : GrossWageBatch_id column in the rar.GrossWageBatchPayee table will be dropped
SELECT
*
FROM rar.GrossWageBatch b
JOIN rar.GrossWageBatchPeriods d
ON b.GrossWageBatch_id = d.GrossWageBatchId
JOIN rar.GrossWageBatchPayee p
ON d.GrossWageBatchPeriodId = p.GrossWageBatchPeriodId
-- MPP linking rar.GrossWageBatch to rar.GrossWageBatchPayee_History
--**** NOTE : GrossWageBatch_id column in the rar.GrossWageBatchPayee_History table will be dropped
SELECT
*
FROM rar.GrossWageBatch b
JOIN rar.GrossWageBatchPeriods d
ON b.GrossWageBatch_id = d.GrossWageBatchId
JOIN rar.GrossWageBatchPayee_History p
ON d.GrossWageBatchPeriodId = p.GrossWageBatchPeriodId
Note that this will ensure that data can be retrieved by the report query but depending on the context of a report, further changes may be required to the report query based on the database schema changes that have been made in this release.
The table below identifies all other database schema changes that have been made to support this release. These changes may have an impact on reports that have been created using custom queries.
It is important to check your custom reports prior to upgrade to ensure they are working as expected. If you find any issues with data, the information below should assist you to make any changes required.
Table | Field | Action | Comment / Extended Property Description |
DebtorInvoiceLinkOrderReference | Deleted | ||
[rar].[PAYGLeavePaymentDeductionHeader] | Deleted | ||
[FPSPayees] | [EmployeeEmploymentPaymentPeriodsCovered] | Added | No of pay periods included in the FPS. |
[InvoiceHeader] | [CustomerName] | Changed | Field length changed |
[FO].[CacheReportDesigner] | [ReportString] | Changed | Made nullable |
[FO].[Countries] | [OnlyIncludeTimesheetsWithinCurrentPeriod] | Deleted | |
[rar].[GrossWageBatch] | [OnlyIncludePayeesWithTimesheets_flag] | Deleted | |
[rar].[GrossWageBatch] | [OnlyIncludeTimesheetsWithWeekendingDateLessThanPayPeriod_flag] | Deleted | |
[rar].[GrossWageBatchItem] | [InsertedIdMappingsBatchNo] | Added | Field needed only for internal logic |
[rar].[GrossWageBatchItem] | [GrossWageBatchItemTempId] | Added | Field needed only for internal logic |
[rar].[GrossWageBatchItemLeavePaymentRateDetails] | [LeaveRequestTimeId] | Added | Foreign key to rar.LeaveRequestTime table. |
[rar].[GrossWageBatchItemLeavePaymentRateDetails] | [GrossWageBatchItemTempId] | Added | Field needed only for internal logic |
[rar].[GrossWageBatchPayee] | [GrossWageBatchPeriodId] | Added | Foreign key to rar.GrossWageBatchPeriods table. |
[rar].[GrossWageBatchPayee] | [PayeeHeaderId] | Added | Foreign key to rar.PayeeHeader table. |
[rar].[GrossWageBatchPayee] | [CandidateId] | Added | Foreign key to FO.Candidates table. |
[rar].[GrossWageBatchPayee] | [IsManuallyAdded] | Added | Indicates if the payee added to the pay batch manually. |
[rar].[GrossWageBatchPayee_History] | [GrossWageBatchPeriodId] | Added | Foreign key to rar.GrossWageBatchPeriods table. |
[rar].[GrossWageBatchPayee_History] | [PayeeHeaderId] | Added | Foreign key to rar.PayeeHeader table. |
[rar].[GrossWageBatchPayee_History] | [CandidateId] | Added | Foreign key to FO.Candidates table. |
[rar].[GrossWageBatchPayeeDeductionsHistoryUK] | [BroughtFowardDeductionAmount] | Added | Brought forward deduction amount. |
[rar].[GrossWageBatchPayeeDeductionsHistoryUK] | [BroughtFowardProtectedEarnings] | Added | Brought forward protected earnings. |
[rar].[GrossWageBatchPayeeDeductionsUK] | [BroughtFowardDeductionAmount] | Added | Brought forward deduction amount. |
[rar].[GrossWageBatchPayeeDeductionsUK] | [BroughtFowardProtectedEarnings] | Added | Brought forward protected earnings. |
[rar].[GrossWageBatchPayeeTax] | [TempGrossWageBatchPayeeTaxId] | Added | Field needed only for internal logic |
[rar].[GrossWageBatchProcessLog] | [PayPeriodId] | Added | Foreign key to rar.PayPeriod table. |
[rar].[LeaveTransaction] | [IsTopUpAccrual] | Added | Indicates if the accrual is a top up accrual. |
[rar].[LeaveTransaction] | [ReprocessType] | Added | Reprocess type, 1 - Reprocess, 2 - Top up |
[rar].[LeaveTransactionGrossWageBatch] | [IsTopUpAccrual] | Added | Indicates if the accrual is a top up accrual. |
[rar].[LeaveTransactionGrossWageBatch] | [ReprocessType] | Added | Reprocess type, 1 - Reprocess, 2 - Top up |
[rar].[LeaveTransactionGrossWageBatch] | [LeaveTransferRuleId] | Added | Foreign key to rar.LeaveTransferRules table. |
[rar].[NationalInsuranceContributionsByNationalInsuranceBandGrossWageBatch] | [GrossWageBatchPayeeId] | Added | Foreign key to rar.GrossWageBatchPayee table. |
[rar].[NationalInsuranceContributionsByNationalInsuranceBandGrossWageBatchHistory] | [GrossWageBatchPayeeHistoryId] | Added | Foreign key to rar.GrossWageBatchPayeeHistory table. |
[rar].[Payee] | [PAYGRate] | Added | PAYG Rate. |
[rar].[PayeeAllowance] | [IsToBePaidEveryPeriod] | Added | Indicate if the allowance should be paid every pay period. |
[rar].[PayeeAllowance] | [ExcludeBasedOnPayCodeGroupId] | Added | Indicate if the allowance should be paid only when a timesheet is not present with the given pay codes. |
[rar].[PayeeSickPeriodOfIncapcityToWorkDetailGrossWageBatchUK] | [SSPWeekEndDay] | Added | Made not nullable. |
[rar].[PayeeSickPeriodOfIncapcityToWorkDetailsGrossWageBatchHistoryUK] | [SSPWeekEndDay] | Added | Made not nullable. |
[rar].[PAYGLeavePayment] | [ConditionalVariableId] | Added | Foreign Key to rar.ConditionalVariable.ConditionalVariable_id |
[rar].[PAYGLeavePayment] | [CountryId] | Added | Foreign key to fo.Countries.CountryId. |
[rar].[PAYGLeavePayment] | [ConfigurationTypeId] | Added | 1 = Standard |
[rar].[PAYGLeavePayment] | [PercentageHierarchyId] | Added | 1 = Payee, 2 = Country |
[rar].[PayGroup] | [IsIncludePriorPeriodsTimesheetsInCurrentPeriod] | Deleted | |
[rar].[GrossWageBatchFailedLeaveRequestStatuses] | Table Added | ||
[rar].[GrossWageBatchHistoricalPeriodsForReprocessing] | Table Added | ||
[rar].[GrossWageBatchLeaveAccrualRulesToBeReprocessedAfterLeavePayments] | Table Added | ||
[rar].[GrossWageBatchLeaveRequestTimePreProcessSnapshots] | Table Added | ||
[rar].[GrossWageBatchNIableEarningsForSSP] | Table Added | ||
[rar].[GrossWageBatchPayeeSSPQualifyingDaysUK] | Table Added | ||
[rar].[GrossWageBatchPeriods] | Table Added |
Database Schema Changes V12.31
Table | Field | Action | Comment / Extended Property Description |
DebtorInvoiceLinkOrderReference | Deleted | ||
[rar].[PAYGLeavePaymentDeductionHeader] | Deleted | ||
[FPSPayees] | [EmployeeEmploymentPaymentPeriodsCovered] | Added | No of pay periods included in the FPS. |
[InvoiceHeader] | [CustomerName] | Changed | Field length changed |
[FO].[CacheReportDesigner] | [ReportString] | Changed | Made nullable |
[FO].[Countries] | [OnlyIncludeTimesheetsWithinCurrentPeriod] | Deleted | |
[rar].[GrossWageBatch] | [OnlyIncludePayeesWithTimesheets_flag] | Deleted | |
[rar].[GrossWageBatch] | [OnlyIncludeTimesheetsWithWeekendingDateLessThanPayPeriod_flag] | Deleted | |
[rar].[GrossWageBatchItem] | [InsertedIdMappingsBatchNo] | Added | Field needed only for internal logic |
[rar].[GrossWageBatchItem] | [GrossWageBatchItemTempId] | Added | Field needed only for internal logic |
[rar].[GrossWageBatchItem] | [IsSystemCalculatedGST] | Added | Indicates if the GST is calculated by the system or not. |
[rar].[GrossWageBatchItemLeavePaymentRateDetails] | [LeaveRequestTimeId] | Added | Foreign key to rar.LeaveRequestTime table. |
[rar].[GrossWageBatchItemLeavePaymentRateDetails] | [GrossWageBatchItemTempId] | Added | Field needed only for internal logic |
[rar].[GrossWageBatchPayee] | [GrossWageBatchPeriodId] | Added | Foreign key to rar.GrossWageBatchPeriods table. |
[rar].[GrossWageBatchPayee] | [PayeeHeaderId] | Added | Foreign key to rar.PayeeHeader table. |
[rar].[GrossWageBatchPayee] | [CandidateId] | Added | Foreign key to FO.Candidates table. |
[rar].[GrossWageBatchPayee] | [IsManuallyAdded] | Added | Indicates if the payee added to the pay batch manually. |
[rar].[GrossWageBatchPayee_History] | [GrossWageBatchPeriodId] | Added | Foreign key to rar.GrossWageBatchPeriods table. |
[rar].[GrossWageBatchPayee_History] | [PayeeHeaderId] | Added | Foreign key to rar.PayeeHeader table. |
[rar].[GrossWageBatchPayee_History] | [CandidateId] | Added | Foreign key to FO.Candidates table. |
[rar].[GrossWageBatchPayeeDeductionsHistoryUK] | [BroughtFowardDeductionAmount] | Added | Brought forward deduction amount. |
[rar].[GrossWageBatchPayeeDeductionsHistoryUK] | [BroughtFowardProtectedEarnings] | Added | Brought forward protected earnings. |
[rar].[GrossWageBatchPayeeDeductionsUK] | [BroughtFowardDeductionAmount] | Added | Brought forward deduction amount. |
[rar].[GrossWageBatchPayeeDeductionsUK] | [BroughtFowardProtectedEarnings] | Added | Brought forward protected earnings. |
[rar].[GrossWageBatchPayeeTax] | [TempGrossWageBatchPayeeTaxId] | Added | Field needed only for internal logic |
[rar].[GrossWageBatchProcessLog] | [PayPeriodId] | Added | Foreign key to rar.PayPeriod table. |
[rar].[LeaveTransaction] | [IsTopUpAccrual] | Added | Indicates if the accrual is a top up accrual. |
[rar].[LeaveTransaction] | [ReprocessType] | Added | Reprocess type, 1 - Reprocess, 2 - Top up |
[rar].[LeaveTransactionGrossWageBatch] | [IsTopUpAccrual] | Added | Indicates if the accrual is a top up accrual. |
[rar].[LeaveTransactionGrossWageBatch] | [ReprocessType] | Added | Reprocess type, 1 - Reprocess, 2 - Top up |
[rar].[LeaveTransactionGrossWageBatch] | [LeaveTransferRuleId] | Added | Foreign key to rar.LeaveTransferRules table. |
[rar].[NationalInsuranceContributionsByNationalInsuranceBandGrossWageBatch] | [GrossWageBatchPayeeId] | Added | Foreign key to rar.GrossWageBatchPayee table. |
[rar].[NationalInsuranceContributionsByNationalInsuranceBandGrossWageBatchHistory] | [GrossWageBatchPayeeHistoryId] | Added | Foreign key to rar.GrossWageBatchPayeeHistory table. |
[rar].[Payee] | [PAYGRate] | Added | PAYG Rate. |
[rar].[PayeeAllowance] | [IsToBePaidEveryPeriod] | Added | Indicate if the allowance should be paid every pay period. |
[rar].[PayeeAllowance] | [ExcludeBasedOnPayCodeGroupId] | Added | Indicate if the allowance should be paid only when a timesheet is not present with the given pay codes. |
[rar].[PayeeSickPeriodOfIncapcityToWorkDetailGrossWageBatchUK] | [SSPWeekEndDay] | Added | Made not nullable. |
[rar].[PayeeSickPeriodOfIncapcityToWorkDetailsGrossWageBatchHistoryUK] | [SSPWeekEndDay] | Added | Made not nullable. |
[rar].[PAYGLeavePayment] | [ConditionalVariableId] | Added | Foreign Key to rar.ConditionalVariable.ConditionalVariable_id |
[rar].[PAYGLeavePayment] | [CountryId] | Added | Foreign key to fo.Countries.CountryId. |
[rar].[PAYGLeavePayment] | [ConfigurationTypeId] | Added | 1 = Standard |
[rar].[PAYGLeavePayment] | [PercentageHierarchyId] | Added | 1 = Payee, 2 = Country |
[rar].[PayGroup] | [IsIncludePriorPeriodsTimesheetsInCurrentPeriod] | Deleted | |
dbo.ProformaRemittancePayResult | [ConsumptionTax] | Added | VAT/GST Amount |
[dbo].[TimesheetHeader] | [IsAWRAdjustment] | Added | |
[FO].[AttendanceDetails] | [AWRNumberOfWeeksPauseApplies] | Added | |
[FO].[Clients] | [QualifyingWeeksBeforeParity] | Added | |
[FO].[JobOrders] | [LegacySystemId] | Added | |
[FO].[JobOrders] | [IsAWRMonitoringNotRequired] | Added | |
[FO].[JobOrders] | [AWRJobRoleComparator] | Added | |
[FO].[JobOrders] | [AWRClockCount] | Added | |
[FO].[JobOrders] | [AWREnhancedHolidayOverride] | Added | |
[FO].[JobOrders] | [IsAWREnhancedHolidayOverrideToBeProcessed] | Added | |
[FO].[JobTemplates] | [LegacySystemId] | Added | |
[FO].[JobTemplates] | [IsAWRMonitoringNotRequired] | Added | |
[FO].[JobTemplates] | [AWRJobRoleComparator] | Added | |
[FO].[JobTemplates] | [AWREnhancedHolidayOverride] | Added | |
[FO].[Offices] | [QualifyingWeeksBeforeParity] | Added | |
[FO].[Offices] | [ResetAfterWeeksNotWorked] | Added | |
[rar].[BillRateSubRule] | [AWRParityType] | Added | |
[rar].[LeaveAccrualRule] | [UseAWREnhHolidayOverrideAtParity] | Added | |
[rar].[LeaveResetRule] | [IsIncludeTerminatedPayees] | Added | |
[rar].[PayBillRateSubRule] | [AWRParityType] | Added | |
[rar].[PayResult] | [ConsumptionTax] | Added | VAT/GST Amount |
[rar].[StandardRate] | [AWRParityType] | Added | |
[rar].[GrossWageBatchFailedLeaveRequestStatuses] | Table Added | ||
[rar].[GrossWageBatchHistoricalPeriodsForReprocessing] | Table Added | ||
[rar].[GrossWageBatchLeaveAccrualRulesToBeReprocessedAfterLeavePayments] | Table Added | ||
[rar].[GrossWageBatchLeaveRequestTimePreProcessSnapshots] | Table Added | ||
[rar].[GrossWageBatchNIableEarningsForSSP] | Table Added | ||
[rar].[GrossWageBatchPayeeSSPQualifyingDaysUK] | Table Added | ||
[rar].[GrossWageBatchPeriods] | Table Added | ||
[dbo].[AwrClockRestPauseServiceLog] | Table Added | ||
[FO].[AWRClockHistory] | Table Added | ||
[FO].[AWRClockHistoryHeader] | Table Added | ||
[FO].[AWRDataInitialisation] | Table Added | ||
[FO].[AWRLinkedJobs] | Table Added | ||
[dbo].[TimesheetHeader] | [IsAWRAdjustment] | Added | |
[FO].[AttendanceDetails] | [AWRNumberOfWeeksPauseApplies] | Added | |
[FO].[Clients] | [QualifyingWeeksBeforeParity] | Added | |
[FO].[Countries] | [IsAWR] | Added | |
[FO].[Countries] | [QualifyingWeeksBeforeParity] | Added | |
[FO].[Countries] | [ResetAfterWeeksNotWorked] | Added | |
[FO].[Countries] | [MonitoringNotRequiredForPayeeTypes] | Added | |
[FO].[Countries] | [ClockCountBasis] | Added | |
[FO].[Countries] | [AWRAdjustmentTimesheetReasonId] | Added | |
[FO].[Countries] | [ClockResetPauseAssessmentDay] | Added | |
[FO].[Countries] | [ClockResetPauseAssessmentTime] | Added |