Differences

This shows you the differences between two versions of the page.

Link to this comparison view

control_sql_-_identify_deposits_in_a_make_deposit_batch [2019/01/27 11:29]
control_sql_-_identify_deposits_in_a_make_deposit_batch [2019/01/27 11:29] (current)
Line 1: Line 1:
 +======  ======
 +
 +
 +
 +===== Explanation of SQL =====
 +
 +
 +
 +Given the JournalID of a make deposit entry, this query shows all of the payments that went into that Make Deposit.  It also includes the make deposit entry itself (this is normally the top entry).
 +
 +
 +
 +===== Risk of Data Corruption if Run Improperly =====
 +
 +**None**. This is a selection query and no data is modified in the running of it.
 +
 +
 +
 +===== SQL =====
 +
 +
 +
 +<code sql>
 +-- Identify the transactions for a make-deposit sequence
 +declare @DepositJournalID int;
 +set @DepositJournalID = 102794;
 +select ID, EntryDateTime, Amount,  
 + (Select AccountName from GLAccount where ID = GLAccountID) as AccountName,
 + (select OrderNumber from TransHeader where ID = TransactionID) as OrderNumber,
 +        (select AccountName from PaymentAccount where ID = Classification) as PaymentType,
 + IsModified, Reconciled, 
 + GLAccountID, Classification, 
 +     GroupID, TransactionID, JournalID, ReconciliationID, OffBalanceSheet, DepositJournalID,
 +        (select Description from Journal where ID = JournalID) as Description,
 +        (select Notes from Journal where ID = JournalID) as Notes,
 + ModifiedDate
 +from Ledger
 +where (JournalID = @DepositJournalID) 
 +      or (DepositJournalID = @DepositJournalID and GLClassificationType = 1007 )
 +order by
 +id desc
 +</code>
 +
 +
 +
 +===== Version Information =====
 +  * Entered : 9/2011
 +  * Version : Control 4.4+
 +
 +
 +