This query will locate any entries in the general ledger that were for finance charges that do not have a corresponding entry within the Journal table. This query relies on the following information being correct, if all of these conditions are not true then you should not utilize this query alone to resolve your problem.

  • The GL.JournalID must not be NULL and must not already existing in the Journal table.
  • This query will not validate or correct any finance charge amounts posted to the order.
  • This query will not validate or correct any finance charge amounts posted to the general ledger.

High. Data is modified in this query. Do not run this except under the direction of a Cyrious Technical Support staff member. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL are permanent and non-reversable.

DECLARE @FinanceChargeAccountID INT;
SET @FinanceChargeAccountID = (SELECT TOP 1 FinanceChargeAccountID FROM Store WITH(NOLOCK) WHERE ID > 0);
INSERT INTO Journal
SELECT 
           JournalID AS ID
           , -1 AS StoreID
           , 20020 AS ClassTypeID
           , 'SYSTEM' AS ModifiedByUser
           , 'SYSTEM' AS ModifiedByComputer
           , GETDATE() AS ModifiedDate
           , 0 AS SeqID
           , 0 AS IsSystem
           , 1 AS IsActive
           , 10 AS EmployeeID -- House, Account
           , 3 AS JournalActivityType
           , 'Order' AS JournalActivityText
           , GL.Description AS Description
           , NULL AS Notes
           , EntryDateTime AS StartDateTime
           , EntryDateTime AS EndDateTime
           , NULL AS TotalTime
           , NULL AS ScheduledDateTime
           , 10 AS CompletedByID
           , EntryDateTime AS CompletedDateTime
           , 0 AS IsSummary
           , 1 AS IsDetail
           , NULL AS SummaryID
           , NULL AS SummaryClassTypeID
           , 0 AS SummaryAmount
           , - Amount AS DetailAmount
           , GroupID AS StartGLGroupID
           , GroupID AS EndGLGroupID
           , GL.AccountID AS AccountID
           , AccountClassTypeID AS AccountClassTypeID
           , T.ContactID AS ContactID
           , 3000 AS ContactClassTypeID
           , TransActionID AS TransactionID
           , TransActionClassTypeID AS TransactionClassTypeID
           , 0 AS IsVoided
           , NULL AS VoidedDateTime
           , NULL AS VoidedEntryID
           , NULL AS VoidedEntryClassTypeID
           , NULL AS VoidedReason
           , EntryDateTime AS QueryStartDateTime
           , EntryDateTime AS QueryEndDateTime
           , NULL AS ReminderDateTime
           , 0 AS ReminderPrompt
           , NULL AS PartID
           , 0 AS ActivityType
           , NULL AS ActivityTypeText
           , 0 AS IsBillable
           , NULL AS BillableDateTime
           , 0 AS UseActualTime
           , NULL AS BillingNotes
           , NULL AS BillingType
           , NULL AS TotalBilledTime
           , NULL AS RecurringActivityID
           , NULL AS LinkID
           , NULL AS LinkStoreID
           , NULL AS LinkClassTypeID
           , NULL AS SpecialCode
           , GL.DivisionID AS DivisionID
           , 0 AS HasCalendarLinks
           , NULL AS TipRecipientID
           , NULL AS PartClassTypeID
           , NULL AS RecurringClassTypeID
           , NULL AS StationID
           , NULL AS StationClassTypeID
           , NULL AS CurrentState
           , NULL AS StageID
           , NULL AS StageClassTypeID
FROM GL WITH(NOLOCK)
LEFT JOIN TransHeader T WITH(NOLOCK) ON T.ID = GL.TransActionID
WHERE GLAccountID = @FinanceChargeAccountID -- FinanceCharge Acct
AND JournalID NOT IN (SELECT ID FROM Journal WITH(NOLOCK))
  • Entered : 7/31/2014 Brandon Readlinger (Cyrious)
You could leave a comment if you were logged in.