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