FastTrack360 Version 12 Online Help

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 2 Next »

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

  • No labels