Issue:
There may be a time when the Deferred Revenue posting shows the amount to process is much less than expected. There can be invoices missing from the table so the deferred is not distributed correctly.
Resolution:
The script below can be used to update the deferred revenue table with the invoices that should be there to process.
The only change that is needed is to update the dates to be used for the invoices.
There is a start and end date. The dates should be for the previous month. If you were running this to correct the deferred for March 31st, the dates would be for the previous month. Edit the Set for the start and end dates.
StartDate = '2025-02-01'
EndDate = '2025-02-28'
This routine will not fix an invoice that has existing posted deferred income.
--- Start of Script ---
IF Not EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeferredFixList]') AND type in (N'U')) Begin CREATE TABLE [dbo].[DeferredFixList]( [InvoiceCorrected] [int] NOT NULL, DateCorrected DateTime) End GO Declare @DeferredAccount Int, @StartDate DateTime, @EndDate DateTime, @NextInvoice Int, @FixInvoiceType Char(1) --'C' 'J' 'A' 'O' SET NOCOUNT ON;
--- Change Dates to match start and end for invoice date. Date will be the previous Month that they run the Deferred process. ---
--- If running through 7/31/2022 invoice dates will be 6/01/2022 to 6/30/2022 ---
Select @DeferredAccount = Def_Revenue_Id from AR_Setup_GL Where Setup_GL_Id = 1 Set @StartDate = '2024-12-01' Set @EndDate = '2023-12-31' Set @FixInvoiceType = 'A' Declare DICursor Insensitive Cursor For Select Inv.Invoice_Id from (Select Invoice_ID, Sum (Amount * Case Credit_or_debit when 'C' then 1 else -1 end) as InvDeferred from gl_register R Inner join (Select distinct Invoice_Id, Register_Number from (Select distinct invoice_id,register_id from ar_invoice_Item Where invoice_id > 1 Union All Select distinct invoice_id,Inter_Branch_Register_Id from ar_invoice_Item Where invoice_id > 1 and Inter_Branch_Register_Id > 1) ii Inner join (select register_id, register_number from gl_register) RX on RX.Register_Id = ii.Register_Id) RL on RL.Register_Number = R.Register_Number Where (Account_Id in (Select Def_Revenue_Id from AR_Setup_GL) or Account_Id in (Select distinct Deferred_Account_Id from ar_item Where Deferred_Account_Id > 1)) Group by Invoice_Id) IValues Full Outer Join (Select Invoice_id, sum(amount) as DefTable from GL_Deferred_Income Where Invoice_Id > 1 Group by Invoice_Id) DValues On DValues.Invoice_Id = IValues.Invoice_Id Left Outer Join AR_Invoice Inv on Inv.Invoice_Id = IsNull(IValues.Invoice_ID, DValues.Invoice_ID) Where IsNull(InvDeferred,0) <> IsNull (DefTable,0) and Inv.posting_date Between @StartDate and @EndDate and (Inv.Type_JSCO = @FixInvoiceType or @FixInvoiceType = 'A') Open DICursor Fetch DICursor Into @NextInvoice While @@FETCH_STATUS = 0 Begin Insert [dbo].[DeferredFixList]([InvoiceCorrected], DateCorrected) Values (@NextInvoice,GETDATE()) Exec Single_Invoice_Deferred_Refile @NextInvoice --Note: This routine will not fix an invoice that has existing posted deferred income! Fetch Next From DICursor Into @NextInvoice End Close DICursor Deallocate DICursor
--- End of Script ---
