How to setup and configure the Agency Income Report

Modified on Tue, 13 Dec, 2022 at 2:34 PM

Known Issue

If a deposit payment has been linked to a deposit and balance schedule it will create two commission entries for that booking, this is incorrect.

You will need to remove these duplicates afterwards. We would not recommend amending payment schedules.

Simply run the following script and it will highlight all the records that are duplicated. I would recommend spot checking the data.

select * From AgencyIncome where ID not in (select MIN(ID) from AgencyIncome group by [Type],[Item],[BookingID],[Net],[Tax],[Gross],[PropertyId],[PaymentScheduleID]) and BookingID is not null
and CheckInDate > '2021-04-01' and PaymentScheduleID not in (select PaymentScheduleID from AgencyIncome where Net<0 and PaymentScheduleID is not null)
order by ID

Once you are happy that the data is valid, you can delete

delete From AgencyIncome where ID not in (select MIN(ID) from AgencyIncome group by [Type],[Item],[BookingID],[Net],[Tax],[Gross],[PropertyId],[PaymentScheduleID]) and BookingID is not null
and CheckInDate > '2021-04-01' and PaymentScheduleID not in (select PaymentScheduleID from AgencyIncome where Net<0 and PaymentScheduleID is not null)