A bill payment consists of a summary record and a detail record. These records are split between the Journal and Payment tables so it is necessary to have a matching record in the Journal and Payment table. This SQL query will identify any Master Bill Payment (summary) records that are missing from the Journal and Payments tables and insert them. It relies on the detail records being present into order to insert the summary record into the database.

Adjustment may be required to this query based on the specific issue you are attempting to resolve. Be sure to backup any data and verify everything before altering any records in the database.This query must be executed in the order listed below by first inserting the missing entries into the Payment table, then they will be entered into the Journal table.

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.

--INSERT INTO Payment
SELECT
ID-1 AS [ID] -- We subtract 1 from the ID since the Summary Record is always 1 number before the detail record.
,[StoreID]
,20037 AS [ClassTypeID] -- The classtypeid must be set to 20037 for the Master Summary Record.
,[ModifiedByUser]
,[ModifiedByComputer]
,[ModifiedDate]
,[SeqID]
,[IsSystem]
,[IsActive]
,[NameOnCard]
,[TrackingNumber]
,[PaymentAccountID]
,[PaymentAccountClassTypeID]
,[ExpirationDate]
,[VCode]
,[IsCCNumEncrypt]
,[DisplayNumber]
,[IsVCodeEncrypted]
,[Undeposited]
,[DepositGLGroupID]
,[DepositJournalID]
,[PaymentDate]
,[BankAccountID]
,[BankAccountClasstypeID]
,[TenderType]
,[PayrollID]
,[PayrollClassTypeID]
,[PaycheckID]
,[BankReference]
,[BankCode]
,[BranchCode]
,[CIN]
,[State]
,[CCAccount]
,[CCCSTransactionGuid]
,[CCCSCustomerGuid]
,[IsActualCheck]
FROM [Payment]
WHERE ID IN (SELECT ID FROM Journal WHERE SummaryID NOT IN (SELECT ID FROM Journal) AND ID > 0)
-- In the where statement above we're checking for all records in the Journal that are missing the summary.
-- Then using those we're able to identify the detail records in the Payment table so we can reconstruct the data.
GO
--INSERT INTO Journal
SELECT
SummaryID AS [ID]
,[StoreID]
,SummaryClassTypeID AS [ClassTypeID]
,[ModifiedByUser]
,[ModifiedByComputer]
,[ModifiedDate]
,[SeqID]
,[IsSystem]
,[IsActive]
,[EmployeeID]
,[JournalActivityType]
,[JournalActivityText]
,CAST('Master Bill Payment for ' + SUBSTRING(Description,18,
    CASE WHEN (CharIndex('(',Description)-18) > 0 THEN (CharIndex('(',Description)-18) ELSE 32 END)
 AS NvarChar(50)) AS [Description]
,[Notes]
,[StartDateTime]
,[EndDateTime]
,[TotalTime]
,[ScheduledDateTime]
,[CompletedByID]
,[CompletedDateTime]
,'1' AS [IsSummary]
,'0' AS [IsDetail]
,NULL AS [SummaryID]
,NULL AS [SummaryClassTypeID]
,DetailAmount AS [SummaryAmount]
,0 AS [DetailAmount]
,[StartGLGroupID]
,[EndGLGroupID]
,[AccountID]
,[AccountClassTypeID]
,NULL AS [ContactID]
,NULL AS [ContactClassTypeID]
,NULL AS [TransactionID]
,NULL AS [TransactionClassTypeID]
,[IsVoided]
,[VoidedDateTime]
,[VoidedEntryID]
,[VoidedEntryClassTypeID]
,[VoidedReason]
,[QueryStartDateTime]
,[QueryEndDateTime]
,[ReminderDateTime]
,[ReminderPrompt]
,[PartID]
,[ActivityType]
,[ActivityTypeText]
,[IsBillable]
,[BillableDateTime]
,[UseActualTime]
,[BillingNotes]
,[BillingType]
,[TotalBilledTime]
,[RecurringActivityID]
,[LinkID]
,[LinkStoreID]
,[LinkClassTypeID]
,[SpecialCode]
,[DivisionID]
,[HasCalendarLinks]
,[TipRecipientID]
,[PartClassTypeID]
,[RecurringClassTypeID]
,[StationID]
,[StationClassTypeID]
,[CurrentState]
,[StageID]
,[StageClassTypeID]
FROM [Journal]
WHERE SummaryClassTypeID = 20037 AND SummaryID NOT IN (SELECT ID FROM Journal) AND ID > 0

• Entered : 04/13/2011
• Version : 4.5
• Reference Ticket: Ticket # 156977