The Open Receipts report in SedonaOffice tracks receipts and returns that have been created, but not tied to any bill/credit, or written off. The Open Receipts report total should always match the Open Receipts GL account total. If these two do not reconcile the script below can run to identify the source of the issue.
--This Query is expected to fetch the following
--Closed Receipts where the total amount from the bills or Credits tied receipt does not equal the total received amount
--Receipts closed no bill where the write off amount does not equal the total received amount or there was no write of at all
select av.Vendor_code, [IR].[Receipt_Id], ir.[Reference_Number], [IR].[Received_Cost]+[IR].[Expense_Cost] [Total_received], [IR].[Received_Date], isnull([iv].[InvAmount], 0) OffsetAmount, 'Invoice' Type
FROM [dbo].[IN_Receipt] as [IR]
INNER JOIN [dbo].[AP_Vendor] as [AV] on av.[Vendor_Id] = ir.[Vendor_Id]
outer apply (select sum(case [reg].[Credit_Or_Debit] when 'C' then -[Reg].[Amount] else Reg.[Amount] end) InvAmount
from [dbo].[AP_Invoice] as [AI]
INNER JOIN [dbo].[GL_Register] as [GR] on gr.[Register_Id] = ai.[Register_Id]
INNER JOIN [dbo].[GL_Register] as Reg on reg.[Register_Number] = gr.[Register_Number]
and [reg].[Account_Id] = (select [Receipt_Id] from [dbo].[AP_Setup] where [Setup_Id] = 1)
where [AI].[Invoice_Id] = ir.[Invoice_Id]
) iv
where [IR].[Register_Id] > 1
and [IR].[Invoice_Id] > 1
and [IR].[Received_Cost]+[IR].[Expense_Cost] <> isnull(iv.InvAmount, 0)
union
select av.Vendor_code, [IR].[Receipt_Id], ir.[Reference_Number], [IR].[Received_Cost]+[IR].[Expense_Cost] [Total_received], [IR].[Received_Date], isnull([iv].[InvAmount], 0) OffsetAmount, 'Credit' Type
FROM [dbo].[IN_Receipt] as [IR]
INNER JOIN [dbo].[AP_Vendor] as [AV] on av.[Vendor_Id] = ir.[Vendor_Id]
Outer apply (select sum(case [reg].[Credit_Or_Debit] when 'C' then -[Reg].[Amount] else Reg.[Amount] end) InvAmount
from [dbo].[AP_Credit] as [AI]
INNER JOIN [dbo].[GL_Register] as [GR] on gr.[Register_Id] = ai.[Register_Id]
INNER JOIN [dbo].[GL_Register] as Reg on reg.[Register_Number] = gr.[Register_Number]
and [reg].[Account_Id] = (select [Receipt_Id] from [dbo].[AP_Setup] where [Setup_Id] = 1)
where [AI].[Credit_Id] = ir.[Credit_Id]
) iv
where [IR].[Register_Id] > 1
and [IR].[Credit_Id] > 1
and [IR].[Received_Cost]+[IR].[Expense_Cost] <> isnull(iv.InvAmount, 0)
union
select av.Vendor_code, [IR].[Receipt_Id], ir.[Reference_Number], [IR].[Received_Cost]+[IR].[Expense_Cost] [Total_received], [IR].[Received_Date], isnull([iv].[InvAmount], 0) OffsetAmount, 'WriteOff' Type
FROM [dbo].[IN_Receipt] as [IR]
INNER JOIN [dbo].[AP_Vendor] as [AV] on av.[Vendor_Id] = ir.[Vendor_Id]
Outer apply (select sum(case [reg].[Credit_Or_Debit] when 'C' then -[Reg].[Amount] else Reg.[Amount] end) InvAmount
from [dbo].[GL_Register] as [GR]
INNER JOIN [dbo].[GL_Register] as Reg on reg.[Register_Number] = gr.[Register_Number]
and [reg].[Account_Id] = (select [Receipt_Id] from [dbo].[AP_Setup] where [Setup_Id] = 1)
where gr.[Register_Id] = ir.[Write_Off_Register_Id]
) iv
where [IR].[Register_Id] > 1
and [IR].[Received_Cost]+[IR].[Expense_Cost] <> 0
and ([IR].[Credit_Id] < 1 or ir.[Invoice_Id] < 1)
and [IR].[Received_Cost]+[IR].[Expense_Cost] <> isnull(iv.InvAmount, 0)
order by [IR].[Received_Date]
